APPS.BOM_OPEN_INTERFACE_UTL SQL Statements

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

Line: 63

/* Resolve the Bill sequence ids for updates and deletes */

   UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET(assembly_item_id, organization_id, alternate_bom_designator)
      = (SELECT assembly_item_id, organization_id , alternate_bom_designator
           FROM bom_structures_b BBM1
           WHERE BBM1.bill_sequence_id = BBMI.bill_sequence_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND bill_sequence_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          )
         AND exists (SELECT 'x'
       FROM bom_structures_b BBM2
       WHERE BBM2.bill_sequence_id = BBMI.bill_sequence_id);

Line: 86

/* Update Organization Code using Organization_id
this also needed if Organization_id is given and code is not given*/

   UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET organization_code = (SELECT organization_code
                                FROM MTL_PARAMETERS MP1
                                WHERE mp1.organization_id = BBMI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          )
         AND exists (SELECT 'x'
                     FROM MTL_PARAMETERS MP2
                     WHERE mp2.organization_id = BBMI.organization_id);

Line: 108

 /* Update Organization_ids if organization code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
         SET organization_id = (SELECT organization_id
                                  FROM MTL_PARAMETERS mp1
                             WHERE mp1.organization_code = BBMI.organization_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
         AND organization_id is null
         AND organization_code is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          );

Line: 126

    /* Update Assembly Item name */
    UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
    SET  item_number   = (SELECT concatenated_segments
                          FROM MTL_SYSTEM_ITEMS_KFV mvl1
                          WHERE mvl1.inventory_item_id = BBMI.assembly_item_id
                          and mvl1.organization_id = BBMI.organization_id)
    WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
    AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
    AND assembly_item_id is not null
    AND organization_id is not null
    AND
    (
    ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
    OR  ( p_batch_id = BBMI.batch_id )
    )
    AND exists (SELECT 'x'
                FROM MTL_SYSTEM_ITEMS MKFV
                WHERE MKFV.inventory_item_id = BBMI.assembly_item_id
                AND MKFV.organization_id = BBMI.organization_id);

Line: 148

    /* Update Assembly Item Id*/
    UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
    SET assembly_item_id = (SELECT inventory_item_id
                            FROM MTL_SYSTEM_ITEMS_KFV mvl1
                            WHERE mvl1.concatenated_segments = BBMI.item_number
                            AND  mvl1.organization_id = BBMI.organization_id)
    WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
    AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
    AND item_number is not null
    AND organization_id is not null
    AND assembly_item_id is null
    AND
    (
         ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
      OR ( p_batch_id = BBMI.batch_id )
     );

Line: 168

       UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
             transaction_type = upper(transaction_type)
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete,'NO_OP')
         AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 189

  UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET(common_assembly_item_id, common_organization_id)
       = (SELECT assembly_item_id, organization_id
           FROM bom_structures_b BBM1
           WHERE BBM1.bill_sequence_id = BBMI.common_bill_sequence_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
         AND common_bill_sequence_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          )
         AND exists (SELECT 'x'
       FROM bom_structures_b BBM2
       WHERE BBM2.bill_sequence_id = BBMI.common_bill_sequence_id);

Line: 211

    UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET common_org_code = (SELECT organization_code
                                 FROM MTL_PARAMETERS mp1
               WHERE mp1.organization_id = BBMI.common_organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
         AND common_organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          )
         AND exists (SELECT 'x'
                      FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BBMI.common_organization_id);

Line: 230

 /* Update Organization_ids if organization_code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET common_organization_id = (SELECT organization_id
                                       FROM MTL_PARAMETERS MP1
                                       WHERE mp1.organization_code = BBMI.common_org_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
       AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
       AND common_organization_id is null
       AND common_org_code is not null
       AND
        (
            ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
        OR  ( p_batch_id = BBMI.batch_id )
        );

Line: 248

/* Update Assembly Item name */

       UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET common_item_number   = (SELECT concatenated_segments
                                   FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                   WHERE mvl1.inventory_item_id = BBMI.common_assembly_item_id
                                   AND mvl1.organization_id = BBMI.common_organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
         AND common_assembly_item_id is not null
         AND common_organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          )
         AND exists (SELECT 'x'
                     FROM MTL_SYSTEM_ITEMS mvl2
                     WHERE mvl2.inventory_item_id = BBMI.common_assembly_item_id
                     AND mvl2.organization_id = BBMI.common_organization_id);

Line: 269

/* Update the delete_group_name from bom_interface_delete_groups */
   stmt_num := 9.5;

Line: 271

   UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
                           = (SELECT DELETE_GROUP_NAME, DESCRIPTION
                             FROM bom_interface_delete_groups
                             Where upper(entity_name) = 'BOM_BILL_OF_MTLS_INTERFACE'
                             And rownum = 1)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete)
         AND organization_id is not null
         AND delete_group_name is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          )
         AND exists (SELECT 'x'
                     FROM bom_interface_delete_groups
                     Where upper(entity_name) = 'BOM_BILL_OF_MTLS_INTERFACE'
                     );

Line: 292

/* Update Bill Sequence Id when there are IDs available */

   UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET bill_sequence_id  = (SELECT bill_sequence_id
                                FROM bom_structures_b bom
                                WHERE bom.assembly_item_id = BBMI.assembly_item_id
                                AND   bom.organization_id = BBMI.organization_id
                                AND   NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BBMI.alternate_bom_designator,FND_API.G_MISS_CHAR))
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          )
         AND exists (SELECT 1
                       FROM bom_structures_b bom1
                      WHERE bom1.assembly_item_id = BBMI.assembly_item_id
                      AND   bom1.organization_id = BBMI.organization_id
                      AND   NVL(bom1.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BBMI.alternate_bom_designator,FND_API.G_MISS_CHAR));

Line: 319

   UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET bill_sequence_id  = (SELECT bill_sequence_id
                             FROM  bom_bill_of_materials bom, mtl_system_items_vl mvll
                             WHERE mvll.concatenated_segments = BBMI.item_number
                             AND   mvll.organization_id = BBMI.organization_id
                             AND   bom.assembly_item_id = mvll.inventory_item_id
                             AND   bom.organization_id = mvll.organization_id
                             AND   NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BBMI.alternate_bom_designator,FND_API.G_MISS_CHAR))
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND item_number is not null
         AND organization_id is not null
         AND bill_sequence_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
          OR  ( p_batch_id = BBMI.batch_id )
          );

Line: 341

/* Update structure type name to the internal name from the display name  */
  UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
     SET structure_type_name = (SELECT structure_type_name
                                FROM  BOM_STRUCTURE_TYPES_VL bstv
                                WHERE decode(BBMI.structure_type_name, null, to_char(bstv.structure_type_id), bstv.display_name)
                                 = decode(BBMI.structure_type_name, null, to_char(BBMI.structure_type_id), BBMI.structure_type_name))
     WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
       AND (structure_type_name is not null OR structure_type_id is not null)
       AND upper(transaction_type) in (G_Create,G_Update,G_NoOp)
       AND
        (
            ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
        OR  ( p_batch_id = BBMI.batch_id )
        )
       AND exists (SELECT null
                   FROM  BOM_STRUCTURE_TYPES_VL bstv
                   WHERE decode(BBMI.structure_type_name, null, to_char(bstv.structure_type_id), bstv.display_name) =
                     decode(BBMI.structure_type_name, null, to_char(BBMI.structure_type_id), BBMI.structure_type_name));

Line: 363

                 INSERT into mtl_item_revisions_interface
                     (ITEM_NUMBER,
                      ORGANIZATION_CODE,
                      REVISION,
                      EFFECTIVITY_DATE,
                      IMPLEMENTATION_DATE,
                      PROCESS_FLAG,
                      TRANSACTION_TYPE,
                      LAST_UPDATE_DATE,
                      LAST_UPDATED_BY,
                      CREATION_DATE,
                      CREATED_BY,
                      LAST_UPDATE_LOGIN,
                      REQUEST_ID,
                      PROGRAM_APPLICATION_ID,
                      PROGRAM_ID,
                      PROGRAM_UPDATE_DATE,
                      SET_PROCESS_ID)
                    select
                      item_number,
                       Organization_Code,
                       REVISION,
                       sysdate,
                       sysdate,
                       1,
                       G_Create,
                       NVL(LAST_UPDATE_DATE, SYSDATE),
                       NVL(LAST_UPDATED_BY, user_id),
                       NVL(CREATION_DATE,SYSDATE),
                       NVL(CREATED_BY, user_id),
                       NVL(LAST_UPDATE_LOGIN, user_id),
                       NVL(REQUEST_ID, req_id),
                       NVL(PROGRAM_APPLICATION_ID, prog_appid),
                       NVL(PROGRAM_ID, prog_id),
                       NVL(PROGRAM_UPDATE_DATE, sysdate),
                       NVL(BATCH_ID,0) -- Replace NULL batch id with 0 - table level default value for set_process_id
                     FROM BOM_BILL_OF_MTLS_INTERFACE
                     WHERE process_flag = 1
                     AND transaction_type = G_Create
                     AND (all_org = 1
                          OR
                          (all_org = 2 AND organization_id = org_id))
                     AND revision is not null
                     AND
                      (
                          ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
                      OR  ( p_batch_id = batch_id )
                      );

Line: 415

/* Update the interface records with process_flag 3 and insert into
MTL_INTERFACE_ERRORS if Item number or Organization_code  is missing*/

 l_sysdate        :=  SYSDATE;

Line: 426

   INSERT INTO MTL_INTERFACE_ERRORS
   (
  TRANSACTION_ID,
  UNIQUE_ID,
  ORGANIZATION_ID,
  COLUMN_NAME,
  TABLE_NAME,
  MESSAGE_NAME,
  ERROR_MESSAGE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY,
  LAST_UPDATE_LOGIN,
  REQUEST_ID,
  PROGRAM_APPLICATION_ID,
  PROGRAM_ID,
  PROGRAM_UPDATE_DATE
   )
  Select
  BBMI.transaction_id,
  MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
  Null,
  null,
  'BOM_BILL_OF_MTLS_INTERFACE',
  decode ( BBMI.Organization_code, null, msg_name1,msg_name2),
  decode ( BBMI.Organization_code, null, msg_text1,msg_text2),
        NVL(LAST_UPDATE_DATE, SYSDATE),
        NVL(LAST_UPDATED_BY, user_id),
        NVL(CREATION_DATE,SYSDATE),
        NVL(CREATED_BY, user_id),
        NVL(LAST_UPDATE_LOGIN, user_id),
         req_id,
        NVL(PROGRAM_APPLICATION_ID, prog_appid),
        NVL(PROGRAM_ID, prog_id),
        NVL(PROGRAM_UPDATE_DATE, sysdate)

    from BOM_BILL_OF_MTLS_INTERFACE BBMI
   where (organization_code is null or item_number is null)
  and transaction_id is not null
  and process_flag =1
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  and
   (
       ( (p_batch_id is null) and (bbmi.batch_id is null) )
   or  ( p_batch_id = bbmi.batch_id )
   );

Line: 475

  Update BOM_BILL_OF_MTLS_INTERFACE BBMI
  set process_flag = 3
  where (item_number is null or Organization_code is null)
  and transaction_id is not null
  and process_flag =1
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  and
   (
       ( (p_batch_id is null) and (BBMI.batch_id is null) )
   or  ( p_batch_id = BBMI.batch_id )
   ) ;

Line: 540

/* Resolve the Component_sequence_ids for updates and deletes */

   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET(bill_sequence_id,  component_item_id, effectivity_date,
         operation_seq_num,  from_end_item_unit_number)
       = (SELECT bill_sequence_id,  component_item_id,
    effectivity_date, operation_seq_num,  from_end_item_unit_number
           FROM bom_components_b BIC1
           WHERE BIC1.component_sequence_id = BICI.component_sequence_id )
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND component_sequence_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND exists (SELECT 'x'
           FROM bom_components_b BIC2
           WHERE BIC2.component_sequence_id = BICI.component_sequence_id );

Line: 564

/* Resolve the Bill sequence ids for updates and deletes */

   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET(assembly_item_id, organization_id, alternate_bom_designator)
       = (SELECT assembly_item_id, organization_id , alternate_bom_designator
           FROM bom_structures_b BBM1
           WHERE BBM1.bill_sequence_id = BICI.bill_sequence_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND bill_sequence_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND exists (SELECT 'x'
       FROM bom_structures_b BBM2
       WHERE BBM2.bill_sequence_id =BICI.bill_sequence_id);

Line: 586

/* Update Organization Code using Organization_id
this also needed if Organization_id is given and code is not given*/

   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
   SET organization_code = (SELECT organization_code
                            FROM MTL_PARAMETERS mp1
                            WHERE mp1.organization_id = BICI.organization_id)
   WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
   AND change_notice is null --added for bug 9673701
   AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
   AND organization_id is not null
   AND
    (
        ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
    OR  ( p_batch_id = BICI.batch_id )
    )
   AND exists (SELECT 'x'
               FROM MTL_PARAMETERS mp2
               WHERE mp2.organization_id = BICI.organization_id);

Line: 609

 /* Update Organization_ids if organization_code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
      SET organization_id = (SELECT organization_id
                             FROM MTL_PARAMETERS mp1
                             WHERE mp1.organization_code = BICI.organization_code)
      WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
      AND change_notice is null --added for bug 9673701
      AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
      AND organization_id is null
      AND organization_code is not null
      AND
       (
           ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
       OR  ( p_batch_id = BICI.batch_id )
       );

Line: 630

/* Update Assembly Item name */

   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET  assembly_item_number  = (SELECT concatenated_segments
                                     FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                     WHERE mvl1.inventory_item_id = BICI.assembly_item_id
                                     AND mvl1.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items mvl12
                      WHERE mvl12.inventory_item_id = BICI.assembly_item_id
                      AND mvl12.organization_id = BICI.organization_id);

Line: 653

   /* Update the Assembly Item Id */

       UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET  assembly_item_id  = (SELECT inventory_item_id
                                 FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                 WHERE mvl1.concatenated_segments = BICI.assembly_item_number
                                 AND mvl1.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND assembly_item_number is not null
         AND organization_id is not null
         AND assembly_item_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          );

Line: 674

  /* Update Component Item name */

   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET  component_item_number   = (SELECT CONCATENATED_SEGMENTS
                                       FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                       WHERE mvl1.inventory_item_id = BICI.component_item_id
                                       AND mvl1.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND COMPONENT_ITEM_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items mvl12
                      WHERE mvl12.inventory_item_id = BICI.component_item_id
                      AND mvl12.organization_id = BICI.organization_id);

Line: 697

/* Update the component_item_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET component_item_id  = (SELECT inventory_item_id
                                 FROM  mtl_system_items_kfv mvll
                                 WHERE mvll.concatenated_segments = BICI.component_item_number
                                 AND   mvll.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND component_item_number is not null
         AND organization_id is not null
         AND component_item_id IS null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          );

Line: 719

       UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
             transaction_type = upper(transaction_type)
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          );

Line: 737

/* Update Supply_locator_name */

 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET  location_name  = (SELECT concatenated_segments
                             FROM MTL_ITEM_LOCATIONS_KFV MIL1
                             WHERE MIL1.inventory_location_id = BICI.supply_locator_id
           and MIL1.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND supply_locator_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_ITEM_LOCATIONS mil2
                       WHERE mil2.INVENTORY_LOCATION_ID = BICI.supply_locator_id
      and mil2.organization_id = BICI.organization_id);

Line: 760

/* Update the delete_group_name from bom_interface_delete_groups */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
                           = (SELECT DELETE_GROUP_NAME, DESCRIPTION
                             FROM bom_interface_delete_groups
                             Where upper(entity_name) = 'BOM_INVENTORY_COMPS_INTERFACE'
                             And rownum = 1)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Delete)
         AND organization_id is not null
         AND delete_group_name is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND exists (SELECT 'x'
                     FROM bom_interface_delete_groups
                     Where upper(entity_name) = 'BOM_INVENTORY_COMPS_INTERFACE'
                     );

Line: 784

/* Update the bill_sequence_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET bill_sequence_id  =  (SELECT bill_sequence_id
                                 FROM  bom_structures_b bom
                                 WHERE bom.assembly_item_id = BICI.assembly_item_id
                                 AND   bom.organization_id = BICI.organization_id
                                 AND   NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BICI.alternate_bom_designator,FND_API.G_MISS_CHAR))
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND exists (SELECT 'x'
                     FROM bom_structures_b bsb
                     WHERE bsb.assembly_item_id = BICI.assembly_item_id
                     AND bsb.organization_id = BICI.organization_id
                     AND NVL(BSB.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BICI.alternate_bom_designator,FND_API.G_MISS_CHAR)
                     );

Line: 811

/* Update the component_sequence_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET COMPONENT_SEQUENCE_ID
                           = (SELECT COMPONENT_SEQUENCE_ID
                             FROM bom_components_b BIC
                             Where BIC.bill_sequence_id = BICI.bill_Sequence_id
                             And BIC.component_item_id = BICI.component_item_id
                             AND BIC.operation_seq_num = BICI.operation_seq_num
                             AND BIC.effectivity_date = BICI.effectivity_date)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Update, G_Delete)
         AND COMPONENT_SEQUENCE_ID is null
         AND bill_sequence_id is not null
         AND component_item_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          );

Line: 835

   UPDATE BOM_INVENTORY_COMPS_INTERFACE
       SET EFFECTIVITY_DATE = SYSDATE
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(Transaction_Type) = G_Create
         AND Effectivity_Date IS NULL
         AND
          (
              ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
          OR  ( p_batch_id = batch_id )
          );

Line: 848

 /* INSERTS ONLY - Load rows from component interface into sub comp interface*/
   INSERT into bom_sub_comps_interface (
        SUBSTITUTE_COMPONENT_ID,
        SUBSTITUTE_COMP_NUMBER,
        ORGANIZATION_ID,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        REQUEST_ID,
        PROGRAM_APPLICATION_ID,
        PROGRAM_ID,
        PROGRAM_UPDATE_DATE,
        COMPONENT_SEQUENCE_ID,
        PROCESS_FLAG,
        TRANSACTION_TYPE,
        SUBSTITUTE_ITEM_QUANTITY,
  BILL_SEQUENCE_ID,
        ASSEMBLY_ITEM_ID,
        ALTERNATE_BOM_DESIGNATOR,
        COMPONENT_ITEM_ID,
        OPERATION_SEQ_NUM,
        EFFECTIVITY_DATE,
        ORGANIZATION_CODE,
        COMPONENT_ITEM_NUMBER,
        ASSEMBLY_ITEM_NUMBER,
        FROM_END_ITEM_UNIT_NUMBER,
        BATCH_ID)
      SELECT
             SUBSTITUTE_COMP_ID,
             SUBSTITUTE_COMP_NUMBER,
             ORGANIZATION_ID,
             NVL(LAST_UPDATE_DATE, SYSDATE),
             NVL(LAST_UPDATED_BY, user_id),
             NVL(CREATION_DATE,SYSDATE),
             NVL(CREATED_BY, user_id),
             NVL(LAST_UPDATE_LOGIN, user_id),
             NVL(REQUEST_ID, req_id),
             NVL(PROGRAM_APPLICATION_ID, prog_appid),
             NVL(PROGRAM_ID, prog_id),
             NVL(PROGRAM_UPDATE_DATE, sysdate),
             COMPONENT_SEQUENCE_ID,
             1,
             G_Create,
             COMPONENT_QUANTITY,
        BILL_SEQUENCE_ID,
             ASSEMBLY_ITEM_ID,
             ALTERNATE_BOM_DESIGNATOR,
             COMPONENT_ITEM_ID,
             OPERATION_SEQ_NUM,
             EFFECTIVITY_DATE,
             ORGANIZATION_CODE,
             COMPONENT_ITEM_NUMBER,
             ASSEMBLY_ITEM_NUMBER,
             FROM_END_ITEM_UNIT_NUMBER,
             BATCH_ID
        FROM bom_inventory_comps_interface
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND transaction_type = G_Create
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND (substitute_comp_id is not null
              OR
              substitute_comp_number is not null)
         AND
          (
              ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
          OR  ( p_batch_id = batch_id )
          );

Line: 924

/* INSERTS ONLY - Load rows from component interface into ref desgs interface*/

   INSERT INTO bom_ref_desgs_interface (
        COMPONENT_REFERENCE_DESIGNATOR,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        REQUEST_ID,
        PROGRAM_APPLICATION_ID,
        PROGRAM_ID,
        PROGRAM_UPDATE_DATE,
        COMPONENT_SEQUENCE_ID,
        TRANSACTION_TYPE,
        PROCESS_FLAG,
  BILL_SEQUENCE_ID,
        ASSEMBLY_ITEM_ID,
        ALTERNATE_BOM_DESIGNATOR,
        ORGANIZATION_ID,
        COMPONENT_ITEM_ID,
        ASSEMBLY_ITEM_NUMBER,
        COMPONENT_ITEM_NUMBER,
        ORGANIZATION_CODE,
        EFFECTIVITY_DATE,
        OPERATION_SEQ_NUM,
        FROM_END_ITEM_UNIT_NUMBER,
        BATCH_ID)
   SELECT
        REFERENCE_DESIGNATOR,
        NVL(LAST_UPDATE_DATE, SYSDATE),
        NVL(LAST_UPDATED_BY, user_id),
        NVL(CREATION_DATE,SYSDATE),
        NVL(CREATED_BY, user_id),
        NVL(LAST_UPDATE_LOGIN, user_id),
        NVL(REQUEST_ID, req_id),
        NVL(PROGRAM_APPLICATION_ID, prog_appid),
        NVL(PROGRAM_ID, prog_id),
        NVL(PROGRAM_UPDATE_DATE, sysdate),
        COMPONENT_SEQUENCE_ID,
        G_Create,
        1,
  BILL_SEQUENCE_ID,
        ASSEMBLY_ITEM_ID,
        ALTERNATE_BOM_DESIGNATOR,
        ORGANIZATION_ID,
        COMPONENT_ITEM_ID,
        ASSEMBLY_ITEM_NUMBER,
        COMPONENT_ITEM_NUMBER,
        ORGANIZATION_CODE,
        EFFECTIVITY_DATE,
        OPERATION_SEQ_NUM,
        FROM_END_ITEM_UNIT_NUMBER,
        BATCH_ID
    FROM bom_inventory_comps_interface
   WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
     AND change_notice is null --added for bug 9673701
     AND transaction_type = G_Create
     AND (all_org = 1
          OR
          (all_org = 2 AND organization_id = org_id))
     AND reference_designator is not null
     AND
      (
          ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
      OR  ( p_batch_id = batch_id )
      );

Line: 995

/* Update the interface records with process_flag 3 and insert into
mtl_interface_errors if Item_number or Organization_code  is missing*/

 l_sysdate        :=  SYSDATE;

Line: 1005

   INSERT INTO mtl_interface_errors
   (
  TRANSACTION_ID,
  UNIQUE_ID,
  ORGANIZATION_ID,
  COLUMN_NAME,
  TABLE_NAME,
  MESSAGE_NAME,
  ERROR_MESSAGE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY,
  LAST_UPDATE_LOGIN,
  REQUEST_ID,
  PROGRAM_APPLICATION_ID,
  PROGRAM_ID,
  PROGRAM_UPDATE_DATE
   )
  Select
  BICI.transaction_id,
  MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
  Null,
  null,
  'BOM_INVENTORY_COMPS_INTERFACE',
  decode ( BICI.Organization_code, null, msg_name1,msg_name2),
  decode ( BICI.Organization_code, null, msg_text1,msg_text2),
        NVL(LAST_UPDATE_DATE, SYSDATE),
        NVL(LAST_UPDATED_BY, user_id),
        NVL(CREATION_DATE,SYSDATE),
        NVL(CREATED_BY, user_id),
        NVL(LAST_UPDATE_LOGIN, user_id),
        NVL(REQUEST_ID, req_id),
        NVL(PROGRAM_APPLICATION_ID, prog_appid),
        NVL(PROGRAM_ID, prog_id),
        NVL(PROGRAM_UPDATE_DATE, sysdate)

   from BOM_INVENTORY_COMPS_INTERFACE BICI
   where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
  and transaction_id is not null
  and process_flag =1
  AND change_notice is null --added for bug 9673701
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  AND
   (
       ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
   OR  ( p_batch_id = BICI.batch_id )
   );

Line: 1056

  Update BOM_INVENTORY_COMPS_INTERFACE
  set process_flag = 3
  where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
  and transaction_id is not null
  and process_flag =1
  AND change_notice is null --added for bug 9673701
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  AND
   (
       ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
   OR  ( p_batch_id = batch_id )
   );

Line: 1122

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET(bill_sequence_id,  component_item_id, effectivity_date,
         operation_seq_num,  from_end_item_unit_number)
       = (SELECT bill_sequence_id,  component_item_id,
    effectivity_date, operation_seq_num,  from_end_item_unit_number
           FROM bom_components_b BIC1
           WHERE BIC1.component_sequence_id = BRDI.component_sequence_id )
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND COMPONENT_SEQUENCE_ID is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
         AND exists (SELECT 'x'
           FROM bom_components_b BIC2
           WHERE BIC2.COMPONENT_SEQUENCE_ID = BRDI.COMPONENT_SEQUENCE_ID );

Line: 1144

/* Resolve the Bill sequence ids for updates and deletes */

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET(assembly_item_id, organization_id, ALTERNATE_BOM_DESIGNATOR)
       = (SELECT assembly_item_id, organization_id , alternate_bom_designator
           FROM bom_structures_b BBM1
           WHERE BBM1.bill_sequence_id = BRDI.bill_sequence_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND bill_sequence_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
         AND exists (SELECT 'x'
       FROM bom_structures_b BBM2
       WHERE BBM2.bill_sequence_id = BRDI.bill_sequence_id);

Line: 1166

/* Update Organization Code using Organization_id
this also needed if Organization_id is given and code is not given*/

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET organization_code = (SELECT organization_code
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_id = BRDI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BRDI.organization_id);

Line: 1189

 /* Update Organization_ids if organization_code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE BOM_REF_DESGS_INTERFACE BRDI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BRDI.organization_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is null
         AND organization_code is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 1210

/* Update Assembly Item name */

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET  ASSEMBLY_ITEM_NUMBER  = (SELECT CONCATENATED_SEGMENTS
                                     FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                     WHERE mvl1.inventory_item_id = BRDI.assembly_item_id
                                     and mvl1.organization_id = BRDI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
          AND exists (select 'x'
                      FROM mtl_system_items MKFV
                      WHERE MKFV.inventory_item_id = BRDI.assembly_item_id
                      AND MKFV.organization_id = BRDI.organization_id );

Line: 1234

/* Update Component Item name */

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET  COMPONENT_ITEM_NUMBER   = (SELECT CONCATENATED_SEGMENTS
                             FROM MTL_SYSTEM_ITEMS_KFV mvl1
                             WHERE mvl1.inventory_item_id = BRDI.component_item_id
                             AND mvl1.organization_id = BRDI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND COMPONENT_ITEM_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
         AND exists (SELECT 'x'
                     FROM mtl_system_items MKFV
                     WHERE MKFV.inventory_item_id = BRDI.component_item_id
                     AND MKFV.organization_id = BRDI.organization_id);

Line: 1260

       UPDATE BOM_REF_DESGS_INTERFACE BRDI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
             transaction_type = upper(transaction_type)
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 1279

/* Update the interface records with process_flag 3 and insert into
mtl_interface_errors if Item_number or Organization_code  is missing*/

 l_sysdate        :=  SYSDATE;

Line: 1289

   INSERT INTO mtl_interface_errors
   (
  TRANSACTION_ID,
  UNIQUE_ID,
  ORGANIZATION_ID,
  COLUMN_NAME,
  TABLE_NAME,
  MESSAGE_NAME,
  ERROR_MESSAGE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY,
  LAST_UPDATE_LOGIN,
  REQUEST_ID,
  PROGRAM_APPLICATION_ID,
  PROGRAM_ID,
  PROGRAM_UPDATE_DATE
   )
  Select
  BRDI.transaction_id,
  MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
  Null,
  null,
  'BOM_REF_DESGS_INTERFACE',
  decode ( BRDI.Organization_code, null, msg_name1,msg_name2),
  decode ( BRDI.Organization_code, null, msg_text1,msg_text2),
        NVL(LAST_UPDATE_DATE, SYSDATE),
        NVL(LAST_UPDATED_BY, user_id),
        NVL(CREATION_DATE,SYSDATE),
        NVL(CREATED_BY, user_id),
        NVL(LAST_UPDATE_LOGIN, user_id),
        NVL(REQUEST_ID, req_id),
        NVL(PROGRAM_APPLICATION_ID, prog_appid),
        NVL(PROGRAM_ID, prog_id),
        NVL(PROGRAM_UPDATE_DATE, sysdate)
    from BOM_REF_DESGS_INTERFACE BRDI
   where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
  and transaction_id is not null
  and process_flag =1
  AND change_notice is null --added for bug 9673701
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  AND
   (
       ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
   OR  ( p_batch_id = BRDI.batch_id )
   );

Line: 1337

  Update BOM_REF_DESGS_INTERFACE
  set process_flag = 3
  where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
  and transaction_id is not null
  and process_flag =1
  AND change_notice is null --added for bug 9673701
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  AND
   (
       ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
   OR  ( p_batch_id = batch_id )
   );

Line: 1401

   UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET(bill_sequence_id,  component_item_id, effectivity_date,
         operation_seq_num,  from_end_item_unit_number)
       = (select bill_sequence_id,  component_item_id,
    EFFECTIVITY_DATE, OPERATION_SEQ_NUM,  FROM_END_ITEM_UNIT_NUMBER
           FROM bom_components_b BIC1
           WHERE BIC1.COMPONENT_SEQUENCE_ID = BSCI.component_sequence_id )
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND COMPONENT_SEQUENCE_ID is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
         AND exists (SELECT 'x'
           FROM bom_components_b BIC2
           WHERE BIC2.COMPONENT_SEQUENCE_ID = BSCI.COMPONENT_SEQUENCE_ID );

Line: 1423

/* Resolve the Bill sequence ids for updates and deletes */

   UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET(assembly_item_id, organization_id, alternate_bom_designator)
       = (SELECT assembly_item_id, organization_id , alternate_bom_designator
           FROM bom_structures_b BBM1
           WHERE BBM1.bill_sequence_id = BSCI.bill_sequence_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND bill_sequence_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
         AND exists (SELECT 'x'
       FROM bom_structures_b BBM2
       WHERE BBM2.bill_sequence_id = BSCI.bill_sequence_id);

Line: 1445

/* Update Organization Code using Organization_id
this also needed if Organization_id is given and code is not given*/

   UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET organization_code = (SELECT organization_code
                                  FROM MTL_PARAMETERS mp1
                             WHERE mp1.organization_id = BSCI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BSCI.organization_id);

Line: 1468

 /* Update Organization_ids if organization_code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE BOM_SUB_COMPS_INTERFACE BSCI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BSCI.organization_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is null
         AND organization_code is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );

Line: 1489

/* Update Assembly Item name */

   UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET  ASSEMBLY_ITEM_NUMBER  = (SELECT CONCATENATED_SEGMENTS
                                     FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                     WHERE mvl1.inventory_item_id = BSCI.assembly_item_id
                                     AND mvl1.organization_id = BSCI.organization_id)
         WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items MKFV
                      WHERE MKFV.inventory_item_id = BSCI.assembly_item_id
                      AND MKFV.organization_id = BSCI.organization_id);

Line: 1513

/* Update Component Item name */

   UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET  COMPONENT_ITEM_NUMBER   = (SELECT CONCATENATED_SEGMENTS
                                       FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                       WHERE mvl1.inventory_item_id = BSCI.component_item_id
                                       AND mvl1.organization_id = BSCI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND COMPONENT_ITEM_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items MKFV
                      WHERE MKFV.inventory_item_id = BSCI.component_item_id
                      AND MKFV.organization_id = BSCI.organization_id);

Line: 1537

/* Update Substitute Component name if Id is given */

   UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET  SUBSTITUTE_COMP_NUMBER = (SELECT CONCATENATED_SEGMENTS
                             FROM MTL_SYSTEM_ITEMS_KFV mvl1
                             WHERE mvl1.inventory_item_id = BSCI.SUBSTITUTE_COMPONENT_ID
                             and mvl1.organization_id = BSCI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND SUBSTITUTE_COMPONENT_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items MKFV
                      WHERE MKFV.inventory_item_id = BSCI.substitute_component_id
                      AND MKFV.organization_id = BSCI.organization_id);

Line: 1562

/* Update new Substitute Component name if Id is given */

   UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET  NEW_SUB_COMP_NUMBER = (SELECT CONCATENATED_SEGMENTS
                             FROM MTL_SYSTEM_ITEMS_KFV mvl1
                             WHERE mvl1.inventory_item_id = BSCI.NEW_SUB_COMP_ID
                             and mvl1.organization_id = BSCI.organization_id)
       WHERE  (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND NEW_SUB_COMP_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items MKFV
                      WHERE MKFV.inventory_item_id = BSCI.new_sub_comp_id
                      AND MKFV.organization_id = BSCI.organization_id);

Line: 1587

       UPDATE BOM_SUB_COMPS_INTERFACE BSCI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
             transaction_type = upper(transaction_type)
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND change_notice is null --added for bug 9673701
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );

Line: 1605

/* Update the interface records with process_flag 3 and insert into
mtl_interface_errors if Item_number or Organization_code  is missing*/

 l_sysdate        :=  SYSDATE;

Line: 1615

   INSERT INTO mtl_interface_errors
   (
  TRANSACTION_ID,
  UNIQUE_ID,
  ORGANIZATION_ID,
  COLUMN_NAME,
  TABLE_NAME,
  MESSAGE_NAME,
  ERROR_MESSAGE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY,
  LAST_UPDATE_LOGIN,
  REQUEST_ID,
  PROGRAM_APPLICATION_ID,
  PROGRAM_ID,
  PROGRAM_UPDATE_DATE
   )
  Select
  BSCI.transaction_id,
  MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
  Null,
  null,
  'BOM_INVENTORY_COMPS_INTERFACE',
  decode ( BSCI.Organization_code, null, msg_name1,msg_name2),
  decode ( BSCI.Organization_code, null, msg_text1,msg_text2),
        NVL(LAST_UPDATE_DATE, SYSDATE),
        NVL(LAST_UPDATED_BY, user_id),
        NVL(CREATION_DATE,SYSDATE),
        NVL(CREATED_BY, user_id),
        NVL(LAST_UPDATE_LOGIN, user_id),
        NVL(REQUEST_ID, req_id),
        NVL(PROGRAM_APPLICATION_ID, prog_appid),
        NVL(PROGRAM_ID, prog_id),
        NVL(PROGRAM_UPDATE_DATE, sysdate)

    from BOM_SUB_COMPS_INTERFACE BSCI
   where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
  and transaction_id is not null
  and process_flag =1
  AND change_notice is null --added for bug 9673701
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  AND
   (
       ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
   OR  ( p_batch_id = BSCI.batch_id )
   );

Line: 1664

  Update BOM_SUB_COMPS_INTERFACE
  set process_flag = 3
  where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
  and transaction_id is not null
  and process_flag =1
  AND change_notice is null --added for bug 9673701
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  AND
   (
       ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
   OR  ( p_batch_id = batch_id )
   );

Line: 1729

   UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
       SET(bill_sequence_id,  component_item_id, effectivity_date,
         operation_seq_num,  from_end_item_unit_number)
       = (SELECT bill_sequence_id,  component_item_id,
    effectivity_date, operation_seq_num,  from_end_item_unit_number
           FROM bom_components_b BIC1
           WHERE BIC1.component_sequence_id = BCOI.component_sequence_id )
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND COMPONENT_SEQUENCE_ID is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
          OR  ( p_batch_id = BCOI.batch_id )
          )
         AND exists (SELECT 'x'
           FROM BOM_INVENTORY_COMPONENTS BIC2
           WHERE BIC2.COMPONENT_SEQUENCE_ID = BCOI.COMPONENT_SEQUENCE_ID );

Line: 1750

/* Resolve the Bill sequence ids for updates and deletes */

   UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
       SET(assembly_item_id, organization_id, alternate_bom_designator)
       = (SELECT assembly_item_id, organization_id , alternate_bom_designator
           FROM bom_structures_b BBM1
           WHERE BBM1.bill_sequence_id = BCOI.bill_sequence_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND bill_sequence_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
          OR  ( p_batch_id = BCOI.batch_id )
          )
         AND exists (SELECT 'x'
       FROM bom_structures_b BBM2
       WHERE BBM2.bill_sequence_id = BCOI.bill_sequence_id);

Line: 1771

/* Update Organization Code using Organization_id
this also needed if orgnaization_id is given and code is not given*/

   UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
       SET organization_code = (SELECT organization_code
                                  FROM MTL_PARAMETERS mp1
                             WHERE mp1.organization_id = BCOI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
          OR  ( p_batch_id = BCOI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BCOI.organization_id);

Line: 1793

 /* Update Organization_ids if organization_code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BCOI.organization_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is null
         AND organization_code is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
          OR  ( p_batch_id = BCOI.batch_id )
          );

Line: 1813

/* Update Assembly Item name */

   UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
       SET  ASSEMBLY_ITEM_NUMBER  = (SELECT CONCATENATED_SEGMENTS
                             FROM MTL_SYSTEM_ITEMS_KFV mvl1
                             WHERE mvl1.inventory_item_id = BCOI.assembly_item_id
           and mvl1.organization_id = BCOI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
          OR  ( p_batch_id = BCOI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items MKFV
                      WHERE MKFV.inventory_item_id = BCOI.assembly_item_id
                      AND MKFV.organization_id = BCOI.organization_id);

Line: 1836

/* Update Component Item name */

   UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
       SET  COMPONENT_ITEM_NUMBER   = (SELECT CONCATENATED_SEGMENTS
                             FROM MTL_SYSTEM_ITEMS_KFV mvl1
                             WHERE mvl1.inventory_item_id = BCOI.component_item_id
           and mvl1.organization_id = BCOI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND COMPONENT_ITEM_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
          OR  ( p_batch_id = BCOI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items MKFV
                      WHERE MKFV.inventory_item_id = BCOI.component_item_id
                      AND MKFV.organization_id = BCOI.organization_id);

Line: 1861

       UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
             transaction_type = upper(transaction_type)
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND
          (
              ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
          OR  ( p_batch_id = BCOI.batch_id )
          );

Line: 1879

/* Update the interface records with process_flag 3 and insert into
mtl_interface_errors if Item_number or Organization_code  is missing*/

 l_sysdate        :=  SYSDATE;

Line: 1889

   INSERT INTO mtl_interface_errors
   (
  TRANSACTION_ID,
  UNIQUE_ID,
  ORGANIZATION_ID,
  COLUMN_NAME,
  TABLE_NAME,
  MESSAGE_NAME,
  ERROR_MESSAGE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY,
  LAST_UPDATE_LOGIN,
  REQUEST_ID,
  PROGRAM_APPLICATION_ID,
  PROGRAM_ID,
  PROGRAM_UPDATE_DATE
   )
  Select
  BCOI.transaction_id,
  MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
  Null,
  null,
  'BOM_COMPONENT_OPS_INTERFACE',
  decode ( BCOI.Organization_code, null, msg_name1,msg_name2),
  decode ( BCOI.Organization_code, null, msg_text1,msg_text2),
        NVL(LAST_UPDATE_DATE, SYSDATE),
        NVL(LAST_UPDATED_BY, user_id),
        NVL(CREATION_DATE,SYSDATE),
        NVL(CREATED_BY, user_id),
        NVL(LAST_UPDATE_LOGIN, user_id),
        req_id,
  NVL(PROGRAM_APPLICATION_ID, prog_appid),
  NVL(PROGRAM_ID, prog_id),
  NVL(PROGRAM_UPDATE_DATE, sysdate)
    from BOM_COMPONENT_OPS_INTERFACE BCOI
   where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
  and transaction_id is not null
  and process_flag =1
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  AND
   (
       ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
   OR  ( p_batch_id = BCOI.batch_id )
   );

Line: 1936

  Update BOM_COMPONENT_OPS_INTERFACE
  set process_flag = 3
  where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
  and transaction_id is not null
  and process_flag =1
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  AND
   (
       ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
   OR  ( p_batch_id = batch_id )
   );

Line: 1985

/* Update Organization Code using Organization_id
this also needed if Organization_id is given and code is not given*/

   UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
       SET organization_code = (SELECT organization_code
                                  FROM MTL_PARAMETERS MP1
                             WHERE mp1.organization_id = MIRI.organization_id)
       WHERE process_flag = 1
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
         AND MIRI.set_process_id = l_set_process_id
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS MP2
                      WHERE mp2.organization_id = MIRI.organization_id);

Line: 2004

 /* Update Organization_ids if organization code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
         SET organization_id = (SELECT organization_id
                                  FROM MTL_PARAMETERS mp1
                             WHERE mp1.organization_code = MIRI.organization_code)
       WHERE process_flag = 1
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is null
         AND organization_code is not null
         AND MIRI.set_process_id = l_set_process_id;

Line: 2021

/* Update Assembly Item name */

   UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
       SET  item_number   = (SELECT concatenated_segments
                             FROM MTL_SYSTEM_ITEMS_KFV mvl1
                             WHERE mvl1.inventory_item_id = MIRI.inventory_item_id
           and mvl1.organization_id = MIRI.organization_id)
       WHERE process_flag = 1
         AND change_notice is null --added for bug 9673701
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND inventory_item_id is not null
         AND organization_id is not null
         AND MIRI.set_process_id = l_set_process_id
         AND exists (SELECT 'x'
                       FROM mtl_system_items mvl2
                      WHERE mvl2.inventory_item_id = MIRI.inventory_item_id
          and mvl2.organization_id = MIRI.organization_id);

Line: 2044

       UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
             transaction_type = upper(transaction_type)
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
         AND change_notice is null --added for bug 9673701
         AND (all_org = 1
             OR
          (all_org = 2 AND organization_id = org_id))
         AND MIRI.set_process_id = l_set_process_id;

Line: 2059

/* Update the interface records with process_flag 3 and insert into
MTL_INTERFACE_ERRORS if Item number or Organization_code  is missing*/

 l_sysdate        :=  SYSDATE;

Line: 2070

   INSERT INTO MTL_INTERFACE_ERRORS
   (
  TRANSACTION_ID,
  UNIQUE_ID,
  ORGANIZATION_ID,
  COLUMN_NAME,
  TABLE_NAME,
  MESSAGE_NAME,
  ERROR_MESSAGE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY,
  LAST_UPDATE_LOGIN,
  REQUEST_ID,
  PROGRAM_APPLICATION_ID,
  PROGRAM_ID,
  PROGRAM_UPDATE_DATE
   )
  Select
  MIRI.transaction_id,
  MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
  Null,
  null,
  'MTL_ITEM_REVISIONS_INTERFACE',
  decode ( MIRI.Organization_code, null, msg_name1,msg_name2),
  decode ( MIRI.Organization_code, null, msg_text1,msg_text2),
        NVL(LAST_UPDATE_DATE, SYSDATE),
        NVL(LAST_UPDATED_BY, user_id),
        NVL(CREATION_DATE,SYSDATE),
        NVL(CREATED_BY, user_id),
        NVL(LAST_UPDATE_LOGIN, user_id),
         req_id,
        NVL(PROGRAM_APPLICATION_ID, prog_appid),
        NVL(PROGRAM_ID, prog_id),
        NVL(PROGRAM_UPDATE_DATE, sysdate)
    from MTL_ITEM_REVISIONS_INTERFACE MIRI
   where (organization_code is null or item_number is null)
  and transaction_id is not null
  and process_flag =1
  AND change_notice is null --added for bug 9673701
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  and MIRI.set_process_id = l_set_process_id;

Line: 2115

  Update  MTL_ITEM_REVISIONS_INTERFACE MIRI
  set process_flag = 3
  where (item_number is null or Organization_code is null)
  and transaction_id is not null
  and process_flag =1
  AND change_notice is null --added for bug 9673701
  and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
  and MIRI.set_process_id = l_set_process_id;

Line: 2223

   UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
       SET pk3_value  = (SELECT mrb.revision_id
                             FROM  mtl_item_revisions_b mrb
                             WHERE mrb.inventory_item_id = BBMI.Assembly_item_id
                             AND   mrb.organization_id = BBMI.organization_id
                             AND   mrb.revision = BBMI.REVISION)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND Assembly_item_id is not null
         AND Revision is not null
         AND organization_id is not null
         AND exists (SELECT 1
                      FROM  mtl_item_revisions_b mrb
                      WHERE mrb.inventory_item_id = BBMI.Assembly_item_id
                      AND   mrb.organization_id = BBMI.organization_id
                      AND   mrb.revision = BBMI.Revision);

Line: 2240

   /* If SYNC rows has valid ComponentSequenceId then update the transaction type to UPDATE */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI SET transaction_type = G_Update
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND UPPER(transaction_type) = 'SYNC'
         AND component_sequence_id IS NOT NULL
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND EXISTS (SELECT 'x'
           FROM BOM_INVENTORY_COMPONENTS BIC2
           WHERE BIC2.component_sequence_id = BICI.component_sequence_id );

Line: 2254

   /* If SYNC rows don't have ComponentSequenceId value then update the transaction type to CREATE */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI SET transaction_type = G_Create
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND UPPER(transaction_type) = 'SYNC'
         AND component_sequence_id IS NULL
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          );

Line: 2265

 /* Update Organization_ids if organization_code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BICI.organization_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_code is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_code = BICI.organization_code);

Line: 2284

 /* Update the Assembly_item_number */
    UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET assembly_item_number  = (SELECT concatenated_segments
                             FROM  mtl_system_items_kfv MKFV
                             WHERE MKFV.inventory_item_id = BICI.Assembly_item_id
                             AND   MKFV.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create,G_NoOp)
         AND Assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM  mtl_system_items MKFV2
                      WHERE MKFV2.inventory_item_id = BICI.Assembly_item_id
                      AND   MKFV2.organization_id = BICI.organization_id);

Line: 2306

 /* Update the Assembly_item_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET Assembly_item_id  = (SELECT inventory_item_id
                             FROM  mtl_system_items_kfv mvll
                             WHERE mvll.concatenated_segments = BICI.Assembly_item_number
                             AND   mvll.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create,G_NoOp)
         AND Assembly_item_number is not null
         AND organization_id is not null
         AND assembly_item_id is NULL
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          );

Line: 2323

/* Update component_item_number */
     UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET component_item_number  = (SELECT concatenated_segments
                             FROM  mtl_system_items_kfv mvll
                             WHERE mvll.inventory_item_id = BICI.Component_item_id
                             AND   mvll.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND Component_item_id is not null
         AND Organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items MKFV
                      WHERE MKFV.inventory_item_id = BICI.Component_item_id
                      AND   MKFV.organization_id = BICI.organization_id);

Line: 2343

   /* Update the component_item_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET component_item_id  = (SELECT inventory_item_id
                                 FROM  mtl_system_items_kfv mvll
                                 WHERE mvll.concatenated_segments = BICI.Component_item_number
                                 AND   mvll.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND Component_item_number is not null
         AND Organization_id is not null
         AND Component_item_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          );

Line: 2360

/* Update the bill_sequence_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET bill_sequence_id  = (SELECT bill_sequence_id
                             FROM  bom_structures_b bom
                             WHERE bom.assembly_item_id = BICI.assembly_item_id
                             AND   bom.organization_id = BICI.organization_id
                             AND   NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BICI.alternate_bom_designator,FND_API.G_MISS_CHAR))
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND assembly_item_number is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM bom_structures_b bom2
                      WHERE bom2.assembly_item_id = BICI.assembly_item_id
                      AND   bom2.organization_id = BICI.organization_id
                      AND   NVL(bom2.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BICI.alternate_bom_designator,FND_API.G_MISS_CHAR));

Line: 2398

/* Resolve the Component_sequence_ids for updates and deletes */

   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET(component_item_id, effectivity_date,
         operation_seq_num,  from_end_item_unit_number)
       = (SELECT component_item_id,
    effectivity_date, operation_seq_num,  from_end_item_unit_number
           FROM BOM_INVENTORY_COMPONENTS BIC1
           WHERE BIC1.component_sequence_id = BICI.component_sequence_id )
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND UPPER(transaction_type) IN (G_Delete, G_Update)
         AND component_sequence_id IS NOT NULL
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND EXISTS (SELECT 'x'
           FROM BOM_INVENTORY_COMPONENTS BIC2
           WHERE BIC2.component_sequence_id = BICI.component_sequence_id );

Line: 2420

/* Update the component_sequence_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET COMPONENT_SEQUENCE_ID
                           = (SELECT COMPONENT_SEQUENCE_ID
                             FROM BOM_INVENTORY_COMPONENTS BIC
                             WHERE BIC.bill_sequence_id = BICI.bill_Sequence_id
                             AND BIC.component_item_id = BICI.component_item_id
                             AND BIC.operation_seq_num = BICI.operation_seq_num
                             AND BIC.effectivity_date = BICI.effectivity_date)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND UPPER(transaction_type) IN (G_Update, G_Delete)
         AND COMPONENT_SEQUENCE_ID IS NULL
         AND bill_sequence_id IS NOT NULL
         AND component_item_id IS NOT NULL
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND EXISTS (SELECT 'x'
                     FROM BOM_INVENTORY_COMPONENTS BIC
                     WHERE BIC.bill_sequence_id = BICI.bill_Sequence_id
                     AND BIC.component_item_id = BICI.component_item_id
                     AND BIC.operation_seq_num = BICI.operation_seq_num
                     AND BIC.effectivity_date = BICI.effectivity_date);

Line: 2448

   /* Update the From_end_item_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET From_End_Item_id = (SELECT inventory_item_id
                             FROM  mtl_system_items_kfv mvll
                             WHERE mvll.concatenated_segments = BICI.From_End_Item
                             AND   mvll.organization_id = BICI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND From_End_Item is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM  mtl_system_items_kfv mvll2
                      WHERE mvll2.concatenated_segments = BICI.From_End_Item
                      AND   mvll2.organization_id = BICI.organization_id);

Line: 2468

   /* Update the From_end_item_rev_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET From_end_item_rev_id  = (SELECT mrb.revision_id
                             FROM  mtl_item_revisions_b mrb
                             WHERE mrb.inventory_item_id = BICI.From_End_Item_id
                             AND   mrb.organization_id = BICI.organization_id
                             AND   mrb.revision = BICI.From_end_item_rev_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND From_End_Item is not null
         AND From_end_item_rev_code is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND exists (SELECT 1
                      FROM  mtl_item_revisions_b mrb
                      WHERE mrb.inventory_item_id = BICI.From_End_Item_id
                      AND   mrb.organization_id = BICI.organization_id
                      AND   mrb.revision = BICI.From_end_item_rev_code);

Line: 2491

   /* Update the To_end_item_rev_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET To_end_item_rev_id  = (SELECT mrb.revision_id
                             FROM  mtl_item_revisions_b mrb
                             WHERE mrb.inventory_item_id = BICI.From_End_Item_id
                             AND   mrb.organization_id = BICI.organization_id
                             AND   mrb.revision = BICI.To_end_item_rev_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND From_End_Item is not null
         AND To_end_item_rev_code is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND exists (SELECT 1
                      FROM  mtl_item_revisions_b mrb
                      WHERE mrb.inventory_item_id = BICI.From_End_Item_id
                      AND   mrb.organization_id = BICI.organization_id
                      AND   mrb.revision = BICI.To_end_item_rev_code);

Line: 2514

   /* Update the component_revision_id */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET Component_revision_id  = (SELECT mrb.revision_id
                             FROM  mtl_item_revisions_b mrb
                             WHERE mrb.inventory_item_id = BICI.component_item_id
                             AND   mrb.organization_id = BICI.organization_id
                             AND   mrb.revision = BICI.Component_revision_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND component_item_id is not null
         AND Component_revision_code is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
         AND exists (SELECT 1
                      FROM  mtl_item_revisions_b mrb
                      WHERE mrb.inventory_item_id = BICI.component_item_id
                      AND   mrb.organization_id = BICI.organization_id
                      AND   mrb.revision = BICI.Component_revision_code);

Line: 2537

   /* Update the assembly items pk3 value */
   UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET Parent_Revision_Id  = (SELECT mrb.revision_id
                             FROM  mtl_item_revisions_b mrb
                             WHERE mrb.inventory_item_id = BICI.Assembly_item_id
                             AND   mrb.organization_id = BICI.organization_id
                             AND   mrb.revision = BICI.Parent_revision_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND UPPER(transaction_type) in (G_Delete, G_Update, G_Create)
         AND Assembly_item_id is not null
         AND Parent_revision_code is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          );

Line: 2556

  /* Update Supply_locator_name */

  UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
       SET  supply_locator_id  = (SELECT inventory_location_id
                            FROM MTL_ITEM_LOCATIONS_KFV MIL1
                            WHERE MIL1.concatenated_segments = BICI.location_name
                            AND MIL1.organization_id = BICI.organization_id)
      WHERE process_flag = 1
        AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
        AND location_name is not null
        AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
          OR  ( p_batch_id = BICI.batch_id )
          )
          AND exists (SELECT 'x'
                     FROM MTL_ITEM_LOCATIONS_KFV mil2
                     WHERE mil2.concatenated_segments = BICI.location_name
                     AND mil2.organization_id = BICI.organization_id);

Line: 2577

  UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
  SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
      transaction_type = upper(transaction_type)
  WHERE transaction_id is null
  AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
  AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
  AND
   (
       ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
   OR  ( p_batch_id = BICI.batch_id )
   );

Line: 2608

      UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET(bill_sequence_id,  component_item_id, effectivity_date,
         operation_seq_num,  from_end_item_unit_number)
       = (SELECT bill_sequence_id,  component_item_id,
    effectivity_date, operation_seq_num,  from_end_item_unit_number
           FROM BOM_INVENTORY_COMPONENTS BIC1
           WHERE BIC1.component_sequence_id = BRDI.component_sequence_id )
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND COMPONENT_SEQUENCE_ID is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
         AND exists (SELECT 'x'
           FROM BOM_INVENTORY_COMPONENTS BIC2
           WHERE BIC2.COMPONENT_SEQUENCE_ID = BRDI.COMPONENT_SEQUENCE_ID );

Line: 2627

/* Resolve the Bill sequence ids for updates and deletes */

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET(assembly_item_id, organization_id, ALTERNATE_BOM_DESIGNATOR)
       = (SELECT assembly_item_id, organization_id , alternate_bom_designator
           FROM BOM_BILL_OF_MATERIALS BBM1
           WHERE BBM1.bill_sequence_id = BRDI.bill_sequence_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND bill_sequence_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
         AND exists (SELECT 'x'
       FROM BOM_BILL_OF_MATERIALS BBM2
       WHERE BBM2.bill_sequence_id = BRDI.bill_sequence_id);

Line: 2646

/* Update Organization Code using Organization_id
this also needed if Organization_id is given and code is not given*/

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET organization_code = (SELECT organization_code
                                  FROM MTL_PARAMETERS mp1
                             WHERE mp1.organization_id = BRDI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BRDI.organization_id);

Line: 2666

 /* Update Organization_ids if organization_code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE BOM_REF_DESGS_INTERFACE BRDI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BRDI.organization_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND organization_id is null
         AND organization_code is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 2684

/* Update Assembly Item name */

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET  ASSEMBLY_ITEM_NUMBER  = (SELECT CONCATENATED_SEGMENTS
                                     FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                     WHERE mvl1.inventory_item_id = BRDI.assembly_item_id
                                     AND mvl1.organization_id = BRDI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items mvl12
                      WHERE mvl12.inventory_item_id = BRDI.assembly_item_id
                      AND mvl12.organization_id = BRDI.organization_id);

Line: 2704

   /* Update the Assembly_item_id */
   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET Assembly_item_id  = (SELECT inventory_item_id
                             FROM  mtl_system_items_kfv mvll
                             WHERE mvll.concatenated_segments = BRDI.Assembly_item_number
                             AND   mvll.organization_id = BRDI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND Assembly_item_number is not null
         AND organization_id is not null
         AND Assembly_item_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 2720

/* Update Component Item name */
   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET  COMPONENT_ITEM_NUMBER   = (SELECT CONCATENATED_SEGMENTS
                                       FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                       WHERE mvl1.inventory_item_id = BRDI.component_item_id
                                       AND mvl1.organization_id = BRDI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND COMPONENT_ITEM_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items mvl12
                      WHERE mvl12.inventory_item_id = BRDI.component_item_id
                      AND mvl12.organization_id = BRDI.organization_id);

Line: 2740

   /* Update the Component_item_id */
   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET Component_item_id  = (SELECT inventory_item_id
                                 FROM  mtl_system_items_kfv mvll
                                 WHERE mvll.concatenated_segments = BRDI.Component_item_number
                                 AND   mvll.organization_id = BRDI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND Component_item_number is not null
         AND organization_id is not null
         AND component_item_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 2757

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET bill_sequence_id  = (SELECT bill_sequence_id
                             FROM  bom_structures_b bom
                             WHERE bom.assembly_item_id = BRDI.assembly_item_id
                             AND   bom.organization_id = BRDI.organization_id
                             AND   NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BRDI.alternate_bom_designator,FND_API.G_MISS_CHAR))
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, 'SYNC', G_Create)
         AND assembly_item_id is not null
         AND organization_id is not null
         AND bill_sequence_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 2774

   /* Update the component_sequence_id */
   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET COMPONENT_SEQUENCE_ID
                           = (SELECT COMPONENT_SEQUENCE_ID
                             FROM bom_components_b BIC
                             WHERE BIC.bill_sequence_id = BRDI.bill_Sequence_id
                             AND BIC.component_item_id = BRDI.component_item_id
                             AND BIC.operation_seq_num = BRDI.operation_seq_num
                             AND BIC.effectivity_date = BRDI.effectivity_date)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND UPPER(transaction_type) IN (G_Update, G_Delete, 'SYNC', G_Create)
         AND COMPONENT_SEQUENCE_ID IS NULL
         AND bill_sequence_id IS NOT NULL
         AND component_item_id IS NOT NULL
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 2795

   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET Assembly_Item_Revision_Id  = (SELECT mrb.revision_id
                             FROM  mtl_item_revisions_b mrb
                             WHERE mrb.inventory_item_id = BRDI.Assembly_item_id
                             AND   mrb.organization_id = BRDI.organization_id
                             AND   mrb.revision = BRDI.Assembly_Item_Revision_Code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND UPPER(transaction_type) IN (G_Update, G_Delete, 'SYNC', G_Create)
         AND Assembly_item_id IS NOT NULL
         AND Assembly_Item_Revision_Code IS NOT NULL
         AND Organization_Id IS NOT NULL
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 2812

    /*Update the transaction_types */
   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET Transaction_Type = G_Update
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND transaction_type = 'SYNC'
         AND COMPONENT_SEQUENCE_ID is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          )
         AND exists (SELECT 'x'
           FROM BOM_REFERENCE_DESIGNATORS BRDI2
           WHERE BRDI2.COMPONENT_SEQUENCE_ID = BRDI.COMPONENT_SEQUENCE_ID
           AND BRDI2.COMPONENT_REFERENCE_DESIGNATOR = BRDI.COMPONENT_REFERENCE_DESIGNATOR
           AND NVL(BRDI2.ACD_TYPE, 1) = NVL(BRDI.ACD_TYPE, 1) );

Line: 2829

    /*Update the transaction_types */
   UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET Transaction_Type = G_Create
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND transaction_type = 'SYNC'
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 2842

       UPDATE BOM_REF_DESGS_INTERFACE BRDI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
             transaction_type = upper(transaction_type)
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND
          (
              ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
          OR  ( p_batch_id = BRDI.batch_id )
          );

Line: 2873

     UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET(bill_sequence_id,  component_item_id, effectivity_date,
         operation_seq_num,  from_end_item_unit_number)
       = (select bill_sequence_id,  component_item_id,
    EFFECTIVITY_DATE, OPERATION_SEQ_NUM,  FROM_END_ITEM_UNIT_NUMBER
           FROM BOM_INVENTORY_COMPONENTS BIC1
           WHERE BIC1.COMPONENT_SEQUENCE_ID = BSCI.component_sequence_id )
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND COMPONENT_SEQUENCE_ID is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
         AND exists (SELECT 'x'
           FROM BOM_INVENTORY_COMPONENTS BIC2
           WHERE BIC2.COMPONENT_SEQUENCE_ID = BSCI.COMPONENT_SEQUENCE_ID );

Line: 2893

/* Resolve the Bill sequence ids for updates and deletes */

   UPDATE bom_sub_comps_interface BSCI
       SET(assembly_item_id, organization_id, ALTERNATE_BOM_DESIGNATOR)
       = (SELECT assembly_item_id, organization_id , alternate_bom_designator
           FROM BOM_BILL_OF_MATERIALS BBM1
           WHERE BBM1.bill_sequence_id = BSCI.bill_sequence_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND bill_sequence_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
         AND exists (SELECT 'x'
       FROM BOM_BILL_OF_MATERIALS BBM2
       WHERE BBM2.bill_sequence_id = BSCI.bill_sequence_id);

Line: 2912

/* Update Organization Code using Organization_id
this also needed if Organization_id is given and code is not given*/

   UPDATE bom_sub_comps_interface BSCI
       SET organization_code = (SELECT organization_code
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_id = BSCI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BSCI.organization_id);

Line: 2932

 /* Update Organization_ids if organization_code is given org id is null.
  Orgnaization_id information is needed in the next steps */

      UPDATE bom_sub_comps_interface BSCI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BSCI.organization_code)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND organization_id is null
         AND organization_code is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );

Line: 2950

/* Update Assembly Item name */

   UPDATE bom_sub_comps_interface BSCI
       SET  ASSEMBLY_ITEM_NUMBER  = (SELECT CONCATENATED_SEGMENTS
                                     FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                     WHERE mvl1.inventory_item_id = BSCI.assembly_item_id
                                     AND mvl1.organization_id = BSCI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items mvl12
                      WHERE mvl12.inventory_item_id = BSCI.assembly_item_id
                      AND mvl12.organization_id = BSCI.organization_id);

Line: 2970

   /* Update the Assembly_item_id */
   UPDATE bom_sub_comps_interface BSCI
       SET Assembly_item_id  = (SELECT inventory_item_id
                                FROM  mtl_system_items_kfv mvll
                                WHERE mvll.concatenated_segments = BSCI.Assembly_item_number
                                AND   mvll.organization_id = BSCI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND Assembly_item_number is not null
         AND organization_id is not null
         AND assembly_item_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );

Line: 2986

/* Update Component Item name */
   UPDATE bom_sub_comps_interface BSCI
       SET  COMPONENT_ITEM_NUMBER   = (SELECT CONCATENATED_SEGMENTS
                                       FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                       WHERE mvl1.inventory_item_id = BSCI.component_item_id
                                       AND mvl1.organization_id = BSCI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND COMPONENT_ITEM_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items mvl12
                      WHERE mvl12.inventory_item_id = BSCI.component_item_id
                      AND mvl12.organization_id = BSCI.organization_id);

Line: 3006

   /* Update the Component_item_id */
   UPDATE bom_sub_comps_interface BSCI
       SET Component_item_id  = (SELECT inventory_item_id
                                 FROM  mtl_system_items_kfv mvll
                                 WHERE mvll.concatenated_segments = BSCI.Component_item_number
                                 AND   mvll.organization_id = BSCI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND Component_item_number is not null
         AND organization_id is not null
         AND component_item_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );

Line: 3023

   UPDATE bom_sub_comps_interface BSCI
       SET bill_sequence_id  = (SELECT bill_sequence_id
                             FROM  bom_bill_of_materials bom
                             WHERE bom.assembly_item_id = BSCI.assembly_item_id
                             AND   bom.organization_id = BSCI.organization_id
                             AND   NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BSCI.alternate_bom_designator,FND_API.G_MISS_CHAR))
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Delete, G_Update, 'SYNC', G_Create)
         AND assembly_item_id is not null
         AND organization_id is not null
         AND bill_sequence_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );

Line: 3040

   /* Update the component_sequence_id */
   UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET COMPONENT_SEQUENCE_ID
                           = (SELECT COMPONENT_SEQUENCE_ID
                             FROM BOM_INVENTORY_COMPONENTS BIC
                             WHERE BIC.bill_sequence_id = BSCI.bill_Sequence_id
                             AND BIC.component_item_id = BSCI.component_item_id
                             AND BIC.operation_seq_num = BSCI.operation_seq_num
                             AND BIC.effectivity_date = BSCI.effectivity_date)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND UPPER(transaction_type) IN (G_Update, G_Delete, 'SYNC', G_Create)
         AND COMPONENT_SEQUENCE_ID IS NULL
         AND bill_sequence_id IS NOT NULL
         AND component_item_id IS NOT NULL
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );

Line: 3061

       UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET  SUBSTITUTE_COMP_NUMBER = (SELECT concatenated_segments
                             FROM MTL_SYSTEM_ITEMS_KFV mvl1
                             WHERE mvl1.inventory_item_id = BSCI.SUBSTITUTE_COMPONENT_ID
                             and mvl1.organization_id = BSCI.organization_id)
        WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update,'SYNC')
         AND SUBSTITUTE_COMPONENT_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items mvl12
                      WHERE mvl12.inventory_item_id = BSCI.SUBSTITUTE_COMPONENT_ID
                      AND mvl12.organization_id = BSCI.organization_id);

Line: 3082

       UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET  SUBSTITUTE_COMPONENT_ID = (SELECT inventory_item_id
                             FROM MTL_SYSTEM_ITEMS_KFV mvl1
                             WHERE mvl1.concatenated_segments = BSCI.SUBSTITUTE_COMP_NUMBER
                             and mvl1.organization_id = BSCI.organization_id)
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update,'SYNC')
         AND substitute_comp_number is not null
         AND organization_id is not null
         AND substitute_component_id is null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );

Line: 3100

       UPDATE BOM_SUB_COMPS_INTERFACE BSCI
       SET  NEW_SUB_COMP_NUMBER = (SELECT CONCATENATED_SEGMENTS
                             FROM MTL_SYSTEM_ITEMS_KFV mvl1
                             WHERE mvl1.inventory_item_id = BSCI.NEW_SUB_COMP_ID
                             and mvl1.organization_id = BSCI.organization_id)
       WHERE  (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update,'SYNC')
         AND NEW_SUB_COMP_ID is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
          AND exists (SELECT 'x'
                      FROM mtl_system_items mvl12
                      WHERE mvl12.inventory_item_id = BSCI.NEW_SUB_COMP_ID
                      and mvl12.organization_id = BSCI.organization_id);

Line: 3122

    /*Update the transaction_types */
   UPDATE bom_sub_comps_interface BSCI
       SET Transaction_Type = G_Update
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND transaction_type = 'SYNC'
         AND COMPONENT_SEQUENCE_ID is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          )
         AND exists (SELECT 'x'
           FROM BOM_SUBSTITUTE_COMPONENTS BSCI2
           WHERE BSCI2.COMPONENT_SEQUENCE_ID = BSCI.COMPONENT_SEQUENCE_ID
           AND NVL(BSCI2.ACD_TYPE, 1) = NVL(BSCI.ACD_TYPE, 1) );

Line: 3138

    /*Update the transaction_types */
   UPDATE bom_sub_comps_interface BSCI
       SET Transaction_Type = G_Create
       WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND transaction_type = 'SYNC'
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );

Line: 3151

       UPDATE bom_sub_comps_interface BSCI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
             transaction_type = upper(transaction_type)
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND
          (
              ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
          OR  ( p_batch_id = BSCI.batch_id )
          );