[Home] [Help]
705: AND bic.bill_sequence_id = to_sequence_id
706: AND NOT EXISTS (
707: SELECT NULL
708: FROM mtl_system_items cmsi,
709: mtl_system_items bmsi
710: WHERE cmsi.organization_id = bom.organization_id
711: AND cmsi.inventory_item_id =
712: bic.component_item_id
713: AND bmsi.organization_id = bom.organization_id
798: CURSOR l_org_item_csr (p_item_id IN NUMBER, p_org_id IN NUMBER)
799: IS
800: SELECT concatenated_segments,
801: primary_uom_code
802: FROM mtl_system_items_b_kfv
803: WHERE inventory_item_id = p_item_id
804: AND organization_id = p_org_id;
805:
806: CURSOR getcurrentminorrevcode (
1744: SELECT 1
1745: FROM fnd_lookup_values_vl flv,
1746: ego_criteria_templates_v ectv,
1747: ego_criteria_v ecv,
1748: mtl_system_items_b msibs -- to assembly item
1749: WHERE ecv.customization_application_id = 702
1750: AND ecv.region_application_id = 702
1751: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
1752: AND ecv.customization_code = ectv.customization_code
1769: (
1770: SELECT 1
1771: FROM fnd_lookup_values_vl flv,
1772: ego_criteria_templates_v ectv,
1773: mtl_system_items_b msibs -- to assembly item
1774: WHERE flv.lookup_type = 'ITEM_TYPE'
1775: AND flv.enabled_flag = 'Y'
1776: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
1777: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
2144: SELECT 1
2145: FROM fnd_lookup_values_vl flv,
2146: ego_criteria_templates_v ectv,
2147: ego_criteria_v ecv,
2148: mtl_system_items_b msibs -- to assembly item
2149: WHERE ecv.customization_application_id = 702
2150: AND ecv.region_application_id = 702
2151: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
2152: AND ecv.customization_code = ectv.customization_code
2169: (
2170: SELECT 1
2171: FROM fnd_lookup_values_vl flv,
2172: ego_criteria_templates_v ectv,
2173: mtl_system_items_b msibs -- to assembly item
2174: WHERE flv.lookup_type = 'ITEM_TYPE'
2175: AND flv.enabled_flag = 'Y'
2176: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
2177: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
2621: SELECT 1
2622: FROM fnd_lookup_values_vl flv,
2623: ego_criteria_templates_v ectv,
2624: ego_criteria_v ecv,
2625: mtl_system_items_b msibs -- to assembly item
2626: WHERE ecv.customization_application_id = 702
2627: AND ecv.region_application_id = 702
2628: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
2629: AND ecv.customization_code = ectv.customization_code
2646: (
2647: SELECT 1
2648: FROM fnd_lookup_values_vl flv,
2649: ego_criteria_templates_v ectv,
2650: mtl_system_items_b msibs -- to assembly item
2651: WHERE flv.lookup_type = 'ITEM_TYPE'
2652: AND flv.enabled_flag = 'Y'
2653: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
2654: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
3062: SELECT 1
3063: FROM fnd_lookup_values_vl flv,
3064: ego_criteria_templates_v ectv,
3065: ego_criteria_v ecv,
3066: mtl_system_items_b msibs -- to assembly item
3067: WHERE ecv.customization_application_id = 702
3068: AND ecv.region_application_id = 702
3069: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
3070: AND ecv.customization_code = ectv.customization_code
3087: (
3088: SELECT 1
3089: FROM fnd_lookup_values_vl flv,
3090: ego_criteria_templates_v ectv,
3091: mtl_system_items_b msibs -- to assembly item
3092: WHERE flv.lookup_type = 'ITEM_TYPE'
3093: AND flv.enabled_flag = 'Y'
3094: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
3095: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
3513: SELECT 1
3514: FROM fnd_lookup_values_vl flv,
3515: ego_criteria_templates_v ectv,
3516: ego_criteria_v ecv,
3517: mtl_system_items_b msibs -- to assembly item
3518: WHERE ecv.customization_application_id = 702
3519: AND ecv.region_application_id = 702
3520: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
3521: AND ecv.customization_code = ectv.customization_code
3538: (
3539: SELECT 1
3540: FROM fnd_lookup_values_vl flv,
3541: ego_criteria_templates_v ectv,
3542: mtl_system_items_b msibs -- to assembly item
3543: WHERE flv.lookup_type = 'ITEM_TYPE'
3544: AND flv.enabled_flag = 'Y'
3545: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
3546: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
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
3759: AND bcb.component_item_id <> to_item_id
3838: fnd_global.conc_request_id,
3839: NULL,
3840: fnd_global.conc_program_id,
3841: sysdate
3842: FROM mtl_system_items_b_kfv msbk1
3843: WHERE msbk1.inventory_item_id = from_item_id
3844: AND msbk1.organization_id = from_org_id;
3845: END IF;
3846:
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
3856: AND bcb.component_item_id <> to_item_id
3935: fnd_global.conc_request_id,
3936: NULL,
3937: fnd_global.conc_program_id,
3938: sysdate
3939: FROM mtl_system_items_b_kfv msbk1
3940: WHERE msbk1.inventory_item_id = from_item_id
3941: AND msbk1.organization_id = from_org_id;
3942: END IF;
3943: END IF;
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
3991: AND bcb.component_item_id <> to_item_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
4162: AND bcb.component_item_id = msbk1.inventory_item_id
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
4162: AND bcb.component_item_id = msbk1.inventory_item_id
4163: AND bcb.component_item_id <> to_item_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
4219: AND bcb.component_item_id <> to_item_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
4402: AND 'Y' <>
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
4467: AND 'Y' <>
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
5175: AND msib2.bom_item_type <> planning
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
5175: AND msib2.bom_item_type <> planning
5176: )
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)
5228: AND msib2.bom_item_type = STANDARD
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)
5228: AND msib2.bom_item_type = STANDARD
5229: AND msib2.base_item_id IS NULL
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'
5282: AND msib1.bom_item_type = option_class
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'
5282: AND msib1.bom_item_type = option_class
5283: AND msib2.pick_components_flag = 'Y'
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'
5336: AND msib1.bom_item_type = STANDARD
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'
5336: AND msib1.bom_item_type = STANDARD
5337: AND msib2.pick_components_flag = 'Y'
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'
5391: AND msib2.replenish_to_order_flag = 'Y'
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'
5391: AND msib2.replenish_to_order_flag = 'Y'
5392: )
5730: SELECT 1
5731: FROM fnd_lookup_values_vl flv,
5732: ego_criteria_templates_v ectv,
5733: ego_criteria_v ecv,
5734: mtl_system_items_b msibs -- to assembly item
5735: WHERE ecv.customization_application_id = 702
5736: AND ecv.region_application_id = 702
5737: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
5738: AND ecv.customization_code = ectv.customization_code
5755: (
5756: SELECT 1
5757: FROM fnd_lookup_values_vl flv,
5758: ego_criteria_templates_v ectv,
5759: mtl_system_items_b msibs -- to assembly item
5760: WHERE flv.lookup_type = 'ITEM_TYPE'
5761: AND flv.enabled_flag = 'Y'
5762: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
5763: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
6313: FOR component_rec IN c_component_cur (p_sort_order, p_explode_grp_id)
6314: LOOP
6315: SELECT concatenated_segments
6316: INTO l_component_name
6317: FROM mtl_system_items_b_kfv msbk
6318: WHERE msbk.inventory_item_id = component_rec.component_item_id
6319: AND msbk.organization_id = p_org_id;
6320:
6321: IF (l_component_path IS NULL)
6398:
6399: l_item_tbl item_table;
6400: l_set_process_id NUMBER := 0;
6401: x_item_table ego_item_pub.item_tbl_type;
6402: x_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
6403: x_organization_id mtl_system_items_b.organization_id%TYPE;
6404: x_return_status VARCHAR2 (1);
6405: x_msg_count NUMBER (10);
6406: x_msg_data VARCHAR2 (2000);
6399: l_item_tbl item_table;
6400: l_set_process_id NUMBER := 0;
6401: x_item_table ego_item_pub.item_tbl_type;
6402: x_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
6403: x_organization_id mtl_system_items_b.organization_id%TYPE;
6404: x_return_status VARCHAR2 (1);
6405: x_msg_count NUMBER (10);
6406: x_msg_data VARCHAR2 (2000);
6407: x_message_list error_handler.error_tbl_type;
6414: l_item_rec_out inv_item_grp.item_rec_type;
6415: l_error_tbl inv_item_grp.error_tbl_type;
6416: x_msg_list error_handler.error_tbl_type;
6417: l_item_org_assign_tbl system.EGO_ITEM_ORG_ASSIGN_TABLE := new system.EGO_ITEM_ORG_ASSIGN_TABLE();
6418: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6419: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6420: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6421: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6422: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6415: l_error_tbl inv_item_grp.error_tbl_type;
6416: x_msg_list error_handler.error_tbl_type;
6417: l_item_org_assign_tbl system.EGO_ITEM_ORG_ASSIGN_TABLE := new system.EGO_ITEM_ORG_ASSIGN_TABLE();
6418: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6419: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6420: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6421: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6422: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6423: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6416: x_msg_list error_handler.error_tbl_type;
6417: l_item_org_assign_tbl system.EGO_ITEM_ORG_ASSIGN_TABLE := new system.EGO_ITEM_ORG_ASSIGN_TABLE();
6418: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6419: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6420: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6421: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6422: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6423: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6424: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6417: l_item_org_assign_tbl system.EGO_ITEM_ORG_ASSIGN_TABLE := new system.EGO_ITEM_ORG_ASSIGN_TABLE();
6418: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6419: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6420: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6421: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6422: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6423: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6424: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6425: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6418: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6419: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6420: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6421: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6422: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6423: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6424: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6425: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6426: l_not_exists NUMBER := 0;
6419: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6420: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6421: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6422: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6423: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6424: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6425: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6426: l_not_exists NUMBER := 0;
6427: l_msg_count NUMBER := 0;
6420: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6421: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6422: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6423: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6424: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6425: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6426: l_not_exists NUMBER := 0;
6427: l_msg_count NUMBER := 0;
6428: l_item_count NUMBER := 1;
6421: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6422: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6423: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6424: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6425: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6426: l_not_exists NUMBER := 0;
6427: l_msg_count NUMBER := 0;
6428: l_item_count NUMBER := 1;
6429: l_index NUMBER := 0;
6444: msibk.dual_uom_deviation_low,
6445: msibk.secondary_default_ind,
6446: msibk.tracking_quantity_ind,
6447: msibk.ont_pricing_qty_source
6448: FROM mtl_system_items_b_kfv msibk,
6449: bom_copy_explosions_v bev
6450: WHERE msibk.inventory_item_id = bev.component_item_id
6451: AND msibk.organization_id = bev.organization_id
6452: AND (bev.implementation_date IS NOT NULL
6470: )
6471: */
6472: AND NOT EXISTS (
6473: SELECT 1
6474: FROM mtl_system_items_b msib
6475: WHERE msib.inventory_item_id = bev.component_item_id
6476: AND msib.organization_id = c_org_id)
6477: AND ( NOT EXISTS (
6478: -- Direct Component Action is exclude
6505: IS
6506: SELECT mmsib.primary_uom_code, 1 l_not_exists, concatenated_segments,
6507: mmsib.secondary_uom_code, mmsib.dual_uom_deviation_high, mmsib.dual_uom_deviation_low,
6508: mmsib.secondary_default_ind, mmsib.tracking_quantity_ind, mmsib.ont_pricing_qty_source
6509: FROM mtl_system_items_b_kfv mmsib
6510: WHERE mmsib.inventory_item_id = p_item_id
6511: AND mmsib.organization_id = p_master_org_id
6512: AND NOT EXISTS
6513: ( SELECT 1
6510: WHERE mmsib.inventory_item_id = p_item_id
6511: AND mmsib.organization_id = p_master_org_id
6512: AND NOT EXISTS
6513: ( SELECT 1
6514: FROM mtl_system_items_b msib
6515: WHERE msib.inventory_item_id = mmsib.inventory_item_id
6516: AND msib.organization_id = p_org_id
6517: );
6518: BEGIN
6806: CASE
6807: WHEN EXISTS
6808: (
6809: SELECT 1
6810: FROM mtl_system_items_b msib
6811: WHERE msib.inventory_item_id = p_item_id
6812: AND msib.organization_id = x_org_list_tbl(l_index)
6813: ) THEN
6814: 'Y'
6848:
6849: CURSOR c1 (cp_item_id IN NUMBER, cp_copy_request_id IN NUMBER)
6850: IS
6851: SELECT mp.organization_code
6852: FROM mtl_system_items_b msib,
6853: mtl_parameters mp,
6854: bom_copy_organization_list bcol
6855: WHERE msib.inventory_item_id = cp_item_id
6856: AND msib.organization_id = mp.organization_id
6975: WHERE bcol.copy_request_id = cp_copy_request_id
6976: AND bcol.organization_id = mp.organization_id
6977: AND NOT EXISTS (
6978: SELECT 1
6979: FROM mtl_system_items_b msib
6980: WHERE msib.inventory_item_id = cp_item_id
6981: AND msib.organization_id = bcol.organization_id);
6982:
6983: l_count NUMBER := 0;
7235: AND bic.bill_sequence_id = to_sequence_id
7236: AND NOT EXISTS (
7237: SELECT NULL
7238: FROM mtl_system_items cmsi,
7239: mtl_system_items bmsi
7240: WHERE cmsi.organization_id = bom.organization_id
7241: AND cmsi.inventory_item_id =
7242: bic.component_item_id
7243: AND bmsi.organization_id = bom.organization_id
7326: CURSOR l_org_item_csr (p_item_id IN NUMBER, p_org_id IN NUMBER)
7327: IS
7328: SELECT concatenated_segments,
7329: primary_uom_code
7330: FROM mtl_system_items_b_kfv
7331: WHERE inventory_item_id = p_item_id
7332: AND organization_id = p_org_id;
7333:
7334: CURSOR l_from_to_comps_csr (
7941: SELECT 1
7942: FROM fnd_lookup_values_vl flv,
7943: ego_criteria_templates_v ectv,
7944: ego_criteria_v ecv,
7945: mtl_system_items_b msibs -- to assembly item
7946: WHERE ecv.customization_application_id = 702
7947: AND ecv.region_application_id = 702
7948: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
7949: AND ecv.customization_code = ectv.customization_code
7966: (
7967: SELECT 1
7968: FROM fnd_lookup_values_vl flv,
7969: ego_criteria_templates_v ectv,
7970: mtl_system_items_b msibs -- to assembly item
7971: WHERE flv.lookup_type = 'ITEM_TYPE'
7972: AND flv.enabled_flag = 'Y'
7973: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
7974: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
8309: SELECT 1
8310: FROM fnd_lookup_values_vl flv,
8311: ego_criteria_templates_v ectv,
8312: ego_criteria_v ecv,
8313: mtl_system_items_b msibs -- to assembly item
8314: WHERE ecv.customization_application_id = 702
8315: AND ecv.region_application_id = 702
8316: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
8317: AND ecv.customization_code = ectv.customization_code
8334: (
8335: SELECT 1
8336: FROM fnd_lookup_values_vl flv,
8337: ego_criteria_templates_v ectv,
8338: mtl_system_items_b msibs -- to assembly item
8339: WHERE flv.lookup_type = 'ITEM_TYPE'
8340: AND flv.enabled_flag = 'Y'
8341: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
8342: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
8720: SELECT 1
8721: FROM fnd_lookup_values_vl flv,
8722: ego_criteria_templates_v ectv,
8723: ego_criteria_v ecv,
8724: mtl_system_items_b msibs -- to assembly item
8725: WHERE ecv.customization_application_id = 702
8726: AND ecv.region_application_id = 702
8727: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
8728: AND ecv.customization_code = ectv.customization_code
8745: (
8746: SELECT 1
8747: FROM fnd_lookup_values_vl flv,
8748: ego_criteria_templates_v ectv,
8749: mtl_system_items_b msibs -- to assembly item
8750: WHERE flv.lookup_type = 'ITEM_TYPE'
8751: AND flv.enabled_flag = 'Y'
8752: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
8753: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
9120: SELECT 1
9121: FROM fnd_lookup_values_vl flv,
9122: ego_criteria_templates_v ectv,
9123: ego_criteria_v ecv,
9124: mtl_system_items_b msibs -- to assembly item
9125: WHERE ecv.customization_application_id = 702
9126: AND ecv.region_application_id = 702
9127: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
9128: AND ecv.customization_code = ectv.customization_code
9145: (
9146: SELECT 1
9147: FROM fnd_lookup_values_vl flv,
9148: ego_criteria_templates_v ectv,
9149: mtl_system_items_b msibs -- to assembly item
9150: WHERE flv.lookup_type = 'ITEM_TYPE'
9151: AND flv.enabled_flag = 'Y'
9152: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
9153: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
9507: SELECT 1
9508: FROM fnd_lookup_values_vl flv,
9509: ego_criteria_templates_v ectv,
9510: ego_criteria_v ecv,
9511: mtl_system_items_b msibs -- to assembly item
9512: WHERE ecv.customization_application_id = 702
9513: AND ecv.region_application_id = 702
9514: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
9515: AND ecv.customization_code = ectv.customization_code
9532: (
9533: SELECT 1
9534: FROM fnd_lookup_values_vl flv,
9535: ego_criteria_templates_v ectv,
9536: mtl_system_items_b msibs -- to assembly item
9537: WHERE flv.lookup_type = 'ITEM_TYPE'
9538: AND flv.enabled_flag = 'Y'
9539: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
9540: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
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
9641: AND bcb.component_item_id <> to_item_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
9742: AND bcb.component_sequence_id = bev.component_sequence_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
9770: AND bcb.component_item_id <> to_item_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
9871: AND bcb.component_sequence_id = bev.component_sequence_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
9936: AND bcb.component_item_id <> to_item_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
10137: AND bcb.component_item_id <> to_item_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
10340: AND 'Y' <>
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
10405: AND 'Y' <>
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
10811: AND msib2.bom_item_type <> planning
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
10811: AND msib2.bom_item_type <> planning
10812: )
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)
10865: AND msib2.bom_item_type = STANDARD
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)
10865: AND msib2.bom_item_type = STANDARD
10866: AND msib2.base_item_id IS NULL
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'
10920: AND msib1.bom_item_type = option_class
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'
10920: AND msib1.bom_item_type = option_class
10921: AND msib2.pick_components_flag = 'Y'
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'
10975: AND msib1.bom_item_type = STANDARD
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'
10975: AND msib1.bom_item_type = STANDARD
10976: AND msib2.pick_components_flag = 'Y'
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'
11031: AND msib2.replenish_to_order_flag = 'Y'
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'
11031: AND msib2.replenish_to_order_flag = 'Y'
11032: )
11293: SELECT 1
11294: FROM fnd_lookup_values_vl flv,
11295: ego_criteria_templates_v ectv,
11296: ego_criteria_v ecv,
11297: mtl_system_items_b msibs -- to assembly item
11298: WHERE ecv.customization_application_id = 702
11299: AND ecv.region_application_id = 702
11300: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
11301: AND ecv.customization_code = ectv.customization_code
11318: (
11319: SELECT 1
11320: FROM fnd_lookup_values_vl flv,
11321: ego_criteria_templates_v ectv,
11322: mtl_system_items_b msibs -- to assembly item
11323: WHERE flv.lookup_type = 'ITEM_TYPE'
11324: AND flv.enabled_flag = 'Y'
11325: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
11326: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)