APPS.AHL_PRD_RESOURCE_TRANX_PVT SQL Statements

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

Line: 15

Select wip_entity_id, workorder_name, status_code
from AHL_WORKORDERS
Where Workorder_id=C_WORKORDER_ID;

Line: 35

SELECT bomr.resource_code,
bomr.resource_type, aor.resource_sequence_num
FROM BOM_RESOURCES bomr,
AHL_WORKORDER_OPERATIONS awop,
AHL_OPERATION_RESOURCES aor
WHERE awop.workorder_operation_id = aor.workorder_operation_id(+)
AND bomr.resource_id = aor.resource_id(+)
AND awop.operation_sequence_num = c_op_seq_num
--AND aor.resource_sequence_num = c_res_seq_num
AND bomr.resource_code = c_res_code
AND awop.workorder_id = c_wo_id
AND bomr.organization_id = c_org_id;

Line: 53

SELECT bomr.resource_code, bomr.resource_type
FROM BOM_RESOURCES bomr
WHERE bomr.organization_id = c_org_id
  and bomr.resource_code = c_res_code;

Line: 58

/*Select * from bom_resources
where resource_code=c_resource_name
and   organization_id=c_org_id;*/

Line: 69

    SELECT instance_id
    FROM bom_dept_res_instances
    WHERE department_id in (
    		   select
  			 distinct nvl(bodres.share_from_dept_id, bodres.department_id)
  		   from
  			 bom_departments bomdep,
  			 bom_department_resources bodres
  		   where
  			 bodres.department_id = bomdep.department_id and
  			 bomdep.department_id = c_department_id and
  			 bomdep.organization_id = c_organization_id
    )
    and   Serial_Number=c_serial_number
    and   Resource_id=c_resource_id;

Line: 96

    SELECT 'x'
    FROM  mtl_employees_current_view pf, bom_resource_employees bre, bom_dept_res_instances bdri
          --, ahl_pp_requirement_v aprv
    WHERE --aprv.department_id = bdri.department_id
      --and aprv.RESOURCE_ID= bdri.resource_id
      --and
      bre.instance_id = bdri.instance_id
      and pf.employee_id=bre.person_id
      and pf.organization_id = bre.organization_id
      and bdri.resource_id = p_resrc_id
      --and aprv.OPERATION_SEQUENCE = p_oper_seq
      --and aprv.RESOURCE_SEQUENCE = p_resrc_seq
      --and aprv.RESOURCE_ID = p_resrc_id
      --and aprv.job_id= p_job_id
      and bre.organization_id= p_org_id
      and pf.employee_num = p_emp_num;

Line: 123

    SELECT 'x'
    FROM  bom_resource_employees bre, bom_dept_res_instances bdri --,
          --ahl_pp_requirement_v aprv
    WHERE --aprv.department_id = bdri.department_id
      --and aprv.RESOURCE_ID= bdri.resource_id
      --and
      bre.instance_id = bdri.instance_id
      and bre.person_id = p_emp_id
      and bdri.resource_id = p_resrc_id
      --and aprv.OPERATION_SEQUENCE = p_oper_seq
      --and aprv.RESOURCE_SEQUENCE = p_resrc_seq
      --and aprv.RESOURCE_ID = p_resrc_id
      --and aprv.job_id= p_job_id
      and bre.organization_id= p_org_id;

Line: 142

SELECT DATE_RELEASED
FROM WIP_DISCRETE_JOBS
WHERE WIP_ENTITY_ID = c_wip_entity_id;

Line: 149

Select vst.organization_id
from  ahl_workorders wo,
ahl_visits_b vst
where vst.visit_id = wo.visit_id
and wo.workorder_id = c_wo_id;

Line: 284

                Select 1 into l_ctr
                from AHL_WORKORDER_OPERATIONS A
                WHERE A.WORKORDER_ID=p_prd_resrc_txn_tbl(I).workorder_id
                AND A.OPERATION_SEQUENCE_NUM=p_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM;

Line: 302

            Select 1 into l_ctr
            From AHL_OPERATION_RESOURCES  A
            WHERE A.WORKORDER_OPERATION_ID=p_prd_resrc_txn_tbl(I).workorder_OPERATION_id
            AND A.RESOURCE_SEQUENCE_NUM=p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM;

Line: 583

Select workorder_operation_id, department_id, department_code
from  ahl_workorder_operations_v
where operation_sequence_num=c_oper_seq
and   workorder_id=c_work_id;

Line: 589

Select awo.workorder_operation_id, wop.department_id, bd.department_code,
       wo.organization_id
from ahl_workorder_tasks_v wo, ahl_workorder_operations awo, bom_departments bd,
     wip_operations wop
where wo.wip_entity_id = wop.wip_entity_id
and wop.operation_seq_num = c_Oper_seq
and wop.department_id = bd.department_id
and wo.workorder_id = awo.workorder_id
and awo.operation_sequence_num=c_oper_seq
and wo.workorder_id=c_work_id;

Line: 605

Select a.resource_id, BR.UNIT_OF_MEASURE UOM_CODE, br.resource_code
from ahl_operation_resources a, bom_resources br
where a.resource_id = br.resource_id
and a.workorder_operation_id = c_workorder_operation_id
and resource_sequence_num=c_res_seq;

Line: 616

Select department_id
From  BOM_DEPARTMENTS
Where department_code=C_department_code
and   organization_id=c_org_id;

Line: 627

Select  AOR.RESOURCE_ID, BOM.UNIT_OF_MEASURE UOM_CODE
FROM AHL_OPERATION_RESOURCES AOR, AHL_WORKORDER_OPERATIONS AWO , BOM_RESOURCES BOM, MFG_LOOKUPS MFG
WHERE AOR.RESOURCE_SEQUENCE_NUM = C_RES_SEQ
AND AOR.WORKORDER_OPERATION_ID = AWO.WORKORDER_OPERATION_ID
AND AWO.WORKORDER_ID = WORK_ID
AND AOR.RESOURCE_ID = BOM.RESOURCE_ID
AND MFG.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'
AND MFG.LOOKUP_CODE(+) = BOM.RESOURCE_TYPE;

Line: 637

/*Select  * -- resource_id, UOM_CODE
From  AHL_PP_REQUIREMENT_V
Where JOB_ID=WORK_ID
AND   RESOURCE_SEQUENCE=C_RES_SEQ;*/

Line: 647

SELECT BR.RESOURCE_ID, aor.resource_sequence_num, BR.UNIT_OF_MEASURE UOM_CODE
FROM BOM_RESOURCES BR, BOM_DEPARTMENT_RESOURCES BDR, ahl_operation_resources aor
WHERE BR.RESOURCE_ID = BDR.RESOURCE_ID
  AND BDR.DEPARTMENT_ID = p_dept_id
  AND BR.RESOURCE_CODE = p_resource_code
  AND BR.ORGANIZATION_ID = p_org_id
  AND aor.resource_id(+) = BDR.resource_id
  AND aor.workorder_operation_id(+) = p_workorder_operation_id;

Line: 660

Select activity_id
From  CST_ACTIVITIES
Where ACTIVITY=C_ACTIVITY;

Line: 666

Select Reason_id
From   mtl_transaction_reasons
Where  reason_name=C_Reason
AND  NVL(disable_date,SYSDATE+1) >=TRUNC(SYSDATE);

Line: 674

SELECT person_id,employee_number,full_name
FROM per_all_people_f pf,per_person_types pt
WHERE pf.person_type_id = pt.person_type_id
AND   pt.system_person_type = 'EMP'
AND UPPER(pf.employee_number) LIKE UPPER(C_EMPLOYEE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(pf.effective_start_date)
AND TRUNC(nvl(pf.effective_end_Date,sysdate+1)) ORDER BY 1;

Line: 684

SELECT employee_id person_id, employee_num employee_number, full_name
FROM   mtl_employees_current_view
WHERE  UPPER(employee_num) LIKE UPPER(C_EMPLOYEE)
AND organization_id = c_org_id;

Line: 693

Select vst.organization_id
from  ahl_workorders wo,
ahl_visits_b vst
where vst.visit_id = wo.visit_id
and wo.workorder_id = c_wo_id;

Line: 702

    SELECT instance_id
    FROM bom_dept_res_instances
    WHERE department_id = c_department_id
    and   Serial_Number=c_serial_number; */

Line: 1007

 Select wip_entity_id
 from ahl_workorders
 where workorder_id=c_work_id;

Line: 1014

 Select WOP.FIRST_UNIT_START_DATE, WOP.LAST_UNIT_COMPLETION_DATE,
        (select nvl(max(resource_seq_num),0) + 10 from wip_operation_resources
         where wip_entity_id = WOP.wip_entity_id and operation_seq_num = WOP.operation_seq_num),
        WOP.Department_id
 From   WIP_OPERATIONS WOP, AHL_WORKORDERS AW
 Where  AW.wip_entity_id = WOP.wip_entity_id
   and  AW.workorder_id = p_workorder_id
   and  WOP.operation_seq_num = p_operation_seq_num;

Line: 1269

		  AHL_DEBUG_PUB.debug( 'Before Insert Process transactions',L_API_NAME);

Line: 1277

        AHL_WIP_JOB_PVT.insert_resource_txn
        (
        p_api_version           =>p_api_version,
        p_init_msg_list         =>L_init_msg_list,
        p_commit                =>l_commit,
        p_validation_level      =>p_validation_level,
        x_return_status         =>x_return_status,
        x_msg_count             =>l_msg_count,
        x_msg_data              =>l_msg_data,
        p_ahl_res_txn_tbl       =>l_ahl_res_txn_tbl
        );

Line: 1364

SELECT workorder_name
FROM AHL_WORKORDERS
WHERE WORKORDER_ID = c_workorder_id;

Line: 1461

SELECT *
FROM   AHL_WORKORDER_OPERATIONS_V
WHERE  workorder_operation_id=c_operation_id;

Line: 1534

 Select wip_entity_id, status_code
 from ahl_workorders
 where workorder_id=c_work_id;

Line: 1541

SELECT  UOM_CODE
FROM  MTL_UNITS_OF_MEASURE
WHERE UPPER(UNIT_OF_MEASURE) = UPPER('Hour')
AND UOM_CLASS = 'Time';

Line: 1549

 SELECT
  AWAS.ASSIGNMENT_ID,
  AWOS.WORKORDER_ID JOB_ID,
  AWOP.WORKORDER_OPERATION_ID OPERATION_ID,
  AWOS.ORGANIZATION_ID,
  AWOP.OPERATION_SEQUENCE_NUM OPERATION_SEQUENCE,
  AOPR.RESOURCE_SEQUENCE_NUM RESOURCE_SEQUENCE,
  BOMR.DESCRIPTION RESOURCE_NAME,
  BOMR.RESOURCE_TYPE RESOURCE_TYPE_CODE ,
  MFGL.MEANING RESOURCE_TYPE_NAME ,
  AOPR.RESOURCE_ID,
  BOMR.RESOURCE_CODE RESOURCE_CODE ,
  PEPF.employee_num EMPLOYEE_NUMBER,
  PEPF.FULL_NAME ,
  AWOS.DEPARTMENT_NAME,
  AWOS.DEPARTMENT_ID,
  AWOS.ITEM_INSTANCE_ID
FROM AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDER_TASKS_V AWOS, AHL_WORKORDER_OPERATIONS AWOP,
AHL_OPERATION_RESOURCES AOPR,AHL_DEPARTMENT_SHIFTS ADS,  mtl_employees_current_view PEPF, BOM_RESOURCES BOMR, MFG_LOOKUPS MFGL
WHERE AWAS.OPERATION_RESOURCE_ID = AOPR.OPERATION_RESOURCE_ID
AND AWOP.WORKORDER_OPERATION_ID = AOPR.WORKORDER_OPERATION_ID
AND AWOP.WORKORDER_ID = AWOS.WORKORDER_ID
AND AWOS.DEPARTMENT_ID = ADS.DEPARTMENT_ID (+)
AND AWAS.EMPLOYEE_ID = PEPF.EMPLOYEE_ID AND SYSTEM_PERSON_TYPE ='EMP' AND AOPR.RESOURCE_ID = BOMR.RESOURCE_ID
AND MFGL.LOOKUP_CODE(+) = BOMR.RESOURCE_TYPE AND MFGL.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'
AND AWAS.ASSIGNMENT_ID  = p_assignment_id;

Line: 1677

		  AHL_DEBUG_PUB.debug( 'Before Insert Process transactions',L_API_NAME);

Line: 1685

        AHL_WIP_JOB_PVT.insert_resource_txn
        (
        p_api_version           =>p_api_version,
        p_init_msg_list         =>L_init_msg_list,
        p_commit                =>l_commit,
        p_validation_level      =>p_validation_level,
        x_return_status         =>x_return_status,
        x_msg_count             =>l_msg_count,
        x_msg_data              =>l_msg_data,
        p_ahl_res_txn_tbl       =>l_ahl_res_txn_tbl
        );

Line: 1703

                  AHL_DEBUG_PUB.debug( 'Error in Insert_Resource_Txn API');

Line: 1856

SELECT         NVL( SUM( transaction_quantity ), 0 )
FROM           WIP_TRANSACTIONS
WHERE          wip_entity_id = c_wip_entity_id
AND            operation_seq_num = c_operation_seq_num
AND            resource_seq_num = c_resource_seq_num
AND            employee_id = c_employee_id;

Line: 1869

SELECT           NVL( SUM( transaction_quantity ), 0 )
FROM             WIP_COST_TXN_INTERFACE
WHERE            wip_entity_id = c_wip_entity_id
AND              operation_seq_num = c_operation_seq_num
AND              resource_seq_num = c_resource_seq_num
AND              employee_id = c_employee_id
AND              process_status = 1;

Line: 1953

	SELECT APRV.RESOURCE_SEQUENCE,
	APRV.RESOURCE_ID,
	APRV.RESOURCE_CODE,
	APRV.RESOURCE_TYPE_NAME,
	APRV.RESOURCE_TYPE_CODE,
	APRV.UOM_NAME,
	APRV.UOM_CODE,
	APRV.DEPARTMENT_ID,
	BD.department_code,
	APRV.REQUIREMENT_ID
	FROM AHL_WORK_ASSIGNMENTS AWAS,
	AHL_PP_REQUIREMENT_V APRV,bom_departments BD
	WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
	AND APRV.DEPARTMENT_ID = BD.department_id
	AND APRV.JOB_ID = x_workorder_id
	AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
	AND AWAS.EMPLOYEE_ID = x_employee_id;

Line: 1974

	SELECT APRV.RESOURCE_SEQUENCE,
	APRV.RESOURCE_ID,
	APRV.RESOURCE_CODE,
	APRV.RESOURCE_TYPE_NAME,
	APRV.RESOURCE_TYPE_CODE,
	APRV.UOM_NAME,
	APRV.UOM_CODE,
	AWAS.SERIAL_NUMBER,
	AWAS.INSTANCE_ID,
	APRV.DEPARTMENT_ID,
	BD.department_code,
	APRV.REQUIREMENT_ID
	FROM AHL_WORK_ASSIGNMENTS AWAS,
	AHL_PP_REQUIREMENT_V APRV,bom_departments BD
	WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
	AND APRV.DEPARTMENT_ID = BD.department_id
	AND APRV.JOB_ID = x_workorder_id
	AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
	AND RESOURCE_TYPE_CODE <> 2;

Line: 1997

	SELECT APRV.RESOURCE_SEQUENCE,
	APRV.RESOURCE_ID,
	APRV.RESOURCE_CODE,
	APRV.RESOURCE_TYPE_NAME,
	APRV.RESOURCE_TYPE_CODE,
	APRV.UOM_NAME,
	APRV.UOM_CODE,
	APRV.DEPARTMENT_ID,
	BD.department_code,
	APRV.REQUIREMENT_ID
	FROM AHL_PP_REQUIREMENT_V APRV ,bom_departments BD
	WHERE APRV.JOB_ID = x_workorder_id
	AND APRV.DEPARTMENT_ID = BD.department_id
	AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
	AND APRV.RESOURCE_TYPE_CODE <> 2
	AND NOT EXISTS (SELECT ASSIGNMENT_ID
	FROM AHL_WORK_ASSIGNMENTS AWAS
	WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID);

Line: 2019

	SELECT APRV.RESOURCE_SEQUENCE,
	APRV.RESOURCE_ID,
	APRV.RESOURCE_CODE,
	APRV.RESOURCE_TYPE_NAME,
	APRV.RESOURCE_TYPE_CODE,
	APRV.UOM_NAME,
	APRV.UOM_CODE,
	AWAS.EMPLOYEE_ID,
	AWAS.SERIAL_NUMBER,
	AWAS.INSTANCE_ID,
	APRV.DEPARTMENT_ID,
	BD.department_code,
	APRV.REQUIREMENT_ID
	FROM AHL_WORK_ASSIGNMENTS AWAS,
	AHL_PP_REQUIREMENT_V APRV ,bom_departments BD
	WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
	AND APRV.DEPARTMENT_ID = BD.department_id
	AND APRV.JOB_ID = x_workorder_id
	AND APRV.OPERATION_SEQUENCE = x_operation_seq_num;

Line: 2042

	SELECT APRV.RESOURCE_SEQUENCE,
	APRV.RESOURCE_ID,
	APRV.RESOURCE_CODE,
	APRV.RESOURCE_TYPE_NAME,
	APRV.RESOURCE_TYPE_CODE,
	APRV.UOM_NAME,
	APRV.UOM_CODE,
	APRV.DEPARTMENT_ID,
	BD.department_code,
	APRV.REQUIREMENT_ID
	FROM AHL_PP_REQUIREMENT_V APRV ,bom_departments BD
	WHERE APRV.JOB_ID = x_workorder_id
	AND APRV.DEPARTMENT_ID = BD.department_id
	AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
	AND NOT EXISTS (SELECT ASSIGNMENT_ID
	FROM AHL_WORK_ASSIGNMENTS AWAS
	WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID);

Line: 2062

        SELECT FULL_NAME,
        EMPLOYEE_NUMBER
        FROM PER_PEOPLE_F
        WHERE PERSON_ID = x_employee_id;