Oracle Database 10g
RHEL 6
Hi Team,
there is a script which is taking time to run.
I see records for July 18 has not been populated.
SQL> select INVOICE_DATE from dun_details where INVOICE_DATE like '%JUL-18%';
The current status is shown below:
With the Partitioning, Data Mining and Real Application Testing options
Table dropped.
Table created.
Commit complete.
329667 rows created.
Commit complete.
329667 rows updated.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jul 24 15:31:53 2018
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
70562 rows created.
Commit complete.
354228 rows updated.
Commit complete.
oracle_linux>cat /exec/products/oracle/ccontrol/run_debtors.sh
#! /usr/bin/ksh
sh /exec/products/oracle/ccontrol/create.sh (tables already created)
sh /exec/products/oracle/ccontrol/insert_detail.sh
insert into dun_details
(INVOICE_DATE,
trans_num,
account_code,
cust_id,
name,
invoice_amt,
inv_tax_amt,
inv_amt_clr,
adjustment_amt,
amt_due,
activation_date,
deactivation_date,
subs_category,
subs_sub_category,
status)
select b.trans_date_d,b.TRANS_NUM_V,a.ACCOUNT_CODE_N,a.SUBSCRIBER_CODE_N,
a.ACCOUNT_NAME_V,b.INVOICE_AMT_N/100,b.TAX_AMT_N/100,b.INV_AMT_CLEARED_N/100,
b.ADJUSTMENT_AMT_N/100,
((b.INVOICE_AMT_N + b.TAX_AMT_N) - (b.INV_AMT_CLEARED_N + b.ADJUSTMENT_AMT_N))/100 Amt_Due,
a.ACTIVATION_DATE_D,a.ERASED_DATE_D,a.SUBSCRIBER_CATEGORY_V,
a.SUBSCRIBER_SUB_CATEGORY_V,a.STATUS_CODE_V
from CB_ACCOUNT_MASTER_CCBS a , cb_sub_invoice_ccbs b
where a.account_code_n = b.account_code_n
--and trunc(b.trans_date_d) <= '31-MAY-17'
and ((b.INVOICE_AMT_N + b.TAX_AMT_N) - (b.INV_AMT_CLEARED_N + b.ADJUSTMENT_AMT_N))> 0
and a.subscriber_code_n not in
('48879','49492','52771','57588','58711','58891','58926','58937',
'58942','58945','58968','59038','59053','59063','59066','59097',
'59110','59201','59285','59291','59337','59415','59427','59430',
'59489','674036','710364','725955','727580','735853','741387',
'744112','782445','785867','804907','873153','873169','873193',
'873214','873369','873549','873550','873558','873572','873602',
'873606','873843','873846','873879','874180','874184','874187',
'874313','874364','874848','874850','874856','892847','964258',
'964286','964287','964288','964293','964295','964517','969129',
'1046554','1078530','1120868','1123690','1125338','1151575','1154441',
'1168606','1214623','48900','78596','967162','1144702','14173745','14076804',
'14179743','14179778','14204730','14204756','14204764','14204772','14204781',
'14204799','14216479','14216487','14216495','14216509','14216614','14216622',
'14216631','14216649','14216665','14216673','14216711','14216720','14216746',
'14216754','14216762','14216771','14216967','14216983','14216991','14217009',
'14218196','14218226','14218234','14218285','14224668','14224676','14224714',
'1032025','1181086','1305914','916160','916162','917222','921026','1196541',
'1196545','1139839','1196554','1032044','1031986','1032052','1196542','1196624',
'1196623','1196629','1196548','1196610','1196618','1196621','1196540','1196632',
'1196550','1196625','1196616','1196535','1226682','1196613','967392','14151512',
'14151521','14152438','14152527','1125266','842359','918590','918610','919182',
'919961','14136424','14158428','14158436','14157456','14173729','14173737',
'14173753','14187070','14187088','14187096','14187100','14187118','14187126',
'14187134','14187142','14187151','14205884','14205892','14205906','14205914',
'14205931','14205949','14205957','14205965','14205973','14205981','14207151',
'14207160','14207186','14207208','14207216','14207224','14207232','14207241',
'14207259','14207275','14152225','14151482','14156182','14156689','14151539',
'14151571','14151466','14151423','858924','14195161','14215049');
--and substr(b.TRANS_NUM_V,1,4) = 'FINV' ;
commit;
update dun_details
set due_date = (select max(due_date_d) from cb_invoice_ccbs)
where due_date is null;
commit;
exit;
sh /exec/products/oracle/ccontrol/up_sum_dun1.sh
I think this script is currently being executed
oracle_linux>cat /exec/products/oracle/ccontrol/up_sum_dun1.sql
insert into cbs_sum_dun2
(account_code_n,
tot_inv_amt,
tot_tax_amt,
TOT_AMT_CLR,
tot_bills_due
)
select account_code,sum(invoice_amt),sum(inv_tax_amt),sum(inv_amt_clr),sum(amt_due)
from dun_details
--where account_code = '879751'
group by account_code;
commit;
update cbs_sum_dun2 s
set s.cnt =
(select
count(*)
from dun_details c
where s.account_code_n = c.account_code
group by c.account_code)
where s.LAST_BILL_DATE is null;
commit;
update cbs_sum_dun2 s
set s.over_30 =
(select sum(c.amt_due) from dun_details c
where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
--and trunc(c.INVOICE_DATE) <= '30-JUN-17'
and substr(trans_num,1,4) = 'FINV'
and (sysdate - trunc(c.INVOICE_DATE)) > 30 and (sysdate - trunc(c.INVOICE_DATE)) <= 60)
where s.LAST_BILL_DATE is null
/
commit;
update cbs_sum_dun2 s
set s.over_60 =
(select sum(c.amt_due) from dun_details c
where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
--and ( (trunc(c.INVOICE_DATE) <= '01-JUN-17')
and substr(trans_num,1,4) = 'FINV'
and (sysdate - trunc(c.INVOICE_DATE)) > 60 and (sysdate - trunc(c.INVOICE_DATE)) <= 90)
where s.LAST_BILL_DATE is null
/
commit;
update cbs_sum_dun2 s
set s.over_90 =
(select sum(c.amt_due) from dun_details c
where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
and substr(trans_num,1,4) = 'FINV'
and (sysdate - trunc(c.INVOICE_DATE)) > 90 and (sysdate - trunc(c.INVOICE_DATE)) <= 120)
where s.LAST_BILL_DATE is null
/
commit;
update cbs_sum_dun2 s
set s.over_120 =
(select sum(c.amt_due) from dun_details c
where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
and substr(trans_num,1,4) = 'FINV'
and (sysdate - trunc(c.INVOICE_DATE)) > 120 and (sysdate - trunc(c.INVOICE_DATE)) <= 180)
where s.LAST_BILL_DATE is null
/
commit;
update cbs_sum_dun2 s
set s.over_180 =
(select sum(c.amt_due) from dun_details c where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
and substr(trans_num,1,4) = 'FINV'
and (sysdate - trunc(c.INVOICE_DATE)) > 180 and (sysdate - trunc(c.INVOICE_DATE)) <= 365)
where s.LAST_BILL_DATE is null
/
commit;
update cbs_sum_dun2 s
set s.over_365 =
(select sum(c.amt_due) from dun_details c
where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
and substr(trans_num,1,4) = 'FINV'
and sysdate - trunc(c.INVOICE_DATE) > 365)
where s.LAST_BILL_DATE is null
/
commit;
update cbs_sum_dun2 s
set s.LAST_BILL_AMT =
(select sum((c.INVOICE_AMT + c.INV_TAX_AMT) - ADJUSTMENT_AMT) from dun_details c
where s.account_code_n = c.account_code
and substr(c.invoice_date,4,6) = substr(sysdate - 30,4,6))
where s.LAST_BILL_DATE is null;
commit;
update cbs_sum_dun2 cd
set cd.tot_surc =
(
select sum(trans_amt_n)/100 from cb_invoice_details_ccbs b , cb_account_master_ccbs c
where b.account_link_code_n = c.account_link_code_n
and b.article_code_v = 'T_LPF00001'
and b.bill_cycle_full_code_n = '1010000012017070'
and c.account_code_n = cd.account_code_n)
where cd.LAST_BILL_DATE is null;
commit;
col sys_date1 new_value new_date;
select (sysdate) sys_date1 from dual;
update cbs_sum_dun2
set LAST_BILL_DATE = '&new_date'
where LAST_BILL_DATE is null;
commit;
exit;
Please advise how do I check the status of the script and why it is taking time.
Regards,
Joe