[Home] [Help]
936: )
937: IS
938: SELECT bcb1.component_sequence_id from_component_seq_id,
939: bcb2.component_sequence_id to_sequence_id
940: FROM bom_components_b bcb1,
941: bom_components_b bcb2
942: WHERE bcb1.bill_sequence_id = p_from_seq_id
943: AND bcb1.component_sequence_id = bcb2.created_by
944: AND bcb2.bill_sequence_id = p_to_seq_id;
937: IS
938: SELECT bcb1.component_sequence_id from_component_seq_id,
939: bcb2.component_sequence_id to_sequence_id
940: FROM bom_components_b bcb1,
941: bom_components_b bcb2
942: WHERE bcb1.bill_sequence_id = p_from_seq_id
943: AND bcb1.component_sequence_id = bcb2.created_by
944: AND bcb2.bill_sequence_id = p_to_seq_id;
945: l_index NUMBER := 0;
952: IS
953: SELECT eec.change_id,
954: bcb.component_sequence_id
955: FROM eng_engineering_changes eec,
956: bom_components_b bcb
957: WHERE eec.change_notice = p_change_notice
958: AND eec.organization_id = p_local_org_id
959: AND bcb.bill_sequence_id = p_bill_seq_id;
960: CURSOR l_fixed_rev_comp_csr (
1392:
1393: IF l_from_eff_ctrl = 1 AND l_to_eff_ctrl = 1 THEN -- Date - Date
1394:
1395:
1396: INSERT INTO bom_components_b
1397: (shipping_allowed,
1398: required_to_ship,
1399: required_for_revenue,
1400: include_on_ship_docs,
1677: THEN 0
1678: ELSE
1679: to_number(NULL)
1680: END AS to_minor_revision_id
1681: FROM bom_components_b bic,
1682: mtl_system_items msi,
1683: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
1684: bom_copy_explosions_v bev
1685: WHERE bic.bill_sequence_id = x_from_sequence_id
1708: )
1709: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
1710: (
1711: SELECT 1
1712: FROM bom_components_b bcb
1713: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
1714: AND bcb.change_notice = context_eco
1715: AND bcb.acd_type = 3
1716: AND bcb.effectivity_date <= trgt_comps_eff_date
1788: OR ( l_from_eff_ctrl = 3 AND l_to_eff_ctrl = 3 ) -- Serial
1789: ) THEN
1790:
1791:
1792: INSERT INTO bom_components_b
1793: (shipping_allowed,
1794: required_to_ship,
1795: required_for_revenue,
1796: include_on_ship_docs,
2076: THEN 0
2077: ELSE
2078: to_number(NULL)
2079: END AS to_minor_revision_id
2080: FROM bom_components_b bic,
2081: mtl_system_items msi,
2082: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
2083: bom_copy_explosions_v bev
2084: WHERE bic.bill_sequence_id = x_from_sequence_id
2108: )
2109: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
2110: (
2111: SELECT 1
2112: FROM bom_components_b bcb
2113: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
2114: AND bcb.change_notice = context_eco
2115: AND bcb.acd_type = 3
2116: AND bcb.effectivity_date <= trgt_comps_eff_date
2188:
2189: ELSIF l_from_eff_ctrl = 4 AND l_to_eff_ctrl = 4 THEN -- Rev - Rev
2190:
2191:
2192: INSERT INTO bom_components_b
2193: (shipping_allowed,
2194: required_to_ship,
2195: required_for_revenue,
2196: include_on_ship_docs,
2553: THEN 0
2554: ELSE
2555: to_number(NULL)
2556: END AS to_minor_revision_id
2557: FROM bom_components_b bic,
2558: mtl_system_items msi,
2559: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
2560: bom_copy_explosions_v bev
2561: WHERE bic.bill_sequence_id = x_from_sequence_id
2585: )
2586: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
2587: (
2588: SELECT 1
2589: FROM bom_components_b bcb
2590: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
2591: AND bcb.change_notice = context_eco
2592: AND bcb.acd_type = 3
2593: AND bcb.effectivity_date <= trgt_comps_eff_date
2664: ELSIF l_from_eff_ctrl = 4 AND l_to_eff_ctrl = 1 THEN -- Rev - Date
2665:
2666:
2667:
2668: INSERT INTO bom_components_b
2669: (shipping_allowed,
2670: required_to_ship,
2671: required_for_revenue,
2672: include_on_ship_docs,
2995: THEN 0
2996: ELSE
2997: to_number(NULL)
2998: END AS to_minor_revision_id
2999: FROM bom_components_b bic,
3000: mtl_system_items msi,
3001: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
3002: bom_copy_explosions_v bev
3003: WHERE bic.bill_sequence_id = x_from_sequence_id
3026: )
3027: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
3028: (
3029: SELECT 1
3030: FROM bom_components_b bcb
3031: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
3032: AND bcb.change_notice = context_eco
3033: AND bcb.acd_type = 3
3034: AND bcb.effectivity_date <= trgt_comps_eff_date
3104: );
3105: ELSIF l_from_eff_ctrl = 1 AND l_to_eff_ctrl = 4 THEN -- Date - Rev
3106:
3107:
3108: INSERT INTO bom_components_b
3109: (shipping_allowed,
3110: required_to_ship,
3111: required_for_revenue,
3112: include_on_ship_docs,
3444: THEN 0
3445: ELSE
3446: to_number(NULL)
3447: END AS to_minor_revision_id
3448: FROM bom_components_b bic,
3449: mtl_system_items msi,
3450: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
3451: bom_copy_explosions_v bev
3452: WHERE bic.bill_sequence_id = x_from_sequence_id
3477: )
3478: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
3479: (
3480: SELECT 1
3481: FROM bom_components_b bcb
3482: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
3483: AND bcb.change_notice = context_eco
3484: AND bcb.acd_type = 3
3485: AND bcb.effectivity_date <= trgt_comps_eff_date
3716: fnd_global.conc_request_id,
3717: NULL,
3718: fnd_global.conc_program_id,
3719: sysdate
3720: FROM bom_components_b bcb
3721: WHERE bcb.component_sequence_id = l_from_comps(l_index);
3722: l_msg_count := l_error_msg_tbl.next(l_msg_count);
3723: END LOOP;
3724: END IF;
3750: END IF;
3751:
3752: SELECT COUNT (*)
3753: INTO l_no_access_comp_cnt
3754: FROM bom_components_b bcb,
3755: mtl_system_items_b_kfv msbk1,
3756: bom_copy_explosions_v bev
3757: WHERE bcb.bill_sequence_id = x_from_sequence_id
3758: AND bcb.component_item_id = msbk1.inventory_item_id
3776: )
3777: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
3778: (
3779: SELECT 1
3780: FROM bom_components_b bcb1
3781: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
3782: AND bcb1.change_notice = context_eco
3783: AND bcb1.acd_type = 3
3784: AND bcb1.effectivity_date <= trgt_comps_eff_date
3847: IF (from_org_id <> to_org_id)
3848: THEN
3849: SELECT COUNT (*)
3850: INTO l_no_access_comp_cnt
3851: FROM bom_components_b bcb,
3852: mtl_system_items_b_kfv msbk1,
3853: bom_copy_explosions_v bev
3854: WHERE bcb.bill_sequence_id = x_from_sequence_id
3855: AND bcb.component_item_id = msbk1.inventory_item_id
3873: )
3874: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
3875: (
3876: SELECT 1
3877: FROM bom_components_b bcb1
3878: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
3879: AND bcb1.change_notice = context_eco
3880: AND bcb1.acd_type = 3
3881: AND bcb1.effectivity_date <= trgt_comps_eff_date
3982: fnd_global.conc_request_id,
3983: NULL,
3984: fnd_global.conc_program_id,
3985: sysdate
3986: FROM bom_components_b bcb,
3987: mtl_system_items_b msib, -- component
3988: bom_copy_explosions_v bev
3989: WHERE bcb.bill_sequence_id = x_from_sequence_id
3990: AND bcb.component_item_id = msib.inventory_item_id
4003: )
4004: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
4005: (
4006: SELECT 1
4007: FROM bom_components_b bcb1
4008: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
4009: AND bcb1.change_notice = context_eco
4010: AND bcb1.acd_type = 3
4011: AND bcb1.effectivity_date <= trgt_comps_eff_date
4071: fnd_global.conc_request_id,
4072: NULL,
4073: fnd_global.conc_program_id,
4074: sysdate
4075: FROM bom_components_b bcb,
4076: bom_copy_explosions_v bev
4077: WHERE bcb.bill_sequence_id = x_from_sequence_id
4078: AND bcb.component_item_id <> to_item_id
4079: AND bcb.eco_for_production <> 2
4088: )
4089: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
4090: (
4091: SELECT 1
4092: FROM bom_components_b bcb1
4093: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
4094: AND bcb1.change_notice = context_eco
4095: AND bcb1.acd_type = 3
4096: AND bcb1.effectivity_date <= trgt_comps_eff_date
4153: user_id,
4154: SYSDATE,
4155: user_id,
4156: 'E'
4157: FROM bom_components_b bcb,
4158: mtl_system_items_b_kfv msbk1,
4159: mtl_system_items_b_kfv msbk2,
4160: bom_copy_explosions_v bev
4161: WHERE bcb.bill_sequence_id = x_from_sequence_id
4210: fnd_global.conc_request_id,
4211: NULL,
4212: fnd_global.conc_program_id,
4213: sysdate
4214: FROM bom_components_b bcb,
4215: mtl_system_items_b msib,
4216: bom_copy_explosions_v bev
4217: WHERE bcb.bill_sequence_id = x_from_sequence_id
4218: AND bcb.component_item_id = msib.inventory_item_id
4234: )
4235: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
4236: (
4237: SELECT 1
4238: FROM bom_components_b bcb1
4239: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
4240: AND bcb1.change_notice = context_eco
4241: AND bcb1.acd_type = 3
4242: AND bcb1.effectivity_date <= trgt_comps_eff_date
4297: fnd_global.conc_request_id,
4298: NULL,
4299: fnd_global.conc_program_id,
4300: sysdate
4301: FROM bom_components_b bcb,
4302: bom_copy_explosions_v bev
4303: WHERE bcb.bill_sequence_id = x_from_sequence_id
4304: AND bcb.component_item_id <> to_item_id
4305: AND bcb.implementation_date IS NOT NULL
4314: )
4315: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
4316: (
4317: SELECT 1
4318: FROM bom_components_b bcb1
4319: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
4320: AND bcb1.change_notice = context_eco
4321: AND bcb1.acd_type = 3
4322: AND bcb1.effectivity_date <= trgt_comps_eff_date
4393: fnd_global.conc_request_id,
4394: NULL,
4395: fnd_global.conc_program_id,
4396: sysdate
4397: FROM bom_components_b bcb,
4398: mtl_system_items_b msib
4399: WHERE bcb.bill_sequence_id = to_sequence_id
4400: AND bcb.component_item_id = msib.inventory_item_id
4401: AND msib.organization_id = to_org_id
4458: fnd_global.conc_request_id,
4459: NULL,
4460: fnd_global.conc_program_id,
4461: sysdate
4462: FROM bom_components_b bcb,
4463: mtl_system_items_b msib
4464: WHERE bcb.bill_sequence_id = to_sequence_id
4465: AND bcb.component_item_id = msib.inventory_item_id
4466: AND msib.organization_id = to_org_id
4532: fnd_global.conc_request_id,
4533: NULL,
4534: fnd_global.conc_program_id,
4535: sysdate
4536: FROM bom_components_b bcb,
4537: bom_copy_explosions_v bev
4538: WHERE bcb.bill_sequence_id = x_from_sequence_id
4539: AND bcb.component_item_id <> to_item_id
4540: AND bcb.implementation_date IS NOT NULL
4551: )
4552: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
4553: (
4554: SELECT 1
4555: FROM bom_components_b bcb1
4556: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
4557: AND bcb1.change_notice = context_eco
4558: AND bcb1.acd_type = 3
4559: AND bcb1.effectivity_date <= trgt_comps_eff_date
4595: )
4596: THEN
4597:
4598:
4599: INSERT INTO bom_components_b
4600: (shipping_allowed,
4601: required_to_ship,
4602: required_for_revenue,
4603: include_on_ship_docs,
4892: l_count2 := replacement_items_arr.FIRST;
4893: l_count3 := replacement_items_rev_ids_arr.FIRST;
4894: IF (l_count1 IS NOT NULL AND l_count2 IS NOT NULL) THEN
4895: FORALL j IN replace_comps_arr.FIRST..replace_comps_arr.LAST
4896: UPDATE BOM_COMPONENTS_B
4897: SET COMPONENT_ITEM_ID = replacement_items_arr(j),
4898: COMPONENT_ITEM_REVISION_ID = replacement_items_rev_ids_arr(j)
4899: WHERE BILL_SEQUENCE_ID = to_sequence_id
4900: AND CREATED_BY = replace_comps_arr(j);
5021: ELSIF l_to_eff_ctrl = 4
5022: THEN
5023: SELECT COUNT (*)
5024: INTO dummy
5025: FROM bom_components_b bcb
5026: WHERE bcb.bill_sequence_id = to_sequence_id
5027: AND EXISTS (
5028: SELECT NULL
5029: FROM bom_components_b bcb2
5025: FROM bom_components_b bcb
5026: WHERE bcb.bill_sequence_id = to_sequence_id
5027: AND EXISTS (
5028: SELECT NULL
5029: FROM bom_components_b bcb2
5030: WHERE bcb2.bill_sequence_id = to_sequence_id
5031: AND bcb2.ROWID <> bcb.ROWID
5032: AND bcb2.operation_seq_num = bcb.operation_seq_num
5033: AND bcb2.component_item_id = bcb.component_item_id
5166: fnd_global.conc_request_id,
5167: NULL,
5168: fnd_global.conc_program_id,
5169: sysdate
5170: FROM bom_components_b bcb,
5171: mtl_system_items_b msib1,
5172: mtl_system_items_b msib2
5173: WHERE bcb.bill_sequence_id = to_sequence_id
5174: AND (msib1.bom_item_type = planning
5219: fnd_global.conc_request_id,
5220: NULL,
5221: fnd_global.conc_program_id,
5222: sysdate
5223: FROM bom_components_b bcb,
5224: mtl_system_items_b msib1,
5225: mtl_system_items_b msib2
5226: WHERE bcb.bill_sequence_id = to_sequence_id
5227: AND (msib1.bom_item_type IN (model, option_class)
5273: fnd_global.conc_request_id,
5274: NULL,
5275: fnd_global.conc_program_id,
5276: sysdate
5277: FROM bom_components_b bcb,
5278: mtl_system_items_b msib1, -- Comp
5279: mtl_system_items_b msib2 -- Structure
5280: WHERE bcb.bill_sequence_id = to_sequence_id
5281: AND (msib1.replenish_to_order_flag = 'Y'
5327: fnd_global.conc_request_id,
5328: NULL,
5329: fnd_global.conc_program_id,
5330: sysdate
5331: FROM bom_components_b bcb,
5332: mtl_system_items_b msib1, -- Comp
5333: mtl_system_items_b msib2 -- Structure
5334: WHERE bcb.bill_sequence_id = to_sequence_id
5335: AND (msib1.replenish_to_order_flag = 'Y'
5382: fnd_global.conc_request_id,
5383: NULL,
5384: fnd_global.conc_program_id,
5385: sysdate
5386: FROM bom_components_b bcb,
5387: mtl_system_items_b msib1, -- Comp
5388: mtl_system_items_b msib2 -- Structure
5389: WHERE bcb.bill_sequence_id = to_sequence_id
5390: AND (msib1.pick_components_flag = 'Y'
5433: sql_stmt_num := 43;
5434: --Turn off trigger BOMTBICX
5435: bom_globals.g_skip_bomtbicx := 'Y';
5436:
5437: UPDATE bom_components_b bic
5438: SET bic.wip_supply_type = phantom
5439: WHERE bic.bill_sequence_id = to_sequence_id
5440: AND EXISTS (
5441: SELECT NULL
5450: bom_globals.g_skip_bomtbicx := 'N';
5451: sql_stmt_num := 44;
5452: bom_globals.g_skip_bomtbicx := 'Y';
5453:
5454: UPDATE bom_components_b bic
5455: SET bic.optional = 1
5456: WHERE bic.bill_sequence_id = to_sequence_id
5457: AND EXISTS (
5458: SELECT NULL
5536: brd.attribute13,
5537: brd.attribute14,
5538: brd.attribute15
5539: FROM bom_reference_designators brd,
5540: bom_components_b bic,
5541: bom_copy_explosions_v bev
5542: WHERE bic.bill_sequence_id = to_sequence_id
5543: AND NVL (bic.eco_for_production, 2) = 2
5544: AND brd.component_sequence_id = bic.created_by
5705: bsc.attribute14,
5706: bsc.attribute15,
5707: bsc.enforce_int_requirements
5708: FROM bom_substitute_components bsc,
5709: bom_components_b bic,
5710: mtl_system_items msi,
5711: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
5712: bom_copy_explosions_v bev
5713: WHERE bic.bill_sequence_id = to_sequence_id
5956: NULL,
5957: fnd_global.conc_program_id,
5958: sysdate
5959: FROM bom_component_operations bco,
5960: bom_components_b bic,
5961: bom_operation_sequences bos,
5962: bom_copy_explosions_v bev
5963: WHERE bic.bill_sequence_id = to_sequence_id
5964: AND NVL (bic.eco_for_production, 2) = 2
6139: IF specific_copy_flag = 'N' THEN
6140: --Turn off trigger BOMTBICX
6141: bom_globals.g_skip_bomtbicx := 'Y';
6142:
6143: UPDATE bom_components_b bic
6144: SET created_by = user_id
6145: WHERE bic.bill_sequence_id = to_sequence_id;
6146:
6147: --Turn on trigger BOMTBICX
6459: Pass trgt_comps_eff_date and do this validation
6460: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
6461: (
6462: SELECT 1
6463: FROM bom_components_b bcb
6464: WHERE bcb.old_component_sequence_id = bev.component_sequence_id
6465: AND bcb.change_notice = context_eco
6466: AND bcb.acd_type = 3
6467: AND bcb.effectivity_date <= trgt_comps_eff_date
7090: IS
7091: BEGIN
7092: bom_globals.g_skip_bomtbicx := 'Y';
7093:
7094: UPDATE bom_components_b bic
7095: SET created_by = p_user_id
7096: WHERE bic.bill_sequence_id = p_to_bill_sequence_id;
7097:
7098: --Turn on trigger BOMTBICX
7338: )
7339: IS
7340: SELECT bcb1.component_sequence_id from_component_seq_id,
7341: bcb2.component_sequence_id to_sequence_id
7342: FROM bom_components_b bcb1,
7343: bom_components_b bcb2
7344: WHERE bcb1.bill_sequence_id = p_from_seq_id
7345: AND bcb1.component_sequence_id = bcb2.created_by
7346: AND bcb2.bill_sequence_id = p_to_seq_id
7339: IS
7340: SELECT bcb1.component_sequence_id from_component_seq_id,
7341: bcb2.component_sequence_id to_sequence_id
7342: FROM bom_components_b bcb1,
7343: bom_components_b bcb2
7344: WHERE bcb1.bill_sequence_id = p_from_seq_id
7345: AND bcb1.component_sequence_id = bcb2.created_by
7346: AND bcb2.bill_sequence_id = p_to_seq_id
7347: AND bcb2.component_sequence_id > p_last_copied_comp_seq_id;
7355: IS
7356: SELECT eec.change_id,
7357: bcb.component_sequence_id
7358: FROM eng_engineering_changes eec,
7359: bom_components_b bcb
7360: WHERE eec.change_notice = p_change_notice
7361: AND eec.organization_id = p_local_org_id
7362: AND bcb.bill_sequence_id = p_bill_seq_id;
7363: CURSOR l_fixed_rev_comp_csr (
7383: p_bill_seq_id IN NUMBER
7384: )
7385: IS
7386: SELECT max(bcb.component_sequence_id)
7387: FROM bom_components_b bcb
7388: WHERE bcb.bill_sequence_id = p_bill_seq_id;
7389: BEGIN
7390: SAVEPOINT begin_revised_item_bill_copy;
7391:
7616:
7617: IF l_from_eff_ctrl = 1 AND l_to_eff_ctrl = 1 THEN -- Date - Date
7618:
7619:
7620: INSERT INTO bom_components_b
7621: (shipping_allowed,
7622: required_to_ship,
7623: required_for_revenue,
7624: include_on_ship_docs,
7870: THEN 0
7871: ELSE
7872: to_number(NULL)
7873: END AS to_minor_revision_id
7874: FROM bom_components_b bic,
7875: mtl_system_items msi,
7876: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
7877: bom_copy_explosions_v bev
7878: WHERE bic.bill_sequence_id = x_from_sequence_id
7902: )
7903: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
7904: (
7905: SELECT 1
7906: FROM bom_components_b bcb
7907: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
7908: AND bcb.change_notice = context_eco
7909: AND bcb.acd_type = 3
7910: AND bcb.effectivity_date <= x_effectivity_date
7985: OR ( l_from_eff_ctrl = 3 AND l_to_eff_ctrl = 3 ) -- Serial
7986: ) THEN
7987:
7988:
7989: INSERT INTO bom_components_b
7990: (shipping_allowed,
7991: required_to_ship,
7992: required_for_revenue,
7993: include_on_ship_docs,
8239: THEN 0
8240: ELSE
8241: to_number(NULL)
8242: END AS to_minor_revision_id
8243: FROM bom_components_b bic,
8244: mtl_system_items msi,
8245: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
8246: bom_copy_explosions_v bev
8247: WHERE bic.bill_sequence_id = x_from_sequence_id
8270: )
8271: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
8272: (
8273: SELECT 1
8274: FROM bom_components_b bcb
8275: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
8276: AND bcb.change_notice = context_eco
8277: AND bcb.acd_type = 3
8278: AND bcb.effectivity_date <= x_effectivity_date
8351: );
8352: ELSIF l_from_eff_ctrl = 4 AND l_to_eff_ctrl = 4 THEN -- Rev - Rev
8353:
8354:
8355: INSERT INTO bom_components_b
8356: (shipping_allowed,
8357: required_to_ship,
8358: required_for_revenue,
8359: include_on_ship_docs,
8640: THEN 0
8641: ELSE
8642: to_number(NULL)
8643: END AS to_minor_revision_id
8644: FROM bom_components_b bic,
8645: mtl_system_items msi,
8646: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
8647: bom_copy_explosions_v bev
8648: WHERE bic.bill_sequence_id = x_from_sequence_id
8671: )
8672: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
8673: (
8674: SELECT 1
8675: FROM bom_components_b bcb
8676: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
8677: AND bcb.change_notice = context_eco
8678: AND bcb.acd_type = 3
8679: AND bcb.effectivity_date <= x_effectivity_date
8762: );
8763: ELSIF l_from_eff_ctrl = 4 AND l_to_eff_ctrl = 1 THEN -- Rev - Date
8764:
8765:
8766: INSERT INTO bom_components_b
8767: (shipping_allowed,
8768: required_to_ship,
8769: required_for_revenue,
8770: include_on_ship_docs,
9038: THEN 0
9039: ELSE
9040: to_number(NULL)
9041: END AS to_minor_revision_id
9042: FROM bom_components_b bic,
9043: mtl_system_items msi,
9044: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
9045: bom_copy_explosions_v bev
9046: WHERE bic.bill_sequence_id = x_from_sequence_id
9069: )
9070: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
9071: (
9072: SELECT 1
9073: FROM bom_components_b bcb
9074: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
9075: AND bcb.change_notice = context_eco
9076: AND bcb.acd_type = 3
9077: AND bcb.effectivity_date <= x_effectivity_date
9162: );
9163: ELSIF l_from_eff_ctrl = 1 AND l_to_eff_ctrl = 4 THEN -- Date - Rev
9164:
9165:
9166: INSERT INTO bom_components_b
9167: (shipping_allowed,
9168: required_to_ship,
9169: required_for_revenue,
9170: include_on_ship_docs,
9437: THEN 0
9438: ELSE
9439: to_number(NULL)
9440: END AS to_minor_revision_id
9441: FROM bom_components_b bic,
9442: mtl_system_items msi,
9443: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
9444: bom_copy_explosions_v bev
9445: WHERE bic.bill_sequence_id = x_from_sequence_id
9468: )
9469: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
9470: (
9471: SELECT 1
9472: FROM bom_components_b bcb
9473: WHERE bcb.old_component_sequence_id = bic.component_sequence_id
9474: AND bcb.change_notice = context_eco
9475: AND bcb.acd_type = 3
9476: AND bcb.effectivity_date <= x_effectivity_date
9632: END IF;
9633:
9634: SELECT COUNT (*)
9635: INTO l_no_access_comp_cnt
9636: FROM bom_components_b bcb,
9637: mtl_system_items_b_kfv msbk1,
9638: bom_copy_explosions_v bev
9639: WHERE bcb.bill_sequence_id = x_from_sequence_id
9640: AND bcb.component_item_id = msbk1.inventory_item_id
9658: )
9659: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
9660: (
9661: SELECT 1
9662: FROM bom_components_b bcb1
9663: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
9664: AND bcb1.change_notice = context_eco
9665: AND bcb1.acd_type = 3
9666: AND bcb1.effectivity_date <= x_effectivity_date
9733: fnd_global.conc_request_id,
9734: NULL,
9735: fnd_global.conc_program_id,
9736: sysdate
9737: FROM bom_components_b bcb,
9738: mtl_system_items_b_kfv msbk1,
9739: bom_copy_explosions_v bev
9740: WHERE msbk1.inventory_item_id = from_item_id
9741: AND msbk1.organization_id = from_org_id
9761: IF (from_org_id <> to_org_id)
9762: THEN
9763: SELECT COUNT (*)
9764: INTO l_no_access_comp_cnt
9765: FROM bom_components_b bcb,
9766: mtl_system_items_b_kfv msbk1,
9767: bom_copy_explosions_v bev
9768: WHERE bcb.bill_sequence_id = x_from_sequence_id
9769: AND bcb.component_item_id = msbk1.inventory_item_id
9787: )
9788: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
9789: (
9790: SELECT 1
9791: FROM bom_components_b bcb1
9792: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
9793: AND bcb1.change_notice = context_eco
9794: AND bcb1.acd_type = 3
9795: AND bcb1.effectivity_date <= x_effectivity_date
9862: fnd_global.conc_request_id,
9863: NULL,
9864: fnd_global.conc_program_id,
9865: sysdate
9866: FROM bom_components_b bcb,
9867: mtl_system_items_b_kfv msbk1,
9868: bom_copy_explosions_v bev
9869: WHERE msbk1.inventory_item_id = from_item_id
9870: AND msbk1.organization_id = from_org_id
9927: fnd_global.conc_request_id,
9928: NULL,
9929: fnd_global.conc_program_id,
9930: sysdate
9931: FROM bom_components_b bcb,
9932: mtl_system_items_b msib1, -- component
9933: bom_copy_explosions_v bev
9934: WHERE bcb.bill_sequence_id = x_from_sequence_id
9935: AND bcb.component_item_id = msib1.inventory_item_id
9948: )
9949: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
9950: (
9951: SELECT 1
9952: FROM bom_components_b bcb1
9953: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
9954: AND bcb1.change_notice = context_eco
9955: AND bcb1.acd_type = 3
9956: AND bcb1.effectivity_date <= x_effectivity_date
10029: fnd_global.conc_request_id,
10030: NULL,
10031: fnd_global.conc_program_id,
10032: sysdate
10033: FROM bom_components_b bcb,
10034: bom_copy_explosions_v bev
10035: WHERE bcb.bill_sequence_id = x_from_sequence_id
10036: AND bcb.component_item_id <> to_item_id
10037: AND bcb.eco_for_production <> 2
10046: )
10047: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
10048: (
10049: SELECT 1
10050: FROM bom_components_b bcb1
10051: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
10052: AND bcb1.change_notice = context_eco
10053: AND bcb1.acd_type = 3
10054: AND bcb1.effectivity_date <= x_effectivity_date
10128: fnd_global.conc_request_id,
10129: NULL,
10130: fnd_global.conc_program_id,
10131: sysdate
10132: FROM bom_components_b bcb,
10133: mtl_system_items_b msib1,
10134: bom_copy_explosions_v bev
10135: WHERE bcb.bill_sequence_id = x_from_sequence_id
10136: AND bcb.component_item_id = msib1.inventory_item_id
10148: )
10149: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
10150: (
10151: SELECT 1
10152: FROM bom_components_b bcb1
10153: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
10154: AND bcb1.change_notice = context_eco
10155: AND bcb1.acd_type = 3
10156: AND bcb1.effectivity_date <= x_effectivity_date
10228: fnd_global.conc_request_id,
10229: NULL,
10230: fnd_global.conc_program_id,
10231: sysdate
10232: FROM bom_components_b bcb,
10233: bom_copy_explosions_v bev
10234: WHERE bcb.bill_sequence_id = x_from_sequence_id
10235: AND bcb.component_item_id <> to_item_id
10236: AND bcb.implementation_date IS NOT NULL
10245: )
10246: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
10247: (
10248: SELECT 1
10249: FROM bom_components_b bcb1
10250: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
10251: AND bcb1.change_notice = context_eco
10252: AND bcb1.acd_type = 3
10253: AND bcb1.effectivity_date <= x_effectivity_date
10331: fnd_global.conc_request_id,
10332: NULL,
10333: fnd_global.conc_program_id,
10334: sysdate
10335: FROM bom_components_b bcb,
10336: mtl_system_items_b msib
10337: WHERE bcb.bill_sequence_id = to_sequence_id
10338: AND bcb.component_item_id = msib.inventory_item_id
10339: AND msib.organization_id = to_org_id
10396: fnd_global.conc_request_id,
10397: NULL,
10398: fnd_global.conc_program_id,
10399: sysdate
10400: FROM bom_components_b bcb,
10401: mtl_system_items_b msib
10402: WHERE bcb.bill_sequence_id = to_sequence_id
10403: AND bcb.component_item_id = msib.inventory_item_id
10404: AND msib.organization_id = to_org_id
10471: fnd_global.conc_request_id,
10472: NULL,
10473: fnd_global.conc_program_id,
10474: sysdate
10475: FROM bom_components_b bcb,
10476: bom_copy_explosions_v bev
10477: WHERE bcb.bill_sequence_id = x_from_sequence_id
10478: AND bcb.component_item_id <> to_item_id
10479: AND bcb.implementation_date IS NOT NULL
10490: )
10491: AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
10492: (
10493: SELECT 1
10494: FROM bom_components_b bcb1
10495: WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
10496: AND bcb1.change_notice = context_eco
10497: AND bcb1.acd_type = 3
10498: AND bcb1.effectivity_date <= x_effectivity_date
10657: ELSIF l_to_eff_ctrl = 4
10658: THEN
10659: SELECT COUNT (*)
10660: INTO dummy
10661: FROM bom_components_b bcb
10662: WHERE bcb.bill_sequence_id = to_sequence_id
10663: AND EXISTS (
10664: SELECT NULL
10665: FROM bom_components_b bcb2
10661: FROM bom_components_b bcb
10662: WHERE bcb.bill_sequence_id = to_sequence_id
10663: AND EXISTS (
10664: SELECT NULL
10665: FROM bom_components_b bcb2
10666: WHERE bcb2.bill_sequence_id = to_sequence_id
10667: AND bcb2.ROWID <> bcb.ROWID
10668: AND bcb2.operation_seq_num = bcb.operation_seq_num
10669: AND bcb2.component_item_id = bcb.component_item_id
10802: fnd_global.conc_request_id,
10803: NULL,
10804: fnd_global.conc_program_id,
10805: sysdate
10806: FROM bom_components_b bcb,
10807: mtl_system_items_b msib1,
10808: mtl_system_items_b msib2
10809: WHERE bcb.bill_sequence_id = to_sequence_id
10810: AND (msib1.bom_item_type = planning
10856: fnd_global.conc_request_id,
10857: NULL,
10858: fnd_global.conc_program_id,
10859: sysdate
10860: FROM bom_components_b bcb,
10861: mtl_system_items_b msib1,
10862: mtl_system_items_b msib2
10863: WHERE bcb.bill_sequence_id = to_sequence_id
10864: AND (msib1.bom_item_type IN (model, option_class)
10911: fnd_global.conc_request_id,
10912: NULL,
10913: fnd_global.conc_program_id,
10914: sysdate
10915: FROM bom_components_b bcb,
10916: mtl_system_items_b msib1, -- Comp
10917: mtl_system_items_b msib2 -- Structure
10918: WHERE bcb.bill_sequence_id = to_sequence_id
10919: AND (msib1.replenish_to_order_flag = 'Y'
10966: fnd_global.conc_request_id,
10967: NULL,
10968: fnd_global.conc_program_id,
10969: sysdate
10970: FROM bom_components_b bcb,
10971: mtl_system_items_b msib1, -- Comp
10972: mtl_system_items_b msib2 -- Structure
10973: WHERE bcb.bill_sequence_id = to_sequence_id
10974: AND (msib1.replenish_to_order_flag = 'Y'
11022: fnd_global.conc_request_id,
11023: NULL,
11024: fnd_global.conc_program_id,
11025: sysdate
11026: FROM bom_components_b bcb,
11027: mtl_system_items_b msib1, -- Comp
11028: mtl_system_items_b msib2 -- Structure
11029: WHERE bcb.bill_sequence_id = to_sequence_id
11030: AND (msib1.pick_components_flag = 'Y'
11074: sql_stmt_num := 43;
11075: --Turn off trigger BOMTBICX
11076: bom_globals.g_skip_bomtbicx := 'Y';
11077:
11078: UPDATE bom_components_b bic
11079: SET bic.wip_supply_type = phantom
11080: WHERE bic.bill_sequence_id = to_sequence_id
11081: AND EXISTS (
11082: SELECT NULL
11092: bom_globals.g_skip_bomtbicx := 'N';
11093: sql_stmt_num := 44;
11094: bom_globals.g_skip_bomtbicx := 'Y';
11095:
11096: UPDATE bom_components_b bic
11097: SET bic.optional = 1
11098: WHERE bic.bill_sequence_id = to_sequence_id
11099: AND EXISTS (
11100: SELECT NULL
11177: brd.attribute13,
11178: brd.attribute14,
11179: brd.attribute15
11180: FROM bom_reference_designators brd,
11181: bom_components_b bic,
11182: bom_copy_explosions_v bev
11183: WHERE bic.bill_sequence_id = to_sequence_id
11184: AND NVL (bic.eco_for_production, 2) = 2
11185: AND brd.component_sequence_id = bic.created_by
11267: bsc.attribute14,
11268: bsc.attribute15,
11269: bsc.enforce_int_requirements
11270: FROM bom_substitute_components bsc,
11271: bom_components_b bic,
11272: mtl_system_items msi,
11273: MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
11274: bom_copy_explosions_v bev
11275: WHERE bic.bill_sequence_id = to_sequence_id
11417: NULL,
11418: fnd_global.conc_program_id,
11419: sysdate
11420: FROM bom_component_operations bco,
11421: bom_components_b bic,
11422: bom_operation_sequences bos,
11423: bom_copy_explosions_v bev
11424: WHERE bic.bill_sequence_id = to_sequence_id
11425: AND NVL (bic.eco_for_production, 2) = 2