[Home] [Help]
PACKAGE BODY: APPS.JG_RX_IR_PKG
Source
1 PACKAGE BODY JG_RX_IR_PKG as
2 /* $Header: jgrxpirb.pls 120.20 2011/04/29 07:02:48 mkandula ship $ */
3 procedure ap_rx_invoice_run (
4 errbuf out nocopy varchar2,
5 retcode out nocopy number,
6 p_request_id in number,
7 p_login_id in number,
8 p_reporting_level in varchar2,
9 p_reporting_entity_id in number,
10 p_set_of_book_id in number,
11 p_chart_of_acct_id in number,
12 p_line_inv in varchar2,
13 p_acct_date_min in date,
14 p_acct_date_max in date,
15 p_batch_id in number,
16 p_invoice_type in varchar2,
17 p_entry_person_id in number,
18 p_doc_sequence_id in number,
19 p_doc_sequence_value_min in number,
20 p_doc_sequence_value_max in number,
21 p_supplier_min in varchar2,
22 p_supplier_max in varchar2,
23 p_liability_min in varchar2,
24 p_liability_max in varchar2,
25 p_dist_acct_min in varchar2,
26 p_dist_acct_max in varchar2,
27 p_inv_currency_code in varchar2,
28 p_dist_amount_min in number,
29 p_dist_amount_max in number,
30 p_entered_date_min in date,
31 p_entered_date_max in date,
32 p_cancelled_inv in varchar2,
33 p_unapproved_inv in varchar2
34 ) is
35
36 /* Dummy parameters */
37 h_cancelled_inv BOOLEAN;
38 h_unapproved_inv BOOLEAN;
39 begin
40
41 h_cancelled_inv := FALSE;
42 h_unapproved_inv := FALSE;
43
44 if nvl(p_cancelled_inv,'N') = 'Y' then
45 h_cancelled_inv := TRUE;
46 end if;
47
48 if nvl(p_unapproved_inv,'N') = 'Y' then
49 h_unapproved_inv := TRUE;
50 end if;
51
52 -- Call main procedure
53 JG_RX_IR_PKG.AP_RX_INVOICE_RUN (
54 errbuf => errbuf,
55 retcode => retcode,
56 p_request_id => p_request_id,
57 p_login_id => p_login_id,
58 p_reporting_level => p_reporting_level,
59 p_reporting_entity_id => p_reporting_entity_id,
60 p_set_of_book_id => p_set_of_book_id,
61 p_chart_of_acct_id => p_chart_of_acct_id,
62 p_line_inv => p_line_inv,
63 p_acct_date_min => p_acct_date_min,
64 p_acct_date_max => p_acct_date_max,
65 p_batch_id => p_batch_id,
66 p_invoice_type => p_invoice_type,
67 p_entry_person_id => p_entry_person_id,
68 p_doc_sequence_id => p_doc_sequence_id,
69 p_doc_sequence_value_min => p_doc_sequence_value_min,
70 p_doc_sequence_value_max => p_doc_sequence_value_max,
71 p_supplier_min => p_supplier_min,
72 p_supplier_max => p_supplier_max,
73 p_liability_min => p_liability_min,
74 p_liability_max => p_liability_max,
75 p_dist_acct_min => p_dist_acct_min,
76 p_dist_acct_max => p_dist_acct_max,
77 p_inv_currency_code => p_inv_currency_code,
78 p_dist_amount_min => p_dist_amount_min,
79 p_dist_amount_max => p_dist_amount_max,
80 p_entered_date_min => p_entered_date_min,
81 p_entered_date_max => p_entered_date_max,
82 p_cancelled_inv => h_cancelled_inv,
83 p_unapproved_inv => h_unapproved_inv
84 );
85
86
87 end;
88
89 procedure ap_rx_invoice_run (
90 errbuf out nocopy varchar2,
91 retcode out nocopy number,
92 p_request_id in number,
93 p_login_id in number,
94 p_reporting_level in varchar2,
95 p_reporting_entity_id in number,
96 p_set_of_book_id in number,
97 p_chart_of_acct_id in number,
98 p_line_inv in varchar2,
99 p_acct_date_min in date,
100 p_acct_date_max in date,
101 p_batch_id in number,
102 p_invoice_type in varchar2,
103 p_entry_person_id in number,
104 p_doc_sequence_id in number,
105 p_doc_sequence_value_min in number,
106 p_doc_sequence_value_max in number,
107 p_supplier_min in varchar2,
108 p_supplier_max in varchar2,
109 p_liability_min in varchar2,
110 p_liability_max in varchar2,
111 p_dist_acct_min in varchar2,
112 p_dist_acct_max in varchar2,
113 p_inv_currency_code in varchar2,
114 p_dist_amount_min in number,
115 p_dist_amount_max in number,
116 p_entered_date_min in date,
117 p_entered_date_max in date,
118 p_cancelled_inv in boolean,
119 p_unapproved_inv in boolean
120 ) is
121
122 /* Dummy parameters */
123 l_start_date date;
124 l_end_date date;
125
126
127 /*Dynamic SQL */
128 v_MainCursor number;
129 v_MainReturn number;
130 v_MainFetch number;
131
132 l_main_sql varchar2(10000);
133 l_acct_date_where varchar2(100);
134 l_entry_person_where varchar2(100);
135 l_inv_type_where varchar2(100);
136 l_batch_id_where varchar2(100);
137 l_doc_seq_id_where varchar2(100);
138 l_doc_seq_value_where varchar2(100);
139 l_supplier_where varchar2(300);
140 l_liability_range_where varchar2(1000);
141 l_dist_acct_range_where varchar2(1000);
142 l_inv_currency_code_where varchar2(100);
143 l_dist_amount_range_where varchar2(100);
144 l_entered_date_where varchar2(100);
145 l_cancelled_where varchar2(100);
146 l_unapproved_inv_where varchar2(100);
147 l_line_inv_where varchar2(30000);
148
149 /* Fetched Variables */
150 h_invoice_id number;
151 h_liability_ccid number;
152 h_invoice_type varchar2(25);
153 h_inv_dist_id number;
154 h_line_number number;
155 h_line_type varchar2(25);
156 h_dist_ccid number;
157
158 /* Added for AP Invoice Line project */
159 h_dist_number number;
160 h_dist_type varchar2(25);
161 h_dist_acct_date date;
162
163 h_liability_acct varchar2(2000);
164 h_liability_desc varchar2(2000);
165 h_liability_natacct varchar2(2000);
166 h_liability_natdesc varchar2(2000);
167 h_dist_acct varchar2(2000);
168 h_dist_desc varchar2(2000);
169 h_dist_natacct varchar2(2000);
170 h_dist_natdesc varchar2(2000);
171
172 h_currency_code varchar2(15);
173 h_book_short_name varchar2(20);
174 /* R11.5.10 - MRC schema drop support */
175 h_mrc_sob_type_code varchar2(30);
176 l_main_from varchar2(500);
177
178 /* Sort Option */
179 h_sort_by_alternate VARCHAR2(1);
180
181 /* Added for MOAC change */
182 h_reporting_level VARCHAR2(30);
183 h_reporting_entity_id NUMBER;
184
185 p_inv_org_where VARCHAR2(2000);
186 p_dist_org_where VARCHAR2(2000);
187 p_line_org_where VARCHAR2(2000);
188 h_set_of_books_id NUMBER;
189 h_chart_of_acct_id NUMBER;
190
191 begin
192
193 fa_rx_util_pkg.enable_debug;
194
195
196 fa_rx_util_pkg.log('***** START JG_RX_IR_PKG.AP_RX_INVOICE_RUN *****');
197
198 /* Print debug parameters */
199 fa_rx_util_pkg.log('p_reporting_level :'||p_reporting_level);
200 fa_rx_util_pkg.log('p_reporting_entity_id :'||p_reporting_entity_id);
201 fa_rx_util_pkg.log('p_request_id :'||p_request_id);
202 fa_rx_util_pkg.log('p_login_id :'||p_login_id);
203 fa_rx_util_pkg.log('p_set_of_book_id :'||p_set_of_book_id);
204 fa_rx_util_pkg.log('p_chart_of_acct_id :'||p_chart_of_acct_id);
205 fa_rx_util_pkg.log('p_line_inv :'||p_line_inv);
206 fa_rx_util_pkg.log('p_acct_date_min :'||p_acct_date_min);
207 fa_rx_util_pkg.log('p_acct_date_max :'||p_acct_date_max);
208 fa_rx_util_pkg.log('p_batch_id :'||p_batch_id);
209 fa_rx_util_pkg.log('p_invoice_type :'||p_invoice_type);
210 fa_rx_util_pkg.log('p_entry_person :'||p_entry_person_id);
211 fa_rx_util_pkg.log('p_doc_sequence_name :'||p_doc_sequence_id);
212 fa_rx_util_pkg.log('p_doc_sequence_value_min :'||p_doc_sequence_value_min);
213 fa_rx_util_pkg.log('p_doc_sequence_value_max :'||p_doc_sequence_value_max);
214 fa_rx_util_pkg.log('p_supplier_min :'||p_supplier_min);
215 fa_rx_util_pkg.log('p_supplier_max :'||p_supplier_max);
216 fa_rx_util_pkg.log('p_liability_min :'||p_liability_min);
217 fa_rx_util_pkg.log('p_liability_max :'||p_liability_max);
218 fa_rx_util_pkg.log('p_dist_acct_min :'||p_dist_acct_min);
219 fa_rx_util_pkg.log('p_dist_acct_max :'||p_dist_acct_max);
220 fa_rx_util_pkg.log('p_dist_amount_min :'||p_dist_amount_min);
221 fa_rx_util_pkg.log('p_dist_amount_max :'||p_dist_amount_max);
222 fa_rx_util_pkg.log('p_entered_date_min :'||p_entered_date_min);
223 fa_rx_util_pkg.log('p_entered_date_max :'||p_entered_date_max);
224
225 /* Added for MOAC change */
226 if p_reporting_level is null then
227 h_reporting_level := '3000'; -- OU level
228 --Bug 5591940
229 -- h_reporting_entity_id := fnd_profile.value('DEFAULT_ORG_ID'); -- Set default OU.
230 h_reporting_entity_id := MO_GLOBAL.get_current_org_id;
231
232 else
233 h_reporting_level := p_reporting_level;
234 if p_reporting_entity_id is null then
235 h_reporting_level := '3000';
236 --Bug 5591940
237 -- h_reporting_entity_id := fnd_profile.value('DEFAULT_ORG_ID'); -- Set default OU
238 h_reporting_entity_id := MO_GLOBAL.get_current_org_id;
239 else
240 h_reporting_entity_id := p_reporting_entity_id;
241 end if;
242 end if;
243
244 --* Call XLA_MO_REPORTING_API
245 --* Initialize
246 XLA_MO_REPORTING_API.INITIALIZE(p_reporting_level => h_reporting_level,
247 p_reporting_entity_id => h_reporting_entity_id);
248
249 --* Get Precidcate for following tables.
250 --* AP_INVOICES_ALL (Alias = INV)
251 p_inv_org_where := XLA_MO_REPORTING_API.GET_PREDICATE(p_alias => 'AP_INV');
252 --* AP_INVOICE_DISTRIBUTIONS_ALL (Alias = DIST)
253 p_dist_org_where := XLA_MO_REPORTING_API.GET_PREDICATE(p_alias => 'DIST');
254 --* AP_INVOICE_LINES_ALL (Alias = INV_LINE)
255 p_line_org_where := XLA_MO_REPORTING_API.GET_PREDICATE(p_alias => 'INV_LINE');
256
257 fa_rx_util_pkg.debug('p_inv_org_where :'||p_inv_org_where);
258 fa_rx_util_pkg.debug('p_dist_org_where :'||p_dist_org_where);
259 fa_rx_util_pkg.debug('p_line_org_where :'||p_line_org_where);
260
261 -- Bug 11700123 -- Start
262 -- Get set_of_books_id value
263 if p_reporting_level = 1000 then
264 h_set_of_books_id := p_reporting_entity_id;
265 elsif p_reporting_level = 2000 then
266 -- Get the ledger id from legal entity id using below query.
267 select distinct primary_ledger_id
268 into h_set_of_books_id
269 from GL_LEDGER_LE_V
270 where legal_entity_id = p_reporting_entity_id;
271 elsif p_reporting_level = 3000 then
272 -- Get set_of_books_id for the given Org_id
273 select set_of_books_id into h_set_of_books_id
274 from hr_operating_units
275 where organization_id = p_reporting_entity_id;
276 end if;
277
278 fa_rx_util_pkg.debug('h_set_of_books_id :'||h_set_of_books_id);
279
280 -- Bug 11700123 -- End
281
282 /*Get Functoinal currncy code */
283 Select currency_code,
284 short_name,
285 alc_ledger_type_code,
286 chart_of_accounts_id
287 into h_currency_code,
288 h_book_short_name,
289 h_mrc_sob_type_code,
290 h_chart_of_acct_id
291 From GL_LEDGERS
292 where ledger_id = h_set_of_books_id;
293
294 fa_rx_util_pkg.debug('h_currency_code :'||h_currency_code);
295 fa_rx_util_pkg.debug('h_book_short_name :'||h_book_short_name);
296 fa_rx_util_pkg.debug('h_mrc_sob_type_code :'||h_mrc_sob_type_code);
297 fa_rx_util_pkg.debug('h_chart_of_acct_id :'||h_chart_of_acct_id);
298
299 /* Switch User Context */
300 if h_mrc_sob_type_code = 'R' then
301 fnd_client_info.set_currency_context(h_set_of_books_id);
302 end if;
303 /* End Switch User Context */
304
305 /* Get Sort By Alternate Option - Now this profile is moved to AP_SYSTEM_PARAMETERS table
306 if (nvl(fnd_profile.value('AP_SORT_BY_ALTERNATE'), 'N')= 'Y') then
307 h_sort_by_alternate :='Y';
308 ELSE
309 h_sort_by_alternate :='N';
310 END IF;
311 */
312
313 begin
314 SELECT nvl(sort_by_alternate_field,'N') --Bug 5591940
315 INTO h_sort_by_alternate
316 FROM AP_SYSTEM_PARAMETERS;
317
318 exception when others then
319 h_sort_by_alternate := 'N';
320 end;
321
322 fa_rx_util_pkg.debug('h_sort_by_alternate :'||h_sort_by_alternate);
323
324 /* ==================================================
325 Create sql statement:
326 From parameters, create sql statment
327 ================================================== */
328
329
330 /* Accounting Date range*/
331 if p_acct_date_min is null and p_acct_date_max is null then
332 l_acct_date_where := to_char(null);
333 elsif p_acct_date_min is not null and p_acct_date_max is null then
334 l_acct_date_where := ' and DIST.ACCOUNTING_DATE >= :c_acct_date_min ';
335 elsif p_acct_date_min is null and p_acct_date_max is not null then
336 l_acct_date_where := ' and DIST.ACCOUNTING_DATE >= :c_acct_date_max ';
337 else
338 l_acct_date_where :=
339 ' and DIST.ACCOUNTING_DATE between :c_acct_date_min and :c_acct_date_max ';
340 end if;
341
342 fa_rx_util_pkg.debug('l_acct_date_where :'||l_acct_date_where);
343
344 /* Entered by */
345 if p_entry_person_id is null then
346 l_entry_person_where := to_char(null);
347 else
348 l_entry_person_where := 'and AP_INV.CREATED_BY = :c_entry_person_id '; -- ||p_entry_person_id;
349 end if;
350
351 fa_rx_util_pkg.debug('l_entry_person_where :'||l_entry_person_where);
352
353 /*Invoice Type */
354 if p_invoice_type is null then
355 l_inv_type_where := to_char(null);
356 else
357 l_inv_type_where :=
358 ' and AP_INV.INVOICE_TYPE_LOOKUP_CODE= :c_invoice_type '; -- '''||p_invoice_type||'''
359
360 end if;
361
362 fa_rx_util_pkg.debug('l_inv_type_where :'||l_inv_type_where);
363
364 /* Batch ID */
365 if p_batch_id is null then
366 l_batch_id_where := to_char(null);
367 else
368 l_batch_id_where := ' and AP_INV.BATCH_ID = :c_batch_id '; -- '''||p_batch_id||'''
369
370 end if;
371
372 fa_rx_util_pkg.debug('l_batch_id_where :'||l_batch_id_where);
373
374 /* Document Sequence Id */
375 if p_doc_sequence_id is null then
376 l_doc_seq_id_where := to_char(null);
377 else
378 l_doc_seq_id_where := ' and AP_INV.DOC_SEQUENCE_ID= :c_doc_sequence_id '; --||p_doc_sequence_id;
379 end if;
380
381 fa_rx_util_pkg.debug('l_doc_seq_id_where :'||l_doc_seq_id_where);
382
383 /* Document Sequence value range */
384 if p_doc_sequence_value_min is null and p_doc_sequence_value_max is null then
385 l_doc_seq_value_where := to_char(null);
386 elsif p_doc_sequence_value_min is not null and p_doc_sequence_value_max is null then
387 l_doc_seq_value_where := ' and AP_INV.DOC_SEQUENCE_VALUE >= :c_doc_sequence_value_min ';
388 -- '||p_doc_sequence_value_min;
389 elsif p_doc_sequence_value_min is null and p_doc_sequence_value_max is not null then
390 l_doc_seq_value_where := ' and AP_INV.DOC_SEQUENCE_VALUE <= :c_doc_sequence_value_max ';
391 --'||p_doc_sequence_value_max;
392 else
393 l_doc_seq_value_where :=
394 ' and AP_INV.DOC_SEQUENCE_VALUE between :c_doc_sequence_value_min and :c_doc_sequence_value_max ';
395 -- || p_doc_sequence_value_min||' and '||p_doc_sequence_value_max;
396 end if;
397
398 fa_rx_util_pkg.debug('l_doc_seq_value_where :'||l_doc_seq_value_where);
399
400 /* Supplier name range */
401 if p_supplier_min is null and p_supplier_max is null then
402 l_supplier_where := to_char(null);
403 elsif p_supplier_min is not null and p_supplier_max is null then
404 l_supplier_where :=' and PO_PV.VENDOR_NAME >= :c_supplier_min '; -- '''||p_supplier_min||''' ';
405 elsif p_supplier_min is null and p_supplier_max is not null then
406 l_supplier_where :=' and PO_PV.VENDOR_NAME <= :c_supplier_max '; -- '''||p_supplier_max||''' ';
407 else
408 l_supplier_where :=
409 ' and PO_PV.VENDOR_NAME between :c_supplier_min and :c_supplier_max ';
410 -- '''||p_supplier_min||''' and '''||p_supplier_max||''' ';
411 end if;
412
413 fa_rx_util_pkg.debug('l_supplier_where :'||l_supplier_where);
414
415 /* Liability account range */
416 if p_liability_min is null or p_liability_max is null then
417 l_liability_range_where := to_char(null);
418 else
419 l_liability_range_where :=' and '||
420 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_acct_id,
421 'GC2','WHERE', 'ALL', 'BETWEEN',p_liability_min,p_liability_max)||' ';
422 end if;
423
424 fa_rx_util_pkg.debug('l_liability_range_where :'||l_liability_range_where);
425
426 /* Distribution account range */
427
428 if p_dist_acct_min is null or p_dist_acct_max is null then
429 l_dist_acct_range_where :=to_char(null);
430 else
431 l_dist_acct_range_where :=' and '||
432 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_acct_id,
433 'GC1','WHERE', 'ALL', 'BETWEEN',p_dist_acct_min, p_dist_acct_max) ||' ';
434 end if;
435
436 fa_rx_util_pkg.debug('l_dist_acct_range_where :'||l_dist_acct_range_where);
437
438 /* Invoice Currency Code */
439 if p_inv_currency_code is null then
440 l_inv_currency_code_where := to_char(null);
441 else
442 l_inv_currency_code_where :=
443 ' and AP_INV.INVOICE_CURRENCY_CODE = :c_inv_currency_code '; -- '''||p_inv_currency_code||''
444 end if;
445
446 fa_rx_util_pkg.debug('l_inv_currency_code_where :'||l_inv_currency_code_where);
447
448 /* Distribution mount range */
449 if p_dist_amount_min is null and p_dist_amount_max is null then
450 l_dist_amount_range_where := to_char(null);
451 elsif p_dist_amount_min is not null and p_dist_amount_max is null then
452 l_dist_amount_range_where :=
453 ' and DIST.AMOUNT >= :c_dist_amount_min '; -- '||p_dist_amount_min;
454 elsif p_dist_amount_min is null and p_dist_amount_max is not null then
455 l_dist_amount_range_where :=
456 ' and DIST.AMOUNT <= :c_dist_amount_max '; -- '||p_dist_amount_max;
457 else
458 l_dist_amount_range_where :=
459 ' and DIST.AMOUNT between :c_dist_amount_min and :c_dist_amount_max ';
460 -- '||p_dist_amount_min||' and '||p_dist_amount_max;
461 end if;
462
463 fa_rx_util_pkg.debug('l_dist_amount_range_where :'||l_dist_amount_range_where);
464
465 /* Entered date range */
466 if p_entered_date_min is null and p_entered_date_max is null then
467 l_entered_date_where := to_char(null);
468 elsif p_entered_date_min is not null and p_entered_date_max is null then
469 l_entered_date_where := ' and trunc(AP_INV.CREATION_DATE) >= :c_entered_date_min ';
470 elsif p_entered_date_min is null and p_entered_date_max is not null then
471 l_entered_date_where := ' and trunc(AP_INV.CREATION_DATE) <= :c_entered_date_max ';
472 elsif p_entered_date_min is not null and p_entered_date_max is not null then
473 l_entered_date_where := ' and trunc(AP_INV.CREATION_DATE) between :c_entered_date_min and :c_entered_date_max ';
474 end if;
475
476 fa_rx_util_pkg.debug('l_entered_date_where :'||l_entered_date_where);
477
478 /* Cancelled Invoice only */
479 if p_cancelled_inv = true then
480 l_cancelled_where := ' and AP_INV.CANCELLED_DATE is not null ';
481 else
482 l_cancelled_where := to_char(null);
483 end if;
484
485 fa_rx_util_pkg.debug('l_cancelled_where :'||l_cancelled_where);
486
487 /* Unapproved Invoice only */
488 if p_unapproved_inv = true then
489 l_unapproved_inv_where :=
490 ' and (DIST.MATCH_STATUS_FLAG =''N'' or DIST.MATCH_STATUS_FLAG is null) ';
491 else
492 l_unapproved_inv_where :=to_char(null);
493 end if;
494
495 fa_rx_util_pkg.debug('l_unapproved_inv_where :'||l_unapproved_inv_where);
496
497 /* Parameter 'Line or INVOICE', WHERE PHASE */
498
499 if p_line_inv ='I' then /* 'Line or INVOICE' = Invoice */
500
501 if l_dist_acct_range_where is null
502 and l_dist_amount_range_where is null
503 and l_acct_date_where is null
504 and l_unapproved_inv_where is null then
505
506 l_line_inv_where := to_char(null);
507 else
508 if h_mrc_sob_type_code = 'R' then
509 l_line_inv_where :=
510 ' and exists
511 (Select DIST.INVOICE_ID
512 from AP_INVOICE_DISTS_MRC_V DIST,
513 GL_CODE_COMBINATIONS GC1
514 where AP_INV.INVOICE_ID =DIST.INVOICE_ID
515 and DIST.DIST_CODE_COMBINATION_ID = GC1.CODE_COMBINATION_ID (+)'
516 ||l_dist_acct_range_where||'
517 '||l_dist_amount_range_where||'
518 '||l_acct_date_where||'
519 '||l_unapproved_inv_where||'
520 '||p_dist_org_where||'
521 '||')';
522 else
523 l_line_inv_where :=
524 ' and exists
525 (Select DIST.INVOICE_ID
526 from AP_INVOICE_DISTRIBUTIONS_ALL DIST,
527 GL_CODE_COMBINATIONS GC1
528 where AP_INV.INVOICE_ID =DIST.INVOICE_ID
529 and DIST.DIST_CODE_COMBINATION_ID = GC1.CODE_COMBINATION_ID (+)'
530 ||l_dist_acct_range_where||'
531 '||l_dist_amount_range_where||'
532 '||l_acct_date_where||'
533 '||l_unapproved_inv_where||'
534 '||p_dist_org_where||'
535 '||')';
536 end if;
537 end if;
538
539 else /* p_line_inv ='L' ('Line or INVOICE' = LINE) */
540
541 l_line_inv_where := l_dist_acct_range_where || l_dist_amount_range_where
542 ||l_acct_date_where||l_unapproved_inv_where;
543 end if;
544
545 fa_rx_util_pkg.debug('l_line_inv_where :'||l_line_inv_where);
546
547 if h_mrc_sob_type_code = 'R' then
548 l_main_from := 'AP_INVOICES_MRC_V AP_INV,
549 AP_INVOICE_LINES_MRC_V INV_LINE, -- Added for AP Invoice Line Project
550 PO_VENDORS PO_PV,
551 AP_INVOICE_DISTS_MRC_V DIST,
552 GL_CODE_COMBINATIONS GC1,
553 GL_CODE_COMBINATIONS GC2 ';
554
555 else
556 l_main_from := 'AP_INVOICES_ALL AP_INV,
557 AP_INVOICE_LINES_ALL INV_LINE, -- Added for AP Invoice Line Project
558 PO_VENDORS PO_PV,
559 AP_INVOICE_DISTRIBUTIONS_ALL DIST,
560 GL_CODE_COMBINATIONS GC1,
561 GL_CODE_COMBINATIONS GC2 ';
562 end if;
563
564
565 l_main_sql :=
566 'Select AP_INV.INVOICE_ID INVOICE_ID,
567 AP_INV.ACCTS_PAY_CODE_COMBINATION_ID LIABILITY_CCID,
568 AP_INV.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
569 DIST.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID,
570 DIST.DISTRIBUTION_LINE_NUMBER DIST_NUMBER,
571 DIST.LINE_TYPE_LOOKUP_CODE DIST_TYPE, -- Originally LINE_TYPE,
572 DIST.DIST_CODE_COMBINATION_ID DISTRIBUTION_CCID,
573 -- Added for AP Invoice Line Project
574 DIST.ACCOUNTING_DATE DIST_ACCT_DATE, -- If necessary will join XLA tables
575 INV_LINE.LINE_NUMBER LINE_NUMBER,
576 INV_LINE.LINE_TYPE_LOOKUP_CODE LINE_TYPE
577 From '|| l_main_from ||'
578 Where AP_INV.VENDOR_ID = PO_PV.VENDOR_ID
579 -- and AP_INV.INVOICE_ID = DIST.INVOICE_ID (+) -- This condition is removed
580 -- Added for AP Invoice Line Project
581 AND AP_INV.INVOICE_ID = INV_LINE.INVOICE_ID (+)
582 AND INV_LINE.INVOICE_ID = DIST.INVOICE_ID (+)
583 AND INV_LINE.LINE_NUMBER = DIST.INVOICE_LINE_NUMBER (+)
584 -- End of addition
585 and GC1.CODE_COMBINATION_ID (+) = DIST.DIST_CODE_COMBINATION_ID
586 and GC2.CODE_COMBINATION_ID (+) = AP_INV.ACCTS_PAY_CODE_COMBINATION_ID
587 '||l_inv_type_where||'
588 ' ||l_batch_id_where||'
589 ' ||l_entry_person_where||'
590 ' ||l_inv_type_where||'
591 ' ||l_batch_id_where||'
592 ' ||l_doc_seq_id_where||'
593 ' ||l_doc_seq_value_where||'
594 ' ||l_inv_currency_code_where||'
595 ' ||l_supplier_where||'
596 ' ||l_liability_range_where||'
597 ' ||l_entered_date_where||'
598 ' ||l_cancelled_where||'
599 ' ||l_line_inv_where||'
600 ' ||p_inv_org_where||'
601 ' ||p_line_org_where||'
602 ' ||p_dist_org_where||'
603 ORDER BY ap_inv.invoice_currency_code,
604 ap_inv.batch_id,
605 decode(:c_sort_by_alternate, ''Y'', upper(po_pv.vendor_name_alt), upper(po_pv.vendor_name)),
606 ap_inv.invoice_num,
607 dist.distribution_line_number';
608
609 fa_rx_util_pkg.debug('Main SQL:');
610 fa_rx_util_pkg.debug(l_main_sql);
611
612 /* Open v_MainCursor */
613 v_MainCursor :=DBMS_SQL.OPEN_CURSOR;
614 fa_rx_util_pkg.debug('***** OPEN CURSOR: v_MainCursor *****');
615 fa_rx_util_pkg.debug('v_MainCursor :'||v_MainCursor);
616
617 /* PARSE v_MainCursor */
618 DBMS_SQL.PARSE (v_MainCursor,l_main_sql,DBMS_SQL.V7);
619 fa_rx_util_pkg.debug('***** PARSE: v_MainCursor *****');
620
621 /* DEFINE COLUMN v_MainCursor */
622 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,1,h_invoice_id);
623 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,2,h_liability_ccid);
624 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,3,h_invoice_type,25);
625 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,4,h_inv_dist_id);
626 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,5,h_dist_number); -- Originally line_number);
627 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,6,h_dist_type,25); -- Originally line_type,25);
628 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,7,h_dist_ccid);
629 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,8,h_dist_acct_date); -- Newly Added
630 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,9,h_line_number); -- Newly Added
631 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,10,h_line_type,25); -- Newly Added
632
633 fa_rx_util_pkg.debug('***** DEFINE COLUMN: v_MainCursor *****');
634
635 /* BIND BARIABLE v_MainCursor */
636 if p_acct_date_min is not null then
637 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_acct_date_min',p_acct_date_min);
638 end if;
639
640 if p_acct_date_max is not null then
641 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_acct_date_max',p_acct_date_max);
642 end if;
643
644 if p_entered_date_min is not null then
645 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_entered_date_min',p_entered_date_min);
646 end if;
647
648 if p_entered_date_max is not null then
649 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_entered_date_max',p_entered_date_max);
650 end if;
651
652 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_sort_by_alternate', h_sort_by_alternate);
653
654 /* Entered by */
655 if p_entry_person_id is not null then
656 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_entry_person_id', p_entry_person_id);
657 end if;
658
659 /*Invoice Type */
660 if p_invoice_type is not null then
661 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_invoice_type', p_invoice_type);
662 end if;
663
664 /* Batch ID */
665 if p_batch_id is not null then
666 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_batch_id', p_batch_id);
667 end if;
668
669 /* Document Sequence Id */
670 if p_doc_sequence_id is not null then
671 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_doc_sequence_id', p_doc_sequence_id);
672 end if;
673
674 /* Document Sequence value range */
675 if p_doc_sequence_value_min is not null then
676 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_doc_sequence_value_min', p_doc_sequence_value_min);
677 end if;
678
679 if p_doc_sequence_value_max is not null then
680 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_doc_sequence_value_max', p_doc_sequence_value_max);
681 end if;
682
683 /* Supplier name range */
684 if p_supplier_min is not null then
685 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_supplier_min', p_supplier_min);
686 end if;
687
688 if p_supplier_max is not null then
689 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_supplier_max', p_supplier_max);
690 end if;
691
692 /* Liability account range */
693 -- if p_liability_min is not null and p_liability_max is not null then
694 -- DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_liability_min', p_liability_min);
695 -- DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_liability_max', p_liability_max);
696 -- end if;
697
698 /* Distribution account range */
699 -- if p_dist_acct_min is not null and p_dist_acct_max is not null then
700 -- DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_dist_acct_min', p_dist_acct_min);
701 -- DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_dist_acct_max', p_dist_acct_max);
702 -- end if;
703
704 /* Invoice Currency Code */
705 if p_inv_currency_code is not null then
706 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_inv_currency_code', p_inv_currency_code);
707 end if;
708
709 /* Distribution mount range */
710 if p_dist_amount_min is not null then
711 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_dist_amount_min', p_dist_amount_min);
712 end if;
713
714 if p_dist_amount_max is not null then
715 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_dist_amount_max', p_dist_amount_max);
716 end if;
717
718 /* Entered date range */
719 if p_entered_date_min is not null then
720 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_entered_date_min', p_entered_date_min);
721 end if;
722
723 if p_entered_date_max is not null then
724 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':c_entered_date_max', p_entered_date_max);
725 end if;
726
727 /* MOAC Change - bind p_reporting_entity_id */
728 -- bug: 9914839 Bind variable set for reporting Level : Legal Entity as well
729 if p_reporting_level = '3000' or p_reporting_level = '2000' then
730 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_reporting_entity_id', h_reporting_entity_id);
731 fa_rx_util_pkg.debug('h_reporting_entity_id :'||h_reporting_entity_id);
732 end if;
733
734 fa_rx_util_pkg.debug('***** BIND VARIABLE: v_MainCursor *****');
735
736
737 v_MainReturn := DBMS_SQL.EXECUTE(v_MainCursor);
738 fa_rx_util_pkg.debug('***** EXECUTE: v_MainCursor *****');
739 fa_rx_util_pkg.debug('v_MainReturn :'||v_MainReturn);
740
741 /* Loop and Fetch v_MainCursor */
742 Loop
743
744 v_MainFetch := DBMS_SQL.FETCH_ROWS(v_MainCursor);
745 fa_rx_util_pkg.debug('***** FETCH ROWS: v_MainCursor *****');
746 fa_rx_util_pkg.debug('v_MainFetch :'||v_MainFetch);
747
748 If v_MainFetch =0 then
749 Exit;
750 end if;
751
752 DBMS_SQL.COLUMN_VALUE(v_MainCursor,1,h_invoice_id);
753 DBMS_SQL.COLUMN_VALUE(v_MainCursor,2,h_liability_ccid);
754 DBMS_SQL.COLUMN_VALUE(v_MainCursor,3,h_invoice_type);
755 DBMS_SQL.COLUMN_VALUE(v_MainCursor,4,h_inv_dist_id);
756 DBMS_SQL.COLUMN_VALUE(v_MainCursor,5,h_dist_number); -- Originally line_number);
757 DBMS_SQL.COLUMN_VALUE(v_MainCursor,6,h_dist_type); -- Originally line_type);
758 DBMS_SQL.COLUMN_VALUE(v_MainCursor,7,h_dist_ccid);
759 DBMS_SQL.COLUMN_VALUE(v_MainCursor,8,h_dist_acct_date); -- Newly Added
760 DBMS_SQL.COLUMN_VALUE(v_MainCursor,9,h_line_number); -- Newly Added
761 DBMS_SQL.COLUMN_VALUE(v_MainCursor,10,h_line_type); -- Newly Added
762
763 fa_rx_util_pkg.debug('h_invoice_id: '||h_invoice_id);
764 fa_rx_util_pkg.debug('h_liability_ccid: '||h_liability_ccid);
765 fa_rx_util_pkg.debug('h_invoice_type: '||h_invoice_type);
766 fa_rx_util_pkg.debug('h_inv_dist_id :'||h_inv_dist_id);
767 fa_rx_util_pkg.debug('h_dist_number :'||h_dist_number); -- Changed to DIST NUMBER
768 fa_rx_util_pkg.debug('h_dist_type :'||h_dist_type); -- Changed to DIST Type
769 fa_rx_util_pkg.debug('h_dist_ccid :'||h_inv_dist_id);
770 fa_rx_util_pkg.debug('h_line_number :'||h_line_number); -- Newly Added
771 fa_rx_util_pkg.debug('h_line_type :'||h_line_type); -- Newly Added
772 fa_rx_util_pkg.debug('h_dist_acct_date :'|| h_dist_acct_date); -- Newly Added
773
774 /* Get Liability account and description, natural account and description */
775
776 if h_liability_ccid is not null then
777
778 h_liability_acct := fa_rx_flex_pkg.get_value(
779 p_application_id => 101,
780 p_id_flex_code => 'GL#',
781 p_id_flex_num => h_chart_of_acct_id,
782 p_qualifier => 'ALL',
783 p_ccid => h_liability_ccid);
784
785 h_liability_desc := fa_rx_flex_pkg.get_description(
786 p_application_id => 101,
787 p_id_flex_code => 'GL#',
788 p_id_flex_num => h_chart_of_acct_id,
789 p_qualifier => 'ALL',
790 p_data => h_liability_acct);
791
792 h_liability_natacct := fa_rx_flex_pkg.get_value(
793 p_application_id => 101,
794 p_id_flex_code => 'GL#',
795 p_id_flex_num => h_chart_of_acct_id,
796 p_qualifier => 'GL_ACCOUNT',
797 p_ccid => h_liability_ccid);
798
799 h_liability_natdesc := fa_rx_flex_pkg.get_description(
800 p_application_id => 101,
801 p_id_flex_code => 'GL#',
802 p_id_flex_num => h_chart_of_acct_id,
803 p_qualifier => 'GL_ACCOUNT',
804 p_data => h_liability_natacct);
805
806 else
807 h_liability_acct := to_char(null);
808 h_liability_desc := to_char(null);
809 h_liability_natacct := to_char(null);
810 h_liability_natdesc := to_char(null);
811 end if;
812
813 fa_rx_util_pkg.debug('h_liability_acct : '||h_liability_acct);
814 fa_rx_util_pkg.debug('h_liability_desc : '||h_liability_desc);
815 fa_rx_util_pkg.debug('h_liability_natacct : '||h_liability_natacct);
816 fa_rx_util_pkg.debug('h_liability_natdesc : '||h_liability_natdesc);
817
818 /*Get Distribution account and description, natural account and description */
819
820 if h_dist_ccid is not null then
821
822 h_dist_acct := fa_rx_flex_pkg.get_value(
823 p_application_id => 101,
824 p_id_flex_code => 'GL#',
825 p_id_flex_num => h_chart_of_acct_id,
826 p_qualifier => 'ALL',
827 p_ccid => h_dist_ccid);
828
829 h_dist_desc := fa_rx_flex_pkg.get_description(
830 p_application_id => 101,
831 p_id_flex_code => 'GL#',
832 p_id_flex_num => h_chart_of_acct_id,
833 p_qualifier => 'ALL',
834 p_data => h_dist_acct);
835
836 h_dist_natacct := fa_rx_flex_pkg.get_value(
837 p_application_id => 101,
838 p_id_flex_code => 'GL#',
839 p_id_flex_num => h_chart_of_acct_id,
840 p_qualifier => 'GL_ACCOUNT',
841 p_ccid => h_dist_ccid);
842
843 h_dist_natdesc := fa_rx_flex_pkg.get_description(
844 p_application_id => 101,
845 p_id_flex_code => 'GL#',
846 p_id_flex_num => h_chart_of_acct_id,
847 p_qualifier => 'GL_ACCOUNT',
848 p_data => h_dist_natacct);
849
850 else
851 h_dist_acct := to_char(null);
852 h_dist_desc := to_char(null);
853 h_dist_natacct := to_char(null);
854 h_dist_natdesc := to_char(null);
855 end if;
856
857 fa_rx_util_pkg.debug('h_dist_acct : '||h_dist_acct);
858 fa_rx_util_pkg.debug('h_dist_desc : '||h_dist_desc);
859 fa_rx_util_pkg.debug('h_dist_natacct : '||h_dist_natacct);
860 fa_rx_util_pkg.debug('h_dist_natdesc : '||h_dist_natdesc);
861
862
863 /* Insert to JG_ZZ_AP_IR_REP_ITF */
864 Insert into JG_ZZ_AP_IR_REP_ITF (
865 REQUEST_ID,
866 CREATED_BY,
867 CREATION_DATE,
868 LAST_UPDATE_DATE,
869 LAST_UPDATED_BY,
870 LAST_UPDATE_LOGIN,
871 FUNCTIONAL_CURRENCY_CODE,
872 ORGANIZATION_NAME,
873 INVOICE_ID,
874 LIABILITY_CCID,
875 LIABILITY_ACCOUNT,
876 LIABILITY_DESC,
877 LIABILITY_NATACC,
878 LIABILITY_NATACC_DESC,
879 INVOICE_TYPE,
880 INVOICE_DISTRIBUTION_ID,
881 LINE_NUMBER,
882 LINE_TYPE,
883 DIST_NUMBER, -- Newly Added
884 DIST_TYPE, -- Newly Added
885 ACCOUNTING_DATE, -- Newly Added
886 DISTRIBUTION_CCID,
887 DISTRIBUTION_ACCOUNT,
888 DISTRIBUTION_ACCOUNT_DESC,
889 DISTRIBUTION_NATACC,
890 DISTRIBUTION_NATACC_DESC
891 )
892 values (
893 p_request_id,
894 p_login_id,
895 sysdate,
896 sysdate,
897 p_login_id,
898 1,
899 h_currency_code,
900 h_book_short_name,
901 h_invoice_id,
902 h_liability_ccid,
903 h_liability_acct,
904 h_liability_desc,
905 h_liability_natacct,
906 h_liability_natdesc,
907 h_invoice_type,
908 h_inv_dist_id,
909 h_line_number,
910 h_line_type,
911 h_dist_number, -- Newly Added
912 h_dist_type, -- Newly Added
913 h_dist_acct_date, -- Newly Added
914 h_dist_ccid,
915 h_dist_acct,
916 h_dist_desc,
917 h_dist_natacct,
918 h_dist_natdesc
919 );
920
921 fa_rx_util_pkg.debug('Inserted invoice_id: '||h_invoice_id||' inv_dist_id : '||h_inv_dist_id);
922
923 End Loop;
924
925 DBMS_SQL.CLOSE_CURSOR(v_MainCursor);
926
927 fa_rx_util_pkg.debug('***** Close Cursor v_MainCursor *****');
928
929 commit;
930
931 Exception
932 when others then
933 retcode :=2;
934 errbuf := sqlerrm;
935 FND_FILE.PUT_LINE(fnd_file.log,errbuf);
936 return;
937
938 end ap_rx_invoice_run;
939
940 end JG_RX_IR_PKG;