Approved Supplier List Setup
—————————-
SELECT haou_u.NAME “Using Organization”
, DECODE(pasl.item_id
, NULL, ‘Commodity’
, ‘Item’) “Type”
, mcv.CATEGORY_CONCAT_SEGS “Commodity”
, msik.CONCATENATED_SEGMENTS “Item”
, ” “[Suppliers]”
, plc_t.DISPLAYED_FIELD “Business Type”
, DECODE(pasl.VENDOR_BUSINESS_TYPE
, ‘MANUFACTURER’, mm_s.MANUFACTURER_NAME
, pv.VENDOR_NAME
) “Supplier”
, pvsa.VENDOR_SITE_CODE “Site”
, pas.STATUS “Status”
, NVL(pasl.DISABLE_FLAG, ‘N’) “Disabled”
, pasl.PRIMARY_VENDOR_ITEM “Supplier Item”
, mm_a.MANUFACTURER_NAME “Manufacturer”
, pasl.REVIEW_BY_DATE “Review By”
, DECODE(pasl.USING_ORGANIZATION_ID
, -1, ‘Y’
, ‘N’) “Global?”
, haou_o.NAME “Owning Org”
, pasl.CREATION_DATE “Created”
, pasl.COMMENTS “Comments”
, paa.PURCHASING_UNIT_OF_MEASURE “Purchasing UOM”
, plc_r.DISPLAYED_FIELD “Release Method”
, paa.PRICE_UPDATE_TOLERANCE “Price Update Tolerance”
, paa.COUNTRY_OF_ORIGIN_CODE “Country of Origin”
, NVL(paa.ENABLE_PLAN_SCHEDULE_FLAG
, ‘N’) “Enable Planning Schedules”
, NVL(paa.ENABLE_SHIP_SCHEDULE_FLAG
, ‘N’) “Enable Shipping Schedules”
, ppf.FULL_NAME “Scheduler”
, NVL(paa.ENABLE_AUTOSCHEDULE_FLAG
, ‘N’) “Enable AutoSchedule”
, NVL(paa.ENABLE_AUTHORIZATIONS_FLAG
, ‘N’) “Enable Authorizations”
, cbp_p.BUCKET_PATTERN_NAME “Plan Bucket Pattern”
, cbp_s.BUCKET_PATTERN_NAME “Ship Bucket Pattern”
, plc_p.DISPLAYED_FIELD “Plan Schedule Type”
, plc_s.DISPLAYED_FIELD “Ship Schedule Type”
, ” “PBP Sequence”
, ” “SBP Sequence”
, ” “PST Sequence”
, ” “SST Sequence”
, ” “PBP Authorization”
, ” “SBP Authorization”
, ” “PST Authorization”
, ” “SST Authorization”
, ” “PBP Cutoff Days”
, ” “SBP Cutoff Days”
, ” “PST Cutoff Days”
, ” “SST Cutoff Days”
, paa.PROCESSING_LEAD_TIME “Processing Lead Time”
, paa.DELIVERY_CALENDAR “Supplier Capacity Calendar”
, paa.MIN_ORDER_QTY “Minimum Order Quantity”
, paa.FIXED_LOT_MULTIPLE “Fixed Lot Multiple”
, NVL(paa.ENABLE_VMI_FLAG, ‘N’) “VMI Enabled”
, NVL(paa.ENABLE_VMI_AUTO_REPLENISH_FLAG
, ‘N’) “Automatic Allowed”
, DECODE(paa.VMI_REPLENISHMENT_APPROVAL
, ‘SUPPLIER_OR_BUYER’, ‘Supplier or Buyer’
, ‘BUYER’ , ‘Buyer’
, ‘NONE’ , ‘None’
) “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’
) “Method”
, paa.FORECAST_HORIZON “Forecast Horizon (Days)”
, paa.VMI_MIN_QTY “Minimum Quantity”
, paa.VMI_MAX_QTY “Maximum Quantity”
, paa.VMI_MIN_DAYS “Minimum Days”
, paa.VMI_MAX_DAYS “Maximum Days”
, paa.FIXED_ORDER_QUANTITY “Fixed Order Quantity”
, NVL(paa.CONSIGNED_FROM_SUPPLIER_FLAG
, ‘N’) “Consigned from Supplier”
, paa.CONSIGNED_BILLING_CYCLE “Billing Cycle (Days)”
, paa.LAST_BILLING_DATE “Last Billing Date”
, NVL(paa.CONSUME_ON_AGING_FLAG
, ‘N’) “Consume on Aging”
, paa.AGING_PERIOD “Aging Period (Days)”
, ” “[Source Documents]”
, padv.SEQUENCE_NUM “Seq”
, padv.DOCUMENT_TYPE_DSP “Document Type”
, padv.DOCUMENT_NUM “Number”
, padv.LINE_NUM “Line”
, padv.DOCUMENT_STATUS_DSP “Status”
, padv.EFFECTIVE_FROM “Effective From”
, padv.EFFECTIVE_TO “Effective To”
FROM PO_APPROVED_SUPPLIER_LIST pasl
, HR_ALL_ORGANIZATION_UNITS haou_u
, MTL_CATEGORIES_V mcv
, MTL_SYSTEM_ITEMS_KFV msik
, PO_LOOKUP_CODES plc_t
, MTL_MANUFACTURERS mm_s
, PO_VENDORS pv
, PO_VENDOR_SITES_ALL pvsa
, PO_ASL_STATUSES pas
, PO_APPROVED_SUPPLIER_LIST pasl_a
, MTL_MANUFACTURERS mm_a
, HR_ALL_ORGANIZATION_UNITS haou_o
, PO_ASL_ATTRIBUTES paa
, PO_LOOKUP_CODES plc_r
, PER_PEOPLE_F ppf
, CHV_BUCKET_PATTERNS cbp_p
, CHV_BUCKET_PATTERNS cbp_s
, PO_LOOKUP_CODES plc_p
, PO_LOOKUP_CODES plc_s
, PO_ASL_DOCUMENTS_V padv
WHERE pasl.USING_ORGANIZATION_ID = haou_u.ORGANIZATION_ID (+)
AND pasl.CATEGORY_ID = mcv.CATEGORY_ID (+)
AND pasl.ITEM_ID = msik.INVENTORY_ITEM_ID (+)
AND pasl.OWNING_ORGANIZATION_ID = msik.ORGANIZATION_ID (+)
AND pasl.VENDOR_BUSINESS_TYPE = plc_t.LOOKUP_CODE
AND pasl.MANUFACTURER_ID = mm_s.MANUFACTURER_ID (+)
AND pasl.VENDOR_ID = pv.VENDOR_ID (+)
AND pasl.VENDOR_SITE_ID = pvsa.VENDOR_SITE_ID (+)
AND pasl.ASL_STATUS_ID = pas.STATUS_ID
AND pasl.MANUFACTURER_ASL_ID = pasl_a.ASL_ID (+)
AND pasl_a.MANUFACTURER_ID = mm_a.MANUFACTURER_ID (+)
AND pasl.OWNING_ORGANIZATION_ID = haou_o.ORGANIZATION_ID (+)
AND pasl.ASL_ID = paa.ASL_ID (+)
AND pasl.USING_ORGANIZATION_ID = paa.USING_ORGANIZATION_ID (+)
AND paa.RELEASE_GENERATION_METHOD = plc_r.LOOKUP_CODE (+)
AND paa.SCHEDULER_ID = ppf.PERSON_ID (+)
AND paa.PLAN_BUCKET_PATTERN_ID = cbp_p.BUCKET_PATTERN_ID (+)
AND paa.SHIP_BUCKET_PATTERN_ID = cbp_s.BUCKET_PATTERN_ID (+)
AND paa.PLAN_SCHEDULE_TYPE = plc_p.LOOKUP_CODE (+)
AND paa.SHIP_SCHEDULE_TYPE = plc_s.LOOKUP_CODE (+)
AND paa.ASL_ID = padv.ASL_ID (+)
AND paa.USING_ORGANIZATION_ID = padv.USING_ORGANIZATION_ID (+)
AND plc_t.LOOKUP_TYPE = ‘ASL_VENDOR_BUSINESS_TYPE’
AND plc_r.LOOKUP_TYPE (+) = ‘DOC GENERATION METHOD’
AND plc_p.lookup_type (+) = ‘PLAN_SCHEDULE_SUBTYPE’
AND plc_s.lookup_type (+) = ‘SHIP_SCHEDULE_SUBTYPE’
ORDER BY haou_u.NAME
, DECODE(pasl.item_id
, NULL, ‘Commodity’
, ‘Item’)
, mcv.CATEGORY_CONCAT_SEGS
, msik.CONCATENATED_SEGMENTS
, plc_t.DISPLAYED_FIELD
, DECODE(pasl.VENDOR_BUSINESS_TYPE
, ‘MANUFACTURER’, mm_s.MANUFACTURER_NAME
, pv.VENDOR_NAME
)
, pvsa.VENDOR_SITE_CODE
, padv.SEQUENCE_NUM
OraFUN 