[Home] [Help]
PACKAGE BODY: APPS.APRX_WT
Source
1 PACKAGE BODY APRX_WT AS
2 /* $Header: aprxwtb.pls 120.2 2005/07/29 11:27:58 mswamina noship $ */
3
4 -- Structure to hold values of parameters
5 -- These include parameters which are passed in the core and all the plug-ins
6 type param_t is record (
7 p_date_from varchar2(20),
8 p_date_to varchar2(20),
9 p_supplier_from varchar2(80),
10 p_supplier_to varchar2(80),
11 p_supplier_type varchar2(25),
12 p_system_acct_method varchar2(240)
13 );
14 parm param_t;
15
16 -- Core Report function
17
18 PROCEDURE GET_WITHOLDING_TAX (
19 request_id in number,
20 section_name in varchar2,
21 retcode out NOCOPY number,
22 errbuf out NOCOPY varchar2
23 )
24 IS
25 BEGIN
26
27 fa_rx_util_pkg.debug('aprx_wt.get_witholding_tax()+');
28
29 -- Initialize request
30
31 fa_rx_util_pkg.init_request('aprx_wt.get_witholding_tax', request_id);
32
33 fa_rx_util_pkg.assign_report(section_name,
34 true,
35 'aprx_wt.before_report;',
36 NULL,
37 NULL,
38 NULL);
39
40 fa_rx_util_pkg.run_report('aprx_wt.get_witholding_tax', retcode, errbuf);
41
42 fa_rx_util_pkg.debug('aprx_wt.get_witholding_tax()-');
43
44 END GET_WITHOLDING_TAX;
45
46 -- This procedure is a plug-in for the (Tax Letter) Report
47
48 PROCEDURE ap_wht_tax_report (
49 p_date_from in varchar2,
50 p_date_to in varchar2,
51 p_supplier_from in varchar2,
52 p_supplier_to in varchar2,
53 p_supplier_type in varchar2,
54 request_id in number,
55 retcode out NOCOPY number,
56 errbuf out NOCOPY varchar2
57 )
58 IS
59 BEGIN
60 fa_rx_util_pkg.debug('aprx_wt.ap_wht_tax_report()+');
61 fa_rx_util_pkg.debug('p_date_from '||p_date_from);
62 fa_rx_util_pkg.debug('canonical date from '||to_char(fnd_date.canonical_to_date(p_date_from)));
63 fa_rx_util_pkg.init_request('aprx_wt.ap_wht_tax_report', request_id);
64
65 -- Store the paremters in a variable which can be accesed globally accross all procedures
66 parm.p_date_from := p_date_from;
67 parm.p_date_to := p_date_to;
68 parm.p_supplier_from := p_supplier_from;
69 parm.p_supplier_to := p_supplier_to;
70 parm.p_supplier_type := p_supplier_type;
71
72 -- Call the core report.This executes the core report and the SELECT statement of the core
73 -- is built.Now the plug-in has to add only what is specific to it.
74 -- No data is inserted into the interface table.
75
76 aprx_wt.get_witholding_tax (
77 request_id,
78 'get_witholding_tax',
79 retcode,
80 errbuf);
81
82 -- Continue with the execution of the plug-in
83 fa_rx_util_pkg.assign_report('get_witholding_tax',
84 true,
85 'aprx_wt.awt_before_report;',
86 'aprx_wt.awt_bind(:CURSOR_SELECT);',
87 NULL, null);
88
89 fa_rx_util_pkg.run_report('aprx_wt.ap_wht_tax_report', retcode, errbuf);
90
91 fa_rx_util_pkg.debug('aprx_wt.ap_wht_tax_report()-');
92
93 END ap_wht_tax_report;
94
95 /*=======================================================================================================
96
97 CORE REPORT
98
99 ========================================================================================================*/
100
101
102 -- This is the before report trigger for the main Report. The code which is written in the " BEFORE
103 -- REPORT " triggers has been incorporated over here. The code is the common code accross all the
104 -- reports.
105
106 PROCEDURE before_report
107 IS
108 BEGIN
109 fa_rx_util_pkg.debug('aprx_wt.before_report()+');
110
111
112 fa_rx_util_pkg.debug('GL_SET_OF_BKS_ID');
113
114 --
115 -- Get Profile GL_SET_OF_BKS_ID
116 --
117 fa_rx_util_pkg.debug('GL_GET_PROFILE_BKS_ID');
118 fnd_profile.get(
119 name => 'GL_SET_OF_BKS_ID',
120 val => var.books_id);
121
122 --
123 -- Get CHART_OF_ACCOUNTS_ID
124 --
125 fa_rx_util_pkg.debug('GL_GET_CHART_OF_ACCOUNTS_ID');
126
127 select CURRENCY_CODE,NAME
128 into var.functional_currency_code
129 , var.organization_name
130 from GL_SETS_OF_BOOKS
131 where SET_OF_BOOKS_ID = var.books_id;
132
133
134
135
136 -- Bug 1759331
137
138
139 /* SELECT name
140 INTO var.organization_name
141 FROM hr_organization_units
142 WHERE organization_id = FND_PROFILE.GET('ORG_ID');
143
144 SELECT currency_code
145 INTO var.functional_currency_code
146 FROM gl_sets_of_books
147 WHERE set_of_books_id = FND_PROFILE.GET('GL_SET_OF_BKS_ID');
148 */
149
150
151
152 -- Get Company Information and store in placeholder variables
153 BEGIN
154 SELECT hrl.address_line_1,
155 hrl.address_line_2,
156 hrl.address_line_3,
157 hrl.town_or_city,
158 hrl.country,
159 hrl.postal_code,
160 hrl.region_1,
161 hrl.region_2
162 INTO var.Address_line1,
163 var.address_line2,
164 var.address_line3,
165 var.city,
166 var.country,
167 var.zip,
168 var.province,
169 var.state
170 FROM hr_locations hrl
171 WHERE hrl.location_id = JG_ZZ_COMPANY_INFO.get_location_id;
172
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 RAISE_APPLICATION_ERROR(-20010,sqlerrm);
176 END;
177
178 --Assign SELECT list
179 -- the Select statement is build over here
180
181 -- fa_rx_util_pkg.assign_column(#, select, insert, place, type, len);
182
183 -->>SELECT_START<<--
184
185 fa_rx_util_pkg.assign_column('1', 'pv1.vendor_name', 'tax_authority','aprx_wt.var.tax_authority','VARCHAR2', 240);
186
187 fa_rx_util_pkg.assign_column('2', 'pv2.vendor_type_lookup_code', 'supplier_type','aprx_wt.var.supplier_type','VARCHAR2',25);
188
189 fa_rx_util_pkg.assign_column('3', 'pv2.vendor_name', 'supplier_name', 'aprx_wt.var.supplier_name','VARCHAR2',240);
190
191 fa_rx_util_pkg.assign_column('4', 'pv2.num_1099', 'taxpayer_id','aprx_wt.var.taxpayer_id','VARCHAR2',30);
192
193 fa_rx_util_pkg.assign_column('5', 'pv2.segment1', 'supplier_number', 'aprx_wt.var.supplier_number','VARCHAR2',30);
194
195 fa_rx_util_pkg.assign_column('6', 'pvs.vendor_site_code', 'supplier_site_code', 'aprx_wt.var.supplier_site_code','VARCHAR2',15);
196
197 fa_rx_util_pkg.assign_column('7', 'pvs.vat_registration_num', 'vat_registration_number', 'aprx_wt.var.vat_registration_number','VARCHAR2',20);
198
199
200 fa_rx_util_pkg.assign_column('8', 'pvs.address_line1', 'supplier_address_line1', 'aprx_wt.var.supplier_address_line1','VARCHAR2',240);
201
202 fa_rx_util_pkg.assign_column('9', 'pvs.address_line2', 'supplier_address_line2', 'aprx_wt.var.supplier_address_line2','VARCHAR2',240);
203
204 fa_rx_util_pkg.assign_column('10', 'pvs.address_line3', 'supplier_address_line3', 'aprx_wt.var.supplier_address_line3','VARCHAR2',240);
205
206 fa_rx_util_pkg.assign_column('11', 'pvs.city', 'supplier_city', 'aprx_wt.var.supplier_city','VARCHAR2',25);
207
208 fa_rx_util_pkg.assign_column('12', 'pvs.state', 'supplier_state', 'aprx_wt.var.supplier_state','VARCHAR2',150);
209
210 fa_rx_util_pkg.assign_column('13', 'pvs.zip', 'supplier_zip', 'aprx_wt.var.supplier_zip','VARCHAR2',20);
211
212 fa_rx_util_pkg.assign_column('14', 'pvs.province', 'supplier_province', 'aprx_wt.var.supplier_province','VARCHAR2',150);
213
214 fa_rx_util_pkg.assign_column('15', 'pvs.country', 'supplier_country', 'aprx_wt.var.supplier_country','VARCHAR2',25);
215
216 fa_rx_util_pkg.assign_column('16', 'ai.invoice_num', 'invoice_num', 'aprx_wt.var.invoice_num','VARCHAR2',50);
217
218 fa_rx_util_pkg.assign_column('17', 'ai.invoice_amount', 'invoice_amount', 'aprx_wt.var.invoice_amount','NUMBER');
219
220 fa_rx_util_pkg.assign_column('18', 'ai.invoice_currency_code', 'invoice_currency_code', 'aprx_wt.var.invoice_currency_code','VARCHAR2',15);
221
222 fa_rx_util_pkg.assign_column('19', 'ai.invoice_date', 'invoice_date', 'aprx_wt.var.invoice_date','DATE');
223
224 fa_rx_util_pkg.assign_column('20', 'atr.tax_name', 'awt_code', 'aprx_wt.var.awt_code','VARCHAR2',15);
225
226 fa_rx_util_pkg.assign_column('21', 'atr.tax_rate', 'awt_rate', 'aprx_wt.var.awt_rate','NUMBER');
227
228 fa_rx_util_pkg.assign_column('22', 'nvl(aid.amount*(-1),0)', 'awt_amount', 'aprx_wt.var.awt_amount','NUMBER');
229
230 fa_rx_util_pkg.assign_column('23', 'nvl(aid.base_amount*(-1),aid.amount*(-1))', 'awt_base_amount', 'aprx_wt.var.awt_base_amount','NUMBER');
231
232 fa_rx_util_pkg.assign_column('24', 'atg.name', 'awt_group_name', 'aprx_wt.var.awt_group_name','VARCHAR2',25);
233
234 fa_rx_util_pkg.assign_column('25', 'aid.accounting_date', 'awt_gl_date', 'aprx_wt.var.awt_gl_date','DATE');
235
236 fa_rx_util_pkg.assign_column('26', 'aid.awt_gross_amount', 'awt_gross_amount', 'aprx_wt.var.awt_gross_amount','NUMBER');
237
238
239 fa_rx_util_pkg.assign_column('27', NULL, 'address_line1','aprx_wt.var.address_line1', 'VARCHAR2',240);
240
241 fa_rx_util_pkg.assign_column('28', NULL, 'address_line2','aprx_wt.var.address_line2', 'VARCHAR2',240);
242
243 fa_rx_util_pkg.assign_column('29', NULL, 'address_line3','aprx_wt.var.address_line3', 'VARCHAR2',240);
244
245 fa_rx_util_pkg.assign_column('30', NULL, 'city','aprx_wt.var.city', 'VARCHAR2',30);
246
247 fa_rx_util_pkg.assign_column('31', NULL, 'zip','aprx_wt.var.zip', 'VARCHAR2',30);
248
249 fa_rx_util_pkg.assign_column('32', NULL, 'country','aprx_wt.var.country', 'VARCHAR2',60);
250
251 fa_rx_util_pkg.assign_column('33', NULL, 'organization_name','aprx_wt.var.organization_name', 'VARCHAR2',240);
252
253 fa_rx_util_pkg.assign_column('34', NULL, 'functional_currency_code','aprx_wt.var.functional_currency_code', 'VARCHAR2',15);
254
255 fa_rx_util_pkg.assign_column('35', NULL, 'province','aprx_wt.var.province', 'VARCHAR2',150);
256
257 fa_rx_util_pkg.assign_column('36', NULL, 'state','aprx_wt.var.state', 'VARCHAR2',150);
258
259 -->>SELECT_END<<--
260
261
262 --
263 -- Assign From Clause
264 --
265 fa_rx_util_pkg.From_Clause :=
266 'po_vendors pv2,
267 po_vendors pv1,
268 po_vendor_sites pvs ,
269 ap_invoices ai,
270 ap_invoice_distributions aid,
271 ap_tax_codes atc,
272 ap_awt_groups atg,
273 ap_awt_tax_rates atr ';
274
275
276 -- Assign Where Clause
277
278 -- Bug 2825570. Add filter on invoice_date between start and end dates.
279
280 fa_rx_util_pkg.Where_Clause := 'pv2.vendor_id = ai.vendor_id and
281 pvs.vendor_site_id = ai.vendor_site_id and
282 atg.group_id(+) = aid.awt_origin_group_id and
283 ai.invoice_id = aid.invoice_id and
284 aid.tax_code_id = atc.tax_id and
285 atc.name = atr.tax_name and
286 aid.line_type_lookup_code=''AWT'' and
287 ai.invoice_date between NVL(atr.start_date , ai.invoice_date) and NVL(atr.end_date , ai.invoice_date) and
288 atc.awt_vendor_id = pv1.vendor_id';
289
290 fa_rx_util_pkg.debug('aprx_wt.before_report()-');
291
292 END BEFORE_REPORT;
293
294
295 -- The after fetch trigger fires after the Select statement has executed
296 /*
297 PROCEDURE after_fetch IS
298 BEGIN
299
300 END;
301 */
302
303 /*=============================================================================================
304
305 END OF CORE REPORT
306
307 ===============================================================================================*/
308
309
310 /*===============================================================================================
311
312 AP Witholding Tax Letter Report(Plug-In)
313
314 ================================================================================================*/
315
316
317 -- This is the before report trigger for the Plug-In. The code which is specific to the AP Witholding Tax report and Letter is written here.
318
319 PROCEDURE awt_before_report IS
320 CURSOR c_methods IS
321 SELECT UPPER(accounting_method_option),
322 UPPER(secondary_accounting_method)
323 FROM ap_system_parameters;
324 first_acct_method ap_system_parameters.accounting_method_option%TYPE;
325 second_acct_method ap_system_parameters.secondary_accounting_method%TYPE;
326
327 BEGIN
328 OPEN c_methods;
329 FETCH c_methods INTO first_acct_method, second_acct_method;
330 CLOSE c_methods;
331 IF (
332 (first_acct_method = 'ACCRUAL')
333 and
334 (
335 (second_acct_method = 'ACCRUAL')
336 or
337 (second_acct_method = 'NONE')
338 or
339 (second_acct_method is null)
340 )
341 ) THEN
342 parm.P_System_Acct_Method := 'ACCRUAL';
343 ELSIF (
344 (first_acct_method = 'CASH')
345 and
346 (
347 (second_acct_method = 'CASH')
348 or
349 (second_acct_method = 'NONE')
350 or
351 (second_acct_method is null)
352 )
353 ) THEN
354 parm.P_System_Acct_Method := 'CASH';
355 ELSE
356 parm.P_System_Acct_Method := 'BOTH';
357 END IF;
358
359 fa_rx_util_pkg.debug('system_acct_method'||parm.P_System_Acct_Method);
363 IF parm.p_date_from IS NOT NULL THEN
360
361 -- Add the WHERE clause which is specific to the AP Witholding Report
362
364 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause || ' and aid.accounting_date >= fnd_date.canonical_to_date(:b_date_from)' ;
365 END IF;
366
367 IF parm.p_date_to IS NOT NULL THEN
368 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause || ' and aid.accounting_date <= fnd_date.canonical_to_date(:b_date_to) ';
369
370 END IF;
371
372 IF parm.p_supplier_from IS NOT NULL THEN
373 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause || ' and UPPER(pv2.vendor_name) >= UPPER(:b_supplier_from)';
374 END IF;
375
376 IF parm.p_supplier_to IS NOT NULL THEN
377 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause || ' and UPPER(pv2.vendor_name) <= UPPER(:b_supplier_to)' ;
378 END IF;
379
380 IF parm.p_supplier_type IS NOT NULL THEN
381 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause || ' and pv2.vendor_type_lookup_code = :b_supplier_type';
382 END IF;
383
384 IF parm.p_system_acct_method IS NOT NULL THEN
385 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and aid.accrual_posted_flag = decode(:b_system_acct_method,''ACCRUAL'',''Y'',''BOTH'',''Y'',aid.accrual_posted_flag)';
386 fa_rx_util_pkg.Where_clause := fa_rx_util_pkg.Where_clause ||' and aid.cash_posted_flag = decode(:b_system_acct_method,''CASH'',''Y'',''BOTH'',''Y'',aid.cash_posted_flag)';
387 END IF;
388
389 fa_rx_util_pkg.debug('aprx_wt.awt_before_report()-');
390
391 END awt_before_report;
392
393 -- This is the bind trigger for the
394 PROCEDURE awt_bind(c in integer)
395 IS
396 b_date_from varchar2(20);
397 b_date_to varchar2(20);
398 b_supplier_from varchar2(80);
399 b_supplier_to varchar2(80);
400 b_supplier_type varchar2(25);
401 b_system_acct_method varchar2(240);
402 BEGIN
403 fa_rx_util_pkg.debug('aprx_wt.awt_bind()+');
404
405 IF parm.p_date_from is not null then
406 fa_rx_util_pkg.debug('Binding b_date_from');
407 dbms_sql.bind_variable(c, 'b_date_from', parm.p_date_from);
408 END IF;
409
410 IF parm.p_date_to is not null then
411 fa_rx_util_pkg.debug('Binding b_date_to');
412 dbms_sql.bind_variable(c, 'b_date_to', parm.p_date_to);
413 END IF;
414
415 IF parm.p_supplier_from is not null then
416 fa_rx_util_pkg.debug('Binding b_supplier_from');
417 dbms_sql.bind_variable(c, 'b_supplier_from', parm.p_supplier_from);
418 END IF;
419
420 IF parm.p_supplier_to is not null then
421 fa_rx_util_pkg.debug('Binding b_supplier_to');
422 dbms_sql.bind_variable(c, 'b_supplier_to', parm.p_supplier_to);
423 END IF;
424
425 IF parm.p_supplier_type is not null then
426 fa_rx_util_pkg.debug('Binding b_supplier_type_from');
427 dbms_sql.bind_variable(c, 'b_supplier_type', parm.p_supplier_type);
428 END IF;
429
430 IF parm.p_system_acct_method is not null then
431 fa_rx_util_pkg.debug('Binding b_system_acct_method');
432 dbms_sql.bind_variable(c, 'b_system_acct_method', parm.p_system_acct_method);
433 END IF;
434
435 fa_rx_util_pkg.debug('aprx_wt.awt_bind()-');
436
437 END awt_bind;
438
439
440 /*=============================================================================================
441
442 END OF AP WITHHOLDING TAX REPORT
443
444 ===============================================================================================*/
445
446 END APRX_WT;