PO Country of Origin (By Supplier)

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Country of Origin (By Supplier) Report (XML)
Short Name: POXCORSL_XML
DB package: PO_POXCORSL_XMLP_PKG
select
      pov.vendor_name C_VENDOR_NAME
     , pvs.vendor_site_code C_VENDOR_SITE
     , null C_FLEX_ITEM
     , msi.description C_ITEM_DESCRIPTION
     , msi.list_price_per_unit C_PRICE
     , paa.country_of_origin_code C_COOC
     , ft.eu_code       C_EU_COOC
     , pasl.primary_vendor_item C_VENDOR_ITEM_NUMBER
     , pas.status_description C_ASL_STATUS_DESC
     , paa.purchasing_unit_of_measure C_UOM, 
	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_asl_attributes paa
   , mtl_system_items msi
   , po_vendors pov
   , po_vendor_sites pvs
   , financials_system_parameters fsp
   , po_approved_supplier_list pasl
   , po_asl_statuses pas
   , fnd_territories ft
where paa.country_of_origin_code IS NOT NULL
  AND paa.item_id=msi.inventory_item_id(+)
  AND msi.organization_id=fsp.inventory_organization_id
  AND paa.vendor_id=pov.vendor_id
  AND pov.vendor_name between nvl(:p_vendor_from, pov.vendor_name) 
                                                 and nvl(:p_vendor_to, pov.vendor_name)
  AND paa.vendor_site_id=pvs.vendor_site_id
  AND pvs.vendor_site_code=nvl(:p_vendor_site,pvs.vendor_site_code)
  AND ((paa.using_organization_id = -1 and not exists
                (SELECT *
                 FROM   po_asl_attributes_val_v paa2
                 WHERE  paa2.using_organization_id = :p_ship_to
                 AND    paa2.vendor_id = paa.vendor_id
                 AND    paa2.vendor_site_id = paa.vendor_site_id
                 AND    paa2.item_id = paa.item_id ))
           OR
           (paa.using_organization_id =:p_ship_to))
  AND paa.asl_id = pasl.asl_id
  AND pasl.asl_status_id = pas.status_id
  AND paa.country_of_origin_code = ft.territory_code (+)
  AND &P_QUERY_WHERE_COUNTRY_CODE
Parameter Name SQL text Validation
Supplier Site
 
LOV Oracle
Supplier To
 
LOV Oracle
Supplier From
 
LOV Oracle
Country of Origin Code
 
LOV Oracle
Ship To Organization
 
LOV Oracle
Title