-- Create table create table LOT_DRAWDOWNBREAKUP_DTL ( ID NUMBER(8) not null, MAKERID VARCHAR2(32) not null, STATUS VARCHAR2(4) not null, MAKERDATE DATE not null, BENEFICIARYID NUMBER(8), DRAWDOWNSCHEDULEDTLID NUMBER(8), CG_DISB_CURRENCY NUMBER(8), CG_CURRENCY NUMBER(8), DRAWDOWN NUMBER(8), COMPAPPLID NUMBER(8), COMPANYID NUMBER(8), CG_TYPE_OF_PAYMENT NUMBER(8), LOANAMOUNT NUMBER(16,2), DISB_AMT NUMBER(16,2), RLSACCNO VARCHAR2(80), FACILITYTYPECODE VARCHAR2(32), LOANREFNO VARCHAR2(200) ) -- Create/Recreate primary, unique and foreign key constraints alter table LOT_DRAWDOWNBREAKUP_DTL add constraint PK_LDD_ID primary key (ID) using index ; alter table LOT_DRAWDOWNBREAKUP_DTL add constraint FK_DRBRDT_COMPANYID foreign key (COMPANYID) references COMPANY (COMPANYID); alter table LOT_DRAWDOWNBREAKUP_DTL add constraint FK_DRBRDT_CURRENCY foreign key (CG_CURRENCY) references COMPANY_GENERIC (GENERICID); alter table LOT_DRAWDOWNBREAKUP_DTL add constraint FK_DRBRDT_DISB_CURRENCY foreign key (CG_DISB_CURRENCY) references COMPANY_GENERIC (GENERICID); alter table LOT_DRAWDOWNBREAKUP_DTL add constraint FK_DRBRDT_TYPE_OF_PAYMENT foreign key (CG_TYPE_OF_PAYMENT) references COMPANY_GENERIC (GENERICID); -- Create/Recreate indexes create index IDX_DRBRDT_001 on LOT_DRAWDOWNBREAKUP_DTL (CG_DISB_CURRENCY); -- Create table create table LOT_DRAWDOWNSCHEDULE_DTL ( ID NUMBER(10) not null, HDRID NUMBER(8), COMPAPPLID NUMBER(8), DRAWDOWN NUMBER(8), CG_TYPE_OF_PAYMENT NUMBER(20), PROPOSEDDATE DATE, BENEFICIARYID NUMBER, DRAWDOWNAMT NUMBER(16,2), PAYMENTSTATUS VARCHAR2(1 CHAR), PAYMENTAMT NUMBER(16,2), PAYMENTDATE DATE, PAYMENTTOBENEFICIARYID NUMBER, STATUS VARCHAR2(1 CHAR), COMPANYID NUMBER(8), MAKERID VARCHAR2(8 CHAR), MAKERDATE DATE, CG_EVENT NUMBER(8), PAYMENTBY VARCHAR2(1 CHAR), PAYMENTSCHEDULEID NUMBER(8), DISBREFNO VARCHAR2(16 CHAR), CG_CURRENCY NUMBER(8), APPLID NUMBER(8), CAPITALIZEYN VARCHAR2(1), ADJUSTEDTOID NUMBER(8), UNUSED_AMT NUMBER(16,2), CG_UNUSED_AMT_CURRENCY NUMBER(8), APPR_FLAG VARCHAR2(4), LOAN_NO VARCHAR2(200), TENURE NUMBER(3) ) alter table LOT_DRAWDOWNSCHEDULE_DTL add constraint FK_DRSCDT_COMPANYID foreign key (COMPANYID) references COMPANY (COMPANYID); alter table LOT_DRAWDOWNSCHEDULE_DTL add constraint FK_DRSCDT_CURRENCY foreign key (CG_CURRENCY) references COMPANY_GENERIC (GENERICID); alter table LOT_DRAWDOWNSCHEDULE_DTL add constraint FK_DRSCDT_EVENT foreign key (CG_EVENT) references COMPANY_GENERIC (GENERICID); alter table LOT_DRAWDOWNSCHEDULE_DTL add constraint FK_DRSCDT_TYPE_OF_PAYMENT foreign key (CG_TYPE_OF_PAYMENT) references COMPANY_GENERIC (GENERICID); alter table LOT_DRAWDOWNSCHEDULE_DTL add constraint FK_DRSCDT_UNUSED_AMT_CURRENCY foreign key (CG_UNUSED_AMT_CURRENCY) references COMPANY_GENERIC (GENERICID); -- Create/Recreate indexes create index CIDX_LDD_BP on LOT_DRAWDOWNSCHEDULE_DTL (BENEFICIARYID, PAYMENTSTATUS, PAYMENTAMT); create index IDX_DRSCDT_001 on LOT_DRAWDOWNSCHEDULE_DTL (CG_TYPE_OF_PAYMENT); create index IDX_DRSCDT_002 on LOT_DRAWDOWNSCHEDULE_DTL (COMPANYID); create index IDX_DRSCDT_003 on LOT_DRAWDOWNSCHEDULE_DTL (CG_EVENT); create index IDX_DRSCDT_004 on LOT_DRAWDOWNSCHEDULE_DTL (CG_CURRENCY) create index IDX_DRSCDT_005 on LOT_DRAWDOWNSCHEDULE_DTL (CG_UNUSED_AMT_CURRENCY); create index INDX_LDD_COMPID on LOT_DRAWDOWNSCHEDULE_DTL (COMPAPPLID); ----- Data---- Table Dat in lot_drawdownbreakup_dtl ID MAKERID STATUS MAKERDATE BENEFICIARYID DRAWDOWNSCHEDULEDTLID CG_DISB_CURRENCY CG_CURRENCY DRAWDOWN COMPAPPLID COMPANYID CG_TYPE_OF_PAYMENT LOANAMOUNT DISB_AMT RLSACCNO FACILITYTYPECODE LOANREFNO 1 72 DDOWNM A 8/21/2009 31 33 242 242 36 5000 710 14000.00 14000.00 2 71 DDOWNM A 8/21/2009 31 33 242 242 36 5000 710 17000.00 17000.00 Table Data IN lot_drawdownschedule_dtl ID HDRID COMPAPPLID DRAWDOWN CG_TYPE_OF_PAYMENT PROPOSEDDATE BENEFICIARYID DRAWDOWNAMT PAYMENTSTATUS PAYMENTAMT PAYMENTDATE PAYMENTTOBENEFICIARYID STATUS COMPANYID MAKERID MAKERDATE CG_EVENT PAYMENTBY PAYMENTSCHEDULEID DISBREFNO CG_CURRENCY APPLID CAPITALIZEYN ADJUSTEDTOID UNUSED_AMT CG_UNUSED_AMT_CURRENCY APPR_FLAG LOAN_NO TENURE 1 33 31 36 710 9/16/2009 31 P 30000.00 A 5000 DDOWNM 8/21/2009 242 34 -1000.00 242 2 34 31 36 710 12/23/2009 31 P 24000.00 A 5000 DDOWNM 8/21/2009 242 34 0.00 242 It might be possiblity that it can contains more than onr rows in lot_drawdownbreakup_dtl for particular comp_appl_id. I have to chk -- 1.Disb_Amt should not exceed with paymentamt from lot_drawdownschedule_dtl for that particular comp_appl_id. 2.It returns only pass or fail resuls.