ASCP SUPPLY & DEMAND WORKBENCH SQL
ASCP SUPPLY DEMAND WORKBENCH with Supply, Excess Supply & Demand and PEGGING DETAILS mapped against the supply
SET SQLBLANKLINES ON
WITH
params AS
(
SELECT 123 AS plan_id,
101 AS organization_id,
trunc(sysdate) AS from_date,
trunc(sysdate)+30 AS to_date
FROM dual
),
item_categories AS
(
SELECT
mic.inventory_item_id,
mic.organization_id,
mic.sr_instance_id,
MAX(CASE WHEN mcs.category_set_name = 'Planning Hierarchy' THEN mic.category_name END) AS planning_sub_family,
MAX(CASE WHEN mcs.category_set_name = 'Product Hierarchy' THEN mic.category_name END) AS product_line
FROM MSC.msc_item_categories mic
JOIN MSC.msc_category_sets mcs ON mcs.category_set_id = mic.category_set_id
JOIN params prm ON prm.organization_id = mic.organization_id
WHERE mcs.category_set_name IN ('Planning Hierarchy', 'Product Hierarchy')
GROUP BY
mic.inventory_item_id,
mic.organization_id,
mic.sr_instance_id
),
item_scope AS
(
SELECT /*+ MATERIALIZE */
si.plan_id,
si.inventory_item_id,
si.organization_id,
si.sr_instance_id,
si.organization_code,
si.item_name,
si.planner_code,
si.description,
si.uom_code,
si.category_name,
si.abc_class,
DECODE(
si.planning_make_buy_code,
1, 'Make',
2, 'Buy'
) AS make_buy,
si.bom_item_type,
si.base_item_id,
si.product_family_id,
si.full_lead_time,
si.standard_cost,
si.cum_manufacturing_lead_time,
ic.planning_sub_family,
ic.dept_wsf,
ic.product_line
FROM MSC.msc_system_items si
JOIN params prm
ON prm.plan_id = si.plan_id
AND prm.organization_id = si.organization_id
LEFT JOIN item_categories ic
ON ic.inventory_item_id = si.inventory_item_id
AND ic.organization_id = si.organization_id
AND ic.sr_instance_id = si.sr_instance_id
),
target_chains AS
(
SELECT /*+ MATERIALIZE */
DISTINCT
CASE
WHEN fp.end_pegging_id IS NULL
OR fp.end_pegging_id = 0
THEN fp.pegging_id
ELSE fp.end_pegging_id
END AS chain_id,
fp.plan_id,
fp.sr_instance_id
FROM params prm
JOIN MSC.msc_full_pegging fp
ON fp.plan_id = prm.plan_id
AND fp.organization_id = prm.organization_id
AND fp.demand_date >= prm.from_date
AND fp.demand_date < prm.to_date_excl
JOIN item_scope isc
ON isc.plan_id = fp.plan_id
AND isc.inventory_item_id = fp.inventory_item_id
AND isc.organization_id = fp.organization_id
AND isc.sr_instance_id = fp.sr_instance_id
),
peg AS
(
SELECT /*+ MATERIALIZE */
p.plan_id,
p.pegging_id,
p.prev_pegging_id,
p.end_pegging_id,
p.sr_instance_id,
p.inventory_item_id,
p.organization_id,
p.supply_type,
p.transaction_id,
p.supply_date,
p.supply_quantity,
p.allocated_quantity,
p.demand_id,
p.demand_date,
p.demand_quantity,
p.project_id,
p.task_id,
p.unit_number,
p.demand_class,
p.end_pegging_id AS chain_id
FROM target_chains tc
JOIN MSC.msc_full_pegging p
ON p.plan_id = tc.plan_id
AND p.sr_instance_id = tc.sr_instance_id
AND p.end_pegging_id = tc.chain_id
WHERE p.end_pegging_id IS NOT NULL
AND p.end_pegging_id <> 0
UNION ALL
SELECT
p.plan_id,
p.pegging_id,
p.prev_pegging_id,
p.end_pegging_id,
p.sr_instance_id,
p.inventory_item_id,
p.organization_id,
p.supply_type,
p.transaction_id,
p.supply_date,
p.supply_quantity,
p.allocated_quantity,
p.demand_id,
p.demand_date,
p.demand_quantity,
p.project_id,
p.task_id,
p.unit_number,
p.demand_class,
p.pegging_id AS chain_id
FROM target_chains tc
JOIN MSC.msc_full_pegging p
ON p.plan_id = tc.plan_id
AND p.sr_instance_id = tc.sr_instance_id
AND p.pegging_id = tc.chain_id
WHERE p.end_pegging_id IS NULL
OR p.end_pegging_id = 0
),
active_people AS
(
SELECT
USER_NAME, --employee_number,
MAX(DESCRIPTION) AS full_name
FROM APPLSYS.FND_USER--hr.per_all_people_f
WHERE NVL(START_DATE, TRUNC(SYSDATE)) < TRUNC(SYSDATE) + 1
AND NVL(END_DATE, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
GROUP BY USER_NAME
),
planner_lookup AS
(
SELECT
plr.planner_code,
plr.organization_id,
plr.sr_instance_id,
plr.description AS planner_description,
plr.user_name,
pap.full_name AS planner_employee_name
FROM MSC.msc_planners plr
LEFT JOIN active_people pap
ON pap.USER_NAME = plr.user_name
),
supplier_lookup AS
(
SELECT
tp.partner_id AS supplier_id,
tps.partner_site_id AS supplier_site_id,
tp.sr_tp_id,
tp.partner_type,
tp.sr_instance_id,
tp.partner_name AS supplier_name,
tp.partner_number AS supplier_number,
tp.organization_code,
tps.tp_site_code AS supplier_site_code
FROM MSC.msc_trading_partners tp
LEFT JOIN MSC.msc_trading_partner_sites tps
ON tps.partner_id = tp.partner_id
AND tps.sr_instance_id = tp.sr_instance_id
WHERE tp.partner_type IN (1,3)
),
demand_lookup AS
(
SELECT /*+ MATERIALIZE */
d.plan_id,
d.sr_instance_id,
d.demand_id,
d.RESERVATION_ID,
d.inventory_item_id,
d.organization_id,
d.order_number#1 order_number,
d.origination_type,
d.sales_order_line_id,
d.customer_id,
d.customer_site_id,
d.schedule_ship_date,
d.promise_date,
d.planned_ship_date,
d.schedule_arrival_date,
d.using_assembly_item_id,
d.primary_component_id,
d.demand_class,
d.project_id,
d.task_id,
d.DEMAND_TYPE,
d.demand_priority,
d.order_priority,
d.release_status,
d.planning_group,
d.source_organization_id,
d.source_org_instance_id,
- NVL(d.daily_demand_rate,d.using_requirement_quantity) AS quantity_rate,
d.reserved_quantity,
d.release_errors,
d.reschedule_flag,
d.org_firm_flag,
d.wip_status_code,
d.wip_supply_type,
d.using_assembly_demand_date,
d.using_requirement_quantity,
CASE
WHEN d.order_number#1 LIKE '%(%)' THEN
CASE
WHEN INSTR(d.order_number#1, '.') > 0 AND INSTR(d.order_number#1, '.') < INSTR(d.order_number#1, '(', -1)
THEN SUBSTR(d.order_number#1, 1, INSTR(d.order_number#1, '.') - 1)
ELSE SUBSTR(d.order_number#1, 1, INSTR(d.order_number#1, '(', -1) - 1)
END
|| '-' ||
SUBSTR(d.order_number#1, INSTR(d.order_number#1, '(', -1) + 1, INSTR(d.order_number#1, ')', -1) - INSTR(d.order_number#1, '(', -1) - 1)
ELSE d.order_number#1
END AS normalized_order_number
FROM MSC.msc_demands d
-- JOIN MSC.msc_plans mp
-- ON mp.plan_id = d.plan_id
WHERE EXISTS
(
SELECT 1
FROM params prm
WHERE prm.plan_id = d.plan_id
)
),
supply_lookup AS
(
SELECT /*+ MATERIALIZE */
s.plan_id,
s.sr_instance_id,
s.transaction_id,
s.inventory_item_id,
s.organization_id,
s.order_type,
s.order_number,
s."ALTERNATE_BOM_DESIGNATOR#1" AS alternate_bom_designator,
s."ALTERNATE_ROUTING_DESIGNATOR#1" AS alternate_routing_designator,
--s.ALTERNATE_BOM_DESIGNATOR,
--s.ALTERNATE_ROUTING_DESIGNATOR,
s.disposition_id,
s.purch_line_num,
s.disposition_status_type,
s.firm_planned_type,
s.supplier_id,
s.supplier_site_id,
s.source_organization_id,
s.implement_job_name,
s.implement_date,
s.new_dock_date,
s.new_ship_date,
s.new_wip_start_date,
s.new_order_placement_date,
-- msc_get_name.action('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, s.order_type,
-- s.reschedule_flag, s.disposition_status_type, s.new_schedule_date, s.old_schedule_date, s.implemented_quantity,
-- s.quantity_in_process, s.new_order_quantity, msi.release_time_fence_code, s.reschedule_days, s.firm_quantity,
-- s.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, s.item_type_value,
-- s.transaction_id) AS action,
s.project_id,
s.task_id,
s.unit_number,
s.release_errors,
s.build_sequence,
s.wip_status_code,
s.release_status,
s.schedule_priority,
s.planning_group,
s.schedule_group_id,
s.implement_as,
s.subinventory_code,
s.reserved_qty,
NVL(s.daily_rate, s.new_order_quantity) AS quantity_rate,
s.line_id,
s.source_supplier_id,
s.source_supplier_site_id,
s.implemented_quantity,
s.implement_daily_rate,
s.implement_quantity,
s.implement_status_code,
s.quantity_in_process,
s.reschedule_flag,
s.old_schedule_date,
s.PROCESS_SEQ_ID,
COALESCE(
s.new_order_quantity,
s.firm_quantity,
s.old_order_quantity
) AS supply_order_qty,
s.new_schedule_date AS supply_schedule_date
FROM MSC.MSC_SUPPLIES s
-- JOIN MSC.msc_system_items msi
-- ON msi.plan_id = s.plan_id
-- AND msi.inventory_item_id = s.inventory_item_id
-- AND msi.organization_id = s.organization_id
-- AND msi.sr_instance_id = s.sr_instance_id
JOIN params prm
ON prm.plan_id = s.plan_id
AND prm.organization_id = s.organization_id
-- WHERE s.plan_id = 69027
-- AND s.organization_id IN (128, 158)
),
pegged_supply AS
(
SELECT /*+ MATERIALIZE */
plan_id,
sr_instance_id,
transaction_id,
SUM(NVL(allocated_quantity, 0)) AS pegged_qty
FROM peg
WHERE transaction_id IS NOT NULL
GROUP BY
plan_id,
sr_instance_id,
transaction_id
),
pegged_demand AS
(
SELECT /*+ MATERIALIZE */
plan_id,
sr_instance_id,
demand_id,
SUM(NVL(allocated_quantity, 0)) AS pegged_qty
FROM peg
WHERE demand_id IS NOT NULL
GROUP BY
plan_id,
sr_instance_id,
demand_id
),
supply_base AS
(
SELECT /*+ MATERIALIZE */
s.plan_id,
s.sr_instance_id,
s.transaction_id,
s.inventory_item_id,
s.organization_id,
s.order_type,
s.order_number,
s."ALTERNATE_BOM_DESIGNATOR#1" AS alternate_bom_designator,
s."ALTERNATE_ROUTING_DESIGNATOR#1" AS alternate_routing_designator,
--s.ALTERNATE_BOM_DESIGNATOR,
--s.ALTERNATE_ROUTING_DESIGNATOR,
s.disposition_id,
s.purch_line_num,
s.disposition_status_type,
s.firm_planned_type,
s.reschedule_days,
s.firm_quantity,
s.daily_rate,
s.new_order_quantity,
s.old_order_quantity,
s.item_type_value,
s.supplier_id,
s.supplier_site_id,
s.source_organization_id,
s.source_supplier_id,
s.source_supplier_site_id,
s.implement_job_name,
s.implement_date,
s.implement_daily_rate,
s.implement_quantity,
s.implement_status_code,
s.implement_as,
s.project_id,
s.task_id,
s.build_sequence,
s.wip_status_code,
s.release_status,
s.schedule_priority,
s.schedule_group_id,
s.planning_group,
s.subinventory_code,
s.reserved_qty,
s.reschedule_flag,
s.implemented_quantity,
s.quantity_in_process,
NVL(s.daily_rate,
s.new_order_quantity) AS quantity_rate,
s.line_id,
s.new_wip_start_date,
s.new_dock_date,
s.new_ship_date,
s.new_order_placement_date,
s.old_schedule_date,
s.release_errors,
s.unit_number,
s.new_schedule_date AS supply_schedule_date,
s.PROCESS_SEQ_ID,
COALESCE(
s.new_order_quantity,
s.firm_quantity,
s.old_order_quantity
) AS supply_order_qty
FROM MSC.MSC_SUPPLIES s
JOIN params prm
ON prm.plan_id = s.plan_id
AND prm.organization_id = s.organization_id
JOIN item_scope isc
ON isc.plan_id = s.plan_id
AND isc.inventory_item_id = s.inventory_item_id
AND isc.organization_id = s.organization_id
AND isc.sr_instance_id = s.sr_instance_id
WHERE s.new_schedule_date >= prm.from_date
AND s.new_schedule_date < prm.to_date_excl
UNION ALL
SELECT
s.plan_id,
s.sr_instance_id,
s.transaction_id,
s.inventory_item_id,
s.organization_id,
s.order_type,
s.order_number,
s."ALTERNATE_BOM_DESIGNATOR#1" AS alternate_bom_designator,
s."ALTERNATE_ROUTING_DESIGNATOR#1" AS alternate_routing_designator,
--s.ALTERNATE_BOM_DESIGNATOR,
--s.ALTERNATE_ROUTING_DESIGNATOR,
s.disposition_id,
s.purch_line_num,
s.disposition_status_type,
s.firm_planned_type,
s.reschedule_days,
s.firm_quantity,
s.daily_rate,
s.new_order_quantity,
s.old_order_quantity,
s.item_type_value,
s.supplier_id,
s.supplier_site_id,
s.source_organization_id,
s.source_supplier_id,
s.source_supplier_site_id,
s.implement_job_name,
s.implement_date,
s.implement_daily_rate,
s.implement_quantity,
s.implement_status_code,
s.implement_as,
s.project_id,
s.task_id,
s.build_sequence,
s.wip_status_code,
s.release_status,
s.schedule_priority,
s.schedule_group_id,
s.planning_group,
s.subinventory_code,
s.reserved_qty,
s.reschedule_flag,
s.implemented_quantity,
s.quantity_in_process,
NVL(s.daily_rate,
s.new_order_quantity) AS quantity_rate,
s.line_id,
s.new_wip_start_date,
s.new_dock_date,
s.new_ship_date,
s.new_order_placement_date,
s.old_schedule_date,
s.release_errors,
s.unit_number,
s.firm_date AS supply_schedule_date,
s.PROCESS_SEQ_ID,
COALESCE(
s.new_order_quantity,
s.firm_quantity,
s.old_order_quantity
) AS supply_order_qty
FROM MSC.MSC_SUPPLIES s
JOIN params prm
ON prm.plan_id = s.plan_id
AND prm.organization_id = s.organization_id
JOIN item_scope isc
ON isc.plan_id = s.plan_id
AND isc.inventory_item_id = s.inventory_item_id
AND isc.organization_id = s.organization_id
AND isc.sr_instance_id = s.sr_instance_id
WHERE s.new_schedule_date IS NULL
AND s.firm_date >= prm.from_date
AND s.firm_date < prm.to_date_excl
UNION ALL
SELECT
s.plan_id,
s.sr_instance_id,
s.transaction_id,
s.inventory_item_id,
s.organization_id,
s.order_type,
s.order_number,
s."ALTERNATE_BOM_DESIGNATOR#1" AS alternate_bom_designator,
s."ALTERNATE_ROUTING_DESIGNATOR#1" AS alternate_routing_designator,
--s.ALTERNATE_BOM_DESIGNATOR,
--s.ALTERNATE_ROUTING_DESIGNATOR,
s.disposition_id,
s.purch_line_num,
s.disposition_status_type,
s.firm_planned_type,
s.reschedule_days,
s.firm_quantity,
s.daily_rate,
s.new_order_quantity,
s.old_order_quantity,
s.item_type_value,
s.supplier_id,
s.supplier_site_id,
s.source_organization_id,
s.source_supplier_id,
s.source_supplier_site_id,
s.implement_job_name,
s.implement_date,
s.implement_daily_rate,
s.implement_quantity,
s.implement_status_code,
s.implement_as,
s.project_id,
s.task_id,
s.build_sequence,
s.wip_status_code,
s.release_status,
s.schedule_priority,
s.schedule_group_id,
s.planning_group,
s.subinventory_code,
s.reserved_qty,
s.reschedule_flag,
s.implemented_quantity,
s.quantity_in_process,
NVL(s.daily_rate,
s.new_order_quantity) AS quantity_rate,
s.line_id,
s.new_wip_start_date,
s.new_dock_date,
s.new_ship_date,
s.new_order_placement_date,
s.old_schedule_date,
s.release_errors,
s.unit_number,
s.old_schedule_date AS supply_schedule_date,
s.PROCESS_SEQ_ID,
COALESCE(
s.new_order_quantity,
s.firm_quantity,
s.old_order_quantity
) AS supply_order_qty
FROM MSC.MSC_SUPPLIES s
JOIN params prm
ON prm.plan_id = s.plan_id
AND prm.organization_id = s.organization_id
JOIN item_scope isc
ON isc.plan_id = s.plan_id
AND isc.inventory_item_id = s.inventory_item_id
AND isc.organization_id = s.organization_id
AND isc.sr_instance_id = s.sr_instance_id
WHERE s.new_schedule_date IS NULL
AND s.firm_date IS NULL
AND s.old_schedule_date >= prm.from_date
AND s.old_schedule_date < prm.to_date_excl
),
process_keys AS
(
SELECT DISTINCT
process_seq_id process_sequence_id,
plan_id,
sr_instance_id
FROM supply_base
WHERE process_seq_id IS NOT NULL
),
process_effectivity_cache AS
(
SELECT /*+ MATERIALIZE */
pe1.plan_id,
pe1.sr_instance_id,
pe1.process_sequence_id,
pe1.bill_sequence_id,
pe1.routing_sequence_id,
mb."ALTERNATE_BOM_DESIGNATOR#1" AS actual_bom_value,
mr."ALTERNATE_ROUTING_DESIGNATOR#1" AS actual_routing_value
FROM MSC.msc_process_effectivity pe1
JOIN process_keys pk
ON pk.plan_id = pe1.plan_id
AND pk.sr_instance_id = pe1.sr_instance_id
AND pk.process_sequence_id = pe1.process_sequence_id
LEFT JOIN MSC.msc_boms mb
ON mb.plan_id = pe1.plan_id
AND mb.sr_instance_id = pe1.sr_instance_id
AND mb.bill_sequence_id = pe1.bill_sequence_id
LEFT JOIN MSC.msc_routings mr
ON mr.plan_id = pe1.plan_id
AND mr.sr_instance_id = pe1.sr_instance_id
AND mr.routing_sequence_id = pe1.routing_sequence_id
),
demand_base AS
(
SELECT /*+ MATERIALIZE */
d.plan_id,
d.sr_instance_id,
d.demand_id,
d.inventory_item_id,
d.organization_id,
d.order_number,
d.normalized_order_number,
d.origination_type,
d.sales_order_line_id,
d.customer_id,
d.customer_site_id,
d.schedule_ship_date,
d.promise_date,
d.planned_ship_date,
d.schedule_arrival_date,
d.using_assembly_item_id,
d.primary_component_id,
d.demand_class,
d.project_id,
d.task_id,
d.demand_priority,
d.order_priority,
d.planning_group,
d.source_organization_id,
d.source_org_instance_id,
d.quantity_rate,
d.reserved_quantity,
d.release_status,
d.release_errors,
d.reschedule_flag,
d.org_firm_flag,
d.using_assembly_demand_date,
--d.action,
d.using_requirement_quantity AS demand_req_qty
FROM demand_lookup d
JOIN params prm
ON prm.plan_id = d.plan_id
AND prm.organization_id = d.organization_id
JOIN item_scope isc
ON isc.plan_id = d.plan_id
AND isc.inventory_item_id = d.inventory_item_id
AND isc.organization_id = d.organization_id
AND isc.sr_instance_id = d.sr_instance_id
WHERE d.using_assembly_demand_date >= prm.from_date
AND d.using_assembly_demand_date < prm.to_date_excl
AND d.using_requirement_quantity > 0
),
project_keys AS
(
SELECT
project_id,
organization_id,
sr_instance_id
FROM supply_base
WHERE project_id IS NOT NULL
UNION
SELECT
project_id,
organization_id,
sr_instance_id
FROM demand_base
WHERE project_id IS NOT NULL
),
project_lookup AS
(
SELECT
mp.project_id,
--mp.organization_id,
mp.sr_instance_id,
mp.project_number
FROM MSC.msc_projects mp
JOIN project_keys pk
ON pk.project_id = mp.project_id
--AND pk.organization_id = mp.organization_id
AND pk.sr_instance_id = mp.sr_instance_id
WHERE mp.plan_id = -1
),
task_keys AS
(
SELECT DISTINCT
task_id,
project_id,
organization_id,
sr_instance_id
FROM supply_base
WHERE task_id IS NOT NULL
UNION
SELECT DISTINCT
task_id,
project_id,
organization_id,
sr_instance_id
FROM demand_base
WHERE task_id IS NOT NULL
),
task_lookup AS
(
SELECT /*+ MATERIALIZE */
mt.task_id,
mt.project_id,
--mt.organization_id,
mt.sr_instance_id,
mt.task_number
FROM MSC.msc_project_tasks mt
JOIN task_keys tk
ON tk.task_id = mt.task_id
AND tk.project_id = mt.project_id
--AND tk.organization_id = mt.organization_id
AND tk.sr_instance_id = mt.sr_instance_id
WHERE mt.plan_id = -1
),
item_keys AS
(
SELECT DISTINCT
base_item_id inventory_item_id
FROM item_scope
WHERE base_item_id IS NOT NULL
UNION
SELECT DISTINCT
product_family_id
FROM item_scope
WHERE product_family_id IS NOT NULL
),
item_lookup AS
(
SELECT /*+ MATERIALIZE */
mi.inventory_item_id,
mi.item_name
FROM MSC.msc_items mi
JOIN item_keys ik
ON ik.inventory_item_id = mi.inventory_item_id
),
comment_keys AS
(
SELECT DISTINCT
plan_id,
transaction_id
FROM supply_base
WHERE transaction_id IS NOT NULL
),
comment_lookup AS
(
SELECT /*+ MATERIALIZE */
mun.plan_id,
mun.transaction_id,
mun.note_text1 AS comments
FROM MSC.msc_user_notes mun
JOIN comment_keys ck
ON ck.plan_id = mun.plan_id
AND ck.transaction_id = mun.transaction_id
WHERE mun.entity_type = 'SUPPLY'
),
wip_status_lookup AS (
SELECT /*+ MATERIALIZE */
TO_NUMBER(lookup_code) AS wip_status_code,
MAX(meaning) AS wip_status_text -- Guarantees exactly 1 row per code
FROM APPLSYS.fnd_lookup_values
WHERE lookup_type = 'WIP_JOB_STATUS'
AND security_group_id = 0
AND language = USERENV('LANG')
AND enabled_flag = 'Y'
GROUP BY TO_NUMBER(lookup_code)
),
demand_type_lookup AS
(
SELECT /*+ MATERIALIZE */
TO_NUMBER(lookup_code) AS demand_type,
MAX(meaning) AS demand_type_text
FROM APPLSYS.fnd_lookup_values
WHERE lookup_type = 'MSC_DEMAND_ORIGINATION'
AND enabled_flag = 'Y'
AND language = 'US'
AND security_group_id = 0
AND TRUNC(SYSDATE) >= NVL(start_date_active, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) < NVL(end_date_active + 1, TRUNC(SYSDATE) + 1)
GROUP BY TO_NUMBER(lookup_code)
),
supply_type_lookup AS
(
SELECT /*+ MATERIALIZE */
TO_NUMBER(lookup_code) AS supply_type,
MAX(meaning) AS supply_type_text
FROM APPLSYS.fnd_lookup_values
WHERE lookup_type = 'MRP_ORDER_TYPE'
AND enabled_flag = 'Y'
AND language = 'US'
AND security_group_id = 0
AND TRUNC(SYSDATE) >= NVL(start_date_active, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) < NVL(end_date_active + 1, TRUNC(SYSDATE) + 1)
GROUP BY TO_NUMBER(lookup_code)
)
--main
SELECT
x.*
FROM
(
/* ============================================================
LEG 1: PEGGED SUPPLY-DEMAND LINKS
============================================================ */
SELECT
'Supply' AS Demand_Supply,
isc.organization_code,
pl.compile_designator AS plan_name,
isc.item_name,
isc.description AS item_description,
CASE
WHEN p.pegging_id = p.chain_id THEN 'DIRECT'
WHEN p.prev_pegging_id IS NULL
OR p.prev_pegging_id = 0 THEN 'DIRECT'
WHEN root_d.demand_id IS NOT NULL
AND root_d.demand_id = d.demand_id THEN 'DIRECT'
ELSE 'MULTI-LEVEL'
END AS match_type,
DECODE(s.release_status, 1, 'YES', 'NO') AS "For Release",
DECODE(s.firm_planned_type, 1, 'YES', 'NO') AS Firm,
NVL(stl.supply_type_text,TO_CHAR(NVL(p.supply_type,s.order_type))) AS supply_doc_type,
CASE
WHEN p.supply_type = 18 THEN TO_CHAR(p.transaction_id)
--'On-hand balance (org ' || p.organization_id || ')'
WHEN s.implement_job_name IS NOT NULL THEN
s.implement_job_name
WHEN p.supply_type IN (1, 2, 7) THEN
'PO/Req '
|| NVL(
TO_CHAR(s.disposition_id),
TO_CHAR(p.transaction_id)
)
|| NVL2(
s.purch_line_num,
' line ' || s.purch_line_num,
''
)
WHEN p.supply_type IN (3, 6) THEN
'Job '
|| NVL(
TO_CHAR(s.transaction_id),
TO_CHAR(s.disposition_id)
)
WHEN p.supply_type = 5 THEN
TO_CHAR(p.transaction_id)
ELSE TO_CHAR(p.transaction_id)
END AS supply_doc_number,
CASE p.supply_type
WHEN 18 THEN 'On hand'
WHEN 1 THEN 'PO'
WHEN 2 THEN 'Req'
WHEN 3 THEN 'Work order'
WHEN 6 THEN 'Work order'
WHEN 5 THEN 'Planned order'
ELSE 'Supply'
END
|| ' '
|| CASE
WHEN p.supply_type = 18 THEN
TO_CHAR(p.transaction_id)
WHEN s.implement_job_name IS NOT NULL THEN
s.implement_job_name
ELSE
NVL(TO_CHAR(s.disposition_id),
TO_CHAR(p.transaction_id))
END
|| ' - '
|| root_d.normalized_order_number
AS link_summary,
CASE
WHEN p.supply_type = 18 THEN
TO_CHAR(p.transaction_id)
WHEN s.implement_job_name IS NOT NULL THEN
s.implement_job_name
ELSE
NVL(TO_CHAR(s.disposition_id),
TO_CHAR(p.transaction_id))
END
AS linking_key,
--d.using_assembly_demand_date AS sugg_order_date,
s.new_order_placement_date AS "Sugg Order Date",
s.new_wip_start_date AS "Sugg Start Date",
s.supply_schedule_date AS "Sugg Due Date",
s.new_dock_date AS "Sugg Dock Date",
s.new_ship_date AS "Sugg Ship Date",
NVL(root_d.schedule_ship_date, d.schedule_ship_date) AS schedule_ship_date,
CAST(NULL AS DATE) AS schedule_arrival_date,
NVL(
root_d.promise_date,
d.promise_date
) AS promise_date,
DECODE(s.implemented_quantity, NULL,'Yes','No') Recommended,
s.QUANTITY_RATE,
s.implement_date,
s.implement_job_name,
NVL(s.implement_daily_rate, s.implement_quantity) AS "Imp Qty/Rate",
isc.planner_code,
plr.planner_description,
plr.user_name,
plr.planner_employee_name AS "Planner Employee Name",
prj.project_number AS "Project Number",
tsk.task_number AS "Task Number",
org_lkp.organization_code AS "Source Org",
TO_NUMBER(NULL) AS source_order_priority,
parent_model.item_name AS "Parent Model",
parent_model.item_name AS "Top Model",
product_family.item_name AS "Product Family",
s.release_errors release_errors,
'Y' AS is_selected_item,
isc.cum_manufacturing_lead_time,
isc.planning_sub_family,
isc.dept_wsf,
isc.product_line,
p.supply_type,
p.supply_date,
p.supply_quantity,
NVL(p.allocated_quantity, 0) AS pegged_quantity,
0 AS excess_quantity,
'PEGGED QUANTITY' AS quantity_context,
s.disposition_status_type AS supply_order_status,
NVL(s.alternate_bom_designator, pec.actual_bom_value) AS alternate_bom_designator,
NVL(s.alternate_routing_designator, pec.actual_routing_value ) AS alternate_routing_designator,
s.supplier_id,
s.supplier_site_id,
sl.supplier_name,
sl.supplier_number,
sl.supplier_site_code,
/* CASE
WHEN d.demand_id IS NULL THEN NULL
WHEN UPPER(d.order_number) LIKE '%INTERNAL ORDER%'
THEN 'Internal Order'
WHEN UPPER(d.order_number) LIKE '%TRADE%'
THEN 'Trade Order'
WHEN d.sales_order_line_id IS NOT NULL
THEN 'Sales Order'
WHEN d.using_assembly_item_id IS NOT NULL
THEN 'Dependent demand (component)'
WHEN UPPER(NVL(d.order_number, ' ')) LIKE '%FORECAST%'
THEN 'Forecast'
ELSE 'Other demand (origination_type '
|| d.origination_type
|| ')' END */
NVL( dtl.demand_type_text,TO_CHAR(d.origination_type)) AS demand_doc_type,
d.normalized_order_number AS demand_doc_number,
p.demand_date,
p.demand_quantity,
CASE
WHEN d.sales_order_line_id IS NOT NULL
OR (
d.demand_id IS NOT NULL
AND d.using_assembly_item_id IS NULL
)
THEN 'Y'
ELSE 'N'
END AS demand_is_independent,
d.using_assembly_item_id,
CASE
WHEN d.using_assembly_item_id = d.inventory_item_id
THEN isc.item_name
ELSE assy.item_name
END AS demand_doc_item,
CASE
WHEN d.using_assembly_item_id = d.inventory_item_id
THEN 'No parent assembly'
ELSE assy.description
END AS assembly_description,
CASE
WHEN d.primary_component_id = d.inventory_item_id
THEN NULL
ELSE orig_comp.item_name
END AS primary_bom_component_number,
NVL(dtl.demand_type_text,TO_CHAR(d.origination_type)) as end_demand_doc_type,
root_d.normalized_order_number AS end_demand_number,
root_d.order_number AS end_demand_order_number,
root_si.item_name AS end_demand_item,
root_d.sales_order_line_id AS end_demand_line_id,
root_p.inventory_item_id AS end_item_id,
root_p.organization_id AS end_item_org_id,
cmt.comments AS "Comments",
NVL(
root_d.customer_id,
d.customer_id
) AS customer_id,
NVL(
root_d.customer_site_id,
d.customer_site_id
) AS customer_site_id,
NVL(
root_d.normalized_order_number,
d.normalized_order_number
) AS soft_pegging_relationship,
CASE
WHEN NVL(root_d.demand_id, d.demand_id) IS NULL
THEN NULL
WHEN UPPER(
NVL(root_d.order_number, d.order_number)
) LIKE '%INTERNAL ORDER%'
THEN 'Internal Order'
WHEN UPPER(
NVL(root_d.order_number, d.order_number)
) LIKE '%TRADE%'
THEN 'Trade Order'
WHEN NVL(
root_d.sales_order_line_id,
d.sales_order_line_id
) IS NOT NULL
THEN 'Sales Order'
ELSE 'Other'
END AS soft_pegging_demand_type,
s.build_sequence AS "Build Sequence",
wsl.wip_status_text AS "WIP Status",
wsl2.wip_status_text AS "Implement Status",
s.schedule_priority AS "Order Priority",
s.planning_group AS "Planning Group",
s.schedule_group_id AS "Schedule Group",
s.implement_as AS "Implement As",
s.subinventory_code AS "Subinventory",
s.reserved_qty AS "Reserved Qty",
DECODE(s.reserved_qty, NULL, 'NO', 0, 'No', 'Yes') AS "Reserved",
s.line_id AS "Line",
s.supply_schedule_date AS "New Date",
s.supply_order_qty AS "New Qty",
s.new_wip_start_date AS "New Start Date",
ROUND(p.supply_date - p.demand_date) AS days_late,
isc.uom_code,
isc.category_name,
isc.abc_class,
isc.make_buy,
isc.full_lead_time,
isc.standard_cost,
ROUND(
NVL(p.allocated_quantity, 0)
* NVL(isc.standard_cost, 0),
2
) AS pegged_std_value,
0 AS excess_std_value,
p.unit_number,
p.demand_class,
p.pegging_id,
p.demand_id,
p.transaction_id,
p.plan_id
FROM peg p
LEFT JOIN demand_lookup d
ON d.plan_id = p.plan_id
AND d.demand_id = p.demand_id
AND d.sr_instance_id = p.sr_instance_id
LEFT JOIN supply_lookup s
ON s.plan_id = p.plan_id
AND s.transaction_id = p.transaction_id
AND s.sr_instance_id = p.sr_instance_id
LEFT JOIN supplier_lookup sl
ON sl.supplier_id = s.supplier_id
AND sl.supplier_site_id = s.supplier_site_id
AND sl.sr_instance_id = s.sr_instance_id
LEFT JOIN supplier_lookup org_lkp
ON org_lkp.sr_tp_id = s.source_organization_id
AND org_lkp.sr_instance_id = s.sr_instance_id
AND org_lkp.partner_type = 3
LEFT JOIN comment_lookup cmt
ON cmt.plan_id = s.plan_id
AND cmt.transaction_id = s.transaction_id
JOIN item_scope isc
ON isc.plan_id = p.plan_id
AND isc.inventory_item_id = p.inventory_item_id
AND isc.organization_id = p.organization_id
AND isc.sr_instance_id = p.sr_instance_id
LEFT JOIN item_lookup parent_model
ON parent_model.inventory_item_id =
isc.base_item_id
LEFT JOIN item_lookup product_family
ON product_family.inventory_item_id =
isc.product_family_id
LEFT JOIN MSC.msc_full_pegging root_p
ON root_p.plan_id = p.plan_id
AND root_p.sr_instance_id = p.sr_instance_id
AND root_p.pegging_id = p.chain_id
LEFT JOIN demand_lookup root_d
ON root_d.plan_id = root_p.plan_id
AND root_d.demand_id = root_p.demand_id
AND root_d.sr_instance_id = root_p.sr_instance_id
LEFT JOIN MSC.msc_system_items root_si
ON root_si.plan_id = root_p.plan_id
AND root_si.inventory_item_id = root_p.inventory_item_id
AND root_si.organization_id = root_p.organization_id
AND root_si.sr_instance_id = root_p.sr_instance_id
LEFT JOIN MSC.msc_plans pl
ON pl.plan_id = p.plan_id
LEFT JOIN planner_lookup plr
ON plr.planner_code = isc.planner_code
AND plr.organization_id = isc.organization_id
AND plr.sr_instance_id = isc.sr_instance_id
LEFT JOIN project_lookup prj
ON prj.project_id = s.project_id
--AND prj.organization_id = s.organization_id
AND prj.sr_instance_id = s.sr_instance_id
LEFT JOIN task_lookup tsk
ON tsk.task_id = s.task_id
AND tsk.project_id = s.project_id
--AND tsk.organization_id = s.organization_id
AND tsk.sr_instance_id = s.sr_instance_id
LEFT JOIN process_effectivity_cache pec
ON pec.plan_id = s.plan_id
AND pec.sr_instance_id = s.sr_instance_id
AND pec.process_sequence_id = s.process_seq_id
LEFT JOIN demand_type_lookup dtl
ON dtl.demand_type = d.origination_type
LEFT JOIN supply_type_lookup stl
ON stl.supply_type = p.supply_type
LEFT JOIN wip_status_lookup wsl
ON wsl.wip_status_code = s.wip_status_code
LEFT JOIN wip_status_lookup wsl2
ON wsl2.wip_status_code = s.implement_status_code
LEFT JOIN MSC.msc_system_items assy
ON assy.plan_id = d.plan_id
AND assy.inventory_item_id = d.using_assembly_item_id
AND assy.organization_id = d.organization_id
AND assy.sr_instance_id = d.sr_instance_id
LEFT JOIN MSC.msc_system_items orig_comp
ON orig_comp.plan_id = d.plan_id
AND orig_comp.inventory_item_id = d.primary_component_id
AND orig_comp.organization_id = d.organization_id
AND orig_comp.sr_instance_id = d.sr_instance_id
UNION ALL
/* ============================================================
LEG 2: EXCESS SUPPLY
============================================================ */
SELECT
'EXCESS SUPPLY' AS Demand_Supply,
si.organization_code,
pl.compile_designator AS plan_name,
si.item_name,
si.description AS item_description,
CASE
WHEN NVL(ps.pegged_qty, 0) = 0
THEN 'UNPEGGED'
ELSE 'PARTIAL EXCESS'
END AS match_type,
DECODE(s.release_status, 1, 'YES', 'NO') AS "For Release",
DECODE(s.firm_planned_type, 1, 'YES', 'NO') AS Firm,
stl.supply_type_text AS supply_doc_type,
CASE
WHEN s.order_type = 18 THEN
'On-hand balance (org ' || s.organization_id || ')'
WHEN s.implement_job_name IS NOT NULL THEN
s.implement_job_name
WHEN s.order_type IN (1, 2, 7) THEN
'PO/Req '
|| NVL(
TO_CHAR(s.disposition_id),
TO_CHAR(s.transaction_id)
)
|| NVL2(
s.purch_line_num,
' line ' || s.purch_line_num,
''
)
WHEN s.order_type IN (3, 6) THEN
'Job '
|| NVL(
TO_CHAR(s.transaction_id),
TO_CHAR(s.disposition_id)
)
WHEN s.order_type = 5 THEN
TO_CHAR(s.transaction_id)
ELSE TO_CHAR(s.transaction_id)
END AS supply_doc_number,
CASE s.order_type
WHEN 18 THEN 'On hand'
WHEN 1 THEN 'PO'
WHEN 2 THEN 'Req'
WHEN 3 THEN 'Work order'
WHEN 6 THEN 'Work order'
WHEN 5 THEN 'Planned order'
ELSE 'Supply'
END
|| ' '
|| CASE
WHEN s.order_type = 18 THEN
'(' || s.organization_id || ')'
WHEN s.implement_job_name IS NOT NULL THEN
s.implement_job_name
ELSE
NVL(
TO_CHAR(s.disposition_id),
TO_CHAR(s.transaction_id)
)
END
|| ' -> '
|| CASE
WHEN NVL(ps.pegged_qty, 0) = 0
THEN 'Excess (no demand)'
ELSE 'Partial Excess after pegging'
END AS link_summary,
s.order_number AS linking_key,
--CAST(NULL AS DATE) AS sugg_order_date,
s.new_order_placement_date AS "Sugg Order Date",
s.new_wip_start_date AS "Sugg Start Date",
s.supply_schedule_date AS "Sugg Due Date",
s.new_dock_date AS "Sugg Dock Date",
s.new_ship_date AS "Sugg Ship Date",
CAST(NULL AS DATE) AS schedule_ship_date,
CAST(NULL AS DATE) AS schedule_arrival_date ,
CAST(NULL AS DATE) AS promise_date,
DECODE(s.implemented_quantity, NULL,'Yes','No') Recommended,
s.QUANTITY_RATE,
s.implement_date,
s.implement_job_name,
NVL(s.implement_daily_rate, s.implement_quantity) AS "Imp Qty/Rate",
si.planner_code,
plr.planner_description,
plr.user_name,
plr.planner_employee_name AS "Planner Employee Name",
prj.project_number AS "Project Number",
tsk.task_number AS "Task Number",
org_lkp.organization_code AS "Source Org",
TO_NUMBER(NULL) AS source_order_priority,
parent_model.item_name AS "Parent Model",
parent_model.item_name AS "Top Model",
product_family.item_name AS "Product Family",
s.release_errors release_errors,
'Y' AS is_selected_item,
si.cum_manufacturing_lead_time,
si.planning_sub_family,
si.dept_wsf,
si.product_line,
s.order_type AS supply_type,
s.supply_schedule_date AS supply_date,
s.supply_order_qty AS supply_quantity,
0 AS pegged_quantity,
s.supply_order_qty - NVL(ps.pegged_qty, 0)
AS excess_quantity,
CASE
WHEN NVL(ps.pegged_qty, 0) = 0
THEN 'UNPEGGED SUPPLY'
ELSE 'EXCESS QUANTITY'
END AS quantity_context,
s.disposition_status_type AS supply_order_status,
--s.alternate_routing_designator,
--s.alternate_bom_designator,
NVL(s.alternate_bom_designator, pec.actual_bom_value) AS alternate_bom_designator,
NVL(s.alternate_routing_designator, pec.actual_routing_value) AS alternate_routing_designator,
s.supplier_id,
s.supplier_site_id,
sl.supplier_name,
sl.supplier_number,
sl.supplier_site_code,
CAST(NULL AS VARCHAR2(120)) AS demand_doc_type,
CAST(NULL AS VARCHAR2(240)) AS demand_doc_number,
CAST(NULL AS DATE) AS demand_date,
CAST(NULL AS NUMBER) AS demand_quantity,
'N' AS demand_is_independent,
CASE
WHEN s.order_type IN (3, 5, 6, 12, 27)
THEN s.inventory_item_id
ELSE CAST(NULL AS NUMBER)
END AS using_assembly_item_id,
si.item_name AS demand_doc_item,
CASE
WHEN s.order_type IN (3, 5, 6, 12, 27)
THEN si.description
ELSE 'No parent assembly'
END AS assembly_description,
CAST(NULL AS VARCHAR2(240))
AS primary_bom_component_number,
CAST(NULL AS VARCHAR2(120)) AS end_demand_doc_type,
CAST(NULL AS VARCHAR2(240)) AS end_demand_number,
CAST(NULL AS VARCHAR2(240)) AS end_demand_order_number,
CAST(NULL AS VARCHAR2(240)) AS end_demand_item,
CAST(NULL AS NUMBER) AS end_demand_line_id,
CAST(NULL AS NUMBER) AS end_item_id,
CAST(NULL AS NUMBER) AS end_item_org_id,
cmt.comments AS "Comments",
CAST(NULL AS NUMBER) AS customer_id,
CAST(NULL AS NUMBER) AS customer_site_id,
CASE
WHEN NVL(ps.pegged_qty, 0) = 0
THEN 'Excess'
ELSE 'Partial Excess'
END AS soft_pegging_relationship,
CAST(NULL AS VARCHAR2(120))
AS soft_pegging_demand_type,
s.build_sequence AS "Build Sequence",
wsl.wip_status_text AS "WIP Status",
wsl2.wip_status_text AS "Implement Status",
s.schedule_priority AS "Order Priority",
s.planning_group AS "Planning Group",
s.schedule_group_id AS "Schedule Group",
s.implement_as AS "Implement As",
s.subinventory_code AS "Subinventory",
s.reserved_qty AS "Reserved Qty",
DECODE(s.reserved_qty, NULL, 'NO', 0, 'No', 'Yes') AS "Reserved",
s.line_id AS "Line",
s.supply_schedule_date AS "New Date",
s.supply_order_qty AS "New Qty",
s.new_wip_start_date AS "New Start Date",
CAST(NULL AS NUMBER) AS days_late,
si.uom_code,
si.category_name,
si.abc_class,
si.make_buy,
si.full_lead_time,
si.standard_cost,
0 AS pegged_std_value,
ROUND(
(
s.supply_order_qty
- NVL(ps.pegged_qty, 0)
)
* NVL(si.standard_cost, 0),
2
) AS excess_std_value,
s.unit_number,
CAST(NULL AS VARCHAR2(120)) AS demand_class,
CAST(NULL AS NUMBER) pegging_id,
CAST(NULL AS NUMBER) demand_id,
s.transaction_id,
s.plan_id
FROM supply_base s
JOIN item_scope si
ON si.plan_id = s.plan_id
AND si.inventory_item_id = s.inventory_item_id
AND si.organization_id = s.organization_id
AND si.sr_instance_id = s.sr_instance_id
LEFT JOIN item_lookup parent_model
ON parent_model.inventory_item_id =
si.base_item_id
LEFT JOIN item_lookup product_family
ON product_family.inventory_item_id =
si.product_family_id
LEFT JOIN supplier_lookup sl
ON sl.supplier_id = s.supplier_id
AND sl.supplier_site_id = s.supplier_site_id
AND sl.sr_instance_id = s.sr_instance_id
LEFT JOIN supplier_lookup org_lkp
ON org_lkp.sr_tp_id = s.source_organization_id
AND org_lkp.sr_instance_id = s.sr_instance_id
AND org_lkp.partner_type = 3
LEFT JOIN comment_lookup cmt
ON cmt.plan_id = s.plan_id
AND cmt.transaction_id = s.transaction_id
LEFT JOIN pegged_supply ps
ON ps.plan_id = s.plan_id
AND ps.transaction_id = s.transaction_id
AND ps.sr_instance_id = s.sr_instance_id
LEFT JOIN MSC.msc_plans pl
ON pl.plan_id = s.plan_id
LEFT JOIN planner_lookup plr
ON plr.planner_code = si.planner_code
AND plr.organization_id = si.organization_id
AND plr.sr_instance_id = si.sr_instance_id
LEFT JOIN project_lookup prj
ON prj.project_id = s.project_id
--AND prj.organization_id = s.organization_id
AND prj.sr_instance_id = s.sr_instance_id
LEFT JOIN task_lookup tsk
ON tsk.task_id = s.task_id
AND tsk.project_id = s.project_id
--AND tsk.organization_id = s.organization_id
AND tsk.sr_instance_id = s.sr_instance_id
LEFT JOIN process_effectivity_cache pec
ON pec.plan_id = s.plan_id
AND pec.sr_instance_id = s.sr_instance_id
AND pec.process_sequence_id = s.process_seq_id
LEFT JOIN supply_type_lookup stl
ON stl.supply_type = s.order_type
LEFT JOIN wip_status_lookup wsl
ON wsl.wip_status_code = s.wip_status_code
LEFT JOIN wip_status_lookup wsl2
ON wsl2.wip_status_code = s.implement_status_code
WHERE s.supply_order_qty > 0
AND s.supply_order_qty - NVL(ps.pegged_qty, 0) > 0
UNION ALL
/* ============================================================
LEG 3: UNPEGGED OR PARTIALLY SHORT DEMAND
============================================================ */
SELECT
'DEMAND' AS Demand_Supply,
si.organization_code,
pl.compile_designator AS plan_name,
si.item_name,
si.description AS item_description,
CASE
WHEN NVL(pd.pegged_qty, 0) = 0
THEN 'UNPEGGED'
ELSE 'PARTIAL SHORT'
END AS match_type,
DECODE(db.release_status, 1, 'YES', 'NO') AS "For Release",
CAST(NULL AS VARCHAR2(10)) AS firm,
CAST(NULL AS VARCHAR2(240)) AS supply_doc_type,
CAST(NULL AS VARCHAR2(240)) AS supply_doc_number,
'Demand '
|| NVL(
db.normalized_order_number,
TO_CHAR(db.demand_id)
)
|| ' -> '
|| CASE
WHEN NVL(pd.pegged_qty, 0) = 0
THEN 'Unpegged (no supply)'
ELSE 'Short after pegging'
END AS link_summary,
NVL(
db.normalized_order_number,
TO_CHAR(db.demand_id)
) AS linking_key,
--db.using_assembly_demand_date AS sugg_order_date,
CAST(NULL AS DATE) AS sugg_order_date,
CAST(NULL AS DATE) AS "Sugg Start Date",
db.using_assembly_demand_date AS "Sugg Due Date",
db.promise_date AS "Sugg Dock Date",
db.schedule_ship_date AS "Sugg Ship Date",
db.schedule_ship_date,
db.schedule_arrival_date,
db.promise_date,
CAST(NULL AS VARCHAR2(3)) Recommended,
db.QUANTITY_RATE,
CAST(NULL AS DATE) AS implement_date,
CAST(NULL AS VARCHAR2(240)) AS implement_job_name,
TO_NUMBER(NULL) AS "Imp Qty/Rate",
si.planner_code,
plr.planner_description,
plr.user_name,
plr.planner_employee_name AS "Planner Employee Name",
prj.project_number AS "Project Number",
tsk.task_number AS "Task Number",
org_lkp.organization_code AS "Source Org",
CASE
WHEN db.origination_type IN (6, 7, 8, 29, 30) THEN db.order_priority
ELSE TO_NUMBER(NULL)
END AS source_order_priority,
parent_model.item_name AS "Parent Model",
parent_model.item_name AS "Top Model",
product_family.item_name AS "Product Family",
db.release_errors release_errors,
'Y' AS is_selected_item,
si.cum_manufacturing_lead_time,
si.planning_sub_family,
si.dept_wsf,
si.product_line,
CAST(NULL AS NUMBER) AS supply_type,
CAST(NULL AS DATE) AS supply_date,
CAST(NULL AS NUMBER) AS supply_quantity,
NVL(pd.pegged_qty, 0) AS pegged_quantity,
db.demand_req_qty - NVL(pd.pegged_qty, 0)
AS excess_quantity,
CASE
WHEN NVL(pd.pegged_qty, 0) = 0
THEN 'UNPEGGED DEMAND'
ELSE 'SHORT QUANTITY'
END AS quantity_context,
CAST(NULL AS NUMBER) AS supply_order_status,
CAST(NULL AS VARCHAR2(40))
AS alternate_routing_designator,
CAST(NULL AS VARCHAR2(40))
AS alternate_bom_designator,
CAST(NULL AS NUMBER) AS supplier_id,
CAST(NULL AS NUMBER) AS supplier_site_id,
CAST(NULL AS VARCHAR2(255)) AS supplier_name,
CAST(NULL AS VARCHAR2(154)) AS supplier_number,
CAST(NULL AS VARCHAR2(255)) AS supplier_site_code,
/* CASE
WHEN UPPER(db.order_number) LIKE '%INTERNAL ORDER%'
THEN 'Internal Order'
WHEN UPPER(db.order_number) LIKE '%TRADE%'
THEN 'Trade Order'
WHEN db.sales_order_line_id IS NOT NULL
THEN 'Sales Order'
WHEN db.using_assembly_item_id IS NOT NULL
THEN 'Dependent demand (component)'
WHEN UPPER(NVL(db.order_number, ' ')) LIKE '%FORECAST%'
THEN 'Forecast'
ELSE
'Other demand (origination_type '
|| db.origination_type
|| ')' END */
NVL(dtl.demand_type_text,TO_CHAR(db.origination_type)) AS demand_doc_type,
db.normalized_order_number AS demand_doc_number,
db.using_assembly_demand_date AS demand_date,
db.demand_req_qty AS demand_quantity,
CASE
WHEN db.sales_order_line_id IS NOT NULL
OR db.using_assembly_item_id IS NULL
THEN 'Y'
ELSE 'N'
END AS demand_is_independent,
db.using_assembly_item_id,
CASE
WHEN db.using_assembly_item_id = db.inventory_item_id
THEN si.item_name
ELSE assy.item_name
END AS demand_doc_item,
CASE
WHEN db.using_assembly_item_id = db.inventory_item_id
THEN 'No parent assembly'
ELSE assy.description
END AS assembly_description,
CASE
WHEN db.primary_component_id = db.inventory_item_id
THEN NULL
ELSE orig_comp.item_name
END AS primary_bom_component_number,
NVL(dtl.demand_type_text,TO_CHAR(db.origination_type)) AS end_demand_doc_type,
/*CASE WHEN db.sales_order_line_id IS NULL AND db.using_assembly_item_id IS NOT NULL
THEN CAST(NULL AS VARCHAR2(240))
ELSE */
db.normalized_order_number AS end_demand_number,
/*CASE
WHEN db.sales_order_line_id IS NULL
AND db.using_assembly_item_id IS NOT NULL
THEN CAST(NULL AS VARCHAR2(240))
ELSE */db.order_number AS end_demand_order_number,
CASE
WHEN db.sales_order_line_id IS NULL
AND db.using_assembly_item_id IS NOT NULL
THEN CAST(NULL AS VARCHAR2(240))
ELSE si.item_name
END AS end_demand_item,
db.sales_order_line_id AS end_demand_line_id,
CASE
WHEN db.sales_order_line_id IS NULL
AND db.using_assembly_item_id IS NOT NULL
THEN CAST(NULL AS NUMBER)
ELSE db.inventory_item_id
END AS end_item_id,
CASE
WHEN db.sales_order_line_id IS NULL
AND db.using_assembly_item_id IS NOT NULL
THEN CAST(NULL AS NUMBER)
ELSE db.organization_id
END AS end_item_org_id,
CAST(NULL AS VARCHAR2(4000)) AS "Comments",
db.customer_id,
db.customer_site_id,
CASE
WHEN NVL(pd.pegged_qty, 0) = 0
THEN 'Short'
ELSE 'Partial Short'
END AS soft_pegging_relationship,
CASE
WHEN UPPER(db.order_number) LIKE '%INTERNAL ORDER%'
THEN 'Internal Order'
WHEN UPPER(db.order_number) LIKE '%TRADE%'
THEN 'Trade Order'
WHEN db.sales_order_line_id IS NOT NULL
THEN 'Sales Order'
ELSE 'Other'
END AS soft_pegging_demand_type,
CAST(NULL AS NUMBER) AS "Build Sequence",
CAST(NULL AS VARCHAR2(80)) AS "WIP Status",
CAST(NULL AS VARCHAR2(80)) AS "Implement Status",
DECODE(
db.plan_id,
-1,
db.order_priority,
db.demand_priority
) AS "Order Priority",
db.planning_group AS "Planning Group",
CAST(NULL AS NUMBER) AS "Schedule Group",
CAST(NULL AS NUMBER) AS "Implement As",
CAST(NULL AS VARCHAR2(50)) AS "Subinventory",
db.reserved_quantity AS "Reserved Qty",
DECODE(db.reserved_quantity, NULL, 'NO', 0, 'No', 'Yes') AS "Reserved",
CAST(NULL AS NUMBER) AS "Line",
db.using_assembly_demand_date AS "New Date",
db.demand_req_qty AS "New Qty",
CAST(NULL AS DATE) AS "New Start Date",
CAST(NULL AS NUMBER) AS days_late,
si.uom_code,
si.category_name,
si.abc_class,
si.make_buy,
si.full_lead_time,
si.standard_cost,
ROUND(
NVL(pd.pegged_qty, 0)
* NVL(si.standard_cost, 0),
2
) AS pegged_std_value,
ROUND(
(
db.demand_req_qty
- NVL(pd.pegged_qty, 0)
)
* NVL(si.standard_cost, 0),
2
) AS excess_std_value,
CAST(NULL AS VARCHAR2(120)) AS unit_number,
db.demand_class,
CAST(NULL AS NUMBER) pegging_id,
db.demand_id,
CAST(NULL AS NUMBER) TRANSACTION_ID,
db.plan_id
FROM demand_base db
JOIN item_scope si
ON si.plan_id = db.plan_id
AND si.inventory_item_id = db.inventory_item_id
AND si.organization_id = db.organization_id
AND si.sr_instance_id = db.sr_instance_id
LEFT JOIN item_lookup parent_model
ON parent_model.inventory_item_id =
si.base_item_id
LEFT JOIN item_lookup product_family
ON product_family.inventory_item_id =
si.product_family_id
LEFT JOIN pegged_demand pd
ON pd.plan_id = db.plan_id
AND pd.demand_id = db.demand_id
AND pd.sr_instance_id = db.sr_instance_id
LEFT JOIN MSC.msc_plans pl
ON pl.plan_id = db.plan_id
LEFT JOIN planner_lookup plr
ON plr.planner_code = si.planner_code
AND plr.organization_id = si.organization_id
AND plr.sr_instance_id = si.sr_instance_id
LEFT JOIN project_lookup prj
ON prj.project_id = db.project_id
--AND prj.organization_id = db.organization_id
AND prj.sr_instance_id = db.sr_instance_id
LEFT JOIN task_lookup tsk
ON tsk.task_id = db.task_id
AND tsk.project_id = db.project_id
--AND tsk.organization_id = db.organization_id
AND tsk.sr_instance_id = db.sr_instance_id
LEFT JOIN supplier_lookup org_lkp
ON org_lkp.sr_tp_id = db.source_organization_id
AND org_lkp.sr_instance_id = db.source_org_instance_id
AND org_lkp.partner_type = 3
LEFT JOIN demand_type_lookup dtl
ON dtl.demand_type = db.origination_type
LEFT JOIN MSC.msc_system_items assy
ON assy.plan_id = db.plan_id
AND assy.inventory_item_id = db.using_assembly_item_id
AND assy.organization_id = db.organization_id
AND assy.sr_instance_id = db.sr_instance_id
LEFT JOIN MSC.msc_system_items orig_comp
ON orig_comp.plan_id = db.plan_id
AND orig_comp.inventory_item_id = db.primary_component_id
AND orig_comp.organization_id = db.organization_id
AND orig_comp.sr_instance_id = db.sr_instance_id
--WHERE db.demand_req_qty - NVL(pd.pegged_qty, 0) > 0
) x;
Comments
Post a Comment