[Home] [Help]
PACKAGE BODY: APPS.PO_EMAIL_GENERATE
Source
1 PACKAGE BODY PO_EMAIL_GENERATE AS
2 /* $Header: POXWPAMB.pls 120.12.12020000.3 2013/04/09 06:18:52 swagajul ship $ */
3
4 /*=======================================================================+
5 | FILENAME
6 | POXWPAMB.pls
7 |
8 | DESCRIPTION
9 | PL/SQL body for package: PO_EMAIL_GENERATE
10 |
11 | NOTES Diwas Kc Created 02/09/01
12 | Created for generating the header and body of a PO Email notification.
13 | MODIFIED (MM/DD/YY)
14 | - bug fix 2203163 - release not showing correct price - 02/07/2002
15 | - bug fix 2336094 - davidng - 05/30/2002
16 | Added 3 new exception handling cases in the exception
17 | block in procedure generate_term
18 | - bug fix 2473707 - davidng - 08/12/2002
19 | Added an IF conditional to display the item revision number
20 | when it is present
21 | - PO UTF8 Project - tpoon - 09/06/2002
22 | Changed the type of l_header_note_to_vendor from
23 | VARCHAR2(240) to po_headers_all.note_to_vendor%TYPE.
24 |
25 | - PO TIMEPHASED - davidng - 09/09/2002
26 | Project 1. Added start_date and end_date to cursor shipment_blanket_cursor
27 | to display effective dates at the Blanket Agreement price breaks.
28 | 2. Added start_date and end_date to the type declaration shipment_record.
29 | 3. Added code to display start_date and end_date in the HTML notification
30 | 4. All code changes done in procedure generate_html().
31 | Search for keyword TIMEPHASED.
32 *=======================================================================*/
33
34 c_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_EMAIL_GENERATE.'; -- Bug 2792156
35
36 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N'); -- BUG 9858430
37
38 TYPE shipment_record IS RECORD (
39 line_num po_lines_all.line_num%TYPE,
40 po_line_id po_lines_all.po_line_id%TYPE,
41 item_num mtl_system_items_kfv.concatenated_segments%TYPE,
42 item_revision po_lines_all.item_revision%TYPE,
43 vendor_product_num po_lines.vendor_product_num%TYPE, /* Bug 3105566 */
44 item_desc po_lines.item_description%TYPE,
45 uom mtl_units_of_measure_tl.unit_of_measure_tl%TYPE, /* Bug 2701946 */
46 quantity po_line_locations.quantity%TYPE,
47 unit_price po_lines.unit_price%TYPE,
48 need_by_date po_line_locations.need_by_date%TYPE,
49 promised_date po_line_locations.promised_date%TYPE,
50 taxable_flag po_line_locations.taxable_flag%TYPE,
51 note_to_vendor po_lines_all.note_to_vendor%TYPE,
52 un_number_id po_lines_all.un_number_id%TYPE,
53 hazard_class_id po_lines_all.hazard_class_id%TYPE,
54 cancel_flag po_line_locations_all.cancel_flag%TYPE,
55 cancel_date po_line_locations_all.cancel_date%TYPE,
56 quantity_cancelled po_line_locations_all.quantity_cancelled%TYPE,
57 item_id po_lines_all.item_id%TYPE,
58 org_id po_line_locations_all.org_id%TYPE,
59 contract_num po_lines_all.contract_num%TYPE,
60 line_location_id po_line_locations_all.line_location_id%TYPE,
61 ship_to_location_id po_line_locations_all.ship_to_location_id%TYPE,
62 consigned_flag po_line_locations_all.consigned_flag%TYPE, -- <SUP_CON FPI>
63 start_date po_line_locations_all.start_date%TYPE, /* <TIMEPHASED FPI> */
64 end_date po_line_locations_all.end_date%TYPE, /* <TIMEPHASED PFI> */
65 --<Bug 2817117 mbhargav START>
66 from_header_id po_lines_all.from_header_id%type,
67 from_line_id po_lines_all.from_line_id%TYPE,
68 --<Bug 2817117 mbhargav END>
69 drop_ship_flag po_line_locations_all.drop_ship_flag%TYPE --Bug 9356725
70 );
71
72
73 procedure generate_html (document_id in varchar2,
74 display_type in Varchar2,
75 document in out NOCOPY clob,
76 document_type in out NOCOPY varchar2) IS
77
78 l_document VARCHAR2(32000) := '';
79 NL VARCHAR2(1) := fnd_global.newline;
80
81 shipmentNum NUMBER := 1;
82
83 l_document_id VARCHAR2(30) := document_id;
84
85 l_document_type VARCHAR2(30) := '';
86
87 l_currency_code fnd_currencies.currency_code%TYPE;
88
89 l_line_loc shipment_record;
90 l_date VARCHAR2(20) := '';
91 -- Bug 3637864. Need to display both Promised and Need By Date
92
93 l_promised_date VARCHAR2(20) := '';
94 l_needby_date VARCHAR2(20) := '';
95 l_start_date VARCHAR2(20) := ''; -- Bug 2687751
96 l_end_date VARCHAR2(20) := ''; -- Bug 2687751
97
98 l_ship_to_count NUMBER := 0;
99
100 l_extension NUMBER := 0;
101 l_extension_total NUMBER := 0;
102 l_blanket_total_amount NUMBER := 0;
103 x_subtype po_headers.type_lookup_code%TYPE;
104 l_datatype_count NUMBER := 0;
105
106 l_vendor_quote_num po_lines_print.VENDOR_QUOTE_NUM%TYPE;
107 l_po_quote_num po_lines_print.PO_QUOTE_NUM%TYPE;
108 l_po_header_id po_headers.po_header_id%TYPE;
109 l_src_ga_flag po_lines_print.SRC_GA_FLAG%TYPE; -- GA FPI
110
111 l_org_id NUMBER;
112 x_orgid NUMBER;
113 l_un_number PO_UN_NUMBERS.UN_NUMBER%TYPE;
114 l_hazard_class PO_HAZARD_CLASSES.HAZARD_CLASS%TYPE;
115 l_text FND_DOCUMENTS_SHORT_TEXT.SHORT_TEXT%TYPE := null;
116
117 l_item_short_text FND_DOCUMENTS_SHORT_TEXT.SHORT_TEXT%TYPE := null;
118 l_item_long_text FND_DOCUMENTS_LONG_TEXT.LONG_TEXT%TYPE := null;
119
120 l_long_text FND_DOCUMENTS_LONG_TEXT.LONG_TEXT%TYPE := null;
121 l_datatype_id fnd_attached_docs_form_vl.datatype_id%TYPE;
122 l_media_id fnd_attached_docs_form_vl.media_id%TYPE;
123 l_item_datatype_id fnd_attached_docs_form_vl.datatype_id%TYPE;
124 l_item_media_id fnd_attached_docs_form_vl.media_id%TYPE;
125 l_cancel_flag po_line_locations_all.cancel_flag%TYPE;
126 l_cancel_date po_line_locations_all.cancel_date%TYPE;
127 l_quantity_cancelled po_line_locations_all.quantity_cancelled%TYPE;
128
129 l_requestor_name po_distributions_print.requestor_name%TYPE;
130 l_requestor_id po_distributions_print.requestor_id%TYPE;
131
132 /* Bug 2870932 increased the size */
133 l_phone per_all_people_f.work_telephone%TYPE;
134 l_email_address per_all_people_f.email_address%TYPE;
135 /* End of Bug 2870932 */
136
137 l_requestor_count NUMBER := 0;
138 l_multiple_flag varchar2(1);
139
140 l_prev_line_po_line_num NUMBER := 0;
141 l_po_line_only varchar2(2) := 'Y';
142 x_display_type varchar2(60);
143
144 --<Bug 2817117 mbhargav>
145 l_global_flag po_headers_all.global_agreement_flag%type;
146
147 -- PO_WF_NOTIF_HEADER_NOTE
148 l_header_text FND_DOCUMENTS_LONG_TEXT.LONG_TEXT%TYPE;
149
150
151 -- PO_WF_NOTIF_SHIPMENT_NOTE
152 l_shipment_text FND_DOCUMENTS_LONG_TEXT.LONG_TEXT%TYPE;
153
154
155 -- variable to hold html string for attachments
156 l_attachments_text varchar2(32000) := '';
157
158 /** Fix for PO UTF8 Project **/
159 -- This holds data from either po_headers.note_to_vendor or
160 -- po_releases.note_to_vendor:
161 l_header_note_to_vendor po_headers_all.note_to_vendor%TYPE;
162 -- l_header_note_to_vendor varchar2(240);
163
164 x_pb_count number;
165
166 l_hrl_location hr_locations_all.location_code%TYPE;
167 l_hrl_description hr_locations_all.description%TYPE;
168 l_hrl_address_line_1 hr_locations_all.address_line_1%TYPE;
169 l_hrl_address_line_2 hr_locations_all.address_line_2%TYPE;
170 l_hrl_address_line_3 hr_locations_all.address_line_3%TYPE;
171 l_hrl_town_or_city hr_locations_all.town_or_city%TYPE;
172 l_hrl_postal_code hr_locations_all.postal_code%TYPE;
173 -- EMAILPO FPH START--
174 l_hrl_to_region1 fnd_lookup_values.meaning%TYPE;
175 l_hrl_to_region2 fnd_lookup_values.meaning%TYPE;
176 l_hrl_to_region3 fnd_lookup_values.meaning%TYPE;
177 /* Bug 2766736. Changed nls_territory to territory_short_name */
178 l_hrl_country fnd_territories_vl.territory_short_name%TYPE;
179 -- EMAILPO FPH END--
180
181 --bug fix 2257742
182 l_min_unit fnd_currencies.minimum_accountable_unit%TYPE;
183 l_precision fnd_currencies.precision%TYPE;
184 l_ext_precision fnd_currencies.extended_precision%TYPE; -- Bug fix 3314246
185
186 l_allow_item_desc_update mtl_system_items.allow_item_desc_update_flag%TYPE;
187 l_mtl_system_items_desc mtl_system_items_tl.description%TYPE;
188
189
190 /* bug 4567441 : added the orderby clause, so that the document attachments
191 are ordered by sequence */
192 cursor attachments_cursor(v_entity_name varchar2, v_document_id number) is
193 Select datatype_id, media_id
194 from fnd_attached_docs_form_vl
195 where entity_name = v_entity_name
196 and pk1_value = to_char(v_document_id) /* Bug 5964375 */
197 and function_name = 'PO_PRINTPO'
198 and datatype_id in (1,2)
199 and media_id is not null
200 order by seq_num;
201
202 --<Bug 2872552 mbhargav START>
203 --Cursor which ensures that only those attachments are picked which
204 --which are shared across operating unit or ones which belong to same
205 --security type
206 /* bug 4567441 : added the orderby clause, so that the document attachments
207 are ordered by sequence */
208 cursor attachments_from_ga_cursor(v_entity_name varchar2, v_document_id number) is
209 Select datatype_id, media_id
210 from fnd_attached_docs_form_vl fad, financials_system_parameters fsp
211 where entity_name = v_entity_name
212 and pk1_value = to_char(v_document_id) /* Bug 5964375 */
213 and function_name = 'PO_PRINTPO'
214 and datatype_id in (1,2)
215 and media_id is not null
216 AND (publish_flag = 'Y'
217 --Security level is Organization
218 OR (security_type = 1 AND security_id = fsp.org_id)
219 --Security level is Set Of Books
220 OR (security_type = 2 AND security_id = fsp.set_of_books_id)
221 --Security level is NONE
222 OR (security_type = 4)
223 )
224 order by fad.seq_num;
225 --<Bug 2872552 mbhargav END>
226 /* bug 4567441 : added the orderby clause, so that the document attachments
227 are ordered by sequence */
228 cursor item_notes_cursor(v_org_id number, v_item_id number) is
229 SELECT datatype_id,
230 media_id
231 FROM fnd_attached_docs_form_vl
232 WHERE entity_name = 'MTL_SYSTEM_ITEMS' AND
233 pk1_value = to_char(v_org_id) AND
234 pk2_value = to_char(v_item_id) AND
235 function_name = 'PO_PRINTPO' and
236 datatype_id in (1,2) and
237 media_id is not null
238 order by seq_num;
239
240 CURSOR shipment_cursor(v_document_id NUMBER) IS
241 SELECT pol.line_num,
242 pll.po_line_id,
243 msi.concatenated_segments,
244 pol.item_revision,
245 pol.vendor_product_num,
246 pol.item_description,
247 umvl.unit_of_measure_tl, /* Bug 2701946 */
248 pll.quantity,
249 pol.unit_price,
250 pll.need_by_date,
251 pll.promised_date,
252 pll.taxable_flag,
253 pol.note_to_vendor,
254 pol.un_number_id,
255 pol.hazard_class_id,
256 pll.cancel_flag,
257 pll.cancel_date,
258 pll.quantity_cancelled,
259 pol.item_id,
260 fsp.inventory_organization_id org_id, /* Bug 3064519 */
261 pol.contract_num,
262 pll.line_location_id,
263 pll.ship_to_location_id,
264 pll.consigned_flag, --< SUP_CON FPI>
265 null, /* <TIMEPHASED FPI> */
266 null, /* <TIMEPHASED FPI> */
267 --<Bug 2817117 mbhargav START>
268 pol.from_header_id,
269 pol.from_line_id,
270 --<Bug 2817117 mbhargav END>
271 pll.drop_ship_flag --Bug 9356725
272 FROM po_lines_all pol, --<R12.MOAC> --po_lines pol,
273 po_line_locations pll,
274 mtl_system_items_kfv msi,
275 mtl_units_of_measure_vl umvl,
276 financials_system_params_all fsp --<R12.MOAC> --financials_system_parameters fsp
277 where PLL.PO_HEADER_ID = v_document_id
278 and PLL.po_line_id = POL.po_line_id
279 and PLL.po_release_id is NULL /* Bug 4513703 */
280 and pol.item_id = msi.inventory_item_id(+)
281 and NVL(msi.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
282 and nvl(pol.cancel_flag,'N') = 'N'
283 and nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code) = umvl.unit_of_measure
284 and POL.org_id = FSP.org_id --<R12.MOAC>
285 order by line_num, pll.shipment_num asc; --<Bug 14794256>
286
287
288 CURSOR shipment_blanket_cursor(v_document_id NUMBER) IS
289 SELECT pol.line_num,
290 pll.po_line_id,
291 msi.concatenated_segments,
292 pol.item_revision,
293 pol.vendor_product_num,
294 pol.item_description,
295 umvl.unit_of_measure_tl, /* Bug 2701946 */
296 pll.quantity,
297 nvl(pll.price_override, pol.unit_price) unit_price,
298 pll.need_by_date,
299 pll.promised_date,
300 pll.taxable_flag,
301 pol.note_to_vendor,
302 pol.un_number_id,
303 pol.hazard_class_id,
304 pll.cancel_flag,
305 pll.cancel_date,
306 pll.quantity_cancelled,
307 pol.item_id,
308 fsp.inventory_organization_id org_id, /* Bug 3064519 */
309 pol.contract_num,
310 pll.line_location_id,
311 pll.ship_to_location_id,
312 NULL, -- <SUP_CON FPI>
313 pll.start_date, /* <TIMEPHASED FPI> */
314 pll.end_date, /* <TIMEPHASED FPI> */
315 --<Bug 2817117 mbhargav START>
316 null,
317 NULL,
318 --<Bug 2817117 mbhargav END>
319 pll.drop_ship_flag --Bug 9356725
320 FROM po_lines_all pol, --<R12.MOAC> --po_lines pol,
321 po_line_locations pll,
322 mtl_system_items_kfv msi,
323 mtl_units_of_measure_vl umvl,
324 financials_system_params_all fsp --<R12.MOAC> --financials_system_parameters fsp
325 where POL.PO_HEADER_ID = v_document_id
326 and POL.po_line_id = PLL.po_line_id(+)
327 and pol.item_id = msi.inventory_item_id(+)
328 and pll.shipment_type(+) = 'PRICE BREAK'
329 and NVL(msi.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
330 and nvl(pol.cancel_flag,'N') = 'N'
331 and nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code) = umvl.unit_of_measure
332 and POL.org_id = FSP.org_id --<R12.MOAC>
333 order by line_num, pll.shipment_num asc; --<Bug 14794256>
334
335 CURSOR blanket_line_cursor(v_document_id NUMBER) IS
336 SELECT pol.line_num,
337 pol.po_line_id,
338 msi.concatenated_segments,
339 pol.item_revision,
340 pol.vendor_product_num,
341 pol.item_description,
342 umvl.unit_of_measure_tl, /* Bug 2701946 */
343 pol.quantity,
344 pol.unit_price,
345 null, --pol.need_by_date,
346 null, -- pol.promised_date,
347 null, --pll.taxable_flag,
348 pol.note_to_vendor,
349 pol.un_number_id,
350 pol.hazard_class_id,
351 pol.cancel_flag,
352 pol.cancel_date,
353 null, --pll.quantity_cancelled,
354 pol.item_id,
355 fsp.inventory_organization_id org_id, /* Bug 3064519 */
356 pol.contract_num,
357 null, --pll.line_location_id,
358 poh.ship_to_location_id,
359 NULL, -- <SUP_CON FPI>
360 null, /* <TIMEPHASED FPI> */
361 null, /* <TIMEPHASED FPI> */
362 --<Bug 2817117 mbhargav START>
363 null,
364 NULL,
365 --<Bug 2817117 mbhargav END>
366 NULL --Bug 9356725
367 FROM po_lines pol,
368 po_headers_all poh, --<R12.MOAC> --po_headers poh,
369 mtl_system_items_kfv msi,
370 mtl_units_of_measure_vl umvl,
371 financials_system_params_all fsp --<R12.MOAC> --financials_system_parameters fsp
372 where POL.PO_HEADER_ID = v_document_id
373 and POL.po_header_id = POH.po_header_id
374 and pol.item_id = msi.inventory_item_id(+)
375 and NVL(msi.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
376 and nvl(pol.cancel_flag,'N') = 'N'
377 and pol.unit_meas_lookup_code = umvl.unit_of_measure
378 and POL.org_id = FSP.org_id --<R12.MOAC>
379 order by line_num asc;
380
381 CURSOR shipment_release_cursor(v_document_id NUMBER) IS
382 SELECT pol.line_num,
383 pll.po_line_id,
384 msi.concatenated_segments,
385 pol.item_revision,
386 pol.vendor_product_num,
387 pol.item_description,
388 umvl.unit_of_measure_tl, /* Bug 2701946 */
389 pll.quantity,
390 pll.price_override,
391 pll.need_by_date,
392 pll.promised_date,
393 pll.taxable_flag,
394 pol.note_to_vendor,
395 pol.un_number_id,
396 pol.hazard_class_id,
397 pll.cancel_flag,
398 pll.cancel_date,
399 pll.quantity_cancelled,
400 pol.item_id,
401 fsp.inventory_organization_id org_id, /* Bug 3064519 */
402 pol.contract_num,
403 pll.line_location_id,
404 pll.ship_to_location_id,
405 NULL, -- <SUP_CON FPI>
406 null, /* <TIMEPHASED FPI> */
407 null, /* <TIMEPHASED FPI> */
408 --<Bug 2817117 mbhargav START>
409 null,
410 NULL,
411 --<Bug 2817117 mbhargav END>
412 pll.drop_ship_flag --Bug 9356725
413 FROM po_lines_all pol, --<R12.MOAC> --po_lines pol,
414 po_line_locations pll,
415 mtl_system_items_kfv msi,
416 mtl_units_of_measure_vl umvl,
417 financials_system_params_all fsp --<R12.MOAC> --financials_system_parameters fsp
418 where PLL.PO_RELEASE_ID = v_document_id
419 and PLL.po_line_id = POL.po_line_id
420 and pol.item_id = msi.inventory_item_id(+)
421 and NVL(msi.organization_id, fsp.inventory_organization_id) =
422 fsp.inventory_organization_id
423 and nvl(pol.cancel_flag,'N') = 'N'
424 and nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code) = umvl.unit_of_measure
425 and PLL.org_id = FSP.org_id --<R12.MOAC>
426 order by line_num, pll.shipment_num asc; --<Bug 14794256>
427
428 cursor requestor_cursor (v_line_location_id NUMBER) IS
429 select distinct deliver_to_person_id
430 FROM po_distributions pdp
431 WHERE pdp.line_location_id = v_line_location_id;
432
433 l_document_status varchar2(100);
434
435 /*Bug 9356725 defined local variables to display drop ship info*/
436 l_drop_ship NUMBER;
437 l_ship_cust_name VARCHAR2(400);
438 l_ship_cont_name VARCHAR2(400);
439 /*Bug 9356725 */
440
441 -- <BUG 9858430>
442 l_item_type wf_items.item_type%TYPE;
443 l_item_key wf_items.item_key%TYPE;
444 l_progress varchar2(100);
445
446 BEGIN
447
448 /* Bug# 2493568: kagarwal
449 ** Desc: The cancelled qty on shipments, if any, was not getting subtracted
450 ** from the ordered shipment qty and the calculated PO Total also included
451 ** the amount for cancelled qty on the shipment.
452 **
453 ** Added clause (l_line_loc.quantity - nvl(l_line_loc.quantity_cancelled, 0))
454 ** wherever the qty ordered was being displayed or being used in calculation.
455 */
456
457 --<BUG 9858430 START>
458 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
459 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
460
461 l_document_id:=wf_engine.GetItemAttrNumber (itemtype => l_item_type,
462 itemkey => l_item_key,
463 aname => 'DOCUMENT_ID');
464
465 l_document_type:=wf_engine.GetItemAttrText (itemtype => l_item_type,
466 itemkey => l_item_key,
467 aname => 'DOCUMENT_TYPE');
468
469 l_org_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
470 itemkey => l_item_key,
471 aname => 'ORG_ID');
472
473 l_progress := 'PO_EMAIL_GENERATE.GENERATE_HTML';
474 IF (g_po_wf_debug = 'Y') THEN
475 PO_WF_DEBUG_PKG.insert_debug('itemtype','itemkey','l_item_type= ' || l_item_type);
476 PO_WF_DEBUG_PKG.insert_debug('itemtype','itemkey','l_item_key= ' || l_item_key);
477 PO_WF_DEBUG_PKG.insert_debug('itemtype','itemkey','l_document_id= ' || l_document_id);
478 PO_WF_DEBUG_PKG.insert_debug('itemtype','itemkey','l_document_type= ' || l_document_type);
479 PO_WF_DEBUG_PKG.insert_debug('itemtype','itemkey','l_org_id= ' || l_org_id);
480 END IF;
481
482 IF l_org_id IS NOT NULL THEN
483 PO_MOAC_UTILS_PVT.set_org_context(to_char(l_org_id)) ;
484 END IF;
485 --<BUG 9858430 END>
486
487 x_display_type := 'text/html';
488
489 --2332866, check if the document is in processing, and
490 -- show warning message to the supplier
491 if(l_document_type in ('PO', 'PA')) then
492 select authorization_status
493 into l_document_status
494 from po_headers_all
495 where po_header_id = l_document_id;
496
497 elsif (l_document_type = 'RELEASE') then
498 /* Bug 2791859 po_releases_all should be used instead of po_releases */
499 select po_header_id into l_po_header_id from po_releases_all
500 where po_release_id = l_document_id;
501
502 select authorization_status
503 into l_document_status
504 from po_headers_all
505 where po_header_id = l_po_header_id;
506 end if;
507
508 if(l_document_status is null or
509 l_document_status in ('IN PROCESS', 'INCOMPLETE', 'REQUIRES REAPPROVAL')) then
510 WF_NOTIFICATION.WriteToClob(document, ' ');
511 return;
512 end if;
513
514 --<BUG 9858430 START>
515 IF (g_po_wf_debug = 'Y') THEN
516 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_document_status= ' || l_document_status);
517 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_po_header_id= ' || l_po_header_id);
518 END IF;
519 --<BUG 9858430 END>
520
521 /* setting the org context here inside this procedure because wf mailer does not supply the
522 context. */
523 /* BUG 9858430
524 PO_REQAPPROVAL_INIT1.get_multiorg_context (l_document_type, l_document_id, x_orgid);
525
526 IF x_orgid is NOT NULL THEN
527
528 PO_MOAC_UTILS_PVT.set_org_context(x_orgid) ; -- <R12.MOAC>
529
530 END IF; */
531
532 IF (l_document_type = 'PA') THEN
533 select type_lookup_code
534 into x_subtype
535 from po_headers
536 where po_header_id = l_document_id;
537 END IF;
538
539 --<BUG 9858430 START>
540 IF (g_po_wf_debug = 'Y') THEN
541 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'x_subtype= ' || x_subtype);
542 END IF;
543 --<BUG 9858430 END>
544
545 IF (l_document_type = 'PA') and (x_subtype = 'CONTRACT') THEN
546 null;
547
548 ELSE
549 IF (l_document_type = 'PO') THEN
550 -- Seeing if the ship to locations are distinct or not
551 select count(distinct pll.ship_to_location_id) into l_ship_to_count
552 from
553 po_lines_all pol, -- <R12.MOAC> --po_lines pol,
554 po_line_locations pll
555 where PLL.PO_HEADER_ID = l_document_id
556 and PLL.po_line_id = POL.po_line_id;
557 ELSIF (l_document_type = 'RELEASE') THEN
558 -- Seeing if the ship to locations are distinct or not
559 select count(distinct pll.ship_to_location_id) into l_ship_to_count
560 FROM po_lines_all pol, -- <R12.MOAC> --po_lines pol,
561 po_line_locations pll
562 where PLL.PO_RELEASE_ID = l_document_id
563 and PLL.po_line_id = POL.po_line_id;
564 ELSIF (l_document_type = 'PA') THEN
565 -- Seeing if the ship to locations are distinct or not
566
567 /* Bug# 2684059: kagarwal
568 ** Added nvl clause for the case when the Blanket PA does not have
569 ** any PRICE BREAKS
570 */
571 select nvl(count(distinct pll.ship_to_location_id),0)
572 into l_ship_to_count
573 FROM po_lines_all pol, -- <R12.MOAC> --po_lines pol,
574 po_line_locations pll
575 where PLL.PO_HEADER_ID = l_document_id
576 and PLL.po_line_id = POL.po_line_id
577 and PLL.shipment_type = 'PRICE BREAK';
578 ELSE
579 null;
580 END IF;
581
582 --<BUG 9858430 START>
583 IF (g_po_wf_debug = 'Y') THEN
584 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_ship_to_count= ' || l_ship_to_count);
585 END IF;
586 --<BUG 9858430 END>
587
588 IF (l_document_type = 'PA') THEN
589 select count(*) into x_pb_count
590 from po_line_locations
591 where po_header_id = l_document_id;
592 END IF;
593
594 --<BUG 9858430 START>
595 IF (g_po_wf_debug = 'Y') THEN
596 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'x_pb_count= ' || x_pb_count);
597 END IF;
598 --<BUG 9858430 END>
599
600 IF (x_display_type = 'text/html') THEN
601
602
603 l_document := NL || NL || '<!-- PO_LINE_DETAILS -->'|| NL || NL || '<P><B>';
604 -- l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS');
605 -- l_document := l_document || '</B>';
606
607 l_document := l_document || '<TABLE WIDTH=100% border=1 cellpadding=2 cellspacing=1>';
608
609 l_document := l_document || '<TR>' || NL;
610
611 l_document := l_document || '<TH>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TH>' || NL;
612
613 l_document := l_document || '<TH width=100>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_PART_NO_DESC') || '</TH>' || NL;
614
615 l_document := l_document || '<TH>' ||
616 fnd_message.get_string('PO', 'PO_WF_NOTIF_DELIVERY_DATE') || '</TH>' || NL;
617
618 /* <TIMEPHASED FPI> */
619 /* Displaying the Effective Date and Expires On column titles */
620 /* Bug 2780755 start.
621 * We need to show effective start and end dates only for document
622 * type PA.
623 */
624 IF ((l_document_type = 'PA') and (x_subtype = 'BLANKET')) THEN
625 l_document := l_document || '<TH>' ||
626 fnd_message.get_string('PO', 'PO_WF_NOTIF_EFFECTIVE_DATE') || '</TH>' || NL;
627
628 l_document := l_document || '<TH>' ||
629 fnd_message.get_string('PO', 'PO_WF_NOTIF_EXPIRES_ON') || '</TH>' || NL;
630 end if;
631 /* Bug 2780755 End. */
632 /* <TIMEPHASED FPI> */
633
634 l_document := l_document || '<TH>' ||
635 fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TH>' || NL;
636
637 l_document := l_document || '<TH>' ||
638 fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || '</TH>' || NL;
639 l_document := l_document || '<TH>' ||
640 fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || '</TH>' || NL;
641
642 l_document := l_document || '<TH>' ||
643 fnd_message.get_string('PO','PO_WF_NOTIF_TAX') || '</TH>' || NL;
644 l_document := l_document || '<TH>' ||
645 fnd_message.get_string('PO', 'PO_WF_NOTIF_EXTENSION') || '</TH>' || NL;
646 /*
647 l_document := l_document || '<TH>' ||
648 fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX') || '</TH>' || NL;
649 */
650 l_document := l_document || '</TR>' || NL;
651
652 l_document := l_document || '</B>';
653
654
655 /* Now generate the html code for the individual rows in the table */
656
657 --<BUG 9858430> Removed fetching org_id from the following sqls
658 IF (l_document_type = 'PO') THEN
659
660 select note_to_vendor ,currency_code
661 into l_header_note_to_vendor , l_currency_code
662 from po_headers
663 where po_header_id = to_number(l_document_id);
664 open shipment_cursor(l_document_id);
665
666
667 ELSIF (l_document_type = 'RELEASE') THEN
668
669
670 select note_to_vendor ,po_header_id
671 into l_header_note_to_vendor , l_po_header_id
672 from po_releases
673 where po_release_id = to_number(l_document_id);
674
675 select currency_code into l_currency_code
676 from po_headers
677 where po_header_id = l_po_header_id;
678
679 open shipment_release_cursor(l_document_id);
680
681 ELSIF (l_document_type = 'PA') THEN
682
683 select note_to_vendor ,currency_code, blanket_total_amount
684 into l_header_note_to_vendor ,l_currency_code, l_blanket_total_amount
685 from po_headers
686 where po_header_id = to_number(l_document_id);
687
688 if x_pb_count <> 0 then
689 open shipment_blanket_cursor(l_document_id);
690 else
691 open blanket_line_cursor(l_document_id);
692 end if;
693
694
695 ELSE
696 null;
697 END IF;
698
699 --<BUG 9858430 START>
700 IF (g_po_wf_debug = 'Y') THEN
701 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_header_note_to_vendor= ' || l_header_note_to_vendor);
702 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_currency_code= ' || l_currency_code);
703 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_blanket_total_amount= ' || l_blanket_total_amount);
704 END IF;
705 --<BUG 9858430 END>
706
707 -- l_currency_code := PO_CORE_S2.get_base_currency;
708
709 -- Bug fix 2257742
710 fnd_currency.get_info(l_currency_code, l_precision, l_ext_precision, l_min_unit);
711
712
713 /* Bug# 2737371: kagarwal
714 ** Desc: Adding the <PRE> tag when adding the attachment text to
715 ** the html document in order to preserve the text formatting in the attachments.
716 ** Made changes to the Header and Line Level attachments only.
717 */
718
719 -- Getting the header level text information and displaying it if it exists
720 if (l_document_type in ('PO', 'PA')) then
721 open attachments_cursor('PO_HEADERS', l_document_id);
722 elsif (l_document_type = 'RELEASE') then
723 open attachments_cursor('PO_RELEASES', l_document_id);
724 else
725 null;
726 end if;
727 loop
728
729 fetch attachments_cursor into l_datatype_id, l_media_id;
730 exit when attachments_cursor%NOTFOUND;
731
732
733
734 if (l_datatype_id = 1) then
735 select short_text into l_text from fnd_documents_short_text
736 where media_id = l_media_id;
737
738
739 if (l_attachments_text is null) then
740
741 l_attachments_text := l_attachments_text ||'<PRE>'|| l_text ||'</PRE>'||NL;
742
743 else
744
745 l_attachments_text := l_attachments_text || '<BR>' || NL;
746 l_attachments_text := l_attachments_text ||'<PRE>'|| l_text ||'</PRE>'|| NL;
747
748 end if;
749
750 elsif (l_datatype_id = 2) then
751
752 select long_text into l_long_text from fnd_documents_long_text
753 where media_id = l_media_id;
754
755 if (l_attachments_text is null) then
756
757 l_attachments_text := l_attachments_text || '<BR>' || NL;
758 -- removed nowrap
759
760 l_attachments_text := l_attachments_text ||'<PRE>'|| l_long_text ||'</PRE>'|| NL;
761
762 else
763
764 l_attachments_text := l_attachments_text || '<BR>' || NL;
765 l_attachments_text := l_attachments_text ||'<PRE>'|| l_long_text ||'</PRE>'|| NL;
766
767 end if;
768 else
769 null;
770
771 end if;
772
773 end loop;
774
775 close attachments_cursor;
776
777 --<Bug 2817117 mbhargav START>
778 -- Getting the header level text information and displaying it if it exists
779 -- for the blanket to which this release is sourced
780 if (l_document_type = 'RELEASE') THEN
781 select po_header_id into l_po_header_id from po_releases
782 where po_release_id = l_document_id;
783
784 open attachments_cursor('PO_HEADERS', l_po_header_id);
785
786 loop
787
788 fetch attachments_cursor into l_datatype_id, l_media_id;
789 exit when attachments_cursor%NOTFOUND;
790
791
792
793 if (l_datatype_id = 1) then
794 select short_text into l_text from fnd_documents_short_text
795 where media_id = l_media_id;
796
797
798 if (l_attachments_text is null) then
799
800 l_attachments_text := l_attachments_text ||'<PRE>'|| l_text ||'</PRE>'||NL;
801
802 else
803
804 l_attachments_text := l_attachments_text || '<BR>' || NL;
805 l_attachments_text := l_attachments_text ||'<PRE>'|| l_text ||'</PRE>'|| NL;
806
807 end if;
808
809 elsif (l_datatype_id = 2) then
810
811 select long_text into l_long_text from fnd_documents_long_text
812 where media_id = l_media_id;
813
814 if (l_attachments_text is null) then
815
816 l_attachments_text := l_attachments_text || '<BR>' || NL;
817 -- removed nowrap
818
819 l_attachments_text := l_attachments_text ||'<PRE>'|| l_long_text ||'</PRE>'|| NL;
820
821 else
822
823 l_attachments_text := l_attachments_text || '<BR>' || NL;
824 l_attachments_text := l_attachments_text ||'<PRE>'|| l_long_text ||'</PRE>'|| NL;
825
826 end if;
827 else
828 null;
829
830 end if;
831
832 end loop;
833
834 close attachments_cursor;
835 end if; --its a Release
836 --<Bug 2817117 mbhargav END>
837
838
839 if (l_header_note_to_vendor is not null) then
840 l_attachments_text := l_header_note_to_vendor || '<BR>' || l_attachments_text || NL;
841 end if;
842
843 if (l_attachments_text is not null) then
844 l_document := l_document || '<TR><TD colspan=9><font color=black>' || l_attachments_text || '</font></TD></TR>' || NL;
845 end if;
846
847
848 loop
849
850
851 l_un_number := null;
852 l_hazard_class := null;
853 l_text := null;
854 l_datatype_id := null;
855 l_media_id := null;
856
857 l_vendor_quote_num := null;
858 l_po_quote_num := null;
859
860
861
862
863 IF (l_document_type = 'PO') THEN
864
865 fetch shipment_cursor into l_line_loc;
866 exit when shipment_cursor%notfound;
867
868 ELSIF (l_document_type = 'RELEASE') THEN
869 fetch shipment_release_cursor into l_line_loc;
870 exit when shipment_release_cursor%notfound;
871
872 ELSIF (l_document_type = 'PA') THEN
873 if x_pb_count <> 0 then
874 fetch shipment_blanket_cursor into l_line_loc;
875 exit when shipment_blanket_cursor%notfound;
876 else
877 fetch blanket_line_cursor into l_line_loc;
878 exit when blanket_line_cursor%notfound;
879 end if;
880
881 ELSE
882 null;
883
884 END IF;
885
886
887 begin
888 /*
889 EMAILPO FPH
890 should not print region1, 2 and 3
891 for US address only region2 code should be printed ignoring 1 and 3
892 for non US addresses region1 i.e. County or region3 i.e. province should be printed
893 for region1 or region3 the meaning should be printed instead of code from fnd_lookup_values
894 Also country should be spelled out instead of code
895 */
896 /* Bug 2766736. Changed ftv.nls_territory to ftv.territory_short_name in
897 the select statement */
898
899 -- Bug 3574886: Query from base tables in case the session context is not set correctly
900 -- when this SQL is executed; fetch translated columns from hr_locations_all_tl
901 select distinct
902 hlt.location_code,
903 hlt.description,
904 hrl.address_line_1,
905 hrl.address_line_2,
906 hrl.address_line_3,
907 --hrl.town_or_city, --bug#5870952 commented to fetch tow_or_city from fnd_lookup_values
908 Decode(hrl.town_or_city,flv4.lookup_code,flv4.meaning,hrl.town_or_city),
909 hrl.postal_code,
910 ftv.territory_short_name,
911 nvl(decode(hrl.region_1, null,
912 hrl.region_2, decode(flv1.meaning,null,
913 decode(flv2.meaning, null, flv3.meaning, flv2.lookup_code),
914 flv1.lookup_code)),
915 hrl.region_2)
916 into l_hrl_location,
917 l_hrl_description,
918 l_hrl_address_line_1,
919 l_hrl_address_line_2,
920 l_hrl_address_line_3,
921 l_hrl_town_or_city,
922 l_hrl_postal_code,
923 l_hrl_country,
924 l_hrl_to_region1
925 from hr_locations_all hrl,
926 hr_locations_all_tl hlt,
927 fnd_territories_vl ftv,
928 fnd_lookup_values_vl flv1,
929 fnd_lookup_values_vl flv2,
930 fnd_lookup_values_vl flv3,
931 fnd_lookup_values_vl flv4
932 where hrl.region_1 = flv1.lookup_code (+)
933 and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
934 and hrl.location_id = hlt.location_id and hlt.language = USERENV('LANG')
935 and hrl.region_2 = flv2.lookup_code (+)
936 and hrl.country || '_STATE' = flv2.lookup_type (+)
937 and hrl.region_1 = flv3.lookup_code (+)
938 and hrl.country || '_COUNTY' = flv3.lookup_type (+)
939 and hrl.country = ftv.territory_code (+)
940 and hrl.location_id = l_line_loc.ship_to_location_id
941 AND hrl.town_or_city = flv4.lookup_code(+)
942 AND hrl.country || '_PROVINCE' = flv4.lookup_type (+);
943
944 /* Bug 2646120. The country code is not a mandatory one in hr_locations. So the country code
945 may be null. Changed the join with ftv to outer join. */
946 /* Bug 2791859 fnd_lookup_values_vl should be used instead of fnd_lookup_values */
947
948 exception
949 when no_data_found then
950
951 BEGIN
952 --Bug 9356725 Commented the following
953 /*select description, address1,
954 address2, address3, city, postal_code,
955 country
956 into l_hrl_description,
957 l_hrl_address_line_1, l_hrl_address_line_2,
958 l_hrl_address_line_3, l_hrl_town_or_city,
959 l_hrl_postal_code, l_hrl_country
960 from hz_locations
961 where location_id = l_line_loc.ship_to_location_id;*/
962
963 --Bug 9356725 Added following sql to get state info in email notification
964 SELECT
965 HLC.DESCRIPTION,
966 HLC.ADDRESS1,
967 HLC.ADDRESS2,
968 HLC.ADDRESS3,
969 HLC.CITY,
970 HLC.POSTAL_CODE,
971 HLC.COUNTRY,
972 NVL(DECODE(HLC.county, NULL, HLC.state,
973 DECODE(FCL1.MEANING, NULL,
974 DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
975 FCL1.LOOKUP_CODE)), HLC.state)
976 INTO
977 l_hrl_description,
978 l_hrl_address_line_1,
979 l_hrl_address_line_2,
980 l_hrl_address_line_3,
981 l_hrl_town_or_city,
982 l_hrl_postal_code,
983 l_hrl_country,
984 l_hrl_to_region1
985 FROM
986 HZ_LOCATIONS HLC,
987 FND_LOOKUP_VALUES FCL1,
988 FND_LOOKUP_VALUES FCL2,
989 FND_LOOKUP_VALUES FCL3
990 WHERE
991 HLC.LOCATION_ID = l_line_loc.ship_to_location_id AND
992 HLC.county = FCL1.LOOKUP_CODE (+) AND
993 HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
994 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
995 DECODE(FCL1.LOOKUP_CODE, NULL, '1',
996 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE,
997 FCL1.VIEW_APPLICATION_ID)) AND
998 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
999 DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
1000 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
1001 DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
1002 HLC.state = FCL2.LOOKUP_CODE (+) AND
1003 HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
1004 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
1005 DECODE(FCL2.LOOKUP_CODE, NULL, '1',
1006 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE,
1007 FCL2.VIEW_APPLICATION_ID)) AND
1008 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
1009 DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
1010 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
1011 DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
1012 HLC.county = FCL3.LOOKUP_CODE (+) AND
1013 HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
1014 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
1015 DECODE(FCL3.LOOKUP_CODE, NULL, '1',
1016 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE,
1017 FCL3.VIEW_APPLICATION_ID)) AND
1018 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
1019 DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
1020 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
1021 DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
1022
1023 exception
1024 when no_data_found then
1025
1026 null;
1027
1028 end;
1029 end;
1030
1031 -- Bug 3637864. Need to display both promised and needby date
1032 /*Modified as part of bug 7551115 changing date format*/
1033 l_promised_date := to_char(l_line_loc.promised_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1034 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''');
1035 l_needby_date := to_char(l_line_loc.need_by_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1036 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''');
1037
1038 if (l_line_loc.un_number_id is not null) then
1039 begin
1040 select un_number into l_un_number from po_un_numbers pun
1041 where pun.un_number_id = l_line_loc.un_number_id;
1042 /* l_un_number := fnd_message.get_string('PO', 'PO_WF_NOTIF_UN_NUMBER') || ' ' || l_un_number; */
1043 /* Bug 2774206 */
1044 exception
1045 when no_data_found then
1046 null;
1047 end;
1048 end if;
1049
1050 if (l_line_loc.hazard_class_id is not null) then
1051 begin
1052 select hazard_class into l_hazard_class
1053 from po_hazard_classes phc
1054 where phc.hazard_class_id = l_line_loc.hazard_class_id;
1055 -- bug fix 2436110 -- display label is handled when html is built - search for 2436110
1056 --l_hazard_class := fnd_message.get_string('PO', 'PO_WF_NOTIF_HAZARD_CLASS') || ' ' || l_hazard_class;
1057 exception
1058 when no_data_found then
1059 null;
1060 end;
1061 end if;
1062
1063
1064
1065 l_document := l_document || '<TR>' || NL;
1066
1067 IF (l_line_loc.line_Num <> l_prev_line_po_line_num) THEN
1068 l_po_line_only := 'Y';
1069 l_prev_line_po_line_num := l_line_loc.line_Num;
1070 ELSE
1071 l_po_line_only := 'N';
1072
1073 END IF;
1074
1075
1076
1077
1078
1079
1080 IF (l_po_line_only = 'Y') THEN
1081 -- modification here : added shipmentnum instead
1082 l_document := l_document || '<TD nowrap align=center><font color=black>' || nvl(to_char(l_line_loc.line_Num), ' ') || '</font></TD>' || NL;
1083 shipmentNum := shipmentNum + 1;
1084
1085
1086 /* Bug 2780755.
1087 * Colspan used to be 8 before FPI time phased pricing project.
1088 * But we need to show the effective start and end dates only when
1089 * document type is PA. Hence make colspan 9 for PA and changed it to 7
1090 * for other document types.
1091 */
1092 IF ((l_document_type = 'PA') and (x_subtype = 'BLANKET')) THEN
1093 l_document := l_document || '<TD colspan=9><font color=black>' || NL;
1094 else
1095 l_document := l_document || '<TD colspan=7><font color=black>' || NL;
1096 end if;
1097 if (l_line_loc.vendor_product_num is not null) then
1098 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SUPPLIER_ITEM') || ' ' || l_line_loc.vendor_product_num || '<BR wrap>' || NL;
1099 end if;
1100
1101 /*
1102 Fix for bug 2473707 by davidng
1103 Added item revision to display in the notification next to item part number
1104 */
1105 if (l_line_loc.item_revision is not null) then
1106 l_document := l_document || nvl(l_line_loc.item_num, ' ') || ' ' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REVISION') ||': ' || l_line_loc.item_revision || NL;
1107 else
1108 l_document := l_document || nvl(l_line_loc.item_num, ' ') || NL;
1109 end if;
1110 /* End fix for 2473707 */
1111
1112 begin
1113 l_mtl_system_items_desc := NULL;
1114 select allow_item_desc_update_flag into l_allow_item_desc_update
1115 from mtl_system_items_vl
1116 where inventory_item_id = l_line_loc.item_id
1117 and organization_id = l_org_id;
1118
1119 if (l_allow_item_desc_update = 'N') then
1120 select description into l_mtl_system_items_desc
1121 from mtl_system_items_vl
1122 where inventory_item_id = l_line_loc.item_id
1123 and organization_id = l_org_id;
1124 end if;
1125 exception
1126 when others then
1127 null;
1128 end;
1129
1130 l_document := l_document || '<BR wrap>' || nvl(l_mtl_system_items_desc, l_line_loc.item_desc) || NL;
1131 open item_notes_cursor(l_line_loc.org_id, l_line_loc.item_id);
1132 loop
1133
1134 fetch item_notes_cursor into l_datatype_id, l_media_id;
1135 exit when item_notes_cursor%NOTFOUND;
1136
1137 -- Bug 3129802. Should use PRE tag as the text is pre formatted
1138
1139 if (l_datatype_id = 1) then
1140 select short_text into l_item_short_text from fnd_documents_short_text
1141 where media_id = l_media_id;
1142 l_document := l_document || '<PRE>' || l_item_short_text ||'</PRE>'|| NL;
1143 elsif (l_datatype_id = 2) then
1144 select long_text into l_item_long_text from fnd_documents_long_text
1145 where media_id = l_media_id;
1146 l_document := l_document || '<PRE>' || l_item_long_text ||'</PRE>'|| NL;
1147 else
1148 null;
1149 end if;
1150
1151
1152
1153 end loop;
1154 close item_notes_cursor;
1155
1156 -- UN Number
1157 if (l_un_number is not null) then
1158 /* Bug 2774206 */
1159 /* l_document := l_document || '<BR wrap>' || l_un_number || NL; */
1160 l_document := l_document || '<BR wrap>' ||fnd_message.get_string('PO', 'PO_WF_NOTIF_UN_NUMBER')
1161 || ' ' || l_un_number;
1162 end if;
1163
1164 -- hazard class
1165 if (l_hazard_class is not null) then
1166 --Bug fix 2436110
1167 --l_document := l_document || '<BR wrap>' || l_hazard_class || NL;
1168 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_HAZARD_CLASS') || ' ' || l_hazard_class || NL;
1169 end if;
1170
1171
1172
1173 -- note to vendor
1174 if (l_line_loc.note_to_vendor is not null) then
1175 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE_TO_VENDOR') || ' ' || l_line_loc.note_to_vendor || NL;
1176 end if;
1177
1178
1179 open attachments_cursor('PO_LINES', l_line_loc.po_line_id);
1180 loop
1181
1182 fetch attachments_cursor into l_datatype_id, l_media_id;
1183 exit when attachments_cursor%NOTFOUND;
1184
1185
1186 if (l_datatype_id = 1) then
1187 select short_text into l_text from fnd_documents_short_text
1188 where media_id = l_media_id;
1189
1190 l_document := l_document || '<BR wrap>' ||
1191 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
1192 '<PRE>'|| l_text ||'</PRE>'|| NL;
1193
1194 elsif (l_datatype_id = 2) then
1195 select long_text into l_long_text from fnd_documents_long_text
1196 where media_id = l_media_id;
1197
1198 l_document := l_document || '<BR wrap>' ||
1199 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
1200 '<PRE>'|| l_long_text ||'</PRE>'|| NL;
1201
1202 else
1203 null;
1204
1205 end if;
1206
1207 end loop;
1208
1209 close attachments_cursor;
1210
1211 --<Bug 2817117 mbhargav START>
1212 --If the document is a PO, check if it refers to any
1213 --Global Agreement. If it does then email also the attachments from
1214 --that document
1215 if (l_document_type = 'PO' and l_line_loc.from_header_id is NOT NULL and
1216 l_line_loc.from_line_id is NOT NULL)
1217 then
1218 begin
1219 select nvl(global_agreement_flag, 'N') into l_global_flag
1220 from po_headers_all where po_header_id = l_line_loc.from_header_id;
1221 exception
1222 when NO_DATA_FOUND then
1223 l_global_flag := 'N';
1224 end;
1225
1226 if l_global_flag = 'Y' then
1227 --<Bug 2872552 mbhargav>
1228 --Use attachments_from_ga_cursor instead of attachments_cursor
1229 open attachments_from_ga_cursor('PO_HEADERS', l_line_loc.from_header_id);
1230 loop
1231
1232 fetch attachments_from_ga_cursor into l_datatype_id, l_media_id;
1233 exit when attachments_from_ga_cursor%NOTFOUND;
1234
1235
1236 if (l_datatype_id = 1) then
1237 select short_text into l_text from fnd_documents_short_text
1238 where media_id = l_media_id;
1239
1240 l_document := l_document || '<BR wrap>' ||
1241 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
1242 '<PRE>'|| l_text ||'</PRE>'|| NL;
1243
1244 elsif (l_datatype_id = 2) then
1245 select long_text into l_long_text from fnd_documents_long_text
1246 where media_id = l_media_id;
1247
1248 l_document := l_document || '<BR wrap>' ||
1249 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
1250 '<PRE>'|| l_long_text ||'</PRE>'|| NL;
1251
1252 else
1253 null;
1254 end if;
1255
1256 end loop;
1257
1258 close attachments_from_ga_cursor;
1259
1260 --<Bug 2872552 mbhargav>
1261 --Use attachments_from_ga_cursor instead of attachments_cursor
1262 open attachments_from_ga_cursor('PO_LINES', l_line_loc.from_line_id);
1263 loop
1264
1265 fetch attachments_from_ga_cursor into l_datatype_id, l_media_id;
1266 exit when attachments_from_ga_cursor%NOTFOUND;
1267
1268
1269 if (l_datatype_id = 1) then
1270 select short_text into l_text from fnd_documents_short_text
1271 where media_id = l_media_id;
1272
1273 l_document := l_document || '<BR wrap>' ||
1274 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
1275 '<PRE>'|| l_text ||'</PRE>'|| NL;
1276
1277 elsif (l_datatype_id = 2) then
1278 select long_text into l_long_text from fnd_documents_long_text
1279 where media_id = l_media_id;
1280
1281 l_document := l_document || '<BR wrap>' ||
1282 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
1283 '<PRE>'|| l_long_text ||'</PRE>'|| NL;
1284
1285 else
1286 null;
1287
1288 end if;
1289
1290 end loop;
1291
1292 close attachments_from_ga_cursor;
1293 end if; --its global agreement reference
1294
1295 END IF; --doc is PO and from_header_id, from_line_id are not NULL
1296 --<Bug 2817117 mbhargav END>
1297
1298 if ( l_line_loc.contract_num is not null) then
1299 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_CONTRACT_PO') || ' ' || l_line_loc.contract_num || NL;
1300 end if;
1301
1302
1303 begin
1304 if (l_document_type in ('PO', 'PA')) then
1305
1306 select vendor_quote_num into l_vendor_quote_num
1307 from po_lines_print
1308 where po_line_id = l_line_loc.po_line_id
1309 and po_header_id = l_document_id;
1310
1311 elsif (l_document_type = 'RELEASE') then
1312 select vendor_quote_num into l_vendor_quote_num
1313 from po_lines_print
1314 where po_line_id = l_line_loc.po_line_id
1315 and po_release_id = l_document_id;
1316
1317 else
1318 null;
1319
1320 end if;
1321
1322 exception
1323 when too_many_rows then
1324 null;
1325 when no_data_found then
1326 null;
1327 end;
1328
1329
1330 begin
1331
1332 if (l_document_type in ('PO', 'PA')) then
1333
1334 select po_quote_num, src_ga_flag
1335 into l_po_quote_num, l_src_ga_flag
1336 from po_lines_print
1337 where po_line_id = l_line_loc.po_line_id
1338 and po_header_id = l_document_id;
1339
1340 elsif (l_document_type = 'RELEASE') then
1341
1342 select po_quote_num into l_po_quote_num
1343 from po_lines_print
1344 where po_line_id = l_line_loc.po_line_id
1345 and po_release_id = l_document_id;
1346 else
1347
1348 null;
1349
1350 end if;
1351
1352 exception
1353 when too_many_rows then
1354 null;
1355 when no_data_found then
1356 null;
1357 end;
1358
1359
1360 if (l_po_quote_num is not null) then
1361 /* GA FPI Start - Change the ref document prompt depending on whether it is GA or Quote */
1362 if l_src_ga_flag = 'Y' then
1363
1364 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_GA_REF') || ' '
1365 || l_po_quote_num || NL;
1366 /* GA FPI End */
1367 else
1368 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_QUOTE') || ' '
1369 || l_po_quote_num || NL;
1370 end if;
1371 end if;
1372
1373 if (l_vendor_quote_num is not null) then
1374 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_VENDOR_QUOTE') || ' '
1375 || l_vendor_quote_num || NL;
1376 end if;
1377
1378
1379 l_document := l_document || '</font></TD></TR>' || NL;
1380
1381 -- SHIPMENTS OF FIRST PO LINE
1382 l_document := l_document || '<TR><TD></TD><TD WIDTH=30% valign=top><font color=black>';
1383
1384 -- Display the address only if they are distinct
1385 if (l_ship_to_count > 1) then
1386 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SMALL') || NL;
1387 /* Bug 9356725*/
1388 IF(l_line_loc.drop_ship_flag = 'Y') THEN -- Check for dropship flag If details present then display the same
1389 l_drop_ship:= PO_COMMUNICATION_PVT.get_drop_ship_details(l_line_loc.line_location_id);
1390 l_ship_cust_name := PO_COMMUNICATION_PVT.getShipCustName();
1391 l_ship_cont_name := PO_COMMUNICATION_PVT.getShipContName();
1392 l_document := l_document || '<BR wrap>' || l_ship_cust_name || NL;
1393 l_document := l_document || '<BR wrap>' || l_ship_cont_name || NL;
1394 END IF;
1395 /* Bug 9356725*/
1396 l_document := l_document || '<BR wrap>' || l_hrl_description || NL;
1397 l_document := l_document || '<BR wrap>' || l_hrl_address_line_1 || NL;
1398 if (l_hrl_address_line_2 is not null) then
1399 l_document := l_document || '<BR wrap>' || l_hrl_address_line_2 || NL;
1400 end if;
1401
1402 if (l_hrl_address_line_3 is not null) then
1403 l_document := l_document || '<BR wrap>' || l_hrl_address_line_3 || NL;
1404 end if;
1405
1406 if (l_hrl_town_or_city is not null) then
1407 l_document := l_document || '<BR wrap>' || l_hrl_town_or_city || ', ' || NL;
1408 end if;
1409
1410 if (l_hrl_to_region1 is not null) then
1411 l_document := l_document || ' ' || l_hrl_to_region1 || NL;
1412 end if;
1413
1414
1415 /* EMAILPO FPH
1416 region1 will print either state code or county or province appropriately
1417 No need to print all three
1418 if (l_hrl_to_region2 is not null) then
1419 l_document := l_document || ' ' || l_hrl_to_region2 || NL;
1420 end if;
1421 if (l_hrl_to_region3 is not null) then
1422 l_document := l_document || ' ' || l_hrl_to_region3 || NL;
1423 end if;
1424 */
1425
1426
1427 if (l_hrl_postal_code is not null) then
1428 l_document := l_document || ' ' || l_hrl_postal_code || NL;
1429 end if;
1430
1431 l_document := l_document || ' ' || l_hrl_country || NL;
1432 else
1433 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SMALL') || NL;
1434 /* Bug 9356725*/
1435 IF(l_line_loc.drop_ship_flag = 'Y') THEN -- Check for dropship flag If details present then display the same
1436 l_drop_ship:= PO_COMMUNICATION_PVT.get_drop_ship_details(l_line_loc.line_location_id);
1437 l_ship_cust_name := PO_COMMUNICATION_PVT.getShipCustName();
1438 l_ship_cont_name := PO_COMMUNICATION_PVT.getShipContName();
1439 l_document := l_document || '<BR wrap>' || l_ship_cust_name || NL;
1440 l_document := l_document || '<BR wrap>' || l_ship_cont_name || NL;
1441 END IF;
1442 /* Bug 9356725*/
1443 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SEE_ABOVE') || NL;
1444 end if;
1445
1446 /* < SUP_CON FPI > */
1447 /* Bug 2766996
1448 * Concatenate '<BR wrap>' after l_document
1449 * if consigned shipment
1450 */
1451 IF (l_line_loc.consigned_flag = 'Y') THEN
1452 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_CONSIGNED_SHIPMENT') || NL;
1453 END IF;
1454 /* < SUP_CON FPI > */
1455
1456 -- Shipment Level Note
1457 open attachments_cursor('PO_SHIPMENTS', l_line_loc.line_location_id);
1458 loop
1459
1460 fetch attachments_cursor into l_datatype_id, l_media_id;
1461 exit when attachments_cursor%NOTFOUND;
1462
1463
1464 if (l_datatype_id = 1) then
1465 select short_text into l_text from fnd_documents_short_text
1466 where media_id = l_media_id;
1467
1468 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPMENT_NOTE') || ' ' || l_text || NL;
1469
1470 elsif (l_datatype_id = 2) then
1471 select long_text into l_long_text from fnd_documents_long_text
1472 where media_id = l_media_id;
1473
1474 l_document := l_document || '<BR wrap>' || ' ' || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPMENT_NOTE') || ' ' || l_long_text || NL;
1475
1476 else
1477 null;
1478
1479 end if;
1480
1481 end loop;
1482
1483 close attachments_cursor;
1484
1485
1486 -- PO has been cancelled
1487 if (l_line_loc.cancel_flag = 'Y') then
1488 /*Modified as part of bug 7551115 changing date format*/
1489 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_CANCELLED_ON') || ' ' || to_char(l_line_loc.cancel_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1490 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''')|| NL;
1491 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_QTY_ORDERED') || ' ' || l_line_loc.quantity || NL;
1492 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_QTY_CANCELLED') || ' ' || l_line_loc.quantity_cancelled || NL;
1493 end if;
1494
1495
1496 -- Deliver to person
1497 l_requestor_count := 0;
1498 l_multiple_flag := 'N';
1499 open requestor_cursor(l_line_loc.line_location_id);
1500 loop
1501 fetch requestor_cursor into l_requestor_id;
1502 exit when requestor_cursor%NOTFOUND;
1503 l_requestor_count := l_requestor_count + 1;
1504 if (l_requestor_count > 1) then
1505 l_multiple_flag := 'Y';
1506 l_requestor_id := 0;
1507 exit;
1508 end if;
1509 end loop;
1510 close requestor_cursor;
1511
1512 if (l_requestor_id <> 0) then
1513 begin
1514
1515 select full_name,work_telephone,email_address
1516 into l_requestor_name,l_phone,l_email_address
1517 from per_all_people_f
1518 where person_id = l_requestor_id
1519 and effective_start_date <= sysdate
1520 and effective_end_date >= sysdate;
1521
1522 exception
1523 when others then
1524 l_requestor_name := null;
1525 l_phone := null;
1526 l_email_address := null;
1527 end;
1528 end if;
1529
1530 if (l_requestor_name is not null) then
1531 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQUESTER_DELIVER') || ' : ' || NL;
1532 l_document := l_document || '<BR wrap>' || l_requestor_name || NL;
1533 if (l_phone is not null) then
1534 l_document := l_document || '<BR wrap>' || l_phone || NL;
1535 end if;
1536 if (l_email_address is not null) then
1537 l_document := l_document || '<BR wrap>' || l_email_address || NL;
1538 end if;
1539 else
1540 if (l_multiple_flag = 'N') then
1541 null;
1542 else
1543 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQUESTER_DELIVER') || ' : ' || NL;
1544 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_MULTIPLE_REQUESTOR') || NL;
1545 end if;
1546 end if;
1547
1548 /* dreddy - After printing the requestor name we need to null it out
1549 because for the next record in the loop it will print even if the
1550 requestor id is null */
1551 l_requestor_name := null;
1552
1553 l_document := l_document ||'</font></TD>' || NL;
1554
1555 -- <Bug 3637864 Start> Need to display both Promised and Need By Dates
1556 l_document := l_document || '<TD nowrap valign=top><font color=black>';
1557 IF l_promised_date IS NOT null THEN
1558 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PROMISED_DATE') || '<BR>';
1559 l_document := l_document || l_promised_date;
1560 IF l_needby_date IS NOT null THEN
1561 l_document := l_document || '<BR>';
1562 END IF;
1563 END IF;
1564 IF l_needby_date IS NOT null THEN
1565 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEEDBY_DATE') || '<BR>';
1566 l_document := l_document || l_needby_date;
1567 ELSIF l_promised_date IS NULL THEN
1568 l_document := l_document || ' ';
1569 END IF;
1570 l_document := l_document || '</font></TD>' || NL;
1571 -- <Bug 3637864 End>
1572
1573 /* <TIMEPHASED FPI START> */
1574 /*
1575 Display the Effective Date and Expires On fields on to the
1576 HTML notification. This is for the first shipment.
1577 */
1578 /* Bug 2687751.
1579 * l_line_loc.start_date and l_line_loc.end_date had
1580 * to be converted into varchar variables and then concatenated
1581 * with l_document. Not doing so resulted an ORA-01858 error.
1582 */
1583 /* Bug 2780755.
1584 * Effective start and end dates have to be shown only when
1585 * the document type is PA.
1586 */
1587 /*Modified as part of bug 7551115 changing date format*/
1588 IF ((l_document_type = 'PA') and (x_subtype = 'BLANKET')) THEN
1589 l_start_date := to_char(l_line_loc.start_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1590 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''');
1591 l_end_date := to_char(l_line_loc.end_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1592 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''');
1593 l_document := l_document || '<TD nowrap><font color=black>' || nvl(l_start_date, ' ') || '</font></TD>' || NL;
1594 l_document := l_document || '<TD nowrap><font color=black>' || nvl(l_end_date, ' ') || '</font></TD>' || NL;
1595 end if;
1596
1597 /* <TIMEPHASED FPI END> */
1598
1599 /* Bug# 2493568 */
1600 l_document := l_document || '<TD nowrap align=right><font color=black>' || nvl(to_char(l_line_loc.quantity - nvl(l_line_loc.quantity_cancelled, 0)), ' ') || '</font></TD>' || NL;
1601
1602 l_document := l_document || '<TD nowrap><font color=black>' || nvl(l_line_loc.uom, ' ') || '</font></TD>' || NL;
1603 /*
1604
1605 l_document := l_document || '<TD nowrap><font color=black>' || nvl(to_char(l_line_loc.unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)), ' ') || '</font></TD>'|| NL;
1606 */
1607 l_document := l_document || '<TD nowrap><font color=black>' ||
1608 '(' || l_currency_code || ')' || l_line_loc.unit_price || '</font></TD>' ||
1609 NL;
1610 l_document := l_document || '<TD nowrap><font color=black>' || nvl(l_line_loc.taxable_flag, ' ') || '</font></TD>' || NL;
1611
1612 /* Bug# 2493568 */
1613 l_extension := (l_line_loc.quantity - nvl(l_line_loc.quantity_cancelled, 0)) * l_line_loc.unit_price;
1614 if (l_document_type = 'PA') then
1615 -- bug fix 2257742
1616 if l_min_unit is null then
1617 l_extension_total := round(nvl(l_blanket_total_amount,0), l_precision);
1618 else
1619 l_extension_total := round(nvl(l_blanket_total_amount,0)/l_min_unit) * l_min_unit;
1620 end if;
1621 else
1622 -- bug fix 2257742
1623 if l_min_unit is null then
1624 l_extension := round(nvl(l_extension,0), l_precision);
1625 else
1626 l_extension := round(nvl(l_extension,0)/l_min_unit) * l_min_unit;
1627 end if;
1628 l_extension_total := l_extension_total + l_extension;
1629 end if;
1630
1631 -- don't display the extension if this is a blanket
1632 if (l_document_type <> 'PA') then
1633
1634
1635 l_document := l_document || '<TD nowrap><font color=black>' || '(' || l_currency_code || ')' ||
1636 nvl(to_char(l_extension, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)), ' ') --bug 3405085
1637 || '</font></TD>' || NL;
1638 else
1639
1640 l_document := l_document || '<TD nowrap> </TD>'|| NL;
1641 end if;
1642
1643
1644
1645 l_document := l_document || '</TR>' || NL;
1646
1647
1648 -- Shipments of the other PO lines
1649 ELSIF (l_po_line_only = 'N') THEN
1650
1651 -- Deliver to person
1652 l_requestor_count := 0;
1653 l_multiple_flag := 'N';
1654 open requestor_cursor(l_line_loc.line_location_id);
1655 loop
1656 fetch requestor_cursor into l_requestor_id;
1657 exit when requestor_cursor%NOTFOUND;
1658 l_requestor_count := l_requestor_count + 1;
1659 if (l_requestor_count > 1) then
1660 l_multiple_flag := 'Y';
1661 l_requestor_id := 0;
1662 exit;
1663 end if;
1664 end loop;
1665 close requestor_cursor;
1666
1667 if (l_requestor_id <> 0) then
1668 begin
1669 select full_name,work_telephone,email_address
1670 into l_requestor_name,l_phone,l_email_address
1671 from per_all_people_f
1672 where person_id = l_requestor_id
1673 and effective_start_date <= sysdate
1674 and effective_end_date >= sysdate;
1675 exception
1676 when others then
1677 l_requestor_name := null;
1678 l_phone := null;
1679 l_email_address := null;
1680 end;
1681 end if;
1682
1683
1684 l_document := l_document || '<TD></TD><TD valign=top><font color=black>';
1685
1686 -- Display the address only if they are distinct
1687 if (l_ship_to_count > 1) then
1688 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SMALL') || NL;
1689 /* Bug 9356725*/
1690 IF(l_line_loc.drop_ship_flag = 'Y') THEN -- Check for dropship flag If details present then display the same
1691 l_drop_ship:= PO_COMMUNICATION_PVT.get_drop_ship_details(l_line_loc.line_location_id);
1692 l_ship_cust_name := PO_COMMUNICATION_PVT.getShipCustName();
1693 l_ship_cont_name := PO_COMMUNICATION_PVT.getShipContName();
1694 l_document := l_document || '<BR wrap>' || l_ship_cust_name || NL;
1695 l_document := l_document || '<BR wrap>' || l_ship_cont_name || NL;
1696 END IF;
1697 /* Bug 9356725*/
1698
1699 if (l_hrl_description is not null) then
1700 l_document := l_document || '<BR wrap>' || l_hrl_description || NL;
1701 end if;
1702
1703 if (l_hrl_address_line_1 is not null) then
1704 l_document := l_document || '<BR wrap>' || l_hrl_address_line_1 || NL;
1705 end if;
1706
1707 if (l_hrl_address_line_2 is not null) then
1708 l_document := l_document || '<BR wrap>' || l_hrl_address_line_2|| NL;
1709 end if;
1710
1711 if (l_hrl_address_line_3 is not null) then
1712 l_document := l_document || '<BR wrap>' || l_hrl_address_line_3|| NL;
1713 end if;
1714
1715
1716 if (l_hrl_town_or_city is not null) then
1717 l_document := l_document || '<BR wrap>' || l_hrl_town_or_city || ', ' || NL;
1718 end if;
1719
1720 if (l_hrl_to_region1 is not null) then
1721 l_document := l_document || ' ' || l_hrl_to_region1 || NL;
1722 end if;
1723
1724 /*
1725 EMAILPO FPH
1726 region1 will print either state code or county or province appropriately No need to print all three
1727 if (l_hrl_to_region2 is not null) then
1728 l_document := l_document || ' ' || l_hrl_to_region2 || NL;
1729 end if;
1730 if (l_hrl_to_region3 is not null) then
1731 l_document := l_document || ' ' || l_hrl_to_region3 || NL;
1732 end if;
1733 */
1734
1735
1736 if (l_hrl_postal_code is not null) then
1737 l_document := l_document || ' ' || l_hrl_postal_code || NL;
1738 end if;
1739
1740
1741
1742 l_document := l_document || ' ' || l_hrl_country || NL ;
1743 else
1744
1745 -- more than one shipment
1746 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SMALL') || NL;
1747 /* Bug 9356725*/
1748 IF(l_line_loc.drop_ship_flag = 'Y') THEN -- Check for dropship flag If details present then display the same
1749 l_drop_ship:= PO_COMMUNICATION_PVT.get_drop_ship_details(l_line_loc.line_location_id);
1750 l_ship_cust_name := PO_COMMUNICATION_PVT.getShipCustName();
1751 l_ship_cont_name := PO_COMMUNICATION_PVT.getShipContName();
1752 l_document := l_document || '<BR wrap>' || l_ship_cust_name || NL;
1753 l_document := l_document || '<BR wrap>' || l_ship_cont_name || NL;
1754 END IF;
1755 /* Bug 9356725*/
1756 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SEE_ABOVE') || NL;
1757
1758 -- l_document := l_document || '</font></TD>' || NL;
1759 end if;
1760
1761 /* Bug# 2557069: kagarwal
1762 ** Desc: Modified the code to display attachments for all the shipments
1763 */
1764
1765 -- Shipment Level Note
1766 open attachments_cursor('PO_SHIPMENTS', l_line_loc.line_location_id);
1767 loop
1768
1769 fetch attachments_cursor into l_datatype_id, l_media_id;
1770 exit when attachments_cursor%NOTFOUND;
1771
1772 if (l_datatype_id = 1) then
1773 select short_text into l_text from fnd_documents_short_text
1774 where media_id = l_media_id;
1775
1776 l_document := l_document || '<BR wrap>' ||
1777 fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPMENT_NOTE') ||
1778 ' ' || l_text || NL;
1779
1780 elsif (l_datatype_id = 2) then
1781 select long_text into l_long_text from fnd_documents_long_text
1782 where media_id = l_media_id;
1783
1784 l_document := l_document || '<BR wrap>' || ' ' ||
1785 fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPMENT_NOTE') ||
1786 ' ' || l_long_text || NL;
1787
1788 else
1789 null;
1790
1791 end if;
1792
1793 end loop;
1794
1795 close attachments_cursor;
1796
1797 if (l_requestor_name is not null) then
1798 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQUESTER_DELIVER') || ' : ' || NL;
1799 l_document := l_document || '<BR wrap>' || l_requestor_name || NL;
1800 if (l_phone is not null) then
1801 l_document := l_document || '<BR wrap>' || l_phone || NL;
1802 end if;
1803 if (l_email_address is not null) then
1804 l_document := l_document || '<BR wrap>' || l_email_address || NL;
1805 end if;
1806
1807 else
1808 if (l_multiple_flag = 'N') then
1809 null;
1810 else
1811 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQUESTER_DELIVER') || ' : ' || NL;
1812 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_MULTIPLE_REQUESTOR') || NL;
1813 end if;
1814 end if;
1815
1816 l_requestor_name := null;
1817
1818 l_document := l_document || '</font></TD>' || NL;
1819
1820 -- <Bug 3637864 Start> Need to display both Promised and Need By Dates
1821 l_document := l_document || '<TD nowrap valign=top><font color=black>';
1822 IF l_promised_date IS NOT null THEN
1823 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PROMISED_DATE') || '<BR>';
1824 l_document := l_document || l_promised_date;
1825 IF l_needby_date IS NOT null THEN
1826 l_document := l_document || '<BR>';
1827 END IF;
1828 END IF;
1829 IF l_needby_date IS NOT null THEN
1830 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEEDBY_DATE') || '<BR>';
1831 l_document := l_document || l_needby_date;
1832 ELSIF l_promised_date IS NULL THEN
1833 l_document := l_document || ' ';
1834 END IF;
1835 l_document := l_document || '</font></TD>' || NL;
1836 -- <Bug 3637864 End>
1837
1838 /* <TIMEPHASED FPI START> */
1839 /*
1840 Display the Effective Date and Expires On fields on to the
1841 HTML notification. This is for subsequent shipments.
1842 */
1843 /* Bug 2687751.
1844 * l_line_loc.start_date and l_line_loc.end_date had
1845 * to be converted into varchar variables and then concatenated
1846 * with l_document. Not doing so resulted an ORA-01858 error.
1847 */
1848 /* Bug 2780755.
1849 * Effective start and end dates have to be shown only when
1850 * the document type is PA.
1851 */
1852 /*Modified as part of bug 7551115 changing date format*/
1853 IF ((l_document_type = 'PA') and (x_subtype = 'BLANKET')) THEN
1854 l_start_date := to_char(l_line_loc.start_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1855 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''');
1856 l_end_date := to_char(l_line_loc.end_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1857 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''');
1858
1859 l_document := l_document || '<TD nowrap><font color=black>' || nvl(l_start_date, ' ') || '</font></TD>' || NL;
1860 l_document := l_document || '<TD nowrap><font color=black>' || nvl(l_end_date, ' ') || '</font></TD>' || NL;
1861 end if;
1862 /* <TIMEPHASED FPI END> */
1863
1864 /* Bug# 2493568 */
1865 l_document := l_document || '<TD nowrap align=right><font color=black>' || nvl(to_char(l_line_loc.quantity - nvl(l_line_loc.quantity_cancelled, 0)), ' ') || '</font></TD>' || NL;
1866
1867 l_document := l_document || '<TD nowrap><font color=black>' || nvl(l_line_loc.uom, ' ') || '</font></TD>' || NL;
1868 /*
1869
1870 l_document := l_document || '<TD nowrap><font color=black>' || nvl(to_char(l_line_loc.unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)), ' ') || '</font></TD>'|| NL;
1871
1872 */
1873 l_document := l_document || '<TD nowrap><font color=black>' ||
1874 '(' || l_currency_code || ')' || l_line_loc.unit_price ||
1875 '</font></TD>' || NL;
1876 l_document := l_document || '<TD nowrap><font color=black>' || nvl(l_line_loc.taxable_flag, ' ') || '</font></TD>' || NL;
1877
1878 /* Bug# 2493568 */
1879 l_extension := (l_line_loc.quantity - nvl(l_line_loc.quantity_cancelled, 0)) * l_line_loc.unit_price;
1880
1881
1882 if (l_document_type = 'PA') then
1883 -- bug fix 2257742
1884 if l_min_unit is null then
1885 l_extension_total := round(nvl(l_blanket_total_amount,0), l_precision);
1886 else
1887 l_extension_total := round(nvl(l_blanket_total_amount,0)/l_min_unit) * l_min_unit;
1888 end if;
1889 else
1890 -- bug fix 2257742
1891 if l_min_unit is null then
1892 l_extension := round(nvl(l_extension,0), l_precision);
1893 else
1894 l_extension := round(nvl(l_extension,0)/l_min_unit) * l_min_unit;
1895 end if;
1896 l_extension_total := l_extension_total + l_extension;
1897 end if;
1898
1899
1900 -- don't display the extension if this is a blanket
1901 if (l_document_type <> 'PA') then
1902
1903
1904 l_document := l_document || '<TD nowrap><font color=black>' ||
1905 '(' || l_currency_code || ')' ||
1906 nvl(to_char(l_extension, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)), ' ') --bug 3405085
1907 || '</font></TD>' || NL;
1908 else
1909
1910 l_document := l_document || '<TD nowrap> </TD>'|| NL;
1911 end if;
1912
1913
1914
1915 l_document := l_document || '</TR>' || NL;
1916
1917 -- LINES or SHIPMENTS
1918 ELSE
1919 null;
1920
1921 END IF;
1922
1923 /* writing the email body into a clob variable */
1924 WF_NOTIFICATION.WriteToClob(document, l_document);
1925 l_document := null;
1926
1927 end loop;
1928
1929
1930 IF (l_document_type = 'PO') THEN
1931 close shipment_cursor;
1932 ELSIF (l_document_type = 'RELEASE') THEN
1933 close shipment_release_cursor;
1934 ELSIF (l_document_type = 'PA') THEN
1935 if x_pb_count <> 0 then
1936 close shipment_blanket_cursor;
1937 else
1938 close blanket_line_cursor;
1939 end if;
1940 ELSE
1941 null;
1942 END IF;
1943
1944
1945 if l_document is null then
1946
1947 if (l_document_type <> 'PA') then
1948 l_document := l_document || '<TR>' || NL;
1949 l_document := l_document || '<TD colspan=8 align=right> ' || NL;
1950 l_document := l_document || '<B>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_TOTAL') || ' </B>' || NL;
1951
1952 l_document := l_document || '<font color=black>' || '(' || l_currency_code || ')' ||
1953 nvl(to_char(l_extension_total, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)), ' ') --bug 3405085
1954 || NL;
1955 l_document := l_document || '</font></TD></TR>';
1956 end if;
1957
1958 l_document := l_document || '</TABLE></P>' || NL;
1959
1960 WF_NOTIFICATION.WriteToClob(document, l_document);
1961 end if;
1962
1963 ELSE
1964
1965
1966 /* Now generate the text code for the individual rows in the table */
1967 --<BUG 9858430> Removed fetching org_id from the below sqls
1968 IF (l_document_type = 'PO') THEN
1969 select note_to_vendor,currency_code
1970 into l_header_note_to_vendor ,l_currency_code
1971 from po_headers
1972 where po_header_id = to_number(l_document_id);
1973 open shipment_cursor(l_document_id);
1974 ELSIF (l_document_type = 'RELEASE') THEN
1975 select note_to_vendor
1976 into l_header_note_to_vendor
1977 from po_releases where po_release_id = to_number(l_document_id);
1978 open shipment_release_cursor(l_document_id);
1979 ELSIF (l_document_type = 'PA') THEN
1980 select note_to_vendor,currency_code
1981 into l_header_note_to_vendor ,l_currency_code
1982 from po_headers
1983 where po_header_id = to_number(l_document_id);
1984 open shipment_blanket_cursor(l_document_id);
1985 END IF;
1986
1987 --<BUG 9858430 START>
1988 IF (g_po_wf_debug = 'Y') THEN
1989 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_header_note_to_vendor= ' || l_header_note_to_vendor);
1990 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_currency_code= ' || l_currency_code);
1991 END IF;
1992 --<BUG 9858430 END>
1993 -- l_currency_code := PO_CORE_S2.get_base_currency;
1994
1995 -- Getting the header level text information and displaying it if it exists
1996 if (l_document_type = 'PO') then
1997 open attachments_cursor('PO_HEADERS', l_document_id);
1998 elsif (l_document_type = 'RELEASE') then
1999 open attachments_cursor('PO_RELEASES', l_document_id);
2000 else
2001 null;
2002 end if;
2003 loop
2004
2005 fetch attachments_cursor into l_datatype_id, l_media_id;
2006 exit when attachments_cursor%NOTFOUND;
2007
2008
2009 if (l_datatype_id = 1) then
2010 select short_text into l_text from fnd_documents_short_text
2011 where media_id = l_media_id;
2012
2013 if (length(l_attachments_text) = 0) then
2014
2015 l_attachments_text := l_attachments_text || NL;
2016 l_attachments_text := l_attachments_text || fnd_message.get_string('PO', 'PO_WF_NOTIF_HEADER_NOTE') || ' ' || l_text || NL;
2017
2018 else
2019
2020 l_attachments_text := l_attachments_text || NL;
2021 l_attachments_text := l_attachments_text || fnd_message.get_string('PO', 'PO_WF_NOTIF_HEADER_NOTE') || ' ' || l_text || NL;
2022
2023 end if;
2024
2025 elsif (l_datatype_id = 2) then
2026
2027 select long_text into l_long_text from fnd_documents_long_text
2028 where media_id = l_media_id;
2029
2030 if (length(l_attachments_text) = 0) then
2031
2032 l_attachments_text := l_attachments_text || fnd_message.get_string('PO', 'PO_WF_NOTIF_HEADER_NOTE') || ' ' || l_long_text || NL;
2033
2034 else
2035
2036 l_attachments_text := l_attachments_text || fnd_message.get_string('PO', 'PO_WF_NOTIF_HEADER_NOTE') || ' ' || l_long_text || NL;
2037
2038 end if;
2039 else
2040 null;
2041
2042 end if;
2043
2044
2045 end loop;
2046
2047 close attachments_cursor;
2048
2049 --<Bug 2817117 mbhargav START>
2050 -- Getting the header level text information and displaying it if it exists
2051 -- for the blanket to which this release is sourced
2052 if (l_document_type = 'RELEASE') THEN
2053
2054 select po_header_id into l_po_header_id from po_releases
2055 where po_release_id = l_document_id;
2056
2057 open attachments_cursor('PO_HEADERS', l_po_header_id);
2058
2059 loop
2060
2061 fetch attachments_cursor into l_datatype_id, l_media_id;
2062 exit when attachments_cursor%NOTFOUND;
2063
2064
2065
2066 if (l_datatype_id = 1) then
2067 select short_text into l_text from fnd_documents_short_text
2068 where media_id = l_media_id;
2069
2070
2071 if (l_attachments_text is null) then
2072
2073 l_attachments_text := l_attachments_text ||'<PRE>'|| l_text ||'</PRE>'||NL;
2074
2075 else
2076
2077 l_attachments_text := l_attachments_text || '<BR>' || NL;
2078 l_attachments_text := l_attachments_text ||'<PRE>'|| l_text ||'</PRE>'|| NL;
2079
2080 end if;
2081
2082 elsif (l_datatype_id = 2) then
2083
2084 select long_text into l_long_text from fnd_documents_long_text
2085 where media_id = l_media_id;
2086
2087 if (l_attachments_text is null) then
2088
2089 l_attachments_text := l_attachments_text || '<BR>' || NL;
2090 -- removed nowrap
2091
2092 l_attachments_text := l_attachments_text ||'<PRE>'|| l_long_text ||'</PRE>'|| NL;
2093
2094 else
2095
2096 l_attachments_text := l_attachments_text || '<BR>' || NL;
2097 l_attachments_text := l_attachments_text ||'<PRE>'|| l_long_text ||'</PRE>'|| NL;
2098
2099 end if;
2100 else
2101 null;
2102
2103 end if;
2104
2105 end loop;
2106
2107 close attachments_cursor;
2108 end if; --its a Release
2109 --<Bug 2817117 mbhargav END>
2110
2111 if (length(l_attachments_text) = 0) then
2112 l_document := l_attachments_text || NL;
2113 end if;
2114
2115 loop
2116
2117
2118 l_un_number := null;
2119 l_hazard_class := null;
2120 l_text := null;
2121 l_datatype_id := null;
2122 l_media_id := null;
2123 l_po_quote_num := null;
2124 l_vendor_quote_num := null;
2125
2126
2127 IF (l_document_type = 'PO') THEN
2128 fetch shipment_cursor into l_line_loc;
2129 exit when shipment_cursor%notfound;
2130 ELSIF (l_document_type = 'RELEASE') THEN
2131 fetch shipment_release_cursor into l_line_loc;
2132 exit when shipment_release_cursor%notfound;
2133 ELSIF (l_document_type = 'PA') THEN
2134 fetch shipment_release_cursor into l_line_loc;
2135 exit when shipment_blanket_cursor%notfound;
2136 END IF;
2137 /*Modified as part of bug 7551115 changing date format*/
2138 l_date := to_char(nvl(l_line_loc.need_by_date, l_line_loc.promised_date),FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
2139 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''');
2140
2141 if (l_line_loc.un_number_id is not null) then
2142 select un_number into l_un_number from po_un_numbers pun
2143 where pun.un_number_id = l_line_loc.un_number_id;
2144 /* Bug 2774206 */
2145 /* l_un_number := fnd_message.get_string('PO', 'PO_WF_NOTIF_UN_NUMBER') || ' ' || l_un_number; */
2146 end if;
2147
2148 if (l_line_loc.hazard_class_id is not null) then
2149 select hazard_class into l_hazard_class
2150 from po_hazard_classes phc
2151 where phc.hazard_class_id = l_line_loc.hazard_class_id;
2152 l_hazard_class := fnd_message.get_string('PO', 'PO_WF_NOTIF_HAZARD_CLASS') || ' ' || l_hazard_class;
2153 end if;
2154
2155 l_document := l_document || NL;
2156
2157 -- modification here : added shipmentnum instead
2158 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || ' ' ;
2159 l_document := l_document || nvl(to_char(l_line_loc.line_Num), ' ') || NL;
2160 shipmentNum := shipmentNum + 1;
2161
2162
2163 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PART_NO_DESC') || ': ' || NL;
2164
2165 if (l_line_loc.vendor_product_num is not null) then
2166 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SUPPLIER_ITEM') || ' ' || l_line_loc.vendor_product_num || NL;
2167 end if;
2168
2169 /* Fix for bug 2473707 by davidng */
2170 if (l_line_loc.item_revision is not null) then
2171 l_document := l_document || nvl(l_line_loc.item_num, ' ') || ' ' || l_line_loc.item_revision || ' , ';
2172 else
2173 l_document := l_document || nvl(l_line_loc.item_num, ' ') || ' , ';
2174 end if;
2175 /* End fix for 2473707 */
2176
2177 l_document := l_document || l_line_loc.item_desc || NL;
2178
2179 -- modifications from Diwas
2180 open item_notes_cursor(l_line_loc.org_id, l_line_loc.item_id);
2181 loop
2182
2183 fetch item_notes_cursor into l_datatype_id, l_media_id;
2184 exit when item_notes_cursor%NOTFOUND;
2185
2186 -- Bug 3129802 Need to add PRE tag as the text is preformatted
2187
2188 if (l_datatype_id = 1) then
2189 select short_text into l_item_short_text from fnd_documents_short_text
2190 where media_id = l_media_id;
2191 l_document := l_document ||'<PRE>'|| l_item_short_text ||'</PRE>'|| NL;
2192 elsif (l_datatype_id = 2) then
2193 select long_text into l_item_long_text from fnd_documents_long_text
2194 where media_id = l_media_id;
2195 l_document := l_document ||'<PRE>'|| l_item_long_text ||'</PRE>'|| NL;
2196 else
2197 null;
2198 end if;
2199
2200 end loop;
2201 close item_notes_cursor;
2202
2203
2204
2205 -- UN Number
2206 if (l_un_number is not null) then
2207 /* Bug 2774206 */
2208 /* l_document := l_document || l_un_number || NL; */
2209 l_document := l_document ||fnd_message.get_string('PO', 'PO_WF_NOTIF_UN_NUMBER') || ' ' || l_un_number;
2210 end if;
2211
2212 -- hazard class
2213 if (l_hazard_class is not null) then
2214 l_document := l_document || l_hazard_class || NL;
2215 end if;
2216
2217
2218 -- PO has been cancelled
2219 if (l_line_loc.cancel_flag = 'Y') then
2220 /*Modified as part of bug 7551115 changing date format*/
2221 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_CANCELLED_ON') || ' ' || to_char(l_line_loc.cancel_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
2222 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''');
2223 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QTY_ORDERED') || ' ' || l_line_loc.quantity || NL;
2224 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QTY_CANCELLED') || ' ' || l_line_loc.quantity_cancelled || NL;
2225 end if;
2226
2227
2228 -- note to vendor
2229 if (l_line_loc.note_to_vendor is not null) then
2230 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE_TO_VENDOR') || ' ' || l_line_loc.note_to_vendor || NL;
2231 end if;
2232
2233 -- note to vendor
2234 if (l_text is not null) then
2235 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' || l_text || NL;
2236 end if;
2237
2238 -- modifications from Diwas
2239 open attachments_cursor('PO_LINES', l_line_loc.po_line_id);
2240 loop
2241
2242 fetch attachments_cursor into l_datatype_id, l_media_id;
2243 exit when attachments_cursor%NOTFOUND;
2244
2245
2246 if (l_datatype_id = 1) then
2247 select short_text into l_text from fnd_documents_short_text
2248 where media_id = l_media_id;
2249
2250 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' || l_text || NL;
2251
2252 elsif (l_datatype_id = 2) then
2253 select long_text into l_long_text from fnd_documents_long_text
2254 where media_id = l_media_id;
2255
2256 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' || l_text || NL;
2257
2258 else
2259 null;
2260
2261 end if;
2262
2263 end loop;
2264
2265 close attachments_cursor;
2266
2267 --<Bug 2817117 mbhargav START>
2268 if (l_document_type = 'PO' and l_line_loc.from_header_id is NOT NULL and
2269 l_line_loc.from_line_id is NOT NULL)
2270 then
2271 begin
2272 select nvl(global_agreement_flag, 'N') into l_global_flag
2273 from po_headers_all where po_header_id = l_line_loc.from_header_id;
2274 exception
2275 when NO_DATA_FOUND then
2276 l_global_flag := 'N';
2277 end;
2278
2279 if l_global_flag = 'Y' then
2280 --<Bug 2872552 mbhargav>
2281 --Use attachments_from_ga_cursor instead of attachments_cursor
2282 open attachments_from_ga_cursor('PO_HEADERS', l_line_loc.from_header_id);
2283 loop
2284
2285 fetch attachments_from_ga_cursor into l_datatype_id, l_media_id;
2286 exit when attachments_from_ga_cursor%NOTFOUND;
2287
2288
2289 if (l_datatype_id = 1) then
2290 select short_text into l_text from fnd_documents_short_text
2291 where media_id = l_media_id;
2292
2293 l_document := l_document || '<BR wrap>' ||
2294 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
2295 '<PRE>'|| l_text ||'</PRE>'|| NL;
2296
2297 elsif (l_datatype_id = 2) then
2298 select long_text into l_long_text from fnd_documents_long_text
2299 where media_id = l_media_id;
2300
2301 l_document := l_document || '<BR wrap>' ||
2302 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
2303 '<PRE>'|| l_long_text ||'</PRE>'|| NL;
2304
2305 else
2306 null;
2307
2308 end if;
2309
2310 end loop;
2311
2312 close attachments_from_ga_cursor;
2313
2314 --<Bug 2872552 mbhargav>
2315 --Use attachments_from_ga_cursor instead of attachments_cursor
2316 open attachments_from_ga_cursor('PO_LINES', l_line_loc.from_line_id);
2317 loop
2318
2319 fetch attachments_from_ga_cursor into l_datatype_id, l_media_id;
2320 exit when attachments_from_ga_cursor%NOTFOUND;
2321
2322
2323 if (l_datatype_id = 1) then
2324 select short_text into l_text from fnd_documents_short_text
2325 where media_id = l_media_id;
2326
2327 l_document := l_document || '<BR wrap>' ||
2328 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
2329 '<PRE>'|| l_text ||'</PRE>'|| NL;
2330
2331 elsif (l_datatype_id = 2) then
2332 select long_text into l_long_text from fnd_documents_long_text
2333 where media_id = l_media_id;
2334
2335 l_document := l_document || '<BR wrap>' ||
2336 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NOTE') || ' ' ||
2337 '<PRE>'|| l_long_text ||'</PRE>'|| NL;
2338
2339 else
2340 null;
2341
2342 end if;
2343
2344 end loop;
2345
2346 close attachments_from_ga_cursor;
2347 end if; --reference a GA
2348 END IF; --doc is PO
2349 --<Bug 2817117 mbhargav END>
2350
2351
2352 -- modifications from Diwas
2353 if ( l_line_loc.contract_num is not null) then
2354 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_CONTRACT_PO') || ' ' || l_line_loc.contract_num || NL;
2355 end if;
2356
2357
2358 select vendor_quote_num into l_vendor_quote_num
2359 from po_lines_print
2360 where po_line_id = l_line_loc.po_line_id;
2361
2362 if (l_vendor_quote_num is not null) then
2363 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_VENDOR_QUOTE') || ' ' || l_vendor_quote_num || NL;
2364 end if;
2365
2366
2367 select po_quote_num into l_po_quote_num
2368 from po_lines_print
2369 where po_line_id = l_line_loc.po_line_id;
2370
2371 if (l_po_quote_num is not null) then
2372 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_QUOTE') || ' ' || l_po_quote_num || NL;
2373 end if;
2374
2375
2376
2377
2378
2379 -- Display the address only if they are distinct
2380 if (l_ship_to_count > 1) then
2381 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SMALL') || ': ' || NL;
2382 /* Bug 9356725*/
2383 IF(l_line_loc.drop_ship_flag = 'Y') THEN -- Check for dropship flag If details present then display the same
2384 l_drop_ship:= PO_COMMUNICATION_PVT.get_drop_ship_details(l_line_loc.line_location_id);
2385 l_ship_cust_name := PO_COMMUNICATION_PVT.getShipCustName();
2386 l_ship_cont_name := PO_COMMUNICATION_PVT.getShipContName();
2387 l_document := l_document || ' ' || l_ship_cust_name || NL;
2388 l_document := l_document || ' ' || l_ship_cont_name || NL;
2389 END IF;
2390 /* Bug 9356725*/
2391 l_document := l_document || ' ' || l_hrl_description || NL;
2392 l_document := l_document || ' ' || l_hrl_address_line_1 || NL;
2393 if (l_hrl_address_line_2 is not null) then
2394 l_document := l_document || ' ' || l_hrl_address_line_2|| NL;
2395 end if;
2396
2397 if (l_hrl_address_line_3 is not null) then
2398 l_document := l_document || ' ' || l_hrl_address_line_3|| NL;
2399 end if;
2400
2401 l_document := l_document || ' ' || l_hrl_town_or_city || ' ' || l_hrl_postal_code || NL;
2402 l_document := l_document || ' ' || l_hrl_country || NL ;
2403 ELSE
2404 /* Bug 9356725*/
2405 IF(l_line_loc.drop_ship_flag = 'Y') THEN -- Check for dropship flag If details present then display the same
2406 l_drop_ship:= PO_COMMUNICATION_PVT.get_drop_ship_details(l_line_loc.line_location_id);
2407 l_ship_cust_name := PO_COMMUNICATION_PVT.getShipCustName();
2408 l_ship_cont_name := PO_COMMUNICATION_PVT.getShipContName();
2409 l_document := l_document || ' ' || l_ship_cust_name || NL;
2410 l_document := l_document || ' ' || l_ship_cont_name || NL;
2411 END IF;
2412 /* Bug 9356725*/
2413 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SEE_ABOVE') || NL;
2414 end if;
2415
2416 -- Shipment Level Note
2417 open attachments_cursor('PO_SHIPMENTS', l_line_loc.line_location_id);
2418 loop
2419
2420 fetch attachments_cursor into l_datatype_id, l_media_id;
2421 exit when attachments_cursor%NOTFOUND;
2422
2423
2424 if (l_datatype_id = 1) then
2425 select short_text into l_text from fnd_documents_short_text
2426 where media_id = l_media_id;
2427
2428 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPMENT_NOTE') || ' ' || l_text || NL;
2429
2430 elsif (l_datatype_id = 2) then
2431 select long_text into l_long_text from fnd_documents_long_text
2432 where media_id = l_media_id;
2433
2434 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIPMENT_NOTE') || ' ' || l_text || NL;
2435
2436 else
2437 null;
2438
2439 end if;
2440
2441 end loop;
2442
2443 close attachments_cursor;
2444
2445
2446 -- Deliver to person
2447
2448 l_requestor_count := 0;
2449 l_multiple_flag := 'N';
2450 open requestor_cursor(l_line_loc.line_location_id);
2451 loop
2452 fetch requestor_cursor into l_requestor_id;
2453 exit when requestor_cursor%NOTFOUND;
2454 l_requestor_count := l_requestor_count + 1;
2455 if (l_requestor_count > 1) then
2456 l_multiple_flag := 'Y';
2457 l_requestor_id := 0;
2458 exit;
2459 end if;
2460 end loop;
2461 close requestor_cursor;
2462
2463 if (l_requestor_id <> 0) then
2464 begin
2465 select full_name,work_telephone,email_address
2466 into l_requestor_name,l_phone,l_email_address
2467 from per_all_people_f
2468 where person_id = l_requestor_id
2469 and effective_start_date <= sysdate
2470 and effective_end_date >= sysdate;
2471 exception
2472 when others then
2473 l_requestor_name := null;
2474 l_phone := null;
2475 l_email_address := null;
2476 end;
2477
2478 end if;
2479 if (l_requestor_name is not null) then
2480
2481 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQUESTER_DELIVER') || ' : ' || l_requestor_name || NL;
2482 l_document := l_document || '<BR wrap>' || l_phone || NL;
2483 l_document := l_document || '<BR wrap>' || l_email_address || NL;
2484 else
2485 if (l_multiple_flag = 'N') then
2486 null;
2487 else
2488 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQUESTER_DELIVER') || ' : ' || NL;
2489 l_document := l_document || '<BR wrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_MULTIPLE_REQUESTOR') || NL;
2490 end if;
2491 end if;
2492
2493 l_requestor_name := null;
2494
2495 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DELIVERY_DATE') || ': ';
2496 l_document := l_document || nvl(l_date, ' ') || ', ';
2497
2498 /* Bug# 2493568 */
2499 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ';
2500 l_document := l_document || nvl(to_char(l_line_loc.quantity - nvl(l_line_loc.quantity_cancelled, 0)), ' ') || ', ';
2501
2502 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ';
2503 l_document := l_document || nvl(l_line_loc.uom, ' ') || ' , ';
2504
2505 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || ': ';
2506 /*
2507 l_document := l_document || nvl(to_char(l_line_loc.unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)), ' ') || ', ';
2508 */
2509 l_document := l_document || '(' || l_currency_code || ')' ||
2510 l_line_loc.unit_price || ' , ' ;
2511 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX') || ': ';
2512 l_document := l_document || nvl(l_line_loc.taxable_flag, ' ') || NL;
2513
2514 /* Bug# 2493568 */
2515 l_extension := (l_line_loc.quantity - nvl(l_line_loc.quantity_cancelled, 0)) * l_line_loc.unit_price;
2516 if (l_document_type = 'PA') then
2517 l_extension_total := l_blanket_total_amount;
2518 else
2519 l_extension_total := l_extension_total + l_extension;
2520 end if;
2521
2522 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_EXTENSION') || ': ';
2523 /*
2524 l_document := l_document || nvl(to_char(l_extension, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)), ' ') || ' , ';
2525 */
2526 l_document := l_document || '(' || l_currency_code || ')' ||
2527 nvl(to_char(l_extension, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)), ' ') --bug 3405085
2528 || ' , ';
2529
2530
2531
2532 -- modifications awaited from Diwas
2533 -- l_document := l_document || 'Shipment-level attachment' || NL;
2534
2535 l_document := l_document || NL;
2536
2537 WF_NOTIFICATION.WriteToClob(document, l_document);
2538 l_document := null;
2539 end loop;
2540
2541 IF (l_document_type = 'PO') THEN
2542 close shipment_cursor;
2543 ELSIF (l_document_type = 'RELEASE') THEN
2544 close shipment_release_cursor;
2545 ELSIF (l_document_type = 'PA') THEN
2546 close shipment_blanket_cursor;
2547 ELSE
2548 null;
2549 END IF;
2550
2551 if l_document is null then
2552 l_document := l_document || NL;
2553 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TOTAL') || ': ';
2554 l_document := l_document || '(' || l_currency_code || ')' ||
2555 nvl(to_char(l_extension_total, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)), ' ') --bug 3405085
2556 || NL;
2557 l_document := l_document || NL;
2558 WF_NOTIFICATION.WriteToClob(document, l_document);
2559 end if;
2560
2561 END IF;
2562
2563 END IF;
2564
2565 EXCEPTION
2566 WHEN OTHERS THEN
2567
2568 WF_NOTIFICATION.WriteToClob(document, 'failed');
2569
2570 RAISE;
2571
2572 END;
2573
2574
2575 procedure generate_header (document_id in varchar2,
2576 display_type in Varchar2,
2577 document in out NOCOPY varchar2,
2578 document_type in out NOCOPY varchar2) IS
2579
2580
2581 NL VARCHAR2(1) := fnd_global.newline;
2582
2583 l_document VARCHAR2(32000) := '';
2584 x_display_type varchar2(60);
2585
2586 l_operating_unit_desc HR_LOCATIONS_ALL.DESCRIPTION%TYPE;
2587 l_operating_unit_add1 HR_LOCATIONS_ALL.address_line_1%TYPE;
2588 l_operating_unit_add2 HR_LOCATIONS_ALL.address_line_2%TYPE;
2589 l_operating_unit_add3 HR_LOCATIONS_ALL.address_line_3%TYPE;
2590 l_operating_unit_city HR_LOCATIONS_ALL.town_or_city%TYPE;
2591 l_operating_unit_postal_code HR_LOCATIONS_ALL.postal_code%TYPE;
2592 -- EMAILPO FPH START--
2593 /* Bug 2766736. Changed nls_territory to territory_short_name */
2594 l_operating_unit_country FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE;
2595 l_operating_unit_state FND_LOOKUP_VALUES.MEANING%TYPE;
2596 l_operating_unit_region2 FND_LOOKUP_VALUES.MEANING%TYPE;
2597 l_operating_unit_region3 FND_LOOKUP_VALUES.MEANING%TYPE;
2598 -- EMAILPO FPH END--
2599 l_operating_unit_id NUMBER;
2600
2601
2602 l_ship_to_desc HR_LOCATIONS_ALL.DESCRIPTION%TYPE;
2603 l_ship_to_add1 HR_LOCATIONS_ALL.address_line_1%TYPE;
2604 l_ship_to_add2 HR_LOCATIONS_ALL.address_line_2%TYPE;
2605 l_ship_to_add3 HR_LOCATIONS_ALL.address_line_3%TYPE;
2606 l_ship_to_city HR_LOCATIONS_ALL.town_or_city%TYPE;
2607 l_ship_to_postal_code HR_LOCATIONS_ALL.postal_code%TYPE;
2608 -- EMAILPO FPH START--
2609 /* Bug 2766736. Changed nls_territory to territory_short_name */
2610 l_ship_to_country FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE;
2611 l_ship_to_region1 FND_LOOKUP_VALUES.MEANING%TYPE;
2612 l_ship_to_region2 FND_LOOKUP_VALUES.MEANING%TYPE;
2613 l_ship_to_region3 FND_LOOKUP_VALUES.MEANING%TYPE;
2614 -- EMAILPO FPH END--
2615
2616 l_vendor_desc PO_VENDORS.VENDOR_NAME%TYPE;
2617 l_vendor_add1 PO_VENDOR_SITES.ADDRESS_LINE1%TYPE;
2618 l_vendor_add2 PO_VENDOR_SITES.ADDRESS_LINE2%TYPE;
2619 l_vendor_add3 PO_VENDOR_SITES.ADDRESS_LINE3%TYPE;
2620 l_vendor_city PO_VENDOR_SITES.CITY%TYPE;
2621 l_vendor_state PO_VENDOR_SITES.STATE%TYPE;
2622 l_vendor_zip PO_VENDOR_SITES.ZIP%TYPE;
2623 /* Bug 2766736. Changed nls_territory to territory_short_name */
2624 l_vendor_country FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE;
2625 l_vendor_site_id NUMBER;
2626
2627 l_vendor_id NUMBER;
2628
2629 l_bill_to_desc hr_locations_all.description%TYPE;
2630 l_bill_to_add1 hr_locations_all.address_line_1%TYPE;
2631 l_bill_to_add2 hr_locations_all.address_line_2%TYPE;
2632 l_bill_to_add3 hr_locations_all.address_line_3%TYPE;
2633 l_bill_to_city hr_locations_all.town_or_city%TYPE;
2634 l_bill_to_postal_code HR_LOCATIONS_ALL.postal_code%TYPE;
2635 /* Bug 2766736. Changed hr_locations_all.country to
2636 fnd_territories_vl.territory_short_name */
2637 l_bill_to_country fnd_territories_vl.territory_short_name%TYPE;
2638 l_bill_to_region1 hr_locations_all.region_1%TYPE;
2639 l_bill_to_region2 hr_locations_all.region_2%TYPE;
2640 l_bill_to_region3 hr_locations_all.region_3%TYPE;
2641 l_bill_to_id NUMBER;
2642
2643
2644 l_po_number PO_HEADERS_ALL.SEGMENT1%TYPE;
2645 l_po_revision NUMBER;
2646 l_release_num PO_RELEASES_ALL.RELEASE_NUM%TYPE;
2647
2648 l_date_of_order VARCHAR2(80);
2649 l_buyer per_all_people_f.full_name%TYPE; --bug 15957689
2650 l_date_of_revision VARCHAR2(80);
2651 l_revision_buyer per_all_people_f.full_name%TYPE; --bug 15957689
2652
2653 l_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
2654 l_ship_via_lookup_desc ORG_FREIGHT_TL.FREIGHT_CODE_TL%TYPE;
2655 l_fob_lookup_code PO_HEADERS_ALL.FOB_LOOKUP_CODE%TYPE;
2656 l_fob_lookup_desc PO_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
2657 l_freight_terms_lc PO_HEADERS_ALL.FREIGHT_TERMS_LOOKUP_CODE%TYPE;
2658 l_freight_terms_dsp PO_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
2659 l_payment_terms_id NUMBER;
2660 l_payment_terms ap_terms_val_v.name%TYPE;
2661 l_customer_acct_num PO_VENDOR_SITES.CUSTOMER_NUM%TYPE;
2662
2663 l_org_id NUMBER; --<BUG 9858430>
2664
2665 l_vendor_contact_id NUMBER;
2666 --EMAILPO FPH--
2667 l_vendor_contact_name varchar2(200) := '';
2668 l_vendor_phone VARCHAR2(100);
2669 l_vendor_num PO_VENDORS.SEGMENT1%TYPE;
2670
2671 l_dummy_count NUMBER := 0;
2672 l_ship_to_string VARCHAR2(500) := '';
2673 l_ship_to_count NUMBER := 0;
2674 l_dummy_count_persons NUMBER := 0;
2675 l_vendor_contacts_count NUMBER := 0;
2676
2677 l_document_id VARCHAR2(30) := '';
2678 l_document_type VARCHAR2(30) := '';
2679
2680 l_po_header_id NUMBER;
2681 l_release_id NUMBER;
2682 l_location_id NUMBER;
2683
2684 l_persons_count NUMBER;
2685 l_deliver_to_id NUMBER;
2686 l_deliver_to_person per_people_f.full_name%TYPE;
2687 l_phone varchar2(20);
2688 l_email_address varchar2(80);
2689 l_legal_entity_id NUMBER;
2690
2691 /** <UTF8 FPI> **/
2692 /** tpoon 9/27/2002 **/
2693 /** Changed x_ship_to_organization_name to use %TYPE **/
2694 -- l_company_name varchar2(60);
2695 l_company_name hr_all_organization_units.name%TYPE;
2696
2697 l_requestor_count NUMBER := 0;
2698 l_multiple_flag varchar2(1);
2699 l_start_date varchar2(80);
2700 l_end_date varchar2(80);
2701 l_blanket_amt_agreed NUMBER := 0;
2702 x_subtype po_headers.type_lookup_code%TYPE;
2703
2704 cursor po_requestor_cursor (v_po_header_id varchar2) IS
2705 select distinct deliver_to_person_id
2706 FROM po_distributions
2707 WHERE po_header_id = v_po_header_id
2708 AND distribution_type <> 'AGREEMENT'; -- <Encumbrance FPJ>
2709
2710 cursor release_requestor_cursor (v_po_release_id varchar2) IS
2711 select distinct deliver_to_person_id
2712 FROM po_distributions
2713 WHERE po_release_id = v_po_release_id;
2714
2715
2716 cursor release_location_id_cursor (v_po_release_id varchar2) IS
2717 select distinct PLL.ship_to_location_id
2718 FROM po_lines_all pol, -- <R12.MOAC> --po_lines pol,
2719 po_line_locations pll
2720 where PLL.PO_RELEASE_ID = to_number(v_po_release_id)
2721 and PLL.po_line_id = POL.po_line_id;
2722
2723 /* Bug# 2684059: kagarwal
2724 ** Desc: Introduced cursor pa_location_count_cursor to count the
2725 ** distinct ship_to_location_id in PRICE BREAKS, if exists, for a Blanket PA.
2726 **
2727 ** Introduced cursor pa_header_location_id_cursor to get the
2728 ** ship_to_location_id from the PO Header table.
2729 **
2730 ** Introduced cursor pa_location_id_cursor to get the distinct
2731 ** ship_to_location_id from the Blanket PA PRICE BREAKS.
2732 */
2733
2734 cursor pa_location_count_cursor (v_po_header_id varchar2) IS
2735 select nvl(count(distinct pll.ship_to_location_id), 0)
2736 FROM po_lines_all pol, -- <R12.MOAC>
2737 po_line_locations pll
2738 where PLL.PO_HEADER_ID = to_number(v_po_header_id)
2739 and PLL.po_line_id = POL.po_line_id
2740 and PLL.shipment_type = 'PRICE BREAK';
2741
2742 cursor pa_header_location_id_cursor (v_po_header_id varchar2) IS
2743 select ship_to_location_id
2744 FROM po_headers
2745 where PO_HEADER_ID = to_number(v_po_header_id);
2746
2747 cursor pa_location_id_cursor (v_po_header_id varchar2) IS
2748 select distinct pll.ship_to_location_id
2749 FROM po_lines_all pol, -- <R12.MOAC>
2750 po_line_locations pll
2751 where PLL.PO_HEADER_ID = to_number(v_po_header_id)
2752 and PLL.po_line_id = POL.po_line_id
2753 and PLL.shipment_type = 'PRICE BREAK';
2754
2755 cursor po_location_id_cursor (v_po_header_id varchar2) IS
2756 select distinct PLL.ship_to_location_id
2757 FROM po_lines_all pol, -- <R12 MOAC>
2758 po_line_locations pll
2759 where PLL.PO_HEADER_ID = to_number(v_po_header_id)
2760 and PLL.po_line_id = POL.po_line_id;
2761
2762 cursor vendor_contacts_cursor(v_vendor_contact_id NUMBER) IS
2763 select area_code || phone
2764 from po_vendor_contacts
2765 where vendor_contact_id = v_vendor_contact_id;
2766
2767 l_document_status varchar2(100);
2768 l_price_break_count number := 0;
2769
2770 --<BUG 9858430 START>
2771 l_progress VARCHAR2(100);
2772 l_item_type wf_items.item_type%TYPE;
2773 l_item_key wf_items.item_key%TYPE;
2774 --<BUG 9858430 END>
2775
2776 /*Bug 11659917*/
2777 x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
2778 x_return_status VARCHAR2(20) ;
2779 x_msg_count NUMBER ;
2780 x_msg_data VARCHAR2(4000) ;
2781 /*Bug 11659917*/
2782
2783 begin
2784
2785
2786 --<BUG 9858430 START>
2787 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
2788 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
2789
2790 l_document_id:=wf_engine.GetItemAttrNumber (itemtype => l_item_type,
2791 itemkey => l_item_key,
2792 aname => 'DOCUMENT_ID');
2793
2794 l_document_type:=wf_engine.GetItemAttrText (itemtype => l_item_type,
2795 itemkey => l_item_key,
2796 aname => 'DOCUMENT_TYPE');
2797
2798 l_org_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
2799 itemkey => l_item_key,
2800 aname => 'ORG_ID');
2801
2802 l_progress := 'PO_EMAIL_GENERATE.GENERATE_HEADER';
2803 IF (g_po_wf_debug = 'Y') THEN
2804 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
2805 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_item_type= ' || l_item_type);
2806 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_item_key= ' || l_item_key);
2807 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_document_id= ' || l_document_id);
2808 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_document_type= ' || l_document_type);
2809 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_org_id= ' || l_org_id);
2810 END IF;
2811
2812 IF l_org_id IS NOT NULL THEN
2813 PO_MOAC_UTILS_PVT.set_org_context(to_char(l_org_id)) ;
2814 END IF;
2815 --<BUG 9858430 END>
2816
2817 x_display_type := 'text/html';
2818
2819 --2332866, check if the document is in processing, and
2820 -- show warning message to the supplier
2821 if(l_document_type in ('PO', 'PA')) then
2822 select authorization_status
2823 into l_document_status
2824 from po_headers_all
2825 where po_header_id = l_document_id;
2826
2827 elsif (l_document_type = 'RELEASE') then
2828
2829 /* Bug# 3046054: kagarwal
2830 ** Desc: The org_context is not yet set hence need to use the _all table
2831 */
2832
2833 select po_header_id into l_po_header_id from po_releases_all
2834 where po_release_id = l_document_id;
2835
2836 select authorization_status
2837 into l_document_status
2838 from po_headers_all
2839 where po_header_id = l_po_header_id;
2840 end if;
2841
2842 --<BUG 9858430 START>
2843 IF (g_po_wf_debug = 'Y') THEN
2844 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'l_document_status= ' || l_document_status);
2845 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'l_po_header_id= ' || l_po_header_id);
2846 END IF;
2847 --<BUG 9858430 END>
2848
2849 if(l_document_status is null or
2850 l_document_status in ('IN PROCESS', 'INCOMPLETE', 'REQUIRES REAPPROVAL')) then
2851 document:=fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_UNAVAILABLE');
2852 return;
2853 end if;
2854
2855 /* setting the org context here inside this procedure because wf mailer does not supply the
2856 context. */
2857 /* BUG 9858430
2858 PO_REQAPPROVAL_INIT1.get_multiorg_context (l_document_type, l_document_id, x_orgid);
2859
2860 IF x_orgid is NOT NULL THEN
2861
2862 PO_MOAC_UTILS_PVT.set_org_context(x_orgid) ; -- <R12.MOAC>
2863
2864 END IF; */
2865
2866 IF (l_document_type in ('PO', 'PA')) THEN
2867
2868 /* Bug# 2684059: kagarwal
2869 ** Desc: Get the subtype
2870 */
2871 select type_lookup_code
2872 into x_subtype
2873 from po_headers
2874 where po_header_id = l_document_id;
2875
2876 --<BUG 9858430 START>
2877 IF (g_po_wf_debug = 'Y') THEN
2878 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'x_subtype= ' || x_subtype);
2879 END IF;
2880 --<BUG 9858430 END>
2881
2882 begin
2883
2884 /* Need the org_id to get the operating unit name later */
2885 /* BUG 9858430
2886 select org_id into l_operating_unit_id from po_headers_all
2887 where po_header_id = l_document_id; */
2888
2889 l_po_header_id := l_document_id;
2890
2891 exception
2892 when no_data_found then
2893 null;
2894 end;
2895
2896 l_requestor_count := 0;
2897 l_multiple_flag := 'N';
2898 open po_requestor_cursor(l_document_id);
2899 loop
2900 fetch po_requestor_cursor into l_deliver_to_id;
2901 exit when po_requestor_cursor%NOTFOUND;
2902 l_requestor_count := l_requestor_count + 1;
2903 if (l_requestor_count > 1) then
2904 l_multiple_flag := 'Y';
2905 l_deliver_to_id := 0;
2906 exit;
2907 end if;
2908 end loop;
2909 close po_requestor_cursor;
2910
2911 if (l_deliver_to_id <> 0) then
2912 begin
2913 select full_name,work_telephone,email_address
2914 into l_deliver_to_person,l_phone,l_email_address
2915 from per_all_people_f
2916 where person_id = l_deliver_to_id
2917 and effective_start_date <= sysdate
2918 and effective_end_date >= sysdate;
2919 exception
2920 when others then
2921 l_deliver_to_person := null;
2922 l_phone := null;
2923 l_email_address := null;
2924 end;
2925 end if;
2926
2927 --<BUG 9858430 START>
2928 IF (g_po_wf_debug = 'Y') THEN
2929 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'l_deliver_to_person= ' || l_deliver_to_person);
2930 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'l_phone= ' || l_phone);
2931 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'l_email_address= ' || l_email_address);
2932 END IF;
2933 --<BUG 9858430 END>
2934
2935
2936 /* Bug# 2684059: kagarwal
2937 ** Desc: For a Blanket Purchase Agreement, check if the PRICE BREAKS exist.
2938 ** If no,then use the ship to location from the header.
2939 ** If yes, then use the ship to loction in the PRICE BREAKS.
2940 */
2941 IF ((l_document_type = 'PA') and (x_subtype = 'BLANKET')) THEN
2942 open pa_location_count_cursor(l_document_id);
2943 loop
2944 fetch pa_location_count_cursor into l_price_break_count;
2945 exit when pa_location_count_cursor%NOTFOUND;
2946 end loop;
2947 close pa_location_count_cursor;
2948
2949 if l_price_break_count = 0 then
2950 /* No price breaks, get the header ship to id */
2951
2952 open pa_header_location_id_cursor(l_document_id);
2953 loop
2954 fetch pa_header_location_id_cursor into l_location_id;
2955 exit when pa_header_location_id_cursor%NOTFOUND;
2956 l_ship_to_count := l_ship_to_count + 1;
2957 end loop;
2958 close pa_header_location_id_cursor;
2959
2960 else
2961 open pa_location_id_cursor(l_document_id);
2962 loop
2963 fetch pa_location_id_cursor into l_location_id;
2964 exit when pa_location_id_cursor%NOTFOUND;
2965 l_ship_to_count := l_ship_to_count + 1;
2966 end loop;
2967 close pa_location_id_cursor;
2968
2969 end if;
2970
2971 ELSE
2972 open po_location_id_cursor(l_document_id);
2973 loop
2974 fetch po_location_id_cursor into l_location_id;
2975 exit when po_location_id_cursor%NOTFOUND;
2976 l_ship_to_count := l_ship_to_count + 1;
2977 end loop;
2978 close po_location_id_cursor;
2979 END IF;
2980
2981 if (l_ship_to_count = 1) then
2982 -- generate the string to display the ship_to_information at header level
2983 --EMAILPO FPH--
2984 -- DISPLAY correct region (either code or full name and also full country name instead of country code
2985 begin
2986 /* Bug 2766736. Changed ftv.nls_territory to ftv.territory_short_name in
2987 the select statement. */
2988
2989 -- Bug 3574886: Query from base tables in case the session context is not set correctly
2990 -- when this SQL is executed; fetch translated columns from hr_locations_all_tl
2991 select distinct hlt.description,
2992 hrl.address_line_1,
2993 hrl.address_line_2,
2994 hrl.address_line_3,
2995 --hrl.town_or_city, -- bug#15993315 commented to fetch tow_or_city from fnd_lookup_values
2996 Decode(hrl.town_or_city,flv4.lookup_code,flv4.meaning,hrl.town_or_city) ,
2997 hrl.postal_code,
2998 ftv.territory_short_name,
2999 nvl(decode(hrl.region_1, null,
3000 hrl.region_2, decode(flv1.meaning,null,
3001 decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),
3002 flv1.lookup_code)),
3003 hrl.region_2)
3004 into
3005 l_ship_to_desc,
3006 l_ship_to_add1,
3007 l_ship_to_add2,
3008 l_ship_to_add3,
3009 l_ship_to_city,
3010 l_ship_to_postal_code,
3011 l_ship_to_country,
3012 l_ship_to_region2
3013 FROM hr_locations_all hrl,
3014 hr_locations_all_tl hlt,
3015 fnd_territories_vl ftv,
3016 fnd_lookup_values_vl flv1,
3017 fnd_lookup_values_vl flv2,
3018 fnd_lookup_values_vl flv3,
3019 fnd_lookup_values_vl flv4
3020 where hrl.region_1 = flv1.lookup_code (+)
3021 and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
3022 and hrl.location_id = hlt.location_id
3023 and hlt.language = USERENV('LANG')
3024 and hrl.region_2 = flv2.lookup_code (+)
3025 and hrl.country || '_STATE' = flv2.lookup_type (+)
3026 and hrl.region_1 = flv3.lookup_code (+)
3027 and hrl.country || '_COUNTY' = flv3.lookup_type (+)
3028 and hrl.country = ftv.territory_code(+)
3029 and HRL.location_id = l_location_id
3030 AND hrl.town_or_city = flv4.lookup_code(+)
3031 AND hrl.country || '_PROVINCE' = flv4.lookup_type (+);
3032
3033 /* Bug 2646120. The country code is not a mandatory one in hr_locations. So the country code may be null.
3034 Changed the join with ftv to outer join. */
3035
3036 exception
3037 when no_data_found then
3038
3039 BEGIN
3040 --Bug 9356725 Commented the following sql
3041 /*select distinct
3042 hrl.description,
3043 hrl.address1,
3044 hrl.address2,
3045 hrl.address3,
3046 hrl.city,
3047 hrl.postal_code,
3048 hrl.country
3049 into
3050 l_ship_to_desc,
3051 l_ship_to_add1,
3052 l_ship_to_add2,
3053 l_ship_to_add3,
3054 l_ship_to_city,
3055 l_ship_to_postal_code,
3056 l_ship_to_country
3057 FROM hz_locations hrl
3058 where HRL.location_id = l_location_id;*/
3059 --Bug 9356725 Added the following sql to get state info
3060 SELECT
3061 HLC.DESCRIPTION,
3062 HLC.ADDRESS1,
3063 HLC.ADDRESS2,
3064 HLC.ADDRESS3,
3065 HLC.CITY,
3066 HLC.POSTAL_CODE,
3067 HLC.COUNTRY,
3068 NVL(DECODE(HLC.county, NULL, HLC.state,
3069 DECODE(FCL1.MEANING, NULL,
3070 DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
3071 FCL1.LOOKUP_CODE)), HLC.state)
3072 INTO
3073 l_ship_to_desc,
3074 l_ship_to_add1,
3075 l_ship_to_add2,
3076 l_ship_to_add3,
3077 l_ship_to_city,
3078 l_ship_to_postal_code,
3079 l_ship_to_country,
3080 l_ship_to_region2
3081 FROM
3082 HZ_LOCATIONS HLC,
3083 FND_LOOKUP_VALUES FCL1,
3084 FND_LOOKUP_VALUES FCL2,
3085 FND_LOOKUP_VALUES FCL3
3086 WHERE
3087 HLC.LOCATION_ID = l_location_id AND
3088 HLC.county = FCL1.LOOKUP_CODE (+) AND
3089 HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
3090 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
3091 DECODE(FCL1.LOOKUP_CODE, NULL, '1',
3092 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE,
3093 FCL1.VIEW_APPLICATION_ID)) AND
3094 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
3095 DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
3096 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
3097 DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
3098 HLC.state = FCL2.LOOKUP_CODE (+) AND
3099 HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
3100 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
3101 DECODE(FCL2.LOOKUP_CODE, NULL, '1',
3102 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE,
3103 FCL2.VIEW_APPLICATION_ID)) AND
3104 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
3105 DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
3106 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
3107 DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
3108 HLC.county = FCL3.LOOKUP_CODE (+) AND
3109 HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
3110 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
3111 DECODE(FCL3.LOOKUP_CODE, NULL, '1',
3112 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE,
3113 FCL3.VIEW_APPLICATION_ID)) AND
3114 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
3115 DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
3116 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
3117 DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
3118
3119 exception
3120 when no_data_found then
3121 null;
3122 end;
3123 end;
3124
3125 IF (x_display_type = 'text/html') THEN
3126
3127 if (l_ship_to_desc is not null) then
3128 l_ship_to_string := l_ship_to_desc || NL;
3129 end if;
3130
3131 if (l_ship_to_add1 is not null) then
3132 l_ship_to_string := l_ship_to_string || '<BR wrap>' || l_ship_to_add1 || NL;
3133 end if;
3134
3135 if (l_ship_to_add2 is not null) then
3136 l_ship_to_string := l_ship_to_string || '<BR wrap>' || l_ship_to_add2 || NL;
3137 end if;
3138
3139 if (l_ship_to_add3 is not null) then
3140 l_ship_to_string := l_ship_to_string || '<BR wrap>' || l_ship_to_add3 || NL;
3141 end if;
3142
3143 if (l_ship_to_city is not null) then
3144 l_ship_to_string := l_ship_to_string || '<BR wrap>' || l_ship_to_city || NL;
3145 end if;
3146
3147 l_ship_to_string := l_ship_to_string || ', ' || NL;
3148
3149 /* we will only be printing the region2 column which contains the state for
3150 US addresses . region1 and region3 contain the county and other info which
3151 we will not be printing */
3152 if (l_ship_to_region2 is not null) then
3153 l_ship_to_string := l_ship_to_string || ' ' || l_ship_to_region2 || NL;
3154 end if;
3155
3156 if (l_ship_to_postal_code is not null) then
3157 l_ship_to_string := l_ship_to_string || ' ' || l_ship_to_postal_code || NL;
3158 end if;
3159
3160 l_ship_to_string := l_ship_to_string || '<br>' || l_ship_to_country || NL;
3161
3162 ELSE
3163
3164 if (l_ship_to_desc is not null) then
3165 l_ship_to_string := l_ship_to_desc || NL;
3166 end if;
3167
3168 if (l_ship_to_add1 is not null) then
3169 l_ship_to_string := l_ship_to_string || l_ship_to_add1 || NL;
3170 end if;
3171
3172 if (l_ship_to_add2 is not null) then
3173 l_ship_to_string := l_ship_to_string || l_ship_to_add2 || NL;
3174 end if;
3175
3176 if (l_ship_to_add3 is not null) then
3177 l_ship_to_string := l_ship_to_string || l_ship_to_add3 || NL;
3178 end if;
3179
3180 if (l_ship_to_city is not null) then
3181 l_ship_to_string := l_ship_to_string || l_ship_to_city || NL;
3182 end if;
3183
3184 if (l_ship_to_region2 is not null) then
3185 l_ship_to_string := l_ship_to_string || ' ' || l_ship_to_region2 || NL;
3186 end if;
3187
3188 if (l_ship_to_postal_code is not null) then
3189 l_ship_to_string := l_ship_to_string || ' ' || l_ship_to_postal_code || NL;
3190 end if;
3191
3192 l_ship_to_string := l_ship_to_string || l_ship_to_country || NL;
3193
3194
3195 END IF;
3196
3197
3198 else
3199
3200 l_ship_to_string := fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SPECIFIC');
3201
3202 end if;
3203
3204 -- RELEASE
3205 ELSE
3206
3207 IF (l_document_type = 'RELEASE') THEN
3208
3209
3210 select po_header_id into l_po_header_id from po_releases
3211 where po_release_id = l_document_id;
3212
3213 /*BUG 9858430
3214 select org_id into l_operating_unit_id from po_headers
3215 where po_header_id = l_po_header_id;*/
3216
3217 l_requestor_count := 0;
3218 l_multiple_flag := 'N';
3219 open release_requestor_cursor(l_document_id);
3220 loop
3221 fetch release_requestor_cursor into l_deliver_to_id;
3222 exit when release_requestor_cursor%NOTFOUND;
3223 l_requestor_count := l_requestor_count + 1;
3224 if (l_requestor_count > 1) then
3225 l_multiple_flag := 'Y';
3226 l_deliver_to_id := 0;
3227 exit;
3228 end if;
3229 end loop;
3230 close release_requestor_cursor;
3231
3232 if (l_deliver_to_id <> 0) then
3233 begin
3234 select full_name,work_telephone,email_address
3235 into l_deliver_to_person,l_phone,l_email_address
3236 from per_all_people_f
3237 where person_id = l_deliver_to_id
3238 and effective_start_date <= sysdate
3239 and effective_end_date >= sysdate;
3240 exception
3241 when others then
3242 l_deliver_to_person := null;
3243 l_phone := null;
3244 l_email_address := null;
3245 end;
3246 end if;
3247
3248 --<BUG 9858430 START>
3249 IF (g_po_wf_debug = 'Y') THEN
3250 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'l_deliver_to_person= ' || l_deliver_to_person);
3251 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'l_phone= ' || l_phone);
3252 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'l_email_address= ' || l_email_address);
3253 END IF;
3254 --<BUG 9858430 END>
3255
3256
3257 open release_location_id_cursor(l_document_id);
3258 loop
3259 fetch release_location_id_cursor into l_location_id;
3260 exit when release_location_id_cursor%NOTFOUND;
3261 l_ship_to_count := l_ship_to_count + 1;
3262 end loop;
3263 close release_location_id_cursor;
3264
3265 if (l_ship_to_count = 1) then
3266 -- generate the string to display the ship_to_information at header level
3267 -- EMAILPO FPH--
3268 -- region should be built correctly and the country should not be abbreviated
3269 begin
3270 /* Bug 2766736. Changed ftv.nls_territory to ftv.territory_short_name in
3271 the select statement. */
3272
3273 -- Bug 3574886: Query from base tables in case the session context is not set correctly
3274 -- when this SQL is executed; fetch translated columns from hr_locations_all_tl
3275 select distinct hlt.description,
3276 hrl.address_line_1,
3277 hrl.address_line_2,
3278 hrl.address_line_3,
3279 --hrl.town_or_city, -- bug#15993315 commented to fetch town_or_city from fnd_lookup_values
3280 Decode(hrl.town_or_city,flv4.lookup_code,flv4.meaning,hrl.town_or_city) ,
3281 hrl.postal_code,
3282 ftv.territory_short_name,
3283 nvl(decode(hrl.region_1, null,
3284 hrl.region_2, decode(flv1.meaning,null,
3285 decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),
3286 flv1.lookup_code)),
3287 hrl.region_2)
3288 into
3289 l_ship_to_desc,
3290 l_ship_to_add1,
3291 l_ship_to_add2,
3292 l_ship_to_add3,
3293 l_ship_to_city,
3294 l_ship_to_postal_code,
3295 l_ship_to_country,
3296 l_ship_to_region2
3297 FROM hr_locations_all hrl,
3298 hr_locations_all_tl hlt,
3299 fnd_territories_vl ftv,
3300 fnd_lookup_values_vl flv1,
3301 fnd_lookup_values_vl flv2,
3302 fnd_lookup_values_vl flv3,
3303 fnd_lookup_values_vl flv4
3304 where hrl.region_1 = flv1.lookup_code (+)
3305 and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
3306 and hrl.location_id = hlt.location_id
3307 and hlt.language = USERENV('LANG')
3308 and hrl.region_2 = flv2.lookup_code (+)
3309 and hrl.country || '_STATE' = flv2.lookup_type (+)
3310 and hrl.region_1 = flv3.lookup_code (+)
3311 and hrl.country || '_COUNTY' = flv3.lookup_type (+)
3312 and hrl.country = ftv.territory_code(+)
3313 and hrl.location_id = l_location_id
3314 AND hrl.town_or_city = flv4.lookup_code(+)
3315 AND hrl.country || '_PROVINCE' = flv4.lookup_type (+);
3316
3317 /* Bug 2646120. The country code is not a mandatory one in hr_locations. So the country code may be null.
3318 Changed the join with ftv to outer join. */
3319
3320 exception
3321 when no_data_found then
3322
3323 BEGIN
3324 --Bug 9356725 Commented the following sql
3325 /*select distinct
3326 hrl.description,
3327 hrl.address1,
3328 hrl.address2,
3329 hrl.address3,
3330 hrl.city,
3331 hrl.postal_code,
3332 hrl.country
3333 into
3334 l_ship_to_desc,
3335 l_ship_to_add1,
3336 l_ship_to_add2,
3337 l_ship_to_add3,
3338 l_ship_to_city,
3339 l_ship_to_postal_code,
3340 l_ship_to_country
3341 FROM hz_locations hrl
3342 where HRL.location_id = l_location_id;*/
3343 --Bug 9356725 Added the following sql to get state info
3344 SELECT
3345 HLC.DESCRIPTION,
3346 HLC.ADDRESS1,
3347 HLC.ADDRESS2,
3348 HLC.ADDRESS3,
3349 HLC.CITY,
3350 HLC.POSTAL_CODE,
3351 HLC.COUNTRY,
3352 NVL(DECODE(HLC.county, NULL, HLC.state,
3353 DECODE(FCL1.MEANING, NULL,
3354 DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
3355 FCL1.LOOKUP_CODE)), HLC.state)
3356 INTO
3357 l_ship_to_desc,
3358 l_ship_to_add1,
3359 l_ship_to_add2,
3360 l_ship_to_add3,
3361 l_ship_to_city,
3362 l_ship_to_postal_code,
3363 l_ship_to_country,
3364 l_ship_to_region2
3365 FROM
3366 HZ_LOCATIONS HLC,
3367 FND_LOOKUP_VALUES FCL1,
3368 FND_LOOKUP_VALUES FCL2,
3369 FND_LOOKUP_VALUES FCL3
3370 WHERE
3371 HLC.LOCATION_ID = l_location_id AND
3372 HLC.county = FCL1.LOOKUP_CODE (+) AND
3373 HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
3374 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
3375 DECODE(FCL1.LOOKUP_CODE, NULL, '1',
3376 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE,
3377 FCL1.VIEW_APPLICATION_ID)) AND
3378 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
3379 DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
3380 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
3381 DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
3382 HLC.state = FCL2.LOOKUP_CODE (+) AND
3383 HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
3384 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
3385 DECODE(FCL2.LOOKUP_CODE, NULL, '1',
3386 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE,
3387 FCL2.VIEW_APPLICATION_ID)) AND
3388 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
3389 DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
3390 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
3391 DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
3392 HLC.county = FCL3.LOOKUP_CODE (+) AND
3393 HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
3394 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
3395 DECODE(FCL3.LOOKUP_CODE, NULL, '1',
3396 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE,
3397 FCL3.VIEW_APPLICATION_ID)) AND
3398 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
3399 DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
3400 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
3401 DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
3402
3403 exception
3404
3405 when no_data_found then
3406 null;
3407
3408 end;
3409 end;
3410
3411 IF (x_display_type = 'text/html') THEN
3412
3413 if (l_ship_to_desc is not null) then
3414 l_ship_to_string := l_ship_to_desc || NL;
3415 end if;
3416
3417 if (l_ship_to_add1 is not null) then
3418 l_ship_to_string := l_ship_to_string || '<BR wrap>' || l_ship_to_add1 || NL;
3419 end if;
3420
3421 if (l_ship_to_add2 is not null) then
3422 l_ship_to_string := l_ship_to_string || '<BR wrap>' || l_ship_to_add2 || NL;
3423 end if;
3424
3425 if (l_ship_to_add3 is not null) then
3426 l_ship_to_string := l_ship_to_string || '<BR wrap>' || l_ship_to_add3 || NL;
3427 end if;
3428
3429 if (l_ship_to_city is not null) then
3430 l_ship_to_string := l_ship_to_string || '<BR wrap>' || l_ship_to_city || NL;
3431 end if;
3432
3433 l_ship_to_string := l_ship_to_string || ', ' || NL;
3434
3435
3436 if (l_ship_to_region2 is not null) then
3437 l_ship_to_string := l_ship_to_string || ' ' || l_ship_to_region2 || NL;
3438 end if;
3439
3440 if (l_ship_to_postal_code is not null) then
3441 l_ship_to_string := l_ship_to_string || ' ' || l_ship_to_postal_code || NL;
3442 end if;
3443
3444 l_ship_to_string := l_ship_to_string || '<BR wrap>' || l_ship_to_country || NL;
3445
3446
3447 ELSE
3448
3449 if (l_ship_to_desc is not null) then
3450 l_ship_to_string := l_ship_to_desc || NL;
3451 end if;
3452
3453 if (l_ship_to_add1 is not null) then
3454 l_ship_to_string := l_ship_to_string || l_ship_to_add1 || NL;
3455 end if;
3456
3457 if (l_ship_to_add2 is not null) then
3458 l_ship_to_string := l_ship_to_string || l_ship_to_add2 || NL;
3459 end if;
3460
3461 if (l_ship_to_add3 is not null) then
3462 l_ship_to_string := l_ship_to_string || l_ship_to_add3 || NL;
3463 end if;
3464
3465 if (l_ship_to_city is not null) then
3466 l_ship_to_string := l_ship_to_string || l_ship_to_city || NL;
3467 end if;
3468
3469 l_ship_to_string := l_ship_to_string || ', ' || NL;
3470
3471 if (l_ship_to_region2 is not null) then
3472 l_ship_to_string := l_ship_to_string || ' ' || l_ship_to_region2 || NL;
3473 end if;
3474
3475 if (l_ship_to_postal_code is not null) then
3476 l_ship_to_string := l_ship_to_string || ' ' || l_ship_to_postal_code || NL;
3477 end if;
3478
3479 l_ship_to_string := l_ship_to_string || l_ship_to_country || NL;
3480
3481
3482 END IF;
3483
3484
3485 else
3486
3487 l_ship_to_string := fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SPECIFIC');
3488
3489 end if;
3490
3491 END if; -- type = release
3492
3493 END IF; -- doc type
3494
3495
3496 begin
3497
3498 select bill_to_location_id, vendor_site_id, vendor_id, segment1,
3499 revision_num, to_char(creation_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
3500 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || ''''),
3501 to_char(revised_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
3502 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || ''''),
3503 ship_via_lookup_code, freight_terms_lookup_code, FOB_LOOKUP_CODE,
3504 nvl(vendor_contact_id, -99), terms_id,
3505 to_char(start_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
3506 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''') ,
3507 to_char(end_date, FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
3508 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || '''') ,
3509 blanket_total_amount
3510 into l_bill_to_id, l_vendor_site_id, l_vendor_id,
3511 l_po_number, l_po_revision, l_date_of_order, l_date_of_revision,
3512 l_ship_via_lookup_code, l_freight_terms_lc, l_fob_lookup_code, l_vendor_contact_id,
3513 l_payment_terms_id,l_start_date,l_end_date,l_blanket_amt_agreed
3514 from po_headers
3515 where po_header_id = to_number(l_po_header_id);
3516
3517 po_core_s.get_displayed_value('FREIGHT TERMS', l_freight_terms_lc,l_freight_terms_dsp);
3518
3519 begin
3520 select freight_code_tl into l_ship_via_lookup_desc
3521 from org_freight_vl
3522 where organization_id = l_org_id --<BUG 9858430>
3523 and freight_code = l_ship_via_lookup_code;
3524 exception
3525 when no_data_found then
3526 null;
3527 end;
3528 exception
3529 when no_data_found then
3530 null;
3531 end;
3532
3533 --EMAILPO FPH--
3534 -- Need to print the Supplier Contact Name
3535 begin
3536 select last_name || ',' || first_name into l_vendor_contact_name from
3537 po_vendor_contacts where vendor_contact_id = l_vendor_contact_id;
3538 exception
3539 when others then
3540 null;
3541 end;
3542
3543 /* Bug - 2112524 - Need to Print the displayed field for FOB instead of the code */
3544
3545 if (l_fob_lookup_code is NOT NULL) then
3546
3547 begin
3548 select displayed_field
3549 into l_fob_lookup_desc
3550 from po_lookup_codes
3551 where lookup_type = 'FOB'
3552 and lookup_code = l_fob_lookup_code;
3553
3554 exception
3555
3556 when others then
3557 null;
3558
3559 end;
3560 end if;
3561
3562 if (l_document_type = 'RELEASE') then
3563
3564 begin
3565 select release_num,
3566 to_char(revised_date ,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK',fnd_global.user_id),
3567 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),
3568 revision_num
3569 into l_release_num, l_date_of_revision, l_po_revision
3570 from po_releases
3571 where po_release_id = l_document_id;
3572
3573 /* Fix for 2341675 by davidng */
3574 select to_char(release_date , FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK',fnd_global.user_id),
3575 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN') || '''')
3576 into l_date_of_order
3577 from po_releases
3578 where po_release_id = l_document_id;
3579 /* End fix*/
3580
3581 exception
3582 when no_data_found then
3583 null;
3584 end;
3585
3586 l_po_number := l_po_number || '-' || to_char(l_release_num);
3587
3588
3589 end if;
3590
3591
3592 begin
3593
3594 /* Bug - 1774523 - Changed the query to get the legal entity name */
3595 -- bug 4969659 - removed to_char on organization_id to prevent FTS
3596 /*select name
3597 into l_company_name
3598 from hr_all_organization_units
3599 where organization_id = (select to_number(org_information2)
3600 from hr_organization_information
3601 where org_information_context = 'Operating Unit Information'
3602 and organization_id = l_operating_unit_id);*/
3603 /*Bug 11659917 Commenting the above query and using XLE API to get legal entity name*/
3604
3605 l_legal_entity_id := PO_CORE_S.get_default_legal_entity_id(l_org_id);
3606
3607 XLE_UTILITIES_GRP.Get_LegalEntity_Info(
3608 x_return_status,
3609 x_msg_count,
3610 x_msg_data,
3611 null,
3612 l_legal_entity_id,
3613 x_legalentity_info);
3614
3615 l_company_name := x_legalentity_info.name ;
3616
3617 --<BUG 9858430 START>
3618 IF (g_po_wf_debug = 'Y') THEN
3619 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'l_company_name= ' || l_company_name);
3620 END IF;
3621 --<BUG 9858430 END>
3622 -- EMAILPO FPH--
3623 -- retrieve correct region instead of just state and also print country in non abbreviated form
3624 /* Bug 2766736. Changed ftv.nls_territory to ftv.territory_short_name in
3625 the select statement. */
3626 select distinct houv.name, houv.address_line_1, houv.address_line_2, houv.address_line_3,
3627 houv.town_or_city,
3628 nvl(decode(houv.region_1,
3629 null, houv.region_2,
3630 decode(flv1.meaning,null, decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),flv1.lookup_code))
3631 ,houv.region_2),
3632 houv.postal_code, ftv.territory_short_name
3633 into
3634 l_operating_unit_desc, l_operating_unit_add1,
3635 l_operating_unit_add2, l_operating_unit_add3, l_operating_unit_city,
3636 l_operating_unit_state,l_operating_unit_postal_code, l_operating_unit_country
3637 from hr_organization_units_v houv, fnd_territories_vl ftv, fnd_lookup_values_vl flv1, fnd_lookup_values_vl flv2,
3638 fnd_lookup_values_vl flv3
3639 where
3640 houv.region_1 = flv1.lookup_code (+) and houv.country || '_PROVINCE' = flv1.lookup_type (+)
3641 and houv.region_2 = flv2.lookup_code (+) and houv.country || '_STATE' = flv2.lookup_type (+)
3642 and houv.region_1 = flv3.lookup_code (+) and houv.country || '_COUNTY' = flv3.lookup_type (+)
3643 and houv.country = ftv.territory_code(+) and organization_id = l_org_id; --<BUG 9858430>
3644
3645 /* Bug 2646120. The country code is not a mandatory one in hr_locations. So the country code may be null.
3646 Changed the join with ftv to outer join. */
3647
3648 exception
3649 when others then
3650 null;
3651 end;
3652
3653
3654 /* Bug 2766736. Changed ftv.nls_territory to ftv.territory_short_name in
3655 the select statement. */
3656 begin
3657
3658 -- Bug 3574886: Query from base tables in case the session context is not set correctly
3659 -- when this SQL is executed; fetch translated columns from hr_locations_all_tl
3660 select distinct hlt.description,
3661 hrl.address_line_1,
3662 hrl.address_line_2,
3663 hrl.address_line_3,
3664 -- hrl.town_or_city, --bug#15993315 commented to fetch town_or_city from fnd_lookup_values
3665 Decode(hrl.town_or_city,flv4.lookup_code,flv4.meaning,hrl.town_or_city) ,
3666 ftv.territory_short_name,
3667 hrl.postal_code,
3668 nvl(decode(hrl.region_1, null,
3669 hrl.region_2, decode(flv1.meaning, null,
3670 decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),
3671 flv1.lookup_code)),
3672 hrl.region_2)
3673 into l_bill_to_desc,
3674 l_bill_to_add1,
3675 l_bill_to_add2,
3676 l_bill_to_add3,
3677 l_bill_to_city,
3678 l_bill_to_country,
3679 l_bill_to_postal_code,
3680 l_bill_to_region2
3681 from hr_locations_all hrl,
3682 hr_locations_all_tl hlt,
3683 fnd_territories_vl ftv,
3684 fnd_lookup_values_vl flv1,
3685 fnd_lookup_values_vl flv2,
3686 fnd_lookup_values_vl flv3,
3687 fnd_lookup_values_vl flv4
3688 where hrl.region_1 = flv1.lookup_code (+)
3689 and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
3690 and hrl.location_id = hlt.location_id
3691 and hlt.language = USERENV('LANG')
3692 and hrl.region_2 = flv2.lookup_code (+)
3693 and hrl.country || '_STATE' = flv2.lookup_type (+)
3694 and hrl.region_1 = flv3.lookup_code (+)
3695 and hrl.country || '_COUNTY' = flv3.lookup_type (+)
3696 and hrl.country = ftv.territory_code(+)
3697 and hrl.location_id = l_bill_to_id
3698 AND hrl.town_or_city = flv4.lookup_code(+)
3699 AND hrl.country || '_PROVINCE' = flv4.lookup_type (+);
3700
3701 /* Bug 2646120. The country code is not a mandatory one in hr_locations. So the country code may be null.
3702 Changed the join with ftv to outer join. */
3703
3704 exception
3705 when no_data_found then
3706 null;
3707 end;
3708
3709 /* Bug 2766736. Changed ftv.nls_territory to ftv.territory_short_name in
3710 the select statement. */
3711 begin
3712 select pvs.address_line1, pvs.address_line2, pvs.address_line3, pvs.city, nvl(nvl(pvs.state, pvs.county), pvs.province),
3713 ftv.territory_short_name, zip
3714 into
3715 l_vendor_add1, l_vendor_add2, l_vendor_add3, l_vendor_city, l_vendor_state,
3716 l_vendor_country, l_vendor_zip
3717 from po_vendor_sites pvs , fnd_territories_vl ftv
3718 where
3719 ftv.territory_code(+) = pvs.country --<Bug 16521587>
3720 and vendor_site_id = l_vendor_site_id;
3721
3722 /* Bug 2646120. The country code is not a mandatory one in hr_locations. So the country code may be null.
3723 Changed the join with ftv to outer join. */
3724
3725 exception
3726 when no_data_found then
3727 null;
3728 end;
3729
3730 -- Need to obtain the buyers from po_headers_archive and po_releases_archive
3731 -- for global buyer's need to get buyer name from per_employees_current_x
3732 if (l_document_type in ('PO', 'PA')) THEN
3733 -- there will be an entry in this table regardless of whether there is a revision
3734 -- or not
3735 begin
3736
3737 if (l_po_revision = 0) then
3738
3739 select full_name into l_buyer
3740 from per_all_people_f --<R12 CWK Enhancement>
3741 where person_id = (select agent_id from po_headers
3742 where po_header_id = to_number(l_po_header_id))
3743 and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
3744
3745 else
3746 select full_name into l_buyer
3747 from per_all_people_f --<R12 CWK Enhancement>
3748 where person_id = (select agent_id from po_headers_archive
3749 where po_header_id = to_number(l_po_header_id) and
3750 revision_num = 0)
3751 and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
3752 end if;
3753
3754 exception
3755 when no_data_found then
3756 l_buyer := '';
3757 end;
3758
3759
3760 begin
3761 if (l_po_revision > 0) then
3762 select full_name into l_revision_buyer
3763 from per_all_people_f --<R12 CWK Enhancement>
3764 where person_id = (select agent_id from po_headers_archive
3765 where po_header_id = to_number(l_po_header_id) and
3766 revision_num = l_po_revision)
3767 and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
3768 end if;
3769
3770 exception
3771 when no_data_found then
3772 l_revision_buyer := '';
3773 end;
3774
3775 elsif (l_document_type = 'RELEASE') then
3776
3777 -- there will be an entry in this table regardless of whether there is a revision
3778 -- or not
3779 begin
3780 select full_name into l_buyer
3781 from per_all_people_f --<R12 CWK Enhancement>
3782 where person_id = (select agent_id from po_releases_archive
3783 where po_release_id = to_number(l_document_id) and
3784 revision_num = 0)
3785 and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
3786 exception
3787 when no_data_found then
3788 l_buyer := '';
3789 end;
3790
3791
3792 begin
3793 if (l_po_revision > 0) then
3794 select full_name into l_revision_buyer
3795 from per_all_people_f --<R12 CWK Enhancement>
3796 where person_id = (select agent_id from po_releases_archive
3797 where po_release_id = to_number(l_document_id) and
3798 revision_num = l_po_revision)
3799 and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
3800 else
3801 null;
3802 end if;
3803
3804 exception
3805 when no_data_found then
3806 l_revision_buyer := '';
3807 when too_many_rows then
3808 l_revision_buyer := '';
3809 end;
3810
3811 else
3812 null;
3813 end if;
3814
3815 open vendor_contacts_cursor(l_vendor_contact_id);
3816 l_vendor_contacts_count := 0;
3817 loop
3818 fetch vendor_contacts_cursor
3819 into l_vendor_phone;
3820
3821 exit when vendor_contacts_cursor%NOTFOUND;
3822 l_vendor_contacts_count := l_vendor_contacts_count + 1;
3823
3824 if (l_vendor_contacts_count > 1) then
3825 l_vendor_phone := '';
3826 end if;
3827
3828 end loop;
3829 close vendor_contacts_cursor;
3830
3831
3832 begin
3833 select nvl(pvs.customer_num, pov.customer_num) into l_customer_acct_num
3834 from po_vendor_sites pvs, po_vendors pov
3835 where pvs.vendor_site_id = l_vendor_site_id
3836 and pov.vendor_id = l_vendor_id;
3837 exception
3838 when others then
3839 l_customer_acct_num := '';
3840 end;
3841 begin
3842 select segment1, vendor_name
3843 into l_vendor_num, l_vendor_desc
3844 from po_vendors pov
3845 where pov.vendor_id = l_vendor_id;
3846 exception
3847 when others then
3848 l_vendor_num := '';
3849 l_vendor_desc := '';
3850 end;
3851
3852 begin
3853 select name into l_payment_terms
3854 from ap_terms_val_v apv
3855 where apv.TERM_ID = l_payment_terms_id;
3856 exception
3857 when others then
3858 l_payment_terms := '';
3859 end;
3860
3861 IF (x_display_type = 'text/html') THEN
3862
3863 l_document := l_document || '<table width=100% border=0 cellpadding=2 cellspacing=1 cols=3 rows=2>' || NL;
3864 l_document := l_document || '<!-- header -->' || NL;
3865 l_document := l_document || ' <tr>' || NL;
3866 l_document := l_document || ' <!-- ORACLE, ship-to, PURCHASE-ORDER -->' || NL;
3867 l_document := l_document || ' <td width=45% valign=top>' || NL;
3868 l_document := l_document || ' <!-- ORACLE -->' || NL;
3869
3870
3871
3872 -- ------------------------------------------------- START: COMPANY NAME / ADDRESS
3873 l_document := l_document || ' <font color=black size=+2>' || NL;
3874 l_document := l_document || ' ' || l_company_name || NL;
3875 l_document := l_document || ' </font><br>' || NL;
3876 /*
3877 l_document := l_document || ' <font color=black> ' || l_operating_unit_desc || NL;
3878 l_document := l_document || ' </font><br>' || NL;
3879 */
3880 l_document := l_document || ' <font color=black> ' || l_operating_unit_add1 || NL;
3881 if (l_operating_unit_add2 is not null) then
3882 l_document := l_document || ' <br> ' || l_operating_unit_add2 || NL;
3883 end if;
3884 if (l_operating_unit_add3 is not null) then
3885 l_document := l_document || ' <br> ' || l_operating_unit_add3 || NL;
3886 end if;
3887 if (l_operating_unit_city is not null) then
3888 l_document := l_document || ' <br> ' || l_operating_unit_city || NL;
3889 end if;
3890 if (l_operating_unit_state is not null) then
3891 l_document := l_document || ', ' || l_operating_unit_state || NL;
3892 end if;
3893 if (l_operating_unit_postal_code is not null) then
3894 l_document := l_document || ' ' || l_operating_unit_postal_code || NL;
3895 end if;
3896 if (l_operating_unit_country is not null) then
3897 l_document := l_document || ' <br> ' || l_operating_unit_country || NL;
3898 end if;
3899 l_document := l_document || ' </font> </br>' || NL;
3900 -- ------------------------------------------------- ENDED: COMPANY NAME / ADDRESS
3901
3902 l_document := l_document || ' </td>' || NL;
3903 l_document := l_document || ' <td width=25% valign=top>' || NL;
3904 l_document := l_document || ' <!-- ship-to -->' || NL;
3905 l_document := l_document || ' <b><u><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO') || '</font></u></b><br>' || NL;
3906 l_document := l_document || ' <font color=black>' || l_ship_to_string || '<br></font>' || NL;
3907
3908 l_document := l_document || ' </td>' || NL;
3909 l_document := l_document || ' <td width=30% valign=top>' || NL;
3910 l_document := l_document || ' <!-- PURCHASE-ORDER -->' || NL;
3911 l_document := l_document || ' <table border=2 rows=2 cols=2>' || NL;
3912 l_document := l_document || ' <tr>' || NL;
3913 l_document := l_document || ' <td colspan=2 align=center valign=top>' || NL;
3914 l_document := l_document || ' <b><font size=+2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_PURCHASE_ORDER') || '</font></b><br>' || NL;
3915 l_document := l_document || ' </td>' || NL;
3916 l_document := l_document || ' </tr>' || NL;
3917 l_document := l_document || ' <tr>' || NL;
3918 l_document := l_document || ' <td width=50% align=left valign=top>' || NL;
3919 l_document := l_document || ' <!-- PO number -->' || NL;
3920 l_document := l_document || ' <b><font size=-3>' ||fnd_message.get_string('PO', 'PO_WF_NOTIF_PURCHASE_ORDER_NO') || '</font></b><br>' || NL;
3921
3922
3923
3924 -- ------------------------------------------------- START: PURCHASE ORDER NO.
3925 l_document := l_document || ' <font color=black>' || NL;
3926 l_document := l_document || ' ' || l_po_number || NL;
3927 l_document := l_document || ' <br></font>' || NL;
3928 -- ------------------------------------------------- ENDED: PURCHASE ORDER NO.
3929
3930
3931
3932 l_document := l_document || ' <p>' || NL;
3933 l_document := l_document || ' </td>' || NL;
3934 l_document := l_document || ' <td width=50% align=left valign=top>' || NL;
3935 l_document := l_document || ' <!-- Revision -->' || NL;
3936
3937
3938
3939 -- ------------------------------------------------- START: REVISION
3940 l_document := l_document || ' <b><font size=-3>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REVISION') || '</font></b><br>' || NL;
3941 l_document := l_document || ' <font color=black>' || NL;
3942 l_document := l_document || ' ' || l_po_revision || NL;
3943 l_document := l_document || ' <br></font>' || NL;
3944 -- ------------------------------------------------- ENDED: REVISION
3945
3946
3947
3948
3949
3950 l_document := l_document || ' <p>' || NL;
3951 l_document := l_document || ' </td>' || NL;
3952 l_document := l_document || ' </tr>' || NL;
3953
3954
3955 l_document := l_document || ' </table><br>' || NL;
3956 l_document := l_document || ' </font>' || NL;
3957 l_document := l_document || ' </td>' || NL;
3958
3959 l_document := l_document || ' </tr>' || NL;
3960 l_document := l_document || ' <tr>' || NL;
3961 l_document := l_document || ' <!-- Vendor, bill-to, dates -->' || NL;
3962 l_document := l_document || ' <td width=45% align=left valign=top>' || NL;
3963 l_document := l_document || ' <!-- Vendor -->' || NL;
3964
3965 /* COMMENTING THIS OUT FOR LEFT ALIGNMENT OF VENDOR INFO, replaced with block below
3966 l_document := l_document || ' <table cols=2>' || NL;
3967 l_document := l_document || ' <tr>' || NL;
3968 l_document := l_document || ' <td width=20% align=left valign=top>' || NL;
3969
3970 l_document := l_document || ' <b><u><font size=-1>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_VENDOR') || '</font></u></b>' || NL;
3971 l_document := l_document || ' </td>' || NL;
3972 l_document := l_document || ' <td width=80% align=left valign=top>' || NL;
3973 */
3974
3975
3976 -- this line for the case without the vendor info
3977 l_document := l_document || ' <b><u><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_VENDOR') || '</font></u></b>' || NL;
3978
3979 -- ------------------------------------------------- START: VENDOR_ADDR
3980 l_document := l_document || '<font color=black>' || NL;
3981 l_document := l_document || '<br>' || l_vendor_desc || NL;
3982 l_document := l_document || '<br>' || l_vendor_add1 || NL;
3983 if (l_vendor_add2 is not null) then
3984 l_document := l_document || '<br>' || l_vendor_add2 || NL;
3985 end if;
3986 if (l_vendor_add3 is not null) then
3987 l_document := l_document || '<br>' || l_vendor_add3 || NL;
3988 end if;
3989 /* Removed the not null check as we are printing city,state and zip
3990 on the same line
3991 */
3992 l_document := l_document || '<br>' || l_vendor_city || ',' || l_vendor_state || ' ' || l_vendor_zip || NL;
3993 if (l_vendor_country is not null) then
3994 l_document := l_document || '<br>' || l_vendor_country || NL;
3995 end if;
3996
3997 l_document := l_document || '<br></font>' || NL;
3998
3999 --EMAILPO FPH--
4000 /* Commented off for 2336672 by davidng */
4001 -- l_document := l_document || '<b><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_VENDOR_ATTN') || ':</font></b>'|| l_vendor_contact_name || NL;
4002
4003 -- ------------------------------------------------- ENDED: VENDOR_ADDR
4004
4005
4006
4007 -- These three lines are commented out when the table for vendor add is removed
4008 -- l_document := l_document || ' </td>' || NL;
4009 -- l_document := l_document || ' </tr>' || NL;
4010 -- l_document := l_document || ' </table>' || NL;
4011
4012
4013 l_document := l_document || ' </td>' || NL;
4014 l_document := l_document || ' <td width=25% valign=top>' || NL;
4015 l_document := l_document || ' <!-- bill-to -->' || NL;
4016 l_document := l_document || ' <b><u><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_BILL_TO') || '</font></u></b><br>' || NL;
4017
4018
4019
4020 -- ------------------------------------------------- START: BILL TO
4021 l_document := l_document || ' <font color=black>' || NL;
4022 l_document := l_document || ' ' || l_bill_to_desc || NL;
4023 if (l_bill_to_add1 is not null) then
4024 l_document := l_document || ' <br> ' || l_bill_to_add1 || NL;
4025 end if;
4026 if (l_bill_to_add2 is not null) then
4027 l_document := l_document || ' <br> ' || l_bill_to_add2 || NL;
4028 end if;
4029 if (l_bill_to_add3 is not null) then
4030 l_document := l_document || ' <br> ' || l_bill_to_add3 || NL;
4031 end if;
4032
4033 if (l_bill_to_city is not null) then
4034 l_document := l_document || ' <br> ' || l_bill_to_city ;
4035 end if;
4036 l_document := l_document || ', ' || NL;
4037
4038 if (l_bill_to_region2 is not null) then
4039 l_document := l_document || l_bill_to_region2 ;
4040 end if;
4041
4042 if (l_bill_to_postal_code is not null) then
4043 l_document := l_document || ' ' || l_bill_to_postal_code ;
4044 end if;
4045 if (l_bill_to_country is not null) then
4046 l_document := l_document || '<br> ' || l_bill_to_country || NL;
4047
4048 end if;
4049 l_document := l_document || ' <br></font>' || NL;
4050 -- ------------------------------------------------- START: BILL TO
4051
4052
4053
4054 l_document := l_document || ' </td>' || NL;
4055 l_document := l_document || ' <td width=30% valign=top>' || NL;
4056 l_document := l_document || ' <!-- dates -->' || NL;
4057 l_document := l_document || ' <table width=100% border=2 rows=2 cols=3>' || NL;
4058 l_document := l_document || ' <tr>' || NL;
4059 l_document := l_document || ' <td width=40% valign=top>' || NL;
4060 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE_OF_ORDER') || '</font></b><br>' || NL;
4061
4062
4063
4064 -- ------------------------------------------------- START: DATE OF ORDER
4065 l_document := l_document || ' <font color=black>' || NL;
4066 l_document := l_document || ' ' || l_date_of_order || NL;
4067 l_document := l_document || ' <br></font>' || NL;
4068 -- ------------------------------------------------- ENDED: DATE OF ORDER
4069
4070
4071
4072 l_document := l_document || ' </td>' || NL;
4073 l_document := l_document || ' <td width=60% align=center valign=top>' || NL;
4074 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_BUYER') || '</font></b><br>' || NL;
4075
4076
4077
4078 -- ------------------------------------------------- START: BUYER
4079 l_document := l_document || ' <font color=black>' || NL;
4080 l_document := l_document || ' ' || l_buyer || NL;
4081 l_document := l_document || ' </font>' || NL;
4082 -- ------------------------------------------------- ENDED: BUYER
4083
4084
4085
4086 l_document := l_document || ' </td>' || NL;
4087 l_document := l_document || ' </tr>' || NL;
4088 l_document := l_document || ' <tr>' || NL;
4089 l_document := l_document || ' <td width=40% valign=top>' || NL;
4090 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE_OF_REVISION') || '</font></b><br>' || NL;
4091
4092
4093
4094 -- ------------------------------------------------- START: DATE OF REVISION
4095 l_document := l_document || ' <font color=black>' || NL;
4096 l_document := l_document || ' ' || l_date_of_revision || NL;
4097 l_document := l_document || ' <br></font>' || NL;
4098 -- ------------------------------------------------- ENDED: DATE OF REVISION
4099
4100
4101
4102 l_document := l_document || ' </td>' || NL;
4103 l_document := l_document || ' <td width=60% align=center valign=top>' || NL;
4104 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_BUYER') || '</font></b><br>' || NL;
4105
4106
4107
4108 -- ------------------------------------------------- START: BUYER
4109
4110 l_document := l_document || ' <font color=black>' || NL;
4111 l_document := l_document || ' ' || l_revision_buyer || NL;
4112 l_document := l_document || ' </font>' || NL;
4113 -- ------------------------------------------------- ENDED: BUYER
4114
4115
4116
4117 l_document := l_document || ' </td>' || NL;
4118 l_document := l_document || ' </tr>' || NL;
4119 l_document := l_document || ' </table>' || NL;
4120 l_document := l_document || ' </td>' || NL;
4121 l_document := l_document || ' </tr>' || NL;
4122 l_document := l_document || '</table>' || NL;
4123
4124
4125
4126 l_document := l_document || '<table width=100% border=1 cellpadding=2 cellspacing=1 cols=6 rows=2>' || NL;
4127 l_document := l_document || '<!-- other info -->' || NL;
4128 l_document := l_document || ' <tr>' || NL;
4129 l_document := l_document || ' <td valign=top width=10%>' || NL;
4130 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_CUSTOMER_ACCT_NO') || '</font></b><br>' || NL;
4131
4132
4133
4134 -- ------------------------------------------------- START: CUST A/C s.t. here?
4135 l_document := l_document || ' <font color=black>' || NL;
4136 l_document := l_document || ' ' || l_customer_acct_num || NL;
4137 l_document := l_document || ' </font>' || NL;
4138 -- ------------------------------------------------- ENDED: CUST A/C
4139
4140
4141
4142 l_document := l_document || ' </td>' || NL;
4143
4144 l_document := l_document || ' <td valign=top width=10%>' || NL;
4145 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO','PO_WF_NOTIF_VENDOR_NO') || '</font></b><br>' || NL;
4146
4147
4148
4149 -- ------------------------------------------------- START: VENDOR NO. s.t/ here?
4150 l_document := l_document || ' <font color=black>' || NL;
4151 l_document := l_document || ' ' || l_vendor_num || NL;
4152 l_document := l_document || ' </font>' || NL;
4153 -- ------------------------------------------------- ENDED: VENDOR NO.
4154
4155
4156
4157 l_document := l_document || ' </td>' || NL;
4158 l_document := l_document || ' <td valign=top width=20%>' || NL;
4159 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_PAYMENT_TERMS') || '</font></b><br>' || NL;
4160
4161
4162
4163 -- ------------------------------------------------- START: PAYMENT TERMS
4164 l_document := l_document || ' <font color=black>' || NL;
4165 l_document := l_document || ' ' || l_payment_terms || NL;
4166 l_document := l_document || ' </font>' || NL;
4167 -- ------------------------------------------------- ENDED: PAYMENT TERMS
4168
4169
4170
4171 l_document := l_document || ' </td>' || NL;
4172 l_document := l_document || ' <td valign=top width=20%>' || NL;
4173 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_FREIGHT_TERMS') || '</font></b><br>' || NL;
4174
4175
4176
4177 -- ------------------------------------------------- START: FREIGHT TERMS
4178 l_document := l_document || ' <font color=black>' || NL;
4179 l_document := l_document || ' ' || nvl(l_freight_terms_dsp, l_freight_terms_lc) || NL;
4180 l_document := l_document || ' </font>' || NL;
4181 -- ------------------------------------------------- ENDED: FREIGHT TERMS
4182
4183
4184
4185 l_document := l_document || ' </td>' || NL;
4186 l_document := l_document || ' <td valign=top width=20%>' || NL;
4187 l_document := l_document || '<b><font size=-2>' || NL;
4188 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_FOB') || NL;
4189 l_document := l_document || '</font></b>' || NL;
4190
4191
4192
4193 -- ------------------------------------------------- START: F.O.B.
4194 l_document := l_document || ' <br><font color=black>' || NL;
4195 l_document := l_document || ' ' || l_fob_lookup_desc || NL;
4196 l_document := l_document || ' </font>' || NL;
4197 -- ------------------------------------------------- ENDED: F.O.B.
4198
4199
4200
4201 l_document := l_document || ' </td>' || NL;
4202 l_document := l_document || ' <td valign=top width=20%>' || NL;
4203 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_VIA') || '</font></b><br>' || NL;
4204
4205
4206
4207 -- ------------------------------------------------- START: SHIP VIA
4208 l_document := l_document || ' <font color=black>' || NL;
4209 l_document := l_document || ' ' || nvl(l_ship_via_lookup_desc, l_ship_via_lookup_code) || NL;
4210 l_document := l_document || ' </font>' || NL;
4211 -- ------------------------------------------------- ENDED: SHIP VIA
4212
4213 /* bug 2073564 : For blanket and contract PO's we show the effectivity dates
4214 and the amount agreed on the email . For a planned PO we only shoe the
4215 effectivity dates */
4216
4217 if (l_document_type = 'PA') then
4218
4219 l_document := l_document || ' </td>' || NL;
4220 l_document := l_document || ' </tr>' || NL;
4221 l_document := l_document || ' <tr>' || NL;
4222 l_document := l_document || ' <td valign=top colspan=3>' || NL;
4223 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO','PO_EMAIL_BLANKET_START_DATE') || '</font></b><br>' || NL;
4224
4225 -- ------------------------------------------------- START: START DATE
4226 l_document := l_document || ' <font color=black>' || NL;
4227
4228 l_document := l_document || ' ' || l_start_date || NL;
4229 l_document := l_document || ' </font>' || NL;
4230 -- ------------------------------------------------- ENDED: START DATE
4231
4232 l_document := l_document || ' </td>' || NL;
4233 l_document := l_document || ' <td valign=top colspan=2>' || NL;
4234 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO','PO_EMAIL_BLANKET_END_DATE') || '</font></b><br>' || NL;
4235
4236 -- ------------------------------------------------- START: END DATE
4237 l_document := l_document || ' <font color=black>' || NL;
4238
4239 l_document := l_document || ' ' || l_end_date || NL;
4240 l_document := l_document || ' </font>' || NL;
4241 -- ------------------------------------------------- ENDED: END DATE
4242
4243 l_document := l_document || ' </td>' || NL;
4244 l_document := l_document || ' <td valign=top colspan=1>' || NL;
4245 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO','PO_EMAIL_BLANKET_AMT_AGREED') || '</font></b><br>' || NL;
4246
4247 -- ------------------------------------------------- START: AMT AGREED
4248 l_document := l_document || ' <font color=black>' || NL;
4249
4250 l_document := l_document || ' ' || l_blanket_amt_agreed || NL;
4251 l_document := l_document || ' </font>' || NL;
4252 -- ------------------------------------------------- ENDED: AMT AGREED
4253
4254 end if;
4255 IF (l_document_type = 'PO') THEN
4256 select type_lookup_code
4257 into x_subtype
4258 from po_headers
4259 where po_header_id = l_document_id;
4260
4261 --<BUG 9858430 START>
4262 IF (g_po_wf_debug = 'Y') THEN
4263 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_item_type, l_item_key, 'x_subtype= ' || x_subtype);
4264 END IF;
4265 --<BUG 9858430 END>
4266
4267 IF (x_subtype = 'PLANNED') THEN
4268
4269 l_document := l_document || ' </td>' || NL;
4270 l_document := l_document || ' </tr>' || NL;
4271 l_document := l_document || ' <tr>' || NL;
4272 l_document := l_document || ' <td valign=top colspan=3>' || NL;
4273 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO','PO_EMAIL_BLANKET_START_DATE') || '</font></b><br>' || NL;
4274
4275 -- ------------------------------------------------- START: START DATE
4276 l_document := l_document || ' <font color=black>' || NL;
4277
4278 l_document := l_document || ' ' || l_start_date || NL;
4279 l_document := l_document || ' </font>' || NL;
4280 -- ------------------------------------------------- ENDED: START DATE
4281
4282 l_document := l_document || ' </td>' || NL;
4283 l_document := l_document || ' <td valign=top colspan=3>' || NL;
4284 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO','PO_EMAIL_BLANKET_END_DATE') || '</font></b><br>' || NL;
4285
4286 -- ------------------------------------------------- START: END DATE
4287 l_document := l_document || ' <font color=black>' || NL;
4288
4289 l_document := l_document || ' ' || l_end_date || NL;
4290 l_document := l_document || ' </font>' || NL;
4291 -- ------------------------------------------------- ENDED: END DATE
4292
4293 END IF;
4294 END IF;
4295
4296
4297
4298 l_document := l_document || ' </td>' || NL;
4299 l_document := l_document || ' </tr>' || NL;
4300 l_document := l_document || ' <tr>' || NL;
4301 l_document := l_document || ' <td valign=top colspan=4>' || NL;
4302 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO','PO_WF_NOTIF_CONFIRM_TO_TELE') || '</font></b><br>' || NL;
4303
4304 -- ------------------------------------------------- START: CONFIRM TO/TELEPHONE
4305 l_document := l_document || ' <font color=black>' || NL;
4306
4307 /*
4308 Displaying the Contact Name and Telephone number in the Confirm To/Telephone field
4309 */
4310
4311 if (l_vendor_contact_name is not null) then
4312 l_document := l_document || ' ' || l_vendor_contact_name || ' ' || NL;
4313 l_document := l_document || ' </font>' || NL;
4314 end if;
4315 /* END FIX */
4316
4317 l_document := l_document || ' ' || l_vendor_phone || NL;
4318 l_document := l_document || ' </font>' || NL;
4319 -- ------------------------------------------------- ENDED: CONFIRM TO/TELEPHONE
4320
4321
4322
4323 l_document := l_document || ' </td>' || NL;
4324 l_document := l_document || ' <td valign=top colspan=2>' || NL;
4325 l_document := l_document || ' <b><font size=-2>' || fnd_message.get_string('PO','PO_WF_NOTIF_REQUESTER_DELIVER') || '</font></b><br>' || NL;
4326
4327
4328
4329 -- ------------------------------------------------- START: REQUEST/DELIVER TO
4330 l_document := l_document || ' <font color=black>' || NL;
4331 if (l_deliver_to_person is not null) then
4332 l_document := l_document || ' ' || l_deliver_to_person || ' ' || NL;
4333 l_document := l_document || ' </font>' || NL;
4334 l_document := l_document || ' <font color=black>' || l_phone || l_email_address || ' ' || NL;
4335 l_document := l_document || ' </font>' || NL;
4336 else
4337 if (l_multiple_flag = 'N') then
4338 null;
4339 else
4340 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_MULTIPLE_REQUESTOR') || NL;
4341 end if;
4342 end if;
4343 -- ------------------------------------------------- ENDED: REQUEST/DELIVER TO
4344
4345
4346
4347
4348
4349 l_document := l_document || ' </td>' || NL;
4350 l_document := l_document || ' </tr>' || NL;
4351 l_document := l_document || '</table>' || NL;
4352
4353 ELSE
4354
4355
4356 -- ------------------------------------------------- START: COMPANY NAME / ADDRESS
4357 /*
4358 l_document := l_document || l_operating_unit_desc || NL;
4359 */
4360 l_document := l_document || l_company_name || NL;
4361 l_document := l_document || l_operating_unit_add1 || NL;
4362
4363 if (l_operating_unit_add2 is not null) then
4364 l_document := l_document || l_operating_unit_add2 || NL;
4365 end if;
4366 if (l_operating_unit_add3 is not null) then
4367 l_document := l_document || l_operating_unit_add3 || NL;
4368 end if;
4369 if (l_operating_unit_city is not null) then
4370 l_document := l_document || l_operating_unit_city || NL;
4371 end if;
4372 if (l_operating_unit_state is not null) then
4373 l_document := l_document || ', ' || l_operating_unit_state || NL;
4374 end if;
4375 if (l_operating_unit_postal_code is not null) then
4376 l_document := l_document || ' ' || l_operating_unit_postal_code || NL;
4377 end if;
4378 if (l_operating_unit_country is not null) then
4379 l_document := l_document || l_operating_unit_country || NL;
4380 end if;
4381 l_document := l_document || NL;
4382 -- ------------------------------------------------- ENDED: COMPANY NAME / ADDRESS
4383
4384 l_document := l_document || '-**- ' || fnd_message.get_string('PO', 'PO_WF_NOTIF_PURCHASE_ORDER') || ' -**-' || NL;
4385 l_document := l_document || NL;
4386
4387 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_TO_SMALL') || NL;
4388 l_document := l_document || l_ship_to_string || NL;
4389
4390 l_document := l_document || NL;
4391 -- l_document := l_document || ' <!-- PURCHASE-ORDER -->' || NL;
4392
4393
4394 -- l_document := l_document || ' <!-- PO number -->' || NL;
4395 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PURCHASE_ORDER_NO') || ': ';
4396
4397
4398
4399 -- ------------------------------------------------- START: PURCHASE ORDER NO.
4400 l_document := l_document || l_po_number || ' , ';
4401 -- ------------------------------------------------- ENDED: PURCHASE ORDER NO.
4402
4403
4404 -- ------------------------------------------------- START: REVISION
4405 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REVISION') || ': ';
4406 l_document := l_document || l_po_revision || NL;
4407 -- ------------------------------------------------- ENDED: REVISION
4408
4409
4410 l_document := l_document || NL;
4411
4412 -- l_document := l_document || ' <!-- Vendor, bill-to, dates -->' || NL;
4413 -- l_document := l_document || ' <!-- Vendor -->' || NL;
4414
4415 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_VENDOR') || NL;
4416
4417
4418
4419 -- ------------------------------------------------- START: VENDOR_ADDR
4420 l_document := l_document || l_vendor_desc || NL;
4421 l_document := l_document || l_vendor_add1 || NL;
4422 l_document := l_document || l_vendor_add2 || NL;
4423 l_document := l_document || l_vendor_add3 || NL;
4424 l_document := l_document || l_vendor_city || NL;
4425 l_document := l_document || l_vendor_state || NL;
4426 l_document := l_document || l_vendor_zip || NL;
4427 l_document := l_document || l_vendor_country || NL;
4428
4429 -- ------------------------------------------------- ENDED: VENDOR_ADDR
4430
4431
4432
4433 -- l_document := l_document || ' <!-- bill-to -->' || NL;
4434 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_BILL_TO') || NL;
4435
4436
4437
4438 -- ------------------------------------------------- START: BILL TO
4439 l_document := l_document || l_bill_to_desc || NL;
4440 l_document := l_document || l_bill_to_add1 || NL;
4441 l_document := l_document || l_bill_to_city || NL;
4442 l_document := l_document || l_bill_to_region2 || NL;
4443 l_document := l_document || l_bill_to_postal_code || NL;
4444 l_document := l_document || l_bill_to_country || NL;
4445 l_document := l_document || NL;
4446 -- ------------------------------------------------- START: BILL TO
4447
4448
4449 -- l_document := l_document || '<!-- dates -->' || NL;
4450 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE_OF_ORDER') || ': ';
4451
4452
4453
4454 -- ------------------------------------------------- START: DATE OF ORDER
4455 l_document := l_document || l_date_of_order || ' , ';
4456 -- ------------------------------------------------- ENDED: DATE OF ORDER
4457
4458 l_document := l_document || fnd_message.get_string('PO', 'POA_BUYER') || ': ';
4459
4460
4461 -- ------------------------------------------------- START: BUYER
4462 l_document := l_document || l_buyer || NL;
4463 -- ------------------------------------------------- ENDED: BUYER
4464
4465
4466 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE_OF_REVISION') || ': ';
4467
4468 -- ------------------------------------------------- START: DATE OF REVISION
4469 l_document := l_document || l_date_of_revision || ' , ';
4470 -- ------------------------------------------------- ENDED: DATE OF REVISION
4471
4472
4473 l_document := l_document || fnd_message.get_string('PO', 'POA_BUYER') || ': ' || NL;
4474
4475
4476
4477 -- ------------------------------------------------- START: BUYER Do we need to have s.t. here?
4478
4479 -- ------------------------------------------------- ENDED: BUYER
4480
4481
4482 l_document := l_document || NL;
4483
4484 -- l_document := l_document || '<!-- other info -->' || NL;
4485 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_CUSTOMER_ACCT_NO') || ': ';
4486
4487
4488
4489 -- ------------------------------------------------- START: CUST A/C s.t. here?
4490 l_document := l_document || l_customer_acct_num || ' , ';
4491 -- ------------------------------------------------- ENDED: CUST A/C
4492
4493
4494
4495 l_document := l_document || fnd_message.get_string('PO','PO_WF_NOTIF_VENDOR_NO') || ': ';
4496
4497
4498
4499 -- ------------------------------------------------- START: VENDOR NO. s.t/ here?
4500 l_document := l_document || l_vendor_num || NL;
4501 -- ------------------------------------------------- ENDED: VENDOR NO.
4502
4503
4504
4505 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PAYMENT_TERMS') || ': ';
4506
4507 -- ------------------------------------------------- START: PAYMENT TERMS
4508 l_document := l_document || l_payment_terms || NL;
4509 -- ------------------------------------------------- ENDED: PAYMENT TERMS
4510
4511
4512
4513 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_FREIGHT_TERMS') || ': ';
4514
4515
4516 -- ------------------------------------------------- START: FREIGHT TERMS
4517 l_document := l_document || nvl(l_freight_terms_dsp, l_freight_terms_lc) || ' , ';
4518 -- ------------------------------------------------- ENDED: FREIGHT TERMS
4519
4520
4521 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_FOB') || ': ';
4522
4523
4524 -- ------------------------------------------------- START: F.O.B.
4525 l_document := l_document || l_fob_lookup_desc || ' , ';
4526 -- ------------------------------------------------- ENDED: F.O.B.
4527
4528
4529 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_VIA') || ': ';
4530
4531 -- ------------------------------------------------- START: SHIP VIA
4532 l_document := l_document || nvl(l_ship_via_lookup_desc, l_ship_via_lookup_code) || NL;
4533 -- ------------------------------------------------- ENDED: SHIP VIA
4534
4535
4536 l_document := l_document || fnd_message.get_string('PO','PO_WF_NOTIF_CONFIRM_TO_TELE') || ': ';
4537
4538 -- ------------------------------------------------- START: CONFIRM TO/TELEPHONE
4539 l_document := l_document || l_vendor_phone || NL;
4540 -- ------------------------------------------------- ENDED: CONFIRM TO/TELEPHONE
4541
4542
4543 l_document := l_document || fnd_message.get_string('PO','PO_WF_NOTIF_REQUESTER_DELIVER') || ': ';
4544
4545
4546 -- ------------------------------------------------- START: REQUEST/DELIVER TO
4547 l_document := l_document || l_deliver_to_person || NL;
4548 l_document := l_document || l_phone || NL;
4549 l_document := l_document || l_email_address || NL;
4550 -- ------------------------------------------------- ENDED: REQUEST/DELIVER TO
4551
4552
4553 END IF;
4554
4555 document := l_document;
4556
4557 EXCEPTION
4558 WHEN OTHERS THEN
4559 null;
4560 RAISE;
4561
4562 end;
4563
4564 /*************************************************************************************/
4565 --EMAILPO FPH--
4566 --changed api signature. Refer to spec for additional explanation
4567 procedure generate_terms (document_id in varchar2,
4568 display_type in varchar2,
4569 document in out NOCOPY clob,
4570 document_type in out NOCOPY varchar2) IS
4571
4572 v_filehandle UTL_FILE.FILE_TYPE;
4573 l_filedir varchar2(2000) := null;
4574 l_filename varchar2(2000) := null;
4575 v_terms varchar2(4000) := null;
4576 NL VARCHAR2(1) := fnd_global.newline;
4577 l_document varchar2(32000);
4578 x_progress varchar2(3);
4579 x_display_type varchar2(60);
4580
4581 --EMAILPO FPH START--
4582 l_user_id number;
4583 l_application_id number;
4584 l_responsibility_id number;
4585 l_userid_name varchar2(100) := 'USER_ID';
4586 l_appid_name varchar2(100) := 'APPLICATION_ID';
4587 l_respid_name varchar2(100) := 'RESPONSIBILITY_ID';
4588 l_item_type varchar2(300) := document_id;
4589 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
4590 l_filename_lang varchar2(2000) := null;
4591 --EMAILPO FPH END--
4592
4593 l_document_id number ;
4594 l_document_type VARCHAR2(30) := '';
4595 l_document_status varchar2(100);
4596 l_po_header_id number;
4597 l_api_name CONSTANT VARCHAR2(30) := 'generate_terms'; -- Bug 2792156
4598
4599 l_progress VARCHAR2(100); --<BUG 9858430>
4600 l_org_id NUMBER; --<BUG 9858430>
4601
4602 BEGIN
4603
4604 /* get the directory and file name where the terms and conditions
4605 for email PO are stored */
4606 l_document := '';
4607 x_progress := '000';
4608 x_display_type := 'text/html';
4609
4610
4611 /* EMAILPO FPH START
4612 set the context so fnd_profile.get methods work correctly
4613 For older wf notifications that send in documentid and documenttypecode instead of
4614 itemtype and itemkey combination the following code to get the user_id, app_id and
4615 resp_id will fail. Will trap and ignore it. The consequence is that tandc profile
4616 options will only be considered at site level(consistent with Previous Behaviour/Bug)
4617 */
4618 BEGIN
4619 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
4620 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
4621
4622 --2332866, check if the document is in processing, and
4623 -- show warning message to the supplier
4624 l_document_id:=wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
4625 itemkey => l_item_key,
4626 aname => 'DOCUMENT_ID');
4627 --
4628 l_document_type:=wf_engine.GetItemAttrText ( itemtype => l_item_type,
4629 itemkey => l_item_key,
4630 aname => 'DOCUMENT_TYPE');
4631 --<BUG 9858430 START>
4632 l_org_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
4633 itemkey => l_item_key,
4634 aname => 'ORG_ID');
4635
4636 l_progress := 'PO_EMAIL_GENERATE.GENERATE_TERMS';
4637 IF (g_po_wf_debug = 'Y') THEN
4638 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
4639 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_item_type= ' || l_item_type);
4640 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_item_key= ' || l_item_key);
4641 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_document_id= ' || l_document_id);
4642 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_document_type= ' || l_document_type);
4643 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_org_id= ' || l_org_id);
4644 END IF;
4645
4646 IF l_org_id IS NOT NULL THEN
4647 PO_MOAC_UTILS_PVT.set_org_context(to_char(l_org_id)) ;
4648 END IF;
4649 --<BUG 9858430 END>
4650
4651 if(l_document_type in ('PO', 'PA')) then
4652 select authorization_status
4653 into l_document_status
4654 from po_headers_all
4655 where po_header_id = l_document_id;
4656
4657 elsif (l_document_type = 'RELEASE') then
4658
4659 select po_header_id into l_po_header_id from po_releases
4660 where po_release_id = l_document_id;
4661
4662 select authorization_status
4663 into l_document_status
4664 from po_headers_all
4665 where po_header_id = l_po_header_id;
4666 end if;
4667
4668 --<BUG 9858430 START>
4669 IF (g_po_wf_debug = 'Y') THEN
4670 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_document_status= ' || l_document_status);
4671 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_po_header_id= ' || l_po_header_id);
4672 END IF;
4673 --<BUG 9858430 END>
4674
4675 if(l_document_status is null or
4676 l_document_status in ('IN PROCESS', 'INCOMPLETE', 'REQUIRES REAPPROVAL')) then
4677 WF_NOTIFICATION.WriteToClob(document, ' ');
4678 return;
4679 end if;
4680
4681
4682 l_user_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
4683 itemkey => l_item_key,
4684 aname => l_userid_name);
4685
4686 l_application_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
4687 itemkey => l_item_key,
4688 aname => l_appid_name);
4689
4690 l_responsibility_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
4691 itemkey => l_item_key,
4692 aname => l_respid_name);
4693 --<BUG 9858430 START>
4694 IF (g_po_wf_debug = 'Y') THEN
4695 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_user_id= ' || l_user_id);
4696 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_application_id= ' || l_application_id);
4697 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,'l_responsibility_id= ' || l_responsibility_id);
4698 END IF;
4699 --<BUG 9858430 END>
4700
4701 EXCEPTION WHEN OTHERS THEN
4702 NULL;
4703 END;
4704 --EMAILPO FPH END--
4705
4706
4707 -- bug 3128426: removed call to fnd_global.apps_initialize
4708 -- instead, use fnd_profile.value_specific to get
4709 -- profile values w/o resetting the app context
4710 l_filedir := FND_PROFILE.VALUE_SPECIFIC(
4711 name => 'PO_EMAIL_TERMS_DIR_NAME'
4712 , user_id => l_user_id
4713 , responsibility_id => l_responsibility_id
4714 , application_id => l_application_id
4715 );
4716 l_filename := FND_PROFILE.VALUE_SPECIFIC(
4717 name => 'PO_EMAIL_TERMS_FILE_NAME'
4718 , user_id => l_user_id
4719 , responsibility_id => l_responsibility_id
4720 , application_id => l_application_id
4721 );
4722
4723 IF (l_filedir is null) OR (l_filename is null) THEN
4724 if (x_display_type = 'text/html') then
4725 l_document := '<p></p>';
4726 else
4727 l_document := ' ';
4728 end if;
4729 WF_NOTIFICATION.WriteToClob(document, l_document);
4730 ELSE
4731
4732 x_progress := '001';
4733
4734 /*
4735 EMAILPO FPH START--
4736 Check for supplier site language tandc file first
4737 if that doesn't exist then check for base language tandc file else check for just l_filename
4738 */
4739 l_filename_lang := l_filename || '_' || userenv('LANG');
4740 BEGIN
4741 /* open the file */
4742 v_filehandle := UTL_FILE.FOPEN(l_filedir,l_filename_lang,'r');
4743 EXCEPTION WHEN OTHERS THEN
4744 BEGIN
4745 l_filename_lang := l_filename || '_' || fnd_global.base_language;
4746 v_filehandle := UTL_FILE.FOPEN(l_filedir,l_filename_lang, 'r');
4747 EXCEPTION WHEN OTHERS THEN
4748 v_filehandle := UTL_FILE.FOPEN(l_filedir,l_filename, 'r');
4749 END;
4750 END;
4751 --EMAILPO FPH END--
4752
4753 x_progress := '002';
4754 IF (x_display_type = 'text/html') THEN
4755 l_document := l_document || '<p>' || NL;
4756 loop
4757
4758 begin
4759 x_progress := '003';
4760 /* write the contents into the document */
4761 UTL_FILE.GET_LINE(v_filehandle,v_terms);
4762 l_document := l_document || v_terms || NL;
4763 l_document := l_document || '<br>' || NL;
4764 WF_NOTIFICATION.WriteToClob(document, l_document);
4765 l_document := null;
4766
4767 x_progress := '004';
4768
4769 exception
4770 when no_data_found then
4771 exit;
4772 end;
4773
4774 end loop;
4775
4776 if l_document is null then
4777 WF_NOTIFICATION.WriteToClob(document, '</p>');
4778 end if;
4779
4780 ELSE
4781
4782 loop
4783
4784 begin
4785 x_progress := '003';
4786 /* write the contents into the document */
4787 UTL_FILE.GET_LINE(v_filehandle,v_terms);
4788 l_document := l_document || v_terms || NL;
4789 WF_NOTIFICATION.WriteToClob(document, l_document);
4790 x_progress := '004';
4791
4792 exception
4793 when no_data_found then
4794 exit;
4795 end;
4796 end loop;
4797
4798 END IF;
4799
4800
4801 x_progress := '005';
4802 /* close the file */
4803 UTL_FILE.FCLOSE(v_filehandle);
4804
4805 END IF;
4806
4807 -- Bug 2792156
4808 -- When any type of exception occurs during the generation of the Terms and Conditions file,
4809 -- it will no longer be raised. Instead, the Email notification will be sent out as
4810 -- normal but without any attachment of Terms and Conditions and a message will be logged
4811 -- in the FND_LOG_MESSAGES table.
4812 EXCEPTION
4813 WHEN UTL_FILE.INVALID_PATH THEN
4814 l_document := '<p></p>';
4815 WF_NOTIFICATION.WriteToClob(document, l_document);
4816 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4817 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, c_log_head || l_api_name ||'.EXCEPTION',
4818 'generate_terms: Exception Type: INVALID_PATH' ||x_progress||sqlcode);
4819 END IF;
4820 UTL_FILE.FCLOSE(v_filehandle);
4821
4822 WHEN UTL_FILE.INVALID_MODE THEN
4823 l_document := '<p></p>';
4824 WF_NOTIFICATION.WriteToClob(document, l_document);
4825 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4826 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, c_log_head || l_api_name ||'.EXCEPTION',
4827 'generate_terms: Exception Type: INVALID_MODE' ||x_progress||sqlcode);
4828 END IF;
4829 UTL_FILE.FCLOSE(v_filehandle);
4830
4831 WHEN UTL_FILE.INTERNAL_ERROR THEN
4832 l_document := '<p></p>';
4833 WF_NOTIFICATION.WriteToClob(document, l_document);
4834 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4835 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, c_log_head || l_api_name ||'.EXCEPTION',
4836 'generate_terms: Exception Type: INTERNAL_ERROR' ||x_progress||sqlcode);
4837 END IF;
4838 UTL_FILE.FCLOSE(v_filehandle);
4839
4840 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
4841 l_document := '<p></p>';
4842 WF_NOTIFICATION.WriteToClob(document, l_document);
4843 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4844 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, c_log_head || l_api_name ||'.EXCEPTION',
4845 'generate_terms: Exception Type: INVALID_FILEHANDLE' ||x_progress||sqlcode);
4846 END IF;
4847 UTL_FILE.FCLOSE(v_filehandle);
4848
4849 WHEN UTL_FILE.INVALID_OPERATION THEN
4850 l_document := '<p></p>';
4851 WF_NOTIFICATION.WriteToClob(document, l_document);
4852 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4853 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, c_log_head || l_api_name ||'.EXCEPTION',
4854 'generate_terms: Exception Type: INVALID_OPERATION' ||x_progress||sqlcode);
4855 END IF;
4856 UTL_FILE.FCLOSE(v_filehandle);
4857
4858 WHEN UTL_FILE.READ_ERROR THEN
4859 l_document := '<p></p>';
4860 WF_NOTIFICATION.WriteToClob(document, l_document);
4861 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4862 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, c_log_head || l_api_name ||'.EXCEPTION',
4863 'generate_terms: Exception Type: READ_ERROR' ||x_progress||sqlcode);
4864 END IF;
4865 UTL_FILE.FCLOSE(v_filehandle);
4866
4867 WHEN OTHERS THEN
4868 l_document := '<p></p>';
4869 WF_NOTIFICATION.WriteToClob(document, l_document);
4870 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4871 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, c_log_head || l_api_name ||'.EXCEPTION',
4872 'generate_terms: Exception Type: OTHERS' ||x_progress||sqlcode);
4873 END IF;
4874 UTL_FILE.FCLOSE(v_filehandle);
4875 END;
4876
4877
4878 END PO_EMAIL_GENERATE;