1 PACKAGE BODY jai_cmn_rgm_processing_pkg AS
2 /* $Header: jai_cmn_rgm_prc.plb 120.72.12020000.8 2013/03/25 02:57:54 zxin ship $ */
3
4 /* */
5 /*----------------------------------------------------------------------------------------------------------------------------
6 CHANGE HISTORY for FILENAME: jai_rgm_trx_processing_pkg_b.sql
7 S.No dd/mm/yyyy Author and Details
8 ------------------------------------------------------------------------------------------------------------------------------
9 1 26/07/2004 Vijay Shankar for Bug# 4068823, Version:115.0
10
11 Basic Package that Starts Service Tax Processing of both AP and AR transactions based on the inputs
12 provided through request of "India - Service Tax Processing" concurrent
13
14 - PROCESS_BATCH - this procedure is invoked from the request submitted by users through JAISRVTP concurrent
15 This picksup all the Operating Units that are linked to the given Service Tax registration number and
16 invokes AP Processor and AR Processor for their respective processing
17
18 - INSERT_REQUEST_DETAILS : inserts a record into batch header with input details
19 - GET_ITEM_LINE_ID : fetches ITEM Invoice Distribution of TAX invoice distribution incase of AP transactions
20
21 - PROCESS_PAYMENT : records the recovered service tax into repository to the tune of payment amount
22 w.r.t invoice amount and service tax distribution amount. this has all the required functional logic related
23 to Payment reversals and apportioning of Service Tax if multiple payments exists for same invoice etc.
24
25 - PROCESS_PAYMENTS : This is the AP Processor that picks up all the eligible Payments(includes prepayments
26 also) and invokes process_payment for each payment
27
28 2 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.1
29 removed the usage of regime effective_date_from and replace it with regime creation_date as part of VAT Impl.
30 This is required as effective dates are removed Regime setup
31
32 3 12/04/2005 Brathod, for Bug# 4286646, Version 115.2
33 Issue :-
34 Because of change in Valueset from JA_IN_DATE to FND_STANDARD_DATE Concurrent was resulting
35 in error because JA_IN_DATE uses normal date format while FND_STANDARD_DATE uses NLS_DATE format
36 and it is passed as character value.
37 Fix :-
38 Procedure signature modified to convert p_trx_from_date, p_trx_from_date from date to
39 pv_trx_from_date, pv_trx_from_date varchar2. And the varchar2 values are converted back
40 to date fromat using fnd_date.canonical_to_date function.
41
42 4. 14/04/2005 ssumaith - bug# 4284505 - File version 115.3
43
44 Added code to pick the third party taxes from the jai_Rcv_tp_inv_details table in case of
45 third party invoices.
46
47 This is done by adding code for checking - source in the ap_invoices_all table , if it
48 equals to 'RECEIPT' , getting the third party taxes from the jai_Rcv_tp_inv_Details table.
49
50 5. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
51 Changed AP Lookup code from 'RECEIPT' to 'INDIA TAX INVOICE'
52
53
54 6. 08-Jun-2005 Version 116.3 jai_cmn_rgm_prc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
55 as required for CASE COMPLAINCE.
56
57 7. 14-Jun-2005 rchandan for bug#4428980, Version 116.4
58 Modified the object to remove literals from DML statements and CURSORS.
59
60 8. 24-Jan-2006 Bug 4991017. Added by Lakshmi Gopalsami version 120.4
61 Merged the cursors c_ap_accounted_invoices and
62 c_event_distributions because of SLA uptake by base
63 and removed the same.
64 (1) Changed the reference to xla_ae_headers instead
65 of ap_ae_headers_all
66 (2) Also added xla_transaction_entities to get the entity_id
67 and source_int_id_1 so that it can be joined with
68 transaction tables.
69 (3) Discussed with shekhar and found that we should derive by
70 accounting_date and not on the creation_date.
71 (4) Added accounting_event_id in cursor
72 (5) Added local variable lv_entity_code
73
74 DEPENDANCY:
75 -----------
76 IN60106 + 4068823 + 4245089
77
78 8. 29/07/2005 Aiyer - bug# 4523205 - File version 120.2 - (R12 Forward Porting FROM 11.5 bugs 4348774, 4357984)
79 Procedure process has been changed for the bug. Please refer the details in the change history section
80 of the procedure.
81
82 9. 08-Mar-2006 , Bug 4947102, By Aiyer , File Version 120.5
83 Issue:-
84 Cursor c_period_payments has high cost of execution.
85
86 Fix:-
87 Merged the cursors c_period_payments with c_invoice_distributions into c_period_payments so that the IL table in cursor
88 can reduce the overall rows searched by the query.
89 SQL-ID as reported in the repository is 14828450.
90
91 Dependency Due to this bug :-
92 None
93
94 10. 09-JUNE-2007 ,Kunkumar for Bug 6012489 version 12.6
95 Added an if condtion for assignment to local variable
96 If action is accounting, then generated vat invoice number is picked
97 11. 05-SEP-2007 CSahoo for bug#5680459, File Version 120.23
98 R.TST1203:FORWARD PORTING FROM 115 BUG 5645003
99 commented the part where lv_inv_gen_process_flag and lv_inv_gen_process_message
100 were getting assigned as NULL.
101 replaced the party_id by party_site_id as the second parameter in the call to check_reg_dealer procedure.
102
103 12. 04-OCT-2007 CSahoo for bug#6436576, File Version 120.24
104 R.TST1203.XB2.QA:SERVICE TAX REVIEW REPOSITORY SHOW MULTIPLE ACCOUNTING LINES
105 added the following AND condition in the cursor c_period_payments in process_payments procedure.
106 AND apinvp.invoice_id = ainvd.invoice_id
107
108 13. 13-01-2009 vumaasha for bug 7684820
109 INDIA LOCALIZATION: SERVICE TAX RECOVERABLE PORTION IS INCORRECT CALCULATED
110
111 14. 17-May-2009 Bug 7522584
112 Issue : Service Tax entered in foreign currency for AR Invoice is not converted to Functional Currency
113 Fix: Modified the code in the procedure process_payment. Added a cursor c_get_curr_dtls
114 to get the currency details. Then multiplied the conversion rate to the tax_amount
115 to get the tax amount in functional currency i.e., INR
116
117 15. 25-May-2009 Bug 8294236
118 Issue: Svc tax transactions created fx balances on tax accounts after settlement
119 Fix: Modified the code in the procedure process_payment. Added the call to the procedure
120 JAI_RGM_TRX_RECORDING_PKG.exc_gain_loss_accounting.
121
122 16. 01-OCT-2009 JMEENA for bug#8943349
123 Issue: India Service Tax Processing Concurrent not processing Standalone Invoices
124 Fix: Modified cursor c_tax_dist_dtl and c_period_payments of Procedure Process_payments
125 Added code to check if the taxes exists with the standalone invoice. If taxes exists the
126 invoices should be picked for the processing.
127
128 17. 08-Oct-2009 CSahoo for bug#8965721,
129 Issue: TST1212.XB2.QA:SERVICE TAX CREDIT NOT ACCOUNTED FOR GOODS TRANSPORT OPERATORS
130 Fix: Modified the cursors c_ap_accounted_inv_dist and c_period_payments. Added a AND condition.
131
132 18. 21-Dec-2009 Xiao Lv for Bug#7191302 .
133 Issue: Service tax is recovered in excess when prepayment is applied
134 with the checkbox "PREPAYMENT ON INVOICE" checked.
135 Fix: included a cursor c_total_inv_amount, which fetches the sum of
136 total invoice amount eligible for tax recovery.
137 For more details please refer to bug.
138
139 19. 02-Apr-2010 Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement)
140 Issue: currently, procedure 'process' only handles shippable items
141 Fix: logic in procedure 'process' should be modified to process both shippable and
142 non-shippable lines.
143 20 18-Apr-2010 Eric Ma remove the non-ASCII Codes in line 2185
144 21. 28-Apr-2010 Allen Yang for bug 9666476
145 In procedure 'process':
146 1) added 'NULLS FIRST' into Order By clause of sql_stmt_all
147 to ensure shippable items are always processed before non-shippable items.
148 2) removed order_number from Order By clause of sql_stmt_shippable
149 22. 13-May-2010 Allen Yang for bug 9709477
150 In procedure 'process':
151 1). added warning message when flags Same as Excise and Generate Single Invoice are both Y.
152 23. 03-Jun-2010 Allen Yang for bug 9737119
153 Issue: TST1213.XB1.QA.EXECPT DIAGNOSTICS,WARNING MESSAGE SHOULD ALSO BE SEEN IN LOG
154 Fix: In procedure 'process', added logic to put message lv_same_as_excise_conf_warning to Log.
155 24. 03-JUN-2010 vkaranam for bug#9772724
156 Issue:
157 SAME VAT INVOICE GETTING GENERATED FOR DIFFERENT CUSTOMERS
158 fix:
159 changes are done in procedure "process".
160 lv_inv_num_already_generated is set to to false if the parties are different.
161
162 25. Jul 22, 2010 Bug 9854974
163 Issue: CESS and SHE CESS amounts are not shown in Service Tax Pending Recovery Report
164 for Standalone Invoices
165 Fix: Item Line ID in JAI_RGM_TRX_REFS should refer to the Item Line to which Service Tax is attached
166 and not the Line Number of the Service Tax.
167 26. 29-OCT-2010 vkaranam for bug#10085619
168 Issue: India Service Tax Processing Concurrent not processing taxes applied on third party invoice.
169 Fix: Modified the cursor c_tp_inv_details of Procedure Process_payments to include the taxes on thirdparty invoice.
170
171 27. 14-Dec-2010 Eric Ma for Bug#10376849
172 Issue : Tax amount for CM of AP invoice are not populated into tax repository
173 Fix : Change the cursor of c_ap_accounted_inv_dist adding the CM into the invoice_type_lookup_code
174 19. 21-jan-2011 vkaranam for bug#11666653
175 Issue:
176 Service Tax repository not updated for 3rd party invoice applied to
177 Prepayment Invoice
178 Reason :
179 Issue is with cursor c_prepayment_applications ,
180 Invoice id for 3p invoice is fetched using the below:
181 select invoice_id
182 from jai_rcv_tp_invoices
183 where vendor_id
184 in (select vendor_id
185 from po_vendors
186 where trim(vendor_type_lookup_code)
187 -- like 'Service Tax Authorities')
188
189 in R12 vendor_type_lookup_code 'Service Tax Authorities' has been changed to 'INDIA SERVICE TAX AUTHORITY'
190 Hence the STP is not processing prepayment on TP invoice.
191
192 Fix:
193 Modified the cursor c_prepayment_applications.
194 Replaced 'Service Tax Authorities' with 'INDIA SERVICE TAX AUTHORITY'.
195
196 29. 23-Feb-2011 Bug 10630847
197 Description: Prepayment application on Standalone AP invoice is not processed by Service Tax Processing
198 Fix: Included an UNION clause in cursor c_prepayment_applications to get the Standalone Invoice
199 on which Prepayment is applied.\
200 30. 06-may-2011 vkaranam for bug#12360337
201 Issue:ST processor concurrent is not processing the standalone AP invoices with source other than Manual Invoice entry.
202 Fix:
203 commented the ap_invoices_all.source='Manual Invoice entry' in cursors c_prepayment_applications,c_period_payments.
204 30. 24-May-2011 Xiao for POT change, reg bug#12533434.
205 Description: ST Processor should process the the transaction on Cash Basis as well as Accrual Basis.
206 Fix: Compare the accounting date of invoice distribution with POT implementation date.
207 Add one more procedure populate_repository to populate the transactions on accrual basis.
208
209 31. 29-May-2011 Xiao for POT change, reg bug#12533434.
210 Fix: Add cancellation logic in procedure populate_repository.
211 Move the accounting date check out of procedure process_payment.
212
213 32. 16-Aug-2011 Bug 12839287
214 Description: Repository is updated with Functional Currency even though the transaction
215 is in Foreign currency
216 Fix: Populated Tax Amount column to store amount in Transaction Currency instead of
217 functional currency
218 33. 22-Aug-2011 Xiao for POT Phase III changes, reg bug#12895841
219 Fixed: Add logic for showing transactions for AP reversal, AP claim, AR reversal, AR claim.
220 34. 12-Sep-2011 Eric Ma fixed bug12926303 - R1213:POT:PH III - SERVICE TAX REVERSAL RESULTS IN ERROR
221 Fixed: The error message in claim_reversal_trx_lines() is not proper. Fix them.
222 35. 30-Sep-2011 Zhiwei Hou code change for bug#13023443. for Service Tax from Open Interface , if External
223 and GL >= POT , then not populate repo.
224 36. 27-OCT-2011 Chong Lei code change for bug#13259755. for POT AP claim issue.
225 37. 08-NOV-2011 Chong Lei code change for bug#13358557. for reversal service tax amount issur,
226 when these are more than one item line in AP invoice.
227 35. 11-nov-2011 vkaranam for bg#13323182
228 Issue:Service tax is not getting processed on cash basis after the patch(12805386)
229 application.
230 fix:
231 + If Accounting Basis has to be CASH, the cursor that fetches POT date will
232 return NULL
233 + Hence when comparing the POT Date for any logic that needs to go by CASH,
234 we need to compare with NULL also.Added the OR ld_St_accrual_date is null for cash basis transactio
235
236 36. 23-nov-2011 amandali for bug 13242818
237 Issue:Service tax settlement form is based on invoice date rather than accounting date of the invoice.
238 Fix: Changed the transaction date from invoice_date to accounting_date in call to jai_cmn_rgm_recording_pkg.insert_repository_entry
239
240 37. 06-JAN-2012 Qiong for bug 13439861 service tax repository can't be updated normally
241
242 38. 31-Aug-2012 amandali for bug 14507573
243 Description:Service Tax repository not updated for PO matched invoice having multiple distributions
244 Fix:Modified the cursor get_match_item_cur as the po_distribution_id would be null in ap_invoice_lines_all for a PO line having multiple distributions.
245 Added the join to jai_ap_match_inv_taxes.
246 39. 25-Feb-2013 amandali for bug 16246654
247 Description:Reverse charge service tax not hitting repository for third party invoices.
248 Fix:Modified the cursor c_tp_inv_details. Added union condition to pick the reverse charge liability distribution
249 ---------------------------------------------------------------------------------------------------------------------------*/
250
251 CURSOR c_rgm_repository_id(cp_source IN VARCHAR2, cp_source_table_name IN VARCHAR2,
252 cp_source_document_id IN NUMBER, cp_reference_id IN NUMBER) IS
253 SELECT repository_id
254 FROM jai_rgm_trx_records
255 WHERE source = cp_source
256 AND source_table_name = cp_source_table_name
257 AND source_document_id = cp_source_document_id
258 AND reference_id = cp_reference_id;
259
260 CURSOR c_repo_recovered_amt(cp_source IN VARCHAR2, cp_source_table_name IN VARCHAR2,
261 cp_source_document_id IN NUMBER, cp_reference_id IN NUMBER) IS
262 SELECT nvl(credit_amount, debit_amount) amount
263 FROM jai_rgm_trx_records
264 WHERE source = cp_source
265 AND source_table_name = cp_source_table_name
266 AND source_document_id = cp_source_document_id
267 AND reference_id = cp_reference_id;
268
269 CURSOR c_reference(cp_source IN VARCHAR2, cp_invoice_id IN NUMBER, cp_invoice_distribution_id IN NUMBER) IS
270 SELECT reference_id, parent_reference_id, item_line_id, reversal_flag, nvl(recovered_amount, 0) recovered_amount,
271 tax_type, recoverable_amount, nvl(discounted_amount,0) discounted_amount
272 FROM jai_rgm_trx_refs
273 WHERE source = cp_source
274 AND invoice_id = cp_invoice_id
275 AND line_id = cp_invoice_distribution_id;
276
277 CURSOR c_reference_using_id(cp_reference_id IN NUMBER) IS
278 SELECT reference_id, parent_reference_id, item_line_id, reversal_flag, recovered_amount, tax_type,
279 recoverable_amount
280 FROM jai_rgm_trx_refs
281 WHERE reference_id = cp_reference_id;
282
283 CURSOR c_invoice_distribution(cp_invoice_distribution_id IN NUMBER) IS
284 SELECT a.invoice_id, a.invoice_distribution_id,
285 a.invoice_line_number, /* INVOICE LINES UPTAKE */
286 a.distribution_line_number, a.prepay_distribution_id,
287 a.amount, a.reversal_flag, a.parent_reversal_id, a.accounting_event_id, a.posted_flag, a.org_id,
288 a.accounting_date, b.invoice_amount, b.amount_paid, b.cancelled_date, b.invoice_type_lookup_code invoice_type,
289 a.creation_date, a.po_distribution_id
290 FROM ap_invoice_distributions_all a, ap_invoices_all b
291 WHERE a.invoice_id = b.invoice_id
292 AND invoice_distribution_id = cp_invoice_distribution_id;
293
294 CURSOR c_invoice_payment(cp_invoice_payment_id IN NUMBER) IS
295 SELECT a.invoice_payment_id, a.check_id, a.amount, a.payment_base_amount, -- a.reversal_flag, reversal_inv_pmt_id,
296 a.org_id, b.status_lookup_code, b.check_date, b.void_date, b.future_pay_due_date,
297 a.accounting_date, a.reversal_inv_pmt_id, discount_taken,
298 -- Added the following for Bug 8294236
299 b.currency_code, b.exchange_rate, b.exchange_date, b.exchange_rate_type
300 FROM ap_invoice_payments_all a, ap_checks_all b
301 WHERE a.check_id = b.check_id
302 AND a.invoice_payment_id = cp_invoice_payment_id;
303
304 -- code ported from BL12.1.3 by zhiwei.xin on 23-JAN-2013 begin
305 --Added by Qiong for POT reversal begin
306 -------------------------------------------
307 FUNCTION get_reversal_account_name(
308 p_regime_id IN NUMBER,
309 p_organization_type IN VARCHAR2,
310 p_organization_id IN NUMBER,
311 p_location_id IN NUMBER
312 ) RETURN VARCHAR2 IS
313
314
315 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_processing_pkg.get_reversal_account_name';
316
317 CURSOR c_regime_code(cp_regime_id IN NUMBER) IS
318 SELECT regime_code
319 FROM JAI_RGM_DEFINITIONS
320 WHERE regime_id = cp_regime_id;
321
322
323 CURSOR c_orgn_tax_type_account
324 ( cp_regime_id IN NUMBER,
325 cp_organization_type IN VARCHAR2,
326 cp_organization_id IN NUMBER,
327 cp_location_id IN NUMBER
328 ) IS
329 SELECT accnts.attribute_value
330 FROM JAI_RGM_ORG_REGNS_V accnts
331 WHERE accnts.regime_id = cp_regime_id
332 AND accnts.attribute_code = 'SERVICE_TAX_REVERSAL_TO_UPDATE'
333 AND accnts.registration_type = jai_constants.regn_type_others
334 AND accnts.organization_type = cp_organization_type
335 AND accnts.organization_id = cp_organization_id
336 AND (cp_location_id IS NULL OR location_id = cp_location_id);
337
338
339 CURSOR c_regime_id(cp_regime_code IN VARCHAR2) IS
340 SELECT regime_id
341 FROM JAI_RGM_DEFINITIONS
342 WHERE regime_code = cp_regime_code;
343
344
345 lv_reversal_account_name JAI_RGM_REGISTRATIONS.attribute_value%TYPE;
346
347 lv_organization_type VARCHAR2(2);
348 ln_organization_id NUMBER;
349
350 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
351 lv_excise_cess JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
352 ln_regime_id JAI_RGM_DEFINITIONS.regime_id%TYPE;
353
354 BEGIN
355
356 lv_excise_cess := 'EXCISE-CESS';
357
358 IF p_regime_id IS NULL THEN
359 OPEN c_regime_id(jai_constants.service_regime);
360 FETCH c_regime_id INTO ln_regime_id;
361 CLOSE c_regime_id;
362 lv_regime_code := jai_constants.service_regime;
363 ELSE
364 ln_regime_id := p_regime_id;
365 OPEN c_regime_code(ln_regime_id);
366 FETCH c_regime_code INTO lv_regime_code;
367 CLOSE c_regime_code;
368 END IF;
369
370
371 lv_organization_type := p_organization_type;
372 ln_organization_id := p_organization_id;
373 IF lv_regime_code = jai_constants.service_regime
374 AND lv_organization_type = jai_constants.orgn_type_io
375 THEN
376
377 OPEN c_orgn_tax_type_account(ln_regime_id, lv_organization_type, ln_organization_id,
378 p_location_id);
379 FETCH c_orgn_tax_type_account INTO lv_reversal_account_name;
380 CLOSE c_orgn_tax_type_account;
381
382
383 ELSE
384 lv_reversal_account_name := NULL;
385 END IF;
386
387 RETURN lv_reversal_account_name;
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
392 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
393 app_exception.raise_exception;
394
395 END get_reversal_account_name;
396 -------------------------------------------------
397 --Added by Qiong for POT reversal end
398 -- code ported from BL12.1.3 by zhiwei.xin on 23-JAN-2013 end.
399
400 ---------------------------- GET_ITEM_LINE_ID ---------------------------
401 FUNCTION get_item_line_id(
402 p_invoice_id IN NUMBER,
403 p_po_distribution_id IN NUMBER,
404 p_rcv_transaction_id IN NUMBER
405 ) RETURN NUMBER IS
406
407 CURSOR c_parent_distribution_id( p_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE ) IS
408 SELECT invoice_distribution_id
409 FROM ap_invoice_distributions_all
410 WHERE invoice_id = p_invoice_id
411 AND (p_rcv_transaction_id IS NULL OR rcv_transaction_id = p_rcv_transaction_id)
412 AND po_distribution_id = p_po_distribution_id
413 AND line_type_lookup_code = p_line_type_lookup_code--rchandan for bug#4428980
414 AND parent_reversal_id IS NULL; -- CHK
415
416 ln_item_distribution_id AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id%TYPE;
417
418 BEGIN
419
420 OPEN c_parent_distribution_id('ITEM');
421 FETCH c_parent_distribution_id INTO ln_item_distribution_id;
422 CLOSE c_parent_distribution_id;
423
424 RETURN ln_item_distribution_id;
425
426 END get_item_line_id;
427
428 ---------------------------- INSERT_REQUEST_DETAILS ---------------------------
429 PROCEDURE insert_request_details(
430 p_batch_id OUT NOCOPY NUMBER,
431 p_regime_id IN NUMBER,
432 p_rgm_registration_num IN VARCHAR2,
433 p_trx_from_date IN DATE,
434 p_trx_till_date IN DATE
435 ) IS
436
437 ln_conc_request_id FND_CONCURRENT_REQUESTS.request_id%TYPE;
438 ln_conc_request_date FND_CONCURRENT_REQUESTS.request_date%TYPE;
439
440 CURSOR c_request_date(cp_request_id IN NUMBER) IS
441 SELECT request_date
442 FROM fnd_concurrent_requests
443 WHERE request_id = cp_request_id;
444
445 /* Added by Ramananda for bug#4407165 */
446 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_processing_pkg.insert_request_details';
447
448 BEGIN
449
450 ln_conc_request_id := FND_PROFILE.value('CONC_REQUEST_ID');
451
452 OPEN c_request_date(ln_conc_request_id);
453 FETCH c_request_date INTO ln_conc_request_date;
454 CLOSE c_request_date;
455
456 INSERT INTO jai_rgm_conc_requests(
457 batch_id,
458 request_id,
459 request_date,
460 regime_id,
461 rgm_registration_num,
462 trx_from_date,
463 trx_till_date,
464 creation_date,
465 created_by,
466 last_update_date,
467 last_updated_by,
468 last_update_login,
469 program_application_id,
470 program_id,
471 program_login_id
472 ) VALUES (
473 jai_rgm_conc_requests_s.nextval,
474 ln_conc_request_id,
475 ln_conc_request_date,
476 p_regime_id,
477 p_rgm_registration_num,
478 p_trx_from_date,
479 p_trx_till_date,
480 sysdate,
481 FND_GLOBAL.user_id,
482 sysdate,
483 fnd_global.user_id,
484 fnd_global.login_id,
485 fnd_profile.value('PROG_APPL_ID'),
486 fnd_profile.value('CONC_PROGRAM_ID'),
487 fnd_profile.value('CONC_LOGIN_ID')
488 ) RETURNING batch_id INTO p_batch_id;
489
490
491 /* Added by Ramananda for bug#4407165 */
492 EXCEPTION
493 WHEN OTHERS THEN
494 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
495 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
496 app_exception.raise_exception;
497
498 END insert_request_details;
499
500
501
502 --Add by Xiao for POT Phase III reg bug#12895841, begin
503 ----------------------------------------------------------------------------------------------
504 PROCEDURE claim_reversal_trx_lines(
505 pv_source IN VARCHAR2,
506 pn_organization_id IN NUMBER,
507 pn_location_id IN NUMBER,
508 pn_invoice_id IN NUMBER,
509 pn_line_number IN NUMBER,
510 pd_date IN DATE,
511 pn_adjusted_amount IN NUMBER,
512 pv_action IN VARCHAR2,
513 pn_claim_payment_id IN NUMBER DEFAULT 0) IS
514
515 CURSOR get_curr_dtls_cur IS
516 SELECT exchange_date
517 , exchange_rate
518 , exchange_rate_type
519 , invoice_date
520 FROM ap_invoices_all
521 WHERE invoice_id = pn_invoice_id ;
522
523 CURSOR get_total_rec_amt_cur IS
524 SELECT SUM(tax_amt)
525 FROM jai_cmn_document_taxes jcdt,
526 jai_cmn_taxes_all jcta
527 WHERE jcdt.source_doc_id = pn_invoice_id
528 --Add by Chong for bug#13358557 08-NOV-2011, begin
529 -----------------------------------------------------------------------------------------------
530 AND jcdt.source_doc_parent_line_no = pn_line_number
531 -----------------------------------------------------------------------------------------------
532 --Add by Chong for bug#13358557 08-NOV-2011, end
533 AND jcdt.modvat_flag = 'Y'
534 AND jcdt.tax_id = jcta.tax_id
535 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
536
537 CURSOR get_total_arrec_amt_cur IS
538 SELECT SUM(jattl.tax_amount * nvl(jcta.mod_cr_percentage,0)/100) tax_amt
539 FROM jai_ar_trx_tax_lines jattl
540 , jai_ar_trx_lines jatl
541 , jai_cmn_taxes_all jcta
542 WHERE jatl.customer_trx_id = pn_invoice_id
543 AND jattl.link_to_cust_trx_line_id = jatl.customer_trx_line_id
544 AND jattl.tax_id = jcta.tax_id
545 AND nvl(jcta.mod_cr_percentage,0) > 0
546 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
547
548 CURSOR get_dist_line_cur IS
549 SELECT 'STANDALONE' match_type, aida.invoice_distribution_id, jcdt.tax_amt, jcdt.tax_type
550 FROM ap_invoice_distributions_all aida,
551 jai_cmn_document_taxes jcdt
552 WHERE aida.invoice_id = pn_invoice_id
553 AND aida.line_type_lookup_code = 'MISCELLANEOUS'
554 AND jcdt.source_doc_id = pn_invoice_id
555 AND jcdt.source_doc_parent_line_no = pn_line_number
556 AND jcdt.source_doc_line_id = aida.invoice_line_number
557 AND jcdt.modvat_flag = 'Y'
558 AND jcdt.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
559 UNION ALL
560 SELECT 'MATCH' match_type, jamt.invoice_distribution_id, jamt.tax_amount tax_amt,jcta.tax_type
561 FROM jai_ap_match_inv_taxes jamt
562 , jai_cmn_taxes_all jcta
563 WHERE jamt.invoice_id = pn_invoice_id
564 --AND jamt.invoice_line_number <> pn_line_number
565 AND jamt.parent_invoice_line_number = pn_line_number
566 AND jamt.tax_id = jcta.tax_id
567 AND jamt.recoverable_flag = 'Y'
568 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
569
570 CURSOR get_matched_tax_rec_tot_cur IS
571 SELECT sum(jamt.tax_amount)
572 FROM jai_ap_match_inv_taxes jamt
573 , jai_cmn_taxes_all jcta
574 WHERE jamt.invoice_id = pn_invoice_id
575 --Add by Chong for bug#13358557 08-NOV-2011, begin
576 -----------------------------------------------------------------------------------------------
577 AND jamt.parent_invoice_line_number = pn_line_number
578 -----------------------------------------------------------------------------------------------
579 --Add by Chong for bug#13358557 08-NOV-2011, end
580 AND jamt.tax_id=jcta.tax_id
581 AND jamt.recoverable_flag = 'Y'
582 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
583
584 CURSOR get_total_amt_cur IS
585 SELECT SUM(amount)
586 FROM ap_invoice_lines_all
587 WHERE invoice_id = pn_invoice_id
588 AND line_type_lookup_code IN ('ITEM', 'MISCELLANEOUS');
589
590 CURSOR get_total_ar_amt_cur IS
591 SELECT SUM(total_amount)
592 FROM jai_ar_trx_lines
593 WHERE customer_trx_id = pn_invoice_id;
594
595 CURSOR get_tax_type_ar_cur IS
596 SELECT jcta.tax_type, jattl.tax_amount * nvl(jcta.mod_cr_percentage,0)/100 tax_amt,
597 jattl.customer_trx_line_id
598 FROM jai_ar_trx_tax_lines jattl
599 , ra_customer_trx_lines_all trxl
600 , jai_cmn_taxes_all jcta
601 WHERE jattl.link_to_cust_trx_line_id = pn_line_number
602 AND trxl.customer_trx_id = pn_invoice_id
603 AND trxl.customer_trx_line_id = pn_line_number
604 AND jattl.tax_id = jcta.tax_id
605 AND nvl(jcta.mod_cr_percentage,0) > 0
606 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
607
608 CURSOR get_currency_cur IS
609 SELECT invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate, trx_date
610 FROM ra_customer_trx_all
611 WHERE customer_trx_id = pn_invoice_id;
612
613 CURSOR get_regime_cur IS
614 SELECT regime_id
615 FROM jai_rgm_definitions
616 WHERE regime_code = jai_constants.service_regime;
617
618 CURSOR c_ar_ref_records(cp_source_document_id NUMBER, cp_source_ar VARCHAR2) IS
619 SELECT *
620 FROM jai_rgm_trx_refs rgtf
621 WHERE line_id = cp_source_document_id
622 AND party_type = 'C'
623 AND SOURCE = cp_source_ar;
624
625 ln_tax_amount NUMBER;
626 ln_rec_total_tax_amount NUMBER;
627
628 lv_process_message VARCHAR2(200);
629 ln_regime_id NUMBER;
630 lv_regime_code VARCHAR2(30) := jai_constants.service_regime;
631 lv_tax_type VARCHAR2(30);
632 ln_organization_id NUMBER := pn_organization_id;
633 ln_location_id NUMBER := pn_location_id;
634 ln_charge_account_id NUMBER;
635 ld_transaction_date DATE;
636 ld_gl_date DATE := pd_date;
637 ld_accrual_date DATE;
638 lv_called_from VARCHAR2(80);
639 lv_currency_code VARCHAR2(30) := 'INR';
640 ld_curr_conv_date DATE;
641 lv_curr_conv_type VARCHAR2(30);
642 ln_curr_conv_rate NUMBER;
643 ln_entered_amt NUMBER;
644 ln_accounted_amt NUMBER;
645
646
647 ln_invoice_dist_id NUMBER;
648 lv_account_name VARCHAR2(30);
649 lv_source_trx_type VARCHAR2(30);
650
651 lr_ref_records jai_rgm_trx_refs%ROWTYPE;
652 lv_match_type VARCHAR2(20);
653 ln_total_amount NUMBER;
654 ln_total_rec_amount NUMBER;
655
656 lv_source_table jai_rgm_trx_records.source_table_name%TYPE;
657 lv_repository_name VARCHAR2(80);
658
659
660 BEGIN
661
662 IF pv_action = 'REVERSAL' THEN
663 lv_source_trx_type := 'REVERSAL_ACCOUNTING';
664 lv_called_from := 'REVERSAL_ACCOUNTING' ;
665 lv_source_table := 'AP_INVOICE_DISTRIBUTIONS_ALL';
666 lv_repository_name := 'invoice_distribution_id';
667 ELSE --'CLAIM'
668 lv_source_trx_type := 'CLAIM_ACCOUNTING';
669 lv_called_from := 'CLAIM_ACCOUNTING' ;
670 lv_source_table := 'AP_INVOICE_PAYMENTS_ALL';
671 lv_repository_name := 'invoice_payment_id';
672 END IF;
673
674 OPEN get_regime_cur;
675 FETCH get_regime_cur INTO ln_regime_id;
676 CLOSE get_regime_cur;
677
678 IF pv_source = 'AP' THEN
679
680 OPEN get_curr_dtls_cur;
681 FETCH get_curr_dtls_cur INTO ld_curr_conv_date, ln_curr_conv_rate, lv_curr_conv_type, ld_transaction_date;
682 CLOSE get_curr_dtls_cur;
683
684 FOR lr_dist_line IN get_dist_line_cur
685 LOOP
686 --Added by Qiong for reverse charge bug#16001407, begin
687 IF jai_cmn_reverse_charge_pkg.is_reverse_charge_dist(lr_dist_line.invoice_distribution_id)= 'Y' THEN
688 null;
689 else
690 --Added by Qiong for reverse charge bug#16001407 end
691
692 IF lr_dist_line.match_type = 'MATCH' THEN
693 OPEN get_matched_tax_rec_tot_cur;
694 FETCH get_matched_tax_rec_tot_cur INTO ln_total_rec_amount;
695 CLOSE get_matched_tax_rec_tot_cur;
696 ELSE
697 OPEN get_total_rec_amt_cur;
698 FETCH get_total_rec_amt_cur INTO ln_total_rec_amount;
699 CLOSE get_total_rec_amt_cur;
700 END IF;
701
702 IF pv_action = 'REVERSAL' THEN--if reversal, assign sum of recoverable tax amount
703 lv_account_name := jai_constants.recovery_interim;
704 ln_total_amount := nvl(ln_total_rec_amount, 0);
705 ln_invoice_dist_id := lr_dist_line.invoice_distribution_id;
706 ELSE --if claim, get sum of all tax amount
707 lv_account_name := jai_constants.recovery;
708 OPEN get_total_amt_cur;
709 FETCH get_total_amt_cur INTO ln_total_amount;
710 CLOSE get_total_amt_cur;
711 ln_invoice_dist_id := pn_claim_payment_id;
712 END IF;
713
714 lv_tax_type := lr_dist_line.tax_type;
715 ln_tax_amount := lr_dist_line.tax_amt;
716 ln_entered_amt := round(pn_adjusted_amount * ln_tax_amount/ln_total_amount, jai_constants.service_rgm_rnd_factor);
717 ln_accounted_amt := ln_entered_amt * nvl(ln_curr_conv_rate, 1);
718
719
720 ln_charge_account_id := jai_cmn_rgm_recording_pkg.get_account(
721 p_regime_id => ln_regime_id
722 , p_organization_type => jai_constants.service_tax_orgn_type
723 , p_organization_id => ln_organization_id
724 , p_location_id => ln_location_id
725 , p_tax_type => lv_tax_type
726 , p_account_name => lv_account_name);
727
728 IF ln_charge_account_id IS NULL THEN
729 lv_process_message := 'Interim Recovery Account/Recovery Account is not defined at the regime registration.';
730 RAISE_APPLICATION_ERROR(-20007, 'Error - package jai_cmn_rgm_processing_pkg.claim_reversal_trx_lines: '
731 ||lv_process_message);
732 END IF;
733
734 IF nvl(ln_entered_amt, 0) <> 0 THEN
735 jai_cmn_rgm_recording_pkg.post_accounting(
736 p_regime_code => lv_regime_code,
737 p_tax_type => lv_tax_type,
738 p_organization_type => jai_constants.service_tax_orgn_type,
739 p_organization_id => ln_organization_id,
740 p_source => jai_constants.source_ap,
741 p_source_trx_type => lv_source_trx_type,
742 p_source_table_name => lv_source_table,
743 p_source_document_id => ln_invoice_dist_id,
744 p_code_combination_id => ln_charge_account_id,
745 p_entered_cr => NULL,
746 p_entered_dr => ln_entered_amt,
747 p_accounted_cr => NULL,
748 p_accounted_dr => ln_accounted_amt,
749 p_accounting_date => ld_gl_date,
750 p_transaction_date => ld_transaction_date,
751 p_calling_object => lv_called_from,
752 p_repository_name => lv_repository_name,
753 p_repository_id => NULL,
754 p_reference_name => lv_source_table,
755 p_reference_id => ln_invoice_dist_id,
756 p_currency_code => lv_currency_code,
757 p_curr_conv_date => ld_curr_conv_date,
758 p_curr_conv_type => lv_curr_conv_type,
759 p_curr_conv_rate => ln_curr_conv_rate
760 );
761
762 IF pv_action = 'REVERSAL' THEN
763 -- code ported from BL12.1.3 by zhiwei.xin on 23-JAN-2013 begin
764 --Changed by Qiong for POT reversal begin
765 ------------------------------------------------------------------
766 lv_account_name := jai_cmn_rgm_processing_pkg.get_reversal_account_name(
767 p_regime_id => ln_regime_id
768 , p_organization_type => jai_constants.service_tax_orgn_type
769 , p_organization_id => ln_organization_id
770 , p_location_id => ln_location_id);
771 --lv_account_name := jai_constants.recovery;
772 -------------------------------------------------------------------
773 --Changed by Qiong for POT reversal End
774 -- code ported from BL12.1.3 by zhiwei.xin on 23-JAN-2013 end.
775 ELSE -- 'CLAIM'
776 lv_account_name := jai_constants.recovery_interim;
777 END IF;
778
779 ln_charge_account_id := jai_cmn_rgm_recording_pkg.get_account(
780 p_regime_id => ln_regime_id
781 , p_organization_type => jai_constants.service_tax_orgn_type
782 , p_organization_id => ln_organization_id
783 , p_location_id => ln_location_id
784 , p_tax_type => lv_tax_type
785 , p_account_name => lv_account_name);
786
787 IF ln_charge_account_id IS NULL THEN
788 lv_process_message := 'Interim Recovery Account/Recovery Account is not defined at the regime registration.';
789 RAISE_APPLICATION_ERROR(-20007, 'Error - package jai_cmn_rgm_processing_pkg.claim_reversal_trx_lines: '
790 ||lv_process_message);
791 END IF;
792 jai_cmn_rgm_recording_pkg.post_accounting(
793 p_regime_code => lv_regime_code,
794 p_tax_type => lv_tax_type,
795 p_organization_type => jai_constants.service_tax_orgn_type,
796 p_organization_id => ln_organization_id,
797 p_source => jai_constants.source_ap,
798 p_source_trx_type => lv_source_trx_type,
799 p_source_table_name => lv_source_table,
800 p_source_document_id => ln_invoice_dist_id,
801 p_code_combination_id => ln_charge_account_id,
802 p_entered_cr => ln_entered_amt,
803 p_entered_dr => NULL,
804 p_accounted_cr => ln_accounted_amt,
805 p_accounted_dr => NULL,
806 p_accounting_date => ld_gl_date,
807 p_transaction_date => ld_transaction_date,
808 p_calling_object => lv_called_from,
809 p_repository_name => lv_repository_name,
810 p_repository_id => NULL,
811 p_reference_name => lv_source_table,
812 p_reference_id => ln_invoice_dist_id,
813 p_currency_code => lv_currency_code,
814 p_curr_conv_date => ld_curr_conv_date,
815 p_curr_conv_type => lv_curr_conv_type,
816 p_curr_conv_rate => ln_curr_conv_rate
817 );
818 END IF;
819 END IF;--Added by Qiong for reverse charge bug#16001407
820
821
822 END LOOP;
823 ELSE -- 'AR'
824
825 FOR service_tax_rec IN get_tax_type_ar_cur
826 LOOP
827
828 OPEN c_ar_ref_records(service_tax_rec.customer_trx_line_id, 'AR');
829 FETCH c_ar_ref_records INTO lr_ref_records;
830 CLOSE c_ar_ref_records;
831
832 OPEN get_currency_cur;
833 FETCH get_currency_cur INTO lv_currency_code, lv_curr_conv_type,
834 ld_curr_conv_date, ln_curr_conv_rate,
835 ld_transaction_date;
836 CLOSE get_currency_cur;
837
838 lv_tax_type := service_tax_rec.tax_type;
839 ln_invoice_dist_id := service_tax_rec.customer_trx_line_id;
840 ln_tax_amount := service_tax_rec.tax_amt;
841
842 IF pv_action = 'REVERSAL' THEN --if reversal, assign sum of recoverable tax amount
843 lv_account_name := jai_constants.liability;
844 OPEN get_total_arrec_amt_cur;
845 FETCH get_total_arrec_amt_cur INTO ln_total_amount;
846 CLOSE get_total_arrec_amt_cur;
847
848 ELSE --if claim, get sum of all tax amount
849 lv_account_name := jai_constants.reversal;
850 OPEN get_total_ar_amt_cur;
851 FETCH get_total_ar_amt_cur INTO ln_total_amount;
852 CLOSE get_total_ar_amt_cur;
853 END IF;
854
855
856 ln_entered_amt := round(pn_adjusted_amount * ln_tax_amount/ln_total_amount, jai_constants.service_rgm_rnd_factor);
857 ln_accounted_amt := ln_entered_amt * nvl(lr_ref_records.curr_conv_rate, 1);
858
859 ln_charge_account_id := jai_cmn_rgm_recording_pkg.get_account(
860 p_regime_id => ln_regime_id
861 , p_organization_type => jai_constants.service_tax_orgn_type
862 , p_organization_id => ln_organization_id
863 , p_location_id => ln_location_id
864 , p_tax_type => lv_tax_type
865 , p_account_name => lv_account_name);
866
867 IF ln_charge_account_id IS NULL THEN
868 lv_process_message := 'Reversal Account /Liability Account is not defined at the regime registration.';
869 RAISE_APPLICATION_ERROR(-20007, 'Error - package jai_cmn_rgm_processing_pkg.claim_reversal_trx_lines: '
870 ||lv_process_message);
871 END IF;
872 jai_cmn_rgm_recording_pkg.post_accounting(
873 p_regime_code => lv_regime_code,
874 p_tax_type => lv_tax_type,
875 p_organization_type => jai_constants.service_tax_orgn_type,
876 p_organization_id => ln_organization_id,
877 p_source => jai_constants.source_ar,
878 p_source_trx_type => lv_source_trx_type,
879 p_source_table_name => 'CUSTOMER_TRX_LINE_ALL',
880 p_source_document_id => ln_invoice_dist_id,
881 p_code_combination_id => ln_charge_account_id,
882 p_entered_cr => NULL,
883 p_entered_dr => ln_entered_amt,
884 p_accounted_cr => NULL,
885 p_accounted_dr => ln_accounted_amt,
886 p_accounting_date => ld_gl_date,
887 p_transaction_date => ld_transaction_date,
888 p_calling_object => lv_called_from,
889 p_repository_name => 'customer_trx_line_id',
890 p_repository_id => NULL,
891 p_reference_name => 'CUSTOMER_TRX_LINE_ALL',
892 p_reference_id => ln_invoice_dist_id,
893 p_currency_code => nvl(lr_ref_records.trx_currency, lv_currency_code),
894 p_curr_conv_date => nvl(lr_ref_records.curr_conv_date, ld_curr_conv_date),
895 p_curr_conv_type => lv_curr_conv_type,
896 p_curr_conv_rate => nvl(lr_ref_records.curr_conv_rate, ln_curr_conv_rate)
897 );
898
899 IF pv_action = 'REVERSAL' THEN
900 lv_account_name := jai_constants.reversal;
901 ELSE -- 'CLAIM'
902 lv_account_name := jai_constants.liability;
903 END IF;
904
905 ln_charge_account_id := jai_cmn_rgm_recording_pkg.get_account(
906 p_regime_id => ln_regime_id
907 , p_organization_type => jai_constants.service_tax_orgn_type
908 , p_organization_id => ln_organization_id
909 , p_location_id => ln_location_id
910 , p_tax_type => lv_tax_type
911 , p_account_name => lv_account_name);
912
913 IF ln_charge_account_id IS NULL THEN
914 lv_process_message := 'Reversal Account /Liability Account is not defined at the regime registration.';
915 RAISE_APPLICATION_ERROR(-20007, 'Error - package jai_cmn_rgm_processing_pkg.claim_reversal_trx_lines: '
916 ||lv_process_message);
917 END IF;
918 jai_cmn_rgm_recording_pkg.post_accounting(
919 p_regime_code => lv_regime_code,
920 p_tax_type => lv_tax_type,
921 p_organization_type => jai_constants.service_tax_orgn_type,
922 p_organization_id => ln_organization_id,
923 p_source => jai_constants.source_ar,
924 p_source_trx_type => lv_source_trx_type,
925 p_source_table_name => 'CUSTOMER_TRX_LINE_ALL',
926 p_source_document_id => ln_invoice_dist_id,
927 p_code_combination_id => ln_charge_account_id,
928 p_entered_cr => ln_entered_amt,
929 p_entered_dr => NULL,
930 p_accounted_cr => ln_accounted_amt,
931 p_accounted_dr => NULL,
932 p_accounting_date => ld_gl_date,
933 p_transaction_date => ld_transaction_date,
934 p_calling_object => lv_called_from,
935 p_repository_name => 'customer_trx_line_id',
936 p_repository_id => NULL,
937 p_reference_name => 'CUSTOMER_TRX_LINE_ALL',
938 p_currency_code => nvl(lr_ref_records.trx_currency, lv_currency_code),
939 p_curr_conv_date => nvl(lr_ref_records.curr_conv_date, ld_curr_conv_date),
940 p_curr_conv_type => lv_curr_conv_type,
941 p_curr_conv_rate => nvl(lr_ref_records.curr_conv_rate, ln_curr_conv_rate)
942 );
943 END LOOP;
944 END IF;
945
946
947 END claim_reversal_trx_lines;
948
949 PROCEDURE process_claim(
950 pn_regime_id IN NUMBER,
951 pn_organization_type IN VARCHAR2,
952 pd_from_date IN DATE,
953 pd_to_date IN DATE,
954 pn_organization_id IN NUMBER) IS
955
956 CURSOR get_claim_trxn_cur IS
957 SELECT jsir.*
958 FROM jai_st_invoice_reversal jsir
959 WHERE jsir.source = 'AP'
960 --Add by Chong for bug#13259755, start
961 AND jsir.organization_id = pn_organization_id
962 --Add by Chong for bug#13259755, end
963 AND jsir.tax_to_be_adjusted > 0;
964
965 CURSOR get_claim_amt_cur(pn_invoice_id IN NUMBER, pn_max_payment_id IN NUMBER) IS
966 SELECT nvl(aipa.amount, 0) claim_amt, aipa.invoice_payment_id, aipa.accounting_date
967 FROM ap_invoice_payments_all aipa
968 WHERE aipa.invoice_id = pn_invoice_id
969 AND aipa.invoice_payment_id > nvl(pn_max_payment_id, 0)
970 AND aipa.accounting_date <= pd_to_date
971 AND NOT EXISTS (SELECT 1
972 FROM jai_rgm_trx_records jrtr
973 WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
974 AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
975 AND jrtr.source = 'AP_CLAIM'
976 AND jrtr.source_document_id = aipa.invoice_payment_id)
977 AND EXISTS (SELECT 1
978 FROM ap_invoice_distributions_all aida
979 WHERE aida.invoice_id = pn_invoice_id
980 AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aipa.accounting_date,
981 aida.accounting_date, 'AP') = 'Y')
982 UNION ALL
983 SELECT abs(nvl(aila.amount, 0)) claim_amt, aipa.invoice_payment_id, aila.accounting_date
984 FROM ap_invoice_lines_all aila,
985 ap_invoice_payments_all aipa
986 WHERE aila.invoice_id = pn_invoice_id
987 AND aila.line_type_lookup_code = 'PREPAY'
988 AND aila.prepay_invoice_id = aipa.invoice_id
989 AND (aipa.invoice_payment_id > nvl(pn_max_payment_id, 0)
990 OR (aipa.invoice_payment_id < nvl(pn_max_payment_id, 0)
991 AND NOT EXISTS (SELECT 1
992 FROM jai_rgm_trx_records jrtr
993 WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
994 AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
995 AND jrtr.source = 'AP_CLAIM'
996 AND jrtr.source_document_id = aipa.invoice_payment_id)))
997 AND aila.accounting_date <= pd_to_date
998 AND nvl(aila.amount, 0) <> 0
999 AND NOT EXISTS (SELECT 1
1000 FROM jai_rgm_trx_records jrtr
1001 WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
1002 AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
1003 AND jrtr.source = 'AP_CLAIM'
1004 AND jrtr.source_document_id = aipa.invoice_payment_id)
1005 AND EXISTS (SELECT 1
1006 FROM ap_invoice_distributions_all aida
1007 WHERE aida.invoice_id = pn_invoice_id
1008 AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aila.accounting_date,
1009 aida.accounting_date, 'AP') = 'Y')
1010 ORDER BY 2;
1011
1012 ln_max_payment_id NUMBER;
1013
1014 BEGIN
1015
1016 FOR reversal_trxn_rec IN get_claim_trxn_cur
1017 LOOP
1018
1019 ln_max_payment_id := reversal_trxn_rec.max_claim_payment_id;
1020
1021 FOR rec_claim_entry IN get_claim_amt_cur(reversal_trxn_rec.invoice_id, reversal_trxn_rec.max_claim_payment_id)
1022 LOOP
1023 IF nvl(rec_claim_entry.claim_amt, 0) > 0 THEN
1024 claim_reversal_trx_lines(
1025 pv_source => 'AP',
1026 pn_organization_id => pn_organization_id,
1027 pn_location_id => reversal_trxn_rec.location_id,
1028 pn_invoice_id => reversal_trxn_rec.invoice_id,
1029 pn_line_number => reversal_trxn_rec.line_num,
1030 pd_date => rec_claim_entry.accounting_date,
1031 pn_adjusted_amount => rec_claim_entry.claim_amt,
1032 pv_action => 'CLAIM',
1033 pn_claim_payment_id => rec_claim_entry.invoice_payment_id);
1034
1035 END IF;
1036
1037 IF rec_claim_entry.invoice_payment_id > nvl(ln_max_payment_id, 0) THEN
1038 ln_max_payment_id := rec_claim_entry.invoice_payment_id;
1039 END IF;
1040
1041 END LOOP;
1042
1043 IF nvl(ln_max_payment_id, 0) > nvl(reversal_trxn_rec.max_claim_payment_id, 0) THEN
1044 UPDATE jai_st_invoice_reversal
1045 SET max_claim_payment_id = ln_max_payment_id
1046 WHERE invoice_id = reversal_trxn_rec.invoice_id
1047 AND line_num = reversal_trxn_rec.line_num
1048 AND SOURCE = 'AP';
1049 END IF;
1050
1051 END LOOP;
1052
1053 END process_claim;
1054 ----------------------------------------------------------------------------------------------
1055 --Add by Xiao for POT Phase III reg bug#12895841, end
1056
1057
1058 ---------------------------- PROCESS_PAYMENT ---------------------------
1059 PROCEDURE process_payment(
1060 p_batch_id IN NUMBER,
1061 p_regime_id IN NUMBER,
1062 p_org_id IN NUMBER,
1063 p_source IN VARCHAR2,
1064 p_payment_table_name IN VARCHAR2,
1065 p_payment_document_id IN NUMBER,
1066 p_invoice_id IN NUMBER,
1067 p_inv_dist_id IN NUMBER,
1068 p_inv_accounting_chk_done IN VARCHAR2,
1069 p_process_flag OUT NOCOPY VARCHAR2,
1070 p_process_message OUT NOCOPY VARCHAR2
1071 ) IS
1072 /*Bug 5879769 bduvarag start*/
1073 /* CURSOR c_inv_organization_id(cp_po_distribution_id IN NUMBER) IS
1074 SELECT b.ship_to_organization_id
1075 FROM po_distributions_all a, po_line_locations_all b
1076 WHERE a.line_location_id = b.line_location_id
1077 AND a.po_distribution_id = cp_po_distribution_id;*/
1078
1079 -- Added for bug#7191302 by Xiao, begin.
1080 /* The cursor c_total_inv_amount is to fetch the total invoice amount without considering
1081 any prepayment applications. Refer to bug for more details*/
1082 CURSOR c_total_inv_amount(cp_invoice_id NUMBER) IS
1083 select sum(amount) from ap_invoice_distributions_all a where invoice_id=cp_invoice_id
1084 and prepay_distribution_id is null;
1085
1086 -- Added for bug#7191302 by Xiao, end.
1087 -- Added the following cursor for Bug 7522584
1088 cursor c_get_curr_dtls (cp_invoice_id NUMBER)
1089 IS
1090 SELECT payment_currency_code,
1091 exchange_rate,
1092 exchange_date,
1093 exchange_rate_type
1094 FROM ap_invoices_all
1095 WHERE invoice_id = cp_invoice_id ;
1096
1097 lv_service_type_code jai_po_line_locations.service_type_code%TYPE;
1098 ln_organization_id NUMBER;
1099 ln_location_id NUMBER;
1100 lv_process_flag VARCHAR2(15);
1101 lv_process_message VARCHAR2(4000);
1102 /*Bug 5879769 bduvarag End*/
1103
1104 r_ref c_reference%ROWTYPE;
1105 r_parent_ref c_reference%ROWTYPE;
1106 r_dist c_invoice_distribution%ROWTYPE;
1107
1108 r_prepayment c_invoice_distribution%ROWTYPE;
1109 r_payment c_invoice_payment%ROWTYPE;
1110
1111 ln_inv_organization_id NUMBER(15);
1112 ln_rgm_reposotory_id NUMBER;
1113 -- ln_payment_amount NUMBER;
1114
1115 lv_src_trx_type VARCHAR2(30);
1116 ln_recovered_amount NUMBER;
1117 ln_parent_recovered_amt NUMBER;
1118
1119 ln_payment_amount NUMBER;
1120 ld_transaction_date JAI_RGM_TRX_RECORDS.transaction_date%TYPE;
1121 ld_accounting_date DATE;
1122 ln_validate_amount NUMBER;
1123 ln_discounted_amount NUMBER := 0;
1124 ln_payment_discount NUMBER := 0;
1125
1126 ln_diff_amount NUMBER;
1127
1128 lv_codepath VARCHAR2(1996); -- := ''; File.Sql.35 by Brathod
1129 lv_total_inv_amount NUMBER; -- added for bug#7191302 by Xiao
1130 lv_called_from VARCHAR2(100); --rchandan for bug#4428980
1131 rec_get_curr_dtls c_get_curr_dtls%rowtype; -- Added for Bug 7522584
1132 ln_func_tax_amount NUMBER;
1133 ln_exc_gain_loss_amt NUMBER; -- Added for Bug 8294236
1134 ln_tot_tax_amt NUMBER; -- Added for Bug 8294236
1135
1136
1137 BEGIN
1138 g_debug := 'Y';
1139 lv_codepath := jai_general_pkg.plot_codepath(1,lv_codepath, 'PROCESS_PAYMENT', 'START');
1140
1141 -- added for bug#7191302 by Xiao, begin
1142 open c_total_inv_amount(p_invoice_id);
1143 fetch c_total_inv_amount into lv_total_inv_amount;
1144 close c_total_inv_amount;
1145 -- added for bug#7191302 by Xiao, end.
1146 -- Bug 7522584 Start
1147 OPEN c_get_curr_dtls(p_invoice_id);
1148 FETCH c_get_curr_dtls INTO rec_get_curr_dtls;
1149 CLOSE c_get_curr_dtls;
1150 -- Bug 7522584 End
1151
1152 -- Accounting check for the invoice_distribution, whether it is accounted or not
1153 IF p_inv_accounting_chk_done = jai_constants.no THEN
1154 OPEN c_invoice_distribution(p_inv_dist_id);
1155 FETCH c_invoice_distribution INTO r_dist;
1156 CLOSE c_invoice_distribution;
1157
1158 r_dist.invoice_amount:=lv_total_inv_amount; -- added for bug#7191302 by Xiao
1159 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
1160
1161 -- Following condition is true only if Invoice Distribution Accounting did not happen
1162 IF r_dist.posted_flag IS NULL OR r_dist.posted_flag <> 'Y' THEN
1163 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
1164 IF g_debug='Y' THEN
1165 fnd_file.put_line(fnd_file.log,'AccntChkFail. InvId,LineNum,DisNum:'||r_dist.invoice_id||','
1166 ||r_dist.invoice_line_number||','||r_dist.distribution_line_number);
1167 END IF;
1168 p_process_flag := jai_constants.not_accounted;
1169 --p_process_message := 'Invoice is not accounted';
1170 RETURN;
1171 END IF;
1172
1173 END IF;
1174
1175 OPEN c_reference(p_source, p_invoice_id, p_inv_dist_id);
1176 FETCH c_reference INTO r_ref;
1177 CLOSE c_reference;
1178
1179 -- If the following if condition is satisfied, then it means there is no REFERENCE entry and thus no RECEOVERY should happen
1180 IF r_ref.reference_id IS NULL THEN
1181 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
1182 RETURN;
1183 -- if the following is satisfied then it means this is a reversal of a parent line which is processed and hence should return back
1184 ELSIF r_ref.reversal_flag = 'Y' THEN
1185 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
1186 p_process_flag := jai_constants.already_processed;
1187 RETURN;
1188 END IF;
1189
1190 IF g_debug='Y' THEN fnd_file.put_line(fnd_file.log,'ProPay.r_ref:'||r_ref.reference_id||',taxty:'||r_ref.tax_type); END IF;
1191
1192 OPEN c_rgm_repository_id(p_source, p_payment_table_name, p_payment_document_id, r_ref.reference_id);
1193 FETCH c_rgm_repository_id INTO ln_rgm_reposotory_id;
1194 CLOSE c_rgm_repository_id;
1195
1196 -- if the following is satisfied, then it means the payment against the invoice is already processed
1197 IF ln_rgm_reposotory_id IS NOT NULL THEN
1198 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath);
1199 p_process_flag := jai_constants.already_processed;
1200 RETURN;
1201 END IF;
1202
1203 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath);
1204 -- following will be true only in case Accounting Check for Invoice distribution is not done in calling procedure
1205 IF r_dist.invoice_distribution_id IS NULL THEN
1206 OPEN c_invoice_distribution(p_inv_dist_id);
1207 FETCH c_invoice_distribution INTO r_dist;
1208 CLOSE c_invoice_distribution;
1209 END IF;
1210
1211 IF p_payment_table_name = jai_constants.ap_payments THEN
1212
1213 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
1214 OPEN c_invoice_payment(p_payment_document_id);
1215 FETCH c_invoice_payment INTO r_payment;
1216 CLOSE c_invoice_payment;
1217
1218 IF r_payment.future_pay_due_date IS NOT NULL AND r_payment.future_pay_due_date > trunc(sysdate) THEN
1219 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
1220 p_process_flag := 'FP';
1221 p_process_message := 'Future payment which is not yet matured';
1222 RETURN;
1223 END IF;
1224
1225 ln_payment_amount := r_payment.amount;
1226 ln_payment_discount := r_payment.discount_taken;
1227
1228 ld_accounting_date := r_payment.accounting_date;
1229
1230 -- To Derive Src Trx Type and Transaction Date for Normal Payment
1231 IF r_payment.amount > 0 THEN
1232 IF r_payment.future_pay_due_date IS NOT NULL THEN
1233 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
1234 lv_src_trx_type := jai_constants.future_payment;
1235 ld_transaction_date := r_payment.future_pay_due_date;
1236 ELSE
1237 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath);
1238 lv_src_trx_type := jai_constants.payment;
1239 ld_transaction_date := r_payment.check_date;
1240 END IF;
1241
1242 -- Void Case
1243 ELSE
1244 IF r_payment.void_date IS NOT NULL THEN
1245 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
1246 lv_src_trx_type := jai_constants.payment_voided;
1247 ld_transaction_date := r_payment.void_date;
1248 ELSE
1249 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath);
1250 lv_src_trx_type := jai_constants.payment_reversal;
1251 ld_transaction_date := r_payment.check_date;
1252 END IF;
1253 END IF;
1254
1255 ELSIF p_payment_table_name = jai_constants.ap_prepayments THEN
1256
1257 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
1258 OPEN c_invoice_distribution(p_payment_document_id);
1259 FETCH c_invoice_distribution INTO r_prepayment;
1260 CLOSE c_invoice_distribution;
1261
1262 -- Prepayment Application is always a -ve line in invoice distributions, so to make it as +ve we need to negate it
1263 ln_payment_amount := -r_prepayment.amount;
1264
1265 ld_accounting_date := r_prepayment.accounting_date;
1266 ld_transaction_date := trunc(r_prepayment.creation_date);
1267
1268 -- if the following condition is satisfied, then it means a prepayment unapplication onto invoice
1269 IF r_prepayment.parent_reversal_id IS NOT NULL THEN
1270 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath);
1271 lv_src_trx_type := jai_constants.prepay_unapplication;
1272 ELSE
1273 lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath);
1274 lv_src_trx_type := jai_constants.prepay_application;
1275 END IF;
1276
1277 END IF;
1278
1279 -- following condition is satisfied if the invoice is cancelled and line has been already claimed that needs to be reversed
1280 --IF r_dist.cancelled_date IS NOT NULL THEN
1281
1282 -- Following condition is satisfied if the distribution tax line is reversal of a parent distribution tax line
1283 IF r_dist.reversal_flag = 'Y' AND r_dist.parent_reversal_id IS NOT NULL THEN
1284
1285 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
1286 OPEN c_reference(p_source, p_invoice_id, r_dist.parent_reversal_id);
1287 FETCH c_reference INTO r_parent_ref;
1288 CLOSE c_reference;
1289
1290 UPDATE jai_rgm_trx_refs
1291 SET reversal_flag = 'Y',
1292 last_update_date = sysdate
1293 WHERE source = p_source
1294 AND invoice_id = p_invoice_id
1295 AND line_id in (p_inv_dist_id, r_dist.parent_reversal_id);
1296
1297 ln_recovered_amount := -r_parent_ref.recovered_amount;
1298 ln_discounted_amount := -r_parent_ref.discounted_amount;
1299
1300 -- following elsif is added to take care of void scenarios, where in the recovered amt againt the main payment is reversed
1301 ELSIF lv_src_trx_type = jai_constants.payment_voided THEN
1302
1303 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
1304 OPEN c_repo_recovered_amt(p_source, p_payment_table_name, r_payment.reversal_inv_pmt_id, r_ref.reference_id);
1305 FETCH c_repo_recovered_amt INTO ln_parent_recovered_amt;
1306 CLOSE c_repo_recovered_amt;
1307
1308 ln_recovered_amount := -ln_parent_recovered_amt;
1309
1310 if r_payment.amount = 0 THEN
1311 r_payment.amount :=1;
1312 end if;
1313
1314 ln_discounted_amount := -ln_parent_recovered_amt * ( nvl(r_payment.discount_taken,0)/nvl(r_payment.amount,1) );
1315
1316 -- following elsif is added to take care of Prepay Unapply scenarios, where in the recovered amt againt the main payment is reversed
1317 ELSIF lv_src_trx_type = jai_constants.prepay_unapplication THEN
1318 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
1319 OPEN c_repo_recovered_amt(p_source, p_payment_table_name, r_prepayment.parent_reversal_id, r_ref.reference_id);
1320 FETCH c_repo_recovered_amt INTO ln_parent_recovered_amt;
1321 CLOSE c_repo_recovered_amt;
1322
1323 ln_recovered_amount := -ln_parent_recovered_amt;
1324
1325 ELSE
1326 IF r_dist.invoice_amount = 0 THEN
1327 r_dist.invoice_amount := 1;
1328 end if;
1329 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath);
1330 ln_recovered_amount := (r_ref.recoverable_amount * ln_payment_amount) / r_dist.invoice_amount; -- CHK
1331
1332 /* Discount is considered only for payments and not for prepayments */
1333 if r_payment.amount = 0 THEN
1334 r_payment.amount := 1;
1335 end if;
1336 ln_discounted_amount := ln_recovered_amount * ( r_payment.discount_taken / r_payment.amount );
1337
1338 END IF;
1339
1340 IF g_debug = 'Y' THEN
1341 FND_FILE.put_line(fnd_file.log, 'RecoAmt:'||ln_recovered_amount||', RefRecobleAmt:'||r_ref.recoverable_amount
1342 ||', PaymtAmt:'||ln_payment_amount||', InvAmt:'||r_dist.invoice_amount||', DiscTaken:'||r_payment.discount_taken
1343 ||', rPayAmt:'||r_payment.amount||', DiscRecoAmt:'||ln_discounted_amount);
1344 END IF;
1345
1346 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath);
1347 ln_recovered_amount := nvl(ln_recovered_amount, 0);
1348 ln_discounted_amount := nvl(ln_discounted_amount, 0);
1349
1350 ln_validate_amount := r_ref.recovered_amount + r_ref.discounted_amount + ln_recovered_amount + ln_discounted_amount;
1351
1352 if ln_validate_amount = 0 THEN
1353 ln_validate_amount := 1;
1354 end if;
1355
1356
1357 IF r_ref.recoverable_amount > 0 AND ln_validate_amount > r_ref.recoverable_amount THEN
1358 lv_codepath := jai_general_pkg.plot_codepath(24, lv_codepath);
1359
1360 ln_diff_amount := ln_validate_amount - r_ref.recoverable_amount;
1361 ln_discounted_amount := ln_discounted_amount - (ln_discounted_amount * ln_diff_amount / ln_validate_amount);
1362 ln_recovered_amount := ln_recovered_amount - (ln_recovered_amount * ln_diff_amount / ln_validate_amount);
1363 --ln_recovered_amount := r_ref.recoverable_amount - r_ref.recovered_amount;
1364
1365 ELSIF r_ref.recoverable_amount < 0 AND ln_validate_amount < r_ref.recoverable_amount THEN
1366 lv_codepath := jai_general_pkg.plot_codepath(25, lv_codepath);
1367
1368 ln_diff_amount := ln_validate_amount - r_ref.recoverable_amount;
1369 ln_discounted_amount := ln_discounted_amount - (ln_discounted_amount * ln_diff_amount / ln_validate_amount);
1370 ln_recovered_amount := ln_recovered_amount - (ln_recovered_amount * ln_diff_amount / ln_validate_amount);
1371 -- ln_recovered_amount := r_ref.recoverable_amount - r_ref.recovered_amount;
1372
1373 END IF;
1374
1375 IF g_debug = 'Y' THEN
1376 FND_FILE.put_line(fnd_file.log, 'DiffAmt:'||ln_diff_amount||', ValidtAmt:'||ln_validate_amount
1377 ||', RecoAmt:'||ln_recovered_amount||', DiscRecoAmt:'||ln_discounted_amount);
1378 END IF;
1379
1380 IF ln_recovered_amount = 0 THEN
1381 lv_codepath := jai_general_pkg.plot_codepath(26, lv_codepath);
1382 IF g_debug='Y' THEN
1383 fnd_file.put_line(fnd_file.log,'Allready amount is recovered');
1384 END IF;
1385 RETURN;
1386 END IF;
1387 /*Bug 5879769 bduvarag start*/
1388 /* OPEN c_inv_organization_id(r_dist.po_distribution_id);
1389 FETCH c_inv_organization_id INTO ln_inv_organization_id;
1390 CLOSE c_inv_organization_id;*/
1391 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => r_ref.reference_id,
1392 p_organization_id => ln_organization_id,
1393 p_location_id => ln_location_id,
1394 p_service_type_code => lv_service_type_code,
1395 p_process_flag => lv_process_flag,
1396 p_process_message => lv_process_message
1397 );
1398 IF lv_process_flag <> jai_constants.successful THEN
1399 lv_codepath := jai_general_pkg.plot_codepath(27.1, lv_codepath);
1400 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
1401 return;
1402 END IF;
1403 /*Bug 5879769 bduvarag End*/
1404 -- Replaced rec_get_curr_dtls by r_payment for Bug 8294236
1405 ln_func_tax_amount := ln_recovered_amount * nvl(r_payment.exchange_rate, 1); -- Added for Bug 7522584
1406
1407 /*Bug 8294236 - Start*/
1408 ln_tot_tax_amt := ln_recovered_amount + nvl(ln_discounted_amount,0);
1409 IF (nvl(r_payment.exchange_rate,1) <> nvl(rec_get_curr_dtls.exchange_rate,1)
1410 AND r_payment.currency_code = rec_get_curr_dtls.payment_currency_code) THEN
1411 ln_exc_gain_loss_amt := (ln_tot_tax_amt * nvl(r_payment.exchange_rate,1))
1412 - (ln_tot_tax_amt * nvl(rec_get_curr_dtls.exchange_rate,1));
1413 ELSE
1414 ln_exc_gain_loss_amt := 0;
1415 END IF;
1416 /*Bug 8294236 - End*/
1417
1418 lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
1419 lv_called_from := 'AP_PROCESSING';--rchandan for bug#4428980
1420 jai_cmn_rgm_recording_pkg.insert_repository_entry(
1421 p_repository_id => ln_rgm_reposotory_id,
1422 p_regime_id => p_regime_id,
1423 p_tax_type => r_ref.tax_type,
1424 p_organization_type => jai_constants.orgn_type_io ,/*5694855*/
1425 p_organization_id => ln_organization_id ,/*5694855*/
1426 p_location_id => ln_location_id,/*5694855*/
1427 p_service_type_code => lv_service_type_code,/*5694855*/
1428 p_source => p_source,
1429 p_source_trx_type => lv_src_trx_type,
1430 p_source_table_name => p_payment_table_name,
1431 p_source_document_id => p_payment_document_id,
1432 p_transaction_date => ld_transaction_date,
1433 p_account_name => null,
1434 p_charge_account_id => null,
1435 p_balancing_account_id => null,
1436 p_amount => ln_func_tax_amount , -- Added for Bug 7522584
1437 p_discounted_amount => ln_discounted_amount,
1438 p_inv_organization_id => ln_organization_id,/*Bug 5879769 bduvarag*/
1439 p_trx_amount => ln_recovered_amount,
1440 p_assessable_value => null,
1441 p_tax_rate => null,
1442 p_reference_id => r_ref.reference_id,
1443 p_batch_id => p_batch_id,
1444 p_called_from => lv_called_from, --rchandan for bug#4428980
1445 p_process_flag => p_process_flag,
1446 p_process_message => p_process_message,
1447 p_accntg_required_flag => jai_constants.yes,
1448 p_accounting_date => ld_accounting_date,
1449 p_balancing_orgn_type => null,
1450 p_balancing_orgn_id => null,
1451 p_balancing_location_id => null,
1452 p_balancing_tax_type => null,
1453 p_balancing_accnt_name => null,
1454 /* added nvl part for bug 9187805 */
1455 p_currency_code => nvl(r_payment.currency_code,rec_get_curr_dtls.payment_currency_code), -- Added for Bug 7522584
1456 p_curr_conv_date => nvl(r_payment.exchange_date,rec_get_curr_dtls.exchange_date), -- Added for Bug 7522584
1457 p_curr_conv_type => nvl(r_payment.exchange_rate_type,rec_get_curr_dtls.exchange_rate_type), -- Added for Bug 7522584
1458 p_curr_conv_rate => nvl(r_payment.exchange_rate,rec_get_curr_dtls.exchange_rate) -- Added for Bug 7522584
1459 );
1460
1461 IF p_process_flag <> jai_constants.successful THEN
1462 lv_codepath := jai_general_pkg.plot_codepath(28, lv_codepath);
1463 RETURN;
1464 END IF;
1465
1466 /*Bug 8294236 - Start*/
1467 IF nvl(ln_exc_gain_loss_amt,0) <> 0 THEN
1468
1469 jai_cmn_rgm_recording_pkg.exc_gain_loss_accounting(
1470 p_repository_id => ln_rgm_reposotory_id ,
1471 p_regime_id => p_regime_id ,
1472 p_tax_type => r_ref.tax_type ,
1473 p_organization_type => jai_constants.orgn_type_io ,
1474 p_organization_id => ln_organization_id ,
1475 p_location_id => ln_location_id ,
1476 p_source => p_source ,
1477 p_source_trx_type => lv_src_trx_type ,
1478 p_source_table_name => p_payment_table_name ,
1479 p_source_document_id => p_payment_document_id ,
1480 p_transaction_date => ld_transaction_date ,
1481 p_account_name => NULL ,
1482 p_charge_account_id => NULL ,
1483 p_balancing_account_id => NULL ,
1484 p_exc_gain_loss_amt => ln_exc_gain_loss_amt ,
1485 p_reference_id => r_ref.reference_id ,
1486 p_called_from => 'AP_PROCESSING' ,
1487 p_process_flag => lv_process_flag ,
1488 p_process_message => lv_process_message ,
1489 p_accounting_date => ld_accounting_date
1490 );
1491
1492 IF lv_process_flag = jai_constants.expected_error OR
1493 lv_process_flag = jai_constants.unexpected_error
1494 THEN
1495 p_process_flag := lv_process_flag ;
1496 p_process_message := lv_process_message ;
1497 fnd_file.put_line( fnd_file.log, 'error in call to jai_rgm_trx_recording_pkg.exc_gain_loss_accounting - lv_process_flag '||lv_process_flag
1498 ||', lv_process_message'||lv_process_message);
1499 RETURN;
1500 END IF;
1501 END IF;
1502 /*Bug 8294236 - end*/
1503
1504 jai_cmn_rgm_recording_pkg.update_reference(
1505 p_source => p_source,
1506 p_reference_id => r_ref.reference_id,
1507 p_recovered_amount => ln_recovered_amount,
1508 p_discounted_amount => ln_discounted_amount, -- CHK (Implementation)
1509 p_process_flag => p_process_flag,
1510 p_process_message => p_process_message
1511 );
1512
1513 <<end_of_dist>>
1514 lv_codepath := jai_general_pkg.plot_codepath(49, lv_codepath, 'PROCESS_PAYMENT', 'END');
1515
1516 IF g_debug = 'Y' THEN
1517 FND_FILE.put_line( fnd_file.log, 'Codepath:'||lv_codepath);
1518 END IF;
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 p_process_flag := jai_constants.unexpected_error;
1522 p_process_message := 'Process Payment Error:'||SQLERRM;
1523 FND_FILE.put_line( fnd_file.log, p_process_message);
1524 FND_FILE.put_line( fnd_file.log, 'Error Codepath:'||lv_codepath);
1525
1526 END process_payment;
1527
1528 ---------------------------- PROCESS_BATCH ---------------------------
1529 PROCEDURE process_batch(
1530 errbuf OUT NOCOPY VARCHAR2,
1531 retcode OUT NOCOPY VARCHAR2,
1532 p_regime_id IN NUMBER,
1533 p_rgm_registration_num IN VARCHAR2,
1534 pv_trx_from_date IN VARCHAR2,
1535 pv_trx_till_date IN VARCHAR2,
1536 p_called_from IN VARCHAR2, -- DEFAULT 'Batch' File.Sql.35 by Brathod
1537 p_debug IN VARCHAR2, -- DEFAULT 'Y' File.Sql.35 by Brathod
1538 p_trace_switch IN VARCHAR2, -- DEFAULT 'N' File.Sql.35 by Brathod
1539 p_organization_id IN NUMBER DEFAULT NULL /*5694855*/
1540 ) IS
1541
1542 ln_batch_id JAI_RGM_CONC_REQUESTS.batch_id%TYPE;
1543 ln_request_id JAI_RGM_CONC_REQUESTS.request_id%TYPE;
1544 ld_trx_start_date DATE;
1545
1546 lv_process_flag VARCHAR2(2);
1547 lv_process_message VARCHAR2(1000);
1548
1549 /* Brathod, for Bug# 4286646*/
1550 p_trx_from_date DATE; -- DEFAULT fnd_date.canonical_to_date(pv_trx_from_date) File.Sql.35 by Brathod
1551 p_trx_till_date DATE; -- DEFAULT fnd_date.canonical_to_date(pv_trx_till_date) File.Sql.35 by Brathod
1552 /*End of Bug# 4286646 */
1553
1554 CURSOR c_regime_orgs(cp_regime_id IN NUMBER, cp_orgn_type IN VARCHAR2, cp_registration_num IN VARCHAR2,p_att_type_code jai_rgm_registrations.attribute_type_code%TYPE,cp_organization_id IN NUMBER ) IS/*Bug 5879769 bduvarag*/
1555 SELECT a.organization_id org_id,a.location_id /*Bug 5879769 bduvarag*/
1556 FROM JAI_RGM_ORG_REGNS_V a
1557 WHERE regime_id = cp_regime_id
1558 AND registration_type = jai_constants.regn_type_others
1559 AND attribute_type_code = p_att_type_code--rchandan for bug#4428980
1560 AND organization_type = cp_orgn_type
1561 AND attribute_value = cp_registration_num
1562 AND a.organization_id = nvl(cp_organization_id,a.organization_id) /*5694855*/;
1563
1564
1565 ld_rgm_effective_from JAI_RGM_DEFINITIONS.effective_date_from%TYPE;
1566 CURSOR c_rgm_effective_from_date(cp_regime_id IN NUMBER) IS
1567 SELECT trunc(creation_date) effective_date_from /* effective_date_from. Commneted this as part of VAT Impl. Vijay Shankar for Bug#425023(4245089) */
1568 FROM JAI_RGM_DEFINITIONS
1569 WHERE regime_id = cp_regime_id;
1570 /*Bug 5879769 bduvarag start*/
1571 CURSOR cur_fetch_ou(cp_organization_id NUMBER)
1572 IS
1573 SELECT org_information3
1574 FROM hr_organization_information
1575 WHERE upper(ORG_INFORMATION_CONTEXT) = 'ACCOUNTING INFORMATION'
1576 AND organization_id = cp_organization_id;
1577
1578 ln_org_id NUMBER; /*5694855*/
1579 /*Bug 5879769 bduvarag End*/
1580 BEGIN
1581
1582 p_trx_from_date := fnd_date.canonical_to_date(pv_trx_from_date); --File.Sql.35 by Brathod
1583 p_trx_till_date := fnd_date.canonical_to_date(pv_trx_till_date); --File.Sql.35 by Brathod
1584
1585 FND_FILE.put_line(fnd_file.log,'Value of from date is '||p_trx_from_date);
1586 FND_FILE.put_line(fnd_file.log,'Value of from date is '||p_trx_till_date);
1587
1588
1589 g_debug := p_debug;
1590 g_debug := 'Y';
1591
1592 IF p_debug = 'Y' THEN
1593 fnd_file.put_line(fnd_file.log, 'Enter1');
1594 END IF;
1595
1596 /*
1597 OPEN c_previous_batch_dtls(p_regime_id, p_rgm_registration_num);
1598 FETCH c_previous_batch_dtls INTO ld_trx_start_date;
1599 CLOSE c_previous_batch_dtls;
1600
1601 IF ld_trx_start_date IS NULL THEN
1602 ld_trx_start_date := to_date('22-DEC-2004', 'DD-MON-YYYY'); -- TEST CODE
1603 END IF;
1604 */
1605
1606 insert_request_details(
1607 p_batch_id => ln_batch_id, -- OUT parameter
1608 p_regime_id => p_regime_id,
1609 p_rgm_registration_num => p_rgm_registration_num,
1610 p_trx_from_date => p_trx_from_date,
1611 p_trx_till_date => p_trx_till_date
1612 );
1613
1614 OPEN c_rgm_effective_from_date(p_regime_id);
1615 FETCH c_rgm_effective_from_date INTO ld_rgm_effective_from;
1616 CLOSE c_rgm_effective_from_date;
1617
1618 IF p_trx_from_date < ld_rgm_effective_from THEN
1619
1620 FND_FILE.put_line(fnd_file.log, 'Start Date('||to_char(p_trx_from_Date,'DD-MON-YYYY')
1621 ||') of Transaction Processing cannot be less than Regime Effective Date('||to_char(ld_rgm_effective_from,'DD-MON-YYYY')||')'
1622 );
1623 retcode := jai_constants.request_error;
1624
1625 RETURN;
1626
1627 END IF;
1628
1629 ld_trx_start_date := p_trx_from_date;
1630
1631 FOR loop_io IN c_regime_orgs(p_regime_id, jai_constants.orgn_type_io, p_rgm_registration_num,'PRIMARY',p_organization_id) LOOP/*Bug 5879769 bduvarag*/
1632
1633 /* start changes by ssumaith - code review comments - bug# 6109941*/
1634 OPEN cur_fetch_ou(loop_io.org_id);
1635 FETCH cur_fetch_ou INTO ln_org_id;
1636 CLOSE cur_fetch_ou;
1637 /* ends additions by ssumaith - bug#6109941*/
1638
1639 /*Added by nprashar for bug # 6636517*/
1640 fnd_file.put_line(fnd_file.log,'P_organization_type :' || jai_constants.orgn_type_io);
1641 fnd_file.put_line(fnd_file.log,'P_organization_id :' ||ln_org_id);
1642 fnd_file.put_line(fnd_file.log,'P_Location_id :' ||loop_io.location_id); /*Ends here for bug #6636517*/
1643 /***************** Processing of AP Start Here *********************/
1644 process_payments(
1645 p_regime_id => p_regime_id,
1646 p_organization_type => jai_constants.orgn_type_io, /* ssumaith - bug# 6109941 */
1647 p_organization_id => loop_io.org_id,/*5694855*/ /* ssumaith 6109941 */
1648 p_trx_from_date => ld_trx_start_date,
1649 p_trx_to_date => p_trx_till_date,
1650 p_org_id => ln_org_id,
1651 p_batch_id => ln_batch_id,
1652 p_debug => p_debug,
1653 p_process_flag => lv_process_flag,
1654 p_process_message => lv_process_message
1655 );
1656
1657 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
1658 GOTO end_of_batch;
1659 END IF;
1660
1661 /***************** Processing of AR Start Here *********************/
1662 jai_ar_rgm_processing_pkg.process_records(
1663 p_regime_id => p_regime_id,
1664 p_organization_type => jai_constants.orgn_type_io,
1665 p_organization_id => loop_io.org_id ,/*5694855*/
1666 p_from_date => ld_trx_start_date,
1667 p_to_date => p_trx_till_date,
1668 p_org_id => ln_org_id,
1669 p_batch_id => ln_batch_id,
1670 p_process_flag => lv_process_flag,
1671 p_process_message => lv_process_message
1672 );
1673
1674 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
1675 GOTO end_of_batch;
1676 END IF;
1677
1678 END LOOP; -- Operating Units loop of Registration Number
1679
1680 <<end_of_batch>>
1681
1682 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
1683 FND_FILE.put_line( FND_FILE.log, 'Problem Message:'||lv_process_message);
1684 fnd_file.put_line(fnd_file.log,'Problem Message:'||lv_process_message);
1685 retcode := jai_constants.request_warning;
1686 errbuf := lv_process_message;
1687 END IF;
1688
1689 -- FINAL Commit to permanently save the transactions
1690 COMMIT;
1691
1692 EXCEPTION
1693 WHEN OTHERS THEN
1694 ROLLBACK;
1695 retcode := jai_constants.request_error;
1696 errbuf := 'Unexpected Error Occured:'||SQLERRM;
1697 FND_FILE.put_line( fnd_file.log, 'Unexpected Error Occured:'||SQLERRM);
1698
1699 END process_batch;
1700
1701 /*Bug 12805386 - Start*/
1702 /*Derive Organization and Location Information for an Invoice based on INVOICE_ID, INVOICE_LINE_NUMBER and PO_DISTRIBUTION_ID*/
1703 PROCEDURE get_org_location(p_invoice_id IN NUMBER,
1704 p_invoice_line_number IN NUMBER,
1705 p_po_distribution_id IN NUMBER,
1706 p_organization_id OUT NOCOPY NUMBER,
1707 p_location_id OUT NOCOPY NUMBER)
1708 IS
1709 /* Modified the cursor get_match_item_cur for bug 14507573 */
1710 CURSOR get_match_item_cur IS
1711
1712 SELECT distinct line.match_type,line.line_number
1713 FROM ap_invoice_lines_all line, jai_ap_match_inv_taxes jamt
1714 WHERE line.invoice_id = p_invoice_id
1715 and jamt.invoice_id=line.invoice_id
1716 and jamt.po_distribution_id=p_po_distribution_id
1717 and line.line_number=jamt.parent_invoice_line_number
1718 AND line.line_type_lookup_code ='ITEM'
1719 AND line.match_type IS NOT NULL;
1720
1721 CURSOR get_org_cur IS
1722 SELECT organization_id,location_id
1723 FROM jai_ap_invoice_lines
1724 WHERE invoice_id = p_invoice_id
1725 AND invoice_line_number = (SELECT parent_invoice_line_number
1726 FROM jai_ap_invoice_lines jail
1727 WHERE jail.invoice_line_number = p_invoice_line_number
1728 AND jail.invoice_id = p_invoice_id)
1729 AND parent_invoice_line_number IS NULL;
1730
1731 CURSOR get_po_matched_org_cur(pn_line_number NUMBER) IS
1732 SELECT po.ship_to_organization_id, po.ship_to_location_id
1733 FROM po_line_locations_all po,ap_invoice_lines_all ap
1734 WHERE po.line_location_id = ap.po_line_location_id
1735 AND ap.invoice_id = p_invoice_id
1736 AND ap.line_number = pn_line_number;
1737
1738 CURSOR get_rcpt_matched_org_cur(pn_line_number NUMBER) IS
1739 SELECT rcv.organization_id, rcv.location_id
1740 FROM jai_rcv_transactions rcv, ap_invoice_lines_all ap
1741 WHERE ap.rcv_transaction_id = rcv.transaction_id
1742 AND ap.invoice_id = p_invoice_id
1743 AND ap.line_number = pn_line_number;
1744
1745 lv_match_type VARCHAR2(50);
1746 ln_item_line_num NUMBER;
1747 ln_organization_id NUMBER;
1748 ln_location_id NUMBER;
1749 BEGIN
1750 IF p_po_distribution_id IS NOT NULL THEN
1751
1752 OPEN get_match_item_cur;
1753 FETCH get_match_item_cur INTO lv_match_type, ln_item_line_num;
1754 CLOSE get_match_item_cur;
1755
1756 IF lv_match_type IN('ITEM_TO_PO','ITEM_TO_RECEIPT') THEN
1757
1758 IF lv_match_type = 'ITEM_TO_PO' THEN
1759
1760 OPEN get_po_matched_org_cur(ln_item_line_num);
1761 FETCH get_po_matched_org_cur INTO ln_organization_id,ln_location_id;
1762 CLOSE get_po_matched_org_cur;
1763
1764 ELSE
1765
1766 OPEN get_rcpt_matched_org_cur(ln_item_line_num);
1767 FETCH get_rcpt_matched_org_cur INTO ln_organization_id,ln_location_id;
1768 CLOSE get_rcpt_matched_org_cur;
1769
1770 IF ln_organization_id IS NULL OR ln_location_id IS NULL THEN
1771
1772 OPEN get_po_matched_org_cur(ln_item_line_num);
1773 FETCH get_po_matched_org_cur INTO ln_organization_id,ln_location_id;
1774 CLOSE get_po_matched_org_cur;
1775
1776 END IF;/*ln_organization_id IS NULL OR ln_location_id IS NULL */
1777
1778 END IF;/*lv_match_type = 'ITEM_TO_PO' */
1779
1780 END IF;/*lv_match_type IN('ITEM_TO_PO','ITEM_TO_RECEIPT')*/
1781
1782 ELSE
1783
1784 OPEN get_org_cur;
1785 FETCH get_org_cur INTO ln_organization_id,ln_location_id;
1786 CLOSE get_org_cur;
1787
1788 END IF;
1789 p_organization_id := ln_organization_id;
1790 p_location_id := ln_location_id;
1791 END get_org_location;
1792 /*Bug 12805386 - End*/
1793
1794 PROCEDURE process_payments(
1795 p_regime_id IN NUMBER,
1796 p_organization_type IN VARCHAR2,
1797 p_trx_from_date IN DATE,
1798 p_trx_to_date IN DATE,
1799 p_org_id IN NUMBER,
1800 p_batch_id IN NUMBER,
1801 p_debug IN VARCHAR2,
1802 p_process_flag OUT NOCOPY VARCHAR2,
1803 p_process_message OUT NOCOPY VARCHAR2,
1804 p_organization_id IN NUMBER DEFAULT NULL /*5694855*/
1805 ) IS
1806
1807 v_today DATE ; -- := trunc(sysdate) -- File.Sql.35 by Brathod
1808 lv_standard_lookup CONSTANT varchar2(30) := 'STANDARD'; --rchandan for bug#4428980
1809 lv_debit_lookup CONSTANT varchar2(30) := 'DEBIT'; --rchandan for bug#4428980
1810 lv_credit_lookup CONSTANT varchar2(30) := 'CREDIT'; --Added by Eric Ma for bug#10376849 on 14-Dec-2010
1811 --Bug 4991017. Added by Lakshmi Gopalsami
1812 lv_entity_code CONSTANT varchar2(30) := 'AP_INVOICES';
1813
1814 CURSOR c_previous_batch_dtls(cp_regime_id IN NUMBER, cp_registration_num IN VARCHAR2) IS
1815 SELECT trx_till_date+1
1816 FROM jai_rgm_conc_requests
1817 WHERE regime_id = cp_regime_id
1818 AND rgm_registration_num = cp_registration_num;
1819
1820 /* Bug 4991017. Added by Lakshmi Gopalsami
1821 Merged the cursors c_ap_accounted_invoices and c_event_distributions
1822 because of SLA uptake by base and removed the same.
1823 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
1824 (2) Also added xla_transaction_entities to get the entity_id and
1825 source_int_id_1 so that it can be joined with transaction tables.
1826 (3) Discussed with shekhar and found that we should derive by
1827 accounting_date and not on the creation_date.
1828 (4) Added accounting_event_id in cursor
1829 */
1830 -- Considers only Localization Tax Distributions created from Receipt, PO, Invoice Matching
1831 CURSOR c_ap_accounted_inv_dist(cp_ae_category IN VARCHAR2,
1832 cp_start_date IN DATE ,
1833 cp_till_date IN DATE ,
1834 cp_sob_id IN NUMBER) IS/*Bug 5879769 bduvarag*/
1835 SELECT aid.invoice_id, aid.invoice_distribution_id,
1836 aid.distribution_line_number, aid.invoice_line_number,
1837 aid.reversal_flag, aid.parent_reversal_id,
1838 aid.accrual_posted_flag, aid.cash_posted_flag,
1839 aid.amount, aid.base_amount,
1840 aid.po_distribution_id, aid.rcv_transaction_id,
1841 -- Bug 4991017 Added by Lakshmi Gopalsami.
1842 -- Added accounting_event_id in cursor.
1843 aid.org_id, aid.accounting_event_id,
1844 ai.vendor_id, ai.vendor_site_id, ai.invoice_currency_code,
1845 aid.exchange_rate, aid.exchange_rate_type, aid.exchange_date,
1846 ai.source
1847 ,aid.accounting_date --Added by zhiwei for POT change Bug#13023443 on 20110930
1848 FROM xla_ae_headers xah ,
1849 xla_transaction_entities xte,
1850 ap_invoices_all ai,
1851 ap_invoice_distributions_all aid
1852 WHERE xah.je_category_name = cp_ae_category
1853 AND xah.ledger_id = cp_sob_id
1854 AND xah.application_id =200
1855 and xah.entity_id = xte.entity_id
1856 AND xte.application_id = 200
1857 and xte.entity_code =lv_entity_code --'AP_INVOICES'
1858 and xte.source_id_int_1 = ai.invoice_id
1859 AND aid.invoice_id = ai.invoice_id
1860 and aid.accounting_event_id = xah.event_id
1861 AND ai.invoice_type_lookup_code IN (lv_standard_lookup, lv_debit_lookup,lv_credit_lookup) --lv_credit_lookup is added by Eric Ma for bug#10376849 on 14-Dec-2010
1862 --AND ai.cancelled_date IS NULL --Commented out by Xiao, for POT cancellation, reg bug#12533434
1863 AND ( aid.line_type_lookup_code = jai_constants.misc_line
1864 or exists (select 1 from jai_rcv_tp_invoices jtp where AID.invoice_id = jtp.invoice_id)) /* modified by vumaasha for bug 8965721 */
1865 AND aid.posted_flag = 'Y'
1866 /*bug 7347127 - moved the trunc in following 2 expressions to the RHS, so that
1867 * the indec on accounting_date would be used in the two tables. In some cases,
1868 * this would prevent performance issue in the Service Tax Processor*/
1869 AND xah.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
1870 AND aid.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
1871 and ai.org_id = p_org_id
1872 -- and aid.org_id = p_org_id; -- commented out by Chong for bug#13259755 on 27-OCT-2011
1873 --Add by Chong for bug#13259755 on 27-OCT-2011 start
1874 and aid.org_id = p_org_id
1875 AND ( EXISTS
1876 (
1877 SELECT 1
1878 FROM jai_ap_invoice_lines
1879 WHERE organization_id = NVL(p_organization_id,organization_id)
1880 AND ai.invoice_id = invoice_id
1881 )
1882 OR EXISTS
1883 (
1884 SELECT 1
1885 FROM po_line_locations_all po
1886 , ap_invoice_lines_all line
1887 WHERE ai.invoice_id = line.invoice_id
1888 AND line.po_line_location_id = po.line_location_id
1889 AND (line.match_type = 'ITEM_TO_RECEIPT'
1890 OR line.match_type = 'ITEM_TO_PO')
1891 AND po.ship_to_organization_id = NVL(p_organization_id,po.ship_to_organization_id)
1892 )
1893 )
1894 ;
1895 --Add by Chong for bug#13259755 on 27-OCT-2011 end
1896
1897 /*bug 7347127 - commented the order by clause to improve performance*/
1898 --ORDER BY aid.accounting_date, aid.invoice_distribution_id;
1899 /*Bug 5879769 bduvarag start*/
1900 CURSOR c_prepayment_applications(cp_start_date IN DATE, cp_till_date IN DATE)
1901 IS
1902 SELECT invoice_id,
1903 invoice_distribution_id,
1904 prepay_distribution_id ,
1905 amount ,
1906 reversal_flag ,
1907 parent_reversal_id ,
1908 org_id ,
1909 invoice_line_number ,/*Bug 12805386*/
1910 po_distribution_id /*Bug 12805386*/
1911 FROM ap_invoice_distributions_all
1912 WHERE org_id = p_org_id
1913 AND line_type_lookup_code = jai_constants.prepay_line
1914 AND invoice_id IN ( SELECT invoice_id
1915 FROM ap_invoice_distributions_all
1916 WHERE po_distribution_id IN ( SELECT pda.po_distribution_id
1917 FROM po_line_locations_all pll,
1918 po_distributions_all pda,
1919 jai_po_line_locations jpll
1920 WHERE pll.line_location_id = jpll.line_location_id
1921 AND pll.line_location_id = pda.line_location_id
1922 AND pll.ship_to_organization_id = p_organization_id
1923 )
1924 AND ( (cp_start_date IS NULL AND creation_date < cp_till_date)
1925 OR (cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
1926 )
1927
1928 -- union added by Xiao Lv for Bug#7191302
1929 UNION
1930 select invoice_id
1931 from jai_rcv_tp_invoices
1932 where vendor_id
1933 in (select vendor_id
1934 from po_vendors
1935 where trim(vendor_type_lookup_code)
1936 -- like 'Service Tax Authorities') commeneted for bug#11666653
1937 -- like 'INDIA SERVICE TAX AUTHORITY') --bug#11666653
1938 like 'INDIA SERVICE TAX%AUTHORITY') --bug#11666653 ,vendor lookup has the values with double space btw tax and authority hence added % in btw.
1939 AND ( (cp_start_date IS NULL AND creation_date < cp_till_date)
1940 OR (cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
1941 )--Xiao Lv for Bug#7191302
1942 /*Bug 10630847 - Start*/
1943 /*Pick Prepayment applications on Standalone AP Invoice also*/
1944 UNION
1945 select aia.invoice_id
1946 from ap_invoices_all aia
1947 where --aia.source='Manual Invoice Entry' and/*commented by vkaranam for bug#12360337*/
1948 exists (select '1'
1949 from jai_ap_invoice_lines jail
1950 where aia.invoice_id = jail.invoice_id
1951 and jail.organization_id = p_organization_id)
1952 AND ((cp_start_date IS NULL AND aia.creation_date < cp_till_date)
1953 OR (cp_start_date IS NOT NULL AND trunc(aia.creation_date) between cp_start_date AND cp_till_date))
1954 /*Bug 10630847 - End*/
1955 )/*5694855*/
1956 AND prepay_distribution_id IS NOT NULL
1957 AND ( ( cp_start_date IS NULL AND creation_date < cp_till_date)
1958 OR ( cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
1959 )
1960
1961 ORDER BY invoice_distribution_id;
1962 /*Bug 5879769 bduvarag end*/
1963 CURSOR c_invoice_distributions(cp_invoice_id IN NUMBER) IS
1964 SELECT a.invoice_id, a.invoice_distribution_id, a.distribution_line_number, a.dist_match_type,
1965 a.invoicE_line_number, /* INVOICE LINES UPTAKE */
1966 a.parent_reversal_id, a.reversal_flag, a.rcv_transaction_id, a.po_distribution_id
1967 , a.accounting_date ----Add by Xiao for POT change, reg bug#12533434 on 29-May-2011
1968 FROM ap_invoice_distributions_all a, jai_rgm_trx_refs b /* second table is used for join just to take IL records */
1969 WHERE a.invoice_id = cp_invoice_id
1970 /*Added the below or condition for bug#11666653*/
1971 AND (
1972 (a.line_type_lookup_code <> 'PREPAY' And exists (select '1' from jai_rcv_tp_invoices where invoice_id=a.invoice_id))
1973 OR
1974 a.line_type_lookup_code = jai_constants.misc_line -- <> 'PREPAY'
1975 )
1976
1977 AND b.source = jai_constants.source_ap
1978 and b.invoice_id = a.invoice_id
1979 and b.line_id = a.invoice_distribution_id
1980 ORDER BY a.invoice_distribution_id;
1981
1982 CURSOR c_tax_dist_dtl(cp_regime_id IN NUMBER, cp_invoice_id IN NUMBER, cp_inv_distribution_id IN NUMBER) IS -- cp_dist_line_no IN NUMBER) IS
1983 SELECT 1 chk, a.tax_id, b.tax_rate, a.tax_amount, a.parent_invoice_distribution_id, b.tax_type,
1984 a.invoice_line_number invoice_line_number, /* INVOICE LINES UPTAKE */
1985 nvl(b.mod_cr_percentage,0) recoverable_ptg, a.base_amount
1986 FROM JAI_AP_MATCH_INV_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c
1987 WHERE a.invoice_id = cp_invoice_id
1988 -- AND a.distribution_line_number = cp_dist_line_no Modified as part of AP INVOICE Lines Uptake project
1989 AND a.invoice_distribution_id = cp_inv_distribution_id
1990 AND a.tax_id = b.tax_id
1991 AND b.tax_type = c.attribute_code
1992 and c.regime_id = cp_regime_id
1993 and c.registration_type = jai_constants.regn_type_tax_types
1994 -- 5763527, modified and condition as below
1995 AND ( mod_cr_percentage > 0 and mod_cr_percentage <= 100 and nvl(recoverable_flag,'Y') <> 'N')
1996 -- Added by qiong for Reverse Charge code port begin
1997 UNION
1998 SELECT 1 chk, match_tax.tax_id, tax.tax_rate, abs(ap_dist.amount), match_tax.parent_invoice_distribution_id,tax.tax_type,
1999 ap_line.line_number invoice_line_number,
2000 100 recoverable_ptg, match_tax.base_amount
2001 FROM AP_INVOICE_LINES_ALL ap_line,
2002 AP_INVOICE_DISTRIBUTIONS_ALL ap_dist, JAI_AP_MATCH_INV_TAXES match_tax,
2003 JAI_CMN_TAXES_ALL tax, JAI_RGM_REGISTRATIONS rgm
2004 WHERE ap_dist.invoice_id = cp_invoice_id
2005 AND ap_dist.invoice_distribution_id = cp_inv_distribution_id
2006 AND ap_line.invoice_id = ap_dist.invoice_id
2007 AND ap_dist.invoice_line_number = ap_line.line_number
2008 AND ap_line.line_type_lookup_code = 'MISCELLANEOUS'
2009 AND ap_dist.amount < 0
2010 AND ap_line.application_id = 7000
2011 AND ap_line.PRODUCT_TABLE = 'JAI_AP_MATCH_INV_TAXES'
2012 AND ap_line.REFERENCE_KEY1 = to_char(match_tax.tax_id)
2013 AND ap_line.REFERENCE_KEY2 = to_char(match_tax.parent_invoice_line_number)
2014 AND match_tax.invoice_id = ap_dist.invoice_id
2015 AND match_tax.tax_id = tax.tax_id
2016 AND match_tax.parent_invoice_distribution_id IS NOT NULL
2017 AND tax.reverse_charge_flag = 'Y'
2018 AND tax.tax_type = rgm.attribute_code
2019 and rgm.regime_id = cp_regime_id
2020 and rgm.registration_type = jai_constants.regn_type_tax_types
2021 -- Added by Qiong for Reverse Charge code port begin
2022 UNION --Added this union for bug#8943349 by JMEENA
2023 SELECT 2 chk, a.tax_id, b.tax_rate, a.tax_amt,null, b.tax_type,
2024 /*Bug 9854974 - Item Line ID in JAI_RGM_TRX_REFS should refer to the Item Line to which Service Tax is attached
2025 and not the Line Number of the Service Tax*/
2026 a.source_doc_parent_line_no invoice_line_number, /* INVOICE LINES UPTAKE */
2027 nvl(b.mod_cr_percentage,0) recoverable_ptg, d.base_amount
2028 FROM JAI_CMN_DOCUMENT_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c,
2029 AP_INVOICE_DISTRIBUTIONS_ALL d
2030 WHERE a.source_doc_id = cp_invoice_id
2031 AND d.invoice_distribution_id = cp_inv_distribution_id
2032 AND d.invoice_id = a.source_doc_id
2033 AND d.invoice_line_number = a.source_doc_line_id
2034 AND a.tax_id = b.tax_id
2035 AND b.tax_type = c.attribute_code
2036 and c.regime_id = cp_regime_id
2037 and c.registration_type = jai_constants.regn_type_tax_types
2038 AND ( mod_cr_percentage > 0 and mod_cr_percentage <= 100 and nvl(modvat_flag,'Y') <> 'N')
2039 -- Added by Qiong for Reverse Charge Code port begin
2040 UNION
2041 SELECT 2 chk, b.tax_id,
2042 b.tax_rate,
2043 abs(ap_dist.amount),
2044 null, b.tax_type,
2045 a.source_doc_parent_line_no invoice_line_number, /* INVOICE LINES UPTAKE */
2046 100 recoverable_ptg,
2047 ap_dist.base_amount
2048 FROM
2049 AP_INVOICE_LINES_ALL ap_line,AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,JAI_CMN_DOCUMENT_TAXES a, JAI_RGM_REGISTRATIONS c,
2050 JAI_CMN_TAXES_ALL b
2051 WHERE ap_dist.invoice_id = cp_invoice_id
2052 AND ap_dist.invoice_distribution_id = cp_inv_distribution_id
2053 AND ap_line.invoice_id = ap_dist.invoice_id
2054 AND ap_dist.invoice_line_number = ap_line.line_number
2055 AND ap_line.line_type_lookup_code = 'MISCELLANEOUS'
2056 AND ap_dist.amount < 0
2057 AND ap_line.application_id = 7000
2058 AND ap_line.PRODUCT_TABLE = 'JAI_CMN_DOCUMENT_TAXES'
2059 AND ap_line.REFERENCE_KEY1 = to_char(a.tax_id)
2060 AND ap_line.REFERENCE_KEY2 = to_char(a.source_doc_parent_line_no)
2061 AND a.source_doc_id = ap_dist.invoice_id
2062 AND a.tax_id = b.tax_id
2063 AND b.reverse_charge_flag = 'Y'
2064 AND b.tax_type = c.attribute_code
2065 And c.regime_id = cp_regime_id
2066 And c.registration_type = jai_constants.regn_type_tax_types;
2067 -- Added by Qiong for Reverse Charge Code port End
2068
2069
2070 -- AND b.mod_cr_percentage > 0;
2071
2072 /* Cursor added by ssumaith - bug# 4284505*/
2073 --added cp_inv_distribution_id in number for bug#10085619
2074 CURSOR c_tp_inv_details (cp_regime_id IN NUMBER , cp_invoice_id IN NUMBER , cp_line_number IN NUMBER,cp_inv_distribution_id in number) IS
2075 SELECT 1 chk , a.tax_id , a.tax_rate , a.tax_amount , NULL ,a.tax_type ,
2076 a.line_number invoice_line_number, /* INVOICE LINES UPTAKE */
2077 NVL(b.mod_cr_percentage,0) recoverable_ptg, NULL
2078 FROM jai_rcv_tp_inv_details a , JAI_CMN_TAXES_ALL b , jai_rcv_tp_invoices c, JAI_RGM_REGISTRATIONS d
2079 WHERE c.invoice_id = cp_invoice_id
2080 AND a.batch_invoice_id = c.batch_invoice_id
2081 AND a.tax_id = b.tax_id
2082 AND a.line_number = cp_line_number /*INVOICE LINES UPTAKE cp_dist_line_number */
2083 AND b.tax_type = attribute_code
2084 and d.regime_id = cp_regime_id
2085 AND d.registration_type = jai_constants.regn_type_tax_types
2086 AND b.mod_cr_percentage > 0
2087 /*start additions for bug#10085619
2088 ,the below union clause will fetch the misc lines attached to the TP invoice**/
2089 UNION
2090 SELECT 2 chk, a.tax_id, b.tax_rate, a.tax_amt,null, b.tax_type,
2091 a.source_doc_parent_line_no invoice_line_number,
2092 nvl(b.mod_cr_percentage,0) recoverable_ptg, d.base_amount
2093 FROM JAI_CMN_DOCUMENT_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c,
2094 AP_INVOICE_DISTRIBUTIONS_ALL d
2095 WHERE a.source_doc_id = cp_invoice_id
2096 AND d.invoice_distribution_id = cp_inv_distribution_id
2097 AND d.invoice_id = a.source_doc_id
2098 AND d.invoice_line_number = a.source_doc_line_id
2099 AND a.tax_id = b.tax_id
2100 AND b.tax_type = c.attribute_code
2101 and c.regime_id = cp_regime_id
2102 and c.registration_type = jai_constants.regn_type_tax_types
2103 AND ( nvl(mod_cr_percentage,0) > 0 and nvl(modvat_flag,'Y') <> 'N')
2104 /* Added below union by Avanija for bug 16246654 */
2105 union
2106 SELECT 2 chk, b.tax_id,
2107 b.tax_rate,
2108 abs(ap_dist.amount),
2109 null, b.tax_type,
2110 a.source_doc_parent_line_no invoice_line_number,
2111 100 recoverable_ptg,
2112 ap_dist.base_amount
2113 FROM
2114 AP_INVOICE_LINES_ALL ap_line,AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,JAI_CMN_DOCUMENT_TAXES a, JAI_RGM_REGISTRATIONS c,
2115 JAI_CMN_TAXES_ALL b
2116 WHERE ap_dist.invoice_id = cp_invoice_id
2117 AND ap_dist.invoice_distribution_id = cp_inv_distribution_id
2118 AND ap_line.invoice_id = ap_dist.invoice_id
2119 AND ap_dist.invoice_line_number = ap_line.line_number
2120 AND ap_line.line_type_lookup_code = 'MISCELLANEOUS'
2121 AND ap_dist.amount < 0
2122 AND ap_line.application_id = 7000
2123 AND ap_line.PRODUCT_TABLE = 'JAI_CMN_DOCUMENT_TAXES'
2124 AND ap_line.REFERENCE_KEY1 = to_char(a.tax_id)
2125 AND ap_line.REFERENCE_KEY2 = to_char(a.source_doc_parent_line_no)
2126 AND a.source_doc_id = ap_dist.invoice_id
2127 AND a.tax_id = b.tax_id
2128 AND b.reverse_charge_flag = 'Y'
2129 AND b.tax_type = c.attribute_code
2130 And c.regime_id = cp_regime_id
2131 And c.registration_type = jai_constants.regn_type_tax_types;
2132
2133
2134
2135
2136 CURSOR c_item_id(cp_po_distribution_id IN NUMBER) IS
2137 SELECT b.item_id
2138 FROM po_distributions_all a, po_lines_all b
2139 WHERE po_distribution_id = cp_po_distribution_id
2140 AND a.po_line_id = b.po_line_id;
2141
2142 CURSOR c_batch_references(cp_batch_id IN NUMBER, cp_source IN VARCHAR2) IS
2143 SELECT distinct invoice_id
2144 FROM jai_rgm_trx_refs
2145 WHERE batch_id = cp_batch_id
2146 AND source = cp_source;
2147
2148 CURSOR c_previous_payments_of_inv(cp_invoice_id IN NUMBER, cp_start_date IN DATE) IS
2149 SELECT a.invoice_payment_id, a.check_id, a.amount, a.payment_base_amount, a.reversal_flag,
2150 a.reversal_inv_pmt_id, a.org_id
2151 FROM ap_invoice_payments_all a, ap_checks_all b
2152 WHERE a.invoice_id = cp_invoice_id
2153 AND a.check_id = b.check_id
2154 AND a.creation_date < cp_start_date
2155 AND nvl(b.future_pay_due_date, v_today) <= v_today
2156 AND a.amount <> 0 /* ssumaith bug# 6104491 */
2157 AND a.invoice_payment_id NOT IN (select source_document_id from jai_rgm_trx_records -- CHK is this required
2158 where source = jai_constants.source_ap
2159 and source_table_name = jai_constants.ap_payments
2160 and source_document_id = a.invoice_payment_id
2161 )
2162 ORDER BY invoice_payment_id;
2163
2164 CURSOR c_previous_prepayments(cp_invoice_id IN NUMBER, cp_start_date IN DATE) IS
2165 SELECT invoice_distribution_id, reversal_flag, parent_reversal_id, amount, org_id
2166 FROM ap_invoice_distributions_all
2167 WHERE invoice_id = cp_invoice_id
2168 AND creation_date < cp_start_date
2169 ORDER BY invoice_distribution_id;
2170
2171 /*
2172 ||Cursor modified by aiyer for the bug 4947102 .
2173 ||Merged the cursors c_period_payments with c_invoice_distributions into c_period_payments
2174 */
2175 CURSOR c_period_payments( /*Bug 5879769 bduvarag*/
2176 cp_start_date IN DATE ,
2177 cp_till_date IN DATE
2178 )
2179 IS
2180 SELECT
2181 ainvd.invoice_id,
2182 ainvd.invoice_distribution_id,
2183 ainvd.distribution_line_number,
2184 ainvd.dist_match_type,
2185 ainvd.invoice_line_number, /* INVOICE LINES UPTAKE */
2186 ainvd.parent_reversal_id,
2187 ainvd.reversal_flag,
2188 ainvd.rcv_transaction_id,
2189 ainvd.po_distribution_id,
2190 apinvp.invoice_payment_id,
2191 apinvp.check_id,
2192 apinvp.amount,
2193 apinvp.org_id,
2194 ainvd.accounting_date -- Xiao for POT change, reg bug#12533434
2195 FROM
2196 ap_invoice_payments_all apinvp,
2197 ap_checks_all apc ,
2198 ap_invoice_distributions_all ainvd ,
2199 jai_rgm_trx_refs jrtr /* second table is used for join just to take IL records */
2200 WHERE
2201 apinvp.org_id = p_org_id
2202 AND apinvp.check_id = apc.check_id
2203 AND nvl(apc.future_pay_due_date, v_today) <= v_today
2204 AND apinvp.accounting_date/*Commented by nprashar for bug #6636517
2205 v_today*/ BETWEEN cp_start_date AND cp_till_date
2206 AND ainvd.invoice_id IN
2207 ( SELECT invoice_id
2208 FROM ap_invoice_distributions_all
2209 WHERE org_id = p_org_id
2210 AND po_distribution_id in
2211 (SELECT pda.po_distribution_id
2212 FROM po_line_locations_all pll,
2213 po_distributions_all pda,
2214 jai_po_line_locations jpll
2215 WHERE pll.line_location_id = jpll.line_location_id
2216 AND pll.line_location_id = pda.line_location_id
2217 AND pll.ship_to_organization_id = p_organization_id
2218 )
2219 /* Bug 7172723. Added by Lakshmi Gopalsami
2220 * Added union clause.
2221 */
2222 UNION
2223 SELECT jrti.invoice_id
2224 FROM jai_rcv_tp_invoices jrti
2225 WHERE jrti.vendor_id = apc.vendor_id
2226 AND jrti.vendor_site_id = apc.vendor_site_id
2227 AND apc.org_id = p_org_id
2228 UNION --Added this union for bug#8943349 by JMEENA
2229 select aia.invoice_id
2230 from ap_invoices_all aia , jai_ap_invoice_lines jail
2231 where aia.invoice_id = jail.invoice_id
2232 -- and aia.source='Manual Invoice Entry' /*commented the condition by vkaranam for bug#12360337*/
2233 and jail.organization_id = p_organization_id
2234 )/*5694855*/
2235
2236 AND ( ainvd.line_type_lookup_code = jai_constants.misc_line
2237 /* modified by vumaasha for bug 8965721 */
2238 OR exists (select 1 from jai_rcv_tp_invoices jtp where jtp.invoice_id=ainvd.invoice_id ) )
2239 AND jrtr.source = jai_constants.source_ap
2240 AND jrtr.invoice_id = ainvd.invoice_id
2241 AND apinvp.invoice_id = ainvd.invoice_id --added by csahoo for bug#6436576
2242 AND jrtr.line_id = ainvd.invoice_distribution_id
2243 ORDER BY
2244 apinvp.invoice_payment_id ,
2245 ainvd.invoice_distribution_id;
2246
2247 CURSOR c_invoice_batch_refs(cp_source IN VARCHAR2, cp_batch_id IN NUMBER, cp_invoice_id IN NUMBER) IS
2248 SELECT *
2249 FROM jai_rgm_trx_refs
2250 WHERE source = cp_source
2251 AND batch_id = cp_batch_id
2252 AND invoice_id = cp_invoice_id
2253 AND reversal_flag IS NULL
2254 ORDER by invoice_id, line_id;
2255
2256 /* Bug 5243532. Added by Lakshmi Gopalsami
2257 removed the cursor c_sob_of_ou and implemented using caching
2258 logic.
2259 */
2260 CURSOR c_payment_chk(cp_source IN VARCHAR2, cp_source_table_name IN VARCHAR2, cp_source_document_id IN NUMBER) IS
2261 SELECT 1
2262 FROM jai_rgm_trx_records
2263 WHERE source = jai_constants.source_ap
2264 AND source_table_name = jai_constants.ap_payments
2265 AND source_document_id = cp_source_document_id;
2266
2267 /*OPEN c_payment_chk(jai_constants.source_ap, jai_constants.ap_payments, inv_payment.invoice_payment_id);
2268 FETCH c_payment_chk INTO ln_chk;
2269 CLOSE c_payment_chk; */
2270
2271 r_ref c_reference%ROWTYPE;
2272 r_parent_ref c_reference%ROWTYPE;
2273 r_parent_dist c_invoice_distribution%ROWTYPE;
2274
2275 r_tax_dist_dtl c_tax_dist_dtl%ROWTYPE;
2276
2277 ln_item_line_id NUMBER(15); -- Incase of AP -> AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id%TYPE;
2278 ln_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE;
2279 ln_reference_id JAI_RGM_TRX_REFS.reference_id%TYPE;
2280 ln_parent_reference_id JAI_RGM_TRX_REFS.parent_reference_id%TYPE;
2281 ln_taxable_basis NUMBER;
2282
2283 ln_rgm_reposotory_id JAI_RGM_TRX_RECORDS.repository_id%TYPE;
2284 ln_recovered_amount JAI_RGM_TRX_REFS.recovered_amount%TYPE;
2285 ln_recoverable_amount JAI_RGM_TRX_REFS.recoverable_amount%TYPE;
2286
2287 ln_sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
2288 lv_src_trx_type JAI_RGM_TRX_RECORDS.source_trx_type%TYPE;
2289 lv_process_flag VARCHAR2(2);
2290 lv_process_message VARCHAR2(1000);
2291
2292 ln_commit_interval NUMBER(5) := 500;
2293 ln_uncommited_trxs NUMBER(6) := 0;
2294
2295 /* Bug 5243532. Added by Lakshmi Gopalsami
2296 Defined variahle for caching logic.
2297 */
2298 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
2299
2300
2301 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, begin
2302 ----------------------------------------------------------------------------------------------
2303 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
2304 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
2305 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
2306
2307 CURSOR c_get_st_accrual_date(p_organization_id NUMBER, p_location_id NUMBER) IS
2308 select to_date(attribute_value, 'DD/MM/YYYY')
2309 from JAI_RGM_ORG_REGNS_V
2310 where regime_id = (SELECT regime_id
2311 FROM JAI_RGM_DEFINITIONS
2312 WHERE regime_code = jai_constants.service_regime)
2313 and organization_id = p_organization_id
2314 and location_id = p_location_id
2315 AND attribute_code = 'EFF_DATE_ST_PT'
2316 AND attribute_type_code = 'OTHERS'
2317 AND registration_type = 'OTHERS'
2318 AND (NOT EXISTS
2319 (select '1'
2320 from JAI_RGM_ORG_REGNS_V
2321 where regime_id = p_regime_id
2322 and attribute_code IN 'INV_ORG_CLASSIFICATION'
2323 and attribute_value <> 'ORGANIZATION'
2324 and organization_id = p_organization_id
2325 and location_id = p_location_id)
2326 OR
2327 NOT EXISTS
2328 (select '1'
2329 from JAI_RGM_ORG_REGNS_V
2330 where regime_id = p_regime_id
2331 and attribute_code IN 'SERVICE TYPE'
2332 and attribute_value <> 'OTHER'
2333 and organization_id = p_organization_id
2334 and location_id = p_location_id)
2335 );
2336
2337 /*Bug 12805386 - End*/
2338 ld_st_accrual_date DATE;
2339
2340 CURSOR get_accounting_date(pn_invoice_distribution_id NUMBER) IS
2341 SELECT accounting_date, po_distribution_id, invoice_id, invoice_line_number /*Bug 12805386*/
2342 FROM ap_invoice_distributions_all
2343
2344 WHERE invoice_distribution_id = pn_invoice_distribution_id;
2345
2346 ld_gl_date DATE;
2347 ln_po_distribution_id NUMBER; /*Bug 12805386*/
2348 ln_invoice_id NUMBER; /*Bug 12805386*/
2349 ln_invoice_line_number NUMBER; /*Bug 12805386*/
2350 l_organization_id NUMBER; /*Bug 12805386*/
2351 l_location_id NUMBER; /*Bug 12805386*/
2352 ----------------------------------------------------------------------------------------------
2353 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, end
2354
2355
2356 --Added by zhiwei for POT change bug#13023443 on 20110930 begin
2357 -------------------------------------------------------------------------
2358 lv_interface_flag jai_ap_invoice_lines.interface_flag%type;
2359 lv_interface_event jai_ap_invoice_lines.interface_event%type;
2360 ln_regime_id number;
2361 -------------------------------------------------------------------------
2362 --Added by zhiwei for POT change bug#13023443 on 20110930 end
2363
2364 --Added by zhiwei for POT change bug#13435283 on 20111201 begin
2365 ----------------------------------------------------------------------------------
2366 Cursor get_interface_info(cn_invoice_id number, cn_invoice_line_number number)
2367 IS
2368 SELECT organization_id,location_id,interface_flag,interface_event
2369 FROM jai_ap_invoice_lines
2370 WHERE invoice_id = cn_invoice_id
2371 AND invoice_line_number = (SELECT parent_invoice_line_number
2372 FROM jai_ap_invoice_lines jail
2373 WHERE jail.invoice_line_number = cn_invoice_line_number
2374 AND jail.invoice_id = cn_invoice_id)
2375 AND parent_invoice_line_number IS NULL;
2376
2377 cursor get_regime_id
2378 is
2379 SELECT regime_id
2380 FROM JAI_RGM_DEFINITIONS
2381 WHERE regime_code = jai_constants.service_regime;
2382 ----------------------------------------------------------------------------------
2383 --Added by zhiwei for POT change bug#13435283 on 20111201 end
2384
2385 BEGIN
2386
2387 v_today := trunc(sysdate); -- File.Sql.35 by Brathod
2388
2389 /* Bug 5243532. Added by Lakshmi Gopalsami
2390 removed the cursor c_sob_of_ou and implemented using caching
2391 logic.
2392 */
2393 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
2394 (p_org_id => p_org_id );
2395 ln_sob_id := l_func_curr_det.ledger_id;
2396
2397
2398 IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'AAA Enter2 - Organization id:'||p_organization_id||'OU ID:'||p_org_id||',sob:'||ln_sob_id); END IF;/*Bug 5879769 bduvarag*/
2399
2400 -- this is required for to rollback the changes made in this procedure incase any unexpected error
2401 -- SAVEPOINT start_payments;
2402
2403 -- ~~~~~~~~~~ Payables Processing ~~~~~~~~~~
2404 -- Logic to Insert data into REFERENCEs Table
2405 -- if the invoice is cancelled, then no references are populated so that the lines are not processed against any
2406 -- Payements/Voids that are present for this invoice
2407 FOR ap_acc_dist IN c_ap_accounted_inv_dist( 'Purchase Invoices', p_trx_from_date, p_trx_to_date, ln_sob_id) LOOP/*Bug 5879769 bduvarag*/
2408
2409 IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'Enter3 - ap_event_id:'||ap_acc_dist.accounting_event_id); END IF;
2410
2411 --Bug 4991017. Added by Lakshmi gopalsami
2412 -- Removed the FOR.. LOOP dist.
2413
2414 IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'Enter4 - invid:'||ap_acc_dist.invoice_id
2415 ||', LineNum:'||ap_acc_dist.invoice_line_number ||',distid:'||ap_acc_dist.distribution_line_number); END IF;
2416
2417 -- Initialization Point
2418 ln_reference_id := null;
2419 ln_item_line_id := null;
2420 ln_item_id := null;
2421
2422 r_ref := null;
2423 r_tax_dist_dtl := null;
2424 r_parent_dist := null;
2425 r_parent_ref := null;
2426
2427 OPEN c_reference(jai_constants.source_ap, ap_acc_dist.invoice_id, ap_acc_dist.invoice_distribution_id); -- , r_tax_dist_dtl.tax_id);
2428 FETCH c_reference INTO r_ref;
2429 CLOSE c_reference;
2430
2431 -- following condition is satisfied if the invoice line is already inserted into REFERENCEs table
2432 IF r_ref.reference_id IS NOT NULL THEN
2433 IF p_debug = 'Y' THEN
2434 fnd_file.put_line(fnd_file.log, 'Enter5 - Return Ref NotNull');
2435 END IF;
2436 GOTO end_of_reference_insertion;
2437 END IF;
2438
2439 -- ~~~~~~~~~~~~~~~~~~~~~~~~~ POPULATION Logic for Data Entry into JAI_RGM_TRX_REFS ~~~~~~~~~~~~~~~~~~~~~~~~
2440 -- following condition is satisfied for REVERSAL lines
2441 IF ap_acc_dist.reversal_flag = 'Y' AND ap_acc_dist.parent_reversal_id IS NOT NULL THEN
2442 OPEN c_invoice_distribution(ap_acc_dist.parent_reversal_id);
2443 FETCH c_invoice_distribution INTO r_parent_dist;
2444 CLOSE c_invoice_distribution;
2445
2446 OPEN c_tax_dist_dtl(p_regime_id, r_parent_dist.invoice_id, r_parent_dist.invoice_distribution_id ); -- distribution_line_number );
2447 FETCH c_tax_dist_dtl INTO r_tax_dist_dtl;
2448 CLOSE c_tax_dist_dtl;
2449
2450 ELSIF NVL(ap_acc_dist.source,'$$$') = 'INDIA TAX INVOICE' THEN --'RECEIPT' THEN --Ramanand for bug#4388958
2451 /*
2452 || above elsif added by ssumaith - bug# 4284505
2453 || NVL(ap_acc_dist.source,'$$$') = 'INDIA TAX INVOICE' --'RECEIPT' - It means a third party invoice.is being processed. ----Ramanand for bug#4388958
2454
2455 || nvl(r_tax_dist_dtl.chk, 0) = 0 means that no records were found in the JAI_AP_MATCH_INV_TAXES table.
2456 || For third party invoices , there will be no records in the JAI_AP_MATCH_INV_TAXES table
2457 || For third party invoices , the tax details need to be picked up from the jai_rcv_tp_inv_details table.
2458 || It should be joined to the jai_rcv_tp_.invoices table based on the batch_invoice_id column and
2459 || we arrive at the correct batch_invoice_id based on the invoice_id link between the third party
2460 || invoice and jai_rcv_tp_invoices table.
2461 || Using this link, if the r_tax_dist_dtl is populated, it will take its normal course.
2462 */
2463
2464 --passed ap_acc_dist.invoice_distribution_id for bug#10085619
2465 OPEN c_tp_inv_details(p_regime_id , ap_acc_dist.invoice_id , ap_acc_dist.invoice_line_number, ap_acc_dist.invoice_distribution_id); /* INVOICE LINES UPTAKE distribution_line_number); */
2466 FETCH c_tp_inv_Details INTO r_tax_dist_dtl;
2467 CLOSE c_tp_inv_Details;
2468
2469 -- Normal Distribution and not a Reversal and not a third party distribution
2470 ELSE
2471 OPEN c_tax_dist_dtl(p_regime_id, ap_acc_dist.invoice_id, ap_acc_dist.invoice_distribution_id); -- AP INVOICE LINES UPTAKE ap_acc_dist.distribution_line_number); --
2472 FETCH c_tax_dist_dtl INTO r_tax_dist_dtl;
2473 CLOSE c_tax_dist_dtl;
2474
2475 END IF;
2476
2477 IF nvl(r_tax_dist_dtl.chk, 0) = 0 THEN
2478 IF p_debug = 'Y' THEN
2479 fnd_file.put_line(fnd_file.log, 'Enter6 - DistChk is 0');
2480 END IF;
2481
2482 GOTO end_of_reference_insertion;
2483 END IF;
2484
2485 IF r_tax_dist_dtl.recoverable_ptg = 0 THEN
2486 IF p_debug = 'Y' THEN
2487 fnd_file.put_line(fnd_file.log, 'Enter7 - recov_ptg is 0');
2488 END IF;
2489 FND_FILE.put_line( FND_FILE.log, 'Invoice_id, LineNum, DistNum->'||ap_acc_dist.invoice_id
2490 ||','||ap_acc_dist.invoice_line_number||','||ap_acc_dist.distribution_line_number||' is not Recoverable');
2491 GOTO end_of_reference_insertion;
2492 END IF;
2493
2494 IF r_tax_dist_dtl.parent_invoice_distribution_id IS NOT NULL THEN
2495 ln_item_line_id := r_tax_dist_dtl.parent_invoice_distribution_id;
2496 /* Bug 7172723. Added by Lakshmi Gopalsami
2497 * If it is third party invoice there is no reference item line.
2498 * and so the dist line itself is the parent.
2499 * assigning the invoice_distribution_id of ST tax itself
2500 */
2501 ELSIF NVL(ap_acc_dist.source,'$$$') = 'INDIA TAX INVOICE' THEN
2502 ln_item_line_id := ap_acc_dist.invoice_distribution_id;
2503
2504 --added the if conditions for bug#10085619/*added as per standalone update bug #8943349 **/
2505 IF r_tax_dist_dtl.chk = 2 then
2506 ln_item_line_id := r_tax_dist_dtl.invoice_line_number;
2507 end if;
2508 --end additions for bug#10085619
2509 ELSIF r_tax_dist_dtl.chk = 2 then --Added this elsif condition for bug#8943349 by JMEENA
2510
2511 ln_item_line_id := r_tax_dist_dtl.invoice_line_number;
2512
2513 ELSE
2514 ln_item_line_id := get_item_line_id(
2515 p_invoice_id => ap_acc_dist.invoice_id,
2516 p_po_distribution_id => ap_acc_dist.po_distribution_id,
2517 p_rcv_transaction_id => ap_acc_dist.rcv_transaction_id
2518 );
2519 END IF;
2520
2521 OPEN c_item_id(ap_acc_dist.po_distribution_id);
2522 FETCH c_item_id INTO ln_item_id;
2523 CLOSE c_item_id;
2524
2525 IF ap_acc_dist.parent_reversal_id is not null then /* condition introduced for AP LINES Uptake Project */
2526 OPEN c_reference(jai_constants.source_ap, ap_acc_dist.invoice_id, ap_acc_dist.parent_reversal_id); -- , r_tax_dist_dtl.tax_id);
2527 FETCH c_reference INTO r_parent_ref;
2528 CLOSE c_reference;
2529 END IF;
2530
2531 ln_recoverable_amount := ap_acc_dist.amount ; /* r_tax_dist_dtl.recoverable_ptg/100 commented for bug 7684820 */
2532 lv_process_flag := null;
2533
2534 savepoint start_of_ref;
2535
2536
2537 --Added by zhiwei for POT change Bug#13023443 begin
2538 -----------------------------------------------------------------------------------------------
2539 --For External Service Tax from Open API and GL >= POT , not populate repo, so ignore this kind Dist line.
2540
2541 --Get account date by cursor ap_acc_dist
2542
2543 OPEN get_accounting_date(ap_acc_dist.invoice_distribution_id);
2544 FETCH get_accounting_date INTO ld_gl_date, ln_po_distribution_id, ln_invoice_id, ln_invoice_line_number;
2545 CLOSE get_accounting_date;
2546
2547 --Added by zhiwei for Bug#13435283 on 20111201 begin
2548 -------------------------------------------------------------------------------------------------------
2549 open get_interface_info(ln_invoice_id,ln_invoice_line_number);
2550 fetch get_interface_info into l_organization_id,l_location_id,lv_interface_flag,lv_interface_event;
2551 close get_interface_info;
2552
2553 open get_regime_id;
2554 fetch get_regime_id into ln_regime_id;
2555 close get_regime_id;
2556 -------------------------------------------------------------------------------------------------------
2557 --Added by zhiwei for Bug#13435283 on 20111201 end
2558
2559 /*
2560 SELECT organization_id,location_id,interface_flag,interface_event
2561 into l_organization_id,l_location_id,lv_interface_flag,lv_interface_event
2562 FROM jai_ap_invoice_lines
2563 WHERE invoice_id = ln_invoice_id
2564 AND invoice_line_number = (SELECT parent_invoice_line_number
2565 FROM jai_ap_invoice_lines jail
2566 WHERE jail.invoice_line_number = ln_invoice_line_number
2567 AND jail.invoice_id = ln_invoice_id)
2568 AND parent_invoice_line_number IS NULL;
2569 */--Commented by Zhiwei.Hou for Bug#13435283.
2570
2571 --get_org_location(ln_invoice_id, ln_invoice_line_number, ln_po_distribution_id, l_organization_id, l_location_id);
2572 --Get POT date
2573 open c_get_st_accrual_date(l_organization_id, l_location_id);
2574 fetch c_get_st_accrual_date into ld_st_accrual_date;
2575 close c_get_st_accrual_date;
2576
2577 /*
2578 SELECT regime_id
2579 into ln_regime_id
2580 FROM JAI_RGM_DEFINITIONS
2581 WHERE regime_code = jai_constants.service_regime;
2582 */--Commented by Zhiwei.Hou for Bug#13435283.
2583
2584
2585 if nvl(ln_regime_id,-1) = p_regime_id
2586 and
2587 nvl(lv_interface_event,'###') = 'EXTERNAL'
2588 and
2589 ap_acc_dist.accounting_date >= ld_st_accrual_date
2590 then
2591 null;
2592 else
2593 -----------------------------------------------------------------------------------------------
2594 --Added by zhiwei for POT change Bug#13023443 end
2595
2596
2597
2598 jai_cmn_rgm_recording_pkg.insert_reference(
2599 p_reference_id => ln_reference_id, -- OUT Variable
2600 p_organization_id => p_organization_id,/*5694855*/
2601 p_source => jai_constants.source_ap,
2602 p_invoice_id => ap_acc_dist.invoice_id,
2603 p_line_id => ap_acc_dist.invoice_distribution_id,
2604 p_tax_type => r_tax_dist_dtl.tax_type,
2605 p_tax_id => r_tax_dist_dtl.tax_id,
2606 p_tax_rate => r_tax_dist_dtl.tax_rate,
2607 p_recoverable_ptg => r_tax_dist_dtl.recoverable_ptg,
2608 p_recoverable_amount => ln_recoverable_amount,
2609 p_party_type => jai_constants.party_type_vendor,
2610 p_party_id => ap_acc_dist.vendor_id,
2611 p_party_site_id => ap_acc_dist.vendor_site_id,
2612 p_tax_amount => nvl(ap_acc_dist.base_amount, ap_acc_dist.amount), /*Bug 12839287 - Tax Amount should hold Amount in Functional currency*/
2613 p_recovered_amount => 0,
2614 p_taxable_basis => r_tax_dist_dtl.base_amount, -- CHK << what amount i should populate >>
2615 p_item_line_id => ln_item_line_id,
2616 p_item_id => ln_item_id,
2617 p_trx_tax_amount => ap_acc_dist.amount,
2618 p_trx_currency => ap_acc_dist.invoice_currency_code,
2619 p_curr_conv_date => ap_acc_dist.exchange_date,
2620 p_curr_conv_rate => ap_acc_dist.exchange_rate,
2621 p_parent_reference_id => r_parent_ref.reference_id,
2622 p_reversal_flag => ap_acc_dist.reversal_flag,
2623 p_batch_id => p_batch_id,
2624 p_process_flag => lv_process_flag,
2625 p_process_message => lv_process_message
2626 );
2627
2628 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
2629 -- RAISE_APPLICATION_ERROR( -20201, p_process_flag||':'||p_process_message);
2630 -- ERROR RECORDING should be there for all errored records, so that we can code the processing in future. CHK
2631 ROLLBACK TO start_of_ref;
2632 p_process_flag := lv_process_flag;
2633 p_process_message := lv_process_message;
2634 END IF;
2635
2636 ln_uncommited_trxs := ln_uncommited_trxs + 1;
2637 IF ln_uncommited_trxs >= ln_commit_interval THEN
2638 COMMIT;
2639 ln_uncommited_trxs := 0;
2640 END IF;
2641 end if;--Added by zhiwei for POT change Bug#13023443 on 20110930
2642 IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'Enter8 - Inserted Reference:'||ln_reference_id); END IF;
2643
2644 <<end_of_reference_insertion>>
2645 NULL;
2646
2647 -- Bug 4991017. Added by Lakshmi Gopalsami.
2648 -- Removed the END LOOP as the two cursors has been merged.
2649
2650 END LOOP; -- ap_acc_dist for Operating Unit
2651
2652
2653 --Added by Qiong for Reverse Charge code port start
2654 jai_cmn_reverse_charge_pkg.process_reverse_payment(
2655 p_org_id => p_org_id,
2656 pn_batch_id => p_batch_id,
2657 pn_regime_id => p_regime_id,
2658 p_trx_from_date => p_trx_from_date,
2659 p_trx_to_date => p_trx_to_date,
2660 pn_organization_id => p_organization_id,
2661 p_debug => p_debug,
2662 p_process_flag => lv_process_flag,
2663 p_process_message => lv_process_message);
2664 --Added by Qiong for Reverse Charge code port end
2665 --Add by Chong for bug#13259755 on 27-OCT-2011 start
2666 process_claim(
2667 pn_regime_id => p_regime_id,
2668 pn_organization_type => p_organization_type,
2669 pd_from_date => p_trx_from_date,
2670 pd_to_date => p_trx_to_date,
2671 pn_organization_id => p_organization_id);
2672 --Add by Chong for bug#13259755 on 27-OCT-2011 end
2673 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, begin
2674 ----------------------------------------------------------------------------------------------
2675 populate_repository(
2676 p_batch_id => p_batch_id,
2677 p_regime_id => p_regime_id,
2678 p_organization_type => p_organization_type,
2679 p_from_date => p_trx_from_date,
2680 p_to_date => p_trx_to_date,
2681 p_org_id => p_org_id,
2682 p_organization_id => p_organization_id,
2683 p_process_flag => lv_process_flag,
2684 p_process_message => lv_process_message);
2685
2686 --Added by Qiong for reverse charge code port begin
2687 jai_cmn_reverse_charge_pkg.process_settlement_repository (
2688 p_batch_id => p_batch_id,
2689 p_regime_id => p_regime_id,
2690 p_organization_type => p_organization_type,
2691 p_from_date => p_trx_from_date,
2692 p_to_date => p_trx_to_date,
2693 p_org_id => p_org_id,
2694 p_organization_id => p_organization_id,
2695 p_process_flag => lv_process_flag,
2696 p_process_message => lv_process_message);
2697 --Added by Qiong for reverse charge code port end
2698
2699 /*Delete by Chong for bug#13259755 on 27-OCT-2011 start
2700 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
2701 process_claim(
2702 pn_regime_id => p_regime_id,
2703 pn_organization_type => p_organization_type,
2704 pd_from_date => p_trx_from_date,
2705 pd_to_date => p_trx_to_date,
2706 pn_organization_id => p_organization_id);
2707 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
2708 Delete by Chong for bug#13259755 on 27-OCT-2011 end*/
2709
2710 ----------------------------------------------------------------------------------------------
2711 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, end
2712
2713 -- Logic to Make Register Entry for the Invoice Distributions that are populated into REFERENCES table and which are PAID
2714 -- Prior to the start date of this concurrent program. This is because localization only considers invoices that are accounted
2715 FOR invo IN c_batch_references(p_batch_id, jai_constants.source_ap) LOOP
2716
2717 -- Logic to Process the PAST DATED PAYMENTS that are not processed due to Invoice Accounting did not happen
2718 FOR inv_payment IN c_previous_payments_of_inv(invo.invoice_id, p_trx_from_date) LOOP
2719
2720 FOR dist IN c_invoice_batch_refs(jai_constants.source_ap, p_batch_id, invo.invoice_id) LOOP
2721
2722 lv_process_flag := null;
2723 -- Add by Xiao for POT change, reg bug#12533434 on 29-May-2011, begin
2724 ----------------------------------------------------------------------------------------------
2725 OPEN get_accounting_date(dist.line_id);
2726 FETCH get_accounting_date INTO ld_gl_date, ln_po_distribution_id, ln_invoice_id, ln_invoice_line_number;
2727 CLOSE get_accounting_date;
2728 /*Bug 12805386 - Start*/
2729 get_org_location(ln_invoice_id, ln_invoice_line_number, ln_po_distribution_id, l_organization_id, l_location_id);
2730
2731 OPEN c_get_st_accrual_date(l_organization_id, l_location_id);
2732 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
2733 CLOSE c_get_st_accrual_date;
2734 /*Bug 12805386 - End*/
2735
2736 IF ld_gl_date < ld_st_accrual_date OR ld_st_accrual_date IS NULL THEN
2737 --ADDED OR ld_st_accrual_date IS NULL THEN for bug#13323182
2738 ----------------------------------------------------------------------------------------------
2739 -- Add by Xiao for POT change, reg bug#12533434 on 29-May-2011, end
2740
2741 SAVEPOINT process_payment;
2742
2743 process_payment(
2744 p_batch_id => p_batch_id,
2745 p_regime_id => p_regime_id,
2746 p_org_id => inv_payment.org_id,
2747 p_source => jai_constants.source_ap,
2748 p_payment_table_name => jai_constants.ap_payments,
2749 p_payment_document_id => inv_payment.invoice_payment_id,
2750 p_invoice_id => dist.invoice_id,
2751 p_inv_dist_id => dist.line_id,
2752 p_inv_accounting_chk_done => jai_constants.yes,
2753 p_process_flag => lv_process_flag,
2754 p_process_message => lv_process_message
2755 );
2756
2757
2758 -- "FP" Means means future payment and it is not yet matured, so
2759 --IF p_process_flag = 'FP' THEN
2760 --ELS
2761 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
2762 ROLLBACK TO process_payment;
2763 --retcode := jai_constants.request_warning;
2764 p_process_flag := lv_process_flag;
2765 p_process_message := lv_process_message;
2766 END IF;
2767
2768 ln_uncommited_trxs := ln_uncommited_trxs + 1;
2769 IF ln_uncommited_trxs >= ln_commit_interval THEN
2770 COMMIT;
2771 ln_uncommited_trxs := 0;
2772 END IF;
2773
2774 END IF; --Add by Xiao for POT change, reg bug#12533434
2775
2776 END LOOP; -- invoice distributions
2777
2778 END LOOP; -- invoice payments
2779
2780 -- Logic to Process the PAST DATED PREPAYMENTS that are not processed due to Invoice Accounting did not happen
2781 FOR pp IN c_period_payments( p_trx_from_date, p_trx_to_date) LOOP
2782
2783 FOR dist IN c_invoice_batch_refs(jai_constants.source_ap, p_batch_id, invo.invoice_id) LOOP
2784
2785 lv_process_flag := null;
2786
2787 SAVEPOINT process_prepayment;
2788 /*Bug 12805386 - Start*/
2789 get_org_location(pp.invoice_id, pp.invoice_line_number, pp.po_distribution_id, l_organization_id, l_location_id);
2790
2791 OPEN c_get_st_accrual_date(l_organization_id, l_location_id);
2792 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
2793 CLOSE c_get_st_accrual_date;
2794 /*Bug 12805386 - End*/
2795 IF pp.accounting_date < ld_st_accrual_date OR ld_st_accrual_date IS NULL THEN --Xiao for POT change, reg bug#12533434 on 10-May-2011
2796 --ADDED OR ld_st_accrual_date IS NULL THEN for bug#13323182
2797 process_payment(
2798 p_batch_id => p_batch_id,
2799 p_regime_id => p_regime_id,
2800 p_org_id => pp.org_id,
2801 p_source => jai_constants.source_ap,
2802 p_payment_table_name => jai_constants.ap_prepayments,
2803 p_payment_document_id => pp.invoice_distribution_id,
2804 p_invoice_id => dist.invoice_id,
2805 p_inv_dist_id => dist.line_id,
2806 p_inv_accounting_chk_done => jai_constants.yes,
2807 p_process_flag => lv_process_flag,
2808 p_process_message => lv_process_message);
2809 END IF; --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011
2810
2811 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
2812 ROLLBACK TO process_prepayment;
2813 -- retcode := jai_constants.request_warning;
2814 p_process_flag := lv_process_flag;
2815 p_process_message := lv_process_message;
2816 END IF;
2817
2818 ln_uncommited_trxs := ln_uncommited_trxs + 1;
2819 IF ln_uncommited_trxs >= ln_commit_interval THEN
2820 COMMIT;
2821 ln_uncommited_trxs := 0;
2822 END IF;
2823
2824 END LOOP; -- invoice distributions
2825
2826 END LOOP; -- invoice prepayments
2827
2828 END LOOP; -- batch_references
2829
2830 -- Logic to Process Payments that fall for the specified period
2831 /*
2832 ||Cursor for Loops c_period_payments and c_invoice_distributions merged into c_period_payments by aiyer for the bug 4947102
2833 || This has been done to derive performance improvement
2834 || SQL ID 14828450
2835 */
2836 FOR inv_payment IN c_period_payments( p_trx_from_date, p_trx_to_date) LOOP
2837 lv_process_flag := null;
2838
2839 IF g_debug='Y' THEN fnd_file.put_line(fnd_file.log,'PeriodPay. Inv,DistId:'||inv_payment.invoice_id||','||inv_payment.invoice_distribution_id); END IF;
2840
2841 /*Bug 12805386 - Start*/
2842 get_org_location(inv_payment.invoice_id, inv_payment.invoice_line_number, inv_payment.po_distribution_id, l_organization_id, l_location_id);
2843
2844 OPEN c_get_st_accrual_date(l_organization_id, l_location_id);
2845 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
2846 CLOSE c_get_st_accrual_date;
2847 /*Bug 12805386 - End*/
2848 -- Add by Xiao for POT change, reg bug#12533434 on 29-May-2011, begin
2849 ----------------------------------------------------------------------------------------------
2850 IF inv_payment.accounting_date < ld_st_accrual_date OR ld_st_accrual_date IS NULL THEN --Xiao for POT change, reg bug#12533434 on 29-May-2011
2851 --ADDED OR ld_st_accrual_date IS NULL THEN for bug#13323182
2852 ----------------------------------------------------------------------------------------------
2853 -- Add by Xiao for POT change, reg bug#12533434 on 29-May-2011, end
2854 SAVEPOINT process_payment;
2855
2856 process_payment(
2857 p_batch_id => p_batch_id,
2858 p_regime_id => p_regime_id,
2859 p_org_id => inv_payment.org_id,
2860 p_source => jai_constants.source_ap,
2861 p_payment_table_name => jai_constants.ap_payments,
2862 p_payment_document_id => inv_payment.invoice_payment_id,
2863 p_invoice_id => inv_payment.invoice_id,
2864 p_inv_dist_id => inv_payment.invoice_distribution_id,
2865 p_inv_accounting_chk_done => jai_constants.no,
2866 p_process_flag => lv_process_flag,
2867 p_process_message => lv_process_message
2868 );
2869
2870 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
2871 ROLLBACK TO process_payment;
2872 -- retcode := jai_constants.request_warning;
2873 p_process_flag := lv_process_flag;
2874 p_process_message := lv_process_message;
2875 END IF;
2876
2877 ln_uncommited_trxs := ln_uncommited_trxs + 1;
2878 IF ln_uncommited_trxs >= ln_commit_interval THEN
2879 COMMIT;
2880 ln_uncommited_trxs := 0;
2881 END IF;
2882 END IF; --Add by Xiao for POT change, reg bug#12533434
2883
2884 END LOOP;
2885
2886 -- Logic to Process Prepayment Applications onto standard invoices that fall in the processing period
2887 FOR pp IN c_prepayment_applications(p_trx_from_date, p_trx_to_date) LOOP
2888 --11666653
2889 IF p_debug = 'Y' THEN
2890 fnd_file.put_line(fnd_file.log, 'Enter FOR pp IN c_prepayment_applications(p_trx_from_date, p_trx_to_date) LOOP pp.invoice_id'||pp.invoice_id);
2891 END IF;
2892
2893 FOR dist IN c_invoice_distributions(pp.invoice_id) LOOP
2894 --11666653
2895 IF p_debug = 'Y' THEN
2896 fnd_file.put_line(fnd_file.log, 'Enter FOR dist IN c_invoice_distributions(pp.invoice_id) LOOP ');
2897 END IF;
2898 lv_process_flag := null;
2899 /*Bug 12805386 - Start*/
2900 get_org_location(pp.invoice_id, pp.invoice_line_number, pp.po_distribution_id, l_organization_id, l_location_id);
2901
2902 OPEN c_get_st_accrual_date(l_organization_id, l_location_id);
2903 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
2904 CLOSE c_get_st_accrual_date;
2905 /*Bug 12805386 - End*/
2906 -- Add by Xiao for POT change, reg bug#12533434 on 29-May-2011, begin
2907 ----------------------------------------------------------------------------------------------
2908 IF dist.accounting_date < ld_st_accrual_date OR ld_st_accrual_date IS NULL THEN
2909 --ADDED OR ld_st_accrual_date IS NULL THEN for bug#13323182
2910 ----------------------------------------------------------------------------------------------
2911 -- Add by Xiao for POT change, reg bug#12533434 on 29-May-2011, end
2912 SAVEPOINT process_prepayment;
2913 --11666653
2914 IF p_debug = 'Y' THEN
2915 fnd_file.put_line(fnd_file.log, 'before call to process_payment dist.invoice_id '||dist.invoice_id||' dist.invoice_distribution_id '||dist.invoice_distribution_id);
2916 fnd_file.put_line(fnd_file.log, 'before call to process_payment pp.invoice_distribution_id '|| pp.invoice_distribution_id);
2917 END IF;
2918 process_payment(
2919 p_batch_id => p_batch_id,
2920 p_regime_id => p_regime_id,
2921 p_org_id => pp.org_id,
2922 p_source => jai_constants.source_ap,
2923 p_payment_table_name => jai_constants.ap_prepayments,
2924 p_payment_document_id => pp.invoice_distribution_id,
2925 p_invoice_id => dist.invoice_id,
2926 p_inv_dist_id => dist.invoice_distribution_id,
2927 p_inv_accounting_chk_done => jai_constants.no,
2928 p_process_flag => lv_process_flag,
2929 p_process_message => lv_process_message
2930 );
2931 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
2932 ROLLBACK TO process_prepayment;
2933 -- retcode := jai_constants.request_warning;
2934 p_process_flag := lv_process_flag;
2935 p_process_message := lv_process_message;
2936 END IF;
2937
2938 ln_uncommited_trxs := ln_uncommited_trxs + 1;
2939 IF ln_uncommited_trxs >= ln_commit_interval THEN
2940 COMMIT;
2941 ln_uncommited_trxs := 0;
2942 END IF;
2943 END IF;-- Add by Xiao for POT change, reg bug#12533434 on 29-May-2011, end
2944 END LOOP;
2945
2946 END LOOP; -- Prepayments
2947
2948 EXCEPTION
2949 WHEN OTHERS THEN
2950 ROLLBACK;
2951 -- retcode := jai_constants.request_error;
2952 -- errbuf := 'Unexpected Error Occured:'||SQLERRM;
2953 p_process_flag := jai_constants.unexpected_error;
2954 p_process_message := 'Unexpected Error Occured in Process_Payments:'||SQLERRM;
2955 FND_FILE.put_line( fnd_file.log, 'Unexpected Error Occured:'||p_process_message);
2956
2957 END process_payments;
2958
2959 /*
2960 CREATED BY : ssumaith
2961 CREATED DATE : 15-MAR-2005
2962 ENHANCEMENT BUG : 4245053
2963 PURPOSE : wrapper program to interpret the input parameters and suitably call program to
2964 generate vat imvoice number and pass accounting during shipment
2965 CALLED FROM : Concurrent program JAIVATP
2966
2967 */
2968
2969
2970 -- foll function created by kunkumar - for seperate vat invoice num for unreg dealers - bug# 5233925
2971
2972
2973 FUNCTION check_reg_dealer ( pn_customer_id NUMBER ,
2974 pn_site_use_id NUMBER ) return boolean
2975
2976 IS
2977 ln_address_id NUMBER;
2978 lv_regno JAI_CMN_CUS_ADDRESSES.vat_Reg_no%type;
2979
2980
2981 CURSOR c_get_address is
2982 SELECT hzcas.cust_acct_site_id
2983 FROM hz_cust_site_uses_all hzcsu ,
2984 hz_cust_acct_sites_all hzcas
2985 WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
2986 AND hzcsu.site_use_id = pn_site_use_id
2987 AND hzcas.cust_account_id = pn_customer_id ;
2988
2989 CURSOR c_regno (pn_address_id NUMBER) IS
2990 SELECT vat_Reg_no
2991 FROM JAI_CMN_CUS_ADDRESSES
2992 WHERE customer_id = pn_customer_id
2993 AND address_id = pn_address_id;
2994
2995 BEGIN
2996
2997 open c_get_address;
2998 fetch c_get_address into ln_address_id;
2999 close c_get_address;
3000 IF ln_address_id IS NOT NULL THEN
3001
3002 open c_regno (ln_address_id);
3003 fetch c_regno into lv_regno;
3004 close c_regno;
3005 END IF;
3006
3007 IF lv_regno IS NULL THEN
3008 return (false);
3009 ELSE
3010 return (true);
3011 END IF;
3012
3013
3014 END check_reg_dealer;
3015
3016 /*
3017 || kunkumar - for seperate vat invoice num for unreg dealers - bug# 5233925
3018 */
3019
3020
3021
3022
3023 PROCEDURE process (
3024 retcode OUT NOCOPY VARCHAR2,
3025 errbuf OUT NOCOPY VARCHAR2,
3026 p_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE,
3027 p_registration_num JAI_RGM_TRX_RECORDS.REGIME_PRIMARY_REGNO%TYPE,
3028 p_organization_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE,
3029 p_location_id JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE,
3030 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3031 p_order_number_from OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE,
3032 p_order_number_to OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE,
3033 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
3034 p_delivery_id_from JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE,
3035 p_delivery_id_to JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE,
3036 pv_delivery_date_from VARCHAR2, --DATE, Harshita for Bug 4918870
3037 pv_delivery_date_to VARCHAR2, --DATE, Harshita for Bug 4918870
3038 p_process_action VARCHAR2,
3039 p_single_invoice_num VARCHAR2,
3040 p_override_invoice_date VARCHAR2, /* aiyer for the bug 5369250 */
3041 p_debug VARCHAR2
3042 -- code ported from BL12.1.3 by zhiwei.xin on 25-MAR-2013 begin
3043 ,p_called_from VARCHAR2 DEFAULT NULL -- added by zhiwei.xin on 8-NOV-2012 for VAT Invoice Generation/Accounting
3044 -- code ported from BL12.1.3 by zhiwei.xin on 25-MAR-2013 end.
3045 )
3046 IS
3047 /*************************************************************************************************************************************
3048 Purpose:-
3049 || It processes single / multiple deliveries based on the parameters entered.
3050 || In a loop , each delivery is processed and two tasks are done based upon the p_process_action parameter
3051 || If the p_process_action = 'Generate Invoice Number' or p_process_action = 'All' then the subsection a) happens.
3052 || If the p_process_action = 'Process Accounting' or p_process_action = 'All' then the subsection b) happens.
3053 ||
3054 || a) make a call to an api to generate vat invoice number depending on various settings
3055 || a.1) If the Parameter p_single_invoice_num is set to 'Y' , then for all the deliveries of a cust / cust site a single
3056 || invoice number is generated. The call happen to the procedure to generate the vat invoice number just once
3057 || and the same value retained for this record set of same cust / cust site
3058 || a.2) If the parameter p_single_invoice_num is set to 'N' , then for each delivery a seperate vat invoice number
3059 || will be generated.
3060 || If the generate vat invoice number api returns error , then the subsection b will not be processed and the delivery
3061 || will be flagged as errored in the table JAI_RGM_INVOICE_GEN_T for the delivery_id
3062 ||
3063 || b) make a call to the api to process accounting
3064 ||
3065 || b.1 If it returns success then if the p_process_action = 'All' then flag both the fields VAT_INV_GEN_STATUS
3066 || and VAT_ACCT_STATUS are to be set to completed - 'C'
3067 || commit the delivery and continue with the next delivery
3068 ||
3069 || If it returns error (either expected error or unexpected error) and if the p_process_action = 'All' then
3070 || flag the fields VAT_INV_GEN_STATUS and VAT_ACCT_STATUS as - Errored 'E'
3071 || Rollback the delivery and continue with the next delivery.
3072 ||
3073 Change History -
3074 1. 29/07/2005 Aiyer - bug# 4523205 - File version 120.2 - (R12 Forward Porting FROM 11.5 bugs 4348774, 4357984)
3075
3076 Issues :
3077 -------
3078 1. The concurrent program is picking up all records irrespective of the registration number passed
3079 in the parameter.
3080 2. (Logged in bug 4534166) Returning clause is not required, hence needs to be removed 4357984.
3081
3082 Fix :
3083 -----
3084 1.The issue has been fixed by adding the p_registration_num and p_Regime_id in the where clause.
3085 2.As the returning clause was not required and hence was removed. Also added the fnd_file log in
3086 the exception section of the procedure.
3087
3088 Dependency due to this bug:-
3089 None
3090 2. 05-Jul-2006 Aiyer for the bug 5369250, Version 120.7
3091 Issue:-
3092 --------
3093 The concurrent failes with the following error :-
3094 "FDPSTP failed due to ORA-01861: literal does not match format string ORA-06512: at line 1 "
3095
3096 Reason:-
3097 ---------
3098 The procedure PROCESS had a parameters p_override_invoice_date of type date , however the concurrent program
3099 passes it in the canonical format and hence the failure.
3100
3101 Fix:-
3102 -----------
3103 Modified the procedure update_excise_invoice_no.
3104 Changed the datatype of p_override_invoice_date from date to varchar2 as this parameter.
3105 Also added the new parameter ld_override_invoice_date . The value in p_override_invoice_date would be converted to date format and
3106 stored in the local variable ld_override_invoice_date.
3107
3108 Dependency due to this fix:-
3109 None
3110
3111 3. 3-Feb-2007 srjayara for bug 4702156, file version 120.8
3112 Forward porting for 11i bug#4542996
3113
3114 Issue:-
3115 --------
3116 VAT invoice number and accounting was not happening for all the delivery lines in a delivery.
3117
3118 Fix:-
3119 ------
3120 Possible reason identified is that the all lines are not inventory interfaced at the same time and
3121 hence only those lines which are inventory interfaced are considered at the time vat processing concurrent
3122 runs.
3123 Added a check that only if all the delivery details are inventory interfaced , the delivery needs to be considered.
3124
3125 4. 4-jun-2007 ssumaith - bug#6109941 -
3126 The Service tax by IO code was incorrectly forward ported to R12. There were some code missing and operating unit was being passed instead of inventory org. Such code has been corrected.
3127
3128
3129 5 07-jun-2007 ssumaith - bug# 6109941 - divisor by zero error was coming . this has been resolved by checking
3130 for zero divides before the divide is done.
3131
3132 6 25-jun-2007 ssumaith - bug#6147385 - when all delivery details in a delivery are not interfaced trip stopped
3133 then, the program was returning instead of processing the next delivery.
3134 It was because of a return statement, instead added the code to process the next delivery and increment the failure counter.
3135
3136 Adde the nvl condition in the where clauseto use the table's registration number its passed as null
3137 11. 12-Jul-2007 CSahoo for bug#6176277, File Version 120.20
3138 assigned the variable ln_excise_invoice_not_done to NULL before opening the cursor.
3139
3140 12. 13-jul-2007 ssumaith - bug# 6176277 - The variable - lv_inv_gen_process_flag was not re-initialised
3141 re-initialised the variables - lv_inv_gen_process_flag , lv_inv_gen_process_message to NULL
3142
3143 13. 04-JUN-2009 JMEENA for bug#8574533
3144 Reset the variable ln_interface_status to zero before fetching the value from cursor c_check_interface_status
3145
3146 14. 07-Jul-2009 Bug 7347127 File version 120.7.12000000.14/120.27.12010000.8/120.35
3147 Modified the cursor c_ap_accounted_inv_dis so that it may use the index on accounting_date
3148 column if required. This is the forward port of 11i bug 7280631.
3149
3150 15. 02-Apr-2010 Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement)
3151 Issue: currently, procedure 'process' only handles shippable items
3152 Fix: logic in procedure 'process' should be modified to process both shippable and
3153 non-shippable lines.
3154 16. 28-Apr-2010 Allen Yang for bug 9666476
3155 In procedure 'process':
3156 1) added 'NULLS FIRST' into Order By clause of sql_stmt_all
3157 to ensure shippable items are always processed before non-shippable items.
3158 2) removed order_number from Order By clause of sql_stmt_shippable
3159 17. 13-May-2010 Allen Yang for bug 9709477
3160 1). added warning message when flags Same as Excise and Generate Single Invoice are both Y.
3161 18. 03-Jun-2010 Allen Yang for bug 9737119
3162 Issue: TST1213.XB1.QA.EXECPT DIAGNOSTICS,WARNING MESSAGE SHOULD ALSO BE SEEN IN LOG
3163 Fix: In procedure 'process', added logic to put message lv_same_as_excise_conf_warning to Log.
3164 **************************************************************************************************************************************/
3165
3166 lv_acct_process_flag VARCHAR2(10);
3167 lv_inv_gen_process_flag VARCHAR2(10);
3168 lv_inv_gen_process_message VARCHAR2(1996);
3169 lv_acct_process_message VARCHAR2(1996);
3170 lv_invoice_generated VARCHAR2(100);
3171 lv_vat_invoice_number VARCHAR2(100);
3172 ln_failure_delivery_ctr NUMBER;
3173 ln_success_delivery_Ctr NUMBER;
3174 ln_regime_id NUMBER;
3175 lv_debug VARCHAR2(5); -- := jai_constants.no /* This should be either 'Y' or 'N' */ File.Sql.35 by Brathod
3176 ln_order_type_id JAI_OM_WSH_LINES_ALL.ORDER_TYPE_ID%TYPE;
3177 lv_inv_num_already_generated VARCHAR2(10); --:= jai_constants.value_false File.Sql.35 by Brathod
3178 ln_batch_id NUMBER;
3179 lv_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
3180 ln_current_party_id NUMBER;
3181 ln_current_party_site_id NUMBER;
3182 lv_party_has_changed VARCHAR2(10);
3183 ln_conc_progam_id NUMBER;
3184 ln_conc_request_id NUMBER;
3185 ln_conc_prog_appl_id NUMBER;
3186 lv_Same_invoice_no VARCHAR2(100);
3187 lv_excise_invoice_no JAI_OM_WSH_LINES_ALL.EXCISE_INVOICE_NO%TYPE;
3188 ld_excise_invoice_date JAI_OM_WSH_LINES_ALL.EXCISE_INVOICE_DATE%TYPE;
3189 lb_completion_status BOOLEAN;
3190 ld_override_invoice_date DATE; /* aiyer for the bug 5369250 */
3191 lv_doc_type_class varchar2(2); /*kunkumar for bug #5233925*/
3192
3193 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3194 lv_SQLStmt VARCHAR2(2000);
3195 v_main_rec_cur MainRec_Cur;
3196 mainrec MainRecord;
3197 ln_current_order_number NUMBER;
3198 lv_order_has_changed VARCHAR2(10);
3199 lv_p_source VARCHAR2(30);
3200
3201 lv_p_registration_num_str VARCHAR2(200);
3202
3203 sql_stmt_shippable VARCHAR2(2000);
3204 sql_stmt_all VARCHAR2(2000);
3205 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
3206
3207 -- added by Allen Yang for bug 9709477 13-May-2010, begin
3208 lv_same_as_excise_conf_warning VARCHAR2(2000);
3209 -- added by Allen Yang for bug 9709477 13-May-2010, end
3210
3211 CURSOR c_regime_cur(cp_trx_Date DATE) IS
3212 SELECT regime_id
3213 FROM JAI_RGM_DEFINITIONS
3214 WHERE regime_code = jai_constants.vat_regime;
3215
3216 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3217 CURSOR c_shipment_info(cp_Delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE
3218 , cp_order_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE)
3219 IS
3220 SELECT order_type_id , excise_invoice_no
3221 FROM JAI_OM_WSH_LINES_ALL
3222 --WHERE delivery_id = cp_delivery_id;
3223 WHERE delivery_id = cp_delivery_id
3224 OR (delivery_id IS NULL AND order_line_id = cp_order_line_id);
3225 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
3226
3227 CURSOR c_same_inv_no(cp_organization_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE , cp_location_id JAI_OM_WSH_LINES_ALL.location_id%TYPE ) IS
3228 SELECT attribute_Value
3229 FROM JAI_RGM_ORG_REGNS_V
3230 WHERE regime_id = p_regime_id
3231 AND attribute_type_code = jai_constants.regn_type_others
3232 AND attribute_code = jai_constants.attr_code_same_inv_no
3233 AND organization_id = cp_organization_id
3234 AND location_id = cp_location_id;
3235
3236 CURSOR c_excise_invoice_not_done ( cp_Delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE)IS
3237 SELECT 1
3238 FROM JAI_OM_OE_GEN_TAXINV_T
3239 WHERE delivery_id = cp_delivery_id;
3240
3241 ln_excise_invoice_not_done NUMBER;
3242
3243 /*srjayara for bug 4702156*/
3244
3245 /*
3246 || The following cursor is added to check that all the delivery lines in the delivery are inventory interfaced
3247 */
3248 CURSOR c_check_interface_status (cp_delivery_id NUMBER) IS
3249 SELECT 1
3250 FROM
3251 wsh_delivery_details wdd ,
3252 wsh_new_deliveries wnd ,
3253 wsh_delivery_assignments wda
3254 WHERE
3255 wdd.delivery_detail_id = wda.delivery_detail_id AND
3256 wda.Delivery_Id = wnd.Delivery_Id AND
3257 wnd.Delivery_Id = cp_delivery_id AND
3258 wdd.source_code = 'OE' AND
3259 NVL(wdd.inv_interfaced_flag,'N') <> 'Y';
3260
3261 /*Bug 6031031 - Start*/
3262 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3263 CURSOR c_vat_inv_gen_status (cp_delivery_id jai_rgm_invoice_gen_t.delivery_id%type
3264 ,cp_order_line_id jai_rgm_invoice_gen_t.order_line_id%type)
3265 IS
3266 SELECT vat_inv_gen_status
3267 FROM JAI_RGM_INVOICE_GEN_T
3268 --WHERE delivery_id = cp_delivery_id ;
3269 WHERE delivery_id = NVL(cp_delivery_id, -1)
3270 OR order_line_id = NVL(cp_order_line_id, -1);
3271 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
3272 lv_vat_inv_gen_status VARCHAR2(1);
3273 /*Bug 6031031 - End*/
3274
3275 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3276 CURSOR c_get_excise_from_shippable (cp_order_number jai_rgm_invoice_gen_t.order_number%TYPE)
3277 IS
3278 SELECT jowla.excise_invoice_no
3279 ,jowla.excise_invoice_date
3280 FROM JAI_RGM_INVOICE_GEN_T jrigt
3281 , JAI_OM_WSH_LINES_ALL jowla
3282 WHERE jrigt.program_id = ln_conc_progam_id
3283 AND jrigt.delivery_id = jowla.delivery_id
3284 AND jowla.excise_invoice_no IS NOT NULL
3285 AND EXISTS (SELECT 1
3286 FROM WSH_DELIVERY_DETAILS wdd
3287 ,WSH_DELIVERY_ASSIGNMENTS wda
3288 ,OE_ORDER_HEADERS_ALL ooha
3289 WHERE ooha.order_number = cp_order_number
3290 AND ooha.header_id = wdd.source_header_id
3291 AND wda.delivery_detail_id = wdd.delivery_detail_id
3292 AND wda.delivery_id = jrigt.delivery_id)
3293 AND rownum = 1;
3294 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
3295
3296 ln_interface_status NUMBER;
3297
3298 /*end bug 4702156*/
3299
3300 -- Harshita for Bug 4918870
3301 p_delivery_date_from DATE DEFAULT fnd_date.canonical_to_date(pv_delivery_date_from);
3302 p_delivery_date_to DATE DEFAULT fnd_date.canonical_to_date(pv_delivery_date_to);
3303
3304 BEGIN
3305 /*
3306 ||aiyer for the bug 5369250
3307 ||convert from canonical to date format
3308 */
3309 ld_override_invoice_date := fnd_date.canonical_to_date(p_override_invoice_date);
3310 lv_inv_num_already_generated := jai_constants.value_false; -- File.Sql.35 by Brathod
3311 lv_debug := NVL(P_DEBUG,jai_constants.no);
3312 ln_current_party_id := -9999;
3313 ln_current_party_site_id := -9999;
3314 -- added by Allen Yang for for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3315 ln_current_order_number := -9999;
3316 ln_regime_id := p_regime_id ;
3317 -- added by Allen Yang for for bug 9485355 (12.1.3 non-shippable Enhancement), end
3318 IF lv_debug = 'Y' THEN
3319 Fnd_File.PUT_LINE(Fnd_File.LOG, ' 1. Entered in the proc with parameters :');
3320 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_REGIME_ID :' || P_REGIME_ID);
3321 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_REGISTRATION_NUM :' || P_REGISTRATION_NUM);
3322 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_ORGANIZATION_ID :' || P_ORGANIZATION_ID);
3323 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_LOCATION_ID :' || P_LOCATION_ID);
3324 -- added by Allen Yang for for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3325 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_ORDER_NUMBER_FROM :' || P_ORDER_NUMBER_FROM);
3326 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_ORDER_NUMBER_TO :' || P_ORDER_NUMBER_TO);
3327 -- added by Allen Yang for for bug 9485355 (12.1.3 non-shippable Enhancement), end
3328 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_ID_FROM :' || P_DELIVERY_ID_FROM);
3329 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_ID_TO :' || P_DELIVERY_ID_TO);
3330 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_DATE_FROM :' || P_DELIVERY_DATE_FROM);
3331 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_DATE_TO : ' || P_DELIVERY_DATE_TO);
3332 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_PROCESS_ACTION :' || P_PROCESS_ACTION);
3333 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_SINGLE_INVOICE_NUM :' || P_SINGLE_INVOICE_NUM);
3334 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_OVERRIDE_INVOICE_DATE :' ||P_OVERRIDE_INVOICE_DATE);
3335 END IF;
3336 ln_conc_progam_id := FND_GLOBAL.conc_program_id;
3337 ln_conc_request_id := FND_GLOBAL.conc_request_id;
3338 ln_conc_prog_appl_id := FND_GLOBAL.prog_appl_id;
3339 lv_inv_gen_process_flag := jai_constants.successful;
3340 lv_acct_process_flag := jai_constants.successful;
3341 lv_inv_gen_process_message := NULL;
3342 lv_acct_process_message := NULL;
3343 ln_batch_id := ln_conc_request_id;
3344
3345 ln_failure_delivery_ctr :=0;
3346 ln_success_delivery_Ctr :=0;
3347 ln_interface_status :=0; /*added by srjayara for bug 4702156*/
3348
3349 IF P_PROCESS_ACTION IS NULL THEN
3350 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ P_PROCESS_ACTION parameter IS NULL Hence returning +++ ' );
3351 RETURN;
3352 END IF;
3353
3354 -- modified by Allen Yang for for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3355
3356 /* only for the below two cases, non-shippable items will be ignored and don't be
3357 processed. So different SQL queries are used here.
3358 1. p_delivery_id_from and p_delivery_id_to are both NOT NULL
3359 2. p_delivery_id_from IS NOT NULL OR p_delivery_id_to IS NOT NULL and
3360 p_order_number_from/p_order_number_to are both NULL.
3361 */
3362 IF p_registration_num IS NULL
3363 THEN
3364 lv_p_registration_num_str := 'NULL';
3365 ELSE
3366 lv_p_registration_num_str := '''' || p_registration_num || '''';
3367 END IF; -- IF p_registration_num IS NULL
3368
3369 sql_stmt_shippable
3370 := 'SELECT delivery_id , delivery_date , organization_id , location_id , vat_invoice_no, '||
3371 'party_id , party_site_id , party_type , vat_inv_gen_status , vat_acct_status, '||
3372 'order_line_id, order_number ' ||
3373 'FROM JAI_RGM_INVOICE_GEN_T jrigt ' ||
3374 'WHERE regime_id = '||p_regime_id ||' '||
3375 'AND registration_num = NVL('||lv_p_registration_num_str||',registration_num) '||
3376 'AND (delivery_id BETWEEN NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
3377 'NVL('||NVL(TO_CHAR(p_delivery_id_to), 'NULL')||',delivery_id)) '||
3378 'AND EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS wdd '||
3379 ',WSH_DELIVERY_ASSIGNMENTS wda '||
3380 ',OE_ORDER_HEADERS_ALL ooha '||
3381 'WHERE ooha.order_number BETWEEN '||
3382 'NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) AND '||
3383 'NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number) '||
3384 'AND ooha.header_id = wdd.source_header_id '||
3385 'AND wda.delivery_detail_id = wdd.delivery_detail_id '||
3386 'AND wda.delivery_id = jrigt.delivery_id) ' ||
3387 'AND (TRUNC(delivery_date) BETWEEN '||
3388 'NVL(TRUNC(TO_DATE('''||pv_delivery_date_from||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date) AND '||
3389 --'NVL(TRUNC('||pv_delivery_date_to||'),delivery_date)) '||
3390 'NVL(TRUNC(TO_DATE('''||pv_delivery_date_to||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date)) '||
3391 'AND organization_id = NVL('||NVL(TO_CHAR(p_organization_id), 'NULL')||',organization_id) '||
3392 'AND location_id = NVL('||NVL(TO_CHAR(p_location_id), 'NULL')||',location_id) ' ||
3393 'AND (vat_inv_gen_status <> ''C'' OR vat_acct_status <> ''C'') '||
3394 -- modified by Allen Yang for bug 9666476 28-apr-2010, begin
3395 --'ORDER BY party_id , party_type, party_site_id, order_number';
3396 'ORDER BY party_id , party_type, party_site_id';
3397 -- modified by Allen Yang for bug 9666476 28-apr-2010, end
3398
3399 sql_stmt_all
3400 := 'SELECT delivery_id , delivery_date , organization_id , location_id , vat_invoice_no, '||
3401 'party_id , party_site_id , party_type , vat_inv_gen_status , vat_acct_status, '||
3402 'order_line_id, order_number ' ||
3403 'FROM JAI_RGM_INVOICE_GEN_T jrigt ' ||
3404 'WHERE regime_id = '||p_regime_id ||' '||
3405 'AND registration_num = NVL('||lv_p_registration_num_str||',registration_num) '||
3406 'AND (delivery_id IS NULL OR (delivery_id BETWEEN '||
3407 'NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
3408 'NVL('||NVL(TO_CHAR(p_delivery_id_to), 'NULL')||',delivery_id) '||
3409 'AND EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS wdd '||
3410 ',WSH_DELIVERY_ASSIGNMENTS wda '||
3411 ',OE_ORDER_HEADERS_ALL ooha '||
3412 'WHERE ooha.order_number BETWEEN '||
3413 'NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) AND '||
3414 'NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number) '||
3415 'AND ooha.header_id = wdd.source_header_id '||
3416 'AND wda.delivery_detail_id = wdd.delivery_detail_id '||
3417 'AND wda.delivery_id = jrigt.delivery_id))) ' ||
3418 'AND (order_number IS NULL '||
3419 'OR order_number BETWEEN NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) ' ||
3420 ' AND NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number)) ' ||
3421 'AND (TRUNC(delivery_date) BETWEEN '||
3422 --'NVL(TRUNC('||pv_delivery_date_from||'),delivery_date) AND '||
3423 --'NVL(TRUNC('||pv_delivery_date_to||'),delivery_date)) '||
3424 'NVL(TRUNC(TO_DATE('''||pv_delivery_date_from||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date) AND '||
3425 'NVL(TRUNC(TO_DATE('''||pv_delivery_date_to||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date)) '||
3426 'AND organization_id = NVL('||NVL(TO_CHAR(p_organization_id), 'NULL')||',organization_id) '||
3427 'AND location_id = NVL('||NVL(TO_CHAR(p_location_id), 'NULL')||',location_id) '||
3428 'AND (vat_inv_gen_status <> ''C'' OR vat_acct_status <> ''C'') '||
3429 -- modified by Allen Yang for bug 9666476 28-apr-2010, begin
3430 'ORDER BY party_id , party_type, party_site_id, order_number NULLS FIRST';
3431 -- modified by Allen Yang for bug 9666476 28-apr-2010, end
3432
3433 IF (p_delivery_id_from IS NOT NULL AND p_delivery_id_to IS NOT NULL) OR
3434 ((p_delivery_id_from IS NOT NULL OR p_delivery_id_to IS NOT NULL) AND
3435 (p_order_number_from IS NULL AND p_order_number_to IS NULL))
3436 THEN
3437 lv_SQLStmt := sql_stmt_shippable;
3438 -- code ported from BL12.1.3 on 25-MAR-2013 begin
3439 -- added by zhiwei.xin on 8-NOV-2012 for VAT Invoice Generation/Accounting begin
3440 if nvl(p_called_from, 'CP') = 'BE' then
3441 lv_SQLStmt := sql_stmt_all;
3442 end if;
3443 -- added by zhiwei.xin on 8-NOV-2012 for VAT Invoice Generation/Accounting end
3444 -- code ported from Bl12.1.3 on 25-MAR-2013 end.
3445 IF lv_debug = 'Y'
3446 THEN
3447 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Query SQL for shippable items only: '||lv_SQLStmt);
3448 END IF; -- lv_debug = 'Y'
3449 ELSE
3450 lv_SQLStmt := sql_stmt_all;
3451 IF lv_debug = 'Y'
3452 THEN
3453 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Query SQL for shippable and non-shippable items: '||lv_SQLStmt);
3454 END IF; -- lv_debug = 'Y'
3455 END IF; -- p_delivery_id_from IS NOT NULL AND p_delivery_id_to IS NOT NULL
3456
3457 OPEN v_main_rec_cur FOR lv_SQLStmt;
3458 LOOP
3459 FETCH v_main_rec_cur INTO mainrec;
3460 EXIT WHEN v_main_rec_cur%NOTFOUND;
3461
3462 --Fnd_File.PUT_LINE(Fnd_File.LOG, 'delivery_id: '||mainrec.delivery_id||' order_line_id: '||mainrec.order_line_id);
3463
3464 -- start processing records
3465 IF mainrec.delivery_id IS NOT NULL -- for shippable line
3466 THEN
3467 lv_p_source := jai_constants.source_wsh;
3468 ln_interface_status:= 0;
3469 OPEN c_check_interface_status(mainrec.delivery_id);
3470 FETCH c_check_interface_status into ln_interface_status;
3471 CLOSE c_check_interface_status;
3472
3473 IF ln_interface_status = 1
3474 THEN
3475 Fnd_File.PUT_LINE(Fnd_File.LOG,
3476 'Delivery - ' || mainrec.delivery_id ||
3477 ' Cannot be processed because all delivery details'||
3478 ' are not inventory interfaced');
3479 -- added by zhiwei.xin for bug#16220878 on 5-FEB-2013 begin
3480 lv_inv_gen_process_message := 'Delivery - ' || mainrec.delivery_id ||
3481 ' Cannot be processed because all delivery details'||
3482 ' are not inventory interfaced';
3483 -- added by zhiwei.xin for bug#16220878 on 5-FEB-2013 end
3484 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
3485 goto NEXTDELIVERY;
3486 END IF; -- ln_interface_status = 1
3487 IF lv_debug = 'Y' THEN
3488 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Processing Delivery - ' || mainrec.delivery_id);
3489 END IF; -- lv_debug = 'Y'
3490 ELSE -- else for non-shippable line
3491 lv_p_source := jai_constants.source_nsh;
3492 IF lv_debug = 'Y' THEN
3493 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Processing Non-shippable Order Line - ' ||
3494 mainrec.order_line_id);
3495 END IF; -- lv_debug = 'Y'
3496 END IF; -- mainrec.delivery_id IS NOT NULL
3497
3498 IF check_reg_dealer(mainrec.party_id,mainrec.party_site_id)
3499 THEN
3500 lv_doc_type_class :='O';
3501 ELSE
3502 lv_doc_type_class :='UO'; /*made it to UO from VO */
3503 END IF; -- check_reg_dealer(mainrec.party_id,mainrec.party_site_id)
3504
3505 IF lv_Debug = 'Y'
3506 THEN
3507 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_current_party_id : ' || ln_current_party_id);
3508 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.party_id : ' || mainrec.party_id);
3509 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_current_party_site_id : ' || ln_current_party_site_id);
3510 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.party_site_id :' || mainrec.party_site_id);
3511 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_current_order_number : ' || ln_current_order_number);
3512 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.order_number :' || mainrec.order_number);
3513 Fnd_File.PUT_LINE(Fnd_File.LOG, 'lv_doc_type_class:' || lv_doc_type_class);
3514 END IF; -- lv_Debug = 'Y'
3515
3516 IF ln_current_party_id <> mainrec.party_id OR
3517 ln_current_party_site_id <> mainrec.party_site_id
3518 THEN
3519 /*
3520 || There has been a change either in the party id or the party site id .
3521 || Hence a new loop needs to start
3522 */
3523 ln_current_party_id := mainrec.party_id;
3524 ln_current_party_site_id := mainrec.party_site_id;
3525 lv_party_has_changed := jai_constants.value_true;
3526 lv_inv_num_already_generated :=jai_constants.value_false;--9772724
3527 ELSE
3528 lv_party_has_changed := jai_constants.value_false;
3529 END IF; -- ln_current_party_id <> mainrec.party_id OR ... ...
3530
3531 -- modified by Allen Yang for bug bug 9485355 (12.1.3 non-shippable Enhancement), begin
3532 --OPEN c_shipment_info(mainrec.delivery_id);
3533 lv_excise_invoice_no := NULL;
3534 OPEN c_shipment_info(mainrec.delivery_id
3535 , mainrec.order_line_id);
3536 FETCH c_shipment_info INTO ln_order_type_id ,lv_excise_invoice_no ;
3537 -- modified by Allen Yang for bug bug 9485355 (12.1.3 non-shippable Enhancement), end
3538 CLOSE c_shipment_info;
3539
3540 OPEN c_vat_inv_gen_status(mainrec.delivery_id, mainrec.order_line_id);
3541 FETCH c_vat_inv_gen_status INTO lv_vat_inv_gen_status ;
3542 CLOSE c_vat_inv_gen_status;
3543
3544 -- Check if the excise invoice number can be used as VAT invoice number.
3545 OPEN c_same_inv_no(mainrec.organization_id , mainrec.location_id );
3546 FETCH c_same_inv_no INTO lv_Same_invoice_no;
3547 CLOSE c_same_inv_no;
3548
3549 IF mainrec.vat_inv_gen_status = 'C' OR lv_vat_inv_gen_status = 'C'
3550 THEN
3551 GOTO Processaccounting;
3552 END IF; -- mainrec.vat_inv_gen_status = 'C' OR lv_vat_inv_gen_status = 'C'
3553
3554 -- here comes the detail logic of VAT generation for shippable and non-shippable lines
3555 IF mainrec.delivery_id IS NOT NULL -- current is shippable line
3556 THEN
3557 IF NVL(lv_Same_invoice_no,jai_constants.no) = jai_constants.yes --same as excise
3558 THEN
3559 /* detailed logic of using excise invoice number as VAT invoice number
3560 1. if excise is not generated, then raise error msg and go NEXTDELIVERY;
3561 2. else, use excise as VAT invoice number, and update JAI_RGM_INVOICE_GEN_T
3562 (vat_invoice_no => excise_inv_number, vat_inv_gen_status => 'C');
3563 */
3564 IF lv_debug = 'Y' THEN
3565 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_Same_invoice_no = Y , Hence we need not call inv num generation routine - using excise inv num instead');
3566 END IF; -- lv_debug = 'Y'
3567
3568 ln_excise_invoice_not_done := NULL;
3569 OPEN c_excise_invoice_not_done (mainrec.delivery_id);
3570 FETCH c_excise_invoice_not_done INTO ln_excise_invoice_not_done;
3571 CLOSE c_excise_invoice_not_done ;
3572
3573 IF lv_debug = 'Y' THEN
3574 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_excise_invoice_not_done = ' || NVL(ln_excise_invoice_not_done,-1));
3575 END IF; -- lv_debug = 'Y'
3576
3577 IF ln_excise_invoice_not_done IS NULL THEN
3578 -- Excise invoice number in not found , populate it as -1
3579 ln_excise_invoice_not_done := -1;
3580 END IF; -- ln_excise_invoice_not_done IS NULL
3581
3582 IF ln_excise_invoice_not_done = 1 THEN
3583 /*
3584 || It means the delivery is still existing in the JAI_OM_OE_GEN_TAXINV_T table
3585 || It means that the excise invoice generation was either not processed or errored out.
3586 || We need to raise an error saying that excise invoice number needs to be run before VAT processing can happen.
3587 */
3588 lv_inv_gen_process_flag := jai_constants.expected_error;
3589 lv_inv_gen_process_message := 'Excise Invoice Generation is not generated yet for this Delivery. Please run the excise invoice number generation number for delivery : '
3590 || mainrec.delivery_id;
3591 -- Not using the debug flag for the following message because it needs to be shown to the user irrespective of debug flag.
3592 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Excise Invoice Generation is not generated yet for this Delivery. Please run the excise invoice number generation number for delivery : '
3593 || mainrec.delivery_id);
3594 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
3595 goto NEXTDELIVERY;
3596 ELSE
3597 /*
3598 || Control comes here - It means record for the delivery does not exist in the JAI_OM_OE_GEN_TAXINV_T table
3599 because of reasons such as :
3600 || a. Delivery does not have excise taxes
3601 || b. Explicit setting such as bond register is set to DOMESTIC_WITHOUT_EXCISE or EXPORT_WITHOUT_EXCISE
3602 || c. The item itself is not excisable , hence no excise invoice num is generated.
3603 || d. Excise invoice is already generated
3604
3605 For cases a to c , need to generate VAT invoice number explicitly.
3606 For case d , need to copy the excise invoice number and make it the vat invoice number.
3607 */
3608 IF lv_excise_invoice_no IS NOT NULL THEN
3609 -- Excise invoice number is not null - so the same excise invoice number needs to be used as VAT invoice number
3610 lv_vat_invoice_number := lv_excise_invoice_no;
3611 IF lv_debug = 'Y' THEN
3612 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Excise Invoice number - ' || lv_vat_invoice_number || ' Will be used as VAT Invoice number for delivery : ' || mainrec.delivery_id);
3613 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_om_wsh_lines_all for ex inv num');
3614 END IF; -- lv_debug = 'Y'
3615
3616 UPDATE JAI_OM_WSH_LINES_ALL
3617 SET vat_invoice_no = excise_invoice_no
3618 , vat_invoice_date = excise_invoice_date
3619 WHERE delivery_id = mainrec.delivery_id;
3620
3621 IF lv_debug = 'Y' THEN
3622 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_rgm_invoice_gen_t for ex inv num');
3623 END IF;
3624
3625 UPDATE JAI_RGM_INVOICE_GEN_T
3626 SET vat_invoice_no = lv_vat_invoice_number
3627 , vat_inv_gen_status = 'C'
3628 , vat_inv_gen_err_message = NULL
3629 , request_id = ln_conc_request_id
3630 , program_id = ln_conc_progam_id
3631 , program_application_id = ln_conc_prog_appl_id
3632 , last_update_login = fnd_global.conc_login_id
3633 , last_update_Date = sysdate
3634 WHERE Delivery_id = mainrec.delivery_id;
3635
3636 IF lv_debug = 'Y' THEN
3637 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after updating jai_rgm_invoice_gen_t for ex inv num');
3638 END IF;
3639
3640 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
3641
3642 IF lv_debug = 'Y' THEN
3643 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Before going to process accounting for generating accounting');
3644 END IF;
3645
3646 lv_inv_gen_process_flag := jai_constants.successful;
3647 GOTO Processaccounting;
3648 END IF; -- lv_excise_invoice_no IS NOT NULL
3649 END IF; -- ln_excise_invoice_not_done = 1
3650 END IF; --Added by nprashar for bug # 12827535
3651
3652 --Bug # 12827535
3653 --Added this new IF coindition which checks even if the setup for Same_invoice for Excise and VAT exists,
3654 --if excise invoice is not generated, VAT invoice number should be generated
3655
3656 IF NVL(lv_Same_invoice_no,jai_constants.no) = jai_constants.no or ( ln_excise_invoice_not_done < 0 and
3657 NVL(lv_Same_invoice_no,jai_constants.no) = jai_constants.yes )
3658 Then
3659
3660 /*
3661 || Check if we need to generate vat invoice number and within that check if an invoice number is already generated.
3662 || if an invoice is already generated and if the parameter p_single_invoice_num is set to 'Y' then do not make an
3663 || API call again and again to the generation api . Just update the JAI_OM_WSH_LINES_ALL table to set the
3664 || vat invoice number for the delivery and continue.
3665 */
3666 IF NVL(p_single_invoice_num,jai_constants.No) = jai_constants.yes -- single invoice number is true
3667 THEN
3668 IF NVL(lv_party_has_changed,jai_constants.value_false) = jai_constants.value_true -- party has changed
3669 THEN
3670 /* generate new VAT invoice number by document sequence;
3671 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
3672 2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
3673 table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number
3674 , vat_inv_gen_status => 'C');
3675 */
3676 IF lv_Debug = 'Y'
3677 THEN
3678 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_party_has_changed :' || lv_party_has_changed);
3679 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_num_already_generated :' || lv_inv_num_already_generated);
3680 END IF; -- lv_Debug = 'Y'
3681 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
3682 THEN
3683 IF lv_inv_num_already_generated = jai_constants.value_false
3684 THEN
3685 IF lv_Debug = 'Y' THEN
3686 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with ln_order_type_id' || ln_order_type_id || 'date ' || mainrec.delivery_date );
3687 END IF; -- lv_Debug = 'Y'
3688 jai_cmn_rgm_setup_pkg.Gen_Invoice_number(p_regime_id => ln_regime_id
3689 , p_organization_id => mainrec.organization_id
3690 , p_location_id => mainrec.location_id
3691 , p_date => mainrec.delivery_date
3692 , p_doc_class => lv_doc_type_class
3693 , p_doc_type_id => ln_order_type_id
3694 , P_invoice_number => lv_vat_invoice_number
3695 , p_process_flag => lv_inv_gen_process_flag
3696 , p_process_msg => lv_inv_gen_process_message
3697 );
3698 IF lv_Debug = 'Y' THEN
3699 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after call with lv_vat_invoice_number:' || lv_vat_invoice_number || lv_inv_gen_process_flag ||lv_inv_gen_process_message);
3700 END IF; -- lv_Debug = 'Y'
3701
3702 -- check the return status and update the JAI_OM_WSH_LINES_ALL table to set the vat invoice number
3703 IF lv_inv_gen_process_flag = jai_constants.successful
3704 THEN
3705 IF lv_vat_invoice_number IS NOT NULL
3706 THEN
3707 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
3708 lv_inv_num_already_generated := jai_constants.value_true;
3709 UPDATE JAI_OM_WSH_LINES_ALL
3710 SET VAT_INVOICE_NO = lv_vat_invoice_number
3711 , VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
3712 , LAST_UPDATE_DATE = sysdate
3713 , LAST_UPDATE_LOGIN = fnd_global.login_id
3714 , LAST_UPDATED_BY = fnd_global.user_id
3715 WHERE DELIVERY_ID = mainrec.delivery_id;
3716
3717 UPDATE JAI_RGM_INVOICE_GEN_T
3718 SET vat_invoice_no = lv_vat_invoice_number
3719 , vat_inv_gen_status = 'C'
3720 , request_id = ln_conc_request_id
3721 , program_id = ln_conc_progam_id
3722 , program_application_id = ln_conc_prog_appl_id
3723 , last_update_login = fnd_global.conc_login_id
3724 , last_update_date = sysdate
3725 WHERE delivery_id = mainrec.delivery_id;
3726 ELSE
3727 lv_inv_gen_process_flag := jai_constants.unexpected_error;
3728 lv_acct_process_flag := jai_constants.expected_error;
3729 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
3730 END IF; -- lv_vat_invoice_number IS NOT NULL
3731 ELSE
3732 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
3733 END IF; -- lv_inv_gen_process_flag = jai_constants.successful
3734 END IF; -- lv_inv_num_already_generated = jai_constants.value_false
3735 END IF; -- p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
3736
3737 ELSE -- party not change
3738 /* 1. use existing VAT invoice number for this record;
3739 2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_INVOICE_GEN_T;
3740 */
3741 IF lv_vat_invoice_number IS NOT NULL
3742 THEN
3743 -- Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current delivery.
3744 UPDATE JAI_OM_WSH_LINES_ALL
3745 SET vat_invoice_no = lv_vat_invoice_number,
3746 VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
3747 last_update_date = sysdate,
3748 last_update_login = fnd_global.login_id,
3749 last_updated_by = fnd_global.user_id
3750 WHERE delivery_id IN (SELECT delivery_id
3751 FROM JAI_RGM_INVOICE_GEN_T jrigt
3752 WHERE party_id = ln_current_party_id
3753 AND party_site_id = ln_current_party_site_id
3754 AND party_type = mainrec.party_type
3755 AND vat_inv_gen_status <> 'C'
3756 AND delivery_id BETWEEN NVL(P_DELIVERY_ID_FROM,delivery_id)
3757 AND NVL(P_DELIVERY_ID_TO,delivery_id)
3758 AND EXISTS (SELECT 1
3759 FROM WSH_DELIVERY_ASSIGNMENTS wda
3760 , WSH_DELIVERY_DETAILS wdd
3761 , OE_ORDER_HEADERS_ALL ooha
3762 WHERE wda.delivery_id = jrigt.delivery_id
3763 AND wda.delivery_detail_id = wdd.delivery_detail_id
3764 AND wdd.source_header_id = ooha.header_id
3765 AND ooha.order_number BETWEEN
3766 NVL(p_order_number_from, ooha.order_number) AND
3767 NVL(p_order_number_to, ooha.order_number))
3768 AND trunc(delivery_Date) BETWEEN NVL(P_DELIVERY_DATE_FROM,Delivery_date)
3769 AND NVL(P_DELIVERY_DATE_TO,delivery_date));
3770
3771 UPDATE JAI_RGM_INVOICE_GEN_T
3772 SET vat_invoice_no = lv_vat_invoice_number,
3773 vat_inv_gen_status = 'C',
3774 request_id = ln_conc_request_id,
3775 program_id = ln_conc_progam_id,
3776 program_application_id = ln_conc_prog_appl_id,
3777 last_update_login = fnd_global.conc_login_id
3778 WHERE delivery_id IN (SELECT delivery_id
3779 FROM JAI_RGM_INVOICE_GEN_T jrigt
3780 WHERE party_id = ln_current_party_id
3781 AND party_site_id = ln_current_party_site_id
3782 AND party_type = mainrec.party_type
3783 AND vat_inv_gen_status <> 'C'
3784 AND delivery_id BETWEEN NVL(P_DELIVERY_ID_FROM,delivery_id)
3785 AND NVL(P_DELIVERY_ID_TO,delivery_id)
3786 AND EXISTS (SELECT 1
3787 FROM WSH_DELIVERY_ASSIGNMENTS wda
3788 , WSH_DELIVERY_DETAILS wdd
3789 , OE_ORDER_HEADERS_ALL ooha
3790 WHERE wda.delivery_id = jrigt.delivery_id
3791 AND wda.delivery_detail_id = wdd.delivery_detail_id
3792 AND wdd.source_header_id = ooha.header_id
3793 AND ooha.order_number BETWEEN
3794 NVL(p_order_number_from, ooha.order_number) AND
3795 NVL(p_order_number_to, ooha.order_number))
3796 AND trunc(delivery_Date) BETWEEN NVL(P_DELIVERY_DATE_FROM,Delivery_date)
3797 AND NVL(P_DELIVERY_DATE_TO,delivery_date));
3798
3799 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + sql%rowcount ;
3800
3801 IF lv_Debug = 'Y' THEN
3802 Fnd_File.PUT_LINE(Fnd_File.LOG, 'No. of Deliveries updated in jai_vat_processing_t: ' || SQL%ROWCOUNT);
3803 END IF; -- lv_Debug = 'Y'
3804 END IF; -- lv_vat_invoice_number IS NOT NULL
3805 END IF; -- lv_party_has_changed
3806 ELSE -- single invoice number is false
3807 /* generate new VAT invoice number by document sequence;
3808 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
3809 2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
3810 table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
3811 vat_inv_gen_status => 'C');
3812 */
3813 IF lv_Debug = 'Y' THEN
3814 Fnd_File.PUT_LINE(Fnd_File.LOG, 'In the Else when p_single_invoice is not Y ');
3815 END IF;
3816 /*
3817 || This is the Else Part of the IF p_single_invoice_num = 'Y' THEN
3818 || In this comes the code that is needed for different generating vat invoice number for every delivery
3819 */
3820 IF lv_Debug = 'Y' THEN
3821 Fnd_File.PUT_LINE(Fnd_File.LOG, '+++ before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number In the Else when p_single_invoice is not Y with order type = '
3822 || ln_order_type_id || ' +++ ' );
3823
3824 Fnd_File.PUT_LINE(Fnd_File.LOG, '0 ' || ln_regime_id || ' ' ||
3825 mainrec.organization_id || ' ' || mainrec.location_id || ' ' ||
3826 mainrec.delivery_date || '' || ln_order_type_id || ' ' ||
3827 lv_vat_invoice_number || ' ' || lv_inv_gen_process_flag || ' ' ||
3828 lv_inv_gen_process_message ) ;
3829
3830 END IF; -- lv_Debug = 'Y'
3831 jai_cmn_rgm_setup_pkg.Gen_Invoice_number( p_regime_id => ln_regime_id
3832 , p_organization_id => mainrec.organization_id
3833 , p_location_id => mainrec.location_id
3834 , p_date => mainrec.delivery_date
3835 , p_doc_class => lv_doc_type_class
3836 , p_doc_type_id => ln_order_type_id
3837 , P_invoice_number => lv_vat_invoice_number
3838 , p_process_flag => lv_inv_gen_process_flag
3839 , p_process_msg => lv_inv_gen_process_message
3840 );
3841 IF lv_Debug = 'Y' THEN
3842 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ after call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number In the Else when p_single_invoice is not Y with lv_vat_invoice_number = '
3843 || lv_vat_invoice_number || '+++');
3844 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ after call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with lv_inv_gen_process_flag = '
3845 || lv_inv_gen_process_flag || 'lv_inv_gen_process_message '|| lv_inv_gen_process_message || '+++');
3846 END IF; -- lv_Debug = 'Y'
3847 IF lv_inv_gen_process_flag = jai_constants.successful
3848 THEN
3849 IF lv_vat_invoice_number IS NOT NULL
3850 THEN
3851 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
3852 UPDATE JAI_OM_WSH_LINES_ALL
3853 SET vat_invoice_no = lv_vat_invoice_number,
3854 vat_invoice_date = nvl(ld_override_invoice_date ,sysdate),
3855 last_update_date = sysdate,
3856 last_update_login = fnd_global.login_id,
3857 last_updated_by = fnd_global.user_id
3858 WHERE delivery_id = mainrec.delivery_id;
3859
3860 UPDATE JAI_RGM_INVOICE_GEN_T
3861 SET vat_invoice_no = lv_vat_invoice_number,
3862 vat_inv_gen_status = 'C',
3863 vat_inv_gen_err_message = NULL ,
3864 request_id = ln_conc_request_id,
3865 program_id = ln_conc_progam_id,
3866 program_application_id = ln_conc_prog_appl_id,
3867 last_update_login = fnd_global.conc_login_id,
3868 last_update_date = sysdate
3869 WHERE delivery_id = mainrec.delivery_id;
3870
3871 ELSE
3872 lv_inv_gen_process_flag := jai_constants.unexpected_error;
3873 lv_inv_gen_process_message := 'No VAT Invoice Number Generated';
3874 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
3875 END IF; -- lv_vat_invoice_number IS NOT NULL
3876 ELSE
3877 UPDATE JAI_RGM_INVOICE_GEN_T
3878 SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
3879 vat_inv_gen_status = 'E',
3880 request_id = ln_conc_request_id,
3881 program_id = ln_conc_progam_id,
3882 program_application_id = ln_conc_prog_appl_id,
3883 last_update_login = fnd_global.conc_login_id,
3884 last_update_date = sysdate
3885 WHERE delivery_id = mainrec.delivery_id;
3886 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
3887 END IF; -- lv_inv_gen_process_flag = jai_constants.successful
3888 END IF; -- NVL(lv_Same_invoice_no,jai_constants.no) = jai_constants.yes
3889 END IF; -- End of condition added for bug # 12827535
3890
3891 -- below logic is for non-shippable lines
3892 ELSE -- current line is non-shippable line
3893 IF ln_current_order_number <> mainrec.order_number
3894 THEN
3895 /*
3896 || There has been a change in order number .
3897 || Hence a new loop needs to start
3898 */
3899 ln_current_order_number := mainrec.order_number;
3900 lv_order_has_changed := jai_constants.value_true;
3901 ELSE
3902 lv_order_has_changed := jai_constants.value_false;
3903 END IF; -- ln_current_order_number <> mainrec.order_number
3904
3905 /* commented following logic as for non-shippable lines, same_as_excise flag will not take effect
3906 -- if same_as_excise flag is 'Y', use excise invoice number of first fetched delivery line as vat invoice number
3907 IF NVL(lv_Same_invoice_no,jai_constants.no) = jai_constants.yes --same as excise
3908 THEN
3909 IF lv_debug = 'Y' THEN
3910 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_Same_invoice_no = Y , Hence we need not call inv num generation routine - using excise inv num instead');
3911 END IF; -- lv_debug = 'Y'
3912
3913 lv_excise_invoice_no := NULL;
3914 OPEN c_get_excise_from_shippable (mainrec.order_number);
3915 FETCH c_get_excise_from_shippable
3916 INTO lv_excise_invoice_no
3917 ,ld_excise_invoice_date;
3918 CLOSE c_get_excise_from_shippable ;
3919
3920 IF lv_debug = 'Y' THEN
3921 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_excise_invoice_no is ' || NVL(lv_excise_invoice_no,'NULL'));
3922 END IF; -- lv_debug = 'Y'
3923
3924 IF lv_excise_invoice_no IS NOT NULL
3925 THEN
3926 -- Excise invoice number is not null - so the same excise invoice number needs to be used as VAT invoice number
3927 IF lv_debug = 'Y' THEN
3928 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Excise Invoice number - ' || lv_excise_invoice_no || ' Will be used as VAT Invoice number for non-shippable order line : ' || mainrec.order_line_id);
3929 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_om_wsh_lines_all for ex inv num');
3930 END IF; -- lv_debug = 'Y'
3931
3932 UPDATE JAI_OM_WSH_LINES_ALL
3933 SET vat_invoice_no = lv_excise_invoice_no
3934 , vat_invoice_date = ld_excise_invoice_date
3935 WHERE order_line_id = mainrec.order_line_id
3936 AND delivery_id IS NULL;
3937
3938 IF lv_debug = 'Y' THEN
3939 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_rgm_invoice_gen_t for ex inv num');
3940 END IF; -- lv_debug = 'Y'
3941
3942 UPDATE JAI_RGM_INVOICE_GEN_T
3943 SET vat_invoice_no = lv_excise_invoice_no
3944 , vat_inv_gen_status = 'C'
3945 , vat_inv_gen_err_message = NULL
3946 , request_id = ln_conc_request_id
3947 , program_id = ln_conc_progam_id
3948 , program_application_id = ln_conc_prog_appl_id
3949 , last_update_login = fnd_global.conc_login_id
3950 , last_update_Date = sysdate
3951 WHERE order_line_id = mainrec.order_line_id;
3952
3953 IF lv_debug = 'Y' THEN
3954 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after updating jai_rgm_invoice_gen_t for ex inv num');
3955 END IF;
3956
3957 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
3958
3959 IF lv_debug = 'Y' THEN
3960 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Before going to process accounting for generating accounting');
3961 END IF;
3962
3963 lv_inv_gen_process_flag := jai_constants.successful;
3964 lv_inv_num_already_generated := jai_constants.value_true;
3965 lv_vat_invoice_number := lv_excise_invoice_no;
3966 GOTO Processaccounting;
3967 END IF; -- lv_excise_invoice_no IS NOT NULL
3968
3969 END IF; -- NVL(lv_Same_invoice_no,jai_constants.no) = jai_constants.yes
3970 */
3971
3972 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_party_has_changed :' || lv_party_has_changed);
3973 Fnd_File.PUT_LINE(Fnd_File.LOG, ' p_single_invoice_num :' || p_single_invoice_num);
3974
3975 IF NVL(p_single_invoice_num,jai_constants.No) = jai_constants.yes -- single invoice number is true
3976 THEN
3977 IF NVL(lv_party_has_changed,jai_constants.value_false) = jai_constants.value_true -- party has changed
3978 THEN
3979 /* generate new VAT invoice number by document sequence;
3980 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
3981 2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
3982 table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
3983 vat_inv_gen_status => 'C');
3984 */
3985 IF lv_Debug = 'Y'
3986 THEN
3987 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_party_has_changed :' || lv_party_has_changed);
3988 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_num_already_generated :' || lv_inv_num_already_generated);
3989 END IF; -- lv_Debug = 'Y'
3990 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
3991 THEN
3992 --IF lv_inv_num_already_generated = jai_constants.value_false
3993 --THEN
3994 IF lv_Debug = 'Y' THEN
3995 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with ln_order_type_id' || ln_order_type_id || 'date ' || mainrec.delivery_date );
3996 END IF; -- lv_Debug = 'Y'
3997 jai_cmn_rgm_setup_pkg.Gen_Invoice_number( p_regime_id => ln_regime_id
3998 , p_organization_id => mainrec.organization_id
3999 , p_location_id => mainrec.location_id
4000 , p_date => mainrec.delivery_date
4001 , p_doc_class => lv_doc_type_class
4002 , p_doc_type_id => ln_order_type_id
4003 , P_invoice_number => lv_vat_invoice_number
4004 , p_process_flag => lv_inv_gen_process_flag
4005 , p_process_msg => lv_inv_gen_process_message
4006 );
4007 IF lv_Debug = 'Y' THEN
4008 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after call with lv_vat_invoice_number:' || lv_vat_invoice_number || lv_inv_gen_process_flag ||lv_inv_gen_process_message);
4009 END IF; -- lv_Debug = 'Y'
4010
4011 -- check the return status and update the JAI_OM_WSH_LINES_ALL table to set the vat invoice number
4012 IF lv_inv_gen_process_flag = jai_constants.successful
4013 THEN
4014 IF lv_vat_invoice_number IS NOT NULL
4015 THEN
4016 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
4017 lv_inv_num_already_generated := jai_constants.value_true;
4018 UPDATE JAI_OM_WSH_LINES_ALL
4019 SET VAT_INVOICE_NO = lv_vat_invoice_number
4020 , VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
4021 , LAST_UPDATE_DATE = sysdate
4022 , LAST_UPDATE_LOGIN = fnd_global.login_id
4023 , LAST_UPDATED_BY = fnd_global.user_id
4024 WHERE order_line_id = mainrec.order_line_id
4025 AND delivery_id IS NULL;
4026
4027 UPDATE JAI_RGM_INVOICE_GEN_T
4028 SET vat_invoice_no = lv_vat_invoice_number
4029 , vat_inv_gen_status = 'C'
4030 , request_id = ln_conc_request_id
4031 , program_id = ln_conc_progam_id
4032 , program_application_id = ln_conc_prog_appl_id
4033 , last_update_login = fnd_global.conc_login_id
4034 , last_update_date = sysdate
4035 WHERE order_line_id = mainrec.order_line_id;
4036 ELSE
4037 lv_inv_gen_process_flag := jai_constants.unexpected_error;
4038 lv_acct_process_flag := jai_constants.expected_error;
4039 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
4040 END IF; -- lv_vat_invoice_number IS NOT NULL
4041 ELSE
4042 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
4043 END IF; -- lv_inv_gen_process_flag = jai_constants.successful
4044 --END IF; -- lv_inv_num_already_generated = jai_constants.value_false
4045 END IF; -- p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
4046 ELSE -- party not change
4047 /* 1. use existing VAT invoice number for this record;
4048 2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_INVOICE_GEN_T;
4049 */
4050 IF lv_vat_invoice_number IS NOT NULL THEN
4051 -- Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current non-shippable line.
4052 UPDATE JAI_OM_WSH_LINES_ALL
4053 SET vat_invoice_no = lv_vat_invoice_number
4054 , VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
4055 , last_update_date = sysdate
4056 , last_update_login = fnd_global.login_id
4057 , last_updated_by = fnd_global.user_id
4058 WHERE order_line_id = mainrec.order_line_id
4059 AND delivery_id IS NULL;
4060
4061 UPDATE JAI_RGM_INVOICE_GEN_T
4062 SET vat_invoice_no = lv_vat_invoice_number
4063 , vat_inv_gen_status = 'C'
4064 , request_id = ln_conc_request_id
4065 , program_id = ln_conc_progam_id
4066 , program_application_id = ln_conc_prog_appl_id
4067 , last_update_login = fnd_global.conc_login_id
4068 WHERE order_line_id = mainrec.order_line_id;
4069
4070 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1 ;
4071 END IF; -- lv_vat_invoice_number IS NOT NULL
4072 END IF; -- lv_party_has_changed
4073 ELSE -- single invoice number is false
4074 -- if single invoice number option is 'NO', then generate VAT invoice by Order Numbers
4075
4076 IF NVL(lv_order_has_changed,jai_constants.value_false) = jai_constants.value_true -- order number has changed
4077 THEN
4078 /* generate new VAT invoice number by document sequence;
4079 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
4080 2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
4081 table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
4082 vat_inv_gen_status => 'C');
4083 */
4084 IF lv_Debug = 'Y'
4085 THEN
4086 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_order_has_changed :' || lv_order_has_changed);
4087 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_num_already_generated :' || lv_inv_num_already_generated);
4088 END IF; -- lv_Debug = 'Y'
4089 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
4090 THEN
4091 --IF lv_inv_num_already_generated = jai_constants.value_false
4092 --THEN
4093 IF lv_Debug = 'Y' THEN
4094 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with ln_order_type_id' || ln_order_type_id || 'date ' || mainrec.delivery_date );
4095 END IF; -- lv_Debug = 'Y'
4096 jai_cmn_rgm_setup_pkg.Gen_Invoice_number( p_regime_id => ln_regime_id
4097 , p_organization_id => mainrec.organization_id
4098 , p_location_id => mainrec.location_id
4099 , p_date => mainrec.delivery_date
4100 , p_doc_class => lv_doc_type_class
4101 , p_doc_type_id => ln_order_type_id
4102 , P_invoice_number => lv_vat_invoice_number
4103 , p_process_flag => lv_inv_gen_process_flag
4104 , p_process_msg => lv_inv_gen_process_message
4105 );
4106 IF lv_Debug = 'Y' THEN
4107 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after call with lv_vat_invoice_number:' || lv_vat_invoice_number || lv_inv_gen_process_flag ||lv_inv_gen_process_message);
4108 END IF; -- lv_Debug = 'Y'
4109
4110 -- check the return status and update the JAI_OM_WSH_LINES_ALL table to set the vat invoice number
4111 IF lv_inv_gen_process_flag = jai_constants.successful
4112 THEN
4113 IF lv_vat_invoice_number IS NOT NULL
4114 THEN
4115 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
4116 lv_inv_num_already_generated := jai_constants.value_true;
4117 UPDATE JAI_OM_WSH_LINES_ALL
4118 SET VAT_INVOICE_NO = lv_vat_invoice_number
4119 , VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
4120 , LAST_UPDATE_DATE = sysdate
4121 , LAST_UPDATE_LOGIN = fnd_global.login_id
4122 , LAST_UPDATED_BY = fnd_global.user_id
4123 WHERE order_line_id = mainrec.order_line_id
4124 AND delivery_id IS NULL;
4125
4126 UPDATE JAI_RGM_INVOICE_GEN_T
4127 SET vat_invoice_no = lv_vat_invoice_number
4128 , vat_inv_gen_status = 'C'
4129 , request_id = ln_conc_request_id
4130 , program_id = ln_conc_progam_id
4131 , program_application_id = ln_conc_prog_appl_id
4132 , last_update_login = fnd_global.conc_login_id
4133 , last_update_date = sysdate
4134 WHERE order_line_id = mainrec.order_line_id;
4135 ELSE
4136 lv_inv_gen_process_flag := jai_constants.unexpected_error;
4137 lv_acct_process_flag := jai_constants.expected_error;
4138 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
4139 END IF; -- lv_vat_invoice_number IS NOT NULL
4140 ELSE
4141 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
4142 END IF; -- lv_inv_gen_process_flag = jai_constants.successful
4143 --END IF; -- lv_inv_num_already_generated = jai_constants.value_false
4144 END IF; -- p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
4145 ELSE -- order number not change
4146 /* 1. use existing VAT invoice number for this record;
4147 2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_INVOICE_GEN_T;
4148 */
4149 IF lv_vat_invoice_number IS NOT NULL THEN
4150 -- Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current delivery.
4151 UPDATE JAI_OM_WSH_LINES_ALL
4152 SET vat_invoice_no = lv_vat_invoice_number
4153 , VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
4154 , last_update_date = sysdate
4155 , last_update_login = fnd_global.login_id
4156 , last_updated_by = fnd_global.user_id
4157 WHERE order_line_id = mainrec.order_line_id
4158 AND delivery_id IS NULL;
4159
4160 UPDATE JAI_RGM_INVOICE_GEN_T
4161 SET vat_invoice_no = lv_vat_invoice_number
4162 , vat_inv_gen_status = 'C'
4163 , request_id = ln_conc_request_id
4164 , program_id = ln_conc_progam_id
4165 , program_application_id = ln_conc_prog_appl_id
4166 , last_update_login = fnd_global.conc_login_id
4167 WHERE order_line_id = mainrec.order_line_id;
4168
4169 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
4170 END IF; -- lv_vat_invoice_number IS NOT NULL
4171 END IF; -- lv_order_has_changed
4172 END IF; -- p_single_invoice_num
4173 END IF; -- IF mainrec.delivery_id IS NOT NULL
4174
4175 -- modified by Allen Yang for for bug 9485355 (12.1.3 non-shippable Enhancement), end
4176
4177 -- Now process the om-ar accounting if it is needed
4178
4179 <<Processaccounting>>
4180
4181 IF lv_debug = 'Y' THEN
4182 Fnd_File.PUT_LINE(Fnd_File.LOG, ' In process accounting section with p_process_action = ' || p_process_action);
4183 END IF;
4184
4185 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt,jai_constants.om_action_gen_accounting) THEN
4186 /*
4187 || Only In case the parameter p_process_action in ('PROCESS ALL','PROCESS ACCOUNTING') AND
4188 ||
4189 */
4190 IF lv_Debug = 'Y' THEN
4191 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.vat_acct_status : ' || mainrec.vat_acct_status || ' lv_inv_gen_process_flag : ' || lv_inv_gen_process_flag );
4192 END IF;
4193 IF mainrec.vat_acct_status = 'C' THEN
4194 GOTO NEXTDELIVERY;
4195 END IF;
4196 IF lv_Debug = 'Y' THEN
4197 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_gen_process_flag = ' || lv_inv_gen_process_flag );
4198 END IF;
4199 IF lv_inv_gen_process_flag = jai_constants.successful THEN
4200
4201 IF lv_vat_invoice_number IS NULL THEN
4202 lv_vat_invoice_number:= mainrec.vat_invoice_no;
4203 END IF;
4204
4205 IF p_process_action = jai_constants.om_action_gen_accounting
4206 THEN
4207 lv_vat_invoice_number := mainrec.vat_invoice_no;
4208 END IF;
4209
4210 IF lv_Debug = 'Y' THEN
4211 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Before Call to jai_cmn_rgm_vat_accnt_pkg.process_order_invoice ');
4212 END IF;
4213 jai_cmn_rgm_vat_accnt_pkg.process_order_invoice(
4214 P_REGIME_ID => ln_regime_id ,
4215 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
4216 P_SOURCE => lv_p_source,
4217 -- P_SOURCE => jai_constants.source_wsh ,
4218 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
4219 P_ORGANIZATION_ID => mainrec.organization_id,
4220 P_LOCATION_ID => mainrec.location_id ,
4221 P_DELIVERY_ID => mainrec.delivery_id ,
4222 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
4223 P_ORDER_LINE_ID => mainrec.order_line_id ,
4224 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
4225 P_CUSTOMER_TRX_ID => NULL ,
4226 P_VAT_INVOICE_NO => lv_vat_invoice_number ,
4227 P_TRANSACTION_TYPE => jai_cmn_rgm_vat_accnt_pkg.gv_transaction_type_dflt,
4228 P_DEFAULT_INVOICE_DATE => NVL(ld_override_invoice_date,SYSDATE),
4229 P_BATCH_ID => ln_batch_id ,
4230 P_CALLED_FROM => 'jai_cmn_rgm_processing_pkg.PROCESS',
4231 P_DEBUG => lv_debug ,
4232 P_PROCESS_FLAG => lv_acct_process_flag ,
4233 P_PROCESS_MESSAGE => lv_acct_process_message
4234 );
4235 IF lv_Debug = 'Y' THEN
4236 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after Call to jai_cmn_rgm_vat_accnt_pkg.process_order_invoice with status = ' || lv_acct_process_flag);
4237 END IF;
4238 IF lv_acct_process_flag = jai_constants.successful THEN
4239 /*
4240 || If the control comes here it means that Accounting got processed successfully.
4241 || Check here if Delivery for successfully processed and invoice got successfully processed
4242 || and only then do a commit
4243 */
4244 IF lv_inv_gen_process_flag = jai_constants.successful AND lv_acct_process_flag = jai_constants.successful THEN
4245 /*
4246 || Both the activities have been succesfully completed
4247 || Can commit the changes made to the delivery.
4248 */
4249 UPDATE JAI_RGM_INVOICE_GEN_T
4250 SET vat_acct_status = 'C',
4251 vat_inv_gen_err_message = NULL, /*following columns added by srjayara for bug 4702156*/
4252 request_id = ln_conc_request_id,
4253 program_id = ln_conc_progam_id,
4254 program_application_id = ln_conc_prog_appl_id,
4255 last_update_login = fnd_global.conc_login_id,
4256 last_update_date = sysdate
4257 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
4258 -- WHERE delivery_id = mainrec.delivery_id;
4259 WHERE delivery_id = NVL(mainrec.delivery_id, -1)
4260 OR order_line_id = NVL(mainrec.order_line_id, -1);
4261 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
4262 COMMIT;
4263 END IF;
4264 ELSE
4265 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
4266 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Error Encountered after call to process_order_invoice is ' || lv_acct_process_message);
4267 END IF;
4268
4269 IF lv_inv_gen_process_flag <> jai_constants.successful OR lv_acct_process_flag <> jai_constants.successful THEN
4270
4271 /*
4272 || There have been some errors which have happened during accounting
4273 */
4274 ROLLBACK;
4275
4276 IF lv_inv_gen_process_flag <> jai_constants.successful THEN
4277 UPDATE JAI_RGM_INVOICE_GEN_T
4278 SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
4279 vat_inv_gen_status = 'E',
4280 request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
4281 program_id = ln_conc_progam_id,
4282 program_application_id = ln_conc_prog_appl_id,
4283 last_update_login = fnd_global.conc_login_id,
4284 last_update_date = sysdate
4285 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
4286 -- WHERE delivery_id = mainrec.delivery_id;
4287 WHERE delivery_id = NVL(mainrec.delivery_id, -1)
4288 OR order_line_id = NVL(mainrec.order_line_id, -1);
4289 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
4290 END IF;
4291
4292 IF lv_acct_process_flag <> jai_constants.successful THEN
4293 UPDATE JAI_RGM_INVOICE_GEN_T
4294 SET vat_acct_err_message = substr(lv_acct_process_message,1,1000),
4295 vat_acct_status = 'E',
4296 request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
4297 program_id = ln_conc_progam_id,
4298 program_application_id = ln_conc_prog_appl_id,
4299 last_update_login = fnd_global.conc_login_id,
4300 last_update_date = sysdate
4301 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
4302 -- WHERE delivery_id = mainrec.delivery_id;
4303 WHERE delivery_id = NVL(mainrec.delivery_id, -1)
4304 OR order_line_id = NVL(mainrec.order_line_id, -1);
4305 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
4306 END IF;
4307 COMMIT;
4308 END IF;
4309
4310 END IF; /* END IF For IF lv_inv_gen_process_flag = jai_constants.successful THEN */
4311 END IF; /* END IF For IF p_process_action in ('ALL','PROCESS ACCOUNTING') THEN */
4312 <<NEXTDELIVERY>>
4313 NULL;
4314 END LOOP;
4315
4316 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
4317 -- need to purse JAI_RGM_INVOICE_GEN_T at the end of this concurrent
4318 DELETE FROM JAI_RGM_INVOICE_GEN_T
4319 WHERE vat_inv_gen_status = 'C'
4320 AND vat_acct_status = 'C';
4321 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
4322
4323
4324 /*
4325 || Coding here to mark the status of the concurrent and generating statictics.
4326 ||
4327 */
4328 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ Number of Successful deliveries : ' || ln_success_delivery_Ctr || '+++');
4329 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ Number of Failed deliveries : ' || ln_failure_delivery_ctr || '+++');
4330
4331 IF ln_failure_delivery_ctr > 0 AND ln_success_delivery_Ctr > 0 then
4332 /*
4333 || Atleast one delivery failed Atleast one delivery Succeeded
4334 || Signal completion with warning
4335 */
4336 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', NVL(lv_acct_process_message,lv_inv_gen_process_message));
4337 retcode := '1';
4338 -- added by zhiwei.xin on 10-JAN-2013 for bug#16167577 wsh error handling begin
4339 errbuf := NVL(lv_acct_process_message,lv_inv_gen_process_message);
4340 -- added by zhiwei.xin on 10-JAN-2013 for bug#16167577 wsh error handling end.
4341 END IF;
4342 IF ln_failure_delivery_ctr = 0 AND ln_success_delivery_Ctr > 0 then
4343 /*
4344 || Atleast one delivery Succeeded and none failed
4345 || Signal completion with success
4346 */
4347 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', NULL);
4348 retcode := '0';
4349 END IF;
4350 IF ln_failure_delivery_ctr > 0 AND ln_success_delivery_Ctr = 0 then
4351 /*
4352 || Atleast one delivery failed and No delivery Succeeded
4353 || Signal completion with error
4354 */
4355 retcode := '2';
4356 errbuf := NVL(lv_acct_process_message,lv_inv_gen_process_message);
4357 -- added by zhiwei.xin on 10-JAN-2013 for bug#16167577 wsh error handling end.
4358 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NVL(lv_acct_process_message,lv_inv_gen_process_message));
4359 END IF;
4360 IF ln_failure_delivery_ctr = 0 AND ln_success_delivery_Ctr = 0 then
4361 /*
4362 || No delivery failed and No delivery Succeeded
4363 || Signal completion with Success
4364 */
4365 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', NULL);
4366 retcode := '0';
4367 END IF;
4368
4369 -- added by Allen Yang for bug 9709477 13-May-2010, begin
4370 IF (
4371 (lv_Same_invoice_no = jai_constants.yes) AND
4372 (NVL(p_single_invoice_num,jai_constants.No) = jai_constants.yes)
4373 )
4374 THEN
4375 lv_same_as_excise_conf_warning := '"Generate Single Invoice" is applicable only when "VAT invoice number same as Excise invoice number" is set as No.';
4376 -- added by Allen Yang for bug 9737119, begin
4377 -------------------------------------------------
4378 Fnd_File.PUT_LINE(Fnd_File.LOG, lv_same_as_excise_conf_warning);
4379 -------------------------------------------------
4380 -- added by Allen Yang for bug 9737119, end
4381 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', lv_same_as_excise_conf_warning);
4382 retcode := '1';
4383 -- added by zhiwei.xin on 10-JAN-2013 for bug#16167577 wsh error handling begin
4384 errbuf := lv_same_as_excise_conf_warning;
4385 -- added by zhiwei.xin on 10-JAN-2013 for bug#16167577 wsh error handling end.
4386 END IF; -- (lv_Same_invoice_no = jai_constants.yes) AND ......
4387 -- added by Allen Yang for bug 9709477 13-May-2010, end
4388
4389 EXCEPTION
4390 WHEN OTHERS THEN
4391 RETCODE := '2';
4392 Fnd_File.PUT_LINE(Fnd_File.LOG,'Unexpected Error occured in procedure jai_cmn_rgm_processing_pkg.process '||substr(sqlerrm,1,300));
4393 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',substr(sqlerrm,1,1000));
4394 ERRBUF := substr(sqlerrm,1,1000);
4395 lv_inv_gen_process_flag := jai_constants.unexpected_error;
4396 lv_acct_process_flag := jai_constants.unexpected_error;
4397 lv_inv_gen_process_message := sqlerrm;
4398 lv_acct_process_message := sqlerrm;
4399
4400 END PROCESS;
4401
4402
4403 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, begin
4404 ----------------------------------------------------------------------------------------------
4405 PROCEDURE populate_repository(
4406 p_batch_id IN NUMBER,
4407 p_regime_id IN NUMBER,
4408 p_organization_type IN VARCHAR2,
4409 p_from_date IN DATE,
4410 p_to_date IN DATE,
4411 p_org_id IN NUMBER,
4412 p_organization_id IN NUMBER,
4413 p_process_flag OUT NOCOPY VARCHAR2,
4414 p_process_message OUT NOCOPY VARCHAR2) IS
4415
4416 CURSOR get_distibutions_cur( cp_ae_category IN VARCHAR2
4417 , cp_start_date IN DATE
4418 , cp_till_date IN DATE
4419 , cp_sob_id IN NUMBER) IS
4420 SELECT aid.invoice_id
4421 , aid.invoice_distribution_id
4422 , aid.distribution_line_number
4423 , aid.invoice_line_number
4424 , aid.reversal_flag
4425 , aid.parent_reversal_id
4426 , aid.accrual_posted_flag
4427 , aid.cash_posted_flag
4428 , aid.amount
4429 , aid.base_amount
4430 , aid.po_distribution_id
4431 , aid.rcv_transaction_id
4432 , aid.org_id
4433 , aid.accounting_event_id
4434 , ai.vendor_id
4435 , ai.vendor_site_id
4436 , ai.invoice_currency_code
4437 , aid.exchange_rate
4438 , aid.exchange_rate_type
4439 , aid.exchange_date
4440 , ai.source
4441 , ai.invoice_type_lookup_code
4442 , ai.invoice_date
4443 , aid.accounting_date
4444 , ai.cancelled_date
4445 FROM xla_ae_headers xah
4446 , xla_transaction_entities xte
4447 , ap_invoices_all ai
4448 , ap_invoice_distributions_all aid
4449 WHERE xah.je_category_name = cp_ae_category
4450 AND xah.ledger_id = cp_sob_id
4451 AND xah.application_id = 200
4452 AND xah.entity_id = xte.entity_id
4453 AND xte.application_id = 200
4454 AND xte.entity_code = 'AP_INVOICES'
4455 AND xte.source_id_int_1 = ai.invoice_id
4456 AND aid.invoice_id = ai.invoice_id
4457 AND aid.accounting_event_id = xah.event_id
4458 AND ai.invoice_type_lookup_code IN ('STANDARD', 'DEBIT', 'CREDIT')
4459 AND aid.parent_reversal_id IS NULL
4460 AND nvl(aid.reversal_flag, 'N') = 'N' /*12793930 - Ensure Repository entries are not passed if a line is discarded*/
4461 AND ( aid.line_type_lookup_code = jai_constants.misc_line
4462 OR EXISTS (SELECT 1
4463 FROM jai_rcv_tp_invoices jtp
4464 WHERE AID.invoice_id = jtp.invoice_id))
4465 AND aid.posted_flag = 'Y'
4466 AND xah.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
4467 AND aid.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
4468 AND ai.org_id = p_org_id
4469 AND aid.org_id = p_org_id
4470 AND NOT EXISTS( SELECT 1
4471 FROM jai_rgm_trx_records rgtr
4472 WHERE rgtr.source = 'AP'
4473 AND rgtr.organization_id = p_organization_id
4474 AND rgtr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4475 AND rgtr.source_document_id = aid.invoice_distribution_id)
4476 AND EXISTS(SELECT 1
4477 FROM jai_rgm_trx_refs rgtf
4478 WHERE rgtf.source = 'AP'
4479 AND rgtf.invoice_id = aid.invoice_id
4480 AND rgtf.organization_id = p_organization_id --Added by zhiwei for POT Bug#12970828 on 20110915
4481 AND rgtf.party_type = 'V');
4482
4483 CURSOR get_cancel_distibutions_cur ( cp_start_date IN DATE
4484 , cp_till_date IN DATE
4485 , cp_sob_id IN NUMBER) IS
4486 SELECT aid.invoice_id
4487 , aid.invoice_distribution_id
4488 , aid.distribution_line_number
4489 , aid.invoice_line_number
4490 , aid.reversal_flag
4491 , aid.parent_reversal_id
4492 , aid.accrual_posted_flag
4493 , aid.cash_posted_flag
4494 , aid.amount
4495 , aid.base_amount
4496 , aid.po_distribution_id
4497 , aid.rcv_transaction_id
4498 , aid.org_id
4499 , aid.accounting_event_id
4500 , ai.vendor_id
4501 , ai.vendor_site_id
4502 , ai.invoice_currency_code
4503 , aid.exchange_rate
4504 , aid.exchange_rate_type
4505 , aid.exchange_date
4506 , ai.source
4507 , ai.invoice_type_lookup_code
4508 , ai.invoice_date
4509 , aid.accounting_date
4510 , ai.cancelled_date
4511 FROM ap_invoices_all ai
4512 , ap_invoice_distributions_all aid
4513 WHERE aid.invoice_id = ai.invoice_id
4514 AND aid.set_of_books_id = cp_sob_id
4515 AND ai.invoice_type_lookup_code IN ('STANDARD', 'DEBIT', 'CREDIT')
4516 AND ai.cancelled_date IS NOT NULL
4517 AND aid.line_type_lookup_code = jai_constants.misc_line
4518 AND aid.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
4519 AND ai.org_id = p_org_id
4520 AND aid.org_id = p_org_id
4521 AND aid.parent_reversal_id IS NOT NULL
4522 AND aid.cancellation_flag = 'Y'
4523 AND EXISTS(SELECT 1
4524 FROM jai_rgm_trx_records rgtr
4525 WHERE rgtr.source = 'AP'
4526 AND rgtr.organization_id = p_organization_id
4527 AND rgtr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4528 AND rgtr.source_document_id = aid.parent_reversal_id )
4529 AND NOT EXISTS(SELECT 1
4530 FROM jai_rgm_trx_records rgtr
4531 WHERE rgtr.source = 'AP'
4532 AND rgtr.organization_id = p_organization_id
4533 AND rgtr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4534 AND rgtr.source_document_id = aid.invoice_distribution_id )
4535 AND EXISTS(SELECT 1
4536 FROM jai_rgm_trx_refs rgtf
4537 WHERE rgtf.source = 'AP'
4538 AND rgtf.invoice_id = aid.invoice_id
4539 AND rgtf.organization_id = p_organization_id--Added by zhiwei for POT Bug#12970828 on 20110915
4540 AND rgtf.party_type = 'V');
4541
4542 lv_source_table CONSTANT jai_rgm_trx_records.source_table_name%TYPE := 'AP_INVOICE_DISTRIBUTIONS_ALL';
4543 lv_called_from CONSTANT varchar2(100) := 'jai_cmn_rgm_processing_pkg.populate_repository';
4544 ln_repository_id NUMBER;
4545 ln_discounted_amt NUMBER := 0;
4546 ln_organization_id NUMBER := p_organization_id;
4547 ln_location_id NUMBER;
4548 lv_service_type_code VARCHAR2(30);
4549
4550 lv_process_flag VARCHAR2(15);
4551 lv_process_message VARCHAR2(4000);
4552 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
4553 ln_sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
4554
4555 CURSOR get_ap_ref_records_cur(pn_invoice_distribution_id NUMBER)IS
4556 SELECT *
4557 FROM jai_rgm_trx_refs rgtf
4558 WHERE line_id = pn_invoice_distribution_id
4559 AND party_type = 'V'
4560 AND SOURCE = 'AP';
4561
4562 CURSOR get_loc_cur(pn_invoice_id NUMBER, pn_invoice_line_number NUMBER) IS
4563 SELECT location_id, service_type_code
4564 FROM jai_ap_invoice_lines
4565 WHERE invoice_id = pn_invoice_id
4566 AND invoice_line_number = (SELECT parent_invoice_line_number
4567 FROM jai_ap_invoice_lines jail
4568 WHERE jail.invoice_line_number = pn_invoice_line_number
4569 AND jail.invoice_id = pn_invoice_id)
4570 AND parent_invoice_line_number IS NULL;
4571
4572 CURSOR c_get_curr_dtls (pn_invoice_id NUMBER) IS
4573 SELECT payment_currency_code,
4574 exchange_rate,
4575 exchange_date,
4576 exchange_rate_type
4577 FROM ap_invoices_all
4578 WHERE invoice_id = pn_invoice_id;
4579
4580 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
4581 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
4582 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
4583
4584 CURSOR c_get_st_accrual_date(p_organization_id NUMBER, p_location_id NUMBER) IS
4585 select to_date(attribute_value, 'DD/MM/YYYY')
4586 from JAI_RGM_ORG_REGNS_V
4587 where regime_id = (SELECT regime_id
4588 FROM JAI_RGM_DEFINITIONS
4589 WHERE regime_code = jai_constants.service_regime)
4590 and organization_id = p_organization_id
4591 and location_id = p_location_id
4592 AND attribute_code = 'EFF_DATE_ST_PT'
4593 AND attribute_type_code = 'OTHERS'
4594 AND registration_type = 'OTHERS'
4595 AND (NOT EXISTS
4596 (select '1'
4597 from JAI_RGM_ORG_REGNS_V
4598 where regime_id = p_regime_id
4599 and attribute_code IN 'INV_ORG_CLASSIFICATION'
4600 and attribute_value <> 'ORGANIZATION'
4601 and organization_id = p_organization_id
4602 and location_id = p_location_id)
4603 OR
4604 NOT EXISTS
4605 (select '1'
4606 from JAI_RGM_ORG_REGNS_V
4607 where regime_id = p_regime_id
4608 and attribute_code IN 'SERVICE TYPE'
4609 and attribute_value <> 'OTHER'
4610 and organization_id = p_organization_id
4611 and location_id = p_location_id)
4612 );
4613
4614 /*Bug 12805386 - End*/
4615
4616 ld_st_accrual_date DATE;
4617 rec_get_curr_dtls c_get_curr_dtls%rowtype;
4618 lv_accounting_event VARCHAR2(30);
4619 ld_accounting_date DATE;
4620
4621
4622 CURSOR check_same_period_cur(pn_parent_reversal_id NUMBER) IS
4623 SELECT 'Y'
4624 FROM ap_invoice_distributions_all aid1,
4625 ap_invoice_distributions_all aid2
4626 WHERE aid1.parent_reversal_id = pn_parent_reversal_id
4627 AND aid2.invoice_distribution_id = pn_parent_reversal_id
4628 AND aid1.period_name = aid2.period_name;
4629
4630 lv_same_period_flag VARCHAR2(1);
4631 lv_account_name VARCHAR2(30);
4632 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
4633 ----------------------------------------------------------------------------------------------
4634 --Modified by Chong for bug#13358557 08-NOV-2011, begin
4635 ----------------------------------------------------------------------------------------------
4636 -- CURSOR get_total_rec_amt_cur(pn_invoice_id IN NUMBER) IS
4637 CURSOR get_total_rec_amt_cur(pn_invoice_id IN NUMBER, pn_line_number IN NUMBER) IS
4638 ----------------------------------------------------------------------------------------------
4639 --Modified by Chong for bug#13358557 08-NOV-2011, end
4640 SELECT SUM(tax_amt)
4641 FROM jai_cmn_document_taxes jcdt,
4642 jai_cmn_taxes_all jcta
4643 WHERE jcdt.source_doc_id = pn_invoice_id
4644 --Add by Chong for bug#13358557 08-NOV-2011, begin
4645 ----------------------------------------------------------------------------------------------
4646 AND jcdt.source_doc_parent_line_no = pn_line_number
4647 ----------------------------------------------------------------------------------------------
4648 --Add by Chong for bug#13358557 08-NOV-2011, end
4649 AND jcdt.modvat_flag = 'Y'
4650 AND jcdt.tax_id = jcta.tax_id
4651 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
4652
4653 --Modified by Chong for bug#13358557 08-NOV-2011, begin
4654 ----------------------------------------------------------------------------------------------
4655 -- CURSOR get_matched_tax_rec_tot_cur(pn_invoice_id IN NUMBER) IS
4656 CURSOR get_matched_tax_rec_tot_cur(pn_invoice_id IN NUMBER, pn_line_number IN NUMBER) IS
4657 ----------------------------------------------------------------------------------------------
4658 --Modified by Chong for bug#13358557 08-NOV-2011, end
4659 SELECT sum(jamt.tax_amount)
4660 FROM jai_ap_match_inv_taxes jamt,
4661 jai_cmn_taxes_all jcta
4662 WHERE jamt.invoice_id = pn_invoice_id
4663 --Add by Chong for bug#13358557 08-NOV-2011, begin
4664 ----------------------------------------------------------------------------------------------
4665 AND jamt.parent_invoice_line_number = pn_line_number
4666 ----------------------------------------------------------------------------------------------
4667 --Add by Chong for bug#13358557 08-NOV-2011, end
4668 AND jamt.tax_id = jcta.tax_id
4669 AND jamt.recoverable_flag = 'Y'
4670 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
4671
4672 CURSOR get_reversal_entries_cur(pn_invoice_id IN NUMBER, pn_line_number IN NUMBER) IS
4673 SELECT jcta.tax_type, jcdt.tax_amt, aida.invoice_distribution_id,
4674 aida.accounting_date, aia.invoice_date, aida.po_distribution_id
4675 FROM jai_cmn_document_taxes jcdt
4676 , jai_cmn_taxes_all jcta
4677 , ap_invoice_distributions_all aida
4678 , ap_invoices_all aia
4679 WHERE jcdt.source_doc_id = pn_invoice_id
4680 AND jcdt.source_doc_parent_line_no = pn_line_number
4681 AND aia.invoice_id = pn_invoice_id
4682 AND aida.invoice_id = pn_invoice_id
4683 AND aida.invoice_line_number = jcdt.source_doc_line_id
4684 AND aida.po_distribution_id IS NULL
4685 AND jcdt.modvat_flag = 'Y'
4686 AND jcdt.tax_id = jcta.tax_id
4687 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
4688 AND NOT EXISTS (SELECT 1
4689 FROM jai_rgm_trx_records
4690 WHERE SOURCE = 'AP_REVERSAL'
4691 AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4692 AND source_document_id = aida.invoice_distribution_id)
4693 AND EXISTS(SELECT 1
4694 FROM jai_rgm_trx_refs rgtf
4695 WHERE rgtf.source = 'AP'
4696 AND rgtf.invoice_id = pn_invoice_id
4697 AND rgtf.party_type = 'V')
4698 UNION ALL
4699 SELECT jcta.tax_type, jamt.tax_amount tax_amt, aida.invoice_distribution_id,
4700 aida.accounting_date, aia.invoice_date, aida.po_distribution_id
4701 FROM jai_ap_match_inv_taxes jamt
4702 , jai_cmn_taxes_all jcta
4703 , ap_invoice_distributions_all aida
4704 , ap_invoices_all aia
4705 WHERE jamt.invoice_id = pn_invoice_id
4706 --AND jamt.invoice_line_number <> pn_line_number
4707 AND jamt.parent_invoice_line_number = pn_line_number
4708 AND jamt.invoice_distribution_id = aida.invoice_distribution_id
4709 AND jamt.tax_id = jcta.tax_id
4710 AND jamt.recoverable_flag = 'Y'
4711 AND aida.po_distribution_id IS NOT NULL
4712 AND aia.invoice_id = pn_invoice_id
4713 AND aida.invoice_id = pn_invoice_id
4714 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
4715 AND NOT EXISTS (SELECT 1
4716 FROM jai_rgm_trx_records
4717 WHERE SOURCE = 'AP_REVERSAL'
4718 AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4719 AND source_document_id = aida.invoice_distribution_id)
4720 AND EXISTS(SELECT 1
4721 FROM jai_rgm_trx_refs rgtf
4722 WHERE rgtf.source = 'AP'
4723 AND rgtf.invoice_id = pn_invoice_id
4724 AND rgtf.party_type = 'V');
4725
4726 CURSOR get_claim_entries_cur(pn_invoice_id IN NUMBER, pn_line_number IN NUMBER, pn_invoice_payment_id IN NUMBER) IS
4727 SELECT jcta.tax_type, jcdt.tax_amt, aida.invoice_distribution_id,
4728 aida.accounting_date, aia.invoice_date, aida.po_distribution_id
4729 FROM jai_cmn_document_taxes jcdt
4730 , jai_cmn_taxes_all jcta
4731 , ap_invoices_all aia
4732 , ap_invoice_distributions_all aida
4733 WHERE jcdt.source_doc_id = pn_invoice_id
4734 AND jcdt.source_doc_parent_line_no = pn_line_number
4735 AND aia.invoice_id = pn_invoice_id
4736 AND aida.invoice_id = pn_invoice_id
4737 AND aida.invoice_line_number = jcdt.source_doc_line_id
4738 AND aida.po_distribution_id IS NULL
4739 AND jcdt.modvat_flag = 'Y'
4740 AND jcdt.tax_id = jcta.tax_id
4741 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
4742 AND EXISTS (SELECT 1
4743 FROM jai_rgm_trx_records
4744 WHERE SOURCE = 'AP_REVERSAL'
4745 AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4746 AND source_document_id = aida.invoice_distribution_id)
4747 AND NOT EXISTS (SELECT 1
4748 FROM jai_rgm_trx_records jrtr,
4749 jai_rgm_trx_refs jrtf --Added by Qiong for bug13439861
4750 WHERE jrtr.SOURCE = 'AP_CLAIM'
4751 AND jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
4752 AND jrtr.source_document_id = pn_invoice_payment_id
4753 --Added by Qiong for bug13439861 begin
4754 -------------------------------------------
4755 AND jrtr.reference_id = jrtf.reference_id
4756 AND jrtf.item_line_id = pn_line_number
4757 -------------------------------------------
4758 --Added by Qiong for bug13439861 end
4759 )
4760 UNION ALL
4761 SELECT jcta.tax_type, jamt.tax_amount tax_amt, aida.invoice_distribution_id,
4762 aida.accounting_date, aia.invoice_date, aida.po_distribution_id
4763 FROM jai_ap_match_inv_taxes jamt
4764 , jai_cmn_taxes_all jcta
4765 , ap_invoice_distributions_all aida
4766 , ap_invoices_all aia
4767 WHERE jamt.invoice_id = pn_invoice_id
4768 --AND jamt.invoice_line_number <> pn_line_number
4769 AND jamt.parent_invoice_line_number = pn_line_number
4770 AND jamt.invoice_distribution_id = aida.invoice_distribution_id
4771 AND jamt.tax_id = jcta.tax_id
4772 AND jamt.recoverable_flag = 'Y'
4773 AND aida.po_distribution_id IS NOT NULL
4774 AND aia.invoice_id = pn_invoice_id
4775 AND aida.invoice_id = pn_invoice_id
4776 AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
4777 AND EXISTS (SELECT 1
4778 FROM jai_rgm_trx_records
4779 WHERE SOURCE = 'AP_REVERSAL'
4780 AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4781 AND source_document_id = aida.invoice_distribution_id)
4782 AND NOT EXISTS (SELECT 1
4783 FROM jai_rgm_trx_records jrtr,
4784 jai_rgm_trx_refs jrtf--Added by Qiong for bug13439861
4785 WHERE jrtr.SOURCE = 'AP_CLAIM'
4786 AND jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
4787 AND jrtr.source_document_id = pn_invoice_payment_id
4788 --Added by Qiong for bug13439861 begin
4789 -----------------------------------------
4790 AND jrtr.reference_id = jrtf.reference_id
4791 AND jrtf.item_line_id = pn_line_number
4792 -----------------------------------------
4793 --Added by Qiong for bug13439861 end
4794 );
4795
4796 CURSOR get_total_amount(pn_invoice_id IN NUMBER) IS
4797 SELECT SUM(amount)
4798 FROM ap_invoice_lines_all
4799 WHERE invoice_id = pn_invoice_id
4800 AND line_type_lookup_code IN ('ITEM', 'MISCELLANEOUS')
4801 GROUP BY invoice_id;
4802
4803 CURSOR get_claim_trxn_cur IS
4804 SELECT jsir.*
4805 FROM jai_st_invoice_reversal jsir
4806 WHERE jsir.source = 'AP'
4807 --Add by Chong for bug#13259755 on 27-OCT-2011 start
4808 AND jsir.organization_id = p_organization_id
4809 --Add by Chong for bug#13259755 on 27-OCT-2011 end
4810 AND jsir.tax_to_be_adjusted > 0
4811 ORDER BY jsir.invoice_id;
4812
4813 CURSOR get_reversal_trxn_cur IS
4814 SELECT jsir.*
4815 FROM jai_st_invoice_reversal jsir
4816 WHERE jsir.source = 'AP'
4817 AND jsir.date_of_reversal BETWEEN p_from_date AND p_to_date;
4818
4819 CURSOR get_claim_amt_cur(pn_invoice_id IN NUMBER, pn_line_number IN NUMBER, pn_max_payment_id IN NUMBER) IS
4820 SELECT nvl(aipa.amount, 0) claim_amt, aipa.invoice_payment_id, aipa.accounting_date
4821 FROM ap_invoice_payments_all aipa
4822 WHERE aipa.invoice_id = pn_invoice_id
4823 AND aipa.accounting_date <= p_to_date
4824 -- AND aipa.invoice_payment_id > nvl(pn_max_payment_id, 0) Commented by Chong for bug#13259755 on 27-OCT-2011
4825 AND NOT EXISTS (SELECT 1
4826 FROM jai_rgm_trx_records jrtr,
4827 jai_rgm_trx_refs jrtf--Added by Qiong for bug13439861
4828 WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
4829 AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
4830 AND jrtr.source = 'AP_CLAIM'
4831 AND jrtr.source_document_id = aipa.invoice_payment_id
4832 --Added by Qiong for bug13439861 begin
4833 -----------------------------------------
4834 AND jrtr.reference_id = jrtf.reference_id
4835 AND jrtf.item_line_id = pn_line_number
4836 -----------------------------------------
4837 --Added by Qiong for bug13439861 end
4838 )
4839 AND EXISTS (SELECT 1
4840 FROM ap_invoice_distributions_all aida
4841 WHERE aida.invoice_id = pn_invoice_id
4842 AND aida.invoice_line_number = pn_line_number
4843 AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aipa.accounting_date,
4844 aida.accounting_date, 'AP') = 'Y')
4845 UNION ALL
4846 SELECT abs(nvl(aila.amount, 0)) claim_amt, aipa.invoice_payment_id, aipa.accounting_date
4847 FROM ap_invoice_lines_all aila,
4848 ap_invoice_payments_all aipa
4849 WHERE aila.invoice_id = pn_invoice_id
4850 AND aila.line_type_lookup_code = 'PREPAY'
4851 AND aila.prepay_invoice_id = aipa.invoice_id
4852 AND aila.accounting_date <= p_to_date
4853 AND nvl(aila.amount, 0) <> 0
4854 /*Commented by Chong for bug#13259755 on 09-NOV-2011 Start
4855 AND (aipa.invoice_payment_id > nvl(pn_max_payment_id, 0)
4856 OR (aipa.invoice_payment_id < nvl(pn_max_payment_id, 0)
4857 AND NOT EXISTS (SELECT 1
4858 FROM jai_rgm_trx_records jrtr
4859 WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
4860 AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
4861 AND jrtr.source = 'AP_CLAIM'
4862 AND jrtr.source_document_id = aipa.invoice_payment_id)))
4863 Commented by Chong for bug#13259755 on 09-NOV-2011 End*/
4864 AND NOT EXISTS (SELECT 1
4865 FROM jai_rgm_trx_records jrtr,
4866 jai_rgm_trx_refs jrtf--Added by Qiong for bug13439861
4867 WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
4868 AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
4869 AND jrtr.source = 'AP_CLAIM'
4870 AND jrtr.source_document_id = aipa.invoice_payment_id
4871 --Added by Qiong for bug13439861 begin
4872 -----------------------------------------
4873 AND jrtr.reference_id = jrtf.reference_id
4874 AND jrtf.item_line_id = pn_line_number
4875 -----------------------------------------
4876 --Added by Qiong for bug13439861 end
4877 )
4878 AND EXISTS (SELECT 1
4879 FROM ap_invoice_distributions_all aida
4880 WHERE aida.invoice_id = pn_invoice_id
4881 AND aida.invoice_line_number = pn_line_number
4882 AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aila.accounting_date,
4883 aida.accounting_date, 'AP') = 'Y')
4884 ORDER BY 2;
4885
4886 CURSOR get_service_type_code_cur(pn_invoice_id NUMBER, pn_invoice_line_number NUMBER) IS
4887 SELECT service_type_code
4888 FROM jai_ap_invoice_lines
4889 WHERE invoice_id = pn_invoice_id
4890 AND invoice_line_number = pn_invoice_line_number
4891 AND parent_invoice_line_number IS NULL;
4892
4893 lr_trx_refs jai_rgm_trx_refs%ROWTYPE;
4894
4895 ln_reversal_amount NUMBER;
4896 ln_tax_amount NUMBER;
4897 ln_rec_total_tax_amount NUMBER;
4898 ld_reversal_date DATE;
4899 ln_total_amount NUMBER;
4900 l_organization_id NUMBER; /*Bug 12805386*/
4901 l_location_id NUMBER; /*Bug 12805386*/
4902
4903 ----------------------------------------------------------------------------------------------
4904 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
4905
4906 BEGIN
4907
4908 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => p_org_id );
4909 ln_sob_id := l_func_curr_det.ledger_id;
4910
4911 FOR rec_ap_dist IN get_distibutions_cur('Purchase Invoices', p_from_date, p_to_date, ln_sob_id)
4912 LOOP
4913 -- Added by Qiong for reverse charge bug#16001407 begin
4914 IF jai_cmn_reverse_charge_pkg.is_reverse_charge_dist(rec_ap_dist.invoice_distribution_id)= 'Y' THEN
4915 null;
4916 else
4917 -- Added by Qiong for reverse charge bug#16001407 end
4918
4919 FOR rec_ap_ref_records IN get_ap_ref_records_cur(rec_ap_dist.invoice_distribution_id)
4920 LOOP
4921 /*Bug 12805386 - Start*/
4922 get_org_location(rec_ap_dist.invoice_id, rec_ap_dist.invoice_line_number, rec_ap_dist.po_distribution_id, l_organization_id, l_location_id);
4923
4924 OPEN c_get_st_accrual_date(l_organization_id, l_location_id);
4925 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
4926 CLOSE c_get_st_accrual_date;
4927 /*Bug 12805386 - End*/
4928 IF rec_ap_dist.accounting_date >= ld_st_accrual_date THEN
4929
4930 OPEN get_loc_cur(rec_ap_dist.invoice_id, rec_ap_dist.invoice_line_number);
4931 FETCH get_loc_cur INTO ln_location_id, lv_service_type_code;
4932 CLOSE get_loc_cur;
4933
4934 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => rec_ap_ref_records.reference_id,
4935 p_organization_id => ln_organization_id,
4936 p_location_id => ln_location_id,
4937 p_service_type_code => lv_service_type_code,
4938 p_process_flag => lv_process_flag,
4939 p_process_message => lv_process_message);
4940
4941 IF lv_process_flag <> jai_constants.successful THEN
4942 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
4943 RETURN;
4944 END IF;
4945
4946 OPEN c_get_curr_dtls(rec_ap_dist.invoice_id);
4947 FETCH c_get_curr_dtls INTO rec_get_curr_dtls;
4948 CLOSE c_get_curr_dtls;
4949
4950 lv_accounting_event := 'INVOICE_ACCOUNTING';
4951 ld_accounting_date := rec_ap_dist.accounting_date;
4952
4953 jai_cmn_rgm_recording_pkg.insert_repository_entry(
4954 p_repository_id => ln_repository_id,
4955 p_regime_id => p_regime_id,
4956 p_tax_type => rec_ap_ref_records.tax_type,
4957 p_organization_type => p_organization_type,
4958 p_organization_id => ln_organization_id,
4959 p_location_id => ln_location_id,
4960 p_service_type_code => lv_service_type_code,
4961 p_source => 'AP',
4962 p_source_trx_type => lv_accounting_event,
4963 p_source_table_name => lv_source_table,
4964 p_source_document_id => rec_ap_dist.invoice_distribution_id,
4965 p_transaction_date => ld_accounting_date, --rec_ap_dist.invoice_date, /* changes made for bug 13242818 by amandali */
4966 p_account_name => NULL,
4967 p_charge_account_id => NULL,
4968 p_balancing_account_id => NULL,
4969 p_amount => rec_ap_ref_records.tax_amount,
4970 p_assessable_value => NULL,
4971 p_tax_rate => rec_ap_ref_records.tax_rate,
4972 p_reference_id => rec_ap_ref_records.reference_id,
4973 p_batch_id => p_batch_id,
4974 p_called_from => lv_called_from,
4975 p_process_flag => lv_process_flag,
4976 p_process_message => lv_process_message,
4977 p_discounted_amount => ln_discounted_amt,
4978 p_inv_organization_id => ln_organization_id,
4979 p_accounting_date => ld_accounting_date,
4980 p_currency_code => rec_get_curr_dtls.payment_currency_code,
4981 p_curr_conv_date => rec_get_curr_dtls.exchange_date,
4982 p_curr_conv_type => rec_get_curr_dtls.exchange_rate_type,
4983 p_curr_conv_rate => rec_get_curr_dtls.exchange_rate,
4984 p_trx_amount => rec_ap_ref_records.trx_tax_amount,
4985 p_accntg_required_flag => jai_constants.no,
4986 p_accrual_basis => 'Y');
4987
4988
4989 IF lv_process_flag = jai_constants.expected_error OR
4990 lv_process_flag = jai_constants.unexpected_error THEN
4991 fnd_file.put_line( fnd_file.log,
4992 '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4993 ||', lv_process_message'||lv_process_message);
4994 p_process_flag := lv_process_flag;
4995 p_process_message := lv_process_message;
4996 END IF;
4997 END IF; --rec_ap_ref_records.accounting_date > ld_st_accrual_date
4998 END LOOP;
4999 END IF;-- Added by Qiong for Reverse Charge bug16001407
5000
5001 END LOOP;
5002
5003 FOR rec_ap_dist IN get_cancel_distibutions_cur( p_from_date, p_to_date, ln_sob_id)
5004 LOOP
5005 FOR rec_ap_ref_records IN get_ap_ref_records_cur(rec_ap_dist.parent_reversal_id)
5006 LOOP
5007 /*Bug 12805386 - Start*/
5008 get_org_location(rec_ap_dist.invoice_id, rec_ap_dist.invoice_line_number, rec_ap_dist.po_distribution_id, l_organization_id, l_location_id);
5009
5010 OPEN c_get_st_accrual_date(l_organization_id, l_location_id);
5011 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
5012 CLOSE c_get_st_accrual_date;
5013 /*Bug 12805386 - End*/
5014 IF rec_ap_dist.accounting_date >= ld_st_accrual_date THEN
5015
5016 OPEN get_loc_cur(rec_ap_dist.invoice_id, rec_ap_dist.invoice_line_number);
5017 FETCH get_loc_cur INTO ln_location_id, lv_service_type_code;
5018 CLOSE get_loc_cur;
5019
5020 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => rec_ap_ref_records.reference_id,
5021 p_organization_id => ln_organization_id,
5022 p_location_id => ln_location_id,
5023 p_service_type_code => lv_service_type_code,
5024 p_process_flag => lv_process_flag,
5025 p_process_message => lv_process_message);
5026
5027 IF lv_process_flag <> jai_constants.successful THEN
5028 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
5029 RETURN;
5030 END IF;
5031
5032 OPEN c_get_curr_dtls(rec_ap_dist.invoice_id);
5033 FETCH c_get_curr_dtls INTO rec_get_curr_dtls;
5034 CLOSE c_get_curr_dtls;
5035
5036 lv_accounting_event := 'INVOICE_CANCELLATION';
5037 ld_accounting_date := rec_ap_dist.cancelled_date;
5038
5039 OPEN check_same_period_cur(rec_ap_dist.parent_reversal_id);
5040 FETCH check_same_period_cur INTO lv_same_period_flag;
5041 CLOSE check_same_period_cur;
5042
5043 IF lv_same_period_flag = 'Y' THEN
5044 lv_account_name := jai_constants.recovery;
5045 ELSE
5046 lv_account_name := jai_constants.liability;
5047 END IF;
5048
5049 jai_cmn_rgm_recording_pkg.insert_repository_entry(
5050 p_repository_id => ln_repository_id,
5051 p_regime_id => p_regime_id,
5052 p_tax_type => rec_ap_ref_records.tax_type,
5053 p_organization_type => p_organization_type,
5054 p_organization_id => ln_organization_id,
5055 p_location_id => ln_location_id,
5056 p_service_type_code => lv_service_type_code,
5057 p_source => 'AP',
5058 p_source_trx_type => lv_accounting_event,
5059 p_source_table_name => lv_source_table,
5060 p_source_document_id => rec_ap_dist.invoice_distribution_id,
5061 p_transaction_date => ld_accounting_date,
5062 p_account_name => lv_account_name,
5063 p_charge_account_id => NULL,
5064 p_balancing_account_id => NULL,
5065 p_amount => rec_ap_ref_records.tax_amount,
5066 p_assessable_value => NULL,
5067 p_tax_rate => rec_ap_ref_records.tax_rate,
5068 p_reference_id => rec_ap_ref_records.reference_id,
5069 p_batch_id => p_batch_id,
5070 p_called_from => lv_called_from,
5071 p_process_flag => lv_process_flag,
5072 p_process_message => lv_process_message,
5073 p_discounted_amount => ln_discounted_amt,
5074 p_inv_organization_id => ln_organization_id,
5075 p_accounting_date => ld_accounting_date,
5076 p_currency_code => rec_get_curr_dtls.payment_currency_code,
5077 p_curr_conv_date => rec_get_curr_dtls.exchange_date,
5078 p_curr_conv_type => rec_get_curr_dtls.exchange_rate_type,
5079 p_curr_conv_rate => rec_get_curr_dtls.exchange_rate,
5080 p_trx_amount => rec_ap_ref_records.trx_tax_amount,
5081 p_accntg_required_flag => jai_constants.no,
5082 p_accrual_basis => 'Y');
5083
5084 IF lv_process_flag = jai_constants.expected_error OR
5085 lv_process_flag = jai_constants.unexpected_error THEN
5086 fnd_file.put_line( fnd_file.log,
5087 '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
5088 ||', lv_process_message'||lv_process_message);
5089 p_process_flag := lv_process_flag;
5090 p_process_message := lv_process_message;
5091 END IF;
5092 END IF; --rec_ap_ref_records.accounting_date > ld_st_accrual_date
5093 END LOOP;
5094 END LOOP;
5095
5096 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
5097 ----------------------------------------------------------------------------------------------
5098
5099 FOR reversal_trxn_rec IN get_reversal_trxn_cur
5100 LOOP
5101
5102 ln_organization_id := reversal_trxn_rec.organization_id;
5103 ln_location_id := reversal_trxn_rec.location_id;
5104 ln_reversal_amount := reversal_trxn_rec.reversal_amount;
5105 ld_accounting_date := reversal_trxn_rec.date_of_reversal;
5106
5107 OPEN c_get_curr_dtls(reversal_trxn_rec.invoice_id);
5108 FETCH c_get_curr_dtls INTO rec_get_curr_dtls;
5109 CLOSE c_get_curr_dtls;
5110 FND_FILE.put_line(fnd_file.log, 'reversal enter1 reversal_trxn_rec.invoice_id:'||reversal_trxn_rec.invoice_id);
5111 FND_FILE.put_line(fnd_file.log, 'reversal enter1 reversal_trxn_rec.line_num:'||reversal_trxn_rec.line_num);
5112 FOR rec_reversal_entries IN get_reversal_entries_cur(reversal_trxn_rec.invoice_id, reversal_trxn_rec.line_num)
5113 LOOP
5114 -- Added by Qiong for Reverse Charge code port bug16001407 begin
5115 IF jai_cmn_reverse_charge_pkg.is_reverse_charge_dist(rec_reversal_entries.invoice_distribution_id)= 'Y' THEN
5116 null;
5117 else
5118 -- Added by Qiong for Reverse Charge code port bug16001407 end
5119
5120 OPEN get_ap_ref_records_cur(rec_reversal_entries.invoice_distribution_id);
5121 FETCH get_ap_ref_records_cur INTO lr_trx_refs;
5122 CLOSE get_ap_ref_records_cur;
5123
5124 IF rec_reversal_entries.po_distribution_id IS NULL THEN
5125 OPEN get_service_type_code_cur(reversal_trxn_rec.invoice_id, reversal_trxn_rec.line_num);
5126 FETCH get_service_type_code_cur INTO lv_service_type_code;
5127 CLOSE get_service_type_code_cur;
5128
5129 --Modified by Chong for bug#13358557 08-NOV-2011, begin
5130 --OPEN get_total_rec_amt_cur(reversal_trxn_rec.invoice_id);
5131 OPEN get_total_rec_amt_cur(reversal_trxn_rec.invoice_id, reversal_trxn_rec.line_num);
5132 --Modified by Chong for bug#13358557 08-NOV-2011, end
5133 FETCH get_total_rec_amt_cur INTO ln_rec_total_tax_amount;
5134 CLOSE get_total_rec_amt_cur;
5135 ELSE
5136 --Modified by Chong for bug#13358557 08-NOV-2011, begin
5137 --OPEN get_matched_tax_rec_tot_cur(reversal_trxn_rec.invoice_id);
5138 OPEN get_matched_tax_rec_tot_cur(reversal_trxn_rec.invoice_id, reversal_trxn_rec.line_num);
5139 --Modified by Chong for bug#13358557 08-NOV-2011, end
5140 FETCH get_matched_tax_rec_tot_cur INTO ln_rec_total_tax_amount;
5141 CLOSE get_matched_tax_rec_tot_cur;
5142 END IF;
5143 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => lr_trx_refs.reference_id,
5144 p_organization_id => ln_organization_id,
5145 p_location_id => ln_location_id,
5146 p_service_type_code => lv_service_type_code,
5147 p_process_flag => lv_process_flag,
5148 p_process_message => lv_process_message);
5149
5150 IF lv_process_flag <> jai_constants.successful THEN
5151 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
5152 RETURN;
5153 END IF;
5154
5155 ln_tax_amount := rec_reversal_entries.tax_amt;
5156
5157
5158 ln_tax_amount := round(ln_reversal_amount * ln_tax_amount/ln_rec_total_tax_amount, jai_constants.service_rgm_rnd_factor);
5159
5160
5161 jai_cmn_rgm_recording_pkg.insert_repository_entry(
5162 p_repository_id => ln_repository_id,
5163 p_regime_id => p_regime_id,
5164 p_tax_type => rec_reversal_entries.tax_type,
5165 p_organization_type => p_organization_type,
5166 p_organization_id => ln_organization_id,
5167 p_location_id => ln_location_id,
5168 p_service_type_code => lv_service_type_code,
5169 p_source => 'AP_REVERSAL',
5170 p_source_trx_type => 'REVERSAL_ACCOUNTING',
5171 p_source_table_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
5172 p_source_document_id => rec_reversal_entries.invoice_distribution_id,
5173 --p_transaction_date => rec_reversal_entries.invoice_date,--Commented by zhiwei for POT Bug#12970828 on 20110913
5174 p_transaction_date => reversal_trxn_rec.date_of_reversal, --Added by zhiwei for POT Bug#12970828 on 20110913
5175 p_account_name => NULL,
5176 p_charge_account_id => NULL,
5177 p_balancing_account_id => NULL,
5178 p_amount => ln_tax_amount,
5179 p_assessable_value => NULL,
5180 p_tax_rate => lr_trx_refs.tax_rate,
5181 p_reference_id => lr_trx_refs.reference_id,
5182 p_batch_id => p_batch_id,
5183 p_called_from => lv_called_from,
5184 p_process_flag => lv_process_flag,
5185 p_process_message => lv_process_message,
5186 p_discounted_amount => ln_discounted_amt,
5187 p_inv_organization_id => ln_organization_id,
5188 p_accounting_date => ld_accounting_date,
5189 p_currency_code => rec_get_curr_dtls.payment_currency_code,
5190 p_curr_conv_date => rec_get_curr_dtls.exchange_date,
5191 p_curr_conv_type => rec_get_curr_dtls.exchange_rate_type,
5192 p_curr_conv_rate => rec_get_curr_dtls.exchange_rate,
5193 p_trx_amount => ln_tax_amount,--lr_trx_refs.trx_tax_amount,
5194 p_accntg_required_flag => jai_constants.no,
5195 p_accrual_basis => 'Y');
5196
5197 IF lv_process_flag = jai_constants.expected_error OR
5198 lv_process_flag = jai_constants.unexpected_error THEN
5199 fnd_file.put_line( fnd_file.log,
5200 '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
5201 ||', lv_process_message'||lv_process_message);
5202 p_process_flag := lv_process_flag;
5203 p_process_message := lv_process_message;
5204 END IF;
5205 END IF; -- Qiong for Reverse Charge code port bug16001407
5206 END LOOP;
5207
5208 END LOOP;
5209
5210 FOR reversal_trxn_rec IN get_claim_trxn_cur
5211 LOOP
5212
5213 ln_organization_id := reversal_trxn_rec.organization_id;
5214 ln_location_id := reversal_trxn_rec.location_id;
5215
5216 OPEN get_total_amount(reversal_trxn_rec.invoice_id);
5217 FETCH get_total_amount INTO ln_total_amount;
5218 CLOSE get_total_amount;
5219
5220 OPEN c_get_curr_dtls(reversal_trxn_rec.invoice_id);
5221 FETCH c_get_curr_dtls INTO rec_get_curr_dtls;
5222 CLOSE c_get_curr_dtls;
5223
5224 FOR rec_claim_line IN get_claim_amt_cur(reversal_trxn_rec.invoice_id,reversal_trxn_rec.line_num,
5225 reversal_trxn_rec.max_claim_payment_id)
5226 LOOP
5227
5228 FOR rec_claim_entries IN get_claim_entries_cur(reversal_trxn_rec.invoice_id, reversal_trxn_rec.line_num,
5229 rec_claim_line.invoice_payment_id)
5230 LOOP
5231 -- Added by Qiong for Reverse Charge code port bug16001407 begin
5232 IF jai_cmn_reverse_charge_pkg.is_reverse_charge_dist(rec_claim_entries.invoice_distribution_id)= 'Y' THEN
5233 null;
5234 else
5235 -- Added by Qiong for Reverse Charge code port bug16001407 end
5236
5237 OPEN get_ap_ref_records_cur(rec_claim_entries.invoice_distribution_id);
5238 FETCH get_ap_ref_records_cur INTO lr_trx_refs;
5239 CLOSE get_ap_ref_records_cur;
5240
5241 IF rec_claim_entries.po_distribution_id IS NULL THEN
5242 OPEN get_service_type_code_cur(reversal_trxn_rec.invoice_id, reversal_trxn_rec.line_num);
5243 FETCH get_service_type_code_cur INTO lv_service_type_code;
5244 CLOSE get_service_type_code_cur;
5245 END IF;
5246
5247 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => lr_trx_refs.reference_id,
5248 p_organization_id => ln_organization_id,
5249 p_location_id => ln_location_id,
5250 p_service_type_code => lv_service_type_code,
5251 p_process_flag => lv_process_flag,
5252 p_process_message => lv_process_message);
5253
5254 IF lv_process_flag <> jai_constants.successful THEN
5255 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
5256 RETURN;
5257 END IF;
5258
5259 ld_accounting_date := rec_claim_line.accounting_date;
5260
5261 ln_tax_amount := round(nvl(rec_claim_line.claim_amt, 0) * rec_claim_entries.tax_amt/ln_total_amount,
5262 jai_constants.service_rgm_rnd_factor);
5263
5264 IF nvl(ln_tax_amount, 0) > 0 THEN
5265 jai_cmn_rgm_recording_pkg.insert_repository_entry(
5266 p_repository_id => ln_repository_id,
5267 p_regime_id => p_regime_id,
5268 p_tax_type => rec_claim_entries.tax_type,
5269 p_organization_type => p_organization_type,
5270 p_organization_id => ln_organization_id,
5271 p_location_id => ln_location_id,
5272 p_service_type_code => lv_service_type_code,
5273 p_source => 'AP_CLAIM',
5274 p_source_trx_type => 'CLAIM_ACCOUNTING',
5275 p_source_table_name => 'AP_INVOICE_PAYMENTS_ALL',
5276 p_source_document_id => rec_claim_line.invoice_payment_id,
5277 p_transaction_date => ld_accounting_date,
5278 p_account_name => NULL,
5279 p_charge_account_id => NULL,
5280 p_balancing_account_id => NULL,
5281 p_amount => ln_tax_amount,
5282 p_assessable_value => NULL,
5283 p_tax_rate => lr_trx_refs.tax_rate,
5284 p_reference_id => lr_trx_refs.reference_id,
5285 p_batch_id => p_batch_id,
5286 p_called_from => lv_called_from,
5287 p_process_flag => lv_process_flag,
5288 p_process_message => lv_process_message,
5289 p_discounted_amount => ln_discounted_amt,
5290 p_inv_organization_id => ln_organization_id,
5291 p_accounting_date => ld_accounting_date,
5292 p_currency_code => rec_get_curr_dtls.payment_currency_code,
5293 p_curr_conv_date => rec_get_curr_dtls.exchange_date,
5294 p_curr_conv_type => rec_get_curr_dtls.exchange_rate_type,
5295 p_curr_conv_rate => rec_get_curr_dtls.exchange_rate,
5296 p_trx_amount => ln_tax_amount,--lr_trx_refs.trx_tax_amount,
5297 p_accntg_required_flag => jai_constants.no,
5298 p_accrual_basis => 'Y');
5299
5300 IF lv_process_flag = jai_constants.expected_error OR
5301 lv_process_flag = jai_constants.unexpected_error THEN
5302 fnd_file.put_line( fnd_file.log,
5303 '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
5304 ||', lv_process_message'||lv_process_message);
5305 p_process_flag := lv_process_flag;
5306 p_process_message := lv_process_message;
5307 END IF;
5308 END IF;
5309 END IF; -- Qiong for Reverse Charge code port bug16001407
5310 END LOOP; --rec_claim_entries IN get_claim_entries_cur
5311 END LOOP; --rec_claim_line IN get_claim_amt_cur
5312 END LOOP; --reversal_trxn_rec IN get_claim_trxn_cur
5313 ----------------------------------------------------------------------------------------------
5314 --Add by Xiao for POT Phase III changes, reg bug#12895841on 1-Aug-2011, end
5315
5316 END populate_repository;
5317 ----------------------------------------------------------------------------------------------
5318 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, end
5319
5320 END jai_cmn_rgm_processing_pkg;
[Home] [Help]