APPS.MODAL_DELETE SQL Statements

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

Line: 66

  SELECT BOM_DELETE_GROUPS_S.NEXTVAL group_id
  FROM SYS.DUAL;

Line: 69

  Select bde.delete_entity_sequence_id
  From   bom_delete_entities bde
  Where  bde.delete_group_sequence_id = new_group_seq_id_v
  And    nvl(bde.bill_sequence_id, 0) = nvl(ent_bill_seq_id, 0)
  And	 nvl(bde.routing_sequence_id, 0) = nvl(ent_rtg_seq_id, 0)
  And    nvl(bde.inventory_item_id, 0) = nvl(ent_inv_item_id, 0)
  And	 nvl(bde.organization_id, 0) = nvl(org_id, 0);

Line: 76

X_EntSeqId bom_delete_entities.delete_entity_sequence_id%type := null;

Line: 78

  Select BOM_DELETE_ENTITIES_S.NEXTVAL Entity_Id
  From sys.dual;

Line: 81

  SELECT BIC.COMPONENT_SEQUENCE_ID,
         BIC.EFFECTIVITY_DATE,
         BIC.DISABLE_DATE,
         BIC.FROM_END_ITEM_UNIT_NUMBER,
         BIC.TO_END_ITEM_UNIT_NUMBER,
         BIC.ITEM_NUM,
         BIC.OPERATION_SEQ_NUM,
         BIC.COMPONENT_ITEM_ID,
         MSIK.CONCATENATED_SEGMENTS,
         MSIK.DESCRIPTION
  FROM MTL_SYSTEM_ITEMS_KFV MSIK,
       BOM_INVENTORY_COMPONENTS BIC
  WHERE BIC.COMPONENT_SEQUENCE_ID = ent_comp_seq_id
  AND MSIK.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
  AND MSIK.ORGANIZATION_ID = org_id
  AND NOT EXISTS (
    SELECT NULL
    FROM BOM_DELETE_SUB_ENTITIES BDSE
    WHERE BDSE.DELETE_ENTITY_SEQUENCE_ID = X_EntSeqId
    AND   BDSE.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID);

Line: 102

  SELECT BOS.OPERATION_SEQUENCE_ID,
         BOS.EFFECTIVITY_DATE,
         BOS.DISABLE_DATE,
         BOS.OPERATION_SEQ_NUM,
         BOS.OPERATION_DESCRIPTION,
         BD.DEPARTMENT_CODE
  FROM BOM_DEPARTMENTS BD,
       BOM_OPERATION_SEQUENCES BOS
  WHERE BOS.OPERATION_SEQUENCE_ID = ent_op_seq_id
  AND BOS.DEPARTMENT_ID = BD.DEPARTMENT_ID
  AND NOT EXISTS (
    SELECT NULL
    FROM BOM_DELETE_SUB_ENTITIES BDSE
    WHERE BDSE.DELETE_ENTITY_SEQUENCE_ID = X_EntSeqId
    AND   BDSE.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID);

Line: 118

     SAVEPOINT BEGIN_DELETE;

Line: 127

          INSERT INTO BOM_DELETE_GROUPS
		(DELETE_GROUP_SEQUENCE_ID,
 	 	DELETE_GROUP_NAME,
		ORGANIZATION_ID,
	 	DELETE_TYPE,
	 	ACTION_TYPE,
	 	DATE_LAST_SUBMITTED,
	 	DESCRIPTION,
	 	ENGINEERING_FLAG,
	 	LAST_UPDATE_DATE,
	 	LAST_UPDATED_BY,
	 	CREATION_DATE,
	 	CREATED_BY,
	 	LAST_UPDATE_LOGIN,
	 	REQUEST_ID,
	 	PROGRAM_APPLICATION_ID,
	 	PROGRAM_ID,
	 	PROGRAM_UPDATE_DATE,
                ORGANIZATION_HIERARCHY,
                DELETE_ORG_TYPE,
                DELETE_COMMON_BILL_FLAG)
     	  VALUES
		(new_group_seq_id_v,
	 	name,
	 	org_id,
	 	del_type,
	 	1,
	 	NULL,
	 	group_desc,
	 	bom_or_eng,
	 	SYSDATE,
	 	user_id,
	 	SYSDATE,
	 	user_id,
	 	user_id,
	 	NULL,
	 	NULL,
	 	NULL,
	 	NULL,
                NULL,
                1,
                2);

Line: 184

  X_EntSeqId := X_Entity.delete_entity_sequence_id;

Line: 192

    INSERT INTO bom_delete_entities
	(DELETE_ENTITY_SEQUENCE_ID,
	 DELETE_GROUP_SEQUENCE_ID,
	 DELETE_ENTITY_TYPE,
	 BILL_SEQUENCE_ID,
	 ROUTING_SEQUENCE_ID,
	 INVENTORY_ITEM_ID,
	 ORGANIZATION_ID,
	 ALTERNATE_DESIGNATOR,
	 ITEM_DESCRIPTION,
	 ITEM_CONCAT_SEGMENTS,
	 DELETE_STATUS_TYPE,
	 DELETE_DATE,
	 PRIOR_PROCESS_FLAG,
	 PRIOR_COMMIT_FLAG,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_LOGIN,
	 REQUEST_ID ,
	 PROGRAM_APPLICATION_ID,
	 PROGRAM_ID,
	 PROGRAM_UPDATE_DATE)
    SELECT
	 X_EntSeqId,
	 new_group_seq_id_v,
	 del_header,
	 ent_bill_seq_id,
	 ent_rtg_seq_id,
	 ent_inv_item_id,
	 org_id,
	 ent_alt_designator,
         MSIK.DESCRIPTION,
	 MSIK.CONCATENATED_SEGMENTS,
	 status_value,  		-- PENDING
	 NULL,		-- Delete date should be null
	 2,		-- Prior process flag
	 1,		-- Prior Commit flag
	 SYSDATE,
	 user_id,
	 SYSDATE,
	 user_id,
	 user_id,
	 NULL,
	 NULL,
	 NULL,
	 NULL
    FROM MTL_SYSTEM_ITEMS_KFV MSIK
    WHERE MSIK.INVENTORY_ITEM_ID = ent_inv_item_id
    AND MSIK.ORGANIZATION_ID = org_id;

Line: 253

    INSERT INTO bom_delete_sub_entities
	(DELETE_ENTITY_SEQUENCE_ID,
	 COMPONENT_SEQUENCE_ID,
 	 OPERATION_SEQUENCE_ID,
	 OPERATION_SEQ_NUM,
	 EFFECTIVITY_DATE,
	 FROM_END_ITEM_UNIT_NUMBER,
	 COMPONENT_ITEM_ID,
	 COMPONENT_CONCAT_SEGMENTS,
	 ITEM_NUM,
	 DISABLE_DATE,
	 TO_END_ITEM_UNIT_NUMBER,
	 DESCRIPTION,
	 OPERATION_DEPARTMENT_CODE,
	 DELETE_STATUS_TYPE,
	 DELETE_DATE,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_LOGIN,
	 REQUEST_ID,
	 PROGRAM_APPLICATION_ID,
	 PROGRAM_ID,
	 PROGRAM_UPDATE_DATE)
       VALUES
	(X_EntSeqId,
	 X_Component.COMPONENT_SEQUENCE_ID,
 	 null,
	 X_Component.OPERATION_SEQ_NUM,
	 X_Component.EFFECTIVITY_DATE,
	 X_Component.FROM_END_ITEM_UNIT_NUMBER,
	 X_Component.COMPONENT_ITEM_ID,
	 X_Component.CONCATENATED_SEGMENTS,
	 X_Component.ITEM_NUM,
	 X_Component.DISABLE_DATE,
	 X_Component.TO_END_ITEM_UNIT_NUMBER,
	 X_Component.DESCRIPTION,
	 null,
	 1,
	 NULL,
	 SYSDATE,
	 user_id,
	 SYSDATE,
	 user_id,
	 user_id,
	 NULL,
	 NULL,
	 NULL,
	 NULL);

Line: 303

  End loop; -- insert component

Line: 308

    INSERT INTO bom_delete_sub_entities
	(DELETE_ENTITY_SEQUENCE_ID,
	 COMPONENT_SEQUENCE_ID,
 	 OPERATION_SEQUENCE_ID,
	 OPERATION_SEQ_NUM,
	 EFFECTIVITY_DATE,
	 COMPONENT_ITEM_ID,
	 COMPONENT_CONCAT_SEGMENTS,
	 ITEM_NUM,
	 DISABLE_DATE,
	 DESCRIPTION,
	 OPERATION_DEPARTMENT_CODE,
	 DELETE_STATUS_TYPE,
	 DELETE_DATE,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_LOGIN,
	 REQUEST_ID,
	 PROGRAM_APPLICATION_ID,
	 PROGRAM_ID,
	 PROGRAM_UPDATE_DATE)
       VALUES
	(X_EntSeqId,
	 null,
 	 X_Operation.OPERATION_SEQUENCE_ID,
	 X_Operation.OPERATION_SEQ_NUM,
	 X_Operation.EFFECTIVITY_DATE,
	 null,
	 null,
	 null,
	 X_Operation.DISABLE_DATE,
	 X_Operation.OPERATION_DESCRIPTION,
	 X_Operation.DEPARTMENT_CODE,
	 1,
	 NULL,
	 SYSDATE,
	 user_id,
	 SYSDATE,
	 user_id,
	 user_id,
	 NULL,
	 NULL,
	 NULL,
	 NULL);

Line: 354

  End loop; -- insert operation

Line: 361

		err_msg := 'MODAL DELETE (' ||sql_stmt_num||' ) ' || SQLERRM;

Line: 364

		ROLLBACK TO BEGIN_DELETE;

Line: 366

END DELETE_MANAGER;

Line: 369

/* --------------------------- Delete_Manager_Oi ----------------------------*/
/*
NAME
     Delete_Manager_Oi
DESCRIPTION
     Create Delete Groups for Bills, Components, Routings and Operations for
     the Open Interface program.
REQUIRES
                new_group_seq_id        Seq Id of new group
                name                    Delete Group name
                group_desc              Delete Group description
                org_id                  Org Id
                bom_or_eng              1 - bom
					2 - eng
                del_type		2 - Bill
					3 - Routing
					4 - Component
					5 - Operation
                ent_bill_seq_id         Bill Seq Id
                ent_rtg_seq_id          Routing Seq Id
                ent_inv_item_id         Bill or Routing Item Id
                ent_alt_designator      Bill or Routing Alternate
                ent_comp_seq_id         Component Sequence Id
                ent_op_seq_id           Operation Sequence Id
                user_id                 User Id
MODIFIES
     BOM_DELETE_GROUPS
     BOM_DELETE_ENTITIES
     BOM_DELETE_SUB_ENTITIES
RETURNS
     0 if successful
     SQLCODE if error
NOTES
-----------------------------------------------------------------------------*/
FUNCTION DELETE_MANAGER_OI(
                new_group_seq_id        IN NUMBER,
                name                    IN VARCHAR2,
                group_desc              IN VARCHAR2,
                org_id                  IN NUMBER,
                bom_or_eng              IN NUMBER,
                del_type                IN NUMBER,
                ent_bill_seq_id         IN NUMBER,
                ent_rtg_seq_id          IN NUMBER,
                ent_inv_item_id         IN NUMBER,
                ent_alt_designator      IN VARCHAR2,
                ent_comp_seq_id         IN NUMBER,
                ent_op_seq_id           IN NUMBER,
                user_id                 IN NUMBER,
		err_text	       IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
                   ) RETURN NUMBER IS
   ITEM    CONSTANT NUMBER 			:= 1;

Line: 432

     Select delete_type
     From BOM_DELETE_GROUPS
     WHERE DELETE_GROUP_NAME = name;

Line: 437

     SELECT BOM_DELETE_GROUPS_S.NEXTVAL group_id
       FROM SYS.DUAL;

Line: 441

     Select bde.delete_entity_sequence_id
       From bom_delete_entities bde
      Where bde.delete_group_sequence_id = new_group_seq_id_v
        And nvl(bde.bill_sequence_id, 0) = nvl(ent_bill_seq_id, 0)
        And nvl(bde.routing_sequence_id, 0) = nvl(ent_rtg_seq_id, 0)
        And nvl(bde.inventory_item_id, 0) = nvl(ent_inv_item_id, 0)
        And nvl(bde.organization_id, 0) = nvl(org_id, 0);

Line: 449

   X_EntSeqId bom_delete_entities.delete_entity_sequence_id%type := null;

Line: 452

     Select BOM_DELETE_ENTITIES_S.NEXTVAL Entity_Id
       From sys.dual;

Line: 456

     SELECT BIC.COMPONENT_SEQUENCE_ID,
            BIC.EFFECTIVITY_DATE,
            BIC.DISABLE_DATE,
            BIC.ITEM_NUM,
            BIC.OPERATION_SEQ_NUM,
            BIC.COMPONENT_ITEM_ID,
            MSIK.CONCATENATED_SEGMENTS,
            MSIK.DESCRIPTION
       FROM MTL_SYSTEM_ITEMS_KFV MSIK,
            BOM_INVENTORY_COMPONENTS BIC
      WHERE BIC.COMPONENT_SEQUENCE_ID = ent_comp_seq_id
        AND MSIK.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
        AND MSIK.ORGANIZATION_ID = org_id
        AND NOT EXISTS (
            SELECT NULL
              FROM BOM_DELETE_SUB_ENTITIES BDSE
             WHERE BDSE.DELETE_ENTITY_SEQUENCE_ID = X_EntSeqId
               AND BDSE.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID);

Line: 476

     SELECT BOS.OPERATION_SEQUENCE_ID,
            BOS.EFFECTIVITY_DATE,
            BOS.DISABLE_DATE,
            BOS.OPERATION_SEQ_NUM,
            BOS.OPERATION_DESCRIPTION,
            BD.DEPARTMENT_CODE
       FROM BOM_DEPARTMENTS BD,
            BOM_OPERATION_SEQUENCES BOS
      WHERE BOS.OPERATION_SEQUENCE_ID = ent_op_seq_id
        AND BOS.DEPARTMENT_ID = BD.DEPARTMENT_ID
        AND NOT EXISTS (
            SELECT NULL
              FROM BOM_DELETE_SUB_ENTITIES BDSE
             WHERE BDSE.DELETE_ENTITY_SEQUENCE_ID = X_EntSeqId
               AND BDSE.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID);

Line: 504

   SAVEPOINT BEGIN_DELETE;

Line: 509

      if  (X_DelType.delete_type not in (2,6)) then
       err_text:= 'Delete Grp type:'||to_char(X_DelType.delete_type) ||
                 'Delete Type:'||to_char(del_type)||
                 'Invalid delete group type';

Line: 516

       if (X_DelType.delete_type not in (3,6)) then
        err_text:= 'Delete Grp type:'||to_char(X_DelType.delete_type) ||
                 'Delete Type:'||to_char(del_type)||
                 'Invalid delete group type';

Line: 522

     elsif X_DelType.delete_type <> del_type then
        err_text:= 'Delete Grp type:'||to_char(X_DelType.delete_type) ||
                 'Delete Type:'||to_char(del_type)||
                 'Invalid delete group type';

Line: 537

          INSERT INTO BOM_DELETE_GROUPS
		(DELETE_GROUP_SEQUENCE_ID,
 	 	DELETE_GROUP_NAME,
		ORGANIZATION_ID,
	 	DELETE_TYPE,
	 	ACTION_TYPE,
	 	DATE_LAST_SUBMITTED,
	 	DESCRIPTION,
	 	ENGINEERING_FLAG,
	 	LAST_UPDATE_DATE,
	 	LAST_UPDATED_BY,
	 	CREATION_DATE,
	 	CREATED_BY,
	 	LAST_UPDATE_LOGIN,
	 	REQUEST_ID,
	 	PROGRAM_APPLICATION_ID,
	 	PROGRAM_ID,
	 	PROGRAM_UPDATE_DATE,
                ORGANIZATION_HIERARCHY,
                DELETE_ORG_TYPE,
                DELETE_COMMON_BILL_FLAG)
     	  VALUES
		(new_group_seq_id_v,
	 	name,
	 	org_id,
	 	del_type,
	 	1,
	 	NULL,
	 	group_desc,
	 	bom_or_eng,
	 	SYSDATE,
	 	l_UserId,
	 	SYSDATE,
	 	l_UserId,
	 	l_LoginId,
	 	l_RequestId,
	 	l_ApplicationId,
	 	l_ProgramId,
	 	sysdate,
                NULL,
                1,
                2);

Line: 594

  X_EntSeqId := X_Entity.delete_entity_sequence_id;

Line: 602

    INSERT INTO bom_delete_entities
	(DELETE_ENTITY_SEQUENCE_ID,
	 DELETE_GROUP_SEQUENCE_ID,
	 DELETE_ENTITY_TYPE,
	 BILL_SEQUENCE_ID,
	 ROUTING_SEQUENCE_ID,
	 INVENTORY_ITEM_ID,
	 ORGANIZATION_ID,
	 ALTERNATE_DESIGNATOR,
	 ITEM_DESCRIPTION,
	 ITEM_CONCAT_SEGMENTS,
	 DELETE_STATUS_TYPE,
	 DELETE_DATE,
	 PRIOR_PROCESS_FLAG,
	 PRIOR_COMMIT_FLAG,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_LOGIN,
	 REQUEST_ID ,
	 PROGRAM_APPLICATION_ID,
	 PROGRAM_ID,
	 PROGRAM_UPDATE_DATE)
    SELECT
	 X_EntSeqId,
	 new_group_seq_id_v,
	 del_header,
	 ent_bill_seq_id,
	 ent_rtg_seq_id,
	 ent_inv_item_id,
	 org_id,
	 ent_alt_designator,
         MSIK.DESCRIPTION,
	 MSIK.CONCATENATED_SEGMENTS,
	 status_value,  		-- PENDING
	 NULL,		-- Delete date should be null
	 2,		-- Prior process flag
	 1,		-- Prior Commit flag
	 SYSDATE,
	 l_UserId,
	 SYSDATE,
	 l_UserId,
	 l_LoginId,
	 l_RequestId,
	 l_ApplicationId,
	 l_ProgramId,
	 sysdate
    FROM MTL_SYSTEM_ITEMS_KFV MSIK
    WHERE MSIK.INVENTORY_ITEM_ID = ent_inv_item_id
    AND MSIK.ORGANIZATION_ID = org_id;

Line: 663

    INSERT INTO bom_delete_sub_entities
	(DELETE_ENTITY_SEQUENCE_ID,
	 COMPONENT_SEQUENCE_ID,
 	 OPERATION_SEQUENCE_ID,
	 OPERATION_SEQ_NUM,
	 EFFECTIVITY_DATE,
	 COMPONENT_ITEM_ID,
	 COMPONENT_CONCAT_SEGMENTS,
	 ITEM_NUM,
	 DISABLE_DATE,
	 DESCRIPTION,
	 OPERATION_DEPARTMENT_CODE,
	 DELETE_STATUS_TYPE,
	 DELETE_DATE,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_LOGIN,
	 REQUEST_ID,
	 PROGRAM_APPLICATION_ID,
	 PROGRAM_ID,
	 PROGRAM_UPDATE_DATE)
       VALUES
	(X_EntSeqId,
	 X_Component.COMPONENT_SEQUENCE_ID,
 	 null,
	 X_Component.OPERATION_SEQ_NUM,
	 X_Component.EFFECTIVITY_DATE,
	 X_Component.COMPONENT_ITEM_ID,
	 X_Component.CONCATENATED_SEGMENTS,
	 X_Component.ITEM_NUM,
	 X_Component.DISABLE_DATE,
	 X_Component.DESCRIPTION,
	 null,
	 1,
	 NULL,
	 SYSDATE,
	 l_UserId,
	 SYSDATE,
	 l_UserId,
	 l_LoginId,
	 l_RequestId,
	 l_ApplicationId,
	 l_ProgramId,
	 sysdate);

Line: 709

  End loop; -- insert component

Line: 714

    INSERT INTO bom_delete_sub_entities
	(DELETE_ENTITY_SEQUENCE_ID,
	 COMPONENT_SEQUENCE_ID,
 	 OPERATION_SEQUENCE_ID,
	 OPERATION_SEQ_NUM,
	 EFFECTIVITY_DATE,
	 COMPONENT_ITEM_ID,
	 COMPONENT_CONCAT_SEGMENTS,
	 ITEM_NUM,
	 DISABLE_DATE,
	 DESCRIPTION,
	 OPERATION_DEPARTMENT_CODE,
	 DELETE_STATUS_TYPE,
	 DELETE_DATE,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_LOGIN,
	 REQUEST_ID,
	 PROGRAM_APPLICATION_ID,
	 PROGRAM_ID,
	 PROGRAM_UPDATE_DATE)
       VALUES
	(X_EntSeqId,
	 null,
 	 X_Operation.OPERATION_SEQUENCE_ID,
	 X_Operation.OPERATION_SEQ_NUM,
	 X_Operation.EFFECTIVITY_DATE,
	 null,
	 null,
	 null,
	 X_Operation.DISABLE_DATE,
	 X_Operation.OPERATION_DESCRIPTION,
	 X_Operation.DEPARTMENT_CODE,
	 1,
	 NULL,
	 SYSDATE,
	 l_UserId,
	 SYSDATE,
	 l_UserId,
	 l_LoginId,
	 l_RequestId,
	 l_ApplicationId,
	 l_ProgramId,
	 sysdate);

Line: 760

  End loop; -- insert operation

Line: 767

     ROLLBACK TO BEGIN_DELETE;

Line: 772

      ROLLBACK TO BEGIN_DELETE;

Line: 774

END DELETE_MANAGER_OI;