SELECT RTI.*, rti.DESTINATION_TYPE_CODE,
rti.interface_transaction_id -- 'interface_transaction_id'
, rhi.header_interface_id -- 'header_interface_id'
, mp.ORGANIZATION_CODE -- 'Org'
, rti.source_document_code -- 'Type'
, pha.segment1 -- 'PO Number'
, pla.line_num -- 'PO Line'
, to_char(rti.transaction_date, 'dd-MON-yyyy') -- 'Transaction Date'
, rti.transaction_type
, rti.PROCESSING_MODE_CODE
, rti.processing_status_code
, rhi.processing_status_code
, to_char(rti.creation_date, 'dd-MON-yyyy') creation_date
, rti.quantity
, msi.segment1 -- 'Item'
, msi.item_type -- 'Item Type'
, rti.item_description
, poh.error_message
, replace(poh.error_message,chr(10),' ') ,msi.organization_id--,poh.*
--, replace(pot.error_message,chr(10),' ')
FROM po.po_interface_errors poh
, po.po_interface_errors pot
, po.rcv_transactions_interface rti
, po.rcv_headers_interface rhi
, inv.mtl_parameters mp
, po.po_headers_all pha
, po.po_lines_all pla
, inv.mtl_system_items_b msi
WHERE 1=1
AND rti.header_interface_id = rhi.header_interface_id (+)
AND rti.interface_transaction_id = pot.interface_transaction_id (+)
AND rhi.header_interface_id = poh.interface_header_id (+)
-- mapping to PO
AND rti.po_header_id = pha.po_header_id AND pha.org_id=8527
AND rti.po_header_id = pla.po_header_id
AND rti.po_line_id = pla.po_line_id
-- mapping to item
AND mp.organization_id = msi.organization_id AND msi.organization_id=106
AND rti.item_id = msi.inventory_item_id
and pha.segment1='1008007890' --in ('20231192' ,'20230863')-- PUT PO NUMBER HERE
--AND pla.line_num IN (14,57)
Order by mp.ORGANIZATION_CODE, pha.segment1, pla.line_num
No comments:
Post a Comment