PO Receipt Adjustments

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Receipt Adjustments Report (XML)
Short Name: POXRVRTN_XML
DB package: PO_POXRVRTN_XMLP_PKG
SELECT  decode(:P_sort,'VENDOR', pov.vendor_name||', '||pvs.vendor_site_code 
			        ,1)		c_sort
,pll.line_location_id             Parent_Line_location_id
,      'PO'                             Join_Document_Type
,      plc.displayed_field              Source_Type
,      pov.vendor_name||', '||pvs.vendor_site_code             
                                        Source
,      null                      C_FLEX_CAT
,      null                     C_FLEX_ITEM
,      poh.segment1                     PO_Number
,      por.release_num                 release_number
,      T.type_name                     Document_Type
,      lot.location_code                Ship_To_Location
,      pol.line_num                     Line
,      pll.need_by_date                 Need_By_Date
,      plt.line_type                    Line_Type
,      pol.item_description             Description
,      poh.currency_code                currency
,      pol.item_revision                rev
,      pol.unit_meas_lookup_code        unit
,     decode(nvl(pol.matching_basis,'QUANTITY'),'AMOUNT', nvl(pll.amount,0),
                            round(nvl(pll.quantity,0),:P_qty_precision))           Quantity_ordered
,      decode(psp.manual_po_num_type,'NUMERIC',null,poh.segment1)
                                        Document_Numbering1
,      decode(psp.manual_po_num_type,'NUMERIC',poh.segment1,null)
                                        Document_Numbering2
,      ood.organization_name        Organization_name, 
	PO_POXRVRTN_XMLP_PKG.document_numberformula(por.release_num, poh.segment1) Document_Number, 
	PO_POXRVRTN_XMLP_PKG.c_qty_net_rcvdformula(:C_qty_received, :C_qty_corrected, :C_qty_rtv, :C_qty_corrected_rtv) C_qty_net_rcvd, 
	PO_POXRVRTN_XMLP_PKG.c_qty_rtv_and_correctedformula(:C_qty_rtv, :C_qty_corrected_rtv) C_qty_rtv_and_corrected, 
	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
from   po_vendors                       pov
,      po_vendor_sites_all              pvs          
,      po_lines_all                          pol            
,      mtl_system_items                 msi
,      hr_locations_all_tl              lot
,      po_line_types                    plt
,      po_line_locations_all           pll              
,      po_releases_all                  por             
,      po_headers                       poh
,      mtl_categories                   mca
,      po_lookup_codes                  plc
,      PO_DOCUMENT_TYPES_ALL_TL         T
,      PO_DOCUMENT_TYPES_ALL_B          B 
,      po_system_parameters_all             psp 
,      per_all_people_f                 papf
,      rcv_transactions                    rct
,      rcv_transactions                    rct1
,       org_organization_definitions ood
where  POH.ORG_ID = PVS.ORG_ID    
AND  POH.ORG_ID = PSP.ORG_ID       
and    rct.po_line_location_id = pll.line_location_id
and    rct1.po_line_location_id = pll.line_location_id
and    poh.po_header_id           = pol.po_header_id
and    pol.line_type_id           = plt.line_type_id
and    pol.po_line_id             = pll.po_line_id
and    pll.po_release_id  = por.po_release_id (+)
and    pol.item_id                = msi.inventory_item_id (+)   
and    pol.category_id            = mca.category_id
and    nvl(msi.organization_id,:organization_id)        = :organization_id
and    lot.language(+) = userenv('LANG')
and    pll.ship_to_location_id    = lot.location_id (+)
AND   pll.ship_to_location_id is not null
and    poh.vendor_id              = pov.vendor_id
and    poh.vendor_site_id         = pvs.vendor_site_id
and    plc.lookup_type            = 'REQUISITION SOURCE TYPE'
and    plc.lookup_code            = 'VENDOR'
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       = T.document_subtype
and    B.document_type_code     in ('PO', 'PA')
AND      (rct.transaction_type = 'RETURN TO VENDOR'
OR         (rct.transaction_type = 'CORRECT' AND rct1.transaction_type in  ('RECEIVE','MATCH'))
OR         (rct.transaction_type = 'CORRECT' AND rct1.transaction_type = 'RETURN TO VENDOR'))
and rct.parent_transaction_id = rct1.transaction_id
and    pvs.vendor_site_code       = nvl(:p_site,pvs.vendor_site_code)
--and    lot.location_code (+)         = nvl(:p_ship_to,lot.location_code(+))
and    (lot.location_code = :p_ship_to  or  :p_ship_to is null)
and    pov.vendor_name    between   nvl(:p_vendor_from,pov.vendor_name)
                              and   nvl(:p_vendor_to,pov.vendor_name)
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.transaction_date BETWEEN 
                                               nvl( :P_TRANS_DATE_FROM_date, rct.transaction_date)
                                               AND nvl( :P_trans_date_to_date, rct.transaction_date)
                       AND      nvl(papf.full_name,'A')  = nvl(:P_receiver,nvl(papf.full_name,'A'))
AND   :P_customer_from is null
AND   :P_customer_to is null
AND    rct.organization_id = nvl(:P_org_id,rct.organization_id)
AND    ood.organization_id = rct.organization_id
UNION
SELECT decode(:P_sort,'VENDOR', hou.name || ', ' || prl.source_subinventory 
			        ,1)		c_sort 
,prl.requisition_line_id             Parent_Line_location_id
,      'REQ'                             Join_Document_Type
,      plc.displayed_field              Source_Type
,      hou.name || ', ' || prl.source_subinventory             
                                        Source
,      null                      C_FLEX_CAT
,      null                     C_FLEX_ITEM
,      prh.segment1                     PO_Number
,      to_number('')                 release_number
,      T.type_name                    Document_Type
,      lot.location_code                Ship_To_Location
,      prl.line_num                     Line
,      prl.need_by_date                 Need_By_Date
,      plt.line_type                    Line_Type
,      prl.item_description             Description
,      prl.currency_code                currency
,      prl.item_revision                rev
,      prl.unit_meas_lookup_code        unit
,      round(nvl(prl.quantity,0),:P_qty_precision)           Quantity_ordered
,      decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment1)
                                        Document_Numbering1
,      decode(psp.manual_req_num_type,'NUMERIC',prh.segment1,null)
                                        Document_Numbering2
,      ood.organization_name        Organization_name, 
	PO_POXRVRTN_XMLP_PKG.document_numberformula(null, prh.segment1) Document_Number, 
	PO_POXRVRTN_XMLP_PKG.c_qty_net_rcvdformula(:C_qty_received, :C_qty_corrected, :C_qty_rtv, :C_qty_corrected_rtv) C_qty_net_rcvd, 
	PO_POXRVRTN_XMLP_PKG.c_qty_rtv_and_correctedformula(:C_qty_rtv, :C_qty_corrected_rtv) C_qty_rtv_and_corrected, 
	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
from   po_requisition_lines_all                         prl 
,       hr_organization_units          hou
,      mtl_system_items                 msi
,      hr_locations_all_tl              lot
,      po_line_types                    plt
,      rcv_transactions                 rct
,      rcv_transactions                rct1
,      po_requisition_headers                       prh
,      mtl_categories                   mca
,      po_lookup_codes                  plc
,      PO_DOCUMENT_TYPES_ALL_TL         T
,      PO_DOCUMENT_TYPES_ALL_B          B 
,      po_system_parameters_all             psp     
,      per_all_people_f                 papf
,       org_organization_definitions ood
where  PRH.ORG_ID = PSP.ORG_ID       
AND   rct.requisition_line_id    = prl.requisition_line_id
and    rct1.requisition_line_id    = prl.requisition_line_id
and    prh.requisition_header_id           = prl.requisition_header_id
and    prl.line_type_id           = plt.line_type_id
and    prl.item_id                = msi.inventory_item_id (+)   
and    prl.category_id            = mca.category_id
and    nvl(msi.organization_id,:organization_id)        = :organization_id
and    lot.language(+) = userenv('LANG')
and    prl.deliver_to_location_id    = lot.location_id (+)
and    prl.deliver_to_location_id is not null
and    prl.source_organization_id = hou.organization_id
and    plc.lookup_type            = 'REQUISITION SOURCE TYPE'
and    plc.lookup_code            = 'VENDOR'
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    prh.type_lookup_code       = T.document_subtype
and    B.document_type_code     = 'REQ'
AND      (rct.transaction_type = 'RETURN TO VENDOR'
OR         (rct.transaction_type = 'CORRECT' AND rct1.transaction_type = 'RECEIVE')
OR         (rct.transaction_type = 'CORRECT' AND rct1.transaction_type = 'RETURN TO VENDOR'))
and rct.parent_transaction_id = rct1.transaction_id
--and    lot.location_code(+)          = nvl(:p_ship_to,lot.location_code(+))
and    (lot.location_code = :p_ship_to  or  :p_ship_to is null)
and    rct.transaction_date between 
       nvl(:P_trans_date_from,rct.transaction_date)
       and nvl(:P_trans_date_to,rct.transaction_date)
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    nvl(papf.full_name, 'A') = nvl(:P_receiver, nvl(papf.full_name,'A'))
and      :P_site is null
and       :P_vendor_from is null
and       :P_vendor_to is null
and       :P_customer_from is null
and      :P_customer_to is null
AND    rct.organization_id = nvl(:P_org_id,rct.organization_id)
AND    ood.organization_id = rct.organization_id
UNION
SELECT  decode(:P_sort,'VENDOR', oev.name, 1)		c_sort
,      oepo.oe_order_line_id             Parent_Line_location_id
,      'RMA'                             Join_Document_Type
,      plc.displayed_field              Source_Type
,      oev.name    Source
,      null                      C_FLEX_CAT
,      null                     C_FLEX_ITEM
,      to_char(oepo.oe_order_num)                     PO_Number
,      to_number(null)                 release_number
,      to_char(null)                    Document_Type
,      lot.location_code                Ship_To_Location
,      oepo.oe_order_line_num                     Line
,      oepo.need_by_date                 Need_By_Date
,      to_char(null)                    Line_Type
,      oepo.item_description             Description
,      oepo.currency_code                currency
,      oepo.item_revision                rev
,      oepo.ordered_uom        unit
,      round(nvl(oepo.ordered_qty,0), :P_qty_precision)           Quantity_ordered
,      to_char(oepo.oe_order_num) Document_Numbering1
,      to_char(oepo.oe_order_num) Document_Numbering2
,      ood.organization_name        Organization_name, 
	PO_POXRVRTN_XMLP_PKG.document_numberformula(null, oepo.oe_order_num) Document_Number, 
	PO_POXRVRTN_XMLP_PKG.c_qty_net_rcvdformula(:C_qty_received, :C_qty_corrected, :C_qty_rtv, :C_qty_corrected_rtv) C_qty_net_rcvd, 
	PO_POXRVRTN_XMLP_PKG.c_qty_rtv_and_correctedformula(:C_qty_rtv, :C_qty_corrected_rtv) C_qty_rtv_and_corrected, 
	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
from   	oe_sold_to_orgs_v			oev
,	oe_po_enter_receipts_v		oepo
,      mtl_system_items                 msi
,      hr_locations_all_tl              lot
,      mtl_categories                   mca
,      po_lookup_codes                  plc
,      per_all_people_f                 papf
,      rcv_transactions                    rct
,      rcv_transactions                    rct1
,       org_organization_definitions ood
where  rct.oe_order_line_id = oepo.oe_order_line_id
and    rct1.oe_order_line_id = oepo.oe_order_line_id
and    oepo.item_id                = msi.inventory_item_id (+)   
and    oepo.item_category_id            = mca.category_id (+)
and    nvl(msi.organization_id,:organization_id)        = :organization_id
and    lot.language(+) = userenv('LANG')
and    oepo.ship_to_location_id    = lot.location_id(+)
and     oepo.ship_to_location_id is not null
and    oepo.customer_id              = oev.customer_id
and    plc.lookup_type            = 'SHIPMENT SOURCE TYPE'
and    plc.lookup_code            = 'CUSTOMER'
AND      (rct.transaction_type = 'RETURN TO CUSTOMER'
OR         (rct.transaction_type = 'CORRECT' AND rct1.transaction_type = 'RECEIVE')
OR         (rct.transaction_type = 'CORRECT' AND rct1.transaction_type = 'RETURN TO CUSTOMER'))
and    rct.parent_transaction_id = rct1.transaction_id
--and    lot.location_code(+)          = nvl(:p_ship_to,lot.location_code(+))
and    (lot.location_code = :p_ship_to  or  :p_ship_to is null)
and    oev.name    between   nvl(:p_customer_from,oev.name)
                              and   nvl(:p_customer_to,oev.name)
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.transaction_date BETWEEN 
                                               nvl(:P_trans_date_from,rct.transaction_date)
                                               AND nvl(:P_trans_date_to,rct.transaction_date)
                       AND      nvl(papf.full_name,'A')  = nvl(:P_receiver,nvl(papf.full_name,'A'))
AND   :P_site is null
AND   :P_vendor_from is null
AND   :P_vendor_to is null
AND    rct.organization_id = nvl(:P_org_id,rct.organization_id)
AND    ood.organization_id = rct.organization_id
ORDER BY &orderby_clause
Parameter Name SQL text Validation
Category Structure
 
Number
To
 
LOV Oracle
Customer From
 
LOV Oracle
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