1 PACKAGE BODY XTR_IMPORT_DEAL_DATA as
2 /* $Header: xtrimddb.pls 120.13 2010/08/04 22:21:16 nipant ship $*/
3
4
5 /*--------------------------------------------------------------*/
6 Procedure Put_Log(Avr_Buff In Varchar2) is
7 /*--------------------------------------------------------------*/
8 Begin
9 Fnd_File.Put_Line(Fnd_file.LOG,Avr_Buff);
10 End;
11
12
13 /*--------------------------------------------------------------*/
14 Procedure Log_Interface_Errors(AExt_Deal_Id In Varchar2,
15 ADeal_Type Varchar2,
16 Error_Column In Varchar2,
17 Error_Code In Varchar2,
18 Transaction_No In Number) is
19 /*--------------------------------------------------------------*/
20
21 Ld_sysdate Date Default Sysdate;
22 v_error_line Varchar2(4000):=' ';
23 v_disp_error_column Varchar2(80);
24
25 Begin
26
27 /* If reporting a DFF error, substitute the value of the first detected
28 error into the column name for the CM log. This assumes that this
29 procedure is immediately called after running validate flex fields*/
30 if (Error_Column='Attribute16' and G_DFF_Error_column is not null) then
31 v_disp_error_column := G_DFF_Error_column;
32 G_DFF_Error_column := ''; -- only use once
33 else
34 v_disp_error_column := nvl(Error_Column,' ');
35 end if;
36
37 if (Transaction_No is not null) then
38 v_error_line:=v_error_line||fnd_message.get_string('XTR','XTR_TRANSACTION_LABEL')||lpad(Transaction_No,6)||', ';
39 end if;
40
41 v_error_line:=v_error_line||lpad(v_disp_error_column,20)||' - '||fnd_message.get_string('XTR',Error_Code);
42
43 g_current_deal_log_list(g_current_deal_log_list.count):=v_error_line;
44
45
46 if (error_column is not null) then
47
48 Insert Into Xtr_Interface_Errors(CREATED_BY ,
49 CREATION_DATE ,
50 LAST_UPDATED_BY ,
51 LAST_UPDATE_DATE ,
52 LAST_UPDATE_LOGIN ,
53 EXTERNAL_DEAL_ID ,
54 DEAL_TYPE ,
55 ERROR_COLUMN ,
56 ERROR_CODE ,
57 TRANSACTION_NO )
58 Values (Fnd_Global.User_Id,
59 Ld_sysdate,
60 Null,
61 Null,
62 Null,
63 AExt_Deal_Id,
64 ADeal_Type,
65 Error_Column,
66 Error_Code,
67 Transaction_No);
68 end if;
69 End;
70
71
72 /*--------------------------------------------------------------*/
73 Procedure Log_Deal_Warning(p_warning_message In Varchar2) IS
74 BEGIN
75 g_current_deal_log_list(g_current_deal_log_list.count):=p_warning_message;
76 g_has_warnings:=true;
77 END Log_Deal_Warning;
78
79 /*--------------------------------------------------------------*/
80 /* The following code implements the duplicate deal check. */
81 /*--------------------------------------------------------------*/
82 PROCEDURE CHECK_DEAL_DUPLICATE_ID(p_external_deal_id IN VARCHAR2,
83 p_external_deal_type IN VARCHAR2,
84 p_deal_type IN VARCHAR2,
85 error OUT NOCOPY BOOLEAN) is
86 /*--------------------------------------------------------------*/
87 l_count NUMBER;
88
89 begin
90
91 /* check for duplicate External Deal ID in XTR_DEALS */
92 if p_deal_type = 'FX' then
93 select count(*)
94 into l_count
95 from XTR_DEALS
96 where external_deal_id = p_external_deal_id
97 and deal_type = p_deal_type
98 and status_code <> 'CANCELLED';
99
100 elsif p_deal_type = 'IG' then
101 select count(*)
102 into l_count
103 from XTR_INTERGROUP_TRANSFERS
104 where external_deal_id = p_external_deal_id;
105
106 elsif p_deal_type = 'NI' then
107 select count(*)
108 into l_count
109 from XTR_DEALS
110 where external_deal_id = p_external_deal_id
111 and deal_type = p_deal_type
112 and status_code <> 'CANCELLED';
113
114 end if;
115
116 if (l_count > 0) then
117 error := TRUE;
118 else
119 error := FALSE;
120 end if;
121
122 if (error = TRUE) then
123 update xtr_deals_interface
124 set load_status_code = 'DUPLICATE_DEAL_ID',
125 last_update_date = trunc(SYSDATE),
126 Last_Updated_by = fnd_global.user_id
127 where external_deal_id = p_external_deal_id
128 and deal_type = p_external_deal_type;
129
130 log_interface_errors(p_external_deal_id,p_external_deal_type,null,'XTR_DUPLICATE_ID');
131 end if;
132
133 end CHECK_DEAL_DUPLICATE_ID;
134
135
136 /*------------------------------------------------------------*/
137 /* The following code implements the CHECK_USER_AUTH process */
138 /*------------------------------------------------------------*/
139 PROCEDURE CHECK_USER_AUTH(p_external_deal_id IN VARCHAR2,
140 p_deal_type IN VARCHAR2,
141 p_company_code IN VARCHAR2,
142 error OUT NOCOPY BOOLEAN) is
143 /*------------------------------------------------------------*/
144 l_dummy varchar2(1);
145
146 BEGIN
147
148 error := FALSE;
149
150 BEGIN
151
152 IF xtr_risk_debug_pkg.g_Debug THEN
153 xtr_risk_debug_pkg.dpush('XTR_IMP_DEAL_DATE.CHECK_USER_AUTH');
154 xtr_risk_debug_pkg.dlog('CHECK_USER_AUTH: ' || 'p_company_code',p_company_code);
155 END IF;
156
157 select 'Y'
158 into l_dummy
159 from xtr_parties_v
160 where party_type = 'C'
161 and party_code = p_company_code
162 and rownum = 1;
163
164 IF xtr_risk_debug_pkg.g_Debug THEN
165 xtr_risk_debug_pkg.dpop('XTR_IMP_DEAL_DATA.CHECK_USER_AUTH');
166 END IF;
167
168 EXCEPTION
169 WHEN NO_DATA_FOUND THEN
170 error := TRUE;
171 log_interface_errors( p_external_deal_id ,p_deal_type,'CompanyCode','XTR_INV_COMP_CODE');
172 IF xtr_risk_debug_pkg.g_Debug THEN
173 xtr_risk_debug_pkg.dpop('XTR_IMP_DEAL_DATA.CHECK_USER_AUTH');
174 END IF;
175
176 END;
177
178 END CHECK_USER_AUTH;
179
180
181 /*--------------------------------------------------------------------------------*/
182 FUNCTION val_desc_flex( p_Interface_Rec IN XTR_DEALS_INTERFACE%ROWTYPE,
183 p_desc_flex IN VARCHAR2,
184 p_error_segment IN OUT NOCOPY VARCHAR2) return BOOLEAN is
185 /*--------------------------------------------------------------------------------*/
186 l_segment number(3);
187
188 BEGIN
189
190 fnd_flex_descval.set_column_value('ATTRIBUTE1',p_Interface_Rec.ATTRIBUTE1);
191 fnd_flex_descval.set_column_value('ATTRIBUTE2',p_Interface_Rec.ATTRIBUTE2);
192 fnd_flex_descval.set_column_value('ATTRIBUTE3',p_Interface_Rec.ATTRIBUTE3);
193 fnd_flex_descval.set_column_value('ATTRIBUTE4',p_Interface_Rec.ATTRIBUTE4);
194 fnd_flex_descval.set_column_value('ATTRIBUTE5',p_Interface_Rec.ATTRIBUTE5);
195 fnd_flex_descval.set_column_value('ATTRIBUTE6',p_Interface_Rec.ATTRIBUTE6);
196 fnd_flex_descval.set_column_value('ATTRIBUTE7',p_Interface_Rec.ATTRIBUTE7);
197 fnd_flex_descval.set_column_value('ATTRIBUTE8',p_Interface_Rec.ATTRIBUTE8);
198 fnd_flex_descval.set_column_value('ATTRIBUTE9',p_Interface_Rec.ATTRIBUTE9);
199 fnd_flex_descval.set_column_value('ATTRIBUTE10',p_Interface_Rec.ATTRIBUTE10);
200 fnd_flex_descval.set_column_value('ATTRIBUTE11',p_Interface_Rec.ATTRIBUTE11);
201 fnd_flex_descval.set_column_value('ATTRIBUTE12',p_Interface_Rec.ATTRIBUTE12);
202 fnd_flex_descval.set_column_value('ATTRIBUTE13',p_Interface_Rec.ATTRIBUTE13);
203 fnd_flex_descval.set_column_value('ATTRIBUTE14',p_Interface_Rec.ATTRIBUTE14);
204 fnd_flex_descval.set_column_value('ATTRIBUTE15',p_Interface_Rec.ATTRIBUTE15);
205
206 fnd_flex_descval.set_context_value(p_Interface_Rec.ATTRIBUTE_CATEGORY);
207
208 /* G_DFF_Error_Column holds the value of the first column that has an error
209 this value is used by log_error to change the column that is displayed
210 in the CM log. See bug 2807931 for more details */
211 G_DFF_Error_Column := '';
212
213 IF fnd_flex_descval.validate_desccols('XTR',p_desc_flex) then
214 if (fnd_flex_descval.is_valid) then
215 null;
216 else
217 l_segment := to_char(fnd_flex_descval.error_segment) ;
218 If l_segment Is not null Then
219 G_DFF_Error_Column := 'Attribute'||l_segment;
220 p_error_segment := 'Attribute16';
221 Else
222 p_error_segment := 'AttributeCategory';
223 End If;
224 return(FALSE);
225 end if;
226
227 if (fnd_flex_descval.value_error OR
228 fnd_flex_descval.unsupported_error) then
229
230 l_segment := to_char(fnd_flex_descval.error_segment) ;
231 If l_segment Is not null Then
232 G_DFF_Error_Column := 'Attribute'||l_segment;
233 p_error_segment := 'Attribute16';
234 Else
235 p_error_segment := 'AttributeCategory';
236 End If;
237
238 return(FALSE);
239 end if;
240
241 return(TRUE);
242
243 ELSE
244 l_segment := to_char(fnd_flex_descval.error_segment) ;
245
246 If l_segment Is not null Then
247 G_DFF_Error_Column := 'Attribute'||l_segment;
248 p_error_segment := 'Attribute16';
249 Else
250 p_error_segment := 'AttributeCategory';
251 End If;
252
253 return(FALSE);
254 END IF;
255
256 END val_desc_flex;
257
258
259 /* val_transaction_desc_flex performs the exact same function as val_desc_flex
260 only it does it for the transaction interface table. Too bad these headers
261 are not polymorphic. Regardless, if you change one of these functions, please
262 make sure to include equivalent changes to this procedure.
263 */
264
265 /*--------------------------------------------------------------------------------*/
266 FUNCTION val_transaction_desc_flex( p_Interface_Rec IN XTR_TRANSACTIONS_INTERFACE%ROWTYPE,
267 p_desc_flex IN VARCHAR2,
268 p_error_segment IN OUT NOCOPY VARCHAR2) return BOOLEAN is
269 /*--------------------------------------------------------------------------------*/
270 l_segment number(3);
271
272 BEGIN
273
274 fnd_flex_descval.set_column_value('ATTRIBUTE1',p_Interface_Rec.ATTRIBUTE1);
275 fnd_flex_descval.set_column_value('ATTRIBUTE2',p_Interface_Rec.ATTRIBUTE2);
276 fnd_flex_descval.set_column_value('ATTRIBUTE3',p_Interface_Rec.ATTRIBUTE3);
277 fnd_flex_descval.set_column_value('ATTRIBUTE4',p_Interface_Rec.ATTRIBUTE4);
278 fnd_flex_descval.set_column_value('ATTRIBUTE5',p_Interface_Rec.ATTRIBUTE5);
279 fnd_flex_descval.set_column_value('ATTRIBUTE6',p_Interface_Rec.ATTRIBUTE6);
280 fnd_flex_descval.set_column_value('ATTRIBUTE7',p_Interface_Rec.ATTRIBUTE7);
281 fnd_flex_descval.set_column_value('ATTRIBUTE8',p_Interface_Rec.ATTRIBUTE8);
282 fnd_flex_descval.set_column_value('ATTRIBUTE9',p_Interface_Rec.ATTRIBUTE9);
283 fnd_flex_descval.set_column_value('ATTRIBUTE10',p_Interface_Rec.ATTRIBUTE10);
284 fnd_flex_descval.set_column_value('ATTRIBUTE11',p_Interface_Rec.ATTRIBUTE11);
285 fnd_flex_descval.set_column_value('ATTRIBUTE12',p_Interface_Rec.ATTRIBUTE12);
286 fnd_flex_descval.set_column_value('ATTRIBUTE13',p_Interface_Rec.ATTRIBUTE13);
287 fnd_flex_descval.set_column_value('ATTRIBUTE14',p_Interface_Rec.ATTRIBUTE14);
288 fnd_flex_descval.set_column_value('ATTRIBUTE15',p_Interface_Rec.ATTRIBUTE15);
289
290 fnd_flex_descval.set_context_value(p_Interface_Rec.ATTRIBUTE_CATEGORY);
291
292 /* G_DFF_Error_Column holds the value of the first column that has an error
293 this value is used by log_error to change the column that is displayed
294 in the CM log. See bug 2807931 for more details */
295 G_DFF_Error_Column := '';
296
297 IF fnd_flex_descval.validate_desccols('XTR',p_desc_flex) then
298 if (fnd_flex_descval.is_valid) then
299 null;
300 else
301 l_segment := to_char(fnd_flex_descval.error_segment) ;
302 If l_segment Is not null Then
303 G_DFF_Error_Column := 'Attribute'||l_segment;
304 p_error_segment := 'Attribute16';
305 Else
306 p_error_segment := 'AttributeCategory';
307 End If;
308 return(FALSE);
309 end if;
310
311 if (fnd_flex_descval.value_error OR
312 fnd_flex_descval.unsupported_error) then
313
314 l_segment := to_char(fnd_flex_descval.error_segment) ;
315 If l_segment Is not null Then
316 G_DFF_Error_Column := 'Attribute'||l_segment;
317 p_error_segment := 'Attribute16';
318 Else
319 p_error_segment := 'AttributeCategory';
320 End If;
321
322 return(FALSE);
323 end if;
324
325 return(TRUE);
326
327 ELSE
328 l_segment := to_char(fnd_flex_descval.error_segment) ;
329
330 If l_segment Is not null Then
331 G_DFF_Error_Column := 'Attribute'||l_segment;
332 p_error_segment := 'Attribute16';
333 Else
334 p_error_segment := 'AttributeCategory';
335 End If;
336
337 return(FALSE);
338 END IF;
339
340 END val_transaction_desc_flex;
341
342
343 /*
344 Translate_Deal_Details changes information from the meaning to
345 lookup code values for specific columns of the specified deal
346 type. This allows the import script to use either what is
347 seen on the form, or the underlying code. We recommend using
348 the underlying code value because it is not mutable.
349 If you call this from the UI, use Translate_Deal_Details_UI
350 to send the translated values back into the table
351 */
352 /* For the CE Bank Migration Enhancement, The CPARTY account
353 name / number translaction is also placed in this procedure
354 */
355 /*--------------------------------------------------------------*/
356 Procedure Translate_Deal_Details( deal_type In Varchar2,
357 ARec In Out NOCOPY xtr_deals_interface%rowtype) is
358 /*--------------------------------------------------------------*/
359
360 b_updated Boolean:=false;
361
362 /*
363 Translate_Column is a helper function for Translate_Deal_Details
364 that takes care of the translation when given a specified column
365 */
366 Procedure Translate_Value(p_lookupType In Varchar2,
367 p_value in out nocopy Varchar2
368 ) is
369 cursor translate_cursor is
370 select lookup_code
371 from fnd_lookups
372 where lookup_type=p_lookupType
373 and (upper(lookup_code)=upper(p_value) or upper(meaning)=upper(p_value))
374 and rownum=1;
375 v_oldValue Varchar2(80):=p_value;
376 BEGIN
377 open translate_cursor;
378 fetch translate_cursor into p_value;
379 close translate_cursor;
380 if (v_oldValue<>p_value) then
381 b_updated:=true;
382 end if;
383 END Translate_Value;
384
385 /*
386 Translate_accounts is a helper function for Translate_Deal_Details
387 that takes care of the translation from account name -> number and
388 vis versa.
389 Because of the translation the values will be valid/invalid together
390 thus validation can be performed on one or the other column as
391 long as the error is placed under the modifiable field.
392 This means we can leave the old validation code untouched.
393 */
394 Procedure Translate_Accounts(p_account_number In Out nocopy Varchar2,
395 p_account_name In Out nocopy Varchar2
396 ) is
397 p_new_name XTR_DEALS_INTERFACE.CPARTY_REF%TYPE;
398 p_new_number XTR_DEALS_INTERFACE.CPARTY_ACCOUNT_NO%TYPE;
399 b_local_updated BOOLEAN := FALSE;
400 CURSOR translate_by_number(p_number VARCHAR2) is
401 SELECT bank_short_code
402 FROM xtr_bank_accounts_v
403 WHERE account_number = p_number;
404
405 CURSOR translate_by_name(p_name VARCHAR2) IS
406 SELECT account_number
407 FROM xtr_bank_accounts_v
408 WHERE bank_short_code=p_name;
409
410 BEGIN
411 IF p_account_number IS NOT NULL THEN
412 OPEN translate_by_number(p_account_number);
413 FETCH translate_by_number INTO p_new_name;
414 CLOSE translate_by_number;
415 p_new_number:=p_account_number;
416 ELSIF p_account_name IS NOT NULL THEN
417 OPEN translate_by_name(p_account_name);
418 FETCH translate_by_name INTO p_new_number;
419 CLOSE translate_by_name;
420 p_new_name:=p_account_name;
421 END IF;
422 IF p_new_number IS NOT NULL AND (p_new_number<>p_account_number OR p_account_number IS NULL) THEN
423 b_local_updated:=TRUE;
424 ELSIF p_new_name IS NOT NULL AND (p_new_name<>p_account_name OR p_account_name IS NULL) THEN
425 b_local_updated:=TRUE;
426 END IF;
427 IF (b_local_updated) THEN
428 p_account_number := p_new_number;
429 p_account_name := p_new_name;
430 b_updated := TRUE;
431 END IF;
432 END;
433
434 BEGIN
435 if (deal_type='FX') then
436 Translate_Value('XTR_DEAL_PRICE_MODELS', ARec.PRICING_MODEL);
437 Translate_Accounts(ARec.CPARTY_ACCOUNT_NO, ARec.CPARTY_REF);
438 elsif (deal_type='IG') then
439 Translate_Value('XTR_DAY_COUNT_TYPE', ARec.DAY_COUNT_TYPE);
440 Translate_Value('XTR_ROUNDING_TYPE', ARec.ROUNDING_TYPE);
441 Translate_Value('XTR_DEAL_PRICE_MODELS', ARec.PRICING_MODEL);
442 Translate_Value('XTR_DEAL_PRICE_MODELS', ARec.MIRROR_PRICING_MODEL);
443 elsif (deal_type='NI') then
444 Translate_Value('XTR_DEAL_PRICE_MODELS', ARec.PRICING_MODEL);
445 Translate_Value('XTR_PRINCIPAL_SETTLED_BY', ARec.SETTLE_ACTION_REQD);
446 Translate_Value('XTR_DISCOUNT_YIELD', ARec.BASIS_TYPE);
447 Translate_Value('XTR_DAY_COUNT_BASIS', ARec.YEAR_CALC_TYPE);
448 Translate_Value('XTR_DAY_COUNT_TYPE', ARec.DAY_COUNT_TYPE);
449 Translate_Value('XTR_ROUNDING_TYPE', ARec.ROUNDING_TYPE);
450 Translate_Accounts(ARec.CPARTY_ACCOUNT_NO, ARec.CPARTY_REF);
451 elsif (deal_type='EXP') then
452 Translate_Accounts(ARec.CPARTY_ACCOUNT_NO, ARec.ACCOUNT_NO_B);
453 end if;
454
455
456 if (b_updated) then
457 update xtr_deals_interface
458 set PRICING_MODEL = ARec.PRICING_MODEL,
459 MIRROR_PRICING_MODEL = ARec.MIRROR_PRICING_MODEL,
460 SETTLE_ACTION_REQD = ARec.SETTLE_ACTION_REQD,
461 BASIS_TYPE = ARec.BASIS_TYPE,
462 YEAR_CALC_TYPE = ARec.YEAR_CALC_TYPE,
463 DAY_COUNT_TYPE = ARec.DAY_COUNT_TYPE,
464 ROUNDING_TYPE = ARec.ROUNDING_TYPE,
465 CPARTY_ACCOUNT_NO = ARec.CPARTY_ACCOUNT_NO,
466 CPARTY_REF = ARec.CPARTY_REF,
467 ACCOUNT_NO_B = ARec.ACCOUNT_NO_B
468 where external_deal_id = ARec.EXTERNAL_DEAL_ID
469 and deal_type = ARec.DEAL_TYPE;
470 end if;
471
472 END Translate_Deal_Details;
473
474 /*
475 This is the call to be made from the UI, pass in the external_deal_id
476 and user_deal_type and this call will then call Translate_Deal_Details
477 */
478 /*--------------------------------------------------------------*/
479 Procedure Translate_Deal_Details_UI(p_external_deal_id xtr_deals_interface.external_deal_id%type,
480 p_user_deal_type xtr_deals_interface.deal_type%type) is
481 /*--------------------------------------------------------------*/
482
483 l_deal_type xtr_deals_interface.external_deal_id%type:=null;
484 ARec xtr_deals_interface%rowtype;
485
486 cursor getDeal is
487 select *
488 from xtr_deals_interface
489 where external_deal_id = p_external_deal_id
490 and deal_type=p_user_deal_type;
491 Cursor getDealType Is
492 Select Deal_Type
493 From Xtr_Deal_Types_V
494 Where User_Deal_Type = p_user_deal_type;
495 BEGIN
496 open getDeal;
497 fetch getDeal into ARec;
498 if (getDeal%FOUND) then
499 Open getDealType;
500 Fetch getDealType Into l_deal_type;
501 Close getDealType;
502 Translate_Deal_Details(l_deal_type,ARec);
503 end if;
504 close getDeal;
505
506
507 END Translate_Deal_Details_UI;
508
509
510
511 Procedure log_successful_deal(Deal_Type IN VARCHAR2,
512 Deal_Number IN NUMBER,
513 Deal_Subtype IN VARCHAR2,
514 Product_Type IN VARCHAR2,
515 Company_Code IN VARCHAR2,
516 Cparty_Code IN VARCHAR2,
517 Currency IN VARCHAR2,
518 Amount IN NUMBER) IS
519 BEGIN
520 put_log(
521 lpad(nvl(Deal_Type ,' '), 5)||','||
522 lpad(nvl(Deal_Number ,0 ),15)||','||
523 lpad(nvl(Deal_Subtype ,' '), 9)||','||
524 lpad(nvl(Product_Type ,' '), 10)||','|| --Bug 9904436
525 lpad(nvl(Company_Code ,' '), 9)||','||
526 lpad(nvl(Cparty_Code ,' '), 9)||','||
527 lpad(nvl(Currency ,' '), 4)||','||
528 lpad(to_char(nvl(Amount,0),fnd_currency.get_format_mask(nvl(Currency,'USD'),20)),20));
529 for i in 0..g_current_deal_log_list.count-1 loop
530 put_log(g_current_deal_log_list(i));
531 end loop;
532 g_current_deal_log_list.delete;
533 END LOG_SUCCESSFUL_DEAL;
534
535 Procedure log_failed_deal(Deal_Type IN VARCHAR2,
536 External_Deal_Id IN VARCHAR2,
537 Deal_Subtype IN VARCHAR2,
538 Product_Type IN VARCHAR2,
539 Company_Code IN VARCHAR2,
540 Cparty_Code IN VARCHAR2,
541 Currency IN VARCHAR2,
542 Amount IN NUMBER) IS
543 BEGIN
544 g_failure_log_list(g_failure_log_list.count):=' ';
545 g_failure_log_list(g_failure_log_list.count):=(
546 lpad(nvl(Deal_Type ,' '), 5)||','||
547 lpad(nvl(External_Deal_Id ,' '),15)||','||
548 lpad(nvl(Deal_Subtype ,' '), 9)||','||
549 lpad(nvl(Product_Type ,' '), 10)||','|| --Bug 9904436
550 lpad(nvl(Company_Code ,' '), 9)||','||
551 lpad(nvl(Cparty_Code ,' '), 9)||','||
552 lpad(nvl(Currency ,' '), 4)||','||
553 lpad(to_char(nvl(Amount,0),fnd_currency.get_format_mask(nvl(Currency,'USD'),20)),20));
554 for i in 0..g_current_deal_log_list.count-1 loop
555 g_failure_log_list(g_failure_log_list.count):=g_current_deal_log_list(i);
556 end loop;
557 g_current_deal_log_list.delete;
558 END LOG_FAILED_DEAL;
559
560
561
562 /*--------------------------------------------------------------*/
563
564 /*--------------------------------------------------------------*/
565 Procedure TRANSFER_DEALS( ERRBUF Out nocopy Varchar2,
566 RETCODE Out nocopy Varchar2,
567 P_Company_Code In Varchar2,
568 P_Deal_Type In Varchar2,
569 P_Ext_Deal_Id_From In Varchar2,
570 P_Ext_Deal_Id_To In Varchar2,
571 P_Load_Status In Varchar2,
572 P_Source In Varchar2) is
573 /*--------------------------------------------------------------*/
574
575
576 /*--------------------------------------------*/
577 /* Call from form: select all 'SUBMIT' deals */
578 /*--------------------------------------------*/
579 Cursor Int_Form_Deal_Cursor(bDeal_Type In Varchar2,
580 bDeal_Fr In Varchar2,
581 bDeal_To In Varchar2) Is
582 Select *
583 From Xtr_Deals_Interface
584 Where Nvl(Load_Status_Code,'NEW') = 'SUBMIT'
585 Order By Deal_Type, company_code, cparty_code, currency_a, date_a, external_deal_id;
586
587
588 /*----------------------------------------------------------*/
589 /* Call from concurrent program: select deals from criteria */
590 /*----------------------------------------------------------*/
591 Cursor Int_Con_Deal_Cursor(bDeal_Type In Varchar2,
592 bDeal_Fr In Varchar2,
593 bDeal_To In Varchar2,
594 bCompany_Code In Varchar2,
595 bLoad_Status In Varchar) Is
596 Select *
597 From Xtr_Deals_Interface
598 Where External_Deal_Id Between Nvl(bDeal_Fr,External_Deal_Id)
599 And Nvl(bDeal_To,External_Deal_Id)
600 And Company_Code Like Nvl(bCompany_Code,'%')
601 And Deal_Type Like Nvl(bDeal_Type,'%')
602 And Nvl(Load_Status_Code,'NEW') like nvl(bLoad_status,'%')
603 Order By Deal_Type, company_code, cparty_code, currency_a, date_a, external_deal_id;
604
605
606 /*------------------------------------------------------*/
607 /* Determine if the deal type is invalid or supported. */
608 /*------------------------------------------------------*/
609 Cursor Valid_Deal_Type(B_Deal_Type In Varchar2) Is
610 Select Deal_Type
611 From Xtr_Deal_Types_V
612 Where User_Deal_Type = B_Deal_Type;
613
614 /*------------------------------------------------------*/
615 /* Determine if the deal type is invalid or supported. */
616 /*------------------------------------------------------*/
617 Cursor Get_deal_name(p_deal_type In Varchar2) Is
618 Select Name
619 From Xtr_Deal_Types_V
620 Where Deal_Type = p_deal_type;
621
622
623 /*-------------------*/
624 /* Local variables. */
625 /*-------------------*/
626 LRec_External_Deal xtr_deals_interface%rowtype;
627 L_Deal_Name xtr_deal_types_v.name%type;
628 L_Deal_Type xtr_deal_types_v.deal_type%type;
629 deal_error BOOLEAN Default FALSE;
630 user_error BOOLEAN Default FALSE;
631 duplicate_error BOOLEAN Default FALSE;
632 mandatory_error BOOLEAN Default FALSE;
633 validation_error BOOLEAN Default FALSE;
634 limit_error BOOLEAN Default FALSE;
635
636 p_has_warnings BOOLEAN Default FALSE;
637
638 p_is_first_fx BOOLEAN Default TRUE;
639 p_is_first_ig BOOLEAN Default TRUE;
640 p_is_first_exp BOOLEAN Default TRUE;
641 p_is_first_ni BOOLEAN Default TRUE;
642
643 p_deal_no Number := 0;
644
645 p_total_fx Number := 0;
646 p_success_fx Number := 0;
647 p_failure_fx Number := 0;
648 p_tot_suc_buy_amt_fx Number := 0;
649 p_tot_suc_sell_amt_fx Number := 0;
650 p_tot_fai_buy_amt_fx Number := 0;
651 p_tot_fai_sell_amt_fx Number := 0;
652
653 p_total_ig Number := 0;
654 p_success_ig Number := 0;
655 p_failure_ig Number := 0;
656 p_tot_suc_prin_amt_ig Number := 0;
657 p_tot_fai_prin_amt_ig Number := 0;
658
659 p_total_exp Number := 0;
660 p_success_exp Number := 0;
661 p_failure_exp Number := 0;
662 p_tot_suc_est_amt_exp Number := 0;
663 p_tot_suc_act_amt_exp Number := 0;
664 p_tot_fai_est_amt_exp Number := 0;
665 p_tot_fai_act_amt_exp Number := 0;
666
667 p_total_ni Number := 0;
668 p_success_ni Number := 0;
669 p_failure_ni Number := 0;
670 p_tot_suc_amt_ni Number := 0;
671 p_tot_suc_amt_ni Number := 0;
672
673 p_is_success BOOLEAN Default FALSE;
674
675 /*-----------------------------------------------------------------------------------*/
676 /* Transfer_Deal_Protected is a wrapper that provides system critical error handling */
677 /* In the event of an unforseen error, undoes the damage and allows recovery */
678 /* This allows for item level exception handling */
679 /*-----------------------------------------------------------------------------------*/
680 Procedure Transfer_Deal_Protected
681 Is
682 Begin
683
684 p_is_success:=false;
685 ----------------------------------------------------------------------------------
686 --* To purge all the related data in the error table before processing the record
687 ----------------------------------------------------------------------------------
688 delete from xtr_interface_errors
689 where external_deal_id = LRec_External_Deal.external_deal_id
690 and deal_type = LRec_External_Deal.deal_type;
691
692
693
694 ------------------------------------------------------------------------------------
695 --* To purge all orphaned transaction data that was not deleted with the deal header
696 ------------------------------------------------------------------------------------
697 delete
698 from xtr_transactions_interface tr
699 where not exists (
700 select dl.external_deal_id
701 from xtr_deals_interface dl
702 where dl.external_deal_id = tr.external_deal_id
703 and dl.deal_type = tr.deal_type
704 );
705
706
707 --* call the appropriate validation and transfer package
708 --* based on curr_deal_type
709
710 IF L_Deal_Type = 'FX' Then
711 p_total_fx := p_total_fx +1;
712 if (p_is_first_fx) then
713 p_is_first_fx:=false;
714 put_log(' ');
715 end if;
716
717 ----------------------------------------------------------------------------------------------
718 --* The following code checks for duplicate External Deal ID in the interface and deal tables
719 ----------------------------------------------------------------------------------------------
720 CHECK_DEAL_DUPLICATE_ID(LRec_External_Deal.external_deal_id,
721 LRec_External_Deal.deal_type,
722 L_Deal_Type, duplicate_error);
723
724 if Duplicate_Error then
725 p_failure_fx := p_failure_fx + 1;
726 p_tot_fai_buy_amt_fx := p_tot_fai_buy_amt_fx + LRec_External_Deal.amount_a;
727 p_tot_fai_sell_amt_fx := p_tot_fai_sell_amt_fx + LRec_External_Deal.amount_b;
728 p_has_warnings := true;
729
730 else
731
732 XTR_FX_TRANSFERS_PKG.TRANSFER_FX_DEALS(LRec_External_Deal,
733 user_error,
734 mandatory_error,
735 validation_error,
736 limit_error,
737 p_deal_no);
738
739 if not User_Error and not Mandatory_Error And
740 not Validation_Error and not Limit_Error then
741 p_success_fx := p_success_fx + 1;
742 p_tot_suc_buy_amt_fx := p_tot_suc_buy_amt_fx + LRec_External_Deal.amount_a;
743 p_tot_suc_sell_amt_fx := p_tot_suc_sell_amt_fx + LRec_External_Deal.amount_b;
744 p_is_success := true;
745 else
746 p_failure_fx := p_failure_fx + 1;
747 p_tot_fai_buy_amt_fx := p_tot_fai_buy_amt_fx + LRec_External_Deal.amount_a;
748 p_tot_fai_sell_amt_fx := p_tot_fai_sell_amt_fx + LRec_External_Deal.amount_b;
749 p_has_warnings := true;
750 end if;
751 end if; /* FX Duplicate_Error */
752
753 ELSIF L_Deal_Type = 'IG' Then
754 p_total_ig := p_total_ig +1;
755 if (p_is_first_ig) then
756 p_is_first_ig:=false;
757 put_log(' ');
758 end if;
759
760 ----------------------------------------------------------------------------------------------
761 --* The following code checks for duplicate External Deal ID in the interface and IG tables
762 ----------------------------------------------------------------------------------------------
763 CHECK_DEAL_DUPLICATE_ID(LRec_External_Deal.external_deal_id,
764 LRec_External_Deal.deal_type,
765 L_Deal_Type, duplicate_error);
766
767 if Duplicate_Error then
768 p_failure_ig := p_failure_ig + 1;
769 p_tot_fai_prin_amt_ig := p_tot_fai_prin_amt_ig + LRec_External_Deal.amount_a;
770 p_has_warnings := true;
771
772 else
773
774 XTR_IG_TRANSFERS_PKG.TRANSFER_IG_DEALS(LRec_External_Deal,
775 null,
776 user_error,
777 mandatory_error,
778 validation_error,
779 limit_error,
780 p_deal_no);
781
782 if not user_error and not mandatory_error and
783 not validation_error and not limit_error then
784 p_success_ig := p_success_ig + 1;
785 p_tot_suc_prin_amt_ig := p_tot_suc_prin_amt_ig + LRec_External_Deal.amount_a;
786 p_is_success := true;
787 else
788 p_failure_ig := p_failure_ig + 1;
789 p_tot_fai_prin_amt_ig := p_tot_fai_prin_amt_ig + LRec_External_Deal.amount_a;
790 p_has_warnings := true;
791 end if;
792 end if; /* IG Duplicate_Error */
793
794 ELSIF L_Deal_Type = 'EXP' Then
795 p_total_exp := p_total_exp +1;
796 if (p_is_first_exp) then
797 p_is_first_exp:=false;
798 put_log(' ');
799 end if;
800
801 ----------------------------------------------------
802 --* The following code checks for duplicate External
803 --* Deal ID in the interface and IG tables
804 ----------------------------------------------------
805 CHECK_DEAL_DUPLICATE_ID(
806 LRec_External_Deal.external_deal_id,
807 LRec_External_Deal.deal_type,
808 L_Deal_Type, duplicate_error);
809
810 if Duplicate_Error then
811 p_failure_exp := p_failure_exp + 1;
812 p_tot_fai_est_amt_exp := p_tot_fai_est_amt_exp+
813 LRec_External_Deal.amount_a;
814 p_tot_fai_act_amt_exp := p_tot_fai_act_amt_exp+
815 LRec_External_Deal.amount_a;
816 p_has_warnings := true;
817 else
818 XTR_EXP_TRANSFERS_PKG.TRANSFER_EXP_DEALS(
819 LRec_External_Deal,
820 null,
821 user_error,
822 mandatory_error,
823 validation_error,
824 limit_error,
825 p_deal_no);
826
827 if not user_error and not mandatory_error and
828 not validation_error and not limit_error then
829 p_success_exp := p_success_exp + 1;
830 p_tot_suc_est_amt_exp:=p_tot_suc_est_amt_exp
831 + LRec_External_Deal.amount_a;
832 p_tot_suc_act_amt_exp:=p_tot_suc_act_amt_exp
833 + LRec_External_Deal.amount_a;
834 p_is_success := true;
835 else
836 p_failure_exp := p_failure_exp + 1;
837 p_has_warnings := true;
838 p_tot_fai_est_amt_exp:=p_tot_fai_est_amt_exp
839 + LRec_External_Deal.amount_a;
840 p_tot_fai_act_amt_exp:=p_tot_fai_act_amt_exp
841 + LRec_External_Deal.amount_a;
842 end if;
843 end if; /* EXP Duplicate_Error */
844
845 ELSIF L_Deal_Type = 'NI' Then
846 p_total_ni := p_total_ni +1;
847 if (p_is_first_ni) then
848 p_is_first_ni:=false;
849 put_log(' ');
850 end if;
851
852 ----------------------------------------------------------------------------------------------
853 --* The following code checks for duplicate External Deal ID in the interface and deal tables
854 ----------------------------------------------------------------------------------------------
855 CHECK_DEAL_DUPLICATE_ID(LRec_External_Deal.external_deal_id,
856 LRec_External_Deal.deal_type,
857 L_Deal_Type, duplicate_error);
858
859 if Duplicate_Error then
860 p_failure_ni := p_failure_ni + 1;
861 p_has_warnings := true;
862 --WDK: How do we mark failed amount? Don't know without looking into transaction details
863 --p_tot_fai_amt_ni := p_tot_fai_amt_ni + LRec_External_Deal.amount_a;
864
865 else
866
867 XTR_NI_TRANSFERS_PKG.TRANSFER_NI_DEALS(LRec_External_Deal,
868 user_error,
869 mandatory_error,
870 validation_error,
871 limit_error,
872 p_deal_no);
873
874 if not User_Error and not Mandatory_Error And
875 not Validation_Error and not Limit_Error then
876 p_success_ni := p_success_ni + 1;
877 p_is_success := true;
878 --WDK: Can we use amount_a to store accumulation?
879 --p_tot_suc_amt_ni := p_suc_suc_amt_ni + LRec_External_Deal.amount_a;
880 else
881 p_failure_ni := p_failure_ni + 1;
882 p_has_warnings := true;
883 --WDK: Can we use amount_a to store accumulation?
884 --Stop short of transaction total on failure?
885 --p_tot_fai_amt_ni := p_suc_fai_amt_ni + LRec_External_Deal.amount_a;
886 end if;
887 end if; /* NI Duplicate_Error */
888
889 ELSE
890 -------------------------------------------------------------
891 --* update as the same error for unsupported deal types also
892 -------------------------------------------------------------
893 UPDATE xtr_deals_interface
894 set load_status_code = 'DEAL_TYPE_ERROR',
895 last_update_date = trunc(SYSDATE),
896 Last_Updated_by = fnd_global.user_id
897 where external_deal_id = LRec_External_Deal.external_deal_id
898 and deal_type = LRec_External_Deal.deal_type;
899
900 p_has_warnings:=true;
901
902 Log_Interface_Errors(LRec_External_Deal.external_deal_id,
903 LRec_External_Deal.deal_type,
904 Null,
905 'XTR_INV_DEAL_TYPE');
906
907
908 END IF;
909
910 if (p_has_warnings) then
911 g_has_warnings:=true;
912 end if;
913
914 if (p_is_success) then
915 log_successful_deal(LRec_External_Deal.Deal_Type,
916 p_deal_no,
917 LRec_External_Deal.Deal_Subtype,
918 LRec_External_Deal.Product_Type,
919 LRec_External_Deal.Company_Code,
920 LRec_External_Deal.Cparty_Code,
921 LRec_External_Deal.Currency_A,
922 LRec_External_Deal.Amount_A);
923 else
924 log_failed_deal(LRec_External_Deal.Deal_Type,
925 LRec_External_Deal.External_Deal_Id,
926 LRec_External_Deal.Deal_Subtype,
927 LRec_External_Deal.Product_Type,
928 LRec_External_Deal.Company_Code,
929 LRec_External_Deal.Cparty_Code,
930 LRec_External_Deal.Currency_A,
931 LRec_External_Deal.Amount_A);
932 end if;
933
934 Exception
935 When Others Then
936 UPDATE Xtr_Deals_Interface
937 SET Load_Status_Code='ERROR'
938 WHERE External_Deal_Id=LRec_External_Deal.External_Deal_Id;
939
940 p_has_warnings:=true;
941 g_has_warnings:=true;
942
943 Log_Interface_Errors(LRec_External_Deal.external_deal_id,
944 LRec_External_Deal.deal_type,
945 Null,
946 'XTR_IMPORT_UNEXPECTED_ERROR');
947
948 if l_deal_type='FX' then
949 p_failure_fx := p_failure_fx + 1;
950 p_tot_fai_buy_amt_fx := p_tot_fai_buy_amt_fx + LRec_External_Deal.amount_a;
951 p_tot_fai_sell_amt_fx := p_tot_fai_sell_amt_fx + LRec_External_Deal.amount_b;
952 elsif l_deal_type='EXP' then
953 p_failure_exp := p_failure_exp + 1;
954 p_tot_fai_est_amt_exp := p_tot_fai_est_amt_exp + LRec_External_Deal.amount_a;
955 p_tot_fai_act_amt_exp := p_tot_fai_act_amt_exp + LRec_External_Deal.amount_a;
956 elsif l_deal_type='IG' then
957 p_failure_ig := p_failure_ig + 1;
958 p_tot_fai_prin_amt_ig := p_tot_fai_prin_amt_ig + LRec_External_Deal.amount_a;
959 elsif l_deal_type='NI' then
960 p_failure_ni := p_failure_ni + 1;
961 end if;
962
963
964 log_failed_deal(LRec_External_Deal.Deal_Type,
965 LRec_External_Deal.External_Deal_Id,
966 LRec_External_Deal.Deal_Subtype,
967 LRec_External_Deal.Product_Type,
968 LRec_External_Deal.Company_Code,
969 LRec_External_Deal.Cparty_Code,
970 LRec_External_Deal.Currency_A,
971 LRec_External_Deal.Amount_A);
972
973
974 End;
975
976
977
978 Begin
979 xtr_risk_debug_pkg.start_conc_prog;
980
981 /*-------------------------------------------------------------------------*/
982 /* Setting the user id which will be used in the CHECK_USER_AUTH procedure */
983 /*-------------------------------------------------------------------------*/
984 IF P_Source = 'FORM' Then
985 Open Int_Form_Deal_Cursor (P_Deal_Type, P_Ext_Deal_Id_From, P_Ext_Deal_Id_To);
986 Fetch Int_Form_Deal_Cursor Into LRec_External_Deal;
987 ELSE
988 Open Int_Con_Deal_Cursor (P_Deal_Type, P_Ext_Deal_Id_From, P_Ext_Deal_Id_To, P_Company_Code, P_Load_Status);
989 Fetch Int_Con_Deal_Cursor Into LRec_External_Deal;
990 END IF;
991
992 put_log(fnd_message.get_string('XTR','XTR_SUCCESSFUL_IMPORT'));
993 put_log(lpad('-',79,'-'));
994 put_log(fnd_message.get_string('XTR','XTR_IMPORT_HEADER'));
995
996 g_current_deal_log_list.delete;
997 g_failure_log_list.delete;
998
999 -----------------------------------
1000 --* Loop through all the records
1001 -----------------------------------
1002 LOOP
1003
1004 deal_error := FALSE;
1005 user_error := FALSE;
1006 duplicate_error := FALSE;
1007 mandatory_error := FALSE;
1008 validation_error := FALSE;
1009 limit_error := FALSE;
1010
1011 IF P_Source = 'FORM' Then
1012 Exit When Int_Form_Deal_Cursor%NotFound;
1013 ELSE
1014 Exit When Int_Con_Deal_Cursor%NotFound;
1015 END IF;
1016
1017 --* Validate deal type
1018 --* Need Not do this as the cursor itself is picked on this
1019 --* Condition Only. So only valid records would be fetched in the
1020 --* Cursor for Importing.
1021 --* But need to do this validation when the cursor is fetched for all deal types
1022
1023 L_Deal_Type := null;
1024
1025 Open Valid_Deal_Type(LRec_External_Deal.Deal_Type);
1026 Fetch Valid_Deal_Type Into L_Deal_Type;
1027 Close Valid_Deal_Type;
1028
1029
1030 IF L_Deal_Type is null then
1031
1032 UPDATE xtr_deals_interface
1033 set load_status_code = 'DEAL_TYPE_ERROR',
1034 last_update_date = trunc(SYSDATE),
1035 Last_Updated_by = fnd_global.user_id
1036 where external_deal_id = LRec_External_Deal.external_deal_id
1037 and deal_type = LRec_External_Deal.deal_type;
1038
1039 g_has_warnings:=true;
1040 Log_Interface_Errors(LRec_External_Deal.external_deal_id,
1041 LRec_External_Deal.deal_type,
1042 Null,
1043 'XTR_INV_DEAL_TYPE');
1044
1045 log_failed_deal(LRec_External_Deal.Deal_Type,
1046 LRec_External_Deal.External_Deal_Id,
1047 LRec_External_Deal.Deal_Subtype,
1048 LRec_External_Deal.Product_Type,
1049 LRec_External_Deal.Company_Code,
1050 LRec_External_Deal.Cparty_Code,
1051 LRec_External_Deal.Currency_A,
1052 LRec_External_Deal.Amount_A);
1053
1054
1055
1056 ELSE
1057
1058 Translate_Deal_Details(L_Deal_Type,LRec_External_Deal);
1059
1060 -- The following is an exception handling wrapper
1061 Transfer_Deal_Protected;
1062
1063
1064
1065
1066 END IF;
1067
1068 IF P_Source = 'FORM' Then
1069 Fetch Int_Form_Deal_Cursor Into LRec_External_Deal;
1070 ELSE
1071 Fetch Int_Con_Deal_Cursor Into LRec_External_Deal;
1072 END IF;
1073
1074
1075 END LOOP;
1076
1077 put_log(' ');
1078 put_log(fnd_message.get_string('XTR','XTR_FAILED_IMPORT'));
1079 put_log(lpad('-',79,'-'));
1080 put_log(fnd_message.get_string('XTR','XTR_IMPORT_FAIL_HEADER'));
1081 for i in 0..g_failure_log_list.count-1 loop
1082 put_log(g_failure_log_list(i));
1083 end loop;
1084
1085
1086 /* ------------------------------------------------
1087 Sample log file for FX
1088 ------------------------------------------------
1089 Foreign Exchange
1090 --------------------
1091 Total number of deals: 50
1092
1093 Total number of successful transfers: 48
1094 Control total buy amount is 123456.78
1095 Control total sell amount is 25252.52
1096
1097 Total number of unsuccessful transfers: 2
1098 Control total buy amount is 1001.01
1099 Control total sell amount is 505.50
1100
1101 */ ------------------------------------------------
1102 IF p_total_fx <> 0 then
1103
1104 Open Get_deal_name('FX');
1105 Fetch Get_deal_name Into L_Deal_Name;
1106 Close Get_deal_name;
1107
1108 put_log(' ');
1109
1110 put_log(L_Deal_Name);
1111 put_log(rpad('-',length(L_Deal_Name),'-'));
1112
1113 Fnd_Message.Set_Name('XTR','XTR_TOT_DEALS');
1114 Fnd_Message.Set_Token('VALUE',p_total_fx);
1115 put_log(Fnd_Message.Get);
1116 Fnd_Message.Clear;
1117
1118 put_log(' ');
1119
1120 Fnd_Message.Set_Name('XTR','XTR_TOT_SUCCESS_DEALS');
1121 Fnd_Message.Set_Token('VALUE',p_success_fx);
1122 put_log(Fnd_Message.Get);
1123 Fnd_Message.Clear;
1124
1125 Fnd_Message.Set_Name('XTR','XTR_FX_CTRL_TOTAL_BUY');
1126 Fnd_Message.Set_Token('VALUE',p_tot_suc_buy_amt_fx);
1127 put_log(Fnd_Message.Get);
1128 Fnd_Message.Clear;
1129
1130 Fnd_Message.Set_Name('XTR','XTR_FX_CTRL_TOTAL_SELL');
1131 Fnd_Message.Set_Token('VALUE',p_tot_suc_sell_amt_fx);
1132 put_log(Fnd_Message.Get);
1133 Fnd_Message.Clear;
1134
1135 put_log(' ');
1136
1137 Fnd_Message.Set_Name('XTR','XTR_TOT_UNSUCCESS_DEALS');
1138 Fnd_Message.Set_Token('VALUE',p_failure_fx);
1139 put_log(Fnd_Message.Get);
1140 Fnd_Message.Clear;
1141
1142 Fnd_Message.Set_Name('XTR','XTR_FX_CTRL_TOTAL_BUY');
1143 Fnd_Message.Set_Token('VALUE',p_tot_fai_buy_amt_fx);
1144 put_log(Fnd_Message.Get);
1145 Fnd_Message.Clear;
1146
1147 Fnd_Message.Set_Name('XTR','XTR_FX_CTRL_TOTAL_SELL');
1148 Fnd_Message.Set_Token('VALUE',p_tot_fai_sell_amt_fx);
1149 put_log(Fnd_Message.Get);
1150 Fnd_Message.Clear;
1151
1152 END IF;
1153
1154 /* ------------------------------------------------
1155 Sample log file for IG
1156 ------------------------------------------------
1157 Intergroup Transfers
1158 --------------------
1159 Total number of deals: 50
1160
1161 Total number of successful transfers: 48
1162 Control total principal adjustment amount is 123456.78
1163
1164 Total number of unsuccessful transfers: 2
1165 Control total principal adjustment amount is 1001.01
1166
1167 */ ------------------------------------------------
1168 IF p_total_ig <> 0 then
1169
1170 Open Get_deal_name('IG');
1171 Fetch Get_deal_name Into L_Deal_Name;
1172 Close Get_deal_name;
1173
1174 put_log(' ');
1175
1176 put_log(L_Deal_Name);
1177 put_log(rpad('-',length(L_Deal_Name),'-'));
1178
1179 Fnd_Message.Set_Name('XTR','XTR_TOT_DEALS');
1180 Fnd_Message.Set_Token('VALUE',p_total_ig);
1181 put_log(Fnd_Message.Get);
1182 Fnd_Message.Clear;
1183
1184 put_log(' ');
1185
1186 Fnd_Message.Set_Name('XTR','XTR_TOT_SUCCESS_DEALS');
1187 Fnd_Message.Set_Token('VALUE',p_success_ig);
1188 put_log(Fnd_Message.Get);
1189 Fnd_Message.Clear;
1190
1191 Fnd_Message.Set_Name('XTR','XTR_IG_CTRL_TOTAL');
1192 Fnd_Message.Set_Token('VALUE',p_tot_suc_prin_amt_ig);
1193 put_log(Fnd_Message.Get);
1194 Fnd_Message.Clear;
1195
1196 put_log(' ');
1197
1198 Fnd_Message.Set_Name('XTR','XTR_TOT_UNSUCCESS_DEALS');
1199 Fnd_Message.Set_Token('VALUE',p_failure_ig);
1200 put_log(Fnd_Message.Get);
1201 Fnd_Message.Clear;
1202
1203 Fnd_Message.Set_Name('XTR','XTR_IG_CTRL_TOTAL');
1204 Fnd_Message.Set_Token('VALUE',p_tot_fai_prin_amt_ig);
1205 put_log(Fnd_Message.Get);
1206 Fnd_Message.Clear;
1207
1208
1209 END IF;
1210
1211 /* ------------------------------------------------
1212 Sample log file for EXP
1213 ------------------------------------------------
1214 Exposure Transactions
1215 ---------------------
1216 Total number of deals: 50
1217
1218 Total number of successful transfers: 48
1219 Control total estimate amount is 123456.78
1220 Control total actual amount is 25252.52
1221
1222 Total number of unsuccessful transfers: 2
1223 Control total estimate amount is 1001.01
1224 Control total actual amount is 505.50
1225
1226 */ ------------------------------------------------
1227 IF p_total_exp <> 0 then
1228
1229 Open Get_deal_name('EXP');
1230 Fetch Get_deal_name Into L_Deal_Name;
1231 Close Get_deal_name;
1232
1233 put_log(' ');
1234
1235 put_log(L_Deal_Name);
1236 put_log(rpad('-',length(L_Deal_Name),'-'));
1237
1238 Fnd_Message.Set_Name('XTR','XTR_TOT_DEALS');
1239 Fnd_Message.Set_Token('VALUE',p_total_exp);
1240 put_log(Fnd_Message.Get);
1241 Fnd_Message.Clear;
1242
1243 put_log(' ');
1244
1245 Fnd_Message.Set_Name('XTR','XTR_TOT_SUCCESS_DEALS');
1246 Fnd_Message.Set_Token('VALUE',p_success_exp);
1247 put_log(Fnd_Message.Get);
1248 Fnd_Message.Clear;
1249
1250 Fnd_Message.Set_Name('XTR','XTR_EXP_CTRL_TOTAL_EST');
1251 Fnd_Message.Set_Token('VALUE',p_tot_suc_est_amt_exp);
1252 put_log(Fnd_Message.Get);
1253 Fnd_Message.Clear;
1254
1255 Fnd_Message.Set_Name('XTR','XTR_EXP_CTRL_TOTAL_ACT');
1256 Fnd_Message.Set_Token('VALUE',p_tot_suc_act_amt_exp);
1257 put_log(Fnd_Message.Get);
1258 Fnd_Message.Clear;
1259
1260 put_log(' ');
1261
1262 Fnd_Message.Set_Name('XTR','XTR_TOT_UNSUCCESS_DEALS');
1263 Fnd_Message.Set_Token('VALUE',p_failure_exp);
1264 put_log(Fnd_Message.Get);
1265 Fnd_Message.Clear;
1266
1267 Fnd_Message.Set_Name('XTR','XTR_EXP_CTRL_TOTAL_EST');
1268 Fnd_Message.Set_Token('VALUE',p_tot_fai_est_amt_exp);
1269 put_log(Fnd_Message.Get);
1270 Fnd_Message.Clear;
1271
1272 Fnd_Message.Set_Name('XTR','XTR_EXP_CTRL_TOTAL_ACT');
1273 Fnd_Message.Set_Token('VALUE',p_tot_fai_act_amt_exp);
1274 put_log(Fnd_Message.Get);
1275 Fnd_Message.Clear;
1276
1277 END IF;
1278
1279 /* ------------------------------------------------
1280 Sample log file for NI
1281 ------------------------------------------------
1282 Intergroup Transfers
1283 --------------------
1284 Total number of deals: 50
1285
1286 Total number of successful transfers: 48
1287 --WDK: what could we display?
1288 --Control total principal adjustment amount is 123456.78
1289
1290 Total number of unsuccessful transfers: 2
1291 --WDK: is there a simple answer?
1292 --Control total principal adjustment amount is 1001.01
1293
1294 */ ------------------------------------------------
1295 IF p_total_ni <> 0 then
1296
1297 Open Get_deal_name('NI');
1298 Fetch Get_deal_name Into L_Deal_Name;
1299 Close Get_deal_name;
1300
1301 put_log(' ');
1302
1303 put_log(L_Deal_Name);
1304 put_log(rpad('-',length(L_Deal_Name),'-'));
1305
1306 Fnd_Message.Set_Name('XTR','XTR_TOT_DEALS');
1307 Fnd_Message.Set_Token('VALUE',p_total_ni);
1308 put_log(Fnd_Message.Get);
1309 Fnd_Message.Clear;
1310
1311 put_log(' ');
1312
1313 Fnd_Message.Set_Name('XTR','XTR_TOT_SUCCESS_DEALS');
1314 Fnd_Message.Set_Token('VALUE',p_success_ni);
1315 put_log(Fnd_Message.Get);
1316 Fnd_Message.Clear;
1317
1318 /* WDK: TBD
1319 Fnd_Message.Set_Name('XTR','XTR_NI_CTRL_TOTAL');
1320 Fnd_Message.Set_Token('VALUE',p_tot_suc_amt_ni
1321 put_log(Fnd_Message.Get);
1322 Fnd_Message.Clear;
1323 */
1324
1325 put_log(' ');
1326
1327 Fnd_Message.Set_Name('XTR','XTR_TOT_UNSUCCESS_DEALS');
1328 Fnd_Message.Set_Token('VALUE',p_failure_ni);
1329 put_log(Fnd_Message.Get);
1330 Fnd_Message.Clear;
1331
1332 /* WDK: TBD
1333 Fnd_Message.Set_Name('XTR','XTR_IG_CTRL_TOTAL');
1334 Fnd_Message.Set_Token('VALUE',p_tot_fai_prin_amt_ig);
1335 put_log(Fnd_Message.Get);
1336 Fnd_Message.Clear;
1337 */
1338
1339
1340 END IF;
1341
1342 IF (P_Source = 'FORM') Then
1343 Close Int_Form_Deal_Cursor;
1344 ELSE
1345 Close Int_Con_Deal_Cursor;
1346 END IF;
1347
1348 IF (G_has_warnings) then
1349 retcode:=1; --completed with warnings
1350 END IF;
1351
1352 xtr_risk_debug_pkg.stop_conc_debug;
1353 end TRANSFER_DEALS;
1354
1355 END Xtr_Import_Deal_Data;
[Home] [Help]