[Home] [Help]
PACKAGE BODY: APPS.PO_MOD_CONTROL_PVT
Source
1 PACKAGE BODY PO_MOD_CONTROL_PVT AS
2 /* $Header: PO_MOD_CONTROL_PVT.plb 120.11.12020000.6 2013/04/12 22:21:48 rarajar ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=PO_LOG.get_package_base('po_mod_control_pvt');
5
6 procedure process_mod_control_action(p_draft_id in number,
7 p_action in varchar2,
8 p_po_header_id in number,
9 resultout out NOCOPY varchar2) is
10
11 d_api_name CONSTANT VARCHAR2(30) := 'process_mod_control_action';
12 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
13 d_position NUMBER;
14
15 l_po_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
16 l_po_draft_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
17 l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
18 l_record_already_exist_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
19
20 begin
21 d_position := 0;
22 IF (PO_LOG.d_proc) THEN
23 PO_LOG.proc_begin(d_module);
24 END IF;
25 resultout := FND_API.G_RET_STS_SUCCESS;
26
27 d_position := 10;
28
29 -- Call PO_DRAFTS_PVT.remove_draft_changes to remove all draft entries
30 PO_DRAFTS_PVT.remove_draft_changes(p_draft_id => p_draft_id,
31 p_exclude_ctrl_tbl => FND_API.G_TRUE,
32 x_return_status => resultout);
33
34 d_position := 20;
35 IF resultout = FND_API.G_RET_STS_SUCCESS THEN
36 -- If return status success, then call PO_HEADERS_DRAFT_PKG.sync_draft_from_txn
37 d_position := 30;
38 l_po_header_id_tbl.extend(1);
39 l_po_draft_id_tbl.extend(1);
40 l_delete_flag_tbl.extend(1);
41 l_po_header_id_tbl(1) := p_po_header_id;
42 l_po_draft_id_tbl(1) := p_draft_id;
43 l_delete_flag_tbl(1) := 'N';
44 d_position := 40;
45 PO_HEADERS_DRAFT_PKG.sync_draft_from_txn(l_po_header_id_tbl,
46 l_po_draft_id_tbl,
47 l_delete_flag_tbl,
48 l_record_already_exist_tbl);
49 END IF;
50
51 d_position := 50;
52 -- If return status success, then go ahead
53
54 update po_headers_draft_all
55 set control_action = p_action
56 where draft_id = p_draft_id;
57
58 d_position := 60;
59 resultout := FND_API.G_RET_STS_SUCCESS;
60
61 IF (PO_LOG.d_proc) THEN
62 PO_LOG.proc_end(d_module);
63 END IF;
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 PO_MESSAGE_S.add_exc_msg
68 ( p_pkg_name => d_pkg_name,
69 p_procedure_name => d_api_name || '.' || d_position
70 );
71
72 resultout := FND_API.G_RET_STS_UNEXP_ERROR;
73
74 end process_mod_control_action;
75
76
77 -- <Mod Project>: Withdraw the draft
78 PROCEDURE process_mod_withdrawn_action(p_draft_id in number,
79 status IN VARCHAR2,
80 result out NOCOPY VARCHAR2
81 )
82 IS
83 d_api_name CONSTANT VARCHAR2(30) := 'process_mod_withdraw_action';
84 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
85 d_position NUMBER;
86 l_conterms_exist_flag VARCHAR2(1);
87 l_po_header_id po_headers_draft_all.po_header_id%TYPE;
88 l_type_lookup_code po_headers_draft_all.type_lookup_code%TYPE;
89 l_contract_document_type VARCHAR2(30);
90 x_return_status VARCHAR2 (1) ;
91 x_msg_count NUMBER;
92 x_msg_data VARCHAR2(100) ;
93 l_item_key_tbl po_tbl_varchar100;
94 l_mod_number po_drafts.modification_number%TYPE;
95 l_item_status varchar2(10);
96 l_result VARCHAR2(10);
97 --bug 15905347 start
98 --l_withdrawn_par_dist_list PO_TBL_NUMBER;
99 --l_withdrawn_par_line_list PO_TBL_NUMBER;
100 l_withdrawn_line_list PO_TBL_NUMBER;
101 l_withdrawn_dist_list PO_TBL_NUMBER;
102 --bug 15905347 end
103
104 BEGIN
105
106 d_position := 0;
107 IF (PO_LOG.d_proc) THEN
108 PO_LOG.proc_begin(d_module);
109 END IF;
110 result := FND_API.G_RET_STS_SUCCESS;
111
112 d_position := 10;
113 PO_DRAFTS_PVT.update_draft_status(p_draft_id,status);
114
115 -- <Conc Mods Project START>
116 -- Delete locks
117 PO_DRAFT_MERGE_PKG.delete_entity_locks(
118 p_draft_id => p_draft_id,
119 x_return_status => x_return_status
120 );
121
122 -- Release OKC locks
123 SELECT NVL (l_conterms_exist_flag, 'N'), po_header_id, type_lookup_code
124 INTO l_conterms_exist_flag, l_po_header_id, l_type_lookup_code
125 FROM po_headers_draft_all
126 WHERE draft_id = p_draft_id;
127
128 IF l_conterms_exist_flag = 'Y' THEN
129 l_contract_document_type :=
130 PO_CONTERMS_UTL_GRP.GET_PO_CONTRACT_DOCTYPE_MOD(
131 p_sub_doc_type => l_type_lookup_code);
132 END IF;
133
134 d_position := 20;
135 OKC_K_ENTITY_LOCKS_GRP.RELEASE_LOCKS(
136 p_api_version => 1,
137 p_doc_type => l_contract_document_type,
138 p_doc_id => p_draft_id,
139 x_return_status => x_return_status,
140 x_msg_count => x_msg_count,
141 x_msg_data => x_msg_data
142 );
143
144 --delete the workflow (notification)process related to this modification
145 SELECT modification_number
146 INTO l_mod_number
147 FROM po_drafts
148 WHERE draft_id = p_draft_id;
149
150 BEGIN
151 SELECT item_key
152 BULK COLLECT INTO l_item_key_tbl
153 FROM wf_items wfi
154 WHERE wfi.user_key = To_Char(p_draft_id) AND item_type = 'POCMODNT';
155
156 FOR i IN 1..l_item_key_tbl.Count()
157 LOOP
158 wf_engine.ItemStatus(itemtype => 'POCMODNT', itemkey => l_item_key_tbl(i),status => l_item_status,result => l_result);
159 IF l_item_status <> 'COMPLETE' THEN
160 wf_engine.abortProcess(itemtype => 'POCMODNT', itemkey => l_item_key_tbl(i));
161 END IF;
162 END LOOP;
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 PO_DEBUG.debug_stmt(d_module,d_position,'No notification data found');
166 END;
167 -- <Conc Mods Project END>
168
169 --start bug 15905347
170 --When a mod is withdrawn, the associated req lines should be sent bact to the pool.Using the same logic for sending the reqs to pool as during the deletion of mod, in 'process_mod_delete_action'
171 -- commented the code specific to the PAR lines and added a common logic that will process the associated reqs for all mod lines regardless of PAR or non-PAR.
172 --Made call to update_header_dummy_req(p_draft_id => p_draft_id) to remove the dummy req in case of PAR. for non-PAR lines there will be no impact.
173
174 l_withdrawn_line_list := PO_TBL_NUMBER();
175 l_withdrawn_dist_list := PO_TBL_NUMBER();
176
177 -- Collect all withdrawn lines
178 SELECT po_line_id
179 BULK COLLECT INTO l_withdrawn_line_list
180 FROM po_lines_draft_all
181 WHERE draft_id = p_draft_id;
182
183 -- Collect all withdrawn distributions
184 SELECT po_distribution_id
185 BULK COLLECT INTO l_withdrawn_dist_list
186 FROM po_distributions_draft_all
187 WHERE draft_id = p_draft_id;
188
189 IF (PO_LOG.d_stmt) THEN
190 PO_LOG.stmt(d_module,d_position,'l_withdrawn_line_list',l_withdrawn_line_list);
191 PO_LOG.stmt(d_module,d_position,'l_withdrawn_dist_list',l_withdrawn_dist_list);
192 END IF;
193
194 update_header_dummy_req(p_draft_id => p_draft_id);
195
196 IF ( l_withdrawn_line_list.Count > 0) THEN
197 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_withdrawn_line_list, 'LINE');
198 END IF;
199
200 IF ( l_withdrawn_dist_list.Count > 0) THEN
201 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_withdrawn_dist_list, 'DISTRIBUTION MOD');
202 END IF;
203
204 -- Send the requisitions back to pool, if eleigible,
205 IF (l_withdrawn_line_list.Count > 0 OR l_withdrawn_dist_list.Count > 0) THEN
206 PO_REQ_LINES_SV.update_reqs_in_pool_flag;
207 END IF;
208
209 /*
210 --<PAR Project> : Return the header and line dummy reqs if any to DWB
211 -- Collect all PAR withdrawn lines
212 SELECT po_line_id
213 BULK COLLECT INTO l_withdrawn_par_line_list
214 FROM po_lines_draft_all pld
215 WHERE draft_id = p_draft_id
216 AND EXISTS (SELECT 'PAR Line' FROM po_lines_draft_all
217 WHERE mod_line_id = pld.po_line_id
218 );
219
220 -- Collect all PAR withdrawn distributions
221 SELECT po_distribution_id
222 BULK COLLECT INTO l_withdrawn_par_dist_list
223 FROM po_distributions_draft_all
224 WHERE draft_id = p_draft_id
225 AND par_distribution_id IS NOT NULL;
226
227 IF (PO_LOG.d_stmt) THEN
228 PO_LOG.stmt(d_module,d_position,'l_withdrawn_par_line_list',l_withdrawn_par_line_list);
229 PO_LOG.stmt(d_module,d_position,'l_withdrawn_par_dist_list',l_withdrawn_par_dist_list);
230 END IF;
231
232 update_header_dummy_req(p_draft_id => p_draft_id);
233
234 IF ( l_withdrawn_par_line_list.Count > 0) THEN
235 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_withdrawn_par_line_list,
236 'LINE');
237 END IF;
238
239 IF ( l_withdrawn_par_dist_list.Count > 0) THEN
240 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_withdrawn_par_dist_list,
241 'DISTRIBUTION MOD');
242 END IF;
243
244 -- Send the requisitions back to pool, if eleigible,
245 IF (l_withdrawn_par_line_list.Count > 0 OR l_withdrawn_par_dist_list.Count > 0)
246 THEN
247 PO_REQ_LINES_SV.update_reqs_in_pool_flag;
248 END IF;
249 --<PAR Project Ends>*/
250 -- Bug: 15905347 - End
251
252 IF (PO_LOG.d_proc) THEN
253 PO_LOG.proc_end(d_module);
254 END IF;
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
259
260 END process_mod_withdrawn_action;
261
262 /*===========================================================================
263 PROCEDURE NAME: update_header_dummy_req
264
265 DESCRIPTION: This procedure will update the ReqsInPoolFlag of the dummy
266 header req when mod is deleted.
267 ===========================================================================*/
268 PROCEDURE update_header_dummy_req(p_draft_id IN NUMBER)
269 IS
270
271 d_api_name CONSTANT VARCHAR2(30) := 'update_header_dummy_req';
272 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
273 d_position NUMBER;
274 BEGIN
275
276 d_position := 0;
277
278 IF ( po_log.d_proc ) THEN
279 po_log.Proc_begin(d_module);
280 END IF;
281
282 d_position := 10;
283
284 UPDATE po_requisition_lines_all prl
285 SET reqs_in_pool_flag = 'Y',
286 linked_po_count = NULL,
287 last_update_date = SYSDATE,
288 last_updated_by = FND_GLOBAL.USER_ID,
289 last_update_login = FND_GLOBAL.LOGIN_ID
290 WHERE par_line_id IS NULL
291 AND clm_info_flag = 'Y'
292 AND par_draft_id = ( SELECT phd.draft_id
293 FROM po_headers_draft_all phd, po_requisition_headers_all prh
294 WHERE phd.mod_draft_id = p_draft_id
295 AND prh.requisition_header_id = prl.requisition_header_id
296 AND Nvl(prh.par_flag, 'N') = 'Y'
297 );
298
299 IF (PO_LOG.d_stmt) THEN
300 PO_LOG.stmt(d_module, d_position, 'Dummy Header Req Updated Count ', SQL%ROWCOUNT);
301 END IF;
302
303 IF ( po_log.d_proc ) THEN
304 po_log.Proc_end(d_module);
305 END IF;
306
307 END update_header_dummy_req;
308
309 -- <Mod Project>: Delete the draft
310 PROCEDURE process_mod_delete_action(p_draft_id in number,
311 result out NOCOPY varchar2)
312 IS
313 d_api_name CONSTANT VARCHAR2(30) := 'process_mod_delete_action';
314 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
315 d_position NUMBER;
316 l_contract_document_type VARCHAR2(20);
317 l_po_header_id NUMBER;
318 l_conterms_exist_flag VARCHAR2(1);
319 l_type_lookup_code PO_HEADERS_DRAFT_ALL.type_lookup_code%type;
320 l_functional_area_code VARCHAR2(500);
321 l_document_style_id NUMBER;
322 l_uda_enabled_flag VARCHAR2(5);
323 l_errorcode VARCHAR2(50);
324
325 l_return_status VARCHAR2(1);
326 l_msg_data VARCHAR2(2000);
327 l_msg_count NUMBER;
328 l_item_key_tbl po_tbl_varchar100;
329 l_mod_number po_drafts.modification_number%TYPE;
330 l_item_status varchar2(10);
331 l_result varchar2(10);
332
333 l_deleted_line_list PO_TBL_NUMBER;
334 l_deleted_dist_list PO_TBL_NUMBER;
335 -- PAR project
336 l_release_locks_yn VARCHAR2(1);
337
338 --SBCR Integration Begin.
339 p_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
340 l_okc_doc_type VARCHAR2(25);
341 --SBCR Integration End.
342
343 BEGIN
344
345 d_position := 0;
346 IF (PO_LOG.d_proc) THEN
347 PO_LOG.proc_begin(d_module);
348 END IF;
349
350 result := FND_API.G_RET_STS_SUCCESS;
351
352 d_position := 10;
353
354 -- delete data from po_entity_locks table while deleting mod
355 PO_DRAFT_MERGE_PKG.delete_entity_locks( p_draft_id => p_draft_id,
356 x_return_status => l_return_status);
357
358 --delete the active workflow (notification)process related to this modification
359
360 d_position := 20;
361
362 -- Collect the item keys for the modification
363 BEGIN
364
365 SELECT item_key
366 BULK COLLECT INTO l_item_key_tbl
367 FROM wf_items wfi
368 WHERE wfi.user_key = To_Char(p_draft_id) AND wfi.item_type = 'POCMODNT';
369
370 d_position := 30;
371
372 FOR i IN 1..l_item_key_tbl.Count()
373 LOOP
374 WF_ENGINE.ItemStatus(itemtype => 'POCMODNT', itemkey => l_item_key_tbl(i),status => l_item_status,result => l_result);
375 IF l_item_status <> 'COMPLETE' THEN
376 wf_engine.abortProcess(itemtype => 'POCMODNT', itemkey => l_item_key_tbl(i));
377 END IF;
378 END LOOP;
379
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 PO_DEBUG.debug_stmt(d_module,d_position,'No notification data found');
383 END;
384
385 d_position := 40;
386
387 -- delete uda data from tables if uda is enabled
388 SELECT 'PURCHASING', TYPE_LOOKUP_CODE, STYLE_ID,
389 nvl(conterms_exist_flag, 'N'), po_header_id, type_lookup_code
390 INTO l_functional_area_code, l_type_lookup_code,
391 l_document_style_id, l_conterms_exist_flag, l_po_header_id, l_type_lookup_code
392 FROM po_headers_draft_all
393 WHERE draft_id = p_draft_id;
394
395 PO_CLM_CLO_UTIL.check_po_uda_enabled(
396 p_document_type_code => l_type_lookup_code,
397 p_document_style_id => l_document_style_id,
398 x_enabled_flag => l_uda_enabled_flag,
399 x_return_status => l_return_status
400 );
401
402 IF l_uda_enabled_flag = 'Y' THEN
403 delete_uda_data(p_draft_id => p_draft_id,
404 p_entity_id => l_po_header_id,
405 p_entity_level => 'PO_HEADER',
406 x_return_status => l_return_status);
407
408 END IF;
409
410 d_position := 50;
411
412 if l_conterms_exist_flag = 'Y' then
413 l_contract_document_type :=
414 PO_CONTERMS_UTL_GRP.get_po_contract_doctype_mod
415 ( p_sub_doc_type => l_type_lookup_code
416 );
417 -- PAR Project : FOR PAR, nas no lickingis done, release locks parameter must be passed as N
418 SELECT DECODE(draft_type,
419 'MOD', 'Y',
420 'PAR', 'N')
421 INTO l_release_locks_yn
422 FROM PO_DRAFTS
423 WHERE draft_id = p_draft_id;
424
425 OKC_TERMS_UTIL_GRP.delete_doc
426 ( p_api_version => 1.0
427 , p_init_msg_list => FND_API.G_TRUE
428 , p_commit => FND_API.G_FALSE
429 , p_doc_id => p_draft_id
430 , p_doc_type => l_contract_document_type
431 , p_validate_commit => FND_API.G_FALSE
432 , p_release_locks_yn => 'Y'
433 , x_return_status => l_return_status
434 , x_msg_data => l_msg_data
435 , x_msg_count => l_msg_count
436 );
437 end if;
438
439 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
440 ( 'PO_HEADERS',
441 l_po_header_id,
442 p_draft_id, '', '', '', 'Y');
443
444 -- Bug: 13948625 - Start
445 l_deleted_line_list := PO_TBL_NUMBER();
446 l_deleted_dist_list := PO_TBL_NUMBER();
447
448 -- Collect all deleted lines
449 SELECT po_line_id
450 BULK COLLECT INTO l_deleted_line_list
451 FROM po_lines_draft_all
452 WHERE draft_id = p_draft_id;
453
454 -- Collect all deleted distributions
455 SELECT po_distribution_id
456 BULK COLLECT INTO l_deleted_dist_list
457 FROM po_distributions_draft_all
458 WHERE draft_id = p_draft_id;
459
460 IF (PO_LOG.d_stmt) THEN
461 PO_LOG.stmt(d_module,d_position,'l_deleted_line_list',l_deleted_line_list);
462 PO_LOG.stmt(d_module,d_position,'l_deleted_dist_list',l_deleted_dist_list);
463 END IF;
464
465
466 -- Bug: 13948625 - Start
467 -- Re-written the logic to send the requisitions back to pool when CLM document or its lines/distributions deleted.
468 -- apply_changes proc of PO_XX_DRAFT_PVT above has a call to update_req_for_linked_po_count, where
469 -- the linked_po_count is updated for unlinked requisitions and those reqs CLINs(Autocreated/SoftLinked)
470 -- and PSlins(Softlinked) inserted into GT table for further procession(updating the reqs_in_pool_flag for entire
471 -- Clin-Slin structure if eligible) in the below proc update_reqs_in_pool_flag.
472
473 -- Call update_req_for_linked_po_count, where the linked_po_count is updated for unlinked requisitions
474 -- and those reqs CLINs(Autocreated/SoftLinked) and PSlins(Softlinked) inserted into GT table for
475 -- further processing
476
477 IF ( l_deleted_line_list.Count > 0) THEN
478 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_deleted_line_list, 'LINE');
479 END IF;
480
481 IF ( l_deleted_dist_list.Count > 0) THEN
482 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_deleted_dist_list, 'DISTRIBUTION MOD');
483 END IF;
484
485 -- Send the requisitions back to pool, if eleigible,
486 IF (l_deleted_line_list.Count > 0 OR l_deleted_dist_list.Count > 0) THEN
487 PO_REQ_LINES_SV.update_reqs_in_pool_flag;
488 END IF;
489 -- Bug: 13948625 - End
490
491 --<PAR Project>
492 --Need to send back the header dummy req to DWB
493 update_header_dummy_req(p_draft_id => p_draft_id);
494
495 -- SBCR Integration Code to Delete associated SBCR Records BEGIN.
496 IF l_type_lookup_code = 'BLANKET' THEN
497 l_okc_doc_type := 'PA_BLANKET_MOD';
498 ELSIF l_type_lookup_code = 'CONTRACT' THEN
499 l_okc_doc_type := 'PA_CONTRACT_MOD';
500 ELSE
501 l_okc_doc_type := 'PO_STANDARD_MOD';
502 END IF;
503
504 p_document_rec.business_document_type := l_okc_doc_type;
505 p_document_rec.business_document_id := p_draft_id;
506
507 OKC_REP_CONTRACT_IMP_PUB.delete_cancel_contract(p_api_version => '1.0',
508 p_commit => fnd_api.g_false,
509 p_document_rec => p_document_rec,
510 p_contract_type => 'REP_SBCR',
511 p_contract_id => null,
512 x_msg_data => l_msg_data ,
513 x_msg_count =>l_msg_count,
514 x_return_status => l_return_status);
515
516 -- SBCR Integration Code to Delete associated SBCR Records END.
517
518 PO_DRAFTS_PVT.remove_draft_changes(p_draft_id,FND_API.G_FALSE,result);
519
520 IF (PO_LOG.d_proc) THEN
521 PO_LOG.proc_end(d_module);
522 END IF;
523
524 EXCEPTION
525 WHEN OTHERS THEN
526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
527
528 END process_mod_delete_action;
529
530
531
532 -- deletes data from uda tables corresponding to entity level passed.
533 procedure delete_uda_data(p_draft_id IN Number,
534 p_entity_id IN Number,
535 p_entity_level IN VARCHAR2,
536 x_return_status OUT NOCOPY VARCHAR2)
537 IS
538
539 d_api_name CONSTANT VARCHAR2(30) := 'delete_uda_data';
540 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
541 d_position NUMBER;
542
543 BEGIN
544
545 d_position := 0;
546 IF (PO_LOG.d_proc) THEN
547 PO_LOG.proc_begin(d_module);
548 END IF;
549
550 IF p_entity_level = 'PO_HEADER' THEN
551 DELETE FROM po_headers_all_ext_b
552 WHERE draft_id = p_draft_id
553 AND po_header_id = p_entity_id;
554
555 DELETE FROM po_headers_all_ext_tl
556 WHERE draft_id = p_draft_id
557 AND po_header_id = p_entity_id;
558
559 DELETE FROM po_lines_all_ext_b
560 WHERE (draft_id = p_draft_id --main record for Mod
561 AND po_line_id IN (SELECT po_line_id
562 FROM po_lines_draft_all
563 WHERE po_header_id = p_entity_id
564 AND draft_id = p_draft_id))
565 OR (draft_id = -p_draft_id -- delta record for Mod complex pricing attribute
566 AND po_line_id IN (SELECT -po_line_id
567 FROM po_lines_draft_all
568 WHERE po_header_id = p_entity_id
569 AND draft_id = p_draft_id)
570 AND pk1_value = -p_draft_id)
571 OR (draft_id = -1 -- old record for Mod Complex pricing.
572 AND pk1_value = p_draft_id --<Bug 14837341>: pk1_value is the p_draft_id not -1
573 AND po_line_id IN (SELECT po_Line_id
574 FROM po_lines_draft_all
575 WHERE po_header_id = p_entity_id
576 AND draft_id = p_draft_id));
577
578 DELETE FROM po_lines_all_ext_tl
579 WHERE (draft_id = p_draft_id --main record for Mod
580 AND po_line_id IN (SELECT po_line_id
581 FROM po_lines_draft_all
582 WHERE po_header_id = p_entity_id
583 AND draft_id = p_draft_id))
584 OR (draft_id = -p_draft_id -- delta record for Mod complex pricing attribute
585 AND po_line_id IN (SELECT -po_line_id
586 FROM po_lines_draft_all
587 WHERE po_header_id = p_entity_id
588 AND draft_id = p_draft_id)
589 AND pk1_value = -p_draft_id)
590 OR (draft_id = -1 -- old record for Mod Complex pricing.
591 AND pk1_value = p_draft_id --<Bug 14837341>: pk1_value is the p_draft_id not -1
592 AND po_line_id IN (SELECT po_Line_id
593 FROM po_lines_draft_all
594 WHERE po_header_id = p_entity_id
595 AND draft_id = p_draft_id));
596
597 DELETE FROM po_line_locations_all_ext_b
598 WHERE draft_id = p_draft_id
599 AND line_location_id IN(SELECT line_location_id
600 FROM po_line_locations_draft_all
601 WHERE po_header_id = p_entity_id
602 AND draft_id = p_draft_id);
603
604 DELETE FROM po_line_locations_all_ext_tl
605 WHERE draft_id = p_draft_id
606 AND line_location_id IN(SELECT line_location_id
607 FROM po_line_locations_draft_all
608 WHERE po_header_id = p_entity_id
609 AND draft_id = p_draft_id);
610
611 ELSIF p_entity_level = 'PO_LINE' THEN
612
613 DELETE FROM po_lines_all_ext_b
614 WHERE ( draft_id = p_draft_id -- main record for Mod
615 AND po_line_id = p_entity_id)
616 OR ( draft_id = -p_draft_id -- delta record for complex pricing
617 AND po_line_id = -p_entity_id
618 AND pk1_value = -p_draft_id)
619 OR ( draft_id = -1 -- old record for complex pricing
620 AND po_line_id = p_entity_id
621 AND pk1_value = p_draft_id);
622
623 DELETE FROM po_line_locations_all_ext_b
624 WHERE draft_id = p_draft_id
625 AND line_location_id IN (SELECT line_location_id
626 FROM po_line_locations_draft_all
627 WHERE po_line_id = p_entity_id
628 AND draft_id = p_draft_id);
629
630 DELETE FROM po_lines_all_ext_tl
631 WHERE ( draft_id = p_draft_id -- main record for Mod
632 AND po_line_id = p_entity_id)
633 OR ( draft_id = -p_draft_id -- delta record for complex pricing
634 AND po_line_id = -p_entity_id
635 AND pk1_value = -p_draft_id)
636 OR ( draft_id = -1 -- old record for complex pricing
637 AND po_line_id = p_entity_id
638 AND pk1_value = p_draft_id);
639
640 DELETE FROM po_line_locations_all_ext_tl
641 WHERE draft_id = p_draft_id
642 AND line_location_id IN (SELECT line_location_id
643 FROM po_line_locations_draft_all
644 WHERE po_line_id = p_entity_id
645 AND draft_id = p_draft_id);
646
647 ELSIF p_entity_level = 'PO_SHIPMENT' THEN
648
649 DELETE FROM po_line_locations_all_ext_b
650 WHERE draft_id = p_draft_id
651 AND line_location_id = p_entity_id;
652
653 DELETE FROM po_line_locations_all_ext_tl
654 WHERE draft_id = p_draft_id
655 AND line_location_id = p_entity_id;
656
657 END IF;
658
659
660 EXCEPTION
661
662 WHEN OTHERS THEN
663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
664
665 END delete_uda_data;
666
667
668 -- The procedure 'do_mod_line_control_action' performing the control actions
669 -- such as CANCEL, CLOSE and FINALLY CLOSE at LINE level and remove all the
670 -- changes from the draft table of its child entities
671
672 procedure do_mod_line_control_action(p_draft_id in number,
673 p_action in varchar2,
674 p_po_line_id in number,
675 resultout out NOCOPY varchar2) is
676
677 d_api_name CONSTANT VARCHAR2(30) := 'do_mod_line_control_action';
678 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
679 d_position NUMBER;
680
681 l_po_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
682 l_po_draft_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
683 l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
684 l_record_already_exist_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
685
686 begin
687 d_position := 0;
688 IF (PO_LOG.d_proc) THEN
689 PO_LOG.proc_begin(d_module);
690 END IF;
691 resultout := FND_API.G_RET_STS_SUCCESS;
692
693 d_position := 10;
694
695
696 PO_LINES_DRAFT_PKG.delete_rows
697 ( p_draft_id => p_draft_id,
698 p_po_line_id => p_po_line_id
699 );
700
701 d_position := 20;
702 PO_LINE_LOCATIONS_DRAFT_PKG.delete_rows
703 ( p_draft_id => p_draft_id,
704 p_line_location_id => NULL
705 );
706
707 d_position := 30;
708 PO_DISTRIBUTIONS_DRAFT_PKG.delete_rows
709 ( p_draft_id => p_draft_id,
710 p_po_distribution_id => NULL
711 );
712
713 d_position := 40;
714 PO_PRICE_DIFF_DRAFT_PKG.delete_rows
715 ( p_draft_id => p_draft_id,
716 p_price_differential_id => NULL
717 );
718
719 d_position := 50;
720 PO_ATTR_VALUES_DRAFT_PKG.delete_rows
721 ( p_draft_id => p_draft_id,
722 p_attribute_values_id => NULL
723 );
724
725 d_position := 60;
726 PO_ATTR_VALUES_TLP_DRAFT_PKG.delete_rows
727 ( p_draft_id => p_draft_id,
728 p_attribute_values_tlp_id => NULL
729 );
730
731 d_position := 70;
732 PO_PRICE_ADJ_DRAFT_PKG.delete_rows
733 ( p_draft_id => p_draft_id,
734 p_price_adjustment_id => NULL
735 );
736
737
738 IF resultout = FND_API.G_RET_STS_SUCCESS THEN
739 -- If return status success, then call PO_HEADERS_DRAFT_PKG.sync_draft_from_txn
740 d_position := 80;
741 l_po_line_id_tbl.extend(1);
742 l_po_draft_id_tbl.extend(1);
743 l_delete_flag_tbl.extend(1);
744 l_po_line_id_tbl(1) := p_po_line_id;
745 l_po_draft_id_tbl(1) := p_draft_id;
746 l_delete_flag_tbl(1) := 'N';
747 d_position := 90;
748 PO_LINES_DRAFT_PKG.sync_draft_from_txn(l_po_line_id_tbl,
749 l_po_draft_id_tbl,
750 l_delete_flag_tbl,
751 l_record_already_exist_tbl);
752 d_position := 40;
753
754 END IF;
755
756 d_position := 50;
757 -- If return status success, then go ahead
758
759 update po_lines_draft_all
760 set control_action = p_action
761 where draft_id = p_draft_id;
762
763 d_position := 60;
764 resultout := FND_API.G_RET_STS_SUCCESS;
765
766 IF (PO_LOG.d_proc) THEN
767 PO_LOG.proc_end(d_module);
768 END IF;
769
770 EXCEPTION
771 WHEN OTHERS THEN
772 PO_MESSAGE_S.add_exc_msg
773 ( p_pkg_name => d_pkg_name,
774 p_procedure_name => d_api_name || '.' || d_position
775 );
776
777 resultout := FND_API.G_RET_STS_UNEXP_ERROR;
778
779 end do_mod_line_control_action;
780
781 -- The procedure 'do_mod_line_loc_control_action' performing the control actions
782 -- such as CANCEL, CLOSE and FINALLY CLOSE at LINE level and remove all the
783 -- changes from the draft table of its child entities
784
785 procedure do_mod_line_loc_control_action(p_draft_id in number,
786 p_action in varchar2,
787 p_po_line_loc_id in number,
788 resultout out NOCOPY varchar2) is
789
790 d_api_name CONSTANT VARCHAR2(30) := 'do_mod_line_loc_control_action';
791 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
792 d_position NUMBER;
793
794 l_po_line_loc_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
795 l_po_draft_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
796 l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
797 l_record_already_exist_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
798
799 begin
800 d_position := 0;
801 IF (PO_LOG.d_proc) THEN
802 PO_LOG.proc_begin(d_module);
803 END IF;
804 resultout := FND_API.G_RET_STS_SUCCESS;
805
806 d_position := 10;
807
808 PO_LINE_LOCATIONS_DRAFT_PKG.delete_rows
809 ( p_draft_id => p_draft_id,
810 p_line_location_id => p_po_line_loc_id
811 );
812
813 d_position := 20;
814 IF resultout = FND_API.G_RET_STS_SUCCESS THEN
815 -- If return status success, then call PO_HEADERS_DRAFT_PKG.sync_draft_from_txn
816 d_position := 30;
817 l_po_line_loc_id_tbl.extend(1);
818 l_po_draft_id_tbl.extend(1);
819 l_delete_flag_tbl.extend(1);
820 l_po_line_loc_id_tbl(1) := p_po_line_loc_id;
821 l_po_draft_id_tbl(1) := p_draft_id;
822 l_delete_flag_tbl(1) := 'N';
823 d_position := 40;
824 PO_LINE_LOCATIONS_DRAFT_PKG.sync_draft_from_txn(l_po_line_loc_id_tbl,
825 l_po_draft_id_tbl,
826 l_delete_flag_tbl,
827 l_record_already_exist_tbl);
828 END IF;
829
830 d_position := 50;
831 -- If return status success, then go ahead
832
833 update po_line_locations_draft_all
834 set control_action = p_action
835 where draft_id = p_draft_id;
836
837 d_position := 60;
838 resultout := FND_API.G_RET_STS_SUCCESS;
839
840 IF (PO_LOG.d_proc) THEN
841 PO_LOG.proc_end(d_module);
842 END IF;
843
844 EXCEPTION
845 WHEN OTHERS THEN
846 PO_MESSAGE_S.add_exc_msg
847 ( p_pkg_name => d_pkg_name,
848 p_procedure_name => d_api_name || '.' || d_position
849 );
850
851 resultout := FND_API.G_RET_STS_UNEXP_ERROR;
852
853 end do_mod_line_loc_control_action;
854
855 ------------------------------------------------------------------------------
856 --Start of Comments
857 --Name: get_mod_valid_control_actions
858 --Function:
859 -- This procedure would get us the list of valid Document Control Actions
860 -- for a given document and the level for which it is requested.
861 -------------------------------------------------------------------------
862 procedure get_mod_valid_control_actions( p_mode IN VARCHAR2
863 ,p_doc_level IN VARCHAR2
864 ,p_doc_type IN VARCHAR2
865 ,p_doc_header_id IN NUMBER
866 ,p_doc_level_id IN NUMBER
867 ,p_draft_id IN NUMBER -- <Mod Project>
868 ,x_return_status OUT NOCOPY VARCHAR2
869 ,x_valid_ctrl_ctn_tbl OUT NOCOPY PO_TBL_VARCHAR30)
870 IS
871 l_valid_actions_tbl PO_DOCUMENT_CONTROL_PVT.g_lookup_code_tbl_type;
872 l_displayed_field_tbl PO_DOCUMENT_CONTROL_PVT.g_displayed_field_tbl_type;
873 l_doc_subtype PO_HEADERS_ALL.type_lookup_code%type;
874 l_cons_trans_exist VARCHAR2(1);
875 l_index NUMBER;
876 l_agent_id NUMBER;
877 l_item_id PO_LINES_ALL.item_Id%TYPE;
878 l_po_line_id PO_LINES_ALL.po_line_id%TYPE;
879 l_current_action PO_LOOKUP_CODES.lookup_code%TYPE;
880 l_mode VARCHAR2(30);
881 l_ship_invalid_for_ctrl_actn VARCHAR2(1) := 'N';
882 d_pos NUMBER;
883 l_api_name CONSTANT VARCHAR2(30) := 'get_mod_valid_control_actions';
884 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_Document_Control_PVT.get_mod_valid_control_actions';
885 BEGIN
886 IF (PO_LOG.d_proc) THEN
887 PO_LOG.proc_begin(d_module);
888 PO_LOG.proc_begin(d_module,'p_mode',p_mode);
889 PO_LOG.proc_begin(d_module,'p_doc_level',p_doc_level);
890 PO_LOG.proc_begin(d_module,'p_doc_type',p_doc_type);
891 PO_LOG.proc_begin(d_module,'p_doc_header_id',p_doc_header_id);
892 PO_LOG.proc_begin(d_module,'p_doc_level_id',p_doc_level_id);
893 END IF;
894
895 --Initialisation of local variables
896 x_return_status := FND_API.g_ret_sts_success;
897 l_cons_trans_exist := 'N';
898 l_index := 1;
899 l_mode := UPPER(p_mode);
900 x_valid_ctrl_ctn_tbl := PO_TBL_VARCHAR30();
901 d_pos := 10;
902 --Get the Employee Id of the Current User
903 l_agent_id := fnd_global.employee_id;
904 --Get Document Sub type
905 IF(p_doc_type IN (PO_CORE_S.g_doc_type_PO, PO_CORE_S.g_doc_type_PA)) THEN
906 SELECT type_lookup_code
907 INTO l_doc_subtype
908 FROM po_headers_all
909 WHERE po_header_id = p_doc_header_id;
910 END IF;
911 d_pos := 20;
912 IF (PO_LOG.d_stmt) THEN
913 PO_LOG.stmt(d_module,d_pos,'l_agent_id',l_agent_id);
914 PO_LOG.stmt(d_module,d_pos,'l_doc_subtype',l_doc_subtype);
915 END IF;
916 IF (p_doc_level = PO_CORE_S.g_doc_level_HEADER) THEN --header level
917 d_pos := 30;
918 PO_DOCUMENT_CONTROL_PVT.get_header_actions
919 ( p_doc_subtype => l_doc_subtype
920 , p_doc_id => p_doc_level_id
921 , p_agent_id => l_agent_id
922 , x_lookup_code_tbl => l_valid_actions_tbl
923 , x_displayed_field_tbl => l_displayed_field_tbl
924 , x_return_status => x_return_status
925 , p_mode => l_mode);
926
927 ELSIF (p_doc_level = PO_CORE_S.g_doc_level_LINE) THEN --line level
928 d_pos := 40;
929 --get the itme_id for the consumption transaction existence check
930 SELECT item_id
931 INTO l_item_id
932 FROM po_lines_merge_v -- <Mod Project> Replaced po_lines_all
933 WHERE po_line_id = p_doc_level_id
934 AND draft_id = p_draft_id;
935
936 IF (PO_LOG.d_stmt) THEN
937 PO_LOG.stmt(d_module,d_pos,'l_item_id',l_agent_id);
938 END IF;
939
940 PO_DOCUMENT_CONTROL_PVT.get_line_actions
941 ( p_doc_subtype => l_doc_subtype
942 , p_doc_line_id => p_doc_level_id
943 , p_agent_id => l_agent_id
944 , x_lookup_code_tbl => l_valid_actions_tbl
945 , x_displayed_field_tbl => l_displayed_field_tbl
946 , x_return_status => x_return_status
947 , p_mode => l_mode);
948
949 ELSIF (p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT) THEN
950 d_pos := 50;
951 --shipment levl
952 PO_DOCUMENT_CONTROL_PVT.get_shipment_actions
953 ( p_doc_type => p_doc_type
954 , p_doc_subtype => l_doc_subtype
955 , p_doc_line_loc_id => p_doc_level_id
956 , p_agent_id => l_agent_id
957 , x_lookup_code_tbl => l_valid_actions_tbl
958 , x_displayed_field_tbl => l_displayed_field_tbl
959 , x_return_status => x_return_status
960 , p_mode => l_mode);
961
962 END IF;
963 IF (PO_LOG.d_stmt) THEN
964 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
965 END IF;
966 d_pos := 60;
967
968 IF (x_return_status = FND_API.g_ret_sts_success)then
969
970 IF p_doc_level IN (PO_CORE_S.g_doc_level_HEADER, PO_CORE_S.g_doc_level_LINE) THEN
971 -- Checks if there exists a consumption transaction that is in process for
972 -- the passed in transaction source document ID and and item ID.
973 l_cons_trans_exist := PO_INV_THIRD_PARTY_STOCK_MDTR.consumption_trans_exist(
974 p_doc_header_id,
975 l_item_id);
976 END IF;
977 d_pos := 70;
978 IF (PO_LOG.d_stmt) THEN
979 PO_LOG.stmt(d_module,d_pos,'l_cons_trans_exist',l_cons_trans_exist);
980 END IF;
981 --<Bug#4515762 Start>
982 --Prevent cancel option for shipment if it is the only shipment on the
983 --line that is not cancelled or finally closed.
984 IF(p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT) THEN
985
986 BEGIN
987 d_pos := 75;
988 SELECT 'N'
989 INTO l_ship_invalid_for_ctrl_actn
990 FROM DUAL
991 WHERE EXISTS(
992 SELECT 1
993 FROM po_line_locations_all poll1,
994 po_line_locations_all poll2
995 WHERE poll1.line_location_id = p_doc_level_id
996 AND poll1.po_line_id = poll2.po_line_id
997 AND NVL(poll2.cancel_flag,'N') <> 'Y'
998 AND NVL(poll2.payment_type, 'NULL') NOT IN ('ADVANCE', 'DELIVERY') --<Complex Work R12>
999 AND NVL(poll2.closed_code, PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN)
1000 <> PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
1001 AND poll2.line_location_id <> p_doc_level_id);
1002 EXCEPTION
1003 WHEN NO_DATA_FOUND THEN
1004 --Current shipment is the only shipment on the line that is not cancelled or finally closed
1005 --OR there are no open, uncancelled shipments.
1006 IF PO_LOG.d_stmt THEN
1007 PO_LOG.stmt(d_module,d_pos,'Control Action cannot be performed on the shipment');
1008 END IF;
1009 l_ship_invalid_for_ctrl_actn := 'Y';
1010 END;
1011
1012 END IF; --p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT
1013 --<Bug#4515762 End>
1014
1015 FOR i IN l_valid_actions_tbl.first..l_valid_actions_tbl.last
1016 LOOP
1017 d_pos := 80;
1018 l_current_action := l_valid_actions_tbl(i);
1019
1020 IF (PO_LOG.d_stmt) THEN
1021 PO_LOG.stmt(d_module,d_pos,'l_current_action',l_current_action);
1022 END IF;
1023 -- If consumption transaction exist we don't allow Cancel and Finally
1024 -- Close actions
1025 IF (l_cons_trans_exist = 'Y'
1026 AND l_current_action in ('CANCEL PO','CANCEL PO LINE', PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)) THEN
1027 NULL;
1028 --If it is the only shipment then we dont allow the cancellation or finally closing of the shipment.
1029 ELSIF(l_ship_invalid_for_ctrl_actn = 'Y'
1030 AND l_current_action in ('CANCEL PO SHIPMENT', PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)) THEN
1031 NULL;
1032 ELSE
1033 -- For Update Mode only Cancel Related And Hold Related
1034 -- Control Actions are valid
1035 IF(l_mode = 'UPDATE'
1036 AND NOT (l_current_action LIKE 'CANCEL%'
1037 OR l_current_action LIKE '%HOLD%')) THEN
1038 NULL;
1039 ELSE
1040 x_valid_ctrl_ctn_tbl.extend;
1041 IF(l_mode = 'MODSUMMARY' AND l_current_action LIKE 'CANCEL%' ) THEN
1042 -- For Summary we Show Cancel Action as Cancel at all the Levels
1043
1044 d_pos := 90;
1045 IF (PO_LOG.d_stmt) THEN
1046 PO_LOG.stmt(d_module,d_pos,l_current_action || 'is replaced by CANCEL for summary mode');
1047 END IF;
1048 x_valid_ctrl_ctn_tbl(l_index) := 'CANCEL';
1049 ELSIF(l_mode = 'UPDATE' AND l_current_action = 'CANCEL PO') THEN
1050
1051 d_pos := 100;
1052 IF (PO_LOG.d_stmt) THEN
1053 PO_LOG.stmt(d_module,d_pos,l_current_action || 'is replaced by CANCEL for update mode');
1054 END IF;
1055 x_valid_ctrl_ctn_tbl(l_index) := 'CANCEL';
1056
1057 ELSE
1058 d_pos := 110;
1059 IF (PO_LOG.d_stmt) THEN
1060 PO_LOG.stmt(d_module,d_pos,l_current_action || 'is directly placed');
1061 END IF;
1062
1063 x_valid_ctrl_ctn_tbl(l_index) := l_valid_actions_tbl(i);
1064 END IF; --mode = 'SUMMARY' AND l_current_action LIKE 'CANCEL%'
1065 l_index := l_index+1;
1066 END IF; --l_mode = 'UPDATE'
1067 END IF; -- l_cons_trans_exist = 'Y'
1068 END LOOP;
1069
1070 ELSIF (x_return_status = FND_API.g_ret_sts_error)then
1071 d_pos := 120;
1072 IF (PO_LOG.d_stmt) THEN
1073 PO_LOG.stmt(d_module,d_pos,'No Valid Control Action Found');
1074 END IF;
1075 ELSE
1076 d_pos := 130;
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078 END IF; --x_return_status = FND_API.g_ret_sts_success
1079 IF (PO_LOG.d_proc) THEN
1080 PO_LOG.proc_end(d_module);
1081 END IF;
1082 EXCEPTION
1083 WHEN OTHERS THEN
1084 x_return_status := FND_API.g_ret_sts_unexp_error;
1085 FND_MSG_PUB.add_exc_msg(d_pkg_name, l_api_name||':'||d_pos);
1086 IF PO_LOG.d_exc THEN
1087 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
1088 END IF;
1089 END get_mod_valid_control_actions;
1090
1091 ------------------------------------------------------------------------------
1092 --Start of Comments
1093 -- <PAR Project>
1094 --Name: process_par_withdraw_action
1095 --Function:
1096 -- This procedure Performs the withdraw action on the PAR
1097 --Parameters :
1098 -- IN:
1099 -- p_draft_id
1100 -- p_employee_id
1101 -- OUT :
1102 -- x_return_status
1103 -- x_return_mssg
1104 -------------------------------------------------------------------------
1105 PROCEDURE process_par_withdraw_action(p_draft_id IN NUMBER,
1106 p_employee_id IN NUMBER,
1107 x_return_status OUT NOCOPY VARCHAR2,
1108 x_return_mssg OUT NOCOPY VARCHAR2)
1109 IS
1110
1111 l_ret_sts VARCHAR2(1);
1112 l_online_report_id NUMBER;
1113 l_return_code VARCHAR2(25);
1114 l_error_message VARCHAR2(2000);
1115 l_req_header_id NUMBER;
1116 l_req_authorization_status po_requisition_headers_all.authorization_status%TYPE;
1117 l_approval_id NUMBER;
1118 l_item_key_tbl PO_TBL_VARCHAR100;
1119 l_item_status VARCHAR2(10);
1120 l_result VARCHAR2(10);
1121 d_api_name CONSTANT VARCHAR2(30) := 'process_par_withdraw_action';
1122 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1123 d_position NUMBER;
1124 l_req_enc_on BOOLEAN;
1125 l_enc_ret_code VARCHAR2(10);
1126 l_enc_report_id NUMBER;
1127 l_org_id NUMBER;
1128 line_status_code po_lines_draft_all.draft_line_status%TYPE;
1129 l_sequence_num NUMBER;
1130 l_object_rev_num NUMBER;
1131 l_approval_path_id NUMBER;
1132
1133 BEGIN
1134 d_position := 0;
1135
1136 -- Logic :
1137 -- 1) Check if the requisition is returned.
1138 -- 2) If not returned, then first return the requsiition
1139 -- 3) Release Funds
1140 -- 4) Update line status to WITHDRAW.
1141 -- 5) If all lines are withdrawn or no lines exist
1142 -- then update header status as well. Also insert in action history.
1143 -- 6) Abort all the open collaboration workflows.
1144
1145 SAVEPOINT WITHDRAW_ACTION_SP;
1146
1147 IF ( po_log.d_proc ) THEN
1148 po_log.Proc_begin(d_module);
1149 END IF;
1150
1151 d_position := 10;
1152
1153 SELECT prh.requisition_header_id,
1154 podc.default_approval_path_id,
1155 prh.authorization_status,
1156 prh.org_id
1157 INTO l_req_header_id,
1158 l_approval_id,
1159 l_req_authorization_status,
1160 l_org_id
1161 FROM po_requisition_headers_all prh,
1162 po_document_types_all podc
1163 WHERE prh.segment1 = To_char(p_draft_id)
1164 AND podc.document_type_code = 'REQUISITION'
1165 AND podc.document_subtype = prh.type_lookup_code
1166 AND podc.org_id = prh.org_id;
1167
1168 PO_MOAC_UTILS_PVt.set_org_context(l_org_id);
1169
1170 IF po_log.d_stmt THEN
1171 po_debug.Debug_stmt(d_module, d_position, 'Req Header Id : ' || l_req_header_id);
1172 END IF;
1173
1174 d_position := 20;
1175
1176 -- Return Requisition, if not already returned
1177 IF(l_req_authorization_status <> 'RETURNED') THEN
1178
1179 po_document_action_pvt.Do_return(p_document_id => l_req_header_id,
1180 p_document_type => 'REQUISITION',
1181 p_document_subtype => 'PURCHASE',
1182 p_note => 'PAR Withdraw',
1183 p_approval_path_id => l_approval_id,
1184 x_return_status => l_ret_sts,
1185 x_return_code => l_return_code,
1186 x_exception_msg => l_error_message,
1187 x_online_report_id => l_online_report_id);
1188
1189
1190 IF(l_ret_sts <> 'S') THEN
1191 x_return_status := l_ret_sts;
1192 x_return_mssg := l_error_message;
1193 RAISE PO_CORE_S.g_early_return_exc;
1194 END IF;
1195
1196 END IF;
1197
1198 -- Release Funds
1199 l_req_enc_on := PO_CORE_S.is_encumbrance_on(
1200 p_doc_type => PO_CORE_S.g_doc_type_REQUISITION
1201 , p_org_id => NULL
1202 );
1203
1204 d_position := 30;
1205 IF (PO_LOG.d_stmt) THEN
1206 PO_LOG.stmt(d_module, d_position, 'l_req_enc_on', l_req_enc_on);
1207 END IF;
1208
1209 IF (l_req_enc_on) THEN
1210
1211 d_position := 40;
1212
1213 PO_DOCUMENT_FUNDS_PVT.do_return(
1214 x_return_status => l_ret_sts
1215 , p_doc_type => 'REQUISITION'
1216 , p_doc_subtype => 'PURCHASE'
1217 , p_doc_level => PO_DOCUMENT_FUNDS_PVT.g_doc_level_HEADER
1218 , p_doc_level_id => l_req_header_id
1219 , p_use_enc_gt_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
1220 , p_use_gl_date => PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE
1221 , p_override_date => SYSDATE
1222 , x_po_return_code => l_enc_ret_code
1223 , x_online_report_id => l_enc_report_id
1224 );
1225
1226
1227 IF (l_ret_sts <> FND_API.g_ret_sts_success)
1228 THEN
1229
1230 d_position := 50;
1231 x_return_status := fnd_api.G_RET_STS_ERROR;
1232 x_return_mssg := 'do_return not successful';
1233 RAISE PO_CORE_S.g_early_return_exc;
1234
1235 END IF;
1236
1237 d_position := 60;
1238
1239 IF (PO_LOG.d_stmt) THEN
1240 PO_LOG.stmt(d_module, d_position, 'l_enc_ret_code', l_enc_ret_code);
1241 PO_LOG.stmt(d_module, d_position, 'l_enc_report_id', l_enc_report_id);
1242 END IF;
1243
1244 IF ((l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_SUCCESS)
1245 OR (l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_WARNING))
1246 THEN
1247
1248 d_position := 70;
1249 -- Just continue with withdraw action.
1250
1251 ELSIF (l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_PARTIAL)
1252 THEN
1253
1254 d_position := 80;
1255 x_return_status := fnd_api.G_RET_STS_ERROR;
1256 x_return_mssg := 'funds do_return partial';
1257 RAISE PO_CORE_S.g_early_return_exc;
1258
1259 ELSIF (l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_FAILURE)
1260 THEN
1261
1262 d_position := 90;
1263 x_return_status := fnd_api.G_RET_STS_ERROR;
1264 x_return_mssg := 'funds do_return failure';
1265
1266 RAISE PO_CORE_S.g_early_return_exc;
1267
1268
1269 ELSIF (l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_FATAL)
1270 THEN
1271
1272 d_position := 100;
1273 x_return_status := fnd_api.G_RET_STS_ERROR;
1274 x_return_mssg := 'funds do_return fatal';
1275 RAISE PO_CORE_S.g_early_return_exc;
1276
1277 ELSE
1278
1279 d_position := 110;
1280 x_return_status := fnd_api.G_RET_STS_ERROR;
1281 x_return_mssg := 'Bad return code from funds do_return';
1282 RAISE PO_CORE_S.g_early_return_exc;
1283
1284 END IF;
1285
1286 END IF;
1287
1288
1289 d_position := 120;
1290
1291 -- Update lines with status Withdrawn
1292
1293 UPDATE po_lines_draft_all
1294 SET draft_line_status = 'WITHDRAWN'
1295 WHERE draft_id = p_draft_id
1296 AND draft_line_status NOT IN( 'MOD_CREATED', 'PO_CREATED' );
1297
1298 -- Check if all lines are withdrawn or there are no lines on PAR.
1299 -- In these 2 cases need to stamp header with WITHDRAWN
1300 -- And in insert action history.
1301 BEGIN
1302 SELECT DISTINCT Decode (draft_line_status,
1303 NULL, 'COMPLETED',
1304 draft_line_status)
1305 INTO line_status_code
1306 FROM (SELECT draft_line_status,
1307 Rank () over ( ORDER BY Decode (draft_line_status,
1308 'ASSIGNED', 1,
1309 'MOD_CREATED', 2,
1310 'PO_CREATED', 3,
1311 'RETURNED', 4,
1312 'WITHDRAWN', 5,
1313 'COMPLETED', 6) ASC) priority
1314 FROM po_lines_draft_all
1315 WHERE draft_id = p_draft_id)
1316 WHERE priority = 1;
1317 d_position := 130;
1318 EXCEPTION
1319 WHEN OTHERS THEN
1320 line_status_code := 'NULL';
1321 END;
1322
1323 IF(line_status_code IN ('NULL', 'WITHDRAWN')) THEN
1324
1325 d_position := 140;
1326
1327 UPDATE po_drafts
1328 SET status = 'WITHDRAWN'
1329 WHERE draft_id = p_draft_id;
1330
1331 -- Insert In Action History
1332 d_position := 150;
1333 SELECT sequence_num,
1334 object_revision_num,
1335 approval_path_id
1336 INTO l_sequence_num,
1337 l_object_rev_num,
1338 l_approval_path_id
1339 FROM po_action_history
1340 WHERE object_id = p_draft_id
1341 AND object_type_code = 'PO'
1342 AND sequence_num = (SELECT max(sequence_num)
1343 FROM po_action_history
1344 WHERE object_id = p_draft_id
1345 AND Object_Type_Code = 'PO'
1346 AND object_sub_type_code = 'POST_AWARD_REQUEST')
1347 AND object_sub_type_code = 'POST_AWARD_REQUEST';
1348
1349 d_position := 160;
1350 INSERT INTO PO_ACTION_HISTORY
1351 (object_id,
1352 object_type_code,
1353 object_sub_type_code,
1354 sequence_num,
1355 last_update_date,
1356 last_updated_by,
1357 creation_date,
1358 created_by,
1359 action_code,
1360 action_date,
1361 employee_id,
1362 note,
1363 object_revision_num,
1364 last_update_login,
1365 request_id,
1366 program_application_id,
1367 program_id,
1368 program_update_date,
1369 approval_path_id,
1370 offline_code)
1371 VALUES
1372 (p_draft_id,
1373 'PO', -- doc_type
1374 'POST_AWARD_REQUEST', -- doc_subtype
1375 l_sequence_num + 1,
1376 sysdate,
1377 fnd_global.user_id,
1378 sysdate,
1379 fnd_global.user_id,
1380 'WITHDRAW', -- action_code
1381 sysdate, --action_date
1382 p_employee_id,
1383 NULL,
1384 l_object_rev_num,
1385 fnd_global.login_id,
1386 0,
1387 0,
1388 0,
1389 '',
1390 l_approval_path_id,
1391 '' );
1392
1393 END IF;
1394
1395 d_position := 170;
1396
1397 --Abort Open Collaboration Workflows.
1398 BEGIN
1399 SELECT item_key
1400 bulk collect INTO l_item_key_tbl
1401 FROM wf_items wfi
1402 WHERE wfi.user_key = To_char(p_draft_id)
1403 AND item_type = 'POCMODNT';
1404
1405 d_position := 180;
1406 FOR i IN 1..l_item_key_tbl.Count() LOOP
1407 wf_engine.Itemstatus(itemtype => 'POCMODNT',
1408 itemkey => L_item_key_tbl(i),
1409 status => l_item_status,
1410 result => l_result);
1411
1412 IF l_item_status <> 'COMPLETE' THEN
1413 wf_engine.Abortprocess(itemtype => 'POCMODNT',
1414 itemkey => L_item_key_tbl(i));
1415 END IF;
1416 END LOOP;
1417 EXCEPTION
1418 WHEN no_data_found THEN
1419 IF po_log.d_stmt THEN
1420 po_debug.Debug_stmt(d_module, d_position, 'No notification data found');
1421 END IF;
1422 END;
1423
1424 d_position := 190;
1425
1426 x_return_status := fnd_api.g_ret_sts_success;
1427
1428 IF ( po_log.d_proc ) THEN
1429 po_log.Proc_end(d_module);
1430 END IF;
1431
1432 EXCEPTION
1433 WHEN PO_CORE_S.g_early_return_exc THEN
1434
1435 IF PO_LOG.d_exc THEN
1436 PO_LOG.exc(d_module, d_position, 'Exception occurred ' || x_return_mssg);
1437 END IF;
1438
1439 ROLLBACK TO WITHDRAW_ACTION_SP;
1440
1441 WHEN OTHERS THEN
1442
1443 IF PO_LOG.d_exc THEN
1444 PO_LOG.exc(d_module, d_position, 'Exception occurred ' || SQLERRM);
1445 END IF;
1446 x_return_status := fnd_api.G_RET_STS_ERROR;
1447 x_return_mssg := SQLERRM;
1448
1449 ROLLBACK TO WITHDRAW_ACTION_SP;
1450
1451 END process_par_withdraw_action;
1452
1453
1454 END PO_MOD_CONTROL_PVT;