[Home] [Help]
709: AND bic.bill_sequence_id = to_sequence_id
710: AND NOT EXISTS (
711: SELECT NULL
712: FROM mtl_system_items cmsi,
713: mtl_system_items bmsi
714: WHERE cmsi.organization_id = bom.organization_id
715: AND cmsi.inventory_item_id =
716: bic.component_item_id
717: AND bmsi.organization_id = bom.organization_id
802: CURSOR l_org_item_csr (p_item_id IN NUMBER, p_org_id IN NUMBER)
803: IS
804: SELECT concatenated_segments,
805: primary_uom_code
806: FROM mtl_system_items_b_kfv
807: WHERE inventory_item_id = p_item_id
808: AND organization_id = p_org_id;
809:
810: CURSOR getcurrentminorrevcode (
1797: SELECT 1
1798: FROM fnd_lookup_values_vl flv,
1799: ego_criteria_templates_v ectv,
1800: ego_criteria_v ecv,
1801: mtl_system_items_b msibs -- to assembly item
1802: WHERE ecv.customization_application_id = 702
1803: AND ecv.region_application_id = 702
1804: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
1805: AND ecv.customization_code = ectv.customization_code
1822: (
1823: SELECT 1
1824: FROM fnd_lookup_values_vl flv,
1825: ego_criteria_templates_v ectv,
1826: mtl_system_items_b msibs -- to assembly item
1827: WHERE flv.lookup_type = 'ITEM_TYPE'
1828: AND flv.enabled_flag = 'Y'
1829: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
1830: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
2197: SELECT 1
2198: FROM fnd_lookup_values_vl flv,
2199: ego_criteria_templates_v ectv,
2200: ego_criteria_v ecv,
2201: mtl_system_items_b msibs -- to assembly item
2202: WHERE ecv.customization_application_id = 702
2203: AND ecv.region_application_id = 702
2204: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
2205: AND ecv.customization_code = ectv.customization_code
2222: (
2223: SELECT 1
2224: FROM fnd_lookup_values_vl flv,
2225: ego_criteria_templates_v ectv,
2226: mtl_system_items_b msibs -- to assembly item
2227: WHERE flv.lookup_type = 'ITEM_TYPE'
2228: AND flv.enabled_flag = 'Y'
2229: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
2230: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
2674: SELECT 1
2675: FROM fnd_lookup_values_vl flv,
2676: ego_criteria_templates_v ectv,
2677: ego_criteria_v ecv,
2678: mtl_system_items_b msibs -- to assembly item
2679: WHERE ecv.customization_application_id = 702
2680: AND ecv.region_application_id = 702
2681: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
2682: AND ecv.customization_code = ectv.customization_code
2699: (
2700: SELECT 1
2701: FROM fnd_lookup_values_vl flv,
2702: ego_criteria_templates_v ectv,
2703: mtl_system_items_b msibs -- to assembly item
2704: WHERE flv.lookup_type = 'ITEM_TYPE'
2705: AND flv.enabled_flag = 'Y'
2706: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
2707: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
3116: SELECT 1
3117: FROM fnd_lookup_values_vl flv,
3118: ego_criteria_templates_v ectv,
3119: ego_criteria_v ecv,
3120: mtl_system_items_b msibs -- to assembly item
3121: WHERE ecv.customization_application_id = 702
3122: AND ecv.region_application_id = 702
3123: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
3124: AND ecv.customization_code = ectv.customization_code
3141: (
3142: SELECT 1
3143: FROM fnd_lookup_values_vl flv,
3144: ego_criteria_templates_v ectv,
3145: mtl_system_items_b msibs -- to assembly item
3146: WHERE flv.lookup_type = 'ITEM_TYPE'
3147: AND flv.enabled_flag = 'Y'
3148: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
3149: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
3567: SELECT 1
3568: FROM fnd_lookup_values_vl flv,
3569: ego_criteria_templates_v ectv,
3570: ego_criteria_v ecv,
3571: mtl_system_items_b msibs -- to assembly item
3572: WHERE ecv.customization_application_id = 702
3573: AND ecv.region_application_id = 702
3574: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
3575: AND ecv.customization_code = ectv.customization_code
3592: (
3593: SELECT 1
3594: FROM fnd_lookup_values_vl flv,
3595: ego_criteria_templates_v ectv,
3596: mtl_system_items_b msibs -- to assembly item
3597: WHERE flv.lookup_type = 'ITEM_TYPE'
3598: AND flv.enabled_flag = 'Y'
3599: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
3600: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
3813:
3814: SELECT COUNT (*)
3815: INTO l_no_access_comp_cnt
3816: FROM bom_components_b bcb,
3817: mtl_system_items_b_kfv msbk1,
3818: bom_copy_explosions_v bev
3819: WHERE bcb.bill_sequence_id = x_from_sequence_id
3820: AND bcb.component_item_id = msbk1.inventory_item_id
3821: AND bcb.component_item_id <> to_item_id
3900: fnd_global.conc_request_id,
3901: NULL,
3902: fnd_global.conc_program_id,
3903: sysdate
3904: FROM mtl_system_items_b_kfv msbk1
3905: WHERE msbk1.inventory_item_id = from_item_id
3906: AND msbk1.organization_id = from_org_id;
3907: END IF;
3908:
3910: THEN
3911: SELECT COUNT (*)
3912: INTO l_no_access_comp_cnt
3913: FROM bom_components_b bcb,
3914: mtl_system_items_b_kfv msbk1,
3915: bom_copy_explosions_v bev
3916: WHERE bcb.bill_sequence_id = x_from_sequence_id
3917: AND bcb.component_item_id = msbk1.inventory_item_id
3918: AND bcb.component_item_id <> to_item_id
3997: fnd_global.conc_request_id,
3998: NULL,
3999: fnd_global.conc_program_id,
4000: sysdate
4001: FROM mtl_system_items_b_kfv msbk1
4002: WHERE msbk1.inventory_item_id = from_item_id
4003: AND msbk1.organization_id = from_org_id;
4004: END IF;
4005: END IF;
4045: NULL,
4046: fnd_global.conc_program_id,
4047: sysdate
4048: FROM bom_components_b bcb,
4049: mtl_system_items_b msib, -- component
4050: bom_copy_explosions_v bev
4051: WHERE bcb.bill_sequence_id = x_from_sequence_id
4052: AND bcb.component_item_id = msib.inventory_item_id
4053: AND bcb.component_item_id <> to_item_id
4216: SYSDATE,
4217: user_id,
4218: 'E'
4219: FROM bom_components_b bcb,
4220: mtl_system_items_b_kfv msbk1,
4221: mtl_system_items_b_kfv msbk2,
4222: bom_copy_explosions_v bev
4223: WHERE bcb.bill_sequence_id = x_from_sequence_id
4224: AND bcb.component_item_id = msbk1.inventory_item_id
4217: user_id,
4218: 'E'
4219: FROM bom_components_b bcb,
4220: mtl_system_items_b_kfv msbk1,
4221: mtl_system_items_b_kfv msbk2,
4222: bom_copy_explosions_v bev
4223: WHERE bcb.bill_sequence_id = x_from_sequence_id
4224: AND bcb.component_item_id = msbk1.inventory_item_id
4225: AND bcb.component_item_id <> to_item_id
4273: NULL,
4274: fnd_global.conc_program_id,
4275: sysdate
4276: FROM bom_components_b bcb,
4277: mtl_system_items_b msib,
4278: bom_copy_explosions_v bev
4279: WHERE bcb.bill_sequence_id = x_from_sequence_id
4280: AND bcb.component_item_id = msib.inventory_item_id
4281: AND bcb.component_item_id <> to_item_id
4456: NULL,
4457: fnd_global.conc_program_id,
4458: sysdate
4459: FROM bom_components_b bcb,
4460: mtl_system_items_b msib
4461: WHERE bcb.bill_sequence_id = to_sequence_id
4462: AND bcb.component_item_id = msib.inventory_item_id
4463: AND msib.organization_id = to_org_id
4464: AND 'Y' <>
4521: NULL,
4522: fnd_global.conc_program_id,
4523: sysdate
4524: FROM bom_components_b bcb,
4525: mtl_system_items_b msib
4526: WHERE bcb.bill_sequence_id = to_sequence_id
4527: AND bcb.component_item_id = msib.inventory_item_id
4528: AND msib.organization_id = to_org_id
4529: AND 'Y' <>
5229: NULL,
5230: fnd_global.conc_program_id,
5231: sysdate
5232: FROM bom_components_b bcb,
5233: mtl_system_items_b msib1,
5234: mtl_system_items_b msib2
5235: WHERE bcb.bill_sequence_id = to_sequence_id
5236: AND (msib1.bom_item_type = planning
5237: AND msib2.bom_item_type <> planning
5230: fnd_global.conc_program_id,
5231: sysdate
5232: FROM bom_components_b bcb,
5233: mtl_system_items_b msib1,
5234: mtl_system_items_b msib2
5235: WHERE bcb.bill_sequence_id = to_sequence_id
5236: AND (msib1.bom_item_type = planning
5237: AND msib2.bom_item_type <> planning
5238: )
5282: NULL,
5283: fnd_global.conc_program_id,
5284: sysdate
5285: FROM bom_components_b bcb,
5286: mtl_system_items_b msib1,
5287: mtl_system_items_b msib2
5288: WHERE bcb.bill_sequence_id = to_sequence_id
5289: AND (msib1.bom_item_type IN (model, option_class)
5290: AND msib2.bom_item_type = STANDARD
5283: fnd_global.conc_program_id,
5284: sysdate
5285: FROM bom_components_b bcb,
5286: mtl_system_items_b msib1,
5287: mtl_system_items_b msib2
5288: WHERE bcb.bill_sequence_id = to_sequence_id
5289: AND (msib1.bom_item_type IN (model, option_class)
5290: AND msib2.bom_item_type = STANDARD
5291: AND msib2.base_item_id IS NULL
5339: NULL,
5340: fnd_global.conc_program_id,
5341: sysdate
5342: FROM bom_components_b bcb,
5343: mtl_system_items_b msib1, -- Comp
5344: mtl_system_items_b msib2 -- Structure
5345: WHERE bcb.bill_sequence_id = to_sequence_id
5346: AND (msib1.replenish_to_order_flag = 'Y'
5347: AND msib1.bom_item_type = option_class
5340: fnd_global.conc_program_id,
5341: sysdate
5342: FROM bom_components_b bcb,
5343: mtl_system_items_b msib1, -- Comp
5344: mtl_system_items_b msib2 -- Structure
5345: WHERE bcb.bill_sequence_id = to_sequence_id
5346: AND (msib1.replenish_to_order_flag = 'Y'
5347: AND msib1.bom_item_type = option_class
5348: AND msib2.pick_components_flag = 'Y'
5393: NULL,
5394: fnd_global.conc_program_id,
5395: sysdate
5396: FROM bom_components_b bcb,
5397: mtl_system_items_b msib1, -- Comp
5398: mtl_system_items_b msib2 -- Structure
5399: WHERE bcb.bill_sequence_id = to_sequence_id
5400: AND (msib1.replenish_to_order_flag = 'Y'
5401: AND msib1.bom_item_type = STANDARD
5394: fnd_global.conc_program_id,
5395: sysdate
5396: FROM bom_components_b bcb,
5397: mtl_system_items_b msib1, -- Comp
5398: mtl_system_items_b msib2 -- Structure
5399: WHERE bcb.bill_sequence_id = to_sequence_id
5400: AND (msib1.replenish_to_order_flag = 'Y'
5401: AND msib1.bom_item_type = STANDARD
5402: AND msib2.pick_components_flag = 'Y'
5450: NULL,
5451: fnd_global.conc_program_id,
5452: sysdate
5453: FROM bom_components_b bcb,
5454: mtl_system_items_b msib1, -- Comp
5455: mtl_system_items_b msib2 -- Structure
5456: WHERE bcb.bill_sequence_id = to_sequence_id
5457: AND (msib1.pick_components_flag = 'Y'
5458: AND msib2.replenish_to_order_flag = 'Y'
5451: fnd_global.conc_program_id,
5452: sysdate
5453: FROM bom_components_b bcb,
5454: mtl_system_items_b msib1, -- Comp
5455: mtl_system_items_b msib2 -- Structure
5456: WHERE bcb.bill_sequence_id = to_sequence_id
5457: AND (msib1.pick_components_flag = 'Y'
5458: AND msib2.replenish_to_order_flag = 'Y'
5459: )
5822: SELECT 1
5823: FROM fnd_lookup_values_vl flv,
5824: ego_criteria_templates_v ectv,
5825: ego_criteria_v ecv,
5826: mtl_system_items_b msibs -- to assembly item
5827: WHERE ecv.customization_application_id = 702
5828: AND ecv.region_application_id = 702
5829: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
5830: AND ecv.customization_code = ectv.customization_code
5847: (
5848: SELECT 1
5849: FROM fnd_lookup_values_vl flv,
5850: ego_criteria_templates_v ectv,
5851: mtl_system_items_b msibs -- to assembly item
5852: WHERE flv.lookup_type = 'ITEM_TYPE'
5853: AND flv.enabled_flag = 'Y'
5854: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
5855: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
6409: FOR component_rec IN c_component_cur (p_sort_order, p_explode_grp_id)
6410: LOOP
6411: SELECT concatenated_segments
6412: INTO l_component_name
6413: FROM mtl_system_items_b_kfv msbk
6414: WHERE msbk.inventory_item_id = component_rec.component_item_id
6415: AND msbk.organization_id = p_org_id;
6416:
6417: IF (l_component_path IS NULL)
6494:
6495: l_item_tbl item_table;
6496: l_set_process_id NUMBER := 0;
6497: x_item_table ego_item_pub.item_tbl_type;
6498: x_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
6499: x_organization_id mtl_system_items_b.organization_id%TYPE;
6500: x_return_status VARCHAR2 (1);
6501: x_msg_count NUMBER (10);
6502: x_msg_data VARCHAR2 (2000);
6495: l_item_tbl item_table;
6496: l_set_process_id NUMBER := 0;
6497: x_item_table ego_item_pub.item_tbl_type;
6498: x_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
6499: x_organization_id mtl_system_items_b.organization_id%TYPE;
6500: x_return_status VARCHAR2 (1);
6501: x_msg_count NUMBER (10);
6502: x_msg_data VARCHAR2 (2000);
6503: x_message_list error_handler.error_tbl_type;
6510: l_item_rec_out inv_item_grp.item_rec_type;
6511: l_error_tbl inv_item_grp.error_tbl_type;
6512: x_msg_list error_handler.error_tbl_type;
6513: l_item_org_assign_tbl system.EGO_ITEM_ORG_ASSIGN_TABLE := new system.EGO_ITEM_ORG_ASSIGN_TABLE();
6514: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6515: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6516: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6517: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6518: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6511: l_error_tbl inv_item_grp.error_tbl_type;
6512: x_msg_list error_handler.error_tbl_type;
6513: l_item_org_assign_tbl system.EGO_ITEM_ORG_ASSIGN_TABLE := new system.EGO_ITEM_ORG_ASSIGN_TABLE();
6514: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6515: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6516: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6517: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6518: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6519: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6512: x_msg_list error_handler.error_tbl_type;
6513: l_item_org_assign_tbl system.EGO_ITEM_ORG_ASSIGN_TABLE := new system.EGO_ITEM_ORG_ASSIGN_TABLE();
6514: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6515: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6516: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6517: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6518: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6519: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6520: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6513: l_item_org_assign_tbl system.EGO_ITEM_ORG_ASSIGN_TABLE := new system.EGO_ITEM_ORG_ASSIGN_TABLE();
6514: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6515: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6516: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6517: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6518: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6519: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6520: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6521: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6514: l_trgt_item_uom_code mtl_system_items_b.primary_uom_code%TYPE;
6515: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6516: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6517: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6518: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6519: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6520: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6521: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6522: l_not_exists NUMBER := 0;
6515: l_trgt_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
6516: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6517: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6518: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6519: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6520: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6521: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6522: l_not_exists NUMBER := 0;
6523: l_msg_count NUMBER := 0;
6516: l_secondary_uom_code mtl_system_items_b_kfv.secondary_uom_code%TYPE;
6517: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6518: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6519: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6520: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6521: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6522: l_not_exists NUMBER := 0;
6523: l_msg_count NUMBER := 0;
6524: l_item_count NUMBER := 1;
6517: l_dual_uom_deviation_high mtl_system_items_b_kfv.dual_uom_deviation_high%TYPE;
6518: l_dual_uom_deviation_low mtl_system_items_b_kfv.dual_uom_deviation_low%TYPE;
6519: l_secondary_default_ind mtl_system_items_b_kfv.secondary_default_ind%TYPE;
6520: l_tracking_quantity_ind mtl_system_items_b_kfv.tracking_quantity_ind%TYPE;
6521: l_ont_pricing_qty_source mtl_system_items_b_kfv.ont_pricing_qty_source%TYPE;
6522: l_not_exists NUMBER := 0;
6523: l_msg_count NUMBER := 0;
6524: l_item_count NUMBER := 1;
6525: l_index NUMBER := 0;
6540: msibk.dual_uom_deviation_low,
6541: msibk.secondary_default_ind,
6542: msibk.tracking_quantity_ind,
6543: msibk.ont_pricing_qty_source
6544: FROM mtl_system_items_b_kfv msibk,
6545: bom_copy_explosions_v bev
6546: WHERE msibk.inventory_item_id = bev.component_item_id
6547: AND msibk.organization_id = bev.organization_id
6548: AND (bev.implementation_date IS NOT NULL
6566: )
6567: */
6568: AND NOT EXISTS (
6569: SELECT 1
6570: FROM mtl_system_items_b msib
6571: WHERE msib.inventory_item_id = bev.component_item_id
6572: AND msib.organization_id = c_org_id)
6573: AND ( NOT EXISTS (
6574: -- Direct Component Action is exclude
6601: IS
6602: SELECT mmsib.primary_uom_code, 1 l_not_exists, concatenated_segments,
6603: mmsib.secondary_uom_code, mmsib.dual_uom_deviation_high, mmsib.dual_uom_deviation_low,
6604: mmsib.secondary_default_ind, mmsib.tracking_quantity_ind, mmsib.ont_pricing_qty_source
6605: FROM mtl_system_items_b_kfv mmsib
6606: WHERE mmsib.inventory_item_id = p_item_id
6607: AND mmsib.organization_id = p_master_org_id
6608: AND NOT EXISTS
6609: ( SELECT 1
6606: WHERE mmsib.inventory_item_id = p_item_id
6607: AND mmsib.organization_id = p_master_org_id
6608: AND NOT EXISTS
6609: ( SELECT 1
6610: FROM mtl_system_items_b msib
6611: WHERE msib.inventory_item_id = mmsib.inventory_item_id
6612: AND msib.organization_id = p_org_id
6613: );
6614: BEGIN
6902: CASE
6903: WHEN EXISTS
6904: (
6905: SELECT 1
6906: FROM mtl_system_items_b msib
6907: WHERE msib.inventory_item_id = p_item_id
6908: AND msib.organization_id = x_org_list_tbl(l_index)
6909: ) THEN
6910: 'Y'
6944:
6945: CURSOR c1 (cp_item_id IN NUMBER, cp_copy_request_id IN NUMBER)
6946: IS
6947: SELECT mp.organization_code
6948: FROM mtl_system_items_b msib,
6949: mtl_parameters mp,
6950: bom_copy_organization_list bcol
6951: WHERE msib.inventory_item_id = cp_item_id
6952: AND msib.organization_id = mp.organization_id
7071: WHERE bcol.copy_request_id = cp_copy_request_id
7072: AND bcol.organization_id = mp.organization_id
7073: AND NOT EXISTS (
7074: SELECT 1
7075: FROM mtl_system_items_b msib
7076: WHERE msib.inventory_item_id = cp_item_id
7077: AND msib.organization_id = bcol.organization_id);
7078:
7079: l_count NUMBER := 0;
7335: AND bic.bill_sequence_id = to_sequence_id
7336: AND NOT EXISTS (
7337: SELECT NULL
7338: FROM mtl_system_items cmsi,
7339: mtl_system_items bmsi
7340: WHERE cmsi.organization_id = bom.organization_id
7341: AND cmsi.inventory_item_id =
7342: bic.component_item_id
7343: AND bmsi.organization_id = bom.organization_id
7426: CURSOR l_org_item_csr (p_item_id IN NUMBER, p_org_id IN NUMBER)
7427: IS
7428: SELECT concatenated_segments,
7429: primary_uom_code
7430: FROM mtl_system_items_b_kfv
7431: WHERE inventory_item_id = p_item_id
7432: AND organization_id = p_org_id;
7433:
7434: CURSOR l_from_to_comps_csr (
8082: SELECT 1
8083: FROM fnd_lookup_values_vl flv,
8084: ego_criteria_templates_v ectv,
8085: ego_criteria_v ecv,
8086: mtl_system_items_b msibs -- to assembly item
8087: WHERE ecv.customization_application_id = 702
8088: AND ecv.region_application_id = 702
8089: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
8090: AND ecv.customization_code = ectv.customization_code
8107: (
8108: SELECT 1
8109: FROM fnd_lookup_values_vl flv,
8110: ego_criteria_templates_v ectv,
8111: mtl_system_items_b msibs -- to assembly item
8112: WHERE flv.lookup_type = 'ITEM_TYPE'
8113: AND flv.enabled_flag = 'Y'
8114: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
8115: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
8451: SELECT 1
8452: FROM fnd_lookup_values_vl flv,
8453: ego_criteria_templates_v ectv,
8454: ego_criteria_v ecv,
8455: mtl_system_items_b msibs -- to assembly item
8456: WHERE ecv.customization_application_id = 702
8457: AND ecv.region_application_id = 702
8458: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
8459: AND ecv.customization_code = ectv.customization_code
8476: (
8477: SELECT 1
8478: FROM fnd_lookup_values_vl flv,
8479: ego_criteria_templates_v ectv,
8480: mtl_system_items_b msibs -- to assembly item
8481: WHERE flv.lookup_type = 'ITEM_TYPE'
8482: AND flv.enabled_flag = 'Y'
8483: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
8484: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
8863: SELECT 1
8864: FROM fnd_lookup_values_vl flv,
8865: ego_criteria_templates_v ectv,
8866: ego_criteria_v ecv,
8867: mtl_system_items_b msibs -- to assembly item
8868: WHERE ecv.customization_application_id = 702
8869: AND ecv.region_application_id = 702
8870: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
8871: AND ecv.customization_code = ectv.customization_code
8888: (
8889: SELECT 1
8890: FROM fnd_lookup_values_vl flv,
8891: ego_criteria_templates_v ectv,
8892: mtl_system_items_b msibs -- to assembly item
8893: WHERE flv.lookup_type = 'ITEM_TYPE'
8894: AND flv.enabled_flag = 'Y'
8895: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
8896: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
9264: SELECT 1
9265: FROM fnd_lookup_values_vl flv,
9266: ego_criteria_templates_v ectv,
9267: ego_criteria_v ecv,
9268: mtl_system_items_b msibs -- to assembly item
9269: WHERE ecv.customization_application_id = 702
9270: AND ecv.region_application_id = 702
9271: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
9272: AND ecv.customization_code = ectv.customization_code
9289: (
9290: SELECT 1
9291: FROM fnd_lookup_values_vl flv,
9292: ego_criteria_templates_v ectv,
9293: mtl_system_items_b msibs -- to assembly item
9294: WHERE flv.lookup_type = 'ITEM_TYPE'
9295: AND flv.enabled_flag = 'Y'
9296: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
9297: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
9652: SELECT 1
9653: FROM fnd_lookup_values_vl flv,
9654: ego_criteria_templates_v ectv,
9655: ego_criteria_v ecv,
9656: mtl_system_items_b msibs -- to assembly item
9657: WHERE ecv.customization_application_id = 702
9658: AND ecv.region_application_id = 702
9659: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
9660: AND ecv.customization_code = ectv.customization_code
9677: (
9678: SELECT 1
9679: FROM fnd_lookup_values_vl flv,
9680: ego_criteria_templates_v ectv,
9681: mtl_system_items_b msibs -- to assembly item
9682: WHERE flv.lookup_type = 'ITEM_TYPE'
9683: AND flv.enabled_flag = 'Y'
9684: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
9685: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
9786:
9787: SELECT COUNT (*)
9788: INTO l_no_access_comp_cnt
9789: FROM bom_components_b bcb,
9790: mtl_system_items_b_kfv msbk1,
9791: bom_copy_explosions_v bev
9792: WHERE bcb.bill_sequence_id = x_from_sequence_id
9793: AND bcb.component_item_id = msbk1.inventory_item_id
9794: AND bcb.component_item_id <> to_item_id
9887: NULL,
9888: fnd_global.conc_program_id,
9889: sysdate
9890: FROM bom_components_b bcb,
9891: mtl_system_items_b_kfv msbk1,
9892: bom_copy_explosions_v bev
9893: WHERE msbk1.inventory_item_id = from_item_id
9894: AND msbk1.organization_id = from_org_id
9895: AND bcb.component_sequence_id = bev.component_sequence_id
9915: THEN
9916: SELECT COUNT (*)
9917: INTO l_no_access_comp_cnt
9918: FROM bom_components_b bcb,
9919: mtl_system_items_b_kfv msbk1,
9920: bom_copy_explosions_v bev
9921: WHERE bcb.bill_sequence_id = x_from_sequence_id
9922: AND bcb.component_item_id = msbk1.inventory_item_id
9923: AND bcb.component_item_id <> to_item_id
10016: NULL,
10017: fnd_global.conc_program_id,
10018: sysdate
10019: FROM bom_components_b bcb,
10020: mtl_system_items_b_kfv msbk1,
10021: bom_copy_explosions_v bev
10022: WHERE msbk1.inventory_item_id = from_item_id
10023: AND msbk1.organization_id = from_org_id
10024: AND bcb.component_sequence_id = bev.component_sequence_id
10081: NULL,
10082: fnd_global.conc_program_id,
10083: sysdate
10084: FROM bom_components_b bcb,
10085: mtl_system_items_b msib1, -- component
10086: bom_copy_explosions_v bev
10087: WHERE bcb.bill_sequence_id = x_from_sequence_id
10088: AND bcb.component_item_id = msib1.inventory_item_id
10089: AND bcb.component_item_id <> to_item_id
10282: NULL,
10283: fnd_global.conc_program_id,
10284: sysdate
10285: FROM bom_components_b bcb,
10286: mtl_system_items_b msib1,
10287: bom_copy_explosions_v bev
10288: WHERE bcb.bill_sequence_id = x_from_sequence_id
10289: AND bcb.component_item_id = msib1.inventory_item_id
10290: AND bcb.component_item_id <> to_item_id
10485: NULL,
10486: fnd_global.conc_program_id,
10487: sysdate
10488: FROM bom_components_b bcb,
10489: mtl_system_items_b msib
10490: WHERE bcb.bill_sequence_id = to_sequence_id
10491: AND bcb.component_item_id = msib.inventory_item_id
10492: AND msib.organization_id = to_org_id
10493: AND 'Y' <>
10550: NULL,
10551: fnd_global.conc_program_id,
10552: sysdate
10553: FROM bom_components_b bcb,
10554: mtl_system_items_b msib
10555: WHERE bcb.bill_sequence_id = to_sequence_id
10556: AND bcb.component_item_id = msib.inventory_item_id
10557: AND msib.organization_id = to_org_id
10558: AND 'Y' <>
10956: NULL,
10957: fnd_global.conc_program_id,
10958: sysdate
10959: FROM bom_components_b bcb,
10960: mtl_system_items_b msib1,
10961: mtl_system_items_b msib2
10962: WHERE bcb.bill_sequence_id = to_sequence_id
10963: AND (msib1.bom_item_type = planning
10964: AND msib2.bom_item_type <> planning
10957: fnd_global.conc_program_id,
10958: sysdate
10959: FROM bom_components_b bcb,
10960: mtl_system_items_b msib1,
10961: mtl_system_items_b msib2
10962: WHERE bcb.bill_sequence_id = to_sequence_id
10963: AND (msib1.bom_item_type = planning
10964: AND msib2.bom_item_type <> planning
10965: )
11010: NULL,
11011: fnd_global.conc_program_id,
11012: sysdate
11013: FROM bom_components_b bcb,
11014: mtl_system_items_b msib1,
11015: mtl_system_items_b msib2
11016: WHERE bcb.bill_sequence_id = to_sequence_id
11017: AND (msib1.bom_item_type IN (model, option_class)
11018: AND msib2.bom_item_type = STANDARD
11011: fnd_global.conc_program_id,
11012: sysdate
11013: FROM bom_components_b bcb,
11014: mtl_system_items_b msib1,
11015: mtl_system_items_b msib2
11016: WHERE bcb.bill_sequence_id = to_sequence_id
11017: AND (msib1.bom_item_type IN (model, option_class)
11018: AND msib2.bom_item_type = STANDARD
11019: AND msib2.base_item_id IS NULL
11065: NULL,
11066: fnd_global.conc_program_id,
11067: sysdate
11068: FROM bom_components_b bcb,
11069: mtl_system_items_b msib1, -- Comp
11070: mtl_system_items_b msib2 -- Structure
11071: WHERE bcb.bill_sequence_id = to_sequence_id
11072: AND (msib1.replenish_to_order_flag = 'Y'
11073: AND msib1.bom_item_type = option_class
11066: fnd_global.conc_program_id,
11067: sysdate
11068: FROM bom_components_b bcb,
11069: mtl_system_items_b msib1, -- Comp
11070: mtl_system_items_b msib2 -- Structure
11071: WHERE bcb.bill_sequence_id = to_sequence_id
11072: AND (msib1.replenish_to_order_flag = 'Y'
11073: AND msib1.bom_item_type = option_class
11074: AND msib2.pick_components_flag = 'Y'
11120: NULL,
11121: fnd_global.conc_program_id,
11122: sysdate
11123: FROM bom_components_b bcb,
11124: mtl_system_items_b msib1, -- Comp
11125: mtl_system_items_b msib2 -- Structure
11126: WHERE bcb.bill_sequence_id = to_sequence_id
11127: AND (msib1.replenish_to_order_flag = 'Y'
11128: AND msib1.bom_item_type = STANDARD
11121: fnd_global.conc_program_id,
11122: sysdate
11123: FROM bom_components_b bcb,
11124: mtl_system_items_b msib1, -- Comp
11125: mtl_system_items_b msib2 -- Structure
11126: WHERE bcb.bill_sequence_id = to_sequence_id
11127: AND (msib1.replenish_to_order_flag = 'Y'
11128: AND msib1.bom_item_type = STANDARD
11129: AND msib2.pick_components_flag = 'Y'
11176: NULL,
11177: fnd_global.conc_program_id,
11178: sysdate
11179: FROM bom_components_b bcb,
11180: mtl_system_items_b msib1, -- Comp
11181: mtl_system_items_b msib2 -- Structure
11182: WHERE bcb.bill_sequence_id = to_sequence_id
11183: AND (msib1.pick_components_flag = 'Y'
11184: AND msib2.replenish_to_order_flag = 'Y'
11177: fnd_global.conc_program_id,
11178: sysdate
11179: FROM bom_components_b bcb,
11180: mtl_system_items_b msib1, -- Comp
11181: mtl_system_items_b msib2 -- Structure
11182: WHERE bcb.bill_sequence_id = to_sequence_id
11183: AND (msib1.pick_components_flag = 'Y'
11184: AND msib2.replenish_to_order_flag = 'Y'
11185: )
11446: SELECT 1
11447: FROM fnd_lookup_values_vl flv,
11448: ego_criteria_templates_v ectv,
11449: ego_criteria_v ecv,
11450: mtl_system_items_b msibs -- to assembly item
11451: WHERE ecv.customization_application_id = 702
11452: AND ecv.region_application_id = 702
11453: AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
11454: AND ecv.customization_code = ectv.customization_code
11471: (
11472: SELECT 1
11473: FROM fnd_lookup_values_vl flv,
11474: ego_criteria_templates_v ectv,
11475: mtl_system_items_b msibs -- to assembly item
11476: WHERE flv.lookup_type = 'ITEM_TYPE'
11477: AND flv.enabled_flag = 'Y'
11478: AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
11479: AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)