[Home] [Help]
PACKAGE BODY: APPS.PO_DRAFT_MERGE_PKG
Source
1 PACKAGE BODY PO_DRAFT_MERGE_PKG AS
2 /* $Header: PO_DRAFT_MERGE_PKG.plb 120.33.12020000.10 2013/04/18 11:43:19 inagdeo ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7
8 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_DRAFT_MERGE_PKG';
9 g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
10
11
12 /* Bug 14046925 : Declaration of global varaibles for tracking error code in UPDATE_AWARD_WITH_MOD_VALUES and MergeReserve_Autonomous */
13 g_action varchar2(1000) := null;
14 g_result varchar2(1000) := null;
15 g_error_message varchar2(1000) := null;
16
17 PROCEDURE MergeReserve_Autonomous(
18 itemtype IN VARCHAR2,
19 itemkey IN VARCHAR2,
20 p_draft_id IN NUMBER,
21 P_Document_Id IN NUMBER,
22 p_doc_type IN VARCHAR2,
23 p_doc_subtype IN VARCHAR2,
24 p_override_funds IN VARCHAR2,
25 p_employee_id IN NUMBER,
26 p_approval_path_id IN NUMBER,
27 p_note PO_ACTION_HISTORY.note%TYPE,
28 x_po_return_code OUT NOCOPY VARCHAR2,
29 x_online_report_id OUT NOCOPY NUMBER,
30 x_return_status OUT NOCOPY VARCHAR2 );
31 /*Bug 13938456 - Concurrent Mod Attachemnts*/
32
33 /* Procedure to merge header level attachments*/
34 procedure MERGE_HEADER_ATTACHMENTS(p_header_id NUMBER,
35 p_draft_id NUMBER) ;
36
37 /* Procedure to merge line level attachments*/
38 procedure MERGE_LINE_ATTACHMENTS(p_draft_id NUMBER);
39
40 /* Procedure to merge line location level attachments*/
41 procedure MERGE_LINE_LOCATION_ATTACHMENT(p_draft_id NUMBER);
42
43 /* Procedure to merge upgraded attachments*/
44 PROCEDURE MERGE_UPGRADED_ATTACHMENTS(p_document_id NUMBER,
45 p_draft_id NUMBER);
46
47 /* Procedure to process upgraded attachments*/
48 procedure PROCESS_ATTACHMENTS(ENTITY_NAME VARCHAR2,
49 pk1 number,
50 pk2 number);
51
52 --PAR Project
53 PROCEDURE update_par_line_status(itemtype IN VARCHAR2,
54 itemkey IN VARCHAR2,
55 p_document_id IN NUMBER,
56 p_draft_id IN NUMBER);
57
58 --<Event Based Delivery>
59 PROCEDURE update_delivery_event_attr(
60 p_document_id IN NUMBER
61 , p_draft_id IN NUMBER
62 );
63
64 -- <Bug 16491111>
65 PROCEDURE docnum_post_apprvl_autonomous(
66 itemtype IN VARCHAR2
67 , itemkey IN VARCHAR2
68 , p_document_id IN NUMBER
69 , p_draft_id IN NUMBER
70 , p_uda_template_id IN NUMBER
71 );
72
73 -------------------------------------------------------------------------------
74 --Bug 13938456 : Concurrent Mod Attachments
75 --Name: PROCESS_ATTACHMENTS
76 --Procedure to process upgraded attachments. Deletes all the attachments from the
77 --base document and copy the modification attachments to the base attachment
78 --End of Comments
79 -------------------------------------------------------------------------------
80 procedure PROCESS_ATTACHMENTS(
81 ENTITY_NAME VARCHAR2,
82 pk1 number,
83 pk2 number)
84 is
85 begin
86 -- delete attachments from main document
87 fnd_attached_documents2_pkg.
88 delete_attachments(entity_name,
89 pk1,
90 '',
91 '',
92 '',
93 '',
94 'N',
95 NULL);
96 -- copy attachments from draft to main document
97 fnd_attached_documents2_pkg.
98 copy_attachments(entity_name,
99 pk1 || '-' ||pk2,
100 '',
101 '',
102 '',
103 '',
104 entity_name,
105 pk1,
106 '',
107 '',
108 '',
109 '',
110 fnd_global.user_id,
111 fnd_global.user_id,
112 '',
113 '',
114 '');
115
116 end;
117
118 -------------------------------------------------------------------------------
119 --Bug 13938456 : Concurrent Mod Attachments
120 --Name: merge_upgraded_attachments
121 --Procedure to merge upgraded attachments for which ORIG_ATTACH_DOC_ID is null
122 --End of Comments
123 --------------------------------------------------------------------------------
124 PROCEDURE merge_upgraded_attachments(p_document_id IN NUMBER,
125 p_draft_id IN NUMBER)
126 is
127 cursor lines_with_attachments_c(p_draft_id number) is
128 select pld.po_line_id as po_line_id
129 from po_headers_draft_all phd,
130 po_lines_draft_all pld
131 where phd.draft_id = p_draft_id
132 and phd.po_header_id = pld.po_header_id
133 and exists (select 1
134 from fnd_attached_documents
135 where entity_name = 'PO_LINES'
136 and pk1_value = to_char(pld.po_line_id) || '-' || to_char(p_draft_id));
137
138 cursor line_locs_with_attachments_c(p_draft_id number) is
139 select plld.po_line_id as po_line_id,
140 plld.line_location_id as line_location_id
141 from po_headers_draft_all phd,
142 po_line_locations_draft_all plld
143 where phd.draft_id = p_draft_id
144 and phd.po_header_id = plld.po_header_id
145 and exists (select 1
146 from fnd_attached_documents
147 where entity_name = 'PO_SHIPMENTS'
148 and pk1_value = to_char(plld.line_location_id) || '-' || to_char(p_draft_id));
149
150 d_api_name CONSTANT VARCHAR2(30) := 'merge_upgraded_attachments';
151 d_module CONSTANT VARCHAR2(2000) := g_pkg_name || d_api_name || '.';
152 d_position NUMBER;
153 x_progress varchar2(1000);
154 l_header_attach_exist varchar2(1):='N';
155
156 Begin
157 d_position := 0;
158
159 begin
160 select 'Y'
161 into l_header_attach_exist
162 from dual
163 where exists (select 'header attachments exist'
164 from fnd_attached_documents
165 where entity_name = 'PO_HEADERS'
166 and pk1_value = to_char(p_document_id) || '-' || to_char(p_draft_id));
167 exception
168 when others then null;
169 end;
170
171 x_progress := 'Header attachment exists for Modification with draft_id '||p_draft_id;
172 IF (PO_LOG.d_stmt) THEN
173 PO_LOG.stmt(d_module, d_position, x_progress);
174 END IF;
175
176 if l_header_attach_exist = 'Y' then
177 process_attachments('PO_HEADERS', p_document_id, p_draft_id);
178 x_progress := 'Header attachment copied from Modification to header ';
179 IF (PO_LOG.d_stmt) THEN
180 PO_LOG.stmt(d_module, d_position, x_progress);
181 END IF;
182
183 end if;
184
185 d_position := 10;
186
187 for l_rec in lines_with_attachments_c(p_draft_id) loop
188 x_progress := 'Line attachment exists for Modification with draft_id '||p_draft_id||
189 ' and line id '||l_rec.po_line_id;
190 process_attachments('PO_LINES', l_rec.po_line_id, p_draft_id);
191 x_progress := 'Line attachment copied from Mod line to line ';
192 IF (PO_LOG.d_stmt) THEN
193 PO_LOG.stmt(d_module, d_position, x_progress);
194 END IF;
195 end loop;
196
197 d_position := 20;
198
199 for ll_rec in line_locs_with_attachments_c(p_draft_id) loop
200 x_progress := 'Shipment attachment exists for Modification with draft_id '||p_draft_id||
201 ' and line loc id '||ll_rec.line_location_id;
202 process_attachments('PO_SHIPMENTS', ll_rec.line_location_id, p_draft_id);
203 x_progress := 'Shipment attachment copied from Mod shipment to shipment ';
204 IF (PO_LOG.d_stmt) THEN
205 PO_LOG.stmt(d_module, d_position, x_progress);
206 END IF;
207 end loop;
208
209 Exception
210 When Others Then
211 d_position := 30;
212 x_progress := 'PO_DRAFT_MERGE_PKG.merge_upgraded_attachments: Exception';
213 IF (PO_LOG.d_stmt) THEN
214 PO_LOG.stmt(d_module, d_position, x_progress);
215 END IF;
216 raise;
217 End merge_upgraded_attachments;
218
219 -------------------------------------------------------------------------------
220 --Bug 13938456 : Concurrent Mod Attachments
221 --Name: MANAGE_ATTACHMENTS_MERGE
222 --Procedure to manage attachments merge.
223 --End of Comments
224 -------------------------------------------------------------------------------
225 -- Changes for bug 14046925
226 procedure MANAGE_ATTACHMENTS_MERGE(itemtype in varchar2,
227 itemkey in varchar2,
228 p_document_id in number,
229 p_document_type in varchar2,
230 p_document_subtype in varchar2,
231 p_draft_id in number)
232 is
233
234 x_progress varchar2(1000);
235 l_mod_attach_exist varchar2(1):='N';
236 l_attach_locks_exist varchar2(1):='N';
237 l_attach_with_orig_attach_id varchar2(1):='N';
238 l_draft_id Number := -1;
239 l_document_id Number := -1;
240 l_attach_count Number := 0;
241 Begin
242 x_progress := 'PO_DRAFT_MERGE_PKG.MANAGE_ATTACHMENTS_MERGE: Start: 01';
243 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
244 --Check if the modification has any attachments
245
246 BEGIN
247 select 'Y'
248 into l_mod_attach_exist
249 from dual
250 where exists (select 'attachments exist for mod'
251 from fnd_attached_documents
252 where pk1_value LIKE '%-'|| to_char(p_draft_id)
253 and entity_name in ('PO_HEADERS','PO_HEADERS_DEL','PO_LINES','PO_LINES_DEL','PO_SHIPMENTS','PO_SHIPMENTS_DEL'));
254 EXCEPTION
255 WHEN NO_DATA_FOUND THEN NULL;
256 END;
257
258 IF l_mod_attach_exist = 'Y' THEN
259 BEGIN
260
261 SELECT 'Y'
262 INTO l_attach_with_orig_attach_id
263 FROM DUAL
264 WHERE EXISTS (SELECT 'mod attachments exist with orig attach id not null'
265 FROM fnd_attached_documents
266 WHERE pk1_value LIKE '%-'|| to_char(p_draft_id)
267 AND entity_name in ('PO_HEADERS','PO_HEADERS_DEL','PO_LINES','PO_LINES_DEL','PO_SHIPMENTS','PO_SHIPMENTS_DEL')
268 AND ORIG_ATTACH_DOC_ID IS NOT NULL);
269
270 SELECT 'Y'
271 INTO l_attach_locks_exist
272 FROM DUAL
273 WHERE EXISTS (SELECT 'attachment locks exist'
274 FROM po_entity_locks
275 WHERE lock_by_draft_id = p_draft_id
276 AND entity_name IN ('PO_HEADERS_ATTACH','PO_LINES_ATTACH','PO_LINE_LOCATIONS_ATTACH'));
277
278 EXCEPTION
279 WHEN NO_DATA_FOUND then null;
280 END;
281
282 -- check if the attachments are upgraded attachments or not
283 -- TThe upgrade script will add locks in po_entity_locks table.
284 -- But the Orig Attach Id of these attachments will be NULL.
285 -- If the above two conditions are satisfied then , it is the case of
286 -- upgraded attachments
287 IF l_attach_locks_exist = 'Y' AND l_attach_with_orig_attach_id = 'N' THEN
288
289 --merge logic for upgraded attachments
290 merge_upgraded_attachments(p_document_id, p_draft_id);
291
292 --If it is not upgraded attachment case
293 ELSE
294
295 --Merging header level attachments
296 merge_header_attachments(p_document_id, p_draft_id);
297 x_progress := 'Header attachment copied from Modification to header ';
298 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
299
300 --Merging line level attachments
301 MERGE_LINE_ATTACHMENTS(p_draft_id);
302 x_progress := 'Line attachment copied from Modification to header ';
303 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
304
305 --Merging shipment level attachments
306 MERGE_LINE_LOCATION_ATTACHMENT(p_draft_id);
307 x_progress := 'Line locations attachment copied from Modification to header ';
308 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
309
310 END IF;
311
312 ELSE
313 x_progress := 'Attachments do not exist for the current modification ';
314 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
315 END IF;
316
317 Exception
318 When Others Then
319 x_progress := 'PO_DRAFT_MERGE_PKG.MANAGE_ATTACHMENTS_MERGE: Exception';
320 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
321 raise;
322 End;
323
324 procedure update_mod_approved_date(p_draft_id IN NUMBER,
325 p_document_id IN NUMBER)
326 IS
327 BEGIN
328 UPDATE po_headers_draft_all
329 SET approved_date = sysdate
330 WHERE draft_id = p_draft_id
331 AND po_header_id = p_document_id;
332
333 UPDATE po_drafts
334 SET mod_effective_date = sysdate
335 WHERE draft_id = p_draft_id
336 AND mod_effective_date is NULL;
337
338 END;
339
340 procedure INSERT_ACT_HIST_MERGE(
341 itemtype in varchar2,
342 itemkey in varchar2,
343 actid in number,
344 funcmode in varchar2,
345 resultout out NOCOPY varchar2
346 )
347 is
348 x_progress varchar2(1000);
349 l_draft_id Number := -1;
350 Begin
351
352 x_progress := 'PO_DRAFT_MERGE_PKG.INSERT_ACT_HIST_MERGE: Start: 01';
353 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
354 -- CLM Aprvl
355 l_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
356 itemkey => itemkey,
357 aname => 'DRAFT_ID');
358
359 If l_draft_id is Null Then
360 l_draft_id := -1;
361 End If;
362 -- CLM Aprvl
363 POR_AME_REQ_WF_PVT.update_action_history_approve(
364 itemtype,
365 itemkey,
366 actid,
367 funcmode,
368 resultout);
369
370 x_progress := 'PO_DRAFT_MERGE_PKG.INSERT_ACT_HIST_MERGE: End';
371 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
372
373 resultout := wf_engine.eng_completed || ':' || 'Y';
374 Exception
375 When Others Then
376 x_progress := 'PO_DRAFT_MERGE_PKG.INSERT_ACT_HIST_MERGE: Exception';
377 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
378 resultout := wf_engine.eng_completed || ':' || 'N';
379 raise;
380 End;
381
382 procedure IS_PDF_COMM_ENABLED(
383 itemtype in varchar2,
384 itemkey in varchar2,
385 actid in number,
386 funcmode in varchar2,
387 resultout out NOCOPY varchar2
388 ) is
389 x_progress varchar2(1000);
390 l_draft_id Number := -1;
391 Begin
392
393 x_progress := 'PO_DRAFT_MERGE_PKG.IS_PDF_COMM_ENABLED: Start: 01';
394 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
395 -- CLM Aprvl
396 l_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
397 itemkey => itemkey,
398 aname => 'DRAFT_ID');
399
400 If l_draft_id is Null Then
401 l_draft_id := -1;
402 End If;
403 -- CLM Aprvl
404 x_progress := 'PO_DRAFT_MERGE_PKG.IS_PDF_COMM_ENABLED: End';
405 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
406
407 resultout := wf_engine.eng_completed || ':' || 'Y';
408 Exception
409 When Others Then
410 x_progress := 'PO_DRAFT_MERGE_PKG.IS_PDF_COMM_ENABLED: Exception';
411 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
412 resultout := wf_engine.eng_completed || ':' || 'N';
413 raise;
414 End;
415
416 procedure DELETE_PDF_ATTACHMENTS(
417 itemtype in varchar2,
418 itemkey in varchar2,
419 actid in number,
420 funcmode in varchar2,
421 resultout out NOCOPY varchar2
422 )is
423 x_progress varchar2(1000);
424 l_draft_id Number := -1;
425 Begin
426
427 x_progress := 'PO_DRAFT_MERGE_PKG.DELETE_PDF_ATTACHMENTS: Start: 01';
428 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
429 -- CLM Aprvl
430 l_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
431 itemkey => itemkey,
432 aname => 'DRAFT_ID');
433
434 If l_draft_id is Null Then
435 l_draft_id := -1;
436 End If;
437 -- CLM Aprvl
438 x_progress := 'PO_DRAFT_MERGE_PKG.DELETE_PDF_ATTACHMENTS: End';
439 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
440
441 resultout := wf_engine.eng_completed || ':' || 'Y';
442 Exception
443 When Others Then
444 x_progress := 'PO_DRAFT_MERGE_PKG.DELETE_PDF_ATTACHMENTS: Exception';
445 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
446 resultout := wf_engine.eng_completed || ':' || 'N';
447 raise;
448 End;
449
450 procedure INSERT_ACT_HIST_REJECT(
451 itemtype in varchar2,
452 itemkey in varchar2,
453 actid in number,
454 funcmode in varchar2,
455 resultout out NOCOPY varchar2
456 )is
457 x_progress varchar2(1000);
458 l_draft_id Number := -1;
459 Begin
460
461 x_progress := 'PO_DRAFT_MERGE_PKG.INSERT_ACT_HIST_REJECT: Start: 01';
462 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
463 -- CLM Aprvl
464 l_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
465 itemkey => itemkey,
466 aname => 'DRAFT_ID');
467
468 If l_draft_id is Null Then
469 l_draft_id := -1;
470 End If;
471 -- CLM Aprvl
472
473 x_progress := 'PO_DRAFT_MERGE_PKG.INSERT_ACT_HIST_REJECT: calling POR_AME_REQ_WF_PVT.update_action_history_reject ';
474 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
475
476 x_progress := 'PO_DRAFT_MERGE_PKG.INSERT_ACT_HIST_REJECT: calling PO_REQAPPROVAL_ACTION.reject_doc';
477 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
478 POR_AME_REQ_WF_PVT.update_action_history_reject(
479 itemtype,
480 itemkey,
481 actid,
482 funcmode,
483 resultout);
484 -- bug 14046925 Call reject doc to reset document status to rejected.
485 PO_REQAPPROVAL_ACTION.reject_doc(
486 itemtype,
487 itemkey,
488 actid,
489 funcmode,
490 resultout);
491
492 x_progress := 'PO_DRAFT_MERGE_PKG.INSERT_ACT_HIST_MERGE: End';
493 x_progress := 'PO_DRAFT_MERGE_PKG.INSERT_ACT_HIST_REJECT: End';
494 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
495
496 resultout := wf_engine.eng_completed || ':' || 'Y';
497 Exception
498 When Others Then
499 x_progress := 'PO_DRAFT_MERGE_PKG.INSERT_ACT_HIST_REJECT: Exception';
500 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
501 resultout := wf_engine.eng_completed || ':' || 'N';
502 raise;
503 End;
504
505
506 -------------------------------------------------------------------------------
507 --Bug 13541195 : Auto Generation of change description not happening
508 --Name: UPDATE_AWARD_WITH_MOD_VALUES
509 --Procedure is called in merge process in approval workflow. This is called only
510 --for modifications
511 --End of Comments
512 -------------------------------------------------------------------------------
513 Procedure UPDATE_AWARD_WITH_MOD_VALUES(
514 itemtype in varchar2,
515 itemkey in varchar2,
516 actid in number,
517 funcmode in varchar2,
518 resultout out NOCOPY varchar2) is
519
520 l_control_action varchar2(30);
521 x_progress varchar2(1000);
522 l_draft_id Number := -1;
523 l_document_id Number := -1;
524 l_doc_string varchar2(200);
525 doc_manager_exception exception;
526 doc_manager_exception1 exception;
527
528 l_return_status VARCHAR2(1);
529 l_msg_count NUMBER;
530 l_msg_data VARCHAR2(2000);
531
532 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
533 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
534
535 l_note PO_ACTION_HISTORY.note%TYPE;
536 l_approval_path_id NUMBER;
537 l_exception_msg VARCHAR2(2000);
538 l_override_funds VARCHAR2(3) := NULL;
539 l_employee_id NUMBER;
540 l_po_return_code VARCHAR2(10);
541 l_Encumbrance_on BOOLEAN;
542 l_online_report_id NUMBER;
543 l_preparer_id NUMBER; --Bug 12944203
544 l_return_code VARCHAR2(10); -- Bug 13934702
545
546
547 Begin
548
549 savepoint UPDATE_AWARD_MOD_VALUES_SP; -- Bug 14046925 : Adding savepoint
550
551 x_progress := 'PO_DRAFT_MERGE_PKG.UPDATE_AWARD_WITH_MOD_VALUES: Start: 01';
552 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
553
554 l_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,itemkey => itemkey,aname => 'DRAFT_ID');
555 l_document_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,itemkey => itemkey,aname => 'DOCUMENT_ID');
556 l_document_type := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,itemkey => itemkey,aname => 'DOCUMENT_TYPE');
557 l_document_subtype := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,itemkey => itemkey,aname => 'DOCUMENT_SUBTYPE');
558 l_approval_path_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,itemkey => itemkey,aname => 'APPROVAL_PATH_ID');
559 l_employee_id := po_wf_util_pkg.Getitemattrnumber(itemtype => itemtype,itemkey => itemkey,aname => 'APPROVER_EMPID');
560 l_note := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,itemkey => itemkey,aname => 'NOTE');
561
562 Begin
563 l_Encumbrance_on := po_core_s.Is_encumbrance_on(p_doc_type => l_document_type,p_org_id => NULL);
564 EXCEPTION
565 WHEN OTHERS THEN
566 l_Encumbrance_on := FALSE;
567 END;
568
569 fnd_profile.Get('PO_REQAPPR_OVERRIDE_FUNDS',l_override_funds);
570 l_override_funds := Nvl(l_override_funds,'N');
571
572 If l_draft_id is Null Then
573 l_draft_id := -1;
574 End If;
575
576 IF (l_draft_id <> -1) THEN
577 SELECT fu.employee_id
578 INTO l_preparer_id
579 FROM fnd_user fu, PO_HEADERS_MERGE_V ph
580 WHERE fu.user_id = ph.OWNER_USER_ID
581 AND ph.po_header_id = l_document_id
582 AND ph.draft_id = l_draft_id ;
583
584 IF(l_preparer_id = l_employee_id) THEN
585
586 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
587 itemkey => itemkey,
588 aname => 'NOTE',
589 avalue => '');
590
591 PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove(itemtype, itemkey,
592 l_draft_id, l_document_type, 'MODIFICATION');
593 END IF;
594
595 ELSE
596
597 l_preparer_id := po_wf_util_pkg.GetItemAttrNumber(
598 itemtype => itemtype,
599 itemkey => itemkey,
600 aname => 'PREPARER_ID');
601
602 IF(l_preparer_id = l_employee_id) THEN
603
604 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
605 itemkey => itemkey,
606 aname => 'NOTE',
607 avalue => '');
608
609 PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove(itemtype, itemkey,
610 l_document_id, l_document_type, l_document_subtype);
611
612 END IF;
613
614 END IF;
615
616 x_progress := 'Calling MergeReserve_Autonomous with Draft : '||l_draft_id ||' Document Id : ' || l_document_id;
617 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, x_progress );
618
619 MergeReserve_Autonomous(
620 itemtype => itemtype,
621 itemkey => itemkey,
622 p_draft_id => l_draft_id,
623 P_Document_Id =>l_document_id,
624 p_doc_type => l_document_type,
625 p_doc_subtype => l_document_subtype,
626 p_override_funds => l_override_funds,
627 p_employee_id => l_employee_id,
628 p_approval_path_id => l_approval_path_id ,
629 p_note => l_note,
630 x_po_return_code => l_po_return_code,
631 x_online_report_id => l_online_report_id,
632 x_return_status=> l_return_status);
633
634 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous - Return Status: '|| l_return_status ;
635 po_wf_debug_pkg.Insert_debug(itemtype,itemkey,x_progress );
636
637 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR OR l_return_status IS NULL) THEN
638 -- copying message from po_online_report_text_gt
639 g_action:= 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : ';
640 SELECT TEXT_LINE into g_error_message FROM po_online_report_text_gt WHERE online_report_id = l_online_report_id
641 and rownum =1;
642 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, g_action || g_error_message );
643 g_result := l_return_status;
644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
645
646 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
647 IF (l_Encumbrance_on) AND (l_po_return_code NOT IN ( po_document_funds_pvt.g_return_success, PO_DOCUMENT_FUNDS_PVT.g_return_WARNING))
648 THEN
649 x_progress := 'MergeReserve_Autonomous - Po Return Code: '|| l_po_return_code;
650 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, x_progress );
651 resultout := wf_engine.eng_completed || ':' || 'N';
652 RETURN;
653 END IF;
654 END IF;
655
656 -- Bug 14277142 : Calling iP rebuild index only for Blanket so that line items becomes serachable in iP store.
657 IF (l_document_type = 'PA' AND l_document_subtype = 'BLANKET')
658 THEN
659 -- Rebuild catalog search index.
660 BEGIN
661 x_progress := 'Calling ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index : ';
662 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, x_progress );
663 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
664 EXCEPTION
665 WHEN OTHERS THEN
666 g_error_message := 'Exception for ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index and erorr encountered is ' ||SQLCODE||' -ERROR- '||SQLERRM ;
667 x_progress := 'PO_DRAFT_MERGE_PKG.UPDATE_AWARD_WITH_MOD_VALUES: ' || g_error_message;
668 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
669 END;
670 END IF;
671
672 IF l_draft_id <> -1 THEN
673 PO_MOD_SYNC_PROCESS_PVT.RELEASE_SYNC_SGD_LOCK(itemtype => itemtype
674 , itemkey => itemkey
675 , actid => actid
676 , funcmode => funcmode
677 , resultout => resultout);
678 END IF;
679 resultout := wf_engine.eng_completed || ':' || 'Y';
680
681 Exception
682 When Others Then
683 ROLLBACK to UPDATE_AWARD_MOD_VALUES_SP; -- Rolling back to savepoint in case of any errors
684 x_progress := 'PO_DRAFT_MERGE_PKG.UPDATE_AWARD_WITH_MOD_VALUES: Exception';
685 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
686 -- Bug14046925 : Setting workflow attributes with values
687 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
688 itemkey => itemkey,
689 aname => 'MERGE_RESERVE_ACTION',
690 avalue => g_action);
691
692 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
693 itemkey => itemkey,
694 aname => 'MERGE_RESERVE_RESULT',
695 avalue => g_action);
696
697 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
698 itemkey => itemkey,
699 aname => 'MERGE_RESERVE_ERROR_MESSAGE',
700 avalue => g_action);
701
702 PO_MOD_SYNC_PROCESS_PVT.RELEASE_SYNC_SGD_LOCK(itemtype => itemtype
703 , itemkey => itemkey
704 , actid => actid
705 , funcmode => funcmode
706 , resultout => resultout);
707 resultout := wf_engine.eng_completed || ':' || 'N';
708 --raise;
709 End UPDATE_AWARD_WITH_MOD_VALUES;
710
711 PROCEDURE PROCESS_CONTROL_ACTIONS(
712 itemtype in varchar2,
713 itemkey in varchar2,
714 actid in number,
715 funcmode in varchar2,
716 resultout out NOCOPY varchar2
717 )
718 is
719 l_draft_id PO_DRAFTS.draft_id%TYPE;
720 cursor control_action_line_c is
721 select nvl(pld.control_action, 'NULL') as control_action,
722 pld.po_line_id as po_line_id
723 from po_headers_draft_all phd,
724 po_lines_draft_all pld
725 where phd.draft_id = l_draft_id
726 and phd.po_header_id = pld.po_header_id
727 and pld.control_action is not NULL;
728
729 cursor control_action_line_loc_c is
730 select nvl(plld.control_action, 'NULL') as control_action,
731 plld.po_line_id as po_line_id,
732 plld.line_location_id as po_line_location_id
733 from po_headers_draft_all phd,
734 po_line_locations_draft_all plld
735 where phd.draft_id = l_draft_id
736 and phd.po_header_id = plld.po_header_id
737 and plld.control_action is not NULL;
738
739
740 l_control_action varchar2(30);
741 p_document_id NUMBER;
742 v_document_type PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
743 v_document_subtype PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
744 v_type_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
745 v_doc_line_id NUMBER;--13004199
746 l_line_id NUMBER;
747 l_shipment_id NUMBER;
748 x_return_status VARCHAR2(1);
749 l_cancel_reason VARCHAR2(240);
750 l_close_reason VARCHAR2(240);
751 l_date DATE;
752 l_exception_msg VARCHAR2(2000);
753 l_return_code VARCHAR2(40);
754 l_online_report_id NUMBER;
755 x_progress varchar2(1000);
756
757 BEGIN
758
759 x_progress := 'PO_DRAFT_MERGE_PKG.PROCESS_CONTROL_ACTIONS: Start: 01';
760 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
761
762 l_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,itemkey => itemkey,aname => 'DRAFT_ID');
763 IF l_draft_id IS NOT NULL ANd l_draft_id <> -1 THEN
764 select phd.type_lookup_code,
765 nvl(phd.control_action, 'NULL'),
766 phd.po_header_id,
767 pd.mod_effective_date
768 into v_type_code,
769 l_control_Action,
770 p_document_id,
771 l_date
772 from po_headers_draft_all phd,
773 po_drafts pd
774 where phd.draft_id = l_draft_id
775 and phd.draft_id = pd.draft_id;
776
777 if (v_type_code in ('STANDARD','PLANNED')) then
778 v_document_type := 'PO';
779 v_document_subtype := v_type_code;
780 elsif (v_type_code in ('BLANKET','CONTRACT')) then
781 v_document_type := 'PA';
782 v_document_subtype := v_type_code;
783 end if;
784 fnd_message.set_name('PO', 'PO_MOD_CANCEL_REASON');
785 fnd_message.set_token('DRAFT_ID', l_draft_id);
786 l_cancel_reason := fnd_message.get;
787 fnd_message.set_name('PO', 'PO_MOD_CLOSE_REASON');
788 fnd_message.set_token('DRAFT_ID', l_draft_id);
789 l_close_reason := fnd_message.get;
790 --Header Level
791 If l_control_action = 'CANCEL' then
792 v_doc_line_id := null; --bug 13004199
793 PO_Document_Control_GRP.control_document(
794 p_api_version => 1.0,
795 p_init_msg_list => FND_API.G_TRUE,
796 p_commit => FND_API.G_FALSE,
797 x_return_status => x_return_status,
798 p_doc_type => v_document_type,
799 p_doc_subtype => v_document_subtype,
800 p_doc_id => p_document_id,
801 p_doc_num => null,
802 p_release_id => null,
803 p_release_num => null,
804 p_doc_line_id => v_doc_line_id,--bug 13004199, use NUMBER type v_doc_line_id instead of assigning null
805 p_doc_line_num => null,
806 p_doc_line_loc_id => null,
807 p_doc_shipment_num => null,
808 p_source => null,
809 p_action => 'CANCEL',
810 p_action_date => l_date,
811 p_cancel_reason => l_cancel_reason,
812 p_cancel_reqs_flag => null,
813 p_print_flag => null,
814 p_note_to_vendor =>NULL);
815 elsif l_control_action = 'CLOSE' or
816 l_control_action = 'FINALLY CLOSE' then
817 PO_DOCUMENT_ACTION_PVT.do_manual_close(
818 p_action => l_control_action
819 , p_document_id => p_document_id
820 , p_document_type => v_document_type
821 , p_document_subtype => v_document_subtype
822 , p_line_id => NULL
823 , p_shipment_id => NULL
824 , p_reason => l_close_reason
825 , p_action_date => l_date
826 , x_return_status => x_return_status
827 , x_exception_msg => l_exception_msg
828 , x_return_code => l_return_code
829 , x_online_report_id => l_online_report_id
830 );
831
832 end if;
833 -- Line Level
834
835 for control_action_line_rec in control_action_line_c loop
836
837 If control_action_line_rec.control_action = 'CANCEL' then
838 PO_Document_Control_GRP.control_document(
839 p_api_version => 1.0,
840 p_init_msg_list => FND_API.G_TRUE,
841 p_commit => FND_API.G_FALSE,
842 x_return_status => x_return_status,
843 p_doc_type => v_document_type,
844 p_doc_subtype => v_document_subtype,
845 p_doc_id => p_document_id,
846 p_doc_num => null,
847 p_release_id => null,
848 p_release_num => null,
849 p_doc_line_id => control_action_line_rec.po_line_id,
850 p_doc_line_num => null,
851 p_doc_line_loc_id => null,
852 p_doc_shipment_num => null,
853 p_source => null,
854 p_action => 'CANCEL',
855 p_action_date => l_date,
856 p_cancel_reason => l_cancel_reason,
857 p_cancel_reqs_flag => null,
858 p_print_flag => null,
859 p_note_to_vendor =>NULL);
860
861 elsif control_action_line_rec.control_action = 'CLOSE' or
862 control_action_line_rec.control_action = 'FINALLY CLOSE' then
863 PO_DOCUMENT_ACTION_PVT.do_manual_close(
864 p_action => control_action_line_rec.control_action
865 , p_document_id => p_document_id
866 , p_document_type => v_document_type
867 , p_document_subtype => v_document_subtype
868 , p_line_id => control_action_line_rec.po_line_id
869 , p_shipment_id => NULL
870 , p_reason => l_close_reason
871 , p_action_date => l_date
872 , x_return_status => x_return_status
873 , x_exception_msg => l_exception_msg
874 , x_return_code => l_return_code
875 , x_online_report_id => l_online_report_id
876 );
877 end if;
878
879 end loop;
880 -- Shipment Level
881
882 for control_action_line_loc_rec in control_action_line_loc_c loop
883
884 If control_action_line_loc_rec.control_action = 'CANCEL' then
885 PO_Document_Control_GRP.control_document(
886 p_api_version => 1.0,
887 p_init_msg_list => FND_API.G_TRUE,
888 p_commit => FND_API.G_FALSE,
889 x_return_status => x_return_status,
890 p_doc_type => v_document_type,
891 p_doc_subtype => v_document_subtype,
892 p_doc_id => p_document_id,
893 p_doc_num => null,
894 p_release_id => null,
895 p_release_num => null,
896 p_doc_line_id => control_action_line_loc_rec.po_line_id,
897 p_doc_line_num => null,
898 p_doc_line_loc_id => control_action_line_loc_rec.po_line_location_id,
899 p_doc_shipment_num => null,
900 p_source => null,
901 p_action => 'CANCEL',
902 p_action_date => l_date,
903 p_cancel_reason => l_cancel_reason,
904 p_cancel_reqs_flag => null,
905 p_print_flag => null,
906 p_note_to_vendor =>NULL);
907
908 elsif control_action_line_loc_rec.control_action = 'CLOSE' or
909 control_action_line_loc_rec.control_action = 'FINALLY CLOSE' then
910 PO_DOCUMENT_ACTION_PVT.do_manual_close(
911 p_action => control_action_line_loc_rec.control_action
912 , p_document_id => p_document_id
913 , p_document_type => v_document_type
914 , p_document_subtype => v_document_subtype
915 , p_line_id => control_action_line_loc_rec.po_line_id
916 , p_shipment_id => control_action_line_loc_rec.po_line_location_id
917 , p_reason => l_close_reason
918 , p_action_date => l_date
919 , x_return_status => x_return_status
920 , x_exception_msg => l_exception_msg
921 , x_return_code => l_return_code
922 , x_online_report_id => l_online_report_id
923 );
924
925 end if;
926 end loop;
927 END IF;
928 resultout := wf_engine.eng_completed || ':' || 'Y';
929 EXCEPTION
930 WHEN OTHERS THEN
931 x_progress := 'PO_DRAFT_MERGE_PKG.Process_control_actions: Exception';
932 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
933 resultout := wf_engine.eng_completed || ':' || 'N';
934 raise;
935 END PROCESS_CONTROL_ACTIONS;
936
937
938
939 procedure IS_CLM_DOCUMENT(
940 itemtype in varchar2,
941 itemkey in varchar2,
942 actid in number,
943 funcmode in varchar2,
944 resultout out NOCOPY varchar2
945 )
946 is
947 x_progress varchar2(1000);
948 l_document_id Number;
949 l_is_clm_document varchar2(1);
950 Begin
951
952 x_progress := 'PO_DRAFT_MERGE_PKG.IS_CLM_DOCUMENT: Start: 01';
953 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
954
955 l_document_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
956 itemkey => itemkey,
957 aname => 'DOCUMENT_ID');
958
959 BEGIN
960 SELECT clm_flag
961 INTO l_is_clm_document
962 FROM po_doc_style_headers psh,
963 po_headers_all poh
964 WHERE psh.style_id = poh.style_id
965 AND poh.po_header_id = l_document_id;
966 EXCEPTION
967 WHEN no_data_found THEN
968 x_progress := 'PO_DRAFT_MERGE_PKG.IS_CLM_DOCUMENT: in no data found exception';
969 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
970 resultout := wf_engine.eng_completed || ':' || 'N';
971 END;
972
973 x_progress := 'PO_DRAFT_MERGE_PKG.IS_CLM_DOCUMENT: l_is_clm_document: '||l_is_clm_document;
974 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
975 resultout := wf_engine.eng_completed || ':' || l_is_clm_document;
976
977
978 Exception
979 When Others Then
980 x_progress := 'PO_DRAFT_MERGE_PKG.IS_CLM_DOCUMENT: Exception';
981 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
982 raise;
983 End;
984
985 PROCEDURE MergeReserve_Autonomous(
986 itemtype IN VARCHAR2,
987 itemkey IN VARCHAR2,
988 p_draft_id IN NUMBER,
989 P_Document_Id IN NUMBER,
990 p_doc_type IN VARCHAR2,
991 p_doc_subtype IN VARCHAR2,
992 p_override_funds IN VARCHAR2,
993 p_employee_id IN NUMBER,
994 p_approval_path_id IN NUMBER,
995 p_note PO_ACTION_HISTORY.note%TYPE,
996 x_po_return_code OUT NOCOPY VARCHAR2,
997 x_online_report_id OUT NOCOPY NUMBER,
998 x_return_status OUT NOCOPY VARCHAR2 )
999 IS
1000 pragma AUTONOMOUS_TRANSACTION;
1001 l_return_status VARCHAR2(1);
1002 x_progress VARCHAR2(2000);
1003 l_Encumbrance_on BOOLEAN;
1004 -- Mod Commercialization
1005 l_functional_area_code VARCHAR2(500);
1006 l_document_type_code VARCHAR2(500);
1007 l_document_style_id NUMBER;
1008 l_enabled_flag VARCHAR2(5);
1009 l_errorcode VARCHAR2(50);
1010 l_msg_count NUMBER;
1011 l_msg_data VARCHAR2(4000);
1012 x_exception_msg VARCHAR2(1000);
1013 l_note PO_ACTION_HISTORY.note%TYPE;
1014 resultout VARCHAR2(1);
1015 l_is_clm_document VARCHAR2(1);
1016 BEGIN
1017
1018 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,'MergeReserve_Autonomous : Start');
1019 SAVEPOINT MergeReserve_Autonomous_SP;
1020 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: Savepoint MergeReserve_Autonomous_SP';
1021 /*fed fields : update IDV total_amt_ordered for header and line and total qty ordered for line*/
1022 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : PO_DOCUMENT_ACTION_UTIL.upd_idv_qty_amt_hdr';
1023 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1024 PO_DOCUMENT_ACTION_UTIL.upd_idv_qty_amt_hdr(p_po_header_id => P_Document_Id
1025 , p_draft_id => p_draft_id
1026 , p_po_mode => PO_DOCUMENT_ACTION_PVT.g_doc_action_APPROVE
1027 , x_return_status => x_return_status
1028 );
1029 IF p_draft_id <> -1 THEN
1030 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: Draft Id: '||p_draft_id ||'. Merge with Award Id: '||P_Document_Id;
1031 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1032
1033 -- Copying contract details to base document
1034 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : PO_CONTERMS_UTL_GRP.copy_conterms_mod';
1035 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1036 PO_CONTERMS_UTL_GRP.copy_conterms_mod(
1037 p_document_id => p_document_id,
1038 p_draft_id => p_draft_id,
1039 p_to_mod => 'N',
1040 p_mode => 'MERGE', -- <Conc Mods Project>
1041 x_return_status => l_return_status);
1042 -- Bug 14277142 : Adding parameter p_caller => 'MERGE' so that ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index
1043 -- done as a part of bug fix is not called.
1044 PO_DRAFTS_PVT.TRANSFER_DRAFT_TO_TXN(
1045 p_api_version => 1.0,
1046 p_init_msg_list => FND_API.G_TRUE ,
1047 p_draft_id => p_draft_id,
1048 p_po_header_id => P_Document_Id,
1049 p_delete_processed_draft => FND_API.G_FALSE ,
1050 p_acceptance_action => NULL,
1051 x_return_status => l_return_status,
1052 p_caller => 'MERGE');
1053
1054 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: Merge Return Status: '||l_return_status;
1055 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1056
1057 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1058 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: Merge Successful';
1059 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
1060 ELSE
1061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1062 END IF;
1063
1064 -- Update the approved_date of the header draft
1065 -- which is used as the signed date of the modification
1066 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : update_mod_approved_date';
1067 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1068 update_mod_approved_date(p_draft_id => p_draft_id,
1069 p_document_id => p_document_id);
1070
1071 --Mod Commercialization
1072
1073 SELECT 'PURCHASING', TYPE_LOOKUP_CODE, STYLE_ID
1074 INTO l_functional_area_code, l_document_type_code, l_document_style_id
1075 FROM po_headers_all
1076 WHERE PO_HEADER_ID = p_document_id;
1077
1078 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : PO_CLM_CLO_UTIL.Check_Uda_Enabled';
1079 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1080 PO_CLM_CLO_UTIL.Check_Uda_Enabled
1081 (
1082 p_functional_area_code => l_functional_area_code
1083 ,p_document_type_code => l_document_type_code
1084 ,p_document_style_id => l_document_style_id
1085 ,x_enabled_flag => l_enabled_flag
1086 ,x_return_status => l_return_status
1087 ,x_errorcode => l_errorcode
1088 ,x_msg_count => l_msg_count
1089 ,x_msg_data => l_msg_data
1090 );
1091
1092 x_progress := 'After Check_Uda_Enabled with x_return_status : ' || l_return_status;
1093 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
1094
1095 x_progress := 'Check_Uda_Enabled with x_enabled_flag : ' || l_enabled_flag;
1096 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
1097
1098 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1099 THEN
1100 g_result := l_return_status;
1101 g_error_message := l_msg_data ;
1102 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_error_message);
1103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1104 END IF;
1105
1106 IF l_enabled_flag = 'Y' THEN
1107
1108 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : PO_DOC_UDA_UTIL.merge_mod_uda';
1109 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1110 PO_DOC_UDA_UTIL.merge_mod_uda(
1111 p_po_header_id => P_Document_Id,
1112 p_draft_id => p_draft_id,
1113 x_return_status => l_return_status );
1114
1115 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: Uda Merge Return Status: '||l_return_status;
1116 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1117
1118 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1120 END IF;
1121
1122 -- TODO : THIS CHECK IS IRRELAVENT AS TRANSFER TO DRAFT DOESN'T RETURN TRUE/FALSE
1123 If l_return_status <> FND_API.G_FALSE THEN
1124 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: Merge Successful';
1125 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
1126 End If;
1127
1128 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : PO_DRAFTS_PVT.update_revnum_headers_ext';
1129 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1130 PO_DRAFTS_PVT.update_revnum_headers_ext(p_draft_id =>p_draft_id,
1131 p_po_header_id =>P_Document_Id);
1132
1133 END IF;
1134 --End Mod Commercialization
1135 -- <Conc Mod Project>
1136 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : delete_entity_locks';
1137 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1138 delete_entity_locks(p_draft_id => p_draft_id
1139 ,x_return_status => l_return_status);
1140
1141 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1142 x_progress := 'PO_DRAFT_MERGE_PKG.UPDATE_AWARD_WITH_MOD_VALUES: Deleting entity locks failed';
1143 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1145 END IF;
1146
1147 x_progress := 'PO_DRAFT_MERGE_PKG.UPDATE_AWARD_WITH_MOD_VALUES: deleting entity locks successful';
1148 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1149 --end of concurrent
1150
1151 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : PO_DOCUMENT_ACTION_PVT.do_approve for Mod';
1152 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1153 PO_DOCUMENT_ACTION_PVT.do_approve(
1154 p_document_id => P_Document_Id
1155 , p_document_type => p_doc_type
1156 , p_document_subtype => p_doc_subtype
1157 , p_draft_id => p_draft_id
1158 , p_note => p_note
1159 , p_approval_path_id => p_approval_path_id
1160 , x_return_status => x_return_status
1161 , x_exception_msg => x_exception_msg
1162 );
1163 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1164 g_result := l_return_status;
1165 g_error_message := x_exception_msg || 'Exception while call to PO_DOCUMENT_ACTION_PVT.do_approve failed';
1166 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_error_message);
1167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1168 END IF;
1169
1170 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : MANAGE_ATTACHMENTS_MERGE';
1171 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1172 -- Copying the attachments
1173 MANAGE_ATTACHMENTS_MERGE(itemtype ,
1174 itemkey ,
1175 p_document_id => P_Document_Id,
1176 p_document_type => p_doc_type,
1177 p_document_subtype => p_doc_subtype,
1178 p_draft_id => p_draft_id);
1179 END IF; -- draft_id <> -1 , Mod loop
1180
1181 Begin
1182 l_Encumbrance_on := po_core_s.Is_encumbrance_on(p_doc_type => p_doc_type,p_org_id => NULL);
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 l_Encumbrance_on := FALSE;
1186 END;
1187
1188 -- Call Reserve only if Encumbrance is On
1189 IF ( l_Encumbrance_on) THEN
1190
1191 --Encumbrance is Not Supported for CLM Agreements
1192 IF (p_doc_type = 'PA' AND
1193 Po_Partial_Funding_pkg.Is_clm_Document(p_doc_Type => 'PA', p_doc_level_id => p_document_id ) = 'Y') THEN
1194 po_wf_debug_pkg.Insert_debug(itemtype,itemkey,' Skip Reserve Call, as Encumbrance is Not supported for CLM Agreements');
1195
1196 ELSE
1197 po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Calling do Reserve with doctype: '||p_doc_type||' - sub: '||p_doc_subtype||'- document id: '||p_document_id||' - Ovrride: '||p_override_funds||'- Emp: '||p_employee_id||'.');
1198 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : po_document_funds_pvt.Do_reserve';
1199 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1200
1201 G_RESERVE_FROM_MOD_MERGE := 'Y'; -- for bypassing co_validate_warrant chec
1202 po_document_funds_pvt.Do_reserve(x_return_status => x_return_status,p_doc_type => p_doc_type,
1203 p_doc_subtype => p_doc_subtype,p_doc_level => po_document_funds_pvt.g_doc_level_header,
1204 p_doc_level_id => p_document_id,
1205 p_use_enc_gt_flag => po_document_funds_pvt.g_parameter_no,
1206 p_prevent_partial_flag => po_document_funds_pvt.g_parameter_no,
1207 p_validate_document => po_document_funds_pvt.g_parameter_yes,
1208 p_override_funds => p_override_funds,p_employee_id => p_employee_id,
1209 x_po_return_code => x_po_return_code,x_online_report_id => x_online_report_id);
1210 G_RESERVE_FROM_MOD_MERGE := 'N';
1211
1212 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: Do Reserve Return Status: '||x_return_status;
1213 po_wf_debug_pkg.Insert_debug(itemtype,itemkey,x_progress);
1214 -- bug 13709183 Autonomous transaction must rollback in case of any error (expected, unexpected)
1215 -- IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1216 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1217 -- fetch error message
1218 SELECT TEXT_LINE into g_error_message FROM po_online_report_text_gt WHERE online_report_id = x_online_report_id
1219 and rownum=1;
1220 g_result := x_return_status;
1221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1222 END IF;
1223 END IF;-- PA Check
1224 END IF; --Enc On Check
1225
1226 g_action := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous : PO_DOCUMENT_ACTION_PVT.do_approve for base doc';
1227 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_action);
1228 PO_DOCUMENT_ACTION_PVT.do_approve(
1229 p_document_id => P_Document_Id
1230 , p_document_type => p_doc_type
1231 , p_document_subtype => p_doc_subtype
1232 , p_draft_id => -1
1233 , p_note => p_note
1234 , p_approval_path_id => p_approval_path_id
1235 , x_return_status => x_return_status
1236 , x_exception_msg => x_exception_msg
1237 );
1238 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1239 -- fetch error message
1240 g_result := x_return_status;
1241 g_error_message := x_exception_msg || 'Exception while call for base doc PO_DOCUMENT_ACTION_PVT.do_approve failed';
1242 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,g_error_message);
1243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1244
1245 END IF;
1246
1247 --<Event Based Delivery>
1248 BEGIN
1249 SELECT clm_flag
1250 INTO l_is_clm_document
1251 FROM po_doc_style_headers psh,
1252 po_headers poh
1253 WHERE psh.style_id = poh.style_id
1254 AND poh.po_header_id = p_document_id;
1255 EXCEPTION
1256 WHEN no_data_found THEN
1257 l_is_clm_document := 'N';
1258 END;
1259
1260 IF (l_is_clm_document = 'Y') THEN
1261 update_delivery_event_attr(
1262 p_document_id => p_document_id,
1263 p_draft_id => p_draft_id);
1264
1265 -- <PAR Project>
1266 -- Check if the MOD has PARs linked through autocreate
1267 -- Update those PAR Line status to PO_CREATED
1268 IF p_draft_id <> -1 THEN
1269 update_par_line_status(itemtype => itemtype,
1270 itemkey => itemkey,
1271 p_document_id => p_document_id,
1272 p_draft_id => p_draft_id);
1273 END IF;
1274 END IF;
1275
1276 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: Approve Action Successful.';
1277 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1278 x_return_status := FND_API.G_RET_STS_SUCCESS;
1279 COMMIT;
1280 EXCEPTION
1281
1282 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1283 g_error_message := g_error_message || ' erorr encountered is ' ||SQLCODE||' -ERROR- '||SQLERRM ;
1284 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: in exception block with error ' || g_error_message;
1285 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1286 ROLLBACK TO MergeReserve_Autonomous_SP;
1287 wf_core.context('PO_REQAPPROVAL_ACTION','MergeReserve_Autonomous', x_progress);
1288 RAISE;
1289 WHEN OTHERS THEN
1290 wf_core.Context('PO_DRAFT_MERGE_PKG','MergeReserve_Autonomous', x_progress);
1291 x_progress := 'PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous: in exception block (when others) with error ' || g_error_message;
1292 g_error_message := g_error_message || ' erorr encountered is ' ||SQLCODE||' -ERROR- '||SQLERRM ;
1293 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1294 ROLLBACK TO MergeReserve_Autonomous_SP;
1295 RAISE;
1296 END MergeReserve_Autonomous;
1297 --
1298
1299 procedure call_docnum_post_approval(
1300 itemtype in varchar2,
1301 itemkey in varchar2,
1302 actid in number,
1303 funcmode in varchar2,
1304 resultout out NOCOPY varchar2
1305 )
1306 IS
1307 x_progress varchar2(1000);
1308 l_draft_id Number := -1;
1309 l_po_hdr_id NUMBER;
1310 l_uda_template_id NUMBER;
1311 Begin
1312
1313 x_progress := 'PO_DRAFT_MERGE_PKG.call_docnum_post_approval: Start: 01';
1314 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1315
1316 l_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
1317 itemkey => itemkey,
1318 aname => 'DRAFT_ID');
1319
1320 l_po_hdr_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
1321 itemkey => itemkey,
1322 aname => 'DOCUMENT_ID');
1323
1324 IF l_draft_id IS NOT NULL AND l_draft_id <> -1 THEN
1325
1326
1327 SELECT uda_template_id
1328 INTO l_uda_template_id
1329 FROM po_headers_draft_all
1330 WHERE po_header_id = l_po_hdr_id
1331 AND draft_id = l_draft_id;
1332
1333 -- <Bug 16491111>
1334 -- Calling po_doc_numbering_pkg.process_dod_awd_mod_doc_num
1335 -- in an autonomous transaction as any error in workflow after this
1336 -- will leave mod in Approved state but contorl character will be C only.
1337
1338 docnum_post_apprvl_autonomous(itemtype => itemtype,
1339 itemkey => itemkey,
1340 p_document_id => l_po_hdr_id,
1341 p_draft_id => l_draft_id,
1342 p_uda_template_id => l_uda_template_id);
1343
1344 END IF;
1345
1346 Exception
1347 When Others Then
1348 x_progress := 'PO_DRAFT_MERGE_PKG.call_docnum_post_approval: Exception ' || SQLERRM;
1349 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1350 wf_core.context('PO_DRAFT_MERGE_PKG','call_docnum_post_approval', x_progress);
1351 raise;
1352 END call_docnum_post_approval;
1353
1354 procedure COPY_ATTACHMENTS(
1355 ENTITY_NAME VARCHAR2,
1356 pk1 VARCHAR2,
1357 pk2 VARCHAR2)
1358 is
1359 pragma AUTONOMOUS_TRANSACTION;
1360 begin
1361 fnd_attached_documents2_pkg.
1362 copy_attachments(entity_name,
1363 pk1,
1364 '',
1365 '',
1366 '',
1367 '',
1368 entity_name,
1369 pk2,
1370 '',
1371 '',
1372 '',
1373 '',
1374 fnd_global.user_id,
1375 fnd_global.user_id,
1376 '',
1377 '',
1378 '');
1379 commit;
1380
1381 end;
1382
1383 /*Bug 11727653: BYPASSING MULTIPLE SUBMISSION CHECKS IN WORKFLOW
1384 Function to check whether a document is mod or not*/
1385
1386 PROCEDURE IS_MOD_DOCUMENT (itemtype IN VARCHAR2,
1387 itemkey IN VARCHAR2,
1388 actid IN NUMBER,
1389 funcmode IN VARCHAR2,
1390 resultout OUT NOCOPY VARCHAR2)
1391 IS
1392 l_is_mod_document VARCHAR2(1) := 'N';
1393 l_draft_id NUMBER;
1394 x_progress VARCHAR2(1000);
1395 BEGIN
1396
1397 l_draft_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
1398 itemkey => itemkey,
1399 aname => 'DRAFT_ID');
1400
1401 x_progress := 'PO_DRAFT_MERGE_PKG.IS_MOD_DOCUMENT: Draft_id: '
1402 || l_draft_id;
1403 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
1404
1405 BEGIN
1406 SELECT 'Y'
1407 INTO l_is_mod_document
1408 FROM po_drafts
1409 WHERE draft_id = l_draft_id
1410 AND draft_type = 'MOD';
1411 EXCEPTION
1412 WHEN No_Data_Found THEN
1413 x_progress := 'PO_DRAFT_MERGE_PKG.IS_MOD_DOCUMENT: in no data found exception';
1414 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1415 END;
1416
1417 resultout := wf_engine.eng_completed || ':' || l_is_mod_document;
1418
1419 EXCEPTION
1420 WHEN OTHERS THEN
1421 x_progress := 'PO_DRAFT_MERGE_PKG.IS_MOD_DOCUMENT: Exception';
1422 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
1423 RAISE;
1424 END;
1425
1426
1427 --------------------------------------------------------------------------------
1428 --Start of Comments
1429 --Name: delete_entity_locks
1430 -- <Conc Mods Project>
1431 --Pre-reqs: None
1432 --Modifies:
1433 -- po_entity_locks
1434 --Function:
1435 -- This procedure deletes records from po_entity_locks for the given p_draft_id
1436 --Parameters:
1437 --IN:
1438 --p_draft_id
1439 -- draft id of the current Modification - Award/IDV
1440 --OUT:
1441 --x_return_status
1442 --returns Y if succesful,N if unsuccessful
1443 --Notes:
1444 --End of Comments
1445 --------------------------------------------------------------------------------
1446 PROCEDURE delete_entity_locks( p_draft_id IN NUMBER
1447 ,x_return_status IN OUT NOCOPY VARCHAR2)
1448 IS
1449 BEGIN
1450
1451 DELETE FROM po_entity_locks
1452 WHERE lock_by_draft_id = p_draft_id;
1453
1454 -- TODO: Add call to OKC procedure to delete OKC locks for the Modification
1455
1456 x_return_status := FND_API.G_RET_STS_SUCCESS;
1457
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1461 END delete_entity_locks;
1462
1463 -------------------------------------------------------------------------------
1464 --Bug 13938456 : Concurrent Mod Attachments
1465 --Name: MERGE_HEADER_ATTACHMENTS
1466 --Procedure to merge header level attachments
1467 --End of Comments
1468 -------------------------------------------------------------------------------
1469
1470 procedure MERGE_HEADER_ATTACHMENTS(p_header_id NUMBER,
1471 p_draft_id NUMBER) AS
1472
1473 cursor modified_header_attachments_c(p_header_id NUMBER,p_draft_id NUMBER,p_entity_name VARCHAR2) is
1474 select fad.ORIG_ATTACH_DOC_ID , fd.datatype_id ,fad.attached_document_id
1475 from fnd_attached_documents fad , fnd_documents fd
1476 where fad.entity_name = p_entity_name
1477 and fad.pk1_value = to_char(p_header_id) || '-' || to_char(p_draft_id)
1478 AND fad.ORIG_ATTACH_DOC_ID IS NOT NULL
1479 AND fd.document_id = fad.document_id
1480 AND fad.category_id NOT IN (SELECT category_id
1481 FROM fnd_document_categories_vl
1482 WHERE application_id = 201 AND name = 'CUSTOM5340'
1483 );
1484
1485 --Fetch all new attachments other than mod specific attachments as merge
1486 -- is not needed for these attachmnets
1487 cursor headers_with_new_attachments_c(p_header_id NUMBER,p_draft_id NUMBER) is
1488 select fad.attached_document_id, fd.datatype_id , fad.ORIG_ATTACH_DOC_ID
1489 from fnd_attached_documents fad , fnd_documents fd
1490 where fad.entity_name = 'PO_HEADERS'
1491 and fad.pk1_value = to_char(p_header_id) || '-' || to_char(p_draft_id)
1492 AND fad.ORIG_ATTACH_DOC_ID IS NULL
1493 AND fd.document_id = fad.document_id
1494 AND fad.category_id NOT IN (SELECT category_id
1495 FROM fnd_document_categories_vl
1496 WHERE application_id = 201 AND name = 'CUSTOM5340'
1497 );
1498
1499 d_api_name CONSTANT VARCHAR2(30) := 'merge_header_attachments';
1500 d_module CONSTANT VARCHAR2(2000) := g_pkg_name || d_api_name || '.';
1501 d_position NUMBER ;
1502 x_progress varchar2(1000);
1503 BEGIN
1504
1505 d_position := 10;
1506 --Fetch copied records
1507
1508 --Fetch copied records
1509 x_progress := 'Merging copied records for Modification with draft_id '||p_draft_id;
1510 IF (PO_LOG.d_stmt) THEN
1511 PO_LOG.stmt(d_module, d_position, x_progress);
1512 END IF;
1513
1514 for l_rec in modified_header_attachments_c(p_header_id,p_draft_id,'PO_HEADERS') LOOP
1515 --delete the old records
1516 fnd_attached_documents3_pkg.delete_row
1517 ( X_attached_document_id => l_rec.ORIG_ATTACH_DOC_ID,
1518 X_datatype_id => l_rec.datatype_id,
1519 delete_document_flag => 'Y'
1520 );
1521
1522 --copy the new records
1523 fnd_attached_documents2_pkg.copy_attachments
1524 ( X_from_entity_name => 'PO_HEADERS',
1525 X_from_pk1_value => To_Char(p_header_id)||'-'||To_Char(p_draft_id),
1526 X_to_entity_name => 'PO_HEADERS',
1527 X_to_pk1_value => To_Char(p_header_id),
1528 X_orig_attach_doc_id => l_rec.attached_document_id
1529 );
1530 END LOOP;
1531
1532 d_position := 20;
1533 --Fetch deleted records
1534 x_progress := 'Deleting attachments marked for deletion by draft_id '||p_draft_id;
1535 IF (PO_LOG.d_stmt) THEN
1536 PO_LOG.stmt(d_module, d_position, x_progress);
1537 END IF;
1538
1539
1540 for l_rec in modified_header_attachments_c(p_header_id,p_draft_id,'PO_HEADERS_DEL') LOOP
1541 --delete the old record
1542 fnd_attached_documents3_pkg.delete_row
1543 ( X_attached_document_id => l_rec.ORIG_ATTACH_DOC_ID,
1544 X_datatype_id => l_rec.datatype_id,
1545 delete_document_flag => 'Y'
1546 );
1547 END LOOP;
1548
1549 d_position := 30;
1550 --Fetch the new attachments adde
1551 x_progress := 'Merging new attachments added by draft_id '||p_draft_id;
1552 IF (PO_LOG.d_stmt) THEN
1553 PO_LOG.stmt(d_module, d_position, x_progress);
1554 END IF;
1555
1556 FOR l_rec IN headers_with_new_attachments_c(p_header_id,p_draft_id) LOOP
1557 fnd_attached_documents2_pkg.copy_attachments
1558 ( X_from_entity_name => 'PO_HEADERS',
1559 X_from_pk1_value => To_Char(p_header_id)||'-'||To_Char(p_draft_id),
1560 X_to_entity_name => 'PO_HEADERS',
1561 X_to_pk1_value => To_Char(p_header_id),
1562 X_orig_attach_doc_id => l_rec.attached_document_id
1563 );
1564 END LOOP;
1565
1566 Exception
1567 When Others Then
1568 d_position := 40;
1569 x_progress := 'PO_DRAFT_MERGE_PKG.merge_header_attachments: Exception';
1570 IF (PO_LOG.d_stmt) THEN
1571 PO_LOG.stmt(d_module, d_position, x_progress);
1572 END IF;
1573 raise;
1574
1575 END MERGE_HEADER_ATTACHMENTS;
1576
1577 -------------------------------------------------------------------------------
1578 --Bug 13938456 : Concurrent Mod Attachments
1579 --Name: MERGE_LINE_ATTACHMENTS
1580 --Procedure to merge line level attachments
1581 --End of Comments
1582 -------------------------------------------------------------------------------
1583
1584 procedure MERGE_LINE_ATTACHMENTS(p_draft_id NUMBER) AS
1585
1586 cursor modified_line_attachments_c(p_draft_id NUMBER,p_entity_name VARCHAR2) is
1587 select pld.po_line_id as po_line_id, fad.attached_document_id , fad.ORIG_ATTACH_DOC_ID ,fd.datatype_id
1588 from fnd_attached_documents fad, po_lines_draft_all pld , fnd_documents fd
1589 where pld.draft_id = p_draft_id
1590 and fad.entity_name = p_entity_name
1591 and fad.pk1_value = to_char(pld.po_line_id) || '-' || to_char(p_draft_id)
1592 AND ORIG_ATTACH_DOC_ID IS NOT NULL
1593 AND fd.document_id = fad.document_id
1594 AND fad.category_id NOT IN (SELECT category_id
1595 FROM fnd_document_categories_vl
1596 WHERE application_id = 201 AND name = 'CUSTOM5340'
1597 );
1598
1599 --Fetch all new attachments other than mod specific attachments as merge
1600 -- is not needed for these attachmnets
1601 cursor new_line_attachments_c(p_draft_id NUMBER) is
1602 select pld.po_line_id as po_line_id, fad.attached_document_id , fad.ORIG_ATTACH_DOC_ID ,fd.datatype_id
1603 from fnd_attached_documents fad, po_lines_draft_all pld , fnd_documents fd
1604 where pld.draft_id = p_draft_id
1605 and fad.entity_name = 'PO_LINES'
1606 and fad.pk1_value = to_char(pld.po_line_id) || '-' || to_char(p_draft_id)
1607 AND ORIG_ATTACH_DOC_ID IS NULL
1608 AND fd.document_id = fad.document_id
1609 AND fad.category_id NOT IN (SELECT category_id
1610 FROM fnd_document_categories_vl
1611 WHERE application_id = 201 AND name = 'CUSTOM5340'
1612 );
1613
1614 d_api_name CONSTANT VARCHAR2(30) := 'MERGE_LINES_ATTACHMENT';
1615 d_module CONSTANT VARCHAR2(2000) := g_pkg_name || d_api_name || '.';
1616 d_position NUMBER ;
1617 x_progress varchar2(1000);
1618
1619 BEGIN
1620
1621 d_position := 10;
1622
1623 --Fetch copied records
1624 x_progress := 'Merging copied records for Modification with draft_id '||p_draft_id;
1625 IF (PO_LOG.d_stmt) THEN
1626 PO_LOG.stmt(d_module, d_position, x_progress);
1627 END IF;
1628
1629 for l_rec in modified_line_attachments_c(p_draft_id,'PO_LINES') LOOP
1630 --delete the old record
1631 fnd_attached_documents3_pkg.delete_row
1632 ( X_attached_document_id => l_rec.ORIG_ATTACH_DOC_ID,
1633 X_datatype_id => l_rec.datatype_id,
1634 delete_document_flag => 'Y'
1635 );
1636 --copy the new records
1637 fnd_attached_documents2_pkg.copy_attachments
1638 ( X_from_entity_name => 'PO_LINES',
1639 X_from_pk1_value => To_Char(l_rec.po_line_id)||'-'||To_Char(p_draft_id),
1640 X_to_entity_name => 'PO_LINES',
1641 X_to_pk1_value => To_Char(l_rec.po_line_id),
1642 X_orig_attach_doc_id => l_rec.attached_document_id
1643 );
1644 END LOOP;
1645
1646 d_position := 20;
1647 --Fetch deleted records
1648 x_progress := 'Deleting attachments marked for deletion by draft_id '||p_draft_id;
1649 IF (PO_LOG.d_stmt) THEN
1650 PO_LOG.stmt(d_module, d_position, x_progress);
1651 END IF;
1652
1653 for l_rec in modified_line_attachments_c(p_draft_id,'PO_LINES_DEL') LOOP
1654 --delete the old record
1655 fnd_attached_documents3_pkg.delete_row
1656 ( X_attached_document_id => l_rec.ORIG_ATTACH_DOC_ID,
1657 X_datatype_id => l_rec.datatype_id,
1658 delete_document_flag => 'Y'
1659 );
1660 END LOOP;
1661
1662 d_position := 30;
1663 --Fetch the new attachments adde
1664 x_progress := 'Merging new attachments added by draft_id '||p_draft_id;
1665 IF (PO_LOG.d_stmt) THEN
1666 PO_LOG.stmt(d_module, d_position, x_progress);
1667 END IF;
1668
1669 FOR l_rec IN new_line_attachments_c(p_draft_id) LOOP
1670 --copy the new attachments
1671 fnd_attached_documents2_pkg.copy_attachments
1672 ( X_from_entity_name => 'PO_LINES',
1673 X_from_pk1_value => To_Char(l_rec.po_line_id)||'-'||To_Char(p_draft_id),
1674 X_to_entity_name => 'PO_LINES',
1675 X_to_pk1_value => To_Char(l_rec.po_line_id),
1676 X_orig_attach_doc_id => l_rec.attached_document_id
1677 );
1678 END LOOP;
1679
1680 Exception
1681 When Others Then
1682 d_position :=40;
1683 x_progress := 'PO_DRAFT_MERGE_PKG.merge_line_attachments: Exception';
1684 IF (PO_LOG.d_stmt) THEN
1685 PO_LOG.stmt(d_module, d_position, x_progress);
1686 END IF;
1687 raise;
1688
1689
1690 END MERGE_LINE_ATTACHMENTS;
1691
1692 -------------------------------------------------------------------------------
1693 --Bug 13938456 : Concurrent Mod Attachments
1694 --Name: MERGE_LINE_LOCATION_ATTACHMENT
1695 --Procedure to merge line location level attachments
1696 --End of Comments
1697 -------------------------------------------------------------------------------
1698
1699 procedure MERGE_LINE_LOCATION_ATTACHMENT(p_draft_id NUMBER) AS
1700
1701 cursor modified_line_loc_attachment_c(p_draft_id NUMBER,p_entity_name VARCHAR2) is
1702 select plld.line_location_id as line_location_id, fad.attached_document_id , fad.ORIG_ATTACH_DOC_ID ,fd.datatype_id
1703 from fnd_attached_documents fad, po_line_locations_draft_all plld , fnd_documents fd
1704 where plld.draft_id = p_draft_id
1705 and fad.entity_name = p_entity_name
1706 and fad.pk1_value = to_char(plld.line_location_id) || '-' || to_char(p_draft_id)
1707 AND ORIG_ATTACH_DOC_ID IS NOT NULL
1708 AND fd.document_id = fad.document_id
1709 AND fad.category_id NOT IN (SELECT category_id
1710 FROM fnd_document_categories_vl
1711 WHERE application_id = 201 AND name = 'CUSTOM5340'
1712 );
1713
1714 --Fetch all new attachments other than mod specific attachments as merge
1715 -- is not needed for these attachmnets
1716 cursor new_line_loc_attachment_c(p_draft_id NUMBER) is
1717 select plld.line_location_id as line_location_id, fad.attached_document_id , fad.ORIG_ATTACH_DOC_ID ,fd.datatype_id
1718 from fnd_attached_documents fad, po_line_locations_draft_all plld , fnd_documents fd
1719 where plld.draft_id = p_draft_id
1720 and fad.entity_name = 'PO_SHIPMENTS'
1721 and fad.pk1_value = to_char(plld.line_location_id) || '-' || to_char(p_draft_id)
1722 AND ORIG_ATTACH_DOC_ID IS NULL
1723 AND fd.document_id = fad.document_id
1724 AND fad.category_id NOT IN (SELECT category_id
1725 FROM fnd_document_categories_vl
1726 WHERE application_id = 201 AND name = 'CUSTOM5340'
1727 );
1728
1729 d_api_name CONSTANT VARCHAR2(30) := 'MERGE_LINE_LOCATION_ATTACHMENT';
1730 d_module CONSTANT VARCHAR2(2000) := g_pkg_name || d_api_name || '.';
1731 d_position NUMBER ;
1732 x_progress varchar2(1000);
1733
1734 BEGIN
1735 d_position := 10;
1736
1737 --Fetch copied records
1738 x_progress := 'Merging copied records for Modification with draft_id '||p_draft_id;
1739 IF (PO_LOG.d_stmt) THEN
1740 PO_LOG.stmt(d_module, d_position, x_progress);
1741 END IF;
1742
1743 for l_rec in modified_line_loc_attachment_c(p_draft_id,'PO_SHIPMENTS') LOOP
1744 --delete the old records
1745 fnd_attached_documents3_pkg.delete_row
1746 ( X_attached_document_id => l_rec.ORIG_ATTACH_DOC_ID,
1747 X_datatype_id => l_rec.datatype_id,
1748 delete_document_flag => 'Y'
1749 );
1750 --Copy the new records
1751 fnd_attached_documents2_pkg.copy_attachments
1752 ( X_from_entity_name => 'PO_SHIPMENTS',
1753 X_from_pk1_value => To_Char(l_rec.line_location_id)||'-'||To_Char(p_draft_id),
1754 X_to_entity_name => 'PO_SHIPMENTS',
1755 X_to_pk1_value => To_Char(l_rec.line_location_id),
1756 X_orig_attach_doc_id => l_rec.attached_document_id
1757 );
1758 END LOOP;
1759
1760 d_position := 20;
1761 --Fetch deleted records
1762 x_progress := 'Deleting attachments marked for deletion by draft_id '||p_draft_id;
1763 IF (PO_LOG.d_stmt) THEN
1764 PO_LOG.stmt(d_module, d_position, x_progress);
1765 END IF;
1766 for l_rec in modified_line_loc_attachment_c(p_draft_id,'PO_SHIPMENTS_DEL') LOOP
1767 --delete the records
1768 fnd_attached_documents3_pkg.delete_row
1769 ( X_attached_document_id => l_rec.ORIG_ATTACH_DOC_ID,
1770 X_datatype_id => l_rec.datatype_id,
1771 delete_document_flag => 'Y'
1772 );
1773 END LOOP;
1774
1775 d_position := 30;
1776 --Fetch the new attachments adde
1777 x_progress := 'Merging new attachments added by draft_id '||p_draft_id;
1778 IF (PO_LOG.d_stmt) THEN
1779 PO_LOG.stmt(d_module, d_position, x_progress);
1780 END IF;
1781 FOR l_rec IN new_line_loc_attachment_c(p_draft_id) LOOP
1782 --copy the new attachments
1783 fnd_attached_documents2_pkg.copy_attachments
1784 ( X_from_entity_name => 'PO_SHIPMENTS',
1785 X_from_pk1_value => To_Char(l_rec.line_location_id)||'-'||To_Char(p_draft_id),
1786 X_to_entity_name => 'PO_SHIPMENTS',
1787 X_to_pk1_value => To_Char(l_rec.line_location_id),
1788 X_orig_attach_doc_id => l_rec.attached_document_id
1789 );
1790 END LOOP;
1791
1792 Exception
1793 When Others Then
1794 d_position := 40;
1795 x_progress := 'PO_DRAFT_MERGE_PKG.merge_line_location_attachment: Exception';
1796 IF (PO_LOG.d_stmt) THEN
1797 PO_LOG.stmt(d_module, d_position, x_progress);
1798 END IF;
1799 raise;
1800
1801 END MERGE_LINE_LOCATION_ATTACHMENT;
1802
1803 PROCEDURE PAR_APPROVE_AUTONOMOUS (po_po_header_id IN NUMBER,
1804 p_draft_id IN NUMBER,
1805 p_employee_id IN NUMBER,
1806 p_document_type IN VARCHAR2)
1807 IS
1808 pragma AUTONOMOUS_TRANSACTION;
1809 l_preparer_id NUMBER;
1810 l_sequence_num NUMBER;
1811 l_object_rev_num NUMBER;
1812 l_approval_path_id NUMBER;
1813 BEGIN
1814 --Set authorization status to approved in po_drafts
1815 UPDATE PO_DRAFTS
1816 SET status = 'COMPLETED'
1817 WHERE draft_id = p_draft_id;
1818 -- Set draft_line_status as well.
1819 UPDATE PO_LINES_DRAFT_ALL
1820 SET draft_line_status = 'COMPLETED'
1821 WHERE draft_id = p_draft_id
1822 AND po_header_id = po_po_header_id
1823 AND draft_line_status is NULL;
1824
1825 -- Add new record ina ction history, in case tehre were no approvers and
1826 -- preparer was the only approver.
1827 SELECT fu.employee_id
1828 INTO l_preparer_id
1829 FROM fnd_user fu, PO_HEADERS_MERGE_V ph
1830 WHERE fu.user_id = ph.OWNER_USER_ID
1831 AND ph.po_header_id = po_po_header_id
1832 AND ph.draft_id = p_draft_id ;
1833
1834
1835 IF l_preparer_id = p_employee_id THEN
1836
1837 -- get data from last entry in po_action_history
1838 SELECT sequence_num,
1839 object_revision_num,
1840 approval_path_id
1841 INTO l_sequence_num,
1842 l_object_rev_num,
1843 l_approval_path_id
1844 FROM po_action_history
1845 WHERE object_id = p_draft_id
1846 AND object_type_code = p_document_type
1847 AND sequence_num = (SELECT max(sequence_num)
1848 FROM po_action_history
1849 WHERE object_id = p_draft_id
1850 AND Object_Type_Code = p_document_type
1851 AND object_sub_type_code = 'POST_AWARD_REQUEST')
1852 AND object_sub_type_code = 'POST_AWARD_REQUEST';
1853
1854 INSERT into PO_ACTION_HISTORY
1855 (object_id,
1856 object_type_code,
1857 object_sub_type_code,
1858 sequence_num,
1859 last_update_date,
1860 last_updated_by,
1861 creation_date,
1862 created_by,
1863 action_code,
1864 action_date,
1865 employee_id,
1866 note,
1867 object_revision_num,
1868 last_update_login,
1869 request_id,
1870 program_application_id,
1871 program_id,
1872 program_update_date,
1873 approval_path_id,
1874 offline_code)
1875 VALUES
1876 (p_draft_id,
1877 'PO', -- doc_type
1878 'POST_AWARD_REQUEST', -- doc_subtype
1879 l_sequence_num + 1,
1880 sysdate,
1881 fnd_global.user_id,
1882 sysdate,
1883 fnd_global.user_id,
1884 'APPROVE', -- action_code
1885 sysdate, --action_date
1886 p_employee_id,
1887 NULL,
1888 l_object_rev_num,
1889 fnd_global.login_id,
1890 0,
1891 0,
1892 0,
1893 '',
1894 l_approval_path_id,
1895 '' );
1896 END IF;
1897
1898 COMMIT;
1899 END PAR_APPROVE_AUTONOMOUS;
1900
1901 PROCEDURE PAR_DOC_APPROVE( itemtype IN VARCHAR2,
1902 itemkey IN VARCHAR2,
1903 actid IN NUMBER,
1904 funcmode IN VARCHAR2,
1905 resultout OUT NOCOPY VARCHAR2)
1906 IS
1907
1908 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
1909 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
1910 l_draft_id NUMBER;
1911 l_document_id NUMBER;
1912 l_employee_id NUMBER;
1913 l_progress VARCHAR2(1000);
1914 BEGIN
1915 l_progress := 'PO_DRAFT_MERGE_PKG.PAR_DOC_APPROVE: Start: 01';
1916
1917 l_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,itemkey => itemkey,aname => 'DRAFT_ID');
1918 l_document_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,itemkey => itemkey,aname => 'DOCUMENT_ID');
1919 l_document_type := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,itemkey => itemkey,aname => 'DOCUMENT_TYPE');
1920 l_document_subtype := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,itemkey => itemkey,aname => 'DOCUMENT_SUBTYPE');
1921 l_employee_id := po_wf_util_pkg.Getitemattrnumber(itemtype => itemtype,itemkey => itemkey,aname => 'APPROVER_EMPID');
1922
1923 IF (g_po_wf_debug = 'Y') THEN
1924 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,l_progress);
1925 END IF;
1926
1927 PAR_APPROVE_AUTONOMOUS (po_po_header_id => l_document_id,
1928 p_draft_id => l_draft_id,
1929 p_employee_id => l_employee_id,
1930 p_document_type => l_document_type);
1931
1932 l_progress := 'PO_DRAFT_MERGE_PKG.PAR_DOC_APPROVE: End: 02';
1933
1934 IF (g_po_wf_debug = 'Y') THEN
1935 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,l_progress);
1936 END IF;
1937
1938 END PAR_DOC_APPROVE;
1939
1940 ------------------------------------------------------------------------------
1941 --Start of Comments
1942 -- PAR Project
1943 --Function : update_par_line_status
1944 --Parameters:
1945 --itemtype
1946 --itemkey
1947 --p_document_id
1948 --p_draft_id
1949 --End of Comments
1950 ------------------------------------------------------------------------------
1951 PROCEDURE update_par_line_status(itemtype IN VARCHAR2,
1952 itemkey IN VARCHAR2,
1953 p_document_id IN NUMBER,
1954 p_draft_id IN NUMBER)
1955 IS
1956 l_is_par_enabled VARCHAR2(1) := 'N';
1957 BEGIN
1958
1959 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,'update_par_line_status start');
1960
1961 BEGIN
1962 SELECT par_enabled_flag
1963 INTO l_is_par_enabled
1964 FROM po_headers_draft_all pha,
1965 po_doc_style_headers pds
1966 WHERE pha.style_id = pds.style_id
1967 AND pha.po_header_id = p_document_id
1968 AND pha.draft_id = p_draft_id;
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 l_is_par_enabled := 'N';
1972 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,'Exception in update_par_line_status - ' || sqlerrm);
1973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1974 END;
1975
1976 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,'Is PAR Enabled : ' || l_is_par_enabled );
1977
1978 IF l_is_par_enabled = 'Y' THEN
1979
1980 UPDATE po_lines_draft_all
1981 SET draft_line_status = 'PO_CREATED'
1982 WHERE po_header_id = p_document_id
1983 AND mod_draft_id = p_draft_id;
1984
1985 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,'No of PAR Lines updated : ' || SQL%ROWCOUNT);
1986
1987 END IF;
1988
1989 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,'update_par_line_status successful');
1990
1991 EXCEPTION
1992 WHEN OTHERS THEN
1993 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,'Exception in update_par_line_status - ' || sqlerrm);
1994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1995 END update_par_line_status;
1996
1997 /**
1998 * <Event Based Delivery>
1999 * Procedure: update_delivery_event_attr
2000 * Requires:
2001 * IN PARAMETERS:
2002 * p_document_id: po_header_id
2003 * p_draft_id: draft_id
2004 *
2005 * Effects: Updates need by date, promised date and POP dates
2006 * as per the delivery event.
2007 * Returns: None
2008 *
2009 */
2010 PROCEDURE update_delivery_event_attr(
2011 p_document_id IN NUMBER
2012 , p_draft_id IN NUMBER
2013 )
2014 IS
2015 d_progress NUMBER;
2016 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_delivery_event_attr';
2017 l_effective_date po_headers_all.clm_effective_date%TYPE;
2018 BEGIN
2019
2020 d_progress := 0;
2021 IF (PO_LOG.d_proc) THEN
2022 PO_LOG.proc_begin(d_module);
2023 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
2024 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
2025 END IF;
2026
2027 -- Take the Clm Effective date / Mod Effective Date into l_effective_date
2028 -- For awards need_by_date/promise_date/pop_dates will be clm_effective_date + periods
2029 -- For New lines in Mods it will be MOD_EFFECTIVE_DATE + periods
2030 IF(p_draft_id = -1) THEN
2031
2032 SELECT clm_effective_date
2033 INTO l_effective_date
2034 FROM po_headers_all
2035 WHERE po_header_id = p_document_id;
2036
2037 d_progress := 1;
2038
2039 ELSE
2040
2041 SELECT pd.MOD_EFFECTIVE_DATE
2042 INTO l_effective_date
2043 FROM po_headers_all pha, po_drafts pd
2044 WHERE pha.po_header_id = p_document_id
2045 AND pha.po_header_id = pd.document_id
2046 AND pd.draft_id = p_draft_id;
2047
2048 d_progress := 2;
2049
2050 END IF;
2051
2052
2053 IF (PO_LOG.d_stmt) THEN
2054 PO_LOG.stmt(d_module, d_progress, 'effective date : ', l_effective_date);
2055 END IF;
2056
2057 -- Update need by date
2058 -- Only when After Date of Contract or Notice of Award event is used
2059 -- And delivery_period is not null.
2060 -- need_by_date = effective_date + delivery_period
2061 -- If POP Duration is there then we update POP Start date and not need by date.
2062 -- In case of Mods update only the New Lines
2063
2064 UPDATE po_line_locations_draft_all poll
2065 SET poll.need_by_date = po_core_s3.Add_period(l_effective_date,
2066 poll.clm_delivery_period,
2067 poll.clm_delivery_period_uom)
2068 WHERE poll.po_header_id = p_document_id
2069 AND poll.po_release_id IS NULL
2070 AND EXISTS (SELECT 'ADC/ANA Delivery Event used'
2071 FROM po_lines_all pol
2072 WHERE pol.po_header_id = poll.po_header_id
2073 AND pol.po_line_id = poll.po_line_id
2074 AND pol.clm_delivery_event_code IN ( 'ADC', 'ANA' ))
2075 AND poll.need_by_date IS NULL
2076 AND poll.clm_period_perf_start_date IS NULL
2077 AND poll.clm_delivery_period IS NOT NULL
2078 AND poll.clm_delivery_period_uom IS NOT NULL
2079 AND poll.clm_pop_duration IS NULL
2080 AND poll.draft_id = p_draft_id
2081 AND poll.change_status = 'NEW';
2082
2083 d_progress := 3;
2084
2085 IF (PO_LOG.d_stmt) THEN
2086 PO_LOG.stmt(d_module, d_progress, 'Updated Need By Date in po_line_locations_draft_all - No of rows updated :' , SQL%ROWCOUNT);
2087 END IF;
2088
2089 UPDATE po_line_locations_all poll
2090 SET poll.need_by_date = po_core_s3.Add_period(l_effective_date,
2091 poll.clm_delivery_period,
2092 poll.clm_delivery_period_uom)
2093 WHERE poll.po_header_id = p_document_id
2094 AND poll.po_release_id IS NULL
2095 AND EXISTS (SELECT 'ADC/ANA Delivery Event used'
2096 FROM po_lines_all pol
2097 WHERE pol.po_header_id = poll.po_header_id
2098 AND pol.po_line_id = poll.po_line_id
2099 AND pol.clm_delivery_event_code IN ( 'ADC', 'ANA' ))
2100 AND poll.need_by_date IS NULL
2101 AND poll.clm_period_perf_start_date IS NULL
2102 AND poll.clm_delivery_period IS NOT NULL
2103 AND poll.clm_delivery_period_uom IS NOT NULL
2104 AND poll.clm_pop_duration IS NULL
2105 AND ( p_draft_id = -1
2106 OR EXISTS (SELECT 'Y'
2107 FROM po_line_locations_draft_all polld
2108 WHERE polld.po_header_id = poll.po_header_id
2109 AND polld.line_location_id =
2110 poll.line_location_id
2111 AND polld.draft_id = p_draft_id
2112 AND polld.change_status = 'NEW') );
2113
2114 d_progress := 4;
2115
2116 IF (PO_LOG.d_stmt) THEN
2117 PO_LOG.stmt(d_module, d_progress, 'Updated Need By Date in po_line_locations_all- No of rows updated :' , SQL%ROWCOUNT);
2118 END IF;
2119
2120 -- Update Promised Date
2121 -- Only when After Date of Contract or Notice of Award event is used
2122 -- And promised_period is not null
2123 -- promised_date = effective_date + promise_period
2124 -- In case of Mods update only the New Lines
2125 -- Also last_accep_date should be updated to promised_date + DAYS_LATE_RECEIPT_ALLOWED
2126
2127 UPDATE po_line_locations_draft_all poll
2128 SET poll.promised_date = po_core_s3.Add_period(l_effective_date,
2129 poll.clm_promise_period,
2130 poll.clm_promise_period_uom),
2131 poll.last_accept_date = po_core_s3.Add_period(l_effective_date,
2132 poll.clm_promise_period,
2133 poll.clm_promise_period_uom)
2134 + Nvl(DAYS_LATE_RECEIPT_ALLOWED,0)
2135 WHERE poll.po_header_id = p_document_id
2136 AND poll.po_release_id IS NULL
2137 AND EXISTS (SELECT 'ADC/ANA Delivery Event used'
2138 FROM po_lines_all pol
2139 WHERE pol.po_header_id = poll.po_header_id
2140 AND pol.po_line_id = poll.po_line_id
2141 AND pol.clm_delivery_event_code IN ( 'ADC', 'ANA' ))
2142 AND poll.promised_date IS NULL
2143 AND poll.clm_promise_period IS NOT NULL
2144 AND poll.clm_promise_period_uom IS NOT NULL
2145 AND poll.draft_id = p_draft_id
2146 AND poll.change_status = 'NEW';
2147
2148 d_progress := 5;
2149 IF (PO_LOG.d_stmt) THEN
2150 PO_LOG.stmt(d_module, d_progress, 'Updated Promised in po_line_locations_draft_all- No of rows updated :' , SQL%ROWCOUNT);
2151 END IF;
2152
2153 UPDATE po_line_locations_all poll
2154 SET poll.promised_date = po_core_s3.Add_period(l_effective_date,
2155 poll.clm_promise_period,
2156 poll.clm_promise_period_uom),
2157 poll.last_accept_date = po_core_s3.Add_period(l_effective_date,
2158 poll.clm_promise_period,
2159 poll.clm_promise_period_uom)
2160 + Nvl(DAYS_LATE_RECEIPT_ALLOWED,0)
2161 WHERE poll.po_header_id = p_document_id
2162 AND poll.po_release_id IS NULL
2163 AND EXISTS (SELECT 'ADC/ANA Delivery Event used'
2164 FROM po_lines_all pol
2165 WHERE pol.po_header_id = poll.po_header_id
2166 AND pol.po_line_id = poll.po_line_id
2167 AND pol.clm_delivery_event_code IN ( 'ADC', 'ANA' ))
2168 AND poll.promised_date IS NULL
2169 AND poll.clm_promise_period IS NOT NULL
2170 AND poll.clm_promise_period_uom IS NOT NULL
2171 AND ( p_draft_id = -1
2172 OR EXISTS (SELECT 'Y'
2173 FROM po_line_locations_draft_all polld
2174 WHERE polld.po_header_id = poll.po_header_id
2175 AND polld.line_location_id =
2176 poll.line_location_id
2177 AND polld.draft_id = p_draft_id
2178 AND polld.change_status = 'NEW') );
2179
2180 d_progress := 6;
2181 IF (PO_LOG.d_stmt) THEN
2182 PO_LOG.stmt(d_module, d_progress, 'Updated Promised in po_line_locations_all- No of rows updated :' , SQL%ROWCOUNT);
2183 END IF;
2184
2185 -- Update POP Start Date
2186 -- Only when After Date of Contract or Notice of Award event is used
2187 -- And delivery_period, pop_duration are not null.
2188 -- pop_start_date = effective_date + delivery_period
2189 -- If POP Duration is there then we update POP Start date and not need by date.
2190 -- In case of Mods update only the New Lines
2191
2192 UPDATE po_line_locations_draft_all poll
2193 SET poll.CLM_PERIOD_PERF_START_DATE = po_core_s3.Add_period(l_effective_date,
2194 poll.clm_delivery_period,
2195 poll.clm_delivery_period_uom)
2196 WHERE poll.po_header_id = p_document_id
2197 AND poll.po_release_id IS NULL
2198 AND EXISTS (SELECT 'ADC/ANA Delivery Event used'
2199 FROM po_lines_all pol
2200 WHERE pol.po_header_id = poll.po_header_id
2201 AND pol.po_line_id = poll.po_line_id
2202 AND pol.clm_delivery_event_code IN ( 'ADC', 'ANA' ))
2203 AND poll.need_by_date IS NULL
2204 AND poll.clm_period_perf_start_date IS NULL
2205 AND poll.clm_delivery_period IS NOT NULL
2206 AND poll.clm_delivery_period_uom IS NOT NULL
2207 AND poll.clm_pop_duration IS NOT NULL
2208 AND poll.clm_pop_duration_uom IS NOT NULL
2209 AND poll.draft_id = p_draft_id
2210 AND poll.change_status = 'NEW';
2211
2212 d_progress := 7;
2213 IF (PO_LOG.d_stmt) THEN
2214 PO_LOG.stmt(d_module, d_progress, 'Updated POP Start Date in po_line_locations_draft_all - No of rows updated :' , SQL%ROWCOUNT);
2215 END IF;
2216
2217 UPDATE po_line_locations_all poll
2218 SET poll.CLM_PERIOD_PERF_START_DATE = po_core_s3.Add_period(l_effective_date,
2219 poll.clm_delivery_period,
2220 poll.clm_delivery_period_uom)
2221 WHERE poll.po_header_id = p_document_id
2222 AND poll.po_release_id IS NULL
2223 AND EXISTS (SELECT 'ADC/ANA Delivery Event used'
2224 FROM po_lines_all pol
2225 WHERE pol.po_header_id = poll.po_header_id
2226 AND pol.po_line_id = poll.po_line_id
2227 AND pol.clm_delivery_event_code IN ( 'ADC', 'ANA' ))
2228 AND poll.need_by_date IS NULL
2229 AND poll.clm_period_perf_start_date IS NULL
2230 AND poll.clm_delivery_period IS NOT NULL
2231 AND poll.clm_delivery_period_uom IS NOT NULL
2232 AND poll.clm_pop_duration IS NOT NULL
2233 AND poll.clm_pop_duration_uom IS NOT NULL
2234 AND ( p_draft_id = -1
2235 OR EXISTS (SELECT 'Y'
2236 FROM po_line_locations_draft_all polld
2237 WHERE polld.po_header_id = poll.po_header_id
2238 AND polld.line_location_id =
2239 poll.line_location_id
2240 AND polld.draft_id = p_draft_id
2241 AND polld.change_status = 'NEW') );
2242
2243 d_progress := 8;
2244 IF (PO_LOG.d_stmt) THEN
2245 PO_LOG.stmt(d_module, d_progress, 'Updated POP Start Date in po_line_locations_all - No of rows updated :' , SQL%ROWCOUNT);
2246 END IF;
2247
2248 -- Update POP End Date
2249 -- Only when After Date of Contract or Notice of Award event is used
2250 -- And delivery_period, pop_duration are not null.
2251 -- POP end date = pop_start_date + pop_duration
2252 -- In case of Mods update only the New Lines
2253
2254 UPDATE po_line_locations_draft_all poll
2255 SET poll.CLM_PERIOD_PERF_END_DATE = po_core_s3.Add_period(poll.clm_period_perf_start_date,
2256 poll.CLM_POP_DURATION,
2257 poll.CLM_POP_DURATION_UOM)
2258 WHERE poll.po_header_id = p_document_id
2259 AND poll.po_release_id IS NULL
2260 AND EXISTS (SELECT 'ADC/ANA Delivery Event used'
2261 FROM po_lines_all pol
2262 WHERE pol.po_header_id = poll.po_header_id
2263 AND pol.po_line_id = poll.po_line_id
2264 AND pol.clm_delivery_event_code IN ( 'ADC', 'ANA' ))
2265 AND poll.need_by_date IS NULL
2266 AND poll.clm_period_perf_start_date IS NOT NULL
2267 AND poll.clm_delivery_period IS NOT NULL
2268 AND poll.clm_delivery_period_uom IS NOT NULL
2269 AND poll.clm_pop_duration IS NOT NULL
2270 AND poll.clm_pop_duration_uom IS NOT NULL
2271 AND poll.draft_id = p_draft_id
2272 AND poll.change_status = 'NEW';
2273
2274 d_progress := 9;
2275 IF (PO_LOG.d_stmt) THEN
2276 PO_LOG.stmt(d_module, d_progress, 'Updated POP End Date in po_line_locations_draft_all - No of rows updated :' , SQL%ROWCOUNT);
2277 END IF;
2278
2279 UPDATE po_line_locations_all poll
2280 SET poll.CLM_PERIOD_PERF_END_DATE = po_core_s3.Add_period(poll.clm_period_perf_start_date,
2281 poll.CLM_POP_DURATION,
2282 poll.CLM_POP_DURATION_UOM)
2283 WHERE poll.po_header_id = p_document_id
2284 AND poll.po_release_id IS NULL
2285 AND EXISTS (SELECT 'ADC/ANA Delivery Event used'
2286 FROM po_lines_all pol
2287 WHERE pol.po_header_id = poll.po_header_id
2288 AND pol.po_line_id = poll.po_line_id
2289 AND pol.clm_delivery_event_code IN ( 'ADC', 'ANA' ))
2290 AND poll.need_by_date IS NULL
2291 AND poll.clm_period_perf_start_date IS NOT NULL
2292 AND poll.clm_delivery_period IS NOT NULL
2293 AND poll.clm_delivery_period_uom IS NOT NULL
2294 AND poll.clm_pop_duration IS NOT NULL
2295 AND poll.clm_pop_duration_uom IS NOT NULL
2296 AND ( p_draft_id = -1
2297 OR EXISTS (SELECT 'Y'
2298 FROM po_line_locations_draft_all polld
2299 WHERE polld.po_header_id = poll.po_header_id
2300 AND polld.line_location_id =
2301 poll.line_location_id
2302 AND polld.draft_id = p_draft_id
2303 AND polld.change_status = 'NEW') );
2304
2305 d_progress := 10;
2306 IF (PO_LOG.d_stmt) THEN
2307 PO_LOG.stmt(d_module, d_progress, 'Updated POP End Date in po_line_locations_all - No of rows updated :' , SQL%ROWCOUNT);
2308 END IF;
2309
2310 -- Sync Archive
2311 FOR poll in (SELECT poll.po_header_id,
2312 poll.po_line_id,
2313 poll.line_location_id,
2314 poll.need_by_date,
2315 poll.promised_date,
2316 poll.last_accept_date,
2317 poll.clm_period_perf_start_date,
2318 poll.clm_period_perf_end_date
2319 FROM po_line_locations_all poll
2320 WHERE poll.po_header_id = p_document_id
2321 AND poll.po_release_id IS NULL
2322 AND EXISTS (SELECT 'ADC/ANA Delivery Event used'
2323 FROM po_lines_all pol
2324 WHERE pol.po_header_id = poll.po_header_id
2325 AND pol.po_line_id = poll.po_line_id
2326 AND pol.clm_delivery_event_code IN ( 'ADC', 'ANA' ))
2327 AND ( p_draft_id = -1
2328 OR EXISTS (SELECT 'Y'
2329 FROM po_line_locations_draft_all polld
2330 WHERE polld.po_header_id = poll.po_header_id
2331 AND polld.line_location_id =
2332 poll.line_location_id
2333 AND polld.draft_id = p_draft_id
2334 AND polld.change_status = 'NEW')))
2335 LOOP
2336
2337 UPDATE po_line_locations_archive_all polla
2338 SET polla.need_by_date = poll.need_by_date,
2339 polla.promised_date = poll.promised_date,
2340 polla.last_accept_date = poll.last_accept_date,
2341 polla.clm_period_perf_start_date = poll.clm_period_perf_start_date,
2342 polla.clm_period_perf_end_date = poll.clm_period_perf_end_date
2343 WHERE polla.po_header_id = poll.po_header_id
2344 AND polla.po_line_id = poll.po_line_id
2345 AND polla.line_location_id = poll.line_location_id
2346 AND polla.latest_external_flag = 'Y';
2347
2348
2349 END LOOP;
2350
2351 d_progress := 11;
2352 IF (PO_LOG.d_stmt) THEN
2353 PO_LOG.stmt(d_module, d_progress, 'Updated POP End Date in po_line_locations_archive_all - No of rows updated :' , SQL%ROWCOUNT);
2354 END IF;
2355
2356
2357 EXCEPTION
2358
2359 WHEN OTHERS THEN
2360 IF (PO_LOG.d_stmt) THEN
2361 PO_LOG.stmt(d_module, d_progress, 'Exception in update_delivery_event_attr ' || sqlerrm);
2362 END IF;
2363
2364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2365
2366 END update_delivery_event_attr;
2367
2368
2369 /**
2370 * <Bug 16491111>
2371 * Procedure: docnum_post_apprvl_autonomous
2372 * Requires:
2373 * IN PARAMETERS:
2374 * itemtype
2375 * itemkey
2376 * p_document_id
2377 * p_draft_id
2378 * p_uda_template_id
2379 * Effects: Calls process_dod_awd_mod_doc_num to update doc number.
2380 *
2381 * Returns: None
2382 *
2383 */
2384 PROCEDURE docnum_post_apprvl_autonomous(
2385 itemtype IN VARCHAR2
2386 , itemkey IN VARCHAR2
2387 , p_document_id IN NUMBER
2388 , p_draft_id IN NUMBER
2389 , p_uda_template_id IN NUMBER
2390 )
2391 IS
2392
2393 pragma AUTONOMOUS_TRANSACTION;
2394
2395 x_progress VARCHAR2(2000);
2396
2397 BEGIN
2398
2399 SAVEPOINT docnum_post_apprvl_auto_SP;
2400
2401 x_progress := 'PO_DRAFT_MERGE_PKG.docnum_post_apprvl_autonomous: Savepoint docnum_post_apprvl_autonomous_SP';
2402 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
2403
2404 po_doc_numbering_pkg.process_dod_awd_mod_doc_num
2405 (
2406 p_po_header_id => p_document_id,
2407 p_po_draft_id => p_draft_id,
2408 p_uda_template_id => p_uda_template_id,
2409 p_ag_type => 'PO_HEADER_EXT_ATTRS'
2410 );
2411
2412 x_progress := 'PO_DRAFT_MERGE_PKG.docnum_post_apprvl_autonomous: Completed process_dod_awd_mod_doc_num';
2413 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
2414
2415 COMMIT;
2416
2417 EXCEPTION
2418
2419 When Others Then
2420
2421 x_progress := 'PO_DRAFT_MERGE_PKG.docnum_post_apprvl_autonomous: Exception ' || SQLERRM;
2422 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
2423
2424 ROLLBACK TO docnum_post_apprvl_auto_SP;
2425
2426 wf_core.context('PO_DRAFT_MERGE_PKG','docnum_post_apprvl_autonomous', x_progress);
2427 raise;
2428
2429 END docnum_post_apprvl_autonomous;
2430
2431 End PO_DRAFT_MERGE_PKG;