APPS.MRP_KANBAN_SNAPSHOT_PK SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 17

SELECT distinct msi.concatenated_segments
FROM   mtl_system_items_kfv msi, mrp_low_level_codes mllc
WHERE  mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
AND    mllc.organization_id =
		mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
AND    mllc.from_subinventory IS NULL
AND    mllc.from_locator_id IS NULL
AND    mllc.kanban_item_flag = 'Y'
AND    mllc.assembly_item_id <> mllc.component_item_id
AND    msi.organization_id = mllc.organization_id
AND    msi.inventory_item_id = mllc.component_item_id;

Line: 43

  SELECT count(*)
  INTO   l_count
  FROM   mrp_low_level_codes
  WHERE  plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
  AND    organization_id = mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
  AND    from_subinventory IS NULL
  AND    from_locator_id IS NULL
  AND    kanban_item_flag = 'Y'
  AND    assembly_item_id <> component_item_id;

Line: 61

    UPDATE mrp_low_level_codes mllc
    SET    (mllc.from_subinventory, mllc.from_locator_id) =
    	   (SELECT bibs.SUBINVENTORY_NAME, bibs.LOCATOR_ID
    	    FROM   bom_inventory_backflush_subinv bibs
    	    AND    bibs.inventory_item_id = mllc.component_item_id
    	    AND	   bibs.organization_id = mllc.organization_id
    	    AND    bibs.location_type = 1)  --  ??
    WHERE  mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
    AND    mllc.organization_id =
		mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
    AND    mllc.from_subinventory IS NULL
    AND	   mllc.from_locator_id IS NULL
    AND	   mllc.kanban_item_flag = 'Y'
    AND	   mllc.assembly_item_id <> mllc.component_item_id;

Line: 80

  SELECT count(*)
  INTO   l_count
  FROM   mrp_low_level_codes
  WHERE  plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
  AND    organization_id = mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
  AND    from_subinventory IS NULL
  AND    from_locator_id IS NULL
  AND    kanban_item_flag = 'Y'
  AND    assembly_item_id <> component_item_id;

Line: 97

    UPDATE mrp_low_level_codes mllc
    SET    (mllc.from_subinventory, mllc.from_locator_id) =
    	   (SELECT msi.wip_supply_subinventory,
  	   	   msi.wip_supply_locator_id
    	    FROM   mtl_system_items msi
            WHERE  msi.organization_id = mllc.organization_id
    	    AND	   msi.inventory_item_id = mllc.component_item_id)
    WHERE  mllc.plan_id =
		mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
    AND    mllc.organization_id =
                mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
    AND    mllc.from_subinventory IS NULL
    AND    mllc.from_locator_id IS NULL
    AND    mllc.kanban_item_flag = 'Y'
    AND    mllc.assembly_item_id <> mllc.component_item_id;

Line: 117

  SELECT count(*)
  INTO   l_count
  FROM   mrp_low_level_codes
  WHERE  plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
  AND    organization_id = mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
  AND    from_subinventory IS NULL
  AND    from_locator_id IS NULL
  AND    kanban_item_flag = 'Y'
  AND    assembly_item_id <> component_item_id;

Line: 202

SELECT 	parent.concatenated_segments assembly_item,
	mllc.to_subinventory,
	parent_loc.inventory_location_id to_location,
	child.concatenated_segments component_item,
	mllc.from_subinventory,
	child_loc.inventory_location_id from_location
FROM
      	mtl_item_locations parent_loc,
      	mtl_item_locations child_loc,
      	mtl_system_items_kfv parent,
	mtl_system_items_kfv child,
	mrp_low_level_codes mllc
WHERE   mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
AND     mllc.organization_id =
			mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
AND     mllc.low_level_code IS NULL
AND     parent.inventory_item_id = mllc.assembly_item_id
AND     parent.organization_id = mllc.organization_id
AND     child.inventory_item_id = mllc.component_item_id
AND     child.organization_id = mllc.organization_id
AND     parent_loc.inventory_location_id (+)  = mllc.to_locator_id
AND     parent_loc.organization_id (+)  = mllc.organization_id
AND     child_loc.inventory_location_id (+)  = mllc.from_locator_id
AND     child_loc.organization_id (+)  = mllc.organization_id
ORDER BY
	assembly_item,
	component_item;

Line: 347

    UPDATE mrp_low_level_codes mllc1
    SET	mllc1.low_level_code = l_low_level_code
    WHERE mllc1.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
    AND   mllc1.organization_id =
		mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
    AND   mllc1.low_level_code IS NULL
    AND  	NOT EXISTS
	(SELECT /*+index(mllc2 MRP_LOW_LEVEL_CODES_N1)*/ 'Exists as parent' /* Bug 4608294 - added hint*/
	 FROM	mrp_low_level_codes mllc2
	 WHERE 		mllc2.plan_id = mllc1.plan_id
         AND		mllc2.organization_id = mllc1.organization_id
         AND		mllc2.low_level_code IS NULL
         AND		( mllc2.assembly_item_id = mllc1.component_item_id AND
            		 ((((mllc2.to_subinventory = mllc1.from_subinventory AND
	 		   nvl(mllc2.to_locator_id,-1) =
					nvl(mllc1.from_locator_id, -1) ) OR
			   mllc2.to_subinventory is NULL  ) AND
			   nvl(mllc1.kanban_item_flag,'N') = 'Y') OR
			   nvl(mllc1.kanban_item_flag,'N') = 'N'))

	);

Line: 470

  SELECT min(bor.priority), count(*)
  INTO   l_highest_priority, l_num_routings
  FROM   bom_operational_routings bor
  WHERE  bor.organization_id = p_organization_id
  AND    bor.assembly_item_id = p_assembly_item_id
  AND    bor.cfm_routing_flag = 1
  AND    bor.line_id  = p_line_id;

Line: 501

  SELECT 'Condition Satisfied'
  INTO   l_dummy
  FROM   bom_operational_routings bor
  WHERE  bor.organization_id  = p_organization_id
  AND    bor.assembly_item_id  = p_assembly_item_id
  AND    bor.line_id  = p_line_id
  AND    bor.cfm_routing_flag  = 1
  AND    NVL(bor.priority,-1) = NVL(l_highest_priority,-1)
  AND    NVL(bor.alternate_routing_designator,'xx') =
             NVL(p_alternate_designator, 'xx');

Line: 534

SELECT msi.base_item_id,msi.bom_item_type
FROM   mtl_system_items msi
WHERE  msi.inventory_item_id = p_assembly_item_id
AND    msi.organization_id = p_organization_id;

Line: 546

SELECT msi.bom_item_type
FROM   mtl_system_items msi
WHERE  msi.inventory_item_id = p_comp_item_id
AND    msi.organization_id = p_organization_id;

Line: 756

    'INSERT INTO mrp_low_level_codes ( ' ||
    'plan_id,' ||
    'organization_id,' ||
    'assembly_item_id,' ||
    'to_subinventory,' ||
    'to_locator_id,' ||
    'component_item_id,' ||
    'from_subinventory,' ||
    'from_locator_id,' ||
    'component_usage,' ||
    'component_yield,' ||
/* Updated by Liye Ma  4/30/2001
   Add two more columns, to fix bug 1745046 and 1757798. */
    'planning_factor,' ||
    'item_num,' ||
/* End of update */
/*  Modified for lot based material support. Adding query of basis_type and wip_supply_type */
    'basis_type,' ||
    'wip_supply_type,' ||
    'alternate_designator,' ||
    'kanban_item_flag,' ||
    'component_category_id,' ||
    'levels_below,' ||
    'request_id,' ||
    'program_application_id,' ||
    'program_id,' ||
    'program_update_date,' ||
    'last_updated_by,' ||
    'last_update_date,' ||
    'created_by,' ||
    'creation_date )' ||
    'SELECT  /*+ ordered */' ||
    'mkp.kanban_plan_id,' ||
    'mkp.organization_id,' ||
    'bbom.assembly_item_id,' ||
    'ps.subinventory_name,' ||
    'ps.locator_id,' ||
    'msi.inventory_item_id,' ||
    'mrp_bic.supply_subinventory,' ||
    'mrp_bic.supply_locator_id,' ||
    'mrp_bic.component_quantity,' ||
    'mrp_bic.component_yield_factor,' ||
/* Updated by Liye Ma  4/30/2001
   Add two more columns, to fix bug 1745046 and 1757798. */
    'mrp_bic.planning_factor,' ||
    'mrp_bic.item_num,' ||
/* End of Update */
/*  Modified for lot based material support. Adding query of basis_type and wip_supply_type */
/*  Basis type of 1 = WIP_CONSTANTS.ITEM_BASED_MTL */
    'nvl(mrp_bic.basis_type,1),' ||
/*  Supply type of 1 = WIP_CONSTANTS.PUSH */
    'nvl(mrp_bic.wip_supply_type,1),' ||
    'bbom.alternate_bom_designator,' ||
    l_quote || 'Y' || l_quote || ',' ||
    'mic.category_id,' ||
    '1,' ||
    'fnd_global.conc_request_id,' ||
    'fnd_global.prog_appl_id,' ||
    'fnd_global.conc_program_id,' ||
    'sysdate,' ||
    'fnd_global.user_id,' ||
    'sysdate,' ||
    'fnd_global.user_id,' ||
    'sysdate ' ||
    'FROM ' ||
    'mrp_kanban_plans mkp, ' ||
    'mtl_system_items msi, ' ||
    '( SELECT /*+ no_merge */  distinct inventory_item_id ,organization_id ' ||
    '  FROM mtl_kanban_pull_sequences ' ||
    '  WHERE kanban_plan_id = :b_PRODUCTION_KANBAN ) iv, ' ||
    'bom_inventory_components mrp_bic, ' ||
    'bom_bill_of_materials bbom, ' ||
    'mtl_kanban_pull_sequences ps, ' ||
    l_additional_tables ||
    'mtl_system_items msi2 ' ||
    'WHERE mkp.kanban_plan_id = :b_kanban_plan_id ' ||
    'AND mkp.organization_id = :b_organization_id ' ||
    'AND msi.organization_id = mkp.organization_id ' ||
    'AND iv.inventory_item_id= msi.inventory_item_id ' ||
    'AND iv.organization_id = msi.organization_id ' ||
    l_additional_where ||
    'AND mrp_bic.component_item_id = msi.inventory_item_id ' ||
    'AND nvl(mrp_bic.disable_date,:b_bom_effectivity) + 1 >= :b_bom_effectivity ' ||
    'AND mrp_bic.effectivity_date <= :b_bom_effectivity ';

Line: 845

			'SELECT /*+ INDEX(bic2 BOM_INVENTORY_COMPONENTS_N1) */ '||
			'NULL ' ||
			'FROM bom_inventory_components bic2 ' ||
			'WHERE  bic2.bill_sequence_id = mrp_bic.bill_sequence_id ' ||
			'AND    bic2.component_item_id = mrp_bic.component_item_id ' ||
			'AND    (decode(bic2.implementation_date, null, ' ||
			'bic2.old_component_sequence_id, ' ||
			'bic2.component_sequence_id) = ' ||
			'decode(mrp_bic.implementation_date, null, ' ||
			'mrp_bic.old_component_sequence_id, ' ||
			'mrp_bic.component_sequence_id) ' ||
			'OR bic2.operation_seq_num = mrp_bic.operation_seq_num) ' ||
			'AND    bic2.effectivity_date <= :b_bom_effectivity ' ||
			'AND    bic2.effectivity_date > mrp_bic.effectivity_date ' ||
			'AND    (bic2.implementation_date is not null OR ' ||
			'(bic2.implementation_date is null AND EXISTS ' ||
			'(SELECT NULL ' ||
			'FROM   eng_revised_items eri ' ||
			'WHERE  bic2.revised_item_sequence_id = ' ||
			'eri.revised_item_sequence_id ' ||
			'AND    eri.mrp_active = 1 )))) ' ||
			'AND   (mrp_bic.implementation_date is not null OR ' ||
			'(mrp_bic.implementation_date is null AND EXISTS ' ||
			'(SELECT NULL ' ||
			'FROM   eng_revised_items eri ' ||
			'WHERE  mrp_bic.revised_item_sequence_id = ' ||
			'eri.revised_item_sequence_id '  ||
			'AND eri.mrp_active = 1 ))) ';

Line: 884

/* Updated by Liye Ma. 1/23/2001
   This check_min_priority serves no purposes...
   ||
    'AND 1 =  MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority ( ' ||
    				'ps.inventory_item_id, ' ||
    				'ps.organization_id, ' ||
    				'ps.wip_line_id, ' ||
    				'bbom.alternate_bom_designator ) '; */

Line: 927

    mrp_kanban_plan_pk.g_log_message := 'Inserted ' ||
		to_char(l_rows_processed) || ' into mrp_low_level_codes table';

Line: 960

		INSERT INTO mrp_low_level_codes (
		plan_id,
		organization_id,
		assembly_item_id,
		to_subinventory,
		to_locator_id,
		component_item_id,
		from_subinventory,
		from_locator_id,
		component_usage,
		component_yield,
/* Updated by Liye Ma  4/30/2001
   Add two more columns, to fix bug 1745046 and 1757798. */
                planning_factor,
		item_num,
/* End of Update */
/*  Modified for lot based material support. Adding query of basis_type and wip_supply_type */
                basis_type,
		wip_supply_type,
		alternate_designator,
		levels_below,
		kanban_item_flag,
		component_category_id,
			request_id,
			program_application_id,
			program_id,
			program_update_date,
			last_updated_by,
			last_update_date,
			created_by,
			creation_date)
		SELECT /*+
                    LEADING(MLLC)
                    USE_NL(MLLC MRP_BIC BBOM MIC PS)
                  */ DISTINCT
		mllc.plan_id,
		mllc.organization_id,
		bbom.assembly_item_id,
		ps.subinventory_name,
		ps.locator_id,
		mrp_bic.component_item_id,
		mrp_bic.supply_subinventory,
		mrp_bic.supply_locator_id,
		mrp_bic.component_quantity,
		mrp_bic.component_yield_factor,
/* Updated by Liye Ma  4/30/2001
   Add two more columns, to fix bug 1745046 and 1757798. */
                mrp_bic.planning_factor,
		mrp_bic.item_num,
/* End of Update */
/*  Modified for lot based material support. Adding query of basis_type and wip_supply_type */
                nvl(mrp_bic.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL),
		nvl(mrp_bic.wip_supply_type,WIP_CONSTANTS.PUSH),
		bbom.alternate_bom_designator,
		l_level_count + 1,
		NULL,		-- set it to NULL and update it next stmt
		mic.category_id,
			fnd_global.conc_request_id,
			fnd_global.prog_appl_id,
			fnd_global.conc_program_id,
			sysdate,
			fnd_global.user_id,
			sysdate,
			fnd_global.user_id,
			sysdate
		FROM
		mtl_kanban_pull_sequences ps,
		bom_bill_of_materials bbom,
		mtl_item_categories mic,
		bom_inventory_components mrp_bic,
		mrp_low_level_codes mllc
		WHERE	mllc.plan_id =
			mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
		AND	mllc.organization_id =
			mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
		AND		mllc.levels_below = l_level_count
		AND		mrp_bic.component_item_id = mllc.assembly_item_id
		AND		(nvl(mrp_bic.disable_date,
		mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity)) +1 >=
			mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
		AND         mrp_bic.effectivity_date <=
			mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
		AND	bbom.common_bill_sequence_id = mrp_bic.bill_sequence_id
		AND     bbom.organization_id = mllc.organization_id
		AND    	ps.kanban_plan_id (+) =
		decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,
		2, mrp_kanban_plan_pk.G_PRODUCTION_KANBAN,
		1, mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
		mrp_kanban_plan_pk.G_PRODUCTION_KANBAN)
		AND       	ps.organization_id (+) = bbom.organization_id
		AND       	ps.inventory_item_id (+) = bbom.assembly_item_id
		AND       	ps.source_type (+) = 4 /* KANBAN_PRODUCTION */
/* Fix bug 2090054
		AND         1 =  Check_Min_Priority (
	        ps.inventory_item_id,
	        ps.organization_id,
	        ps.wip_line_id,
		bbom.alternate_bom_designator)
*/
		AND    	mic.organization_id (+)  =
		mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
		AND    	mic.inventory_item_id (+) = mllc.assembly_item_id
		AND    	mic.category_set_id (+) =
			mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id
		/*  Avoid re-selecting items already in mrp_low_level_codes */
		AND	 NOT EXISTS
		( SELECT 'Exists'
		 FROM 	mrp_low_level_codes mllc2
		 WHERE  mllc2.plan_id =
			mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
		 AND	mllc2.organization_id =
			mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
		 AND    mllc2.component_item_id = mrp_bic.component_item_id )
         AND    EXISTS(
            SELECT  /*+no_unnest*/ 1
             FROM mtl_system_items msi
            WHERE msi.organization_id = bbom.organization_id
              AND msi.inventory_item_id = bbom.assembly_item_id
              AND msi.planning_make_buy_code = 1);

Line: 1080

		INSERT INTO mrp_low_level_codes (
		plan_id,
		organization_id,
		assembly_item_id,
		to_subinventory,
		to_locator_id,
		component_item_id,
		from_subinventory,
		from_locator_id,
		component_usage,
		component_yield,
/* Updated by Liye Ma  4/30/2001
   Add two more columns, to fix bug 1745046 and 1757798. */
                planning_factor,
		item_num,
/* End of Update */
/*  Modified for lot based material support. Adding query of basis_type and wip_supply_type */
                basis_type,
		wip_supply_type,
		alternate_designator,
		levels_below,
		kanban_item_flag,
		component_category_id,
			request_id,
			program_application_id,
			program_id,
			program_update_date,
			last_updated_by,
			last_update_date,
			created_by,
			creation_date)
		SELECT /*+ INDEX(PS MTL_KANBAN_PULL_SEQUENCES_N1) */ DISTINCT
		mllc.plan_id,
		mllc.organization_id,
		bbom.assembly_item_id,
		ps.subinventory_name,
		ps.locator_id,
		mrp_bic.component_item_id,
		mrp_bic.supply_subinventory,
		mrp_bic.supply_locator_id,
		mrp_bic.component_quantity,
		mrp_bic.component_yield_factor,
/* Updated by Liye Ma  4/30/2001
   Add two more columns, to fix bug 1745046 and 1757798. */
                mrp_bic.planning_factor,
		mrp_bic.item_num,
/* End of Update */
/*  Modified for lot based material support. Adding query of basis_type and wip_supply_type */
                nvl(mrp_bic.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL),
		nvl(mrp_bic.wip_supply_type,WIP_CONSTANTS.PUSH),
		bbom.alternate_bom_designator,
		l_level_count + 1,
		NULL,		-- set it to NULL and update it next stmt
		mic.category_id,
			fnd_global.conc_request_id,
			fnd_global.prog_appl_id,
			fnd_global.conc_program_id,
			sysdate,
			fnd_global.user_id,
			sysdate,
			fnd_global.user_id,
			sysdate
		FROM
		mtl_kanban_pull_sequences ps,
		bom_bill_of_materials bbom,
			mtl_item_categories mic,
		bom_inventory_components mrp_bic,
		mrp_low_level_codes mllc
		WHERE	mllc.plan_id =
			mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
		AND	mllc.organization_id =
			mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
		AND		mllc.levels_below = l_level_count
		AND		mrp_bic.component_item_id = mllc.assembly_item_id
		AND		(nvl(mrp_bic.disable_date,
		mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity)) +1 >=
		mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
		AND         mrp_bic.effectivity_date <=
			mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
		AND         NOT EXISTS (
		SELECT /*+ INDEX(bic2 BOM_INVENTORY_COMPONENTS_N1) */
		NULL
		FROM   bom_inventory_components bic2
		WHERE  bic2.bill_sequence_id = mrp_bic.bill_sequence_id
		AND    bic2.component_item_id = mrp_bic.component_item_id
		AND    (decode(bic2.implementation_date, null,
	        bic2.old_component_sequence_id,
	        bic2.component_sequence_id) =
		decode(mrp_bic.implementation_date, null,
		mrp_bic.old_component_sequence_id,
		mrp_bic.component_sequence_id)
		OR bic2.operation_seq_num = mrp_bic.operation_seq_num)
		AND    bic2.effectivity_date <=
		mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
		AND    bic2.effectivity_date > mrp_bic.effectivity_date
		AND    (bic2.implementation_date is not null OR
		(bic2.implementation_date is null AND EXISTS
		(SELECT NULL
		  FROM   eng_revised_items eri
		  WHERE  bic2.revised_item_sequence_id =
		  eri.revised_item_sequence_id
		  AND    eri.mrp_active = 1 ))))
		AND 	(mrp_bic.implementation_date is not null OR
		(mrp_bic.implementation_date is null AND EXISTS
		(SELECT NULL
		 FROM   eng_revised_items eri
		 WHERE  mrp_bic.revised_item_sequence_id =
			eri.revised_item_sequence_id
		 AND    eri.mrp_active = 1 )))
		 AND	bbom.common_bill_sequence_id = mrp_bic.bill_sequence_id
		AND     bbom.organization_id = mllc.organization_id
		AND     ps.kanban_plan_id (+) =
			decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,
		        2, mrp_kanban_plan_pk.G_PRODUCTION_KANBAN,
			1, mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
			mrp_kanban_plan_pk.G_PRODUCTION_KANBAN)
		AND     ps.organization_id (+) = bbom.organization_id
		AND     ps.inventory_item_id (+) = bbom.assembly_item_id
		AND     ps.source_type (+) = 4 /* KANBAN_PRODUCTION */
/* Fix bug 2090054
		AND     1 =  Check_Min_Priority (
			ps.inventory_item_id,
			ps.organization_id,
			ps.wip_line_id,
			bbom.alternate_bom_designator)
*/
		AND     mic.organization_id (+)  =
			mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
		AND     mic.inventory_item_id (+) = mllc.assembly_item_id
		AND     mic.category_set_id (+) =
			mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id
		/*  Avoid re-selecting items already in mrp_low_level_codes */
		AND	 NOT EXISTS
		( SELECT 'Exists'
		 FROM 	mrp_low_level_codes mllc2
		 WHERE  mllc2.plan_id =
			mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
		 AND	mllc2.organization_id =
			mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
		 AND    mllc2.component_item_id = mrp_bic.component_item_id )
         AND    EXISTS(
            SELECT  /*+no_unnest*/ 1
             FROM mtl_system_items msi
            WHERE msi.organization_id = bbom.organization_id
              AND msi.inventory_item_id = bbom.assembly_item_id
              AND msi.planning_make_buy_code = 1);

Line: 1241

  UPDATE mrp_low_level_codes mllc
  SET (mllc.kanban_item_flag) =
      (select nvl(max(decode(kbn_items.release_kanban_flag, 1, 'Y', 'Y')), 'N')
       from   mtl_kanban_pull_sequences kbn_items
       where kbn_items.kanban_plan_id =
	     mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
       and   kbn_items.organization_id =
	     mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
       and   kbn_items.inventory_item_id = mllc.assembly_item_id)
  WHERE mllc.plan_id =
          mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
  AND mllc.organization_id =
          mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
  AND mllc.kanban_item_flag is null;

Line: 1277

  UPDATE  mrp_low_level_codes mllc
  SET	  (mllc.operation_yield,mllc.net_planning_percent) =
	  (SELECT min(bos.reverse_cumulative_yield),
	 	  min(bos.net_planning_percent)
	   FROM	  bom_operation_sequences bos,
		  bom_operational_routings bor,
		  bom_inventory_components mrp_bic,
		  bom_bill_of_materials bbom
	   WHERE  bbom.assembly_item_id = mllc.assembly_item_id
	   AND	  bbom.organization_id = mllc.organization_id
	   AND	  nvl(bbom.alternate_bom_designator, 'xxx') =
               	  nvl(mllc.alternate_designator, 'xxx')
	   AND	  mrp_bic.bill_sequence_id = bbom.common_bill_sequence_id
	   AND	  mrp_bic.component_item_id = mllc.component_item_id
    	   AND	  (nvl(mrp_bic.disable_date,
		   mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity) +1) >=
		   mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
    	   AND     mrp_bic.effectivity_date <=
		   mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
      	   AND    NOT EXISTS (
               	  SELECT NULL
                  FROM   bom_inventory_components bic2
                  WHERE  bic2.bill_sequence_id = mrp_bic.bill_sequence_id
                  AND    bic2.component_item_id = mrp_bic.component_item_id
                  AND    (decode(bic2.implementation_date, null,
                               bic2.old_component_sequence_id,
                               bic2.component_sequence_id) =
                       decode(mrp_bic.implementation_date, null,
                              mrp_bic.old_component_sequence_id,
                              mrp_bic.component_sequence_id)
                       OR bic2.operation_seq_num = mrp_bic.operation_seq_num)
                  AND    bic2.effectivity_date <=
			mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
                  AND    bic2.effectivity_date > mrp_bic.effectivity_date
                  AND    (bic2.implementation_date is not null OR
                         (bic2.implementation_date is null AND EXISTS
                         (SELECT NULL
                          FROM   eng_revised_items eri
                          WHERE  bic2.revised_item_sequence_id =
                                                eri.revised_item_sequence_id
                          AND    eri.mrp_active = 1 ))))
           AND    (mrp_bic.implementation_date is not null OR
                         (mrp_bic.implementation_date is null AND EXISTS
                         (SELECT NULL
                          FROM   eng_revised_items eri
                          WHERE  mrp_bic.revised_item_sequence_id =
                                                eri.revised_item_sequence_id
                          AND    eri.mrp_active = 1 )))
	   AND	  bor.organization_id = bbom.organization_id
	   AND	  bor.assembly_item_id = bbom.assembly_item_id
	   AND	  nvl(bor.alternate_routing_designator, 'xxx') =
               	  nvl(bbom.alternate_bom_designator, 'xxx')
	   AND	  bos.routing_sequence_id = bor.routing_sequence_id
	   AND	  bos.operation_seq_num = mrp_bic.operation_seq_num
	   AND	  nvl(bos.operation_type, 1) = 1
	   AND	  nvl(bos.disable_date,
		    mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity) + 1
			>= mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
	   AND	  bos.effectivity_date <=
		  mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity)
  WHERE	   mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id;

Line: 1346

    mrp_kanban_plan_pk.g_log_message := 'Updated mrp_low_level_codes ' ||
			'with net planning percent and yield information';

Line: 1360

    INSERT INTO mrp_low_level_codes (
	plan_id,
	organization_id,
	assembly_item_id,
	component_item_id,
        from_subinventory,
	from_locator_id,
	levels_below,
	kanban_item_flag,
	component_category_id,
    	request_id,
    	program_application_id,
    	program_id,
    	program_update_date,
        last_updated_by,
        last_update_date,
        created_by,
        creation_date )
    SELECT DISTINCT
	mllc1.plan_id,
	mllc1.organization_id,
	-1,
	mllc1.assembly_item_id,
	mllc1.to_subinventory,
	mllc1.to_locator_id,
	l_level_count + 1,
	decode(kbn_items.release_kanban_flag, 1, 'Y', 2, 'Y', 'N'),
	mic.category_id,
        fnd_global.conc_request_id,
        fnd_global.prog_appl_id,
        fnd_global.conc_program_id,
        sysdate,
        fnd_global.user_id,
        sysdate,
        fnd_global.user_id,
        sysdate
    FROM
 	mtl_item_categories mic,
 	mtl_kanban_pull_sequences kbn_items,
	mrp_low_level_codes mllc1
    WHERE
    	mllc1.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id AND
    	mllc1.organization_id =
	   mrp_kanban_plan_pk.g_kanban_info_rec.organization_id AND
	kbn_items.inventory_item_id (+) =
				mrp_kanban_plan_pk.G_PRODUCTION_KANBAN AND
	kbn_items.inventory_item_id (+) = mllc1.assembly_item_id AND
	kbn_items.organization_id (+) = mllc1.organization_id AND
	mic.inventory_item_id (+) = mllc1.assembly_item_id AND
	mic.organization_id (+) = mllc1.organization_id AND
	mic.category_set_id (+) =
		mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id AND
    	--select only the assembly items that do not exist as components
     	NOT EXISTS
	(SELECT 'Exists'
	 FROM 	mrp_low_level_codes mllc2
	 WHERE  mllc2.plan_id = mllc1.plan_id AND
	 	mllc2.organization_id = mllc1.organization_id AND
	     	mllc2.component_item_id = mllc1.assembly_item_id );

Line: 1441

  INSERT INTO mrp_low_level_codes (
        plan_id,
        organization_id,
        assembly_item_id,
        to_subinventory,
        to_locator_id,
        component_item_id,
        from_subinventory,
        from_locator_id,
        component_usage,
        component_yield,
	supply_source_type,
	replenishment_lead_time,
	kanban_item_flag,
	component_category_id,
    	request_id,
    	program_application_id,
    	program_id,
    	program_update_date,
        last_updated_by,
        last_update_date,
        created_by,
        creation_date )
  SELECT DISTINCT
	mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
	ps.organization_id,
	ps.inventory_item_id,
	ps.subinventory_name,
	ps.locator_id,
	ps.inventory_item_id,
	ps.source_subinventory,
	ps.source_locator_id,
	1,
	1,
	ps.source_type,
	ps.replenishment_lead_time,
	'Y',
	mllc.component_category_id,
        fnd_global.conc_request_id,
        fnd_global.prog_appl_id,
        fnd_global.conc_program_id,
        sysdate,
        fnd_global.user_id,
        sysdate,
        fnd_global.user_id,
        sysdate
  FROM  mtl_kanban_pull_sequences ps,
	mrp_low_level_codes mllc
  WHERE	ps.source_type = 3 -- only intra org replenishments
  AND   ps.kanban_plan_id =
		decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,
                2, mrp_kanban_plan_pk.G_PRODUCTION_KANBAN,
                1, mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
                mrp_kanban_plan_pk.G_PRODUCTION_KANBAN)
  AND	ps.organization_id = mllc.organization_id
  AND	ps.inventory_item_id = mllc.component_item_id
  AND	mllc.organization_id =
		mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
  AND	mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
  AND   mllc.kanban_item_flag = 'Y';

Line: 1505

		'Completed inserting into mrp_low_level_codes table';