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.
MATERIALIZED VIEW: APPS.ISC_DBI_SCR_000_MV

Source


SELECT /* 12.0: bug#4526784 */  0							DATA_MARKER,  sc.sales_grp_id					SALES_GRP_ID,  sc.resource_id					RESOURCE_ID,  f.customer_id						CUSTOMER_ID,  class.class_code					CLASS_CODE,  nvl(item.vbh_category_id,-1)				ITEM_CATEGORY_ID,  f.item_id						INVENTORY_ITEM_ID,  substr(item.MASTER_ID, instr(item.MASTER_ID,'-')+1)	MASTER_ORAGNIZATION_ID,  to_number(to_char(f.time_booked_date_id,'J'))	TIME_ID,  sum(decode( f.line_category_code, 'RETURN',0,1)   * f.booked_amt_g * sc.sales_credit_percent / 100)	BOOKED_AMT_G,  count(decode( f.line_category_code, 'RETURN',0,1)   * f.booked_amt_g * sc.sales_credit_percent / 100)	BOOKED_AMT_G_CNT,  sum(decode( f.line_category_code, 'RETURN',1,0)   * f.booked_amt_g * sc.sales_credit_percent / 100)	RETURNED_AMT_G,  count(decode( f.line_category_code, 'RETURN',1,0)   * f.booked_amt_g * sc.sales_credit_percent / 100)	RETURNED_AMT_G_CNT,  sum(decode( f.line_category_code, 'RETURN',-1,1)   * f.booked_amt_g * sc.sales_credit_percent / 100)	NET_BOOKED_AMT_G,  count(decode( f.line_category_code, 'RETURN',-1,1)   * f.booked_amt_g * sc.sales_credit_percent / 100)	NET_BOOKED_AMT_G_CNT,  sum(decode( f.line_category_code,'RETURN',-1,1)   * CASE WHEN (f.time_fulfilled_date_id IS NULL and f.open_flag = 'N')     THEN 0 ELSE 1 END   * f.booked_amt_g * sc.sales_credit_percent / 100)	NET_BOOKED_AMT2_G,  count(decode( f.line_category_code,'RETURN',-1,1)   * CASE WHEN (f.time_fulfilled_date_id IS NULL and f.open_flag = 'N')     THEN 0 ELSE 1 END   * f.booked_amt_g * sc.sales_credit_percent / 100)	NET_BOOKED_AMT2_G_CNT,  sum(0)						NET_FULFILLED_AMT_G,  count(0)						NET_FULFILLED_AMT_G_CNT,  sum(decode( f.line_category_code, 'RETURN',0,1)   * f.booked_amt_g1 * sc.sales_credit_percent / 100)	BOOKED_AMT_G1,  count(decode( f.line_category_code, 'RETURN',0,1)   * f.booked_amt_g1 * sc.sales_credit_percent / 100)	BOOKED_AMT_G1_CNT,  sum(decode( f.line_category_code, 'RETURN',1,0)   * f.booked_amt_g1 * sc.sales_credit_percent / 100)	RETURNED_AMT_G1,  count(decode( f.line_category_code, 'RETURN',1,0)   * f.booked_amt_g1 * sc.sales_credit_percent / 100)	RETURNED_AMT_G1_CNT,  sum(decode( f.line_category_code, 'RETURN',-1,1)   * f.booked_amt_g1 * sc.sales_credit_percent / 100)	NET_BOOKED_AMT_G1,  count(decode( f.line_category_code, 'RETURN',-1,1)   * f.booked_amt_g1 * sc.sales_credit_percent / 100)	NET_BOOKED_AMT_G1_CNT,  sum(decode( f.line_category_code,'RETURN',-1,1)   * CASE WHEN (f.time_fulfilled_date_id IS NULL and f.open_flag = 'N')     THEN 0 ELSE 1 END   * f.booked_amt_g1 * sc.sales_credit_percent / 100)	NET_BOOKED_AMT2_G1,  count(decode( f.line_category_code,'RETURN',-1,1)   * CASE WHEN (f.time_fulfilled_date_id IS NULL and f.open_flag = 'N')     THEN 0 ELSE 1 END   * f.booked_amt_g1 * sc.sales_credit_percent / 100)	NET_BOOKED_AMT2_G1_CNT,  sum(0)						NET_FULFILLED_AMT_G1,  count(0)						NET_FULFILLED_AMT_G1_CNT,  NULL                                                  AMOUNT_B,  NULL                                                  PRIM_RECOGNIZED_AMT_G,  NULL                                                  PRIM_DEFERRED_AMT_G,  NULL                                                  SEC_RECOGNIZED_AMT_G,  NULL                                                  SEC_DEFERRED_AMT_G,  NULL   						BOOKED_REV_PRIM_YR,   NULL   						BOOKED_REV_PRIM_QR,   NULL    						BOOKED_REV_PRIM_PE,   NULL    						BOOKED_REV_PRIM_WK,      NULL    						BOOKED_REV_SEC_YR,   NULL    						BOOKED_REV_SEC_QR,   NULL    						BOOKED_REV_SEC_PE,    NULL    						BOOKED_REV_SEC_WK,    NULL    						BOOKED_REV_PRIM_YRCT,    NULL    						BOOKED_REV_PRIM_QRCT,    NULL    						BOOKED_REV_PRIM_PECT,      NULL    						BOOKED_REV_PRIM_WKCT,      NULL    						BOOKED_REV_SEC_YRCT,       NULL        						BOOKED_REV_SEC_QRCT,    NULL        						BOOKED_REV_SEC_PECT,      NULL        						BOOKED_REV_SEC_WKCT,      NULL                                                  AMOUNT_BCT,  NULL                                                  PRIM_RECOGNIZED_AMT_GCT,  NULL                                                  PRIM_DEFERRED_AMT_GCT,  NULL                                                  SEC_RECOGNIZED_AMT_GCT,  NULL                                                  SEC_DEFERRED_AMT_GCT,  count(*)                                              CT  FROM ISC.ISC_BOOK_SUM2_F	f,       ISC.ISC_SALES_CREDITS_F	sc,       ENI.ENI_OLTP_ITEM_STAR	item,       FII.FII_PARTY_MKT_CLASS	class  WHERE f.line_id = sc.line_id   AND f.inventory_item_id = item.inventory_item_id   AND f.item_inv_org_id = item.organization_id   AND f.customer_id = class.party_id   AND f.order_source_id <> 27   AND f.order_source_id <> 10   AND f.item_type_code <> 'SERVICE'   AND f.ordered_quantity <> 0   AND f.charge_periodicity_code is NULL GROUP BY       sc.resource_id,sc.sales_grp_id,       f.customer_id,class.class_code,nvl(item.vbh_category_id,-1),       to_number(to_char(f.time_booked_date_id,'J')),       f.item_id,       substr(item.MASTER_ID, instr(item.MASTER_ID,'-')+1)  UNION ALL  SELECT /* 12.0: bug#4526784 */ /*+ ORDERED */  1							DATA_MARKER,  nvl(sr.salesgroup_id, nvl(g.group_id, -1))            SALES_GRP_ID,  nvl(g.resource_id, -1)				RESOURCE_ID,  rev.BILL_TO_PARTY_ID					CUSTOMER_ID,  class.class_code					CLASS_CODE,  nvl(star.VBH_CATEGORY_ID, -1)        			ITEM_CATEGORY_ID,  nvl(rev.inventory_item_id, -1)			INVENTORY_ITEM_ID,  substr(star.MASTER_ID, instr(star.MASTER_ID,'-')+1)	MASTER_ORAGNIZATION_ID,  to_number(to_char(rev.invoice_date,'J'))		TIME_ID,  sum(0)						BOOKED_AMT_G,  count(0)						BOOKED_AMT_G_CNT,  sum(0)						RETURNED_AMT_G,  count(0)						RETURNED_AMT_G_CNT,  sum(0)						NET_BOOKED_AMT_G,  count(0)						NET_BOOKED_AMT_G_CNT,  sum(0)						NET_BOOKED_AMT2_G,  count(0)						NET_BOOKED_AMT2_G_CNT,  sum(rev.prim_amount_g * sr.revenue_percent_split / 100)	NET_FULFILLED_AMT_G,  count(rev.prim_amount_g * sr.revenue_percent_split / 100)	NET_FULFILLED_AMT_G_CNT,  sum(0)						BOOKED_AMT_G1,  count(0)						BOOKED_AMT_G1_CNT,  sum(0)						RETURNED_AMT_G1,  count(0)						RETURNED_AMT_G1_CNT,  sum(0)						NET_BOOKED_AMT_G1,  count(0)						NET_BOOKED_AMT_G1_CNT,  sum(0)						NET_BOOKED_AMT2_G1,  count(0)						NET_BOOKED_AMT2_G1_CNT,  sum(rev.sec_amount_g * sr.revenue_percent_split / 100)	NET_FULFILLED_AMT_G1,  count(rev.sec_amount_g * sr.revenue_percent_split / 100)	NET_FULFILLED_AMT_G1_CNT,  NULL                                                  AMOUNT_B,  NULL                                                  PRIM_RECOGNIZED_AMT_G,  NULL                                                  PRIM_DEFERRED_AMT_G,  NULL                                                  SEC_RECOGNIZED_AMT_G,  NULL                                                  SEC_DEFERRED_AMT_G,  NULL   						BOOKED_REV_PRIM_YR,   NULL   						BOOKED_REV_PRIM_QR,   NULL    						BOOKED_REV_PRIM_PE,   NULL    						BOOKED_REV_PRIM_WK,      NULL    						BOOKED_REV_SEC_YR,   NULL    						BOOKED_REV_SEC_QR,   NULL    						BOOKED_REV_SEC_PE,    NULL    						BOOKED_REV_SEC_WK,    NULL    						BOOKED_REV_PRIM_YRCT,    NULL    						BOOKED_REV_PRIM_QRCT,    NULL    						BOOKED_REV_PRIM_PECT,      NULL    						BOOKED_REV_PRIM_WKCT,      NULL    						BOOKED_REV_SEC_YRCT,       NULL        						BOOKED_REV_SEC_QRCT,    NULL        						BOOKED_REV_SEC_PECT,      NULL        						BOOKED_REV_SEC_WKCT,      NULL                                                  AMOUNT_BCT,  NULL                                                  PRIM_RECOGNIZED_AMT_GCT,  NULL                                                  PRIM_DEFERRED_AMT_GCT,  NULL                                                  SEC_RECOGNIZED_AMT_GCT,  NULL                                                  SEC_DEFERRED_AMT_GCT,  count(*)                                              CT  FROM      JTF.JTF_RS_SRP_GROUPS g,      FII.FII_AR_SALES_CREDITS sr,      FII.FII_AR_REVENUE_B rev,      ENI.ENI_OLTP_ITEM_STAR star,      FII.FII_PARTY_MKT_CLASS class  WHERE  rev.invoice_line_id = sr.invoice_line_id  and g.salesrep_id = sr.salesrep_id  and g.org_id = rev.operating_unit_id  and rev.invoice_date between g.start_date and g.end_date  and rev.inventory_item_id = star.inventory_item_id  and rev.item_organization_id = star.organization_id  and rev.bill_to_party_id = class.party_id  and rev.OM_PRODUCT_REVENUE_FLAG = 'Y'  GROUP BY      nvl(rev.inventory_item_id, -1),      to_number(to_char(rev.invoice_date,'J')),      nvl(sr.salesgroup_id, nvl(g.group_id, -1)),      nvl(g.resource_id, -1),      substr(star.MASTER_ID, instr(star.MASTER_ID,'-')+1),      rev.BILL_TO_PARTY_ID, class.class_code,     nvl(star.VBH_CATEGORY_ID, -1)  UNION ALL  SELECT /* 12.0: bug#4526784 */ /*+ ORDERED */  2							DATA_MARKER,  nvl(sr.salesgroup_id, nvl(g.group_id, -1))            SALES_GRP_ID,  nvl(g.resource_id, -1)				RESOURCE_ID,  rev.BILL_TO_PARTY_ID					CUSTOMER_ID,  class.class_code					CLASS_CODE,  nvl(star.VBH_CATEGORY_ID, -1)        			ITEM_CATEGORY_ID,  nvl(rev.inventory_item_id, -1)			INVENTORY_ITEM_ID,  substr(star.MASTER_ID, instr(star.MASTER_ID,'-')+1)	MASTER_ORAGNIZATION_ID,  greatest(rev.gl_date_id,    to_number(to_char(global_start_date,'J')))  	TIME_ID,  NULL							BOOKED_AMT_G,  NULL		      					BOOKED_AMT_G_CNT,  NULL							RETURNED_AMT_G,  NULL    						RETURNED_AMT_G_CNT,  NULL							NET_BOOKED_AMT_G,  NULL    						NET_BOOKED_AMT_G_CNT,  NULL							NET_BOOKED_AMT2_G,  NULL    						NET_BOOKED_AMT2_G_CNT,  NULL                                           	NET_FULFILLED_AMT_G,  NULL                                           	NET_FULFILLED_AMT_G_CNT,  NULL							BOOKED_AMT_G1,  NULL		      					BOOKED_AMT_G1_CNT,  NULL							RETURNED_AMT_G1,  NULL    						RETURNED_AMT_G1_CNT,  NULL							NET_BOOKED_AMT_G1,  NULL    						NET_BOOKED_AMT_G1_CNT,  NULL							NET_BOOKED_AMT2_G1,  NULL    						NET_BOOKED_AMT2_G1_CNT,  NULL                                           	NET_FULFILLED_AMT_G1,  NULL                                           	NET_FULFILLED_AMT_G1_CNT,  sum(rev.AMOUNT_B * sr.revenue_percent_split / 100)    AMOUNT_B,   sum(decode(rev.FIN_CAT_TYPE_CODE, 'R', rev.PRIM_AMOUNT_G, 0)   * sr.revenue_percent_split / 100)			PRIM_RECOGNIZED_AMT_G,   sum(decode(rev.FIN_CAT_TYPE_CODE, 'DR', rev.PRIM_AMOUNT_G, 0)   * sr.revenue_percent_split / 100)			PRIM_DEFERRED_AMT_G,   sum(decode(rev.FIN_CAT_TYPE_CODE, 'R', rev.SEC_AMOUNT_G, 0)   * sr.revenue_percent_split / 100)			SEC_RECOGNIZED_AMT_G,   sum(decode(rev.FIN_CAT_TYPE_CODE, 'DR', rev.SEC_AMOUNT_G, 0)   * sr.revenue_percent_split / 100)			SEC_DEFERRED_AMT_G,   sum(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_YEAR_START_DATE)     then rev.prim_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_PRIM_YR,  sum(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_QTR_START_DATE)     then rev.prim_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_PRIM_QR,  sum(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_PERIOD_START_DATE)     then rev.prim_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_PRIM_PE,  sum(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.WEEK_START_DATE)     then  rev.prim_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_PRIM_WK,    sum(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_YEAR_START_DATE)     then   rev.sec_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_SEC_YR,  sum(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_QTR_START_DATE)     then  rev.sec_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_SEC_QR,  sum(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_PERIOD_START_DATE)     then rev.sec_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_SEC_PE,  sum(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.WEEK_START_DATE)     then rev.sec_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_SEC_WK,    count(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_YEAR_START_DATE)     then  rev.prim_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_PRIM_YRCT,  count(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_QTR_START_DATE)     then rev.prim_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_PRIM_QRCT,  count(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_PERIOD_START_DATE)     then  rev.prim_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_PRIM_PECT,  count(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.WEEK_START_DATE)     then  rev.prim_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_PRIM_WKCT,  count(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_YEAR_START_DATE)     then  rev.sec_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_SEC_YRCT,  count(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_QTR_START_DATE)      then  rev.sec_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_SEC_QRCT,  count(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.ENT_PERIOD_START_DATE)      then  rev.sec_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_SEC_PECT,  count(case when (rev.FIN_CAT_TYPE_CODE = 'R' and rev.rev_booked_date  >=  d.WEEK_START_DATE)      then  rev.sec_amount_g * sr.revenue_percent_split / 100 else 0 end )  BOOKED_REV_SEC_WKCT,    count(rev.AMOUNT_B * sr.revenue_percent_split / 100)  AMOUNT_BCT,   count(decode(rev.FIN_CAT_TYPE_CODE, 'R', rev.PRIM_AMOUNT_G, 0)   * sr.revenue_percent_split / 100)			PRIM_RECOGNIZED_AMT_GCT,   count(decode(rev.FIN_CAT_TYPE_CODE, 'DR', rev.PRIM_AMOUNT_G, 0)   * sr.revenue_percent_split / 100)			PRIM_DEFERRED_AMT_GCT,   count(decode(rev.FIN_CAT_TYPE_CODE, 'R', rev.SEC_AMOUNT_G, 0)   * sr.revenue_percent_split / 100)			SEC_RECOGNIZED_AMT_GCT,   count(decode(rev.FIN_CAT_TYPE_CODE, 'DR', rev.SEC_AMOUNT_G, 0)   * sr.revenue_percent_split / 100)			SEC_DEFERRED_AMT_GCT,  count(*)            					CT  FROM      FII.FII_GLOBAL_START_DATES gsd,      JTF.JTF_RS_SRP_GROUPS g,      FII.FII_AR_SALES_CREDITS sr,      FII.FII_AR_REVENUE_B rev,      ENI.ENI_OLTP_ITEM_STAR star,       FII.FII_PARTY_MKT_CLASS class,      FII.FII_TIME_DAY d  WHERE  rev.invoice_line_id = sr.invoice_line_id  and g.salesrep_id = sr.salesrep_id  and g.org_id = rev.operating_unit_id  and rev.gl_date between g.start_date and g.end_date  and rev.inventory_item_id = star.inventory_item_id  and rev.item_organization_id = star.organization_id  and rev.bill_to_party_id = class.party_id  and rev.OM_PRODUCT_REVENUE_FLAG = 'Y'  and rev.gl_date = d.report_date   GROUP BY      nvl(rev.inventory_item_id, -1),      greatest(rev.gl_date_id,to_number(to_char(global_start_date,'J'))),      nvl(sr.salesgroup_id, nvl(g.group_id, -1)),      nvl(g.resource_id, -1),      substr(star.MASTER_ID, instr(star.MASTER_ID,'-')+1),      rev.BILL_TO_PARTY_ID, class.class_code,     nvl(star.VBH_CATEGORY_ID, -1)