PO Overshipments

SELECT pll.line_location_id             Parent_Line_location_id
,      plc.displayed_field              Source_Type
,      pov.vendor_name||', '||pvs.vendor_site_code             
                                        Source
,      null                      C_FLEX_CAT
,      null                     C_FLEX_ITEM
,      poh.segment1                     Document_Number
,      T.type_name                    Document_Type
,      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 - nvl(pll.quantity_cancelled,0)) ,:P_qty_precision)                     Quantity_Amount_Ordered
,      round(pll.quantity_received,:P_qty_precision)            Quantity_Amount_Received
,      round(-1 * (nvl(pll.quantity,0) - nvl(pll.quantity_cancelled,0) - 
        nvl(pll.quantity_received,0)),:P_qty_precision)   Quantity_amount_remaining
,      round(pll.quantity_cancelled,:P_qty_precision)           Quantity_amount_cancelled
,      poh.currency_code                currency
,      pol.item_revision                rev
,      pol.unit_meas_lookup_code        unit
,      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, 
	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,
	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
from   po_vendors                       pov
,      po_vendor_sites_all                  pvs         
,      po_lines_all                         pol                
,      mtl_system_items                 msi
,      financials_system_params_all     fsp       
,      hr_locations_all_tl            lot
,      po_line_types                    plt
,      po_line_locations                pll
,      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    
where  poh.org_id = pvs.org_id                       
AND poh.org_id = fsp.org_id                           
AND poh.org_id = psp.org_id                          
and poh.po_header_id           = pol.po_header_id
and    pol.line_type_id           = plt.line_type_id
and    plt.matching_basis <> 'AMOUNT'               
and    pol.po_line_id             = pll.po_line_id
and    pol.item_id                = msi.inventory_item_id (+)
and    pol.category_id            = mca.category_id
and    nvl(msi.organization_id,fsp.inventory_organization_id) =
                                  fsp.inventory_organization_id 
and    msi.organization_id (+)    = :organization_id
and    pll.ship_to_location_id    = lot.location_id (+)
and    LOT.LANGUAGE (+) = USERENV('LANG')
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       = B.document_subtype
and    B.document_type_code     in ('PO', 'PA')
and &P_WHERE_VD_SITE
and &P_WHERE_ST
and &P_WHERE_VF
and &P_WHERE_VT
and    nvl(pll.quantity,0) - nvl(pll.quantity_cancelled,0) <
                                   nvl(pll.quantity_received,0)
and exists (select 'receipt exists'
            from   rcv_transactions           rct
            ,      rcv_shipment_headers     rsh
            ,      per_all_people_f                    ppf
            where  rct.po_line_location_id = pll.line_location_id
            and      rct.shipment_header_id = rsh.shipment_header_id
            and    rct.transaction_type in ('RECEIVE', 'RETURN TO RECEIVING', 
                                            'CORRECT', 'RETURN TO VENDOR') 
            and &P_WHERE_TXND_FO
			and &P_WHERE_TXND_TO
            and    rsh.employee_id      = ppf.person_id(+)
            and &P_WHERE_RCER
            and &P_WHERE_ORG
             and ppf.business_group_id + 0 = (select nvl(max(fsp.business_group_id),0)
                                          from financials_system_parameters fsp)
                                          and trunc(sysdate) between ppf.effective_start_date
                                          and ppf.effective_end_date)
ORDER BY &orderby_clause