eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_PRD_WO_PUB SQL Statements

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

Line: 10


select user_id from fnd_user where user_name = p_user_id;

Line: 16


select responsibility_id from fnd_responsibility_vl where responsibility_key = G_BPEL_USER_ROLE_KEY;

Line: 75


IS SELECT WO.WORKORDER_ID,
       WO.OBJECT_VERSION_NUMBER,
       WO.JOB_NUMBER,
       WO.JOB_DESCRIPTION,
       WO.ORGANIZATION_NAME,
       WO.DEPARTMENT_NAME,
       WO.JOB_STATUS_CODE,
       WO.JOB_STATUS_MEANING,
       WO.PRIORITY_MEANING,
       WO.SCHEDULED_START_DATE,
       WO.SCHEDULED_END_DATE,
       WO.ACTUAL_START_DATE,
       WO.ACTUAL_END_DATE,
       WO.UNIT_NAME,
       WO.WO_PART_NUMBER,
       WO.SERIAL_NUMBER,
       WO.VISIT_ID,
       WO.VISIT_NUMBER,
       WO.VISIT_NAME,
       WO.VISIT_TASK_ID,
       WO.VISIT_STATUS_CODE,
       AMH.MR_HEADER_ID,
       WO.VISIT_TASK_NUMBER,
       AMH.TITLE MR_TITLE,
       WO.MR_ROUTE_ID,
       WO.ROUTE_ID,
       AR.ROUTE_NO ROUTE_TITLE,
       AR.ROUTE_NO ROUTE_NUMBER,
       PAA.NAME PROJECT_NAME,
       PAT.TASK_NAME PROJECT_TASK_NAME,
       WO.UNIT_EFFECTIVITY_ID,
       WO.LOT_NUMBER,
       WO.UC_HEADER_ID,
       WO.UNIT_QUARANTINE_FLAG,
       WO.ORGANIZATION_ID,
       WO.DEPARTMENT_ID,
       WOS.PLAN_ID,
       VST.start_date_time,
       VST.close_date_time,
       VTS.service_request_id,
       VTS.service_request_id service_request_number
FROM AHL_WORKORDER_TASKS_V WO, PA_PROJECTS_ALL PAA, PA_TASKS PAT,
AHL_MR_ROUTES AMR, AHL_MR_HEADERS_B AMH, AHL_ROUTES_B AR, AHL_VISIT_TASKS_B VTS,
AHL_VISITS_B VST,AHL_WORKORDERS WOS
WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
AND WO.VISIT_ID=VST.VISIT_ID
AND WO.MR_ROUTE_ID=AMR.MR_ROUTE_ID (+)
AND AMR.MR_HEADER_ID=AMH.MR_HEADER_ID(+)
AND WO.ROUTE_ID=AR.ROUTE_ID (+)
AND VST.PROJECT_ID=PAA.PROJECT_ID (+)
AND VTS.PROJECT_TASK_ID=PAT.TASK_ID (+)
AND WO.WORKORDER_ID = WOS.workorder_id
AND WO.WORKORDER_ID = p_WorkorderId;

Line: 241


        x_WO_DETAILS_REC.IsUpdateEnabled := 'F';

Line: 243


        x_WO_DETAILS_REC.IsUpdateEnabled := 'T';

Line: 303


SELECT WORKORDER_OPERATION_ID,OBJECT_VERSION_NUMBER,  OPERATION_SEQUENCE_NUM,DESCRIPTION,
       WORKORDER_ID, DEPARTMENT_ID,
       DEPARTMENT_NAME,  OPERATION_ID,  OPERATION_CODE,  OPERATION_TYPE_CODE,
       OPERATION_TYPE,  STATUS_CODE, STATUS,
        SCHEDULED_START_DATE,  SCHEDULED_END_DATE,
        ACTUAL_START_DATE, ACTUAL_END_DATE, PLAN_ID
FROM AHL_WORKORDER_OPERATIONS_V
WHERE  WORKORDER_ID = p_WorkorderId
AND WORKORDER_OPERATION_ID = NVL(p_WoOperationId, WORKORDER_OPERATION_ID)
ORDER BY OPERATION_SEQUENCE_NUM;

Line: 380


    x_Operations(l_op_index).IsUpdateEnabled := AHL_PRD_UTIL_PKG.Is_Op_Updatable(op_details.WORKORDER_ID, op_details.OPERATION_SEQUENCE_NUM);

Line: 385


    ELSIF(op_details.PLAN_ID IS NOT NULL AND x_Operations(l_op_index).IsUpdateEnabled = 'T')THEN
       x_Operations(l_op_index).IsQualityEnabled := 'T' ;

Line: 387


    ELSIF(op_details.PLAN_ID IS NOT NULL AND x_Operations(l_op_index).IsUpdateEnabled = 'F')THEN
       x_Operations(l_op_index).IsQualityEnabled := 'F' ;

Line: 444


SELECT ASML.operation_sequence,  MSIK.concatenated_segments part_number,MSIK.description Part_Desc, MSIK.primary_unit_of_measure part_uom  ,
WIRO.REQUIRED_QUANTITY required_quantity , WIRO.DATE_REQUIRED required_date,
asml.scheduled_quantity schedule_quantity,asml.scheduled_date schedule_date,
 nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
 AWOS.workorder_id,
asml.inventory_item_id,
ASML.organization_id,
ASML.scheduled_material_id
from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
WHERE AWOS.visit_task_id = ASML.visit_task_id
and ASML.inventory_item_id = MSIK.inventory_item_id
and ASML.organization_id = MSIK.organization_id
AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
AND AWOS.WORKORDER_ID = p_WorkorderId
AND ASML.WORKORDER_OPERATION_ID = NVL(p_WoOperationId,ASML.WORKORDER_OPERATION_ID);

Line: 576


SELECT
	  DOC.DOCUMENT_NO,	  DOC.DOCUMENT_TITLE,	  DOC.ASO_OBJECT_TYPE_DESC,	  DOC.REVISION_NO,
	  DOC.CHAPTER,	  DOC.SECTION,	  DOC.SUBJECT,	  DOC.PAGE,	  DOC.FIGURE,	  DOC.NOTE
	FROM
	  AHL_WORKORDERS WO,	  ahl_reference_doc_v DOC,	  AHL_DOC_REVISIONS_B REV
	WHERE
	  WO.ROUTE_ID = DOC.ASO_OBJECT_ID
	  AND WO.WORKORDER_ID = p_WorkorderId
	  AND DOC.ASO_OBJECT_TYPE_CODE = 'ROUTE'
	  AND REV.DOCUMENT_ID(+) = DOC.DOCUMENT_ID
	  AND TRUNC(NVL(REV.OBSOLETE_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
	UNION ALL
	-- OPERATION DOC ASSOCIATIONS
	SELECT
	  DOC.DOCUMENT_NO,	  DOC.DOCUMENT_TITLE,  	  DOC.ASO_OBJECT_TYPE_DESC,	  DOC.REVISION_NO,
	  DOC.CHAPTER,	  DOC.SECTION,	  DOC.SUBJECT,	  DOC.PAGE,	  DOC.FIGURE,	  DOC.NOTE
	FROM
	  AHL_WORKORDER_OPERATIONS WOP,	  ahl_reference_doc_v DOC,		  AHL_DOC_REVISIONS_B REV
	WHERE
	  WOP.OPERATION_ID = DOC.ASO_OBJECT_ID
	  AND WOP.WORKORDER_ID = p_WorkorderId
 	  AND DOC.ASO_OBJECT_TYPE_CODE = 'OPERATION'
	  AND REV.DOCUMENT_ID(+) = DOC.DOCUMENT_ID
	  AND TRUNC(NVL(REV.OBSOLETE_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
	UNION ALL
	-- MR DOCUMENT ASSOCIATIONS
	SELECT
	  DOC.DOCUMENT_NO,	  DOC.DOCUMENT_TITLE,	  DOC.ASO_OBJECT_TYPE_DESC,		  DOC.REVISION_NO,
	  DOC.CHAPTER,	  DOC.SECTION,	  DOC.SUBJECT,	  DOC.PAGE,	  DOC.FIGURE,	  DOC.NOTE
	FROM
	  AHL_WORKORDERS WO,	  AHL_VISIT_TASKS_B VST,	  ahl_reference_doc_v DOC,		  AHL_DOC_REVISIONS_B REV
	WHERE
	  WO.VISIT_TASK_ID = VST.VISIT_TASK_ID
	  AND WO.WORKORDER_ID = p_WorkorderId
	  AND VST.MR_ID = DOC.ASO_OBJECT_ID
 	  AND DOC.ASO_OBJECT_TYPE_CODE = 'MR'
  	  AND REV.DOCUMENT_ID(+) = DOC.DOCUMENT_ID
	  AND TRUNC(NVL(REV.OBSOLETE_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
	UNION ALL
	-- MC DOCUMENT ASSOCIATIONS
	SELECT
	  DOC.DOCUMENT_NO,	  DOC.DOCUMENT_TITLE,	  DOC.ASO_OBJECT_TYPE_DESC,		  DOC.REVISION_NO,
	  DOC.CHAPTER,	  DOC.SECTION,	  DOC.SUBJECT,	  DOC.PAGE,	  DOC.FIGURE,	  DOC.NOTE
	FROM
	  AHL_WORKORDERS WO,	  CSI_II_RELATIONSHIPS CSI,	  AHL_VISIT_TASKS_B VTS,
	  ahl_reference_doc_v DOC,		  AHL_DOC_REVISIONS_B REV
	WHERE
	  WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
	  AND WO.WORKORDER_ID = p_WorkorderId
	  AND VTS.INSTANCE_ID = CSI.SUBJECT_ID
	  AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
	  AND (SYSDATE BETWEEN NVL(CSI.ACTIVE_START_DATE, SYSDATE) AND NVL(CSI.ACTIVE_END_DATE, SYSDATE))
	  AND TO_NUMBER(CSI.POSITION_REFERENCE) = DOC.ASO_OBJECT_ID
 	  AND DOC.ASO_OBJECT_TYPE_CODE = 'MC'
  	  AND REV.DOCUMENT_ID(+) = DOC.DOCUMENT_ID
	  AND TRUNC(NVL(REV.OBSOLETE_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
	UNION ALL
	-- PC DOC ASSOCIATIONS
	SELECT
	  DOC.DOCUMENT_NO,	  DOC.DOCUMENT_TITLE,	  DOC.ASO_OBJECT_TYPE_DESC,		  DOC.REVISION_NO,
	  DOC.CHAPTER,	  DOC.SECTION,	  DOC.SUBJECT,  DOC.PAGE,	  DOC.FIGURE,	  DOC.NOTE
	FROM
	  AHL_WORKORDERS WO,	  AHL_PC_ASSOCIATIONS PCA,	  AHL_VISIT_TASKS_B VTS,
	  ahl_reference_doc_v DOC,		  AHL_DOC_REVISIONS_B REV
	WHERE
	  WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
	  AND WO.WORKORDER_ID = p_WorkorderId
	  AND AHL_UTIL_UC_PKG.GET_UC_HEADER_ID(VTS.INSTANCE_ID) = PCA.UNIT_ITEM_ID
	  AND PCA.PC_NODE_ID = DOC.ASO_OBJECT_ID
 	  AND DOC.ASO_OBJECT_TYPE_CODE = 'PC'
  	  AND REV.DOCUMENT_ID(+) = DOC.DOCUMENT_ID
	  AND TRUNC(NVL(REV.OBSOLETE_DATE, SYSDATE + 1)) > TRUNC(SYSDATE);

Line: 748


Select jtf_note_id,
source_object_id,
source_object_code,
entered_date,
fu.employee_id entered_by,
(Select DISTINCT PF.full_name from mtl_employees_current_view PF where pf.employee_id =fu.employee_id)  entered_by_name,
notes note
from jtf_notes_vl JTF,fnd_user fu
where source_object_code = 'AHL_WO_TURNOVER_NOTES'
and fu.user_id = JTF.entered_by
and source_object_id = p_WorkorderId ORDER BY entered_date DESC;

Line: 858


SELECT WIP.TRANSACTION_ID,       WIP.WORKORDER_ID,       WIP.JOB_NUMBER,
       WIP.OPERATION_SEQ_NUM,       WIP.RESOURCE_SEQ_NUM,      WIP.EMPLOYEE_ID,       WIP.EMPLOYEE_NUMBER,
       WIP.FULL_NAME,       WIP.RESOURCE_CODE,       WIP.DESCRIPTION,       WIP.RESOURCE_ID,       WIP.DEPARTMENT_ID,
       WIP.DEPT_DESCRIPTION,       WIP.QUANTITY,       WIP.USAGE_RATE_OR_AMOUNT,       WIP.PRIMARY_UOM,
       WIP.UOM_MEANING,       WIP.ACTIVITY_ID,       WIP.ACTIVITY,       WIP.REASON_ID,       WIP.REASON_NAME,
       WIP.REFERENCE,       WIP.TRANSACTION_DATE,
       WIP.TRANSACTION_STATUS,   MFGL.MEANING RESOURCE_TYPE,
       BR.RESOURCE_TYPE RESOURCE_TYPE_CODE, NULL SERIAL_NUMBER
FROM AHL_WIP_RESOURCE_TXNS_V WIP,BOM_RESOURCES BR, MFG_LOOKUPS MFGL
WHERE WORKORDER_ID = p_WorkorderId
AND OPERATION_SEQ_NUM = NVL(p_OperationSequence,OPERATION_SEQ_NUM )
AND BR.resource_id = WIP.resource_id
AND MFGL.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'
AND MFGL.LOOKUP_CODE(+) = BR.RESOURCE_TYPE;

Line: 874


SELECT OPERATION_SEQUENCE_NUM  FROM ahl_workorder_operations
WHERE WORKORDER_OPERATION_ID = p_WoOperationId;

Line: 879


SELECT OPERATION_SEQUENCE_NUM FROM ahl_workorder_operations
WHERE WORKORDER_ID = p_WorkorderId
AND OPERATION_SEQUENCE_NUM = NVL(p_OperationSequence, OPERATION_SEQUENCE_NUM);

Line: 1065


Select plan_id,collection_id  from ahl_search_workorders_v
WHERE workorder_id = p_WorkorderId;

Line: 1069


Select plan_id,collection_id  from ahl_workorder_operations_v
WHERE workorder_operation_id = p_WoOperationId;

Line: 1079


SELECT char_id , prompt_sequence
       , prompt, enabled_flag , default_value
       , default_value_id , result_column_name
        , DECODE( NVL( sql_string_flag, 'N' ), 'N', DECODE( hardcoded_column, NULL, 'N', DECODE( QA_SS_LOV_API.get_lov_sql( plan_id, char_id, organization_id, null, null, null, null, null ), NULL, 'N', 'Y' ) ), sql_string_flag )    sql_string_flag
    , values_exist_flag, displayed_flag
    , char_name , datatype , display_length, hardcoded_column
    , developer_name , mandatory_flag
FROM QA_PLAN_CHARS_V WHERE plan_id = p_plan_id ORDER BY prompt_sequence;

Line: 1089


SELECT plan_id , organization_id,  name
    , description FROM QA_PLANS_V
WHERE plan_id = p_plan_id;

Line: 1094


SELECT WO.JOB_NUMBER,
       AMH.TITLE MR_TITLE,
       WOP.OPERATION_SEQUENCE_NUM,
       WOP.STATUS_CODE,
       WO.JOB_STATUS_CODE,
       WO.WO_PART_NUMBER,
       WO.ITEM_INSTANCE_NUMBER,
       WO.LOT_NUMBER,
       WO.SERIAL_NUMBER
FROM AHL_WORKORDER_TASKS_V WO, AHL_WORKORDER_OPERATIONS WOP,
AHL_MR_ROUTES AMR, AHL_MR_HEADERS_B AMH
WHERE WO.MR_ROUTE_ID=AMR.MR_ROUTE_ID (+)
AND AMR.MR_HEADER_ID=AMH.MR_HEADER_ID(+)
AND WO.WORKORDER_ID = WOP.WORKORDER_ID
AND WOP.workorder_operation_id = p_WoOperationId;

Line: 1113


SELECT WO.JOB_NUMBER,
       AMH.TITLE MR_TITLE,
       WO.JOB_STATUS_CODE,
       WO.WO_PART_NUMBER,
       WO.ITEM_INSTANCE_NUMBER,
       WO.LOT_NUMBER,
       WO.SERIAL_NUMBER
FROM AHL_WORKORDER_TASKS_V WO,
AHL_MR_ROUTES AMR, AHL_MR_HEADERS_B AMH
WHERE WO.MR_ROUTE_ID=AMR.MR_ROUTE_ID (+)
AND AMR.MR_HEADER_ID=AMH.MR_HEADER_ID(+)
AND WO.WORKORDER_ID = p_WorkorderId;

Line: 1356


Select WORKORDER_ID FROM AHL_WORKORDERS
WHERE WORKORDER_NAME = p_workorder_number;

Line: 1378


Select WORKORDER_OPERATION_ID FROM AHL_WORKORDER_OPERATIONS
WHERE WORKORDER_ID = p_WorkorderId AND
      OPERATION_SEQUENCE_NUM = p_operation_sequence;

Line: 1404


SELECT char_id , prompt_sequence
       , prompt, enabled_flag , default_value
       , default_value_id , result_column_name
        , DECODE( NVL( sql_string_flag, 'N' ), 'N', DECODE( hardcoded_column, NULL, 'N', DECODE( QA_SS_LOV_API.get_lov_sql( plan_id, char_id, organization_id, null, null, null, null, null ), NULL, 'N', 'Y' ) ), sql_string_flag )    sql_string_flag
    , values_exist_flag, displayed_flag
    , char_name , datatype , display_length, hardcoded_column
    , developer_name , mandatory_flag
FROM QA_PLAN_CHARS_V WHERE plan_id = p_plan_id ORDER BY prompt_sequence;

Line: 1419


   l_sql_string := 'SELECT RESULTS.OCCURRENCE ';

Line: 1435


          l_result_column_name := '(Select ' ||l_result_column_name || ' FROM  MTL_SYSTEM_ITEMS_KFV ITEM '
                        || ' WHERE ITEM.inventory_item_id  = RESULTS.'|| qa_plan_attributes.hardcoded_column
                        || ' AND ITEM.organization_id  = RESULTS.organization_id ) ' || l_result_column;

Line: 1442


          l_result_column_name := '(Select ' || l_result_column_name ||' FROM  MTL_ITEM_LOCATIONS_KFV LOCATOR '
                        || ' WHERE LOCATOR.inventory_location_id  = RESULTS.' || qa_plan_attributes.hardcoded_column
                        + ' AND LOCATOR.organization_id  = RESULTS.organization_id ) ' || l_result_column;

Line: 1486


    IF(p_WO_DETAILS_REC.IsUpdateEnabled <> 'T')THEN
      FND_MESSAGE.SET_NAME('AHL','AHL_PRD_TRNNTREC_NALWD');

Line: 1492


    AHL_PRD_WORKORDER_PVT.INSERT_TURNOVER_NOTES
    (
        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          =>  'OAF',
        x_return_status        => x_return_status,
        x_msg_count            => x_msg_count,
        x_msg_data             => x_msg_data,
        p_trunover_notes_tbl   => l_trunover_notes_tbl
    );

Line: 1527


  IF(p_CURR_WO_DETAILS_REC.IsUpdateEnabled <> 'T')THEN
      FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_UPD_NALWD');

Line: 1594


    AHL_PRD_WORKORDER_PVT.update_job
    (
        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          =>  'OAF',
        x_return_status        => x_return_status,
        x_msg_count            => x_msg_count,
        x_msg_data             => x_msg_data,
        p_wip_load_flag        => 'Y',
        p_x_prd_workorder_rec  => l_prd_workorder_rec,
        p_x_prd_workoper_tbl   => l_prd_workoper_tbl
     );

Line: 1729


SELECT object_version_number FROM AHL_WORKORDER_OPERATIONS
WHERE WORKORDER_OPERATION_ID = p_WoOperationId;

Line: 1770


    IF(l_Operations(0).IsUpdateEnabled <> 'T')THEN
      FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPUPD_NALWD');

Line: 2021


    IF(p_WO_DETAILS_REC.IsUpdateEnabled <> 'T')THEN
      FND_MESSAGE.SET_NAME('AHL','AHL_PRD_RES_TXN_NALWD');

Line: 2066


SELECT object_version_number FROM AHL_WORKORDERS
WHERE WORKORDER_ID = p_WotkorderId;