Last active
August 3, 2023 14:03
-
-
Save kdclaw3/a3cb477706433946f7a1771a42eecf1e to your computer and use it in GitHub Desktop.
Activity Document Example SQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| ---------------------------------------------------------------------------------------------------- | |
| Table: W1_DOCUMENT - Document | |
| ---------------------------------------------------------------------------------------------------- | |
| The data for CLOB/XML field BO_DATA_AREA should be enclosed by <endclob>. | |
| Delimiter: , | |
| Enclosing Character: " | |
| For not-nullable fields with no value, provide a single space enclosed in enclosing character | |
| Fields: | |
| The first 30 characters contain target table name W1_DOCUMENT padded with spaces | |
| DOCUMENT_ID - Document ID CHAR (12) | |
| DESCR100 - Description VARC (100) NULLABLE | |
| W1_DESCR100_UPR - Description Upper VARC (100) NULLABLE | |
| DOCUMENT_TYPE_CD - Document Type VARC (30) | |
| BUS_OBJ_CD - Business Object CHAR (30) | |
| BO_STATUS_CD - Status CHAR (12) NULLABLE | |
| STATUS_UPD_DTTM - Status Date/Time DATE (26) 'YYYY-MM-DD HH24:MI:SS' NULLABLE | |
| BO_STATUS_REASON_CD - Status Reason VARC (30) NULLABLE | |
| DOCUMENT_CLASS_FLG - Document Class CHAR (4) | |
| DOCUMENT_CATEGORY_FLG - Category CHAR (4) NULLABLE | |
| ATTACHMENT_ID - Attachment ID CHAR (14) | |
| BO_DATA_AREA - Business Object Data Area CHAR (10000) NULLABLE | |
| VERSION - Version NUMB (5) | |
| CRE_DTTM - Create Date/Time DATE (26) 'YYYY-MM-DD HH24:MI:SS' | |
| OWNING_ACCESS_GRP_CD - Owning Organization CHAR (12) NULLABLE*/ | |
| --standard notes | |
| select | |
| rpad('W1_DOCUMENT',30,' ') TABLE_FILLER, | |
| lpad(ora_hash(plant || standard_note_id),12,0) DOCUMENT_ID, --Document ID CHAR (12) | |
| standard_note_id DESCR100, --Description VARC (100) NULLABLE | |
| upper(standard_note_id) W1_DESCR100_UPR, --Description Upper VARC (100) NULLABLE | |
| 'STANDARD NOTE' DOCUMENT_TYPE_CD, --Document Type VARC (30) | |
| 'W1-StandardNote' BUS_OBJ_CD, --Business Object CHAR (30) | |
| 'ACTIVE' BO_STATUS_CD, --Status CHAR (12) NULLABLE | |
| to_char(nvl(last_update_date,to_date('19700101','YYYYMMDD')),'YYYY-MM-DD HH24:MI:SS') STATUS_UPD_DTTM, --Status Date/Time DATE (26) 'YYYY-MM-DD HH24:MI:SS' NULLABLE | |
| null BO_STATUS_REASON_CD, --Status Reason VARC (30) NULLABLE | |
| 'W1SN' DOCUMENT_CLASS_FLG, --Document Class CHAR (4) | |
| --20211019 John DOCUMENT_CATEGORY_FLG | |
| case | |
| when standard_note_type is null then null | |
| when standard_note_type = 'BUYER' then 'BUY' | |
| when standard_note_type = 'FREIGHT' then 'FRGT' | |
| when standard_note_type = 'TAX' then 'TAX' | |
| when standard_note_type = 'RISK' then 'RISK' | |
| else substrb(standard_note_type,1,4) | |
| end DOCUMENT_CATEGORY_FLG, --Category CHAR (4) NULLABLE | |
| ' ' ATTACHMENT_ID, --Attachment ID CHAR (14) | |
| '<note>'|| standard_note_desc ||'</note>' BO_DATA_AREA, --Business Object Data Area CHAR (10000) NULLABLE | |
| 1 VERSION, --Version NUMB (5) | |
| to_char(nvl(created_date,to_date('19700101','YYYYMMDD')),'YYYY-MM-DD HH24:MI:SS') CRE_DTTM, --Create Date/Time DATE (26) 'YYYY-MM-DD HH24:MI:SS' | |
| null OWNING_ACCESS_GRP_CD --Owning Organization CHAR (12) NULLABLE | |
| from synergen.sa_standard_notes a | |
| where plant = '01'; | |
| --attachments | |
| select | |
| rpad('W1_DOCUMENT',30,' ') TABLE_FILLER, | |
| '9' || lpad(to_number(document_id),11,0) DOCUMENT_ID, --Document ID CHAR (12) | |
| document_desc DESCR100, --Description VARC (100) NULLABLE | |
| upper(document_desc) W1_DESCR100_UPR, --Description Upper VARC (100) NULLABLE | |
| case | |
| when document_storage_type != 'INTERNAL' then 'URL' | |
| else nvl(file_type,'PDF') | |
| end DOCUMENT_TYPE_CD, --Document Type VARC (30) | |
| 'W1-DocAttachment' BUS_OBJ_CD, --Business Object CHAR (30) | |
| 'ACTIVE' BO_STATUS_CD, --Status CHAR (12) NULLABLE | |
| to_char(nvl(last_update_date,to_date('19700101','YYYYMMDD')),'YYYY-MM-DD HH24:MI:SS') STATUS_UPD_DTTM, --Status Date/Time DATE (26) 'YYYY-MM-DD HH24:MI:SS' NULLABLE | |
| null BO_STATUS_REASON_CD, --Status Reason VARC (30) NULLABLE | |
| 'W1AT' DOCUMENT_CLASS_FLG, --Document Class CHAR (4) | |
| null DOCUMENT_CATEGORY_FLG, --Category CHAR (4) NULLABLE | |
| ' ' ATTACHMENT_ID, --Attachment ID CHAR (14) | |
| null BO_DATA_AREA, --Business Object Data Area CHAR (10000) NULLABLE | |
| 1 VERSION, --Version NUMB (5) | |
| to_char(nvl(created_date,to_date('19700101','YYYYMMDD')),'YYYY-MM-DD HH24:MI:SS') CRE_DTTM, --Create Date/Time DATE (26) 'YYYY-MM-DD HH24:MI:SS' | |
| null OWNING_ACCESS_GRP_CD --Owning Organization CHAR (12) NULLABLE | |
| from synergen.sa_document a | |
| where plant = '01'; | |
| /* | |
| ---------------------------------------------------------------------------------------------------- | |
| Table: W1_ACTIVITY_DOCUMENT - Activity Document | |
| ---------------------------------------------------------------------------------------------------- | |
| Delimiter: , | |
| Enclosing Character: " | |
| For not-nullable fields with no value, provide a single space enclosed in enclosing character | |
| Fields: | |
| The first 30 characters contain target table name W1_ACTIVITY_DOCUMENT padded with spaces | |
| ACT_ID - Activity CHAR (14) | |
| DOCUMENT_ID - Document ID CHAR (12) | |
| ATTACHMENT_ID - Attachment ID CHAR (14) | |
| VERSION - Version NUMB (5) | |
| CRE_DTTM - Create Date/Time DATE (26) 'YYYY-MM-DD HH24:MI:SS' NULLABLE | |
| USER_ID - User CHAR (8)*/ | |
| --task attachment | |
| select | |
| rpad('W1_ACTIVITY_DOCUMENT',30,' ') TABLE_FILLER, | |
| rpad(plant || a.work_order_no || a.work_order_task_no,14,0) ACT_ID, --Activity CHAR (14) | |
| ' ' DOCUMENT_ID, --Document ID CHAR (12) | |
| '9' || lpad(to_number(attachment_id),13,0) ATTACHMENT_ID, --Attachment ID CHAR (14) | |
| 1 VERSION, --Version NUMB (5) | |
| to_char(nvl(created_date,to_date('19700101','YYYYMMDD')),'YYYY-MM-DD HH24:MI:SS') CRE_DTTM, --Create Date/Time DATE (26) 'YYYY-MM-DD HH24:MI:SS' NULLABLE | |
| substrb(nvl(a.created_by,'DATACONV'),0,8) USER_ID --User CHAR (8) | |
| from synergen.sa_work_order_attachment a | |
| where plant = '01' | |
| and work_order_no not like 'B%' | |
| and attachment = 'DOCUMENT'; | |
| --task history attachment | |
| select | |
| rpad('W1_ACTIVITY_DOCUMENT',30,' ') TABLE_FILLER, | |
| rpad(plant || a.work_order_no || a.work_order_task_no,14,0) ACT_ID, --Activity CHAR (14) | |
| ' ' DOCUMENT_ID, --Document ID CHAR (12) | |
| '9' || lpad(to_number(attachment_id),13,0) ATTACHMENT_ID, --Attachment ID CHAR (14) | |
| 1 VERSION, --Version NUMB (5) | |
| to_char(nvl(created_date,to_date('19700101','YYYYMMDD')),'YYYY-MM-DD HH24:MI:SS') CRE_DTTM, --Create Date/Time DATE (26) 'YYYY-MM-DD HH24:MI:SS' NULLABLE | |
| substrb(nvl(a.created_by,'DATACONV'),0,8) USER_ID --User CHAR (8) | |
| from synergen.sa_work_history_attachment a | |
| where plant = '01' | |
| and work_order_no not like 'B%' | |
| and attachment = 'DOCUMENT'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment