VIEW: APPS.MSC_CUST_BUCKET_V

Source

View Text - Preformatted

SELECT decode(publisher_order_type, 1, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as sales_forecast, decode(publisher_order_type, 2, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as order_forecast, decode(publisher_order_type, 3, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as supply_commit, decode(publisher_order_type, 4, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as historical_sales, decode(publisher_order_type, 5, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as sell_through_forecast, decode(publisher_order_type, 6, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as negotiated_capacity, decode(publisher_order_type, 7, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as safety_stock, decode(publisher_order_type, 9, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as allocated_onhand, decode(publisher_order_type, 10, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as unallocated_onhand, decode(publisher_order_type, 13, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as purchase_order, decode(publisher_order_type, 14, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as sales_order, decode(publisher_order_type, 15, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as asn, decode(publisher_order_type, 16, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as shipment_receipt, decode(publisher_order_type, 19, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as replenishment, decode(publisher_order_type, 20, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as requisition, decode(publisher_order_type, 21, decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity), 0) as po_acknowledge, item_name, supplier_name as supplier_name, supplier_site_name as supplier_site_name, customer_name as customer_name, customer_site_name as customer_site_name, publisher_name as document_owner, key_date as start_date from msc_sup_dem_entries_ui_v WHERE customer_id IS NOT NULL AND supplier_id IS NOT NULL AND key_date is not null and publisher_order_type != 17

View Text - HTML Formatted

SELECT DECODE(PUBLISHER_ORDER_TYPE
, 1
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS SALES_FORECAST
, DECODE(PUBLISHER_ORDER_TYPE
, 2
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS ORDER_FORECAST
, DECODE(PUBLISHER_ORDER_TYPE
, 3
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS SUPPLY_COMMIT
, DECODE(PUBLISHER_ORDER_TYPE
, 4
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS HISTORICAL_SALES
, DECODE(PUBLISHER_ORDER_TYPE
, 5
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS SELL_THROUGH_FORECAST
, DECODE(PUBLISHER_ORDER_TYPE
, 6
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS NEGOTIATED_CAPACITY
, DECODE(PUBLISHER_ORDER_TYPE
, 7
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS SAFETY_STOCK
, DECODE(PUBLISHER_ORDER_TYPE
, 9
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS ALLOCATED_ONHAND
, DECODE(PUBLISHER_ORDER_TYPE
, 10
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS UNALLOCATED_ONHAND
, DECODE(PUBLISHER_ORDER_TYPE
, 13
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS PURCHASE_ORDER
, DECODE(PUBLISHER_ORDER_TYPE
, 14
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS SALES_ORDER
, DECODE(PUBLISHER_ORDER_TYPE
, 15
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS ASN
, DECODE(PUBLISHER_ORDER_TYPE
, 16
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS SHIPMENT_RECEIPT
, DECODE(PUBLISHER_ORDER_TYPE
, 19
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS REPLENISHMENT
, DECODE(PUBLISHER_ORDER_TYPE
, 20
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS REQUISITION
, DECODE(PUBLISHER_ORDER_TYPE
, 21
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, PUBLISHER_ID
, PRIMARY_QUANTITY
, DECODE(PUBLISHER_ID
, CUSTOMER_ID
, SUPPLIER_ID
, CUSTOMER_ID)
, TP_QUANTITY
, QUANTITY)
, 0) AS PO_ACKNOWLEDGE
, ITEM_NAME
, SUPPLIER_NAME AS SUPPLIER_NAME
, SUPPLIER_SITE_NAME AS SUPPLIER_SITE_NAME
, CUSTOMER_NAME AS CUSTOMER_NAME
, CUSTOMER_SITE_NAME AS CUSTOMER_SITE_NAME
, PUBLISHER_NAME AS DOCUMENT_OWNER
, KEY_DATE AS START_DATE
FROM MSC_SUP_DEM_ENTRIES_UI_V
WHERE CUSTOMER_ID IS NOT NULL
AND SUPPLIER_ID IS NOT NULL
AND KEY_DATE IS NOT NULL
AND PUBLISHER_ORDER_TYPE != 17