VIEW: APPS.MSD_SR_CMRO_PLAN_HIST_V

Source

View Text - Preformatted

SELECT nvl(asm.organization_id, msd_sr_util.get_null_pk), null, nvl(asm.inventory_item_id, msd_sr_util.get_null_pk), null, msd_sr_util.uom_conv(asm.uom, asm.inventory_item_id) * asm.completed_quantity, asm.requested_date, nvl(asm.scheduled_date, asm.requested_date) FROM msd_app_instance_orgs app, msd_setup_parameters morg, msd_setup_parameters para2, mtl_system_items itm, ahl_schedule_materials asm, ahl_visit_tasks_b avt, ahl_visits_b av WHERE morg.parameter_name = 'MSD_MASTER_ORG' and para2.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and itm.inventory_item_id = asm.inventory_item_id and itm.organization_id = asm.organization_id and app.organization_id = asm.organization_id and asm.visit_id = avt.visit_id and asm.visit_task_id = avt.visit_task_id and avt.visit_id = av.visit_id and avt.task_type_code = 'PLANNED' and asm.material_request_type = 'PLANNED' and av.status_code not in ('CANCELED', 'DELETED') and nvl(asm.completed_quantity, 0) > 0 and asm.requested_date < sysdate and decode(nvl(itm.ato_forecast_control,3), 3, decode(nvl(para2.parameter_value, '2'), '1', msd_sr_util.is_product_family_forecastable(morg.parameter_value, asm.inventory_item_id, 2), 2), 1) = 1 and (itm.mrp_planning_code <> 6 or (itm.mrp_planning_code = 6 and itm.pick_components_flag = 'Y'))

View Text - HTML Formatted

SELECT NVL(ASM.ORGANIZATION_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(ASM.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, MSD_SR_UTIL.UOM_CONV(ASM.UOM
, ASM.INVENTORY_ITEM_ID) * ASM.COMPLETED_QUANTITY
, ASM.REQUESTED_DATE
, NVL(ASM.SCHEDULED_DATE
, ASM.REQUESTED_DATE)
FROM MSD_APP_INSTANCE_ORGS APP
, MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS PARA2
, MTL_SYSTEM_ITEMS ITM
, AHL_SCHEDULE_MATERIALS ASM
, AHL_VISIT_TASKS_B AVT
, AHL_VISITS_B AV
WHERE MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND PARA2.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND ITM.INVENTORY_ITEM_ID = ASM.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = ASM.ORGANIZATION_ID
AND APP.ORGANIZATION_ID = ASM.ORGANIZATION_ID
AND ASM.VISIT_ID = AVT.VISIT_ID
AND ASM.VISIT_TASK_ID = AVT.VISIT_TASK_ID
AND AVT.VISIT_ID = AV.VISIT_ID
AND AVT.TASK_TYPE_CODE = 'PLANNED'
AND ASM.MATERIAL_REQUEST_TYPE = 'PLANNED'
AND AV.STATUS_CODE NOT IN ('CANCELED'
, 'DELETED')
AND NVL(ASM.COMPLETED_QUANTITY
, 0) > 0
AND ASM.REQUESTED_DATE < SYSDATE
AND DECODE(NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(PARA2.PARAMETER_VALUE
, '2')
, '1'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, ASM.INVENTORY_ITEM_ID
, 2)
, 2)
, 1) = 1
AND (ITM.MRP_PLANNING_CODE <> 6 OR (ITM.MRP_PLANNING_CODE = 6
AND ITM.PICK_COMPONENTS_FLAG = 'Y'))