1 PACKAGE BODY JE_IL_TAX_PKG
2 -- $Header: jeilwhtapb.pls 120.36.12020000.7 2013/03/25 07:14:21 sakekuma ship $
3 AS
4
5 --------------------------------------------------------------------------------
6 -- constant for first party details
7 --------------------------------------------------------------------------------
8 C_FIRST_PARTY_QUERY VARCHAR2(8000) :=
9 'SELECT
10 JE_IL_TAX_PKG.IS_NUMBER(max(decode(person_last_name, ''TN'', person_first_name)),''DFN'') Deduction_File_Number,
11 JE_IL_TAX_PKG.IS_NUMBER(max(decode(person_last_name, ''TM'', person_first_name)),''CTPID'') Tax_Payer_ID,
12 max(decode(q1.le_role ,''Legal Contact'',email_address,null)) Email,
13 ''96'' Type_Code,
14 :P_Manual_Rpt_Exist P_Manual,
15 :P_Comp_Rpt_Exist P_Complimentary_Rpt,
16 :P_Payer_Position P_Payer_Pos,
17 max(decode(person_last_name, ''TN'',substr(hp.primary_phone_area_code|| hp.primary_phone_number,1,10))) Phone_No
18 FROM hz_parties hp,
19 (SELECT subject_id ,XLE_CONTACT_GRP.concat_contact_roles (subject_id,object_id) le_role
20 FROM hz_relationships
21 WHERE object_id = (
22 SELECT party_id
23 FROM xle_firstparty_information_v
24 WHERE legal_entity_id = :P_Legal_Entity_ID
25 )
26 AND relationship_code = ''CONTACT_OF''
27 AND directional_flag = ''F'' ) q1
28 WHERE hp.party_id = q1.subject_id';
29
30 C_FIRST_PARTY_NULL_QUERY VARCHAR2(2000) :=
31 'SELECT NULL Deduction_File_Number, NULL Tax_Payer_ID,
32 NULL Email, NULL Type_Code, NULL P_Manual,
33 NULL P_Complimentary_Rpt, NULL P_Payer_Pos
34 FROM DUAL';
35
36
37 --------------------------------------------------------------------------------
38 -- constant for payment details
39 --------------------------------------------------------------------------------
40 C_PAYMENT_INFO_QUERY VARCHAR2(8000) :=
41 'SELECT aag.name awt_group_name, ''A'' awt_flag
42 FROM ap_invoices_all ai,
43 ap_invoice_payments_all aip,
44 ap_awt_groups aag,
45 ap_checks_all ac
46 WHERE ac.check_id = :check_id
47 AND ac.check_id = aip.check_id
48 AND aip.invoice_id = ai.invoice_id
49 AND aag.group_id = nvl(ai.awt_group_id, ai.pay_awt_group_id)
50 AND rownum = 1';
51
52 C_PAYMENT_INFO_NULL_QUERY VARCHAR2(1000) :=
53 'SELECT NULL awt_group_name, NULL awt_flag FROM DUAL';
54
55 --------------------------------------------------------------------------------
56 -- constant for vendor balance details
57 --------------------------------------------------------------------------------
58 C_VENDOR_BALANCE_QUERY VARCHAR2(8000) :=
59 'SELECT (SUM(acctd_rounded_cr) - SUM(acctd_rounded_dr)) vendor_balance,
60 party_id vendor_id3,
61 party_site_id vendor_site_id3
62 FROM xla_trial_balances
63 WHERE party_id = :vendor_id2
64 AND party_site_id = :vendor_site_id2
65 AND ledger_id = :p_ledger_id
66 AND definition_code IN
67 (SELECT definition_code
68 FROM xla_tb_definitions_b
69 WHERE ledger_id = :p_ledger_id)
70 --AND gl_date BETWEEN :p_start_date AND :p_end_date -- Bug 14162763
71 AND trunc(gl_date) between trunc(to_date(''01-01-1950'',''DD-MM-YYYY'')) and trunc(to_date(:p_end_date,''DD-MM-YYYY'')) -- Bug 14162763
72 GROUP BY party_site_id, party_id
73 HAVING(SUM(acctd_rounded_cr) - SUM(acctd_rounded_dr)) > 0';
74
75 C_VENDOR_BALANCE_NULL_QUERY VARCHAR2(1000) :=
76 'SELECT NULL vendor_balance, NULL vendor_id3, NULL vendor_site_id3 FROM DUAL';
77
78 --------------------------------------------------------------------------------
79 -- constant for AWT Tax Rate Details
80 --------------------------------------------------------------------------------
81 C_AWT_TAXRATES_QUERY VARCHAR2(8000) :=
82 'SELECT vendor_site_id rate_site_id,
83 tax_name tax_name,
84 tax_rate tax_rate,
85 to_char(start_date, ''DD-MON-YYYY'') start_date1,
86 to_char(end_date, ''DD-MON-YYYY'') end_date1,
87 comments comments
88 FROM ap_awt_tax_rates_all
89 WHERE vendor_site_id = :vendor_site_id2 ';
90
91 C_AWT_TAXRATES_NULL_QUERY VARCHAR2(1000) :=
92 'SELECT NULL rate_site_id, NULL tax_name,
93 NULL tax_rate, NULL start_date1,
94 NULL end_date1, NULL comments
95 FROM DUAL';
96
97 --------------------------------------------------------------------------------
98 -- constant for count lines
99 --------------------------------------------------------------------------------
100 C_COUNT_LINES_QUERY VARCHAR2(8000);
101
102 C_COUNT_LINES_NULL_QUERY VARCHAR2(1000) :=
103 'SELECT NULL count_lines FROM DUAL';
104
105 --------------------------------------------------------------------------------
106 -- constant for count vendor
107 --------------------------------------------------------------------------------
108 C_COUNT_VENDORS_QUERY VARCHAR2(8000);
109
110 C_COUNT_VENDORS_NULL_QUERY VARCHAR2(1000) :=
111 'SELECT NULL Count_Vendors FROM DUAL';
112
113 C_VENDOR_BALANCE_QUERY_JEILWHT VARCHAR2(8000) :=
114 'SELECT (SUM(nvl(acctd_rounded_cr,0)) - SUM(nvl(acctd_rounded_dr,0))) vendor_balance,
115 party_id vendor_id3,
116 party_site_id vendor_site_id3
117 FROM xla_trial_balances
118 WHERE party_id = :vendor_id2
119 AND party_site_id = :vendor_site_id2
120 AND ledger_id = :p_ledger_id
121 AND definition_code = nvl(:p_definition_code,definition_code)
122 -- AND gl_date BETWEEN :p_start_date AND :p_end_date -- Bug 14162763
123 AND trunc(gl_date) between trunc(to_date(''01-01-1950'',''DD-MM-YYYY'')) and trunc(to_date(:p_end_date,''DD-MM-YYYY'')) -- Bug 14162763
124 GROUP BY party_site_id, party_id ';
125
126 /*
127 REM +======================================================================+
128 REM Name: IS_NUMBER
129 REM
130 REM Description: This function is called in the query Q_FIRST_PARTY ,Q_VENDOR_SITE
131 REM of data template, for validating the Deduction File Number and
132 REM Taxpayer Id of the company and Taxpayer id of the supplier.
133 REM
134 REM Parameters:
135 REM p_str1 : String needs to be validated.
136 REM p_str2 : Idenfies the type of validating string.
137 REM
138 REM +======================================================================+
139 */
140
141 FUNCTION IS_NUMBER (p_str1 VARCHAR2,p_str2 VARCHAR2) RETURN VARCHAR2 IS
142
143 n NUMBER;
144
145 BEGIN
146
147 n := TO_NUMBER(p_str1);
148 RETURN p_str1;
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 IF p_str2 = 'DFN' THEN
153 fnd_message.set_name('JE', 'JE_IL_INVALID_DFN');
154 fnd_file.put_line(fnd_file.log,fnd_message.get);
155 ELSIF p_str2 = 'CTPID' THEN
156 fnd_message.set_name('JE', 'JE_IL_INVALID_TAXPAYER_ID');
157 fnd_file.put_line(fnd_file.log,fnd_message.get);
158 ELSE
159 fnd_message.set_name('JE', 'JE_IL_INVALID_SUP_TAXPAYER_ID');
160 fnd_message.set_token('P_VENDOR_NUM',p_str2);
161 fnd_file.put_line(fnd_file.log,fnd_message.get);
162 END IF;
163
164 RETURN NULL;
165
166 END IS_NUMBER;
167
168
169 /*
170 REM +======================================================================+
171 REM Name: BEFORE_REPORT
172 REM
173 REM Description: This function is called as a before report trigger by the
174 REM data template. It populates the data in the global_tmp table
175 REM and creates the dynamic where clause for the data template
176 REM queries(lexical reference).
177 REM
178 REM Parameters: None
179 REM +======================================================================+
180 */
181 FUNCTION BeforeReport RETURN BOOLEAN IS
182 l_currency_code gl_ledgers.currency_code%TYPE;
183
184 BEGIN
185 -- Get the profile value FND: Debug Log Enabled to print debug messages
186 fnd_debug_log := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
187
188 -- default values
189 p_name_level := NVL(p_name_level,'NONE');
190 p_information_level := NVL(p_information_level,'NONE');
191 p_vendor_type := NVL(p_vendor_type,'NONE');
192 p_vat_reg_no := ' ';
193 p_vendor_type_col := ' 0 B1, 0 B2 ';
194 p_vendor_type_cond := ' ';
195 p_vendor_name_cond := ' ';
196 p_vendor_site_cond := ' ';
197
198 --
199 -- Identifying primary ledger id
200 --
201 BEGIN
202 SELECT primary_ledger_id
203 INTO l_primary_ledger_id
204 FROM gl_ledger_relationships
205 WHERE target_ledger_id = p_ledger_id
206 AND (target_ledger_category_code = 'PRIMARY' OR source_ledger_id <> target_ledger_id)
207 AND ROWNUM = 1;
208 EXCEPTION
209 WHEN OTHERS THEN
210 l_primary_ledger_id := 0;
211 IF fnd_debug_log = 'Y' THEN
212 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
213 END IF;
214 return FALSE;
215 END;
216
217
218 BEGIN
219
220 SELECT currency_code
221 INTO l_currency_code
222 FROM gl_ledgers
223 WHERE ledger_id = p_ledger_id;
224
225 IF l_currency_code = 'ILS' THEN
226 l_currency_check := ' AND 1=1 ';
227 ELSE
228 l_currency_check := ' AND 1=2 ';
229 END IF;
230 EXCEPTION
231
232 WHEN OTHERS THEN
233 l_currency_code := NULL;
234 IF fnd_debug_log = 'Y' THEN
235 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
236 END IF;
237 return FALSE;
238 END;
239
240
241
242 -- <Vendor name column based on Name Level>
243 IF p_name_level = 'VAN' THEN
244 p_vendor_name_col := ' pvend.vendor_name_alt vendor_name1, ';
245 ELSE
246 p_vendor_name_col := ' pvend.vendor_name vendor_name1, ';
247 END IF;
248
249 -- <Vendor site code column based on Name Level>
250 IF p_name_level = 'VSAN' THEN
251 p_vendor_sitecode_col := ' pvs.vendor_site_code_alt vendor_site_code, ';
252 ELSE
253 p_vendor_sitecode_col := ' pvs.vendor_site_code vendor_site_code, ';
254 END IF;
255
256 -- <Columns based on Information Level>
257 IF p_information_level = 'S' THEN
258 -- Vat registration Num
259 p_vat_reg_no := ' (SELECT zx.rep_registration_number
260 FROM zx_party_tax_profile zx
261 WHERE pvs.party_site_id = zx.party_id
262 AND zx.party_type_code = ''THIRD_PARTY_SITE''
263 AND ROWNUM = 1) vat_reg_no, ';
264 -- Flex value condition for IRS Tax officer column
265 p_flex_value_cond := ' pvs.global_attribute11 ';
266 -- Deduction type
267 p_deduction_type_cond := ' pvs.global_attribute17 ';
268 -- Exp IRS
269 p_exp_irs_cond := 'CASE pvs.GLOBAL_ATTRIBUTE15
270 WHEN ''1'' THEN 1
271 WHEN ''2'' THEN 2
272 WHEN ''3'' THEN 3
273 WHEN ''9'' THEN 1
274 WHEN ''92'' THEN 5
275 WHEN ''93'' THEN 3
276 WHEN ''21'' THEN 1
277 WHEN ''22'' THEN 2
278 WHEN ''23'' THEN 3
279 ELSE NULL END Exp_IRS';
280 -- Supplier Type
281 p_supplier_type := 'CASE pvs.GLOBAL_ATTRIBUTE15
282 WHEN ''1'' THEN 0
283 WHEN ''2'' THEN 0
284 WHEN ''3'' THEN 0
285 WHEN ''9'' THEN 4
286 WHEN ''92'' THEN 4
287 WHEN ''93'' THEN 4
288 WHEN ''21'' THEN 2
289 WHEN ''22'' THEN 2
290 WHEN ''23'' THEN 2
291 ELSE NULL END SUPPLIER_TYPE';
292
293 p_bank_supplier := 'DECODE(pvs.global_attribute17,''08'',
294 CASE pvs.global_attribute15
295 WHEN ''9'' THEN 1
296 WHEN ''92'' THEN 1
297 WHEN ''93'' THEN 1
298 ELSE 0 END,0) BANK_SUPPLIER ';
299
300 l_foreign_suppliers_check := ' AND pvs1.global_attribute17 = ''08''
301 AND pvs1.global_attribute15 in (''9'',''92'',''93'') ';
302
303 -- Business Sector
304 p_business_sec_cond := ' pvs.global_attribute14 business_sector, ';
305 -- Tax payer ID
306 -- p_tax_payerid_cond := ' decode(pvs.global_attribute15, ''9'', ''999999999'', nvl(papf.national_identifier, nvl(pvend.individual_1099, pvend.num_1099))) ';
307 p_tax_payerid_cond := ' CASE pvs.global_attribute15 WHEN ''9'' THEN ''999999999'' WHEN ''92'' THEN ''999999999'' WHEN ''93'' THEN ''999999999'' ELSE nvl(papf.national_identifier, nvl(pvend.individual_1099, pvend.num_1099)) END';
308 ELSE
309 -- Flex value condition for IRS Tax officer column
310 p_flex_value_cond := ' pvend.global_attribute11 ';
311 -- Deduction type
312 p_deduction_type_cond := ' pvend.global_attribute17 ';
313 -- Exp IRS
314 p_exp_irs_cond := 'CASE pvend.GLOBAL_ATTRIBUTE15
315 WHEN ''1'' THEN 1
316 WHEN ''2'' THEN 2
317 WHEN ''3'' THEN 3
318 WHEN ''9'' THEN 1
319 WHEN ''92'' THEN 5
320 WHEN ''93'' THEN 3
321 WHEN ''21'' THEN 1
322 WHEN ''22'' THEN 2
323 WHEN ''23'' THEN 3
324 ELSE NULL END Exp_IRS';
325 -- Supplier Type
326 p_supplier_type := 'CASE pvend.GLOBAL_ATTRIBUTE15
327 WHEN ''1'' THEN 0
328 WHEN ''2'' THEN 0
329 WHEN ''3'' THEN 0
330 WHEN ''9'' THEN 4
331 WHEN ''92'' THEN 4
332 WHEN ''93'' THEN 4
333 WHEN ''21'' THEN 2
334 WHEN ''22'' THEN 2
335 WHEN ''23'' THEN 2
336 ELSE NULL END SUPPLIER_TYPE';
337
338 p_bank_supplier := 'DECODE(pvend.global_attribute17,''08'',
339 CASE pvend.global_attribute15
340 WHEN ''9'' THEN 1
341 WHEN ''92'' THEN 1
342 WHEN ''93'' THEN 1
343 ELSE 0 END,0) BANK_SUPPLIER ';
344
345 l_foreign_suppliers_check := ' AND pvend1.global_attribute17 = ''08''
346 AND pvend1.global_attribute15 in (''9'',''92'',''93'') ';
347
348 -- Business Sector
349 p_business_sec_cond := ' pvend.global_attribute14 business_sector, ';
350 -- Tax payer ID
351 -- p_tax_payerid_cond := ' decode(pvend.global_attribute15, ''9'', ''999999999'', nvl(papf.national_identifier, nvl(pvend.individual_1099, pvend.num_1099))) ';
352
353 p_tax_payerid_cond := ' CASE pvend.global_attribute15 WHEN ''9'' THEN ''999999999'' WHEN ''92'' THEN ''999999999'' WHEN ''93'' THEN ''999999999'' ELSE nvl(papf.national_identifier, nvl(pvend.individual_1099, pvend.num_1099)) END';
354 END IF;
355
356 IF p_information_level = 'V' THEN
357 -- Vat registration Num
358 p_vat_reg_no := ' pvend.vat_registration_num vat_reg_no, ';
359 -- Condition on global attribute
360 p_global_cond := ' nvl(pvend.global_attribute15,-1) ';
361 ELSE
362 -- Condition on global attribute
363 p_global_cond := ' nvl(pvs.global_attribute15,-1) ';
364 END IF;
365
366
367 -- <conditions based on FROM Supplier Number>
368 IF p_from_supplier_number IS NULL THEN
369 p_supplier_num_from := ' ';
370 ELSE
371 p_supplier_num_from := ' AND pvend.segment1 >= '''|| to_char(p_from_supplier_number) ||'''';
372 END IF;
373
374 -- <conditions based on TO Supplier Number>
375 IF p_to_supplier_number IS NULL THEN
376 p_supplier_num_to := ' ';
377 ELSE
378 p_supplier_num_to := ' AND pvend.segment1 <= '''|| to_char(p_to_supplier_number) ||'''';
379 END IF;
380
381 -- Condition based on vendor type
382 IF p_vendor_type = 'A' THEN
383 p_vendor_type_col := ' 999 B1, 0 B2 ';
384 p_vendor_type_cond := ' AND ' || p_global_cond || ' in (''1'',''2'',''3'',''9'',''92'',''93'',''21'',''22'',''23'') ';
385 ELSIF p_vendor_type = 'F' THEN
386 p_vendor_type_col := ' 0 B1, 9 B2 ';
387 p_vendor_type_cond := ' AND ' || p_global_cond || ' in (''9'',''92'',''93'') ';
388 ELSIF p_vendor_type = 'O' THEN
389 p_vendor_type_col := ' 0 B1, 99 B2 ';
390 p_vendor_type_cond := ' AND ' || p_global_cond || ' in (''1'',''2'',''3'',''21'',''22'',''23'') ';
391 END IF;
392
393 -- Condition based on vendor name and vendor site
394 IF P_Report_Name = 'JEILWHTR' THEN
395 IF P_Vendor_Name IS NOT NULL THEN
396 p_vendor_name_cond := ' AND pvend.vendor_id = '''|| P_Vendor_Name ||'''';
397 END IF;
398 IF P_Vendor_Site IS NOT NULL THEN
399 p_vendor_site_cond := ' AND pvs.vendor_site_id = '''|| P_Vendor_Site ||'''';
400 END IF;
401 END IF;
402
403 --------------------------------------------------------------------------------
404 -- constant for count lines
405 --------------------------------------------------------------------------------
406 C_COUNT_LINES_QUERY :=
407 ' SELECT SUM(countv) count_lines
408 FROM
409 (SELECT DISTINCT ac.vendor_site_id vendor_site_id2, (
410 CASE
411 WHEN SUM(nvl(nvl(aid.base_amount, aid.amount) *-1, 0)) < 0
412 OR(SUM(nvl(aip.payment_base_amount, aip.amount)) +
413 SUM(nvl(nvl(aid.base_amount, aid.amount) *-1, 0))) < 0
414 THEN 0
415 ELSE 1
416 END) countv
417 FROM ap_invoices_all ai,
418 ap_invoice_distributions_all aid,
419 ap_checks_all ac,
420 ap_invoice_payments_all aip,
421 po_vendors pvend,
422 po_vendor_sites_all pvs,
423 (SELECT distinct person_id,
424 national_identifier
425 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
426 WHERE ai.invoice_id = aid.invoice_id
427 AND ac.check_id = aip.check_id
428 AND aip.invoice_id = ai.invoice_id
429 AND ai.set_of_books_id = ' || l_primary_ledger_id || '
430 AND aid.set_of_books_id = ' || l_primary_ledger_id || '
431 AND ai.legal_entity_id = :p_legal_entity_id
432 AND aid.line_type_lookup_code = ''AWT''
433 AND aid.awt_flag = ''A''
434 AND aid.awt_invoice_payment_id IS NOT NULL
435 AND(aid.accounting_date >= :p_start_date
436 AND aid.accounting_date <= :p_end_date)
437 AND pvend.vendor_id = pvs.vendor_id
438 AND nvl(pvend.employee_id, -99) = papf.person_id (+)
439 AND pvs.vendor_id = ai.vendor_id'
440 || p_vendor_type_cond ||
441 ' GROUP BY ac.vendor_site_id) ';
442 --------------------------------------------------------------------------------
443 -- constant for count vendor
444 --------------------------------------------------------------------------------
445 C_COUNT_VENDORS_QUERY :=
446 'SELECT SUM(DECODE(SUM(countv), SUM(countt), 1, 0)) Count_Vendors
447 FROM po_vendors pvend,
448 po_vendor_sites_all pvs,
449 (SELECT distinct person_id
450 ,national_identifier
451 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf,
452 (SELECT distinct ac.vendor_site_id Vendor_Site_IDs,
453 ( CASE
454 WHEN SUM(NVL(NVL(aid.base_amount,aid.amount)*-1,0)) < 0
455 OR (SUM(NVL(aip.payment_base_amount,aip.amount)) +
456 SUM(NVL(NVL(aid.base_amount,aid.amount)*-1,0))) < 0
457 THEN 0
458 ELSE 1
459 END ) countv,
460 COUNT(distinct(ac.vendor_site_id)) countt
461 FROM ap_invoices_all ai
462 ,ap_invoice_distributions_all aid
463 ,ap_checks_all ac
464 ,ap_invoice_payments_all aip
465 WHERE ai.invoice_id = aid.invoice_id
466 AND ac.check_id=aip.check_id
467 AND aip.invoice_id=ai.invoice_id
468 AND ai.set_of_books_id = ' || l_primary_ledger_id || '
469 AND aid.set_of_books_id = ' || l_primary_ledger_id || '
470 AND ai.legal_entity_id = :P_Legal_Entity_ID
471 AND aid.line_type_lookup_code = ''AWT''
472 AND ((aid.awt_flag = ''A''
473 AND aid.awt_invoice_payment_id = aip.invoice_payment_id))
474 AND (aid.accounting_date >= :P_START_DATE
475 AND aid.accounting_date <= :P_END_DATE)
476 GROUP BY ac.vendor_site_id
477 UNION
478 SELECT distinct ac1.vendor_site_id Vendor_Site_IDs,
479 ( CASE
480 WHEN SUM(NVL(aip1.payment_base_amount,aip1.amount)) < 0
481 THEN 0
482 ELSE 1
483 END ) countv,
484 COUNT(distinct(ac1.vendor_site_id)) countt
485 FROM AP_INVOICE_PAYMENTS_ALL aip1
486 ,AP_CHECKS_ALL ac1
487 ,PO_VENDORS pvend1
488 ,PO_VENDOR_SITES_ALL pvs1
489 WHERE aip1.check_id = ac1.check_id
490 AND aip1.set_of_books_id= ' || l_primary_ledger_id || '
491 AND ac1.global_attribute_category=''JE.IL.APXPAWKB.CHECKS''
492 AND NVL(ac1.global_attribute1,0) > 0
493 AND aip1.accounting_date >=:P_START_DATE
494 AND aip1.accounting_date <=:P_END_DATE
495 AND ac1.vendor_id= pvend1.vendor_id
496 AND ac1.vendor_site_id = pvs1.vendor_site_id
497 AND pvend1.vendor_id = pvs1.vendor_id
498 AND :P_REPORT_NAME = ''JEILWHTT''
499 '|| l_currency_check
500 || l_foreign_suppliers_check|| '
501 group by ac1.vendor_site_id) q1
502 WHERE pvend.vendor_id = pvs.vendor_id
503 AND nvl(pvend.employee_id, -99) = papf.person_id (+)
504 AND pvs.vendor_site_id = q1.Vendor_Site_IDs'
505 || p_vendor_type_cond ||
506 'GROUP BY ' || p_tax_payerid_cond;
507
508 --Condition based on Order By column
509 IF P_Order_By = 'T' THEN
510 p_order_by_cond := p_tax_payerid_cond || ' Order_By1 ';
511 ELSE
512 p_order_by_cond := ' pvend.segment1 Order_By1 ';
513 END IF;
514
515 IF P_Report_Name = 'JEILWHTT' THEN
516 p_first_party_query := C_FIRST_PARTY_QUERY;
517 ELSE
518 p_first_party_query := C_FIRST_PARTY_NULL_QUERY;
519 END IF;
520
521 IF P_Report_Name = 'JEILWHTD' THEN
522 p_payment_info_query := C_PAYMENT_INFO_NULL_QUERY;
523 p_awt_taxrates_query := C_AWT_TAXRATES_NULL_QUERY;
524 ELSE
525 p_payment_info_query := C_PAYMENT_INFO_QUERY;
526 p_awt_taxrates_query := C_AWT_TAXRATES_QUERY;
527 END IF;
528
529 IF P_Report_Name = 'JEILWHTR' THEN
530 p_vendor_balance_query := C_VENDOR_BALANCE_NULL_QUERY;
531 p_count_lines_query := C_COUNT_LINES_NULL_QUERY;
532 p_count_vendors_query := C_COUNT_VENDORS_NULL_QUERY;
533 ELSIF P_Report_Name = 'JEILWHTT' THEN
534 p_vendor_balance_query := C_VENDOR_BALANCE_QUERY_JEILWHT;
535 p_count_lines_query := C_COUNT_LINES_QUERY;
536 p_count_vendors_query := C_COUNT_VENDORS_QUERY ;
537 ELSE
538 p_vendor_balance_query := C_VENDOR_BALANCE_QUERY;
539 p_count_lines_query := C_COUNT_LINES_QUERY;
540 p_count_vendors_query := C_COUNT_VENDORS_QUERY ;
541 END IF;
542
543
544 IF P_Report_Name = 'JEILWHTT' THEN
545
546 BEGIN
547 SELECT period_set_name
548 INTO l_period_set_name
549 FROM gl_ledgers
550 WHERE ledger_id = l_primary_ledger_id;
551
552 l_period_set_name := ''''||l_period_set_name||'''';
553
554 EXCEPTION
555 WHEN OTHERS THEN
556 l_period_set_name := NULL;
557 IF fnd_debug_log = 'Y' THEN
558 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
559 END IF;
560 RETURN FALSE;
561 END;
562
563 END IF;
564
565
566 RETURN TRUE;
567
568 END BeforeReport;
569 /*
570 REM +======================================================================+
571 REM Name: get_amounts
572 REM
573 REM Description: This function is called from get_gross_amount Function
574 REM This functions fetches the Payment + Withholding Amount for
575 REM a given Ledger ID from SLA tables.
576 REM It also gets the AWT amounts, for Withholdings created at
577 REM different scenarios from SLA tables
578 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
579 REM +======================================================================+
580 */
581 Function Get_Amounts (Pn_Invoice_Id Number,Pn_Check_Id Number,Pd_Start_Date Date, Pd_End_Date Date, Pv_Void Number
582 -- the below default parameter pn_awt_amt_flag is added by RAJANAR on 05-Mar-2012 as part of ER 13599326
583 , pn_awt_amt_flag IN NUMBER DEFAULT 0 ) RETURN NUMBER
584 IS
585 ln_invoice_id NUMBER;
586 ln_gross_amount NUMBER;
587 ln_awt_amount NUMBER;
588 ln_invoice_amount NUMBER;
589 ln_event_id NUMBER;
590 ln_pay_invoice_id NUMBER;
591 ln_awt_event NUMBER;
592 ln_sign NUMBER;
593 lv_invoice_type VARCHAR2(20);
594 --Bug 9151599 Modified cursors c_get_gross and c_get_gross_void
595 ln_bank_awt_amount NUMBER;
596 ln_bank_awt_check NUMBER := -1;
597
598 CURSOR c_get_gross IS
599 SELECT xlah.event_id pay_event_id ,
600 aip.invoice_id invoice_id,
601 SUM(nvl(xdln.unrounded_accounted_dr,0)) - SUM(nvl(xdln.unrounded_accounted_cr,0)) gross_amount
602 FROM (select distinct a.invoice_id,a.accounting_event_id from ap_invoice_payments_all a
603 WHERE a.invoice_id = pn_invoice_id
604 AND a.check_id = pn_check_id
605 AND a.accounting_date >= pd_start_date
606 AND a.accounting_date <= pd_end_date
607 AND a.reversal_inv_pmt_id IS NULL) aip,
608 xla_ae_lines xdln ,
609 xla_ae_headers xlah
610 WHERE aip.accounting_event_id = xlah.event_id
611 AND xlah.application_id = xdln.application_id
612 AND xlah.ae_header_id = xdln.ae_header_id
613 AND xlah.ledger_id = p_ledger_id
614 AND xlah.accounting_entry_status_code = 'F'
615 AND (xdln.accounting_class_code IN ('LIABILITY' , 'EXCHANGE_GAIN_LOSS') or xdln.gain_or_loss_flag = 'Y') --bug14152662
616 GROUP BY xlah.event_id,
617 aip.invoice_id;
618
619 CURSOR c_get_gross_void IS
620 SELECT xlah.event_id pay_event_id ,
621 aip.invoice_id invoice_id,
622 SUM(nvl(xdln.unrounded_accounted_cr,0)) - SUM(nvl(xdln.unrounded_accounted_dr,0)) gross_amount
623 FROM (select distinct a.invoice_id,a.accounting_event_id from ap_invoice_payments_all a
624 WHERE a.invoice_id = pn_invoice_id
625 AND a.check_id = pn_check_id
626 AND a.accounting_date >= pd_start_date
627 AND a.accounting_date <= pd_end_date
628 AND a.reversal_inv_pmt_id IS NOT NULL) aip,
629 xla_ae_lines xdln ,
630 xla_ae_headers xlah
631 WHERE aip.accounting_event_id = xlah.event_id
632 AND xlah.application_id = xdln.application_id
633 AND xlah.ae_header_id = xdln.ae_header_id
634 AND xlah.ledger_id = p_ledger_id
635 AND xlah.accounting_entry_status_code = 'F'
636 AND (xdln.accounting_class_code IN ('LIABILITY' , 'EXCHANGE_GAIN_LOSS') or xdln.gain_or_loss_flag = 'Y') --bug14152662
637 GROUP BY xlah.event_id ,
638 aip.invoice_id;
639
640 CURSOR c_get_payevent_awt(cn_invoice_id NUMBER, cn_event_id NUMBER) IS
641 SELECT xlah.event_id event,
642 SUM(nvl(xdln.unrounded_accounted_cr,0)) - SUM(nvl(xdln.unrounded_accounted_dr,0)) pay_amount --modified for bug10262743
643 FROM xla_ae_lines xdln,
644 xla_ae_headers xlah ,
645 ap_invoice_payments_all aip
646 WHERE aip.invoice_id = cn_invoice_id
647 AND aip.check_id = pn_check_id
648 AND aip.accounting_event_id = cn_event_id
649 AND (xdln.unrounded_accounted_dr IS NOT NULL
650 OR
651 xdln.unrounded_accounted_cr IS NOT NULL)
652 AND xlah.application_id = xdln.application_id
653 AND xlah.ae_header_id = xdln.ae_header_id
654 AND xdln.accounting_class_code ='AWT'
655 AND xlah.event_id = aip.accounting_event_id
656 AND xlah.ledger_id = p_ledger_id
657 AND xlah.accounting_entry_status_code = 'F'
658 GROUP BY
659 xlah.event_id;
660
661 CURSOR c_get_invwht_check (cn_invoice_id NUMBER) IS
662 SELECT ai.invoice_id invoice_id
663 FROM ap_invoice_payments_all aip,
664 ap_invoice_distributions_all aid,
665 ap_invoices_all ai
666 WHERE ai.invoice_id = cn_invoice_id
667 AND ai.invoice_id = aip.invoice_id
668 AND aip.invoice_id = aid.invoice_id
669 AND aid.line_type_lookup_code = 'AWT'
670 AND aip.accounting_event_id = aid.accounting_event_id
671 AND ROWNUM =1;
672
673 CURSOR c_get_invevent_awt(cn_invoice_id NUMBER) IS
674 SELECT xdln.event_id event,
675 -- SUM(xdln.unrounded_accounted_dr) pay_amount Commented for bug 10279386
676 SUM(nvl(xdln.unrounded_accounted_dr,0)) - SUM(nvl(xdln.unrounded_accounted_cr,0)) pay_amount --modified for bug10279386
677 FROM ap_invoice_distributions_all aid ,
678 xla_distribution_links xdln,
679 xla_ae_headers xlah
680 WHERE aid.invoice_id = cn_invoice_id
681 AND aid.line_type_lookup_code = 'AWT'
682 AND xlah.application_id = xdln.application_id
683 AND xdln.event_id = aid.accounting_event_id
684 AND xdln.unrounded_accounted_dr IS NOT NULL
685 AND xlah.ae_header_id = xdln.ae_header_id
686 AND xlah.event_id = xdln.event_id
687 AND xlah.ledger_id = p_ledger_id
688 AND xdln.source_distribution_id_num_1 = aid.invoice_distribution_id
689 AND xlah.accounting_entry_status_code = 'F'
690 AND xdln.source_distribution_type='AP_INV_DIST'
691 GROUP BY xdln.event_id;
692
693 CURSOR c_get_bank_awt IS
694 SELECT TO_NUMBER(ac.global_attribute1) bank_wht_amount
695 FROM AP_CHECKS_ALL ac
696 ,PO_VENDORS pv
697 ,PO_VENDOR_SITES_ALL pvs
698 WHERE ac.check_id =pn_check_id
699 AND ac.vendor_id = pv.vendor_id
700 AND ac.vendor_site_id = pvs.vendor_site_id
701 AND pv.vendor_id = pvs.vendor_id
702 AND ac.global_attribute_category='JE.IL.APXPAWKB.CHECKS'
703 AND ( ( p_information_level = 'V'
704 AND pv.global_attribute17 = '08'
705 AND pv.global_attribute15 in ('9','92','93'))
706 OR
707 ( p_information_level = 'S'
708 AND pvs.global_attribute17 = '08'
709 AND pvs.global_attribute15 in ('9','92','93'))
710 );
711
712
713
714
715
716 BEGIN
717 gn_awt_amount := 0;
718 gn_invoice_id := -99;
719 ln_sign := 1;
720
721 IF pv_void IS NULL THEN
722
723 FOR rec_gross IN c_get_gross
724 LOOP
725 ln_gross_amount := rec_gross.gross_amount;
726 ln_invoice_id := rec_gross.invoice_id;
727 ln_event_id := rec_gross.pay_event_id;
728 END LOOP;
729
730 ELSE
731
732 FOR rec_gross_void IN c_get_gross_void
733 LOOP
734 ln_gross_amount := rec_gross_void.gross_amount;
735 ln_invoice_id := rec_gross_void.invoice_id;
736 ln_event_id := rec_gross_void.pay_event_id;
737 END LOOP;
738
739 END IF;
740
741 FOR rec_invwht IN c_get_invwht_check(ln_invoice_id)
742 LOOP
743 ln_pay_invoice_id := rec_invwht.invoice_id;
744 END LOOP;
745
746 -- the below IF condition check is added by RAJANAR on 05-Mar-2012 as part of ER 13599326
747
748 IF Pn_Awt_Amt_Flag <> 1 THEN
749
750 FOR rec_bank_wht IN c_get_bank_awt
751 LOOP
752 ln_awt_amount := rec_bank_wht.bank_wht_amount;
753 ln_bank_awt_check := 1;
754 END LOOP;
755
756 END IF;
757
758 IF ln_invoice_id = ln_pay_invoice_id THEN
759 FOR rec_payevent_awt IN c_get_payevent_awt(ln_invoice_id,ln_event_id)
760 LOOP
761 ln_awt_amount := rec_payevent_awt.pay_amount;
762 ln_awt_event := rec_payevent_awt.event;
763 END LOOP;
764 ELSIF ln_bank_awt_check <> 1 THEN
765
766 BEGIN
767 SELECT SUM(xdln.unrounded_accounted_dr) INTO ln_invoice_amount
768 FROM ap_invoice_distributions_all aid ,
769 xla_distribution_links xdln,
770 xla_ae_headers xlah
771 WHERE aid.invoice_id = ln_invoice_id
772 AND aid.line_type_lookup_code <> 'AWT'
773 AND xdln.event_id = aid.accounting_event_id
774 AND xdln.unrounded_accounted_dr IS NOT NULL
775 AND xlah.application_id = xdln.application_id
776 AND xlah.ae_header_id = xdln.ae_header_id
777 AND xlah.event_id = xdln.event_id
778 AND xlah.ledger_id = p_ledger_id
779 AND xdln.source_distribution_id_num_1 = aid.invoice_distribution_id
780 AND xlah.accounting_entry_status_code = 'F'
781 AND xdln.source_distribution_type = 'AP_INV_DIST';
782 EXCEPTION
783 WHEN others THEN
784 ln_invoice_amount := 0;
785 IF fnd_debug_log = 'Y' THEN
786 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
787 END IF;
788 END;
789
790 FOR rec_invevent_awt IN c_get_invevent_awt(ln_invoice_id)
791 LOOP
792 ln_awt_amount := (rec_invevent_awt.pay_amount*ln_gross_amount)/(ln_invoice_amount-rec_invevent_awt.pay_amount);
793 ln_awt_event := rec_invevent_awt.event;
794 ln_gross_amount := ln_gross_amount + NVL(ln_awt_amount,0);
795 END LOOP;
796
797 IF fnd_debug_log = 'Y' THEN
798 fnd_file.put_line(fnd_file.log,'Return from Cursor c_get_invevent_awt:Invoice ID:'||ln_invoice_id||'Amount:'||ln_gross_amount);
799 END IF;
800
801 END IF;
802
803 BEGIN
804 IF ln_invoice_id = ln_pay_invoice_id or ln_bank_awt_check = 1 THEN
805
806 SELECT SIGN(SUM(NVL(aip.payment_base_amount,aip.amount))) INTO ln_sign
807 FROM ap_invoice_payments_all aip
808 WHERE aip.accounting_event_id = ln_event_id;
809 /* Bug 12668719 - changed ln_sign to 1,when payment done is 0 in case of full withheld invoices */
810 IF ln_sign = 0 THEN
811 ln_sign := 1;
812 END IF;
813 /* Bug 12668719 */
814 ELSE
815
816 SELECT SIGN(NVL(aip.payment_base_amount,aip.amount)) INTO ln_sign
817 FROM ap_invoice_payments_all aip
818 WHERE aip.invoice_id = ln_invoice_id
819 AND aip.accounting_event_id = ln_event_id;
820
821 END IF;
822
823 EXCEPTION
824 WHEN others THEN
825 ln_sign := 1;
826 END;
827
828 ln_gross_amount := ln_gross_amount * ln_sign;
829
830 BEGIN
831 SELECT invoice_type_lookup_code INTO lv_invoice_type
832 FROM ap_invoices_all WHERE invoice_id = ln_invoice_id;
833 EXCEPTION
834 WHEN others THEN
835 lv_invoice_type := 'STANDARD';
836 END;
837
838 IF lv_invoice_type = 'CREDIT' THEN
839 ln_awt_amount := 0;
840 END IF;
841 /* ELSE -- commented for bug 10262743
842 IF pv_void IS NULL THEN
843 ln_awt_amount := ln_awt_amount;
844 ELSE
845 ln_awt_amount := ln_awt_amount * (-1);
846 END IF;
847 END IF;*/
848
849 gn_awt_amount := NVL(ln_awt_amount,0);
850 gn_invoice_id := NVL(ln_invoice_id,-99);
851 IF fnd_debug_log = 'Y' THEN
852 fnd_file.put_line(fnd_file.log,'Return 1:'||ln_gross_amount);
853 fnd_file.put_line(fnd_file.log,'Return 2:'||gn_awt_amount);
854 END IF;
855 RETURN ln_gross_amount;
856 END get_amounts;
857 /*
858 REM +======================================================================+
859 REM Name: get_gross_amount
860 REM
861 REM Description: This function is called from XML query Q_PAYMENTS
862 REM This functions fetches the Payment + Withholding Amount for
863 REM a given Ledger ID from SLA tables by making a call to get_amounts.
864 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
865 REM +======================================================================+
866 */
867 FUNCTION get_gross_amount(pn_invoice_id NUMBER,pn_check_id NUMBER,pd_start_date DATE, pd_end_date DATE, pv_void NUMBER)
868 RETURN NUMBER
869 IS
870 vn_ret_gross NUMBER;
871 ln_pay_invoice_id NUMBER;
872 i number;
873 t number;
874 ln_invoice_id number;
875 ln_check_id number;
876 v_tot_cnt number; -- Bug 8548767
877 v_cre_cnt number; -- Bug 8548767
878 v_temp varchar2(1); -- Bug 8548767
879 l_inv_with_awt number; --10415410
880 l_bank_awt_check number;
881
882 CURSOR c_get_invwht_check (cn_invoice_id NUMBER) IS
883 SELECT ai.invoice_id invoice_id
884 FROM ap_invoice_payments_all aip,
885 ap_invoice_distributions_all aid,
886 ap_invoices_all ai
887 WHERE ai.invoice_id = cn_invoice_id
888 AND ai.invoice_id = aip.invoice_id
889 AND aip.invoice_id = aid.invoice_id
890 AND aid.line_type_lookup_code = 'AWT'
891 AND aip.accounting_event_id = aid.accounting_event_id
892 AND ROWNUM =1;
893
894 BEGIN
895
896 IF fnd_debug_log = 'Y' THEN
897 fnd_file.put_line(fnd_file.log,'pn_check_id :'||pn_check_id);
898 fnd_file.put_line(fnd_file.log,'pn_invoice_id :'||pn_invoice_id);
899 END IF;
900
901 BEGIN
902 --- Verify if the check is related to Bank AWT
903 SELECT count(1) INTO l_bank_awt_check
904 FROM AP_CHECKS_ALL ac
905 WHERE ac.check_id= pn_check_id
906 AND ac.global_attribute_category='JE.IL.APXPAWKB.CHECKS'
907 AND NVL(ac.global_attribute1,0) > 0;
908
909 IF l_bank_awt_check= 0 THEN
910
911 -- Verify if the invoice has AWT distributions or not
912 SELECT count(1)
913 INTO l_inv_with_awt
914 FROM ap_invoice_distributions_all aid
915 WHERE aid.invoice_id = pn_invoice_id
916 AND aid.line_type_lookup_code = 'AWT'
917 AND aid.awt_flag ='A'
918 AND aid.awt_invoice_payment_id IS NOT NULL
919 AND aid.accounting_date >= pd_start_date
920 AND aid.accounting_date <= pd_end_date;
921
922 -- If the invoice is not having any AWT distribution then simply return zero.
923 IF l_inv_with_awt =0 THEN
924 gn_awt_amount :=0;
925 RETURN 0;
926 END IF;
927
928 END IF;
929
930 EXCEPTION
931 WHEN others THEN
932 fnd_file.put_line(fnd_file.log,'Error in get_gross_amount:');
933 END;
934
935 BEGIN
936
937 t :=0;
938
939 for i in 1..t_check_inv.count loop
940
941 if t_check_inv(i).check_id = pn_check_id then
942 t := 1;
943 ln_check_id := t_check_inv(i).check_id;
944 ln_invoice_id := t_check_inv(i).invoice_id;
945 end if;
946
947 end loop;
948
949 EXCEPTION
950 WHEN others THEN
951 ln_check_id := NULL;
952 ln_invoice_id := NULL;
953 END;
954
955 if t=0 then
956 ln_check_id := NULL;
957 ln_invoice_id := NULL;
958 end if;
959
960
961 FOR rec_invwht IN c_get_invwht_check(pn_invoice_id)
962 LOOP
963 ln_pay_invoice_id := rec_invwht.invoice_id;
964 END LOOP;
965
966
967 IF ln_pay_invoice_id = pn_invoice_id THEN
968 IF fnd_debug_log = 'Y' THEN
969 fnd_file.put_line(fnd_file.log,'pn_check_id :'||pn_check_id);
970 fnd_file.put_line(fnd_file.log,'gn_check_id :'||gn_check_id);
971 fnd_file.put_line(fnd_file.log,'pn_invoice_id :'||pn_invoice_id);
972 fnd_file.put_line(fnd_file.log,'gn_invoice_id :'||gn_invoice_id);
973 END IF;
974 IF ( (NVL(ln_check_id,-99) = pn_check_id ) and (nvl(ln_invoice_id,-99) <> pn_invoice_id)) THEN
975 gn_awt_amount :=0;
976 IF fnd_debug_log = 'Y' THEN
977 fnd_file.put_line(fnd_file.log,'Returning gross amt as 0 for second ');
978 END IF;
979 RETURN 0;
980 END IF;
981 END IF;
982
983 BEGIN
984
985 /* Bug 8548767 Start
986 If there are more than one invoice corresponding to a check_id,if credit memo invoice happens to be first
987 invoice that gets picked then AWT amount will be 0 for always.
988
989 So modified code as below
990
991 If credit memo invoice is picked first then do not put that on the PL/SQL table.
992 Bye pass it similar to code above.If not process it normally.
993 */
994
995 if t=0 then
996
997 select count(1) into v_tot_cnt from ap_invoices_all where invoice_id in (
998 select invoice_id from ap_invoice_payments_all where check_id = pn_check_id);
999
1000 select count(1) into v_cre_cnt from ap_invoices_all where invoice_id in (
1001 select invoice_id from ap_invoice_payments_all where check_id = pn_check_id)
1002 and INVOICE_TYPE_LOOKUP_CODE = 'CREDIT';
1003
1004 if nvl(v_tot_cnt,0) > nvl(v_cre_cnt,0) then
1005 begin
1006 select 'N' into v_temp from ap_invoices_all where invoice_id = pn_invoice_id
1007 and INVOICE_TYPE_LOOKUP_CODE = 'CREDIT';
1008 exception when others then
1009 v_temp := 'Y';
1010 end;
1011 if v_temp = 'N' then
1012 gn_awt_amount :=0;
1013 return 0;
1014 end if;
1015 --elsif nvl(v_tot_cnt,0) = nvl(v_cre_cnt,0) then
1016 else
1017 v_temp := 'Y';
1018 end if;
1019
1020 if v_temp = 'Y' then
1021
1022 t_check_inv(t_check_inv.count+1).check_id:=pn_check_id;
1023 t_check_inv(t_check_inv.count).invoice_id:=pn_invoice_id;
1024
1025 end if;
1026
1027 end if;
1028 -- Bug 8548767 End
1029
1030 vn_ret_gross := NVL(get_amounts(pn_invoice_id,pn_check_id, pd_start_date, pd_end_date,pv_void),0);
1031 EXCEPTION
1032 WHEN others THEN -- Don't Error out the report. Display 0
1033 vn_ret_gross := 0;
1034 IF fnd_debug_log = 'Y' THEN
1035 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
1036 END IF;
1037 END;
1038 IF fnd_debug_log = 'Y' THEN
1039 fnd_file.put_line(fnd_file.log,'Return gross:'||vn_ret_gross);
1040 END IF;
1041 RETURN vn_ret_gross;
1042 END get_gross_amount;
1043 /*
1044 REM +======================================================================+
1045 REM Name: get_amounts
1046 REM
1047 REM Description: This function is called from XML query Q_PAYMENTS
1048 REM This functions fetches the Withholding Amount for
1049 REM a given Ledger ID from SLA tables.
1050 REM When get_amounts is called from get_gross_amount
1051 REM it fetches the AWT data and updates the Global Varieble
1052 REM gn_awt_amount. This function fetches data from this GT Varieble
1053 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
1054 REM +======================================================================+
1055 */
1056 FUNCTION get_awt_amount
1057 RETURN NUMBER
1058 IS
1059 vn_ret_awt NUMBER;
1060 BEGIN
1061 vn_ret_awt := NVL(gn_awt_amount,0);
1062 IF fnd_debug_log = 'Y' THEN
1063 fnd_file.put_line(fnd_file.log,'Return awt:'||vn_ret_awt);
1064 END IF;
1065 RETURN vn_ret_awt;
1066 End Get_Awt_Amount;
1067
1068 -- the below function is added by RAJANAR on 05-Mar-2012 as part of ER 13599326
1069 /*
1070 REM +======================================================================+
1071 REM Name: Get_Awt_Amt
1072 REM
1073 REM Description: This function is called from XML queries Q_PAYMENTS,Q_BY_PERIODS
1074 REM This functions fetches the Withholding Amount for
1075 REM a given Ledger ID from SLA tables excluding the
1076 REM withholding amount paid by bank present in global attribute1
1077 REM column of ap_checks_all table.
1078 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
1079 REM +======================================================================+
1080 */
1081 FUNCTION Get_Awt_Amt( Pn_Invoice_Id IN NUMBER
1082 , Pn_Check_Id IN NUMBER
1083 , pd_start_date IN DATE
1084 , Pd_End_Date IN DATE
1085 , Pv_Void In Number)
1086 Return Number
1087 IS
1088 Vn_Ret_Awt NUMBER;
1089 vn_temp_val NUMBER;
1090 BEGIN
1091 vn_ret_awt := NVL(gn_awt_amount,0);
1092
1093 If Vn_Ret_Awt > 0 Then
1094 vn_temp_val := get_amounts(pn_invoice_id,pn_check_id, pd_start_date, pd_end_date,pv_void,1);
1095 END IF;
1096
1097 Vn_Ret_Awt := Nvl(Gn_Awt_Amount,0);
1098
1102
1099 IF fnd_debug_log = 'Y' THEN
1100 fnd_file.put_line(fnd_file.log,'Return Get_Awt_Amt:'||vn_ret_awt);
1101 End If;
1103 RETURN Vn_Ret_Awt;
1104
1105 END Get_Awt_Amt;
1106
1107
1108
1109 /*
1110 REM +======================================================================+
1111 REM Name: get_invoice_id
1112 REM
1113 REM Description: This function is called from XML query Q_PAYMENTS
1114 REM This functions fetches the Valid Invoice Id
1115 REM which passed the validation in the Cursor c_get_gross
1116 REM of the function get_amounts
1117 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
1118 REM +======================================================================+
1119 */
1120 FUNCTION get_invoice_id
1121 RETURN NUMBER
1122 IS
1123 vn_ret_invoice_id NUMBER;
1124 BEGIN
1125 vn_ret_invoice_id := NVL(gn_invoice_id,-99);
1126 IF fnd_debug_log = 'Y' THEN
1127 fnd_file.put_line(fnd_file.log,'Return Invoice:'||vn_ret_invoice_id);
1128 END IF;
1129 RETURN vn_ret_invoice_id;
1130 End Get_Invoice_Id;
1131
1132 /*
1133 REM +======================================================================+
1134 REM Name: get_awt_bank_amt
1135 REM
1136 REM Description: This function is called from XML query Q_PAYMENTS
1137 REM This functions fetches the Valid Invoice Id
1138 REM which passed the validation in the Cursor c_get_gross
1139 REM of the function get_amounts
1140 REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
1141 REM +======================================================================+
1142 */
1143 FUNCTION get_awt_bank_amt ( Pn_Check_Id IN NUMBER)
1144 RETURN NUMBER
1145 IS
1146 CURSOR c_get_bank_awt IS
1147 SELECT TO_NUMBER(ac.global_attribute1) bank_wht_amount
1148 FROM AP_CHECKS_ALL ac
1149 ,PO_VENDORS pv
1150 ,PO_VENDOR_SITES_ALL pvs
1151 WHERE ac.check_id =pn_check_id
1152 AND ac.vendor_id = pv.vendor_id
1153 AND ac.vendor_site_id = pvs.vendor_site_id
1154 AND pv.vendor_id = pvs.vendor_id
1155 AND ac.global_attribute_category='JE.IL.APXPAWKB.CHECKS'
1156 AND ( ( p_information_level = 'V'
1157 AND pv.global_attribute17 = '08'
1158 AND pv.global_attribute15 in ('9','92','93'))
1159 OR
1160 ( p_information_level = 'S'
1161 AND pvs.global_attribute17 = '08'
1162 AND pvs.global_attribute15 in ('9','92','93'))
1163 );
1164
1165 vn_ret_awt NUMBER;
1166 BEGIN
1167 FOR rec_bank_wht IN c_get_bank_awt
1168 LOOP
1169 vn_ret_awt := rec_bank_wht.bank_wht_amount;
1170 END LOOP;
1171 IF fnd_debug_log = 'Y' THEN
1172 fnd_file.put_line(fnd_file.log,'Return awt amount paid by bank:'||nvl(vn_ret_awt,0));
1173 END IF;
1174 RETURN nvl(vn_ret_awt,0);
1175 EXCEPTION
1176 WHEN others THEN
1177 vn_ret_awt := 0;
1178 IF fnd_debug_log = 'Y' THEN
1179 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error
1180 Code:'||SQLCODE);
1181 END IF;
1182 Return Vn_Ret_Awt;
1183 End get_awt_bank_amt;
1184
1185 END JE_IL_TAX_PKG;
[Home] [Help]