APPS.BOM_RTG_OI_UTIL SQL Statements

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

Line: 26

G_Update constant varchar2(10) := 'UPDATE'; -- transaction type

Line: 27

G_Delete constant varchar2(10) := 'DELETE'; -- transaction type

Line: 69

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

   UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
       SET(assembly_item_id, organization_id, alternate_routing_designator)
      = (SELECT assembly_item_id, organization_id , alternate_routing_designator
	         FROM BOM_OPERATIONAL_ROUTINGS BOR1
	         WHERE BOR1.routing_sequence_id = BORI.routing_sequence_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND routing_sequence_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
			 FROM BOM_OPERATIONAL_ROUTINGS BOR2
			 WHERE BOR2.routing_sequence_id = BORI.routing_sequence_id);

Line: 90

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

   UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
       SET organization_code = (SELECT organization_code
                                  FROM MTL_PARAMETERS MP1
                             WHERE mp1.organization_id = BORI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
         AND organization_code is NULL      -- Bug #3411601
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS MP2
                      WHERE mp2.organization_id = BORI.organization_id);

Line: 111

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

      UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
         SET organization_id = (SELECT organization_id
                                  FROM MTL_PARAMETERS mp1
                             WHERE mp1.organization_code = BORI.organization_code)
       WHERE process_flag = 1
         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 (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          );

Line: 129

/* Update Assembly Item name */

   UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
       SET assembly_item_number   = (SELECT concatenated_segments
                                     FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                     WHERE mvl1.inventory_item_id = BORI.assembly_item_id
                                     and mvl1.organization_id = BORI.organization_id)
       WHERE process_flag = 1
         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 (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_SYSTEM_ITEMS mvl2
                       WHERE mvl2.inventory_item_id = BORI.assembly_item_id
              		     and mvl2.organization_id = BORI.organization_id);

Line: 155

       UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
       WHERE transaction_id is null
--Bug 3411601  AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete, 'NO_OP')
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 170

       UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
         SET transaction_type = upper(transaction_type)
       WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 188

  UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
       SET(common_assembly_item_id)
       = (SELECT assembly_item_id
	         FROM BOM_OPERATIONAL_ROUTINGS BOR1
	         WHERE BOR1.routing_sequence_id = BORI.common_routing_sequence_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND common_routing_sequence_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
			 FROM BOM_OPERATIONAL_ROUTINGS BOR2
			 WHERE BOR2.routing_sequence_id = BORI.common_routing_sequence_id);

Line: 207

/* Update Assembly Item name */

   UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
       SET common_item_number   = (SELECT concatenated_segments
                                   FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                   WHERE mvl1.inventory_item_id = BORI.common_assembly_item_id
                                   and mvl1.organization_id = BORI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND common_assembly_item_id is not null
         AND organization_id is not null
         AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_SYSTEM_ITEMS mvl2
                       WHERE mvl2.inventory_item_id = BORI.common_assembly_item_id
		                   and mvl2.organization_id = BORI.organization_id);

Line: 230

/* Update the line code from line_id */
   UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
       SET line_code   = (SELECT line_code
                             FROM WIP_LINES wl1
                             WHERE wl1.LINE_ID = BORI.LINE_ID -- Bug Fix 3782414
                             AND wl1.organization_id = BORI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND line_id is not null
	 AND organization_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM WIP_LINES wl2
                      WHERE wl2.organization_id = BORI.organization_id
		      AND nvl(wl2.disable_date, trunc(sysdate) + 1) > trunc(sysdate));

Line: 251

/* Update the delete_group_name from bom_interface_delete_groups */
   UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
       SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
			   = (SELECT DELETE_GROUP_NAME, DESCRIPTION
                             FROM bom_interface_delete_groups
			     Where upper(entity_name) = G_RtgDelEntity
			     And rownum = 1)
       WHERE process_flag = 1
         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 (BORI.batch_id IS NULL) )
              OR ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
                     FROM bom_interface_delete_groups
		     Where upper(entity_name) = G_RtgDelEntity
                     );

Line: 273

/* Update Supply_locator_name */

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

Line: 299

                 INSERT into MTL_RTG_ITEM_REVS_INTERFACE
                     (INVENTORY_ITEM_NUMBER,
                      ORGANIZATION_CODE,
                      PROCESS_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,
		      BATCH_ID
                     )
                    select
                      assembly_item_number,
                      Organization_Code,
                      upper(PROCESS_REVISION),
                      sysdate + 1/1440,
                      sysdate + 1/1440,
                      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),
		      BATCH_ID
                    FROM BOM_OP_ROUTINGS_INTERFACE
                     WHERE process_flag = 1
                     AND transaction_type = G_Create
		     AND
                      (
                          ( (p_batch_id is null) AND (batch_id is null) )
                          OR ( p_batch_id = batch_id )
                      )
                     AND (all_org = 1
                          OR
                          (all_org = 2 AND organization_id = org_id))
                     AND process_revision is not null;

Line: 352

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

 msg_name1	 := 'BOM_ORG_ID_MISSING';

Line: 362

   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
	BORI.transaction_id,
	MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
	Null,
	null,
	'BOM_OP_ROUTINGS_INTERFACE',
	decode ( BORI.Organization_code, null, msg_name1,msg_name2),
	decode ( BORI.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_OP_ROUTINGS_INTERFACE BORI
   where (organization_code is null or assembly_item_number is null)
	and transaction_id is not null
	and process_flag =1
	and
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 409

	Update BOM_OP_ROUTINGS_INTERFACE BORI
	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
         (
             ( (p_batch_id is null) AND (batch_id is null) )
             OR ( p_batch_id = batch_id )
         )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 472

/* Resolve the routing_sequence_ids for updates and deletes */

   UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
       SET(routing_sequence_id, effectivity_date,
	       operation_seq_num, operation_type)
       = (SELECT routing_sequence_id, effectivity_date, operation_seq_num, operation_type
	         FROM BOM_OPERATION_SEQUENCES BOS1
	         WHERE BOS1.operation_sequence_id = BOSI.operation_sequence_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND operation_sequence_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND exists (SELECT 'x'
	         FROM BOM_OPERATION_SEQUENCES BOS2
	         WHERE BOS2.operation_sequence_id = BOSI.operation_sequence_id );

Line: 496

   UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
       SET(assembly_item_id, organization_id, alternate_routing_designator)
       = (SELECT assembly_item_id, organization_id , alternate_routing_designator
	         FROM BOM_OPERATIONAL_ROUTINGS BOR1
	         WHERE BOR1.routing_sequence_id = BOSI.routing_sequence_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND routing_sequence_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND exists (SELECT 'x'
			 FROM BOM_OPERATIONAL_ROUTINGS BOR2
			 WHERE BOR2.routing_sequence_id = BOSI.routing_sequence_id);

Line: 515

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

   UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
       SET organization_code = (SELECT organization_code
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_id = BOSI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
	       AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BOSI.organization_id);

Line: 537

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

      UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BOSI.organization_code)
       WHERE process_flag = 1
         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 (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          );

Line: 557

/* Update Assembly Item name */

   UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
       SET assembly_item_number  = (SELECT concatenated_segments
                                   FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                   WHERE mvl1.inventory_item_id = BOSI.assembly_item_id
                                   and mvl1.organization_id = BOSI.organization_id)
       WHERE process_flag = 1
         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 (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND exists (SELECT 'x'
                      FROM MTL_SYSTEM_ITEMS mvl2
                      WHERE mvl2.inventory_item_id = BOSI.assembly_item_id
		                  and mvl2.organization_id = BOSI.organization_id);

Line: 582

       UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 596

       UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
         SET transaction_type = upper(transaction_type)
       WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 611

/* Update the operation code from the standard operation id */

   UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
       SET operation_code  = (SELECT operation_code
                             FROM BOM_STANDARD_OPERATIONS bso
                             WHERE bso.standard_operation_id = BOSI.standard_operation_id
			     and bso.organization_id = BOSI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND standard_operation_id is not null
	 AND organization_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND exists (SELECT 'x'
                      FROM  BOM_STANDARD_OPERATIONS bso
                      WHERE bso.standard_operation_id = BOSI.standard_operation_id
		      and bso.organization_id = BOSI.organization_id);

Line: 633

/* Update the department code from the department id */

   UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
       SET department_code  = (SELECT department_code
                             FROM BOM_DEPARTMENTS bd
                             WHERE bd.department_id = BOSI.department_id
			     and bd.organization_id = BOSI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND department_id is not null
	 AND organization_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND exists (SELECT 'x'
                      FROM  BOM_STANDARD_OPERATIONS bso
                      WHERE bso.department_id = BOSI.department_id
		      and bso.organization_id = BOSI.organization_id);

Line: 657

   UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
       SET(line_op_seq_number, line_op_code)
       = (SELECT bos1.operation_seq_num, bso1.operation_code
	         FROM BOM_OPERATION_SEQUENCES BOS1, BOM_STANDARD_OPERATIONS BSO1
	         WHERE BOS1.operation_sequence_id = BOSI.line_op_seq_id
		 AND BSO1.organization_id = BOSI.organization_id
		 AND BOS1.standard_operation_id = BSO1.standard_operation_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND line_op_seq_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND exists (SELECT 'x'
	         FROM BOM_OPERATION_SEQUENCES BOS1, BOM_STANDARD_OPERATIONS BSO1
	         WHERE BOS1.operation_sequence_id = BOSI.line_op_seq_id
		 AND BSO1.organization_id = BOSI.organization_id
		 AND BOS1.standard_operation_id = BSO1.standard_operation_id);

Line: 678

   UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
       SET(process_seq_number, process_code)
       = (SELECT bos1.operation_seq_num, bso1.operation_code
	         FROM BOM_OPERATION_SEQUENCES BOS1, BOM_STANDARD_OPERATIONS BSO1
	         WHERE BOS1.operation_sequence_id = BOSI.process_op_seq_id
		 AND BSO1.organization_id = BOSI.organization_id
		 AND BOS1.standard_operation_id = BSO1.standard_operation_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update)
         AND process_op_seq_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND exists (SELECT 'x'
	         FROM BOM_OPERATION_SEQUENCES BOS1, BOM_STANDARD_OPERATIONS BSO1
	         WHERE BOS1.operation_sequence_id = BOSI.process_op_seq_id
		 AND BSO1.organization_id = BOSI.organization_id
		 AND BOS1.standard_operation_id = BSO1.standard_operation_id);

Line: 700

/* Update the delete_group_name from bom_interface_delete_groups */
   UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
       SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
			   = (SELECT DELETE_GROUP_NAME, DESCRIPTION
                             FROM bom_interface_delete_groups
			     Where upper(entity_name) = G_OprDelEntity
			     And rownum = 1)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete)
	 AND organization_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
          OR  ( p_batch_id = BOSI.batch_id )
          )
         AND exists (SELECT 'x'
                     FROM bom_interface_delete_groups
		     Where upper(entity_name) = G_RtgDelEntity
                     );

Line: 722

 /* INSERTS ONLY - Load rows from operation interface into resource interface*/
   INSERT into bom_op_resources_interface (
        RESOURCE_ID,
        RESOURCE_CODE,
        ORGANIZATION_ID,
	ORGANIZATION_CODE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        REQUEST_ID,
        PROGRAM_APPLICATION_ID,
        PROGRAM_ID,
        PROGRAM_UPDATE_DATE,
        OPERATION_SEQUENCE_ID,
	OPERATION_SEQ_NUM,
	ASSEMBLY_ITEM_NUMBER,
	ASSEMBLY_ITEM_ID,
	ALTERNATE_ROUTING_DESIGNATOR,
	EFFECTIVITY_DATE,
	RESOURCE_SEQ_NUM,
        PROCESS_FLAG,
        TRANSACTION_TYPE,
	BATCH_ID)
      SELECT
             RESOURCE_ID1,
             RESOURCE_CODE1,
             ORGANIZATION_ID,
	     ORGANIZATION_CODE,
             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),
             OPERATION_SEQUENCE_ID,
	     OPERATION_SEQ_NUM,
	     ASSEMBLY_ITEM_NUMBER,
	     ASSEMBLY_ITEM_ID,
	     ALTERNATE_ROUTING_DESIGNATOR,
	     EFFECTIVITY_DATE,
	     10,
             1,
             G_Create,
	     BATCH_ID
        FROM BOM_OP_SEQUENCES_INTERFACE
       WHERE process_flag = 1
         AND transaction_type = G_Create
	 AND
          (
              ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
              OR ( p_batch_id = batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND (RESOURCE_CODE1 is not null
              OR
              RESOURCE_ID1 is not null);

Line: 786

   INSERT into bom_op_resources_interface (
        RESOURCE_ID,
        RESOURCE_CODE,
        ORGANIZATION_ID,
	ORGANIZATION_CODE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        REQUEST_ID,
        PROGRAM_APPLICATION_ID,
        PROGRAM_ID,
        PROGRAM_UPDATE_DATE,
        OPERATION_SEQUENCE_ID,
	OPERATION_SEQ_NUM,
	ASSEMBLY_ITEM_NUMBER,
	ASSEMBLY_ITEM_ID,
	ALTERNATE_ROUTING_DESIGNATOR,
	EFFECTIVITY_DATE,
	RESOURCE_SEQ_NUM,
        PROCESS_FLAG,
        TRANSACTION_TYPE,
	BATCH_ID)
      SELECT
             RESOURCE_ID2,
             RESOURCE_CODE2,
             ORGANIZATION_ID,
	     ORGANIZATION_CODE,
             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),
             OPERATION_SEQUENCE_ID,
	     OPERATION_SEQ_NUM,
	     ASSEMBLY_ITEM_NUMBER,
	     ASSEMBLY_ITEM_ID,
	     ALTERNATE_ROUTING_DESIGNATOR,
	     EFFECTIVITY_DATE,
	     20,
             1,
             G_Create,
	     BATCH_ID
        FROM BOM_OP_SEQUENCES_INTERFACE
       WHERE process_flag = 1
         AND transaction_type = G_Create
	 AND
          (
              ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
              OR ( p_batch_id = batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND (RESOURCE_CODE2 is not null
              OR
              RESOURCE_ID2 is not null);

Line: 849

   INSERT into bom_op_resources_interface (
        RESOURCE_ID,
        RESOURCE_CODE,
        ORGANIZATION_ID,
	ORGANIZATION_CODE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
      CREATED_BY,
        LAST_UPDATE_LOGIN,
        REQUEST_ID,
        PROGRAM_APPLICATION_ID,
        PROGRAM_ID,
        PROGRAM_UPDATE_DATE,
        OPERATION_SEQUENCE_ID,
	OPERATION_SEQ_NUM,
	ASSEMBLY_ITEM_NUMBER,
	ASSEMBLY_ITEM_ID,
	ALTERNATE_ROUTING_DESIGNATOR,
	EFFECTIVITY_DATE,
	RESOURCE_SEQ_NUM,
        PROCESS_FLAG,
        TRANSACTION_TYPE,
	BATCH_ID)
      SELECT
             RESOURCE_ID3,
             RESOURCE_CODE3,
             ORGANIZATION_ID,
	     ORGANIZATION_CODE,
             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),
             OPERATION_SEQUENCE_ID,
	     OPERATION_SEQ_NUM,
	     ASSEMBLY_ITEM_NUMBER,
	     ASSEMBLY_ITEM_ID,
	     ALTERNATE_ROUTING_DESIGNATOR,
	     EFFECTIVITY_DATE,
	     30,
             1,
             G_Create,
	     BATCH_ID
        FROM BOM_OP_SEQUENCES_INTERFACE
       WHERE process_flag = 1
         AND transaction_type = G_Create
	 AND
          (
              ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
              OR ( p_batch_id = batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id))
         AND (RESOURCE_CODE3 is not null
              OR
              RESOURCE_ID3 is not null);

Line: 917

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

 msg_name1	 := 'BOM_ORG_ID_MISSING';

Line: 926

   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
	BOSI.transaction_id,
	MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
	Null,
	null,
	'BOM_OP_SEQUENCES_INTERFACE',
	decode ( BOSI.Organization_code, null, msg_name1,msg_name2),
	decode ( BOSI.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_OP_SEQUENCES_INTERFACE BOSI
   where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
	and transaction_id is not null
	and process_flag = 1
	and
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 973

	Update BOM_OP_SEQUENCES_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
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 1036

   UPDATE BOM_OP_RESOURCES_INTERFACE BORI
       SET(routing_sequence_id, effectivity_date, operation_seq_num)
       = (SELECT routing_sequence_id, effectivity_date, operation_seq_num
	         FROM BOM_OPERATION_SEQUENCES BOS1
	         WHERE BOS1.operation_sequence_id = BORI.operation_sequence_id )
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND OPERATION_SEQUENCE_ID is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
          OR  ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
	         FROM BOM_OPERATION_SEQUENCES BOS2
	         WHERE BOS2.OPERATION_SEQUENCE_ID = BORI.OPERATION_SEQUENCE_ID);

Line: 1057

   UPDATE BOM_OP_RESOURCES_INTERFACE BORI
       SET(assembly_item_id, organization_id, alternate_routing_designator)
       = (SELECT assembly_item_id, organization_id, alternate_routing_designator
	         FROM BOM_OPERATIONAL_ROUTINGS BOR1
	         WHERE BOR1.routing_sequence_id = BORI.routing_sequence_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND routing_sequence_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
          OR  ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
			 FROM BOM_OPERATIONAL_ROUTINGS BOR2
			 WHERE BOR2.routing_sequence_id = BORI.routing_sequence_id);

Line: 1076

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

   UPDATE BOM_OP_RESOURCES_INTERFACE BORI
       SET organization_code = (SELECT organization_code
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_id = BORI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
	       AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
          OR  ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BORI.organization_id);

Line: 1098

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

      UPDATE BOM_OP_RESOURCES_INTERFACE BORI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BORI.organization_code)
       WHERE process_flag = 1
         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 (BORI.batch_id IS NULL) )
          OR  ( p_batch_id = BORI.batch_id )
          );

Line: 1118

/* Update Assembly Item name */

   UPDATE BOM_OP_RESOURCES_INTERFACE BORI
       SET ASSEMBLY_ITEM_NUMBER  = (SELECT CONCATENATED_SEGMENTS
                                   FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                   WHERE mvl1.inventory_item_id = BORI.assembly_item_id
                                   and mvl1.organization_id = BORI.organization_id)
       WHERE process_flag = 1
         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 (BORI.batch_id IS NULL) )
          OR  ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
                      FROM MTL_SYSTEM_ITEMS mvl2
                      WHERE mvl2.inventory_item_id = BORI.assembly_item_id
		                  and mvl2.organization_id = BORI.organization_id);

Line: 1141

/* Update resource code */

   UPDATE BOM_OP_RESOURCES_INTERFACE BORI
       SET  resource_code   = (SELECT resource_code
                             FROM BOM_RESOURCES br
                             WHERE br.resource_id = BORI.resource_id
			     and br.organization_id = BORI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND resource_id is not null
	 AND organization_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
          OR  ( p_batch_id = BORI.batch_id )
          )
         AND exists (SELECT 'x'
                      FROM  BOM_RESOURCES br
                      WHERE br.resource_id = BORI.resource_id
		      and br.organization_id = BORI.organization_id);

Line: 1166

       UPDATE BOM_OP_RESOURCES_INTERFACE BORI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
          OR  ( p_batch_id = BORI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 1180

       UPDATE BOM_OP_RESOURCES_INTERFACE BORI
         SET transaction_type = upper(transaction_type)
       WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
          OR  ( p_batch_id = BORI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 1196

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

 msg_name1	 := 'BOM_ORG_ID_MISSING';

Line: 1205

   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
	BORI.transaction_id,
	MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
	Null,
	null,
	'BOM_OP_RESOURCES_INTERFACE',
	decode ( BORI.Organization_code, null, msg_name1,msg_name2),
	decode ( BORI.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_OP_RESOURCES_INTERFACE BORI
   where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
	and transaction_id is not null
	and process_flag =1
	and
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 1252

	Update BOM_OP_RESOURCES_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
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 1313

   UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
       SET(routing_sequence_id, effectivity_date, operation_seq_num)
       = (select routing_sequence_id, EFFECTIVITY_DATE, OPERATION_SEQ_NUM
	         FROM BOM_OPERATION_SEQUENCES BOS1
	         WHERE BOS1.OPERATION_SEQUENCE_ID = BSORI.operation_sequence_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND OPERATION_SEQUENCE_ID is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
          OR  ( p_batch_id = BSORI.batch_id )
          )
         AND exists (SELECT 'x'
	         FROM BOM_OPERATION_SEQUENCES BOS2
	         WHERE BOS2.OPERATION_SEQUENCE_ID = BSORI.OPERATION_SEQUENCE_ID);

Line: 1334

   UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
       SET(assembly_item_id, organization_id, alternate_routing_designator)
       = (SELECT assembly_item_id, organization_id , alternate_routing_designator
	         FROM BOM_OPERATIONAL_ROUTINGS BOR1
	         WHERE BOR1.routing_sequence_id = BSORI.routing_sequence_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND routing_sequence_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
          OR  ( p_batch_id = BSORI.batch_id )
          )
         AND exists (SELECT 'x'
			 FROM BOM_OPERATIONAL_ROUTINGS BOR2
			 WHERE BOR2.routing_sequence_id = BSORI.routing_sequence_id);

Line: 1353

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

   UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
       SET organization_code = (SELECT organization_code
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_id = BSORI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
	       AND
          (
              ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
          OR  ( p_batch_id = BSORI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BSORI.organization_id);

Line: 1375

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

      UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BSORI.organization_code)
       WHERE process_flag = 1
         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 (BSORI.batch_id IS NULL) )
          OR  ( p_batch_id = BSORI.batch_id )
          );

Line: 1395

/* Update Assembly Item name */

   UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
       SET  ASSEMBLY_ITEM_NUMBER  = (SELECT CONCATENATED_SEGMENTS
                                     FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                     WHERE mvl1.inventory_item_id = BSORI.assembly_item_id
                                     and mvl1.organization_id = BSORI.organization_id)
       WHERE process_flag = 1
         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 (BSORI.batch_id IS NULL) )
          OR  ( p_batch_id = BSORI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_SYSTEM_ITEMS mvl2
                      WHERE mvl2.inventory_item_id = BSORI.assembly_item_id
		      and mvl2.organization_id = BSORI.organization_id);

Line: 1418

/* Update resource code */

   UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
       SET sub_resource_code   = (SELECT resource_code
                             FROM BOM_RESOURCES br
                             WHERE br.resource_id = BSORI.resource_id
			     and br.organization_id = BSORI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND resource_id is not null
	 AND organization_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
          OR  ( p_batch_id = BSORI.batch_id )
          )
         AND exists (SELECT 'x'
                      FROM  BOM_RESOURCES br
                      WHERE br.resource_id = BSORI.resource_id
		      and br.organization_id = BSORI.organization_id);

Line: 1443

       UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
          OR  ( p_batch_id = BSORI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 1457

       UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
         SET transaction_type = upper(transaction_type)
       WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
          OR  ( p_batch_id = BSORI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 1472

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

 msg_name1	 := 'BOM_ORG_ID_MISSING';

Line: 1481

   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
	BSORI.transaction_id,
	MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
	Null,
	null,
	'BOM_SUB_OP_RESOURCES_INTERFACE',
	decode ( BSORI.Organization_code, null, msg_name1,msg_name2),
	decode ( BSORI.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_OP_RESOURCES_INTERFACE BSORI
   where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
	and transaction_id is not null
	and process_flag =1
	and
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 1528

	Update BOM_SUB_OP_RESOURCES_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
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 1590

   UPDATE BOM_OP_NETWORKS_INTERFACE BONI
       SET(routing_sequence_id, from_op_seq_number, from_start_effective_date)
       = (SELECT routing_sequence_id, operation_seq_num, effectivity_date
	         FROM BOM_OPERATION_SEQUENCES BOS1
	         WHERE BOS1.operation_sequence_id = BONI.from_Op_seq_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND BONI.from_Op_seq_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
          OR  ( p_batch_id = BONI.batch_id )
          )
         AND exists (SELECT 'x'
	         FROM BOM_OPERATION_SEQUENCES BOS2
	         WHERE BOS2.OPERATION_SEQUENCE_ID = BONI.FROM_OP_SEQ_ID );

Line: 1607

   UPDATE BOM_OP_NETWORKS_INTERFACE BONI
       SET(routing_sequence_id, to_op_seq_number, to_start_effective_date)
       = (SELECT routing_sequence_id, operation_seq_num, effectivity_date
	         FROM BOM_OPERATION_SEQUENCES BOS1
	         WHERE BOS1.operation_sequence_id = BONI.to_Op_seq_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND BONI.to_Op_seq_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
          OR  ( p_batch_id = BONI.batch_id )
          )
         AND exists (SELECT 'x'
	         FROM BOM_OPERATION_SEQUENCES BOS2
	         WHERE BOS2.OPERATION_SEQUENCE_ID = BONI.TO_OP_SEQ_ID );

Line: 1626

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

   UPDATE BOM_OP_NETWORKS_INTERFACE BONI
       SET(assembly_item_id, organization_id, alternate_routing_designator)
       = (SELECT assembly_item_id, organization_id , alternate_routing_designator
	         FROM BOM_OPERATIONAL_ROUTINGS BOR1
	         WHERE BOR1.routing_sequence_id = BONI.routing_sequence_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
         AND routing_sequence_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
          OR  ( p_batch_id = BONI.batch_id )
          )
         AND exists (SELECT 'x'
			 FROM BOM_OPERATIONAL_ROUTINGS BOR2
			 WHERE BOR2.routing_sequence_id = BONI.routing_sequence_id);

Line: 1647

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

   UPDATE BOM_OP_NETWORKS_INTERFACE BONI
       SET organization_code = (SELECT organization_code
                                  FROM MTL_PARAMETERS mp1
                             WHERE mp1.organization_id = BONI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
          OR  ( p_batch_id = BONI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS mp2
                      WHERE mp2.organization_id = BONI.organization_id);

Line: 1669

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

      UPDATE BOM_OP_NETWORKS_INTERFACE BONI
         SET organization_id = (SELECT organization_id
                                FROM MTL_PARAMETERS mp1
                                WHERE mp1.organization_code = BONI.organization_code)
       WHERE process_flag = 1
         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 (BONI.batch_id IS NULL) )
          OR  ( p_batch_id = BONI.batch_id )
          );

Line: 1689

/* Update Assembly Item name */

   UPDATE BOM_OP_NETWORKS_INTERFACE BONI
       SET  ASSEMBLY_ITEM_NUMBER  = (SELECT CONCATENATED_SEGMENTS
                                     FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                     WHERE mvl1.inventory_item_id = BONI.assembly_item_id
                                     and mvl1.organization_id = BONI.organization_id)
       WHERE process_flag = 1
         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 (BONI.batch_id IS NULL) )
          OR  ( p_batch_id = BONI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_SYSTEM_ITEMS mvl2
                      WHERE mvl2.inventory_item_id = BONI.assembly_item_id
		      and mvl2.organization_id = BONI.organization_id);

Line: 1713

       UPDATE BOM_OP_NETWORKS_INTERFACE BONI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
          OR  ( p_batch_id = BONI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 1727

       UPDATE BOM_OP_NETWORKS_INTERFACE BONI
         SET transaction_type = upper(transaction_type)
       WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
          OR  ( p_batch_id = BONI.batch_id )
          )
         AND (all_org = 1
             OR
            (all_org = 2 AND organization_id = org_id));

Line: 1743

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

 msg_name1	 := 'BOM_ORG_ID_MISSING';

Line: 1752

   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
	BONI.transaction_id,
	MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
	Null,
	null,
	'BOM_OP_NETWORKS_INTERFACE',
	decode ( BONI.Organization_code, null, msg_name1,msg_name2),
	decode ( BONI.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_OP_NETWORKS_INTERFACE BONI
   where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
	and transaction_id is not null
	and process_flag =1
	and
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 1799

	Update BOM_OP_NETWORKS_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
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 1857

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

   UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
       SET organization_code = (SELECT organization_code
                                  FROM MTL_PARAMETERS MP1
                             WHERE mp1.organization_id = MRIRI.organization_id)
       WHERE process_flag = 1
         AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
         AND organization_id is not null
	 AND
          (
              ( (p_batch_id IS NULL) AND (MRIRI.batch_id IS NULL) )
          OR  ( p_batch_id = MRIRI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_PARAMETERS MP2
                      WHERE mp2.organization_id = MRIRI.organization_id);

Line: 1879

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

      UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
         SET organization_id = (SELECT organization_id
                                  FROM MTL_PARAMETERS mp1
                             WHERE mp1.organization_code = MRIRI.organization_code)
       WHERE process_flag = 1
         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 (MRIRI.batch_id IS NULL) )
          OR  ( p_batch_id = MRIRI.batch_id )
          );

Line: 1899

/* Update Assembly Item name */

   UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
       SET inventory_item_number   = (SELECT concatenated_segments
                                     FROM MTL_SYSTEM_ITEMS_KFV mvl1
                                     WHERE mvl1.inventory_item_id = MRIRI.inventory_item_id
                                     and mvl1.organization_id = MRIRI.organization_id)
       WHERE process_flag = 1
         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
          (
              ( (p_batch_id IS NULL) AND (MRIRI.batch_id IS NULL) )
          OR  ( p_batch_id = MRIRI.batch_id )
          )
         AND exists (SELECT 'x'
                       FROM MTL_SYSTEM_ITEMS mvl2
                      WHERE mvl2.inventory_item_id = MRIRI.inventory_item_id
		      and mvl2.organization_id = MRIRI.organization_id);

Line: 1925

       UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
         SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
       WHERE transaction_id is null
         AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (MRIRI.batch_id IS NULL) )
          OR  ( p_batch_id = MRIRI.batch_id )
          )
         AND (all_org = 1
             OR
          (all_org = 2 AND organization_id = org_id));

Line: 1939

       UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
         SET transaction_type = upper(transaction_type),
             process_revision = upper(process_revision) -- bug 3756121
       WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
         AND process_flag = 1
	 AND
          (
              ( (p_batch_id IS NULL) AND (MRIRI.batch_id IS NULL) )
          OR  ( p_batch_id = MRIRI.batch_id )
          )
         AND (all_org = 1
             OR
          (all_org = 2 AND organization_id = org_id));

Line: 1956

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

 msg_name1	 := 'BOM_ORG_ID_MISSING';

Line: 1966

   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
	MRIRI.transaction_id,
	MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
	Null,
	null,
	'MTL_RTG_ITEM_REVS_INTERFACE',
	decode ( MRIRI.Organization_code, null, msg_name1,msg_name2),
	decode ( MRIRI.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_RTG_ITEM_REVS_INTERFACE MRIRI
   where (organization_code is null or inventory_item_number is null)
	and transaction_id is not null
	and process_flag =1
	and
	 (
	     ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
	     OR ( p_batch_id = batch_id )
	 )
	and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;

Line: 2013

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