-- Generated 18-Nov-2012 14:08:05 from APPS@EBS_8005
CREATE OR REPLACE
PACKAGE BODY xxapsp0058_pkg
IS
/*************************************************************************************
NAME: XXAPSP0057_PKG. GET_WHEREUSE
PURPOSE: 1. XXAPSP0057 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,'WW') promise_date,
sn.inventory_item_id, mp.organization_id,
a.segment1 AS inventory_item,
mp.organization_code, -- mtl_parameters.organization_code=mtl_secondary_inventories.attribute10
-- oh.order_number||'-'||ol.line_number||'.'|| shipment_number AS order_number,
SUM((NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0))) AS quantity
SELECT COUNT(*)
FROM oe_odr_lines_sn sn --1190793
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') --54850
INNER JOIN oe_order_headers_all oh ON ol.header_id = oh.header_id --54850
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 --24714
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10 --19979 --EMS mtl_secondary_inventories
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = sn.inventory_item_id --1972801
AND mic.organization_id = sn.organization_id --78959
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM') -- 18911
------------------------------------------------
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 --18895
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 --18895
WHERE NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0) > 0 --18895
GROUP BY TRUNC(sn.promise_date+1,'WW'), -- promise_date,
sn.inventory_item_id,
mp.organization_id,
a.segment1; -- AS inventory_item,
-- 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
FOR r1 IN c_mds
LOOP
v_mod := v_mod + 1;
DBMS_OUTPUT.put_line ( ' R1.INVENTORY_ITEM_ID='|| r1.inventory_item_id);
END LOOP;
--MSC_DEMANDS 3 Work order demand
FOR r1 IN c_wo_demand
LOOP
v_mod := v_mod + 1;
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 );
END LOOP;
-- 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;
END LOOP;
-- 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 );
END LOOP;
-- 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 );
END LOOP;
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;
/
-- End of DDL Script for Package Body APPS.XXAPSP0058_PKG
沒有留言:
張貼留言