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.

VIEW: APPS.AHL_MATERIAL_REQUIREMENTS_V

Source

View Text - Preformatted


SELECT asmt.visit_id, asmt.visit_task_id, avtl.visit_task_number, avtl.status_code, avb.visit_number, avb.visit_type_code, flvt1.meaning visit_type_mean, avb.status_code visit_status_code, flvt.meaning visit_status_mean, asmt.requested_quantity, asmt.requested_date, decode(sign(trunc(scheduled_date) - trunc(requested_date)),1,scheduled_date,null) exception_date, asmt.scheduled_quantity, asmt.inventory_item_id, mstk.concatenated_segments item_description, NVL(mstk.comms_nl_trackable_flag,'N') trackable_flag, DECODE(mstk.serial_number_control_code, 1,'N','Y') serial_control_flag, asmt.organization_Id, hrou.name organization_name, mstk.primary_unit_of_measure uom, asmt.scheduled_material_id schedule_material_id, asmt.object_version_number, rtom.rework_percent, rtom.replace_percent, awo.workorder_name workorder_number, ( SELECT SUM(mr.primary_reservation_quantity) FROM mtl_reservations MR WHERE mr .demand_source_line_detail = asmt.scheduled_material_id AND mr.organization_id = asmt.organization_id AND mr.requirement_date = asmt.requested_date AND mr.inventory_item_id = asmt.inventory_item_id AND mr.external_source_code = 'AHL' GROUP BY mr.demand_source_line_detail) reserved_quantity, asmt.rt_oper_material_id, asmt.workorder_operation_id, asmt.item_group_id, asmt.position_path_id, asmt.relationship_id, ( SELECT name FROM ahl_mc_headers_b hdr,ahl_mc_relationships rel WHERE hdr.mc_header_id = rel.mc_header_id AND relationship_id = asmt.relationship_id ) subconfig_name, ( SELECT title FROM ahl_mr_headers_b WHERE mr_header_id= amrr.mr_header_id ) mr_title, ( SELECT route_no FROM ahl_routes_b WHERE route_id = amrr.route_id ) route_number, ( SELECT title FROM AHL_ROUTES_TL T WHERE T.route_id = amrr.route_id AND T.language = userenv('LANG') ) route_title, asmt.operation_sequence operation_seq_num, asmt.completed_quantity FROM ahl_visit_tasks_b avtl, ahl_visits_b avb, mtl_system_items_kfv mstk, ahl_schedule_materials asmt, ahl_rt_oper_materials rtom, hr_all_organization_units hrou, fnd_lookup_values flvt, fnd_lookup_values flvt1, ahl_mr_routes amrr, ahl_workorders awo WHERE avtl.visit_id = avb.visit_id AND avtl.visit_task_id = asmt.visit_task_id AND avtl.mr_route_id = amrr.mr_route_id(+) AND asmt.inventory_item_id = mstk.inventory_item_id(+) AND asmt.organization_id = mstk.organization_id(+) AND asmt.rt_oper_material_id = rtom.rt_oper_material_id(+) AND nvl(asmt.status,'') = 'ACTIVE' AND asmt.requested_quantity <>0 AND flvt.lookup_type(+) = 'AHL_VWP_VISIT_STATUS' AND flvt.lookup_code(+) = avb.status_code AND flvt.language(+) = userenv('LANG') AND flvt1.lookup_type(+) = 'AHL_PLANNING_VISIT_TYPE' AND flvt1.lookup_code(+) = avb.visit_type_code AND flvt1.language(+) = userenv('LANG') AND asmt.organization_id = hrou.organization_id(+) AND asmt.visit_task_id = awo.visit_task_id (+) AND NVL(awo.status_code,-1) NOT IN (7,22) AND ( AVTL.status_code = 'PLANNING' OR ( AVTL.status_code = 'RELEASED' AND EXISTS ( SELECT awo.visit_task_id FROM ahl_workorders awo WHERE avtl.visit_task_id = awo.visit_task_id AND (awo.status_code = '1' OR awo.status_code='3') ) ) ) AND HROU.ORGANIZATION_ID IN ( SELECT organization_id FROM org_organization_definitions WHERE operating_unit = mo_global.get_current_org_id() )

View Text - HTML Formatted

SELECT ASMT.VISIT_ID
, ASMT.VISIT_TASK_ID
, AVTL.VISIT_TASK_NUMBER
, AVTL.STATUS_CODE
, AVB.VISIT_NUMBER
, AVB.VISIT_TYPE_CODE
, FLVT1.MEANING VISIT_TYPE_MEAN
, AVB.STATUS_CODE VISIT_STATUS_CODE
, FLVT.MEANING VISIT_STATUS_MEAN
, ASMT.REQUESTED_QUANTITY
, ASMT.REQUESTED_DATE
, DECODE(SIGN(TRUNC(SCHEDULED_DATE) - TRUNC(REQUESTED_DATE))
, 1
, SCHEDULED_DATE
, NULL) EXCEPTION_DATE
, ASMT.SCHEDULED_QUANTITY
, ASMT.INVENTORY_ITEM_ID
, MSTK.CONCATENATED_SEGMENTS ITEM_DESCRIPTION
, NVL(MSTK.COMMS_NL_TRACKABLE_FLAG
, 'N') TRACKABLE_FLAG
, DECODE(MSTK.SERIAL_NUMBER_CONTROL_CODE
, 1
, 'N'
, 'Y') SERIAL_CONTROL_FLAG
, ASMT.ORGANIZATION_ID
, HROU.NAME ORGANIZATION_NAME
, MSTK.PRIMARY_UNIT_OF_MEASURE UOM
, ASMT.SCHEDULED_MATERIAL_ID SCHEDULE_MATERIAL_ID
, ASMT.OBJECT_VERSION_NUMBER
, RTOM.REWORK_PERCENT
, RTOM.REPLACE_PERCENT
, AWO.WORKORDER_NAME WORKORDER_NUMBER
, ( SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
FROM MTL_RESERVATIONS MR
WHERE MR .DEMAND_SOURCE_LINE_DETAIL = ASMT.SCHEDULED_MATERIAL_ID
AND MR.ORGANIZATION_ID = ASMT.ORGANIZATION_ID
AND MR.REQUIREMENT_DATE = ASMT.REQUESTED_DATE
AND MR.INVENTORY_ITEM_ID = ASMT.INVENTORY_ITEM_ID
AND MR.EXTERNAL_SOURCE_CODE = 'AHL' GROUP BY MR.DEMAND_SOURCE_LINE_DETAIL) RESERVED_QUANTITY
, ASMT.RT_OPER_MATERIAL_ID
, ASMT.WORKORDER_OPERATION_ID
, ASMT.ITEM_GROUP_ID
, ASMT.POSITION_PATH_ID
, ASMT.RELATIONSHIP_ID
, ( SELECT NAME
FROM AHL_MC_HEADERS_B HDR
, AHL_MC_RELATIONSHIPS REL
WHERE HDR.MC_HEADER_ID = REL.MC_HEADER_ID
AND RELATIONSHIP_ID = ASMT.RELATIONSHIP_ID ) SUBCONFIG_NAME
, ( SELECT TITLE
FROM AHL_MR_HEADERS_B
WHERE MR_HEADER_ID= AMRR.MR_HEADER_ID ) MR_TITLE
, ( SELECT ROUTE_NO
FROM AHL_ROUTES_B
WHERE ROUTE_ID = AMRR.ROUTE_ID ) ROUTE_NUMBER
, ( SELECT TITLE
FROM AHL_ROUTES_TL T
WHERE T.ROUTE_ID = AMRR.ROUTE_ID
AND T.LANGUAGE = USERENV('LANG') ) ROUTE_TITLE
, ASMT.OPERATION_SEQUENCE OPERATION_SEQ_NUM
, ASMT.COMPLETED_QUANTITY
FROM AHL_VISIT_TASKS_B AVTL
, AHL_VISITS_B AVB
, MTL_SYSTEM_ITEMS_KFV MSTK
, AHL_SCHEDULE_MATERIALS ASMT
, AHL_RT_OPER_MATERIALS RTOM
, HR_ALL_ORGANIZATION_UNITS HROU
, FND_LOOKUP_VALUES FLVT
, FND_LOOKUP_VALUES FLVT1
, AHL_MR_ROUTES AMRR
, AHL_WORKORDERS AWO
WHERE AVTL.VISIT_ID = AVB.VISIT_ID
AND AVTL.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
AND AVTL.MR_ROUTE_ID = AMRR.MR_ROUTE_ID(+)
AND ASMT.INVENTORY_ITEM_ID = MSTK.INVENTORY_ITEM_ID(+)
AND ASMT.ORGANIZATION_ID = MSTK.ORGANIZATION_ID(+)
AND ASMT.RT_OPER_MATERIAL_ID = RTOM.RT_OPER_MATERIAL_ID(+)
AND NVL(ASMT.STATUS
, '') = 'ACTIVE'
AND ASMT.REQUESTED_QUANTITY <>0
AND FLVT.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_STATUS'
AND FLVT.LOOKUP_CODE(+) = AVB.STATUS_CODE
AND FLVT.LANGUAGE(+) = USERENV('LANG')
AND FLVT1.LOOKUP_TYPE(+) = 'AHL_PLANNING_VISIT_TYPE'
AND FLVT1.LOOKUP_CODE(+) = AVB.VISIT_TYPE_CODE
AND FLVT1.LANGUAGE(+) = USERENV('LANG')
AND ASMT.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
AND ASMT.VISIT_TASK_ID = AWO.VISIT_TASK_ID (+)
AND NVL(AWO.STATUS_CODE
, -1) NOT IN (7
, 22)
AND ( AVTL.STATUS_CODE = 'PLANNING' OR ( AVTL.STATUS_CODE = 'RELEASED'
AND EXISTS ( SELECT AWO.VISIT_TASK_ID
FROM AHL_WORKORDERS AWO
WHERE AVTL.VISIT_TASK_ID = AWO.VISIT_TASK_ID
AND (AWO.STATUS_CODE = '1' OR AWO.STATUS_CODE='3') ) ) )
AND HROU.ORGANIZATION_ID IN ( SELECT ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE OPERATING_UNIT = MO_GLOBAL.GET_CURRENT_ORG_ID() )