Hi,
The following is a Pl/SQL script which I need to tune. Also, I am told that in the 'for' loop, the 'select count(*).....' statement is not required. How do I tune it? Please let me know.

My job is at stake.

Thanks.




/* 06/19/00 SR Initial Coding */
/* This script regenerates the ACCT_STATIC_DETAILS table */
/* 08/27/00 SR Changed where condition accessing party_relationship. */
/* Gary recommended that the CUSTBASENO records MUST have a */
/* party relationship entry if lec.pty_id is not null */
/* 08/28/00 SR Teamtrack - DBA0236472 to modify logic to obtain bank name */
/* This one uses party(p3) and bank identifier(bi) */
/* 10/22/00 SR Teamtrack - DBA0240259 modified logic for bank name */
/* 11/01/00 SR M Elgart's Request to add the account.fund_bank_nam column */
/* before searching in party (p3) table for bank name */

declare
start_time INTEGER;
end_time INTEGER;
total_time INTEGER;
chk_pk INTEGER;
current_dt DATE;
dups_at_start INTEGER;
dups_at_end INTEGER;
cursor c1 is
SELECT /*+ FIRST_ROWS ORDERED index(AP2 ACCOUNT_ACCOUNT_PARTY_FK)
index(ap3 ACCOUNT_ACCOUNT_PARTY_FK) */
a1.acct_id as acct_id,
a1.acct_sta_typ as acct_sta_typ,
a1.pty_id as pty_id,
a1.pty_sta_typ as pty_sta_typ,
a1.acct_no as acct_no,

-- Teamtrack - DBA0236472
-- Commented out code below for bank name as requested in Teamtrack DBA0236472
-- decode(a1.fund_bank_nam,null,(decode(a1.br_cod,'152',null,'OBBR',null,'CITIBANK')),
-- a1.fund_bank_nam) as bnk_nm,

-- Teamtrack - DBA0240259
-- nvl(p3.pty_nam,decode(a1.bank_rte_id,null,'CITIBANK',' ')) as bnk_nm,

-- decode(a1.br_cod,
-- 'OBBR',decode(bi.bank_cod,'FW',p3.pty_nam,' '),
-- '152' ,decode(bi.bank_cod,'IS',p3.pty_nam,' '),
-- nvl(p3.pty_nam,decode(a1.bank_rte_id,null,'CITIBANK',' '))) as bnk_nm,

nvl(a1.fund_bank_nam,nvl(p3.pty_nam,decode(a1.bank_rte_id,null,'CITIBANK',' '))) as bnk_nm,

nvl(ap1.acct_fam_nam,ap2.acct_fam_nam) as acct_nm,
a1.br_cod as br_cod,
p1.pty_nam as brn_nm,
p2.pty_id as clnt_id,
p2.pty_nam as clnt_ent_nm,
ap1.acct_fam_nam as ap1_acct_fam_nam,
ap2.acct_fam_nam as ap2_acct_fam_nam,
nvl(leg.lgl_nam,lec.lgl_nam) as lgl_nam,
nvl(leg.cust_ref_id,lec.cust_ref_id) as cust_ref_id,
lec.cust_ref_id as cust_bas_no,
leg.cust_ref_id as gfcid,
b1.br_tax_id as br_tax_id,
a1.iso_ccy_cod as acct_ccy,
a1.entprise_des as entprise_des,
a1.lst_stmt_dt as last_stmt_dt,
a1.acct_typ as acct_typ,
a1.dr_acct_ind as dr_ind_prn,
p1.pty_id as brn_id,
leg.pty_id as gfcid_id,
lec.pty_id as cust_bas_no_id,
ap1.pty_id as ap_pty_id,
ap1.acct_id as ap_acct_id,
ap1.pty_sta_typ as ap_pty_sta_typ,
ap1.pty_role_typ as ap_pty_role_typ,
ap1.acct_sta_typ as ap_acct_sta_typ,
ap1.acct_pty_sta_typ as ap_acct_pty_sta_typ
from account_party ap1,
account a1,
account_party ap2,
account_party ap3,
branch b1,
party p2,
party p1,
legal_entity leg,
legal_entity lec,
bank_identifier bi,
party p3
where
a1.acct_sta_typ = 'PROCESSED' and
a1.pty_id = b1.pty_id and
a1.pty_sta_typ = b1.pty_sta_typ and
a1.br_cod = b1.br_cod and
b1.pty_id = p1.pty_id and
b1.pty_sta_typ = p1.pty_sta_typ and
p1.pty_sta_typ = 'PROCESSED' and
p1.pty_typ = 'BRCH' and
a1.acct_id = ap1.acct_id and
a1.acct_sta_typ = ap1.acct_sta_typ and
ap1.acct_pty_sta_typ = 'PROCESSED' and
ap1.pty_sta_typ = 'PROCESSED' and
ap1.acct_role_typ = 'ACCESS' and
ap1.pty_role_typ = 'CLNT' and
ap1.pty_id = p2.pty_id and
ap1.pty_sta_typ = p2.pty_sta_typ and
a1.acct_id = ap2.acct_id(+) and
a1.acct_sta_typ = ap2.acct_sta_typ(+) and
ap2.acct_role_typ(+) = 'OWNER' and
ap2.pty_role_typ(+) = 'LGLBU' and
ap2.pty_sta_typ(+) = 'PROCESSED' and
ap2.pty_id = leg.pty_id(+) and
ap2.pty_sta_typ = leg.pty_sta_typ(+) and
leg.cust_ref_typ(+) = 'GFCID' and
a1.acct_id = ap3.acct_id(+) and
a1.acct_sta_typ = ap3.acct_sta_typ(+) and
ap3.acct_role_typ(+) = 'GROUP' and
ap3.pty_role_typ(+) = 'LGLBU' and
ap3.pty_sta_typ(+) = 'PROCESSED' and
ap3.pty_id = lec.pty_id(+) and
ap3.pty_sta_typ = lec.pty_sta_typ(+) and
lec.cust_ref_typ(+) = 'CUSTBASENO' and
a1.bank_rte_id = bi.bank_rte_id(+) and
bi.pty_id = p3.pty_id(+) and
((bi.bank_cod = decode(a1.br_cod,'OBBR','FW','152','IS',bi.bank_cod)) or
(bi.bank_cod is null)) and
(lec.pty_id is null or
exists ( select 1 from party_relationship pr
where lec.pty_id = pr.pty_id and
pr.rel_pty_id = b1.pty_id));


Begin
start_time := DBMS_UTILITY.GET_TIME;
dbms_output.put_line('Creating acct_static_details For ' ||to_char(sysdate,'DD-MON-YYYY
HH24:MI:SS')||'.');

select sysdate into current_dt from dual;

select count(*) into dups_at_start from acct_static_details_exceptions;

delete from acct_static_details;

for c1rec in c1 loop
SELECT count(*) into chk_pk
FROM acct_static_details asd
WHERE c1rec.clnt_id = asd.clnt_id
AND c1rec.acct_id = asd.acct_id
AND c1rec.pty_id = asd.pty_id;

if chk_pk = 0 then
INSERT INTO acct_static_details
VALUES
(c1rec.ACCT_ID
,c1rec.ACCT_STA_TYP
,c1rec.PTY_ID
,c1rec.PTY_STA_TYP
,c1rec.ACCT_NO
,c1rec.BNK_NM
,c1rec.ACCT_NM
,c1rec.BR_COD
,c1rec.BRN_NM
,c1rec.CLNT_ID
,c1rec.CLNT_ENT_NM
,c1rec.AP1_ACCT_FAM_NAM
,c1rec.AP2_ACCT_FAM_NAM
,c1rec.LGL_NAM
,c1rec.CUST_REF_ID
,c1rec.CUST_BAS_NO
,c1rec.GFCID
,c1rec.BR_TAX_ID
,c1rec.ACCT_CCY
,c1rec.ENTPRISE_DES
,c1rec.LAST_STMT_DT
,c1rec.ACCT_TYP
,c1rec.DR_IND_PRN
,c1rec.BRN_ID
,c1rec.GFCID_ID
,c1rec.CUST_BAS_NO_ID
,c1rec.AP_PTY_ID
,c1rec.AP_ACCT_ID
,c1rec.AP_PTY_STA_TYP
,c1rec.AP_PTY_ROLE_TYP
,c1rec.AP_ACCT_STA_TYP
,c1rec.AP_ACCT_PTY_STA_TYP ) ;
else
INSERT INTO acct_static_details_exceptions
VALUES
(c1rec.ACCT_ID
,c1rec.ACCT_STA_TYP
,c1rec.PTY_ID
,c1rec.PTY_STA_TYP
,c1rec.ACCT_NO
,c1rec.BNK_NM
,c1rec.ACCT_NM
,c1rec.BR_COD
,c1rec.BRN_NM
,c1rec.CLNT_ID
,c1rec.CLNT_ENT_NM
,c1rec.AP1_ACCT_FAM_NAM
,c1rec.AP2_ACCT_FAM_NAM
,c1rec.LGL_NAM
,c1rec.CUST_REF_ID
,c1rec.CUST_BAS_NO
,c1rec.GFCID
,c1rec.BR_TAX_ID
,c1rec.ACCT_CCY
,c1rec.ENTPRISE_DES
,c1rec.LAST_STMT_DT
,c1rec.ACCT_TYP
,c1rec.DR_IND_PRN
,c1rec.BRN_ID
,c1rec.GFCID_ID
,c1rec.CUST_BAS_NO_ID
,c1rec.AP_PTY_ID
,c1rec.AP_ACCT_ID
,c1rec.AP_PTY_STA_TYP
,c1rec.AP_PTY_ROLE_TYP
,c1rec.AP_ACCT_STA_TYP
,c1rec.AP_ACCT_PTY_STA_TYP
,current_dt) ;
end if;

end loop;
commit;

select count(*) into dups_at_end from acct_static_details_exceptions;

if dups_at_end > dups_at_start then
dbms_output.put_line('Duplicate Rows Found.'||user);
end if;

end_time := DBMS_UTILITY.GET_TIME;

total_time := (end_time - start_time)/100;

dbms_output.put_line('Acct_static_details created successfully at ' ||to_char(sysdate,'DD-MON-YYYY
HH24:MI:SS')||'.');
dbms_output.put_line ('Script took '|| total_time ||' seconds');
Exception
When NO_data_found then
dbms_output.put_line('No Rows Found.'||user);
rollback;
When dup_val_on_index then
dbms_output.put_line('Duplicate Rows Found.'||user);
rollback;
When Others then
dbms_output.put_line('Error : '||user||sqlerrm);
rollback;

End;
/