APPS.ENG_BOM_PKG SQL Statements

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

Line: 15

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

Line: 49

PROCEDURE BOM_UPDATE
(
X_org_id			IN NUMBER,
X_eng_item_id			IN NUMBER,
X_designator_option		IN NUMBER,
X_transfer_option		IN NUMBER,
X_alt_bom_designator		IN VARCHAR2,
X_effectivity_date		IN DATE,
X_implemented_only		IN NUMBER,
X_unit_number			IN VARCHAR2 DEFAULT NULL
)
IS
  X_stmt_num	NUMBER;

Line: 71

  select bill_sequence_id from bom_structures_b
       WHERE ORGANIZATION_ID = X_org_id
       AND ASSEMBLY_ITEM_ID = X_eng_item_id
       AND ((X_designator_option = 2 AND
	   ALTERNATE_BOM_DESIGNATOR IS NULL)
        OR
        (X_designator_option = 3 AND
         ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
        OR
         X_designator_option = 1)
       AND nvl(effectivity_control , 1) <> 4;

Line: 86

   select msib.bom_item_type, msib.replenish_to_order_flag, bcb.optional
   from bom_components_b bcb, mtl_system_items_b msib
   where bcb.bill_sequence_id = bill_id
   and msib.inventory_item_id = bcb.component_item_id
   and msib.organization_id = bcb.pk2_value;

Line: 98

select msib.bom_item_type, msib.pick_components_flag into l_parent_BIT, l_PTO_flag from mtl_system_items_b msib where inventory_item_id = X_eng_item_id and organization_id = X_org_id;

Line: 130

  UPDATE BOM_BILL_OF_MATERIALS
  SET ASSEMBLY_TYPE = 1,
      LAST_UPDATE_DATE = sysdate,
      LAST_UPDATED_BY = to_number(fnd_profile.value('USER_ID'))
  WHERE ORGANIZATION_ID = X_org_id
  AND ASSEMBLY_ITEM_ID = X_eng_item_id
  AND ((X_designator_option = 2 AND
        ALTERNATE_BOM_DESIGNATOR IS NULL)
       OR
       (X_designator_option = 3 AND
        ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
       OR
       X_designator_option = 1)
  AND nvl(effectivity_control , 1) <> 4 -- Bug 4210718
  AND ((X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
      OR STRUCTURE_TYPE_ID IS NULL); --added for bug 9436790

Line: 146

  UPDATE BOM_EXPLOSIONS_ALL   -- Update Sql added for bug#9260472
     SET ASSEMBLY_TYPE = 1,
         LAST_UPDATE_DATE = sysdate,
         LAST_UPDATED_BY = to_number(fnd_profile.value('USER_ID'))
   WHERE ORGANIZATION_ID   = X_org_id
     AND COMPONENT_ITEM_ID = X_eng_item_id
     AND ((X_designator_option = 2 AND
           ALTERNATE_BOM_DESIGNATOR IS NULL)
         OR
         (X_designator_option = 3 AND
           ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
         OR
          X_designator_option = 1)
  AND nvl(effectivity_control , 1) <> 4
  AND ((X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
      OR STRUCTURE_TYPE_ID IS NULL); --added for bug 9436790

Line: 169

    DELETE FROM BOM_INVENTORY_COMPONENTS BIC
    WHERE ((X_implemented_only = 1 AND BIC.IMPLEMENTATION_DATE IS NULL)
      OR (X_transfer_option = 2 AND
           (BIC.FROM_END_ITEM_UNIT_NUMBER > X_unit_number
            OR (BIC.FROM_END_ITEM_UNIT_NUMBER < X_unit_number
		AND NVL(BIC.TO_END_ITEM_UNIT_NUMBER, X_unit_number)
              < X_unit_number)))
      OR (X_transfer_option = 3 AND
	   NVL(BIC.TO_END_ITEM_UNIT_NUMBER, X_unit_number) < X_unit_number))
    AND BIC.BILL_SEQUENCE_ID in  (SELECT BOM_T.BILL_SEQUENCE_ID
              FROM BOM_BILL_OF_MATERIALS BOM_T
              WHERE ORGANIZATION_ID = X_org_id
              AND ASSEMBLY_ITEM_ID = X_eng_item_id
              AND ASSEMBLY_TYPE = 1
              AND ((X_designator_option = 2 AND ALTERNATE_BOM_DESIGNATOR IS NULL)
                OR (X_designator_option = 3 AND 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_T.effectivity_control , 1) <> 4 -- Bug 4210718
              );

Line: 190

    DELETE FROM BOM_INVENTORY_COMPONENTS BIC
    WHERE ((X_implemented_only = 1 AND BIC.IMPLEMENTATION_DATE IS NULL)
      OR (X_transfer_option = 2 AND
           (BIC.EFFECTIVITY_DATE > X_effectivity_date
            OR NVL(BIC.DISABLE_DATE, X_effectivity_date + 1)
              <= X_effectivity_date))
      OR (X_transfer_option = 3 AND NVL(BIC.DISABLE_DATE, X_effectivity_date +
		 1) <= X_effectivity_date))
    AND BIC.BILL_SEQUENCE_ID in  (SELECT BOM_T.BILL_SEQUENCE_ID
              FROM BOM_BILL_OF_MATERIALS BOM_T
              WHERE ORGANIZATION_ID = X_org_id
              AND ASSEMBLY_ITEM_ID = X_eng_item_id
              AND ASSEMBLY_TYPE = 1
              AND ((X_designator_option = 2 AND ALTERNATE_BOM_DESIGNATOR IS NULL)
                OR (X_designator_option = 3 AND 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_T.effectivity_control , 1) <> 4 -- Bug 4210718
              );

Line: 226

    ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'BOM_UPDATE',
                                         stmt_num => X_stmt_num,
                                         message_name => 'ENG_ENUBRT_ERROR',
                                         token => SQLERRM);

Line: 231

END BOM_UPDATE;

Line: 290

  select bill_sequence_id from bom_structures_b
       WHERE ORGANIZATION_ID = X_org_id
       AND ASSEMBLY_ITEM_ID = X_eng_item_id
       AND ((X_designator_option = 2 AND
	   ALTERNATE_BOM_DESIGNATOR IS NULL)
        OR
        (X_designator_option = 3 AND
         ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
        OR
         X_designator_option = 1)
       AND nvl(effectivity_control , 1) <> 4;

Line: 305

select msib.bom_item_type, msib.replenish_to_order_flag, bcb.optional
from bom_components_b bcb, mtl_system_items_b msib
where bcb.bill_sequence_id = bill_id
and msib.inventory_item_id = bcb.component_item_id
and msib.organization_id = bcb.pk2_value;

Line: 314

    SELECT BILL_SEQUENCE_ID, ALTERNATE_BOM_DESIGNATOR
    FROM BOM_BILL_OF_MATERIALS
    WHERE ORGANIZATION_ID = X_org_id
    AND ASSEMBLY_ITEM_ID = X_mfg_item_id
    AND nvl(effectivity_control, 1) <> 4 -- Bug 4210718
    AND Source_BILL_SEQUENCE_ID = BILL_SEQUENCE_ID; --R12

Line: 323

    SELECT BILL_SEQUENCE_ID, ALTERNATE_BOM_DESIGNATOR, assembly_item_id, organization_id, obj_name
    FROM BOM_BILL_OF_MATERIALS BOM_T
    WHERE ORGANIZATION_ID = X_org_id
    AND ASSEMBLY_ITEM_ID = X_mfg_item_id
    AND nvl(BOM_T.effectivity_control, 1) <> 4 -- Bug 4210718
    AND ((X_designator_option = 2 AND
          BOM_T.ALTERNATE_BOM_DESIGNATOR IS NULL)
         OR
         (X_designator_option = 3 AND
          BOM_T.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
         OR
         (X_designator_option = 1));

Line: 344

select msib.bom_item_type, msib.pick_components_flag into l_parent_BIT, l_PTO_flag
from mtl_system_items_b msib where inventory_item_id = X_eng_item_id and organization_id = X_org_id;

Line: 412

	-- Bug 3523263 Bug 4240131 inserted effectivity_control in table.
    INSERT INTO BOM_BILL_OF_MATERIALS(
      ASSEMBLY_ITEM_ID,
      ORGANIZATION_ID,
      ALTERNATE_BOM_DESIGNATOR,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      COMMON_ASSEMBLY_ITEM_ID,
      SPECIFIC_ASSEMBLY_COMMENT,
      PENDING_FROM_ECN,
      ATTRIBUTE_CATEGORY,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      ATTRIBUTE14,
      ATTRIBUTE15,
      ASSEMBLY_TYPE,
      BILL_SEQUENCE_ID,
      COMMON_BILL_SEQUENCE_ID,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE,
      COMMON_ORGANIZATION_ID,
      NEXT_EXPLODE_DATE,
      EFFECTIVITY_CONTROL,
      source_bill_sequence_id, --R12
      STRUCTURE_TYPE_ID, -- Bug 12555524
      pk1_value, --Bug 4707618
      pk2_value) --Bug 4707618
    SELECT
      X_mfg_item_id,
      ORGANIZATION_ID,
      BOM_T.ALTERNATE_BOM_DESIGNATOR,
      SYSDATE,
      to_number(Fnd_Profile.Value('USER_ID')),
      SYSDATE,
      to_number(Fnd_Profile.Value('USER_ID')),
      to_number(Fnd_Profile.Value('LOGIN_ID')),
      BOM_T.COMMON_ASSEMBLY_ITEM_ID,
      BOM_T.SPECIFIC_ASSEMBLY_COMMENT,
      BOM_T.PENDING_FROM_ECN,
      BOM_T.ATTRIBUTE_CATEGORY,
      BOM_T.ATTRIBUTE1,
      BOM_T.ATTRIBUTE2,
      BOM_T.ATTRIBUTE3,
      BOM_T.ATTRIBUTE4,
      BOM_T.ATTRIBUTE5,
      BOM_T.ATTRIBUTE6,
      BOM_T.ATTRIBUTE7,
      BOM_T.ATTRIBUTE8,
      BOM_T.ATTRIBUTE9,
      BOM_T.ATTRIBUTE10,
      BOM_T.ATTRIBUTE11,
      BOM_T.ATTRIBUTE12,
      BOM_T.ATTRIBUTE13,
      BOM_T.ATTRIBUTE14,
      BOM_T.ATTRIBUTE15,
      1,
      BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
      DECODE(BOM_T.COMMON_BILL_SEQUENCE_ID,BOM_T.BILL_SEQUENCE_ID,BOM_INVENTORY_COMPONENTS_S.CURRVAL,BOM_T.COMMON_BILL_SEQUENCE_ID),
      BOM_T.REQUEST_ID,
      BOM_T.PROGRAM_APPLICATION_ID,
      BOM_T.PROGRAM_ID,
      BOM_T.PROGRAM_UPDATE_DATE,
      BOM_T.COMMON_ORGANIZATION_ID,
      BOM_T.NEXT_EXPLODE_DATE,
      BOM_T.EFFECTIVITY_CONTROL,
      DECODE(BOM_T.COMMON_BILL_SEQUENCE_ID,BOM_T.BILL_SEQUENCE_ID,BOM_INVENTORY_COMPONENTS_S.CURRVAL,BOM_T.COMMON_BILL_SEQUENCE_ID),
      STRUCTURE_TYPE_ID, -- Bug 12555524
      X_mfg_item_id,
      ORGANIZATION_ID
    FROM BOM_BILL_OF_MATERIALS BOM_T
    WHERE ORGANIZATION_ID = X_org_id
    AND ASSEMBLY_ITEM_ID = X_eng_item_id
    AND ((X_designator_option = 2 AND
          BOM_T.ALTERNATE_BOM_DESIGNATOR IS NULL)
         OR
         (X_designator_option = 3 AND
          BOM_T.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
         OR
         (X_designator_option = 1))
    AND nvl(BOM_T.effectivity_control , 1) <> 4 -- Bug 4210718
    AND (X_GTIN_ST_TYPE_ID IS NULL or BOM_T.STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID);

Line: 547

      SELECT BILL_SEQUENCE_ID
      INTO X_from_bill_sequence_id
      FROM BOM_BILL_OF_MATERIALS
      WHERE ORGANIZATION_ID = X_org_id
      AND ASSEMBLY_ITEM_ID = X_eng_item_id
      AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(BOM1.ALTERNATE_BOM_DESIGNATOR,'NONE');

Line: 585

  UPDATE BOM_BILL_OF_MATERIALS BOM1
  SET BOM1.ALTERNATE_BOM_DESIGNATOR =NULL
  WHERE ORGANIZATION_ID = X_org_id
    AND ASSEMBLY_ITEM_ID = X_mfg_item_id
    AND (X_designator_option = 3 AND
          BOM1.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator);