Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-5356

NullPointerException thrown when unique index based on both system and non-system generated columns

    XMLWordPrintable

Details

    Description

       

      [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" ;
      

      Attachments

        1. desc.png
          desc.png
          89 kB
        2. idx.png
          idx.png
          41 kB
        3. idxqry.png
          idxqry.png
          44 kB
        4. mv_and_table_samename.png
          mv_and_table_samename.png
          7 kB
        5. no_constraints.png
          no_constraints.png
          18 kB

        Activity

          People

            ccranfor@redhat.com Chris Cranford
            tooptoop toop toop
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: