Forum Stats

  • 3,826,581 Users
  • 2,260,667 Discussions
  • 7,897,008 Comments

Discussions

Categories

im using oracle 12c. select query executing faster but insert is getting longer

User_ROTJD
User_ROTJD Member Posts: 5 Green Ribbon
edited May 20, 2022 5:44AM in SQL & PL/SQL

there are more than 1crore rows in temp_final_consolidation table. here is the insert code. it getting more than 5 days to insert the record into tem_age_consolidate table. anyone please help to rectify the issue. here is my code. table structure is already created using the same query. while inserting its getting longer.

for below distinct row are 12

       ls_sql := 'select distinct(sub_fund_code) from temp_final_consolidated';

    OPEN ref_cur_c1 FOR ls_sql;

    LOOP

     FETCH Ref_cur_c1

      INTO ls_sub_fund;

     EXIT WHEN ref_cur_c1%NOTFOUND;

     

     ls_sql := Q'# INSERT INTO TEMP_AGE_CONSOLIDATED

      with temp_fc as (select * from temp_final_consolidated where sub_fund_code=:sub_fund_code)

          SELECT f.*,

            f.new_to_age new_to_age1

       FROM (SELECT e.*,

       row_number() over(PARTITION BY product_code, brokerage_type, report_description, date_flag_for_rate_selection, folio_no || purch_id || cons_code || brok_dlr_code || brok_dlr_catg, asset_class || sub_fund_code || scheme_code, source_asset_class || source_sub_fund_code || source_scheme_code, sys_regn_to_date, sys_regn_from_date, trade_date_to, trade_date_from, ter_location, transaction_head, sub_type, from_amount, to_amount, brokerage_rate , new_from_age, new_to_age||adj_advance_upfront ORDER BY product_code, brokerage_type, report_description, new_from_age, new_to_age) rank1 

      FROM (

        SELECT MIN(d.from_age) over(PARTITION BY product_code, brokerage_type, report_description, date_flag_for_rate_selection, folio_no || purch_id || cons_code || brok_dlr_code || brok_dlr_catg, asset_class || sub_fund_code || scheme_code, source_asset_class || source_sub_fund_code || source_scheme_code, sys_regn_to_date, sys_regn_from_date, trade_date_to, trade_date_from, ter_location, transaction_head, sub_type, from_amount, to_amount, brokerage_rate, new_to_age||adj_advance_upfront ORDER BY product_code, brokerage_type, report_description, new_to_age) new_from_age,

        d.*

        FROM (SELECT MAX(to_age) over(PARTITION BY product_code, brokerage_type, report_description, date_flag_for_rate_selection, folio_no || purch_id || cons_code || brok_dlr_code || brok_dlr_catg, asset_class || sub_fund_code || scheme_code, source_asset_class || source_sub_fund_code || source_scheme_code, sys_regn_to_date, sys_regn_from_date, trade_date_to, c.trade_date_from, ter_location, transaction_head, sub_type, from_amount, to_amount, brokerage_rate, c.root_from_age||adj_advance_upfront ORDER BY root_from_age) new_to_age,

                c.*

        FROM (SELECT c.*,

          connect_by_root from_age AS root_from_age

          FROM temp_fc c

         CONNECT BY PRIOR to_age + 1 = from_age AND

            PRIOR

             nvl(brokerage_rate, 0) = nvl(brokerage_rate, 0) AND

            PRIOR nvl(brokerage_type, '$$') =

             nvl(brokerage_type, '$$') AND

            PRIOR

             nvl(product_code, '$$') = nvl(product_code, '$$') AND

            PRIOR nvl(report_description, '$$') =

             nvl(report_description, '$$') AND

            PRIOR nvl(folio_no, '$$') = nvl(folio_no, '$$') AND

            PRIOR nvl(purch_id, 0) = nvl(purch_id, 0) AND

            PRIOR nvl(cons_code, '$$') = nvl(cons_code, '$$') AND

            PRIOR

             nvl(brok_dlr_code, '$$') = nvl(brok_dlr_code, '$$') AND

            PRIOR

             nvl(brok_dlr_catg, '$$') = nvl(brok_dlr_catg, '$$') AND

            PRIOR

             nvl(asset_class, '$$') = nvl(asset_class, '$$') AND

            PRIOR

             nvl(sub_fund_code, '$$') = nvl(sub_fund_code, '$$') AND

            PRIOR

             nvl(scheme_code, '$$') = nvl(scheme_code, '$$') AND

            PRIOR nvl(source_asset_class, '$$') =

             nvl(source_asset_class, '$$') AND

            PRIOR nvl(source_sub_fund_code, '$$') =

             nvl(source_sub_fund_code, '$$') AND

            PRIOR nvl(source_scheme_code, '$$') =

             nvl(source_scheme_code, '$$') AND

            PRIOR nvl(load_basis, '$$') = nvl(load_basis, '$$') AND

            PRIOR nvl(fee_aging, '$$') = nvl(fee_aging, '$$') AND

            PRIOR nvl(transaction_head, '$$') =

             nvl(transaction_head, '$$') AND

            PRIOR nvl(sub_type, '$$') = nvl(sub_type, '$$') AND

            PRIOR nvl(date_flag_for_rate_selection, '$$') =

             nvl(date_flag_for_rate_selection, '$$') AND

            PRIOR nvl(sys_regn_from_date, '31-Dec-2999') =

             nvl(sys_regn_from_date, '31-Dec-2999') AND

            PRIOR nvl(sys_regn_to_date, '31-Dec-2999') =

             nvl(sys_regn_to_date, '31-Dec-2999') AND

            PRIOR nvl(trade_date_from, '31-Dec-2999') =

             nvl(trade_date_from, '31-Dec-2999') AND

            PRIOR nvl(trade_date_to, '31-Dec-2999') =

             nvl(trade_date_to, '31-Dec-2999') AND

            PRIOR

             nvl(ter_location, '$$') = nvl(ter_location, '$$') AND

            PRIOR nvl(from_amount, 0) = nvl(from_amount, 0) AND

            PRIOR nvl(to_amount, 0) = nvl(to_amount, 0) AND

            PRIOR nvl(tf_valuation_basis, '$$') =

             nvl(tf_valuation_basis, '$$')

         ORDER BY brokerage_rate,

            folio_no,

            purch_id,

            cons_code,

            brok_dlr_code,

            brok_dlr_catg,

            asset_class,

            sub_fund_code,

            scheme_code,

            source_asset_class,

            source_sub_fund_code,

            source_scheme_code,

            load_basis,

            fee_aging,

            transaction_head,

            sub_type,

            date_flag_for_rate_selection,

            sys_regn_from_date,

            sys_regn_to_date,

            trade_date_from,

            trade_date_to,

            ter_location,

            from_amount,

            tf_valuation_basis) c) d) e) f

          WHERE rank1 = 1 #';

     

     execute immediate ls_sql

      using ls_sub_fund;

     commit;

    END LOOP;

    CLOSE ref_cur_c1;

Tagged:
· Share on TwitterShare on Facebook

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    We don't have your tables or data so have no idea whether it's because of the structure of your tables or your SQL.

    Some obvious things about your (unformatted) code...

    a) You're doing multiple inserts within a cursor loop - that's row by row processing (aka slow-by-slow). You should always aim to do things 'set based' by using just straightforward SQL if you can to do all the data in one go. There has to be good reason to result in doing it in loops.

    b) You're using dynamic SQL to do the insert. Why?

    c) Your insert query is using, what looks like, a seriously hideous connect by query with lots of conditions to check. Are those things indexed? I doubt it.

    d) You have analytic functions that are partitioning by loads of concatenated data.... is that really necessary? What are you trying to achieve there?

    e) Some of your connect by conditions appear to be using default values where null by using the NVL function, but some of those look like they should be defaulting as a DATE datatype, but you are providing VARCHAR2 strings. No only will those rely on implicit datatype conversion, but the code may easily fail in another database session where the NLS date format is set differently. If the column is of DATE datatype, then the default values should be explicitly converted to DATE datatype also.


    We'd need to see the table structures, and some example data and understand the logic to understand why your insert is taking so long. Also, the query execution plan and trace would be good to see, as it looks seriously like the SELECT part of that insert is the performance issue at a glance.

    · Share on TwitterShare on Facebook
  • User_ROTJD
    User_ROTJD Member Posts: 5 Green Ribbon

    this query execution getting long executing mote than 6days. i thinks its getting infinite loop.

    we are doing consolidating the value by using column are mentioned in connect by clause. based on the hierarchy we took rank1 rows. basically we reducing the number of rows. this query is giving the result in 30sec for records more than 40lakhs. but I'm facing issue when trying to execute attached data there is 972 rows available. please help me find out the issue . (record attached which is pertains to table temp_final_consolidated_1)

    temp_final_consolidated_1.xlsx


    with temp_fc as

     (select /*+PARALLEL (C,6)*/

      C.*

      from temp_final_consolidated_1 C )

    SELECT f.*, f.new_to_age new_to_age1

     FROM (SELECT e.*,

            row_number() over(PARTITION BY product_code, brokerage_type, report_description, date_flag_for_rate_selection, folio_no || purch_id || cons_code || brok_dlr_code || brok_dlr_catg, asset_class || sub_fund_code || scheme_code, source_asset_class || source_sub_fund_code || source_scheme_code, sys_regn_to_date, sys_regn_from_date, trade_date_to, trade_date_from, ter_location, transaction_head, sub_type, from_amount, to_amount, brokerage_rate, new_from_age, new_to_age || adj_advance_upfront ORDER BY product_code, brokerage_type, report_description, new_from_age, new_to_age) rank1 

         FROM (SELECT MIN(d.from_age) over(PARTITION BY product_code, brokerage_type, report_description, date_flag_for_rate_selection, folio_no || purch_id || cons_code || brok_dlr_code || brok_dlr_catg, asset_class || sub_fund_code || scheme_code, source_asset_class || source_sub_fund_code || source_scheme_code, sys_regn_to_date, sys_regn_from_date, trade_date_to, trade_date_from, ter_location, transaction_head, sub_type, from_amount, to_amount, brokerage_rate, new_to_age || adj_advance_upfront ORDER BY product_code, brokerage_type, report_description, new_to_age) new_from_age, 

                d.*

             FROM (SELECT MAX(to_age) over(PARTITION BY product_code, brokerage_type, report_description, date_flag_for_rate_selection, folio_no || purch_id || cons_code || brok_dlr_code || brok_dlr_catg, asset_class || sub_fund_code || scheme_code, source_asset_class || source_sub_fund_code || source_scheme_code, sys_regn_to_date, sys_regn_from_date, trade_date_to, c.trade_date_from, ter_location, transaction_head, sub_type, from_amount, to_amount, brokerage_rate, c.root_from_age || adj_advance_upfront ORDER BY root_from_age) new_to_age, 

                    c.*

                 FROM (SELECT /*+PARALLEL (C,6)*/

                     c.*, connect_by_root from_age AS root_from_age

                     FROM temp_fc c

                    CONNECT BY prior to_age+1 = from_age and

                        PRIOR nvl(brokerage_rate, 0) =

                          nvl(brokerage_rate, 0)

                        AND PRIOR nvl(brokerage_type, '$$') =

                          nvl(brokerage_type, '$$')

                        AND PRIOR nvl(product_code, '$$') =

                          nvl(product_code, '$$')

                        AND PRIOR nvl(report_description, '$$') =

                          nvl(report_description, '$$')

                        AND PRIOR nvl(folio_no, '$$') =

                          nvl(folio_no, '$$')

                        AND PRIOR

                          nvl(purch_id, 0) = nvl(purch_id, 0)

                        AND PRIOR nvl(cons_code, '$$') =

                          nvl(cons_code, '$$')

                        AND PRIOR nvl(brok_dlr_code, '$$') =

                          nvl(brok_dlr_code, '$$')

                        AND PRIOR nvl(brok_dlr_catg, '$$') =

                          nvl(brok_dlr_catg, '$$')

                        AND PRIOR nvl(asset_class, '$$') =

                          nvl(asset_class, '$$')

                        AND PRIOR nvl(sub_fund_code, '$$') =

                          nvl(sub_fund_code, '$$')

                        AND PRIOR nvl(scheme_code, '$$') =

                          nvl(scheme_code, '$$')

                        AND PRIOR nvl(source_asset_class, '$$') =

                          nvl(source_asset_class, '$$')

                        AND PRIOR nvl(source_sub_fund_code, '$$') =

                          nvl(source_sub_fund_code, '$$')

                        AND PRIOR nvl(source_scheme_code, '$$') =

                          nvl(source_scheme_code, '$$')

                        AND PRIOR nvl(load_basis, '$$') =

                          nvl(load_basis, '$$')

                        AND PRIOR nvl(fee_aging, '$$') =

                          nvl(fee_aging, '$$')

                        AND PRIOR nvl(transaction_head, '$$') =

                          nvl(transaction_head, '$$')

                        AND PRIOR nvl(sub_type, '$$') =

                          nvl(sub_type, '$$')

                        AND PRIOR

                          nvl(date_flag_for_rate_selection,

                            '$$') = nvl(date_flag_for_rate_selection,

                                  '$$')

                        AND PRIOR nvl(sys_regn_from_date,

                               '31-Dec-2999') =

                          nvl(sys_regn_from_date,

                               '31-Dec-2999')

                        AND PRIOR

                          nvl(sys_regn_to_date, '31-Dec-2999') =

                          nvl(sys_regn_to_date, '31-Dec-2999')

                        AND PRIOR

                          nvl(trade_date_from, '31-Dec-2999') =

                          nvl(trade_date_from, '31-Dec-2999')

                        AND PRIOR

                          nvl(trade_date_to, '31-Dec-2999') =

                          nvl(trade_date_to, '31-Dec-2999')

                        AND PRIOR nvl(ter_location, '$$') =

                          nvl(ter_location, '$$')

                        AND PRIOR nvl(from_amount, 0) =

                          nvl(from_amount, 0)

                        AND PRIOR nvl(to_amount, 0) =

                          nvl(to_amount, 0)

                        AND PRIOR nvl(tf_valuation_basis, '$$') =

                          nvl(tf_valuation_basis, '$$')

                           

                     ORDER BY from_age,

                     to_age,brokerage_rate,

                         folio_no,

                         purch_id,

                         cons_code,

                         brok_dlr_code,

                         brok_dlr_catg,

                         asset_class,

                         sub_fund_code,

                         scheme_code,

                         source_asset_class,

                         source_sub_fund_code,

                         source_scheme_code,

                         load_basis,

                         fee_aging,

                         transaction_head,

                         sub_type,

                         date_flag_for_rate_selection,

                         sys_regn_from_date,

                         sys_regn_to_date,

                         trade_date_from,

                         trade_date_to,

                         ter_location,

                         from_amount,

                         tf_valuation_basis) c) d) e) f

     WHERE rank1 = 1

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    Please post create table and insert statements for example data directly here on the forum.

    Nobody with any sense downloads Office documents from unknown people on the internet (and some companies actually prevent their employees from doing that)

    · Share on TwitterShare on Facebook
  • User_ROTJD
    User_ROTJD Member Posts: 5 Green Ribbon

    insert statement is on next comment

    create table temp_final_consolidated_1

    (BROKERAGE_TYPE varchar2(5),

    REPORT_DESCRIPTION varchar2(100),

    SETUP_CREATED_ON date,

    SETUP_CLOSED_ON date,

    FOLIO_NO varchar2(10),

    PURCH_ID number(20),

    CONS_CODE varchar2(20),

    BROK_DLR_CODE varchar2(20),

    BROK_DLR_CATG varchar2(20),

    ASSET_CLASS varchar2(10),

    SUB_FUND_CODE varchar2(5),

    SCHEME_CODE varchar2(5),

    SOURCE_ASSET_CLASS varchar2(10),

    SOURCE_SUB_FUND_CODE varchar2(5),

    SOURCE_SCHEME_CODE varchar2(5),

    LOAD_BASIS varchar2(1),

    TRADE_DATE_FROM date,

    TRADE_DATE_TO date,

    SYS_REGN_FROM_DATE date,

    SYS_REGN_TO_DATE date,

    DATE_FLAG_FOR_RATE_SELECTION varchar2(2),

    FEE_AGING char(1),

    TER_LOCATION varchar2(1),

    TRANSACTION_HEAD varchar2(20),

    SUB_TYPE varchar2(10),

    FROM_AMOUNT number(25,8),

    TO_AMOUNT number(25,8),

    FROM_AGE number(4),

    TO_AGE number(4),

    AM_RATE number(8,4),

    TF_VALUATION_BASIS char(1),

    TRXN_LEVEL_FEE char(1),

    DYNAMIC_RATES char(1),

    DEFER_FREQUENCY char(1),

    NO_OF_INSTALMENTS_DEFERRED number(5),

    TRANSACTION_AGING char(1),

    BROKERAGE_RATE number(8,4),

    STRUCTURE_REF_NO varchar2(50),

    CREATED_AUTHORIZED_BY varchar2(100),

    UPFRONT_AMORT_TENURE number(5),

    REMARKS varchar2(1000),

    INCLUDE_DR char(1),

    INCLUDE_DTP char(1),

    SOURCE_PROGRAM varchar2(50),

    WEIGHTAGE number(25),

    STRUCTURE_SEQ_NO number(10),

    OVERRIDING_SETUP char(1),

    INSTALMENT_NO_FROM number(10),

    INSTALMENT_NO_TO number(10),

    LINKED_TO_EXIT_LOAD char(1),

    SIP_PERIODICITY varchar2(5),

    SIP_FROM_TENURE number(10),

    SIP_TO_TENURE number(10),

    RETRO_ADJUSTMENT_REQUIRED char(1),

    RETRO_ADJ_PROCESS_RUN_DATE date,

    VALID_FLAG char(1),

    VALID_FLAG_UPDATED_DATE date,

    PRODUCT_CODE varchar2(10),

    INSERTED_ON date,

    COMPUTING_FREQUENCY char(1),

    OLD_TF_RATE number(8,4),

    RATE_REVISION_EFFECTIVE_DATE date,

    CLAWBACK_APPLICABLE char(1),

    PAYMENT_TYPE varchar2(3),

    FORMULAE_TYPE varchar2(1),

    OVERLAP_TYPE varchar2(16),

    SYS_TRAIL_PRIOR_OCT2018 varchar2(1),

    DTER number(8,4),

    DTER_PERC_OF_SHARING number(5,2),

    NORMAL_RUN varchar2(1),

    SIP_PRODUCT_TYPE varchar2(10),

    FEE_START_DATE date,

    FEE_END_DATE date,

    BROKER_DRIVE_ID number(10),

    TF_TYPE varchar2(5),

    ADJ_ADVANCE_UPFRONT varchar2(10),

    HIERARCHY varchar2(25),

    HIERARCHY_CODE varchar2(20),

    FIRST_INVESTMENT char(1),

    PAYOUT_MECHANISM varchar2(7),

    PAYABLE_SIP_MONTHS number(10));

    · Share on TwitterShare on Facebook
  • User_ROTJD
    User_ROTJD Member Posts: 5 Green Ribbon

    insert statement have 324 rows. unable to post it here. reached maximum bytes

    DUMMY_INSERT.txt


    · Share on TwitterShare on Facebook
  • User_ROTJD
    User_ROTJD Member Posts: 5 Green Ribbon

    here is some sample data. from and to age is like wise

    from_age to_age

    0 1

    2 6

    7 12

    13 18

    19 24

    25 36

    37 48

    49 60

    61 9999

    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'B', 'B', 'S', 0.01000000, 1000000.00000000, 0, 1, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394941509, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'T', 'B', 'S', 1000000.01000000, 999999999999.98989000, 19, 24, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394932404, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'B', 'B', 'S', 1000000.01000000, 999999999999.98989000, 25, 36, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394940167, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'B', 'B', 'S', 1000000.01000000, 999999999999.98989000, 37, 48, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394941486, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'T', 'B', 'S', 1000000.01000000, 999999999999.98989000, 19, 24, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394940183, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'T', 'B', 'S', 0.01000000, 1000000.00000000, 13, 18, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394933426, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'B', 'B', 'S', 0.01000000, 1000000.00000000, 49, 60, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394940851, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'B', 'B', 'S', 1000000.01000000, 999999999999.98989000, 25, 36, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394932882, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'T', 'B', 'S', 0.01000000, 1000000.00000000, 19, 24, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 395017076, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'B', 'B', 'S', 0.01000000, 1000000.00000000, 19, 24, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394933410, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'T', 'B', 'S', 0.01000000, 1000000.00000000, 49, 60, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394933932, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);


    insert into temp_final_consolidated_1 (BROKERAGE_TYPE, REPORT_DESCRIPTION, SETUP_CREATED_ON, SETUP_CLOSED_ON, FOLIO_NO, PURCH_ID, CONS_CODE, BROK_DLR_CODE, BROK_DLR_CATG, ASSET_CLASS, SUB_FUND_CODE, SCHEME_CODE, SOURCE_ASSET_CLASS, SOURCE_SUB_FUND_CODE, SOURCE_SCHEME_CODE, LOAD_BASIS, TRADE_DATE_FROM, TRADE_DATE_TO, SYS_REGN_FROM_DATE, SYS_REGN_TO_DATE, DATE_FLAG_FOR_RATE_SELECTION, FEE_AGING, TER_LOCATION, TRANSACTION_HEAD, SUB_TYPE, FROM_AMOUNT, TO_AMOUNT, FROM_AGE, TO_AGE, AM_RATE, TF_VALUATION_BASIS, TRXN_LEVEL_FEE, DYNAMIC_RATES, DEFER_FREQUENCY, NO_OF_INSTALMENTS_DEFERRED, TRANSACTION_AGING, BROKERAGE_RATE, STRUCTURE_REF_NO, CREATED_AUTHORIZED_BY, UPFRONT_AMORT_TENURE, REMARKS, INCLUDE_DR, INCLUDE_DTP, SOURCE_PROGRAM, WEIGHTAGE, STRUCTURE_SEQ_NO, OVERRIDING_SETUP, INSTALMENT_NO_FROM, INSTALMENT_NO_TO, LINKED_TO_EXIT_LOAD, SIP_PERIODICITY, SIP_FROM_TENURE, SIP_TO_TENURE, RETRO_ADJUSTMENT_REQUIRED, RETRO_ADJ_PROCESS_RUN_DATE, VALID_FLAG, VALID_FLAG_UPDATED_DATE, PRODUCT_CODE, INSERTED_ON, COMPUTING_FREQUENCY, OLD_TF_RATE, RATE_REVISION_EFFECTIVE_DATE, CLAWBACK_APPLICABLE, PAYMENT_TYPE, FORMULAE_TYPE, OVERLAP_TYPE, SYS_TRAIL_PRIOR_OCT2018, DTER, DTER_PERC_OF_SHARING, NORMAL_RUN, SIP_PRODUCT_TYPE, FEE_START_DATE, FEE_END_DATE, BROKER_DRIVE_ID, TF_TYPE, ADJ_ADVANCE_UPFRONT, HIERARCHY, HIERARCHY_CODE, FIRST_INVESTMENT, PAYOUT_MECHANISM, PAYABLE_SIP_MONTHS)

    values ('TF', 'DUMMY', to_date('01-01-2020', 'dd-mm-yyyy'), to_date('31-12-2020', 'dd-mm-yyyy'), null, null, null, 'XYZ-12345', null, null, 'XXXX', null, null, null, null, 'G', to_date('01-05-0219', 'dd-mm-yyyy'), to_date('31-07-2020', 'dd-mm-yyyy'), null, null, 'BT', 'C', 'B', 'B', 'S', 0.01000000, 1000000.00000000, 25, 36, 0.0000, 'F', null, null, 'M', null, 'C', 0.2500, 'TT-222-12-XXX', 'USER', null, 'DUMMY', 'Y', 'Y', null, 1210005, 394940883, 'N', null, null, null, null, null, null, null, null, 'Y', null, null, to_date('26-05-2022 17:31:23', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'N', 'PF', 'I', null, null, 0.0000, null, 'N', null, null, null, null, 'NTF', null, 'DUMMY', 'B/S/RT', null, null, null);

    · Share on TwitterShare on Facebook