SELECT HEADER_SEQ_ID , SOURCE , SRC_DUMMY , RSN_STATUS , ORG_ID , ( SELECT COUNT (DISTINCT err_seq_id) FROM APPS.XXIF01W_INTERFACE_ERRORS asn_errors , apps.XXPO01T_ASN_INBOUND asn_lines WHERE 1 = 1 AND asn_errors.orig_sys_ref = TO_CHAR (mc.HEADER_seq_id) AND ASN_ERRORS.attribute2 = to_char (mc.file_id) AND asn_errors.orig_sys_ref = TO_CHAR (asn_lines.HEADER_seq_id) AND asn_errors.attribute2 = TO_CHAR (asn_lines.file_id) AND asn_errors.interface_cd = 'OF075' AND asn_errors.ebs_request_id = ( SELECT MAX (asn_err.ebs_request_id) FROM APPS.XXIF01W_INTERFACE_ERRORS asn_err WHERE asn_errors.orig_sys_ref = asn_err.orig_sys_ref AND asn_errors.attribute2 = asn_err.attribute2 AND asn_errors.interface_cd = asn_err.interface_cd ) ) No_of_errors , TOTAL_QUANTITY , TOTAL_AMOUNT , TOTAL_LINES , ASN_CREATION_DATE , INVOICE_DATE , FILE_ID , RSN_DELETED , RSN_RESUBMIT , LOGICAL_DELETE CREATED_BY , CREATION_DATE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN FROM ( SELECT TO_CHAR (asn_h.HEADER_seq_id) HEADER_seq_id , asn_h.attribute3 source , flv.description src_dummy , asn_h.invoice_number RSN , DECODE (asn_h.PROCESS_FLAG, '2', 'E') RSN_status , asn_h.org_id Org_ID , to_char (total_qty) total_quantity , asn_h.total_Amount total_amount , COUNT (asn_l.HEADER_SEQ_ID) total_lines , asn_h.issue_date ASN_creation_date , to_char (TO_DATE (asn_h.attribute1, 'DD-MM-YY'), 'DD-MON-YYYY') invoice_date , to_char (asn_h.file_id) file_id , NVL (ASN_H.RSN_DELETED, 'N') RSN_DELETED , NVL (ASN_H.RSN_RESUBMIT, 'N') RSN_RESUBMIT , NVL (asn_h.attribute2, 0) logical_delete , ASN_H.CREATED_BY created_by , ASN_H.CREATION_DATE creation_date , fnd_profile.value ('USER_ID') last_updated_by , sysdate LAST_UPDATE_DATE , fnd_profile.value ('LOGIN_ID') LAST_UPDATE_LOGIN FROM XXPO01T_ASN_AG_LAYOUT ASN_H , XXPO01T_ASN_INBOUND asn_l , fnd_lookup_values flv WHERE 1 = 1 AND asn_h.INVOICE_NUMBER = asn_l.INVOICE_NUM (+) AND asn_h.file_id = asn_l.file_id (+) AND asn_h.header_seq_id = asn_l.header_seq_id (+) AND asn_h.org_id = asn_l.org_id (+) AND asn_h.PROCESS_FLAG = '2' AND asn_h.org_id = fnd_profile.value ('ORG_ID') AND asn_h.attribute3 = flv.lookup_code AND flv.enabled_flag = 'Y' AND flv.language = USERENV ('LANG') AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (flv.start_date_active, SYSDATE - 1)) AND TRUNC (NVL (flv.end_date_active, SYSDATE + 1)) AND flv.lookup_type = 'XXWMS01_ASNCOR_312_SRC_SWAP' GROUP BY TO_CHAR (asn_h.HEADER_seq_id) , asn_h.attribute3 , flv.description , asn_h.invoice_number , DECODE (asn_h.PROCESS_FLAG, '2', 'E') , asn_h.org_id , to_char (total_qty) , asn_h.total_Amount , asn_h.issue_date , to_char (TO_DATE (asn_h.attribute1, 'DD-MM-YY'), 'DD-MON-YYYY') , to_char (asn_h.file_id) , NVL (ASN_H.RSN_DELETED, 'N') , NVL (ASN_H.RSN_RESUBMIT, 'N') , NVL (asn_h.attribute2, 0) , ASN_H.CREATED_BY , ASN_H.CREATION_DATE , fnd_profile.value ('USER_ID') , sysdate , fnd_profile.value ('LOGIN_ID') ) mc UNION ALL SELECT HEADER_SEQ_ID , SOURCE , SRC_DUMMY , RSN_STATUS , ORG_ID , ( SELECT COUNT (DISTINCT err_seq_id) FROM APPS.XXIF01W_INTERFACE_ERRORS asn_errors , apps.XXPO01T_ASN_INBOUND asn_lines WHERE 1 = 1 AND asn_errors.orig_sys_ref = TO_CHAR (a.HEADER_seq_id) AND asn_errors.orig_sys_ref = TO_CHAR (asn_lines.HEADER_seq_id) AND asn_errors.attribute2 = TO_CHAR (asn_lines.file_id) AND asn_errors.interface_cd IN ('OF168', 'OF335') AND asn_errors.ebs_request_id = ( SELECT MAX (asn_err.ebs_request_id) FROM APPS.XXIF01W_INTERFACE_ERRORS asn_err WHERE asn_errors.orig_sys_ref = asn_err.orig_sys_ref AND asn_errors.attribute2 = asn_err.attribute2 AND asn_errors.interface_cd = asn_err.interface_cd ) ) No_of_errors , TOTAL_QUANTITY , TOTAL_AMOUNT , TOTAL_LINES , ASN_CREATION_DATE , INVOICE_DATE , FILE_ID , RSN_DELETED , RSN_RESUBMIT , LOGICAL_DELETE CREATED_BY , CREATION_DATE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN FROM ( SELECT DISTINCT TO_CHAR (asn_h.HEADER_seq_id) HEADER_SEQ_ID , DECODE (interface_transaction_id, '856', 'EDI', '856FORD', 'FORD') source , flv.description src_dummy , DECODE (interface_transaction_id, '856FORD', DECODE (comments, 'DUP_ASN_FORD_SUP', attribute17, shipment_num) , '856', shipment_num) RSN , DECODE (asn_h.PROCESS_FLAG, '2', 'E', asn_h.PROCESS_FLAG) RSN_status , asn_h.org_id Org_ID , asn_h.attribute10 total_quantity , NULL total_amount , COUNT (HEADER_seq_id) total_lines , trunc (shipped_date) ASN_creation_date , NULL invoice_date , to_char (asn_h.file_id) file_id , NVL (ASN_H.RSN_DELETED, 'N') RSN_DELETED , NVL (ASN_H.RSN_RESUBMIT, 'N') RSN_RESUBMIT , NULL logical_delete , ASN_H.CREATED_BY created_by , ASN_H.CREATION_DATE creation_date , APPS.fnd_profile.value ('USER_ID') last_updated_by , sysdate LAST_UPDATE_DATE , APPS.fnd_profile.value ('LOGIN_ID') LAST_UPDATE_LOGIN FROM APPS.XXPO01T_ASN_INBOUND ASN_H , fnd_lookup_values flv WHERE 1 = 1 AND asn_h.process_flag = '2' AND interface_transaction_id IN ('856', '856FORD') AND asn_h.org_id = fnd_profile.value ('ORG_ID') AND asn_h.interface_transaction_id = flv.lookup_code AND flv.enabled_flag = 'Y' AND flv.language = USERENV ('LANG') AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (flv.start_date_active, SYSDATE - 1)) AND TRUNC (NVL (flv.end_date_active, SYSDATE + 1)) AND flv.lookup_type = 'XXWMS01_ASNCOR_312_SRC_SWAP' GROUP BY TO_CHAR (asn_h.HEADER_seq_id) , DECODE (interface_transaction_id, '856', 'EDI', '856FORD', 'FORD') , flv.description , DECODE (interface_transaction_id, '856FORD', DECODE (comments, 'DUP_ASN_FORD_SUP', attribute17, shipment_num) , '856', shipment_num) , DECODE (asn_h.PROCESS_FLAG, '2', 'E', asn_h.PROCESS_FLAG) , asn_h.org_id , asn_h.attribute10 , NULL , trunc (shipped_date) , NULL , to_char (asn_h.file_id) , NVL (ASN_H.RSN_DELETED, 'N') , NVL (ASN_H.RSN_RESUBMIT, 'N') , NULL , ASN_H.CREATED_BY , ASN_H.CREATION_DATE , APPS.fnd_profile.value ('USER_ID') , sysdate , APPS.fnd_profile.value ('LOGIN_ID') ) a