Skip to content

Instantly share code, notes, and snippets.

@iSkeeve

iSkeeve/gist:5684266

Created May 31, 2013 11:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save iSkeeve/5684266 to your computer and use it in GitHub Desktop.
Save iSkeeve/5684266 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
create or replace
PACKAGE BODY SCHEDULE_CARD_VIEW AS
--вывод графика по кредитным картам
procedure getCreditCardPayList(pSchedId in integer,pResult out sys_refcursor) is
paytable sched.CREDIT_CARD_VIEW_TAB;
vApDelayPayed number:=0;
vApPercDelayPayed number:=0;
vOtherPayed number:=0;
vMinApNeedPayed number;
vMinPercentsNeedPayed number;
vApDelayPayedCopy number:=0;
vMinPayAll number;
vPublCode varchar2(600);
vReportEnd integer;
vBankDate date;
vForgivePens number;
begin
select syn_publ_code into vPublCode from oas.dec_product_syn,sched.sched_list,oas.dec_contract where sched_list_id=pSchedId and sched_list_contr_id=contr_id and contr_prod_id=syn_id;
vBankDate:=oas.fin_kernel.getBankDate;
if (vPublCode in ('4001','4002')) then
vReportEnd :=4;
else
vReportEnd:=3;
end if;
vMinPayAll:=schedule_card_api.getMinPay(pSchedId,vBankDate);
select nvl(sum(card_payment_fact_ap_delay),0),
nvl(sum(card_payment_fact_apperc_delay+ card_payment_fact_apperc_del_n),0),
nvl(sum(card_payment_fact_amount-(card_payment_fact_percents+ card_payment_fact_percents+CARD_PAYMENT_FACT_PERC_N+card_payment_fact_ap_delay+card_payment_fact_ap)),0)
into vApDelayPayed,
vApPercDelayPayed,
vOtherPayed
from sched_card, sched_card_payments where card_sched_id=pSchedId and card_min_pay_id=card_payment_id and card_pay_type =2;
select nvl(sum((card_payment_arg_percents- card_payment_a_percent_balance)+(card_payment_arg_percents_n- card_payment_a_percent_bal_n)+(card_payment_arg_percent_delay- card_payment_aperdelay_balance)+(card_payment_arg_percents_n- card_payment_aperdelay_bal_n)+(card_payment_fixed_penalty- card_payment_fixed_penalty_bal)+(card_payment_fixed_penalty- card_payment_fixed_penalty_bal)+(card_payment_penalty_ap- card_payment_penaltyap_balance)+(card_payment_penalty_percents- card_payment_penaltyperc_bal)),0)
into vForgivePens
from sched_card, sched_card_payments where card_sched_id=pSchedId and card_min_pay_id=card_payment_id and card_pay_type =7;
vOtherPayed:=vOtherPayed+vForgivePens;
vApDelayPayedCopy:=vApDelayPayed;
select sched.CREDIT_CARD_VIEW_REC(report_date_begin,report_date_end,ap_balance,pay_date_begin,pay_date_end,min_all,min_ap,min_apdelay,min_perc,min_other,
nvl(sum(card_payment_fact_amount),0),nvl(sum(card_payment_fact_ap),0),nvl(sum(card_payment_fact_ap_delay),0),nvl(sum(fact_percents),0),nvl(sum(fact_other),0),decode(delays.delay_date_from,null,0,nvl(nvl(delays.delay_date_to,vBankDate)-pay_date_end,0)),'S') bulk collect into paytable
from (select nvl(report_date_begin,contr_date_from) report_date_begin,
report_date_end,
-(card_payment_ap+card_payment_ap_delay) ap_balance,
pay_date_begin,
nvl(pay_date_end, sched_list_report_date) pay_date_end,
card_payment_ap_balance+card_payment_apdelay_balance+min_perc+min_other as min_all,
card_payment_ap_balance min_ap,
card_payment_apdelay_balance as min_apdelay,
min_perc,
min_other
from sched_list
join oas.dec_contract
on sched_list_contr_id=contr_id
join (select rownum as roworder,card1_order.* from
(select
card_date as report_date_end,
card_date+1 as pay_date_begin,
card_id,
card_sched_id,
card_payment_ap,
card_payment_ap_delay,
card_payment_ap_balance,
card_payment_percent_balance+ card_payment_percent_bal_n as min_perc,
card_payment_apdelay_balance,
card_payment_appercdel_balance
+card_payment_percdelay_balance
+card_payment_aperdelay_balance
+CARD_PAYMENT_FIXED_PENALTY_BAL
+card_payment_penaltyap_balance
+CARD_PAYMENT_PENALTYPERC_BAL
+card_payment_commiss_balance
+CARD_PAYMENT_PERCDELAY_BAL_N
+CARD_PAYMENT_APERDELAY_BAL_N
+CARD_PAYMENT_APPERCDEL_BAL_N as min_other
from sched_card,
sched_card_payments
where card_min_pay_id=card_payment_id
and card_sched_id=pSchedId
and card_pay_type=vReportEnd order by card_date)card1_order) card1
on card1.card_sched_id= sched_list.sched_list_id
left outer join
(select rownum as roworder, card2_order.* from (select
card_date+1 as report_date_begin
from sched_card,
sched_card_payments
where card_sched_id=pSchedId
and card_pay_id=card_payment_id
and card_pay_type=vReportEnd order by card_date)card2_order) card2
on card1.roworder-1=card2.roworder
left outer join
(select rownum as roworder, card3_order.* from (select
card_date as pay_date_end
from sched_card,
sched_card_payments
where card_sched_id=pSchedId
and card_pay_id=card_payment_id
and card_pay_type=3 order by card_date)card3_order) card3
on card1.roworder+1=card3.roworder) periods
left outer join
(select card_date,
card_payment_fact_amount,
card_payment_fact_ap,
card_payment_fact_percents+ card_payment_fact_perc_n as fact_percents,
card_payment_fact_ap_delay,
card_payment_fact_amount-(card_payment_fact_ap+card_payment_fact_percents+card_payment_fact_perc_n+card_payment_fact_ap_delay) as fact_other
from sched_card,
sched_card_payments
where card_payment_id=card_min_pay_id
and card_sched_id=pSchedId and card_pay_type =2) card
on card.card_date>=periods.pay_date_begin
and card.card_date<=periods.pay_date_end
left outer join (
select delay_date_from,delay_date_to from sched_delays where delay_sched_id=pSchedId) delays
on delays.delay_date_from<=periods.pay_date_end and (delays.delay_date_to>=periods.pay_date_end or delays.delay_date_to is null)
group by report_date_begin,report_date_end,ap_balance,pay_date_begin,pay_date_end,min_all,min_ap,min_apdelay,min_perc,min_other,delays.delay_date_to,delays.delay_date_from order by periods.pay_date_begin;
if (paytable.last>1) then
for i in paytable.first .. paytable.last-1 loop
payPart(vApDelayPayed,paytable(i).min_ap-paytable(i).min_ap_payed,paytable(i).min_ap_payed,true,vApDelayPayed,paytable(i).min_ap_payed);
payPart(vApDelayPayedCopy,paytable(i).min_apdelay-paytable(i).min_apdelay_payed,paytable(i).min_apdelay_payed,false,vApDelayPayedCopy,paytable(i).min_apdelay_payed);
payPart(vApPercDelayPayed,paytable(i).min_perc-paytable(i).min_perc_payed,paytable(i).min_perc_payed,true,vApPercDelayPayed,paytable(i).min_perc_payed);
payPart(vOtherPayed,paytable(i).min_other-paytable(i).min_other_payed,paytable(i).min_other_payed,false,vOtherPayed,paytable(i).min_other_payed);
paytable(i).min_all_payed:=paytable(i).min_ap_payed+paytable(i).min_apdelay_payed+paytable(i).min_perc_payed+paytable(i).min_other_payed;
paytable(i).min_ap_payed:=checkSum(paytable(i).min_ap,paytable(i).min_ap_payed);
--dbms_output.put_line(paytable(i).min_apdelay_payed);
paytable(i).min_apdelay_payed:=checkSum(paytable(i).min_apdelay,paytable(i).min_apdelay_payed);
paytable(i).min_perc_payed:=checkSum(paytable(i).min_perc,paytable(i).min_perc_payed);
paytable(i).min_other_payed:=checkSum(paytable(i).min_other,paytable(i).min_other_payed);
paytable(i).min_all_payed:=checkSum(paytable(i).min_all,paytable(i).min_all_payed);
if (paytable(i/*paytable.last*/).delay_days>0) then
if((paytable(i).min_all_payed-paytable(i).min_all)<0) then
paytable(i).payment_color:='R';
else paytable(i).payment_color:='B';
end if;
end if;
end loop;
paytable(paytable.last).min_ap_payed:=checkSum(paytable(paytable.last).min_ap,paytable(paytable.last).min_ap_payed);
paytable(paytable.last).min_apdelay_payed:=checkSum(paytable(paytable.last).min_apdelay,paytable(paytable.last).min_apdelay_payed);
paytable(paytable.last).min_perc_payed:=checkSum(paytable(paytable.last).min_perc,paytable(paytable.last).min_perc_payed);
paytable(paytable.last).min_all_payed:=checkSum(paytable(paytable.last).min_all,paytable(paytable.last).min_all_payed);
paytable(paytable.last).min_other:=vMinPayAll-(paytable(paytable.last).min_ap-paytable(paytable.last).min_ap_payed+paytable(paytable.last).min_apdelay-paytable(paytable.last).min_apdelay_payed+paytable(paytable.last).min_perc-paytable(paytable.last).min_perc_payed-paytable(paytable.last).min_other_payed);
paytable(paytable.last).min_other_payed:=checkSum(paytable(paytable.last).min_other,paytable(paytable.last).min_other_payed);
paytable(paytable.last).delay_days:=0;
end if;
open pResult for select * from table(cast(paytable as sched.CREDIT_CARD_VIEW_TAB));
end;
--проверка сумм погашения с суммами минимального платежа и ее обновление
function checkSum(pCalcAmount in number, pPayAmount in number) return number is
begin
if (pCalcAmount<pPayAmount) then
return pCalcAmount;
else return pPayAmount;
end if;
end;
-- обновление предыдущих полей в графике за счет последних погашений
procedure payPart(pAmountForPay in number,pNeedPayed in number,pAmountPayed in number, pChange in boolean,pChangeAmountForPay out number,pChangeAmountPayed out number) is
begin
pChangeAmountForPay:=0;
pChangeAmountPayed:=0;
if (pAmountForPay>0) then
if (pNeedPayed<=pAmountForPay) then
pChangeAmountPayed:=pAmountPayed+pNeedPayed;
if (pChange) then
pChangeAmountForPay:=pAmountForPay-pNeedPayed;
else pChangeAmountForPay:=pAmountForPay;
end if;
else
pChangeAmountPayed:=pAmountPayed+pAmountForPay;
if (pChange) then
pChangeAmountForPay:=0;
else pChangeAmountForPay:=pAmountForPay;
end if;
end if;
else
pChangeAmountPayed:=pAmountPayed;
end if;
end;
END SCHEDULE_CARD_VIEW;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment