DBA Data[Home] [Help]

PACKAGE BODY: APPS.AP_XML_INVOICE_INBOUND_PKG

Source


1 PACKAGE BODY AP_XML_INVOICE_INBOUND_PKG as
2 /* $Header: apxmlinb.pls 120.2 2006/02/14 08:55:14 kgurumur noship $ */
3 
4 function get_token_display_field(p_lookup_code in VARCHAR2) return VARCHAR2 as
5 
6   l_displayed_field VARCHAR2(80);
7   cursor l_token_csr(c_lookup_code in VARCHAR2) is
8 	select 	displayed_field
9 	from 	ap_lookup_codes
10 	where 	lookup_type = 'XML TOKEN NAME'
11           and 	lookup_code = p_lookup_code;
12 
13 begin
14   open l_token_csr(p_lookup_code);
15   fetch l_token_csr into l_displayed_field;
16   close l_token_csr;
17   return l_displayed_field;
18 end;
19 
20 /*===========================================================================+
21  | PROCEDURE                                                                 |
22  |    correct_freight_line                                                   |
23  |                                                                           |
24  | DESCRIPTION                                                               |
25  +===========================================================================*/
26 procedure correct_freight_line(p_invoice_id in NUMBER) as
27 
28 begin
29   ap_debug_pkg.print('Y',
30                      'AP_XML_INVOICE_INBOUND_PKG.correct_freight_line(+)');
31 
32   update ap_invoice_lines_interface
33   set DIST_CODE_COMBINATION_ID =
34     (select FREIGHT_CODE_COMBINATION_ID
35      from ap_system_parameters_all sys,ap_invoices_interface h
36      where sys.org_id = h.org_id and h.invoice_id = p_invoice_id)
37   where line_type_lookup_code = 'FREIGHT' and
38         invoice_id = p_invoice_id;
39 
40   ap_debug_pkg.print('Y',
41                      'AP_XML_INVOICE_INBOUND_PKG.correct_freight)lihe(-)');
42 end;
43 
44 
45 /*===========================================================================+
46  | PROCEDURE                                                                 |
47  |    correct_line_type                                                      |
48  |                                                                           |
49  | DESCRIPTION                                                               |
50  +===========================================================================*/
51 procedure correct_line_type(p_invoice_id in number) as
52 begin
53   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND.correct_line_type(+)');
54   --
55   UPDATE AP_INVOICE_LINES_INTERFACE
56   SET LINE_TYPE_LOOKUP_CODE = 'MISCELLANEOUS'
57   WHERE LINE_TYPE_LOOKUP_CODE NOT IN ('ITEM', 'TAX', 'FREIGHT') and
58         INVOICE_ID = p_invoice_id;
59   --
60   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND.correct_line_type(-)');
61 end;
62 
63 
64 /*===========================================================================+
65  | PROCEDURE                                                                 |
66  |    change_case                                                            |
67  |                                                                           |
68  | DESCRIPTION                                                               |
69  |   Called by XMLGateway to change the case                                 |
70  +===========================================================================*/
71 
72 procedure change_case(p_in_string in VARCHAR2,
73                       p_out_string out NOCOPY VARCHAR2,
74                       p_direction in VARCHAR2 default 'U') as
75 
76 begin
77   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.change_case(+)');
78 
79   if p_direction = 'U' then
80     p_out_string := upper(p_in_string);
81   elsif p_direction = 'L' then
82     p_out_string := lower(p_in_string);
83   else
84     null;
85   end if;
86 
87   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.change_case(-)');
88 end;
89 
90 /*===========================================================================+
91  | PROCEDURE                                                                 |
92  |    derive_org_id                                                          |
93  |                                                                           |
94  | DESCRIPTION                                                               |
95  |   Called by XMLGateway to derive org_id                                   |
96  +===========================================================================*/
97 procedure derive_org_id(p_po_number in VARCHAR2 default NULL,
98                         p_org_id out NOCOPY NUMBER) as
99   l_org_id NUMBER;
100 begin
101   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.derive_org_id(+)');
102   begin
103     select distinct org_id into l_org_id
104     from po_headers_all
105     where segment1 = p_po_number;
106   exception
107     when others then
108       ap_debug_pkg.print('Y',
109                        'AP_XML_INVOICE_INBOUND_PKG.derive_org_id(EXCEPTION)');
110      l_org_id := NULL;
111   end;
112   p_org_id := l_org_id;
113   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.derive_org_id(-)');
114 end;
115 
116 /*===========================================================================+
117  | PROCEDURE                                                                 |
118  |    derive_vendor_id                                                       |
119  |                                                                           |
120  | DESCRIPTION                                                               |
121  |   Called by XMLGateway to derive vendor_id                                |
122  +===========================================================================*/
123 procedure derive_vendor_id(p_vendor_site_id in NUMBER,
124                            p_org_id in NUMBER,
125                            p_vendor_id out NOCOPY NUMBER) as
126 begin
127   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.derive_vendor_id(+)');
128   select vendor_id into p_vendor_id
129   from po_vendor_sites_all
130   where org_id = p_org_id and vendor_site_id = p_vendor_site_id;
131   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.derive_vendor_id(-)');
132 exception
133   when others then
134     ap_debug_pkg.print('Y',
135                     'AP_XML_INVOICE_INBOUND_PKG.derive_vendor_id(EXCEPTION)');
136     p_vendor_id := NULL;
137 end;
138 /*===========================================================================+
139  | PROCEDURE                                                                 |
140  |    derive_email_address                                                   |
141  |                                                                           |
142  | DESCRIPTION                                                               |
143  |   Called by XMLGateway to derive email_address                            |
144  +===========================================================================*/
145 procedure derive_email_address(p_vendor_site_id in NUMBER,
146                                p_vendor_id in NUMBER,
147                                p_email_address out NOCOPY VARCHAR2) as
148 -- Bug 2079388
149 l_statement VARCHAR2(2000) ;
150 l_party_type varchar2(1) := 'S';
151 
152 
153 begin
154     ap_debug_pkg.print('Y',
155                        'AP_XML_INVOICE_INBOUND_PKG.derive_email_address(+)');
156 
157 /* Bug 2079388
158    Replace the sql below with dynamic sql to prevent dependency on XML
159    datamodel in some cases. Details mentioned in the bug
160 
161     select company_admin_email
162     into p_email_address
163     from ecx_tp_headers
164     where party_type = 'S' and
165           party_site_id = p_vendor_site_id and
166           party_id = p_vendor_id;
167     ap_debug_pkg.print('Y',
168                        'AP_XML_INVOICE_INBOUND_PKG.derive_email_address(-)');
169 
170 */
171     l_statement :=
172 'SELECT  company_admin_email INTO :tab FROM ecx_tp_headers
173     where party_site_id = ' || p_vendor_site_id ||
174           ' and party_id = ' || p_vendor_id ||
175           ' and party_type = '|| ''''|| l_party_type || '''';
176 
177     l_statement := 'BEGIN ' || l_statement;
178     l_statement := l_statement || '; END;';
179 
180     EXECUTE IMMEDIATE l_statement USING OUT p_email_address ;
181 
182 
183 exception
184   when others then
185     ap_debug_pkg.print('Y',
186                 'AP_XML_INVOICE_INBOUND_PKG.derive_email_address(EXCEPTION)');
187     p_email_address := NULL;
188 end;
189 
190 /*===========================================================================+
191  | PROCEDURE                                                                 |
192  |    start_open_interface                                                   |
193  |                                                                           |
194  | DESCRIPTION                                                               |
195  |   Called by XMLGateway's post process trigger. This starts open interface.|
196  +===========================================================================*/
197 procedure start_open_interface as
198 
199   l_request_id number;
200   type num_table_type is table of number index by binary_integer;
201   l_inv_ids num_table_type;
202   l_vendor_ids num_table_type;
203   l_tax_code varchar2(30);
204 
205   cursor inv_csr is
206     select h.invoice_id, h.vendor_id
207     from   ap_invoices_interface h
208     where  h.source like 'XML GATEWAY' and
209            h.status is NULL and
210            h.vendor_name is NOT NULL;
211 
212 begin
213   ap_debug_pkg.print('Y',
214                      'AP_XML_INVOICE_INBOUND_PKG.start_open_interface(+)');
215 
216     open inv_csr;
217     fetch inv_csr bulk collect into l_inv_ids, l_vendor_ids;
218     close inv_csr;
219 
220     forall i in nvl(l_inv_ids.first,1)..nvl(l_inv_ids.last,0)
221         update ap_invoices_interface
222         set vendor_name = null
223         where invoice_id = l_inv_ids(i);
224      --
225      -- populate tax_code for line_type tax
226      -- fix line type lookup code
227      --
228     for i in nvl(l_inv_ids.first,1)..nvl(l_inv_ids.last,0) loop
229       --
230       correct_line_type(l_inv_ids(i));
231       --
232 -- Bug 2186813, part of obsoleting tax defaulting based on the p2p tax
233 -- setup form
234      --   AP_XML_TAX_DERIVATION_PKG.correct_tax(l_inv_ids(i), l_vendor_ids(i));
235       --
236       correct_freight_line(l_inv_ids(i));
237 
238 
239     end loop;
240   commit;
241 
242   --
243   -- Request is scheduled as a request set.
244   --
245   /*
246   l_request_id := FND_REQUEST.SUBMIT_REQUEST(
247                     application=>'SQLAP',
248                     program=> 'APXIIMPT',
249                     sub_request=>FALSE,
250                     argument1=>'XML GATEWAY');
251   arp_util_tax.debug('Request_id:'||l_request_id);
252   */
253   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.start_open_interface(-)');
254 end;
255 
256 
257 procedure send_email(p_mail_subject in VARCHAR2,
258                      p_mail_content in VARCHAR2,
259                      p_mail_address in VARCHAR2) as
260 
261   l_role                        VARCHAR2(100);
262   l_display_role_name           VARCHAR2(100);
263   l_item_key                    VARCHAR2(100);
264 
265 begin
266     ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.send_email(+)');
267     arp_util_tax.debug('Creating adhoc role(+)');
268     l_role := null;
269     l_display_role_name := null;
270     WF_DIRECTORY.createAdhocRole(role_name => l_role,
271                                  role_display_name => l_display_role_name,
272                                  email_address => p_mail_address,
273                                  notification_preference => 'MAILTEXT');
274     ap_debug_pkg.print('Y','Creating adhoc role(-)');
275     --
276     -- Creating a workflow process
277     --
278     select ap_p2p_inbound_notification_s.nextval into l_item_key from dual;
279     ap_debug_pkg.print('Y','Creating a workflow process(+)');
280     WF_ENGINE.createProcess('P2P',l_item_key, 'PROCESS_FOR_NOTIFICATION');
281 
282     ap_debug_pkg.print('Y','Creating a workflow process(-)');
283     --
284     -- Initializing attributes
285     --
286     ap_debug_pkg.print('Y','Initializing Mail Subject (+)');
287     ap_debug_pkg.print('Y','subject:'||p_mail_subject);
288 
289     WF_ENGINE.setItemAttrText('P2P',l_item_key, 'MAIL_SUBJECT',p_mail_subject);
290     ap_debug_pkg.print('Y','Initializing Mail Subject (-)');
291 
292     ap_debug_pkg.print('Y','Initializing Mail Header (+)');
293     WF_ENGINE.setItemAttrText('P2P',l_item_key, 'MAIL_HEADER',NULL);
294     ap_debug_pkg.print('Y','Initializing Mail Header (-)');
295 
296     ap_debug_pkg.print('Y','Initializing Mail Content (+)');
297     WF_ENGINE.setItemAttrText('P2P',
298                              l_item_key,'MAIL_CONTENT1', p_mail_content);
299 
300     ap_debug_pkg.print('Y','Initializing Mail Content (-)');
301 
302     ap_debug_pkg.print('Y','Initializing Adhoc Role(+)');
303     WF_ENGINE.setItemAttrText('P2P',l_item_key,'ADHOC_ROLE',l_role);
304     ap_debug_pkg.print('Y','Initializing Adhoc Role(-)');
305 
306     --
307     -- Starting the process
308     --
309     ap_debug_pkg.print('Y','Starting the process(+)');
310     WF_ENGINE.startProcess('P2P', l_item_key);
311     ap_debug_pkg.print('Y','Starting the process(-)');
312     ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.send_email(-)');
313 
314    commit;
315 end;
316 
317 
318 /*===========================================================================+
319  | PROCEDURE                                                                 |
320  |    notify_supplier                                                        |
321  |                                                                           |
322  | DESCRIPTION                                                               |
323  |   Called by Open interface to start Workflow notification.                |
324  +===========================================================================*/
325 procedure notify_supplier(p_request_id in NUMBER,
326                           p_calling_sequence VARCHAR2) as
327 
328   cursor l_invoice_line_number_csr is
329     select 	fnd_global.tab||displayed_field||': '
330     from  	ap_lookup_codes
331     where 	lookup_type = 'XML TOKEN NAME' and
332                 lookup_code = 'INVOICE LINE NUMBER';
333 
334   cursor l_invoice_number_csr is
335     select 	displayed_field||': '
336     from  	ap_lookup_codes
337     where 	lookup_type = 'XML TOKEN NAME' and
338                 lookup_code = 'INVOICE NUMBER';
339 
340   cursor l_email_csr(c_request_id in NUMBER) is
341     select 	distinct vendor_email_address
342     from 	ap_invoices_interface
343     where 	request_id = c_request_id
344     and         vendor_email_address is not null; --bug4065112
345 
346   cursor l_message_csr(c_request_id in NUMBER,
347                        c_vendor_email_address in VARCHAR2) is
348     select 	h.invoice_id, to_number(null),h.invoice_num, to_number(null),
349 		fnd_global.tab||fnd_global.tab||lc.description||
350 		decode(r.token_name1,  null, null,
351                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
352                        get_token_display_field(r.token_name1)
353                           ||': '||r.token_value1) ||
354 		decode(r.token_name2,  null, null,
355                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
356                        get_token_display_field(r.token_name2)
357                           ||': '||r.token_value2) ||
358 		decode(r.token_name3,  null, null,
359                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
360                        get_token_display_field(r.token_name3)
361                           ||': '||r.token_value3) ||
362 		decode(r.token_name4,  null, null,
363                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
364                        get_token_display_field(r.token_name4)
365                           ||': '||r.token_value4) ||
366 		decode(r.token_name5,  null, null,
367                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
371                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
368                        get_token_display_field(r.token_name5)
369                           ||': '||r.token_value5) ||
370 		decode(r.token_name6,  null, null,
372                        get_token_display_field(r.token_name6)
373                           ||': '||r.token_value6) ||
374 		decode(r.token_name7,  null, null,
375                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
376                        get_token_display_field(r.token_name7)
377                           ||': '||r.token_value7) ||
378 		decode(r.token_name8,  null, null,
379                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
380                        get_token_display_field(r.token_name8)
381                           ||': '||r.token_value8) ||
382 		decode(r.token_name9,  null, null,
383                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
384                        get_token_display_field(r.token_name9)
385                           ||': '||r.token_value9) ||
386 		decode(r.token_name10, null, null,
387                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
388                        get_token_display_field(r.token_name10)
389                           ||': '||r.token_value10)||
390                 fnd_global.newline
391      -- Bug  4065112 starts
392                ,group_id, external_doc_ref
393      -- Bug  4065112 ends
394 
395     from	ap_invoices_interface h,
396 		ap_interface_rejections r,
397 		ap_lookup_codes lc
398     where	h.request_id = c_request_id
399     and         nvl(r.notify_vendor_flag, 'N') = 'Y'
400     and 	h.invoice_id = r.parent_id
401     and         r.parent_table = 'AP_INVOICES_INTERFACE'
402     and 	h.vendor_email_address = c_vendor_email_address
403     and		lc.lookup_code = r.reject_lookup_code
404     and		lc.lookup_type = 'REJECT CODE'
405     union all
406     select 	h.invoice_id, l.invoice_line_id,h.invoice_num, l.line_number,
407 		fnd_global.tab||fnd_global.tab||lc.description||
408 		decode(r.token_name1,  null, null,
409                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
410                        get_token_display_field(r.token_name1)
411                           ||': '||r.token_value1) ||
412 		decode(r.token_name2,  null, null,
413                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
414                        get_token_display_field(r.token_name2)
415                           ||': '||r.token_value2) ||
416 		decode(r.token_name3,  null, null,
417                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
418                        get_token_display_field(r.token_name3)
419                           ||': '||r.token_value3) ||
420 		decode(r.token_name4,  null, null,
421                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
422                        get_token_display_field(r.token_name4)
423                           ||': '||r.token_value4) ||
424 		decode(r.token_name5,  null, null,
425                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
426                        get_token_display_field(r.token_name5)
427                           ||': '||r.token_value5) ||
428 		decode(r.token_name6,  null, null,
429                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
430                        get_token_display_field(r.token_name6)
431                           ||': '||r.token_value6) ||
432 		decode(r.token_name7,  null, null,
433                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
434                        get_token_display_field(r.token_name7)
435                           ||': '||r.token_value7) ||
436 		decode(r.token_name8,  null, null,
437                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
438                        get_token_display_field(r.token_name8)
439                           ||': '||r.token_value8) ||
440 		decode(r.token_name9,  null, null,
441                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
442                        get_token_display_field(r.token_name9)
443                           ||': '||r.token_value9) ||
444 		decode(r.token_name10, null, null,
445                        fnd_global.newline||fnd_global.tab||fnd_global.tab||
446                        get_token_display_field(r.token_name10)
447                           ||': '||r.token_value10)||
448                 fnd_global.newline
449      -- Bug 4065112 starts
450      ,group_id, external_doc_ref
451      -- Bug 4065112  ends
452     from	ap_invoices_interface h,
453                 ap_invoice_lines_interface l,
454 		ap_interface_rejections r,
455 		ap_lookup_codes lc
456     where	h.request_id = c_request_id
457     and         h.invoice_id = l.invoice_id
458     and         nvl(r.notify_vendor_flag, 'N') = 'Y'
459     and 	l.invoice_line_id = r.parent_id
460     and         r.parent_table = 'AP_INVOICE_LINES_INTERFACE'
461     and 	h.vendor_email_address = c_vendor_email_address
462     and		lc.lookup_code = r.reject_lookup_code
463     and		lc.lookup_type = 'REJECT CODE'
464     order by 1, 2;
465 
466   l_vendor_email_address 	VARCHAR2(2000);
467   l_message_line  		VARCHAR2(2000);
468 
469   type email_and_message_rec is RECORD(
470     vendor_email_address	VARCHAR2(2000),
471     message			VARCHAR2(32000)
472   );
473 
474   type email_and_message_table_type
478   type message_table_type is table of VARCHAR2(4000) index by binary_integer;
475       is table of email_and_message_rec index by binary_integer;
476   l_email_and_message_table	email_and_message_table_type;
477 
479   l_message_table message_table_type;
480 
481   l_invoice_id_table ap_utilities_pkg.number_table_type;
482   l_invoice_line_id_table ap_utilities_pkg.number_table_type;
483 
484   l_message			VARCHAR2(32000);
485   l_index 			NUMBER := 0;
486   l_item_key			VARCHAR2(100);
487   l_role			VARCHAR2(100);
488   l_display_role_name		VARCHAR2(100);
489   l_temp_string			VARCHAR2(1000);
490   l_invoice_id			NUMBER;
491   l_invoice_line_id		NUMBER;
492   l_invoice_index		NUMBER := 0;
493   l_invoice_line_index		NUMBER := 0;
494   l_prev_invoice_id		NUMBER;
495   l_prev_invoice_line_id	NUMBER;
496   l_result			boolean;
497   l_invoice_number		VARCHAR2(30);
498   l_invoice_number_tmp		VARCHAR2(50);
499   l_org_id			NUMBER;
500   l_invoice_line_number		VARCHAR2(30);
501   l_invoice_line_number_tmp	NUMBER;
502   -- Bug 4065112  starts
503   l_group_id            ap_invoices_interface.group_id%TYPE;
504   l_external_doc_ref    ap_invoices_interface.external_doc_ref%TYPE;
505   l_call_3c4_invoice_id ap_invoices_interface.invoice_id%TYPE;
506   -- Bug 4065112 ends
507 
508 begin
509   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.notify_supplier(+)');
510   ap_debug_pkg.print('Y','request_id: '||to_char(p_request_id));
511   --
512   open l_invoice_number_csr;
513   fetch l_invoice_number_csr into l_invoice_number;
514   close l_invoice_number_csr;
515   --
516   open l_invoice_line_number_csr;
517   fetch l_invoice_line_number_csr into l_invoice_line_number;
518   close l_invoice_line_number_csr;
519   --
520   -- Create table of email address and message.
521   --
522   open l_email_csr(p_request_id);
523   loop
524     fetch l_email_csr into l_vendor_email_address;
525     exit when l_email_csr%notfound;
526     ap_debug_pkg.print('Y','distinct email:'||l_vendor_email_address);
527     --
528     open l_message_csr(p_request_id, l_vendor_email_address);
529     loop
530       fetch l_message_csr into l_invoice_id, l_invoice_line_id,
531             l_invoice_number_tmp, l_invoice_line_number_tmp, l_message_line,
532                                 --Bug 4065112 starts
533                                  l_group_id, l_external_doc_ref;
534                                  --Bug 4065112 ends
535 
536       exit when l_message_csr%notfound;
537       --
538       ap_debug_pkg.print('Y','invoice_num:'||l_invoice_number_tmp);
539       --
540       -- SQL is ordered by invoice_id and invoice_line_id
541       --
542       if (l_invoice_line_id is null and
543           l_invoice_id <> nvl(l_prev_invoice_id,0)) then
544         l_invoice_index := l_invoice_index + 1;
545         l_prev_invoice_id := l_invoice_id;
546         l_prev_invoice_line_id := NULL;
547         l_invoice_id_table(l_invoice_index) := l_invoice_id;
548         --
549         ap_debug_pkg.print('Y','New Invoice');
550           -- Bug 4065112: add the check condition for '3C4'
551           if ( l_vendor_email_address <> '3C4' ) then
552             l_message_line := fnd_global.newline||
553                           l_invoice_number ||l_invoice_number_tmp||
554                           fnd_global.newline||l_message_line;
555           end if;
556       end if;
557       --
558       if (l_invoice_line_id is not null and
559           l_invoice_line_id <> nvl(l_prev_invoice_line_id,0)) then
560         l_invoice_line_index := l_invoice_line_index + 1;
561         l_prev_invoice_line_id := l_invoice_line_id;
562         l_invoice_line_id_table(l_invoice_line_index) := l_invoice_line_id;
563         --
564         ap_debug_pkg.print('Y','New Invoice Line');
565 	  -- Bug 4065112: add the check condition for '3C4'
566           if ( l_vendor_email_address <> '3C4' ) then
567             l_message_line := fnd_global.newline||
568                               l_invoice_number || l_invoice_number_tmp||
569 			                  fnd_global.newline||
570 			                  l_invoice_line_number ||
571                                           l_invoice_line_number_tmp||
572 			                  fnd_global.newline||
573                               l_message_line;
574           end if;
575 
576       end if;
577       --
578       --
579       ap_debug_pkg.print('Y','invoice_id:'||to_char(l_invoice_id));
580       ap_debug_pkg.print('Y','invoice_line_id:'||to_char(l_invoice_line_id));
581         -- Bug 4065112: add the check condition for '3C4'
582         if ( l_vendor_email_address <> '3C4' ) then
583          ap_debug_pkg.print('Y','Email prepared for sending main notification');
584 
585 
586          if ( lengthb(l_message||l_message_line) > 32000) then
587           l_index := l_index + 1;
588           l_email_and_message_table(l_index).vendor_email_address
589                            := l_vendor_email_address;
590           l_email_and_message_table(l_index).message := l_message;
591           l_message := NULL;
592          else
593           l_message := l_message||l_message_line;
594          end if;
595        else
596           -- Bug 4065112 starts
597           if ( nvl(l_call_3c4_invoice_id, 0 ) <> nvl( l_invoice_id, 0)) then
598            ap_debug_pkg.print('Y','Calling API to generate reject xml message for 3C4');
599             CLN_3C3_AP_TRIGGER_PKG.TRIGGER_REJECTION(l_invoice_id,
603             l_call_3c4_invoice_id := l_invoice_id;
600                                         l_group_id,
601                                         p_request_id,
602                                         l_external_doc_ref);
604           end if;
605           -- Bug 4065112 ends
606         end if;
607 
608       --
609       --
610     end loop;
611     close l_message_csr;
612     --
613     --
614     if (l_message is not null) then
615       l_index := l_index + 1;
616       l_email_and_message_table(l_index).vendor_email_address
617                     := l_vendor_email_address;
618       l_email_and_message_table(l_index).message := l_message;
619     end if;
620     --
621     --
622     l_message := NULL;
623     --
624   end loop;
625   close l_email_csr;
626   --
627   --
628   -- Table l_email_address_table are populated as follows:
629   -- ++++++++++++++++++++++++++++++++++++++
630   -- vendor_email_address	message
631   -- ++++++++++++++++++++++++++++++++++++++
632   -- 1 [email protected]	'hello'
633   -- 2 [email protected]	'good morning'
634   -- ...
635   ---
636   -- Now start Workflow process for each element of the table
637   --
638   for i in 1..nvl(l_email_and_message_table.last, 0) loop
639     ap_debug_pkg.print('Y','index:'||to_char(i));
640     ap_debug_pkg.print('Y',
641            'size of message:'||lengthb(l_email_and_message_table(i).message));
642     ap_debug_pkg.print('Y',
643            'email:'||l_email_and_message_table(i).vendor_email_address);
644     ap_debug_pkg.print('Y',
645            l_email_and_message_table(i).message);
646     --
647     l_message_table(1) := substrb(l_email_and_message_table(i).message,
648                                   1,     4000);
649     l_message_table(2) := substrb(l_email_and_message_table(i).message,
650                                   4001,  4000);
651     l_message_table(3) := substrb(l_email_and_message_table(i).message,
652                                   8001,  4000);
653     l_message_table(4) := substrb(l_email_and_message_table(i).message,
654                                   12001, 4000);
655     l_message_table(5) := substrb(l_email_and_message_table(i).message,
656                                   16001, 4000);
657     l_message_table(6) := substrb(l_email_and_message_table(i).message,
658                                   20001, 4000);
659     l_message_table(7) := substrb(l_email_and_message_table(i).message,
660                                   24001, 4000);
661     l_message_table(8) := substrb(l_email_and_message_table(i).message,
662                                   28001, 4000);
663     --
664     -- Create an Adhoc role
665     --
666     ap_debug_pkg.print('Y','Creating adhoc role(+)');
667     l_role := null;
668     l_display_role_name := null;
669     WF_DIRECTORY.createAdhocRole(role_name => l_role,
670                             role_display_name => l_display_role_name,
671                             email_address =>
672                             l_email_and_message_table(i).vendor_email_address,
673                             notification_preference => 'MAILTEXT');
674     ap_debug_pkg.print('Y','Creating adhoc role(-)');
675     --
676     -- Creating a workflow process
677     --
678     select ap_p2p_inbound_notification_s.nextval into l_item_key from dual;
679     ap_debug_pkg.print('Y','Creating a workflow process(+)');
680     WF_ENGINE.createProcess('P2P',l_item_key,
681                             'PROCESS_FOR_NOTIFICATION');
682 
683     ap_debug_pkg.print('Y','Creating a workflow process(-)');
684     --
685     -- Initializing attributes
686     --
687     ap_debug_pkg.print('Y','Initializing Mail Subject (+)');
688     FND_MESSAGE.SET_NAME('SQLAP', 'AP_XML_WF_SUPPLIER_EMAIL_SUBJ');
689     l_temp_string := FND_MESSAGE.GET;
690 
691     select NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO'),1,1),' ',
692            NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
693     into   l_org_id
694     from   dual;
695 
696     if (l_org_id is not null) then
697       l_temp_string := l_temp_string ||
698                       '('||mo_utils.get_ledger_name(l_org_id) ||')';
699     end if;
700 
701     ap_debug_pkg.print('Y','subject:'||l_temp_string);
702     WF_ENGINE.setItemAttrText('P2P',l_item_key, 'MAIL_SUBJECT',l_temp_string);
703     ap_debug_pkg.print('Y','Initializing Mail Subject (-)');
704 
705     ap_debug_pkg.print('Y','Initializing Mail Header (+)');
706     FND_MESSAGE.SET_NAME('SQLAP', 'AP_XML_WF_SUPPLIER_EMAIL_CONT');
707     l_temp_string := FND_MESSAGE.GET;
708     ap_debug_pkg.print('Y','mail header:'||l_temp_string);
709     WF_ENGINE.setItemAttrText('P2P',l_item_key, 'MAIL_HEADER',l_temp_string);
710     ap_debug_pkg.print('Y','Initializing Mail Header (-)');
711 
712     for j in 1..8 loop
713       ap_debug_pkg.print('Y','Initializing Mail Content (+)');
714       WF_ENGINE.setItemAttrText('P2P',l_item_key,'MAIL_CONTENT'||
715                                  to_char(j),l_message_table(j));
716       ap_debug_pkg.print('Y','Initializing Mail Content (-)');
717     end loop;
718 
719     ap_debug_pkg.print('Y','Initializing Adhoc Role(+)');
720     WF_ENGINE.setItemAttrText('P2P',l_item_key,'ADHOC_ROLE',l_role);
721     ap_debug_pkg.print('Y','Initializing Adhoc Role(-)');
722 
723     --
727     WF_ENGINE.startProcess('P2P', l_item_key);
724     -- Starting the process
725     --
726     ap_debug_pkg.print('Y','Starting the process(+)');
728     ap_debug_pkg.print('Y','Starting the process(-)');
729   end loop;
730   --
731   --
732   ap_debug_pkg.print('Y','Deleting from the interfaces(+)');
733   ap_debug_pkg.print('Y','# of element in l_invoice_id_table:'||
734                          to_char(nvl(l_invoice_id_table.last,0)));
735   ap_debug_pkg.print('Y','# of element in l_invoice_line_id_table:'||
736                          to_char(nvl(l_invoice_line_id_table.last,0)));
737   l_result := ap_utilities_pkg.delete_invoice_from_interface(
738                   l_invoice_id_table,
739                   l_invoice_line_id_table,
740                   'AP_XML_INVOICE_INBOUND_PKG.notify_supplier');
741   ap_debug_pkg.print('Y','Deleting from the interfaces(-)');
742   --
743   --
744   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.notify_supplier(-)');
745 end;
746 
747 /*===========================================================================+
748  | PROCEDURE                                                                 |
749  |    notify_recipient                                                       |
750  |                                                                           |
751  | DESCRIPTION                                                               |
752  |   Called by the request set                                               |
753  +===========================================================================*/
754 procedure notify_recipient(p_errbuf out NOCOPY VARCHAR2, p_return_code out NOCOPY VARCHAR2)
755 is
756 
757   cursor parent_req is
758     select PRIORITY_REQUEST_ID
759     from   fnd_concurrent_requests
760     where  request_id = FND_GLOBAL.CONC_REQUEST_ID;
761 
762   l_priority_request_id number;
763 
764   cursor req_set is
765     select fnd_global.tab||PROGRAM ||' with Request ID: '||
766            to_char(request_id)||fnd_global.newline
767     from   fnd_conc_req_summary_v
768     where  priority_request_id = l_priority_request_id
769     order by request_id;
770 
771   l_request varchar2(1000);
772   l_requests varchar2(3000);
773   l_email_address varchar2(200);
774 
775 begin
776   ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.notify_recipient(+)');
777   fnd_profile.get('AP_NOTIFICATION_EMAIL', l_email_address);
778   ap_debug_pkg.print('Y','email address: '||l_email_address);
779 
780   open parent_req;
781   fetch parent_req into l_priority_request_id;
782   close parent_req;
783 
784   open req_set;
785   loop
786     fetch req_set into l_request;
787     exit when req_set%notfound;
788     if l_request is not null then
789       l_requests := l_requests || l_request;
790     end if;
791   end loop;
792   close req_set;
793 
794   if l_requests is not null then
795     l_requests := 'The following requests are submitted:'||
796                   fnd_global.newline||fnd_global.newline||
797                   l_requests||fnd_global.newline||
798                   'Please check the result for each request.';
799 
800     ap_debug_pkg.print('Y','l_requests:'||l_requests);
801     ap_debug_pkg.print('Y','sending email +');
802     send_email('P2P Inbound Process Request Set has been submitted',
803                          l_requests,
804                          l_email_address);
805     ap_debug_pkg.print('Y','sending email -');
806   end if;
807 
808   p_return_code := '0';
809   ap_debug_pkg.print('Y','ap_xml_invoice_inbound_pkg.notify_recipient(-)');
810 exception
811   when others then
812     ap_debug_pkg.print('Y',
813                  'ap_xml_invoice_inbound_pkg.notify_recipient EXCEPTION(-)');
814     p_return_code := '2';
815 
816 end notify_recipient;
817 
818 /*===========================================================================+
819  | PROCEDURE                                                                 |
820  |    set_taxable_flag                                                       |
821  |                                                                           |
822  | DESCRIPTION                                                               |
823  |    bug 2524551                                                            |
824  |    For all invoices in this group,                                        |
825  |      If common tax line (with no line_group_number) exists,               |
826  |        set taxable_flag='Y' on all item lines                             |
827  +===========================================================================*/
828 procedure set_taxable_flag(p_group_id in VARCHAR2) as
829 begin
830 
831   -- if any tax line is not affiliated with a particular item line
832   -- affiliate  it with all item lines
833   UPDATE  ap_invoice_lines_interface
834   SET     taxable_flag =  'Y'
835   WHERE   line_type_lookup_code = 'ITEM'
836   AND     invoice_id IN
837             (SELECT   h.invoice_id
838              FROM     ap_invoices_interface h, ap_invoice_lines_interface l
839              WHERE    h.invoice_id = l.invoice_id
840              AND      h.source = 'XML GATEWAY'
841              AND      h.group_id = p_group_id
842              AND      l.line_type_lookup_code = 'TAX'
843              AND      l.line_group_number is null
844              GROUP BY h.invoice_id);
845 
846 end set_taxable_flag;
847 
848 /*===========================================================================+
852  | DESCRIPTION                                                               |
849  | PROCEDURE                                                                 |
850  |    set_taxable_flag2                                                      |
851  |                                                                           |
853  |    bug 2524551                                                            |
854  |    Used in the map.                                                       |
855  |    Set taxable_flag='Y' for the given item line                           |
856  +===========================================================================*/
857 procedure set_taxable_flag2(p_item_line_id in number) as
858 begin
859 
860   UPDATE  ap_invoice_lines_interface
861   SET     taxable_flag =  'Y'
862   WHERE   invoice_line_id = p_item_line_id;
863 
864 end set_taxable_flag2;
865 
866 /*===========================================================================+
867  | PROCEDURE                                                                 |
868  |    correct_charge_type                                                    |
869  |                                                                           |
870  | DESCRIPTION                                                               |
871  |    bug 2524551                                                            |
872  |    changes p_charge_type to either FREIGHT or MISCELLANEOUS               |
873  +===========================================================================*/
874 procedure correct_charge_type(p_charge_type in out NOCOPY VARCHAR2) as
875 begin
876 
877   IF ( upper( trim(' ' from nvl(p_charge_type,'DUMMY') ) ) = 'FREIGHT' ) THEN
878     p_charge_type := 'FREIGHT';
879   ELSE
880     p_charge_type := 'MISCELLANEOUS';
881   END IF;
882 
883 end correct_charge_type;
884 
885 /*===========================================================================+
886  | PROCEDURE                                                                 |
887  |    correct_charge_ccid                                                    |
888  |                                                                           |
889  | DESCRIPTION                                                               |
890  |    bug 2524551                                                            |
891  |    Do charge account derivation                                           |
892  +===========================================================================*/
893 procedure correct_charge_ccid(p_group_id in VARCHAR2) as
894 
895   l_org_id           number;
896   l_freight_ccid     number;
897   l_misc_ccid        number;
898 
899   cursor org_csr is
900     SELECT  distinct ORG_ID
901     FROM    AP_INVOICES_INTERFACE
902     WHERE   GROUP_ID = p_group_id
903     AND     SOURCE = 'XML GATEWAY';
904 
905 begin
906   ap_debug_pkg.print('Y',
907                      'AP_XML_INVOICE_INBOUND_PKG.correct_charge_ccid(+)');
908 
909   open org_csr;
910   loop
911     fetch org_csr into l_org_id;
912     exit when org_csr%notfound or org_csr%notfound is null;
913 
914     -- reset loop variables
915     l_freight_ccid := NULL;
916     l_misc_ccid    := NULL;
917 
918     -- fetch setup info
919     SELECT FREIGHT_CODE_COMBINATION_ID
920     INTO   l_freight_ccid
921     FROM   AP_SYSTEM_PARAMETERS_ALL
922     WHERE  NVL(ORG_ID,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
923              ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))) =
924            NVL(l_org_id,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
925              ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')));
926 
927     SELECT MISC_CHARGE_CCID
928     INTO   l_misc_ccid
929     FROM   FINANCIALS_SYSTEM_PARAMS_ALL
930     WHERE  NVL(ORG_ID,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
931              ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))) =
932            NVL(l_org_id,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
933              ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')));
934 
935     -- update freight and misc lines
936     UPDATE AP_INVOICE_LINES_INTERFACE
937     SET    DIST_CODE_COMBINATION_ID =
938              decode(LINE_TYPE_LOOKUP_CODE,'FREIGHT',l_freight_ccid,
939                                           'MISCELLANEOUS',l_misc_ccid),
940            PRORATE_ACROSS_FLAG =
941              decode(LINE_TYPE_LOOKUP_CODE,'FREIGHT',decode(l_freight_ccid,'','Y','N'),
942                                           'MISCELLANEOUS',decode(l_misc_ccid,'','Y','N'))
943     WHERE  INVOICE_ID in
944            (SELECT INVOICE_ID
945             FROM   AP_INVOICES_INTERFACE
946             WHERE  GROUP_ID = p_group_id
947             AND    NVL(ORG_ID,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
948                      ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))) =
949                    NVL(l_org_id,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
950                      ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')))
951             AND    SOURCE = 'XML GATEWAY')
952     AND    LINE_TYPE_LOOKUP_CODE in ('FREIGHT','MISCELLANEOUS');
953 
954   end loop; -- finish looping through org_id
955   close org_csr;
956 
957   ap_debug_pkg.print('Y',
958                      'AP_XML_INVOICE_INBOUND_PKG.correct_charge_ccid(-)');
959 end correct_charge_ccid;
960 
964  |                                                                           |
961 /*===========================================================================+
962  | PROCEDURE                                                                 |
963  |    after_map                                                              |
965  | DESCRIPTION                                                               |
966  |    bug 2524551                                                            |
967  |    Used at the end of map                                                 |
968  +===========================================================================*/
969 procedure after_map(p_group_id in VARCHAR2) as
970 begin
971   correct_charge_ccid(p_group_id);
972   set_taxable_flag(p_group_id);
973 end after_map;
974 
975 
976 END AP_XML_INVOICE_INBOUND_PKG;