PO Unordered Receipts

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Unordered Receipts Report
Application: Purchasing
Source: Unordered Receipts Report (XML)
Short Name: POXRVRUR_XML
DB package: PO_POXRVRUR_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

SELECT 
decode (rct.source_document_code,  'RMA', oec.name,  pov.vendor_name)  Vendor
,      null                             C_FLEX_ITEM
,      decode(rct.source_document_code, 'RMA', rsl.customer_item_num, rsl.vendor_item_num)     Vendor_Product_Number
,      rsl.item_description                     Description
,       papf.full_name                            Receiver
,      rsh.receipt_num                         Receipt_Number
,      TO_CHAR(rct.transaction_date, 'DD/MON/YYYY HH24:MI:SS')                     Receipt_Date
,      rct.unit_of_measure                      Unit
,      round(rsl.quantity_received,:P_qty_precision)     Quantity_Amount_Received
,      substr(rct.comments,1,240)                             Comments
,      ood.organization_code 		      org_code, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP
FROM   rcv_shipment_lines                       rsl
,      rcv_transactions                         rct
,      rcv_shipment_headers                     rsh
,      per_all_people_f                            papf        
,      po_vendors                               pov
,      mtl_system_items                         msi
,      org_organization_definitions 	ood
,      financials_system_parameters             fsp
,      oe_sold_to_orgs_v oec
,      po_lookup_codes plc
WHERE  rct.shipment_line_id   = rsl.shipment_line_id
AND    rsl.shipment_header_id = rsh.shipment_header_id
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE (+) AND PAPF.EFFECTIVE_END_DATE (+)
AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', 
HR_SECURITY.SHOW_PERSON (PAPF.person_id (+),PAPF.current_applicant_flag (+),PAPF.current_employee_Flag (+),PAPF.current_npw_flag (+),PAPF.employee_number (+),PAPF.applicant_number (+),PAPF.npw_number(+))) = 'TRUE'  
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF.BUSINESS_GROUP_ID (+) ,
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF.BUSINESS_GROUP_ID (+)
AND    rct.employee_id        = papf.person_id (+)
AND    rct.vendor_id          = pov.vendor_id(+)
AND    rct.customer_id      = oec.customer_id(+)
AND    rsl.item_id            = msi.inventory_item_id(+)
AND    nvl(msi.organization_id,:c_organization_id) = :c_organization_id
AND    rct.transaction_date 
       BETWEEN nvl(:P_trans_date_from,rct.transaction_date - 1)
       AND     nvl(:P_trans_date_to,rct.transaction_date + 1)
AND    rct.substitute_unordered_code = 'UNORDERED' 
AND    rct.transaction_type          = 'UNORDERED'
AND    rct.organization_id = nvl(:P_org_id,rct.organization_id)
AND    rct.organization_id = ood.organization_id
 AND   rsl.quantity_received > 0
AND   rct.source_document_code = nvl(:P_RECEIPT_SOURCE,  rct.source_document_code)
AND   rsh.receipt_source_code = plc.lookup_code
AND   plc.lookup_type = 'SHIPMENT SOURCE TYPE'
AND   plc.displayed_field = :P_RECEIPT_SOURCE_CODE
AND    NOT EXISTS
	(SELECT NULL
	FROM rcv_transactions rct1
	WHERE rct1.transaction_type  = 'MATCH'
	AND rct1.parent_transaction_id = rct.transaction_id)
ORDER BY pov.vendor_name, oec.name
Parameter Name SQL text Validation
Operating Unit
 
LOV
Title
 
Char
Transaction Dates From
 
DateTime
To
 
DateTime
Organization Name
 
LOV Oracle
Dynamic Precision Option
 
LOV Oracle
Receipt Source Type
 
LOV Oracle