- 论坛徽章:
- 0
|
过程名为acct.getpaycash2
create or replace procedure acct.GetPayCash2(
in_area in varchar2,
in_product_family_id in varchar2,
in_telno in varchar2,
total_value out varchar2,
punish_value out varchar2,
balance_value out varchar2,
out_flag out varchar2) is
late_amount number;
n_late_amount number;
owe_amount number;
n_balabce number;
n_serv_id number;
n_region_id number;
n_parent_region_id number;
n_acct_id number;
v_out_flag number;
n_billing_cycle_id number;
n_serv_to_10000 number;
table_name varchar2(20);
type cur_type is ref cursor;
rec cur_type;
recc cur_type;
reccc cur_type;
rec_billing_cycle_id number;
rec_acct_item_type_id number;
rec_amount number(16,5);
v_sql VARCHAR2(2000);
vv_sql VARCHAR2(2000);
vvv_sql VARCHAR2(2000);
/* cursor select_acct_item is
/*SELECT BILLING_CYCLE_ID,ACCT_ITEM_TYPE_ID,SUM(AMOUNT) amount
FROM acct.ACCT_ITEM_730
WHERE SERV_ID =n_serv_id
AND state != '5JB'
GROUP BY BILLING_CYCLE_ID,ACCT_ITEM_TYPE_ID;*/
/*SELECT /*+rule BILLING_CYCLE_ID,ACCT_ITEM_TYPE_ID,SUM(AMOUNT)-sum(BALANCE_PAID) amount*/
/* FROM table_name
WHERE SERV_ID in (
select serv_id from serv_id_to_10000 where serial_id=n_serv_to_10000)
AND state in ('5JA','5JT')
GROUP BY BILLING_CYCLE_ID,ACCT_ITEM_TYPE_ID;*/
begin
late_amount:=0;
owe_amount :=0;
n_balabce :=0;
table_name := 'acct'||'.'||'acct'||'_'||'item'||'_'||substr(in_area,2,3); /*动态生成ACCT_ITEM表*/
SELECT /*+rule */SERV_ID,REGION_ID INTO n_serv_id,n_region_id
FROM cust.SERV_HISTORY
WHERE ACC_NBR=trim(in_area||'-'||in_telno)
AND PRODUCT_FAMILY_ID=to_number(in_product_family_id)
AND SERV_STATE<>'2HF'
AND EFF_DATE<SYSDATE
AND EXP_DATE>SYSDATE;
SELECT /*+rule */PARENT_REGION_ID INTO n_parent_region_id
FROM PROD.REGION
WHERE REGION_ID in (
SELECT PARENT_REGION_ID
FROM PROD.REGION
WHERE REGION_ID=n_region_id);
SELECT /*+rule */T_AC.ACCT_ID INTO n_acct_id
FROM acct.ACCT T_AC,acct.SERV_ACCT T_SA
WHERE T_AC.ACCT_ID=T_SA.ACCT_ID
AND T_SA.SERV_ID=n_serv_id
AND T_SA.STATE='00A'
AND T_AC.STATE='00A';
SELECT /*+rule */SUM(BALANCE) into n_balabce
FROM ACCT.ACCT_BALANCE
WHERE STATE='00A'
AND ACCT_BALANCE_ID IN(
SELECT ACCT_BALANCE_ID
FROM ACCT.BALANCE_RELATION
WHERE OBJ_TYPE='5BA' /*5BA 帐户 5BB客户 5BC用户*/
AND OBJ_ID=n_acct_id);
select serv_id_to_10000id.nextval into n_serv_to_10000 from dual;
insert into /*+rule */serv_id_to_10000 (serial_id,serv_id)
select n_serv_to_10000,serv_id from (
select n_serv_id serv_id from dual
UNION
select to_number(ATTR_VAL) serv_id from cust.serv_attr
where ATTR_ID=888 and serv_id=n_serv_id);
commit;
v_sql := 'SELECT BILLING_CYCLE_ID,ACCT_ITEM_TYPE_ID,SUM(AMOUNT)-sum(BALANCE_PAID) amount
FROM '||table_name||'
WHERE SERV_ID in (
select serv_id from serv_id_to_10000 where serial_id='||n_serv_to_10000||')
AND state in (''5JA'',''5JT'')
GROUP BY BILLING_CYCLE_ID,ACCT_ITEM_TYPE_ID';
open rec for v_sql;
LOOP
FETCH rec INTO rec_billing_cycle_id, rec_acct_item_type_id, rec_amount;
exit when rec%notfound;
account_cash_late_fee(rec_billing_cycle_id,rec_acct_item_type_id,n_acct_id,rec_amount,n_parent_region_id,n_late_amount,v_out_flag);
if v_out_flag<>1 then
n_late_amount:=0;
end if;
late_amount:=late_amount+round(n_late_amount,2);
owe_amount :=owe_amount+rec_amount;
out_flag :='N';
end loop;
/* for rec in select_acct_item loop
account_cash_late_fee(rec.billing_cycle_id,rec.acct_item_type_id,n_acct_id,rec.amount,n_parent_region_id,n_late_amount,v_out_flag);
if v_out_flag<>1 then
n_late_amount:=0;
end if;
late_amount:=late_amount+round(n_late_amount,2);
owe_amount :=owe_amount+rec.amount;
out_flag :='N';
end loop;*/
if owe_amount=0 then
out_flag :='Y';
vv_sql :=
'select max(billing_cycle_id) from '||table_name||'
WHERE SERV_ID = '||n_serv_id;
open recc for vv_sql;
loop
fetch recc into n_billing_cycle_id;
exit when recc%notfound;
end loop;
if n_billing_cycle_id <> 0 then
vvv_sql :=
'select sum(AMOUNT) from '||table_name||'
WHERE SERV_ID = '||n_serv_id||' and billing_cycle_id= '||n_billing_cycle_id;
open reccc for vvv_sql;
loop
fetch reccc into owe_amount;
exit when reccc%notfound;
end loop;
end if;
end if;
total_value:=nvl(owe_amount,0);
punish_value:=nvl(late_amount,0);
balance_value:=nvl(n_balabce,0);
delete from serv_id_to_10000 where serial_id=n_serv_to_10000;
commit;
EXCEPTION WHEN OTHERS THEN
total_value:=0;
punish_value:=0;
balance_value:=0;
out_flag :='O';
end GetPayCash2; |
|
免费注册
发表于 2007-05-31 09:43
