1 package body PO_CONTERMS_WF_PVT AS
2 /* $Header: POXVWCTB.pls 120.4 2006/08/25 22:38:26 bao noship $ */
3
4 --< CONTERMS FPJ Start>
5 -- Get profile option that enables/disables the debug log for workflow
6 g_po_wf_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
7 -- Read the profile option that enables/disables the debug log
8 g_fnd_debug CONSTANT VARCHAR2(1) := NVL (FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9
10 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_CONTEMRS_WF_PVT';
11 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.'||g_pkg_name||'.';
12
13 -------------------------------------------------------------------------------
14 --Start of Comments
15 --Name: show_error
16 --Pre-reqs:
17 -- None
18 --Modifies:
19 -- None
20 --Locks:
21 -- None
22 --Function:
23 -- Put messages in workflow debuf if contracts call failed
24 --Parameters:
25 --IN:
26 --itemtype
27 -- Standard workflow Parameter.
28 --itemkey
29 -- Standard workflow parameter
30 --p_api_name
31 -- Name of the Contracts API called
32 --p_return_status
33 -- Staus returned by called API
34 --Notes:
35 -- None
36 --Testing:
37 -- Test this API by failing contract API call
38 -- For more details refer to UT test scripts in DLD
39 --End of Comments
40 -------------------------------------------------------------------------------
41 PROCEDURE show_error (itemtype IN VARCHAR2,
42 itemkey IN VARCHAR2,
43 p_api_name IN VARCHAR2,
44 p_return_status IN VARCHAR2) IS
45
46 l_count number:= FND_MSG_PUB.Count_Msg;
47 BEGIN
48 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
49 '10: Start show error');
50 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
51 '20:Return status for '||p_api_name||':'||p_return_status);
52 FOR i IN 1..l_count LOOP
53 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
54 (20+i)||':Error-'||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
55 END LOOP;
56 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
57 '100: End show error');
58 END show_error;
59
60 -------------------------------------------------------------------------------
61 --Start of Comments
62 --Name: get_wf_params
63 --Pre-reqs:
64 -- None
65 --Modifies:
66 -- None
67 --Locks:
68 -- None
69 --Function:
70 -- Get values for attributes needed for contract call
71 --Parameters:
72 --IN:
73 --itemtype
74 -- Standard workflow Parameter.
75 --itemkey
76 -- Standard workflow parameter
77 --OUT:
78 --x_po_header_id
79 -- header id of the po being approved in this wf process
80 --x_po_doc_type
81 -- Main document type of the po being approved in this wf process
82 --x_po_doc_subtype
83 -- Sub document type of the po being approved in this wf process
84 --Notes:
85 -- None
86 --Testing:
87 -- Test this procedure by checking debug
88 -- For more details refer to UT test scripts in DLD
89 --End of Comments
90 -------------------------------------------------------------------------------
91 PROCEDURE get_wf_params(itemtype IN VARCHAR2,
92 itemkey IN VARCHAR2,
93 x_po_header_id OUT NOCOPY NUMBER,
94 x_po_doc_type OUT NOCOPY VARCHAR2,
95 x_po_doc_subtype OUT NOCOPY VARCHAR2) IS
96
97 BEGIN
98
99 IF (g_po_wf_debug = 'Y') THEN
100 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
101 '10:Start get_wf_params ');
102 END IF;
103 x_po_header_id := PO_wf_Util_Pkg.GetItemAttrNumber(
104 itemtype => itemtype,
105 itemkey => itemkey,
106 aname => 'DOCUMENT_ID');
107
108 x_po_doc_type := PO_wf_Util_Pkg.GetItemAttrText(
109 itemtype => itemtype,
110 itemkey => itemkey,
111 aname => 'DOCUMENT_TYPE');
112
113 x_po_doc_subtype := PO_wf_Util_Pkg.GetItemAttrText(
114 itemtype => itemtype,
115 itemkey => itemkey,
116 aname => 'DOCUMENT_SUBTYPE');
117
118 IF (g_po_wf_debug = 'Y') THEN
119 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
120 '30:po_header_id = '|| to_char(x_po_header_id));
121 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
122 '40:po doc type= '|| x_po_doc_type);
123 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
124 '50:po sub type= '|| x_po_doc_subtype);
125 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
126 '100:End get_wf_params ');
127 END IF;
128 END get_wf_params;
129
130 -------------------------------------------------------------------------------
131 --Start of Comments
132 --Name: Get_DELIVERABLE_EVENTS
133 --Pre-reqs:
134 -- None
135 --Modifies:
136 -- None
137 --Locks:
138 -- None
139 --Function:
140 -- Returns the deliverable date based event codes and their dates on sent in po-header_id
141 --Parameters:
142 --IN:
143 --p_po_header_id
144 -- Header id of the PO
145 --p_action_code
146 -- action for which event codes are needed.
147 -- 'U'- Action code is update deliverables
148 -- 'A'- Action code is activate deliverables.Called from here(Update Contract Terms) and QA
149 --OUT:
150 --x_event_tbl
151 -- the event table code
152 --Notes:
153 -- None
154 --Testing:
155 -- For more details refer to UT test scripts in DLD
156 --End of Comments
157 -------------------------------------------------------------------------------
158 PROCEDURE Get_DELIVERABLE_EVENTS (p_po_header_id IN NUMBER,
159 p_action_code IN VARCHAR2,
160 p_doc_subtype IN VARCHAR2,
161 x_event_tbl OUT NOCOPY EVENT_TBL_TYPE) IS
162
163 l_po_revision_num PO_HEADERS_ALL.REVISION_NUM%TYPE;
164 l_po_start_date PO_HEADERS_ALL.START_DATE%TYPE;
165 l_po_end_date PO_HEADERS_ALL.END_DATE%TYPE;
166 l_archive_start_date PO_HEADERS_ALL.START_DATE%TYPE;
167 l_archive_end_date PO_HEADERS_ALL.END_DATE%TYPE;
168
169 BEGIN
170 IF g_fnd_debug = 'Y' then
171 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
172 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
173 MODULE =>g_module_prefix||'Get_DELIVERABLE_EVENTS',
174 MESSAGE =>'10: Start: Get_DELIVERABLE_EVENTS');
175 END IF;
176 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
177 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
178 MODULE =>g_module_prefix||'Get_DELIVERABLE_EVENTS',
179 MESSAGE =>'15: Action Code: Doc subtype'||p_action_code||': '||p_doc_subtype);
180 END IF;
181 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
182 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
183 MODULE =>g_module_prefix||'Get_DELIVERABLE_EVENTS',
184 MESSAGE =>'20: Count in Event table'||x_event_tbl.count);
185 END IF;
186
187 END IF;
188 -- The event codes and dates will be sent only for BPA or CPA
189 -- and not for SPO as currently there are no date based events
190 -- for SPO seeded. Change the if below , in case this changes
191 IF (p_doc_subtype IN ('BLANKET','CONTRACT') ) THEN
192 --SQL WHAT: Selects items needed to call contracts events
193 --SQL WHY: These values are used in deciding activation and update
194 -- of contract deliverables
195 --SQl Join:None
196 SELECT start_date,
197 end_date,
198 revision_num
199 INTO
200 l_po_start_date,
201 l_po_end_date,
202 l_po_revision_num
203
204 FROM po_headers_all
205 WHERE po_header_id = p_po_header_id;
206
207 IF g_fnd_debug = 'Y' then
208 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
209 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
210 MODULE =>g_module_prefix||'Get_DELIVERABLE_EVENTS',
211 MESSAGE =>'30: After Select.po found');
212 END IF;
213
214 END IF;
215
216 IF (p_action_code = 'A') then --if call is for activation of deliverables
217 x_event_tbl(1).event_code := 'PO_START_DATE';
218 x_event_tbl(1).event_date := l_po_start_date;
219 x_event_tbl(2).event_code := 'PO_END_DATE';
220 x_event_tbl(2).event_date := l_po_end_date;
221
222
223 ELSIF (p_action_code = 'U') then -- If call is for update of deliverables
224 --SQL WHAT: Selects start date and end date from archive table for
225 -- last but one archival since this is always called after
226 -- archive of PO, the latest will have same value as working copy
227 --SQL WHY: These values are used to send changed dates for update deliverables
228 --SQl Join:None
229 SELECT start_date,
230 end_date
231 INTO
232 l_archive_start_date,
233 l_archive_end_date
234 FROM po_headers_archive_all
235 WHERE po_header_id = p_po_header_id
236 AND revision_num = (l_po_revision_num-1);
237
238 -- If start date changed since last revision, then
239 -- send the event code and date to update Deliverables
240 IF (nvl(l_archive_start_date,FND_API.G_MISS_DATE) <>
241 nvl(l_po_start_date,FND_API.G_MISS_DATE)) THEN
242
243 x_event_tbl(1).event_code := 'PO_START_DATE';
244 x_event_tbl(1).event_date := l_po_start_date;
245 END IF;
246 -- If end date changed since last revision, then
247 -- send the event code and date to update Deliverables
248 IF (nvl(l_archive_end_date,FND_API.G_MISS_DATE) <>
249 nvl(l_po_end_date,FND_API.G_MISS_DATE)) THEN
250
251 x_event_tbl(2).event_code := 'PO_END_DATE';
252 x_event_tbl(2).event_date := l_po_end_date;
253 END IF;
254 END IF;--action_code=A or U
255 END IF;--doc subtype code
256
257 IF g_fnd_debug = 'Y' then
258 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
259 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
260 MODULE =>g_module_prefix||'Get_DELIVERABLE_EVENTS',
261 MESSAGE =>'90: Count in Event table'||x_event_tbl.count);
262 END IF;
263 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
264 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
265 MODULE =>g_module_prefix||'Get_DELIVERABLE_EVENTS',
266 MESSAGE =>'100: End Get_DELIVERABLE_EVENTS');
267 END IF;
268
269 END IF;
270 END GET_DELIVERABLE_EVENTS;
271 -------------------------------------------------------------------------------
272 --Start of Comments
273 --Name: CONTRACT_TERMS_CHANGED
274 --Pre-reqs:
275 -- Contracts package stubs should be there
276 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
277 --Modifies:
278 -- None
279 --Locks:
280 -- None
281 --Function:
282 -- This API will be called by approval workflow to determine if
283 -- Contract terms have changed or not in this revision
284 --Parameters:
285 --IN:
286 --itemtype
287 -- Standard workflow Parameter.
288 --itemkey
289 -- Standard workflow parameter
290 --Return:
291 -- Y- Yes Contract terms were changed in this revision
292 -- N- No Contract terms were not changed in this revision
293 --Notes:
294 -- None
295 --Testing:
296 -- Test this API by Changing contract terms, by not changing Contract terms
297 -- and for POs which are not Procurement Contract.
298 -- For more details refer to UT test scripts in DLD
299 --End of Comments
300 -------------------------------------------------------------------------------
301 FUNCTION CONTRACT_TERMS_CHANGED(itemtype in varchar2,
302 Itemkey IN VARCHAR2)
303
304 return VARCHAR2 IS
305 l_changed VARCHAR2(1) := 'N';
306 l_k_terms_changed VARCHAR2(30);
307 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
308
309 l_return_status VARCHAR2(1);
310 l_msg_data VARCHAR2(2000);
311 l_msg_count NUMBER;
312
313 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
314 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
315 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
316 l_archived_conterms_flag PO_headers_all.conterms_exist_Flag%Type :='N';
317
318 l_contracts_call_exception exception;
319 l_api_name VARCHAR2(100);
320 BEGIN
321 IF (g_po_wf_debug = 'Y') THEN
322 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
323 '10: Start function contract_terms_Changed ');
324 END IF;
325
326
327 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
328 itemtype => itemtype,
329 itemkey => itemkey,
330 aname => 'CONTERMS_EXIST_FLAG');
331
332 -- get other needed values from attribs
333 get_wf_params(itemtype =>itemtype,
334 itemkey =>itemkey,
335 x_po_header_id =>l_po_header_id,
336 x_po_doc_type =>l_po_doc_type,
337 x_po_doc_subtype =>l_po_doc_subtype);
338
339 -- Migrate PO
340 -- Now that conterms can be added at any revision of the PO
341 -- We need to check if the conterms flag has changed before
342 -- checking the contract amendments
343
344 l_archived_conterms_flag := PO_CONTERMS_UTL_GRP.get_archive_conterms_flag (
345 p_po_header_id => l_po_header_id);
346
347 IF nvl(l_conterms_yn,'N') = 'Y' AND
348 nvl(l_archived_conterms_flag,'N') = 'N' THEN
349
350 IF (g_po_wf_debug = 'Y') THEN
351 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
352 '120:Previous version does not have terms');
353 END IF;
354 l_changed:='Y';
355
356 ELSIF (l_conterms_yn = 'Y') then
357
358 -- Call contracts to find out if contract terms changed
359 -- Bug 4100563: OKC has provided a new API contract_terms_amended to check if
360 -- the primary contract document has changed. We are
361 -- calling this new API as an additional check here.
362 -- Start bug 4100563
363 l_api_name := 'OKC_TERMS_UTIL_GRP.CONTRACT_TERMS_AMENDED';
364
365 IF (g_po_wf_debug = 'Y') THEN
366 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
367 '130: Return status before the call'||l_return_status);
368 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
369 '132: Call OKC_TERMS_UTIL_GRP.contract_terms_amended');
370 END IF;
371
372 l_k_terms_changed := OKC_TERMS_UTIL_GRP.CONTRACT_TERMS_AMENDED(
373 p_api_version => 1.0,
374 p_doc_id => l_po_header_id,
375 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
376 x_return_status => l_return_status,
377 x_msg_data => l_msg_data,
378 x_msg_count => l_msg_count);
379
380 IF (g_po_wf_debug = 'Y') THEN
381 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
382 '135: Return status after call to CONTRACT_TERMS_AMENDED'||l_return_status);
383
384 END IF;
385
386 -- Check l_return_status for CONTRACT_TERMS_AMENDED
387 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
388
389 -- Check return value from CONTRACT_TERMS_AMENDED
390 IF (l_k_terms_changed = 'NONE') THEN
391 IF (g_po_wf_debug = 'Y') THEN
392 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
393 '138: K contract terms changed: N'||l_k_terms_changed);
394 END IF;
395
396 --End Bug 4100563
397
398 --Call contracts to find out if contract terms changed
399 l_api_name := 'OKC_TERMS_UTIl_GRP.IS_ARTICLE_AMENDED';
400 IF (g_po_wf_debug = 'Y') THEN
401 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
402 '140: Return status Before the call'||l_return_status);
403 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
404 '142: Call OKC_TERMS_UTIl_GRP.IS_ARTICLE_AMENDED');
405 END IF;
406
407 l_k_terms_changed :=OKC_TERMS_UTIl_GRP.IS_ARTICLE_AMENDED(
408 p_api_version => 1.0,
409 p_doc_id => l_po_header_id,
410 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
411 x_return_status => l_return_status,
412 x_msg_data => l_msg_data,
413 x_msg_count => l_msg_count);
414
415 IF (g_po_wf_debug = 'Y') THEN
416 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
417 '150: Return status after call to IS_ARTICLE_AMENDED'||l_return_status);
418
419 END IF;
420
421 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
422
423 IF (l_k_terms_changed = 'NONE') THEN
424 IF (g_po_wf_debug = 'Y') THEN
425 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
426 '155: K Articles changed: N'||l_k_terms_changed);
427 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
428 '160: Call OKC_TERMS_UTIl_GRP.Is_Deliverable_Amended');
429 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
430 '162: Return status Before the call'||l_return_status);
431 END IF;
432
433 l_api_name := 'OKC_TERMS_UTIl_GRP.Is_Deliverable_Amended';
434 l_k_terms_changed :=OKC_TERMS_UTIl_GRP.Is_Deliverable_Amended(
435 p_api_version => 1.0,
436 p_doc_id => l_po_header_id,
437 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
438 x_return_status => l_return_status,
439 x_msg_data => l_msg_data,
440 x_msg_count => l_msg_count);
441
442 IF (g_po_wf_debug = 'Y') THEN
443 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
444 '165: Return status after call to Is_Deliverable_Amended'||l_return_status);
445
446 END IF;
447
448 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
449 IF (l_k_terms_changed = 'NONE') THEN
450 IF (g_po_wf_debug = 'Y') THEN
451 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
452 '170: K Deliverables changed: N'||l_k_terms_changed);
453 END IF;
454 l_changed:='N';
455
456 ELSE -- if deliverables changed
457 IF (g_po_wf_debug = 'Y') THEN
458 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
459 '175:K deliverables Changed: Y'||l_k_terms_changed);
460 END IF;
461 l_changed:='Y';
462
463 END IF; -- if deliverables changed
464 ELSE
465 RAISE l_Contracts_call_exception;
466 END IF; -- Return status for is_deliverables_amended
467
468 ELSE -- if articles changed
469 IF (g_po_wf_debug = 'Y') THEN
470 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
471 '180:K Terms Changed: Y'||l_k_terms_changed);
472 END IF;
473 l_changed:='Y';
474
475 END IF; -- if articles changed
476
477 ELSE
478 RAISE l_Contracts_call_exception;
479 END IF; -- Return status for is_articles_amended
480
481
482 -- Start bug 4100563
483 ELSE -- if contract terms changed
484
485 IF (g_po_wf_debug = 'Y') THEN
486 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
487 '190:K Contract terms Changed: Y'||l_k_terms_changed);
488 END IF;
489 l_changed:='Y';
490
491 END IF; -- if contract terms changed
492 ELSE
493 RAISE l_Contracts_call_exception;
494 END IF; -- Return status for contract_terms_amended
495
496 -- End bug 4100563
497
498 ELSE -- if no conterms
499 IF (g_po_wf_debug = 'Y') THEN
500 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
501 '120:Not a Procurement contract');
502 END IF;
503 l_changed:='N';
504 END IF; -- if conterms exist
505
506 IF (g_po_wf_debug = 'Y') THEN
507 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
508 '200 End: contract_terms_Changed ');
509 END IF;
510
511
512 return(l_changed);
513
514 EXCEPTION
515 -- Handle Exceptions and re raise
516 WHEN l_contracts_call_exception then
517 IF (g_po_wf_debug = 'Y') THEN
518 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
519 '250: End contracts_call_exception: contract_terms_Changed ');
520
521 show_error(itemtype => itemtype,
522 itemkey => itemkey,
523 p_api_name => l_api_name,
524 p_return_status => l_return_status);
525 END IF;
526 wf_core.context('PO_CONTERMS_WF_PVT', 'CONTRACT_TERMS_CHANGED', 'l_contracts_call_Exception');
527 RAISE;
528 WHEN OTHERS THEN
529 IF (g_po_wf_debug = 'Y') THEN
530 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
531 '300: End IN Exception: contract_terms_Changed ');
532 END IF;
533 wf_core.context('PO_CONTERMS_WF_PVT', 'CONTRACT_TERMS_CHANGED', 'Exception');
534 RAISE;
535
536 END CONTRACT_TERMS_CHANGED;
537
538 -------------------------------------------------------------------------------
539 --Start of Comments
540 --Name: UPDATE_CONTERMS_DATES
541 --Pre-reqs:
542 -- Contracts package stubs should be there
543 -- popo.odf 115.54
544 --Modifies:
545 -- None
546 --Locks:
547 -- None
548 --Function:
549 -- returns the last update date for deliverables and articles
550 -- These dates need to be synced up when PO is approved and cannot
551 -- be changed for current revision
552 --Parameters:
553 --IN:
554 --p_po_header_id
555 -- po_header_id of the po
556 --p_po_doc_type
557 -- Document type of the PO (PO/PA)
558 --p_po_doc_subtype
559 -- Document subtype- (STANDARD,BLANKET,CONTRACT)
560 --p_conterms_exist_flag
561 -- If this po is a procurement contract
562 --OUT:
563 --x_return_status
564 -- Return status of the call
565 --X_msg_data
566 -- error message from Contract if x_return_status is not S
567 --x_msg_count
568 -- Number of error messages returned
569 --Notes:
570 -- None
571 --Testing:
572 -- For details refer to UT test scripts in DLD
573 --End of Comments
574 -------------------------------------------------------------------------------
575 PROCEDURE UPDATE_CONTERMS_DATES(p_po_header_id IN NUMBER,
576 p_po_doc_type IN VARCHAR2,
577 p_po_doc_subtype IN VARCHAR2,
578 p_conterms_exist_flag IN VARCHAR2,
579 x_return_status OUT NOCOPY VARCHAR2,
580 x_msg_data OUT NOCOPY VARCHAR2,
581 x_msg_count OUT NOCOPY NUMBER
582 ) IS
583
584 l_articles_upd_date DATE;
585 l_deliv_upd_date DATE;
586
587 l_k_api_name VARCHAR2(60);
588 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_CONTERMS_DATES';
589
590 l_Contracts_call_exception EXCEPTION;
591 BEGIN
592 IF g_fnd_debug = 'Y' then
593 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
594 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
595 MODULE =>g_module_prefix||l_api_name,
596 MESSAGE =>'10: Start UPDATE_CONTERMS_DATES');
597 END IF;
598 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
599 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
600 MODULE =>g_module_prefix||l_api_name,
601 MESSAGE =>'15: p_conterms_exist_flag'||p_conterms_exist_flag);
602 END IF;
603 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
604 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
605 MODULE =>g_module_prefix||l_api_name,
606 MESSAGE =>'18: p_po_doc_type '||p_po_doc_type );
607 END IF;
608 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
609 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
610 MODULE =>g_module_prefix||l_api_name,
611 MESSAGE =>'20: p_po_doc_subtype'||p_po_doc_subtype);
612 END IF;
613 End if;
614 IF p_conterms_exist_flag = 'Y' then
615 IF g_fnd_debug = 'Y' then
616 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
617 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
618 MODULE =>g_module_prefix||l_api_name,
619 MESSAGE =>'50: It is a procurement Contract');
620 END IF;
621 End if;
622 l_k_api_name:='OKC_TERMS_UTIl_GRP.Get_Last_Update_Date';
623 IF g_fnd_debug = 'Y' then
624 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
625 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
626 MODULE =>g_module_prefix||l_api_name,
627 MESSAGE =>'70: Before call to OKC_TERMS_UTIl_GRP.Get_Last_Update_Date');
628 END IF;
629 End if;
630 OKC_TERMS_UTIl_GRP.Get_Last_Update_Date(
631 p_api_version => 1.0,
632 p_doc_id => p_po_header_id,
633 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(p_po_doc_subtype),
634 x_deliverable_changed_date => l_deliv_upd_date,
635 x_terms_changed_date => l_articles_upd_date,
636 x_return_status => x_return_status,
637 x_msg_data => x_msg_data,
638 x_msg_count => x_msg_count);
639 IF g_fnd_debug = 'Y' then
640 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
641 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
642 MODULE =>g_module_prefix||l_api_name,
643 MESSAGE =>'80: After call to OKC_...Get_Last_Update_Date. Status'||x_return_status);
644 END IF;
645 End if;
646 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
647 RAISE l_Contracts_call_exception;
648 END IF; -- Return status from contracts
649 ELSE
650 -- There might be some value in these populated when user clicked the
651 -- Author button in forms but might have never actually attached a template
652 -- So null these fields as they make sense only for a procurement contract
653 l_articles_upd_date := null;
654 l_deliv_upd_date := null;
655 END IF;
656
657 IF g_fnd_debug = 'Y' then
658 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
659 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
660 MODULE =>g_module_prefix||l_api_name,
661 MESSAGE =>'100: Articles Upd Date'||l_articles_upd_date);
662 END IF;
663 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
664 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
665 MODULE =>g_module_prefix||l_api_name,
666 MESSAGE =>'100: Deliverables Upd Date'||l_deliv_upd_date);
667 END IF;
668 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
669 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
670 MODULE =>g_module_prefix||l_api_name,
671 MESSAGE =>'120: Before update of dates in po headers'||l_deliv_upd_date);
672 END IF;
673 END IF;
674 -- SQL What:Updates PO_HEADERS_ALL table and sets the contract terms dates
675 -- SQL Why :After PO is Approved, sync up contract terms dates.
676 -- SQL Join:none
677 UPDATE PO_HEADERS_ALL
678 SET conterms_articles_upd_date = l_articles_upd_date,
679 conterms_deliv_upd_date = l_deliv_upd_date,
680 last_updated_by = FND_GLOBAL.user_id,
681 last_update_login = FND_GLOBAL.login_id,
682 last_update_date = sysdate
683 WHERE po_header_id = p_po_header_id;
684 IF g_fnd_debug = 'Y' then
685 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
686 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
687 MODULE =>g_module_prefix||l_api_name,
688 MESSAGE =>'200: End: UPDATE_CONTERMS_DATES');
689 END IF;
690
691 END IF;
692
693
694 EXCEPTION
695 WHEN l_Contracts_call_exception then
696 IF g_fnd_debug = 'Y' then
697 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
698 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
699 MODULE =>g_module_prefix||l_api_name,
700 MESSAGE =>'300: End Update_conTerms_dates.In Exception l_Contracts_call_exception');
701 END IF;
702
703 END IF;
704 -- Show one error message atleast
705 IF x_msg_data is null and FND_MSG_PUB.Count_Msg >0 then
706 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
707 ELSE
708 Fnd_message.set_name('PO','PO_API_ERROR');
709 Fnd_message.set_token( token => 'PROC_CALLER'
710 , VALUE => 'PO_CONTERMS_WF_PVT.UPDATE_CONTERMS_DATES');
711 Fnd_message.set_token( token => 'PROC_CALLED'
712 , VALUE => l_k_api_name);
713 FND_MSG_PUB.Add;
714 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
715
716 END IF;
717 IF g_fnd_debug = 'Y' then
718 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
719 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
720 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
721 MODULE =>g_module_prefix||l_api_name,
722 MESSAGE =>':Errors in stack-'||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
723 END IF;
724 END LOOP;
725
726 END IF;
727 WHEN OTHERS THEN
728 IF g_fnd_debug = 'Y' then
729 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
730 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
731 MODULE =>g_module_prefix||l_api_name,
732 MESSAGE =>'400: End Update_conTerms_dates.In Exception OTHERS');
733 END IF;
734
735 END IF;
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 IF FND_MSG_PUB.Check_Msg_Level
738 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
739 THEN
740 FND_MSG_PUB.Add_Exc_Msg
741 (p_pkg_name => 'PO_CONTERMS_WF_PVT',
742 p_procedure_name =>'UPDATE_CONTERMS_DATES');
743
744 END IF; --msg level
745 FND_MSG_PUB.Count_And_Get
746 (p_count => x_msg_count,
747 p_data => x_msg_data );
748 -- show one error message at least
749 IF x_msg_data is null and FND_MSG_PUB.Count_Msg >0 then
750 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
751 IF x_msg_data is null then
752 x_msg_data := SQLCODE||':'||SQLERRM;
753 END IF;
754 END IF;
755 IF g_fnd_debug = 'Y' then
756 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
757 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
758 MODULE =>g_module_prefix||l_api_name,
759 MESSAGE =>'410: x_msg_data:'||x_msg_data);
760 END IF;
761 END IF;
762 END UPDATE_CONTERMS_DATES;
763
764 -------------------------------------------------------------------------------
765 --Start of Comments
766 --Name: UPDATE_CONTRACT_TERMS
767 --Pre-reqs:
768 -- Contracts package stubs should be there
769 -- popo.odf 115.54
770 --Modifies:
771 -- None
772 --Locks:
773 -- None
774 --Function:
775 -- This API informs Contracts about signing/approval of new doc revision
776 -- This API will be called from PO archival, po acceptances
777 -- Also. this API is called from po_signature_pvt.update_po_details
778 --Parameters:
779 --IN:
780 --p_po_header_id
781 -- po_header_id of the po
782 --p_po_signed_date
783 -- Date PO is signed
784 --OUT:
785 --x_return_status
786 -- Return status of the call
787 --X_msg_data
788 -- error message from Contract if x_return_status is not S
789 --x_msg_count
790 -- Number of error messages returned
791 --Notes:
792 -- None
793 --Testing:
794 -- For details refer to UT test scripts in DLD
795 --End of Comments
796 -------------------------------------------------------------------------------
797 PROCEDURE UPDATE_CONTRACT_TERMS(p_po_header_id IN NUMBER,
798 p_signed_date IN DATE,
799 x_return_status OUT NOCOPY VARCHAR2,
800 x_msg_data OUT NOCOPY VARCHAR2,
801 x_msg_count OUT NOCOPY NUMBER) IS
802
803 l_conterms_exist_flag PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
804 l_po_doc_subtype PO_DOCUMENT_TYPES_ALL_B.DOCUMENT_SUBTYPE%TYPE;
805 l_po_revision_num PO_HEADERS_ALL.REVISION_NUM%TYPE;
806 l_event_tbl EVENT_TBL_TYPE;
807 l_k_api_name VARCHAR2(100);
808 l_cancel_flag PO_HEADERS_ALL.CANCEL_FLAG%TYPE;
809 l_po_doc_type VARCHAR2(2);
810
811 l_i BINARY_INTEGER;
812 l_Contracts_call_exception EXCEPTION;
813 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_CONTRACT_TERMS';
814
815 -- Bug 3652222 START
816 l_last_signed_revision PO_HEADERS_ALL.REVISION_NUM%TYPE;
817 l_signed_records VARCHAR2(1);
818 -- Bug 3652222 END
819 BEGIN
820 IF g_fnd_debug = 'Y' then
821 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
822 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
823 MODULE =>g_module_prefix||l_api_name,
824 MESSAGE =>'10: Start: UPDATE_CONTRACT_TERMS');
825 END IF;
826
827 END IF;
828 --SQL WHAT: Selects items needed to call contracts API
829 --SQL WHY: These values are used in deciding activation and update
830 -- of contract deliverables
831 --SQl Join:None
832 SELECT conterms_exist_flag,
833 type_lookup_code,
834 revision_num,
835 cancel_flag,
836 DECODE(type_lookup_code, 'STANDARD', 'PO', 'BLANKET', 'PA', 'CONTRACT', 'PA', NULL)
837 INTO l_conterms_exist_flag,
838 l_po_doc_subtype,
839 l_po_revision_num,
840 l_cancel_flag,
841 l_po_doc_type
842 FROM po_headers_all
843 WHERE po_header_id = p_po_header_id;
844
845 IF g_fnd_debug = 'Y' then
846 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
847 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
848 MODULE =>g_module_prefix||l_api_name,
849 MESSAGE =>'30: After Select. Conterms Exist'||l_conterms_exist_flag);
850 END IF;
851
852 END IF;
853 IF l_conterms_exist_flag = 'Y' then
854
855 IF g_fnd_debug = 'Y' then
856 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
857 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
858 MODULE =>g_module_prefix||l_api_name,
859 MESSAGE =>'50: Doc type'||l_po_doc_subtype);
860 END IF;
861 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
862 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
863 MODULE =>g_module_prefix||l_api_name,
864 MESSAGE =>'51: po headerid'||p_po_header_id);
865 END IF;
866 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
867 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
868 MODULE =>g_module_prefix||l_api_name,
869 MESSAGE =>'54: p_po_revision_num'||l_po_revision_num);
870 END IF;
871 END IF;
872
873 -- activate deliverables created in this revision
874 Get_DELIVERABLE_EVENTS(p_po_header_id => p_po_header_id,
875 p_action_code => 'A',
876 p_doc_subtype => l_po_doc_subtype,
877 x_event_tbl => l_event_tbl);
878 l_k_api_name:='OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables';
879 IF g_fnd_debug = 'Y' then
880 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
881 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
882 MODULE =>g_module_prefix||l_api_name,
883 MESSAGE =>'60: event codes passed for OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables');
884 END IF;
885 IF (l_event_tbl.count>0) THEN
886 FOR l_event in l_event_tbl.FIRST..l_event_tbl.LAST LOOP
887 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
888 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
889 MODULE =>g_module_prefix||l_api_name,
890 MESSAGE =>'event_code'||l_event||' '||l_event_tbl(l_event).event_code
891 ||l_event_tbl(l_event).event_date);
892 END IF;
893
894 END LOOP;
895 END IF;--(l_event_tbl.count>0)
896 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
897 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
898 MODULE =>g_module_prefix||l_api_name,
899 MESSAGE =>'70: Before call to OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables');
900 END IF;
901
902 END IF;--debug on
903 OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables (
904 p_api_version => 1.0,
905 p_bus_doc_id => p_po_header_id,
906 p_bus_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
907 p_bus_doc_version => l_po_revision_num,
908 p_event_code => 'PO_SIGNED',
909 p_event_date => p_signed_date,
910 p_sync_flag => FND_API.G_TRUE,
911 p_bus_doc_date_events_tbl => l_event_tbl,
912 x_msg_data => x_msg_data,
913 x_msg_count => x_msg_count,
914 x_return_status => x_return_status);
915 IF g_fnd_debug = 'Y' then
916 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
917 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
918 MODULE =>g_module_prefix||l_api_name,
919 MESSAGE =>'80: After call to OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables');
920 END IF;
921 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
922 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
923 MODULE =>g_module_prefix||l_api_name,
924 MESSAGE =>'90: return status'||x_return_status);
925 END IF;
926
927 END IF;
928 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
929 RAISE l_Contracts_call_exception;
930 END IF; -- Return status from contracts
931 IF (l_po_revision_num > 0) then --Reresolution will only happen if revision num is greater than 0
932 -- update resolved deliverables with changed date
933 -- Since already resolved deliverables for last revision's
934 -- signed date should not be reresolved
935 -- We should just update the deliverables based on po start or end date
936 Get_DELIVERABLE_EVENTS(p_po_header_id => p_po_header_id,
937 p_action_code => 'U',
938 p_doc_subtype => l_po_doc_subtype,
939 x_event_tbl => l_event_tbl);
940 l_k_api_name:='OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables';
941 IF g_fnd_debug = 'Y' then
942 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
943 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
944 MODULE =>g_module_prefix||l_api_name,
945 MESSAGE =>'100:Count-event codes passed for OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables:'||l_event_tbl.count);
946 END IF;
947 END IF;
948 IF (l_event_tbl.count>0) THEN
949 IF g_fnd_debug = 'Y' then
950 FOR l_event in l_event_tbl.FIRST..l_event_tbl.LAST LOOP
951 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
952 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
953 MODULE =>g_module_prefix||l_api_name,
954 MESSAGE =>'event_code'||l_event||' '||l_event_tbl(l_event).event_code
955 ||l_event_tbl(l_event).event_date);
956 END IF;
957
958 END LOOP;
959 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
960 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
961 MODULE =>g_module_prefix||l_api_name,
962 MESSAGE =>'110: Before call to OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables');
963 END IF;
964
965 END IF;-- fnd debug
966 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables (
967 p_api_version => 1.0,
968 p_bus_doc_id => p_po_header_id,
969 p_bus_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
970 p_bus_doc_version => l_po_revision_num,
971 p_bus_doc_date_events_tbl => l_event_tbl,
972 x_msg_data => x_msg_data,
973 x_msg_count => x_msg_count,
974 x_return_status => x_return_status);
975 IF g_fnd_debug = 'Y' then
976 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
977 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
978 MODULE =>g_module_prefix||l_api_name,
979 MESSAGE =>'120: After call to OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables');
980 END IF;
981 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
982 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
983 MODULE =>g_module_prefix||l_api_name,
984 MESSAGE =>'130: Return Status'|| x_return_status);
985 END IF;
986
987 END IF;--debug
988 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
989 RAISE l_Contracts_call_exception;
990 END IF; -- Return status from contracts
991 END IF;--l_event_tbl.count>0
992
993 -- Bug 3652222 START
994 IF g_fnd_debug = 'Y' then
995 PO_DEBUG.debug_stmt(g_module_prefix||l_api_name, '133',
996 'Before call Get_Last_Signed_Revision');
997 END IF;
998
999 -- Migrate PO:
1000 -- Replaced the pvt api with the grp one as this one checks if the
1001 -- previous revision has conterms in the first place as this is possible
1002 -- now with migrate PO
1003 PO_CONTERMS_UTL_GRP.Get_Last_Signed_Revision(
1004 p_api_version => 1.0,
1005 p_init_msg_list => FND_API.G_FALSE,
1006 p_header_id => p_po_header_id,
1007 p_revision_num => l_po_revision_num,
1008 x_signed_revision_num => l_last_signed_revision,
1009 x_signed_records => l_signed_records,
1010 x_return_status => x_return_status,
1011 x_msg_data => x_msg_data,
1012 x_msg_count => x_msg_count);
1013
1014 IF g_fnd_debug = 'Y' then
1015 PO_DEBUG.debug_stmt(g_module_prefix||l_api_name, '135',
1016 'l_last_signed_revision: ' || l_last_signed_revision ||
1017 ', l_signed_records: ' || l_signed_records);
1018 END IF;
1019
1020 IF (l_signed_records = 'Y' AND l_last_signed_revision >= 0) THEN
1021 -- Bug 3652222 END
1022
1023 -- Disable the deliverables attached to previous revision of PO
1024 IF g_fnd_debug = 'Y' then
1025 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1026 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1027 MODULE =>g_module_prefix||l_api_name,
1028 MESSAGE =>'140: Before call to OKC_MANAGE_DELIVERABLES_GRP.DisableDeliverables');
1029 END IF;
1030
1031 END IF;
1032
1033 OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables (
1034 p_api_version => 1.0,
1035 p_bus_doc_id => p_po_header_id,
1036 p_bus_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1037 -- Bug 3652222, should pass last signed revision
1038 -- p_bus_doc_version => (l_po_revision_num -1),
1039 p_bus_doc_version => l_last_signed_revision,
1040 x_msg_data => x_msg_data,
1041 x_msg_count => x_msg_count,
1042 x_return_status => x_return_status);
1043 IF g_fnd_debug = 'Y' then
1044 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1045 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1046 MODULE =>g_module_prefix||l_api_name,
1047 MESSAGE =>'150: After call to OKC_MANAGE_DELIVERABLES_GRP.DisableDeliverables');
1048 END IF;
1049 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1050 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1051 MODULE =>g_module_prefix||l_api_name,
1052 MESSAGE =>'170: return status'||x_return_status);
1053 END IF;
1054
1055 END IF;
1056 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
1057 RAISE l_Contracts_call_exception;
1058 END IF; -- Return status from contracts
1059
1060 -- Bug 3652222 START
1061 END IF; /* IF (l_signed_records = 'Y' AND l_last_signed_revision >= 0) */
1062 -- Bug 3652222 END
1063
1064 -- cancel deliverables only if po is being archived after cancel
1065 IF (UPPER(NVL(l_cancel_flag, 'N'))='Y') THEN
1066
1067 IF g_fnd_debug = 'Y' then
1068 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1069 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1070 MODULE =>g_module_prefix||l_api_name,
1071 MESSAGE =>'180: Before call to wrapper procedure to Cancel Deliverables');
1072 END IF;
1073
1074 END IF;
1075
1076 cancel_deliverables(p_bus_doc_id => p_po_header_id
1077 ,p_bus_doc_type => l_po_doc_type
1078 ,p_bus_doc_subtype => l_po_doc_subtype
1079 ,p_bus_doc_version => l_po_revision_num
1080 ,p_event_code => 'PO_CANCEL'
1081 ,p_event_date => SYSDATE
1082 ,p_busdocdates_tbl => l_event_tbl
1083 ,x_return_status => x_return_status);
1084 IF g_fnd_debug = 'Y' then
1085 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1086 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1087 MODULE =>g_module_prefix||l_api_name,
1088 MESSAGE =>'190: After call to wrapper procedure to Cancel Deliverables');
1089 END IF;
1090
1091 END IF;
1092
1093 IF g_fnd_debug = 'Y' then
1094 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1095 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1096 MODULE =>g_module_prefix||l_api_name,
1097 MESSAGE =>'200: return status '||x_return_status);
1098 END IF;
1099
1100 END IF;
1101 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
1102 RAISE l_Contracts_call_exception;
1103 END IF; -- Return status from contracts
1104
1105
1106 END IF; -- if the PO is cancelled
1107
1108 END IF;-- If po revision>0
1109
1110 END IF; -- if conterms exist
1111 IF g_fnd_debug = 'Y' then
1112 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1113 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1114 MODULE =>g_module_prefix||l_api_name,
1115 MESSAGE =>'210: End Update Contract Terms');
1116 END IF;
1117
1118 END IF;
1119 EXCEPTION
1120 WHEN l_Contracts_call_exception then
1121 IF g_fnd_debug = 'Y' then
1122 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1123 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1124 MODULE =>g_module_prefix||l_api_name,
1125 MESSAGE =>'300: End Update_contract Terms.In Exception l_Contracts_call_exception');
1126 END IF;
1127 END IF;
1128 -- Show one error message atleast
1129 IF x_msg_data is null and FND_MSG_PUB.Count_Msg >0 then
1130 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
1131 ELSE
1132 Fnd_message.set_name('PO','PO_API_ERROR');
1133 Fnd_message.set_token( token => 'PROC_CALLER'
1134 , VALUE => 'PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS');
1135 Fnd_message.set_token( token => 'PROC_CALLED'
1136 , VALUE => l_k_api_name);
1137 FND_MSG_PUB.Add;
1138 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
1139
1140 END IF;
1141 IF g_fnd_debug = 'Y' then
1142 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
1143 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1144 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1145 MODULE =>g_module_prefix||l_api_name,
1146 MESSAGE =>':Errors in stack-'||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
1147 END IF;
1148 END LOOP;
1149
1150 END IF;
1151 WHEN OTHERS THEN
1152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1153 IF FND_MSG_PUB.Check_Msg_Level
1154 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1155 THEN
1156 FND_MSG_PUB.Add_Exc_Msg
1157 (p_pkg_name => 'PO_CONTERMS_WF_PVT',
1158 p_procedure_name =>'UPDATE_CONTRACT_TERMS');
1159
1160 END IF; --msg level
1161 FND_MSG_PUB.Count_And_Get
1162 (p_count => x_msg_count,
1163 p_data => x_msg_data );
1164
1165 IF g_fnd_debug = 'Y' then
1166 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1167 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1168 MODULE =>g_module_prefix||l_api_name,
1169 MESSAGE =>'400: End Update_contract_terms.In Exception others');
1170 END IF;
1171 END IF;
1172 -- show one error message at least
1173 IF x_msg_data is null and FND_MSG_PUB.Count_Msg >0 then
1174 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
1175 IF x_msg_data is null then
1176 x_msg_data := SQLCODE||':'||SQLERRM;
1177 END IF;
1178 END IF;
1179 IF g_fnd_debug = 'Y' then
1180 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1181 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1182 MODULE =>g_module_prefix||l_api_name,
1183 MESSAGE =>'410: sql error:'||SQLCODE||':'||SQLERRM);
1184 END IF;
1185 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1186 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1187 MODULE =>g_module_prefix||l_api_name,
1188 MESSAGE =>'420: x_msg_data:'||x_msg_data);
1189 END IF;
1190 END IF;
1191 END UPDATE_CONTRACT_TERMS;
1192 -------------------------------------------------------------------------------
1193 --Start of Comments
1194 --Name: IS_STANDARD_CONTRACT
1195 --Pre-reqs:
1196 -- Contracts package stubs should be there
1197 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1198 --Modifies:
1199 -- None
1200 --Locks:
1201 -- None
1202 --Function:
1203 -- This API will be called by approval workflow Function IS_STANDARD_CONTRACT
1204 -- to determine if Contract terms have changed from what were defaulted
1205 -- on Contract template
1206 --Parameters:
1207 --IN:
1208 --itemtype
1209 -- Standard workflow Parameter.
1210 --itemkey
1211 -- Standard workflow parameter
1212 --actid
1213 -- Standard workflow parameter
1214 --funcmode
1215 -- Standard workflow parameter
1216 --OUT:
1217 --Result
1218 -- Result of the call
1219 -- Possible Return Values:
1220 -- NO_CHANGE
1221 -- There is not change in Contract terms from Standard Contract template
1222 -- ARTICLES_CHANGED
1223 -- Articles are changed from Contract terms in Standard Contract template
1224 -- DELIVERABLES_CHANGED
1225 -- Deliverables are changed from Contract terms in Standard Contract template
1226 -- ALL_CHANGED
1227 -- Deliverables and Articles are changed from Contract terms in
1228 -- Standard Contract template
1229 --Notes:
1230 -- None
1231 --Testing:
1232 -- Test this API by Changing contract terms, by not changing Contract terms
1233 -- and for POs which are not Procurement Contract.
1234 -- For more details refer to UT test scripts in DLD
1235 --End of Comments
1236 -------------------------------------------------------------------------------
1237 PROCEDURE IS_STANDARD_CONTRACT (itemtype IN VARCHAR2,
1238 itemkey IN VARCHAR2,
1239 actid IN NUMBER,
1240 funcmode IN VARCHAR2,
1241 result OUT NOCOPY VARCHAR2) IS
1242
1243 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1244 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1245 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1246 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1247
1248 l_return_status VARCHAR2(1);
1249 l_msg_data VARCHAR2(2000);
1250 l_msg_count NUMBER;
1251
1252 l_doc_string VARCHAR2(200);
1253 l_preparer_user_name VARCHAR2(100);
1254
1255 l_contracts_call_exception exception;
1256 BEGIN
1257 IF (g_po_wf_debug = 'Y') THEN
1258 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1259 '10: Start function IS_STANDARD_CONTRACT ');
1260 END IF;
1261 -- Do nothing in cancel or timeout mode
1262 IF (funcmode <> WF_ENGINE.eng_run) then
1263 result := WF_ENGINE.eng_null;
1264 return;
1265 END IF;
1266 result := 'NO_CHANGE';
1267 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1268 itemtype => itemtype,
1269 itemkey => itemkey,
1270 aname => 'CONTERMS_EXIST_FLAG');
1271 IF (l_conterms_yn = 'Y') then
1272
1273 -- get other needed values from attribs
1274 get_wf_params(itemtype =>itemtype,
1275 itemkey =>itemkey,
1276 x_po_header_id =>l_po_header_id,
1277 x_po_doc_type =>l_po_doc_type,
1278 x_po_doc_subtype =>l_po_doc_subtype);
1279
1280 --Call contracts to find out if contract terms deviated from standard template
1281
1282 result :=OKC_TERMS_UTIL_GRP.Deviation_From_Standard(
1283 p_api_version => 1.0,
1284 p_doc_id => l_po_header_id,
1285 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1286 x_return_status => l_return_status,
1287 x_msg_data => l_msg_data,
1288 x_msg_count => l_msg_count);
1289 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1290
1291 IF (g_po_wf_debug = 'Y') THEN
1292 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1293 '100: returned value from Contracts'||result);
1294 END IF;
1295
1296 ELSE
1297 RAISE l_Contracts_call_exception;
1298 END IF; -- Return status from contracts
1299
1300
1301 ELSE -- if no conterms
1302 IF (g_po_wf_debug = 'Y') THEN
1303 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1304 '140:Not a Procurement contract');
1305 END IF;
1306 END IF; -- if conterms exist
1307
1308 IF (g_po_wf_debug = 'Y') THEN
1309 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1310 '200 End: IS_STANDARD_CONTRACT ');
1311 END IF;
1312
1313
1314 EXCEPTION
1315 -- Handle Exceptions and re raise
1316 WHEN l_contracts_call_exception then
1317 IF (g_po_wf_debug = 'Y') THEN
1318 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1319 '250: End contracts_call_exception: IS_STANDARD_CONTRACT ');
1320 show_error(itemtype => itemtype,
1321 itemkey => itemkey,
1322 p_api_name =>'OKC_TERMS_UTIl_GRP.DEVIATION_FROM_STANDARD',
1323 p_return_status => l_return_status);
1324 END IF;
1325 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1326 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1327 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_STANDARD_CONTRACT', 'l_contracts_call_Exception');
1328
1329 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1330 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1331 'PO_CONTERMS_WF_PVT.IS_STANDARD_CONTRACT');
1332 RAISE;
1333
1334 WHEN OTHERS THEN
1335 IF (g_po_wf_debug = 'Y') THEN
1336 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1337 '300: End IN Exception: IS_STANDARD_CONTRACT ');
1338 END IF;
1339 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1340 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1341 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_STANDARD_CONTRACT', 'l_contracts_call_Exception');
1342
1343 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1344 l_doc_string, sqlerrm,
1345 'PO_CONTERMS_WF_PVT.IS_STANDARD_CONTRACT');
1346 RAISE;
1347
1348 END IS_STANDARD_CONTRACT;
1349 -------------------------------------------------------------------------------
1350 --Start of Comments
1351 --Name: IS_CONTRACT_TEMPLATE_EXPIRED
1352 --Pre-reqs:
1353 -- Contracts package stubs should be there
1354 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1355 --Modifies:
1356 -- None
1357 --Locks:
1358 -- None
1359 --Function:
1360 -- This API will be called by approval workflow Function IS_CONTRACT_TEMPLATE_EXPIRED
1361 -- to determine if Contract terms template being used has expired or not
1362 --Parameters:
1363 --IN:
1364 --itemtype
1365 -- Standard workflow Parameter.
1366 --itemkey
1367 -- Standard workflow parameter
1368 --actid
1369 -- Standard workflow parameter
1370 --funcmode
1371 -- Standard workflow parameter
1372 --OUT:
1373 --Result
1374 -- Result of the call
1375 -- Possible Return Values:
1376 -- Y
1377 -- Yes- The template has expired
1378 -- N
1379 -- No- The template is not expired
1380 --Notes:
1381 -- None
1382 --Testing:
1383 -- Test this API by using expired and effective templates
1384 -- For more details refer to UT test scripts in DLD
1385 --End of Comments
1386 -------------------------------------------------------------------------------
1387 PROCEDURE IS_CONTRACT_TEMPLATE_EXPIRED(itemtype IN VARCHAR2,
1388 itemkey IN VARCHAR2,
1389 actid IN NUMBER,
1390 funcmode IN VARCHAR2,
1391 result OUT NOCOPY VARCHAR2) IS
1392
1393 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1394 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1395 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1396 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1397
1398 l_return_status VARCHAR2(1);
1399 l_msg_data VARCHAR2(2000);
1400 l_msg_count NUMBER;
1401
1402 l_doc_string VARCHAR2(200);
1403 l_preparer_user_name VARCHAR2(100);
1404
1405 l_contracts_call_exception exception;
1406 BEGIN
1407 IF (g_po_wf_debug = 'Y') THEN
1408 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1409 '10: Start function IS_CONTRACT_TEMPLATE_EXPIRED ');
1410 END IF;
1411 -- Do nothing in cancel or timeout mode
1412 IF (funcmode <> WF_ENGINE.eng_run) then
1413 result := WF_ENGINE.eng_null;
1414 return;
1415 END IF;
1416 result := 'N';
1417 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1418 itemtype => itemtype,
1419 itemkey => itemkey,
1420 aname => 'CONTERMS_EXIST_FLAG');
1421 IF (l_conterms_yn = 'Y') then
1422
1423 -- get other needed values from attribs
1424 get_wf_params(itemtype =>itemtype,
1425 itemkey =>itemkey,
1426 x_po_header_id =>l_po_header_id,
1427 x_po_doc_type =>l_po_doc_type,
1428 x_po_doc_subtype =>l_po_doc_subtype);
1429
1430 --Call contracts to find out if contract template expired
1431 result :=OKC_TERMS_UTIl_GRP.IS_TEMPLATE_EXPIRED(
1432 p_api_version => 1.0,
1433 p_doc_id => l_po_header_id,
1434 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1435 x_return_status => l_return_status,
1436 x_msg_data => l_msg_data,
1437 x_msg_count => l_msg_count);
1438 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1439
1440 IF (g_po_wf_debug = 'Y') THEN
1441 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1442 '100: returned value from Contracts'||result);
1443 END IF;
1444
1445 ELSE
1446 RAISE l_Contracts_call_exception;
1447 END IF; -- Return status from contracts
1448
1449
1450 ELSE -- if no conterms
1451 IF (g_po_wf_debug = 'Y') THEN
1452 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1453 '140:Not a Procurement contract');
1454 END IF;
1455 END IF; -- if conterms exist
1456
1457 IF (g_po_wf_debug = 'Y') THEN
1458 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1459 '200 End: IS_CONTRACT_TEMPLATE_EXPIRED ');
1460 END IF;
1461
1462
1463 EXCEPTION
1464 -- Handle Exceptions and re raise
1465 WHEN l_contracts_call_exception then
1466 IF (g_po_wf_debug = 'Y') THEN
1467 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1468 '250: End contracts_call_exception: IS_CONTRACT_TEMPLATE_EXPIRED ');
1469 show_error(itemtype => itemtype,
1470 itemkey => itemkey,
1471 p_api_name =>'OKC_TERMS_UTIl_GRP.IS_CONTRACT_TEMPLATE_EXPIRED',
1472 p_return_status => l_return_status);
1473 END IF;
1474 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1475 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1476 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_TEMPLATE_EXPIRED', 'l_contracts_call_Exception');
1477
1478 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1479 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1480 'PO_CONTERMS_WF_PVT.IS_CONTRACT_TEMPLATE_EXPIRED');
1481 RAISE;
1482
1483 WHEN OTHERS THEN
1484 IF (g_po_wf_debug = 'Y') THEN
1485 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1486 '300: End IN Exception: IS_CONTRACT_TEMPLATE_EXPIRED ');
1487 END IF;
1488 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1489 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1490 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_TEMPLATE_EXPIRED', 'l_contracts_call_Exception');
1491
1492 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1493 l_doc_string, sqlerrm,
1494 'PO_CONTERMS_WF_PVT.IS_CONTRACT_TEMPLATE_EXPIRED');
1495 RAISE;
1496
1497 END IS_CONTRACT_TEMPLATE_EXPIRED;
1498
1499 -------------------------------------------------------------------------------
1500 --Start of Comments
1501 --Name: IS_CONTRACT_ARTICLES_EXIST
1502 --Pre-reqs:
1503 -- Contracts package stubs should be there
1504 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1505 --Modifies:
1506 -- None
1507 --Locks:
1508 -- None
1509 --Function:
1510 -- This API will be called by approval workflow Function IS_CONTRACT_ARTICLES_EXIST
1511 -- to determine if Contract terms have articles attached to Purchase Order
1512 --Parameters:
1513 --IN:
1514 --itemtype
1515 -- Standard workflow Parameter.
1516 --itemkey
1517 -- Standard workflow parameter
1518 --actid
1519 -- Standard workflow parameter
1520 --funcmode
1521 -- Standard workflow parameter
1522 --OUT:
1523 --Result
1524 -- Result of the call
1525 -- Possible Return Values:
1526 -- NONE
1527 -- There are no articles attached to this purchase order
1528 -- ONLY_STANDARD
1529 -- Only standard Articles exist on this purchase order
1530 -- NON_STANDARD
1531 -- Standard as well as non standard Articles exist on this purchase order
1532 --Notes:
1533 -- None
1534 --Testing:
1535 -- For more details refer to UT test scripts in DLD
1536 --End of Comments
1537 -------------------------------------------------------------------------------
1538 PROCEDURE IS_CONTRACT_ARTICLES_EXIST (itemtype IN VARCHAR2,
1539 itemkey IN VARCHAR2,
1540 actid IN NUMBER,
1541 funcmode IN VARCHAR2,
1542 result OUT NOCOPY VARCHAR2) IS
1543
1544 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1545 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1546 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1547 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1548
1549 l_return_status VARCHAR2(1);
1550 l_msg_data VARCHAR2(2000);
1551 l_msg_count NUMBER;
1552
1553 l_doc_string VARCHAR2(200);
1554 l_preparer_user_name VARCHAR2(100);
1555
1556 l_contracts_call_exception exception;
1557 BEGIN
1558 IF (g_po_wf_debug = 'Y') THEN
1559 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1560 '10: Start function IS_CONTRACT_ARTICLES_EXIST ');
1561 END IF;
1562 -- Do nothing in cancel or timeout mode
1563 IF (funcmode <> WF_ENGINE.eng_run) then
1564 result := WF_ENGINE.eng_null;
1565 return;
1566 END IF;
1567 result := 'NONE';
1568 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1569 itemtype => itemtype,
1570 itemkey => itemkey,
1571 aname => 'CONTERMS_EXIST_FLAG');
1572 IF (l_conterms_yn = 'Y') then
1573
1574 -- get other needed values from attribs
1575 get_wf_params(itemtype =>itemtype,
1576 itemkey =>itemkey,
1577 x_po_header_id =>l_po_header_id,
1578 x_po_doc_type =>l_po_doc_type,
1579 x_po_doc_subtype =>l_po_doc_subtype);
1580
1581 --Call contracts to find out if contract articles attached
1582 result :=OKC_TERMS_UTIl_GRP.IS_ARTICLE_EXIST(
1583 p_api_version => 1.0,
1584 p_doc_id => l_po_header_id,
1585 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1586 x_return_status => l_return_status,
1587 x_msg_data => l_msg_data,
1588 x_msg_count => l_msg_count);
1589 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1590
1591 IF (g_po_wf_debug = 'Y') THEN
1592 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1593 '100: returned value from Contracts'||result);
1594 END IF;
1595
1596 ELSE
1597 RAISE l_Contracts_call_exception;
1598 END IF; -- Return status from contracts
1599
1600
1601 ELSE -- if no conterms
1602 IF (g_po_wf_debug = 'Y') THEN
1603 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1604 '140:Not a Procurement contract');
1605 END IF;
1606 END IF; -- if conterms exist
1607
1608 IF (g_po_wf_debug = 'Y') THEN
1609 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1610 '200 End: IS_CONTRACT_ARTICLES_EXIST ');
1611 END IF;
1612
1613
1614 EXCEPTION
1615 -- Handle Exceptions and re raise
1616 WHEN l_contracts_call_exception then
1617 IF (g_po_wf_debug = 'Y') THEN
1618 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1619 '250: End contracts_call_exception: IS_CONTRACT_ARTICLES_EXIST ');
1620 show_error(itemtype => itemtype,
1621 itemkey => itemkey,
1622 p_api_name =>'OKC_TERMS_UTIl_GRP.IS_ARTICLE_EXIST',
1623 p_return_status => l_return_status);
1624 END IF;
1625 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1626 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1627 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_ARTICLES_EXIST', 'l_contracts_call_Exception');
1628
1629 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1630 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1631 'PO_CONTERMS_WF_PVT.IS_CONTRACT_ARTICLES_EXIST');
1632 RAISE;
1633
1634 WHEN OTHERS THEN
1635 IF (g_po_wf_debug = 'Y') THEN
1636 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1637 '300: End IN Exception: IS_CONTRACT_ARTICLES_EXIST ');
1638 END IF;
1639 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1640 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1641 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_ARTICLES_EXIST', 'l_contracts_call_Exception');
1642
1643 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1644 l_doc_string, sqlerrm,
1645 'PO_CONTERMS_WF_PVT.IS_CONTRACT_ARTICLES_EXIST');
1646 RAISE;
1647
1648
1649 END IS_CONTRACT_ARTICLES_EXIST;
1650
1651 -------------------------------------------------------------------------------
1652 --Start of Comments
1653 --Name: IS_CONTRACT_ARTICLES_AMENDED
1654 --Pre-reqs:
1655 -- Contracts package stubs should be there
1656 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1657 --Modifies:
1658 -- None
1659 --Locks:
1660 -- None
1661 --Function:
1662 -- This API will be called by approval workflow Function IS_CONTRACT_ARTICLES_AMENDED
1663 -- to determine if contract articles were amended in this revision
1664 --Parameters:
1665 --IN:
1666 --itemtype
1667 -- Standard workflow Parameter.
1668 --itemkey
1669 -- Standard workflow parameter
1670 --actid
1671 -- Standard workflow parameter
1672 --funcmode
1673 -- Standard workflow parameter
1674 --OUT:
1675 --Result
1676 -- Result of the call
1677 -- Possible Return Values:
1678 -- NONE
1679 -- No articles were amended in this revision of purchase order
1680 -- ONLY_STANDARD
1681 -- Only standard Articles were amended in this revision of purchase order
1682 -- NON_STANDARD
1683 -- Standard as well as non standard Articles were amended in this revision of purchase order
1684 --Notes:
1685 -- None
1686 --Testing:
1687 -- For more details refer to UT test scripts in DLD
1688 --End of Comments
1689 -------------------------------------------------------------------------------
1690 PROCEDURE IS_CONTRACT_ARTICLES_AMENDED(itemtype IN VARCHAR2,
1691 itemkey IN VARCHAR2,
1692 actid IN NUMBER,
1693 funcmode IN VARCHAR2,
1694 result OUT NOCOPY VARCHAR2) IS
1695
1696 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1697 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1698 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1699 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1700
1701 l_return_status VARCHAR2(1);
1702 l_msg_data VARCHAR2(2000);
1703 l_msg_count NUMBER;
1704
1705 l_doc_string VARCHAR2(200);
1706 l_preparer_user_name VARCHAR2(100);
1707
1708 l_contracts_call_exception exception;
1709 BEGIN
1710 IF (g_po_wf_debug = 'Y') THEN
1711 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1712 '10: Start function IS_CONTRACT_ARTICLES_AMENDED');
1713 END IF;
1714 -- Do nothing in cancel or timeout mode
1715 IF (funcmode <> WF_ENGINE.eng_run) then
1716 result := WF_ENGINE.eng_null;
1717 return;
1718 END IF;
1719 result := 'NON_STANDARD';
1720 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1721 itemtype => itemtype,
1722 itemkey => itemkey,
1723 aname => 'CONTERMS_EXIST_FLAG');
1724 IF (l_conterms_yn = 'Y') then
1725
1726 -- get other needed values from attribs
1727 get_wf_params(itemtype =>itemtype,
1728 itemkey =>itemkey,
1729 x_po_header_id =>l_po_header_id,
1730 x_po_doc_type =>l_po_doc_type,
1731 x_po_doc_subtype =>l_po_doc_subtype);
1732
1733 --Call contracts to find out if contract articles were amended in this revision
1734 result :=OKC_TERMS_UTIl_GRP.IS_ARTICLE_AMENDED(
1735 p_api_version => 1.0,
1736 p_doc_id => l_po_header_id,
1737 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1738 x_return_status => l_return_status,
1739 x_msg_data => l_msg_data,
1740 x_msg_count => l_msg_count);
1741 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1742
1743 IF (g_po_wf_debug = 'Y') THEN
1744 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1745 '100: returned value from Contracts'||result);
1746 END IF;
1747
1748 ELSE
1749 RAISE l_Contracts_call_exception;
1750 END IF; -- Return status from contracts
1751
1752
1753 ELSE -- if no conterms
1754 IF (g_po_wf_debug = 'Y') THEN
1755 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1756 '140:Not a Procurement contract');
1757 END IF;
1758 END IF; -- if conterms exist
1759
1760 IF (g_po_wf_debug = 'Y') THEN
1761 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1762 '200 End: IS_CONTRACT_ARTICLES_AMENDED ');
1763 END IF;
1764
1765
1766 EXCEPTION
1767 -- Handle Exceptions and re raise
1768 WHEN l_contracts_call_exception then
1769 IF (g_po_wf_debug = 'Y') THEN
1770 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1771 '250: End contracts_call_exception: IS_CONTRACT_ARTICLES_AMENDED ');
1772 show_error(itemtype => itemtype,
1773 itemkey => itemkey,
1774 p_api_name =>'OKC_TERMS_UTIl_GRP.IS_ARTICLE_AMENDED',
1775 p_return_status => l_return_status);
1776 END IF;
1777 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1778 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1779 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_ARTICLES_AMENDED', 'l_contracts_call_Exception');
1780
1781 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1782 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1783 'PO_CONTERMS_WF_PVT.IS_CONTRACT_ARTICLES_AMENDED');
1784 RAISE;
1785
1786 WHEN OTHERS THEN
1787 IF (g_po_wf_debug = 'Y') THEN
1788 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1789 '300: End IN Exception: IS_CONTRACT_ARTICLES_AMENDED ');
1790 END IF;
1791 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1792 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1793 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_ARTICLES_AMENDED', 'l_contracts_call_Exception');
1794
1795 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1796 l_doc_string, sqlerrm,
1797 'PO_CONTERMS_WF_PVT.IS_CONTRACT_ARTICLES_AMENDED');
1798 RAISE;
1799
1800
1801 END IS_CONTRACT_ARTICLES_AMENDED;
1802
1803
1804 -------------------------------------------------------------------------------
1805 --Start of Comments
1806 --Name: IS_CONTRACT_DELIVRABLS_EXIST
1807 --Pre-reqs:
1808 -- Contracts package stubs should be there
1809 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1810 --Modifies:
1811 -- None
1812 --Locks:
1813 -- None
1814 --Function:
1815 -- This API will be called by approval workflow Function IS_CONTRACT_DELIVRABLS_EXIST
1816 -- to determine if contract deliverables are attached to PO
1817 --Parameters:
1818 --IN:
1819 --itemtype
1820 -- Standard workflow Parameter.
1821 --itemkey
1822 -- Standard workflow parameter
1823 --actid
1824 -- Standard workflow parameter
1825 --funcmode
1826 -- Standard workflow parameter
1827 --OUT:
1828 --Result
1829 -- Result of the call
1830 -- Possible Return Values:
1831 -- NONE
1832 -- No deliverables are attached
1833 -- CONTRACTUAL
1834 -- Only contractual deliverables are attached
1835 -- INTERNAL
1836 -- Only Internal deliverables are attached
1837 -- ALL
1838 -- Contractual as well as Internal deliverables are attached
1839 --Notes:
1840 -- None
1841 --Testing:
1842 -- For more details refer to UT test scripts in DLD
1843 --End of Comments
1844 -------------------------------------------------------------------------------
1845 PROCEDURE IS_CONTRACT_DELIVRABLS_EXIST(itemtype IN VARCHAR2,
1846 itemkey IN VARCHAR2,
1847 actid IN NUMBER,
1848 funcmode IN VARCHAR2,
1849 result OUT NOCOPY VARCHAR2) IS
1850
1851 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1852 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1853 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1854 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1855
1856 l_return_status VARCHAR2(1);
1857 l_msg_data VARCHAR2(2000);
1858 l_msg_count NUMBER;
1859
1860 l_doc_string VARCHAR2(200);
1861 l_preparer_user_name VARCHAR2(100);
1862
1863 l_contracts_call_exception exception;
1864 BEGIN
1865 IF (g_po_wf_debug = 'Y') THEN
1866 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1867 '10: Start function IS_CONTRACT_DELIVRABLS_EXIST');
1868 END IF;
1869 -- Do nothing in cancel or timeout mode
1870 IF (funcmode <> WF_ENGINE.eng_run) then
1871 result := WF_ENGINE.eng_null;
1872 return;
1873 END IF;
1874 result := 'ALL';
1875 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1876 itemtype => itemtype,
1877 itemkey => itemkey,
1878 aname => 'CONTERMS_EXIST_FLAG');
1879 IF (l_conterms_yn = 'Y') then
1880
1881 -- get other needed values from attribs
1882 get_wf_params(itemtype =>itemtype,
1883 itemkey =>itemkey,
1884 x_po_header_id =>l_po_header_id,
1885 x_po_doc_type =>l_po_doc_type,
1886 x_po_doc_subtype =>l_po_doc_subtype);
1887
1888 --Call contracts to find out if contract deliverables were amended in this revision
1889 result :=OKC_TERMS_UTIL_GRP.Is_Deliverable_Exist(
1890 p_api_version => 1.0,
1891 p_doc_id => l_po_header_id,
1892 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1893 x_return_status => l_return_status,
1894 x_msg_data => l_msg_data,
1895 x_msg_count => l_msg_count);
1896 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1897 IF (result = 'CONTRACTUAL_AND_INTERNAL') THEN
1898 result := 'ALL';
1899 END IF;
1900 IF (g_po_wf_debug = 'Y') THEN
1901 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1902 '100: returned value from Contracts'||result);
1903 END IF;
1904
1905 ELSE
1906 RAISE l_Contracts_call_exception;
1907 END IF; -- Return status from contracts
1908
1909
1910 ELSE -- if no conterms
1911 IF (g_po_wf_debug = 'Y') THEN
1912 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1913 '140:Not a Procurement contract');
1914 END IF;
1915 END IF; -- if conterms exist
1916
1917 IF (g_po_wf_debug = 'Y') THEN
1918 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1919 '200 End: IS_CONTRACT_DELIVRABLS_EXIST ');
1920 END IF;
1921
1922
1923 EXCEPTION
1924 -- Handle Exceptions and re raise
1925 WHEN l_contracts_call_exception then
1926 IF (g_po_wf_debug = 'Y') THEN
1927 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1928 '250: End contracts_call_exception: IS_CONTRACT_DELIVRABLS_EXIST ');
1929 show_error(itemtype => itemtype,
1930 itemkey => itemkey,
1931 p_api_name =>'OKC_TERMS_UTIl_GRP.Is_Deliverable_Exist',
1932 p_return_status => l_return_status);
1933 END IF;
1934 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1935 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1936 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_DELIVRABLS_EXIST', 'l_contracts_call_Exception');
1937
1938 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1939 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1940 'PO_CONTERMS_WF_PVT.IS_CONTRACT_DELIVRABLS_EXIST');
1941 RAISE;
1942
1943 WHEN OTHERS THEN
1944 IF (g_po_wf_debug = 'Y') THEN
1945 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1946 '300: End IN Exception: IS_CONTRACT_DELIVRABLS_EXIST ');
1947 END IF;
1948 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1949 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1950 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_DELIVRABLS_EXIST', 'l_contracts_call_Exception');
1951
1952 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1953 l_doc_string, sqlerrm,
1954 'PO_CONTERMS_WF_PVT.IS_CONTRACT_DELIVRABLS_EXIST');
1955 RAISE;
1956
1957
1958 END IS_CONTRACT_DELIVRABLS_EXIST;
1959
1960 -------------------------------------------------------------------------------
1961 --Start of Comments
1962 --Name: IS_CONTRACT_DELIVRABLS_AMENDED
1963 --Pre-reqs:
1964 -- Contracts package stubs should be there
1965 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1966 --Modifies:
1967 -- None
1968 --Locks:
1969 -- None
1970 --Function:
1971 -- This API will be called by approval workflow Function IS_CONTRACT_DELIVRABLS_AMENDED
1972 -- to determine if contract deliverables were amended in this revision
1973 --Parameters:
1974 --IN:
1975 --itemtype
1976 -- Standard workflow Parameter.
1977 --itemkey
1978 -- Standard workflow parameter
1979 --actid
1980 -- Standard workflow parameter
1981 --funcmode
1982 -- Standard workflow parameter
1983 --OUT:
1984 --Result
1985 -- Result of the call
1986 -- Possible Return Values:
1987 -- NONE
1988 -- No deliverables were amended in this revision of purchase order
1989 -- CONTRACTUAL
1990 -- Only contractual deliverables were amended in this revision of purchase order
1991 -- INTERNAL
1992 -- Only Internal deliverables were amended in this revision of purchase order
1993 -- ALL
1994 -- Contractual as well as Internal deliverables were amended in this revision of purchase order
1995 --Notes:
1996 -- None
1997 --Testing:
1998 -- For more details refer to UT test scripts in DLD
1999 --End of Comments
2000 -------------------------------------------------------------------------------
2001 PROCEDURE IS_CONTRACT_DELIVRABLS_AMENDED(itemtype IN VARCHAR2,
2002 itemkey IN VARCHAR2,
2003 actid IN NUMBER,
2004 funcmode IN VARCHAR2,
2005 result OUT NOCOPY VARCHAR2) IS
2006
2007 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
2008 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
2009 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
2010 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
2011
2012 l_return_status VARCHAR2(1);
2013 l_msg_data VARCHAR2(2000);
2014 l_msg_count NUMBER;
2015
2016 l_doc_string VARCHAR2(200);
2017 l_preparer_user_name VARCHAR2(100);
2018
2019 l_contracts_call_exception exception;
2020 BEGIN
2021 IF (g_po_wf_debug = 'Y') THEN
2022 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2023 '10: Start function IS_CONTRACT_ARTICLES_AMENDED');
2024 END IF;
2025 -- Do nothing in cancel or timeout mode
2026 IF (funcmode <> WF_ENGINE.eng_run) then
2027 result := WF_ENGINE.eng_null;
2028 return;
2029 END IF;
2030 result := 'ALL';
2031 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
2032 itemtype => itemtype,
2033 itemkey => itemkey,
2034 aname => 'CONTERMS_EXIST_FLAG');
2035 IF (l_conterms_yn = 'Y') then
2036
2037 -- get other needed values from attribs
2038 get_wf_params(itemtype =>itemtype,
2039 itemkey =>itemkey,
2040 x_po_header_id =>l_po_header_id,
2041 x_po_doc_type =>l_po_doc_type,
2042 x_po_doc_subtype =>l_po_doc_subtype);
2043
2044 --Call contracts to find out if contract deliverables were amended in this revision
2045 result :=OKC_TERMS_UTIL_GRP.Is_Deliverable_Amended(
2046 p_api_version => 1.0,
2047 p_doc_id => l_po_header_id,
2048 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
2049 x_return_status => l_return_status,
2050 x_msg_data => l_msg_data,
2051 x_msg_count => l_msg_count);
2052 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
2053 IF (result = 'CONTRACTUAL_AND_INTERNAL') THEN
2054 result := 'ALL';
2055 END IF;
2056 IF (g_po_wf_debug = 'Y') THEN
2057 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2058 '100: returned value from Contracts'||result);
2059 END IF;
2060
2061 ELSE
2062 RAISE l_Contracts_call_exception;
2063 END IF; -- Return status from contracts
2064
2065
2066 ELSE -- if no conterms
2067 IF (g_po_wf_debug = 'Y') THEN
2068 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2069 '140:Not a Procurement contract');
2070 END IF;
2071 END IF; -- if conterms exist
2072
2073 IF (g_po_wf_debug = 'Y') THEN
2074 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2075 '200 End: IS_CONTRACT_DELIVRABLS_AMENDED ');
2076 END IF;
2077
2078
2079 EXCEPTION
2080 -- Handle Exceptions and re raise
2081 WHEN l_contracts_call_exception then
2082 IF (g_po_wf_debug = 'Y') THEN
2083 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2084 '250: End contracts_call_exception: IS_CONTRACT_DELIVRABLS_AMENDED ');
2085 show_error(itemtype => itemtype,
2086 itemkey => itemkey,
2087 p_api_name =>'OKC_TERMS_UTIl_GRP.IS_DELIVERABLE_AMENDED',
2088 p_return_status => l_return_status);
2089 END IF;
2090 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2091 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2092 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_DELIVRABLS_AMENDED', 'l_contracts_call_Exception');
2093
2094 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
2095 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
2096 'PO_CONTERMS_WF_PVT.IS_CONTRACT_DELIVRABLS_AMENDED');
2097 RAISE;
2098
2099 WHEN OTHERS THEN
2100 IF (g_po_wf_debug = 'Y') THEN
2101 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2102 '300: End IN Exception: IS_CONTRACT_DELIVRABLS_AMENDED ');
2103 END IF;
2104 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2105 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2106 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_DELIVRABLS_AMENDED', 'l_contracts_call_Exception');
2107
2108 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
2109 l_doc_string, sqlerrm,
2110 'PO_CONTERMS_WF_PVT.IS_CONTRACT_DELIVRABLS_AMENDED');
2111 RAISE;
2112
2113
2114 END IS_CONTRACT_DELIVRABLS_AMENDED;
2115
2116
2117
2118
2119 -------------------------------------------------------------------------------
2120 --Start of Comments
2121 --Name: cancel_deliverables
2122 --Pre-reqs:
2123 -- None.
2124 --Modifies:
2125 -- Cancels deliverables recorded in the OKC schema, on the Purchasing document
2126 --Locks:
2127 -- None.
2128 --Function:
2129 -- A wrapper procedure to call Contracts API to cancel deliverables on a PO CONTRACT
2130 --Parameters:
2131 --IN:
2132 --p_bus_doc_id
2133 -- PO header id
2134 --p_bus_doc_type
2135 -- PA - Purchase Agreement
2136 -- PO - Purchase Order
2137 --p_bus_doc_subtype
2138 -- STANDARD
2139 -- BALNKET
2140 -- CONTRACT
2141 --p_bus_doc_version
2142 -- Document revision number
2143 --p_event_code
2144 -- One of the seeded PO Contracts event
2145 -- PO_CLOSE - Finally Close PO
2146 -- PO_CANCEL - Cancel PO
2147 --p_event_date
2148 -- Date on which the PO Contract event occurred. Default is SYSDATE
2149 --p_busdocdates_tbl
2150 -- OKC_MANAGE_DELIVERABLES_GRP.busdocdates_tbl_type table type
2151 -- is a table of dates based events on the PO to resolve deliverables
2152 -- that are based on PO dates (ex. Start date).
2153 --OUT:
2154 --x_return_status
2155 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected/other error
2156 -- FND_API.G_RET_STS_SUCCESS - for successful execution of the API
2157 --Testing:
2158 --
2159 --Notes:
2160 -- This procedure should be called when it is needed to cancel deliverables
2161 -- on the Purchasing document. It should be called instead of calling the OKC
2162 -- API directly.
2163 --End of Comments
2164 -------------------------------------------------------------------------------
2165
2166 PROCEDURE cancel_deliverables (
2167 p_bus_doc_id IN NUMBER
2168 ,p_bus_doc_type IN VARCHAR2
2169 ,p_bus_doc_subtype IN VARCHAR2
2170 ,p_bus_doc_version IN NUMBER
2171 ,p_event_code IN VARCHAR2
2172 ,p_event_date IN DATE
2173 ,p_busdocdates_tbl IN EVENT_TBL_TYPE
2174 ,x_return_status OUT NOCOPY VARCHAR2
2175 ) IS
2176
2177 l_bus_doc_version PO_HEADERS_ALL.revision_num%TYPE;
2178 l_contracts_document_type VARCHAR2(150);
2179
2180 l_api_name VARCHAR2(30) := 'cancel_deliverables';
2181 l_msg_data VARCHAR2(2000);
2182 l_msg_count NUMBER;
2183 l_return_status VARCHAR2(1);
2184
2185 BEGIN
2186 -- initialize return status
2187 x_return_status := FND_API.G_RET_STS_SUCCESS;
2188
2189
2190 -- select the business document version if passed null
2191 IF (p_bus_doc_version IS NULL) THEN
2192 -- SQL what: select the document version
2193 -- SQL why : to cancel deliverables on the current version
2194 -- SQL join: po_header_id
2195 SELECT revision_num
2196 INTO l_bus_doc_version
2197 FROM po_headers_all
2198 WHERE po_header_id = p_bus_doc_id;
2199 ELSE
2200 l_bus_doc_version := p_bus_doc_version;
2201 END IF;
2202
2203 l_contracts_document_type := p_bus_doc_type||'_'||p_bus_doc_subtype;
2204
2205 IF (g_fnd_debug = 'Y') THEN
2206 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2207 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE
2208 ,module => g_module_prefix || l_api_name
2209 ,message => 'Before calling contracts API to cancel deliverables');
2210 END IF;
2211 END IF;
2212
2213 -- call to the actual API
2214 OKC_MANAGE_DELIVERABLES_GRP.cancelDeliverables(
2215 p_api_version => 1.0
2216 ,p_init_msg_list => FND_API.G_FALSE
2217 ,p_commit => FND_API.G_FALSE
2218 ,p_bus_doc_id => p_bus_doc_id
2219 ,p_bus_doc_type => l_contracts_document_type
2220 ,p_bus_doc_version => l_bus_doc_version
2221 ,p_event_code => p_event_code
2222 ,p_event_date => p_event_date
2223 ,p_bus_doc_date_events_tbl => p_busdocdates_tbl
2224 ,x_msg_data => l_msg_data
2225 ,x_msg_count => l_msg_count
2226 ,x_return_status => l_return_status);
2227
2228 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2230 END IF;
2231
2232 IF (g_fnd_debug = 'Y') THEN
2233 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2234 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE
2235 ,module => g_module_prefix || l_api_name
2236 ,message => 'Deliverables cancelled successfully');
2237 END IF;
2238 END IF;
2239
2240 EXCEPTION
2241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2242 IF (g_fnd_debug = 'Y') THEN
2243 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2244 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE
2245 ,module => g_module_prefix || l_api_name
2246 ,message => l_msg_data);
2247 END IF;
2248 END IF;
2249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2250 WHEN OTHERS THEN
2251 ROLLBACK;
2252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2253 IF (g_fnd_debug = 'Y') THEN
2254 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2255 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE
2256 ,module => g_module_prefix || l_api_name
2257 ,message => 'Others Exception');
2258 END IF;
2259 END IF;
2260
2261 END cancel_deliverables;
2262
2263 /* CONTERMS FPJ END */
2264 --<CONTERMS FPJ END>
2265 End PO_CONTERMS_WF_PVT;
[Home] [Help]