PO Country of Origin (By Item)

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Country of Origin (By Item) Report (XML)
Short Name: POXCORIT_XML
DB package: PO_POXCORIT_XMLP_PKG
select
 distinct rsl.country_of_origin_code  rsl_country_code
, ft2.eu_code rsl_eu_country_code
, poll.country_of_origin_code poll_country_code
, ft1.eu_code poll_eu_country_code
, pov.vendor_name supplier
, povs.vendor_site_code supplier_site
, null C_ITEM_CAT
, null C_ITEM_NUM
, pol.item_description description
, poh.segment1 po_num
, pol.line_num po_line_num
, poll.shipment_num po_shipment_num
, rsh.receipt_num
, poh.org_id c_using_org_id
, pov.vendor_id c_vendor_id
, povs.vendor_site_id c_vendor_site_id
, pol.item_id c_item_id
, msi.primary_unit_of_measure c_po_uom
, ood.organization_name c_ship_to_org
, rsl.shipment_line_id rsl_line_id, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CF_ITEM_CAT_DISP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item_num_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CF_ITEM_NUM_DISP, 
	PO_POXCORIT_XMLP_PKG.cf_vendor_item_numberformula(poh.org_id, pov.vendor_id, povs.vendor_site_id, pol.item_id) CF_VENDOR_ITEM_NUMBER
from
      po_line_locations poll
    , po_lines pol
    , po_headers poh
    , mtl_system_items msi
    , mtl_categories mca
    , po_vendors pov
    , po_vendor_sites povs
    , rcv_shipment_headers rsh
    , rcv_shipment_lines rsl
    , fnd_territories ft1
    , fnd_territories ft2
    , org_organization_definitions ood
where poll.country_of_origin_code IS NOT NULL
  AND poll.approved_flag='Y'
  AND poll.po_line_id=pol.po_line_id
  AND poll.po_header_id=poh.po_header_id
  AND poh.vendor_id=pov.vendor_id
  AND poh.vendor_site_id=povs.vendor_site_id
  AND pol.item_id=msi.inventory_item_id(+)
  AND poll.ship_to_organization_id = msi.organization_id
  AND pol.category_id = mca.category_id(+)
  AND poll.line_location_id=rsl.po_line_location_id
  AND rsl.shipment_header_id=rsh.shipment_header_id
  AND poll.country_of_origin_code = ft1.territory_code (+)
  AND rsl.country_of_origin_code = ft2.territory_code (+)
  AND ood.organization_id = poll.ship_to_organization_id
  AND poll.ship_to_organization_id = nvl(:p_org_id,poll.ship_to_organization_id)
  AND &p_query_where_country_code
  AND &p_query_where_cat
  AND &p_query_where_item
Parameter Name SQL text Validation
P_CAT_STRUCT_NUM
 
Number
Country of Origin
 
LOV Oracle
Date To
 
Date
Date From
 
Date
Ship To Organization
 
LOV Oracle
Category To
 
Category From
 
Items To
 
Items From
 
Title