Coding Tutorials-3 Oracle programming
--------- NL_CREATE_JOB_INSTANT
create or replace procedure estaple. NL_create_job_instance is
begin
declare
v_counter number;
v_total number :=0;
v_ssa_grp varchar2(10000);
--tot_count number;
job_count number :=0;
--v_date date:='01-feb-2012';
v_var varchar2(10000);
cursor c1 is
select ssa,count(*) as counts from NL_Es_Home_Sys_Account h
where h.is_active =1
and h.enrollment_date < add_months(last_day(to_date(SYSDATE))+1,-1)
group by ssa
order by count(*)desc;
begin
v_counter:=0;
for r1 in c1 loop
v_counter := v_counter + r1.counts;
v_ssa_grp := v_ssa_grp||'''' || r1.ssa ||''',';
if (v_counter>=10000) then
v_ssa_grp := substr(v_ssa_grp,1,length(v_ssa_grp)-1);
job_count:= job_count+1;
/*DBMS_SCHEDULER.CREATE_JOB(auto_drop => TRUE,
job_name => 'MY_JOB_FETCH' || job_count,
job_type => 'STORED_PROCEDURE',
job_action => \*'PKG_ES_BILL_INV_SUMMARY_4.sp_get_cdr_bill_inv_ssa'*\'sp_parent_recon',
start_date => '10-JUL-12 04:30:00 PM',
number_of_arguments => 2,--------update argument nos
enabled => FALSE,
end_date => '10-JUL-12 04:35:00 PM',
repeat_interval => 'FREQ=MONTHLY');
v_var:=v_ssa_grp;
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'MY_JOB_FETCH' || job_count,
argument_position => 1,
argument_value => '1-JUL-2012');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'MY_JOB_FETCH' || job_count,
argument_position => 2,
argument_value => v_var);
*/
v_total :=v_total+v_counter;
dbms_output.put_line(v_ssa_grp || v_counter||'job count : ' ||job_count );
v_counter:=0;
v_ssa_grp:= null;
end if;
end loop;
if (v_counter>=0) then
v_total :=v_total+v_counter;
v_ssa_grp := substr(v_ssa_grp,1,length(v_ssa_grp)-1);
job_count:= job_count+1;
dbms_output.put_line(v_ssa_grp || v_counter||'job count : ' ||job_count );
dbms_output.put_line('Total Count ='||v_total );
end if;
end;
end;
==========
NL_SCHEDULER_JOB2
create or replace procedure estaple. NL_scheduler_job2 is
begin
DBMS_SCHEDULER.CREATE_JOB(auto_drop => TRUE,
job_name => 'MY_JOBNT_R1' /*|| job_count*/,
job_type => 'STORED_PROCEDURE',
job_action => 'sp_parent_recon2',
start_date => '14-aug-12 6:00:00 AM',
number_of_arguments => 1,
enabled => FALSE,
end_date => '14-aug-12 12:30:00 PM',
repeat_interval => 'FREQ=MONTHLY');
/*v_var:=v_ssa_grp;*/
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'MY_JOBNT_R1'/* || job_count*/,
argument_position => 1,
argument_value => '1-AUG-2012');
/*DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'MY_JOB' || job_count,
argument_position => 2,
argument_value => v_var); */
dbms_scheduler.enable('MY_JOBNT_R1' /*|| job_count*/);
/*job_count:= job_count+1;
dbms_output.put_line(v_ssa_grp || v_counter );
v_counter:=0;
v_ssa_grp:= null;
end if;
end loop;*/
/*end;*/
end NL_scheduler_job2;
======= RAJ_CREATE_PROD
CREATE OR REPLACE PROCEDURE ESTAPLE.raj_create_prod(p_exe_date date) is
cursor xxx is
select * from B200203226.bulk_product_creation bpc where bpc.exe_date=p_exe_date;
/*select cdp.parent_id,cdp.account_id,cdp.net_diff as difference from crtprd cdp where
cdp.account_id=(select max(cdp1.account_id) from crtprd2 cdp1 where cdp1.parent_id=cdp.parent_id)
and cdp.parent_id not in ('1019458230',
'1007265403','1007248566','1001121417','1007271029','1007251938');*/
/*select *\*sum(bdff.difference)/1000 *\from NL_es_diff bdff
where bdff.parent_account_id in ('1017986414')
and bdff.logged_date > '21-jun-2012'
and bdff.logged_date=(select max(bdff1.logged_date)
from NL_es_diff bdff1 where bdff1.home_sys_account_id=bdff.home_sys_account_id);*/
begin
for rxxx in xxx
loop
begin
sp_create_product_accountwise(p_customer_ref => rxxx.account_id,
p_account_id =>rxxx.account_id ,
p_product_charges => -1*rxxx.product_charges,
p_product_id =>rxxx.product_id,
p_invoive_seq_no =>NULL,
p_start_date =>rxxx.start_date,
p_end_date =>rxxx.end_date);
exception
when others then
dbms_output.put_line(rxxx.account_id||sqlerrm);
end;
end loop;
end;
=============RAJ_FIND_DIFF
CREATE OR REPLACE PROCEDURE ESTAPLE.raj_find_diff as
cursor xxx is
select distinct bpr1.parent_id from NL_parent_recon bpr1 where bpr1.logged_date >= '14-aug-2012'
and bpr1.remarks not like 'OK'
;
begin
for rxxx in xxx
loop
begin
/* sp_create_product_accountwise(p_customer_ref => rxxx.account_id,
p_account_id =>rxxx.account_id ,
p_product_charges => (-1)*rxxx.difference,
p_product_id =>'188',
p_invoive_seq_no =>NULL,
p_start_date =>'1-apr-2012',
p_end_date =>'30-apr-2012');*/
sp_find_difference(rxxx.parent_id);
end;
end loop;
end;
===========SP.ANLYSE_ADJ_DISC
create or replace procedure estaple.sp_anlyse_adj_disc(p_bill_cycle_date in date )
as
v_account_id varchar2(20);
v_home_sys_account_id varchar2(20);
v_es_discount number;
v_adjustment number;
v_adj_at_parent number;
v_es_disc_at_parent number;
cursor cur_get_accounts is
SELECT h.account_id,h.home_sys_account_id ,es_discount,adjustment
FROM NL_es_hbs_payments h
WHERE h.logged_date > p_bill_cycle_date
and h.account_id in
(select account_id
from NL_es_home_sys_account bh where ssa in('PJM','PUN','AHD','VDA')) ;
begin
for rec_get_accounts in cur_get_accounts loop
v_account_id:=rec_get_accounts.account_id;
v_home_sys_account_id := rec_get_accounts.home_sys_account_id;
v_es_discount :=rec_get_accounts.es_discount;
v_adjustment :=rec_get_accounts.adjustment;
if (v_adjustment<>0) then
select nvl(sum(bp.charge),0)
into v_adj_at_parent
from NL_es_post_charges_log bp
where bp.child_account_id=v_account_id and product_id=187
and bp.logged_date>p_bill_cycle_date;
if abs(v_adj_at_parent+v_adjustment)>1000 then
dbms_output.put_line('child ' ||v_account_id ||' adjustment in irb '||v_adjustment ||' '|| ' charges of product'||' '|| v_adj_at_parent);
end if;
end if ;
if (v_es_discount<>0 ) then
select nvl(sum(bp.charge),0)
into v_es_disc_at_parent
from NL_es_post_charges_log bp
where bp.child_account_id=v_account_id and product_id=190
and bp.logged_date>p_bill_cycle_date;
if abs(v_es_discount+v_es_disc_at_parent) >1000 then
dbms_output.put_line('child ' ||v_account_id ||' es discount in irb '||v_es_discount ||' '|| ' charges of product'||' '|| v_es_disc_at_parent);
end if;
end if;
end loop;
end;
============= SP_BULK_CREATE
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_bulk_create IS
v_account_id NL_es_home_sys_account.account_id%TYPE;
v_amount NUMBER;
v_invoice_seq_no NL_es_cdr_bill_invoice.invoice_seq_no%TYPE;
v_enrollment_date DATE;
CURSOR cur_get_accounts IS
/*SELECT account_id, charge FROM billsummary_updates bh;*/
SELECT d.account_id,d.difference
FROM NL_es_diff d
WHERE d.parent_account_id = '1020844719' AND d.logged_date > '21-jun-2012'
and d.is_active=1 and d.difference>0;
BEGIN
FOR rec_get_accounts IN cur_get_accounts LOOP
v_account_id := rec_get_accounts.account_id;
v_amount := -1*rec_get_accounts.difference;
SELECT enrollment_date
INTO v_enrollment_date
FROM NL_es_home_sys_account b
WHERE b.account_id = v_account_id;
BEGIN
SELECT max(bc.invoice_seq_no)
INTO v_invoice_seq_no
FROM NL_es_cdr_bill_invoice bc
WHERE bc.account_id = v_account_id AND
bc.bill_cycle_date = '1-may-2012';
sp_create_product(v_account_id,
v_account_id,
v_amount,
188,
v_invoice_seq_no,
'1-may-2012',
'31-may-2012');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(v_account_id || SQLERRM);
END;
END LOOP;
END;
====
============= SP_CORRECT_ADJUSTMENT
create or replace procedure estaple.sp_correct_adjustment
as
cursor cur_get_accounts
is
select b.es_account_id,b.child_account_id,b.charge_desc,
b.charge+h.adjustment as diff,b.charge,h.adjustment,product_seq
from NL_es_post_charges_log b inner join NL_es_hbs_payments h
on h.account_id=b.child_account_id and h.invoice_seq_no=b.invoice_seq_no
and b.logged_date>'19-may-2012'
and b.product_id in(187)
and abs(b.charge+h.adjustment)>900;
v_parent_account_id NL_es_account.account_id%type;
v_account_id NL_es_account.account_id%type;
v_home_sys_account_id NL_es_account.account_id%type;
v_diff number;
v_charge number;
v_adjustment number;
v_count number :=0;
v_product_seq number;
begin
for rec_get_accounts in cur_get_accounts loop
v_parent_account_id:=rec_get_accounts.es_account_id;
v_account_id :=rec_get_accounts.child_account_id;
v_home_sys_account_id :=rec_get_accounts.charge_desc;
v_diff :=rec_get_accounts.diff;
v_charge :=rec_get_accounts.charge;
v_adjustment :=rec_get_accounts.adjustment;
v_product_seq :=rec_get_accounts.product_seq;
update NL_es_post_charges_log bp
set bp.charge=-v_adjustment
where bp.child_account_id=v_account_id
and bp.product_seq=v_product_seq;
v_count :=v_count+1;
INSERT INTO update_adj
VALUES
(v_parent_account_id,
v_account_id,
v_home_sys_account_id,
v_charge,
v_adjustment,
v_diff,
v_product_seq);
sp_correct_everything(v_account_id);
end loop;
dbms_output.put_line('updated' ||v_count||' rows for parent '||v_parent_account_id
);
end;
===============SP_CORRECT_CHARGES
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_correct_charges(p_product_id NUMBER) IS
CURSOR cur_get_accounts IS
SELECT customer_ref
FROM account@cdr_nz
WHERE account_num = '1010974985';
v_product_charges custoverrideprice.one_off_number@cdr_nz%TYPE;
v_customer_ref customer.customer_ref@cdr_nz%TYPE;
v_product_start_date custoverrideprice.start_dat@cdr_nz%TYPE;
v_product_end_date custoverrideprice.end_dat@cdr_nz%TYPE;
v_product_seq custoverrideprice.product_seq@cdr_nz%TYPE;
BEGIN
FOR rec_get_accounts IN cur_get_accounts LOOP
v_customer_ref := rec_get_accounts.customer_ref;
BEGIN
SELECT one_off_number, start_dat, end_dat, bp.product_seq
INTO v_product_charges,
v_product_start_date,
v_product_end_date,
v_product_seq
FROM custoverrideprice@cdr_nz c INNER JOIN NL_es_post_charges_log bp ON bp.child_account_id = customer_ref
WHERE customer_ref = v_customer_ref AND
c.product_seq = bp.product_seq AND
bp.product_id = p_product_id AND bp.product_seq IN (5, 11);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(v_customer_ref);
END;
IF (v_product_seq IS NOT NULL) THEN
BEGIN
gnvoverrideprice.modifyforproduct2nc@cdr_nz(p_customerref => v_customer_ref,
p_overridepriceseq => v_product_seq,
p_productseq => v_product_seq,
p_newstartdat => '1-nov-2010',
p_newenddat => '30-nov-2010',
p_newoneoffmodid => 1,
p_newoneoffnumber => v_product_charges,
p_oldoneoffmodid => 1,
p_oldoneoffnumber => v_product_charges,
p_newrecurmodid => NULL,
p_newrecurnumber => NULL,
p_newtermmodid => NULL,
p_newtermnumber => NULL,
p_newsusponemodid => NULL,
p_newsusponenumber => NULL,
p_newsusprecurmodid => NULL,
p_newsusprecurnumber => NULL,
p_newreactmodid => NULL,
p_newreactnumber => NULL,
p_newnotes => NULL,
p_oldstartdat => v_product_start_date,
p_oldenddat => v_product_end_date,
p_oldrecurmodid => NULL,
p_oldrecurnumber => NULL,
p_oldtermmodid => NULL,
p_oldtermnumber => NULL,
p_oldsusponemodid => NULL,
p_oldsusponenumber => NULL,
p_oldsusprecurmodid => NULL,
p_oldsusprecurnumber => NULL,
p_oldreactmodid => NULL,
p_oldreactnumber => NULL,
p_oldnotes => NULL);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(v_customer_ref || SQLERRM);
END;
END IF;
COMMIT;
END LOOP;
END;
=======
============== SP_CORRECT_DETAILS_ON_REBILL
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_correct_details_on_rebill(p_bill_cycle_date DATE) IS
v_bill_version NUMBER;
v_bill_run_date DATE;
v_invoice_amount NUMBER;
v_adjustment NUMBER;
v_charges NUMBER;
v_due_date DATE;
v_bill_seq NUMBER;
v_invoice_amount_old NUMBER;
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_account_id NL_es_home_sys_account.account_id%TYPE;
v_is_new NL_es_home_sys_account.account_id%TYPE;
v_payment NUMBER;
CURSOR cur_get_accounts IS
SELECT account_num,
bill_version,
actual_bill_dtm,
balance_out_mny,
invoice_net_mny,
adjustments_mny,
payment_due_dat,
bill_seq,
payments_mny
FROM billsummary@cdr_nz
WHERE account_num IN
(select * from rebilled_hbs) AND
bill_status = 1 AND bill_dtm = p_bill_cycle_date;
BEGIN
FOR rec_get_accounts IN cur_get_accounts LOOP BEGIN v_home_sys_account_id := rec_get_accounts.account_num;
v_bill_version := rec_get_accounts.bill_version;
v_bill_run_date := rec_get_accounts.actual_bill_dtm;
v_invoice_amount := rec_get_accounts.balance_out_mny;
v_charges := rec_get_accounts.invoice_net_mny;
v_adjustment := rec_get_accounts.adjustments_mny;
v_due_date := rec_get_accounts.payment_due_dat;
v_bill_seq := rec_get_accounts.bill_seq;
v_payment := rec_get_accounts.payments_mny;
SELECT bh.account_id, is_new INTO v_account_id, v_is_new FROM NL_es_home_sys_account bh WHERE bh.home_sys_account_id = v_home_sys_account_id AND bh.is_active = 1;
SELECT bc.invoice_amount INTO v_invoice_amount_old FROM NL_es_cdr_bill_invoice bc WHERE bc.account_id = v_account_id AND bc.end_date = p_bill_cycle_date AND bc.logged_date > p_bill_cycle_date;
IF ((v_invoice_amount >= 0 AND v_invoice_amount_old >= 0) OR
(v_invoice_amount_old < 0 AND v_invoice_amount < 0)) THEN
UPDATE NL_es_cdr_bill_invoice bc SET bc.bill_run_date = v_bill_run_date, bc.due_date = v_due_date, bc.charges = v_charges, bc.invoice_amount = v_invoice_amount, bc.outstanding_bal = v_invoice_amount, bc.adjustment = v_adjustment, bc.bill_version = v_bill_version, bc.last_payment = -v_payment WHERE bc.account_id = v_account_id AND bc.invoice_seq_no = v_bill_seq;
ELSIF(v_invoice_amount <= 0 AND v_invoice_amount_old >= 0) THEN UPDATE NL_es_cdr_bill_invoice bc SET bc.bill_run_date = v_bill_run_date, bc.due_date = v_due_date, bc.charges = v_charges, bc.invoice_amount = v_invoice_amount, bc.outstanding_bal = v_invoice_amount, bc.adjustment = v_adjustment, bc.bill_version = v_bill_version, bc.last_payment = -v_payment WHERE bc.account_id = v_account_id AND bc.invoice_seq_no = v_bill_seq;
UPDATE NL_es_home_sys_account bh SET bh.is_new = -1 WHERE account_id = v_account_id;
ELSIF(v_invoice_amount <= 0 AND v_invoice_amount_old > 0) THEN
UPDATE NL_es_cdr_bill_invoice bc SET bc.bill_run_date = v_bill_run_date, bc.due_date = v_due_date, bc.charges = v_charges, bc.invoice_amount = v_invoice_amount, bc.outstanding_bal = v_invoice_amount, bc.adjustment = v_adjustment, bc.bill_version = v_bill_version, bc.last_payment = -v_payment WHERE bc.account_id = v_account_id AND bc.invoice_seq_no = v_bill_seq;
UPDATE NL_es_home_sys_account bh SET bh.is_new = 0 WHERE account_id = v_account_id;
END IF; COMMIT;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(v_account_id || SQLERRM);
END;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_correct_details_on_rebill(p_bill_cycle_date DATE) IS
v_bill_version NUMBER;
v_bill_run_date DATE;
v_invoice_amount NUMBER;
v_adjustment NUMBER;
v_charges NUMBER;
v_due_date DATE;
v_bill_seq NUMBER;
v_invoice_amount_old NUMBER;
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_account_id NL_es_home_sys_account.account_id%TYPE;
v_is_new NL_es_home_sys_account.account_id%TYPE;
v_payment NUMBER;
CURSOR cur_get_accounts IS
SELECT account_num,
bill_version,
actual_bill_dtm,
balance_out_mny,
invoice_net_mny,
adjustments_mny,
payment_due_dat,
bill_seq,
payments_mny
FROM billsummary@cdr_nz
WHERE account_num IN
(select * from rebilled_hbs) AND
bill_status = 1 AND bill_dtm = p_bill_cycle_date;
BEGIN
FOR rec_get_accounts IN cur_get_accounts LOOP BEGIN v_home_sys_account_id := rec_get_accounts.account_num;
v_bill_version := rec_get_accounts.bill_version;
v_bill_run_date := rec_get_accounts.actual_bill_dtm;
v_invoice_amount := rec_get_accounts.balance_out_mny;
v_charges := rec_get_accounts.invoice_net_mny;
v_adjustment := rec_get_accounts.adjustments_mny;
v_due_date := rec_get_accounts.payment_due_dat;
v_bill_seq := rec_get_accounts.bill_seq;
v_payment := rec_get_accounts.payments_mny;
SELECT bh.account_id, is_new INTO v_account_id, v_is_new FROM NL_es_home_sys_account bh WHERE bh.home_sys_account_id = v_home_sys_account_id AND bh.is_active = 1;
SELECT bc.invoice_amount INTO v_invoice_amount_old FROM NL_es_cdr_bill_invoice bc WHERE bc.account_id = v_account_id AND bc.end_date = p_bill_cycle_date AND bc.logged_date > p_bill_cycle_date;
IF ((v_invoice_amount >= 0 AND v_invoice_amount_old >= 0) OR
(v_invoice_amount_old < 0 AND v_invoice_amount < 0)) THEN
UPDATE NL_es_cdr_bill_invoice bc SET bc.bill_run_date = v_bill_run_date, bc.due_date = v_due_date, bc.charges = v_charges, bc.invoice_amount = v_invoice_amount, bc.outstanding_bal = v_invoice_amount, bc.adjustment = v_adjustment, bc.bill_version = v_bill_version, bc.last_payment = -v_payment WHERE bc.account_id = v_account_id AND bc.invoice_seq_no = v_bill_seq;
ELSIF(v_invoice_amount <= 0 AND v_invoice_amount_old >= 0) THEN UPDATE NL_es_cdr_bill_invoice bc SET bc.bill_run_date = v_bill_run_date, bc.due_date = v_due_date, bc.charges = v_charges, bc.invoice_amount = v_invoice_amount, bc.outstanding_bal = v_invoice_amount, bc.adjustment = v_adjustment, bc.bill_version = v_bill_version, bc.last_payment = -v_payment WHERE bc.account_id = v_account_id AND bc.invoice_seq_no = v_bill_seq;
UPDATE NL_es_home_sys_account bh SET bh.is_new = -1 WHERE account_id = v_account_id;
ELSIF(v_invoice_amount <= 0 AND v_invoice_amount_old > 0) THEN
UPDATE NL_es_cdr_bill_invoice bc SET bc.bill_run_date = v_bill_run_date, bc.due_date = v_due_date, bc.charges = v_charges, bc.invoice_amount = v_invoice_amount, bc.outstanding_bal = v_invoice_amount, bc.adjustment = v_adjustment, bc.bill_version = v_bill_version, bc.last_payment = -v_payment WHERE bc.account_id = v_account_id AND bc.invoice_seq_no = v_bill_seq;
UPDATE NL_es_home_sys_account bh SET bh.is_new = 0 WHERE account_id = v_account_id;
END IF; COMMIT;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(v_account_id || SQLERRM);
END;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_correct_details_on_rebill(p_bill_cycle_date DATE) IS
v_bill_version NUMBER;
v_bill_run_date DATE;
v_invoice_amount NUMBER;
v_adjustment NUMBER;
v_charges NUMBER;
v_due_date DATE;
v_bill_seq NUMBER;
v_invoice_amount_old NUMBER;
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_account_id NL_es_home_sys_account.account_id%TYPE;
v_is_new NL_es_home_sys_account.account_id%TYPE;
v_payment NUMBER;
CURSOR cur_get_accounts IS
SELECT account_num,
bill_version,
actual_bill_dtm,
balance_out_mny,
invoice_net_mny,
adjustments_mny,
payment_due_dat,
bill_seq,
payments_mny
FROM billsummary@cdr_nz
WHERE account_num IN
(select * from rebilled_hbs) AND
bill_status = 1 AND bill_dtm = p_bill_cycle_date;
BEGIN
FOR rec_get_accounts IN cur_get_accounts LOOP BEGIN v_home_sys_account_id := rec_get_accounts.account_num;
v_bill_version := rec_get_accounts.bill_version;
v_bill_run_date := rec_get_accounts.actual_bill_dtm;
v_invoice_amount := rec_get_accounts.balance_out_mny;
v_charges := rec_get_accounts.invoice_net_mny;
v_adjustment := rec_get_accounts.adjustments_mny;
v_due_date := rec_get_accounts.payment_due_dat;
v_bill_seq := rec_get_accounts.bill_seq;
v_payment := rec_get_accounts.payments_mny;
SELECT bh.account_id, is_new INTO v_account_id, v_is_new FROM NL_es_home_sys_account bh WHERE bh.home_sys_account_id = v_home_sys_account_id AND bh.is_active = 1;
SELECT bc.invoice_amount INTO v_invoice_amount_old FROM NL_es_cdr_bill_invoice bc WHERE bc.account_id = v_account_id AND bc.end_date = p_bill_cycle_date AND bc.logged_date > p_bill_cycle_date;
IF ((v_invoice_amount >= 0 AND v_invoice_amount_old >= 0) OR
(v_invoice_amount_old < 0 AND v_invoice_amount < 0)) THEN
UPDATE NL_es_cdr_bill_invoice bc SET bc.bill_run_date = v_bill_run_date, bc.due_date = v_due_date, bc.charges = v_charges, bc.invoice_amount = v_invoice_amount, bc.outstanding_bal = v_invoice_amount, bc.adjustment = v_adjustment, bc.bill_version = v_bill_version, bc.last_payment = -v_payment WHERE bc.account_id = v_account_id AND bc.invoice_seq_no = v_bill_seq;
ELSIF(v_invoice_amount <= 0 AND v_invoice_amount_old >= 0) THEN UPDATE NL_es_cdr_bill_invoice bc SET bc.bill_run_date = v_bill_run_date, bc.due_date = v_due_date, bc.charges = v_charges, bc.invoice_amount = v_invoice_amount, bc.outstanding_bal = v_invoice_amount, bc.adjustment = v_adjustment, bc.bill_version = v_bill_version, bc.last_payment = -v_payment WHERE bc.account_id = v_account_id AND bc.invoice_seq_no = v_bill_seq;
UPDATE NL_es_home_sys_account bh SET bh.is_new = -1 WHERE account_id = v_account_id;
ELSIF(v_invoice_amount <= 0 AND v_invoice_amount_old > 0) THEN
UPDATE NL_es_cdr_bill_invoice bc SET bc.bill_run_date = v_bill_run_date, bc.due_date = v_due_date, bc.charges = v_charges, bc.invoice_amount = v_invoice_amount, bc.outstanding_bal = v_invoice_amount, bc.adjustment = v_adjustment, bc.bill_version = v_bill_version, bc.last_payment = -v_payment WHERE bc.account_id = v_account_id AND bc.invoice_seq_no = v_bill_seq;
UPDATE NL_es_home_sys_account bh SET bh.is_new = 0 WHERE account_id = v_account_id;
END IF; COMMIT;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(v_account_id || SQLERRM);
END;
END LOOP;
END;
=======SP_CORRECT_EVERYTHING
CREATE OR REPLACE PROCEDURE ESTAPLE.SP_CORRECT_EVERYTHING(p_account_id varchar2)
AS
V_ACCOUNT_ID NL_ES_POST_CHARGES_LOG.CHILD_ACCOUNT_ID%TYPE;
V_CUSTOMER_REF CUSTOVERRIDEPRICE.CUSTOMER_REF@CDR_NZ%TYPE;
V_PRODUCT_ID NL_ES_POST_CHARGES_LOG.PRODUCT_ID%TYPE;
V_OVERRIDE_PRICE_SEQ NL_ES_POST_CHARGES_LOG.PRODUCT_SEQ%TYPE;
V_PRODUCT_SEQ NL_ES_POST_CHARGES_LOG.PRODUCT_SEQ%TYPE;
V_START_DAT CUSTOVERRIDEPRICE.START_DAT@CDR_NZ%TYPE;
V_END_DAT CUSTOVERRIDEPRICE.END_DAT@CDR_NZ%TYPE;
V_CORRECT_CHARGE CUSTOVERRIDEPRICE.ONE_OFF_NUMBER@CDR_NZ%TYPE;
V_ONE_OFF_NUMBER CUSTOVERRIDEPRICE.ONE_OFF_NUMBER@CDR_NZ%TYPE;
CURSOR CUR_GET_ACCOUNTS IS
SELECT ACCOUNT_ID, CUSTOMER_REF
FROM NL_ES_HOME_SYS_ACCOUNT B INNER JOIN ACCOUNT@CDR_NZ A ON A.ACCOUNT_NUM = B.ACCOUNT_ID
and b.account_id =p_account_id;
CURSOR CUR_GET_INCORRECT_CHARGES IS
SELECT PRODUCT_ID,
OVERRIDE_PRICE_SEQ,
C.PRODUCT_SEQ,
START_DAT,
END_DAT,
BP.CHARGE,
ONE_OFF_NUMBER
FROM CUSTOVERRIDEPRICE@CDR_NZ C INNER JOIN NL_ES_POST_CHARGES_LOG BP ON C.CUSTOMER_REF = BP.CHILD_ACCOUNT_ID AND BP.PRODUCT_SEQ = C.PRODUCT_SEQ
WHERE BP.CHARGE <> C.ONE_OFF_NUMBER AND CUSTOMER_REF = V_CUSTOMER_REF AND
TRUNC(CHARGE_POST_DATE) >= '01-APR-2012'
;
BEGIN
FOR REC_GET_ACCOUNTS IN CUR_GET_ACCOUNTS LOOP
V_ACCOUNT_ID := REC_GET_ACCOUNTS.ACCOUNT_ID;
V_CUSTOMER_REF := REC_GET_ACCOUNTS.CUSTOMER_REF;
FOR REC_GET_INCORRECT_CHARGES IN CUR_GET_INCORRECT_CHARGES LOOP
V_PRODUCT_ID := REC_GET_INCORRECT_CHARGES.PRODUCT_ID;
V_OVERRIDE_PRICE_SEQ := REC_GET_INCORRECT_CHARGES.OVERRIDE_PRICE_SEQ;
V_PRODUCT_SEQ := REC_GET_INCORRECT_CHARGES.PRODUCT_SEQ;
V_START_DAT := REC_GET_INCORRECT_CHARGES.START_DAT;
V_END_DAT := REC_GET_INCORRECT_CHARGES.END_DAT;
V_CORRECT_CHARGE := REC_GET_INCORRECT_CHARGES.CHARGE;
V_ONE_OFF_NUMBER := REC_GET_INCORRECT_CHARGES.ONE_OFF_NUMBER;
GNVOVERRIDEPRICE.MODIFYFORPRODUCT2NC@CDR_NZ(P_CUSTOMERREF => V_CUSTOMER_REF,
P_OVERRIDEPRICESEQ => V_OVERRIDE_PRICE_SEQ,
P_PRODUCTSEQ => V_PRODUCT_SEQ,
P_NEWSTARTDAT => V_START_DAT,
P_NEWENDDAT => V_END_DAT,
P_NEWONEOFFMODID => 1,
P_NEWONEOFFNUMBER => V_CORRECT_CHARGE,
P_OLDONEOFFMODID => 1,
P_OLDONEOFFNUMBER => V_ONE_OFF_NUMBER,
P_NEWRECURMODID => NULL,
P_NEWRECURNUMBER => NULL,
P_NEWTERMMODID => NULL,
P_NEWTERMNUMBER => NULL,
P_NEWSUSPONEMODID => NULL,
P_NEWSUSPONENUMBER => NULL,
P_NEWSUSPRECURMODID => NULL,
P_NEWSUSPRECURNUMBER => NULL,
P_NEWREACTMODID => NULL,
P_NEWREACTNUMBER => NULL,
P_NEWNOTES => NULL,
P_OLDSTARTDAT => V_START_DAT,
P_OLDENDDAT => V_END_DAT,
P_OLDRECURMODID => NULL,
P_OLDRECURNUMBER => NULL,
P_OLDTERMMODID => NULL,
P_OLDTERMNUMBER => NULL,
P_OLDSUSPONEMODID => NULL,
P_OLDSUSPONENUMBER => NULL,
P_OLDSUSPRECURMODID => NULL,
P_OLDSUSPRECURNUMBER => NULL,
P_OLDREACTMODID => NULL,
P_OLDREACTNUMBER => NULL,
P_OLDNOTES => NULL);
--update for NL_ES_hbs_ledger
Pkg_MISC.SP_LEDGER_UPDATE(V_ACCOUNT_ID,(V_CORRECT_CHARGE-V_ONE_OFF_NUMBER));
COMMIT;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('error for child account ' || V_ACCOUNT_ID ||
SQLERRM);
END;
=========SP.CORRECT_PAYMENTS
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_correct_payments(p_account_id VARCHAR2,
p_payment_mny IN NL_es_cdr_bill_invoice.last_payment%TYPE) IS
CURSOR cur_get_accounts IS
SELECT customer_ref
FROM account@cdr_nz
WHERE account_num = p_account_id;
v_product_charges_old custoverrideprice.one_off_number@cdr_nz%TYPE;
v_product_charges_new custoverrideprice.one_off_number@cdr_nz%TYPE;
v_customer_ref customer.customer_ref@cdr_nz%TYPE;
v_override_price_seq custoverrideprice.override_price_seq@cdr_nz%TYPE;
v_product_seq custoverrideprice.product_seq@cdr_nz%TYPE;
v_end_dat custoverrideprice.end_dat@cdr_nz%TYPE;
v_start_dat custoverrideprice.start_dat@cdr_nz%TYPE;
v_errmsg VARCHAR2(3000);
BEGIN
FOR rec_get_accounts IN cur_get_accounts LOOP
v_customer_ref := rec_get_accounts.customer_ref;
SELECT one_off_number,
override_price_seq,
product_seq,
start_dat,
end_dat
INTO v_product_charges_old,
v_override_price_seq,
v_product_seq,
v_start_dat,
v_end_dat
FROM custoverrideprice@cdr_nz
WHERE customer_ref = v_customer_ref AND
end_dat = to_date('31-10-2010', 'dd-mm-yyyy') AND
override_price_seq IN
(SELECT MAX(product_seq)
FROM custhasproduct@cdr_nz
WHERE customer_ref = v_customer_ref AND product_id = 188);
gnvoverrideprice.modifyforproduct2nc@cdr_nz(p_customerref => v_customer_ref,
p_overridepriceseq => v_override_price_seq,
p_productseq => v_product_seq,
p_newstartdat => v_start_dat,
p_newenddat => v_end_dat,
p_newoneoffmodid => 1,
p_newoneoffnumber => v_product_charges_old -
p_payment_mny,
p_oldoneoffmodid => 1,
p_oldoneoffnumber => v_product_charges_old,
p_newrecurmodid => NULL,
p_newrecurnumber => NULL,
p_newtermmodid => NULL,
p_newtermnumber => NULL,
p_newsusponemodid => NULL,
p_newsusponenumber => NULL,
p_newsusprecurmodid => NULL,
p_newsusprecurnumber => NULL,
p_newreactmodid => NULL,
p_newreactnumber => NULL,
p_newnotes => NULL,
p_oldstartdat => v_start_dat,
p_oldenddat => v_end_dat,
p_oldrecurmodid => NULL,
p_oldrecurnumber => NULL,
p_oldtermmodid => NULL,
p_oldtermnumber => NULL,
p_oldsusponemodid => NULL,
p_oldsusponenumber => NULL,
p_oldsusprecurmodid => NULL,
p_oldsusprecurnumber => NULL,
p_oldreactmodid => NULL,
p_oldreactnumber => NULL,
p_oldnotes => NULL);
UPDATE NL_es_post_charges_log
SET charge = v_product_charges_old - p_payment_mny
WHERE child_account_id = p_account_id AND product_id = 188 AND
trunc(charge_post_date) >= '11-dec-2010';
COMMIT;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(SQLERRM);
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
========== SP_CREATE_PRODUCT
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_create_product(p_customer_ref IN VARCHAR2,
p_account_id IN VARCHAR2,
p_product_charges IN NUMBER,
p_product_id IN NUMBER,
p_invoive_seq_no IN NUMBER,
p_start_date DATE,
p_end_date DATE)
AS
CURSOR cur_product_attributes IS
SELECT pa.product_attribute_subid
FROM productattribute@cdr_nz pa
WHERE pa.product_id = p_product_id;
v_tariff_id tariff.tariff_id@cdr_nz%TYPE;
v_cps_id cpsproducttax.cps_id@cdr_nz%TYPE;
v_attribute_subid productattribute.product_attribute_subid@cdr_nz%TYPE;
v_product_seq custhasproduct.product_seq@cdr_nz%TYPE;
v_override_priceseq custoverrideprice.override_price_seq@cdr_nz%TYPE;
v_product_attribute custproductattrdetails.attribute_value@cdr_nz%TYPE;
v_parent_account_id NL_es_account.account_id%TYPE;
v_hbs_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%TYPE;
BEGIN
SELECT t.tariff_id
INTO v_tariff_id
FROM tariff@cdr_nz t
WHERE t.tariff_name = pkg_es.tariff_name AND
t.catalogue_change_id =
(SELECT catalogue_change_id
FROM cataloguechange@cdr_nz
WHERE catalogue_status = pkg_es.catalogue_status_live);
SELECT cpt.cps_id
INTO v_cps_id
FROM cpsproducttax@cdr_nz cpt
WHERE cpt.product_id = p_product_id;
--Stored procedure for creating a product instance for customer ref,account id, product id combination
--for a period specified by p_startdtm,p_termdtm(output : product seq)
gnvcustproduct.createcustproduct7nc@cdr_nz(p_customerref => p_customer_ref,
p_productid => p_product_id,
p_startdtm => p_start_date,
p_termdtm => NULL,
p_parentproductseq => NULL,
p_packageseq => NULL,
p_productpackageinstance => NULL,
p_accountnum => p_account_id,
p_budgetcentreseq => NULL,
p_tariffid => v_tariff_id,
p_competitortariffid => NULL,
p_productlabel => NULL,
p_productquantity => pkg_es.product_quantity,
p_custordernum => NULL,
p_supplierordernum => NULL,
p_custproductcontactseq => NULL,
p_contractedpos => v_cps_id,
p_cpstaxexemptref => NULL,
p_cpstaxexempttxt => NULL,
p_contractseq => NULL,
p_additions_quantity => NULL,
p_terminations_quantity => NULL,
p_productstatus => pkg_es.product_status,
p_statusreasontxt => NULL,
p_subscription_ref => NULL,
p_productoptionalboo => pkg_es.product_optional_boo,
p_productseq => v_product_seq);
--Stored procedure for overriding prices (periodic price) for the customer product instance(output: p_overridepriceseq)
gnvoverrideprice.addtoproduct2nc@cdr_nz(p_customerref => p_customer_ref,
p_productseq => v_product_seq,
p_startdat => p_start_date,
p_enddat => nvl(p_end_date,
last_day(p_start_date)),
p_oneoffmodid => pkg_es.recurring_mod_type_id,
p_oneoffnumber => p_product_charges,
p_recurmodid => NULL,
p_recurnumber => NULL,
p_termmodid => NULL,
p_termnumber => NULL,
p_susponemodid => NULL,
p_susponenumber => NULL,
p_susprecurmodid => NULL,
p_susprecurnumber => NULL,
p_reactmodid => NULL,
p_reactnumber => NULL,
p_notes => NULL,
p_overridepriceseq => v_override_priceseq);
--Stored procedure for modifying the attribute value of the customer product instance
--The attribute is Charge_desc (contains the name of the product of HBS accounts)
FOR rec_product_attributes IN cur_product_attributes LOOP
v_attribute_subid := rec_product_attributes.product_attribute_subid;
SELECT home_sys_account_id
INTO v_product_attribute
FROM NL_es_home_sys_account bh
WHERE bh.account_id = p_account_id;
--v_product_attribute := 'id7612332917' || ' ' || v_product_attribute;
IF (v_attribute_subid = 1) THEN
gnvproductattributes.modifycustprodattr3nc@cdr_nz(customerref => p_customer_ref,
productseq => v_product_seq,
attributesubid => v_attribute_subid,
changefromdate => p_start_date,
changeuptodate => NULL,
oldvalue => NULL,
newvalue => v_product_attribute);
ELSIF (v_attribute_subid = 2) THEN
v_product_attribute := 1;
gnvproductattributes.modifycustprodattr3nc@cdr_nz(customerref => p_customer_ref,
productseq => v_product_seq,
attributesubid => v_attribute_subid,
changefromdate => p_start_date,
changeuptodate => NULL,
oldvalue => NULL,
newvalue => v_product_attribute);
END IF;
END LOOP;
SELECT parent_account_id, invoice_seq_no, home_sys_account_id
INTO v_parent_account_id, v_invoice_seq_no, v_hbs_id
FROM NL_es_home_sys_account bh
WHERE bh.account_id = p_account_id;
--added for parallel posting
IF p_invoive_seq_no IS NOT NULL THEN
v_invoice_seq_no := p_invoive_seq_no;
END IF;
--Stored procedure for redirecting the charges from child account to ES parent account
gnvproductcharging.modifycustprodchrg1nc@cdr_nz(customerref => p_customer_ref,
productseq => v_product_seq,
oldstartdate => p_start_date,
oldenddate => NULL,
oldaccountnum => p_account_id,
oldbudgetcentreseq => NULL,
oldchrgtype => pkg_es.charge_type,
oldpercentage => pkg_es.charge_percentage,
newstartdate => p_start_date,
newenddate => NULL,
newaccountnum => v_parent_account_id,
newbudgetcentreseq => NULL,
newchrgtype => pkg_es.charge_type,
newpercentage => pkg_es.charge_percentage);
pkg_es_post_charges_del.sp_log_charges_posted(p_account_id,
v_invoice_seq_no,
p_product_id,
v_product_seq,
p_product_charges,
v_hbs_id,
v_parent_account_id);
--update for NL_ES_hbs_ledger
Pkg_MISC.SP_LEDGER_UPDATE(p_account_id,p_product_charges);
END;
==========SP_CREATE_PRODUCT_ACCOUNTWISE
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_create_product_accountwise(p_customer_ref IN VARCHAR2,
p_account_id IN VARCHAR2,
p_product_charges IN NUMBER,
p_product_id IN NUMBER,
p_invoive_seq_no IN NUMBER,
p_start_date DATE,
p_end_date DATE)
AS
CURSOR cur_product_attributes IS
SELECT pa.product_attribute_subid
FROM productattribute@cdr_nz pa
WHERE pa.product_id = p_product_id;
v_tariff_id tariff.tariff_id@cdr_nz%TYPE;
v_cps_id cpsproducttax.cps_id@cdr_nz%TYPE;
v_attribute_subid productattribute.product_attribute_subid@cdr_nz%TYPE;
v_product_seq custhasproduct.product_seq@cdr_nz%TYPE;
v_override_priceseq custoverrideprice.override_price_seq@cdr_nz%TYPE;
v_product_attribute custproductattrdetails.attribute_value@cdr_nz%TYPE;
v_parent_account_id NL_es_account.account_id%TYPE;
v_hbs_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%TYPE;
BEGIN
SELECT t.tariff_id
INTO v_tariff_id
FROM tariff@cdr_nz t
WHERE t.tariff_name = pkg_es.tariff_name AND
t.catalogue_change_id =
(SELECT catalogue_change_id
FROM cataloguechange@cdr_nz
WHERE catalogue_status = pkg_es.catalogue_status_live);
SELECT cpt.cps_id
INTO v_cps_id
FROM cpsproducttax@cdr_nz cpt
WHERE cpt.product_id = p_product_id;
--Stored procedure for creating a product instance for customer ref,account id, product id combination
--for a period specified by p_startdtm,p_termdtm(output : product seq)
gnvcustproduct.createcustproduct7nc@cdr_nz(p_customerref => p_customer_ref,
p_productid => p_product_id,
p_startdtm => p_start_date,
p_termdtm => NULL,
p_parentproductseq => NULL,
p_packageseq => NULL,
p_productpackageinstance => NULL,
p_accountnum => p_account_id,
p_budgetcentreseq => NULL,
p_tariffid => v_tariff_id,
p_competitortariffid => NULL,
p_productlabel => NULL,
p_productquantity => pkg_es.product_quantity,
p_custordernum => NULL,
p_supplierordernum => NULL,
p_custproductcontactseq => NULL,
p_contractedpos => v_cps_id,
p_cpstaxexemptref => NULL,
p_cpstaxexempttxt => NULL,
p_contractseq => NULL,
p_additions_quantity => NULL,
p_terminations_quantity => NULL,
p_productstatus => pkg_es.product_status,
p_statusreasontxt => NULL,
p_subscription_ref => NULL,
p_productoptionalboo => pkg_es.product_optional_boo,
p_productseq => v_product_seq);
--Stored procedure for overriding prices (periodic price) for the customer product instance(output: p_overridepriceseq)
gnvoverrideprice.addtoproduct2nc@cdr_nz(p_customerref => p_customer_ref,
p_productseq => v_product_seq,
p_startdat => p_start_date,
p_enddat => nvl(p_end_date,
last_day(p_start_date)),
p_oneoffmodid => pkg_es.recurring_mod_type_id,
p_oneoffnumber => p_product_charges,
p_recurmodid => NULL,
p_recurnumber => NULL,
p_termmodid => NULL,
p_termnumber => NULL,
p_susponemodid => NULL,
p_susponenumber => NULL,
p_susprecurmodid => NULL,
p_susprecurnumber => NULL,
p_reactmodid => NULL,
p_reactnumber => NULL,
p_notes => NULL,
p_overridepriceseq => v_override_priceseq);
--Stored procedure for modifying the attribute value of the customer product instance
--The attribute is Charge_desc (contains the name of the product of HBS accounts)
FOR rec_product_attributes IN cur_product_attributes LOOP
v_attribute_subid := rec_product_attributes.product_attribute_subid;
SELECT home_sys_account_id
INTO v_product_attribute
FROM NL_es_home_sys_account bh
WHERE bh.account_id = p_account_id;
--v_product_attribute := 'id7612332917' || ' ' || v_product_attribute;
IF (v_attribute_subid = 1) THEN
gnvproductattributes.modifycustprodattr3nc@cdr_nz(customerref => p_customer_ref,
productseq => v_product_seq,
attributesubid => v_attribute_subid,
changefromdate => p_start_date,
changeuptodate => NULL,
oldvalue => NULL,
newvalue => v_product_attribute);
ELSIF (v_attribute_subid = 2) THEN
v_product_attribute := 1;
gnvproductattributes.modifycustprodattr3nc@cdr_nz(customerref => p_customer_ref,
productseq => v_product_seq,
attributesubid => v_attribute_subid,
changefromdate => p_start_date,
changeuptodate => NULL,
oldvalue => NULL,
newvalue => v_product_attribute);
END IF;
END LOOP;
====================
SELECT parent_account_id, invoice_seq_no, home_sys_account_id
INTO v_parent_account_id, v_invoice_seq_no, v_hbs_id
FROM NL_es_home_sys_account bh
WHERE bh.account_id = p_account_id;
--added for parallel posting
IF p_invoive_seq_no IS NOT NULL THEN
v_invoice_seq_no := p_invoive_seq_no;
END IF;
--Stored procedure for redirecting the charges from child account to ES parent account
gnvproductcharging.modifycustprodchrg1nc@cdr_nz(customerref => p_customer_ref,
productseq => v_product_seq,
oldstartdate => p_start_date,
oldenddate => NULL,
oldaccountnum => p_account_id,
oldbudgetcentreseq => NULL,
oldchrgtype => pkg_es.charge_type,
oldpercentage => pkg_es.charge_percentage,
newstartdate => p_start_date,
newenddate => NULL,
newaccountnum => v_parent_account_id,
newbudgetcentreseq => NULL,
newchrgtype => pkg_es.charge_type,
newpercentage => pkg_es.charge_percentage);
pkg_es_post_charges_del.sp_log_charges_posted(p_account_id,
v_invoice_seq_no,
p_product_id,
v_product_seq,
p_product_charges,
v_hbs_id,
v_parent_account_id);
--update for NL_ES_hbs_ledger
Pkg_MISC.SP_LEDGER_UPDATE(p_account_id,p_product_charges);
END;
======== SP_CREATE_PRODUCT_INSTANCE
CREATE OR REPLACE PROCEDURE ESTAPLE.SP_CREATE_PRODUCT_INSTANCE(P_CUSTOMER_REF IN CUSTOMER.CUSTOMER_REF@CDR_NZ%TYPE,
P_ACCOUNT_ID IN NL_ES_HOME_SYS_ACCOUNT.ACCOUNT_ID%TYPE,
P_PRODUCT_ID IN PRODUCT.PRODUCT_ID@CDR_NZ%TYPE,
P_PRODUCT_START_DATE IN DATE,
P_PRODUCT_END_DATE IN DATE,
P_TARIFF_ID IN TARIFF.TARIFF_ID@CDR_NZ%TYPE,
P_PRODUCT_CHARGES IN NL_ES_CDR_BILL_INVOICE.CHARGES%TYPE,
P_PRODUCT_ATTRIBUTE IN CUSTPRODUCTATTRDETAILS.ATTRIBUTE_VALUE@CDR_NZ%TYPE,
P_INVOICE_SEQ_NO IN NL_ES_CDR_BILL_INVOICE.INVOICE_SEQ_NO%TYPE,
-- p_invoice_ref_no IN NL_es_gsm_bill_inv_det.invoice_ref_no%TYPE,
-- p_invoice_ref_resets IN NL_es_gsm_bill_inv_det.invoice_ref_resets%TYPE,
P_PARENT_ACCOUNT_ID IN NL_ES_ACCOUNT.ACCOUNT_ID%TYPE,
-- Purpose : Creates a product instance for the account id, customer ref,product id for a specified period,
-- overrides the price of this product for this period,
-- redirects the charges of this product instance to the parent account id
-- and records the posting information in ES schema( NL_es_post_charges)
P_LINE_OF_BUSINESS NUMBER) AS
--Local variables
V_CPS_ID CPSPRODUCTTAX.CPS_ID@CDR_NZ%TYPE;
V_ATTRIBUTE_SUBID PRODUCTATTRIBUTE.PRODUCT_ATTRIBUTE_SUBID@CDR_NZ%TYPE;
V_PRODUCT_SEQ CUSTHASPRODUCT.PRODUCT_SEQ@CDR_NZ%TYPE;
V_OVERRIDE_PRICESEQ CUSTOVERRIDEPRICE.OVERRIDE_PRICE_SEQ@CDR_NZ%TYPE;
V_PRODUCT_ATTRIBUTE CUSTPRODUCTATTRDETAILS.ATTRIBUTE_VALUE@CDR_NZ%TYPE;
CURSOR CUR_PRODUCT_ATTRIBUTES IS
SELECT PA.PRODUCT_ATTRIBUTE_SUBID
FROM PRODUCTATTRIBUTE@CDR_NZ PA
WHERE PA.PRODUCT_ID = P_PRODUCT_ID;
BEGIN
SELECT CPT.CPS_ID
INTO V_CPS_ID
FROM CPSPRODUCTTAX@CDR_NZ CPT
WHERE CPT.PRODUCT_ID = P_PRODUCT_ID;
--Stored procedure for creating a product instance for customer ref,account id, product id combination
--for a period specified by p_startdtm,p_termdtm(output : product seq)
GNVCUSTPRODUCT.CREATECUSTPRODUCT7NC@CDR_NZ(P_CUSTOMERREF => P_CUSTOMER_REF,
P_PRODUCTID => P_PRODUCT_ID,
P_STARTDTM => P_PRODUCT_START_DATE,
P_TERMDTM => NULL,
P_PARENTPRODUCTSEQ => NULL,
P_PACKAGESEQ => NULL,
P_PRODUCTPACKAGEINSTANCE => NULL,
P_ACCOUNTNUM => P_ACCOUNT_ID,
P_BUDGETCENTRESEQ => NULL,
P_TARIFFID => P_TARIFF_ID,
P_COMPETITORTARIFFID => NULL,
P_PRODUCTLABEL => NULL,
P_PRODUCTQUANTITY => PKG_ES.PRODUCT_QUANTITY,
P_CUSTORDERNUM => NULL,
P_SUPPLIERORDERNUM => NULL,
P_CUSTPRODUCTCONTACTSEQ => NULL,
P_CONTRACTEDPOS => V_CPS_ID,
P_CPSTAXEXEMPTREF => NULL,
P_CPSTAXEXEMPTTXT => NULL,
P_CONTRACTSEQ => NULL,
P_ADDITIONS_QUANTITY => NULL,
P_TERMINATIONS_QUANTITY => NULL,
P_PRODUCTSTATUS => PKG_ES.PRODUCT_STATUS,
P_STATUSREASONTXT => NULL,
P_SUBSCRIPTION_REF => NULL,
P_PRODUCTOPTIONALBOO => PKG_ES.PRODUCT_OPTIONAL_BOO,
P_PRODUCTSEQ => V_PRODUCT_SEQ);
--Stored procedure for overriding prices (periodic price) for the customer product instance(output: p_overridepriceseq)
GNVOVERRIDEPRICE.ADDTOPRODUCT2NC@CDR_NZ(P_CUSTOMERREF => P_CUSTOMER_REF,
P_PRODUCTSEQ => V_PRODUCT_SEQ,
P_STARTDAT => P_PRODUCT_START_DATE,
P_ENDDAT => P_PRODUCT_END_DATE,
P_ONEOFFMODID => PKG_ES.RECURRING_MOD_TYPE_ID,
P_ONEOFFNUMBER => P_PRODUCT_CHARGES,
P_RECURMODID => NULL,
P_RECURNUMBER => NULL,
P_TERMMODID => NULL,
P_TERMNUMBER => NULL,
P_SUSPONEMODID => NULL,
P_SUSPONENUMBER => NULL,
P_SUSPRECURMODID => NULL,
P_SUSPRECURNUMBER => NULL,
P_REACTMODID => NULL,
P_REACTNUMBER => NULL,
P_NOTES => NULL,
P_OVERRIDEPRICESEQ => V_OVERRIDE_PRICESEQ);
--Stored procedure for modifying the attribute value of the customer product instance
--The attribute is Charge_desc (contains the name of the product of HBS accounts)
FOR REC_PRODUCT_ATTRIBUTES IN CUR_PRODUCT_ATTRIBUTES LOOP
V_ATTRIBUTE_SUBID := REC_PRODUCT_ATTRIBUTES.PRODUCT_ATTRIBUTE_SUBID;
IF (V_ATTRIBUTE_SUBID = 1) THEN
GNVPRODUCTATTRIBUTES.MODIFYCUSTPRODATTR3NC@CDR_NZ(CUSTOMERREF => P_CUSTOMER_REF,
PRODUCTSEQ => V_OVERRIDE_PRICESEQ,
ATTRIBUTESUBID => V_ATTRIBUTE_SUBID,
CHANGEFROMDATE => P_PRODUCT_START_DATE,
CHANGEUPTODATE => NULL,
OLDVALUE => NULL,
NEWVALUE => P_PRODUCT_ATTRIBUTE);
ELSIF (V_ATTRIBUTE_SUBID = 2) THEN
V_PRODUCT_ATTRIBUTE := P_LINE_OF_BUSINESS;
GNVPRODUCTATTRIBUTES.MODIFYCUSTPRODATTR3NC@CDR_NZ(CUSTOMERREF => P_CUSTOMER_REF,
PRODUCTSEQ => V_OVERRIDE_PRICESEQ,
ATTRIBUTESUBID => V_ATTRIBUTE_SUBID,
CHANGEFROMDATE => P_PRODUCT_START_DATE,
CHANGEUPTODATE => NULL,
OLDVALUE => NULL,
NEWVALUE => V_PRODUCT_ATTRIBUTE);
END IF;
END LOOP;
-- Log the charges posted in ES database
/*sp_log_charges_posted(p_account_id,
p_invoice_seq_no,
p_product_id,
v_product_seq,
p_product_charges,
p_product_attribute,
-- p_invoice_ref_no, --------- NotRequiredInMigration
-- p_invoice_ref_resets, --------- NotRequiredInMigration
p_parent_account_id);
*/
--Stored procedure for redirecting the charges from child account to ES parent account
GNVPRODUCTCHARGING.MODIFYCUSTPRODCHRG1NC@CDR_NZ(CUSTOMERREF => P_CUSTOMER_REF,
PRODUCTSEQ => V_PRODUCT_SEQ,
OLDSTARTDATE => P_PRODUCT_START_DATE,
OLDENDDATE => NULL,
OLDACCOUNTNUM => P_ACCOUNT_ID,
OLDBUDGETCENTRESEQ => NULL,
OLDCHRGTYPE => PKG_ES.CHARGE_TYPE,
OLDPERCENTAGE => PKG_ES.CHARGE_PERCENTAGE,
NEWSTARTDATE => P_PRODUCT_START_DATE,
NEWENDDATE => NULL,
NEWACCOUNTNUM => P_PARENT_ACCOUNT_ID,
NEWBUDGETCENTRESEQ => NULL,
NEWCHRGTYPE => PKG_ES.CHARGE_TYPE,
NEWPERCENTAGE => PKG_ES.CHARGE_PERCENTAGE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE NO_DATA_FOUND;
WHEN TOO_MANY_ROWS THEN
RAISE TOO_MANY_ROWS;
WHEN DUP_VAL_ON_INDEX THEN
RAISE DUP_VAL_ON_INDEX;
WHEN OTHERS THEN
RAISE;
END SP_CREATE_PRODUCT_INSTANCE;
========== SP_DEL_ACCTS
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_del_accts(p_parent_id IN VARCHAR2,
p_ssa IN VARCHAR) IS
v_account_id VARCHAR2(20);
CURSOR cur_get_accounts IS
SELECT b.account_id
FROM NL_es_home_sys_account b
WHERE b.parent_account_id = p_parent_id AND b.ssa = p_ssa;
BEGIN
FOR get_accounts IN cur_get_accounts LOOP
v_account_id := get_accounts.account_id;
DELETE FROM NL_es_cust_acq_response ca
WHERE ca.es_child_account_id = v_account_id AND
ca.es_account_id = p_parent_id;
DELETE FROM NL_es_cdr_services bs WHERE bs.account_id = v_account_id;
DELETE FROM NL_es_account ba
WHERE ba.account_id = p_parent_id AND ba.ssa = p_ssa;
DELETE FROM NL_es_home_sys_account b
WHERE b.parent_account_id = p_parent_id AND b.ssa = p_ssa;
COMMIT;
END LOOP;
END sp_del_accts;
==========SP_DETECT_MISMATCH
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_detect_mismatch(p_parent_account_id VARCHAR2) AS
v_account_id NL_es_home_sys_account.account_id%TYPE;
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%TYPE;
v_posted NUMBER;
v_irb_net NUMBER;
v_flag NUMBER := 0;
CURSOR cur_get_child IS
SELECT account_id, home_sys_account_id, invoice_seq_no
FROM NL_es_home_sys_account
WHERE parent_account_id = p_parent_account_id;
BEGIN
FOR rec_get_child IN cur_get_child LOOP
v_account_id := rec_get_child.account_id;
v_home_sys_account_id := rec_get_child.home_sys_account_id;
v_invoice_seq_no := rec_get_child.invoice_seq_no;
SELECT SUM(charge)
INTO v_posted
FROM NL_es_post_charges_log bp
WHERE bp.child_account_id = v_account_id AND
product_id NOT IN (188, 190) AND
invoice_seq_no = v_invoice_seq_no;
SELECT invoice_net_mny + invoice_tax_mny
INTO v_irb_net
FROM billsummary@cdr_nz
WHERE account_num = v_home_sys_account_id AND
bill_seq = v_invoice_seq_no;
IF (v_posted <> v_irb_net) THEN
dbms_output.put_line('mismatch for hbs ' || v_home_sys_account_id ||
' child_account_id' || v_account_id || ' ' ||
' posted ' || ' ' || v_posted || ' in irb ' ||
v_irb_net);
v_flag := 1;
END IF;
END LOOP;
IF (v_flag = 0) THEN
dbms_output.put_line('success');
END IF;
END;
==========SP_DISCOUNT_REPORT
create or replace procedure estaple.sp_discount_report(p_ssa varchar2)
as
v_parent_account_id varchar2(200);
v_account_id varchar2(200);
v_home_sys_account_id varchar2(200);
v_is_active varchar2(200);
v_remarks varchar2(200);
v_balance_out number;
v_payment number;
v_name varchar2(1000);
v_charges number;
v_discount number;
v_bill_cycle_date date;
v_actual_bill_dtm date ;
v_payment_due_dat date;
cursor cur_get_accounts
is
select account_id,s.bill_cycle_date
from NL_es_account b
inner join NL_es_discount s on s.es_account_id=b.account_id
and ssa=p_ssa
and is_split=1;
cursor cur_get_child_accounts is
select h.account_id,home_sys_account_id,is_active,remarks
from NL_es_home_sys_account h where h.parent_account_id=v_parent_account_id;
begin
for rec_get_accounts in cur_get_accounts loop
v_parent_account_id := rec_get_accounts.account_id;
v_bill_cycle_date := rec_get_accounts.bill_cycle_date;
select balance_out_mny/1000,actual_bill_dtm , payment_due_dat
into v_balance_out,v_actual_bill_dtm ,v_payment_due_dat
from billsummary@cdr_nz where
account_num=v_parent_account_id
and bill_dtm=v_bill_cycle_date and bill_status=1;
select sum(account_payment_mny)/1000
into v_payment
from accountpayment@cdr_nz ap
where account_num=v_parent_account_id and
account_payment_dat between v_actual_bill_dtm and v_payment_due_dat
and ap.account_num=v_parent_account_id and failed_dtm is null
;
for rec_get_child_accounts in cur_get_child_accounts loop
v_account_id:=rec_get_child_accounts.account_id;
v_home_sys_account_id :=rec_get_child_accounts.home_sys_account_id;
v_is_active :=rec_get_child_accounts.is_active;
v_remarks :=rec_get_child_accounts.remarks;
select first_name ||' '||last_name
into v_name
from contact@cdr_nz c where customer_ref=v_home_sys_account_id
and contact_type_id=1000002;
select sum(discount_amount/1000)
into v_discount
from NL_es_discount_split d where d.account_id=v_account_id
and discount_app_date=v_bill_cycle_date
and logged_date<'24-may-2012';
select sum(charges/1000)
into v_charges
from NL_es_cdr_bill_invoice bc inner join NL_es_home_sys_account bh
on bh.account_id=bc.account_id and
bc.account_id=v_account_id
and bc.bill_cycle_date=v_bill_cycle_date;
INSERT INTO oper_discount_details
(home_sys_account_id,
parent_id,
bill_cycle_date,
invoice_net_hbs,
parent_bill,
act_pmt_mny,
discount,
hbs_name,
remark)
VALUES
(v_home_sys_account_id,
v_parent_account_id,
v_bill_cycle_date,
v_charges,
v_balance_out,
v_payment,
v_discount,
v_name,
v_remarks);
COMMIT;
end loop;
end loop;
end;
==========SP_DROP_JOBS
create or replace procedure estaple.sp_drop_jobs
is
cursor cur_get_jobs
is
select b.job_name
from user_scheduler_jobs b ;
v_job_name varchar2(30);
begin
for rec_get_jobs in cur_get_jobs loop
v_job_name := rec_get_jobs.job_name;
dbms_scheduler.drop_job(job_name => v_job_name);
dbms_output.put_line(v_job_name||' dropped' );
end loop;
end;
==========SP_ES_DISCOUNT
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_es_discount(p_bill_cycle_date DATE) IS
v_es_account_id NL_es_discount.es_account_id%TYPE;
v_discount_id NL_es_discount.discount_id%TYPE;
v_discount_amount NL_es_discount.discount_amount%TYPE;
v_total_charges NL_es_discount.total_charges%TYPE;
v_payment_due_date DATE;
v_total_outstanding NUMBER(18);
v_actual_bill_dtm DATE;
v_new_payment NUMBER(18);
V_NET_PAY number(18);
CURSOR cur_get_accounts IS
SELECT bd.es_account_id,
bd.discount_id,
bd.discount_amount,
bd.total_charges
FROM NL_es_discount bd
WHERE bd.is_split = 0 AND
bd.bill_cycle_date = p_bill_cycle_date;
BEGIN
FOR rec_get_accounts IN cur_get_accounts LOOP
v_es_account_id := rec_get_accounts.es_account_id;
v_discount_id := rec_get_accounts.discount_id;
v_discount_amount := rec_get_accounts.discount_amount;
v_total_charges := rec_get_accounts.total_charges;
SELECT payment_due_dat, balance_out_mny, actual_bill_dtm
INTO v_payment_due_date, v_total_outstanding, v_actual_bill_dtm
FROM billsummary@cdr_nz
WHERE account_num = v_es_account_id AND
bill_dtm = p_bill_cycle_date AND bill_status = 1;
BEGIN
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_new_payment
FROM accountpayment@cdr_nz a
WHERE a.account_num = v_es_account_id AND
created_dtm >= v_actual_bill_dtm AND
account_payment_dat <= v_payment_due_date AND
account_payment_status = 1 AND deposit_boo = 'F';
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
V_NET_PAY := (V_TOTAL_OUTSTANDING - V_NEW_PAYMENT);
IF (V_NET_PAY <= 999) THEN
sp_es_discount_split(v_es_account_id,p_bill_cycle_date );
UPDATE NL_ES_DISCOUNT
SET IS_SPLIT = 1
WHERE ES_ACCOUNT_ID = v_es_account_id AND
BILL_CYCLE_DATE = p_bill_cycle_date;
end if;
end lOOP;
END;
==========SP_ES_DISCOUNT_SPLIT
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_es_discount_split (P_PARENT_ACCOUNT_ID IN VARCHAR2,p_bill_cycle_date date)
IS
-- cursor that retreives all the ES accounts.
CURSOR cur_es_child_accounts IS
SELECT parent_account_id,
account_id,
home_sys_account_id,
invoice_seq_no,
line_of_business,
parent_account_id
FROM NL_es_home_sys_account
WHERE parent_account_id =P_PARENT_ACCOUNT_ID
AND is_active <> 5;
v_parent_account_id NL_es_home_sys_account.parent_account_id%TYPE;
v_child_account_id NL_es_home_sys_account.account_id%TYPE;
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%TYPE;
v_line_of_business NL_es_home_sys_account.line_of_business%TYPE;
CURSOR cur_discount_details IS
SELECT total_charges, discount_id, bill_cycle_date, discount_amount
FROM NL_es_discount
WHERE es_account_id = v_parent_account_id AND is_split = 0 AND
bill_cycle_date =p_bill_cycle_date;
v_total_charges NL_es_discount.total_charges%TYPE;
v_discount_id NL_es_discount.discount_id%TYPE;
v_bill_date NL_es_discount.bill_cycle_date%TYPE;
v_total_discount NL_es_discount.discount_amount%TYPE;
v_cdr_charges NL_es_cdr_bill_invoice.charges%TYPE;
v_cdr_discount NL_es_discount_split.discount_amount%TYPE;
v_date DATE;
v_errmsg VARCHAR2(500);
v_errcode NUMBER(6);
BEGIN
SELECT SYSDATE INTO v_date FROM dual;
--opening of the cursor cur_es_child_accounts.
OPEN cur_es_child_accounts;
LOOP
FETCH cur_es_child_accounts
INTO v_parent_account_id, v_child_account_id, v_home_sys_account_id, v_invoice_seq_no, v_line_of_business, v_parent_account_id;
EXIT WHEN cur_es_child_accounts%NOTFOUND;
OPEN cur_discount_details;
LOOP
FETCH cur_discount_details
INTO v_total_charges, v_discount_id, v_bill_date, v_total_discount;
EXIT WHEN cur_discount_details%NOTFOUND;
v_cdr_discount := 0;
BEGIN
SELECT nvl(sum(charges - adjustment),0) -- selects the charges of an invoice of the CDR child account.
INTO v_cdr_charges
FROM NL_es_cdr_bill_invoice
WHERE account_id = v_child_account_id AND
bill_cycle_date = v_bill_date;
v_cdr_discount := (v_cdr_charges * v_total_discount) /
v_total_charges;
-- inserting the calculated discount amount of a child account(CDR) into NL_es_discount_split
INSERT INTO NL_es_discount_split
(account_id,
home_sys_account_id,
line_of_business,
discount_date,
discount_id,
es_account_id,
discount_amount,
disc_post_date,
discount_app_date,
logged_date)
VALUES
(v_child_account_id,
v_home_sys_account_id,
v_line_of_business,
v_date,
v_discount_id,
v_parent_account_id,
v_cdr_discount,
NULL,
v_bill_date,
SYSDATE);
EXCEPTION
WHEN no_data_found THEN
NULL;
WHEN OTHERS THEN
v_errmsg := 'CDR Charges for ES child account no:' ||
v_child_account_id || 'ES parent account no:' ||
v_parent_account_id || ' ' || SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Discount Split', v_errcode, v_errmsg, SYSDATE);
COMMIT;
END;
COMMIT;
END LOOP;
CLOSE cur_discount_details;
-- closing of the cursor cur_es_child_accounts.
END LOOP;
UPDATE NL_es_discount
SET is_split = 1
WHERE es_account_id = v_parent_account_id AND
bill_cycle_date = v_bill_date;
COMMIT;
CLOSE cur_es_child_accounts;
--for logging the exceptions.
EXCEPTION
WHEN dup_val_on_index THEN
ROLLBACK;
v_errmsg := 'ES child account no:' || v_child_account_id ||
'ES parent account no:' || v_parent_account_id || ' ' ||
SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Discount Split ', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
v_errmsg := 'ES child account no:' || v_child_account_id ||
'ES parent account no:' || v_parent_account_id || ' ' ||
SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Discount Split', v_errcode, v_errmsg, SYSDATE);
COMMIT;
END sp_es_discount_split; --end of the procedure sp_es_discount_split
==========SP_FIND_DIFFERENCE
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_find_difference(p_parent_account_id IN VARCHAR2)
AS
v_parent_account_id VARCHAR2(20);
v_account_id VARCHAR2(20);
v_home_sys_account_id VARCHAR2(20);
v_is_active NUMBER;
v_charges NUMBER;
v_payments NUMBER;
v_outstanding_at_parent NUMBER;
v_max_invoice NUMBER;
v_outsanding_at_hbs NUMBER;
v_diff NUMBER := 0;
v_total_diff NUMBER := 0;
CURSOR cur_get_accounts IS
SELECT parent_account_id, account_id, home_sys_account_id, is_active
FROM NL_es_home_sys_account
WHERE parent_account_id IN
(
p_parent_account_id
)
;
=====================================
BEGIN
FOR rec_get_accounts IN cur_get_accounts LOOP
v_parent_account_id := rec_get_accounts.parent_account_id;
v_account_id := rec_get_accounts.account_id;
v_home_sys_account_id := rec_get_accounts.home_sys_account_id;
v_is_active := rec_get_accounts.is_active;
SELECT nvl(SUM(one_off_number), 0)
INTO v_charges
FROM custoverrideprice@cdr_nz c
WHERE customer_ref = v_account_id;
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_payments
FROM accountpayment@cdr_nz a
WHERE a.account_num = v_home_sys_account_id AND
account_payment_ref IN
(SELECT transaction_id
FROM NL_es_payment_split
WHERE account_id = v_account_id) AND failed_dtm IS NULL;
v_outstanding_at_parent := v_charges - v_payments;
SELECT nvl(MAX(invoice_seq_no), 0)
INTO v_max_invoice
FROM NL_es_cdr_bill_invoice bc
WHERE account_id = v_account_id AND is_posted = 1;
BEGIN
SELECT nvl(outstanding_bal, 0)
INTO v_outsanding_at_hbs
FROM NL_es_cdr_bill_invoice bc
WHERE account_id = v_account_id AND invoice_seq_no = v_max_invoice;
EXCEPTION
WHEN no_data_found THEN
v_outsanding_at_hbs := 0;
END;
IF (v_is_active = 5 OR v_outsanding_at_hbs < 0) THEN
v_outsanding_at_hbs := 0;
END IF;
IF abs(v_outstanding_at_parent - v_outsanding_at_hbs) > 2000 THEN
v_diff := v_outstanding_at_parent - v_outsanding_at_hbs;
INSERT INTO NL_es_diff
(parent_account_id,
account_id,
home_sys_account_id,
is_active,
payments,
outsanding_at_hbs,
outstanding_at_parent,
difference,
charges,
logged_date)
VALUES
(v_parent_account_id,
v_account_id,
v_home_sys_account_id,
v_is_active,
v_payments,
v_outsanding_at_hbs,
v_outstanding_at_parent,
v_outstanding_at_parent - v_outsanding_at_hbs,
v_charges,
SYSDATE);
ELSE
v_diff := 0;
END IF;
v_total_diff := v_total_diff + v_diff;
/*dbms_output.put_line('total difference for parent is'||v_total_diff);*/
END LOOP;
if(v_total_diff is not null ) then
UPDATE NL_es_diff
SET diffference_at_parent = v_total_diff
WHERE parent_account_id = v_parent_account_id;
else
dbms_output.put_line('error for parent'||v_parent_account_id);
end if;
COMMIT;
v_total_diff := 0;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(v_account_id || ' ' || SQLERRM);
END;
========= SP_GEN_ACCOUNTING_FEED
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_gen_accounting_feed IS
-- Author :Sumit Arora(258075)
-- Created Date :March 25,2009 9.59 AM
-- The procedure generates an accounting feed for the ES accounts
-- Only the excess payments are available in the feed
v_filehandle utl_file.file_type;
--v_es_child_account_id NL_es_cust_acq_response.es_child_account_id%TYPE := NULL;
--v_home_sys_ext_id NL_es_home_sys_account.home_sys_external_id%TYPE := NULL;
v_payment_amount NL_es_payment_split.payment_amount%TYPE := NULL;
v_amount NUMBER(18, 3);
v_amount1 VARCHAR2(20);
v_implied_decimel NL_es_parameters.int_value%TYPE := NULL;
v_rcv_ssa_code NL_es_payment.rcv_ssa_code%TYPE := NULL;
--v_owning_ssa NL_es_home_sys_account.ssa%TYPE := NULL;
v_owning_circle NL_es_home_sys_account.circle%TYPE := NULL;
--v_owning_ssa_code NL_ssa_map.ssa_code%TYPE := NULL;
v_owning_ssa_code NL_es_payment.rcv_ssa_code%TYPE;
v_owning_circle_code NL_ssa_circle_map.circle_name%TYPE := NULL;
--v_owning_ssa_id NL_ssa_map.ssa_id%TYPE := NULL;
-- v_owning_circle_id NL_ssa_map.circle_id%TYPE := NULL;
v_lob VARCHAR2(3);
v_gl_posting_date VARCHAR2(12);
filename VARCHAR2(50);
v_sn NUMBER(10) := 0;
v_err_num NUMBER;
v_err_msg VARCHAR2(100);
v_feed_no VARCHAR2(14);
v_owning_zone CONSTANT VARCHAR2(14) := 'W';
-- NL_ssa_map.zone_name%TYPE := NULL;
v_rcv_circle_code NL_ssa_circle_map.circle_name%TYPE := NULL;
v_rcv_zone CONSTANT VARCHAR2(14) := 'W';
v_payment_type NL_es_payment_split.payment_type%TYPE := NULL;
v_count NUMBER(10) := 0;
v_last_run_dtm DATE;
CURSOR cur_excess_payment IS
SELECT p.rcv_ssa_code, ep.payment_type, SUM(ep.remaining_amount)
FROM NL_es_excess_payment ep INNER JOIN NL_es_payment p ON ep.transaction_id = p.transaction_id AND ep.payment_type = p.payment_type
WHERE ep.excess_trans_date >= /*v_last_run_dtm*/'1-jan-2012'
--and EP.TRANSACTION_ID LIKE 'JAL%'
GROUP BY p.rcv_ssa_code, ep.payment_type
--HAVING SUM(ep.remaining_amount) <> 0
;
BEGIN
SELECT to_date(char_value, 'MM/DD/YYYY hh:mi:ss AM')
INTO v_last_run_dtm
FROM NL_es_parameters
WHERE TRIM(lower(parameter_name)) = 'last_accounting_feed' AND
TRIM(upper(module_name)) = 'AF';
UPDATE NL_es_parameters
SET char_value = to_char(SYSDATE, 'MM/DD/YYYY hh:mi:ss AM')
WHERE TRIM(lower(parameter_name)) = 'last_accounting_feed' AND
TRIM(upper(module_name)) = 'AF';
COMMIT;
SELECT int_value
INTO v_implied_decimel
FROM NL_es_parameters b
WHERE b.module_name = 'ES' AND b.parameter_name = 'Implied Decimel';
SELECT to_char(SYSDATE, 'dd.mm.yyyy') INTO v_gl_posting_date FROM dual;
SELECT to_char(SYSDATE, 'DDMMYYYYHH24MISS') INTO v_feed_no FROM dual;
OPEN cur_excess_payment;
LOOP
FETCH cur_excess_payment
INTO v_rcv_ssa_code, v_payment_type, v_payment_amount;
EXIT WHEN cur_excess_payment%NOTFOUND;
IF v_count = 0 THEN
--filename will be in the format es_payment_DDMMYYYY.csv
filename := 'es_accounting_' ||
to_char(SYSDATE, 'DDMMYYYYHH24MISS') || '.csv';
v_filehandle := utl_file.fopen('ACCOUNTING_DIR', filename, 'w');
END IF;
v_sn := v_sn + 1;
v_amount := v_payment_amount / (10 ** v_implied_decimel);
SELECT TRIM(to_char(v_amount, '9999999999999999.99'))
INTO v_amount1
FROM dual;
v_lob := 'EP';
v_rcv_ssa_code := TRIM(' ' FROM v_rcv_ssa_code);
--v_owning_ssa_id := 120;
SELECT circle_name
INTO v_owning_circle_code
FROM NL_ssa_circle_map bp
WHERE bp.ssa_name = v_rcv_ssa_code;
v_rcv_circle_code := v_owning_circle_code;
/* SELECT m.zone_name, m.circle_code, m.ssa_code
INTO v_owning_zone, v_owning_circle_code, v_owning_ssa_code
FROM NL_ssa_map m
WHERE m.ssa_id = v_owning_ssa_id;
SELECT p.zone_name, p.circle_code
INTO v_rcv_zone, v_rcv_circle_code
FROM NL_ssa_map p
WHERE p.ssa_code = v_rcv_ssa_code;*/
IF v_payment_type = 'REV' THEN
v_amount1 := '-' || v_amount1;
END IF;
utl_file.putf(v_filehandle,
v_sn || ',' || 'EZ' || ',' || to_char(v_feed_no) || ',' ||
v_gl_posting_date || ',' || v_rcv_zone || ',' ||
v_rcv_circle_code || ',' || v_rcv_ssa_code || ',' ||
v_rcv_zone || ',' || v_rcv_circle_code || ',' ||
v_rcv_ssa_code || ',' || v_lob || ',' || 'N' || ',' ||
to_char(v_amount1));
utl_file.new_line(v_filehandle);
v_count := v_count + 1;
END LOOP;
COMMIT;
IF utl_file.is_open(v_filehandle) THEN
--closing the filehandle for the feed file
utl_file.fclose(v_filehandle);
END IF;
EXCEPTION
WHEN no_data_found THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Accounting Feed generation :' || filename ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Accounting feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.invalid_path THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Accounting Feed generation :, Invalid Path:' ||
filename || substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Accounting feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.invalid_mode THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Accounting Feed generation ::, Invalid Mode:' ||
filename || substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Accounting feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.invalid_filehandle THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Accounting Feed generation :,Invalid Filehandle:' ||
filename || substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Accounting feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.invalid_operation THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Accounting Feed generation :, Invalid Operation:' ||
filename || substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Accounting feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.read_error THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Accounting Feed generation :, Read Error:' ||
filename || substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Accounting feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.write_error THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Accounting Feed generation ::, Write Error:' ||
filename || substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Accounting feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.internal_error THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Accounting Feed generation :, Internal Error:' ||
filename || substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Accounting feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Accounting Feed generation :' || filename ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Accounting feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
--end of sp_gen_accounting_feed procedure
END sp_gen_accounting_feed;
========SP_GEN_CHILD_LEDGER
CREATE OR REPLACE PROCEDURE ESTAPLE.SP_GEN_CHILD_LEDGER(P_SSA VARCHAR2) IS
V_CHARGES NUMBER;
V_AMT_PAID NUMBER;
V_AMT_REV NUMBER;
V_OUTSTANDING NUMBER;
V_ACCOUNT_ID VARCHAR2(20);
V_HOME_SYS_ACCOUNT_ID VARCHAR2(20);
V_PARENT_ID VARCHAR2(20);
V_IS_ACTIVE NUMBER;
V_INVOICE_SEQ NUMBER;
CURSOR CUR_ACC IS
SELECT ACCOUNT_ID,
HOME_SYS_ACCOUNT_ID,
PARENT_ACCOUNT_ID,
IS_ACTIVE,
INVOICE_SEQ_NO
FROM NL_ES_HOME_SYS_ACCOUNT
WHERE SSA = P_SSA;
BEGIN
FOR REC_ACC IN CUR_ACC LOOP
V_ACCOUNT_ID := REC_ACC.ACCOUNT_ID;
V_HOME_SYS_ACCOUNT_ID := REC_ACC.HOME_SYS_ACCOUNT_ID;
V_PARENT_ID := REC_ACC.PARENT_ACCOUNT_ID;
V_IS_ACTIVE := REC_ACC.IS_ACTIVE;
V_INVOICE_SEQ := REC_ACC.INVOICE_SEQ_NO;
BEGIN
SELECT NVL(SUM(ONE_OFF_NUMBER), 0)
INTO V_CHARGES
FROM CUSTOVERRIDEPRICE@CDR_NZ
WHERE CUSTOMER_REF = V_ACCOUNT_ID;
SELECT NVL(SUM(S.PAYMENT_AMOUNT), 0)
INTO V_AMT_PAID
FROM NL_ES_PAYMENT_SPLIT S
WHERE S.ACCOUNT_ID = V_ACCOUNT_ID AND S.PAYMENT_TYPE = 'APP';
SELECT NVL(SUM(S.PAYMENT_AMOUNT), 0)
INTO V_AMT_REV
FROM NL_ES_PAYMENT_SPLIT S
WHERE S.ACCOUNT_ID = V_ACCOUNT_ID AND S.PAYMENT_TYPE = 'REV';
CASE V_IS_ACTIVE
WHEN 1 THEN
BEGIN
SELECT NVL(I.OUTSTANDING_BAL, 0)
INTO V_OUTSTANDING
FROM NL_ES_CDR_BILL_INVOICE I
WHERE I.ACCOUNT_ID = V_ACCOUNT_ID AND
I.INVOICE_SEQ_NO = V_INVOICE_SEQ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_OUTSTANDING :=0;
END;
WHEN 5 THEN
V_OUTSTANDING := 0;
WHEN 4 THEN
BEGIN
SELECT NVL(I.OUTSTANDING_BAL, 0)
INTO V_OUTSTANDING
FROM NL_ES_CDR_BILL_INVOICE I
WHERE I.ACCOUNT_ID = V_ACCOUNT_ID AND
I.INVOICE_SEQ_NO = V_INVOICE_SEQ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_OUTSTANDING :=0;
END;
END CASE;
IF (V_OUTSTANDING < 0) THEN
V_OUTSTANDING := 0;
END IF;
INSERT INTO NL_ES_CHILD_LEDGER
VALUES
(V_ACCOUNT_ID,
V_HOME_SYS_ACCOUNT_ID,
V_PARENT_ID,
V_CHARGES,
V_AMT_PAID,
V_AMT_REV,
V_OUTSTANDING,
(V_CHARGES - V_AMT_PAID + V_AMT_REV) - V_OUTSTANDING,
V_IS_ACTIVE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(REC_ACC.ACCOUNT_ID|| SQLERRM);
END;
END LOOP;
END SP_GEN_CHILD_LEDGER;
===========SP_GEN_CUST_ACQ_FEED
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_gen_cust_acq_feed IS
--version 1.0
--Author: 230328
--Purpose: To generates feed file for CA response to CRM
--It uses the table NL_es_cust_acq_response in ES database to fetch the details for this purpose
-- Variable Declaration
--v_filehandle utl_file.file_type;
v_es_account_id NL_es_home_sys_account.account_id%TYPE;
v_cust_acq_status NL_es_cust_acq_response.cust_acq_status%TYPE;
v_es_child_account_id NL_es_cust_acq_response.es_child_account_id%TYPE;
--v_es_ext_id NL_es_cust_acq_response.external_es_account_id%TYPE;
--v_es_child_ext_id NL_es_cust_acq_response.external_es_child_account_id%TYPE;
v_status_desc NL_es_cust_acq_response.status_desc%TYPE;
--filename VARCHAR2(50);
v_status VARCHAR2(20);
v_err_num NUMBER;
v_err_msg VARCHAR2(570);
v_mandt sapsr3.zestap_feed.mandt@crm_db_bc7%TYPE := NULL;
v_acq_date sapsr3.zestap_feed.timestamped@crm_db_bc7%TYPE := NULL;
--v_count NUMBER(10) := 0;
CURSOR cur_accounts IS
SELECT ca.es_account_id,
ca.es_child_account_id,
ca.cust_acq_status,
ca.status_desc,
to_char(ca.acq_date, 'DDMMYYYY HH24:MI:SS:FF3') acq_date
FROM NL_es_cust_acq_response ca
WHERE (ca.is_feed_sent IS NULL OR ca.is_feed_sent = 0) ;
BEGIN
v_mandt := '400';
--open cursor cur_accounts
OPEN cur_accounts;
LOOP
FETCH cur_accounts
INTO v_es_account_id, v_es_child_account_id, v_cust_acq_status, v_status_desc, v_acq_date;
EXIT WHEN cur_accounts%NOTFOUND;
/*IF v_count = 0 THEN
--filename will be in the format eStapling_DDMMYYHHMISS.txt
filename := 'eStapling_' || to_char(SYSDATE, 'DDMMYYHHMISS') ||
'.txt';
v_filehandle := utl_file.fopen('CRM_DIR', filename, 'a');
END IF;*/
IF v_es_child_account_id IS NULL THEN
v_es_child_account_id := ' ';
END IF;
CASE v_cust_acq_status
WHEN 1 THEN
v_status := 'Active';
WHEN -1 THEN
v_status := 'Inactive';
WHEN 0 THEN
-- v_status := 'Active';
v_status := 'Inactive';
END CASE;
/*Inserting the customer acquisition info in the feed file
The info will be in the format:-
es_parent_account_id (CRM generated id)
es_child_account_id( CRM generated child es id)
status- Inactive or Active
status description
The delimiter will be comma(,)*/
/*utl_file.putf(v_filehandle,
v_es_account_id || ',' || v_es_child_account_id || ',' ||
v_status || ',' || v_status_desc);
utl_file.new_line(v_filehandle);
v_count := v_count + 1;*/
INSERT INTO sapsr3.zestap_feed@crm_db_bc7
(mandt, p_bill, c_bill, status, descr, timestamped)
VALUES
(v_mandt,
v_es_account_id,
v_es_child_account_id,
v_status,
v_status_desc,
v_acq_date);
UPDATE NL_es_cust_acq_response r
SET r.is_feed_sent = 1
WHERE r.es_account_id = v_es_account_id AND
r.es_child_account_id = v_es_child_account_id AND
(r.is_feed_sent IS NULL OR r.is_feed_sent = 0);
END LOOP;
COMMIT;
/*IF utl_file.is_open(v_filehandle) THEN
--closing the filehandle for the feed file
utl_file.fclose(v_filehandle);
END IF;*/
--Exception Handling
EXCEPTION
WHEN no_data_found THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Feed generation for Cust acq:' ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Cust Acq Feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Feed generation for Cust acq:' ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Cust Acq Feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
--end of sp_es_cust_acq_feed procedure
END sp_gen_cust_acq_feed;
==========SP_GEN_CUST_ACQ_FEED_ADD_DEL
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_gen_cust_acq_feed_add_del AS
--version 1.0
--created by - Sumit Arora(258075)
--purpose - to Give Added or deleted account response to CRM
v_parent_account_id NL_es_account.account_id%TYPE;
v_child_account_id NL_es_add_del_accounts.child_account_id%TYPE;
v_home_child_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_action_code NL_es_add_del_accounts.action_code%TYPE;
v_order_id NL_es_add_del_accounts.order_id%TYPE;
v_is_action NL_es_add_del_accounts.is_action%TYPE;
v_status_desc NL_es_add_del_accounts.status_description%TYPE;
v_status sapsr3.zestap_add_del.status@crm_db_bc7%TYPE;
v_err_num NUMBER;
v_err_msg VARCHAR2(570);
v_action VARCHAR2(50);
v_date sapsr3.zestap_feed.timestamped@crm_db_bc7%TYPE := NULL;
CURSOR cur_add_del_accounts IS
SELECT ad.parent_account_id,
ad.child_account_id,
ad.action_code,
ad.order_id,
ad.is_action,
ad.status_description
FROM NL_es_add_del_accounts ad
WHERE order_id NOT IN
(SELECT zextra2 FROM sapsr3.zestap_feed@crm_db_bc7) AND
is_feed_sent = 0;
BEGIN
FOR rec_add_del_accounts IN cur_add_del_accounts LOOP
v_parent_account_id := rec_add_del_accounts.parent_account_id;
v_child_account_id := rec_add_del_accounts.child_account_id;
v_is_action := rec_add_del_accounts.is_action;
v_action_code := rec_add_del_accounts.action_code;
v_order_id := rec_add_del_accounts.order_id;
v_status_desc := rec_add_del_accounts.status_description;
v_date := to_char(systimestamp, 'DDMMYYYY HH24:MI:SS:FF3');
-- EXIT WHEN cur_add_del_accounts%NOTFOUND;
CASE v_action_code
WHEN '01' THEN
CASE v_is_action
WHEN -1 THEN
v_status := 'Inactive';
WHEN 1 THEN
v_status := 'Active';
END CASE;
v_action := 'ADD';
WHEN '02' THEN
CASE v_is_action
WHEN 1 THEN
v_status := 'Inactive';
WHEN -1 THEN
v_status := 'Active';
END CASE;
v_action := 'DELETE';
DELETE FROM sapsr3.zestap_ser@crm_db_bc7
WHERE p_bill = v_parent_account_id AND c_bill = v_child_account_id;
END CASE;
INSERT INTO sapsr3.zestap_feed@crm_db_bc7
(mandt, p_bill, c_bill, status, descr, zextra1, zextra2, timestamped)
VALUES
(400,
v_parent_account_id,
v_child_account_id,
v_status,
v_status_desc,
v_action,
v_order_id,
v_date);
UPDATE NL_es_add_del_accounts bad
SET bad.is_feed_sent = 1
WHERE bad.parent_account_id = v_parent_account_id AND
bad.child_account_id = v_child_account_id AND
bad.order_id = v_order_id;
UPDATE NL_ES_CUST_ACQ_RESPONSE R
SET R.IS_FEED_SENT = 1
WHERE R.ES_ACCOUNT_ID = V_PARENT_ACCOUNT_ID AND
R.ES_CHILD_ACCOUNT_ID = V_CHILD_ACCOUNT_ID AND
(R.IS_FEED_SENT IS NULL OR R.IS_FEED_SENT = 0);
END LOOP;
COMMIT;
EXCEPTION
WHEN no_data_found THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Feed generation for Cust acq add del:' ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Cust Acq Feed Add Del', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Cust Acq Feed Add Del', v_err_msg, SYSDATE, v_err_num);
COMMIT;
END sp_gen_cust_acq_feed_add_del;
========SP_GEN_ES_BILLING_ADD_DEL_FEED
CREATE OR REPLACE PROCEDURE ESTAPLE.SP_GEN_ES_BILLING_ADD_DEL_FEED AS
--version 1.0
--created by - Abhilasha Saxena (313274)
--purpose -- To Give newly added and deleted account details to billing
V_FILEHANDLE UTL_FILE.FILE_TYPE;
FILENAME_FEED VARCHAR2(50);
--fetches the account details
CURSOR CUR_ES_BILL_FEED IS
SELECT B.PARENT_ACCOUNT_ID,
B.CHILD_ACCOUNT_ID,
B.HOME_SYS_ACCOUNT_ID,
B.ACTION_CODE,
B.REQUEST_DATE
FROM NL_ES_ADD_DEL_ACCOUNTS B;
V_PARENT_ACCOUNT_ID NL_ES_ADD_DEL_ACCOUNTS.PARENT_ACCOUNT_ID%TYPE;
V_HOME_SYS_ID NL_ES_ADD_DEL_ACCOUNTS.HOME_SYS_ACCOUNT_ID%TYPE;
V_ACTION_CODE NL_ES_ADD_DEL_ACCOUNTS.ACTION_CODE%TYPE;
V_REQUEST_DT VARCHAR2(12); -- NL_es_add_del_accounts.request_date%TYPE;
V_COUNT NUMBER;
V_IMPLIED_DECIMEL NL_ES_PARAMETERS.INT_VALUE%TYPE;
V_ERRCODE VARCHAR2(500);
V_ERRMSG VARCHAR2(500);
BEGIN
--initializing the feed file line counter
V_COUNT := 0;
SELECT BP.INT_VALUE
INTO V_IMPLIED_DECIMEL
FROM NL_ES_PARAMETERS BP
WHERE BP.MODULE_NAME LIKE '%ES%' AND
BP.PARAMETER_NAME = 'Implied Decimel';
FOR REC_BILL_FEED IN CUR_ES_BILL_FEED LOOP
V_PARENT_ACCOUNT_ID := REC_BILL_FEED.PARENT_ACCOUNT_ID;
V_HOME_SYS_ID := REC_BILL_FEED.HOME_SYS_ACCOUNT_ID;
V_ACTION_CODE := REC_BILL_FEED.ACTION_CODE;
V_REQUEST_DT := TO_CHAR(REC_BILL_FEED.REQUEST_DATE, 'DD-MM-YYYY');
IF V_ACTION_CODE = '01' THEN
V_ACTION_CODE := 'A';
ELSE
V_ACTION_CODE := 'D';
END IF;
IF V_COUNT = 0 THEN
--the name of the file which will store payment feed
FILENAME_FEED := ' NL_ES_' || TO_CHAR(SYSDATE, 'DDMMYYYYHH24MISS') ||
'.txt';
V_FILEHANDLE := UTL_FILE.FOPEN('ES_BILLING_DIR', FILENAME_FEED, 'a');
END IF;
UTL_FILE.PUTF(V_FILEHANDLE,
V_PARENT_ACCOUNT_ID || ',' || V_HOME_SYS_ID || ',' ||
V_REQUEST_DT || ',' || V_ACTION_CODE);
UTL_FILE.NEW_LINE(V_FILEHANDLE);
V_COUNT := V_COUNT + 1;
COMMIT;
END LOOP;
--closing the filehandle for the feed file
UTL_FILE.FCLOSE(V_FILEHANDLE);
--exception handling
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN CASE_NOT_FOUND THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN CURSOR_ALREADY_OPEN THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN INVALID_CURSOR THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN TOO_MANY_ROWS THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
END SP_GEN_ES_BILLING_ADD_DEL_FEED;
=========SP_GEN_ES_BILLING_FEED
CREATE OR REPLACE PROCEDURE ESTAPLE.SP_GEN_ES_BILLING_FEED(P_BILL_CYCLE_DATE IN DATE) AS
V_FILEHANDLE UTL_FILE.FILE_TYPE;
FILENAME_FEED VARCHAR2(50);
--fetches the account details
CURSOR CUR_ES_BILLING_FEED IS
SELECT B.PARENT_ACCOUNT_ID, B.HOME_SYS_ACCOUNT_ID, B.IS_ACTIVE
FROM NL_ES_HOME_SYS_ACCOUNT B
WHERE B.IS_ACTIVE <> 2 AND B.ENROLLMENT_DATE < P_BILL_CYCLE_DATE
ORDER BY B.IS_ACTIVE DESC;
V_PARENT_ACCOUNT_ID NL_ES_HOME_SYS_ACCOUNT.PARENT_ACCOUNT_ID%TYPE;
V_HOME_ACC_ID NL_ES_HOME_SYS_ACCOUNT.HOME_SYS_ACCOUNT_ID%TYPE;
V_COUNT NUMBER;
V_IMPLIED_DECIMEL NL_ES_PARAMETERS.INT_VALUE%TYPE;
V_ERRCODE VARCHAR2(500);
V_ERRMSG VARCHAR2(500);
V_EFFECTIVE_DT VARCHAR2(10);
V_ACTION_CODE VARCHAR2(10);
HBS_COUNT NUMBER;
BEGIN
--initializing the feed file line counter
V_COUNT := 0;
V_EFFECTIVE_DT := TO_CHAR(SYSDATE, 'DD-MM-YYYY');
/* v_action_code := 'A';*/
SELECT BP.INT_VALUE
INTO V_IMPLIED_DECIMEL
FROM NL_ES_PARAMETERS BP
WHERE BP.MODULE_NAME LIKE '%ES%' AND
BP.PARAMETER_NAME = 'Implied Decimel';
FOR REC_BILLING_FEED IN CUR_ES_BILLING_FEED LOOP
V_PARENT_ACCOUNT_ID := REC_BILLING_FEED.PARENT_ACCOUNT_ID;
V_HOME_ACC_ID := REC_BILLING_FEED.HOME_SYS_ACCOUNT_ID;
V_ACTION_CODE := REC_BILLING_FEED.IS_ACTIVE;
HBS_COUNT := 0;
IF V_ACTION_CODE <> 1 THEN
SELECT COUNT(*)
INTO HBS_COUNT
FROM NL_ES_HOME_SYS_ACCOUNT H
WHERE H.IS_ACTIVE = 1 AND H.HOME_SYS_ACCOUNT_ID = V_HOME_ACC_ID;
END IF;
IF HBS_COUNT = 0 THEN
CASE V_ACTION_CODE
WHEN 1 THEN
V_ACTION_CODE := 'A';
WHEN 4 THEN
V_ACTION_CODE := 'D';
WHEN 5 THEN
V_ACTION_CODE := 'D';
END CASE;
IF V_COUNT = 0 THEN
--the name of the file which will store payment feed
FILENAME_FEED := ' NL_ES_' || TO_CHAR(SYSDATE, 'DDMMYYYYHH24MISS') ||
'.txt';
V_FILEHANDLE := UTL_FILE.FOPEN('ES_BILLING_FEED',
FILENAME_FEED,
'a');
END IF;
UTL_FILE.PUTF(V_FILEHANDLE,
V_PARENT_ACCOUNT_ID || ',' || V_HOME_ACC_ID || ',' ||
V_EFFECTIVE_DT || ',' || V_ACTION_CODE);
UTL_FILE.NEW_LINE(V_FILEHANDLE);
V_COUNT := V_COUNT + 1;
END IF;
COMMIT;
END LOOP;
--closing the filehandle for the feed file
UTL_FILE.FCLOSE(V_FILEHANDLE);
--exception handling
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN CASE_NOT_FOUND THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN CURSOR_ALREADY_OPEN THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN INVALID_CURSOR THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN TOO_MANY_ROWS THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('BILLING ES Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
END SP_GEN_ES_BILLING_FEED;
========SP_GEN_ES_PMS_FEED
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_gen_es_pms_feed (P_bill_cycle_date IN DATE)
AS
--version 1.0
--created by - Abhilasha Saxena(313274)
--Purpose - To give HBS Details to PMS to block HBS payments
v_filehandle utl_file.file_type;
filename_feed VARCHAR2(50);
--fetches the ES invoice details
CURSOR cur_es_pms_feed IS
SELECT --b.account_id,
b.home_sys_account_id
FROM NL_es_home_sys_account b
WHERE b.is_active NOT IN (3, 4, 5) AND
/* b.home_sys_account_id not in
('1004854941', '1004855929', '1004854943', '1004780473',
'1004886892', '1004845862', '1004862513')*/
b.enrollment_date <P_bill_cycle_date;
-- v_parent_account_id NL_es_home_sys_account.account_id%TYPE;
v_home_acc_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_count NUMBER;
v_implied_decimel NL_es_parameters.int_value%TYPE;
v_errcode VARCHAR2(500);
v_errmsg VARCHAR2(500);
BEGIN
--initializing the feed file line counter
v_count := 0;
SELECT bp.int_value
INTO v_implied_decimel
FROM NL_es_parameters bp
WHERE bp.module_name LIKE '%ES%' AND
bp.parameter_name = 'Implied Decimel';
FOR rec_pms_feed IN cur_es_pms_feed LOOP
v_home_acc_id := rec_pms_feed.home_sys_account_id;
IF v_count = 0 THEN
--the name of the file which will store payment feed
filename_feed := 'es_account_' ||
to_char(SYSDATE, 'DDMMYYYYHH24MISS') || '.csv';
v_filehandle := utl_file.fopen('PMS_HBS_BLOCKING_DIR',
filename_feed,
'a');
END IF;
utl_file.putf(v_filehandle, v_home_acc_id);
utl_file.new_line(v_filehandle);
v_count := v_count + 1;
COMMIT;
END LOOP;
--closing the filehandle for the feed file
utl_file.fclose(v_filehandle);
--exception handling
EXCEPTION
WHEN no_data_found THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS ES Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN case_not_found THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS ES Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN cursor_already_open THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS ES Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN dup_val_on_index THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS ES Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN invalid_cursor THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS ES Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN too_many_rows THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS ES Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS ES Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
END sp_gen_es_pms_feed;
===========SP_GEN_ES_HBS_PAYMENT_FEED
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_gen_hbs_payment_feed AS
v_parent_account_id NL_es_home_sys_account.parent_account_id%TYPE;
v_payment_amount NL_es_hbs_payment.payment_amount%TYPE;
v_count NUMBER;
v_filehandle utl_file.file_type;
v_date VARCHAR2(20);
filename VARCHAR2(50);
v_implied_decimel NL_es_parameters.int_value%TYPE := NULL;
v_batch_no VARCHAR2(20);
v_batch_date VARCHAR2(12);
v_transaction_id1 TIMESTAMP;
v_transaction_id VARCHAR2(50);
v_transaction_id2 VARCHAR2(50);
v_ssa_code VARCHAR2(20);
v_bank_name VARCHAR2(20);
v_instrument_no VARCHAR2(20);
v_micr_code VARCHAR2(20);
v_reversal_type VARCHAR2(20);
v_reason_code_name VARCHAR2(20);
v_late_payment_flag VARCHAR2(20);
v_payment_method VARCHAR2(6) := 'PDAL';
v_paymnet_date VARCHAR2(12);
v_batch_timestamp VARCHAR2(20);
v_channel_name VARCHAR2(9) := 'EStapling';
v_remarks VARCHAR2(9) := 'EStapling';
v_currency_code VARCHAR2(20) := 'INR';
v_deposit_id VARCHAR2(3) := '000';
v_payment_type CONSTANT VARCHAR2(10) := 'Normal';
CURSOR cur_get_hbs_payments IS
SELECT b.parent_account_id, SUM(payment_amount) AS payment_mny
FROM NL_es_hbs_payment bh INNER JOIN NL_es_home_sys_account b ON b.account_id = bh.account_id
GROUP BY b.parent_account_id;
BEGIN
v_count := 0;
v_date := to_char(SYSDATE, 'DDMMYYYYHH24MISS');
SELECT bp.int_value
INTO v_implied_decimel
FROM NL_es_parameters bp
WHERE bp.module_name LIKE '%ES%' AND
bp.parameter_name = 'Implied Decimel';
FOR rec_get_hbs_payments IN cur_get_hbs_payments LOOP
v_parent_account_id := rec_get_hbs_payments.parent_account_id;
v_payment_amount := rec_get_hbs_payments.payment_mny;
IF (v_count = 0) THEN
filename := 'ES_DUMMY_PAYMENT_' || v_date || '.txt';
v_filehandle := utl_file.fopen('PMS_ES_INVOICE_DIR', filename, 'w');
END IF;
SELECT systimestamp INTO v_transaction_id1 FROM dual;
SELECT to_char(v_transaction_id1, 'DDMMYYYYHH24MISSFF5')
INTO v_transaction_id2
FROM dual;
v_payment_amount := v_payment_amount / (10 ** v_implied_decimel);
v_transaction_id := 'ES' || v_transaction_id2;
SELECT to_char(v_transaction_id1, 'DDMMYYYYHH24MISS')
INTO v_batch_timestamp
FROM dual;
SELECT to_char(SYSDATE, 'DD-MON-YYYY') INTO v_batch_date FROM dual;
v_batch_no := 'BID' || v_batch_timestamp;
SELECT ssa
INTO v_ssa_code
FROM NL_es_account ea
WHERE ea.account_id = v_parent_account_id;
--v_ssa_code := pkg_es.ssa_code_es_parent;
utl_file.putf(v_filehandle,
TRIM(' ' FROM v_batch_no) || ',' ||
TRIM(' ' FROM v_batch_date) || ',' || '18-MAY-2010' || ',' ||
v_parent_account_id || ',' || v_payment_amount || ',' ||
TRIM(' ' FROM v_transaction_id) || ',' ||
TRIM(' ' FROM v_ssa_code) || ',' ||
TRIM(' ' FROM v_bank_name) || ',' ||
TRIM(' ' FROM v_instrument_no) || ',' ||
TRIM(' ' FROM v_micr_code) || ',' ||
TRIM(' ' FROM v_currency_code) || ',' || v_payment_method || ',' ||
v_payment_type || ',' || TRIM(' ' FROM v_reversal_type) || ',' ||
TRIM(' ' FROM v_reason_code_name) || ',' ||
TRIM(' ' FROM v_late_payment_flag) || ',' ||
TRIM(' ' FROM v_remarks) || ',' || v_channel_name || ',' ||
v_deposit_id);
utl_file.new_line(v_filehandle);
v_count := v_count + 1;
END LOOP;
IF utl_file.is_open(v_filehandle) THEN
--closing the filehandle for the feed file
utl_file.fclose(v_filehandle);
END IF;
END;
==========SP_GEN_MAPPING_DATA
create or replace procedure estaple.sp_gen_mapping_data is
v_cust_ref custeventsource.customer_ref@cdr_nz%TYPE;
v_home_sys accountattributes.home_sys_account_id@cdr_nz%TYPE;
v_account_id accountattributes.account_num@cdr_nz%TYPE;
v_legacy_account accountattributes.legacy_system_account_num@cdr_nz%TYPE;
v_last_bill_date account.last_bill_dtm@cdr_nz%TYPE;
v_parent_id NL_es_home_sys_account.parent_account_id%TYPE;
V_ERR_NUM NUMBER;
V_ERR_MSG VARCHAR2(570);
cursor cur_custref is
select distinct customer_ref
from custeventsource@cdr_nz
where event_source in
('07882222329', '07882221763', '07882221925', '07882221574',
'07882221536', '07882222401', '07882221674', '07882221609',
'07882221754', '07882221737', '07882221824', '07882221816',
'07882221807', '07882222311', '07882221678', '07882295274',
'07882285720', '07882221721');
CURSOR cur_accattrib IS
select account_num, legacy_system_account_num
from accountattributes@cdr_nz
where home_sys_account_id = v_home_sys;
CURSOR cur_account IS
select last_bill_dtm
from account@cdr_nz
where account_num = v_home_sys;
CURSOR cur_parent IS
select parent_account_id
from NL_es_home_sys_account
where account_id = v_account_id;
BEGIN
delete from NL_es_mapping_temp;
for rec_custref in cur_custref LOOP
v_cust_ref := rec_custref.customer_ref;
v_home_sys := v_cust_ref;
for rec_accattrib in cur_accattrib LOOP
v_account_id := rec_accattrib.account_num;
v_legacy_account := rec_accattrib.legacy_system_account_num;
END LOOP;
for rec_parent in cur_parent LOOP
v_parent_id := rec_parent.parent_account_id;
END LOOP;
for rec_account in cur_account LOOP
v_last_bill_date := rec_account.last_bill_dtm;
END LOOP;
insert into NL_es_mapping_temp
(parent_id,
home_sys_account_id,
legacy_sys_account_id,
last_bill_dtm)
values
(v_parent_id, v_home_sys, v_legacy_account, v_last_bill_date);
commit;
END LOOP;
end sp_gen_mapping_data;
======SP_GEN_MISSED_BILL_ACCT_FEED
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_gen_missed_bill_acct_feed(p_bill_cycle_date NL_es_cdr_bill_inv_log.bill_cycle_date%TYPE /*,
P_SSA IN VARCHAR2*/) AS
--version 1.0
--created by - Sumit Arora(258075)
--Purpose - To give parents and accounts whose billls have been missed.
-- Variable Declaration
v_filehandle utl_file.file_type;
v_account_id NL_es_cdr_bill_inv_log.account_id%TYPE;
v_parent_account_id NL_es_cdr_bill_inv_log.parent_account_id%TYPE;
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_date VARCHAR2(20);
filename VARCHAR2(50);
v_count NUMBER(10) := 0;
v_err_num NUMBER;
v_err_msg VARCHAR2(100);
/*cursor cur_accounts to fetch the parent and child accouonts*/
CURSOR cur_accounts IS
SELECT DISTINCT log.parent_account_id,
log.account_id,
home_sys.home_sys_account_id
FROM NL_es_cdr_bill_inv_log log INNER JOIN NL_es_home_sys_account home_sys ON log.account_id = home_sys.account_id
WHERE log.fetch_status = 0 AND log.bill_cycle_date = p_bill_cycle_date AND
is_active = 1;
BEGIN
v_date := to_char(p_bill_cycle_date, 'YYYYMMDD');
--open cursor cur_accounts
OPEN cur_accounts;
LOOP
FETCH cur_accounts
INTO v_parent_account_id, v_account_id, v_home_sys_account_id;
EXIT WHEN cur_accounts%NOTFOUND;
IF v_count = 0 THEN
filename := 'es_missed_bill_acct_' || v_date || '.csv';
v_filehandle := utl_file.fopen('BIF_DIR_MISSED_BILL', filename, 'w');
utl_file.putf(v_filehandle,
'ES_PARENT_ID' || ',' || 'ES_CHILD_ID' || ',' ||
'HBS_ACCOUNT_ID' || ',' || 'BILL_CYCLE_DATE');
utl_file.new_line(v_filehandle);
END IF;
utl_file.putf(v_filehandle,
lpad(TRIM(' ' FROM v_parent_account_id), 10) || ',' ||
lpad(TRIM(' ' FROM v_account_id), 10) || ',' ||
lpad(TRIM(' ' FROM v_home_sys_account_id), 10) || ',' ||
lpad(TRIM(' ' FROM p_bill_cycle_date), 9));
utl_file.new_line(v_filehandle);
v_count := v_count + 1;
END LOOP;
COMMIT;
IF utl_file.is_open(v_filehandle) THEN
--closing the filehandle for the feed file
utl_file.fclose(v_filehandle);
END IF;
--Exception Handling
EXCEPTION
WHEN no_data_found THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in missed bill feed' || filename ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.invalid_path THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in missed bill feed:, Invalid Path:' || filename ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Missed bill feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.invalid_mode THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in missed bill feed, Invalid Mode:' || filename ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Missed bill feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.invalid_filehandle THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in missed bill feed:,Invalid Filehandle:' ||
filename || substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Missed bill feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.invalid_operation THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in missed bill feed, Invalid Operation:' ||
filename || substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Missed bill feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.read_error THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in missed bill feed:, Read Error:' || filename ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Missed bill feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.write_error THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in missed bill feed:, Write Error:' || filename ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Missed bill feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN utl_file.internal_error THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in missed bill feed:, Internal Error:' || filename ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Missed bill feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in missed bill feed:' || filename ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Missed bill feed', v_err_msg, SYSDATE, v_err_num);
COMMIT;
--end of sp_gen_discount_gsm_feed procedure
--insert into NL_es_feed_status (
END sp_gen_missed_bill_acct_feed;
==============SP_GEN_PMS_INVOICE_FEE
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_gen_pms_invoice_feed(p_actual_bill_date DATE) AS
--version 1.0
--created by - Sumit Arora(258075)
--Purpose - To generate invoice feed for Es parent accounts
v_filehandle utl_file.file_type;
filename VARCHAR2(50);
--fetches the ES invoice details
CURSOR cur_pms_feed IS
SELECT c.customer_ref customer_ref,
a.account_num account_num,
ata.account_type account_category,
bi.bill_seq invoice_seq_no,
--bi.invoice_net_mny invoice_amount,
bi.balance_out_mny outstanding_bal,
bi.payment_due_dat payment_due_date,
bi.actual_bill_dtm invoice_date,
b.legacy_system_account_num AS group_id,
ata.circle_code circle_code,
bi.invoice_num,
b.ssa ssa_code
FROM customer@cdr_nz c INNER JOIN account@cdr_nz a ON c.customer_ref = a.customer_ref INNER JOIN NL_es_account b ON a.account_num = b.account_id INNER JOIN accountattributes@cdr_nz ata ON a.account_num = ata.account_num
/*INNER JOIN contact@cdr_nz ct ON c.customer_ref = ct.customer_ref
INNER JOIN contacttype@cdr_nz ctt ON ctt.contact_type_id = ct.contact_type_id*/ INNER JOIN billsummary@cdr_nz bi ON a.account_num = bi.account_num
WHERE
c.parent_customer_ref IS NULL AND
bi.bill_seq > b.last_invoice_seq_no AND
TRIM(upper(ata.account_type)) IN
(pkg_es.account_type_zonal_individual,
pkg_es.account_type_zonal_corporate) AND
TRIM(upper(ata.account_subtype)) = pkg_es.account_subtype_parent AND
bi.bill_status = 1 AND b.ssa <> 'DUMMY' AND
trunc(actual_bill_dtm) =
nvl(p_actual_bill_date, trunc(SYSDATE - 1))
;
v_account_id NL_es_account.account_id%TYPE;
v_customer_ref customer.customer_ref@cdr_nz%TYPE;
v_account_category accountattributes.account_type@cdr_nz%TYPE;
v_customer_name VARCHAR2(85);
v_invoice_seq billsummary.bill_seq@cdr_nz%TYPE;
v_invoice_number billsummary.invoice_num@cdr_nz%TYPE;
v_invoice_amt billsummary.balance_out_mny@cdr_nz%TYPE;
v_outstanding_amt billsummary.balance_out_mny@cdr_nz%TYPE;
v_invoice_date billsummary.actual_bill_dtm@cdr_nz%TYPE;
v_pay_due_date billsummary.payment_due_dat@cdr_nz%TYPE;
v_payment_method paymentmethod.payment_method_name@cdr_nz%TYPE;
v_payment_method_id paymentmethod.payment_method_id@cdr_nz%TYPE;
v_invoice_amount1 NUMBER(18, 2);
v_invoice_amount2 VARCHAR2(20);
v_outstanding_amt1 NUMBER(18, 2);
v_outstanding_amt2 VARCHAR2(20);
v_bank_provided_id VARCHAR2(50);
v_mandate_attr2 VARCHAR2(50);
v_bank_name VARCHAR2(50);
v_branch_micr_code VARCHAR2(50);
v_bank_acc_num VARCHAR2(50);
v_bank_acc_type VARCHAR2(50);
v_circle_code_es_parent VARCHAR2(30);
--pkg_es.circle_code_es_parent;
v_ssa_code_es_parent VARCHAR2(30);
--pkg_es.ssa_code_es_parent;
v_group_id NL_es_account.legacy_system_account_num%TYPE;
--variable used for exception handling
v_errcode NUMBER;
--variable used to store the error message.
v_errmsg VARCHAR2(570);
--v_count variable to track the lines in the PMS feed file.
v_count NUMBER(10);
v_implied_decimel NL_es_parameters.int_value%TYPE := NULL;
BEGIN
--initializing the feed file line counter
v_count := 0;
SELECT bp.int_value
INTO v_implied_decimel
FROM NL_es_parameters bp
WHERE bp.module_name LIKE '%ES%' AND
bp.parameter_name = 'Implied Decimel';
FOR rec_pms_feed IN cur_pms_feed LOOP
v_bank_provided_id := NULL;
v_bank_name := NULL;
v_branch_micr_code := NULL;
v_bank_acc_num := NULL;
v_bank_acc_type := NULL;
v_customer_ref := rec_pms_feed.customer_ref;
v_account_id := rec_pms_feed.account_num;
v_account_category := TRIM(rec_pms_feed.account_category);
/*v_customer_name := TRIM(rec_pms_feed.customer_name);*/
v_invoice_seq := rec_pms_feed.invoice_seq_no;
--v_invoice_amt := rec_pms_feed.invoice_amount;
v_outstanding_amt := rec_pms_feed.outstanding_bal;
v_invoice_date := rec_pms_feed.invoice_date;
v_pay_due_date := rec_pms_feed.payment_due_date;
v_group_id := rec_pms_feed.group_id;
v_circle_code_es_parent := rec_pms_feed.circle_code;
v_ssa_code_es_parent := rec_pms_feed.ssa_code;
v_invoice_number := rec_pms_feed.invoice_num;
--Invoice amount in invoice feed is same as the outsanding
v_invoice_amt := v_outstanding_amt;
SELECT ad.payment_method_id
INTO v_payment_method_id
FROM accountdetails@cdr_nz ad
WHERE account_num = v_account_id
AND END_DAT IS NULL
GROUP BY payment_method_id;
SELECT pm.payment_method_name
INTO v_payment_method
FROM paymentmethod@cdr_nz pm
WHERE pm.payment_method_id = v_payment_method_id;
SELECT circle_name
INTO v_circle_code_es_parent
FROM NL_ssa_circle_map
WHERE ssa_name = v_ssa_code_es_parent;
BEGIN
SELECT address_name
INTO v_customer_name
FROM contact@cdr_nz
WHERE customer_ref = v_customer_ref AND
contact_seq IN
(SELECT MAX(contact_seq)
FROM contact@cdr_nz
WHERE customer_ref = v_customer_ref AND
TRIM(address_name) IS NOT NULL);
EXCEPTION
WHEN no_data_found THEN
BEGIN
SELECT nvl(first_name || last_name, '.')
INTO v_customer_name
FROM contact@cdr_nz
WHERE customer_ref = v_customer_ref AND
contact_seq IN
(SELECT billing_contact_seq
FROM accountdetails@cdr_nz
WHERE account_num = v_account_id AND end_dat IS NULL);
EXCEPTION
WHEN no_data_found THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed',
v_errcode,
v_errmsg || 'customer name is missing',
SYSDATE);
COMMIT;
END;
END;
BEGIN
/* SELECT decode(bank_account_number,
bank_branch_number,
mandate_attr_1,
mandate_attr_2
INTO v_bank_acc_num,
v_branch_micr_code,
v_bank_name,
v_mandate_attr2
FROM prmandate@cdr_nz
WHERE account_num = v_account_id AND
payment_method_id = v_payment_method_id;*/
select DECODE(PAYMENT_METHOD_id,
6,ipf_admin.data_security.decryptandgetclear_1@cdr_nz('RB_PRMANDATE_BANK_ACCOUNT_NUMBER',BANK_ACCOUNT_NUMBER) ,
12,ipf_admin.data_security.decryptandgetclear_1@cdr_nz('RB_PRMANDATE_CARD_NUMBER',CARD_NUMBER),''),
DECODE(6,6,P.Mandate_Attr_3,12,P.Mandate_Attr_3,'') ,
DECODE(P.PAYMENT_METHOD_ID,6,P.MANDATE_ATTR_2,12,P.MANDATE_ATTR_2,''),
DECODE(P.PAYMENT_METHOD_ID,6,P.MANDATE_ATTR_4,'')
into v_bank_acc_num,v_bank_name ,v_branch_micr_code,v_bank_acc_type
from prmandate@cdr_nz p
WHERE account_num = v_account_id AND
payment_method_id = v_payment_method_id
and (active_to_dat is null or active_to_dat >sysdate);
/*select DECODE(v_PAYMENT_METHOD_id,
6,ipf_admin.data_security.decryptandgetclear_1@cdr_nz('RB_PRMANDATE_BANK_ACCOUNT_NUMBER',v_bank_acc_num),
12,ipf_admin.data_security.decryptandgetclear_1@cdr_nz('RB_PRMANDATE_CARD_NUMBER',v_bank_acc_num),'') ,
DECODE(v_PAYMENT_METHOD_ID,6,P.Mandate_Attr_1,12,P.Mandate_Attr_1,'') ,
DECODE(v_PAYMENT_METHOD_ID,6,P.MANDATE_ATTR_2,12,P.MANDATE_ATTR_2,'') ,
DECODE(v_PAYMENT_METHOD_ID,6,P.MANDATE_ATTR_4,12,'','')
into v_bank_acc_num,v_bank_name ,v_branch_micr_code,v_bank_acc_type
from prmandate@cdr_nz p;*/
/* IF TRIM(upper(v_payment_method)) = 'ECS' THEN
v_bank_acc_type := v_mandate_attr2;
ELSIF TRIM(upper(v_payment_method)) = 'SICC' THEN
v_bank_provided_id := v_mandate_attr2;
END IF;*/
EXCEPTION
WHEN no_data_found THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
INSERT INTO NL_es_pms_feed
(customer_id,
customer_name,
account_num,
invoice_seq_no,
invoice_date,
invoice_amt,
tot_outstanding_amt,
pay_due_date,
customer_category,
payment_type,
bank_provider_id,
bank_name,
branch_micr_code,
bank_account_no,
bank_account_type,
logged_date)
VALUES
(v_customer_ref,
TRIM(v_customer_name),
v_account_id,
v_invoice_seq,
v_invoice_date,
v_invoice_amt,
v_outstanding_amt,
v_pay_due_date,
v_account_category,
v_payment_method,
NULL,
v_bank_name,
v_branch_micr_code,
v_bank_acc_num,
v_bank_acc_type,
SYSDATE);
COMMIT;
IF v_count = 0 THEN
--the name of the file which will store payment feed
filename := 'es_invoice_' || to_char(SYSDATE, 'DDMMYYYYHH24MISS') ||
'.csv';
v_filehandle := utl_file.fopen('PMS_ES_INVOICE_DIR', filename, 'a');
END IF;
v_invoice_amount1 := v_invoice_amt / (10 ** v_implied_decimel);
SELECT TRIM(to_char(v_invoice_amount1, '9999999999999999.99'))
INTO v_invoice_amount2
FROM dual;
v_outstanding_amt1 := v_outstanding_amt / (10 ** v_implied_decimel);
SELECT TRIM(to_char(v_outstanding_amt1, '9999999999999999.99'))
INTO v_outstanding_amt2
FROM dual;
utl_file.putf(v_filehandle,
'|:|' || v_customer_ref || '|:|!*!|:|' ||
TRIM(v_customer_name) || '|:|!*!|:|' || v_account_id ||
'|:|!*!|:|' || v_invoice_number || '|:|!*!|:|' ||
to_char(v_invoice_date, 'DD-MM-YYYY') || '|:|!*!|:|' ||
round(v_invoice_amount2, 0) || '|:|!*!|:|' ||
round(v_outstanding_amt2, 0) || '|:|!*!|:|' ||
to_char(v_pay_due_date, 'DD-MM-YYYY') || '|:|!*!|:|' ||
TRIM(v_ssa_code_es_parent) || '|:|!*!|:|' ||
TRIM(v_circle_code_es_parent) || '|:|!*!|:|' ||
TRIM(v_account_category) || '|:|!*!|:|' ||
TRIM(v_payment_method) || '|:|!*!|:|' ||
TRIM(v_bank_provided_id) || '|:|!*!|:|' ||
TRIM(v_bank_name) || '|:|!*!|:|' ||
TRIM(v_branch_micr_code) || '|:|!*!|:|' ||
TRIM(v_bank_acc_num) || '|:|!*!|:|' ||
TRIM(v_bank_acc_type) || '|:|!*!|:|' || TRIM(v_group_id) ||
'|:|!*!');
utl_file.new_line(v_filehandle);
v_count := v_count + 1;
UPDATE NL_es_pms_feed
SET feed_name = filename
WHERE trunc(logged_date) = trunc(SYSDATE) AND
customer_id = v_customer_ref AND invoice_seq_no = v_invoice_seq;
COMMIT;
END LOOP;
--closing the filehandle for the feed file
utl_file.fclose(v_filehandle);
--exception handling
EXCEPTION
WHEN no_data_found THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN case_not_found THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN cursor_already_open THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN dup_val_on_index THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN invalid_cursor THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN too_many_rows THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
END sp_gen_pms_invoice_feed;
=============sp_gen_pms_invoice_SSA
CREATE OR REPLACE PROCEDURE ESTAPLE.SP_GEN_PMS_INVOICE_FEED_SSA(P_SSA_CODE IN VARCHAR2) AS
--Author: 230328
--Brief Info: the procedure creates invoice feed for Es parent accounts
--version :1.7
/* last modified by 230328
date :Sept 24,2009
Changed the delimiter as requested by PMS
*/
V_FILEHANDLE UTL_FILE.FILE_TYPE;
FILENAME VARCHAR2(50);
--fetches the ES invoice details
CURSOR CUR_PMS_FEED IS
SELECT DISTINCT C.CUSTOMER_REF CUSTOMER_REF,
A.ACCOUNT_NUM ACCOUNT_NUM,
ATA.ACCOUNT_TYPE ACCOUNT_CATEGORY,
CT.FIRST_NAME || ' ' || CT.LAST_NAME CUSTOMER_NAME,
BI.BILL_SEQ INVOICE_SEQ_NO,
--bi.invoice_net_mny invoice_amount,
BI.BALANCE_OUT_MNY OUTSTANDING_BAL,
BI.PAYMENT_DUE_DAT PAYMENT_DUE_DATE,
BI.ACTUAL_BILL_DTM INVOICE_DATE,
B.LEGACY_SYSTEM_ACCOUNT_NUM AS GROUP_ID,
ATA.CIRCLE_CODE CIRCLE_CODE,
ATA.SSA_CODE SSA_CODE
FROM CUSTOMER@CDR_NZ C INNER JOIN ACCOUNT@CDR_NZ A ON C.CUSTOMER_REF = A.CUSTOMER_REF INNER JOIN NL_ES_ACCOUNT B ON A.ACCOUNT_NUM = B.ACCOUNT_ID INNER JOIN ACCOUNTATTRIBUTES@CDR_NZ ATA ON A.ACCOUNT_NUM = ATA.ACCOUNT_NUM INNER JOIN CONTACT@CDR_NZ CT ON C.CUSTOMER_REF = CT.CUSTOMER_REF INNER JOIN CONTACTTYPE@CDR_NZ CTT ON CTT.CONTACT_TYPE_ID = CT.CONTACT_TYPE_ID INNER JOIN BILLSUMMARY@CDR_NZ BI ON A.ACCOUNT_NUM = BI.ACCOUNT_NUM
WHERE C.PARENT_CUSTOMER_REF IS NULL AND
--bi.bill_seq > b.last_invoice_seq_no AND
TRIM(UPPER(ATA.ACCOUNT_TYPE)) IN
(PKG_ES.ACCOUNT_TYPE_ZONAL_INDIVIDUAL,
PKG_ES.ACCOUNT_TYPE_ZONAL_CORPORATE) AND
TRIM(UPPER(ATA.ACCOUNT_SUBTYPE)) = PKG_ES.ACCOUNT_SUBTYPE_PARENT AND
TRIM(UPPER(CTT.CONTACT_TYPE_NAME)) = 'BILLING CONTACT' AND
BILL_DTM = TO_DATE('01-01-2011', 'dd-mm-yyyy') AND
B.SSA = P_SSA_CODE AND BI.BILL_STATUS = 1;
V_ACCOUNT_ID NL_ES_ACCOUNT.ACCOUNT_ID%TYPE;
V_CUSTOMER_REF CUSTOMER.CUSTOMER_REF@CDR_NZ%TYPE;
V_ACCOUNT_CATEGORY ACCOUNTATTRIBUTES.ACCOUNT_TYPE@CDR_NZ%TYPE;
V_CUSTOMER_NAME VARCHAR2(85);
V_INVOICE_SEQ BILLSUMMARY.BILL_SEQ@CDR_NZ%TYPE;
V_INVOICE_AMT BILLSUMMARY.BALANCE_OUT_MNY@CDR_NZ%TYPE;
V_OUTSTANDING_AMT BILLSUMMARY.BALANCE_OUT_MNY@CDR_NZ%TYPE;
V_INVOICE_DATE BILLSUMMARY.ACTUAL_BILL_DTM@CDR_NZ%TYPE;
V_PAY_DUE_DATE BILLSUMMARY.PAYMENT_DUE_DAT@CDR_NZ%TYPE;
V_PAYMENT_METHOD PAYMENTMETHOD.PAYMENT_METHOD_NAME@CDR_NZ%TYPE;
V_PAYMENT_METHOD_ID PAYMENTMETHOD.PAYMENT_METHOD_ID@CDR_NZ%TYPE;
V_INVOICE_AMOUNT1 NUMBER(18, 2);
V_INVOICE_AMOUNT2 VARCHAR2(20);
V_OUTSTANDING_AMT1 NUMBER(18, 2);
V_OUTSTANDING_AMT2 VARCHAR2(20);
V_BANK_PROVIDED_ID VARCHAR2(50);
V_MANDATE_ATTR2 VARCHAR2(50);
V_BANK_NAME VARCHAR2(50);
V_BRANCH_MICR_CODE VARCHAR2(50);
V_BANK_ACC_NUM VARCHAR2(50);
V_BANK_ACC_TYPE VARCHAR2(5);
V_CIRCLE_CODE_ES_PARENT VARCHAR2(30);
--pkg_es.circle_code_es_parent;
V_SSA_CODE_ES_PARENT VARCHAR2(30);
--pkg_es.ssa_code_es_parent;
V_GROUP_ID NL_ES_ACCOUNT.LEGACY_SYSTEM_ACCOUNT_NUM%TYPE;
--variable used for exception handling
V_ERRCODE NUMBER;
--variable used to store the error message.
V_ERRMSG VARCHAR2(570);
--v_count variable to track the lines in the PMS feed file.
V_COUNT NUMBER(10);
V_IMPLIED_DECIMEL NL_ES_PARAMETERS.INT_VALUE%TYPE := NULL;
BEGIN
--initializing the feed file line counter
V_COUNT := 0;
SELECT BP.INT_VALUE
INTO V_IMPLIED_DECIMEL
FROM NL_ES_PARAMETERS BP
WHERE BP.MODULE_NAME LIKE '%ES%' AND
BP.PARAMETER_NAME = 'Implied Decimel';
FOR REC_PMS_FEED IN CUR_PMS_FEED LOOP
V_BANK_PROVIDED_ID := NULL;
V_BANK_NAME := NULL;
V_BRANCH_MICR_CODE := NULL;
V_BANK_ACC_NUM := NULL;
V_BANK_ACC_TYPE := NULL;
V_CUSTOMER_REF := REC_PMS_FEED.CUSTOMER_REF;
V_ACCOUNT_ID := REC_PMS_FEED.ACCOUNT_NUM;
V_ACCOUNT_CATEGORY := TRIM(REC_PMS_FEED.ACCOUNT_CATEGORY);
V_CUSTOMER_NAME := TRIM(REC_PMS_FEED.CUSTOMER_NAME);
V_INVOICE_SEQ := REC_PMS_FEED.INVOICE_SEQ_NO;
--v_invoice_amt := rec_pms_feed.invoice_amount;
V_OUTSTANDING_AMT := REC_PMS_FEED.OUTSTANDING_BAL;
V_INVOICE_DATE := REC_PMS_FEED.INVOICE_DATE;
V_PAY_DUE_DATE := REC_PMS_FEED.PAYMENT_DUE_DATE;
V_GROUP_ID := REC_PMS_FEED.GROUP_ID;
V_CIRCLE_CODE_ES_PARENT := REC_PMS_FEED.CIRCLE_CODE;
V_SSA_CODE_ES_PARENT := REC_PMS_FEED.SSA_CODE;
--Invoice amount in invoice feed is same as the outsanding
V_INVOICE_AMT := V_OUTSTANDING_AMT;
SELECT AD.PAYMENT_METHOD_ID
INTO V_PAYMENT_METHOD_ID
FROM ACCOUNTDETAILS@CDR_NZ AD
WHERE ACCOUNT_NUM = V_ACCOUNT_ID
GROUP BY PAYMENT_METHOD_ID;
SELECT PM.PAYMENT_METHOD_NAME
INTO V_PAYMENT_METHOD
FROM PAYMENTMETHOD@CDR_NZ PM
WHERE PM.PAYMENT_METHOD_ID = V_PAYMENT_METHOD_ID;
BEGIN
SELECT BANK_ACCOUNT_NUMBER,
BANK_BRANCH_NUMBER,
MANDATE_ATTR_1,
MANDATE_ATTR_2
INTO V_BANK_ACC_NUM,
V_BRANCH_MICR_CODE,
V_BANK_NAME,
V_MANDATE_ATTR2
FROM PRMANDATE@CDR_NZ
WHERE ACCOUNT_NUM = V_ACCOUNT_ID AND
PAYMENT_METHOD_ID = V_PAYMENT_METHOD_ID;
IF TRIM(UPPER(V_PAYMENT_METHOD)) = 'ECS' THEN
V_BANK_ACC_TYPE := V_MANDATE_ATTR2;
ELSIF TRIM(UPPER(V_PAYMENT_METHOD)) = 'SICC' THEN
V_BANK_PROVIDED_ID := V_MANDATE_ATTR2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
INSERT INTO NL_ES_PMS_FEED
(CUSTOMER_ID,
CUSTOMER_NAME,
ACCOUNT_NUM,
INVOICE_SEQ_NO,
INVOICE_DATE,
INVOICE_AMT,
TOT_OUTSTANDING_AMT,
PAY_DUE_DATE,
CUSTOMER_CATEGORY,
PAYMENT_TYPE,
BANK_PROVIDER_ID,
BANK_NAME,
BRANCH_MICR_CODE,
BANK_ACCOUNT_NO,
BANK_ACCOUNT_TYPE)
VALUES
(V_CUSTOMER_REF,
V_CUSTOMER_NAME,
V_ACCOUNT_ID,
V_INVOICE_SEQ,
V_INVOICE_DATE,
V_INVOICE_AMT,
V_OUTSTANDING_AMT,
V_PAY_DUE_DATE,
V_ACCOUNT_CATEGORY,
V_PAYMENT_METHOD,
NULL,
NULL,
NULL,
NULL,
NULL);
COMMIT;
IF V_COUNT = 0 THEN
--the name of the file which will store payment feed
FILENAME := 'es_invoice_' || TO_CHAR(SYSDATE, 'DDMMYYYYHH24MISS') ||
'.csv';
V_FILEHANDLE := UTL_FILE.FOPEN('PMS_ES_INVOICE_DIR', FILENAME, 'a');
END IF;
V_INVOICE_AMOUNT1 := V_INVOICE_AMT / (10 ** V_IMPLIED_DECIMEL);
SELECT TRIM(TO_CHAR(V_INVOICE_AMOUNT1, '9999999999999999.99'))
INTO V_INVOICE_AMOUNT2
FROM DUAL;
V_OUTSTANDING_AMT1 := V_OUTSTANDING_AMT / (10 ** V_IMPLIED_DECIMEL);
SELECT TRIM(TO_CHAR(V_OUTSTANDING_AMT1, '9999999999999999.99'))
INTO V_OUTSTANDING_AMT2
FROM DUAL;
UTL_FILE.PUTF(V_FILEHANDLE,
'|:|' || V_CUSTOMER_REF || '|:|!*!|:|' ||
TRIM(V_CUSTOMER_NAME) || '|:|!*!|:|' || V_ACCOUNT_ID ||
'|:|!*!|:|' || V_INVOICE_SEQ || '|:|!*!|:|' ||
TO_CHAR(V_INVOICE_DATE, 'DD-MM-YYYY') || '|:|!*!|:|' ||
V_INVOICE_AMOUNT2 || '|:|!*!|:|' || V_OUTSTANDING_AMT2 ||
'|:|!*!|:|' || TO_CHAR(V_PAY_DUE_DATE, 'DD-MM-YYYY') ||
'|:|!*!|:|' || TRIM(V_SSA_CODE_ES_PARENT) || '|:|!*!|:|' ||
TRIM(V_CIRCLE_CODE_ES_PARENT) || '|:|!*!|:|' ||
TRIM(V_ACCOUNT_CATEGORY) || '|:|!*!|:|' ||
TRIM(V_PAYMENT_METHOD) || '|:|!*!|:|' ||
TRIM(V_BANK_PROVIDED_ID) || '|:|!*!|:|' ||
TRIM(V_BANK_NAME) || '|:|!*!|:|' ||
TRIM(V_BRANCH_MICR_CODE) || '|:|!*!|:|' ||
TRIM(V_BANK_ACC_NUM) || '|:|!*!|:|' ||
TRIM(V_BANK_ACC_TYPE) || '|:|!*!' || TRIM(V_GROUP_ID) ||
'|:|!*!');
UTL_FILE.NEW_LINE(V_FILEHANDLE);
V_COUNT := V_COUNT + 1;
COMMIT;
END LOOP;
--closing the filehandle for the feed file
UTL_FILE.FCLOSE(V_FILEHANDLE);
--exception handling
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('PMS Invoice Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN CASE_NOT_FOUND THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('PMS Invoice Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN CURSOR_ALREADY_OPEN THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('PMS Invoice Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('PMS Invoice Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN INVALID_CURSOR THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('PMS Invoice Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN TOO_MANY_ROWS THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('PMS Invoice Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
V_ERRCODE := SQLCODE;
V_ERRMSG := SQLERRM;
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_CODE, ERROR_MSG, ERROR_DATETIME)
VALUES
('PMS Invoice Feed', V_ERRCODE, V_ERRMSG, SYSDATE);
COMMIT;
END SP_GEN_PMS_INVOICE_FEED_SSA;
=====SP_GEN_PMS_INVOICE_FEED_ZERO
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_gen_pms_invoice_feed_zero /*(P_SSA_CODE IN VARCHAR2)*/
AS
--version 1.0
--created by - Deepa Miglani(230328)
--Purpose - To generate invoice feed for Es parent accounts
v_filehandle utl_file.file_type;
filename VARCHAR2(50);
--fetches the ES invoice details
CURSOR cur_pms_feed IS
SELECT c.customer_ref customer_ref,
a.account_num account_num,
ata.account_type account_category,
ct.first_name || ' ' || ct.last_name customer_name,
1 AS invoice_seq_no,
--bi.invoice_net_mny invoice_amount,
0 outstanding_bal,
'22-SEP-2011' AS payment_due_date,
'07-SEP-2011' AS invoice_date,
b.legacy_system_account_num AS group_id,
ata.circle_code circle_code,
ata.ssa_code ssa_code
FROM customer@cdr_nz c INNER JOIN account@cdr_nz a ON c.customer_ref = a.customer_ref INNER JOIN NL_es_account b ON a.account_num = b.account_id INNER JOIN accountattributes@cdr_nz ata ON a.account_num = ata.account_num INNER JOIN contact@cdr_nz ct ON c.customer_ref = ct.customer_ref INNER JOIN contacttype@cdr_nz ctt ON ctt.contact_type_id = ct.contact_type_id
--bi.bill_seq > b.last_invoice_seq_no AND
WHERE TRIM(upper(ata.account_type)) IN
('ES ZONAL INDIVIDUAL', 'ES ZONAL CORPORATE') AND
/*ssa_code = 'KYN' and */ TRIM(upper(ata.account_subtype)) = 'PARENT'
and c.customer_ref = '1016705322' and contact_seq = 1;
v_account_id NL_es_account.account_id%TYPE;
v_customer_ref customer.customer_ref@cdr_nz%TYPE;
v_account_category accountattributes.account_type@cdr_nz%TYPE;
v_customer_name VARCHAR2(85);
v_invoice_seq billsummary.bill_seq@cdr_nz%TYPE;
v_invoice_amt billsummary.balance_out_mny@cdr_nz%TYPE;
v_outstanding_amt billsummary.balance_out_mny@cdr_nz%TYPE;
v_invoice_date billsummary.actual_bill_dtm@cdr_nz%TYPE;
v_pay_due_date billsummary.payment_due_dat@cdr_nz%TYPE;
v_payment_method paymentmethod.payment_method_name@cdr_nz%TYPE;
v_payment_method_id paymentmethod.payment_method_id@cdr_nz%TYPE;
v_invoice_amount1 NUMBER(18, 2);
v_invoice_amount2 VARCHAR2(20);
v_outstanding_amt1 NUMBER(18, 2);
v_outstanding_amt2 VARCHAR2(20);
v_bank_provided_id VARCHAR2(50);
v_mandate_attr2 VARCHAR2(50);
v_bank_name VARCHAR2(50);
v_branch_micr_code VARCHAR2(50);
v_bank_acc_num VARCHAR2(50);
v_bank_acc_type VARCHAR2(5);
v_circle_code_es_parent VARCHAR2(30);
--pkg_es.circle_code_es_parent;
v_ssa_code_es_parent VARCHAR2(30);
--pkg_es.ssa_code_es_parent;
v_group_id NL_es_account.legacy_system_account_num%TYPE;
--variable used for exception handling
v_errcode NUMBER;
--variable used to store the error message.
v_errmsg VARCHAR2(570);
--v_count variable to track the lines in the PMS feed file.
v_count NUMBER(10);
v_implied_decimel NL_es_parameters.int_value%TYPE := NULL;
BEGIN
--initializing the feed file line counter
v_count := 0;
SELECT bp.int_value
INTO v_implied_decimel
FROM NL_es_parameters bp
WHERE bp.module_name LIKE '%ES%' AND
bp.parameter_name = 'Implied Decimel';
FOR rec_pms_feed IN cur_pms_feed LOOP
v_bank_provided_id := NULL;
v_bank_name := NULL;
v_branch_micr_code := NULL;
v_bank_acc_num := NULL;
v_bank_acc_type := NULL;
v_customer_ref := rec_pms_feed.customer_ref;
v_account_id := rec_pms_feed.account_num;
v_account_category := TRIM(rec_pms_feed.account_category);
v_customer_name := TRIM(rec_pms_feed.customer_name);
v_invoice_seq := rec_pms_feed.invoice_seq_no;
--v_invoice_amt := rec_pms_feed.invoice_amount;
v_outstanding_amt := rec_pms_feed.outstanding_bal;
v_invoice_date := rec_pms_feed.invoice_date;
v_pay_due_date := rec_pms_feed.payment_due_date;
v_group_id := rec_pms_feed.group_id;
v_circle_code_es_parent := nvl(rec_pms_feed.circle_code, 'MH');
v_ssa_code_es_parent := nvl(rec_pms_feed.ssa_code, 'KYN');
--Invoice amount in invoice feed is same as the outsanding
v_invoice_amt := v_outstanding_amt;
SELECT ad.payment_method_id
INTO v_payment_method_id
FROM accountdetails@cdr_nz ad
WHERE account_num = v_account_id
GROUP BY payment_method_id;
SELECT pm.payment_method_name
INTO v_payment_method
FROM paymentmethod@cdr_nz pm
WHERE pm.payment_method_id = v_payment_method_id;
BEGIN
SELECT bank_account_number,
bank_branch_number,
mandate_attr_1,
mandate_attr_2
INTO v_bank_acc_num,
v_branch_micr_code,
v_bank_name,
v_mandate_attr2
FROM prmandate@cdr_nz
WHERE account_num = v_account_id AND
payment_method_id = v_payment_method_id;
IF TRIM(upper(v_payment_method)) = 'ECS' THEN
v_bank_acc_type := v_mandate_attr2;
ELSIF TRIM(upper(v_payment_method)) = 'SICC' THEN
v_bank_provided_id := v_mandate_attr2;
END IF;
EXCEPTION
WHEN no_data_found THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
INSERT INTO NL_es_pms_feed
(customer_id,
customer_name,
account_num,
invoice_seq_no,
invoice_date,
invoice_amt,
tot_outstanding_amt,
pay_due_date,
customer_category,
payment_type,
bank_provider_id,
bank_name,
branch_micr_code,
bank_account_no,
bank_account_type,
logged_date)
VALUES
(v_customer_ref,
v_customer_name,
v_account_id,
v_invoice_seq,
v_invoice_date,
v_invoice_amt,
v_outstanding_amt,
v_pay_due_date,
v_account_category,
v_payment_method,
NULL,
NULL,
NULL,
NULL,
NULL,
SYSDATE);
COMMIT;
IF v_count = 0 THEN
--the name of the file which will store payment feed
filename := 'es_invoice_' || to_char(SYSDATE, 'DDMMYYYYHH24MISS') ||
'.csv';
v_filehandle := utl_file.fopen('PMS_ES_INVOICE_DIR', filename, 'a');
END IF;
v_invoice_amount1 := v_invoice_amt / (10 ** v_implied_decimel);
SELECT TRIM(to_char(v_invoice_amount1, '9999999999999999.99'))
INTO v_invoice_amount2
FROM dual;
v_outstanding_amt1 := v_outstanding_amt / (10 ** v_implied_decimel);
SELECT TRIM(to_char(v_outstanding_amt1, '9999999999999999.99'))
INTO v_outstanding_amt2
FROM dual;
utl_file.putf(v_filehandle,
'|:|' || v_customer_ref || '|:|!*!|:|' ||
TRIM(v_customer_name) || '|:|!*!|:|' || v_account_id ||
'|:|!*!|:|' || v_invoice_seq || '|:|!*!|:|' ||
to_char(v_invoice_date, 'DD-MM-YYYY') || '|:|!*!|:|' ||
v_invoice_amount2 || '|:|!*!|:|' || v_outstanding_amt2 ||
'|:|!*!|:|' || to_char(v_pay_due_date, 'DD-MM-YYYY') ||
'|:|!*!|:|' || TRIM(v_ssa_code_es_parent) || '|:|!*!|:|' ||
TRIM(v_circle_code_es_parent) || '|:|!*!|:|' ||
TRIM(v_account_category) || '|:|!*!|:|' ||
TRIM(v_payment_method) || '|:|!*!|:|' ||
TRIM(v_bank_provided_id) || '|:|!*!|:|' ||
TRIM(v_bank_name) || '|:|!*!|:|' ||
TRIM(v_branch_micr_code) || '|:|!*!|:|' ||
TRIM(v_bank_acc_num) || '|:|!*!|:|' ||
TRIM(v_bank_acc_type) || '|:|!*!|:|' || TRIM(v_group_id) ||
'|:|!*!');
utl_file.new_line(v_filehandle);
v_count := v_count + 1;
UPDATE NL_es_pms_feed
SET feed_name = filename
WHERE logged_date = SYSDATE AND customer_id = v_customer_ref AND
invoice_seq_no = v_invoice_seq;
COMMIT;
END LOOP;
--closing the filehandle for the feed file
utl_file.fclose(v_filehandle);
--exception handling
EXCEPTION
WHEN no_data_found THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN case_not_found THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN cursor_already_open THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN dup_val_on_index THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN invalid_cursor THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN too_many_rows THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('PMS Invoice Feed', v_errcode, v_errmsg, SYSDATE);
COMMIT;
END sp_gen_pms_invoice_feed_zero;
============SP_GEN_SERVICES_FEED
CREATE OR REPLACE PROCEDURE ESTAPLE.SP_GEN_SERVICES_FEED IS
-- Variable Declaration
--version 1.0
--Created by - Deepa Milglani (230326)
--Purpose - To populate zestap_ser table in CRM database in BC7 system
V_ES_CHILD_ACCOUNT_ID NL_ES_HOME_SYS_ACCOUNT.ACCOUNT_ID%TYPE := NULL;
V_ES_PARENT_ID NL_ES_HOME_SYS_ACCOUNT.PARENT_ACCOUNT_ID%TYPE := NULL;
V_SERVICE_ID NL_ES_CDR_SERVICES.TELEPHONE_NO%TYPE := NULL;
V_RATE_PLAN NL_ES_CDR_SERVICES.RATE_PLAN%TYPE := NULL;
v_rowid ROWID;
V_MANDT SAPSR3.ZESTAP_SER.MANDT@CRM_DB_BC7%TYPE := NULL;
V_STATUS SAPSR3.ZESTAP_SER.STATUS@CRM_DB_BC7%TYPE := NULL;
V_ERR_NUM NUMBER;
V_ERR_MSG VARCHAR2(570);
/*cursor cur_accounts to fetch the accounts acquisition info
from NL_es_cdr_services, NL_es_gsm_services, NL_es_nib_services table in ES database*/
CURSOR CUR_ACCOUNTS IS
SELECT ACCOUNT_ID,
TELEPHONE_NO SERVICE_ID,
RATE_PLAN,
CASE PRODUCT_STATUS
WHEN 1 THEN
'Active'
WHEN 2 THEN
'Suspended'
WHEN 3 THEN
'Pending'
END CASE,ROWID
FROM NL_ES_CDR_SERVICES
WHERE (is_feed_sent IS null OR is_feed_sent =0);
BEGIN
--DELETE FROM SAPSR3.ZESTAP_SER@CRM_DB_BC7;
COMMIT;
V_MANDT := '400';
--open cursor cur_accounts
OPEN CUR_ACCOUNTS;
LOOP
FETCH CUR_ACCOUNTS
INTO V_ES_CHILD_ACCOUNT_ID, V_SERVICE_ID, V_RATE_PLAN, V_STATUS,v_rowid;
EXIT WHEN CUR_ACCOUNTS%NOTFOUND;
SELECT ES_C.PARENT_ACCOUNT_ID
INTO V_ES_PARENT_ID
FROM NL_ES_HOME_SYS_ACCOUNT ES_C
WHERE ES_C.ACCOUNT_ID = V_ES_CHILD_ACCOUNT_ID;
/*Inserting the services table in BC7 system of CRM
The info will be in the format:-
es_parent_account_id
es_child_account_id
Service Id
Traiff plan name*/
BEGIN
INSERT INTO SAPSR3.ZESTAP_SER@CRM_DB_BC7
(MANDT, P_BILL, C_BILL, SERVICE, RPLAN, STATUS)
VALUES
(V_MANDT,
V_ES_PARENT_ID,
V_ES_CHILD_ACCOUNT_ID,
V_SERVICE_ID,
V_RATE_PLAN,
V_STATUS);
UPDATE NL_ES_CDR_SERVICES B
SET B.IS_FEED_SENT = 1
WHERE B.ACCOUNT_ID = V_ES_CHILD_ACCOUNT_ID AND
ROWID = v_rowid AND
(B.IS_FEED_SENT IS NULL OR B.IS_FEED_SENT = 0);
EXCEPTION
WHEN OTHERS THEN
V_ERR_NUM := SQLCODE;
V_ERR_MSG := SQLERRM || 'parent' || V_ES_PARENT_ID || 'child' ||
V_ES_CHILD_ACCOUNT_ID;
--Inserting into NL_es_error_log
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_MSG, ERROR_DATETIME, ERROR_CODE)
VALUES
('Services Feed', V_ERR_MSG, SYSDATE, V_ERR_NUM);
END;
END LOOP;
COMMIT;
--Exception Handling
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_ERR_NUM := SQLCODE;
V_ERR_MSG := 'Error in services feed:' || SUBSTR(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_MSG, ERROR_DATETIME, ERROR_CODE)
VALUES
('Services Feed', V_ERR_MSG, SYSDATE, V_ERR_NUM);
COMMIT;
WHEN OTHERS THEN
V_ERR_NUM := SQLCODE;
V_ERR_MSG := 'Error in services feed:' || SUBSTR(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_ES_ERROR_LOG
(MODULE_NAME, ERROR_MSG, ERROR_DATETIME, ERROR_CODE)
VALUES
('Services Feed', V_ERR_MSG, SYSDATE, V_ERR_NUM);
COMMIT;
--end of sp_gen_services_feed procedure
END SP_GEN_SERVICES_FEED;
===========SP_GEN_HBS_PAYMENTS
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_get_hbs_payments(p_bill_cycle_date DATE) AS
v_account_id NL_es_home_sys_account.account_id%TYPE;
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
V_CUSTOMER_REF account.customer_ref@cdr_nz%TYpe;
v_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%TYPE;
v_bill_ver NUMBER(1);
v_payment_mny NL_es_cdr_bill_invoice.last_payment%TYPE;
v_hbs_payment_mny NL_es_cdr_bill_invoice.last_payment%TYPE;
v_prev_bill_run_date NL_es_cdr_bill_invoice.bill_run_date%TYPE;
v_current_bill_run_date NL_es_cdr_bill_invoice.bill_run_date%TYPE;
v_failed_payment_mny NL_es_cdr_bill_invoice.last_payment%TYPE;
v_deposit_rebate_mny NL_es_cdr_bill_invoice.refunds%TYPE;
v_refund_mny NL_es_cdr_bill_invoice.refunds%TYPE;
v_product_charge NL_es_post_charges_log.charge%TYPE;
v_bill_seq NL_es_home_sys_account.invoice_seq_no%TYPE;
v_start_date DATE;
v_end_date DATE;
V_ES_DISCOUNT NL_es_hbs_payments.es_discount%type;
v_payment_method_id paymentmethod.payment_method_id@cdr_nz%TYPE;
v_payment_origin_id paymentorigin.payment_origin_id@cdr_nz%TYPE;
v_error_string VARCHAR2(512) := NULL;
v_errcode NUMBER(6);
CURSOR cur_billable_acc IS
SELECT h.account_id,
h.home_sys_account_id,
MAX(hbs.invoice_seq_no) AS invoice_seq_no
FROM NL_es_home_sys_account h INNER JOIN NL_es_hbs_payments hbs ON h.account_id = hbs.account_id
WHERE h.is_active = 1 AND h.enrollment_date < p_bill_cycle_date AND
h.is_new = 0
GROUP BY h.account_id, h.home_sys_account_id;
CURSOR cur_get_bill IS
SELECT actual_bill_dtm,
bill_version,
bill_seq,
start_of_bill_dtm,
bill_dtm
FROM billsummary@cdr_nz
WHERE account_num = v_home_sys_account_id AND
bill_seq > v_invoice_seq_no AND bill_status IN (1, 7, 8) AND
balance_out_mny >= 0 AND bill_type_id IN (1, 2, 3)
ORDER BY bill_seq;
BEGIN
SELECT PAYMENT_ORIGIN_ID
INTO V_PAYMENT_ORIGIN_ID
FROM PAYMENTORIGIN@CDR_NZ
WHERE TRIM(UPPER(PAYMENT_ORIGIN_NAME)) =
TRIM(UPPER('PHYSICAL PAYMENT'));
SELECT PAYMENT_METHOD_ID
INTO V_PAYMENT_METHOD_ID
FROM PAYMENTMETHOD@CDR_NZ
WHERE TRIM(UPPER(PAYMENT_METHOD_NAME)) = TRIM(UPPER('ES DISCOUNT'));
FOR rec_billable_acc IN cur_billable_acc LOOP
v_account_id := rec_billable_acc.account_id;
v_home_sys_account_id := rec_billable_acc.home_sys_account_id;
v_invoice_seq_no := rec_billable_acc.invoice_seq_no;
v_hbs_payment_mny := 0;
v_failed_payment_mny := 0;
v_deposit_rebate_mny := 0;
FOR rec_get_bill IN cur_get_bill LOOP
v_current_bill_run_date := rec_get_bill.actual_bill_dtm;
v_bill_ver := rec_get_bill.bill_version;
v_bill_seq := rec_get_bill.bill_seq;
v_start_date := rec_get_bill.start_of_bill_dtm;
v_end_date := rec_get_bill.bill_dtm - 1;
EXIT WHEN cur_get_bill%NOTFOUND;
begin
SELECT MAX(s.invoice_seq_no)
INTO v_invoice_seq_no
FROM NL_es_hbs_payments s
WHERE s.account_id = v_account_id;
BEGIN
SELECT actual_bill_dtm
INTO v_prev_bill_run_date
FROM billsummary@cdr_nz
WHERE account_num = v_home_sys_account_id AND
bill_seq = v_invoice_seq_no AND bill_status IN (1, 7, 8);
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_refund_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_home_sys_account_id AND deposit_boo = 'F' AND
created_dtm >= v_prev_bill_run_date AND
created_dtm <= v_current_bill_run_date AND
account_payment_mny < 0 AND account_payment_status = 1;
EXCEPTION
WHEN no_data_found THEN
v_refund_mny := 0;
WHEN OTHERS THEN
RAISE;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_payment_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_home_sys_account_id AND
created_dtm <= v_current_bill_run_date AND
created_dtm >= v_prev_bill_run_date AND
(account_payment_ref IS NOT NULL
AND
account_payment_ref NOT IN
(SELECT transaction_id
FROM NL_es_payment_split bp
WHERE bp.home_sys_account_id = v_home_sys_account_id) AND
account_payment_ref NOT LIKE 'ES%') AND
account_payment_mny > 0 AND deposit_boo = 'F';
EXCEPTION
WHEN no_data_found THEN
v_payment_mny := 0;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_failed_payment_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_home_sys_account_id AND
failed_dtm <= v_current_bill_run_date AND
failed_dtm >= v_prev_bill_run_date AND
(account_payment_ref IS NULL
OR account_payment_ref NOT IN
(SELECT transaction_id
FROM NL_es_payment_split bp
WHERE bp.home_sys_account_id = v_home_sys_account_id) AND
account_payment_ref NOT LIKE 'ES%') AND deposit_boo = 'F';
EXCEPTION
WHEN no_data_found THEN
v_failed_payment_mny := 0;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_deposit_rebate_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_home_sys_account_id AND
created_dtm <= v_current_bill_run_date AND
created_dtm >= v_prev_bill_run_date AND
account_payment_ref IS NULL AND deposit_boo = 'F' AND
account_payment_mny > 0;
EXCEPTION
WHEN no_data_found THEN
v_deposit_rebate_mny := 0;
END;
v_product_charge := v_failed_payment_mny - v_deposit_rebate_mny -
v_payment_mny - v_refund_mny;
BEGIN
-- THIS section fetches the value of es_discount
SELECT customer_ref
into V_CUSTOMER_REF
FROM ACCOUNT@CDR_NZ A
WHERE ACCOUNT_NUM = V_HOME_SYS_ACCOUNT_ID ;
SELECT nvl(sum(PHYSICAL_PAYMENT_mny),0)
INTO V_ES_DISCOUNT
FROM PHYSICALPAYMENT@CDR_NZ a
WHERE PAYMENT_METHOD_ID = V_PAYMENT_METHOD_ID AND
CUSTOMER_REF = V_CUSTOMER_REF AND
A.CREATED_DTM <= v_current_bill_run_date AND
A.CREATED_DTM >= v_prev_bill_run_date
and physical_PAYMENT_STATUS=1;
EXCEPTION
WHEN no_data_found THEN
V_ES_DISCOUNT := 0;
END;
INSERT INTO NL_es_hbs_payments
(account_id,
home_sys_account_id,
invoice_seq_no,
bill_ver,
hbs_payment,
failed_hbs_payment,
deposit,
refunds,
charge,
es_discount,
logged_date,
start_date,
end_date)
VALUES
(v_account_id,
v_home_sys_account_id,
v_bill_seq,
v_bill_ver,
v_payment_mny,
v_failed_payment_mny,
v_deposit_rebate_mny,
v_refund_mny,
v_product_charge,
v_es_discount,
SYSDATE,
v_start_date,
v_end_date);
sp_create_product(v_account_id,
v_account_id,
v_product_charge,
188,
v_bill_seq,
v_start_date,
v_end_date);
if V_ES_DISCOUNT <>0 then
sp_create_product(v_account_id,
v_account_id,
-v_es_discount,
190,
v_bill_seq,
v_start_date,
v_end_date);
end if;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
rollback;
V_ERROR_STRING := 'ES child account no:' || V_ACCOUNT_ID || ' ' ||
'invoice number' || v_invoice_seq_no || ' ' ||
SQLERRM;
V_ERRCODE := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('ES HBS PAYMENTS', V_ERRCODE, V_ERROR_STRING, SYSDATE);
COMMIT;
end;
END LOOP;
END LOOP;
END sp_get_hbs_payments;
===========SP_GET_HBS_PAYMENTS
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_get_hbs_payments(p_bill_cycle_date DATE) AS
v_account_id NL_es_home_sys_account.account_id%TYPE;
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
V_CUSTOMER_REF account.customer_ref@cdr_nz%TYpe;
v_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%TYPE;
v_bill_ver NUMBER(1);
v_payment_mny NL_es_cdr_bill_invoice.last_payment%TYPE;
v_hbs_payment_mny NL_es_cdr_bill_invoice.last_payment%TYPE;
v_prev_bill_run_date NL_es_cdr_bill_invoice.bill_run_date%TYPE;
v_current_bill_run_date NL_es_cdr_bill_invoice.bill_run_date%TYPE;
v_failed_payment_mny NL_es_cdr_bill_invoice.last_payment%TYPE;
v_deposit_rebate_mny NL_es_cdr_bill_invoice.refunds%TYPE;
v_refund_mny NL_es_cdr_bill_invoice.refunds%TYPE;
v_product_charge NL_es_post_charges_log.charge%TYPE;
v_bill_seq NL_es_home_sys_account.invoice_seq_no%TYPE;
v_start_date DATE;
v_end_date DATE;
V_ES_DISCOUNT NL_es_hbs_payments.es_discount%type;
v_payment_method_id paymentmethod.payment_method_id@cdr_nz%TYPE;
v_payment_origin_id paymentorigin.payment_origin_id@cdr_nz%TYPE;
v_error_string VARCHAR2(512) := NULL;
v_errcode NUMBER(6);
CURSOR cur_billable_acc IS
SELECT h.account_id,
h.home_sys_account_id,
MAX(hbs.invoice_seq_no) AS invoice_seq_no
FROM NL_es_home_sys_account h INNER JOIN NL_es_hbs_payments hbs ON h.account_id = hbs.account_id
WHERE h.is_active = 1 AND h.enrollment_date < p_bill_cycle_date AND
h.is_new = 0
GROUP BY h.account_id, h.home_sys_account_id;
CURSOR cur_get_bill IS
SELECT actual_bill_dtm,
bill_version,
bill_seq,
start_of_bill_dtm,
bill_dtm
FROM billsummary@cdr_nz
WHERE account_num = v_home_sys_account_id AND
bill_seq > v_invoice_seq_no AND bill_status IN (1, 7, 8) AND
balance_out_mny >= 0 AND bill_type_id IN (1, 2, 3)
ORDER BY bill_seq;
BEGIN
SELECT PAYMENT_ORIGIN_ID
INTO V_PAYMENT_ORIGIN_ID
FROM PAYMENTORIGIN@CDR_NZ
WHERE TRIM(UPPER(PAYMENT_ORIGIN_NAME)) =
TRIM(UPPER('PHYSICAL PAYMENT'));
SELECT PAYMENT_METHOD_ID
INTO V_PAYMENT_METHOD_ID
FROM PAYMENTMETHOD@CDR_NZ
WHERE TRIM(UPPER(PAYMENT_METHOD_NAME)) = TRIM(UPPER('ES DISCOUNT'));
FOR rec_billable_acc IN cur_billable_acc LOOP
v_account_id := rec_billable_acc.account_id;
v_home_sys_account_id := rec_billable_acc.home_sys_account_id;
v_invoice_seq_no := rec_billable_acc.invoice_seq_no;
v_hbs_payment_mny := 0;
v_failed_payment_mny := 0;
v_deposit_rebate_mny := 0;
FOR rec_get_bill IN cur_get_bill LOOP
v_current_bill_run_date := rec_get_bill.actual_bill_dtm;
v_bill_ver := rec_get_bill.bill_version;
v_bill_seq := rec_get_bill.bill_seq;
v_start_date := rec_get_bill.start_of_bill_dtm;
v_end_date := rec_get_bill.bill_dtm - 1;
EXIT WHEN cur_get_bill%NOTFOUND;
begin
SELECT MAX(s.invoice_seq_no)
INTO v_invoice_seq_no
FROM NL_es_hbs_payments s
WHERE s.account_id = v_account_id;
BEGIN
SELECT actual_bill_dtm
INTO v_prev_bill_run_date
FROM billsummary@cdr_nz
WHERE account_num = v_home_sys_account_id AND
bill_seq = v_invoice_seq_no AND bill_status IN (1, 7, 8);
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_refund_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_home_sys_account_id AND deposit_boo = 'F' AND
created_dtm >= v_prev_bill_run_date AND
created_dtm <= v_current_bill_run_date AND
account_payment_mny < 0 AND account_payment_status = 1;
EXCEPTION
WHEN no_data_found THEN
v_refund_mny := 0;
WHEN OTHERS THEN
RAISE;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_payment_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_home_sys_account_id AND
created_dtm <= v_current_bill_run_date AND
created_dtm >= v_prev_bill_run_date AND
(account_payment_ref IS NOT NULL
AND
account_payment_ref NOT IN
(SELECT transaction_id
FROM NL_es_payment_split bp
WHERE bp.home_sys_account_id = v_home_sys_account_id) AND
account_payment_ref NOT LIKE 'ES%') AND
account_payment_mny > 0 AND deposit_boo = 'F';
EXCEPTION
WHEN no_data_found THEN
v_payment_mny := 0;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_failed_payment_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_home_sys_account_id AND
failed_dtm <= v_current_bill_run_date AND
failed_dtm >= v_prev_bill_run_date AND
(account_payment_ref IS NULL
OR account_payment_ref NOT IN
(SELECT transaction_id
FROM NL_es_payment_split bp
WHERE bp.home_sys_account_id = v_home_sys_account_id) AND
account_payment_ref NOT LIKE 'ES%') AND deposit_boo = 'F';
EXCEPTION
WHEN no_data_found THEN
v_failed_payment_mny := 0;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny), 0)
INTO v_deposit_rebate_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_home_sys_account_id AND
created_dtm <= v_current_bill_run_date AND
created_dtm >= v_prev_bill_run_date AND
account_payment_ref IS NULL AND deposit_boo = 'F' AND
account_payment_mny > 0;
EXCEPTION
WHEN no_data_found THEN
v_deposit_rebate_mny := 0;
END;
v_product_charge := v_failed_payment_mny - v_deposit_rebate_mny -
v_payment_mny - v_refund_mny;
BEGIN
-- THIS section fetches the value of es_discount
SELECT customer_ref
into V_CUSTOMER_REF
FROM ACCOUNT@CDR_NZ A
WHERE ACCOUNT_NUM = V_HOME_SYS_ACCOUNT_ID ;
SELECT nvl(sum(PHYSICAL_PAYMENT_mny),0)
INTO V_ES_DISCOUNT
FROM PHYSICALPAYMENT@CDR_NZ a
WHERE PAYMENT_METHOD_ID = V_PAYMENT_METHOD_ID AND
CUSTOMER_REF = V_CUSTOMER_REF AND
A.CREATED_DTM <= v_current_bill_run_date AND
A.CREATED_DTM >= v_prev_bill_run_date
and physical_PAYMENT_STATUS=1;
EXCEPTION
WHEN no_data_found THEN
V_ES_DISCOUNT := 0;
END;
INSERT INTO NL_es_hbs_payments
(account_id,
home_sys_account_id,
invoice_seq_no,
bill_ver,
hbs_payment,
failed_hbs_payment,
deposit,
refunds,
charge,
es_discount,
logged_date,
start_date,
end_date)
VALUES
(v_account_id,
v_home_sys_account_id,
v_bill_seq,
v_bill_ver,
v_payment_mny,
v_failed_payment_mny,
v_deposit_rebate_mny,
v_refund_mny,
v_product_charge,
v_es_discount,
SYSDATE,
v_start_date,
v_end_date);
sp_create_product(v_account_id,
v_account_id,
v_product_charge,
188,
v_bill_seq,
v_start_date,
v_end_date);
if V_ES_DISCOUNT <>0 then
sp_create_product(v_account_id,
v_account_id,
-v_es_discount,
190,
v_bill_seq,
v_start_date,
v_end_date);
end if;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
rollback;
V_ERROR_STRING := 'ES child account no:' || V_ACCOUNT_ID || ' ' ||
'invoice number' || v_invoice_seq_no || ' ' ||
SQLERRM;
V_ERRCODE := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('ES HBS PAYMENTS', V_ERRCODE, V_ERROR_STRING, SYSDATE);
COMMIT;
end;
END LOOP;
END LOOP;
END sp_get_hbs_payments;
===========SP_GET_PARENT_OUTSTANDANDING
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_get_parent_outstanding AS
CURSOR cur_get_parents IS
SELECT account_id FROM NL_es_account;
v_parent_account_id NL_es_account.account_id%TYPE;
CURSOR cur_get_child_accounts IS
SELECT account_id
FROM NL_es_home_sys_account
WHERE parent_account_id = v_parent_account_id;
v_account_id NL_es_home_sys_account.account_id%TYPE;
CURSOR cur_get_outstanding IS
SELECT outstanding_bal
FROM NL_es_cdr_bill_invoice
WHERE account_id = v_account_id;
v_outstanding_bal NL_es_cdr_bill_invoice.outstanding_bal%TYPE;
v_total NUMBER(18);
BEGIN
FOR rec_get_parents IN cur_get_parents LOOP
v_parent_account_id := rec_get_parents.account_id;
v_total := 0;
FOR rec_get_child_accounts IN cur_get_child_accounts LOOP
v_account_id := rec_get_child_accounts.account_id;
FOR rec_get_outstanding IN cur_get_outstanding LOOP
v_outstanding_bal := rec_get_outstanding.outstanding_bal;
IF (v_outstanding_bal <= 0) THEN
v_outstanding_bal := 0;
END IF;
v_total := v_total + v_outstanding_bal;
END LOOP;
END LOOP;
dbms_output.put_line('parent account id ' || v_parent_account_id ||
'outstanding is ' || v_total);
END LOOP;
END;
==========SP_GET_PAYMENTS
CREATE OR REPLACE PROCEDURE ESTAPLE.SP_GET_PAYMENTS AS
V_INT_VALUE NL_ES_PARAMETERS.INT_VALUE%TYPE;
CURSOR CUR_GET_ACCOUNTS IS
SELECT BA.LEGACY_SYSTEM_ACCOUNT_NUM,
B.ACCOUNT_ID,
PARENT_ACCOUNT_ID,
HOME_SYS_ACCOUNT_ID
FROM NL_ES_HOME_SYS_ACCOUNT B INNER JOIN NL_ES_ACCOUNT BA ON BA.ACCOUNT_ID = B.PARENT_ACCOUNT_ID
WHERE B.SSA = 'BHR';
V_GROUP_ID NL_ES_ACCOUNT.LEGACY_SYSTEM_ACCOUNT_NUM%TYPE;
V_ACCOUNT_ID NL_ES_HOME_SYS_ACCOUNT.ACCOUNT_ID%TYPE;
V_PARENT_ACCOUNT_ID NL_ES_HOME_SYS_ACCOUNT.PARENT_ACCOUNT_ID%TYPE;
V_HOME_SYS_ACCOUNT_ID NL_ES_HOME_SYS_ACCOUNT.HOME_SYS_ACCOUNT_ID%TYPE;
V_LAST_BILL_RUN_DATE BILLSUMMARY.ACTUAL_BILL_DTM@CDR_NZ%TYPE;
V_PAYMENT_TYPE PAYMENTORIGIN.PAYMENT_ORIGIN_NAME@CDR_NZ%TYPE;
V_PAYMENT_AMT ACCOUNTPAYMENT.ACCOUNT_PAYMENT_MNY@CDR_NZ%TYPE;
V_PAYMENT_DAT ACCOUNTPAYMENT.ACCOUNT_PAYMENT_DAT@CDR_NZ%TYPE;
V_CREATED_DTM ACCOUNTPAYMENT.CREATED_DTM@CDR_NZ%TYPE;
V_TRANSACTION_ID ACCOUNTPAYMENT.ACCOUNT_PAYMENT_REF@CDR_NZ%TYPE;
CURSOR CUR_GET_PAYMENTS IS
SELECT ACCOUNT_PAYMENT_MNY,
ACCOUNT_PAYMENT_DAT,
PAYMENT_ORIGIN_NAME,
CREATED_DTM,
ACCOUNT_PAYMENT_REF
FROM ACCOUNTPAYMENT@CDR_NZ A INNER JOIN PAYMENTORIGIN@CDR_NZ P ON A. PAYMENT_ORIGIN_ID = P.PAYMENT_ORIGIN_ID
WHERE ACCOUNT_NUM = V_HOME_SYS_ACCOUNT_ID AND
CREATED_DTM >= V_LAST_BILL_RUN_DATE --AND deposit_boo = 'F'
;
BEGIN
SELECT INT_VALUE
INTO V_INT_VALUE
FROM NL_ES_PARAMETERS P
WHERE P.PARAMETER_NAME LIKE 'Implied Decimel';
FOR REC_GET_ACCOUNTS IN CUR_GET_ACCOUNTS LOOP
V_GROUP_ID := REC_GET_ACCOUNTS.LEGACY_SYSTEM_ACCOUNT_NUM;
V_ACCOUNT_ID := REC_GET_ACCOUNTS.ACCOUNT_ID;
V_PARENT_ACCOUNT_ID := REC_GET_ACCOUNTS.PARENT_ACCOUNT_ID;
V_HOME_SYS_ACCOUNT_ID := REC_GET_ACCOUNTS.HOME_SYS_ACCOUNT_ID;
SELECT MAX(TRANS_BILLED_TO_DTM)
INTO V_LAST_BILL_RUN_DATE
FROM BILLSUMMARY@CDR_NZ
WHERE ACCOUNT_NUM = V_HOME_SYS_ACCOUNT_ID AND BILL_STATUS = 1;
FOR REC_GET_PAYMENTS IN CUR_GET_PAYMENTS LOOP
V_PAYMENT_AMT := REC_GET_PAYMENTS.ACCOUNT_PAYMENT_MNY;
V_PAYMENT_DAT := REC_GET_PAYMENTS.ACCOUNT_PAYMENT_DAT;
V_PAYMENT_TYPE := REC_GET_PAYMENTS.PAYMENT_ORIGIN_NAME;
V_CREATED_DTM := REC_GET_PAYMENTS.CREATED_DTM;
V_PAYMENT_AMT := V_PAYMENT_AMT / (10 ** V_INT_VALUE);
V_TRANSACTION_ID := REC_GET_PAYMENTS.ACCOUNT_PAYMENT_REF;
INSERT INTO NL_ES_PAYMENT_REPORT BP
(GROUP_ID,
PARENT_ACCOUNT_ID,
CHILD_ACCOUNT_ID,
HOME_SYS_ACCOUNT_ID,
PAYMENT_AMT,
PAYMENT_DAT,
CREATED_DTM,
LAST_BILL_RUN_DATE,
TRANSACTION_ID)
VALUES
(V_GROUP_ID,
V_PARENT_ACCOUNT_ID,
V_ACCOUNT_ID,
V_HOME_SYS_ACCOUNT_ID,
V_PAYMENT_AMT,
V_PAYMENT_DAT,
V_CREATED_DTM,
V_LAST_BILL_RUN_DATE,
V_TRANSACTION_ID);
END LOOP;
END LOOP;
COMMIT;
END;
===========SP_GET_PENDING_ES_PAYMENTS
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_get_pending_es_payments AS
v_account_id NL_es_account.account_id%TYPE;
v_bill_seq billsummary.bill_seq@cdr_nz%TYPE;
v_invoice_total NUMBER(18, 2);
v_paid_amt NUMBER(18, 2);
v_implied_decimal NL_es_parameters.parameter_name%TYPE;
v_filehandle utl_file.file_type;
v_count NUMBER(10) := 0;
filename VARCHAR2(50);
v_error_num NL_es_error_log.error_code%TYPE;
v_error_msg NL_es_error_log.error_msg%TYPE;
v_date VARCHAR2(20);
v_outstanding_balance NUMBER(18, 2);
CURSOR cur_get_parents IS
SELECT account_id FROM NL_es_account;
CURSOR cur_get_report IS
SELECT bs.account_num,
bs.bill_seq,
bs.invoice_net_mny + bs.invoice_tax_mny AS total_invoice_mny,
nvl(SUM(atb.amount_mny), 0) AS paid_amt,
(bs.invoice_net_mny + bs.invoice_tax_mny -
nvl(SUM(atb.amount_mny), 0)) AS outstanding_balance
FROM billsummary@cdr_nz bs, allocationtobill@cdr_nz atb
WHERE bs.account_num = atb.account_num(+) AND
bs.bill_seq = atb.bill_seq(+) AND bs.account_num = v_account_id
GROUP BY bs.account_num,
bs.invoice_net_mny + bs.invoice_tax_mny,
bs.bill_seq
HAVING(bs.invoice_net_mny + bs.invoice_tax_mny) > nvl(SUM(atb.amount_mny), 0)
ORDER BY bs.account_num, bs.bill_seq;
BEGIN
SELECT int_value
INTO v_implied_decimal
FROM NL_es_parameters
WHERE parameter_name LIKE 'Implied Decimel';
v_date := to_char(SYSDATE, 'DDMMYYYYHH24MISS');
FOR rec_get_parents IN cur_get_parents LOOP
v_account_id := rec_get_parents.account_id;
IF (v_count = 0) THEN
filename := 'es_parent_outstanding_' || v_date || '.txt';
v_filehandle := utl_file.fopen('REPORTS_DIR', filename, 'w');
utl_file.putf(v_filehandle,
'ES_PARENT_ACCOUNT_ID' || ' ' || ' BILL_SEQ' || ' ' ||
' INVOICE_TOTAL' || ' PAID_AMT' ||
' OUTSTANDING_BALANCE');
utl_file.new_line(v_filehandle);
END IF;
FOR rec_get_report IN cur_get_report LOOP
v_bill_seq := rec_get_report.bill_seq;
v_invoice_total := (rec_get_report.total_invoice_mny /
(10 ** v_implied_decimal));
v_paid_amt := (rec_get_report.paid_amt /
(10 ** v_implied_decimal));
v_outstanding_balance := (rec_get_report.outstanding_balance /
(10 ** v_implied_decimal));
utl_file.putf(v_filehandle,
lpad(to_char(v_account_id), 20) ||
lpad(to_char(v_bill_seq), 20) ||
lpad(to_char(v_invoice_total), 20) ||
lpad(to_char(v_paid_amt), 20) ||
lpad(to_char(v_outstanding_balance), 20));
utl_file.new_line(v_filehandle);
v_count := v_count + 1;
END LOOP;
END LOOP;
IF utl_file.is_open(v_filehandle) THEN
--closing the filehandle for the feed file
utl_file.fclose(v_filehandle);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_num := SQLCODE;
v_error_msg := 'error in es report generation for es parent' ||
v_account_id || SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('es report generation', v_error_msg, SYSDATE, v_error_num);
COMMIT;
END;
=======SP_GET_PENDING_ES_PAYMENTS
create or replace procedure estaple.sp_ledger_payment_update
as
v_account_id NL_es_home_sys_account.account_id%type;
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%type;
v_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%type;
v_is_new NL_es_home_sys_account.is_new%type;
v_payment_split NL_es_payment_split.payment_amount%type;
v_outstanding_bal NL_es_cdr_bill_invoice.outstanding_bal%type;
v_old_outstanding NL_es_hbs_ledger.outstanding_at_parent%type;
cursor cur_get_accounts
is
select bh.account_id,bh.home_sys_account_id,bh.invoice_seq_no,bh.is_new
from NL_es_home_sys_account bh where bh.is_active=1 and bh.enrollment_date<'1-feb-2012'
and is_new=0
and bh.account_id='1007253782';
begin
for rec_get_accounts in cur_get_accounts loop
v_account_id := rec_get_accounts.account_id;
v_home_sys_account_id :=rec_get_accounts.home_sys_account_id;
v_invoice_seq_no :=rec_get_accounts.invoice_seq_no;
v_is_new := rec_get_accounts.is_new;
select bc.amount_paid,bc.outstanding_bal
into v_payment_split,v_outstanding_bal
from NL_es_cdr_bill_invoice bc
where bc.account_id=v_account_id
and bc.invoice_seq_no=v_invoice_seq_no and
bc.invoice_amount>=0;
select b.outstanding_at_parent
into v_old_outstanding
from NL_es_hbs_ledger b
where b.child_account_id=v_account_id;
update NL_es_hbs_ledger b
set b.outstanding_at_parent=b.outstanding_at_parent-v_payment_split
where b.child_account_id= v_account_id;
if (v_old_outstanding-v_payment_split)<>v_outstanding_bal
then
dbms_output.put_line('mismatch in account id'||v_account_id);
end if;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
=======SP_LOG_BILL_INV_ERROR
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_log_bill_inv_error(p_module_name VARCHAR2,
p_error_code VARCHAR2,
p_error_msg IN OUT VARCHAR2,
p_error_datetime DATE) IS
--version 1.0
--created by - Sumit Aroro(258075)
--Purpose - To log errors of bill invoice in error table
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
(p_module_name, p_error_code, p_error_msg, p_error_datetime);
COMMIT;
END sp_log_bill_inv_error;
=======SP_LOG_ERROR
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_log_error(p_module_name VARCHAR2,
p_error_code VARCHAR2,
p_error_msg IN OUT VARCHAR2,
p_error_datetime DATE,
p_es_account_id VARCHAR2,
p_child_es_account_id VARCHAR2,
p_cust_acq_status NUMBER
) IS
-- Purpose : To log the errors into ES database
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
(p_module_name, p_error_code, p_error_msg, p_error_datetime);
p_error_msg := 'HBS account id of ES child account is inactive';
INSERT INTO NL_es_cust_acq_response
(es_account_id,
es_child_account_id,
cust_acq_status,
status_desc,
acq_date
)
VALUES
(p_es_account_id,
p_child_es_account_id,
p_cust_acq_status,
p_error_msg,
p_error_datetime
);
COMMIT;
END;
==========SP_PARENT_RECON2
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_parent_recon2(p_bill_date DATE) IS
v_parent_id NL_es_home_sys_account.parent_account_id%TYPE;
v_parent_cust_ref account.customer_ref@cdr_nz%TYPE;
v_ssa NL_es_account.ssa%TYPE;
v_last_bill_seq NL_es_account.last_invoice_seq_no%TYPE;
v_excess NUMBER(10, 2);
v_expected NUMBER(10, 2);
v_remarks VARCHAR2(200);
v_balance_fwd NUMBER(10, 2);
v_charges NUMBER(10, 2);
v_last_bill_dtm DATE;
v_payment_mny NUMBER(10, 2);
v_failed_payment NUMBER(10, 2);
v_net_payment NUMBER(10, 2);
v_missed_amt NUMBER(10, 2);
v_actual_bill NUMBER(10, 2);
v_diff NUMBER(10, 2);
v_excess_diff NUMBER(10, 2);
v_missed_diff NUMBER(10, 2);
v_parent_adjustment NUMBER(10, 2);
v_net_diff NUMBER;
CURSOR cur_parent_acc IS
SELECT DISTINCT parent_account_id
FROM NL_es_home_sys_account h
WHERE h.is_active = 1 AND h.enrollment_date < p_bill_date
AND h.parent_account_id IN
( SELECT parent_account_id
FROM b200203226.parent_recon
);
BEGIN
FOR rec_parent_acc IN cur_parent_acc LOOP
v_parent_id := rec_parent_acc.parent_account_id;
BEGIN
SELECT customer_ref
INTO v_parent_cust_ref
FROM account@cdr_nz
WHERE account_num = v_parent_id;
SELECT ssa, last_invoice_seq_no
INTO v_ssa, v_last_bill_seq
FROM NL_es_account a
WHERE account_id = v_parent_id;
-- excess
SELECT nvl(SUM(ep.remaining_amount), 0) / 1000
INTO v_excess
FROM NL_es_excess_payment ep
WHERE ep.account_id = v_parent_id;
BEGIN
--expected amount calculation
SELECT NVL(SUM(BS.BALANCE_OUT_MNY / 1000), 0)
INTO V_EXPECTED
FROM BILLSUMMARY@CDR_NZ BS INNER JOIN NL_ES_HOME_SYS_ACCOUNT HB ON HB.HOME_SYS_ACCOUNT_ID = BS.ACCOUNT_NUM
WHERE HB.PARENT_ACCOUNT_ID = V_PARENT_ID AND HB.IS_ACTIVE = 1 AND
ENROLLMENT_DATE < P_BILL_DATE AND
BS.BILL_DTM = P_BILL_DATE AND BS.BILL_STATUS = 1 AND
BS.BALANCE_OUT_MNY >= 0;
/* SELECT nvl(SUM(bs.account_balance / 1000), 0)
INTO v_expected
FROM pvaccountbalance@cdr_nz bs INNER JOIN NL_es_home_sys_account hb ON hb.home_sys_account_id = bs.account_num
WHERE hb.parent_account_id = v_parent_id AND hb.is_active = 1 AND
enrollment_date < p_bill_date ;*/
EXCEPTION
WHEN no_data_found THEN
v_expected := 0;
END;
BEGIN
--calculation of charges,balance_forward
IF v_last_bill_seq = 0 THEN
v_balance_fwd := 0;
SELECT nvl(SUM(p.charge), 0) / 1000
INTO v_charges
FROM NL_es_post_charges_log p
WHERE p.es_account_id = v_parent_id;
ELSE
BEGIN
SELECT actual_bill_dtm, balance_out_mny / 1000
INTO v_last_bill_dtm, v_balance_fwd
FROM billsummary@cdr_nz
WHERE account_num = v_parent_id AND
bill_dtm = add_months(p_bill_date, -1) AND
bill_status = 1;
EXCEPTION
WHEN no_data_found THEN
v_remarks := 'LAST BILL NOT FOUND';
END;
/* SELECT SUM(p.charge) / 1000
/* INTO v_charges
FROM NL_es_post_charges_log p
WHERE p.es_account_id = v_parent_id AND
p.charge_post_date >= v_last_bill_dtm;*/
SELECT SUM(one_off_number) / 1000
INTO v_charges
FROM custproductcharge@cdr_nz cpc INNER JOIN custoverrideprice@cdr_nz co ON cpc.customer_ref = co.customer_ref AND cpc.product_seq = co.product_seq AND (cpc_status = 2 OR last_taxed_cps_id IS NULL) AND co.customer_ref IN (SELECT b.account_id
FROM NL_es_home_sys_account b
WHERE b.parent_account_id =
v_parent_id) AND charge_type = 1;
END IF;
END;
--payments
BEGIN
v_failed_payment := 0;
v_net_payment := 0;
SELECT nvl(SUM(account_payment_mny) / 1000, 0)
INTO v_payment_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_parent_id AND created_dtm >= v_last_bill_dtm;
EXCEPTION
WHEN no_data_found THEN
v_payment_mny := 0;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny) / 1000, 0)
INTO v_failed_payment
FROM accountpayment@cdr_nz
WHERE account_num = v_parent_id AND failed_dtm > v_last_bill_dtm;
EXCEPTION
WHEN no_data_found THEN
v_failed_payment := 0;
END;
v_net_payment := v_payment_mny - v_failed_payment;
BEGIN
--missed bill
v_missed_amt := 0;
/*SELECT nvl(SUM(i.outstanding_bal) / 1000, 0)
INTO v_missed_amt
FROM NL_es_cdr_bill_invoice i INNER JOIN NL_es_home_sys_account h ON i.account_id = h.account_id AND i.invoice_seq_no = h.invoice_seq_no
WHERE h.parent_account_id = v_parent_id AND
i.end_date <> p_bill_date AND h.is_active = 1 AND
i.invoice_seq_no = h.invoice_seq_no AND
h.enrollment_date < p_bill_date AND i.outstanding_bal >= 0;*/
EXCEPTION
WHEN no_data_found THEN
v_missed_amt := 0;
NULL;
END;
BEGIN
--Adjustment at parent
SELECT nvl(SUM(adjustment_net_mny) / 1000, 0)
INTO v_parent_adjustment
FROM adjustment@cdr_nz
WHERE account_num = v_parent_id AND adjustment_status = 3;
EXCEPTION
WHEN no_data_found THEN
v_parent_adjustment := 0;
NULL;
END;
v_actual_bill := v_charges + v_balance_fwd - v_net_payment;
-- - V_PARENT_ADJUSTMENT;
v_diff := v_expected - v_actual_bill;
v_excess_diff := v_diff - v_excess;
v_missed_diff := v_excess_diff + v_missed_amt - v_parent_adjustment;
v_net_diff := v_missed_diff - v_parent_adjustment;
IF (v_missed_diff > -5 AND v_missed_diff < 5) THEN
v_remarks := 'OK';
ELSE
v_remarks := 'difference of ' || v_missed_diff;
END IF;
END;
INSERT INTO NL_parent_recon
VALUES
(v_parent_id,
v_parent_cust_ref,
v_ssa,
p_bill_date,
v_expected,
v_charges,
v_balance_fwd,
v_net_payment,
v_actual_bill,
v_diff,
v_excess,
v_excess_diff,
v_missed_amt,
v_missed_diff,
v_remarks,
SYSDATE,
v_parent_adjustment,
v_net_diff);
COMMIT;
END LOOP;
END sp_parent_recon2;
=============SP_PARENT_RECON_SSA
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_parent_recon_ssa(p_bill_date DATE, p_ssa varchar2) IS
v_parent_id NL_es_home_sys_account.parent_account_id%TYPE;
v_parent_cust_ref account.customer_ref@cdr_nz%TYPE;
v_ssa NL_es_account.ssa%TYPE;
v_last_bill_seq NL_es_account.last_invoice_seq_no%TYPE;
v_excess NUMBER(10, 2);
v_expected NUMBER(10, 2);
v_remarks VARCHAR2(200);
v_balance_fwd NUMBER(10, 2);
v_charges NUMBER(10, 2);
v_last_bill_dtm DATE;
v_payment_mny NUMBER(10, 2);
v_failed_payment NUMBER(10, 2);
v_net_payment NUMBER(10, 2);
v_missed_amt NUMBER(10, 2);
v_actual_bill NUMBER(10, 2);
v_diff NUMBER(10, 2);
v_excess_diff NUMBER(10, 2);
v_missed_diff NUMBER(10, 2);
v_parent_adjustment NUMBER(10, 2);
v_net_diff NUMBER;
CURSOR cur_parent_acc IS
SELECT DISTINCT parent_account_id
FROM NL_es_home_sys_account h
WHERE h.is_active = 1 AND h.enrollment_date < p_bill_date
AND h.ssa in(p_ssa);
BEGIN
FOR rec_parent_acc IN cur_parent_acc LOOP
v_parent_id := rec_parent_acc.parent_account_id;
BEGIN
SELECT customer_ref
INTO v_parent_cust_ref
FROM account@cdr_nz
WHERE account_num = v_parent_id;
SELECT ssa, last_invoice_seq_no
INTO v_ssa, v_last_bill_seq
FROM NL_es_account a
WHERE account_id = v_parent_id;
-- excess
SELECT nvl(SUM(ep.remaining_amount), 0) / 1000
INTO v_excess
FROM NL_es_excess_payment ep
WHERE ep.account_id = v_parent_id;
BEGIN
--expected amount calculation
SELECT nvl(SUM(bs.balance_out_mny / 1000), 0)
INTO v_expected
FROM billsummary@cdr_nz bs INNER JOIN NL_es_home_sys_account hb ON hb.home_sys_account_id = bs.account_num
WHERE hb.parent_account_id = v_parent_id AND hb.is_active = 1 AND
enrollment_date < p_bill_date AND bs.bill_dtm = p_bill_date AND
bs.bill_status = 1 AND bs.balance_out_mny >= 0;
EXCEPTION
WHEN no_data_found THEN
v_expected := 0;
END;
BEGIN
--calculation of charges,balance_forward
IF v_last_bill_seq = 0 THEN
v_balance_fwd := 0;
SELECT nvl(SUM(p.charge), 0) / 1000
INTO v_charges
FROM NL_es_post_charges_log p
WHERE p.es_account_id = v_parent_id;
ELSE
BEGIN
SELECT actual_bill_dtm, balance_out_mny / 1000
INTO v_last_bill_dtm, v_balance_fwd
FROM billsummary@cdr_nz
WHERE account_num = v_parent_id AND
bill_dtm = add_months(p_bill_date, -1) AND
bill_status = 1;
EXCEPTION
WHEN no_data_found THEN
v_remarks := 'LAST BILL NOT FOUND';
END;
SELECT SUM(p.charge) / 1000
INTO v_charges
FROM NL_es_post_charges_log p
WHERE p.es_account_id = v_parent_id AND
p.charge_post_date >= v_last_bill_dtm;
END IF;
END;
--payments
BEGIN
v_failed_payment := 0;
v_net_payment := 0;
SELECT nvl(SUM(account_payment_mny) / 1000, 0)
INTO v_payment_mny
FROM accountpayment@cdr_nz
WHERE account_num = v_parent_id AND created_dtm >= v_last_bill_dtm;
EXCEPTION
WHEN no_data_found THEN
v_payment_mny := 0;
END;
BEGIN
SELECT nvl(SUM(account_payment_mny) / 1000, 0)
INTO v_failed_payment
FROM accountpayment@cdr_nz
WHERE account_num = v_parent_id AND failed_dtm > v_last_bill_dtm;
EXCEPTION
WHEN no_data_found THEN
v_failed_payment := 0;
END;
v_net_payment := v_payment_mny - v_failed_payment;
BEGIN
--missed bill
SELECT nvl(SUM(i.outstanding_bal) / 1000, 0)
INTO v_missed_amt
FROM NL_es_cdr_bill_invoice i INNER JOIN NL_es_home_sys_account h ON i.account_id = h.account_id AND i.invoice_seq_no = h.invoice_seq_no
WHERE h.parent_account_id = v_parent_id AND
i.end_date <> p_bill_date AND h.is_active = 1 AND i.invoice_seq_no=h.invoice_seq_no
and h.enrollment_date < p_bill_date AND i.outstanding_bal >= 0;
EXCEPTION
WHEN no_data_found THEN
v_missed_amt := 0;
NULL;
END;
BEGIN
--Adjustment at parent
SELECT nvl(SUM(adjustment_net_mny) / 1000, 0)
INTO v_parent_adjustment
FROM adjustment@cdr_nz
WHERE account_num = v_parent_id AND adjustment_status = 3;
EXCEPTION
WHEN no_data_found THEN
v_parent_adjustment := 0;
NULL;
END;
v_actual_bill := v_charges + v_balance_fwd - v_net_payment;
-- - V_PARENT_ADJUSTMENT;
v_diff := v_expected - v_actual_bill;
v_excess_diff := v_diff - v_excess;
v_missed_diff := v_excess_diff + v_missed_amt - v_parent_adjustment;
v_net_diff := v_missed_diff - v_parent_adjustment;
IF (v_missed_diff > -5 AND v_missed_diff < 5) THEN
v_remarks := 'OK';
ELSE
v_remarks := 'difference of ' || v_missed_diff;
END IF;
END;
INSERT INTO NL_parent_recon
VALUES
(v_parent_id,
v_parent_cust_ref,
v_ssa,
p_bill_date,
v_expected,
v_charges,
v_balance_fwd,
v_net_payment,
v_actual_bill,
v_diff,
v_excess,
v_excess_diff,
v_missed_amt,
v_missed_diff,
v_remarks,
SYSDATE,
v_parent_adjustment,
v_net_diff);
COMMIT;
END LOOP;
END sp_parent_recon;
===========SP_PAYMENT_ROUNDING
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_payment_rounding IS
v_trans_id NL_es_payment.transaction_id%TYPE;
v_acc_id NL_es_Payment.account_id%TYPE;
v_es_payment NL_es_payment.payment_amount%TYPE;
v_pay_split NUMBER;
v_inv_amt NL_es_cdr_bill_invoice.invoice_amount%TYPE;
v_diff NUMBER;
v_inv_max NUMBER;
v_max_acc NUMBER;
v_acc NL_es_cdr_bill_invoice.account_id%TYPE;
v_err_num varchar2(10);
v_err_msg varchar2(100);
CURSOR cur_es_payments IS
select ep.transaction_id, ep.account_id
from NL_es_payment ep
where ep.account_id in
(SELECT account_num
FROM accountpayment@cdr_nz acc_pay INNER JOIN NL_es_account es_acc ON acc_pay.account_num = es_acc.account_id
WHERE es_acc.next_pay_seq <= acc_pay.account_payment_seq AND
acc_pay.account_payment_status = 1 AND
acc_pay.deposit_boo = 'F');
CURSOR cur_inv IS
SELECT account_id, invoice_amount--, invoice_seq_no
FROM NL_es_cdr_bill_invoice
WHERE (account_id, invoice_seq_no) IN
(SELECT account_id, invoice_seq_no
FROM NL_es_home_sys_account hsa
WHERE parent_account_id = v_acc_id);
BEGIN
OPEN cur_es_payments;
FETCH cur_es_payments
INTO v_trans_id, v_acc_id;
--EXIT WHEN cur_es_payments%NOTFOUND;
WHILE cur_es_payments%FOUND LOOP
SELECT sum(eps.payment_amount)
INTO v_pay_split
FROM NL_es_payment_split eps
where eps.transaction_id = v_trans_id;
SELECT p.payment_amount
INTO v_es_payment
FROM NL_es_payment p
WHERE p.account_id = v_acc_id AND
p.transaction_id=v_trans_id;
/* SELECT ecb.invoice_amount
FROM NL_es_cdr_bill_invoice ecb
WHERE ecb.account_id IN (select ehs.account_id
from NL_es_home_sys_account ehs
where ehs.parent_account_id=v_acc_id)
AND ecb.invoice_seq_no=(select max(ecb.invoice_seq_no)from NL_es_cdr_bill_invoice ecb where );*/
/* SELECT ec.account_id, ec.invoice_amount
INTO v_acc, v_inv_amt
FROM NL_es_cdr_bill_invoice ec
WHERE ec.account_id IN
(SELECT h.home_sys_account_id
FROM NL_es_home_sys_account h
WHERE h.parent_account_id = v_acc_id)
AND ec.invoice_amount =
(SELECT max(ec.invoice_amount)
from NL_es_cdr_bill_invoice ec
where ec.account_id = v_acc_id);*/
v_inv_max:=0;
v_max_acc:=null;
OPEN cur_inv;
FETCH cur_inv
INTO v_acc,v_inv_amt;
-- EXIT WHEN cur_inv%NOTFOUND;
WHILE cur_inv%FOUND LOOP
-- v_tmp := v_inv_amt;
IF v_inv_max<v_inv_amt THEN
v_inv_max:=v_inv_amt;
v_max_acc:=v_acc;
END IF;
END LOOP;
END LOOP;
IF v_pay_split <> v_es_payment THEN
v_diff := v_es_payment - v_pay_split;
UPDATE NL_es_cdr_bill_invoice bi
SET bi.amount_paid=bi.amount_paid + v_diff
WHERE bi.account_id=v_max_acc
AND bi.invoice_amount=v_inv_max;
UPDATE NL_es_payment_split ps
SET ps.payment_amount = ps.payment_amount + v_diff
WHERE ps.account_id=v_max_acc;
COMMIT;
END IF;
EXCEPTION
WHEN no_data_found THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Payment Rounding:' ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Payment Rounding', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Payment Rounding:' ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Payment Rounding', v_err_msg, SYSDATE, v_err_num);
COMMIT;
END sp_payment_rounding;
==========SP.PAYMENT_ROUNDING
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_payment_rounding IS
v_trans_id NL_es_payment.transaction_id%TYPE;
v_acc_id NL_es_Payment.account_id%TYPE;
v_es_payment NL_es_payment.payment_amount%TYPE;
v_pay_split NUMBER;
v_inv_amt NL_es_cdr_bill_invoice.invoice_amount%TYPE;
v_diff NUMBER;
v_inv_max NUMBER;
v_max_acc NUMBER;
v_acc NL_es_cdr_bill_invoice.account_id%TYPE;
v_err_num varchar2(10);
v_err_msg varchar2(100);
CURSOR cur_es_payments IS
select ep.transaction_id, ep.account_id
from NL_es_payment ep
where ep.account_id in
(SELECT account_num
FROM accountpayment@cdr_nz acc_pay INNER JOIN NL_es_account es_acc ON acc_pay.account_num = es_acc.account_id
WHERE es_acc.next_pay_seq <= acc_pay.account_payment_seq AND
acc_pay.account_payment_status = 1 AND
acc_pay.deposit_boo = 'F');
CURSOR cur_inv IS
SELECT account_id, invoice_amount--, invoice_seq_no
FROM NL_es_cdr_bill_invoice
WHERE (account_id, invoice_seq_no) IN
(SELECT account_id, invoice_seq_no
FROM NL_es_home_sys_account hsa
WHERE parent_account_id = v_acc_id);
BEGIN
OPEN cur_es_payments;
FETCH cur_es_payments
INTO v_trans_id, v_acc_id;
--EXIT WHEN cur_es_payments%NOTFOUND;
WHILE cur_es_payments%FOUND LOOP
SELECT sum(eps.payment_amount)
INTO v_pay_split
FROM NL_es_payment_split eps
where eps.transaction_id = v_trans_id;
SELECT p.payment_amount
INTO v_es_payment
FROM NL_es_payment p
WHERE p.account_id = v_acc_id AND
p.transaction_id=v_trans_id;
/* SELECT ecb.invoice_amount
FROM NL_es_cdr_bill_invoice ecb
WHERE ecb.account_id IN (select ehs.account_id
from NL_es_home_sys_account ehs
where ehs.parent_account_id=v_acc_id)
AND ecb.invoice_seq_no=(select max(ecb.invoice_seq_no)from NL_es_cdr_bill_invoice ecb where );*/
/* SELECT ec.account_id, ec.invoice_amount
INTO v_acc, v_inv_amt
FROM NL_es_cdr_bill_invoice ec
WHERE ec.account_id IN
(SELECT h.home_sys_account_id
FROM NL_es_home_sys_account h
WHERE h.parent_account_id = v_acc_id)
AND ec.invoice_amount =
(SELECT max(ec.invoice_amount)
from NL_es_cdr_bill_invoice ec
where ec.account_id = v_acc_id);*/
v_inv_max:=0;
v_max_acc:=null;
OPEN cur_inv;
FETCH cur_inv
INTO v_acc,v_inv_amt;
-- EXIT WHEN cur_inv%NOTFOUND;
WHILE cur_inv%FOUND LOOP
-- v_tmp := v_inv_amt;
IF v_inv_max<v_inv_amt THEN
v_inv_max:=v_inv_amt;
v_max_acc:=v_acc;
END IF;
END LOOP;
END LOOP;
IF v_pay_split <> v_es_payment THEN
v_diff := v_es_payment - v_pay_split;
UPDATE NL_es_cdr_bill_invoice bi
SET bi.amount_paid=bi.amount_paid + v_diff
WHERE bi.account_id=v_max_acc
AND bi.invoice_amount=v_inv_max;
UPDATE NL_es_payment_split ps
SET ps.payment_amount = ps.payment_amount + v_diff
WHERE ps.account_id=v_max_acc;
COMMIT;
END IF;
EXCEPTION
WHEN no_data_found THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Payment Rounding:' ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Payment Rounding', v_err_msg, SYSDATE, v_err_num);
COMMIT;
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := 'Error in Payment Rounding:' ||
substr(SQLCODE, 1, 100);
--Inserting into NL_es_error_log
INSERT INTO NL_es_error_log
(module_name, error_msg, error_datetime, error_code)
VALUES
('Payment Rounding', v_err_msg, SYSDATE, v_err_num);
COMMIT;
END sp_payment_rounding;
===========SP_POST_DISCOUNT
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_post_discount
is
v_account_id NL_es_home_sys_account.account_id%type;
v_charge NL_es_cdr_bill_invoice.charges%type;
v_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%type;
v_invoice_amount NL_es_cdr_bill_invoice.invoice_amount%type;
CURSOR cur_get_accounts iS
SELECT bd.account_id, bd.discount_amount
FROM NL_es_discount_split bd
WHERE bd.discount_app_date = '1-may-2012';
BEGIN
FOR rec_get_accounts IN cur_get_accounts LOOP
v_account_id := rec_get_accounts.account_id;
v_charge := rec_get_accounts.discount_amount;
select bh.invoice_seq_no
into v_invoice_seq_no
from NL_es_home_sys_account bh where bh.account_id=v_account_id;
select bc.invoice_amount
into v_invoice_amount
from NL_es_cdr_bill_invoice bc where bc.account_id=v_account_id and
invoice_seq_no=v_invoice_seq_no;
if (v_invoice_amount>=0) then
sp_create_product(v_account_id,
v_account_id,
-v_charge,
190,
v_invoice_seq_no,
'1-may-2012',
'31-may-2012');
end if;
END LOOP;
END;
========SP_REBILL_CORRECT
CREATE OR REPLACE PROCEDURE ESTAPLE.SP_REBILL_CORRECT(P_REBILL_DTM IN DATE,
P_CURRENT_BILL_DTM DATE) IS
BILL_DATE DATE;
REBILL_DATE DATE;
REBILL_BAL NUMBER(20);
BILL_BAL NUMBER(20);
NET_CHARGE NUMBER(20);
PAYMENTS_MNY NUMBER(20);
V_REBILL_VER NUMBER;
CURSOR ACC IS
SELECT *
FROM NL_ES_HOME_SYS_ACCOUNT H
WHERE H.HOME_SYS_ACCOUNT_ID IN
('1007572195', '1007571623', '1007571369', '1007553592', '1007534809',
'1007534397', '1007606799', '1007529729', '1007539178', '1007538936',
'1007543179', '1007543180', '1007543187', '1007545581', '1007548604',
'1007606072', '1007533903', '1007533983', '1007534565', '1007535659',
'1007535663', '1007535666', '1007535669', '1007535671', '1007537951',
'1007537974', '1007541756', '1007568487', '1007554014', '1007596798',
'1007608659', '1007554467', '1007560878', '1007568435', '1007600020',
'1007529749', '1007568785', '1007575211', '1007575209', '1007568512',
'1007578463', '1007578475', '1007542001', '1007541999', '1007541997',
'1007554425', '1007554404', '1007550623', '1007537707', '1007548786',
'1007545676', '1007531325', '1007568437', '1007568439', '1007549541',
'1007549538', '1007558279', '1007537948', '1007536500');
/* (SELECT ACCOUNT_NUM
FROM ACCOUNT@CDR_NZ
WHERE ACCOUNT_NUM IN
(SELECT BH.HOME_SYS_ACCOUNT_ID
FROM NL_ES_HOME_SYS_ACCOUNT BH INNER JOIN NL_ES_CDR_BILL_INVOICE BC ON BC.ACCOUNT_ID = BH.ACCOUNT_ID AND BH.IS_ACTIVE = 1 AND BC.BILL_CYCLE_DATE = P_REBILL_DTM AND BH.PARENT_ACCOUNT_ID IN (SELECT TO_CHAR(PR.PARENT_ID)
FROM NL_PARENT_RECON PR
WHERE PR.BILL_CYCLE_DATE =
P_CURRENT_BILL_DTM AND
PR.REMARKS <> 'OK') INNER JOIN BILLSUMMARY@CDR_NZ B ON(B.ACCOUNT_NUM = BH.HOME_SYS_ACCOUNT_ID AND BC.END_DATE = B.BILL_DTM)
WHERE BC.BILL_VERSION <> B.BILL_VERSION AND
BILL_STATUS IN (1, 7, 8)) AND
LAST_BILL_DTM = P_CURRENT_BILL_DTM) AND H.IS_NEW <> 1;*/
BEGIN
FOR ACC_REC IN ACC LOOP
BEGIN
SELECT ACTUAL_BILL_DTM, BALANCE_OUT_MNY, BILL_VERSION
INTO REBILL_DATE, REBILL_BAL, V_REBILL_VER
FROM BILLSUMMARY@CDR_NZ
WHERE ACCOUNT_NUM = ACC_REC.HOME_SYS_ACCOUNT_ID AND
BILL_DTM = P_REBILL_DTM AND BILL_STATUS IN (1, 7, 8);
IF ACC_REC.IS_NEW = 0 THEN
SELECT ACTUAL_BILL_DTM, BALANCE_OUT_MNY
INTO BILL_DATE, BILL_BAL
FROM BILLSUMMARY@CDR_NZ
WHERE ACCOUNT_NUM = ACC_REC.HOME_SYS_ACCOUNT_ID AND
BILL_DTM = P_REBILL_DTM AND BILL_STATUS NOT IN (1, 7, 8);
SELECT NVL(SUM(ACCOUNT_PAYMENT_MNY), 0)
INTO PAYMENTS_MNY
FROM ACCOUNTPAYMENT@CDR_NZ
WHERE ACCOUNT_NUM = ACC_REC.HOME_SYS_ACCOUNT_ID AND
DEPOSIT_BOO = 'F' AND CREATED_DTM > BILL_DATE AND
CREATED_DTM < REBILL_DATE AND ACCOUNT_PAYMENT_STATUS = 1 AND
ACCOUNT_PAYMENT_REF IN
(SELECT S.TRANSACTION_ID
FROM NL_ES_PAYMENT_SPLIT S
WHERE S.HOME_SYS_ACCOUNT_ID = ACC_REC.HOME_SYS_ACCOUNT_ID);
NET_CHARGE := REBILL_BAL - BILL_BAL + PAYMENTS_MNY;
IF NET_CHARGE <> 0 THEN
UPDATE NL_ES_POST_CHARGES_LOG P
SET P.CHARGE = P.CHARGE + NET_CHARGE
WHERE P.CHILD_ACCOUNT_ID = ACC_REC.ACCOUNT_ID AND
P.PRODUCT_ID = 188 AND
P.INVOICE_SEQ_NO = ACC_REC.INVOICE_SEQ_NO AND
P.LOGGED_DATE > P_CURRENT_BILL_DTM;
-- COMMIT;
SP_CORRECT_EVERYTHING(ACC_REC.ACCOUNT_ID);
END IF;
END IF;
UPDATE NL_ES_CDR_BILL_INVOICE I
SET I.BILL_VERSION = V_REBILL_VER
WHERE I.ACCOUNT_ID = ACC_REC.ACCOUNT_ID AND
I.END_DATE = P_REBILL_DTM;
COMMIT;
DBMS_OUTPUT.PUT_LINE(ACC_REC.PARENT_ACCOUNT_ID || ' ' ||
ACC_REC.HOME_SYS_ACCOUNT_ID || ' ' ||
NET_CHARGE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(ACC_REC.PARENT_ACCOUNT_ID || ' ' ||
ACC_REC.HOME_SYS_ACCOUNT_ID || ' error ');
END;
END LOOP;
END SP_REBILL_CORRECT;
========SP_RUN_POSTINGcreate or replace procedure estaple.sp_run_posting1(p_jobname varchar2, p_instance_no number, p_procedure1 varchar2
,p_billcycle1 date ) as
counter1 number;
jobname varchar2(21);
begin
jobname := p_jobname||'%';
loop
select count(*) into counter1 from user_scheduler_job_run_details urdl where
urdl.job_name like
jobname and urdl.STATUS='SUCCEEDED';
if (counter1 <= p_instance_no -1 ) then
DBMS_LOCK.sleep(60);
else
NL_create_job_instance1(p_procedure1,p_billcycle1);-------call posting code
dbms_output.put_line ('HI');
exit;
end if;
end loop;
end;
==========SP_RUN_POSING1
create or replace procedure estaple.sp_run_posting1(p_jobname varchar2, p_instance_no number, p_procedure1 varchar2
,p_billcycle1 date ) as
counter1 number;
jobname varchar2(21);
begin
jobname := p_jobname||'%';
loop
select count(*) into counter1 from user_scheduler_job_run_details urdl where
urdl.job_name like
jobname and urdl.STATUS='SUCCEEDED';
if (counter1 <= p_instance_no -1 ) then
DBMS_LOCK.sleep(60);
else
NL_create_job_instance1(p_procedure1,p_billcycle1);-------call posting code
dbms_output.put_line ('HI');
exit;
end if;
end loop;
end;
=============SP_RUN_POSTING2
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_special_post AS
v_account_id NL_es_home_sys_account.account_id%TYPE;
v_parent_account_id NL_es_home_sys_account.parent_account_id%TYPE;
v_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%TYPE;
v_customer_ref account.customer_ref@cdr_nz%TYPE;
v_product_name product.product_name@cdr_nz%TYPE;
v_product_start_date NL_es_cdr_bill_invoice.start_date%TYPE;
v_product_end_date NL_es_cdr_bill_invoice.end_date%TYPE;
v_adjustment_mny NL_es_cdr_bill_invoice.adjustment%TYPE;
v_payment_mny NL_es_cdr_bill_invoice.last_payment%TYPE;
v_charges NL_es_cdr_bill_invoice.charges%TYPE;
v_tariff_id tariff.tariff_id@cdr_nz%TYPE;
v_is_posted NL_es_cdr_bill_invoice.is_posted%TYPE;
v_product_attribute NL_es_cdr_bill_inv_det.line_item_code_desc%TYPE;
-- v_invoice_ref_no NL_es_gsm_bill_inv_det.invoice_ref_no%TYPE;--------- NotRequiredInMigration
-- v_invoice_ref_resets NL_es_gsm_bill_inv_det.invoice_ref_resets%TYPE;--------- NotRequiredInMigration
v_line_item_code_desc NL_es_cdr_bill_inv_det.line_item_code_desc%TYPE; --------- NotRequiredInMigration
v_line_item_code NL_es_cdr_bill_inv_det.line_item_code%TYPE; --------- NotRequiredInMigration
v_product_id custhasproduct.product_id@cdr_nz%TYPE;
v_line_item_amount NL_es_cdr_bill_inv_det.amount%TYPE; --------- NotRequiredInMigration
v_is_new NL_es_home_sys_account.is_new%TYPE;
v_balance_forward NL_es_cdr_bill_invoice.balance_forward%TYPE;
v_es_adjustment NL_es_cdr_bill_invoice.outstanding_bal%TYPE;
v_outstanding_bal NL_es_cdr_bill_invoice.outstanding_bal%TYPE; --------- Added as RequiredInMigration
v_home_sys_account_id NL_es_home_sys_account.home_sys_account_id%TYPE;
v_newstartdatetime custproductstatus.effective_dtm@cdr_nz%TYPE;
v_product_seq custproductstatus.product_seq@cdr_nz%TYPE;
v_count NUMBER(10) := 0;
v_enrollment_date NL_es_home_sys_account.enrollment_date%TYPE;
v_is_active NL_es_home_sys_account.is_active%TYPE;
v_prev_bill_run_date NL_es_cdr_bill_invoice.bill_run_date%TYPE;
v_current_bill_run_date NL_es_cdr_bill_invoice.bill_run_date%TYPE;
v_end_date NL_es_cdr_bill_invoice.end_date%TYPE;
v_left NL_es_cdr_bill_invoice.outstanding_bal%TYPE;
v_refunds_mny NL_es_cdr_bill_invoice.refunds%TYPE;
v_failed_payment_mny NL_es_cdr_bill_invoice.last_payment%TYPE;
--v_es_discount NL_es_cdr_bill_inv_det.amount%TYPE;
-- v_es_discount NL_es_cdr_bill_inv_det.amount%TYPE;
--Select all HBS child accounts whose charges are to be posted
v_errmsg pkg_es_post_charges_del.v_errmsg%TYPE;
v_errcode pkg_es_post_charges_del.v_errcode%TYPE;
CURSOR cur_cdr_accounts IS
SELECT a.account_id,
a.enrollment_date,
a.parent_account_id,
b.invoice_seq_no,
a.home_sys_account_id,
is_active
FROM NL_es_home_sys_account a INNER JOIN NL_es_cdr_bill_invoice b ON a.account_id = b.account_id
/* and a.home_sys_account_id = '3679'*/
--ROBIN: bill_cycle_date is updated when bill_invoices are fetched, and the arguement passed is the bill_run_date
--to compare we add the appropriate time period to the date
WHERE
-- a.line_of_business = pkg_es.line_of_business_cdr_code AND --------- NotRequiredInMigration
( a.parent_account_id = '1013116736' or a.account_id
in('1021342733')
) AND
/*b.fetch_status = 1 AND*/
/*a.ssa = p_ssa AND*/
b.is_posted = pkg_es.is_not_posted AND
a.is_active NOT IN (pkg_es.del_in_progress, pkg_es.deleted)
ORDER BY parent_account_id, account_id, invoice_seq_no ASC;
--Select each invoice details according to its line_item_code for a particular account and invoice
CURSOR cur_cdr_invoice_details IS
SELECT line_item_code, line_item_code_desc, amount
FROM NL_es_cdr_bill_inv_det a
WHERE a.account_id = v_account_id AND
a.invoice_seq_no = v_invoice_seq_no
ORDER BY line_item_code;
CURSOR cur_customer_products IS
SELECT chp.product_seq AS product_seq,
chp.product_id AS product_id,
cps.effective_dtm AS old_start_date,
cps.product_status AS product_status
FROM custhasproduct@cdr_nz chp INNER JOIN custproductstatus@cdr_nz cps ON chp.customer_ref = cps.customer_ref AND chp.product_seq = cps.product_seq
WHERE chp.customer_ref = v_customer_ref AND
cps.effective_dtm =
(SELECT MAX(cps1.effective_dtm)
FROM custproductstatus@cdr_nz cps1
WHERE cps1.customer_ref = v_customer_ref AND
cps1.product_seq = cps.product_seq) AND
cps.product_status = pkg_es.product_status_ok;
BEGIN
--Main procedure begins
--Select the tariff_id/Price Plan under which the prices for the products have been overridden
SELECT t.tariff_id
INTO v_tariff_id
FROM tariff@cdr_nz t
WHERE t.tariff_name = pkg_es.tariff_name AND
t.catalogue_change_id =
(SELECT catalogue_change_id
FROM cataloguechange@cdr_nz
WHERE catalogue_status = pkg_es.catalogue_status_live);
--For loop for each of the account
FOR rec_cdr_accounts IN cur_cdr_accounts LOOP
BEGIN
v_account_id := rec_cdr_accounts.account_id;
v_parent_account_id := rec_cdr_accounts.parent_account_id;
v_invoice_seq_no := rec_cdr_accounts.invoice_seq_no;
v_home_sys_account_id := rec_cdr_accounts.home_sys_account_id;
v_enrollment_date := rec_cdr_accounts.enrollment_date;
v_is_active := rec_cdr_accounts.is_active;
--Select the ES child customer against whose account these charges are to be posted
SELECT a.customer_ref
INTO v_customer_ref
FROM account@cdr_nz a
WHERE a.account_num = v_account_id;
SELECT start_date,
end_date,
bill_run_date,
charges,
adjustment,
last_payment,
is_posted,
balance_forward,
outstanding_bal
INTO v_product_start_date,
v_end_date,
v_current_bill_run_date,
v_charges,
v_adjustment_mny,
v_payment_mny,
v_is_posted,
v_balance_forward,
v_outstanding_bal
FROM NL_es_cdr_bill_invoice a
WHERE a.account_id = v_account_id AND
a.invoice_seq_no = v_invoice_seq_no;
IF v_product_start_date < v_enrollment_date THEN
v_product_start_date := v_enrollment_date;
END IF;
SELECT is_new
INTO v_is_new
FROM NL_es_home_sys_account
WHERE account_id = v_account_id;
IF (v_is_active <> pkg_es.del_in_progress) THEN
IF (v_is_new = 1) THEN
v_product_start_date := rec_cdr_accounts.enrollment_date;
v_newstartdatetime := rec_cdr_accounts.enrollment_date;
ELSE
v_newstartdatetime := v_product_start_date;
END IF; --v_is_new
v_product_end_date := v_end_date - 1;
END IF;
IF (v_is_posted = pkg_es.is_not_posted OR
v_is_active = pkg_es.del_in_progress) THEN
v_count := 0;
IF (v_is_active <> pkg_es.del_in_progress) THEN
IF (v_outstanding_bal < 0) THEN
FOR rec_cdr_invoice_details IN cur_cdr_invoice_details LOOP
v_line_item_code := rec_cdr_invoice_details.line_item_code;
v_line_item_amount := rec_cdr_invoice_details.amount;
IF (v_line_item_amount <> 0) THEN
CASE v_line_item_code
WHEN pkg_es.line_item_code_nrc THEN
v_line_item_code_desc := rec_cdr_invoice_details.line_item_code_desc;
v_product_attribute := v_line_item_code_desc || ' ' ||
v_home_sys_account_id;
v_product_name := pkg_es.cdr_one_time_charge;
WHEN pkg_es.line_item_code_rc THEN
v_line_item_code_desc := rec_cdr_invoice_details.line_item_code_desc;
v_product_attribute := v_line_item_code_desc || ' ' ||
v_home_sys_account_id;
v_product_name := pkg_es.cdr_periodic_charge;
WHEN pkg_es.line_item_code_usage THEN
v_line_item_code_desc := rec_cdr_invoice_details.line_item_code_desc;
v_product_attribute := v_line_item_code_desc || ' ' ||
v_home_sys_account_id;
v_product_name := pkg_es.cdr_usage_charge;
WHEN pkg_es.line_item_code_discount THEN
v_product_attribute := v_home_sys_account_id;
v_product_name := pkg_es.cdr_discount;
WHEN pkg_es.line_item_code_tax THEN
v_product_attribute := v_home_sys_account_id;
v_product_name := pkg_es.cdr_tax;
WHEN pkg_es.line_item_code_es_discount THEN
v_product_attribute := v_home_sys_account_id;
v_product_name := pkg_es.cdr_es_discount;
END CASE;
SELECT p.product_id
INTO v_product_id
FROM product@cdr_nz p
WHERE p.product_name = v_product_name;
-- create a new customer product instance
pkg_es_post_charges_del.sp_create_product_instance(v_customer_ref,
v_account_id,
v_product_id,
v_product_start_date,
v_product_end_date,
v_tariff_id,
v_line_item_amount,
v_product_attribute,
v_invoice_seq_no,
--v_invoice_ref_no,
--v_invoice_ref_resets,
v_parent_account_id,
pkg_es.line_of_business_cdr_code,
v_is_active);
END IF; --v_line_item_amount
END LOOP; --cur_cdr_invoice_details
INSERT INTO NL_es_hbs_payments
(account_id,
home_sys_account_id,
invoice_seq_no,
bill_ver,
hbs_payment,
failed_hbs_payment,
deposit,
refunds,
charge,
logged_date,
start_date,
end_date)
VALUES
(v_account_id,
v_home_sys_account_id,
v_invoice_seq_no,
1,
0,
0,
0,
0,
0,
SYSDATE,
v_product_start_date,
v_product_end_date);
END IF; --(v_is_new = 1 AND v_payment_mny <> -v_balance_forward)
--Posting Adjustment
IF (v_adjustment_mny <> 0) THEN
v_product_name := pkg_es.cdr_adjustment;
v_product_attribute := v_home_sys_account_id;
SELECT p.product_id
INTO v_product_id
FROM product@cdr_nz p
WHERE p.product_name = v_product_name;
pkg_es_post_charges_del.sp_create_product_instance(v_customer_ref,
v_account_id,
v_product_id,
v_product_start_date,
v_product_end_date,
v_tariff_id,
v_adjustment_mny,
v_product_attribute,
v_invoice_seq_no,
v_parent_account_id,
pkg_es.line_of_business_cdr_code,
v_is_active);
END IF; --v_adjustment_mny
UPDATE NL_es_cdr_bill_invoice
SET is_posted = pkg_es.is_posted
WHERE account_id = v_account_id AND
invoice_seq_no = v_invoice_seq_no;
COMMIT;
END IF; --(v_outstanding_bal >= 0)
/*ELSE
--(v_is_active <> pkg_es.del_in_progress)
SELECT outstanding_bal
INTO v_es_adjustment
FROM NL_es_cdr_bill_invoice
WHERE account_id = v_account_id AND
invoice_seq_no =
(SELECT MAX(invoice_seq_no)
FROM NL_es_cdr_bill_invoice a
WHERE account_id = v_account_id AND
a.outstanding_bal >= 0);
IF (v_es_adjustment <> 0) THEN
v_product_attribute := v_home_sys_account_id;
v_product_name := pkg_es.cdr_es_adjustment;
SELECT p.product_id
INTO v_product_id
FROM product@cdr_nz p
WHERE p.product_name = v_product_name;
sp_create_product_instance(v_customer_ref,
v_account_id,
v_product_id,
v_product_start_date,
v_product_end_date,
v_tariff_id,
-v_es_adjustment,
v_product_attribute,
v_invoice_seq_no,
-- v_invoice_ref_no,
--v_invoice_ref_resets,
v_parent_account_id,
pkg_es.line_of_business_cdr_code,
v_is_active);
UPDATE NL_es_home_sys_account
SET is_active = pkg_es.deleted
WHERE account_id = v_account_id;
END IF; --(v_es_adjustment <> 0)*/
END IF; --(v_is_active <> pkg_es.del_in_progress)
IF (v_is_new IN (1, 2) AND v_outstanding_bal > 0) THEN
UPDATE NL_es_home_sys_account
SET is_new = 0
WHERE account_id = v_account_id;
END IF; --(v_is_new = 1)
-- END IF; --v_is_posted
EXCEPTION
WHEN no_data_found THEN
IF (cur_cdr_accounts%ISOPEN) THEN
CLOSE cur_cdr_accounts;
ELSIF (cur_cdr_invoice_details%ISOPEN) THEN
CLOSE cur_cdr_invoice_details;
ELSIF (cur_customer_products%ISOPEN) THEN
CLOSE cur_customer_products;
END IF;
ROLLBACK;
v_errmsg := 'ES child account no:' || v_account_id ||
'ES parent account no:' || v_parent_account_id || ' ' ||
SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Posting Charges', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN dup_val_on_index THEN
IF (cur_cdr_accounts%ISOPEN) THEN
CLOSE cur_cdr_accounts;
ELSIF (cur_cdr_invoice_details%ISOPEN) THEN
CLOSE cur_cdr_invoice_details;
ELSIF (cur_customer_products%ISOPEN) THEN
CLOSE cur_customer_products;
END IF;
ROLLBACK;
v_errmsg := 'ES child account no:' || v_account_id ||
'ES parent account no:' || v_parent_account_id || ' ' ||
SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Posting Charges', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN invalid_cursor THEN
IF (cur_cdr_accounts%ISOPEN) THEN
CLOSE cur_cdr_accounts;
ELSIF (cur_cdr_invoice_details%ISOPEN) THEN
CLOSE cur_cdr_invoice_details;
ELSIF (cur_customer_products%ISOPEN) THEN
CLOSE cur_customer_products;
END IF;
ROLLBACK;
v_errmsg := 'ES child account no:' || v_account_id ||
'ES parent account no:' || v_parent_account_id || ' ' ||
SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Posting Charges', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN too_many_rows THEN
IF (cur_cdr_accounts%ISOPEN) THEN
CLOSE cur_cdr_accounts;
ELSIF (cur_cdr_invoice_details%ISOPEN) THEN
CLOSE cur_cdr_invoice_details;
ELSIF (cur_customer_products%ISOPEN) THEN
CLOSE cur_customer_products;
END IF;
ROLLBACK;
v_errmsg := 'ES child account no:' || v_account_id ||
'ES parent account no:' || v_parent_account_id || ' ' ||
SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Posting Charges', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN cursor_already_open THEN
IF (cur_cdr_accounts%ISOPEN) THEN
CLOSE cur_cdr_accounts;
ELSIF (cur_cdr_invoice_details%ISOPEN) THEN
CLOSE cur_cdr_invoice_details;
ELSIF (cur_customer_products%ISOPEN) THEN
CLOSE cur_customer_products;
END IF;
ROLLBACK;
v_errmsg := 'ES child account no:' || v_account_id ||
'ES parent account no:' || v_parent_account_id || ' ' ||
SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Posting Charges', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN case_not_found THEN
IF (cur_cdr_accounts%ISOPEN) THEN
CLOSE cur_cdr_accounts;
ELSIF (cur_cdr_invoice_details%ISOPEN) THEN
CLOSE cur_cdr_invoice_details;
ELSIF (cur_customer_products%ISOPEN) THEN
CLOSE cur_customer_products;
END IF;
ROLLBACK;
v_errmsg := 'ES child account no:' || v_account_id ||
'ES parent account no:' || v_parent_account_id || ' ' ||
SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Posting Charges', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN OTHERS THEN
/* IF (cur_cdr_accounts%ISOPEN) THEN
CLOSE cur_cdr_accounts;
ELSIF (cur_cdr_invoice_details%ISOPEN) THEN
CLOSE cur_cdr_invoice_details;
ELSIF (cur_customer_products%ISOPEN) THEN
CLOSE cur_customer_products;
END IF;*/
ROLLBACK;
v_errmsg := 'ES child account no:' || v_account_id ||
'ES parent account no:' || v_parent_account_id || ' ' ||
SQLERRM;
v_errcode := SQLCODE;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('Posting Charges', v_errcode, v_errmsg, SYSDATE);
COMMIT;
END;
END LOOP; --cur_cdr_accounts
END sp_special_post;
=============SP_TERMINATE_PRODUCTS
create or replace procedure estaple.sp_test_bill_check(p_ssa in VarChar2,
p_bill_cycle_date in date,
p_charge_post_date in date) as
v_charge number(20);
v_total_charges number(20);
v_invoice_amount number(20);
cursor cur_parents is
select distinct parent_account_id, account_id
from NL_es_home_sys_account
where ssa = p_ssa and is_active = 1;
v_parent_account_id varchar2(20);
v_account_id varchar2(20);
cursor cur_balance_forward is
select balance_out_mny, actual_bill_dtm
from billsummary@cdR_nz
where account_num = v_parent_account_id and
bill_dtm = (add_months(last_day(p_bill_cycle_date), -2) + 1);
v_balance_out_mny number(20);
v_actual_bill_dtm date;
cursor cur_payemnts_mny is
select sum(account_payment_mny)
from accountpayment@cdR_nz
where account_num = v_parent_account_id and
trunc(account_payment_dat) > = trunc(v_actual_bill_dtm)
group by account_num;
v_payment_mny number(20);
BEGIN
open cur_parents;
loop
fetch cur_parents
into v_parent_account_id, v_account_id;
exit when cur_parents%NOTFOUND;
begin
select sum(charge)
into v_charge
from NL_Es_Post_Charges_Log p
where p.child_account_id = v_account_id and
trunc(p.charge_post_date) = p_charge_post_date;
if v_charge is null then v_charge := 0;
end if;
v_total_charges := v_total_charges + v_charge;
Exception
When no_data_found then
v_charge := 0;
end;
open cur_balance_forward;
loop
fetch cur_balance_forward
into v_balance_out_mny, v_actual_bill_dtm;
exit when cur_balance_forward%NOTFOUND;
open cur_payemnts_mny;
loop
fetch cur_payemnts_mny
into v_payment_mny;
exit when cur_payemnts_mny%NOTFOUND;
v_invoice_amount := v_total_charges + v_balance_out_mny -
v_payment_mny;
insert into test_bill_check
values
(v_parent_account_id,
v_total_charges,
v_balance_out_mny,
v_payment_mny,
v_invoice_amount);
end loop;
close cur_payemnts_mny;
end loop;
close cur_balance_forward;
end loop;
close cur_parents;
end sp_test_bill_check;
=============SP_UPDATE_ES_POST_ES_BILL_RUN
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_update_es_post_es_bill_run /*(P_SSA VARCHAR2)*/
AS
--Author : 230328
--created : march16,2009
--Brief Info: the script updates ES bill cycle date and ES invoice sequence after the ES bill run.
--version changed from 1.0 to 1.1 , proc name changed ,exception handled if bill for es parent account is not found in IRB
--variable declaration
v_es_bill_cycle_date NL_es_account.bill_cycle_date%TYPE;
v_last_es_bill_cycle_date NL_es_home_sys_account.last_bill_cycle_date%TYPE;
v_child_invoice_seq_no NL_es_home_sys_account.invoice_seq_no%TYPE;
v_es_invoice_seq_no billsummary.bill_seq@cdr_nz %TYPE;
v_last_invoice_seq_no NL_es_account.last_invoice_seq_no%TYPE;
v_parent_account_id NL_es_account.account_id%TYPE;
v_child_account_id NL_es_home_sys_account.account_id%TYPE;
v_err_num NUMBER;
v_err_msg VARCHAR2(570);
--v_bill_seq billsummary.bill_seq@cdr_nz%type;
--cursor to fetch all parent accounts' next bill cycle date
CURSOR cur_parent_account IS
SELECT es.account_id,
acc.next_bill_dtm,
acc.last_bill_dtm,
acc.last_bill_seq
FROM NL_es_account es INNER JOIN account@cdr_nz acc ON es.account_id = acc.account_num /*AND ES.SSA = P_SSA*/
;
--cursor to fetch child accounts and their latest invoice seq no.
CURSOR cur_child_accounts IS
SELECT h.account_id, h.invoice_seq_no
FROM NL_es_home_sys_account h
WHERE h.parent_account_id = v_parent_account_id;
BEGIN
--open cursor cur_parent_account
OPEN cur_parent_account;
LOOP
FETCH cur_parent_account
INTO v_parent_account_id, v_es_bill_cycle_date, v_last_es_bill_cycle_date, v_last_invoice_seq_no;
EXIT WHEN cur_parent_account%NOTFOUND;
v_es_bill_cycle_date := to_date(to_char(v_es_bill_cycle_date,
'MM/DD/YYYY'),
'MM/DD/YYYY');
v_last_es_bill_cycle_date := to_date(to_char(v_last_es_bill_cycle_date,
'MM/DD/YYYY'),
'MM/DD/YYYY');
BEGIN
--fetching the latest es invoice seq no
SELECT bill_seq
INTO v_es_invoice_seq_no
FROM billsummary@cdr_nz
WHERE account_num = v_parent_account_id AND
to_date(to_char(bill_dtm, 'MM/DD/YYYY'), 'MM/DD/YYYY') =
v_last_es_bill_cycle_date AND bill_status = 1;
--updating next bill cycle date in NL_es_account
UPDATE NL_es_account es
SET es.bill_cycle_date = v_es_bill_cycle_date,
last_invoice_seq_no = v_last_invoice_seq_no
WHERE es.account_id = v_parent_account_id;
--updating latest es invoice seq no in NL_es_discount
/* UPDATE NL_es_discount disc
SET disc.es_invoice_seq_no = v_es_invoice_seq_no
WHERE disc.es_account_id = v_parent_account_id AND
disc.bill_cycle_date = v_last_es_bill_cycle_date;*/
IF (cur_child_accounts%ISOPEN) THEN
CLOSE cur_child_accounts;
END IF;
--open cusor cur_child_accounts
OPEN cur_child_accounts;
LOOP
FETCH cur_child_accounts
INTO v_child_account_id, v_child_invoice_seq_no;
EXIT WHEN cur_child_accounts%NOTFOUND;
--updating latest es invoice seq for each child account
UPDATE NL_es_post_charges_log l
SET l.es_invoice_seq_no = v_es_invoice_seq_no
WHERE l.child_account_id = v_child_account_id AND
l.invoice_seq_no = v_child_invoice_seq_no;
--end loop for cursor cur_child_accounts
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
COMMIT;
--end loop for cursor cur_parent_account
END LOOP;
--exception handling
EXCEPTION
WHEN no_data_found THEN
ROLLBACK;
v_err_num := SQLCODE;
v_err_msg := 'Error in updating es bill cycle /es invoice seq :' || ' ' ||
SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('ES UPDATE SCRIPT', v_err_num, v_err_msg, SYSDATE);
COMMIT;
WHEN cursor_already_open THEN
ROLLBACK;
v_err_num := SQLCODE;
v_err_msg := 'Error in updating es bill cycle /es invoice seq :' || ' ' ||
SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('ES UPDATE SCRIPT', v_err_num, v_err_msg, SYSDATE);
COMMIT;
WHEN dup_val_on_index THEN
ROLLBACK;
v_err_num := SQLCODE;
v_err_msg := 'Error in updating es bill cycle /es invoice seq :' || ' ' ||
SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('ES UPDATE SCRIPT', v_err_num, v_err_msg, SYSDATE);
COMMIT;
WHEN invalid_cursor THEN
ROLLBACK;
v_err_num := SQLCODE;
v_err_msg := 'Error in updating es bill cycle /es invoice seq :' || ' ' ||
SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('ES UPDATE SCRIPT', v_err_num, v_err_msg, SYSDATE);
COMMIT;
WHEN too_many_rows THEN
ROLLBACK;
v_err_num := SQLCODE;
v_err_msg := 'Error in updating es bill cycle /es invoice seq :' || ' ' ||
SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('ES UPDATE SCRIPT', v_err_num, v_err_msg, SYSDATE);
COMMIT;
ROLLBACK;
--end procedure
END;
==========SP_UPDATE_HBS_BILLING_TABLE
CREATE OR REPLACE PROCEDURE ESTAPLE.sp_update_hbs_billing_table(p_bill_cycle_date DATE) IS
v_home_sys NL_es_home_sys_account.home_sys_account_id%TYPE;
v_ssa NL_es_home_sys_account.ssa%TYPE;
v_errcode VARCHAR2(500);
v_errmsg VARCHAR2(500);
CURSOR get_ssa IS
SELECT DISTINCT ssa
FROM NL_es_home_sys_account hb
WHERE ssa IS NOT NULL ;
CURSOR get_details IS
SELECT home_sys_account_id
FROM NL_es_home_sys_account h
WHERE h.ssa = v_ssa AND h.is_active = pkg_es.is_active
and h.enrollment_date<p_bill_cycle_date;
BEGIN
delete from oper_rem.HBS_all_ssa@CDR_NZ;
FOR rec_ssa IN get_ssa LOOP
v_ssa := rec_ssa.ssa;
FOR rec_details IN get_details LOOP
v_home_sys := rec_details.home_sys_account_id;
BEGIN
INSERT INTO oper_rem.HBS_ALL_SSA@CDR_NZ VALUES(v_home_sys,v_ssa);
--EXECUTE IMMEDIATE 'insert into oper_rem.HBS_' || v_ssa ||'@cdr_nz VALUES(' || v_home_sys || ')';
EXCEPTION
WHEN OTHERS THEN
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
dbms_output.put_line('hbs' || v_ssa || v_errmsg || v_errcode);
END;
END LOOP;
COMMIT;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('UPDATE HBS BILLING TABLE', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN cursor_already_open THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('UPDATE HBS BILLING TABLE', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN dup_val_on_index THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('UPDATE HBS BILLING TABLE', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN invalid_cursor THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('UPDATE HBS BILLING TABLE', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN too_many_rows THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('UPDATE HBS BILLING TABLE', v_errcode, v_errmsg, SYSDATE);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
INSERT INTO NL_es_error_log
(module_name, error_code, error_msg, error_datetime)
VALUES
('UPDATE HBS BILLING TABLE '||'HBS_'||V_SSA , v_errcode, v_errmsg, SYSDATE);
COMMIT;
COMMIT;
END sp_update_hbs_billing_table;
=============UPDATE_INVOICE_NO
CREATE OR REPLACE PROCEDURE ESTAPLE.update_invoice_no IS
v_account NL_es_account.account_id%TYPE;
v_bill_dtm DATE;
v_bill_seq NUMBER;
CURSOR cur_accounts IS
SELECT r.home_sys_account_id
FROM NL_es_home_sys_account r
WHERE r.parent_account_id IN
(SELECT a.account_id FROM NL_es_account a WHERE a.ssa = 'KYN');
BEGIN
OPEN cur_accounts;
LOOP
FETCH cur_accounts
INTO v_account;
EXIT WHEN cur_accounts%NOTFOUND;
SELECT bill_seq, bill_dtm
INTO v_bill_seq, v_bill_dtm
FROM billsummary@cdr_nz
WHERE bill_seq = (SELECT MAX(bill_seq)
FROM billsummary@cdr_nz
WHERE account_num = '1001392775') AND
account_num = '1001392775' AND bill_status = 1;
IF v_bill_dtm = to_date('01-05-2010', 'dd-mm-yyyy') THEN
UPDATE NL_es_home_sys_account
SET invoice_seq_no = v_bill_seq
WHERE account_id = '1001392775';
ELSE
dbms_output.put_line(v_account);
END IF;
END LOOP;
END update_invoice_no;
===============UPDATE_SERVICES
create or replace procedure estaple.update_services is
v_account_id varchar2(20);
v_hbs_id varchar2(20);
v_prod_label varchar2(20);
v_customer_ref varchar2(20);
v_prod_seq varchar2(2);
v_product_seq varchar2(2);
cursor cur_get_accounts is
select account_id, home_sys_account_id, product_seq
from NL_es_cdr_services t
where t.telephone_no is null;
cursor cur_get_customer_ref is
select customer_ref from account@cdr_nz where account_num = v_hbs_id;
cursor cur_get_product_label is
select product_label, product_seq
from custproductdetails@cdr_nz
where customer_ref = v_customer_ref and product_seq = v_prod_seq;
begin
for get_accounts in cur_get_accounts LOOP
v_account_id := get_accounts.account_id;
v_hbs_id := get_accounts.home_sys_account_id;
v_prod_seq := get_accounts.product_seq;
for get_customer_ref in cur_get_customer_ref LOOP
v_customer_ref := get_customer_ref.customer_ref;
for get_product_label in cur_get_product_label LOOP
v_prod_label := get_product_label.product_label;
v_product_seq := get_product_label.product_seq;
update NL_es_cdr_services
set telephone_no = v_prod_label
where account_id = v_account_id and product_seq = v_product_seq;
end loop;
end loop;
end loop;
commit;
end update_services;
E-stapling procedures.txt
Displaying E-stapling procedures.txt.
===============================
.png)
.png)
No comments:
Post a Comment