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