-
Bug
-
Resolution: Done
-
Major
-
2.0.0.Alpha3
-
None
-
False
-
None
-
False
[2022-07-03 06:30:49,834] INFO WorkerSourceTask{id=kafka-connect-src-01-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask) [2022-07-03 06:30:54,411] INFO Snapshot - Final stage (io.debezium.pipeline.source.AbstractSnapshotChangeEventSource) [2022-07-03 06:30:54,451] ERROR Producer failure (io.debezium.pipeline.ErrorHandler) io.debezium.DebeziumException: java.lang.NullPointerException at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:85) at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155) at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137) at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: java.lang.NullPointerException at io.debezium.relational.TableEditorImpl.columnWithName(TableEditorImpl.java:46) at io.debezium.relational.TableEditorImpl.hasColumnWithName(TableEditorImpl.java:50) at io.debezium.relational.TableEditorImpl.lambda$updatePrimaryKeys$0(TableEditorImpl.java:103) at java.base/java.util.ArrayList.removeIf(ArrayList.java:1702) at java.base/java.util.ArrayList.removeIf(ArrayList.java:1690) at io.debezium.relational.TableEditorImpl.updatePrimaryKeys(TableEditorImpl.java:102) at io.debezium.relational.TableEditorImpl.create(TableEditorImpl.java:267) at io.debezium.relational.Tables.lambda$overwriteTable$2(Tables.java:192) at io.debezium.util.FunctionalReadWriteLock.write(FunctionalReadWriteLock.java:84) at io.debezium.relational.Tables.overwriteTable(Tables.java:186) at io.debezium.jdbc.JdbcConnection.readSchema(JdbcConnection.java:1208) at io.debezium.connector.oracle.OracleSnapshotChangeEventSource.readTableStructure(OracleSnapshotChangeEventSource.java:167) at io.debezium.connector.oracle.OracleSnapshotChangeEventSource.readTableStructure(OracleSnapshotChangeEventSource.java:35) at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:115) at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:76) ... 8 more [2022-07-03 06:30:54,452] INFO Connected metrics set to 'false' (io.debezium.pipeline.ChangeEventSourceCoordinator) [2022-07-03 06:30:54,876] INFO WorkerSourceTask{id=kafka-connect-src-01-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask) [2022-07-03 06:30:54,876] ERROR WorkerSourceTask{id=kafka-connect-src-01-0} Task threw an uncaught and unrecoverable exception. Task is being killed and will not recover until manually restarted (org.apache.kafka.connect.runtime.WorkerTask) org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped. at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:50) at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:116) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: io.debezium.DebeziumException: java.lang.NullPointerException at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:85) at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155) at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137) at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109) ... 5 more Caused by: java.lang.NullPointerException at io.debezium.relational.TableEditorImpl.columnWithName(TableEditorImpl.java:46) at io.debezium.relational.TableEditorImpl.hasColumnWithName(TableEditorImpl.java:50) at io.debezium.relational.TableEditorImpl.lambda$updatePrimaryKeys$0(TableEditorImpl.java:103) at java.base/java.util.ArrayList.removeIf(ArrayList.java:1702) at java.base/java.util.ArrayList.removeIf(ArrayList.java:1690) at io.debezium.relational.TableEditorImpl.updatePrimaryKeys(TableEditorImpl.java:102) at io.debezium.relational.TableEditorImpl.create(TableEditorImpl.java:267) at io.debezium.relational.Tables.lambda$overwriteTable$2(Tables.java:192) at io.debezium.util.FunctionalReadWriteLock.write(FunctionalReadWriteLock.java:84) at io.debezium.relational.Tables.overwriteTable(Tables.java:186) at io.debezium.jdbc.JdbcConnection.readSchema(JdbcConnection.java:1208) at io.debezium.connector.oracle.OracleSnapshotChangeEventSource.readTableStructure(OracleSnapshotChangeEventSource.java:167) at io.debezium.connector.oracle.OracleSnapshotChangeEventSource.readTableStructure(OracleSnapshotChangeEventSource.java:35) at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:115) at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:76) ... 8 more [2022-07-03 06:30:54,876] INFO Stopping down connector (io.debezium.connector.common.BaseSourceTask)
got this error during initial snapshot^
CREATE TABLE "APPS"."ISC_DBI_SCR_000_MV" ( "DATA_MARKER" NUMBER, "SALES_GRP_ID" NUMBER, "RESOURCE_ID" NUMBER, "CUSTOMER_ID" NUMBER, "CLASS_CODE" VARCHAR2(30), "ITEM_CATEGORY_ID" NUMBER, "INVENTORY_ITEM_ID" NUMBER, "MASTER_ORAGNIZATION_ID" VARCHAR2(75), "TIME_ID" NUMBER, "BOOKED_AMT_G" NUMBER, "BOOKED_AMT_G_CNT" NUMBER, "RETURNED_AMT_G" NUMBER, "RETURNED_AMT_G_CNT" NUMBER, "NET_BOOKED_AMT_G" NUMBER, "NET_BOOKED_AMT_G_CNT" NUMBER, "NET_BOOKED_AMT2_G" NUMBER, "NET_BOOKED_AMT2_G_CNT" NUMBER, "NET_FULFILLED_AMT_G" NUMBER, "NET_FULFILLED_AMT_G_CNT" NUMBER, "BOOKED_AMT_G1" NUMBER, "BOOKED_AMT_G1_CNT" NUMBER, "RETURNED_AMT_G1" NUMBER, "RETURNED_AMT_G1_CNT" NUMBER, "NET_BOOKED_AMT_G1" NUMBER, "NET_BOOKED_AMT_G1_CNT" NUMBER, "NET_BOOKED_AMT2_G1" NUMBER, "NET_BOOKED_AMT2_G1_CNT" NUMBER, "NET_FULFILLED_AMT_G1" NUMBER, "NET_FULFILLED_AMT_G1_CNT" NUMBER, "AMOUNT_B" NUMBER, "PRIM_RECOGNIZED_AMT_G" NUMBER, "PRIM_DEFERRED_AMT_G" NUMBER, "SEC_RECOGNIZED_AMT_G" NUMBER, "SEC_DEFERRED_AMT_G" NUMBER, "BOOKED_REV_PRIM_YR" NUMBER, "BOOKED_REV_PRIM_QR" NUMBER, "BOOKED_REV_PRIM_PE" NUMBER, "BOOKED_REV_PRIM_WK" NUMBER, "BOOKED_REV_SEC_YR" NUMBER, "BOOKED_REV_SEC_QR" NUMBER, "BOOKED_REV_SEC_PE" NUMBER, "BOOKED_REV_SEC_WK" NUMBER, "BOOKED_REV_PRIM_YRCT" NUMBER, "BOOKED_REV_PRIM_QRCT" NUMBER, "BOOKED_REV_PRIM_PECT" NUMBER, "BOOKED_REV_PRIM_WKCT" NUMBER, "BOOKED_REV_SEC_YRCT" NUMBER, "BOOKED_REV_SEC_QRCT" NUMBER, "BOOKED_REV_SEC_PECT" NUMBER, "BOOKED_REV_SEC_WKCT" NUMBER, "AMOUNT_BCT" NUMBER, "PRIM_RECOGNIZED_AMT_GCT" NUMBER, "PRIM_DEFERRED_AMT_GCT" NUMBER, "SEC_RECOGNIZED_AMT_GCT" NUMBER, "SEC_DEFERRED_AMT_GCT" NUMBER, "CT" NUMBER ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "APPS_TS_SUMMARY";
CREATE MATERIALIZED VIEW APPS.ISC_DBI_SCR_000_MV TABLESPACE APPS_TS_SUMMARY BUILD DEFERRED REFRESH FAST ON DEMAND WITH ROWID DISABLE QUERY REWRITE AS 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) ;
CREATE UNIQUE INDEX APPS.U_SNAP$_ISC_DBI_SCR_000_MV ON APPS.ISC_DBI_SCR_000_MV (SYS_OP_MAP_NONNULL("INVENTORY_ITEM_ID") ASC, SYS_OP_MAP_NONNULL("TIME_ID") ASC, SYS_OP_MAP_NONNULL("SALES_GRP_ID") ASC, SYS_OP_MAP_NONNULL("RESOURCE_ID") ASC, SYS_OP_MAP_NONNULL("MASTER_ORAGNIZATION_ID") ASC, SYS_OP_MAP_NONNULL("CUSTOMER_ID") ASC, SYS_OP_MAP_NONNULL("CLASS_CODE") ASC, SYS_OP_MAP_NONNULL("ITEM_CATEGORY_ID") ASC, DATA_MARKER ASC) TABLESPACE APPS_TS_SUMMARY;
CREATE INDEX "APPS"."I_SNAP$_ISC_DBI_SCR_000_MV" ON "APPS"."ISC_DBI_SCR_000_MV" (SYS_OP_MAP_NONNULL("INVENTORY_ITEM_ID"), SYS_OP_MAP_NONNULL("TIME_ID"), SYS_OP_MAP_NONNULL("SALES_GRP_ID"), SYS_OP_MAP_NONNULL("RESOURCE_ID"), SYS_OP_MAP_NONNULL("MASTER_ORAGNIZATION_ID"), SYS_OP_MAP_NONNULL("CUSTOMER_ID"), SYS_OP_MAP_NONNULL("CLASS_CODE"), SYS_OP_MAP_NONNULL("ITEM_CATEGORY_ID"), SYS_OP_MAP_NONNULL("DATA_MARKER")) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "APPS_TS_TX_DATA" ;