PO Approved Supplier List Upload

➜ Back to Library
Description

PO Approved Supplier List Upload creates, updates and deletes Oracle Purchasing Approved Supplier List (ASL) entries – item or commodity to supplier or manufacturer assignments – together with their supplier attributes (purchasing, scheduling, planning, inventory, VMI, consigned), source documents, authorizations, capacities and tolerances, from Excel, via the standard Oracle public API.

When to use it

  • Mass-create new ASL entries linking items or commodities to suppliers or manufacturers.
  • Bulk-maintain ASL supplier attributes (purchasing UOM, release method, scheduling, VMI, consigned, inventory settings) across many ASLs.
  • Define local (organization-specific) attributes against existing global ASLs.
  • Attach or remove source documents (blankets/quotations/contracts) in bulk.
  • Load or update supplier-scheduling authorizations, capacities and tolerances.
  • Disable/enable ASLs or change ASL status en masse, or delete obsolete ASLs.

Before you start

  • Blitz Report is installed and you are signed in to a responsibility with access to the owning/using organizations.
  • The supplier (with a purchasing site) and/or manufacturer exists, and the item or purchasing commodity exists.
  • ASL statuses, bucket patterns, schedulers and purchasing documents referenced are set up.

Step 1 – Choose a mode, set the parameters and download

In Blitz Report, open PO Approved Supplier List Upload, choose an Upload Mode (Create, Update – the default – downloads existing ASLs to edit; Create gives an empty template), and set the parameters:

ParameterPurpose
Upload ModeCreate or Create, Update. Default is Create, Update.
Owning Organization Code / Type / Item / Commodity / Supplier / Manufacturer / StatusRestrict which existing ASLs are downloaded.
Download Authorizations / Capacities / TolerancesInclude these child rows in the download (excluded by default).

Run the upload to download and open the Excel file. Each ASL and child entity is automatically handled as a create or an update depending on whether it already exists.

Step 2 – Enter the ASL entries

On each row choose the Type (Item or Commodity) and the Item or Commodity, the Supplier Business Type (Direct / Manufacturer / Distributor) and the Supplier or Manufacturer, set Global ASL (Yes for all organizations) and the Status, and fill the supplier attributes, source document, authorization, capacity or tolerance columns as needed.

Step 3 – Delete entries (optional)

Set Delete this ASL to remove an ASL, or Delete this Document / Authorization / Capacity / Tolerance to remove an individual child entity. Manufacturer ASLs cannot be deleted (an Oracle API restriction).

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s validation, then saves the file. Correct anything it flags before continuing.

Step 5 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which creates, updates or deletes each ASL and child entity. When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Created, updated or deleted ASL entries with their attributes, source documents, authorizations, capacities and tolerances.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Do I set the Action column to choose create vs update?
No. The upload auto-detects create vs update from the ASL key (item/commodity, supplier, site, owning and using organization).

How do I add a local attribute set to a global ASL?
Mark Global ASL = Yes and populate the Using Organization; the upload adds local attributes without altering the global ASL.

Can I add a document, authorization, capacity and tolerance for the same ASL in one row?
Yes, on entry. On download they appear in separate rows to reduce duplication.

Why don’t Authorizations/Capacities/Tolerances appear in my download?
They are excluded by default – set Download Authorizations / Capacities / Tolerances to Yes.

Why can’t I delete a Manufacturer ASL?
The standard Oracle API does not support it; the upload blocks it with a clear message.

Troubleshooting

MessageCauseWhat to do
Manufacturer ASLs cannot be deleted using this uploadDelete this ASL = Yes on a Manufacturer business-type ASL.Remove it through the Oracle form; this upload cannot delete Manufacturer ASLs.
Vendor / Manufacturer / Distributor Manufacturer is requiredThe Business Type doesn’t match the supplier fields.Populate the supplier field that matches the chosen Business Type.
An ASL for this Manufacturer does not exist for this Item/CommodityA Distributor ASL references a Manufacturer with no existing ASL.Create the Manufacturer ASL first, then the Distributor ASL.
Document / Authorization / Capacity / Tolerance cannot be deleted – it does not existDelete = Yes for a child entity that isn’t there.Verify the child entity exists before flagging it for deletion.
Multiple suppliers named X exist – populate the Supplier NumberDuplicate supplier/manufacturer names.Populate the Supplier Number to disambiguate.
select
null action_,
null status_,
null message_,
null request_id_,
null modified_columns_,
:p_upload_mode upload_mode_,
null asl_id,
null att_row_id,
null doc_row_id,
null ath_row_id,
null cap_row_id,
null tol_row_id,
asl.*
from
(
--
-- Q1 ASL Supplier Attributes and Documents
--
select
-- Owning Organization
mp_o.organization_code owning_organization_code,
haout_o.name owning_organization,
-- ASL item/commodity
nvl2(pasl.item_id,'Item','Commodity') type,
mck.concatenated_segments commodity,
msiv.concatenated_segments item,
case when pasl.item_id is not null
then msiv.description
else (select mct.description from mtl_categories_tl mct where mct.category_id = pasl.category_id and mct.language = userenv('lang'))
end description,
--
-- ASL Suppliers
xxen_util.meaning(pasl.vendor_business_type,'ASL_VENDOR_BUSINESS_TYPE',201) supplier_business_type,
aps.vendor_name supplier,
aps.segment1 supplier_number,
ass.vendor_site_code supplier_site,
mm.manufacturer_name manufacturer,
(select mm2.manufacturer_name from po_approved_supplier_list pasl2, mtl_manufacturers mm2 where pasl2.manufacturer_id = mm2.manufacturer_id and pasl2.asl_id = pasl.manufacturer_asl_id) distributor_manufacturer,
xxen_util.meaning(decode(pasl.using_organization_id,-1,'Y','N'),'YES_NO',0) global_asl,
(select pas.status from po_asl_statuses pas where pas.status_id = pasl.asl_status_id) status,
xxen_util.meaning(decode(pasl.disable_flag,'Y','Y',null),'YES_NO',0) disabled,
pasl.primary_vendor_item supplier_item,
pasl.review_by_date review_by_date,
pasl.comments,
pasl.creation_date created,
--
-- ASL Supplier Attributes
mp_u.organization_code using_organization_code,
haout_u.name using_organization,
xxen_util.meaning(decode(paa.using_organization_id,-1,'Y',nvl2(paa.using_organization_id,'N',null)),'YES_NO',0) global_attributes,
null delete_this_asl,
paa.purchasing_unit_of_measure purchasing_uom,
xxen_util.meaning(paa.release_generation_method,'DOC GENERATION METHOD',201) release_method,
paa.price_update_tolerance,
paa.country_of_origin_code country_of_origin,
-- Supplier Scheduling Attributes
xxen_util.meaning(decode(paa.enable_plan_schedule_flag,'Y','Y',null),'YES_NO',0) enable_planning_schedules,
xxen_util.meaning(decode(paa.enable_ship_schedule_flag,'Y','Y',null),'YES_NO',0) enable_shipping_schedules,
(select ppx.full_name from per_people_x ppx where paa.scheduler_id=ppx.person_id) scheduler,
(select ppx.employee_number from per_people_x ppx where paa.scheduler_id=ppx.person_id) scheduler_emp_num,
xxen_util.meaning(decode(paa.enable_autoschedule_flag,'Y','Y',null),'YES_NO',0) enable_autoschedule,
(select cbp.bucket_pattern_name from chv_bucket_patterns cbp where cbp.bucket_pattern_id=paa.plan_bucket_pattern_id) plan_bucket_pattern,
(select cbp.bucket_pattern_name from chv_bucket_patterns cbp where cbp.bucket_pattern_id=paa.ship_bucket_pattern_id) ship_bucket_pattern,
xxen_util.meaning(paa.plan_schedule_type,'PLAN_SCHEDULE_SUBTYPE',201) plan_schedule_type,
xxen_util.meaning(paa.ship_schedule_type,'SHIP_SCHEDULE_SUBTYPE',201) ship_schedule_type,
xxen_util.meaning(decode(paa.enable_authorizations_flag,'Y','Y',null),'YES_NO',0) enable_authorizations,
-- Planning Constraints Attributes
paa.delivery_calendar supplier_capacity_calendar,
paa.processing_lead_time,
-- Inventory Attributes
paa.min_order_qty inv_minimum_order_quantity,
paa.fixed_lot_multiple inv_fixed_lot_multiple,
paa.fixed_order_quantity inv_fixed_order_quantity,
-- VMI Attributes
xxen_util.meaning(decode(paa.enable_vmi_flag,'Y','Y',null),'YES_NO',0) vmi_enabled,
xxen_util.meaning(decode(paa.enable_vmi_auto_replenish_flag,'Y','Y',null),'YES_NO',0) vmi_allow_auto_replenishment,
decode(paa.vmi_replenishment_approval,'SUPPLIER_OR_BUYER','Supplier or Buyer',initcap(paa.vmi_replenishment_approval)) vmi_replenishment_approval,
decode(paa.replenishment_method,1,'Min - Max Quantities',2,'Min - Max Days',3,'Min Qty and Fixed Order Qty',4,'Min Days and Fixed Order Qty',paa.replenishment_method) vmi_replenishment_method,
paa.forecast_horizon vmi_forecast_horizon_days,
paa.vmi_min_qty vmi_minimum_quantity,
paa.vmi_max_qty vmi_maximum_quantity,
paa.vmi_min_days vmi_minimum_days,
paa.vmi_max_days vmi_maximum_days,
-- Consigned Attributes
xxen_util.meaning(decode(paa.consigned_from_supplier_flag,'Y','Y',null),'YES_NO',0) consigned_from_supplier,
paa.consigned_billing_cycle consigned_billing_cycle_days,
paa.last_billing_date consigned_last_billing_date,
xxen_util.meaning(decode(paa.consume_on_aging_flag,'Y','Y',null),'YES_NO',0) consigned_consume_on_aging,
paa.aging_period consigned_aging_period_days,
--
-- ASL Supplier Documents
pad.sequence_num source_document_seq,
xxen_util.meaning(pad.document_type_code,'SOURCE DOCUMENT TYPE',201) source_document_type,
pha.segment1 source_document_number,
pla.line_num source_document_line,
null delete_this_document,
xxen_util.meaning(decode(pha.global_agreement_flag,'Y','Y',null),'YES_NO',0) document_global_agreement,
decode(pha.global_agreement_flag,'Y',po_moac_utils_pvt.get_ou_name(pha.org_id)) document_owning_org,
xxen_util.meaning(decode(pha.type_lookup_code,'QUOTATION',pha.status_lookup_code,'BLANKET',pha.authorization_status,'CONTRACT',pha.authorization_status),
                  decode(pha.type_lookup_code, 'QUOTATION','RFQ/QUOTE STATUS','BLANKET','AUTHORIZATION STATUS','CONTRACT','AUTHORIZATION STATUS'),
                  201
) document_status,
pha.start_date document_effective_from,
pha.end_date document_effective_to,
--
-- Authorizations
to_number(null) authorization_seq,
null authorization,
to_number(null) authorization_cutoff_days,
null delete_this_authorization,
--
-- Capacity
to_date(null) capacity_from_date,
to_date(null) capacity_to_date,
to_number(null) capacity_per_day,
null delete_this_capacity,
--
-- Tolerances
to_number(null) tolerance_days_in_advance,
to_number(null) tolerance_percentage,
null delete_this_tolerance,
--
-- ASL DFFs
xxen_util.display_flexfield_context(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category) po_asl_dff_category,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE1',pasl.rowid,pasl.attribute1) po_asl_attribute1,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE2',pasl.rowid,pasl.attribute2) po_asl_attribute2,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE3',pasl.rowid,pasl.attribute3) po_asl_attribute3,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE4',pasl.rowid,pasl.attribute4) po_asl_attribute4,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE5',pasl.rowid,pasl.attribute5) po_asl_attribute5,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE6',pasl.rowid,pasl.attribute6) po_asl_attribute6,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE7',pasl.rowid,pasl.attribute7) po_asl_attribute7,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE8',pasl.rowid,pasl.attribute8) po_asl_attribute8,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE9',pasl.rowid,pasl.attribute9) po_asl_attribute9,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE10',pasl.rowid,pasl.attribute10) po_asl_attribute10,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE11',pasl.rowid,pasl.attribute11) po_asl_attribute11,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE12',pasl.rowid,pasl.attribute12) po_asl_attribute12,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE13',pasl.rowid,pasl.attribute13) po_asl_attribute13,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE14',pasl.rowid,pasl.attribute14) po_asl_attribute14,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE15',pasl.rowid,pasl.attribute15) po_asl_attribute15,
-- Attribute DFFs
xxen_util.display_flexfield_context(201,'PO_ASL_ATTRIBUTES',paa.attribute_category) po_asl_att_dff_category,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE1',paa.rowid,paa.attribute1) po_asl_att_attribute1,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE2',paa.rowid,paa.attribute2) po_asl_att_attribute2,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE3',paa.rowid,paa.attribute3) po_asl_att_attribute3,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE4',paa.rowid,paa.attribute4) po_asl_att_attribute4,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE5',paa.rowid,paa.attribute5) po_asl_att_attribute5,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE6',paa.rowid,paa.attribute6) po_asl_att_attribute6,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE7',paa.rowid,paa.attribute7) po_asl_att_attribute7,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE8',paa.rowid,paa.attribute8) po_asl_att_attribute8,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE9',paa.rowid,paa.attribute9) po_asl_att_attribute9,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE10',paa.rowid,paa.attribute10) po_asl_att_attribute10,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE11',paa.rowid,paa.attribute11) po_asl_att_attribute11,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE12',paa.rowid,paa.attribute12) po_asl_att_attribute12,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE13',paa.rowid,paa.attribute13) po_asl_att_attribute13,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE14',paa.rowid,paa.attribute14) po_asl_att_attribute14,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE15',paa.rowid,paa.attribute15) po_asl_att_attribute15,
-- Document DFFs
xxen_util.display_flexfield_context(201,'PO_ASL_DOCUMENTS',pad.attribute_category) po_asl_doc_dff_category,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE1',pad.rowid,pad.attribute1) po_asl_doc_attribute1,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE2',pad.rowid,pad.attribute2) po_asl_doc_attribute2,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE3',pad.rowid,pad.attribute3) po_asl_doc_attribute3,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE4',pad.rowid,pad.attribute4) po_asl_doc_attribute4,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE5',pad.rowid,pad.attribute5) po_asl_doc_attribute5,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE6',pad.rowid,pad.attribute6) po_asl_doc_attribute6,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE7',pad.rowid,pad.attribute7) po_asl_doc_attribute7,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE8',pad.rowid,pad.attribute8) po_asl_doc_attribute8,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE9',pad.rowid,pad.attribute9) po_asl_doc_attribute9,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE10',pad.rowid,pad.attribute10) po_asl_doc_attribute10,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE11',pad.rowid,pad.attribute11) po_asl_doc_attribute11,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE12',pad.rowid,pad.attribute12) po_asl_doc_attribute12,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE13',pad.rowid,pad.attribute13) po_asl_doc_attribute13,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE14',pad.rowid,pad.attribute14) po_asl_doc_attribute14,
xxen_util.display_flexfield_value(201,'PO_ASL_DOCUMENTS',pad.attribute_category,'ATTRIBUTE15',pad.rowid,pad.attribute15) po_asl_doc_attribute15,
--
-- IDs
pasl.asl_id old_asl_id,
rowidtochar(paa.rowid) old_att_row_id,
rowidtochar(pad.rowid) old_doc_row_id,
null old_ath_row_id,
null old_cap_row_id,
null old_tol_row_id,
to_number(null) upload_row
from
po_approved_supplier_list pasl,
mtl_system_items_vl msiv,
mtl_categories_b_kfv mck,
ap_suppliers aps,
ap_supplier_sites ass,
mtl_manufacturers mm,
mtl_parameters mp_o,
hr_all_organization_units_tl haout_o,
--
po_asl_attributes paa,
mtl_parameters mp_u,
hr_all_organization_units_tl haout_u,
--
po_asl_documents pad,
po_headers_all pha,
po_lines_all pla
where
1=1 and
2=2 and
pasl.owning_organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
pasl.owning_organization_id=msiv.organization_id(+) and
pasl.item_id=msiv.inventory_item_id(+) and
pasl.category_id=mck.category_id(+) and
pasl.vendor_id=aps.vendor_id(+) and
pasl.vendor_site_id=ass.vendor_site_id(+) and
((pasl.vendor_site_id is not null and ass.vendor_site_code is not null) or
 (pasl.vendor_site_id is null and ass.vendor_site_code is null)
) and
pasl.manufacturer_id=mm.manufacturer_id(+) and
pasl.owning_organization_id=mp_o.organization_id and
pasl.owning_organization_id=haout_o.organization_id and
haout_o.language = userenv('lang') and
--
decode(pasl.vendor_business_type,'MANUFACTURER',-1,pasl.asl_id)=paa.asl_id(+) and
paa.using_organization_id=mp_u.organization_id(+) and
paa.using_organization_id=haout_u.organization_id(+) and
haout_u.language(+) = userenv('lang') and
--
paa.asl_id=pad.asl_id(+) and
paa.using_organization_id=pad.using_organization_id(+) and
pad.document_header_id=pha.po_header_id(+) and
pad.document_line_id=pla.po_line_id(+)
--
union all
--
-- Q2 ASL Authorizations
--
select
-- Owning Organization
mp_o.organization_code owning_organization_code,
haout_o.name owning_organization,
-- ASL item/commodity
nvl2(pasl.item_id,'Item','Commodity') type,
mck.concatenated_segments commodity,
msiv.concatenated_segments item,
case when pasl.item_id is not null
then msiv.description
else (select mct.description from mtl_categories_tl mct where mct.category_id = pasl.category_id and mct.language = userenv('lang'))
end description,
--
-- ASL Suppliers
xxen_util.meaning(pasl.vendor_business_type,'ASL_VENDOR_BUSINESS_TYPE',201) supplier_business_type,
aps.vendor_name supplier,
aps.segment1 supplier_number,
ass.vendor_site_code supplier_site,
mm.manufacturer_name manufacturer,
(select mm2.manufacturer_name from po_approved_supplier_list pasl2, mtl_manufacturers mm2 where pasl2.manufacturer_id = mm2.manufacturer_id and pasl2.asl_id = pasl.manufacturer_asl_id) distributor_manufacturer,
xxen_util.meaning(decode(pasl.using_organization_id,-1,'Y','N'),'YES_NO',0) global_asl,
(select pas.status from po_asl_statuses pas where pas.status_id = pasl.asl_status_id) status,
xxen_util.meaning(decode(pasl.disable_flag,'Y','Y',null),'YES_NO',0) disabled,
pasl.primary_vendor_item supplier_item,
pasl.review_by_date review_by_date,
pasl.comments,
pasl.creation_date created,
--
-- ASL Supplier Attributes
mp_u.organization_code using_organization_code,
haout_u.name using_organization,
xxen_util.meaning(decode(paa.using_organization_id,-1,'Y',nvl2(paa.using_organization_id,'N',null)),'YES_NO',0) global_attributes,
null delete_this_asl,
paa.purchasing_unit_of_measure purchasing_uom,
xxen_util.meaning(paa.release_generation_method,'DOC GENERATION METHOD',201) release_method,
paa.price_update_tolerance,
paa.country_of_origin_code country_of_origin,
-- Supplier Scheduling Attributes
xxen_util.meaning(decode(paa.enable_plan_schedule_flag,'Y','Y',null),'YES_NO',0) enable_planning_schedules,
xxen_util.meaning(decode(paa.enable_ship_schedule_flag,'Y','Y',null),'YES_NO',0) enable_shipping_schedules,
(select ppx.full_name from per_people_x ppx where paa.scheduler_id=ppx.person_id) scheduler,
(select ppx.employee_number from per_people_x ppx where paa.scheduler_id=ppx.person_id) scheduler_emp_num,
xxen_util.meaning(decode(paa.enable_autoschedule_flag,'Y','Y',null),'YES_NO',0) enable_autoschedule,
(select cbp.bucket_pattern_name from chv_bucket_patterns cbp where cbp.bucket_pattern_id=paa.plan_bucket_pattern_id) plan_bucket_pattern,
(select cbp.bucket_pattern_name from chv_bucket_patterns cbp where cbp.bucket_pattern_id=paa.ship_bucket_pattern_id) ship_bucket_pattern,
xxen_util.meaning(paa.plan_schedule_type,'PLAN_SCHEDULE_SUBTYPE',201) plan_schedule_type,
xxen_util.meaning(paa.ship_schedule_type,'SHIP_SCHEDULE_SUBTYPE',201) ship_schedule_type,
xxen_util.meaning(decode(paa.enable_authorizations_flag,'Y','Y',null),'YES_NO',0) enable_authorizations,
-- Planning Constraints Attributes
paa.delivery_calendar supplier_capacity_calendar,
paa.processing_lead_time,
-- Inventory Attributes
paa.min_order_qty inv_minimum_order_quantity,
paa.fixed_lot_multiple inv_fixed_lot_multiple,
paa.fixed_order_quantity inv_fixed_order_quantity,
-- VMI Attributes
xxen_util.meaning(decode(paa.enable_vmi_flag,'Y','Y',null),'YES_NO',0) vmi_enabled,
xxen_util.meaning(decode(paa.enable_vmi_auto_replenish_flag,'Y','Y',null),'YES_NO',0) vmi_allow_auto_replenishment,
decode(paa.vmi_replenishment_approval,'SUPPLIER_OR_BUYER','Supplier or Buyer',initcap(paa.vmi_replenishment_approval)) vmi_replenishment_approval,
decode(paa.replenishment_method,1,'Min - Max Quantities',2,'Min - Max Days',3,'Min Qty and Fixed Order Qty',4,'Min Days and Fixed Order Qty',paa.replenishment_method) vmi_replenishment_method,
paa.forecast_horizon vmi_forecast_horizon_days,
paa.vmi_min_qty vmi_minimum_quantity,
paa.vmi_max_qty vmi_maximum_quantity,
paa.vmi_min_days vmi_minimum_days,
paa.vmi_max_days vmi_maximum_days,
-- Consigned Attributes
xxen_util.meaning(decode(paa.consigned_from_supplier_flag,'Y','Y',null),'YES_NO',0) consigned_from_supplier,
paa.consigned_billing_cycle consigned_billing_cycle_days,
paa.last_billing_date consigned_last_billing_date,
xxen_util.meaning(decode(paa.consume_on_aging_flag,'Y','Y',null),'YES_NO',0) consigned_consume_on_aging,
paa.aging_period consigned_aging_period_days,
--
-- ASL Supplier Documents
to_number(null) source_document_seq,
null source_document_type,
null source_document_number,
null source_document_line,
null delete_this_document,
null document_global_agreement,
null document_owning_org,
null document_status,
to_date(null) document_effective_from,
to_date(null) document_effective_to,
--
-- Supplier Authorizations
ca.authorization_sequence authorization_seq,
xxen_util.meaning(ca.authorization_code,'AUTHORIZATION_TYPE',201) authorization,
ca.timefence_days authorization_cutoff_days,
null delete_this_authorization,
--
-- Capacity
to_date(null) capacity_from_date,
to_date(null) capacity_to_date,
to_number(null) capacity_per_day,
null delete_this_capacity,
--
-- Tolerances
to_number(null) tolerance_days_in_advance,
to_number(null) tolerance_percentage,
null delete_this_tolerance,
--
-- ASL DFFs
xxen_util.display_flexfield_context(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category) po_asl_dff_category,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE1',pasl.rowid,pasl.attribute1) po_asl_attribute1,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE2',pasl.rowid,pasl.attribute2) po_asl_attribute2,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE3',pasl.rowid,pasl.attribute3) po_asl_attribute3,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE4',pasl.rowid,pasl.attribute4) po_asl_attribute4,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE5',pasl.rowid,pasl.attribute5) po_asl_attribute5,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE6',pasl.rowid,pasl.attribute6) po_asl_attribute6,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE7',pasl.rowid,pasl.attribute7) po_asl_attribute7,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE8',pasl.rowid,pasl.attribute8) po_asl_attribute8,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE9',pasl.rowid,pasl.attribute9) po_asl_attribute9,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE10',pasl.rowid,pasl.attribute10) po_asl_attribute10,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE11',pasl.rowid,pasl.attribute11) po_asl_attribute11,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE12',pasl.rowid,pasl.attribute12) po_asl_attribute12,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE13',pasl.rowid,pasl.attribute13) po_asl_attribute13,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE14',pasl.rowid,pasl.attribute14) po_asl_attribute14,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE15',pasl.rowid,pasl.attribute15) po_asl_attribute15,
-- Attribute DFFs
xxen_util.display_flexfield_context(201,'PO_ASL_ATTRIBUTES',paa.attribute_category) po_asl_att_dff_category,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE1',paa.rowid,paa.attribute1) po_asl_att_attribute1,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE2',paa.rowid,paa.attribute2) po_asl_att_attribute2,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE3',paa.rowid,paa.attribute3) po_asl_att_attribute3,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE4',paa.rowid,paa.attribute4) po_asl_att_attribute4,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE5',paa.rowid,paa.attribute5) po_asl_att_attribute5,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE6',paa.rowid,paa.attribute6) po_asl_att_attribute6,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE7',paa.rowid,paa.attribute7) po_asl_att_attribute7,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE8',paa.rowid,paa.attribute8) po_asl_att_attribute8,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE9',paa.rowid,paa.attribute9) po_asl_att_attribute9,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE10',paa.rowid,paa.attribute10) po_asl_att_attribute10,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE11',paa.rowid,paa.attribute11) po_asl_att_attribute11,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE12',paa.rowid,paa.attribute12) po_asl_att_attribute12,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE13',paa.rowid,paa.attribute13) po_asl_att_attribute13,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE14',paa.rowid,paa.attribute14) po_asl_att_attribute14,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE15',paa.rowid,paa.attribute15) po_asl_att_attribute15,
-- Document DFFs
null po_asl_doc_dff_category,
null po_asl_doc_attribute1,
null po_asl_doc_attribute2,
null po_asl_doc_attribute3,
null po_asl_doc_attribute4,
null po_asl_doc_attribute5,
null po_asl_doc_attribute6,
null po_asl_doc_attribute7,
null po_asl_doc_attribute8,
null po_asl_doc_attribute9,
null po_asl_doc_attribute10,
null po_asl_doc_attribute11,
null po_asl_doc_attribute12,
null po_asl_doc_attribute13,
null po_asl_doc_attribute14,
null po_asl_doc_attribute15,
--
-- IDs
pasl.asl_id old_asl_id,
null old_att_row_id,
null old_doc_row_id,
rowidtochar(ca.rowid) old_ath_row_id,
null old_cap_row_id,
null old_tol_row_id,
to_number(null) upload_row
from
po_approved_supplier_list pasl,
mtl_system_items_vl msiv,
mtl_categories_b_kfv mck,
ap_suppliers aps,
ap_supplier_sites ass,
mtl_manufacturers mm,
mtl_parameters mp_o,
hr_all_organization_units_tl haout_o,
--
po_asl_attributes paa,
mtl_parameters mp_u,
hr_all_organization_units_tl haout_u,
--
chv_authorizations ca
--
where
:p_download_authorizations = 'Y' and
1=1 and
3=3 and
pasl.vendor_business_type != 'MANUFACTURER' and
pasl.owning_organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
pasl.owning_organization_id=msiv.organization_id(+) and
pasl.item_id=msiv.inventory_item_id(+) and
pasl.category_id=mck.category_id(+) and
pasl.vendor_id=aps.vendor_id(+) and
pasl.vendor_site_id=ass.vendor_site_id(+) and
((pasl.vendor_site_id is not null and ass.vendor_site_code is not null) or
 (pasl.vendor_site_id is null and ass.vendor_site_code is null)
) and
pasl.manufacturer_id=mm.manufacturer_id(+) and
pasl.owning_organization_id=mp_o.organization_id and
pasl.owning_organization_id=haout_o.organization_id and
haout_o.language = userenv('lang') and
--
pasl.asl_id=paa.asl_id and
pasl.using_organization_id = paa.using_organization_id and
paa.using_organization_id=mp_u.organization_id(+) and
paa.using_organization_id=haout_u.organization_id(+) and
haout_u.language(+) = userenv('lang') and
--
pasl.asl_id=ca.reference_id and
pasl.using_organization_id=ca.using_organization_id and
ca.reference_type='ASL'
--
union all
--
-- Q3 ASL Supplier Capacities
--
select
-- Owning Organization
mp_o.organization_code owning_organization_code,
haout_o.name owning_organization,
-- ASL item/commodity
nvl2(pasl.item_id,'Item','Commodity') type,
mck.concatenated_segments commodity,
msiv.concatenated_segments item,
case when pasl.item_id is not null
then msiv.description
else (select mct.description from mtl_categories_tl mct where mct.category_id = pasl.category_id and mct.language = userenv('lang'))
end description,
--
-- ASL Suppliers
xxen_util.meaning(pasl.vendor_business_type,'ASL_VENDOR_BUSINESS_TYPE',201) supplier_business_type,
aps.vendor_name supplier,
aps.segment1 supplier_number,
ass.vendor_site_code supplier_site,
mm.manufacturer_name manufacturer,
(select mm2.manufacturer_name from po_approved_supplier_list pasl2, mtl_manufacturers mm2 where pasl2.manufacturer_id = mm2.manufacturer_id and pasl2.asl_id = pasl.manufacturer_asl_id) distributor_manufacturer,
xxen_util.meaning(decode(pasl.using_organization_id,-1,'Y','N'),'YES_NO',0) global_asl,
(select pas.status from po_asl_statuses pas where pas.status_id = pasl.asl_status_id) status,
xxen_util.meaning(decode(pasl.disable_flag,'Y','Y',null),'YES_NO',0) disabled,
pasl.primary_vendor_item supplier_item,
pasl.review_by_date review_by_date,
pasl.comments,
pasl.creation_date created,
--
-- ASL Supplier Attributes
mp_u.organization_code using_organization_code,
haout_u.name using_organization,
xxen_util.meaning(decode(paa.using_organization_id,-1,'Y',nvl2(paa.using_organization_id,'N',null)),'YES_NO',0) global_attributes,
null delete_this_asl,
paa.purchasing_unit_of_measure purchasing_uom,
xxen_util.meaning(paa.release_generation_method,'DOC GENERATION METHOD',201) release_method,
paa.price_update_tolerance,
paa.country_of_origin_code country_of_origin,
-- Supplier Scheduling Attributes
xxen_util.meaning(decode(paa.enable_plan_schedule_flag,'Y','Y',null),'YES_NO',0) enable_planning_schedules,
xxen_util.meaning(decode(paa.enable_ship_schedule_flag,'Y','Y',null),'YES_NO',0) enable_shipping_schedules,
(select ppx.full_name from per_people_x ppx where paa.scheduler_id=ppx.person_id) scheduler,
(select ppx.employee_number from per_people_x ppx where paa.scheduler_id=ppx.person_id) scheduler_emp_num,
xxen_util.meaning(decode(paa.enable_autoschedule_flag,'Y','Y',null),'YES_NO',0) enable_autoschedule,
(select cbp.bucket_pattern_name from chv_bucket_patterns cbp where cbp.bucket_pattern_id=paa.plan_bucket_pattern_id) plan_bucket_pattern,
(select cbp.bucket_pattern_name from chv_bucket_patterns cbp where cbp.bucket_pattern_id=paa.ship_bucket_pattern_id) ship_bucket_pattern,
xxen_util.meaning(paa.plan_schedule_type,'PLAN_SCHEDULE_SUBTYPE',201) plan_schedule_type,
xxen_util.meaning(paa.ship_schedule_type,'SHIP_SCHEDULE_SUBTYPE',201) ship_schedule_type,
xxen_util.meaning(decode(paa.enable_authorizations_flag,'Y','Y',null),'YES_NO',0) enable_authorizations,
-- Planning Constraints Attributes
paa.delivery_calendar supplier_capacity_calendar,
paa.processing_lead_time,
-- Inventory Attributes
paa.min_order_qty inv_minimum_order_quantity,
paa.fixed_lot_multiple inv_fixed_lot_multiple,
paa.fixed_order_quantity inv_fixed_order_quantity,
-- VMI Attributes
xxen_util.meaning(decode(paa.enable_vmi_flag,'Y','Y',null),'YES_NO',0) vmi_enabled,
xxen_util.meaning(decode(paa.enable_vmi_auto_replenish_flag,'Y','Y',null),'YES_NO',0) vmi_allow_auto_replenishment,
decode(paa.vmi_replenishment_approval,'SUPPLIER_OR_BUYER','Supplier or Buyer',initcap(paa.vmi_replenishment_approval)) vmi_replenishment_approval,
decode(paa.replenishment_method,1,'Min - Max Quantities',2,'Min - Max Days',3,'Min Qty and Fixed Order Qty',4,'Min Days and Fixed Order Qty',paa.replenishment_method) vmi_replenishment_method,
paa.forecast_horizon vmi_forecast_horizon_days,
paa.vmi_min_qty vmi_minimum_quantity,
paa.vmi_max_qty vmi_maximum_quantity,
paa.vmi_min_days vmi_minimum_days,
paa.vmi_max_days vmi_maximum_days,
-- Consigned Attributes
xxen_util.meaning(decode(paa.consigned_from_supplier_flag,'Y','Y',null),'YES_NO',0) consigned_from_supplier,
paa.consigned_billing_cycle consigned_billing_cycle_days,
paa.last_billing_date consigned_last_billing_date,
xxen_util.meaning(decode(paa.consume_on_aging_flag,'Y','Y',null),'YES_NO',0) consigned_consume_on_aging,
paa.aging_period consigned_aging_period_days,
--
-- ASL Supplier Documents
to_number(null) source_document_seq,
null source_document_type,
null source_document_number,
null source_document_line,
null delete_this_document,
null document_global_agreement,
null document_owning_org,
null document_status,
to_date(null) document_effective_from,
to_date(null) document_effective_to,
--
-- Authorizations
to_number(null) authorization_seq,
null authorization,
to_number(null) authorization_cutoff_days,
null delete_this_authorization,
--
-- Capacity
psic.from_date capacity_from_date,
psic.to_date capacity_to_date,
psic.capacity_per_day,
null delete_this_capacity,
--
-- Tolerances
to_number(null) tolerance_days_in_advance,
to_number(null) tolerance_percentage,
null delete_this_tolerance,
--
-- ASL DFFs
xxen_util.display_flexfield_context(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category) po_asl_dff_category,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE1',pasl.rowid,pasl.attribute1) po_asl_attribute1,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE2',pasl.rowid,pasl.attribute2) po_asl_attribute2,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE3',pasl.rowid,pasl.attribute3) po_asl_attribute3,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE4',pasl.rowid,pasl.attribute4) po_asl_attribute4,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE5',pasl.rowid,pasl.attribute5) po_asl_attribute5,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE6',pasl.rowid,pasl.attribute6) po_asl_attribute6,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE7',pasl.rowid,pasl.attribute7) po_asl_attribute7,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE8',pasl.rowid,pasl.attribute8) po_asl_attribute8,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE9',pasl.rowid,pasl.attribute9) po_asl_attribute9,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE10',pasl.rowid,pasl.attribute10) po_asl_attribute10,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE11',pasl.rowid,pasl.attribute11) po_asl_attribute11,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE12',pasl.rowid,pasl.attribute12) po_asl_attribute12,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE13',pasl.rowid,pasl.attribute13) po_asl_attribute13,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE14',pasl.rowid,pasl.attribute14) po_asl_attribute14,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE15',pasl.rowid,pasl.attribute15) po_asl_attribute15,
-- Attribute DFFs
xxen_util.display_flexfield_context(201,'PO_ASL_ATTRIBUTES',paa.attribute_category) po_asl_att_dff_category,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE1',paa.rowid,paa.attribute1) po_asl_att_attribute1,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE2',paa.rowid,paa.attribute2) po_asl_att_attribute2,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE3',paa.rowid,paa.attribute3) po_asl_att_attribute3,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE4',paa.rowid,paa.attribute4) po_asl_att_attribute4,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE5',paa.rowid,paa.attribute5) po_asl_att_attribute5,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE6',paa.rowid,paa.attribute6) po_asl_att_attribute6,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE7',paa.rowid,paa.attribute7) po_asl_att_attribute7,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE8',paa.rowid,paa.attribute8) po_asl_att_attribute8,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE9',paa.rowid,paa.attribute9) po_asl_att_attribute9,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE10',paa.rowid,paa.attribute10) po_asl_att_attribute10,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE11',paa.rowid,paa.attribute11) po_asl_att_attribute11,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE12',paa.rowid,paa.attribute12) po_asl_att_attribute12,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE13',paa.rowid,paa.attribute13) po_asl_att_attribute13,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE14',paa.rowid,paa.attribute14) po_asl_att_attribute14,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE15',paa.rowid,paa.attribute15) po_asl_att_attribute15,
-- Document DFFs
null po_asl_doc_dff_category,
null po_asl_doc_attribute1,
null po_asl_doc_attribute2,
null po_asl_doc_attribute3,
null po_asl_doc_attribute4,
null po_asl_doc_attribute5,
null po_asl_doc_attribute6,
null po_asl_doc_attribute7,
null po_asl_doc_attribute8,
null po_asl_doc_attribute9,
null po_asl_doc_attribute10,
null po_asl_doc_attribute11,
null po_asl_doc_attribute12,
null po_asl_doc_attribute13,
null po_asl_doc_attribute14,
null po_asl_doc_attribute15,
--
-- IDs
pasl.asl_id old_asl_id,
null old_att_row_id,
null old_doc_row_id,
null old_ath_row_id,
rowidtochar(psic.rowid) old_cap_row_id,
null old_tol_row_id,
to_number(null) upload_row
from
po_approved_supplier_list pasl,
mtl_system_items_vl msiv,
mtl_categories_b_kfv mck,
ap_suppliers aps,
ap_supplier_sites ass,
mtl_manufacturers mm,
mtl_parameters mp_o,
hr_all_organization_units_tl haout_o,
--
po_asl_attributes paa,
mtl_parameters mp_u,
hr_all_organization_units_tl haout_u,
--
po_supplier_item_capacity psic
where
:p_download_capacities = 'Y' and
1=1 and
3=3 and
pasl.vendor_business_type != 'MANUFACTURER' and
pasl.owning_organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
pasl.owning_organization_id=msiv.organization_id(+) and
pasl.item_id=msiv.inventory_item_id(+) and
pasl.category_id=mck.category_id(+) and
pasl.vendor_id=aps.vendor_id(+) and
pasl.vendor_site_id=ass.vendor_site_id(+) and
((pasl.vendor_site_id is not null and ass.vendor_site_code is not null) or
 (pasl.vendor_site_id is null and ass.vendor_site_code is null)
) and
pasl.manufacturer_id=mm.manufacturer_id(+) and
pasl.owning_organization_id=mp_o.organization_id and
pasl.owning_organization_id=haout_o.organization_id and
haout_o.language = userenv('lang') and
--
pasl.asl_id=paa.asl_id and
pasl.using_organization_id = paa.using_organization_id and
paa.using_organization_id=mp_u.organization_id(+) and
paa.using_organization_id=haout_u.organization_id(+) and
haout_u.language(+) = userenv('lang') and
--
pasl.asl_id=psic.asl_id and
pasl.using_organization_id=psic.using_organization_id
--
union all
--
--
-- Q4 ASL Supplier Tolerances
--
select
-- Owning Organization
mp_o.organization_code owning_organization_code,
haout_o.name owning_organization,
-- ASL item/commodity
nvl2(pasl.item_id,'Item','Commodity') type,
mck.concatenated_segments commodity,
msiv.concatenated_segments item,
case when pasl.item_id is not null
then msiv.description
else (select mct.description from mtl_categories_tl mct where mct.category_id = pasl.category_id and mct.language = userenv('lang'))
end description,
--
-- ASL Suppliers
xxen_util.meaning(pasl.vendor_business_type,'ASL_VENDOR_BUSINESS_TYPE',201) supplier_business_type,
aps.vendor_name supplier,
aps.segment1 supplier_number,
ass.vendor_site_code supplier_site,
mm.manufacturer_name manufacturer,
(select mm2.manufacturer_name from po_approved_supplier_list pasl2, mtl_manufacturers mm2 where pasl2.manufacturer_id = mm2.manufacturer_id and pasl2.asl_id = pasl.manufacturer_asl_id) distributor_manufacturer,
xxen_util.meaning(decode(pasl.using_organization_id,-1,'Y','N'),'YES_NO',0) global_asl,
(select pas.status from po_asl_statuses pas where pas.status_id = pasl.asl_status_id) status,
xxen_util.meaning(decode(pasl.disable_flag,'Y','Y',null),'YES_NO',0) disabled,
pasl.primary_vendor_item supplier_item,
pasl.review_by_date review_by_date,
pasl.comments,
pasl.creation_date created,
--
-- ASL Supplier Attributes
mp_u.organization_code using_organization_code,
haout_u.name using_organization,
xxen_util.meaning(decode(paa.using_organization_id,-1,'Y',nvl2(paa.using_organization_id,'N',null)),'YES_NO',0) global_attributes,
null delete_this_asl,
paa.purchasing_unit_of_measure purchasing_uom,
xxen_util.meaning(paa.release_generation_method,'DOC GENERATION METHOD',201) release_method,
paa.price_update_tolerance,
paa.country_of_origin_code country_of_origin,
-- Supplier Scheduling Attributes
xxen_util.meaning(decode(paa.enable_plan_schedule_flag,'Y','Y',null),'YES_NO',0) enable_planning_schedules,
xxen_util.meaning(decode(paa.enable_ship_schedule_flag,'Y','Y',null),'YES_NO',0) enable_shipping_schedules,
(select ppx.full_name from per_people_x ppx where paa.scheduler_id=ppx.person_id) scheduler,
(select ppx.employee_number from per_people_x ppx where paa.scheduler_id=ppx.person_id) scheduler_emp_num,
xxen_util.meaning(decode(paa.enable_autoschedule_flag,'Y','Y',null),'YES_NO',0) enable_autoschedule,
(select cbp.bucket_pattern_name from chv_bucket_patterns cbp where cbp.bucket_pattern_id=paa.plan_bucket_pattern_id) plan_bucket_pattern,
(select cbp.bucket_pattern_name from chv_bucket_patterns cbp where cbp.bucket_pattern_id=paa.ship_bucket_pattern_id) ship_bucket_pattern,
xxen_util.meaning(paa.plan_schedule_type,'PLAN_SCHEDULE_SUBTYPE',201) plan_schedule_type,
xxen_util.meaning(paa.ship_schedule_type,'SHIP_SCHEDULE_SUBTYPE',201) ship_schedule_type,
xxen_util.meaning(decode(paa.enable_authorizations_flag,'Y','Y',null),'YES_NO',0) enable_authorizations,
-- Planning Constraints Attributes
paa.delivery_calendar supplier_capacity_calendar,
paa.processing_lead_time,
-- Inventory Attributes
paa.min_order_qty inv_minimum_order_quantity,
paa.fixed_lot_multiple inv_fixed_lot_multiple,
paa.fixed_order_quantity inv_fixed_order_quantity,
-- VMI Attributes
xxen_util.meaning(decode(paa.enable_vmi_flag,'Y','Y',null),'YES_NO',0) vmi_enabled,
xxen_util.meaning(decode(paa.enable_vmi_auto_replenish_flag,'Y','Y',null),'YES_NO',0) vmi_allow_auto_replenishment,
decode(paa.vmi_replenishment_approval,'SUPPLIER_OR_BUYER','Supplier or Buyer',initcap(paa.vmi_replenishment_approval)) vmi_replenishment_approval,
decode(paa.replenishment_method,1,'Min - Max Quantities',2,'Min - Max Days',3,'Min Qty and Fixed Order Qty',4,'Min Days and Fixed Order Qty',paa.replenishment_method) vmi_replenishment_method,
paa.forecast_horizon vmi_forecast_horizon_days,
paa.vmi_min_qty vmi_minimum_quantity,
paa.vmi_max_qty vmi_maximum_quantity,
paa.vmi_min_days vmi_minimum_days,
paa.vmi_max_days vmi_maximum_days,
-- Consigned Attributes
xxen_util.meaning(decode(paa.consigned_from_supplier_flag,'Y','Y',null),'YES_NO',0) consigned_from_supplier,
paa.consigned_billing_cycle consigned_billing_cycle_days,
paa.last_billing_date consigned_last_billing_date,
xxen_util.meaning(decode(paa.consume_on_aging_flag,'Y','Y',null),'YES_NO',0) consigned_consume_on_aging,
paa.aging_period consigned_aging_period_days,
--
-- ASL Supplier Documents
to_number(null) source_document_seq,
null source_document_type,
null source_document_number,
null source_document_line,
null delete_this_document,
null document_global_agreement,
null document_owning_org,
null document_status,
to_date(null) document_effective_from,
to_date(null) document_effective_to,
--
-- Authorizations
to_number(null) authorization_seq,
null authorization,
to_number(null) authorization_cutoff_days,
null delete_this_authorization,
--
-- Capacity
to_date(null) capacity_from_date,
to_date(null) capacity_to_date,
to_number(null) capacity_per_day,
null delete_this_capacity,
--
-- Tolerances
psit.number_of_days tolerance_days_in_advance,
psit.tolerance tolerance_percentage,
null delete_this_tolerance,
--
-- ASL DFFs
xxen_util.display_flexfield_context(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category) po_asl_dff_category,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE1',pasl.rowid,pasl.attribute1) po_asl_attribute1,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE2',pasl.rowid,pasl.attribute2) po_asl_attribute2,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE3',pasl.rowid,pasl.attribute3) po_asl_attribute3,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE4',pasl.rowid,pasl.attribute4) po_asl_attribute4,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE5',pasl.rowid,pasl.attribute5) po_asl_attribute5,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE6',pasl.rowid,pasl.attribute6) po_asl_attribute6,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE7',pasl.rowid,pasl.attribute7) po_asl_attribute7,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE8',pasl.rowid,pasl.attribute8) po_asl_attribute8,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE9',pasl.rowid,pasl.attribute9) po_asl_attribute9,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE10',pasl.rowid,pasl.attribute10) po_asl_attribute10,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE11',pasl.rowid,pasl.attribute11) po_asl_attribute11,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE12',pasl.rowid,pasl.attribute12) po_asl_attribute12,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE13',pasl.rowid,pasl.attribute13) po_asl_attribute13,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE14',pasl.rowid,pasl.attribute14) po_asl_attribute14,
xxen_util.display_flexfield_value(201,'PO_APPROVED_SUPPLIER_LIST',pasl.attribute_category,'ATTRIBUTE15',pasl.rowid,pasl.attribute15) po_asl_attribute15,
-- Attribute DFFs
xxen_util.display_flexfield_context(201,'PO_ASL_ATTRIBUTES',paa.attribute_category) po_asl_att_dff_category,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE1',paa.rowid,paa.attribute1) po_asl_att_attribute1,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE2',paa.rowid,paa.attribute2) po_asl_att_attribute2,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE3',paa.rowid,paa.attribute3) po_asl_att_attribute3,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE4',paa.rowid,paa.attribute4) po_asl_att_attribute4,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE5',paa.rowid,paa.attribute5) po_asl_att_attribute5,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE6',paa.rowid,paa.attribute6) po_asl_att_attribute6,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE7',paa.rowid,paa.attribute7) po_asl_att_attribute7,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE8',paa.rowid,paa.attribute8) po_asl_att_attribute8,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE9',paa.rowid,paa.attribute9) po_asl_att_attribute9,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE10',paa.rowid,paa.attribute10) po_asl_att_attribute10,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE11',paa.rowid,paa.attribute11) po_asl_att_attribute11,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE12',paa.rowid,paa.attribute12) po_asl_att_attribute12,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE13',paa.rowid,paa.attribute13) po_asl_att_attribute13,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE14',paa.rowid,paa.attribute14) po_asl_att_attribute14,
xxen_util.display_flexfield_value(201,'PO_ASL_ATTRIBUTES',paa.attribute_category,'ATTRIBUTE15',paa.rowid,paa.attribute15) po_asl_att_attribute15,
-- Document DFFs
null po_asl_doc_dff_category,
null po_asl_doc_attribute1,
null po_asl_doc_attribute2,
null po_asl_doc_attribute3,
null po_asl_doc_attribute4,
null po_asl_doc_attribute5,
null po_asl_doc_attribute6,
null po_asl_doc_attribute7,
null po_asl_doc_attribute8,
null po_asl_doc_attribute9,
null po_asl_doc_attribute10,
null po_asl_doc_attribute11,
null po_asl_doc_attribute12,
null po_asl_doc_attribute13,
null po_asl_doc_attribute14,
null po_asl_doc_attribute15,
--
-- IDs
pasl.asl_id old_asl_id,
null old_att_row_id,
null old_doc_row_id,
null old_ath_row_id,
null old_cap_row_id,
rowidtochar(psit.rowid) old_tol_row_id,
to_number(null) upload_row
from
po_approved_supplier_list pasl,
mtl_system_items_vl msiv,
mtl_categories_b_kfv mck,
ap_suppliers aps,
ap_supplier_sites ass,
mtl_manufacturers mm,
mtl_parameters mp_o,
--
po_asl_attributes paa,
mtl_parameters mp_u,
hr_all_organization_units_tl haout_u,
--
hr_all_organization_units_tl haout_o,
po_supplier_item_tolerance psit
--
where
:p_download_tolerances = 'Y' and
1=1 and
3=3 and
pasl.vendor_business_type != 'MANUFACTURER' and
pasl.owning_organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
pasl.owning_organization_id=msiv.organization_id(+) and
pasl.item_id=msiv.inventory_item_id(+) and
pasl.category_id=mck.category_id(+) and
pasl.vendor_id=aps.vendor_id(+) and
pasl.vendor_site_id=ass.vendor_site_id(+) and
((pasl.vendor_site_id is not null and ass.vendor_site_code is not null) or
 (pasl.vendor_site_id is null and ass.vendor_site_code is null)
) and
pasl.manufacturer_id=mm.manufacturer_id(+) and
pasl.owning_organization_id=mp_o.organization_id and
pasl.owning_organization_id=haout_o.organization_id and
haout_o.language = userenv('lang') and
--
pasl.asl_id=paa.asl_id and
pasl.using_organization_id = paa.using_organization_id and
paa.using_organization_id=mp_u.organization_id(+) and
paa.using_organization_id=haout_u.organization_id(+) and
haout_u.language(+) = userenv('lang') and
--
pasl.asl_id=psit.asl_id and
pasl.using_organization_id=psit.using_organization_id
) asl
where
:p_upload_mode like '%' || xxen_upload.action_update
Parameter NameSQL textValidation
Upload Mode
 
LOV
Owning Organization Code
mp_o.organization_code=:p_owning_organization_code
LOV
Type
nvl2(pasl.item_id,'Item','Commodity') = :p_type
LOV
Item
msiv.concatenated_segments=:p_item
LOV
Item From
msiv.concatenated_segments>=:p_item_from
LOV
Item To
msiv.concatenated_segments<=:p_item_to
LOV
Commodity
mck.concatenated_segments=:p_category
LOV
Commodity From
mck.concatenated_segments>=:p_category_from
LOV
Commodity To
mck.concatenated_segments<=:p_category_to
LOV
Global ASL
decode(pasl.using_organization_id,-1,'Y','N') = :p_gloabl_asl_flag
LOV Oracle
Using Organization Code
exists
(select 
 null
 from
 mtl_parameters mp
 where
 mp.organization_code=:p_using_organization_code and
 (pasl.using_organization_id = -1 or
  pasl.using_organization_id = mp.organization_id
 ) and
 (pasl.item_id is null or
  exists (select null from mtl_system_items_b msib where msib.organization_id = mp.organization_id and msib.inventory_item_id = pasl.item_id)
 )
)
LOV
Missing Release Method
paa.release_generation_method is null
LOV
Business Type
pasl.vendor_business_type = :p_business_type
LOV
Operating Unit
po_moac_utils_pvt.get_ou_name(ass.org_id)=:p_operating_unit
LOV
Supplier
aps.vendor_name=:p_supplier
LOV
Supplier Number
aps.segment1=:p_supplier_number
LOV
Supplier Site
ass.vendor_site_code=:p_supplier_site
LOV
Manufacturer
(mm.manufacturer_name = :p_manufacturer or
 pasl.manufacturer_asl_id in
 (select
  pasl2.asl_id 
  from
  mtl_manufacturers mm2,
  po_approved_supplier_list pasl2
  where
  pasl2.manufacturer_id = mm2.manufacturer_id and
  mm2.manufacturer_name = :p_manufacturer
 )
)
LOV
ASL Status
pasl.asl_status_id=:p_asl_status
LOV
ASL Disabled
nvl(pasl.disable_flag,'N')=:p_asl_disabled
LOV Oracle
Source Document
pha.segment1=:p_source_document
LOV
Download Authorizations
 
LOV
Download Capacities
 
LOV
Download Tolerances
 
LOV
Download
Blitz Report™