PO Receipt Adjustments

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