APPS.AHL_PRD_UTIL_PKG SQL Statements

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

Line: 21

  select subinventory_code
     from mtl_item_locations_kfv
     where organization_id = p_org_id
     and inventory_location_id = p_locator_id
     and nvl(disable_Date, sysdate) >= sysdate;

Line: 87

     select 'x'
            from mtl_material_statuses
            where status_id=p_condn_id
            and enabled_flag =1;

Line: 118

      select 'x'
            from MTL_TRANSACTION_REASONS
            where reason_id=p_reason_id
            and nvl(disable_date, sysdate) >= sysdate;

Line: 158

	SELECT status_id
		FROM MTL_SECONDARY_INVENTORIES
		WHERE ORGANIZATION_ID = p_org_Id
		AND SECONDARY_INVENTORY_NAME = p_Subinv_Code;

Line: 224

SELECT
	workorder_id
FROM
	ahl_workorders
WHERE
	visit_id = p_visit_id AND
	status_code not in (22, 7, 17) AND
	master_workorder_flag <> 'Y';

Line: 240

SELECT
	vst.item_instance_id,
	vtk.instance_id
FROM
	ahl_visit_tasks_b vtk,
	ahl_visits_b vst,
	ahl_workorders wo
WHERE
	wo.workorder_id = p_workorder_id AND
	vtk.visit_task_id = wo.visit_task_id AND
	vtk.visit_id = vst.visit_id;

Line: 257

SELECT
	csi_item_instance_id
FROM
	AHL_UNIT_EFFECTIVITIES_B
WHERE
	unit_effectivity_id = p_ue_id;

Line: 347

 * Cursor for selecting workorder status validity.
 */
CURSOR c_validate_wo_status(p_workorder_id IN NUMBER) IS
SELECT
	'X'
FROM
	ahl_workorders
WHERE
	workorder_id = p_workorder_id AND
	status_code in (7, 12, 22);

Line: 369

	-- the workorder can't be updated.
	IF p_check_unit = FND_API.G_TRUE THEN
	IF Is_Unit_Locked(
			p_workorder_id		=> 	p_workorder_id,
			P_ue_id			=>	null,
			P_visit_id		=>	null,
			P_item_instance_id	=>	null
		 ) = FND_API.G_TRUE
	THEN
		RETURN FND_API.G_FALSE;

Line: 416

 * Cursor for selecting workorder status validity.
 */
CURSOR c_validate_wo_status(p_workorder_id IN NUMBER)
IS
SELECT
	'X'
FROM
	ahl_workorders
WHERE
	workorder_id = p_workorder_id AND
	status_code in (4, 5, 7, 12, 22);

Line: 432

SELECT
	'X'
FROM
	ahl_workorder_operations
WHERE
	workorder_id = p_workorder_id AND
	operation_sequence_num = p_op_seq_no AND
	status_code = 1;

Line: 507

SELECT
	mr_header_id,
	qa_collection_id
FROM
	AHL_UNIT_EFFECTIVITIES_B
WHERE
	unit_effectivity_id = p_ue_id;

Line: 520

SELECT
	DISTINCT plan_id
FROM
	QA_RESULTS
WHERE
	collection_id = p_qa_collection_id;

Line: 532

SELECT
	qa_inspection_type
FROM
	AHL_MR_HEADERS_B
WHERE
	mr_header_id = p_mr_header_id;

Line: 544

SELECT
  QP.PLAN_ID
FROM
  QA_PLANS_VAL_V QP,
  QA_PLAN_TRANSACTIONS QPT,
  QA_PLAN_COLLECTION_TRIGGERS QPCT
WHERE
  QP.PLAN_ID = QPT.PLAN_ID AND
  QPT.PLAN_TRANSACTION_ID = QPCT.PLAN_TRANSACTION_ID AND
  QP.ORGANIZATION_ID =   (SELECT
  				ORGANIZATION_ID
  			  FROM
  			  	AHL_VISITS_B VST,
  			  	AHL_VISIT_TASKS_B TSK
  			  WHERE VST.VISIT_ID = TSK.VISIT_ID AND
				TSK.UNIT_EFFECTIVITY_ID = p_ue_id AND
			  ROWNUM < 2) AND
  QPT.TRANSACTION_NUMBER IN (9999,2001)	AND
  QPCT.COLLECTION_TRIGGER_ID = 87 AND
  QPCT.LOW_VALUE = p_qa_insp_type
GROUP BY
	QP.PLAN_ID,
	QPT.TRANSACTION_NUMBER
HAVING
	TRANSACTION_NUMBER = MAX(TRANSACTION_NUMBER);

Line: 657

SELECT AWOS.STATUS_CODE,
       NVL(VTS.INSTANCE_ID, VST.ITEM_INSTANCE_ID)
FROM
	AHL_WORKORDERS AWOS,
	AHL_VISITS_B VST,
	AHL_VISIT_TASKS_B VTS
WHERE
        AWOS.VISIT_TASK_ID = VTS.VISIT_TASK_ID   AND
        VST.VISIT_ID = VTS.VISIT_ID  AND
	WORKORDER_ID = p_workorder_id;

Line: 740

SELECT
	WREQ.operation_sequence,
	WREQ.resource_sequence
FROM
	AHL_WORK_ASSIGNMENTS WASS,
	AHL_PP_REQUIREMENT_V WREQ
WHERE
	WASS.assignment_id = p_assignment_id AND
	WREQ.resource_id = WASS.operation_resource_id AND
	login_date IS NOT NULL;

Line: 763

	-- If the Unit is locked then Assignment cant be deleted return false.
	IF p_check_unit = FND_API.G_TRUE THEN
	IF Is_Unit_Locked(
			  p_workorder_id 	=> p_workorder_id,
			  P_ue_id		=>	null,
			  P_visit_id		=>	null,
			  P_item_instance_id	=>	null
			 ) = FND_API.G_TRUE
	THEN
		Fnd_Message.Set_Name('AHL', 'AHL_PP_DEL_RESASG_UNTLCKD');

Line: 797

		-- the resource assignment cannot be deleted
		RETURN FND_API.G_FALSE;

Line: 812

 * Cursor for selecting workorder status validity.
 */
CURSOR c_validate_wo_status(p_workorder_id IN NUMBER) IS
SELECT
	'X'
FROM
	ahl_workorders
WHERE
	workorder_id = p_workorder_id AND
	status_code in (1,4,5,7, 12,21, 22);

Line: 824

 * Cursor for selecting operation status validity.
 */
CURSOR c_validate_op_status(p_workorder_id IN NUMBER) IS
SELECT
	'X'
FROM
	ahl_workorder_operations
WHERE
	workorder_id = p_workorder_id AND
	status_code in (0,2);

Line: 908

SELECT STATUS_CODE
FROM AHL_WORKORDERS
WHERE WORKORDER_ID = c_wo_id;

Line: 974

SELECT 'x'
FROM AHL_WORKORDERS WO,wip_discrete_jobs WIP
WHERE WO.wip_entity_id = WIP.wip_entity_id
AND WIP.date_released IS NOT NULL AND
WO.WORKORDER_ID = c_wo_id;

Line: 1042

    SELECT SUM((AWAS.assign_end_date - AWAS.assign_start_date) * 24)
    FROM AHL_WORKORDER_OPERATIONS WO, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, BOM_RESOURCES BOM
    WHERE WO.workorder_operation_id = AOR.workorder_operation_id
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND BOM.resource_id = AOR.resource_id
      AND BOM.resource_type = 2  -- Person
      AND WO.workorder_id = p_workorder_id
      AND WO.operation_sequence_num = p_operation_seq_num
      AND AWAS.employee_id = p_employee_id;

Line: 1056

    SELECT SUM(NVL(AOR.duration, 0))
    FROM AHL_OPERATION_RESOURCES AOR,
         BOM_RESOURCES BOMR,
         AHL_WORKORDER_OPERATIONS AWOP
    WHERE AOR.RESOURCE_ID = BOMR.RESOURCE_ID
      AND BOMR.resource_type = 2  -- Person
      AND AOR.WORKORDER_OPERATION_ID = AWOP.WORKORDER_OPERATION_ID
      AND AWOP.workorder_id = p_workorder_id
      AND AWOP.operation_sequence_num = p_operation_seq_num;

Line: 1124

    SELECT SUM((AWAS.assign_end_date - AWAS.assign_start_date) * 24)
    FROM AHL_WORKORDER_OPERATIONS WO, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, BOM_RESOURCES BOM
    WHERE WO.workorder_operation_id = AOR.workorder_operation_id
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND BOM.resource_id = AOR.resource_id
      AND BOM.resource_type = 2  -- Person
      AND WO.workorder_id = p_workorder_id
      AND WO.operation_sequence_num = p_operation_seq_num
      AND AOR.resource_id = p_resource_id
      AND AWAS.employee_id = p_employee_id;

Line: 1140

    SELECT SUM((AWAS.assign_end_date - AWAS.assign_start_date) * 24)
    FROM AHL_WORKORDER_OPERATIONS WO, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, BOM_RESOURCES BOM
    WHERE WO.workorder_operation_id = AOR.workorder_operation_id
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND BOM.resource_id = AOR.resource_id
      AND BOM.resource_type = 2  -- Person
      AND WO.workorder_id = p_workorder_id
      AND WO.operation_sequence_num = p_operation_seq_num
      AND AOR.resource_id = p_resource_id;

Line: 1206

    SELECT   NVL( SUM( transaction_quantity ), 0 )
      FROM    WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
      WHERE   WT.resource_id = BRS.resource_id
        AND   BRS.resource_type = 2  -- person.
        AND   wt.wip_entity_id = p_wip_entity_id
        AND   wt.operation_seq_num = p_operation_seq_num
        AND   wt.employee_id = p_employee_id;

Line: 1218

    SELECT NVL( SUM( transaction_quantity ), 0 )
    FROM   WIP_COST_TXN_INTERFACE wcti, bom_resources br, wip_operation_resources wor
    WHERE  wcti.wip_entity_id = wor.wip_entity_id
      AND  wcti.operation_seq_num = wor.operation_seq_num
      AND  wcti.resource_seq_num = wor.resource_seq_num
      AND  wcti.organization_id = wor.organization_id
      AND  wor.resource_id = br.resource_id
      AND  br.resource_type = 2
      AND  wcti.wip_entity_id = p_wip_entity_id
      AND  wcti.operation_seq_num = p_operation_seq_num
      AND  wcti.employee_id = p_employee_id
      AND  wcti.process_status <> 3; -- skip errored txns.

Line: 1234

    SELECT   NVL( SUM( transaction_quantity ), 0 )
      FROM   WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
      WHERE  WT.resource_id = BRS.resource_id
        AND  BRS.resource_type = 2  -- person.
        AND  WT.wip_entity_id = p_wip_entity_id
        AND  WT.operation_seq_num = p_operation_seq_num;

Line: 1246

    SELECT NVL( SUM( transaction_quantity ), 0 )
    FROM   WIP_COST_TXN_INTERFACE wcti, bom_resources br, wip_operation_resources wor
    WHERE  wcti.wip_entity_id = wor.wip_entity_id
      AND  wcti.operation_seq_num = wor.operation_seq_num
      AND  wcti.resource_seq_num = wor.resource_seq_num
      AND  wcti.organization_id = wor.organization_id
      AND  wor.resource_id = br.resource_id
      AND  br.resource_type = 2
      AND  wcti.wip_entity_id = p_wip_entity_id
      AND  wcti.operation_seq_num = p_operation_seq_num
      AND  wcti.process_status <> 3; -- skip errored txns.

Line: 1333

    SELECT   NVL( SUM( transaction_quantity ), 0 )
      FROM    WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
      WHERE   WT.resource_id = BRS.resource_id
        AND   BRS.resource_type = 2  -- person.
        AND   WT.transaction_type = 1 -- resource txn.
        AND   wt.wip_entity_id = p_wip_entity_id
        AND   wt.operation_seq_num = p_operation_seq_num
        AND   wt.resource_seq_num = p_resource_seq_num
        AND   wt.employee_id = p_employee_id;

Line: 1348

    SELECT NVL( SUM( transaction_quantity ), 0 )
    FROM   WIP_COST_TXN_INTERFACE wcti, bom_resources br,
           wip_operation_resources wor
    WHERE  wcti.wip_entity_id = wor.wip_entity_id
      AND  wcti.operation_seq_num = wor.operation_seq_num
      AND  wcti.resource_seq_num = wor.resource_seq_num
      AND  wcti.organization_id = wor.organization_id
      AND  wor.resource_id = br.resource_id
      AND  br.resource_type = 2  -- person
      AND  wcti.transaction_type = 1  -- resource txn.
      AND  wcti.wip_entity_id = p_wip_entity_id
      AND  wcti.operation_seq_num = p_operation_seq_num
      AND  wcti.resource_seq_num = p_resource_seq_num
      AND  wcti.employee_id = p_employee_id
      AND  wcti.process_status <> 3; -- skip errored txns.

Line: 1368

    SELECT   NVL( SUM( transaction_quantity ), 0 )
      FROM    WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
      WHERE   WT.resource_id = BRS.resource_id
        AND   BRS.resource_type = 2  -- person.
        AND   WT.transaction_type = 1 -- resource txn.
        AND   wt.wip_entity_id = p_wip_entity_id
        AND   wt.operation_seq_num = p_operation_seq_num
        AND   wt.resource_seq_num = p_resource_seq_num;

Line: 1383

    SELECT NVL( SUM( transaction_quantity ), 0 )
    FROM   WIP_COST_TXN_INTERFACE wcti, bom_resources br,
           wip_operation_resources wor
    WHERE  wcti.wip_entity_id = wor.wip_entity_id
      AND  wcti.operation_seq_num = wor.operation_seq_num
      AND  wcti.resource_seq_num = wor.resource_seq_num
      AND  wcti.organization_id = wor.organization_id
      AND  wor.resource_id = br.resource_id
      AND  br.resource_type = 2  -- person
      AND  wcti.transaction_type = 1  -- resource txn.
      AND  wcti.wip_entity_id = p_wip_entity_id
      AND  wcti.operation_seq_num = p_operation_seq_num
      AND  wcti.resource_seq_num = p_resource_seq_num
      AND  wcti.process_status <> 3; -- skip errored txns.

Line: 1493

    SELECT MIN(AWAS.ASSIGN_START_DATE)
    FROM WIP_Operation_Resources WOR, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDERS AW
    WHERE WOR.resource_id = AOR.resource_id
      AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND WOR.wip_entity_id = AW.wip_entity_id
      AND AWAS.employee_id = p_employee_id
      AND AW.workorder_id = p_workorder_id
      AND WOR.operation_seq_num = p_operation_seq_num;

Line: 1507

    SELECT SCHEDULED_START_DATE
    FROM AHL_WORKORDER_OPERATIONS_V  AO
    WHERE AO.workorder_id = p_workorder_id
      AND AO.operation_sequence_num = p_operation_seq_num;

Line: 1576

    SELECT MAX(AWAS.ASSIGN_END_DATE)
    FROM WIP_Operation_Resources WOR, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDERS AW
    WHERE WOR.resource_id = AOR.resource_id
      AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND WOR.wip_entity_id = AW.wip_entity_id
      AND AWAS.employee_id = p_employee_id
      AND AW.workorder_id = p_workorder_id
      AND WOR.operation_seq_num = p_operation_seq_num;

Line: 1590

    SELECT SCHEDULED_END_DATE
    FROM AHL_WORKORDER_OPERATIONS_V  AO
    WHERE AO.workorder_id = p_workorder_id
      AND AO.operation_sequence_num = p_operation_seq_num;