PO Overshipments

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Overshipments Report (XML)
Short Name: POXRVOVS_XML
DB package: PO_POXRVOVS_XMLP_PKG
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
Parameter Name SQL text Validation
Category Structure
 
Number
Quantity Precision Option
 
LOV Oracle
Sort By
 
LOV Oracle
Organization Name
 
LOV Oracle
Vendor Site
 
LOV Oracle
To
 
LOV Oracle
Vendor From
 
LOV Oracle
To
 
DateTime
Receipt Date From
 
DateTime
Receiver
 
LOV Oracle
Receipt Location
 
LOV Oracle
Title
 
Ask a question