PO Country of Origin (By Item)

Description
Categories: BI Publisher
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
Title
 
Items From
 
Items To
 
Category From
 
Category To
 
Ship To Organization
 
LOV Oracle
Date From
 
Date
Date To
 
Date
Country of Origin
 
LOV Oracle
P_CAT_STRUCT_NUM
 
Number