[Home] [Help]
PACKAGE BODY: APPS.PA_CI_SUPPLIER_UTILS
Source
1 package body PA_CI_SUPPLIER_UTILS as
2 -- $Header: PASIUTLB.pls 120.28 2010/06/24 16:17:25 racheruv ship $
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 -- gboomina added for supplier cost 12.1.3 requirement - start
42 -- This method is used to check whether the record is unique in supplier cost
43 procedure is_record_unique(p_api_version in number,
44 p_init_msg_list in varchar2 default fnd_api.g_false,
45 x_return_status in out nocopy varchar2 ,
46 x_msg_data in out nocopy varchar2,
47 x_msg_count in out nocopy number,
48 p_ci_id in number,
49 p_task_id in number,
50 p_resource_list_mem_id in number,
51 p_expenditure_type in varchar2,
52 p_currency_code in varchar2,
53 p_vendor_id in number,
54 p_expenditure_org_id in number,
55 p_need_by_date in date,
56 p_po_line_id in number,
57 p_record_status in varchar2,
58 p_ci_transaction_id in number)
59 IS
60 l_return_status varchar2(1) := 'Y';
61 l_error_msg_code varchar2(100):= null;
62 l_msg_count number := 0;
63
64 -- Cursor to check whether supplier record is unique if
65 -- Change type is 'Create New', during SC Line create
66 cursor is_supp_cost_rec_exist_cre_1 is
67 select 'Y'
68 from pa_ci_supplier_details
69 where ci_id = p_ci_id
70 and task_id = p_task_id
71 and resource_list_member_id = p_resource_list_mem_id
72 and expenditure_type = p_expenditure_type
73 and currency_code = p_currency_code
74 and change_type = 'CREATE'
75 and vendor_id = p_vendor_id
76 and expenditure_org_id = p_expenditure_org_id
77 and need_by_date = p_need_by_date;
78
79 -- Cursor to check whether supplier record is unique if
80 -- Change type is 'Update Existing', during SC Line create
81 cursor is_supp_cost_rec_exist_cre_2 is
82 select 'Y'
83 from pa_ci_supplier_details
84 where ci_id = p_ci_id
85 and task_id = p_task_id
86 and resource_list_member_id = p_resource_list_mem_id
87 and expenditure_type = p_expenditure_type
88 and currency_code = p_currency_code
89 and change_type = 'UPDATE'
90 and po_line_id = p_po_line_id;
91
92 cursor is_direct_cost_rec_exist is
93 select 'Y'
94 from pa_ci_direct_cost_details
95 where ci_id = p_ci_id
96 and task_id = p_task_id
97 and resource_list_member_id = p_resource_list_mem_id
98 and expenditure_type = p_expenditure_type
99 and currency_code = p_currency_code;
100
101 l_exist varchar2(1) := 'N';
102
103 -- Cursor to check whether supplier record is unique if
104 -- Change type is 'Create New' during Update SC Line
105 cursor is_supp_cost_rec_exist_upd_1 is
106 select 'Y'
107 from pa_ci_supplier_details
108 where ci_id = p_ci_id
109 and task_id = p_task_id
110 and resource_list_member_id = p_resource_list_mem_id
111 and expenditure_type = p_expenditure_type
112 and currency_code = p_currency_code
113 and change_type = 'CREATE'
114 and vendor_id = p_vendor_id
115 and expenditure_org_id = p_expenditure_org_id
116 and need_by_date = p_need_by_date
117 and ci_transaction_id <> p_ci_transaction_id;
118
119 -- Cursor to check whether supplier record is unique if
120 -- Change type is 'Update Existing' during Update SC Line
121 cursor is_supp_cost_rec_exist_upd_2 is
122 select 'Y'
123 from pa_ci_supplier_details
124 where ci_id = p_ci_id
125 and task_id = p_task_id
126 and resource_list_member_id = p_resource_list_mem_id
127 and expenditure_type = p_expenditure_type
128 and currency_code = p_currency_code
129 and change_type = 'UPDATE'
130 and po_line_id = p_po_line_id
131 and ci_transaction_id <> p_ci_transaction_id;
132
133 BEGIN
134
135 IF p_ci_id is NOT NULL then
136
137 if (p_record_status = 'NEW') then
138 -- If the change type is 'Create New' then
139 -- we need to check whether the same combination of Task,
140 -- Expenditure Type, Resource, Supplier, Expenditure Org and Need by date
141 -- is present in Supplier Cost
142 open is_supp_cost_rec_exist_cre_1;
143 fetch is_supp_cost_rec_exist_cre_1 into l_exist;
144 close is_supp_cost_rec_exist_cre_1;
145
146 if ( l_exist = 'Y') then
147 l_error_msg_code := 'PA_CI_SC_REC_NOT_UNIQUE_CRE';
148 raise PA_API.G_EXCEPTION_ERROR;
149 else
150 -- If the change type is 'Update Existing' then
151 -- we need to check whether the same combination of Task,
152 -- Expenditure Type, Resource, Supplier, PO number and
153 -- PO Line Number is present in Supplier Cost
154 open is_supp_cost_rec_exist_cre_2;
155 fetch is_supp_cost_rec_exist_cre_2 into l_exist;
156 close is_supp_cost_rec_exist_cre_2;
157 if ( l_exist = 'Y') then
158 l_error_msg_code := 'PA_CI_SC_REC_NOT_UNIQUE_UPD';
159 raise PA_API.G_EXCEPTION_ERROR;
160 else
161 -- we need to check whether the same combination of Task,
162 -- Expenditure Type and Resource is present between Supplier
163 -- Cost end Direct Cost
164 open is_direct_cost_rec_exist;
165 fetch is_direct_cost_rec_exist into l_exist;
166 close is_direct_cost_rec_exist;
167 if ( l_exist = 'Y') then
168 l_error_msg_code := 'PA_FIN_SAME_PLANNING_ELEMENT';
169 raise PA_API.G_EXCEPTION_ERROR;
170 end if;
171 end if;
172 end if;
173 else if (p_record_status = 'CHANGED') then
174 -- If the change type is 'Create New' then
175 -- we need to check whether the same combination of Task,
176 -- Expenditure Type, Resource, Supplier, Expenditure Org and Need by date
177 -- is present in Supplier Cost
178 open is_supp_cost_rec_exist_upd_1;
179 fetch is_supp_cost_rec_exist_upd_1 into l_exist;
180 close is_supp_cost_rec_exist_upd_1;
181
182 if ( l_exist = 'Y') then
183 l_error_msg_code := 'PA_CI_SC_REC_NOT_UNIQUE_CRE';
184 raise PA_API.G_EXCEPTION_ERROR;
185 else
186 -- If the change type is 'Update Existing' then
187 -- we need to check whether the same combination of Task,
188 -- Expenditure Type, Resource, Supplier, PO number and
189 -- PO Line Number is present in Supplier Cost
190 open is_supp_cost_rec_exist_upd_2;
191 fetch is_supp_cost_rec_exist_upd_2 into l_exist;
192 close is_supp_cost_rec_exist_upd_2;
193 if ( l_exist = 'Y') then
194 l_error_msg_code := 'PA_CI_SC_REC_NOT_UNIQUE_UPD';
195 raise PA_API.G_EXCEPTION_ERROR;
196 end if;
197 end if;
198 end if;
199 end if;
200
201 END IF;
202 x_return_status := PA_API.G_RET_STS_SUCCESS;
203 EXCEPTION
204 WHEN PA_API.G_EXCEPTION_ERROR THEN
205 x_msg_count := l_msg_count+1;
206 x_msg_data := l_error_msg_code;
207 x_return_status := PA_API.G_RET_STS_ERROR;
208 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
209 x_msg_count := l_msg_count+1;
210 x_msg_data := l_error_msg_code;
211 x_return_status := PA_API.G_RET_STS_UNEXP_ERROR;
212 WHEN OTHERS THEN
213 x_msg_count := l_msg_count+1;
214 x_msg_data := l_error_msg_code||sqlerrm;
215 x_return_status := PA_API.G_RET_STS_UNEXP_ERROR;
216
217 END is_record_unique;
218 -- gboomina added for supplier cost 12.1.3 requirement - end
219
220 /** This api validates the supplier impact records and populates id for name **/
221 PROCEDURE validate_SI_record(
222 p_RECORD_STATUS IN VARCHAR2
223 ,p_CI_ID IN NUMBER
224 ,P_CALLING_MODE IN VARCHAR2
225 ,P_CI_STATUS IN VARCHAR2
226 ,P_ORG_ID IN VARCHAR2
227 ,x_VENDOR_ID IN OUT NOCOPY NUMBER
228 ,p_VENDOR_NAME IN VARCHAR2
229 ,x_PO_HEADER_ID IN OUT NOCOPY NUMBER
230 ,p_PO_NUMBER IN VARCHAR2
231 ,x_PO_LINE_ID IN OUT NOCOPY NUMBER
232 ,p_PO_LINE_NUM IN NUMBER
233 ,p_ADJUSTED_TRANSACTION_ID IN NUMBER
234 ,p_CURRENCY_CODE IN VARCHAR2
235 ,p_CHANGE_AMOUNT IN NUMBER
236 ,p_CHANGE_TYPE IN VARCHAR2
237 ,p_CHANGE_DESCRIPTION IN VARCHAR2
238 ,p_Task_Id IN VARCHAR2
239 ,p_Resource_List_Mem_Id IN VARCHAR2
240 ,p_From_Date IN VARCHAR2
241 ,p_To_Date IN VARCHAR2
242 ,p_Estimated_Cost IN VARCHAR2
243 ,p_Quoted_Cost IN VARCHAR2
244 ,p_Negotiated_Cost IN VARCHAR2
245 ,p_Burdened_cost IN VARCHAR2
246 ,p_revenue_override_rate IN varchar2
247 ,p_audit_history_number in number
248 ,p_current_audit_flag in varchar2
249 ,p_Original_supp_trans_id in number
250 ,p_Source_supp_trans_id in number
251 ,p_Sup_ref_no in number
252 ,p_version_type in varchar2 default 'ALL'
253 ,p_ci_transaction_id IN NUMBER
254 ,x_return_status OUT NOCOPY VARCHAR2
255 ,x_error_msg_code OUT NOCOPY VARCHAR2 ) IS
256
257 return_error EXCEPTION;
258 l_error_msg varchar2(1000) := NULL;
259 l_return_status VARCHAR2(10) := 'S';
260 l_vendor_id NUMBER;
261 l_po_header_id NUMBER;
262 l_po_line_id NUMBER;
263 l_change_type VARCHAR2(100);
264 l_currency_code VARCHAR2(100);
265 l_debug_mode varchar2(1) := 'N';
266 l_task_id number;
267
268 PROCEDURE validate_supplier(l_error_msg OUT NOCOPY varchar2) IS
269 cursor cur_ven is
270 SELECT vendor_id
271 FROM po_vendors
272 WHERE vendor_name = p_vendor_name;
273 BEGIN
274 If p_record_status in ('NEW','CHANGED') Then
275 If l_debug_mode = 'Y' Then
276 print_msg('inside validate_supplier api');
277 End If;
278 If p_vendor_name is NULL then
279 l_error_msg := 'PA_CISI_SUPPLIER_NULL';
280 Else
281 OPEN cur_ven;
282 FETCH cur_ven INTO l_vendor_id;
283 IF cur_ven%NOTFOUND then
284
285 l_error_msg := 'PA_CISI_SUPPLIER_INVALID';
286 END IF;
287 CLOSE cur_ven;
288 x_vendor_id := l_vendor_id;
289 End If;
290 End If;
291 EXCEPTION
292 WHEN OTHERS THEN
293 l_error_msg := sqlcode||sqlerrm;
294 Raise;
295
296 END validate_supplier;
297
298 PROCEDURE validate_PO(c_vendor_id number,l_error_msg OUT NOCOPY varchar2) IS
299 cursor cur_po is
300 SELECT po.po_header_id
301 FROM po_headers_all po
302 WHERE po.segment1 = p_po_number
303 AND po.vendor_id = c_vendor_id
304 /* added this condition to cehck Po status is OPEN or APPRVOED */
305 AND NVL(po.closed_code,'XX') NOT in ('FINALLY CLOSED','CLOSED')
306 AND (( po.org_id = p_org_id
307 AND p_org_id is NOT NULL )
308 OR p_org_id is NULL
309 );
310 BEGIN
311 If l_debug_mode = 'Y' Then
312 print_msg('insdie validate_PO api c_vendor_id['||c_vendor_id||']');
313 End If;
314 If p_change_type is NOT NULL and p_change_type = 'CREATE'
315 and p_po_number is NOT NULL then
316 l_error_msg := 'PA_CISI_INVALID_PO_CHANGE';
317 Elsif p_change_type = 'UPDATE' and p_po_number is NOT NULL and p_org_id is NOT NULL then
318 OPEN cur_po;
319 FETCH cur_po INTO l_po_header_id;
320 IF cur_po%NOTFOUND THEN
321 l_error_msg := 'PA_CISI_INVALID_PO';
322 Else
323 x_po_header_id := l_po_header_id;
324 End If;
325 CLOSE cur_po;
326 Elsif p_change_type = 'UPDATE' and p_po_number is NOT NULL and p_org_id is NULL then
327 OPEN cur_po;
328 FETCH cur_po INTO l_po_header_id;
329 IF cur_po%NOTFOUND THEN
330 l_error_msg := 'PA_CISI_INVALID_PO';
331 Else
332 if cur_po%Rowcount > 1 then
333 l_error_msg := 'PA_CISI_PO_EXISTS';
334 Else
335 x_po_header_id := l_po_header_id;
336 End If;
337 End If;
338 CLOSE cur_po;
339 End If;
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 l_error_msg := sqlcode||sqlerrm;
344 Raise;
345
346 END validate_PO;
347
348 PROCEDURE validate_PO_line(c_po_header_id number,c_po_line_num number
349 ,l_error_msg OUT NOCOPY varchar2) IS
350 cursor cur_po_line is
351 SELECT pol.po_line_id
352 FROM po_lines_all pol
353 ,po_headers_all poh
354 WHERE pol.po_header_id = poh.po_header_id
355 AND poh.po_header_id = c_po_header_id
356 AND pol.line_num = c_po_line_num
357 /* added this condition to cehck Po status is OPEN or APPRVOED */
358 AND NVL(poh.closed_code,'XX') NOT in ('FINALLY CLOSED','CLOSED');
359
360 BEGIN
361 If l_debug_mode = 'Y' Then
362 print_msg('inside validate_PO_line api HeaderId['||c_po_header_id||
363 ']c_po_line_num['||c_po_line_num||']');
364 End If;
365 IF c_po_header_id is NOT NULL then
366 If c_po_line_num is NULL then
367 l_error_msg := 'PA_CISI_POLINE_NULL';
368 Else
369 OPEN cur_po_line;
370 FETCH cur_po_line INTO l_po_line_id;
371 IF cur_po_line%NOTFOUND then
372 l_error_msg := 'PA_CISI_INVALID_POLINE'; /*Bug fix : 2634057 */
373 Else
374 x_po_line_id := l_po_line_id;
375 End If;
376 CLOSE cur_po_line;
377 End if;
378 End If;
379 EXCEPTION
380 WHEN OTHERS THEN
381 l_error_msg := SQLCODE||SQLERRM;
382 raise;
383
384 END validate_PO_line;
385
386 PROCEDURE Validate_change_type(l_error_msg OUT NOCOPY varchar2) IS
387 BEGIN
388 If l_debug_mode = 'Y' Then
389 print_msg('inside Validate_change_type api');
390 End If;
391 If p_change_type is NULL then
392 --Changes for new supplier region
393 l_error_msg := 'PA_CISI_CHANGE_TYPE_NULL';
394
395 /* ElsIf p_po_number is null and p_PO_LINE_NUM is null AND p_change_type = 'UPDATE' then
396 l_error_msg := 'PA_CISI_INVALID_CHANGE_TYPE';
397 Elsif p_po_number is null and p_PO_LINE_NUM is NOT Null AND p_change_type = 'UPDATE' then
398 l_error_msg := 'PA_CISI_INVALID_PO';
399 Elsif p_po_number is NOT Null and p_PO_LINE_NUM is Null AND p_change_type = 'UPDATE' then
400 l_error_msg := 'PA_CISI_POLINE_NULL';
401 Elsif (p_po_number is NOT Null OR p_PO_LINE_NUM is NOT Null) AND p_change_type = 'CREATE' then
402 l_error_msg := 'PA_CISI_INVALID_CHANGE_TYPE'; */
403 End if;
404
405 END Validate_change_type;
406
407 PROCEDURE Validate_Currency(c_po_header_id number,l_error_msg OUT NOCOPY varchar2) IS
408 cursor cur_po_currency IS
409 SELECT po.currency_code
410 FROM po_headers_all po
411 WHERE po.po_header_id = c_po_header_id
412 AND po.currency_code = p_currency_code;
413
414 cursor cur_currency is
415 SELECT currency_code
416 FROM fnd_currencies -- Modified for Bug 4403203.
417 WHERE enabled_flag = 'Y'
418 AND trunc(sysdate) between nvl(start_date_active,trunc(sysdate))
419 and nvl(end_date_active,trunc(sysdate))
420 AND currency_code = p_currency_code;
421 BEGIN
422 If l_debug_mode = 'Y' Then
423 print_msg('inside Validate_Currency api['||c_po_header_id||']');
424 End If;
425 If p_currency_code is NULL and p_vendor_name is NOT NULL then
426 l_error_msg := 'PA_CISI_CURRENCY_NULL';
427
428 ElsIf c_po_header_id is NOT NULL then
429 OPEN cur_po_currency;
430 FETCH cur_po_currency INTO l_currency_code;
431 IF cur_po_currency%NOTFOUND THEN
432 l_error_msg := 'PA_CISI_INVALID_CURRENCY';
433 End If;
434 CLOSE cur_po_currency;
435 Else
436 OPEN cur_currency;
437 FETCH cur_currency INTO l_currency_code;
438 IF cur_currency%NOTFOUND THEN
439 l_error_msg := 'PA_CISI_INVALID_CURRENCY';
440 End if;
441 CLOSE cur_currency;
442 End if;
443 EXCEPTION
444 WHEN OTHERS THEN
445 l_error_msg := sqlcode||sqlerrm;
446
447 END Validate_Currency;
448
449 PROCEDURE Validate_changeamt Is
450
451 BEGIN
452 If l_debug_mode = 'Y' Then
453 print_msg('inside Validate_changeamt');
454 End If;
455 /** this condition is commented out as -ve amt should be allowed for SI impact
456 if p_change_amount < 0 then
457 l_error_msg := 'PA_CISI_NEG_AMT';
458 Els **/
459 if p_change_amount is NULL then
460 l_error_msg := 'PA_CISI_CHANGEAMT_NULL';
461 End if;
462
463
464 END Validate_changeamt ;
465
466
467 BEGIN
468 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
469 l_debug_mode := NVL(l_debug_mode, 'N');
470 --x_PO_HEADER_ID := 44878;
471 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
472
473 /** reset the return status and error msg code **/
474 x_return_status := 'S';
475 x_error_msg_code := NULL;
476 l_error_msg := null;
477
478
479 /** VALIDATE SUPPLIER NAME **/
480 validate_supplier(l_error_msg);
481 If l_error_msg is NOT NULL then
482 Raise return_error;
483 End If;
484
485 validate_change_type(l_error_msg);
486 If l_error_msg is NOT NULL then
487 Raise return_error;
488 End If;
489
490 validate_PO(x_vendor_id,l_error_msg);
491 If l_error_msg is NOT NULL then
492 Raise return_error;
493 End If;
494
495 if (p_po_line_num is not null) then
496 validate_PO_line(x_po_header_id,p_po_line_num,l_error_msg);
497 If l_error_msg is NOT NULL then
498 Raise return_error;
499 End If;
500 end if;
501
502 validate_currency(x_po_header_id,l_error_msg);
503 If l_error_msg is NOT NULL then
504 Raise return_error;
505 End If;
506
507 Validate_changeamt;
508 If l_error_msg is NOT NULL then
509 Raise return_error;
510 End If;
511
512 EXCEPTION
513 WHEN return_error then
514 x_return_status := 'E';
515 x_error_msg_code := l_error_msg;
516 If l_debug_mode = 'Y' Then
517 print_msg('errmsg='||l_error_msg);
518 End If;
519 Return;
520 when others then
521 x_return_status := 'U';
522 x_error_msg_code := sqlcode||sqlerrm;
523 Raise;
524
525 END validate_SI_record;
526
527
528 /** This is called from Supplier Impact UI scrren this is a wrapper api which in turn
529 ** calls validate SI record for each single record
530 **/
531 PROCEDURE validate_insert_SI (
532 p_ROWID IN OUT NOCOPY PA_VC_1000_150
533 ,p_RECORD_STATUS IN PA_VC_1000_150
534 ,p_CI_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
535 ,p_CI_TYPE_ID IN PA_VC_1000_150
536 ,p_CI_IMPACT_ID IN PA_VC_1000_150
537 ,P_CALLING_MODE IN VARCHAR2
538 ,P_CI_STATUS IN PA_VC_1000_150
539 ,P_ORG_ID IN PA_VC_1000_150
540 ,x_VENDOR_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
541 ,p_VENDOR_NAME IN PA_VC_1000_150
542 ,x_PO_HEADER_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
543 ,p_PO_NUMBER IN PA_VC_1000_150
544 ,x_PO_LINE_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
545 ,p_PO_LINE_NUM IN PA_VC_1000_150 --PA_VC_1000_NUM
546 ,p_ADJUSTED_TRANSACTION_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
547 ,p_CURRENCY_CODE IN PA_VC_1000_150
548 ,p_CHANGE_AMOUNT IN PA_VC_1000_150 --PA_VC_1000_NUM
549 ,p_CHANGE_TYPE IN PA_VC_1000_150
550 ,p_CHANGE_DESCRIPTION IN PA_VC_1000_150
551 ,p_Task_Id IN PA_VC_1000_150
552 ,p_Resource_List_Mem_Id IN PA_VC_1000_150
553 ,p_From_Date IN PA_VC_1000_150
554 ,p_To_Date IN PA_VC_1000_150
555 ,p_Estimated_Cost IN PA_VC_1000_150
556 ,p_Quoted_Cost IN PA_VC_1000_150
557 ,p_Negotiated_Cost IN PA_VC_1000_150
558 ,p_Burdened_cost IN PA_VC_1000_150
559 ,p_revenue_override_rate IN PA_VC_1000_150
560 ,p_audit_history_number in number
561 ,p_current_audit_flag in varchar2
562 ,p_Original_supp_trans_id in number
563 ,p_Source_supp_trans_id in number
564 ,p_Sup_ref_no in number
565 ,p_version_type in varchar2 default 'ALL'
566 -- gboomina modified for supplier cost 12.1.3 requirement - start
567 ,p_expenditure_type in varchar2
568 ,p_expenditure_org_id in number
569 ,p_change_reason_code in varchar2
570 ,p_quote_negotiation_reference in varchar2
571 ,p_need_by_date in varchar2
572 -- gboomina modified for supplier cost 12.1.3 requirement - end
573 ,p_ci_transaction_id IN OUT NOCOPY PA_VC_1000_150
574 ,p_RECORD_ID IN OUT NOCOPY PA_VC_1000_150
575 ,p_REC_RETURN_STATUS IN OUT NOCOPY PA_VC_1000_150
576 ,x_return_status IN OUT NOCOPY VARCHAR2
577 ,x_msg_data IN OUT NOCOPY VARCHAR2
578 ,x_msg_count IN OUT NOCOPY NUMBER
579 ) IS
580
581 l_error_msg_code varchar2(100):= null;
582 l_rec_count number := 0;
583 l_counter number := 0;
584 l_msg_count number := 0;
585
586 l_vendor_id pa_plsql_datatypes.IdTabTyp;
587 l_po_header_id pa_plsql_datatypes.IdTabTyp;
588 l_po_line_id pa_plsql_datatypes.IdTabTyp;
589 l_return_status pa_plsql_datatypes.char50TabTyp;
590 l_rowid pa_plsql_datatypes.char150TabTyp;
591 l_ci_transaction_id pa_plsql_datatypes.IdTabTyp;
592
593 l_debug_mode varchar2(1) := 'N';
594
595
596
597 BEGIN
598 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
599 l_debug_mode := NVL(l_debug_mode, 'N');
600
601 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
602
603 -- initialize the error stack
604 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.validate_insert_SI');
605
606 /** clear the message stack **/
607 fnd_msg_pub.INITIALIZE;
608
609 l_rec_count := p_RECORD_STATUS.count();
610
611 /** Initialize plsql tables **/
612 l_vendor_id.delete;
613 l_po_header_id.delete;
614 l_po_line_id.delete;
615 l_return_status.delete;
616 l_rowid.delete;
617 l_ci_transaction_id.delete;
618
619
620 IF (p_calling_mode = 'VALIDATEANDINSERT') then
621
622 FOR i in 1 .. l_rec_count LOOP
623
624 /** print the inpput params **/
625 If l_debug_mode = 'Y' THEN
626 print_msg('p_RECORD_STATUS['||p_RECORD_STATUS(i)||']p_CI_ID['||p_CI_ID(i)||
627 ']P_CI_STATUS['||P_CI_STATUS(i)||']p_VENDOR_NAME['||p_VENDOR_NAME(i)||
628 ']p_PO_NUMBER['||p_PO_NUMBER(i)||']p_PO_LINE_NUM['||p_PO_LINE_NUM(i)||']p_CURRENCY_CODE['||
629 p_CURRENCY_CODE(i)||']p_CHANGE_AMOUNT['||p_CHANGE_AMOUNT(i)||']p_CHANGE_TYPE['||p_CHANGE_TYPE(i)||
630 ']p_CHANGE_DESCRIPTION['||p_CHANGE_DESCRIPTION(i)||']p_rowid['||p_rowid(i)||
631 ']p_ci_transaction_id['||p_ci_transaction_id(i)||']' );
632 End If;
633
634
635 l_error_msg_code := NULL;
636 l_vendor_id(i) := null;
637 l_po_header_id(i) := null;
638 l_po_line_id(i) := null;
639 l_return_status(i) := 'S';
640 l_rowid(i) := p_rowid(i);
641 l_ci_transaction_id(i) := p_ci_transaction_id(i);
642 p_REC_RETURN_STATUS(i) := 'S';
643 If l_debug_mode = 'Y' THEN
644 print_msg('calling validate_SI_record');
645 End If;
646
647 validate_SI_record(
648 p_RECORD_STATUS => p_RECORD_STATUS(i)
649 ,p_CI_ID => p_CI_ID(i)
650 ,P_CALLING_MODE => 'VALIDATE'
651 ,P_CI_STATUS => P_CI_STATUS(i)
652 ,P_ORG_ID => p_org_id(i) /* Bug fix fnd_profile.value('ORG_ID') */
653 ,x_VENDOR_ID => l_vendor_id(i)
654 ,p_VENDOR_NAME => p_VENDOR_NAME(i)
655 ,x_PO_HEADER_ID => l_po_header_id(i)
656 ,p_PO_NUMBER => p_PO_NUMBER(i)
657 ,x_PO_LINE_ID => l_po_line_id(i)
658 ,p_PO_LINE_NUM => p_PO_LINE_NUM(i)
659 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID(i)
660 ,p_CURRENCY_CODE => p_CURRENCY_CODE(i)
661 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT(i)
662 ,p_CHANGE_TYPE => p_CHANGE_TYPE(i)
663 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION(i)
664 ,p_Task_Id => p_Task_Id(i)
665 ,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id(i)
666 ,p_From_Date => p_From_Date(i)
667 ,p_To_Date => p_To_Date(i)
668 ,p_Estimated_Cost => p_Estimated_Cost(i)
669 ,p_Quoted_Cost => p_Quoted_Cost(i)
670 ,p_Negotiated_Cost => p_Negotiated_Cost(i)
671 ,p_Burdened_cost => p_Burdened_cost(i)
672 ,p_revenue_override_rate => p_revenue_override_rate(i)
673 ,p_audit_history_number => p_audit_history_number
674 ,p_current_audit_flag => p_current_audit_flag
675 ,p_Original_supp_trans_id => p_Original_supp_trans_id
676 ,p_Source_supp_trans_id => p_Source_supp_trans_id
677 ,p_Sup_ref_no => p_Sup_ref_no
678 ,p_ci_transaction_id => l_ci_transaction_id(i)
679 ,x_return_status => l_return_status(i)
680 ,x_error_msg_code => l_error_msg_code );
681
682 /** depending on return status and record status call the appropriate table handler api **/
683
684 If l_return_status(i) = 'S' and nvl(l_error_msg_code,'X') = 'X' then
685
686 If p_RECORD_STATUS(i) = 'NEW' then
687 If l_debug_mode = 'Y' THEN
688 print_msg('calling insert_row api');
689 End If;
690 PA_CI_SUPPLIER_PKG.insert_row (
691 x_rowid => l_rowid(i)
692 ,x_ci_transaction_id => l_ci_transaction_id(i)
693 ,p_CI_TYPE_ID => p_ci_type_id(i)
694 ,p_CI_ID => p_CI_ID(i)
695 ,p_CI_IMPACT_ID => p_ci_impact_id(i)
696 ,p_VENDOR_ID => l_vendor_id(i)
697 ,p_PO_HEADER_ID => l_po_header_id(i)
698 ,p_PO_LINE_ID => l_po_line_id(i)
699 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID(i)
700 ,p_CURRENCY_CODE => p_CURRENCY_CODE(i)
701 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT(i)
702 ,p_CHANGE_TYPE => p_CHANGE_TYPE(i)
703 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION(i)
704 ,p_CREATED_BY => FND_GLOBAL.login_id
705 ,p_CREATION_DATE => trunc(sysdate)
706 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
707 ,p_LAST_UPDATE_DATE => trunc(sysdate)
708 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
709 ,p_Task_Id => p_Task_Id(i)
710 ,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id(i)
711 ,p_From_Date => p_From_Date(i)
712 ,p_To_Date => p_To_Date(i)
713 ,p_Estimated_Cost => p_Estimated_Cost(i)
714 ,p_Quoted_Cost => p_Quoted_Cost(i)
715 ,p_Negotiated_Cost => p_Negotiated_Cost(i)
716 ,p_Burdened_cost => p_Burdened_cost(i)
717 ,p_Revenue => null
718 ,p_revenue_override_rate => p_revenue_override_rate(i)
719 ,p_audit_history_number => p_audit_history_number
720 ,p_current_audit_flag => 'Y'
721 ,p_Original_supp_trans_id => p_Original_supp_trans_id
722 ,p_Source_supp_trans_id => p_Source_supp_trans_id
723 ,p_Sup_ref_no => p_Sup_ref_no
724 ,p_version_type => p_version_type
725 -- gboomina modified for supplier cost 12.1.3 requirement - start
726 ,p_expenditure_type => p_expenditure_type
727 ,p_expenditure_org_id => p_expenditure_org_id
728 ,p_change_reason_code => p_change_reason_code
729 ,p_quote_negotiation_reference => p_quote_negotiation_reference
730 ,p_need_by_date => p_need_by_date
731 -- gboomina modified for supplier cost 12.1.3 requirement - end
732 ,x_return_status => l_return_status(i)
733 ,x_error_msg_code => l_error_msg_code );
734
735 If l_return_status(i) = 'S' then
736 p_ci_transaction_id(i) := l_ci_transaction_id(i);
737 p_rowid(i) := l_rowid(i);
738 If l_debug_mode = 'Y' THEN
739 print_msg('Assigning citransactionid ='||p_ci_transaction_id(i));
740 End If;
741 End if;
742 If l_debug_mode = 'Y' THEN
743 print_msg('end of insert row api');
744 End If;
745
746
747 Elsif p_RECORD_STATUS(i) = 'CHANGED' then
748 If l_debug_mode = 'Y' THEN
749 print_msg('calling update row api');
750 End If;
751 PA_CI_SUPPLIER_PKG.update_row (
752 p_rowid => l_rowid(i)
753 ,p_ci_transaction_id => l_ci_transaction_id(i)
754 ,p_CI_TYPE_ID => p_ci_type_id(i)
755 ,p_CI_ID => p_CI_ID(i)
756 ,p_CI_IMPACT_ID => p_ci_impact_id(i)
757 ,p_VENDOR_ID => l_vendor_id(i)
758 ,p_PO_HEADER_ID => l_po_header_id(i)
759 ,p_PO_LINE_ID => l_po_line_id(i)
760 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID(i)
761 ,p_CURRENCY_CODE => p_CURRENCY_CODE(i)
762 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT(i)
763 ,p_CHANGE_TYPE => p_CHANGE_TYPE(i)
764 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION(i)
765 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
766 ,p_LAST_UPDATE_DATE => trunc(sysdate)
767 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
768 ,p_Task_Id => p_Task_Id(i)
769 ,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id(i)
770 ,p_From_Date => p_From_Date(i)
771 ,p_To_Date => p_To_Date(i)
772 ,p_Estimated_Cost => p_Estimated_Cost(i)
773 ,p_Quoted_Cost => p_Quoted_Cost(i)
774 ,p_Negotiated_Cost => p_Negotiated_Cost(i)
775 ,p_Burdened_cost => p_Burdened_cost(i)
776 ,p_Revenue => null
777 ,p_revenue_override_rate => p_revenue_override_rate(i)
778 ,p_audit_history_number => p_audit_history_number
779 ,p_current_audit_flag => p_current_audit_flag
780 ,p_Original_supp_trans_id => p_Original_supp_trans_id
781 ,p_Source_supp_trans_id => p_Source_supp_trans_id
782 ,p_ci_status => p_Sup_ref_no
783 -- gboomina modified for supplier cost 12.1.3 requirement - start
784 ,p_expenditure_type => p_expenditure_type
785 ,p_expenditure_org_id => p_expenditure_org_id
786 ,p_change_reason_code => p_change_reason_code
787 ,p_quote_negotiation_reference => p_quote_negotiation_reference
788 ,p_need_by_date => p_need_by_date
789 -- gboomina modified for supplier cost 12.1.3 requirement - end
790 ,x_return_status => l_return_status(i)
791 ,x_error_msg_code => l_error_msg_code );
792 If l_debug_mode = 'Y' THEN
793 print_msg('end of update row api');
794 End If;
795
796 End if;
797
798 End if;
799
800 If l_return_status(i) <> 'S' and nvl(l_error_msg_code,'X') <> 'X' then
801 If l_debug_mode = 'Y' THEN
802 print_msg('adding error msg to stack'||l_error_msg_code);
803 End If;
804
805 p_REC_RETURN_STATUS(i) := 'E';
806 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
807 ,p_msg_name =>l_error_msg_code
808 );
809 l_msg_count := l_msg_count +1;
810
811 End if;
812
813
814 END LOOP;
815
816 If l_msg_count = 1 then
817 x_return_status := 'E';
818 x_msg_count := l_msg_count;
819 x_msg_data := l_error_msg_code;
820 Elsif l_msg_count > 1 then
821 x_return_status := 'E';
822 x_msg_count := l_msg_count;
823 x_msg_data := null;
824 End if;
825
826
827 END IF;
828
829 pa_debug.reset_err_stack;
830 EXCEPTION
831 when others then
832 x_return_status := 'U';
833 x_msg_count := 1;
834 x_msg_data := SQLCODE||SQLERRM;
835 pa_debug.reset_err_stack;
836 RAISE;
837
838 END validate_insert_SI;
839
840 PROCEDURE validateSI(p_ROWID IN OUT NOCOPY VARCHAR2
841 ,p_RECORD_STATUS IN VARCHAR2
842 ,p_CI_ID IN VARCHAR2
843 ,p_CI_TYPE_ID IN VARCHAR2
844 ,p_CI_IMPACT_ID IN VARCHAR2
845 ,P_CALLING_MODE IN VARCHAR2
846 ,P_ORG_ID IN VARCHAR2
847 ,p_VENDOR_NAME IN VARCHAR2
848 ,p_PO_NUMBER IN VARCHAR2
849 ,p_PO_LINE_NUM IN VARCHAR2
850 ,p_ADJUSTED_TRANSACTION_ID IN VARCHAR2
851 ,p_CURRENCY_CODE IN VARCHAR2
852 ,p_CHANGE_AMOUNT IN VARCHAR2
853 ,p_CHANGE_TYPE IN VARCHAR2
854 ,p_CHANGE_DESCRIPTION IN VARCHAR2
855 ,p_Task_Id IN VARCHAR2
856 ,p_Resource_List_Mem_Id IN VARCHAR2
857 ,p_From_Date IN VARCHAR2
858 ,p_To_Date IN VARCHAR2
859 ,p_Estimated_Cost IN VARCHAR2
860 ,p_Quoted_Cost IN VARCHAR2
861 ,p_Negotiated_Cost IN VARCHAR2
862 ,p_Burdened_cost IN VARCHAR2
863 ,p_Revenue IN VARCHAR2 default NULL
864 ,p_revenue_override_rate IN varchar2
865 ,p_audit_history_number in number
866 ,p_current_audit_flag in varchar2
867 ,p_Original_supp_trans_id in number
868 ,p_Source_supp_trans_id in number
869 ,p_Sup_ref_no in number default null
870 ,p_version_type in varchar2 default 'ALL'
871 -- gboomina modified for supplier cost 12.1.3 requirement - start
872 ,p_expenditure_type in varchar2
873 ,p_expenditure_org_id in number
874 ,p_change_reason_code in varchar2
875 ,p_quote_negotiation_reference in varchar2
876 ,p_need_by_date in varchar2
877 -- gboomina modified for supplier cost 12.1.3 requirement - end
878 ,p_CI_TRANSACTION_ID IN OUT NOCOPY VARCHAR2
879 ,x_return_status IN OUT NOCOPY VARCHAR2
880 ,x_msg_data IN OUT NOCOPY VARCHAR2
881 ,x_msg_count IN OUT NOCOPY NUMBER
882 ) IS
883
884 l_CI_STATUS varchar2(10);
885 l_error_msg_code varchar2(1000);
886 l_vendor_id number ;
887 l_po_header_id number;
888 l_po_line_id number;
889 l_return_status varchar2(1) := 'S';
890 l_rowid varchar2(100) := p_rowid;
891 l_ci_transaction_id number := p_ci_transaction_id;
892 l_msg_count number := 0;
893 l_msg_index_out number := 0;
894 l_ci_impact_id number;
895 l_debug_mode varchar2(1) := 'N';
896 cursor cur_impact_id IS
897 SELECT ci_impact_id
898 FROM pa_ci_impacts pci
899 WHERE pci.ci_id = p_ci_id
900 AND pci.IMPACT_TYPE_CODE = 'SUPPLIER';
901
902
903
904 BEGIN
905 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
906 l_debug_mode := NVL(l_debug_mode, 'N');
907
908 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
909
910 -- initialize the error stack
911 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.validateSI');
912
913 /** clear the message stack **/
914 fnd_msg_pub.INITIALIZE;
915
916 IF (p_calling_mode = 'VALIDATEANDINSERT') then
917
918 /** print the inpput params **/
919 IF l_debug_mode = 'Y' THEN
920 print_msg('p_RECORD_STATUS['||p_RECORD_STATUS||']p_CI_ID['||p_CI_ID||
921 ']p_VENDOR_NAME['||p_VENDOR_NAME||
922 -- gboomina addded for 12.1.3 supplier cost requirement
923 ']EXPENDITURE_TYPE['||p_expenditure_type||
924 ']p_PO_NUMBER['||p_PO_NUMBER||']p_PO_LINE_NUM['||p_PO_LINE_NUM||']p_CURRENCY_CODE['||
925 p_CURRENCY_CODE||']p_CHANGE_AMOUNT['||p_CHANGE_AMOUNT||']p_CHANGE_TYPE['||p_CHANGE_TYPE||
926 ']p_CHANGE_DESCRIPTION['||p_CHANGE_DESCRIPTION||']p_rowid['||p_rowid||
927 ']p_ci_transaction_id['||p_ci_transaction_id||']p_org_id['||p_org_id||']' );
928 End If;
929
930
931 l_error_msg_code := NULL;
932 l_vendor_id := null;
933 l_po_header_id := null;
934 l_po_line_id := null;
935 l_return_status := 'S';
936 l_rowid := p_rowid;
937 l_ci_transaction_id := p_ci_transaction_id;
938 l_CI_STATUS := null;
939 IF l_debug_mode = 'Y' THEN
940 print_msg('calling validate_SI_record');
941 End If;
942
943 validate_SI_record(
944 p_RECORD_STATUS => p_RECORD_STATUS
945 ,p_CI_ID => p_CI_ID
946 ,P_CALLING_MODE => 'VALIDATE'
947 ,P_CI_STATUS => l_CI_STATUS
948 ,P_ORG_ID => p_org_id /*Bug fix fnd_profile.value('ORG_ID')*/
949 ,x_VENDOR_ID => l_vendor_id
950 ,p_VENDOR_NAME => p_VENDOR_NAME
951 ,x_PO_HEADER_ID => l_po_header_id
952 ,p_PO_NUMBER => p_PO_NUMBER
953 ,x_PO_LINE_ID => l_po_line_id
954 ,p_PO_LINE_NUM => p_PO_LINE_NUM
955 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
956 ,p_CURRENCY_CODE => p_CURRENCY_CODE
957 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
958 ,p_CHANGE_TYPE => p_CHANGE_TYPE
959 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
960 ,p_Task_Id => p_Task_Id
961 ,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id
962 ,p_From_Date => p_From_Date
963 ,p_To_Date => p_To_Date
964 ,p_Estimated_Cost => p_Estimated_Cost
965 ,p_Quoted_Cost => p_Quoted_Cost
966 ,p_Negotiated_Cost => p_Negotiated_Cost
967 ,p_Burdened_cost => p_Burdened_cost
968 ,p_revenue_override_rate => p_revenue_override_rate
969 ,p_audit_history_number => p_audit_history_number
970 ,p_current_audit_flag => p_current_audit_flag
971 ,p_Original_supp_trans_id => p_Original_supp_trans_id
972 ,p_Source_supp_trans_id => p_Source_supp_trans_id
973 ,p_Sup_ref_no => p_Sup_ref_no
974 ,p_ci_transaction_id => l_ci_transaction_id
975 ,x_return_status => l_return_status
976 ,x_error_msg_code => l_error_msg_code );
977
978 /** depending on return status and record status call the appropriate table handler api **/
979
980 If l_return_status = 'S' and nvl(l_error_msg_code,'X') = 'X' then
981
982 If p_RECORD_STATUS = 'NEW' then
983
984 /* Bug fix: 2634102 create impact line if not exists*/
985 IF ( NOT pa_ci_impacts_util.is_impact_exist
986 (p_ci_id => p_ci_id,
987 p_impact_type_code => 'SUPPLIER') ) THEN
988
989 IF l_debug_mode = 'Y' THEN
990 print_msg('Calling PA_CI_IMPACTS_pub.create_ci_impact Api');
991 End If;
992
993 PA_CI_IMPACTS_pub.create_ci_impact(
994 p_ci_id => p_ci_id,
995 p_impact_type_code => 'SUPPLIER',
996 p_status_code => 'CI_IMPACT_PENDING',
997 p_commit => 'F',
998 p_validate_only => 'F',
999 p_description => NULL,
1000 p_implementation_comment => NULL,
1001 x_ci_impact_id => l_ci_impact_id,
1002 x_return_status => l_return_status,
1003 x_msg_count => l_msg_count,
1004 x_msg_data =>l_error_msg_code
1005 );
1006
1007 ELSE
1008 OPEN cur_impact_id;
1009 FETCH cur_impact_id INTO l_ci_impact_id;
1010 CLOSE cur_impact_id;
1011 End If;
1012 /* End of bug fix : 2634102 */
1013
1014 IF l_debug_mode = 'Y' THEN
1015 print_msg('calling insert_row api');
1016 End If;
1017
1018 PA_CI_SUPPLIER_PKG.insert_row (
1019 x_rowid => l_rowid
1020 ,x_ci_transaction_id => l_ci_transaction_id
1021 ,p_CI_TYPE_ID => p_ci_type_id
1022 ,p_CI_ID => p_CI_ID
1023 ,p_CI_IMPACT_ID => l_ci_impact_id
1024 ,p_VENDOR_ID => l_vendor_id
1025 ,p_PO_HEADER_ID => l_po_header_id
1026 ,p_PO_LINE_ID => l_po_line_id
1027 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
1028 ,p_CURRENCY_CODE => p_CURRENCY_CODE
1029 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
1030 ,p_CHANGE_TYPE => p_CHANGE_TYPE
1031 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
1032 ,p_CREATED_BY => FND_GLOBAL.login_id
1033 ,p_CREATION_DATE => trunc(sysdate)
1034 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
1035 ,p_LAST_UPDATE_DATE => trunc(sysdate)
1036 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
1037 ,p_Task_Id => p_Task_Id
1038 ,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id
1039 ,p_From_Date => p_From_Date
1040 ,p_To_Date => p_To_Date
1041 ,p_Estimated_Cost => p_Estimated_Cost
1042 ,p_Quoted_Cost => p_Quoted_Cost
1043 ,p_Negotiated_Cost => p_Negotiated_Cost
1044 ,p_Burdened_cost => p_Burdened_cost
1045 ,p_Revenue => p_Revenue
1046 ,p_revenue_override_rate => p_revenue_override_rate
1047 ,p_audit_history_number => p_audit_history_number
1048 ,p_current_audit_flag => p_current_audit_flag
1049 ,p_Original_supp_trans_id => p_Original_supp_trans_id
1050 ,p_Source_supp_trans_id => p_Source_supp_trans_id
1051 ,p_Sup_ref_no => p_Sup_ref_no
1052 ,p_version_type => p_version_type
1053 -- gboomina modified for supplier cost 12.1.3 requirement - start
1054 ,p_expenditure_type => p_expenditure_type
1055 ,p_expenditure_org_id => p_expenditure_org_id
1056 ,p_change_reason_code => p_change_reason_code
1057 ,p_quote_negotiation_reference => p_quote_negotiation_reference
1058 ,p_need_by_date => p_need_by_date
1059 -- gboomina modified for supplier cost 12.1.3 requirement - end
1060 ,x_return_status => l_return_status
1061 ,x_error_msg_code => l_error_msg_code );
1062
1063 If l_return_status = 'S' then
1064 p_ci_transaction_id := l_ci_transaction_id;
1065 p_rowid := l_rowid;
1066 IF l_debug_mode = 'Y' THEN
1067 print_msg('Assigning citransactionid ='||p_ci_transaction_id);
1068 End If;
1069
1070 End if;
1071 IF l_debug_mode = 'Y' THEN
1072 print_msg('end of insert row api');
1073 End If;
1074
1075
1076 Elsif p_RECORD_STATUS = 'CHANGED' then
1077 /** Check if the ci_transaction_id is already populated then update the row else
1078 ** insert the row with same ci_transaction_id. so that populating unnecessary sequence
1079 ** number can be avoided.
1080 **/
1081
1082 If check_trx_exists(l_ci_transaction_id) = 'Y' then
1083 IF l_debug_mode = 'Y' THEN
1084 print_msg('calling update row api');
1085 End If;
1086
1087 PA_CI_SUPPLIER_PKG.update_row (
1088 p_rowid => l_rowid
1089 ,p_ci_transaction_id => l_ci_transaction_id
1090 ,p_CI_TYPE_ID => p_ci_type_id
1091 ,p_CI_ID => p_CI_ID
1092 ,p_CI_IMPACT_ID => p_ci_impact_id
1093 ,p_VENDOR_ID => l_vendor_id
1094 ,p_PO_HEADER_ID => l_po_header_id
1095 ,p_PO_LINE_ID => l_po_line_id
1096 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
1097 ,p_CURRENCY_CODE => p_CURRENCY_CODE
1098 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
1099 ,p_CHANGE_TYPE => p_CHANGE_TYPE
1100 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
1101 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
1102 ,p_LAST_UPDATE_DATE => trunc(sysdate)
1103 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
1104 ,p_Task_Id => p_Task_Id
1105 ,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id
1106 ,p_From_Date => p_From_Date
1107 ,p_To_Date => p_To_Date
1108 ,p_Estimated_Cost => p_Estimated_Cost
1109 ,p_Quoted_Cost => p_Quoted_Cost
1110 ,p_Negotiated_Cost => p_Negotiated_Cost
1111 ,p_Burdened_cost => p_Burdened_cost
1112 ,p_Revenue => p_Revenue
1113 ,p_revenue_override_rate =>p_revenue_override_rate
1114 ,p_audit_history_number => p_audit_history_number
1115 ,p_current_audit_flag => p_current_audit_flag
1116 ,p_Original_supp_trans_id => p_Original_supp_trans_id
1117 ,p_Source_supp_trans_id => p_Source_supp_trans_id
1118 ,p_ci_status => p_Sup_ref_no
1119 -- gboomina modified for supplier cost 12.1.3 requirement - start
1120 ,p_expenditure_type => p_expenditure_type
1121 ,p_expenditure_org_id => p_expenditure_org_id
1122 ,p_change_reason_code => p_change_reason_code
1123 ,p_quote_negotiation_reference => p_quote_negotiation_reference
1124 ,p_need_by_date => p_need_by_date
1125 -- gboomina modified for supplier cost 12.1.3 requirement - end
1126 ,x_return_status => l_return_status
1127 ,x_error_msg_code => l_error_msg_code );
1128
1129 IF l_debug_mode = 'Y' THEN
1130 print_msg('end of update row api');
1131 End If;
1132 Else
1133 IF l_debug_mode = 'Y' THEN
1134 print_msg('calling insert_row api for record status CHANGED');
1135 End If;
1136
1137 PA_CI_SUPPLIER_PKG.insert_row (
1138 x_rowid => l_rowid
1139 ,x_ci_transaction_id => l_ci_transaction_id
1140 ,p_CI_TYPE_ID => p_ci_type_id
1141 ,p_CI_ID => p_CI_ID
1142 ,p_CI_IMPACT_ID => p_ci_impact_id
1143 ,p_VENDOR_ID => l_vendor_id
1144 ,p_PO_HEADER_ID => l_po_header_id
1145 ,p_PO_LINE_ID => l_po_line_id
1146 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
1147 ,p_CURRENCY_CODE => p_CURRENCY_CODE
1148 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
1149 ,p_CHANGE_TYPE => p_CHANGE_TYPE
1150 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
1151 ,p_CREATED_BY => FND_GLOBAL.login_id
1152 ,p_CREATION_DATE => trunc(sysdate)
1153 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
1154 ,p_LAST_UPDATE_DATE => trunc(sysdate)
1155 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
1156 ,p_Task_Id => p_Task_Id
1157 ,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id
1158 ,p_From_Date => p_From_Date
1159 ,p_To_Date => p_To_Date
1160 ,p_Estimated_Cost => p_Estimated_Cost
1161 ,p_Quoted_Cost => p_Quoted_Cost
1162 ,p_Negotiated_Cost => p_Negotiated_Cost
1163 ,p_Burdened_cost => p_Burdened_cost
1164 ,p_Revenue => p_Revenue
1165 ,p_revenue_override_rate =>p_revenue_override_rate
1166 ,p_audit_history_number => p_audit_history_number
1167 ,p_current_audit_flag => p_current_audit_flag
1168 ,p_Original_supp_trans_id => p_Original_supp_trans_id
1169 ,p_Source_supp_trans_id => p_Source_supp_trans_id
1170 ,p_Sup_ref_no => p_Sup_ref_no
1171 ,p_version_type => p_version_type
1172 -- gboomina modified for supplier cost 12.1.3 requirement - start
1173 ,p_expenditure_type => p_expenditure_type
1174 ,p_expenditure_org_id => p_expenditure_org_id
1175 ,p_change_reason_code => p_change_reason_code
1176 ,p_quote_negotiation_reference => p_quote_negotiation_reference
1177 ,p_need_by_date => p_need_by_date
1178 -- gboomina modified for supplier cost 12.1.3 requirement - end
1179 ,x_return_status => l_return_status
1180 ,x_error_msg_code => l_error_msg_code );
1181
1182 If l_return_status = 'S' then
1183 p_ci_transaction_id := l_ci_transaction_id;
1184 p_rowid := l_rowid;
1185 IF l_debug_mode = 'Y' THEN
1186 print_msg('Assigning citransactionid ='||p_ci_transaction_id);
1187 End If;
1188 /* Bug fix: 2634102 create impact line if not exists*/
1189 IF ( NOT pa_ci_impacts_util.is_impact_exist
1190 (p_ci_id => p_ci_id,
1191 p_impact_type_code => 'SUPPLIER') ) THEN
1192 IF l_debug_mode = 'Y' THEN
1193 print_msg('Calling PA_CI_IMPACTS_pub.create_ci_impact in Update');
1194 End If;
1195
1196 PA_CI_IMPACTS_pub.create_ci_impact(
1197 p_ci_id => p_ci_id,
1198 p_impact_type_code => 'SUPPLIER',
1199 p_status_code => 'CI_IMPACT_PENDING',
1200 p_commit => 'F',
1201 p_validate_only => 'F',
1202 p_description => NULL,
1203 p_implementation_comment => NULL,
1204 x_ci_impact_id => l_ci_impact_id,
1205 x_return_status => l_return_status,
1206 x_msg_count => l_msg_count,
1207 x_msg_data =>l_error_msg_code
1208 );
1209 End If;
1210 /* End of bug fix : 2634102 */
1211
1212 End if;
1213 IF l_debug_mode = 'Y' THEN
1214 print_msg('end of insert row api');
1215 End if;
1216
1217
1218 End If; -- End of check_trx_exists
1219 End if;
1220
1221 End if;
1222 If l_return_status <> 'S' and nvl(l_error_msg_code,'X') <> 'X' then
1223
1224 IF l_debug_mode = 'Y' THEN
1225 print_msg('adding error msg to stack'||l_error_msg_code);
1226 End If;
1227
1228 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1229 ,p_msg_name =>l_error_msg_code
1230 );
1231 l_msg_count := l_msg_count +1;
1232
1233 End if;
1234
1235 If l_msg_count = 1 then
1236 pa_interface_utils_pub.get_messages
1237 ( p_encoded => FND_API.G_TRUE
1238 ,p_msg_index => 1
1239 ,p_data => x_msg_data
1240 ,p_msg_index_out => l_msg_index_out
1241 );
1242
1243 x_return_status := 'E';
1244 --x_msg_count := l_msg_count;
1245 --x_msg_data := l_error_msg_code;
1246 Elsif l_msg_count > 1 then
1247 x_return_status := 'E';
1248 x_msg_count := l_msg_count;
1249 x_msg_data := null;
1250 End if;
1251 END IF; -- end of p_callingModule
1252 pa_debug.reset_err_stack;
1253 EXCEPTION
1254 when others then
1255 x_return_status := 'U';
1256 x_msg_count := 1;
1257 x_msg_data := SQLCODE||SQLERRM;
1258 pa_debug.reset_err_stack;
1259 RAISE;
1260 END validateSI;
1261
1262 PROCEDURE deleteSIrecord(P_CALLING_MODE IN varchar2
1263 ,p_ROWID IN varchar2
1264 ,P_CI_TRANSACTION_ID IN number
1265 ,X_RETURN_STATUS IN OUT NOCOPY varchar2
1266 ,x_MSG_DATA IN OUT NOCOPY varchar2
1267 ,X_MSG_COUNT IN OUT NOCOPY number ) IS
1268
1269 l_debug_mode varchar2(1) := 'N';
1270 BEGIN
1271 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1272 l_debug_mode := NVL(l_debug_mode, 'N');
1273
1274 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1275
1276 -- initialize the error stack
1277 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.deleteSIrecord');
1278 IF l_debug_mode = 'Y' THEN
1279 print_msg('inside deleteSIrecord api P_CALLING_MODE['||P_CALLING_MODE||']p_ROWID['||p_ROWID||
1280 '] P_CI_TRANSACTION_ID['||P_CI_TRANSACTION_ID||']');
1281 End If;
1282
1283 X_RETURN_STATUS := 'S';
1284 x_MSG_DATA := null;
1285 X_MSG_COUNT := 0;
1286
1287 If nvl(P_CI_TRANSACTION_ID ,0) <> 0 then
1288
1289 PA_CI_SUPPLIER_PKG.delete_row (p_ci_transaction_id => P_CI_TRANSACTION_ID);
1290 /** issuing commit to prevent rollack issued by checkErrors method from java calls**/
1291 commit;
1292
1293 End if;
1294
1295
1296 pa_debug.reset_err_stack;
1297 EXCEPTION
1298 when others then
1299 IF l_debug_mode = 'Y' THEN
1300 print_msg('deleteSIrecord Error:'||sqlcode||sqlerrm);
1301 End If;
1302 x_return_status := 'U';
1303 x_msg_count := 1;
1304 x_msg_data := SQLCODE||SQLERRM;
1305 RAISE;
1306
1307
1308 END deleteSIrecord;
1309
1310
1311 /** This API copies the supplier Impact details from one
1312 ** project control item to another project control item
1313 **/
1314 PROCEDURE Merge_suppliers
1315 ( p_from_ci_item_id IN NUMBER
1316 ,p_to_ci_item_id IN NUMBER
1317 ,x_return_status OUT NOCOPY VARCHAR2
1318 ,x_error_msg OUT NOCOPY VARCHAR2
1319 ) IS
1320
1321 l_debug_mode varchar2(1) := 'N';
1322
1323 BEGIN
1324 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1325 l_debug_mode := NVL(l_debug_mode, 'N');
1326
1327 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1328
1329 IF l_debug_mode = 'Y' THEN
1330 print_msg('Inside Merge_suppliers Api params p_from_ci_item_id['||p_from_ci_item_id||
1331 ']p_to_ci_item_id['||p_to_ci_item_id||']' );
1332 End If;
1333
1334 x_return_status := 'S';
1335 x_error_msg := NULL;
1336
1337 IF p_from_ci_item_id is NOT NULL and
1338 p_to_ci_item_id is NOT NULL Then
1339
1340 INSERT INTO PA_CI_SUPPLIER_DETAILS
1341 (
1342 CI_TRANSACTION_ID
1343 ,CI_TYPE_ID
1344 ,CI_ID
1345 ,CI_IMPACT_ID
1346 ,VENDOR_ID
1347 ,PO_HEADER_ID
1348 ,PO_LINE_ID
1349 ,ADJUSTED_CI_TRANSACTION_ID
1350 ,CURRENCY_CODE
1351 ,CHANGE_AMOUNT
1352 ,CHANGE_TYPE
1353 ,CHANGE_DESCRIPTION
1354 ,CREATED_BY
1355 ,CREATION_DATE
1356 ,LAST_UPDATED_BY
1357 ,LAST_UPDATE_DATE
1358 ,LAST_UPDATE_LOGIN
1359 )
1360 SELECT
1361 PA_CI_SUPPLIER_DETAILS_S.nextval
1362 ,ci.CI_TYPE_ID
1363 ,p_to_ci_item_id
1364 ,si.CI_IMPACT_ID
1365 ,si.VENDOR_ID
1366 ,si.PO_HEADER_ID
1367 ,si.PO_LINE_ID
1368 ,si.CI_TRANSACTION_ID
1369 ,si.CURRENCY_CODE
1370 ,si.CHANGE_AMOUNT
1371 ,si.CHANGE_TYPE
1372 ,si.CHANGE_DESCRIPTION
1373 ,NVL(FND_GLOBAL.login_id,-99)
1374 ,sysdate
1375 ,NVL(FND_GLOBAL.login_id,-99)
1376 ,sysdate
1377 ,NVL(FND_GLOBAL.login_id,-99)
1378 FROM PA_CI_SUPPLIER_DETAILS si
1379 ,PA_CONTROL_ITEMS ci
1380 WHERE si.CI_ID = p_from_ci_item_id
1381 AND ci.ci_id = p_to_ci_item_id;
1382
1383 IF l_debug_mode = 'Y' THEN
1384 print_msg('Num of rows merged['||sql%rowcount||']');
1385 End If;
1386
1387 End If;
1388 EXCEPTION
1389 when others then
1390 IF l_debug_mode = 'Y' THEN
1391 print_msg('sqlerror:'||sqlcode||sqlerrm);
1392 End If;
1393 x_return_status := 'U';
1394 x_error_msg := SQLCODE||SQLERRM;
1395 RAISE;
1396
1397 END Merge_suppliers;
1398
1399 PROCEDURE DELETE_IMPACT(p_ci_id IN NUMBER
1400 ,x_return_status OUT NOCOPY VARCHAR2
1401 ,x_msg_data OUT NOCOPY VARCHAR2
1402 ,x_msg_count OUT NOCOPY NUMBER
1403 )IS
1404
1405 l_debug_mode varchar2(1) := 'N';
1406
1407 BEGIN
1408 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1409 l_debug_mode := NVL(l_debug_mode, 'N');
1410
1411 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1412
1413 -- initialize the error stack
1414 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.delete_impact');
1415 IF l_debug_mode = 'Y' THEN
1416 print_msg('Inside DELETE_IMPACT api p_ci_id['||p_ci_id||']' );
1417 End If;
1418
1419 X_RETURN_STATUS := 'S';
1420 x_MSG_DATA := null;
1421 X_MSG_COUNT := 0;
1422
1423 If nvl(p_ci_id ,0) <> 0 then
1424 DELETE FROM PA_CI_SUPPLIER_DETAILS
1425 WHERE ci_id = p_ci_id;
1426 commit;
1427
1428 End if;
1429
1430 pa_debug.reset_err_stack;
1431 EXCEPTION
1432 when others then
1433 IF l_debug_mode = 'Y' THEN
1434 print_msg('sqlerror:'||sqlcode||sqlerrm);
1435 End If;
1436 x_return_status := 'U';
1437 x_msg_count := 1;
1438 x_msg_data := SQLCODE||SQLERRM;
1439 pa_debug.reset_err_stack;
1440 RAISE;
1441
1442 END DELETE_IMPACT;
1443
1444 /** This Api checks transactions exists in supplier impact details
1445 ** and returns success 'S' if there are no transactions exists
1446 ** returns Error if transactions exists. This api is called before
1447 ** deleting records from pa_ci_impacts
1448 **/
1449
1450 PROCEDURE IS_SI_DELETE_OK(p_ci_id IN NUMBER
1451 ,x_return_status OUT NOCOPY VARCHAR2
1452 ,x_msg_data OUT NOCOPY VARCHAR2
1453 ,x_msg_count OUT NOCOPY NUMBER
1454 ) IS
1455
1456 cursor c1 is
1457 SELECT CI_TRANSACTION_ID
1458 FROM pa_ci_supplier_details
1459 WHERE ci_id = p_ci_id;
1460
1461 l_ci_transaction_id Number;
1462 l_msg_index_out Number;
1463 l_debug_mode varchar2(1) := 'N';
1464
1465 BEGIN
1466 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1467 l_debug_mode := NVL(l_debug_mode, 'N');
1468
1469 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1470
1471 -- initialize the error stack
1472 PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.delete_impact');
1473
1474 /** clear the message stack **/
1475 fnd_msg_pub.INITIALIZE;
1476
1477 IF l_debug_mode = 'Y' THEN
1478 print_msg('Inside IS_SI_DELETE_OK api p_ci_id['||p_ci_id||']' );
1479 End If;
1480 x_return_status := 'S';
1481 x_msg_data := Null;
1482 x_msg_count := 0;
1483
1484 OPEN c1;
1485 FETCH c1 INTO l_ci_transaction_id;
1486 IF c1%FOUND then
1487 IF l_debug_mode = 'Y' THEN
1488 print_msg('Transaction Exists For Supplier Impacts');
1489 End If;
1490 x_msg_count:= 1;
1491 x_return_status := 'E';
1492 x_msg_data := 'PA_CISI_TRANS_EXISTS';
1493 End If;
1494 CLOSE C1;
1495
1496 If x_return_status <> 'S' then
1497 IF l_debug_mode = 'Y' THEN
1498 print_msg('Adding error msg to stack'||x_msg_data);
1499 End If;
1500 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1501 ,p_msg_name =>x_msg_data
1502 );
1503 End if;
1504
1505 If x_msg_count = 1 then
1506 pa_interface_utils_pub.get_messages
1507 ( p_encoded => FND_API.G_TRUE
1508 ,p_msg_index => 1
1509 ,p_data => x_msg_data
1510 ,p_msg_index_out => l_msg_index_out
1511 );
1512
1513 End if;
1514 -- Reset the error stack
1515 pa_debug.reset_err_stack;
1516
1517 EXCEPTION
1518
1519 WHEN OTHERS THEN
1520 IF l_debug_mode = 'Y' THEN
1521 print_msg('Error From IS_SI_DELETE_OK :sqlerror:'||sqlcode||sqlerrm);
1522 End If;
1523 x_return_status := 'U';
1524 x_msg_count := 1;
1525 x_msg_data := SQLCODE||SQLERRM;
1526 pa_debug.reset_err_stack;
1527 RAISE;
1528
1529 END IS_SI_DELETE_OK;
1530
1531 /** This is a overloaded function which makes calls to IS_SI_DELETE_OK plsql API
1532 ** and returns 'Y' to delete the records from supplier impact details
1533 **/
1534
1535 FUNCTION IS_SI_DELETE_OK(p_ci_id IN NUMBER) return varchar2 IS
1536
1537 l_return_status varchar2(10);
1538 l_err_msg_data varchar2(1000);
1539 l_msg_count Number;
1540 l_return_flag varchar2(1);
1541
1542 BEGIN
1543 l_return_flag := 'N';
1544
1545 PA_CI_SUPPLIER_UTILS.IS_SI_DELETE_OK
1546 (p_ci_id =>p_ci_id
1547 ,x_return_status =>l_return_status
1548 ,x_msg_data =>l_err_msg_data
1549 ,x_msg_count =>l_msg_count
1550 );
1551
1552 If l_return_status <> 'S' then
1553 -- Indicates records exists in SI table so donot delete header lines (pa_ci_impacts)
1554 l_return_flag := 'N';
1555 Else
1556 -- No records exists in SI table so delete header lines (pa_ci_impacts)
1557 l_return_flag := 'Y';
1558 End If;
1559
1560 RETURN l_return_flag;
1561
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 RAISE;
1565 l_return_flag := 'N';
1566 RETURN l_return_flag;
1567
1568 END IS_SI_DELETE_OK;
1569 /** This API removes the negative format mask such as if the
1570 * if the number <200,000.00> or {20,000.00} or [2,000.00]
1571 * this api returns with out brackets as -200,000.00/-20,000.00/-2,000.00
1572 * This api is begin used in Supplier impact java screen as a workaround
1573 * method of entereing negative amounts in supplier impact region
1574 * OA Framework is still working on this issue to fix the prolbem
1575 * refer to bug 2747172 and 2748904 for details
1576 **/
1577 FUNCTION get_formated_amount(p_currency_code varchar2
1578 ,p_amount number ) return varchar2
1579 IS
1580 l_string varchar2(100);
1581 s1 varchar2(100);
1582 s2 varchar2(100);
1583 l_return_string varchar2(200);
1584
1585 begin
1586 l_string := p_amount;
1587 if p_amount is not null and p_currency_code is not null then
1588 select to_char(p_amount, fnd_currency.get_format_mask(p_currency_code,30))
1589 into l_string
1590 from dual;
1591
1592 s1 := substr(l_string,0,1);
1593 s2 := substr(l_string,length(l_string),1);
1594 if((s1 ='<' and s2='>')OR (s1='(' and s2=')')OR(s1='[' and s2=']')) then
1595 l_return_string := ( '-'||substr(l_string,2,length(l_string)-2));
1596 elsif(s2='-') then
1597 l_return_string := ( '-'||substr(l_string,1,length(l_string)-1));
1598 elsif(s2='+') then
1599 l_return_string :=( substr(l_string,1,length(l_string)-1));
1600 else
1601 l_return_string:= l_string;
1602 End If;
1603
1604 end if;
1605 return l_return_string;
1606
1607 END get_formated_amount;
1608
1609 PROCEDURE GET_Original_Ci_ID(p_ci_id IN NUMBER
1610 ,x_original_ci_id OUT NOCOPY number
1611 ,x_return_status OUT NOCOPY VARCHAR2
1612 ,x_msg_data OUT NOCOPY VARCHAR2
1613 ,x_msg_count OUT NOCOPY NUMBER) IS
1614
1615 CURSOR cur(p_ci_id number) is
1616 SELECT
1617 decode (NVL(original_ci_id,0),0,ci_id, original_ci_id)
1618 FROM pa_control_items
1619 WHERE ci_id = p_ci_id;
1620
1621 l_ci_id number;
1622 original_ci_id number;
1623 l_no_of_app_plan_types number;
1624
1625 BEGIN
1626 l_ci_id := p_ci_id;
1627
1628 if l_ci_id is not null then
1629 open cur(l_ci_id);
1630 fetch cur into original_ci_id;
1631 close cur;
1632 else
1633 print_msg('Ci Id cannot be null');
1634 end if;
1635 x_original_ci_id := original_ci_id;
1636 EXCEPTION
1637
1638 WHEN OTHERS THEN
1639
1640 x_return_status := 'U';
1641 x_msg_count := 1;
1642 x_msg_data := SQLCODE||SQLERRM;
1643 pa_debug.reset_err_stack;
1644 RAISE;
1645
1646 end GET_Original_Ci_ID;
1647
1648 --knk procedure for getting the resource list id
1649 PROCEDURE GET_RESOURCE_LIST_ID(p_project_id IN NUMBER
1650 ,x_res_list_id OUT NOCOPY number
1651 ,x_return_status OUT NOCOPY VARCHAR2
1652 ,x_msg_data OUT NOCOPY VARCHAR2
1653 ,x_msg_count OUT NOCOPY NUMBER) IS
1654
1655 CURSOR cur(p_project_id number) is
1656 SELECT
1657 decode (NVL(all_resource_list_id,0),0,cost_resource_list_id , all_resource_list_id)
1658 FROM pa_proj_fp_options pfo, pa_budget_versions pbv
1659 WHERE pbv.project_id = p_project_id AND
1660 pbv.current_working_flag = 'Y' AND
1661 pbv.budget_status_code = 'W' AND
1662 Nvl(pbv.approved_cost_plan_type_flag,'N') = 'Y' AND
1663 pfo.fin_plan_version_id = pbv.budget_version_id;
1664
1665
1666
1667 l_project_id number;
1668 res_list_id number := 1000;
1669 l_no_of_app_plan_types number;
1670
1671 BEGIN
1672 l_project_id := p_project_id;
1673
1674 if l_project_id is not null then
1675 SELECT COUNT(*) INTO l_no_of_app_plan_types FROM Pa_Proj_Fp_Options
1676 WHERE
1677 Project_Id = l_project_id AND
1678 Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
1679 ( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' ) ;
1680
1681 if l_no_of_app_plan_types <> 0 then
1682 open cur(l_project_id);
1683 fetch cur into res_list_id;
1684 close cur;
1685 else
1686 null;
1687 end if;
1688
1689 else
1690 print_msg('Project Id cannot be null');
1691 end if;
1692 x_res_list_id := res_list_id;
1693 EXCEPTION
1694
1695 WHEN OTHERS THEN
1696
1697 x_return_status := 'U';
1698 x_msg_count := 1;
1699 x_msg_data := SQLCODE||SQLERRM;
1700 pa_debug.reset_err_stack;
1701 RAISE;
1702
1703 end GET_RESOURCE_LIST_ID;
1704
1705 --knk proc for getting the total cost of the corresponding Change Document
1706 PROCEDURE GET_TOTAL_COST(p_ci_id IN NUMBER
1707 ,x_total_cost OUT NOCOPY NUMBER
1708 ,x_return_status OUT NOCOPY VARCHAR2
1709 ,x_msg_data OUT NOCOPY VARCHAR2
1710 ,x_msg_count OUT NOCOPY NUMBER) IS
1711
1712 l_ci_id number;
1713 total_cost number := 1000;
1714
1715 BEGIN
1716 l_ci_id := p_ci_id;
1717
1718 if l_ci_id is not null then
1719 SELECT sum(change_amount) INTO total_cost
1720 FROM pa_ci_supplier_details_v
1721 WHERE
1722 ci_id = l_ci_id;
1723 else
1724 print_msg('Project Id cannot be null');
1725 end if;
1726 x_total_cost := total_cost;
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 x_return_status := 'U';
1730 x_msg_count := 1;
1731 x_msg_data := SQLCODE||SQLERRM;
1732 pa_debug.reset_err_stack;
1733 RAISE;
1734
1735 end GET_TOTAL_COST;
1736
1737 -- gboomina added for 12.1.3 supplier cost requirement - start
1738 PROCEDURE delete_supplier_costs(
1739 p_api_version IN NUMBER,
1740 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1741 x_return_status OUT NOCOPY VARCHAR2,
1742 x_msg_count OUT NOCOPY NUMBER,
1743 x_msg_data OUT NOCOPY VARCHAR2,
1744 p_ci_transaction_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE ,
1745 p_ci_id IN NUMBER,
1746 p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1747 p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
1748 p_rlmi_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1749 p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
1750 p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1751 ) IS
1752
1753 l_api_version number := 1;
1754 l_api_name CONSTANT VARCHAR2(30) := 'delete_supplier_costs';
1755 l_return_status VARCHAR2(1) := PA_API.G_RET_STS_SUCCESS;
1756 l_msg_count number := 0;
1757 l_msg_data varchar2(2000);
1758 l_error_msg_code varchar2(100):= null;
1759
1760
1761 cursor get_project_id is
1762 select project_id
1763 from pa_control_items
1764 where ci_id = p_ci_id;
1765
1766 l_project_id number;
1767
1768 cursor get_budget_cost_version_id is
1769 SELECT budget_version_id FROM pa_budget_versions
1770 WHERE ci_id = p_ci_id;
1771
1772 l_budget_version_id number;
1773
1774 begin
1775
1776 l_return_status := PA_API.START_ACTIVITY(l_api_name,
1777 p_init_msg_list,
1778 NULL,
1779 x_return_status);
1780 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
1781 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
1782 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
1783 RAISE PA_API.G_EXCEPTION_ERROR;
1784 END IF;
1785
1786 if (p_ci_transaction_id_tbl.count > 0) then
1787 for i in p_ci_transaction_id_tbl.first..p_ci_transaction_id_tbl.last loop
1788 PA_CI_SUPPLIER_PKG.delete_row (p_ci_transaction_id => p_ci_transaction_id_tbl(i));
1789 end loop;
1790
1791 open get_project_id;
1792 fetch get_project_id into l_project_id;
1793 close get_project_id;
1794
1795 open get_budget_cost_version_id;
1796 fetch get_budget_cost_version_id into l_budget_version_id;
1797 close get_budget_cost_version_id;
1798
1799 pa_process_ci_lines_pkg.process_planning_lines(
1800 p_api_version => l_api_version,
1801 p_init_msg_list => FND_API.G_FALSE,
1802 x_return_status => l_return_status,
1803 x_msg_count => l_msg_count,
1804 x_msg_data => l_error_msg_code,
1805 p_calling_context => 'SUPPLIER_COST',
1806 p_action_type => 'DELETE',
1807 p_bvid => l_budget_version_id,
1808 p_ci_id => p_ci_id,
1809 p_line_id_tbl => p_ci_transaction_id_tbl,
1810 p_project_id => l_project_id,
1811 p_task_id_tbl => p_task_id_tbl,
1812 p_currency_code_tbl => p_currency_code_tbl,
1813 p_rlmi_id_tbl => p_rlmi_id_tbl,
1814 p_res_assgn_id_tbl => p_resource_assignment_id_tbl
1815 );
1816
1817 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
1818 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
1819 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
1820 RAISE PA_API.G_EXCEPTION_ERROR;
1821 END IF;
1822
1823 end if;
1824
1825 x_return_status := l_return_status;
1826 PA_API.END_ACTIVITY(x_msg_count, x_msg_data);
1827
1828 EXCEPTION
1829 WHEN PA_API.G_EXCEPTION_ERROR THEN
1830 pa_api.set_message('PA',l_error_msg_code);
1831 x_msg_count := l_msg_count+1;
1832 x_msg_data := l_error_msg_code;
1833 FND_MSG_PUB.Count_And_Get
1834 (
1835 p_count => x_msg_count,
1836 p_data => x_msg_data
1837 );
1838 x_return_status := PA_API.G_RET_STS_ERROR;
1839 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1840 pa_api.set_message('PA',l_error_msg_code);
1841 x_msg_count := l_msg_count+1;
1842 x_msg_data := l_error_msg_code;
1843 FND_MSG_PUB.Count_And_Get
1844 (
1845 p_count => x_msg_count,
1846 p_data => x_msg_data
1847 );
1848 x_return_status := PA_API.G_RET_STS_UNEXP_ERROR;
1849 WHEN OTHERS THEN
1850 x_msg_count := l_msg_count+1;
1851 x_msg_data := l_error_msg_code||sqlerrm;
1852 FND_MSG_PUB.Count_And_Get
1853 (
1854 p_count => x_msg_count,
1855 p_data => x_msg_data
1856 );
1857 x_return_status := PA_API.G_RET_STS_UNEXP_ERROR;
1858
1859 end delete_supplier_costs;
1860
1861
1862 procedure save_supplier_costs(
1863 p_api_version IN NUMBER
1864 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1865 ,x_return_status IN OUT NOCOPY VARCHAR2
1866 ,x_msg_data IN OUT NOCOPY VARCHAR2
1867 ,x_msg_count IN OUT NOCOPY NUMBER
1868 ,p_rowid_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
1869 ,p_ci_transaction_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1870 ,p_ci_id IN NUMBER
1871 ,p_ci_type_id IN NUMBER
1872 ,p_ci_impact_id IN NUMBER DEFAULT NULL
1873 ,p_calling_mode IN VARCHAR2
1874 ,p_org_id IN NUMBER
1875 ,p_version_type IN VARCHAR2 DEFAULT 'ALL'
1876 ,p_record_status_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
1877 ,p_vendor_name_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
1878 ,p_vendor_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1879 ,p_po_number_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
1880 ,p_po_header_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1881 ,p_po_line_num_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
1882 ,p_po_line_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1883 ,p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
1884 ,p_change_amount_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1885 ,p_change_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
1886 ,p_change_description_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
1887 ,p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1888 ,p_resource_list_mem_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1889 ,p_adjusted_transaction_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1890 ,p_from_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
1891 ,p_to_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
1892 ,p_need_by_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
1893 ,p_estimated_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1894 ,p_quoted_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1895 ,p_negotiated_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1896 ,p_burdened_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1897 ,p_revenue_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT NULL
1898 ,p_revenue_override_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1899 ,p_audit_history_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1900 ,p_current_audit_flag_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
1901 ,p_original_supp_trans_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1902 ,p_source_supp_trans_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1903 ,p_sup_ref_no_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT NULL
1904 ,p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
1905 ,p_expenditure_org_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1906 ,p_change_reason_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
1907 ,p_quote_negotiation_ref_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
1908 ,p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1909 )
1910 IS
1911 l_api_version number := 1;
1912 l_api_name CONSTANT VARCHAR2(30) := 'save_supplier_costs';
1913 l_return_status VARCHAR2(1) := PA_API.G_RET_STS_SUCCESS;
1914 l_msg_count number := 0;
1915 l_msg_data varchar2(2000);
1916 l_error_msg_code varchar2(100):= null;
1917
1918 l_rowid varchar2(100);
1919 l_ci_transaction_id number(15);
1920 l_ci_id number(15);
1921 l_ci_type_id number(15);
1922 l_ci_impact_id number(15);
1923 l_vendor_id number(15);
1924 l_po_header_id number(15);
1925 l_po_line_id number(15);
1926 l_change_amount number;
1927 l_currency_code varchar2(15);
1928 l_adjusted_ci_transaction_id number(15);
1929 l_change_description varchar2(2000);
1930 l_change_type varchar2(100);
1931 l_created_by number;
1932 l_creation_date date;
1933 l_last_updated_by number;
1934 l_last_update_date date;
1935 l_last_update_login number;
1936 l_change_approver varchar2(50);
1937 l_audit_history_number number;
1938 l_current_audit_flag varchar2(1);
1939 l_original_supp_trans_id number;
1940 l_source_supp_trans_id number;
1941 l_from_change_date date;
1942 l_to_change_date date;
1943 l_raw_cost number;
1944 l_burdened_cost number;
1945 l_revenue_rate number;
1946 l_revenue_override_rate number;
1947 l_revenue number;
1948 l_total_revenue number;
1949 l_sup_quote_ref_no number;
1950 l_task_id number(15);
1951 l_resource_list_member_id number(15);
1952 l_estimated_cost number;
1953 l_quoted_cost number;
1954 l_negotiated_cost number;
1955 -- l_final_cost number;
1956 l_markup_cost number;
1957 l_status varchar2(15);
1958 l_expenditure_org_id number(15);
1959 l_change_reason_code varchar2(30);
1960 l_quote_negotiation_ref varchar2(150);
1961 l_need_by_date date;
1962 l_expenditure_type varchar2(30);
1963 l_resource_assignment_id number;
1964
1965 l_ci_transaction_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1966 l_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1967
1968 cursor get_project_id is
1969 select project_id
1970 from pa_control_items
1971 where ci_id = p_ci_id;
1972
1973 l_project_id number;
1974
1975 cursor get_budget_cost_version_id is
1976 SELECT budget_version_id FROM pa_budget_versions
1977 WHERE ci_id = p_ci_id and version_type IN ('ALL', 'COST');
1978
1979 l_budget_version_id number;
1980
1981 cursor get_budget_update_method is
1982 Select typ.impact_budget_type_code
1983 from pa_ci_types_b typ, pa_control_items items
1984 where items.ci_type_id=typ.ci_type_id and ci_id = p_ci_id;
1985
1986 l_budget_update_method varchar2(30);
1987
1988 cursor get_po_dates(c_po_header_id NUMBER) is
1989 select start_date, end_date from po_headers_all
1990 where po_header_id = c_po_header_id;
1991
1992 l_po_start_date date;
1993 l_po_end_date date;
1994
1995 -- budget impact tables for inserted record
1996 l_ins_count number := 0;
1997 b_ins_task_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1998 b_ins_sc_line_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1999 b_ins_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2000 b_ins_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2001 b_ins_res_assgn_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2002 b_ins_rlmi_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2003 b_ins_currency_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2004
2005 -- budget impact tables for updated record
2006 l_upd_count number := 0;
2007 b_upd_task_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2008 b_upd_sc_line_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2009 b_upd_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2010 b_upd_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2011 b_upd_res_assgn_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2012 b_upd_rlmi_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2013 b_upd_currency_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2014
2015 cursor get_sc_line(c_sc_line_id number) is
2016 select nvl(raw_cost, 0) raw_cost,
2017 from_change_date, to_change_date,
2018 burdened_cost
2019 from pa_ci_supplier_details
2020 where ci_id = p_ci_id
2021 and ci_transaction_id = c_sc_line_id;
2022
2023 sc_line_row get_sc_line%ROWTYPE;
2024
2025 begin
2026 l_return_status := PA_API.START_ACTIVITY(l_api_name,
2027 p_init_msg_list,
2028 NULL,
2029 x_return_status);
2030 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
2031 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
2032 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
2033 RAISE PA_API.G_EXCEPTION_ERROR;
2034 END IF;
2035
2036 -- Get the budget update method of the control item
2037 open get_budget_update_method;
2038 fetch get_budget_update_method into l_budget_update_method;
2039 close get_budget_update_method;
2040
2041 if (p_ci_transaction_id_tbl.count > 0) then
2042 for i in p_ci_transaction_id_tbl.first..p_ci_transaction_id_tbl.last loop
2043
2044 l_rowid := p_rowid_tbl(i);
2045 l_ci_transaction_id := p_ci_transaction_id_tbl(i);
2046 l_ci_id := p_ci_id;
2047 l_ci_type_id := p_ci_type_id;
2048 l_ci_impact_id := p_ci_impact_id;
2049 l_vendor_id := p_vendor_id_tbl(i);
2050 l_po_header_id := p_po_header_id_tbl(i);
2051 l_po_line_id := p_po_line_id_tbl(i);
2052 l_change_amount := p_change_amount_tbl(i);
2053 l_currency_code := p_currency_code_tbl(i);
2054 l_adjusted_ci_transaction_id := p_adjusted_transaction_id_tbl(i);
2055 l_change_description := p_change_description_tbl(i);
2056 l_change_type := p_change_type_tbl(i);
2057 l_audit_history_number := p_audit_history_number_tbl(i);
2058 l_task_id := p_task_id_tbl(i);
2059 l_current_audit_flag := p_current_audit_flag_tbl(i);
2060 l_original_supp_trans_id := p_original_supp_trans_id_tbl(i);
2061 l_source_supp_trans_id := p_source_supp_trans_id_tbl(i);
2062 l_from_change_date := p_from_date_tbl(i);
2063 l_to_change_date := p_to_date_tbl(i);
2064 l_raw_cost := p_change_amount_tbl(i);
2065 l_burdened_cost := p_burdened_cost_tbl(i);
2066 l_revenue_override_rate := p_revenue_override_rate_tbl(i);
2067 l_revenue := p_revenue_tbl(i);
2068 l_sup_quote_ref_no := p_sup_ref_no_tbl(i);
2069 l_resource_list_member_id := p_resource_list_mem_id_tbl(i);
2070 l_estimated_cost := p_estimated_cost_tbl(i);
2071 l_quoted_cost := p_quoted_cost_tbl(i);
2072 l_negotiated_cost := p_negotiated_cost_tbl(i);
2073 l_expenditure_org_id := p_expenditure_org_id_tbl(i);
2074 l_change_reason_code := p_change_reason_code_tbl(i);
2075 l_quote_negotiation_ref := p_quote_negotiation_ref_tbl(i);
2076 l_need_by_date := p_need_by_date_tbl(i);
2077 l_expenditure_type := p_expenditure_type_tbl(i);
2078 l_resource_assignment_id := p_resource_assignment_id_tbl(i);
2079
2080
2081 if (p_record_status_tbl(i) = 'NEW') then
2082
2083 -- if Budget Update Method is 'Cost and Revenue planning' then only
2084 -- validate uniqueness.
2085 if (l_budget_update_method = 'DIRECT_COST_ENTRY') then
2086 -- check for uniqueness of the record
2087 is_record_unique(p_api_version => p_api_version
2088 ,p_init_msg_list => p_init_msg_list
2089 ,x_return_status => l_return_status
2090 ,x_msg_data => x_msg_data
2091 ,x_msg_count => x_msg_count
2092 ,p_ci_id => p_ci_id
2093 ,p_task_id => l_task_id
2094 ,p_resource_list_mem_id => l_resource_list_member_id
2095 ,p_expenditure_type => l_expenditure_type
2096 ,p_currency_code => l_currency_code
2097 ,p_vendor_id => l_vendor_id
2098 ,p_expenditure_org_id => l_expenditure_org_id
2099 ,p_need_by_date => l_need_by_date
2100 ,p_po_line_id => l_po_line_id
2101 ,p_record_status => p_record_status_tbl(i)
2102 ,p_ci_transaction_id => l_ci_transaction_id );
2103
2104 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
2105 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
2106 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
2107 RAISE PA_API.G_EXCEPTION_ERROR;
2108 END IF;
2109 end if;
2110
2111 PA_CI_SUPPLIER_PKG.insert_row (
2112 x_rowid => l_rowid
2113 ,x_ci_transaction_id => l_ci_transaction_id
2114 ,p_CI_TYPE_ID => l_ci_type_id
2115 ,p_CI_ID => l_ci_id
2116 ,p_CI_IMPACT_ID => l_ci_impact_id
2117 ,p_VENDOR_ID => l_vendor_id
2118 ,p_PO_HEADER_ID => l_po_header_id
2119 ,p_PO_LINE_ID => l_po_line_id
2120 ,p_ADJUSTED_TRANSACTION_ID => l_adjusted_ci_transaction_id
2121 ,p_CURRENCY_CODE => l_currency_code
2122 ,p_CHANGE_AMOUNT => l_change_amount
2123 ,p_CHANGE_TYPE => l_change_type
2124 ,p_CHANGE_DESCRIPTION => l_change_description
2125 ,p_CREATED_BY => FND_GLOBAL.login_id
2126 ,p_CREATION_DATE => trunc(sysdate)
2127 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
2128 ,p_LAST_UPDATE_DATE => trunc(sysdate)
2129 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
2130 ,p_Task_Id => l_task_id
2131 ,p_Resource_List_Mem_Id => l_resource_list_member_id
2132 ,p_From_Date => l_from_change_date
2133 ,p_To_Date => l_to_change_date
2134 ,p_Estimated_Cost => l_estimated_cost
2135 ,p_Quoted_Cost => l_quoted_cost
2136 ,p_Negotiated_Cost => l_negotiated_cost
2137 ,p_Burdened_cost => l_burdened_cost
2138 ,p_Revenue => l_revenue
2139 ,p_revenue_override_rate => l_revenue_override_rate
2140 ,p_audit_history_number => l_audit_history_number
2141 ,p_current_audit_flag => l_current_audit_flag
2142 ,p_Original_supp_trans_id => l_original_supp_trans_id
2143 ,p_Source_supp_trans_id => l_source_supp_trans_id
2144 ,p_Sup_ref_no => l_sup_quote_ref_no
2145 ,p_version_type => p_version_type
2146 ,p_expenditure_type => l_expenditure_type
2147 ,p_expenditure_org_id => l_expenditure_org_id
2148 ,p_change_reason_code => l_change_reason_code
2149 ,p_quote_negotiation_reference => l_quote_negotiation_ref
2150 ,p_need_by_date => l_need_by_date
2151 ,x_return_status => l_return_status
2152 ,x_error_msg_code => l_error_msg_code );
2153
2154 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
2155 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
2156 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
2157 RAISE PA_API.G_EXCEPTION_ERROR;
2158 END IF;
2159
2160 -- populate the budget impact tables
2161
2162 b_ins_sc_line_id_tbl.extend(1);
2163 b_ins_task_id_tbl.extend(1);
2164 b_ins_currency_code_tbl.extend(1);
2165 b_ins_rlmi_id_tbl.extend(1);
2166 b_ins_res_assgn_id_tbl.extend(1);
2167 b_ins_quantity_tbl.extend(1);
2168 b_ins_raw_cost_tbl.extend(1);
2169
2170 l_ins_count := l_ins_count + 1 ;
2171 b_ins_task_id_tbl(l_ins_count) := l_task_id;
2172 b_ins_sc_line_id_tbl(l_ins_count) := l_ci_transaction_id;
2173 b_ins_quantity_tbl(l_ins_count) := NULL; -- Quantity is null for Supplier cost
2174 b_ins_raw_cost_tbl(l_ins_count) := l_change_amount;
2175 b_ins_res_assgn_id_tbl(l_ins_count) := l_resource_assignment_id;
2176 b_ins_rlmi_id_tbl(l_ins_count) := l_resource_list_member_id;
2177 b_ins_currency_code_tbl(l_ins_count) := l_currency_code;
2178
2179 elsif (p_record_status_tbl(i) = 'CHANGED') then
2180
2181 -- if Budget Update Method is 'Cost and Revenue planning' then only
2182 -- validate uniqueness.
2183 if (l_budget_update_method = 'DIRECT_COST_ENTRY') then
2184 -- check for uniqueness of the record
2185 is_record_unique(p_api_version => p_api_version
2186 ,p_init_msg_list => p_init_msg_list
2187 ,x_return_status => l_return_status
2188 ,x_msg_data => x_msg_data
2189 ,x_msg_count => x_msg_count
2190 ,p_ci_id => p_ci_id
2191 ,p_task_id => l_task_id
2192 ,p_resource_list_mem_id => l_resource_list_member_id
2193 ,p_expenditure_type => l_expenditure_type
2194 ,p_currency_code => l_currency_code
2195 ,p_vendor_id => l_vendor_id
2196 ,p_expenditure_org_id => l_expenditure_org_id
2197 ,p_need_by_date => l_need_by_date
2198 ,p_po_line_id => l_po_line_id
2199 ,p_record_status => p_record_status_tbl(i)
2200 ,p_ci_transaction_id => l_ci_transaction_id );
2201
2202 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
2203 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
2204 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
2205 RAISE PA_API.G_EXCEPTION_ERROR;
2206 END IF;
2207 end if;
2208
2209 -- populate the budget impact tables
2210 open get_sc_line(l_ci_transaction_id);
2211 fetch get_sc_line into sc_line_row;
2212 close get_sc_line;
2213
2214 if (sc_line_row.from_change_date <> l_from_change_date or
2215 sc_line_row.to_change_date <> l_to_change_date or
2216 sc_line_row.raw_cost <> l_change_amount ) then
2217
2218 b_upd_sc_line_id_tbl.extend(1);
2219 b_upd_task_id_tbl.extend(1);
2220 b_upd_currency_code_tbl.extend(1);
2221 b_upd_rlmi_id_tbl.extend(1);
2222 b_upd_res_assgn_id_tbl.extend(1);
2223 b_upd_quantity_tbl.extend(1);
2224 b_upd_raw_cost_tbl.extend(1);
2225
2226 l_upd_count := l_upd_count + 1 ;
2227 b_upd_task_id_tbl(l_upd_count) := l_task_id;
2228 b_upd_sc_line_id_tbl(l_upd_count) := l_ci_transaction_id;
2229 b_upd_quantity_tbl(l_upd_count) := NULL; -- Quantity is null for Supplier cost
2230 b_upd_raw_cost_tbl(l_upd_count) := l_change_amount;
2231 b_upd_res_assgn_id_tbl(l_upd_count) := l_resource_assignment_id;
2232 b_upd_rlmi_id_tbl(l_upd_count) := l_resource_list_member_id;
2233 b_upd_currency_code_tbl(l_upd_count) := l_currency_code;
2234
2235 -- for bug 9840053: calculate the burdened_cost and pass on to the update_row()
2236 if sc_line_row.raw_cost <> l_change_amount and sc_line_row.raw_cost <> 0 then
2237 if sc_line_row.burdened_cost is not null then
2238 l_burdened_cost := (sc_line_row.burdened_cost/sc_line_row.raw_cost) * l_change_amount;
2239 end if;
2240 end if;
2241
2242 end if;
2243
2244 PA_CI_SUPPLIER_PKG.update_row (
2245 p_rowid => l_rowid
2246 ,p_ci_transaction_id => l_ci_transaction_id
2247 ,p_CI_TYPE_ID => l_ci_type_id
2248 ,p_CI_ID => l_ci_id
2249 ,p_CI_IMPACT_ID => l_ci_impact_id
2250 ,p_VENDOR_ID => l_vendor_id
2251 ,p_PO_HEADER_ID => l_po_header_id
2252 ,p_PO_LINE_ID => l_po_line_id
2253 ,p_ADJUSTED_TRANSACTION_ID => l_adjusted_ci_transaction_id
2254 ,p_CURRENCY_CODE => l_currency_code
2255 ,p_CHANGE_AMOUNT => l_change_amount
2256 ,p_CHANGE_TYPE => l_change_type
2257 ,p_CHANGE_DESCRIPTION => l_change_description
2258 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
2259 ,p_LAST_UPDATE_DATE => trunc(sysdate)
2260 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
2261 ,p_Task_Id => l_task_id
2262 ,p_Resource_List_Mem_Id => l_resource_list_member_id
2263 ,p_From_Date => l_from_change_date
2264 ,p_To_Date => l_to_change_date
2265 ,p_Estimated_Cost => l_estimated_cost
2266 ,p_Quoted_Cost => l_quoted_cost
2267 ,p_Negotiated_Cost => l_negotiated_cost
2268 ,p_Burdened_cost => l_burdened_cost
2269 ,p_Revenue => l_revenue
2270 ,p_revenue_override_rate => l_revenue_override_rate
2271 ,p_audit_history_number => l_audit_history_number
2272 ,p_current_audit_flag => l_current_audit_flag
2273 ,p_Original_supp_trans_id => l_original_supp_trans_id
2274 ,p_Source_supp_trans_id => l_source_supp_trans_id
2275 ,p_Sup_ref_no => l_sup_quote_ref_no
2276 ,p_version_type => p_version_type
2277 ,p_expenditure_type => l_expenditure_type
2278 ,p_expenditure_org_id => l_expenditure_org_id
2279 ,p_change_reason_code => l_change_reason_code
2280 ,p_quote_negotiation_reference => l_quote_negotiation_ref
2281 ,p_need_by_date => l_need_by_date
2282 ,x_return_status => l_return_status
2283 ,x_error_msg_code => l_error_msg_code );
2284
2285 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
2286 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
2287 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
2288 RAISE PA_API.G_EXCEPTION_ERROR;
2289 END IF;
2290
2291 end if;
2292 l_ci_transaction_id_tbl.extend(1);
2293 l_ci_transaction_id_tbl(i) := l_ci_transaction_id;
2294 -- l_quantity_tbl.extend(1);
2295 -- l_quantity_tbl(i) := NULL; --Quantity will be null for supplier cost
2296 end loop;
2297 end if;
2298
2299 -- Get the budget update method of the control item
2300 open get_budget_update_method;
2301 fetch get_budget_update_method into l_budget_update_method;
2302 close get_budget_update_method;
2303
2304 -- if Budget Update Method is 'Cost and Revenue planning' then only
2305 -- Create budget impacts.
2306 if (l_budget_update_method = 'DIRECT_COST_ENTRY') then
2307 --if (l_ci_transaction_id_tbl.count > 0) then
2308 open get_project_id;
2309 fetch get_project_id into l_project_id;
2310 close get_project_id;
2311
2312 open get_budget_cost_version_id;
2313 fetch get_budget_cost_version_id into l_budget_version_id;
2314 close get_budget_cost_version_id;
2315
2316
2317 if (l_ins_count > 0 ) then
2318 pa_process_ci_lines_pkg.process_planning_lines(
2319 p_api_version => l_api_version,
2320 p_init_msg_list => FND_API.G_FALSE,
2321 x_return_status => l_return_status,
2322 x_msg_count => l_msg_count,
2323 x_msg_data => l_msg_data,
2324 p_calling_context => 'SUPPLIER_COST',
2325 p_action_type => 'INSERT',
2326 p_bvid => l_budget_version_id,
2327 p_ci_id => p_ci_id,
2328 p_line_id_tbl => b_ins_sc_line_id_tbl,
2329 p_project_id => l_project_id,
2330 p_task_id_tbl => b_ins_task_id_tbl,
2331 p_currency_code_tbl => b_ins_currency_code_tbl,
2332 p_rlmi_id_tbl => b_ins_rlmi_id_tbl,
2333 p_res_assgn_id_tbl => b_ins_res_assgn_id_tbl,
2334 p_quantity_tbl => b_ins_quantity_tbl, -- this will be null for supplier cost
2335 p_raw_cost_tbl => b_ins_raw_cost_tbl
2336 );
2337
2338 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
2339 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
2340 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
2341 RAISE PA_API.G_EXCEPTION_ERROR;
2342 END IF;
2343
2344 -- update resource assignment id, effective dates and
2345 -- burdened cost in supplier details table after
2346 -- processing the supplier records and createing/updating
2347 -- resource assignments
2348 forall i in b_ins_task_id_tbl.first..b_ins_task_id_tbl.last
2349 update pa_ci_supplier_details pcsc
2350 set (resource_assignment_id, FROM_CHANGE_DATE,
2351 TO_CHANGE_DATE, burdened_cost) =
2352 (select prac.resource_assignment_id,
2353 decode(pcsc.FROM_CHANGE_DATE,
2354 null,pra.planning_start_date, pcsc.FROM_CHANGE_DATE),
2355 decode(pcsc.TO_CHANGE_DATE,
2356 null, pra.planning_end_date, pcsc.TO_CHANGE_DATE),
2357 pcsc.raw_cost * prac.txn_average_burden_cost_rate
2358 from pa_resource_assignments pra, pa_resource_asgn_curr prac
2359 where pra.budget_version_id = l_budget_version_id
2360 and pra.task_id = pcsc.task_id
2361 and pra.resource_list_member_id = pcsc.resource_list_member_id
2362 and prac.txn_currency_code = pcsc.currency_code
2363 and prac.resource_assignment_id = pra.resource_assignment_id)
2364 where ci_id = p_ci_id
2365 and source_supp_trans_id = b_ins_sc_line_id_tbl(i);
2366 end if;
2367
2368 if (l_upd_count > 0 ) then
2369 pa_process_ci_lines_pkg.process_planning_lines(
2370 p_api_version => l_api_version,
2371 p_init_msg_list => FND_API.G_FALSE,
2372 x_return_status => l_return_status,
2373 x_msg_count => l_msg_count,
2374 x_msg_data => l_msg_data,
2375 p_calling_context => 'SUPPLIER_COST',
2376 p_action_type => 'UPDATE',
2377 p_bvid => l_budget_version_id,
2378 p_ci_id => p_ci_id,
2379 p_line_id_tbl => b_upd_sc_line_id_tbl,
2380 p_project_id => l_project_id,
2381 p_task_id_tbl => b_upd_task_id_tbl,
2382 p_currency_code_tbl => b_upd_currency_code_tbl,
2383 p_rlmi_id_tbl => b_upd_rlmi_id_tbl,
2384 p_res_assgn_id_tbl => b_upd_res_assgn_id_tbl,
2385 p_quantity_tbl => b_upd_quantity_tbl, -- this will be null for supplier cost
2386 p_raw_cost_tbl => b_upd_raw_cost_tbl
2387 );
2388
2389 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
2390 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
2391 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
2392 RAISE PA_API.G_EXCEPTION_ERROR;
2393 END IF;
2394
2395 -- update resource assignment id, effective dates and
2396 -- burdened cost in supplier details table after
2397 -- processing the supplier records and createing/updating
2398 -- resource assignments
2399 forall i in b_upd_task_id_tbl.first..b_upd_task_id_tbl.last
2400 update pa_ci_supplier_details pcsc
2401 set (resource_assignment_id, FROM_CHANGE_DATE,
2402 TO_CHANGE_DATE, burdened_cost) =
2403 (select prac.resource_assignment_id,
2404 decode(pcsc.FROM_CHANGE_DATE,
2405 null,pra.planning_start_date, pcsc.FROM_CHANGE_DATE),
2406 decode(pcsc.TO_CHANGE_DATE,
2407 null, pra.planning_end_date, pcsc.TO_CHANGE_DATE),
2408 pcsc.raw_cost * prac.txn_average_burden_cost_rate
2409 from pa_resource_assignments pra, pa_resource_asgn_curr prac
2410 where pra.budget_version_id = l_budget_version_id
2411 and pra.task_id = pcsc.task_id
2412 and pra.resource_list_member_id = pcsc.resource_list_member_id
2413 and prac.txn_currency_code = pcsc.currency_code
2414 and prac.resource_assignment_id = pra.resource_assignment_id)
2415 where ci_id = p_ci_id
2416 and source_supp_trans_id = b_upd_sc_line_id_tbl(i);
2417
2418 end if;
2419
2420 --end if;
2421 end if;
2422
2423 PA_API.END_ACTIVITY(x_msg_count,x_msg_data);
2424
2425 EXCEPTION
2426 WHEN PA_API.G_EXCEPTION_ERROR THEN
2427 pa_api.set_message('PA',x_msg_data);
2428 x_msg_count := l_msg_count+1;
2429 x_msg_data := x_msg_data;
2430 FND_MSG_PUB.Count_And_Get
2431 (
2432 p_count => x_msg_count,
2433 p_data => x_msg_data
2434 );
2435 x_return_status := PA_API.G_RET_STS_ERROR;
2436 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2437 pa_api.set_message('PA',x_msg_data);
2438 x_msg_count := l_msg_count+1;
2439 x_msg_data := x_msg_data;
2440 FND_MSG_PUB.Count_And_Get
2441 (
2442 p_count => x_msg_count,
2443 p_data => x_msg_data
2444 );
2445 x_return_status := PA_API.G_RET_STS_UNEXP_ERROR;
2446 WHEN OTHERS THEN
2447 x_msg_count := l_msg_count+1;
2448 x_msg_data := x_msg_data||sqlerrm;
2449 FND_MSG_PUB.Count_And_Get
2450 (
2451 p_count => x_msg_count,
2452 p_data => x_msg_data
2453 );
2454 x_return_status := PA_API.G_RET_STS_UNEXP_ERROR;
2455
2456 end save_supplier_costs;
2457
2458 -- gboomina added for 12.1.3 supplier cost requirement - end
2459
2460 END PA_CI_SUPPLIER_UTILS;