PACKAGE BODY xxapsp0058_pkg
IS
/*************************************************************************************
NAME: XXAPSP0058_PKG. GET_WHEREUSE
PURPOSE: 1. XXAPSP0058 ON HANDS
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2012/11/11 Albert 1. Created this Package.
*************************************************************************************/
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
-- P_BU VARCHAR2 , --remove this paramenter form user request(once for all)
-- P_EMS VARCHAR2 , --(same with above)
-- P_PRODUCT_LINE VARCHAR2 , --(same with above)
p_oh VARCHAR2, --'Y/N'
p_so VARCHAR2, --'Y/N'
p_wip VARCHAR2, --'Y/N'
p_po VARCHAR2, --'Y/N'
p_mds VARCHAR2 --'Y/N'
)
IS
CURSOR c_so
IS
SELECT /*+PARALLEL */
TRUNC(sn.promise_date+1,'DAY'),
sn.inventory_item_id, mp.organization_id,
a.segment1 AS inventory_item,
oh.order_number
|| '-'
|| ol.line_number
|| '.'
|| shipment_number AS order_number,
(NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0)
) AS quantity
FROM oe_odr_lines_sn sn
INNER JOIN oe_order_lines_all ol ON ol.line_id = sn.line_id
AND ol.flow_status_code = 'AWAITING_SHIPPING'
AND TRUNC (ol.schedule_ship_date) < TRUNC (SYSDATE, 'WW') -- + 6
INNER JOIN oe_order_headers_all oh ON ol.header_id = oh.header_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id = sn.organization_id
AND msi.secondary_inventory_name = ol.subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10 --EMS
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = sn.inventory_item_id
AND mic.organization_id = sn.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
------------------------------------------------
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --sn.organization_id
--AND e.attribute12 = 'Open' -- this not a index key
--AND e.attribute10 IN (2,5) -- (same with above)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id --sn.organization_id
AND b.inventory_item_id = sn.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON a.organization_id =b.organization_id --a.segment1 AS inventory_item
AND a.inventory_item_id =b.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0) > 0;
-- MDS --
CURSOR c_mds
IS
SELECT /*+PARALLEL */
msd.inventory_item_id,
msd.organization_id,
a.segment1 AS inventory_item,
msd.schedule_designator AS order_number,
msd.schedule_quantity --schedule_quantity
FROM mrp_schedule_dates msd
INNER JOIN mtl_parameters mp ON mp.organization_id = msd.organization_id
-- AND mp.ORGANIZATION_CODE = NVL(P_EMS,mp.ORGANIZATION_CODE )
-- AND mp.ATTRIBUTE13 = NVL(P_BU, mp.ATTRIBUTE13)
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = msd.inventory_item_id
AND mic.organization_id = msd.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
-- AND mc.SEGMENT2 = NVL(P_PRODUCT_LINE, mc.SEGMENT
------------
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
--msd.organization_id --1,183,923
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
--msd.organization_id
AND b.inventory_item_id = msd.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE msd.schedule_designator LIKE '%PMALLC';
-- Work Order Order Scrap --
-- MSC_SUPPLIES 3 Work order
-- MSC_DEMANDS 17 Work Order scrap
CURSOR c_wo
IS
SELECT /*+PARALLEL */
we.wip_entity_name order_number, a.segment1 inventory_item,
mp.organization_id, wdj.primary_item_id inventory_item_id,
NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) wip_qty,
--CEIL (wdj.net_quantity * NVL (msb.shrinkage_rate, 0)) wip_scrap_qty
LEAST(CEIL (NVL(wdj.net_quantity,0) * NVL (msb.shrinkage_rate, 0)),NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) ) as wip_scrap_qty
FROM wip_dscr_jobs_sn wip
INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id = wip.wip_entity_id
AND wdj.status_type IN (1, 3, 6)
INNER JOIN wip_entities we ON we.wip_entity_id = wip.wip_entity_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id = wdj.organization_id
AND msi.secondary_inventory_name = wdj.completion_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_system_items_b msb ON msb.organization_id = mp.organization_id
AND msb.inventory_item_id = wdj.primary_item_id
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = wdj.primary_item_id
AND mic.organization_id = wdj.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
AND b.inventory_item_id = wdj.primary_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
--AND NVL (wdj.start_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
AND NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
-- Work Order Demand --Component -- [Wip ???[status ??1. 3. 6 ???????H} OK
CURSOR c_wo_demand
IS
SELECT /*+PARALLEL */
wwo.inventory_item_id,
a.segment1 AS inventory_item,
mp.organization_id, -- wwo.ORGANIZATION_ID,
we.wip_entity_name AS order_number,
NVL (wwo.required_quantity, 0)
- NVL (wwo.quantity_issued, 0) AS wo_demand_qty
FROM wip_wreq_oprs_sn wwo
INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id=wwo.wip_entity_id
AND wdj.status_type IN (1, 3, 6) --UNRELEASED, RELEASED, ON HOLD
INNER JOIN wip_entities we ON we.wip_entity_id = wwo.wip_entity_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id =wdj.organization_id
AND msi.secondary_inventory_name=wdj.completion_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = wwo.inventory_item_id
AND mic.organization_id = wwo.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --wwo.organization_id
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id --wwo.organization_id
AND b.inventory_item_id = wwo.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE NVL (wwo.required_quantity, 0) - NVL (wwo.quantity_issued, 0) <> 0;
-- MSC_SUPPLIES 1 Purchase order
-- MSC_SUPPLIES 2 Purchase requisition
-- MSC_SUPPLIES 8 PO in receiving
CURSOR c_po
IS
SELECT /*+PARALLEL */
COALESCE (po.segment1,
ro.segment1,
so.shipment_num
)
|| '-'
|| COALESCE (pl.line_num, rl.line_num, sl.line_num)
AS order_number,
CASE
WHEN po.segment1 IS NOT NULL
THEN 1
WHEN ro.segment1 IS NOT NULL
THEN 2
WHEN so.shipment_num IS NOT NULL
THEN 8
ELSE 0
END AS order_type,
sn.to_subinventory, msi.secondary_inventory_name,
mp.organization_id, sn.item_id AS inventory_item_id,
a.segment1 AS inventory_item, sn.supply_type_code, sn.quantity
FROM mtl_supply_sn sn
LEFT OUTER JOIN po_headers_all po ON sn.po_header_id =po.po_header_id
LEFT OUTER JOIN po_lines_all pl ON sn.po_line_id =pl.po_line_id
LEFT OUTER JOIN po_requisition_headers_all ro ON sn.req_header_id =ro.requisition_header_id
LEFT OUTER JOIN po_requisition_lines_all rl ON sn.req_line_id =rl.requisition_line_id
LEFT OUTER JOIN rcv_shipment_headers so ON sn.shipment_header_id=so.shipment_header_id
LEFT OUTER JOIN rcv_shipment_lines sl ON sn.shipment_line_id =sl.shipment_line_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id=COALESCE (po.org_id, ro.org_id, so.organization_id)
AND msi.secondary_inventory_name=sn.to_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = sn.item_id
AND mic.organization_id = COALESCE (po.org_id, ro.org_id, so.organization_id)
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
--COALESCE (po.org_id, ro.org_id, so.organization_id)
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
--COALESCE (po.org_id, ro.org_id, so.organization_id)
AND b.inventory_item_id = sn.item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
-- AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
INNER JOIN cux.xx_c_mcatp_rule_t zz ON zz.inventory_item_id = sn.item_id
AND zz.organization_id = COALESCE (po.org_id, ro.org_id, so.organization_id)
WHERE 1=1;
--ON HwAND
CURSOR c_onhand
IS
SELECT /*+PARALLEL */
mp.attribute1 AS ems_group,
mp.organization_code AS ems,
miq.inventory_item_id,
mp.organization_id,
a.segment1 AS inventory_item,
msi.secondary_inventory_name AS subinventory_code,
SUM (miq.transaction_quantity) AS on_hand_quantity
FROM mtl_oh_qtys_sn miq
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id =miq.organization_id
AND msi.secondary_inventory_name=miq.subinventory_code
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
-- AND mp.ORGANIZATION_CODE = NVL(P_EMS , mp.ORGANIZATION_CODE)
-- AND mp.ATTRIBUTE13 = NVL(P_BU , mp.ATTRIBUTE13)
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = miq.inventory_item_id
AND mic.organization_id = miq.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
-- AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --1,183,923
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
AND b.inventory_item_id = miq.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE 1 = 1
GROUP BY msi.secondary_inventory_name,
mp.organization_id,
mp.attribute1, -- EMS_GROUP
mp.organization_code, -- EMS
miq.inventory_item_id,
a.segment1
ORDER BY msi.secondary_inventory_name,
mp.organization_id,
mp.attribute1,
mp.organization_code,
miq.inventory_item_id,
a.segment1 ;
v_user_id NUMBER := fnd_global.user_id;
v_login_id NUMBER := fnd_global.conc_login_id;
v_bu_id NUMBER;
v_assembly_item_id NUMBER;
v_cnt NUMBER;
v_mod NUMBER := 0;
v_end_item_id NUMBER;
v_organization_id NUMBER;
v_component_item_id NUMBER;
v_counttable NUMBER;
v_char VARCHAR2 (10);
v_sql VARCHAR2 (2000);
v_string VARCHAR2 (2000);
v_plan_id NUMBER := 0;
v_instance_code VARCHAR2 (10) := 'EBS';
BEGIN
/*
-- End of DDL Script for Table CUX.XX_APS_MCATP_DEMAND_PLAN
---------???M??Temp Table ---------------
MSC_SUPPLIES 3 Work order
MSC_DEMANDS 17 Work Order scrap
MSC_SUPPLIES 15 Nonstandard job by-product
MSC_SUPPLIES 7 Non-standard job
MSC_SUPPLIES 18 On Hand
MSC_SUPPLIES 3 Work order
MSC_SUPPLIES 5 Planned order
MSC_SUPPLIES 11 Intransit shipment
MSC_SUPPLIES 1 Purchase order
MSC_SUPPLIES 2 Purchase requisition
MSC_SUPPLIES 8 PO in receiving
MSC_DEMANDS 3 Work order demand
MSC_DEMANDS 30 Sales Orders
MSC_DEMANDS 1 Planned order demand
MSC_DEMANDS 2 Non-standard job demand
MSC_DEMANDS 16 Planned order scrap
MSC_DEMANDS 17 Work Order scrap
MSC_DEMANDS 8 Manual Demand
*/
-- create_mcatp_rule; --(P_BU,P_EMS,P_PRODUCT_LINE);
v_string := 'TRUNCATE TABLE CUX.XX_APS_PCATP_DETAIL_T';
EXECUTE IMMEDIATE v_string;
v_sql :=
'DELETE CUX.XX_APS_PCATP_DETAIL WHERE SD_TYPE=:1 AND ORDER_TYPE=:2 ';
IF UPPER (NVL (p_mds, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'D', 8; --MSC_DEMANDS 8 Manual Demand
END IF;
IF UPPER (NVL (p_oh, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'S', 18; --MSC_SUPPLIES 18 On Hand
END IF;
IF UPPER (NVL (p_so, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'D', 30; --MSC_DEMANDS 30 Sales Orders
END IF;
IF UPPER (NVL (p_wip, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'S', 3; --MSC_SUPPLIES 3 Work order
EXECUTE IMMEDIATE v_sql USING 'D', 3; --MSC_DEMANDS 3 Work order demand
EXECUTE IMMEDIATE v_sql USING 'D', 17; --MSC_DEMANDS 17 Work Order scrap
END IF;
IF UPPER (NVL (p_po, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'S', 1; --MSC_SUPPLIES 1 Purchase order
EXECUTE IMMEDIATE v_sql USING 'S', 2; --MSC_SUPPLIES 2 Purchase requisition
EXECUTE IMMEDIATE v_sql USING 'S', 8; --MSC_SUPPLIES 8 PO in receiving
EXECUTE IMMEDIATE v_sql USING 'S', 0; --MSC_SUPPLIES 8 PO in receiving
END IF;
-- MDS --
DBMS_OUTPUT.put_line ('P_MDS--MSC_DEMANDS 8 Manual Demand');
v_mod := 0;
IF UPPER (NVL (p_mds, 'N')) = 'Y'
THEN
FOR r1 IN c_mds
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
--V_END_ITEM_ID := GET_WHEREUSE(R1.INVENTORY_ITEM_ID);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'D', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 8, -- ORDER_TYPE
r1.schedule_quantity,
-- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END IF;
IF UPPER (NVL (p_wip, 'N')) = 'Y'
THEN
--MSC_DEMANDS 3 Work order demand
FOR r1 IN c_wo_demand
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
--V_END_ITEM_ID := GET_WHEREUSE(R1.INVENTORY_ITEM_ID);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'D', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 3, -- ORDER_TYPE
r1.wo_demand_qty, -- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
-- MSC_SUPPLIES 3 Work order
-- MSC_DEMANDS 17 Work Order scrap
FOR r1 IN c_wo
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
--V_END_ITEM_ID := GET_WHEREUSE(R1.INVENTORY_ITEM_ID);
-- MSC_SUPPLIES 3 Work order
IF NVL (r1.wip_qty, 0) <> 0
THEN
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login,
order_number, plan_name,
------------------------------ 15
instance_code,
tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'S', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 3, -- ORDER_TYPE
r1.wip_qty,
-- QUANTITY --WIP_SCRAP_QTY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code,
-- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
END IF;
-- MSC_DEMANDS 17 Work Order scrap
IF NVL (r1.wip_scrap_qty, 0) <> 0
THEN
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login,
order_number, plan_name,
------------------------------ 15
instance_code,
tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'D', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 17, -- ORDER_TYPE
r1.wip_scrap_qty,
-- QUANTITY --WIP_SCRAP_QTY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code,
-- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
END IF;
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END IF;
IF UPPER (NVL (p_po, 'N')) = 'Y'
THEN
-- MSC_SUPPLIES 1 Purchase order
-- MSC_SUPPLIES 2 Purchase requisition
-- MSC_SUPPLIES 8 PO in receiving
FOR r1 IN c_po
LOOP
v_mod := v_mod + 1;
-- DBMS_OUTPUT.PUT_LINE('C_PO:: R1.INVENTORY_ITEM_ID='|| R1.INVENTORY_ITEM_ID);
-- V_END_ITEM_ID := GET_WHEREUSE(R1.INVENTORY_ITEM_ID);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'S', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, r1.order_type, -- ORDER_TYPE
r1.quantity,
-- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END IF;
IF UPPER (NVL (p_so, 'N')) = 'Y'
THEN
-- MSC_DEMANDS 30 Sales Orders
-- DBMS_OUTPUT.PUT_LINE('FOR R1 IN C10 LOOP');
FOR r1 IN c_so
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'D', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 30, -- ORDER_TYPE
r1.quantity, -- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END IF;
IF UPPER (NVL (p_oh, 'N')) = 'Y'
THEN
-- MSC_SUPPLIES 18 On Hand
FOR r1 IN c_onhand
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'S', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 18, -- ORDER_TYPE
r1.on_hand_quantity,
-- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
NULL, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP; --CURSOR C1
END IF;
/*
v_String :=''
||'CREATE TABLE CUX.XX_APS_PCATP_DETAIL_T AS '
||'SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID, '
||'APPS.XXAPSP0057_PKG.GET_WHEREUSE(INVENTORY_ITEM_ID,ORGANIZATION_ID)AS END_ITEM_ID '
||'FROM (SELECT DISTINCT INVENTORY_ITEM_ID,ORGANIZATION_ID '
||'FROM CUX.XX_APS_PCATP_DETAIL_T) ';
EXECUTE IMMEDIATE v_String;
*/
v_string :=
''
|| 'INSERT INTO CUX.XX_APS_PCATP_DETAIL( '
|| 'plan_id , '
|| 'sd_type ,'
|| 'inventory_item_id ,'
|| 'item_number ,'
|| 'end_item_id ,'
|| 'organization_id ,'
|| 'order_type ,'
|| 'quantity ,'
|| 'creation_date ,'
|| 'last_update_date ,'
|| 'created_by ,'
|| 'last_updated_by ,'
|| 'last_update_login ,'
|| 'order_number ,'
|| 'plan_name ,'
|| 'instance_code ,'
|| 'tune_seg_9m_code )'
|| 'SELECT '
|| 'a.plan_id ,'
|| 'a.sd_type ,'
|| 'a.inventory_item_id ,'
|| 'a.item_number ,'
|| 'b.END_ITEM_ID ,'
|| 'a.organization_id ,'
|| 'a.order_type ,'
|| 'a.quantity ,'
|| 'a.creation_date ,'
|| 'a.last_update_date ,'
|| 'a.created_by ,'
|| 'a.last_updated_by ,'
|| 'a.last_update_login ,'
|| 'a.order_number ,'
|| 'a.plan_name ,'
|| 'a.instance_code ,'
|| 'a.tune_seg_9m_code '
|| 'FROM CUX.XX_APS_PCATP_DETAIL_T a INNER JOIN '
|| '(SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID, '
|| ' APPS.XXAPSP0057_PKG.GET_WHEREUSE(INVENTORY_ITEM_ID,ORGANIZATION_ID ) AS END_ITEM_ID '
|| 'FROM (SELECT DISTINCT INVENTORY_ITEM_ID,ORGANIZATION_ID FROM CUX.XX_APS_PCATP_DETAIL_T) '
|| ') b ON a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID '
|| 'AND a.ORGANIZATION_ID=b.ORGANIZATION_ID ';
EXECUTE IMMEDIATE v_string;
COMMIT;
--?n?????z?b?{????????
--?A?I?s?o?@?? procedure, ?p?U
xxapsf0051_ebs_pkg.gen_pcatp_sum (v_plan_id);
--?o?? procedure?|?N?g?J?? Detail????,
--?????? Summary???? ( PC ATP Maintain Form?????@???e ??, GSM?i???@??????)
COMMIT;
<>
DBMS_OUTPUT.put_line ('....END....');
END main;
FUNCTION get_whereuse (p_component_item_id NUMBER, p_organization_id NUMBER)
RETURN NUMBER
IS
v_return NUMBER := 0;
v_count NUMBER := 0;
CURSOR c4 (x_component_item_id NUMBER, x_organization_id NUMBER)
IS
--SELECT * FROM (
SELECT bbo.assembly_item_id
-- , bic.component_item_id, bsc.substitute_component_id
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
-- bom_substitute_components bsc
WHERE bic.component_item_id = x_component_item_id
-- AND bsc.component_sequence_id = bic.component_sequence_id
-- AND bsc.substitute_component_id = x_component_item_id
AND bbo.organization_id = x_organization_id
ORDER BY bbo.assembly_item_id;
--) WHERE ROWNUM = 1;
CURSOR c4s (x_component_item_id NUMBER, x_organization_id NUMBER)
IS
-- SELECT * FROM (
SELECT bbo.assembly_item_id
-- , bic.component_item_id, bsc.substitute_component_id
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
INNER JOIN bom_substitute_components bsc ON bsc.component_sequence_id = bic.component_sequence_id
WHERE 1 = 1
AND bsc.substitute_component_id = x_component_item_id
AND bbo.organization_id = x_organization_id
ORDER BY bbo.assembly_item_id;
-- ) WHERE ROWNUM = 1;
v_component_item_id NUMBER := 0;
v_organization_id NUMBER := 0;
v_assembly_item_id NUMBER := 0;
BEGIN
v_assembly_item_id := p_component_item_id;
-- ?? cmponent ???X assembly
FOR r4 IN c4 (p_component_item_id, p_organization_id)
LOOP
v_assembly_item_id := r4.assembly_item_id;
SELECT COUNT (*)
INTO v_count
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
WHERE 1 = 1
AND bic.component_item_id = v_assembly_item_id
AND bbo.organization_id = p_organization_id;
IF v_count > 0
THEN
v_assembly_item_id := get_whereuse (v_assembly_item_id, p_organization_id);
END IF;
IF v_assembly_item_id <> p_component_item_id THEN
GOTO substitute;
END IF;
END LOOP;
<>
IF v_assembly_item_id = p_component_item_id
THEN --
FOR r4 IN c4s (p_component_item_id, p_organization_id)
LOOP
v_assembly_item_id := r4.assembly_item_id;
SELECT COUNT (*)
INTO v_count
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
WHERE bic.component_item_id = v_assembly_item_id
AND bbo.organization_id = p_organization_id;
IF v_count > 0
THEN
v_assembly_item_id := get_whereuse(v_assembly_item_id, p_organization_id);
END IF;
IF v_assembly_item_id <> p_component_item_id THEN
GOTO end_substitute;
END IF;
END LOOP;
END IF;
<>
-- FND_FILE.PUT_LINE(FND_FILE.OUTPUT , 'LOOP-4: ' || 'Y' ) ;
RETURN v_assembly_item_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_assembly_item_id;
END get_whereuse;
END XXAPSP0058_PKG;
IS
/*************************************************************************************
NAME: XXAPSP0058_PKG. GET_WHEREUSE
PURPOSE: 1. XXAPSP0058 ON HANDS
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2012/11/11 Albert 1. Created this Package.
*************************************************************************************/
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
-- P_BU VARCHAR2 , --remove this paramenter form user request(once for all)
-- P_EMS VARCHAR2 , --(same with above)
-- P_PRODUCT_LINE VARCHAR2 , --(same with above)
p_oh VARCHAR2, --'Y/N'
p_so VARCHAR2, --'Y/N'
p_wip VARCHAR2, --'Y/N'
p_po VARCHAR2, --'Y/N'
p_mds VARCHAR2 --'Y/N'
)
IS
CURSOR c_so
IS
SELECT /*+PARALLEL */
TRUNC(sn.promise_date+1,'DAY'),
sn.inventory_item_id, mp.organization_id,
a.segment1 AS inventory_item,
oh.order_number
|| '-'
|| ol.line_number
|| '.'
|| shipment_number AS order_number,
(NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0)
) AS quantity
FROM oe_odr_lines_sn sn
INNER JOIN oe_order_lines_all ol ON ol.line_id = sn.line_id
AND ol.flow_status_code = 'AWAITING_SHIPPING'
AND TRUNC (ol.schedule_ship_date) < TRUNC (SYSDATE, 'WW') -- + 6
INNER JOIN oe_order_headers_all oh ON ol.header_id = oh.header_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id = sn.organization_id
AND msi.secondary_inventory_name = ol.subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10 --EMS
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = sn.inventory_item_id
AND mic.organization_id = sn.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
------------------------------------------------
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --sn.organization_id
--AND e.attribute12 = 'Open' -- this not a index key
--AND e.attribute10 IN (2,5) -- (same with above)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id --sn.organization_id
AND b.inventory_item_id = sn.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON a.organization_id =b.organization_id --a.segment1 AS inventory_item
AND a.inventory_item_id =b.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0) > 0;
-- MDS --
CURSOR c_mds
IS
SELECT /*+PARALLEL */
msd.inventory_item_id,
msd.organization_id,
a.segment1 AS inventory_item,
msd.schedule_designator AS order_number,
msd.schedule_quantity --schedule_quantity
FROM mrp_schedule_dates msd
INNER JOIN mtl_parameters mp ON mp.organization_id = msd.organization_id
-- AND mp.ORGANIZATION_CODE = NVL(P_EMS,mp.ORGANIZATION_CODE )
-- AND mp.ATTRIBUTE13 = NVL(P_BU, mp.ATTRIBUTE13)
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = msd.inventory_item_id
AND mic.organization_id = msd.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
-- AND mc.SEGMENT2 = NVL(P_PRODUCT_LINE, mc.SEGMENT
------------
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
--msd.organization_id --1,183,923
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
--msd.organization_id
AND b.inventory_item_id = msd.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE msd.schedule_designator LIKE '%PMALLC';
-- Work Order Order Scrap --
-- MSC_SUPPLIES 3 Work order
-- MSC_DEMANDS 17 Work Order scrap
CURSOR c_wo
IS
SELECT /*+PARALLEL */
we.wip_entity_name order_number, a.segment1 inventory_item,
mp.organization_id, wdj.primary_item_id inventory_item_id,
NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) wip_qty,
--CEIL (wdj.net_quantity * NVL (msb.shrinkage_rate, 0)) wip_scrap_qty
LEAST(CEIL (NVL(wdj.net_quantity,0) * NVL (msb.shrinkage_rate, 0)),NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) ) as wip_scrap_qty
FROM wip_dscr_jobs_sn wip
INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id = wip.wip_entity_id
AND wdj.status_type IN (1, 3, 6)
INNER JOIN wip_entities we ON we.wip_entity_id = wip.wip_entity_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id = wdj.organization_id
AND msi.secondary_inventory_name = wdj.completion_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_system_items_b msb ON msb.organization_id = mp.organization_id
AND msb.inventory_item_id = wdj.primary_item_id
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = wdj.primary_item_id
AND mic.organization_id = wdj.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
AND b.inventory_item_id = wdj.primary_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
--AND NVL (wdj.start_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
AND NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
-- Work Order Demand --Component -- [Wip ???[status ??1. 3. 6 ???????H} OK
CURSOR c_wo_demand
IS
SELECT /*+PARALLEL */
wwo.inventory_item_id,
a.segment1 AS inventory_item,
mp.organization_id, -- wwo.ORGANIZATION_ID,
we.wip_entity_name AS order_number,
NVL (wwo.required_quantity, 0)
- NVL (wwo.quantity_issued, 0) AS wo_demand_qty
FROM wip_wreq_oprs_sn wwo
INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id=wwo.wip_entity_id
AND wdj.status_type IN (1, 3, 6) --UNRELEASED, RELEASED, ON HOLD
INNER JOIN wip_entities we ON we.wip_entity_id = wwo.wip_entity_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id =wdj.organization_id
AND msi.secondary_inventory_name=wdj.completion_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = wwo.inventory_item_id
AND mic.organization_id = wwo.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --wwo.organization_id
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id --wwo.organization_id
AND b.inventory_item_id = wwo.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE NVL (wwo.required_quantity, 0) - NVL (wwo.quantity_issued, 0) <> 0;
-- MSC_SUPPLIES 1 Purchase order
-- MSC_SUPPLIES 2 Purchase requisition
-- MSC_SUPPLIES 8 PO in receiving
CURSOR c_po
IS
SELECT /*+PARALLEL */
COALESCE (po.segment1,
ro.segment1,
so.shipment_num
)
|| '-'
|| COALESCE (pl.line_num, rl.line_num, sl.line_num)
AS order_number,
CASE
WHEN po.segment1 IS NOT NULL
THEN 1
WHEN ro.segment1 IS NOT NULL
THEN 2
WHEN so.shipment_num IS NOT NULL
THEN 8
ELSE 0
END AS order_type,
sn.to_subinventory, msi.secondary_inventory_name,
mp.organization_id, sn.item_id AS inventory_item_id,
a.segment1 AS inventory_item, sn.supply_type_code, sn.quantity
FROM mtl_supply_sn sn
LEFT OUTER JOIN po_headers_all po ON sn.po_header_id =po.po_header_id
LEFT OUTER JOIN po_lines_all pl ON sn.po_line_id =pl.po_line_id
LEFT OUTER JOIN po_requisition_headers_all ro ON sn.req_header_id =ro.requisition_header_id
LEFT OUTER JOIN po_requisition_lines_all rl ON sn.req_line_id =rl.requisition_line_id
LEFT OUTER JOIN rcv_shipment_headers so ON sn.shipment_header_id=so.shipment_header_id
LEFT OUTER JOIN rcv_shipment_lines sl ON sn.shipment_line_id =sl.shipment_line_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id=COALESCE (po.org_id, ro.org_id, so.organization_id)
AND msi.secondary_inventory_name=sn.to_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = sn.item_id
AND mic.organization_id = COALESCE (po.org_id, ro.org_id, so.organization_id)
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
--COALESCE (po.org_id, ro.org_id, so.organization_id)
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
--COALESCE (po.org_id, ro.org_id, so.organization_id)
AND b.inventory_item_id = sn.item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
-- AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
INNER JOIN cux.xx_c_mcatp_rule_t zz ON zz.inventory_item_id = sn.item_id
AND zz.organization_id = COALESCE (po.org_id, ro.org_id, so.organization_id)
WHERE 1=1;
--ON HwAND
CURSOR c_onhand
IS
SELECT /*+PARALLEL */
mp.attribute1 AS ems_group,
mp.organization_code AS ems,
miq.inventory_item_id,
mp.organization_id,
a.segment1 AS inventory_item,
msi.secondary_inventory_name AS subinventory_code,
SUM (miq.transaction_quantity) AS on_hand_quantity
FROM mtl_oh_qtys_sn miq
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id =miq.organization_id
AND msi.secondary_inventory_name=miq.subinventory_code
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
-- AND mp.ORGANIZATION_CODE = NVL(P_EMS , mp.ORGANIZATION_CODE)
-- AND mp.ATTRIBUTE13 = NVL(P_BU , mp.ATTRIBUTE13)
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = miq.inventory_item_id
AND mic.organization_id = miq.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
-- AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --1,183,923
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
AND b.inventory_item_id = miq.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE 1 = 1
GROUP BY msi.secondary_inventory_name,
mp.organization_id,
mp.attribute1, -- EMS_GROUP
mp.organization_code, -- EMS
miq.inventory_item_id,
a.segment1
ORDER BY msi.secondary_inventory_name,
mp.organization_id,
mp.attribute1,
mp.organization_code,
miq.inventory_item_id,
a.segment1 ;
v_user_id NUMBER := fnd_global.user_id;
v_login_id NUMBER := fnd_global.conc_login_id;
v_bu_id NUMBER;
v_assembly_item_id NUMBER;
v_cnt NUMBER;
v_mod NUMBER := 0;
v_end_item_id NUMBER;
v_organization_id NUMBER;
v_component_item_id NUMBER;
v_counttable NUMBER;
v_char VARCHAR2 (10);
v_sql VARCHAR2 (2000);
v_string VARCHAR2 (2000);
v_plan_id NUMBER := 0;
v_instance_code VARCHAR2 (10) := 'EBS';
BEGIN
/*
-- End of DDL Script for Table CUX.XX_APS_MCATP_DEMAND_PLAN
---------???M??Temp Table ---------------
MSC_SUPPLIES 3 Work order
MSC_DEMANDS 17 Work Order scrap
MSC_SUPPLIES 15 Nonstandard job by-product
MSC_SUPPLIES 7 Non-standard job
MSC_SUPPLIES 18 On Hand
MSC_SUPPLIES 3 Work order
MSC_SUPPLIES 5 Planned order
MSC_SUPPLIES 11 Intransit shipment
MSC_SUPPLIES 1 Purchase order
MSC_SUPPLIES 2 Purchase requisition
MSC_SUPPLIES 8 PO in receiving
MSC_DEMANDS 3 Work order demand
MSC_DEMANDS 30 Sales Orders
MSC_DEMANDS 1 Planned order demand
MSC_DEMANDS 2 Non-standard job demand
MSC_DEMANDS 16 Planned order scrap
MSC_DEMANDS 17 Work Order scrap
MSC_DEMANDS 8 Manual Demand
*/
-- create_mcatp_rule; --(P_BU,P_EMS,P_PRODUCT_LINE);
v_string := 'TRUNCATE TABLE CUX.XX_APS_PCATP_DETAIL_T';
EXECUTE IMMEDIATE v_string;
v_sql :=
'DELETE CUX.XX_APS_PCATP_DETAIL WHERE SD_TYPE=:1 AND ORDER_TYPE=:2 ';
IF UPPER (NVL (p_mds, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'D', 8; --MSC_DEMANDS 8 Manual Demand
END IF;
IF UPPER (NVL (p_oh, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'S', 18; --MSC_SUPPLIES 18 On Hand
END IF;
IF UPPER (NVL (p_so, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'D', 30; --MSC_DEMANDS 30 Sales Orders
END IF;
IF UPPER (NVL (p_wip, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'S', 3; --MSC_SUPPLIES 3 Work order
EXECUTE IMMEDIATE v_sql USING 'D', 3; --MSC_DEMANDS 3 Work order demand
EXECUTE IMMEDIATE v_sql USING 'D', 17; --MSC_DEMANDS 17 Work Order scrap
END IF;
IF UPPER (NVL (p_po, 'N')) = 'Y'
THEN
EXECUTE IMMEDIATE v_sql USING 'S', 1; --MSC_SUPPLIES 1 Purchase order
EXECUTE IMMEDIATE v_sql USING 'S', 2; --MSC_SUPPLIES 2 Purchase requisition
EXECUTE IMMEDIATE v_sql USING 'S', 8; --MSC_SUPPLIES 8 PO in receiving
EXECUTE IMMEDIATE v_sql USING 'S', 0; --MSC_SUPPLIES 8 PO in receiving
END IF;
-- MDS --
DBMS_OUTPUT.put_line ('P_MDS--MSC_DEMANDS 8 Manual Demand');
v_mod := 0;
IF UPPER (NVL (p_mds, 'N')) = 'Y'
THEN
FOR r1 IN c_mds
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
--V_END_ITEM_ID := GET_WHEREUSE(R1.INVENTORY_ITEM_ID);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'D', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 8, -- ORDER_TYPE
r1.schedule_quantity,
-- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END IF;
IF UPPER (NVL (p_wip, 'N')) = 'Y'
THEN
--MSC_DEMANDS 3 Work order demand
FOR r1 IN c_wo_demand
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
--V_END_ITEM_ID := GET_WHEREUSE(R1.INVENTORY_ITEM_ID);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'D', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 3, -- ORDER_TYPE
r1.wo_demand_qty, -- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
-- MSC_SUPPLIES 3 Work order
-- MSC_DEMANDS 17 Work Order scrap
FOR r1 IN c_wo
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
--V_END_ITEM_ID := GET_WHEREUSE(R1.INVENTORY_ITEM_ID);
-- MSC_SUPPLIES 3 Work order
IF NVL (r1.wip_qty, 0) <> 0
THEN
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login,
order_number, plan_name,
------------------------------ 15
instance_code,
tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'S', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 3, -- ORDER_TYPE
r1.wip_qty,
-- QUANTITY --WIP_SCRAP_QTY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code,
-- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
END IF;
-- MSC_DEMANDS 17 Work Order scrap
IF NVL (r1.wip_scrap_qty, 0) <> 0
THEN
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login,
order_number, plan_name,
------------------------------ 15
instance_code,
tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'D', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 17, -- ORDER_TYPE
r1.wip_scrap_qty,
-- QUANTITY --WIP_SCRAP_QTY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code,
-- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
END IF;
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END IF;
IF UPPER (NVL (p_po, 'N')) = 'Y'
THEN
-- MSC_SUPPLIES 1 Purchase order
-- MSC_SUPPLIES 2 Purchase requisition
-- MSC_SUPPLIES 8 PO in receiving
FOR r1 IN c_po
LOOP
v_mod := v_mod + 1;
-- DBMS_OUTPUT.PUT_LINE('C_PO:: R1.INVENTORY_ITEM_ID='|| R1.INVENTORY_ITEM_ID);
-- V_END_ITEM_ID := GET_WHEREUSE(R1.INVENTORY_ITEM_ID);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'S', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, r1.order_type, -- ORDER_TYPE
r1.quantity,
-- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END IF;
IF UPPER (NVL (p_so, 'N')) = 'Y'
THEN
-- MSC_DEMANDS 30 Sales Orders
-- DBMS_OUTPUT.PUT_LINE('FOR R1 IN C10 LOOP');
FOR r1 IN c_so
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'D', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 30, -- ORDER_TYPE
r1.quantity, -- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
r1.order_number, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END IF;
IF UPPER (NVL (p_oh, 'N')) = 'Y'
THEN
-- MSC_SUPPLIES 18 On Hand
FOR r1 IN c_onhand
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='
|| r1.inventory_item_id
);
INSERT INTO cux.XX_APS_PCATP_DETAIL_T
(plan_id, sd_type, inventory_item_id,
item_number, end_item_id,
------------------------------ 5
organization_id, order_type, quantity,
creation_date, last_update_date,
------------------------------ 10
created_by,
last_updated_by, last_update_login, order_number,
plan_name,
------------------------------ 15
instance_code, tune_seg_9m_code
)
VALUES (v_plan_id, -- PLAN_ID
'S', -- SD_TYPE
r1.inventory_item_id,
-- INVENTORY_ITEM_ID
r1.inventory_item, -- ITEM_NUMBER
v_end_item_id, -- END_ITEM_ID
-------------------------------05
r1.organization_id, 18, -- ORDER_TYPE
r1.on_hand_quantity,
-- QUANTITY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
-------------------------------10
v_user_id, -- CREATED_BY
v_user_id, -- LAST_UPDATED_BY
v_login_id, -- LAST_UPDATE_LOGIN
NULL, -- ORDER_NUMBER
'PC-ATP', -- PLAN_NAME
-------------------------------15
v_instance_code, -- INSTANCE_CODE
NULL -- TUNE_SEG_9M_CODE
);
-- ?C?@?d??COMMIT?@??
IF MOD (v_mod, 1000) = 0
THEN
COMMIT;
END IF;
END LOOP; --CURSOR C1
END IF;
/*
v_String :=''
||'CREATE TABLE CUX.XX_APS_PCATP_DETAIL_T AS '
||'SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID, '
||'APPS.XXAPSP0057_PKG.GET_WHEREUSE(INVENTORY_ITEM_ID,ORGANIZATION_ID)AS END_ITEM_ID '
||'FROM (SELECT DISTINCT INVENTORY_ITEM_ID,ORGANIZATION_ID '
||'FROM CUX.XX_APS_PCATP_DETAIL_T) ';
EXECUTE IMMEDIATE v_String;
*/
v_string :=
''
|| 'INSERT INTO CUX.XX_APS_PCATP_DETAIL( '
|| 'plan_id , '
|| 'sd_type ,'
|| 'inventory_item_id ,'
|| 'item_number ,'
|| 'end_item_id ,'
|| 'organization_id ,'
|| 'order_type ,'
|| 'quantity ,'
|| 'creation_date ,'
|| 'last_update_date ,'
|| 'created_by ,'
|| 'last_updated_by ,'
|| 'last_update_login ,'
|| 'order_number ,'
|| 'plan_name ,'
|| 'instance_code ,'
|| 'tune_seg_9m_code )'
|| 'SELECT '
|| 'a.plan_id ,'
|| 'a.sd_type ,'
|| 'a.inventory_item_id ,'
|| 'a.item_number ,'
|| 'b.END_ITEM_ID ,'
|| 'a.organization_id ,'
|| 'a.order_type ,'
|| 'a.quantity ,'
|| 'a.creation_date ,'
|| 'a.last_update_date ,'
|| 'a.created_by ,'
|| 'a.last_updated_by ,'
|| 'a.last_update_login ,'
|| 'a.order_number ,'
|| 'a.plan_name ,'
|| 'a.instance_code ,'
|| 'a.tune_seg_9m_code '
|| 'FROM CUX.XX_APS_PCATP_DETAIL_T a INNER JOIN '
|| '(SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID, '
|| ' APPS.XXAPSP0057_PKG.GET_WHEREUSE(INVENTORY_ITEM_ID,ORGANIZATION_ID ) AS END_ITEM_ID '
|| 'FROM (SELECT DISTINCT INVENTORY_ITEM_ID,ORGANIZATION_ID FROM CUX.XX_APS_PCATP_DETAIL_T) '
|| ') b ON a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID '
|| 'AND a.ORGANIZATION_ID=b.ORGANIZATION_ID ';
EXECUTE IMMEDIATE v_string;
COMMIT;
--?n?????z?b?{????????
--?A?I?s?o?@?? procedure, ?p?U
xxapsf0051_ebs_pkg.gen_pcatp_sum (v_plan_id);
--?o?? procedure?|?N?g?J?? Detail????,
--?????? Summary???? ( PC ATP Maintain Form?????@???e ??, GSM?i???@??????)
COMMIT;
<
DBMS_OUTPUT.put_line ('....END....');
END main;
FUNCTION get_whereuse (p_component_item_id NUMBER, p_organization_id NUMBER)
RETURN NUMBER
IS
v_return NUMBER := 0;
v_count NUMBER := 0;
CURSOR c4 (x_component_item_id NUMBER, x_organization_id NUMBER)
IS
--SELECT * FROM (
SELECT bbo.assembly_item_id
-- , bic.component_item_id, bsc.substitute_component_id
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
-- bom_substitute_components bsc
WHERE bic.component_item_id = x_component_item_id
-- AND bsc.component_sequence_id = bic.component_sequence_id
-- AND bsc.substitute_component_id = x_component_item_id
AND bbo.organization_id = x_organization_id
ORDER BY bbo.assembly_item_id;
--) WHERE ROWNUM = 1;
CURSOR c4s (x_component_item_id NUMBER, x_organization_id NUMBER)
IS
-- SELECT * FROM (
SELECT bbo.assembly_item_id
-- , bic.component_item_id, bsc.substitute_component_id
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
INNER JOIN bom_substitute_components bsc ON bsc.component_sequence_id = bic.component_sequence_id
WHERE 1 = 1
AND bsc.substitute_component_id = x_component_item_id
AND bbo.organization_id = x_organization_id
ORDER BY bbo.assembly_item_id;
-- ) WHERE ROWNUM = 1;
v_component_item_id NUMBER := 0;
v_organization_id NUMBER := 0;
v_assembly_item_id NUMBER := 0;
BEGIN
v_assembly_item_id := p_component_item_id;
-- ?? cmponent ???X assembly
FOR r4 IN c4 (p_component_item_id, p_organization_id)
LOOP
v_assembly_item_id := r4.assembly_item_id;
SELECT COUNT (*)
INTO v_count
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
WHERE 1 = 1
AND bic.component_item_id = v_assembly_item_id
AND bbo.organization_id = p_organization_id;
IF v_count > 0
THEN
v_assembly_item_id := get_whereuse (v_assembly_item_id, p_organization_id);
END IF;
IF v_assembly_item_id <> p_component_item_id THEN
GOTO substitute;
END IF;
END LOOP;
<
IF v_assembly_item_id = p_component_item_id
THEN --
FOR r4 IN c4s (p_component_item_id, p_organization_id)
LOOP
v_assembly_item_id := r4.assembly_item_id;
SELECT COUNT (*)
INTO v_count
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
WHERE bic.component_item_id = v_assembly_item_id
AND bbo.organization_id = p_organization_id;
IF v_count > 0
THEN
v_assembly_item_id := get_whereuse(v_assembly_item_id, p_organization_id);
END IF;
IF v_assembly_item_id <> p_component_item_id THEN
GOTO end_substitute;
END IF;
END LOOP;
END IF;
<
-- FND_FILE.PUT_LINE(FND_FILE.OUTPUT , 'LOOP-4: ' || 'Y' ) ;
RETURN v_assembly_item_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_assembly_item_id;
END get_whereuse;
END XXAPSP0058_PKG;
