[Home] [Help]
PACKAGE BODY: APPS.POS_ACK_PO
Source
1 PACKAGE BODY POS_ACK_PO AS
2 /* $Header: POSISPAB.pls 120.5.12010000.2 2008/08/02 14:55:34 sthoppan ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(50) := 'POS_ACK_PO';
5 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
6 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7
8 /* Bug No: 6670166
9 Added g_default_promise_date global variable to get the POS_DEFAULT_PROMISE_DATE_ACK profile value. */
10
11 g_default_promise_date VARCHAR2(1) := NVL(FND_PROFILE.VALUE('POS_DEFAULT_PROMISE_DATE_ACK'),'N');
12 /*Bug 6772960
13 Modified the signature which takes l_last_update_date as IN parameter
14 and returns x_error as OUT parameter. l_last_update_date is used to
15 check the concurrency i.e., to check whether multiple supplier users
16 are acting on the same PO simutaneously. If the supplier try to modify
17 the PO which has already been modified by other user x_error returns false.
18 */
19 PROCEDURE ACKNOWLEDGE_PO (
20 l_po_header_id IN VARCHAR2,
21 l_po_release_id IN VARCHAR2 default null,
22 l_po_buyer_id IN VARCHAR2,
23 l_po_accept_reject IN VARCHAR2,
24 l_po_acc_type_code IN VARCHAR2,
25 l_po_ack_comments IN VARCHAR2 ,
26 l_user_id IN VARCHAR2,
27 l_last_update_date IN DATE DEFAULT fnd_api.G_NULL_DATE,
28 x_error OUT NOCOPY VARCHAR2)
29 IS
30 l_acceptance_id NUMBER;
31 l_revision_num NUMBER := 0;
32 l_error VARCHAR2(240);
33 l_item_type VARCHAR2(20) := 'POSACKNT';
34 l_seq_val NUMBER;
35 l_item_key VARCHAR2(100);
36 l_accp_type VARCHAR2(240);
37 l_accp_res fnd_new_messages.message_text%type := null;
38 l_doc NUMBER;
39 l_nid NUMBER;
40 l_doc_type VARCHAR2(20);
41 l_po_item_type VARCHAR2(100);
42 l_po_item_key VARCHAR2(100);
43 l_supp_username VARCHAR2(100);
44 l_supplier_displayname VARCHAR2(100);
45 l_action VARCHAR2(20);
46 x_vendor VARCHAR2(240);
47 l_pending_signature_flag PO_HEADERS_ALL.pending_signature_flag%type;
48 x_row_id varchar2(30);
49 x_Acceptance_id number;
50 x_Last_Update_Date date ;
51 x_Last_Updated_By number;
52 l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
53 x_Creation_Date date := TRUNC(SYSDATE);
54 x_Created_By number := fnd_global.user_id;
55 x_Po_Header_Id number;
56 x_Po_Release_Id number;
57 x_Action varchar2(240) := 'NEW';
58 x_Action_Date date := TRUNC(SYSDATE);
59 x_Employee_Id number;
60 x_Revision_Num number;
61 x_Accepted_Flag varchar2(1) := '';
62 x_Acceptance_Lookup_Code varchar2(25);
63 x_document_id number;
64 x_document_type_code varchar2(30);
65 l_signature_flag VARCHAR2(10);
66 l_accepting_party VARCHAR2(1);
67 l_role VARCHAR2(150);
68 l_doc_subtype VARCHAR2(20);
69 l_api_name VARCHAR2(100) := 'ACKNOWLEDGE_PO';
70 l_progress VARCHAR2(100);
71 l_message_name varchar2(30);
72 l_wf_user_id number;
73 l_wf_resp_id number;
74 l_wf_appl_id number;
75 l_wf_org_id number;
76
77 --Bug 6772960 - Start
78 /*Added the l_last_upd_date variables to check the concurrency.
79 Added the cursors to get the latest last_update_date which is there in the database.
80 */
81 l_last_upd_date po_headers_all.last_update_date%type;
82
83 CURSOR PO_CSR(p_po_header_id in number) IS
84 SELECT last_update_date
85 FROM PO_HEADERS_ALL
86 WHERE PO_HEADER_ID = p_po_header_id
87 FOR UPDATE of last_update_date NOWAIT;
88
89 poRec PO_CSR%ROWTYPE;
90
91 CURSOR REL_CSR(p_po_release_id in number) IS
92 SELECT last_update_date
93 FROM PO_RELEASES_ALL
94 WHERE PO_RELEASE_ID = p_po_release_id
95 FOR UPDATE of last_update_date NOWAIT;
96
97 relRec REL_CSR%ROWTYPE;
98
99 CURSOR NID_CSR(p_nid in number) IS
100 select notification_id
101 from wf_notifications
102 where notification_id = p_nid
103 FOR UPDATE of notification_id NOWAIT;
104
105 nidRec NID_CSR%ROWTYPE;
106 --Bug 6772960 - End
107
108 BEGIN
109
110 -- Find if a notification has been sent thru core PO
111 -- then complete the activity , if not then do the regular
112
113 --dbms_output.put_line('Starting Offf');
114 l_progress := '0';
115 if l_po_release_id is null then
116
117 --dbms_output.put_line('For Releases');
118 begin
119 select a.notification_id,poh.wf_item_type,poh.wf_item_key, a.message_name
120 INTO l_nid,l_po_item_type,l_po_item_key, l_message_name
121 from wf_notifications a,po_headers_all poh,
122 wf_item_activity_statuses wa
123 where poh.po_header_id=l_po_header_id
124 and poh.wf_item_key=wa.item_key
125 and poh.wf_item_type=wa.item_type
126 and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
127 and a.notification_id=wa.notification_id and a.status = 'OPEN'
128 and wa.activity_status = 'NOTIFIED';
129 exception
130 when no_data_found then l_nid := null;
131 end;
132
133 else
134 begin
135 --dbms_output.put_line('For Std PO');
136 select a.notification_id,por.wf_item_type,por.wf_item_key
137 INTO l_nid,l_po_item_type,l_po_item_key
138 from wf_notifications a,po_releases_all por,
139 wf_item_activity_statuses wa
140 where por.po_release_id=l_po_release_id
141 and por.wf_item_key=wa.item_key
142 and por.wf_item_type=wa.item_type
143 and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
144 and a.notification_id=wa.notification_id and a.status = 'OPEN'
145 and wa.activity_status = 'NOTIFIED';
146 exception
147 when no_data_found then l_nid := null;
148 end;
149
150 end if;
151 l_progress := '1';
152 --Commented out since l_po_acc_type_code is passed in as null from FPI.
153 /*
154 select description into l_accp_type from POS_ACK_ACC_TYPE_LOV_V
155 where LOOKUP_CODE = l_po_acc_type_code; */
156
157 -- Get the supplier user name
158 WF_DIRECTORY.GetUserName( 'FND_USR',
159 l_user_id,
160 l_supp_username,
161 l_supplier_displayname);
162 -- Get the Vendor Name
163 if l_po_release_id is null then
164 select pov.vendor_name
165 into x_vendor
166 from po_vendors pov,po_headers_all poh
167 where pov.vendor_id = poh.vendor_id
168 and poh.po_header_id=l_po_header_id;
169 else
170 select pov.vendor_name
171 into x_vendor
172 from po_releases_all por,po_headers_all poh,po_vendors pov
173 where por.po_release_id = l_po_release_id
174 and por.po_header_id = poh.po_header_id
175 and poh.vendor_id = pov.vendor_id;
176 end if;
177 l_progress := '2';
178 if l_po_accept_reject = 'Y' then
179 select fnd_message.get_string('POS','POS_PO_ACCEPTED')
180 into l_accp_res from dual;
181 else
182 select fnd_message.get_string('POS','POS_PO_REJECTED')
183 into l_accp_res from dual;
184 end if;
185
186 if l_nid is not null then
187 l_progress := '3';
188
189 /*Bug 6772960 - Start
190 Locking the Notification id to allow only one user to update when more than
191 one supplier user tries to acknowledge the PO.*/
192 IF (l_last_update_date <> fnd_api.G_NULL_DATE) THEN
193 BEGIN
194 OPEN NID_CSR(l_nid);
195 FETCH NID_CSR INTO nidRec;
196 if (NID_CSR%NOTFOUND) then
197 CLOSE NID_CSR;
198 end if;
199 CLOSE NID_CSR;
200 EXCEPTION
201 WHEN OTHERS THEN
202 if (sqlcode = '-54') THEN
203 x_error := 'true';
204 return;
205 end if;
206 END;
207 END IF;
208 --Bug 6772960 - End
209
210 --dbms_output.put_line('Notification found from core PO');
211 if l_po_accept_reject = 'Y' then
212 l_action := 'ACCEPT';
213 else
214 l_action := 'REJECT';
215
216 end if;
217
218
219 -- Set the attributes for the acceptance message to buyer
220 PO_WF_UTIL_PKG.SetItemAttrText
221 (
222 ItemType => l_po_item_type,
223 ItemKey => l_po_item_key,
224 aname => 'ACCEPTANCE_TYPE',
225 avalue => l_accp_type
226 );
227
228 PO_WF_UTIL_PKG.SetItemAttrText
229 (
230 ItemType => l_po_item_type,
231 ItemKey => l_po_item_key,
232 aname => 'ACCEPTANCE_LOOKUP_CODE',
233 avalue => l_po_acc_type_code
234 );
235
236 PO_WF_UTIL_PKG.SetItemAttrText
237 (
238 ItemType => l_po_item_type,
239 ItemKey => l_po_item_key,
240 aname => 'ACCEPTANCE_RESULT',
241 avalue => l_accp_res
242 );
243
244
245 PO_WF_UTIL_PKG.SetItemAttrText
246 (
247 ItemType => l_po_item_type,
248 ItemKey => l_po_item_key,
249 aname => 'ACCEPTANCE_COMMENTS',
250 avalue => l_po_ack_comments
251 );
252
253 PO_WF_UTIL_PKG.SetItemAttrText
254 (
255 ItemType => l_po_item_type,
256 ItemKey => l_po_item_key,
257 aname => 'SUPPLIER',
258 avalue => x_vendor
259 );
260
261 PO_WF_UTIL_PKG.SetItemAttrText
262 (
263 ItemType => l_po_item_type,
264 ItemKey => l_po_item_key,
265 aname => 'SUPPLIER_USER_NAME',
266 avalue => l_supp_username
267 );
268
269 l_progress := '3-0';
270 /*
271 We will not reinitialize apps here. This may make the next activity after the
272 notification be deferred briefly. But, this helps in keeping the responder name
273 same as the l_supplier_name. See bug 3900146 for details.
274
275 */
276 l_progress := '3-1';
277 WF_NOTIFICATION.SetAttrText(nid =>l_nid ,
278 aname => 'RESULT',
279 avalue => l_action);
280
281 l_progress := '3-2';
282 WF_NOTIFICATION.Respond(nid =>l_nid ,
283 respond_comment =>null,
284 responder =>l_supp_username
285 --action_source in varchar2 default null
286 );
287
288 else
289
290 --Bug 6772960 - Start
291 /*
292 l_last_update_date contains the last update date which is currently seen by supplier user.
293 l_last_upd_date contains the last update date which is currently there is database.
294 If there is any mismatch in the above dates that means the PO data whatever supplier user
295 seeing currently is not the latest.
296 */
297 -- Lock the PO Header Row for update of Last Update Date
298 IF (l_last_update_date <> fnd_api.G_NULL_DATE) THEN
299 if (l_po_release_id is not null ) then
300 BEGIN
301 OPEN REL_CSR(l_po_release_id);
302 FETCH REL_CSR INTO relRec;
303 l_last_upd_date := relRec.last_update_date;
304 if (REL_CSR%NOTFOUND) then
305 CLOSE REL_CSR;
306 end if;
307 CLOSE REL_CSR;
308 EXCEPTION
309 WHEN OTHERS THEN
310 if (sqlcode = '-54') THEN
311 x_error := 'true';
312 return;
313 end if;
314 END;
315 else
316 BEGIN
317 OPEN PO_CSR(l_po_header_id);
318 FETCH PO_CSR INTO poRec;
319 l_last_upd_date := poRec.last_update_date;
320 if (PO_CSR%NOTFOUND) then
321 CLOSE PO_CSR;
322 end if;
323 CLOSE PO_CSR;
324 EXCEPTION
325 WHEN OTHERS THEN
326 if (sqlcode = '-54') then
327 x_error := 'true';
328 return;
329 end if;
330 END;
331 end if;
332
333 -- Check if the same record is being update
334 -- Check against last_updated_date to make sure that
335 -- The record that was queried is being updated
336
337 if (l_last_update_date <> l_last_upd_date) THEN
338 x_error := 'true';
339 return;
340 end if;
341 END IF;
342 --Bug 6772960 - End
343
344 --dbms_output.put_line('No Notification found ');
345 l_progress := '4';
346 select po_acceptances_s.nextval into l_acceptance_id from dual;
347
348 if l_po_release_id is null then
349 l_doc := l_po_header_id;
350 x_po_header_id := l_po_header_id;
351
352 select revision_num ,nvl(pending_signature_flag,'N'),type_lookup_code
353 into l_revision_num,l_pending_signature_flag,l_doc_subtype
354 from po_headers_all
355 where po_header_id = to_number(l_po_header_id);
356
357 if (l_doc_subtype in ('STANDARD','PLANNED')) then
358 l_doc_type := 'PO';
359 elsif (l_doc_subtype in ('BLANKET','CONTRACT')) then
360 l_doc_type := 'PA';
361 end if;
362 else
363 l_doc := l_po_release_id;
364 l_doc_type := 'RELEASE';
365 x_po_header_id := null;
366 l_doc_subtype := 'RELEASE';
367
368 select revision_num into l_revision_num
369 from po_releases_all
370 where po_release_id = to_number(l_po_release_id);
371 end if;
372 l_progress := '5';
373 l_role := null;
374 select pos_party_management_pkg.get_job_title_for_user( l_user_id)
375 into l_role
376 from dual;
377
378 l_progress := '6';
379 /*BINDING IMPACT */
380 if (l_pending_signature_flag = 'Y') then
381 l_signature_flag := 'Y';
382 l_accepting_party := 'S' ;
383 else
384 -- For Regular Acceptances Signature_Flag = 'N' and Accepting_Party='S'
385 l_signature_flag := 'N';
386 l_accepting_party := 'S' ;
387 end if;
388
389 l_progress := '7';
390 -- RDP
391
392 /* Bug No: 6670166
393 Modified the if condition to replace the Promise Date with Need By Date only if POS_DEFAULT_PROMISE_DATE_ACK profile is set to 'Y'.
394 Old behaviour used to replace the Promise Date with Need By Date irrespective of the POS_DEFAULT_PROMISE_DATE_ACK profile value. */
395
396 IF(l_po_accept_reject = 'Y' AND g_default_promise_date = 'Y') THEN
397 POS_ACK_PO.Acknowledge_promise_date (null,l_po_header_id,l_po_release_id,l_revision_num,l_user_id); -- RDP
398 END IF;
399
400 PO_ACCEPTANCES_INS_PVT.insert_row(
401 x_rowid => x_row_id,
402 x_acceptance_id => l_acceptance_id,
403 x_Last_Update_Date => x_Last_Update_Date,
404 x_Last_Updated_By => x_Last_Updated_By,
405 x_Last_Update_Login => l_Last_Update_Login,
406 p_creation_date => x_Creation_Date,
407 p_created_by => x_Created_by,
408 p_po_header_id => x_po_header_id,
409 p_po_release_id => l_po_release_id,
410 p_action => fnd_message.get_string('ICX','ICX_POS_ACK_WEB'),
411 p_action_date => x_Action_Date,
412 p_employee_id => null,
413 p_revision_num => l_revision_num,
414 p_accepted_flag => l_po_accept_reject,
415 p_acceptance_lookup_code=> l_po_acc_type_code,
416 p_note => l_po_ack_comments,
417 p_accepting_party => l_accepting_party,
418 p_signature_flag => l_signature_flag,
419 p_role => l_role);
420
421 l_progress := '8';
422 /* Use PO API As Above */
423 /* Bug 2807782, set employee_id to NULL. */
424 /* insert into po_acceptances (
425 acceptance_id,
426 last_update_Date,
427 last_updated_by,
428 last_update_login,
429 creation_date,
430 created_by,
431 po_header_id,
432 po_release_id,
433 action,
434 action_date,
435 --employee_id,
436 revision_num,
437 accepted_flag,
438 acceptance_lookup_code,
439 note
440 )
441 values (
442 l_acceptance_id,
443 sysdate,
444 l_user_id,
445 l_user_id,
446 sysdate,
447 l_user_id,
448 decode(l_po_release_id, null, l_po_header_id, null),
449 l_po_release_id,
450 fnd_message.get_string('ICX','ICX_POS_ACK_WEB'),
451 sysdate,
452 --l_po_buyer_id,
453 l_revision_num,
454 l_po_accept_reject,
455 l_po_acc_type_code,
456 l_po_ack_comments
457 );
458 */
459 If (l_po_accept_reject = 'N' and l_pending_signature_flag = 'Y') then
460 PO_SIGNATURE_PVT.Update_Po_Details(
461 p_po_header_id => l_doc,
462 p_status => 'REJECTED',
463 p_action_code => 'SUPPLIER REJECTED',
464 p_object_type_code => l_doc_type,
465 p_object_subtype_code => l_doc_subtype,
466 p_employee_id => null,
467 p_revision_num => l_revision_num);
468 END IF;
469
470 l_progress := '9';
471
472 -- Reset the Acceptance required Flag
473 --Bug 6772960 - Start
474 -- Update the last update date when po_headers_all table is updated.
475
476 if l_po_release_id is not null then
477 update po_releases_all
478 set acceptance_required_flag = 'N',
479 LAST_UPDATE_DATE = SYSDATE,
480 acceptance_due_date=''
481 where po_release_id = l_po_release_id;
482 else
483 -- Do not reset the acceptance_required Flag for signatures
484 if (l_pending_signature_flag = 'N') then
485 update po_headers_all
486 set acceptance_required_flag = 'N',
487 LAST_UPDATE_DATE = SYSDATE,
488 acceptance_due_date=''
489 where po_header_id = l_po_header_id;
490 end if;
491 end if;
492 --Bug 6772960 - End
493 l_progress := '10';
494
495 --dbms_output.put_line('Calling Workflow');
496 -- call workflow to send the notification
497 select po_wf_itemkey_s.nextval into l_seq_val from dual;
498
499 l_item_key := 'POSACKNT_' || l_doc || '_' || to_char(l_seq_val);
500
501 --dbms_output.put_line('Item Key is ' ||l_item_key);
502 wf_engine.createProcess(ItemType => l_item_type,
503 ItemKey => l_item_key,
504 Process => 'MAIN_PROCESS'
505 );
506
507 PO_WF_UTIL_PKG.SetItemAttrNumber
508 (
509 ItemType => l_item_type,
510 ItemKey => l_item_key,
511 aname => 'DOCUMENT_ID',
512 avalue => l_doc
513 );
514 PO_WF_UTIL_PKG.SetItemAttrText
515 (
516 ItemType => l_item_type,
517 ItemKey => l_item_key,
518 aname => 'DOCUMENT_TYPE_CODE',
519 avalue => l_doc_type
520 );
521
522 PO_WF_UTIL_PKG.SetItemAttrText
523 (
524 ItemType => l_item_type,
525 ItemKey => l_item_key,
526 aname => 'SUPPLIER_USER_NAME',
527 avalue => l_supp_username
528 );
529
530 PO_WF_UTIL_PKG.SetItemAttrText
531 (
532 ItemType => l_item_type,
533 ItemKey => l_item_key,
534 aname => 'ACCEPTANCE_RESULT',
535 avalue => l_accp_res
536 );
537
538 PO_WF_UTIL_PKG.SetItemAttrText (
539 ItemType => l_item_type,
540 ItemKey => l_item_key,
541 aname => 'ACCEPTANCE_TYPE',
542 avalue => l_accp_type);
543
544 PO_WF_UTIL_PKG.SetItemAttrText (
545 ItemType => l_item_type,
546 ItemKey => l_item_key,
547 aname => 'ACCEPTANCE_COMMENTS',
548 avalue => l_po_ack_comments);
549
550 PO_WF_UTIL_PKG.SetItemAttrText
551 (
552 ItemType => l_item_type,
553 ItemKey => l_item_key,
554 aname => 'SUPPLIER',
555 avalue => x_vendor
556 );
557
558 l_progress := '11';
559 wf_engine.StartProcess( ItemType => l_item_type,
560 ItemKey => l_item_key );
561 l_progress := '12';
562 end if;
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 l_error := sqlerrm;
567 IF g_fnd_debug = 'Y' THEN
568
569 IF ( FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
570 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
571 l_api_name || '.others_exception', l_progress||':'||sqlerrm);
572 END IF;
573
574 END IF;
575
576 raise;
577
578 END ACKNOWLEDGE_PO;
579
580 PROCEDURE CREATE_HEADER_PROCESS(
581 pos_po_header_id IN VARCHAR2,
582 pos_po_release_id IN VARCHAR2,
583 pos_user_id IN NUMBER,
584 pos_item_type OUT NOCOPY VARCHAR2,
585 pos_item_key OUT NOCOPY VARCHAR2
586 )
587 IS
588 l_item_type VARCHAR2(100) := 'POSMPDPT';
589 l_item_key VARCHAR2(100);
590 l_doc VARCHAR2(240);
591 l_user_id NUMBER;
592 l_seq_val NUMBER;
593 l_error VARCHAR2(240);
594 BEGIN
595
596
597 if pos_po_release_id is null then
598 l_doc := pos_po_header_id;
599 else
600 l_doc := pos_po_release_id;
601 end if;
602
603 select po_wf_itemkey_s.nextval into l_seq_val
604 from dual;
605
606 l_item_key := 'POS_PODATE_CHG_' || l_doc || '_' || to_char(l_seq_val);
607 pos_item_type := l_item_type;
608 pos_item_key := l_item_key;
609 wf_engine.createProcess(ItemType => l_item_type,
610 ItemKey => l_item_key,
611 Process => 'MAIN_PROCESS'
612 );
613 PO_WF_UTIL_PKG.SetItemAttrNumber
614 (
615 ItemType => l_item_type,
616 ItemKey => l_item_key,
617 aname => 'SUPPLIER_USER_ID',
618 avalue => pos_user_id
619 );
620 PO_WF_UTIL_PKG.SetItemAttrNumber
621 (
622 ItemType => l_item_type,
623 ItemKey => l_item_key,
624 aname => 'DOCUMENT_ID',
625 avalue => to_number(l_doc)
626 );
627
628 if pos_po_release_id is null then
629 update po_headers_all
630 set authorization_status = 'IN PROCESS'
631 where po_header_id = to_number(pos_po_header_id);
632 else
633 update po_releases_all
634 set authorization_status = 'IN PROCESS'
635 where po_release_id = to_number(pos_po_release_id);
636 end if;
637
638
639
640 EXCEPTION
641 WHEN OTHERS THEN
642 l_error := sqlerrm;
643
644 END CREATE_HEADER_PROCESS;
645
646 PROCEDURE START_HEADER_PROCESS(
647 l_item_type IN VARCHAR2,
648 l_item_key IN VARCHAR2
649 )
650 IS
651 l_error VARCHAR2(240);
652 BEGIN
653 wf_engine.StartProcess( ItemType => l_item_type,
654 ItemKey => l_item_key );
655 EXCEPTION
656 WHEN OTHERS THEN
657 l_error := sqlerrm;
658 END START_HEADER_PROCESS;
659
660
661 PROCEDURE ADD_SHIPMENT(
662 l_item_type IN VARCHAR2,
663 l_item_key IN VARCHAR2,
664 l_line_location_id IN VARCHAR2,
665 l_new_promise_date IN VARCHAR2,
666 l_old_promise_date IN VARCHAR2,
667 l_new_need_by_date IN VARCHAR2,
668 l_old_need_by_date IN VARCHAR2,
669 l_reason IN VARCHAR2
670 )
671 IS
672 -- l_error VARCHAR2(240);
673 BEGIN
674
675 -- Add Shipment Atrributes
676 -- Commenting out the call as POS_CHANGE_PROM_DATES has been stubbed out
677
678 /* POS_CHANGE_PROM_DATES.Add_Shipment_Attribute
679 (
680 l_item_type,
681 l_item_key,
682 l_line_location_id,
683 to_date(l_old_promise_date,'YYYY-MM-DD'),
684 to_date(l_new_promise_date,'YYYY-MM-DD'),
685 to_date(l_old_need_by_date,'YYYY-MM-DD'),
686 to_date(l_new_need_by_date,'YYYY-MM-DD'),
687 l_reason
688 ); */
689
690 null;
691 EXCEPTION
692 WHEN OTHERS THEN
693 -- l_error := sqlerrm;
694 null;
695 END ADD_SHIPMENT;
696
697 --RDP new procedure defaults the promise_date with need_by_date while acknowledging
698 PROCEDURE Acknowledge_promise_date (
699 p_line_location_id IN NUMBER,
700 p_po_header_id IN NUMBER,
701 p_po_release_id IN NUMBER,
702 p_revision_num IN NUMBER,
703 p_user_id IN NUMBER)
704
705 IS
706 BEGIN
707
708 --write_log('JUGGU',TO_CHAR(p_revision_num),'SU',sysdate);
709 IF p_po_release_id is null THEN
710
711 IF p_line_location_id is not null THEN
712
713 UPDATE PO_LINE_LOCATIONS_ALL PLL
714 SET pll.promised_date = pll.need_by_date,
715 pll.last_update_date = sysdate,
716 pll.last_updated_by = p_user_id
717 WHERE pll.po_header_id= p_po_header_id
718 AND pll.po_release_id is null
719 AND pll.line_location_id= p_line_location_id
720 AND pll.promised_date is null;
721
722 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
723 SET plla.promised_date = plla.need_by_date,
724 plla.last_update_date = sysdate,
725 plla.last_updated_by = p_user_id
726 WHERE plla.po_header_id = p_po_header_id
727 AND plla.po_release_id is null
728 AND plla.line_location_id = p_line_location_id
729 AND plla.promised_date is null
730 AND plla.revision_num = (SELECT max(plla2.revision_num)
731 FROM po_line_locations_archive_all plla2
732 WHERE plla2.line_location_id = plla.line_location_id
733 AND plla.revision_num <= p_revision_num);
734 ELSE
735
736 UPDATE PO_LINE_LOCATIONS_ALL PLL
737 SET pll.promised_date = pll.need_by_date,
738 pll.last_update_date = sysdate,
739 pll.last_updated_by = p_user_id
740 WHERE pll.po_header_id = p_po_header_id
741 AND pll.promised_date is null
742 /* AND exists (
743 SELECT 1
744 FROM PO_ACCEPTANCES PA
745 WHERE pa.po_header_id = p_po_header_id
746 AND pa.revision_num = p_revision_num
747 AND pa.po_line_location_id = pll.line_location_id ) */
748 AND nvl(pll.cancel_flag, 'N') = 'N'
749 AND ((nvl(pll.closed_code, 'OPEN') = 'OPEN' and
750 nvl(pll.consigned_flag, 'N') = 'N') OR
751 (pll.closed_code = 'CLOSED FOR INVOICE' and pll.consigned_flag = 'Y'));
752
753 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
754 SET plla.promised_date = plla.need_by_date,
755 plla.last_update_date = sysdate,
756 plla.last_updated_by = p_user_id
757 WHERE plla.po_header_id = p_po_header_id
758 AND plla.promised_date is null
759 AND plla.revision_num = (SELECT max(plla2.revision_num)
760 FROM po_line_locations_archive_all plla2
761 WHERE plla2.line_location_id = plla.line_location_id
762 AND plla.revision_num <= p_revision_num)
763 /* AND exists (
764 SELECT 1
765 FROM PO_ACCEPTANCES PA
766 WHERE pa.po_header_id = p_po_header_id
767 AND pa.revision_num = p_revision_num
768 AND pa.po_line_location_id = plla.line_location_id ) */
769 AND nvl(plla.cancel_flag, 'N') = 'N'
770 AND ((nvl(plla.closed_code, 'OPEN') = 'OPEN' and
771 nvl(plla.consigned_flag, 'N') = 'N') OR
772 (plla.closed_code = 'CLOSED FOR INVOICE' and plla.consigned_flag = 'Y'));
773
774 END IF;
775
776 ELSE
777
778
779 IF p_line_location_id is not null THEN
780
781 UPDATE po_line_locations_all pll
782 SET pll.promised_date =need_by_date,
783 pll.last_update_date = sysdate,
784 pll.last_updated_by = p_user_id
785 WHERE pll.po_header_id= p_po_header_id
786 AND pll.line_location_id= p_line_location_id
787 AND pll.po_release_id = p_po_release_id
788 AND pll. promised_date is null;
789
790 UPDATE po_line_locations_archive_all plla
791 SET plla.promised_date = plla.need_by_date,
792 plla.last_update_date = sysdate,
793 plla.last_updated_by = p_user_id
794 WHERE plla.po_header_id= p_po_header_id
795 AND plla.line_location_id= p_line_location_id
796 AND plla.po_release_id = p_po_release_id
797 AND plla. promised_date is null
798 AND plla.revision_num = (SELECT max(plla2.revision_num)
799 FROM po_line_locations_archive_all plla2
800 WHERE plla2.line_location_id = plla.line_location_id
801 AND plla.revision_num <= p_revision_num);
802
803 ELSE
804
805 UPDATE PO_LINE_LOCATIONS_ALL PLL
806 SET pll.promised_date =need_by_date,
807 pll.last_update_date = sysdate,
808 pll.last_updated_by = p_user_id
809 WHERE pll.po_header_id = p_po_header_id
810 AND pll.po_release_id = p_po_release_id
811 AND pll.promised_date is null
812 /* AND exists (
813 SELECT 1
814 FROM PO_ACCEPTANCES PA
815 WHERE pa.po_release_id = p_po_release_id
816 AND pa.po_header_id = p_po_header_id
817 AND pa.revision_num = p_revision_num
818 AND pa.po_line_location_id = PLL.line_location_id ) */
819 AND nvl(pll.cancel_flag, 'N') = 'N'
820 AND ((nvl(pll.closed_code, 'OPEN') = 'OPEN' AND (nvl(pll.consigned_flag, 'N') = 'N')) OR (pll.closed_code = 'CLOSED FOR INVOICE' AND pll.consigned_flag = 'Y'));
821
822 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
823 SET plla.promised_date =need_by_date,
824 plla.last_update_date = sysdate,
825 plla.last_updated_by = p_user_id
826 WHERE plla.po_header_id = p_po_header_id
827 AND plla.po_release_id = p_po_release_id
828 AND plla.promised_date is null
829 AND plla.revision_num = (SELECT max(plla2.revision_num)
830 FROM po_line_locations_archive_all plla2
831 WHERE plla2.line_location_id = plla.line_location_id
832 AND plla.revision_num <= p_revision_num)
833 /* AND exists (
834 SELECT 1
835 FROM PO_ACCEPTANCES PA
836 WHERE pa.po_release_id = p_po_release_id
837 AND pa.po_header_id = p_po_header_id
838 AND pa.revision_num = p_revision_num
839 AND pa.po_line_location_id = PLLA.line_location_id ) */
840 AND nvl(plla.cancel_flag, 'N') = 'N'
841 AND ((nvl(plla.closed_code, 'OPEN') = 'OPEN' AND (nvl(plla.consigned_flag, 'N') = 'N')) OR (plla.closed_code = 'CLOSED FOR INVOICE' AND plla.consigned_flag = 'Y'));
842
843 END IF;
844
845
846
847 END IF;
848
849
850 END Acknowledge_promise_date;
851
852
853 END POS_ACK_PO;