PO Receiving Exceptions

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Receiving Exceptions Report (XML)
Short Name: POXRVRER_XML
DB package: PO_POXRVRER_XMLP_PKG
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
Parameter Name SQL text Validation
Category Structure
 
Number
Dynamic Precision Option
 
LOV Oracle
Sort By
 
LOV Oracle
Organization Name
 
LOV Oracle
Supplier Site
 
LOV Oracle
To
 
LOV Oracle
Supplier From
 
LOV Oracle
To
 
DateTime
Receipt Date From
 
DateTime
Receiver
 
LOV Oracle
Receipt Location
 
LOV Oracle
Title