APPS.AHL_PRD_OPERATIONS_PVT SQL Statements

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

Line: 20

  SELECT WO.route_id,
         WO.wip_entity_id,
         VST.organization_id,
         WDJ.scheduled_start_date,
         WDJ.scheduled_completion_date,
         WO.actual_start_date,
         WO.actual_end_date
  FROM   AHL_WORKORDERS WO,
         AHL_VISITS_B VST,
         WIP_DISCRETE_JOBS WDJ
  WHERE  WO.workorder_id         =c_workorder_id
  AND    WO.status_code          <> '22'
  AND    WO.visit_id             =VST.visit_id
  AND    WO.wip_entity_id        =WDJ.wip_entity_id (+);

Line: 45

  SELECT OP.operation_id,
         OP.description,
         OP.qa_inspection_type
  FROM   AHL_OPERATIONS_VL OP
  WHERE  OP.concatenated_segments=c_operation_code
  AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(OP.start_date_active,SYSDATE))
                        AND     TRUNC(NVL(OP.end_date_active,SYSDATE+1))
  AND    OP.revision_status_code='COMPLETE'
  AND    OP.revision_number IN
         ( SELECT MAX(revision_number)
           FROM   AHL_OPERATIONS_B_KFV
           WHERE  concatenated_segments=OP.concatenated_segments
           AND    TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
                                         TRUNC(NVL(end_date_active,SYSDATE+1))
         );

Line: 87

  SELECT ahl_workorder_operations_s.NEXTVAL
  INTO   p_x_prd_workoper_rec.workorder_operation_id
  FROM   DUAL;

Line: 91

  p_x_prd_workoper_rec.LAST_UPDATE_DATE     :=SYSDATE;

Line: 92

  p_x_prd_workoper_rec.LAST_UPDATED_BY      :=FND_GLOBAL.user_id;

Line: 95

  p_x_prd_workoper_rec.LAST_UPDATE_LOGIN    :=FND_GLOBAL.user_id;

Line: 243

SELECT *
FROM   AHL_WORKORDER_OPERATIONS_V
WHERE  workorder_operation_id=c_operation_id;

Line: 577

  IF p_x_prd_workoper_rec.LAST_UPDATE_DATE=FND_API.G_MISS_DATE THEN
    p_x_prd_workoper_rec.LAST_UPDATE_DATE:=NULL;

Line: 579

  ELSIF p_x_prd_workoper_rec.LAST_UPDATE_DATE IS NULL THEN
    p_x_prd_workoper_rec.LAST_UPDATE_DATE:=l_old_operation_rec.LAST_UPDATE_DATE;

Line: 583

  IF p_x_prd_workoper_rec.LAST_UPDATED_BY= FND_API.G_MISS_NUM THEN
    p_x_prd_workoper_rec.LAST_UPDATED_BY:=NULL;

Line: 585

  ELSIF p_x_prd_workoper_rec.LAST_UPDATED_BY IS NULL THEN
    p_x_prd_workoper_rec.LAST_UPDATED_BY:=l_old_operation_rec.LAST_UPDATED_BY;

Line: 601

  IF p_x_prd_workoper_rec.LAST_UPDATE_LOGIN= FND_API.G_MISS_NUM THEN
    p_x_prd_workoper_rec.LAST_UPDATE_LOGIN:=NULL;

Line: 603

  ELSIF p_x_prd_workoper_rec.LAST_UPDATE_LOGIN IS NULL THEN
    p_x_prd_workoper_rec.LAST_UPDATE_LOGIN:=l_old_operation_rec.LAST_UPDATE_LOGIN;

Line: 610

FUNCTION is_valid_operation_update(
                      	p_operation_id   IN 	NUMBER,
                      	p_wo_op_id       IN     NUMBER,
                      	p_operation_code IN 	VARCHAR2,
                      	p_dml_operation  IN 	VARCHAR2
)
RETURN NUMBER
IS

---- declare cursors here----
-- cursor for getting operation code of existing work order operation
CURSOR c_get_wo_op(p_wo_op_id IN NUMBER)
IS
SELECT
   rop.concatenated_segments
FROM
   AHL_WORKORDER_OPERATIONS wop,
   AHL_OPERATIONS_B_KFV rop
WHERE
       rop.operation_id = wop.operation_id
   AND wop.workorder_operation_id = p_wo_op_id;

Line: 635

SELECT OP.operation_id
FROM   AHL_OPERATIONS_B_KFV OP
WHERE  OP.concatenated_segments=c_operation_code
AND    OP.revision_number IN
         ( SELECT MAX(OP1.revision_number)
           FROM   AHL_OPERATIONS_B_KFV OP1
           WHERE  OP1.concatenated_segments=OP.concatenated_segments
           AND    TRUNC(SYSDATE) BETWEEN TRUNC(OP1.start_date_active) AND
                                         TRUNC(NVL(OP1.end_date_active,SYSDATE+1))
           AND    OP1.revision_status_code='COMPLETE'
         );

Line: 659

			'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
			'p_operation_id : ' || p_operation_id
		);

Line: 665

			'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
			'p_wo_op_id : ' || p_wo_op_id
		);

Line: 671

			'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
			'p_operation_code : ' || p_operation_code
		);

Line: 677

			'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
			'p_dml_operation : ' || p_dml_operation
		);

Line: 725

			'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
			'Operation id returned -> l_operation_id : ' || l_operation_id
		);

Line: 732

END is_valid_operation_update;

Line: 742

SELECT operation_id
FROM   AHL_OPERATIONS_B_KFV
WHERE  concatenated_segments=c_operation_code
AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
                      AND  TRUNC(NVL(end_date_active,SYSDATE+1))
AND    revision_status_code='COMPLETE';

Line: 751

SELECT A.Department_id,
       A.department_code,
       A.description
FROM   BOM_DEPARTMENTS A
WHERE  UPPER(A.description) LIKE UPPER(c_department_name)
AND    A.organization_id=c_org_id;

Line: 762

SELECT lookup_code
FROM   FND_LOOKUP_VALUES_VL
WHERE  lookup_type='AHL_OPERATION_TYPE'
AND    UPPER(meaning)=UPPER(c_operation_type)
AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
                      AND TRUNC(NVL(end_date_active,SYSDATE+1));

Line: 771

SELECT a.Secondary_inventory
FROM   MTL_ITEM_SUB_INVENTORIES a,
       MTL_PARAMETERS b
WHERE  a.Secondary_inventory=c_com_subinv
AND    a.organization_id=b.organization_id
AND    a.inventory_item_id=c_inventory_item_id;

Line: 781

SELECT
   TO_CHAR(FIRST_UNIT_START_DATE, 'ss') schedule_start_sec,
   TO_CHAR(LAST_UNIT_COMPLETION_DATE, 'ss') schedule_end_sec
FROM
   WIP_OPERATIONS
WHERE
   WIP_ENTITY_ID = c_wip_entity_id AND
   OPERATION_SEQ_NUM = c_op_seq_no;

Line: 793

SELECT
   TO_CHAR(ACTUAL_START_DATE, 'ss') actual_start_sec,
   TO_CHAR(ACTUAL_END_DATE, 'ss') actual_end_sec
FROM
   AHL_WORKORDER_OPERATIONS
WHERE
   workorder_operation_id = c_wo_op_id;

Line: 836

  l_operation_id := is_valid_operation_update(
                      	p_operation_id 		=>	p_x_prd_workoper_rec.operation_id,
                      	p_wo_op_id		=>	p_x_prd_workoper_rec.workorder_operation_id,
                      	p_operation_code	=>	p_x_prd_workoper_rec.operation_code,
                      	p_dml_operation 	=>	p_x_prd_workoper_rec.dml_operation
  		      );

Line: 977

SELECT  'X'
FROM    AHL_WORKORDER_OPERATIONS
WHERE   workorder_id            =c_workorder_id
AND     operation_sequence_num  =c_operation_seq_num;

Line: 986

SELECT B.eam_enabled_flag
FROM   BOM_DEPARTMENTS A,
       MTL_PARAMETERS B
WHERE  A.department_id=c_dept_id
AND    A.organization_id=B.organization_id
AND    A.organization_id=c_org_id;

Line: 995

SELECT AWOS.status_code,
							FNDL.meaning
FROM AHL_WORKORDERS AWOS,
					FND_LOOKUP_VALUES_VL FNDL
WHERE AWOS.workorder_id = c_wo_id
AND FNDL.LOOKUP_CODE(+) = AWOS.STATUS_CODE
AND FNDL.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS';

Line: 1005

SELECT
  ACTUAL_START_DATE,
  ACTUAL_END_DATE
FROM
  AHL_WORKORDERS
WHERE
  WORKORDER_ID = c_wo_id;

Line: 1022

SELECT  status_code
FROM    AHL_WORKORDER_OPERATIONS
WHERE   workorder_id            =c_workorder_id
AND     operation_sequence_num  =c_operation_seq_num;

Line: 1044

				-- Cannot update an operation which is in status 'Complete'
      FND_MESSAGE.SET_NAME('AHL','AHL_PRD_UPDOP_STS_COMP');

Line: 1237

  SELECT STATUS_CODE
  FROM AHL_WORKORDER_OPERATIONS
  WHERE WORKORDER_OPERATION_ID = x_workorder_operation_id;

Line: 1349

            AHL_DEBUG_PUB.debug( l_api_name || ' - Before Inserting into AHL_WORKORDER_OPERATIONS' );

Line: 1352

          INSERT INTO AHL_WORKORDER_OPERATIONS
          (
            WORKORDER_OPERATION_ID,
            OBJECT_VERSION_NUMBER ,
            LAST_UPDATE_DATE      ,
            LAST_UPDATED_BY       ,
            CREATION_DATE         ,
            CREATED_BY            ,
            LAST_UPDATE_LOGIN     ,
            OPERATION_SEQUENCE_NUM,
            WORKORDER_ID          ,
            STATUS_CODE           ,
            OPERATION_ID          ,
            PLAN_ID               ,
            COLLECTION_ID         ,
            OPERATION_TYPE_CODE   ,
            ACTUAL_START_DATE     ,
            ACTUAL_END_DATE       ,
            ATTRIBUTE_CATEGORY    ,
            ATTRIBUTE1            ,
            ATTRIBUTE2            ,
            ATTRIBUTE3            ,
            ATTRIBUTE4            ,
            ATTRIBUTE5            ,
            ATTRIBUTE6            ,
            ATTRIBUTE7            ,
            ATTRIBUTE8            ,
            ATTRIBUTE9            ,
            ATTRIBUTE10           ,
            ATTRIBUTE11           ,
            ATTRIBUTE12           ,
            ATTRIBUTE13           ,
            ATTRIBUTE14           ,
            ATTRIBUTE15
          ) VALUES
          (
            p_x_prd_operation_tbl(I).WORKORDER_OPERATION_ID,
            p_x_prd_operation_tbl(I).OBJECT_VERSION_NUMBER ,
            p_x_prd_operation_tbl(I).LAST_UPDATE_DATE      ,
            p_x_prd_operation_tbl(I).LAST_UPDATED_BY       ,
            p_x_prd_operation_tbl(I).CREATION_DATE         ,
            p_x_prd_operation_tbl(I).CREATED_BY            ,
            p_x_prd_operation_tbl(I).LAST_UPDATE_LOGIN     ,
            p_x_prd_operation_tbl(I).OPERATION_SEQUENCE_NUM,
            p_x_prd_operation_tbl(I).WORKORDER_ID          ,
            NVL(p_x_prd_operation_tbl(I).STATUS_CODE,'2'),
            p_x_prd_operation_tbl(I).OPERATION_ID          ,
            p_x_prd_operation_tbl(I).PLAN_ID               ,
            p_x_prd_operation_tbl(I).COLLECTION_ID         ,
            p_x_prd_operation_tbl(I).OPERATION_TYPE_CODE   ,
            p_x_prd_operation_tbl(I).ACTUAL_START_DATE     ,
            p_x_prd_operation_tbl(I).ACTUAL_END_DATE       ,
            p_x_prd_operation_tbl(I).ATTRIBUTE_CATEGORY    ,
            p_x_prd_operation_tbl(I).ATTRIBUTE1            ,
            p_x_prd_operation_tbl(I).ATTRIBUTE2            ,
            p_x_prd_operation_tbl(I).ATTRIBUTE3            ,
            p_x_prd_operation_tbl(I).ATTRIBUTE4            ,
            p_x_prd_operation_tbl(I).ATTRIBUTE5            ,
            p_x_prd_operation_tbl(I).ATTRIBUTE6            ,
            p_x_prd_operation_tbl(I).ATTRIBUTE7            ,
            p_x_prd_operation_tbl(I).ATTRIBUTE8            ,
            p_x_prd_operation_tbl(I).ATTRIBUTE9            ,
            p_x_prd_operation_tbl(I).ATTRIBUTE10           ,
            p_x_prd_operation_tbl(I).ATTRIBUTE11           ,
            p_x_prd_operation_tbl(I).ATTRIBUTE12           ,
            p_x_prd_operation_tbl(I).ATTRIBUTE13           ,
            p_x_prd_operation_tbl(I).ATTRIBUTE14           ,
            p_x_prd_operation_tbl(I).ATTRIBUTE15
          );

Line: 1441

          UPDATE AHL_WORKORDER_OPERATIONS SET
            OBJECT_VERSION_NUMBER   =p_x_prd_operation_tbl(I).OBJECT_VERSION_NUMBER +1,
            LAST_UPDATE_DATE        =NVL(p_x_prd_operation_tbl(I).LAST_UPDATE_DATE,SYSDATE),
            LAST_UPDATED_BY         =NVL(p_x_prd_operation_tbl(I).LAST_UPDATED_BY,FND_GLOBAL.user_id),
            OPERATION_SEQUENCE_NUM  =p_x_prd_operation_tbl(I).OPERATION_SEQUENCE_NUM,
            WORKORDER_ID            =p_x_prd_operation_tbl(I).WORKORDER_ID          ,
            STATUS_CODE             =p_x_prd_operation_tbl(I).STATUS_CODE          ,
            OPERATION_ID            =p_x_prd_operation_tbl(I).OPERATION_ID          ,
            PLAN_ID                 =p_x_prd_operation_tbl(I).PLAN_ID               ,
            COLLECTION_ID           =p_x_prd_operation_tbl(I).COLLECTION_ID         ,
            OPERATION_TYPE_CODE     =p_x_prd_operation_tbl(I).OPERATION_TYPE_CODE   ,
            ACTUAL_START_DATE       =p_x_prd_operation_tbl(I).ACTUAL_START_DATE     ,
            ACTUAL_END_DATE         =p_x_prd_operation_tbl(I).ACTUAL_END_DATE       ,
            ATTRIBUTE_CATEGORY      =p_x_prd_operation_tbl(I).ATTRIBUTE_CATEGORY    ,
            ATTRIBUTE1              =p_x_prd_operation_tbl(I).ATTRIBUTE1            ,
            ATTRIBUTE2              =p_x_prd_operation_tbl(I).ATTRIBUTE2            ,
            ATTRIBUTE3              =p_x_prd_operation_tbl(I).ATTRIBUTE3            ,
            ATTRIBUTE4              =p_x_prd_operation_tbl(I).ATTRIBUTE4            ,
            ATTRIBUTE5              =p_x_prd_operation_tbl(I).ATTRIBUTE5            ,
            ATTRIBUTE6              =p_x_prd_operation_tbl(I).ATTRIBUTE6            ,
            ATTRIBUTE7              =p_x_prd_operation_tbl(I).ATTRIBUTE7            ,
            ATTRIBUTE8              =p_x_prd_operation_tbl(I).ATTRIBUTE8            ,
            ATTRIBUTE9              =p_x_prd_operation_tbl(I).ATTRIBUTE9            ,
            ATTRIBUTE10             =p_x_prd_operation_tbl(I).ATTRIBUTE10           ,
            ATTRIBUTE11             =p_x_prd_operation_tbl(I).ATTRIBUTE11           ,
            ATTRIBUTE12             =p_x_prd_operation_tbl(I).ATTRIBUTE12           ,
            ATTRIBUTE13             =p_x_prd_operation_tbl(I).ATTRIBUTE13           ,
            ATTRIBUTE14             =p_x_prd_operation_tbl(I).ATTRIBUTE14           ,
            ATTRIBUTE15             =p_x_prd_operation_tbl(I).ATTRIBUTE15
          WHERE  WORKORDER_OPERATION_ID=p_x_prd_operation_tbl(I).WORKORDER_OPERATION_ID
          AND    OBJECT_VERSION_NUMBER =p_x_prd_operation_tbl(I).OBJECT_VERSION_NUMBER;

Line: 1497

      AHL_DEBUG_PUB.debug( l_api_name || ' - Before AHL_EAM_JOB_PVT.update_job_operations' );

Line: 1500

    AHL_EAM_JOB_PVT.update_job_operations
    (
      p_api_version            => 1.0                        ,
      p_init_msg_list          => FND_API.G_TRUE             ,
      p_commit                 => FND_API.G_FALSE            ,
      p_validation_level       => FND_API.G_VALID_LEVEL_FULL ,
      p_default                => FND_API.G_TRUE             ,
      p_module_type            => NULL                       ,
      x_return_status          => l_return_status            ,
      x_msg_count              => l_msg_count                ,
      x_msg_data               => l_msg_data                 ,
      p_workorder_rec          => l_empty_workorder_rec      ,
      p_operation_tbl          => p_x_prd_operation_tbl      ,
      p_material_req_tbl       => l_material_tbl             ,
      p_resource_req_tbl       => l_resource_tbl
    );