[Home] [Help]
PACKAGE BODY: APPS.AP_XML_INVOICE_INBOUND_PKG
Source
4 function get_token_display_field(p_lookup_code in VARCHAR2) return VARCHAR2 as
1 PACKAGE BODY AP_XML_INVOICE_INBOUND_PKG as
2 /* $Header: apxmlinb.pls 120.2.12010000.2 2009/10/13 22:23:14 gagrawal ship $ */
3
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 /*===========================================================================+
68 | DESCRIPTION |
65 | PROCEDURE |
66 | change_case |
67 | |
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 |
195 | Called by XMLGateway's post process trigger. This starts open interface.|
192 | start_open_interface |
193 | |
194 | DESCRIPTION |
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||': '
333
330 from ap_lookup_codes
331 where lookup_type = 'XML TOKEN NAME' and
332 lookup_code = 'INVOICE LINE NUMBER';
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||
368 get_token_display_field(r.token_name5)
369 ||': '||r.token_value5) ||
370 decode(r.token_name6, null, null,
371 fnd_global.newline||fnd_global.tab||fnd_global.tab||
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||
441 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,
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
475 is table of email_and_message_rec index by binary_integer;
476 l_email_and_message_table email_and_message_table_type;
477
478 type message_table_type is table of VARCHAR2(4000) index by binary_integer;
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 ||
574 end if;
571 l_invoice_line_number_tmp||
572 fnd_global.newline||
573 l_message_line;
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,
600 l_group_id,
601 p_request_id,
602 l_external_doc_ref);
603 l_call_3c4_invoice_id := l_invoice_id;
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;
682
679 ap_debug_pkg.print('Y','Creating a workflow process(+)');
680 WF_ENGINE.createProcess('P2P',l_item_key,
681 'PROCESS_FOR_NOTIFICATION');
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
692 -- Commented below code for 9007991
693 -- select NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO'),1,1),' ',
694 -- NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
695 -- into l_org_id
696 -- from dual;
697
698 -- if (l_org_id is not null) then
699 -- l_temp_string := l_temp_string ||
700 -- '('||mo_utils.get_ledger_name(l_org_id) ||')';
701 -- end if;
702
703 ap_debug_pkg.print('Y','subject:'||l_temp_string);
704 WF_ENGINE.setItemAttrText('P2P',l_item_key, 'MAIL_SUBJECT',l_temp_string);
705 ap_debug_pkg.print('Y','Initializing Mail Subject (-)');
706
707 ap_debug_pkg.print('Y','Initializing Mail Header (+)');
708 FND_MESSAGE.SET_NAME('SQLAP', 'AP_XML_WF_SUPPLIER_EMAIL_CONT');
709 l_temp_string := FND_MESSAGE.GET;
710 ap_debug_pkg.print('Y','mail header:'||l_temp_string);
711 WF_ENGINE.setItemAttrText('P2P',l_item_key, 'MAIL_HEADER',l_temp_string);
712 ap_debug_pkg.print('Y','Initializing Mail Header (-)');
713
714 for j in 1..8 loop
715 ap_debug_pkg.print('Y','Initializing Mail Content (+)');
716 WF_ENGINE.setItemAttrText('P2P',l_item_key,'MAIL_CONTENT'||
717 to_char(j),l_message_table(j));
718 ap_debug_pkg.print('Y','Initializing Mail Content (-)');
719 end loop;
720
721 ap_debug_pkg.print('Y','Initializing Adhoc Role(+)');
722 WF_ENGINE.setItemAttrText('P2P',l_item_key,'ADHOC_ROLE',l_role);
723 ap_debug_pkg.print('Y','Initializing Adhoc Role(-)');
724
725 --
726 -- Starting the process
727 --
728 ap_debug_pkg.print('Y','Starting the process(+)');
729 WF_ENGINE.startProcess('P2P', l_item_key);
730 ap_debug_pkg.print('Y','Starting the process(-)');
731 end loop;
732 --
733 --
734 ap_debug_pkg.print('Y','Deleting from the interfaces(+)');
735 ap_debug_pkg.print('Y','# of element in l_invoice_id_table:'||
736 to_char(nvl(l_invoice_id_table.last,0)));
737 ap_debug_pkg.print('Y','# of element in l_invoice_line_id_table:'||
738 to_char(nvl(l_invoice_line_id_table.last,0)));
739 l_result := ap_utilities_pkg.delete_invoice_from_interface(
740 l_invoice_id_table,
741 l_invoice_line_id_table,
742 'AP_XML_INVOICE_INBOUND_PKG.notify_supplier');
743 ap_debug_pkg.print('Y','Deleting from the interfaces(-)');
744 --
745 --
746 ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.notify_supplier(-)');
747 end;
748
749 /*===========================================================================+
753 | DESCRIPTION |
750 | PROCEDURE |
751 | notify_recipient |
752 | |
754 | Called by the request set |
755 +===========================================================================*/
756 procedure notify_recipient(p_errbuf out NOCOPY VARCHAR2, p_return_code out NOCOPY VARCHAR2)
757 is
758
759 cursor parent_req is
760 select PRIORITY_REQUEST_ID
761 from fnd_concurrent_requests
762 where request_id = FND_GLOBAL.CONC_REQUEST_ID;
763
764 l_priority_request_id number;
765
766 cursor req_set is
767 select fnd_global.tab||PROGRAM ||' with Request ID: '||
768 to_char(request_id)||fnd_global.newline
769 from fnd_conc_req_summary_v
770 where priority_request_id = l_priority_request_id
771 order by request_id;
772
773 l_request varchar2(1000);
774 l_requests varchar2(3000);
775 l_email_address varchar2(200);
776
777 begin
778 ap_debug_pkg.print('Y','AP_XML_INVOICE_INBOUND_PKG.notify_recipient(+)');
779 fnd_profile.get('AP_NOTIFICATION_EMAIL', l_email_address);
780 ap_debug_pkg.print('Y','email address: '||l_email_address);
781
782 open parent_req;
783 fetch parent_req into l_priority_request_id;
784 close parent_req;
785
786 open req_set;
787 loop
791 l_requests := l_requests || l_request;
788 fetch req_set into l_request;
789 exit when req_set%notfound;
790 if l_request is not null then
792 end if;
793 end loop;
794 close req_set;
795
796 if l_requests is not null then
797 l_requests := 'The following requests are submitted:'||
798 fnd_global.newline||fnd_global.newline||
799 l_requests||fnd_global.newline||
800 'Please check the result for each request.';
801
802 ap_debug_pkg.print('Y','l_requests:'||l_requests);
803 ap_debug_pkg.print('Y','sending email +');
804 send_email('P2P Inbound Process Request Set has been submitted',
805 l_requests,
806 l_email_address);
807 ap_debug_pkg.print('Y','sending email -');
808 end if;
809
810 p_return_code := '0';
811 ap_debug_pkg.print('Y','ap_xml_invoice_inbound_pkg.notify_recipient(-)');
812 exception
813 when others then
814 ap_debug_pkg.print('Y',
815 'ap_xml_invoice_inbound_pkg.notify_recipient EXCEPTION(-)');
816 p_return_code := '2';
817
818 end notify_recipient;
819
820 /*===========================================================================+
821 | PROCEDURE |
822 | set_taxable_flag |
823 | |
824 | DESCRIPTION |
825 | bug 2524551 |
826 | For all invoices in this group, |
827 | If common tax line (with no line_group_number) exists, |
828 | set taxable_flag='Y' on all item lines |
829 +===========================================================================*/
830 procedure set_taxable_flag(p_group_id in VARCHAR2) as
831 begin
832
833 -- if any tax line is not affiliated with a particular item line
834 -- affiliate it with all item lines
835 UPDATE ap_invoice_lines_interface
836 SET taxable_flag = 'Y'
837 WHERE line_type_lookup_code = 'ITEM'
838 AND invoice_id IN
839 (SELECT h.invoice_id
840 FROM ap_invoices_interface h, ap_invoice_lines_interface l
841 WHERE h.invoice_id = l.invoice_id
842 AND h.source = 'XML GATEWAY'
843 AND h.group_id = p_group_id
844 AND l.line_type_lookup_code = 'TAX'
845 AND l.line_group_number is null
846 GROUP BY h.invoice_id);
847
848 end set_taxable_flag;
849
850 /*===========================================================================+
851 | PROCEDURE |
852 | set_taxable_flag2 |
853 | |
854 | DESCRIPTION |
855 | bug 2524551 |
856 | Used in the map. |
857 | Set taxable_flag='Y' for the given item line |
858 +===========================================================================*/
859 procedure set_taxable_flag2(p_item_line_id in number) as
860 begin
861
862 UPDATE ap_invoice_lines_interface
863 SET taxable_flag = 'Y'
864 WHERE invoice_line_id = p_item_line_id;
865
866 end set_taxable_flag2;
867
868 /*===========================================================================+
869 | PROCEDURE |
870 | correct_charge_type |
871 | |
872 | DESCRIPTION |
873 | bug 2524551 |
874 | changes p_charge_type to either FREIGHT or MISCELLANEOUS |
875 +===========================================================================*/
876 procedure correct_charge_type(p_charge_type in out NOCOPY VARCHAR2) as
877 begin
878
879 IF ( upper( trim(' ' from nvl(p_charge_type,'DUMMY') ) ) = 'FREIGHT' ) THEN
880 p_charge_type := 'FREIGHT';
881 ELSE
882 p_charge_type := 'MISCELLANEOUS';
883 END IF;
884
885 end correct_charge_type;
886
887 /*===========================================================================+
888 | PROCEDURE |
889 | correct_charge_ccid |
890 | |
891 | DESCRIPTION |
892 | bug 2524551 |
896
893 | Do charge account derivation |
894 +===========================================================================*/
895 procedure correct_charge_ccid(p_group_id in VARCHAR2) as
897 l_org_id number;
898 l_freight_ccid number;
899 l_misc_ccid number;
900
901 cursor org_csr is
902 SELECT distinct ORG_ID
903 FROM AP_INVOICES_INTERFACE
904 WHERE GROUP_ID = p_group_id
905 AND SOURCE = 'XML GATEWAY';
906
907 begin
908 ap_debug_pkg.print('Y',
909 'AP_XML_INVOICE_INBOUND_PKG.correct_charge_ccid(+)');
910
911 open org_csr;
912 loop
913 fetch org_csr into l_org_id;
914 exit when org_csr%notfound or org_csr%notfound is null;
915
916 -- reset loop variables
917 l_freight_ccid := NULL;
918 l_misc_ccid := NULL;
919
920 -- fetch setup info
921 SELECT FREIGHT_CODE_COMBINATION_ID
922 INTO l_freight_ccid
923 FROM AP_SYSTEM_PARAMETERS_ALL
924 WHERE NVL(ORG_ID,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
925 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))) =
926 NVL(l_org_id,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
927 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')));
928
929 SELECT MISC_CHARGE_CCID
930 INTO l_misc_ccid
931 FROM FINANCIALS_SYSTEM_PARAMS_ALL
932 WHERE NVL(ORG_ID,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
933 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))) =
934 NVL(l_org_id,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
935 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')));
936
937 -- update freight and misc lines
938 UPDATE AP_INVOICE_LINES_INTERFACE
939 SET DIST_CODE_COMBINATION_ID =
940 decode(LINE_TYPE_LOOKUP_CODE,'FREIGHT',l_freight_ccid,
941 'MISCELLANEOUS',l_misc_ccid),
942 PRORATE_ACROSS_FLAG =
943 decode(LINE_TYPE_LOOKUP_CODE,'FREIGHT',decode(l_freight_ccid,'','Y','N'),
944 'MISCELLANEOUS',decode(l_misc_ccid,'','Y','N'))
945 WHERE INVOICE_ID in
946 (SELECT INVOICE_ID
947 FROM AP_INVOICES_INTERFACE
948 WHERE GROUP_ID = p_group_id
949 AND NVL(ORG_ID,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
950 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))) =
951 NVL(l_org_id,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
952 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')))
953 AND SOURCE = 'XML GATEWAY')
954 AND LINE_TYPE_LOOKUP_CODE in ('FREIGHT','MISCELLANEOUS');
955
956 end loop; -- finish looping through org_id
957 close org_csr;
958
959 ap_debug_pkg.print('Y',
960 'AP_XML_INVOICE_INBOUND_PKG.correct_charge_ccid(-)');
961 end correct_charge_ccid;
962
963 /*===========================================================================+
964 | PROCEDURE |
965 | after_map |
966 | |
967 | DESCRIPTION |
968 | bug 2524551 |
969 | Used at the end of map |
970 +===========================================================================*/
971 procedure after_map(p_group_id in VARCHAR2) as
972 begin
973 correct_charge_ccid(p_group_id);
974 set_taxable_flag(p_group_id);
975 end after_map;
976
977
978 END AP_XML_INVOICE_INBOUND_PKG;