[Home] [Help]
PACKAGE BODY: APPS.POS_ASN_NOTIF
Source
1 PACKAGE BODY POS_ASN_NOTIF AS
2 /* $Header: POSASNNB.pls 120.15.12020000.3 2013/02/09 13:51:42 hvutukur ship $ */
3
4 TYPE AsnBuyerArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5
6 asn_buyers AsnBuyerArray;
7 asn_buyers_empty AsnBuyerArray;
8 asn_buyer_num INTEGER := 0;
9
10 PROCEDURE GENERATE_NOTIF (
11 p_shipment_num IN VARCHAR2,
12 p_notif_type IN VARCHAR2,
13 p_vendor_id IN NUMBER,
14 p_vendor_site_id IN NUMBER,
15 p_user_id IN INTEGER)
16 IS
17
18 l_item_type VARCHAR2(20) := 'POSASNNB';
19 l_item_key VARCHAR2(240) ;
20 l_seq_val NUMBER;
21 l_supp_username VARCHAR2(320);
22 l_supplier_displayname VARCHAR2(360);
23
24 BEGIN
25
26 SELECT po_wf_itemkey_s.nextval INTO l_seq_val FROM dual;
27 l_item_key := 'POSASNNB_' || p_shipment_num || '_' || to_char(l_seq_val);
28
29 if (p_notif_type = 'CANCEL') then
30 wf_engine.createProcess( ItemType => l_item_type,
31 ItemKey => l_item_key,
32 Process => 'BUYER_NOTIF_CANCEL'
33 );
34 else
35 wf_engine.createProcess( ItemType => l_item_type,
36 ItemKey => l_item_key,
37 Process => 'BUYER_NOTIFICATION'
38 );
39 end if;
40
41 -- Get the supplier user name
42 WF_DIRECTORY.GetUserName( 'FND_USR',
43 p_user_id,
44 l_supp_username,
45 l_supplier_displayname);
46
47 wf_engine.SetItemAttrText
48 (
49 ItemType => l_item_type,
50 ItemKey => l_item_key,
51 aname => 'SHIPMENT_NUM',
52 avalue => p_shipment_num
53 );
54
55 wf_engine.SetItemAttrText
56 (
57 ItemType => l_item_type,
58 ItemKey => l_item_key,
59 aname => 'SUPPLIER_USERNAME',
60 avalue => l_supp_username
61 );
62
63 wf_engine.SetItemAttrNumber
64 (
65 ItemType => l_item_type,
66 ItemKey => l_item_key,
67 aname => 'VENDOR_ID',
68 avalue => p_vendor_id
69 );
70
71 wf_engine.SetItemAttrNumber
72 (
73 ItemType => l_item_type,
74 ItemKey => l_item_key,
75 aname => 'VENDOR_SITE_ID',
76 avalue => p_vendor_site_id
77 );
78 --dbms_output.put_line('Item Key ' || l_item_key );
79 wf_engine.StartProcess( ItemType => l_item_type,
80 ItemKey => l_item_key );
81
82
83 END GENERATE_NOTIF;
84
85 PROCEDURE GENERATE_WC_NOTIF
86 (
87 p_wc_num IN VARCHAR2,
88 p_wc_id IN NUMBER,
89 p_wc_status IN VARCHAR2,
90 p_po_header_id IN NUMBER,
91 p_buyer_id IN NUMBER,
92 p_user_id IN NUMBER,
93 x_return_status OUT NOCOPY VARCHAR2,
94 x_return_msg OUT NOCOPY VARCHAR2)
95 IS
96
97 l_item_type VARCHAR2(20) := 'WCAPPRV';
98 l_item_key VARCHAR2(240);
99 l_seq_val NUMBER;
100 l_supp_username VARCHAR2(320);
101 l_supplier_displayname VARCHAR2(360);
102 l_buyer_user_name VARCHAR2(320);
103 l_buyer_user_displayname VARCHAR2(360);
104
105 BEGIN
106
107 SELECT po_wf_itemkey_s.nextval INTO l_seq_val FROM dual;
108 l_item_key := 'WCAPPRV_' || p_wc_num || '_' || to_char(l_seq_val);
109
110
111 wf_engine.createProcess
112 (
113 ItemType => l_item_type,
114 ItemKey => l_item_key,
115 Process => 'BUYER_NOTIF_WC_CANCEL'
116 );
117
118
119 -- Get the supplier user name
120 WF_DIRECTORY.GetUserName( 'FND_USR',
121 p_user_id,
122 l_supp_username,
123 l_supplier_displayname);
124
125 WF_DIRECTORY.GetUserName( 'PER',
126 p_buyer_id,
127 l_buyer_user_name,
128 l_buyer_user_displayname);
129
130
131
132 wf_engine.SetItemAttrText(
133 ItemType => l_item_type,
134 ItemKey => l_item_key,
135 aname => 'BUYER_NAME',
136 avalue => l_buyer_user_name
137 );
138
139
140 wf_engine.SetItemAttrText
141 (
142 ItemType => l_item_type,
143 ItemKey => l_item_key,
144 aname => 'WORK_CONFIRMATION_ID',
145 avalue => p_wc_id
146 );
147
148 wf_engine.SetItemAttrText
149 (
150 ItemType => l_item_type,
151 ItemKey => l_item_key,
152 aname => 'WORK_CONFIRMATION_NUMBER',
153 avalue => p_wc_num
154 );
155
156
157 wf_engine.SetItemAttrText
158 (
159 ItemType => l_item_type,
160 ItemKey => l_item_key,
161 aname => 'DOC_STATUS',
162 avalue => p_wc_status
163 );
164
165 wf_engine.SetItemAttrText
166 (
167 ItemType => l_item_type,
168 ItemKey => l_item_key,
169 aname => 'PO_DOCUMENT_ID',
170 avalue => p_po_header_id
171 );
172
173 wf_engine.SetItemAttrText
174 (
175 ItemType => l_item_type,
176 ItemKey => l_item_key,
177 aname => 'SUPPLIER_USERNAME',
178 avalue => l_supp_username
179 );
180
181 wf_engine.StartProcess( ItemType => l_item_type,
182 ItemKey => l_item_key );
183
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185
186 EXCEPTION
187
188 WHEN OTHERS THEN
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
190
191 END GENERATE_WC_NOTIF;
192
193
194
195
196 -- This procedure sets the next Buyer to send notification
197 PROCEDURE SET_NEXT_BUYER(
198 l_item_type IN VARCHAR2,
199 l_item_key IN VARCHAR2,
200 actid IN NUMBER,
201 funcmode IN VARCHAR2,
202 result OUT NOCOPY VARCHAR2
203 )
204 IS
205
206 x_buyer_user_name VARCHAR2(320);
207 x_buyer_user_displayname VARCHAR2(360);
208 x_total_num_buyers NUMBER;
209 x_curr_buyer NUMBER;
210 x_shipment_num VARCHAR2(80);
211 x_vendor_id NUMBER;
212 x_vendor_site_id NUMBER;
213
214 h_expected_receipt_date_ts TIMESTAMP;
215 h_expected_receipt_date_print VARCHAR2(100);
216 l_user_id NUMBER;
217 l_timezone_conversion VARCHAR2(1);
218 l_server_tz NUMBER;
219 l_client_tz NUMBER;
220
221 BEGIN
222 --dbms_output.put_line('Calling Set Next Buyer');
223 x_total_num_buyers := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
224 itemkey => l_item_key,
225 aname => 'TOTAL_BUYER_NUM');
226
227 x_curr_buyer := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
228 itemkey => l_item_key,
229 aname => 'CURR_BUYER_NUM');
230
231 x_shipment_num := wf_engine.GetItemAttrText ( itemtype => l_item_type,
232 itemkey => l_item_key,
233 aname => 'SHIPMENT_NUM');
234
235 x_vendor_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
236 itemkey => l_item_key,
237 aname => 'VENDOR_ID');
238
239 x_vendor_site_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
240 itemkey => l_item_key,
241 aname => 'VENDOR_SITE_ID');
242
243 /* code added for bug 10408761
244 conversion of expected receipt date and shipment date according to buyer time zone
245
246 the following code resets the value of expected receipt date timestamp value back to server time zone
247 for each buyer (in case this value was modified for any previous buyer user)
248 */
249
250 BEGIN
251
252 SELECT poh.expected_receipt_date
253 INTO h_expected_receipt_date_ts
254 FROM POS_HEADERS_V poh,PO_VENDORS pov
255 WHERE poh.shipment_num = x_shipment_num AND
256 poh.vendor_id = pov.vendor_id AND
257 poh.vendor_id = to_number(x_vendor_id) AND
258 poh.vendor_site_id = to_number(x_vendor_site_id);
259
260 EXCEPTION
261
262 WHEN OTHERS then
263 RAISE;
264
265 END;
266
267 h_expected_receipt_date_print := to_char(h_expected_receipt_date_ts,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS');
268
269 wf_engine.SetItemAttrText(ItemType => l_item_type,
270 ItemKey => l_item_key,
271 aname => 'EXPECTED_RECEIPT_TS',
272 avalue => h_expected_receipt_date_print
273 );
274 -- end of code added to reset the expected receipt date timestamp
275
276 --dbms_output.put_line('Buyer Num is ' || to_char(x_curr_buyer));
277
278
279 IF ( x_curr_buyer <= x_total_num_buyers ) THEN
280
281 -- dbms_output.put_line('Buyer id is ' || to_char(asn_buyers(x_curr_buyer)) );
282 wf_directory.getusername('PER',
283 asn_buyers(x_curr_buyer),
284 x_buyer_user_name,
285 x_buyer_user_displayname);
286
287 wf_engine.SetItemAttrText
288 (
289 ItemType => l_item_type,
290 ItemKey => l_item_key,
291 aname => 'ASN_BUYER',
292 avalue => x_buyer_user_name
293 );
294
295 /* code added to convert expected_receipt_date timestamp value for asn notification */
296
297 -- fetch the user id to get the timezone conversion preferences
298
299 select Nvl(user_id, -1)
300 INTO l_user_id
301 FROM fnd_user
302 WHERE user_name = x_buyer_user_name;
303
304 IF l_user_id <> -1 THEN
305
306 -- verify if timezone conversion preference is enabled for the buyer user
307
308 SELECT Nvl(FND_PROFILE.value_specific('ENABLE_TIMEZONE_CONVERSIONS', l_user_id), 'N')
309 INTO l_timezone_conversion
310 FROM dual;
311
312 IF l_timezone_conversion = 'Y' THEN
313
314 -- get the server timezone and client time zone values for date time stamp conversion
315
316 l_server_tz := fnd_profile.value_specific('SERVER_TIMEZONE_ID');
317 l_client_tz := fnd_profile.value_specific('CLIENT_TIMEZONE_ID',l_user_id);
318
319 IF ( l_client_tz IS NOT NULL AND l_server_tz IS NOT NULL ) THEN
320
321 h_expected_receipt_date_ts := HZ_TIMEZONE_PUB.Convert_DateTime(l_server_tz, l_client_tz, h_expected_receipt_date_ts);
322 h_expected_receipt_date_print := to_char(h_expected_receipt_date_ts,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS');
323
324 wf_engine.SetItemAttrText(
325 ItemType => l_item_type,
326 ItemKey => l_item_key,
327 aname => 'EXPECTED_RECEIPT_TS',
328 avalue => h_expected_receipt_date_print
329 );
330
331 END IF;
332
333 END IF;
334
335 END if;
336
337 /* end of code added for conversion of expected_receipt_date time stamp */
338
339 wf_engine.SetItemAttrText (
340 itemtype => l_item_type,
341 itemkey => l_item_key,
342 aname => 'ASN_INFO',
343 avalue => 'PLSQLCLOB:POS_ASN_NOTIF.GENERATE_ASN_BODY/'
344 || x_shipment_num || '*%$*' || to_char(asn_buyers(x_curr_buyer))
345 ||'%'||to_char(x_vendor_id)||'#'||to_char(x_vendor_site_id)
346 );
347
348 -- dbms_output.put_line('Buyer Name is ' || x_buyer_user_name );
349 x_curr_buyer := x_curr_buyer + 1;
350
351 wf_engine.SetItemAttrNumber
352 (
353 ItemType => l_item_type,
354 ItemKey => l_item_key,
355 aname => 'CURR_BUYER_NUM',
356 avalue => x_curr_buyer
357 );
358
359
360 result := 'COMPLETE:Y';
361 ELSE
362
363 result := 'COMPLETE:N';
364 END IF;
365
366 END SET_NEXT_BUYER;
367
368
369 -----------------------------------------------------------------------
370 -- Procedure to retrieve Buyers for each ASN
371 -- and generates the headers
372
373 PROCEDURE GET_ASN_BUYERS(
374 l_item_type IN VARCHAR2,
375 l_item_key IN VARCHAR2,
376 actid IN NUMBER,
377 funcmode IN VARCHAR2,
378 result OUT NOCOPY VARCHAR2
379 )
380 IS
381
382 l_buyer_id number;
383 x_buyer_id number;
384 x_shipment_num VARCHAR2(80);
385 x_asn_type VARCHAR2(20);
386 x_vendor_name VARCHAR2(240);
387 x_shipped_date DATE;
388 x_shipped_date_ts varchar2(30);
389 x_expected_receipt_date date;
390 x_expected_receipt_ts varchar2(30);
391 x_invoice_num VARCHAR2(50);
392 x_total_invoice_amount NUMBER;
393 x_invoice_date date;
394 x_invoice_ts varchar2(150);
395 x_tax_amount NUMBER;
396 l_document1 VARCHAR2(32000) := '';
397 NL VARCHAR2(1) := fnd_global.newline;
398 x_display_type VARCHAR2(60);
399 x_buyer_user_name VARCHAR2(320);
400 x_buyer_user_displayname VARCHAR2(360);
401 l_nid NUMBER;
402 i INTEGER;
403 x_vendor_id NUMBER;
404 x_vendor_site_id NUMBER;
405
406 -- changed cursor re bug 2876139
407 -- changed cursor re bug 9907309 - not fetching end dated buyer users.
408 CURSOR asn_buyer(v_shipment_num varchar2,v_vendor_id number,v_vendor_site_id number) is
409 SELECT NVL(POR.AGENT_ID,POH.AGENT_ID)
410 FROM RCV_TRANSACTIONS_INTERFACE RTI,
411 RCV_HEADERS_INTERFACE RHI,
412 PO_HEADERS_ALL POH,
413 PO_RELEASES_ALL POR,
414 WF_USERS WUSR
415 WHERE POH.PO_HEADER_ID = RTI.PO_HEADER_ID AND
416 RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
417 POH.PO_HEADER_ID = POR.PO_HEADER_ID (+) AND
418 RHI.SHIPMENT_NUM = v_shipment_num AND
419 POH.VENDOR_ID= v_vendor_id AND
420 POH.VENDOR_SITE_ID=v_vendor_site_id AND
421 Nvl(POR.AGENT_ID, POH.AGENT_ID) = WUSR.orig_system_id AND
422 WUSR.orig_system = 'PER'
423 /** Added for BUG:11869868**/
424 AND (POR.PO_RELEASE_ID=RTI.PO_RELEASE_ID
425 OR POR.PO_RELEASE_ID IS NULL)
426 /***/
427 UNION
428 SELECT NVL(POR.AGENT_ID,POH.AGENT_ID)
429 FROM RCV_SHIPMENT_LINES RSL,
430 RCV_SHIPMENT_HEADERS RSH,
431 PO_HEADERS_ALL POH,
432 PO_RELEASES_ALL POR,
433 WF_USERS WUSR
434 WHERE
435 POH.PO_HEADER_ID = RSL.PO_HEADER_ID AND
436 RSL.SHIPMENT_HEADER_ID= RSH.SHIPMENT_HEADER_ID AND
437 POH.PO_HEADER_ID = POR.PO_HEADER_ID (+) AND
438 RSH.SHIPMENT_NUM=v_shipment_num AND
439 POH.VENDOR_ID= v_vendor_id AND
440 POH.VENDOR_SITE_ID=v_vendor_site_id AND
441 Nvl(POR.AGENT_ID, POH.AGENT_ID) = WUSR.orig_system_id AND
442 WUSR.orig_system = 'PER'
443 /** Added for BUG:11869868**/
444 AND (POR.PO_RELEASE_ID=RSL.PO_RELEASE_ID OR
445 POR.PO_RELEASE_ID IS NULL)
446 /******/;
447
448 BEGIN
449
450 asn_buyers := asn_buyers_empty;
451 --asn_buyer_num := 0;
452
453 x_shipment_num := wf_engine.GetItemAttrText ( itemtype => l_item_type,
454 itemkey => l_item_key,
455 aname => 'SHIPMENT_NUM');
456
457 x_vendor_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
458 itemkey => l_item_key,
459 aname => 'VENDOR_ID');
460
461 x_vendor_site_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
462 itemkey => l_item_key,
463 aname => 'VENDOR_SITE_ID');
464
465 --dbms_output.put_line('Shipment Num is ' || x_shipment_num);
466 i:= 1;
467 open asn_buyer(x_shipment_num,x_vendor_id,x_vendor_site_id);
468 --dbms_output.put_line('Before Open Buyer Cursor ');
469 -- Populate the global pl/sql table with buyer id's
470 LOOP
471 FETCH asn_buyer INTO x_buyer_id;
472 EXIT WHEN asn_buyer%NOTFOUND;
473 asn_buyers(i) := x_buyer_id;
474 --dbms_output.put_line('Buyer Id is ' || to_char(x_buyer_id));
475 i := i+1;
476
477 END LOOP;
478
479 CLOSE asn_buyer;
480 --dbms_output.put_line('First Buyer Id is ' || to_char(asn_buyers(1)));
481
482 BEGIN
483 /*
484 * Modified as part of bug 7524698 changing date format
485 * if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
486 * or (FND_RELEASE.MAJOR_VERSION > 12) then
487 */
488 /*
489 * Commented above if condition and added below if condition as part of Bug #: 11824503
490 */
491 IF ( fnd_release.major_version = 12
492 AND fnd_release.minor_version = 1
493 AND fnd_release.point_version >= 1 )
494 OR ( fnd_release.major_version = 12
495 AND fnd_release.minor_version >= 2 )
496 OR ( fnd_release.major_version > 12 ) THEN
497
498 SELECT DISTINCT poh.shipment_num,
499 pov.vendor_name,
500 poh.shipped_date,
501 To_char(poh.shipped_date,fnd_profile.Value_specific('ICX_DATE_FORMAT_MASK',fnd_global.user_id),
502 'NLS_CALENDAR = '''
503 ||Nvl(fnd_profile.Value_specific('FND_FORMS_USER_CALENDAR',fnd_global.user_id),
504 'GREGORIAN')
505 ||''''),
506 poh.expected_receipt_date,
507 To_char(poh.expected_receipt_date,fnd_profile.Value_specific('ICX_DATE_FORMAT_MASK',fnd_global.user_id),
508 'NLS_CALENDAR = '''
509 ||Nvl(fnd_profile.Value_specific('FND_FORMS_USER_CALENDAR',fnd_global.user_id),
510 'GREGORIAN')
511 ||''''),
512 poh.invoice_num,
513 poh.total_invoice_amount,
514 To_char(poh.invoice_date,fnd_profile.Value_specific('ICX_DATE_FORMAT_MASK',fnd_global.user_id),
515 'NLS_CALENDAR = '''
516 ||Nvl(fnd_profile.Value_specific('FND_FORMS_USER_CALENDAR',fnd_global.user_id),
517 'GREGORIAN')
518 ||''''),
519 poh.tax_amount,
520 poh.asn_type
521 INTO x_shipment_num,x_vendor_name,x_shipped_date,x_shipped_date_ts,
522 x_expected_receipt_date,x_expected_receipt_ts,x_invoice_num,x_total_invoice_amount,
523 x_invoice_ts,x_tax_amount,x_asn_type
524 FROM pos_headers_v poh, po_vendors pov
525 WHERE poh.shipment_num = x_shipment_num AND
526 poh.vendor_id = pov.vendor_id AND
527 poh.vendor_id = x_vendor_id AND
528 poh.vendor_site_id = x_vendor_site_id ;
529
530 else
531 SELECT distinct poh.shipment_num,pov.vendor_name,
532 poh.shipped_date,
533 to_char(poh.shipped_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
534 poh.expected_receipt_date,
535 to_char(poh.expected_receipt_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
536 poh.invoice_num,poh.total_invoice_amount,
537 poh.invoice_date,
538 poh.tax_amount,poh.asn_type
539 INTO x_shipment_num,x_vendor_name,
540 x_shipped_date,
541 x_shipped_date_ts,
542 x_expected_receipt_date,
543 x_expected_receipt_ts,
544 x_invoice_num,x_total_invoice_amount,
545 x_invoice_date,x_tax_amount,x_asn_type
546 FROM POS_HEADERS_V poh,PO_VENDORS pov
547 WHERE poh.shipment_num = x_shipment_num AND
548 poh.vendor_id = pov.vendor_id AND
549 poh.vendor_id = x_vendor_id AND
550 poh.vendor_site_id = x_vendor_site_id ;
551 end if;
552 /*Modified as part of bug 7524698 changing date format*/
553 EXCEPTION
554 WHEN NO_DATA_FOUND then
555 l_document1:= 'ASN has been cancelled';
556 -- fnd_message.get_string('POS','POS_ASN_CANCELLED');
557 wf_engine.SetItemAttrText
558 (
559 ItemType => l_item_type,
560 ItemKey => l_item_key,
561 aname => 'ASN_HEADERS',
562 avalue => l_document1
563 );
564
565 wf_directory.getusername('PER',
566 asn_buyers(1),
567 x_buyer_user_name,
568 x_buyer_user_displayname);
569
570 wf_engine.SetItemAttrText
571 (
572 ItemType => l_item_type,
573 ItemKey => l_item_key,
574 aname => 'ASN_BUYER',
575 avalue => x_buyer_user_name
576 );
577 return;
578 WHEN OTHERS then
579 RAISE;
580 END;
581
582 --dbms_output.put_line('Asn Type is ' || x_asn_type);
583 --dbms_output.put_line('Vendor Name is ' || x_vendor_name);
584 wf_engine.SetItemAttrText
585 (
586 ItemType => l_item_type,
587 ItemKey => l_item_key,
588 aname => 'SUPPLIER',
589 avalue => x_vendor_name
590 );
591
592 wf_engine.SetItemAttrText
593 (
594 ItemType => l_item_type,
595 ItemKey => l_item_key,
596 aname => 'EXPECTED_RECEIPT_TS',
597 avalue => x_expected_receipt_ts
598 );
599
600 wf_engine.SetItemAttrDate
601 (
602 ItemType => l_item_type,
603 ItemKey => l_item_key,
604 aname => 'EXPECTED_RECEIPT_DATE',
605 avalue => x_expected_receipt_date
606 );
607
608 wf_engine.SetItemAttrText
609 (
610 ItemType => l_item_type,
611 ItemKey => l_item_key,
612 aname => 'ASN_TYPE',
613 avalue => x_asn_type
614 );
615
616 --x_display_type := 'text/html';
617
618 l_document1 := '<font size=3 color=#336699 face=arial><b>' ||fnd_message.get_string('POS', 'POS_ASN_NOTIF_DETAILS') ||
619 '</B></font><HR size=1 color=#336699>' ;
620
621 l_document1 := l_document1 || '<TABLE cellpadding=2 cellspacing=1>';
622
623 l_document1 := l_document1 || '<TR>' ;
624
625 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
626 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SUPP_NAME') || '</B></font></TD> ' ;
627 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
628 x_vendor_name || '</font></TD> ' ;
629 l_document1 := l_document1 || '</TR>' ;
630
631 l_document1 := l_document1 || '<TR>' ;
632 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
633 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIPMENT_NUM') || '</B></font></TD> ' ;
634 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
635 x_shipment_num || '</font></TD> ' ;
636 l_document1 := l_document1 || '</TR>' ;
637
638 l_document1 := l_document1 || '<TR>' ;
639 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
640 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIPMENT_DATE') || '</B></font></TD> ' ;
641 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
642 x_shipped_date_ts || '</font></TD> ' ;
643 l_document1 := l_document1 || '</TR>' ;
644
645 l_document1 := l_document1 || '<TR>' ;
646 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
647 fnd_message.get_string('POS', 'POS_ASN_NOTIF_EXPT_RCPT_DATE') || '</B></font></TD> ';
648 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
649 x_expected_receipt_ts || '</font></TD> ' ;
650 l_document1 := l_document1 || '</TR>' ;
651
652 l_document1 := l_document1 || '</TABLE></P>' ;
653
654
655 IF (x_asn_type = 'ASBN') THEN
656
657 wf_engine.SetItemAttrText
658 (
659 ItemType => l_item_type,
660 ItemKey => l_item_key,
661 aname => 'INVOICE_INFO',
662 avalue => 'and Invoice'
663 );
664
665 wf_engine.SetItemAttrText
666 (
667 ItemType => l_item_type,
668 ItemKey => l_item_key,
669 aname => 'INVOICE_NUM',
670 avalue => x_invoice_num
671 );
672
673
674 l_document1 := l_document1 || '<font size=3 color=#336699 face=arial><b>'||
675 fnd_message.get_string('POS', 'POS_ASN_NOTIF_BILL_INFO') ||'</B></font><HR size=1 color=#336699>' ;
676
677 l_document1 := l_document1 || '<TABLE cellpadding=2 cellspacing=1>';
678
679 l_document1 := l_document1 || '<TR>' ;
680 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
681 fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_NUMBER') || '</B></font></TD> ' ;
682 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
683 x_invoice_num || '</font></TD> ' ;
684 l_document1 := l_document1 || '</TR>' ;
685
686 l_document1 := l_document1 || '<TR>' ;
687 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
688 fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_AMOUNT') || '</B></font></TD> ' ;
689 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
690 x_total_invoice_amount || '</font></TD> ' ;
691 l_document1 := l_document1 || '</TR>' ;
692
693 l_document1 := l_document1 || '<TR>' ;
694 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
695 fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_DATE') || '</B></font></TD> ' ;
696 /*
697 * Modified as part of bug 7524698 changing date format
698 * if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
699 * or (FND_RELEASE.MAJOR_VERSION > 12) then
700 */
701 /*
702 * Commented above if condition and added below if condition as part of Bug #: 11824503
703 */
704 IF ( fnd_release.major_version = 12
705 AND fnd_release.minor_version = 1
706 AND fnd_release.point_version >= 1 )
707 OR ( fnd_release.major_version = 12
708 AND fnd_release.minor_version >= 2 )
709 OR ( fnd_release.major_version > 12 ) THEN
710 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
711 x_invoice_ts || '</font></TD> ' ;
712 else
713 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
714 x_invoice_date || '</font></TD> ' ;
715 end if;
716 /*Modified as part of bug 7524698 changing date format*/
717 l_document1 := l_document1 || '</TR>' ;
718
719 l_document1 := l_document1 || '<TR>' ;
720 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
721 fnd_message.get_string('POS', 'POS_ASN_NOTIF_TAX_AMOUNT') || '</B></font></TD> ' ;
722 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
723 x_tax_amount || '</font></TD> ' ;
724 l_document1 := l_document1 || '</TR>' ;
725
726 l_document1 := l_document1 || '</TABLE></P>' ;
727
728
729 ELSE
730 wf_engine.SetItemAttrText
731 (
732 ItemType => l_item_type,
733 ItemKey => l_item_key,
734 aname => 'INVOICE_INFO',
735 avalue => ''
736 );
737
738 wf_engine.SetItemAttrText
739 (
740 ItemType => l_item_type,
741 ItemKey => l_item_key,
742 aname => 'INVOICE_NUM',
743 avalue => ''
744 );
745
746 END IF;
747
748 -- This Attribute is not being set to l_document any more , moved to the body section as pl/sql clob
749
750 wf_engine.SetItemAttrText
751 (
752 ItemType => l_item_type,
753 ItemKey => l_item_key,
754 aname => 'ASN_HEADERS',
755 avalue => ''
756 );
757
758 -- Set the Buyer Count and Current Number in the Workflow
759 wf_engine.SetItemAttrNumber
760 (
761 ItemType => l_item_type,
762 ItemKey => l_item_key,
763 aname => 'TOTAL_BUYER_NUM',
764 avalue => asn_buyers.COUNT
765 );
766 wf_engine.SetItemAttrNumber
767 (
768 ItemType => l_item_type,
769 ItemKey => l_item_key,
770 aname => 'CURR_BUYER_NUM',
771 avalue => 1
772 );
773
774 END GET_ASN_BUYERS;
775
776
777
778 PROCEDURE GENERATE_ASN_BODY(p_ship_num_buyer_id IN VARCHAR2,
779 display_type in Varchar2,
780 document in OUT NOCOPY clob,
781 document_type in OUT NOCOPY varchar2)
782 IS
783
784 TYPE asn_lines_record is record (
785 po_num po_headers_all.CLM_DOCUMENT_NUMBER%TYPE,
786 po_rev_no po_headers_all.revision_num%TYPE,
787 line_num po_lines_all.line_num%TYPE,
788 ship_num po_line_locations_all.shipment_num%TYPE,
789 item_num varchar2(80),
790 item_desc po_lines_all.item_description%TYPE,
791 uom po_lines_all.unit_meas_lookup_code%TYPE,
792 order_qty po_line_locations_all.quantity%TYPE,
793 ship_qty rcv_transactions_interface.quantity%TYPE,
794 --rcvd_qty po_line_locations_all.quantity_received%type,
795 rcvd_qty NUMBER,
796 ship_to rcv_transactions_interface.ship_to_location_code%type,
797 ship_to_org org_organization_definitions.ORGANIZATION_CODE%type
798 );
799
800 NL VARCHAR2(1) := fnd_global.newline;
801 l_document VARCHAR2(32000) := '';
802 l_asn_lines asn_lines_record;
803 x_shipment_num pos_lines_v.shipment_num%TYPE;
804 x_buyer_id NUMBER;
805 x_vendor_id NUMBER;
806 x_vendor_site_id NUMBER;
807 x_num_lines NUMBER;
808 x_bvs_id VARCHAR2(50);
809 x_vs_id VARCHAR2(50);
810
811 h_shipment_num pos_headers_v.shipment_num%TYPE;
812 h_asn_type VARCHAR2(20);
813 h_vendor_name VARCHAR2(240);
814 h_shipped_date varchar2(2000);
815 h_expected_receipt_date varchar2(2000);
816 h_invoice_num VARCHAR2(50);
817 h_total_invoice_amount NUMBER;
818 h_invoice_date DATE;
819 h_tax_amount NUMBER;
820
821 h_shipped_date_ts TIMESTAMP;
822 h_expected_receipt_date_ts TIMESTAMP;
823 l_user_id NUMBER;
824 l_timezone_conversion VARCHAR2(1);
825 l_server_tz NUMBER;
826 l_client_tz NUMBER;
827
828 x_buyer_user_name VARCHAR2(320);
829 x_buyer_user_displayname VARCHAR2(360);
830
831 l_remit_to_site_id NUMBER;
832 l_remit_to_site_code PO_VENDOR_SITES_ALL.vendor_site_code%TYPE;
833 l_remit_to_address1 PO_VENDOR_SITES_ALL.address_line1%TYPE;
834 l_remit_to_address2 PO_VENDOR_SITES_ALL.address_line2%TYPE;
835 l_remit_to_address3 PO_VENDOR_SITES_ALL.address_line3%TYPE;
836 l_remit_to_address4 PO_VENDOR_SITES_ALL.address_line4%TYPE;
837 l_remit_to_czinfo VARCHAR2(200);
838 l_remit_to_country PO_VENDOR_SITES_ALL.country%TYPE;
839
840 l_remit_exist_flag VARCHAR2(1) := 'T';
841
842
843 CURSOR asn_lines(p_shipment_num varchar2,v_buyer_id number,p_vendor_id number,p_vendor_site_id number) IS
844 SELECT
845 DECODE(PRL.PO_RELEASE_ID,NULL,PH.clm_document_number,PH.clm_document_number || '-' || TO_CHAR(PRL.RELEASE_NUM)) PO_NUM,
846 ph.revision_num REVISION_NUM,
847 pola.line_num LINE_NUM,
848 pll.shipment_num SHIPMENT_NUM,
849 pos_get.get_item_number(rti.item_id,ood.organization_id) ITEM_NUM,
850 pola.item_description ITEM_DESC,
851 pola.unit_meas_lookup_code UOM,
852 pll.quantity QUANTITY_ORDERED,
853 rti.quantity QUANTITY_SHIPPED,
854 pll.quantity_received QUANTITY_RECEIVED,
855 NVL( HRL.LOCATION_CODE,
856 SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) ship_to_location_code,
857 ood.ORGANIZATION_CODE ORGANIZATION_CODE
858 FROM rcv_transactions_interface rti, rcv_headers_interface rhi ,
859 org_organization_definitions ood,po_releases_all prl,
860 po_line_locations_all pll,po_lines_all pola,po_headers_all ph,
861 hr_locations_all_tl hrl, hz_locations hz
862 WHERE rhi.header_interface_id=rti.header_interface_id and
863 rhi.shipment_num= p_shipment_num and
864 pola.po_line_id = rti.po_line_id and
865 nvl(prl.agent_id,ph.agent_id)=v_buyer_id and
866 pll.po_release_id = prl.po_release_id(+) and
867 pll.line_location_id=rti.po_line_location_id and
868 ood.organization_id = pll.ship_to_organization_id and
869 ph.po_header_id = rti.po_header_id and
870 rti.vendor_id = p_vendor_id and
871 rti.vendor_site_id = p_vendor_site_id and
872 HRL.LOCATION_ID (+) = rti.SHIP_TO_LOCATION_ID AND
873 HRL.LANGUAGE(+) = USERENV('LANG') AND
874 HZ.LOCATION_ID(+) = rti.SHIP_TO_LOCATION_ID
875 UNION ALL
876 SELECT
877 DECODE(PRL.PO_RELEASE_ID,NULL,PH.clm_document_number,PH.clm_document_number || '-' || TO_CHAR(PRL.RELEASE_NUM)) PO_NUM,
878 ph.revision_num REVISION_NUM,
879 pola.line_num LINE_NUM,
880 pll.shipment_num SHIPMENT_NUM,
881 pos_get.get_item_number(rsl.item_id,ood.organization_id) ITEM_NUM,
882 pola.item_description ITEM_DESC,
883 pola.unit_meas_lookup_code UOM,
884 pll.quantity QUANTITY_ORDERED,
885 rsl.quantity_shipped QUANTITY_SHIPPED,
886 pll.quantity_received QUANTITY_RECEIVED,
887 NVL( HRL.LOCATION_CODE,
888 SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) ship_to_location_code,
889 ood.ORGANIZATION_CODE ORGANIZATION_CODE
890 FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh ,
891 org_organization_definitions ood,po_releases_all prl,
892 po_line_locations_all pll,po_lines_all pola,po_headers_all ph,
893 hr_locations_all_tl hrl,hz_locations hz
894 WHERE rsh.shipment_header_id=rsl.shipment_header_id and
895 rsh.shipment_num= p_shipment_num and
896 pola.po_line_id = rsl.po_line_id and
897 nvl(prl.agent_id,ph.agent_id)=v_buyer_id and
898 pll.po_release_id = prl.po_release_id(+) and
899 pll.line_location_id=rsl.po_line_location_id and
900 ood.organization_id = pll.ship_to_organization_id and
901 ph.po_header_id = rsl.po_header_id and
902 HRL.LOCATION_ID (+) = rsl.SHIP_TO_LOCATION_ID AND
903 HRL.LANGUAGE(+) = USERENV('LANG') AND
904 HZ.LOCATION_ID(+) = rsl.SHIP_TO_LOCATION_ID and
905 rsh.vendor_id = p_vendor_id and
906 rsh.vendor_site_id=p_vendor_site_id;
907
908
909 BEGIN
910
911 x_shipment_num := substr(p_ship_num_buyer_id,1,instr(p_ship_num_buyer_id,'*%$*')-1);
912 x_bvs_id := substr(p_ship_num_buyer_id,instr(p_ship_num_buyer_id,'*%$*')+ 4,length(p_ship_num_buyer_id)-2);
913 x_buyer_id := substr(x_bvs_id,1,instr(x_bvs_id, '%')- 1);
914 x_vs_id := substr(x_bvs_id,instr(x_bvs_id,'%')+1,length(x_bvs_id)-2);
915 x_vendor_id := substr(x_vs_id,1,instr(x_vs_id,'#')-1);
916 x_vendor_site_id := substr(x_vs_id,instr(x_vs_id,'#')+ 1,length(x_vs_id)-2);
917
918 --Generate the Header
919
920 BEGIN
921
922 SELECT distinct poh.shipment_num,pov.vendor_name,
923 --to_char(poh.shipped_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
924 --to_char(poh.expected_receipt_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
925 poh.shipped_date,
926 poh.expected_receipt_date,
927 poh.invoice_num,poh.total_invoice_amount,
928 poh.invoice_date,
929 poh.tax_amount,poh.asn_type,
930 poh.remit_to_site_id
931 INTO h_shipment_num,h_vendor_name,
932 --h_shipped_date,
933 --h_expected_receipt_date,
934 h_shipped_date_ts,
935 h_expected_receipt_date_ts,
936 h_invoice_num,h_total_invoice_amount,
937 h_invoice_date,
938 h_tax_amount,h_asn_type,
939 l_remit_to_site_id
940 FROM POS_HEADERS_V poh,PO_VENDORS pov
941 WHERE poh.shipment_num = x_shipment_num AND
942 poh.vendor_id = pov.vendor_id AND
943 poh.vendor_id = to_number(x_vendor_id) AND
944 poh.vendor_site_id = to_number(x_vendor_site_id);
945 EXCEPTION
946 WHEN NO_DATA_FOUND then
947 l_document := 'NO_DATA';
948 WHEN OTHERS then
949 RAISE;
950 END;
951
952 if (l_document = 'NO_DATA') then
953 -- if you didnt find any data in the headers do not draw the header section at all
954 l_document := '';
955 else
956
957
958 if (l_remit_to_site_id is not null) then
959 BEGIN
960
961 SELECT pvs.VENDOR_SITE_CODE,
962 pvs.address_line1,
963 pvs.address_line2,
964 pvs.address_line3,
965 pvs.address_line4,
966 pvs.city || ', ' || pvs.state || ' ' || pvs.zip,
967 pvs.country
968 INTO l_remit_to_site_code,
969 l_remit_to_address1,
970 l_remit_to_address2,
971 l_remit_to_address3,
972 l_remit_to_address4,
973 l_remit_to_czinfo,
974 l_remit_to_country
975 FROM PO_VENDOR_SITES_ALL pvs
976 WHERE pvs.vendor_site_id = l_remit_to_site_id;
977
978 EXCEPTION
979 WHEN NO_DATA_FOUND THEN
980 l_remit_exist_flag := 'F';
981
982 WHEN OTHERS then
983 RAISE;
984 END;
985
986 end if;
987
988 /* code changes for bug 10408761
989 fetch the timezone converted values for the shipping date and expected receipt date
990 based on the user preferences (buyer user to whom notification is being sent) */
991
992 -- fetch the user id to get the timezone conversion preferences
993
994 wf_directory.getusername('PER',
995 x_buyer_id,
996 x_buyer_user_name,
997 x_buyer_user_displayname);
998
999 select Nvl(user_id, -1)
1000 INTO l_user_id
1001 FROM fnd_user
1002 WHERE user_name = x_buyer_user_name;
1003
1004 IF l_user_id <> -1 THEN
1005
1006 -- verify if timezone conversion preference is enabled for the buyer user
1007
1008 SELECT Nvl(FND_PROFILE.value_specific('ENABLE_TIMEZONE_CONVERSIONS', l_user_id), 'N')
1009 INTO l_timezone_conversion
1010 FROM dual;
1011
1012 IF l_timezone_conversion = 'Y' THEN
1013
1014 -- get the server timezone and client time zone values for date time stamp conversion
1015
1016 l_server_tz := fnd_profile.value_specific('SERVER_TIMEZONE_ID');
1017 l_client_tz := fnd_profile.value_specific('CLIENT_TIMEZONE_ID',l_user_id);
1018
1019 IF ( l_client_tz IS NOT NULL AND l_server_tz IS NOT NULL ) THEN
1020
1021 h_shipped_date_ts := HZ_TIMEZONE_PUB.Convert_DateTime(l_server_tz, l_client_tz, h_shipped_date_ts);
1022 h_expected_receipt_date_ts := HZ_TIMEZONE_PUB.Convert_DateTime(l_server_tz, l_client_tz, h_expected_receipt_date_ts);
1023
1024 END IF;
1025
1026 END IF;
1027
1028 END if;
1029
1030 h_shipped_date := to_char(h_shipped_date_ts,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS');
1031 h_expected_receipt_date := to_char(h_expected_receipt_date_ts,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS');
1032
1033 /* end of code changes for bug 10408761 */
1034
1035 l_document := l_document || NL || NL || '<font size=3 color=#336699 face=arial><b>' ||fnd_message.get_string('POS', 'POS_ASN_NOTIF_DETAILS') || '</B></font><HR size=1 color=#336699>' ;
1036
1037 l_document := l_document || '<TABLE cellpadding=2 cellspacing=1>';
1038
1039 l_document := l_document || '<TR>' ;
1040 l_document := l_document || '<TD nowrap>' ||
1041 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SUPP_NAME') || '</TD> ' ;
1042 l_document := l_document || '<TD nowrap><B>' || h_vendor_name || '</B></TD> ' ;
1043 l_document := l_document || '</TR>' ;
1044
1045 l_document := l_document || '<TR>' ;
1046 l_document := l_document || '<TD nowrap>' ||
1047 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIPMENT_NUM') || '</TD> ' ;
1048 l_document := l_document || '<TD nowrap><B>' || h_shipment_num || '</B></TD> ' ;
1049 l_document := l_document || '</TR>' ;
1050
1051 l_document := l_document || '<TR>' ;
1052 l_document := l_document || '<TD nowrap>' ||
1053 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIPMENT_DATE') || '</TD> ' ;
1054 l_document := l_document || '<TD nowrap><B>' || h_shipped_date || '</B></TD> ' ;
1055 l_document := l_document || '</TR>' ;
1056
1057 l_document := l_document || '<TR>' ;
1058 l_document := l_document || '<TD nowrap>' ||
1059 fnd_message.get_string('POS', 'POS_ASN_NOTIF_EXPT_RCPT_DATE') || '</TD> ';
1060 l_document := l_document || '<TD nowrap><B>' || h_expected_receipt_date || '</B></TD> ' ;
1061 l_document := l_document || '</TR>' ;
1062
1063 l_document := l_document || '</TABLE></P>' ;
1064
1065
1066 IF (h_asn_type = 'ASBN') THEN
1067
1068 l_document := l_document || '<font size=3 color=#336699 face=arial><b>'||
1069 fnd_message.get_string('POS', 'POS_ASN_NOTIF_BILL_INFO') ||'</B></font><HR size=1 color=#336699>' ;
1070
1071 l_document := l_document || '<TABLE cellpadding=2 cellspacing=1>' ;
1072
1073 l_document := l_document || '<TR>' ;
1074 l_document := l_document || '<TD nowrap>' ||
1075 fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_NUMBER') || '</TD> ' ;
1076 l_document := l_document || '<TD nowrap><B>' ||
1077 h_invoice_num || '</B></TD> ' ;
1078 l_document := l_document || '</TR>' ;
1079
1080 l_document := l_document || '<TR>' ;
1081 l_document := l_document || '<TD nowrap>' ||
1082 fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_AMOUNT') || '</TD> ' ;
1083 l_document := l_document || '<TD nowrap><B>' ||
1084 h_total_invoice_amount || '</B></TD> ' ;
1085 l_document := l_document || '</TR>' ;
1086
1087 l_document := l_document || '<TR>' ;
1088 l_document := l_document || '<TD nowrap>' ||
1089 fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_DATE') || '</TD> ' ;
1090 l_document := l_document || '<TD nowrap><B>' || h_invoice_date || '</B></TD></TR>' ;
1091
1092 l_document := l_document || '<TR>' ;
1093 l_document := l_document || '<TD nowrap>' ||
1094 fnd_message.get_string('POS', 'POS_ASN_NOTIF_TAX_AMOUNT') || '</TD> ' ;
1095 l_document := l_document || '<TD nowrap><B>' || h_tax_amount || '</B></TD> ' ;
1096 l_document := l_document || '</TR>' ;
1097
1098 --mji Remit-to Info
1099 IF (l_remit_exist_flag = 'T') THEN
1100
1101 l_document := l_document || '<TR>' ;
1102 l_document := l_document || '<TD nowrap>' ||
1103 fnd_message.get_string('POS', 'POS_ASN_NOTIF_REMIT_NAME') || '</TD> ' ;
1104 l_document := l_document || '<TD nowrap><B>' || l_remit_to_site_code || '</B></TD></TR>' ;
1105
1106
1107 l_document := l_document || '<TR>' ;
1108 l_document := l_document || '<TD nowrap>' ||
1109 fnd_message.get_string('POS', 'POS_ASN_NOTIF_REMIT_ADDR') || '</TD> ' ;
1110 l_document := l_document || '<TD nowrap><B>' || l_remit_to_address1 || '</B></TD></TR>' ;
1111
1112
1113 if (l_remit_to_address2 is not null) then
1114 l_document := l_document || '<TR>' ;
1115 l_document := l_document || '<TD> </TD> ' ;
1116 l_document := l_document || '<TD nowrap><B>' || l_remit_to_address2 || '</B></TD> ' ;
1117 l_document := l_document || '</TR>' ;
1118 end if;
1119
1120
1121 if (l_remit_to_address3 is not null) then
1122 l_document := l_document || '<TR>' ;
1123 l_document := l_document || '<TD> </TD> ' ;
1124 l_document := l_document || '<TD nowrap><B>' || l_remit_to_address3 || '</B></TD> ' ;
1125 l_document := l_document || '</TR>' ;
1126 end if;
1127
1128
1129 if (l_remit_to_address4 is not null) then
1130 l_document := l_document || '<TR>' ;
1131 l_document := l_document || '<TD> </TD> ' ;
1132 l_document := l_document || '<TD nowrap><B>' || l_remit_to_address4 || '</B></TD> ' ;
1133 l_document := l_document || '</TR>' ;
1134 end if;
1135
1136
1137 l_document := l_document || '<TR>' ;
1138 l_document := l_document || '<TD> </TD> ' ;
1139 l_document := l_document || '<TD nowrap><B>' || l_remit_to_czinfo || '</B></TD> ' ;
1140 l_document := l_document || '</TR>' ;
1141
1142
1143 l_document := l_document || '<TR>' ;
1144 l_document := l_document || '<TD> </TD> ' ;
1145 l_document := l_document || '<TD nowrap><B>' || l_remit_to_country || '</B></TD> ' ;
1146 l_document := l_document || '</TR>' ;
1147
1148 END IF;
1149
1150 l_document := l_document || '</TABLE></P>' ;
1151
1152 END IF;
1153 end if ; -- end of if no data
1154 -- End of Header Info
1155
1156
1157 -- check if notification was cancelled then do not generate the table
1158 select count(*) into x_num_lines from pos_headers_v
1159 where shipment_num=x_shipment_num and
1160 vendor_id = x_vendor_id and
1161 vendor_site_id = x_vendor_site_id;
1162
1163 if (x_num_lines < 1) then
1164 l_document := '';
1165 l_document := fnd_message.get_string('POS', 'POS_ASN_NOTIF_CANCELLED');
1166
1167 WF_NOTIFICATION.WriteToClob(document, l_document);
1168
1169 else
1170 OPEN asn_lines(x_shipment_num,x_buyer_id,x_vendor_id,x_vendor_site_id);
1171
1172
1173 --Generate HTML TABLE HEADER
1174 l_document := l_document || NL || NL ||'<font size=3 color=#336699 face=arial><b>'||
1175 fnd_message.get_string('POS', 'POS_ASN_NOTIF_ASN_DTLS') ||'</B></font><HR size=1 color=#336699>'|| NL ;
1176
1177 l_document := l_document || '<TABLE WIDTH=100% cellpadding=2 cellspacing=1>';
1178 l_document := l_document || '<TR bgcolor=#CFE0F1>' || NL;
1179
1180 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1181 fnd_message.get_string('POS', 'POS_ASN_NOTIF_ORDER_NUMBER') || '</font></TH>' || NL;
1182
1183 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1184 fnd_message.get_string('POS', 'POS_ASN_NOTIF_REVISION_NUMBER') || '</font></TH>' || NL;
1185
1186 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1187 fnd_message.get_string('POS', 'POS_ASN_NOTIF_LINE_NUM') || '</font></TH>' || NL;
1188
1189 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1190 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIP_NUM') || '</font></TH>' || NL;
1191
1192 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1193 fnd_message.get_string('POS', 'POS_ASN_NOTIF_ITEM') || '</font></TH>' || NL;
1194
1195 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1196 fnd_message.get_string('POS', 'POS_ASN_NOTIF_ITEM_DESC') || '</font></TH>' || NL;
1197
1198 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1199 fnd_message.get_string('POS', 'POS_ASN_NOTIF_UOM') || '</font></TH>' || NL;
1200
1201 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1202 fnd_message.get_string('POS','POS_ASN_NOTIF_QUANTITY_ORD') || '</font></TH>' || NL;
1203
1204 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1205 fnd_message.get_string('POS','POS_ASN_NOTIF_QUANTITY_SHIP') || '</font></TH>' || NL;
1206
1207 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1208 fnd_message.get_string('POS','POS_ASN_NOTIF_QUANTITY_RCVD') || '</font></TH>' || NL;
1209
1210 l_document := l_document || '<TH align=left nowrap><font color=#3C3C3C >' ||
1211 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIP_TO') || '</font></TH>' || NL;
1212
1213 l_document := l_document || '<TH align=left><font color=#3C3C3C >' ||
1214 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIP_TO_ORG') || '</font></TH>' || NL;
1215
1216 l_document := l_document || '</TR>' || NL;
1217
1218 l_document := l_document || '</B>';
1219
1220 LOOP
1221
1222 FETCH asn_lines INTO l_asn_lines;
1223 EXIT WHEN asn_lines%NOTFOUND;
1224
1225 l_document := l_document || '<TR bgcolor=#F2F2F5>' || NL;
1226
1227 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1228 nvl(l_asn_lines.po_num, ' ') || '</font></TD> ' || NL;
1229
1230 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1231 nvl(to_char(l_asn_lines.po_rev_no), ' ') || '</font></TD> ' || NL;
1232
1233 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1234 nvl(to_char(l_asn_lines.line_num), ' ') || '</font></TD> ' || NL;
1235
1236 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1237 nvl(to_char(l_asn_lines.ship_num), ' ') || '</font></TD> ' || NL;
1238
1239 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1240 nvl(l_asn_lines.item_num, ' ') || '</font></TD> ' || NL;
1241
1242 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1243 nvl(l_asn_lines.item_desc, ' ') || '</font></TD> ' || NL;
1244
1245 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1246 nvl(l_asn_lines.uom, ' ') || '</font></TD> ' || NL;
1247
1248 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1249 nvl(to_char(l_asn_lines.order_qty), ' ') || '</font></TD> ' || NL;
1250
1251 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1252 nvl(to_char(l_asn_lines.ship_qty), ' ') || '</font></TD> ' || NL;
1253
1254 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1255 nvl(to_char(l_asn_lines.rcvd_qty), ' ') || '</font></TD> ' || NL;
1256
1257 l_document := l_document || '<TD nowrap><font color=#3C3C3C>' ||
1258 nvl(l_asn_lines.ship_to, ' ') || '</font></TD> ' || NL;
1259
1260 l_document := l_document || '<TD><font color=#3C3C3C>' ||
1261 nvl(l_asn_lines.ship_to_org, ' ') || '</font></TD> ' || NL;
1262
1263 l_document := l_document || '</TR>' || NL;
1264
1265 WF_NOTIFICATION.WriteToClob(document, l_document);
1266 l_document := null;
1267 END LOOP;
1268
1269 CLOSE asn_lines;
1270
1271 l_document := l_document || '</TABLE></P>' || NL;
1272
1273 WF_NOTIFICATION.WriteToClob(document, l_document);
1274 end if;
1275
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 RAISE;
1279 END GENERATE_ASN_BODY;
1280
1281 END POS_ASN_NOTIF;