eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BOM_EXPLODER_PUB dependencies on MTL_ITEM_REVISIONS_B

Line 152: SELECT 'Y' INTO l_result FROM mtl_item_revisions_b WHERE inventory_item_id = p_inventory_item_id AND

148: IF p_revision_id IS NULL
149: THEN
150: Return 'N';
151: END IF;
152: SELECT 'Y' INTO l_result FROM mtl_item_revisions_b WHERE inventory_item_id = p_inventory_item_id AND
153: organization_id = p_organization_id AND revision_id = p_revision_id;
154: Return l_result;
155: EXCEPTION WHEN NO_DATA_FOUND
156: THEN

Line 167: SELECT revision INTO l_revision FROM mtl_item_revisions_b WHERE revision_id = p_revision_id;

163: IF p_revision_id IS NULL
164: THEN
165: Return null;
166: END IF;
167: SELECT revision INTO l_revision FROM mtl_item_revisions_b WHERE revision_id = p_revision_id;
168: Return l_revision;
169: EXCEPTION WHEN NO_DATA_FOUND
170: THEN
171: Return null;

Line 178: SELECT revision, revision_id,revision_label FROM mtl_item_revisions_b WHERE

174: FUNCTION Get_Current_RevisionDetails( p_inventory_item_id IN NUMBER,
175: p_organization_id IN NUMBER,
176: p_effectivity_date IN DATE) RETURN VARCHAR2 IS
177: CURSOR c1 IS
178: SELECT revision, revision_id,revision_label FROM mtl_item_revisions_b WHERE
179: inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
180: effectivity_date <= p_effectivity_date
181: 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
182: ORDER BY effectivity_date DESC;

Line 969: FROM MTL_ITEM_REVISIONS_B

965: AND BIC.DISABLE_DATE IS NULL
966: AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
967: AND ((BET.PLAN_LEVEL > 0
968: AND EXISTS (SELECT NULL
969: FROM MTL_ITEM_REVISIONS_B
970: WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
971: AND ORGANIZATION_ID = BET.ORGANIZATION_ID
972: AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
973: AND (BIC.TO_END_ITEM_REV_ID IS NULL

Line 978: FROM MTL_ITEM_REVISIONS_B

974: OR (NVL(BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(BET.COMPONENT_ITEM_ID,BET.ORGANIZATION_ID,C_REV_DATE),
975: (SELECT STARTING_REVISION
976: FROM MTL_PARAMETERS
977: WHERE ORGANIZATION_ID = BET.ORGANIZATION_ID)) <= (SELECT REVISION
978: FROM MTL_ITEM_REVISIONS_B
979: WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
980: AND ORGANIZATION_ID = BET.ORGANIZATION_ID
981: AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))
982: OR (EXISTS (SELECT NULL

Line 983: FROM MTL_ITEM_REVISIONS_B

979: WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
980: AND ORGANIZATION_ID = BET.ORGANIZATION_ID
981: AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))
982: OR (EXISTS (SELECT NULL
983: FROM MTL_ITEM_REVISIONS_B
984: WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
985: AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
986: AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
987: AND (BIC.TO_END_ITEM_REV_ID IS NULL

Line 989: FROM MTL_ITEM_REVISIONS_B

985: AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
986: AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
987: AND (BIC.TO_END_ITEM_REV_ID IS NULL
988: OR (BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(L_END_ITEM_ID,L_END_ITEM_ORG_ID,C_REV_DATE) <= (SELECT REVISION
989: FROM MTL_ITEM_REVISIONS_B
990: WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
991: AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
992: AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))))
993: OR (C_EXPLODE_OPTION = 2 --Current

Line 998: FROM MTL_ITEM_REVISIONS_B

994: AND BIC.DISABLE_DATE IS NULL
995: AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
996: AND ((BET.PLAN_LEVEL > 0
997: AND EXISTS (SELECT NULL
998: FROM MTL_ITEM_REVISIONS_B
999: WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
1000: AND ORGANIZATION_ID = BET.ORGANIZATION_ID
1001: AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
1002: AND ((NVL(BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(BET.COMPONENT_ITEM_ID,BET.ORGANIZATION_ID,C_REV_DATE),

Line 1006: FROM MTL_ITEM_REVISIONS_B

1002: AND ((NVL(BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(BET.COMPONENT_ITEM_ID,BET.ORGANIZATION_ID,C_REV_DATE),
1003: (SELECT STARTING_REVISION
1004: FROM MTL_PARAMETERS
1005: WHERE ORGANIZATION_ID = BET.ORGANIZATION_ID)) > = (SELECT REVISION
1006: FROM MTL_ITEM_REVISIONS_B
1007: WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
1008: AND ORGANIZATION_ID = BET.ORGANIZATION_ID
1009: AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID))
1010: AND (BIC.TO_END_ITEM_REV_ID IS NULL

Line 1015: FROM MTL_ITEM_REVISIONS_B

1011: OR (NVL(BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(BET.COMPONENT_ITEM_ID,BET.ORGANIZATION_ID,C_REV_DATE),
1012: (SELECT STARTING_REVISION
1013: FROM MTL_PARAMETERS
1014: WHERE ORGANIZATION_ID = BET.ORGANIZATION_ID)) <= (SELECT REVISION
1015: FROM MTL_ITEM_REVISIONS_B
1016: WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
1017: AND ORGANIZATION_ID = BET.ORGANIZATION_ID
1018: AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))
1019: )

Line 1021: FROM MTL_ITEM_REVISIONS_B

1017: AND ORGANIZATION_ID = BET.ORGANIZATION_ID
1018: AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))
1019: )
1020: OR (EXISTS (SELECT NULL
1021: FROM MTL_ITEM_REVISIONS_B
1022: WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
1023: AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
1024: AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
1025: AND ((BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(L_END_ITEM_ID,L_END_ITEM_ORG_ID,C_REV_DATE) >= (SELECT REVISION

Line 1026: FROM MTL_ITEM_REVISIONS_B

1022: WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
1023: AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
1024: AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID)
1025: AND ((BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(L_END_ITEM_ID,L_END_ITEM_ORG_ID,C_REV_DATE) >= (SELECT REVISION
1026: FROM MTL_ITEM_REVISIONS_B
1027: WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
1028: AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
1029: AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID))
1030: AND (BIC.TO_END_ITEM_REV_ID IS NULL

Line 1032: FROM MTL_ITEM_REVISIONS_B

1028: AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
1029: AND REVISION_ID = BIC.FROM_END_ITEM_REV_ID))
1030: AND (BIC.TO_END_ITEM_REV_ID IS NULL
1031: OR (BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(L_END_ITEM_ID,L_END_ITEM_ORG_ID,C_REV_DATE) <= (SELECT REVISION
1032: FROM MTL_ITEM_REVISIONS_B
1033: WHERE INVENTORY_ITEM_ID = L_END_ITEM_ID
1034: AND ORGANIZATION_ID = L_END_ITEM_ORG_ID
1035: AND REVISION_ID = BIC.TO_END_ITEM_REV_ID))))
1036: )))

Line 1052: FROM mtl_item_revisions_b

1048: AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
1049: AND
1050: ( (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'),
1051: to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
1052: FROM mtl_item_revisions_b
1053: WHERE inventory_item_id = l_end_item_id AND
1054: organization_id = l_end_item_org_id AND
1055: revision_id = nvl(BIC.TO_END_ITEM_REV_ID,BIC.FROM_END_ITEM_REV_ID)))
1056: OR

Line 1059: FROM mtl_item_revisions_b

1055: revision_id = nvl(BIC.TO_END_ITEM_REV_ID,BIC.FROM_END_ITEM_REV_ID)))
1056: OR
1057: (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'),
1058: to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
1059: FROM mtl_item_revisions_b
1060: WHERE inventory_item_id = BET.component_item_id AND
1061: organization_id = BET.organization_id AND
1062: revision_id = nvl(BIC.TO_END_ITEM_REV_ID,BIC.FROM_END_ITEM_REV_ID)))
1063: )

Line 1073: FROM mtl_item_revisions_b

1069: AND
1070: (
1071: (l_end_item_minor_rev_code >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1072: to_char(nvl(BIC.from_end_item_minor_rev_id,0)))
1073: FROM mtl_item_revisions_b
1074: WHERE inventory_item_id = l_end_item_id AND
1075: organization_id = l_end_item_org_id AND
1076: revision_id = BIC.FROM_END_ITEM_REV_ID)
1077: AND (BIC.to_end_item_rev_id IS NULL OR

Line 1080: FROM mtl_item_revisions_b

1076: revision_id = BIC.FROM_END_ITEM_REV_ID)
1077: AND (BIC.to_end_item_rev_id IS NULL OR
1078: l_end_item_minor_rev_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1079: to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
1080: FROM mtl_item_revisions_b
1081: WHERE inventory_item_id = l_end_item_id AND
1082: organization_id = l_end_item_org_id AND
1083: revision_id = BIC.TO_END_ITEM_REV_ID) ))
1084:

Line 1089: FROM mtl_item_revisions_b

1085: OR
1086:
1087: (BET.minor_revision_code >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1088: to_char(nvl(BIC.from_end_item_minor_rev_id,0)))
1089: FROM mtl_item_revisions_b
1090: WHERE inventory_item_id = BET.component_item_id AND
1091: organization_id = BET.organization_id AND
1092: revision_id = BIC.FROM_END_ITEM_REV_ID)
1093: AND (BIC.to_end_item_rev_id IS NULL OR

Line 1096: FROM mtl_item_revisions_b

1092: revision_id = BIC.FROM_END_ITEM_REV_ID)
1093: AND (BIC.to_end_item_rev_id IS NULL OR
1094: BET.minor_revision_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1095: to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
1096: FROM mtl_item_revisions_b
1097: WHERE inventory_item_id = BET.component_item_id AND
1098: organization_id = BET.organization_id AND
1099: revision_id = BIC.TO_END_ITEM_REV_ID)) )
1100: )

Line 1156: FROM mtl_item_revisions_b WHERE revision_id = BIC.FROM_OBJECT_REVISION_ID)

1152: OR
1153: ( BET.OBJ_NAME IS NULL AND
1154: BET.minor_revision_code >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1155: to_char(nvl(BIC.from_minor_revision_id,0)))
1156: FROM mtl_item_revisions_b WHERE revision_id = BIC.FROM_OBJECT_REVISION_ID)
1157: AND (BIC.to_object_revision_id IS NULL OR
1158: BET.minor_revision_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1159: to_char(nvl(BIC.to_minor_revision_id,9999999999999999)))
1160: FROM mtl_item_revisions_b WHERE revision_id = BIC.TO_OBJECT_REVISION_ID))

Line 1160: FROM mtl_item_revisions_b WHERE revision_id = BIC.TO_OBJECT_REVISION_ID))

1156: FROM mtl_item_revisions_b WHERE revision_id = BIC.FROM_OBJECT_REVISION_ID)
1157: AND (BIC.to_object_revision_id IS NULL OR
1158: BET.minor_revision_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1159: to_char(nvl(BIC.to_minor_revision_id,9999999999999999)))
1160: FROM mtl_item_revisions_b WHERE revision_id = BIC.TO_OBJECT_REVISION_ID))
1161: )
1162: )
1163: )
1164: )

Line 1235: SELECT revision_id, revision, revision_label FROM mtl_item_revisions_b WHERE revision_id = p_revision_id;

1231: total number;
1232:
1233: /*
1234: CURSOR getItemRevDetails (p_revision_id IN NUMBER) IS
1235: SELECT revision_id, revision, revision_label FROM mtl_item_revisions_b WHERE revision_id = p_revision_id;
1236: */
1237:
1238: CURSOR getCurrentMinorRev (p_obj_name IN VARCHAR2,
1239: p_pk1_value IN VARCHAR2,

Line 1250: SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(p_minor_rev_id)) mrev_code FROM mtl_item_revisions_b

1246: nvl(pk3_value,'-1') = nvl(p_pk3_value,'-1');
1247:
1248: CURSOR getCurrentMinorRevCode (p_revision_id IN NUMBER,
1249: p_minor_rev_id IN NUMBER) IS
1250: SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(p_minor_rev_id)) mrev_code FROM mtl_item_revisions_b
1251: WHERE revision_id = p_revision_id;
1252:
1253:
1254: CURSOR getItemRevision (p_inventory_item_id IN NUMBER,

Line 1259: FROM mtl_item_revisions_b MIR

1255: p_organization_id IN NUMBER,
1256: p_revision_date IN DATE,
1257: p_impl_flag IN NUMBER) IS
1258: SELECT revision,revision_label,revision_id
1259: FROM mtl_item_revisions_b MIR
1260: WHERE mir.inventory_item_id = p_inventory_item_id
1261: AND mir.organization_id = p_organization_id
1262: AND mir.effectivity_date <= p_revision_date
1263: AND (p_impl_flag = 2 OR (p_impl_flag = 1 AND mir.implementation_date IS NOT NULL) )

Line 1310: FROM Mtl_Item_Revisions_B rev2 , Mtl_Item_Revisions_B rev1

1306: NVL( MIN(rev2.Effectivity_Date - 1/(60*60*24)),
1307: GREATEST(TO_DATE('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.Effectivity_Date)
1308: ) High_Date,
1309: rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label
1310: FROM Mtl_Item_Revisions_B rev2 , Mtl_Item_Revisions_B rev1
1311: WHERE rev1.revision_id = p_revision_id AND rev1.Organization_Id = rev2.Organization_Id(+)
1312: AND rev1.Inventory_Item_Id = rev2.Inventory_Item_Id(+) AND rev2.Effectivity_Date(+) > rev1.Effectivity_Date
1313: AND rev2.implementation_date (+) IS NOT NULL
1314: GROUP BY rev1.Organization_Id, rev1.Inventory_Item_Id, rev1.Revision_Id, rev1.Revision, rev1.Effectivity_Date,

Line 2795: rev1.implementation_date, rev1.change_notice FROM mtl_item_revisions_b rev2 , mtl_item_revisions_b rev1

2791: SELECT revision revision, revision_id revision_id FROM (
2792: SELECT rev1.organization_id , rev1.inventory_item_id , rev1.revision_id , rev1.revision ,
2793: rev1.effectivity_date , nvl(min(rev2.effectivity_date - 1/(60*60*24)),
2794: greatest(to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.effectivity_date)) high_date,
2795: rev1.implementation_date, rev1.change_notice FROM mtl_item_revisions_b rev2 , mtl_item_revisions_b rev1
2796: WHERE rev1.inventory_item_id = p_inventory_item_id AND rev1.organization_id = p_organization_id AND
2797: rev1.organization_id = rev2.organization_id(+) AND
2798: rev1.inventory_item_id = rev2.inventory_item_id(+) AND
2799: rev2.effectivity_date(+) > rev1.effectivity_date

Line 2805: SELECT inventory_item_id, organization_id, revision,effectivity_date FROM mtl_item_revisions_b

2801: rev1.revision , rev1.effectivity_date , rev1.implementation_date , rev1.change_notice)
2802: WHERE p_effective_date BETWEEN effectivity_date AND high_date;
2803:
2804: CURSOR getEndItemRev (p_item_revision_id IN NUMBER) IS
2805: SELECT inventory_item_id, organization_id, revision,effectivity_date FROM mtl_item_revisions_b
2806: WHERE revision_id = p_item_revision_id;
2807:
2808: CURSOR getCurrentMinorRev (p_obj_name IN VARCHAR2,
2809: p_pk1_value IN VARCHAR2,

Line 2825: SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(p_minor_rev_id)) mrev_code FROM mtl_item_revisions_b

2821: AND pk3_value = p_item_rev_id;
2822:
2823: CURSOR getEndItemMinorRevCode (p_revision_id IN NUMBER,
2824: p_minor_rev_id IN NUMBER) IS
2825: SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(p_minor_rev_id)) mrev_code FROM mtl_item_revisions_b
2826: WHERE revision_id = p_revision_id;
2827:
2828: CURSOR getPreferredStructure ( p_obj_name IN VARCHAR2,
2829: p_pk1_value IN VARCHAR2,

Line 2844: SELECT effectivity_date, revision_id, revision FROM mtl_item_revisions_b WHERE

2840: ORDER BY sort_order;
2841:
2842: CURSOR c_get_first_revision (p_inventory_item_id IN NUMBER,
2843: p_organization_id IN NUMBER) IS
2844: SELECT effectivity_date, revision_id, revision FROM mtl_item_revisions_b WHERE
2845: inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
2846: effectivity_date = ( SELECT min(effectivity_date) FROM mtl_item_revisions_b
2847: WHERE inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id );
2848:

Line 2846: effectivity_date = ( SELECT min(effectivity_date) FROM mtl_item_revisions_b

2842: CURSOR c_get_first_revision (p_inventory_item_id IN NUMBER,
2843: p_organization_id IN NUMBER) IS
2844: SELECT effectivity_date, revision_id, revision FROM mtl_item_revisions_b WHERE
2845: inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
2846: effectivity_date = ( SELECT min(effectivity_date) FROM mtl_item_revisions_b
2847: WHERE inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id );
2848:
2849: CURSOR c_Pkg_Structure_Type IS
2850: SELECT structure_type_id

Line 2856: mir.revision revision FROM bom_explosions_all bet, mtl_item_revisions_b mir

2852: WHERE structure_type_name = 'Packaging Hierarchy';
2853:
2854: CURSOR getComponentFixedRevisions (p_group_id IN NUMBER) IS
2855: SELECT bet.component_sequence_id, bet.component_item_revision_id revision_id,
2856: mir.revision revision FROM bom_explosions_all bet, mtl_item_revisions_b mir
2857: WHERE bet.group_id = p_group_id AND bet.plan_level <> 0 AND nvl(bet.component_item_revision_id,0) <> 0
2858: AND bet.component_item_revision_id = mir.revision_id;
2859:
2860: CURSOR getFixedRevDetails (p_group_id IN NUMBER) IS

Line 2882: FROM bom_explosions_all bet, Mtl_Item_Revisions_B rev2 , Mtl_Item_Revisions_B rev1

2878: NVL( MIN(rev2.Effectivity_Date - 1/(60*60*24)),
2879: GREATEST(TO_DATE('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.Effectivity_Date)
2880: ) High_Date,
2881: rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label
2882: FROM bom_explosions_all bet, Mtl_Item_Revisions_B rev2 , Mtl_Item_Revisions_B rev1
2883: WHERE bet.group_id = p_group_id AND bet.component_item_revision_id IS NOT NULL AND
2884: rev1.revision_id = bet.component_item_revision_id AND rev1.Organization_Id = rev2.Organization_Id(+)
2885: AND rev1.Inventory_Item_Id = rev2.Inventory_Item_Id(+) AND rev2.Effectivity_Date(+) > rev1.Effectivity_Date
2886: AND rev2.implementation_date (+) IS NOT NULL

Line 2938: FROM bom_explosions_all bet, mtl_item_revisions_b mirb, MTL_SYSTEM_ITEMS_b msi

2934: NVL(mirb.current_phase_id , msi.current_phase_id) AS phase_id,
2935: msi.item_catalog_group_id item_catalog_group_id,
2936: msi.inventory_item_id, msi.organization_id , mirb.revision_id,
2937: bet.component_sequence_id, bet.structure_type_id
2938: FROM bom_explosions_all bet, mtl_item_revisions_b mirb, MTL_SYSTEM_ITEMS_b msi
2939: WHERE bet.group_id = p_group_id AND bet.comp_bill_seq_id IS NOT NULL
2940: AND bet.component_item_id = msi.INVENTORY_ITEM_ID AND
2941: bet.organization_id = msi.ORGANIZATION_ID AND
2942: mirb.revision_id = BOM_EXPLODER_PUB.Get_Component_Revision_Id(NVL(BET.component_sequence_id,0))

Line 3558: 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

3554: -- Get the end item information
3555: IF l_end_item_id IS NULL THEN
3556:
3557: BEGIN
3558: 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
3559: WHERE revision_id = l_end_item_revision_id;
3560: EXCEPTION WHEN OTHERS THEN
3561: --Dbms_Output.put_line('End item revision is not valid'||sqlerrm);
3562: raise parameter_error;

Line 4233: AND Get_Component_Revision(nvl(be.component_sequence_id,0)) >= (SELECT revision FROM mtl_item_revisions_b WHERE

4229: AND excl.from_revision_id IS NOT NULL --conditions to pickup only rev level exclusions
4230: AND excl.implementation_date IS NOT NULL -- do not pickup the pending exclusions
4231: AND excl.disable_date IS NULL -- do not pickup the disabled exclusions
4232: AND excl.acd_type = 1 -- pickup only the exclusion entries
4233: AND Get_Component_Revision(nvl(be.component_sequence_id,0)) >= (SELECT revision FROM mtl_item_revisions_b WHERE
4234: revision_id = excl.from_revision_id) AND
4235: ( excl.to_revision_id IS NULL OR
4236: Get_Component_Revision(nvl(be.component_sequence_id,0)) <= (SELECT revision FROM mtl_item_revisions_b WHERE
4237: revision_id = excl.to_revision_id));

Line 4236: Get_Component_Revision(nvl(be.component_sequence_id,0)) <= (SELECT revision FROM mtl_item_revisions_b WHERE

4232: AND excl.acd_type = 1 -- pickup only the exclusion entries
4233: AND Get_Component_Revision(nvl(be.component_sequence_id,0)) >= (SELECT revision FROM mtl_item_revisions_b WHERE
4234: revision_id = excl.from_revision_id) AND
4235: ( excl.to_revision_id IS NULL OR
4236: Get_Component_Revision(nvl(be.component_sequence_id,0)) <= (SELECT revision FROM mtl_item_revisions_b WHERE
4237: revision_id = excl.to_revision_id));
4238: error_code := out_code;
4239: err_msg := out_message;
4240:

Line 4699: SELECT revision, revision_id,revision_label FROM mtl_item_revisions_b WHERE

4695: FUNCTION Get_Current_RevisionId( p_inventory_item_id IN NUMBER,
4696: p_organization_id IN NUMBER,
4697: p_effectivity_date IN DATE) RETURN NUMBER IS
4698: CURSOR c1 IS
4699: SELECT revision, revision_id,revision_label FROM mtl_item_revisions_b WHERE
4700: inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
4701: effectivity_date <= p_effectivity_date
4702: 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
4703: ORDER BY effectivity_date DESC;