PO Substitute Receipts

SELECT     poh.segment1||decode(por.release_num,null,null,'-'||por.release_num)       Document_Number
,      T.type_name          Document_Type
,      pov.vendor_name || decode( pvs.vendor_site_code ,null,null,',' || pvs.vendor_site_code )  Source
,      papf1.full_name          Buyer
,      TO_CHAR(poh.creation_date, 'DD/MON/YYYY HH24:MI:SS')      Creation_Date
,      decode(psp.manual_po_num_type,'NUMERIC',null,poh.segment1)
                              Document_Numbering1
,      decode(psp.manual_po_num_type,'NUMERIC',to_number(poh.segment1),null)
                              Document_Numbering2
,      pol.line_num             Line
,      null         C_FLEX_ITEM_ORD 
,      pol.item_revision        Rev
,      pol.item_description     Description
,      null         C_FLEX_ITEM_SUB
,      rct.po_revision_num      Rev1
,      rsl.item_description     Description1
,      round(rct.quantity,:P_qty_precision)  Received
,      rct.unit_of_measure      Unit
,      papf.full_name            Receiver
,      lot.location_code        Location
,      TO_CHAR(rct.transaction_date, 'DD/MON/YYYY HH24:MI:SS')     TDate
,      rsh.receipt_num          Receipt_Number
,      substr(rct.comments,1,240)             Comments
,      plc.displayed_field      Displayed_Source_Type
,      ood.organization_code            Org, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_ord_disp', 'INV', 'MSTK', 101, MSI1.ORGANIZATION_ID, MSI1.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_ORD_DISP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_sub_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_SUB_DISP
FROM   mtl_system_items              msi
,      mtl_system_items              msi1
,      PER_ALL_PEOPLE_F PAPF
,      PER_ALL_PEOPLE_F PAPF1
,      hr_locations_all_tl          lot
,      po_lines_all                      pol                 
,      po_lookup_codes               plc
,      PO_DOCUMENT_TYPES_ALL_TL     T
,      PO_DOCUMENT_TYPES_ALL_B      B 
,      po_system_parameters_all psp              
,      po_releases_all      por                           
,      po_vendor_sites_all    pvs                     
,      po_vendors         pov
,      po_headers         poh
,      rcv_shipment_lines            rsl
,      rcv_shipment_headers          rsh
,      rcv_transactions              rct
,      org_organization_definitions ood
WHERE  poh.org_id = pvs.org_id           
AND    poh.org_id = psp.org_id              
AND    poh.vendor_id         = pov.vendor_id
AND    poh.vendor_site_id  = pvs.vendor_site_id (+)
AND PAPF1.BUSINESS_GROUP_ID = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0) 
FROM FINANCIALS_SYSTEM_PARAMETERS FSP) 
AND TRUNC(SYSDATE) BETWEEN PAPF1.EFFECTIVE_START_DATE  AND PAPF1.EFFECTIVE_END_DATE 
AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', 
HR_SECURITY.SHOW_PERSON (PAPF1.person_id ,PAPF1.current_applicant_flag ,PAPF1.current_employee_Flag ,PAPF1.current_npw_flag ,PAPF1.employee_number ,PAPF1.applicant_number ,PAPF1.npw_number)) = 'TRUE'  
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF1.BUSINESS_GROUP_ID ,
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF1.BUSINESS_GROUP_ID 
AND    poh.agent_id          = PAPF1.person_id
AND    rct.po_header_id                          = poh.po_header_id
AND    rct.po_line_id                            = pol.po_line_id
AND    rct.po_release_id                          =  por.po_release_id (+)
AND    rct.shipment_line_id                      = rsl.shipment_line_id
AND    rct.shipment_header_id                = rsh.shipment_header_id
AND    rct.substitute_unordered_code             = 'SUBSTITUTE' 
AND    rct.transaction_type                      = 'RECEIVE'
AND    rsl.item_id                               = msi.inventory_item_id(+)
AND    pol.item_id                               = msi1.inventory_item_id(+)
AND    nvl(msi.organization_id,:c_organization_id) = :c_organization_id
AND    nvl(msi1.organization_id,:c_organization_id) = :c_organization_id
AND    rct.employee_id                           = papf.person_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.location_id                           = LOT.location_id(+)
AND    LOT.LANGUAGE(+) = USERENV('LANG')
AND    plc.lookup_type                           = 'SHIPMENT SOURCE TYPE'
AND    plc.lookup_code                           = rsh.receipt_source_code
AND    B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE 
AND    B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE 
AND    B.ORG_ID = T.ORG_ID    
AND    B.ORG_ID = POH.ORG_ID  
AND    T.LANGUAGE = USERENV('LANG')
AND    poh.type_lookup_code  = B.document_subtype
AND   B.document_type_code in ('PO', 'PA')
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.organization_id = nvl(:P_org_id,rct.organization_id)
AND   ood.organization_id = rct.organization_id
ORDER BY Document_Number
,	DISPLAYED_SOURCE_TYPE
,	BUYER
,	CREATION_DATE
,	DOCUMENT_TYPE
,	SOURCE
,	DOCUMENT_NUMBERING1
,	DOCUMENT_NUMBERING2
,	ORG
,	LINE