SELECT ship_to_organization_code          PDC
     , (
          SELECT aps.segment1
            FROM po_headers_all      poh
               , ap_suppliers        aps
               , xxpo01t_asn_inbound asn_l --MITS
           WHERE 1 = 1
             AND ROWNUM = 1
             AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
             AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
             AND asn_l.file_id = asn_lines.file_id--MITS
             AND poh.attribute1 (+) = asn_lines.document_num
             AND poh.org_id = asn_h.org_id
             AND aps.vendor_id (+) = poh.vendor_id
       )                                  vendor_number
     , (
          SELECT apss.vendor_site_code
            FROM ap_suppliers          asa
               , ap_supplier_sites_all apss
               , po_headers_all        poh
               , xxpo01t_asn_inbound   asn_l --MITS
           WHERE 1 = 1
             AND ROWNUM = 1
             AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
             AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
             AND asn_l.file_id = asn_lines.file_id--MITS
             AND asa.vendor_id = apss.vendor_id
             AND poh.vendor_id = asa.vendor_id (+)
             AND poh.vendor_site_id = apss.vendor_site_id (+)
             AND poh.org_id = asn_h.org_id
             AND poh.attribute1 (+) = asn_lines.document_num
       )                                  vendor_site_code
     ,
       --aps.segment1 vendor_number,
       --  apss.vendor_site_code,
       asn_lines.shipped_date
     , TO_CHAR (asn_lines.line_seq_id)    line_seq_id
     , asn_lines.expected_receipt_date
     , asn_lines.invoice_num
     , asn_lines.item_num
     , asn_lines.item_description
     , asn_lines.quantity_shipped         Quantity
     , asn_lines.po_unit_price            FOB
     , asn_lines.document_num             po_number
     , asn_lines.document_line_num        po_line_num
     , (
          SELECT poh.segment1
            FROM po_headers_all      poh
               , xxpo01t_asn_inbound asn_l
           WHERE 1 = 1
             AND ROWNUM = 1
             AND poh.attribute1 (+) = asn_l.document_num
             AND poh.org_id = asn_l.org_id
             AND asn_l.header_seq_id = asn_lines.header_seq_id
             AND asn_l.line_seq_id = asn_lines.line_seq_id
             AND asn_l.file_id = asn_lines.file_id
             --Start of MITS CR-190509-0077
             AND poh.type_lookup_code = 'STANDARD'
             AND NVL (poh.cancel_flag, 'N') = 'N'
             AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
       --End of MITS CR-190509-0077
       )                                  ebs_po_num
     , asn_lines.container_num
     , asn_lines.attribute11              Case_num
     , --asn_lines.attribute10 Case_num,
       asn_lines.currency_code            currency
     , asn_lines.currency_conversion_date exchange_date
     , asn_lines.currency_conversion_rate exchange_rate
     , asn_lines.currency_conversion_type exchange_type
     , asn_lines.attribute4               arrangement_num
     , asn_lines.attribute5               arrangement_line_num
     ,
       --start of 1.1 shipped_seeded_part
       asn_lines.substitute_item_num
     ,
       --end of 1.1 shipped_seeded_part
       asn_lines.CREATED_BY               created_by
     , asn_lines.CREATION_DATE            creation_date
     , fnd_profile.value ('USER_ID')      last_updated_by
     , sysdate                            LAST_UPDATE_DATE
     , fnd_profile.value ('LOGIN_ID')     LAST_UPDATE_LOGIN
     , asn_lines.file_id                  file_id
     , TO_CHAR (asn_lines.header_seq_id)  header_seq_id
     , TO_CHAR (asn_lines.line_seq_id)    line_id
     , asn_lines.invoice_num              rsn_num --CR8_OF_20
  FROM XXPO01T_ASN_AG_LAYOUT ASN_H
     , xxpo01t_asn_inbound   asn_lines
 -- ,xxif01w_interface_errors asn_errors
 WHERE asn_lines.invoice_num = asn_h.invoice_number
   AND asn_lines.header_seq_id = asn_h.header_seq_id
   AND asn_lines.file_id = asn_h.file_id
   AND asn_h.process_flag = '2'
   AND asn_lines.org_id = fnd_profile.value ('ORG_ID')
   -- Start of CR8_OF_20
   AND (test_flag = 'E'
          OR EXISTS (
             SELECT orig_line_ref
               FROM XXIF01W_INTERFACE_ERRORS asn_e
              WHERE 1 = 1
                    AND asn_e.orig_line_ref = to_char (asn_lines.line_seq_id)
                    AND asn_e.orig_sys_ref = to_char (asn_lines.header_seq_id)
                    AND asn_e.attribute2 = to_char (asn_lines.file_id)
                    AND asn_e.interface_cd = 'OF075'
                    AND ebs_request_id = (
                       SELECT MAX (ebs_request_id)
                         FROM XXIF01W_INTERFACE_ERRORS asn_err
                        WHERE 1 = 1
                              AND asn_err.orig_sys_ref = asn_e.orig_sys_ref
                              AND asn_err.interface_cd = asn_e.interface_cd
                    )
          )
       ) -- End of CR8_OF_20
UNION ALL -- UNION CR8_OF_20
SELECT ship_to_organization_code                                                                                                           PDC
     , (
          SELECT aps.segment1
            FROM ap_suppliers          aps
               , ap_supplier_sites_all apss
               , xxpo01t_asn_inbound   asn_l --MITS
           WHERE 1 = 1
             AND ROWNUM = 1
             AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
             AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
             AND asn_l.file_id = asn_lines.file_id--MITS
             AND apss.vendor_site_code = asn_lines.vendor_site_code
             AND aps.vendor_id = apss.vendor_id
       ---and poh.org_id = asn_lines.org_id
       --and aps.vendor_id = poh.vendor_id
       )                                                                                                                                   vendor_number
     , asn_lines.vendor_site_code
     , asn_lines.shipped_date
     , TO_CHAR (asn_lines.line_seq_id)                                                                                                     line_seq_id
     , asn_lines.expected_receipt_date
     , asn_lines.invoice_num
     , asn_lines.item_num
     , asn_lines.item_description
     , asn_lines.quantity_shipped                                                                                                          Quantity
     , NULL                                                                                                                                FOB
     , asn_lines.document_num                                                                                                              po_number
     , asn_lines.document_line_num                                                                                                         po_line_num
     , (
          SELECT poh.segment1
            FROM po_headers_all      poh
               , xxpo01t_asn_inbound asn_l
           WHERE 1 = 1
             AND ROWNUM = 1
             AND poh.attribute1 (+) = asn_l.document_num
             AND poh.org_id = asn_l.org_id
             AND asn_l.header_seq_id = asn_lines.header_seq_id
             AND asn_l.line_seq_id = asn_lines.line_seq_id
             AND asn_l.file_id = asn_lines.file_id
             --Start of MITS CR-190509-0077
             AND poh.type_lookup_code = 'STANDARD'
             AND NVL (poh.cancel_flag, 'N') = 'N'
             AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
       --End of MITS CR-190509-0077
       )                                                                                                                                   ebs_po_num
     , DECODE (interface_transaction_id, '856', asn_lines.container_num, '856FORD', NULL)                                                  container_number
     , --asn_lines.container_num,-- CR16_OF_38 For FORD ASN
       NULL                                                                                                                                Case_num
     , NULL                                                                                                                                currency
     , NULL                                                                                                                                exchange_date
     , NULL                                                                                                                                exchange_rate
     , NULL                                                                                                                                exchange_type
     , NULL                                                                                                                                arrangement_num
     , NULL                                                                                                                                arrangement_line_num
     ,
       --start of 1.1 shipped_seeded_part
       asn_lines.substitute_item_num
     ,
       --end of 1.1 shipped_seeded_part
       asn_lines.CREATED_BY                                                                                                                created_by
     , asn_lines.CREATION_DATE                                                                                                             creation_date
     , fnd_profile.value ('USER_ID')                                                                                                       last_updated_by
     , sysdate                                                                                                                             LAST_UPDATE_DATE
     , fnd_profile.value ('LOGIN_ID')                                                                                                      LAST_UPDATE_LOGIN
     , asn_lines.file_id                                                                                                                   file_id
     , TO_CHAR (asn_lines.header_seq_id)                                                                                                   header_seq_id
     , TO_CHAR (asn_lines.line_seq_id)                                                                                                     line_id
     , DECODE (interface_transaction_id, '856FORD', DECODE (comments, 'DUP_ASN_FORD_SUP', attribute17, shipment_num), '856',
     shipment_num) rsn_num --asn_lines.shipment_num rsn_num --CR8_OF_20 --CR16_OF_38 FORD ASN
  FROM xxpo01t_asn_inbound asn_lines
 WHERE 1 = 1
   AND interface_transaction_id IN ('856', '856FORD') -- = '856' -- CR16_OF_38 For FORD ASN
   AND process_flag = '2'
   --Start of CR8_OF_20
   AND EXISTS (
          SELECT orig_line_ref
            FROM XXIF01W_INTERFACE_ERRORS asn_e
           WHERE 1 = 1
             AND asn_e.orig_line_ref = to_char (asn_lines.line_seq_id)
             AND asn_e.orig_sys_ref = to_char (asn_lines.header_seq_id)
             AND asn_e.attribute2 = to_char (asn_lines.file_id)
             AND asn_e.interface_cd IN ('OF168', 'OF335') -- = 'OF168' -- CR16_OF_38 For FORD ASN
             AND ebs_request_id = (
                    SELECT MAX (ebs_request_id)
                      FROM XXIF01W_INTERFACE_ERRORS asn_err
                     WHERE 1 = 1
                       AND asn_err.orig_sys_ref = asn_e.orig_sys_ref
                       AND asn_err.interface_cd = asn_e.interface_cd
                 )
       ) -- End of CR8_OF_20




Modified -

SELECT ship_to_organization_code          PDC
     , (
          SELECT aps.segment1
            FROM po_headers_all      poh
               , ap_suppliers        aps
               , xxpo01t_asn_inbound asn_l --MITS
           WHERE 1 = 1
             AND ROWNUM = 1
             AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
             AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
             AND asn_l.file_id = asn_lines.file_id--MITS
             AND poh.attribute1 (+) = asn_lines.document_num
             AND poh.org_id = asn_h.org_id
             AND aps.vendor_id (+) = poh.vendor_id
       )                                  vendor_number
     , (
          SELECT apss.vendor_site_code
            FROM ap_suppliers          asa
               , ap_supplier_sites_all apss
               , po_headers_all        poh
               , xxpo01t_asn_inbound   asn_l --MITS
           WHERE 1 = 1
             AND ROWNUM = 1
             AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
             AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
             AND asn_l.file_id = asn_lines.file_id--MITS
             AND asa.vendor_id = apss.vendor_id
             AND poh.vendor_id = asa.vendor_id (+)
             AND poh.vendor_site_id = apss.vendor_site_id (+)
             AND poh.org_id = asn_h.org_id
             AND poh.attribute1 (+) = asn_lines.document_num
       )                                  vendor_site_code
     ,
       --aps.segment1 vendor_number,
       --  apss.vendor_site_code,
       asn_lines.shipped_date
     , TO_CHAR (asn_lines.line_seq_id)    line_seq_id
     , asn_lines.expected_receipt_date
     , asn_lines.invoice_num
     , asn_lines.item_num
     , asn_lines.item_description
     , asn_lines.quantity_shipped         Quantity
     , asn_lines.po_unit_price            FOB
     , asn_lines.document_num             po_number
     , asn_lines.document_line_num        po_line_num
     , (
          SELECT poh.segment1
            FROM po_headers_all      poh
               , xxpo01t_asn_inbound asn_l
           WHERE 1 = 1
             AND ROWNUM = 1
             AND poh.attribute1 (+) = asn_l.document_num
             AND poh.org_id = asn_l.org_id
             AND asn_l.header_seq_id = asn_lines.header_seq_id
             AND asn_l.line_seq_id = asn_lines.line_seq_id
             AND asn_l.file_id = asn_lines.file_id
             --Start of MITS CR-190509-0077
             AND poh.type_lookup_code = 'STANDARD'
             AND NVL (poh.cancel_flag, 'N') = 'N'
             AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
       --End of MITS CR-190509-0077
       )                                  ebs_po_num
     , asn_lines.container_num
     , asn_lines.attribute11              Case_num
     , --asn_lines.attribute10 Case_num,
       asn_lines.currency_code            currency
     , asn_lines.currency_conversion_date exchange_date
     , asn_lines.currency_conversion_rate exchange_rate
     , asn_lines.currency_conversion_type exchange_type
     , asn_lines.attribute4               arrangement_num
     , asn_lines.attribute5               arrangement_line_num
     ,
       --start of 1.1 shipped_seeded_part
       asn_lines.substitute_item_num
     ,
       --end of 1.1 shipped_seeded_part
       asn_lines.CREATED_BY               created_by
     , asn_lines.CREATION_DATE            creation_date
     , fnd_profile.value ('USER_ID')      last_updated_by
     , sysdate                            LAST_UPDATE_DATE
     , fnd_profile.value ('LOGIN_ID')     LAST_UPDATE_LOGIN
     , asn_lines.file_id                  file_id
     , TO_CHAR (asn_lines.header_seq_id)  header_seq_id
     , TO_CHAR (asn_lines.line_seq_id)    line_id
     , asn_lines.invoice_num              rsn_num --CR8_OF_20
  FROM XXPO01T_ASN_AG_LAYOUT ASN_H
     , xxpo01t_asn_inbound   asn_lines
 -- ,xxif01w_interface_errors asn_errors
 WHERE asn_lines.invoice_num = asn_h.invoice_number
   AND asn_lines.header_seq_id = asn_h.header_seq_id
   AND asn_lines.file_id = asn_h.file_id
   AND asn_h.process_flag = '2'
   AND asn_lines.org_id = fnd_profile.value ('ORG_ID')
   -- Start of CR8_OF_20
   AND (test_flag = 'E'
          OR EXISTS (
             SELECT /*+ No_unnest(@SQ_101) */ 
                    orig_line_ref
               FROM XXIF01W_INTERFACE_ERRORS asn_e
              WHERE 1 = 1
                    AND asn_e.orig_line_ref = to_char (asn_lines.line_seq_id)
                    AND asn_e.orig_sys_ref = to_char (asn_lines.header_seq_id)
                    AND asn_e.attribute2 = to_char (asn_lines.file_id)
                    AND asn_e.interface_cd = 'OF075'
                    AND ebs_request_id = (
                       SELECT /*+ qb_name(SQ_101) */
                              MAX (ebs_request_id)
                         FROM XXIF01W_INTERFACE_ERRORS asn_err
                        WHERE 1 = 1
                              AND asn_err.orig_sys_ref = asn_e.orig_sys_ref
                              AND asn_err.interface_cd = asn_e.interface_cd
                    )
          )
       ) -- End of CR8_OF_20
UNION ALL -- UNION CR8_OF_20
SELECT ship_to_organization_code                                                                                                           PDC
     , (
          SELECT aps.segment1
            FROM ap_suppliers          aps
               , ap_supplier_sites_all apss
               , xxpo01t_asn_inbound   asn_l --MITS
           WHERE 1 = 1
             AND ROWNUM = 1
             AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
             AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
             AND asn_l.file_id = asn_lines.file_id--MITS
             AND apss.vendor_site_code = asn_lines.vendor_site_code
             AND aps.vendor_id = apss.vendor_id
       ---and poh.org_id = asn_lines.org_id
       --and aps.vendor_id = poh.vendor_id
       )                                                                                                                                   vendor_number
     , asn_lines.vendor_site_code
     , asn_lines.shipped_date
     , TO_CHAR (asn_lines.line_seq_id)                                                                                                     line_seq_id
     , asn_lines.expected_receipt_date
     , asn_lines.invoice_num
     , asn_lines.item_num
     , asn_lines.item_description
     , asn_lines.quantity_shipped                                                                                                          Quantity
     , NULL                                                                                                                                FOB
     , asn_lines.document_num                                                                                                              po_number
     , asn_lines.document_line_num                                                                                                         po_line_num
     , (
          SELECT poh.segment1
            FROM po_headers_all      poh
               , xxpo01t_asn_inbound asn_l
           WHERE 1 = 1
             AND ROWNUM = 1
             AND poh.attribute1 (+) = asn_l.document_num
             AND poh.org_id = asn_l.org_id
             AND asn_l.header_seq_id = asn_lines.header_seq_id
             AND asn_l.line_seq_id = asn_lines.line_seq_id
             AND asn_l.file_id = asn_lines.file_id
             --Start of MITS CR-190509-0077
             AND poh.type_lookup_code = 'STANDARD'
             AND NVL (poh.cancel_flag, 'N') = 'N'
             AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
       --End of MITS CR-190509-0077
       )                                                                                                                                   ebs_po_num
     , DECODE (interface_transaction_id, '856', asn_lines.container_num, '856FORD', NULL)                                                  container_number
     , --asn_lines.container_num,-- CR16_OF_38 For FORD ASN
       NULL                                                                                                                                Case_num
     , NULL                                                                                                                                currency
     , NULL                                                                                                                                exchange_date
     , NULL                                                                                                                                exchange_rate
     , NULL                                                                                                                                exchange_type
     , NULL                                                                                                                                arrangement_num
     , NULL                                                                                                                                arrangement_line_num
     ,
       --start of 1.1 shipped_seeded_part
       asn_lines.substitute_item_num
     ,
       --end of 1.1 shipped_seeded_part
       asn_lines.CREATED_BY                                                                                                                created_by
     , asn_lines.CREATION_DATE                                                                                                             creation_date
     , fnd_profile.value ('USER_ID')                                                                                                       last_updated_by
     , sysdate                                                                                                                             LAST_UPDATE_DATE
     , fnd_profile.value ('LOGIN_ID')                                                                                                      LAST_UPDATE_LOGIN
     , asn_lines.file_id                                                                                                                   file_id
     , TO_CHAR (asn_lines.header_seq_id)                                                                                                   header_seq_id
     , TO_CHAR (asn_lines.line_seq_id)                                                                                                     line_id
     , DECODE (interface_transaction_id, '856FORD', DECODE (comments, 'DUP_ASN_FORD_SUP', attribute17, shipment_num), '856',
     shipment_num) rsn_num --asn_lines.shipment_num rsn_num --CR8_OF_20 --CR16_OF_38 FORD ASN
  FROM xxpo01t_asn_inbound asn_lines
 WHERE 1 = 1
   AND interface_transaction_id IN ('856', '856FORD') -- = '856' -- CR16_OF_38 For FORD ASN
   AND process_flag = '2'
   --Start of CR8_OF_20
   AND EXISTS (
          SELECT /*+ No_unnest(@SQ_201) */  
                 orig_line_ref
            FROM XXIF01W_INTERFACE_ERRORS asn_e
           WHERE 1 = 1
             AND asn_e.orig_line_ref = to_char (asn_lines.line_seq_id)
             AND asn_e.orig_sys_ref = to_char (asn_lines.header_seq_id)
             AND asn_e.attribute2 = to_char (asn_lines.file_id)
             AND asn_e.interface_cd IN ('OF168', 'OF335') -- = 'OF168' -- CR16_OF_38 For FORD ASN
             AND ebs_request_id = (
                    SELECT /*+ qb_name(SQ_201) */
                           MAX (ebs_request_id)
                      FROM XXIF01W_INTERFACE_ERRORS asn_err
                     WHERE 1 = 1
                       AND asn_err.orig_sys_ref = asn_e.orig_sys_ref
                       AND asn_err.interface_cd = asn_e.interface_cd
                 )
       ) -- End of CR8_OF_20