--Object Type create or replace type OPN_DLVRY_SCALARTYP as object(ACCRD_INTRST NUMBER(21,7), AGGREGATION_REQD NUMBER(9,0), AGGR_DLVRY_IND NUMBER(1,0), ALCTN_INSTNC NUMBER(9,0), BOOKING_TYPE NUMBER(2,0), CASH_ACNT_NUM NUMBER(9,0), CASH_ACNT_TYPE NUMBER(4,0), CASH_ACNT_TYPE_CD NUMBER(12,0), CHRGS_ACNT_NUM NUMBER(9,0), CHRGS_ACNT_TYPE NUMBER(4,0), CHRGS_ACNT_TYPE_CD NUMBER(12,0), CLAIM_REF NUMBER(9,0), CNTR_PRTY_ADRS_1 VARCHAR2(35 CHAR), CNTR_PRTY_ADRS_2 VARCHAR2(35 CHAR), CNTR_PRTY_ADRS_3 VARCHAR2(35 CHAR), CNTR_PRTY_ID NUMBER(9,0), CNTR_PRTY_NAME VARCHAR2(35 CHAR), CNTR_PRTY_PRTCPNT_ID VARCHAR2(35 CHAR), CORR_STLMNT_ADVC_ID NUMBER(9,0), CRNCY VARCHAR2(3 CHAR), CROSS_INDCTR NUMBER(1,0), CSTDN_ACNT_NUM NUMBER(8,0), CSTDN_ID NUMBER(9,0), CSTDN_ORDR_ID VARCHAR2(22 CHAR), CS_CSH_BMDL NUMBER(2,0), CS_CSH_BMDL_CD NUMBER(12,0), CS_CSH_BMTHD NUMBER(2,0), CS_CSH_BMTHD_CD NUMBER(12,0), CS_SEC_BMDL NUMBER(2,0), CS_SEC_BMDL_CD NUMBER(12,0), CS_SEC_BMTHD NUMBER(2,0), CS_SEC_BMTHD_CD NUMBER(12,0), DEAL_PRICE NUMBER(21,7), DISCOUNT NUMBER(21,7), DI_ID NUMBER(9,0), DM_BTID NUMBER(9,0), DM_BTNUMBER NUMBER(10,0), DM_LSTUPDDT DATE, DM_USERID NUMBER(9,0), DM_WSID NUMBER(4,0), DT_ID NUMBER(9,0), EXT_FLAG NUMBER(1,0), EXT_NAME VARCHAR2(35 CHAR), EXT_TXN_NO NUMBER(9,0), GEN_MSG_FLAG NUMBER(1,0), INCLUDE_FLAG NUMBER(1,0), INSTRMNT_ID NUMBER(8,0), LNKD_DLVRY_ID NUMBER(9,0), MARKET NUMBER(8,0), MARKET_CD NUMBER(12,0), MIR_FLAG NUMBER(1,0), MRKT VARCHAR2(2 CHAR), MSG_SEME VARCHAR2(16 CHAR), NET_STLMNT_FLAG NUMBER(2,0), NO_OF_ACCRL_DAYS NUMBER(6,0), OPEN_DELIVERY_VER NUMBER(4,0), OPEN_DLVRY_ID NUMBER(9,0), OPEN_DLVRY_STAT NUMBER(2,0), OPEN_DLVRY_STAT_CD NUMBER(12,0), OPEN_DLVRY_TYPE NUMBER(2,0), OPEN_DLVRY_TYPE_CD NUMBER(12,0), ORDR_DEAL_POS_ID NUMBER(9,0), ORDR_DEAL_POS_TYPE NUMBER(4,0), ORDR_DEAL_POS_TYPE_CD NUMBER(12,0), ORDR_TYPE NUMBER(2,0), OU_ID VARCHAR2(15 CHAR), PAYMENT_DATE DATE, PAYMENT_ID VARCHAR2(15 CHAR), POSK NUMBER(9,0), POS_TYPE NUMBER(2,0), POS_TYPE_CD NUMBER(12,0), PRE_MTCHNG_REASON NUMBER(3,0), PRE_MTCHNG_STAT NUMBER(2,0), PRE_MTCHNG_STAT_CD NUMBER(12,0), PRTL_STLMNT NUMBER(2,0), PRTL_STLMNT_CD NUMBER(12,0), RLTD_REF VARCHAR2(22 CHAR), RMRKS VARCHAR2(35 CHAR), SEC_AMT_QTY NUMBER(21,7), SE_EXEC_ID VARCHAR2(15 CHAR), SE_NET_STLMNT_ID NUMBER(9,0), SE_PRTCPNT_ID VARCHAR2(15 CHAR), SS_CSH_BMDL NUMBER(2,0), SS_CSH_BMDL_CD NUMBER(12,0), SS_CSH_BMTHD NUMBER(2,0), SS_CSH_BMTHD_CD NUMBER(12,0), SS_SEC_BMDL NUMBER(2,0), SS_SEC_BMDL_CD NUMBER(12,0), SS_SEC_BMTHD NUMBER(2,0), SS_SEC_BMTHD_CD NUMBER(12,0), STCK_EXCH_ID NUMBER(9,0), STLMNT_ADVC_DT DATE, STLMNT_ADVC_ID NUMBER(6,0), STLMNT_AMT NUMBER(21,7), STLMNT_CRNCY VARCHAR2(3 CHAR), STLMNT_DT DATE, STLMNT_MODEL NUMBER(2,0), STLMNT_MODEL_CD NUMBER(12,0), STLMNT_TYPE NUMBER(3,0), STLMNT_TYPE_CD NUMBER(12,0), SUB_POS_NUM NUMBER(9,0), SUB_POS_TYPE NUMBER(4,0), TAX_APPLICABLE NUMBER(1,0), TRD_DT DATE, TXN_DT DATE, TXN_DTL VARCHAR2(135 CHAR), UNIT NUMBER(1,0), UNIT_CD NUMBER(12,0), UNMATCHED_REASON VARCHAR2(35 CHAR), YIELD NUMBER(21,7)) / CREATE OR REPLACE TYPE OPN_DLVRY_ARRAYTYP AS TABLE OF OPN_DLVRY_SCALARTYP / ---------------------------------------------------------------------------------- --Anonymus Block DECLARE ERR_NUM NUMBER; ERR_MSG VARCHAR2(2000 CHAR); V_ROW_COUNT NUMBER; V_ROW_COUNT_T NUMBER DEFAULT 0; cursor c is SELECT OPN_DLVRY_SCALARTYP(ACCRD_INTRST, AGGR_DLVRY_IND, AGGREGATION_REQD, ALCTN_INSTNC, BOOKING_TYPE, CASH_ACNT_NUM, CASH_ACNT_TYPE, CASH_ACNT_TYPE_CD, CHRGS_ACNT_NUM, CHRGS_ACNT_TYPE, CHRGS_ACNT_TYPE_CD, CLAIM_REF, CNTR_PRTY_ADRS_1, CNTR_PRTY_ADRS_2, CNTR_PRTY_ADRS_3, CNTR_PRTY_ID, CNTR_PRTY_NAME, CNTR_PRTY_PRTCPNT_ID, CORR_STLMNT_ADVC_ID, CRNCY, CROSS_INDCTR, CS_CSH_BMDL, CS_CSH_BMDL_CD, CS_CSH_BMTHD, CS_CSH_BMTHD_CD, CS_SEC_BMDL, CS_SEC_BMDL_CD, CS_SEC_BMTHD, CS_SEC_BMTHD_CD, CSTDN_ACNT_NUM, CSTDN_ID, CSTDN_ORDR_ID, DEAL_PRICE, DI_ID, DISCOUNT, DM_BTID, DM_BTNUMBER, DM_LSTUPDDT, DM_USERID, DM_WSID, DT_ID, EXT_FLAG, EXT_NAME, EXT_TXN_NO, GEN_MSG_FLAG, INCLUDE_FLAG, INSTRMNT_ID, LNKD_DLVRY_ID, MARKET, MARKET_CD, MIR_FLAG, MRKT, MSG_SEME, NET_STLMNT_FLAG, NO_OF_ACCRL_DAYS, OPEN_DELIVERY_VER, OPEN_DLVRY_ID, OPEN_DLVRY_STAT, OPEN_DLVRY_STAT_CD, OPEN_DLVRY_TYPE, OPEN_DLVRY_TYPE_CD, ORDR_DEAL_POS_ID, ORDR_DEAL_POS_TYPE, ORDR_DEAL_POS_TYPE_CD, ORDR_TYPE, OU_ID, PAYMENT_DATE, PAYMENT_ID, POS_TYPE, POS_TYPE_CD, POSK, PRE_MTCHNG_REASON, PRE_MTCHNG_STAT, PRE_MTCHNG_STAT_CD, PRTL_STLMNT, PRTL_STLMNT_CD, RLTD_REF, RMRKS, SE_EXEC_ID, SE_NET_STLMNT_ID, SE_PRTCPNT_ID, SEC_AMT_QTY, SS_CSH_BMDL, SS_CSH_BMDL_CD, SS_CSH_BMTHD, SS_CSH_BMTHD_CD, SS_SEC_BMDL, SS_SEC_BMDL_CD, SS_SEC_BMTHD, SS_SEC_BMTHD_CD, STCK_EXCH_ID, STLMNT_ADVC_DT, STLMNT_ADVC_ID, STLMNT_AMT, STLMNT_CRNCY, STLMNT_DT, STLMNT_MODEL, STLMNT_MODEL_CD, STLMNT_TYPE, STLMNT_TYPE_CD, SUB_POS_NUM, SUB_POS_TYPE, TAX_APPLICABLE, TRD_DT, TXN_DT, TXN_DTL, UNIT, UNIT_CD, UNMATCHED_REASON, YIELD ) FROM (SELECT SRC1.ALCTN_INSTNC ACCRD_INTRST, NULL AGGR_DLVRY_IND, NULL AGGREGATION_REQD, NULL ALCTN_INSTNC, DECODE(BSRC1.DEAL_POS_TYPE,64,2) BOOKING_TYPE, SRC1.CASH_ACNT_NUM CASH_ACNT_NUM, MigD_ChangedSysCode(5290000,SRC1.CASH_ACNT_TYPE) CASH_ACNT_TYPE, 5290000 CASH_ACNT_TYPE_CD, SRC1.CHRGS_ACNT_NUM CHRGS_ACNT_NUM, MigD_ChangedSysCode(5290000,SRC1.CHRGS_ACNT_TYPE) CHRGS_ACNT_TYPE, 5290000 CHRGS_ACNT_TYPE_CD, NULL CLAIM_REF, SRC1.CNTR_PRTY_ADRS_1 CNTR_PRTY_ADRS_1, SRC1.CNTR_PRTY_ADRS_2 CNTR_PRTY_ADRS_2, SRC1.CNTR_PRTY_ADRS_3 CNTR_PRTY_ADRS_3, SRC1.CNTR_PRTY_ID CNTR_PRTY_ID, SRC1.CNTR_PRTY_NAME CNTR_PRTY_NAME, SRC1.CNTR_PRTY_PRTCPNT_ID CNTR_PRTY_PRTCPNT_ID, SRC1.CORR_STLMNT_ADVC_ID CORR_STLMNT_ADVC_ID, (SELECT DISTINCT BSRC3.DEAL_CRNCY FROM ST_DEAL BSRC3 WHERE BSRC3.DEAL_POS_TYPE = 64 AND BSRC3.DEAL_ID = BSRC1.DEAL_ID AND BSRC3.DEAL_POS_TYPE = BSRC1.DEAL_POS_TYPE) CRNCY, SRC1.CROSS_INDCTR CROSS_INDCTR, 1 CS_CSH_BMDL, 15690000 CS_CSH_BMDL_CD, 2 CS_CSH_BMTHD, 10180000 CS_CSH_BMTHD_CD, 1 CS_SEC_BMDL, 15690000 CS_SEC_BMDL_CD, 2 CS_SEC_BMTHD, 10180000 CS_SEC_BMTHD_CD, NULL CSTDN_ACNT_NUM, SRC1.CSTDN_ID CSTDN_ID, SRC1.CSTDN_ORDR_ID CSTDN_ORDR_ID, NULL DEAL_PRICE, NULL DI_ID, NULL DISCOUNT, 1 DM_BTID, 1 DM_BTNUMBER, SRC1.DM_LSTUPDDT DM_LSTUPDDT, 1 DM_USERID, 1 DM_WSID, NULL DT_ID, NULL EXT_FLAG, NULL EXT_NAME, NULL EXT_TXN_NO, 2 GEN_MSG_FLAG, SRC1.INCLUDE_FLAG INCLUDE_FLAG, SRC1.INSTRMNT_ID INSTRMNT_ID, NULL LNKD_DLVRY_ID, NULL MARKET, 13620000 MARKET_CD, NULL MIR_FLAG, NULL MRKT, NULL MSG_SEME, SRC1.NET_STLMNT_FLAG NET_STLMNT_FLAG, NULL NO_OF_ACCRL_DAYS, SRC1.OPEN_DELIVERY_VER OPEN_DELIVERY_VER, SRC1.OPEN_DLVRY_ID OPEN_DLVRY_ID, MigD_ChangedSysCode(2650000,SRC1.OPEN_DLVRY_STAT) OPEN_DLVRY_STAT, 2650000 OPEN_DLVRY_STAT_CD, MigD_ChangedSysCode(5700000,SRC1.OPEN_DLVRY_TYPE) OPEN_DLVRY_TYPE, 5700000 OPEN_DLVRY_TYPE_CD, SRC1.ORDR_DEAL_POS_ID ORDR_DEAL_POS_ID, MigD_ChangedSysCode(5290000,SRC1.ORDR_DEAL_POS_TYPE) ORDR_DEAL_POS_TYPE, NULL ORDR_DEAL_POS_TYPE_CD, SRC1.ORDR_TYPE ORDR_TYPE, DECODE(SRC1.OU_ID,'RSK','GSLAL','SLI','GSLIL',SRC1.OU_ID) OU_ID, NULL PAYMENT_DATE, NULL PAYMENT_ID, MigD_ChangedSysCode(5300000,SRC1.POS_TYPE) POS_TYPE, 5300000 POS_TYPE_CD, NULL POSK, NULL PRE_MTCHNG_REASON, MigD_ChangedSysCode(4110000,SRC1.PRE_MTCHNG_STAT) PRE_MTCHNG_STAT, 4110000 PRE_MTCHNG_STAT_CD, 2 PRTL_STLMNT, 7290000 PRTL_STLMNT_CD, SRC1.RLTD_REF RLTD_REF, SRC1.RMRKS RMRKS, SRC1.SE_EXEC_ID SE_EXEC_ID, SRC1.SE_NET_STLMNT_ID SE_NET_STLMNT_ID, SRC1.SE_PRTCPNT_ID SE_PRTCPNT_ID, SRC1.SEC_AMT_QTY SEC_AMT_QTY, 1 SS_CSH_BMDL, 15690000 SS_CSH_BMDL_CD, 2 SS_CSH_BMTHD, 10180000 SS_CSH_BMTHD_CD, 1 SS_SEC_BMDL, 15690000 SS_SEC_BMDL_CD, 2 SS_SEC_BMTHD, 10180000 SS_SEC_BMTHD_CD, SRC1.STCK_EXCH_ID STCK_EXCH_ID, SRC1.STLMNT_ADVC_DT STLMNT_ADVC_DT, SRC1.STLMNT_ADVC_ID STLMNT_ADVC_ID, SRC1.STLMNT_AMT STLMNT_AMT, SRC1.STLMNT_CRNCY STLMNT_CRNCY, SRC1.STLMNT_DT STLMNT_DT, 3 STLMNT_MODEL, 25150000 STLMNT_MODEL_CD, MigD_ChangedSysCode(6470000,SRC1.STLMNT_TYPE) STLMNT_TYPE, 6470000 STLMNT_TYPE_CD, SRC1.SUB_POS_NUM SUB_POS_NUM, SRC1.SUB_POS_TYPE SUB_POS_TYPE, NULL TAX_APPLICABLE, SRC1.TRD_DT TRD_DT, SRC1.TXN_DT TXN_DT, SRC1.TXN_DTL TXN_DTL, MigD_ChangedSysCode(1170000,SRC1.UNIT) UNIT, 1170000 UNIT_CD, NULL UNMATCHED_REASON, NULL YIELD FROM OPEN_DELIVERY@RSLAL SRC1, ST_DEAL BSRC1 WHERE SRC1.ORDR_DEAL_POS_ID = BSRC1.DEAL_ID AND SRC1.ORDR_DEAL_POS_TYPE = BSRC1.DEAL_POS_TYPE AND BSRC1.DEAL_POS_TYPE =64); L_DATA OPN_DLVRY_ARRAYTYP; begin open c; loop L_DATA := OPN_DLVRY_ARRAYTYP(); FETCH C BULK COLLECT INTO L_DATA LIMIT 50000; DBMS_OUTPUT.PUT_LINE( 'Fetched '||L_DATA.COUNT||' rows' ); insert into OPEN_DELIVERY ( ACCRD_INTRST, AGGR_DLVRY_IND, AGGREGATION_REQD, ALCTN_INSTNC, BOOKING_TYPE, CASH_ACNT_NUM, CASH_ACNT_TYPE, CASH_ACNT_TYPE_CD, CHRGS_ACNT_NUM, CHRGS_ACNT_TYPE, CHRGS_ACNT_TYPE_CD, CLAIM_REF, CNTR_PRTY_ADRS_1, CNTR_PRTY_ADRS_2, CNTR_PRTY_ADRS_3, CNTR_PRTY_ID, CNTR_PRTY_NAME, CNTR_PRTY_PRTCPNT_ID, CORR_STLMNT_ADVC_ID, CRNCY, CROSS_INDCTR, CS_CSH_BMDL, CS_CSH_BMDL_CD, CS_CSH_BMTHD, CS_CSH_BMTHD_CD, CS_SEC_BMDL, CS_SEC_BMDL_CD, CS_SEC_BMTHD, CS_SEC_BMTHD_CD, CSTDN_ACNT_NUM, CSTDN_ID, CSTDN_ORDR_ID, DEAL_PRICE, DI_ID, DISCOUNT, DM_BTID, DM_BTNUMBER, DM_LSTUPDDT, DM_USERID, DM_WSID, DT_ID, EXT_FLAG, EXT_NAME, EXT_TXN_NO, GEN_MSG_FLAG, INCLUDE_FLAG, INSTRMNT_ID, LNKD_DLVRY_ID, MARKET, MARKET_CD, MIR_FLAG, MRKT, MSG_SEME, NET_STLMNT_FLAG, NO_OF_ACCRL_DAYS, OPEN_DELIVERY_VER, OPEN_DLVRY_ID, OPEN_DLVRY_STAT, OPEN_DLVRY_STAT_CD, OPEN_DLVRY_TYPE, OPEN_DLVRY_TYPE_CD, ORDR_DEAL_POS_ID, ORDR_DEAL_POS_TYPE, ORDR_DEAL_POS_TYPE_CD, ORDR_TYPE, OU_ID, PAYMENT_DATE, PAYMENT_ID, POS_TYPE, POS_TYPE_CD, POSK, PRE_MTCHNG_REASON, PRE_MTCHNG_STAT, PRE_MTCHNG_STAT_CD, PRTL_STLMNT, PRTL_STLMNT_CD, RLTD_REF, RMRKS, SE_EXEC_ID, SE_NET_STLMNT_ID, SE_PRTCPNT_ID, SEC_AMT_QTY, SS_CSH_BMDL, SS_CSH_BMDL_CD, SS_CSH_BMTHD, SS_CSH_BMTHD_CD, SS_SEC_BMDL, SS_SEC_BMDL_CD, SS_SEC_BMTHD, SS_SEC_BMTHD_CD, STCK_EXCH_ID, STLMNT_ADVC_DT, STLMNT_ADVC_ID, STLMNT_AMT, STLMNT_CRNCY, STLMNT_DT, STLMNT_MODEL, STLMNT_MODEL_CD, STLMNT_TYPE, STLMNT_TYPE_CD, SUB_POS_NUM, SUB_POS_TYPE, TAX_APPLICABLE, TRD_DT, TXN_DT, TXN_DTL, UNIT, UNIT_CD, UNMATCHED_REASON, YIELD ) SELECT * FROM TABLE( CAST (L_DATA AS OPN_DLVRY_ARRAYTYP) ); V_ROW_COUNT := SQL%ROWCOUNT; V_ROW_COUNT_T := V_ROW_COUNT_T + V_ROW_COUNT; exit when c%notfound; end loop; CLOSE C; COMMIT; DBMS_OUTPUT.PUT_LINE( 'Total Number of rows Inserted: '||V_ROW_COUNT_T); EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 100); DBMS_OUTPUT.PUT_LINE('EXCEPTION ERR_MSG: '||ERR_MSG); ROLLBACK; END; /