[Home] [Help]
PACKAGE BODY: APPS.POS_ACK_PO
Source
1 PACKAGE BODY POS_ACK_PO AS
2 /* $Header: POSISPAB.pls 120.6 2008/02/26 15:12:30 vchiran 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
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 --Bug 6772960 - End
208
209 --dbms_output.put_line('Notification found from core PO');
210 if l_po_accept_reject = 'Y' then
211 l_action := 'ACCEPT';
212 else
213 l_action := 'REJECT';
214
215 end if;
216
217
218 -- Set the attributes for the acceptance message to buyer
219 PO_WF_UTIL_PKG.SetItemAttrText
220 (
221 ItemType => l_po_item_type,
222 ItemKey => l_po_item_key,
223 aname => 'ACCEPTANCE_TYPE',
224 avalue => l_accp_type
225 );
226
227 PO_WF_UTIL_PKG.SetItemAttrText
228 (
229 ItemType => l_po_item_type,
230 ItemKey => l_po_item_key,
231 aname => 'ACCEPTANCE_LOOKUP_CODE',
232 avalue => l_po_acc_type_code
233 );
234
235 PO_WF_UTIL_PKG.SetItemAttrText
236 (
237 ItemType => l_po_item_type,
238 ItemKey => l_po_item_key,
239 aname => 'ACCEPTANCE_RESULT',
240 avalue => l_accp_res
241 );
242
243
244 PO_WF_UTIL_PKG.SetItemAttrText
245 (
246 ItemType => l_po_item_type,
247 ItemKey => l_po_item_key,
248 aname => 'ACCEPTANCE_COMMENTS',
249 avalue => l_po_ack_comments
250 );
251
252 PO_WF_UTIL_PKG.SetItemAttrText
253 (
254 ItemType => l_po_item_type,
255 ItemKey => l_po_item_key,
256 aname => 'SUPPLIER',
257 avalue => x_vendor
258 );
259
260 PO_WF_UTIL_PKG.SetItemAttrText
261 (
262 ItemType => l_po_item_type,
263 ItemKey => l_po_item_key,
264 aname => 'SUPPLIER_USER_NAME',
265 avalue => l_supp_username
266 );
267
268 l_progress := '3-0';
269 /*
270 We will not reinitialize apps here. This may make the next activity after the
271 notification be deferred briefly. But, this helps in keeping the responder name
272 same as the l_supplier_name. See bug 3900146 for details.
273
274 */
275 l_progress := '3-1';
276 WF_NOTIFICATION.SetAttrText(nid =>l_nid ,
277 aname => 'RESULT',
278 avalue => l_action);
279
280 l_progress := '3-2';
281 WF_NOTIFICATION.Respond(nid =>l_nid ,
282 respond_comment =>null,
283 responder =>l_supp_username
284 --action_source in varchar2 default null
285 );
286
287 else
288
289 --Bug 6772960 - Start
290 /*
291 l_last_update_date contains the last update date which is currently seen by supplier user.
292 l_last_upd_date contains the last update date which is currently there is database.
293 If there is any mismatch in the above dates that means the PO data whatever supplier user
294 seeing currently is not the latest.
295 */
296 -- Lock the PO Header Row for update of Last Update Date
297 if (l_po_release_id is not null ) then
298 BEGIN
299 OPEN REL_CSR(l_po_release_id);
300 FETCH REL_CSR INTO relRec;
301 l_last_upd_date := relRec.last_update_date;
302 if (REL_CSR%NOTFOUND) then
303 CLOSE REL_CSR;
304 end if;
305 CLOSE REL_CSR;
306 EXCEPTION
307 WHEN OTHERS THEN
308 if (sqlcode = '-54') THEN
309 x_error := 'true';
310 return;
311 end if;
312 END;
313 else
314 BEGIN
315 OPEN PO_CSR(l_po_header_id);
316 FETCH PO_CSR INTO poRec;
317 l_last_upd_date := poRec.last_update_date;
318 if (PO_CSR%NOTFOUND) then
319 CLOSE PO_CSR;
320 end if;
321 CLOSE PO_CSR;
322 EXCEPTION
323
324 WHEN OTHERS THEN
325 if (sqlcode = '-54') then
326 x_error := 'true';
327 return;
328 end if;
329 END;
330 end if;
331
332 -- Check if the same record is being update
333 -- Check against last_updated_date to make sure that
334 -- The record that was queried is being updated
335
336 if (l_last_update_date <> l_last_upd_date) THEN
337 x_error := 'true';
338 return;
339 end if;
340
341 --Bug 6772960 - End
342
343 --dbms_output.put_line('No Notification found ');
344 l_progress := '4';
345 select po_acceptances_s.nextval into l_acceptance_id from dual;
346
347 if l_po_release_id is null then
348 l_doc := l_po_header_id;
349 x_po_header_id := l_po_header_id;
350
351 select revision_num ,nvl(pending_signature_flag,'N'),type_lookup_code
352 into l_revision_num,l_pending_signature_flag,l_doc_subtype
353 from po_headers_all
354 where po_header_id = to_number(l_po_header_id);
355
356 if (l_doc_subtype in ('STANDARD','PLANNED')) then
357 l_doc_type := 'PO';
358 elsif (l_doc_subtype in ('BLANKET','CONTRACT')) then
359 l_doc_type := 'PA';
360 end if;
361 else
362 l_doc := l_po_release_id;
363 l_doc_type := 'RELEASE';
364 x_po_header_id := null;
365 l_doc_subtype := 'RELEASE';
366
367 select revision_num into l_revision_num
368 from po_releases_all
369 where po_release_id = to_number(l_po_release_id);
370 end if;
371 l_progress := '5';
372 l_role := null;
373 select pos_party_management_pkg.get_job_title_for_user( l_user_id)
374 into l_role
375 from dual;
376
377 l_progress := '6';
378 /*BINDING IMPACT */
379 if (l_pending_signature_flag = 'Y') then
380 l_signature_flag := 'Y';
381 l_accepting_party := 'S' ;
382 else
383 -- For Regular Acceptances Signature_Flag = 'N' and Accepting_Party='S'
384 l_signature_flag := 'N';
385 l_accepting_party := 'S' ;
386 end if;
387
388 l_progress := '7';
389 -- RDP
390
391 /* Bug No: 6670166
392 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'.
393 Old behaviour used to replace the Promise Date with Need By Date irrespective of the POS_DEFAULT_PROMISE_DATE_ACK profile value. */
394
395 IF(l_po_accept_reject = 'Y' AND g_default_promise_date = 'Y') THEN
396 POS_ACK_PO.Acknowledge_promise_date (null,l_po_header_id,l_po_release_id,l_revision_num,l_user_id); -- RDP
397 END IF;
398
399 PO_ACCEPTANCES_INS_PVT.insert_row(
400 x_rowid => x_row_id,
401 x_acceptance_id => l_acceptance_id,
402 x_Last_Update_Date => x_Last_Update_Date,
403 x_Last_Updated_By => x_Last_Updated_By,
404 x_Last_Update_Login => l_Last_Update_Login,
405 p_creation_date => x_Creation_Date,
406 p_created_by => x_Created_by,
407 p_po_header_id => x_po_header_id,
408 p_po_release_id => l_po_release_id,
409 p_action => fnd_message.get_string('ICX','ICX_POS_ACK_WEB'),
410 p_action_date => x_Action_Date,
411 p_employee_id => null,
412 p_revision_num => l_revision_num,
413 p_accepted_flag => l_po_accept_reject,
414 p_acceptance_lookup_code=> l_po_acc_type_code,
415 p_note => l_po_ack_comments,
416 p_accepting_party => l_accepting_party,
417 p_signature_flag => l_signature_flag,
418 p_role => l_role);
419
420 l_progress := '8';
421 /* Use PO API As Above */
422 /* Bug 2807782, set employee_id to NULL. */
423 /* insert into po_acceptances (
424 acceptance_id,
425 last_update_Date,
426 last_updated_by,
427 last_update_login,
428 creation_date,
429 created_by,
430 po_header_id,
431 po_release_id,
432 action,
433 action_date,
434 --employee_id,
435 revision_num,
436 accepted_flag,
437 acceptance_lookup_code,
438 note
439 )
440 values (
441 l_acceptance_id,
442 sysdate,
443 l_user_id,
444 l_user_id,
445 sysdate,
446 l_user_id,
447 decode(l_po_release_id, null, l_po_header_id, null),
448 l_po_release_id,
449 fnd_message.get_string('ICX','ICX_POS_ACK_WEB'),
450 sysdate,
451 --l_po_buyer_id,
452 l_revision_num,
453 l_po_accept_reject,
454 l_po_acc_type_code,
455 l_po_ack_comments
456 );
457 */
458 If (l_po_accept_reject = 'N' and l_pending_signature_flag = 'Y') then
459 PO_SIGNATURE_PVT.Update_Po_Details(
460 p_po_header_id => l_doc,
461 p_status => 'REJECTED',
462 p_action_code => 'SUPPLIER REJECTED',
463 p_object_type_code => l_doc_type,
464 p_object_subtype_code => l_doc_subtype,
465 p_employee_id => null,
466 p_revision_num => l_revision_num);
467 END IF;
468
469 l_progress := '9';
470
471 -- Reset the Acceptance required Flag
472 --Bug 6772960 - Start
473 -- Update the last update date when po_headers_all table is updated.
474
475 if l_po_release_id is not null then
476 update po_releases_all
477 set acceptance_required_flag = 'N',
478 LAST_UPDATE_DATE = SYSDATE,
479 acceptance_due_date=''
480 where po_release_id = l_po_release_id;
481 else
482 -- Do not reset the acceptance_required Flag for signatures
483 if (l_pending_signature_flag = 'N') then
484 update po_headers_all
485 set acceptance_required_flag = 'N',
486 LAST_UPDATE_DATE = SYSDATE,
487 acceptance_due_date=''
488 where po_header_id = l_po_header_id;
489 end if;
490 end if;
491 --Bug 6772960 - End
492 l_progress := '10';
493
494 --dbms_output.put_line('Calling Workflow');
495 -- call workflow to send the notification
496 select po_wf_itemkey_s.nextval into l_seq_val from dual;
497
498 l_item_key := 'POSACKNT_' || l_doc || '_' || to_char(l_seq_val);
499
500 --dbms_output.put_line('Item Key is ' ||l_item_key);
501 wf_engine.createProcess(ItemType => l_item_type,
502 ItemKey => l_item_key,
503 Process => 'MAIN_PROCESS'
504 );
505
506 PO_WF_UTIL_PKG.SetItemAttrNumber
507 (
508 ItemType => l_item_type,
509 ItemKey => l_item_key,
510 aname => 'DOCUMENT_ID',
511 avalue => l_doc
512 );
513 PO_WF_UTIL_PKG.SetItemAttrText
514 (
515 ItemType => l_item_type,
516 ItemKey => l_item_key,
517 aname => 'DOCUMENT_TYPE_CODE',
518 avalue => l_doc_type
519 );
520
521 PO_WF_UTIL_PKG.SetItemAttrText
522 (
523 ItemType => l_item_type,
524 ItemKey => l_item_key,
525 aname => 'SUPPLIER_USER_NAME',
526 avalue => l_supp_username
527 );
528
529 PO_WF_UTIL_PKG.SetItemAttrText
530 (
531 ItemType => l_item_type,
532 ItemKey => l_item_key,
533 aname => 'ACCEPTANCE_RESULT',
534 avalue => l_accp_res
535 );
536
537 PO_WF_UTIL_PKG.SetItemAttrText (
538 ItemType => l_item_type,
539 ItemKey => l_item_key,
540 aname => 'ACCEPTANCE_TYPE',
541 avalue => l_accp_type);
542
543 PO_WF_UTIL_PKG.SetItemAttrText (
544 ItemType => l_item_type,
545 ItemKey => l_item_key,
546 aname => 'ACCEPTANCE_COMMENTS',
547 avalue => l_po_ack_comments);
548
549 PO_WF_UTIL_PKG.SetItemAttrText
550 (
551 ItemType => l_item_type,
552 ItemKey => l_item_key,
553 aname => 'SUPPLIER',
554 avalue => x_vendor
555 );
556
557 l_progress := '11';
558 wf_engine.StartProcess( ItemType => l_item_type,
559 ItemKey => l_item_key );
560 l_progress := '12';
561 end if;
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 l_error := sqlerrm;
566 IF g_fnd_debug = 'Y' THEN
567
568 IF ( FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
569 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
570 l_api_name || '.others_exception', l_progress||':'||sqlerrm);
571 END IF;
572
573 END IF;
574
575 raise;
576
577 END ACKNOWLEDGE_PO;
578
579 PROCEDURE CREATE_HEADER_PROCESS(
580 pos_po_header_id IN VARCHAR2,
581 pos_po_release_id IN VARCHAR2,
582 pos_user_id IN NUMBER,
583 pos_item_type OUT NOCOPY VARCHAR2,
584 pos_item_key OUT NOCOPY VARCHAR2
585 )
586 IS
587 l_item_type VARCHAR2(100) := 'POSMPDPT';
588 l_item_key VARCHAR2(100);
589 l_doc VARCHAR2(240);
590 l_user_id NUMBER;
591 l_seq_val NUMBER;
592 l_error VARCHAR2(240);
593 BEGIN
594
595
596 if pos_po_release_id is null then
597 l_doc := pos_po_header_id;
598 else
599 l_doc := pos_po_release_id;
600 end if;
601
602 select po_wf_itemkey_s.nextval into l_seq_val
603 from dual;
604
605 l_item_key := 'POS_PODATE_CHG_' || l_doc || '_' || to_char(l_seq_val);
606 pos_item_type := l_item_type;
607 pos_item_key := l_item_key;
608 wf_engine.createProcess(ItemType => l_item_type,
609 ItemKey => l_item_key,
610 Process => 'MAIN_PROCESS'
611 );
612 PO_WF_UTIL_PKG.SetItemAttrNumber
613 (
614 ItemType => l_item_type,
615 ItemKey => l_item_key,
616 aname => 'SUPPLIER_USER_ID',
617 avalue => pos_user_id
618 );
619 PO_WF_UTIL_PKG.SetItemAttrNumber
620 (
621 ItemType => l_item_type,
622 ItemKey => l_item_key,
623 aname => 'DOCUMENT_ID',
624 avalue => to_number(l_doc)
625 );
626
627 if pos_po_release_id is null then
628 update po_headers_all
629 set authorization_status = 'IN PROCESS'
630 where po_header_id = to_number(pos_po_header_id);
631 else
632 update po_releases_all
633 set authorization_status = 'IN PROCESS'
634 where po_release_id = to_number(pos_po_release_id);
635 end if;
636
637
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 l_error := sqlerrm;
642
643 END CREATE_HEADER_PROCESS;
644
645 PROCEDURE START_HEADER_PROCESS(
646 l_item_type IN VARCHAR2,
647 l_item_key IN VARCHAR2
648 )
649 IS
650 l_error VARCHAR2(240);
651 BEGIN
652 wf_engine.StartProcess( ItemType => l_item_type,
653 ItemKey => l_item_key );
654 EXCEPTION
655 WHEN OTHERS THEN
656 l_error := sqlerrm;
657 END START_HEADER_PROCESS;
658
659
660 PROCEDURE ADD_SHIPMENT(
661 l_item_type IN VARCHAR2,
662 l_item_key IN VARCHAR2,
663 l_line_location_id IN VARCHAR2,
664 l_new_promise_date IN VARCHAR2,
665 l_old_promise_date IN VARCHAR2,
666 l_new_need_by_date IN VARCHAR2,
667 l_old_need_by_date IN VARCHAR2,
668 l_reason IN VARCHAR2
669 )
670 IS
671 -- l_error VARCHAR2(240);
672 BEGIN
673
674 -- Add Shipment Atrributes
675 -- Commenting out the call as POS_CHANGE_PROM_DATES has been stubbed out
676
677 /* POS_CHANGE_PROM_DATES.Add_Shipment_Attribute
678 (
679 l_item_type,
680 l_item_key,
681 l_line_location_id,
682 to_date(l_old_promise_date,'YYYY-MM-DD'),
683 to_date(l_new_promise_date,'YYYY-MM-DD'),
684 to_date(l_old_need_by_date,'YYYY-MM-DD'),
685 to_date(l_new_need_by_date,'YYYY-MM-DD'),
686 l_reason
687 ); */
688
689 null;
690 EXCEPTION
691 WHEN OTHERS THEN
692 -- l_error := sqlerrm;
693 null;
694 END ADD_SHIPMENT;
695
696 --RDP new procedure defaults the promise_date with need_by_date while acknowledging
697 PROCEDURE Acknowledge_promise_date (
698 p_line_location_id IN NUMBER,
699 p_po_header_id IN NUMBER,
700 p_po_release_id IN NUMBER,
701 p_revision_num IN NUMBER,
702 p_user_id IN NUMBER)
703
704 IS
705 BEGIN
706
707 --write_log('JUGGU',TO_CHAR(p_revision_num),'SU',sysdate);
708 IF p_po_release_id is null THEN
709
710 IF p_line_location_id is not null THEN
711
712 UPDATE PO_LINE_LOCATIONS_ALL PLL
713 SET pll.promised_date = pll.need_by_date,
714 pll.last_update_date = sysdate,
715 pll.last_updated_by = p_user_id
716 WHERE pll.po_header_id= p_po_header_id
717 AND pll.po_release_id is null
718 AND pll.line_location_id= p_line_location_id
719 AND pll.promised_date is null;
720
721 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
722 SET plla.promised_date = plla.need_by_date,
723 plla.last_update_date = sysdate,
724 plla.last_updated_by = p_user_id
725 WHERE plla.po_header_id = p_po_header_id
726 AND plla.po_release_id is null
727 AND plla.line_location_id = p_line_location_id
728 AND plla.promised_date is null
729 AND plla.revision_num = (SELECT max(plla2.revision_num)
730 FROM po_line_locations_archive_all plla2
731 WHERE plla2.line_location_id = plla.line_location_id
732 AND plla.revision_num <= p_revision_num);
733 ELSE
734
735 UPDATE PO_LINE_LOCATIONS_ALL PLL
736 SET pll.promised_date = pll.need_by_date,
737 pll.last_update_date = sysdate,
738 pll.last_updated_by = p_user_id
739 WHERE pll.po_header_id = p_po_header_id
740 AND pll.promised_date is null
741 /* AND exists (
742 SELECT 1
743 FROM PO_ACCEPTANCES PA
744 WHERE pa.po_header_id = p_po_header_id
745 AND pa.revision_num = p_revision_num
746 AND pa.po_line_location_id = pll.line_location_id ) */
747 AND nvl(pll.cancel_flag, 'N') = 'N'
748 AND ((nvl(pll.closed_code, 'OPEN') = 'OPEN' and
749 nvl(pll.consigned_flag, 'N') = 'N') OR
750 (pll.closed_code = 'CLOSED FOR INVOICE' and pll.consigned_flag = 'Y'));
751
752 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
753 SET plla.promised_date = plla.need_by_date,
754 plla.last_update_date = sysdate,
755 plla.last_updated_by = p_user_id
756 WHERE plla.po_header_id = p_po_header_id
757 AND plla.promised_date is null
758 AND plla.revision_num = (SELECT max(plla2.revision_num)
759 FROM po_line_locations_archive_all plla2
760 WHERE plla2.line_location_id = plla.line_location_id
761 AND plla.revision_num <= p_revision_num)
762 /* AND exists (
763 SELECT 1
764 FROM PO_ACCEPTANCES PA
765 WHERE pa.po_header_id = p_po_header_id
766 AND pa.revision_num = p_revision_num
767 AND pa.po_line_location_id = plla.line_location_id ) */
768 AND nvl(plla.cancel_flag, 'N') = 'N'
769 AND ((nvl(plla.closed_code, 'OPEN') = 'OPEN' and
770 nvl(plla.consigned_flag, 'N') = 'N') OR
771 (plla.closed_code = 'CLOSED FOR INVOICE' and plla.consigned_flag = 'Y'));
772
773 END IF;
774
775 ELSE
776
777
778 IF p_line_location_id is not null THEN
779
780 UPDATE po_line_locations_all pll
781 SET pll.promised_date =need_by_date,
782 pll.last_update_date = sysdate,
783 pll.last_updated_by = p_user_id
784 WHERE pll.po_header_id= p_po_header_id
785 AND pll.line_location_id= p_line_location_id
786 AND pll.po_release_id = p_po_release_id
787 AND pll. promised_date is null;
788
789 UPDATE po_line_locations_archive_all plla
790 SET plla.promised_date = plla.need_by_date,
791 plla.last_update_date = sysdate,
792 plla.last_updated_by = p_user_id
793 WHERE plla.po_header_id= p_po_header_id
794 AND plla.line_location_id= p_line_location_id
795 AND plla.po_release_id = p_po_release_id
796 AND plla. promised_date is null
797 AND plla.revision_num = (SELECT max(plla2.revision_num)
798 FROM po_line_locations_archive_all plla2
799 WHERE plla2.line_location_id = plla.line_location_id
800 AND plla.revision_num <= p_revision_num);
801
802 ELSE
803
804 UPDATE PO_LINE_LOCATIONS_ALL PLL
805 SET pll.promised_date =need_by_date,
806 pll.last_update_date = sysdate,
807 pll.last_updated_by = p_user_id
808 WHERE pll.po_header_id = p_po_header_id
809 AND pll.po_release_id = p_po_release_id
810 AND pll.promised_date is null
811 /* AND exists (
812 SELECT 1
813 FROM PO_ACCEPTANCES PA
814 WHERE pa.po_release_id = p_po_release_id
815 AND pa.po_header_id = p_po_header_id
816 AND pa.revision_num = p_revision_num
817 AND pa.po_line_location_id = PLL.line_location_id ) */
818 AND nvl(pll.cancel_flag, 'N') = 'N'
819 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'));
820
821 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
822 SET plla.promised_date =need_by_date,
823 plla.last_update_date = sysdate,
824 plla.last_updated_by = p_user_id
825 WHERE plla.po_header_id = p_po_header_id
826 AND plla.po_release_id = p_po_release_id
827 AND plla.promised_date is null
828 AND plla.revision_num = (SELECT max(plla2.revision_num)
829 FROM po_line_locations_archive_all plla2
830 WHERE plla2.line_location_id = plla.line_location_id
831 AND plla.revision_num <= p_revision_num)
832 /* AND exists (
833 SELECT 1
834 FROM PO_ACCEPTANCES PA
835 WHERE pa.po_release_id = p_po_release_id
836 AND pa.po_header_id = p_po_header_id
837 AND pa.revision_num = p_revision_num
838 AND pa.po_line_location_id = PLLA.line_location_id ) */
839 AND nvl(plla.cancel_flag, 'N') = 'N'
840 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'));
841
842 END IF;
843
844
845
846 END IF;
847
848
849 END Acknowledge_promise_date;
850
851
852 END POS_ACK_PO;