[Home] [Help]
Skip to content
PACKAGE BODY: APPS.JAI_ST_REVERSAL_EXTRACT_PKG
Source
1 package body JAI_ST_REVERSAL_EXTRACT_PKG AS
2 /*$Header: jai_st_reversal_ext.plb 120.9.12020000.3 2012/12/21 08:49:11 qioliu ship $ */
3 /*------------------------------------------------------------------------------------------------------------
4 CHANGE HISTORY
5 ------------------------------------------------------------------------------------------------------------
6 Sl.No. Date Developer BugNo Version Remarks
7 ------------------------------------------------------------------------------------------------------------
8 1. 05-Aug-2005 wenzhou 12895841 Created the initial version
9 2. 30-Sep-2011 Zhhou 13023443 For External event from Open Interface
10 should not be picked up by Reversal
11 3. 31-Dec-2011 qiong 13435306 Service tax reversal can't be updated
12 normally
13 ------------------------------------------------------------------------------------------------------------*/
14 lv_object_name jai_cmn_debug_contexts.log_context%type default 'jai_st_reversal_extract_pkg';
15 lv_member_name jai_cmn_debug_contexts.log_context%type;
16 lv_context jai_cmn_debug_contexts.log_context%type;
17 procedure extract_reversal_trxs
18 ( pv_registration_num jai_rgm_registrations.ATTRIBUTE_VALUE%TYPE
19 , pn_organization_id NUMBER
20 , pn_location_id NUMBER
21 , pv_source VARCHAR2
22 , pn_vendor_id NUMBER
23 , pn_vendor_site_id NUMBER
24 , pv_invoice_number ap_invoices_all.invoice_num%TYPE
25 , pd_invoice_date DATE
26 , pd_as_of_date DATE
27 , p_process_message OUT NOCOPY varchar
28 , p_process_flag OUT NOCOPY varchar2
29 ) IS
30 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
31 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
32 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
33
34 CURSOR c_get_st_accrual_date(p_organization_id NUMBER, p_location_id NUMBER) IS
35 select to_date(attribute_value, 'DD/MM/YYYY')
36 from JAI_RGM_ORG_REGNS_V
37 where regime_id IN (SELECT regime_id
38 FROM jai_rgm_definitions
39 WHERE regime_code = jai_constants.service_regime
40 )
41 and organization_id = p_organization_id
42 and location_id = p_location_id
43 AND attribute_code = 'EFF_DATE_ST_PT'
44 AND attribute_type_code = 'OTHERS'
45 AND registration_type = 'OTHERS'
46 AND (NOT EXISTS
47 (select '1'
48 from JAI_RGM_ORG_REGNS_V
49 where regime_id IN (SELECT regime_id
50 FROM jai_rgm_definitions
51 WHERE regime_code = jai_constants.service_regime
52 )
53 and attribute_code IN 'INV_ORG_CLASSIFICATION'
54 and attribute_value <> 'ORGANIZATION'
55 and organization_id = p_organization_id
56 and location_id = p_location_id)
57 OR
58 NOT EXISTS
59 (select '1'
60 from JAI_RGM_ORG_REGNS_V
61 where regime_id IN (SELECT regime_id
62 FROM jai_rgm_definitions
63 WHERE regime_code = jai_constants.service_regime
64 )
65 and attribute_code IN 'SERVICE TYPE'
66 and attribute_value <> 'OTHER'
67 and organization_id = p_organization_id
68 and location_id = p_location_id)
69 );
70
71 CURSOR c_ap_reversal_unit IS
72 SELECT jrr.attribute_value
73 FROM jai_rgm_registrations jrr, jai_rgm_definitions jrd
74 WHERE jrr.regime_id = jrd.regime_id
75 AND jrd.regime_code = 'SERVICE'
76 AND jrr.attribute_code = 'REVERSAL_AP_UNIT'
77 AND jrr.registration_type = 'OTHERS';
78
79 CURSOR c_ar_reversal_unit IS
80 SELECT jrr.attribute_value
81 FROM jai_rgm_registrations jrr, jai_rgm_definitions jrd
82 WHERE jrr.regime_id = jrd.regime_id
83 AND jrd.regime_code = 'SERVICE'
84 AND jrr.attribute_code = 'REVERSAL_AR_UNIT'
85 AND jrr.registration_type = 'OTHERS';
86
87 CURSOR c_ap_reversal_num IS
88 SELECT jrr.attribute_value
89 FROM jai_rgm_registrations jrr, jai_rgm_definitions jrd
90 WHERE jrr.regime_id = jrd.regime_id
91 AND jrd.regime_code = 'SERVICE'
92 AND jrr.attribute_code = 'REVERSAL_AP_NUM'
93 AND jrr.registration_type = 'OTHERS';
94
95 CURSOR c_ar_reversal_num IS
96 SELECT jrr.attribute_value
97 FROM jai_rgm_registrations jrr, jai_rgm_definitions jrd
98 WHERE jrr.regime_id = jrd.regime_id
99 AND jrd.regime_code = 'SERVICE'
100 AND jrr.attribute_code = 'REVERSAL_AR_NUM'
101 AND jrr.registration_type = 'OTHERS';
102
103 CURSOR c_standard_ap (cp_accrual_date DATE
104 , cp_organization_id NUMBER
105 , cp_location_id NUMBER
106 , cp_vendor_id NUMBER
107 , cp_vendor_site_id NUMBER
108 , cp_invoice_num VARCHAR2
109 , cp_invoice_date DATE
110 , cp_as_of_date DATE
111 )IS
112 SELECT DISTINCT
113 apa.payment_status_flag,
114 jail.organization_id,
115 jail.location_id,
116 apa.invoice_id,
117 apa.invoice_date, apa.invoice_num,
118 apa.vendor_id, apa.vendor_site_id,
119 jail.invoice_line_number,
120 jail.line_amount,
121 aid.accounting_date
122 FROM ap_invoices_all apa,
123 jai_ap_invoice_lines jail,
124 ap_invoice_distributions_all aid
125 WHERE
126 jail.invoice_id = apa.invoice_id
127 AND apa.invoice_id = aid.invoice_id
128 AND aid.posted_flag = 'Y'
129 AND jail.invoice_line_number = aid.invoice_line_number
130 AND jail.parent_invoice_line_number IS NULL
131 AND
132 ( ap_invoices_utility_pkg.get_approval_status(apa.invoice_id
133 ,apa.invoice_amount
134 ,apa.payment_status_flag
135 ,apa.invoice_type_lookup_code) ='APPROVED' )
136 AND invoice_type_lookup_code NOT IN ('DEBIT', 'CREDIT')
137 AND EXISTS (
138 SELECT 1 FROM
139 jai_cmn_document_taxes jdt
140 WHERE apa.invoice_id = jdt.source_doc_id
141 AND jdt.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
142 )
143 AND jail.organization_id = nvl(cp_organization_id,jail.organization_id)
144 AND jail.location_id = nvl(cp_location_id,jail.location_id)
145 AND apa.vendor_id = nvl(cp_vendor_id,apa.vendor_id)
146 AND apa.vendor_site_id = nvl(cp_vendor_site_id,apa.vendor_site_id)
147 AND apa.invoice_num = nvl(cp_invoice_num,apa.invoice_num)
148 AND apa.invoice_date = nvl(cp_invoice_date,apa.invoice_date)
149 AND aid.accounting_date <= cp_as_of_date
150 AND aid.accounting_date >= cp_accrual_date
151 AND NOT EXISTS(
152 SELECT 1
153 FROM JAI_ST_INVOICE_REVERSAL jsir
154 WHERE jsir.invoice_id = apa.invoice_id
155 AND jsir.line_num = jail.invoice_line_number
156 AND jsir.source = 'AP'
157 )
158 and nvl(jail.interface_event,'#') <> 'EXTERNAL'--Added by zhiwei for POT change Bug#13023443 on 20110930
159 ORDER BY aid.accounting_date,apa.vendor_id, apa.invoice_num;
160
161
162 CURSOR c_matched_ap (cp_accrual_date DATE
163 , cp_organization_id NUMBER
164 , cp_location_id NUMBER
165 , cp_vendor_id NUMBER
166 , cp_vendor_site_id NUMBER
167 , cp_invoice_num VARCHAR2
168 , cp_invoice_date DATE
169 , cp_as_of_date DATE
170 )IS
171 SELECT DISTINCT
172 apa.payment_status_flag,
173 po.ship_to_organization_id organization_id,
174 po.ship_to_location_id location_id,
175 apa.invoice_id,invoice_date, apa.invoice_num invoice_num,
176 apa.vendor_id vendor_id, apa.vendor_site_id,
177 line.line_number,
178 line.amount line_amount,
179 line.match_type,
180 aid.accounting_date accounting_date
181 FROM ap_invoices_all apa,
182 ap_invoice_lines_all line,
183 ap_invoice_distributions_all aid,
184 po_line_locations_all po
185 WHERE
186 apa.invoice_id = line.invoice_id
187 AND aid.invoice_id = apa.invoice_id
188 AND aid.posted_flag = 'Y'
189 AND line.line_type_lookup_code ='ITEM'
190 AND aid.invoice_line_number = line.line_number
191 AND line.match_type = 'ITEM_TO_PO'
192 AND po.line_location_id = line.po_line_location_id
193 AND EXISTS (
194 SELECT 1 FROM jai_ap_match_inv_taxes jamit, jai_cmn_taxes_all jct
195 WHERE jamit.tax_id = jct.tax_id
196 AND apa.invoice_id = jamit.invoice_id
197 AND jct.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
198 )
199 AND ap_invoices_utility_pkg.get_approval_status(apa.invoice_id
200 ,apa.invoice_amount
201 ,apa.payment_status_flag
202 ,apa.invoice_type_lookup_code) = 'APPROVED'
203 AND apa.invoice_type_lookup_code NOT IN ('DEBIT', 'CREDIT')
204 AND po.ship_to_organization_id = nvl(cp_organization_id,po.ship_to_organization_id)
205 AND po.ship_to_location_id = nvl(cp_location_id,po.ship_to_location_id)
206 AND apa.vendor_id = nvl(cp_vendor_id,apa.vendor_id)
207 AND apa.vendor_site_id = nvl(cp_vendor_site_id,apa.vendor_site_id)
208 AND apa.invoice_num = nvl(cp_invoice_num,apa.invoice_num)
209 AND apa.invoice_date = nvl(cp_invoice_date,apa.invoice_date)
210 AND aid.accounting_date <= cp_as_of_date
211 AND aid.accounting_date >= cp_accrual_date
212 AND NOT EXISTS(
213 SELECT 1
214 FROM JAI_ST_INVOICE_REVERSAL jsir
215 WHERE jsir.invoice_id = apa.invoice_id
216 AND jsir.line_num = line.line_number
217 AND jsir.source = 'AP'
218 )
219 UNION ALL
220 SELECT DISTINCT
221 apa.payment_status_flag,
222 rcv.organization_id organization_id,
223 --rcv.location_id location_id,--Commented by qiong for bug13435306
224 plla.ship_to_location_id location_id, --Added by qiong for bug13435306
225 apa.invoice_id,invoice_date, apa.invoice_num invoice_num,
226 apa.vendor_id vendor_id, apa.vendor_site_id,
227 line.line_number,
228 line.amount,
229 line.match_type,
230 aid.accounting_date accounting_date
231 FROM ap_invoices_all apa,
232 ap_invoice_lines_all line,
233 jai_rcv_transactions rcv,
234 ap_invoice_distributions_all aid,
235 po_line_locations_all plla,--Added by qiong for bug 13435306
236 rcv_shipment_lines cl --Added by qiong for bug 13435306
237 WHERE
238 line.invoice_id = apa.invoice_id
239 AND aid.invoice_id = apa.invoice_id
240 AND line.line_type_lookup_code ='ITEM'
241 AND line.match_type = 'ITEM_TO_RECEIPT'
242 AND aid.posted_flag = 'Y'
243 AND aid.invoice_line_number = line.line_number
244 AND line.rcv_transaction_id = rcv.transaction_id
245 AND EXISTS (
246 SELECT 1 FROM jai_ap_match_inv_taxes jamit, jai_cmn_taxes_all jct
247 WHERE jamit.tax_id = jct.tax_id
248 AND apa.invoice_id = jamit.invoice_id
249 AND jct.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
250 )
251 AND (ap_invoices_utility_pkg.get_approval_status(apa.invoice_id
252 ,apa.invoice_amount
253 ,apa.payment_status_flag
254 ,apa.invoice_type_lookup_code) = 'APPROVED' )
255 AND invoice_type_lookup_code NOT IN ('DEBIT', 'CREDIT')
256 AND rcv.organization_id = nvl(cp_organization_id,rcv.organization_id)
257 --AND rcv.location_id = nvl(cp_location_id,rcv.location_id) Commented by Qiong for bug13435306
258 AND plla.ship_to_location_id = nvl(cp_location_id, plla.ship_to_location_id) --Added by qiong for bug13435306
259 AND apa.vendor_id = nvl(cp_vendor_id,apa.vendor_id)
260 AND apa.vendor_site_id = nvl(cp_vendor_site_id,apa.vendor_site_id)
261 AND apa.invoice_num = nvl(cp_invoice_num,apa.invoice_num)
262 AND apa.invoice_date = nvl(cp_invoice_date,apa.invoice_date)
263 AND aid.accounting_date <= cp_as_of_date
264 AND aid.accounting_date >= cp_accrual_date
265 -- Added by qiong for bug 13435306 begin
266 AND rcv.shipment_header_id = cl.shipment_header_id
267 AND rcv.shipment_line_id = cl.shipment_line_id
268 AND plla.line_location_id = cl.po_line_location_id
269 -- Added by qiong for bug 13435306 end
270 AND NOT EXISTS(
271 SELECT 1
272 FROM JAI_ST_INVOICE_REVERSAL jsir
273 WHERE jsir.invoice_id = apa.invoice_id
274 AND jsir.line_num = line.line_number
275 AND jsir.source = 'AP'
276 )ORDER BY accounting_date,vendor_id, invoice_num ;
277
278 CURSOR c_ar ( cp_accrual_date DATE
279 , cp_organization_id NUMBER
280 , cp_location_id NUMBER
281 , cp_vendor_id NUMBER
282 , cp_vendor_site_id NUMBER
283 , cp_invoice_num VARCHAR2
284 , cp_invoice_date DATE
285 , cp_as_of_date DATE
286 )IS
287 SELECT DISTINCT
288 jict1.organization_id,
289 jict1.location_id,
290 jict1.customer_trx_id invoice_id,
291 rt1.trx_date invoice_date,
292 rt1.bill_to_customer_id party_id,
293 rt1.bill_to_site_use_id party_site_id,
294 jict1.trx_number invoice_number,
295 jatl1.line_number,
296 jatl1.customer_trx_line_id,
297 rctgl.gl_date
298 FROM
299 ra_customer_trx_all rt1,
300 ra_cust_trx_line_gl_dist_all rctgl,
301 jai_ar_trxs jict1,
302 jai_ar_trx_lines jatl1
303 WHERE
304 rt1.customer_trx_id = jict1.customer_trx_id
305 AND jatl1.customer_trx_id = jict1.customer_trx_id
306 AND rctgl.customer_trx_line_id = jatl1.customer_trx_line_id
307 AND rctgl.customer_trx_id = jict1.customer_trx_id
308 AND rt1.complete_flag = 'Y'
309 AND rctgl.account_class = 'REV'
310 AND EXISTS (SELECT 1
311 FROM jai_ar_trx_tax_lines jrtf_tax,
312 jai_cmn_taxes_all jcta
313 WHERE
314 jatl1.customer_trx_line_id = jrtf_tax.link_to_cust_trx_line_id
315 AND jcta.tax_id = jrtf_tax.tax_id
316 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
317 )
318 AND jict1.organization_id = nvl(cp_organization_id,jict1.organization_id)
319 AND jict1.location_id = nvl(cp_location_id,jict1.location_id)
320 AND rt1.bill_to_customer_id = nvl(cp_vendor_id,rt1.bill_to_customer_id)
321 AND rt1.bill_to_site_use_id = nvl(cp_vendor_site_id,rt1.bill_to_site_use_id)
322 AND jict1.trx_number = nvl(cp_invoice_num,jict1.trx_number)
323 AND rt1.trx_date = nvl(cp_invoice_date,rt1.trx_date)
324 AND rctgl.gl_date <= cp_as_of_date
325 AND rctgl.gl_date >= cp_accrual_date
326 AND NOT EXISTS(
327 SELECT 1
328 FROM JAI_ST_INVOICE_REVERSAL jsir
329 WHERE jsir.invoice_id = jict1.customer_trx_id
330 AND jsir.line_num = jatl1.line_number
331 AND jsir.source = 'AR'
332 )
333 ORDER BY rctgl.gl_date,party_id, invoice_number;
334
335 CURSOR c_max_payment_date(cp_invoice_id NUMBER) IS
336 SELECT MAX(aca.check_date)
337 FROM ap_invoice_payments_all aipa,
338 ap_checks_all aca
339 WHERE aipa.invoice_id = cp_invoice_id
340 AND aca.check_id = aipa.check_id;
341
342 CURSOR c_max_prepayment_date (cp_invoice_id NUMBER) IS
343 SELECT MAX(aila.accounting_date)
344 FROM ap_invoice_lines_all aila
345 WHERE aila.invoice_id = cp_invoice_id
346 AND aila.line_type_lookup_code = 'PREPAY';
347
348 CURSOR c_sum_all_tax_amt_standard(cp_invoice_id NUMBER, cp_line_num NUMBER, cd_as_of_date DATE) IS
349 SELECT NVL(SUM(jcdt.tax_amt),0)
350 FROM jai_cmn_document_taxes jcdt
351 ,jai_cmn_taxes_all jcta
352 ,jai_ap_invoice_lines jail
353 ,ap_invoice_distributions_all aida
354 WHERE
355 jcdt.source_doc_id = cp_invoice_id
356 AND jail.invoice_id = jcdt.source_doc_id
357 AND jail.parent_invoice_line_number = cp_line_num
358 AND jail.invoice_line_number = jcdt.source_doc_line_id
359 AND jail.invoice_id = aida.invoice_id
360 AND jcdt.tax_id = jcta.tax_id
361 AND nvl(jcta.inclusive_tax_flag,'N') <> 'Y'
362 AND jcdt.source_doc_line_id = aida.invoice_line_number
363 AND aida.accounting_date <= cd_as_of_date
364 --Added by Qiong for reverse charge bug#16001407, begin
365 AND jai_cmn_reverse_charge_pkg.is_reverse_charge_dist(aida.invoice_distribution_id)<> 'Y';
366 --Added by Qiong for reverse charge bug#16001407 begin
367
368 CURSOR c_sum_rv_st_tax_amt_standard(cp_invoice_id NUMBER, cp_line_num NUMBER, cd_as_of_date DATE) IS
369 SELECT sum(jcdt.tax_amt)
370 FROM jai_cmn_document_taxes jcdt
371 ,jai_ap_invoice_lines jail
372 ,ap_invoice_distributions_all aida
373 WHERE
374 jcdt.source_doc_id = cp_invoice_id
375 and jail.invoice_id = jcdt.source_doc_id
376 AND jail.invoice_id = aida.invoice_id
377 AND jail.parent_invoice_line_number = cp_line_num
378 AND jail.invoice_line_number = jcdt.source_doc_line_id
379 AND jcdt.source_doc_line_id = aida.invoice_line_number
380 AND jcdt.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
381 AND NVL(jcdt.modvat_flag,'N')='Y'
382 AND aida.accounting_date <= cd_as_of_date
383 --Added by Qiong for reverse charge bug#16001407 begin
384 AND jai_cmn_reverse_charge_pkg.is_reverse_charge_dist(aida.invoice_distribution_id)<> 'Y';
385 --Added by Qiong for reverse charge bug#16001407 begin
386
387
388 CURSOR c_sum_all_tax_amt_matched(cp_invoice_id NUMBER, cp_line_num NUMBER, cd_as_of_date DATE, cp_match_type VARCHAR2) IS
389 SELECT NVL(sum(jcdt_st.tax_amount),0)
390 FROM jai_ap_match_inv_taxes jcdt_st
391 , jai_cmn_taxes_all jcta
392 ,ap_invoice_distributions_all aida
393 ,ap_invoice_lines_all line
394 WHERE
395 jcdt_st.invoice_id = cp_invoice_id
396 AND jcdt_st.parent_invoice_line_number = cp_line_num
397 AND line.invoice_id = jcdt_st.invoice_id
398 AND aida.invoice_id = jcdt_st.invoice_id
399 AND aida.invoice_line_number = line.line_number
400 AND line.line_number = jcdt_st.parent_invoice_line_number
401 AND line.line_type_lookup_code ='ITEM'
402 AND line.match_type = cp_match_type
403 AND jcdt_st.tax_id = jcta.tax_id
404 AND nvl(jcta.inclusive_tax_flag,'N') <> 'Y'
405 AND aida.accounting_date <= cd_as_of_date;
406
407 CURSOR c_sum_rv_st_tax_amt_matched(cp_invoice_id NUMBER, cp_line_num NUMBER, cd_as_of_date DATE, cp_match_type VARCHAR2) IS
408 SELECT sum(jcdt_st.tax_amount)
409 FROM jai_ap_match_inv_taxes jcdt_st
410 , jai_cmn_taxes_all jcta
411 ,ap_invoice_distributions_all aida
412 ,ap_invoice_lines_all line
413 WHERE
414 jcdt_st.invoice_id = cp_invoice_id
415 AND jcdt_st.parent_invoice_line_number = cp_line_num
416 AND line.invoice_id = jcdt_st.invoice_id
417 AND aida.invoice_id = jcdt_st.invoice_id
418 AND aida.invoice_line_number = line.line_number
419 AND line.line_number = jcdt_st.parent_invoice_line_number
420 AND line.line_type_lookup_code ='ITEM'
421 AND line.match_type = cp_match_type
422 AND jcdt_st.tax_id = jcta.tax_id
423 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
424 AND NVL(jcdt_st.recoverable_flag,'N')='Y'
425 AND aida.accounting_date <= cd_as_of_date;
426
427 CURSOR c_paid_amt(cp_invoice_id NUMBER, cp_grace_date DATE) IS
428 SELECT nvl(SUM(apc.amount),0)
429 FROM ap_checks_all apc
430 , ap_invoice_payments_all apip
431 WHERE apc.check_id = apip.check_id
432 AND apip.invoice_id = cp_invoice_id
433 AND apc.check_date <= cp_grace_date;
434
435 CURSOR c_prepaid_amt(cp_invoice_id NUMBER, cp_grace_date DATE) IS
436 SELECT abs(nvl(SUM(aila.amount),0))
437 FROM ap_invoice_lines_all aila
438 WHERE aila.invoice_id = cp_invoice_id
439 AND aila.line_type_lookup_code = 'PREPAY'
440 AND aila.accounting_date <= cp_grace_date;
441
442 CURSOR c_total_ap_amount(cp_invoice_id NUMBER) IS
443 select invoice_amount
444 from ap_invoices_all
445 where invoice_id = cp_invoice_id;
446
447 CURSOR c_total_ar_amount(cp_invoice_id NUMBER) IS
448 SELECT amount_due_original
449 ,amount_due_remaining
450 FROM AR_payment_schedules_all
451 WHERE customer_trx_id = cp_invoice_id;
452
453 CURSOR c_total_ar_line_amount(cp_invoice_id NUMBER, cp_line_num NUMBER, cd_as_of_date DATE) IS
454 SELECT sum(rctg.acctd_amount)
455 FROM RA_CUSTOMER_TRX_LINES_ALL rctl
456 ,ra_cust_trx_line_gl_dist_all rctg
457 WHERE rctg.customer_trx_id =cp_invoice_id
458 AND rctg.customer_trx_id = rctl.customer_trx_id
459 AND rctg.customer_trx_line_id = rctl.customer_trx_line_id
460 AND rctl.line_number = cp_line_num
461 AND rctg.gl_date <= cd_as_of_date;
462
463 CURSOR c_ar_applied_amount(cp_invoice_id NUMBER, cd_reversal_date DATE) IS
464 SELECT nvl(SUM(amount_applied),0)
465 FROM AR_RECEIVABLE_APPLICATIONS_ALL
466 WHERE applied_customer_trx_id = cp_invoice_id
467 AND apply_date<= cd_reversal_date;
468
469 CURSOR c_ar_rc_st_amount(cp_invoice_id NUMBER, cp_line_num NUMBER, cd_reversal_date DATE) IS
470 SELECT jcta.mod_cr_percentage, jrtf.tax_amount
471 FROM jai_ar_trx_tax_lines jrtf,
472 jai_ar_trx_lines jatl,
473 jai_cmn_taxes_all jcta,
474 ra_cust_trx_line_gl_dist_all rctgl
475 WHERE jatl.customer_trx_id = cp_invoice_id
476 AND jatl.customer_trx_line_id = jrtf.link_to_cust_trx_line_id
477 AND rctgl.customer_trx_line_id = jatl.customer_trx_line_id
478 AND rctgl.customer_trx_id = cp_invoice_id
479 AND jatl.line_number = cp_line_num
480 AND rctgl.account_class = 'REV'
481 AND rctgl.gl_date <= cd_reversal_date
482 AND jcta.tax_id = jrtf.tax_id
483 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
484 AND jcta.mod_cr_percentage > 0;
485
486 ln_serial_num NUMBER := 1;
487 ln_reversal_num NUMBER;
488 lv_reversal_unit VARCHAR2(30);
489 ld_grace_date DATE;
490 ld_accrual_date DATE;
491 ld_new_as_of_date DATE;
492 ld_max_payment_date DATE;
493 ld_max_prepayment_date DATE;
494 ln_original_line_amt NUMBER;
495 ln_sum_tax_amt NUMBER;
496 ln_rcv_st_amt NUMBER;
497 ln_paid_amt NUMBER;
498 ln_prepaid_amt NUMBER;
499 ln_unpaid_pctg NUMBER;
500 ln_total_amt NUMBER;
501 ln_open_amt NUMBER;
502 ln_tax_to_be_adjusted NUMBER;
503 ln_un_applied_amt NUMBER;
504 lv_reversal_flag VARCHAR2(1) := 'Y';
505 ln_reg_id number;
506 r_reversal_gt JAI_ST_INVOICE_REVERSAL_GT%ROWTYPE;
507 r_standard_ap c_standard_ap%ROWTYPE;
508 r_matched_ap c_matched_ap%ROWTYPE;
509 r_ar c_ar%ROWTYPE;
510 r_ar_rc_st_amount c_ar_rc_st_amount%ROWTYPE;
511
512 BEGIN
513 lv_member_name := 'EXTRACT_ST_REVERSAL_TRXS';
514 set_debug_context;
515 p_process_flag := jai_constants.SUCCESSFUL;
516 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
517 , pn_reg_id => ln_reg_id
518 );
519
520 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Call Parameters:' || fnd_global.local_chr(10) ||
521 'pv_registration_num =' ||pv_registration_num || fnd_global.local_chr(10) ||
522 'pn_organization_id =' ||pn_organization_id || fnd_global.local_chr(10) ||
523 'pn_location_id =' ||pn_location_id || fnd_global.local_chr(10) ||
524 'pv_source =' ||pv_source || fnd_global.local_chr(10) ||
525 'pn_vendor_id =' ||pn_vendor_id || fnd_global.local_chr(10) ||
526 'pn_vendor_site_id =' ||pn_vendor_site_id || fnd_global.local_chr(10) ||
527 'pv_invoice_number =' ||pv_invoice_number || fnd_global.local_chr(10) ||
528 'pd_invoice_date =' ||pd_invoice_date || fnd_global.local_chr(10) ||
529 'pd_as_of_date =' ||pd_as_of_date
530 );
531 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'pv_registration_num =' ||pv_registration_num );
532 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'pn_organization_id =' ||pn_organization_id );
533 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'pn_location_id =' ||pn_location_id );
534 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'pv_source =' ||pv_source );
535 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'pn_vendor_id =' ||pn_vendor_id );
536 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'pn_vendor_site_id =' ||pn_vendor_site_id );
537 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'pv_invoice_number =' ||pv_invoice_number );
538 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'pd_invoice_date =' ||pd_invoice_date );
539 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'pd_as_of_date =' ||pd_as_of_date );
540
541 OPEN c_get_st_accrual_date(pn_organization_id, pn_location_id);
542 FETCH c_get_st_accrual_date INTO ld_accrual_date;
543 CLOSE c_get_st_accrual_date;
544
545 IF pv_source = 'AP' THEN
546 OPEN c_ap_reversal_unit;
547 FETCH c_ap_reversal_unit INTO lv_reversal_unit;
548 CLOSE c_ap_reversal_unit;
549 OPEN c_ap_reversal_num;
550 FETCH c_ap_reversal_num INTO ln_reversal_num;
551 CLOSE c_ap_reversal_num;
552 ELSE
553 OPEN c_ar_reversal_unit;
554 FETCH c_ar_reversal_unit INTO lv_reversal_unit;
555 CLOSE c_ar_reversal_unit;
556 OPEN c_ar_reversal_num;
557 FETCH c_ar_reversal_num INTO ln_reversal_num;
558 CLOSE c_ar_reversal_num;
559 END IF;
560
561 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'lv_reversal_unit='||lv_reversal_unit);
562 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_reversal_num='||ln_reversal_num);
563
564 IF lv_reversal_unit = 'DAY' THEN
565 /*Modified by Qinglei 14-Dec-2011 bug# 13395658 begin*/
566 SELECT decode(sign(pd_as_of_date - SYSDATE)
567 ,-1
568 ,pd_as_of_date
569 ,1
570 ,SYSDATE
571 ,pd_as_of_date) - ln_reversal_num
572 INTO ld_new_as_of_date
573 FROM dual;
574 /*Modified by Qinglei 14-Dec-2011 bug# 13395658 end*/
575 ELSE
576 /*Modified by Qinglei 14-Dec-2011 bug# 13395658 begin*/
577 SELECT add_months(decode(sign(pd_as_of_date - SYSDATE)
578 ,-1
579 ,pd_as_of_date
580 ,1
581 ,SYSDATE
582 ,pd_as_of_date),-ln_reversal_num)
583 INTO ld_new_as_of_date
584 FROM dual;
585 /*Modified by Qinglei 14-Dec-2011 bug# 13395658 end*/
586 END IF;
587 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ld_new_as_of_date='||ld_new_as_of_date);
588
589 /*Commented by Qinglei 14-Dec-2011 bug# 13395658*/
590 /*IF ld_new_as_of_date > pd_as_of_date THEN
591 ld_new_as_of_date := pd_as_of_date;
592 END IF;*/
593
594 IF pv_source = 'AP' THEN
595 FOR r_standard_ap IN c_standard_ap( ld_accrual_date
596 ,pn_organization_id
597 ,pn_location_id
598 ,pn_vendor_id
599 ,pn_vendor_site_id
600 ,pv_invoice_number
601 ,pd_invoice_date
602 ,ld_new_as_of_date)
603 LOOP
604 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'invoice_id='||r_standard_ap.invoice_id);
605 IF lv_reversal_unit = 'DAY' THEN
606 ld_grace_date := r_standard_ap.accounting_date + ln_reversal_num;
607 ELSE
608 SELECT add_months(r_standard_ap.accounting_date,ln_reversal_num) INTO ld_grace_date FROM dual;
609 END IF;--calculate grace date
610 lv_reversal_flag := 'Y';
611 IF r_standard_ap.payment_status_flag = 'Y' THEN
612 OPEN c_max_payment_date(r_standard_ap.invoice_id);
613 FETCH c_max_payment_date INTO ld_max_payment_date;
614 CLOSE c_max_payment_date;
615
616 OPEN c_max_prepayment_date(r_standard_ap.invoice_id);
617 FETCH c_max_prepayment_date INTO ld_max_prepayment_date;
618 CLOSE c_max_prepayment_date;
619
620 IF ld_max_prepayment_date IS NULL AND ld_max_payment_date IS NULL THEN
621 p_process_message := 'When payment status is Y but neither payment nor prepayment.';
622 p_process_flag := jai_constants.EXPECTED_ERROR;
623 return;
624
625 END IF;
626 ld_max_payment_date := get_max_date(ld_max_payment_date,ld_max_prepayment_date);
627
628 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ld_max_payment_date='||ld_max_payment_date);
629 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'grace_date='||ld_grace_date);
630 IF trunc(ld_max_payment_date) <= trunc(ld_grace_date) THEN
631 lv_reversal_flag := 'N';
632 END IF;--this condition indicate that the payment has been done before grace date, no need to reversal.
633 END IF;--payment status flag is 'Y'
634
635 IF lv_reversal_flag = 'Y' THEN
636 OPEN c_sum_all_tax_amt_standard(r_standard_ap.invoice_id, r_standard_ap.invoice_line_number,ld_new_as_of_date);
637 FETCH c_sum_all_tax_amt_standard INTO ln_sum_tax_amt;
638 CLOSE c_sum_all_tax_amt_standard;
639
640 ln_original_line_amt := r_standard_ap.line_amount + ln_sum_tax_amt;
641
642 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_sum_tax_amt='||ln_sum_tax_amt);
643 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_original_line_amt='||ln_original_line_amt);
644 OPEN c_sum_rv_st_tax_amt_standard(r_standard_ap.invoice_id, r_standard_ap.invoice_line_number,ld_new_as_of_date);
645 FETCH c_sum_rv_st_tax_amt_standard INTO ln_rcv_st_amt;
646 CLOSE c_sum_rv_st_tax_amt_standard;
647
648 OPEN c_total_ap_amount(r_standard_ap.invoice_id);
649 FETCH c_total_ap_amount INTO ln_total_amt;
650 CLOSE c_total_ap_amount;
651
652 OPEN c_paid_amt(r_standard_ap.invoice_id,ld_grace_date);
653 FETCH c_paid_amt INTO ln_paid_amt;
654 CLOSE c_paid_amt;
655
656 OPEN c_prepaid_amt(r_standard_ap.invoice_id,ld_grace_date);
657 FETCH c_prepaid_amt INTO ln_prepaid_amt;
658 CLOSE c_prepaid_amt;
659
660 ln_paid_amt := nvl(ln_paid_amt,0) + nvl (ln_prepaid_amt,0);
661
662 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'grace_date='||ld_grace_date);
663 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_rcv_st_amt='||ln_rcv_st_amt);
664 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_total_amt='||ln_total_amt);
665 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_paid_amt='||ln_paid_amt);
666
667 ln_unpaid_pctg := 1 - ln_paid_amt/ln_total_amt;
668 ln_open_amt := ln_original_line_amt * ln_unpaid_pctg;
669 ln_tax_to_be_adjusted := ln_rcv_st_amt * ln_unpaid_pctg;
670
671 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_unpaid_pctg='||ln_unpaid_pctg);
672 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_open_amt='||ln_open_amt);
673 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_tax_to_be_adjusted='||ln_tax_to_be_adjusted);
674 r_reversal_gt.SERIAL_NUM := ln_serial_num;
675 r_reversal_gt.INVOICE_NUM := r_standard_ap.invoice_num;
676 r_reversal_gt.LINE_NUMBER := r_standard_ap.invoice_line_number;
677 r_reversal_gt.ACCOUNTING_DATE := r_standard_ap.accounting_date;
678 r_reversal_gt.ORIGINAL_LINE_AMT := ln_original_line_amt;
679 r_reversal_gt.RCV_SVC_TAX := ln_rcv_st_amt;
680 r_reversal_gt.OPEN_AMT := ln_open_amt;
681 r_reversal_gt.TAX_TO_BE_ADJUSTED := ln_tax_to_be_adjusted;
682
683 r_reversal_gt.source := 'AP';
684 r_reversal_gt.invoice_id := r_standard_ap.invoice_id;
685 r_reversal_gt.party_id := r_standard_ap.vendor_id;
686 r_reversal_gt.party_site_id := r_standard_ap.vendor_site_id;
687 r_reversal_gt.DATE_OF_REVERSAL := ld_grace_date;
688
689 insert_trxs(r_reversal_gt);
690 ln_serial_num := ln_serial_num + 1;
691 END IF;
692 END LOOP;
693
694 FOR r_matched_ap IN c_matched_ap( ld_accrual_date
695 ,pn_organization_id
696 ,pn_location_id
697 ,pn_vendor_id
698 ,pn_vendor_site_id
699 ,pv_invoice_number
700 ,pd_invoice_date
701 ,ld_new_as_of_date)
702
703 LOOP
704 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'invoice_id='||r_matched_ap.invoice_id);
705 IF lv_reversal_unit = 'DAY' THEN
706 ld_grace_date := r_matched_ap.accounting_date + ln_reversal_num;
707 ELSE
708 SELECT add_months(r_matched_ap.accounting_date,ln_reversal_num) INTO ld_grace_date FROM dual;
709 END IF;--calculate grace date
710 lv_reversal_flag := 'Y';--Added by qiong for bug13435306
711 IF r_matched_ap.payment_status_flag = 'Y' THEN
712 OPEN c_max_payment_date(r_matched_ap.invoice_id);
713 FETCH c_max_payment_date INTO ld_max_payment_date;
714 CLOSE c_max_payment_date;
715
716 OPEN c_max_prepayment_date(r_matched_ap.invoice_id);
717 FETCH c_max_prepayment_date INTO ld_max_prepayment_date;
718 CLOSE c_max_prepayment_date;
719
720 IF ld_max_prepayment_date IS NULL AND ld_max_payment_date IS NULL THEN
721 p_process_message := 'When payment status is Y but neither payment nor prepayment.';
722 p_process_flag := jai_constants.EXPECTED_ERROR;
723 return;
724
725 END IF;
726 ld_max_payment_date := get_max_date(ld_max_payment_date,ld_max_prepayment_date);
727
728 IF lv_reversal_unit = 'DAY' THEN
729 ld_grace_date := r_matched_ap.accounting_date + ln_reversal_num;
730 ELSE
731 SELECT add_months(r_matched_ap.accounting_date,ln_reversal_num) INTO ld_grace_date FROM dual;
732 END IF;--calculate grace date
733
734 lv_reversal_flag := 'Y';
735
736 IF ld_max_payment_date < ld_grace_date THEN
737 lv_reversal_flag := 'N';
738 END IF;--this condition indicate that the payment has been done before grace date, no need to reversal.
739 END IF;--payment status flag is 'Y'
740 IF lv_reversal_flag = 'Y' THEN
741 OPEN c_sum_all_tax_amt_matched(r_matched_ap.invoice_id, r_matched_ap.line_number,ld_new_as_of_date,r_matched_ap.match_type);
742 FETCH c_sum_all_tax_amt_matched INTO ln_sum_tax_amt;
743 CLOSE c_sum_all_tax_amt_matched;
744
745 ln_original_line_amt := r_matched_ap.line_amount + ln_sum_tax_amt;
746
747 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_sum_tax_amt='||ln_sum_tax_amt);
748 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_original_line_amt='||ln_original_line_amt);
749 OPEN c_sum_rv_st_tax_amt_matched(r_matched_ap.invoice_id, r_matched_ap.line_number,ld_new_as_of_date,r_matched_ap.match_type);
750 FETCH c_sum_rv_st_tax_amt_matched INTO ln_rcv_st_amt;
751 CLOSE c_sum_rv_st_tax_amt_matched;
752
753 OPEN c_total_ap_amount(r_matched_ap.invoice_id);
754 FETCH c_total_ap_amount INTO ln_total_amt;
755 CLOSE c_total_ap_amount;
756
757 OPEN c_paid_amt(r_matched_ap.invoice_id,ld_grace_date);
758 FETCH c_paid_amt INTO ln_paid_amt;
759 CLOSE c_paid_amt;
760
761 OPEN c_prepaid_amt(r_matched_ap.invoice_id,ld_grace_date);
762 FETCH c_prepaid_amt INTO ln_prepaid_amt;
763 CLOSE c_prepaid_amt;
764
765 ln_paid_amt := nvl(ln_paid_amt,0) + nvl (ln_prepaid_amt,0);
766 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_rcv_st_amt='||ln_rcv_st_amt);
767 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_total_amt='||ln_total_amt);
768 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_paid_amt='||ln_paid_amt);
769
770 ln_unpaid_pctg := 1 - ln_paid_amt/ln_total_amt;
771 ln_open_amt := ln_original_line_amt * ln_unpaid_pctg;
772 ln_tax_to_be_adjusted := ln_rcv_st_amt * ln_unpaid_pctg;
773
774 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_unpaid_pctg='||ln_unpaid_pctg);
775 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_open_amt='||ln_open_amt);
776 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_tax_to_be_adjusted='||ln_tax_to_be_adjusted);
777 r_reversal_gt.SERIAL_NUM := ln_serial_num;
778 r_reversal_gt.INVOICE_NUM := r_matched_ap.invoice_num;
779 r_reversal_gt.LINE_NUMBER := r_matched_ap.line_number;
780 r_reversal_gt.match_type := r_matched_ap.match_type;
781 r_reversal_gt.ACCOUNTING_DATE := r_matched_ap.accounting_date;
782 r_reversal_gt.ORIGINAL_LINE_AMT := ln_original_line_amt;
783 r_reversal_gt.RCV_SVC_TAX := ln_rcv_st_amt;
784 r_reversal_gt.OPEN_AMT := ln_open_amt;
785 r_reversal_gt.TAX_TO_BE_ADJUSTED := ln_tax_to_be_adjusted;
786 r_reversal_gt.source := 'AP';
787 r_reversal_gt.invoice_id := r_matched_ap.invoice_id;
788 r_reversal_gt.party_id := r_matched_ap.vendor_id;
789 r_reversal_gt.party_site_id := r_matched_ap.vendor_site_id;
790 r_reversal_gt.DATE_OF_REVERSAL := ld_grace_date;
791
792
793 insert_trxs(r_reversal_gt);
794 ln_serial_num := ln_serial_num + 1;
795 END IF;
796 END LOOP;
797 ELSE
798 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'Process or AR invoices.');
799 FOR r_ar IN c_ar( ld_accrual_date
800 ,pn_organization_id
801 ,pn_location_id
802 ,pn_vendor_id
803 ,pn_vendor_site_id
804 ,pv_invoice_number
805 ,pd_invoice_date
806 ,ld_new_as_of_date)
807
808 LOOP
809 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'invoice_id='||r_ar.invoice_id);
810 OPEN c_total_ar_amount(r_ar.invoice_id);
811 FETCH c_total_ar_amount INTO ln_total_amt,ln_un_applied_amt;
812 CLOSE c_total_ar_amount;
813
814 IF lv_reversal_unit = 'DAY' THEN
815 ld_grace_date := r_ar.gl_date + ln_reversal_num;
816 ELSE
817 SELECT add_months(r_ar.gl_date,ln_reversal_num) INTO ld_grace_date FROM dual;
818 END IF;--calculate grace date
819
820 OPEN c_ar_applied_amount(r_ar.invoice_id,ld_grace_date);
821 FETCH c_ar_applied_amount INTO ln_paid_amt;
822 CLOSE c_ar_applied_amount;
823
824 IF (ln_un_applied_amt = 0 AND ln_total_amt - ln_paid_amt <> 0 )
825 OR ln_un_applied_amt <> 0 THEN
826 OPEN c_total_ar_line_amount(r_ar.invoice_id, r_ar.line_number,ld_new_as_of_date);
827 FETCH c_total_ar_line_amount INTO ln_original_line_amt;
828 CLOSE c_total_ar_line_amount;
829
830 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_original_line_amt='||ln_original_line_amt);
831 ln_rcv_st_amt := 0;
832 FOR r_ar_rc_st_amount IN c_ar_rc_st_amount(r_ar.invoice_id, r_ar.line_number,ld_new_as_of_date)
833 LOOP
834 ln_rcv_st_amt := ln_rcv_st_amt + r_ar_rc_st_amount.tax_amount * r_ar_rc_st_amount.mod_cr_percentage/100;
835 END LOOP;
836
837 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_rcv_st_amt='||ln_rcv_st_amt);
838 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_total_amt='||ln_total_amt);
839 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_paid_amt='||ln_paid_amt);
840
841 ln_unpaid_pctg := 1 - ln_paid_amt/ln_total_amt;
842 ln_open_amt := ln_original_line_amt * ln_unpaid_pctg;
843 ln_tax_to_be_adjusted := ln_rcv_st_amt * ln_unpaid_pctg;
844
845 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_unpaid_pctg='||ln_unpaid_pctg);
846 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_open_amt='||ln_open_amt);
847 jai_cmn_debug_contexts_pkg.print (ln_reg_id,'ln_tax_to_be_adjusted='||ln_tax_to_be_adjusted);
848 r_reversal_gt.SERIAL_NUM := ln_serial_num;
849 r_reversal_gt.INVOICE_NUM := r_ar.invoice_number;
850 r_reversal_gt.LINE_NUMBER := r_ar.line_number;
851 r_reversal_gt.ACCOUNTING_DATE := r_ar.gl_date;
852 r_reversal_gt.ORIGINAL_LINE_AMT := ln_original_line_amt;
853 r_reversal_gt.RCV_SVC_TAX := ln_rcv_st_amt;
854 r_reversal_gt.OPEN_AMT := ln_open_amt;
855 r_reversal_gt.TAX_TO_BE_ADJUSTED := ln_tax_to_be_adjusted;
856 r_reversal_gt.source := 'AR';
857 r_reversal_gt.invoice_id := r_ar.invoice_id;
858 r_reversal_gt.CUSTOMER_TRX_LINE_ID := r_ar.customer_trx_line_id;
859 r_reversal_gt.party_id := r_ar.party_id;
860 r_reversal_gt.party_site_id := r_ar.party_site_id;
861 r_reversal_gt.DATE_OF_REVERSAL := ld_grace_date;
862
863 insert_trxs(r_reversal_gt);
864 ln_serial_num := ln_serial_num + 1;
865
866 END IF;--ln_un_applied_amt = 0 currently
867
868 END LOOP;
869 END IF;
870 exception
871 when others then
872 p_process_flag := jai_constants.unexpected_error;
873 p_process_message := lv_context||'->'||sqlerrm;
874 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
875 jai_cmn_debug_contexts_pkg.print_stack;
876 END extract_reversal_trxs;
877
878 PROCEDURE insert_trxs (r_reversal_trx JAI_ST_INVOICE_REVERSAL_GT%ROWTYPE) IS
879 BEGIN
880 INSERT INTO JAI_ST_INVOICE_REVERSAL_GT(
881 SERIAL_NUM
882 ,INVOICE_NUM
883 ,LINE_NUMBER
884 ,match_type
885 ,ACCOUNTING_DATE
886 ,ORIGINAL_LINE_AMT
887 ,RCV_SVC_TAX
888 ,OPEN_AMT
889 ,TAX_TO_BE_ADJUSTED
890 ,SOURCE
891 ,invoice_id
892 ,customer_trx_line_id
893 ,DATE_OF_REVERSAL
894 ,party_id
895 ,party_site_id
896 )VALUES (
897 r_reversal_trx.SERIAL_NUM
898 ,r_reversal_trx.INVOICE_NUM
899 ,r_reversal_trx.LINE_NUMBER
900 ,r_reversal_trx.match_type
901 ,r_reversal_trx.ACCOUNTING_DATE
902 ,r_reversal_trx.ORIGINAL_LINE_AMT
903 ,r_reversal_trx.RCV_SVC_TAX
904 ,r_reversal_trx.OPEN_AMT
905 ,r_reversal_trx.TAX_TO_BE_ADJUSTED
906 ,r_reversal_trx.SOURCE
907 ,r_reversal_trx.invoice_id
908 ,r_reversal_trx.customer_trx_line_id
909 ,r_reversal_trx.DATE_OF_REVERSAL
910 ,r_reversal_trx.party_id
911 ,r_reversal_trx.party_site_id
912 );
913 END insert_trxs;
914
915 procedure set_debug_context
916 is
917
918 begin
919 lv_context := rtrim(lv_object_name || '.'||lv_member_name,'.');
920 end set_debug_context;
921
922
923 FUNCTION is_paid_after_reversal(pd_payment_date DATE, pd_gl_date DATE, pv_source VARCHAR2) RETURN VARCHAR2
924 IS
925 ld_grace_date DATE;
926 lv_flag VARCHAR2(1);
927 BEGIN
928
929 ld_grace_date := get_reversal_date(pd_gl_date,pv_source);
930
931 IF pd_payment_date > ld_grace_date THEN
932 lv_flag := 'Y';
933 ELSE
934 lv_flag := 'N';
935 END IF;
936 RETURN lv_flag;
937 END is_paid_after_reversal;
938 FUNCTION get_reversal_date(pd_gl_date DATE, pv_source VARCHAR2) RETURN DATE IS
939 CURSOR c_ap_reversal_unit IS
940 SELECT jrr.attribute_value
941 FROM jai_rgm_registrations jrr, jai_rgm_definitions jrd
942 WHERE jrr.regime_id = jrd.regime_id
943 AND jrd.regime_code = 'SERVICE'
944 AND jrr.attribute_code = 'REVERSAL_AP_UNIT'
945 AND jrr.registration_type = 'OTHERS';
946
947 CURSOR c_ar_reversal_unit IS
948 SELECT jrr.attribute_value
949 FROM jai_rgm_registrations jrr, jai_rgm_definitions jrd
950 WHERE jrr.regime_id = jrd.regime_id
951 AND jrd.regime_code = 'SERVICE'
952 AND jrr.attribute_code = 'REVERSAL_AR_UNIT'
953 AND jrr.registration_type = 'OTHERS';
954
955 CURSOR c_ap_reversal_num IS
956 SELECT jrr.attribute_value
957 FROM jai_rgm_registrations jrr, jai_rgm_definitions jrd
958 WHERE jrr.regime_id = jrd.regime_id
959 AND jrd.regime_code = 'SERVICE'
960 AND jrr.attribute_code = 'REVERSAL_AP_NUM'
961 AND jrr.registration_type = 'OTHERS';
962
963 CURSOR c_ar_reversal_num IS
964 SELECT jrr.attribute_value
965 FROM jai_rgm_registrations jrr, jai_rgm_definitions jrd
966 WHERE jrr.regime_id = jrd.regime_id
967 AND jrd.regime_code = 'SERVICE'
968 AND jrr.attribute_code = 'REVERSAL_AR_NUM'
969 AND jrr.registration_type = 'OTHERS';
970 lv_reversal_unit VARCHAR2(30);
971 ln_reversal_num NUMBER;
972 ld_grace_date DATE;
973 BEGIN
974 IF pv_source = 'AP' THEN
975 OPEN c_ap_reversal_unit;
976 FETCH c_ap_reversal_unit INTO lv_reversal_unit;
977 CLOSE c_ap_reversal_unit;
978 OPEN c_ap_reversal_num;
979 FETCH c_ap_reversal_num INTO ln_reversal_num;
980 CLOSE c_ap_reversal_num;
981 ELSE
982 OPEN c_ar_reversal_unit;
983 FETCH c_ar_reversal_unit INTO lv_reversal_unit;
984 CLOSE c_ar_reversal_unit;
985 OPEN c_ar_reversal_num;
986 FETCH c_ar_reversal_num INTO ln_reversal_num;
987 CLOSE c_ar_reversal_num;
988 END IF;
989
990 IF lv_reversal_unit = 'DAY' THEN
991 ld_grace_date := pd_gl_date + ln_reversal_num;
992 ELSE
993 SELECT add_months(pd_gl_date,ln_reversal_num) INTO ld_grace_date FROM dual;
994 END IF;--calculate grace date
995 RETURN ld_grace_date;
996 END get_reversal_date;
997
998 FUNCTION get_max_date (pd_date1 DATE, pd_date2 DATE) RETURN DATE IS
999 ld_max_date DATE;
1000 BEGIN
1001 IF pd_date1 IS NOT NULL AND pd_date2 IS NOT NULL THEN
1002 IF pd_date1 > pd_date2 THEN
1003 ld_max_date := pd_date1;
1004 ELSE
1005 ld_max_date := pd_date2;
1006 END IF;
1007 ELSIF pd_date1 IS NULL AND pd_date2 IS NOT NULL THEN
1008 ld_max_date := pd_date2;
1009 ELSIF pd_date2 IS NULL AND pd_date1 IS NOT NULL THEN
1010 ld_max_date := pd_date1;
1011 END IF;
1012 RETURN ld_max_date;
1013 END get_max_date;
1014 END jai_st_reversal_extract_pkg;