[Home] [Help]
PACKAGE BODY: APPS.PO_REQCHANGEREQUESTNOTIF_PVT
Source
1 PACKAGE BODY PO_ReqChangeRequestNotif_PVT AS
2 /* $Header: POXVRCNB.pls 120.15 2006/10/03 13:40:28 kikhlaq noship $ */
3
4 /*************************************************************************
5 * +=======================================================================+
6 * | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
7 * | All rights reserved. |
8 * +=======================================================================+
9 * | FILE NAME: POXVRCNB.pls |
10 * | |
11 * | PACKAGE NAME: PO_ReqChangeRequestNotif_PVT |
12 * | |
13 * | DESCRIPTION: |
14 * | PO_ReqChangeRequestNotif_PVT is a private level package. |
15 * | It contains 3 public procedure which are used to generate |
16 * | notifications used in requester change order workflows. |
17 * | |
18 * | PROCEDURES: |
19 * | Get_Req_Chg_Approval_Notif |
20 * | generate the req change approval notification |
21 * | Get_Req_Chg_Response_Notif |
22 * | generate the notification to requester about the response |
23 * | to the change request |
24 * | Get_Po_Chg_Approval_Notif |
25 * | generate the notification to the buyer of the PO |
26 * | for buyer's approval |
27 * | FUNCTIONS: |
28 * | none |
29 * | |
30 * +=======================================================================+
31 */
32
33 NL VARCHAR2(1) := fnd_global.newline;
34 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_REQCHANGEREQUESTNOTIF_PVT';
35 G_FILE_NAME CONSTANT VARCHAR2(30) := 'POXVRCNB.pls';
36
37 -- Read the profile option that enables/disables the debug log
38 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
39 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
40
41 Procedure get_additional_details(p_req_header_id in number,
42 p_document out NOCOPY varchar2);
43
44
45 /*************************************************************************
46 * Private Procedure: GetReqLinesDetailsLink
47 *
48 * Effects: generate the line part of the req change approval
49 * notification
50 *
51 * Returns:
52 ************************************************************************/
53 PROCEDURE GetReqLinesDetailsLink(document_id in varchar2,
54 display_type in varchar2,
55 document in out NOCOPY varchar2,
56 document_type in out NOCOPY varchar2);
57 /*************************************************************************
58 * Private Procedure: GetReqLinesResponse
59 *
60 * Effects: generate the line part of the change response
61 * notification
62 *
63 * Returns:
64 ************************************************************************/
65 PROCEDURE GetReqLinesResponse(document_id in varchar2,
66 display_type in varchar2,
67 document in out NOCOPY varchar2,
68 document_type in out NOCOPY varchar2);
69
70 /*************************************************************************
71 * Private Procedure: GetActionHistoryHtml
72 *
73 * Effects: generate the action history part of the change response
74 * notification and req change approval notification
75 *
76 * Returns:
77 ************************************************************************/
78 PROCEDURE GetActionHistoryHtml(document_id in varchar2,
79 display_type in varchar2,
80 document in out NOCOPY varchar2,
81 document_type in out NOCOPY varchar2);
82
83
84 /*************************************************************************
85 * Private Procedure: GetPendingActionHtml
86 *
87 * Effects: generate the pending action history part, called in
88 * GetActionHistoryHtml
89 *
90 * Returns:
91 ************************************************************************/
92 PROCEDURE GetPendingActionHtml(p_item_type in varchar2,
93 p_item_key in varchar2,
94 max_seqno in number,
95 p_document out NOCOPY varchar2);
96
97 /*************************************************************************
98 * Private Procedure: ConstructHeaderInfo
99 *
100 * Effects: generate the header part of the req approval notification
101 * and the change response notification
102 *
103 * Returns:
104 ************************************************************************/
105 function ConstructHeaderInfo(l_item_type in varchar2,
106 l_item_key in varchar2,
107 l_change_request_group_id in number,
108 l_document_id in number,
109 l_call_from in varchar2) return varchar2;
110 /*
111 l_old_req_amount in varchar2,
112 l_currency_code in varchar2,
113 l_old_tax_amount in varchar2,
114 l_new_req_amount in varchar2,
115 l_new_tax_amount in varchar2,
116 l_note in varchar2) return varchar2;
117 */
118
119 /*************************************************************************
120 * Private Procedure: PrintHeading
121 *
122 * Effects: print the l_text in html header format
123 *
124 * Returns:
125 ************************************************************************/
126 function PrintHeading(l_text in varchar2) return varchar2;
127
128 /*************************************************************************
129 * Private Procedure: IsForeignCurrencyDisplayed
130 *
131 * Effects: check if the foreign currency need to be displayed in the
132 * line part of the notification
133 *
134 * Returns:
135 ************************************************************************/
136 function IsForeignCurrencyDisplayed (l_document_id in number,
137 l_display_txn_curr in varchar2,
138 l_currency_code in varchar2) return boolean;
139
140 function get_po_number(p_line_location_id in number) return varchar2;
141 function get_so_number(req_line_id NUMBER) RETURN VARCHAR2;
142 /*************************************************************************
143 * Private Procedure: GetChangeValues
144 *
145 * Effects: get the new value and old value of the req line which
146 * is displayed in the line details table
147 *
148 * Returns:
149 ************************************************************************/
150 procedure GetChangeValues(p_group_id in number,
151 p_req_line_id in number,
152 p_call_flag in varchar2,
153 p_old_need_by_date out NOCOPY date,
154 p_new_need_by_date out NOCOPY date,
155 p_is_need_by_changed out NOCOPY boolean,
156 p_old_quantity out NOCOPY number,
157 p_new_quantity out NOCOPY number,
158 p_is_quantity_changed out NOCOPY boolean,
159 p_old_currency_price out NOCOPY number,
160 p_new_currency_price out NOCOPY number,
161 p_old_price out NOCOPY number,
162 p_new_price out NOCOPY number,
163 p_is_price_changed out NOCOPY varchar2,
164 p_cancel out NOCOPY boolean,
165 p_change_reason out NOCOPY varchar2,
166 p_request_status out NOCOPY varchar2);
167
168 /*************************************************************************
169 * Private Procedure: GetPoLineShipment
170 *
171 * Effects: generate the line/shipment part of the po approval notification
172 *
173 * Returns:
174 ************************************************************************/
175 procedure GetPoLineShipment(l_line_num in number,
176 l_ship_num in number,
177 l_item_id in number,
178 l_org_id in number,
179 l_old_need_by_date in date,
180 l_new_need_by_date in date,
181 l_old_price in number,
182 l_new_price in number,
183 l_po_currency in varchar2,
184 l_old_qty in number,
185 l_new_qty in number,
186 l_action_type in varchar2,
187 l_item_desc in varchar2,
188 l_uom in varchar2,
189 l_ship_to_location in varchar2,
190 l_request_reason in varchar2,
191 l_old_start_date in date,
192 l_new_start_date in date,
193 l_old_end_date in date,
194 l_new_end_date in date,
195 l_old_amount in number,
196 l_new_amount in number,
197 l_has_temp_labor in boolean,
198 l_display_type in varchar2,
199 l_document out NOCOPY varchar2);
200
201
202
203 -- set context for calls to doc manager
204 procedure SetDocMgrContext(itemtype VARCHAR2, itemkey VARCHAR2);
205
206
207 TYPE line_record IS RECORD (
208
209 req_line_id po_requisition_lines.requisition_line_id%TYPE,
210 line_num po_requisition_lines.line_num%TYPE,
211 item_num mtl_system_items_kfv.concatenated_segments%TYPE,
212 item_revision po_requisition_lines.item_revision%TYPE,
213 item_desc po_requisition_lines.item_description%TYPE,
214 uom mtl_units_of_measure.unit_of_measure_tl%TYPE,
215 quantity po_requisition_lines.quantity%TYPE,
216 unit_price po_requisition_lines.unit_price%TYPE,
217 line_amount NUMBER,
218 need_by_date po_requisition_lines.need_by_date%TYPE,
219 location hr_locations.location_code%TYPE,
220 requestor per_people_f.full_name%TYPE,
221 sugg_supplier po_requisition_lines.suggested_vendor_name%TYPE,
222 sugg_site po_requisition_lines.suggested_vendor_location%TYPE,
223 txn_curr_code po_requisition_lines.currency_code%TYPE,
224 curr_unit_price po_requisition_lines.currency_unit_price%TYPE,
225 order_type po_lookup_codes.displayed_field%TYPE,
226 source_type_code po_requisition_lines.source_type_code%TYPE,
227 line_location_id po_requisition_lines.line_location_id%TYPE,
228 cancel_flag po_requisition_lines.cancel_flag%TYPE
229 );
230
231 TYPE history_record IS RECORD (
232
233 seq_num po_action_history_v.sequence_num%TYPE,
234 employee_name po_action_history_v.employee_name%TYPE,
235 action po_action_history_v.action_code_dsp%TYPE,
236 action_date po_action_history_v.action_date%TYPE,
237 note po_action_history_v.note%TYPE,
238 revision po_action_history_v.object_revision_num%TYPE);
239
240 L_TABLE_STYLE VARCHAR2(100) := ' style="border-collapse:collapse" cellpadding="1" cellspacing="0" border="0" width="100%" ';
241
242 L_TABLE_HEADER_STYLE VARCHAR2(100) := ' class="tableheader" style="border-left:1px solid #f7f7e7" ';
243
244 L_TABLE_LABEL_STYLE VARCHAR2(100) := ' class="tableheaderright" nowrap align=right style="border:1px solid #f7f7e7" ';
245
246 L_TABLE_CELL_STYLE VARCHAR2(100) := ' class="tabledata" nowrap align=left style="border:1px solid #cccc99" ';
247
248 L_TABLE_CELL_WRAP_STYLE VARCHAR2(100) := ' class="tabledata" align=left style="border:1px solid #cccc99" ';
249
250 L_TABLE_CELL_RIGHT_STYLE VARCHAR2(100) := ' class="tabledata" nowrap align=right style="border:1px solid #cccc99" ';
251
252 L_TABLE_CELL_HIGH_STYLE VARCHAR2(100) := ' class="tabledatahighlight" nowrap align=left style="border:1px solid #cccc99" ';
253
254
255
256 /*************************************************************************
257 * Public Procedure: Get_Req_Chg_Approval_Notif
258 *
259 * Effects: generate the req change approval notification
260 *
261 ************************************************************************/
262 PROCEDURE Get_Req_Chg_Approval_Notif(document_id in varchar2,
263 display_type in varchar2,
264 document in out nocopy clob,
265 document_type in out nocopy varchar2) IS
266 max_seqno number;
267 l_item_type wf_items.item_type%TYPE;
268 l_item_key wf_items.item_key%TYPE;
269
270 l_document_id po_requisition_headers.requisition_header_id%TYPE;
271 l_org_id po_requisition_headers.org_id%TYPE;
272 l_document_subtype po_lookup_codes.displayed_field%TYPE;
273 l_document_type po_lookup_codes.displayed_field%TYPE;
274 l_document_number po_requisition_headers.segment1%TYPE;
275 l_currency_code fnd_currencies.CURRENCY_CODE%TYPE;
276 l_change_request_group_id number;
277
278 l_old_req_amount VARCHAR2(30);
279 l_old_tax_amount VARCHAR2(30);
280 l_old_tax_amt NUMBER;
281 l_new_req_amount VARCHAR2(30);
282 l_new_tax_amt number;
283 l_new_tax_amount VARCHAR2(30);
284
285
286 l_note po_action_history.note%TYPE;
287
288 l_document VARCHAR2(32000) := '';
289 l_header_msg VARCHAR2(2225);
290 l_document_2 VARCHAR2(32000) := '';
291 l_document_3 VARCHAR2(32000) := '';
292
293
294 NL VARCHAR2(1) := fnd_global.newline;
295
296 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
297
298
299 BEGIN
300
301 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
302 l_item_key := substr(document_id, instr(document_id, ':') + 1,
303 length(document_id) - 2);
304
305 l_org_id := wf_engine.GetItemAttrNumber
306 (itemtype => l_item_type,
307 itemkey => l_item_key,
308 aname => 'ORG_ID');
309
310 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
311
312 l_document_id := wf_engine.GetItemAttrNumber
313 (itemtype => l_item_type,
314 itemkey => l_item_key,
315 aname => 'DOCUMENT_ID');
316 l_change_request_group_id := wf_engine.GetItemAttrNumber
317 (itemtype => l_item_type,
318 itemkey => l_item_key,
319 aname => 'CHANGE_REQUEST_GROUP_ID');
320
321 /*
322 l_currency_code := wf_engine.GetItemAttrText
323 (itemtype => l_item_type,
324 itemkey => l_item_key,
325 aname => 'FUNCTIONAL_CURRENCY');
326
327 l_old_req_amount := wf_engine.GetItemAttrText
328 (itemtype => l_item_type,
329 itemkey => l_item_key,
330 aname => 'REQ_AMOUNT_CURRENCY_DSP');
331
332 l_old_tax_amount := wf_engine.GetItemAttrText
333 (itemtype => l_item_type,
334 itemkey => l_item_key,
335 aname => 'TAX_AMOUNT_CURRENCY_DSP');
336
337 l_new_req_amount := wf_engine.GetItemAttrText
338 (itemtype => l_item_type,
339 itemkey => l_item_key,
340 aname => 'NEW_REQ_AMOUNT_CURRENCY_DSP');
341
342 l_new_tax_amount := wf_engine.GetItemAttrText
343 (itemtype => l_item_type,
344 itemkey => l_item_key,
345 aname => 'NEW_TAX_AMOUNT_CURRENCY_DSP');
346
347 l_note := PO_WF_UTIL_PKG.GetItemAttrText
348 (itemtype => l_item_type,
349 itemkey => l_item_key,
350 aname => 'JUSTIFICATION');
351
352 if l_note is null then
353
354 l_note := wf_engine.GetItemAttrText
355 (itemtype => l_item_type,
356 itemkey => l_item_key,
357 aname => 'NOTE');
358
359 end if;
360
361 select to_char(sum(decode(pcr1.action_type, 'CANCELLATION', 0, nvl(pcr1.new_price, prl.unit_price)*
362 nvl(pcr2.new_quantity,prd.req_line_quantity)*prd.nonrecoverable_tax
363 /(prl.unit_price*prd.req_line_quantity))), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
364 into l_new_tax_amount
365 from po_requisition_lines_all prl,
366 po_req_distributions_all prd,
367 po_change_requests pcr1,
368 po_change_requests pcr2
369 where prl.requisition_line_id=pcr1.document_line_id(+)
370 and pcr1.change_request_group_id(+)=l_change_request_group_id
371 and pcr1.request_level(+)='LINE'
372 and prl.requisition_line_id=prd.requisition_line_id
373 and nvl(prd.nonrecoverable_tax, 0) >0
374 and prd.distribution_id=pcr2.document_distribution_id(+)
375 and pcr2.change_request_group_id(+)=l_change_request_group_id
376 and prl.requisition_header_id=l_document_id
377 AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
378 and NVL(prl.cancel_flag, 'N')='N';
379
380 select to_char(sum(decode(pcr1.action_type, 'CANCELLATION', 0, nvl(pcr1.new_price, prl.unit_price)*
381 nvl(pcr2.new_quantity,prd.req_line_quantity))),
382 FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
383 into l_new_req_amount
384 from po_requisition_lines_all prl,
385 po_req_distributions_all prd,
386 po_change_requests pcr1,
387 po_change_requests pcr2
388 where prl.requisition_line_id=pcr1.document_line_id(+)
389 and pcr1.change_request_group_id(+)=l_change_request_group_id
390 and pcr1.request_level(+)='LINE'
391 and prl.requisition_line_id=prd.requisition_line_id
392 and prd.distribution_id=pcr2.document_distribution_id(+)
393 and pcr2.change_request_group_id(+)=l_change_request_group_id
394 and prl.requisition_header_id=l_document_id
395 AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
396 and NVL(prl.cancel_flag, 'N')='N';
397
398 */
399
400 if (display_type = 'text/html') then
401
402
403 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_href || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
404 if(wf_core.translate('WF_HEADER_ATTR') <> 'Y') then
405 l_document := l_document || ConstructHeaderInfo(l_item_type,
406 l_item_key, l_change_request_group_id,
407 l_document_id, 'A');
408
409 WF_NOTIFICATION.WriteToClob(document,l_document);
410 end if;
411
412 l_document_3 := NULL;
413
414 GetReqLinesDetailsLink(document_id, display_type, l_document_3, document_type);
415
416 WF_NOTIFICATION.WriteToClob(document,l_document_3);
417 l_document_2 := NULL;
418
419 GetActionHistoryHtml(document_id, display_type, l_document_2, document_type);
420
421 WF_NOTIFICATION.WriteToClob(document,l_document_2||NL);
422 -- l_document := l_document || l_document_3 || l_document_2 || NL ;
423
424 else -- Text message
425 null;
426 -- todo after a text version
427 end if;
428
429 END Get_Req_Chg_Approval_Notif;
430
431 /*************************************************************************
432 * Public Procedure: Get_Req_Chg_Response_Notif
433 *
434 * Effects: generate the notification to requester about the response
435 * to the change request
436 *
437 ************************************************************************/
438 PROCEDURE Get_Req_Chg_Response_Notif(document_id in varchar2,
439 display_type in varchar2,
440 document in out nocopy clob,
441 document_type in out nocopy varchar2) IS
442 max_seqno number;
443 l_item_type wf_items.item_type%TYPE;
444 l_item_key wf_items.item_key%TYPE;
445
446 l_change_request_group_id number;
447
448 l_document_id po_requisition_headers.requisition_header_id%TYPE;
449 l_org_id po_requisition_headers.org_id%TYPE;
450 l_document_subtype po_lookup_codes.displayed_field%TYPE;
451 l_document_type po_lookup_codes.displayed_field%TYPE;
452 l_document_number po_requisition_headers.segment1%TYPE;
453 l_currency_code fnd_currencies.CURRENCY_CODE%TYPE;
454
455 l_old_req_amount VARCHAR2(30);
456 l_old_tax_amount VARCHAR2(30);
457 l_new_req_amount VARCHAR2(30);
458 l_new_tax_amount VARCHAR2(30);
459
460
461 l_note po_action_history.note%TYPE;
462
463 l_document VARCHAR2(32000) := '';
464 l_header_msg VARCHAR2(2225);
465 l_document_2 VARCHAR2(32000) := '';
466 l_document_3 VARCHAR2(32000) := '';
467
468
469 NL VARCHAR2(1) := fnd_global.newline;
470
471 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
472
473 cursor l_get_wf_keys_csr is
474 select wf_item_type, wf_item_key
475 from po_change_requests
476 where change_request_group_id=l_change_request_group_id;
477
478
479 BEGIN
480 -- WF_NOTIFICATION.WriteToClob(document,'<table> <th><td> aaa</td></th></table>');
481 l_change_request_group_id :=to_number(document_id);
482 open l_get_wf_keys_csr;
483 fetch l_get_wf_keys_csr into l_item_type, l_item_key;
484 close l_get_wf_keys_csr;
485
486 l_org_id := wf_engine.GetItemAttrNumber
487 (itemtype => l_item_type,
488 itemkey => l_item_key,
489 aname => 'ORG_ID');
490
491 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
492
493 l_document_id := wf_engine.GetItemAttrNumber
494 (itemtype => l_item_type,
495 itemkey => l_item_key,
496 aname => 'DOCUMENT_ID');
497 /*
498 l_currency_code := wf_engine.GetItemAttrText
499 (itemtype => l_item_type,
500 itemkey => l_item_key,
501 aname => 'FUNCTIONAL_CURRENCY');
502
503 l_old_req_amount := wf_engine.GetItemAttrText
504 (itemtype => l_item_type,
505 itemkey => l_item_key,
506 aname => 'REQ_AMOUNT_DSP');
507
508 l_old_tax_amount := wf_engine.GetItemAttrText
509 (itemtype => l_item_type,
510 itemkey => l_item_key,
511 aname => 'TAX_AMOUNT_DSP');
512
513 l_note := PO_WF_UTIL_PKG.GetItemAttrText
514 (itemtype => l_item_type,
515 itemkey => l_item_key,
516 aname => 'JUSTIFICATION');
517
518 if l_note is null then
519
520 l_note := wf_engine.GetItemAttrText
521 (itemtype => l_item_type,
522 itemkey => l_item_key,
523 aname => 'NOTE');
524
525 end if;
526
527 SELECT to_char(nvl(sum(nonrecoverable_tax), 0), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
528 INTO l_new_tax_amount
529 FROM po_requisition_lines rl,
530 po_req_distributions rd
531 WHERE rl.requisition_header_id = l_document_id
532 AND rd.requisition_line_id = rl.requisition_line_id
533 AND NVL(rl.modified_by_agent_flag, 'N') = 'N'
534 and NVL(rl.cancel_flag, 'N')='N';
535
536 SELECT to_char(nvl(SUM(quantity * unit_price), 0), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
537 into l_new_req_amount
538 FROM po_requisition_lines
539 WHERE requisition_header_id = l_document_id
540 AND NVL(cancel_flag,'N') = 'N'
541 AND NVL(modified_by_agent_flag, 'N') = 'N';
542 */
543
544 if (display_type = 'text/html') then
545
546
547 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_href || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
548 if(wf_core.translate('WF_HEADER_ATTR') <> 'Y') then
549 l_document := l_document || ConstructHeaderInfo(l_item_type,
550 l_item_key, l_change_request_group_id,
551 l_document_id, 'R');
552 WF_NOTIFICATION.WriteToClob(document,l_document);
553 end if;
554
555 l_document_3 := NULL;
556
557 GetReqLinesResponse(document_id, display_type, l_document_3, document_type);
558 WF_NOTIFICATION.WriteToClob(document,l_document_3);
559
560 l_document_2 := NULL;
561
562 GetActionHistoryHtml(l_item_type||':'||l_item_key, display_type, l_document_2, document_type);
563
564 WF_NOTIFICATION.WriteToClob(document,l_document_2||NL);
565 -- l_document := l_document || l_document_3 || l_document_2 || NL ;
566
567 else -- Text message
568 null;
569 -- todo after a text version
570 end if;
571
572 -- document := l_document;
573
574 END Get_Req_Chg_Response_Notif;
575
576
577 /*************************************************************************
578 * Private Procedure: GetReqLinesDetailsLink
579 *
580 * Effects: generate the line part of the req change approval
581 * notification
582 *
583 * Returns:
584 ************************************************************************/
585 PROCEDURE GetReqLinesDetailsLink(document_id in varchar2,
586 display_type in varchar2,
587 document in out NOCOPY varchar2,
588 document_type in out NOCOPY varchar2) IS
589 nsegments number;
590 l_segments fnd_flex_ext.SegmentArray;
591 l_cost_center VARCHAR2(200);
592 l_segment_num number;
593 l_column_name VARCHAR2(20);
594 l_link_url varchar2(4000);
595
596 cc_Id number;
597
598 cost_center_1 VARCHAR2(200);
599
600 l_account_id number;
601 dist_num number;
602 multiple_cost_center VARCHAR2(100):= '';
603
604
605 l_item_type wf_items.item_type%TYPE;
606 l_item_key wf_items.item_key%TYPE;
607
608 l_document_id po_requisition_lines.requisition_header_id%TYPE;
609 l_org_id po_requisition_lines.org_id%TYPE;
610
611 l_line line_record;
612
613 l_num_lines NUMBER := 0;
614
615 l_max_lines NUMBER := 0;
616
617 l_document VARCHAR2(32000) := '';
618
619 l_req_status po_requisition_headers.authorization_status%TYPE;
620
621 l_req_line_msg VARCHAR2(2000) := '';
622
623 l_currency_code fnd_currencies.currency_code%TYPE;
624
625 NL VARCHAR2(1) := fnd_global.newline;
626
627 i number := 0;
628
629 l_group_id number := 0;
630 l_new_need_by_date date;
631 l_is_need_by_changed boolean;
632 l_new_quantity number;
633 l_is_quantity_changed boolean;
634 l_new_currency_price number;
635 l_new_price number;
636 l_is_price_changed varchar2(10);
637 l_cancel boolean;
638 l_display_currency_price_cell boolean;
639 l_new_line_amount number;
640 l_order_num varchar2(40);
641 l_change_reason po_change_requests.request_reason%type;
642 l_cancel_display FND_LOOKUPS.MEANING%type;
643
644 l_old_quantity number;
645 l_old_price number;
646 l_old_currency_price number;
647 l_old_need_by_date date;
648 l_old_line_amount number;
649 l_request_status po_change_requests.request_status%type;
650
651 display_txn_curr VARCHAR2(30);
652 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
653
654
655 CURSOR line_csr(v_document_id NUMBER) IS
656 SELECT rql.requisition_line_id,
657 rql.line_num,
658 msi.concatenated_segments,
659 rql.item_revision,
660 rql.item_description,
661 nvl(muom.unit_of_measure_tl, rql.unit_meas_lookup_code),
662 rql.quantity,
663 rql.unit_price,
664 rql.quantity * rql.unit_price,
665 rql.need_by_date,
666 hrt.location_code,
667 per.full_name,
668 decode(rql.source_type_code,'VENDOR', rql.suggested_vendor_name, org.organization_code ||' - '||
669 org.organization_name),
670 decode(rql.source_type_code, 'VENDOR',rql.suggested_vendor_location,''),
671 rql.currency_code,
672 rql.currency_unit_price,
673 PLC.DISPLAYED_FIELD,
674 rql.source_type_code,
675 rql.line_location_id,
676 rql.cancel_flag
677 FROM po_requisition_lines rql,
678 mtl_system_items_kfv msi,
679 hr_locations_all hrt,
680 per_all_people_f per,
681 mtl_units_of_measure muom,
682 org_organization_definitions org,
683 PO_LOOKUP_CODES PLC
684 WHERE rql.requisition_header_id = v_document_id
685 AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
686 AND hrt.location_id (+) = rql.deliver_to_location_id
687 AND rql.item_id = msi.inventory_item_id(+)
688 AND nvl(msi.organization_id, rql.destination_organization_id) =
689 rql.destination_organization_id
690 AND rql.to_person_id = per.person_id(+)
691 AND per.effective_start_date(+) <= trunc(sysdate)
692 AND per.effective_end_date(+) >= trunc(sysdate)
693 AND rql.source_organization_id = org.organization_id (+)
694 AND muom.unit_of_measure = rql.unit_meas_lookup_code -- bug 2401933.add
695 AND PLC.LOOKUP_TYPE = 'REQUISITION TYPE'
696 AND PLC.LOOKUP_CODE = DECODE(RQL.SOURCE_TYPE_CODE,'VENDOR','PURCHASE','INTERNAL')
697 ORDER BY rql.line_num;
698
699
700 CURSOR ccId_csr(req_line_id NUMBER) IS
701 SELECT CODE_COMBINATION_ID
702 FROM PO_REQ_DISTRIBUTIONS_ALL
703 WHERE REQUISITION_LINE_ID = req_line_id;
704
705
706 BEGIN
707
708 select meaning
709 into l_cancel_display
710 from FND_LOOKUPS
711 where lookup_type='YES_NO'
712 and lookup_code='Y';
713
714 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
715 l_item_key := substr(document_id, instr(document_id, ':') + 1,
716 length(document_id) - 2);
717
718 l_group_id := wf_engine.GetItemAttrNumber
719 (itemtype => l_item_type,
720 itemkey => l_item_key,
721 aname => 'CHANGE_REQUEST_GROUP_ID');
722
723 l_document_id := wf_engine.GetItemAttrNumber
724 (itemtype => l_item_type,
725 itemkey => l_item_key,
726 aname => 'DOCUMENT_ID');
727
728 l_org_id := wf_engine.GetItemAttrNumber
729 (itemtype => l_item_type,
730 itemkey => l_item_key,
731 aname => 'ORG_ID');
732
733 SetDocMgrContext(l_item_type, l_item_key);
734
735 display_txn_curr := FND_PROFILE.value('POR_DEFAULT_DISP_TRANS_CURRENCY');
736
737
738 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
739
740 l_currency_code := PO_CORE_S2.get_base_currency;
741
742 l_display_currency_price_cell := IsForeignCurrencyDisplayed (l_document_id, display_txn_curr, l_currency_code);
743
744 multiple_cost_center := fnd_message.get_string('PO', 'PO_WF_NOTIF_MULTIPLE');
745
746 begin
747
748 select fs.segment_num, gls.chart_of_accounts_id
749 into l_segment_num, l_account_id
750 from FND_ID_FLEX_SEGMENTS fs,
751 fnd_segment_attribute_values fsav,
752 financials_system_parameters fsp,
753 gl_sets_of_books gls
754 where fsp.set_of_books_id = gls.set_of_books_id and
755 fsav.id_flex_num = gls.chart_of_accounts_id and
756 fsav.id_flex_code = 'GL#' and
757 fsav.application_id = 101 and
758 fsav.segment_attribute_type = 'FA_COST_CTR' and
759 fsav.id_flex_num = fs.id_flex_num and
760 fsav.id_flex_code = fs.id_flex_code and
761 fsav.application_id = fs.application_id and
762 fsav.application_column_name = fs.application_column_name and
763 fsav.attribute_value='Y';
764
765 exception
766 when others then
767 l_segment_num := -1;
768 end;
769
770 if (display_type = 'text/html') then
771
772
773 l_document := l_document || NL || NL || '<!-- CHANGE REQ_LINE_DETAILS -->'|| NL || NL || '<P>';
774
775 l_document := l_document || PrintHeading(fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS'));
776
777 l_max_lines := to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
778
779 select count(1)
780 into l_num_lines
781 from po_requisition_lines
782 where requisition_header_id = l_document_id;
783
784 l_document := l_document || '<TABLE width="100%" SUMMARY="">' || NL;
785
786 l_document := l_document || '<TR>'|| NL;
787
788 l_req_line_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_VALUE');
789
790 l_req_line_msg := replace(l_req_line_msg, '&LIMIT', to_char(l_max_lines));
791
792 l_req_line_msg := '<TD class=instructiontext>'||'<img src='
793 || l_base_href
794 || '/OA_MEDIA/newupdateditem_status.gif ALT="">'|| l_req_line_msg;
795
796 l_document := l_document || l_req_line_msg || NL ;
797
798 l_req_line_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_CANCELLED_LINES');
799 l_req_line_msg := '<br>'||'<img src='
800 || l_base_href
801 || '/OA_MEDIA/cancelind_status.gif ALT="">'|| l_req_line_msg;
802 l_document := l_document || l_req_line_msg || NL ;
803
804 l_document := l_document || '</TD></TR>' || NL;
805
806 l_document := l_document || '</TABLE>' || NL;
807
808
809 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || 'summary="' || fnd_message.get_string('ICX','ICX_POR_TBL_REQ_TO_APPROVE_SUM') || '"> '|| NL;
810
811 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=3% id="lineNum_1">' ||
812 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TH>' || NL;
813
814 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=25% id="itemDesc_1">' ||
815 fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || '</TH>' || NL;
816
817 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=15% id="supplier_1">' ||
818 fnd_message.get_string('PO', 'PO_WF_NOTIF_SUPPLIER') || '</TH>' || NL;
819
820 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="costCenter_1">' ||
821 fnd_message.get_string('PO', 'PO_WF_NOTIF_COST_CENTER') || '</TH>' || NL;
822
823 --here added order type, order, need-by
824 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="orderType_1">' ||
825 fnd_message.get_string('PO', 'PO_WF_NOTIF_ORDER_TYPE') || '</TH>' || NL;
826
827 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="order_1">' ||
828 fnd_message.get_string('PO', 'PO_WF_NOTIF_ORDER') || '</TH>' || NL;
829
830 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="needBy_1">' ||
831 fnd_message.get_string('PO', 'PO_WF_NOTIF_NEED_BY') || '</TH>' || NL;
832
833 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="UOM_1">' ||
834 fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT') || '</TH>' || NL;
835
836 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=8% id="quant_1">' ||
837 fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TH>' || NL;
838
839 if(l_display_currency_price_cell) then
840 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=8% id="quant_1">' ||
841 fnd_message.get_string('PO', 'PO_WF_NOTIF_TRANS_PRICE') || '</TH>' || NL;
842 end if;
843
844 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="unitPrice_1">' ||
845 fnd_message.get_string('PO', 'PO_WF_NOTIF_PRICE')||
846 ' (' || l_currency_code || ')' || '</TH>' || NL;
847
848 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% nowrap id="lineAmt_1">' ||
849 fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT') ||
850 ' (' || l_currency_code || ')' || '</TH>' || NL;
851
852 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="cancel_1">' ||
853 fnd_message.get_string('PO', 'PO_WF_NOTIF_CANCEL') || '</TH>' || NL;
854
855 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="reason_1">' ||
856 fnd_message.get_string('PO', 'PO_WF_NOTIF_REASON') || '</TH>' || NL;
857
858 l_document := l_document || '</TR>' || NL;
859
860 open line_csr(l_document_id);
861
862 loop
863
864 fetch line_csr into l_line;
865 i := i + 1;
866
867 exit when line_csr%notfound;
868
869 if(l_line.source_type_code = 'VENDOR') then
870 l_order_num := get_po_number(l_line.line_location_id);
871 else
872 l_order_num := get_so_number(l_line.req_line_id);
873 end if;
874
875 l_is_need_by_changed:=false;
876 l_is_quantity_changed:=false;
877 l_is_price_changed:='NO';
878 l_cancel:=false;
879 l_change_reason:=null;
880 GetChangeValues(l_group_id,
881 l_line.req_line_id,
882 'APPROVE',
883 l_old_need_by_date,
884 l_new_need_by_date,
885 l_is_need_by_changed,
886 l_old_quantity,
887 l_new_quantity,
888 l_is_quantity_changed,
889 l_old_currency_price,
890 l_new_currency_price,
891 l_old_price,
892 l_new_price,
893 l_is_price_changed,
894 l_cancel,
895 l_change_reason,
896 l_request_status);
897
898
899 begin
900
901 if l_segment_num = -1 then
902 l_cost_center := '';
903 else
904
905 l_cost_center := 'SINGLE';
906
907 dist_num := 1;
908
909 open ccId_csr(l_line.req_line_id);
910 loop
911 fetch ccId_csr into cc_Id;
912 exit when ccid_csr%notfound;
913
914 if fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) then
915 l_cost_center := l_segments(l_segment_num);
916 else
917 l_cost_center := '';
918 end if;
919
920 if dist_num = 1 then
921 cost_center_1 := l_cost_center;
922 dist_num := 2;
923 else
924 if l_cost_center <> cost_center_1 then
925 l_cost_center := multiple_cost_center;
926 exit;
927 end if;
928 end if;
929 end loop;
930 close ccId_csr;
931
932 if l_cost_center <> multiple_cost_center then
933 if fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) then
934 l_cost_center := l_segments(l_segment_num);
935 else
936 l_cost_center := '';
937 end if;
938 end if;
939
940 end if; --if l_segment_num = -1
941
942 exception --any exception while retrieving the cost center
943 when others then
944 l_cost_center := '';
945 end;
946
947
948 l_document := l_document || '<TR>' || NL;
949
950 if(l_line.cancel_flag='Y') then
951 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineNum_1">' ||
952 '<img src='||l_base_href||'/OA_MEDIA/cancelind_status.gif ALT="">' ||
953 nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
954 else
955 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineNum_1">' ||
956 nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
957 end if;
958
959 l_document := l_document || '<TD ' || L_TABLE_CELL_WRAP_STYLE || ' headers="itemDesc_1">' ||
960 nvl(l_line.item_desc, ' ') || '</TD>' || NL;
961
962 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="supplier_1">' ||
963 nvl(l_line.sugg_supplier, ' ') || '</TD>' || NL;
964
965 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="costCenter_1">' ||
966 nvl(l_cost_center, ' ') || '</TD>' || NL;
967
968 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="orderType_1">' ||
969 nvl(l_line.order_type, ' ') || '</TD>' || NL;
970
971 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="order_1">' ||
972 nvl(l_order_num, ' ') || '</TD>' || NL;
973 if (l_is_need_by_changed = true) then
974 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="needBy_1">' ||
975 nvl(to_char(l_old_need_by_date), ' ') || '<BR>' ||
976 nvl(to_char(l_new_need_by_date), ' ') || '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
977
978 else
979 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="needBy_1">' ||
980 nvl(to_char(l_line.need_by_date), ' ') || '</TD>' || NL;
981 end if;
982
983 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="UOM_1">' ||
984 nvl(l_line.uom, ' ') || '</TD>' || NL;
985
986 if (l_is_quantity_changed = true) then
987 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="quant_1">' ||
988 nvl(to_char(l_old_quantity), ' ') || '<BR>' ||
989 nvl(to_char(l_new_quantity), ' ')|| '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
990 else
991 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="quant_1">' ||
992 nvl(to_char(l_line.quantity), ' ') || '</TD>' || NL;
993 end if;
994
995 -- fix bug 2739962, display the price in format
996 IF (l_display_currency_price_cell) THEN
997 if ( l_line.txn_curr_code is not null AND
998 l_currency_code <> l_line.txn_curr_code) then
999
1000 if (l_is_price_changed = 'YES') then
1001 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1002 to_char(l_old_currency_price, FND_CURRENCY.GET_FORMAT_MASK(l_line.txn_curr_code, 30)) || ' ' || l_line.txn_curr_code || '<BR>' ||
1003 to_char(l_new_currency_price, FND_CURRENCY.GET_FORMAT_MASK(l_line.txn_curr_code, 30)) || ' ' || l_line.txn_curr_code ||
1004 '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
1005 elsif(l_is_price_changed='DERIVED') then
1006 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1007 to_char(l_new_currency_price, FND_CURRENCY.GET_FORMAT_MASK(l_line.txn_curr_code, 30)) || ' ' || l_line.txn_curr_code ||
1008 '</TD>' || NL;
1009 else
1010 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1011 to_char(l_line.curr_unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_line.txn_curr_code, 30)) || ' ' || l_line.txn_curr_code ||
1012 '</TD>' || NL;
1013
1014 end if;
1015 else --display a blank cell
1016 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
1017 || ' headers="unitPrice_1"> </TD>' || NL;
1018 end if;
1019 END IF;
1020
1021 if (l_is_price_changed = 'YES') then
1022 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1023 to_char(l_old_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || '<BR>' ||
1024 to_char(l_new_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1025 '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
1026 elsif(l_is_price_changed='DERIVED') then
1027 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1028 to_char(l_new_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1029 '</TD>' || NL;
1030 else
1031 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1032 to_char(l_line.unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1033 '</TD>' || NL;
1034 end if;
1035
1036 l_old_line_amount:=l_line.line_amount;
1037 if (l_is_price_changed in ('DERIVED', 'YES') or l_is_quantity_changed = true) then
1038 if(l_is_price_changed in ('DERIVED', 'YES') and l_is_quantity_changed = true) then
1039 l_new_line_amount:=l_new_price*l_new_quantity;
1040 elsif(l_is_price_changed in ('DERIVED', 'YES'))then
1041 l_new_line_amount:=l_new_price*l_line.quantity;
1042 else
1043 l_new_line_amount:=l_line.unit_price*l_new_quantity;
1044 end if;
1045 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineAmt_1">' ||
1046 TO_CHAR(l_old_line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || '<BR>' ||
1047 TO_CHAR(l_new_line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1048 '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
1049 else
1050 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineAmt_1">' ||
1051 TO_CHAR(l_old_line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1052 '</TD>' || NL;
1053 end if;
1054
1055 if(l_cancel=true) then
1056 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="cancel_1">' ||
1057 nvl(l_cancel_display, ' ') || '</TD>' || NL;
1058 else
1059 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="cancel_1"> </TD>' || NL;
1060 end if;
1061 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="reason_1">' ||
1062 nvl(l_change_reason, ' ') || '</TD>' || NL;
1063
1064 l_document := l_document || '</TR>' || NL;
1065
1066
1067 exit when i = l_max_lines;
1068 end loop;
1069
1070 close line_csr;
1071
1072 l_document := l_document || '</TABLE>';
1073
1074 /*
1075 get_additional_details(l_document_id, l_link_url);
1076
1077 l_document:=l_document||l_link_url;
1078 */
1079
1080
1081 end if;
1082
1083 document := l_document;
1084
1085 END GetReqLinesDetailsLink;
1086
1087
1088 /*************************************************************************
1089 * Private Procedure: GetReqLinesResponse
1090 *
1091 * Effects: generate the line part of the change response
1092 * notification
1093 *
1094 * Returns:
1095 ************************************************************************/
1096 PROCEDURE GetReqLinesResponse(document_id in varchar2,
1097 display_type in varchar2,
1098 document in out NOCOPY varchar2,
1099 document_type in out NOCOPY varchar2) IS
1100 nsegments number;
1101 l_segments fnd_flex_ext.SegmentArray;
1102 l_cost_center VARCHAR2(200);
1103 l_segment_num number;
1104 l_column_name VARCHAR2(20);
1105
1106 l_link_url varchar2(4000);
1107
1108 cc_Id number;
1109
1110 cost_center_1 VARCHAR2(200);
1111
1112 l_account_id number;
1113 dist_num number;
1114 multiple_cost_center VARCHAR2(100):= '';
1115
1116
1117 l_item_type wf_items.item_type%TYPE;
1118 l_item_key wf_items.item_key%TYPE;
1119
1120 l_document_id po_requisition_lines.requisition_header_id%TYPE;
1121 l_org_id po_requisition_lines.org_id%TYPE;
1122
1123 l_line line_record;
1124
1125 l_num_lines NUMBER := 0;
1126
1127 l_max_lines NUMBER := 0;
1128
1129 l_document VARCHAR2(32000) := '';
1130
1131 l_req_status po_requisition_headers.authorization_status%TYPE;
1132
1133 l_req_line_msg VARCHAR2(2000) := '';
1134
1135 l_currency_code fnd_currencies.currency_code%TYPE;
1136
1137 NL VARCHAR2(1) := fnd_global.newline;
1138
1139 i number := 0;
1140
1141 l_group_id number := 0;
1142 l_new_need_by_date date;
1143 l_is_need_by_changed boolean;
1144 l_new_quantity number;
1145 l_is_quantity_changed boolean;
1146 l_new_currency_price number;
1147 l_new_price number;
1148 l_is_price_changed varchar2(10);
1149 l_cancel boolean;
1150 l_display_currency_price_cell boolean;
1151 l_new_line_amount number;
1152 l_order_num varchar2(40);
1153 l_change_reason po_change_requests.request_reason%type;
1154
1155 l_old_quantity number;
1156 l_old_price number;
1157 l_old_currency_price number;
1158 l_old_need_by_date date;
1159 l_old_line_amount number;
1160 l_request_status po_change_requests.request_status%type;
1161
1162 display_txn_curr VARCHAR2(30);
1163 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
1164
1165
1166 CURSOR line_csr(v_document_id NUMBER) IS
1167 SELECT rql.requisition_line_id,
1168 rql.line_num,
1169 msi.concatenated_segments,
1170 rql.item_revision,
1171 rql.item_description,
1172 nvl(muom.unit_of_measure_tl, rql.unit_meas_lookup_code),
1173 rql.quantity,
1174 rql.unit_price,
1175 rql.quantity * rql.unit_price,
1176 rql.need_by_date,
1177 hrt.location_code,
1178 per.full_name,
1179 decode(rql.source_type_code,'VENDOR', rql.suggested_vendor_name, org.organization_code ||' - '||
1180 org.organization_name),
1181 decode(rql.source_type_code, 'VENDOR',rql.suggested_vendor_location,''),
1182 rql.currency_code,
1183 rql.currency_unit_price,
1184 PLC.DISPLAYED_FIELD,
1185 rql.source_type_code,
1186 rql.line_location_id,
1187 rql.cancel_flag
1188 FROM po_requisition_lines rql,
1189 mtl_system_items_kfv msi,
1190 hr_locations_all hrt,
1191 per_all_people_f per,
1192 mtl_units_of_measure muom,
1193 org_organization_definitions org,
1194 PO_LOOKUP_CODES PLC
1195 WHERE rql.requisition_header_id = v_document_id
1196 AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
1197 AND hrt.location_id (+) = rql.deliver_to_location_id
1198 AND rql.item_id = msi.inventory_item_id(+)
1199 AND nvl(msi.organization_id, rql.destination_organization_id) =
1200 rql.destination_organization_id
1201 AND rql.to_person_id = per.person_id(+)
1202 AND per.effective_start_date(+) <= trunc(sysdate)
1203 AND per.effective_end_date(+) >= trunc(sysdate)
1204 AND rql.source_organization_id = org.organization_id (+)
1205 AND muom.unit_of_measure = rql.unit_meas_lookup_code -- bug 2401933.add
1206 AND PLC.LOOKUP_TYPE = 'REQUISITION TYPE'
1207 AND PLC.LOOKUP_CODE = DECODE(RQL.SOURCE_TYPE_CODE,'VENDOR','PURCHASE','INTERNAL')
1208 ORDER BY rql.line_num;
1209
1210
1211 CURSOR ccId_csr(req_line_id NUMBER) IS
1212 SELECT CODE_COMBINATION_ID
1213 FROM PO_REQ_DISTRIBUTIONS_ALL
1214 WHERE REQUISITION_LINE_ID = req_line_id;
1215
1216 cursor l_get_wf_keys_csr is
1217 select wf_item_type, wf_item_key
1218 from po_change_requests
1219 where change_request_group_id=l_group_id;
1220
1221 l_cancel_display FND_LOOKUPS.MEANING%type;
1222
1223 BEGIN
1224 select meaning
1225 into l_cancel_display
1226 from FND_LOOKUPS
1227 where lookup_type='YES_NO'
1228 and lookup_code='Y';
1229
1230 l_group_id :=to_number(document_id);
1231 open l_get_wf_keys_csr;
1232 fetch l_get_wf_keys_csr into l_item_type, l_item_key;
1233 close l_get_wf_keys_csr;
1234
1235 l_document_id := wf_engine.GetItemAttrNumber
1236 (itemtype => l_item_type,
1237 itemkey => l_item_key,
1238 aname => 'DOCUMENT_ID');
1239
1240 l_org_id := wf_engine.GetItemAttrNumber
1241 (itemtype => l_item_type,
1242 itemkey => l_item_key,
1243 aname => 'ORG_ID');
1244
1245 SetDocMgrContext(l_item_type, l_item_key);
1246
1247 display_txn_curr := FND_PROFILE.value('POR_DEFAULT_DISP_TRANS_CURRENCY');
1248
1249 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1250
1251 l_currency_code := PO_CORE_S2.get_base_currency;
1252
1253 l_display_currency_price_cell := IsForeignCurrencyDisplayed (l_document_id, display_txn_curr, l_currency_code);
1254
1255 multiple_cost_center := fnd_message.get_string('PO', 'PO_WF_NOTIF_MULTIPLE');
1256
1257 begin
1258
1259 select fs.segment_num, gls.chart_of_accounts_id
1260 into l_segment_num, l_account_id
1261 from FND_ID_FLEX_SEGMENTS fs,
1262 fnd_segment_attribute_values fsav,
1263 financials_system_parameters fsp,
1264 gl_sets_of_books gls
1265 where fsp.set_of_books_id = gls.set_of_books_id and
1266 fsav.id_flex_num = gls.chart_of_accounts_id and
1267 fsav.id_flex_code = 'GL#' and
1268 fsav.application_id = 101 and
1269 fsav.segment_attribute_type = 'FA_COST_CTR' and
1270 fsav.id_flex_num = fs.id_flex_num and
1271 fsav.id_flex_code = fs.id_flex_code and
1272 fsav.application_id = fs.application_id and
1273 fsav.application_column_name = fs.application_column_name and
1274 fsav.attribute_value='Y';
1275
1276 exception
1277 when others then
1278 l_segment_num := -1;
1279 end;
1280
1281 if (display_type = 'text/html') then
1282
1283
1284 l_document := l_document || NL || NL || '<!-- CHANGE REQ_LINE_DETAILS -->'|| NL || NL || '<P>';
1285
1286 l_document := l_document || PrintHeading(fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS'));
1287
1288 l_max_lines := to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
1289
1290 select count(1)
1291 into l_num_lines
1292 from po_requisition_lines
1293 where requisition_header_id = l_document_id;
1294
1295 l_document := l_document || '<TABLE width="100%" SUMMARY="">' || NL;
1296
1297 l_document := l_document || '<TR>'|| NL;
1298
1299 l_req_line_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_VALUE');
1300
1301 l_req_line_msg := replace(l_req_line_msg, '&LIMIT', to_char(l_max_lines));
1302
1303 l_req_line_msg := '<TD class=instructiontext>'||'<img src='||l_base_href|| '/OA_MEDIA/newupdateditem_status.gif ALT="">'|| l_req_line_msg;
1304
1305 l_document := l_document || l_req_line_msg || NL ;
1306
1307 l_req_line_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_CANCELLED_LINES');
1308 l_req_line_msg := '<br>'||'<img src='
1309 || l_base_href
1310 || '/OA_MEDIA/cancelind_status.gif ALT="">'|| l_req_line_msg;
1311 l_document := l_document || l_req_line_msg || NL ;
1312
1313 l_document := l_document || '</TD></TR>' || NL;
1314
1315 l_document := l_document || '</TABLE>' || NL;
1316
1317
1318 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || 'summary="' || fnd_message.get_string('ICX','ICX_POR_TBL_REQ_TO_APPROVE_SUM') || '"> '|| NL;
1319
1320 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=3% id="lineNum_1">' ||
1321 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TH>' || NL;
1322
1323 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=25% id="itemDesc_1">' ||
1324 fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || '</TH>' || NL;
1325
1326 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=15% id="supplier_1">' ||
1327 fnd_message.get_string('PO', 'PO_WF_NOTIF_SUPPLIER') || '</TH>' || NL;
1328
1329 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="costCenter_1">' ||
1330 fnd_message.get_string('PO', 'PO_WF_NOTIF_COST_CENTER') || '</TH>' || NL;
1331
1332 --here added order type, order, need-by
1333 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="orderType_1">' ||
1334 fnd_message.get_string('PO', 'PO_WF_NOTIF_ORDER_TYPE') || '</TH>' || NL;
1335
1336 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="order_1">' ||
1337 fnd_message.get_string('PO', 'PO_WF_NOTIF_ORDER') || '</TH>' || NL;
1338
1339 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="needBy_1">' ||
1340 fnd_message.get_string('PO', 'PO_WF_NOTIF_NEED_BY') || '</TH>' || NL;
1341
1342 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="UOM_1">' ||
1343 fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT') || '</TH>' || NL;
1344
1345 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=8% id="quant_1">' ||
1346 fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TH>' || NL;
1347
1348 if(l_display_currency_price_cell) then
1349 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=8% id="quant_1">' ||
1350 fnd_message.get_string('PO', 'PO_WF_NOTIF_TRANS_PRICE') || '</TH>' || NL;
1351 end if;
1352
1353 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="unitPrice_1">' ||
1354 fnd_message.get_string('PO', 'PO_WF_NOTIF_PRICE')||
1355 ' (' || l_currency_code || ')'|| '</TH>' || NL;
1356
1357 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% nowrap id="lineAmt_1">' ||
1358 fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT') ||
1359 ' (' || l_currency_code || ')' || '</TH>' || NL;
1360
1361 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="cancel_1">' ||
1362 fnd_message.get_string('PO', 'PO_WF_NOTIF_CANCEL') || '</TH>' || NL;
1363
1364 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="reason_1">' ||
1365 fnd_message.get_string('PO', 'PO_WF_NOTIF_OVERALL_STATUS') || '</TH>' || NL;
1366
1367 l_document := l_document || '</TR>' || NL;
1368
1369 open line_csr(l_document_id);
1370
1371 loop
1372
1373 fetch line_csr into l_line;
1374 i := i + 1;
1375
1376 exit when line_csr%notfound;
1377
1378 if(l_line.source_type_code = 'VENDOR') then
1379 l_order_num := get_po_number(l_line.line_location_id);
1380 else
1381 l_order_num := get_so_number(l_line.req_line_id);
1382 end if;
1383
1384 l_is_need_by_changed:=false;
1385 l_is_quantity_changed:=false;
1386 l_is_price_changed:='NO';
1387 l_cancel:=false;
1388 l_request_status:=null;
1389 GetChangeValues(l_group_id,
1390 l_line.req_line_id,
1391 'RESPONSE',
1392 l_old_need_by_date,
1393 l_new_need_by_date,
1394 l_is_need_by_changed,
1395 l_old_quantity,
1396 l_new_quantity,
1397 l_is_quantity_changed,
1398 l_old_currency_price,
1399 l_new_currency_price,
1400 l_old_price,
1401 l_new_price,
1402 l_is_price_changed,
1403 l_cancel,
1404 l_change_reason,
1405 l_request_status);
1406
1407
1408 begin
1409
1410 if l_segment_num = -1 then
1411 l_cost_center := '';
1412 else
1413
1414 l_cost_center := 'SINGLE';
1415
1416 dist_num := 1;
1417
1418 open ccId_csr(l_line.req_line_id);
1419 loop
1420 fetch ccId_csr into cc_Id;
1421 exit when ccid_csr%notfound;
1422
1423 if fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) then
1424 l_cost_center := l_segments(l_segment_num);
1425 else
1426 l_cost_center := '';
1427 end if;
1428
1429 if dist_num = 1 then
1430 cost_center_1 := l_cost_center;
1431 dist_num := 2;
1432 else
1433 if l_cost_center <> cost_center_1 then
1434 l_cost_center := multiple_cost_center;
1435 exit;
1436 end if;
1437 end if;
1438 end loop;
1439 close ccId_csr;
1440
1441 if l_cost_center <> multiple_cost_center then
1442 if fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) then
1443 l_cost_center := l_segments(l_segment_num);
1444 else
1445 l_cost_center := '';
1446 end if;
1447 end if;
1448
1449 end if; --if l_segment_num = -1
1450
1451 exception --any exception while retrieving the cost center
1452 when others then
1453 l_cost_center := '';
1454 end;
1455
1456
1457 l_document := l_document || '<TR>' || NL;
1458
1459 if(l_line.cancel_flag='Y' and nvl(l_cancel, false)<>true) then
1460 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineNum_1">' ||
1461 '<img src='||l_base_href||'/OA_MEDIA/cancelind_status.gif ALT="">' ||
1462 nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
1463 else
1464 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineNum_1">' ||
1465 nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
1466 end if;
1467
1468 l_document := l_document || '<TD ' || L_TABLE_CELL_WRAP_STYLE || ' headers="itemDesc_1">' ||
1469 nvl(l_line.item_desc, ' ') || '</TD>' || NL;
1470
1471 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="supplier_1">' ||
1472 nvl(l_line.sugg_supplier, ' ') || '</TD>' || NL;
1473
1474 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="costCenter_1">' ||
1475 nvl(l_cost_center, ' ') || '</TD>' || NL;
1476
1477 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="orderType_1">' ||
1478 nvl(l_line.order_type, ' ') || '</TD>' || NL;
1479
1480 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="order_1">' ||
1481 nvl(l_order_num, ' ') || '</TD>' || NL;
1482 if (l_is_need_by_changed = true) then
1483 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="needBy_1">' ||
1484 nvl(to_char(l_old_need_by_date), ' ') || '<BR>' ||
1485 nvl(to_char(l_new_need_by_date), ' ') || '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
1486
1487 else
1488 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="needBy_1">' ||
1489 nvl(to_char(l_line.need_by_date), ' ') || '</TD>' || NL;
1490 end if;
1491
1492 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="UOM_1">' ||
1493 nvl(l_line.uom, ' ') || '</TD>' || NL;
1494
1495 if (l_is_quantity_changed = true) then
1496 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="quant_1">' ||
1497 nvl(to_char(l_old_quantity), ' ') || '<BR>' ||
1498 nvl(to_char(l_new_quantity), ' ')|| '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
1499 else
1500 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="quant_1">' ||
1501 nvl(to_char(l_line.quantity), ' ') || '</TD>' || NL;
1502 end if;
1503
1504 -- bug 2739962, display the price in format of currency
1505 IF (l_display_currency_price_cell) THEN
1506 if (l_line.txn_curr_code is not null AND
1507 l_currency_code <> l_line.txn_curr_code) then
1508
1509 if (l_is_price_changed = 'YES') then
1510 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1511 to_char(l_old_currency_price, FND_CURRENCY.GET_FORMAT_MASK(l_line.txn_curr_code, 30)) || ' ' || l_line.txn_curr_code || '<BR>' ||
1512 to_char(l_new_currency_price, FND_CURRENCY.GET_FORMAT_MASK(l_line.txn_curr_code, 30)) || ' ' || l_line.txn_curr_code ||
1513 '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
1514 elsif(l_is_price_changed = 'DERIVED') then
1515 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1516 to_char(l_new_currency_price, FND_CURRENCY.GET_FORMAT_MASK(l_line.txn_curr_code, 30)) || ' ' || l_line.txn_curr_code ||
1517 '</TD>' || NL;
1518 else
1519 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1520 to_char(l_line.curr_unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_line.txn_curr_code, 30)) || ' ' || l_line.txn_curr_code ||
1521 '</TD>' || NL;
1522
1523 end if;
1524 else --display a blank cell
1525 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
1526 || ' headers="unitPrice_1"> </TD>' || NL;
1527 end if;
1528 END IF;
1529
1530 if (l_is_price_changed = 'YES') then
1531 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1532 to_char(l_old_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || '<BR>' ||
1533 to_char(l_new_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1534 '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
1535 elsif(l_is_price_changed = 'DERIVED') then
1536 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1537 to_char(l_new_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1538 '</TD>' || NL;
1539 else
1540 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="unitPrice_1">' ||
1541 to_char(l_line.unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1542 '</TD>' || NL;
1543 end if;
1544
1545 if(l_cancel=true) then
1546 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineAmt_1">' ||
1547 TO_CHAR(0, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1548 '</TD>' || NL;
1549 elsif (l_is_price_changed in('DERIVED', 'YES') or l_is_quantity_changed = true) then
1550 if(l_is_price_changed in('DERIVED', 'YES') and l_is_quantity_changed = true) then
1551 l_new_line_amount:=l_new_price*l_new_quantity;
1552 l_old_line_amount:=l_old_price*l_old_quantity;
1553 elsif(l_is_price_changed in('DERIVED', 'YES') ) then
1554 l_new_line_amount:=l_new_price*l_line.quantity;
1555 l_old_line_amount:=l_old_price*l_line.quantity;
1556 else
1557 l_new_line_amount:=l_line.unit_price*l_new_quantity;
1558 l_old_line_amount:=l_line.unit_price*l_old_quantity;
1559 end if;
1560 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineAmt_1">' ||
1561 TO_CHAR(l_old_line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || '<BR>' ||
1562 TO_CHAR(l_new_line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1563 '<img src='||l_base_href||'/OA_MEDIA/newupdateditem_status.gif ALT=""></TD>' || NL;
1564 else
1565 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineAmt_1">' ||
1566 TO_CHAR(l_old_line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1567 '</TD>' || NL;
1568 end if;
1569
1570 if(l_cancel=true) then
1571 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="cancel_1">' ||
1572 nvl(l_cancel_display, ' ') || '</TD>' || NL;
1573 else
1574 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="cancel_1"> </TD>' || NL;
1575 end if;
1576
1577 if(upper(l_request_status)='ACCEPTED') then
1578 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="reason_1">' ||
1579 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACCEPTED') || '</TD>' || NL;
1580 elsif(upper(l_request_status)='REJECTED') then
1581 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="reason_1">' ||
1582 fnd_message.get_string('PO', 'PO_WF_NOTIF_REJ') || '</TD>' || NL;
1583 elsif(upper(l_request_status)='PATIALLY') then
1584 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="reason_1">' ||
1585 fnd_message.get_string('PO', 'PO_WF_NOTIF_PARTIALLY_ACCP') || '</TD>' || NL;
1586 else
1587 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="reason_1"> </TD>' || NL;
1588 end if;
1589
1590 l_document := l_document || '</TR>' || NL;
1591
1592
1593 exit when i = l_max_lines;
1594 end loop;
1595
1596 close line_csr;
1597
1598 l_document := l_document || '</TABLE>';
1599
1600 /*
1601 get_additional_details(l_document_id, l_link_url);
1602
1603 l_document:=l_document||l_link_url;
1604 */
1605
1606
1607 end if;
1608
1609 document := l_document;
1610
1611 END GetReqLinesResponse;
1612
1613
1614 /*************************************************************************
1615 * Private Procedure: GetActionHistoryHtml
1616 *
1617 * Effects: generate the action history part of the change response
1618 * notification and req change approval notification
1619 *
1620 * Returns:
1621 ************************************************************************/
1622 PROCEDURE GetActionHistoryHtml(document_id in varchar2,
1623 display_type in varchar2,
1624 document in out NOCOPY varchar2,
1625 document_type in out NOCOPY varchar2) IS
1626
1627
1628 l_item_type wf_items.item_type%TYPE;
1629 l_item_key wf_items.item_key%TYPE;
1630
1631 l_document_id po_requisition_lines.requisition_header_id%TYPE;
1632 l_object_type po_action_history.object_type_code%TYPE;
1633 l_org_id po_requisition_lines.org_id%TYPE;
1634
1635 l_document VARCHAR2(32000) := '';
1636 l_document_hist VARCHAR2(32000) := '';
1637 l_document_pend VARCHAR2(32000) := '';
1638
1639 l_history history_record;
1640 l_history_seq number;
1641
1642 l_first_seq number;
1643 MAX_SEQNO number := 0;
1644
1645 NL VARCHAR2(1) := fnd_global.newline;
1646
1647 CURSOR history_csr(v_document_id NUMBER,
1648 v_object_type VARCHAR2) IS
1649
1650 SELECT poh.SEQUENCE_NUM,
1651 per.FULL_NAME,
1652 polc.DISPLAYED_FIELD,
1653 poh.ACTION_DATE,
1654 poh.NOTE,
1655 poh.OBJECT_REVISION_NUM
1656 from po_action_history poh,
1657 per_people_f per,
1658 po_lookup_codes polc
1659 where OBJECT_TYPE_CODE = v_object_type
1660 and poh.action_code = polc.lookup_code
1661 and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
1662 and per.person_id = poh.employee_id
1663 and trunc(sysdate) between per.effective_start_date
1664 and per.effective_end_date
1665 and OBJECT_ID = v_document_id
1666 UNION ALL
1667 SELECT poh.SEQUENCE_NUM,
1668 per.FULL_NAME,
1669 NULL,
1670 poh.ACTION_DATE,
1671 poh.NOTE,
1672 poh.OBJECT_REVISION_NUM
1673 from po_action_history poh,
1674 per_people_f per
1675 where OBJECT_TYPE_CODE = v_object_type
1676 and poh.action_code is null
1677 and per.person_id = poh.employee_id
1678 and trunc(sysdate) between per.effective_start_date
1679 and per.effective_end_date
1680 and OBJECT_ID = v_document_id
1681 order by 1 desc;
1682
1683 BEGIN
1684 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1685 l_item_key := substr(document_id, instr(document_id, ':') + 1,
1686 length(document_id) - 2);
1687
1688 l_document_id := wf_engine.GetItemAttrNumber
1689 (itemtype => l_item_type,
1690 itemkey => l_item_key,
1691 aname => 'DOCUMENT_ID');
1692
1693 l_org_id := wf_engine.GetItemAttrNumber
1694 (itemtype => l_item_type,
1695 itemkey => l_item_key,
1696 aname => 'ORG_ID');
1697
1698 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1699
1700 l_object_type := 'REQUISITION';
1701
1702 if (display_type = 'text/html') then
1703
1704 l_document := NL || NL || '<!-- ACTION_HISTORY -->'|| NL || NL || '<P>';
1705
1706 l_document := l_document || PrintHeading(fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_HISTORY'));
1707
1708 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || ' summary="' || fnd_message.get_string('ICX', 'ICX_POR_TBL_OF_APPROVERS') || '">' || NL;
1709
1710 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="seqNum_3"> </TH>' || NL;
1711
1712 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=20% id="employee_3">' ||
1713 fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || '</TH>' || NL;
1714
1715 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=12% id="action_3">' ||
1716 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || '</TH>' || NL;
1717
1718 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=12% id="date_3">' ||
1719 fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') || '</TH>' || NL;
1720
1721 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=35% id="actionNote_3">' ||
1722 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') || '</TH>' || NL;
1723
1724 l_document := l_document || '</TR>' || NL;
1725
1726 /*
1727 select max(sequence_num)
1728 into l_first_seq
1729 from po_action_history
1730 where action_code='SUBMIT CHANGE'
1731 and object_type_code=l_object_type
1732 and object_id=l_document_id;
1733 */
1734
1735 open history_csr(l_document_id, l_object_type);
1736 loop
1737
1738 fetch history_csr into l_history;
1739
1740 exit when history_csr%notfound;
1741
1742 max_seqno := max_seqno + 1;
1743 l_history_seq := l_history.seq_num + 1;
1744
1745 IF (l_history.action is not NULL) THEN
1746
1747 l_document_hist := l_document_hist || NL || '<TR>' || NL;
1748
1749 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="seqNum_3">' ||
1750 nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
1751
1752 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="employee_3">' ||
1753 nvl(l_history.employee_name, ' ') || '</TD>' || NL;
1754
1755 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="action_3">' ||
1756 nvl(l_history.action, ' ') || '</TD>' || NL;
1757
1758 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="date_3">' ||
1759 nvl(to_char(l_history.action_date), ' ') || '</TD>' || NL;
1760
1761 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="actionNote_3">' ||
1762 nvl(l_history.note, ' ') || '</TD>' || NL;
1763
1764 l_document_hist := l_document_hist || '</TR>' || NL;
1765
1766 ELSE
1767
1768 l_document_hist := l_document_hist || NL || '<TR>' || NL;
1769
1770 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="seqNum_3">' ||
1771 nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
1772
1773 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="employee_3">' ||
1774 nvl(l_history.employee_name, ' ') || '</TD>' || NL;
1775
1776 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="action_3">' ||
1777 nvl(l_history.action, ' ') || '</TD>' || NL;
1778
1779 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="date_3">' ||
1780 nvl(to_char(l_history.action_date), ' ') || '</TD>' || NL;
1781
1782 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="actionNote_3">' ||
1783 nvl(l_history.note, ' ') || '</TD>' || NL;
1784
1785 l_document_hist := l_document_hist || '</TR>' || NL;
1786
1787 END IF;
1788
1789 end loop;
1790
1791 close history_csr;
1792
1793 GetPendingActionHtml(l_item_type, l_item_key, max_seqno, l_document_pend);
1794
1795 l_document := l_document || l_document_pend || l_document_hist || '</TABLE>';
1796
1797 document := l_document;
1798
1799 elsif (display_type = 'text/plain') then
1800
1801 document := '';
1802
1803 end if;
1804 END GetActionHistoryHtml;
1805
1806 /*************************************************************************
1807 * Private Procedure: GetPendingActionHtml
1808 *
1809 * Effects: generate the pending action history part(approvers), called in
1810 * GetActionHistoryHtml
1811 *
1812 * Returns:
1813 ************************************************************************/
1814 PROCEDURE GetPendingActionHtml(p_item_type in varchar2,
1815 p_item_key in varchar2,
1816 max_seqno in number,
1817 p_document out NOCOPY varchar2) IS
1818
1819 l_document_id po_requisition_lines.requisition_header_id%TYPE;
1820 l_object_type po_action_history.object_type_code%TYPE;
1821 l_org_id po_requisition_lines.org_id%TYPE;
1822
1823 l_document VARCHAR2(32000) := '';
1824 l_sub_document VARCHAR2(32000) := '';
1825 l_one_row VARCHAR2(32000) := '';
1826
1827 l_history history_record;
1828 l_history_seq number;
1829 noPendAppr number := 0;
1830
1831 l_is_po_approval boolean := true;
1832 approverList ame_util.approversTable;
1833 upperLimit integer;
1834 fullName varchar2(240);
1835
1836 NL VARCHAR2(1) := fnd_global.newline;
1837
1838 CURSOR pending_csr(v_document_id NUMBER, v_object_type VARCHAR2) IS
1839
1840 SELECT pal.SEQUENCE_NUM,per.FULL_NAME,null,null,null,null
1841 FROM per_people_f per,
1842 po_approval_list_lines pal,
1843 po_approval_list_headers pah
1844 WHERE pah.document_id = v_document_id
1845 and pah.document_type = v_object_type
1846 and pah.latest_revision = 'Y'
1847 and pal.APPROVAL_LIST_HEADER_ID = pah.APPROVAL_LIST_HEADER_ID
1848 and pal.STATUS IS NULL
1849 and per.PERSON_ID = pal.APPROVER_ID
1850 and trunc(sysdate) between per.EFFECTIVE_START_DATE
1851 and per.EFFECTIVE_END_DATE
1852 ORDER BY 1 asc;
1853
1854 BEGIN
1855
1856 l_document_id := wf_engine.GetItemAttrNumber
1857 (itemtype => p_item_type,
1858 itemkey => p_item_key,
1859 aname => 'DOCUMENT_ID');
1860
1861 l_org_id := wf_engine.GetItemAttrNumber
1862 (itemtype => p_item_type,
1863 itemkey => p_item_key,
1864 aname => 'ORG_ID');
1865
1866 l_object_type := 'REQUISITION';
1867
1868 l_document := NL || NL || '<!-- PENDING APPROVER -->'|| NL || NL;
1869
1870 l_document := l_document || '<!-- the value of maxseqno in pending' || max_seqno || '-->' || NL;
1871 l_history_seq := max_seqno - 1;
1872
1873 open pending_csr(l_document_id, l_object_type);
1874
1875 loop
1876
1877 fetch pending_csr into l_history;
1878
1879 exit when pending_csr%notfound;
1880
1881
1882 l_history_seq := l_history_seq + 1;
1883
1884 noPendAppr := noPendAppr + 1;
1885 l_one_row := '<TR>' || NL;
1886
1887 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="seqNum_3">'
1888 || nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
1889
1890 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="employee_3">' ||
1891 nvl(l_history.employee_name, ' ') || '</TD>' || NL;
1892
1893 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="action_3">' ||
1894 nvl(l_history.action, ' ') || '</TD>' || NL;
1895
1896 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="date_3">' ||
1897 nvl(to_char(l_history.action_date), ' ') || '</TD>' || NL;
1898
1899 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="actionNote_3">' ||
1900 nvl(l_history.note, ' ') || '</TD>' || NL;
1901 l_one_row := l_one_row || '</TR>' || NL;
1902
1903 if noPendAppr <> 1 THEN
1904 l_sub_document := l_one_row || l_sub_document;
1905 END IF;
1906
1907 end loop;
1908 close pending_csr;
1909
1910 l_document := l_document || l_sub_document;
1911
1912 if noPendAppr > 1 then
1913 p_document := l_document;
1914 else
1915 p_document := '';
1916 end if;
1917
1918 END GetPendingActionHtml;
1919
1920 /*************************************************************************
1921 * Private Procedure: ConstructHeaderInfo
1922 *
1923 * Effects: generate the header part of the req approval notification
1924 * and the change response notification
1925 *
1926 * Returns:
1927 ************************************************************************/
1928 function ConstructHeaderInfo(l_item_type in varchar2,
1929 l_item_key in varchar2,
1930 l_change_request_group_id in number,
1931 l_document_id in number,
1932 l_call_from in varchar2) return varchar2 is
1933
1934 l_document VARCHAR2(32000) := '';
1935
1936 NL VARCHAR2(1) := fnd_global.newline;
1937
1938 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
1939
1940 l_note po_action_history.note%TYPE;
1941 l_old_req_amount VARCHAR2(40);
1942 l_old_tax_amount VARCHAR2(40);
1943 l_new_req_amount VARCHAR2(40);
1944 l_new_tax_amount VARCHAR2(40);
1945 l_currency_code fnd_currencies.CURRENCY_CODE%TYPE;
1946
1947 BEGIN
1948 begin
1949 l_currency_code := wf_engine.GetItemAttrText
1950 (itemtype => l_item_type,
1951 itemkey => l_item_key,
1952 aname => 'FUNCTIONAL_CURRENCY');
1953 l_old_req_amount := wf_engine.GetItemAttrText
1954 (itemtype => l_item_type,
1955 itemkey => l_item_key,
1956 aname => 'REQ_AMOUNT_CURRENCY_DSP');
1957 l_old_tax_amount := wf_engine.GetItemAttrText
1958 (itemtype => l_item_type,
1959 itemkey => l_item_key,
1960 aname => 'TAX_AMOUNT_CURRENCY_DSP');
1961 exception
1962 when others then
1963
1964 l_old_req_amount := wf_engine.GetItemAttrText
1965 (itemtype => l_item_type,
1966 itemkey => l_item_key,
1967 aname => 'REQ_AMOUNT_DSP')
1968 ||' '||l_currency_code;
1969
1970 l_old_tax_amount := wf_engine.GetItemAttrText
1971 (itemtype => l_item_type,
1972 itemkey => l_item_key,
1973 aname => 'TAX_AMOUNT_DSP')
1974 ||' '||l_currency_code;
1975
1976 end;
1977
1978 if(l_call_from = 'A') then
1979 begin
1980 l_new_req_amount := wf_engine.GetItemAttrText
1981 (itemtype => l_item_type,
1982 itemkey => l_item_key,
1983 aname => 'NEW_REQ_AMOUNT_CURRENCY_DSP');
1984 l_new_tax_amount := wf_engine.GetItemAttrText
1985 (itemtype => l_item_type,
1986 itemkey => l_item_key,
1987 aname => 'NEW_TAX_AMOUNT_CURRENCY_DSP');
1988 exception
1989 when others then
1990 select to_char(nvl(sum(nvl(decode(pcr3.action_type, 'CANCELLATION', 0,
1991 decode(prl.unit_price, 0, 0,
1992 nvl(pcr1.new_price, prl.unit_price)*
1993 nvl(pcr2.new_quantity, prl.quantity)*
1994 por_view_reqs_pkg.get_line_nonrec_tax_total(
1995 prl.requisition_line_id)/
1996 (prl.unit_price*prl.quantity))),0)),0),
1997 FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
1998 ||' '|| l_currency_code,
1999 to_char(nvl(sum(decode(pcr3.action_type, 'CANCELLATION', 0,
2000 nvl(pcr1.new_price, prl.unit_price)*
2001 nvl(pcr2.new_quantity, prl.quantity))), 0),
2002 FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
2003 ||' '|| l_currency_code
2004 into l_new_tax_amount, l_new_req_amount
2005 from po_requisition_lines_all prl,
2006 po_change_requests pcr1,
2007 po_change_requests pcr2,
2008 po_change_requests pcr3
2009 where prl.requisition_line_id=pcr1.document_line_id(+)
2010 and pcr1.change_request_group_id(+)=l_change_request_group_id
2011 and pcr1.request_level(+)='LINE'
2012 and pcr1.change_active_flag(+)='Y'
2013 and pcr1.new_price(+) is not null
2014 and prl.requisition_line_id=pcr2.document_line_id(+)
2015 and pcr2.change_request_group_id(+)=l_change_request_group_id
2016 and pcr2.request_level(+)='LINE'
2017 and pcr2.action_type(+)='DERIVED'
2018 and pcr2.new_quantity(+) is not null
2019 and prl.requisition_line_id=pcr3.document_line_id(+)
2020 and pcr3.change_request_group_id(+)=l_change_request_group_id
2021 and pcr3.request_level(+)='LINE'
2022 and pcr3.action_type(+)='CANCELLATION'
2023 and prl.requisition_header_id=l_document_id
2024 AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
2025 and NVL(prl.cancel_flag, 'N')='N';
2026
2027 end;
2028 else
2029 SELECT to_char(nvl(sum(nvl(nonrecoverable_tax, 0)), 0),
2030 FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
2031 ||' '|| l_currency_code
2032 INTO l_new_tax_amount
2033 FROM po_requisition_lines rl,
2034 po_req_distributions_all rd -- <R12 MOAC>
2035 WHERE rl.requisition_header_id = l_document_id
2036 AND rd.requisition_line_id = rl.requisition_line_id
2037 AND NVL(rl.modified_by_agent_flag, 'N') = 'N'
2038 and NVL(rl.cancel_flag, 'N')='N';
2039
2040 SELECT to_char(nvl(SUM(nvl(quantity * unit_price, 0)), 0),
2041 FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
2042 ||' '|| l_currency_code
2043 into l_new_req_amount
2044 FROM po_requisition_lines
2045 WHERE requisition_header_id = l_document_id
2046 AND NVL(cancel_flag,'N') = 'N'
2047 AND NVL(modified_by_agent_flag, 'N') = 'N';
2048
2049 end if;
2050 l_note := PO_WF_UTIL_PKG.GetItemAttrText
2051 (itemtype => l_item_type,
2052 itemkey => l_item_key,
2053 aname => 'JUSTIFICATION');
2054
2055 if l_note is null then
2056
2057 l_note := wf_engine.GetItemAttrText
2058 (itemtype => l_item_type,
2059 itemkey => l_item_key,
2060 aname => 'NOTE');
2061
2062 end if;
2063
2064
2065 l_document := l_document || NL || '<!-- REQ CHANGE SUMMARY -->'|| NL || NL || '<P>';
2066
2067 l_document := l_document || PrintHeading(fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_CHG_SUMMARY'));
2068
2069 -- New Table Style
2070
2071 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || 'SUMMARY=""><TR>
2072 <TD ' || L_TABLE_LABEL_STYLE || ' width="15%">' ||
2073 fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_REQ_AMOUNT')
2074 || ' </TD>' || NL;
2075
2076 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' >'
2077 || l_new_req_amount || '</TD></TR>' || NL;
2078
2079 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2080 fnd_message.get_string('PO', 'PO_WF_NOTIF_OLD_REQ_AMOUNT') || ' </TD>' || NL;
2081 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' >'
2082 || l_old_req_amount || '</TD></TR>' || NL;
2083
2084 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2085 fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_REQ_TAX') || ' </TD>' || NL;
2086
2087 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>'
2088 || l_new_tax_amount || '</TD></TR>' || NL;
2089
2090 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2091 fnd_message.get_string('PO', 'PO_WF_NOTIF_OLD_REQ_TAX') || ' </TD>' || NL;
2092
2093 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>'
2094 || l_old_tax_amount || '</TD></TR>' || NL;
2095
2096 l_document := l_document || NL;
2097
2098 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2099 fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || ' </TD>' || NL;
2100
2101 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>' || l_note || '<BR></TD></TR>' || NL;
2102
2103 l_document := l_document || '</TABLE>' || NL;
2104
2105 return l_document;
2106
2107 END ConstructHeaderInfo;
2108
2109
2110
2111 /*************************************************************************
2112 * Private Procedure: PrintHeading
2113 *
2114 * Effects: print the l_text in html header format
2115 *
2116 * Returns:
2117 ************************************************************************/
2118 function PrintHeading(l_text in varchar2) return varchar2 is
2119
2120 l_document varchar2(1000) := '';
2121
2122 NL VARCHAR2(1) := fnd_global.newline;
2123 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
2124
2125 begin
2126
2127 l_document := '<TABLE width="100%" border=0 cellpadding=0 cellspacing=0 SUMMARY="">';
2128 l_document := l_document || '<TR>'||NL;
2129 l_document := l_document || '<TD class=subheader1>'|| l_text;
2130 l_document := l_document || '</TD></TR>';
2131
2132 -- horizontal line
2133 l_document := l_document || '<TR>' || NL;
2134 l_document := l_document || '<TD colspan=2 height=1 bgcolor=#cccc99>
2135 <img src=' || l_base_href || '/OA_MEDIA/FNDITPNT.gif ALT=""></TD></TR>';
2136
2137 l_document := l_document || '<TR><TD colspan=2 height=5><img src=' || l_base_href || '/OA_MEDIA/PORTRANS.gif ALT=""></TD></TR></TABLE>' || NL;
2138
2139 return l_document;
2140
2141 end;
2142
2143 /*************************************************************************
2144 * Private Procedure: IsForeignCurrencyDisplayed
2145 *
2146 * Effects: check if the foreign currency need to be displayed in the
2147 * line part of the notification
2148 *
2149 * Returns:
2150 ************************************************************************/
2151 function IsForeignCurrencyDisplayed (l_document_id in number, l_display_txn_curr in varchar2, l_currency_code in varchar2) return boolean IS
2152
2153 l_currency po_requisition_lines_all.currency_code%type;
2154 begin
2155
2156 if l_display_txn_curr='Y' then
2157
2158 select currency_code
2159 into l_currency
2160 FROM po_requisition_lines_all
2161 WHERE requisition_header_id = l_document_id
2162 AND NVL(cancel_flag,'N') = 'N'
2163 AND NVL(modified_by_agent_flag, 'N') = 'N'
2164 and currency_code <> l_currency_code;
2165
2166 return true;
2167 else
2168 return false;
2169 end if;
2170 exception
2171 when no_data_found then
2172 return false;
2173 when too_many_rows then
2174 return true;
2175 when others then
2176 return false;
2177 end;
2178
2179 function get_po_number(p_line_location_id in number) return varchar2 IS
2180
2181 l_po_num varchar2(50);
2182
2183 l_count number;
2184
2185 BEGIN
2186 SELECT PH.SEGMENT1|| DECODE(PR.RELEASE_NUM, NULL, '', '-' || PR.RELEASE_NUM)
2187 INTO l_po_num
2188 FROM
2189 PO_RELEASES PR,
2190 PO_HEADERS_ALL PH, -- <R12 MOAC>
2191 PO_LINE_LOCATIONS PLL
2192 WHERE
2193 pll.line_location_id=p_line_location_id and
2194 PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND
2195 PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+);
2196
2197 RETURN l_po_num;
2198
2199 EXCEPTION
2200 WHEN OTHERS THEN
2201 RETURN '';
2202 END;
2203
2204 function get_so_number(req_line_id NUMBER) RETURN VARCHAR2 is
2205 l_status_code VARCHAR2(50);
2206 l_flow_meaning VARCHAR2(50);
2207 l_so_number VARCHAR2(50);
2208 l_line_id NUMBER;
2209 l_released_count NUMBER;
2210 l_total_count NUMBER;
2211 begin
2212 select to_char(OOH.ORDER_NUMBER), OOL.FLOW_STATUS_CODE, OOL.LINE_ID
2213 INTO l_so_number, l_status_code, l_line_id
2214 from PO_REQUISITION_LINES PRL,
2215 PO_REQUISITION_HEADERS_ALL PRH, -- <R12 MOAC>
2216 OE_ORDER_HEADERS_ALL OOH,
2217 OE_ORDER_LINES_ALL OOL,
2218 PO_SYSTEM_PARAMETERS PSP
2219 WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
2220 AND PRL.REQUISITION_LINE_ID = req_line_id
2221 AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
2222 AND OOL.HEADER_ID = OOH.HEADER_ID
2223 AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
2224 AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
2225
2226 return l_so_number;
2227
2228 EXCEPTION
2229 WHEN no_data_found THEN
2230 RETURN null;
2231 end get_so_number;
2232
2233 /*************************************************************************
2234 * Private Procedure: GetChangeValues
2235 *
2236 * Effects: get the new value and old value of the req line which
2237 * is displayed in the line details table
2238 *
2239 * Returns:
2240 ************************************************************************/
2241 procedure GetChangeValues(p_group_id in number,
2242 p_req_line_id in number,
2243 p_call_flag in varchar2,
2244 p_old_need_by_date out NOCOPY date,
2245 p_new_need_by_date out NOCOPY date,
2246 p_is_need_by_changed out NOCOPY boolean,
2247 p_old_quantity out NOCOPY number,
2248 p_new_quantity out NOCOPY number,
2249 p_is_quantity_changed out NOCOPY boolean,
2250 p_old_currency_price out NOCOPY number,
2251 p_new_currency_price out NOCOPY number,
2252 p_old_price out NOCOPY number,
2253 p_new_price out NOCOPY number,
2254 p_is_price_changed out NOCOPY varchar2,
2255 p_cancel out NOCOPY boolean,
2256 p_change_reason out NOCOPY varchar2,
2257 p_request_status out NOCOPY varchar2) is
2258
2259 cursor l_change_request_csr is
2260 select action_type,
2261 new_price,
2262 old_price,
2263 new_currency_unit_price,
2264 old_currency_unit_price,
2265 new_need_by_date,
2266 old_need_by_date,
2267 request_reason,
2268 request_status,
2269 new_quantity,
2270 old_quantity
2271 from po_change_requests
2272 where change_request_group_id=p_group_id
2273 and document_line_id=p_req_line_id
2274 and request_level='LINE';
2275 cursor l_request_status_csr is
2276 select distinct request_status
2277 from po_change_requests
2278 where change_request_group_id=p_group_id
2279 and document_line_id=p_req_line_id
2280 and action_type<>'DERIVED';
2281
2282 cursor l_get_reason_csr is
2283 select request_reason
2284 from po_change_requests
2285 where change_request_group_id=p_group_id
2286 and document_line_id=p_req_line_id
2287 and request_reason is not null;
2288
2289 cursor l_get_app_qty_change_csr is
2290 select change_request_id
2291 from po_change_requests
2292 where change_request_group_id=p_group_id
2293 and document_line_id=p_req_line_id
2294 and request_level='DISTRIBUTION'
2295 and request_status<>'REJECTED';
2296
2297 cursor l_get_res_qty_change_csr is
2298 select change_request_id
2299 from po_change_requests
2300 where change_request_group_id=p_group_id
2301 and document_line_id=p_req_line_id
2302 and request_level='DISTRIBUTION';
2303
2304 l_old_quantity number:=0;
2305 l_new_quantity number:=0;
2306 l_old_price number;
2307 l_new_price number;
2308 l_old_currency_unit_price number;
2309 l_new_currency_unit_price number;
2310 l_old_need_by_date DATE;
2311 l_new_need_by_date DATE;
2312 l_action_type po_change_requests.action_type%type;
2313 l_request_level po_change_requests.request_level%type;
2314 l_request_status po_change_requests.request_status%type;
2315 l_change_request_id number;
2316 begin
2317
2318 p_request_status:=null;
2319 open l_change_request_csr;
2320 loop
2321 fetch l_change_request_csr
2322 into l_action_type,
2323 l_new_price,
2324 l_old_price,
2325 l_new_currency_unit_price,
2326 l_old_currency_unit_price,
2327 l_new_need_by_date,
2328 l_old_need_by_date,
2329 p_change_reason,
2330 p_request_status,
2331 l_new_quantity,
2332 l_old_quantity;
2333 exit when l_change_request_csr%NOTFOUND;
2334
2335 if(p_call_flag='APPROVE' and p_request_status='REJECTED') then
2336 p_change_reason:=null;
2337 end if;
2338
2339 if(l_action_type='CANCELLATION') then
2340 p_cancel:=true;
2341 close l_change_request_csr;
2342 return;
2343 else
2344 if(p_call_flag<>'APPROVE' or p_request_status<>'REJECTED') then
2345 if(l_new_price is not null) then
2346 if(l_action_type = 'DERIVED') then
2347 p_is_price_changed :='DERIVED';
2348 else
2349 p_is_price_changed :='YES';
2350 end if;
2351 p_new_price:=l_new_price;
2352 p_new_currency_price:=l_new_currency_unit_price;
2353 p_old_price:=l_old_price;
2354 p_old_currency_price:=l_old_currency_unit_price;
2355 elsif(l_new_need_by_date is not null) then
2356 p_new_need_by_date:=l_new_need_by_date;
2357 p_old_need_by_date:=l_old_need_by_date;
2358 p_is_need_by_changed :=true;
2359 elsif(l_new_quantity is not null) then
2360 p_old_quantity:=l_old_quantity;
2361 p_new_quantity:=l_new_quantity;
2362 end if;
2363 end if;
2364 end if;
2365 end loop;
2366 close l_change_request_csr;
2367
2368
2369 p_is_quantity_changed :=false;
2370 if(p_call_flag='RESPONSE') then
2371 open l_get_res_qty_change_csr;
2372 fetch l_get_res_qty_change_csr into l_change_request_id;
2373 close l_get_res_qty_change_csr;
2374 if(l_change_request_id is not null) then
2375 p_is_quantity_changed :=true;
2376 end if;
2377 else
2378 open l_get_app_qty_change_csr;
2379 fetch l_get_app_qty_change_csr into l_change_request_id;
2380 close l_get_app_qty_change_csr;
2381 if(l_change_request_id is not null) then
2382 p_is_quantity_changed :=true;
2383 end if;
2384 end if;
2385
2386 if(p_call_flag='RESPONSE') then
2387 p_request_status:=null;
2388 open l_request_status_csr;
2389 loop
2390 fetch l_request_status_csr into l_request_status;
2391 exit when l_request_status_csr%NOTFOUND;
2392 if(p_request_status is null) then
2393 p_request_status:=l_request_status;
2394 elsif(p_request_status <>l_request_status) then
2395 p_request_status:='PATIALLY';
2396 exit;
2397 end if;
2398 end loop;
2399 close l_request_status_csr;
2400 elsif(p_change_reason is null) then
2401 open l_get_reason_csr;
2402 fetch l_get_reason_csr into p_change_reason;
2403 close l_get_reason_csr;
2404 end if;
2405 end;
2406
2407
2408 PROCEDURE SetDocMgrContext (itemtype VARCHAR2, itemkey VARCHAR2) is
2409
2410 l_user_id number;
2411 l_responsibility_id number;
2412 l_application_id number;
2413
2414 l_progress varchar2(200);
2415
2416 BEGIN
2417
2418 l_user_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
2419 itemkey => itemkey,
2420 aname => 'USER_ID');
2421 --
2422 l_application_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
2423 itemkey => itemkey,
2424 aname => 'APPLICATION_ID');
2425 --
2426 l_responsibility_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
2427 itemkey => itemkey,
2428 aname => 'RESPONSIBILITY_ID');
2429
2430 /* Set the context for the doc manager */
2431 fnd_global.APPS_INITIALIZE (l_user_id,
2432 l_responsibility_id,
2433 l_application_id);
2434
2435 l_progress := 'SetDocMgrContext. USER_ID= ' || to_char(l_user_id)
2436 || ' APPLICATION_ID= ' || to_char(l_application_id) ||
2437 'RESPONSIBILITY_ID= ' || to_char(l_responsibility_id);
2438
2439 EXCEPTION
2440
2441 WHEN OTHERS THEN
2442 wf_core.context('PO_WF_REQ_NOTIFICATION','SetDocMgrContext',l_progress);
2443 raise;
2444
2445 END SetDocMgrContext;
2446
2447
2448 /*************************************************************************
2449 * Private Procedure: GetPoLineShipment
2450 *
2451 * Effects: generate the line/shipment part of the po approval notification
2452 *
2453 * Returns:
2454 ************************************************************************/
2455 procedure GetPoLineShipment(l_line_num in number,
2456 l_ship_num in number,
2457 l_item_id in number,
2458 l_org_id in number,
2459 l_old_need_by_date in date,
2460 l_new_need_by_date in date,
2461 l_old_price in number,
2462 l_new_price in number,
2463 l_po_currency in varchar2,
2464 l_old_qty in number,
2465 l_new_qty in number,
2466 l_action_type in varchar2,
2467 l_item_desc in varchar2,
2468 l_uom in varchar2,
2469 l_ship_to_location in varchar2,
2470 l_request_reason in varchar2,
2471 l_old_start_date in date,
2472 l_new_start_date in date,
2473 l_old_end_date in date,
2474 l_new_end_date in date,
2475 l_old_amount in number,
2476 l_new_amount in number,
2477 l_has_temp_labor in boolean,
2478 l_display_type in varchar2,
2479 l_document out NOCOPY varchar2) is
2480
2481 l_item MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
2482 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
2483
2484 begin
2485
2486 if (l_display_type = 'text/html' ) then
2487 l_document := l_document || '<TR>' || NL;
2488 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2489 || ' headers="lineNum_1">' ||
2490 nvl(to_char(l_line_num), ' ') || '</TD>' || NL;
2491
2492 if(l_action_type = 'CANCELLATION') then
2493 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2494 || ' headers="ShipNum_1">'
2495 || '<img src=' || l_base_href || '/OA_MEDIA/cancelind_status.gif ALT="">'
2496 ||to_char(l_ship_num)||'</TD>' || NL;
2497
2498 else
2499 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2500 || ' headers="ShipNum_1">'
2501 ||to_char(l_ship_num)||'</TD>' || NL;
2502 end if;
2503
2504 -- fix bug 2739962, get the item
2505 begin
2506 select msi.concatenated_segments
2507 into l_item
2508 from mtl_system_items_kfv msi,
2509 financials_system_params_all fsp
2510 where msi.inventory_item_id=l_item_id
2511 and fsp.INVENTORY_ORGANIZATION_ID =
2512 NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID)
2513 and fsp.org_id=l_org_id;
2514 exception
2515 when others then
2516 l_item:=null;
2517 end;
2518
2519 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2520 || ' headers="Item_1">' ||
2521 nvl(l_item, ' ') || '</TD>' || NL;
2522
2523 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2524 || ' headers="Discription_1">' ||
2525 nvl(l_item_desc, ' ') || '</TD>' || NL;
2526
2527 -- fix bug 2739962, swap the price and unit column
2528 if(l_new_price is null) then
2529 -- fix bug 2739962, display the price in format
2530 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2531 || ' headers="Price_1">'
2532 ||to_char(l_old_price)||'</TD>' || NL;
2533 else
2534 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2535 || ' headers="Price_1">'
2536 || to_char(l_old_price)|| '<BR>'|| to_char(l_new_price)
2537 || '<img src=' || l_base_href || '/OA_MEDIA/newupdateditem_status.gif ALT="">'
2538 ||'</TD>' || NL;
2539 end if;
2540
2541 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2542 || ' headers="Unit_1">' ||
2543 nvl(l_uom, ' ') || '</TD>' || NL;
2544
2545 if (l_has_temp_labor) then
2546 if(l_new_start_date is null) then
2547 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2548 || ' headers="Start_1">'
2549 ||nvl(to_char(l_old_start_date), ' ')||'</TD>' || NL;
2550 else
2551 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2552 || ' headers="Start_1">'
2553 || to_char(l_old_start_date)|| '<BR>'|| to_char(l_new_start_date)
2554 || '<img src=' || l_base_href || '/OA_MEDIA/newupdateditem_status.gif ALT="">'
2555 ||'</TD>' || NL;
2556 end if;
2557 if(l_new_end_date is null) then
2558 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2559 || ' headers="End_1">'
2560 ||nvl(to_char(l_old_end_date), ' ')||'</TD>' || NL;
2561 else
2562 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2563 || ' headers="End_1">'
2564 || to_char(l_old_end_date)|| '<BR>'|| to_char(l_new_end_date)
2565 || '<img src=' || l_base_href || '/OA_MEDIA/newupdateditem_status.gif ALT="">'
2566 ||'</TD>' || NL;
2567 end if;
2568
2569 if(l_new_amount is null) then
2570 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2571 || ' headers="Amount_1">'
2572 ||to_char(l_old_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30))||'</TD>' || NL;
2573 else
2574 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2575 || ' headers="Amount_1">'
2576 || to_char(l_old_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30))
2577 || '<BR>'|| to_char(l_new_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30))
2578 || '<img src=' || l_base_href || '/OA_MEDIA/newupdateditem_status.gif ALT="">'
2579 ||'</TD>' || NL;
2580 end if;
2581
2582 else
2583 if(l_new_qty is null or l_new_qty=l_old_qty) then
2584 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2585 || ' headers="Quantity_1">'
2586 ||to_char(l_old_qty)||'</TD>' || NL;
2587 else
2588 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2589 || ' headers="Quantity_1">'
2590 || to_char(l_old_qty)|| '<BR>'|| to_char(l_new_qty)
2591 || '<img src=' || l_base_href || '/OA_MEDIA/newupdateditem_status.gif ALT="">'
2592 ||'</TD>' || NL;
2593 end if;
2594
2595 if(l_new_amount is null) then
2596 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2597 || ' headers="Amount_1">'
2598 ||to_char(l_old_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30))||'</TD>' || NL;
2599 else
2600 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2601 || ' headers="Amount_1">'
2602 || to_char(l_old_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30))
2603 || '<BR>'|| to_char(l_new_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30))
2604 || '<img src=' || l_base_href || '/OA_MEDIA/newupdateditem_status.gif ALT="">'
2605 ||'</TD>' || NL;
2606 end if;
2607
2608 if(l_new_need_by_date is null) then
2609 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2610 || ' headers="NeedBy_1">'
2611 ||to_char(l_old_need_by_date)||'</TD>' || NL;
2612 else
2613 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2614 || ' headers="NeedBy_1">'
2615 || to_char(l_old_need_by_date)|| '<BR>'|| to_char(l_new_need_by_date)
2616 || '<img src=' || l_base_href || '/OA_MEDIA/newupdateditem_status.gif ALT="">'
2617 ||'</TD>' || NL;
2618 end if;
2619 end if;
2620 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE
2621 || ' headers="ShipTo_1">'
2622 ||nvl(l_ship_to_location, ' ')||'</TD>' || NL;
2623 l_document := l_document || '<TD ' || L_TABLE_CELL_WRAP_STYLE
2624 || ' headers="Reason_1">'
2625 ||nvl(l_request_reason, ' ')||'</TD>' || NL;
2626 l_document:= l_document|| '</TR>';
2627
2628 else -- text
2629 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || ': ' || nvl(to_char(l_line_num), '') || NL;
2630
2631 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPMENT') || ': ' || to_char(l_ship_num)|| NL;
2632
2633 -- fix bug 2739962, get the item
2634 begin
2635 select msi.concatenated_segments
2636 into l_item
2637 from mtl_system_items_kfv msi,
2638 financials_system_params_all fsp
2639 where msi.inventory_item_id=l_item_id
2640 and fsp.INVENTORY_ORGANIZATION_ID =
2641 NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID)
2642 and fsp.org_id=l_org_id;
2643 exception
2644 when others then
2645 l_item:=null;
2646 end;
2647 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM') || ': ' || nvl(l_item, '') || NL;
2648
2649 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || ': ' || nvl(l_item_desc, '') || NL;
2650
2651 -- fix bug 2739962, swap the price and unit column
2652 if(l_new_price is null) then
2653 -- fix bug 2739962, display the price in format
2654 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PRICE') || ': ' ||to_char(l_old_price)|| NL;
2655
2656 else
2657 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_OLD_PRICE') || ': ' ||to_char(l_old_price) || NL;
2658 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_PRICE') || ': ' || to_char(l_new_price) || NL;
2659
2660 end if;
2661
2662 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT') || ': ' || nvl(l_uom, '') || NL;
2663
2664 if (l_has_temp_labor) then
2665 if(l_new_start_date is null) then
2666 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_START_DATE') || ': ' ||nvl(to_char(l_old_start_date), '')|| NL;
2667 else
2668 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_OLD_START_DATE') || ': ' ||nvl(to_char(l_old_start_date), '')|| NL;
2669 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_START_DATE') || ': ' ||nvl(to_char(l_new_start_date), '')|| NL;
2670 end if;
2671 if(l_new_end_date is null) then
2672 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_END_DATE') || ': ' ||nvl(to_char(l_old_end_date), '')|| NL;
2673
2674 else
2675 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_OLD_END_DATE') || ': ' ||nvl(to_char(l_old_end_date), '')|| NL;
2676 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_END_DATE') || ': ' ||nvl(to_char(l_new_end_date), '')|| NL;
2677 end if;
2678
2679 if(l_new_amount is null) then
2680 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT') || ': ' || to_char(l_old_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30)) || NL;
2681 else
2682 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_OLD_AMOUNT') || ': ' || to_char(l_old_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30)) || NL;
2683 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_AMOUNT') || ': ' || to_char(l_new_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30)) || NL;
2684 end if;
2685
2686 else
2687 if(l_new_qty is null or l_new_qty=l_old_qty) then
2688 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QTY_ORDERED') || ': ' ||to_char(l_old_qty)|| NL;
2689
2690 else
2691 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_OLD_QTY_ORDERED') || ': ' ||to_char(l_old_qty)|| NL;
2692 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_QTY_ORDERED') || ': ' || to_char(l_new_qty) || NL;
2693
2694 end if;
2695
2696 if(l_new_amount is null) then
2697 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT') || ': ' || to_char(l_old_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30)) || NL;
2698
2699 else
2700 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_OLD_AMOUNT') || ': ' || to_char(l_old_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30)) || NL;
2701 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_AMOUNT') || ': ' || to_char(l_new_amount, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30)) || NL;
2702 end if;
2703
2704 if(l_new_need_by_date is null) then
2705 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEED_BY') || ': ' ||to_char(l_old_need_by_date)|| NL;
2706
2707 else
2708 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_OLD_NEED_BY') || ': ' || to_char(l_old_need_by_date)|| NL;
2709 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_NEED_BY') || ': ' || to_char(l_new_need_by_date) || NL;
2710 end if;
2711 end if;
2712
2713 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPTO') || ': ' ||nvl(l_ship_to_location, '')|| NL;
2714 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REASON') || ': ' ||nvl(l_request_reason, '')|| NL || NL;
2715
2716 end if;
2717
2718 end GetPoLineShipment;
2719
2720 /*************************************************************************
2721 * Public Function: Get_Goods_Shipment_New_Amount
2722 *
2723 * Effects: This function calculates new shipment total for a goods based
2724 * line and used in new total calculations of RCO Buyer
2725 * Notifications
2726 *
2727 ************************************************************************/
2728 FUNCTION get_goods_shipment_new_amount( p_group_id IN NUMBER,
2729 p_po_line_id IN NUMBER,
2730 p_po_shipment_id IN NUMBER,
2731 p_old_price IN NUMBER,
2732 p_old_quantity IN NUMBER
2733 )
2734 RETURN NUMBER IS
2735
2736 l_new_price po_change_requests.new_price%TYPE := null;
2737 l_new_quantity po_change_requests.new_quantity%TYPE := null;
2738
2739 BEGIN
2740 begin
2741 select new_price
2742 into l_new_price
2743 from po_change_requests pcr
2744 where pcr.change_request_group_id=p_group_id
2745 and pcr.document_line_id= p_po_line_id
2746 and pcr.request_level = 'LINE'
2747 and new_price is not null;
2748 exception
2749 when NO_DATA_FOUND then
2750 l_new_price := null;
2751 end;
2752
2753 begin
2754 select new_quantity
2755 into l_new_quantity
2756 from po_change_requests pcr
2757 where pcr.change_request_group_id=p_group_id
2758 and pcr.document_line_id= p_po_line_id
2759 and pcr.document_line_location_id =p_po_shipment_id
2760 and pcr.request_level = 'SHIPMENT'
2761 and new_quantity is not null;
2762 exception
2763 when NO_DATA_FOUND then
2764 l_new_quantity := null;
2765 end;
2766
2767 -- when there is only need by date change, we want to return old line
2768 -- total.
2769 if (l_new_price is null and l_new_quantity is null) then
2770 return (p_old_price * p_old_quantity);
2771 elsif (l_new_price is null) then
2772 l_new_price := p_old_price;
2773 elsif (l_new_quantity is null) then
2774 l_new_quantity := p_old_quantity;
2775 end if;
2776
2777 return (l_new_price * l_new_quantity);
2778
2779 EXCEPTION
2780 when others then
2781 return null;
2782 END;
2783
2784
2785 /*************************************************************************
2786 * Public Function: Get_Goods_Shipment_New_Amount
2787 *
2788 * Effects: This function calculates new shipment total
2789 * and used in new total calculations of RCO Buyer
2790 * Notifications.
2791 * Notice: This function is used to calculate new shipment amount
2792 * for lines of all types ( not only 'Goods').
2793 * The name is because of historical reason.
2794 ************************************************************************/
2795
2796 FUNCTION get_goods_shipment_new_amount(p_org_id in number,
2797 p_group_id in number,
2798 p_line_id in number,
2799 p_item_id in number,
2800 p_line_uom in varchar2,
2801 p_old_price in number,
2802 p_line_location_id in number)
2803
2804 RETURN number IS
2805
2806 l_new_price po_change_requests.new_price%TYPE := null;
2807 l_new_quantity po_change_requests.new_quantity%TYPE := null;
2808 l_blanket_header_id number;
2809
2810 l_old_quantity number;
2811 l_new_amount number;
2812 l_tmp_new_amount number;
2813 l_document_type varchar2(100);
2814 l_matching_basis varchar2(100);
2815
2816 l_po_header_id number;
2817 l_po_order_type varchar2(20);
2818 l_po_in_txn_currency varchar2(1):='N';
2819 l_rate number:=1;
2820 l_result number;
2821
2822 l_progress VARCHAR2(100) := '000';
2823 l_api_name varchar2(50):= 'get_goods_shipment_new_amount';
2824
2825 BEGIN
2826
2827 l_progress := '001';
2828 if g_fnd_debug = 'Y' then
2829 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2830 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
2831 END IF;
2832 end if;
2833
2834 -- price change are displayed at line level. Quantity,Need_by_date and amount change are displayed at shipment level. For line level rows ( where p_line_location_id is null ), we don't show amount.
2835
2836 IF ( p_line_location_id is null ) then
2837 l_progress:= '002';
2838 if g_fnd_debug = 'Y' then
2839 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2840 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
2841 END IF;
2842 end if;
2843
2844 return null;
2845
2846 -- below we get new amount for shipment level rows
2847 ELSE
2848
2849 -- first check if we can get the new amount from po_change_requests table.
2850 -- notice: p_line_id and p_line_location_id uniquely identify a row.
2851 SELECT pcr.new_amount,(nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0)),pol.matching_basis
2852 into l_tmp_new_amount, l_old_quantity,l_matching_basis
2853 FROM po_change_requests pcr,
2854 po_lines_all pol,
2855 po_line_locations_all pll,
2856 po_headers_all poh
2857 WHERE pcr.change_request_group_id= p_group_id
2858 AND pcr.request_status IN ('PENDING', 'BUYER_APP', 'ACCEPTED', 'REJECTED')
2859 AND pol.po_line_id = p_line_id
2860 AND pll.line_location_id = p_line_location_id
2861 AND pcr.document_header_id=pol.po_header_id
2862 AND pcr.document_line_id=pol.po_line_id
2863 AND nvl(pcr.document_line_location_id,
2864 -1)=pll.line_location_id(+)
2865 AND pcr.request_level<>'DISTRIBUTION'
2866 AND pol.from_header_id=poh.po_header_id(+);
2867
2868
2869 l_progress:= '003';
2870 if g_fnd_debug = 'Y' then
2871 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2872 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
2873 END IF;
2874 end if;
2875
2876 if ( l_tmp_new_amount is not null ) then
2877
2878 l_progress:= '004';
2879 if g_fnd_debug = 'Y' then
2880 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2881 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
2882 END IF;
2883 end if;
2884 l_result:= l_tmp_new_amount;
2885
2886 else
2887
2888 -- for amount_based line with only need_by date change, return null for the new amount
2889 if (l_matching_basis = 'AMOUNT') then
2890
2891 l_result:= null;
2892
2893 else
2894
2895 -- for qty_based line, if pcr.new_amount is null, we need to calculate new amount.
2896 -- first, we get new quantity from po_change_request.
2897
2898 begin
2899 select new_quantity
2900 into l_new_quantity
2901 from po_change_requests pcr
2902 where pcr.change_request_group_id=p_group_id
2903 and pcr.document_line_id= p_line_id
2904 and pcr.document_line_location_id =p_line_location_id
2905 and pcr.request_level = 'SHIPMENT'
2906 and new_quantity is not null;
2907 exception
2908 when NO_DATA_FOUND then
2909 l_new_quantity := null;
2910 end;
2911
2912 l_progress := '005';
2913 if g_fnd_debug = 'Y' then
2914 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2915 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
2916 END IF;
2917 end if;
2918
2919 -- Then we get new price
2920 -- First we check if there are some source documents related to the line.
2921 --using distinct if a line from a blanket exists on multiple requisitions
2922
2923 begin
2924 select distinct pcr.document_type
2925 into l_document_type
2926 from po_change_requests pcr
2927 where pcr.change_request_group_id=p_group_id
2928 and pcr.document_line_id= p_line_id
2929 and pcr.document_line_location_id = p_line_location_id;
2930
2931 -- release always has source document ( blanket )
2932 -- for SPO,go to prl.blanket_po_header_id
2933 -- to check if there is some backing source document
2934 if ( l_document_type = 'PO') then
2935 select
2936 distinct prl.blanket_po_header_id
2937 into
2938 l_blanket_header_id
2939 from
2940 po_requisition_lines_all prl,
2941 po_line_locations_all pll,
2942 po_lines_all pol
2943 where
2944 pol.po_line_id = p_line_id and
2945 pol.po_line_id = pll.po_line_id and
2946 prl.line_location_id = pll.line_location_id;
2947
2948 end if;
2949
2950 exception
2951 when NO_DATA_FOUND then
2952 if g_fnd_debug = 'Y' then
2953 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2954 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
2955 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'this po line does not have source document');
2956 END IF;
2957 end if;
2958 l_blanket_header_id := null;
2959 end;
2960
2961 l_progress := '006';
2962 if g_fnd_debug = 'Y' then
2963 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2964 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
2965 END IF;
2966 end if;
2967
2968 -- for PO without source document, get new price from po_change_requests
2969 IF (l_document_type = 'PO' and l_blanket_header_id is NULL) THEN
2970
2971 begin
2972 select new_price
2973 into l_new_price
2974 from po_change_requests pcr
2975 where pcr.change_request_group_id=p_group_id
2976 and pcr.document_line_id= p_line_id
2977 and pcr.request_level = 'LINE'
2978 and new_price is not null;
2979 exception
2980 when NO_DATA_FOUND then
2981 l_new_price := null;
2982 end;
2983
2984 -- for PO with source document or RELEASE,use get_po_price_break_grp function to get the price.
2985 ELSE
2986 l_new_price := Get_PO_Price_Break_Grp( p_org_id, p_group_id, p_line_id, p_item_id, p_line_uom, p_old_price,p_line_location_id);
2987
2988 END IF;
2989
2990 l_progress := '007';
2991 if g_fnd_debug = 'Y' then
2992 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2993 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
2994 END IF;
2995 end if;
2996
2997 -- when there is only need by date change, we want to return old line total.
2998 if (l_new_price is null and l_new_quantity is null) then
2999 return (p_old_price * l_old_quantity);
3000 elsif (l_new_price is null) then
3001 l_new_price := p_old_price;
3002 elsif (l_new_quantity is null) then
3003 l_new_quantity := l_old_quantity;
3004 end if;
3005
3006 l_progress := '008';
3007 if g_fnd_debug = 'Y' then
3008 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3009 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
3010 END IF;
3011 end if;
3012
3013 l_result:= l_new_price * l_new_quantity;
3014
3015 end if;
3016
3017 end if; -- for l_tmp_new_amount
3018
3019 /* bug 5363103 */
3020
3021 select pol.po_header_id, pol.order_type_lookup_code
3022 into l_po_header_id, l_po_order_type
3023 from po_lines_all pol
3024 where pol.po_line_id = p_line_id;
3025
3026 /* Removed code for dividing result by rate because now we have txn amt and txn price
3027 in PO_CHANGE_REQUESTs */
3028 return l_result;
3029
3030 END IF; -- for p_line_location_id is null
3031
3032 EXCEPTION
3033 when others then
3034 if g_fnd_debug = 'Y' then
3035 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3036 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3037 l_api_name, sqlerrm);
3038 END IF;
3039 end if;
3040 raise;
3041 END get_goods_shipment_new_amount;
3042
3043
3044 /*************************************************************************
3045 * Public Procedure: Get_Po_Chg_Approval_Notif
3046 *
3047 * Effects: generate the notification to the buyer of the PO
3048 * for buyer's approval
3049
3050 ************************************************************************/
3051 PROCEDURE Get_Po_Chg_Approval_Notif(document_id IN varchar2,
3052 display_type in Varchar2,
3053 document in out nocopy clob,
3054 document_type in out nocopy varchar2)
3055 IS
3056 l_clob_flag number :=1;
3057 l_header_id number;
3058 l_release_id number;
3059 l_grp_id number;
3060 l_blanket_num number;
3061 l_release_num number;
3062 l_po_doc_id number;
3063 l_rel_doc_id number;
3064
3065 l_acceptance_required_flag varchar2(1);
3066 l_document varchar2(32000);
3067 l_type_lookup_code varchar2(25);
3068 l_document_type varchar2(30);
3069 l_po_num varchar2(20);
3070 l_revision_num number;
3071 l_po_total number;
3072 l_po_currency varchar2(15);
3073 l_vendor_id number;
3074 l_vendor_site_id number;
3075
3076 l_supplier_name po_vendors.vendor_name%TYPE;
3077 l_sup_address_line1 po_vendor_sites_all.address_line1%TYPE;
3078 l_sup_address_line2 po_vendor_sites_all.address_line2%TYPE;
3079 l_sup_address_line3 po_vendor_sites_all.address_line3%TYPE;
3080 l_sup_city po_vendor_sites_all.city%TYPE;
3081 l_sup_state po_vendor_sites_all.state%TYPE;
3082 l_sup_zip po_vendor_sites_all.zip%TYPE;
3083 l_order_date date;
3084 l_fob po_headers_all.fob_lookup_code%TYPE;
3085 l_carrier po_headers_all.ship_via_lookup_code%TYPE;
3086 l_ship_to_id number;
3087 l_ship_addr_l1 hr_locations_all.address_line_1%TYPE;
3088 l_ship_addr_l2 hr_locations_all.address_line_2%TYPE;
3089 l_ship_addr_l3 hr_locations_all.address_line_3%TYPE;
3090 l_ship_city hr_locations_all.town_or_city%TYPE;
3091 l_ship_state hr_locations_all.region_1%TYPE;
3092 l_ship_zip hr_locations_all.postal_code%TYPE;
3093
3094 l_num_of_changes number;
3095 l_num_of_cancels number;
3096 l_document1 VARCHAR2(32000) := '';
3097 l_line_num number;
3098 l_ship_num number;
3099 l_sup_pt_num varchar2(240);
3100 l_old_need_by_date date;
3101 l_new_need_by_date date;
3102 l_old_qty number;
3103 l_change_old_qty number;
3104 l_change_new_qty number;
3105 l_old_price number;
3106 l_new_price number;
3107 l_action_type po_change_requests.action_type%TYPE;
3108 l_item_desc po_lines_all.item_description%TYPE;
3109 l_uom po_line_locations_all.unit_meas_lookup_code%TYPE;
3110 l_line_quantity number;
3111 l_line_uom po_lines_all.unit_meas_lookup_code%TYPE;
3112 l_ship_to_location hr_locations_all.location_code%type;
3113 l_request_reason po_change_requests.request_reason%type;
3114
3115 l_old_quantity number;
3116 l_new_quantity number;
3117 l_gen_flag boolean :=false;
3118 l_item_id number;
3119 l_org_id number;
3120 l_line_id number:=null;
3121 l_pre_line_id number:=null;
3122 l_pb_new_price number;
3123
3124 l_supplier_id po_headers_all.vendor_id%TYPE;
3125 l_supplier_site_id po_headers_all.vendor_site_id%TYPE;
3126 l_creation_date po_headers_all.creation_date%TYPE;
3127 l_po_header_id po_headers_all.po_header_id%TYPE;
3128 l_po_line_id po_lines_all.po_line_id%TYPE;
3129 l_line_type_id po_lines_all.line_type_id%TYPE;
3130 l_item_revision po_lines_all.item_revision%TYPE;
3131 l_category_id po_lines_all.category_id%TYPE;
3132 l_supplier_item_num po_lines_all.VENDOR_PRODUCT_NUM%TYPE;
3133 l_base_unit_price po_lines_all.base_unit_price%TYPE;
3134 l_pb_base_unit_price number;
3135 l_pb_break_id number;
3136
3137 l_global_flag po_headers_all.global_agreement_flag%type;
3138
3139 --difference in Get_Po_Chg_Approval_Notif
3140
3141 -- fix bug 2733542
3142 -- because we insert an extra record at shipment level about the quantity
3143 -- change, so when we show the notification to buyer, we only need to
3144 -- get the new quantity from the shipment
3145 -- thus we add a new condition 'and pcr.request_level<>'DISTRIBUTION''
3146
3147 -- fix bug 2739962
3148 -- item: we get item id here, and will get the item from it when display
3149 -- reason: we add reason in the cursor
3150 cursor po_chg_req(grp_id number) is
3151 select pol.line_num,
3152 pol.po_line_id,
3153 pll.shipment_num,
3154 pol.item_id,
3155 pll.need_by_date old_need_by_date,
3156 pcr.new_need_by_date,
3157 nvl(pcr.old_price, nvl(pll.price_override, pol.unit_price)) old_price,
3158 pcr.new_price new_price,
3159 pol.quantity,
3160 pll.quantity old_quantity,
3161 pcr.old_quantity change_old_quantity,
3162 pcr.new_quantity change_new_quantity,
3163 pcr.action_type,
3164 pol.item_description,
3165 pol.unit_meas_lookup_code,
3166 pll.unit_meas_lookup_code,
3167 hla.location_code,
3168 pcr.request_reason,
3169 pol.org_id,
3170 nvl(pcr.old_start_date, pol.start_date) old_start_date,
3171 pcr.new_start_date,
3172 nvl(pcr.old_expiration_date, pol.expiration_date) old_end_date,
3173 pcr.new_expiration_date,
3174 nvl(pcr.old_amount,
3175 decode(pcr.document_line_location_id,
3176 null, pol.amount,
3177 nvl(pll.amount,
3178 (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))
3179 *pll.price_override))) old_amount,
3180 nvl(pcr.new_amount,
3181 decode(pcr.document_line_location_id,
3182 null, null, /* the calcuated amount will show at shipment level */
3183 PO_ReqChangeRequestNotif_PVT.get_goods_shipment_new_amount(grp_id,
3184 pol.po_line_id, pcr.document_line_location_id,
3185 nvl(pcr.old_price, nvl(pll.price_override, pol.unit_price)),
3186 (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0)))))
3187 from po_change_requests pcr,
3188 po_lines_all pol,
3189 po_line_locations_all pll,
3190 hr_locations_all hla
3191 where pcr.change_request_group_id=grp_id
3192 and pcr.request_status = 'PENDING'
3193 and pcr.document_header_id=pol.po_header_id
3194 and pcr.document_line_id=pol.po_line_id
3195 and nvl(pcr.document_line_location_id, -1)=pll.line_location_id(+)
3196 and pll.ship_to_location_id=hla.location_id(+)
3197 and pcr.request_level<>'DISTRIBUTION'
3198 order by line_num, nvl(shipment_num, 0);
3199 cursor l_get_document_id_csr(p_group_id in number) is
3200 select document_header_id, po_release_id
3201 from po_change_requests
3202 where change_request_group_id=p_group_id;
3203
3204 cursor l_get_line_qty(grp_id number, line_id number) is
3205 select sum(decode(pcr.action_type, 'CANCELLATION', 0,
3206 nvl(pcr.new_quantity, pll.quantity)))
3207 from po_change_requests pcr,
3208 po_line_locations_all pll
3209 where pcr.change_request_group_id(+)=grp_id
3210 and pcr.document_line_id(+)=line_id
3211 and pcr.document_line_location_id(+)=pll.line_location_id
3212 and pcr.request_level(+)='SHIPMENT'
3213 and pll.po_line_id=line_id
3214 and nvl(pll.cancel_flag,'N') <> 'Y'
3215 and nvl(pll.closed_code,'OPEN') not in('FINALLY CLOSED');
3216
3217 cursor l_get_pb_info(grp_id number, line_id number) is
3218 select pll.ship_to_location_id,
3219 nvl(pcr.new_need_by_date, pll.need_by_date),
3220 poh.currency_code,
3221 poh.rate_type,
3222 nvl(pcr.action_type, 'A'),
3223 poh.vendor_id,
3224 poh.vendor_site_id,
3225 poh.creation_date,
3226 poh.po_header_id,
3227 pol.po_line_id,
3228 pol.line_type_id,
3229 pol.item_revision,
3230 pol.category_id,
3231 pol.VENDOR_PRODUCT_NUM,
3232 nvl(pol.base_unit_price, pol.unit_price),
3233 nvl(pll.quantity_received,0),
3234 nvl(pll.accrue_on_receipt_flag,'N'),
3235 nvl(pll.quantity_billed,0)
3236 from po_lines_all pol,
3237 po_headers_all poh,
3238 po_line_locations_all pll,
3239 po_change_requests pcr
3240 where pol.po_line_id=line_id
3241 and pol.po_header_id=poh.po_header_id
3242 and pll.po_line_id=line_id
3243 and pll.line_location_id=pcr.document_line_location_id(+)
3244 and pcr.request_level(+)='SHIPMENT'
3245 and grp_id=pcr.change_request_group_id(+);
3246
3247 l_action_type1 varchar2(30);
3248 l_blanket_header_id number;
3249 l_blanket_line_id number;
3250 l_blanket_line_num number;
3251 l_line_total_qty number;
3252 l_ship_to_loc_id number;
3253 l_currency_code po_headers_all.currency_code%type;
3254 l_rate_type po_headers_all.rate_type%type;
3255 l_ship_need_by DATE;
3256 l_pb_new_curr_price number;
3257 l_pb_discount number;
3258 l_pb_currency_code po_headers_all.currency_code%type;
3259 l_pb_rate_type po_headers_all.rate_type%type;
3260 l_pb_rate_date date;
3261 l_pb_rate number;
3262
3263 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
3264 NL VARCHAR2(1) := fnd_global.newline;
3265
3266 l_has_temp_labor boolean :=false;
3267 l_num_temp_labors number :=0;
3268 l_old_start_date po_line_locations_all.start_date%TYPE;
3269 l_new_start_date po_line_locations_all.start_date%TYPE;
3270 l_old_end_date po_line_locations_all.end_date%TYPE;
3271 l_new_end_date po_line_locations_all.end_date%TYPE;
3272 l_old_amount po_line_locations_all.amount%TYPE;
3273 l_new_amount po_line_locations_all.amount%TYPE;
3274
3275 -- added for retroactive pricing checks
3276 l_retropricing varchar2(20) := '';
3277 l_quantity_received number;
3278 l_accrue_on_receipt_flag po_line_locations_all.accrue_on_receipt_flag%type;
3279 l_quantity_billed number;
3280 l_call_price_break boolean := true;
3281
3282
3283 BEGIN
3284 l_retropricing := fnd_profile.value('PO_ALLOW_RETROPRICING_OF_PO');
3285
3286 l_grp_id := to_number(document_id);
3287 open l_get_document_id_csr(l_grp_id);
3288 fetch l_get_document_id_csr into l_header_id, l_release_id;
3289 close l_get_document_id_csr;
3290
3291 if(l_grp_id is not null) then
3292 select count(1)
3293 into l_num_of_changes
3294 from (select distinct document_line_id, document_line_location_id
3295 from po_change_requests
3296 where change_request_group_id = l_grp_id
3297 and action_type = 'MODIFICATION'
3298 and request_status='PENDING');
3299
3300 select count(1) into l_num_of_cancels
3301 from po_change_requests
3302 where change_request_group_id = l_grp_id
3303 and action_type = 'CANCELLATION'
3304 and request_status='PENDING';
3305 end if;
3306
3307 if(l_release_id is null) then
3308 l_document_type := fnd_message.get_string('PO','PO_WF_NOTIF_STD_PO');
3309 select segment1, revision_num, pos_totals_po_sv.get_po_total(po_header_id), currency_code,
3310 vendor_id, vendor_site_id, creation_date, fob_lookup_code,
3311 ship_via_lookup_code, ship_to_location_id, acceptance_required_flag,type_lookup_code
3312 into
3313 l_po_num, l_revision_num, l_po_total, l_po_currency,
3314 l_vendor_id,l_vendor_site_id,l_order_date, l_fob,
3315 l_carrier, l_ship_to_id, l_acceptance_required_flag,l_type_lookup_code
3316 from po_headers_all
3317 where po_header_id = l_header_id;
3318
3319 if(l_type_lookup_code = 'STANDARD') then
3320 l_document_type := fnd_message.get_string('PO','PO_WF_NOTIF_STD_PO');
3321 elsif(l_type_lookup_code = 'PLANNED') then
3322 l_document_type := fnd_message.get_string('PO','PO_WF_NOTIF_PLAN_PO');
3323 elsif(l_type_lookup_code = 'BLANKET') then
3324 l_document_type := fnd_message.get_string('PO','PO_WF_NOTIF_BLANKET');
3325 else
3326 l_document_type := 'Error';
3327 end if;
3328
3329 if(l_acceptance_required_flag = 'Y') then
3330 l_po_doc_id := l_header_id;
3331 else
3332 l_po_doc_id := -1;
3333 end if;
3334 else
3335
3336 select ph.segment1, pr.release_num, pr.revision_num, pos_totals_po_sv.get_release_total(pr.po_release_id), ph.currency_code,
3337 ph.vendor_id, ph.vendor_site_id, pr.creation_date, ph.fob_lookup_code,
3338 ph.ship_via_lookup_code, ph.ship_to_location_id, pr.acceptance_required_flag,ph.type_lookup_code
3339 into
3340 l_blanket_num, l_release_num, l_revision_num, l_po_total, l_po_currency,
3341 l_vendor_id,l_vendor_site_id,l_order_date, l_fob,
3342 l_carrier, l_ship_to_id, l_acceptance_required_flag,l_type_lookup_code
3343 from po_releases_all pr, po_headers_all ph
3344 where pr.po_release_id = l_release_id
3345 and pr.po_header_id = ph.po_header_id;
3346
3347 if(l_type_lookup_code = 'BLANKET') then
3348 l_document_type := fnd_message.get_string('PO','PO_WF_NOTIF_BKT_REL');
3349 elsif(l_type_lookup_code = 'PLANNED') then
3350 l_document_type := fnd_message.get_string('PO','PO_WF_NOTIF_SCH_REL');
3351 else
3352 l_document_type := 'Error';
3353 end if;
3354
3355 if(l_acceptance_required_flag = 'Y') then
3356 l_rel_doc_id := l_release_id;
3357 else
3358 l_rel_doc_id := -1;
3359 end if;
3360
3361 end if;
3362
3363 select vendor_name into l_supplier_name from po_vendors where vendor_id = l_vendor_id;
3364 select address_line1, address_line2, address_line3, city,state,zip
3365 into l_sup_address_line1, l_sup_address_line2, l_sup_address_line3, l_sup_city,
3366 l_sup_state,l_sup_zip
3367 from po_vendor_sites_all
3368 where vendor_site_id = l_vendor_site_id;
3369
3370 select address_line_1, address_line_2, address_line_3, town_or_city, region_1, postal_code
3371 into l_ship_addr_l1, l_ship_addr_l2, l_ship_addr_l3, l_ship_city, l_ship_state, l_ship_zip
3372 from hr_locations_all
3373 where location_id = l_ship_to_id;
3374
3375 if (display_type = 'text/html') then
3376
3377 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_href|| '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
3378
3379 l_document := l_document || NL || '<!-- PO CHANGE SUMMARY -->'|| NL || NL || '<P>';
3380
3381 if(l_release_id is null) then
3382 l_document := l_document || PrintHeading(l_document_type||' '||l_po_num||' '||fnd_message.get_string('PO','PO_WF_NOTIF_REV')
3383 ||' '||l_revision_num||' ('||fnd_message.get_string('PO','PO_WF_NOTIF_TOTAL')||'('||l_po_currency||') '||to_char(l_po_total, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30))||') - '||fnd_message.get_string('PO','PO_WF_NOTIF_NUM_OF_CHN')||' - '||
3384 l_num_of_changes||' '||fnd_message.get_string('PO','PO_WF_NOTIF_CANCELED')||' - '||l_num_of_cancels);
3385 else
3386 l_document := l_document || PrintHeading(l_document_type||' '||l_blanket_num||'-'||l_release_num||' '||fnd_message.get_string('PO','PO_WF_NOTIF_REV')||' '
3387 ||l_revision_num||' ('||fnd_message.get_string('PO','PO_WF_NOTIF_TOTAL')||'('||
3388 l_po_currency||') '||l_po_total||') - '||fnd_message.get_string('PO','PO_WF_NOTIF_NUM_OF_CHN')||' - '||
3389 l_num_of_changes||' '||fnd_message.get_string('PO','PO_WF_NOTIF_CANCELED')||' - '||l_num_of_cancels);
3390
3391 end if;
3392
3393 l_document := l_document||'
3394 <TABLE width="100%" cellpadding=2 cellspacing=1>
3395 <TR>
3396 <TD width="4%"/>
3397 <TD width="48%">
3398 <TABLE cellpadding=2 cellspacing=1>
3399 <TR>
3400 <TD nowrap align=right><font color=black><B>
3401 '||fnd_message.get_string('PO','PO_WF_NOTIF_VENDOR')||'</B></font></TD>
3402 <TD nowrap align=left><font color=black>'||l_supplier_name||'</font></TD>
3403 </TR>
3404 <TR>
3405 <TD nowrap valign=TOP align=right><font color=black><B>
3406 '||fnd_message.get_string('PO','PO_WF_NOTIF_ADDRESS')||'</B></font></TD>
3407 <TD align=left><font color=black>'||l_sup_address_line1||' '||l_sup_address_line2||' '||
3408 l_sup_address_line3||'<BR>'||l_sup_city||' '||l_sup_state||' '||l_sup_zip||
3409 '</font></TD>
3410 </TR>';
3411
3412 if(not l_has_temp_labor) then
3413 l_document := l_document||'
3414 <TR>
3415 <TD nowrap align=right><font color=black><B>
3416 '||fnd_message.get_string('PO','PO_WF_NOTIF_FOB')||'</B></font></TD>
3417 <TD nowrap align=left><font color=black>'||l_fob||
3418 '</font></TD>
3419 </TR>
3420
3421
3422 <TR>
3423 <TD nowrap align=right><font color=black><B>
3424 '||fnd_message.get_string('PO','PO_WF_NOTIF_CARRIER')||'</B></font></TD>
3425 <TD nowrap align=left><font color=black>'||l_carrier||
3426 '</font></TD>
3427 </TR>';
3428 end if;
3429
3430 l_document := l_document||'
3431 </TABLE>
3432 </TD>
3433 <TD width="48%" valign=TOP>
3434 <TABLE cellpadding=2 cellspacing=1>
3435 <TR>
3436 <TD nowrap align=right><font color=black><B>
3437 '||fnd_message.get_string('PO','PO_WF_NOTIF_ORDER_DATE')||'</B></font></TD>
3438 <TD nowrap align=left><font color=black>'||l_order_date||
3439 '</font></TD>
3440 </TR>
3441
3442 <TR>
3443 <TD nowrap valign=TOP align=right><font color=black><B>
3444 '||fnd_message.get_string('PO','PO_WF_NOTIF_SHPTO_ADD')||'</B></font></TD>
3445 <TD align=left><font color=black>
3446 '||
3447 l_ship_addr_l1||' '||l_ship_addr_l2||' '||l_ship_addr_l3||'<BR>'||
3448 l_ship_city||' '||l_ship_state||' '||l_ship_zip
3449 ||'
3450 </font></TD>
3451 </TR>
3452
3453 </TABLE>
3454 </TD>
3455 </TR>
3456 </TABLE></P>';
3457
3458 else --text
3459
3460 if(l_release_id is null) then
3461 l_document := l_document || l_document_type ||' '||l_po_num||' '||fnd_message.get_string('PO','PO_WF_NOTIF_REV')
3462 ||' '||l_revision_num||' ('||fnd_message.get_string('PO','PO_WF_NOTIF_TOTAL')||'('||l_po_currency||') '||to_char(l_po_total, FND_CURRENCY.GET_FORMAT_MASK(l_po_currency, 30))||') - '||fnd_message.get_string('PO','PO_WF_NOTIF_NUM_OF_CHN')||' - '||
3463 l_num_of_changes||' '||fnd_message.get_string('PO','PO_WF_NOTIF_CANCELED')||' - '||l_num_of_cancels || NL;
3464 else
3465 l_document := l_document || l_document_type||' '||l_blanket_num||'-'||l_release_num||' '||fnd_message.get_string('PO','PO_WF_NOTIF_REV')||' '
3466 ||l_revision_num||' ('||fnd_message.get_string('PO','PO_WF_NOTIF_TOTAL')||'('||
3467 l_po_currency||') '||l_po_total||') - '||fnd_message.get_string('PO','PO_WF_NOTIF_NUM_OF_CHN')||' - '||
3468 l_num_of_changes||' '||fnd_message.get_string('PO','PO_WF_NOTIF_CANCELED')||' - '||l_num_of_cancels || NL;
3469
3470 end if;
3471
3472 l_document := l_document|| fnd_message.get_string('PO','PO_WF_NOTIF_VENDOR')||': ' || l_supplier_name|| NL;
3473 l_document := l_document || fnd_message.get_string('PO','PO_WF_NOTIF_ADDRESS')||': ' || l_sup_address_line1||' '||l_sup_address_line2||' '|| l_sup_address_line3||' '||l_sup_city||' '||l_sup_state||' '||l_sup_zip|| NL;
3474
3475 if(not l_has_temp_labor) then
3476 l_document := l_document|| fnd_message.get_string('PO','PO_WF_NOTIF_FOB')||': ' || l_fob|| NL;
3477 l_document := l_document|| fnd_message.get_string('PO','PO_WF_NOTIF_CARRIER')||': ' || l_carrier|| NL;
3478 end if;
3479
3480 l_document := l_document|| fnd_message.get_string('PO','PO_WF_NOTIF_ORDER_DATE')||': ' || l_order_date|| NL;
3481
3482 l_document := l_document|| fnd_message.get_string('PO','PO_WF_NOTIF_SHPTO_ADD')||': ' || l_ship_addr_l1||' '||l_ship_addr_l2||' '||l_ship_addr_l3||' '|| l_ship_city||' '||l_ship_state||' '||l_ship_zip || NL || NL;
3483
3484 end if;
3485
3486 WF_NOTIFICATION.WriteToClob(document,l_document);
3487
3488 begin
3489 select count(1)
3490 into l_num_temp_labors
3491 from po_change_requests pcr,
3492 po_lines_all pol
3493 where pcr.change_request_group_id=l_grp_id
3494 and pcr.request_status = 'PENDING'
3495 and pcr.document_header_id=pol.po_header_id
3496 and pcr.document_line_id=pol.po_line_id
3497 and pcr.request_level<>'DISTRIBUTION'
3498 and pol.purchase_basis ='TEMP LABOR';
3499
3500 exception
3501 when others then
3502 l_num_temp_labors := 0;
3503 end;
3504
3505 if (l_num_temp_labors > 0) then
3506 l_has_temp_labor := true;
3507 end if;
3508
3509 open po_chg_req(l_grp_id);
3510 fetch po_chg_req
3511 into
3512 l_line_num,
3513 l_line_id,
3514 l_ship_num,
3515 l_item_id,
3516 l_old_need_by_date,
3517 l_new_need_by_date,
3518 l_old_price,
3519 l_new_price,
3520 l_line_quantity,
3521 l_old_qty,
3522 l_change_old_qty,
3523 l_change_new_qty,
3524 l_action_type,
3525 l_item_desc,
3526 l_line_uom,
3527 l_uom,
3528 l_ship_to_location,
3529 l_request_reason,
3530 l_org_id,
3531 l_old_start_date,
3532 l_new_start_date,
3533 l_old_end_date,
3534 l_new_end_date,
3535 l_old_amount,
3536 l_new_amount;
3537 close po_chg_req;
3538
3539 if(l_line_num is not null) then
3540 if (display_type = 'text/html') then
3541 l_document := NL || NL || '<!-- CHANGE Details-->'||
3542 NL || NL || '<P>';
3543
3544 l_document := l_document || PrintHeading(fnd_message.get_string('PO','PO_WF_NOTIF_REQUEST_DETAILS'));
3545 l_document := l_document || '<TABLE width="100%" SUMMARY="">' || NL;
3546 l_document := l_document || '<TR>'|| NL;
3547 l_document:= l_document||'<TD class=instructiontext>'||'<img src='
3548 || l_base_href
3549 || '/OA_MEDIA/newupdateditem_status.gif ALT="">'
3550 || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_VALUE');
3551 l_document := l_document || '</TD></TR>' || NL;
3552 l_document := l_document || '<TR>'|| NL;
3553 l_document:= l_document||'<TD class=instructiontext>'||'<img src='
3554 || l_base_href
3555 || '/OA_MEDIA/cancelind_status.gif ALT="">'
3556 || fnd_message.get_string('PO', 'PO_WF_NOTIF_CANCEL_PENDING');
3557 l_document := l_document || '</TD></TR>' || NL;
3558 l_document := l_document || '</TABLE>' || NL;
3559
3560 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || 'summary=""> '|| NL;
3561 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3562 || ' id="lineNum_1">'
3563 || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER')
3564 || '</TH>' || NL;
3565 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3566 || ' id="ShipNum_1">'
3567 || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPMENT')
3568 || '</TH>' || NL;
3569 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3570 || ' id="Item_1">'
3571 || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM')
3572 || '</TH>' || NL;
3573 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3574 || ' id="Discription_1">'
3575 || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION')
3576 || '</TH>' || NL;
3577
3578 if(l_has_temp_labor) then
3579 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3580 || ' id="Price_1">'
3581 || fnd_message.get_string('PO', 'PO_WF_NOTIF_RATE')
3582 ||'(' ||l_po_currency|| ')</TH>' || NL;
3583 else
3584 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3585 || ' id="Price_1">'
3586 || fnd_message.get_string('PO', 'PO_WF_NOTIF_PRICE')
3587 ||'(' ||l_po_currency|| ')</TH>' || NL;
3588 end if;
3589
3590 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3591 || ' id="Unit_1">'
3592 || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT')
3593 || '</TH>' || NL;
3594 if(l_has_temp_labor) then
3595 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3596 || ' id="Start_1">'
3597 || fnd_message.get_string('PO', 'PO_WF_NOTIF_START_DATE')
3598 || '</TH>' || NL;
3599 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3600 || ' id="End_1">'
3601 || fnd_message.get_string('PO', 'PO_WF_NOTIF_END_DATE')
3602 || '</TH>' || NL;
3603 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3604 || ' id="Amount_1">'
3605 || fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT')
3606 ||'(' ||l_po_currency|| ')</TH>' || NL;
3607 else
3608 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3609 || ' id="Quantity_1">'
3610 || fnd_message.get_string('PO', 'PO_WF_NOTIF_QTY_ORDERED')
3611 || '</TH>' || NL;
3612 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3613 || ' id="Amount_1">'
3614 || fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT')
3615 ||'(' ||l_po_currency|| ')</TH>' || NL;
3616 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3617 || ' id="NeedBy_1">'
3618 || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEED_BY')
3619 || '</TH>' || NL;
3620 end if;
3621 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3622 || ' id="ShipTo_1">'
3623 || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPTO')
3624 || '</TH>' || NL;
3625 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE
3626 || ' id="Reason_1">'
3627 || fnd_message.get_string('PO', 'PO_WF_NOTIF_REASON')
3628 || '</TH>' || NL;
3629 l_document := l_document || '</TR>' || NL;
3630 else -- text
3631 l_document := fnd_message.get_string('PO','PO_WF_NOTIF_REQUEST_DETAILS') || NL || NL;
3632 end if;
3633
3634 --document:=document||l_document;
3635 WF_NOTIFICATION.WriteToClob(document,l_document);
3636 open po_chg_req(l_grp_id);
3637 loop
3638 fetch po_chg_req
3639 into
3640 l_line_num,
3641 l_line_id,
3642 l_ship_num,
3643 l_item_id,
3644 l_old_need_by_date,
3645 l_new_need_by_date,
3646 l_old_price,
3647 l_new_price,
3648 l_line_quantity,
3649 l_old_qty,
3650 l_change_old_qty,
3651 l_change_new_qty,
3652 l_action_type,
3653 l_item_desc,
3654 l_line_uom,
3655 l_uom,
3656 l_ship_to_location,
3657 l_request_reason,
3658 l_org_id,
3659 l_old_start_date,
3660 l_new_start_date,
3661 l_old_end_date,
3662 l_new_end_date,
3663 l_old_amount,
3664 l_new_amount;
3665
3666 EXIT when po_chg_req%NOTFOUND;
3667
3668 if(l_pre_line_id is null or l_line_id<>l_pre_line_id) then
3669 l_pre_line_id:=l_line_id;
3670
3671 open l_get_line_qty(l_grp_id, l_line_id);
3672 fetch l_get_line_qty into l_line_total_qty;
3673 close l_get_line_qty;
3674
3675 --using distinct as a line on a blanket can exist on multiple requisitions
3676 select
3677 distinct prl.blanket_po_header_id,
3678 prl.blanket_po_line_num
3679 into
3680 l_blanket_header_id, l_blanket_line_num
3681 from
3682 po_requisition_lines_all prl,
3683 po_line_locations_all pll,
3684 po_lines_all pol
3685 where
3686 pol.po_line_id = l_line_id and
3687 pol.po_line_id = pll.po_line_id and
3688 prl.line_location_id = pll.line_location_id;
3689
3690 open l_get_pb_info(l_grp_id, l_line_id);
3691 loop
3692 fetch l_get_pb_info
3693 into l_ship_to_loc_id,
3694 l_ship_need_by,
3695 l_currency_code,
3696 l_rate_type,
3697 l_action_type1,
3698 l_supplier_id,
3699 l_supplier_site_id,
3700 l_creation_date,
3701 l_po_header_id,
3702 l_po_line_id,
3703 l_line_type_id,
3704 l_item_revision,
3705 l_category_id,
3706 l_supplier_item_num,
3707 l_base_unit_price,
3708 l_quantity_received,
3709 l_accrue_on_receipt_flag,
3710 l_quantity_billed;
3711
3712 exit when l_get_pb_info%NOTFOUND
3713 or l_action_type1<>'CANCELLATION';
3714 end loop;
3715 if(l_get_pb_info%FOUND and l_blanket_header_id is not null ) then
3716
3717 IF (l_retropricing = 'ALL_RELEASES') THEN
3718 l_call_price_break := true;
3719 ELSE
3720 IF ((l_quantity_received > 0 AND
3721 l_accrue_on_receipt_flag = 'Y') OR
3722 (l_quantity_billed > 0)) THEN
3723 l_call_price_break := false;
3724 END IF;
3725 END IF;
3726
3727 IF (l_call_price_break) THEN
3728
3729 po_price_break_grp.Get_Price_Break (
3730 P_SOURCE_DOCUMENT_HEADER_ID => l_blanket_header_id,
3731 P_SOURCE_DOCUMENT_LINE_NUM => l_blanket_line_num,
3732 P_IN_QUANTITY => l_line_total_qty,
3733 P_UNIT_OF_MEASURE => l_line_uom,
3734 P_DELIVER_TO_LOCATION_ID => l_ship_to_loc_id,
3735 P_REQUIRED_CURRENCY => l_currency_code,
3736 P_REQUIRED_RATE_TYPE => l_rate_type,
3737 P_NEED_BY_DATE => l_ship_need_by,
3738 P_DESTINATION_ORG_ID => l_org_id,
3739 P_ORG_ID =>l_org_id,
3740 P_SUPPLIER_ID => l_supplier_id,
3741 P_SUPPLIER_SITE_ID => l_supplier_site_id,
3742 P_CREATION_DATE => l_creation_date,
3743 P_ORDER_HEADER_ID => l_po_header_id,
3744 P_ORDER_LINE_ID => l_po_line_id,
3745 P_LINE_TYPE_ID => l_line_type_id,
3746 P_ITEM_REVISION => l_item_revision,
3747 P_ITEM_ID => l_item_id,
3748 P_CATEGORY_ID => l_category_id,
3749 P_SUPPLIER_ITEM_NUM => l_supplier_item_num,
3750 P_IN_PRICE => l_base_unit_price,
3751 --Below is OUTPUT
3752 X_BASE_UNIT_PRICE => l_pb_base_unit_price,
3753 X_BASE_PRICE => l_pb_new_price,
3754 X_CURRENCY_PRICE => l_pb_new_curr_price,
3755 X_DISCOUNT => l_pb_discount,
3756 X_CURRENCY_CODE => l_pb_currency_code,
3757 X_RATE_TYPE => l_pb_rate_type,
3758 X_RATE_DATE => l_pb_rate_date,
3759 X_RATE => l_pb_rate,
3760 X_PRICE_BREAK_ID => l_pb_break_id);
3761
3762
3763 end if;
3764 else
3765 l_pb_new_price:=l_old_price;
3766 end if;
3767 close l_get_pb_info;
3768
3769 end if;
3770 if(l_ship_num is null) then
3771 l_old_qty:=l_line_quantity;
3772 end if;
3773 -- fix bug 2733542,
3774 -- since the we add a new record at shipment level of the new quantity,
3775 -- we can always display the shipment directly,
3776 -- no need to go through the distribution to calculate the
3777 -- quantity
3778 GetPoLineShipment(l_line_num,
3779 l_ship_num,
3780 l_item_id,
3781 l_org_id,
3782 l_old_need_by_date,
3783 l_new_need_by_date,
3784 l_old_price,
3785 l_new_price,
3786 l_po_currency,
3787 l_old_qty,
3788 l_change_new_qty,
3789 l_action_type,
3790 l_item_desc,
3791 l_line_uom,
3792 l_ship_to_location,
3793 l_request_reason,
3794 l_old_start_date,
3795 l_new_start_date,
3796 l_old_end_date,
3797 l_new_end_date,
3798 l_old_amount,
3799 l_new_amount,
3800 l_has_temp_labor,
3801 display_type,
3802 l_document);
3803 WF_NOTIFICATION.WriteToClob(document,l_document);
3804 END LOOP;
3805 CLOSE PO_CHG_REQ;
3806
3807 if (display_type = 'text/html' ) then
3808 WF_NOTIFICATION.WriteToClob(document, '</TABLE></P>');
3809 end if;
3810
3811 end if;
3812 END Get_Po_Chg_Approval_Notif;
3813
3814
3815 Procedure get_additional_details(p_req_header_id in number,
3816 p_document out NOCOPY varchar2) is
3817
3818 l_document varchar2(4000);
3819 l_req_details_url varchar2(1000);
3820 NL VARCHAR2(1) := fnd_global.newline;
3821
3822
3823 begin
3824 l_document := '<TABLE width="100%" SUMMARY="">' || NL;
3825
3826 -- fix bug 2373901, the link url
3827 l_req_details_url := '<a href="'|| FND_WEB_CONFIG.JSP_AGENT||'OA.jsp?OAFunc=ICXPOR_CHO_HISTORY_PAGE&ReqHeaderId='||to_char(p_req_header_id)||'&ChangeHistoryOrigin=Notification">' ||
3828 fnd_message.get_string('PO', 'PO_WF_NOTIF_ADDITIONAL_REQ_CHG') || '</a>';
3829
3830 l_document := l_document || '<TR>'|| NL;
3831 l_document := l_document || '<TD align=right>'|| l_req_details_url ;
3832
3833
3834 l_document := l_document || '</TD></TR>' || NL;
3835
3836 l_document := l_document || '</TABLE>';
3837
3838 p_document:=l_document;
3839
3840 end;
3841
3842
3843 /*
3844 PROCEDURE get_new_req_amount(document_id IN varchar2,
3845 display_type in Varchar2,
3846 document in out nocopy varchar2,
3847 document_type in out nocopy varchar2)
3848 is
3849 l_item_type wf_items.item_type%type;
3850 l_item_key wf_items.item_key%type;
3851 begin
3852 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
3853 l_item_key := substr(document_id, instr(document_id, ':') + 1,
3854 length(document_id) - 2);
3855
3856 document:=wf_engine.GetItemAttrText
3857 (itemtype => l_item_type,
3858 itemkey => l_item_key,
3859 aname => 'NEW_REQ_AMOUNT_CURRENCY_DSP');
3860
3861 end get_new_req_amount;
3862 */
3863
3864
3865 /*************************************************************************
3866 * Public Procedure: Get_PO_Price_Break_Grp
3867 *
3868 * Effects: Returns the Price Break value
3869 *
3870 ************************************************************************/
3871 FUNCTION Get_PO_Price_Break_Grp(p_org_id in number,
3872 p_group_id in number,
3873 p_line_id in number,
3874 p_item_id in number,
3875 p_line_uom in varchar2,
3876 p_old_price in number,
3877 p_line_location_id in number) RETURN number
3878 IS
3879
3880 l_supplier_id po_headers_all.vendor_id%TYPE;
3881 l_supplier_site_id po_headers_all.vendor_site_id%TYPE;
3882 l_creation_date po_headers_all.creation_date%TYPE;
3883 l_po_header_id po_headers_all.po_header_id%TYPE;
3884 l_po_line_id po_lines_all.po_line_id%TYPE;
3885 l_line_type_id po_lines_all.line_type_id%TYPE;
3886 l_item_revision po_lines_all.item_revision%TYPE;
3887 l_category_id po_lines_all.category_id%TYPE;
3888 l_supplier_item_num po_lines_all.VENDOR_PRODUCT_NUM%TYPE;
3889 l_base_unit_price po_lines_all.base_unit_price%TYPE;
3890
3891 l_action_type1 varchar2(30);
3892 l_blanket_header_id number;
3893 l_blanket_line_id number;
3894 l_blanket_line_num number;
3895 l_line_total_qty number;
3896 l_ship_to_loc_id number;
3897 l_currency_code po_headers_all.currency_code%type;
3898 l_rate_type po_headers_all.rate_type%type;
3899 l_ship_need_by DATE;
3900 l_pb_new_curr_price number;
3901 l_pb_discount number;
3902 l_pb_currency_code po_headers_all.currency_code%type;
3903 l_pb_rate_type po_headers_all.rate_type%type;
3904 l_pb_rate_date date;
3905 l_pb_rate number;
3906 l_pb_base_unit_price number;
3907 l_pb_break_id number;
3908 l_pb_new_price number;
3909 l_from_line_id number;
3910 l_contract_id number;
3911 l_from_advanced_pricing varchar2(100);
3912 l_return_status varchar2(100);
3913
3914
3915 l_retropricing varchar2(20) := '';
3916 l_quantity_received number;
3917 l_accrue_on_receipt_flag po_line_locations_all.accrue_on_receipt_flag%type;
3918 l_quantity_billed number;
3919 l_call_price_break boolean := true;
3920 l_new_amount number;
3921 l_document_type varchar2(100);
3922
3923 l_cumulative_flag BOOLEAN;
3924 l_release_shipment_quantity number;
3925 l_price_break_type varchar2(100);
3926
3927 l_progress varchar2(200);
3928 l_api_name varchar2(50):= 'Get_PO_Price_Break_Grp';
3929
3930
3931 -- This cursor is used for getting quantity for a SPO line
3932 cursor l_get_line_qty(grp_id number, line_id number) is
3933 select sum(decode(pcr.action_type, 'CANCELLATION', 0,
3934 nvl(pcr.new_quantity, pll.quantity)))
3935 from po_change_requests pcr,
3936 po_line_locations_all pll
3937 where pcr.change_request_group_id = grp_id
3938 and pcr.document_line_id = line_id
3939 and pcr.document_line_location_id = pll.line_location_id
3940 and pcr.request_level = 'SHIPMENT'
3941 and pll.po_line_id = line_id
3942 and nvl(pll.cancel_flag,'N') <> 'Y'
3943 and nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED');
3944
3945 -- This cursor is used for getting price break information for a SPO line
3946 cursor l_get_pb_info(grp_id number, line_id number) is
3947 select pll.ship_to_location_id,
3948 nvl(pcr.new_need_by_date, pll.need_by_date),
3949 poh.currency_code,
3950 poh.rate_type,
3951 nvl(pcr.action_type, 'A'),
3952 poh.vendor_id,
3953 poh.vendor_site_id,
3954 poh.creation_date,
3955 pol.po_header_id,
3956 pol.po_line_id,
3957 pol.line_type_id,
3958 pol.item_revision,
3959 pol.category_id,
3960 pol.VENDOR_PRODUCT_NUM,
3961 nvl(pol.base_unit_price, pol.unit_price),
3962 nvl(pll.quantity_received,0),
3963 nvl(pll.accrue_on_receipt_flag,'N'),
3964 nvl(pll.quantity_billed,0),
3965 pol.from_line_id,
3966 pol.contract_id
3967 from po_lines_all pol,
3968 po_headers_all poh,
3969 po_line_locations_all pll,
3970 po_change_requests pcr
3971 where pol.po_line_id=line_id
3972 and pol.po_header_id=poh.po_header_id
3973 and pll.po_line_id=line_id
3974 and pll.line_location_id=pcr.document_line_location_id
3975 and pcr.request_level = 'SHIPMENT'
3976 and grp_id=pcr.change_request_group_id;
3977
3978 BEGIN
3979
3980 -- first we check p_line_location_id. It shouldn't be null.
3981 if ( p_line_location_id is null ) then
3982 if g_fnd_debug = 'Y' then
3983 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3984 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'line location id is null,return');
3985 END IF;
3986 end if;
3987
3988 return null;
3989
3990 end if;
3991
3992 l_retropricing := fnd_profile.value('PO_ALLOW_RETROPRICING_OF_PO');
3993
3994 l_progress:='001';
3995 if g_fnd_debug = 'Y' then
3996 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3997 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
3998 END IF;
3999 end if;
4000
4001 -- Since WF notification can't set the org context, we need to set it here.
4002 PO_MOAC_UTILS_PVT.set_policy_context('S', p_org_id);
4003
4004 open l_get_line_qty(p_group_id, p_line_id);
4005 fetch l_get_line_qty into l_line_total_qty;
4006 close l_get_line_qty;
4007
4008 l_progress:='002';
4009 if g_fnd_debug = 'Y' then
4010 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4011 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4012 END IF;
4013 end if;
4014
4015 begin
4016 select distinct pcr.document_type
4017 into l_document_type
4018 from po_change_requests pcr
4019 where pcr.change_request_group_id=p_group_id
4020 and pcr.document_line_id= p_line_id
4021 and pcr.document_line_location_id = p_line_location_id;
4022
4023 exception
4024 when others then
4025 if g_fnd_debug = 'Y' then
4026 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
4027 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
4028 l_api_name, sqlerrm);
4029 END IF;
4030 end if;
4031 raise;
4032 end;
4033
4034 l_progress:='003';
4035 if g_fnd_debug = 'Y' then
4036 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4037 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4038 END IF;
4039 end if;
4040
4041 if ( l_document_type = 'PO') then
4042 open l_get_pb_info(p_group_id, p_line_id);
4043
4044 loop
4045 fetch l_get_pb_info
4046 into l_ship_to_loc_id,
4047 l_ship_need_by,
4048 l_currency_code,
4049 l_rate_type,
4050 l_action_type1,
4051 l_supplier_id,
4052 l_supplier_site_id,
4053 l_creation_date,
4054 l_po_header_id,
4055 l_po_line_id,
4056 l_line_type_id,
4057 l_item_revision,
4058 l_category_id,
4059 l_supplier_item_num,
4060 l_base_unit_price,
4061 l_quantity_received,
4062 l_accrue_on_receipt_flag,
4063 l_quantity_billed,
4064 l_from_line_id,
4065 l_contract_id;
4066
4067 exit when l_get_pb_info%NOTFOUND
4068 or l_action_type1<>'CANCELLATION';
4069 end loop;
4070
4071 l_progress:='004';
4072 if g_fnd_debug = 'Y' then
4073 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4074 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4075 END IF;
4076 end if;
4077
4078
4079 select
4080 prl.blanket_po_header_id
4081 into
4082 l_blanket_header_id
4083 from
4084 po_requisition_lines_all prl
4085 where
4086 prl.line_location_id = p_line_location_id;
4087
4088 -- bug 5504366
4089 -- Req with source document can be created into SPO without any source document;
4090 -- PO_SOURCING2_SV.get_break_price shouldn't be called if both l_from_line_id and l_contract_id are null.
4091
4092 IF (l_get_pb_info%FOUND and l_blanket_header_id is not null and (l_from_line_id is not null or l_contract_id is not null) ) THEN
4093
4094 IF (l_retropricing = 'ALL_RELEASES') THEN
4095 l_call_price_break := true;
4096
4097 ELSE
4098 IF ((l_quantity_received > 0 AND
4099 l_accrue_on_receipt_flag = 'Y') OR
4100 (l_quantity_billed > 0)) THEN
4101 l_call_price_break := false;
4102 END IF;
4103 END IF;
4104
4105 l_progress:='005';
4106 if g_fnd_debug = 'Y' then
4107 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4108 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4109 END IF;
4110 end if;
4111
4112 IF (l_call_price_break) THEN
4113 -- call PO price break function
4114 PO_SOURCING2_SV.get_break_price
4115 ( p_api_version => 1.0
4116 , p_order_quantity => l_line_total_qty
4117 , p_ship_to_org => p_org_id
4118 , p_ship_to_loc => l_ship_to_loc_id
4119 , p_po_line_id => l_from_line_id
4120 , p_cum_flag => FALSE
4121 , p_need_by_date => l_ship_need_by
4122 , p_line_location_id => p_line_location_id
4123 -- <FPJ Advanced Price START>
4124 , p_contract_id => l_contract_id
4125 , p_org_id => p_org_id
4126 , p_supplier_id => l_supplier_id
4127 , p_supplier_site_id => l_supplier_site_id
4128 , p_creation_date => l_creation_date
4129 , p_order_header_id => l_po_header_id
4130 , p_order_line_id => l_po_line_id
4131 , p_line_type_id => l_line_type_id
4132 , p_item_revision => l_item_revision
4133 , p_item_id => p_item_id
4134 , p_category_id => l_category_id
4135 , p_supplier_item_num => l_supplier_item_num
4136 , p_in_price => l_base_unit_price
4137 , p_uom => p_line_uom
4138 , p_currency_code => l_currency_code
4139 , x_base_unit_price => l_pb_base_unit_price
4140 -- <FPJ Advanced Price END>
4141 , x_price_break_id => l_pb_break_id
4142 , x_price => l_pb_new_price
4143 , x_from_advanced_pricing => l_from_advanced_pricing
4144 , x_return_status => l_return_status
4145 );
4146
4147 l_progress:='006';
4148 if g_fnd_debug = 'Y' then
4149 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4150 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4151 END IF;
4152 end if;
4153
4154 ELSE
4155 l_pb_new_price:=p_old_price;
4156
4157 END IF; -- for l_call_price_break is true
4158
4159 ELSE
4160 l_pb_new_price:=p_old_price;
4161
4162 END IF; -- for pb_info found
4163
4164 close l_get_pb_info;
4165
4166 elsif ( l_document_type = 'RELEASE') then
4167 SELECT NVL(pcr.new_quantity, PLL.quantity),
4168 PLL.ship_to_location_id,
4169 nvl(pcr.new_need_by_date, pll.need_by_date),
4170 PLL.po_line_id,
4171 POL.price_break_lookup_code,
4172 nvl(pll.quantity_received,0),
4173 nvl(pll.accrue_on_receipt_flag,'N'),
4174 nvl(pll.quantity_billed,0)
4175 INTO l_release_shipment_quantity,
4176 l_ship_to_loc_id,
4177 l_ship_need_by,
4178 l_from_line_id,
4179 l_price_break_type,
4180 l_quantity_received,
4181 l_accrue_on_receipt_flag,
4182 l_quantity_billed
4183 FROM po_lines_all pol,
4184 po_line_locations_all pll,
4185 po_change_requests pcr
4186 WHERE pcr.change_request_group_id = p_group_id
4187 and pll.line_location_id = p_line_location_id
4188 and pcr.document_line_location_id = pll.line_location_id
4189 and pll.po_line_id = pol.po_line_id
4190 and pcr.request_level(+)='SHIPMENT' ;
4191
4192 l_progress:='007';
4193 if g_fnd_debug = 'Y' then
4194 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4195 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4196 END IF;
4197 end if;
4198
4199 -- True if price break type is CUMULATIVE, false otherwise:
4200 l_cumulative_flag := (l_price_break_type = 'CUMULATIVE');
4201
4202 IF (l_retropricing = 'ALL_RELEASES') THEN
4203 l_call_price_break := true;
4204
4205 ELSE
4206 IF ((l_quantity_received > 0 AND
4207 l_accrue_on_receipt_flag = 'Y') OR
4208 (l_quantity_billed > 0)) THEN
4209 l_call_price_break := false;
4210 END IF;
4211 END IF;
4212
4213 l_progress:='008';
4214 if g_fnd_debug = 'Y' then
4215 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4216 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4217 END IF;
4218 end if;
4219
4220 IF (l_call_price_break) THEN
4221
4222 PO_SOURCING2_SV.get_break_price(
4223 p_api_version => 1.0,
4224 p_order_quantity => l_release_shipment_quantity,
4225 p_ship_to_org => p_org_id,
4226 p_ship_to_loc => l_ship_to_loc_id,
4227 p_po_line_id => l_from_line_id ,
4228 p_cum_flag => l_cumulative_flag,
4229 p_need_by_date => l_ship_need_by,
4230 p_line_location_id => p_line_location_id,
4231 x_price_break_id => l_pb_break_id,
4232 x_price => l_pb_new_price,
4233 x_return_status => l_return_status
4234 );
4235
4236 ELSE
4237 l_pb_new_price:=p_old_price;
4238
4239 END IF;
4240
4241 end if; -- for l_document_type is PO/ RELEASE
4242
4243 return l_pb_new_price;
4244
4245 EXCEPTION
4246 WHEN OTHERS THEN
4247 if g_fnd_debug = 'Y' then
4248 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
4249 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
4250 l_api_name, sqlerrm);
4251 END IF;
4252 end if;
4253
4254 raise;
4255
4256 END Get_PO_Price_Break_Grp;
4257
4258 /*************************************************************************
4259 * Public Procedure: Get_Price
4260 *
4261 * Effects: Returns the Price value
4262 *
4263 ************************************************************************/
4264 FUNCTION Get_Price(p_org_id in number,
4265 p_group_id in number,
4266 p_line_id in number,
4267 p_item_id in number,
4268 p_line_uom in varchar2,
4269 p_old_price in number,
4270 p_line_location_id in number) RETURN number
4271 IS
4272
4273
4274 l_blanket_po_header_id number;
4275 l_price number;
4276
4277 l_progress varchar2(100);
4278 l_api_name varchar2(50):= 'Get_Price';
4279
4280 l_po_in_txn_curr varchar2(1):='N';
4281 l_rate number;
4282 l_po_matching_basis varchar2(100);
4283 l_po_order_type varchar2(20);
4284 l_po_header_id number;
4285 l_pcr_old_price number;
4286
4287 l_pol_unit_price number;
4288
4289 BEGIN
4290
4291 l_progress:='001';
4292 if g_fnd_debug = 'Y' then
4293 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4294 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4295 END IF;
4296 end if;
4297
4298
4299 -- for CPA/BPA req lines, quantity change can cause price change. In such case, we need to call price break function to get the correct price.
4300
4301 -- This kind of price change only happens at 'shipment' level ( where p_line_location_id is not null). So for shipment level rows, we check their blanket_po_header_id to decide whether we should get old_price by price break function.
4302
4303 l_blanket_po_header_id := null;
4304
4305 if ( p_line_location_id is not null ) then
4306 begin
4307
4308 --using distinct as a line on a blanket can exist on multiple requisitions
4309 select distinct prl.blanket_po_header_id
4310 into l_blanket_po_header_id
4311 from po_requisition_lines_all prl,
4312 po_line_locations_all pll,
4313 po_lines_all pol
4314 where pol.po_line_id = p_line_id
4315 and pol.po_line_id = pll.po_line_id
4316 and prl.line_location_id = pll.line_location_id
4317 and pll.line_location_id = p_line_location_id;
4318
4319 exception
4320 when NO_DATA_FOUND then
4321 l_blanket_po_header_id := null;
4322 end;
4323
4324 end if;
4325
4326 l_progress:='002';
4327 if g_fnd_debug = 'Y' then
4328 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4329 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4330 END IF;
4331 end if;
4332
4333 if (l_blanket_po_header_id is null) then
4334 -- if l_blanket_po_header_id is null, it is a line level row or it is a shipment level row but with no source document related.
4335 -- We don't need to call price break function to get the old price
4336
4337 if ( p_line_location_id is null ) then
4338
4339 select pcr.old_price, nvl(pcr.old_price,nvl(pll.price_override, pol.unit_price))
4340 into l_pcr_old_price, l_price
4341 FROM po_change_requests pcr,
4342 po_lines_all pol,
4343 po_line_locations_all pll,
4344 po_headers_all poh
4345 WHERE pcr.change_request_group_id= p_group_id
4346 AND pcr.request_status IN ('PENDING', 'BUYER_APP', 'ACCEPTED', 'REJECTED')
4347 AND pol.po_line_id = p_line_id
4348 AND pll.line_location_id is null
4349 AND pcr.document_header_id=pol.po_header_id
4350 AND pcr.document_line_id=pol.po_line_id
4351 AND nvl(pcr.document_line_location_id,-1)=pll.line_location_id(+)
4352 AND pcr.request_level<>'DISTRIBUTION'
4353 AND pol.from_header_id=poh.po_header_id(+);
4354
4355 else
4356 select pcr.old_price, pol.unit_price,nvl(pcr.old_price,nvl(pll.price_override, pol.unit_price))
4357 into l_pcr_old_price,l_pol_unit_price,l_price
4358 FROM po_change_requests pcr,
4359 po_lines_all pol,
4360 po_line_locations_all pll,
4361 po_headers_all poh
4362 WHERE pcr.change_request_group_id= p_group_id
4363 AND pcr.request_status IN ('PENDING', 'BUYER_APP', 'ACCEPTED', 'REJECTED')
4364 AND pol.po_line_id = p_line_id
4365 AND pll.line_location_id = p_line_location_id
4366 AND pcr.document_header_id=pol.po_header_id
4367 AND pcr.document_line_id=pol.po_line_id
4368 AND nvl(pcr.document_line_location_id,-1)=pll.line_location_id(+)
4369 AND pcr.request_level<>'DISTRIBUTION'
4370 AND pol.from_header_id=poh.po_header_id(+);
4371
4372 end if;
4373
4374 /*
4375 bug 5385384:
4376 for lines with 'QUANTITY' order type and 'QUANTITY' matching basis, if PO is in txn
4377 currency, should convert the price to txn currency for the displaying purpose on
4378 buyer notif page,since price values in pcr table are in functional currency.
4379
4380 Only prices fetched from pcr table need to be converted (l_pcr_old_price is not
4381 null); prices obtained from po lines/shipments are already in txn currency
4382
4383 */
4384
4385 select pol.po_header_id, pol.matching_basis,pol.order_type_lookup_code
4386 into l_po_header_id, l_po_matching_basis,l_po_order_type
4387 from po_lines_all pol
4388 where pol.po_line_id = p_line_id;
4389
4390 /* Removed code for dividing result by rate because now we have txn amt and txn price
4391 in PO_CHANGE_REQUESTs */
4392
4393 l_progress:='003';
4394 if g_fnd_debug = 'Y' then
4395 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4396 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4397 END IF;
4398 end if;
4399
4400 else
4401 -- price obtained from po price break function is already in txn currency,
4402 -- no need to convert.
4403 l_price:= Get_PO_Price_Break_Grp( p_org_id, p_group_id, p_line_id, p_item_id, p_line_uom, p_old_price, p_line_location_id);
4404
4405 l_progress:='004';
4406 if g_fnd_debug = 'Y' then
4407 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4408 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress);
4409 END IF;
4410 end if;
4411
4412 end if;
4413
4414 return l_price;
4415
4416 EXCEPTION
4417 WHEN OTHERS THEN
4418 if g_fnd_debug = 'Y' then
4419 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
4420 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
4421 l_api_name, sqlerrm);
4422 END IF;
4423 end if;
4424 raise;
4425
4426 END Get_Price;
4427
4428 /**************************************************************************
4429 * Public Procedure: Get_Currency_Info
4430 * Effects: This procedure is called from UI to get currency information so that
4431 * new qty and amount on buyer notification page can be displayed properly if PO
4432 * is created in txn currency
4433 **************************************************************************/
4434 PROCEDURE Get_Currency_Info ( p_po_header_id IN NUMBER,
4435 p_org_id IN NUMBER,
4436 x_po_in_txn_currency OUT NOCOPY VARCHAR2,
4437 x_rate OUT NOCOPY NUMBER
4438 )
4439 IS
4440 l_functional_currency_code gl_sets_of_books.currency_code%TYPE;
4441 l_po_currency_code varchar2(100);
4442 l_rate number;
4443 l_progress varchar2(5);
4444 l_api_name varchar2(50):= 'Get_Currency_Info';
4445
4446 BEGIN
4447
4448 if g_fnd_debug = 'Y' then
4449 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4450 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering Get_Currency_info' );
4451 END IF;
4452 end if;
4453
4454 l_progress := '001';
4455 x_po_in_txn_currency:= 'N';
4456 x_rate:= 1;
4457
4458 l_progress := '002';
4459
4460 SELECT sob.currency_code
4461 INTO l_functional_currency_code
4462 FROM gl_sets_of_books sob, financials_system_params_all fsp
4463 WHERE fsp.org_id = p_org_id
4464 AND fsp.set_of_books_id = sob.set_of_books_id;
4465
4466 l_progress := '003';
4467
4468 select poh.currency_code,poh.rate
4469 into l_po_currency_code,l_rate
4470 from po_headers_all poh
4471 where poh.po_header_id = p_po_header_id;
4472
4473 if (l_functional_currency_code <> l_po_currency_code ) then
4474 x_po_in_txn_currency:= 'Y';
4475
4476 end if;
4477
4478 l_progress := '004';
4479
4480 x_rate := l_rate;
4481
4482 if g_fnd_debug = 'Y' then
4483 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4484 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Exit Get_Currency_info' );
4485 END IF;
4486 end if;
4487
4488 EXCEPTION
4489 when others then
4490 wf_core.context('PO_WF_REQ_NOTIFICATION','Get_Currency_Info',l_progress);
4491 raise;
4492
4493 END Get_Currency_Info;
4494
4495 END PO_ReqChangeRequestNotif_PVT;