[Home] [Help]
PACKAGE BODY: APPS.OKC_EURO_CONV_PUB
Source
1 package body OKC_EURO_CONV_PUB as
2 /* $Header: OKCPEURB.pls 120.1.12010000.2 2008/11/12 15:42:27 vgujarat ship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 --
7 ----------------------------------------------------------------------------
8 --
9 g_api_version constant number :=1;
10 g_init_msg_list varchar2(1) := OKC_API.G_FALSE;
11 g_msg_count NUMBER;
12 g_msg_data varchar2(2000);
13 g_conversion_type VARCHAR2(200) ;
14 g_conversion_rate_date DATE;
15 ---
16 --Set header rec:
17 --This procedure populates the table of record with the contract
18 --hdr infomation to be updated
19 ----
20 FUNCTION set_header_rec(p_chr_rec OKC_K_HEADERS_B%ROWTYPE, x_return_status OUT NOCOPY VARCHAR2 )
21 RETURN OKC_CONTRACT_PUB.chrv_tbl_type IS
22 x_chrv_rec OKC_CONTRACT_PUB.chrv_rec_type;
23 x_chrv_tbl OKC_CONTRACT_PUB.chrv_tbl_type;
24 l_rate number;
25 l_euro_rate number;
26 l_proc VARCHAR2(30) := 'set header rec';
27 l_return_status varchar2(1) :='S';
28 BEGIN
29
30 IF (l_debug = 'Y') THEN
31 okc_debug.Set_Indentation('OKC_EURO_CONV_PUB');
32 okc_debug.log('200: Entered set header rec', 2);
33 END IF;
34
35 x_chrv_rec.id := p_chr_rec.id;
36 x_chrv_rec.contract_number := p_chr_rec.contract_number;
37 x_chrv_rec.authoring_org_id := p_chr_rec.authoring_org_id;
38 x_chrv_rec.contract_number_modifier := p_chr_rec.contract_number_modifier;
39 x_chrv_rec.chr_id_response := p_chr_rec.chr_id_response;
40 x_chrv_rec.chr_id_award := p_chr_rec.chr_id_award;
41 x_chrv_rec.INV_ORGANIZATION_ID := p_chr_rec.INV_ORGANIZATION_ID;
42 x_chrv_rec.sts_code := p_chr_rec.sts_code;
43 x_chrv_rec.qcl_id := p_chr_rec.qcl_id;
44 x_chrv_rec.scs_code := p_chr_rec.scs_code;
45 x_chrv_rec.trn_code := p_chr_rec.trn_code;
46 x_chrv_rec.currency_code := p_chr_rec.currency_code;
47 x_chrv_rec.archived_yn := p_chr_rec.archived_yn;
48 x_chrv_rec.deleted_yn := p_chr_rec.deleted_yn;
49 x_chrv_rec.template_yn := p_chr_rec.template_yn;
50 x_chrv_rec.chr_type := p_chr_rec.chr_type;
51 x_chrv_rec.object_version_number := p_chr_rec.object_version_number;
52 x_chrv_rec.created_by := p_chr_rec.created_by;
53 x_chrv_rec.creation_date := p_chr_rec.creation_date;
54 x_chrv_rec.last_updated_by := p_chr_rec.last_updated_by;
55 x_chrv_rec.last_update_date := p_chr_rec.last_update_date;
56 x_chrv_rec.cust_po_number_req_yn := p_chr_rec.cust_po_number_req_yn;
57 x_chrv_rec.pre_pay_req_yn := p_chr_rec.pre_pay_req_yn;
58 x_chrv_rec.cust_po_number := p_chr_rec.cust_po_number;
59 x_chrv_rec.dpas_rating := p_chr_rec.dpas_rating;
60 x_chrv_rec.template_used := p_chr_rec.template_used;
61 x_chrv_rec.date_approved := p_chr_rec.date_approved;
62 x_chrv_rec.datetime_cancelled := p_chr_rec.datetime_cancelled;
63 x_chrv_rec.auto_renew_days := p_chr_rec.auto_renew_days;
64 x_chrv_rec.date_issued := p_chr_rec.date_issued;
65 x_chrv_rec.datetime_responded := p_chr_rec.datetime_responded;
66 x_chrv_rec.rfp_type := p_chr_rec.rfp_type;
67 x_chrv_rec.keep_on_mail_list := p_chr_rec.keep_on_mail_list;
68 x_chrv_rec.set_aside_percent := p_chr_rec.set_aside_percent;
69 x_chrv_rec.response_copies_req := p_chr_rec.response_copies_req;
70 x_chrv_rec.date_close_projected := p_chr_rec.date_close_projected;
71 x_chrv_rec.datetime_proposed := p_chr_rec.datetime_proposed;
72 x_chrv_rec.date_signed := p_chr_rec.date_signed;
73 x_chrv_rec.date_terminated := p_chr_rec.date_terminated;
74 x_chrv_rec.date_renewed := p_chr_rec.date_renewed;
75 x_chrv_rec.start_date := p_chr_rec.start_date;
76 x_chrv_rec.end_date := p_chr_rec.end_date;
77 x_chrv_rec.buy_or_sell := p_chr_rec.buy_or_sell;
78 x_chrv_rec.issue_or_receive := p_chr_rec.issue_or_receive;
79 x_chrv_rec.estimated_amount := p_chr_rec.estimated_amount;
80 x_chrv_rec.estimated_amount_renewed := p_chr_rec.estimated_amount_renewed;
81 x_chrv_rec.currency_code_renewed := p_chr_rec.currency_code_renewed;
82 x_chrv_rec.last_update_login := p_chr_rec.last_update_login;
83 x_chrv_rec.upg_orig_system_ref := p_chr_rec.upg_orig_system_ref;
84 x_chrv_rec.upg_orig_system_ref_id := p_chr_rec.upg_orig_system_ref_id;
85 x_chrv_rec.application_id := p_chr_rec.application_id;
86 x_chrv_rec.orig_system_source_code := p_chr_rec.orig_system_source_code;
87 x_chrv_rec.orig_system_id1 := p_chr_rec.orig_system_id1;
88 x_chrv_rec.orig_system_reference1 := p_chr_rec.orig_system_reference1 ;
89 x_chrv_rec.program_id := p_chr_rec.program_id;
90 x_chrv_rec.request_id := p_chr_rec.request_id;
91 x_chrv_rec.program_update_date := p_chr_rec.program_update_date;
92 x_chrv_rec.program_application_id := p_chr_rec.program_application_id;
93 x_chrv_rec.price_list_id := p_chr_rec.price_list_id;
94 x_chrv_rec.pricing_date := p_chr_rec.pricing_date;
95 x_chrv_rec.sign_by_date := p_chr_rec.sign_by_date;
96 x_chrv_rec.total_line_list_price := p_chr_rec.total_line_list_price;
97 x_chrv_rec.USER_ESTIMATED_AMOUNT := p_chr_rec.USER_ESTIMATED_AMOUNT;
98 x_chrv_rec.governing_contract_yn := p_chr_rec.governing_contract_yn;
99 x_chrv_rec.attribute_category := p_chr_rec.attribute_category;
100 x_chrv_rec.attribute1 := p_chr_rec.attribute1;
101 x_chrv_rec.attribute2 := p_chr_rec.attribute2;
102 x_chrv_rec.attribute3 := p_chr_rec.attribute3;
103 x_chrv_rec.attribute4 := p_chr_rec.attribute4;
104 x_chrv_rec.attribute5 := p_chr_rec.attribute5;
105 x_chrv_rec.attribute6 := p_chr_rec.attribute6;
106 x_chrv_rec.attribute7 := p_chr_rec.attribute7;
107 x_chrv_rec.attribute8 := p_chr_rec.attribute8;
108 x_chrv_rec.attribute9 := p_chr_rec.attribute9;
109 x_chrv_rec.attribute10 := p_chr_rec.attribute10;
110 x_chrv_rec.attribute11 := p_chr_rec.attribute11;
111 x_chrv_rec.attribute12 := p_chr_rec.attribute12;
112 x_chrv_rec.attribute13 := p_chr_rec.attribute13;
113 x_chrv_rec.attribute14 := p_chr_rec.attribute14;
114 x_chrv_rec.attribute15 := p_chr_rec.attribute15;
115 --new columns to replace rules
116 x_chrv_rec.conversion_type := p_chr_rec.conversion_type;
117 x_chrv_rec.conversion_rate := p_chr_rec.conversion_rate;
118 x_chrv_rec.conversion_rate_date := p_chr_rec.conversion_rate_date;
119 x_chrv_rec.conversion_euro_rate := p_chr_rec.conversion_euro_rate;
120 x_chrv_rec.cust_acct_id := p_chr_rec.cust_acct_id;
121 x_chrv_rec.bill_to_site_use_id := p_chr_rec.bill_to_site_use_id;
122 x_chrv_rec.inv_rule_id := p_chr_rec.inv_rule_id;
123 x_chrv_rec.renewal_type_code := p_chr_rec.renewal_type_code;
124 x_chrv_rec.renewal_notify_to := p_chr_rec.renewal_notify_to;
125 x_chrv_rec.renewal_end_date := p_chr_rec.renewal_end_date;
126 x_chrv_rec.ship_to_site_use_id := p_chr_rec.ship_to_site_use_id;
127 x_chrv_rec.payment_term_id := p_chr_rec.payment_term_id;
128
129 x_chrv_tbl(1) := x_chrv_rec;
130
131
132 x_chrv_tbl(1).conversion_type:=g_conversion_type;
133 x_chrv_tbl(1).conversion_rate_date := g_conversion_rate_date;
134
135 --get rates
136 okc_currency_api.get_rate(
137 p_FROM_CURRENCY => x_chrv_tbl(1).currency_code
138 ,p_TO_CURRENCY => okc_currency_api.get_ou_currency(x_chrv_tbl(1).authoring_org_id)
139 ,p_CONVERSION_DATE => g_conversion_rate_date
140 ,p_CONVERSION_TYPE => x_chrv_tbl(1).conversion_type
141 ,x_CONVERSION_RATE => l_rate
142 ,x_EURO_RATE => l_euro_rate
143 ,x_return_status => l_return_status);
144
145 x_return_status := l_return_status;
146
147 IF (l_debug = 'Y') THEN
148 okc_debug.log('okc_currency_api.get_rate is ' || l_return_status);
149 END IF;
150 IF l_return_status <> 'S' THEN
151 RETURN x_chrv_tbl;
152 END IF;
153
154 x_chrv_tbl(1).conversion_rate := l_rate;
155
156 IF l_euro_rate IS NOT NULL THEN
157 x_chrv_tbl(1).conversion_euro_rate := l_rate;
158 x_chrv_tbl(1).conversion_rate := l_euro_rate;
159 END IF;
160
161
162 return x_chrv_tbl;
163 EXCEPTION
164 WHEN others THEN
165
166 IF (l_debug = 'Y') THEN
167 okc_debug.log('400: Exiting set_header_rec:others Exception', 2);
168 okc_debug.Reset_Indentation;
169 END IF;
170 x_return_status:='U';
171
172 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
173 fnd_message.set_token('ROUTINE',l_proc);
174 fnd_message.set_token('REASON',SQLERRM);
175 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
176 End ;
177
178
179 ----This procecure is added for rules migration, conversion data now resides in okc_k_headers_b
180 --Add conversion data
181 --This procedure updates the header record if no/incomplete conversion data
182 ----
183
184 Procedure Add_conversion_data(p_contracts_rec IN okc_k_headers_b%rowtype,
185 p_update_mode IN VARCHAR2 ,
186 x_return_status OUT NOCOPY VARCHAR2 ) is
187
188 l_proc VARCHAR2(30) := 'Add_Conversion_data';
189 l_return_status VARCHAR2(1) := 'S';
190 y_return_status VARCHAR2(1) := 'S';
191 p_update_all VARCHAR2(1) ;
192 l_chrv_tbl OKC_CONTRACT_PUB.chrv_tbl_type;
193 x_chrv_tbl OKC_CONTRACT_PUB.chrv_tbl_type;
194
195 BEGIN
196 IF (l_debug = 'Y') THEN
197 okc_debug.Set_Indentation('OKC_EURO_CONV_PUB');
198 okc_debug.log('600: Entered Add_conversion_data', 2);
199 END IF;
200
201
202 If
203 (p_contracts_rec.conversion_type is null AND
204 p_contracts_rec.conversion_rate is null AND
205 p_contracts_rec.conversion_rate_date is null) THEN
206 p_update_all := 'Y' ;
207 IF (l_debug = 'Y') THEN
208 okc_debug.log('625: No conversion data found',2);
209 END IF;
210
211 Elsif
212 (p_contracts_rec.conversion_type is null OR
213 p_contracts_rec.conversion_rate is null OR
214 p_contracts_rec.conversion_rate_date is null) THEN
215 p_update_all := 'N' ;
216 IF (l_debug = 'Y') THEN
217 okc_debug.log('650: Conversion data found, VALUES:'||
218 p_contracts_rec.conversion_rate || '/' ||
219 p_contracts_rec.conversion_rate_date || '/' ||
220 p_contracts_rec.conversion_type,2);
221 END IF;
222 Else
223 p_update_all:= 'X';
224 x_return_status:= 'N';
225 End If;
226
227
228 -- If update mode is yes update the record if all or any of the
229 -- conversuion data apart from euro rate is missing
230
231 If p_update_mode = 'Y' and p_update_all in ('Y','N') then
232
233 --get values from DB into record
234 l_chrv_tbl := set_header_rec(p_contracts_rec, y_return_status);
235
236 If y_return_status = 'S' Then
237
238 IF (l_debug = 'Y') THEN
239 okc_debug.log('640: Calling update_contract_header');
240 END IF;
241
242 OKC_CONTRACT_PUB.update_contract_header(
243 p_api_version => g_api_version,
244 p_init_msg_list => g_init_msg_list,
245 x_return_status => l_return_status,
246 x_msg_count => g_msg_count,
247 x_msg_data => g_msg_data,
248 p_chrv_tbl => l_chrv_tbl,
249 x_chrv_tbl => x_chrv_tbl);
250
251 x_return_status := l_return_status;
252 IF (l_debug = 'Y') THEN
253 okc_debug.log('635: update_header returns ' || l_return_status);
254 END IF;
255
256 Else
257 If l_debug = 'Y' Then
258 okc_debug.log('636:set header returns error');
259 End If;
260 End If;
261 Else
262 IF p_update_all = 'Y' THEN
263 x_return_status := 'A';
264 Elsif p_update_all ='N' Then
265 x_return_status := 'U';
266 END IF;
267 End If;
268
269 IF (l_debug = 'Y') THEN
270 okc_debug.log('700: Exiting Add_Conversion_data', 2);
271 okc_debug.Reset_Indentation;
272 END IF;
273
274
275 EXCEPTION
276
277 WHEN others THEN
278
279 IF (l_debug = 'Y') THEN
280 okc_debug.log('800: Exiting Add_Conversion_data:others Exception', 2);
281 okc_debug.Reset_Indentation;
282 END IF;
283
284 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
285 fnd_message.set_token('ROUTINE',l_proc);
286 fnd_message.set_token('REASON',SQLERRM);
287 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
288
289 END;
290
291
292 /*----------------------------------------------------------------------------
293 Procudure Convert_Contracts
294 *----------------------------------------------------------------------------*/
295 PROCEDURE CONVERT_CONTRACTS (
296 errbuf OUT NOCOPY VARCHAR2,
297 retcode OUT NOCOPY VARCHAR2,
298 p_org_id NUMBER,
299 p_conversion_type VARCHAR2,
300 p_conversion_date VARCHAR2,
301 p_update_yn VARCHAR2 ) IS
302
303
304 Cursor contracts_csr is
305 Select CHRB.*
306 from okc_k_headers_b CHRB, okc_subclasses_b SCS
307 where CHRB.template_yn='N'
308 and (CHRB.sts_code in (select code from okc_statuses_b
309 where ste_code in ('ACTIVE','SIGNED','HOLD'))
310 OR
311 CHRB.sts_code in (select code from okc_statuses_b
312 where ste_code='EXPIRED'
313 and date_renewed is null))
314
315 and CHRB.scs_code = SCS.code
316 and SCS.cls_code = 'SERVICE'
317 and authoring_org_id = p_org_id;
318
319 l_funct_currency VARCHAR2(3);
320 l_proc VARCHAR2(30) := 'Convert_Contracts';
321 l_upgrade_status_hdr VARCHAR2(30) ;
322 l_upgrade_status VARCHAR2(80) ;
323 l_report_title VARCHAR2(80) ;
324 NO_CONVERSION_RQD Exception;
325 p_update_mode_yn VARCHAR2(1);
326 l_return_status VARCHAR2(1);
327 l_conversion_date DATE;
328 l_record_processed NUMBER := 0;
329 k_counter NUMBER := 0;
330 k1_counter NUMBER := 0;
331 k_success_counter NUMBER := 0;
332 k_failure_counter NUMBER := 0;
333
334 BEGIN
335
336 p_update_mode_yn := nvl(p_update_yn,'N');
337 IF NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') = 'Y' THEN
338 okc_util.init_trace;
339 fnd_file.put_line(FND_FILE.LOG,'Trace Mode is Enabled');
340 END IF;
341
342 IF (l_debug = 'Y') THEN
343 okc_debug.Set_Indentation('OKC_EURO_CONV_PUB');
344 okc_debug.log('900: Entered CONVERT_CONTRACTS', 2);
345 END IF;
346
347 fnd_file.put_line(FND_FILE.LOG,' ---------------------------------------------------------- ');
348 fnd_file.put_line(FND_FILE.LOG,' Starting Concurrent Program ... ');
349 fnd_file.put_line(FND_FILE.LOG,' ---------------------------------------------------------- ');
350 fnd_file.put_line(FND_FILE.LOG,' ');
351 fnd_file.put_line(FND_FILE.LOG,' ');
352 -- log session details
353 fnd_file.put_line(FND_FILE.LOG,' ********** DATABASE TRACE INFORMATION*************** ');
354 fnd_file.put_line(FND_FILE.LOG,'User id : '||fnd_global.user_id);
355 fnd_file.put_line(FND_FILE.LOG,'Conc Req id : '||fnd_global.conc_request_id);
356 fnd_file.put_line(FND_FILE.LOG,' **************************************************** ');
357 fnd_file.put_line(FND_FILE.LOG,' ');
358 fnd_file.put_line(FND_FILE.LOG,' ');
359
360 -- log messages with parameters
361 fnd_file.put_line(FND_FILE.LOG,' ********* Program Parameters **************** ');
362 fnd_file.put_line(FND_FILE.LOG,' Update Mode :'||p_update_mode_yn);
363 fnd_file.put_line(FND_FILE.LOG,' Conversion Date :'||p_conversion_date);
364 fnd_file.put_line(FND_FILE.LOG,' Conversion Type :'||p_conversion_type);
365 fnd_file.put_line(FND_FILE.LOG,' Organization Id :'||p_org_id);
366 fnd_file.put_line(FND_FILE.LOG,' **************************************************** ');
367 fnd_file.put_line(FND_FILE.LOG,' ');
368
369 fnd_file.put_line(FND_FILE.OUTPUT,' ********* Program Parameters **************** ');
370 fnd_file.put_line(FND_FILE.OUTPUT,' Update Mode :'||p_update_mode_yn);
371 fnd_file.put_line(FND_FILE.OUTPUT,' Conversion Date :'||p_conversion_date);
372 fnd_file.put_line(FND_FILE.OUTPUT,' Conversion Type :'||p_conversion_type);
373 fnd_file.put_line(FND_FILE.OUTPUT,' Organization Id :'||p_org_id);
374 fnd_file.put_line(FND_FILE.OUTPUT,' ************************************************ ');
375
376 IF (l_debug = 'Y') THEN
377 okc_debug.Log('20: p_org_id : '||p_org_id,2);
378 okc_debug.Log('20: p_conversion_date : '||p_conversion_date,2);
379 okc_debug.Log('20: p_conversion_type : '||p_conversion_type,2);
380 END IF;
381
382 IF (l_debug = 'Y') THEN
383 okc_debug.Log('25: User ID : '||fnd_global.user_id,2);
384 okc_debug.Log('25: Conc Req ID : '||fnd_global.conc_request_id,2);
385 END IF;
386
387 g_conversion_type := p_conversion_type;
388 g_conversion_rate_date := fnd_date.canonical_to_date(p_conversion_date);
389
390 Select currency_code
391 into l_funct_currency
392 from okx_set_of_books_v
393 where set_of_books_id=(select set_of_books_id
394 from hr_operating_units
395 where organization_id = p_org_id);
396
397 If l_funct_currency <> 'EUR' then
398 RAISE NO_CONVERSION_RQD;
399 End If;
400
401 -- set okc org context for this organization
402 okc_context.SET_OKC_ORG_CONTEXT(P_ORG_ID => p_org_id);
403
404 If p_update_mode_yn = 'Y' then
405
406 l_report_title:='CONTRACTS PROCESSED FOR EURO CONVERSION';
407 l_upgrade_status_hdr := 'Upgrade Status';
408 Else
409 l_report_title:='CONTRACTS TO BE PROCESSED FOR EURO CONVERSION';
410 l_upgrade_status_hdr:='Upgrade changes Needed';
411 End If;
412 fnd_file.put_line(FND_FILE.OUTPUT,' ');
413 fnd_file.put_line(FND_FILE.OUTPUT,' ');
414 fnd_file.put_line(FND_FILE.OUTPUT,lpad(l_report_title,90));
415 fnd_file.put_line(FND_FILE.OUTPUT,' ');
416 fnd_file.put_line(FND_FILE.OUTPUT,' ');
417 fnd_file.put_line(FND_FILE.OUTPUT,
418 '-----------------------------------------------------------------------------------------------------------------------------');
419 fnd_file.put_line(FND_FILE.OUTPUT,
420 rpad('Contract Number',30)||rpad('Modifier',20)||rpad('Currency Code',15)||rpad('Status',10)||
421 rpad('Start_date',15)||rpad('End date',15)||rpad(l_upgrade_status_hdr,30));
422
423 fnd_file.put_line(FND_FILE.OUTPUT,
424 '=============================================================================================================================');
425 FOR contracts_rec IN contracts_csr LOOP
426 --- Check if contract eligible for conversion
427 --dbms_output.put_line(contracts_rec.contract_number);
428
429 If OKC_CURRENCY_API.IS_EURO_CONVERSION_NEEDED(contracts_rec.currency_code)= 'Y' then
430
431 -- increment counter
432 k_counter := k_counter + 1;
433 k1_counter:= k1_counter +1;
434 IF (l_debug = 'Y') THEN
435 okc_debug.log('1000: Processing '||
436 (contracts_rec.contract_number || '(' ||
437 contracts_rec.contract_number_modifier || ')' ||
438 ' Currency: ' || contracts_rec.currency_code ||
439 ' Status: ' || contracts_rec.sts_code), 2);
440 END IF;
441
442 /* fnd_file.put_line(FND_FILE.OUTPUT,contracts_rec.contract_number,20) ||
443 ', '|| contracts_rec.contract_number_modifier ||
444 ', '|| contracts_rec.currency_code ||
445 ', '|| contracts_rec.sts_code);*/
446
447 add_conversion_data(p_contracts_rec =>contracts_rec,
448 p_update_mode =>p_update_mode_yn,
449 x_return_status =>l_return_status);
450
451 If p_update_mode_YN = 'Y' Then
452 If l_return_status='S' Then
453 k_success_counter := k_success_counter+1;
454 IF (l_debug = 'Y') THEN
455 okc_debug.log('1200: Record Updated ', 2);
456 END IF;
457 Elsif l_return_status = 'N' Then
458 k_success_counter := k_success_counter+1;
459 IF (l_debug = 'Y') THEN
460 okc_debug.log('1100: Upgrade not required ', 2);
461 END IF;
462 Else
463 fnd_message.set_name('OKC','OKC_K_CONV_ERROR');
464 fnd_message.set_token('CONTRACT_NUMBER',contracts_rec.contract_number);
465 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
466 IF (l_debug = 'Y') THEN
467 okc_debug.log('1100: Record not Updated ', 2);
468 END IF;
469 k_failure_counter := k_failure_counter+1;
470 end If;
471 --dbms_output.put_line(contracts_rec.contract_number);
472
473 If (k_counter >= 1000) Then
474 commit;
475 k_counter := 0;
476 End If;
477 End If;
478
479 --replaced by stmt below for rules migration
480 /* select decode(p_update_mode_yn,'Y',decode(l_return_status,'S','Successful','N',
481 'No change ','Failed'),decode(l_return_status,'U','Incomplete CVN Rule',
482 'A','No CVN rule','N','No change required'))
483 into l_upgrade_status
484 from dual;*/
485
486 select decode(p_update_mode_yn,'Y',decode(l_return_status,'S','Successful','N',
487 'No change ','Failed'),decode(l_return_status,'U','Incomplete Conversion data',
488 'A','No Conversion data ','N','No change required'))
489 into l_upgrade_status
490 from dual;
491
492 fnd_file.put_line(FND_FILE.OUTPUT,rpad(contracts_rec.contract_number,30)||
493 substr(rpad(nvl(contracts_rec.contract_number_modifier,'X'),21),2)||
494 rpad(contracts_rec.currency_code,15)||
495 rpad(contracts_rec.sts_code,10)||
496 rpad(contracts_rec.start_date,15)||
497 substr(rpad(nvl(to_char(contracts_rec.end_date),'X'),15),2)||
498 rpad(l_upgrade_status,30));
499 End If;
500
501 END LOOP;
502
503
504 fnd_file.put_line(FND_FILE.OUTPUT,
505 '----------------------------------------------------------------------------------------------------------------------------');
506 fnd_file.put_line(FND_FILE.OUTPUT,'Total Contracts processed : '||k1_counter);
507 fnd_file.put_line(FND_FILE.LOG,'Total Contracts processed : '||k1_counter);
508
509 IF (l_debug = 'Y') THEN
510 okc_debug.log('1115: After LOOP' || 'Contracts proccessed' ,2);
511 END IF;
512
513 If p_update_mode_yn = 'Y' then
514 fnd_file.put_line(FND_FILE.OUTPUT,'Number of Contracts - Success: '||k_success_counter);
515 fnd_file.put_line(FND_FILE.OUTPUT,'Number of Contracts - Failure: '||k_failure_counter);
516 commit;
517 End If;
518
519 IF (l_debug = 'Y') THEN
520 okc_debug.log('1100: ' || k1_counter || 'Contracts Processed' ,2);
521 END IF;
522
523 fnd_file.put_line(FND_FILE.LOG,' ---------------------------------------------------------- ');
524 fnd_file.put_line(FND_FILE.LOG,'Completed Concurrent Program. ');
525 fnd_file.put_line(FND_FILE.LOG,' ---------------------------------------------------------- ');
526
527 IF NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') = 'Y' THEN
528 okc_util.stop_trace;
529 END IF;
530
531 IF (l_debug = 'Y') THEN
532 okc_debug.log('1100: Exiting CONVERT_CONTRACTS', 2);
533 okc_debug.Reset_Indentation;
534 END IF;
535
536
537 EXCEPTION
538
539 WHEN NO_CONVERSION_RQD then
540
541 IF (l_debug = 'Y') THEN
542 okc_debug.log('1200: Exiting CONVERT_CONTRACTS:NO_CONVERSION_RQD Exception', 2);
543 okc_debug.Reset_Indentation;
544 END IF;
545
546 fnd_message.set_name('OKC','OKC_CONV_NOT_REQD');
547 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
548
549 WHEN others THEN
550
551 IF (l_debug = 'Y') THEN
552 okc_debug.log('1200: Exiting CONVERT_CONTRACTS:others Exception', 2);
553 okc_debug.Reset_Indentation;
554 END IF;
555
556 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
557 fnd_message.set_token('ROUTINE',l_proc);
558 fnd_message.set_token('REASON',SQLERRM);
559 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
560 raise;
561
562
563 END CONVERT_CONTRACTS;
564
565 end OKC_EURO_CONV_PUB;