VIEW: APPS.MSD_SR_SERVICE_PART_USAGE_V

Source

View Text - Preformatted

SELECT 1, nvl(cdl.inventory_item_id, msd_sr_util.get_null_pk), 7, nvl(cdl.issuing_inventory_org_id, msd_sr_util.get_null_pk), 15, decode(decode(nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1', hca.attribute1, 'attribute2', hca.attribute2, 'attribute3', hca.attribute3, 'attribute4', hca.attribute4, 'attribute5', hca.attribute5, 'attribute6', hca.attribute6, 'attribute7', hca.attribute7, 'attribute8', hca.attribute8, 'attribute9', hca.attribute9, 'attribute10', hca.attribute10, 'attribute11', hca.attribute11, 'attribute12', hca.attribute12, 'attribute13',hca.attribute13, 'attribute14', hca.attribute14, 'attribute15', hca.attribute15, '2'), '1', nvl(cia.account_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), 9, nvl(trunc( cdl.service_date, 'dd'),to_date ('1000/01/01', 'yyyy/mm/dd')), msd_sr_util.uom_conv(cdl.uom_code, cdl.inventory_item_id) * cdl.quantity, nvl(msi.comms_nl_trackable_flag,'N'), jtv.source_object_type_code FROM msd_app_instance_orgs maio, msd_setup_parameters msp, msd_setup_parameters msp2, msd_setup_parameters morg, cs_incidents_all cia, csd_repairs cdr, jtf_tasks_b jtv, jtf_task_assignments jta, csf_debrief_headers cdh, csf_debrief_lines cdl, cs_transaction_types ctt, mtl_system_items msi , HZ_CUST_ACCOUNTS hca, (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust WHERE /*DR - JTF Customer */ msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and morg.parameter_name = 'MSD_MASTER_ORG' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 2) ) , 1), decode( nvl(msi.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and cia.incident_id = cdr.incident_id AND cdr.repair_line_id = jtv.source_object_id AND jtv.source_object_type_code = 'DR' AND jtv.task_id = jta.task_id AND jta.assignee_role = 'ASSIGNEE' AND jta.task_assignment_id = cdh.task_assignment_id AND cdh.debrief_header_id = cdl.debrief_header_id AND cdl.transaction_type_id = ctt.transaction_type_id AND ctt.line_order_category_code = 'ORDER' AND msi.inventory_item_id = cdl.inventory_item_id AND msi.organization_id = cdl.issuing_inventory_org_id AND hca.cust_account_id (+) = cia.account_id and maio.organization_id = msi.organization_id UNION ALL /* DR WIP at Customer */ SELECT 1, nvl( mmt.inventory_item_id, msd_sr_util.get_null_pk), 7, msd_sr_util.get_service_req_org_id(mmt.transaction_source_id), 15, msd_sr_util.get_service_req_acct_id(mmt.transaction_source_id, filtercust.parameter_value), 9, nvl(trunc(mmt.transaction_date, 'dd'), to_date ('1000/01/01', 'yyyy/mm/dd')), abs(mmt.primary_quantity), nvl(msi.comms_nl_trackable_flag, 'N'), 'DR' FROM mtl_material_transactions mmt, mtl_system_items msi , (select parameter_value from msd_setup_parameters WHERE parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust, msd_app_instance_orgs maio, msd_setup_parameters msp, msd_setup_parameters morg, msd_setup_parameters msp2 WHERE msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and /* DWK New */ morg.parameter_name = 'MSD_MASTER_ORG' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 2) ) , 1), decode( nvl(msi.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and msd_sr_util.is_txn_depot_repair(mmt.transaction_source_id) = 'Y' AND mmt.transaction_type_id = 35 AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id and maio.organization_id = msi.organization_id UNION ALL /* DR WIP at All Geo */ SELECT 1, nvl( mmt.inventory_item_id, msd_sr_util.get_null_pk), 7, msd_sr_util.get_service_req_org_id(mmt.transaction_source_id), 30, msd_sr_util.get_all_geo_pk, 9, nvl(trunc(mmt.transaction_date, 'dd'), to_date ('1000/01/01', 'yyyy/mm/dd')), abs(mmt.primary_quantity) , nvl(msi.comms_nl_trackable_flag, 'N'), 'DR' FROM mtl_material_transactions mmt, mtl_system_items msi , msd_app_instance_orgs maio, msd_setup_parameters msp, msd_setup_parameters morg, msd_setup_parameters msp2 WHERE msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and /* DWK New */ morg.parameter_name = 'MSD_MASTER_ORG' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 2) ) , 1), decode( nvl(msi.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and msd_sr_util.is_txn_depot_repair(mmt.transaction_source_id) = 'Y' AND mmt.transaction_type_id = 35 AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id and maio.organization_id = msi.organization_id UNION ALL /* DR TASK - All Geo */ select 1, nvl(cdl.inventory_item_id, msd_sr_util.get_null_pk), 7, nvl(cdl.issuing_inventory_org_id, msd_sr_util.get_null_pk), 30, msd_sr_util.get_all_geo_pk, 9, nvl(trunc( cdl.service_date, 'dd'),to_date ('1000/01/01', 'yyyy/mm/dd')), msd_sr_util.uom_conv(cdl.uom_code, cdl.inventory_item_id) * cdl.quantity, nvl(msi.comms_nl_trackable_flag,'N'), jtv.source_object_type_code FROM msd_app_instance_orgs maio, msd_setup_parameters msp, msd_setup_parameters msp2, msd_setup_parameters morg, cs_incidents_all cia, csd_repairs cdr, jtf_tasks_b jtv, jtf_task_assignments jta, csf_debrief_headers cdh, csf_debrief_lines cdl, cs_transaction_types ctt, mtl_system_items msi WHERE msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and /* DWK New */ morg.parameter_name = 'MSD_MASTER_ORG' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 2) ) , 1), decode( nvl(msi.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and cia.incident_id = cdr.incident_id AND cdr.repair_line_id = jtv.source_object_id AND jtv.source_object_type_code = 'DR' AND jtv.task_id = jta.task_id AND jta.assignee_role = 'ASSIGNEE' AND jta.task_assignment_id = cdh.task_assignment_id AND cdh.debrief_header_id = cdl.debrief_header_id AND cdl.transaction_type_id = ctt.transaction_type_id AND ctt.line_order_category_code = 'ORDER' AND msi.inventory_item_id = cdl.inventory_item_id AND msi.organization_id = cdl.issuing_inventory_org_id and maio.organization_id = msi.organization_id UNION ALL /* JTF at Customer */ SELECT 1, nvl(cdl.inventory_item_id, msd_sr_util.get_null_pk), 7, nvl(cdl.issuing_inventory_org_id, msd_sr_util.get_null_pk), 15, decode(decode(nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1', hca.attribute1, 'attribute2', hca.attribute2, 'attribute3', hca.attribute3, 'attribute4', hca.attribute4, 'attribute5', hca.attribute5, 'attribute6', hca.attribute6, 'attribute7', hca.attribute7, 'attribute8', hca.attribute8, 'attribute9', hca.attribute9, 'attribute10', hca.attribute10, 'attribute11', hca.attribute11, 'attribute12', hca.attribute12, 'attribute13',hca.attribute13, 'attribute14', hca.attribute14, 'attribute15', hca.attribute15, '2'), '1', nvl(cia.account_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), 9, nvl(trunc(cdl.service_date,'dd'), to_date('1000/01/01', 'yyyy/mm/dd')), msd_sr_util.uom_conv(cdl.uom_code, cdl.inventory_item_id) * cdl.quantity, nvl(COMMS_NL_TRACKABLE_FLAG,'N'), jtv.source_object_type_code from msd_app_instance_orgs maio, msd_setup_parameters msp, msd_setup_parameters msp2, msd_setup_parameters morg, csf_debrief_lines cdl, CSF_DEBRIEF_HEADERS cdh, JTF_TASK_ASSIGNMENTS jta, cs_incidents_all cia, JTF_TASKS_B jtv, CS_TRANSACTION_TYPES CTT, mtl_system_items msi, HZ_CUST_ACCOUNTS hca, (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust where msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and /* DWK New */ morg.parameter_name = 'MSD_MASTER_ORG' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 2) ) , 1), decode( nvl(msi.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and msi.inventory_item_id = cdl.inventory_item_id and msi.organization_id = cdl.issuing_inventory_org_id and jta.assignee_role = 'ASSIGNEE' and cia.incident_id = jtv.source_object_id and jta.task_id = jtv.task_id and cdh.debrief_header_id = cdl.debrief_header_id and cia.incident_id = jtv.source_object_id and cdh.task_assignment_id = jta.task_assignment_id and cdl.transaction_type_id = ctt.transaction_type_id and ctt.line_order_category_code = 'ORDER' and hca.cust_account_id (+) = cia.account_id and ( msi.mrp_planning_code <> 6 or (msi.mrp_planning_code = 6 and msi.pick_components_flag = 'Y')) and maio.organization_id = msi.organization_id and jtv.source_object_type_code = 'SR' /* Field Service JTF at All Geography Level */ UNION ALL SELECT 1, nvl(cdl.inventory_item_id, msd_sr_util.get_null_pk), 7, nvl(cdl.issuing_inventory_org_id, msd_sr_util.get_null_pk), 30, msd_sr_util.get_all_geo_pk, 9, nvl(trunc(cdl.service_date,'dd'), to_date('1000/01/01', 'yyyy/mm/dd')), msd_sr_util.uom_conv(cdl.uom_code, cdl.inventory_item_id) * cdl.quantity, nvl(COMMS_NL_TRACKABLE_FLAG,'N'), jtv.source_object_type_code from msd_app_instance_orgs maio, msd_setup_parameters msp, msd_setup_parameters msp2, msd_setup_parameters morg, csf_debrief_lines cdl, CSF_DEBRIEF_HEADERS cdh, JTF_TASK_ASSIGNMENTS jta, cs_incidents_all cia, JTF_TASKS_B jtv, CS_TRANSACTION_TYPES CTT, mtl_system_items msi, (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust where msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and /* DWK New */ morg.parameter_name = 'MSD_MASTER_ORG' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 2) ) , 1), decode( nvl(msi.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and msi.inventory_item_id = cdl.inventory_item_id and msi.organization_id = cdl.issuing_inventory_org_id and jta.assignee_role = 'ASSIGNEE' and cia.incident_id = jtv.source_object_id and cdh.debrief_header_id = cdl.debrief_header_id and jta.task_id = jtv.task_id and cia.incident_id = jtv.source_object_id and cdh.task_assignment_id = jta.task_assignment_id and cdl.transaction_type_id = ctt.transaction_type_id and ctt.line_order_category_code = 'ORDER' and ( msi.mrp_planning_code <> 6 or (msi.mrp_planning_code = 6 and msi.pick_components_flag = 'Y')) and maio.organization_id = msi.organization_id and jtv.source_object_type_code = 'SR' UNION ALL /* Field Service JTF at Zone Level */ SELECT 1, nvl(cdl.inventory_item_id, msd_sr_util.get_null_pk), 7, nvl(cdl.issuing_inventory_org_id, msd_sr_util.get_null_pk), 42, msd_sr_util.get_sr_zone_pk ( hps.location_id, filterzone.parameter_value), 9, nvl(trunc(cdl.service_date,'dd'), to_date('1000/01/01', 'yyyy/mm/dd')), msd_sr_util.uom_conv(cdl.uom_code, cdl.inventory_item_id) * cdl.quantity, nvl(COMMS_NL_TRACKABLE_FLAG,'N'), jtv.source_object_type_code FROM msd_app_instance_orgs maio, msd_setup_parameters msp, msd_setup_parameters msp2, msd_setup_parameters morg, cs_incidents_all cia, JTF_TASKS_B jtv, JTF_TASK_ASSIGNMENTS jta, CSF_DEBRIEF_HEADERS cdh, hz_party_sites hps, csf_debrief_lines cdl, CS_TRANSACTION_TYPES CTT, mtl_system_items msi, (select msd_sr_util.get_zone_attr parameter_value from dual) filterzone WHERE msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and /* DWK New */ morg.parameter_name = 'MSD_MASTER_ORG' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 2) ) , 1), decode( nvl(msi.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and msi.inventory_item_id = cdl.inventory_item_id and msi.organization_id = cdl.issuing_inventory_org_id and jta.assignee_role = 'ASSIGNEE' and cia.incident_id = jtv.source_object_id and jta.task_id = jtv.task_id and cdh.debrief_header_id = cdl.debrief_header_id and cia.incident_id = jtv.source_object_id and cdh.task_assignment_id = jta.task_assignment_id and cdl.transaction_type_id = ctt.transaction_type_id and cia.install_site_id = hps.party_site_id (+) and ctt.line_order_category_code = 'ORDER' and ( msi.mrp_planning_code <> 6 or (msi.mrp_planning_code = 6 and msi.pick_components_flag = 'Y')) and maio.organization_id = msi.organization_id and jtv.source_object_type_code = 'SR' UNION ALL /* DR WIP at Zone */ SELECT 1, nvl( mmt.inventory_item_id, msd_sr_util.get_null_pk), 7, msd_sr_util.get_service_req_org_id(mmt.transaction_source_id), 42, msd_sr_util.get_service_req_zone_id(mmt.transaction_source_id, filterzone.parameter_value), 9, nvl(trunc(mmt.transaction_date, 'dd'), to_date ('1000/01/01', 'yyyy/mm/dd')), abs(mmt.primary_quantity), nvl(msi.comms_nl_trackable_flag, 'N'), 'DR' FROM mtl_material_transactions mmt, mtl_system_items msi , (select msd_sr_util.get_zone_attr parameter_value from dual) filterzone, msd_app_instance_orgs maio, msd_setup_parameters msp, msd_setup_parameters morg, msd_setup_parameters msp2 WHERE msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and /* DWK New */ morg.parameter_name = 'MSD_MASTER_ORG' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 2) ) , 1), decode( nvl(msi.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and msd_sr_util.is_txn_depot_repair(mmt.transaction_source_id) = 'Y' AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id and mmt.transaction_type_id = 35 and maio.organization_id = msi.organization_id UNION ALL /* Depot Repair JTF at Zone Level */ SELECT 1, nvl(cdl.inventory_item_id, msd_sr_util.get_null_pk), 7, nvl(cdl.issuing_inventory_org_id, msd_sr_util.get_null_pk), 42, msd_sr_util.get_sr_zone_pk ( hps.location_id, filterzone.parameter_value), 9, nvl(trunc( cdl.service_date, 'dd'), to_date ('1000/01/01', 'yyyy/mm/dd')), msd_sr_util.uom_conv(cdl.uom_code, cdl.inventory_item_id) * cdl.quantity, nvl(msi.comms_nl_trackable_flag, 'N'), jtv.source_object_type_code FROM msd_app_instance_orgs maio, msd_setup_parameters msp, msd_setup_parameters msp2, msd_setup_parameters morg, cs_incidents_all cia, csd_repairs cdr, jtf_tasks_b jtv, jtf_task_assignments jta, csf_debrief_headers cdh, csf_debrief_lines cdl, cs_transaction_types ctt, mtl_system_items msi , hz_party_sites hps, (select msd_sr_util.get_zone_attr parameter_value from dual) filterzone WHERE msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and /* DWK New */ morg.parameter_name = 'MSD_MASTER_ORG' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(morg.parameter_value, msi.inventory_item_id, 2) ) , 1), decode( nvl(msi.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and cia.incident_id = cdr.incident_id AND cdr.repair_line_id = jtv.source_object_id AND jtv.source_object_type_code = 'DR' AND jtv.task_id = jta.task_id AND jta.assignee_role = 'ASSIGNEE' AND jta.task_assignment_id = cdh.task_assignment_id AND cdh.debrief_header_id = cdl.debrief_header_id AND cdl.transaction_type_id = ctt.transaction_type_id AND ctt.line_order_category_code = 'ORDER' AND msi.inventory_item_id = cdl.inventory_item_id AND msi.organization_id = cdl.issuing_inventory_org_id AND maio.organization_id = msi.organization_id and cia.install_site_id = hps.party_site_id (+)

View Text - HTML Formatted

SELECT 1
, NVL(CDL.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, NVL(CDL.ISSUING_INVENTORY_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 15
, DECODE(DECODE(NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, HCA.ATTRIBUTE1
, 'ATTRIBUTE2'
, HCA.ATTRIBUTE2
, 'ATTRIBUTE3'
, HCA.ATTRIBUTE3
, 'ATTRIBUTE4'
, HCA.ATTRIBUTE4
, 'ATTRIBUTE5'
, HCA.ATTRIBUTE5
, 'ATTRIBUTE6'
, HCA.ATTRIBUTE6
, 'ATTRIBUTE7'
, HCA.ATTRIBUTE7
, 'ATTRIBUTE8'
, HCA.ATTRIBUTE8
, 'ATTRIBUTE9'
, HCA.ATTRIBUTE9
, 'ATTRIBUTE10'
, HCA.ATTRIBUTE10
, 'ATTRIBUTE11'
, HCA.ATTRIBUTE11
, 'ATTRIBUTE12'
, HCA.ATTRIBUTE12
, 'ATTRIBUTE13'
, HCA.ATTRIBUTE13
, 'ATTRIBUTE14'
, HCA.ATTRIBUTE14
, 'ATTRIBUTE15'
, HCA.ATTRIBUTE15
, '2')
, '1'
, NVL(CIA.ACCOUNT_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, 9
, NVL(TRUNC( CDL.SERVICE_DATE
, 'DD')
, TO_DATE ('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(CDL.UOM_CODE
, CDL.INVENTORY_ITEM_ID) * CDL.QUANTITY
, NVL(MSI.COMMS_NL_TRACKABLE_FLAG
, 'N')
, JTV.SOURCE_OBJECT_TYPE_CODE
FROM MSD_APP_INSTANCE_ORGS MAIO
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MSP2
, MSD_SETUP_PARAMETERS MORG
, CS_INCIDENTS_ALL CIA
, CSD_REPAIRS CDR
, JTF_TASKS_B JTV
, JTF_TASK_ASSIGNMENTS JTA
, CSF_DEBRIEF_HEADERS CDH
, CSF_DEBRIEF_LINES CDL
, CS_TRANSACTION_TYPES CTT
, MTL_SYSTEM_ITEMS MSI
, HZ_CUST_ACCOUNTS HCA
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE /*DR - JTF CUSTOMER */ MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND CIA.INCIDENT_ID = CDR.INCIDENT_ID
AND CDR.REPAIR_LINE_ID = JTV.SOURCE_OBJECT_ID
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'DR'
AND JTV.TASK_ID = JTA.TASK_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND JTA.TASK_ASSIGNMENT_ID = CDH.TASK_ASSIGNMENT_ID
AND CDH.DEBRIEF_HEADER_ID = CDL.DEBRIEF_HEADER_ID
AND CDL.TRANSACTION_TYPE_ID = CTT.TRANSACTION_TYPE_ID
AND CTT.LINE_ORDER_CATEGORY_CODE = 'ORDER'
AND MSI.INVENTORY_ITEM_ID = CDL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CDL.ISSUING_INVENTORY_ORG_ID
AND HCA.CUST_ACCOUNT_ID (+) = CIA.ACCOUNT_ID
AND MAIO.ORGANIZATION_ID = MSI.ORGANIZATION_ID UNION ALL /* DR WIP AT CUSTOMER */ SELECT 1
, NVL( MMT.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, MSD_SR_UTIL.GET_SERVICE_REQ_ORG_ID(MMT.TRANSACTION_SOURCE_ID)
, 15
, MSD_SR_UTIL.GET_SERVICE_REQ_ACCT_ID(MMT.TRANSACTION_SOURCE_ID
, FILTERCUST.PARAMETER_VALUE)
, 9
, NVL(TRUNC(MMT.TRANSACTION_DATE
, 'DD')
, TO_DATE ('1000/01/01'
, 'YYYY/MM/DD'))
, ABS(MMT.PRIMARY_QUANTITY)
, NVL(MSI.COMMS_NL_TRACKABLE_FLAG
, 'N')
, 'DR'
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_SYSTEM_ITEMS MSI
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
, MSD_APP_INSTANCE_ORGS MAIO
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS MSP2
WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND /* DWK NEW */ MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND MSD_SR_UTIL.IS_TXN_DEPOT_REPAIR(MMT.TRANSACTION_SOURCE_ID) = 'Y'
AND MMT.TRANSACTION_TYPE_ID = 35
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MAIO.ORGANIZATION_ID = MSI.ORGANIZATION_ID UNION ALL /* DR WIP AT ALL GEO */ SELECT 1
, NVL( MMT.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, MSD_SR_UTIL.GET_SERVICE_REQ_ORG_ID(MMT.TRANSACTION_SOURCE_ID)
, 30
, MSD_SR_UTIL.GET_ALL_GEO_PK
, 9
, NVL(TRUNC(MMT.TRANSACTION_DATE
, 'DD')
, TO_DATE ('1000/01/01'
, 'YYYY/MM/DD'))
, ABS(MMT.PRIMARY_QUANTITY)
, NVL(MSI.COMMS_NL_TRACKABLE_FLAG
, 'N')
, 'DR'
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_SYSTEM_ITEMS MSI
, MSD_APP_INSTANCE_ORGS MAIO
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS MSP2
WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND /* DWK NEW */ MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND MSD_SR_UTIL.IS_TXN_DEPOT_REPAIR(MMT.TRANSACTION_SOURCE_ID) = 'Y'
AND MMT.TRANSACTION_TYPE_ID = 35
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MAIO.ORGANIZATION_ID = MSI.ORGANIZATION_ID UNION ALL /* DR TASK - ALL GEO */ SELECT 1
, NVL(CDL.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, NVL(CDL.ISSUING_INVENTORY_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 30
, MSD_SR_UTIL.GET_ALL_GEO_PK
, 9
, NVL(TRUNC( CDL.SERVICE_DATE
, 'DD')
, TO_DATE ('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(CDL.UOM_CODE
, CDL.INVENTORY_ITEM_ID) * CDL.QUANTITY
, NVL(MSI.COMMS_NL_TRACKABLE_FLAG
, 'N')
, JTV.SOURCE_OBJECT_TYPE_CODE
FROM MSD_APP_INSTANCE_ORGS MAIO
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MSP2
, MSD_SETUP_PARAMETERS MORG
, CS_INCIDENTS_ALL CIA
, CSD_REPAIRS CDR
, JTF_TASKS_B JTV
, JTF_TASK_ASSIGNMENTS JTA
, CSF_DEBRIEF_HEADERS CDH
, CSF_DEBRIEF_LINES CDL
, CS_TRANSACTION_TYPES CTT
, MTL_SYSTEM_ITEMS MSI
WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND /* DWK NEW */ MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND CIA.INCIDENT_ID = CDR.INCIDENT_ID
AND CDR.REPAIR_LINE_ID = JTV.SOURCE_OBJECT_ID
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'DR'
AND JTV.TASK_ID = JTA.TASK_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND JTA.TASK_ASSIGNMENT_ID = CDH.TASK_ASSIGNMENT_ID
AND CDH.DEBRIEF_HEADER_ID = CDL.DEBRIEF_HEADER_ID
AND CDL.TRANSACTION_TYPE_ID = CTT.TRANSACTION_TYPE_ID
AND CTT.LINE_ORDER_CATEGORY_CODE = 'ORDER'
AND MSI.INVENTORY_ITEM_ID = CDL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CDL.ISSUING_INVENTORY_ORG_ID
AND MAIO.ORGANIZATION_ID = MSI.ORGANIZATION_ID UNION ALL /* JTF AT CUSTOMER */ SELECT 1
, NVL(CDL.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, NVL(CDL.ISSUING_INVENTORY_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 15
, DECODE(DECODE(NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, HCA.ATTRIBUTE1
, 'ATTRIBUTE2'
, HCA.ATTRIBUTE2
, 'ATTRIBUTE3'
, HCA.ATTRIBUTE3
, 'ATTRIBUTE4'
, HCA.ATTRIBUTE4
, 'ATTRIBUTE5'
, HCA.ATTRIBUTE5
, 'ATTRIBUTE6'
, HCA.ATTRIBUTE6
, 'ATTRIBUTE7'
, HCA.ATTRIBUTE7
, 'ATTRIBUTE8'
, HCA.ATTRIBUTE8
, 'ATTRIBUTE9'
, HCA.ATTRIBUTE9
, 'ATTRIBUTE10'
, HCA.ATTRIBUTE10
, 'ATTRIBUTE11'
, HCA.ATTRIBUTE11
, 'ATTRIBUTE12'
, HCA.ATTRIBUTE12
, 'ATTRIBUTE13'
, HCA.ATTRIBUTE13
, 'ATTRIBUTE14'
, HCA.ATTRIBUTE14
, 'ATTRIBUTE15'
, HCA.ATTRIBUTE15
, '2')
, '1'
, NVL(CIA.ACCOUNT_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, 9
, NVL(TRUNC(CDL.SERVICE_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(CDL.UOM_CODE
, CDL.INVENTORY_ITEM_ID) * CDL.QUANTITY
, NVL(COMMS_NL_TRACKABLE_FLAG
, 'N')
, JTV.SOURCE_OBJECT_TYPE_CODE
FROM MSD_APP_INSTANCE_ORGS MAIO
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MSP2
, MSD_SETUP_PARAMETERS MORG
, CSF_DEBRIEF_LINES CDL
, CSF_DEBRIEF_HEADERS CDH
, JTF_TASK_ASSIGNMENTS JTA
, CS_INCIDENTS_ALL CIA
, JTF_TASKS_B JTV
, CS_TRANSACTION_TYPES CTT
, MTL_SYSTEM_ITEMS MSI
, HZ_CUST_ACCOUNTS HCA
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND /* DWK NEW */ MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND MSI.INVENTORY_ITEM_ID = CDL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CDL.ISSUING_INVENTORY_ORG_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND CIA.INCIDENT_ID = JTV.SOURCE_OBJECT_ID
AND JTA.TASK_ID = JTV.TASK_ID
AND CDH.DEBRIEF_HEADER_ID = CDL.DEBRIEF_HEADER_ID
AND CIA.INCIDENT_ID = JTV.SOURCE_OBJECT_ID
AND CDH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID
AND CDL.TRANSACTION_TYPE_ID = CTT.TRANSACTION_TYPE_ID
AND CTT.LINE_ORDER_CATEGORY_CODE = 'ORDER'
AND HCA.CUST_ACCOUNT_ID (+) = CIA.ACCOUNT_ID
AND ( MSI.MRP_PLANNING_CODE <> 6 OR (MSI.MRP_PLANNING_CODE = 6
AND MSI.PICK_COMPONENTS_FLAG = 'Y'))
AND MAIO.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'SR' /* FIELD SERVICE JTF AT ALL GEOGRAPHY LEVEL */ UNION ALL SELECT 1
, NVL(CDL.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, NVL(CDL.ISSUING_INVENTORY_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 30
, MSD_SR_UTIL.GET_ALL_GEO_PK
, 9
, NVL(TRUNC(CDL.SERVICE_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(CDL.UOM_CODE
, CDL.INVENTORY_ITEM_ID) * CDL.QUANTITY
, NVL(COMMS_NL_TRACKABLE_FLAG
, 'N')
, JTV.SOURCE_OBJECT_TYPE_CODE
FROM MSD_APP_INSTANCE_ORGS MAIO
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MSP2
, MSD_SETUP_PARAMETERS MORG
, CSF_DEBRIEF_LINES CDL
, CSF_DEBRIEF_HEADERS CDH
, JTF_TASK_ASSIGNMENTS JTA
, CS_INCIDENTS_ALL CIA
, JTF_TASKS_B JTV
, CS_TRANSACTION_TYPES CTT
, MTL_SYSTEM_ITEMS MSI
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND /* DWK NEW */ MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND MSI.INVENTORY_ITEM_ID = CDL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CDL.ISSUING_INVENTORY_ORG_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND CIA.INCIDENT_ID = JTV.SOURCE_OBJECT_ID
AND CDH.DEBRIEF_HEADER_ID = CDL.DEBRIEF_HEADER_ID
AND JTA.TASK_ID = JTV.TASK_ID
AND CIA.INCIDENT_ID = JTV.SOURCE_OBJECT_ID
AND CDH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID
AND CDL.TRANSACTION_TYPE_ID = CTT.TRANSACTION_TYPE_ID
AND CTT.LINE_ORDER_CATEGORY_CODE = 'ORDER'
AND ( MSI.MRP_PLANNING_CODE <> 6 OR (MSI.MRP_PLANNING_CODE = 6
AND MSI.PICK_COMPONENTS_FLAG = 'Y'))
AND MAIO.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'SR' UNION ALL /* FIELD SERVICE JTF AT ZONE LEVEL */ SELECT 1
, NVL(CDL.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, NVL(CDL.ISSUING_INVENTORY_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 42
, MSD_SR_UTIL.GET_SR_ZONE_PK ( HPS.LOCATION_ID
, FILTERZONE.PARAMETER_VALUE)
, 9
, NVL(TRUNC(CDL.SERVICE_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(CDL.UOM_CODE
, CDL.INVENTORY_ITEM_ID) * CDL.QUANTITY
, NVL(COMMS_NL_TRACKABLE_FLAG
, 'N')
, JTV.SOURCE_OBJECT_TYPE_CODE
FROM MSD_APP_INSTANCE_ORGS MAIO
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MSP2
, MSD_SETUP_PARAMETERS MORG
, CS_INCIDENTS_ALL CIA
, JTF_TASKS_B JTV
, JTF_TASK_ASSIGNMENTS JTA
, CSF_DEBRIEF_HEADERS CDH
, HZ_PARTY_SITES HPS
, CSF_DEBRIEF_LINES CDL
, CS_TRANSACTION_TYPES CTT
, MTL_SYSTEM_ITEMS MSI
, (SELECT MSD_SR_UTIL.GET_ZONE_ATTR PARAMETER_VALUE
FROM DUAL) FILTERZONE
WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND /* DWK NEW */ MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND MSI.INVENTORY_ITEM_ID = CDL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CDL.ISSUING_INVENTORY_ORG_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND CIA.INCIDENT_ID = JTV.SOURCE_OBJECT_ID
AND JTA.TASK_ID = JTV.TASK_ID
AND CDH.DEBRIEF_HEADER_ID = CDL.DEBRIEF_HEADER_ID
AND CIA.INCIDENT_ID = JTV.SOURCE_OBJECT_ID
AND CDH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID
AND CDL.TRANSACTION_TYPE_ID = CTT.TRANSACTION_TYPE_ID
AND CIA.INSTALL_SITE_ID = HPS.PARTY_SITE_ID (+)
AND CTT.LINE_ORDER_CATEGORY_CODE = 'ORDER'
AND ( MSI.MRP_PLANNING_CODE <> 6 OR (MSI.MRP_PLANNING_CODE = 6
AND MSI.PICK_COMPONENTS_FLAG = 'Y'))
AND MAIO.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'SR' UNION ALL /* DR WIP AT ZONE */ SELECT 1
, NVL( MMT.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, MSD_SR_UTIL.GET_SERVICE_REQ_ORG_ID(MMT.TRANSACTION_SOURCE_ID)
, 42
, MSD_SR_UTIL.GET_SERVICE_REQ_ZONE_ID(MMT.TRANSACTION_SOURCE_ID
, FILTERZONE.PARAMETER_VALUE)
, 9
, NVL(TRUNC(MMT.TRANSACTION_DATE
, 'DD')
, TO_DATE ('1000/01/01'
, 'YYYY/MM/DD'))
, ABS(MMT.PRIMARY_QUANTITY)
, NVL(MSI.COMMS_NL_TRACKABLE_FLAG
, 'N')
, 'DR'
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_SYSTEM_ITEMS MSI
, (SELECT MSD_SR_UTIL.GET_ZONE_ATTR PARAMETER_VALUE
FROM DUAL) FILTERZONE
, MSD_APP_INSTANCE_ORGS MAIO
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS MSP2
WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND /* DWK NEW */ MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND MSD_SR_UTIL.IS_TXN_DEPOT_REPAIR(MMT.TRANSACTION_SOURCE_ID) = 'Y'
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MMT.TRANSACTION_TYPE_ID = 35
AND MAIO.ORGANIZATION_ID = MSI.ORGANIZATION_ID UNION ALL /* DEPOT REPAIR JTF AT ZONE LEVEL */ SELECT 1
, NVL(CDL.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, NVL(CDL.ISSUING_INVENTORY_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 42
, MSD_SR_UTIL.GET_SR_ZONE_PK ( HPS.LOCATION_ID
, FILTERZONE.PARAMETER_VALUE)
, 9
, NVL(TRUNC( CDL.SERVICE_DATE
, 'DD')
, TO_DATE ('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(CDL.UOM_CODE
, CDL.INVENTORY_ITEM_ID) * CDL.QUANTITY
, NVL(MSI.COMMS_NL_TRACKABLE_FLAG
, 'N')
, JTV.SOURCE_OBJECT_TYPE_CODE
FROM MSD_APP_INSTANCE_ORGS MAIO
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MSP2
, MSD_SETUP_PARAMETERS MORG
, CS_INCIDENTS_ALL CIA
, CSD_REPAIRS CDR
, JTF_TASKS_B JTV
, JTF_TASK_ASSIGNMENTS JTA
, CSF_DEBRIEF_HEADERS CDH
, CSF_DEBRIEF_LINES CDL
, CS_TRANSACTION_TYPES CTT
, MTL_SYSTEM_ITEMS MSI
, HZ_PARTY_SITES HPS
, (SELECT MSD_SR_UTIL.GET_ZONE_ATTR PARAMETER_VALUE
FROM DUAL) FILTERZONE
WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND /* DWK NEW */ MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND CIA.INCIDENT_ID = CDR.INCIDENT_ID
AND CDR.REPAIR_LINE_ID = JTV.SOURCE_OBJECT_ID
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'DR'
AND JTV.TASK_ID = JTA.TASK_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND JTA.TASK_ASSIGNMENT_ID = CDH.TASK_ASSIGNMENT_ID
AND CDH.DEBRIEF_HEADER_ID = CDL.DEBRIEF_HEADER_ID
AND CDL.TRANSACTION_TYPE_ID = CTT.TRANSACTION_TYPE_ID
AND CTT.LINE_ORDER_CATEGORY_CODE = 'ORDER'
AND MSI.INVENTORY_ITEM_ID = CDL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CDL.ISSUING_INVENTORY_ORG_ID
AND MAIO.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND CIA.INSTALL_SITE_ID = HPS.PARTY_SITE_ID (+)