APPS.ENG_ITEM_PKG SQL Statements

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

Line: 85

      UPDATE MTL_SYSTEM_ITEMS
      SET ENG_ITEM_FLAG = 'N',
          --DESCRIPTION = X_mfg_description,
          LAST_UPDATE_LOGIN = to_number(Fnd_Profile.Value('LOGIN_ID')),

          -----------------------------------
          -- Commented out by AS on 04/14/98
          -- See bug 647693.

          -- CREATED_BY = to_number(Fnd_Profile.Value('USER_ID')),
	  -- CREATION_DATE = SYSDATE,
          -----------------------------------

          LAST_UPDATE_DATE = SYSDATE,
          LAST_UPDATED_BY = to_number(Fnd_Profile.Value('USER_ID')),
          ENGINEERING_DATE = SYSDATE,
          ENGINEERING_ECN_CODE = X_ecn_name
      WHERE INVENTORY_ITEM_ID = X_eng_item_id
      AND ORGANIZATION_ID = X_org_id;

Line: 116

      SELECT MASTER_ORGANIZATION_ID
      INTO X_master_org
      FROM MTL_PARAMETERS
      WHERE ORGANIZATION_ID = X_org_id;

Line: 133

      UPDATE MTL_SYSTEM_ITEMS
      SET ENG_ITEM_FLAG = 'N',
          --DESCRIPTION = X_mfg_description,
          LAST_UPDATE_LOGIN = to_number(Fnd_Profile.Value('LOGIN_ID')),

          -----------------------------------
          -- Commented out by AS on 04/14/98
          -- See bug 647693.

          -- CREATED_BY = to_number(Fnd_Profile.Value('USER_ID')),
	  -- CREATION_DATE = SYSDATE,
          -----------------------------------

          LAST_UPDATE_DATE = SYSDATE,
          LAST_UPDATED_BY = to_number(Fnd_Profile.Value('USER_ID')),
          ENGINEERING_DATE = SYSDATE,
          ENGINEERING_ECN_CODE = X_ecn_name
      WHERE INVENTORY_ITEM_ID = X_eng_item_id
      AND ORGANIZATION_ID = X_master_org
      AND ENG_ITEM_FLAG <> 'N';

Line: 165

      SELECT MASTER_ORGANIZATION_ID
      INTO X_master_org
      FROM MTL_PARAMETERS
      WHERE ORGANIZATION_ID = X_org_id;

Line: 457

                                                     X_last_update_date => SYSDATE,
                                                     X_last_updated_by => to_number(Fnd_Profile.Value('USER_ID')),
                                                     X_creation_date => SYSDATE,
                                                     X_created_by => to_number(Fnd_Profile.Value('USER_ID')),
                                                     X_last_update_login => to_number(Fnd_Profile.Value('LOGIN_ID')),
                                                     X_effectivity_date => SYSDATE,
                                                     X_change_notice => X_ecn_name,
                                                     X_implementation_date => SYSDATE);

Line: 478

                                                 X_last_update_date => SYSDATE,
                                                 X_last_updated_by => to_number(Fnd_Profile.Value('USER_ID')),
                                                 X_creation_date => SYSDATE,
                                                 X_created_by => to_number(Fnd_Profile.Value('USER_ID')),
                                                 X_last_update_login => to_number(Fnd_Profile.Value('LOGIN_ID')),
                                                 X_effectivity_date => SYSDATE,
                                                 X_change_notice => X_ecn_name,
                                                 X_implementation_date => SYSDATE);

Line: 502

    SELECT Structure_Type_Id
      INTO l_GTIN_Id
        FROM bom_structure_types_vl
    WHERE Structure_Type_Name ='Packaging Hierarchy';

Line: 555

  UPDATE MTL_SYSTEM_ITEMS
  SET ENG_ITEM_FLAG = 'N'
  WHERE ORGANIZATION_ID = X_org_id
  AND ENG_ITEM_FLAG <> 'N'
  AND INVENTORY_ITEM_ID IN
  (SELECT BIC.COMPONENT_ITEM_ID
   FROM BOM_INVENTORY_COMPONENTS BIC,
        BOM_BILL_OF_MATERIALS BOM
   WHERE BOM.ORGANIZATION_ID = X_org_id
   AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
   AND ((X_designator_option = 2 AND
         BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
     OR (X_designator_option = 3 AND
         BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
     OR (X_designator_option = 1))
   AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
   AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
   AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID);

Line: 591

  UPDATE MTL_SYSTEM_ITEMS
  SET ENG_ITEM_FLAG = 'N'
  WHERE ORGANIZATION_ID = X_org_id
  AND ENG_ITEM_FLAG <> 'N'
  AND INVENTORY_ITEM_ID IN
  (select BSC.SUBSTITUTE_COMPONENT_ID
   from BOM_SUBSTITUTE_COMPONENTS BSC,
        BOM_INVENTORY_COMPONENTS BIC,
        BOM_BILL_OF_MATERIALS BOM
   WHERE
   BSC.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID
   AND BOM.ORGANIZATION_ID = X_org_id
   AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
   AND ((X_designator_option = 2 AND
         BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
     OR (X_designator_option = 3 AND
         BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
     OR (X_designator_option = 1))
   AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
   AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
   AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID);

Line: 628

      SELECT MASTER_ORGANIZATION_ID
      INTO X_master_org
      FROM MTL_PARAMETERS
      WHERE ORGANIZATION_ID = X_org_id;

Line: 645

  	UPDATE MTL_SYSTEM_ITEMS
  	SET ENG_ITEM_FLAG = 'N'
  	WHERE ORGANIZATION_ID = X_master_org
  	AND ENG_ITEM_FLAG <> 'N'
  	AND INVENTORY_ITEM_ID IN
  	(SELECT BIC.COMPONENT_ITEM_ID
   	FROM BOM_INVENTORY_COMPONENTS BIC,
        	BOM_BILL_OF_MATERIALS BOM
   	WHERE BOM.ORGANIZATION_ID = X_org_id
   	AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
   	AND ((X_designator_option = 2 AND
         	BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
     	OR (X_designator_option = 3 AND
         	BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
     	OR (X_designator_option = 1))
   	AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
        AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
   	AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID);

Line: 718

  SELECT *
  FROM BOM_BILL_OF_MATERIALS BOM
  WHERE BOM.ORGANIZATION_ID = X_org_id
  AND BOM.ASSEMBLY_ITEM_ID = X_item_id
  AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
  AND ((X_designator_option = 2 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
    OR (X_designator_option = 3 AND BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
    OR (X_designator_option = 1));

Line: 757

				SELECT BOR.COMMON_ROUTING_SEQUENCE_ID, BOR.ROUTING_TYPE
				INTO l_routing_sequence_id, l_routing_type
				FROM BOM_OPERATIONAL_ROUTINGS BOR
				WHERE BOR.ASSEMBLY_ITEM_ID = ctb.ASSEMBLY_ITEM_ID
				AND BOR.ORGANIZATION_ID = ctb.ORGANIZATION_ID
				AND BOR.ALTERNATE_ROUTING_DESIGNATOR = ctb.ALTERNATE_BOM_DESIGNATOR;

Line: 774

				SELECT BOR.COMMON_ROUTING_SEQUENCE_ID, BOR.ROUTING_TYPE
				INTO l_routing_sequence_id, l_routing_type
				FROM BOM_OPERATIONAL_ROUTINGS BOR
				WHERE BOR.ASSEMBLY_ITEM_ID = ctb.ASSEMBLY_ITEM_ID
				AND BOR.ORGANIZATION_ID = ctb.ORGANIZATION_ID
				AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL;

Line: 804

	-- Step 2: Update OPERATION_SEQ_NUM Accordingly
	BEGIN
		IF ( l_routing_sequence_id = -1 )
		THEN
			X_stmt_num := 303;

Line: 809

			UPDATE BOM_INVENTORY_COMPONENTS
			SET OPERATION_SEQ_NUM = 1
		        WHERE BILL_SEQUENCE_ID = ctb.BILL_SEQUENCE_ID;

Line: 814

			UPDATE BOM_INVENTORY_COMPONENTS BIC
		        SET BIC.OPERATION_SEQ_NUM = 1
		        WHERE BIC.BILL_SEQUENCE_ID = ctb.BILL_SEQUENCE_ID
		        AND NOT EXISTS (SELECT NULL
					FROM BOM_OPERATION_SEQUENCES BOS
					WHERE ROUTING_SEQUENCE_ID = l_routing_sequence_id
					AND BOS.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM);

Line: 842

	-- Step 3: Validate that there are no overlapping components created by result of the above update
	BEGIN
		X_stmt_num := 305;

Line: 845

		SELECT count(*)
		INTO DUMMY
		FROM BOM_INVENTORY_COMPONENTS BIC
		WHERE BIC.BILL_SEQUENCE_ID = ctb.BILL_SEQUENCE_ID
		AND EXISTS
		(SELECT NULL
		 FROM BOM_INVENTORY_COMPONENTS BIC2
		 WHERE BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
		 AND BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
		 AND BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
		 AND NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
		 AND BIC2.COMPONENT_SEQUENCE_ID <> BIC.COMPONENT_SEQUENCE_ID
		 AND NVL(BIC2.OLD_COMPONENT_SEQUENCE_ID, BIC2.COMPONENT_SEQUENCE_ID)
		         <> BIC.COMPONENT_SEQUENCE_ID
		 AND ((X_unit_assembly = 'Y'
		       AND BIC2.DISABLE_DATE IS NULL
		       AND (BIC.TO_END_ITEM_UNIT_NUMBER IS NULL
		            OR BIC.TO_END_ITEM_UNIT_NUMBER >= BIC2.FROM_END_ITEM_UNIT_NUMBER)
		       AND (BIC2.TO_END_ITEM_UNIT_NUMBER IS NULL
		            OR BIC.FROM_END_ITEM_UNIT_NUMBER <= BIC2.TO_END_ITEM_UNIT_NUMBER))
		     OR (X_unit_assembly = 'N'
		         AND BIC2.EFFECTIVITY_DATE BETWEEN BIC.EFFECTIVITY_DATE
			                           AND NVL(BIC.DISABLE_DATE - 1, BIC2.EFFECTIVITY_DATE + 1)))
		);

Line: 889

    UPDATE BOM_INVENTORY_COMPONENTS BIC
    SET OPERATION_SEQ_NUM = 1
    WHERE NOT EXISTS
      (SELECT 'X'
       FROM BOM_OPERATIONAL_ROUTINGS BOR,
            BOM_BILL_OF_MATERIALS BOM,
            BOM_OPERATION_SEQUENCES BOS
       WHERE BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
       AND BOR.ROUTING_TYPE = 1
       AND BOM.ASSEMBLY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
       AND BOM.ORGANIZATION_ID = BOR.ORGANIZATION_ID
       AND NVL(BOM.ALTERNATE_BOM_DESIGNATOR, 'NONE') =
           NVL(BOR.ALTERNATE_ROUTING_DESIGNATOR, 'NONE')
       AND BOR.COMMON_ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
       AND BIC.OPERATION_SEQ_NUM = BOS.OPERATION_SEQ_NUM)
    AND BIC.BILL_SEQUENCE_ID IN
      (SELECT BOM2.BILL_SEQUENCE_ID
       FROM BOM_BILL_OF_MATERIALS BOM2
       WHERE BOM2.ORGANIZATION_ID = X_org_id
       AND BOM2.ASSEMBLY_ITEM_ID = X_item_id
       AND ((X_designator_option = 2 AND
             BOM2.ALTERNATE_BOM_DESIGNATOR IS NULL)
            OR
            (X_designator_option = 3 AND
             BOM2.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
            OR
            (X_designator_option = 1)));

Line: 939

  SELECT count(*)
  INTO DUMMY
  FROM BOM_INVENTORY_COMPONENTS BIC
  WHERE BIC.BILL_SEQUENCE_ID IN
    (SELECT BOM.BILL_SEQUENCE_ID
     FROM BOM_BILL_OF_MATERIALS BOM
     WHERE BOM.ORGANIZATION_ID = X_org_id
     AND BOM.ASSEMBLY_ITEM_ID = X_item_id
     AND ((X_designator_option = 2 AND
           BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
          OR
          (X_designator_option = 3 AND
           BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
          OR
          (X_designator_option = 1)))
     AND EXISTS
       (SELECT NULL
        FROM BOM_INVENTORY_COMPONENTS BIC2
        WHERE BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
        AND BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
        AND BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
	AND NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
        AND BIC2.COMPONENT_SEQUENCE_ID <> BIC.COMPONENT_SEQUENCE_ID
        AND NVL(BIC2.OLD_COMPONENT_SEQUENCE_ID, BIC2.COMPONENT_SEQUENCE_ID) <> BIC.COMPONENT_SEQUENCE_ID
        AND ((X_unit_assembly = 'Y'
            AND BIC2.DISABLE_DATE IS NULL
	    AND (BIC.TO_END_ITEM_UNIT_NUMBER IS NULL
             OR BIC.TO_END_ITEM_UNIT_NUMBER >= BIC2.FROM_END_ITEM_UNIT_NUMBER)
            AND (BIC2.TO_END_ITEM_UNIT_NUMBER IS NULL
             OR BIC.FROM_END_ITEM_UNIT_NUMBER <= BIC2.TO_END_ITEM_UNIT_NUMBER))
	 OR (X_unit_assembly = 'N'
	    AND BIC2.EFFECTIVITY_DATE BETWEEN BIC.EFFECTIVITY_DATE AND
            NVL(BIC.DISABLE_DATE - 1, BIC2.EFFECTIVITY_DATE + 1))));