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.MSD_SR_TOTAL_BACKLOG_V

Source

View Text - Preformatted


SELECT nvl(itm.organization_id, msd_sr_util.get_null_pk), null, nvl(itm.inventory_item_id, msd_sr_util.get_null_pk), null, 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(hca.cust_account_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), null, msd_sr_util.get_null_pk, null, msd_sr_util.get_null_pk, null, msd_sr_util.get_all_dcs_pk, null, s.end_date, s.curr_bklg_value, msd_sr_util.uom_conv(itm.PRIMARY_UOM_CODE, itm.inventory_item_id) * s.curr_bklg_qty, msd_sr_util.get_null_pk, null, msd_sr_util.get_null_pk, null FROM (SELECT end_date END_DATE, inv_org_id INV_ORG_ID, item_id ITEM_ID, customer_id CUSTOMER_ID, sum(c_y_bklg) + sum(c_book_ytd) - sum(c_fulf_ytd) CURR_BKLG_VALUE, sum(c_y_bklg_q) + sum(c_book_ytd_q) - sum(c_fulf_ytd_q) CURR_BKLG_QTY FROM /* Compute year backlog balance */ (SELECT day.end_date END_DATE, fact.inv_org_id INV_ORG_ID, fact.item_id ITEM_ID, fact.customer_id CUSTOMER_ID, fact.bklg_amt_g C_Y_BKLG, fact.bklog_qty C_Y_BKLG_Q, 0 C_BOOK_YTD, 0 C_FULF_YTD, 0 C_BOOK_YTD_Q, 0 C_FULF_YTD_Q FROM ISC_DBI_CFM_010_MV fact, FII_TIME_DAY day WHERE day.ent_year_start_date = fact.time_snapshot_date_id AND fact.item_cat_flag = 0 AND fact.customer_flag = 0 UNION ALL /* Computer YTD net Booking */ SELECT day.end_date END_DATE, fact.inv_org_id INV_ORG_ID, fact.item_id ITEM_ID, fact.customer_id CUSTOMER_ID, 0 C_Y_BKLG, 0 C_Y_BKLG_Q, fact.booked_amt_g C_BOOK_YTD, 0 C_FULF_YTD, fact.booked_qty C_BOOK_YTD_Q, 0 C_FULF_YTD_Q FROM ISC_DBI_CFM_004_MV fact, FII_TIME_RPT_STRUCT_V cal, FII_TIME_DAY day WHERE cal.report_date = least(day.end_date, sysdate) AND fact.time_id = cal.time_id AND fact.period_type_id = cal.period_type_id AND bitand(cal.record_type_id, 119) = cal.record_type_id AND fact.item_cat_flag = 0 AND fact.customer_flag = 0 UNION ALL /* Computer YTD net fulfillment */ SELECT day.end_date END_DATE, fact.inv_org_id INV_ORG_ID, fact.item_id ITEM_ID, fact.customer_id CUSTOMER_ID, 0 C_Y_BKLG, 0 C_Y_BKLG_Q, 0 C_BOOK_YTD, fact.booked_amt_g C_FULF_YTD, 0 C_BOOK_YTD_Q, fact.booked_qty C_FULF_YTD_Q FROM ISC_DBI_CFM_005_MV fact, FII_TIME_RPT_STRUCT_V cal, FII_TIME_DAY day WHERE cal.report_date = least(day.end_date, sysdate) AND fact.time_id = cal.time_id AND fact.period_type_id = cal.period_type_id AND bitand(cal.record_type_id, 119) = cal.record_type_id AND fact.item_cat_flag = 0 AND fact.customer_flag = 0 AND fact.return_flag = 0 AND fact.internal_flag = 0 ) GROUP BY end_date, inv_org_id, item_id, customer_id) s, FII_TIME_DAY fii, MTL_SYSTEM_ITEMS itm, msd_app_instance_orgs morg, msd_setup_parameters morg2, HZ_CUST_ACCOUNTS hca, (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust WHERE fii.end_date = s.end_date(+) AND morg2.parameter_name = 'MSD_MASTER_ORG' AND itm.inventory_item_id = substr(s.ITEM_ID,1,instr(s.ITEM_ID,'-')-1) AND itm.organization_id = s.inv_org_id and morg.organization_id = s.INV_ORG_ID and (itm.mrp_planning_code <> 6 or (itm.mrp_planning_code = 6 and itm.pick_components_flag = 'Y')) and hca.cust_account_id = s.customer_id ORDER BY fii.end_date

View Text - HTML Formatted

SELECT NVL(ITM.ORGANIZATION_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(ITM.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, 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(HCA.CUST_ACCOUNT_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_ALL_DCS_PK
, NULL
, S.END_DATE
, S.CURR_BKLG_VALUE
, MSD_SR_UTIL.UOM_CONV(ITM.PRIMARY_UOM_CODE
, ITM.INVENTORY_ITEM_ID) * S.CURR_BKLG_QTY
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
FROM (SELECT END_DATE END_DATE
, INV_ORG_ID INV_ORG_ID
, ITEM_ID ITEM_ID
, CUSTOMER_ID CUSTOMER_ID
, SUM(C_Y_BKLG) + SUM(C_BOOK_YTD) - SUM(C_FULF_YTD) CURR_BKLG_VALUE
, SUM(C_Y_BKLG_Q) + SUM(C_BOOK_YTD_Q) - SUM(C_FULF_YTD_Q) CURR_BKLG_QTY
FROM /* COMPUTE YEAR BACKLOG BALANCE */ (SELECT DAY.END_DATE END_DATE
, FACT.INV_ORG_ID INV_ORG_ID
, FACT.ITEM_ID ITEM_ID
, FACT.CUSTOMER_ID CUSTOMER_ID
, FACT.BKLG_AMT_G C_Y_BKLG
, FACT.BKLOG_QTY C_Y_BKLG_Q
, 0 C_BOOK_YTD
, 0 C_FULF_YTD
, 0 C_BOOK_YTD_Q
, 0 C_FULF_YTD_Q
FROM ISC_DBI_CFM_010_MV FACT
, FII_TIME_DAY DAY
WHERE DAY.ENT_YEAR_START_DATE = FACT.TIME_SNAPSHOT_DATE_ID
AND FACT.ITEM_CAT_FLAG = 0
AND FACT.CUSTOMER_FLAG = 0 UNION ALL /* COMPUTER YTD NET BOOKING */ SELECT DAY.END_DATE END_DATE
, FACT.INV_ORG_ID INV_ORG_ID
, FACT.ITEM_ID ITEM_ID
, FACT.CUSTOMER_ID CUSTOMER_ID
, 0 C_Y_BKLG
, 0 C_Y_BKLG_Q
, FACT.BOOKED_AMT_G C_BOOK_YTD
, 0 C_FULF_YTD
, FACT.BOOKED_QTY C_BOOK_YTD_Q
, 0 C_FULF_YTD_Q
FROM ISC_DBI_CFM_004_MV FACT
, FII_TIME_RPT_STRUCT_V CAL
, FII_TIME_DAY DAY
WHERE CAL.REPORT_DATE = LEAST(DAY.END_DATE
, SYSDATE)
AND FACT.TIME_ID = CAL.TIME_ID
AND FACT.PERIOD_TYPE_ID = CAL.PERIOD_TYPE_ID
AND BITAND(CAL.RECORD_TYPE_ID
, 119) = CAL.RECORD_TYPE_ID
AND FACT.ITEM_CAT_FLAG = 0
AND FACT.CUSTOMER_FLAG = 0 UNION ALL /* COMPUTER YTD NET FULFILLMENT */ SELECT DAY.END_DATE END_DATE
, FACT.INV_ORG_ID INV_ORG_ID
, FACT.ITEM_ID ITEM_ID
, FACT.CUSTOMER_ID CUSTOMER_ID
, 0 C_Y_BKLG
, 0 C_Y_BKLG_Q
, 0 C_BOOK_YTD
, FACT.BOOKED_AMT_G C_FULF_YTD
, 0 C_BOOK_YTD_Q
, FACT.BOOKED_QTY C_FULF_YTD_Q
FROM ISC_DBI_CFM_005_MV FACT
, FII_TIME_RPT_STRUCT_V CAL
, FII_TIME_DAY DAY
WHERE CAL.REPORT_DATE = LEAST(DAY.END_DATE
, SYSDATE)
AND FACT.TIME_ID = CAL.TIME_ID
AND FACT.PERIOD_TYPE_ID = CAL.PERIOD_TYPE_ID
AND BITAND(CAL.RECORD_TYPE_ID
, 119) = CAL.RECORD_TYPE_ID
AND FACT.ITEM_CAT_FLAG = 0
AND FACT.CUSTOMER_FLAG = 0
AND FACT.RETURN_FLAG = 0
AND FACT.INTERNAL_FLAG = 0 ) GROUP BY END_DATE
, INV_ORG_ID
, ITEM_ID
, CUSTOMER_ID) S
, FII_TIME_DAY FII
, MTL_SYSTEM_ITEMS ITM
, MSD_APP_INSTANCE_ORGS MORG
, MSD_SETUP_PARAMETERS MORG2
, HZ_CUST_ACCOUNTS HCA
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE FII.END_DATE = S.END_DATE(+)
AND MORG2.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND ITM.INVENTORY_ITEM_ID = SUBSTR(S.ITEM_ID
, 1
, INSTR(S.ITEM_ID
, '-')-1)
AND ITM.ORGANIZATION_ID = S.INV_ORG_ID
AND MORG.ORGANIZATION_ID = S.INV_ORG_ID
AND (ITM.MRP_PLANNING_CODE <> 6 OR (ITM.MRP_PLANNING_CODE = 6
AND ITM.PICK_COMPONENTS_FLAG = 'Y'))
AND HCA.CUST_ACCOUNT_ID = S.CUSTOMER_ID ORDER BY FII.END_DATE