[Home] [Help]
PACKAGE BODY: APPS.PA_CI_SUPPLIER_UTILS
Source
1 package body PA_CI_SUPPLIER_UTILS as
2 -- $Header: PASIUTLB.pls 120.1 2005/06/07 04:57:40 appldev $
3
4 PROCEDURE print_msg(p_msg varchar2) IS
5 BEGIN
6 --dbms_output.put_line('Log:'||p_msg);
7 --r_debug.r_msg('Log:'||p_msg);
8 PA_DEBUG.g_err_stage := p_msg;
9 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
10 NULL;
11
12 END print_msg;
13
14 /** This API checks whether the record exists or not in pa_ci_supplier_details **/
15 FUNCTION check_trx_exists(p_ci_transaction_id in NUMBER)
16 RETURN VARCHAR2 IS
17 l_return_status varchar2(1) := 'N';
18 BEGIN
19
20 IF p_ci_transaction_id is NOT NULL then
21
22 SELECT 'Y'
23 INTO l_return_status
24 FROM pa_ci_supplier_details
25 WHERE ci_transaction_id = p_ci_transaction_id;
26
27 ELSE
28 l_return_status := 'N';
29 END IF;
30
31 return l_return_status;
32
33 EXCEPTION
34 WHEN NO_DATA_FOUND THEN
35 return 'N';
36 WHEN OTHERS THEN
37 RAISE;
38
39 END check_trx_exists;
40
41 /** This api validates the supplier impact records and populates id for name **/
42 PROCEDURE validate_SI_record(
43 p_RECORD_STATUS IN VARCHAR2
44 ,p_CI_ID IN NUMBER
45 ,P_CALLING_MODE IN VARCHAR2
46 ,P_CI_STATUS IN VARCHAR2
47 ,P_ORG_ID IN VARCHAR2
48 ,x_VENDOR_ID IN OUT NOCOPY NUMBER
49 ,p_VENDOR_NAME IN VARCHAR2
50 ,x_PO_HEADER_ID IN OUT NOCOPY NUMBER
51 ,p_PO_NUMBER IN VARCHAR2
52 ,x_PO_LINE_ID IN OUT NOCOPY NUMBER
53 ,p_PO_LINE_NUM IN NUMBER
54 ,p_ADJUSTED_TRANSACTION_ID IN NUMBER
55 ,p_CURRENCY_CODE IN VARCHAR2
56 ,p_CHANGE_AMOUNT IN NUMBER
57 ,p_CHANGE_TYPE IN VARCHAR2
58 ,p_CHANGE_DESCRIPTION IN VARCHAR2
59 ,p_ci_transaction_id IN NUMBER
60 ,x_return_status OUT NOCOPY VARCHAR2
61 ,x_error_msg_code OUT NOCOPY VARCHAR2 ) IS
62
63 return_error EXCEPTION;
64 l_error_msg varchar2(1000) := NULL;
65 l_return_status VARCHAR2(10) := 'S';
66 l_vendor_id NUMBER;
67 l_po_header_id NUMBER;
68 l_po_line_id NUMBER;
69 l_change_type VARCHAR2(100);
70 l_currency_code VARCHAR2(100);
71 l_debug_mode varchar2(1) := 'N';
72
73
74 PROCEDURE validate_supplier(l_error_msg OUT NOCOPY varchar2) IS
75 cursor cur_ven is
76 SELECT vendor_id
77 FROM po_vendors
78 WHERE vendor_name = p_vendor_name;
79 BEGIN
80 If p_record_status in ('NEW','CHANGED') Then
81 If l_debug_mode = 'Y' Then
82 print_msg('inside validate_supplier api');
83 End If;
84 If p_vendor_name is NULL then
85 l_error_msg := 'PA_CISI_SUPPLIER_NULL';
86 Else
87 OPEN cur_ven;
88 FETCH cur_ven INTO l_vendor_id;
89 IF cur_ven%NOTFOUND then
90
91 l_error_msg := 'PA_CISI_SUPPLIER_INVALID';
92 END IF;
93 CLOSE cur_ven;
94 x_vendor_id := l_vendor_id;
95 End If;
96 End If;
97 EXCEPTION
98 WHEN OTHERS THEN
99 l_error_msg := sqlcode||sqlerrm;
100 Raise;
101
102 END validate_supplier;
103
104 PROCEDURE validate_PO(c_vendor_id number,l_error_msg OUT NOCOPY varchar2) IS
105 cursor cur_po is
106 SELECT po.po_header_id
107 FROM po_headers_all po
108 WHERE po.segment1 = p_po_number
109 AND po.vendor_id = c_vendor_id
110 /* added this condition to cehck Po status is OPEN or APPRVOED */
111 AND NVL(po.closed_code,'XX') NOT in ('FINALLY CLOSED','CLOSED')
112 AND (( po.org_id = p_org_id
113 AND p_org_id is NOT NULL )
114 OR p_org_id is NULL
115 );
116 BEGIN
117 If l_debug_mode = 'Y' Then
118 print_msg('insdie validate_PO api c_vendor_id['||c_vendor_id||']');
119 End If;
120 If p_change_type is NOT NULL and p_change_type = 'CREATE'
121 and p_po_number is NOT NULL then
122 l_error_msg := 'PA_CISI_INVALID_PO_CHANGE';
123 Elsif p_change_type = 'UPDATE' and p_po_number is NOT NULL and p_org_id is NOT NULL then
124 OPEN cur_po;
125 FETCH cur_po INTO l_po_header_id;
126 IF cur_po%NOTFOUND THEN
127 l_error_msg := 'PA_CISI_INVALID_PO';
128 Else
129 x_po_header_id := l_po_header_id;
130 End If;
131 CLOSE cur_po;
132 Elsif p_change_type = 'UPDATE' and p_po_number is NOT NULL and p_org_id is NULL then
133 OPEN cur_po;
134 FETCH cur_po INTO l_po_header_id;
135 IF cur_po%NOTFOUND THEN
136 l_error_msg := 'PA_CISI_INVALID_PO';
137 Else
138 if cur_po%Rowcount > 1 then
139 l_error_msg := 'PA_CISI_PO_EXISTS';
140 Else
141 x_po_header_id := l_po_header_id;
142 End If;
143 End If;
144 CLOSE cur_po;
145 End If;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 l_error_msg := sqlcode||sqlerrm;
150 Raise;
151
152 END validate_PO;
153
154 PROCEDURE validate_PO_line(c_po_header_id number,c_po_line_num number
155 ,l_error_msg OUT NOCOPY varchar2) IS
156 cursor cur_po_line is
157 SELECT pol.po_line_id
158 FROM po_lines_all pol
159 ,po_headers_all poh
160 WHERE pol.po_header_id = poh.po_header_id
161 AND poh.po_header_id = c_po_header_id
162 AND pol.line_num = c_po_line_num
163 /* added this condition to cehck Po status is OPEN or APPRVOED */
164 AND NVL(poh.closed_code,'XX') NOT in ('FINALLY CLOSED','CLOSED');
165
166 BEGIN
167 If l_debug_mode = 'Y' Then
168 print_msg('inside validate_PO_line api HeaderId['||c_po_header_id||
169 ']c_po_line_num['||c_po_line_num||']');
170 End If;
171 IF c_po_header_id is NOT NULL then
172 If c_po_line_num is NULL then
173 l_error_msg := 'PA_CISI_POLINE_NULL';
174 Else
175 OPEN cur_po_line;
176 FETCH cur_po_line INTO l_po_line_id;
177 IF cur_po_line%NOTFOUND then
178 l_error_msg := 'PA_CISI_INVALID_POLINE'; /*Bug fix : 2634057 */
179 Else
180 x_po_line_id := l_po_line_id;
181 End If;
182 CLOSE cur_po_line;
183 End if;
184 End If;
185 EXCEPTION
186 WHEN OTHERS THEN
187 l_error_msg := SQLCODE||SQLERRM;
188 raise;
189
190 END validate_PO_line;
191
192 PROCEDURE Validate_change_type(l_error_msg OUT NOCOPY varchar2) IS
193 BEGIN
194 If l_debug_mode = 'Y' Then
195 print_msg('inside Validate_change_type api');
196 End If;
197 If p_change_type is NULL then
198 l_error_msg := 'PA_CISI_CHANGE_TYPE_NULL';
199 /* bug fix :2690413 */
200 ElsIf p_po_number is null and p_PO_LINE_NUM is null AND p_change_type = 'UPDATE' then
201 l_error_msg := 'PA_CISI_INVALID_CHANGE_TYPE';
202 Elsif p_po_number is null and p_PO_LINE_NUM is NOT Null AND p_change_type = 'UPDATE' then
203 l_error_msg := 'PA_CISI_INVALID_PO';
204 Elsif p_po_number is NOT Null and p_PO_LINE_NUM is Null AND p_change_type = 'UPDATE' then
205 l_error_msg := 'PA_CISI_POLINE_NULL';
206 Elsif (p_po_number is NOT Null OR p_PO_LINE_NUM is NOT Null) AND p_change_type = 'CREATE' then
207 l_error_msg := 'PA_CISI_INVALID_CHANGE_TYPE';
208 /* End of bug fix:2690413 */
209 End if;
210
211 END Validate_change_type;
212
213 PROCEDURE Validate_Currency(c_po_header_id number,l_error_msg OUT NOCOPY varchar2) IS
214 cursor cur_po_currency IS
215 SELECT po.currency_code
216 FROM po_headers_all po
217 WHERE po.po_header_id = c_po_header_id
218 AND po.currency_code = p_currency_code;
219
220 cursor cur_currency is
221 SELECT currency_code
222 FROM fnd_currencies -- Modified for Bug 4403203.
223 WHERE enabled_flag = 'Y'
224 AND trunc(sysdate) between nvl(start_date_active,trunc(sysdate))
225 and nvl(end_date_active,trunc(sysdate))
226 AND currency_code = p_currency_code;
227 BEGIN
228 If l_debug_mode = 'Y' Then
229 print_msg('inside Validate_Currency api['||c_po_header_id||']');
230 End If;
231 If p_currency_code is NULL and p_vendor_name is NOT NULL then
232 l_error_msg := 'PA_CISI_CURRENCY_NULL';
233
234 ElsIf c_po_header_id is NOT NULL then
235 OPEN cur_po_currency;
236 FETCH cur_po_currency INTO l_currency_code;
237 IF cur_po_currency%NOTFOUND THEN
238 l_error_msg := 'PA_CISI_INVALID_CURRENCY';
239 End If;
240 CLOSE cur_po_currency;
241 Else
242 OPEN cur_currency;
243 FETCH cur_currency INTO l_currency_code;
244 IF cur_currency%NOTFOUND THEN
245 l_error_msg := 'PA_CISI_INVALID_CURRENCY';
246 End if;
247 CLOSE cur_currency;
248 End if;
249 EXCEPTION
250 WHEN OTHERS THEN
251 l_error_msg := sqlcode||sqlerrm;
252
253 END Validate_Currency;
254
255 PROCEDURE Validate_changeamt Is
256
257 BEGIN
258 If l_debug_mode = 'Y' Then
259 print_msg('inside Validate_changeamt');
260 End If;
261 /** this condition is commented out as -ve amt should be allowed for SI impact
262 if p_change_amount < 0 then
263 l_error_msg := 'PA_CISI_NEG_AMT';
264 Els **/
265 if p_change_amount is NULL then
266 l_error_msg := 'PA_CISI_CHANGEAMT_NULL';
267 End if;
268
269
270 END Validate_changeamt ;
271
272 BEGIN
273 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
274 l_debug_mode := NVL(l_debug_mode, 'N');
275
276 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
277
278 /** reset the return status and error msg code **/
279 x_return_status := 'S';
280 x_error_msg_code := NULL;
281 l_error_msg := null;
282
283
284 /** VALIDATE SUPPLIER NAME **/
285 validate_supplier(l_error_msg);
286 If l_error_msg is NOT NULL then
287 Raise return_error;
288 End If;
289
290 validate_change_type(l_error_msg);
291 If l_error_msg is NOT NULL then
292 Raise return_error;
293 End If;
294
295 validate_PO(x_vendor_id,l_error_msg);
296 If l_error_msg is NOT NULL then
297 Raise return_error;
298 End If;
299
300 validate_PO_line(x_po_header_id,p_po_line_num,l_error_msg);
301 If l_error_msg is NOT NULL then
302 Raise return_error;
303 End If;
304
305 validate_currency(x_po_header_id,l_error_msg);
306 If l_error_msg is NOT NULL then
307 Raise return_error;
308 End If;
309
310 Validate_changeamt;
311 If l_error_msg is NOT NULL then
312 Raise return_error;
313 End If;
314
315
316
317 EXCEPTION
318 WHEN return_error then
319 x_return_status := 'E';
320 x_error_msg_code := l_error_msg;
321 If l_debug_mode = 'Y' Then
322 print_msg('errmsg='||l_error_msg);
323 End If;
324 Return;
325 when others then
326 x_return_status := 'U';
327 x_error_msg_code := sqlcode||sqlerrm;
328 Raise;
329
330 END validate_SI_record;
331
332
333 /** This is called from Supplier Impact UI scrren this is a wrapper api which in turn
334 ** calls validate SI record for each single record
335 **/
336 PROCEDURE validate_insert_SI (
337 p_ROWID IN OUT NOCOPY PA_VC_1000_150
338 ,p_RECORD_STATUS IN PA_VC_1000_150
339 ,p_CI_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
340 ,p_CI_TYPE_ID IN PA_VC_1000_150
341 ,p_CI_IMPACT_ID IN PA_VC_1000_150
342 ,P_CALLING_MODE IN VARCHAR2
343 ,P_CI_STATUS IN PA_VC_1000_150
344 ,P_ORG_ID IN PA_VC_1000_150
345 ,x_VENDOR_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
346 ,p_VENDOR_NAME IN PA_VC_1000_150
347 ,x_PO_HEADER_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
348 ,p_PO_NUMBER IN PA_VC_1000_150
349 ,x_PO_LINE_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
350 ,p_PO_LINE_NUM IN PA_VC_1000_150 --PA_VC_1000_NUM
351 ,p_ADJUSTED_TRANSACTION_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
352 ,p_CURRENCY_CODE IN PA_VC_1000_150
353 ,p_CHANGE_AMOUNT IN PA_VC_1000_150 --PA_VC_1000_NUM
354 ,p_CHANGE_TYPE IN PA_VC_1000_150
355 ,p_CHANGE_DESCRIPTION IN PA_VC_1000_150
356 ,p_ci_transaction_id IN OUT NOCOPY PA_VC_1000_150
357 ,p_RECORD_ID IN OUT NOCOPY PA_VC_1000_150
358 ,p_REC_RETURN_STATUS IN OUT NOCOPY PA_VC_1000_150
359 ,x_return_status IN OUT NOCOPY VARCHAR2
360 ,x_msg_data IN OUT NOCOPY VARCHAR2
361 ,x_msg_count IN OUT NOCOPY NUMBER
362 ) IS
363
364 l_error_msg_code varchar2(100):= null;
365 l_rec_count number := 0;
366 l_counter number := 0;
367 l_msg_count number := 0;
368
369 l_vendor_id pa_plsql_datatypes.IdTabTyp;
370 l_po_header_id pa_plsql_datatypes.IdTabTyp;
371 l_po_line_id pa_plsql_datatypes.IdTabTyp;
372 l_return_status pa_plsql_datatypes.char50TabTyp;
373 l_rowid pa_plsql_datatypes.char150TabTyp;
374 l_ci_transaction_id pa_plsql_datatypes.IdTabTyp;
375
376 l_debug_mode varchar2(1) := 'N';
377
378
379
380 BEGIN
381 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
382 l_debug_mode := NVL(l_debug_mode, 'N');
383
384 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
385
386 -- initialize the error stack
387 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.validate_insert_SI');
388
389 /** clear the message stack **/
390 fnd_msg_pub.INITIALIZE;
391
392 l_rec_count := p_RECORD_STATUS.count();
393
394 /** Initialize plsql tables **/
395 l_vendor_id.delete;
396 l_po_header_id.delete;
397 l_po_line_id.delete;
398 l_return_status.delete;
399 l_rowid.delete;
400 l_ci_transaction_id.delete;
401
402
403 IF (p_calling_mode = 'VALIDATEANDINSERT') then
404
405 FOR i in 1 .. l_rec_count LOOP
406
407 /** print the inpput params **/
408 If l_debug_mode = 'Y' THEN
409 print_msg('p_RECORD_STATUS['||p_RECORD_STATUS(i)||']p_CI_ID['||p_CI_ID(i)||
410 ']P_CI_STATUS['||P_CI_STATUS(i)||']p_VENDOR_NAME['||p_VENDOR_NAME(i)||
411 ']p_PO_NUMBER['||p_PO_NUMBER(i)||']p_PO_LINE_NUM['||p_PO_LINE_NUM(i)||']p_CURRENCY_CODE['||
412 p_CURRENCY_CODE(i)||']p_CHANGE_AMOUNT['||p_CHANGE_AMOUNT(i)||']p_CHANGE_TYPE['||p_CHANGE_TYPE(i)||
413 ']p_CHANGE_DESCRIPTION['||p_CHANGE_DESCRIPTION(i)||']p_rowid['||p_rowid(i)||
414 ']p_ci_transaction_id['||p_ci_transaction_id(i)||']' );
415 End If;
416
417
418 l_error_msg_code := NULL;
419 l_vendor_id(i) := null;
420 l_po_header_id(i) := null;
421 l_po_line_id(i) := null;
425 p_REC_RETURN_STATUS(i) := 'S';
422 l_return_status(i) := 'S';
423 l_rowid(i) := p_rowid(i);
424 l_ci_transaction_id(i) := p_ci_transaction_id(i);
426 If l_debug_mode = 'Y' THEN
427 print_msg('calling validate_SI_record');
428 End If;
429
430 validate_SI_record(
431 p_RECORD_STATUS => p_RECORD_STATUS(i)
432 ,p_CI_ID => p_CI_ID(i)
433 ,P_CALLING_MODE => 'VALIDATE'
434 ,P_CI_STATUS => P_CI_STATUS(i)
435 ,P_ORG_ID => p_org_id(i) /* Bug fix fnd_profile.value('ORG_ID') */
436 ,x_VENDOR_ID => l_vendor_id(i)
437 ,p_VENDOR_NAME => p_VENDOR_NAME(i)
438 ,x_PO_HEADER_ID => l_po_header_id(i)
439 ,p_PO_NUMBER => p_PO_NUMBER(i)
440 ,x_PO_LINE_ID => l_po_line_id(i)
441 ,p_PO_LINE_NUM => p_PO_LINE_NUM(i)
442 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID(i)
443 ,p_CURRENCY_CODE => p_CURRENCY_CODE(i)
444 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT(i)
445 ,p_CHANGE_TYPE => p_CHANGE_TYPE(i)
446 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION(i)
447 ,p_ci_transaction_id => l_ci_transaction_id(i)
448 ,x_return_status => l_return_status(i)
449 ,x_error_msg_code => l_error_msg_code );
450
451 /** depending on return status and record status call the appropriate table handler api **/
452
453 If l_return_status(i) = 'S' and nvl(l_error_msg_code,'X') = 'X' then
454
455 If p_RECORD_STATUS(i) = 'NEW' then
456 If l_debug_mode = 'Y' THEN
457 print_msg('calling insert_row api');
458 End If;
459 PA_CI_SUPPLIER_PKG.insert_row (
460 x_rowid => l_rowid(i)
461 ,x_ci_transaction_id => l_ci_transaction_id(i)
462 ,p_CI_TYPE_ID => p_ci_type_id(i)
463 ,p_CI_ID => p_CI_ID(i)
464 ,p_CI_IMPACT_ID => p_ci_impact_id(i)
465 ,p_VENDOR_ID => l_vendor_id(i)
466 ,p_PO_HEADER_ID => l_po_header_id(i)
467 ,p_PO_LINE_ID => l_po_line_id(i)
468 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID(i)
469 ,p_CURRENCY_CODE => p_CURRENCY_CODE(i)
470 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT(i)
471 ,p_CHANGE_TYPE => p_CHANGE_TYPE(i)
472 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION(i)
473 ,p_CREATED_BY => FND_GLOBAL.login_id
474 ,p_CREATION_DATE => trunc(sysdate)
475 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
476 ,p_LAST_UPDATE_DATE => trunc(sysdate)
477 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
478 ,p_ci_status => P_CI_STATUS(i)
479 ,x_return_status => l_return_status(i)
480 ,x_error_msg_code => l_error_msg_code );
481
482 If l_return_status(i) = 'S' then
483 p_ci_transaction_id(i) := l_ci_transaction_id(i);
484 p_rowid(i) := l_rowid(i);
485 If l_debug_mode = 'Y' THEN
486 print_msg('Assigning citransactionid ='||p_ci_transaction_id(i));
487 End If;
488 End if;
489 If l_debug_mode = 'Y' THEN
490 print_msg('end of insert row api');
491 End If;
492
493
494 Elsif p_RECORD_STATUS(i) = 'CHANGED' then
495 If l_debug_mode = 'Y' THEN
496 print_msg('calling update row api');
497 End If;
498 PA_CI_SUPPLIER_PKG.update_row (
499 p_rowid => l_rowid(i)
500 ,p_ci_transaction_id => l_ci_transaction_id(i)
501 ,p_CI_TYPE_ID => p_ci_type_id(i)
502 ,p_CI_ID => p_CI_ID(i)
503 ,p_CI_IMPACT_ID => p_ci_impact_id(i)
504 ,p_VENDOR_ID => l_vendor_id(i)
505 ,p_PO_HEADER_ID => l_po_header_id(i)
506 ,p_PO_LINE_ID => l_po_line_id(i)
507 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID(i)
508 ,p_CURRENCY_CODE => p_CURRENCY_CODE(i)
509 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT(i)
510 ,p_CHANGE_TYPE => p_CHANGE_TYPE(i)
511 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION(i)
512 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
513 ,p_LAST_UPDATE_DATE => trunc(sysdate)
514 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
515 ,p_ci_status => P_CI_STATUS(i)
516 ,x_return_status => l_return_status(i)
517 ,x_error_msg_code => l_error_msg_code );
518 If l_debug_mode = 'Y' THEN
519 print_msg('end of update row api');
520 End If;
521
522 End if;
523
524 End if;
528 print_msg('adding error msg to stack'||l_error_msg_code);
525
526 If l_return_status(i) <> 'S' and nvl(l_error_msg_code,'X') <> 'X' then
527 If l_debug_mode = 'Y' THEN
529 End If;
530
531 p_REC_RETURN_STATUS(i) := 'E';
532 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
533 ,p_msg_name =>l_error_msg_code
534 );
535 l_msg_count := l_msg_count +1;
536
537 End if;
538
539
540 END LOOP;
541
542 If l_msg_count = 1 then
543 x_return_status := 'E';
544 x_msg_count := l_msg_count;
545 x_msg_data := l_error_msg_code;
546 Elsif l_msg_count > 1 then
547 x_return_status := 'E';
548 x_msg_count := l_msg_count;
549 x_msg_data := null;
550 End if;
551
552
553 END IF;
554
555 pa_debug.reset_err_stack;
556 EXCEPTION
557 when others then
558 x_return_status := 'U';
559 x_msg_count := 1;
560 x_msg_data := SQLCODE||SQLERRM;
561 pa_debug.reset_err_stack;
562 RAISE;
563
564 END validate_insert_SI;
565
566 PROCEDURE validateSI(p_ROWID IN OUT NOCOPY VARCHAR2
567 ,p_RECORD_STATUS IN VARCHAR2
568 ,p_CI_ID IN VARCHAR2
569 ,p_CI_TYPE_ID IN VARCHAR2
570 ,p_CI_IMPACT_ID IN VARCHAR2
571 ,P_CALLING_MODE IN VARCHAR2
572 ,P_ORG_ID IN VARCHAR2
573 ,p_VENDOR_NAME IN VARCHAR2
574 ,p_PO_NUMBER IN VARCHAR2
575 ,p_PO_LINE_NUM IN VARCHAR2
576 ,p_ADJUSTED_TRANSACTION_ID IN VARCHAR2
577 ,p_CURRENCY_CODE IN VARCHAR2
578 ,p_CHANGE_AMOUNT IN VARCHAR2
579 ,p_CHANGE_TYPE IN VARCHAR2
580 ,p_CHANGE_DESCRIPTION IN VARCHAR2
581 ,p_CI_TRANSACTION_ID IN OUT NOCOPY VARCHAR2
582 ,x_return_status IN OUT NOCOPY VARCHAR2
583 ,x_msg_data IN OUT NOCOPY VARCHAR2
584 ,x_msg_count IN OUT NOCOPY NUMBER
585 ) IS
586
587 l_CI_STATUS varchar2(10);
588 l_error_msg_code varchar2(1000);
589 l_vendor_id number ;
590 l_po_header_id number;
591 l_po_line_id number;
592 l_return_status varchar2(1) := 'S';
593 l_rowid varchar2(100) := p_rowid;
594 l_ci_transaction_id number := p_ci_transaction_id;
595 l_msg_count number := 0;
596 l_msg_index_out number := 0;
597 l_ci_impact_id number;
598 l_debug_mode varchar2(1) := 'N';
599
600 BEGIN
601
602 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
603 l_debug_mode := NVL(l_debug_mode, 'N');
604
605 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
606
607 -- initialize the error stack
608 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.validateSI');
609
610 /** clear the message stack **/
611 fnd_msg_pub.INITIALIZE;
612
613 IF (p_calling_mode = 'VALIDATEANDINSERT') then
614
615 /** print the inpput params **/
616 IF l_debug_mode = 'Y' THEN
617 print_msg('p_RECORD_STATUS['||p_RECORD_STATUS||']p_CI_ID['||p_CI_ID||
618 ']p_VENDOR_NAME['||p_VENDOR_NAME||
619 ']p_PO_NUMBER['||p_PO_NUMBER||']p_PO_LINE_NUM['||p_PO_LINE_NUM||']p_CURRENCY_CODE['||
620 p_CURRENCY_CODE||']p_CHANGE_AMOUNT['||p_CHANGE_AMOUNT||']p_CHANGE_TYPE['||p_CHANGE_TYPE||
621 ']p_CHANGE_DESCRIPTION['||p_CHANGE_DESCRIPTION||']p_rowid['||p_rowid||
622 ']p_ci_transaction_id['||p_ci_transaction_id||']p_org_id['||p_org_id||']' );
623 End If;
624
625
626 l_error_msg_code := NULL;
627 l_vendor_id := null;
628 l_po_header_id := null;
629 l_po_line_id := null;
630 l_return_status := 'S';
631 l_rowid := p_rowid;
632 l_ci_transaction_id := p_ci_transaction_id;
633 l_CI_STATUS := null;
634 IF l_debug_mode = 'Y' THEN
635 print_msg('calling validate_SI_record');
636 End If;
637
638 validate_SI_record(
639 p_RECORD_STATUS => p_RECORD_STATUS
640 ,p_CI_ID => p_CI_ID
641 ,P_CALLING_MODE => 'VALIDATE'
642 ,P_CI_STATUS => l_CI_STATUS
643 ,P_ORG_ID => p_org_id /*Bug fix fnd_profile.value('ORG_ID')*/
644 ,x_VENDOR_ID => l_vendor_id
645 ,p_VENDOR_NAME => p_VENDOR_NAME
646 ,x_PO_HEADER_ID => l_po_header_id
647 ,p_PO_NUMBER => p_PO_NUMBER
648 ,x_PO_LINE_ID => l_po_line_id
649 ,p_PO_LINE_NUM => p_PO_LINE_NUM
653 ,p_CHANGE_TYPE => p_CHANGE_TYPE
650 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
651 ,p_CURRENCY_CODE => p_CURRENCY_CODE
652 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
654 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
655 ,p_ci_transaction_id => l_ci_transaction_id
656 ,x_return_status => l_return_status
657 ,x_error_msg_code => l_error_msg_code );
658
659 /** depending on return status and record status call the appropriate table handler api **/
660
661 If l_return_status = 'S' and nvl(l_error_msg_code,'X') = 'X' then
662
663 If p_RECORD_STATUS = 'NEW' then
664 IF l_debug_mode = 'Y' THEN
665 print_msg('calling insert_row api');
666 End If;
667 PA_CI_SUPPLIER_PKG.insert_row (
668 x_rowid => l_rowid
669 ,x_ci_transaction_id => l_ci_transaction_id
670 ,p_CI_TYPE_ID => p_ci_type_id
671 ,p_CI_ID => p_CI_ID
672 ,p_CI_IMPACT_ID => p_ci_impact_id
673 ,p_VENDOR_ID => l_vendor_id
674 ,p_PO_HEADER_ID => l_po_header_id
675 ,p_PO_LINE_ID => l_po_line_id
676 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
677 ,p_CURRENCY_CODE => p_CURRENCY_CODE
678 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
679 ,p_CHANGE_TYPE => p_CHANGE_TYPE
680 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
681 ,p_CREATED_BY => FND_GLOBAL.login_id
682 ,p_CREATION_DATE => trunc(sysdate)
683 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
684 ,p_LAST_UPDATE_DATE => trunc(sysdate)
685 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
686 ,p_ci_status => l_CI_STATUS
687 ,x_return_status => l_return_status
688 ,x_error_msg_code => l_error_msg_code );
689
690 If l_return_status = 'S' then
691 p_ci_transaction_id := l_ci_transaction_id;
692 p_rowid := l_rowid;
693 IF l_debug_mode = 'Y' THEN
694 print_msg('Assigning citransactionid ='||p_ci_transaction_id);
695 End If;
696 /* Bug fix: 2634102 create impact line if not exists*/
697 IF ( NOT pa_ci_impacts_util.is_impact_exist
698 (p_ci_id => p_ci_id,
699 p_impact_type_code => 'SUPPLIER') ) THEN
700
701 IF l_debug_mode = 'Y' THEN
702 print_msg('Calling PA_CI_IMPACTS_pub.create_ci_impact Api');
703 End If;
704
705 PA_CI_IMPACTS_pub.create_ci_impact(
706 p_ci_id => p_ci_id,
707 p_impact_type_code => 'SUPPLIER',
708 p_status_code => 'CI_IMPACT_PENDING',
709 p_commit => 'F',
710 p_validate_only => 'F',
711 p_description => NULL,
712 p_implementation_comment => NULL,
713 x_ci_impact_id => l_ci_impact_id,
714 x_return_status => l_return_status,
715 x_msg_count => l_msg_count,
716 x_msg_data =>l_error_msg_code
717 );
718 End If;
719 /* End of bug fix : 2634102 */
720 End if;
721 IF l_debug_mode = 'Y' THEN
722 print_msg('end of insert row api');
723 End If;
724
725
726 Elsif p_RECORD_STATUS = 'CHANGED' then
727
728 /** Check if the ci_transaction_id is already populated then update the row else
729 ** insert the row with same ci_transaction_id. so that populating unnecessary sequence
730 ** number can be avoided.
731 **/
732
733 If check_trx_exists(l_ci_transaction_id) = 'Y' then
734 IF l_debug_mode = 'Y' THEN
735 print_msg('calling update row api');
736 End If;
737 PA_CI_SUPPLIER_PKG.update_row (
738 p_rowid => l_rowid
739 ,p_ci_transaction_id => l_ci_transaction_id
740 ,p_CI_TYPE_ID => p_ci_type_id
741 ,p_CI_ID => p_CI_ID
742 ,p_CI_IMPACT_ID => p_ci_impact_id
743 ,p_VENDOR_ID => l_vendor_id
744 ,p_PO_HEADER_ID => l_po_header_id
745 ,p_PO_LINE_ID => l_po_line_id
746 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
747 ,p_CURRENCY_CODE => p_CURRENCY_CODE
748 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
749 ,p_CHANGE_TYPE => p_CHANGE_TYPE
750 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
751 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
752 ,p_LAST_UPDATE_DATE => trunc(sysdate)
756 ,x_error_msg_code => l_error_msg_code );
753 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
754 ,p_ci_status => l_CI_STATUS
755 ,x_return_status => l_return_status
757 IF l_debug_mode = 'Y' THEN
758 print_msg('end of update row api');
759 End If;
760 Else
761 IF l_debug_mode = 'Y' THEN
762 print_msg('calling insert_row api for record status CHANGED');
763 End If;
764 PA_CI_SUPPLIER_PKG.insert_row (
765 x_rowid => l_rowid
766 ,x_ci_transaction_id => l_ci_transaction_id
767 ,p_CI_TYPE_ID => p_ci_type_id
768 ,p_CI_ID => p_CI_ID
769 ,p_CI_IMPACT_ID => p_ci_impact_id
770 ,p_VENDOR_ID => l_vendor_id
771 ,p_PO_HEADER_ID => l_po_header_id
772 ,p_PO_LINE_ID => l_po_line_id
773 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
774 ,p_CURRENCY_CODE => p_CURRENCY_CODE
775 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
776 ,p_CHANGE_TYPE => p_CHANGE_TYPE
777 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
778 ,p_CREATED_BY => FND_GLOBAL.login_id
779 ,p_CREATION_DATE => trunc(sysdate)
780 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
781 ,p_LAST_UPDATE_DATE => trunc(sysdate)
782 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
783 ,p_ci_status => l_CI_STATUS
784 ,x_return_status => l_return_status
785 ,x_error_msg_code => l_error_msg_code );
786
787 If l_return_status = 'S' then
788 p_ci_transaction_id := l_ci_transaction_id;
789 p_rowid := l_rowid;
790 IF l_debug_mode = 'Y' THEN
791 print_msg('Assigning citransactionid ='||p_ci_transaction_id);
792 End If;
793 /* Bug fix: 2634102 create impact line if not exists*/
794 IF ( NOT pa_ci_impacts_util.is_impact_exist
795 (p_ci_id => p_ci_id,
796 p_impact_type_code => 'SUPPLIER') ) THEN
797 IF l_debug_mode = 'Y' THEN
798 print_msg('Calling PA_CI_IMPACTS_pub.create_ci_impact in Update');
799 End If;
800
801 PA_CI_IMPACTS_pub.create_ci_impact(
802 p_ci_id => p_ci_id,
803 p_impact_type_code => 'SUPPLIER',
804 p_status_code => 'CI_IMPACT_PENDING',
805 p_commit => 'F',
806 p_validate_only => 'F',
807 p_description => NULL,
808 p_implementation_comment => NULL,
809 x_ci_impact_id => l_ci_impact_id,
810 x_return_status => l_return_status,
811 x_msg_count => l_msg_count,
812 x_msg_data =>l_error_msg_code
813 );
814 End If;
815 /* End of bug fix : 2634102 */
816
817 End if;
818 IF l_debug_mode = 'Y' THEN
819 print_msg('end of insert row api');
820 End if;
821
822
823 End If; -- End of check_trx_exists
824 End if;
825
826 End if;
827
828 If l_return_status <> 'S' and nvl(l_error_msg_code,'X') <> 'X' then
829 IF l_debug_mode = 'Y' THEN
830 print_msg('adding error msg to stack'||l_error_msg_code);
831 End If;
832
833 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
834 ,p_msg_name =>l_error_msg_code
835 );
836 l_msg_count := l_msg_count +1;
837
838 End if;
839
840 If l_msg_count = 1 then
841 pa_interface_utils_pub.get_messages
842 ( p_encoded => FND_API.G_TRUE
843 ,p_msg_index => 1
844 ,p_data => x_msg_data
848 x_return_status := 'E';
845 ,p_msg_index_out => l_msg_index_out
846 );
847
849 --x_msg_count := l_msg_count;
850 --x_msg_data := l_error_msg_code;
851 Elsif l_msg_count > 1 then
852 x_return_status := 'E';
853 x_msg_count := l_msg_count;
854 x_msg_data := null;
855 End if;
856 END IF; -- end of p_callingModule
857 pa_debug.reset_err_stack;
858 EXCEPTION
859 when others then
860 x_return_status := 'U';
861 x_msg_count := 1;
862 x_msg_data := SQLCODE||SQLERRM;
863 pa_debug.reset_err_stack;
864 RAISE;
865 END validateSI;
866
867 PROCEDURE deleteSIrecord(P_CALLING_MODE IN varchar2
868 ,p_ROWID IN varchar2
869 ,P_CI_TRANSACTION_ID IN number
870 ,X_RETURN_STATUS IN OUT NOCOPY varchar2
871 ,x_MSG_DATA IN OUT NOCOPY varchar2
872 ,X_MSG_COUNT IN OUT NOCOPY number ) IS
873
874 l_debug_mode varchar2(1) := 'N';
875 BEGIN
876 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
877 l_debug_mode := NVL(l_debug_mode, 'N');
878
879 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
880
881 -- initialize the error stack
882 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.deleteSIrecord');
883 IF l_debug_mode = 'Y' THEN
884 print_msg('inside deleteSIrecord api P_CALLING_MODE['||P_CALLING_MODE||']p_ROWID['||p_ROWID||
885 '] P_CI_TRANSACTION_ID['||P_CI_TRANSACTION_ID||']');
886 End If;
887
888 X_RETURN_STATUS := 'S';
889 x_MSG_DATA := null;
890 X_MSG_COUNT := 0;
891
892 If nvl(P_CI_TRANSACTION_ID ,0) <> 0 then
893
894 PA_CI_SUPPLIER_PKG.delete_row (p_ci_transaction_id => P_CI_TRANSACTION_ID);
895 /** issuing commit to prevent rollack issued by checkErrors method from java calls**/
896 commit;
897
898 End if;
899
900
901 pa_debug.reset_err_stack;
902 EXCEPTION
903 when others then
904 IF l_debug_mode = 'Y' THEN
905 print_msg('deleteSIrecord Error:'||sqlcode||sqlerrm);
906 End If;
907 x_return_status := 'U';
908 x_msg_count := 1;
909 x_msg_data := SQLCODE||SQLERRM;
910 RAISE;
911
912
913 END deleteSIrecord;
914
915
916 /** This API copies the supplier Impact details from one
917 ** project control item to another project control item
918 **/
919 PROCEDURE Merge_suppliers
920 ( p_from_ci_item_id IN NUMBER
921 ,p_to_ci_item_id IN NUMBER
922 ,x_return_status OUT NOCOPY VARCHAR2
923 ,x_error_msg OUT NOCOPY VARCHAR2
924 ) IS
925
926 l_debug_mode varchar2(1) := 'N';
927
928 BEGIN
929 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
930 l_debug_mode := NVL(l_debug_mode, 'N');
931
932 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
933
934 IF l_debug_mode = 'Y' THEN
935 print_msg('Inside Merge_suppliers Api params p_from_ci_item_id['||p_from_ci_item_id||
936 ']p_to_ci_item_id['||p_to_ci_item_id||']' );
937 End If;
938
939 x_return_status := 'S';
940 x_error_msg := NULL;
941
942 IF p_from_ci_item_id is NOT NULL and
943 p_to_ci_item_id is NOT NULL Then
944
945 INSERT INTO PA_CI_SUPPLIER_DETAILS
946 (
947 CI_TRANSACTION_ID
948 ,CI_TYPE_ID
949 ,CI_ID
950 ,CI_IMPACT_ID
951 ,VENDOR_ID
952 ,PO_HEADER_ID
953 ,PO_LINE_ID
954 ,ADJUSTED_CI_TRANSACTION_ID
955 ,CURRENCY_CODE
956 ,CHANGE_AMOUNT
957 ,CHANGE_TYPE
958 ,CHANGE_DESCRIPTION
959 ,CREATED_BY
960 ,CREATION_DATE
961 ,LAST_UPDATED_BY
962 ,LAST_UPDATE_DATE
963 ,LAST_UPDATE_LOGIN
964 )
965 SELECT
966 PA_CI_SUPPLIER_DETAILS_S.nextval
967 ,ci.CI_TYPE_ID
968 ,p_to_ci_item_id
969 ,si.CI_IMPACT_ID
970 ,si.VENDOR_ID
971 ,si.PO_HEADER_ID
972 ,si.PO_LINE_ID
973 ,si.CI_TRANSACTION_ID
974 ,si.CURRENCY_CODE
975 ,si.CHANGE_AMOUNT
976 ,si.CHANGE_TYPE
977 ,si.CHANGE_DESCRIPTION
978 ,NVL(FND_GLOBAL.login_id,-99)
979 ,sysdate
980 ,NVL(FND_GLOBAL.login_id,-99)
981 ,sysdate
982 ,NVL(FND_GLOBAL.login_id,-99)
983 FROM PA_CI_SUPPLIER_DETAILS si
984 ,PA_CONTROL_ITEMS ci
985 WHERE si.CI_ID = p_from_ci_item_id
986 AND ci.ci_id = p_to_ci_item_id;
987
988 IF l_debug_mode = 'Y' THEN
989 print_msg('Num of rows merged['||sql%rowcount||']');
990 End If;
991
992 End If;
993 EXCEPTION
994 when others then
998 x_return_status := 'U';
995 IF l_debug_mode = 'Y' THEN
996 print_msg('sqlerror:'||sqlcode||sqlerrm);
997 End If;
999 x_error_msg := SQLCODE||SQLERRM;
1000 RAISE;
1001
1002 END Merge_suppliers;
1003
1004 PROCEDURE DELETE_IMPACT(p_ci_id IN NUMBER
1005 ,x_return_status OUT NOCOPY VARCHAR2
1006 ,x_msg_data OUT NOCOPY VARCHAR2
1007 ,x_msg_count OUT NOCOPY NUMBER
1008 )IS
1009
1010 l_debug_mode varchar2(1) := 'N';
1011
1012 BEGIN
1013 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1014 l_debug_mode := NVL(l_debug_mode, 'N');
1015
1016 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1017
1018 -- initialize the error stack
1019 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.delete_impact');
1020 IF l_debug_mode = 'Y' THEN
1021 print_msg('Inside DELETE_IMPACT api p_ci_id['||p_ci_id||']' );
1022 End If;
1023
1024 X_RETURN_STATUS := 'S';
1025 x_MSG_DATA := null;
1026 X_MSG_COUNT := 0;
1027
1028 If nvl(p_ci_id ,0) <> 0 then
1029 DELETE FROM PA_CI_SUPPLIER_DETAILS
1030 WHERE ci_id = p_ci_id;
1031 commit;
1032
1033 End if;
1034
1035 pa_debug.reset_err_stack;
1036 EXCEPTION
1037 when others then
1038 IF l_debug_mode = 'Y' THEN
1039 print_msg('sqlerror:'||sqlcode||sqlerrm);
1040 End If;
1041 x_return_status := 'U';
1042 x_msg_count := 1;
1043 x_msg_data := SQLCODE||SQLERRM;
1044 pa_debug.reset_err_stack;
1045 RAISE;
1046
1047 END DELETE_IMPACT;
1048
1049 /** This Api checks transactions exists in supplier impact details
1050 ** and returns success 'S' if there are no transactions exists
1051 ** returns Error if transactions exists. This api is called before
1052 ** deleting records from pa_ci_impacts
1053 **/
1054
1055 PROCEDURE IS_SI_DELETE_OK(p_ci_id IN NUMBER
1056 ,x_return_status OUT NOCOPY VARCHAR2
1057 ,x_msg_data OUT NOCOPY VARCHAR2
1058 ,x_msg_count OUT NOCOPY NUMBER
1059 ) IS
1060
1061 cursor c1 is
1062 SELECT CI_TRANSACTION_ID
1063 FROM pa_ci_supplier_details
1064 WHERE ci_id = p_ci_id;
1065
1066 l_ci_transaction_id Number;
1067 l_msg_index_out Number;
1068 l_debug_mode varchar2(1) := 'N';
1069
1070 BEGIN
1071 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1072 l_debug_mode := NVL(l_debug_mode, 'N');
1073
1074 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1075
1076 -- initialize the error stack
1077 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.delete_impact');
1078
1079 /** clear the message stack **/
1080 fnd_msg_pub.INITIALIZE;
1081
1082 IF l_debug_mode = 'Y' THEN
1083 print_msg('Inside IS_SI_DELETE_OK api p_ci_id['||p_ci_id||']' );
1084 End If;
1085 x_return_status := 'S';
1086 x_msg_data := Null;
1087 x_msg_count := 0;
1088
1089 OPEN c1;
1090 FETCH c1 INTO l_ci_transaction_id;
1091 IF c1%FOUND then
1092 IF l_debug_mode = 'Y' THEN
1093 print_msg('Transaction Exists For Supplier Impacts');
1094 End If;
1095 x_msg_count:= 1;
1096 x_return_status := 'E';
1097 x_msg_data := 'PA_CISI_TRANS_EXISTS';
1098 End If;
1099 CLOSE C1;
1100
1101 If x_return_status <> 'S' then
1102 IF l_debug_mode = 'Y' THEN
1103 print_msg('Adding error msg to stack'||x_msg_data);
1104 End If;
1105 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1106 ,p_msg_name =>x_msg_data
1107 );
1108 End if;
1109
1110 If x_msg_count = 1 then
1111 pa_interface_utils_pub.get_messages
1112 ( p_encoded => FND_API.G_TRUE
1113 ,p_msg_index => 1
1114 ,p_data => x_msg_data
1115 ,p_msg_index_out => l_msg_index_out
1116 );
1117
1118 End if;
1119 -- Reset the error stack
1120 pa_debug.reset_err_stack;
1121
1122 EXCEPTION
1123
1124 WHEN OTHERS THEN
1125 IF l_debug_mode = 'Y' THEN
1126 print_msg('Error From IS_SI_DELETE_OK :sqlerror:'||sqlcode||sqlerrm);
1127 End If;
1128 x_return_status := 'U';
1129 x_msg_count := 1;
1130 x_msg_data := SQLCODE||SQLERRM;
1131 pa_debug.reset_err_stack;
1132 RAISE;
1133
1134 END IS_SI_DELETE_OK;
1135
1136 /** This is a overloaded function which makes calls to IS_SI_DELETE_OK plsql API
1137 ** and returns 'Y' to delete the records from supplier impact details
1138 **/
1139
1140 FUNCTION IS_SI_DELETE_OK(p_ci_id IN NUMBER) return varchar2 IS
1141
1142 l_return_status varchar2(10);
1143 l_err_msg_data varchar2(1000);
1144 l_msg_count Number;
1145 l_return_flag varchar2(1);
1146
1147 BEGIN
1148 l_return_flag := 'N';
1149
1150 PA_CI_SUPPLIER_UTILS.IS_SI_DELETE_OK
1151 (p_ci_id =>p_ci_id
1152 ,x_return_status =>l_return_status
1153 ,x_msg_data =>l_err_msg_data
1154 ,x_msg_count =>l_msg_count
1155 );
1156
1157 If l_return_status <> 'S' then
1158 -- Indicates records exists in SI table so donot delete header lines (pa_ci_impacts)
1159 l_return_flag := 'N';
1160 Else
1161 -- No records exists in SI table so delete header lines (pa_ci_impacts)
1162 l_return_flag := 'Y';
1163 End If;
1164
1165 RETURN l_return_flag;
1166
1167 EXCEPTION
1168 WHEN OTHERS THEN
1169 RAISE;
1170 l_return_flag := 'N';
1171 RETURN l_return_flag;
1172
1173 END IS_SI_DELETE_OK;
1174 /** This API removes the negative format mask such as if the
1175 * if the number <200,000.00> or {20,000.00} or [2,000.00]
1176 * this api returns with out brackets as -200,000.00/-20,000.00/-2,000.00
1177 * This api is begin used in Supplier impact java screen as a workaround
1178 * method of entereing negative amounts in supplier impact region
1179 * OA Framework is still working on this issue to fix the prolbem
1180 * refer to bug 2747172 and 2748904 for details
1181 **/
1182 FUNCTION get_formated_amount(p_currency_code varchar2
1183 ,p_amount number ) return varchar2
1184 IS
1185 l_string varchar2(100);
1186 s1 varchar2(100);
1187 s2 varchar2(100);
1188 l_return_string varchar2(200);
1189
1190 begin
1191 l_string := p_amount;
1192 if p_amount is not null and p_currency_code is not null then
1193 select to_char(p_amount, fnd_currency.get_format_mask(p_currency_code,30))
1194 into l_string
1195 from dual;
1196
1197 s1 := substr(l_string,0,1);
1198 s2 := substr(l_string,length(l_string),1);
1199 if((s1 ='<' and s2='>')OR (s1='(' and s2=')')OR(s1='[' and s2=']')) then
1200 l_return_string := ( '-'||substr(l_string,2,length(l_string)-2));
1201 elsif(s2='-') then
1202 l_return_string := ( '-'||substr(l_string,1,length(l_string)-1));
1203 elsif(s2='+') then
1204 l_return_string :=( substr(l_string,1,length(l_string)-1));
1205 else
1206 l_return_string:= l_string;
1207 End If;
1208
1209 end if;
1210 return l_return_string;
1211
1212 END get_formated_amount;
1213
1214 END PA_CI_SUPPLIER_UTILS;