VIEW: APPS.AHL_WIP_RESOURCE_TXNS_V

Source

View Text - Preformatted

SELECT distinct(wipt.transaction_id), awos.workorder_id, awos.Workorder_name, wipt.wip_entity_id, wipt.operation_seq_num, wipt.resource_seq_num, wipt.employee_id, pepf.employee_number, pepf.full_name, bomr.resource_code, bomr.description, wipt.resource_id, nvl(wipt.charge_department_id, wipt.department_id), bomd.description dept_description, wipt.primary_quantity quantity, wipt.usage_rate_or_amount, wipt.primary_uom, muom.unit_of_measure_tl uom_meaning, wipt.activity_id, csat.activity, wipt.reason_id, mtlr.reason_name, wipt.reference, wipt.transaction_date, awos.visit_task_id, awos.status_code, fndl.meaning, 'Complete' FROM ahl_workorders awos, wip_transactions wipt, bom_resources bomr, bom_departments bomd, per_people_f pepf, mtl_units_of_measure muom, mtl_transaction_reasons mtlr, cst_activities csat, fnd_lookup_values_vl fndl WHERE wipt.wip_entity_id = awos.wip_entity_id and wipt.resource_id = bomr.resource_id and nvl(wipt.charge_department_id, wipt.department_id) = bomd.department_id and wipt.primary_uom = muom.uom_code and wipt.reason_id = mtlr.reason_id(+) and wipt.activity_id = csat.activity_id(+) and wipt.employee_id = pepf.person_id(+) and fndl.lookup_type(+) = 'AHL_JOB_STATUS' and awos.status_code = fndl.lookup_code(+) UNION ALL SELECT distinct(wict.transaction_id), awos.workorder_id, awos.workorder_name, wict.wip_entity_id, wict.operation_seq_num, wict.resource_seq_num, wict.employee_id, pepf.employee_number, pepf.full_name, bomr.resource_code, bomr.description, wiCt.resource_id, nvl(wict.charge_department_id, wict.department_id), bomd.description dept_description, wict.primary_quantity quantity, wict.usage_rate_or_amount, wict.primary_uom, muom.unit_of_measure_tl uom_meaning, wiCt.activity_id, csat.activity, wiCt.reason_id, mtlr.reason_name, wict.reference, wict.transaction_date, awos.visit_task_id, awos.status_code, fndl.meaning, MFGL.MEANING FROM ahl_workorders awos, wip_COST_TXN_INTERFACE wict, bom_resources bomr, bom_departments bomd, per_people_f pepf, mtl_units_of_measure muom, mtl_transaction_reasons mtlr, cst_activities csat, MFG_LOOKUPS MFGL, fnd_lookup_values_vl fndl, wip_entities WIP WHERE wict.wip_entity_id = awos.wip_entity_id and wict.resource_id = bomr.resource_id and nvl(wict.charge_department_id, wict.department_id) = bomd.department_id and wict.primary_uom = muom.uom_code and wict.reason_id = mtlr.reason_id(+) and wict.activity_id = csat.activity_id(+) and wict.employee_id = pepf.person_id(+) and MFGL.LOOKUP_TYPE = 'WIP_PROCESS_STATUS' AND MFGL.LOOKUP_CODE = WICT.PROCESS_STATUS and fndl.lookup_type(+) = 'AHL_JOB_STATUS' and awos.status_code = fndl.lookup_code(+) and WIP.wip_entity_id = awos.wip_entity_id and wict.organization_id = WIP.organization_id

View Text - HTML Formatted

SELECT DISTINCT(WIPT.TRANSACTION_ID)
, AWOS.WORKORDER_ID
, AWOS.WORKORDER_NAME
, WIPT.WIP_ENTITY_ID
, WIPT.OPERATION_SEQ_NUM
, WIPT.RESOURCE_SEQ_NUM
, WIPT.EMPLOYEE_ID
, PEPF.EMPLOYEE_NUMBER
, PEPF.FULL_NAME
, BOMR.RESOURCE_CODE
, BOMR.DESCRIPTION
, WIPT.RESOURCE_ID
, NVL(WIPT.CHARGE_DEPARTMENT_ID
, WIPT.DEPARTMENT_ID)
, BOMD.DESCRIPTION DEPT_DESCRIPTION
, WIPT.PRIMARY_QUANTITY QUANTITY
, WIPT.USAGE_RATE_OR_AMOUNT
, WIPT.PRIMARY_UOM
, MUOM.UNIT_OF_MEASURE_TL UOM_MEANING
, WIPT.ACTIVITY_ID
, CSAT.ACTIVITY
, WIPT.REASON_ID
, MTLR.REASON_NAME
, WIPT.REFERENCE
, WIPT.TRANSACTION_DATE
, AWOS.VISIT_TASK_ID
, AWOS.STATUS_CODE
, FNDL.MEANING
, 'COMPLETE'
FROM AHL_WORKORDERS AWOS
, WIP_TRANSACTIONS WIPT
, BOM_RESOURCES BOMR
, BOM_DEPARTMENTS BOMD
, PER_PEOPLE_F PEPF
, MTL_UNITS_OF_MEASURE MUOM
, MTL_TRANSACTION_REASONS MTLR
, CST_ACTIVITIES CSAT
, FND_LOOKUP_VALUES_VL FNDL
WHERE WIPT.WIP_ENTITY_ID = AWOS.WIP_ENTITY_ID
AND WIPT.RESOURCE_ID = BOMR.RESOURCE_ID
AND NVL(WIPT.CHARGE_DEPARTMENT_ID
, WIPT.DEPARTMENT_ID) = BOMD.DEPARTMENT_ID
AND WIPT.PRIMARY_UOM = MUOM.UOM_CODE
AND WIPT.REASON_ID = MTLR.REASON_ID(+)
AND WIPT.ACTIVITY_ID = CSAT.ACTIVITY_ID(+)
AND WIPT.EMPLOYEE_ID = PEPF.PERSON_ID(+)
AND FNDL.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND AWOS.STATUS_CODE = FNDL.LOOKUP_CODE(+) UNION ALL SELECT DISTINCT(WICT.TRANSACTION_ID)
, AWOS.WORKORDER_ID
, AWOS.WORKORDER_NAME
, WICT.WIP_ENTITY_ID
, WICT.OPERATION_SEQ_NUM
, WICT.RESOURCE_SEQ_NUM
, WICT.EMPLOYEE_ID
, PEPF.EMPLOYEE_NUMBER
, PEPF.FULL_NAME
, BOMR.RESOURCE_CODE
, BOMR.DESCRIPTION
, WICT.RESOURCE_ID
, NVL(WICT.CHARGE_DEPARTMENT_ID
, WICT.DEPARTMENT_ID)
, BOMD.DESCRIPTION DEPT_DESCRIPTION
, WICT.PRIMARY_QUANTITY QUANTITY
, WICT.USAGE_RATE_OR_AMOUNT
, WICT.PRIMARY_UOM
, MUOM.UNIT_OF_MEASURE_TL UOM_MEANING
, WICT.ACTIVITY_ID
, CSAT.ACTIVITY
, WICT.REASON_ID
, MTLR.REASON_NAME
, WICT.REFERENCE
, WICT.TRANSACTION_DATE
, AWOS.VISIT_TASK_ID
, AWOS.STATUS_CODE
, FNDL.MEANING
, MFGL.MEANING
FROM AHL_WORKORDERS AWOS
, WIP_COST_TXN_INTERFACE WICT
, BOM_RESOURCES BOMR
, BOM_DEPARTMENTS BOMD
, PER_PEOPLE_F PEPF
, MTL_UNITS_OF_MEASURE MUOM
, MTL_TRANSACTION_REASONS MTLR
, CST_ACTIVITIES CSAT
, MFG_LOOKUPS MFGL
, FND_LOOKUP_VALUES_VL FNDL
, WIP_ENTITIES WIP
WHERE WICT.WIP_ENTITY_ID = AWOS.WIP_ENTITY_ID
AND WICT.RESOURCE_ID = BOMR.RESOURCE_ID
AND NVL(WICT.CHARGE_DEPARTMENT_ID
, WICT.DEPARTMENT_ID) = BOMD.DEPARTMENT_ID
AND WICT.PRIMARY_UOM = MUOM.UOM_CODE
AND WICT.REASON_ID = MTLR.REASON_ID(+)
AND WICT.ACTIVITY_ID = CSAT.ACTIVITY_ID(+)
AND WICT.EMPLOYEE_ID = PEPF.PERSON_ID(+)
AND MFGL.LOOKUP_TYPE = 'WIP_PROCESS_STATUS'
AND MFGL.LOOKUP_CODE = WICT.PROCESS_STATUS
AND FNDL.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND AWOS.STATUS_CODE = FNDL.LOOKUP_CODE(+)
AND WIP.WIP_ENTITY_ID = AWOS.WIP_ENTITY_ID
AND WICT.ORGANIZATION_ID = WIP.ORGANIZATION_ID