Friday, July 29, 2016

Query to check RTP stuck Records in R12



 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

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...