[Home] [Help]
PACKAGE BODY: APPS.JGRX_WT
Source
1 PACKAGE BODY JGRX_WT AS
2 /* $Header: jgrxwtb.pls 120.13 2006/09/22 17:43:34 dbetanco ship $ */
3 /**************************************************************************
4 * Record Structure to hold placeholder values *
5 **************************************************************************/
6
7 type param_t is record ( p_gldate_from DATE,
8 p_gldate_to DATE,
9 p_supplier_from VARCHAR2(240),
10 p_supplier_to VARCHAR2(240),
11 p_supp_tax_reg_num VARCHAR2(20),
12 p_invoice_number VARCHAR2(50),
13 p_reporting_level VARCHAR2(50),
14 p_reporting_context VARCHAR2(50),
15 /* Bug 3017170 - Increased the width to 1000 from 50 */
16 p_legal_entity_id NUMBER,
17 p_acct_flexfield_from VARCHAR2(1000),
18 p_acct_flexfield_to VARCHAR2(1000),
19 p_org_type VARCHAR2(25),
20 p_location NUMBER(15),
21 p_res_inc_categ VARCHAR2(80),
22 p_for_inc_categ VARCHAR2(80)
23 );
24
25 parm param_t;
26
27 /**************************************************************************
28 * Definition of private variables to be used inside the package *
29 **************************************************************************/
30
31 l_coa_id gl_sets_of_books.chart_of_accounts_id%TYPE;
32 l_msg varchar2(500);
33 l_retcode number :=0;
34 l_errbuf varchar2(2000);
35
36 TYPE lookup_rectype is record(
37 PRODUCT VARCHAR2(3),
38 LOOKUP_TYPE VARCHAR2(60),
39 LOOKUP_CODE VARCHAR2(30),
40 MEANING VARCHAR2(80));
41 lookup_meaning_rec lookup_rectype;
42 TYPE lookup_tabtype is table of lookup_rectype
43 index by binary_integer;
44
45 p_gbl_lookup_table lookup_tabtype;
46
47 /**************************************************************************
48 * Private Procedures Specification *
49 **************************************************************************/
50
51 /**************************************************************************
52 * *
53 * Name : CONSTRUCT_SELECT, CONSTRUCT_FROM, CONSTRUCT_WHERE *
54 * Purpose : These procedures are used to construct the complete *
55 * SELECT statement *
56 * *
57 **************************************************************************/
58
59 PROCEDURE CONSTRUCT_SELECT;
60 PROCEDURE CONSTRUCT_FROM;
61 PROCEDURE CONSTRUCT_WHERE;
62 PROCEDURE build_gbl_lookup_table;
63
64
65 /**************************************************************************
66 * Public Procedures *
67 **************************************************************************/
68
69 /**************************************************************************
70 * *
71 * Name : Get_Withholding_Tax *
72 * Purpose : This is the core generic withholding tax routine, which *
73 * populates the interface table JG_ZZ_AP_WHT_ITF. *
74 * This has a call to the following: *
75 * 1. Before Report - where it constructs the basic SELECT *
76 * 2. Bind - binds the variables *
77 * *
78 **************************************************************************/
79 PROCEDURE GET_WITHHOLDING_TAX (request_id in number,
80 section_name in varchar2,
81 retcode out NOCOPY number,
82 errbuf out NOCOPY varchar2)
83 IS
84 BEGIN
85 fa_rx_util_pkg.debug('jgrx_wt.get_withholding_tax()+');
86
87 -- Initialize request
88 fa_rx_util_pkg.init_request('jgrx_wt.get_withholding_tax', request_id);
89
90 fa_rx_util_pkg.assign_report(section_name,
91 true,
92 'jgrx_wt.before_report;',
93 NULL,
94 NULL,
95 NULL);
96
97 fa_rx_util_pkg.run_report('jgrx_wt.get_withholding_tax', retcode, errbuf);
98
99 fa_rx_util_pkg.debug('jgrx_wt.get_withholding_tax()-');
100
101 END GET_WITHHOLDING_TAX;
102
103
104 /**************************************************************************
105 * *
106 * Name : jg_wht_extract *
107 * Purpose : This plug-in is specific to suit Korean withholding tax *
108 * needs. It has the following procedures *
109 * 1. Call to the BASIC procedure Get_Withholding_Tax *
110 * 2. Before Report - To add conditions specific to Korea *
111 * 3. Bind - binds the variables *
112 * 4. After Fetch - does manipulation on fetched record *
113 * *
114 **************************************************************************/
115 PROCEDURE jg_wht_extract ( p_gldate_from in DATE,
116 p_gldate_to in DATE,
117 p_supplier_from in VARCHAR2,
118 p_supplier_to in VARCHAR2,
119 p_supp_tax_reg_num in VARCHAR2,
120 p_invoice_number in VARCHAR2,
121 p_reporting_level in VARCHAR2,
122 p_reporting_context in VARCHAR2,
123 p_legal_entity_id in NUMBER,
124 p_acct_flexfield_from in VARCHAR2,
125 p_acct_flexfield_to in VARCHAR2,
126 p_org_type in VARCHAR2,
127 p_location in NUMBER,
128 p_res_inc_categ in VARCHAR2,
129 p_for_inc_categ in VARCHAR2,
130 request_id in NUMBER,
131 retcode out NOCOPY NUMBER,
132 errbuf out NOCOPY VARCHAR2)
133 IS
134 BEGIN
135
136 fa_rx_util_pkg.debug('jgrx_wt.jg_wht_extract()+');
137
138 -- Initialize request
139 fa_rx_util_pkg.init_request('jgrx_wt.jg_wht_extract', request_id);
140
141 -- Store the parameters in a variable which can be accessed globally across
142 -- all procedures
143 parm.p_gldate_from := p_gldate_from;
144 parm.p_gldate_to := p_gldate_to;
145 parm.p_supplier_from := p_supplier_from;
146 parm.p_supplier_to := p_supplier_to;
147 parm.p_supp_tax_reg_num := p_supp_tax_reg_num;
148 parm.p_invoice_number := p_invoice_number;
149 parm.p_reporting_level := p_reporting_level;
150 parm.p_reporting_context := p_reporting_context;
151 parm.p_legal_entity_id := p_legal_entity_id;
152 parm.p_acct_flexfield_from := p_acct_flexfield_from;
153 parm.p_acct_flexfield_to := p_acct_flexfield_to;
154 parm.p_org_type := p_org_type;
155 parm.p_location := p_location;
156 parm.p_res_inc_categ := p_res_inc_categ;
157 parm.p_for_inc_categ := p_for_inc_categ;
158
159
160 -- Call to construct the basic query. The basic SELECT statement is built in
161 -- this stage. Plug-in would add what is specific to the report. No data is
162 -- inserted into the interface table at this stage.
163
164
165 jgrx_wt.get_withholding_tax( request_id,
166 'get_withholding_tax',
167 retcode,
168 errbuf);
169
170 -- Plug-in code is executed here.
171
172 fa_rx_util_pkg.assign_report('get_withholding_tax',
173 true,
174 'jgrx_wt.wht_before_report;',
175 'jgrx_wt.wht_bind(:CURSOR_SELECT);',
176 'jgrx_wt.wht_after_fetch;',
177 NULL);
178
179 fa_rx_util_pkg.run_report('jgrx_wt.jg_wht_extract', retcode, errbuf);
180
181 fa_rx_util_pkg.debug('jgrx_wt.jg_wht_extract()-');
182
183 END jg_wht_extract;
184
185 /***************************************************************************
186 * *
187 * Name : before_report *
188 * Purpose : This procedure constructs the basic SELECT and INSERT *
189 * statement to populate the interface table JG_ZZ_AP_WHT_ITF *
190 * *
191 ***************************************************************************/
192 PROCEDURE before_report
193 IS
194 BEGIN
195 fa_rx_util_pkg.debug('jgrx_wt.before_report(+)');
196
197 -- Get the Reporting SOB_ID, SOB_NAME, Functional_currency_code
198 fnd_profile.get('GL_SET_OF_BKS_ID', jgrx_wt.var.sob_id);
199
200 begin
201 select name, currency_code, chart_of_accounts_id
202 into jgrx_wt.var.reporting_sob_name,
203 jgrx_wt.var.func_currency_code, l_coa_id
204 from gl_sets_of_books
205 where set_of_books_id = jgrx_wt.var.sob_id;
206 exception
207 WHEN no_data_found THEN
208 RAISE_APPLICATION_ERROR(-20010,sqlerrm);
209 end;
210
211 -- Call to construct the basic select, from and where clauses
212 CONSTRUCT_SELECT;
213 CONSTRUCT_FROM;
214 CONSTRUCT_WHERE;
215
216 END before_report;
217
218 /***************************************************************************
219 * *
220 * Name : wht_before_report *
221 * Purpose : This procedure has Korean specific WHERE clauses *
222 * for populating the interface table JG_ZZ_AP_WHT_ITF *
223 * *
224 ***************************************************************************/
225
226 PROCEDURE wht_before_report
227 IS
228 l_where_flex VARCHAR2(2000);
229 b_acct_flexfield_from VARCHAR2(1000);
230 b_acct_flexfield_to VARCHAR2(1000);
231 BEGIN
232
233 b_acct_flexfield_from := parm.p_acct_flexfield_from;
234 b_acct_flexfield_to := parm.p_acct_flexfield_to;
235 --
236 -- In the WHERE clause, check for Korean context.
237 --
238 /* Commented out NOCOPY as the category can be null when there are no mandatory
239 segments in the gdf
240 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
241 hrl1.global_attribute_category = ''JA.KR.PERWSLOC.WITHHOLDING'' ';
242 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
243 atc.global_attribute_category = ''JA.KR.APXTADTC.WITHHOLDING'' ';
244 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
245 pvs.global_attribute_category = ''JA.KR.APXVDMVD.WITHHOLDING'' '; */
246
247 --
248 -- Add the WHERE clause specific to the Korean Withholding Report
249 --
250 If parm.p_gldate_from IS NOT NULL then
251 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
252 ind.accounting_date>= :b_gldate_from';
253 End If;
254 If parm.p_gldate_to IS NOT NULL then
255 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
256 ind.accounting_date <= :b_gldate_to';
257 End If;
258 If parm.p_supplier_from IS NOT NULL then
259 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
260 UPPER(pov.vendor_name) >= UPPER(:b_supplier_from)';
261 End If;
262 If parm.p_supplier_to IS NOT NULL then
263 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
264 UPPER(pov.vendor_name) <= UPPER(:b_supplier_to)';
265 End If;
266 If parm.p_supp_tax_reg_num IS NOT NULL then
267 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
268 UPPER(pov.vat_registration_num) = UPPER(:b_supp_tax_reg_num)';
269 End If;
270 If parm.p_invoice_number IS NOT NULL then
271 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
272 UPPER(ap_inv.invoice_num) = UPPER(:b_invoice_number)';
273 End If;
274 If parm.p_org_type IS NOT NULL then
275 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
276 UPPER(pov.organization_type_lookup_code) = UPPER(:b_org_type)';
277 End If;
278 If parm.p_location IS NOT NULL then
279 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
280 hrl1.location_id = :b_location';
281 End If;
282
283 If parm.p_res_inc_categ IS NOT NULL then
284 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
285 UPPER(atc.global_attribute9) = UPPER(:b_res_inc_categ)';
286 End If;
287
288 If parm.p_for_inc_categ IS NOT NULL then
289 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
290 UPPER(atc.global_attribute5) = UPPER(:b_for_inc_categ)';
291 End If;
292
293 If parm.p_legal_entity_id IS NOT NULL then
294 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and
295 ap_inv.legal_entity_id =(:b_legal_entity_id)';
296 End If;
297
298 -- Build the WHERE clause to restrict data based on the accounting flex
299 -- field range specified.
300 If (parm.p_acct_flexfield_from IS NOT NULL
301 and parm.p_acct_flexfield_to IS NOT NULL) then
302 l_where_flex := FA_RX_FLEX_PKG.FLEX_SQL(P_APPLICATION_ID => 101,
303 P_ID_FLEX_CODE => 'GL#',
304 P_ID_FLEX_NUM => L_COA_ID,
305 P_TABLE_ALIAS => 'CC',
306 P_MODE => 'WHERE',
307 P_QUALIFIER => 'ALL',
308 P_FUNCTION => 'BETWEEN',
309 P_OPERAND1 => b_acct_flexfield_from,
310 P_OPERAND2 => b_acct_flexfield_to);
311
312 l_where_flex := ' and '||l_where_flex;
313 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause || l_where_flex;
314 End if;
315
316 fa_rx_util_pkg.debug('jgrx_wt.wht_before_report()-');
317
318 END wht_before_report;
319
320
321 /***************************************************************************
322 * *
323 * Name : wht_bind *
324 * Purpose : This procedure accepts an integer parameter :CURSOR_SELECT *
325 * and binds the parameter to variables *
326 * *
327 ***************************************************************************/
328
329 PROCEDURE wht_bind(c in integer)
330 IS
331 b_gldate_from date;
332 b_gldate_to date;
333 b_supplier_from varchar2(240);
334 b_supplier_to varchar2(240);
335 b_supp_tax_reg_num varchar2(20);
336 b_invoice_number varchar2(50);
337 b_acct_flexfield_from varchar2(1000);
338 b_acct_flexfield_to varchar2(1000);
339 b_legal_entity_id number;
340 b_org_type varchar2(25);
341 b_location number(15);
342 b_res_inc_categ varchar2(80);
343 b_for_inc_categ varchar2(80);
344
345 BEGIN
346
347 fa_rx_util_pkg.debug('jgrx_wt.wht_bind()+');
348
349 If parm.p_gldate_from IS NOT NULL then
350 fa_rx_util_pkg.debug('Binding b_gldate_from');
351 dbms_sql.bind_variable(c, 'b_gldate_from', parm.p_gldate_from);
352 End If;
353
354 If parm.p_gldate_to IS NOT NULL then
355 fa_rx_util_pkg.debug('Binding b_gldate_to');
356 dbms_sql.bind_variable(c, 'b_gldate_to', parm.p_gldate_to);
357 End If;
358
359 If parm.p_supplier_from IS NOT NULL then
360 fa_rx_util_pkg.debug('Binding b_supplier_from');
361 dbms_sql.bind_variable(c, 'b_supplier_from', parm.p_supplier_from);
362 End If;
363
364 If parm.p_supplier_to IS NOT NULL then
365 fa_rx_util_pkg.debug('Binding b_supplier_to');
366 dbms_sql.bind_variable(c, 'b_supplier_to', parm.p_supplier_to);
367 End If;
368
369 If parm.p_supp_tax_reg_num IS NOT NULL then
370 fa_rx_util_pkg.debug('Binding b_supp_tax_reg_num');
371 dbms_sql.bind_variable(c, 'b_supp_tax_reg_num', parm.p_supp_tax_reg_num);
372 End If;
373
374 If parm.p_invoice_number IS NOT NULL then
375 fa_rx_util_pkg.debug('Binding b_invoice_number');
376 dbms_sql.bind_variable(c, 'b_invoice_number', parm.p_invoice_number);
377 End If;
378
379 /* Commented out NOCOPY for Bug 1339331
380
381 If parm.p_acct_flexfield_from IS NOT NULL then
382 fa_rx_util_pkg.debug('Binding b_acct_flexfield_from');
383 dbms_sql.bind_variable(c, 'b_acct_flexfield_from',parm.p_acct_flexfield_from);
384 End If;
385
386 If parm.p_acct_flexfield_to IS NOT NULL then
387 fa_rx_util_pkg.debug('Binding b_acct_flexfield_to');
388 dbms_sql.bind_variable(c, 'b_acct_flexfield_to', parm.p_acct_flexfield_to);
389 End If;
390 End Comment for Bug 1339331 */
391
392 If parm.p_org_type IS NOT NULL then
393 fa_rx_util_pkg.debug('Binding b_org_type');
394 dbms_sql.bind_variable(c, 'b_org_type', parm.p_org_type);
395 End If;
396
397 If parm.p_location IS NOT NULL then
398 fa_rx_util_pkg.debug('Binding b_location');
399 dbms_sql.bind_variable(c, 'b_location', parm.p_location);
400 End If;
401
402 If parm.p_res_inc_categ IS NOT NULL then
403 fa_rx_util_pkg.debug('Binding b_res_inc_categ');
404 dbms_sql.bind_variable(c, 'b_res_inc_categ', parm.p_res_inc_categ);
405 End If;
406
407 If parm.p_for_inc_categ IS NOT NULL then
408 fa_rx_util_pkg.debug('Binding b_for_inc_categ');
409 dbms_sql.bind_variable(c, 'b_for_inc_categ', parm.p_for_inc_categ);
410 End If;
411
412 If parm.p_legal_entity_id IS NOT NULL then
413 fa_rx_util_pkg.debug('Binding b_legal_entity_id');
414 dbms_sql.bind_variable(c, 'b_legal_entity_id', parm.p_legal_entity_id);
415 End If;
416
417
418 fa_rx_util_pkg.debug('jgrx_wt.wht_bind()-');
419 END wht_bind;
420
421 /***************************************************************************
422 * *
423 * Name : wht_after_fetch *
424 * Purpose : This procedure does any manipulation required on the *
425 * fetched record before populating the interface table *
426 * JG_ZZ_AP_WHT_ITF *
427 * *
428 ***************************************************************************/
429 PROCEDURE wht_after_fetch
430 IS
431 BEGIN
432 fa_rx_util_pkg.debug('jgrx_wt.wht_after_fetch()+');
433
434 jgrx_wt.var.total_wht_amount := jgrx_wt.var.resident_tax+
435 jgrx_wt.var.income_tax;
436 /* Bug 1339324 */
437 IF jgrx_wt.var.amt_subject_to_wh is NULL then
438 jgrx_wt.var.amt_subject_to_wh := jgrx_wt.var.invoice_amount;
439 END IF;
440
441 /* Bug 1347708 */
442 IF (jgrx_wt.var.total_wht_amount > 0 and jgrx_wt.var.amt_subject_to_wh > 0)
443 THEN
444 jgrx_wt.var.amt_subject_to_wh := -1 * jgrx_wt.var.amt_subject_to_wh;
445 END IF;
446
447 jgrx_wt.var.recognized_expense_amt := jgrx_wt.var.amt_subject_to_wh*
448 to_number(jgrx_wt.var.recognized_expense_percent)/100;
449
450 jgrx_wt.var.nominal_or_reg_tax_rate :=
451 nvl(to_number(jgrx_wt.var.nominal_tax_rate),
452 jgrx_wt.var.tax_rate);
453
454 IF (jgrx_wt.var.withholding_tax_type = 'INCOME') THEN
455 jgrx_wt.var.inc_wh_tax_base_amt := jgrx_wt.var.amt_subject_to_wh-
456 nvl(jgrx_wt.var.recognized_expense_amt,0);
457 jgrx_wt.var.inc_wh_tax_base_amt := Ap_Utilities_Pkg.Ap_Round_Currency
458 (jgrx_wt.var.inc_wh_tax_base_amt,
459 jgrx_wt.var.func_currency_code);
460 ELSE
461 jgrx_wt.var.inc_wh_tax_base_amt := 0;
462 END IF;
463
464 IF (jgrx_wt.var.withholding_tax_type = 'RESIDENT') THEN
465 jgrx_wt.var.res_wh_tax_base_amt := jgrx_wt.var.amt_subject_to_wh;
466 ELSE
467 jgrx_wt.var.res_wh_tax_base_amt := 0;
468 END IF;
469
470 jgrx_wt.var.total_tax_base_amt := nvl(jgrx_wt.var.res_wh_tax_base_amt,0) +
471 nvl(jgrx_wt.var.inc_wh_tax_base_amt,0);
472
473 IF (jgrx_wt.var.create_dist = 'PAYMENT') then
474 jgrx_wt.var.net_amount:= jgrx_wt.var.payment_amount;
475 ELSIF (jgrx_wt.var.create_dist = 'APPROVAL') then
476 declare
477 l_amount NUMBER := 0;
478 begin
479 select sum(nvl(ind.base_amount,ind.amount))
480 into l_amount
481 from ap_invoice_distributions_all ind
482 where ind.invoice_id = jgrx_wt.var.invoice_id
483 and ind.line_type_lookup_code = 'AWT'
484 and ind.org_id = jgrx_wt.var.org_id;
485 jgrx_wt.var.net_amount:= jgrx_wt.var.invoice_amount + l_amount;
486 exception
487 WHEN no_data_found THEN
488 RAISE_APPLICATION_ERROR(-20010,sqlerrm);
489 when others then
490 fa_rx_util_pkg.debug( 'Exception in wht_after_fetch'||
491 SQLCODE||';'||SQLERRM);
492 end;
493 END IF;
494
495 jgrx_wt.var.supp_concatenated_address := jgrx_wt.var.supplier_address_line1
496 || jgrx_wt.var.supplier_address_line2|| jgrx_wt.var.supplier_address_line3
497 || jgrx_wt.var.supplier_country|| jgrx_wt.var.supplier_postal_code;
498
499 jgrx_wt.var.accounting_flexfield := FA_RX_FLEX_PKG.GET_VALUE
500 (P_APPLICATION_ID => 101,
501 P_ID_FLEX_CODE => 'GL#',
502 P_ID_FLEX_NUM => L_COA_ID,
503 P_QUALIFIER => 'ALL',
504 P_CCID => jgrx_wt.var.dist_code_combination_id);
505
506 jgrx_wt.var.biz_inc_sub_categ_meaning := get_lookup_meaning('FND',
507 'JAKR_AP_AWT_BIZ_INC_SUB_CAT',
508 jgrx_wt.var.business_inc_sub_category);
509
510 jgrx_wt.var.org_type_meaning := get_lookup_meaning('PO',
511 'ORGANIZATION TYPE',
512 jgrx_wt.var.organization_type);
513
514 jgrx_wt.var.wh_tax_type_meaning := get_lookup_meaning('FND',
515 'JAKR_AP_AWT_TAX_TYPE',
516 jgrx_wt.var.withholding_tax_type);
517
518 jgrx_wt.var.res_inc_categ_meaning := get_lookup_meaning('FND',
519 'JAKR_AP_AWT_INC_CAT_DOMESTIC',
520 jgrx_wt.var.resident_inc_categ_code);
521
522 jgrx_wt.var.for_inc_categ_meaning := get_lookup_meaning('FND',
523 'JAKR_AP_AWT_INC_CAT_FOREIGN',
524 jgrx_wt.var.foreign_inc_categ_code);
525
526 fa_rx_util_pkg.debug('jgrx_wt.wht_after_fetch()-');
527
528 END wht_after_fetch;
529
530
531 /**************************************************************************
532 * *
533 * Name : get_lookup_meaning *
534 * Purpose : This function returns the meaning for the matching *
535 * lookup_type and lookup_code in po_lookup_codes, *
536 * fnd_lookups using a memory structure. *
537 * *
538 **************************************************************************/
539
540 FUNCTION get_lookup_meaning( p_product in varchar2,
541 p_lookup_type in varchar2,
542 p_lookup_code in varchar2)
543 RETURN varchar2
544 IS
545 BEGIN
546
547 if nvl(p_gbl_lookup_table.LAST,0) <= 0 then
548 build_gbl_lookup_table;
549 end if;
550
551 for i in 1 .. p_gbl_lookup_table.count loop
552 if p_gbl_lookup_table(i).lookup_type = p_lookup_type and
553 p_gbl_lookup_table(i).lookup_code = p_lookup_code and
554 p_gbl_lookup_table(i).product = p_product
555 then
556 return p_gbl_lookup_table(i).meaning ;
557 end if;
558
559 end loop;
560
561 return (NULL);
562
563 End;
564
565 /**************************************************************************
566 Private Procedures
567 **************************************************************************/
568
569 /**************************************************************************
570 * *
571 * Name : CONSTRUCT_SELECT, CONSTRUCT_FROM, CONSTRUCT_WHERE *
572 * Purpose : These procedures are used to construct the complete *
573 * SELECT statement *
574 * *
575 **************************************************************************/
576
577 PROCEDURE CONSTRUCT_SELECT
578 IS
579 BEGIN
580 -- Write the basic select statement, From and Where clause
581 fa_rx_util_pkg.assign_column('1', 'hrl1.location_code', 'location_name',
582 'jgrx_wt.var.location_name', 'VARCHAR2', 60);
583 fa_rx_util_pkg.assign_column('2', 'hrl1.address_line_1', 'location_address1',
584 'jgrx_wt.var.location_address1', 'VARCHAR2', 240);
585 fa_rx_util_pkg.assign_column('3', 'hrl1.address_line_2', 'location_address2',
586 'jgrx_wt.var.location_address2', 'VARCHAR2', 240);
587 fa_rx_util_pkg.assign_column('4', 'hrl1.address_line_3', 'location_address3',
588 'jgrx_wt.var.location_address3', 'VARCHAR2', 240);
589 fa_rx_util_pkg.assign_column('5', 'hrl1.country', 'location_country',
590 'jgrx_wt.var.location_country', 'VARCHAR2', 60);
591 fa_rx_util_pkg.assign_column('6', 'hrl1.postal_code', 'location_zipcode',
592 'jgrx_wt.var.location_zipcode', 'VARCHAR2', 30);
593 fa_rx_util_pkg.assign_column('7', 'hrl1.telephone_number_1', 'location_phone'
594 , 'jgrx_wt.var.location_phone', 'VARCHAR2', 30);
595 fa_rx_util_pkg.assign_column('8', 'xle.name', 'legal_entity_name',
596 'jgrx_wt.var.legal_entity_name', 'VARCHAR2',60);
597 fa_rx_util_pkg.assign_column('9', 'xle.town_or_city', 'legal_entity_city',
598 'jgrx_wt.var.legal_entity_city', 'VARCHAR2', 30);
599 fa_rx_util_pkg.assign_column('10','xle.address_line_1','legal_entity_address1',
600 'jgrx_wt.var.legal_entity_address1', 'VARCHAR2', 240);
601 fa_rx_util_pkg.assign_column('11', 'xle.address_line_2','legal_entity_address2',
602 'jgrx_wt.var.legal_entity_address2', 'VARCHAR2', 240);
603 fa_rx_util_pkg.assign_column('12', 'xle.address_line_3','legal_entity_address3',
604 'jgrx_wt.var.legal_entity_address3', 'VARCHAR2', 240);
605 fa_rx_util_pkg.assign_column('13', 'xle.country','legal_entity_country',
606 'jgrx_wt.var.legal_entity_country', 'VARCHAR2', 60);
607 fa_rx_util_pkg.assign_column('14', 'xle.postal_code','legal_entity_zipcode',
608 'jgrx_wt.var.legal_entity_zipcode', 'VARCHAR2', 30);
609 -- Modified the below to NULL for bug 4734440
610 fa_rx_util_pkg.assign_column('15', NULL, 'legal_entity_phone',
611 'jgrx_wt.var.legal_entity_phone', 'VARCHAR2', 30);
612 fa_rx_util_pkg.assign_column('16', 'hrl1.global_attribute1',
613 'hrl_global_attribute1', 'jgrx_wt.var.tax_registration_num', 'VARCHAR2',150);
614 fa_rx_util_pkg.assign_column('17', 'hrl1.global_attribute4',
615 'hrl_global_attribute4', 'jgrx_wt.var.loc_taxable_person', 'VARCHAR2', 150);
616 fa_rx_util_pkg.assign_column('18', 'pov.vendor_id', 'supplier_id',
617 'jgrx_wt.var.supplier_id', 'NUMBER');
618 fa_rx_util_pkg.assign_column('19', 'pov.vendor_name', 'supplier_name',
619 'jgrx_wt.var.supplier_name', 'VARCHAR2', 240);
620 fa_rx_util_pkg.assign_column('20', 'pvs.vendor_site_id', 'supplier_site_id',
621 'jgrx_wt.var.supplier_site_id', 'NUMBER');
622 fa_rx_util_pkg.assign_column('21','pvs.vendor_site_code','supplier_site_name'
623 ,'jgrx_wt.var.supplier_site_name','VARCHAR2',100);
624 fa_rx_util_pkg.assign_column('22', 'pov.attribute1', ' pv_attribute1',
625 'jgrx_wt.var.pv_attribute1','VARCHAR2', 150);
626 fa_rx_util_pkg.assign_column('23', 'pov.attribute2', ' pv_attribute2',
627 'jgrx_wt.var.pv_attribute2','VARCHAR2', 150);
628 fa_rx_util_pkg.assign_column('24', 'pov.attribute3', ' pv_attribute3',
629 'jgrx_wt.var.pv_attribute3','VARCHAR2', 150);
630 fa_rx_util_pkg.assign_column('25', 'pov.attribute4', ' pv_attribute4',
631 'jgrx_wt.var.pv_attribute4','VARCHAR2', 150);
632 fa_rx_util_pkg.assign_column('26', 'pov.attribute5', ' pv_attribute5',
633 'jgrx_wt.var.pv_attribute5','VARCHAR2', 150);
634 fa_rx_util_pkg.assign_column('27', 'pov.attribute6', ' pv_attribute6',
635 'jgrx_wt.var.pv_attribute6','VARCHAR2', 150);
636 fa_rx_util_pkg.assign_column('28', 'pov.attribute7', ' pv_attribute7',
637 'jgrx_wt.var.pv_attribute7','VARCHAR2', 150);
638 fa_rx_util_pkg.assign_column('29', 'pov.attribute8', ' pv_attribute8',
639 'jgrx_wt.var.pv_attribute8','VARCHAR2', 150);
640 fa_rx_util_pkg.assign_column('30', 'pov.attribute9', ' pv_attribute9',
641 'jgrx_wt.var.pv_attribute9','VARCHAR2', 150);
642 fa_rx_util_pkg.assign_column('31', 'pov.attribute10', ' pv_attribute10',
643 'jgrx_wt.var.pv_attribute10','VARCHAR2', 150);
644 fa_rx_util_pkg.assign_column('32', 'pov.attribute11', ' pv_attribute11',
645 'jgrx_wt.var.pv_attribute11','VARCHAR2', 150);
646 fa_rx_util_pkg.assign_column('33', 'pov.attribute12', ' pv_attribute12',
647 'jgrx_wt.var.pv_attribute12','VARCHAR2', 150);
648 fa_rx_util_pkg.assign_column('34', 'pov.attribute13', ' pv_attribute13',
649 'jgrx_wt.var.pv_attribute13','VARCHAR2', 150);
650 fa_rx_util_pkg.assign_column('35', 'pov.attribute14', ' pv_attribute14',
651 'jgrx_wt.var.pv_attribute14','VARCHAR2', 150);
652 fa_rx_util_pkg.assign_column('36', 'pov.attribute15', ' pv_attribute15',
653 'jgrx_wt.var.pv_attribute15','VARCHAR2', 150);
654 fa_rx_util_pkg.assign_column('37', 'pvs.attribute1', ' pvs_attribute1',
655 'jgrx_wt.var.pvs_attribute1','VARCHAR2',150);
656 fa_rx_util_pkg.assign_column('38', 'pvs.attribute2', ' pvs_attribute2',
657 'jgrx_wt.var.pvs_attribute2','VARCHAR2',150);
658 fa_rx_util_pkg.assign_column('39', 'pvs.attribute3', ' pvs_attribute3',
659 'jgrx_wt.var.pvs_attribute3','VARCHAR2',150);
660 fa_rx_util_pkg.assign_column('40', 'pvs.attribute4', ' pvs_attribute4',
661 'jgrx_wt.var.pvs_attribute4','VARCHAR2',150);
662 fa_rx_util_pkg.assign_column('41', 'pvs.attribute5', ' pvs_attribute5',
663 'jgrx_wt.var.pvs_attribute5','VARCHAR2',150);
664 fa_rx_util_pkg.assign_column('42', 'pvs.attribute6', ' pvs_attribute6',
665 'jgrx_wt.var.pvs_attribute6','VARCHAR2', 150);
666 fa_rx_util_pkg.assign_column('43', 'pvs.attribute7', ' pvs_attribute7',
667 'jgrx_wt.var.pvs_attribute7','VARCHAR2', 150);
668 fa_rx_util_pkg.assign_column('44', 'pvs.attribute8', ' pvs_attribute8',
669 'jgrx_wt.var.pvs_attribute8','VARCHAR2', 150);
670 fa_rx_util_pkg.assign_column('45', 'pvs.attribute9', ' pvs_attribute9',
671 'jgrx_wt.var.pvs_attribute9','VARCHAR2', 150);
672 fa_rx_util_pkg.assign_column('46', 'pvs.attribute10', ' pvs_attribute10',
673 'jgrx_wt.var.pvs_attribute10','VARCHAR2', 150);
674 fa_rx_util_pkg.assign_column('47', 'pvs.attribute11', ' pvs_attribute11',
675 'jgrx_wt.var.pvs_attribute11','VARCHAR2', 150);
676 fa_rx_util_pkg.assign_column('48', 'pvs.attribute12', ' pvs_attribute12',
677 'jgrx_wt.var.pvs_attribute12','VARCHAR2', 150);
678 fa_rx_util_pkg.assign_column('49', 'pvs.attribute13', ' pvs_attribute13',
679 'jgrx_wt.var.pvs_attribute13','VARCHAR2', 150);
680 fa_rx_util_pkg.assign_column('50', 'pvs.attribute14', ' pvs_attribute14',
681 'jgrx_wt.var.pvs_attribute14','VARCHAR2', 150);
682 fa_rx_util_pkg.assign_column('51', 'pvs.attribute15', ' pvs_attribute15',
683 'jgrx_wt.var.pvs_attribute15','VARCHAR2', 150);
684 fa_rx_util_pkg.assign_column('52','ap_inv.attribute1', ' inv_attribute1',
685 'jgrx_wt.var.inv_attribute1','VARCHAR2', 150);
686 fa_rx_util_pkg.assign_column('53', 'ap_inv.attribute2', ' inv_attribute2',
687 'jgrx_wt.var.inv_attribute2','VARCHAR2', 150);
688 fa_rx_util_pkg.assign_column('54', 'ap_inv.attribute3', ' inv_attribute3',
689 'jgrx_wt.var.inv_attribute3','VARCHAR2', 150);
690 fa_rx_util_pkg.assign_column('55', 'ap_inv.attribute4', ' inv_attribute4',
691 'jgrx_wt.var.inv_attribute4','VARCHAR2', 150);
692 fa_rx_util_pkg.assign_column('56', 'ap_inv.attribute5', ' inv_attribute5',
693 'jgrx_wt.var.inv_attribute5','VARCHAR2', 150);
694 fa_rx_util_pkg.assign_column('57', 'ap_inv.attribute6', ' inv_attribute6',
695 'jgrx_wt.var.inv_attribute6','VARCHAR2', 150);
696 fa_rx_util_pkg.assign_column('58', 'ap_inv.attribute7', ' inv_attribute7',
697 'jgrx_wt.var.inv_attribute7','VARCHAR2', 150);
698 fa_rx_util_pkg.assign_column('59', 'ap_inv.attribute8', ' inv_attribute8',
699 'jgrx_wt.var.inv_attribute8','VARCHAR2', 150);
700 fa_rx_util_pkg.assign_column('60', 'ap_inv.attribute9', ' inv_attribute9',
701 'jgrx_wt.var.inv_attribute9','VARCHAR2', 150);
702 fa_rx_util_pkg.assign_column('61', 'ap_inv.attribute10', ' inv_attribute10',
703 'jgrx_wt.var.inv_attribute10','VARCHAR2', 150);
704 fa_rx_util_pkg.assign_column('62', 'ap_inv.attribute11', ' inv_attribute11',
705 'jgrx_wt.var.inv_attribute11','VARCHAR2', 150);
706 fa_rx_util_pkg.assign_column('63', 'ap_inv.attribute12', ' inv_attribute12',
707 'jgrx_wt.var.inv_attribute12','VARCHAR2', 150);
708 fa_rx_util_pkg.assign_column('64', 'ap_inv.attribute13', ' inv_attribute13',
709 'jgrx_wt.var.inv_attribute13','VARCHAR2', 150);
710 fa_rx_util_pkg.assign_column('65', 'ap_inv.attribute14', ' inv_attribute14',
711 'jgrx_wt.var.inv_attribute14','VARCHAR2', 150);
712 fa_rx_util_pkg.assign_column('66', 'ap_inv.attribute15', ' inv_attribute15',
713 'jgrx_wt.var.inv_attribute15','VARCHAR2', 150);
714 fa_rx_util_pkg.assign_column('67', 'pvs.country', 'supplier_country',
715 'jgrx_wt.var.supplier_country','VARCHAR2', 25);
716 fa_rx_util_pkg.assign_column('68', 'pvs.address_line1',
717 'supplier_address_line1','jgrx_wt.var.supplier_address_line1','VARCHAR2',240);
718 fa_rx_util_pkg.assign_column('69', 'pvs.address_line2',
719 'supplier_address_line2','jgrx_wt.var.supplier_address_line2','VARCHAR2',240);
720 fa_rx_util_pkg.assign_column('70', 'pvs.address_line3',
721 'supplier_address_line3','jgrx_wt.var.supplier_address_line3','VARCHAR2',240);
722 fa_rx_util_pkg.assign_column('71', 'pvs.city', 'supplier_city',
723 'jgrx_wt.var.supplier_city','VARCHAR2', 25);
724 fa_rx_util_pkg.assign_column('72', 'pvs.zip', 'supplier_postal_code',
725 'jgrx_wt.var.supplier_postal_code','VARCHAR2', 20);
726 fa_rx_util_pkg.assign_column('73', 'pvs.province', 'supplier_province',
727 'jgrx_wt.var.supplier_province','VARCHAR2', 150);
728 fa_rx_util_pkg.assign_column('74', 'pvs.county', 'supplier_county',
729 'jgrx_wt.var.supplier_county','VARCHAR2', 150);
730 fa_rx_util_pkg.assign_column('75', 'pvs.global_attribute1',
731 'pvs_global_attribute1','jgrx_wt.var.supplier_taxable_person','VARCHAR2',150);
732 fa_rx_util_pkg.assign_column('76', 'nvl(pvs.vat_registration_num,
733 pov.vat_registration_num)', 'supplier_tax_registration_num',
734 'jgrx_wt.var.supplier_tax_registration_num','VARCHAR2', 20);
735 fa_rx_util_pkg.assign_column('77','pov.num_1099', 'supplier_taxpayer_id',
736 'jgrx_wt.var.supplier_taxpayer_id','VARCHAR2', 30);
737 fa_rx_util_pkg.assign_column('78','pvs.global_attribute2',
738 'pvs_global_attribute2','jgrx_wt.var.business_inc_sub_category','VARCHAR2',150);
739 fa_rx_util_pkg.assign_column('79', NULL, 'biz_inc_sub_categ_meaning',
740 'jgrx_wt.var.biz_inc_sub_categ_meaning','VARCHAR2', 80);
741 fa_rx_util_pkg.assign_column('80', 'ind.dist_code_combination_id',
742 'dist_code_combination_id','jgrx_wt.var.dist_code_combination_id', 'NUMBER',15);
743 fa_rx_util_pkg.assign_column('81', 'ap_inv.invoice_num', 'transaction_number',
744 'jgrx_wt.var.transaction_number','VARCHAR2', 50);
745 fa_rx_util_pkg.assign_column('82', 'ind.accounting_date', 'accounting_date',
746 'jgrx_wt.var.accounting_date','DATE');
747 fa_rx_util_pkg.assign_column('83', 'ap_inv.voucher_num', 'document_number',
748 'jgrx_wt.var.document_number','VARCHAR2', 50);
749 fa_rx_util_pkg.assign_column('84', 'pov.organization_type_lookup_code',
750 'organization_type', 'jgrx_wt.var.organization_type','VARCHAR2', 25);
751 fa_rx_util_pkg.assign_column('85', NULL, 'org_type_meaning',
752 'jgrx_wt.var.org_type_meaning','VARCHAR2', 80);
753 fa_rx_util_pkg.assign_column('86', 'atc.tax_id', 'tax_id',
754 'jgrx_wt.var.tax_id', 'NUMBER', 15);
755 fa_rx_util_pkg.assign_column('87', 'atr.tax_name', 'tax_code',
756 'jgrx_wt.var.tax_code','VARCHAR2', 15);
757 fa_rx_util_pkg.assign_column('88', 'atc.description', 'awt_description',
758 'jgrx_wt.var.awt_description','VARCHAR2', 240);
759 fa_rx_util_pkg.assign_column('89', 'atc.tax_type', 'tax_type',
760 'jgrx_wt.var.tax_type','VARCHAR2', 25);
761 fa_rx_util_pkg.assign_column('90', 'atr.tax_rate_id', 'tax_rate_id',
762 'jgrx_wt.var.tax_rate_id','NUMBER', 15);
763 fa_rx_util_pkg.assign_column('91', 'atr.tax_rate', 'tax_rate',
764 'jgrx_wt.var.tax_rate', 'NUMBER');
765 fa_rx_util_pkg.assign_column('92', 'atc.global_attribute6',
766 'atc_global_attribute6','jgrx_wt.var.recognized_expense_percent',
767 'VARCHAR2', 150);
768 fa_rx_util_pkg.assign_column('93', 'atc.global_attribute7',
769 'atc_global_attribute7', 'jgrx_wt.var.nominal_tax_rate', 'VARCHAR2',150);
770 fa_rx_util_pkg.assign_column('94', 'atc.global_attribute1',
771 'atc_global_attribute1', 'jgrx_wt.var.tax_location', 'VARCHAR2', 150);
772 fa_rx_util_pkg.assign_column('95', 'atc.global_attribute4',
773 'atc_global_attribute4', 'jgrx_wt.var.withholding_tax_type', 'VARCHAR2',150);
774 fa_rx_util_pkg.assign_column('96', NULL,
775 'wh_tax_type_meaning', 'jgrx_wt.var.wh_tax_type_meaning', 'VARCHAR2',80);
776 fa_rx_util_pkg.assign_column('97', 'atc.global_attribute9',
777 'atc_global_attribute9','jgrx_wt.var.resident_inc_categ_code','VARCHAR2',150);
778 fa_rx_util_pkg.assign_column('98', NULL,
779 'res_inc_categ_meaning', 'jgrx_wt.var.res_inc_categ_meaning','VARCHAR2',80);
780 fa_rx_util_pkg.assign_column('99', 'atc.global_attribute5',
781 'atc_global_attribute5','jgrx_wt.var.foreign_inc_categ_code','VARCHAR2',150);
782 fa_rx_util_pkg.assign_column('100', NULL,
783 'for_inc_categ_meaning', 'jgrx_wt.var.for_inc_categ_meaning', 'VARCHAR2',80);
784 fa_rx_util_pkg.assign_column('101', 'pv1.vendor_name',
785 'tax_authority_name', 'jgrx_wt.var.tax_authority_name', 'VARCHAR2',240);
786 fa_rx_util_pkg.assign_column('102', 'ap_inv.payment_status_flag',
787 'status', 'jgrx_wt.var.status', 'VARCHAR2',1);
788 fa_rx_util_pkg.assign_column('103', 'decode(atc.global_attribute4,''INCOME'',
789 nvl(ind.base_amount,ind.amount),0)', 'income_tax', 'jgrx_wt.var.income_tax' ,'NUMBER');
790 fa_rx_util_pkg.assign_column('104','decode(atc.global_attribute4,''RESIDENT'',
791 nvl(ind.base_amount,ind.amount),0)','resident_tax', 'jgrx_wt.var.resident_tax' ,'NUMBER');
792 fa_rx_util_pkg.assign_column('105', NULL, 'total_wht_amount',
793 'jgrx_wt.var.total_wht_amount' ,'NUMBER');
794 fa_rx_util_pkg.assign_column('106','decode(ap_sp.create_awt_dists_type,''PAYMENT
795 '',ap_inv.payment_currency_code, NULL)','payment_currency',
796 'jgrx_wt.var.payment_currency' , 'VARCHAR2', 15);
797 fa_rx_util_pkg.assign_column('107','decode(ap_sp.create_awt_dists_type,''PAYMENT
798 '',aip.invoice_payment_id, NULL)','invoice_payment_id',
799 'jgrx_wt.var.invoice_payment_id' , 'NUMBER', 15);
800 fa_rx_util_pkg.assign_column('108','decode(ap_sp.create_awt_dists_type,''PAYMENT''
801 ,nvl(aip.payment_base_amount,aip.amount), NULL)','payment_amount', 'jgrx_wt.var.payment_amount' , 'NUMBER');
802 fa_rx_util_pkg.assign_column('109','decode(ap_sp.create_awt_dists_type,
803 ''PAYMENT'',apc.check_date, NULL)','payment_date',
804 'jgrx_wt.var.payment_date' , 'DATE');
805 fa_rx_util_pkg.assign_column('110','decode(ap_sp.create_awt_dists_type,
806 ''PAYMENT'', aip.payment_num, NULL)','payment_number',
807 'jgrx_wt.var.payment_number','NUMBER', 15);
808 fa_rx_util_pkg.assign_column('111','decode(ap_sp.create_awt_dists_type,''PAYMENT''
809 ,apc.check_id, NULL)','check_id', 'jgrx_wt.var.check_id' , 'NUMBER', 15);
810 fa_rx_util_pkg.assign_column('112','decode(ap_sp.create_awt_dists_type,
811 ''PAYMENT'', apc.check_number, NULL)','check_number',
812 'jgrx_wt.var.check_number','NUMBER',15);
813 fa_rx_util_pkg.assign_column('113','decode(ap_sp.create_awt_dists_type,
814 ''PAYMENT'', nvl(apc.base_amount,apc.amount), NULL)','check_amount',
815 'jgrx_wt.var.check_amount' , 'NUMBER');
816 fa_rx_util_pkg.assign_column('114','ap_inv.invoice_id','invoice_id',
817 'jgrx_wt.var.invoice_id' , 'NUMBER', 15);
818 fa_rx_util_pkg.assign_column('115','ind.invoice_distribution_id',
819 'invoice_distribution_id','jgrx_wt.var.invoice_distribution_id','NUMBER', 15);
820 -- Added to handle foreign currency invoices
821 fa_rx_util_pkg.assign_column('116','nvl(ap_inv.base_amount,ap_inv.invoice_amount)', 'invoice_amount', 'jgrx_wt.var.invoice_amount' , 'NUMBER');
822 fa_rx_util_pkg.assign_column('117','ap_inv.invoice_date','invoice_date',
823 'jgrx_wt.var.invoice_date' , 'DATE');
824 fa_rx_util_pkg.assign_column('118','ap_inv.invoice_currency_code','currency_code', 'jgrx_wt.var.currency_code' , 'VARCHAR2', 15);
825 fa_rx_util_pkg.assign_column('119',NULL,'func_currency_code',
826 'jgrx_wt.var.func_currency_code' , 'VARCHAR2', 15);
827 fa_rx_util_pkg.assign_column('120','ind.awt_gross_amount*nvl(ap_inv.exchange_rate
828 ,1)','amt_subject_to_wh', 'jgrx_wt.var.amt_subject_to_wh' ,'NUMBER');
829 fa_rx_util_pkg.assign_column('121',NULL, 'recognized_expense_amt',
830 'jgrx_wt.var.recognized_expense_amt','NUMBER');
831 fa_rx_util_pkg.assign_column('122',NULL, 'inc_wh_tax_base_amt',
832 'jgrx_wt.var.inc_wh_tax_base_amt' ,'NUMBER');
833 fa_rx_util_pkg.assign_column('123',NULL, 'res_wh_tax_base_amt',
834 'jgrx_wt.var.res_wh_tax_base_amt' ,'NUMBER');
835 fa_rx_util_pkg.assign_column('124',NULL, 'total_tax_base_amt',
836 'jgrx_wt.var.total_tax_base_amt','NUMBER');
837 fa_rx_util_pkg.assign_column('125', NULL, 'net_amount',
838 'jgrx_wt.var.net_amount', 'NUMBER');
839 fa_rx_util_pkg.assign_column('126','ind.distribution_line_number',
840 'line_number', 'jgrx_wt.var.line_number' , 'NUMBER', 15);
841 fa_rx_util_pkg.assign_column('127','ind.type_1099','TYPE_1099',
842 'jgrx_wt.var.type_1099' , 'VARCHAR2', 10);
843 fa_rx_util_pkg.assign_column('128','ind.description', 'item_description',
844 'jgrx_wt.var.item_description' , 'VARCHAR2', 240);
845 fa_rx_util_pkg.assign_column('129', 'xle.registration_number',
846 'hrl_global_attribute11', 'jgrx_wt.var.corporate_id_number', 'VARCHAR2', 150);
847 fa_rx_util_pkg.assign_column('130','hrl1.telephone_number_2','location_fax',
848 'jgrx_wt.var.location_fax', 'VARCHAR2', 60);
849 fa_rx_util_pkg.assign_column('131', NULL, 'accounting_flexfield',
850 'jgrx_wt.var.accounting_flexfield', 'VARCHAR2', 1000);
851 fa_rx_util_pkg.assign_column('132',NULL, 'supp_concatenated_address',
852 'jgrx_wt.var.supp_concatenated_address' , 'VARCHAR2', 800);
853 fa_rx_util_pkg.assign_column('133','ou.name','organization_name',
854 'jgrx_wt.var.organization_name' , 'VARCHAR2', 60);
855 fa_rx_util_pkg.assign_column('134',NULL,'reporting_entity_name',
856 'jgrx_wt.var.reporting_entity_name','VARCHAR2', 15);
857 fa_rx_util_pkg.assign_column('135',NULL,'reporting_sob_name',
858 'jgrx_wt.var.reporting_sob_name' , 'VARCHAR2', 15);
859 fa_rx_util_pkg.assign_column('136', NULL, 'sob_id','jgrx_wt.var.sob_id' ,
860 'VARCHAR2', 15);
861 fa_rx_util_pkg.assign_column('137','ap_sp.create_awt_dists_type',
862 NULL, 'jgrx_wt.var.create_dist' , 'VARCHAR2', 15);
863 fa_rx_util_pkg.assign_column('138','ap_inv.org_id',
864 NULL, 'jgrx_wt.var.org_id' , 'NUMBER', 15);
865 fa_rx_util_pkg.assign_column('139',NULL, 'nominal_or_reg_tax_rate',
866 'jgrx_wt.var.nominal_or_reg_tax_rate', 'NUMBER');
867 fa_rx_util_pkg.assign_column('140','ind.invoice_line_number',
868 'invoice_line_num', 'jgrx_wt.var.invoice_line_number', 'NUMBER');
869
870
871 END CONSTRUCT_SELECT;
872
873 PROCEDURE CONSTRUCT_FROM
874 IS
875 BEGIN
876 -- Assign the FROM clause
877 fa_rx_util_pkg.debug('jgrx_wt.construct_from()-');
878 fa_rx_util_pkg.From_clause := 'ap_invoices_all ap_inv,
879 ap_invoice_distributions_all ind,
880 po_vendors pov,
881 po_vendors pv1,
882 po_vendor_sites_all pvs,
883 ap_tax_codes_all atc,
884 ap_awt_tax_rates_all atr,
885 hr_locations_all hrl1,
886 xle_firstparty_information_v xle,
887 ap_invoice_payments_all aip,
888 ap_checks_all apc,
889 gl_code_combinations cc,
890 ap_system_parameters_all ap_sp,
891 hr_all_organization_units ou,
892 hr_organization_information oi';
893
894
895 fa_rx_util_pkg.debug('jgrx_wt.construct_from()+');
896
897 END CONSTRUCT_FROM;
898
899 PROCEDURE CONSTRUCT_WHERE
900 IS
901 l_reporting_context VARCHAR2(25);
902 l_where_reporting_context_inv VARCHAR2(500);
903 l_where_reporting_context_ind VARCHAR2(500);
904 l_where_reporting_context_atc VARCHAR2(500);
905 l_where_reporting_context_atr VARCHAR2(500);
906 l_where_reporting_context_pvs VARCHAR2(500);
907 l_where_reporting_context_aip VARCHAR2(500);
908 l_where_reporting_context_apc VARCHAR2(500);
909 l_where_reporting_context_asp VARCHAR2(500);
910
911 BEGIN
912 -- Assign the WHERE clause
913 fa_rx_util_pkg.debug('jgrx_wt.construct_Where()-');
914
915 -- This call to be Changed due to MOAC uptake
916 -- Condition to check if the report is run for LE or for cross-org
917
918
919 l_reporting_context := parm.p_reporting_context;
920
921 if parm.p_legal_entity_id is null then
922
923 fnd_mo_reporting_api.initialize(parm.p_reporting_level,
924 l_reporting_context, 'AUTO');
925 l_where_reporting_context_inv := fnd_mo_reporting_api.get_predicate('AP_INV', NULL, l_reporting_context);
926 l_where_reporting_context_ind := fnd_mo_reporting_api.get_predicate('IND', NULL, l_reporting_context);
927 l_where_reporting_context_atc := fnd_mo_reporting_api.get_predicate('ATC', NULL, l_reporting_context);
928 l_where_reporting_context_atr := fnd_mo_reporting_api.get_predicate('ATR', NULL, l_reporting_context);
929 l_where_reporting_context_pvs := fnd_mo_reporting_api.get_predicate('PVS', NULL, l_reporting_context);
930 l_where_reporting_context_asp := fnd_mo_reporting_api.get_predicate('AP_SP', NULL, l_reporting_context);
931 l_where_reporting_context_aip := fnd_mo_reporting_api.get_predicate('AIP', NULL, l_reporting_context);
932 l_where_reporting_context_apc := fnd_mo_reporting_api.get_predicate('APC', NULL, l_reporting_context);
933
934 else
935 -- Set LE ID on tables with LE stamped.
936 -- XLE view condition set on before wh procedure.
937
938 l_where_reporting_context_inv := '';
939 l_where_reporting_context_aip := '';
940 l_where_reporting_context_apc := '';
941 l_where_reporting_context_ind := '';
942 l_where_reporting_context_atc := '';
943 l_where_reporting_context_atr := '';
944 l_where_reporting_context_pvs := '';
945 l_where_reporting_context_asp := '';
946
947 end if;
948
949 -- legal entity id is set at running time.
950 -- also removed:and ou.organization_id = oi.org_information2
951
952 fa_rx_util_pkg.where_clause :=
953 ' ap_inv.invoice_id = ind.invoice_id
954 and ind.line_type_lookup_code= ''AWT''
955 and ap_inv.vendor_id = pov.vendor_id
956 and ap_inv.vendor_site_id = pvs.vendor_site_id
957 and ind.WITHHOLDING_tax_code_id = atc.tax_id
958 /* Commented out NOCOPY the following condition to consider manual wh.
959 and ind.awt_tax_rate_id = atr.tax_rate_id */
960 and atc.name = atr.tax_name
961 and ind.accounting_date >= nvl(atr.start_date,ind.accounting_date)
962 and ind.accounting_date <= nvl(atr.end_date,ind.accounting_date)
963 and cc.code_combination_id = ind.dist_code_combination_id
964 and pv1.vendor_id = atc.awt_vendor_id
965 and hrl1.location_id = atc.global_attribute1
966 and aip.invoice_payment_id(+) = ind.awt_invoice_payment_id
967 and apc.check_id(+) = aip.check_id
968 and ap_inv.legal_entity_id = xle.legal_entity_id
969 and oi.organization_id = ap_inv.org_id
970 and ap_sp.org_id = ap_inv.org_id
971 and oi.org_information_context = ''Operating Unit Information''
972 and ou.organization_id = oi.org_information2
973 ' || l_where_reporting_context_inv
974 || l_where_reporting_context_ind
975 || l_where_reporting_context_pvs
976 || l_where_reporting_context_atc
977 || l_where_reporting_context_atr
978 || l_where_reporting_context_asp
979 || l_where_reporting_context_aip
980 || l_where_reporting_context_apc ;
981
982 END CONSTRUCT_WHERE;
983
984 /**************************************************************************
985 * *
986 * Name : build_gbl_lookup_table *
987 * Purpose : This procedure builds p_gbl_lookup_table for use by *
988 * function get_lookup_meaning *
989 * *
990 **************************************************************************/
991 PROCEDURE build_gbl_lookup_table is
992 cursor lookup_meaning_cursor is
993 Select 'PO', lookup_type, lookup_code, displayed_field
994 from po_lookup_codes
995 where lookup_type in ('ORGANIZATION TYPE')
996 and sysdate < nvl(inactive_date, sysdate+1)
997 union all
998 select 'FND',lookup_type, lookup_code, meaning
999 from fnd_lookups
1000 where lookup_type in ('JAKR_AP_AWT_BIZ_INC_SUB_CAT',
1001 'JAKR_AP_AWT_TAX_TYPE',
1002 'JAKR_AP_AWT_INC_CAT_DOMESTIC',
1003 'JAKR_AP_AWT_INC_CAT_FOREIGN')
1004 and enabled_flag = 'Y';
1005 l_index number := 0;
1006 Begin
1007 fa_rx_util_pkg.debug('build_gbl_lookup_table()+');
1008
1009 open lookup_meaning_cursor;
1010 loop
1011 fetch lookup_meaning_cursor into lookup_meaning_rec;
1012 exit when lookup_meaning_cursor%notfound;
1013 l_index := l_index + 1;
1014 p_gbl_lookup_table(l_index) := lookup_meaning_rec;
1015 end loop;
1016
1017 if lookup_meaning_cursor%isopen then
1018 close lookup_meaning_cursor;
1019 end if;
1020 fa_rx_util_pkg.debug('build_gbl_lookup_table()-');
1021 Exception
1022 when others then
1023 fa_rx_util_pkg.debug(
1024 'Exception in build_gbl_lookup_table:'||
1025 SQLCODE||';'||SQLERRM);
1026 if lookup_meaning_cursor%isopen then
1027 close lookup_meaning_cursor;
1028 end if;
1029 End build_gbl_lookup_table ;
1030
1031
1032 PROCEDURE append_errbuf(p_msg in varchar2) is
1033 BEGIN
1034 if nvl(length(L_ERRBUF),0) = 0 THEN
1035 L_ERRBUF := p_msg;
1036 elsif nvl(length(L_ERRBUF),0) < 2000 - nvl(length(p_msg),0) then
1037 L_ERRBUF := L_ERRBUF ||';'||p_msg;
1038 end if;
1039 L_ERRBUF := L_ERRBUF || fnd_global.newline;
1040 END append_errbuf;
1041
1042 PROCEDURE set_retcode(p_retcode in number) is
1043 BEGIN
1044 If p_retcode = 2 then
1045 L_RETCODE := p_retcode;
1046 elsif p_retcode = 1 then
1047 IF L_RETCODE = 2 then
1048 NULL;
1049 ELSE
1050 L_RETCODE := p_retcode;
1051 END IF;
1052 end if;
1053 END set_retcode;
1054
1055 END jgrx_wt;