1 PACKAGE BODY JE_IL_TAX_PKG
2 -- $Header: jeilwhtapb.pls 120.11.12010000.2 2008/08/04 12:27:28 vgadde ship $
3 AS
4
5 /*
6 REM +======================================================================+
7 REM Name: IS_NUMBER
8 REM
9 REM Description: This function is called in the query Q_FIRST_PARTY ,Q_VENDOR_SITE
10 REM of data template, for validating the Deduction File Number and
11 REM Taxpayer Id of the company and Taxpayer id of the supplier.
12 REM
13 REM Parameters:
14 REM p_str1 : String needs to be validated.
15 REM p_str2 : Idenfies the type of validating string.
16 REM
17 REM +======================================================================+
18 */
19
20 FUNCTION IS_NUMBER (p_str1 VARCHAR2,p_str2 VARCHAR2) RETURN VARCHAR2 IS
21
22 n NUMBER;
23
24 BEGIN
25
26 n := TO_NUMBER(p_str1);
27 RETURN p_str1;
28
29 EXCEPTION
30 WHEN OTHERS THEN
31 IF p_str2 = 'DFN' THEN
32 fnd_message.set_name('JE', 'JE_IL_INVALID_DFN');
33 fnd_file.put_line(fnd_file.log,fnd_message.get);
34 ELSIF p_str2 = 'CTPID' THEN
35 fnd_message.set_name('JE', 'JE_IL_INVALID_TAXPAYER_ID');
36 fnd_file.put_line(fnd_file.log,fnd_message.get);
37 ELSE
38 fnd_message.set_name('JE', 'JE_IL_INVALID_SUP_TAXPAYER_ID');
39 fnd_message.set_token('P_VENDOR_NUM',p_str2);
40 fnd_file.put_line(fnd_file.log,fnd_message.get);
41 END IF;
42
43 RAISE;
44
45 END IS_NUMBER;
46
47
48 /*
49 REM +======================================================================+
50 REM Name: BEFORE_REPORT
51 REM
52 REM Description: This function is called as a before report trigger by the
53 REM data template. It populates the data in the global_tmp table
54 REM and creates the dynamic where clause for the data template
55 REM queries(lexical reference).
56 REM
57 REM Parameters: None
58 REM +======================================================================+
59 */
60 FUNCTION BeforeReport RETURN BOOLEAN IS
61
62 l_cleared_select VARCHAR2(2000);
63
64 BEGIN
65
66 RETURN TRUE;
67
68 END BeforeReport;
69 /*
70 REM +======================================================================+
71 REM Name: get_amounts
72 REM
73 REM Description: This function is called from get_gross_amount Function
74 REM This functions fetches the Payment + Withholding Amount for
75 REM a given Ledger ID from SLA tables.
76 REM It also gets the AWT amounts, for Withholdings created at
77 REM different scenarios from SLA tables
78 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
79 REM +======================================================================+
80 */
81 FUNCTION get_amounts (pn_invoice_id NUMBER,pn_check_id NUMBER,pd_start_date DATE, pd_end_date DATE, pv_void VARCHAR2 ) RETURN NUMBER
82 IS
83 ln_invoice_id NUMBER;
84 ln_gross_amount NUMBER;
85 ln_awt_amount NUMBER;
86 ln_invoice_amount NUMBER;
87 ln_event_id NUMBER;
88 ln_pay_invoice_id NUMBER;
89 ln_awt_event NUMBER;
90 ln_sign NUMBER;
91 lv_invoice_type VARCHAR2(20);
92 CURSOR c_get_gross IS
93 SELECT xdln.event_id pay_event_id ,
94 xlah.ae_header_id,
95 xlah.ledger_id,
96 SUM(xdln.unrounded_accounted_dr) gross_amount,
97 MIN(aip.invoice_id) invoice_id
98 FROM ap_invoice_distributions aid ,
99 ap_invoice_payments aip ,
100 ap_payment_hist_dists payh ,
101 xla_distribution_links xdln ,
102 xla_ae_headers xlah
103 WHERE aip.invoice_id = pn_invoice_id
104 AND aip.check_id = pn_check_id
105 AND aip.invoice_id = aid.invoice_id
106 AND (aip.accounting_date >= pd_start_date AND aip.accounting_date <= pd_end_date)
107 AND aip.reversal_inv_pmt_id IS NULL
108 AND payh.invoice_payment_id = aip.invoice_payment_id
109 AND payh.accounting_event_id = aip.accounting_event_id
110 AND payh.invoice_distribution_id = aid.invoice_distribution_id
111 AND payh.accounting_event_id = xdln.event_id
112 AND xdln.source_distribution_id_num_1 = payh.payment_hist_dist_id
113 AND xdln.unrounded_accounted_dr IS NOT NULL
114 AND xlah.ae_header_id = xdln.ae_header_id
115 AND xlah.event_id = xdln.event_id
116 AND xlah.ledger_id = p_ledger_id
117 AND xlah.accounting_entry_status_code = 'F'
118 GROUP BY xdln.event_id,
119 xlah.ae_header_id,
120 xlah.ledger_id;
121 CURSOR c_get_gross_void IS
122 SELECT xdln.event_id pay_event_id ,
123 xlah.ae_header_id,
124 xlah.ledger_id,
125 SUM(xdln.unrounded_accounted_dr) gross_amount,
126 MIN(aip.invoice_id) invoice_id
127 FROM ap_invoice_distributions aid ,
128 ap_invoice_payments aip ,
129 ap_payment_hist_dists payh ,
130 xla_distribution_links xdln ,
131 xla_ae_headers xlah
132 WHERE aip.invoice_id = pn_invoice_id
133 AND aip.check_id = pn_check_id
134 AND aip.invoice_id = aid.invoice_id
135 AND (aip.accounting_date >= pd_start_date AND aip.accounting_date <= pd_end_date)
136 AND aip.reversal_inv_pmt_id IS NOT NULL
137 AND payh.invoice_payment_id = aip.invoice_payment_id
138 AND payh.accounting_event_id = aip.accounting_event_id
139 AND payh.invoice_distribution_id = aid.invoice_distribution_id
140 AND payh.accounting_event_id = xdln.event_id
141 AND xdln.source_distribution_id_num_1 = payh.payment_hist_dist_id
142 AND xdln.unrounded_accounted_dr IS NOT NULL
143 AND xlah.ae_header_id = xdln.ae_header_id
144 AND xlah.event_id = xdln.event_id
145 AND xlah.ledger_id = p_ledger_id
146 AND xlah.accounting_entry_status_code = 'F'
147 GROUP BY xdln.event_id,
148 xlah.ae_header_id,
149 xlah.ledger_id;
150
151 CURSOR c_get_payevent_awt(cn_invoice_id NUMBER, cn_event_id NUMBER) IS
152 SELECT xdln.event_id event,
153 xlah.ae_header_id ,
154 xlah.ledger_id ,
155 SUM(xdln.unrounded_accounted_dr) pay_amount
156 FROM ap_invoice_distributions_all aid ,
157 ap_payment_hist_dists payh ,
158 xla_distribution_links xdln ,
159 xla_ae_headers xlah ,
160 ap_invoice_payments_all aip
161 WHERE aid.invoice_id = cn_invoice_id
162 AND aip.invoice_payment_id = payh.invoice_payment_id
163 AND aip.check_id = pn_check_id
164 AND aid.line_type_lookup_code ='AWT'
165 AND payh.accounting_event_id = cn_event_id
166 AND payh.invoice_distribution_id = aid.invoice_distribution_id
167 AND xdln.event_id = payh.accounting_event_id
168 AND xdln.source_distribution_id_num_1 = payh.payment_hist_dist_id
169 AND xdln.unrounded_accounted_dr IS NOT NULL
170 AND xlah.ae_header_id = xdln.ae_header_id
171 AND xlah.event_id = xdln.event_id
172 AND xlah.ledger_id = p_ledger_id
173 AND xlah.accounting_entry_status_code = 'F'
174 GROUP BY xdln.event_id,
175 xlah.ae_header_id,
176 xlah.ledger_id;
177
178 CURSOR c_get_invwht_check (cn_invoice_id NUMBER) IS
179 SELECT ai.invoice_id invoice_id
180 FROM ap_invoice_payments aip,
181 ap_invoice_distributions aid,
182 ap_invoices ai
183 WHERE ai.invoice_id = cn_invoice_id
184 AND ai.invoice_id = aip.invoice_id
185 AND aip.invoice_id = aid.invoice_id
186 AND aid.line_type_lookup_code = 'AWT'
187 AND aip.accounting_event_id = aid.accounting_event_id
188 AND ROWNUM =1;
189
190 CURSOR c_get_invevent_awt(cn_invoice_id NUMBER) IS
191 SELECT xdln.event_id event,
192 xlah.ae_header_id ,
193 xlah.ledger_id ,
194 SUM(xdln.unrounded_accounted_dr) pay_amount
195 FROM ap_invoice_distributions_all aid ,
196 xla_distribution_links xdln,
197 xla_ae_headers xlah
198 WHERE aid.invoice_id = cn_invoice_id
199 AND aid.line_type_lookup_code = 'AWT'
200 AND xdln.event_id = aid.accounting_event_id
201 AND xdln.unrounded_accounted_dr IS NOT NULL
202 AND xlah.ae_header_id = xdln.ae_header_id
203 AND xlah.event_id = xdln.event_id
204 AND xlah.ledger_id = p_ledger_id
205 AND xdln.source_distribution_id_num_1 = aid.invoice_distribution_id
206 AND xlah.accounting_entry_status_code = 'F'
207 GROUP BY xdln.event_id,
208 xlah.ae_header_id,
209 xlah.ledger_id;
210
211 BEGIN
212 gn_awt_amount := 0;
213 gn_invoice_id := -99;
214 ln_sign := 1;
215 IF pv_void = 'N' THEN
216 FOR rec_gross IN c_get_gross
217 LOOP
218 ln_gross_amount := rec_gross.gross_amount;
219 ln_invoice_id := rec_gross.invoice_id;
220 ln_event_id := rec_gross.pay_event_id;
221 END LOOP;
222 ELSE
223 FOR rec_gross_void IN c_get_gross_void
224 LOOP
225 ln_gross_amount := rec_gross_void.gross_amount;
226 ln_invoice_id := rec_gross_void.invoice_id;
227 ln_event_id := rec_gross_void.pay_event_id;
228 END LOOP;
229 END IF;
230 FOR rec_invwht IN c_get_invwht_check(ln_invoice_id)
231 LOOP
232 ln_pay_invoice_id := rec_invwht.invoice_id;
233 END LOOP;
234 IF ln_invoice_id = ln_pay_invoice_id THEN
235 FOR rec_payevent_awt IN c_get_payevent_awt(ln_invoice_id,ln_event_id)
236 LOOP
237 ln_awt_amount := rec_payevent_awt.pay_amount;
238 ln_awt_event := rec_payevent_awt.event;
239 END LOOP;
240 ELSE
241 BEGIN
242 SELECT SUM(xdln.unrounded_accounted_dr) INTO ln_invoice_amount
243 FROM ap_invoice_distributions_all aid ,
244 xla_distribution_links xdln,
245 xla_ae_headers xlah
246 WHERE aid.invoice_id = ln_invoice_id
247 AND aid.line_type_lookup_code <> 'AWT'
248 AND xdln.event_id = aid.accounting_event_id
249 AND xdln.unrounded_accounted_dr IS NOT NULL
250 AND xlah.ae_header_id = xdln.ae_header_id
251 AND xlah.event_id = xdln.event_id
252 AND xlah.ledger_id = p_ledger_id
253 AND xdln.source_distribution_id_num_1 = aid.invoice_distribution_id
254 AND xlah.accounting_entry_status_code = 'F';
255 EXCEPTION
256 WHEN others THEN
257 ln_invoice_amount := 0;
258 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
259 END;
260 FOR rec_invevent_awt IN c_get_invevent_awt(ln_invoice_id)
261 LOOP
262 ln_awt_amount := (rec_invevent_awt.pay_amount*ln_gross_amount)/(ln_invoice_amount-rec_invevent_awt.pay_amount);
263 ln_awt_event := rec_invevent_awt.event;
264 ln_gross_amount := ln_gross_amount + NVL(ln_awt_amount,0);
265 END LOOP;
266 fnd_file.put_line(fnd_file.log,'Return from Cursor c_get_invevent_awt:Invoice ID:'||ln_invoice_id||'Amount:'||ln_gross_amount);
267 END IF;
268 BEGIN
269 SELECT SIGN(NVL(aip.payment_base_amount,aip.amount)) INTO ln_sign
270 FROM ap_invoice_payments aip
271 WHERE aip.invoice_id = ln_invoice_id
272 AND aip.accounting_event_id = ln_event_id;
273 EXCEPTION
274 WHEN others THEN
275 ln_sign := 1;
276 END;
277 ln_gross_amount := ln_gross_amount * ln_sign;
278 BEGIN
279 SELECT invoice_type_lookup_code INTO lv_invoice_type
280 FROM ap_invoices WHERE invoice_id = ln_invoice_id;
281 EXCEPTION
282 WHEN others THEN
283 lv_invoice_type := 'STANDARD';
284 END;
285 IF lv_invoice_type = 'CREDIT' THEN
286 ln_awt_amount := 0;
287 ELSE
288 IF pv_void = 'N' THEN
289 ln_awt_amount := ln_awt_amount;
290 ELSE
291 ln_awt_amount := ln_awt_amount * (-1);
292 END IF;
293 END IF;
294 gn_awt_amount := NVL(ln_awt_amount,0);
295 gn_invoice_id := NVL(ln_invoice_id,-99);
296 fnd_file.put_line(fnd_file.log,'Return 1:'||ln_gross_amount);
297 fnd_file.put_line(fnd_file.log,'Return 2:'||gn_awt_amount);
298 RETURN ln_gross_amount;
299 END get_amounts;
300 /*
301 REM +======================================================================+
302 REM Name: get_gross_amount
303 REM
304 REM Description: This function is called from XML query Q_PAYMENTS
305 REM This functions fetches the Payment + Withholding Amount for
306 REM a given Ledger ID from SLA tables by making a call to get_amounts.
307 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
308 REM +======================================================================+
309 */
310 FUNCTION get_gross_amount(pn_invoice_id NUMBER,pn_check_id NUMBER,pd_start_date DATE, pd_end_date DATE, pv_void VARCHAR2)
311 RETURN NUMBER
312 IS
313 vn_ret_gross NUMBER;
314 BEGIN
315 BEGIN
316 vn_ret_gross := NVL(get_amounts(pn_invoice_id,pn_check_id, pd_start_date, pd_end_date,pv_void),0);
317 EXCEPTION
318 WHEN others THEN -- Don't Error out the report. Display 0
319 vn_ret_gross := 0;
320 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
321 END;
322 fnd_file.put_line(fnd_file.log,'Return gross:'||vn_ret_gross);
323 RETURN vn_ret_gross;
324 END get_gross_amount;
325 /*
326 REM +======================================================================+
327 REM Name: get_amounts
328 REM
329 REM Description: This function is called from XML query Q_PAYMENTS
330 REM This functions fetches the Withholding Amount for
331 REM a given Ledger ID from SLA tables.
332 REM When get_amounts is called from get_gross_amount
333 REM it fetches the AWT data and updates the Global Varieble
334 REM gn_awt_amount. This function fetches data from this GT Varieble
335 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
336 REM +======================================================================+
337 */
338 FUNCTION get_awt_amount
339 RETURN NUMBER
340 IS
341 vn_ret_awt NUMBER;
342 BEGIN
343 vn_ret_awt := NVL(gn_awt_amount,0);
344 fnd_file.put_line(fnd_file.log,'Return awt:'||vn_ret_awt);
345 RETURN vn_ret_awt;
346 END get_awt_amount;
347 /*
348 REM +======================================================================+
349 REM Name: get_invoice_id
350 REM
351 REM Description: This function is called from XML query Q_PAYMENTS
352 REM This functions fetches the Valid Invoice Id
353 REM which passed the validation in the Cursor c_get_gross
354 REM of the function get_amounts
355 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
356 REM +======================================================================+
357 */
358 FUNCTION get_invoice_id
359 RETURN NUMBER
360 IS
361 vn_ret_invoice_id NUMBER;
362 BEGIN
363 vn_ret_invoice_id := NVL(gn_invoice_id,-99);
364 fnd_file.put_line(fnd_file.log,'Return Invoice:'||vn_ret_invoice_id);
365 RETURN vn_ret_invoice_id;
366 END get_invoice_id;
367
368 END JE_IL_TAX_PKG;
[Home] [Help]