1 Package body XTR_BISF_P as
2 /* $Header: xtrbisfb.pls 120.2.12010000.2 2008/08/06 10:42:12 srsampat ship $ */
3
4
5 Function Xtr_Reference_Spot(
6 p_deal_type in varchar2,
7 p_deal_number in varchar2
8 ) return number is
9
10 l_base_rate number;
11
12 cursor get_base_rate is
13 select base_rate
14 from xtr_deals
15 where deal_no=to_number(p_deal_number);
16
17 Begin
18
19 if p_deal_type in('FX','FXO') then
20 open get_base_rate;
21 fetch get_base_rate into l_base_rate;
22 close get_base_rate;
23 end if;
24 return (l_base_rate);
25
26 End Xtr_Reference_Spot;
27
28
29 Function Xtr_Reference_Internal(
30 p_deal_type in varchar2,
31 p_deal_number in varchar2,
32 p_transaction_number in number
33 ) return varchar2 is
34
35 l_comments varchar2(255);
36
37 cursor d_ref_int is
38 select internal_ticket_no
39 from xtr_deals
40 where deal_no=to_number(p_deal_number);
41
42 cursor ro_ref_int is
43 select internal_ticket_no
44 from xtr_rollover_transactions
45 where deal_number=to_number(p_deal_number)
46 and transaction_number=p_transaction_number;
47
48 Begin
49 if p_deal_type in('FX','FXO','BOND','BDO','STOCK','FRA','NI','IRO','IRS','SWPTN','TMM','RTMM') then
50 open d_ref_int;
51 fetch d_ref_int into l_comments;
52 close d_ref_int;
53 elsif p_deal_type ='ONC' then
54 open ro_ref_int;
55 fetch ro_ref_int into l_comments;
56 close ro_ref_int;
57 end if;
58 return (l_comments);
59
60 End Xtr_Reference_Internal;
61
62 Function Xtr_Ig_Onc_Sum_Intadj(
63 p_deal_type in varchar2,
64 p_deal_number in number,
65 p_transaction_number in number,
66 p_journal_date in date
67 ) return number is
68
69 l_sum_intadj number;
70 l_prev_journal_date date;
71
72 cursor ig_get_prev_intset is
73 select journal_date
74 from xtr_journals
75 where deal_type=p_deal_type and
76 deal_number=p_deal_number and
77 amount_type in ('INTSET','COMPOND') and
78 journal_date < p_journal_date and
79 accounted_dr <>0
80 order by journal_date desc;
81
82 cursor ig_sum_intadj(p_prev_journal_date date) is
83 select sum(ACCOUNTED_DR)
84 from xtr_journals
85 where deal_type=p_deal_type and
86 deal_number=p_deal_number and
87 amount_type ='INTADJ' and
88 journal_date > p_prev_journal_date and
89 journal_date <= p_journal_date and
90 accounted_dr <>0;
91
92 cursor onc_get_prev_intset is
93 select journal_date
94 from xtr_journals
95 where deal_type=p_deal_type and
96 deal_number=p_deal_number and
97 amount_type ='INTSET' and
98 accounted_dr <> 0 and
99 journal_date < p_journal_date and
100 transaction_number in (select a.transaction_number from xtr_rollover_transactions a
101 where a.deal_number=p_deal_number
102 start with a.cross_ref_to_trans = p_transaction_number
103 connect by (a.deal_number = PRIOR a.deal_number and a.cross_ref_to_trans = PRIOR a.transaction_number))
104 order by journal_date desc;
105
106
107 cursor onc_sum_intadj is
108 select sum(ACCOUNTED_DR)
109 from xtr_journals
110 where deal_type=p_deal_type and
111 deal_number=p_deal_number and
112 amount_type ='INTADJ' and
113 journal_date > l_prev_journal_date and
114 journal_date <= p_journal_date and
115 accounted_dr <> 0 and
116 transaction_number in (select a.transaction_number from xtr_rollover_transactions a
117 where a.deal_number=p_deal_number
118 start with a.cross_ref_to_trans = p_transaction_number
119 connect by (a.deal_number = PRIOR a.deal_number and a.cross_ref_to_trans = PRIOR a.transaction_number));
120
121
122 Begin
123 l_sum_intadj :=0;
124 If p_deal_type ='IG' then
125 open ig_get_prev_intset;
126 fetch ig_get_prev_intset into l_prev_journal_date;
127 if ig_get_prev_intset%NOTFOUND then
128 l_prev_journal_date :=to_date('01/01/1900','DD/MM/YYYY');
129 end if;
130 close ig_get_prev_intset;
131
132 open ig_sum_intadj(l_prev_journal_date);
133 fetch ig_sum_intadj into l_sum_intadj;
134 close ig_sum_intadj;
135 elsif p_deal_type ='ONC' then
136 open onc_get_prev_intset;
137 fetch onc_get_prev_intset into l_prev_journal_date;
138 if onc_get_prev_intset%NOTFOUND then
139 l_prev_journal_date :=to_date('01/01/1900','DD/MM/YYYY');
140 end if;
141 close onc_get_prev_intset;
142
143 open onc_sum_intadj;
144 fetch onc_sum_intadj into l_sum_intadj;
145 close onc_sum_intadj;
146 end if;
147 return(l_sum_intadj);
148
149 End Xtr_Ig_Onc_Sum_Intadj;
150
151 End XTR_BISF_P;
[Home] [Help]