1 PACKAGE BODY Xtr_Settlement_Summary_P AS
2 /* $Header: xtrsetlb.pls 120.0.12010000.2 2008/08/06 10:44:29 srsampat ship $ */
3 --Package that inserts and updates rows in the xtr_settlement_summary table.
4
5 /* table handler for Xtr_Settlement_Summary */
6
7 /**********************************************************************************/
8 /* This procedure serves for two purposes: */
9 /* (1) when user authorizes a settlement, then corresponding record is entered in */
10 /* (2) For two or more settlements are netted, a new records with netted amount */
11 /* is created in Xtr_Settlement_Summary */
12 /**********************************************************************************/
13
14
15
16 Procedure INS_SETTLEMENT_SUMMARY(p_settlement_number IN Number,
17 p_company IN VARCHAR2,
18 p_currency IN VARCHAR2,
19 p_settlement_amount IN Number,
20 p_settlement_date IN Date,
21 p_company_acct_no IN Varchar2,
22 p_cparty_acct_no IN Varchar2,
23 p_net_ID IN Number,
24 p_status IN Varchar2,
25 p_created_by IN Number,
26 p_creation_date IN Date,
27 p_external_source IN Varchar2,
28 p_cparty_code IN Varchar2, -- bug 3832387
29 p_settlement_ID OUT NOCOPY Number )
30
31 Is
32 v_settlement_ID Number;
33 v_exists char(1);
34
35 Begin
36 Begin
37 Select 'Y', settlement_summary_id
38 Into v_exists, v_settlement_id
39 From Xtr_Settlement_Summary
40 Where settlement_number = p_settlement_number;
41 Exception
42 When no_data_found then
43 v_exists := 'N';
44 End;
45 If v_exists = 'N' then
46 Select Xtr_Settlement_Summary_S.Nextval
47 Into v_settlement_ID
48 From Dual;
49
50 Insert into Xtr_Settlement_Summary(Settlement_Summary_Id, Settlement_Number, Company, Currency,
51 Settlement_Amount, Settlement_Date, Company_Acct_No, Cparty_Acct_No, Net_ID, Status,
52 Created_By, Creation_Date, Last_Updated_By, Last_Update_Date, Last_Update_Login, cparty_code,
53 external_source)
54 Values(v_settlement_ID, p_settlement_number, p_company, p_currency, p_settlement_amount,
55 p_settlement_date, p_company_acct_no, p_cparty_acct_no, p_net_ID, p_status,
56 p_created_by, p_creation_date, p_created_by,
57 p_creation_date,null,p_cparty_code,p_external_source); -- bug 3832387
58
59 p_settlement_ID := v_settlement_ID;
60 Else
61 Update Xtr_Settlement_Summary
62 set settlement_date = p_settlement_date,
63 cparty_code = p_cparty_code -- bug 3832387
64 Where settlement_number = p_settlement_number;
65
66 p_settlement_ID := v_settlement_ID;
67 End if;
68 End INS_Settlement_Summary;
69
70 /******************************************************************************/
71 /* This procedure handles the situation that 2 or more settlements are netted,*/
72 /* the corresponding entries in Xtr_Settlement_Summary are set UNAVAILABLE for*/
73 /* CE Reconciliation (status set to 'I' for original entries) */
74 /******************************************************************************/
75 Procedure UPD_SETTLEMENT_SUMMARY(p_flag IN Char, p_netoff_number in number, p_settlement_ID IN Number)
76 Is
77 Cursor C1 is
78 Select settlement_number, deal_number, transaction_number
79 From Xtr_Deal_Date_Amounts
80 Where netoff_number = p_netoff_number;
81
82 Begin
83 For C1_Rec in C1
84 Loop
85 Update Xtr_Settlement_Summary -- bug 3076732
86 Set status = 'I',
87 net_ID = p_settlement_ID
88 Where (settlement_number = C1_Rec.SETTLEMENT_number) or
89 (settlement_summary_id in (select net_id
90 from XTR_SETTLEMENT_SUMMARY
91 where settlement_number = C1_REC.settlement_number));
92
93 End Loop;
94
95 End UPD_Settlement_Summary;
96
97 /******************************************************************************/
98 /* This procedure handles the situation when a netted group is created, */
99 /* and a new netted transaction is added. In this case, the new added entries */
100 /* in Xtr_Settlement_Summary is set UNAVAILABLE for CE Reconciliation (status */
101 /* set to 'I' for original entries). Also update the existing netted */
102 /* trnasaction with new netted amount. */
103 /******************************************************************************/
104 Procedure UPD_Settlement_Summary(p_flag IN Char, p_netoff_number IN Number, p_settlement_ID IN Number, p_amount IN Number)
105 Is
106 Cursor C1 is
107 Select settlement_number, deal_number, transaction_number
108 From Xtr_Deal_Date_Amounts
109 Where netoff_number = p_netoff_number;
110 Begin
111
112 For C1_Rec in C1 Loop
113 Delete from XTR_SETTLEMENT_SUMMARY
114 where settlement_summary_id in (select net_id
115 from XTR_SETTLEMENT_SUMMARY
116 where settlement_number = C1_rec.SETTLEMENT_NUMBER)
117 and settlement_summary_id <> p_settlement_id;
118
119 Update Xtr_Settlement_Summary
120 Set status = 'I',
121 net_ID = p_settlement_ID
122 Where settlement_number = C1_Rec.SETTLEMENT_number;
123
124 End Loop;
125
126 Update Xtr_Settlement_Summary
127 Set settlement_amount = nvl(p_amount, 0)
128 Where Settlement_Summary_ID = p_settlement_ID;
129
130 End UPD_Settlement_Summary;
131
132 /******************************************************************************/
133 /* This procedure handles the situation when user undo partial netted */
134 /* settlement, the unnetted transaction in Xtr_Settlement_Summary is made */
135 /* AVAILABLE for CE Reconciliation( status back to 'A'). The original netted */
136 /* transaction need to be updated with the new netted amount */
137 /******************************************************************************/
138 Procedure UPD_Settlement_Summary(p_settlement_number IN Number, p_amount IN Number)
139 Is
140 v_net_ID Xtr_Settlement_Summary.net_ID%Type;
141 v_no_of_netted_recs number;
142 Begin
143 Select net_ID
144 Into v_net_ID
145 From Xtr_Settlement_Summary
146 Where settlement_number = p_settlement_number;
147
148 Update Xtr_Settlement_Summary
149 Set status = 'A',
150 net_ID = null
151 Where settlement_number = p_settlement_number;
152
153 Select count(*)
154 Into v_no_of_netted_recs
155 From Xtr_Settlement_Summary
156 Where net_ID = v_net_ID;
157
158 If v_no_of_netted_recs <> 0 then
159 Update Xtr_Settlement_Summary
160 Set settlement_amount = nvl(settlement_amount, 0) - nvl(p_amount,0)
161 Where settlement_summary_ID = v_net_ID;
162
163 Else
164 Delete From Xtr_Settlement_Summary
165 Where settlement_Summary_ID = v_net_ID;
166 End if;
167
168 End UPD_Settlement_Summary;
169
170 /******************************************************************************/
171 /* This procedure handles the situation when user want to unnet settlements */
172 /* totally (i.e., change radio button back to 'None', the netted transaction */
173 /* in Xtr_Settlement_Summary should be deleted. Also update the status to 'A' */
174 /* for original entries. */
175 /******************************************************************************/
176
177 Procedure DEL_SETTLEMENT_SUMMARY(p_settle_date IN Date,
178 p_currency IN Varchar2,
179 p_acct_no IN Varchar2,
180 p_cpacct_no IN Varchar2,
181 p_company_code IN Varchar2,
182 p_flag IN Char,
183 p_return OUT NOCOPY Char)
184 Is
185 v_net_ID Number;
186 v_first_record Char(1) ;
187 v_del_resultant Char(1);
188 v_upd_single Char(1);
189
190 Cursor C2 is
191 Select settlement_number
192 From Xtr_Deal_Date_Amounts
193 where ACTUAL_SETTLEMENT_DATE = p_settle_DATE
194 and CASHFLOW_AMOUNT <> 0
195 and CURRENCY = p_CURRENCY
196 and ACCOUNT_NO = p_ACCT_NO
197 and nvl(BENEFICIARY_ACCOUNT_NO,CPARTY_ACCOUNT_NO) = nvl(p_CPACCT_NO,NULL)
198 and COMPANY_CODE = p_COMPANY_CODE
199 and SETTLEMENT_ACTIONED is NULL
200 and RECONCILED_PASS_CODE is NULL
201 and RECONCILED_REFERENCE is NULL
202 and NETOFF_NUMBER is NULL -- jhung
203 and AMOUNT_TYPE <> 'FXOBUY'
204 and AMOUNT_TYPE <> 'FXOSELL'
205 /********* code below modified by Ilavenil for 2344133 ********/
206 and (nvl(EXP_SETTLE_REQD, 'Y') = 'Y' or DEAL_TYPE <> 'EXP')
207 /*********/
208 and nvl(MULTIPLE_SETTLEMENTS, 'N') = 'N'
209 and DEAL_SUBTYPE <> 'INDIC'
210 and DEAL_TYPE <> 'CA';
211
212 Begin
213 p_return := 'Y';
214 v_first_record := 'Y';
215 v_del_resultant := 'N';
216 v_upd_single := 'N';
217
218 For C2_Rec in C2 Loop
219 -- If v_first_record = 'Y' then -- jhung
220 Select net_ID
221 Into v_net_ID
222 From Xtr_Settlement_Summary
223 Where Settlement_Number = C2_Rec.Settlement_Number;
224
225 Delete from Xtr_Settlement_Summary
226 Where Settlement_Summary_ID = v_net_ID;
227 If SQL%FOUND then
228 /* resultant record has been deleted */
229 v_del_resultant := 'Y';
230 End if;
231
232 v_first_record := 'N';
233
234 Update Xtr_Settlement_Summary
235 Set Status = 'A',
236 Net_ID = null
237 Where Settlement_Number = C2_Rec.Settlement_Number;
238 If SQL%FOUND then
239 /* individual records contributing the resultant figure are updated as AVAILABLE */
240 v_upd_single := 'Y';
241 End if;
242
243 End Loop;
244
245 If v_del_resultant = 'N' and v_upd_single = 'N' then
246 /* reconciled grouped settlement cannot be unnetted. user has to unreconcile first and then proceed */
247 p_return := 'N';
248 -- DISP_ERR('XTR_CANNOT_UNNET_RECONCILED');
249 -- Raise Form_Trigger_Failure;
250 End if;
251
252 End DEL_Settlement_Summary ;
253
254 /******************************************************************************/
255 /* This procedure handles the situation when user unauthorize the Settlements,*/
256 /* the corresponding entry in Xtr_Settlement_Summary has to be removed. */
257 /* Before removing we are to check whether the settlement was involved in */
258 /* netting. If so, then resultant record needs to be modified accordingly */
259 /******************************************************************************/
260 Procedure DEL_SETTLEMENT_SUMMARY(p_settlement_number IN Number,
261 p_settlement_amount IN Number) is
262 v_net_ID Xtr_Settlement_Summary.Net_ID%Type;
263 v_no_of_netted_recs number;
264 Begin
265 Begin
266 Select net_ID
267 Into v_net_ID
268 From Xtr_Settlement_Summary
269 Where settlement_number = p_settlement_number;
270 Exception
271 When no_data_found then
272 null;
273 End;
274
275 /* if the un-authorized settlement is involved in netting, then */
276 If v_net_Id is not null then
277 /* remove the un-authorized settlement, update resultant record for the amount */
278
279 Delete From Xtr_Settlement_Summary
280 Where settlement_number = p_settlement_number;
281
282 Select count(*)
283 Into v_no_of_netted_Recs
284 From Xtr_Settlement_Summary
285 Where net_ID = v_net_ID;
286
287 If v_no_of_netted_recs <> 0 then
288 Update Xtr_Settlement_summary
289 Set settlement_amount = nvl(settlement_amount, 0) - nvl(p_settlement_amount, 0)
290 Where settlement_summary_ID = v_net_ID;
291
292 Else
293 Delete From Xtr_Settlement_Summary
294 Where settlement_summary_ID = v_net_ID;
295 End if;
296 Else
297 /* if un-authorized settlement is not involved in netting, then */
298 Delete from Xtr_Settlement_Summary
299 Where Settlement_Number = p_settlement_number;
300 If SQL%NOTFOUND then
301 null;
302 End if;
303
304 End if;
305 End DEL_Settlement_Summary;
306
307 Procedure Include_Settlement_Group(p_settlement_number IN Number,
308 p_netoff_number IN Number,
309 p_company IN Varchar2,
310 p_currency IN Varchar2,
311 p_settlement_amount IN Number,
312 p_settlement_date IN Date,
313 p_company_acct_no IN Varchar2,
314 p_cparty_acct_no IN Varchar2,
315 p_created_by IN Number,
316 p_creation_date IN Date,
317 p_cparty_code IN Varchar2) -- bug 3832387
318 Is
319 v_exists Char(1);
320 v_settlement_number number;
321 v_net_ID number;
322 v_settlement_ID Number;
323 Begin
324 /* say for example user included a particular settlement and a particular group. then user changes mind and
325 wants to exclude the settlement from the group. again the user changes mind and wants to include
326 the settlement under the same group */
327 Begin
328 Select 'Y'
329 Into v_exists
330 From Xtr_Settlement_Summary
331 Where settlement_number = p_settlement_number;
332 Exception
333 when no_data_found then
334 v_exists := 'N';
335 End;
336
337 Select settlement_number
338 Into v_settlement_number
339 From Xtr_Deal_Date_Amounts
340 Where netoff_number = p_netoff_number
341 And rownum < 2;
342
343 Select net_ID
344 Into v_net_ID
345 From Xtr_Settlement_Summary
346 Where settlement_number = v_settlement_number;
347
348 If v_exists = 'Y' then
349 Update Xtr_Settlement_Summary
350 Set status = 'I',
354 Select Xtr_Settlement_Summary_S.Nextval
351 net_ID = v_net_ID
352 Where settlement_number = p_settlement_number;
353 Else
355 Into v_settlement_ID
356 From Dual;
357
358 Insert into Xtr_Settlement_Summary(Settlement_Summary_Id, Settlement_Number, Company, Currency,
359 Settlement_Amount, Settlement_Date, Company_Acct_No, Cparty_Acct_No, Net_ID, Status,
360 Created_By, Creation_Date, Last_Updated_By, Last_Update_Date, Last_Update_Login, cparty_code)
361 Values(v_settlement_ID, p_settlement_number, p_company, p_currency, p_settlement_amount,
362 p_settlement_date, p_company_acct_no, p_cparty_acct_no, v_net_ID, 'I',
363 p_created_by, p_creation_date, p_created_by, p_creation_date,null,p_cparty_code); -- bug 3832387
364 End if;
365
366 Update Xtr_Settlement_Summary
370 End Include_Settlement_Group;
371
372
373 END XTR_SETTLEMENT_SUMMARY_P;
369
367 Set settlement_amount = nvl(settlement_amount, 0) + nvl(p_settlement_amount, 0)
368 Where settlement_summary_ID = v_net_ID;
[Home] [Help]