[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_quantity_of_children_tbl.DELETE;
g_total_qty_at_next_level_tbl.DELETE;
g_parent_sort_order_tbl.DELETE;
SELECT COUNT(1) INTO l_count FROM ego_internal_people_v
WHERE user_name = p_user_name;
SELECT 'X' INTO l_exists
FROM fnd_form_functions functions,
fnd_menu_entries cmf,
fnd_menus menus
WHERE functions.function_name = p_function_name
AND functions.function_id = cmf.function_id
AND menus.menu_id = cmf.menu_id
AND menus.menu_name = FND_PROFILE.VALUE('EGO_INTERNAL_USER_DEFAULT_ROLE');
SELECT 'Y' INTO l_result FROM mtl_item_revisions_b WHERE inventory_item_id = p_inventory_item_id AND
organization_id = p_organization_id AND revision_id = p_revision_id;
SELECT revision INTO l_revision FROM mtl_item_revisions_b WHERE revision_id = p_revision_id;
SELECT revision, revision_id,revision_label FROM mtl_item_revisions_b WHERE
inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
effectivity_date <= p_effectivity_date
AND ((BOM_GLOBALS.get_show_Impl_comps_only = 'Y' AND implementation_date IS NOT NULL) OR BOM_GLOBALS.get_show_Impl_comps_only = 'N') -- added for Bug 7242865
ORDER BY effectivity_date DESC;
SELECT BBOM_C.bill_sequence_id bill_seq_id FROM bom_structures_b BBOM_C
WHERE BBOM_C.assembly_item_id = p_pk1_value AND BBOM_C.organization_id = p_organization_id AND
BBOM_C.alternate_bom_designator = p_top_alternate_designator;
SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
WHERE nvl(BBOM_C.obj_name,'EGO_ITEM') = nvl(p_obj_name,'EGO_ITEM')
AND BBOM_C.pk1_value = p_pk1_value
AND nvl(BBOM_C.pk2_value,'-1') = nvl(p_pk2_value,'-1')
AND BBOM_C.organization_id = p_organization_id
AND nvl(BBOM_C.alternate_bom_designator, 'NONE') = nvl(p_top_alternate_designator, 'NONE');
SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
WHERE BBOM_C.assembly_item_id = p_pk1_value AND BBOM_C.organization_id = p_organization_id AND
BBOM_C.alternate_bom_designator IS NULL;
SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
WHERE BBOM_C.assembly_item_id = p_pk1_value AND BBOM_C.organization_id = p_organization_id AND
BBOM_C.alternate_bom_designator = p_top_alternate_designator;
SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
WHERE BBOM_C.assembly_item_id = p_pk1_value AND BBOM_C.organization_id = p_organization_id AND
BBOM_C.alternate_bom_designator IS NULL;
SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
WHERE BBOM_C.obj_name = P_OBJ_NAME AND BBOM_C.pk1_value = p_pk1_value AND
BBOM_C.organization_id = p_organization_id AND BBOM_C.alternate_bom_designator is NULL;
SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
WHERE BBOM_C.obj_name = P_OBJ_NAME AND BBOM_C.pk1_value = p_pk1_value AND
BBOM_C.organization_id = p_organization_id AND BBOM_C.alternate_bom_designator = p_top_alternate_designator;
SELECT
ecp.policy_char_value INTO l_change_policy_char_val
FROM
MTL_SYSTEM_ITEMS ITEM_DTLS, ENG_CHANGE_POLICIES_V ECP, Bom_Structures_b bsb
WHERE
ecp.policy_object_pk1_value =
(SELECT TO_CHAR(ic.item_catalog_group_id)
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS (SELECT olc.object_classification_code CatalogId
FROM EGO_OBJ_TYPE_LIFECYCLES olc
WHERE olc.object_id = (SELECT OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM')
AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
AND olc.object_classification_code = ic.item_catalog_group_id
)
AND ROWNUM = 1
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
AND ecp.policy_object_pk3_value = ITEM_DTLS.current_phase_id
AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
AND ecp.attribute_code = 'STRUCTURE_TYPE'
AND bsb.Structure_Type_id = ecp.attribute_number_value
AND bsb.Assembly_item_id = ITEM_DTLS.inventory_item_id
AND bsb.organization_id = ITEM_DTLS.organization_id
AND bsb.Bill_Sequence_id = p_bill_seq_id;
* Procedure : Insert_Attachments
* Scope : Local
* Parameters : p_group_id
* Purpose : This procedure is called at the end of the explosion call to insert attachments
* for all the nodes in the tree
* The sort order of the attachment will be computed as sort_order + rowcount of the attachment
* so that it pushes the attachments as children of the component
*
********************************************************************************************/
PROCEDURE Insert_Attachments( p_group_id IN NUMBER
, p_plan_level IN NUMBER
)
IS
BEGIN
INSERT INTO BOM_EXPLOSIONS_ALL
( top_bill_sequence_id
,bill_sequence_id
,organization_id
,explosion_type
,component_sequence_id
,component_item_id
,plan_level
,sort_order
,creation_date
,created_by
,last_update_date
,last_updated_by
,top_item_id
,basis_type
,component_quantity
,assembly_item_id
,item_num
,comp_bill_seq_id
,group_id
,alternate_bom_designator
,parent_sort_order
,structure_type_id
,pk1_value
,hgrid_flag
,revision_id
,effectivity_control
,access_flag
,line_id
,obj_name
,exploded_option
,rexplode_flag
,exploded_date
,exploded_unit_number
,exploded_end_item_rev
)
SELECT
BET.TOP_BILL_SEQUENCE_ID,
BET.BILL_SEQUENCE_ID,
BET.ORGANIZATION_ID,
BET.EXPLOSION_TYPE,
ATDOCS.SEQ_NUM COMPONENT_SEQUENCE_ID,
ATDOCS.ATTACHED_DOCUMENT_ID COMPONENT_ITEM_ID,
plan_level +1 ,
bet.sort_order||'99'||LPAD(ROWNUM, 6, '0') SORT_ORDER,
BET.CREATION_DATE ,
BET.CREATED_BY ,
ATDOCS.LAST_UPDATE_DATE ,
ATDOCS.LAST_UPDATED_BY ,
BET.TOP_ITEM_ID,
1 BASIS_TYPE,
1 COMPONENT_QUANTITY,
BET.COMPONENT_ITEM_ID ASSEMBLY_ITEM_ID ,
ATDOCS.ATTACHED_DOCUMENT_ID ITEM_NUM ,
BET.COMP_BILL_SEQ_ID ,
BET.GROUP_ID ,
BOM_GLOBALS.GET_ALTERNATE(BET.BILL_SEQUENCE_ID) ALT_BOM_DESG,
BET.SORT_ORDER PARENT_SORT_ORDER,
BET.STRUCTURE_TYPE_ID STRUCTURE_TYPE_ID ,
TO_CHAR(ATDOCS.ATTACHED_DOCUMENT_ID) PK1_VALUE,
BET.HGRID_FLAG HGRID_FLAG ,
BET.REVISION_ID REVISION_ID ,
BET.EFFECTIVITY_CONTROL EFFECTIVITY_CONTROL,
BET.ACCESS_FLAG ACCESS_FLAG,
ATDOCS.category_id,
'ATTACHMENT',
BET.EXPLODED_OPTION,
0, -- default insert attachment with rexplode flag of 0
BET.exploded_date,
BET.exploded_unit_number,
BET.exploded_end_item_rev
FROM BOM_EXPLOSIONS_ALL BET ,
FND_ATTACHED_DOCUMENTS ATDOCS,
FND_DOCUMENTS_TL DOCTL
WHERE ATDOCS.DOCUMENT_ID = DOCTL.DOCUMENT_ID
AND DOCTL.LANGUAGE = USERENV('LANG')
AND ( ( ATDOCS.ENTITY_NAME = 'MTL_SYSTEM_ITEMS'
AND ATDOCS.PK1_VALUE = TO_CHAR(BET.ORGANIZATION_ID)
AND ATDOCS.PK2_VALUE = TO_CHAR(BET.COMPONENT_ITEM_ID) )
OR
( ATDOCS.ENTITY_NAME = 'MTL_ITEM_REVISIONS'
AND ATDOCS.PK1_VALUE = TO_CHAR(BET.ORGANIZATION_ID)
AND ATDOCS.PK2_VALUE = TO_CHAR(BET.COMPONENT_ITEM_ID)
AND ATDOCS.PK3_VALUE = BET.REVISION_ID ) )
AND ATDOCS.CATEGORY_ID IN
( SELECT BIA.attach_category_id FROM BOM_ITEM_ATTACH_CATEGORY_ASSOC BIA
WHERE BIA.STRUCTURE_TYPE_ID IN
( SELECT bst1.structure_type_id
FROM BOM_STRUCTURE_TYPES_B bst1
CONNECT BY PRIOR bst1.parent_structure_type_id = bst1.structure_type_id
START WITH bst1.structure_type_id =
( select strb.structure_type_id
from bom_structures_b strb
where strb.bill_sequence_id = bet.bill_sequence_id
)
)
)
AND group_id = p_group_id
AND plan_level = p_plan_level
AND NVL(obj_name,'EGO_ITEM') = 'EGO_ITEM';
for c in (select item_name from bom_explosions_v where explode_group_id = p_group_id
and plan_level = 0
)
loop
Dbms_Output.put_line('file name: ' || c.item_name);
END Insert_Attachments;
SELECT '%'||d.exclusion_path || '%'
BULK COLLECT INTO exclusion_t
FROM bom_rules_b r,
bom_exclusion_rule_def d
WHERE d.rule_id = r.rule_id
AND d.from_revision_id IS NULL
AND d.implementation_date IS NOT NULL
AND d.disable_date IS NULL
AND d.acd_type = 1
AND r.bill_sequence_id IN
(SELECT bill_sequence_id
FROM BOM_EXPLOSIONS_ALL
WHERE group_id = p_Group_Id
);
UPDATE BOM_EXPLOSIONS_ALL
SET is_excluded_by_rule = NULL
WHERE group_id = p_Group_Id
AND is_excluded_by_rule = 'Y';
UPDATE BOM_EXPLOSIONS_ALL
SET reapply_exclusions = NULL
WHERE group_id = p_Group_Id AND plan_level = 0;
UPDATE BOM_EXPLOSIONS_ALL
SET is_excluded_by_rule = 'Y'
WHERE group_id = p_Group_Id
AND new_component_code like exclusion_t(i);
SELECT '%'||d.exclusion_path || '%'
BULK COLLECT INTO exclusion_t
FROM bom_rules_b r,
bom_exclusion_rule_def d
WHERE d.rule_id = r.rule_id
AND d.from_revision_id IS NULL
AND d.implementation_date IS NOT NULL
AND d.disable_date IS NULL
AND d.acd_type = 1
AND r.bill_sequence_id = p_bill_sequence_id;
UPDATE BOM_EXPLOSIONS_ALL
SET is_excluded_by_rule = 'Y'
WHERE group_id IN
(SELECT t.Group_Id FROM BOM_EXPLOSIONS_ALL t
WHERE t.bill_sequence_id = p_bill_sequence_id
)
AND new_component_code like exclusion_t(i);
UPDATE BOM_EXPLOSIONS_ALL
SET reapply_exclusions = 'Y'
WHERE Top_bill_sequence_id IN
(SELECT Top_bill_sequence_id FROM BOM_EXPLOSIONS_ALL
WHERE bill_sequence_id = p_bill_sequence_id
)
AND plan_level = 0;
SELECT /*+ LEADING(bet) */
BET.TOP_BILL_SEQUENCE_ID TBSI,
BOM.BILL_SEQUENCE_ID BSI,
BOM.COMMON_BILL_SEQUENCE_ID CBSI,
nvl(BOM.COMMON_ORGANIZATION_ID,BOM.ORGANIZATION_ID) COI,
BOM.ORGANIZATION_ID OI,
BIC.COMPONENT_SEQUENCE_ID CSI,
BIC.PK1_VALUE CID,
BIC.BASIS_TYPE BT,
BIC.COMPONENT_QUANTITY CQ,
C_LEVEL PLAN_LEVEL,
(BIC.COMPONENT_QUANTITY * decode(BIC.BASIS_TYPE, 1,BET.EXTENDED_QUANTITY,1) *
decode(c_plan_factor_flag, 1, BIC.PLANNING_FACTOR/100, 1) /
decode(BIC.COMPONENT_YIELD_FACTOR, 0, 1,BIC.COMPONENT_YIELD_FACTOR)) EQ,
TO_CHAR(NULL) SO,
C_GRP_ID GROUP_ID,
BET.TOP_ALTERNATE_DESIGNATOR TAD,
BIC.COMPONENT_YIELD_FACTOR CYF,
BET.TOP_ITEM_ID TID,
BET.COMPONENT_CODE CC,
BIC.INCLUDE_IN_COST_ROLLUP IICR,
BET.LOOP_FLAG LF,
BIC.PLANNING_FACTOR PF,
BIC.OPERATION_SEQ_NUM OSN,
BIC.BOM_ITEM_TYPE BIT,
BET.BOM_ITEM_TYPE PBIT,
--to_char(BET.COMPONENT_ITEM_ID) PAID,
BET.PK1_VALUE PAID,
BOM.ALTERNATE_BOM_DESIGNATOR, -- for routing
BIC.WIP_SUPPLY_TYPE WST,
BIC.ITEM_NUM ITN,
BIC.EFFECTIVITY_DATE ED,
BIC.DISABLE_DATE DD,
/*
Greatest(BIC.EFFECTIVITY_DATE,Nvl(BET.EFFECTIVITY_DATE,BIC.EFFECTIVITY_DATE)) TED,
Least(Nvl(BIC.DISABLE_DATE,BET.DISABLE_DATE),Nvl(BET.DISABLE_DATE,BIC.DISABLE_DATE)) TDD,
*/
/* When there is a fixed parent rev, then the trimmed eff dt and trimmed disable dt are same as eff date
and dis date, as the effectivity check is anyways reapplied in the view */
decode(BET.COMP_FIXED_REV_HIGH_DATE,
null,
Greatest(BIC.EFFECTIVITY_DATE,Nvl(BET.TRIMMED_EFFECTIVITY_DATE,BIC.EFFECTIVITY_DATE)),
BIC.EFFECTIVITY_DATE) TED,
--Greatest(BIC.EFFECTIVITY_DATE,Nvl(BET.TRIMMED_EFFECTIVITY_DATE,BIC.EFFECTIVITY_DATE)) TED,
decode(BET.COMP_FIXED_REV_HIGH_DATE,
null,
Least(Nvl(BIC.DISABLE_DATE,BET.TRIMMED_DISABLE_DATE),Nvl(BET.TRIMMED_DISABLE_DATE,BIC.DISABLE_DATE)),
BIC.DISABLE_DATE) TDD,
--Least(Nvl(BIC.DISABLE_DATE,BET.TRIMMED_DISABLE_DATE),Nvl(BET.TRIMMED_DISABLE_DATE,BIC.DISABLE_DATE)) TDD,
BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
BIC.TO_END_ITEM_UNIT_NUMBER EUN,
/*
Greatest(BIC.FROM_END_ITEM_UNIT_NUMBER,Nvl(BET.FROM_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)) TFUN,
Least(Nvl(BIC.TO_END_ITEM_UNIT_NUMBER,BET.TO_END_ITEM_UNIT_NUMBER),Nvl(BET.TO_END_ITEM_UNIT_NUMBER,BIC.TO_END_ITEM_UNIT_NUMBER)) TEUN,
*/
Greatest(BIC.FROM_END_ITEM_UNIT_NUMBER,Nvl(BET.TRIMMED_FROM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)) TFUN,
Least(Nvl(BIC.TO_END_ITEM_UNIT_NUMBER,BET.TRIMMED_TO_UNIT_NUMBER),Nvl(BET.TRIMMED_TO_UNIT_NUMBER,BIC.TO_END_ITEM_UNIT_NUMBER)) TEUN,
BIC.IMPLEMENTATION_DATE ID,
--decode(BIC.IMPLEMENTATION_DATE,null,BIC.IMPLEMENTATION_DATE,decode(BET.IMPLEMENTATION_DATE,null,BET.IMPLEMENTATION_DATE,BIC.IMPLEMENTATION_DATE)) ID,
--decode(BIC.IMPLEMENTATION_DATE,null,null,decode(BET.IMPLEMENTATION_DATE,null,null,BIC.IMPLEMENTATION_DATE)) ID,
--decode(BET.IMPLEMENTATION_DATE,null,null,BIC.IMPLEMENTATION_DATE) ID,
BIC.OPTIONAL OPT,
BIC.SUPPLY_SUBINVENTORY SS,
BIC.SUPPLY_LOCATOR_ID SLI,
BIC.COMPONENT_REMARKS CR,
BIC.CHANGE_NOTICE CN,
--decode(BIC.IMPLEMENTATION_DATE,null,BIC.CHANGE_NOTICE,decode(BET.IMPLEMENTATION_DATE,null,BET.CHANGE_NOTICE,BIC.CHANGE_NOTICE)) CN,
--decode(BET.IMPLEMENTATION_DATE,null,BET.CHANGE_NOTICE,BIC.CHANGE_NOTICE) CN,
BIC.OPERATION_LEAD_TIME_PERCENT OLTP,
BIC.MUTUALLY_EXCLUSIVE_OPTIONS MEO,
BIC.CHECK_ATP CATP,
BIC.REQUIRED_TO_SHIP RTS,
BIC.REQUIRED_FOR_REVENUE RFR,
BIC.INCLUDE_ON_SHIP_DOCS IOSD,
BIC.LOW_QUANTITY LQ,
BIC.HIGH_QUANTITY HQ,
BIC.SO_BASIS SB,
BET.OPERATION_OFFSET,
BET.CURRENT_REVISION,
BET.LOCATOR,
BIC.ATTRIBUTE_CATEGORY,
BIC.ATTRIBUTE1,
BIC.ATTRIBUTE2,
BIC.ATTRIBUTE3,
BIC.ATTRIBUTE4,
BIC.ATTRIBUTE5,
BIC.ATTRIBUTE6,
BIC.ATTRIBUTE7,
BIC.ATTRIBUTE8,
BIC.ATTRIBUTE9,
BIC.ATTRIBUTE10,
BIC.ATTRIBUTE11,
BIC.ATTRIBUTE12,
BIC.ATTRIBUTE13,
BIC.ATTRIBUTE14,
BIC.ATTRIBUTE15,
BIC.obj_name,
BIC.pk1_value,
BIC.pk2_value,
BIC.pk3_value,
BIC.pk4_value,
BIC.pk5_value,
BIC.from_end_item_rev_id FEREVID,
BIC.from_end_item_minor_rev_id FEMREVID,
BIC.to_end_item_rev_id TEREVID,
BIC.to_end_item_minor_rev_id TEMREVID,
BET.NEW_COMPONENT_CODE CLCC,
BET.SORT_ORDER PARENT_SORT_ORDER,
to_number(NULL) CCBSI,
BOM_EXPLODER_PUB.Get_Comp_Bill_Seq_Id (BIC.OBJ_NAME, BET.TOP_ALTERNATE_DESIGNATOR,
--NVL(BET.COMMON_ORGANIZATION_ID,BET.ORGANIZATION_ID),
BET.ORGANIZATION_ID,
BIC.pk1_value,BIC.pk2_value) CBSID, -- comp_bill_seq_id
'T' ACFLAG,
BOM.ASSEMBLY_TYPE AST,
to_char(NULL) REVISION_LABEL,
to_number(NULL) REVISION_ID,
BOM.EFFECTIVITY_CONTROL BEFC,
to_number(NULL) OREVID,
to_number(NULL) MREVID,
to_char(NULL) MREVCODE,
BIC.FROM_OBJECT_REVISION_ID FORI,
BIC.FROM_MINOR_REVISION_ID FMRI,
BIC.TO_OBJECT_REVISION_ID TORI,
BIC.TO_MINOR_REVISION_ID TMRI,
/* If the BOM is commoned across org, then do not pick up the fixed component item revision id */
/*DECODE( SIGN(BET.ORGANIZATION_ID - BET.COMMON_ORGANIZATION_ID),
0,
BIC.COMPONENT_ITEM_REVISION_ID,
NULL) COMPONENT_ITEM_REVISION_ID,*/
--Commented by arudresh for bug 5235768. If a component exists as a fixed rev, the rev must exist
--in all orgs in which the BOM is commoned. This check is enforced during common bom creation.
BIC.COMPONENT_ITEM_REVISION_ID,
BIC.COMPONENT_MINOR_REVISION_ID,
BOM.IMPLEMENTATION_DATE,
BET.TOP_GTIN_NUMBER TGTIN,
BET.TOP_GTIN_DESCRIPTION TGTIN_DESC,
BET.TOP_TRADE_ITEM_DESCRIPTOR TTRADE_DESC,
BET.GTIN_NUMBER PGTIN,
BET.GTIN_DESCRIPTION PGTIN_DESC,
BET.TRADE_ITEM_DESCRIPTOR PTRADE_DESC,
BIC.CREATION_DATE CRDATE,
BIC.CREATED_BY CRBY,
BIC.LAST_UPDATE_DATE LUDATE,
BIC.LAST_UPDATED_BY LUBY,
BIC.AUTO_REQUEST_MATERIAL AREQ,
decode(nvl(comp_common_bill_seq_id,'0'),'0','0','1') REEXPLODE,
BIC.ACD_TYPE ACD,
--decode(BIC.IMPLEMENTATION_DATE,null,BIC.ACD_TYPE,decode(BET.IMPLEMENTATION_DATE,null,BET.ACD_TYPE,BIC.ACD_TYPE)) ACD,
--decode(BET.IMPLEMENTATION_DATE,null,BET.ACD_TYPE,BIC.ACD_TYPE) ACD,
BIC.QUANTITY_RELATED QTR,
'ALLOWED',--BET.CHANGE_POLICY_VALUE,
BET.EXPLODED_OPTION EXPOP,
BOM.STRUCTURE_TYPE_ID STYPE,
BET.COMP_FIXED_REV_HIGH_DATE CRHGDT,
NVL(BET.COMPONENT_ITEM_REVISION_ID,BET.COMP_FIXED_REVISION_ID) FPR,
BET.COMPONENT_SEQUENCE_ID PCSEQ,
BOM.IS_PREFERRED,
decode(BET.PARENT_IMPLEMENTATION_DATE,null,BET.PARENT_IMPLEMENTATION_DATE,BET.IMPLEMENTATION_DATE) PID,
NVL( DECODE(BET.IMPLEMENTATION_DATE,null,BET.CHANGE_NOTICE,null), BET.PARENT_CHANGE_NOTICE) PCN ,
BOM.SOURCE_BILL_SEQUENCE_ID SBSI,
BIC.COMMON_COMPONENT_SEQUENCE_ID CCSI,
to_number(NULL) CSBSI,
to_number(NULL) COMP_EFFECTIVITY_CONTROL
FROM
--BOM_PLM_EXPLOSION_TEMP BET,
BOM_EXPLOSIONS_ALL BET,
BOM_STRUCTURES_B BOM,
BOM_COMPONENTS_B BIC
WHERE BET.GROUP_ID = c_grp_id
AND BET.PLAN_LEVEL = c_level - 1
/* Do not explode the component if it is a pending change (disable, change)) */
AND (BET.PLAN_LEVEL = 0
OR BET.IMPLEMENTATION_DATE IS NOT NULL
OR BET.ACD_TYPE = 1)
AND BET.REXPLODE_FLAG = 1
AND BET.ACCESS_FLAG = 'T'
AND BET.COMP_BILL_SEQ_ID IS NOT NULL
AND BET.COMP_BILL_SEQ_ID = BOM.BILL_SEQUENCE_ID
-- Link BOM AND Components
AND BOM.COMMON_BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
-- This check is valid only IF the BOM AND Component both are inventory items
AND ( (BET.obj_name IS NULL AND BIC.obj_name IS NULL
AND (c_std_comp_flag = 1 -- only std components
AND BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 2)
OR
(c_std_comp_flag = 2)
OR
(c_std_comp_flag = 3 AND nvl(BET.BOM_ITEM_TYPE, 1) IN (1,2)
AND (BIC.BOM_ITEM_TYPE IN (1,2)
OR
(BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 1)))
) OR 1=1 )
AND ( (c_bom_or_eng = 1 AND BOM.ASSEMBLY_TYPE = 1)
OR
(c_bom_or_eng = 2)
)
--uncommented by arudresh for bug: 4422266
-- whether to include option classes AND models under a standard item
-- special logic added at CST request
-- This check is valid only IF the BOM AND Component both are inventory items
AND ( (BET.obj_name IS NULL AND BIC.obj_name IS NULL
AND (c_incl_oc = 1)
or
(c_incl_oc = 2 AND
( BET.BOM_ITEM_TYPE = 4 AND BIC.BOM_ITEM_TYPE = 4)
OR ( BET.BOM_ITEM_TYPE <> 4)))
OR 1 = 1)
--change made for P4Telco CMR, bug# 8761845
AND ( (c_std_bom_explode_flag = 'N' AND BET.BOM_ITEM_TYPE <> 4)
OR (c_std_bom_explode_flag = 'Y')
)
-- do not explode IF immediate parent is standard AND current
-- component is option class or model - special logic for config items
AND ( (BET.obj_name IS NULL
AND NOT ( BET.PARENT_BOM_ITEM_TYPE = 4 AND BET.BOM_ITEM_TYPE IN (1,2)))
OR (BET.obj_name IS NOT NULL))
AND (
( NVL(BOM.EFFECTIVITY_CONTROL,1) = 2 -- Unit/Serial Effectivity
AND ( (c_explode_option = 1) -- ALL
OR (c_explode_option IN (2,3) AND BIC.DISABLE_DATE IS NULL
AND BIC.from_end_item_unit_number IS NOT NULL
AND ( (c_explode_option = 2
AND unit_number >= BIC.from_end_item_unit_number
AND (BIC.to_end_item_unit_number is null OR unit_number <= Nvl( BIC.to_end_item_unit_number, unit_number)))--bug14116670
OR
(c_explode_option = 3
AND (BIC.to_end_item_unit_number is null OR unit_number <= Nvl( BIC.to_end_item_unit_number, unit_number)))--bug14116670
)
AND ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
OR c_impl_flag = 2 ))
)
)
OR
( NVL(BOM.EFFECTIVITY_CONTROL,1) = 4 -- End Item rev effectivity
AND ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
OR c_impl_flag = 2 )
AND
( (c_explode_option = 1) -- ALL
/*Have separated the logic of CURRENT and CURRENT and FUTURE for bug 8635467 with base bug 8628001*/
OR (C_EXPLODE_OPTION = 3 -- Current and Future
AND BIC.DISABLE_DATE IS NULL
AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
AND ((BET.PLAN_LEVEL > 0
AND EXISTS (SELECT NULL
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
AND ORGANIZATION_ID = BET.ORGANIZATION_ID
AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
AND (BIC.TO_END_ITEM_REV_ID IS NULL
OR (NVL(BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(BET.COMPONENT_ITEM_ID,BET.ORGANIZATION_ID,C_REV_DATE),
(SELECT STARTING_REVISION
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = BET.ORGANIZATION_ID)) <= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
AND ORGANIZATION_ID = BET.ORGANIZATION_ID
AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))
OR (EXISTS (SELECT NULL
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
AND (BIC.TO_END_ITEM_REV_ID IS NULL
OR (BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(L_END_ITEM_ID,L_END_ITEM_ORG_ID,C_REV_DATE) <= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))))
OR (C_EXPLODE_OPTION = 2 --Current
AND BIC.DISABLE_DATE IS NULL
AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
AND ((BET.PLAN_LEVEL > 0
AND EXISTS (SELECT NULL
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
AND ORGANIZATION_ID = BET.ORGANIZATION_ID
AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
AND ((NVL(BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(BET.COMPONENT_ITEM_ID,BET.ORGANIZATION_ID,C_REV_DATE),
(SELECT STARTING_REVISION
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = BET.ORGANIZATION_ID)) > = (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
AND ORGANIZATION_ID = BET.ORGANIZATION_ID
AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID))
AND (BIC.TO_END_ITEM_REV_ID IS NULL
OR (NVL(BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(BET.COMPONENT_ITEM_ID,BET.ORGANIZATION_ID,C_REV_DATE),
(SELECT STARTING_REVISION
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = BET.ORGANIZATION_ID)) <= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
AND ORGANIZATION_ID = BET.ORGANIZATION_ID
AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))
)
OR (EXISTS (SELECT NULL
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
AND ((BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(L_END_ITEM_ID,L_END_ITEM_ORG_ID,C_REV_DATE) >= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID))
AND (BIC.TO_END_ITEM_REV_ID IS NULL
OR (BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(L_END_ITEM_ID,L_END_ITEM_ORG_ID,C_REV_DATE) <= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))
)))
))
/*End of change*/
/*
( NVL(BOM.EFFECTIVITY_CONTROL,1) = 4 -- End Item rev effectivity
AND ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
OR c_impl_flag = 2 )
AND
( (c_explode_option = 1) -- ALL
OR
(c_explode_option = 3 -- Current and Future
AND BIC.DISABLE_DATE IS NULL
AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
AND
( (l_end_item_minor_rev_code <= (SELECT concat(to_char(decode(BIC.TO_END_ITEM_REV_ID,null,to_date('9999-12-31','YYYY-MM-DD'),effectivity_date),'yyyymmddhh24miss'),
to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
FROM mtl_item_revisions_b
WHERE inventory_item_id = l_end_item_id AND
organization_id = l_end_item_org_id AND
revision_id = nvl(BIC.TO_END_ITEM_REV_ID,BIC.FROM_END_ITEM_REV_ID)))
OR
(BET.minor_revision_code <= (SELECT concat(to_char(decode(BIC.TO_END_ITEM_REV_ID,null,to_date('9999-12-31','YYYY-MM-DD'),effectivity_date),'yyyymmddhh24miss'),
to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
FROM mtl_item_revisions_b
WHERE inventory_item_id = BET.component_item_id AND
organization_id = BET.organization_id AND
revision_id = nvl(BIC.TO_END_ITEM_REV_ID,BIC.FROM_END_ITEM_REV_ID)))
)
)
OR
( c_explode_option = 2 -- Current
AND BIC.DISABLE_DATE IS NULL
AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
AND
(
(l_end_item_minor_rev_code >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
to_char(nvl(BIC.from_end_item_minor_rev_id,0)))
FROM mtl_item_revisions_b
WHERE inventory_item_id = l_end_item_id AND
organization_id = l_end_item_org_id AND
revision_id = BIC.FROM_END_ITEM_REV_ID)
AND (BIC.to_end_item_rev_id IS NULL OR
l_end_item_minor_rev_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
FROM mtl_item_revisions_b
WHERE inventory_item_id = l_end_item_id AND
organization_id = l_end_item_org_id AND
revision_id = BIC.TO_END_ITEM_REV_ID) ))
OR
(BET.minor_revision_code >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
to_char(nvl(BIC.from_end_item_minor_rev_id,0)))
FROM mtl_item_revisions_b
WHERE inventory_item_id = BET.component_item_id AND
organization_id = BET.organization_id AND
revision_id = BIC.FROM_END_ITEM_REV_ID)
AND (BIC.to_end_item_rev_id IS NULL OR
BET.minor_revision_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
FROM mtl_item_revisions_b
WHERE inventory_item_id = BET.component_item_id AND
organization_id = BET.organization_id AND
revision_id = BIC.TO_END_ITEM_REV_ID)) )
)
)
)
)
*/
OR
( NVL(BOM.EFFECTIVITY_CONTROL,1) =1 -- Date Effectivity
AND ((c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL) OR c_impl_flag = 2 )
AND
( (c_explode_option = 1 -- ALL
AND (c_level - 1 = 0 OR
-- make sure the component is effective for the parent IF it is other than 1st level
-- though the option is ALL
( BIC.effectivity_date <= nvl(BET.disable_date, BIC.effectivity_date) AND
NVL(BIC.disable_date, BET.effectivity_date) >= BET.effectivity_date)))
OR
( ( BIC.IMPLEMENTATION_DATE IS NOT NULL AND
((c_explode_option = 2 AND
nvl(BET.comp_fixed_rev_high_date,c_rev_date) >= BIC.EFFECTIVITY_DATE AND
nvl(BET.comp_fixed_rev_high_date,c_rev_date) < nvl(BIC.DISABLE_DATE, nvl(BET.comp_fixed_rev_high_date,c_rev_date)+1)) -- CURRENT
OR
(c_explode_option = 3 AND
nvl(BIC.DISABLE_DATE, nvl(BET.comp_fixed_rev_high_date,c_rev_date)+ 1) > nvl(BET.comp_fixed_rev_high_date,c_rev_date) )) -- CURRENT AND FUTURE
)
OR
( BIC.IMPLEMENTATION_DATE IS NULL AND
nvl(BIC.ACD_TYPE,1) = 3
OR
(
((c_explode_option = 2 AND
nvl(BET.comp_fixed_rev_high_date,c_rev_date) >= BIC.EFFECTIVITY_DATE AND
nvl(BET.comp_fixed_rev_high_date,c_rev_date) < nvl(BIC.DISABLE_DATE, nvl(BET.comp_fixed_rev_high_date,c_rev_date)+1)) -- CURRENT
OR
(c_explode_option = 3 AND
nvl(BIC.DISABLE_DATE, nvl(BET.comp_fixed_rev_high_date,c_rev_date)+ 1) > nvl(BET.comp_fixed_rev_high_date,c_rev_date) )) -- CURRENT AND FUTURE
)
)
) -- OR
)-- AND
)--Date eff
)
/*
AND ( BET.minor_revision_code IS NULL
OR
( BET.minor_revision_code IS NOT NULL AND
(
( BET.OBJ_NAME IS NOT NULL AND
BET.minor_revision_id BETWEEN nvl(BIC.from_minor_revision_id,BET.minor_revision_id) AND
nvl(BIC.to_minor_revision_id,BET.minor_revision_id))
OR
( BET.OBJ_NAME IS NULL AND
BET.minor_revision_code >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
to_char(nvl(BIC.from_minor_revision_id,0)))
FROM mtl_item_revisions_b WHERE revision_id = BIC.FROM_OBJECT_REVISION_ID)
AND (BIC.to_object_revision_id IS NULL OR
BET.minor_revision_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
to_char(nvl(BIC.to_minor_revision_id,9999999999999999)))
FROM mtl_item_revisions_b WHERE revision_id = BIC.TO_OBJECT_REVISION_ID))
)
)
)
)
*/
AND BET.LOOP_FLAG = 2
AND ( filter_pbom IS NULL
OR
EXISTS (SELECT null FROM ego_items_v WHERE inventory_item_id = BIC.component_item_id AND
organization_id = NVL(BET.COMMON_ORGANIZATION_ID,BET.ORGANIZATION_ID) AND
TRADE_ITEM_DESCRIPTOR IS NOT NULL)
)
/*
ORDER BY BET.TOP_BILL_SEQUENCE_ID, BET.SORT_ORDER,
decode(c_order_by, 1, BIC.OPERATION_SEQ_NUM, BIC.ITEM_NUM),
decode(c_order_by, 1, BIC.ITEM_NUM, BIC.OPERATION_SEQ_NUM) */
ORDER BY BET.TOP_BILL_SEQUENCE_ID, BET.SORT_ORDER; --Added for bug 9341312
Select mil.concatenated_segments
From mtl_item_locations_kfv mil
Where mil.inventory_location_id = P_Locator;
Select round(bos.operation_lead_time_percent, 2) oltp
From Bom_Operation_Sequences bos,
Bom_Operational_Routings bor
Where bor.assembly_item_id = P_Assembly
And bor.organization_Id = org_id
And (bor.alternate_routing_designator = P_Alternate
or
(bor.alternate_routing_designator is null AND not exists (
SELECT null
FROM bom_operational_routings bor2
WHERE bor2.assembly_item_id = P_Assembly
AND bor2.organization_id = org_id
AND bor2.alternate_routing_designator = P_Alternate)
))
And bor.common_routing_sequence_id = bos.routing_sequence_id
And bos.operation_seq_num = P_Operation
And bos.effectivity_date <=
trunc(rev_date)
And nvl(bos.disable_date,
rev_date + 1) >=
trunc(rev_date);
Select P_Percent/100 * msi.full_lead_time offset
From mtl_system_items_b msi
Where msi.inventory_item_id = P_ParentItem
And msi.organization_id = Org_Id;
Select msi.concatenated_segments,
bom.alternate_bom_designator
From mtl_system_items_b_kfv msi,
bom_structures_b bom,
BOM_EXPLOSIONS_ALL bet
Where msi.inventory_item_id = bom.assembly_item_id
And msi.organization_id = bom.organization_id
And bom.bill_sequence_id = bet.top_bill_sequence_id
And bet.group_id = grp_id
And rownum = 1;
SELECT revision_id, revision, revision_label FROM mtl_item_revisions_b WHERE revision_id = p_revision_id;
SELECT nvl(max(minor_revision_id),0) minor_revision_id FROM ego_minor_revisions
WHERE obj_name = p_obj_name AND
pk1_value = p_pk1_value AND
nvl(pk2_value,'-1') = nvl(p_pk2_value,'-1') AND
nvl(pk3_value,'-1') = nvl(p_pk3_value,'-1');
SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(p_minor_rev_id)) mrev_code FROM mtl_item_revisions_b
WHERE revision_id = p_revision_id;
SELECT revision,revision_label,revision_id
FROM mtl_item_revisions_b MIR
WHERE mir.inventory_item_id = p_inventory_item_id
AND mir.organization_id = p_organization_id
AND mir.effectivity_date <= p_revision_date
AND (p_impl_flag = 2 OR (p_impl_flag = 1 AND mir.implementation_date IS NOT NULL) )
ORDER BY mir.effectivity_date DESC;
SELECT nvl(max(structure_type_id),-1) structure_type_id FROM bom_structure_types_b WHERE structure_type_name ='Packaging Hierarchy'
AND structure_type_id = p_structure_type_id;
SELECT structure_type_id
FROM bom_structure_types_b
WHERE structure_type_name = 'Packaging Hierarchy';
SELECT
DECODE( SIGN(high_date-SYSDATE),
-1 ,
'P',
1 ,
DECODE( SIGN(effectivity_date-SYSDATE),
1 ,
'F',
'C'
) ,
0 ,
'C'
) Revision_Scope,
DECODE( SIGN(high_date-SYSDATE),
-1 ,
high_date,
1 ,
DECODE( SIGN(effectivity_date-SYSDATE),
1 ,
effectivity_date,
SYSDATE
) ,
0 ,
SYSDATE
) Revision_high_date,
Effectivity_Date, High_Date, Implementation_Date,
Inventory_Item_Id, Organization_Id, Revision, Revision_label
FROM
( SELECT
rev1.Organization_Id, rev1.Inventory_Item_Id, rev1.Revision_Id, rev1.Revision, rev1.Effectivity_Date,
NVL( MIN(rev2.Effectivity_Date - 1/(60*60*24)),
GREATEST(TO_DATE('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.Effectivity_Date)
) High_Date,
rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label
FROM Mtl_Item_Revisions_B rev2 , Mtl_Item_Revisions_B rev1
WHERE rev1.revision_id = p_revision_id AND rev1.Organization_Id = rev2.Organization_Id(+)
AND rev1.Inventory_Item_Id = rev2.Inventory_Item_Id(+) AND rev2.Effectivity_Date(+) > rev1.Effectivity_Date
AND rev2.implementation_date (+) IS NOT NULL
GROUP BY rev1.Organization_Id, rev1.Inventory_Item_Id, rev1.Revision_Id, rev1.Revision, rev1.Effectivity_Date,
rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label);
/* Declare pl/sql tables for all coulmns in the select list. BULK BIND and INSERT with
pl/sql table of records work fine in 9i releases but not in 8i. So, the only option is
to use individual pl/sql table for each column in the cursor select list */
TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
last_update_date_tbl DATE_TBL_TYPE;
last_updated_by_tbl NUMBER_TBL_TYPE;
Insert_Attachments( p_group_id => grp_id
, p_plan_level => cur_level
);
last_update_date_tbl,
last_updated_by_tbl ,
auto_request_material_tbl,
rexplode_flag_tbl,
acd_type_tbl,
quantity_related_tbl,
change_policy_val_tbl,
explode_option_tbl ,
structure_type_id_tbl,
comp_fixed_rev_high_date_tbl,
comp_fixed_revision_id_tbl ,
parent_comp_sequence_id_tbl,
is_preferred_tbl,
parent_impl_date_tbl,
parent_change_notice_tbl,
source_bill_sequence_id_tbl,
common_component_seq_id_tbl,
comp_source_bill_seq_tbl,
comp_effectivity_control_tbl LIMIT l_batch_size;
exit; -- do not insert extra level
SELECT gtin
, description
, trade_item_descriptor
, primary_uom_code
, eng_item_flag
, primary_uom_code_desc
, trade_item_descriptor_desc
, publication_status
INTO gtin_number_tbl(i)
, gtin_description_tbl(i)
, trade_item_descriptor_tbl(i)
, primary_uom_code_tbl(i)
, eng_item_flag_tbl(i)
, primary_uom_desc_tbl(i)
, trade_item_descriptor_desc_tbl(i)
,gtin_publication_status_tbl(i)
FROM ego_items_v egi
WHERE inventory_item_id = component_item_id_tbl(i)
--AND organization_id = nvl(common_organization_id_tbl(i),organization_id_tbl(i));
SELECT msi.primary_uom_code
, msi.eng_item_flag
, mum.description
INTO primary_uom_code_tbl(i)
, eng_item_flag_tbl(i)
, primary_uom_desc_tbl(i)
FROM mtl_system_items_b msi
--, mtl_units_of_measure mum
, mtl_units_of_measure_tl mum
WHERE msi.inventory_item_id = component_item_id_tbl(i)
AND msi.organization_id = nvl(common_organization_id_tbl(i),
organization_id_tbl(i))
AND msi.primary_uom_code = mum.uom_code
AND mum.language = userenv('LANG');
SELECT max(common_bill_sequence_id), max(structure_type_id), max(is_preferred),
max(implementation_date), max(source_bill_sequence_id),max(assembly_type), max(effectivity_control)
INTO comp_common_bill_seq_tbl(i), structure_type_id_tbl(i), is_preferred_tbl(i), bom_implementation_date_tbl(i),
comp_source_bill_seq_tbl(i),assembly_type_tbl(i), comp_effectivity_control_tbl(i)
FROM bom_structures_b WHERE
bill_sequence_id = comp_bill_seq_tbl(i);
INSERT INTO bom_plm_explosion_temp VALUES be_temp_TBL(i);
INSERT INTO BOM_EXPLOSIONS_ALL
(
TOP_BILL_SEQUENCE_ID ,
BILL_SEQUENCE_ID ,
COMMON_BILL_SEQUENCE_ID ,
COMMON_ORGANIZATION_ID ,
ORGANIZATION_ID ,
COMPONENT_SEQUENCE_ID ,
COMPONENT_ITEM_ID ,
BASIS_TYPE ,
COMPONENT_QUANTITY ,
PLAN_LEVEL ,
EXTENDED_QUANTITY ,
SORT_ORDER ,
GROUP_ID ,
TOP_ALTERNATE_DESIGNATOR ,
COMPONENT_YIELD_FACTOR ,
TOP_ITEM_ID ,
COMPONENT_CODE ,
INCLUDE_IN_ROLLUP_FLAG ,
LOOP_FLAG ,
PLANNING_FACTOR ,
OPERATION_SEQ_NUM ,
BOM_ITEM_TYPE ,
PARENT_BOM_ITEM_TYPE ,
PRIMARY_UOM_CODE ,
PRIMARY_UNIT_OF_MEASURE ,
ASSEMBLY_ITEM_ID ,
ALTERNATE_BOM_DESIGNATOR ,
WIP_SUPPLY_TYPE ,
ITEM_NUM ,
EFFECTIVITY_DATE ,
DISABLE_DATE ,
TRIMMED_EFFECTIVITY_DATE ,
TRIMMED_DISABLE_DATE ,
TRIMMED_FROM_UNIT_NUMBER ,
TRIMMED_TO_UNIT_NUMBER ,
FROM_END_ITEM_UNIT_NUMBER ,
TO_END_ITEM_UNIT_NUMBER ,
IMPLEMENTATION_DATE ,
OPTIONAL ,
SUPPLY_SUBINVENTORY ,
SUPPLY_LOCATOR_ID ,
COMPONENT_REMARKS ,
CHANGE_NOTICE ,
OPERATION_LEAD_TIME_PERCENT ,
MUTUALLY_EXCLUSIVE_OPTIONS ,
CHECK_ATP ,
REQUIRED_TO_SHIP ,
REQUIRED_FOR_REVENUE ,
INCLUDE_ON_SHIP_DOCS ,
LOW_QUANTITY ,
HIGH_QUANTITY ,
SO_BASIS ,
OPERATION_OFFSET ,
CURRENT_REVISION ,
LOCATOR ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
OBJ_NAME ,
PK1_VALUE ,
PK2_VALUE ,
PK3_VALUE ,
PK4_VALUE ,
PK5_VALUE ,
FROM_END_ITEM_REV_ID ,
FROM_END_ITEM_MINOR_REV_ID ,
TO_END_ITEM_REV_ID ,
TO_END_ITEM_MINOR_REV_ID ,
NEW_COMPONENT_CODE ,
PARENT_SORT_ORDER ,
COMP_COMMON_BILL_SEQ_ID ,
COMP_BILL_SEQ_ID ,
ACCESS_FLAG ,
ENG_ITEM_FLAG ,
ASSEMBLY_TYPE ,
REVISION_LABEL ,
REVISION_ID ,
EFFECTIVITY_CONTROL ,
OBJECT_REVISION_ID ,
MINOR_REVISION_ID ,
MINOR_REVISION_CODE ,
FROM_OBJECT_REVISION_ID ,
FROM_MINOR_REVISION_ID ,
TO_OBJECT_REVISION_ID ,
TO_MINOR_REVISION_ID ,
COMPONENT_ITEM_REVISION_ID ,
COMPONENT_MINOR_REVISION_ID ,
BOM_IMPLEMENTATION_DATE ,
GTIN_NUMBER ,
GTIN_DESCRIPTION ,
TRADE_ITEM_DESCRIPTOR ,
TRADE_ITEM_DESCRIPTOR_DESC ,
GTIN_PUBLICATION_STATUS ,
TOP_GTIN_NUMBER ,
TOP_GTIN_DESCRIPTION ,
TOP_TRADE_ITEM_DESCRIPTOR,
PARENT_GTIN_NUMBER ,
PARENT_GTIN_DESCRIPTION ,
PARENT_TRADE_ITEM_DESCRIPTOR ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
AUTO_REQUEST_MATERIAL,
REXPLODE_FLAG,
ACD_TYPE,
QUANTITY_RELATED,
CHANGE_POLICY_VALUE,
EXPLODED_OPTION,
COMP_FIXED_REV_HIGH_DATE,
COMP_FIXED_REVISION_ID,
MAX_BILL_LEVEL,
PARENT_COMP_SEQ_ID,
END_ITEM_ID,
END_ITEM_ORG_ID,
STRUCTURE_TYPE_ID,
IS_PREFERRED,
PARENT_IMPLEMENTATION_DATE,
PARENT_CHANGE_NOTICE,
SOURCE_BILL_SEQUENCE_ID,
COMMON_COMPONENT_SEQUENCE_ID,
COMP_SOURCE_BILL_SEQ_ID,
COMP_EFFECTIVITY_CONTROL)
VALUES
(
top_bill_sequence_id_tbl(i) ,
bill_sequence_id_tbl(i) ,
common_bill_sequence_id_tbl(i) ,
common_organization_id_tbl(i) ,
organization_id_tbl(i) ,
component_sequence_id_tbl(i) ,
component_item_id_tbl(i) ,
basis_type_tbl(i) ,
component_quantity_tbl(i) ,
plan_level_tbl(i) ,
extended_quantity_tbl(i) ,
sort_order_tbl(i) ,
group_id_tbl(i) ,
top_alternate_designator_tbl(i) ,
component_yield_factor_tbl(i) ,
top_item_id_tbl(i) ,
component_code_tbl(i) ,
include_in_cost_rollup_tbl(i) ,
loop_flag_tbl(i) ,
planning_factor_tbl(i) ,
operation_seq_num_tbl(i) ,
bom_item_type_tbl(i) ,
parent_bom_item_type_tbl(i) ,
primary_uom_code_tbl(i) ,
primary_uom_desc_tbl(i) ,
parent_item_id_tbl(i) ,
alternate_bom_designator_tbl(i) ,
wip_supply_type_tbl(i) ,
item_num_tbl(i) ,
effectivity_date_tbl(i) ,
disable_date_tbl(i) ,
trimmed_effectivity_date_tbl(i) ,
trimmed_disable_date_tbl(i) ,
trimmed_from_unit_number_tbl(i),
trimmed_to_unit_number_tbl(i),
from_end_item_unit_number_tbl(i) ,
to_end_item_unit_number_tbl(i) ,
implementation_date_tbl(i) ,
optional_tbl(i) ,
supply_subinventory_tbl(i) ,
supply_locator_id_tbl(i) ,
component_remarks_tbl(i) ,
change_notice_tbl(i) ,
operation_leadtime_percent_tbl(i) ,
mutually_exclusive_options_tbl(i) ,
check_atp_tbl(i) ,
required_to_ship_tbl(i) ,
required_for_revenue_tbl(i) ,
include_on_ship_docs_tbl(i) ,
low_quantity_tbl(i) ,
high_quantity_tbl(i) ,
so_basis_tbl(i) ,
operation_offset_tbl(i) ,
Current_revision_tbl(i) ,
locator_tbl(i) ,
attribute_category_tbl(i) ,
attribute1_tbl(i) ,
attribute2_tbl(i) ,
attribute3_tbl(i) ,
attribute4_tbl(i) ,
attribute5_tbl(i) ,
attribute6_tbl(i) ,
attribute7_tbl(i) ,
attribute8_tbl(i) ,
attribute9_tbl(i) ,
attribute10_tbl(i) ,
attribute11_tbl(i) ,
attribute12_tbl(i) ,
attribute13_tbl(i) ,
attribute14_tbl(i) ,
attribute15_tbl(i) ,
obj_name_tbl(i) ,
pk1_value_tbl(i) ,
pk2_value_tbl(i) ,
pk3_value_tbl(i) ,
pk4_value_tbl(i) ,
pk5_value_tbl(i) ,
from_end_item_rev_id_tbl(i) ,
from_end_item_minor_rev_id_tbl(i) ,
to_end_item_rev_id_tbl(i) ,
to_end_item_minor_rev_id_tbl(i) ,
new_component_code_tbl(i) ,
parent_sort_order_tbl(i) ,
comp_common_bill_seq_tbl(i) ,
comp_bill_seq_tbl(i) ,
access_flag_tbl(i) ,
eng_item_flag_tbl(i) ,
assembly_type_tbl(i) ,
revision_label_tbl(i) ,
revision_id_tbl(i) ,
effectivity_control_tbl(i) ,
object_rev_id_tbl(i) ,
minor_rev_id_tbl(i) ,
minor_rev_code_tbl(i) ,
from_object_rev_id_tbl(i) ,
from_minor_rev_id_tbl(i) ,
to_object_rev_id_tbl(i) ,
to_minor_rev_id_tbl(i) ,
component_item_revision_id_tbl(i) ,
component_minorrevision_id_tbl(i) ,
decode(comp_bill_seq_tbl(i), null, to_date(null), bom_implementation_date_tbl(i)) ,
gtin_number_tbl(i),
gtin_description_tbl(i),
trade_item_descriptor_tbl(i),
trade_item_descriptor_desc_tbl(i),
gtin_publication_status_tbl(i),
top_gtin_number_tbl(i),
top_gtin_description_tbl(i),
top_trade_item_descriptor_tbl(i),
parent_gtin_number_tbl(i),
parent_gtin_description_tbl(i),
parent_trade_descriptor_tbl(i),
creation_date_tbl(i),
created_by_tbl(i),
last_update_date_tbl(i),
last_updated_by_tbl(i),
auto_request_material_tbl(i),
rexplode_flag_tbl(i),
acd_type_tbl(i),
quantity_related_tbl(i),
change_policy_val_tbl(i),
explode_option_tbl(i),
comp_fixed_rev_high_date_tbl(i),
comp_fixed_revision_id_tbl(i),
max_level,
parent_comp_sequence_id_tbl(i),
l_end_item_id,
l_end_item_org_id,
decode(comp_bill_seq_tbl(i), null, null, structure_type_id_tbl(i)),
decode(comp_bill_seq_tbl(i), null, null, is_preferred_tbl(i)),
parent_impl_date_tbl(i),
parent_change_notice_tbl(i),
source_bill_sequence_id_tbl(i) ,
common_component_seq_id_tbl(i),
comp_source_bill_seq_tbl(i),
comp_effectivity_control_tbl(i));
/* Update the quantity of children for every parent, total quantity for every parent */
--
-- IF total rows fetched is 0, THEN break the loop here since nothing
-- more to explode
--
IF total_rows = 0 THEN
-- Do not break the loop. We might find some dirty nodes somewhere deep in the hierarchy
--exit;
UPDATE BOM_EXPLOSIONS_ALL
SET quantity_of_children = g_quantity_of_children_tbl(i),
total_qty_at_next_level = g_total_qty_at_next_level_tbl(i)
WHERE group_id = grp_id
AND sort_order = g_parent_sort_order_tbl(i);
UPDATE BOM_EXPLOSIONS_ALL bet SET (bet.primary_uom_code, bet.eng_item_flag, bet.primary_unit_of_measure) =
(SELECT msi.primary_uom_code
, msi.eng_item_flag
, mum.unit_of_measure
FROM mtl_system_items_b msi
, mtl_units_of_measure_tl mum
WHERE msi.inventory_item_id = bet.component_item_id
AND msi.organization_id = bet.common_organization_id
AND msi.primary_uom_code = mum.uom_code
AND mum.language = userenv('LANG'))
WHERE bet.group_id = grp_id AND bet.obj_name IS NULL AND bet.primary_uom_code IS NULL;
/*SELECT max(MAXIMUM_BOM_LEVEL)
INTO max_level
FROM BOM_PARAMETERS
WHERE (org_id = -1
or
(org_id <> -1 AND ORGANIZATION_ID = org_id)
);
/* insert the attachments for the current explosion
Attachments are now inserted to improve performance of the view when querying the explosion results
Only the pk1 and status id, etc is inserted into the explosion. Rest of the user displayed columns
are still left in the view.
The following columns are resused for ATTACHMENT node
pk1_value = ATTACHED_DOCUMENT_ID
LINE_ID = DOCUMENT_ID
*/
--dbms_output.put_line('Inserting Attachments . . .');
SELECT
COMPONENT_CODE,
LOOP_FLAG,
PLAN_LEVEL
FROM BOM_EXPLOSIONS_ALL
WHERE GROUP_ID = c_group_id
AND LOOP_FLAG = 1;
SELECT NVL( TOP_ALTERNATE_DESIGNATOR, 'none' ), ORGANIZATION_ID
INTO top_alt, org_id
FROM BOM_EXPLOSIONS_ALL
WHERE GROUP_ID = grp_id
AND ROWNUM = 1
AND PLAN_LEVEL = 0;
SELECT
substrb(MIF.ITEM_NUMBER || ' ' || BBM.ALTERNATE_BOM_DESIGNATOR,1,16)
INTO cur_msgstr
FROM MTL_ITEM_FLEXFIELDS MIF, BOM_BILL_OF_MATERIALS BBM
WHERE MIF.ORGANIZATION_ID = BBM.ORGANIZATION_ID
AND MIF.ITEM_ID = BBM.ASSEMBLY_ITEM_ID
AND BBM.ASSEMBLY_ITEM_ID = cur_item_id
AND BBM.ORGANIZATION_ID = org_id
AND (
((top_alt = 'none') AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL)
OR
((top_alt <> 'none')
AND (
( EXISTS ( SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM1
WHERE BBM1.ORGANIZATION_ID = org_id
AND BBM1.ASSEMBLY_ITEM_ID = cur_item_id
AND BBM1.ALTERNATE_BOM_DESIGNATOR = top_alt)
AND BBM.ALTERNATE_BOM_DESIGNATOR = top_alt
)
OR
( NOT EXISTS (SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM2
WHERE BBM2.ORGANIZATION_ID = org_id
AND BBM2.ASSEMBLY_ITEM_ID = cur_item_id
AND BBM2.ALTERNATE_BOM_DESIGNATOR = top_alt)
AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL
)
)
)
);
Select BET.organization_id curOI,
BET.bill_sequence_id curBSI,
BET.component_sequence_id curCSI,
/*if the top item is not the assigned to both orgs, this can lead to problems bug: 5522821*/
Decode(BET.component_item_id, BET.top_item_id,BOM.assembly_item_id,BET.component_item_id) curCII,
BET.common_bill_sequence_id curCBSI,
BET.group_id curGI,
BET.primary_uom_code curPUC,
BET.primary_unit_of_measure curPUM
FROM BOM_EXPLOSIONS_ALL BET, bom_structures_b BOM
WHERE BET.group_id = p_group_id AND BET.obj_name IS NULL AND
BET.bill_sequence_id <> BET.common_bill_sequence_id AND
BET.source_bill_sequence_id = BOM.BILL_SEQUENCE_ID AND
BET.plan_level <> 0;
SELECT conversion_rate
FROM mtl_uom_conversions_view
WHERE primary_uom_code = t_master_uom and
uom_code = t_child_uom and
inventory_item_id = t_inv_id and
organization_id = t_master_org_id;
SELECT revision revision, revision_id revision_id FROM mtl_item_rev_highdate_v
WHERE inventory_item_id = p_inventory_item_id AND
organization_id = p_organization_id AND
p_effective_date BETWEEN effectivity_date AND decode( sign(high_date-effectivity_date), 1 , high_date, p_effective_date) ;
SELECT revision revision, revision_id revision_id FROM (
SELECT rev1.organization_id , rev1.inventory_item_id , rev1.revision_id , rev1.revision ,
rev1.effectivity_date , nvl(min(rev2.effectivity_date - 1/(60*60*24)),
greatest(to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.effectivity_date)) high_date,
rev1.implementation_date, rev1.change_notice FROM mtl_item_revisions_b rev2 , mtl_item_revisions_b rev1
WHERE rev1.inventory_item_id = p_inventory_item_id AND rev1.organization_id = p_organization_id AND
rev1.organization_id = rev2.organization_id(+) AND
rev1.inventory_item_id = rev2.inventory_item_id(+) AND
rev2.effectivity_date(+) > rev1.effectivity_date
GROUP BY rev1.organization_id , rev1.inventory_item_id , rev1.revision_id ,
rev1.revision , rev1.effectivity_date , rev1.implementation_date , rev1.change_notice)
WHERE p_effective_date BETWEEN effectivity_date AND high_date;
SELECT inventory_item_id, organization_id, revision,effectivity_date FROM mtl_item_revisions_b
WHERE revision_id = p_item_revision_id;
SELECT nvl(max(minor_revision_id),0) minor_revision_id FROM ego_minor_revisions
WHERE obj_name = p_obj_name AND
pk1_value = p_pk1_value AND
nvl(pk2_value,'-1') = nvl(p_pk2_value,'-1') AND
nvl(pk3_value,'-1') = nvl(p_pk3_value,'-1');
SELECT nvl(max(minor_revision_id),0) minor_revision_id FROM ego_minor_revisions
WHERE obj_name = 'EGO_ITEM_REVISION'
AND pk3_value = p_item_rev_id;
SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(p_minor_rev_id)) mrev_code FROM mtl_item_revisions_b
WHERE revision_id = p_revision_id;
SELECT alternate_bom_designator FROM bom_structures_b WHERE ( (p_obj_name IS NULL AND obj_name IS NULL)
OR (p_obj_name IS NOT NULL AND obj_name = p_obj_name)) AND pk1_value = p_pk1_value AND
pk2_value = p_pk2_value AND structure_type_id = p_structure_type_id;
SELECT sort_order, comp_common_bill_seq_id, comp_bill_seq_id FROM BOM_EXPLOSIONS_ALL WHERE
group_id = grp_id AND comp_bill_seq_id IS NOT NULL AND rexplode_flag = 1
ORDER BY sort_order;
SELECT effectivity_date, revision_id, revision FROM mtl_item_revisions_b WHERE
inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
effectivity_date = ( SELECT min(effectivity_date) FROM mtl_item_revisions_b
WHERE inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id );
SELECT structure_type_id
FROM bom_structure_types_b
WHERE structure_type_name = 'Packaging Hierarchy';
SELECT bet.component_sequence_id, bet.component_item_revision_id revision_id,
mir.revision revision FROM bom_explosions_all bet, mtl_item_revisions_b mir
WHERE bet.group_id = p_group_id AND bet.plan_level <> 0 AND nvl(bet.component_item_revision_id,0) <> 0
AND bet.component_item_revision_id = mir.revision_id;
SELECT
DECODE( SIGN(high_date-SYSDATE),
-1 ,
high_date,
1 ,
DECODE( SIGN(effectivity_date-SYSDATE),
1 ,
effectivity_date,
SYSDATE
) ,
0 ,
SYSDATE
) Revision_high_date,
Revision_id
FROM
( SELECT
rev1.Organization_Id, rev1.Inventory_Item_Id, rev1.Revision_Id, rev1.Revision, rev1.Effectivity_Date,
NVL( MIN(rev2.Effectivity_Date - 1/(60*60*24)),
GREATEST(TO_DATE('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.Effectivity_Date)
) High_Date,
rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label
FROM bom_explosions_all bet, Mtl_Item_Revisions_B rev2 , Mtl_Item_Revisions_B rev1
WHERE bet.group_id = p_group_id AND bet.component_item_revision_id IS NOT NULL AND
rev1.revision_id = bet.component_item_revision_id AND rev1.Organization_Id = rev2.Organization_Id(+)
AND rev1.Inventory_Item_Id = rev2.Inventory_Item_Id(+) AND rev2.Effectivity_Date(+) > rev1.Effectivity_Date
AND rev2.implementation_date (+) IS NOT NULL
GROUP BY rev1.Organization_Id, rev1.Inventory_Item_Id, rev1.Revision_Id, rev1.Revision, rev1.Effectivity_Date,
rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label);
SELECT nvl(BE.component_sequence_id,0) component_sequence_id, --nvl(BE.current_revision,
--always call Get_Current_RevisionDetails, we want to pick the rev label from items
-- even for fixed rev comps.
BOM_EXPLODER_PUB.Get_Current_RevisionDetails(BE.component_item_id,
BE.organization_id,
decode(nvl(BE.component_item_revision_id, BE.comp_fixed_revision_id),
null,
BOM_EXPLODER_PUB.get_explosion_date,
BOM_EXPLODER_PUB.Get_Revision_HighDate(nvl(BE.component_item_revision_id, BE.comp_fixed_revision_id)))) current_revision,
nvl(BE.component_item_revision_id,BOM_EXPLODER_PUB.Get_Current_Revision_Id) REVISION_ID,
--nvl(BE.revision_label,
BOM_EXPLODER_PUB.Get_Current_Revision_Label revision_label,
BE.new_component_code component_code
FROM bom_explosions_all BE WHERE BE.group_id = p_group_id;
SELECT nvl(BE.component_sequence_id,0) component_sequence_id, nvl(BE.current_revision,
BOM_EXPLODER_PUB.Get_Current_RevisionDetails(BE.component_item_id,
BE.organization_id,
decode(BE.comp_fixed_revision_id,
null,
BOM_EXPLODER_PUB.get_explosion_date,
BOM_EXPLODER_PUB.Get_Revision_HighDate(BE.comp_fixed_revision_id)))) current_revision,
nvl(BE.component_item_revision_id,BOM_EXPLODER_PUB.Get_Current_Revision_Id) REVISION_ID,
nvl(BE.revision_label, BOM_EXPLODER_PUB.Get_Current_Revision_Label) revision_label,
BOM_SECURITY_PUB.CHECK_USER_PRIVILEGE(
1,
BOM_SECURITY_PUB.GET_FUNCTION_NAME_TO_CHECK,
'EGO_ITEM',
BE.PK1_VALUE,
BE.ORGANIZATION_ID,
NULL,
NULL,
NULL,
BOM_EXPLODER_PUB.Get_EGO_User) ACCESS_FLAG
FROM bom_explosions_all BE WHERE BE.group_id = p_group_id;
SELECT
nvl(item_dtls.component_sequence_id,0) AS component_sequence_id, ecp.policy_char_value
FROM
(SELECT NVL(mirb.lifecycle_id, msi.lifecycle_id) AS lifecycle_id,
NVL(mirb.current_phase_id , msi.current_phase_id) AS phase_id,
msi.item_catalog_group_id item_catalog_group_id,
msi.inventory_item_id, msi.organization_id , mirb.revision_id,
bet.component_sequence_id, bet.structure_type_id
FROM bom_explosions_all bet, mtl_item_revisions_b mirb, MTL_SYSTEM_ITEMS_b msi
WHERE bet.group_id = p_group_id AND bet.comp_bill_seq_id IS NOT NULL
AND bet.component_item_id = msi.INVENTORY_ITEM_ID AND
bet.organization_id = msi.ORGANIZATION_ID AND
mirb.revision_id = BOM_EXPLODER_PUB.Get_Component_Revision_Id(NVL(BET.component_sequence_id,0))
AND (mirb.current_phase_id IS NOT NULL OR msi.current_phase_id IS NOT NULL)) ITEM_DTLS,
ENG_CHANGE_POLICIES_V ECP
WHERE --ecp.policy_object_pk1_value = item_dtls.item_catalog_group_id
ecp.policy_object_pk1_value =
(SELECT TO_CHAR(ic.item_catalog_group_id)
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS (SELECT olc.object_classification_code CatalogId
FROM EGO_OBJ_TYPE_LIFECYCLES olc
WHERE olc.object_id = (SELECT OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM')
AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
AND olc.object_classification_code = ic.item_catalog_group_id
)
AND ROWNUM = 1
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
AND ecp.policy_object_pk2_value = item_dtls.lifecycle_id
AND ecp.policy_object_pk3_value = item_dtls.phase_id
AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
AND ecp.attribute_code = 'STRUCTURE_TYPE'
AND ecp.attribute_number_value = item_dtls.structure_type_id;
SELECT max(MAXIMUM_BOM_LEVEL)
INTO max_level
FROM BOM_PARAMETERS
WHERE (org_id = -1
or
(org_id <> -1 AND ORGANIZATION_ID = org_id)
);
SELECT bill_sequence_id,common_bill_sequence_id,effectivity_control, bill_sequence_id, structure_type_id, assembly_type
INTO l_bill_sequence_id, l_common_bill_sequence_id, l_effectivity_control,l_top_bill_sequence_id, l_structure_type_id, l_bom_or_eng
FROM bom_structures_b bom
WHERE nvl(bom.obj_name,'EGO_ITEM') = nvl(object_name,'EGO_ITEM')
AND bom.pk1_value = pk_value1
AND nvl(bom.pk2_value,'-1') = nvl(pk_value2,'-1')
AND bom.organization_id = org_id
AND nvl(bom.alternate_bom_designator, 'NONE') = nvl(l_alt_desg, 'NONE');
/**** Moved before the insertion of 0th row
so that revision id is fetched only once and can be inserted
in the 0th row
*****/
l_item_rev := null; -- CAD component
SELECT pk1_value, pk2_value,obj_name INTO l_pk_value1, l_pk_value2, l_obj_name FROM
bom_structures_b WHERE bill_sequence_id = l_common_bill_sequence_id;
SELECT 'Y'
, rexplode_flag
, exploded_date
, exploded_unit_number
, exploded_end_item_rev
, exploded_end_item_id
, exploded_end_item_org_id
, exploded_option
, group_id
, reapply_exclusions
, max_bill_level
INTO l_bill_expl_exists
, l_rexplode_flag
, l_exploded_date
, l_exploded_unit_number
, l_exploded_end_item_rev
, l_exploded_end_item_id
, l_exploded_end_item_org_id
, l_exploded_option
, l_explosion_group_id
, l_reapply_exclusions
, l_max_bill_level
FROM BOM_EXPLOSIONS_ALL
WHERE top_bill_sequence_id = l_bill_sequence_id
AND exploded_option = l_explode_option
AND plan_level = 0;
UPDATE BOM_EXPLOSIONS_ALL
SET rexplode_flag = 1
WHERE group_id = grp_id
AND sort_order = '0000001';
DELETE FROM BOM_EXPLOSIONS_ALL
WHERE group_id = grp_id
AND sort_order <> '0000001';
SELECT BOM_EXPLOSIONS_ALL_S.NEXTVAL INTO grp_id FROM dual;
insert INTO BOM_EXPLOSIONS_ALL
(
group_id,
bill_sequence_id,
common_bill_sequence_id,
common_organization_id,
component_sequence_id,
organization_id,
top_item_id,
component_item_id,
plan_level,
extended_quantity,
basis_type,
component_quantity,
sort_order,
program_update_date,
top_bill_sequence_id,
component_code,
loop_flag,
top_alternate_designator,
obj_name,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
new_component_code,
parent_sort_order,
comp_common_bill_seq_id,
comp_source_bill_seq_id,
comp_bill_seq_id,
effectivity_control,
access_flag,
assembly_type,
bom_implementation_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
rexplode_flag,
exploded_option,
structure_type_id,
revision_id,
implementation_date,
max_bill_level,
is_preferred,
parent_implementation_date,
hgrid_flag,
source_bill_sequence_id,
comp_effectivity_control
)
(select
--explosion_group_id,
grp_id,
bom.bill_sequence_id,
bom.common_bill_sequence_id,
nvl(bom.common_organization_id,org_id),
NULL,
org_id,
item_id,
item_id,
0,
expl_qty,
1,
1,
lpad('1', X_SortWidth, '0'),
sysdate,
bom.bill_sequence_id,
nvl(comp_code, lpad(pk1_value, 16, '0')),
2,
l_alt_desg,
obj_name,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
nvl(comp_code, lpad(decode(obj_name,'DDD_CADVIEW','C','I')||pk1_value, 20, '0')),
null,
bom.common_bill_sequence_id,
bom.source_bill_sequence_id,
bom.bill_sequence_id,
bom.effectivity_control,
'T',
bom.assembly_type,
bom.implementation_date,
bom.creation_date,
bom.created_by,
bom.last_update_date,
bom.last_updated_by,
1,
l_explode_option,
structure_type_id,
l_item_rev_id,
bom.implementation_date,
max_level,
is_preferred,
bom.implementation_date,
'Y',
bom.source_bill_sequence_id,
bom.effectivity_control
FROM bom_structures_b bom
where bill_sequence_id = l_bill_sequence_id);
insert INTO BOM_EXPLOSIONS_ALL
(
group_id,
top_item_id,
component_item_id,
organization_id,
bill_sequence_id,
top_bill_sequence_id,
plan_level,
sort_order,
gtin_number,
gtin_description,
trade_item_descriptor,
trade_item_descriptor_desc,
obj_name,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
hgrid_flag
)
(select
grp_id,
item_id,
item_id,
org_id,
0,
0,
0,
lpad('1', X_SortWidth, '0'),
gtin,
description,
trade_item_descriptor,
trade_item_descriptor_desc,
object_name,
pk_value1,
pk_value2,
pk_value3,
pk_value4,
pk_value5,
'Y'
FROM ego_items_v eiv
where
eiv.inventory_item_id = item_id
AND eiv.organization_id = org_id);
SELECT inventory_item_id, organization_id, revision INTO l_end_item_id, l_end_item_org_id,l_end_item_revision_code FROM mtl_item_revisions_b
WHERE revision_id = l_end_item_revision_id;
UPDATE BOM_EXPLOSIONS_ALL
SET rexplode_flag = 1
WHERE group_id = grp_id
AND sort_order = '0000001';
DELETE FROM BOM_EXPLOSIONS_ALL
WHERE group_id = grp_id
AND sort_order <> '0000001';
/* Update the top bill with the effectivity and minor rev information.
Do not do this if the explosion criteria matches with the exploded one */
IF l_bill_criteria_exists = 'Y' AND nvl(l_rexplode_flag,'0') <> '1'
THEN
l_dirty_node_exists := 'N';
DELETE FROM BOM_EXPLOSIONS_ALL
WHERE group_id = grp_id
AND sort_order like r1.sort_order||'%' AND sort_order <> r1.sort_order;
UPDATE BOM_EXPLOSIONS_ALL
SET CHANGE_POLICY_VALUE = Get_Change_Policy_Val(0, r1.comp_bill_seq_id)
WHERE sort_order = r1.sort_order
AND group_id = grp_id;
UPDATE BOM_EXPLOSIONS_ALL
SET exploded_date = l_rev_date,
exploded_unit_number = unit_number,
--exploded_unit_number = l_unit_number,
exploded_end_item_rev = l_end_item_revision_id,
exploded_end_item_id = l_end_item_id,
exploded_end_item_org_id = l_end_item_org_id,
object_revision_id = l_item_rev_id,
minor_revision_id = l_minor_rev_id,
revision_id = l_item_rev_id, --insert top item's rev id
max_bill_level = nvl(max_level,60),
end_item_id = l_end_item_id,
end_item_org_id = l_end_item_org_id
--effectivity_date = l_rev_date,
--from_end_item_unit_number = unit_number
WHERE group_id = grp_id
AND sort_order = '0000001';
UPDATE BOM_EXPLOSIONS_ALL
SET exploded_date = l_rev_date,
exploded_unit_number = unit_number,
--exploded_unit_number = l_unit_number,
exploded_end_item_rev = l_end_item_revision_id,
exploded_end_item_id = l_end_item_id,
exploded_end_item_org_id = l_end_item_org_id,
object_revision_id = l_item_rev_id,
minor_revision_id = l_minor_rev_id,
revision_id = l_item_rev_id, --insert top item's rev id
max_bill_level = nvl(max_level,60),
end_item_id = l_end_item_id,
end_item_org_id = l_end_item_org_id
--effectivity_date = l_rev_date,
--from_end_item_unit_number = unit_number
WHERE group_id = grp_id
AND sort_order = '0000001';
UPDATE BOM_EXPLOSIONS_ALL
SET access_flag = l_access_flag,
(bom_item_type, parent_bom_item_type, primary_uom_code, eng_item_flag, primary_unit_of_measure) = (SELECT msi.bom_item_type, msi.bom_item_type, msi.primary_uom_code,
msi.eng_item_flag, (select description from mtl_units_of_measure where uom_code = msi.primary_uom_code) FROM
mtl_system_items_b msi WHERE msi.inventory_item_id = item_id AND msi.organization_id = org_id)
WHERE group_id = grp_id
AND sort_order = '0000001';
UPDATE BOM_EXPLOSIONS_ALL
SET access_flag = l_access_flag,
(bom_item_type, parent_bom_item_type, primary_uom_code, eng_item_flag, primary_unit_of_measure) = (SELECT msi.bom_item_type, msi.bom_item_type, msi.primary_uom_code,
msi.eng_item_flag, muom.unit_of_measure FROM mtl_system_items_b msi, mtl_units_of_measure muom
WHERE msi.inventory_item_id = item_id AND msi.organization_id = org_id AND muom.uom_code = msi.primary_uom_code)
WHERE group_id = grp_id
AND sort_order = '0000001';
UPDATE BOM_EXPLOSIONS_ALL
SET (gtin_number, gtin_description, trade_item_descriptor, top_gtin_number, top_gtin_description, top_trade_item_descriptor, trade_item_descriptor_desc, gtin_publication_status) =
(SELECT gtin, description, trade_item_descriptor, gtin, description, trade_item_descriptor, trade_item_descriptor_desc, publication_status
FROM ego_items_v egi
WHERE inventory_item_id = item_id AND organization_id = org_id)
WHERE group_id = grp_id
AND sort_order = '0000001';
/* Update the change policy value for the top item */
UPDATE BOM_EXPLOSIONS_ALL
SET CHANGE_POLICY_VALUE = Get_Change_Policy_Val(revision_id, Comp_bill_seq_Id)
WHERE group_id = grp_id
AND sort_order = '0000001';
SELECT count(*) INTO cnt
FROM mtl_parameters
WHERE organization_id = cost_organization_id
AND organization_id = org_id;
SELECT msi.primary_uom_code, msi.organization_id into
t_master_uom, t_master_org_id
FROM mtl_system_items_b msi, bom_structures_b bbm
WHERE cr.curCBSI = bbm.bill_sequence_id and
bbm.organization_id = msi.organization_id and
msi.inventory_item_id = cr.curCII;
SELECT msi.primary_uom_code INTO t_child_uom
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = cr.curCII and
msi.organization_id = cr.curOI;
UPDATE BOM_EXPLOSIONS_ALL
SET item_cost = item_cost*t_conversion_rate
WHERE group_id = cr.curGI and
component_sequence_id = cr.curCSI and
bill_sequence_id = cr.curBSI and
common_bill_sequence_id = cr.curCBSI;
UPDATE BOM_EXPLOSIONS_ALL
SET component_quantity = component_quantity/t_conversion_rate,
extended_quantity = extended_quantity/t_conversion_rate,
-- item_cost = item_cost*t_conversion_rate,
primary_uom_code = cr.curPUC,
primary_unit_of_measure = cr.curPUM
WHERE group_id = cr.curGI and
component_sequence_id = cr.curCSI and
bill_sequence_id = cr.curBSI and
common_bill_sequence_id = cr.curCBSI;
/* Update the explosion to make sure reexplosion doesn't happen */
/* insert attachments for level 0 */
/*
for c in (select rexplode_flag from BOM_EXPLOSIONS_ALL
where group_id = grp_id
and plan_level = 0)
loop
if c.rexplode_flag = 1
then
--Dbms_Output.put_line('Inserting attachments for level 0 .....................2');
Insert_Attachments( p_group_id => grp_id
, p_plan_level => 0
);
DELETE FROM bom_explosions_all
WHERE group_id = grp_id
AND nvl(trimmed_disable_date, l_rev_date+1) <= l_rev_date;
UPDATE bom_explosions_all
SET exploded_date = l_rev_date,
object_revision_id = l_item_rev_id,
minor_revision_id = l_minor_rev_id,
revision_id = l_item_rev_id --insert top item's rev id
--effectivity_date = l_rev_date
WHERE group_id = grp_id
AND sort_order = '0000001';
UPDATE BOM_EXPLOSIONS_ALL BE
SET (quantity_of_children, total_qty_at_next_level) =
(SELECT count(*), sum(component_quantity) FROM bom_explosions_all
WHERE group_id = BE.group_id AND
parent_sort_order = BE.sort_order)
WHERE group_id = grp_id AND comp_bill_seq_id IS NOT NULL;
UPDATE BOM_EXPLOSIONS_ALL
SET rexplode_flag = 0
WHERE group_id = grp_id AND comp_bill_seq_id IS NOT NULL AND rexplode_flag = 1;
UPDATE BOM_EXPLOSIONS_ALL
SET rexplode_flag = 1
WHERE group_id = grp_id AND comp_bill_seq_id IS NOT NULL AND rexplode_flag = 0;
revision_highdate_array.DELETE;
component_revision_array.DELETE;
component_revision_id_array.DELETE;
component_revision_label_array.DELETE;
asss_without_access_array.DELETE;
compseqs_without_access_array.DELETE;
access_flag_array.DELETE;
change_policy_array.DELETE;
SELECT 'HZ_PARTY'||':'||person_party_id INTO l_person
FROM fnd_user WHERE user_name = FND_Global.User_Name;
/* Select all the assemblies for which the user has no access */
EXECUTE IMMEDIATE 'SELECT BE.new_component_code FROM bom_explosions_all BE WHERE BE.group_id = '||grp_id||' AND comp_bill_seq_id IS NOT NULL AND NOT '|| l_predicate
BULK COLLECT INTO asss_without_access_array;
/* Select all the leaf nodes for which the user has no access */
EXECUTE IMMEDIATE 'SELECT BE.component_sequence_id FROM bom_explosions_all BE WHERE BE.group_id = '||grp_id||' AND comp_bill_seq_id IS NULL AND NOT '|| l_predicate
BULK COLLECT INTO compseqs_without_access_array;
SELECT exclusion_path
BULK COLLECT INTO rev_specific_exclusions_array
FROM bom_explosions_all be,
bom_rules_b rule,
bom_exclusion_rule_def excl
WHERE be.group_id = grp_id
AND be.comp_bill_seq_id IS NOT NULL --get only the bills not its components
AND be.comp_bill_seq_id = rule.bill_sequence_id
AND rule.rule_id = excl.rule_id
AND excl.from_revision_id IS NOT NULL --conditions to pickup only rev level exclusions
AND excl.implementation_date IS NOT NULL -- do not pickup the pending exclusions
AND excl.disable_date IS NULL -- do not pickup the disabled exclusions
AND excl.acd_type = 1 -- pickup only the exclusion entries
AND Get_Component_Revision(nvl(be.component_sequence_id,0)) >= (SELECT revision FROM mtl_item_revisions_b WHERE
revision_id = excl.from_revision_id) AND
( excl.to_revision_id IS NULL OR
Get_Component_Revision(nvl(be.component_sequence_id,0)) <= (SELECT revision FROM mtl_item_revisions_b WHERE
revision_id = excl.to_revision_id));
SELECT revision, revision_id,revision_label FROM mtl_item_revisions_b WHERE
inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
effectivity_date <= p_effectivity_date
AND ((BOM_GLOBALS.get_show_Impl_comps_only = 'Y' AND implementation_date IS NOT NULL) OR BOM_GLOBALS.get_show_Impl_comps_only = 'N') -- added for Bug 7242865
ORDER BY effectivity_date DESC;