1 package body PO_CONTERMS_WF_PVT AS
2 /* $Header: POXVWCTB.pls 120.5 2011/12/12 09:23:45 ssindhe ship $ */
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 --For Bug 13123360
994 --Deliverables attached to document were duplicating when
995 --PDOI is ran for multiple batches.This piece of code getting last
996 --signed revision fails as PDOI does not track action updates to
997 --PO_ACTION_HISTORY table.Instead of checking the revision at PO
998 --and disabling the deliverables at OKC,by this fix trying to disable
999 --the deliverables which are for those document revisions which is less
1000 --than the current revision number -1.
1001 /*
1002 -- Bug 3652222 START
1003 IF g_fnd_debug = 'Y' then
1004 PO_DEBUG.debug_stmt(g_module_prefix||l_api_name, '133',
1005 'Before call Get_Last_Signed_Revision');
1006 END IF;
1007
1008 -- Migrate PO:
1009 -- Replaced the pvt api with the grp one as this one checks if the
1010 -- previous revision has conterms in the first place as this is possible
1011 -- now with migrate PO
1012 PO_CONTERMS_UTL_GRP.Get_Last_Signed_Revision(
1013 p_api_version => 1.0,
1014 p_init_msg_list => FND_API.G_FALSE,
1015 p_header_id => p_po_header_id,
1016 p_revision_num => l_po_revision_num,
1017 x_signed_revision_num => l_last_signed_revision,
1018 x_signed_records => l_signed_records,
1019 x_return_status => x_return_status,
1020 x_msg_data => x_msg_data,
1021 x_msg_count => x_msg_count);
1022
1023 IF g_fnd_debug = 'Y' then
1024 PO_DEBUG.debug_stmt(g_module_prefix||l_api_name, '135',
1025 'l_last_signed_revision: ' || l_last_signed_revision ||
1026 ', l_signed_records: ' || l_signed_records);
1027 END IF;
1028
1029 IF (l_signed_records = 'Y' AND l_last_signed_revision >= 0) THEN
1030 -- Bug 3652222 END
1031 */
1032 -- Disable the deliverables attached to previous revision of PO
1033 IF g_fnd_debug = 'Y' then
1034 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1035 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1036 MODULE =>g_module_prefix||l_api_name,
1037 MESSAGE =>'140: Before call to OKC_MANAGE_DELIVERABLES_GRP.DisableDeliverables');
1038 END IF;
1039
1040 END IF;
1041
1042 --For Bug 13123360
1043 FOR i IN 0..l_po_revision_num-1
1044 LOOP
1045
1046 OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables (
1047 p_api_version => 1.0,
1048 p_bus_doc_id => p_po_header_id,
1049 p_bus_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1050 -- Bug 3652222, should pass last signed revision
1051 -- p_bus_doc_version => (l_po_revision_num -1),
1052 --For bug 13123360
1053 --p_bus_doc_version => l_last_signed_revision,
1054 p_bus_doc_version => i,
1055 x_msg_data => x_msg_data,
1056 x_msg_count => x_msg_count,
1057 x_return_status => x_return_status);
1058 IF g_fnd_debug = 'Y' then
1059 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1060 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1061 MODULE =>g_module_prefix||l_api_name,
1062 MESSAGE =>'150: After call to OKC_MANAGE_DELIVERABLES_GRP.DisableDeliverables');
1063 END IF;
1064 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1065 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1066 MODULE =>g_module_prefix||l_api_name,
1067 MESSAGE =>'170: return status'||x_return_status);
1068 END IF;
1069
1070 END IF;
1071 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
1072 RAISE l_Contracts_call_exception;
1073 END IF; -- Return status from contracts
1074
1075 --For bug 13123360
1076 END LOOP;
1077
1078 --Commented enf if for bug 13123360
1079 -- Bug 3652222 START
1080 --END IF; /* IF (l_signed_records = 'Y' AND l_last_signed_revision >= 0) */
1081 -- Bug 3652222 END
1082
1083 -- cancel deliverables only if po is being archived after cancel
1084 IF (UPPER(NVL(l_cancel_flag, 'N'))='Y') THEN
1085
1086 IF g_fnd_debug = 'Y' then
1087 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1088 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1089 MODULE =>g_module_prefix||l_api_name,
1090 MESSAGE =>'180: Before call to wrapper procedure to Cancel Deliverables');
1091 END IF;
1092
1093 END IF;
1094
1095 cancel_deliverables(p_bus_doc_id => p_po_header_id
1096 ,p_bus_doc_type => l_po_doc_type
1097 ,p_bus_doc_subtype => l_po_doc_subtype
1098 ,p_bus_doc_version => l_po_revision_num
1099 ,p_event_code => 'PO_CANCEL'
1100 ,p_event_date => SYSDATE
1101 ,p_busdocdates_tbl => l_event_tbl
1102 ,x_return_status => x_return_status);
1103 IF g_fnd_debug = 'Y' then
1104 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1105 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1106 MODULE =>g_module_prefix||l_api_name,
1107 MESSAGE =>'190: After call to wrapper procedure to Cancel Deliverables');
1108 END IF;
1109
1110 END IF;
1111
1112 IF g_fnd_debug = 'Y' then
1113 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1114 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1115 MODULE =>g_module_prefix||l_api_name,
1116 MESSAGE =>'200: return status '||x_return_status);
1117 END IF;
1118
1119 END IF;
1120 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
1121 RAISE l_Contracts_call_exception;
1122 END IF; -- Return status from contracts
1123
1124
1125 END IF; -- if the PO is cancelled
1126
1127 END IF;-- If po revision>0
1128
1129 END IF; -- if conterms exist
1130 IF g_fnd_debug = 'Y' then
1131 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1132 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1133 MODULE =>g_module_prefix||l_api_name,
1134 MESSAGE =>'210: End Update Contract Terms');
1135 END IF;
1136
1137 END IF;
1138 EXCEPTION
1139 WHEN l_Contracts_call_exception then
1140 IF g_fnd_debug = 'Y' then
1141 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1142 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1143 MODULE =>g_module_prefix||l_api_name,
1144 MESSAGE =>'300: End Update_contract Terms.In Exception l_Contracts_call_exception');
1145 END IF;
1146 END IF;
1147 -- Show one error message atleast
1148 IF x_msg_data is null and FND_MSG_PUB.Count_Msg >0 then
1149 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
1150 ELSE
1151 Fnd_message.set_name('PO','PO_API_ERROR');
1152 Fnd_message.set_token( token => 'PROC_CALLER'
1153 , VALUE => 'PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS');
1154 Fnd_message.set_token( token => 'PROC_CALLED'
1155 , VALUE => l_k_api_name);
1156 FND_MSG_PUB.Add;
1157 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
1158
1159 END IF;
1160 IF g_fnd_debug = 'Y' then
1161 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
1162 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1163 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
1164 MODULE =>g_module_prefix||l_api_name,
1165 MESSAGE =>':Errors in stack-'||FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F' ));
1166 END IF;
1167 END LOOP;
1168
1169 END IF;
1170 WHEN OTHERS THEN
1171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1172 IF FND_MSG_PUB.Check_Msg_Level
1173 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1174 THEN
1175 FND_MSG_PUB.Add_Exc_Msg
1176 (p_pkg_name => 'PO_CONTERMS_WF_PVT',
1177 p_procedure_name =>'UPDATE_CONTRACT_TERMS');
1178
1179 END IF; --msg level
1180 FND_MSG_PUB.Count_And_Get
1181 (p_count => x_msg_count,
1182 p_data => x_msg_data );
1183
1184 IF g_fnd_debug = 'Y' then
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 =>'400: End Update_contract_terms.In Exception others');
1189 END IF;
1190 END IF;
1191 -- show one error message at least
1192 IF x_msg_data is null and FND_MSG_PUB.Count_Msg >0 then
1193 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
1194 IF x_msg_data is null then
1195 x_msg_data := SQLCODE||':'||SQLERRM;
1196 END IF;
1197 END IF;
1198 IF g_fnd_debug = 'Y' then
1199 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1200 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1201 MODULE =>g_module_prefix||l_api_name,
1202 MESSAGE =>'410: sql error:'||SQLCODE||':'||SQLERRM);
1203 END IF;
1204 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1205 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
1206 MODULE =>g_module_prefix||l_api_name,
1207 MESSAGE =>'420: x_msg_data:'||x_msg_data);
1208 END IF;
1209 END IF;
1210 END UPDATE_CONTRACT_TERMS;
1211 -------------------------------------------------------------------------------
1212 --Start of Comments
1213 --Name: IS_STANDARD_CONTRACT
1214 --Pre-reqs:
1215 -- Contracts package stubs should be there
1216 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1217 --Modifies:
1218 -- None
1219 --Locks:
1220 -- None
1221 --Function:
1222 -- This API will be called by approval workflow Function IS_STANDARD_CONTRACT
1223 -- to determine if Contract terms have changed from what were defaulted
1224 -- on Contract template
1225 --Parameters:
1226 --IN:
1227 --itemtype
1228 -- Standard workflow Parameter.
1229 --itemkey
1230 -- Standard workflow parameter
1231 --actid
1232 -- Standard workflow parameter
1233 --funcmode
1234 -- Standard workflow parameter
1235 --OUT:
1236 --Result
1237 -- Result of the call
1238 -- Possible Return Values:
1239 -- NO_CHANGE
1240 -- There is not change in Contract terms from Standard Contract template
1241 -- ARTICLES_CHANGED
1242 -- Articles are changed from Contract terms in Standard Contract template
1243 -- DELIVERABLES_CHANGED
1244 -- Deliverables are changed from Contract terms in Standard Contract template
1245 -- ALL_CHANGED
1246 -- Deliverables and Articles are changed from Contract terms in
1247 -- Standard Contract template
1248 --Notes:
1249 -- None
1250 --Testing:
1251 -- Test this API by Changing contract terms, by not changing Contract terms
1252 -- and for POs which are not Procurement Contract.
1253 -- For more details refer to UT test scripts in DLD
1254 --End of Comments
1255 -------------------------------------------------------------------------------
1256 PROCEDURE IS_STANDARD_CONTRACT (itemtype IN VARCHAR2,
1257 itemkey IN VARCHAR2,
1258 actid IN NUMBER,
1259 funcmode IN VARCHAR2,
1260 result OUT NOCOPY VARCHAR2) IS
1261
1262 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1263 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1264 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1265 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1266
1267 l_return_status VARCHAR2(1);
1268 l_msg_data VARCHAR2(2000);
1269 l_msg_count NUMBER;
1270
1271 l_doc_string VARCHAR2(200);
1272 l_preparer_user_name VARCHAR2(100);
1273
1274 l_contracts_call_exception exception;
1275 BEGIN
1276 IF (g_po_wf_debug = 'Y') THEN
1277 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1278 '10: Start function IS_STANDARD_CONTRACT ');
1279 END IF;
1280 -- Do nothing in cancel or timeout mode
1281 IF (funcmode <> WF_ENGINE.eng_run) then
1282 result := WF_ENGINE.eng_null;
1283 return;
1284 END IF;
1285 result := 'NO_CHANGE';
1286 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1287 itemtype => itemtype,
1288 itemkey => itemkey,
1289 aname => 'CONTERMS_EXIST_FLAG');
1290 IF (l_conterms_yn = 'Y') then
1291
1292 -- get other needed values from attribs
1293 get_wf_params(itemtype =>itemtype,
1294 itemkey =>itemkey,
1295 x_po_header_id =>l_po_header_id,
1296 x_po_doc_type =>l_po_doc_type,
1297 x_po_doc_subtype =>l_po_doc_subtype);
1298
1299 --Call contracts to find out if contract terms deviated from standard template
1300
1301 result :=OKC_TERMS_UTIL_GRP.Deviation_From_Standard(
1302 p_api_version => 1.0,
1303 p_doc_id => l_po_header_id,
1304 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1305 x_return_status => l_return_status,
1306 x_msg_data => l_msg_data,
1307 x_msg_count => l_msg_count);
1308 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1309
1310 IF (g_po_wf_debug = 'Y') THEN
1311 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1312 '100: returned value from Contracts'||result);
1313 END IF;
1314
1315 ELSE
1316 RAISE l_Contracts_call_exception;
1317 END IF; -- Return status from contracts
1318
1319
1320 ELSE -- if no conterms
1321 IF (g_po_wf_debug = 'Y') THEN
1322 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1323 '140:Not a Procurement contract');
1324 END IF;
1325 END IF; -- if conterms exist
1326
1327 IF (g_po_wf_debug = 'Y') THEN
1328 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1329 '200 End: IS_STANDARD_CONTRACT ');
1330 END IF;
1331
1332
1333 EXCEPTION
1334 -- Handle Exceptions and re raise
1335 WHEN l_contracts_call_exception then
1336 IF (g_po_wf_debug = 'Y') THEN
1337 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1338 '250: End contracts_call_exception: IS_STANDARD_CONTRACT ');
1339 show_error(itemtype => itemtype,
1340 itemkey => itemkey,
1341 p_api_name =>'OKC_TERMS_UTIl_GRP.DEVIATION_FROM_STANDARD',
1342 p_return_status => l_return_status);
1343 END IF;
1344 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1345 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1346 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_STANDARD_CONTRACT', 'l_contracts_call_Exception');
1347
1348 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1349 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1350 'PO_CONTERMS_WF_PVT.IS_STANDARD_CONTRACT');
1351 RAISE;
1352
1353 WHEN OTHERS THEN
1354 IF (g_po_wf_debug = 'Y') THEN
1355 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1356 '300: End IN Exception: IS_STANDARD_CONTRACT ');
1357 END IF;
1358 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1359 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1360 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_STANDARD_CONTRACT', 'l_contracts_call_Exception');
1361
1362 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1363 l_doc_string, sqlerrm,
1364 'PO_CONTERMS_WF_PVT.IS_STANDARD_CONTRACT');
1365 RAISE;
1366
1367 END IS_STANDARD_CONTRACT;
1368 -------------------------------------------------------------------------------
1369 --Start of Comments
1370 --Name: IS_CONTRACT_TEMPLATE_EXPIRED
1371 --Pre-reqs:
1372 -- Contracts package stubs should be there
1373 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1374 --Modifies:
1375 -- None
1376 --Locks:
1377 -- None
1378 --Function:
1379 -- This API will be called by approval workflow Function IS_CONTRACT_TEMPLATE_EXPIRED
1380 -- to determine if Contract terms template being used has expired or not
1381 --Parameters:
1382 --IN:
1383 --itemtype
1384 -- Standard workflow Parameter.
1385 --itemkey
1386 -- Standard workflow parameter
1387 --actid
1388 -- Standard workflow parameter
1389 --funcmode
1390 -- Standard workflow parameter
1391 --OUT:
1392 --Result
1393 -- Result of the call
1394 -- Possible Return Values:
1395 -- Y
1396 -- Yes- The template has expired
1397 -- N
1398 -- No- The template is not expired
1399 --Notes:
1400 -- None
1401 --Testing:
1402 -- Test this API by using expired and effective templates
1403 -- For more details refer to UT test scripts in DLD
1404 --End of Comments
1405 -------------------------------------------------------------------------------
1406 PROCEDURE IS_CONTRACT_TEMPLATE_EXPIRED(itemtype IN VARCHAR2,
1407 itemkey IN VARCHAR2,
1408 actid IN NUMBER,
1409 funcmode IN VARCHAR2,
1410 result OUT NOCOPY VARCHAR2) IS
1411
1412 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1413 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1414 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1415 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1416
1417 l_return_status VARCHAR2(1);
1418 l_msg_data VARCHAR2(2000);
1419 l_msg_count NUMBER;
1420
1421 l_doc_string VARCHAR2(200);
1422 l_preparer_user_name VARCHAR2(100);
1423
1424 l_contracts_call_exception exception;
1425 BEGIN
1426 IF (g_po_wf_debug = 'Y') THEN
1427 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1428 '10: Start function IS_CONTRACT_TEMPLATE_EXPIRED ');
1429 END IF;
1430 -- Do nothing in cancel or timeout mode
1431 IF (funcmode <> WF_ENGINE.eng_run) then
1432 result := WF_ENGINE.eng_null;
1433 return;
1434 END IF;
1435 result := 'N';
1436 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1437 itemtype => itemtype,
1438 itemkey => itemkey,
1439 aname => 'CONTERMS_EXIST_FLAG');
1440 IF (l_conterms_yn = 'Y') then
1441
1442 -- get other needed values from attribs
1443 get_wf_params(itemtype =>itemtype,
1444 itemkey =>itemkey,
1445 x_po_header_id =>l_po_header_id,
1446 x_po_doc_type =>l_po_doc_type,
1447 x_po_doc_subtype =>l_po_doc_subtype);
1448
1449 --Call contracts to find out if contract template expired
1450 result :=OKC_TERMS_UTIl_GRP.IS_TEMPLATE_EXPIRED(
1451 p_api_version => 1.0,
1452 p_doc_id => l_po_header_id,
1453 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1454 x_return_status => l_return_status,
1455 x_msg_data => l_msg_data,
1456 x_msg_count => l_msg_count);
1457 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1458
1459 IF (g_po_wf_debug = 'Y') THEN
1460 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1461 '100: returned value from Contracts'||result);
1462 END IF;
1463
1464 ELSE
1465 RAISE l_Contracts_call_exception;
1466 END IF; -- Return status from contracts
1467
1468
1469 ELSE -- if no conterms
1470 IF (g_po_wf_debug = 'Y') THEN
1471 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1472 '140:Not a Procurement contract');
1473 END IF;
1474 END IF; -- if conterms exist
1475
1476 IF (g_po_wf_debug = 'Y') THEN
1477 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1478 '200 End: IS_CONTRACT_TEMPLATE_EXPIRED ');
1479 END IF;
1480
1481
1482 EXCEPTION
1483 -- Handle Exceptions and re raise
1484 WHEN l_contracts_call_exception then
1485 IF (g_po_wf_debug = 'Y') THEN
1486 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1487 '250: End contracts_call_exception: IS_CONTRACT_TEMPLATE_EXPIRED ');
1488 show_error(itemtype => itemtype,
1489 itemkey => itemkey,
1490 p_api_name =>'OKC_TERMS_UTIl_GRP.IS_CONTRACT_TEMPLATE_EXPIRED',
1491 p_return_status => l_return_status);
1492 END IF;
1493 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1494 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1495 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_TEMPLATE_EXPIRED', 'l_contracts_call_Exception');
1496
1497 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1498 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1499 'PO_CONTERMS_WF_PVT.IS_CONTRACT_TEMPLATE_EXPIRED');
1500 RAISE;
1501
1502 WHEN OTHERS THEN
1503 IF (g_po_wf_debug = 'Y') THEN
1504 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1505 '300: End IN Exception: IS_CONTRACT_TEMPLATE_EXPIRED ');
1506 END IF;
1507 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1508 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1509 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_TEMPLATE_EXPIRED', 'l_contracts_call_Exception');
1510
1511 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1512 l_doc_string, sqlerrm,
1513 'PO_CONTERMS_WF_PVT.IS_CONTRACT_TEMPLATE_EXPIRED');
1514 RAISE;
1515
1516 END IS_CONTRACT_TEMPLATE_EXPIRED;
1517
1518 -------------------------------------------------------------------------------
1519 --Start of Comments
1520 --Name: IS_CONTRACT_ARTICLES_EXIST
1521 --Pre-reqs:
1522 -- Contracts package stubs should be there
1523 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1524 --Modifies:
1525 -- None
1526 --Locks:
1527 -- None
1528 --Function:
1529 -- This API will be called by approval workflow Function IS_CONTRACT_ARTICLES_EXIST
1530 -- to determine if Contract terms have articles attached to Purchase Order
1531 --Parameters:
1532 --IN:
1533 --itemtype
1534 -- Standard workflow Parameter.
1535 --itemkey
1536 -- Standard workflow parameter
1537 --actid
1538 -- Standard workflow parameter
1539 --funcmode
1540 -- Standard workflow parameter
1541 --OUT:
1542 --Result
1543 -- Result of the call
1544 -- Possible Return Values:
1545 -- NONE
1546 -- There are no articles attached to this purchase order
1547 -- ONLY_STANDARD
1548 -- Only standard Articles exist on this purchase order
1549 -- NON_STANDARD
1550 -- Standard as well as non standard Articles exist on this purchase order
1551 --Notes:
1552 -- None
1553 --Testing:
1554 -- For more details refer to UT test scripts in DLD
1555 --End of Comments
1556 -------------------------------------------------------------------------------
1557 PROCEDURE IS_CONTRACT_ARTICLES_EXIST (itemtype IN VARCHAR2,
1558 itemkey IN VARCHAR2,
1559 actid IN NUMBER,
1560 funcmode IN VARCHAR2,
1561 result OUT NOCOPY VARCHAR2) IS
1562
1563 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1564 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1565 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1566 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1567
1568 l_return_status VARCHAR2(1);
1569 l_msg_data VARCHAR2(2000);
1570 l_msg_count NUMBER;
1571
1572 l_doc_string VARCHAR2(200);
1573 l_preparer_user_name VARCHAR2(100);
1574
1575 l_contracts_call_exception exception;
1576 BEGIN
1577 IF (g_po_wf_debug = 'Y') THEN
1578 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1579 '10: Start function IS_CONTRACT_ARTICLES_EXIST ');
1580 END IF;
1581 -- Do nothing in cancel or timeout mode
1582 IF (funcmode <> WF_ENGINE.eng_run) then
1583 result := WF_ENGINE.eng_null;
1584 return;
1585 END IF;
1586 result := 'NONE';
1587 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1588 itemtype => itemtype,
1589 itemkey => itemkey,
1590 aname => 'CONTERMS_EXIST_FLAG');
1591 IF (l_conterms_yn = 'Y') then
1592
1593 -- get other needed values from attribs
1594 get_wf_params(itemtype =>itemtype,
1595 itemkey =>itemkey,
1596 x_po_header_id =>l_po_header_id,
1597 x_po_doc_type =>l_po_doc_type,
1598 x_po_doc_subtype =>l_po_doc_subtype);
1599
1600 --Call contracts to find out if contract articles attached
1601 result :=OKC_TERMS_UTIl_GRP.IS_ARTICLE_EXIST(
1602 p_api_version => 1.0,
1603 p_doc_id => l_po_header_id,
1604 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1605 x_return_status => l_return_status,
1606 x_msg_data => l_msg_data,
1607 x_msg_count => l_msg_count);
1608 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1609
1610 IF (g_po_wf_debug = 'Y') THEN
1611 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1612 '100: returned value from Contracts'||result);
1613 END IF;
1614
1615 ELSE
1616 RAISE l_Contracts_call_exception;
1617 END IF; -- Return status from contracts
1618
1619
1620 ELSE -- if no conterms
1621 IF (g_po_wf_debug = 'Y') THEN
1622 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1623 '140:Not a Procurement contract');
1624 END IF;
1625 END IF; -- if conterms exist
1626
1627 IF (g_po_wf_debug = 'Y') THEN
1628 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1629 '200 End: IS_CONTRACT_ARTICLES_EXIST ');
1630 END IF;
1631
1632
1633 EXCEPTION
1634 -- Handle Exceptions and re raise
1635 WHEN l_contracts_call_exception then
1636 IF (g_po_wf_debug = 'Y') THEN
1637 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1638 '250: End contracts_call_exception: IS_CONTRACT_ARTICLES_EXIST ');
1639 show_error(itemtype => itemtype,
1640 itemkey => itemkey,
1641 p_api_name =>'OKC_TERMS_UTIl_GRP.IS_ARTICLE_EXIST',
1642 p_return_status => l_return_status);
1643 END IF;
1644 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1645 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1646 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_ARTICLES_EXIST', 'l_contracts_call_Exception');
1647
1648 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1649 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1650 'PO_CONTERMS_WF_PVT.IS_CONTRACT_ARTICLES_EXIST');
1651 RAISE;
1652
1653 WHEN OTHERS THEN
1654 IF (g_po_wf_debug = 'Y') THEN
1655 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1656 '300: End IN Exception: IS_CONTRACT_ARTICLES_EXIST ');
1657 END IF;
1658 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1659 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1660 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_ARTICLES_EXIST', 'l_contracts_call_Exception');
1661
1662 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1663 l_doc_string, sqlerrm,
1664 'PO_CONTERMS_WF_PVT.IS_CONTRACT_ARTICLES_EXIST');
1665 RAISE;
1666
1667
1668 END IS_CONTRACT_ARTICLES_EXIST;
1669
1670 -------------------------------------------------------------------------------
1671 --Start of Comments
1672 --Name: IS_CONTRACT_ARTICLES_AMENDED
1673 --Pre-reqs:
1674 -- Contracts package stubs should be there
1675 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1676 --Modifies:
1677 -- None
1678 --Locks:
1679 -- None
1680 --Function:
1681 -- This API will be called by approval workflow Function IS_CONTRACT_ARTICLES_AMENDED
1682 -- to determine if contract articles were amended in this revision
1683 --Parameters:
1684 --IN:
1685 --itemtype
1686 -- Standard workflow Parameter.
1687 --itemkey
1688 -- Standard workflow parameter
1689 --actid
1690 -- Standard workflow parameter
1691 --funcmode
1692 -- Standard workflow parameter
1693 --OUT:
1694 --Result
1695 -- Result of the call
1696 -- Possible Return Values:
1697 -- NONE
1698 -- No articles were amended in this revision of purchase order
1699 -- ONLY_STANDARD
1700 -- Only standard Articles were amended in this revision of purchase order
1701 -- NON_STANDARD
1702 -- Standard as well as non standard Articles were amended in this revision of purchase order
1703 --Notes:
1704 -- None
1705 --Testing:
1706 -- For more details refer to UT test scripts in DLD
1707 --End of Comments
1708 -------------------------------------------------------------------------------
1709 PROCEDURE IS_CONTRACT_ARTICLES_AMENDED(itemtype IN VARCHAR2,
1710 itemkey IN VARCHAR2,
1711 actid IN NUMBER,
1712 funcmode IN VARCHAR2,
1713 result OUT NOCOPY VARCHAR2) IS
1714
1715 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1716 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1717 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1718 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1719
1720 l_return_status VARCHAR2(1);
1721 l_msg_data VARCHAR2(2000);
1722 l_msg_count NUMBER;
1723
1724 l_doc_string VARCHAR2(200);
1725 l_preparer_user_name VARCHAR2(100);
1726
1727 l_contracts_call_exception exception;
1728 BEGIN
1729 IF (g_po_wf_debug = 'Y') THEN
1730 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1731 '10: Start function IS_CONTRACT_ARTICLES_AMENDED');
1732 END IF;
1733 -- Do nothing in cancel or timeout mode
1734 IF (funcmode <> WF_ENGINE.eng_run) then
1735 result := WF_ENGINE.eng_null;
1736 return;
1737 END IF;
1738 result := 'NON_STANDARD';
1739 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1740 itemtype => itemtype,
1741 itemkey => itemkey,
1742 aname => 'CONTERMS_EXIST_FLAG');
1743 IF (l_conterms_yn = 'Y') then
1744
1745 -- get other needed values from attribs
1746 get_wf_params(itemtype =>itemtype,
1747 itemkey =>itemkey,
1748 x_po_header_id =>l_po_header_id,
1749 x_po_doc_type =>l_po_doc_type,
1750 x_po_doc_subtype =>l_po_doc_subtype);
1751
1752 --Call contracts to find out if contract articles were amended in this revision
1753 result :=OKC_TERMS_UTIl_GRP.IS_ARTICLE_AMENDED(
1754 p_api_version => 1.0,
1755 p_doc_id => l_po_header_id,
1756 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1757 x_return_status => l_return_status,
1758 x_msg_data => l_msg_data,
1759 x_msg_count => l_msg_count);
1760 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1761
1762 IF (g_po_wf_debug = 'Y') THEN
1763 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1764 '100: returned value from Contracts'||result);
1765 END IF;
1766
1767 ELSE
1768 RAISE l_Contracts_call_exception;
1769 END IF; -- Return status from contracts
1770
1771
1772 ELSE -- if no conterms
1773 IF (g_po_wf_debug = 'Y') THEN
1774 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1775 '140:Not a Procurement contract');
1776 END IF;
1777 END IF; -- if conterms exist
1778
1779 IF (g_po_wf_debug = 'Y') THEN
1780 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1781 '200 End: IS_CONTRACT_ARTICLES_AMENDED ');
1782 END IF;
1783
1784
1785 EXCEPTION
1786 -- Handle Exceptions and re raise
1787 WHEN l_contracts_call_exception then
1788 IF (g_po_wf_debug = 'Y') THEN
1789 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1790 '250: End contracts_call_exception: IS_CONTRACT_ARTICLES_AMENDED ');
1791 show_error(itemtype => itemtype,
1792 itemkey => itemkey,
1793 p_api_name =>'OKC_TERMS_UTIl_GRP.IS_ARTICLE_AMENDED',
1794 p_return_status => l_return_status);
1795 END IF;
1796 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1797 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1798 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_ARTICLES_AMENDED', 'l_contracts_call_Exception');
1799
1800 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1801 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1802 'PO_CONTERMS_WF_PVT.IS_CONTRACT_ARTICLES_AMENDED');
1803 RAISE;
1804
1805 WHEN OTHERS THEN
1806 IF (g_po_wf_debug = 'Y') THEN
1807 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1808 '300: End IN Exception: IS_CONTRACT_ARTICLES_AMENDED ');
1809 END IF;
1810 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1811 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1812 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_ARTICLES_AMENDED', 'l_contracts_call_Exception');
1813
1814 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1815 l_doc_string, sqlerrm,
1816 'PO_CONTERMS_WF_PVT.IS_CONTRACT_ARTICLES_AMENDED');
1817 RAISE;
1818
1819
1820 END IS_CONTRACT_ARTICLES_AMENDED;
1821
1822
1823 -------------------------------------------------------------------------------
1824 --Start of Comments
1825 --Name: IS_CONTRACT_DELIVRABLS_EXIST
1826 --Pre-reqs:
1827 -- Contracts package stubs should be there
1828 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1829 --Modifies:
1830 -- None
1831 --Locks:
1832 -- None
1833 --Function:
1834 -- This API will be called by approval workflow Function IS_CONTRACT_DELIVRABLS_EXIST
1835 -- to determine if contract deliverables are attached to PO
1836 --Parameters:
1837 --IN:
1838 --itemtype
1839 -- Standard workflow Parameter.
1840 --itemkey
1841 -- Standard workflow parameter
1842 --actid
1843 -- Standard workflow parameter
1844 --funcmode
1845 -- Standard workflow parameter
1846 --OUT:
1847 --Result
1848 -- Result of the call
1849 -- Possible Return Values:
1850 -- NONE
1851 -- No deliverables are attached
1852 -- CONTRACTUAL
1853 -- Only contractual deliverables are attached
1854 -- INTERNAL
1855 -- Only Internal deliverables are attached
1856 -- ALL
1857 -- Contractual as well as Internal deliverables are attached
1858 --Notes:
1859 -- None
1860 --Testing:
1861 -- For more details refer to UT test scripts in DLD
1862 --End of Comments
1863 -------------------------------------------------------------------------------
1864 PROCEDURE IS_CONTRACT_DELIVRABLS_EXIST(itemtype IN VARCHAR2,
1865 itemkey IN VARCHAR2,
1866 actid IN NUMBER,
1867 funcmode IN VARCHAR2,
1868 result OUT NOCOPY VARCHAR2) IS
1869
1870 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
1871 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
1872 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
1873 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
1874
1875 l_return_status VARCHAR2(1);
1876 l_msg_data VARCHAR2(2000);
1877 l_msg_count NUMBER;
1878
1879 l_doc_string VARCHAR2(200);
1880 l_preparer_user_name VARCHAR2(100);
1881
1882 l_contracts_call_exception exception;
1883 BEGIN
1884 IF (g_po_wf_debug = 'Y') THEN
1885 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1886 '10: Start function IS_CONTRACT_DELIVRABLS_EXIST');
1887 END IF;
1888 -- Do nothing in cancel or timeout mode
1889 IF (funcmode <> WF_ENGINE.eng_run) then
1890 result := WF_ENGINE.eng_null;
1891 return;
1892 END IF;
1893 result := 'ALL';
1894 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
1895 itemtype => itemtype,
1896 itemkey => itemkey,
1897 aname => 'CONTERMS_EXIST_FLAG');
1898 IF (l_conterms_yn = 'Y') then
1899
1900 -- get other needed values from attribs
1901 get_wf_params(itemtype =>itemtype,
1902 itemkey =>itemkey,
1903 x_po_header_id =>l_po_header_id,
1904 x_po_doc_type =>l_po_doc_type,
1905 x_po_doc_subtype =>l_po_doc_subtype);
1906
1907 --Call contracts to find out if contract deliverables were amended in this revision
1908 result :=OKC_TERMS_UTIL_GRP.Is_Deliverable_Exist(
1909 p_api_version => 1.0,
1910 p_doc_id => l_po_header_id,
1911 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
1912 x_return_status => l_return_status,
1913 x_msg_data => l_msg_data,
1914 x_msg_count => l_msg_count);
1915 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1916 IF (result = 'CONTRACTUAL_AND_INTERNAL') THEN
1917 result := 'ALL';
1918 END IF;
1919 IF (g_po_wf_debug = 'Y') THEN
1920 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1921 '100: returned value from Contracts'||result);
1922 END IF;
1923
1924 ELSE
1925 RAISE l_Contracts_call_exception;
1926 END IF; -- Return status from contracts
1927
1928
1929 ELSE -- if no conterms
1930 IF (g_po_wf_debug = 'Y') THEN
1931 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1932 '140:Not a Procurement contract');
1933 END IF;
1934 END IF; -- if conterms exist
1935
1936 IF (g_po_wf_debug = 'Y') THEN
1937 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1938 '200 End: IS_CONTRACT_DELIVRABLS_EXIST ');
1939 END IF;
1940
1941
1942 EXCEPTION
1943 -- Handle Exceptions and re raise
1944 WHEN l_contracts_call_exception then
1945 IF (g_po_wf_debug = 'Y') THEN
1946 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1947 '250: End contracts_call_exception: IS_CONTRACT_DELIVRABLS_EXIST ');
1948 show_error(itemtype => itemtype,
1949 itemkey => itemkey,
1950 p_api_name =>'OKC_TERMS_UTIl_GRP.Is_Deliverable_Exist',
1951 p_return_status => l_return_status);
1952 END IF;
1953 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1954 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1955 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_DELIVRABLS_EXIST', 'l_contracts_call_Exception');
1956
1957 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1958 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
1959 'PO_CONTERMS_WF_PVT.IS_CONTRACT_DELIVRABLS_EXIST');
1960 RAISE;
1961
1962 WHEN OTHERS THEN
1963 IF (g_po_wf_debug = 'Y') THEN
1964 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1965 '300: End IN Exception: IS_CONTRACT_DELIVRABLS_EXIST ');
1966 END IF;
1967 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1968 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1969 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_DELIVRABLS_EXIST', 'l_contracts_call_Exception');
1970
1971 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1972 l_doc_string, sqlerrm,
1973 'PO_CONTERMS_WF_PVT.IS_CONTRACT_DELIVRABLS_EXIST');
1974 RAISE;
1975
1976
1977 END IS_CONTRACT_DELIVRABLS_EXIST;
1978
1979 -------------------------------------------------------------------------------
1980 --Start of Comments
1981 --Name: IS_CONTRACT_DELIVRABLS_AMENDED
1982 --Pre-reqs:
1983 -- Contracts package stubs should be there
1984 -- Runtime poxwfpoa.wft 115.91( Conterms_exist_flag attribute defined)
1985 --Modifies:
1986 -- None
1987 --Locks:
1988 -- None
1989 --Function:
1990 -- This API will be called by approval workflow Function IS_CONTRACT_DELIVRABLS_AMENDED
1991 -- to determine if contract deliverables were amended in this revision
1992 --Parameters:
1993 --IN:
1994 --itemtype
1995 -- Standard workflow Parameter.
1996 --itemkey
1997 -- Standard workflow parameter
1998 --actid
1999 -- Standard workflow parameter
2000 --funcmode
2001 -- Standard workflow parameter
2002 --OUT:
2003 --Result
2004 -- Result of the call
2005 -- Possible Return Values:
2006 -- NONE
2007 -- No deliverables were amended in this revision of purchase order
2008 -- CONTRACTUAL
2009 -- Only contractual deliverables were amended in this revision of purchase order
2010 -- INTERNAL
2011 -- Only Internal deliverables were amended in this revision of purchase order
2012 -- ALL
2013 -- Contractual as well as Internal deliverables were amended in this revision of purchase order
2014 --Notes:
2015 -- None
2016 --Testing:
2017 -- For more details refer to UT test scripts in DLD
2018 --End of Comments
2019 -------------------------------------------------------------------------------
2020 PROCEDURE IS_CONTRACT_DELIVRABLS_AMENDED(itemtype IN VARCHAR2,
2021 itemkey IN VARCHAR2,
2022 actid IN NUMBER,
2023 funcmode IN VARCHAR2,
2024 result OUT NOCOPY VARCHAR2) IS
2025
2026 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
2027 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N';
2028 l_po_doc_type PO_Document_Types_all_B.Document_type_code%Type;
2029 l_po_doc_subtype PO_Document_Types_all_B.Document_subtype%Type;
2030
2031 l_return_status VARCHAR2(1);
2032 l_msg_data VARCHAR2(2000);
2033 l_msg_count NUMBER;
2034
2035 l_doc_string VARCHAR2(200);
2036 l_preparer_user_name VARCHAR2(100);
2037
2038 l_contracts_call_exception exception;
2039 BEGIN
2040 IF (g_po_wf_debug = 'Y') THEN
2041 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2042 '10: Start function IS_CONTRACT_ARTICLES_AMENDED');
2043 END IF;
2044 -- Do nothing in cancel or timeout mode
2045 IF (funcmode <> WF_ENGINE.eng_run) then
2046 result := WF_ENGINE.eng_null;
2047 return;
2048 END IF;
2049 result := 'ALL';
2050 l_conterms_yn := PO_wf_Util_Pkg.GetItemAttrText(
2051 itemtype => itemtype,
2052 itemkey => itemkey,
2053 aname => 'CONTERMS_EXIST_FLAG');
2054 IF (l_conterms_yn = 'Y') then
2055
2056 -- get other needed values from attribs
2057 get_wf_params(itemtype =>itemtype,
2058 itemkey =>itemkey,
2059 x_po_header_id =>l_po_header_id,
2060 x_po_doc_type =>l_po_doc_type,
2061 x_po_doc_subtype =>l_po_doc_subtype);
2062
2063 --Call contracts to find out if contract deliverables were amended in this revision
2064 result :=OKC_TERMS_UTIL_GRP.Is_Deliverable_Amended(
2065 p_api_version => 1.0,
2066 p_doc_id => l_po_header_id,
2067 p_doc_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype(l_po_doc_subtype),
2068 x_return_status => l_return_status,
2069 x_msg_data => l_msg_data,
2070 x_msg_count => l_msg_count);
2071 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
2072 IF (result = 'CONTRACTUAL_AND_INTERNAL') THEN
2073 result := 'ALL';
2074 END IF;
2075 IF (g_po_wf_debug = 'Y') THEN
2076 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2077 '100: returned value from Contracts'||result);
2078 END IF;
2079
2080 ELSE
2081 RAISE l_Contracts_call_exception;
2082 END IF; -- Return status from contracts
2083
2084
2085 ELSE -- if no conterms
2086 IF (g_po_wf_debug = 'Y') THEN
2087 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2088 '140:Not a Procurement contract');
2089 END IF;
2090 END IF; -- if conterms exist
2091
2092 IF (g_po_wf_debug = 'Y') THEN
2093 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2094 '200 End: IS_CONTRACT_DELIVRABLS_AMENDED ');
2095 END IF;
2096
2097
2098 EXCEPTION
2099 -- Handle Exceptions and re raise
2100 WHEN l_contracts_call_exception then
2101 IF (g_po_wf_debug = 'Y') THEN
2102 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2103 '250: End contracts_call_exception: IS_CONTRACT_DELIVRABLS_AMENDED ');
2104 show_error(itemtype => itemtype,
2105 itemkey => itemkey,
2106 p_api_name =>'OKC_TERMS_UTIl_GRP.IS_DELIVERABLE_AMENDED',
2107 p_return_status => l_return_status);
2108 END IF;
2109 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2110 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2111 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_DELIVRABLS_AMENDED', 'l_contracts_call_Exception');
2112
2113 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
2114 l_doc_string, FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' ),
2115 'PO_CONTERMS_WF_PVT.IS_CONTRACT_DELIVRABLS_AMENDED');
2116 RAISE;
2117
2118 WHEN OTHERS THEN
2119 IF (g_po_wf_debug = 'Y') THEN
2120 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2121 '300: End IN Exception: IS_CONTRACT_DELIVRABLS_AMENDED ');
2122 END IF;
2123 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2124 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2125 wf_core.context('PO_CONTERMS_WF_PVT', 'IS_CONTRACT_DELIVRABLS_AMENDED', 'l_contracts_call_Exception');
2126
2127 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
2128 l_doc_string, sqlerrm,
2129 'PO_CONTERMS_WF_PVT.IS_CONTRACT_DELIVRABLS_AMENDED');
2130 RAISE;
2131
2132
2133 END IS_CONTRACT_DELIVRABLS_AMENDED;
2134
2135
2136
2137
2138 -------------------------------------------------------------------------------
2139 --Start of Comments
2140 --Name: cancel_deliverables
2141 --Pre-reqs:
2142 -- None.
2143 --Modifies:
2144 -- Cancels deliverables recorded in the OKC schema, on the Purchasing document
2145 --Locks:
2146 -- None.
2147 --Function:
2148 -- A wrapper procedure to call Contracts API to cancel deliverables on a PO CONTRACT
2149 --Parameters:
2150 --IN:
2151 --p_bus_doc_id
2152 -- PO header id
2153 --p_bus_doc_type
2154 -- PA - Purchase Agreement
2155 -- PO - Purchase Order
2156 --p_bus_doc_subtype
2157 -- STANDARD
2158 -- BALNKET
2159 -- CONTRACT
2160 --p_bus_doc_version
2161 -- Document revision number
2162 --p_event_code
2163 -- One of the seeded PO Contracts event
2164 -- PO_CLOSE - Finally Close PO
2165 -- PO_CANCEL - Cancel PO
2166 --p_event_date
2167 -- Date on which the PO Contract event occurred. Default is SYSDATE
2168 --p_busdocdates_tbl
2169 -- OKC_MANAGE_DELIVERABLES_GRP.busdocdates_tbl_type table type
2170 -- is a table of dates based events on the PO to resolve deliverables
2171 -- that are based on PO dates (ex. Start date).
2172 --OUT:
2173 --x_return_status
2174 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected/other error
2175 -- FND_API.G_RET_STS_SUCCESS - for successful execution of the API
2176 --Testing:
2177 --
2178 --Notes:
2179 -- This procedure should be called when it is needed to cancel deliverables
2180 -- on the Purchasing document. It should be called instead of calling the OKC
2181 -- API directly.
2182 --End of Comments
2183 -------------------------------------------------------------------------------
2184
2185 PROCEDURE cancel_deliverables (
2186 p_bus_doc_id IN NUMBER
2187 ,p_bus_doc_type IN VARCHAR2
2188 ,p_bus_doc_subtype IN VARCHAR2
2189 ,p_bus_doc_version IN NUMBER
2190 ,p_event_code IN VARCHAR2
2191 ,p_event_date IN DATE
2192 ,p_busdocdates_tbl IN EVENT_TBL_TYPE
2193 ,x_return_status OUT NOCOPY VARCHAR2
2194 ) IS
2195
2196 l_bus_doc_version PO_HEADERS_ALL.revision_num%TYPE;
2197 l_contracts_document_type VARCHAR2(150);
2198
2199 l_api_name VARCHAR2(30) := 'cancel_deliverables';
2200 l_msg_data VARCHAR2(2000);
2201 l_msg_count NUMBER;
2202 l_return_status VARCHAR2(1);
2203
2204 BEGIN
2205 -- initialize return status
2206 x_return_status := FND_API.G_RET_STS_SUCCESS;
2207
2208
2209 -- select the business document version if passed null
2210 IF (p_bus_doc_version IS NULL) THEN
2211 -- SQL what: select the document version
2212 -- SQL why : to cancel deliverables on the current version
2213 -- SQL join: po_header_id
2214 SELECT revision_num
2215 INTO l_bus_doc_version
2216 FROM po_headers_all
2217 WHERE po_header_id = p_bus_doc_id;
2218 ELSE
2219 l_bus_doc_version := p_bus_doc_version;
2220 END IF;
2221
2222 l_contracts_document_type := p_bus_doc_type||'_'||p_bus_doc_subtype;
2223
2224 IF (g_fnd_debug = 'Y') THEN
2225 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2226 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE
2227 ,module => g_module_prefix || l_api_name
2228 ,message => 'Before calling contracts API to cancel deliverables');
2229 END IF;
2230 END IF;
2231
2232 -- call to the actual API
2233 OKC_MANAGE_DELIVERABLES_GRP.cancelDeliverables(
2234 p_api_version => 1.0
2235 ,p_init_msg_list => FND_API.G_FALSE
2236 ,p_commit => FND_API.G_FALSE
2237 ,p_bus_doc_id => p_bus_doc_id
2238 ,p_bus_doc_type => l_contracts_document_type
2239 ,p_bus_doc_version => l_bus_doc_version
2240 ,p_event_code => p_event_code
2241 ,p_event_date => p_event_date
2242 ,p_bus_doc_date_events_tbl => p_busdocdates_tbl
2243 ,x_msg_data => l_msg_data
2244 ,x_msg_count => l_msg_count
2245 ,x_return_status => l_return_status);
2246
2247 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2249 END IF;
2250
2251 IF (g_fnd_debug = 'Y') THEN
2252 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2253 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE
2254 ,module => g_module_prefix || l_api_name
2255 ,message => 'Deliverables cancelled successfully');
2256 END IF;
2257 END IF;
2258
2259 EXCEPTION
2260 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2261 IF (g_fnd_debug = 'Y') THEN
2262 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2263 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE
2264 ,module => g_module_prefix || l_api_name
2265 ,message => l_msg_data);
2266 END IF;
2267 END IF;
2268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2269 WHEN OTHERS THEN
2270 ROLLBACK;
2271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2272 IF (g_fnd_debug = 'Y') THEN
2273 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2274 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE
2275 ,module => g_module_prefix || l_api_name
2276 ,message => 'Others Exception');
2277 END IF;
2278 END IF;
2279
2280 END cancel_deliverables;
2281
2282 /* CONTERMS FPJ END */
2283 --<CONTERMS FPJ END>
2284 End PO_CONTERMS_WF_PVT;
[Home] [Help]