PO Receiving Exceptions

SELECT   pll.line_location_id                    Parent_Line_location_id
,        pov.vendor_name                         Vendor
,        pvs.vendor_site_code                    Vendor_Site
,        null                             C_FLEX_CAT
,        null                            C_FLEX_ITEM
,          poh.segment1||decode(por.release_num,null,null,'-'||por.release_num)                          PO_Number
,        lot.location_code                       Ship_To_Location
,        pol.line_num                            Line
,        TO_CHAR(pll.need_by_date, 'DD/MON/YYYY HH24:MI:SS')                        Need_By_Date
,        plt.line_type                           Line_Type
,        pol.item_description                    Description
,        round(pll.quantity,:P_qty_precision)    C_QUANTITY             
,        poh.currency_code                       C_CURRENCY
,        pol.item_revision                       Rev
,        pol.unit_meas_lookup_code               Unit
,        T.type_name                           Document_Type
,        'PO'                                             Parent_Join_type
,        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, 
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP, 
	--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
	  decode(pol.category_id,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')) C_FLEX_CAT_DISP,           -- Bug20005085	
	decode(msi.inventory_item_id,null,null,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   -- Bug20005085	
FROM     hr_locations_all_tl                 lot
,        po_vendor_sites_all              pvs        
,        po_vendors                   pov
,        po_line_locations_all            pll           
,        po_lines_all                     pol              
,        po_line_types                plt
,        mtl_system_items             msi
,        mtl_categories               mca
,        po_headers                   poh
,        PO_DOCUMENT_TYPES_ALL_TL    T
,        PO_DOCUMENT_TYPES_ALL_B      B 
,        po_system_parameters_all         psp 
,        po_releases_all                   por         
WHERE poh.org_id = pvs.org_id       
AND      poh.org_id = psp.org_id       
AND      poh.po_header_id             = pol.po_header_id
AND      B.document_type_code       in ('PO', 'PA')
AND      B.document_subtype         = poh.type_lookup_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      mca.category_id              = pol.category_id
AND      msi.inventory_item_id (+)    = pol.item_id
AND      :organization_id          = nvl(msi.organization_id,:organization_id ) 
AND      pol.line_type_id             = plt.line_type_id
AND      pol.po_line_id               = pll.po_line_id
AND      pll.ship_to_location_id      = lot.location_id (+)
AND      pll.ship_to_location_id is not null
AND      LOT.LANGUAGE (+) = USERENV('LANG')
AND      pll.po_release_id       = por.po_release_id (+)
AND      poh.vendor_id                = pov.vendor_id
AND      poh.vendor_site_id           = pvs.vendor_site_id
AND      pov.vendor_name              BETWEEN nvl(:P_vendor_from,pov.vendor_name)
                                      AND     nvl(:P_vendor_to,pov.vendor_name)
AND      pvs.vendor_site_code         =  nvl(:P_site, pvs.vendor_site_code)
AND      lot.location_code (+)           =  nvl(:P_ship_to ,lot.location_code (+))
AND      EXISTS (SELECT 'x' 
                 FROM    rcv_transactions           rct,
                                per_all_people_f           papf
                 WHERE   rct.po_line_location_id    = pll.line_location_id
                 AND     rct.receipt_exception_flag = 'Y' 
                 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.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     nvl(papf.full_name (+), 'A')    = nvl(:P_receiver, nvl(papf.full_name (+),'A')) 
                   AND rct.organization_id = nvl(:P_org_id,rct.organization_id)
                )  
UNION
SELECT   prl.requisition_line_id                 Parent_Line_location_id
,        null                                    Vendor
,        null                                    Vendor_Site
,        null                             C_FLEX_CAT
,        null                            C_FLEX_ITEM
,        prh.segment1                            PO_Number
,        lot.location_code                       Ship_To_Location
,        prl.line_num                            Line
,        TO_CHAR(prl.need_by_date, 'DD/MON/YYYY HH24:MI:SS')                        Need_By_Date
,        plt.line_type                           Line_Type
,        prl.item_description                    Description
,        round(prl.quantity,:P_qty_precision)    C_QUANTITY             
,        :GL_CURRENCY                      C_CURRENCY
,        prl.item_revision                       Rev
,        prl.unit_meas_lookup_code               Unit
,        T.type_name                           Document_Type
,        'REQ'                                           Parent_Join_type
,        decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment1) Document_Numbering1
,	 decode(psp.manual_req_num_type,'NUMERIC',to_number(prh.segment1),null) Document_Numbering2
, --	 fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP
--,	 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
  decode(prl.category_id,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')) C_FLEX_CAT_DISP,           -- Bug20005085
	decode(msi.inventory_item_id,null,null,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   -- Bug20005085	
FROM     hr_locations_all_tl                 lot
,        po_requisition_lines_all         prl    
,        po_requisition_headers       prh
,        po_line_types                plt
,        mtl_system_items             msi
,        mtl_categories               mca
,        PO_DOCUMENT_TYPES_ALL_TL    T
,        PO_DOCUMENT_TYPES_ALL_B     B 
,        po_system_parameters_all         psp 
WHERE  prh.org_id = psp.org_id           
AND      prh.requisition_header_id    = prl.requisition_header_id
AND      B.document_type_code       = 'REQUISITION'
AND      B.document_subtype         = prh.type_lookup_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 = PRH.ORG_ID
AND T.LANGUAGE = USERENV('LANG')
AND      mca.category_id              = prl.category_id
AND      msi.inventory_item_id (+)    = prl.item_id
AND      :organization_id          = nvl(msi.organization_id,:organization_id ) 
AND      prl.line_type_id             = plt.line_type_id
AND      prl.deliver_to_location_id   = lot.location_id (+)
AND      prl.deliver_to_location_id is not null
AND      LOT.LANGUAGE (+) = USERENV('LANG')
AND      lot.location_code  (+)          =  nvl(:P_ship_to ,lot.location_code (+) )                                            
AND      EXISTS (SELECT 'x' 
                 FROM    rcv_transactions           rct,
                                per_all_people_f           papf
                 WHERE   rct.requisition_line_id    = prl.requisition_line_id
                 AND     rct.receipt_exception_flag = 'Y' 
                 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.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     nvl(papf.full_name (+), 'A')    = nvl(:P_receiver, nvl(papf.full_name (+),'A'))   
                    AND rct.organization_id = nvl(:P_org_id,rct.organization_id)
               )
ORDER BY &orderby_clause