PO Unordered Receipts

➜ Back to Library
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 
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
Download
Related Blitz Reports
PO Receiving Transactions Register PO Receipt Adjustments GL Account Analysis (Distributions) PO Substitute Receipts GL Account Analysis PO Overshipments PO Receiving Transactions Pipeline GL Account Analysis (Drilldown) (with inventory and WIP) CST AP and PO Accrual Reconciliation
Blitz Report™

Blitz Report™ provides multiple benefits: