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