1 PACKAGE BODY PO_CONTERMS_UTL_GRP AS
2 /* $Header: POXGCTUB.pls 120.25.12020000.5 2013/04/17 06:43:51 mabaig ship $ */
3
4 -- Initialize debug variables
5 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
6 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.' ;
7
8
9 FUNCTION get_po_header_id(p_draft_id NUMBER) RETURN NUMBER
10 IS
11 l_po_header_id NUMBER:=0;
12 BEGIN
13 SELECT po_header_id
14 INTO l_po_header_id
15 FROM po_headers_draft_all
16 WHERE draft_id = p_draft_id
17 AND rownum < 2;
18 RETURN(l_po_header_id);
19 EXCEPTION
20 WHEN OTHERS THEN
21 RETURN(0);
22 END;
23
24 -------------------------------------------------------------------------------
25 --Start of Comments
26 --Name: is_contracts_enabled
27 --Pre-reqs:
28 -- None.
29 --Modifies:
30 -- None.
31 --Locks:
32 -- None.
33 --Function:
34 -- This function determines if Contracts is enabled or not.
35 --Parameters:
36 -- None
37 --Returns:
38 -- FND_API.G_TRUE if Procurement Contracts is enabled
39 -- FND_API.G_FALSE if Procurement Contracts is disabled.
40 --Testing:
41 --
42 --End of Comments
43 -------------------------------------------------------------------------------
44
45 FUNCTION is_contracts_enabled RETURN VARCHAR2 IS
46
47 BEGIN
48
49 -- read the global variable that stores the profile option.
50 IF (g_contracts_enabled = 'Y') THEN
51 RETURN FND_API.G_TRUE;
52 ELSE
53 RETURN FND_API.G_FALSE;
54 END IF;
55
56 EXCEPTION
57 WHEN OTHERS THEN
58 RAISE;
59 END is_contracts_enabled;
60
61 -------------------------------------------------------------------------------
62 --Start of Comments
63 --Name: is_contracts_enabled
64 --Pre-reqs:
65 -- None.
66 --Modifies:
67 -- None.
68 --Locks:
69 -- None.
70 --Function:
71 -- This procedure determines if Contracts is enabled or not.
72 --Parameters:
73 --IN:
74 --p_init_msg_list
75 -- True/False parameter to initialize message list
76 --p_api_version
77 -- API version
78 --OUT:
79 --x_msg_count
80 -- Message count
81 --x_msg_data
82 -- message data
83 --x_return_status
84 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
85 --x_contracts_enabled
86 -- FND_API.G_TRUE if Procurement Contracts is enabled
87 -- FND_API.G_FALSE if Procurement Contracts is disabled.
88 --Testing:
89 --
90 --End of Comments
91 -------------------------------------------------------------------------------
92
93 PROCEDURE is_contracts_enabled
94 (p_api_version IN NUMBER --bug4028805
95 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
96 ,x_return_status OUT NOCOPY VARCHAR2
97 ,x_msg_count OUT NOCOPY NUMBER
98 ,x_msg_data OUT NOCOPY VARCHAR2
99 ,x_contracts_enabled OUT NOCOPY VARCHAR2) IS
100
101 -- declare local variables
102 l_api_name CONSTANT VARCHAR2(30) := 'is_contracts_enabled';
103 l_api_version CONSTANT NUMBER := 1.0; --bug 4028805
104
105 BEGIN
106 IF NOT (FND_API.compatible_api_call(l_api_version
107 ,p_api_version
108 ,l_api_name
109 ,g_pkg_name)) THEN
110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 END IF;
112
113 -- initialize API return status to success
114 x_return_status:= FND_API.G_RET_STS_SUCCESS;
115
116 -- initialize meesage list
117 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
118 FND_MSG_PUB.initialize;
119 END IF;
120
121 x_contracts_enabled := is_contracts_enabled;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
126 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
127 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
128 IF (g_fnd_debug='Y') THEN
129 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
130 FND_LOG.string(log_level => FND_LOG.level_unexpected
131 ,module => g_module_prefix ||l_api_name
132 ,message => SQLERRM);
133 END IF;
134 END IF;
135 END IF;
136 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
137 ,p_data => x_msg_data);
138 END is_contracts_enabled;
139
140
141 -------------------------------------------------------------------------------
142 --Start of Comments
143 --Name: GET_PO_CONTRACT_DOCTYPE
144 --Pre-reqs:
145 -- None.
146 --Modifies:
147 -- None.
148 --Locks:
149 -- None.
150 --Function:
151 -- Returns the Contract document type to be used for a purchase order
152 --Parameters:
153 --IN:
154 --p_sub_doc_type
155 -- The sub document type of Purchase Order
156 --Returns:
157 -- Contract document type to be used with the call
158 --Testing:
159 --
160 --End of Comments
161 -------------------------------------------------------------------------------
162 FUNCTION Get_Po_Contract_Doctype(p_sub_doc_type IN VARCHAR2) RETURN VARCHAR2 IS
163 BEGIN
164 IF (p_sub_doc_type = 'STANDARD') THEN
165 RETURN 'PO_STANDARD';
166 ELSIF (p_sub_doc_type = 'BLANKET') THEN
167 RETURN 'PA_BLANKET';
168 ELSIF (p_sub_doc_type = 'CONTRACT') THEN
169 RETURN 'PA_CONTRACT';
170 ELSE
171 RETURN NULL;
172 END IF;
173
174 END Get_Po_Contract_Doctype;
175
176
177 -------------------------------------------------------------------------------
178 --Start of Comments
179 --Name: GET_PO_CONTRACT_DOCTYPE_MOD
180 --Pre-reqs:
181 -- None.
182 --Modifies:
183 -- None.
184 --Locks:
185 -- None.
186 --Function:
187 -- Returns the Contract document type to be used for a purchase order
188 --Parameters:
189 --IN:
190 --p_sub_doc_type
191 -- The sub document type of Purchase Order
192 --Returns:
193 -- Contract document type for Modification to be used with the call
194 --Testing:
195 --
196 --End of Comments
197 -------------------------------------------------------------------------------
198 --CLM Mod
199 FUNCTION Get_Po_Contract_Doctype_Mod(p_sub_doc_type IN VARCHAR2) RETURN VARCHAR2 IS
200 BEGIN
201 IF (p_sub_doc_type = 'STANDARD') THEN
202 RETURN 'PO_STANDARD_MOD';
203 ELSIF (p_sub_doc_type = 'BLANKET') THEN
204 RETURN 'PA_BLANKET_MOD';
205 ELSIF (p_sub_doc_type = 'CONTRACT') THEN
206 RETURN 'PA_CONTRACT_MOD';
207 ELSE
208 RETURN NULL;
209 END IF;
210
211 END Get_Po_Contract_Doctype_Mod;
212
213
214
215 -------------------------------------------------------------------------------
216 --Start of Comments
217 --Name: get_external_userlist
218 --Pre-reqs:
219 -- None.
220 --Modifies:
221 -- None.
222 --Locks:
223 -- None.
224 --Function:
225 -- This procedure is built as a wrapper over procedure get_external_userlist
226 -- in the new group API po_vendors_grp.
227 -- This procedure is called by Contracts API to determine the supplier users
228 -- to send notifications to, when deliverables undergo a status change
229 -- (example: it is overdue).
230 --Parameters:
231 --IN:
232 --p_document_id
233 -- PO header ID
234 --p_document_type
235 -- Contracts business document type ex: PA_BLANKET or PO_STANDARD
236 -- This will be parsed to retrieve the PO document type
237 --p_external_contact_id
238 -- supplier contact ID on a contract deliverable. Default is NULL
239 --p_init_msg_list
240 -- True/False parameter to initialize message list
241 --p_api_version
242 -- API version
243 --OUT:
244 --x_msg_count
245 -- Message count
246 --x_msg_data
247 -- message data
248 --x_return_status
249 -- FND_API.G_RET_STS_ERROR - for expected error
250 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
251 -- FND_API.G_RET_STS_SUCCESS - for success
252 --x_supplier_userlist
253 -- PL/SQL table of supplier user names
254 --Notes:
255 -- SAHEGDE 07/18/2003
256 -- This procedure calls get_external_userlist in PO_VENDORS_GRP to
257 -- retrieve supplier user names as VARCHAR2 as well as PL/SQL table, besides
258 -- other OUT parameters. Going forward, signature of the get_external_userlist
259 -- might change to return only PL/SQL table. The callout then will need to
260 -- accomodate this change. This however will not change the GRP API signature.
261 --Testing:
262 --
263 --End of Comments
264 -------------------------------------------------------------------------------
265
266 PROCEDURE get_external_userlist
267 (p_api_version IN NUMBER --bug4028805
268 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
269 ,p_document_id IN NUMBER
270 ,p_document_type IN VARCHAR2
271 ,p_external_contact_id IN NUMBER DEFAULT NULL
272 ,x_return_status OUT NOCOPY VARCHAR2
273 ,x_msg_count OUT NOCOPY NUMBER
274 ,x_msg_data OUT NOCOPY VARCHAR2
275 ,x_external_user_tbl OUT NOCOPY external_user_tbl_type) IS
276
277
278 -- declare local variables
279 l_api_name CONSTANT VARCHAR2(30) := 'get_external_userlist';
280 l_api_version CONSTANT NUMBER := 1.0; --bug4028805
281 l_document_type po_headers.type_lookup_code%TYPE;
282 l_document_id NUMBER;
283 l_return_status VARCHAR2(1);
284 l_external_user_tbl external_user_tbl_type;
285
286
287 BEGIN
288 IF NOT (FND_API.compatible_api_call(l_api_version
289 ,p_api_version
290 ,l_api_name
291 ,g_pkg_name)) THEN
292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
293 END IF;
294
295 -- initialize API return status to success
296 x_return_status:= FND_API.G_RET_STS_SUCCESS;
297
298 -- initialize meesage list
299 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
300 FND_MSG_PUB.initialize;
301 END IF;
302
303 -- parse the contracts document type to type lookup
304 l_document_type := SUBSTR(p_document_type, 1, 2);
305 /* Mod changes */
306 /* Get the po_header_id from the draft table using the
307 draft_id */
308 IF p_document_type = 'PO_STANDARD_MOD' OR
309 p_document_type = 'PA_BLANKET_MOD' OR --<Bug 15834898>
310 p_document_type = 'PA_CONTRACT_MOD' THEN --<Bug 15834898>
311 l_document_id := get_po_header_id(p_document_id);
312 ELSE
313 l_document_id := p_document_id;
314 END IF;
315 /* Mod changes */
316
317
318 po_vendors_grp.get_external_userlist
319 (p_api_version => 1.0 --bug4028805
320 ,p_document_id => l_document_id
321 ,p_document_type => l_document_type
322 ,p_external_contact_id => p_external_contact_id
323 ,x_return_status => l_return_status
324 ,x_msg_count => x_msg_count
325 ,x_msg_data => x_msg_data
326 ,x_external_user_tbl => l_external_user_tbl);
327
328
329 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 END IF;
332
333 -- populate the out parameter. Contracts need comma delimited list of users.
334 x_external_user_tbl := l_external_user_tbl;
335
336 EXCEPTION
337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
340 ,p_data => x_msg_data);
341 WHEN OTHERS THEN
342 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
343 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
344 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
345 IF (g_fnd_debug='Y') THEN
346 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
347 FND_LOG.string(log_level => FND_LOG.level_unexpected
348 ,module => g_module_prefix ||l_api_name
349 ,message => SQLERRM);
350 END IF;
351 END IF;
352 END IF;
353 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
354 ,p_data => x_msg_data);
355 END get_external_userlist;
356
357 -------------------------------------------------------------------------------
358 --Start of Comments
359 --Name: get_external_userlist
360 --Pre-reqs:
361 -- None.
362 --Modifies:
363 -- None.
364 --Locks:
365 -- None.
366 --Function:
367 -- This procedure is built as a wrapper over procedure get_external_userlist
368 -- in the new group API po_vendors_grp.
369 -- This procedure is called by Contracts team to determine the supplier users
370 -- to send notifications to, when deliverables undergo a status change
371 -- (example: it is overdue) and supplier user is not specified on the deliverable.
372 --Parameters:
373 --IN:
374 --p_document_id
375 -- PO header ID
376 --p_document_type
377 -- Contracts business document type ex: PA_BLANKET or PO_STANDARD
378 -- This will be parsed to retrieve the PO document type
379 --p_external_contact_id
380 -- Supplier contact ID on the deliverable. Default is null.
381 --p_init_msg_list
382 -- True/False parameter to initialize message list
383 --p_api_version
384 -- API version
385 --OUT:
386 --x_msg_count
387 -- Message count
388 --x_msg_data
389 -- message data
390 --x_return_status
391 -- FND_API.G_RET_STS_ERROR - for expected error
392 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
393 -- FND_API.G_RET_STS_SUCCESS - for success
394 --x_supplier_userlist
395 -- Comma delimited list of supplier user names
396 --Notes:
397 -- This is an overloaded API to return the supplier names in a comma delimited
398 -- fashion.
399 --Testing:
400 --
401 --End of Comments
402 -------------------------------------------------------------------------------
403 PROCEDURE get_external_userlist
404 (p_api_version IN NUMBER --bug4028805
405 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
406 ,p_document_id IN NUMBER
407 ,p_document_type IN VARCHAR2
408 ,p_external_contact_id IN NUMBER DEFAULT NULL
409 ,x_return_status OUT NOCOPY VARCHAR2
410 ,x_msg_count OUT NOCOPY NUMBER
411 ,x_msg_data OUT NOCOPY VARCHAR2
412 ,x_external_userlist OUT NOCOPY VARCHAR2) IS
413
414
415 -- declare local variables
416 l_api_name CONSTANT VARCHAR2(30) := 'get_external_userlist';
417 l_api_version CONSTANT NUMBER := 1.0;
418 l_document_type po_headers.type_lookup_code%TYPE;
419 l_document_id NUMBER;
420 l_return_status VARCHAR2(1);
421 l_external_userlist VARCHAR2(2000);
422 l_external_userlist_for_sql VARCHAR2(2000);
423 l_external_user_tbl external_user_tbl_type;
424 l_num_users NUMBER;
425 l_vendor_id NUMBER;
426
427
428
429 BEGIN
430 IF NOT (FND_API.compatible_api_call(l_api_version
431 ,p_api_version
432 ,l_api_name
433 ,g_pkg_name)) THEN
434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
435 END IF;
436
437 -- initialize API return status to success
438 x_return_status:= FND_API.G_RET_STS_SUCCESS;
439
440 -- initialize meesage list
441 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
442 FND_MSG_PUB.initialize;
443 END IF;
444
445 -- parse the contracts document type to type lookup
446 l_document_type := SUBSTR(p_document_type, 1, 2);
447 /* Mod changes */
448 /* Get the po_header_id from the draft table using the
449 draft_id */
450 IF p_document_type = 'PO_STANDARD_MOD' OR
451 p_document_type = 'PA_BLANKET_MOD' OR --<Bug 15834898>
452 p_document_type = 'PA_CONTRACT_MOD' THEN --<Bug 15834898>
453 l_document_id := get_po_header_id(p_document_id);
454 ELSE
455 l_document_id := p_document_id;
456 END IF;
457 /* Mod changes */
458
459 PO_VENDORS_GRP.get_external_userlist
460 (p_api_version => 1.0 --bug4028805
461 ,p_init_msg_list => FND_API.G_FALSE
462 ,p_document_id => l_document_id
463 ,p_document_type => l_document_type
464 ,p_external_contact_id => p_external_contact_id
465 ,x_return_status => l_return_status
466 ,x_msg_count => x_msg_count
467 ,x_msg_data => x_msg_data
468 ,x_external_user_tbl => l_external_user_tbl
469 ,x_supplier_userlist => l_external_userlist
470 ,x_supplier_userlist_for_sql => l_external_userlist_for_sql
471 ,x_num_users => l_num_users
472 ,x_vendor_id => l_vendor_id);
473
474
475
476 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
478 END IF;
479
480 -- populate the out parameter. Contracts need comma delimited list of users.
481 x_external_userlist := l_external_userlist;
482
483 EXCEPTION
484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
486 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
487 ,p_data => x_msg_data);
488 WHEN OTHERS THEN
489 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
490 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
491 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
492 IF (g_fnd_debug='Y') THEN
493 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
494 FND_LOG.string(log_level => FND_LOG.level_unexpected
495 ,module => g_module_prefix ||l_api_name
496 ,message => SQLERRM);
497 END IF;
498 END IF;
499 END IF;
500 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
501 ,p_data => x_msg_data);
502 END get_external_userlist;
503
504 -------------------------------------------------------------------------------
505 --Start of Comments
506 --Name: get_item_categorylist
507 --Pre-reqs:
508 -- None.
509 --Modifies:
510 -- None.
511 --Locks:
512 -- None.
513 --Function:
514 -- This procedure returns a PL/SQl table of concatenated item category names for
515 -- all PO lines, except for those cancelled.
516 -- It also returns concatenated items in a PL/SQL table
517 --Parameters:
518 --IN:
519 --p_doc_type
520 -- OKC Document type
521 --p_document_id
522 -- PO header ID
523 --p_init_msg_list
524 -- True/False parameter to initialize message list
525 --p_api_version
526 -- API version
527 --OUT:
528 --x_msg_count
529 -- Message count
530 --x_msg_data
531 -- message data
532 --x_return_status
533 -- FND_API.G_RET_STS_ERROR - for expected error
534 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
535 -- FND_API.G_RET_STS_SUCCESS - for success
536 --x_category_tbl
537 -- PL/SQL table of concatenated category names for lines used in the PO
538 --x_item_tbl
539 -- PL/SQL table of concatenated items for the lines used in the PO
540 --Notes:
541 -- SAHEGDE 07/17/2003
542 --Testing:
543 --
544 --End of Comments
545 -------------------------------------------------------------------------------
546
547 PROCEDURE get_item_categorylist
548 (p_api_version IN NUMBER
549 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
550 ,p_doc_type IN VARCHAR2 := NULL -- CLM Mod project
551 ,p_document_id IN NUMBER
552 ,x_return_status OUT NOCOPY VARCHAR2
553 ,x_msg_count OUT NOCOPY NUMBER
554 ,x_msg_data OUT NOCOPY VARCHAR2
555 ,x_category_tbl OUT NOCOPY item_category_tbl_type
556 ,x_item_tbl OUT NOCOPY item_tbl_type) IS
557
558 -- local variables
559 l_api_name CONSTANT VARCHAR2(30) := 'get item categorylist';
560 l_api_version CONSTANT NUMBER := 1.0;
561
562 l_is_mod VARCHAR2(1) := 'N'; -- CLM Mod
563
564 TYPE category_table_type IS TABLE OF
565 mtl_categories_b_kfv.concatenated_segments%TYPE;
566
567 TYPE item_table_type IS TABLE OF
568 mtl_system_items_b_kfv.concatenated_segments%TYPE;
569
570 l_category_tbl category_table_type;
571 l_item_tbl item_table_type;
572
573 BEGIN
574 IF NOT (FND_API.compatible_api_call(l_api_version
575 ,p_api_version
576 ,l_api_name
577 ,g_pkg_name)) THEN
578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
579 END IF;
580
581 -- initialize API return status to success
582 x_return_status:= FND_API.G_RET_STS_SUCCESS;
583
584 -- initialize meesage list
585 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
586 FND_MSG_PUB.initialize;
587 END IF;
588
589 /* Mod changes */
590 IF p_doc_type = 'PO_STANDARD_MOD' OR
591 p_doc_type = 'PA_BLANKET_MOD' OR --<Bug 15834898>
592 p_doc_type = 'PA_CONTRACT_MOD' THEN --<Bug 15834898>
593 l_is_mod := 'Y';
594 END IF;
595 /* Mod changes */
596
597 -- what: for the given document id, bulk collect item categories
598 -- into the PL/SQL table
599 -- why : Contracts needs to validate terms based on categories
600 -- join: po_header_id, category_id
601 IF l_is_mod = 'N' THEN -- CLM Mod
602 SELECT DISTINCT kfv.concatenated_segments
603 BULK COLLECT INTO l_category_tbl
604 FROM po_lines_all pol, mtl_categories_b_kfv kfv
605 WHERE pol.category_id = kfv.category_id
606 AND pol.po_header_id = p_document_id
607 AND NVL(pol.cancel_flag,'N') ='N';
608 ELSE -- CLM Mod
609 SELECT DISTINCT kfv.concatenated_segments
610 BULK COLLECT INTO l_category_tbl
611 FROM po_lines_draft_all pol, mtl_categories_b_kfv kfv
612 WHERE pol.category_id = kfv.category_id
613 AND pol.draft_id = p_document_id
614 AND NVL(pol.delete_flag,'N') ='N';
615 END IF; --l_is_mod = 'N' THEN -- CLM Mod
616
617 -- return null when no data.
618 IF SQL%NOTFOUND THEN
619 NULL;
620 END IF;
621
622 -- what: for the given document id, bulk collect items
623 -- into the PL/SQL table
624 -- why : Contracts needs to validate terms based on items
625 -- join: po_header_id, item_id
626 IF l_is_mod = 'N' THEN -- CLM Mod
627 SELECT DISTINCT kfv.concatenated_segments
628 BULK COLLECT INTO l_item_tbl
629 FROM po_lines_all pol, mtl_system_items_b_kfv kfv
630 WHERE pol.item_id = kfv.inventory_item_id
631 AND pol.po_header_id = p_document_id
632 AND NVL(pol.cancel_flag,'N') ='N';
633 ELSE -- CLM Mod
634 SELECT DISTINCT kfv.concatenated_segments
635 BULK COLLECT INTO l_item_tbl
636 FROM po_lines_draft_all pol, mtl_system_items_b_kfv kfv
637 WHERE pol.item_id = kfv.inventory_item_id
638 AND pol.draft_id = p_document_id
639 AND NVL(pol.delete_flag,'N') ='N';
640 END IF; --l_is_mod = 'N' THEN -- CLM Mod
641
642 -- return null when no data.
643 IF SQL%NOTFOUND THEN
644 NULL;
645 END IF;
646
647
648 -- move the data into Contracts Table types.
649 -- Bug 3293119, Oracle 8i bulk collect limitation
650 x_category_tbl.delete();
651 x_item_tbl.delete();
652
653 IF (l_category_tbl.COUNT > 0) THEN
654 FOR l_index in l_category_tbl.FIRST..l_category_tbl.LAST LOOP
655 x_category_tbl(l_index).category_name := l_category_tbl(l_index);
656 END LOOP;
657 END IF;
658
659 IF (l_item_tbl.COUNT > 0) THEN
660 FOR l_index in l_item_tbl.FIRST..l_item_tbl.LAST loop
661 x_item_tbl(l_index).name := l_item_tbl(l_index);
662 end loop;
663 END IF;
664
665 EXCEPTION
666 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
668 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
669 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
670 IF (g_fnd_debug='Y') THEN
671 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
672 FND_LOG.string(log_level => FND_LOG.level_unexpected
673 ,module => g_module_prefix ||l_api_name
674 ,message => SQLERRM);
675 END IF;
676 END IF;
677 END IF;
678 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
679 ,p_data => x_msg_data);
680
681 END get_item_categorylist;
682
683 -------------------------------------------------------------------------------
684 --Start of Comments
685 --Name: Is_po_update_allowed
686 --Pre-reqs:
687 -- 1.DONOT Use this API from wherever Functional Security Check is needed. This API
688 -- Assumes that the org context is already set
689 -- 2.If the p_lock_flag is set to Y when this API is called, the calling API is expected to commit or
690 -- rollback to release the lock
691 --Modifies:
692 -- None.
693 --Locks:
694 -- PO_headers_all if p_lock_flag parameter is set to Y
695 --Function:
696 -- This procedure is called by Contracts team to determine whether
697 -- the Purchase Order against which terms will be saved is in updatable
698 -- status or not. This API will compare the passed in status and version to
699 -- the current status and version of the PO and if it is same, it will do some extra checks and
700 -- Return the current status and version back to Contracts with the results.
701 --Parameters:
702 --IN:
703 --p_api_version
704 -- Standard Parameter. API version number expected by the caller
705 --p_init_msg_list
706 -- Standard parameter.Initialize message list
707 --p_doc_type
708 -- OKC Document type
709 --p_header_id
710 -- PO header id
711 --p_callout_string
712 -- This string will contain concatenation of following parameters, delimited by comma
713 -- : Status of the PO stored in Calling application
714 -- : Revision Number of the PO stored in calling application
715 -- : Employee id
716 -- Note that the above parameters should always be concatenated in the same order with no extra spaces around
717 -- Status and revision are always expected to be there. For Employee Id pass 'Null' if status is not 'IN PROCESS'
718 --p_lock_flag
719 -- tells whether po_headers_all be locked for the record
720 --OUT:
721 --x_update_allowed
722 -- Returns Y or N depending PO is in updatable status or not
723 --x_msg_count
724 -- Standard parameter.Message count
725 --x_msg_data
726 -- Standard parameter.message data
727 --x_return_status
728 -- Standard parameter. Status Returned to calling API. Possible values are following
729 -- FND_API.G_RET_STS_ERROR - for expected error
730 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
731 -- FND_API.G_RET_STS_SUCCESS - for success
732 --Notes:
733 -- 07/11/2003 smhanda
734 -- 1. This API has been written specifically for integration with contracts wherein Contracts Changes
735 -- are treated as extension to PO Entry form changes of the PO. Before using it in any other place
736 -- Please diagnose the impact including security.
737 -- 2. Though the generic API for Document status check is called in this, right now it is not doing much
738 -- as the only extra check needed from there is for closed code or cancel flag which cannot be changed
739 -- If user is in PO updatable mode. Say the PO was requires reapproval when callout was made. Now to
740 -- set cancel flag on PO it must be in status Approved. In that case the first check itself in this
741 -- API - "Approved <> Requires Reapproval" will fail and the API will return
742 -- But still the call is being kept to cover for any future changes in Document Status check API
743 -- 3. The check for revision is needed at least for one corner case in which while the callout was made
744 -- in revision 0.0 in normal mode but while the changes were saved to Contracts, the revision changed to
745 -- 1.0 . Now the callout should have been made in Amend mode and not in normal mode. So, this API will
746 -- return "Update not allowed"
747 --Testing:
748 -- Test this API by passing various status and revision of PO while changing those for the PO thru UI
749 -- Test for changing the closed code and cancel status
750 -- Test for Inprocess status
751 -- Test for Changing just the revison while keeping the status same
752 --End of Comments
753 -------------------------------------------------------------------------------
754 PROCEDURE IS_po_update_allowed (
755 p_api_version IN NUMBER,
756 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
757 p_doc_type IN VARCHAR2 := NULL, -- CLM Mod
758 p_header_id IN NUMBER,
759 p_callout_string IN VARCHAR2,
760 p_lock_flag IN VARCHAR2 DEFAULT 'N',
761 x_update_allowed OUT NOCOPY VARCHAR2,
762 x_return_status OUT NOCOPY VARCHAR2,
763 x_msg_data OUT NOCOPY VARCHAR2,
764 x_msg_count OUT NOCOPY NUMBER
765 ) IS
766 l_api_name CONSTANT VARCHAR(30) := 'IS_PO_UPDATE_ALLOWED';
767 l_api_version CONSTANT NUMBER := 1.0;
768 l_callout_status PO_HEADERS_ALL.AUTHORIZATION_STATUS%TYPE;
769 l_callout_revision PO_HEADERS_ALL.REVISION_NUM%TYPE;
770 l_po_status PO_HEADERS_ALL.AUTHORIZATION_STATUS%TYPE;
771 l_po_revision PO_HEADERS_ALL.REVISION_NUM%TYPE;
772
773 l_emp_id NUMBER;
774 l_emp VARCHAR2(100);
775 l_doc_type_code PO_DOCUMENT_TYPES_ALL_B.DOCUMENT_SUBTYPE%TYPE;
776 l_document_type PO_DOCUMENT_TYPES_ALL_B.DOCUMENT_TYPE_CODE%TYPE;
777 l_modify_action BOOLEAN;
778 l_start NUMBER;
779 l_end NUMBER;
780 l_Status_changed EXCEPTION;
781 l_DONOT_update EXCEPTION;
782 l_po_status_rec PO_STATUS_REC_TYPE;
783 l_is_mod VARCHAR2(1) := 'N'; -- CLM Mod
784
785 BEGIN
786 If g_fnd_debug = 'Y' then
787 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
788 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
789 MODULE =>g_module_prefix||l_api_name,
790 MESSAGE =>'10: Start' ||l_api_name);
791 END IF;
792 End if;
793
794 -- Standard call to check for call compatibility.
795 IF NOT FND_API.Compatible_API_Call (p_current_version_number=>l_api_version,
796 p_caller_version_number =>p_api_version,
797 p_api_name =>l_api_name,
798 p_pkg_name =>G_PKG_NAME)
799
800 THEN
801 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
802 END IF;
803
804 -- Initialize message list if p_init_msg_list is set to TRUE.
805 IF FND_API.to_Boolean(p_init_msg_list ) THEN
806 FND_MSG_PUB.initialize;
807 END IF;
808
809 -- Initialize API return status to success
810 x_return_status := FND_API.G_RET_STS_SUCCESS;
811 X_update_allowed := 'Y';
812
813 If g_fnd_debug = 'Y' then
814 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
815 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
816 MODULE =>g_module_prefix||l_api_name,
817 MESSAGE =>'50: Do basic validations');
818 END IF;
819 End if;
820
821 -- Basic validations about in parameters
822 IF p_header_id is null then
823 Fnd_message.set_name('PO','PO_VALUE_MISSING');
824 Fnd_message.set_token( token => 'VARIABLE'
825 , VALUE => 'p_header_id');
826 FND_MSG_PUB.Add;
827 x_return_status := FND_API.G_RET_STS_ERROR;
828 RAISE FND_API.G_EXC_ERROR;
829 END IF;
830
831 If p_callout_string is not null then
832 L_start := 1;
833 L_end := instr(p_callout_string,',',1,1)-1;
834 L_callout_status := substr(p_callout_string,l_start,l_end);
835 L_start := l_end+2;
836 L_end := instr(p_callout_string,',',l_start,1)-1;
837 L_callout_revision := to_number(substr(p_callout_string,l_start,(l_end-l_start+1)));
838 L_start := l_end+2;
839 L_emp := substr(p_callout_string,l_start);
840 If l_emp = 'NULL' then
841 L_emp := null;
842 Else
843 l_emp_id := to_number(l_emp);
844 END IF;
845
846 ELSE
847 Fnd_message.set_name('PO','PO_VALUE_MISSING');
848 Fnd_message.set_token( token => 'VARIABLE'
849 , VALUE => 'p_callout_string');
850 FND_MSG_PUB.Add;
851 x_return_status := FND_API.G_RET_STS_ERROR;
852 RAISE FND_API.G_EXC_ERROR;
853
854 END IF;-- p_callout _string
855 If g_fnd_debug = 'Y' then
856 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
857 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
858 MODULE =>g_module_prefix||l_api_name,
859 MESSAGE =>'100: callout status '||l_callout_status);
860 END IF;
861 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
862 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
863 MODULE =>g_module_prefix||l_api_name,
864 MESSAGE =>'110:callout revision '||l_callout_revision);
865 END IF;
866 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
867 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
868 MODULE =>g_module_prefix||l_api_name,
869 MESSAGE =>'120:callout employee '||l_emp_id);
870 END IF;
871 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
872 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
873 MODULE =>g_module_prefix||l_api_name,
874 MESSAGE =>'140:Header id '||p_header_id);
875 END IF;
876 END IF;
877 IF l_callout_status is null then
878 Fnd_message.set_name('PO','PO_VALUE_MISSING');
879 Fnd_message.set_token( token => 'VARIABLE'
880 , VALUE => 'callout_status');
881 FND_MSG_PUB.Add;
882 x_return_status := FND_API.G_RET_STS_ERROR;
883 RAISE FND_API.G_EXC_ERROR;
884 END IF;
885 IF l_callout_revision is null then
886 Fnd_message.set_name('PO','PO_VALUE_MISSING');
887 Fnd_message.set_token( token => 'VARIABLE'
888 , VALUE => 'callout_revision');
889 FND_MSG_PUB.Add;
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 RAISE FND_API.G_EXC_ERROR;
892 END IF;
893 /* Mod changes */
894 IF p_doc_type = 'PO_STANDARD_MOD' OR
895 p_doc_type = 'PA_BLANKET_MOD' OR --<Bug 15834898>
896 p_doc_type = 'PA_CONTRACT_MOD' THEN --<Bug 15834898>
897 l_is_mod := 'Y';
898 END IF;
899 /* Mod changes */
900 BEGIN
901 -- SQL WHAT-get the current status and revision of the PO
902 -- SQL WHY - Needed to compare status and revision with passed in values
903 -- SQL JOIN- PO_header_id
904 IF l_is_mod = 'N' THEN
905 SELECT nvl(authorization_status,'INCOMPLETE'), revision_num, type_lookup_code
906 ,DECODE(type_lookup_code,'STANDARD','PO','BLANKET','PA','CONTRACT','PA',null)
907 INTO l_po_status,l_po_revision, l_doc_type_code
908 ,l_document_type
909 FROM po_headers_all
910 WHERE po_header_id = p_header_id;
911 ELSE -- CLM Mod
912 SELECT nvl(authorization_status,'INCOMPLETE'), revision_num, type_lookup_code
913 ,DECODE(type_lookup_code,'STANDARD','PO','BLANKET','PA','CONTRACT','PA',null)
914 INTO l_po_status,l_po_revision, l_doc_type_code
915 ,l_document_type
916 FROM po_headers_draft_all
917 WHERE draft_id = p_header_id;
918 END IF; -- CLM Mod
919
920 EXCEPTION
921 WHEN NO_DATA_FOUND then
922 Fnd_message.set_name('PO','PO_DOESNOT_EXIST');
923 FND_MSG_PUB.Add;
924 x_return_status := FND_API.G_RET_STS_ERROR;
925 RAISE FND_API.G_EXC_ERROR;
926
927 END;
928
929 IF g_fnd_debug = 'Y' then
930 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
931 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
932 MODULE =>g_module_prefix||l_api_name,
933 MESSAGE =>'200:current status '||l_po_status);
934 END IF;
935 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
936 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
937 MODULE =>g_module_prefix||l_api_name,
938 MESSAGE =>'220:current revision '||l_po_revision);
939 END IF;
940
941 END IF;
942 IF (l_is_mod = 'N') then -- CLM Mod
943 IF (l_po_status = l_callout_status) and (l_po_revision = l_callout_revision) then
944
945 If (l_callout_status in ('IN PROCESS','PRE-APPROVED')) then --- Bug 5606590
946 If l_emp_id is null then
947 Fnd_message.set_name('PO','PO_VALUE_MISSING');
948 Fnd_message.set_token( token => 'VARIABLE'
949 , VALUE => 'emp_id_in_callout_str');
950 FND_MSG_PUB.Add;
951 x_return_status := FND_API.G_RET_STS_ERROR;
952 RAISE FND_API.G_EXC_ERROR;
953 End if;--emp id is null
954 --Check if current user is the current approver for the PO
955 IF g_fnd_debug = 'Y' then
956 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
957 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
958 MODULE =>g_module_prefix||l_api_name,
959 MESSAGE =>'250:doc type '||l_doc_type_code);
960 END IF;
961 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
962 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
963 MODULE =>g_module_prefix||l_api_name,
964 MESSAGE =>'255:before Call to PO_DOCUMENT_CHECKS_GRP.PO_STATUS_CHECK. l_emp_id '||l_emp_id);
965 END IF;
966 END IF;
967 PO_SECURITY_CHECK_SV.Check_before_lock(
968 x_type_lookup_code => l_doc_type_code,
969 x_object_id => p_header_id,
970 x_logged_emp_id => l_emp_id,
971 x_modify_action => l_modify_action
972 );
973 IF g_fnd_debug = 'Y' then
974 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
975 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
976 MODULE =>g_module_prefix||l_api_name,
977 MESSAGE =>'260:After Call to PO_DOCUMENT_CHECKS_GRP.PO_STATUS_CHECK. modify action ');
978 END IF;
979 END IF;
980 --This API suppresses the exception. So if something fails, we won't be able to catch it.
981 IF l_modify_action then
982 IF g_fnd_debug = 'Y' then
983 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
984 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
985 MODULE =>g_module_prefix||l_api_name,
986 MESSAGE =>'270:modify action true ');
987 END IF;
988 END IF;
989
990 ELSE
991 x_update_allowed := 'N';
992 IF g_fnd_debug = 'Y' then
993 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
994 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
995 MODULE =>g_module_prefix||l_api_name,
996 MESSAGE =>'290:modify action false ');
997 END IF;
998 END IF;
999 RAISE l_DONOT_update;
1000
1001 END IF;
1002 END IF; -- po status INPROCESS
1003 IF g_fnd_debug = 'Y' then
1004 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1005 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1006 MODULE =>g_module_prefix||l_api_name,
1007 MESSAGE =>'292:before status check po id -update allowed '||p_header_id||x_update_allowed);
1008 END IF;
1009 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1010 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1011 MODULE =>g_module_prefix||l_api_name,
1012 MESSAGE =>'295:before status check lock '||p_lock_flag);
1013 END IF;
1014 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1015 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1016 MODULE =>g_module_prefix||l_api_name,
1017 MESSAGE =>'297:before status check doc type '||l_document_type);
1018 END IF;
1019 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1020 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1021 MODULE =>g_module_prefix||l_api_name,
1022 MESSAGE =>'299:Before Call to PO_DOCUMENT_CHECKS_GRP.PO_STATUS_CHECK');
1023 END IF;
1024 END IF;
1025 -- call Generic PO Document Checks API for further checks
1026 PO_DOCUMENT_CHECKS_GRP.PO_STATUS_CHECK (
1027 p_api_version => 1.0,
1028 p_header_id => p_header_id,
1029 p_document_type => l_document_type,
1030 p_mode => 'CHECK_UPDATEABLE',
1031 p_lock_flag => p_lock_flag,
1032 x_po_status_rec => l_po_status_rec,
1033 x_return_status => x_return_status
1034 );
1035 IF g_fnd_debug = 'Y' then
1036 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1037 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1038 MODULE =>g_module_prefix||l_api_name,
1039 MESSAGE =>'295:After Call to PO_DOCUMENT_CHECKS_GRP.PO_STATUS_CHECK. Return status '||x_return_status);
1040 END IF;
1041 END IF;
1042 ---Return status handling.
1043 --If any errors happen abort API.
1044 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1045 RAISE FND_API.G_EXC_ERROR;
1046 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1048 END IF;
1049 --PO Status check returns N if document is in status "In Process"
1050 -- But for Terms authoring it is a valid status. So ignore
1051 -- results by PO Status Check in that case because the other flags
1052 -- for which we need to call Po status Check(Firm,cancelled,closed)
1053 -- also are not possible in this status.
1054 -- Bug 4914819: we should also ignore results in a "pre-approved"
1055 -- case as the check will return N for that also but we do allow
1056 -- approver to edit such a PO
1057 IF (l_callout_status not in ('IN PROCESS','PRE-APPROVED')) THEN
1058 x_update_allowed := l_po_status_Rec.updatable_flag(
1059 l_po_status_rec.updatable_flag.FIRST);
1060 END IF;
1061 IF x_update_allowed = 'N' then
1062 RAISE l_DONOT_update;
1063 END IF;
1064 ELSE -- status and revision same
1065 RAISE l_Status_Changed;
1066 END IF; -- if po_status and revision same
1067 /* TODO Status check and Security check for mod*/
1068 END IF; -- (l_is_mod = 'N') then -- CLM Mod
1069 IF g_fnd_debug = 'Y' then
1070 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1071 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1072 MODULE =>g_module_prefix||l_api_name,
1073 MESSAGE =>'400:End '||l_api_name);
1074 END IF;
1075 END IF;
1076 EXCEPTION
1077 WHEN l_STATUS_CHANGED then
1078 x_return_status := FND_API.G_RET_STS_ERROR;
1079 x_update_allowed := 'N';
1080 FND_MESSAGE.set_name('PO', 'PO_STATUS_CHANGED');
1081 FND_MSG_PUB.Add;
1082 FND_MSG_PUB.Count_And_Get
1083 (p_count => x_msg_count,
1084 p_data => x_msg_data );
1085 IF g_fnd_debug = 'Y' then
1086 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1087 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1088 MODULE =>g_module_prefix||l_api_name,
1089 MESSAGE =>'500:Exception l_status_changed ');
1090 END IF;
1091 END IF;
1092
1093
1094 WHEN l_DONOT_UPDATE then
1095 x_return_status := FND_API.G_RET_STS_ERROR;
1096 x_update_allowed := 'N';
1097 FND_MESSAGE.set_name('PO', 'PO_NO_UPDATE_ALLOWED');
1098 FND_MSG_PUB.Add;
1099 FND_MSG_PUB.Count_And_Get
1100 (p_count => x_msg_count,
1101 p_data => x_msg_data );
1102 IF g_fnd_debug = 'Y' then
1103 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1104 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1105 MODULE =>g_module_prefix||l_api_name,
1106 MESSAGE =>'550:Exception l_donot_update ');
1107 END IF;
1108 END IF;
1109
1110 WHEN FND_API.G_EXC_ERROR then
1111 x_return_status := FND_API.G_RET_STS_ERROR;
1112 x_update_allowed := 'N';
1113 FND_MSG_PUB.Count_And_Get
1114 (p_count => x_msg_count,
1115 p_data => x_msg_data );
1116 IF g_fnd_debug = 'Y' then
1117 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1118 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1119 MODULE =>g_module_prefix||l_api_name,
1120 MESSAGE =>'600:Exception Expected error ');
1121 END IF;
1122 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
1123 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1124 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1125 MODULE =>g_module_prefix||l_api_name,
1126 MESSAGE =>'600:errors '||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
1127 END IF;
1128 END LOOP;
1129
1130 END IF;
1131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
1132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1133 x_update_allowed := 'N';
1134 FND_MSG_PUB.Count_And_Get
1135 (p_count => x_msg_count,
1136 p_data => x_msg_data );
1137 IF g_fnd_debug = 'Y' then
1138 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1139 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1140 MODULE =>g_module_prefix||l_api_name,
1141 MESSAGE =>'610:Exception UnExpected error ');
1142 END IF;
1143 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
1144 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1145 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1146 MODULE =>g_module_prefix||l_api_name,
1147 MESSAGE =>'610:errors '||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
1148 END IF;
1149 END LOOP;
1150
1151 END IF;
1152
1153 WHEN OTHERS THEN
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1155 X_update_allowed := 'N';
1156
1157 IF FND_MSG_PUB.Check_Msg_Level
1158 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1159 THEN
1160 FND_MSG_PUB.Add_Exc_Msg
1161 (p_pkg_name => 'PO_CONTERMS_UTL_GRP',
1162 p_procedure_name => l_api_name);
1163 END IF; --msg level
1164 FND_MSG_PUB.Count_And_Get
1165 (p_count => x_msg_count,
1166 p_data => x_msg_data );
1167 IF g_fnd_debug = 'Y' then
1168 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1169 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1170 MODULE =>g_module_prefix||l_api_name,
1171 MESSAGE =>'650:Exception UnExpected error '||sqlcode||':'||sqlerrm);
1172 END IF;
1173 End if;
1174
1175 END is_po_update_allowed;
1176
1177
1178 -------------------------------------------------------------------------------
1179 --Start of Comments
1180 --Name: Apply_template_change
1181 --Pre-reqs:
1182 -- 1.DONOT Use this API from wherever Functional Security Check is needed. This API
1183 -- Assumes that the org context is already set
1184 -- 2.when this API is called, the calling API is expected to commit or
1185 -- rollback to release the lock on Po_headers_all unless p_commit is set to Y
1186 --Modifies:
1187 -- PO_headers_ALL. The following Columns will be modified
1188 -- CONTERMS_EXIST_FLAG
1189 -- CONTERMS_ARTICLES_UPD_DATE
1190 -- CONTERMS_DELIV_UPD_DATE
1191 --Locks:
1192 -- PO_headers_all
1193 --Function:
1194 -- This API will be called by Contracts when user attaches or deletes a template
1195 -- from a purchasing document. This API will first check if Po is in updatable status. If yes
1196 -- It will update the conterms fields in po_headers_all based on the action taken in contracts
1197 -- Contracts will populate parameter p_template_changed to Y when this API is called
1198 -- after a new template is attached to PO. Contracts will populate parameter p_template_changed to D when
1199 -- this API is called after template is removed.
1200
1201 --Parameters:
1202 --IN:
1203 --p_api_version
1204 -- Standard Parameter. API version number expected by the caller
1205 --p_init_msg_list
1206 -- Standard parameter.Initialize message list
1207 --p_doc_type
1208 -- OKC Document type
1209 --p_header_id
1210 -- PO header id
1211 --p_callout_string
1212 -- This string will contain concatenation of following parameters, delimited by comma
1213 -- : Status of the PO stored in Calling application
1214 -- : Revision Number of the PO stored in calling application
1215 -- : Employee id
1216 -- Note that the above parameters should always be concatenated in the same order with no extra spaces around
1217 -- Status and revision are always expected to be there. For Employee Id pass 'Null' if status is not 'IN PROCESS'
1218 --p_template_changed
1219 -- tells Whether this call is being made when a new template was attached
1220 -- or existing template was dropped. Possible values for this parameter are
1221 -- Y: Template was added (or contract source changes to attached document)
1222 -- D: Template was dropped
1223 --OUT:
1224 --x_update_allowed
1225 -- Returns Y or N depending PO is in updatable status or not
1226 --x_msg_count
1227 -- Standard parameter.Message count
1228 --x_msg_data
1229 -- Standard parameter.message data
1230 --x_return_status
1231 -- Standard parameter. Status Returned to calling API. Possible values are following
1232 -- FND_API.G_RET_STS_ERROR - for expected error
1233 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
1234 -- FND_API.G_RET_STS_SUCCESS - for success
1235 --Notes:
1236 -- 07/11/2003 smhanda
1237 -- 1. This API has been written specifically for integration with contracts wherein Contracts Changes
1238 -- are treated as extension to PO Entry form changes of the PO. Before using it in any other place
1239 -- Please diagnose the impact about security and all.
1240 -- 2.Template can only be removed when a PO is in status Incomplete or rejected and PO revision
1241 -- number is 0.
1242 -- This should be enforced by Contracts UI but this API will also enforce that because
1243 -- Contracts Enforces that template can be dropped in Update mode. So, if this
1244 -- API is called in Update mode with Status InProcess, Contracts UI will show the button
1245 -- "Remove Contract Template" but this API will not let user do it as Dropping a template
1246 -- when the PO is "in process" would require that we set the workflow parameter "Procurement Contract"
1247 -- Everytime PO goes from one approver to next. This is a very corner case and functionally
1248 -- Not very viable as an approver should be rejecting the PO rather than making it non procuremet
1249 -- contract from a procurement contract during approval. If there is any valid business
1250 -- requirement during customer implementation rather than what is being mandated here
1251 -- the changes in workflow approval should also be taken care of.
1252 -- 3.Since this API also checks PO updatable status, Contracts should call just this
1253 -- API when template is attached. There is no need to call PO_UPDATE_ALLOWED in this case
1254 --
1255 --Testing:
1256 -- Test this API by passing various status of PO while changing those for the PO thru UI
1257 -- Test for passing various values for p_template_change
1258 --End of Comments
1259 -------------------------------------------------------------------------------
1260 PROCEDURE Apply_template_change (
1261 p_api_version IN NUMBER,
1262 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1263 p_doc_type IN VARCHAR2 := NULL, -- CLM Mod
1264 p_header_id IN NUMBER,
1265 p_callout_string IN VARCHAR2,
1266 p_template_changed IN VARCHAR2,
1267 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1268 x_update_allowed OUT NOCOPY VARCHAR2,
1269 x_return_status OUT NOCOPY VARCHAR2,
1270 x_msg_data OUT NOCOPY VARCHAR2,
1271 x_msg_count OUT NOCOPY NUMBER
1272 ) IS
1273 l_api_name CONSTANT VARCHAR(30) := 'Apply_template_Change';
1274 l_api_version CONSTANT NUMBER := 1.0;
1275 l_update_not_allowed EXCEPTION;
1276 l_date DATE;
1277 l_conterms_exist_flag VARCHAR2(1);
1278
1279 l_callout_status PO_HEADERS_ALL.AUTHORIZATION_STATUS%TYPE;
1280 l_callout_revision PO_HEADERS_ALL.REVISION_NUM%TYPE;
1281 l_old_conterms_flag VARCHAR2(1); -- <11i10+ Contracts ER Migrate PO>
1282 l_start NUMBER;
1283 l_end NUMBER;
1284 l_is_mod VARCHAR2(1) := 'N'; -- CLM Mod
1285 BEGIN
1286 IF g_fnd_debug = 'Y' then
1287 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1288 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1289 MODULE =>g_module_prefix||l_api_name,
1290 MESSAGE =>'10: Start' ||l_api_name);
1291 END IF;
1292 End if;
1293 --Savepoint
1294 SAVEPOINT SP_APPLY_TEMPLATE_CHANGE;
1295 -- Standard call to check for call compatibility.
1296 IF NOT FND_API.Compatible_API_Call (p_current_version_number=>l_api_version,
1297 p_caller_version_number =>p_api_version,
1298 p_api_name =>l_api_name,
1299 p_pkg_name =>G_PKG_NAME)
1300 THEN
1301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1302 END IF;
1303
1304 -- Initialize message list if p_init_msg_list is set to TRUE.
1305 IF FND_API.to_Boolean(p_init_msg_list ) THEN
1306 FND_MSG_PUB.initialize;
1307 END IF;
1308
1309 -- Initialize API return status to success
1310 x_return_status := FND_API.G_RET_STS_SUCCESS;
1311 x_update_allowed := 'N';
1312
1313 IF g_fnd_debug = 'Y' then
1314 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1315 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1316 MODULE =>g_module_prefix||l_api_name,
1317 MESSAGE =>'50: x_update_allowed' ||x_update_allowed);
1318 END IF;
1319 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1320 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1321 MODULE =>g_module_prefix||l_api_name,
1322 MESSAGE =>'70: p_template_changed' ||p_template_changed);
1323 END IF;
1324 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1325 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1326 MODULE =>g_module_prefix||l_api_name,
1327 MESSAGE =>'100: p_callout_string' ||p_callout_string);
1328 END IF;
1329 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1330 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1331 MODULE =>g_module_prefix||l_api_name,
1332 MESSAGE =>'120: p_header_id' ||p_header_id);
1333 END IF;
1334
1335 End if;
1336 /* Mod changes */
1337 IF p_doc_type = 'PO_STANDARD_MOD' OR
1338 p_doc_type = 'PA_BLANKET_MOD' OR --<Bug 15834898>
1339 p_doc_type = 'PA_CONTRACT_MOD' THEN --<Bug 15834898>
1340 l_is_mod := 'Y';
1341 END IF;
1342 /* Mod changes */
1343 IF P_template_changed in ('Y','D') then
1344
1345 -- Business Rule- You cannot remove or attach template if PO is in status
1346 -- other than incomplete or rejected OR PO revision is greater than 0
1347 If p_callout_string is not null then
1348 L_start := 1;
1349 L_end := instr(p_callout_string,',',1,1)-1;
1350 L_callout_status := substr(p_callout_string,l_start,l_end);
1351 L_start := l_end+2;
1352 L_end := instr(p_callout_string,',',l_start,1)-1;
1353 l_callout_revision := to_number(substr(p_callout_string,l_start,(l_end-l_start+1)));
1354
1355 ELSE
1356 Fnd_message.set_name('PO','PO_VALUE_MISSING');
1357 Fnd_message.set_token( token => 'VARIABLE'
1358 , VALUE => 'p_callout_string');
1359 FND_MSG_PUB.Add;
1360 x_return_status := FND_API.G_RET_STS_ERROR;
1361 RAISE FND_API.G_EXC_ERROR;
1362
1363 END IF;-- p_callout _string
1364 IF g_fnd_debug = 'Y' then
1365 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1366 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1367 MODULE =>g_module_prefix||l_api_name,
1368 MESSAGE =>'150: po status should be Incomplete/rejected-'||l_callout_status);
1369 END IF;
1370 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1371 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1372 MODULE =>g_module_prefix||l_api_name,
1373 MESSAGE =>'155: po revision should be 0-'||l_callout_revision);
1374 END IF;
1375 End if;
1376
1377 -- <Start Bug 4007548>: Comment out check
1378 -- See bug for version that needs to be added back in
1379 -- if we allow add/drop template in ammend, this needs to
1380 -- be modified as mentioned in the bug
1381 --
1382 -- IF (l_callout_status NOT IN ('INCOMPLETE','REJECTED', 'APPROVED'))
1383 -- THEN
1384 -- RAISE l_update_not_allowed;
1385 -- END IF;
1386 --
1387 -- <End Bug 4007548>
1388
1389 -- <11i10+ Migrate PO Start>
1390 -- Added the following condition to prevent adding/dropping conterms
1391 -- if the approver comes in between the workflow process. The message
1392 -- is also modified accordingly
1393 IF (l_callout_status IN ('PRE-APPROVED','IN PROCESS'))
1394 THEN
1395
1396 IF l_is_mod = 'N' THEN -- CLM Mod
1397 SELECT poh.conterms_exist_flag
1398 INTO l_old_conterms_flag
1399 FROM PO_HEADERS_ALL poh
1400 WHERE poh.po_header_id = p_header_id;
1401 ELSE -- CLM Mod
1402 SELECT poh.conterms_exist_flag
1403 INTO l_old_conterms_flag
1404 FROM PO_HEADERS_DRAFT_ALL poh
1405 WHERE poh.draft_id = p_header_id
1406 AND rownum <2;
1407 END IF; -- CLM Mod
1408
1409 --12934631 add NVL() function to handle the null value when create a
1410 --new docutment
1411 IF ((NVL(l_old_conterms_flag, 'N') = 'N') AND (p_template_changed = 'Y'))
1412 OR ((l_old_conterms_flag = 'Y') AND (p_template_changed = 'D'))
1413 THEN
1414 RAISE l_update_not_allowed;
1415 END IF;
1416 END IF;
1417 -- <11i10+ Migrate PO End>
1418
1419 IF l_is_mod = 'N' THEN -- CLM Mod
1420 -- call po update allowed to check PO status
1421 IS_PO_UPDATE_ALLOWED (
1422 p_api_version => p_api_version,
1423 p_doc_type => p_doc_type,
1424 p_header_id => p_header_id,
1425 p_callout_string => p_callout_string,
1426 p_lock_flag => 'Y',
1427 x_update_allowed => x_update_allowed,
1428 x_return_status => x_return_status,
1429 x_msg_data => x_msg_data,
1430 x_msg_count => x_msg_count);
1431 ---Return status handling.
1432 --If any errors happen abort API.
1433 ELSE
1434 /* TODO Status check and Security check for mod*/
1435 x_update_allowed := 'Y';
1436 END IF; -- l_is_mod = 'N' THEN -- CLM Mod
1437 IF g_fnd_debug = 'Y' then
1438 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1439 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1440 MODULE =>g_module_prefix||l_api_name,
1441 MESSAGE =>'200:update allowed after calling is_po_update_allowed'||x_update_allowed);
1442 END IF;
1443 End if;
1444 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1445 RAISE FND_API.G_EXC_ERROR;
1446 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1448 END IF;
1449
1450 -- take action on PO if update is allowed. The Control should reach here
1451 -- only if x_update_allowed = Y. Otherwise po_update_allowed
1452 -- would return Error
1453
1454 If (p_template_changed = 'Y') then -- set flag to Y if template is added
1455 L_date := Sysdate;
1456 L_conterms_exist_flag := 'Y';
1457 IF g_fnd_debug = 'Y' then
1458 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1459 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1460 MODULE =>g_module_prefix||l_api_name,
1461 MESSAGE =>'250:Procurement Contract. id'||p_header_id);
1462 END IF;
1463 End if;
1464 ELSE -- if template is being deleted set dates to null, flag to N;
1465 L_date := null;
1466 L_conterms_exist_flag := 'N';
1467 IF g_fnd_debug = 'Y' then
1468 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1469 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1470 MODULE =>g_module_prefix||l_api_name,
1471 MESSAGE =>'270:No More Procurement Contract. id'||p_header_id);
1472 END IF;
1473 End if;
1474 END IF;
1475
1476 IF l_is_mod = 'N' THEN --CLM Mod
1477 -- SQL WHAT-Update the conterms_exist_flag and the contract terms dates in po_headers_all
1478 -- SQL WHY - Update the flag based on if contract terms template attached or removed
1479 -- SQL JOIN- None
1480 Update po_headers_all
1481 Set conterms_exist_flag = l_conterms_exist_flag,
1482 Conterms_articles_upd_date = l_date,
1483 Conterms_DELIV_upd_date = l_date,
1484 Last_update_date = sysdate,
1485 Last_updated_by = FND_GLOBAl.USER_ID,
1486 Last_update_login = FND_GLOBAL.LOGIN_ID
1487 WHERE po_header_id= p_header_id;
1488 ELSE --CLM Mod
1489 -- SQL WHAT-Update the conterms_exist_flag and the contract terms dates in po_headers_all
1490 -- SQL WHY - Update the flag based on if contract terms template attached or removed
1491 -- SQL JOIN- None
1492 Update po_headers_draft_all
1493 Set conterms_exist_flag = l_conterms_exist_flag,
1494 Conterms_articles_upd_date = l_date,
1495 Conterms_DELIV_upd_date = l_date,
1496 Last_update_date = sysdate,
1497 Last_updated_by = FND_GLOBAl.USER_ID,
1498 Last_update_login = FND_GLOBAL.LOGIN_ID
1499 WHERE draft_id= p_header_id;
1500 END IF; --CLM Mod
1501
1502 IF g_fnd_debug = 'Y' then
1503 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1504 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1505 MODULE =>g_module_prefix||l_api_name,
1506 MESSAGE =>'300:Po headers all updated for header id'||p_header_id);
1507 END IF;
1508 End if;
1509
1510 ELSE -- if p_template_changed other than Y or D
1511
1512 Fnd_message.set_name('PO','PO_VALUE_MISSING');
1513 Fnd_message.set_token( token => 'VARIABLE'
1514 , VALUE => 'p_template_changed');
1515 FND_MSG_PUB.Add;
1516 x_return_status := FND_API.G_RET_STS_ERROR;
1517 RAISE FND_API.G_EXC_ERROR;
1518
1519 END IF; -- if p_template_changed other than Y or D
1520
1521 -- Commit the transaction if p_commit set true
1522 IF FND_API.TO_BOOLEAN(p_commit) then
1523 IF g_fnd_debug = 'Y' then
1524 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1525 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1526 MODULE =>g_module_prefix||l_api_name,
1527 MESSAGE =>'390: Commit set to true');
1528 END IF;
1529 End if;
1530 COMMIT;
1531 END IF;
1532 IF g_fnd_debug = 'Y' then
1533 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1534 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1535 MODULE =>g_module_prefix||l_api_name,
1536 MESSAGE =>'400: End' ||l_api_name);
1537 END IF;
1538 End if;
1539 EXCEPTION
1540 WHEN l_update_not_allowed then
1541 x_return_status := FND_API.G_RET_STS_ERROR;
1542 X_update_allowed := 'N';
1543 ROLLBACK TO SP_APPLY_TEMPLATE_CHANGE;
1544 FND_MESSAGE.set_name('PO', 'PO_NO_TEMPLATE_CHANGE');
1545 FND_MSG_PUB.Add;
1546 FND_MSG_PUB.Count_And_Get
1547 (p_count => x_msg_count,
1548 p_data => x_msg_data );
1549 IF g_fnd_debug = 'Y' then
1550 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1551 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1552 MODULE =>g_module_prefix||l_api_name,
1553 MESSAGE =>'450:Exception l_update_not_allowed ');
1554 END IF;
1555 END IF;
1556 WHEN FND_API.G_EXC_ERROR then
1557 x_return_status := FND_API.G_RET_STS_ERROR;
1558 ROLLBACK TO SP_APPLY_TEMPLATE_CHANGE;
1559 FND_MSG_PUB.Count_And_Get
1560 (p_count => x_msg_count,
1561 p_data => x_msg_data );
1562 IF g_fnd_debug = 'Y' then
1563 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1564 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1565 MODULE =>g_module_prefix||l_api_name,
1566 MESSAGE =>'470: expected error ');
1567 END IF;
1568 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
1569 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1570 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1571 MODULE =>g_module_prefix||l_api_name,
1572 MESSAGE =>'470:errors '||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
1573 END IF;
1574 END LOOP;
1575 END IF;
1576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
1577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1578 ROLLBACK TO SP_APPLY_TEMPLATE_CHANGE;
1579 FND_MSG_PUB.Count_And_Get
1580 (p_count => x_msg_count,
1581 p_data => x_msg_data );
1582 IF g_fnd_debug = 'Y' then
1583 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1584 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1585 MODULE =>g_module_prefix||l_api_name,
1586 MESSAGE =>'480:Exception UnExpected error ');
1587 END IF;
1588 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
1589 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1590 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1591 MODULE =>g_module_prefix||l_api_name,
1592 MESSAGE =>'480:errors '||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
1593 END IF;
1594 END LOOP;
1595
1596 END IF;
1597
1598 WHEN OTHERS THEN
1599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1600 ROLLBACK TO SP_APPLY_TEMPLATE_CHANGE;
1601 IF FND_MSG_PUB.Check_Msg_Level
1602 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1603 THEN
1604 FND_MSG_PUB.Add_Exc_Msg
1605 (p_pkg_name => 'PO_CONTERMS_UTL_GRP',
1606 p_procedure_name => l_api_name);
1607 END IF; --msg level
1608 FND_MSG_PUB.Count_And_Get
1609 (p_count => x_msg_count,
1610 p_data => x_msg_data );
1611 IF g_fnd_debug = 'Y' then
1612 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1613 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1614 MODULE =>g_module_prefix||l_api_name,
1615 MESSAGE =>'500:Exception UnExpected error '||sqlcode||':'||sqlerrm);
1616 END IF;
1617 END IF;
1618 END Apply_template_change;
1619 -------------------------------------------------------------------------------
1620 --Start of Comments
1621 --Name: Attribute_value_changed
1622 --Pre-reqs:
1623 -- 1.DONOT Use this API from wherever Functional Security Check is needed. This API
1624 -- Assumes that the org context is already set
1625 -- 2.This API should only be called when Contracts QA is in Amend mode
1626 --Modifies:
1627 -- None
1628 --Locks:
1629 -- None
1630 --Function:
1631 -- This API will be called by Contracts to check if values of system variables
1632 -- Changed between latest revision (working copy) and the last archived one.
1633 --Parameters:
1634 --IN:
1635 --p_api_version
1636 -- Standard Parameter. API version number expected by the caller
1637 --p_init_msg_list
1638 -- Standard parameter.Initialize message list
1639 --p_doc_type
1640 -- OKC Document type
1641 --p_doc_id
1642 -- PO header id
1643 --IN OUT:
1644 --p_sys_var_tbl
1645 -- A table of varchar2(40) to hold the system variable codes which changed between the two revisions
1646 -- Contracts will pass list of all PO attributes being used in Contract terms fot that PO
1647 -- This APi will filter that list to return only those which changed since last revsion
1648 --OUT:
1649 --x_msg_count
1650 -- Standard parameter.Message count
1651 --x_msg_data
1652 -- Standard parameter.message data
1653 --x_return_status
1654 -- Standard parameter. Status Returned to calling API. Possible values are following
1655 -- FND_API.G_RET_STS_ERROR - for expected error
1656 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
1657 -- FND_API.G_RET_STS_SUCCESS - for success
1658 --Notes:
1659 -- 07/11/2003 smhanda
1660 -- 1. This API has been written specifically for integration with contracts Before using it in any other place
1661 -- Please diagnose the impact about including security
1662 --Testing:
1663 -- Test this API by passing different po Header where in some are archived, some are not, some are signed
1664 -- Test for value changes between last version and current
1665 --End of Comments
1666 -------------------------------------------------------------------------------
1667 PROCEDURE attribute_value_changed (
1668 p_api_version IN NUMBER,
1669 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1670 p_doc_type IN VARCHAR2 := NULL, -- CLM Mod project
1671 p_doc_id IN NUMBER,
1672 p_sys_var_tbl IN OUT NOCOPY VARIABLE_CODE_TBL_TYPE,
1673 x_return_status OUT NOCOPY VARCHAR2,
1674 x_msg_data OUT NOCOPY VARCHAR2,
1675 x_msg_count OUT NOCOPY NUMBER
1676 ) IS
1677 l_api_name CONSTANT VARCHAR(30) := 'Attribute_value_changed';
1678 l_api_version CONSTANT NUMBER := 1.0;
1679 l_type_lookup_code po_headers_all.type_lookup_code%type;
1680 -- the length varchar40 fixed with the assumption that the length of variable code
1681 -- will not exceed this. For using %type, dependence on OKC tables. Consider for refactor
1682 l_po_attrib_tbl VARIABLE_CODE_TBL_TYPE;
1683 l_sys_var_index BINARY_INTEGER;
1684 l_po_attribute_index BINARY_INTEGER;
1685 l_found BOOLEAN;
1686 l_check VARCHAR2(1);
1687 l_spo_amt NUMBER;
1688 l_archived_spo_amt NUMBER;
1689 l_is_mod VARCHAR2(1) := 'N'; -- CLM Mod
1690 BEGIN
1691 IF g_fnd_debug = 'Y' then
1692 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1693 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1694 MODULE =>g_module_prefix||l_api_name,
1695 MESSAGE =>'10: Start API' ||l_api_name);
1696 END IF;
1697 END IF;
1698 -- Standard call to check for call compatibility.
1699 IF NOT FND_API.Compatible_API_Call (p_current_version_number=>l_api_version,
1700 p_caller_version_number =>p_api_version,
1701 p_api_name =>l_api_name,
1702 p_pkg_name =>G_PKG_NAME)
1703 THEN
1704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1705 END IF;
1706
1707 -- Initialize message list if p_init_msg_list is set to TRUE.
1708 IF FND_API.to_Boolean(p_init_msg_list ) THEN
1709 FND_MSG_PUB.initialize;
1710 END IF;
1711
1712 -- Initialize API return status to success
1713 x_return_status := FND_API.G_RET_STS_SUCCESS;
1714
1715 -- Basic validations about in parameters
1716 IF p_doc_id is null then
1717 Fnd_message.set_name('PO','PO_VALUE_MISSING');
1718 Fnd_message.set_token( token => 'VARIABLE'
1719 , VALUE => 'p_doc_id');
1720 FND_MSG_PUB.Add;
1721 x_return_status := FND_API.G_RET_STS_ERROR;
1722 RAISE FND_API.G_EXC_ERROR;
1723 END IF;
1724
1725
1726 /* Mod changes */
1727 IF p_doc_type = 'PO_STANDARD_MOD' OR
1728 p_doc_type = 'PA_BLANKET_MOD' OR --<Bug 15834898>
1729 p_doc_type = 'PA_CONTRACT_MOD' THEN --<Bug 15834898>
1730 l_is_mod := 'Y';
1731 END IF;
1732 /* Mod changes */
1733
1734
1735 -- SQL WHAT-get the changed status for PO system Variables
1736 -- SQL WHY - Needed by Contracts to check if amendments generated for changed ones
1737 -- SQL JOIN- PO_header_id
1738 -- This query assumes that there is a record in archives since this procedure
1739 -- should be called by contracts only in amend mode, which happens, if PO has been
1740 -- archived at least once.
1741 IF l_is_mod = 'N' THEN --CLM Mod
1742 SELECT
1743 DECODE(poh.org_id,poha.org_id,'N','OKC$B_ORGANIZATION')
1744 ,DECODE(poh.vendor_id,poha.vendor_id,'N','OKC$B_SUPPLIER_NAME')
1745 ,DECODE(poh.vendor_site_id,poha.vendor_site_id,'N','OKC$B_SUPPLIER_SITE')
1746 ,DECODE(poh.vendor_contact_id,poha.vendor_contact_id,'N','OKC$B_SUPPLIER_CONTACT')
1747 ,DECODE(poh.ship_to_location_id,poha.ship_to_location_id,'N','OKC$B_SHIP_TO_ADDRESS')
1748 ,DECODE(poh.bill_to_location_id,poha.bill_to_location_id,'N','OKC$B_BILL_TO_ADDRESS')
1749 ,DECODE(poh.currency_code,poha.currency_code,'N','OKC$B_TXN_CURRENCY')
1750 ,DECODE(poh.agent_id,poha.agent_id,'N','OKC$B_BUYER')
1751 ,DECODE(poh.blanket_total_amount,poha.blanket_total_amount,'N','OKC$B_AGREEMENT_AMOUNT_TXN')
1752 ,DECODE(poh.blanket_total_amount,poha.blanket_total_amount,'N','OKC$B_AGREEMENT_AMOUNT_FUNC')
1753 ,DECODE(poh.global_agreement_flag,poha.global_agreement_flag,'N','OKC$B_GLOBAL_FLAG')
1754 ,DECODE(poh.rate_type,poha.rate_type,'N','OKC$B_RATE_TYPE')
1755 ,DECODE(poh.rate_date,poha.rate_date,'N','OKC$B_RATE_DATE')
1756 ,DECODE(poh.rate,poha.rate,'N','OKC$B_RATE')
1757 ,DECODE(poh.terms_id ,poha.terms_id,'N','OKC$B_PAYMENT_TERMS')
1758 ,DECODE(poh.freight_terms_lookup_code,poha.freight_terms_lookup_code,'N','OKC$B_FREIGHT_TERMS')
1759 ,DECODE(poh.ship_via_lookup_code,poha.ship_via_lookup_code,'N','OKC$B_CARRIER')
1760 ,DECODE(poh.fob_lookup_code,poha.fob_lookup_code,'N','OKC$B_FOB')
1761 ,DECODE(poh.pay_on_code,poha.pay_on_code,'N','OKC$B_PAY_ON_CODE')
1762 ,DECODE(poh.acceptance_required_flag,poha.acceptance_required_flag,'N','OKC$B_ACCEPTANCE_METHOD')
1763 ,DECODE(poh.acceptance_due_date,poha.acceptance_due_date,'N','OKC$B_ACCEPTANCE_REQD_DATE')
1764 ,DECODE(poh.supply_agreement_flag,poha.supply_agreement_flag,'N','OKC$B_SUPPLY_AGREEMENT_FLAG')
1765 ,DECODE(poh.start_date,poha.start_date,'N','OKC$B_AGREEMENT_START_DATE')
1766 ,DECODE(poh.end_date,poha.end_date,'N','OKC$B_AGREEMENT_END_DATE')
1767 ,DECODE(poh.min_release_amount,poha.min_release_amount,'N','OKC$B_MINIMUM_RELEASE_AMT_TXN')
1768 ,DECODE(poh.min_release_amount,poha.min_release_amount,'N','OKC$B_MINIMUM_RELEASE_AMT_FUNC')
1769 ,poh.type_lookup_code
1770 ,DECODE(poh.shipping_control, poha.shipping_control, 'N', 'OKC$B_TRANSPORTATION_ARRANGED') --<HTML Agreements R12>
1771 INTO
1772 l_po_attrib_tbl(1)
1773 ,l_po_attrib_tbl(2)
1774 ,l_po_attrib_tbl(3)
1775 ,l_po_attrib_tbl(4)
1776 ,l_po_attrib_tbl(5)
1777 ,l_po_attrib_tbl(6)
1778 ,l_po_attrib_tbl(7)
1779 ,l_po_attrib_tbl(8)
1780 ,l_po_attrib_tbl(9)
1781 ,l_po_attrib_tbl(10)
1782 ,l_po_attrib_tbl(11)
1783 ,l_po_attrib_tbl(12)
1784 ,l_po_attrib_tbl(13)
1785 ,l_po_attrib_tbl(14)
1786 ,l_po_attrib_tbl(15)
1787 ,l_po_attrib_tbl(16)
1788 ,l_po_attrib_tbl(17)
1789 ,l_po_attrib_tbl(18)
1790 ,l_po_attrib_tbl(19)
1791 ,l_po_attrib_tbl(20)
1792 ,l_po_attrib_tbl(21)
1793 ,l_po_attrib_tbl(22)
1794 ,l_po_attrib_tbl(23)
1795 ,l_po_attrib_tbl(24)
1796 ,l_po_attrib_tbl(25)
1797 ,l_po_attrib_tbl(26)
1798 --before adding next running index here Note that l_po_attrib_tbl(27) and l_po_attrib_tbl(28)
1799 --are used below for header amounts
1800 ,l_type_lookup_code
1801 ,l_po_attrib_tbl(29) --<HTML Agreement R12>
1802
1803 FROM
1804 po_headers_all poh
1805 ,po_headers_archive_all poha
1806 WHERE poh.po_header_id = p_doc_id
1807 AND poh.po_header_id = poha.po_header_id
1808 AND poha.latest_external_flag = 'Y';
1809
1810 IF g_fnd_debug = 'Y' then
1811 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1812 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1813 MODULE =>g_module_prefix||l_api_name,
1814 MESSAGE =>'120: selected columns with changed values');
1815 END IF;
1816 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1817 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1818 MODULE =>g_module_prefix||l_api_name,
1819 MESSAGE =>'130: document type '||l_type_lookup_code);
1820 END IF;
1821
1822 END IF;
1823 -- Get Change for PO_total_amount for SPO
1824 -- The following query has an outer join based on the assumption that
1825 -- it is possible that only header was archived and the line was not
1826 -- since all the changes that happened were in Header only
1827 IF l_type_lookup_code = 'STANDARD' then
1828 -- get total amount for working copy of PO
1829 l_spo_amt := po_core_s.get_total(x_object_type=>'H',
1830 x_object_id =>p_doc_id);
1831 --get total amount for last archived version of PO
1832 l_archived_spo_amt:=po_core_s.get_archive_total
1833 (p_object_id =>p_doc_id,
1834 p_doc_type => 'PO',
1835 p_doc_subtype => 'STANDARD');
1836 IF l_spo_amt <> l_archived_spo_amt THEN
1837 l_po_attrib_tbl(27) := 'OKC$B_PO_TOTAL_AMOUNT_TXN' ;
1838 --If amount for transaction currency has changed, then assume that amount in
1839 --Function currency Changed as well.
1840 l_po_attrib_tbl(28) := 'OKC$B_PO_TOTAL_AMOUNT_FUNC';
1841 END IF; -- l_spo_amt <> l_archived_spo_amt
1842
1843
1844 IF g_fnd_debug = 'Y' then
1845 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1846 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1847 MODULE =>g_module_prefix||l_api_name,
1848 MESSAGE =>'150: Got header amount differnce');
1849 END IF;
1850 END IF;
1851 END IF;-- type_lookup_code='STANDARD'
1852 IF g_fnd_debug = 'Y' then
1853 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1854 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1855 MODULE =>g_module_prefix||l_api_name,
1856 MESSAGE =>'160: First p_sys_var'||p_sys_var_tbl.FIRST);
1857 END IF;
1858 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1859 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1860 MODULE =>g_module_prefix||l_api_name,
1861 MESSAGE =>'165: Last p_sys_var'||p_sys_var_tbl.LAST);
1862 END IF;
1863 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1864 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1865 MODULE =>g_module_prefix||l_api_name,
1866 MESSAGE =>'170: first l_po_var'||p_sys_var_tbl.FIRST);
1867 END IF;
1868 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1869 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1870 MODULE =>g_module_prefix||l_api_name,
1871 MESSAGE =>'175: last l_po_var'||p_sys_var_tbl.LAST);
1872 END IF;
1873 END IF;
1874 ELSE --CLM Mod
1875 -- No attribute change for Mod
1876 null;
1877 END IF; --CLM Mod
1878 -- filter the changed value sent by contracts
1879 l_sys_var_index := p_sys_var_tbl.FIRST;
1880 While l_sys_var_index <= p_sys_var_tbl.last
1881 LOOP
1882 IF g_fnd_debug = 'Y' then
1883 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1884 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1885 MODULE =>g_module_prefix||l_api_name,
1886 MESSAGE =>'180: current p_sys_var index'||l_sys_var_index);
1887 END IF;
1888 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1889 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1890 MODULE =>g_module_prefix||l_api_name,
1891 MESSAGE =>'185: current p_sys_var value'||p_sys_var_tbl(l_sys_var_index));
1892 END IF;
1893 END IF;
1894 l_found := false;
1895 l_po_attribute_index := l_po_attrib_tbl.FIRST;
1896 While l_po_attribute_index <= l_po_attrib_tbl.LAST
1897 LOOP
1898 IF g_fnd_debug = 'Y' then
1899 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1900 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1901 MODULE =>g_module_prefix||l_api_name,
1902 MESSAGE =>'190: current l_po_var index'||l_po_attribute_index);
1903 END IF;
1904 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1905 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1906 MODULE =>g_module_prefix||l_api_name,
1907 MESSAGE =>'200: current l_po_var value'||l_po_attrib_tbl(l_po_attribute_index));
1908 END IF;
1909
1910 END IF;
1911 IF l_po_attrib_tbl(l_po_attribute_index)= 'N' then
1912 IF g_fnd_debug = 'Y' then
1913 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1914 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1915 MODULE =>g_module_prefix||l_api_name,
1916 MESSAGE =>'210: Delete l_po_var'||l_po_attrib_tbl(l_po_attribute_index));
1917 END IF;
1918 END IF;
1919 l_po_attrib_tbl.DELETE(l_po_attribute_index);
1920 IF g_fnd_debug = 'Y' then
1921 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1922 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1923 MODULE =>g_module_prefix||l_api_name,
1924 MESSAGE =>'215: Deleted');
1925 END IF;
1926 END IF;
1927
1928 ELSIF l_po_attrib_tbl(l_po_attribute_index) = p_sys_var_tbl(l_sys_var_index) then
1929 l_found:=true;
1930 Exit;
1931 END IF;-- if l_po_attrib_tbl has something other than 'N'
1932 l_po_attribute_index := l_po_attrib_tbl.next(l_po_attribute_index);
1933 END LOOP;-- l_po_attribute_index inner loop
1934 If NOT l_found then
1935 IF g_fnd_debug = 'Y' then
1936 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1937 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1938 MODULE =>g_module_prefix||l_api_name,
1939 MESSAGE =>'220: Delete p_sys_var'||p_sys_var_tbl(l_sys_var_index));
1940 END IF;
1941 END IF;
1942 p_sys_var_tbl.delete(l_sys_var_index);
1943 IF g_fnd_debug = 'Y' then
1944 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1945 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1946 MODULE =>g_module_prefix||l_api_name,
1947 MESSAGE =>'225: Deleted');
1948 END IF;
1949 END IF;
1950 End if; --not l_found
1951 l_sys_var_index := p_sys_var_tbl.next(l_sys_var_index);
1952
1953 END LOOP;-- l_sys_var_index outer loop
1954 IF g_fnd_debug = 'Y' then
1955 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1956 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1957 MODULE =>g_module_prefix||l_api_name,
1958 MESSAGE =>'230: Filtering ended. element in p_sys_var'||p_sys_var_tbl.count);
1959 END IF;
1960
1961 l_sys_var_index := p_sys_var_tbl.FIRST;
1962 While l_sys_var_index <= p_sys_var_tbl.last
1963 LOOP
1964 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1965 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1966 MODULE =>g_module_prefix||l_api_name,
1967 MESSAGE =>'240: current index' ||l_sys_var_index);
1968 END IF;
1969 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1970 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1971 MODULE =>g_module_prefix||l_api_name,
1972 MESSAGE =>'250: column being sent'||p_sys_var_tbl(l_sys_var_index));
1973 END IF;
1974
1975 l_sys_var_index := p_sys_var_tbl.next(l_sys_var_index);
1976 END LOOP;
1977 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1978 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1979 MODULE =>g_module_prefix||l_api_name,
1980 MESSAGE =>'500: End API ' ||l_api_name);
1981 END IF;
1982 END IF; -- if fnd debug
1983 EXCEPTION
1984
1985 WHEN FND_API.G_EXC_ERROR then
1986 x_return_status := FND_API.G_RET_STS_ERROR;
1987 FND_MSG_PUB.Count_And_Get
1988 (p_count => x_msg_count,
1989 p_data => x_msg_data );
1990 IF g_fnd_debug = 'Y' then
1991 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1992 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1993 MODULE =>g_module_prefix||l_api_name,
1994 MESSAGE =>'600:Exception Expected error ');
1995 END IF;
1996 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
1997 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1998 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1999 MODULE =>g_module_prefix||l_api_name,
2000 MESSAGE =>'600:errors '||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
2001 END IF;
2002 END LOOP;
2003
2004 END IF;
2005 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
2006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2007 FND_MSG_PUB.Count_And_Get
2008 (p_count => x_msg_count,
2009 p_data => x_msg_data );
2010 IF g_fnd_debug = 'Y' then
2011 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2012 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
2013 MODULE =>g_module_prefix||l_api_name,
2014 MESSAGE =>'610:Exception UnExpected error ');
2015 END IF;
2016 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
2017 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2018 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
2019 MODULE =>g_module_prefix||l_api_name,
2020 MESSAGE =>'610:errors '||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
2021 END IF;
2022 END LOOP;
2023
2024 END IF;
2025
2026 WHEN OTHERS THEN
2027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2028 IF FND_MSG_PUB.Check_Msg_Level
2029 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2030 THEN
2031 FND_MSG_PUB.Add_Exc_Msg
2032 (p_pkg_name => 'PO_CONTERMS_UTILS_GRP',
2033 p_procedure_name => l_api_name);
2034 END IF; --msg level
2035 FND_MSG_PUB.Count_And_Get
2036 (p_count => x_msg_count,
2037 p_data => x_msg_data );
2038 IF g_fnd_debug = 'Y' then
2039 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2040 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
2041 MODULE =>g_module_prefix||l_api_name,
2042 MESSAGE =>'700:Exception UnExpected error '||sqlcode||':'||sqlerrm);
2043 END IF;
2044 END IF;
2045 END attribute_value_changed;
2046
2047
2048
2049 -------------------------------------------------------------------------------
2050 --Start of Comments
2051 --Name: Get_PO_Attribute_values
2052 --Pre-reqs:
2053 -- 1.DONOT Use this API from wherever Functional Security Check is needed.
2054 -- 2. This API Assumes that the org context is already set
2055 -- 3. This API will return valid values for System Variables used in Configurator
2056 -- OR for the system variables who reside directly on Po Headers. For the
2057 -- rest, it returns dummy value( See HLD)- No need to check whether value
2058 -- exists or not for setup system variables. This API returns dummy value
2059 -- so that the QA check for any value exists does not return uneccesary warnings
2060 -- For set up variables that are not directly on PO
2061 --Modifies:
2062 -- None
2063 --Locks:
2064 -- None
2065 --Function:
2066 -- This API will be called by Contracts to get values of system variables
2067 -- used in Contract terms configurator rules ( Non tabular ones)
2068 -- This API is also being called by Contracts QA to check if the used variables
2069 -- have a value.
2070 --Parameters:
2071 --IN:
2072 --p_api_version
2073 -- Standard Parameter. API version number expected by the caller
2074 --p_init_msg_list
2075 -- Standard parameter.Initialize message list
2076 --p_doc_type
2077 -- OKC Document type
2078 --p_doc_id
2079 -- PO header id
2080 --IN OUT:
2081 --p_sys_var_value_tbl
2082 -- A table of records to hold the system variable codes and values in working copy
2083 --OUT:
2084 --x_msg_count
2085 -- Standard parameter.Message count
2086 --x_msg_data
2087 -- Standard parameter.message data
2088 --x_return_status
2089 -- Standard parameter. Status Returned to calling API. Possible values are following
2090 -- FND_API.G_RET_STS_ERROR - for expected error
2091 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
2092 -- FND_API.G_RET_STS_SUCCESS - for success
2093 --Notes:
2094 -- 07/11/2003 smhanda
2095 -- 1. This API has been written specifically for integration with contracts Before using it in any other place
2096 -- Please diagnose the impact about including security
2097 --
2098 --Testing:
2099 -- Test for existing/not existing values in PO
2100 --End of Comments
2101 -------------------------------------------------------------------------------
2102 PROCEDURE Get_PO_Attribute_values(
2103 p_api_version IN NUMBER,
2104 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2105 p_doc_type IN VARCHAR2 := NULL, -- CLM Mod project
2106 p_doc_id IN NUMBER,
2107 p_sys_var_value_tbl IN OUT NOCOPY VARIABLE_VALUE_TBL_TYPE,
2108 x_return_status OUT NOCOPY VARCHAR2,
2109 x_msg_data OUT NOCOPY VARCHAR2,
2110 x_msg_count OUT NOCOPY NUMBER) IS
2111
2112 l_api_name CONSTANT VARCHAR(30) := 'Get_PO_Attribute_values';
2113 l_api_version CONSTANT NUMBER := 1.0;
2114 l_type_lookup_code po_headers_all.type_lookup_code%type;
2115 -- the length varchar40 fixed with the assumption that the length of variable code
2116 -- will not exceed this. For using %type, dependence on OKC tables. Consider for refactor
2117 l_po_attrib_tbl VARIABLE_VALUE_TBL_TYPE;
2118 l_sys_var_index BINARY_INTEGER;
2119 l_po_attribute_index BINARY_INTEGER;
2120 l_found BOOLEAN;
2121 l_check VARCHAR2(1);
2122 --BUG#3809298.Introducing the variables below to fetch the
2123 --parameters involved in calculating the functional and trancastion amounts
2124 --from the SQL query below.
2125 l_poh_type_lookup_code PO_HEADERS.type_lookup_code%type;
2126 l_poh_rate PO_HEADERS.rate%type ;
2127 l_cu_MINIMUM_ACCOUNTABLE_UNIT FND_CURRENCIES_VL.minimum_accountable_unit%type;
2128 l_cu_precision FND_CURRENCIES_VL.precision%type;
2129 l_cuf_MINIMUM_ACCOUNTABLE_UNIT FND_CURRENCIES_VL.minimum_accountable_unit%type;
2130 l_cuf_precision FND_CURRENCIES_VL.precision%type;
2131 l_po_total_amount NUMBER;
2132 --BUG#3809298 End of variable declarations.
2133
2134 -- Bug 3250745. l_dummy_value will be returned for all the variables which neither
2135 --live in po tables nor are needed for configurator (As per HLD)
2136 -- The reason for doing this is to avoid extra processing and joins
2137 -- For setup system variables whose values are not changed frequently
2138 --If a system variable is changed to use in Configurator. Make sure
2139 --its actual value is returned and not dummy
2140 l_dummy_value VARCHAR2(10) := 'NOT_NULL';
2141 l_is_mod VARCHAR2(1) := 'N'; -- CLM Mod
2142 BEGIN
2143 IF g_fnd_debug = 'Y' then
2144 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2145 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2146 MODULE =>g_module_prefix||l_api_name,
2147 MESSAGE =>'10: Start API' ||l_api_name);
2148 END IF;
2149 END IF;
2150 -- Standard call to check for call compatibility.
2151 IF NOT FND_API.Compatible_API_Call (p_current_version_number=>l_api_version,
2152 p_caller_version_number =>p_api_version,
2153 p_api_name =>l_api_name,
2154 p_pkg_name =>G_PKG_NAME)
2155 THEN
2156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2157 END IF;
2158
2159 -- Initialize message list if p_init_msg_list is set to TRUE.
2160 IF FND_API.to_Boolean(p_init_msg_list ) THEN
2161 FND_MSG_PUB.initialize;
2162 END IF;
2163
2164 -- Initialize API return status to success
2165 x_return_status := FND_API.G_RET_STS_SUCCESS;
2166
2167 -- Basic validations about in parameters
2168 IF p_doc_id is null then
2169 Fnd_message.set_name('PO','PO_VALUE_MISSING');
2170 Fnd_message.set_token( token => 'VARIABLE'
2171 , VALUE => 'p_doc_id');
2172 FND_MSG_PUB.Add;
2173 x_return_status := FND_API.G_RET_STS_ERROR;
2174 RAISE FND_API.G_EXC_ERROR;
2175 END IF;
2176
2177 -- make the return table with attribute names
2178 l_po_attrib_tbl(1).variable_code:= 'OKC$B_ORGANIZATION';
2179 l_po_attrib_tbl(2).variable_code:= 'OKC$B_DOCUMENT_TYPE';
2180 l_po_attrib_tbl(3).variable_code:= 'OKC$B_SUPPLIER_NAME';
2181 l_po_attrib_tbl(4).variable_code:= 'OKC$B_SUPPLIER_SITE';
2182 l_po_attrib_tbl(5).variable_code:= 'OKC$B_SUPPLIER_STATE';
2183 l_po_attrib_tbl(6).variable_code:= 'OKC$B_SUPPLIER_ZIP';
2184 l_po_attrib_tbl(7).variable_code:= 'OKC$B_SUPPLIER_COUNTRY';
2185 l_po_attrib_tbl(8).variable_code:= 'OKC$B_SHIP_TO_ADDRESS';
2186 l_po_attrib_tbl(9).variable_code:= 'OKC$B_BILL_TO_ADDRESS';
2187 l_po_attrib_tbl(10).variable_code:= 'OKC$B_TXN_CURRENCY';
2188 l_po_attrib_tbl(11).variable_code:= 'OKC$B_FUNC_CURRENCY';
2189 l_po_attrib_tbl(12).variable_code:= 'OKC$B_PO_TOTAL_AMOUNT_TXN';
2190 l_po_attrib_tbl(13).variable_code:= 'OKC$B_PO_TOTAL_AMOUNT_FUNC';
2191 l_po_attrib_tbl(14).variable_code:= 'OKC$B_AGREEMENT_AMOUNT_TXN';
2192 l_po_attrib_tbl(15).variable_code:= 'OKC$B_AGREEMENT_AMOUNT_FUNC';
2193 l_po_attrib_tbl(16).variable_code:= 'OKC$B_GLOBAL_FLAG';
2194 l_po_attrib_tbl(17).variable_code:= 'OKC$B_RATE_TYPE';
2195 l_po_attrib_tbl(18).variable_code:= 'OKC$B_PAYMENT_TERMS';
2196 l_po_attrib_tbl(19).variable_code:= 'OKC$B_FREIGHT_TERMS';
2197 l_po_attrib_tbl(20).variable_code:= 'OKC$B_CARRIER';
2198 l_po_attrib_tbl(21).variable_code:= 'OKC$B_FOB';
2199 l_po_attrib_tbl(22).variable_code:= 'OKC$B_PAY_ON_CODE';
2200 l_po_attrib_tbl(23).variable_code:= 'OKC$B_SUPPLY_AGREEMENT_FLAG';
2201 l_po_attrib_tbl(24).variable_code:= 'OKC$B_MINIMUM_RELEASE_AMT_TXN';
2202 l_po_attrib_tbl(25).variable_code:= 'OKC$B_MINIMUM_RELEASE_AMT_FUNC';
2203 l_po_attrib_tbl(26).variable_code:= 'OKC$B_LEGAL_ENTITY';
2204 l_po_attrib_tbl(27).variable_code:= 'OKC$B_DOCUMENT_NUMBER';
2205 l_po_attrib_tbl(28).variable_code:= 'OKC$B_DOCUMENT_REVISION';
2206 l_po_attrib_tbl(29).variable_code:= 'OKC$B_SUPPLIER_CONTACT';
2207 l_po_attrib_tbl(30).variable_code:= 'OKC$B_BUYER';
2208 l_po_attrib_tbl(31).variable_code:= 'OKC$B_RATE_DATE';
2209 l_po_attrib_tbl(32).variable_code:= 'OKC$B_RATE';
2210 l_po_attrib_tbl(33).variable_code:= 'OKC$B_ACCEPTANCE_METHOD';
2211 l_po_attrib_tbl(34).variable_code:= 'OKC$B_ACCEPTANCE_REQD_DATE';
2212 l_po_attrib_tbl(35).variable_code:= 'OKC$B_AGREEMENT_START_DATE';
2213 l_po_attrib_tbl(36).variable_code:= 'OKC$B_AGREEMENT_END_DATE';
2214
2215
2216 --------------------------------------------------------------------
2217 -- As per HLD (BUG 3250745), the system variables which are part of Oracle setup but neither
2218 -- live in PO tables nor used in Configurator Contract Expert, we
2219 -- donot need to return the value for QA Check- "If some value is there"
2220 -- So we are returning dummy value so that unnecessary warnings are not
2221 -- generated in QA
2222 --START - SET UP DUMMIES--------------------------------------------
2223 l_po_attrib_tbl(37).variable_code:= 'OKC$B_ORGANIZATION_REGION1';
2224 l_po_attrib_tbl(37).variable_value_id:= l_dummy_value;
2225
2226 l_po_attrib_tbl(38).variable_code:= 'OKC$B_ORGANIZATION_REGION2';
2227 l_po_attrib_tbl(38).variable_value_id:= l_dummy_value;
2228
2229 l_po_attrib_tbl(39).variable_code:= 'OKC$B_ORGANIZATION_REGION3';
2230 l_po_attrib_tbl(39).variable_value_id:= l_dummy_value;
2231
2232 l_po_attrib_tbl(40).variable_code:= 'OKC$B_ORGANIZATION_ADDR_STYLE';
2233 l_po_attrib_tbl(40).variable_value_id:= l_dummy_value;
2234
2235 l_po_attrib_tbl(41).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR';
2236 l_po_attrib_tbl(41).variable_value_id:= l_dummy_value;
2237
2238 l_po_attrib_tbl(42).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_STYLE';
2239 l_po_attrib_tbl(42).variable_value_id:= l_dummy_value;
2240
2241 l_po_attrib_tbl(43).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_LINE_1';
2242 l_po_attrib_tbl(43).variable_value_id:= l_dummy_value;
2243
2244 l_po_attrib_tbl(44).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_LINE_2';
2245 l_po_attrib_tbl(44).variable_value_id:= l_dummy_value;
2246
2247 l_po_attrib_tbl(45).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_LINE_3';
2248 l_po_attrib_tbl(45).variable_value_id:= l_dummy_value;
2249
2250 l_po_attrib_tbl(46).variable_code:= 'OKC$B_LEGAL_ENTITY_CITY';
2251 l_po_attrib_tbl(46).variable_value_id:= l_dummy_value;
2252
2253 l_po_attrib_tbl(47).variable_code:= 'OKC$B_LEGAL_ENTITY_ZIP';
2254 l_po_attrib_tbl(47).variable_value_id:= l_dummy_value;
2255
2256 l_po_attrib_tbl(48).variable_code:= 'OKC$B_LEGAL_ENTITY_COUNTRY';
2257 l_po_attrib_tbl(48).variable_value_id:= l_dummy_value;
2258
2259 l_po_attrib_tbl(49).variable_code:= 'OKC$B_LEGAL_ENTITY_REGION1';
2260 l_po_attrib_tbl(49).variable_value_id:= l_dummy_value;
2261
2262 l_po_attrib_tbl(50).variable_code:= 'OKC$B_LEGAL_ENTITY_REGION2';
2263 l_po_attrib_tbl(50).variable_value_id:= l_dummy_value;
2264
2265 l_po_attrib_tbl(51).variable_code:= 'OKC$B_LEGAL_ENTITY_REGION3';
2266 l_po_attrib_tbl(51).variable_value_id:= l_dummy_value;
2267
2268 l_po_attrib_tbl(52).variable_code:= 'OKC$B_SUPPLIER_ADDRESS';
2269 l_po_attrib_tbl(52).variable_value_id:= l_dummy_value;
2270
2271 l_po_attrib_tbl(53).variable_code:= 'OKC$B_SUPPLIER_ADDRESS_LINE_1';
2272 l_po_attrib_tbl(53).variable_value_id:= l_dummy_value;
2273
2274 l_po_attrib_tbl(54).variable_code:= 'OKC$B_SUPPLIER_ADDRESS_LINE_2';
2275 l_po_attrib_tbl(54).variable_value_id:= l_dummy_value;
2276
2277 l_po_attrib_tbl(55).variable_code:= 'OKC$B_SUPPLIER_ADDRESS_LINE_3';
2278 l_po_attrib_tbl(55).variable_value_id:= l_dummy_value;
2279
2280 l_po_attrib_tbl(56).variable_code:= 'OKC$B_SUPPLIER_CITY';
2281 l_po_attrib_tbl(56).variable_value_id:= l_dummy_value;
2282
2283 l_po_attrib_tbl(57).variable_code:= 'OKC$B_SUPPLIER_CLASSIFICATION';
2284 l_po_attrib_tbl(57).variable_value_id:= l_dummy_value;
2285
2286 l_po_attrib_tbl(58).variable_code:= 'OKC$B_SUPPLIER_MINORITY_TYPE';
2287 l_po_attrib_tbl(58).variable_value_id:= l_dummy_value;
2288
2289 l_po_attrib_tbl(59).variable_code:= 'OKC$B_SHIP_TO_ADDR_STYLE';
2290 l_po_attrib_tbl(59).variable_value_id:= l_dummy_value;
2291
2292 l_po_attrib_tbl(60).variable_code:= 'OKC$B_SHIP_TO_ADDR_LINE1';
2293 l_po_attrib_tbl(60).variable_value_id:= l_dummy_value;
2294
2295 l_po_attrib_tbl(61).variable_code:= 'OKC$B_SHIP_TO_ADDR_LINE2';
2296 l_po_attrib_tbl(61).variable_value_id:= l_dummy_value;
2297
2298 l_po_attrib_tbl(62).variable_code:= 'OKC$B_SHIP_TO_ADDR_LINE3';
2299 l_po_attrib_tbl(62).variable_value_id:= l_dummy_value;
2300
2301 l_po_attrib_tbl(63).variable_code:= 'OKC$B_SHIP_TO_ADDR_CITY';
2302 l_po_attrib_tbl(63).variable_value_id:= l_dummy_value;
2303
2304 l_po_attrib_tbl(64).variable_code:= 'OKC$B_SHIP_TO_ADDR_ZIP';
2305 l_po_attrib_tbl(64).variable_value_id:= l_dummy_value;
2306
2307 l_po_attrib_tbl(65).variable_code:= 'OKC$B_SHIP_TO_ADDR_COUNTRY';
2308 l_po_attrib_tbl(65).variable_value_id:= l_dummy_value;
2309
2310 l_po_attrib_tbl(66).variable_code:= 'OKC$B_SHIP_TO_ADDR_REGION1';
2311 l_po_attrib_tbl(66).variable_value_id:= l_dummy_value;
2312
2313 l_po_attrib_tbl(67).variable_code:= 'OKC$B_SHIP_TO_ADDR_REGION2';
2314 l_po_attrib_tbl(67).variable_value_id:= l_dummy_value;
2315
2316 l_po_attrib_tbl(68).variable_code:= 'OKC$B_SHIP_TO_ADDR_REGION3';
2317 l_po_attrib_tbl(68).variable_value_id:= l_dummy_value;
2318
2319 l_po_attrib_tbl(69).variable_code:= 'OKC$B_BILL_TO_ADDR_STYLE';
2320 l_po_attrib_tbl(69).variable_value_id:= l_dummy_value;
2321
2322 l_po_attrib_tbl(70).variable_code:= 'OKC$B_BILL_TO_ADDR_LINE1';
2323 l_po_attrib_tbl(70).variable_value_id:= l_dummy_value;
2324
2325 l_po_attrib_tbl(71).variable_code:= 'OKC$B_BILL_TO_ADDR_LINE2';
2326 l_po_attrib_tbl(71).variable_value_id:= l_dummy_value;
2327
2328 l_po_attrib_tbl(72).variable_code:= 'OKC$B_BILL_TO_ADDR_LINE3';
2329 l_po_attrib_tbl(72).variable_value_id:= l_dummy_value;
2330
2331 l_po_attrib_tbl(73).variable_code:= 'OKC$B_BILL_TO_ADDR_CITY';
2332 l_po_attrib_tbl(73).variable_value_id:= l_dummy_value;
2333
2334 l_po_attrib_tbl(74).variable_code:= 'OKC$B_BILL_TO_ADDR_ZIP';
2335 l_po_attrib_tbl(74).variable_value_id:= l_dummy_value;
2336
2337 l_po_attrib_tbl(75).variable_code:= 'OKC$B_BILL_TO_ADDR_COUNTRY';
2338 l_po_attrib_tbl(75).variable_value_id:= l_dummy_value;
2339
2340 l_po_attrib_tbl(76).variable_code:= 'OKC$B_BILL_TO_ADDR_REGION1';
2341 l_po_attrib_tbl(76).variable_value_id:= l_dummy_value;
2342
2343 l_po_attrib_tbl(77).variable_code:= 'OKC$B_BILL_TO_ADDR_REGION2';
2344 l_po_attrib_tbl(77).variable_value_id:= l_dummy_value;
2345
2346 l_po_attrib_tbl(78).variable_code:= 'OKC$B_BILL_TO_ADDR_REGION3';
2347 l_po_attrib_tbl(78).variable_value_id:= l_dummy_value;
2348
2349 l_po_attrib_tbl(79).variable_code:= 'OKC$B_ORGANIZATION_ADDR';
2350 l_po_attrib_tbl(79).variable_value_id:= l_dummy_value;
2351
2352 l_po_attrib_tbl(80).variable_code:= 'OKC$B_ORGANIZATION_ADDR_LINE_1';
2353 l_po_attrib_tbl(80).variable_value_id:= l_dummy_value;
2354
2355 l_po_attrib_tbl(81).variable_code:= 'OKC$B_ORGANIZATION_ADDR_LINE_2';
2356 l_po_attrib_tbl(81).variable_value_id:= l_dummy_value;
2357
2358 l_po_attrib_tbl(82).variable_code:= 'OKC$B_ORGANIZATION_ADDR_LINE_3';
2359 l_po_attrib_tbl(82).variable_value_id:= l_dummy_value;
2360
2361 l_po_attrib_tbl(83).variable_code:= 'OKC$B_ORGANIZATION_CITY';
2362 l_po_attrib_tbl(83).variable_value_id:= l_dummy_value;
2363
2364 l_po_attrib_tbl(84).variable_code:= 'OKC$B_ORGANIZATION_ZIP';
2365 l_po_attrib_tbl(84).variable_value_id:= l_dummy_value;
2366
2367 l_po_attrib_tbl(85).variable_code:= 'OKC$B_ORGANIZATION_COUNTRY';
2368 l_po_attrib_tbl(85).variable_value_id:= l_dummy_value;
2369
2370 l_po_attrib_tbl(86).variable_code:= 'OKC$B_SUPPLIER_ADDRESS_LINE_4';
2371 l_po_attrib_tbl(86).variable_value_id:= l_dummy_value;
2372
2373 --END SET UP DUMMIES-----------------------------------------------
2374
2375 l_po_attrib_tbl(87).variable_code := 'OKC$B_TRANSPORTATION_ARRANGED';--<HTML Agreement R12>
2376 l_po_attrib_tbl(88).variable_code := 'OKC$B_PURCHASING_STYLE'; --<R12 STYLES PHASE II>
2377
2378
2379 /* Mod changes */
2380 IF p_doc_type = 'PO_STANDARD_MOD' OR
2381 p_doc_type = 'PA_BLANKET_MOD' OR --<Bug 15834898>
2382 p_doc_type = 'PA_CONTRACT_MOD' THEN --<Bug 15834898>
2383 l_is_mod := 'Y';
2384 END IF;
2385 /* Mod changes */
2386
2387 -------------------------------------------------------------------
2388 -- SQL WHAT-get the value for PO system Variables
2389 -- SQL WHY - Needed by Contracts to default in contract expert
2390 -- SQL JOIN- None
2391 IF l_is_mod = 'N' THEN -- CLM Mod
2392 SELECT
2393 poh.org_id
2394 ,poh.type_lookup_code
2395 ,poh.vendor_id
2396 ,poh.vendor_site_id
2397 ,pvs.state
2398 ,pvs.zip
2399 ,pvs.country
2400 ,poh.ship_to_location_id
2401 ,poh.bill_to_location_id
2402 ,poh.currency_code
2403 ,gsb.currency_code
2404 --Bug#3809298.Selecting the following columns also to calculate the
2405 --functional and transaction amounts.
2406 ,poh.type_lookup_code
2407 ,poh.rate
2408 ,cu.MINIMUM_ACCOUNTABLE_UNIT
2409 ,cu.precision
2410 ,cuf.MINIMUM_ACCOUNTABLE_UNIT
2411 ,cuf.precision
2412 --Bug#3809298.Commenting out the below two calculations of funational
2413 --and transaction amounts as they will be replaced subsequently.
2414 /*Start of commenting for Bug#3809298 .
2415 ,decode(poh.type_lookup_code, 'STANDARD',l_po_total_amount,0)
2416 ,round(round(
2417 decode (poh.type_lookup_code,
2418 'STANDARD',l_po_total_amount,0)
2419 * nvl(poh.rate,1)/nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cu.MINIMUM_ACCOUNTABLE_UNIT,null,cu.precision,0)
2420 ) * nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1) /
2421 nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cuf.MINIMUM_ACCOUNTABLE_UNIT,null,cuf.precision,0)
2422 )* nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1) po_total_amount_func
2423 End of Commenting.for Bug#3809298 */
2424 ,nvl(poh.blanket_total_amount,0)
2425 ,round(round(
2426 nvl(poh.blanket_total_amount,0) *
2427 nvl(poh.rate,1)/nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cu.MINIMUM_ACCOUNTABLE_UNIT,null,cu.precision,0)
2428 ) * nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1) /
2429 nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cuf.MINIMUM_ACCOUNTABLE_UNIT,null,cuf.precision,0)
2430 )* nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1) agreement_amount_func
2431 ,NVL(poh.global_agreement_flag,'N')
2432 ,poh.rate_type
2433 ,poh.terms_id
2434 ,poh. freight_terms_lookup_code
2435 ,poh. ship_via_lookup_code
2436 ,poh. fob_lookup_code
2437 ,poh.pay_on_code
2438 ,nvl(poh.supply_agreement_flag, 'N') --<CONTRACT EXPERT 11.5.10+>
2439 ,nvl(poh.min_release_amount,0)
2440 ,round(round(
2441 nvl(poh. min_release_amount,0) *
2442 nvl(poh.rate,1)/nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cu.MINIMUM_ACCOUNTABLE_UNIT,null,cu.precision,0)
2443 ) * nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1) /
2444 nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cuf.MINIMUM_ACCOUNTABLE_UNIT,null,cuf.precision,0)
2445 )* nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1) min_release_amount_func
2446 ,PO_CORE_S.get_default_legal_entity_id(poh.org_id) -- Bug 4654758, Bug 4691758
2447 ,poh.segment1
2448 ,poh.revision_num
2449 ,poh.vendor_contact_id
2450 ,poh.agent_id
2451 ,poh.rate_date
2452 ,poh.rate
2453 ,poh.acceptance_required_flag
2454 ,poh.acceptance_due_date
2455 ,poh.start_date
2456 ,poh.end_date
2457 ,poh.shipping_control --<HTML Agreement R12>
2458 ,poh.style_id || '-' || poh.type_lookup_code -- Bug 5063781
2459
2460 INTO
2461 l_po_attrib_tbl(1).variable_value_id
2462 ,l_po_attrib_tbl(2).variable_value_id
2463 ,l_po_attrib_tbl(3).variable_value_id
2464 ,l_po_attrib_tbl(4).variable_value_id
2465 ,l_po_attrib_tbl(5).variable_value_id
2466 ,l_po_attrib_tbl(6).variable_value_id
2467 ,l_po_attrib_tbl(7).variable_value_id
2468 ,l_po_attrib_tbl(8).variable_value_id
2469 ,l_po_attrib_tbl(9).variable_value_id
2470 ,l_po_attrib_tbl(10).variable_value_id
2471 ,l_po_attrib_tbl(11).variable_value_id
2472 --Bug#3809298.Commenting out l_po_attrib_tbl(12).variable_value_id
2473 --and l_po_attrib_tbl(13).variable_value_id and replacing them
2474 --with the local variables declared .
2475 /* ,l_po_attrib_tbl(12).variable_value_id
2476 ,l_po_attrib_tbl(13).variable_value_id */
2477 ,l_poh_type_lookup_code
2478 ,l_poh_rate
2479 ,l_cu_MINIMUM_ACCOUNTABLE_UNIT
2480 ,l_cu_precision
2481 ,l_cuf_MINIMUM_ACCOUNTABLE_UNIT
2482 ,l_cuf_precision
2483 --Bug#3809298.
2484 ,l_po_attrib_tbl(14).variable_value_id
2485 ,l_po_attrib_tbl(15).variable_value_id
2486 ,l_po_attrib_tbl(16).variable_value_id
2487 ,l_po_attrib_tbl(17).variable_value_id
2488 ,l_po_attrib_tbl(18).variable_value_id
2489 ,l_po_attrib_tbl(19).variable_value_id
2490 ,l_po_attrib_tbl(20).variable_value_id
2491 ,l_po_attrib_tbl(21).variable_value_id
2492 ,l_po_attrib_tbl(22).variable_value_id
2493 ,l_po_attrib_tbl(23).variable_value_id
2494 ,l_po_attrib_tbl(24).variable_value_id
2495 ,l_po_attrib_tbl(25).variable_value_id
2496 ,l_po_attrib_tbl(26).variable_value_id
2497 ,l_po_attrib_tbl(27).variable_value_id
2498 ,l_po_attrib_tbl(28).variable_value_id
2499 ,l_po_attrib_tbl(29).variable_value_id
2500 ,l_po_attrib_tbl(30).variable_value_id
2501 ,l_po_attrib_tbl(31).variable_value_id
2502 ,l_po_attrib_tbl(32).variable_value_id
2503 ,l_po_attrib_tbl(33).variable_value_id
2504 ,l_po_attrib_tbl(34).variable_value_id
2505 ,l_po_attrib_tbl(35).variable_value_id
2506 ,l_po_attrib_tbl(36).variable_value_id
2507 ,l_po_attrib_tbl(87).variable_value_id --<HTML Agreement R12>
2508 ,l_po_attrib_tbl(88).variable_value_id --<R12 STYLES PHASE II>
2509
2510 FROM
2511 po_headers_all poh
2512 ,FINANCIALS_SYSTEM_PARAMS_ALL FP
2513 ,FND_CURRENCIES_VL CU
2514 ,GL_SETS_OF_BOOKS gsb
2515 ,FND_CURRENCIES_VL cuf
2516 ,po_vendor_sites_all pvs
2517 WHERE
2518 poh.po_header_id = p_doc_id
2519 AND poh.vendor_site_id = pvs.vendor_site_id(+)
2520 AND poh.currency_code = cu.currency_code
2521 AND nvl(poh.org_id,-99) = nvl(fp.org_id,-99)
2522 AND FP.set_of_books_id = gsb.set_of_books_id
2523 AND cuf.currency_code = gsb.currency_code
2524 ;
2525 ELSE -- CLM Mod
2526 SELECT
2527 poh.org_id
2528 ,poh.type_lookup_code
2529 ,poh.vendor_id
2530 ,poh.vendor_site_id
2531 ,pvs.state
2532 ,pvs.zip
2533 ,pvs.country
2534 ,poh.ship_to_location_id
2535 ,poh.bill_to_location_id
2536 ,poh.currency_code
2537 ,gsb.currency_code
2538 --Bug#3809298.Selecting the following columns also to calculate the
2539 --functional and transaction amounts.
2540 ,poh.type_lookup_code
2541 ,poh.rate
2542 ,cu.MINIMUM_ACCOUNTABLE_UNIT
2543 ,cu.precision
2544 ,cuf.MINIMUM_ACCOUNTABLE_UNIT
2545 ,cuf.precision
2546 --Bug#3809298.Commenting out the below two calculations of funational
2547 --and transaction amounts as they will be replaced subsequently.
2548 /*Start of commenting for Bug#3809298 .
2549 ,decode(poh.type_lookup_code, 'STANDARD',l_po_total_amount,0)
2550 ,round(round(
2551 decode (poh.type_lookup_code,
2552 'STANDARD',l_po_total_amount,0)
2553 * nvl(poh.rate,1)/nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cu.MINIMUM_ACCOUNTABLE_UNIT,null,cu.precision,0)
2554 ) * nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1) /
2555 nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cuf.MINIMUM_ACCOUNTABLE_UNIT,null,cuf.precision,0)
2556 )* nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1) po_total_amount_func
2557 End of Commenting.for Bug#3809298 */
2558 ,nvl(poh.blanket_total_amount,0)
2559 ,round(round(
2560 nvl(poh.blanket_total_amount,0) *
2561 nvl(poh.rate,1)/nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cu.MINIMUM_ACCOUNTABLE_UNIT,null,cu.precision,0)
2562 ) * nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1) /
2563 nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cuf.MINIMUM_ACCOUNTABLE_UNIT,null,cuf.precision,0)
2564 )* nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1) agreement_amount_func
2565 ,NVL(poh.global_agreement_flag,'N')
2566 ,poh.rate_type
2567 ,poh.terms_id
2568 ,poh. freight_terms_lookup_code
2569 ,poh. ship_via_lookup_code
2570 ,poh. fob_lookup_code
2571 ,poh.pay_on_code
2572 ,nvl(poh.supply_agreement_flag, 'N') --<CONTRACT EXPERT 11.5.10+>
2573 ,nvl(poh.min_release_amount,0)
2574 ,round(round(
2575 nvl(poh. min_release_amount,0) *
2576 nvl(poh.rate,1)/nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cu.MINIMUM_ACCOUNTABLE_UNIT,null,cu.precision,0)
2577 ) * nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1) /
2578 nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cuf.MINIMUM_ACCOUNTABLE_UNIT,null,cuf.precision,0)
2579 )* nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1) min_release_amount_func
2580 ,PO_CORE_S.get_default_legal_entity_id(poh.org_id) -- Bug 4654758, Bug 4691758
2581 ,poh.segment1
2582 ,poh.revision_num
2583 ,poh.vendor_contact_id
2584 ,poh.agent_id
2585 ,poh.rate_date
2586 ,poh.rate
2587 ,poh.acceptance_required_flag
2588 ,poh.acceptance_due_date
2589 ,poh.start_date
2590 ,poh.end_date
2591 ,poh.shipping_control --<HTML Agreement R12>
2592 ,poh.style_id || '-' || poh.type_lookup_code -- Bug 5063781
2593
2594 INTO
2595 l_po_attrib_tbl(1).variable_value_id
2596 ,l_po_attrib_tbl(2).variable_value_id
2597 ,l_po_attrib_tbl(3).variable_value_id
2598 ,l_po_attrib_tbl(4).variable_value_id
2599 ,l_po_attrib_tbl(5).variable_value_id
2600 ,l_po_attrib_tbl(6).variable_value_id
2601 ,l_po_attrib_tbl(7).variable_value_id
2602 ,l_po_attrib_tbl(8).variable_value_id
2603 ,l_po_attrib_tbl(9).variable_value_id
2604 ,l_po_attrib_tbl(10).variable_value_id
2605 ,l_po_attrib_tbl(11).variable_value_id
2606 --Bug#3809298.Commenting out l_po_attrib_tbl(12).variable_value_id
2607 --and l_po_attrib_tbl(13).variable_value_id and replacing them
2608 --with the local variables declared .
2609 /* ,l_po_attrib_tbl(12).variable_value_id
2610 ,l_po_attrib_tbl(13).variable_value_id */
2611 ,l_poh_type_lookup_code
2612 ,l_poh_rate
2613 ,l_cu_MINIMUM_ACCOUNTABLE_UNIT
2614 ,l_cu_precision
2615 ,l_cuf_MINIMUM_ACCOUNTABLE_UNIT
2616 ,l_cuf_precision
2617 --Bug#3809298.
2618 ,l_po_attrib_tbl(14).variable_value_id
2619 ,l_po_attrib_tbl(15).variable_value_id
2620 ,l_po_attrib_tbl(16).variable_value_id
2621 ,l_po_attrib_tbl(17).variable_value_id
2622 ,l_po_attrib_tbl(18).variable_value_id
2623 ,l_po_attrib_tbl(19).variable_value_id
2624 ,l_po_attrib_tbl(20).variable_value_id
2625 ,l_po_attrib_tbl(21).variable_value_id
2626 ,l_po_attrib_tbl(22).variable_value_id
2627 ,l_po_attrib_tbl(23).variable_value_id
2628 ,l_po_attrib_tbl(24).variable_value_id
2629 ,l_po_attrib_tbl(25).variable_value_id
2630 ,l_po_attrib_tbl(26).variable_value_id
2631 ,l_po_attrib_tbl(27).variable_value_id
2632 ,l_po_attrib_tbl(28).variable_value_id
2633 ,l_po_attrib_tbl(29).variable_value_id
2634 ,l_po_attrib_tbl(30).variable_value_id
2635 ,l_po_attrib_tbl(31).variable_value_id
2636 ,l_po_attrib_tbl(32).variable_value_id
2637 ,l_po_attrib_tbl(33).variable_value_id
2638 ,l_po_attrib_tbl(34).variable_value_id
2639 ,l_po_attrib_tbl(35).variable_value_id
2640 ,l_po_attrib_tbl(36).variable_value_id
2641 ,l_po_attrib_tbl(87).variable_value_id --<HTML Agreement R12>
2642 ,l_po_attrib_tbl(88).variable_value_id --<R12 STYLES PHASE II>
2643
2644 FROM
2645 po_headers_draft_all poh
2646 ,FINANCIALS_SYSTEM_PARAMS_ALL FP
2647 ,FND_CURRENCIES_VL CU
2648 ,GL_SETS_OF_BOOKS gsb
2649 ,FND_CURRENCIES_VL cuf
2650 ,po_vendor_sites_all pvs
2651 WHERE
2652 poh.draft_id = p_doc_id
2653 AND poh.vendor_site_id = pvs.vendor_site_id(+)
2654 AND poh.currency_code = cu.currency_code
2655 AND nvl(poh.org_id,-99) = nvl(fp.org_id,-99)
2656 AND FP.set_of_books_id = gsb.set_of_books_id
2657 AND cuf.currency_code = gsb.currency_code
2658 ;
2659 END IF; -- CLM Mod
2660
2661 --Bug#3809298.Check if the document type is "Standard"
2662 --If so then calculate the amount in functional and transaction currency.
2663 --If not assign zero those two.
2664 IF(l_poh_type_lookup_code='STANDARD')THEN
2665 l_po_total_amount :=po_core_s.get_total('H',p_doc_id);
2666 l_po_attrib_tbl(12).variable_value_id :=l_po_total_amount;
2667 SELECT ROUND
2668 (
2669 ROUND ( l_po_total_amount * NVL (l_poh_rate, 1) / NVL (l_cu_minimum_accountable_unit, 1),
2670 DECODE (l_cu_minimum_accountable_unit, NULL, l_cu_precision, 0)
2671 )
2672 * NVL (l_cu_minimum_accountable_unit, 1) / NVL (l_cuf_minimum_accountable_unit, 1),
2673 DECODE (l_cuf_minimum_accountable_unit, NULL, l_cuf_precision, 0)
2674 )
2675 * NVL (l_cuf_minimum_accountable_unit, 1) po_total_amount_func
2676 INTO l_po_attrib_tbl(13).variable_value_id
2677 FROM DUAL;
2678 ELSE
2679 l_po_attrib_tbl(12).variable_value_id :=0;
2680 l_po_attrib_tbl(13).variable_value_id :=0;
2681
2682 END IF;
2683 --Bug#3809298.End of bug.
2684
2685 IF g_fnd_debug = 'Y' then
2686 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2687 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2688 MODULE =>g_module_prefix||l_api_name,
2689 MESSAGE =>'50: First p_sys_var'||p_sys_var_value_tbl.FIRST);
2690 END IF;
2691 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2692 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2693 MODULE =>g_module_prefix||l_api_name,
2694 MESSAGE =>'60: Last p_sys_var'||p_sys_var_value_tbl.LAST);
2695 END IF;
2696 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2697 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2698 MODULE =>g_module_prefix||l_api_name,
2699 MESSAGE =>'70: first l_po_var'||p_sys_var_value_tbl.FIRST);
2700 END IF;
2701 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2702 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2703 MODULE =>g_module_prefix||l_api_name,
2704 MESSAGE =>'80: last l_po_var'||p_sys_var_value_tbl.LAST);
2705 END IF;
2706 END IF;
2707 -- filter the changed value sent by contracts
2708 l_sys_var_index := p_sys_var_value_tbl.FIRST;
2709 While l_sys_var_index <= p_sys_var_value_tbl.last
2710 LOOP
2711 IF g_fnd_debug = 'Y' then
2712 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2713 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2714 MODULE =>g_module_prefix||l_api_name,
2715 MESSAGE =>'100: current p_sys_var index'||l_sys_var_index);
2716 END IF;
2717 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2718 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2719 MODULE =>g_module_prefix||l_api_name,
2720 MESSAGE =>'110: current p_sys_var value code'
2721 ||p_sys_var_value_tbl(l_sys_var_index).variable_code);
2722 END IF;
2723 END IF;
2724 l_found := false;
2725 l_po_attribute_index := l_po_attrib_tbl.FIRST;
2726 While l_po_attribute_index <= l_po_attrib_tbl.LAST
2727 LOOP
2728 IF g_fnd_debug = 'Y' then
2729 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2730 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2731 MODULE =>g_module_prefix||l_api_name,
2732 MESSAGE =>'160: current l_po_var index'||l_po_attribute_index);
2733 END IF;
2734 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2735 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2736 MODULE =>g_module_prefix||l_api_name,
2737 MESSAGE =>'165: next l_po_var value'||l_po_attrib_tbl.next(l_po_attribute_index));
2738 END IF;
2739 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2740 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2741 MODULE =>g_module_prefix||l_api_name,
2742 MESSAGE =>'165: current l_po_var value'||l_po_attrib_tbl(l_po_attribute_index).variable_code);
2743 END IF;
2744 END IF;
2745
2746 If l_po_attrib_tbl(l_po_attribute_index).variable_code =
2747 p_sys_var_value_tbl(l_sys_var_index).variable_code then
2748 IF g_fnd_debug = 'Y' then
2749 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2750 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2751 MODULE =>g_module_prefix||l_api_name,
2752 MESSAGE =>'180: value found for'||l_po_attrib_tbl(l_po_attribute_index).variable_code);
2753 END IF;
2754 END IF;
2755 p_sys_var_value_tbl(l_sys_var_index).variable_value_id :=
2756 l_po_attrib_tbl(l_po_attribute_index).variable_value_id;
2757 l_found:=true;
2758 Exit;
2759 END IF;-- if l_po_attrib_tbl variable code found in p_sys_var-value_tbl
2760 l_po_attribute_index := l_po_attrib_tbl.next(l_po_attribute_index);
2761 END LOOP;-- l_po_attribute_index inner loop
2762 If NOT l_found then
2763
2764 IF g_fnd_debug = 'Y' then
2765 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2766 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2767 MODULE =>g_module_prefix||l_api_name,
2768 MESSAGE =>'190: Po does not have the variable code'||
2769 p_sys_var_value_tbl(l_sys_var_index).variable_code);
2770 END IF;
2771 END IF;
2772
2773
2774 End if;
2775
2776 l_sys_var_index := p_sys_var_value_tbl.next(l_sys_var_index);
2777
2778 END LOOP;-- l_sys_var_index outer loop
2779 IF g_fnd_debug = 'Y' then
2780 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2781 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2782 MODULE =>g_module_prefix||l_api_name,
2783 MESSAGE =>'220: Filtering ended. element in p_sys_var_value'||p_sys_var_value_tbl.count);
2784 END IF;
2785
2786 l_sys_var_index := p_sys_var_Value_tbl.FIRST;
2787 While l_sys_var_index <= p_sys_var_value_tbl.last
2788 LOOP
2789 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2790 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2791 MODULE =>g_module_prefix||l_api_name,
2792 MESSAGE =>'240: current index' ||l_sys_var_index);
2793 END IF;
2794 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2795 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2796 MODULE =>g_module_prefix||l_api_name,
2797 MESSAGE =>'250: column being sent'||p_sys_var_value_tbl(l_sys_var_index).variable_code);
2798 END IF;
2799 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2800 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2801 MODULE =>g_module_prefix||l_api_name,
2802 MESSAGE =>'270: value being sent'||p_sys_var_value_tbl(l_sys_var_index).variable_value_id);
2803 END IF;
2804 l_sys_var_index := p_sys_var_value_tbl.next(l_sys_var_index);
2805 end loop;
2806 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2807 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
2808 MODULE =>g_module_prefix||l_api_name,
2809 MESSAGE =>'300: End API ' ||l_api_name);
2810 END IF;
2811 END IF;-- if fnd debug
2812 EXCEPTION
2813
2814 WHEN FND_API.G_EXC_ERROR then
2815 x_return_status := FND_API.G_RET_STS_ERROR;
2816 FND_MSG_PUB.Count_And_Get
2817 (p_count => x_msg_count,
2818 p_data => x_msg_data );
2819 IF g_fnd_debug = 'Y' then
2820 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2821 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
2822 MODULE =>g_module_prefix||l_api_name,
2823 MESSAGE =>'400:Exception Expected error ');
2824 END IF;
2825 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
2826 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2827 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
2828 MODULE =>g_module_prefix||l_api_name,
2829 MESSAGE =>'610:errors '||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
2830 END IF;
2831 END LOOP;
2832 END IF;
2833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
2834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2835 FND_MSG_PUB.Count_And_Get
2836 (p_count => x_msg_count,
2837 p_data => x_msg_data );
2838 IF g_fnd_debug = 'Y' then
2839 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2840 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
2841 MODULE =>g_module_prefix||l_api_name,
2842 MESSAGE =>'410:Exception Expected error ');
2843 END IF;
2844 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
2845 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2846 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
2847 MODULE =>g_module_prefix||l_api_name,
2848 MESSAGE =>'410:errors '||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
2849 END IF;
2850 END LOOP;
2851
2852 END IF;
2853
2854 WHEN OTHERS THEN
2855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2856 IF FND_MSG_PUB.Check_Msg_Level
2857 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2858 THEN
2859 FND_MSG_PUB.Add_Exc_Msg
2860 (p_pkg_name => 'PO_CONTERMS_UTILS_GRP',
2861 p_procedure_name => l_api_name);
2862 END IF; --msg level
2863 FND_MSG_PUB.Count_And_Get
2864 (p_count => x_msg_count,
2865 p_data => x_msg_data );
2866 IF g_fnd_debug = 'Y' then
2867 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2868 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
2869 MODULE =>g_module_prefix||l_api_name,
2870 MESSAGE =>'500:Exception UnExpected error '||sqlcode||':'||sqlerrm);
2871 END IF;
2872 END IF;
2873 End Get_PO_Attribute_values;
2874
2875
2876 -------------------------------------------------------------------------------
2877 --Start of Comments
2878 --Name: Get_Last_Signed_Revision
2879 --Pre-reqs:
2880 -- None
2881 --Modifies:
2882 -- None
2883 --Locks:
2884 -- None
2885 --Function:
2886 -- This API will be called by Contracts to get the last signed document revision
2887 --Parameters:
2888 --IN:
2889 --p_api_version
2890 -- Standard Parameter. API version number expected by the caller
2891 --p_init_msg_list
2892 -- Standard parameter.Initialize message list
2893 --p_doc_type
2894 -- OKC Document type
2895 --p_header_id
2896 -- PO header id
2897 --p_revision_num
2898 -- Document Revision Number
2899 --OUT:
2900 --x_msg_count
2901 -- Standard parameter.Message count
2902 --x_msg_data
2903 -- Standard parameter.message data
2904 --x_return_status
2905 -- Standard parameter. Status Returned to calling API. Possible values are following
2906 -- FND_API.G_RET_STS_ERROR - for expected error
2907 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
2908 -- FND_API.G_RET_STS_SUCCESS - for success
2909 --x_signed_records
2910 -- Returns 'Y' if there exists a Signed record. Otherwise returns 'N'
2911 --Notes:
2912 -- 09/23/2003 rbairraj
2913 -- 1. This API has been written specifically for integration with contracts Before using it in any other place
2914 -- Please diagnose the impact
2915 --
2916 --Testing:
2917 -- Testing to be done based on the test cases in Binding DLD
2918 --End of Comments
2919 -------------------------------------------------------------------------------
2920 PROCEDURE Get_Last_Signed_Revision (
2921 p_api_version IN NUMBER,
2922 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2923 p_doc_type IN VARCHAR2 := NULL, -- CLM Mod project
2924 p_header_id IN NUMBER,
2925 p_revision_num IN NUMBER,
2926 x_signed_revision_num OUT NOCOPY NUMBER,
2927 x_signed_records OUT NOCOPY VARCHAR2,
2928 x_return_status OUT NOCOPY VARCHAR2,
2929 x_msg_data OUT NOCOPY VARCHAR2,
2930 x_msg_count OUT NOCOPY NUMBER)
2931 IS
2932 l_api_name CONSTANT VARCHAR2(30) := 'get_last_signed_revision';
2933 l_api_version CONSTANT NUMBER := 1.0;
2934
2935 l_archived_conterms_flag PO_headers_all.conterms_exist_Flag%Type :='N';
2936 l_signed_revision_num NUMBER;
2937 l_signed_records VARCHAR2(1);
2938 l_is_mod VARCHAR2(1) := 'N'; -- CLM Mod
2939
2940 BEGIN
2941
2942 IF NOT (FND_API.compatible_api_call(l_api_version
2943 ,p_api_version
2944 ,l_api_name
2945 ,g_pkg_name)) THEN
2946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2947 END IF;
2948
2949 -- initialize API return status to success
2950 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2951
2952 -- initialize meesage list
2953 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2954 FND_MSG_PUB.initialize;
2955 END IF;
2956
2957
2958 /* Mod changes */
2959 IF p_doc_type = 'PO_STANDARD_MOD' OR
2960 p_doc_type = 'PA_BLANKET_MOD' OR --<Bug 15834898>
2961 p_doc_type = 'PA_CONTRACT_MOD' THEN --<Bug 15834898>
2962 l_is_mod := 'Y';
2963 END IF;
2964 /* Mod changes */
2965
2966 IF l_is_mod = 'N' THEN -- CLM Mod
2967 PO_SIGNATURE_PVT.get_last_signed_revision(
2968 p_po_header_id => p_header_id,
2969 p_revision_num => p_revision_num,
2970 x_signed_revision_num => l_signed_revision_num,
2971 x_signed_records => l_signed_records,
2972 x_return_status => x_return_status);
2973
2974 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2976 END IF;
2977
2978 IF l_signed_revision_num is not null THEN
2979 -- Migrate PO
2980 -- Check if this last signed revision has contract terms
2981 -- Before sending it to contracts
2982 BEGIN
2983
2984 SELECT poha.conterms_exist_flag
2985 INTO l_archived_conterms_flag
2986 FROM po_headers_archive_all poha
2987 WHERE poha.po_header_id = p_header_id
2988 AND poha.revision_num = l_signed_revision_num;
2989
2990 EXCEPTION
2991 When no_data_found then
2992 l_archived_conterms_flag := 'N';
2993 END;
2994
2995 IF NVL(l_archived_conterms_flag,'N') = 'Y' THEN
2996 x_signed_records := l_signed_records;
2997 x_signed_revision_num := l_signed_revision_num;
2998 ELSE
2999 x_signed_records := 'N';
3000 x_signed_revision_num := NULL;
3001 END IF;
3002
3003 ELSE
3004 -- If the revision num is null return the out parameters as is
3005 x_signed_records := l_signed_records;
3006 x_signed_revision_num := l_signed_revision_num;
3007 END IF;
3008 END IF; -- l_is_mod = 'N' THEN -- CLM Mod
3009
3010 EXCEPTION
3011 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3013 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
3014 ,p_data => x_msg_data);
3015 WHEN OTHERS THEN
3016 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3017 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3018 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3019 IF (g_fnd_debug='Y') THEN
3020 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3021 FND_LOG.string(log_level => FND_LOG.level_unexpected
3022 ,module => g_module_prefix ||l_api_name
3023 ,message => SQLERRM);
3024 END IF;
3025 END IF;
3026 END IF;
3027 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
3028 ,p_data => x_msg_data);
3029 END Get_Last_Signed_Revision;
3030
3031 ----------------------------------------------------------------------------------
3032 --Start of Comments
3033 --<11i10+ Auto Apply Contracts>
3034 --Name: Auto_Apply_Conterms()
3035 --Pre-reqs:
3036 -- None.
3037 --Modifies:
3038 -- None.
3039 --Locks:
3040 -- None.
3041 --Function:
3042 -- This procedure is a wrapper to call the OKC API's to get the default template
3043 -- and apply it to the current document type
3044 --Parameters:
3045 --IN:
3046 --p_document_id
3047 -- PO header ID
3048 --p_template_id
3049 -- defaulted template id
3050 --OUT:
3051 --x_msg_count
3052 -- Message count
3053 --x_msg_data
3054 -- message data
3055 --x_return_status
3056 -- FND_API.G_RET_STS_ERROR - for expected error
3057 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
3058 -- FND_API.G_RET_STS_SUCCESS - for success
3059 --Testing:
3060 --
3061 --End of Comments
3062 ------------------------------------------------------------------------------
3063 PROCEDURE Auto_Apply_ConTerms (
3064 p_document_id IN NUMBER,
3065 p_template_id IN NUMBER,
3066 x_return_status OUT NOCOPY VARCHAR2) IS
3067
3068 l_api_name CONSTANT VARCHAR2(30) := 'Auto_Apply_ConTerms';
3069 l_log_head CONSTANT VARCHAR2(100) := g_log_head||l_api_name;
3070
3071 l_template_id OKC_TERMS_TEMPLATES_ALL.template_id%TYPE;
3072 l_template_name OKC_TERMS_TEMPLATES_ALL.template_name%TYPE;
3073 l_template_desc OKC_TERMS_TEMPLATES_ALL.description%TYPE;
3074 l_k_doc_type VARCHAR2(240);
3075 l_document_type PO_HEADERS_ALL.type_lookup_code%TYPE;
3076 l_doc_number PO_HEADERS_ALL.segment1%TYPE; -- Bug 4096095
3077 l_agent_id PO_HEADERS_ALL.agent_id%TYPE; -- Bug 4096095
3078 l_vendor_id PO_HEADERS_ALL.vendor_id%TYPE; -- Bug 4096095
3079 l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE; -- Bug 4096095
3080 l_status PO_HEADERS_ALL.authorization_status%TYPE;
3081 l_conterms_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
3082 l_revision PO_HEADERS_ALL.revision_num%TYPE;
3083 l_org_id PO_HEADERS_ALL.org_id%TYPE;
3084 l_msg_data VARCHAR2(2000);
3085 l_msg_count NUMBER;
3086
3087 l_progress NUMBER := 0;
3088
3089 BEGIN
3090 l_progress := 10;
3091
3092 IF g_debug_stmt THEN
3093 PO_DEBUG.debug_begin(l_log_head);
3094 END IF;
3095
3096 l_progress := 20;
3097 -- Initialize API return status to success
3098 x_return_status := FND_API.G_RET_STS_SUCCESS;
3099
3100 l_progress := 25;
3101 IF g_contracts_enabled = 'N' OR
3102 g_auto_apply_template = 'N' THEN
3103
3104 l_progress := 30;
3105 IF g_debug_stmt THEN
3106 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3107 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3108 || l_progress,'Auto Apply Template is off or contracts in not enabled');
3109 END IF;
3110 END IF;
3111
3112 Return;
3113 END IF;
3114
3115 l_progress := 35;
3116 IF g_debug_stmt THEN
3117 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3118 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3119 || l_progress,'Derive parameters for OKC calls');
3120 END IF;
3121 END IF;
3122
3123 -- SQL What: Get the document type ,segment1 and org id from the document
3124 -- that is passed in
3125 -- SQL Why: These are required by the OKC calls to get and apply the
3126 -- default template
3127 Begin
3128 SELECT poh.type_lookup_code,
3129 poh.org_id,
3130 poh.segment1, -- Bug 4096095
3131 nvl(poh.authorization_status,'INCOMPLETE'),
3132 poh.revision_num,
3133 poh.vendor_id,
3134 poh.vendor_site_id,
3135 poh.agent_id,
3136 poh.conterms_exist_flag
3137 INTO l_document_type,
3138 l_org_id,
3139 l_doc_number, -- Bug 4096095
3140 l_status,
3141 l_revision,
3142 l_vendor_id,
3143 l_vendor_site_id,
3144 l_agent_id,
3145 l_conterms_flag
3146 FROM po_headers_all poh
3147 WHERE poh.po_header_id = p_document_id;
3148 Exception
3149 When others then
3150 l_document_type := null;
3151 l_org_id := null;
3152 End;
3153
3154 -- Check the Contract terms auto Apply profile option value
3155 IF l_document_type not in ('STANDARD', 'BLANKET','CONTRACT') OR
3156 nvl(l_conterms_flag,'N') = 'Y' THEN
3157
3158 l_progress := 40;
3159 IF g_debug_stmt THEN
3160 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3161 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3162 || l_progress,'not the compatible doc type');
3163 END IF;
3164 END IF;
3165
3166 Return;
3167 END IF;
3168
3169 -- Create doc type code to be sent over to
3170 -- Contracts (consider doing a parameter)
3171 l_k_doc_type := get_po_contract_doctype(l_document_type);
3172
3173 -- we make a call out to the OKC API to get the default
3174 -- template and apply . This API will attach the default
3175 -- template to the given PO header and sets the
3176 -- conterms_exists_flag through a call back API
3177
3178 IF p_template_id is null THEN
3179
3180 l_progress := 50;
3181 IF g_debug_stmt THEN
3182 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3183 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3184 || l_progress,'Call OKC API to get default template');
3185 END IF;
3186 END IF;
3187
3188 OKC_TERMS_UTIL_GRP.GET_DEFAULT_TEMPLATE(
3189 p_api_version => 1.0,
3190 p_init_msg_list => FND_API.G_TRUE,
3191 x_return_status => x_return_status,
3192 x_msg_data => l_msg_data,
3193 x_msg_count => l_msg_count,
3194 p_document_type => l_k_doc_type,
3195 p_org_id => l_org_id,
3196 p_valid_date => sysdate,
3197 x_template_id => l_template_id,
3198 x_template_name => l_template_name,
3199 x_template_description => l_template_desc);
3200
3201 IF g_debug_stmt THEN
3202 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3203 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3204 || l_progress,'Return status from get default template:'||x_return_status );
3205 END IF;
3206 END IF;
3207
3208 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3210 END IF;
3211
3212 ELSE
3213 l_progress := 60;
3214 IF g_debug_stmt THEN
3215 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3216 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3217 || l_progress,'Default template passed in from the client side');
3218 END IF;
3219 END IF;
3220
3221 l_template_id := p_template_id;
3222 END IF;
3223
3224 IF l_template_id is not null THEN
3225
3226 l_progress := 70;
3227 IF g_debug_stmt THEN
3228 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3229 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3230 || l_progress,'Call the OKC API to apply terms');
3231 END IF;
3232 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3233 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3234 || l_progress ,'Template: ' || l_template_id);
3235 END IF;
3236 END IF;
3237
3238 -- Call the OKC API to apply the template
3239 -- Bug 4028805: Change '1.0' to 1.0 as api version should be a NUMBER.
3240 -- Bug 4096095: Passed in segment1 to the API
3241 OKC_TERMS_COPY_GRP.copy_terms(
3242 p_api_version => 1.0, --bug4028805
3243 x_return_status => x_return_status,
3244 x_msg_data => l_msg_data,
3245 x_msg_count => l_msg_count,
3246 p_commit => FND_API.G_FALSE,
3247 p_template_id => l_template_id,
3248 p_target_doc_type => l_k_doc_type,
3249 p_target_doc_id => p_document_id,
3250 p_document_number => l_doc_number, -- Bug 4096095
3251 p_internal_party_id => l_org_id, -- Bug 4096095
3252 p_external_party_id => l_vendor_id, -- Bug 4096095
3253 p_external_party_site_id => l_vendor_site_id, -- Bug 4096095
3254 p_retain_deliverable => 'N',
3255 p_internal_contact_id => l_agent_id,
3256 p_article_effective_date => sysdate,
3257 p_validate_commit => FND_API.G_TRUE,
3258 p_validation_string => l_status ||','||l_revision||','|| null
3259 );
3260
3261 IF g_debug_stmt THEN
3262 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3263 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3264 || l_progress,'Return status from copy terms:'||x_return_status );
3265 END IF;
3266 END IF;
3267 ELSE
3268 -- Do not apply any terms if the template is null
3269 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3271 END IF;
3272 END IF;
3273
3274 l_progress := 80;
3275 IF g_debug_stmt THEN
3276 PO_DEBUG.debug_end(l_log_head);
3277 END IF;
3278
3279 Exception
3280 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3282 FND_MSG_PUB.Count_and_Get(p_count => l_msg_count
3283 ,p_data => l_msg_data);
3284 WHEN OTHERS THEN
3285 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3286 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3287 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3288 IF (g_fnd_debug='Y') THEN
3289 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3290 FND_LOG.string(log_level => FND_LOG.level_unexpected
3291 ,module => g_module_prefix ||l_api_name
3292 ,message => SQLERRM);
3293 END IF;
3294 END IF;
3295 END IF;
3296 FND_MSG_PUB.Count_and_Get(p_count => l_msg_count
3297 ,p_data => l_msg_data);
3298 End Auto_Apply_ConTerms;
3299
3300
3301 --CLM Mod
3302 ----------------------------------------------------------------------------------
3303 --Start of Comments
3304 --Name: Copy_Conterms_Mod()
3305 --Pre-reqs:
3306 -- None.
3307 --Modifies:
3308 -- None.
3309 --Locks:
3310 -- None.
3311 --Function:
3312 -- This procedure is a wrapper to call the OKC API's to get the default template
3313 -- and apply it to the current document type
3314 --Parameters:
3315 --IN:
3316 --p_document_id
3317 -- PO header ID
3318 --p_template_id
3319 -- defaulted template id
3320 --p_mode
3321 -- mode for calling this procedure. Values can be:
3322 -- CREATE, SYNC, MERGE, PAR_MERGE
3323 --OUT:
3324 --x_msg_count
3325 -- Message count
3326 --x_msg_data
3327 -- message data
3328 --x_return_status
3329 -- FND_API.G_RET_STS_ERROR - for expected error
3330 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
3331 -- FND_API.G_RET_STS_SUCCESS - for success
3332 --Testing:
3333 --
3334 --End of Comments
3335 ------------------------------------------------------------------------------
3336 --PAR Project : Rewriting whoel procedure for PAR project
3337 PROCEDURE Copy_Conterms_Mod(
3338 p_document_id IN NUMBER,
3339 p_draft_id IN NUMBER,
3340 p_to_mod IN VARCHAR2 := 'N',
3341 p_mode IN VARCHAR2 := 'CREATE',
3342 p_target_draft_id IN NUMBER DEFAULT NULL, -- PAR Project
3343 x_return_status OUT NOCOPY VARCHAR2) IS
3344
3345 l_api_name CONSTANT VARCHAR2(30) := 'Copy_ConTerms_Mod';
3346 l_log_head CONSTANT VARCHAR2(100) := g_log_head||l_api_name;
3347
3348 l_document_type PO_HEADERS_ALL.type_lookup_code%TYPE;
3349 l_doc_number PO_HEADERS_ALL.segment1%TYPE; -- Bug 4096095
3350 l_conterms_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
3351 l_conterms_flag_mod PO_HEADERS_ALL.conterms_exist_flag%TYPE;
3352 l_msg_data VARCHAR2(2000);
3353 l_msg_count NUMBER;
3354 l_k_doc_type_source VARCHAR2(240);
3355 l_k_doc_type_target VARCHAR2(240);
3356 l_doc_source NUMBER;
3357 l_doc_target NUMBER;
3358 terms_exist VARCHAR2(1);
3359 deliverables_exist VARCHAR2(1);
3360 l_progress NUMBER := 0;
3361 -- PAR Project
3362 l_target_draft_id NUMBER;
3363 l_target_doc_num po_drafts.modification_number%TYPE;
3364 BEGIN
3365 l_progress := 10;
3366
3367 IF g_debug_stmt THEN
3368 PO_DEBUG.debug_begin(l_log_head);
3369 END IF;
3370
3371 l_progress := 20;
3372 -- Initialize API return status to success
3373 x_return_status := FND_API.G_RET_STS_SUCCESS;
3374
3375 l_progress := 25;
3376 IF g_contracts_enabled = 'N' THEN
3377 l_progress := 30;
3378 Return;
3379 END IF;
3380
3381 l_progress := 35;
3382 IF g_debug_stmt THEN
3383 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3384 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3385 || l_progress,'Derive parameters for OKC calls');
3386 END IF;
3387 END IF;
3388
3389 -- SQL What: Get the document type ,segment1 and org id from the document
3390 -- that is passed in
3391 -- SQL Why: These are required by the OKC calls to get and apply the
3392 -- default template
3393 IF p_to_mod = 'Y' THEN
3394 Begin
3395 SELECT poh.type_lookup_code,
3396 poh.clm_document_number,
3397 nvl(poh.conterms_exist_flag, 'N')
3398 INTO l_document_type,
3399 l_doc_number,
3400 l_conterms_flag
3401 FROM po_headers_all poh
3402 WHERE poh.po_header_id = p_document_id;
3403 Exception
3404 When others then
3405 l_document_type := null;
3406 End;
3407 ELSE
3408 Begin
3409 SELECT phda.type_lookup_code,
3410 phda.clm_document_number,
3411 nvl(phda.conterms_exist_flag, 'N')
3412 INTO l_document_type,
3413 l_doc_number,
3414 l_conterms_flag_mod
3415 FROM po_headers_draft_all phda
3416 WHERE phda.po_header_id = p_document_id
3417 AND phda.draft_id = p_draft_id;
3418 Exception
3419 When others then
3420 l_document_type := null;
3421 End;
3422 END IF;
3423 -- Check the Contract terms auto Apply profile option value
3424 IF l_document_type not in ('STANDARD', 'BLANKET','CONTRACT') OR
3425 (p_to_mod = 'N' AND l_conterms_flag_mod = 'N') OR
3426 (p_to_mod = 'Y' AND l_conterms_flag = 'N') THEN
3427
3428 l_progress := 40;
3429 IF g_debug_stmt THEN
3430 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3431 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3432 || l_progress,'nothing to copy');
3433 END IF;
3434 END IF;
3435
3436
3437 Return;
3438 END IF;
3439
3440 -- Create doc type code to be sent over to
3441 IF p_to_mod = 'Y' THEN
3442 l_k_doc_type_source := get_po_contract_doctype(l_document_type);
3443 l_k_doc_type_target := get_po_contract_doctype_mod(l_document_type);
3444 l_doc_source := p_document_id;
3445 l_doc_target := p_draft_id;
3446 ELSE
3447 l_k_doc_type_source := get_po_contract_doctype_mod(l_document_type);
3448 l_doc_source := p_draft_id;
3449 -- In case of PAR_MERGE, target doc should be 'PO_STANDARD_MOD' itself.
3450 IF p_mode ='PAR_MERGE' THEN
3451 l_k_doc_type_target := get_po_contract_doctype_mod(l_document_type);
3452 l_doc_target := p_draft_id;
3453 ELSIF p_mode = 'MERGE' THEN
3454 l_k_doc_type_target := get_po_contract_doctype(l_document_type);
3455 l_doc_target := p_document_id;
3456 END IF;
3457 END IF;
3458
3459 -- Get target document number.
3460 -- For mode = MERGE, target document = Award Number.
3461 -- For mode = 'CREATE', 'SYNC', target document = Modification Number.
3462 -- For mode = PAR_MERGE, target document = Modification Number created after autocreate.
3463 IF p_mode = 'MERGE' THEN
3464 l_target_doc_num := l_doc_number;
3465 ELSE
3466 IF p_mode IN ('CREATE', 'SYNC') THEN
3467 l_target_draft_id := p_draft_id;
3468 ELSIF p_mode ='PAR_MERGE' AND p_target_draft_id IS NOT NULL THEN
3469 l_target_draft_id := p_target_draft_id;
3470 END IF;
3471 -- Get modification number from po_drafts.
3472 SELECT modification_number
3473 INTO l_target_doc_num
3474 FROM po_drafts
3475 WHERE draft_id = l_target_draft_id;
3476 END IF;
3477
3478 -- <Conc Mods Project>
3479 -- During Sync we copy terms from the Confirmed Award to Mod
3480 -- In that case even if the terms exists on the modification document,
3481 -- we shoud copy the changes from the Award to Mod.
3482 -- In case of Sync p_to_mod is 'Y'
3483 -- Added condition - p_mode <> 'SYNC'
3484 IF p_to_mod = 'Y' AND p_mode <> 'SYNC' THEN
3485 terms_exist := OKC_TERMS_UTIL_GRP.has_terms(l_k_doc_type_target, l_doc_target);
3486 --16669316 : Check for deliverables(Cdrls) as well.
3487 deliverables_exist := OKC_TERMS_UTIL_GRP.has_deliverables(l_k_doc_type_target, l_doc_target);
3488 IF terms_exist = 'Y' OR deliverables_exist = 'Y' then
3489 l_progress := 50;
3490 IF g_debug_stmt THEN
3491 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3492 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
3493 || l_progress,'already copied');
3494 END IF;
3495 END IF;
3496 return;
3497 END IF;
3498 END IF;
3499
3500 -- Call the OKC API to porcess mod terms
3501 OKC_TERMS_COPY_GRP.process_mod_terms(
3502 p_api_version => 1.0,
3503 p_init_msg_list => FND_API.G_TRUE,
3504 p_commit => FND_API.G_FALSE,
3505 p_process => p_mode,
3506 p_source_doc_type => l_k_doc_type_source,
3507 p_source_doc_id => l_doc_source,
3508 p_target_doc_type => l_k_doc_type_target,
3509 p_target_doc_id => l_doc_target,
3510 p_target_document_number => l_doc_number,
3511 x_return_status => x_return_status,
3512 x_msg_data => l_msg_data,
3513 x_msg_count => l_msg_count);
3514
3515 EXCEPTION
3516 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3518 FND_MSG_PUB.Count_and_Get(p_count => l_msg_count
3519 ,p_data => l_msg_data);
3520 WHEN OTHERS THEN
3521 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3522 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3523 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3524 IF (g_fnd_debug='Y') THEN
3525 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3526 FND_LOG.string(log_level => FND_LOG.level_unexpected
3527 ,module => g_module_prefix ||l_api_name
3528 ,message => SQLERRM);
3529 END IF;
3530 END IF;
3531 END IF;
3532 FND_MSG_PUB.Count_and_Get(p_count => l_msg_count
3533 ,p_data => l_msg_data);
3534 End Copy_Conterms_Mod;
3535 --CLM Mod
3536
3537 ----------------------------------------------------------------------------------
3538 --Start of Comments
3539 --<Auto Apply Contracts>
3540 --Name: get_def_proc_contract_info()
3541 --Pre-reqs:
3542 -- None.
3543 --Modifies:
3544 -- None.
3545 --Locks:
3546 -- None.
3547 --Function:
3548 -- This procedure is a wrapper to call the OKC API's to get the default template
3549 --Parameters:
3550 --IN:
3551 --p_doc_subtype
3552 -- PO Doc Subtype
3553 --p_org_id
3554 -- Org Id
3555 --p_conterms_exist_flag
3556 -- Flag indicating if conterms exists
3557 --OUT:
3558 --x_template_id
3559 -- defaulted template id
3560 --x_template_name
3561 -- defaulted template name
3562 --x_return_status
3563 -- FND_API.G_RET_STS_ERROR - for expected error
3564 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
3565 -- FND_API.G_RET_STS_SUCCESS - for success
3566 --Testing:
3567 --
3568 --End of Comments
3569 ------------------------------------------------------------------------------
3570 PROCEDURE get_def_proc_contract_info (
3571 p_doc_subtype IN VARCHAR2,
3572 p_org_id IN NUMBER,
3573 p_conterms_exist_flag IN VARCHAR2,
3574 x_template_id OUT NOCOPY VARCHAR2,
3575 x_template_name OUT NOCOPY VARCHAR2,
3576 x_authoring_party OUT NOCOPY VARCHAR2,
3577 x_return_status OUT NOCOPY VARCHAR2) IS
3578
3579 l_api_name CONSTANT VARCHAR2 (30) := 'get_def_proc_contract_info';
3580 l_log_head CONSTANT VARCHAR2 (100) := g_log_head || l_api_name;
3581 l_template_desc okc_terms_templates_all.description%TYPE;
3582 l_contract_source VARCHAR2(2000);
3583 l_k_doc_type VARCHAR2 (240);
3584 l_msg_data VARCHAR2 (2000);
3585 l_msg_count NUMBER;
3586 l_progress NUMBER := 0;
3587 BEGIN
3588 l_progress := 10;
3589
3590 IF g_debug_stmt
3591 THEN
3592 po_debug.debug_begin (l_log_head);
3593 END IF;
3594
3595 l_progress := 20;
3596 -- Initialize API return status to success
3597 x_return_status := fnd_api.g_ret_sts_success;
3598 l_progress := 25;
3599
3600 IF g_contracts_enabled = 'N' OR g_auto_apply_template = 'N'
3601 THEN
3602 l_progress := 30;
3603
3604 IF g_debug_stmt
3605 THEN
3606 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3607 fnd_log.STRING (fnd_log.level_statement,
3608 g_log_head || '.' || l_api_name || '.' || l_progress,
3609 'Auto Apply Template is off or contracts in not enabled'
3610 );
3611 END IF;
3612 END IF;
3613
3614 RETURN;
3615 END IF;
3616
3617 -- Check the Contract terms auto Apply profile option value
3618 IF p_doc_subtype NOT IN ('STANDARD', 'BLANKET', 'CONTRACT')
3619 OR NVL (p_conterms_exist_flag, 'N') = 'Y'
3620 THEN
3621 l_progress := 40;
3622
3623 IF g_debug_stmt
3624 THEN
3625 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3626 fnd_log.STRING (fnd_log.level_statement,
3627 g_log_head || '.' || l_api_name || '.' || l_progress,
3628 'not the compatible doc type'
3629 );
3630 END IF;
3631 END IF;
3632
3633 RETURN;
3634 END IF;
3635
3636 -- Create doc type code to be sent over to
3637 -- Contracts (consider doing a parameter)
3638 l_k_doc_type := get_po_contract_doctype(p_doc_subtype);
3639
3640 -- we make a call out to the OKC API to get the default
3641 -- template.
3642 l_progress := 50;
3643
3644 IF g_debug_stmt
3645 THEN
3646 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3647 fnd_log.STRING (fnd_log.level_statement,
3648 g_log_head || '.' || l_api_name || '.' || l_progress,
3649 'Call OKC API to get default template'
3650 );
3651 END IF;
3652 END IF;
3653
3654 okc_terms_util_grp.get_default_template (
3655 p_api_version => 1.0,
3656 p_init_msg_list => fnd_api.g_true,
3657 x_return_status => x_return_status,
3658 x_msg_data => l_msg_data,
3659 x_msg_count => l_msg_count,
3660 p_document_type => l_k_doc_type,
3661 p_org_id => p_org_id,
3662 p_valid_date => SYSDATE,
3663 x_template_id => x_template_id,
3664 x_template_name => x_template_name,
3665 x_template_description => l_template_desc
3666 );
3667
3668 IF g_debug_stmt
3669 THEN
3670 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3671 fnd_log.STRING (fnd_log.level_statement,
3672 g_log_head || '.' || l_api_name || '.' || l_progress,
3673 'Return status from get default template:'
3674 || x_return_status
3675 );
3676 END IF;
3677 END IF;
3678
3679 IF x_return_status <> fnd_api.g_ret_sts_success
3680 THEN
3681 RAISE fnd_api.g_exc_unexpected_error;
3682 END IF;
3683
3684 IF x_template_id IS NOT NULL
3685 THEN
3686 okc_terms_util_grp.get_contract_defaults (
3687 p_api_version => 1.0,
3688 p_init_msg_list => fnd_api.g_true,
3689 x_return_status => x_return_status,
3690 x_msg_data => l_msg_data,
3691 x_msg_count => l_msg_count,
3692 p_template_id => x_template_id,
3693 p_document_type => l_k_doc_type,
3694 x_authoring_party => x_authoring_party,
3695 x_contract_source => l_contract_source,
3696 x_template_name => x_template_name,
3697 x_template_description => l_template_desc
3698 );
3699
3700 IF x_return_status <> fnd_api.g_ret_sts_success
3701 THEN
3702 RAISE fnd_api.g_exc_unexpected_error;
3703 END IF;
3704 END IF;
3705
3706 l_progress := 60;
3707
3708 IF g_debug_stmt
3709 THEN
3710 po_debug.debug_end (l_log_head);
3711 END IF;
3712 EXCEPTION
3713 WHEN fnd_api.g_exc_unexpected_error
3714 THEN
3715 x_return_status := fnd_api.g_ret_sts_unexp_error;
3716 fnd_msg_pub.count_and_get (p_count => l_msg_count,
3717 p_data => l_msg_data);
3718 WHEN OTHERS
3719 THEN
3720 x_return_status := fnd_api.g_ret_sts_unexp_error;
3721
3722 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3723 THEN
3724 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3725
3726 IF (g_fnd_debug = 'Y')
3727 THEN
3728 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3729 fnd_log.STRING (log_level => fnd_log.level_unexpected,
3730 module => g_module_prefix || l_api_name,
3731 MESSAGE => SQLERRM
3732 );
3733 END IF;
3734 END IF;
3735 END IF;
3736
3737 fnd_msg_pub.count_and_get (p_count => l_msg_count,
3738 p_data => l_msg_data);
3739 END get_def_proc_contract_info;
3740
3741 ----------------------------------------------------------------------------------
3742 --Start of Comments
3743 --<R12 Procurement Contracts Integration>
3744 --Name: Get_Contract_Details()
3745 --Pre-reqs:
3746 -- None.
3747 --Modifies:
3748 -- None.
3749 --Locks:
3750 -- None.
3751 --Function:
3752 -- This procedure is a wrapper to call the OKC API's to get contract details
3753 --Parameters:
3754 --IN:
3755 --p_doc_subtype
3756 -- PO Doc Subtype
3757 --p_document_id
3758 -- PO Header ID
3759 --OUT:
3760 --x_template_name
3761 -- template name
3762 --x_authoring_party
3763 -- authoring party
3764 --x_return_status
3765 -- FND_API.G_RET_STS_ERROR - for expected error
3766 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
3767 -- FND_API.G_RET_STS_SUCCESS - for success
3768 --Testing:
3769 --
3770 --End of Comments
3771 ------------------------------------------------------------------------------
3772 Procedure Get_Contract_Details(
3773 x_return_status OUT NOCOPY VARCHAR2,
3774 p_doc_type IN VARCHAR2 := NULL,
3775 p_doc_subtype IN VARCHAR2,
3776 p_document_id IN NUMBER,
3777 x_authoring_party OUT NOCOPY VARCHAR2,
3778 x_template_name OUT NOCOPY VARCHAR2
3779 ) IS
3780
3781 l_api_version CONSTANT NUMBER := 1;
3782 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Contract_Details';
3783 l_log_head CONSTANT VARCHAR2 (100) := g_log_head || l_api_name;
3784 l_template_desc okc_terms_templates_all.description%TYPE;
3785 l_contract_source VARCHAR2(2000);
3786 l_k_doc_type VARCHAR2(240);
3787 l_msg_data VARCHAR2(2000);
3788 l_msg_count NUMBER;
3789 l_progress NUMBER := 0;
3790 l_template_name_none_msg okc_terms_templates_all.template_name%TYPE;
3791 l_authoring_party_none_msg okc_template_usages.authoring_party_code%TYPE;
3792 BEGIN
3793
3794
3795 -- Check that the doc subtype is allowed.
3796 IF p_doc_subtype NOT IN ('STANDARD', 'BLANKET', 'CONTRACT')
3797 THEN
3798 l_progress := 20;
3799 IF g_debug_stmt
3800 THEN
3801 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3802 fnd_log.STRING (fnd_log.level_statement,
3803 g_log_head || '.' || l_api_name || '.' || l_progress,
3804 'not the compatible doc type'
3805 );
3806 END IF;
3807 END IF;
3808
3809 RETURN;
3810 END IF;
3811
3812 l_k_doc_type := get_po_contract_doctype(p_doc_subtype);
3813
3814 -- we make a call out to the OKC API to get the default
3815 -- template.
3816 l_progress := 10;
3817
3818 IF g_debug_stmt
3819 THEN
3820 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3821 fnd_log.STRING (fnd_log.level_statement,
3822 g_log_head || '.' || l_api_name || '.' || l_progress,
3823 'Call OKC API to get contract details'
3824 );
3825 END IF;
3826 END IF;
3827
3828 OKC_TERMS_UTIL_GRP.Get_Contract_Details(
3829 p_api_version => l_api_version,
3830 p_init_msg_list => FND_API.G_TRUE,
3831 x_return_status => x_return_status,
3832 x_msg_data => l_msg_data,
3833 x_msg_count => l_msg_count,
3834 p_document_type => l_k_doc_type,
3835 p_document_id => p_document_id,
3836 x_authoring_party => x_authoring_party,
3837 x_contract_source => l_contract_source,
3838 x_template_name => x_template_name,
3839 x_template_description => l_template_desc
3840 );
3841
3842 l_progress := 20;
3843
3844 IF g_debug_stmt
3845 THEN
3846 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3847 fnd_log.STRING (fnd_log.level_statement,
3848 g_log_head || '.' || l_api_name || '.' || l_progress,
3849 'Return status from get contract details:'
3850 || x_return_status
3851 );
3852 END IF;
3853 END IF;
3854
3855 IF x_return_status <> fnd_api.g_ret_sts_success
3856 THEN
3857 RAISE fnd_api.g_exc_unexpected_error;
3858 END IF;
3859
3860 -- Bug 4691053
3861 -- Override template name and authoring party names to be null in case
3862 -- contract terms do not exist.
3863
3864 fnd_message.set_name('OKC','OKC_TERMS_TEMPLATE_NAME_NONE');
3865 l_template_name_none_msg:= fnd_message.get;
3866
3867 IF x_template_name = l_template_name_none_msg THEN
3868 x_template_name := NULL;
3869 END IF;
3870
3871 fnd_message.set_name('OKC','OKC_TERMS_AUTH_PARTY_NONE');
3872 l_authoring_party_none_msg := fnd_message.get;
3873
3874 IF x_authoring_party = l_authoring_party_none_msg THEN
3875 x_authoring_party := NULL;
3876 END IF;
3877
3878 EXCEPTION
3879 WHEN fnd_api.g_exc_unexpected_error
3880 THEN
3881 x_return_status := fnd_api.g_ret_sts_unexp_error;
3882 fnd_msg_pub.count_and_get (p_count => l_msg_count,
3883 p_data => l_msg_data);
3884 WHEN OTHERS
3885 THEN
3886 x_return_status := fnd_api.g_ret_sts_unexp_error;
3887
3888 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3889 THEN
3890 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3891
3892 IF (g_fnd_debug = 'Y')
3893 THEN
3894 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3895 fnd_log.STRING (log_level => fnd_log.level_unexpected,
3896 module => g_module_prefix || l_api_name,
3897 MESSAGE => SQLERRM
3898 );
3899 END IF;
3900 END IF;
3901 END IF;
3902
3903 fnd_msg_pub.count_and_get (p_count => l_msg_count,
3904 p_data => l_msg_data);
3905 END Get_Contract_Details;
3906
3907 ----------------------------------------------------------------------------------
3908 --Start of Comments
3909 --<FP CU2-R12 : Migrate PO>
3910 --Name: get_archive_conterms_flag()
3911 --Pre-reqs:
3912 -- None.
3913 --Modifies:
3914 -- None.
3915 --Locks:
3916 -- None.
3917 --Function:
3918 -- This function checks if the latest archived version has conterms
3919 --Parameters:
3920 --IN:
3921 --p_document_id
3922 -- PO header ID
3923 --Testing:
3924 --
3925 --End of Comments
3926 ------------------------------------------------------------------------------
3927 FUNCTION get_archive_conterms_flag (p_po_header_id IN NUMBER)
3928 RETURN VARCHAR2 IS
3929
3930 l_archived_conterms_flag PO_headers_all.conterms_exist_Flag%Type :='N';
3931
3932 BEGIN
3933 -- SQL What: Query to check if the last archived version had conterms
3934 -- SQL Why: To check if the conterms were applied to the last archived rev
3935 SELECT nvl(poha.conterms_exist_flag,'N')
3936 INTO l_archived_conterms_flag
3937 FROM po_headers_archive_all poha
3938 WHERE poha.po_header_id = p_po_header_id
3939 AND poha.latest_external_flag = 'Y';
3940
3941 Return l_archived_conterms_flag;
3942
3943 EXCEPTION
3944 -- Never Archived
3945 When no_data_found THEN
3946 l_archived_conterms_flag := 'X';
3947
3948 END get_archive_conterms_flag;
3949
3950 END PO_CONTERMS_UTL_GRP;
[Home] [Help]