VIEW: APPS.MSD_SR_SERVICE_PART_RETURN_V

Source

View Text - Preformatted

SELECT /* All Geo Level */ 1, nvl(cdl.inventory_item_id, msd_sr_util.get_null_pk), 7, nvl(cdl.receiving_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') 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 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.receiving_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 jtv.source_object_type_code = 'SR' and cdh.task_assignment_id = jta.task_assignment_id and ctt.transaction_type_id = cdl.transaction_type_id and ctt.line_order_category_code = 'RETURN' 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 UNION ALL /* Field Service JTF at Zone Level */ SELECT 1, nvl(cdl.inventory_item_id, msd_sr_util.get_null_pk), 7, nvl(cdl.receiving_inventory_org_id, msd_sr_util.get_null_pk), 42, 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(wrv.region_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') 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, hz_party_sites hps, wsh_region_locations wrl, wsh_regions_v wrv, wsh_zone_regions wzr, (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust, (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.receiving_inventory_org_id and jta.assignee_role = 'ASSIGNEE' and cia.incident_id = jtv.source_object_id and jta.task_id = jtv.task_id and jtv.source_object_type_code = 'SR' 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 ctt.transaction_type_id = cdl.transaction_type_id and /* */ cia.install_site_use_id = hps.party_site_id and hps.location_id = wrl.location_id (+) and wrl.region_id = wzr.region_id (+) and wzr.parent_region_id = wrv.region_id (+) and wrv.region_type (+) = 10 and /* */ ctt.line_order_category_code = 'RETURN' 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 decode(nvl(lower(filterzone.parameter_value), '2'), 'attribute1', wrv.attribute1, 'attribute2', wrv.attribute2, 'attribute3',wrv.attribute3, 'attribute4', wrv.attribute4, 'attribute5', wrv.attribute5, 'attribute6', wrv.attribute6, 'attribute7', wrv.attribute7, 'attribute8', wrv.attribute8, 'attribute9', wrv.attribute9, 'attribute10', wrv.attribute10, 'attribute11', wrv.attribute11, 'attribute12', wrv.attribute12, 'attribute13', wrv.attribute13, 'attribute14', wrv.attribute14, 'attribute15', wrv.attribute15, '2') = '1' UNION ALL /* Field Service JTF at Customer */ SELECT 1, nvl(cdl.inventory_item_id, msd_sr_util.get_null_pk), 7, nvl(cdl.receiving_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') 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 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.receiving_inventory_org_id and jta.assignee_role = 'ASSIGNEE' and cia.incident_id = jtv.source_object_id and jta.task_id = jtv.task_id and jtv.source_object_type_code = 'SR' 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 ctt.transaction_type_id = cdl.transaction_type_id and ctt.line_order_category_code = 'RETURN' 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

View Text - HTML Formatted

SELECT /* ALL GEO LEVEL */ 1
, NVL(CDL.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, NVL(CDL.RECEIVING_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')
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 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.RECEIVING_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 JTV.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND CDH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID
AND CTT.TRANSACTION_TYPE_ID = CDL.TRANSACTION_TYPE_ID
AND CTT.LINE_ORDER_CATEGORY_CODE = 'RETURN'
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 UNION ALL /* FIELD SERVICE JTF AT ZONE LEVEL */ SELECT 1
, NVL(CDL.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, NVL(CDL.RECEIVING_INVENTORY_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 42
, 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(WRV.REGION_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')
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
, HZ_PARTY_SITES HPS
, WSH_REGION_LOCATIONS WRL
, WSH_REGIONS_V WRV
, WSH_ZONE_REGIONS WZR
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
, (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.RECEIVING_INVENTORY_ORG_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND CIA.INCIDENT_ID = JTV.SOURCE_OBJECT_ID
AND JTA.TASK_ID = JTV.TASK_ID
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'SR'
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 CTT.TRANSACTION_TYPE_ID = CDL.TRANSACTION_TYPE_ID
AND /* */ CIA.INSTALL_SITE_USE_ID = HPS.PARTY_SITE_ID
AND HPS.LOCATION_ID = WRL.LOCATION_ID (+)
AND WRL.REGION_ID = WZR.REGION_ID (+)
AND WZR.PARENT_REGION_ID = WRV.REGION_ID (+)
AND WRV.REGION_TYPE (+) = 10
AND /* */ CTT.LINE_ORDER_CATEGORY_CODE = 'RETURN'
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 DECODE(NVL(LOWER(FILTERZONE.PARAMETER_VALUE)
, '2')
, 'ATTRIBUTE1'
, WRV.ATTRIBUTE1
, 'ATTRIBUTE2'
, WRV.ATTRIBUTE2
, 'ATTRIBUTE3'
, WRV.ATTRIBUTE3
, 'ATTRIBUTE4'
, WRV.ATTRIBUTE4
, 'ATTRIBUTE5'
, WRV.ATTRIBUTE5
, 'ATTRIBUTE6'
, WRV.ATTRIBUTE6
, 'ATTRIBUTE7'
, WRV.ATTRIBUTE7
, 'ATTRIBUTE8'
, WRV.ATTRIBUTE8
, 'ATTRIBUTE9'
, WRV.ATTRIBUTE9
, 'ATTRIBUTE10'
, WRV.ATTRIBUTE10
, 'ATTRIBUTE11'
, WRV.ATTRIBUTE11
, 'ATTRIBUTE12'
, WRV.ATTRIBUTE12
, 'ATTRIBUTE13'
, WRV.ATTRIBUTE13
, 'ATTRIBUTE14'
, WRV.ATTRIBUTE14
, 'ATTRIBUTE15'
, WRV.ATTRIBUTE15
, '2') = '1' UNION ALL /* FIELD SERVICE JTF AT CUSTOMER */ SELECT 1
, NVL(CDL.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 7
, NVL(CDL.RECEIVING_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')
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 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.RECEIVING_INVENTORY_ORG_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND CIA.INCIDENT_ID = JTV.SOURCE_OBJECT_ID
AND JTA.TASK_ID = JTV.TASK_ID
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'SR'
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 CTT.TRANSACTION_TYPE_ID = CDL.TRANSACTION_TYPE_ID
AND CTT.LINE_ORDER_CATEGORY_CODE = 'RETURN'
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