R12 Ürün Ağacı Patlatma ( R12 BOM EXPLOSION)

DECLARE
l_model_item VARCHAR2 (100);
l_organization_code VARCHAR2 (100);
l_model_item_id NUMBER;
l_organization_id NUMBER;
l_cnt NUMBER DEFAULT 0;
l_err_msg VARCHAR2 (240);
l_err_code NUMBER DEFAULT 0;
l_verify_flag NUMBER DEFAULT 0;
l_online_flag NUMBER DEFAULT 2;
l_item_id NUMBER DEFAULT 0;
l_org_id NUMBER DEFAULT 0;
l_alternate VARCHAR2 (240) DEFAULT NULL;
l_list_id NUMBER DEFAULT 0;
l_order_by NUMBER DEFAULT 1;
l_grp_id NUMBER DEFAULT 0;
l_session_id NUMBER DEFAULT 0;
l_req_id NUMBER DEFAULT 0;
l_prgm_appl_id NUMBER DEFAULT -1;
l_prgm_id NUMBER DEFAULT -1;
l_levels_to_explode NUMBER DEFAULT 100;
l_bom_or_eng NUMBER DEFAULT 1;
l_impl_flag NUMBER DEFAULT 1;
l_plan_factor_flag NUMBER DEFAULT 2;
l_incl_lt_flag NUMBER DEFAULT 2;
l_explode_option NUMBER DEFAULT 2;
l_module NUMBER DEFAULT 2;
l_cst_type_id NUMBER DEFAULT 0;
l_std_comp_flag NUMBER DEFAULT 0;
l_rel_date VARCHAR2 (240);
l_comp_code VARCHAR2 (240) DEFAULT NULL;
l_expl_qty NUMBER DEFAULT 1;
 
CURSOR rec
IS
SELECT MSI.*
FROM mtl_system_items msi
,org_organization_definitions ood
WHERE msi.segment1 IN ('67300000092','67910000044',)
AND msi.organization_id = ood.organization_id
AND ood.organization_code ='ANK';
BEGIN
 
FOR x IN rec
LOOP
--BOM Date
l_rel_date := TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS');
 
-- l_grp_id is a unique identifier for this run of the exploder
SELECT bom_explosion_temp_s.NEXTVAL
INTO l_grp_id
FROM DUAL;
 
-- determine maximum levels to explode from bom_explosions
SELECT maximum_bom_level
INTO l_levels_to_explode
FROM bom_parameters
WHERE organization_id = x.organization_id;
--calling apps standard userexit for explosion of star item
apps.bompexpl.exploder_userexit (l_verify_flag,
x.organization_id,
l_order_by,
l_grp_id,
l_session_id,
l_levels_to_explode,
l_bom_or_eng,
l_impl_flag,
l_plan_factor_flag,
l_explode_option,
l_module,
l_cst_type_id,
l_std_comp_flag,
l_expl_qty,
x.inventory_item_id,
l_alternate,
l_comp_code,
l_rel_date,
NULL,
0,
l_err_msg,
l_err_code
);
 
IF (l_err_code <> 0)
THEN
DBMS_OUTPUT.put_line ( 'Error in BOM Explosion API '
|| l_err_code
|| '::'
|| l_err_msg
);
ROLLBACK;
ELSE
DBMS_OUTPUT.put_line ('Group Id is : ' || l_grp_id);
 
--Retrieve number of records
SELECT COUNT (*)
INTO l_cnt
FROM bom_explosion_temp
WHERE GROUP_ID = l_grp_id;
 
DBMS_OUTPUT.put_line ('Number of Records are : ' || l_cnt);
DBMS_OUTPUT.put_line ( 'Number of levels exploded are : '
|| l_levels_to_explode
);
COMMIT;
END IF;
END LOOP;
END;

SELECT (SELECT msi2.segment1
FROM bom_structures_b bs,mtl_system_items_b msi2
WHERE bs.bill_sequence_id = TOP_BILL_SEQUENCE_ID
AND bs.organization_id = 1794
AND bs.assembly_item_id = msi2.inventory_item_id
AND bs.organization_id = msi2.organization_id)
enbaba_govde,
PLAN_LEVEL,
msi.SEGMENT1 item,
msi.description,
bet.effectivity_date,
bet.disable_date,
flk.meaning item_type,
DECODE (msi.PLANNING_MAKE_BUY_CODE, 2, 'Buy', 'Make')
make_buy_code,
msi.inventory_item_id,
bet.component_code,
BET.COMPONENT_QUANTITY,
ITEM_NUM,
DECODE (BET.WIP_SUPPLY_TYPE,
1, 'PUSH',
2, 'ASSEMBLY PULL',
3, 'OPERATION PULL',
4, 'BULK',
5, 'SUPPLIER',
6, 'PHANTOM',
7, 'BASED ON BILL')
WIP_SUPPLY_TYPE,
SUPPLY_SUBINVENTORY
FROM bom_explosion_temp bet,
mtl_system_items msi,
fnd_lookup_values_vl flk
WHERE msi.inventory_item_id = bet.component_item_id
AND msi.organization_id = bet.organization_id
AND flk.lookup_type = 'ITEM_TYPE'
AND flk.lookup_code = msi.item_type
AND plan_level<>0
ORDER BY 1,2,12