PO Country of Origin (By Supplier)

Description
Categories: BI Publisher
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
Title
 
Char
Ship To Organization
 
LOV Oracle
Country of Origin Code
 
LOV Oracle
Supplier From
 
LOV Oracle
Supplier To
 
LOV Oracle
Supplier Site
 
LOV Oracle