[Home] [Help]
PACKAGE BODY: APPS.OE_CONTRACTS_UTIL
Source
1 PACKAGE BODY OE_Contracts_util AS
2 /* $Header: OEXUOKCB.pls 120.6 2011/07/14 13:32:33 snimmaga ship $ */
3
4 --ETR
5 ------------------------------------------------------------------------------
6 -- GLOBAL CONSTANTS
7 ------------------------------------------------------------------------------
8 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
9 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
10
11 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
12 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
13 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
14
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Contracts_Util';
16 --ETR
17
18 G_CURRENT_VERSION_NUMBER CONSTANT NUMBER := -99; /* Note: the contract document attachment creation java API always creates the current
19 version of the attachment as -99 during the workflow approval process.
20 (the contract document attachment creation java API increments the version number
21 from 0,1... later after the attachment has been archived once) */
22
23
24 /* this function is used to simply return the value of G_BSA_DOC_TYPE
25 used within forms libraries to access G_BSA_DOC_TYPE as the PL/SQL implementation of
26 the PL/SQL version used in forms does not allow direct reference to G_BSA_DOC_TYPE */
27 FUNCTION get_G_BSA_DOC_TYPE
28 RETURN VARCHAR2 IS
29 BEGIN
30 RETURN (OE_Contracts_util.G_BSA_DOC_TYPE);
31 END;
32
33
34 /* this function is used to simply return the value of G_SO_DOC_TYPE
35 used within forms libraries to access G_SO_DOC_TYPE as the PL/SQL implementation of
36 the PL/SQL version used in forms does not allow direct reference to G_SO_DOC_TYPE */
37 FUNCTION get_G_SO_DOC_TYPE
38 RETURN VARCHAR2 IS
39 BEGIN
40 RETURN (OE_Contracts_util.G_SO_DOC_TYPE);
41 END;
42
43
44 FUNCTION check_license
45 RETURN VARCHAR2 IS
46
47
48 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
49 l_sales_contracts_enabled VARCHAR2(3);
50 l_dummy BOOLEAN;
51 lx_status VARCHAR2(1);
52 lx_industry VARCHAR2(1);
53 lx_oracle_schema VARCHAR2(30);
54
55 BEGIN
56 IF l_debug_level > 0 THEN
57 oe_debug_pub.add('In OE_Contracts_util.check_license', 1);
58 END IF;
59
60 IF OE_CONTRACTS_UTIL.G_CNTR_LICENSED IS NULL OR
61 OE_CONTRACTS_UTIL.G_CNTR_LICENSED NOT IN ('Y','N') THEN
62 --this is the first time this check is being performed
63
64 --first verify if contracts is installed
65 l_dummy := FND_INSTALLATION.get_app_info (
66 application_short_name => 'OKC',
67 status => lx_status,
68 industry => lx_industry,
69 oracle_schema => lx_oracle_schema
70 );
71
72 IF l_debug_level > 0 THEN
73 oe_debug_pub.add('performing check first time, lx_status: ' || lx_status, 3);
74 END IF;
75
76 IF lx_status = 'I' THEN
77 --contracts is installed so check profile option to see if it is also licensed
78
79 l_sales_contracts_enabled := NVL(FND_PROFILE.VALUE('OKC_ENABLE_SALES_CONTRACTS'),'N');
80 l_sales_contracts_enabled := UPPER(SUBSTR(l_sales_contracts_enabled,1,1)); -- take 'Y' or 'N'
81
82 IF l_debug_level > 0 THEN
83 oe_debug_pub.add('l_sales_contracts_enabled: ' || l_sales_contracts_enabled, 3);
84 END IF;
85
86 IF l_sales_contracts_enabled = 'Y' THEN
87 OE_CONTRACTS_UTIL.G_CNTR_LICENSED := 'Y';
88 RETURN 'Y';
89 ELSIF l_sales_contracts_enabled = 'N' THEN
90 OE_CONTRACTS_UTIL.G_CNTR_LICENSED := 'N';
91 RETURN 'N';
92 ELSE
93 OE_CONTRACTS_UTIL.G_CNTR_LICENSED := 'N';
94 RETURN 'N';
95 END IF;
96
97 ELSE
98 --contracts is not installed at all
99 OE_CONTRACTS_UTIL.G_CNTR_LICENSED := 'N';
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.add('Contracts is not installed at all, returning ''N''', 3);
102 END IF;
103 RETURN 'N';
104 END IF;
105
106
107 -- we have already performed the license check earlier so reuse the cache
108 ELSE
109 IF l_debug_level > 0 THEN
110 oe_debug_pub.add('cache already has value, OE_CONTRACTS_UTIL.G_BSA_CNTR_LICENSE: ' ||OE_CONTRACTS_UTIL.G_CNTR_LICENSED, 3);
111 END IF;
112
113 RETURN OE_CONTRACTS_UTIL.G_CNTR_LICENSED;
114 END IF;
115
116
117 IF l_debug_level > 0 THEN
118 oe_debug_pub.add('End of OE_Contracts_util.check_license', 1);
119 END IF;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123
124 IF l_debug_level > 0 THEN
125 oe_debug_pub.add('WHEN-OTHERS in check_license', 1);
126 END IF;
127
128 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
129 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
130 'check_license'
131 );
132 END IF;
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134
135 END check_license;
136
137
138
139 --Copy Document Articles
140 PROCEDURE copy_articles
141 (
142 p_api_version IN NUMBER,
143 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
144 p_commit IN VARCHAR2 := FND_API.G_FALSE,
145
146 p_doc_type IN VARCHAR2,
147 p_copy_from_doc_id IN NUMBER,
148 p_version_number IN VARCHAR2 DEFAULT NULL,
149 p_copy_to_doc_id IN NUMBER,
150 p_copy_to_doc_start_date IN DATE := SYSDATE,
151 p_keep_version IN VARCHAR2 := 'N',
152 p_copy_to_doc_number IN NUMBER DEFAULT NULL,
153
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2
157 ) IS
158
159 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
160 l_copy_to_doc_number VARCHAR2(30) := TO_CHAR(p_copy_to_doc_number);
161
162 l_doc_type VARCHAR2(5) := p_doc_type;
163 l_copy_to_doc_id NUMBER := p_copy_to_doc_id;
164
165
166 l_latest_version_number NUMBER;
167
168 BEGIN
169
170 IF l_debug_level > 0 THEN
171 oe_debug_pub.add('In OE_Contracts_util.copy_articles ', 1);
172 END IF;
173
174
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176
177 IF p_init_msg_list = FND_API.G_TRUE THEN
178 oe_msg_pub.initialize;
179 END IF;
180
181
182 --Check for licensing
183 IF OE_Contracts_util.check_license() <> 'Y' THEN
184 IF l_debug_level > 0 THEN
185 oe_debug_pub.add('Contractual option not licensed, hence not performing copy ', 3);
186 END IF;
187 RETURN;
188 END IF;
189
190
191 --checking if version number passed is the latest BSA or Sales Order version
192 IF p_version_number IS NOT NULL THEN
193 oe_debug_pub.add('Checking for latest version number for for p_doc_type: '|| p_doc_type
194 || ' and p_copy_from_doc_id: ' || p_copy_from_doc_id, 3);
195
196 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
197 SELECT version_number
198 INTO l_latest_version_number
199 FROM oe_blanket_headers_all
200 WHERE header_id = p_copy_from_doc_id;
201
202 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
203 SELECT version_number
204 INTO l_latest_version_number
205 FROM oe_order_headers_all
206 WHERE header_id = p_copy_from_doc_id;
207
208 END IF;
209 oe_debug_pub.add('l_latest_version_number for p_doc_type: '||p_doc_type || ' and p_copy_from_doc_id: '
210 || p_copy_from_doc_id || ' is: ' || l_latest_version_number, 3);
211 END IF;
212
213
214
215 -----IF p_version_number = FND_API.G_MISS_CHAR THEN
216 IF p_version_number IS NULL OR
217 p_version_number = l_latest_version_number THEN
218 --we are in the context of copying from a BSA or Sales Order to create a new BSA or Sales Order
219
220 IF l_debug_level > 0 THEN
221 oe_debug_pub.add('Calling OKC_TERMS_COPY_GRP.copy_doc ', 3);
222 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
223 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
224 oe_debug_pub.add('p_commit: ' || p_commit,3);
225 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
226 oe_debug_pub.add('p_copy_from_doc_id: ' || p_copy_from_doc_id, 3);
227 oe_debug_pub.add('p_copy_to_doc_id: ' || p_copy_to_doc_id, 3);
228 oe_debug_pub.add('p_keep_version: ' || p_keep_version, 3);
229 oe_debug_pub.add('p_copy_to_doc_start_date: ' || p_copy_to_doc_start_date, 3);
230 oe_debug_pub.add('l_copy_to_doc_number: ' || l_copy_to_doc_number, 3);
231 END IF;
232
233 OKC_TERMS_COPY_GRP.copy_doc (
234 p_api_version => p_api_version,
235 p_init_msg_list => p_init_msg_list,
236 p_commit => p_commit,
237 p_source_doc_type => l_doc_type,
238 p_source_doc_id => p_copy_from_doc_id,
239 p_target_doc_type => l_doc_type,
240 p_target_doc_id => l_copy_to_doc_id,
241 p_keep_version => p_keep_version,
242 ---------p_article_effective_date => p_copy_to_doc_start_date,
243 p_article_effective_date => NULL, -- we should not pass effectivity date ref: Bug 3307561
244 p_document_number => l_copy_to_doc_number,
245 ----p_reinitialize_deliverables: defaulted, not passed
246 x_return_status => x_return_status,
247 x_msg_data => x_msg_data,
248 x_msg_count => x_msg_count,
249 p_copy_abstract_yn => 'N'
250 );
251
252 IF l_debug_level > 0 THEN
253 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
254 END IF;
255
256 --ETR
257 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
259 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
260 RAISE FND_API.G_EXC_ERROR ;
261 END IF;
262 --ETR
263
264 ELSIF p_version_number <> FND_API.G_MISS_CHAR AND
265 p_version_number <> l_latest_version_number THEN
266 --we are in the context of copying from an archived version of a BSA Sales Order to create a new BSA Sales Order
267
268 IF l_debug_level > 0 THEN
269 oe_debug_pub.add('Calling OKC_TERMS_COPY_GRP.copy_archived_doc ', 3);
270 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
271 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
272 oe_debug_pub.add('p_commit: ' || p_commit,3);
273 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
274 oe_debug_pub.add('p_copy_from_doc_id: ' || p_copy_from_doc_id, 3);
275 oe_debug_pub.add('p_version_number: ' || p_version_number, 3);
276 oe_debug_pub.add('p_copy_to_doc_id: ' || p_copy_to_doc_id, 3);
277 oe_debug_pub.add('p_keep_version: ' || p_keep_version, 3);
278 oe_debug_pub.add('l_copy_to_doc_number: ' || l_copy_to_doc_number, 3);
279 END IF;
280
281 OKC_TERMS_COPY_GRP.copy_archived_doc (
282 p_api_version => p_api_version,
283 p_init_msg_list => p_init_msg_list,
284 p_commit => p_commit,
285 p_source_doc_type => p_doc_type,
286 p_source_doc_id => p_copy_from_doc_id,
287 p_source_version_number => p_version_number,
288 p_target_doc_type => p_doc_type,
289 p_target_doc_id => p_copy_to_doc_id,
290 p_document_number => l_copy_to_doc_number,
291 x_return_status => x_return_status,
292 x_msg_data => x_msg_data,
293 x_msg_count => x_msg_count
294 );
295
296 IF l_debug_level > 0 THEN
297 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
298 END IF;
299
300 --ETR
301 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
303 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
304 RAISE FND_API.G_EXC_ERROR ;
305 END IF;
306 --ETR
307
308 END IF;
309
310
311 IF l_debug_level > 0 THEN
312 oe_debug_pub.add('End of OE_Contracts_util.copy_articles, x_return_status ' || x_return_status, 1);
313 END IF;
314
315 EXCEPTION
316 WHEN FND_API.G_EXC_ERROR THEN
317
318 IF l_debug_level > 0 THEN
319 oe_debug_pub.add('WHEN G_EXC_ERROR in copy_articles ', 3);
320 END IF;
321
322 x_return_status := FND_API.G_RET_STS_ERROR;
323
324 /*****
325 not needed as per meeting
326 --transfer error messages on OKC stack to OM stack
327 OE_MSG_PUB.Transfer_Msg_Stack;
328 *****/
329
330 --Get message count and data
331 OE_MSG_PUB.Count_And_Get (
332 p_count => x_msg_count,
333 p_data => x_msg_data
334 );
335
336
337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
338
339 IF l_debug_level > 0 THEN
340 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in copy_articles ', 3);
341 END IF;
342
343 --close any cursors
344
345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
346
347 /*****
348 not needed as per meeting
349 --transfer error messages on OKC stack to OM stack
350 OE_MSG_PUB.Transfer_Msg_Stack;
351 *****/
352
353 --Get message count and data
354 OE_MSG_PUB.Count_And_Get (
355 p_count => x_msg_count,
356 p_data => x_msg_data
357 );
358
359
360 WHEN OTHERS THEN
361
362 IF l_debug_level > 0 THEN
363 oe_debug_pub.add('WHEN OTHERS in copy_articles ', 3);
364 END IF;
365
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
367
368 /*****
369 not needed as per meeting
370 --transfer error messages on OKC stack to OM stack
371 OE_MSG_PUB.Transfer_Msg_Stack;
372 *****/
373
374 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
375 OE_MSG_PUB.Add_Exc_Msg (
376 G_PKG_NAME,
377 'copy_articles'
378 );
379 END IF;
380
381
382 --Get message count and data
383 OE_MSG_PUB.Count_And_Get (
384 p_count => x_msg_count,
385 p_data => x_msg_data
386 );
387
388
389 END copy_articles;
390
391
392
393
394 --Version articles of BSA or Sales Order
395 PROCEDURE version_articles
396 (
397 p_api_version IN NUMBER,
398 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
399 p_commit IN VARCHAR2 := FND_API.G_FALSE,
400
401 p_doc_type IN VARCHAR2,
402 p_doc_id IN NUMBER,
403 p_version_number IN VARCHAR2,
404 p_clear_amendment IN VARCHAR2 := 'Y',
405
406 x_return_status OUT NOCOPY VARCHAR2,
407 x_msg_count OUT NOCOPY NUMBER,
408 x_msg_data OUT NOCOPY VARCHAR2
409 ) IS
410
411 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
412
413 BEGIN
414
415 IF l_debug_level > 0 THEN
416 oe_debug_pub.add('In OE_Contracts_util.version_articles ', 1);
417 END IF;
418
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420
421 IF p_init_msg_list = FND_API.G_TRUE THEN
422 oe_msg_pub.initialize;
423 END IF;
424
425
426 --Check for licensing
427 IF OE_Contracts_util.check_license() <> 'Y' THEN
428 IF l_debug_level > 0 THEN
429 oe_debug_pub.add('Contractual option not licensed, hence not performing versioning ', 3);
430 END IF;
431 RETURN;
432 END IF;
433
434
435 IF l_debug_level > 0 THEN
436 oe_debug_pub.add('Calling OKC_TERMS_VERSION_GRP.version_doc ', 3);
437 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
438 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
439 oe_debug_pub.add('p_commit: ' || p_commit,3);
440 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
441 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
442 oe_debug_pub.add('p_version_number: ' || p_version_number, 3);
443 END IF;
444 --go ahead and version the articles belonging to the BSA or Sales Order
445 OKC_TERMS_VERSION_GRP.version_doc (
446 p_api_version => p_api_version,
447 p_init_msg_list => p_init_msg_list,
448 p_commit => p_commit,
449 p_doc_type => p_doc_type,
450 p_doc_id => p_doc_id,
451 p_version_number => p_version_number,
452 p_clear_amendment => p_clear_amendment,
453 x_return_status => x_return_status,
454 x_msg_data => x_msg_data,
455 x_msg_count => x_msg_count
456 );
457
458 IF l_debug_level > 0 THEN
459 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
460 END IF;
461
462 --ETR
463 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
465 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
466 RAISE FND_API.G_EXC_ERROR ;
467 END IF;
468 --ETR
469
470 IF l_debug_level > 0 THEN
471 oe_debug_pub.add('End of OE_Contracts_util.version_articles , x_return_status ' || x_return_status, 1);
472 END IF;
473
474 EXCEPTION
475 WHEN FND_API.G_EXC_ERROR THEN
476
477 IF l_debug_level > 0 THEN
478 oe_debug_pub.add('WHEN G_EXC_ERROR in version_articles ', 3);
479 END IF;
480
481 --close any cursors
482
483 x_return_status := FND_API.G_RET_STS_ERROR;
484
485 /*****
486 not needed as per meeting
487 --transfer error messages on OKC stack to OM stack
488 OE_MSG_PUB.Transfer_Msg_Stack;
489 *****/
490
491 --Get message count and data
492 OE_MSG_PUB.Count_And_Get (
493 p_count => x_msg_count,
494 p_data => x_msg_data
495 );
496
497
498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
499
500 IF l_debug_level > 0 THEN
501 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in version_articles ', 3);
502 END IF;
503
504 --close any cursors
505
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
507
508 /*****
509 not needed as per meeting
510 --transfer error messages on OKC stack to OM stack
511 OE_MSG_PUB.Transfer_Msg_Stack;
512 *****/
513
514 --Get message count and data
515 OE_MSG_PUB.Count_And_Get (
516 p_count => x_msg_count,
517 p_data => x_msg_data
518 );
519
520
521 WHEN OTHERS THEN
522
523 IF l_debug_level > 0 THEN
524 oe_debug_pub.add('WHEN OTHERS in version_articles ', 3);
525 END IF;
526
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528
529 /*****
530 not needed as per meeting
531 --transfer error messages on OKC stack to OM stack
532 OE_MSG_PUB.Transfer_Msg_Stack;
533 *****/
534
535 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
536 OE_MSG_PUB.Add_Exc_Msg (
537 G_PKG_NAME,
538 'version_articles'
539 );
540 END IF;
541
542
543 --Get message count and data
544 OE_MSG_PUB.Count_And_Get (
545 p_count => x_msg_count,
546 p_data => x_msg_data
547 );
548
549
550 END version_articles;
551
552
553
554
555 --perform QA checks upon the articles belonging to a BSA or Sales Order
556 PROCEDURE qa_articles
557 (
558 p_api_version IN NUMBER,
559 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
560 p_commit IN VARCHAR2 := FND_API.G_FALSE,
561
562 p_qa_mode IN VARCHAR2 := OKC_TERMS_QA_GRP.G_NORMAL_QA,
563 p_doc_type IN VARCHAR2,
564 p_doc_id IN NUMBER,
565
566 x_qa_return_status OUT NOCOPY VARCHAR2,
567 x_return_status OUT NOCOPY VARCHAR2,
568 x_msg_count OUT NOCOPY NUMBER,
569 x_msg_data OUT NOCOPY VARCHAR2
570 ) IS
571
572
573 l_article_exist VARCHAR2(100);
574 --ETR
575 l_order_signed VARCHAR2(1);
576 --ETR
577 lx_qa_result_tbl qa_result_tbl_type;
578 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
579 l_template_id NUMBER;
580 l_template_name VARCHAR2(240);
581 l_contract_source VARCHAR2(240);
582 l_authoring_party VARCHAR2(240);
583 l_contract_source_code VARCHAR2(240);
584 l_has_primary_doc VARCHAR2(240);
585 l_run_expert_flag VARCHAR2(1); --bug6318133
586
587 BEGIN
588
589 IF l_debug_level > 0 THEN
590 oe_debug_pub.add('In OE_Contracts_util.qa_articles ', 1);
591 END IF;
592
593 x_return_status := FND_API.G_RET_STS_SUCCESS;
594
595 IF p_init_msg_list = FND_API.G_TRUE THEN
596 oe_msg_pub.initialize;
597 END IF;
598
599
600 --Check for licensing
601 IF OE_Contracts_util.check_license() <> 'Y' THEN
602 IF l_debug_level > 0 THEN
603 oe_debug_pub.add('Contractual option not licensed, hence not performing article QA ', 3);
604 END IF;
605 RETURN;
606 END IF;
607
608 --ETR
609 IF p_doc_type = OE_CONTRACTS_UTIL.get_G_SO_DOC_TYPE() THEN
610 IF l_debug_level > 0 THEN
611 oe_debug_pub.add('Calling OE_CONTRACTS_UTIL_GRP.is_order_signed ', 3);
612 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
613 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
614 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
615 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
616 END IF;
617
618 --Check whether Order already signed ; if signed, QA already performed
619 l_order_signed := oe_contracts_util.is_order_signed (
620 p_api_version => p_api_version,
621 p_init_msg_list => p_init_msg_list,
622 p_doc_id => p_doc_id,
623 x_return_status => x_return_status,
624 x_msg_data => x_msg_data,
625 x_msg_count => x_msg_count
626 );
627 IF l_debug_level > 0 THEN
628 oe_debug_pub.add('l_order_signed: '|| l_order_signed, 3);
629 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
630 END IF;
631
632 --ETR
633 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
635 END IF;
636 --ETR
637
638 IF l_order_signed = 'Y' THEN
639 IF l_debug_level > 0 THEN
640 oe_debug_pub.add('SO signed and therefore already QAd, hence not performing article QA ', 3);
641 END IF;
642 RETURN;
643 END IF;
644 END IF; --IF p_doc_type = 'O' THEN
645 --ETR
646
647 IF l_debug_level > 0 THEN
648 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.is_article_exist ', 3);
649 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
650 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
651 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
652 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
653 END IF;
654
655 --Determine whether any articles exist for the BSA or Sales Order being QA'd
656 l_article_exist := OKC_TERMS_UTIL_GRP.is_article_exist (
657 p_api_version => p_api_version,
658 p_init_msg_list => p_init_msg_list,
659 p_doc_type => p_doc_type,
660 p_doc_id => p_doc_id,
661 x_return_status => x_return_status,
662 x_msg_data => x_msg_data,
663 x_msg_count => x_msg_count
664
665 );
666 IF l_debug_level > 0 THEN
667 oe_debug_pub.add('l_article_exist: '|| l_article_exist, 3);
668 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
669 END IF;
670
671 --ETR
672 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
674 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
675 RAISE FND_API.G_EXC_ERROR ;
676 END IF;
677 --ETR
678
679 IF l_article_exist <> OKC_TERMS_UTIL_GRP.G_NO_ARTICLE_EXIST THEN --i.e. 'NONE'
680 --i.e. proceed with articles QA process only if articles exist for the BSA
681 OE_CONTRACTS_UTIL.get_contract_details_all (
682 p_api_version => 1.0,
683 p_init_msg_list => 'F',
684 p_doc_type => p_doc_type,
685 p_doc_id => p_doc_id,
686 x_template_id => l_template_id,
687 x_authoring_party => l_authoring_party,
688 x_contract_source => l_contract_source,
689 x_contract_source_code => l_contract_source_code,
690 x_has_primary_doc => l_has_primary_doc,
691 x_template_name => l_template_name,
692 x_return_status => x_return_status,
693 x_msg_count => x_msg_count,
694 x_msg_data => x_msg_data
695 );
696
697 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
699 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
700 RAISE FND_API.G_EXC_ERROR ;
701 END IF;
702
703
704 IF l_debug_level > 0 THEN
705 oe_debug_pub.add('Calling OKC_TERMS_QA_GRP.QA_doc ', 3);
706 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
707 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
708 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
709 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
710 oe_debug_pub.add('p_qa_mode ' || p_qa_mode,3);
711 END IF;
712 --p_run_expert_flag added for fix 5186582 to skip expert validations in Quote to order
713
714 --bug6318133
715 IF p_doc_type = OE_CONTRACTS_UTIL.get_G_SO_DOC_TYPE() then
716 l_run_expert_flag := 'N';
717 ELSE
718 l_run_expert_flag := 'Y';
719 END IF;
720
721
722 OKC_TERMS_QA_GRP.QA_doc (
723 p_api_version => p_api_version,
724 p_init_msg_list => p_init_msg_list,
725 ----p_commit => p_commit,
726 p_qa_mode => p_qa_mode,
727 p_doc_type => p_doc_type,
728 p_doc_id => p_doc_id,
729 p_run_expert_flag => l_run_expert_flag,
730 x_qa_return_status => x_qa_return_status,
731 x_qa_result_tbl => lx_qa_result_tbl,
732
733 x_return_status => x_return_status,
734 x_msg_data => x_msg_data,
735 x_msg_count => x_msg_count
736 );
737
738 IF l_debug_level > 0 THEN
739 oe_debug_pub.add('x_qa_return_status: '|| x_qa_return_status, 3);
740 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
741 oe_debug_pub.add('lx_qa_result_tbl.COUNT: ' || lx_qa_result_tbl.COUNT, 3);
742 END IF;
743
744 IF l_contract_source_code = 'ATTACHED' AND l_has_primary_doc = 'N' THEN
745 --set qa return status to error
746 x_qa_return_status:= G_RET_STS_ERROR;
747 fnd_message.set_name('ONT','ONT_NO_PRIMARY_OKC_DOCUMENT');
748 OE_MSG_PUB.Add;
749
750 END IF;
751
752 --ETR
753 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
755 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
756 RAISE FND_API.G_EXC_ERROR ;
757 END IF;
758 --ETR
759
760 IF l_debug_level > 0 THEN
761 oe_debug_pub.add('retrieving QA messages from lx_qa_result_tbl and place them onto the FND error message stack ', 3);
762 END IF;
763
764 --retrieve QA messages from lx_qa_result_tbl and place them onto the FND error message stack
765 IF lx_qa_result_tbl.FIRST IS NOT NULL THEN
766 FOR i IN lx_qa_result_tbl.FIRST..lx_qa_result_tbl.LAST LOOP
767
768 IF lx_qa_result_tbl(i).Problem_details IS NOT NULL THEN
769 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
770 fnd_message.set_token('message', lx_qa_result_tbl(i).Problem_details); --!!!! need to use SUBSTR here ??!!!!!
771 --Note: Problem_details is a translated string provided by the QA
772 fnd_msg_pub.add;
773 END IF;
774 END LOOP;
775
776 IF l_debug_level > 0 THEN
777 oe_debug_pub.add('Transferring messages from FND stack to OM error message stack ', 3);
778 END IF;
779 OE_MSG_PUB.Transfer_Msg_Stack;
780
781 --Get message count and data
782 OE_MSG_PUB.Count_And_Get (
783 p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786
787 END IF;
788
789
790
791 END IF;
792
793
794 IF l_debug_level > 0 THEN
795 oe_debug_pub.add('End of OE_Contracts_util.qa_articles , x_return_status ' || x_return_status, 1);
796 END IF;
797
798 EXCEPTION
799 WHEN FND_API.G_EXC_ERROR THEN
800
801 IF l_debug_level > 0 THEN
802 oe_debug_pub.add('WHEN G_EXC_ERROR in qa_articles ', 3);
803 END IF;
804
805 --close any cursors
806
807 x_return_status := FND_API.G_RET_STS_ERROR;
808
809 /*****
810 not needed as per meeting
811 --transfer error messages on OKC stack to OM stack
812 OE_MSG_PUB.Transfer_Msg_Stack;
813 *****/
814
815 --Get message count and data
816 OE_MSG_PUB.Count_And_Get (
817 p_count => x_msg_count,
818 p_data => x_msg_data
819 );
820
821
822 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
823
824 IF l_debug_level > 0 THEN
825 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in qa_articles ', 3);
826 END IF;
827
828 --close any cursors
829
830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
831
832
833 /*****
834 not needed as per meeting
835 --transfer error messages on OKC stack to OM stack
836 OE_MSG_PUB.Transfer_Msg_Stack;
837 *****/
838
839 --Get message count and data
840 OE_MSG_PUB.Count_And_Get (
841 p_count => x_msg_count,
842 p_data => x_msg_data
843 );
844
845
846 WHEN OTHERS THEN
847
848 IF l_debug_level > 0 THEN
849 oe_debug_pub.add('WHEN OTHERS in qa_articles ', 3);
850 END IF;
851
852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
853
854
855 /*****
856 not needed as per meeting
857 --transfer error messages on OKC stack to OM stack
858 OE_MSG_PUB.Transfer_Msg_Stack;
859 *****/
860
861
862 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
863 OE_MSG_PUB.Add_Exc_Msg (
864 G_PKG_NAME,
865 'qa_articles'
866 );
867 END IF;
868
869 --Get message count and data
870 OE_MSG_PUB.Count_And_Get (
871 p_count => x_msg_count,
872 p_data => x_msg_data
873 );
874
875
876 END qa_articles;
877
878
879 --to determine whether any non standard articles exists for the BSA or Sales Order
880 --called from the approval workflow to determine whether non standard articles exist for the BSA or Sales Orders being approved
881 FUNCTION non_standard_article_exists
882 (
883 p_api_version IN NUMBER,
884 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
885 p_commit IN VARCHAR2 := FND_API.G_FALSE,
886 p_doc_type IN VARCHAR2,
887 p_doc_id IN NUMBER,
888 x_return_status OUT NOCOPY VARCHAR2,
889 x_msg_count OUT NOCOPY NUMBER,
890 x_msg_data OUT NOCOPY VARCHAR2
891 )
892
893 RETURN VARCHAR2 IS
894 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
895 l_article_type VARCHAR2(50);
896 BEGIN
897
898 IF l_debug_level > 0 THEN
899 oe_debug_pub.add('In OE_Contracts_util.non_standard_article_exists ', 1);
900 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
901 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
902 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.is_article_exist',3);
903 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
904 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
905 END IF;
906
907 x_return_status := FND_API.G_RET_STS_SUCCESS;
908
909 --Check for licensing
910 IF OE_Contracts_util.check_license() <> 'Y' THEN
911 IF l_debug_level > 0 THEN
912 oe_debug_pub.add('Contractual option not licensed, hence not performing non_standard_article_exists check ', 3);
913 END IF;
914 ---RETURN NULL;
915 RETURN 'N';
916 END IF;
917
918
919 l_article_type := OKC_TERMS_UTIL_GRP.is_article_exist (
920 p_api_version => p_api_version,
921 p_init_msg_list => p_init_msg_list,
922 p_doc_type => p_doc_type,
923 p_doc_id => p_doc_id,
924 x_return_status => x_return_status,
925 x_msg_count => x_msg_count,
926 x_msg_data => x_msg_data
927 );
928
929 IF l_debug_level > 0 THEN
930 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
931 oe_debug_pub.add('l_article_type: ' || l_article_type, 3);
932 END IF;
933
934 --ETR
935 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
936 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
937 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
938 RAISE FND_API.G_EXC_ERROR ;
939 END IF;
940 --ETR
941
942 IF l_article_type = OKC_TERMS_UTIL_GRP.G_NON_STANDARD_ART_EXIST THEN --i.e. 'NON_STANDARD_EXIST'
943 IF l_debug_level > 0 THEN
944 oe_debug_pub.add('End of OE_Contracts_util.non_standard_article_exists , returning Y');
945 END IF;
946 RETURN 'Y';
947 ELSE
948 IF l_debug_level > 0 THEN
949 oe_debug_pub.add('End of OE_Contracts_util.non_standard_article_exists , returning N');
950 END IF;
951 RETURN 'N';
952 END IF;
953
954
955 EXCEPTION
956 WHEN FND_API.G_EXC_ERROR THEN
957
958 IF l_debug_level > 0 THEN
959 oe_debug_pub.add('WHEN G_EXC_ERROR in non_standard_article_exists ', 3);
960 END IF;
961
962 x_return_status := FND_API.G_RET_STS_ERROR;
963
964 /*****
965 not needed as per meeting
966 --transfer error messages on OKC stack to OM stack
967 OE_MSG_PUB.Transfer_Msg_Stack;
968 *****/
969
970 --Get message count and data
971 OE_MSG_PUB.Count_And_Get (
972 p_count => x_msg_count,
973 p_data => x_msg_data
974 );
975
976
977 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
978
979 IF l_debug_level > 0 THEN
980 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in non_standard_article_exists ', 3);
981 END IF;
982
983 --close any cursors
984
985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
986
987 /*****
988 not needed as per meeting
989 --transfer error messages on OKC stack to OM stack
990 OE_MSG_PUB.Transfer_Msg_Stack;
991 *****/
992
993 --Get message count and data
994 OE_MSG_PUB.Count_And_Get (
995 p_count => x_msg_count,
996 p_data => x_msg_data
997 );
998
999
1000
1001 WHEN OTHERS THEN
1002 IF l_debug_level > 0 THEN
1003 oe_debug_pub.add('WHEN OTHERS in non_standard_article_exists ', 3);
1004 END IF;
1005
1006 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1007 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1008 'non_standard_article_exists'
1009 );
1010 END IF;
1011 -----RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1012
1013 --Get message count and data
1014 OE_MSG_PUB.Count_And_Get (
1015 p_count => x_msg_count,
1016 p_data => x_msg_data
1017 );
1018
1019
1020 END non_standard_article_exists;
1021
1022
1023 --workflow wrapper procedure for non_standard_article_exists()
1024 PROCEDURE WF_non_stndrd_article_exists (
1025 itemtype IN VARCHAR2,
1026 itemkey IN VARCHAR2,
1027 actid IN NUMBER,
1028 funcmode IN VARCHAR2,
1029 resultout OUT NOCOPY VARCHAR2) IS
1030
1031 l_non_standard_article_exists VARCHAR2(1);
1032 l_doc_id NUMBER; -- header id of BSA or sales order
1033 l_sales_document_type_code VARCHAR2(30); -- i.e. either 'B' or 'O'
1034
1035 l_api_version CONSTANT NUMBER := 1;
1036 l_api_name CONSTANT VARCHAR2(30) := 'WF_non_standard_article_exists';
1037 lx_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1038 lx_msg_count NUMBER := 0;
1039 lx_msg_data VARCHAR2(2000);
1040
1041 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1042
1043 BEGIN
1044
1045 IF l_debug_level > 0 THEN
1046 oe_debug_pub.add( 'ENTERING WF_non_standard_article_exists',1) ;
1047 END IF;
1048
1049 OE_STANDARD_WF.Set_Msg_Context(actid);
1050
1051 --get the header id of the BSA
1052 --header_id is the itemkey of the workflow
1053 l_doc_id := to_number(itemkey);
1054
1055 IF l_debug_level > 0 THEN
1056 oe_debug_pub.add('l_doc_id: ' || l_doc_id,3);
1057 END IF;
1058
1059 l_sales_document_type_code := wf_engine.GetItemAttrText(itemtype,
1060 itemkey,
1061 'SALES_DOCUMENT_TYPE_CODE');
1062 IF l_debug_level > 0 THEN
1063 oe_debug_pub.add('l_sales_document_type_code: ' || l_sales_document_type_code,3);
1064 oe_debug_pub.add('Calling non_standard_article_exists()',3);
1065 END IF;
1066
1067
1068
1069 l_non_standard_article_exists :=
1070 non_standard_article_exists (
1071 p_api_version => l_api_version,
1072 p_doc_type => l_sales_document_type_code,
1073 p_doc_id => l_doc_id,
1074 x_return_status => lx_return_status,
1075 x_msg_count => lx_msg_count,
1076 x_msg_data => lx_msg_data
1077 );
1078
1079
1080 IF l_debug_level > 0 THEN
1081 oe_debug_pub.add('l_non_standard_article_exists: ' || l_non_standard_article_exists, 3);
1082 oe_debug_pub.add('lx_return_status: ' || lx_return_status, 3);
1083 END IF;
1084
1085
1086 IF (funcmode = 'RUN') then
1087 resultout := 'COMPLETE:' || l_non_standard_article_exists; --'Y' or 'N'
1088 RETURN;
1089 END IF;
1090
1091 IF (funcmode = 'CANCEL') THEN
1092 resultout := 'COMPLETE:';
1093 RETURN;
1094 END IF;
1095
1096 IF (funcmode = 'TIMEOUT') THEN
1097 resultout := 'COMPLETE:';
1098 RETURN;
1099 END IF;
1100
1101
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 IF l_debug_level > 0 THEN
1105 oe_debug_pub.add('In WHEN OTHERS: ', 3);
1106 END IF;
1107
1108 wf_core.context('OE_CONTRACTS_UTIL',
1109 'WF_non_standard_article_exists',
1110 itemtype,
1111 itemkey,
1112 to_char(actid),
1113 funcmode);
1114 RAISE;
1115
1116
1117 END WF_non_stndrd_article_exists;
1118
1119
1120
1121 /* During the BSA or Sales Order approval workflow process, the notification sent by workflow
1122 has a link that points to the attachment representing the BSA or Sales Order.
1123 This procedure is used by that link (by a specialized item attribute) to point
1124 to the OM entity or contract entity attachment representing the BSA/Sales Order. */
1125 PROCEDURE attachment_location
1126 (
1127 p_api_version IN NUMBER,
1128 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1129
1130 p_doc_type IN VARCHAR2,
1131 p_doc_id IN NUMBER,
1132
1133 x_workflow_string OUT NOCOPY VARCHAR2,
1134 x_return_status OUT NOCOPY VARCHAR2,
1135 x_msg_count OUT NOCOPY NUMBER,
1136 x_msg_data OUT NOCOPY VARCHAR2
1137 ) IS
1138
1139 l_template_id NUMBER;
1140 l_template_name VARCHAR2(500);
1141 l_workflow_string VARCHAR2(1000);
1142
1143 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1144
1145 l_doc_version_number NUMBER;
1146
1147 l_attachment_exist_check CHAR(1);
1148
1149 --cursor to determine whether any attachments exist for the BSA or Sales Order
1150 CURSOR c_attachment_exist_check IS
1151 SELECT 'x'
1152 FROM fnd_attached_documents
1153 WHERE
1154 (
1155 entity_name = 'OKC_CONTRACT_DOCS'
1156 AND pk1_value = p_doc_type
1157 AND pk2_value = to_char(p_doc_id)
1158 -------AND pk3_value = l_doc_version_number
1159 AND pk3_value = G_CURRENT_VERSION_NUMBER /* Note: the contract document attachment creation java API always creates the current
1160 version of the attachment as -99 during the workflow approval process.
1161 (the contract document attachment creation java API increments the version number
1162 from 0,1... later after the attachment has been archived once) */
1163 )
1164 OR
1165 (
1166 entity_name = 'OE_ORDER_HEADERS'
1167 AND pk1_value = to_char(p_doc_id)
1168 );
1169
1170
1171
1172 /*********
1173 --cursor to get the version number of the blanket
1174 CURSOR c_get_bsa_version (cp_header_id NUMBER) IS
1175 SELECT version_number
1176 FROM oe_blanket_headers_all
1177 WHERE header_id = cp_header_id;
1178
1179
1180 --cursor to get the version number of the sales order
1181 CURSOR c_get_so_version (cp_header_id NUMBER) IS
1182 SELECT version_number
1183 FROM oe_order_headers_all
1184 WHERE header_id = cp_header_id;
1185 *********/
1186
1187
1188 BEGIN
1189
1190 IF l_debug_level > 0 THEN
1191 oe_debug_pub.add('In OE_Contracts_util.attachment_location ', 1);
1192 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
1193 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
1194 oe_debug_pub.add('p_doc_type: '|| p_doc_type, 3);
1195 oe_debug_pub.add('p_doc_id: '|| p_doc_id, 3);
1196 END IF;
1197
1198 x_return_status := FND_API.G_RET_STS_SUCCESS;
1199
1200 IF p_init_msg_list = FND_API.G_TRUE THEN
1201 oe_msg_pub.initialize;
1202 END IF;
1203
1204 /*
1205 ----------
1206 Even if there is no OKC license, at least the standard attachments
1207 should get displayed. In other words this check should NOT be done
1208 here.
1209
1210 That is the fix implemented during the solution of bug 11769204.
1211 ----------
1212 --Check for licensing, proceed with procesing only if licensed
1213 IF OE_Contracts_util.check_license() <> 'Y' THEN
1214 IF l_debug_level > 0 THEN
1215 oe_debug_pub.add('Contractual option not licensed, hence exiting attachment_location ', 3);
1216 END IF;
1217 RETURN;
1218 END IF;
1219 */
1220
1221
1222
1223 --Determine whether any attachments exist for the BSA or sales Order, proceed with processing only if attachments exist
1224 IF c_attachment_exist_check%ISOPEN THEN
1225 CLOSE c_attachment_exist_check;
1226 END IF;
1227 OPEN c_attachment_exist_check;
1228 FETCH c_attachment_exist_check INTO l_attachment_exist_check;
1229 CLOSE c_attachment_exist_check;
1230 IF l_attachment_exist_check IS NULL THEN
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add('No attachments exist for the BSA or Sales Order, hence exiting attachment_location...', 3);
1233 END IF;
1234 x_workflow_string := NULL; --returning NULL will ensure that no paper clip icon is shown in the workflow
1235 RETURN;
1236 END IF;
1237
1238
1239 IF l_debug_level > 0 THEN
1240 oe_debug_pub.add('Calling OE_Contracts_util.get_terms_template ', 3);
1241 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
1242 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
1243 oe_debug_pub.add('p_doc_type: '|| p_doc_type, 3);
1244 oe_debug_pub.add('p_doc_id: '|| p_doc_id, 3);
1245 END IF;
1246
1247 --first determine whether any terms and conditions have been instantiated for the BSA or Sales Order
1248 oe_contracts_util.get_terms_template (
1249 p_api_version => 1.0,
1250 p_init_msg_list => p_init_msg_list,
1251
1252 p_doc_type => p_doc_type,
1253 p_doc_id => p_doc_id,
1254
1255 x_template_id => l_template_id,
1256 x_template_name => l_template_name,
1257 x_return_status => x_return_status,
1258 x_msg_count => x_msg_count,
1259 x_msg_data => x_msg_data
1260 );
1261
1262
1263 IF l_debug_level > 0 THEN
1264 oe_debug_pub.add('x_template_id: '|| l_template_id, 3);
1265 oe_debug_pub.add('x_template_name: '|| l_template_name, 3);
1266 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
1267 END IF;
1268
1269 /*
1270 IF l_template_id IS NOT NULL THEN
1271 --terms and conditions do exist for the BSA or Sales Order so return contract attachment string to workflow request
1272 */
1273 IF OE_CONTRACTS_UTIL.Terms_Exists
1274 ( p_doc_type => p_doc_type
1275 , p_doc_id => p_doc_id) = 'Y' THEN
1276
1277 /************
1278 --get the version number for pk3 of contract attachment entity OKC_CONTRACT_DOCS
1279 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
1280 IF c_get_bsa_version%ISOPEN THEN
1281 CLOSE c_get_bsa_version;
1282 END IF;
1283
1284 OPEN c_get_bsa_version (p_doc_id);
1285 FETCH c_get_bsa_version INTO l_doc_version_number;
1286 CLOSE c_get_bsa_version;
1287
1288 IF l_debug_level > 0 THEN
1289 oe_debug_pub.add('l_doc_version_number of blanket: '|| l_doc_version_number, 3);
1290 END IF;
1291
1292 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
1293 IF c_get_so_version%ISOPEN THEN
1294 CLOSE c_get_so_version;
1295 END IF;
1296
1297 OPEN c_get_so_version (p_doc_id);
1298 FETCH c_get_so_version INTO l_doc_version_number;
1299 CLOSE c_get_so_version;
1300
1301 IF l_debug_level > 0 THEN
1302 oe_debug_pub.add('l_doc_version_number of sales order: '|| l_doc_version_number, 3);
1303 END IF;
1304
1305 END IF;
1306 ************/
1307
1308 l_workflow_string := 'FND:entity=OKC_CONTRACT_DOCS'
1309 || '&' || 'pk1name=BusinessDocumentType'
1310 || '&' || 'pk2name=BusinessDocumentId'
1311 || '&' || 'pk3name=BusinessDocumentVersion';
1312
1313
1314 l_workflow_string := l_workflow_string ||'&'|| 'pk1value=' || p_doc_type;
1315 l_workflow_string := l_workflow_string ||'&'|| 'pk2value=' || p_doc_id;
1316 -----l_workflow_string := l_workflow_string ||'&'|| 'pk3value=' || l_doc_version_number;
1317 l_workflow_string := l_workflow_string ||'&'|| 'pk3value=' || G_CURRENT_VERSION_NUMBER; --i.e. -99
1318 l_workflow_string := l_workflow_string ||'&'|| 'categories=OKC_REPO_CONTRACT,OKC_REPO_APP_ABSTRACT';
1319
1320 ELSE
1321 --NO terms and conditions exist for the BSA or Sales Order so return OM attachment string to workflow request
1322 --Attachment entities for blanket agreements and sales orders are the same.
1323 l_workflow_string := 'FND:entity=OE_ORDER_HEADERS' ||'&' || 'pk1name=HEADER_ID';
1324 l_workflow_string := l_workflow_string || '&' || 'pk1value=' || p_doc_id;
1325 l_workflow_string := l_workflow_string ||'&'|| 'categories=OE_PRINT_CATEGORY';
1326 END IF;
1327
1328 IF l_debug_level > 0 THEN
1329 oe_debug_pub.add('l_workflow_string: '|| l_workflow_string, 3);
1330 oe_debug_pub.add('End of OE_Contracts_util.attachment_location, x_return_status ' || x_return_status, 1);
1331 END IF;
1332
1333 x_workflow_string := l_workflow_string;
1334
1335
1336 EXCEPTION
1337 WHEN FND_API.G_EXC_ERROR THEN
1338
1339 IF l_debug_level > 0 THEN
1340 oe_debug_pub.add('WHEN OTHERS in attachment_location ', 3);
1341 END IF;
1342
1343 IF c_attachment_exist_check%ISOPEN THEN
1344 CLOSE c_attachment_exist_check;
1345 END IF;
1346
1347 /********
1348 IF c_get_bsa_version%ISOPEN THEN
1349 CLOSE c_get_bsa_version;
1350 END IF;
1351
1352 IF c_get_so_version%ISOPEN THEN
1353 CLOSE c_get_so_version;
1354 END IF;
1355 ********/
1356
1357
1358 x_return_status := FND_API.G_RET_STS_ERROR;
1359
1360 /*****
1361 not needed as per meeting
1362 --transfer error messages on OKC stack to OM stack
1363 OE_MSG_PUB.Transfer_Msg_Stack;
1364 *****/
1365
1366 --Get message count and data
1367 OE_MSG_PUB.Count_And_Get (
1368 p_count => x_msg_count,
1369 p_data => x_msg_data
1370 );
1371
1372
1373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1374
1375 IF l_debug_level > 0 THEN
1376 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in attachment_location ', 3);
1377 END IF;
1378
1379 IF c_attachment_exist_check%ISOPEN THEN
1380 CLOSE c_attachment_exist_check;
1381 END IF;
1382
1383 /********
1384 IF c_get_bsa_version%ISOPEN THEN
1385 CLOSE c_get_bsa_version;
1386 END IF;
1387
1388 IF c_get_so_version%ISOPEN THEN
1389 CLOSE c_get_so_version;
1390 END IF;
1391 ********/
1392
1393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1394
1395 /*****
1396 not needed as per meeting
1397 --transfer error messages on OKC stack to OM stack
1398 OE_MSG_PUB.Transfer_Msg_Stack;
1399 *****/
1400
1401 --Get message count and data
1402 OE_MSG_PUB.Count_And_Get (
1403 p_count => x_msg_count,
1404 p_data => x_msg_data
1405 );
1406
1407
1408 WHEN OTHERS THEN
1409
1410 IF l_debug_level > 0 THEN
1411 oe_debug_pub.add('WHEN OTHERS in attachment_location ', 3);
1412 END IF;
1413
1414 IF c_attachment_exist_check%ISOPEN THEN
1415 CLOSE c_attachment_exist_check;
1416 END IF;
1417
1418 /********
1419 IF c_get_bsa_version%ISOPEN THEN
1420 CLOSE c_get_bsa_version;
1421 END IF;
1422
1423 IF c_get_so_version%ISOPEN THEN
1424 CLOSE c_get_so_version;
1425 END IF;
1426 ********/
1427
1428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1429
1430 /*****
1431 not needed as per meeting
1432 --transfer error messages on OKC stack to OM stack
1433 OE_MSG_PUB.Transfer_Msg_Stack;
1434 *****/
1435
1436 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1437 OE_MSG_PUB.Add_Exc_Msg (
1438 G_PKG_NAME,
1439 'get_terms_template'
1440 );
1441 END IF;
1442
1443
1444 --Get message count and data
1445 OE_MSG_PUB.Count_And_Get (
1446 p_count => x_msg_count,
1447 p_data => x_msg_data
1448 );
1449
1450
1451
1452
1453 END attachment_location;
1454
1455
1456
1457 /* Check if Blanket or Sales Order has any terms and conditions instantiated against it i.e. if
1458 an article template exists for the Blanket or Sales Order or not.
1459 This just translates the output of the already existing procedure 'get_terms_template'
1460 into a 'Y' or 'N' */
1461 -- needed and requested by the preview print application
1462 FUNCTION terms_exists (
1463 p_doc_type IN VARCHAR2,
1464 p_doc_id IN NUMBER
1465 )
1466 RETURN VARCHAR2 IS
1467
1468 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1469 l_result VARCHAR2(1) := 'N';
1470
1471 /* l_return_status VARCHAR2(1);
1472 l_msg_count NUMBER;
1473 l_msg_data VARCHAR2(2000);
1474
1475 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1476 l_template_id NUMBER;
1477 l_template_name VARCHAR2(500);
1478 */
1479 BEGIN
1480
1481 IF l_debug_level > 0 THEN
1482 oe_debug_pub.add('In OE_Contracts_util.terms_exists ', 1);
1483 END IF;
1484
1485 --Check for licensing
1486 IF OE_Contracts_util.check_license() <> 'Y' THEN
1487 IF l_debug_level > 0 THEN
1488 oe_debug_pub.add('Contracts not licensed, exiting terms_exists', 3);
1489 END IF;
1490 --RETURN NULL;
1491 RETURN 'N';
1492 END IF;
1493
1494
1495 IF l_debug_level > 0 THEN
1496 oe_debug_pub.add('Calling OE_Contracts_util.has_terms ', 3);
1497 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
1498 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
1499 END IF;
1500
1501 --first determine whether any terms and conditions have been instantiated for the BSA or sales order
1502 /* oe_contracts_util.get_terms_template (
1503 p_api_version => 1.0,
1504
1505 p_doc_type => p_doc_type,
1506 p_doc_id => p_doc_id,
1507
1508 x_template_id => l_template_id,
1509 x_template_name => l_template_name,
1510 x_return_status => l_return_status,
1511 x_msg_count => l_msg_count,
1512 x_msg_data => l_msg_data
1513 );
1514
1515
1516 IF l_debug_level > 0 THEN
1517 oe_debug_pub.add('l_template_id: '|| l_template_id, 3);
1518 oe_debug_pub.add('l_template_name: '|| l_template_name, 3);
1519 oe_debug_pub.add('l_return_status: '|| l_return_status, 3);
1520 END IF;
1521
1522
1523 IF l_template_id IS NOT NULL THEN
1524 IF l_debug_level > 0 THEN
1525 oe_debug_pub.add('End of OE_Contracts_util.terms_exists , returning Y');
1526 END IF;
1527 RETURN ('Y');
1528 ELSE
1529 IF l_debug_level > 0 THEN
1530 oe_debug_pub.add('End of OE_Contracts_util.terms_exists , returning Y');
1531 END IF;
1532 RETURN ('N');
1533 END IF;*/
1534
1535 -- check if terms exist
1536
1537 l_result := OKC_TERMS_UTIL_GRP.HAS_TERMS (
1538 p_document_type => p_doc_type,
1539 p_document_id => p_doc_id
1540 );
1541
1542 IF l_debug_level > 0 THEN
1543 oe_debug_pub.add('End of OE_Contracts_util.terms_exists result is:'||l_result );
1544 END IF;
1545
1546 RETURN l_result;
1547
1548 EXCEPTION
1549
1550
1551 WHEN OTHERS THEN
1552
1553 IF l_debug_level > 0 THEN
1554 oe_debug_pub.add('WHEN-OTHERS in terms_exists', 1);
1555 END IF;
1556
1557 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1558 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1559 'terms_exists'
1560 );
1561 END IF;
1562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1563
1564
1565 END terms_exists;
1566
1567
1568
1569 --delete articles belonging to the BSA or Sales Order
1570 PROCEDURE delete_articles
1571 (
1572 p_api_version IN NUMBER,
1573 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1574 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1575
1576 p_doc_type IN VARCHAR2,
1577 p_doc_id IN NUMBER,
1578
1579 x_return_status OUT NOCOPY VARCHAR2,
1580 x_msg_count OUT NOCOPY NUMBER,
1581 x_msg_data OUT NOCOPY VARCHAR2
1582 ) IS
1583
1584 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1585
1586 BEGIN
1587
1588 IF l_debug_level > 0 THEN
1589 oe_debug_pub.add('In OE_Contracts_util.delete_articles ', 1);
1590 END IF;
1591
1592 x_return_status := FND_API.G_RET_STS_SUCCESS;
1593
1594 IF p_init_msg_list = FND_API.G_TRUE THEN
1595 oe_msg_pub.initialize;
1596 END IF;
1597
1598 --Check for licensing
1599 IF OE_Contracts_util.check_license() <> 'Y' THEN
1600 IF l_debug_level > 0 THEN
1601 oe_debug_pub.add('Contractual option not licensed, hence exiting delete_articles ', 3);
1602 END IF;
1603 RETURN;
1604 END IF;
1605
1606
1607
1608 IF l_debug_level > 0 THEN
1609 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.delete_doc ', 3);
1610 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
1611 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
1612 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
1613 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
1614 oe_debug_pub.add('p_commit: ' || p_commit,3);
1615 END IF;
1616
1617 OKC_TERMS_UTIL_GRP.delete_doc (
1618 p_api_version => p_api_version,
1619 p_init_msg_list => p_init_msg_list,
1620 p_commit => p_commit,
1621 p_doc_type => p_doc_type,
1622 p_doc_id => p_doc_id,
1623 x_return_status => x_return_status,
1624 x_msg_data => x_msg_data,
1625 x_msg_count => x_msg_count
1626 );
1627
1628 IF l_debug_level > 0 THEN
1629 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
1630 END IF;
1631
1632 --ETR
1633 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1635 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1636 RAISE FND_API.G_EXC_ERROR ;
1637 END IF;
1638 --ETR
1639
1640 IF l_debug_level > 0 THEN
1641 oe_debug_pub.add('End of OE_Contracts_util.delete_articles, x_return_status ' || x_return_status, 1);
1642 END IF;
1643
1644 EXCEPTION
1645 WHEN FND_API.G_EXC_ERROR THEN
1646
1647 IF l_debug_level > 0 THEN
1648 oe_debug_pub.add('WHEN G_EXC_ERROR in delete_articles ', 3);
1649 END IF;
1650
1651 --close any cursors
1652
1653 x_return_status := FND_API.G_RET_STS_ERROR;
1654
1655 /*****
1656 not needed as per meeting
1657 --transfer error messages on OKC stack to OM stack
1658 OE_MSG_PUB.Transfer_Msg_Stack;
1659 *****/
1660
1661 --Get message count and data
1662 OE_MSG_PUB.Count_And_Get (
1663 p_count => x_msg_count,
1664 p_data => x_msg_data
1665 );
1666
1667
1668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1669
1670 IF l_debug_level > 0 THEN
1671 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in delete_articles ', 3);
1672 END IF;
1673
1674 --close any cursors
1675
1676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1677
1678 /*****
1679 not needed as per meeting
1680 --transfer error messages on OKC stack to OM stack
1681 OE_MSG_PUB.Transfer_Msg_Stack;
1682 *****/
1683
1684 --Get message count and data
1685 OE_MSG_PUB.Count_And_Get (
1686 p_count => x_msg_count,
1687 p_data => x_msg_data
1688 );
1689
1690
1691 WHEN OTHERS THEN
1692
1693 IF l_debug_level > 0 THEN
1694 oe_debug_pub.add('WHEN OTHERS in delete_articles ', 3);
1695 END IF;
1696
1697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1698
1699 /*****
1700 not needed as per meeting
1701 --transfer error messages on OKC stack to OM stack
1702 OE_MSG_PUB.Transfer_Msg_Stack;
1703 *****/
1704
1705 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1706 OE_MSG_PUB.Add_Exc_Msg (
1707 G_PKG_NAME,
1708 'delete_articles'
1709 );
1710 END IF;
1711
1712 --Get message count and data
1713 OE_MSG_PUB.Count_And_Get (
1714 p_count => x_msg_count,
1715 p_data => x_msg_data
1716 );
1717
1718 END delete_articles;
1719
1720
1721
1722 --purge articles belonging to the BSA's or Sales Orders
1723 PROCEDURE purge_articles
1724 (
1725 p_api_version IN NUMBER,
1726 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1727 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1728
1729 p_doc_tbl IN doc_tbl_type,
1730
1731 x_return_status OUT NOCOPY VARCHAR2,
1732 x_msg_count OUT NOCOPY NUMBER,
1733 x_msg_data OUT NOCOPY VARCHAR2
1734 ) IS
1735
1736 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1737
1738 BEGIN
1739
1740 IF l_debug_level > 0 THEN
1741 oe_debug_pub.add('In OE_Contracts_util.purge_articles ', 1);
1742 END IF;
1743
1744 x_return_status := FND_API.G_RET_STS_SUCCESS;
1745
1746 IF p_init_msg_list = FND_API.G_TRUE THEN
1747 oe_msg_pub.initialize;
1748 END IF;
1749
1750 --Check for licensing
1751 IF OE_Contracts_util.check_license() <> 'Y' THEN
1752 IF l_debug_level > 0 THEN
1753 oe_debug_pub.add('Contractual option not licensed, hence exiting purge_articles ', 3);
1754 END IF;
1755 RETURN;
1756 END IF;
1757
1758
1759
1760 IF l_debug_level > 0 THEN
1761 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.purge_articles ', 3);
1762 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
1763 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
1764 oe_debug_pub.add('p_commit: ' || p_commit,3);
1765 END IF;
1766
1767 OKC_TERMS_UTIL_GRP.purge_doc (
1768 p_api_version => p_api_version,
1769 p_init_msg_list => p_init_msg_list,
1770 p_commit => p_commit,
1771 p_doc_tbl => p_doc_tbl,
1772 x_return_status => x_return_status,
1773 x_msg_data => x_msg_data,
1774 x_msg_count => x_msg_count
1775 );
1776
1777 IF l_debug_level > 0 THEN
1778 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
1779 END IF;
1780
1781 --ETR
1782 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1784 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1785 RAISE FND_API.G_EXC_ERROR ;
1786 END IF;
1787 --ETR
1788
1789 IF l_debug_level > 0 THEN
1790 oe_debug_pub.add('End of OE_Contracts_util.purge_articles, x_return_status ' || x_return_status, 1);
1791 END IF;
1792
1793 EXCEPTION
1794 WHEN FND_API.G_EXC_ERROR THEN
1795
1796 IF l_debug_level > 0 THEN
1797 oe_debug_pub.add('WHEN G_EXC_ERROR in purge_articles ', 3);
1798 END IF;
1799
1800 --close any cursors
1801
1802 x_return_status := FND_API.G_RET_STS_ERROR;
1803
1804 /*****
1805 not needed as per meeting
1806 --transfer error messages on OKC stack to OM stack
1807 OE_MSG_PUB.Transfer_Msg_Stack;
1808 *****/
1809
1810 --Get message count and data
1811 OE_MSG_PUB.Count_And_Get (
1812 p_count => x_msg_count,
1813 p_data => x_msg_data
1814 );
1815
1816
1817 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1818
1819 IF l_debug_level > 0 THEN
1820 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in purge_articles ', 3);
1821 END IF;
1822
1823 --close any cursors
1824
1825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1826
1827 /*****
1828 not needed as per meeting
1829 --transfer error messages on OKC stack to OM stack
1830 OE_MSG_PUB.Transfer_Msg_Stack;
1831 *****/
1832
1833 --Get message count and data
1834 OE_MSG_PUB.Count_And_Get (
1835 p_count => x_msg_count,
1836 p_data => x_msg_data
1837 );
1838
1839
1840 WHEN OTHERS THEN
1841
1842 IF l_debug_level > 0 THEN
1843 oe_debug_pub.add('WHEN OTHERS in purge_articles ', 3);
1844 END IF;
1845
1846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1847
1848 /*****
1849 not needed as per meeting
1850 --transfer error messages on OKC stack to OM stack
1851 OE_MSG_PUB.Transfer_Msg_Stack;
1852 *****/
1853
1854 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1855 OE_MSG_PUB.Add_Exc_Msg (
1856 G_PKG_NAME,
1857 'purge_articles'
1858 );
1859 END IF;
1860
1861 --Get message count and data
1862 OE_MSG_PUB.Count_And_Get (
1863 p_count => x_msg_count,
1864 p_data => x_msg_data
1865 );
1866
1867 END purge_articles;
1868
1869
1870
1871 PROCEDURE get_article_variable_values
1872 (
1873 p_api_version IN NUMBER,
1874 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1875 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1876
1877 p_doc_type IN VARCHAR2,
1878 p_doc_id IN NUMBER,
1879 p_sys_var_value_tbl IN OUT NOCOPY sys_var_value_tbl_type,
1880
1881 x_return_status OUT NOCOPY VARCHAR2,
1882 x_msg_count OUT NOCOPY NUMBER,
1883 x_msg_data OUT NOCOPY VARCHAR2
1884 )
1885
1886
1887 IS
1888
1889
1890
1891 /** note: in cursors based on table OE_BLANKET_HEADERS_ALL, we select on the basis
1892 of HEADER_ID only as it is unique and an index is based on that, we don't need SALES_DOCUMENT_TYPE_CODE **/
1893
1894 --cursor to fetch value of header level variables such as OKC$S_BLANKET_NUMBER etc. for blankets
1895 CURSOR c_get_bsa_header_variables IS
1896 SELECT bh.order_number,
1897 bh.agreement_id,
1898 bh.sold_to_org_id,
1899 bh.order_type_id,
1900 bh.cust_po_number,
1901 bh.version_number,
1902 bh.sold_to_contact_id,
1903 bh.salesrep_id,
1904 bh.transactional_curr_code,
1905 bhe.start_date_active,
1906 bhe.end_date_active,
1907 bh.freight_terms_code,
1908 bh.shipping_method_code,
1909 bh.payment_term_id,
1910 bh.invoicing_rule_id,
1911 bhe.blanket_min_amount,
1912 bhe.blanket_max_amount,
1913 bh.org_id
1914 FROM oe_blanket_headers_all bh,
1915 oe_blanket_headers_ext bhe
1916 WHERE bh.header_id = p_doc_id
1917 AND bh.order_number = bhe.order_number;
1918
1919
1920 --cursor to fetch value of header level variables such as OKC$S_ORDER_NUMBER etc. for sales orders
1921 CURSOR c_get_so_header_variables IS
1922 SELECT oh.order_number,
1923 oh.blanket_number,
1924 oh.agreement_id,
1925 oh.quote_number,
1926 oh.sold_to_org_id,
1927 oh.cust_po_number,
1928 oh.version_number,
1929 oh.sold_to_contact_id,
1930 oh.salesrep_id,
1931 oh.transactional_curr_code,
1932 oh.freight_terms_code,
1933 oh.shipping_method_code,
1934 oh.payment_term_id,
1935 oh.invoicing_rule_id,
1936 oh.org_id
1937 FROM oe_order_headers_all oh
1938
1939 WHERE oh.header_id = p_doc_id;
1940
1941
1942
1943
1944 l_bsa_header_variables c_get_bsa_header_variables%ROWTYPE;
1945 l_so_header_variables c_get_so_header_variables%ROWTYPE;
1946 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1947
1948 BEGIN
1949
1950 IF l_debug_level > 0 THEN
1951 oe_debug_pub.add('In OE_Contracts_util.get_article_variable_values for header level variables', 1);
1952 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
1953 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
1954 END IF;
1955
1956 x_return_status := FND_API.G_RET_STS_SUCCESS;
1957
1958 IF p_init_msg_list = FND_API.G_TRUE THEN
1959 oe_msg_pub.initialize;
1960 END IF;
1961
1962
1963 -- For articles QA: -
1964 -- Query OM tables OE_BLANKET_HEADERS_ALL and OE_BLANKET_HEADERS_EXT to retrieve values against variable codes
1965 -- sent in by calling articles QA API.
1966
1967 ----IF p_sys_var_value_tbl.COUNT > 0 THEN
1968 IF p_sys_var_value_tbl.FIRST IS NOT NULL THEN
1969
1970 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
1971
1972 IF c_get_bsa_header_variables%ISOPEN THEN
1973 CLOSE c_get_bsa_header_variables;
1974 END IF;
1975
1976 OPEN c_get_bsa_header_variables;
1977 FETCH c_get_bsa_header_variables INTO l_bsa_header_variables;
1978 IF l_debug_level > 0 THEN
1979 oe_debug_pub.add('c_get_bsa_header_variables%ROWCOUNT: ' || c_get_bsa_header_variables%ROWCOUNT, 3);
1980 END IF;
1981 CLOSE c_get_bsa_header_variables;
1982
1983 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
1984
1985 IF c_get_bsa_header_variables%ISOPEN THEN
1986 CLOSE c_get_so_header_variables;
1987 END IF;
1988
1989 OPEN c_get_so_header_variables;
1990 FETCH c_get_so_header_variables INTO l_so_header_variables;
1991 IF l_debug_level > 0 THEN
1992 oe_debug_pub.add('c_get_so_header_variables%ROWCOUNT: ' || c_get_so_header_variables%ROWCOUNT, 3);
1993 END IF;
1994 CLOSE c_get_so_header_variables;
1995
1996 END IF;
1997
1998
1999
2000 -----------------------------------------------------------------------------------------------
2001 FOR i IN p_sys_var_value_tbl.FIRST..p_sys_var_value_tbl.LAST LOOP
2002
2003
2004 IF p_sys_var_value_tbl(i).variable_code = 'OKC$S_ORDER_NUMBER' THEN
2005 BEGIN
2006 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.order_number;
2007 END;
2008
2009 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_BLANKET_NUMBER' THEN
2010 BEGIN
2011 IF p_doc_type = OE_CONTRACTS_UTIL.get_G_BSA_DOC_TYPE() THEN
2012 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.order_number;
2013 ELSIF p_doc_type = OE_CONTRACTS_UTIL.get_G_SO_DOC_TYPE() THEN
2014 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.blanket_number;
2015 END IF;
2016 END;
2017
2018 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_PA_NUMBER' THEN
2019 BEGIN
2020 IF p_doc_type = OE_CONTRACTS_UTIL.get_G_BSA_DOC_TYPE() THEN
2021 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.agreement_id;
2022 ELSIF p_doc_type = OE_CONTRACTS_UTIL.get_G_SO_DOC_TYPE() THEN
2023 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.agreement_id;
2024 END IF;
2025 END;
2026
2027 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_PA_NAME' THEN
2028 BEGIN
2029 IF p_doc_type = OE_CONTRACTS_UTIL.get_G_BSA_DOC_TYPE() THEN
2030 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.agreement_id;
2031 ELSIF p_doc_type = OE_CONTRACTS_UTIL.get_G_SO_DOC_TYPE() THEN
2032 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.agreement_id;
2033 END IF;
2034 END;
2035
2036 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_QUOTE_NUMBER' THEN
2037 BEGIN
2038 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.quote_number;
2039 END;
2040
2041 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CUSTOMER_NAME' THEN
2042 BEGIN
2043 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2044 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.sold_to_org_id;
2045 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2046 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.sold_to_org_id;
2047 END IF;
2048 END;
2049
2050 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CUSTOMER_NUMBER' THEN
2051 BEGIN
2052 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2053 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.sold_to_org_id;
2054 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2055 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.sold_to_org_id;
2056 END IF;
2057 END;
2058
2059 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_BLANKET_AGREEMENT_TYPE' THEN
2060 BEGIN
2061 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.order_type_id;
2062 END;
2063
2064 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CUST_PO_NUMBER' THEN
2065 BEGIN
2066 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2067 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.cust_po_number;
2068 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2069 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.cust_po_number;
2070 END IF;
2071 END;
2072
2073 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_VERSION_NUMBER' THEN
2074 BEGIN
2075 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2076 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.version_number;
2077 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2078 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.version_number;
2079 END IF;
2080 END;
2081
2082 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CUST_CONTACT_NAME' THEN
2083 BEGIN
2084 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2085 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.sold_to_contact_id;
2086 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2087 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.sold_to_contact_id;
2088 END IF;
2089 END;
2090
2091 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_SALESREP_NAME' THEN
2092 BEGIN
2093 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2094 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.salesrep_id;
2095 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2096 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.salesrep_id;
2097 END IF;
2098 END;
2099
2100 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CURRENCY_CODE' THEN
2101 BEGIN
2102 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2103 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.transactional_curr_code;
2104 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2105 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.transactional_curr_code;
2106 END IF;
2107 END;
2108
2109 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CURRENCY_NAME' THEN
2110 BEGIN
2111 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2112 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.transactional_curr_code;
2113 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2114 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.transactional_curr_code;
2115 END IF;
2116 END;
2117
2118 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CURRENCY_SYMBOL' THEN
2119 BEGIN
2120 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2121 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.transactional_curr_code;
2122 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2123 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.transactional_curr_code;
2124 END IF;
2125 END;
2126
2127 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_ACTIVATION_DATE' THEN
2128 BEGIN
2129 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.start_date_active;
2130 END;
2131
2132 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_EXPIRATION_DATE' THEN
2133 BEGIN
2134 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.end_date_active;
2135 END;
2136
2137 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_FREIGHT_TERMS' THEN
2138 BEGIN
2139 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2140 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.freight_terms_code;
2141 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2142 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.freight_terms_code;
2143 END IF;
2144 END;
2145
2146 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_SHIPPING_METHOD' THEN
2147 BEGIN
2148 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2149 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.shipping_method_code;
2150 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2151 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.shipping_method_code;
2152 END IF;
2153 END;
2154
2155 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_PAYMENT_TERM' THEN
2156 BEGIN
2157 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2158 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.payment_term_id;
2159 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2160 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.payment_term_id;
2161 END IF;
2162 END;
2163
2164 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_INVOICING_RULE' THEN
2165 BEGIN
2166 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2167 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.invoicing_rule_id;
2168 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2169 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.invoicing_rule_id;
2170 END IF;
2171 END;
2172
2173 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_MIN_AMOUNT_AGREED' THEN
2174 BEGIN
2175 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.blanket_min_amount;
2176 END;
2177
2178 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_MAX_AMOUNT_AGREED' THEN
2179 BEGIN
2180 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.blanket_max_amount;
2181 END;
2182
2183 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_SUPPLIER_NAME' THEN
2184 BEGIN
2185 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2186 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.org_id;
2187 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2188 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.org_id;
2189 END IF;
2190 END;
2191
2192 ELSE NULL;
2193 END IF;
2194
2195 IF l_debug_level > 0 THEN
2196 oe_debug_pub.add(p_sys_var_value_tbl(i).variable_code || ': ' || p_sys_var_value_tbl(i).variable_value_id, 3);
2197 END IF;
2198
2199
2200 END LOOP;
2201 -----------------------------------------------------------------------------------------------
2202
2203 END IF;
2204
2205 IF l_debug_level > 0 THEN
2206 oe_debug_pub.add('End of OE_Contracts_util.get_article_variable_values for header level variables, x_return_status ' || x_return_status, 1);
2207 END IF;
2208
2209
2210 EXCEPTION
2211 WHEN FND_API.G_EXC_ERROR THEN
2212
2213 IF l_debug_level > 0 THEN
2214 oe_debug_pub.add('WHEN G_EXC_ERROR in get_article_variable_values ', 3);
2215 END IF;
2216
2217 --close any cursors
2218 IF c_get_bsa_header_variables%ISOPEN THEN
2219 CLOSE c_get_bsa_header_variables;
2220 END IF;
2221
2222 x_return_status := FND_API.G_RET_STS_ERROR;
2223
2224 /*****
2225 not needed as per meeting
2226 --transfer error messages on OKC stack to OM stack
2227 OE_MSG_PUB.Transfer_Msg_Stack;
2228 *****/
2229
2230 --Get message count and data
2231 OE_MSG_PUB.Count_And_Get (
2232 p_count => x_msg_count,
2233 p_data => x_msg_data
2234 );
2235
2236
2237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2238
2239 IF l_debug_level > 0 THEN
2240 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_article_variable_values ', 3);
2241 END IF;
2242
2243 --close any cursors
2244 IF c_get_bsa_header_variables%ISOPEN THEN
2245 CLOSE c_get_bsa_header_variables;
2246 END IF;
2247
2248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2249
2250 /*****
2251 not needed as per meeting
2252 --transfer error messages on OKC stack to OM stack
2253 OE_MSG_PUB.Transfer_Msg_Stack;
2254 *****/
2255
2256 --Get message count and data
2257 OE_MSG_PUB.Count_And_Get (
2258 p_count => x_msg_count,
2259 p_data => x_msg_data
2260 );
2261
2262
2263 WHEN OTHERS THEN
2264
2265 IF l_debug_level > 0 THEN
2266 oe_debug_pub.add('WHEN OTHERS in get_article_variable_values ', 3);
2267 END IF;
2268
2269 IF c_get_bsa_header_variables%ISOPEN THEN
2270 CLOSE c_get_bsa_header_variables;
2271 END IF;
2272
2273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2274
2275 /*****
2276 not needed as per meeting
2277 --transfer error messages on OKC stack to OM stack
2278 OE_MSG_PUB.Transfer_Msg_Stack;
2279 *****/
2280
2281 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2282 OE_MSG_PUB.Add_Exc_Msg (
2283 G_PKG_NAME,
2284 'get_article_variable_values'
2285 );
2286 END IF;
2287
2288
2289 --Get message count and data
2290 OE_MSG_PUB.Count_And_Get (
2291 p_count => x_msg_count,
2292 p_data => x_msg_data
2293 );
2294
2295 END get_article_variable_values;
2296
2297
2298
2299
2300 --this overloaded signature is called from the contract expert
2301 PROCEDURE get_article_variable_values
2302 (
2303 p_api_version IN NUMBER,
2304 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2305 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2306
2307 p_doc_type IN VARCHAR2,
2308 p_doc_id IN NUMBER,
2309 p_line_var_tbl IN line_var_tbl_type,
2310
2311 x_line_var_value_tbl OUT NOCOPY sys_var_value_tbl_type,
2312 x_return_status OUT NOCOPY VARCHAR2,
2313 x_msg_count OUT NOCOPY NUMBER,
2314 x_msg_data OUT NOCOPY VARCHAR2
2315 ) IS
2316
2317 --cursor to get all the items of the BSA i.e. internal (INT) customer (CUST) etc.
2318 --returns non-translatable code eg. AS54888
2319 CURSOR c_get_items IS
2320 SELECT item_identifier_type, --eg. INT
2321 ordered_item, --eg. AS54888
2322 ordered_item_id,
2323 org_id,
2324 inventory_item_id,
2325 sold_to_org_id
2326 FROM oe_blanket_lines_all
2327 WHERE header_id = p_doc_id
2328 AND p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE
2329 AND item_identifier_type <> 'CAT'
2330
2331 UNION ALL
2332
2333 --cursor to get all the items of the Sales Order i.e. internal (INT) customer (CUST) etc.
2334 --returns non-translatable code eg. AS54888
2335 SELECT item_identifier_type, --eg. INT
2336 ordered_item, --eg. AS54888
2337 ordered_item_id,
2338 org_id,
2339 inventory_item_id,
2340 sold_to_org_id
2341 FROM oe_order_lines_all
2342 WHERE header_id = p_doc_id
2343 AND p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE
2344 AND item_identifier_type <> 'CAT'
2345 ORDER BY ordered_item;
2346
2347
2348
2349
2350 --cursor to retrieve the item categories (CATs) in the BSA
2351 --returns non-translatable code eg. 208.05
2352 CURSOR c_get_item_categories IS
2353 SELECT ordered_item
2354 FROM oe_blanket_lines_all
2355 WHERE header_id = p_doc_id
2356 AND p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE
2357 AND item_identifier_type = 'CAT'
2358
2359 UNION ALL
2360
2361 --cursor to retrieve the item categories (CATs) in the Sales Order
2362 --returns non-translatable code eg. 208.05
2363 SELECT ordered_item
2364 FROM oe_order_lines_all
2365 WHERE header_id = p_doc_id
2366 AND p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE
2367 AND item_identifier_type = 'CAT'
2368 ORDER BY ordered_item;
2369
2370
2371
2372
2373 -- cursor to retrieve categories to which the INT (internal) and non-INT items in the BSA or Sales Order belong
2374 /** Note: the inventory_item_id stored in OE_BLANKET_LINES_ALL OE_ORDER_LINES_ALL against the non-INT item is
2375 that of the mapped INT item so we can use it directly to get the item category **/
2376 -- returns non-translatable code eg. HOSPITAL.MISC
2377 CURSOR c_get_derived_item_category (cp_org_id NUMBER,
2378 cp_inventory_item_id NUMBER) IS
2379 SELECT category_concat_segs
2380 FROM mtl_item_categories_v
2381 WHERE inventory_item_id = cp_inventory_item_id
2382 AND organization_id = cp_org_id -- should be inventory master org
2383 AND structure_id = 101; -- hardcoded to 101 i.e. Item Categories (Inv. Items) for Order Management
2384
2385
2386 l_bsa_derived_item_category c_get_derived_item_category%ROWTYPE;
2387
2388 j BINARY_INTEGER := 1;
2389 l_master_org_id NUMBER;
2390 lx_ordered_item VARCHAR2(2000);
2391 lx_inventory_item VARCHAR2(2000);
2392 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2393
2394 BEGIN
2395
2396 IF l_debug_level > 0 THEN
2397 oe_debug_pub.add('In OE_Contracts_util.get_article_variable_values for line level variables ', 1);
2398 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 1);
2399 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 1);
2400 oe_debug_pub.add('p_line_var_tbl.COUNT: ' || p_line_var_tbl.COUNT, 1);
2401 END IF;
2402
2403
2404 x_return_status := FND_API.G_RET_STS_SUCCESS;
2405
2406 IF p_init_msg_list = FND_API.G_TRUE THEN
2407 oe_msg_pub.initialize;
2408 END IF;
2409
2410
2411 -- For articles wizard expert: -
2412 -- Query OM tables OE_BLANKET_HEADERS_ALL and OE_BLANKET_LINES_ALL to retrieve values against variable codes sent
2413 -- in by calling articles wizard expert API
2414
2415 IF p_line_var_tbl.FIRST IS NOT NULL THEN
2416 FOR i IN p_line_var_tbl.FIRST..p_line_var_tbl.LAST LOOP
2417
2418 IF l_debug_level > 0 THEN
2419 oe_debug_pub.add('Processing for ' || p_line_var_tbl(i), 3);
2420 END IF;
2421
2422 IF p_line_var_tbl(i) = 'OKC$S_ITEMS' THEN
2423
2424 FOR c_get_items_rec IN c_get_items LOOP
2425 --loop thru all the items for internal INT items
2426 IF l_debug_level > 0 THEN
2427 oe_debug_pub.add('c_get_items_rec.item_identifier_type: '||c_get_items_rec.item_identifier_type, 3);
2428 END IF;
2429
2430 IF c_get_items_rec.item_identifier_type = 'INT' THEN
2431 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_INTERNAL_ITEM
2432 x_line_var_value_tbl(j).variable_value_id := c_get_items_rec.ordered_item; --eg. AS54888
2433
2434 ELSIF c_get_items_rec.item_identifier_type <> 'INT' THEN
2435 --map the non-INT items to INT items
2436
2437 --get inventory master org
2438 /******************************************************/
2439 -- FOR TESTING ONLY, REMOVE WHEN DONE! THIS CONTEXT WILL AUTOMATICALLY BE SET IN FORMS
2440 -- dbms_application_info.set_client_info('204');
2441 /******************************************************/
2442 l_master_org_id := TO_NUMBER(oe_sys_parameters.value (
2443 param_name => 'MASTER_ORGANIZATION_ID'
2444 ));
2445
2446 IF l_debug_level > 0 THEN
2447 oe_debug_pub.add('l_master_org_id: ' || l_master_org_id, 3);
2448 oe_debug_pub.add('mapping non-INT item to INT item, Calling OE_Id_To_Value.Ordered_Item ', 3);
2449 END IF;
2450
2451 --map non-INT item to INT item
2452 OE_Id_To_Value.Ordered_Item (
2453 p_item_identifier_type => c_get_items_rec.item_identifier_type,
2454 p_inventory_item_id => c_get_items_rec.inventory_item_id,
2455 p_organization_id => l_master_org_id,
2456 p_ordered_item_id => c_get_items_rec.ordered_item_id,
2457 p_sold_to_org_id => c_get_items_rec.sold_to_org_id,
2458 p_ordered_item => c_get_items_rec.ordered_item,
2459 x_ordered_item => lx_ordered_item,
2460 x_inventory_item => lx_inventory_item
2461 );
2462
2463 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_INTERNAL_ITEM
2464 x_line_var_value_tbl(j).variable_value_id := lx_inventory_item;
2465
2466 IF l_debug_level > 0 THEN
2467 oe_debug_pub.add('lx_inventory_item: ' || lx_inventory_item, 3);
2468 oe_debug_pub.add('x_line_var_value_tbl(j).variable_code: ' || x_line_var_value_tbl(j).variable_code,3);
2469 oe_debug_pub.add('x_line_var_value_tbl(j).variable_value_id: ' || x_line_var_value_tbl(j).variable_value_id,3);
2470 END IF;
2471
2472
2473 END IF;
2474
2475 j := j + 1;
2476
2477 END LOOP;
2478
2479
2480
2481
2482 ELSIF p_line_var_tbl(i) = 'OKC$S_ITEM_CATEGORIES' THEN
2483
2484
2485 --get all the item categories in the BSA
2486 FOR c_get_item_categories_rec IN c_get_item_categories LOOP
2487
2488 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_ITEM_CATEGORIES
2489 x_line_var_value_tbl(j).variable_value_id := c_get_item_categories_rec.ordered_item;
2490
2491 IF l_debug_level > 0 THEN
2492 oe_debug_pub.add('x_line_var_value_tbl(j).variable_code: ' || x_line_var_value_tbl(j).variable_code,3);
2493 oe_debug_pub.add('x_line_var_value_tbl(j).variable_value_id: ' || x_line_var_value_tbl(j).variable_value_id,3);
2494 END IF;
2495
2496 j := j + 1;
2497
2498 END LOOP;
2499
2500 --get the item categories to which the INT and non-INT items in the BSA belong to
2501 /** note: the inventory_item_id stored in oe_blanket_lines_all against the non-INT items is actually that of the mapped INT
2502 item so we can use it directly to get the item category **/
2503 FOR c_get_items_rec IN c_get_items LOOP
2504
2505 --get inventory master org
2506 /******************************************************/
2507 -- FOR TESTING ONLY, REMOVE WHEN DONE!!!!! THIS CONTEXT WILL AUTOMATICALLY BE SET IN FORMS
2508 --dbms_application_info.set_client_info('204');
2509 /******************************************************/
2510 l_master_org_id := TO_NUMBER(oe_sys_parameters.value (
2511 param_name => 'MASTER_ORGANIZATION_ID'
2512 ));
2513
2514 IF l_debug_level > 0 THEN
2515 oe_debug_pub.add('l_master_org_id: ' || l_master_org_id, 3);
2516 oe_debug_pub.add('get the item categories to which the INT and non-INT items in the BSA belong to',3);
2517 END IF;
2518
2519 l_bsa_derived_item_category := null; --initialize !!!!!!! this causes NULL values: ref: Arun/Aftab issue
2520 IF c_get_derived_item_category%ISOPEN THEN
2521 CLOSE c_get_derived_item_category;
2522 END IF;
2523 OPEN c_get_derived_item_category(l_master_org_id, c_get_items_rec.inventory_item_id);
2524 FETCH c_get_derived_item_category INTO l_bsa_derived_item_category;
2525 CLOSE c_get_derived_item_category;
2526
2527 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_ITEM_CATEGORIES
2528 x_line_var_value_tbl(j).variable_value_id := l_bsa_derived_item_category.category_concat_segs;
2529
2530 IF l_debug_level > 0 THEN
2531 oe_debug_pub.add('x_line_var_value_tbl(j).variable_code: ' || x_line_var_value_tbl(j).variable_code,3);
2532 oe_debug_pub.add('x_line_var_value_tbl(j).variable_value_id: ' || x_line_var_value_tbl(j).variable_value_id,3);
2533 END IF;
2534
2535 j := j + 1;
2536
2537 END LOOP;
2538
2539 END IF;
2540
2541
2542 END LOOP;
2543 END IF; ----IF p_line_var_tbl.FIRST IS NOT NULL THEN
2544
2545
2546 IF l_debug_level > 0 THEN
2547 oe_debug_pub.add('End of OE_Contracts_util.get_article_variable_values for line level variables, x_return_status: '|| x_return_status, 1);
2548 END IF;
2549
2550 EXCEPTION
2551 WHEN FND_API.G_EXC_ERROR THEN
2552
2553 IF l_debug_level > 0 THEN
2554 oe_debug_pub.add('WHEN G_EXC_ERROR in get_article_variable_values ', 3);
2555 END IF;
2556
2557 --close any cursors
2558 IF c_get_derived_item_category%ISOPEN THEN
2559 CLOSE c_get_derived_item_category;
2560 END IF;
2561
2562 x_return_status := FND_API.G_RET_STS_ERROR;
2563
2564 /*****
2565 not needed as per meeting
2566 --transfer error messages on OKC stack to OM stack
2567 OE_MSG_PUB.Transfer_Msg_Stack;
2568 *****/
2569
2570
2571 --Get message count and data
2572 OE_MSG_PUB.Count_And_Get (
2573 p_count => x_msg_count,
2574 p_data => x_msg_data
2575 );
2576
2577
2578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2579
2580 IF l_debug_level > 0 THEN
2581 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_article_variable_values ', 3);
2582 END IF;
2583
2584 --close any cursors
2585 IF c_get_derived_item_category%ISOPEN THEN
2586 CLOSE c_get_derived_item_category;
2587 END IF;
2588
2589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2590
2591 /*****
2592 not needed as per meeting
2593 --transfer error messages on OKC stack to OM stack
2594 OE_MSG_PUB.Transfer_Msg_Stack;
2595 *****/
2596
2597 --Get message count and data
2598 OE_MSG_PUB.Count_And_Get (
2599 p_count => x_msg_count,
2600 p_data => x_msg_data
2601 );
2602
2603
2604 WHEN OTHERS THEN
2605
2606 IF l_debug_level > 0 THEN
2607 oe_debug_pub.add('WHEN OTHERS in get_article_variable_values ', 3);
2608 END IF;
2609
2610 --close any cursors
2611 IF c_get_derived_item_category%ISOPEN THEN
2612 CLOSE c_get_derived_item_category;
2613 END IF;
2614
2615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2616
2617 /*****
2618 not needed as per meeting
2619 --transfer error messages on OKC stack to OM stack
2620 OE_MSG_PUB.Transfer_Msg_Stack;
2621 *****/
2622
2623 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2624 OE_MSG_PUB.Add_Exc_Msg (
2625 G_PKG_NAME,
2626 'get_article_variable_values'
2627 );
2628 END IF;
2629
2630
2631 --Get message count and data
2632 OE_MSG_PUB.Count_And_Get (
2633 p_count => x_msg_count,
2634 p_data => x_msg_data
2635 );
2636
2637 END get_article_variable_values;
2638
2639
2640
2641
2642
2643 --to return details about an article template being used by a particular BSA or Sales Order
2644 PROCEDURE get_terms_template
2645 (
2646 p_api_version IN NUMBER,
2647 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2648 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2649
2650 p_doc_type IN VARCHAR2,
2651 p_doc_id IN NUMBER,
2652
2653 x_template_id OUT NOCOPY NUMBER,
2654 x_template_name OUT NOCOPY VARCHAR2,
2655 x_return_status OUT NOCOPY VARCHAR2,
2656 x_msg_count OUT NOCOPY NUMBER,
2657 x_msg_data OUT NOCOPY VARCHAR2
2658 )
2659
2660 IS
2661
2662
2663 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2664
2665
2666 BEGIN
2667
2668 IF l_debug_level > 0 THEN
2669 oe_debug_pub.add('In OE_Contracts_util.get_terms_template ', 1);
2670 END IF;
2671
2672 x_return_status := FND_API.G_RET_STS_SUCCESS;
2673
2674 IF p_init_msg_list = FND_API.G_TRUE THEN
2675 oe_msg_pub.initialize;
2676 END IF;
2677
2678 --Check for licensing
2679 IF OE_Contracts_util.check_license() <> 'Y' THEN
2680 IF l_debug_level > 0 THEN
2681 oe_debug_pub.add('Contractual option not licensed, hence exiting get_terms_template ', 3);
2682 END IF;
2683 x_template_id := NULL;
2684 x_template_name := NULL;
2685 RETURN;
2686 END IF;
2687
2688
2689 IF l_debug_level > 0 THEN
2690 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.get_terms_template ', 3);
2691 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
2692 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
2693 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
2694 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
2695 END IF;
2696
2697 OKC_TERMS_UTIL_GRP.get_terms_template (
2698 p_api_version => p_api_version,
2699 p_init_msg_list => p_init_msg_list,
2700 ---p_commit => p_commit,
2701 p_doc_type => p_doc_type,
2702 p_doc_id => p_doc_id,
2703 x_template_id => x_template_id,
2704 x_template_name => x_template_name,
2705 x_return_status => x_return_status,
2706 x_msg_count => x_msg_count,
2707 x_msg_data => x_msg_data
2708 );
2709
2710 IF l_debug_level > 0 THEN
2711 oe_debug_pub.add('x_template_id: '|| x_template_id, 3);
2712 oe_debug_pub.add('x_template_name: '|| x_template_name, 3);
2713 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
2714 END IF;
2715
2716 --ETR
2717 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2719 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2720 RAISE FND_API.G_EXC_ERROR ;
2721 END IF;
2722 --ETR
2723
2724 IF l_debug_level > 0 THEN
2725 oe_debug_pub.add('End of OE_Contracts_util.get_terms_template , x_return_status ' || x_return_status, 1);
2726 END IF;
2727
2728 EXCEPTION
2729 WHEN FND_API.G_EXC_ERROR THEN
2730
2731 IF l_debug_level > 0 THEN
2732 oe_debug_pub.add('WHEN G_EXC_ERROR in get_terms_template ', 3);
2733 END IF;
2734
2735 x_return_status := FND_API.G_RET_STS_ERROR;
2736
2737 /*****
2738 not needed as per meeting
2739 --transfer error messages on OKC stack to OM stack
2740 OE_MSG_PUB.Transfer_Msg_Stack;
2741 *****/
2742
2743 --Get message count and data
2744 OE_MSG_PUB.Count_And_Get (
2745 p_count => x_msg_count,
2746 p_data => x_msg_data
2747 );
2748
2749
2750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2751
2752 IF l_debug_level > 0 THEN
2753 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_terms_template ', 3);
2754 END IF;
2755
2756 --close any cursors
2757
2758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2759
2760 /*****
2761 not needed as per meeting
2762 --transfer error messages on OKC stack to OM stack
2763 OE_MSG_PUB.Transfer_Msg_Stack;
2764 *****/
2765
2766 --Get message count and data
2767 OE_MSG_PUB.Count_And_Get (
2768 p_count => x_msg_count,
2769 p_data => x_msg_data
2770 );
2771
2772
2773 WHEN OTHERS THEN
2774
2775 IF l_debug_level > 0 THEN
2776 oe_debug_pub.add('WHEN OTHERS in get_terms_template ', 3);
2777 END IF;
2778
2779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2780
2781 /*****
2782 not needed as per meeting
2783 --transfer error messages on OKC stack to OM stack
2784 OE_MSG_PUB.Transfer_Msg_Stack;
2785 *****/
2786
2787 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2788 OE_MSG_PUB.Add_Exc_Msg (
2789 G_PKG_NAME,
2790 'get_terms_template'
2791 );
2792 END IF;
2793
2794
2795 --Get message count and data
2796 OE_MSG_PUB.Count_And_Get (
2797 p_count => x_msg_count,
2798 p_data => x_msg_data
2799 );
2800
2801 END get_terms_template;
2802
2803
2804
2805
2806 /* Gets the name of a contract template. It does not have to be instantiated against anything. */
2807 FUNCTION Get_Template_Name(
2808 p_api_version IN NUMBER,
2809 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2810 p_template_id IN NUMBER,
2811
2812 x_return_status OUT NOCOPY VARCHAR2,
2813 x_msg_data OUT NOCOPY VARCHAR2,
2814 x_msg_count OUT NOCOPY NUMBER
2815 ) RETURN VARCHAR2 IS
2816
2817
2818 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2819 l_template_name VARCHAR2(500);
2820
2821
2822 BEGIN
2823
2824 IF l_debug_level > 0 THEN
2825 oe_debug_pub.add('In OE_Contracts_util.Get_Template_Name ', 1);
2826 oe_debug_pub.add('p_api_version: ' || p_api_version, 3);
2827 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
2828 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.Get_Template_Name',3);
2829 oe_debug_pub.add('p_tempate_id: ' || p_template_id,3);
2830 END IF;
2831
2832 x_return_status := FND_API.G_RET_STS_SUCCESS;
2833
2834 --Check for licensing
2835 IF OE_Contracts_util.check_license() <> 'Y' THEN
2836 IF l_debug_level > 0 THEN
2837 oe_debug_pub.add('Contractual option not licensed, hence exiting Get_Template_Name ', 3);
2838 END IF;
2839 RETURN TO_CHAR(NULL); --returning null is OK here as null will be displayed
2840 END IF;
2841
2842
2843 l_template_name := OKC_TERMS_UTIL_GRP.Get_Template_Name(
2844 p_api_version => p_api_version,
2845 p_init_msg_list => p_init_msg_list,
2846 p_template_id => p_template_id,
2847 x_return_status => x_return_status,
2848 x_msg_data => x_msg_data,
2849 x_msg_count => x_msg_count
2850 );
2851
2852
2853 IF l_debug_level > 0 THEN
2854 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
2855 oe_debug_pub.add('l_template_name: ' || l_template_name, 3);
2856 END IF;
2857
2858 --ETR
2859 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2860 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2861 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2862 RAISE FND_API.G_EXC_ERROR ;
2863 END IF;
2864 --ETR
2865
2866 IF l_debug_level > 0 THEN
2867 oe_debug_pub.add('End of OE_Contracts_util.Get_Template_Name, returning l_template_name: ' || l_template_name);
2868 END IF;
2869
2870 RETURN l_template_name;
2871
2872
2873 EXCEPTION
2874 WHEN FND_API.G_EXC_ERROR THEN
2875
2876 IF l_debug_level > 0 THEN
2877 oe_debug_pub.add('WHEN G_EXC_ERROR in Get_Template_Name ', 3);
2878 END IF;
2879
2880 x_return_status := FND_API.G_RET_STS_ERROR;
2881
2882 /*****
2883 not needed as per meeting
2884 --transfer error messages on OKC stack to OM stack
2885 OE_MSG_PUB.Transfer_Msg_Stack;
2886 *****/
2887
2888 --Get message count and data
2889 OE_MSG_PUB.Count_And_Get (
2890 p_count => x_msg_count,
2891 p_data => x_msg_data
2892 );
2893
2894
2895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2896
2897 IF l_debug_level > 0 THEN
2898 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in Get_Template_Name ', 3);
2899 END IF;
2900
2901 --close any cursors
2902
2903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2904
2905 /*****
2906 not needed as per meeting
2907 --transfer error messages on OKC stack to OM stack
2908 OE_MSG_PUB.Transfer_Msg_Stack;
2909 *****/
2910
2911 --Get message count and data
2912 OE_MSG_PUB.Count_And_Get (
2913 p_count => x_msg_count,
2914 p_data => x_msg_data
2915 );
2916
2917
2918 WHEN OTHERS THEN
2919 IF l_debug_level > 0 THEN
2920 oe_debug_pub.add('WHEN OTHERS in Get_Template_Name ', 3);
2921 END IF;
2922
2923 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2924 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2925 'non_standard_article_exists'
2926 );
2927 END IF;
2928
2929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2930
2931 --Get message count and data
2932 OE_MSG_PUB.Count_And_Get (
2933 p_count => x_msg_count,
2934 p_data => x_msg_data
2935 );
2936
2937 END Get_Template_Name;
2938
2939
2940
2941
2942 --to instantiate T's/C's from a Terms template to a BSA or Sales Order
2943 --used internally by instantiate_doc_terms
2944 PROCEDURE instantiate_terms
2945 (
2946 p_api_version IN NUMBER,
2947 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2948 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2949
2950 p_template_id IN NUMBER,
2951 p_doc_type IN VARCHAR2,
2952 p_doc_id IN NUMBER,
2953 p_doc_start_date IN DATE ,
2954 p_doc_number IN VARCHAR2,
2955
2956 x_return_status OUT NOCOPY VARCHAR2,
2957 x_msg_count OUT NOCOPY NUMBER,
2958 x_msg_data OUT NOCOPY VARCHAR2
2959 )
2960
2961 IS
2962
2963 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2964 BEGIN
2965
2966 IF l_debug_level > 0 THEN
2967 oe_debug_pub.add('In OE_Contracts_util.instantiate_terms ', 1);
2968 END IF;
2969
2970 x_return_status := FND_API.G_RET_STS_SUCCESS;
2971
2972 IF p_init_msg_list = FND_API.G_TRUE THEN
2973 oe_msg_pub.initialize;
2974 END IF;
2975
2976 --Check for licensing
2977 IF OE_Contracts_util.check_license() <> 'Y' THEN
2978 IF l_debug_level > 0 THEN
2979 oe_debug_pub.add('Contractual option not licensed, hence exiting instantiate_terms ', 3);
2980 END IF;
2981 RETURN;
2982 END IF;
2983
2984
2985 IF l_debug_level > 0 THEN
2986 oe_debug_pub.add('Calling OKC_TERMS_COPY_GRP.copy_terms ', 3);
2987 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
2988 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
2989 oe_debug_pub.add('p_template_id: ' || p_template_id, 3);
2990 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
2991 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
2992 oe_debug_pub.add('p_doc_start_date: ' || p_doc_start_date, 3);
2993 oe_debug_pub.add('p_doc_number: ' || p_doc_number, 3);
2994 END IF;
2995
2996
2997 OKC_TERMS_COPY_GRP.copy_terms (
2998 p_api_version => p_api_version,
2999 p_init_msg_list => p_init_msg_list,
3000 p_commit => p_commit,
3001 p_template_id => p_template_id,
3002 p_target_doc_type => p_doc_type,
3003 p_target_doc_id => p_doc_id,
3004 ------p_article_effective_date => p_doc_start_date, -- we should not pass effectivity date ref: Bug 3307561
3005 p_article_effective_date => NULL,
3006 ------------------------------p_copy_deliverables => 'N', parameter no longer exists
3007 p_validation_string => NULL,
3008 p_document_number => p_doc_number,
3009 x_return_status => x_return_status,
3010 x_msg_count => x_msg_count,
3011 x_msg_data => x_msg_data
3012 );
3013
3014 IF l_debug_level > 0 THEN
3015 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
3016 END IF;
3017
3018 --ETR
3019 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3021 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3022 RAISE FND_API.G_EXC_ERROR ;
3023 END IF;
3024 --ETR
3025
3026 IF l_debug_level > 0 THEN
3027 oe_debug_pub.add('End of OE_Contracts_util.instantiate_terms , x_return_status ' || x_return_status, 1);
3028 END IF;
3029
3030 EXCEPTION
3031 WHEN FND_API.G_EXC_ERROR THEN
3032
3033 IF l_debug_level > 0 THEN
3034 oe_debug_pub.add('WHEN G_EXC_ERROR in instantiate_terms ', 3);
3035 END IF;
3036
3037 x_return_status := FND_API.G_RET_STS_ERROR;
3038
3039 /*****
3040 not needed as per meeting
3041 --transfer error messages on OKC stack to OM stack
3042 OE_MSG_PUB.Transfer_Msg_Stack;
3043 *****/
3044
3045 --Get message count and data
3046 OE_MSG_PUB.Count_And_Get (
3047 p_count => x_msg_count,
3048 p_data => x_msg_data
3049 );
3050
3051
3052 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3053
3054 IF l_debug_level > 0 THEN
3055 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in instantiate_terms ', 3);
3056 END IF;
3057
3058 --close any cursors
3059
3060 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3061
3062 /*****
3063 not needed as per meeting
3064 --transfer error messages on OKC stack to OM stack
3065 OE_MSG_PUB.Transfer_Msg_Stack;
3066 *****/
3067
3068 --Get message count and data
3069 OE_MSG_PUB.Count_And_Get (
3070 p_count => x_msg_count,
3071 p_data => x_msg_data
3072 );
3073
3074
3075 WHEN OTHERS THEN
3076
3077 IF l_debug_level > 0 THEN
3078 oe_debug_pub.add('WHEN OTHERS in instantiate_terms ', 3);
3079 END IF;
3080
3081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3082
3083 /*****
3084 not needed as per meeting
3085 --transfer error messages on OKC stack to OM stack
3086 OE_MSG_PUB.Transfer_Msg_Stack;
3087 *****/
3088
3089 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3090 OE_MSG_PUB.Add_Exc_Msg (
3091 G_PKG_NAME,
3092 'get_terms_template'
3093 );
3094 END IF;
3095
3096
3097 --Get message count and data
3098 OE_MSG_PUB.Count_And_Get (
3099 p_count => x_msg_count,
3100 p_data => x_msg_data
3101 );
3102
3103 END instantiate_terms;
3104
3105
3106
3107 --to instantiate T's/C's from a Terms template to a BSA or Sales Order when after saving the BSA/Sales Order
3108 --the contract template id is defaulted for a new BSA or Sales Order
3109 PROCEDURE instantiate_doc_terms
3110 (
3111 p_api_version IN NUMBER,
3112 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3113 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3114
3115 p_template_id IN NUMBER,
3116 p_doc_type IN VARCHAR2,
3117 p_doc_id IN NUMBER,
3118 p_doc_start_date IN DATE ,
3119 p_doc_number IN VARCHAR2,
3120
3121 x_return_status OUT NOCOPY VARCHAR2,
3122 x_msg_count OUT NOCOPY NUMBER,
3123 x_msg_data OUT NOCOPY VARCHAR2
3124 )
3125
3126 IS
3127
3128 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3129
3130 l_instntiatd_templt_id NUMBER;
3131 lx_template_name VARCHAR2(500);
3132
3133 BEGIN
3134
3135 IF l_debug_level > 0 THEN
3136 oe_debug_pub.add('In OE_Contracts_util.instantiate_doc_terms ', 1);
3137 END IF;
3138
3139 x_return_status := FND_API.G_RET_STS_SUCCESS;
3140
3141 IF p_init_msg_list = FND_API.G_TRUE THEN
3142 oe_msg_pub.initialize;
3143 END IF;
3144
3145 --Check for licensing
3146 IF OE_Contracts_util.check_license() <> 'Y' THEN
3147 IF l_debug_level > 0 THEN
3148 oe_debug_pub.add('Contractual option not licensed, hence exiting instantiate_doc_terms ', 3);
3149 END IF;
3150 RETURN;
3151 END IF;
3152
3153
3154 IF l_debug_level > 0 THEN
3155 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3156 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3157 oe_debug_pub.add('p_template_id: ' || p_template_id, 3);
3158 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
3159 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
3160 oe_debug_pub.add('p_doc_start_date: ' || p_doc_start_date, 3);
3161 oe_debug_pub.add('p_doc_number: ' || p_doc_number, 3);
3162 END IF;
3163
3164
3165
3166 /** In case , a contract template has already been freshly defaulted for the BSA or Sales Order, we need to instantiate
3167 the terms and conditions of the template for the BSA or Sales Order before invoking the articles authoring UI **/
3168
3169 IF p_template_id IS NOT NULL THEN
3170
3171
3172 IF l_debug_level > 0 THEN
3173 oe_debug_pub.add('Calling oe_contracts_util.get_terms_template', 3);
3174 END IF;
3175
3176
3177 --first determine whether any terms and conditions have been instantiated for the BSA or Sales Order
3178 oe_contracts_util.get_terms_template (
3179 p_api_version => 1.0,
3180 p_init_msg_list => FND_API.G_FALSE,
3181 p_commit => FND_API.G_FALSE,
3182
3183 p_doc_type => p_doc_type,
3184 p_doc_id => p_doc_id,
3185
3186 x_template_id => l_instntiatd_templt_id,
3187 x_template_name => lx_template_name,
3188 x_return_status => x_return_status,
3189 x_msg_count => x_msg_count,
3190 x_msg_data => x_msg_data
3191 );
3192
3193 IF l_debug_level > 0 THEN
3194 oe_debug_pub.add('l_instntiatd_templt_id: ' || l_instntiatd_templt_id, 3);
3195 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
3196 END IF;
3197
3198
3199 IF l_instntiatd_templt_id IS NULL THEN
3200 /** i.e. the contract template freshly defaulted in the form has not yet been
3201 instantiated so go ahead and instantiate it against the BSA or SO **/
3202
3203 IF l_debug_level > 0 THEN
3204 oe_debug_pub.add('Calling oe_contracts_util.instantiate_terms', 3);
3205 END IF;
3206
3207 oe_contracts_util.instantiate_terms (
3208 p_api_version => 1.0,
3209 p_init_msg_list => FND_API.G_FALSE,
3210 p_commit => FND_API.G_TRUE, --important: need to save before invoking articles UI
3211
3212 p_template_id => p_template_id,
3213 p_doc_type => p_doc_type,
3214 p_doc_id => p_doc_id,
3215 --------p_doc_start_date => NVL(p_doc_start_date, SYSDATE),
3216 p_doc_start_date => null, -- we should not pass effectivity date ref: Bug 3307561
3217 p_doc_number => p_doc_number,
3218 x_return_status => x_return_status,
3219 x_msg_count => x_msg_count,
3220 x_msg_data => x_msg_data
3221 );
3222
3223
3224 IF l_debug_level > 0 THEN
3225 oe_debug_pub.add('After trying to instantiate p_template_id: ' || p_template_id, 3);
3226 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
3227 END IF;
3228
3229
3230
3231 END IF;
3232
3233 END IF;
3234
3235
3236
3237 IF l_debug_level > 0 THEN
3238 oe_debug_pub.add('End of OE_Contracts_util.instantiate_doc_terms , x_return_status ' || x_return_status, 1);
3239 END IF;
3240
3241 EXCEPTION
3242 WHEN FND_API.G_EXC_ERROR THEN
3243
3244 IF l_debug_level > 0 THEN
3245 oe_debug_pub.add('WHEN G_EXC_ERROR in instantiate_doc_terms ', 3);
3246 END IF;
3247
3248 x_return_status := FND_API.G_RET_STS_ERROR;
3249
3250 /*****
3251 not needed as per meeting
3252 --transfer error messages on OKC stack to OM stack
3253 OE_MSG_PUB.Transfer_Msg_Stack;
3254 *****/
3255
3256 --Get message count and data
3257 OE_MSG_PUB.Count_And_Get (
3258 p_count => x_msg_count,
3259 p_data => x_msg_data
3260 );
3261
3262
3263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3264
3265 IF l_debug_level > 0 THEN
3266 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in instantiate_doc_terms ', 3);
3267 END IF;
3268
3269 --close any cursors
3270
3271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3272
3273 /*****
3274 not needed as per meeting
3275 --transfer error messages on OKC stack to OM stack
3276 OE_MSG_PUB.Transfer_Msg_Stack;
3277 *****/
3278
3279 --Get message count and data
3280 OE_MSG_PUB.Count_And_Get (
3281 p_count => x_msg_count,
3282 p_data => x_msg_data
3283 );
3284
3285
3286 WHEN OTHERS THEN
3287
3288 IF l_debug_level > 0 THEN
3289 oe_debug_pub.add('WHEN OTHERS in instantiate_doc_terms ', 3);
3290 END IF;
3291
3292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3293
3294 /*****
3295 not needed as per meeting
3296 --transfer error messages on OKC stack to OM stack
3297 OE_MSG_PUB.Transfer_Msg_Stack;
3298 *****/
3299
3300 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3301 OE_MSG_PUB.Add_Exc_Msg (
3302 G_PKG_NAME,
3303 'get_terms_template'
3304 );
3305 END IF;
3306
3307
3308 --Get message count and data
3309 OE_MSG_PUB.Count_And_Get (
3310 p_count => x_msg_count,
3311 p_data => x_msg_data
3312 );
3313
3314
3315
3316
3317 END instantiate_doc_terms;
3318
3319
3320 --ETR
3321 --This function is to check whether or not the given order has already been
3322 --accepted (i.e signed). Returns 'Y' if accepted, and 'N' otherwise.
3323 FUNCTION Is_order_signed(
3324 p_api_version IN NUMBER,
3325 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3326
3327 x_return_status OUT NOCOPY VARCHAR2,
3328 x_msg_data OUT NOCOPY VARCHAR2,
3329 x_msg_count OUT NOCOPY NUMBER,
3330
3331 p_doc_id IN NUMBER
3332
3333 ) RETURN VARCHAR2 IS
3334 l_api_version CONSTANT NUMBER := 1;
3335 l_api_name CONSTANT VARCHAR2(30) := 'Is_order_signed';
3336 l_return_value VARCHAR2(100) := 'N';
3337 --ETR
3338 l_sign_by VARCHAR2(240);
3339 l_sign_date DATE;
3340 --ETR
3341 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3342
3343 CURSOR find_ord_signed IS
3344 SELECT a.customer_signature,
3345 a.customer_signature_date
3346 FROM oe_order_headers_all a
3347 WHERE a.header_id = p_doc_id;
3348
3349 BEGIN
3350 IF (l_debug_level > 0) THEN
3351 oe_debug_pub.add('In OE_Contracts_util.is_order_signed', 2);
3352 END IF;
3353 -- Initialize message list if p_init_msg_list is set to TRUE.
3354 IF p_init_msg_list = FND_API.G_TRUE THEN
3355 oe_msg_pub.initialize;
3356 END IF;
3357 -- Initialize API return status to success
3358 x_return_status := FND_API.G_RET_STS_SUCCESS;
3359 IF l_debug_level > 0 THEN
3360 oe_debug_pub.add('Fetching customer_signature and customer_signature_date from oe_order_headers_all ', 3);
3361 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3362 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3363 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
3364 END IF;
3365
3366 OPEN find_ord_signed;
3367 FETCH find_ord_signed INTO l_sign_by, l_sign_date;
3368 CLOSE find_ord_signed;
3369
3370 /*************************************************
3371 IF l_sign_by IS NULL OR l_sign_date IS NULL THEN
3372 l_return_value :='N';
3373 ELSE
3374 l_return_value :='Y';
3375 END IF;
3376 *************************************************/
3377 IF l_sign_by IS NOT NULL OR l_sign_date IS NOT NULL THEN
3378 l_return_value :='Y';
3379 ELSE
3380 l_return_value :='N';
3381 END IF;
3382
3383
3384 IF l_debug_level > 0 THEN
3385 oe_debug_pub.add('Order signed ?: sign_by = ' || l_sign_by || ' sign_date = ' || l_sign_date, 3);
3386 oe_debug_pub.add('Order signed ?: return value = ' || l_return_value, 3);
3387 END IF;
3388
3389 IF (l_debug_level > 0) THEN
3390 oe_debug_pub.add('End of OE_Contracts_util.is_order_signed', 2);
3391 END IF;
3392 RETURN l_return_value;
3393
3394 EXCEPTION
3395
3396 WHEN OTHERS THEN
3397 IF l_debug_level > 0 THEN
3398 oe_debug_pub.add('WHEN-OTHERS in is_order_signed: '||sqlerrm, 1);
3399 END IF;
3400 IF find_ord_signed%ISOPEN THEN
3401 CLOSE find_ord_signed;
3402 END IF;
3403
3404 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3405 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
3406 'is_order_signed'
3407 );
3408 END IF;
3409 x_return_status := G_RET_STS_UNEXP_ERROR;
3410 END Is_order_signed ;
3411 --ETR
3412
3413
3414
3415 --This function will be called from process order to copy terms and coditions
3416 --from quote to order(terms instantiated on quote)
3417 --from quote to order(terms not instantiated on quote) ,get terms from template
3418 -- from sales order to sales order
3419 --instantiate from template to sales order
3420
3421 PROCEDURE copy_doc
3422 (
3423 p_api_version IN NUMBER,
3424 p_init_msg_list IN VARCHAR2,
3425 p_commit IN VARCHAR2,
3426 p_source_doc_type IN VARCHAR2,
3427 p_source_doc_id IN NUMBER,
3428 p_target_doc_type IN VARCHAR2,
3429 p_target_doc_id IN NUMBER,
3430 p_contract_template_id IN NUMBER,
3431 x_return_status OUT NOCOPY VARCHAR2,
3432 x_msg_count OUT NOCOPY NUMBER,
3433 x_msg_data OUT NOCOPY VARCHAR2)
3434
3435 IS
3436
3437 l_target_doc_type VARCHAR2(30):= p_target_doc_type;
3438 l_target_doc_id NUMBER := p_target_doc_id;
3439 l_doc_template_name VARCHAR2(240):= null;
3440 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3441 l_keep_version VARCHAR2(1) := 'Y';
3442 l_copy_attch VARCHAR2(1) := 'N';
3443 l_document_number NUMBER:= null;
3444
3445 BEGIN
3446
3447 IF l_debug_level > 0 THEN
3448 oe_debug_pub.add('In OE_Contracts_util.copy_doc ', 1);
3449 END IF;
3450
3451 x_return_status := FND_API.G_RET_STS_SUCCESS;
3452
3453 IF p_init_msg_list = FND_API.G_TRUE THEN
3454 oe_msg_pub.initialize;
3455 END IF;
3456
3457 --Check contract Licence
3458 IF OE_Contracts_util.check_license() <> 'Y' THEN
3459 IF l_debug_level > 0 THEN
3460 oe_debug_pub.add('Contractual option not licensed. Exiting copy_doc', 3);
3461 END IF;
3462 RETURN;
3463 END IF;
3464
3465
3466 IF l_debug_level > 0 THEN
3467 oe_debug_pub.add('Parameter Values passed', 3);
3468 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3469 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3470 oe_debug_pub.add('p_commit: ' || p_commit,3);
3471 oe_debug_pub.add('p_source_doc_type: ' || p_source_doc_type,3);
3472 oe_debug_pub.add('p_source_doc_id: ' || p_source_doc_id, 3);
3473 oe_debug_pub.add('p_target_doc_type: ' || p_target_doc_type,3);
3474 oe_debug_pub.add('p_target_doc_id: ' || p_target_doc_id, 3);
3475 oe_debug_pub.add('p_contract_template_id' || p_contract_template_id, 3);
3476 END IF;
3477
3478 IF p_source_doc_id is not null Then
3479 l_doc_template_name := okc_terms_util_grp.Get_Terms_Template(
3480 p_doc_type => p_source_doc_type,
3481 p_doc_id => p_source_doc_id);
3482 END IF;
3483
3484 IF p_target_doc_type = 'O' THEN
3485 BEGIN
3486 SELECT order_number
3487 INTO l_document_number
3488 FROM oe_order_headers_all
3489 WHERE header_id = p_target_doc_id;
3490 EXCEPTION
3491 WHEN NO_DATA_FOUND THEN
3492 l_document_number := NULL;
3493 END;
3494 END IF;
3495 If l_debug_level > 0 THEN
3496 oe_debug_pub.add('l_document_number: ' || l_document_number, 3);
3497 End If;
3498
3499 IF l_doc_template_name is null THEN
3500 --Instantiate from the template
3501 If p_contract_template_id is not null then
3502 If l_debug_level > 0 THEN
3503 oe_debug_pub.add('Instantiating COntract Terms, No articles on source document',3);
3504 End If;
3505
3506 OKC_TERMS_COPY_GRP.copy_terms (
3507 p_api_version => p_api_version,
3508 p_init_msg_list => p_init_msg_list,
3509 p_commit => p_commit,
3510 p_template_id => p_contract_template_id,
3511 p_target_doc_type => l_target_doc_type,
3512 p_target_doc_id => p_target_doc_id,
3513 p_validation_string => NULL,
3514 -----p_article_effective_date => sysdate, -- we should not pass effectivity date ref: Bug 3307561
3515 p_article_effective_date => null,
3516 p_document_number => to_char(l_document_number),
3517 x_return_status => x_return_status,
3518 x_msg_count => x_msg_count,
3519 x_msg_data => x_msg_data);
3520
3521
3522 IF x_return_status = FND_API.G_RET_STS_ERROR Then
3523 RAISE FND_API.G_EXC_ERROR;
3524 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3526 END IF;
3527
3528 End If;
3529 ELSE
3530 If l_debug_level > 0 THEN
3531 oe_debug_pub.add('Terms exist in source document',3);
3532 End If;
3533
3534 If (p_source_doc_type <> p_target_doc_type) Then
3535 l_copy_attch := 'Y';
3536 Else
3537 l_keep_version :='N';
3538 End If;
3539
3540 If (p_source_doc_type =p_target_doc_type and
3541 p_source_doc_id =p_target_doc_id) THEN
3542
3543 If l_debug_level > 0 THEN
3544 oe_debug_pub.add('Target Doc and Source Document are Same. exiting copy_doc',3);
3545 End If;
3546 RETURN;
3547 End If;
3548
3549 If l_debug_level > 0 THEN
3550 oe_debug_pub.add('Calling OKC_TERMS_COPY_GRP.copy_doc',3);
3551 oe_debug_pub.add('p_copy_doc_attachments: ' ||l_copy_attch,3);
3552 oe_debug_pub.add('p_keep_version: ' || l_keep_version, 3);
3553 oe_debug_pub.add('p_target_doc_type: ' ||l_target_doc_type,3);
3554 oe_debug_pub.add('p_target_doc_id: ' || l_target_doc_id, 3);
3555 End If;
3556
3557 OKC_TERMS_COPY_GRP.copy_doc (
3558 p_api_version => p_api_version,
3559 p_init_msg_list => p_init_msg_list,
3560 p_commit => p_commit,
3561 p_source_doc_type => p_source_doc_type,
3562 p_source_doc_id => p_source_doc_id,
3563 p_target_doc_type => l_target_doc_type,
3564 p_target_doc_id => l_target_doc_id,
3565 p_keep_version => l_keep_version,
3566 -----p_article_effective_date => sysdate,
3567 p_article_effective_date => null, -- we should not pass effectivity date ref: Bug 3307561
3568 p_copy_doc_attachments => l_copy_attch,
3569 x_return_status => x_return_status,
3570 x_msg_data => x_msg_data,
3571 x_msg_count => x_msg_count,
3572 p_copy_abstract_yn => 'Y');
3573
3574
3575 IF x_return_status = FND_API.G_RET_STS_ERROR Then
3576 RAISE FND_API.G_EXC_ERROR;
3577 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3579 END IF;
3580
3581
3582 END IF;
3583
3584 If l_debug_level > 0 THEN
3585 oe_debug_pub.add('Return Status + x_return_status',3);
3586 End If;
3587
3588
3589 EXCEPTION
3590 WHEN FND_API.G_EXC_ERROR THEN
3591
3592 IF l_debug_level > 0 THEN
3593 oe_debug_pub.add('WHEN G_EXC_ERROR in copy_doc ', 3);
3594 END IF;
3595 --Get message count and data
3596 OE_MSG_PUB.Count_And_Get (
3597 p_count => x_msg_count,
3598 p_data => x_msg_data);
3599
3600 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3601
3602 IF l_debug_level > 0 THEN
3603 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in copy_doc ', 3);
3604 END IF;
3605
3606 --Get message count and data
3607 OE_MSG_PUB.Count_And_Get (
3608 p_count => x_msg_count,
3609 p_data => x_msg_data);
3610
3611 WHEN OTHERS THEN
3612
3613 IF l_debug_level > 0 THEN
3614 oe_debug_pub.add('WHEN OTHERS in copy_doc ', 3);
3615 END IF;
3616
3617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3618
3619 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3620 OE_MSG_PUB.Add_Exc_Msg (
3621 G_PKG_NAME,
3622 'copy_doc');
3623 END IF;
3624
3625 --Get message count and data
3626 OE_MSG_PUB.Count_And_Get (
3627 p_count => x_msg_count,
3628 p_data => x_msg_data
3629 );
3630
3631 END COPY_DOC;
3632
3633
3634
3635
3636 -- This function is a wrapper on top of oe_line_util.get_item_info
3637 -- procedure. This is used to get the value and description for the products
3638 -- in the blanket sales lines.
3639 -- This will return the internal item and description for all but customer items
3640 -- for which it returns the customer product and description
3641 -- This function is used in the oe_blktprt_lines_v view, for the printing solution
3642
3643 FUNCTION GET_ITEM_INFO
3644 ( p_item_or_desc IN VARCHAR2
3645 , p_item_identifier_type IN VARCHAR2
3646 , p_inventory_item_id IN Number
3647 , p_ordered_item_id IN Number
3648 , p_sold_to_org_id IN Number
3649 , p_ordered_item IN VARCHAR2
3650 , p_org_id IN Number DEFAULT NULL
3651 ) RETURN VARCHAR2 IS
3652
3653 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3654 l_ordered_item varchar2(2000);
3655 l_inventory_item varchar2(300);
3656 l_ordered_item_desc varchar2(2000);
3657 l_return_status varchar2(30);
3658 l_msg_count number := 0;
3659 l_msg_data varchar2(2000);
3660 l_value varchar2(2000);
3661
3662 BEGIN
3663 IF l_debug_level > 0 THEN
3664 oe_debug_pub.add( 'ENTER GET_ITEM_INFO FUNCTION' ) ;
3665 oe_debug_pub.add( 'ITEM_OR_DESC : '||P_ITEM_OR_DESC ) ;
3666 oe_debug_pub.add( 'ITEM_IDENTIFIER_TYPE : '||P_ITEM_IDENTIFIER_TYPE ) ;
3667 oe_debug_pub.add( 'INVENTORY_ITEM_ID : '||P_INVENTORY_ITEM_ID ) ;
3668 oe_debug_pub.add( 'ORDERED_ITEM_ID : '||P_ORDERED_ITEM_ID ) ;
3669 oe_debug_pub.add( 'ORDERED_ITEM : '||P_ORDERED_ITEM ) ;
3670 oe_debug_pub.add( 'SOLD_TO_ORG_ID : '||P_SOLD_TO_ORG_ID ) ;
3671 END IF;
3672
3673 OE_LINE_UTIL.GET_ITEM_INFO (
3674 x_return_status => l_return_status
3675 , x_msg_count => l_msg_count
3676 , x_msg_data => l_msg_data
3677 , p_item_identifier_type => p_item_identifier_type
3678 , p_inventory_item_id => p_inventory_item_id
3679 , p_ordered_item_id => p_ordered_item_id
3680 , p_sold_to_org_id => p_sold_to_org_id
3681 , p_ordered_item => p_ordered_item
3682 , x_ordered_item => l_ordered_item
3683 , x_ordered_item_desc => l_ordered_item_desc
3684 , x_inventory_item => l_inventory_item
3685 , p_org_id => p_org_id
3686
3687 );
3688
3689 IF l_debug_level > 0 THEN
3690 oe_debug_pub.add('Return status from OE_LINE_UTIL.GET_ITEM_INFO is '||l_return_status) ;
3691 END IF;
3692
3693 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3695 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3696 RAISE FND_API.G_EXC_ERROR;
3697 END IF;
3698
3699 IF p_item_or_desc = 'I' THEN
3700 l_value := l_inventory_item;
3701 ELSIF p_item_or_desc = 'D' THEN
3702 l_value := l_ordered_item_desc;
3703 END IF;
3704
3705 IF l_debug_level > 0 THEN
3706 oe_debug_pub.add( 'l_value = '||l_value ) ;
3707 END IF;
3708
3709 IF l_debug_level > 0 THEN
3710 oe_debug_pub.add( 'EXIT GET_ITEM_INFO FUNCTION' ) ;
3711 END IF;
3712
3713 RETURN l_value;
3714
3715 EXCEPTION
3716 WHEN FND_API.G_EXC_ERROR THEN
3717 IF l_debug_level > 0 THEN
3718 oe_debug_pub.add('In oe_contracts_util.get_item_info:g_exc_error section') ;
3719 END IF;
3720 RAISE FND_API.G_EXC_ERROR;
3721 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3722 IF l_debug_level > 0 THEN
3723 oe_debug_pub.add('In oe_contracts_util.get_item_info:g_exc_unexpected_error section') ;
3724 END IF;
3725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3726 WHEN OTHERS THEN
3727 IF l_debug_level > 0 THEN
3728 oe_debug_pub.add('In oe_contracts_util.get_item_info: when others section') ;
3729 END IF;
3730 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3731 THEN
3732 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'GET_ITEM_INFO');
3733 END IF;
3734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3735 END GET_ITEM_INFO;
3736
3737 --FP word integration
3738 PROCEDURE get_contract_defaults
3739 (
3740 p_api_version IN NUMBER,
3741 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3742 p_doc_type IN VARCHAR2,
3743 p_template_id IN NUMBER,
3744 x_authoring_party OUT NOCOPY VARCHAR2,
3745 x_contract_source OUT NOCOPY VARCHAR2,
3746 x_template_name OUT NOCOPY VARCHAR2,
3747 x_return_status OUT NOCOPY VARCHAR2,
3748 x_msg_count OUT NOCOPY NUMBER,
3749 x_msg_data OUT NOCOPY VARCHAR2
3750 )
3751
3752 IS
3753 l_template_description VARCHAR2(2000); -- bug 4382305
3754 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3755
3756 BEGIN
3757
3758 IF l_debug_level > 0 THEN
3759 oe_debug_pub.add('In OE_Contracts_util.get_contract_defaults ', 1);
3760 END IF;
3761
3762 x_return_status := FND_API.G_RET_STS_SUCCESS;
3763
3764 IF p_init_msg_list = FND_API.G_TRUE THEN
3765 oe_msg_pub.initialize;
3766 END IF;
3767
3768 --Check for licensing
3769 IF OE_Contracts_util.check_license() <> 'Y' THEN
3770 IF l_debug_level > 0 THEN
3771 oe_debug_pub.add('Contractual option not licensed, hence exiting get_terms_template ', 3);
3772 END IF;
3773 x_contract_source := NULL;
3774 x_authoring_party := NULL;
3775 x_template_name := NULL;
3776 RETURN;
3777 END IF;
3778
3779
3780 IF l_debug_level > 0 THEN
3781 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.get_contract_defaults ', 3);
3782 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3783 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3784 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
3785 oe_debug_pub.add('p_template_id: ' || p_template_id, 3);
3786 END IF;
3787
3788 OKC_TERMS_UTIL_GRP.get_contract_defaults (
3789 p_api_version => p_api_version,
3790 p_init_msg_list => p_init_msg_list,
3791 x_return_status => x_return_status,
3792 x_msg_data => x_msg_data,
3793 x_msg_count => x_msg_count,
3794 p_template_id => p_template_id,
3795 p_document_type => p_doc_type,
3796 x_authoring_party => x_authoring_party,
3797 x_contract_source => x_contract_source,
3798 x_template_name => x_template_name,
3799 x_template_description => l_template_description
3800 );
3801
3802 IF l_debug_level > 0 THEN
3803 oe_debug_pub.add('p_template_id: '|| p_template_id, 3);
3804 oe_debug_pub.add('x_contract_source: '|| x_contract_source, 3);
3805 oe_debug_pub.add('x_authoring_party: '|| x_authoring_party, 3);
3806 oe_debug_pub.add('x_template_name: '|| x_template_name, 3);
3807 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
3808 END IF;
3809
3810 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3812 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3813 RAISE FND_API.G_EXC_ERROR ;
3814 END IF;
3815
3816 IF l_debug_level > 0 THEN
3817 oe_debug_pub.add('End of OE_Contracts_util.get_contract_defaults , x_return_status ' || x_return_status, 1);
3818 END IF;
3819
3820 EXCEPTION
3821 WHEN FND_API.G_EXC_ERROR THEN
3822
3823 IF l_debug_level > 0 THEN
3824 oe_debug_pub.add('WHEN G_EXC_ERROR in get_contract_defaults ', 3);
3825 END IF;
3826
3827 x_return_status := FND_API.G_RET_STS_ERROR;
3828
3829 --Get message count and data
3830 OE_MSG_PUB.Count_And_Get (
3831 p_count => x_msg_count,
3832 p_data => x_msg_data
3833 );
3834
3835
3836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3837
3838 IF l_debug_level > 0 THEN
3839 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_contract_defaults ', 3);
3840 END IF;
3841
3842 --close any cursors
3843
3844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3845
3846 --Get message count and data
3847 OE_MSG_PUB.Count_And_Get (
3848 p_count => x_msg_count,
3849 p_data => x_msg_data
3850 );
3851
3852
3853 WHEN OTHERS THEN
3854
3855 IF l_debug_level > 0 THEN
3856 oe_debug_pub.add('WHEN OTHERS in get_contract_defaults ', 3);
3857 END IF;
3858
3859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3860
3861 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3862 OE_MSG_PUB.Add_Exc_Msg (
3863 G_PKG_NAME,
3864 'get_contract_defaults'
3865 );
3866 END IF;
3867
3868
3869 --Get message count and data
3870 OE_MSG_PUB.Count_And_Get (
3871 p_count => x_msg_count,
3872 p_data => x_msg_data
3873 );
3874
3875 END get_contract_defaults;
3876
3877
3878 --get the template name, id, source and authoring party for the doc id
3879 PROCEDURE get_contract_details_all
3880 (
3881 p_api_version IN NUMBER,
3882 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3883 p_doc_type IN VARCHAR2,
3884 p_doc_id IN NUMBER,
3885 p_document_version IN NUMBER := NULL,
3886 x_template_id OUT NOCOPY NUMBER,
3887 x_authoring_party OUT NOCOPY VARCHAR2,
3888 x_contract_source OUT NOCOPY VARCHAR2,
3889 x_contract_source_code OUT NOCOPY VARCHAR2,
3890 x_has_primary_doc OUT NOCOPY VARCHAR2,
3891 x_template_name OUT NOCOPY VARCHAR2,
3892 x_return_status OUT NOCOPY VARCHAR2,
3893 x_msg_count OUT NOCOPY NUMBER,
3894 x_msg_data OUT NOCOPY VARCHAR2
3895 )
3896
3897 IS
3898
3899 l_has_terms VARCHAR2(100);
3900 l_authoring_party_code VARCHAR2(100);
3901 l_template_description VARCHAR2(2000); -- bug 4382305
3902 l_template_instruction VARCHAR2(2000); -- bug 4382305
3903 l_is_primary_doc_mergeable VARCHAR2(100);
3904 l_primary_doc_file_id VARCHAR2(100);
3905 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3906
3907
3908 BEGIN
3909
3910 IF l_debug_level > 0 THEN
3911 oe_debug_pub.add('In OE_Contracts_util.get_contract_details_all ', 1);
3912 END IF;
3913
3914 x_return_status := FND_API.G_RET_STS_SUCCESS;
3915
3916 IF p_init_msg_list = FND_API.G_TRUE THEN
3917 oe_msg_pub.initialize;
3918 END IF;
3919
3920 --Check for licensing
3921 IF OE_Contracts_util.check_license() <> 'Y' THEN
3922 IF l_debug_level > 0 THEN
3923 oe_debug_pub.add('Contractual option not licensed, hence exiting get_contract_details_all ', 3);
3924 END IF;
3925 x_contract_source := NULL;
3926 x_authoring_party := NULL;
3927 x_template_name := NULL;
3928 x_template_id := NULL;
3929 RETURN;
3930 END IF;
3931
3932
3933 IF l_debug_level > 0 THEN
3934 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.get_contract_details_all ', 3);
3935 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3936 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3937 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
3938 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
3939 END IF;
3940
3941 OKC_TERMS_UTIL_GRP.get_contract_details_all (
3942 p_api_version => p_api_version,
3943 p_init_msg_list => p_init_msg_list,
3944 x_return_status => x_return_status,
3945 x_msg_data => x_msg_data,
3946 x_msg_count => x_msg_count,
3947 p_document_type => p_doc_type,
3948 p_document_id => p_doc_id,
3949 p_document_version => p_document_version,
3950 x_has_terms => l_has_terms,
3951 x_authoring_party_code => l_authoring_party_code,
3952 x_authoring_party => x_authoring_party,
3953 x_contract_source_code => x_contract_source_code,
3954 x_contract_source => x_contract_source,
3955 x_template_id => x_template_id,
3956 x_template_name => x_template_name,
3957 x_template_description => l_template_description,
3958 x_template_instruction => l_template_instruction,
3959 x_has_primary_doc => x_has_primary_doc,
3960 x_is_primary_doc_mergeable => l_is_primary_doc_mergeable,
3961 x_primary_doc_file_id => l_primary_doc_file_id
3962 );
3963
3964 IF l_debug_level > 0 THEN
3965 oe_debug_pub.add('x_contract_source: '|| x_contract_source, 3);
3966 oe_debug_pub.add('x_contract_source_code: '|| x_contract_source_code, 3);
3967 oe_debug_pub.add('x_template_id: '|| x_template_id, 3);
3968 oe_debug_pub.add('x_authoring_party: '|| x_authoring_party, 3);
3969 oe_debug_pub.add('x_template_name: '|| x_template_name, 3);
3970 oe_debug_pub.add('x_has_primary_doc: '|| x_has_primary_doc, 3);
3971 oe_debug_pub.add('x_msg_data: '|| x_msg_data, 3); -- bug 4382305
3972 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
3973 END IF;
3974
3975 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3977 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3978 RAISE FND_API.G_EXC_ERROR ;
3979 END IF;
3980
3981 IF l_debug_level > 0 THEN
3982 oe_debug_pub.add('End of OE_Contracts_util.get_contract_details_all , x_return_status ' || x_return_status, 1);
3983 END IF;
3984
3985 EXCEPTION
3986 WHEN FND_API.G_EXC_ERROR THEN
3987
3988 IF l_debug_level > 0 THEN
3989 oe_debug_pub.add('WHEN G_EXC_ERROR in get_contract_details_all ', 3);
3990 END IF;
3991
3992 x_return_status := FND_API.G_RET_STS_ERROR;
3993
3994 --Get message count and data
3995 OE_MSG_PUB.Count_And_Get (
3996 p_count => x_msg_count,
3997 p_data => x_msg_data
3998 );
3999
4000
4001 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4002
4003 IF l_debug_level > 0 THEN
4004 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_contract_details_all ', 3);
4005 END IF;
4006
4007 --close any cursors
4008
4009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4010
4011 --Get message count and data
4012 OE_MSG_PUB.Count_And_Get (
4013 p_count => x_msg_count,
4014 p_data => x_msg_data
4015 );
4016
4017
4018 WHEN OTHERS THEN
4019
4020 IF l_debug_level > 0 THEN
4021 oe_debug_pub.add('WHEN OTHERS in get_contract_details_all ', 3);
4022 END IF;
4023
4024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4025
4026 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4027 OE_MSG_PUB.Add_Exc_Msg (
4028 G_PKG_NAME,
4029 'get_contract_details_all'
4030 );
4031 END IF;
4032
4033
4034 --Get message count and data
4035 OE_MSG_PUB.Count_And_Get (
4036 p_count => x_msg_count,
4037 p_data => x_msg_data
4038 );
4039
4040 END get_contract_details_all;
4041
4042
4043
4044
4045 --check if template attached to order type is valid or not
4046 Function Is_Terms_Template_Valid
4047 (
4048 p_api_version IN NUMBER,
4049 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4050 x_return_status OUT NOCOPY VARCHAR2,
4051 x_msg_count OUT NOCOPY NUMBER,
4052 x_msg_data OUT NOCOPY VARCHAR2,
4053 p_doc_type IN VARCHAR2,
4054 p_template_id IN NUMBER,
4055 p_org_id IN NUMBER
4056 ) RETURN VARCHAR2 IS
4057
4058 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4059 l_result VARCHAR2(1) := 'N';
4060
4061 BEGIN
4062
4063 IF l_debug_level > 0 THEN
4064 oe_debug_pub.add('In OE_Contracts_util.Is_Terms_Template_Valid ', 1);
4065 END IF;
4066
4067 x_return_status := FND_API.G_RET_STS_SUCCESS;
4068
4069 IF p_init_msg_list = FND_API.G_TRUE THEN
4070 oe_msg_pub.initialize;
4071 END IF;
4072
4073 --Check for licensing
4074 IF OE_Contracts_util.check_license() <> 'Y' THEN
4075 IF l_debug_level > 0 THEN
4076 oe_debug_pub.add('Contractual option not licensed, hence exiting Is_Terms_Template_Valid ', 3);
4077 END IF;
4078 RETURN NULL;
4079 END IF;
4080
4081
4082 IF l_debug_level > 0 THEN
4083 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.Is_Terms_Template_Valid ', 3);
4084 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
4085 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
4086 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
4087 oe_debug_pub.add('p_template_id: ' || p_template_id, 3);
4088 END IF;
4089
4090 l_result := OKC_TERMS_UTIL_GRP.Is_Terms_Template_Valid (
4091 p_api_version => p_api_version,
4092 p_init_msg_list => p_init_msg_list,
4093 x_return_status => x_return_status,
4094 x_msg_data => x_msg_data,
4095 x_msg_count => x_msg_count,
4096 p_template_id => p_template_id,
4097 p_doc_type => p_doc_type,
4098 p_org_id => p_org_id,
4099 p_valid_date => SYSDATE
4100 );
4101
4102
4103 IF l_debug_level > 0 THEN
4104 oe_debug_pub.add('p_template_id: '|| p_template_id, 3);
4105 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
4106 oe_debug_pub.add(' l_result: '|| l_result, 3);
4107 END IF;
4108
4109 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4111 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4112 RAISE FND_API.G_EXC_ERROR ;
4113 END IF;
4114
4115 IF l_debug_level > 0 THEN
4116 oe_debug_pub.add('End of OE_Contracts_util.Is_Terms_Template_Valid , x_return_status ' || x_return_status, 1);
4117 END IF;
4118
4119 RETURN l_result;
4120
4121 EXCEPTION
4122 WHEN FND_API.G_EXC_ERROR THEN
4123
4124 IF l_debug_level > 0 THEN
4125 oe_debug_pub.add('WHEN G_EXC_ERROR in Is_Terms_Template_Valid ', 3);
4126 END IF;
4127
4128 x_return_status := FND_API.G_RET_STS_ERROR;
4129
4130 --Get message count and data
4131 OE_MSG_PUB.Count_And_Get (
4132 p_count => x_msg_count,
4133 p_data => x_msg_data
4134 );
4135
4136
4137 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4138
4139 IF l_debug_level > 0 THEN
4140 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in Is_Terms_Template_Valid ', 3);
4141 END IF;
4142
4143 --close any cursors
4144
4145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4146
4147 --Get message count and data
4148 OE_MSG_PUB.Count_And_Get (
4149 p_count => x_msg_count,
4150 p_data => x_msg_data
4151 );
4152
4153
4154 WHEN OTHERS THEN
4155
4156 IF l_debug_level > 0 THEN
4157 oe_debug_pub.add('WHEN OTHERS in Is_Terms_Template_Valid ', 3);
4158 END IF;
4159
4160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4161
4162 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4163 OE_MSG_PUB.Add_Exc_Msg (
4164 G_PKG_NAME,
4165 'Is_Terms_Template_Valid'
4166 );
4167 END IF;
4168
4169
4170 --Get message count and data
4171 OE_MSG_PUB.Count_And_Get (
4172 p_count => x_msg_count,
4173 p_data => x_msg_data
4174 );
4175
4176 END Is_Terms_Template_Valid;
4177
4178
4179 --Function to check if the Authoring Party is Internal, required by Preview and Print
4180 Function Is_Auth_Party_Internal
4181 (
4182 p_doc_type IN VARCHAR2,
4183 p_doc_id IN NUMBER
4184 )
4185 RETURN VARCHAR2 IS
4186 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4187 l_authoring_party_code VARCHAR2(100);
4188 BEGIN
4189
4190 IF l_debug_level > 0 THEN
4191 oe_debug_pub.add('In OE_Contracts_util.Is_Auth_Party_Internal ', 1);
4192 END IF;
4193
4194 --Check for licensing
4195 IF OE_Contracts_util.check_license() <> 'Y' THEN
4196 IF l_debug_level > 0 THEN
4197 oe_debug_pub.add('Contractual option not licensed, hence exiting Is_Auth_Party_Internal ', 3);
4198 END IF;
4199
4200 RETURN 'N';
4201 END IF;
4202
4203 IF l_debug_level > 0 THEN
4204 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.Get_Authoring_Party_Code', 3);
4205 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
4206 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
4207 END IF;
4208
4209 l_authoring_party_code := OKC_TERMS_UTIL_GRP.Get_Authoring_Party_Code(
4210 p_document_type => p_doc_type,
4211 p_document_id => p_doc_id
4212 );
4213
4214 IF l_debug_level > 0 THEN
4215 oe_debug_pub.add('l_authoring_party_code '|| l_authoring_party_code, 3);
4216 END IF;
4217
4218 IF l_authoring_party_code = 'INTERNAL_ORG' THEN
4219 RETURN 'Y';
4220 ELSE
4221 RETURN 'N';
4222 END IF;
4223
4224 EXCEPTION
4225
4226 WHEN OTHERS THEN
4227
4228 IF l_debug_level > 0 THEN
4229 oe_debug_pub.add('WHEN-OTHERS in Is_Auth_Party_Internal ', 1);
4230 END IF;
4231
4232 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4233 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4234 'Is_Auth_Party_Internal'
4235 );
4236 END IF;
4237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4238
4239 END Is_Auth_Party_Internal;
4240
4241 --Function to check if Recurring Charges is Enabled, required by Preview and Print
4242 Function Is_RChg_Enabled
4243 (
4244 p_doc_id IN NUMBER
4245 )
4246 RETURN VARCHAR2 IS
4247 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4248 l_rch_enabled VARCHAR2(1) := 'N';
4249 l_org_id NUMBER := NULL;
4250
4251 BEGIN
4252
4253 IF l_debug_level > 0 THEN
4254 oe_debug_pub.add('In OE_Contracts_util.Is_RChg_Enabled ', 1);
4255 END IF;
4256 --Get the org_id
4257 BEGIN
4258 SELECT org_id into l_org_id
4259 FROM oe_order_headers_all
4260 WHERE header_id=p_doc_id;
4261 EXCEPTION
4262 when others then
4263 l_org_id:=NULL;
4264 END;
4265
4266 IF l_debug_level > 0 THEN
4267 oe_debug_pub.add('Calling OE_SYS_PARAMETER.VALUE(RECURRING_CHARGES)', 3);
4268 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
4269 oe_debug_pub.add('l_org_id: ' || l_org_id, 3);
4270 END IF;
4271
4272 IF l_org_id is not null then
4273 l_rch_enabled := nvl(OE_SYS_PARAMETERS.VALUE('RECURRING_CHARGES',l_org_id),'N');
4274 ELSE
4275 l_rch_enabled := 'N';
4276 END IF;
4277
4278 IF l_debug_level > 0 THEN
4279 oe_debug_pub.add('l_rch_enabled '|| l_rch_enabled, 3);
4280 END IF;
4281
4282 return l_rch_enabled;
4283
4284 EXCEPTION
4285
4286 WHEN OTHERS THEN
4287
4288 IF l_debug_level > 0 THEN
4289 oe_debug_pub.add('WHEN-OTHERS in Is_RChg_Enabled ', 1);
4290 END IF;
4291
4292 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4293 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4294 'Is_RChg_Enabled'
4295 );
4296 END IF;
4297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4298
4299 END Is_RChg_Enabled;
4300
4301 END OE_Contracts_util;