[Home] [Help]
PACKAGE BODY: APPS.OE_CONTRACTS_UTIL
Source
1 PACKAGE BODY OE_Contracts_util AS
2 /* $Header: OEXUOKCB.pls 120.4.12010000.2 2008/11/12 12:36:53 smanian 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 --Check for licensing, proceed with procesing only if licensed
1205 IF OE_Contracts_util.check_license() <> 'Y' THEN
1206 IF l_debug_level > 0 THEN
1207 oe_debug_pub.add('Contractual option not licensed, hence exiting attachment_location ', 3);
1208 END IF;
1209 RETURN;
1210 END IF;
1211
1212
1213
1214 --Determine whether any attachments exist for the BSA or sales Order, proceed with processing only if attachments exist
1215 IF c_attachment_exist_check%ISOPEN THEN
1216 CLOSE c_attachment_exist_check;
1217 END IF;
1218 OPEN c_attachment_exist_check;
1219 FETCH c_attachment_exist_check INTO l_attachment_exist_check;
1220 CLOSE c_attachment_exist_check;
1221 IF l_attachment_exist_check IS NULL THEN
1222 IF l_debug_level > 0 THEN
1223 oe_debug_pub.add('No attachments exist for the BSA or Sales Order, hence exiting attachment_location...', 3);
1224 END IF;
1225 x_workflow_string := NULL; --returning NULL will ensure that no paper clip icon is shown in the workflow
1226 RETURN;
1227 END IF;
1228
1229
1230 IF l_debug_level > 0 THEN
1231 oe_debug_pub.add('Calling OE_Contracts_util.get_terms_template ', 3);
1232 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
1233 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
1234 oe_debug_pub.add('p_doc_type: '|| p_doc_type, 3);
1235 oe_debug_pub.add('p_doc_id: '|| p_doc_id, 3);
1236 END IF;
1237
1238 --first determine whether any terms and conditions have been instantiated for the BSA or Sales Order
1239 oe_contracts_util.get_terms_template (
1240 p_api_version => 1.0,
1241 p_init_msg_list => p_init_msg_list,
1242
1243 p_doc_type => p_doc_type,
1244 p_doc_id => p_doc_id,
1245
1246 x_template_id => l_template_id,
1247 x_template_name => l_template_name,
1248 x_return_status => x_return_status,
1249 x_msg_count => x_msg_count,
1250 x_msg_data => x_msg_data
1251 );
1252
1253
1254 IF l_debug_level > 0 THEN
1255 oe_debug_pub.add('x_template_id: '|| l_template_id, 3);
1256 oe_debug_pub.add('x_template_name: '|| l_template_name, 3);
1257 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
1258 END IF;
1259
1260 /*
1261 IF l_template_id IS NOT NULL THEN
1262 --terms and conditions do exist for the BSA or Sales Order so return contract attachment string to workflow request
1263 */
1264 IF OE_CONTRACTS_UTIL.Terms_Exists
1265 ( p_doc_type => p_doc_type
1266 , p_doc_id => p_doc_id) = 'Y' THEN
1267
1268 /************
1269 --get the version number for pk3 of contract attachment entity OKC_CONTRACT_DOCS
1270 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
1271 IF c_get_bsa_version%ISOPEN THEN
1272 CLOSE c_get_bsa_version;
1273 END IF;
1274
1275 OPEN c_get_bsa_version (p_doc_id);
1276 FETCH c_get_bsa_version INTO l_doc_version_number;
1277 CLOSE c_get_bsa_version;
1278
1279 IF l_debug_level > 0 THEN
1280 oe_debug_pub.add('l_doc_version_number of blanket: '|| l_doc_version_number, 3);
1281 END IF;
1282
1283 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
1284 IF c_get_so_version%ISOPEN THEN
1285 CLOSE c_get_so_version;
1286 END IF;
1287
1288 OPEN c_get_so_version (p_doc_id);
1289 FETCH c_get_so_version INTO l_doc_version_number;
1290 CLOSE c_get_so_version;
1291
1292 IF l_debug_level > 0 THEN
1293 oe_debug_pub.add('l_doc_version_number of sales order: '|| l_doc_version_number, 3);
1294 END IF;
1295
1296 END IF;
1297 ************/
1298
1299 l_workflow_string := 'FND:entity=OKC_CONTRACT_DOCS'
1300 || '&' || 'pk1name=BusinessDocumentType'
1301 || '&' || 'pk2name=BusinessDocumentId'
1302 || '&' || 'pk3name=BusinessDocumentVersion';
1303
1304
1305 l_workflow_string := l_workflow_string ||'&'|| 'pk1value=' || p_doc_type;
1306 l_workflow_string := l_workflow_string ||'&'|| 'pk2value=' || p_doc_id;
1307 -----l_workflow_string := l_workflow_string ||'&'|| 'pk3value=' || l_doc_version_number;
1308 l_workflow_string := l_workflow_string ||'&'|| 'pk3value=' || G_CURRENT_VERSION_NUMBER; --i.e. -99
1309 l_workflow_string := l_workflow_string ||'&'|| 'categories=OKC_REPO_CONTRACT,OKC_REPO_APP_ABSTRACT';
1310
1311 ELSE
1312 --NO terms and conditions exist for the BSA or Sales Order so return OM attachment string to workflow request
1313 --Attachment entities for blanket agreements and sales orders are the same.
1314 l_workflow_string := 'FND:entity=OE_ORDER_HEADERS' ||'&' || 'pk1name=HEADER_ID';
1315 l_workflow_string := l_workflow_string || '&' || 'pk1value=' || p_doc_id;
1316 l_workflow_string := l_workflow_string ||'&'|| 'categories=OE_PRINT_CATEGORY';
1317 END IF;
1318
1319 IF l_debug_level > 0 THEN
1320 oe_debug_pub.add('l_workflow_string: '|| l_workflow_string, 3);
1321 oe_debug_pub.add('End of OE_Contracts_util.attachment_location, x_return_status ' || x_return_status, 1);
1322 END IF;
1323
1324 x_workflow_string := l_workflow_string;
1325
1326
1327 EXCEPTION
1328 WHEN FND_API.G_EXC_ERROR THEN
1329
1330 IF l_debug_level > 0 THEN
1331 oe_debug_pub.add('WHEN OTHERS in attachment_location ', 3);
1332 END IF;
1333
1334 IF c_attachment_exist_check%ISOPEN THEN
1335 CLOSE c_attachment_exist_check;
1336 END IF;
1337
1338 /********
1339 IF c_get_bsa_version%ISOPEN THEN
1340 CLOSE c_get_bsa_version;
1341 END IF;
1342
1343 IF c_get_so_version%ISOPEN THEN
1344 CLOSE c_get_so_version;
1345 END IF;
1346 ********/
1347
1348
1349 x_return_status := FND_API.G_RET_STS_ERROR;
1350
1351 /*****
1352 not needed as per meeting
1353 --transfer error messages on OKC stack to OM stack
1354 OE_MSG_PUB.Transfer_Msg_Stack;
1355 *****/
1356
1357 --Get message count and data
1358 OE_MSG_PUB.Count_And_Get (
1359 p_count => x_msg_count,
1360 p_data => x_msg_data
1361 );
1362
1363
1364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1365
1366 IF l_debug_level > 0 THEN
1367 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in attachment_location ', 3);
1368 END IF;
1369
1370 IF c_attachment_exist_check%ISOPEN THEN
1371 CLOSE c_attachment_exist_check;
1372 END IF;
1373
1374 /********
1375 IF c_get_bsa_version%ISOPEN THEN
1376 CLOSE c_get_bsa_version;
1377 END IF;
1378
1379 IF c_get_so_version%ISOPEN THEN
1380 CLOSE c_get_so_version;
1381 END IF;
1382 ********/
1383
1384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1385
1386 /*****
1387 not needed as per meeting
1388 --transfer error messages on OKC stack to OM stack
1389 OE_MSG_PUB.Transfer_Msg_Stack;
1390 *****/
1391
1392 --Get message count and data
1393 OE_MSG_PUB.Count_And_Get (
1394 p_count => x_msg_count,
1395 p_data => x_msg_data
1396 );
1397
1398
1399 WHEN OTHERS THEN
1400
1401 IF l_debug_level > 0 THEN
1402 oe_debug_pub.add('WHEN OTHERS in attachment_location ', 3);
1403 END IF;
1404
1405 IF c_attachment_exist_check%ISOPEN THEN
1406 CLOSE c_attachment_exist_check;
1407 END IF;
1408
1409 /********
1410 IF c_get_bsa_version%ISOPEN THEN
1411 CLOSE c_get_bsa_version;
1412 END IF;
1413
1414 IF c_get_so_version%ISOPEN THEN
1415 CLOSE c_get_so_version;
1416 END IF;
1417 ********/
1418
1419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1420
1421 /*****
1422 not needed as per meeting
1423 --transfer error messages on OKC stack to OM stack
1424 OE_MSG_PUB.Transfer_Msg_Stack;
1425 *****/
1426
1427 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1428 OE_MSG_PUB.Add_Exc_Msg (
1429 G_PKG_NAME,
1430 'get_terms_template'
1431 );
1432 END IF;
1433
1434
1435 --Get message count and data
1436 OE_MSG_PUB.Count_And_Get (
1437 p_count => x_msg_count,
1438 p_data => x_msg_data
1439 );
1440
1441
1442
1443
1444 END attachment_location;
1445
1446
1447
1448 /* Check if Blanket or Sales Order has any terms and conditions instantiated against it i.e. if
1449 an article template exists for the Blanket or Sales Order or not.
1450 This just translates the output of the already existing procedure 'get_terms_template'
1451 into a 'Y' or 'N' */
1452 -- needed and requested by the preview print application
1453 FUNCTION terms_exists (
1454 p_doc_type IN VARCHAR2,
1455 p_doc_id IN NUMBER
1456 )
1457 RETURN VARCHAR2 IS
1458
1459 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1460 l_result VARCHAR2(1) := 'N';
1461
1462 /* l_return_status VARCHAR2(1);
1463 l_msg_count NUMBER;
1464 l_msg_data VARCHAR2(2000);
1465
1466 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1467 l_template_id NUMBER;
1468 l_template_name VARCHAR2(500);
1469 */
1470 BEGIN
1471
1472 IF l_debug_level > 0 THEN
1473 oe_debug_pub.add('In OE_Contracts_util.terms_exists ', 1);
1474 END IF;
1475
1476 --Check for licensing
1477 IF OE_Contracts_util.check_license() <> 'Y' THEN
1478 IF l_debug_level > 0 THEN
1479 oe_debug_pub.add('Contracts not licensed, exiting terms_exists', 3);
1480 END IF;
1481 --RETURN NULL;
1482 RETURN 'N';
1483 END IF;
1484
1485
1486 IF l_debug_level > 0 THEN
1487 oe_debug_pub.add('Calling OE_Contracts_util.has_terms ', 3);
1488 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
1489 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
1490 END IF;
1491
1492 --first determine whether any terms and conditions have been instantiated for the BSA or sales order
1493 /* oe_contracts_util.get_terms_template (
1494 p_api_version => 1.0,
1495
1496 p_doc_type => p_doc_type,
1497 p_doc_id => p_doc_id,
1498
1499 x_template_id => l_template_id,
1500 x_template_name => l_template_name,
1501 x_return_status => l_return_status,
1502 x_msg_count => l_msg_count,
1503 x_msg_data => l_msg_data
1504 );
1505
1506
1507 IF l_debug_level > 0 THEN
1508 oe_debug_pub.add('l_template_id: '|| l_template_id, 3);
1509 oe_debug_pub.add('l_template_name: '|| l_template_name, 3);
1510 oe_debug_pub.add('l_return_status: '|| l_return_status, 3);
1511 END IF;
1512
1513
1514 IF l_template_id IS NOT NULL THEN
1515 IF l_debug_level > 0 THEN
1516 oe_debug_pub.add('End of OE_Contracts_util.terms_exists , returning Y');
1517 END IF;
1518 RETURN ('Y');
1519 ELSE
1520 IF l_debug_level > 0 THEN
1521 oe_debug_pub.add('End of OE_Contracts_util.terms_exists , returning Y');
1522 END IF;
1523 RETURN ('N');
1524 END IF;*/
1525
1526 -- check if terms exist
1527
1528 l_result := OKC_TERMS_UTIL_GRP.HAS_TERMS (
1529 p_document_type => p_doc_type,
1530 p_document_id => p_doc_id
1531 );
1532
1533 IF l_debug_level > 0 THEN
1534 oe_debug_pub.add('End of OE_Contracts_util.terms_exists result is:'||l_result );
1535 END IF;
1536
1537 RETURN l_result;
1538
1539 EXCEPTION
1540
1541
1542 WHEN OTHERS THEN
1543
1544 IF l_debug_level > 0 THEN
1545 oe_debug_pub.add('WHEN-OTHERS in terms_exists', 1);
1546 END IF;
1547
1548 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1549 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1550 'terms_exists'
1551 );
1552 END IF;
1553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1554
1555
1556 END terms_exists;
1557
1558
1559
1560 --delete articles belonging to the BSA or Sales Order
1561 PROCEDURE delete_articles
1562 (
1563 p_api_version IN NUMBER,
1564 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1565 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1566
1567 p_doc_type IN VARCHAR2,
1568 p_doc_id IN NUMBER,
1569
1570 x_return_status OUT NOCOPY VARCHAR2,
1571 x_msg_count OUT NOCOPY NUMBER,
1572 x_msg_data OUT NOCOPY VARCHAR2
1573 ) IS
1574
1575 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1576
1577 BEGIN
1578
1579 IF l_debug_level > 0 THEN
1580 oe_debug_pub.add('In OE_Contracts_util.delete_articles ', 1);
1581 END IF;
1582
1583 x_return_status := FND_API.G_RET_STS_SUCCESS;
1584
1585 IF p_init_msg_list = FND_API.G_TRUE THEN
1586 oe_msg_pub.initialize;
1587 END IF;
1588
1589 --Check for licensing
1590 IF OE_Contracts_util.check_license() <> 'Y' THEN
1591 IF l_debug_level > 0 THEN
1592 oe_debug_pub.add('Contractual option not licensed, hence exiting delete_articles ', 3);
1593 END IF;
1594 RETURN;
1595 END IF;
1596
1597
1598
1599 IF l_debug_level > 0 THEN
1600 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.delete_doc ', 3);
1601 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
1602 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
1603 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
1604 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
1605 oe_debug_pub.add('p_commit: ' || p_commit,3);
1606 END IF;
1607
1608 OKC_TERMS_UTIL_GRP.delete_doc (
1609 p_api_version => p_api_version,
1610 p_init_msg_list => p_init_msg_list,
1611 p_commit => p_commit,
1612 p_doc_type => p_doc_type,
1613 p_doc_id => p_doc_id,
1614 x_return_status => x_return_status,
1615 x_msg_data => x_msg_data,
1616 x_msg_count => x_msg_count
1617 );
1618
1619 IF l_debug_level > 0 THEN
1620 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
1621 END IF;
1622
1623 --ETR
1624 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1626 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1627 RAISE FND_API.G_EXC_ERROR ;
1628 END IF;
1629 --ETR
1630
1631 IF l_debug_level > 0 THEN
1632 oe_debug_pub.add('End of OE_Contracts_util.delete_articles, x_return_status ' || x_return_status, 1);
1633 END IF;
1634
1635 EXCEPTION
1636 WHEN FND_API.G_EXC_ERROR THEN
1637
1638 IF l_debug_level > 0 THEN
1639 oe_debug_pub.add('WHEN G_EXC_ERROR in delete_articles ', 3);
1640 END IF;
1641
1642 --close any cursors
1643
1644 x_return_status := FND_API.G_RET_STS_ERROR;
1645
1646 /*****
1647 not needed as per meeting
1648 --transfer error messages on OKC stack to OM stack
1649 OE_MSG_PUB.Transfer_Msg_Stack;
1650 *****/
1651
1652 --Get message count and data
1653 OE_MSG_PUB.Count_And_Get (
1654 p_count => x_msg_count,
1655 p_data => x_msg_data
1656 );
1657
1658
1659 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1660
1661 IF l_debug_level > 0 THEN
1662 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in delete_articles ', 3);
1663 END IF;
1664
1665 --close any cursors
1666
1667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1668
1669 /*****
1670 not needed as per meeting
1671 --transfer error messages on OKC stack to OM stack
1672 OE_MSG_PUB.Transfer_Msg_Stack;
1673 *****/
1674
1675 --Get message count and data
1676 OE_MSG_PUB.Count_And_Get (
1677 p_count => x_msg_count,
1678 p_data => x_msg_data
1679 );
1680
1681
1682 WHEN OTHERS THEN
1683
1684 IF l_debug_level > 0 THEN
1685 oe_debug_pub.add('WHEN OTHERS in delete_articles ', 3);
1686 END IF;
1687
1688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1689
1690 /*****
1691 not needed as per meeting
1692 --transfer error messages on OKC stack to OM stack
1693 OE_MSG_PUB.Transfer_Msg_Stack;
1694 *****/
1695
1696 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1697 OE_MSG_PUB.Add_Exc_Msg (
1698 G_PKG_NAME,
1699 'delete_articles'
1700 );
1701 END IF;
1702
1703 --Get message count and data
1704 OE_MSG_PUB.Count_And_Get (
1705 p_count => x_msg_count,
1706 p_data => x_msg_data
1707 );
1708
1709 END delete_articles;
1710
1711
1712
1713 --purge articles belonging to the BSA's or Sales Orders
1714 PROCEDURE purge_articles
1715 (
1716 p_api_version IN NUMBER,
1717 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1718 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1719
1720 p_doc_tbl IN doc_tbl_type,
1721
1722 x_return_status OUT NOCOPY VARCHAR2,
1723 x_msg_count OUT NOCOPY NUMBER,
1724 x_msg_data OUT NOCOPY VARCHAR2
1725 ) IS
1726
1727 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1728
1729 BEGIN
1730
1731 IF l_debug_level > 0 THEN
1732 oe_debug_pub.add('In OE_Contracts_util.purge_articles ', 1);
1733 END IF;
1734
1735 x_return_status := FND_API.G_RET_STS_SUCCESS;
1736
1737 IF p_init_msg_list = FND_API.G_TRUE THEN
1738 oe_msg_pub.initialize;
1739 END IF;
1740
1741 --Check for licensing
1742 IF OE_Contracts_util.check_license() <> 'Y' THEN
1743 IF l_debug_level > 0 THEN
1744 oe_debug_pub.add('Contractual option not licensed, hence exiting purge_articles ', 3);
1745 END IF;
1746 RETURN;
1747 END IF;
1748
1749
1750
1751 IF l_debug_level > 0 THEN
1752 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.purge_articles ', 3);
1753 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
1754 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
1755 oe_debug_pub.add('p_commit: ' || p_commit,3);
1756 END IF;
1757
1758 OKC_TERMS_UTIL_GRP.purge_doc (
1759 p_api_version => p_api_version,
1760 p_init_msg_list => p_init_msg_list,
1761 p_commit => p_commit,
1762 p_doc_tbl => p_doc_tbl,
1763 x_return_status => x_return_status,
1764 x_msg_data => x_msg_data,
1765 x_msg_count => x_msg_count
1766 );
1767
1768 IF l_debug_level > 0 THEN
1769 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
1770 END IF;
1771
1772 --ETR
1773 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1775 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1776 RAISE FND_API.G_EXC_ERROR ;
1777 END IF;
1778 --ETR
1779
1780 IF l_debug_level > 0 THEN
1781 oe_debug_pub.add('End of OE_Contracts_util.purge_articles, x_return_status ' || x_return_status, 1);
1782 END IF;
1783
1784 EXCEPTION
1785 WHEN FND_API.G_EXC_ERROR THEN
1786
1787 IF l_debug_level > 0 THEN
1788 oe_debug_pub.add('WHEN G_EXC_ERROR in purge_articles ', 3);
1789 END IF;
1790
1791 --close any cursors
1792
1793 x_return_status := FND_API.G_RET_STS_ERROR;
1794
1795 /*****
1796 not needed as per meeting
1797 --transfer error messages on OKC stack to OM stack
1798 OE_MSG_PUB.Transfer_Msg_Stack;
1799 *****/
1800
1801 --Get message count and data
1802 OE_MSG_PUB.Count_And_Get (
1803 p_count => x_msg_count,
1804 p_data => x_msg_data
1805 );
1806
1807
1808 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1809
1810 IF l_debug_level > 0 THEN
1811 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in purge_articles ', 3);
1812 END IF;
1813
1814 --close any cursors
1815
1816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1817
1818 /*****
1819 not needed as per meeting
1820 --transfer error messages on OKC stack to OM stack
1821 OE_MSG_PUB.Transfer_Msg_Stack;
1822 *****/
1823
1824 --Get message count and data
1825 OE_MSG_PUB.Count_And_Get (
1826 p_count => x_msg_count,
1827 p_data => x_msg_data
1828 );
1829
1830
1831 WHEN OTHERS THEN
1832
1833 IF l_debug_level > 0 THEN
1834 oe_debug_pub.add('WHEN OTHERS in purge_articles ', 3);
1835 END IF;
1836
1837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1838
1839 /*****
1840 not needed as per meeting
1841 --transfer error messages on OKC stack to OM stack
1842 OE_MSG_PUB.Transfer_Msg_Stack;
1843 *****/
1844
1845 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1846 OE_MSG_PUB.Add_Exc_Msg (
1847 G_PKG_NAME,
1848 'purge_articles'
1849 );
1850 END IF;
1851
1852 --Get message count and data
1853 OE_MSG_PUB.Count_And_Get (
1854 p_count => x_msg_count,
1855 p_data => x_msg_data
1856 );
1857
1858 END purge_articles;
1859
1860
1861
1862 PROCEDURE get_article_variable_values
1863 (
1864 p_api_version IN NUMBER,
1865 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1866 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1867
1868 p_doc_type IN VARCHAR2,
1869 p_doc_id IN NUMBER,
1870 p_sys_var_value_tbl IN OUT NOCOPY sys_var_value_tbl_type,
1871
1872 x_return_status OUT NOCOPY VARCHAR2,
1873 x_msg_count OUT NOCOPY NUMBER,
1874 x_msg_data OUT NOCOPY VARCHAR2
1875 )
1876
1877
1878 IS
1879
1880
1881
1882 /** note: in cursors based on table OE_BLANKET_HEADERS_ALL, we select on the basis
1883 of HEADER_ID only as it is unique and an index is based on that, we don't need SALES_DOCUMENT_TYPE_CODE **/
1884
1885 --cursor to fetch value of header level variables such as OKC$S_BLANKET_NUMBER etc. for blankets
1886 CURSOR c_get_bsa_header_variables IS
1887 SELECT bh.order_number,
1888 bh.agreement_id,
1889 bh.sold_to_org_id,
1890 bh.order_type_id,
1891 bh.cust_po_number,
1892 bh.version_number,
1893 bh.sold_to_contact_id,
1894 bh.salesrep_id,
1895 bh.transactional_curr_code,
1896 bhe.start_date_active,
1897 bhe.end_date_active,
1898 bh.freight_terms_code,
1899 bh.shipping_method_code,
1900 bh.payment_term_id,
1901 bh.invoicing_rule_id,
1902 bhe.blanket_min_amount,
1903 bhe.blanket_max_amount,
1904 bh.org_id
1905 FROM oe_blanket_headers_all bh,
1906 oe_blanket_headers_ext bhe
1907 WHERE bh.header_id = p_doc_id
1908 AND bh.order_number = bhe.order_number;
1909
1910
1911 --cursor to fetch value of header level variables such as OKC$S_ORDER_NUMBER etc. for sales orders
1912 CURSOR c_get_so_header_variables IS
1913 SELECT oh.order_number,
1914 oh.blanket_number,
1915 oh.agreement_id,
1916 oh.quote_number,
1917 oh.sold_to_org_id,
1918 oh.cust_po_number,
1919 oh.version_number,
1920 oh.sold_to_contact_id,
1921 oh.salesrep_id,
1922 oh.transactional_curr_code,
1923 oh.freight_terms_code,
1924 oh.shipping_method_code,
1925 oh.payment_term_id,
1926 oh.invoicing_rule_id,
1927 oh.org_id
1928 FROM oe_order_headers_all oh
1929
1930 WHERE oh.header_id = p_doc_id;
1931
1932
1933
1934
1935 l_bsa_header_variables c_get_bsa_header_variables%ROWTYPE;
1936 l_so_header_variables c_get_so_header_variables%ROWTYPE;
1937 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1938
1939 BEGIN
1940
1941 IF l_debug_level > 0 THEN
1942 oe_debug_pub.add('In OE_Contracts_util.get_article_variable_values for header level variables', 1);
1943 oe_debug_pub.add('p_doc_type: ' || p_doc_type,3);
1944 oe_debug_pub.add('p_doc_id: ' || p_doc_id,3);
1945 END IF;
1946
1947 x_return_status := FND_API.G_RET_STS_SUCCESS;
1948
1949 IF p_init_msg_list = FND_API.G_TRUE THEN
1950 oe_msg_pub.initialize;
1951 END IF;
1952
1953
1954 -- For articles QA: -
1955 -- Query OM tables OE_BLANKET_HEADERS_ALL and OE_BLANKET_HEADERS_EXT to retrieve values against variable codes
1956 -- sent in by calling articles QA API.
1957
1958 ----IF p_sys_var_value_tbl.COUNT > 0 THEN
1959 IF p_sys_var_value_tbl.FIRST IS NOT NULL THEN
1960
1961 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
1962
1963 IF c_get_bsa_header_variables%ISOPEN THEN
1964 CLOSE c_get_bsa_header_variables;
1965 END IF;
1966
1967 OPEN c_get_bsa_header_variables;
1968 FETCH c_get_bsa_header_variables INTO l_bsa_header_variables;
1969 IF l_debug_level > 0 THEN
1970 oe_debug_pub.add('c_get_bsa_header_variables%ROWCOUNT: ' || c_get_bsa_header_variables%ROWCOUNT, 3);
1971 END IF;
1972 CLOSE c_get_bsa_header_variables;
1973
1974 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
1975
1976 IF c_get_bsa_header_variables%ISOPEN THEN
1977 CLOSE c_get_so_header_variables;
1978 END IF;
1979
1980 OPEN c_get_so_header_variables;
1981 FETCH c_get_so_header_variables INTO l_so_header_variables;
1982 IF l_debug_level > 0 THEN
1983 oe_debug_pub.add('c_get_so_header_variables%ROWCOUNT: ' || c_get_so_header_variables%ROWCOUNT, 3);
1984 END IF;
1985 CLOSE c_get_so_header_variables;
1986
1987 END IF;
1988
1989
1990
1991 -----------------------------------------------------------------------------------------------
1992 FOR i IN p_sys_var_value_tbl.FIRST..p_sys_var_value_tbl.LAST LOOP
1993
1994
1995 IF p_sys_var_value_tbl(i).variable_code = 'OKC$S_ORDER_NUMBER' THEN
1996 BEGIN
1997 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.order_number;
1998 END;
1999
2000 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_BLANKET_NUMBER' THEN
2001 BEGIN
2002 IF p_doc_type = OE_CONTRACTS_UTIL.get_G_BSA_DOC_TYPE() THEN
2003 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.order_number;
2004 ELSIF p_doc_type = OE_CONTRACTS_UTIL.get_G_SO_DOC_TYPE() THEN
2005 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.blanket_number;
2006 END IF;
2007 END;
2008
2009 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_PA_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.agreement_id;
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.agreement_id;
2015 END IF;
2016 END;
2017
2018 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_PA_NAME' 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_QUOTE_NUMBER' THEN
2028 BEGIN
2029 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.quote_number;
2030 END;
2031
2032 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CUSTOMER_NAME' THEN
2033 BEGIN
2034 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2035 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.sold_to_org_id;
2036 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2037 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.sold_to_org_id;
2038 END IF;
2039 END;
2040
2041 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CUSTOMER_NUMBER' 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_BLANKET_AGREEMENT_TYPE' THEN
2051 BEGIN
2052 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.order_type_id;
2053 END;
2054
2055 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CUST_PO_NUMBER' THEN
2056 BEGIN
2057 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2058 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.cust_po_number;
2059 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2060 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.cust_po_number;
2061 END IF;
2062 END;
2063
2064 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_VERSION_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.version_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.version_number;
2070 END IF;
2071 END;
2072
2073 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CUST_CONTACT_NAME' 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.sold_to_contact_id;
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.sold_to_contact_id;
2079 END IF;
2080 END;
2081
2082 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_SALESREP_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.salesrep_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.salesrep_id;
2088 END IF;
2089 END;
2090
2091 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CURRENCY_CODE' 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.transactional_curr_code;
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.transactional_curr_code;
2097 END IF;
2098 END;
2099
2100 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_CURRENCY_NAME' 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_SYMBOL' 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_ACTIVATION_DATE' THEN
2119 BEGIN
2120 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.start_date_active;
2121 END;
2122
2123 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_EXPIRATION_DATE' THEN
2124 BEGIN
2125 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.end_date_active;
2126 END;
2127
2128 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_FREIGHT_TERMS' THEN
2129 BEGIN
2130 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2131 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.freight_terms_code;
2132 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2133 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.freight_terms_code;
2134 END IF;
2135 END;
2136
2137 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_SHIPPING_METHOD' 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.shipping_method_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.shipping_method_code;
2143 END IF;
2144 END;
2145
2146 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_PAYMENT_TERM' 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.payment_term_id;
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.payment_term_id;
2152 END IF;
2153 END;
2154
2155 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_INVOICING_RULE' 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.invoicing_rule_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.invoicing_rule_id;
2161 END IF;
2162 END;
2163
2164 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_MIN_AMOUNT_AGREED' THEN
2165 BEGIN
2166 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.blanket_min_amount;
2167 END;
2168
2169 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_MAX_AMOUNT_AGREED' THEN
2170 BEGIN
2171 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.blanket_max_amount;
2172 END;
2173
2174 ELSIF p_sys_var_value_tbl(i).variable_code = 'OKC$S_SUPPLIER_NAME' THEN
2175 BEGIN
2176 IF p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE THEN
2177 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.org_id;
2178 ELSIF p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE THEN
2179 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.org_id;
2180 END IF;
2181 END;
2182
2183 ELSE NULL;
2184 END IF;
2185
2186 IF l_debug_level > 0 THEN
2187 oe_debug_pub.add(p_sys_var_value_tbl(i).variable_code || ': ' || p_sys_var_value_tbl(i).variable_value_id, 3);
2188 END IF;
2189
2190
2191 END LOOP;
2192 -----------------------------------------------------------------------------------------------
2193
2194 END IF;
2195
2196 IF l_debug_level > 0 THEN
2197 oe_debug_pub.add('End of OE_Contracts_util.get_article_variable_values for header level variables, x_return_status ' || x_return_status, 1);
2198 END IF;
2199
2200
2201 EXCEPTION
2202 WHEN FND_API.G_EXC_ERROR THEN
2203
2204 IF l_debug_level > 0 THEN
2205 oe_debug_pub.add('WHEN G_EXC_ERROR in get_article_variable_values ', 3);
2206 END IF;
2207
2208 --close any cursors
2209 IF c_get_bsa_header_variables%ISOPEN THEN
2210 CLOSE c_get_bsa_header_variables;
2211 END IF;
2212
2213 x_return_status := FND_API.G_RET_STS_ERROR;
2214
2215 /*****
2216 not needed as per meeting
2217 --transfer error messages on OKC stack to OM stack
2218 OE_MSG_PUB.Transfer_Msg_Stack;
2219 *****/
2220
2221 --Get message count and data
2222 OE_MSG_PUB.Count_And_Get (
2223 p_count => x_msg_count,
2224 p_data => x_msg_data
2225 );
2226
2227
2228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2229
2230 IF l_debug_level > 0 THEN
2231 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_article_variable_values ', 3);
2232 END IF;
2233
2234 --close any cursors
2235 IF c_get_bsa_header_variables%ISOPEN THEN
2236 CLOSE c_get_bsa_header_variables;
2237 END IF;
2238
2239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2240
2241 /*****
2242 not needed as per meeting
2243 --transfer error messages on OKC stack to OM stack
2244 OE_MSG_PUB.Transfer_Msg_Stack;
2245 *****/
2246
2247 --Get message count and data
2248 OE_MSG_PUB.Count_And_Get (
2249 p_count => x_msg_count,
2250 p_data => x_msg_data
2251 );
2252
2253
2254 WHEN OTHERS THEN
2255
2256 IF l_debug_level > 0 THEN
2257 oe_debug_pub.add('WHEN OTHERS in get_article_variable_values ', 3);
2258 END IF;
2259
2260 IF c_get_bsa_header_variables%ISOPEN THEN
2261 CLOSE c_get_bsa_header_variables;
2262 END IF;
2263
2264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2265
2266 /*****
2267 not needed as per meeting
2268 --transfer error messages on OKC stack to OM stack
2269 OE_MSG_PUB.Transfer_Msg_Stack;
2270 *****/
2271
2272 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2273 OE_MSG_PUB.Add_Exc_Msg (
2274 G_PKG_NAME,
2275 'get_article_variable_values'
2276 );
2277 END IF;
2278
2279
2280 --Get message count and data
2281 OE_MSG_PUB.Count_And_Get (
2282 p_count => x_msg_count,
2283 p_data => x_msg_data
2284 );
2285
2286 END get_article_variable_values;
2287
2288
2289
2290
2291 --this overloaded signature is called from the contract expert
2292 PROCEDURE get_article_variable_values
2293 (
2294 p_api_version IN NUMBER,
2295 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2296 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2297
2298 p_doc_type IN VARCHAR2,
2299 p_doc_id IN NUMBER,
2300 p_line_var_tbl IN line_var_tbl_type,
2301
2302 x_line_var_value_tbl OUT NOCOPY sys_var_value_tbl_type,
2303 x_return_status OUT NOCOPY VARCHAR2,
2304 x_msg_count OUT NOCOPY NUMBER,
2305 x_msg_data OUT NOCOPY VARCHAR2
2306 ) IS
2307
2308 --cursor to get all the items of the BSA i.e. internal (INT) customer (CUST) etc.
2309 --returns non-translatable code eg. AS54888
2310 CURSOR c_get_items IS
2311 SELECT item_identifier_type, --eg. INT
2312 ordered_item, --eg. AS54888
2313 ordered_item_id,
2314 org_id,
2315 inventory_item_id,
2316 sold_to_org_id
2317 FROM oe_blanket_lines_all
2318 WHERE header_id = p_doc_id
2319 AND p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE
2320 AND item_identifier_type <> 'CAT'
2321
2322 UNION ALL
2323
2324 --cursor to get all the items of the Sales Order i.e. internal (INT) customer (CUST) etc.
2325 --returns non-translatable code eg. AS54888
2326 SELECT item_identifier_type, --eg. INT
2327 ordered_item, --eg. AS54888
2328 ordered_item_id,
2329 org_id,
2330 inventory_item_id,
2331 sold_to_org_id
2332 FROM oe_order_lines_all
2333 WHERE header_id = p_doc_id
2334 AND p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE
2335 AND item_identifier_type <> 'CAT'
2336 ORDER BY ordered_item;
2337
2338
2339
2340
2341 --cursor to retrieve the item categories (CATs) in the BSA
2342 --returns non-translatable code eg. 208.05
2343 CURSOR c_get_item_categories IS
2344 SELECT ordered_item
2345 FROM oe_blanket_lines_all
2346 WHERE header_id = p_doc_id
2347 AND p_doc_type = OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE
2348 AND item_identifier_type = 'CAT'
2349
2350 UNION ALL
2351
2352 --cursor to retrieve the item categories (CATs) in the Sales Order
2353 --returns non-translatable code eg. 208.05
2354 SELECT ordered_item
2355 FROM oe_order_lines_all
2356 WHERE header_id = p_doc_id
2357 AND p_doc_type = OE_CONTRACTS_UTIL.G_SO_DOC_TYPE
2358 AND item_identifier_type = 'CAT'
2359 ORDER BY ordered_item;
2360
2361
2362
2363
2364 -- cursor to retrieve categories to which the INT (internal) and non-INT items in the BSA or Sales Order belong
2365 /** Note: the inventory_item_id stored in OE_BLANKET_LINES_ALL OE_ORDER_LINES_ALL against the non-INT item is
2366 that of the mapped INT item so we can use it directly to get the item category **/
2367 -- returns non-translatable code eg. HOSPITAL.MISC
2368 CURSOR c_get_derived_item_category (cp_org_id NUMBER,
2369 cp_inventory_item_id NUMBER) IS
2370 SELECT category_concat_segs
2371 FROM mtl_item_categories_v
2372 WHERE inventory_item_id = cp_inventory_item_id
2373 AND organization_id = cp_org_id -- should be inventory master org
2374 AND structure_id = 101; -- hardcoded to 101 i.e. Item Categories (Inv. Items) for Order Management
2375
2376
2377 l_bsa_derived_item_category c_get_derived_item_category%ROWTYPE;
2378
2379 j BINARY_INTEGER := 1;
2380 l_master_org_id NUMBER;
2381 lx_ordered_item VARCHAR2(2000);
2382 lx_inventory_item VARCHAR2(2000);
2383 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2384
2385 BEGIN
2386
2387 IF l_debug_level > 0 THEN
2388 oe_debug_pub.add('In OE_Contracts_util.get_article_variable_values for line level variables ', 1);
2389 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 1);
2390 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 1);
2391 oe_debug_pub.add('p_line_var_tbl.COUNT: ' || p_line_var_tbl.COUNT, 1);
2392 END IF;
2393
2394
2395 x_return_status := FND_API.G_RET_STS_SUCCESS;
2396
2397 IF p_init_msg_list = FND_API.G_TRUE THEN
2398 oe_msg_pub.initialize;
2399 END IF;
2400
2401
2402 -- For articles wizard expert: -
2403 -- Query OM tables OE_BLANKET_HEADERS_ALL and OE_BLANKET_LINES_ALL to retrieve values against variable codes sent
2404 -- in by calling articles wizard expert API
2405
2406 IF p_line_var_tbl.FIRST IS NOT NULL THEN
2407 FOR i IN p_line_var_tbl.FIRST..p_line_var_tbl.LAST LOOP
2408
2409 IF l_debug_level > 0 THEN
2410 oe_debug_pub.add('Processing for ' || p_line_var_tbl(i), 3);
2411 END IF;
2412
2413 IF p_line_var_tbl(i) = 'OKC$S_ITEMS' THEN
2414
2415 FOR c_get_items_rec IN c_get_items LOOP
2416 --loop thru all the items for internal INT items
2417 IF l_debug_level > 0 THEN
2418 oe_debug_pub.add('c_get_items_rec.item_identifier_type: '||c_get_items_rec.item_identifier_type, 3);
2419 END IF;
2420
2421 IF c_get_items_rec.item_identifier_type = 'INT' THEN
2422 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_INTERNAL_ITEM
2423 x_line_var_value_tbl(j).variable_value_id := c_get_items_rec.ordered_item; --eg. AS54888
2424
2425 ELSIF c_get_items_rec.item_identifier_type <> 'INT' THEN
2426 --map the non-INT items to INT items
2427
2428 --get inventory master org
2429 /******************************************************/
2430 -- FOR TESTING ONLY, REMOVE WHEN DONE! THIS CONTEXT WILL AUTOMATICALLY BE SET IN FORMS
2431 -- dbms_application_info.set_client_info('204');
2432 /******************************************************/
2433 l_master_org_id := TO_NUMBER(oe_sys_parameters.value (
2434 param_name => 'MASTER_ORGANIZATION_ID'
2435 ));
2436
2437 IF l_debug_level > 0 THEN
2438 oe_debug_pub.add('l_master_org_id: ' || l_master_org_id, 3);
2439 oe_debug_pub.add('mapping non-INT item to INT item, Calling OE_Id_To_Value.Ordered_Item ', 3);
2440 END IF;
2441
2442 --map non-INT item to INT item
2443 OE_Id_To_Value.Ordered_Item (
2444 p_item_identifier_type => c_get_items_rec.item_identifier_type,
2445 p_inventory_item_id => c_get_items_rec.inventory_item_id,
2446 p_organization_id => l_master_org_id,
2447 p_ordered_item_id => c_get_items_rec.ordered_item_id,
2448 p_sold_to_org_id => c_get_items_rec.sold_to_org_id,
2449 p_ordered_item => c_get_items_rec.ordered_item,
2450 x_ordered_item => lx_ordered_item,
2451 x_inventory_item => lx_inventory_item
2452 );
2453
2454 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_INTERNAL_ITEM
2455 x_line_var_value_tbl(j).variable_value_id := lx_inventory_item;
2456
2457 IF l_debug_level > 0 THEN
2458 oe_debug_pub.add('lx_inventory_item: ' || lx_inventory_item, 3);
2459 oe_debug_pub.add('x_line_var_value_tbl(j).variable_code: ' || x_line_var_value_tbl(j).variable_code,3);
2460 oe_debug_pub.add('x_line_var_value_tbl(j).variable_value_id: ' || x_line_var_value_tbl(j).variable_value_id,3);
2461 END IF;
2462
2463
2464 END IF;
2465
2466 j := j + 1;
2467
2468 END LOOP;
2469
2470
2471
2472
2473 ELSIF p_line_var_tbl(i) = 'OKC$S_ITEM_CATEGORIES' THEN
2474
2475
2476 --get all the item categories in the BSA
2477 FOR c_get_item_categories_rec IN c_get_item_categories LOOP
2478
2479 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_ITEM_CATEGORIES
2480 x_line_var_value_tbl(j).variable_value_id := c_get_item_categories_rec.ordered_item;
2481
2482 IF l_debug_level > 0 THEN
2483 oe_debug_pub.add('x_line_var_value_tbl(j).variable_code: ' || x_line_var_value_tbl(j).variable_code,3);
2484 oe_debug_pub.add('x_line_var_value_tbl(j).variable_value_id: ' || x_line_var_value_tbl(j).variable_value_id,3);
2485 END IF;
2486
2487 j := j + 1;
2488
2489 END LOOP;
2490
2491 --get the item categories to which the INT and non-INT items in the BSA belong to
2492 /** note: the inventory_item_id stored in oe_blanket_lines_all against the non-INT items is actually that of the mapped INT
2493 item so we can use it directly to get the item category **/
2494 FOR c_get_items_rec IN c_get_items LOOP
2495
2496 --get inventory master org
2497 /******************************************************/
2498 -- FOR TESTING ONLY, REMOVE WHEN DONE!!!!! THIS CONTEXT WILL AUTOMATICALLY BE SET IN FORMS
2499 --dbms_application_info.set_client_info('204');
2500 /******************************************************/
2501 l_master_org_id := TO_NUMBER(oe_sys_parameters.value (
2502 param_name => 'MASTER_ORGANIZATION_ID'
2503 ));
2504
2505 IF l_debug_level > 0 THEN
2506 oe_debug_pub.add('l_master_org_id: ' || l_master_org_id, 3);
2507 oe_debug_pub.add('get the item categories to which the INT and non-INT items in the BSA belong to',3);
2508 END IF;
2509
2510 l_bsa_derived_item_category := null; --initialize !!!!!!! this causes NULL values: ref: Arun/Aftab issue
2511 IF c_get_derived_item_category%ISOPEN THEN
2512 CLOSE c_get_derived_item_category;
2513 END IF;
2514 OPEN c_get_derived_item_category(l_master_org_id, c_get_items_rec.inventory_item_id);
2515 FETCH c_get_derived_item_category INTO l_bsa_derived_item_category;
2516 CLOSE c_get_derived_item_category;
2517
2518 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_ITEM_CATEGORIES
2519 x_line_var_value_tbl(j).variable_value_id := l_bsa_derived_item_category.category_concat_segs;
2520
2521 IF l_debug_level > 0 THEN
2522 oe_debug_pub.add('x_line_var_value_tbl(j).variable_code: ' || x_line_var_value_tbl(j).variable_code,3);
2523 oe_debug_pub.add('x_line_var_value_tbl(j).variable_value_id: ' || x_line_var_value_tbl(j).variable_value_id,3);
2524 END IF;
2525
2526 j := j + 1;
2527
2528 END LOOP;
2529
2530 END IF;
2531
2532
2533 END LOOP;
2534 END IF; ----IF p_line_var_tbl.FIRST IS NOT NULL THEN
2535
2536
2537 IF l_debug_level > 0 THEN
2538 oe_debug_pub.add('End of OE_Contracts_util.get_article_variable_values for line level variables, x_return_status: '|| x_return_status, 1);
2539 END IF;
2540
2541 EXCEPTION
2542 WHEN FND_API.G_EXC_ERROR THEN
2543
2544 IF l_debug_level > 0 THEN
2545 oe_debug_pub.add('WHEN G_EXC_ERROR in get_article_variable_values ', 3);
2546 END IF;
2547
2548 --close any cursors
2549 IF c_get_derived_item_category%ISOPEN THEN
2550 CLOSE c_get_derived_item_category;
2551 END IF;
2552
2553 x_return_status := FND_API.G_RET_STS_ERROR;
2554
2555 /*****
2556 not needed as per meeting
2557 --transfer error messages on OKC stack to OM stack
2558 OE_MSG_PUB.Transfer_Msg_Stack;
2559 *****/
2560
2561
2562 --Get message count and data
2563 OE_MSG_PUB.Count_And_Get (
2564 p_count => x_msg_count,
2565 p_data => x_msg_data
2566 );
2567
2568
2569 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2570
2571 IF l_debug_level > 0 THEN
2572 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_article_variable_values ', 3);
2573 END IF;
2574
2575 --close any cursors
2576 IF c_get_derived_item_category%ISOPEN THEN
2577 CLOSE c_get_derived_item_category;
2578 END IF;
2579
2580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2581
2582 /*****
2583 not needed as per meeting
2584 --transfer error messages on OKC stack to OM stack
2585 OE_MSG_PUB.Transfer_Msg_Stack;
2586 *****/
2587
2588 --Get message count and data
2589 OE_MSG_PUB.Count_And_Get (
2590 p_count => x_msg_count,
2591 p_data => x_msg_data
2592 );
2593
2594
2595 WHEN OTHERS THEN
2596
2597 IF l_debug_level > 0 THEN
2598 oe_debug_pub.add('WHEN OTHERS in get_article_variable_values ', 3);
2599 END IF;
2600
2601 --close any cursors
2602 IF c_get_derived_item_category%ISOPEN THEN
2603 CLOSE c_get_derived_item_category;
2604 END IF;
2605
2606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2607
2608 /*****
2609 not needed as per meeting
2610 --transfer error messages on OKC stack to OM stack
2611 OE_MSG_PUB.Transfer_Msg_Stack;
2612 *****/
2613
2614 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2615 OE_MSG_PUB.Add_Exc_Msg (
2616 G_PKG_NAME,
2617 'get_article_variable_values'
2618 );
2619 END IF;
2620
2621
2622 --Get message count and data
2623 OE_MSG_PUB.Count_And_Get (
2624 p_count => x_msg_count,
2625 p_data => x_msg_data
2626 );
2627
2628 END get_article_variable_values;
2629
2630
2631
2632
2633
2634 --to return details about an article template being used by a particular BSA or Sales Order
2635 PROCEDURE get_terms_template
2636 (
2637 p_api_version IN NUMBER,
2638 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2639 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2640
2641 p_doc_type IN VARCHAR2,
2642 p_doc_id IN NUMBER,
2643
2644 x_template_id OUT NOCOPY NUMBER,
2645 x_template_name OUT NOCOPY VARCHAR2,
2646 x_return_status OUT NOCOPY VARCHAR2,
2647 x_msg_count OUT NOCOPY NUMBER,
2648 x_msg_data OUT NOCOPY VARCHAR2
2649 )
2650
2651 IS
2652
2653
2654 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2655
2656
2657 BEGIN
2658
2659 IF l_debug_level > 0 THEN
2660 oe_debug_pub.add('In OE_Contracts_util.get_terms_template ', 1);
2661 END IF;
2662
2663 x_return_status := FND_API.G_RET_STS_SUCCESS;
2664
2665 IF p_init_msg_list = FND_API.G_TRUE THEN
2666 oe_msg_pub.initialize;
2667 END IF;
2668
2669 --Check for licensing
2670 IF OE_Contracts_util.check_license() <> 'Y' THEN
2671 IF l_debug_level > 0 THEN
2672 oe_debug_pub.add('Contractual option not licensed, hence exiting get_terms_template ', 3);
2673 END IF;
2674 x_template_id := NULL;
2675 x_template_name := NULL;
2676 RETURN;
2677 END IF;
2678
2679
2680 IF l_debug_level > 0 THEN
2681 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.get_terms_template ', 3);
2682 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
2683 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
2684 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
2685 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
2686 END IF;
2687
2688 OKC_TERMS_UTIL_GRP.get_terms_template (
2689 p_api_version => p_api_version,
2690 p_init_msg_list => p_init_msg_list,
2691 ---p_commit => p_commit,
2692 p_doc_type => p_doc_type,
2693 p_doc_id => p_doc_id,
2694 x_template_id => x_template_id,
2695 x_template_name => x_template_name,
2696 x_return_status => x_return_status,
2697 x_msg_count => x_msg_count,
2698 x_msg_data => x_msg_data
2699 );
2700
2701 IF l_debug_level > 0 THEN
2702 oe_debug_pub.add('x_template_id: '|| x_template_id, 3);
2703 oe_debug_pub.add('x_template_name: '|| x_template_name, 3);
2704 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
2705 END IF;
2706
2707 --ETR
2708 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2710 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2711 RAISE FND_API.G_EXC_ERROR ;
2712 END IF;
2713 --ETR
2714
2715 IF l_debug_level > 0 THEN
2716 oe_debug_pub.add('End of OE_Contracts_util.get_terms_template , x_return_status ' || x_return_status, 1);
2717 END IF;
2718
2719 EXCEPTION
2720 WHEN FND_API.G_EXC_ERROR THEN
2721
2722 IF l_debug_level > 0 THEN
2723 oe_debug_pub.add('WHEN G_EXC_ERROR in get_terms_template ', 3);
2724 END IF;
2725
2726 x_return_status := FND_API.G_RET_STS_ERROR;
2727
2728 /*****
2729 not needed as per meeting
2730 --transfer error messages on OKC stack to OM stack
2731 OE_MSG_PUB.Transfer_Msg_Stack;
2732 *****/
2733
2734 --Get message count and data
2735 OE_MSG_PUB.Count_And_Get (
2736 p_count => x_msg_count,
2737 p_data => x_msg_data
2738 );
2739
2740
2741 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2742
2743 IF l_debug_level > 0 THEN
2744 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_terms_template ', 3);
2745 END IF;
2746
2747 --close any cursors
2748
2749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2750
2751 /*****
2752 not needed as per meeting
2753 --transfer error messages on OKC stack to OM stack
2754 OE_MSG_PUB.Transfer_Msg_Stack;
2755 *****/
2756
2757 --Get message count and data
2758 OE_MSG_PUB.Count_And_Get (
2759 p_count => x_msg_count,
2760 p_data => x_msg_data
2761 );
2762
2763
2764 WHEN OTHERS THEN
2765
2766 IF l_debug_level > 0 THEN
2767 oe_debug_pub.add('WHEN OTHERS in get_terms_template ', 3);
2768 END IF;
2769
2770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2771
2772 /*****
2773 not needed as per meeting
2774 --transfer error messages on OKC stack to OM stack
2775 OE_MSG_PUB.Transfer_Msg_Stack;
2776 *****/
2777
2778 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2779 OE_MSG_PUB.Add_Exc_Msg (
2780 G_PKG_NAME,
2781 'get_terms_template'
2782 );
2783 END IF;
2784
2785
2786 --Get message count and data
2787 OE_MSG_PUB.Count_And_Get (
2788 p_count => x_msg_count,
2789 p_data => x_msg_data
2790 );
2791
2792 END get_terms_template;
2793
2794
2795
2796
2797 /* Gets the name of a contract template. It does not have to be instantiated against anything. */
2798 FUNCTION Get_Template_Name(
2799 p_api_version IN NUMBER,
2800 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2801 p_template_id IN NUMBER,
2802
2803 x_return_status OUT NOCOPY VARCHAR2,
2804 x_msg_data OUT NOCOPY VARCHAR2,
2805 x_msg_count OUT NOCOPY NUMBER
2806 ) RETURN VARCHAR2 IS
2807
2808
2809 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2810 l_template_name VARCHAR2(500);
2811
2812
2813 BEGIN
2814
2815 IF l_debug_level > 0 THEN
2816 oe_debug_pub.add('In OE_Contracts_util.Get_Template_Name ', 1);
2817 oe_debug_pub.add('p_api_version: ' || p_api_version, 3);
2818 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
2819 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.Get_Template_Name',3);
2820 oe_debug_pub.add('p_tempate_id: ' || p_template_id,3);
2821 END IF;
2822
2823 x_return_status := FND_API.G_RET_STS_SUCCESS;
2824
2825 --Check for licensing
2826 IF OE_Contracts_util.check_license() <> 'Y' THEN
2827 IF l_debug_level > 0 THEN
2828 oe_debug_pub.add('Contractual option not licensed, hence exiting Get_Template_Name ', 3);
2829 END IF;
2830 RETURN TO_CHAR(NULL); --returning null is OK here as null will be displayed
2831 END IF;
2832
2833
2834 l_template_name := OKC_TERMS_UTIL_GRP.Get_Template_Name(
2835 p_api_version => p_api_version,
2836 p_init_msg_list => p_init_msg_list,
2837 p_template_id => p_template_id,
2838 x_return_status => x_return_status,
2839 x_msg_data => x_msg_data,
2840 x_msg_count => x_msg_count
2841 );
2842
2843
2844 IF l_debug_level > 0 THEN
2845 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
2846 oe_debug_pub.add('l_template_name: ' || l_template_name, 3);
2847 END IF;
2848
2849 --ETR
2850 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2852 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2853 RAISE FND_API.G_EXC_ERROR ;
2854 END IF;
2855 --ETR
2856
2857 IF l_debug_level > 0 THEN
2858 oe_debug_pub.add('End of OE_Contracts_util.Get_Template_Name, returning l_template_name: ' || l_template_name);
2859 END IF;
2860
2861 RETURN l_template_name;
2862
2863
2864 EXCEPTION
2865 WHEN FND_API.G_EXC_ERROR THEN
2866
2867 IF l_debug_level > 0 THEN
2868 oe_debug_pub.add('WHEN G_EXC_ERROR in Get_Template_Name ', 3);
2869 END IF;
2870
2871 x_return_status := FND_API.G_RET_STS_ERROR;
2872
2873 /*****
2874 not needed as per meeting
2875 --transfer error messages on OKC stack to OM stack
2876 OE_MSG_PUB.Transfer_Msg_Stack;
2877 *****/
2878
2879 --Get message count and data
2880 OE_MSG_PUB.Count_And_Get (
2881 p_count => x_msg_count,
2882 p_data => x_msg_data
2883 );
2884
2885
2886 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2887
2888 IF l_debug_level > 0 THEN
2889 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in Get_Template_Name ', 3);
2890 END IF;
2891
2892 --close any cursors
2893
2894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2895
2896 /*****
2897 not needed as per meeting
2898 --transfer error messages on OKC stack to OM stack
2899 OE_MSG_PUB.Transfer_Msg_Stack;
2900 *****/
2901
2902 --Get message count and data
2903 OE_MSG_PUB.Count_And_Get (
2904 p_count => x_msg_count,
2905 p_data => x_msg_data
2906 );
2907
2908
2909 WHEN OTHERS THEN
2910 IF l_debug_level > 0 THEN
2911 oe_debug_pub.add('WHEN OTHERS in Get_Template_Name ', 3);
2912 END IF;
2913
2914 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2915 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2916 'non_standard_article_exists'
2917 );
2918 END IF;
2919
2920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2921
2922 --Get message count and data
2923 OE_MSG_PUB.Count_And_Get (
2924 p_count => x_msg_count,
2925 p_data => x_msg_data
2926 );
2927
2928 END Get_Template_Name;
2929
2930
2931
2932
2933 --to instantiate T's/C's from a Terms template to a BSA or Sales Order
2934 --used internally by instantiate_doc_terms
2935 PROCEDURE instantiate_terms
2936 (
2937 p_api_version IN NUMBER,
2938 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2939 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2940
2941 p_template_id IN NUMBER,
2942 p_doc_type IN VARCHAR2,
2943 p_doc_id IN NUMBER,
2944 p_doc_start_date IN DATE ,
2945 p_doc_number IN VARCHAR2,
2946
2947 x_return_status OUT NOCOPY VARCHAR2,
2948 x_msg_count OUT NOCOPY NUMBER,
2949 x_msg_data OUT NOCOPY VARCHAR2
2950 )
2951
2952 IS
2953
2954 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2955 BEGIN
2956
2957 IF l_debug_level > 0 THEN
2958 oe_debug_pub.add('In OE_Contracts_util.instantiate_terms ', 1);
2959 END IF;
2960
2961 x_return_status := FND_API.G_RET_STS_SUCCESS;
2962
2963 IF p_init_msg_list = FND_API.G_TRUE THEN
2964 oe_msg_pub.initialize;
2965 END IF;
2966
2967 --Check for licensing
2968 IF OE_Contracts_util.check_license() <> 'Y' THEN
2969 IF l_debug_level > 0 THEN
2970 oe_debug_pub.add('Contractual option not licensed, hence exiting instantiate_terms ', 3);
2971 END IF;
2972 RETURN;
2973 END IF;
2974
2975
2976 IF l_debug_level > 0 THEN
2977 oe_debug_pub.add('Calling OKC_TERMS_COPY_GRP.copy_terms ', 3);
2978 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
2979 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
2980 oe_debug_pub.add('p_template_id: ' || p_template_id, 3);
2981 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
2982 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
2983 oe_debug_pub.add('p_doc_start_date: ' || p_doc_start_date, 3);
2984 oe_debug_pub.add('p_doc_number: ' || p_doc_number, 3);
2985 END IF;
2986
2987
2988 OKC_TERMS_COPY_GRP.copy_terms (
2989 p_api_version => p_api_version,
2990 p_init_msg_list => p_init_msg_list,
2991 p_commit => p_commit,
2992 p_template_id => p_template_id,
2993 p_target_doc_type => p_doc_type,
2994 p_target_doc_id => p_doc_id,
2995 ------p_article_effective_date => p_doc_start_date, -- we should not pass effectivity date ref: Bug 3307561
2996 p_article_effective_date => NULL,
2997 ------------------------------p_copy_deliverables => 'N', parameter no longer exists
2998 p_validation_string => NULL,
2999 p_document_number => p_doc_number,
3000 x_return_status => x_return_status,
3001 x_msg_count => x_msg_count,
3002 x_msg_data => x_msg_data
3003 );
3004
3005 IF l_debug_level > 0 THEN
3006 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
3007 END IF;
3008
3009 --ETR
3010 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3012 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3013 RAISE FND_API.G_EXC_ERROR ;
3014 END IF;
3015 --ETR
3016
3017 IF l_debug_level > 0 THEN
3018 oe_debug_pub.add('End of OE_Contracts_util.instantiate_terms , x_return_status ' || x_return_status, 1);
3019 END IF;
3020
3021 EXCEPTION
3022 WHEN FND_API.G_EXC_ERROR THEN
3023
3024 IF l_debug_level > 0 THEN
3025 oe_debug_pub.add('WHEN G_EXC_ERROR in instantiate_terms ', 3);
3026 END IF;
3027
3028 x_return_status := FND_API.G_RET_STS_ERROR;
3029
3030 /*****
3031 not needed as per meeting
3032 --transfer error messages on OKC stack to OM stack
3033 OE_MSG_PUB.Transfer_Msg_Stack;
3034 *****/
3035
3036 --Get message count and data
3037 OE_MSG_PUB.Count_And_Get (
3038 p_count => x_msg_count,
3039 p_data => x_msg_data
3040 );
3041
3042
3043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3044
3045 IF l_debug_level > 0 THEN
3046 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in instantiate_terms ', 3);
3047 END IF;
3048
3049 --close any cursors
3050
3051 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3052
3053 /*****
3054 not needed as per meeting
3055 --transfer error messages on OKC stack to OM stack
3056 OE_MSG_PUB.Transfer_Msg_Stack;
3057 *****/
3058
3059 --Get message count and data
3060 OE_MSG_PUB.Count_And_Get (
3061 p_count => x_msg_count,
3062 p_data => x_msg_data
3063 );
3064
3065
3066 WHEN OTHERS THEN
3067
3068 IF l_debug_level > 0 THEN
3069 oe_debug_pub.add('WHEN OTHERS in instantiate_terms ', 3);
3070 END IF;
3071
3072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3073
3074 /*****
3075 not needed as per meeting
3076 --transfer error messages on OKC stack to OM stack
3077 OE_MSG_PUB.Transfer_Msg_Stack;
3078 *****/
3079
3080 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3081 OE_MSG_PUB.Add_Exc_Msg (
3082 G_PKG_NAME,
3083 'get_terms_template'
3084 );
3085 END IF;
3086
3087
3088 --Get message count and data
3089 OE_MSG_PUB.Count_And_Get (
3090 p_count => x_msg_count,
3091 p_data => x_msg_data
3092 );
3093
3094 END instantiate_terms;
3095
3096
3097
3098 --to instantiate T's/C's from a Terms template to a BSA or Sales Order when after saving the BSA/Sales Order
3099 --the contract template id is defaulted for a new BSA or Sales Order
3100 PROCEDURE instantiate_doc_terms
3101 (
3102 p_api_version IN NUMBER,
3103 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3104 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3105
3106 p_template_id IN NUMBER,
3107 p_doc_type IN VARCHAR2,
3108 p_doc_id IN NUMBER,
3109 p_doc_start_date IN DATE ,
3110 p_doc_number IN VARCHAR2,
3111
3112 x_return_status OUT NOCOPY VARCHAR2,
3113 x_msg_count OUT NOCOPY NUMBER,
3114 x_msg_data OUT NOCOPY VARCHAR2
3115 )
3116
3117 IS
3118
3119 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3120
3121 l_instntiatd_templt_id NUMBER;
3122 lx_template_name VARCHAR2(500);
3123
3124 BEGIN
3125
3126 IF l_debug_level > 0 THEN
3127 oe_debug_pub.add('In OE_Contracts_util.instantiate_doc_terms ', 1);
3128 END IF;
3129
3130 x_return_status := FND_API.G_RET_STS_SUCCESS;
3131
3132 IF p_init_msg_list = FND_API.G_TRUE THEN
3133 oe_msg_pub.initialize;
3134 END IF;
3135
3136 --Check for licensing
3137 IF OE_Contracts_util.check_license() <> 'Y' THEN
3138 IF l_debug_level > 0 THEN
3139 oe_debug_pub.add('Contractual option not licensed, hence exiting instantiate_doc_terms ', 3);
3140 END IF;
3141 RETURN;
3142 END IF;
3143
3144
3145 IF l_debug_level > 0 THEN
3146 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3147 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3148 oe_debug_pub.add('p_template_id: ' || p_template_id, 3);
3149 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
3150 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
3151 oe_debug_pub.add('p_doc_start_date: ' || p_doc_start_date, 3);
3152 oe_debug_pub.add('p_doc_number: ' || p_doc_number, 3);
3153 END IF;
3154
3155
3156
3157 /** In case , a contract template has already been freshly defaulted for the BSA or Sales Order, we need to instantiate
3158 the terms and conditions of the template for the BSA or Sales Order before invoking the articles authoring UI **/
3159
3160 IF p_template_id IS NOT NULL THEN
3161
3162
3163 IF l_debug_level > 0 THEN
3164 oe_debug_pub.add('Calling oe_contracts_util.get_terms_template', 3);
3165 END IF;
3166
3167
3168 --first determine whether any terms and conditions have been instantiated for the BSA or Sales Order
3169 oe_contracts_util.get_terms_template (
3170 p_api_version => 1.0,
3171 p_init_msg_list => FND_API.G_FALSE,
3172 p_commit => FND_API.G_FALSE,
3173
3174 p_doc_type => p_doc_type,
3175 p_doc_id => p_doc_id,
3176
3177 x_template_id => l_instntiatd_templt_id,
3178 x_template_name => lx_template_name,
3179 x_return_status => x_return_status,
3180 x_msg_count => x_msg_count,
3181 x_msg_data => x_msg_data
3182 );
3183
3184 IF l_debug_level > 0 THEN
3185 oe_debug_pub.add('l_instntiatd_templt_id: ' || l_instntiatd_templt_id, 3);
3186 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
3187 END IF;
3188
3189
3190 IF l_instntiatd_templt_id IS NULL THEN
3191 /** i.e. the contract template freshly defaulted in the form has not yet been
3192 instantiated so go ahead and instantiate it against the BSA or SO **/
3193
3194 IF l_debug_level > 0 THEN
3195 oe_debug_pub.add('Calling oe_contracts_util.instantiate_terms', 3);
3196 END IF;
3197
3198 oe_contracts_util.instantiate_terms (
3199 p_api_version => 1.0,
3200 p_init_msg_list => FND_API.G_FALSE,
3201 p_commit => FND_API.G_TRUE, --important: need to save before invoking articles UI
3202
3203 p_template_id => p_template_id,
3204 p_doc_type => p_doc_type,
3205 p_doc_id => p_doc_id,
3206 --------p_doc_start_date => NVL(p_doc_start_date, SYSDATE),
3207 p_doc_start_date => null, -- we should not pass effectivity date ref: Bug 3307561
3208 p_doc_number => p_doc_number,
3209 x_return_status => x_return_status,
3210 x_msg_count => x_msg_count,
3211 x_msg_data => x_msg_data
3212 );
3213
3214
3215 IF l_debug_level > 0 THEN
3216 oe_debug_pub.add('After trying to instantiate p_template_id: ' || p_template_id, 3);
3217 oe_debug_pub.add('x_return_status: ' || x_return_status, 3);
3218 END IF;
3219
3220
3221
3222 END IF;
3223
3224 END IF;
3225
3226
3227
3228 IF l_debug_level > 0 THEN
3229 oe_debug_pub.add('End of OE_Contracts_util.instantiate_doc_terms , x_return_status ' || x_return_status, 1);
3230 END IF;
3231
3232 EXCEPTION
3233 WHEN FND_API.G_EXC_ERROR THEN
3234
3235 IF l_debug_level > 0 THEN
3236 oe_debug_pub.add('WHEN G_EXC_ERROR in instantiate_doc_terms ', 3);
3237 END IF;
3238
3239 x_return_status := FND_API.G_RET_STS_ERROR;
3240
3241 /*****
3242 not needed as per meeting
3243 --transfer error messages on OKC stack to OM stack
3244 OE_MSG_PUB.Transfer_Msg_Stack;
3245 *****/
3246
3247 --Get message count and data
3248 OE_MSG_PUB.Count_And_Get (
3249 p_count => x_msg_count,
3250 p_data => x_msg_data
3251 );
3252
3253
3254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3255
3256 IF l_debug_level > 0 THEN
3257 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in instantiate_doc_terms ', 3);
3258 END IF;
3259
3260 --close any cursors
3261
3262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3263
3264 /*****
3265 not needed as per meeting
3266 --transfer error messages on OKC stack to OM stack
3267 OE_MSG_PUB.Transfer_Msg_Stack;
3268 *****/
3269
3270 --Get message count and data
3271 OE_MSG_PUB.Count_And_Get (
3272 p_count => x_msg_count,
3273 p_data => x_msg_data
3274 );
3275
3276
3277 WHEN OTHERS THEN
3278
3279 IF l_debug_level > 0 THEN
3280 oe_debug_pub.add('WHEN OTHERS in instantiate_doc_terms ', 3);
3281 END IF;
3282
3283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3284
3285 /*****
3286 not needed as per meeting
3287 --transfer error messages on OKC stack to OM stack
3288 OE_MSG_PUB.Transfer_Msg_Stack;
3289 *****/
3290
3291 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3292 OE_MSG_PUB.Add_Exc_Msg (
3293 G_PKG_NAME,
3294 'get_terms_template'
3295 );
3296 END IF;
3297
3298
3299 --Get message count and data
3300 OE_MSG_PUB.Count_And_Get (
3301 p_count => x_msg_count,
3302 p_data => x_msg_data
3303 );
3304
3305
3306
3307
3308 END instantiate_doc_terms;
3309
3310
3311 --ETR
3312 --This function is to check whether or not the given order has already been
3313 --accepted (i.e signed). Returns 'Y' if accepted, and 'N' otherwise.
3314 FUNCTION Is_order_signed(
3315 p_api_version IN NUMBER,
3316 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3317
3318 x_return_status OUT NOCOPY VARCHAR2,
3319 x_msg_data OUT NOCOPY VARCHAR2,
3320 x_msg_count OUT NOCOPY NUMBER,
3321
3322 p_doc_id IN NUMBER
3323
3324 ) RETURN VARCHAR2 IS
3325 l_api_version CONSTANT NUMBER := 1;
3326 l_api_name CONSTANT VARCHAR2(30) := 'Is_order_signed';
3327 l_return_value VARCHAR2(100) := 'N';
3328 --ETR
3329 l_sign_by VARCHAR2(240);
3330 l_sign_date DATE;
3331 --ETR
3332 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3333
3334 CURSOR find_ord_signed IS
3335 SELECT a.customer_signature,
3336 a.customer_signature_date
3337 FROM oe_order_headers_all a
3338 WHERE a.header_id = p_doc_id;
3339
3340 BEGIN
3341 IF (l_debug_level > 0) THEN
3342 oe_debug_pub.add('In OE_Contracts_util.is_order_signed', 2);
3343 END IF;
3344 -- Initialize message list if p_init_msg_list is set to TRUE.
3345 IF p_init_msg_list = FND_API.G_TRUE THEN
3346 oe_msg_pub.initialize;
3347 END IF;
3348 -- Initialize API return status to success
3349 x_return_status := FND_API.G_RET_STS_SUCCESS;
3350 IF l_debug_level > 0 THEN
3351 oe_debug_pub.add('Fetching customer_signature and customer_signature_date from oe_order_headers_all ', 3);
3352 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3353 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3354 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
3355 END IF;
3356
3357 OPEN find_ord_signed;
3358 FETCH find_ord_signed INTO l_sign_by, l_sign_date;
3359 CLOSE find_ord_signed;
3360
3361 /*************************************************
3362 IF l_sign_by IS NULL OR l_sign_date IS NULL THEN
3363 l_return_value :='N';
3364 ELSE
3365 l_return_value :='Y';
3366 END IF;
3367 *************************************************/
3368 IF l_sign_by IS NOT NULL OR l_sign_date IS NOT NULL THEN
3369 l_return_value :='Y';
3370 ELSE
3371 l_return_value :='N';
3372 END IF;
3373
3374
3375 IF l_debug_level > 0 THEN
3376 oe_debug_pub.add('Order signed ?: sign_by = ' || l_sign_by || ' sign_date = ' || l_sign_date, 3);
3377 oe_debug_pub.add('Order signed ?: return value = ' || l_return_value, 3);
3378 END IF;
3379
3380 IF (l_debug_level > 0) THEN
3381 oe_debug_pub.add('End of OE_Contracts_util.is_order_signed', 2);
3382 END IF;
3383 RETURN l_return_value;
3384
3385 EXCEPTION
3386
3387 WHEN OTHERS THEN
3388 IF l_debug_level > 0 THEN
3389 oe_debug_pub.add('WHEN-OTHERS in is_order_signed: '||sqlerrm, 1);
3390 END IF;
3391 IF find_ord_signed%ISOPEN THEN
3392 CLOSE find_ord_signed;
3393 END IF;
3394
3395 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3396 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
3397 'is_order_signed'
3398 );
3399 END IF;
3400 x_return_status := G_RET_STS_UNEXP_ERROR;
3401 END Is_order_signed ;
3402 --ETR
3403
3404
3405
3406 --This function will be called from process order to copy terms and coditions
3407 --from quote to order(terms instantiated on quote)
3408 --from quote to order(terms not instantiated on quote) ,get terms from template
3409 -- from sales order to sales order
3410 --instantiate from template to sales order
3411
3412 PROCEDURE copy_doc
3413 (
3414 p_api_version IN NUMBER,
3415 p_init_msg_list IN VARCHAR2,
3416 p_commit IN VARCHAR2,
3417 p_source_doc_type IN VARCHAR2,
3418 p_source_doc_id IN NUMBER,
3419 p_target_doc_type IN VARCHAR2,
3420 p_target_doc_id IN NUMBER,
3421 p_contract_template_id IN NUMBER,
3422 x_return_status OUT NOCOPY VARCHAR2,
3423 x_msg_count OUT NOCOPY NUMBER,
3424 x_msg_data OUT NOCOPY VARCHAR2)
3425
3426 IS
3427
3428 l_target_doc_type VARCHAR2(30):= p_target_doc_type;
3429 l_target_doc_id NUMBER := p_target_doc_id;
3430 l_doc_template_name VARCHAR2(240):= null;
3431 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3432 l_keep_version VARCHAR2(1) := 'Y';
3433 l_copy_attch VARCHAR2(1) := 'N';
3434 l_document_number NUMBER:= null;
3435
3436 BEGIN
3437
3438 IF l_debug_level > 0 THEN
3439 oe_debug_pub.add('In OE_Contracts_util.copy_doc ', 1);
3440 END IF;
3441
3442 x_return_status := FND_API.G_RET_STS_SUCCESS;
3443
3444 IF p_init_msg_list = FND_API.G_TRUE THEN
3445 oe_msg_pub.initialize;
3446 END IF;
3447
3448 --Check contract Licence
3449 IF OE_Contracts_util.check_license() <> 'Y' THEN
3450 IF l_debug_level > 0 THEN
3451 oe_debug_pub.add('Contractual option not licensed. Exiting copy_doc', 3);
3452 END IF;
3453 RETURN;
3454 END IF;
3455
3456
3457 IF l_debug_level > 0 THEN
3458 oe_debug_pub.add('Parameter Values passed', 3);
3459 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3460 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3461 oe_debug_pub.add('p_commit: ' || p_commit,3);
3462 oe_debug_pub.add('p_source_doc_type: ' || p_source_doc_type,3);
3463 oe_debug_pub.add('p_source_doc_id: ' || p_source_doc_id, 3);
3464 oe_debug_pub.add('p_target_doc_type: ' || p_target_doc_type,3);
3465 oe_debug_pub.add('p_target_doc_id: ' || p_target_doc_id, 3);
3466 oe_debug_pub.add('p_contract_template_id' || p_contract_template_id, 3);
3467 END IF;
3468
3469 IF p_source_doc_id is not null Then
3470 l_doc_template_name := okc_terms_util_grp.Get_Terms_Template(
3471 p_doc_type => p_source_doc_type,
3472 p_doc_id => p_source_doc_id);
3473 END IF;
3474
3475 IF p_target_doc_type = 'O' THEN
3476 BEGIN
3477 SELECT order_number
3478 INTO l_document_number
3479 FROM oe_order_headers_all
3480 WHERE header_id = p_target_doc_id;
3481 EXCEPTION
3482 WHEN NO_DATA_FOUND THEN
3483 l_document_number := NULL;
3484 END;
3485 END IF;
3486 If l_debug_level > 0 THEN
3487 oe_debug_pub.add('l_document_number: ' || l_document_number, 3);
3488 End If;
3489
3490 IF l_doc_template_name is null THEN
3491 --Instantiate from the template
3492 If p_contract_template_id is not null then
3493 If l_debug_level > 0 THEN
3494 oe_debug_pub.add('Instantiating COntract Terms, No articles on source document',3);
3495 End If;
3496
3497 OKC_TERMS_COPY_GRP.copy_terms (
3498 p_api_version => p_api_version,
3499 p_init_msg_list => p_init_msg_list,
3500 p_commit => p_commit,
3501 p_template_id => p_contract_template_id,
3502 p_target_doc_type => l_target_doc_type,
3503 p_target_doc_id => p_target_doc_id,
3504 p_validation_string => NULL,
3505 -----p_article_effective_date => sysdate, -- we should not pass effectivity date ref: Bug 3307561
3506 p_article_effective_date => null,
3507 p_document_number => to_char(l_document_number),
3508 x_return_status => x_return_status,
3509 x_msg_count => x_msg_count,
3510 x_msg_data => x_msg_data);
3511
3512
3513 IF x_return_status = FND_API.G_RET_STS_ERROR Then
3514 RAISE FND_API.G_EXC_ERROR;
3515 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3517 END IF;
3518
3519 End If;
3520 ELSE
3521 If l_debug_level > 0 THEN
3522 oe_debug_pub.add('Terms exist in source document',3);
3523 End If;
3524
3525 If (p_source_doc_type <> p_target_doc_type) Then
3526 l_copy_attch := 'Y';
3527 Else
3528 l_keep_version :='N';
3529 End If;
3530
3531 If (p_source_doc_type =p_target_doc_type and
3532 p_source_doc_id =p_target_doc_id) THEN
3533
3534 If l_debug_level > 0 THEN
3535 oe_debug_pub.add('Target Doc and Source Document are Same. exiting copy_doc',3);
3536 End If;
3537 RETURN;
3538 End If;
3539
3540 If l_debug_level > 0 THEN
3541 oe_debug_pub.add('Calling OKC_TERMS_COPY_GRP.copy_doc',3);
3542 oe_debug_pub.add('p_copy_doc_attachments: ' ||l_copy_attch,3);
3543 oe_debug_pub.add('p_keep_version: ' || l_keep_version, 3);
3544 oe_debug_pub.add('p_target_doc_type: ' ||l_target_doc_type,3);
3545 oe_debug_pub.add('p_target_doc_id: ' || l_target_doc_id, 3);
3546 End If;
3547
3548 OKC_TERMS_COPY_GRP.copy_doc (
3549 p_api_version => p_api_version,
3550 p_init_msg_list => p_init_msg_list,
3551 p_commit => p_commit,
3552 p_source_doc_type => p_source_doc_type,
3553 p_source_doc_id => p_source_doc_id,
3554 p_target_doc_type => l_target_doc_type,
3555 p_target_doc_id => l_target_doc_id,
3556 p_keep_version => l_keep_version,
3557 -----p_article_effective_date => sysdate,
3558 p_article_effective_date => null, -- we should not pass effectivity date ref: Bug 3307561
3559 p_copy_doc_attachments => l_copy_attch,
3560 x_return_status => x_return_status,
3561 x_msg_data => x_msg_data,
3562 x_msg_count => x_msg_count,
3563 p_copy_abstract_yn => 'Y');
3564
3565
3566 IF x_return_status = FND_API.G_RET_STS_ERROR Then
3567 RAISE FND_API.G_EXC_ERROR;
3568 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3570 END IF;
3571
3572
3573 END IF;
3574
3575 If l_debug_level > 0 THEN
3576 oe_debug_pub.add('Return Status + x_return_status',3);
3577 End If;
3578
3579
3580 EXCEPTION
3581 WHEN FND_API.G_EXC_ERROR THEN
3582
3583 IF l_debug_level > 0 THEN
3584 oe_debug_pub.add('WHEN G_EXC_ERROR in copy_doc ', 3);
3585 END IF;
3586 --Get message count and data
3587 OE_MSG_PUB.Count_And_Get (
3588 p_count => x_msg_count,
3589 p_data => x_msg_data);
3590
3591 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3592
3593 IF l_debug_level > 0 THEN
3594 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in copy_doc ', 3);
3595 END IF;
3596
3597 --Get message count and data
3598 OE_MSG_PUB.Count_And_Get (
3599 p_count => x_msg_count,
3600 p_data => x_msg_data);
3601
3602 WHEN OTHERS THEN
3603
3604 IF l_debug_level > 0 THEN
3605 oe_debug_pub.add('WHEN OTHERS in copy_doc ', 3);
3606 END IF;
3607
3608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3609
3610 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3611 OE_MSG_PUB.Add_Exc_Msg (
3612 G_PKG_NAME,
3613 'copy_doc');
3614 END IF;
3615
3616 --Get message count and data
3617 OE_MSG_PUB.Count_And_Get (
3618 p_count => x_msg_count,
3619 p_data => x_msg_data
3620 );
3621
3622 END COPY_DOC;
3623
3624
3625
3626
3627 -- This function is a wrapper on top of oe_line_util.get_item_info
3628 -- procedure. This is used to get the value and description for the products
3629 -- in the blanket sales lines.
3630 -- This will return the internal item and description for all but customer items
3631 -- for which it returns the customer product and description
3632 -- This function is used in the oe_blktprt_lines_v view, for the printing solution
3633
3634 FUNCTION GET_ITEM_INFO
3635 ( p_item_or_desc IN VARCHAR2
3636 , p_item_identifier_type IN VARCHAR2
3637 , p_inventory_item_id IN Number
3638 , p_ordered_item_id IN Number
3639 , p_sold_to_org_id IN Number
3640 , p_ordered_item IN VARCHAR2
3641 , p_org_id IN Number DEFAULT NULL
3642 ) RETURN VARCHAR2 IS
3643
3644 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3645 l_ordered_item varchar2(2000);
3646 l_inventory_item varchar2(300);
3647 l_ordered_item_desc varchar2(2000);
3648 l_return_status varchar2(30);
3649 l_msg_count number := 0;
3650 l_msg_data varchar2(2000);
3651 l_value varchar2(2000);
3652
3653 BEGIN
3654 IF l_debug_level > 0 THEN
3655 oe_debug_pub.add( 'ENTER GET_ITEM_INFO FUNCTION' ) ;
3656 oe_debug_pub.add( 'ITEM_OR_DESC : '||P_ITEM_OR_DESC ) ;
3657 oe_debug_pub.add( 'ITEM_IDENTIFIER_TYPE : '||P_ITEM_IDENTIFIER_TYPE ) ;
3658 oe_debug_pub.add( 'INVENTORY_ITEM_ID : '||P_INVENTORY_ITEM_ID ) ;
3659 oe_debug_pub.add( 'ORDERED_ITEM_ID : '||P_ORDERED_ITEM_ID ) ;
3660 oe_debug_pub.add( 'ORDERED_ITEM : '||P_ORDERED_ITEM ) ;
3661 oe_debug_pub.add( 'SOLD_TO_ORG_ID : '||P_SOLD_TO_ORG_ID ) ;
3662 END IF;
3663
3664 OE_LINE_UTIL.GET_ITEM_INFO (
3665 x_return_status => l_return_status
3666 , x_msg_count => l_msg_count
3667 , x_msg_data => l_msg_data
3668 , p_item_identifier_type => p_item_identifier_type
3669 , p_inventory_item_id => p_inventory_item_id
3670 , p_ordered_item_id => p_ordered_item_id
3671 , p_sold_to_org_id => p_sold_to_org_id
3672 , p_ordered_item => p_ordered_item
3673 , x_ordered_item => l_ordered_item
3674 , x_ordered_item_desc => l_ordered_item_desc
3675 , x_inventory_item => l_inventory_item
3676 , p_org_id => p_org_id
3677
3678 );
3679
3680 IF l_debug_level > 0 THEN
3681 oe_debug_pub.add('Return status from OE_LINE_UTIL.GET_ITEM_INFO is '||l_return_status) ;
3682 END IF;
3683
3684 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3686 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3687 RAISE FND_API.G_EXC_ERROR;
3688 END IF;
3689
3690 IF p_item_or_desc = 'I' THEN
3691 l_value := l_inventory_item;
3692 ELSIF p_item_or_desc = 'D' THEN
3693 l_value := l_ordered_item_desc;
3694 END IF;
3695
3696 IF l_debug_level > 0 THEN
3697 oe_debug_pub.add( 'l_value = '||l_value ) ;
3698 END IF;
3699
3700 IF l_debug_level > 0 THEN
3701 oe_debug_pub.add( 'EXIT GET_ITEM_INFO FUNCTION' ) ;
3702 END IF;
3703
3704 RETURN l_value;
3705
3706 EXCEPTION
3707 WHEN FND_API.G_EXC_ERROR THEN
3708 IF l_debug_level > 0 THEN
3709 oe_debug_pub.add('In oe_contracts_util.get_item_info:g_exc_error section') ;
3710 END IF;
3711 RAISE FND_API.G_EXC_ERROR;
3712 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3713 IF l_debug_level > 0 THEN
3714 oe_debug_pub.add('In oe_contracts_util.get_item_info:g_exc_unexpected_error section') ;
3715 END IF;
3716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3717 WHEN OTHERS THEN
3718 IF l_debug_level > 0 THEN
3719 oe_debug_pub.add('In oe_contracts_util.get_item_info: when others section') ;
3720 END IF;
3721 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3722 THEN
3723 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'GET_ITEM_INFO');
3724 END IF;
3725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3726 END GET_ITEM_INFO;
3727
3728 --FP word integration
3729 PROCEDURE get_contract_defaults
3730 (
3731 p_api_version IN NUMBER,
3732 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3733 p_doc_type IN VARCHAR2,
3734 p_template_id IN NUMBER,
3735 x_authoring_party OUT NOCOPY VARCHAR2,
3736 x_contract_source OUT NOCOPY VARCHAR2,
3737 x_template_name OUT NOCOPY VARCHAR2,
3738 x_return_status OUT NOCOPY VARCHAR2,
3739 x_msg_count OUT NOCOPY NUMBER,
3740 x_msg_data OUT NOCOPY VARCHAR2
3741 )
3742
3743 IS
3744 l_template_description VARCHAR2(2000); -- bug 4382305
3745 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3746
3747 BEGIN
3748
3749 IF l_debug_level > 0 THEN
3750 oe_debug_pub.add('In OE_Contracts_util.get_contract_defaults ', 1);
3751 END IF;
3752
3753 x_return_status := FND_API.G_RET_STS_SUCCESS;
3754
3755 IF p_init_msg_list = FND_API.G_TRUE THEN
3756 oe_msg_pub.initialize;
3757 END IF;
3758
3759 --Check for licensing
3760 IF OE_Contracts_util.check_license() <> 'Y' THEN
3761 IF l_debug_level > 0 THEN
3762 oe_debug_pub.add('Contractual option not licensed, hence exiting get_terms_template ', 3);
3763 END IF;
3764 x_contract_source := NULL;
3765 x_authoring_party := NULL;
3766 x_template_name := NULL;
3767 RETURN;
3768 END IF;
3769
3770
3771 IF l_debug_level > 0 THEN
3772 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.get_contract_defaults ', 3);
3773 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3774 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3775 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
3776 oe_debug_pub.add('p_template_id: ' || p_template_id, 3);
3777 END IF;
3778
3779 OKC_TERMS_UTIL_GRP.get_contract_defaults (
3780 p_api_version => p_api_version,
3781 p_init_msg_list => p_init_msg_list,
3782 x_return_status => x_return_status,
3783 x_msg_data => x_msg_data,
3784 x_msg_count => x_msg_count,
3785 p_template_id => p_template_id,
3786 p_document_type => p_doc_type,
3787 x_authoring_party => x_authoring_party,
3788 x_contract_source => x_contract_source,
3789 x_template_name => x_template_name,
3790 x_template_description => l_template_description
3791 );
3792
3793 IF l_debug_level > 0 THEN
3794 oe_debug_pub.add('p_template_id: '|| p_template_id, 3);
3795 oe_debug_pub.add('x_contract_source: '|| x_contract_source, 3);
3796 oe_debug_pub.add('x_authoring_party: '|| x_authoring_party, 3);
3797 oe_debug_pub.add('x_template_name: '|| x_template_name, 3);
3798 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
3799 END IF;
3800
3801 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3803 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3804 RAISE FND_API.G_EXC_ERROR ;
3805 END IF;
3806
3807 IF l_debug_level > 0 THEN
3808 oe_debug_pub.add('End of OE_Contracts_util.get_contract_defaults , x_return_status ' || x_return_status, 1);
3809 END IF;
3810
3811 EXCEPTION
3812 WHEN FND_API.G_EXC_ERROR THEN
3813
3814 IF l_debug_level > 0 THEN
3815 oe_debug_pub.add('WHEN G_EXC_ERROR in get_contract_defaults ', 3);
3816 END IF;
3817
3818 x_return_status := FND_API.G_RET_STS_ERROR;
3819
3820 --Get message count and data
3821 OE_MSG_PUB.Count_And_Get (
3822 p_count => x_msg_count,
3823 p_data => x_msg_data
3824 );
3825
3826
3827 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3828
3829 IF l_debug_level > 0 THEN
3830 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_contract_defaults ', 3);
3831 END IF;
3832
3833 --close any cursors
3834
3835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3836
3837 --Get message count and data
3838 OE_MSG_PUB.Count_And_Get (
3839 p_count => x_msg_count,
3840 p_data => x_msg_data
3841 );
3842
3843
3844 WHEN OTHERS THEN
3845
3846 IF l_debug_level > 0 THEN
3847 oe_debug_pub.add('WHEN OTHERS in get_contract_defaults ', 3);
3848 END IF;
3849
3850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3851
3852 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3853 OE_MSG_PUB.Add_Exc_Msg (
3854 G_PKG_NAME,
3855 'get_contract_defaults'
3856 );
3857 END IF;
3858
3859
3860 --Get message count and data
3861 OE_MSG_PUB.Count_And_Get (
3862 p_count => x_msg_count,
3863 p_data => x_msg_data
3864 );
3865
3866 END get_contract_defaults;
3867
3868
3869 --get the template name, id, source and authoring party for the doc id
3870 PROCEDURE get_contract_details_all
3871 (
3872 p_api_version IN NUMBER,
3873 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3874 p_doc_type IN VARCHAR2,
3875 p_doc_id IN NUMBER,
3876 p_document_version IN NUMBER := NULL,
3877 x_template_id OUT NOCOPY NUMBER,
3878 x_authoring_party OUT NOCOPY VARCHAR2,
3879 x_contract_source OUT NOCOPY VARCHAR2,
3880 x_contract_source_code OUT NOCOPY VARCHAR2,
3881 x_has_primary_doc OUT NOCOPY VARCHAR2,
3882 x_template_name OUT NOCOPY VARCHAR2,
3883 x_return_status OUT NOCOPY VARCHAR2,
3884 x_msg_count OUT NOCOPY NUMBER,
3885 x_msg_data OUT NOCOPY VARCHAR2
3886 )
3887
3888 IS
3889
3890 l_has_terms VARCHAR2(100);
3891 l_authoring_party_code VARCHAR2(100);
3892 l_template_description VARCHAR2(2000); -- bug 4382305
3893 l_template_instruction VARCHAR2(2000); -- bug 4382305
3894 l_is_primary_doc_mergeable VARCHAR2(100);
3895 l_primary_doc_file_id VARCHAR2(100);
3896 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3897
3898
3899 BEGIN
3900
3901 IF l_debug_level > 0 THEN
3902 oe_debug_pub.add('In OE_Contracts_util.get_contract_details_all ', 1);
3903 END IF;
3904
3905 x_return_status := FND_API.G_RET_STS_SUCCESS;
3906
3907 IF p_init_msg_list = FND_API.G_TRUE THEN
3908 oe_msg_pub.initialize;
3909 END IF;
3910
3911 --Check for licensing
3912 IF OE_Contracts_util.check_license() <> 'Y' THEN
3913 IF l_debug_level > 0 THEN
3914 oe_debug_pub.add('Contractual option not licensed, hence exiting get_contract_details_all ', 3);
3915 END IF;
3916 x_contract_source := NULL;
3917 x_authoring_party := NULL;
3918 x_template_name := NULL;
3919 x_template_id := NULL;
3920 RETURN;
3921 END IF;
3922
3923
3924 IF l_debug_level > 0 THEN
3925 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.get_contract_details_all ', 3);
3926 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
3927 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
3928 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
3929 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
3930 END IF;
3931
3932 OKC_TERMS_UTIL_GRP.get_contract_details_all (
3933 p_api_version => p_api_version,
3934 p_init_msg_list => p_init_msg_list,
3935 x_return_status => x_return_status,
3936 x_msg_data => x_msg_data,
3937 x_msg_count => x_msg_count,
3938 p_document_type => p_doc_type,
3939 p_document_id => p_doc_id,
3940 p_document_version => p_document_version,
3941 x_has_terms => l_has_terms,
3942 x_authoring_party_code => l_authoring_party_code,
3943 x_authoring_party => x_authoring_party,
3944 x_contract_source_code => x_contract_source_code,
3945 x_contract_source => x_contract_source,
3946 x_template_id => x_template_id,
3947 x_template_name => x_template_name,
3948 x_template_description => l_template_description,
3949 x_template_instruction => l_template_instruction,
3950 x_has_primary_doc => x_has_primary_doc,
3951 x_is_primary_doc_mergeable => l_is_primary_doc_mergeable,
3952 x_primary_doc_file_id => l_primary_doc_file_id
3953 );
3954
3955 IF l_debug_level > 0 THEN
3956 oe_debug_pub.add('x_contract_source: '|| x_contract_source, 3);
3957 oe_debug_pub.add('x_contract_source_code: '|| x_contract_source_code, 3);
3958 oe_debug_pub.add('x_template_id: '|| x_template_id, 3);
3959 oe_debug_pub.add('x_authoring_party: '|| x_authoring_party, 3);
3960 oe_debug_pub.add('x_template_name: '|| x_template_name, 3);
3961 oe_debug_pub.add('x_has_primary_doc: '|| x_has_primary_doc, 3);
3962 oe_debug_pub.add('x_msg_data: '|| x_msg_data, 3); -- bug 4382305
3963 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
3964 END IF;
3965
3966 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3968 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3969 RAISE FND_API.G_EXC_ERROR ;
3970 END IF;
3971
3972 IF l_debug_level > 0 THEN
3973 oe_debug_pub.add('End of OE_Contracts_util.get_contract_details_all , x_return_status ' || x_return_status, 1);
3974 END IF;
3975
3976 EXCEPTION
3977 WHEN FND_API.G_EXC_ERROR THEN
3978
3979 IF l_debug_level > 0 THEN
3980 oe_debug_pub.add('WHEN G_EXC_ERROR in get_contract_details_all ', 3);
3981 END IF;
3982
3983 x_return_status := FND_API.G_RET_STS_ERROR;
3984
3985 --Get message count and data
3986 OE_MSG_PUB.Count_And_Get (
3987 p_count => x_msg_count,
3988 p_data => x_msg_data
3989 );
3990
3991
3992 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3993
3994 IF l_debug_level > 0 THEN
3995 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in get_contract_details_all ', 3);
3996 END IF;
3997
3998 --close any cursors
3999
4000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4001
4002 --Get message count and data
4003 OE_MSG_PUB.Count_And_Get (
4004 p_count => x_msg_count,
4005 p_data => x_msg_data
4006 );
4007
4008
4009 WHEN OTHERS THEN
4010
4011 IF l_debug_level > 0 THEN
4012 oe_debug_pub.add('WHEN OTHERS in get_contract_details_all ', 3);
4013 END IF;
4014
4015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4016
4017 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4018 OE_MSG_PUB.Add_Exc_Msg (
4019 G_PKG_NAME,
4020 'get_contract_details_all'
4021 );
4022 END IF;
4023
4024
4025 --Get message count and data
4026 OE_MSG_PUB.Count_And_Get (
4027 p_count => x_msg_count,
4028 p_data => x_msg_data
4029 );
4030
4031 END get_contract_details_all;
4032
4033
4034
4035
4036 --check if template attached to order type is valid or not
4037 Function Is_Terms_Template_Valid
4038 (
4039 p_api_version IN NUMBER,
4040 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4041 x_return_status OUT NOCOPY VARCHAR2,
4042 x_msg_count OUT NOCOPY NUMBER,
4043 x_msg_data OUT NOCOPY VARCHAR2,
4044 p_doc_type IN VARCHAR2,
4045 p_template_id IN NUMBER,
4046 p_org_id IN NUMBER
4047 ) RETURN VARCHAR2 IS
4048
4049 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4050 l_result VARCHAR2(1) := 'N';
4051
4052 BEGIN
4053
4054 IF l_debug_level > 0 THEN
4055 oe_debug_pub.add('In OE_Contracts_util.Is_Terms_Template_Valid ', 1);
4056 END IF;
4057
4058 x_return_status := FND_API.G_RET_STS_SUCCESS;
4059
4060 IF p_init_msg_list = FND_API.G_TRUE THEN
4061 oe_msg_pub.initialize;
4062 END IF;
4063
4064 --Check for licensing
4065 IF OE_Contracts_util.check_license() <> 'Y' THEN
4066 IF l_debug_level > 0 THEN
4067 oe_debug_pub.add('Contractual option not licensed, hence exiting Is_Terms_Template_Valid ', 3);
4068 END IF;
4069 RETURN NULL;
4070 END IF;
4071
4072
4073 IF l_debug_level > 0 THEN
4074 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.Is_Terms_Template_Valid ', 3);
4075 oe_debug_pub.add('p_api_version: ' || p_api_version,3);
4076 oe_debug_pub.add('p_init_msg_list: ' || p_init_msg_list,3);
4077 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
4078 oe_debug_pub.add('p_template_id: ' || p_template_id, 3);
4079 END IF;
4080
4081 l_result := OKC_TERMS_UTIL_GRP.Is_Terms_Template_Valid (
4082 p_api_version => p_api_version,
4083 p_init_msg_list => p_init_msg_list,
4084 x_return_status => x_return_status,
4085 x_msg_data => x_msg_data,
4086 x_msg_count => x_msg_count,
4087 p_template_id => p_template_id,
4088 p_doc_type => p_doc_type,
4089 p_org_id => p_org_id,
4090 p_valid_date => SYSDATE
4091 );
4092
4093
4094 IF l_debug_level > 0 THEN
4095 oe_debug_pub.add('p_template_id: '|| p_template_id, 3);
4096 oe_debug_pub.add('x_return_status: '|| x_return_status, 3);
4097 oe_debug_pub.add(' l_result: '|| l_result, 3);
4098 END IF;
4099
4100 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4102 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4103 RAISE FND_API.G_EXC_ERROR ;
4104 END IF;
4105
4106 IF l_debug_level > 0 THEN
4107 oe_debug_pub.add('End of OE_Contracts_util.Is_Terms_Template_Valid , x_return_status ' || x_return_status, 1);
4108 END IF;
4109
4110 RETURN l_result;
4111
4112 EXCEPTION
4113 WHEN FND_API.G_EXC_ERROR THEN
4114
4115 IF l_debug_level > 0 THEN
4116 oe_debug_pub.add('WHEN G_EXC_ERROR in Is_Terms_Template_Valid ', 3);
4117 END IF;
4118
4119 x_return_status := FND_API.G_RET_STS_ERROR;
4120
4121 --Get message count and data
4122 OE_MSG_PUB.Count_And_Get (
4123 p_count => x_msg_count,
4124 p_data => x_msg_data
4125 );
4126
4127
4128 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4129
4130 IF l_debug_level > 0 THEN
4131 oe_debug_pub.add('WHEN G_EXC_UNEXPECTED_ERROR in Is_Terms_Template_Valid ', 3);
4132 END IF;
4133
4134 --close any cursors
4135
4136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4137
4138 --Get message count and data
4139 OE_MSG_PUB.Count_And_Get (
4140 p_count => x_msg_count,
4141 p_data => x_msg_data
4142 );
4143
4144
4145 WHEN OTHERS THEN
4146
4147 IF l_debug_level > 0 THEN
4148 oe_debug_pub.add('WHEN OTHERS in Is_Terms_Template_Valid ', 3);
4149 END IF;
4150
4151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4152
4153 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4154 OE_MSG_PUB.Add_Exc_Msg (
4155 G_PKG_NAME,
4156 'Is_Terms_Template_Valid'
4157 );
4158 END IF;
4159
4160
4161 --Get message count and data
4162 OE_MSG_PUB.Count_And_Get (
4163 p_count => x_msg_count,
4164 p_data => x_msg_data
4165 );
4166
4167 END Is_Terms_Template_Valid;
4168
4169
4170 --Function to check if the Authoring Party is Internal, required by Preview and Print
4171 Function Is_Auth_Party_Internal
4172 (
4173 p_doc_type IN VARCHAR2,
4174 p_doc_id IN NUMBER
4175 )
4176 RETURN VARCHAR2 IS
4177 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4178 l_authoring_party_code VARCHAR2(100);
4179 BEGIN
4180
4181 IF l_debug_level > 0 THEN
4182 oe_debug_pub.add('In OE_Contracts_util.Is_Auth_Party_Internal ', 1);
4183 END IF;
4184
4185 --Check for licensing
4186 IF OE_Contracts_util.check_license() <> 'Y' THEN
4187 IF l_debug_level > 0 THEN
4188 oe_debug_pub.add('Contractual option not licensed, hence exiting Is_Auth_Party_Internal ', 3);
4189 END IF;
4190
4191 RETURN 'N';
4192 END IF;
4193
4194 IF l_debug_level > 0 THEN
4195 oe_debug_pub.add('Calling OKC_TERMS_UTIL_GRP.Get_Authoring_Party_Code', 3);
4196 oe_debug_pub.add('p_doc_type: ' || p_doc_type, 3);
4197 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
4198 END IF;
4199
4200 l_authoring_party_code := OKC_TERMS_UTIL_GRP.Get_Authoring_Party_Code(
4201 p_document_type => p_doc_type,
4202 p_document_id => p_doc_id
4203 );
4204
4205 IF l_debug_level > 0 THEN
4206 oe_debug_pub.add('l_authoring_party_code '|| l_authoring_party_code, 3);
4207 END IF;
4208
4209 IF l_authoring_party_code = 'INTERNAL_ORG' THEN
4210 RETURN 'Y';
4211 ELSE
4212 RETURN 'N';
4213 END IF;
4214
4215 EXCEPTION
4216
4217 WHEN OTHERS THEN
4218
4219 IF l_debug_level > 0 THEN
4220 oe_debug_pub.add('WHEN-OTHERS in Is_Auth_Party_Internal ', 1);
4221 END IF;
4222
4223 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4224 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4225 'Is_Auth_Party_Internal'
4226 );
4227 END IF;
4228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4229
4230 END Is_Auth_Party_Internal;
4231
4232 --Function to check if Recurring Charges is Enabled, required by Preview and Print
4233 Function Is_RChg_Enabled
4234 (
4235 p_doc_id IN NUMBER
4236 )
4237 RETURN VARCHAR2 IS
4238 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4239 l_rch_enabled VARCHAR2(1) := 'N';
4240 l_org_id NUMBER := NULL;
4241
4242 BEGIN
4243
4244 IF l_debug_level > 0 THEN
4245 oe_debug_pub.add('In OE_Contracts_util.Is_RChg_Enabled ', 1);
4246 END IF;
4247 --Get the org_id
4248 BEGIN
4249 SELECT org_id into l_org_id
4250 FROM oe_order_headers_all
4251 WHERE header_id=p_doc_id;
4252 EXCEPTION
4253 when others then
4254 l_org_id:=NULL;
4255 END;
4256
4257 IF l_debug_level > 0 THEN
4258 oe_debug_pub.add('Calling OE_SYS_PARAMETER.VALUE(RECURRING_CHARGES)', 3);
4259 oe_debug_pub.add('p_doc_id: ' || p_doc_id, 3);
4260 oe_debug_pub.add('l_org_id: ' || l_org_id, 3);
4261 END IF;
4262
4263 IF l_org_id is not null then
4264 l_rch_enabled := nvl(OE_SYS_PARAMETERS.VALUE('RECURRING_CHARGES',l_org_id),'N');
4265 ELSE
4266 l_rch_enabled := 'N';
4267 END IF;
4268
4269 IF l_debug_level > 0 THEN
4270 oe_debug_pub.add('l_rch_enabled '|| l_rch_enabled, 3);
4271 END IF;
4272
4273 return l_rch_enabled;
4274
4275 EXCEPTION
4276
4277 WHEN OTHERS THEN
4278
4279 IF l_debug_level > 0 THEN
4280 oe_debug_pub.add('WHEN-OTHERS in Is_RChg_Enabled ', 1);
4281 END IF;
4282
4283 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4284 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4285 'Is_RChg_Enabled'
4286 );
4287 END IF;
4288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4289
4290 END Is_RChg_Enabled;
4291
4292 END OE_Contracts_util;