eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.POS_ASN_NOTIF

Source


1 PACKAGE BODY POS_ASN_NOTIF AS
2 /* $Header: POSASNNB.pls 120.5 2006/08/14 17:47:57 jbalakri noship $ */
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 BEGIN
215    --dbms_output.put_line('Calling Set Next Buyer');
216 x_total_num_buyers := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
217                                                     itemkey  => l_item_key,
218                                                     aname    => 'TOTAL_BUYER_NUM');
219 
220 x_curr_buyer       := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
221                                                     itemkey  => l_item_key,
222                                                     aname    => 'CURR_BUYER_NUM');
223 
224 x_shipment_num     := wf_engine.GetItemAttrText (   itemtype => l_item_type,
225                                                     itemkey  => l_item_key,
226                                                     aname    => 'SHIPMENT_NUM');
227 
228 x_vendor_id        := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
229                                                     itemkey  => l_item_key,
230                                                     aname    => 'VENDOR_ID');
231 
232 x_vendor_site_id   := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
233                                                     itemkey  => l_item_key,
234                                                     aname    => 'VENDOR_SITE_ID');
235 
236 --dbms_output.put_line('Buyer Num is ' || to_char(x_curr_buyer));
237 
238 
239 IF ( x_curr_buyer <= x_total_num_buyers ) THEN
240 
241  --  dbms_output.put_line('Buyer id is ' ||  to_char(asn_buyers(x_curr_buyer)) );
242    wf_directory.getusername('PER',
243 			       asn_buyers(x_curr_buyer),
244 			       x_buyer_user_name,
245 			       x_buyer_user_displayname);
246 
247    wf_engine.SetItemAttrText
248                             (
249                             ItemType    => l_item_type,
250                             ItemKey     => l_item_key,
251                             aname       => 'ASN_BUYER',
252                             avalue      => x_buyer_user_name
253                             );
254 
255 
256    wf_engine.SetItemAttrText (
257 				itemtype       => l_item_type,
258                                 itemkey       => l_item_key,
259                                 aname         => 'ASN_INFO',
260                                 avalue        => 'PLSQLCLOB:POS_ASN_NOTIF.GENERATE_ASN_BODY/'
261 				|| x_shipment_num || '*%$*' || to_char(asn_buyers(x_curr_buyer))
262 				||'%'||to_char(x_vendor_id)||'#'||to_char(x_vendor_site_id)
263 				);
264 
265 --   dbms_output.put_line('Buyer Name is ' || x_buyer_user_name );
266    x_curr_buyer := x_curr_buyer + 1;
267 
268     wf_engine.SetItemAttrNumber
269                             (
270                             ItemType    => l_item_type,
271                             ItemKey     => l_item_key,
272                             aname       => 'CURR_BUYER_NUM',
273                             avalue      => x_curr_buyer
274                             );
275 
276 
277 	result := 'COMPLETE:Y';
278 ELSE
279 
280 	result := 'COMPLETE:N';
281 END IF;
282 
283 END SET_NEXT_BUYER;
284 
285 
286 -----------------------------------------------------------------------
287 -- Procedure to retrieve Buyers for each ASN
288 -- and generates the headers
289 
290 PROCEDURE GET_ASN_BUYERS(
291 			 l_item_type IN VARCHAR2,
292 			 l_item_key  IN VARCHAR2,
293 			 actid       IN NUMBER,
294                          funcmode    IN  VARCHAR2,
295                          result      OUT NOCOPY VARCHAR2
296 )
297 IS
298 
299 l_buyer_id      number;
300 x_buyer_id      number;
301 x_shipment_num  VARCHAR2(80);
302 x_asn_type      VARCHAR2(20);
303 x_vendor_name   VARCHAR2(240);
304 x_shipped_date  DATE;
305 x_shipped_date_ts  varchar2(30);
306 x_expected_receipt_date date;
307 x_expected_receipt_ts varchar2(30);
308 x_invoice_num   VARCHAR2(50);
309 x_total_invoice_amount  NUMBER;
310 x_invoice_date  date;
311 x_tax_amount    NUMBER;
312 l_document1     VARCHAR2(32000) := '';
313 NL              VARCHAR2(1) := fnd_global.newline;
314 x_display_type  VARCHAR2(60);
315 x_buyer_user_name VARCHAR2(320);
316 x_buyer_user_displayname VARCHAR2(360);
317 l_nid 		NUMBER;
318 i		INTEGER;
319 x_vendor_id       NUMBER;
320 x_vendor_site_id  NUMBER;
321 
322 -- changed cursor re bug 2876139
323 CURSOR asn_buyer(v_shipment_num varchar2,v_vendor_id number,v_vendor_site_id number) is
324 SELECT NVL(POR.AGENT_ID,POH.AGENT_ID)
325 FROM RCV_TRANSACTIONS_INTERFACE RTI,
326      RCV_HEADERS_INTERFACE RHI,
327      PO_HEADERS_ALL POH,
328      PO_RELEASES_ALL POR
329   WHERE POH.PO_HEADER_ID = RTI.PO_HEADER_ID AND
330 	RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
331 	POH.PO_HEADER_ID = POR.PO_HEADER_ID (+) AND
332 	RHI.SHIPMENT_NUM = v_shipment_num AND
333 	POH.VENDOR_ID= v_vendor_id AND
334 	POH.VENDOR_SITE_ID=v_vendor_site_id
335 UNION
336 SELECT NVL(POR.AGENT_ID,POH.AGENT_ID)
337 FROM    RCV_SHIPMENT_LINES RSL,
338 	RCV_SHIPMENT_HEADERS RSH,
339 	PO_HEADERS_ALL POH,
340 	PO_RELEASES_ALL POR
341 WHERE
342 	POH.PO_HEADER_ID = RSL.PO_HEADER_ID AND
343 	RSL.SHIPMENT_HEADER_ID= RSH.SHIPMENT_HEADER_ID AND
344 	POH.PO_HEADER_ID = POR.PO_HEADER_ID (+) AND
345 	RSH.SHIPMENT_NUM=v_shipment_num AND
346 	POH.VENDOR_ID= v_vendor_id AND
347 	POH.VENDOR_SITE_ID=v_vendor_site_id;
348 
349 
350 BEGIN
351 
352 asn_buyers :=  asn_buyers_empty;
353 --asn_buyer_num := 0;
354 
355 x_shipment_num := wf_engine.GetItemAttrText  ( itemtype => l_item_type,
356                                                itemkey  => l_item_key,
357                                                aname    => 'SHIPMENT_NUM');
358 
359 x_vendor_id := wf_engine.GetItemAttrNumber     ( itemtype => l_item_type,
360                                                  itemkey  => l_item_key,
361                                                  aname    => 'VENDOR_ID');
362 
363 x_vendor_site_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
364                                                   itemkey  => l_item_key,
365                                                   aname    => 'VENDOR_SITE_ID');
366 
367 --dbms_output.put_line('Shipment Num is ' || x_shipment_num);
368 i:= 1;
369 open asn_buyer(x_shipment_num,x_vendor_id,x_vendor_site_id);
370 --dbms_output.put_line('Before Open Buyer Cursor ');
371 -- Populate the global pl/sql table with buyer id's
372 LOOP
373 	FETCH asn_buyer INTO x_buyer_id;
374 	EXIT WHEN asn_buyer%NOTFOUND;
375 	asn_buyers(i) := x_buyer_id;
376 --dbms_output.put_line('Buyer Id is  ' || to_char(x_buyer_id));
377         i := i+1;
378 
379 END LOOP;
380 
381 CLOSE asn_buyer;
382 --dbms_output.put_line('First Buyer Id is ' || to_char(asn_buyers(1)));
383 
384      BEGIN
385 
386 	SELECT distinct poh.shipment_num,pov.vendor_name,
387                 poh.shipped_date,
388 		to_char(poh.shipped_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
389 		poh.expected_receipt_date,
390 		to_char(poh.expected_receipt_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
391        		poh.invoice_num,poh.total_invoice_amount,
392        	        poh.invoice_date,
393        	poh.tax_amount,poh.asn_type
394 	INTO   x_shipment_num,x_vendor_name,
395 		x_shipped_date,
396 		x_shipped_date_ts,
397 		x_expected_receipt_date,
398 		x_expected_receipt_ts,
399        		x_invoice_num,x_total_invoice_amount,
400        		x_invoice_date,x_tax_amount,x_asn_type
401 	FROM   POS_HEADERS_V poh,PO_VENDORS pov
402 	WHERE  poh.shipment_num   = x_shipment_num AND
403                poh.vendor_id      = pov.vendor_id  AND
404                poh.vendor_id      = x_vendor_id    AND
405                poh.vendor_site_id = x_vendor_site_id ;
406     EXCEPTION
407         WHEN NO_DATA_FOUND then
408         l_document1:= 'ASN has been cancelled';
409         -- fnd_message.get_string('POS','POS_ASN_CANCELLED');
410         wf_engine.SetItemAttrText
411                             (
412                             ItemType    => l_item_type,
413                             ItemKey     => l_item_key,
414                             aname       => 'ASN_HEADERS',
415                             avalue      => l_document1
416                             );
417 
418         wf_directory.getusername('PER',
419 			       asn_buyers(1),
420 			       x_buyer_user_name,
421 			       x_buyer_user_displayname);
422 
423         wf_engine.SetItemAttrText
424                             (
425                             ItemType    => l_item_type,
426                             ItemKey     => l_item_key,
427                             aname       => 'ASN_BUYER',
428                             avalue      => x_buyer_user_name
429                             );
430         return;
431         WHEN OTHERS then
432         RAISE;
433     END;
434 
435 --dbms_output.put_line('Asn Type is ' || x_asn_type);
436 --dbms_output.put_line('Vendor Name is ' || x_vendor_name);
437    wf_engine.SetItemAttrText
438                             (
439                             ItemType    => l_item_type,
440                             ItemKey     => l_item_key,
441                             aname       => 'SUPPLIER',
442                             avalue      => x_vendor_name
443                             );
444 
445    wf_engine.SetItemAttrText
446                             (
447                             ItemType    => l_item_type,
448                             ItemKey     => l_item_key,
449                             aname       => 'EXPECTED_RECEIPT_TS',
450                             avalue      => x_expected_receipt_ts
451                             );
452 
453    wf_engine.SetItemAttrDate
454                             (
455                             ItemType    => l_item_type,
456                             ItemKey     => l_item_key,
457                             aname       => 'EXPECTED_RECEIPT_DATE',
458                             avalue      => x_expected_receipt_date
459                             );
460 
461    wf_engine.SetItemAttrText
462                             (
463                             ItemType    => l_item_type,
464                             ItemKey     => l_item_key,
465                             aname       => 'ASN_TYPE',
466                             avalue      => x_asn_type
467                             );
468 
469 --x_display_type := 'text/html';
470 
471 l_document1 := '<font size=3 color=#336699 face=arial><b>' ||fnd_message.get_string('POS', 'POS_ASN_NOTIF_DETAILS') ||
472                 '</B></font><HR size=1 color=#cccc99>' ;
473 
474 l_document1 := l_document1 || '<TABLE  cellpadding=2 cellspacing=1>';
475 
476 l_document1 := l_document1 || '<TR>' ;
477 
478 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
479                      fnd_message.get_string('POS', 'POS_ASN_NOTIF_SUPP_NAME') || '</B></font></TD> ' ;
480 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
481                       x_vendor_name || '</font></TD> ' ;
482 l_document1 := l_document1 || '</TR>' ;
483 
484 l_document1 := l_document1 || '<TR>' ;
485 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
486                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIPMENT_NUM') || '</B></font></TD> ' ;
487 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
488                       x_shipment_num || '</font></TD> ' ;
489 l_document1 := l_document1 || '</TR>' ;
490 
491 l_document1 := l_document1 || '<TR>' ;
492 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
493                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIPMENT_DATE') || '</B></font></TD> ' ;
494 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
495                       x_shipped_date_ts || '</font></TD> ' ;
496 l_document1 := l_document1 || '</TR>' ;
497 
498 l_document1 := l_document1 || '<TR>' ;
499 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
500                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_EXPT_RCPT_DATE') || '</B></font></TD> ';
501 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
502                       x_expected_receipt_ts || '</font></TD> ' ;
503 l_document1 := l_document1 || '</TR>' ;
504 
505 l_document1 := l_document1 || '</TABLE></P>' ;
506 
507 
508 IF (x_asn_type = 'ASBN') THEN
509 
510   wf_engine.SetItemAttrText
511                             (
512                             ItemType    => l_item_type,
513                             ItemKey     => l_item_key,
514                             aname       => 'INVOICE_INFO',
515                             avalue      => 'and Invoice'
516                             );
517 
518   wf_engine.SetItemAttrText
519                             (
520                             ItemType    => l_item_type,
521                             ItemKey     => l_item_key,
522                             aname       => 'INVOICE_NUM',
523                             avalue      => x_invoice_num
524                             );
525 
526 
527 l_document1 := l_document1 || '<font size=3 color=#336699 face=arial><b>'||
528                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_BILL_INFO') ||'</B></font><HR size=1 color=#cccc99>' ;
529 
530 l_document1 := l_document1 || '<TABLE  cellpadding=2 cellspacing=1>';
531 
532 l_document1 := l_document1 || '<TR>' ;
533 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
534                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_NUMBER') || '</B></font></TD> ' ;
535 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
536                       x_invoice_num || '</font></TD> ' ;
537 l_document1 := l_document1 || '</TR>' ;
538 
539 l_document1 := l_document1 || '<TR>' ;
540 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
541                        fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_AMOUNT') || '</B></font></TD> ' ;
542 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
543                       x_total_invoice_amount || '</font></TD> ' ;
544 l_document1 := l_document1 || '</TR>' ;
545 
546 l_document1 := l_document1 || '<TR>' ;
547 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
548                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_DATE') || '</B></font></TD> ' ;
549 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
550                       x_invoice_date || '</font></TD> ' ;
551 l_document1 := l_document1 || '</TR>' ;
552 
553 l_document1 := l_document1 || '<TR>' ;
554 l_document1 := l_document1 || '<TD nowrap><font color=black><B>' ||
555                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_TAX_AMOUNT') || '</B></font></TD> ' ;
556 l_document1 := l_document1 || '<TD nowrap><font color=black>' ||
557                       x_tax_amount || '</font></TD> ' ;
558 l_document1 := l_document1 || '</TR>' ;
559 
560 l_document1 := l_document1 || '</TABLE></P>' ;
561 
562 
563 ELSE
564  wf_engine.SetItemAttrText
565                             (
566                             ItemType    => l_item_type,
567                             ItemKey     => l_item_key,
568                             aname       => 'INVOICE_INFO',
569                             avalue      => ''
570                             );
571 
572   wf_engine.SetItemAttrText
573                             (
574                             ItemType    => l_item_type,
575                             ItemKey     => l_item_key,
576                             aname       => 'INVOICE_NUM',
577                             avalue      => ''
578                             );
579 
580 END IF;
581 
582  -- This Attribute is not being set to l_document any more , moved to the body section as pl/sql clob
583 
584      wf_engine.SetItemAttrText
585                             (
586                             ItemType    => l_item_type,
587                             ItemKey     => l_item_key,
588                             aname       => 'ASN_HEADERS',
589                             avalue      => ''
590                             );
591 
592 -- Set the Buyer Count and Current Number in the Workflow
593     wf_engine.SetItemAttrNumber
594                             (
595                             ItemType    => l_item_type,
596                             ItemKey     => l_item_key,
597                             aname       => 'TOTAL_BUYER_NUM',
598                             avalue      => asn_buyers.COUNT
599                             );
600     wf_engine.SetItemAttrNumber
601                             (
602                             ItemType    => l_item_type,
603                             ItemKey     => l_item_key,
604                             aname       => 'CURR_BUYER_NUM',
605                             avalue      => 1
606                             );
607 
608 END GET_ASN_BUYERS;
609 
610 
611 
612 PROCEDURE GENERATE_ASN_BODY(p_ship_num_buyer_id IN VARCHAR2,
613 			    display_type   in      Varchar2,
614 			    document in OUT NOCOPY clob,
615 			    document_type  in OUT NOCOPY  varchar2)
616 IS
617 
618 TYPE asn_lines_record is record (
619 po_num          po_headers_all.segment1%TYPE,
620 po_rev_no       po_headers_all.revision_num%TYPE,
621 line_num        po_lines_all.line_num%TYPE,
622 ship_num        po_line_locations_all.shipment_num%TYPE,
623 item_num        varchar2(80),
624 item_desc       po_lines_all.item_description%TYPE,
625 uom             po_lines_all.unit_meas_lookup_code%TYPE,
626 order_qty       po_line_locations_all.quantity%TYPE,
627 ship_qty        rcv_transactions_interface.quantity%TYPE,
628 --rcvd_qty        po_line_locations_all.quantity_received%type,
629 rcvd_qty        NUMBER,
630 ship_to         rcv_transactions_interface.ship_to_location_code%type,
631 ship_to_org     org_organization_definitions.ORGANIZATION_CODE%type
632 );
633 
634 NL              VARCHAR2(1) := fnd_global.newline;
635 l_document      VARCHAR2(32000) := '';
636 l_asn_lines     asn_lines_record;
637 x_shipment_num  pos_lines_v.shipment_num%TYPE;
638 x_buyer_id		NUMBER;
639 x_vendor_id		NUMBER;
640 x_vendor_site_id	NUMBER;
641 x_num_lines		NUMBER;
642 x_bvs_id		VARCHAR2(50);
643 x_vs_id			VARCHAR2(50);
644 
645 h_shipment_num  pos_headers_v.shipment_num%TYPE;
646 h_asn_type      VARCHAR2(20);
647 h_vendor_name   VARCHAR2(240);
648 h_shipped_date  varchar2(2000);
649 h_expected_receipt_date varchar2(2000);
650 h_invoice_num   VARCHAR2(50);
651 h_total_invoice_amount  NUMBER;
652 h_invoice_date  DATE;
653 h_tax_amount    NUMBER;
654 
655 l_remit_to_site_id	NUMBER;
656 l_remit_to_site_code	PO_VENDOR_SITES_ALL.vendor_site_code%TYPE;
657 l_remit_to_address1	PO_VENDOR_SITES_ALL.address_line1%TYPE;
658 l_remit_to_address2	PO_VENDOR_SITES_ALL.address_line2%TYPE;
659 l_remit_to_address3	PO_VENDOR_SITES_ALL.address_line3%TYPE;
660 l_remit_to_address4	PO_VENDOR_SITES_ALL.address_line4%TYPE;
661 l_remit_to_czinfo	VARCHAR2(200);
662 l_remit_to_country	PO_VENDOR_SITES_ALL.country%TYPE;
663 
664 l_remit_exist_flag	VARCHAR2(1) := 'T';
665 
666 
667 CURSOR asn_lines(p_shipment_num varchar2,v_buyer_id number,p_vendor_id number,p_vendor_site_id number) IS
668 SELECT
669       DECODE(PRL.PO_RELEASE_ID,NULL,PH.SEGMENT1,PH.SEGMENT1 || '-' || TO_CHAR(PRL.RELEASE_NUM)) PO_NUM,
670       ph.revision_num REVISION_NUM,
671       pola.line_num LINE_NUM,
672       pll.shipment_num SHIPMENT_NUM,
673       pos_get.get_item_number(rti.item_id,ood.organization_id) ITEM_NUM,
674       pola.item_description ITEM_DESC,
675       pola.unit_meas_lookup_code UOM,
676       pll.quantity QUANTITY_ORDERED,
677       rti.quantity QUANTITY_SHIPPED,
678       pll.quantity_received QUANTITY_RECEIVED,
679       NVL( HRL.LOCATION_CODE,
680       SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) ship_to_location_code,
681       ood.ORGANIZATION_CODE ORGANIZATION_CODE
682 FROM  rcv_transactions_interface rti, rcv_headers_interface rhi ,
683       org_organization_definitions ood,po_releases_all prl,
684       po_line_locations_all pll,po_lines_all pola,po_headers_all ph,
685       hr_locations_all_tl hrl, hz_locations hz
686 WHERE rhi.header_interface_id=rti.header_interface_id and
687       rhi.shipment_num= p_shipment_num and
688       pola.po_line_id = rti.po_line_id and
689       nvl(prl.agent_id,ph.agent_id)=v_buyer_id and
690       pll.po_release_id = prl.po_release_id(+) and
691       pll.line_location_id=rti.po_line_location_id and
692       ood.organization_id = pll.ship_to_organization_id  and
693       ph.po_header_id = rti.po_header_id and
694       rti.vendor_id   = p_vendor_id and
695       rti.vendor_site_id = p_vendor_site_id and
696       HRL.LOCATION_ID (+) = rti.SHIP_TO_LOCATION_ID AND
697       HRL.LANGUAGE(+) = USERENV('LANG') AND
698       HZ.LOCATION_ID(+) = rti.SHIP_TO_LOCATION_ID
699 UNION ALL
700 SELECT
701       DECODE(PRL.PO_RELEASE_ID,NULL,PH.SEGMENT1,PH.SEGMENT1 || '-' || TO_CHAR(PRL.RELEASE_NUM)) PO_NUM,
702       ph.revision_num REVISION_NUM,
703       pola.line_num LINE_NUM,
704       pll.shipment_num SHIPMENT_NUM,
705       pos_get.get_item_number(rsl.item_id,ood.organization_id) ITEM_NUM,
706       pola.item_description ITEM_DESC,
707       pola.unit_meas_lookup_code UOM,
708       pll.quantity QUANTITY_ORDERED,
709       rsl.quantity_shipped QUANTITY_SHIPPED,
710       pll.quantity_received QUANTITY_RECEIVED,
711       NVL( HRL.LOCATION_CODE,
712       SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) ship_to_location_code,
713       ood.ORGANIZATION_CODE ORGANIZATION_CODE
714 FROM  rcv_shipment_lines rsl, rcv_shipment_headers rsh ,
715       org_organization_definitions ood,po_releases_all prl,
716       po_line_locations_all pll,po_lines_all pola,po_headers_all ph,
717       hr_locations_all_tl hrl,hz_locations hz
718 WHERE rsh.shipment_header_id=rsl.shipment_header_id and
719       rsh.shipment_num= p_shipment_num and
720       pola.po_line_id = rsl.po_line_id and
721       nvl(prl.agent_id,ph.agent_id)=v_buyer_id and
722       pll.po_release_id = prl.po_release_id(+) and
723       pll.line_location_id=rsl.po_line_location_id and
724       ood.organization_id = pll.ship_to_organization_id  and
725       ph.po_header_id = rsl.po_header_id and
726       HRL.LOCATION_ID (+) = rsl.SHIP_TO_LOCATION_ID AND
727       HRL.LANGUAGE(+) = USERENV('LANG') AND
728       HZ.LOCATION_ID(+) = rsl.SHIP_TO_LOCATION_ID and
729       rsh.vendor_id = p_vendor_id and
730       rsh.vendor_site_id=p_vendor_site_id;
731 
732 
733 BEGIN
734 
735 x_shipment_num   := substr(p_ship_num_buyer_id,1,instr(p_ship_num_buyer_id,'*%$*')-1);
736 x_bvs_id         := substr(p_ship_num_buyer_id,instr(p_ship_num_buyer_id,'*%$*')+ 4,length(p_ship_num_buyer_id)-2);
737 x_buyer_id       := substr(x_bvs_id,1,instr(x_bvs_id, '%')- 1);
738 x_vs_id          := substr(x_bvs_id,instr(x_bvs_id,'%')+1,length(x_bvs_id)-2);
739 x_vendor_id      := substr(x_vs_id,1,instr(x_vs_id,'#')-1);
740 x_vendor_site_id := substr(x_vs_id,instr(x_vs_id,'#')+ 1,length(x_vs_id)-2);
741 
742 --Generate the Header
743 
744      BEGIN
745 
746 	SELECT distinct poh.shipment_num,pov.vendor_name,
747 		to_char(poh.shipped_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
748 		to_char(poh.expected_receipt_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
749        		poh.invoice_num,poh.total_invoice_amount,
750        		poh.invoice_date,
751        		poh.tax_amount,poh.asn_type,
752                 poh.remit_to_site_id
753 	INTO   h_shipment_num,h_vendor_name,
754 		h_shipped_date,
755 		h_expected_receipt_date,
756        		h_invoice_num,h_total_invoice_amount,
757        		h_invoice_date,
758        		h_tax_amount,h_asn_type,
759                 l_remit_to_site_id
760 	FROM   POS_HEADERS_V poh,PO_VENDORS pov
761 	WHERE  poh.shipment_num   = x_shipment_num AND
762                poh.vendor_id      = pov.vendor_id  AND
763                poh.vendor_id      = to_number(x_vendor_id)   AND
764                poh.vendor_site_id = to_number(x_vendor_site_id);
765      EXCEPTION
766         WHEN NO_DATA_FOUND then
767         l_document := 'NO_DATA';
768         WHEN OTHERS then
769         RAISE;
770      END;
771 
772 if (l_document = 'NO_DATA') then
773         -- if you didnt find any data in the headers do not draw the header section at all
774         l_document := '';
775 else
776 
777 
778   if (l_remit_to_site_id is not null) then
779     BEGIN
780 
781       SELECT pvs.VENDOR_SITE_CODE,
782              pvs.address_line1,
783              pvs.address_line2,
784              pvs.address_line3,
785              pvs.address_line4,
786              pvs.city || ', ' || pvs.state || ' ' || pvs.zip,
787 	     pvs.country
788       INTO   l_remit_to_site_code,
789 	     l_remit_to_address1,
790 	     l_remit_to_address2,
791 	     l_remit_to_address3,
792 	     l_remit_to_address4,
793 	     l_remit_to_czinfo,
794              l_remit_to_country
795       FROM   PO_VENDOR_SITES_ALL pvs
796       WHERE  pvs.vendor_site_id = l_remit_to_site_id;
797 
798     EXCEPTION
799       WHEN NO_DATA_FOUND THEN
800         l_remit_exist_flag := 'F';
801 
802       WHEN OTHERS then
803         RAISE;
804     END;
805 
806   end if;
807 
808 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=#cccc99>' ;
809 
810 l_document := l_document || '<TABLE cellpadding=2 cellspacing=1>';
811 
812 l_document := l_document || '<TR>' ;
813 l_document := l_document || '<TD nowrap>' ||
814                      fnd_message.get_string('POS', 'POS_ASN_NOTIF_SUPP_NAME') || '</TD> ' ;
815 l_document := l_document || '<TD nowrap><B>' || h_vendor_name || '</B></TD> ' ;
816 l_document := l_document || '</TR>' ;
817 
818 l_document := l_document || '<TR>' ;
819 l_document := l_document || '<TD nowrap>' ||
820                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIPMENT_NUM') || '</TD> ' ;
821 l_document := l_document || '<TD nowrap><B>' || h_shipment_num || '</B></TD> ' ;
822 l_document := l_document || '</TR>' ;
823 
824 l_document := l_document || '<TR>' ;
825 l_document := l_document || '<TD nowrap>' ||
826                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIPMENT_DATE') || '</TD> ' ;
827 l_document := l_document || '<TD nowrap><B>' || h_shipped_date || '</B></TD> ' ;
828 l_document := l_document || '</TR>' ;
829 
830 l_document := l_document || '<TR>' ;
831 l_document := l_document || '<TD nowrap>' ||
832                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_EXPT_RCPT_DATE') || '</TD> ';
833 l_document := l_document || '<TD nowrap><B>' || h_expected_receipt_date || '</B></TD> ' ;
834 l_document := l_document || '</TR>' ;
835 
836 l_document := l_document || '</TABLE></P>' ;
837 
838 
839 IF (h_asn_type = 'ASBN') THEN
840 
841 l_document := l_document || '<font size=3 color=#336699 face=arial><b>'||
842                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_BILL_INFO') ||'</B></font><HR size=1 color=#cccc99>' ;
843 
844 l_document := l_document || '<TABLE  cellpadding=2 cellspacing=1>' ;
845 
846 l_document := l_document || '<TR>' ;
847 l_document := l_document || '<TD nowrap>' ||
848                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_NUMBER') || '</TD> ' ;
849 l_document := l_document || '<TD nowrap><B>' ||
850                       h_invoice_num || '</B></TD> ' ;
851 l_document := l_document || '</TR>' ;
852 
853 l_document := l_document || '<TR>' ;
854 l_document := l_document || '<TD nowrap>' ||
855                        fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_AMOUNT') || '</TD> ' ;
856 l_document := l_document || '<TD nowrap><B>' ||
857                       h_total_invoice_amount || '</B></TD> ' ;
858 l_document := l_document || '</TR>' ;
859 
860 l_document := l_document || '<TR>' ;
861 l_document := l_document || '<TD nowrap>' ||
862                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_INVOICE_DATE') || '</TD> ' ;
863 l_document := l_document || '<TD nowrap><B>' || h_invoice_date || '</B></TD></TR>' ;
864 
865 l_document := l_document || '<TR>' ;
866 l_document := l_document || '<TD nowrap>' ||
867                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_TAX_AMOUNT') || '</TD> ' ;
868 l_document := l_document || '<TD nowrap><B>' || h_tax_amount || '</B></TD> ' ;
869 l_document := l_document || '</TR>' ;
870 
871 --mji Remit-to Info
872 IF (l_remit_exist_flag = 'T') THEN
873 
874 l_document := l_document || '<TR>' ;
875 l_document := l_document || '<TD nowrap>' ||
876                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_REMIT_NAME') || '</TD> ' ;
877 l_document := l_document || '<TD nowrap><B>' || l_remit_to_site_code || '</B></TD></TR>' ;
878 
879 
880 l_document := l_document || '<TR>' ;
881 l_document := l_document || '<TD nowrap>' ||
882                       fnd_message.get_string('POS', 'POS_ASN_NOTIF_REMIT_ADDR') || '</TD> ' ;
883 l_document := l_document || '<TD nowrap><B>' || l_remit_to_address1 || '</B></TD></TR>' ;
884 
885 
886 if (l_remit_to_address2 is not null) then
887   l_document := l_document || '<TR>' ;
888   l_document := l_document || '<TD> </TD> ' ;
889   l_document := l_document || '<TD nowrap><B>' || l_remit_to_address2 || '</B></TD> ' ;
890   l_document := l_document || '</TR>' ;
891 end if;
892 
893 
894 if (l_remit_to_address3 is not null) then
895   l_document := l_document || '<TR>' ;
896   l_document := l_document || '<TD> </TD> ' ;
897   l_document := l_document || '<TD nowrap><B>' || l_remit_to_address3 || '</B></TD> ' ;
898   l_document := l_document || '</TR>' ;
899 end if;
900 
901 
902 if (l_remit_to_address4 is not null) then
903   l_document := l_document || '<TR>' ;
904   l_document := l_document || '<TD> </TD> ' ;
905   l_document := l_document || '<TD nowrap><B>' || l_remit_to_address4 || '</B></TD> ' ;
906   l_document := l_document || '</TR>' ;
907 end if;
908 
909 
910 l_document := l_document || '<TR>' ;
911 l_document := l_document || '<TD> </TD> ' ;
912 l_document := l_document || '<TD nowrap><B>' || l_remit_to_czinfo || '</B></TD> ' ;
913 l_document := l_document || '</TR>' ;
914 
915 
916 l_document := l_document || '<TR>' ;
917 l_document := l_document || '<TD> </TD> ' ;
918 l_document := l_document || '<TD nowrap><B>' || l_remit_to_country || '</B></TD> ' ;
919 l_document := l_document || '</TR>' ;
920 
921 END IF;
922 
923 l_document := l_document || '</TABLE></P>' ;
924 
925 END IF;
926 end if ; -- end of if no data
927 -- End of Header Info
928 
929 
930 -- check if notification was cancelled then do not generate the table
931 select count(*) into x_num_lines from pos_headers_v
932 where shipment_num=x_shipment_num and
933 vendor_id  = x_vendor_id and
934 vendor_site_id = x_vendor_site_id;
935 
936 if (x_num_lines < 1) then
937 	l_document := '';
938 	l_document := fnd_message.get_string('POS', 'POS_ASN_NOTIF_CANCELLED');
939 
940  	WF_NOTIFICATION.WriteToClob(document, l_document);
941 
942 else
943 OPEN asn_lines(x_shipment_num,x_buyer_id,x_vendor_id,x_vendor_site_id);
944 
945 
946 --Generate HTML TABLE HEADER
947 l_document := l_document || NL || NL ||'<font size=3 color=#336699 face=arial><b>'||
948                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_ASN_DTLS') ||'</B></font><HR size=1 color=#cccc99>'|| NL ;
949 
950 l_document := l_document || '<TABLE WIDTH=100% cellpadding=2 cellspacing=1>';
951 l_document := l_document || '<TR bgcolor=#cccc99>' || NL;
952 
953 l_document := l_document || '<TH align=left><font color=#336699 >' ||
954                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_ORDER_NUMBER') || '</font></TH>' || NL;
955 
956 l_document := l_document || '<TH align=left><font color=#336699 >' ||
957                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_REVISION_NUMBER') || '</font></TH>' || NL;
958 
959 l_document := l_document || '<TH align=left><font color=#336699 >' ||
960                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_LINE_NUM') || '</font></TH>' || NL;
961 
962 l_document := l_document || '<TH align=left><font color=#336699 >' ||
963                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIP_NUM') || '</font></TH>' || NL;
964 
965 l_document := l_document || '<TH align=left><font color=#336699 >' ||
966                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_ITEM') || '</font></TH>' || NL;
967 
968 l_document := l_document || '<TH align=left><font color=#336699 >' ||
969                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_ITEM_DESC') || '</font></TH>' || NL;
970 
971 l_document := l_document || '<TH align=left><font color=#336699 >' ||
972                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_UOM') || '</font></TH>' || NL;
973 
974 l_document := l_document || '<TH align=left><font color=#336699 >' ||
975                 fnd_message.get_string('POS','POS_ASN_NOTIF_QUANTITY_ORD') || '</font></TH>' || NL;
976 
977 l_document := l_document || '<TH align=left><font color=#336699 >' ||
978                 fnd_message.get_string('POS','POS_ASN_NOTIF_QUANTITY_SHIP') || '</font></TH>' || NL;
979 
980 l_document := l_document || '<TH align=left><font color=#336699 >' ||
981                 fnd_message.get_string('POS','POS_ASN_NOTIF_QUANTITY_RCVD') || '</font></TH>' || NL;
982 
983 l_document := l_document || '<TH align=left nowrap><font color=#336699 >' ||
984                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIP_TO') || '</font></TH>' || NL;
985 
986 l_document := l_document || '<TH align=left><font color=#336699 >' ||
987                 fnd_message.get_string('POS', 'POS_ASN_NOTIF_SHIP_TO_ORG') || '</font></TH>' || NL;
988 
989 l_document := l_document || '</TR>' || NL;
990 
991 l_document := l_document || '</B>';
992 
993      LOOP
994 
995         FETCH asn_lines INTO l_asn_lines;
996         EXIT WHEN asn_lines%NOTFOUND;
997 
998         l_document := l_document || '<TR bgcolor=#f7f7e7>' || NL;
999 
1000         l_document := l_document || '<TD><font color=black>' ||
1001                       nvl(l_asn_lines.po_num, ' ') || '</font></TD> ' || NL;
1002 
1003         l_document := l_document || '<TD><font color=black>' ||
1004                       nvl(to_char(l_asn_lines.po_rev_no), ' ') || '</font></TD> ' || NL;
1005 
1006         l_document := l_document || '<TD><font color=black>' ||
1007                       nvl(to_char(l_asn_lines.line_num), ' ') || '</font></TD> ' || NL;
1008 
1009         l_document := l_document || '<TD><font color=black>' ||
1010                       nvl(to_char(l_asn_lines.ship_num), ' ') || '</font></TD> ' || NL;
1011 
1012         l_document := l_document || '<TD><font color=black>' ||
1013                       nvl(l_asn_lines.item_num, ' ') || '</font></TD> ' || NL;
1014 
1015         l_document := l_document || '<TD><font color=black>' ||
1016                       nvl(l_asn_lines.item_desc, ' ') || '</font></TD> ' || NL;
1017 
1018         l_document := l_document || '<TD><font color=black>' ||
1019                       nvl(l_asn_lines.uom, ' ') || '</font></TD> ' || NL;
1020 
1021         l_document := l_document || '<TD><font color=black>' ||
1022                       nvl(to_char(l_asn_lines.order_qty), ' ') || '</font></TD> ' || NL;
1023 
1024         l_document := l_document || '<TD><font color=black>' ||
1025                       nvl(to_char(l_asn_lines.ship_qty), ' ') || '</font></TD> ' || NL;
1026 
1027         l_document := l_document || '<TD><font color=black>' ||
1028                       nvl(to_char(l_asn_lines.rcvd_qty), ' ') || '</font></TD> ' || NL;
1029 
1030         l_document := l_document || '<TD nowrap><font color=black>' ||
1031                       nvl(l_asn_lines.ship_to, ' ') || '</font></TD> ' || NL;
1032 
1033         l_document := l_document || '<TD><font color=black>' ||
1034                       nvl(l_asn_lines.ship_to_org, ' ') || '</font></TD> ' || NL;
1035 
1036         l_document := l_document || '</TR>' || NL;
1037 
1038  	WF_NOTIFICATION.WriteToClob(document, l_document);
1039 	l_document := null;
1040      END LOOP;
1041 
1042      CLOSE asn_lines;
1043 
1044 	l_document := l_document || '</TABLE></P>' || NL;
1045 
1046  	WF_NOTIFICATION.WriteToClob(document, l_document);
1047 end if;
1048 
1049 EXCEPTION
1050 WHEN OTHERS THEN
1051     RAISE;
1052 END GENERATE_ASN_BODY;
1053 
1054 END POS_ASN_NOTIF;