PO Overshipments
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Overshipments Report
Application: Purchasing
Source: Overshipments Report (XML)
Short Name: POXRVOVS_XML
DB package: PO_POXRVOVS_XMLP_PKG
Description: Overshipments Report
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 | |
---|---|---|---|
Operating Unit |
|
LOV | |
Title |
|
Char | |
Receipt Location |
|
LOV Oracle | |
Receiver |
|
LOV Oracle | |
Receipt Date From |
|
DateTime | |
To |
|
DateTime | |
Vendor From |
|
LOV Oracle | |
To 2 |
|
LOV Oracle | |
Vendor Site |
|
LOV Oracle | |
Organization Name |
|
LOV Oracle | |
Sort By |
|
LOV Oracle | |
Quantity Precision Option |
|
LOV Oracle |