[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
- INSERT_REQUEST_DETAILS : inserts a record into batch header with input details
- GET_ITEM_LINE_ID : fetches ITEM Invoice Distribution of TAX invoice distribution incase of AP transactions
- PROCESS_PAYMENT : records the recovered service tax into repository to the tune of payment amount
w.r.t invoice amount and service tax distribution amount. this has all the required functional logic related
to Payment reversals and apportioning of Service Tax if multiple payments exists for same invoice etc.
- PROCESS_PAYMENTS : This is the AP Processor that picks up all the eligible Payments(includes prepayments
also) and invokes process_payment for each payment
2 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.1
removed the usage of regime effective_date_from and replace it with regime creation_date as part of VAT Impl.
This is required as effective dates are removed Regime setup
3 12/04/2005 Brathod, for Bug# 4286646, Version 115.2
Issue :-
Because of change in Valueset from JA_IN_DATE to FND_STANDARD_DATE Concurrent was resulting
in error because JA_IN_DATE uses normal date format while FND_STANDARD_DATE uses NLS_DATE format
and it is passed as character value.
Fix :-
Procedure signature modified to convert p_trx_from_date, p_trx_from_date from date to
pv_trx_from_date, pv_trx_from_date varchar2. And the varchar2 values are converted back
to date fromat using fnd_date.canonical_to_date function.
4. 14/04/2005 ssumaith - bug# 4284505 - File version 115.3
Added code to pick the third party taxes from the jai_Rcv_tp_inv_details table in case of
third party invoices.
This is done by adding code for checking - source in the ap_invoices_all table , if it
equals to 'RECEIPT' , getting the third party taxes from the jai_Rcv_tp_inv_Details table.
5. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
Changed AP Lookup code from 'RECEIPT' to 'INDIA TAX INVOICE'
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
as required for CASE COMPLAINCE.
7. 14-Jun-2005 rchandan for bug#4428980, Version 116.4
Modified the object to remove literals from DML statements and CURSORS.
8. 24-Jan-2006 Bug 4991017. Added by Lakshmi Gopalsami version 120.4
Merged the cursors c_ap_accounted_invoices and
c_event_distributions because of SLA uptake by base
and removed the same.
(1) Changed the reference to xla_ae_headers instead
of ap_ae_headers_all
(2) Also added xla_transaction_entities to get the entity_id
and source_int_id_1 so that it can be joined with
transaction tables.
(3) Discussed with shekhar and found that we should derive by
accounting_date and not on the creation_date.
(4) Added accounting_event_id in cursor
(5) Added local variable lv_entity_code
DEPENDANCY:
-----------
IN60106 + 4068823 + 4245089
8. 29/07/2005 Aiyer - bug# 4523205 - File version 120.2 - (R12 Forward Porting FROM 11.5 bugs 4348774, 4357984)
Procedure process has been changed for the bug. Please refer the details in the change history section
of the procedure.
9. 08-Mar-2006 , Bug 4947102, By Aiyer , File Version 120.5
Issue:-
Cursor c_period_payments has high cost of execution.
Fix:-
Merged the cursors c_period_payments with c_invoice_distributions into c_period_payments so that the IL table in cursor
can reduce the overall rows searched by the query.
SQL-ID as reported in the repository is 14828450.
Dependency Due to this bug :-
None
10. 09-JUNE-2007 ,Kunkumar for Bug 6012489 version 12.6
Added an if condtion for assignment to local variable
If action is accounting, then generated vat invoice number is picked
11. 05-SEP-2007 CSahoo for bug#5680459, File Version 120.23
R.TST1203:FORWARD PORTING FROM 115 BUG 5645003
commented the part where lv_inv_gen_process_flag and lv_inv_gen_process_message
were getting assigned as NULL.
replaced the party_id by party_site_id as the second parameter in the call to check_reg_dealer procedure.
12. 04-OCT-2007 CSahoo for bug#6436576, File Version 120.24
R.TST1203.XB2.QA:SERVICE TAX REVIEW REPOSITORY SHOW MULTIPLE ACCOUNTING LINES
added the following AND condition in the cursor c_period_payments in process_payments procedure.
AND apinvp.invoice_id = ainvd.invoice_id
13. 13-01-2009 vumaasha for bug 7684820
INDIA LOCALIZATION: SERVICE TAX RECOVERABLE PORTION IS INCORRECT CALCULATED
14. 17-May-2009 Bug 7522584
Issue : Service Tax entered in foreign currency for AR Invoice is not converted to Functional Currency
Fix: Modified the code in the procedure process_payment. Added a cursor c_get_curr_dtls
to get the currency details. Then multiplied the conversion rate to the tax_amount
to get the tax amount in functional currency i.e., INR
15. 25-May-2009 Bug 8294236
Issue: Svc tax transactions created fx balances on tax accounts after settlement
Fix: Modified the code in the procedure process_payment. Added the call to the procedure
JAI_RGM_TRX_RECORDING_PKG.exc_gain_loss_accounting.
16. 01-OCT-2009 JMEENA for bug#8943349
Issue: India Service Tax Processing Concurrent not processing Standalone Invoices
Fix: Modified cursor c_tax_dist_dtl and c_period_payments of Procedure Process_payments
Added code to check if the taxes exists with the standalone invoice. If taxes exists the
invoices should be picked for the processing.
17. 08-Oct-2009 CSahoo for bug#8965721,
Issue: TST1212.XB2.QA:SERVICE TAX CREDIT NOT ACCOUNTED FOR GOODS TRANSPORT OPERATORS
Fix: Modified the cursors c_ap_accounted_inv_dist and c_period_payments. Added a AND condition.
18. 21-Dec-2009 Xiao Lv for Bug#7191302 .
Issue: Service tax is recovered in excess when prepayment is applied
with the checkbox "PREPAYMENT ON INVOICE" checked.
Fix: included a cursor c_total_inv_amount, which fetches the sum of
total invoice amount eligible for tax recovery.
For more details please refer to bug.
19. 02-Apr-2010 Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement)
Issue: currently, procedure 'process' only handles shippable items
Fix: logic in procedure 'process' should be modified to process both shippable and
non-shippable lines.
20 18-Apr-2010 Eric Ma remove the non-ASCII Codes in line 2185
21. 28-Apr-2010 Allen Yang for bug 9666476
In procedure 'process':
1) added 'NULLS FIRST' into Order By clause of sql_stmt_all
to ensure shippable items are always processed before non-shippable items.
2) removed order_number from Order By clause of sql_stmt_shippable
22. 13-May-2010 Allen Yang for bug 9709477
In procedure 'process':
1). added warning message when flags Same as Excise and Generate Single Invoice are both Y.
23. 03-Jun-2010 Allen Yang for bug 9737119
Issue: TST1213.XB1.QA.EXECPT DIAGNOSTICS,WARNING MESSAGE SHOULD ALSO BE SEEN IN LOG
Fix: In procedure 'process', added logic to put message lv_same_as_excise_conf_warning to Log.
24. 03-JUN-2010 vkaranam for bug#9772724
Issue:
SAME VAT INVOICE GETTING GENERATED FOR DIFFERENT CUSTOMERS
fix:
changes are done in procedure "process".
lv_inv_num_already_generated is set to to false if the parties are different.
25. Jul 22, 2010 Bug 9854974
Issue: CESS and SHE CESS amounts are not shown in Service Tax Pending Recovery Report
for Standalone Invoices
Fix: Item Line ID in JAI_RGM_TRX_REFS should refer to the Item Line to which Service Tax is attached
and not the Line Number of the Service Tax.
26. 29-OCT-2010 vkaranam for bug#10085619
Issue: India Service Tax Processing Concurrent not processing taxes applied on third party invoice.
Fix: Modified the cursor c_tp_inv_details of Procedure Process_payments to include the taxes on thirdparty invoice.
27. 14-Dec-2010 Eric Ma for Bug#10376849
Issue : Tax amount for CM of AP invoice are not populated into tax repository
Fix : Change the cursor of c_ap_accounted_inv_dist adding the CM into the invoice_type_lookup_code
19. 21-jan-2011 vkaranam for bug#11666653
Issue:
Service Tax repository not updated for 3rd party invoice applied to
Prepayment Invoice
Reason :
Issue is with cursor c_prepayment_applications ,
Invoice id for 3p invoice is fetched using the below:
select invoice_id
from jai_rcv_tp_invoices
where vendor_id
in (select vendor_id
from po_vendors
where trim(vendor_type_lookup_code)
-- like 'Service Tax Authorities')
in R12 vendor_type_lookup_code 'Service Tax Authorities' has been changed to 'INDIA SERVICE TAX AUTHORITY'
Hence the STP is not processing prepayment on TP invoice.
Fix:
Modified the cursor c_prepayment_applications.
Replaced 'Service Tax Authorities' with 'INDIA SERVICE TAX AUTHORITY'.
29. 23-Feb-2011 Bug 10630847
Description: Prepayment application on Standalone AP invoice is not processed by Service Tax Processing
Fix: Included an UNION clause in cursor c_prepayment_applications to get the Standalone Invoice
on which Prepayment is applied.\
30. 06-may-2011 vkaranam for bug#12360337
Issue:ST processor concurrent is not processing the standalone AP invoices with source other than Manual Invoice entry.
Fix:
commented the ap_invoices_all.source='Manual Invoice entry' in cursors c_prepayment_applications,c_period_payments.
30. 24-May-2011 Xiao for POT change, reg bug#12533434.
Description: ST Processor should process the the transaction on Cash Basis as well as Accrual Basis.
Fix: Compare the accounting date of invoice distribution with POT implementation date.
Add one more procedure populate_repository to populate the transactions on accrual basis.
31. 29-May-2011 Xiao for POT change, reg bug#12533434.
Fix: Add cancellation logic in procedure populate_repository.
Move the accounting date check out of procedure process_payment.
32. 16-Aug-2011 Bug 12839287
Description: Repository is updated with Functional Currency even though the transaction
is in Foreign currency
Fix: Populated Tax Amount column to store amount in Transaction Currency instead of
functional currency
33. 22-Aug-2011 Xiao for POT Phase III changes, reg bug#12895841
Fixed: Add logic for showing transactions for AP reversal, AP claim, AR reversal, AR claim.
34. 12-Sep-2011 Eric Ma fixed bug12926303 - R1213:POT:PH III - SERVICE TAX REVERSAL RESULTS IN ERROR
Fixed: The error message in claim_reversal_trx_lines() is not proper. Fix them.
35. 30-Sep-2011 Zhiwei Hou code change for bug#13023443. for Service Tax from Open Interface , if External
and GL >= POT , then not populate repo.
36. 27-OCT-2011 Chong Lei code change for bug#13259755. for POT AP claim issue.
37. 08-NOV-2011 Chong Lei code change for bug#13358557. for reversal service tax amount issur,
when these are more than one item line in AP invoice.
35. 11-nov-2011 vkaranam for bg#13323182
Issue:Service tax is not getting processed on cash basis after the patch(12805386)
application.
fix:
+ If Accounting Basis has to be CASH, the cursor that fetches POT date will
return NULL
+ Hence when comparing the POT Date for any logic that needs to go by CASH,
we need to compare with NULL also.Added the OR ld_St_accrual_date is null for cash basis transactio
36. 23-nov-2011 amandali for bug 13242818
Issue:Service tax settlement form is based on invoice date rather than accounting date of the invoice.
Fix: Changed the transaction date from invoice_date to accounting_date in call to jai_cmn_rgm_recording_pkg.insert_repository_entry
37. 06-JAN-2012 Qiong for bug 13439861 service tax repository can't be updated normally
38. 31-Aug-2012 amandali for bug 14507573
Description:Service Tax repository not updated for PO matched invoice having multiple distributions
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.
Added the join to jai_ap_match_inv_taxes.
39. 25-Feb-2013 amandali for bug 16246654
Description:Reverse charge service tax not hitting repository for third party invoices.
Fix:Modified the cursor c_tp_inv_details. Added union condition to pick the reverse charge liability distribution
---------------------------------------------------------------------------------------------------------------------------*/
CURSOR c_rgm_repository_id(cp_source IN VARCHAR2, cp_source_table_name IN VARCHAR2,
cp_source_document_id IN NUMBER, cp_reference_id IN NUMBER) IS
SELECT repository_id
FROM jai_rgm_trx_records
WHERE source = cp_source
AND source_table_name = cp_source_table_name
AND source_document_id = cp_source_document_id
AND reference_id = cp_reference_id;
SELECT nvl(credit_amount, debit_amount) amount
FROM jai_rgm_trx_records
WHERE source = cp_source
AND source_table_name = cp_source_table_name
AND source_document_id = cp_source_document_id
AND reference_id = cp_reference_id;
SELECT reference_id, parent_reference_id, item_line_id, reversal_flag, nvl(recovered_amount, 0) recovered_amount,
tax_type, recoverable_amount, nvl(discounted_amount,0) discounted_amount
FROM jai_rgm_trx_refs
WHERE source = cp_source
AND invoice_id = cp_invoice_id
AND line_id = cp_invoice_distribution_id;
SELECT reference_id, parent_reference_id, item_line_id, reversal_flag, recovered_amount, tax_type,
recoverable_amount
FROM jai_rgm_trx_refs
WHERE reference_id = cp_reference_id;
SELECT a.invoice_id, a.invoice_distribution_id,
a.invoice_line_number, /* INVOICE LINES UPTAKE */
a.distribution_line_number, a.prepay_distribution_id,
a.amount, a.reversal_flag, a.parent_reversal_id, a.accounting_event_id, a.posted_flag, a.org_id,
a.accounting_date, b.invoice_amount, b.amount_paid, b.cancelled_date, b.invoice_type_lookup_code invoice_type,
a.creation_date, a.po_distribution_id
FROM ap_invoice_distributions_all a, ap_invoices_all b
WHERE a.invoice_id = b.invoice_id
AND invoice_distribution_id = cp_invoice_distribution_id;
SELECT a.invoice_payment_id, a.check_id, a.amount, a.payment_base_amount, -- a.reversal_flag, reversal_inv_pmt_id,
a.org_id, b.status_lookup_code, b.check_date, b.void_date, b.future_pay_due_date,
a.accounting_date, a.reversal_inv_pmt_id, discount_taken,
-- Added the following for Bug 8294236
b.currency_code, b.exchange_rate, b.exchange_date, b.exchange_rate_type
FROM ap_invoice_payments_all a, ap_checks_all b
WHERE a.check_id = b.check_id
AND a.invoice_payment_id = cp_invoice_payment_id;
SELECT regime_code
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = cp_regime_id;
SELECT accnts.attribute_value
FROM JAI_RGM_ORG_REGNS_V accnts
WHERE accnts.regime_id = cp_regime_id
AND accnts.attribute_code = 'SERVICE_TAX_REVERSAL_TO_UPDATE'
AND accnts.registration_type = jai_constants.regn_type_others
AND accnts.organization_type = cp_organization_type
AND accnts.organization_id = cp_organization_id
AND (cp_location_id IS NULL OR location_id = cp_location_id);
SELECT regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = cp_regime_code;
SELECT invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND (p_rcv_transaction_id IS NULL OR rcv_transaction_id = p_rcv_transaction_id)
AND po_distribution_id = p_po_distribution_id
AND line_type_lookup_code = p_line_type_lookup_code--rchandan for bug#4428980
AND parent_reversal_id IS NULL; -- CHK
PROCEDURE insert_request_details(
p_batch_id OUT NOCOPY NUMBER,
p_regime_id IN NUMBER,
p_rgm_registration_num IN VARCHAR2,
p_trx_from_date IN DATE,
p_trx_till_date IN DATE
) IS
ln_conc_request_id FND_CONCURRENT_REQUESTS.request_id%TYPE;
SELECT request_date
FROM fnd_concurrent_requests
WHERE request_id = cp_request_id;
lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_processing_pkg.insert_request_details';
INSERT INTO jai_rgm_conc_requests(
batch_id,
request_id,
request_date,
regime_id,
rgm_registration_num,
trx_from_date,
trx_till_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_login_id
) VALUES (
jai_rgm_conc_requests_s.nextval,
ln_conc_request_id,
ln_conc_request_date,
p_regime_id,
p_rgm_registration_num,
p_trx_from_date,
p_trx_till_date,
sysdate,
FND_GLOBAL.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID')
) RETURNING batch_id INTO p_batch_id;
END insert_request_details;
SELECT exchange_date
, exchange_rate
, exchange_rate_type
, invoice_date
FROM ap_invoices_all
WHERE invoice_id = pn_invoice_id ;
SELECT SUM(tax_amt)
FROM jai_cmn_document_taxes jcdt,
jai_cmn_taxes_all jcta
WHERE jcdt.source_doc_id = pn_invoice_id
--Add by Chong for bug#13358557 08-NOV-2011, begin
-----------------------------------------------------------------------------------------------
AND jcdt.source_doc_parent_line_no = pn_line_number
-----------------------------------------------------------------------------------------------
--Add by Chong for bug#13358557 08-NOV-2011, end
AND jcdt.modvat_flag = 'Y'
AND jcdt.tax_id = jcta.tax_id
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT SUM(jattl.tax_amount * nvl(jcta.mod_cr_percentage,0)/100) tax_amt
FROM jai_ar_trx_tax_lines jattl
, jai_ar_trx_lines jatl
, jai_cmn_taxes_all jcta
WHERE jatl.customer_trx_id = pn_invoice_id
AND jattl.link_to_cust_trx_line_id = jatl.customer_trx_line_id
AND jattl.tax_id = jcta.tax_id
AND nvl(jcta.mod_cr_percentage,0) > 0
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT 'STANDALONE' match_type, aida.invoice_distribution_id, jcdt.tax_amt, jcdt.tax_type
FROM ap_invoice_distributions_all aida,
jai_cmn_document_taxes jcdt
WHERE aida.invoice_id = pn_invoice_id
AND aida.line_type_lookup_code = 'MISCELLANEOUS'
AND jcdt.source_doc_id = pn_invoice_id
AND jcdt.source_doc_parent_line_no = pn_line_number
AND jcdt.source_doc_line_id = aida.invoice_line_number
AND jcdt.modvat_flag = 'Y'
AND jcdt.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
UNION ALL
SELECT 'MATCH' match_type, jamt.invoice_distribution_id, jamt.tax_amount tax_amt,jcta.tax_type
FROM jai_ap_match_inv_taxes jamt
, jai_cmn_taxes_all jcta
WHERE jamt.invoice_id = pn_invoice_id
--AND jamt.invoice_line_number <> pn_line_number
AND jamt.parent_invoice_line_number = pn_line_number
AND jamt.tax_id = jcta.tax_id
AND jamt.recoverable_flag = 'Y'
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT sum(jamt.tax_amount)
FROM jai_ap_match_inv_taxes jamt
, jai_cmn_taxes_all jcta
WHERE jamt.invoice_id = pn_invoice_id
--Add by Chong for bug#13358557 08-NOV-2011, begin
-----------------------------------------------------------------------------------------------
AND jamt.parent_invoice_line_number = pn_line_number
-----------------------------------------------------------------------------------------------
--Add by Chong for bug#13358557 08-NOV-2011, end
AND jamt.tax_id=jcta.tax_id
AND jamt.recoverable_flag = 'Y'
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT SUM(amount)
FROM ap_invoice_lines_all
WHERE invoice_id = pn_invoice_id
AND line_type_lookup_code IN ('ITEM', 'MISCELLANEOUS');
SELECT SUM(total_amount)
FROM jai_ar_trx_lines
WHERE customer_trx_id = pn_invoice_id;
SELECT jcta.tax_type, jattl.tax_amount * nvl(jcta.mod_cr_percentage,0)/100 tax_amt,
jattl.customer_trx_line_id
FROM jai_ar_trx_tax_lines jattl
, ra_customer_trx_lines_all trxl
, jai_cmn_taxes_all jcta
WHERE jattl.link_to_cust_trx_line_id = pn_line_number
AND trxl.customer_trx_id = pn_invoice_id
AND trxl.customer_trx_line_id = pn_line_number
AND jattl.tax_id = jcta.tax_id
AND nvl(jcta.mod_cr_percentage,0) > 0
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate, trx_date
FROM ra_customer_trx_all
WHERE customer_trx_id = pn_invoice_id;
SELECT regime_id
FROM jai_rgm_definitions
WHERE regime_code = jai_constants.service_regime;
SELECT *
FROM jai_rgm_trx_refs rgtf
WHERE line_id = cp_source_document_id
AND party_type = 'C'
AND SOURCE = cp_source_ar;
SELECT jsir.*
FROM jai_st_invoice_reversal jsir
WHERE jsir.source = 'AP'
--Add by Chong for bug#13259755, start
AND jsir.organization_id = pn_organization_id
--Add by Chong for bug#13259755, end
AND jsir.tax_to_be_adjusted > 0;
SELECT nvl(aipa.amount, 0) claim_amt, aipa.invoice_payment_id, aipa.accounting_date
FROM ap_invoice_payments_all aipa
WHERE aipa.invoice_id = pn_invoice_id
AND aipa.invoice_payment_id > nvl(pn_max_payment_id, 0)
AND aipa.accounting_date <= pd_to_date
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr
WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
AND jrtr.source = 'AP_CLAIM'
AND jrtr.source_document_id = aipa.invoice_payment_id)
AND EXISTS (SELECT 1
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = pn_invoice_id
AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aipa.accounting_date,
aida.accounting_date, 'AP') = 'Y')
UNION ALL
SELECT abs(nvl(aila.amount, 0)) claim_amt, aipa.invoice_payment_id, aila.accounting_date
FROM ap_invoice_lines_all aila,
ap_invoice_payments_all aipa
WHERE aila.invoice_id = pn_invoice_id
AND aila.line_type_lookup_code = 'PREPAY'
AND aila.prepay_invoice_id = aipa.invoice_id
AND (aipa.invoice_payment_id > nvl(pn_max_payment_id, 0)
OR (aipa.invoice_payment_id < nvl(pn_max_payment_id, 0)
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr
WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
AND jrtr.source = 'AP_CLAIM'
AND jrtr.source_document_id = aipa.invoice_payment_id)))
AND aila.accounting_date <= pd_to_date
AND nvl(aila.amount, 0) <> 0
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr
WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
AND jrtr.source = 'AP_CLAIM'
AND jrtr.source_document_id = aipa.invoice_payment_id)
AND EXISTS (SELECT 1
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = pn_invoice_id
AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aila.accounting_date,
aida.accounting_date, 'AP') = 'Y')
ORDER BY 2;
UPDATE jai_st_invoice_reversal
SET max_claim_payment_id = ln_max_payment_id
WHERE invoice_id = reversal_trxn_rec.invoice_id
AND line_num = reversal_trxn_rec.line_num
AND SOURCE = 'AP';
SELECT b.ship_to_organization_id
FROM po_distributions_all a, po_line_locations_all b
WHERE a.line_location_id = b.line_location_id
AND a.po_distribution_id = cp_po_distribution_id;*/
select sum(amount) from ap_invoice_distributions_all a where invoice_id=cp_invoice_id
and prepay_distribution_id is null;
SELECT payment_currency_code,
exchange_rate,
exchange_date,
exchange_rate_type
FROM ap_invoices_all
WHERE invoice_id = cp_invoice_id ;
UPDATE jai_rgm_trx_refs
SET reversal_flag = 'Y',
last_update_date = sysdate
WHERE source = p_source
AND invoice_id = p_invoice_id
AND line_id in (p_inv_dist_id, r_dist.parent_reversal_id);
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_rgm_reposotory_id,
p_regime_id => p_regime_id,
p_tax_type => r_ref.tax_type,
p_organization_type => jai_constants.orgn_type_io ,/*5694855*/
p_organization_id => ln_organization_id ,/*5694855*/
p_location_id => ln_location_id,/*5694855*/
p_service_type_code => lv_service_type_code,/*5694855*/
p_source => p_source,
p_source_trx_type => lv_src_trx_type,
p_source_table_name => p_payment_table_name,
p_source_document_id => p_payment_document_id,
p_transaction_date => ld_transaction_date,
p_account_name => null,
p_charge_account_id => null,
p_balancing_account_id => null,
p_amount => ln_func_tax_amount , -- Added for Bug 7522584
p_discounted_amount => ln_discounted_amount,
p_inv_organization_id => ln_organization_id,/*Bug 5879769 bduvarag*/
p_trx_amount => ln_recovered_amount,
p_assessable_value => null,
p_tax_rate => null,
p_reference_id => r_ref.reference_id,
p_batch_id => p_batch_id,
p_called_from => lv_called_from, --rchandan for bug#4428980
p_process_flag => p_process_flag,
p_process_message => p_process_message,
p_accntg_required_flag => jai_constants.yes,
p_accounting_date => ld_accounting_date,
p_balancing_orgn_type => null,
p_balancing_orgn_id => null,
p_balancing_location_id => null,
p_balancing_tax_type => null,
p_balancing_accnt_name => null,
/* added nvl part for bug 9187805 */
p_currency_code => nvl(r_payment.currency_code,rec_get_curr_dtls.payment_currency_code), -- Added for Bug 7522584
p_curr_conv_date => nvl(r_payment.exchange_date,rec_get_curr_dtls.exchange_date), -- Added for Bug 7522584
p_curr_conv_type => nvl(r_payment.exchange_rate_type,rec_get_curr_dtls.exchange_rate_type), -- Added for Bug 7522584
p_curr_conv_rate => nvl(r_payment.exchange_rate,rec_get_curr_dtls.exchange_rate) -- Added for Bug 7522584
);
jai_cmn_rgm_recording_pkg.update_reference(
p_source => p_source,
p_reference_id => r_ref.reference_id,
p_recovered_amount => ln_recovered_amount,
p_discounted_amount => ln_discounted_amount, -- CHK (Implementation)
p_process_flag => p_process_flag,
p_process_message => p_process_message
);
SELECT a.organization_id org_id,a.location_id /*Bug 5879769 bduvarag*/
FROM JAI_RGM_ORG_REGNS_V a
WHERE regime_id = cp_regime_id
AND registration_type = jai_constants.regn_type_others
AND attribute_type_code = p_att_type_code--rchandan for bug#4428980
AND organization_type = cp_orgn_type
AND attribute_value = cp_registration_num
AND a.organization_id = nvl(cp_organization_id,a.organization_id) /*5694855*/;
SELECT trunc(creation_date) effective_date_from /* effective_date_from. Commneted this as part of VAT Impl. Vijay Shankar for Bug#425023(4245089) */
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = cp_regime_id;
SELECT org_information3
FROM hr_organization_information
WHERE upper(ORG_INFORMATION_CONTEXT) = 'ACCOUNTING INFORMATION'
AND organization_id = cp_organization_id;
insert_request_details(
p_batch_id => ln_batch_id, -- OUT parameter
p_regime_id => p_regime_id,
p_rgm_registration_num => p_rgm_registration_num,
p_trx_from_date => p_trx_from_date,
p_trx_till_date => p_trx_till_date
);
SELECT distinct line.match_type,line.line_number
FROM ap_invoice_lines_all line, jai_ap_match_inv_taxes jamt
WHERE line.invoice_id = p_invoice_id
and jamt.invoice_id=line.invoice_id
and jamt.po_distribution_id=p_po_distribution_id
and line.line_number=jamt.parent_invoice_line_number
AND line.line_type_lookup_code ='ITEM'
AND line.match_type IS NOT NULL;
SELECT organization_id,location_id
FROM jai_ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND invoice_line_number = (SELECT parent_invoice_line_number
FROM jai_ap_invoice_lines jail
WHERE jail.invoice_line_number = p_invoice_line_number
AND jail.invoice_id = p_invoice_id)
AND parent_invoice_line_number IS NULL;
SELECT po.ship_to_organization_id, po.ship_to_location_id
FROM po_line_locations_all po,ap_invoice_lines_all ap
WHERE po.line_location_id = ap.po_line_location_id
AND ap.invoice_id = p_invoice_id
AND ap.line_number = pn_line_number;
SELECT rcv.organization_id, rcv.location_id
FROM jai_rcv_transactions rcv, ap_invoice_lines_all ap
WHERE ap.rcv_transaction_id = rcv.transaction_id
AND ap.invoice_id = p_invoice_id
AND ap.line_number = pn_line_number;
SELECT trx_till_date+1
FROM jai_rgm_conc_requests
WHERE regime_id = cp_regime_id
AND rgm_registration_num = cp_registration_num;
SELECT aid.invoice_id, aid.invoice_distribution_id,
aid.distribution_line_number, aid.invoice_line_number,
aid.reversal_flag, aid.parent_reversal_id,
aid.accrual_posted_flag, aid.cash_posted_flag,
aid.amount, aid.base_amount,
aid.po_distribution_id, aid.rcv_transaction_id,
-- Bug 4991017 Added by Lakshmi Gopalsami.
-- Added accounting_event_id in cursor.
aid.org_id, aid.accounting_event_id,
ai.vendor_id, ai.vendor_site_id, ai.invoice_currency_code,
aid.exchange_rate, aid.exchange_rate_type, aid.exchange_date,
ai.source
,aid.accounting_date --Added by zhiwei for POT change Bug#13023443 on 20110930
FROM xla_ae_headers xah ,
xla_transaction_entities xte,
ap_invoices_all ai,
ap_invoice_distributions_all aid
WHERE xah.je_category_name = cp_ae_category
AND xah.ledger_id = cp_sob_id
AND xah.application_id =200
and xah.entity_id = xte.entity_id
AND xte.application_id = 200
and xte.entity_code =lv_entity_code --'AP_INVOICES'
and xte.source_id_int_1 = ai.invoice_id
AND aid.invoice_id = ai.invoice_id
and aid.accounting_event_id = xah.event_id
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
--AND ai.cancelled_date IS NULL --Commented out by Xiao, for POT cancellation, reg bug#12533434
AND ( aid.line_type_lookup_code = jai_constants.misc_line
or exists (select 1 from jai_rcv_tp_invoices jtp where AID.invoice_id = jtp.invoice_id)) /* modified by vumaasha for bug 8965721 */
AND aid.posted_flag = 'Y'
/*bug 7347127 - moved the trunc in following 2 expressions to the RHS, so that
* the indec on accounting_date would be used in the two tables. In some cases,
* this would prevent performance issue in the Service Tax Processor*/
AND xah.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
AND aid.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
and ai.org_id = p_org_id
-- and aid.org_id = p_org_id; -- commented out by Chong for bug#13259755 on 27-OCT-2011
SELECT 1
FROM jai_ap_invoice_lines
WHERE organization_id = NVL(p_organization_id,organization_id)
AND ai.invoice_id = invoice_id
)
OR EXISTS
(
SELECT 1
FROM po_line_locations_all po
, ap_invoice_lines_all line
WHERE ai.invoice_id = line.invoice_id
AND line.po_line_location_id = po.line_location_id
AND (line.match_type = 'ITEM_TO_RECEIPT'
OR line.match_type = 'ITEM_TO_PO')
AND po.ship_to_organization_id = NVL(p_organization_id,po.ship_to_organization_id)
)
)
;
SELECT invoice_id,
invoice_distribution_id,
prepay_distribution_id ,
amount ,
reversal_flag ,
parent_reversal_id ,
org_id ,
invoice_line_number ,/*Bug 12805386*/
po_distribution_id /*Bug 12805386*/
FROM ap_invoice_distributions_all
WHERE org_id = p_org_id
AND line_type_lookup_code = jai_constants.prepay_line
AND invoice_id IN ( SELECT invoice_id
FROM ap_invoice_distributions_all
WHERE po_distribution_id IN ( SELECT pda.po_distribution_id
FROM po_line_locations_all pll,
po_distributions_all pda,
jai_po_line_locations jpll
WHERE pll.line_location_id = jpll.line_location_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = p_organization_id
)
AND ( (cp_start_date IS NULL AND creation_date < cp_till_date)
OR (cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
)
-- union added by Xiao Lv for Bug#7191302
UNION
select invoice_id
from jai_rcv_tp_invoices
where vendor_id
in (select vendor_id
from po_vendors
where trim(vendor_type_lookup_code)
-- like 'Service Tax Authorities') commeneted for bug#11666653
-- like 'INDIA SERVICE TAX AUTHORITY') --bug#11666653
like 'INDIA SERVICE TAX%AUTHORITY') --bug#11666653 ,vendor lookup has the values with double space btw tax and authority hence added % in btw.
AND ( (cp_start_date IS NULL AND creation_date < cp_till_date)
OR (cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
)--Xiao Lv for Bug#7191302
/*Bug 10630847 - Start*/
/*Pick Prepayment applications on Standalone AP Invoice also*/
UNION
select aia.invoice_id
from ap_invoices_all aia
where --aia.source='Manual Invoice Entry' and/*commented by vkaranam for bug#12360337*/
exists (select '1'
from jai_ap_invoice_lines jail
where aia.invoice_id = jail.invoice_id
and jail.organization_id = p_organization_id)
AND ((cp_start_date IS NULL AND aia.creation_date < cp_till_date)
OR (cp_start_date IS NOT NULL AND trunc(aia.creation_date) between cp_start_date AND cp_till_date))
/*Bug 10630847 - End*/
)/*5694855*/
AND prepay_distribution_id IS NOT NULL
AND ( ( cp_start_date IS NULL AND creation_date < cp_till_date)
OR ( cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
)
ORDER BY invoice_distribution_id;
SELECT a.invoice_id, a.invoice_distribution_id, a.distribution_line_number, a.dist_match_type,
a.invoicE_line_number, /* INVOICE LINES UPTAKE */
a.parent_reversal_id, a.reversal_flag, a.rcv_transaction_id, a.po_distribution_id
, a.accounting_date ----Add by Xiao for POT change, reg bug#12533434 on 29-May-2011
FROM ap_invoice_distributions_all a, jai_rgm_trx_refs b /* second table is used for join just to take IL records */
WHERE a.invoice_id = cp_invoice_id
/*Added the below or condition for bug#11666653*/
AND (
(a.line_type_lookup_code <> 'PREPAY' And exists (select '1' from jai_rcv_tp_invoices where invoice_id=a.invoice_id))
OR
a.line_type_lookup_code = jai_constants.misc_line -- <> 'PREPAY'
)
AND b.source = jai_constants.source_ap
and b.invoice_id = a.invoice_id
and b.line_id = a.invoice_distribution_id
ORDER BY a.invoice_distribution_id;
SELECT 1 chk, a.tax_id, b.tax_rate, a.tax_amount, a.parent_invoice_distribution_id, b.tax_type,
a.invoice_line_number invoice_line_number, /* INVOICE LINES UPTAKE */
nvl(b.mod_cr_percentage,0) recoverable_ptg, a.base_amount
FROM JAI_AP_MATCH_INV_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c
WHERE a.invoice_id = cp_invoice_id
-- AND a.distribution_line_number = cp_dist_line_no Modified as part of AP INVOICE Lines Uptake project
AND a.invoice_distribution_id = cp_inv_distribution_id
AND a.tax_id = b.tax_id
AND b.tax_type = c.attribute_code
and c.regime_id = cp_regime_id
and c.registration_type = jai_constants.regn_type_tax_types
-- 5763527, modified and condition as below
AND ( mod_cr_percentage > 0 and mod_cr_percentage <= 100 and nvl(recoverable_flag,'Y') <> 'N')
-- Added by qiong for Reverse Charge code port begin
UNION
SELECT 1 chk, match_tax.tax_id, tax.tax_rate, abs(ap_dist.amount), match_tax.parent_invoice_distribution_id,tax.tax_type,
ap_line.line_number invoice_line_number,
100 recoverable_ptg, match_tax.base_amount
FROM AP_INVOICE_LINES_ALL ap_line,
AP_INVOICE_DISTRIBUTIONS_ALL ap_dist, JAI_AP_MATCH_INV_TAXES match_tax,
JAI_CMN_TAXES_ALL tax, JAI_RGM_REGISTRATIONS rgm
WHERE ap_dist.invoice_id = cp_invoice_id
AND ap_dist.invoice_distribution_id = cp_inv_distribution_id
AND ap_line.invoice_id = ap_dist.invoice_id
AND ap_dist.invoice_line_number = ap_line.line_number
AND ap_line.line_type_lookup_code = 'MISCELLANEOUS'
AND ap_dist.amount < 0
AND ap_line.application_id = 7000
AND ap_line.PRODUCT_TABLE = 'JAI_AP_MATCH_INV_TAXES'
AND ap_line.REFERENCE_KEY1 = to_char(match_tax.tax_id)
AND ap_line.REFERENCE_KEY2 = to_char(match_tax.parent_invoice_line_number)
AND match_tax.invoice_id = ap_dist.invoice_id
AND match_tax.tax_id = tax.tax_id
AND match_tax.parent_invoice_distribution_id IS NOT NULL
AND tax.reverse_charge_flag = 'Y'
AND tax.tax_type = rgm.attribute_code
and rgm.regime_id = cp_regime_id
and rgm.registration_type = jai_constants.regn_type_tax_types
-- Added by Qiong for Reverse Charge code port begin
UNION --Added this union for bug#8943349 by JMEENA
SELECT 2 chk, a.tax_id, b.tax_rate, a.tax_amt,null, b.tax_type,
/*Bug 9854974 - Item Line ID in JAI_RGM_TRX_REFS should refer to the Item Line to which Service Tax is attached
and not the Line Number of the Service Tax*/
a.source_doc_parent_line_no invoice_line_number, /* INVOICE LINES UPTAKE */
nvl(b.mod_cr_percentage,0) recoverable_ptg, d.base_amount
FROM JAI_CMN_DOCUMENT_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c,
AP_INVOICE_DISTRIBUTIONS_ALL d
WHERE a.source_doc_id = cp_invoice_id
AND d.invoice_distribution_id = cp_inv_distribution_id
AND d.invoice_id = a.source_doc_id
AND d.invoice_line_number = a.source_doc_line_id
AND a.tax_id = b.tax_id
AND b.tax_type = c.attribute_code
and c.regime_id = cp_regime_id
and c.registration_type = jai_constants.regn_type_tax_types
AND ( mod_cr_percentage > 0 and mod_cr_percentage <= 100 and nvl(modvat_flag,'Y') <> 'N')
-- Added by Qiong for Reverse Charge Code port begin
UNION
SELECT 2 chk, b.tax_id,
b.tax_rate,
abs(ap_dist.amount),
null, b.tax_type,
a.source_doc_parent_line_no invoice_line_number, /* INVOICE LINES UPTAKE */
100 recoverable_ptg,
ap_dist.base_amount
FROM
AP_INVOICE_LINES_ALL ap_line,AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,JAI_CMN_DOCUMENT_TAXES a, JAI_RGM_REGISTRATIONS c,
JAI_CMN_TAXES_ALL b
WHERE ap_dist.invoice_id = cp_invoice_id
AND ap_dist.invoice_distribution_id = cp_inv_distribution_id
AND ap_line.invoice_id = ap_dist.invoice_id
AND ap_dist.invoice_line_number = ap_line.line_number
AND ap_line.line_type_lookup_code = 'MISCELLANEOUS'
AND ap_dist.amount < 0
AND ap_line.application_id = 7000
AND ap_line.PRODUCT_TABLE = 'JAI_CMN_DOCUMENT_TAXES'
AND ap_line.REFERENCE_KEY1 = to_char(a.tax_id)
AND ap_line.REFERENCE_KEY2 = to_char(a.source_doc_parent_line_no)
AND a.source_doc_id = ap_dist.invoice_id
AND a.tax_id = b.tax_id
AND b.reverse_charge_flag = 'Y'
AND b.tax_type = c.attribute_code
And c.regime_id = cp_regime_id
And c.registration_type = jai_constants.regn_type_tax_types;
SELECT 1 chk , a.tax_id , a.tax_rate , a.tax_amount , NULL ,a.tax_type ,
a.line_number invoice_line_number, /* INVOICE LINES UPTAKE */
NVL(b.mod_cr_percentage,0) recoverable_ptg, NULL
FROM jai_rcv_tp_inv_details a , JAI_CMN_TAXES_ALL b , jai_rcv_tp_invoices c, JAI_RGM_REGISTRATIONS d
WHERE c.invoice_id = cp_invoice_id
AND a.batch_invoice_id = c.batch_invoice_id
AND a.tax_id = b.tax_id
AND a.line_number = cp_line_number /*INVOICE LINES UPTAKE cp_dist_line_number */
AND b.tax_type = attribute_code
and d.regime_id = cp_regime_id
AND d.registration_type = jai_constants.regn_type_tax_types
AND b.mod_cr_percentage > 0
/*start additions for bug#10085619
,the below union clause will fetch the misc lines attached to the TP invoice**/
UNION
SELECT 2 chk, a.tax_id, b.tax_rate, a.tax_amt,null, b.tax_type,
a.source_doc_parent_line_no invoice_line_number,
nvl(b.mod_cr_percentage,0) recoverable_ptg, d.base_amount
FROM JAI_CMN_DOCUMENT_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c,
AP_INVOICE_DISTRIBUTIONS_ALL d
WHERE a.source_doc_id = cp_invoice_id
AND d.invoice_distribution_id = cp_inv_distribution_id
AND d.invoice_id = a.source_doc_id
AND d.invoice_line_number = a.source_doc_line_id
AND a.tax_id = b.tax_id
AND b.tax_type = c.attribute_code
and c.regime_id = cp_regime_id
and c.registration_type = jai_constants.regn_type_tax_types
AND ( nvl(mod_cr_percentage,0) > 0 and nvl(modvat_flag,'Y') <> 'N')
/* Added below union by Avanija for bug 16246654 */
union
SELECT 2 chk, b.tax_id,
b.tax_rate,
abs(ap_dist.amount),
null, b.tax_type,
a.source_doc_parent_line_no invoice_line_number,
100 recoverable_ptg,
ap_dist.base_amount
FROM
AP_INVOICE_LINES_ALL ap_line,AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,JAI_CMN_DOCUMENT_TAXES a, JAI_RGM_REGISTRATIONS c,
JAI_CMN_TAXES_ALL b
WHERE ap_dist.invoice_id = cp_invoice_id
AND ap_dist.invoice_distribution_id = cp_inv_distribution_id
AND ap_line.invoice_id = ap_dist.invoice_id
AND ap_dist.invoice_line_number = ap_line.line_number
AND ap_line.line_type_lookup_code = 'MISCELLANEOUS'
AND ap_dist.amount < 0
AND ap_line.application_id = 7000
AND ap_line.PRODUCT_TABLE = 'JAI_CMN_DOCUMENT_TAXES'
AND ap_line.REFERENCE_KEY1 = to_char(a.tax_id)
AND ap_line.REFERENCE_KEY2 = to_char(a.source_doc_parent_line_no)
AND a.source_doc_id = ap_dist.invoice_id
AND a.tax_id = b.tax_id
AND b.reverse_charge_flag = 'Y'
AND b.tax_type = c.attribute_code
And c.regime_id = cp_regime_id
And c.registration_type = jai_constants.regn_type_tax_types;
SELECT b.item_id
FROM po_distributions_all a, po_lines_all b
WHERE po_distribution_id = cp_po_distribution_id
AND a.po_line_id = b.po_line_id;
SELECT distinct invoice_id
FROM jai_rgm_trx_refs
WHERE batch_id = cp_batch_id
AND source = cp_source;
SELECT a.invoice_payment_id, a.check_id, a.amount, a.payment_base_amount, a.reversal_flag,
a.reversal_inv_pmt_id, a.org_id
FROM ap_invoice_payments_all a, ap_checks_all b
WHERE a.invoice_id = cp_invoice_id
AND a.check_id = b.check_id
AND a.creation_date < cp_start_date
AND nvl(b.future_pay_due_date, v_today) <= v_today
AND a.amount <> 0 /* ssumaith bug# 6104491 */
AND a.invoice_payment_id NOT IN (select source_document_id from jai_rgm_trx_records -- CHK is this required
where source = jai_constants.source_ap
and source_table_name = jai_constants.ap_payments
and source_document_id = a.invoice_payment_id
)
ORDER BY invoice_payment_id;
SELECT invoice_distribution_id, reversal_flag, parent_reversal_id, amount, org_id
FROM ap_invoice_distributions_all
WHERE invoice_id = cp_invoice_id
AND creation_date < cp_start_date
ORDER BY invoice_distribution_id;
SELECT
ainvd.invoice_id,
ainvd.invoice_distribution_id,
ainvd.distribution_line_number,
ainvd.dist_match_type,
ainvd.invoice_line_number, /* INVOICE LINES UPTAKE */
ainvd.parent_reversal_id,
ainvd.reversal_flag,
ainvd.rcv_transaction_id,
ainvd.po_distribution_id,
apinvp.invoice_payment_id,
apinvp.check_id,
apinvp.amount,
apinvp.org_id,
ainvd.accounting_date -- Xiao for POT change, reg bug#12533434
FROM
ap_invoice_payments_all apinvp,
ap_checks_all apc ,
ap_invoice_distributions_all ainvd ,
jai_rgm_trx_refs jrtr /* second table is used for join just to take IL records */
WHERE
apinvp.org_id = p_org_id
AND apinvp.check_id = apc.check_id
AND nvl(apc.future_pay_due_date, v_today) <= v_today
AND apinvp.accounting_date/*Commented by nprashar for bug #6636517
v_today*/ BETWEEN cp_start_date AND cp_till_date
AND ainvd.invoice_id IN
( SELECT invoice_id
FROM ap_invoice_distributions_all
WHERE org_id = p_org_id
AND po_distribution_id in
(SELECT pda.po_distribution_id
FROM po_line_locations_all pll,
po_distributions_all pda,
jai_po_line_locations jpll
WHERE pll.line_location_id = jpll.line_location_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = p_organization_id
)
/* Bug 7172723. Added by Lakshmi Gopalsami
* Added union clause.
*/
UNION
SELECT jrti.invoice_id
FROM jai_rcv_tp_invoices jrti
WHERE jrti.vendor_id = apc.vendor_id
AND jrti.vendor_site_id = apc.vendor_site_id
AND apc.org_id = p_org_id
UNION --Added this union for bug#8943349 by JMEENA
select aia.invoice_id
from ap_invoices_all aia , jai_ap_invoice_lines jail
where aia.invoice_id = jail.invoice_id
-- and aia.source='Manual Invoice Entry' /*commented the condition by vkaranam for bug#12360337*/
and jail.organization_id = p_organization_id
)/*5694855*/
AND ( ainvd.line_type_lookup_code = jai_constants.misc_line
/* modified by vumaasha for bug 8965721 */
OR exists (select 1 from jai_rcv_tp_invoices jtp where jtp.invoice_id=ainvd.invoice_id ) )
AND jrtr.source = jai_constants.source_ap
AND jrtr.invoice_id = ainvd.invoice_id
AND apinvp.invoice_id = ainvd.invoice_id --added by csahoo for bug#6436576
AND jrtr.line_id = ainvd.invoice_distribution_id
ORDER BY
apinvp.invoice_payment_id ,
ainvd.invoice_distribution_id;
SELECT *
FROM jai_rgm_trx_refs
WHERE source = cp_source
AND batch_id = cp_batch_id
AND invoice_id = cp_invoice_id
AND reversal_flag IS NULL
ORDER by invoice_id, line_id;
SELECT 1
FROM jai_rgm_trx_records
WHERE source = jai_constants.source_ap
AND source_table_name = jai_constants.ap_payments
AND source_document_id = cp_source_document_id;
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = (SELECT regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = jai_constants.service_regime)
and organization_id = p_organization_id
and location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'INV_ORG_CLASSIFICATION'
and attribute_value <> 'ORGANIZATION'
and organization_id = p_organization_id
and location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id)
);
SELECT accounting_date, po_distribution_id, invoice_id, invoice_line_number /*Bug 12805386*/
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = pn_invoice_distribution_id;
SELECT organization_id,location_id,interface_flag,interface_event
FROM jai_ap_invoice_lines
WHERE invoice_id = cn_invoice_id
AND invoice_line_number = (SELECT parent_invoice_line_number
FROM jai_ap_invoice_lines jail
WHERE jail.invoice_line_number = cn_invoice_line_number
AND jail.invoice_id = cn_invoice_id)
AND parent_invoice_line_number IS NULL;
SELECT regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = jai_constants.service_regime;
GOTO end_of_reference_insertion;
GOTO end_of_reference_insertion;
GOTO end_of_reference_insertion;
SELECT organization_id,location_id,interface_flag,interface_event
into l_organization_id,l_location_id,lv_interface_flag,lv_interface_event
FROM jai_ap_invoice_lines
WHERE invoice_id = ln_invoice_id
AND invoice_line_number = (SELECT parent_invoice_line_number
FROM jai_ap_invoice_lines jail
WHERE jail.invoice_line_number = ln_invoice_line_number
AND jail.invoice_id = ln_invoice_id)
AND parent_invoice_line_number IS NULL;
SELECT regime_id
into ln_regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = jai_constants.service_regime;
jai_cmn_rgm_recording_pkg.insert_reference(
p_reference_id => ln_reference_id, -- OUT Variable
p_organization_id => p_organization_id,/*5694855*/
p_source => jai_constants.source_ap,
p_invoice_id => ap_acc_dist.invoice_id,
p_line_id => ap_acc_dist.invoice_distribution_id,
p_tax_type => r_tax_dist_dtl.tax_type,
p_tax_id => r_tax_dist_dtl.tax_id,
p_tax_rate => r_tax_dist_dtl.tax_rate,
p_recoverable_ptg => r_tax_dist_dtl.recoverable_ptg,
p_recoverable_amount => ln_recoverable_amount,
p_party_type => jai_constants.party_type_vendor,
p_party_id => ap_acc_dist.vendor_id,
p_party_site_id => ap_acc_dist.vendor_site_id,
p_tax_amount => nvl(ap_acc_dist.base_amount, ap_acc_dist.amount), /*Bug 12839287 - Tax Amount should hold Amount in Functional currency*/
p_recovered_amount => 0,
p_taxable_basis => r_tax_dist_dtl.base_amount, -- CHK << what amount i should populate >>
p_item_line_id => ln_item_line_id,
p_item_id => ln_item_id,
p_trx_tax_amount => ap_acc_dist.amount,
p_trx_currency => ap_acc_dist.invoice_currency_code,
p_curr_conv_date => ap_acc_dist.exchange_date,
p_curr_conv_rate => ap_acc_dist.exchange_rate,
p_parent_reference_id => r_parent_ref.reference_id,
p_reversal_flag => ap_acc_dist.reversal_flag,
p_batch_id => p_batch_id,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message
);
IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'Enter8 - Inserted Reference:'||ln_reference_id); END IF;
<>
NULL;
/*Delete by Chong for bug#13259755 on 27-OCT-2011 start
--Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
process_claim(
pn_regime_id => p_regime_id,
pn_organization_type => p_organization_type,
pd_from_date => p_trx_from_date,
pd_to_date => p_trx_to_date,
pn_organization_id => p_organization_id);
Delete by Chong for bug#13259755 on 27-OCT-2011 end*/
----------------------------------------------------------------------------------------------
--Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, end
-- Logic to Make Register Entry for the Invoice Distributions that are populated into REFERENCES table and which are PAID
-- Prior to the start date of this concurrent program. This is because localization only considers invoices that are accounted
FOR invo IN c_batch_references(p_batch_id, jai_constants.source_ap) LOOP
-- Logic to Process the PAST DATED PAYMENTS that are not processed due to Invoice Accounting did not happen
FOR inv_payment IN c_previous_payments_of_inv(invo.invoice_id, p_trx_from_date) LOOP
FOR dist IN c_invoice_batch_refs(jai_constants.source_ap, p_batch_id, invo.invoice_id) LOOP
lv_process_flag := null;
SELECT hzcas.cust_acct_site_id
FROM hz_cust_site_uses_all hzcsu ,
hz_cust_acct_sites_all hzcas
WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
AND hzcsu.site_use_id = pn_site_use_id
AND hzcas.cust_account_id = pn_customer_id ;
SELECT vat_Reg_no
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = pn_customer_id
AND address_id = pn_address_id;
Modified the procedure update_excise_invoice_no.
Changed the datatype of p_override_invoice_date from date to varchar2 as this parameter.
Also added the new parameter ld_override_invoice_date . The value in p_override_invoice_date would be converted to date format and
stored in the local variable ld_override_invoice_date.
Dependency due to this fix:-
None
3. 3-Feb-2007 srjayara for bug 4702156, file version 120.8
Forward porting for 11i bug#4542996
Issue:-
--------
VAT invoice number and accounting was not happening for all the delivery lines in a delivery.
Fix:-
------
Possible reason identified is that the all lines are not inventory interfaced at the same time and
hence only those lines which are inventory interfaced are considered at the time vat processing concurrent
runs.
Added a check that only if all the delivery details are inventory interfaced , the delivery needs to be considered.
4. 4-jun-2007 ssumaith - bug#6109941 -
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.
5 07-jun-2007 ssumaith - bug# 6109941 - divisor by zero error was coming . this has been resolved by checking
for zero divides before the divide is done.
6 25-jun-2007 ssumaith - bug#6147385 - when all delivery details in a delivery are not interfaced trip stopped
then, the program was returning instead of processing the next delivery.
It was because of a return statement, instead added the code to process the next delivery and increment the failure counter.
Adde the nvl condition in the where clauseto use the table's registration number its passed as null
11. 12-Jul-2007 CSahoo for bug#6176277, File Version 120.20
assigned the variable ln_excise_invoice_not_done to NULL before opening the cursor.
12. 13-jul-2007 ssumaith - bug# 6176277 - The variable - lv_inv_gen_process_flag was not re-initialised
re-initialised the variables - lv_inv_gen_process_flag , lv_inv_gen_process_message to NULL
13. 04-JUN-2009 JMEENA for bug#8574533
Reset the variable ln_interface_status to zero before fetching the value from cursor c_check_interface_status
14. 07-Jul-2009 Bug 7347127 File version 120.7.12000000.14/120.27.12010000.8/120.35
Modified the cursor c_ap_accounted_inv_dis so that it may use the index on accounting_date
column if required. This is the forward port of 11i bug 7280631.
15. 02-Apr-2010 Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement)
Issue: currently, procedure 'process' only handles shippable items
Fix: logic in procedure 'process' should be modified to process both shippable and
non-shippable lines.
16. 28-Apr-2010 Allen Yang for bug 9666476
In procedure 'process':
1) added 'NULLS FIRST' into Order By clause of sql_stmt_all
to ensure shippable items are always processed before non-shippable items.
2) removed order_number from Order By clause of sql_stmt_shippable
17. 13-May-2010 Allen Yang for bug 9709477
1). added warning message when flags Same as Excise and Generate Single Invoice are both Y.
18. 03-Jun-2010 Allen Yang for bug 9737119
Issue: TST1213.XB1.QA.EXECPT DIAGNOSTICS,WARNING MESSAGE SHOULD ALSO BE SEEN IN LOG
Fix: In procedure 'process', added logic to put message lv_same_as_excise_conf_warning to Log.
**************************************************************************************************************************************/
lv_acct_process_flag VARCHAR2(10);
SELECT regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = jai_constants.vat_regime;
SELECT order_type_id , excise_invoice_no
FROM JAI_OM_WSH_LINES_ALL
--WHERE delivery_id = cp_delivery_id;
SELECT attribute_Value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_id = p_regime_id
AND attribute_type_code = jai_constants.regn_type_others
AND attribute_code = jai_constants.attr_code_same_inv_no
AND organization_id = cp_organization_id
AND location_id = cp_location_id;
SELECT 1
FROM JAI_OM_OE_GEN_TAXINV_T
WHERE delivery_id = cp_delivery_id;
SELECT 1
FROM
wsh_delivery_details wdd ,
wsh_new_deliveries wnd ,
wsh_delivery_assignments wda
WHERE
wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.Delivery_Id = wnd.Delivery_Id AND
wnd.Delivery_Id = cp_delivery_id AND
wdd.source_code = 'OE' AND
NVL(wdd.inv_interfaced_flag,'N') <> 'Y';
SELECT vat_inv_gen_status
FROM JAI_RGM_INVOICE_GEN_T
--WHERE delivery_id = cp_delivery_id ;
SELECT jowla.excise_invoice_no
,jowla.excise_invoice_date
FROM JAI_RGM_INVOICE_GEN_T jrigt
, JAI_OM_WSH_LINES_ALL jowla
WHERE jrigt.program_id = ln_conc_progam_id
AND jrigt.delivery_id = jowla.delivery_id
AND jowla.excise_invoice_no IS NOT NULL
AND EXISTS (SELECT 1
FROM WSH_DELIVERY_DETAILS wdd
,WSH_DELIVERY_ASSIGNMENTS wda
,OE_ORDER_HEADERS_ALL ooha
WHERE ooha.order_number = cp_order_number
AND ooha.header_id = wdd.source_header_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = jrigt.delivery_id)
AND rownum = 1;
:= 'SELECT delivery_id , delivery_date , organization_id , location_id , vat_invoice_no, '||
'party_id , party_site_id , party_type , vat_inv_gen_status , vat_acct_status, '||
'order_line_id, order_number ' ||
'FROM JAI_RGM_INVOICE_GEN_T jrigt ' ||
'WHERE regime_id = '||p_regime_id ||' '||
'AND registration_num = NVL('||lv_p_registration_num_str||',registration_num) '||
'AND (delivery_id BETWEEN NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
'NVL('||NVL(TO_CHAR(p_delivery_id_to), 'NULL')||',delivery_id)) '||
'AND EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS wdd '||
',WSH_DELIVERY_ASSIGNMENTS wda '||
',OE_ORDER_HEADERS_ALL ooha '||
'WHERE ooha.order_number BETWEEN '||
'NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) AND '||
'NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number) '||
'AND ooha.header_id = wdd.source_header_id '||
'AND wda.delivery_detail_id = wdd.delivery_detail_id '||
'AND wda.delivery_id = jrigt.delivery_id) ' ||
'AND (TRUNC(delivery_date) BETWEEN '||
'NVL(TRUNC(TO_DATE('''||pv_delivery_date_from||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date) AND '||
--'NVL(TRUNC('||pv_delivery_date_to||'),delivery_date)) '||
'NVL(TRUNC(TO_DATE('''||pv_delivery_date_to||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date)) '||
'AND organization_id = NVL('||NVL(TO_CHAR(p_organization_id), 'NULL')||',organization_id) '||
'AND location_id = NVL('||NVL(TO_CHAR(p_location_id), 'NULL')||',location_id) ' ||
'AND (vat_inv_gen_status <> ''C'' OR vat_acct_status <> ''C'') '||
-- modified by Allen Yang for bug 9666476 28-apr-2010, begin
--'ORDER BY party_id , party_type, party_site_id, order_number';
:= 'SELECT delivery_id , delivery_date , organization_id , location_id , vat_invoice_no, '||
'party_id , party_site_id , party_type , vat_inv_gen_status , vat_acct_status, '||
'order_line_id, order_number ' ||
'FROM JAI_RGM_INVOICE_GEN_T jrigt ' ||
'WHERE regime_id = '||p_regime_id ||' '||
'AND registration_num = NVL('||lv_p_registration_num_str||',registration_num) '||
'AND (delivery_id IS NULL OR (delivery_id BETWEEN '||
'NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
'NVL('||NVL(TO_CHAR(p_delivery_id_to), 'NULL')||',delivery_id) '||
'AND EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS wdd '||
',WSH_DELIVERY_ASSIGNMENTS wda '||
',OE_ORDER_HEADERS_ALL ooha '||
'WHERE ooha.order_number BETWEEN '||
'NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) AND '||
'NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number) '||
'AND ooha.header_id = wdd.source_header_id '||
'AND wda.delivery_detail_id = wdd.delivery_detail_id '||
'AND wda.delivery_id = jrigt.delivery_id))) ' ||
'AND (order_number IS NULL '||
'OR order_number BETWEEN NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) ' ||
' AND NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number)) ' ||
'AND (TRUNC(delivery_date) BETWEEN '||
--'NVL(TRUNC('||pv_delivery_date_from||'),delivery_date) AND '||
--'NVL(TRUNC('||pv_delivery_date_to||'),delivery_date)) '||
'NVL(TRUNC(TO_DATE('''||pv_delivery_date_from||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date) AND '||
'NVL(TRUNC(TO_DATE('''||pv_delivery_date_to||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date)) '||
'AND organization_id = NVL('||NVL(TO_CHAR(p_organization_id), 'NULL')||',organization_id) '||
'AND location_id = NVL('||NVL(TO_CHAR(p_location_id), 'NULL')||',location_id) '||
'AND (vat_inv_gen_status <> ''C'' OR vat_acct_status <> ''C'') '||
-- modified by Allen Yang for bug 9666476 28-apr-2010, begin
'ORDER BY party_id , party_type, party_site_id, order_number NULLS FIRST';
2. else, use excise as VAT invoice number, and update JAI_RGM_INVOICE_GEN_T
(vat_invoice_no => excise_inv_number, vat_inv_gen_status => 'C');
UPDATE JAI_OM_WSH_LINES_ALL
SET vat_invoice_no = excise_invoice_no
, vat_invoice_date = excise_invoice_date
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number
, vat_inv_gen_status = 'C'
, vat_inv_gen_err_message = NULL
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
, last_update_Date = sysdate
WHERE Delivery_id = mainrec.delivery_id;
|| API call again and again to the generation api . Just update the JAI_OM_WSH_LINES_ALL table to set the
|| vat invoice number for the delivery and continue.
*/
IF NVL(p_single_invoice_num,jai_constants.No) = jai_constants.yes -- single invoice number is true
THEN
IF NVL(lv_party_has_changed,jai_constants.value_false) = jai_constants.value_true -- party has changed
THEN
/* generate new VAT invoice number by document sequence;
2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number
, vat_inv_gen_status => 'C');
UPDATE JAI_OM_WSH_LINES_ALL
SET VAT_INVOICE_NO = lv_vat_invoice_number
, VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATE_LOGIN = fnd_global.login_id
, LAST_UPDATED_BY = fnd_global.user_id
WHERE DELIVERY_ID = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number
, vat_inv_gen_status = 'C'
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
, last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_INVOICE_GEN_T;
UPDATE JAI_OM_WSH_LINES_ALL
SET vat_invoice_no = lv_vat_invoice_number,
VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE delivery_id IN (SELECT delivery_id
FROM JAI_RGM_INVOICE_GEN_T jrigt
WHERE party_id = ln_current_party_id
AND party_site_id = ln_current_party_site_id
AND party_type = mainrec.party_type
AND vat_inv_gen_status <> 'C'
AND delivery_id BETWEEN NVL(P_DELIVERY_ID_FROM,delivery_id)
AND NVL(P_DELIVERY_ID_TO,delivery_id)
AND EXISTS (SELECT 1
FROM WSH_DELIVERY_ASSIGNMENTS wda
, WSH_DELIVERY_DETAILS wdd
, OE_ORDER_HEADERS_ALL ooha
WHERE wda.delivery_id = jrigt.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_header_id = ooha.header_id
AND ooha.order_number BETWEEN
NVL(p_order_number_from, ooha.order_number) AND
NVL(p_order_number_to, ooha.order_number))
AND trunc(delivery_Date) BETWEEN NVL(P_DELIVERY_DATE_FROM,Delivery_date)
AND NVL(P_DELIVERY_DATE_TO,delivery_date));
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number,
vat_inv_gen_status = 'C',
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id
WHERE delivery_id IN (SELECT delivery_id
FROM JAI_RGM_INVOICE_GEN_T jrigt
WHERE party_id = ln_current_party_id
AND party_site_id = ln_current_party_site_id
AND party_type = mainrec.party_type
AND vat_inv_gen_status <> 'C'
AND delivery_id BETWEEN NVL(P_DELIVERY_ID_FROM,delivery_id)
AND NVL(P_DELIVERY_ID_TO,delivery_id)
AND EXISTS (SELECT 1
FROM WSH_DELIVERY_ASSIGNMENTS wda
, WSH_DELIVERY_DETAILS wdd
, OE_ORDER_HEADERS_ALL ooha
WHERE wda.delivery_id = jrigt.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_header_id = ooha.header_id
AND ooha.order_number BETWEEN
NVL(p_order_number_from, ooha.order_number) AND
NVL(p_order_number_to, ooha.order_number))
AND trunc(delivery_Date) BETWEEN NVL(P_DELIVERY_DATE_FROM,Delivery_date)
AND NVL(P_DELIVERY_DATE_TO,delivery_date));
Fnd_File.PUT_LINE(Fnd_File.LOG, 'No. of Deliveries updated in jai_vat_processing_t: ' || SQL%ROWCOUNT);
2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
vat_inv_gen_status => 'C');
UPDATE JAI_OM_WSH_LINES_ALL
SET vat_invoice_no = lv_vat_invoice_number,
vat_invoice_date = nvl(ld_override_invoice_date ,sysdate),
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number,
vat_inv_gen_status = 'C',
vat_inv_gen_err_message = NULL ,
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
vat_inv_gen_status = 'E',
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_OM_WSH_LINES_ALL
SET vat_invoice_no = lv_excise_invoice_no
, vat_invoice_date = ld_excise_invoice_date
WHERE order_line_id = mainrec.order_line_id
AND delivery_id IS NULL;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_excise_invoice_no
, vat_inv_gen_status = 'C'
, vat_inv_gen_err_message = NULL
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
, last_update_Date = sysdate
WHERE order_line_id = mainrec.order_line_id;
2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
vat_inv_gen_status => 'C');
UPDATE JAI_OM_WSH_LINES_ALL
SET VAT_INVOICE_NO = lv_vat_invoice_number
, VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATE_LOGIN = fnd_global.login_id
, LAST_UPDATED_BY = fnd_global.user_id
WHERE order_line_id = mainrec.order_line_id
AND delivery_id IS NULL;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number
, vat_inv_gen_status = 'C'
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
, last_update_date = sysdate
WHERE order_line_id = mainrec.order_line_id;
2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_INVOICE_GEN_T;
UPDATE JAI_OM_WSH_LINES_ALL
SET vat_invoice_no = lv_vat_invoice_number
, VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, last_update_date = sysdate
, last_update_login = fnd_global.login_id
, last_updated_by = fnd_global.user_id
WHERE order_line_id = mainrec.order_line_id
AND delivery_id IS NULL;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number
, vat_inv_gen_status = 'C'
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
WHERE order_line_id = mainrec.order_line_id;
2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
vat_inv_gen_status => 'C');
UPDATE JAI_OM_WSH_LINES_ALL
SET VAT_INVOICE_NO = lv_vat_invoice_number
, VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATE_LOGIN = fnd_global.login_id
, LAST_UPDATED_BY = fnd_global.user_id
WHERE order_line_id = mainrec.order_line_id
AND delivery_id IS NULL;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number
, vat_inv_gen_status = 'C'
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
, last_update_date = sysdate
WHERE order_line_id = mainrec.order_line_id;
2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_INVOICE_GEN_T;
UPDATE JAI_OM_WSH_LINES_ALL
SET vat_invoice_no = lv_vat_invoice_number
, VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, last_update_date = sysdate
, last_update_login = fnd_global.login_id
, last_updated_by = fnd_global.user_id
WHERE order_line_id = mainrec.order_line_id
AND delivery_id IS NULL;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number
, vat_inv_gen_status = 'C'
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
WHERE order_line_id = mainrec.order_line_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_acct_status = 'C',
vat_inv_gen_err_message = NULL, /*following columns added by srjayara for bug 4702156*/
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
-- WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
vat_inv_gen_status = 'E',
request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
-- WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_acct_err_message = substr(lv_acct_process_message,1,1000),
vat_acct_status = 'E',
request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
-- WHERE delivery_id = mainrec.delivery_id;
DELETE FROM JAI_RGM_INVOICE_GEN_T
WHERE vat_inv_gen_status = 'C'
AND vat_acct_status = 'C';
SELECT aid.invoice_id
, aid.invoice_distribution_id
, aid.distribution_line_number
, aid.invoice_line_number
, aid.reversal_flag
, aid.parent_reversal_id
, aid.accrual_posted_flag
, aid.cash_posted_flag
, aid.amount
, aid.base_amount
, aid.po_distribution_id
, aid.rcv_transaction_id
, aid.org_id
, aid.accounting_event_id
, ai.vendor_id
, ai.vendor_site_id
, ai.invoice_currency_code
, aid.exchange_rate
, aid.exchange_rate_type
, aid.exchange_date
, ai.source
, ai.invoice_type_lookup_code
, ai.invoice_date
, aid.accounting_date
, ai.cancelled_date
FROM xla_ae_headers xah
, xla_transaction_entities xte
, ap_invoices_all ai
, ap_invoice_distributions_all aid
WHERE xah.je_category_name = cp_ae_category
AND xah.ledger_id = cp_sob_id
AND xah.application_id = 200
AND xah.entity_id = xte.entity_id
AND xte.application_id = 200
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
AND aid.invoice_id = ai.invoice_id
AND aid.accounting_event_id = xah.event_id
AND ai.invoice_type_lookup_code IN ('STANDARD', 'DEBIT', 'CREDIT')
AND aid.parent_reversal_id IS NULL
AND nvl(aid.reversal_flag, 'N') = 'N' /*12793930 - Ensure Repository entries are not passed if a line is discarded*/
AND ( aid.line_type_lookup_code = jai_constants.misc_line
OR EXISTS (SELECT 1
FROM jai_rcv_tp_invoices jtp
WHERE AID.invoice_id = jtp.invoice_id))
AND aid.posted_flag = 'Y'
AND xah.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
AND aid.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
AND ai.org_id = p_org_id
AND aid.org_id = p_org_id
AND NOT EXISTS( SELECT 1
FROM jai_rgm_trx_records rgtr
WHERE rgtr.source = 'AP'
AND rgtr.organization_id = p_organization_id
AND rgtr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND rgtr.source_document_id = aid.invoice_distribution_id)
AND EXISTS(SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE rgtf.source = 'AP'
AND rgtf.invoice_id = aid.invoice_id
AND rgtf.organization_id = p_organization_id --Added by zhiwei for POT Bug#12970828 on 20110915
AND rgtf.party_type = 'V');
SELECT aid.invoice_id
, aid.invoice_distribution_id
, aid.distribution_line_number
, aid.invoice_line_number
, aid.reversal_flag
, aid.parent_reversal_id
, aid.accrual_posted_flag
, aid.cash_posted_flag
, aid.amount
, aid.base_amount
, aid.po_distribution_id
, aid.rcv_transaction_id
, aid.org_id
, aid.accounting_event_id
, ai.vendor_id
, ai.vendor_site_id
, ai.invoice_currency_code
, aid.exchange_rate
, aid.exchange_rate_type
, aid.exchange_date
, ai.source
, ai.invoice_type_lookup_code
, ai.invoice_date
, aid.accounting_date
, ai.cancelled_date
FROM ap_invoices_all ai
, ap_invoice_distributions_all aid
WHERE aid.invoice_id = ai.invoice_id
AND aid.set_of_books_id = cp_sob_id
AND ai.invoice_type_lookup_code IN ('STANDARD', 'DEBIT', 'CREDIT')
AND ai.cancelled_date IS NOT NULL
AND aid.line_type_lookup_code = jai_constants.misc_line
AND aid.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
AND ai.org_id = p_org_id
AND aid.org_id = p_org_id
AND aid.parent_reversal_id IS NOT NULL
AND aid.cancellation_flag = 'Y'
AND EXISTS(SELECT 1
FROM jai_rgm_trx_records rgtr
WHERE rgtr.source = 'AP'
AND rgtr.organization_id = p_organization_id
AND rgtr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND rgtr.source_document_id = aid.parent_reversal_id )
AND NOT EXISTS(SELECT 1
FROM jai_rgm_trx_records rgtr
WHERE rgtr.source = 'AP'
AND rgtr.organization_id = p_organization_id
AND rgtr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND rgtr.source_document_id = aid.invoice_distribution_id )
AND EXISTS(SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE rgtf.source = 'AP'
AND rgtf.invoice_id = aid.invoice_id
AND rgtf.organization_id = p_organization_id--Added by zhiwei for POT Bug#12970828 on 20110915
AND rgtf.party_type = 'V');
SELECT *
FROM jai_rgm_trx_refs rgtf
WHERE line_id = pn_invoice_distribution_id
AND party_type = 'V'
AND SOURCE = 'AP';
SELECT location_id, service_type_code
FROM jai_ap_invoice_lines
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = (SELECT parent_invoice_line_number
FROM jai_ap_invoice_lines jail
WHERE jail.invoice_line_number = pn_invoice_line_number
AND jail.invoice_id = pn_invoice_id)
AND parent_invoice_line_number IS NULL;
SELECT payment_currency_code,
exchange_rate,
exchange_date,
exchange_rate_type
FROM ap_invoices_all
WHERE invoice_id = pn_invoice_id;
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = (SELECT regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = jai_constants.service_regime)
and organization_id = p_organization_id
and location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'INV_ORG_CLASSIFICATION'
and attribute_value <> 'ORGANIZATION'
and organization_id = p_organization_id
and location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id)
);
SELECT 'Y'
FROM ap_invoice_distributions_all aid1,
ap_invoice_distributions_all aid2
WHERE aid1.parent_reversal_id = pn_parent_reversal_id
AND aid2.invoice_distribution_id = pn_parent_reversal_id
AND aid1.period_name = aid2.period_name;
SELECT SUM(tax_amt)
FROM jai_cmn_document_taxes jcdt,
jai_cmn_taxes_all jcta
WHERE jcdt.source_doc_id = pn_invoice_id
--Add by Chong for bug#13358557 08-NOV-2011, begin
----------------------------------------------------------------------------------------------
AND jcdt.source_doc_parent_line_no = pn_line_number
----------------------------------------------------------------------------------------------
--Add by Chong for bug#13358557 08-NOV-2011, end
AND jcdt.modvat_flag = 'Y'
AND jcdt.tax_id = jcta.tax_id
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT sum(jamt.tax_amount)
FROM jai_ap_match_inv_taxes jamt,
jai_cmn_taxes_all jcta
WHERE jamt.invoice_id = pn_invoice_id
--Add by Chong for bug#13358557 08-NOV-2011, begin
----------------------------------------------------------------------------------------------
AND jamt.parent_invoice_line_number = pn_line_number
----------------------------------------------------------------------------------------------
--Add by Chong for bug#13358557 08-NOV-2011, end
AND jamt.tax_id = jcta.tax_id
AND jamt.recoverable_flag = 'Y'
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT jcta.tax_type, jcdt.tax_amt, aida.invoice_distribution_id,
aida.accounting_date, aia.invoice_date, aida.po_distribution_id
FROM jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta
, ap_invoice_distributions_all aida
, ap_invoices_all aia
WHERE jcdt.source_doc_id = pn_invoice_id
AND jcdt.source_doc_parent_line_no = pn_line_number
AND aia.invoice_id = pn_invoice_id
AND aida.invoice_id = pn_invoice_id
AND aida.invoice_line_number = jcdt.source_doc_line_id
AND aida.po_distribution_id IS NULL
AND jcdt.modvat_flag = 'Y'
AND jcdt.tax_id = jcta.tax_id
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records
WHERE SOURCE = 'AP_REVERSAL'
AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND source_document_id = aida.invoice_distribution_id)
AND EXISTS(SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE rgtf.source = 'AP'
AND rgtf.invoice_id = pn_invoice_id
AND rgtf.party_type = 'V')
UNION ALL
SELECT jcta.tax_type, jamt.tax_amount tax_amt, aida.invoice_distribution_id,
aida.accounting_date, aia.invoice_date, aida.po_distribution_id
FROM jai_ap_match_inv_taxes jamt
, jai_cmn_taxes_all jcta
, ap_invoice_distributions_all aida
, ap_invoices_all aia
WHERE jamt.invoice_id = pn_invoice_id
--AND jamt.invoice_line_number <> pn_line_number
AND jamt.parent_invoice_line_number = pn_line_number
AND jamt.invoice_distribution_id = aida.invoice_distribution_id
AND jamt.tax_id = jcta.tax_id
AND jamt.recoverable_flag = 'Y'
AND aida.po_distribution_id IS NOT NULL
AND aia.invoice_id = pn_invoice_id
AND aida.invoice_id = pn_invoice_id
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records
WHERE SOURCE = 'AP_REVERSAL'
AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND source_document_id = aida.invoice_distribution_id)
AND EXISTS(SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE rgtf.source = 'AP'
AND rgtf.invoice_id = pn_invoice_id
AND rgtf.party_type = 'V');
SELECT jcta.tax_type, jcdt.tax_amt, aida.invoice_distribution_id,
aida.accounting_date, aia.invoice_date, aida.po_distribution_id
FROM jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta
, ap_invoices_all aia
, ap_invoice_distributions_all aida
WHERE jcdt.source_doc_id = pn_invoice_id
AND jcdt.source_doc_parent_line_no = pn_line_number
AND aia.invoice_id = pn_invoice_id
AND aida.invoice_id = pn_invoice_id
AND aida.invoice_line_number = jcdt.source_doc_line_id
AND aida.po_distribution_id IS NULL
AND jcdt.modvat_flag = 'Y'
AND jcdt.tax_id = jcta.tax_id
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
AND EXISTS (SELECT 1
FROM jai_rgm_trx_records
WHERE SOURCE = 'AP_REVERSAL'
AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND source_document_id = aida.invoice_distribution_id)
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr,
jai_rgm_trx_refs jrtf --Added by Qiong for bug13439861
WHERE jrtr.SOURCE = 'AP_CLAIM'
AND jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
AND jrtr.source_document_id = pn_invoice_payment_id
--Added by Qiong for bug13439861 begin
-------------------------------------------
AND jrtr.reference_id = jrtf.reference_id
AND jrtf.item_line_id = pn_line_number
-------------------------------------------
--Added by Qiong for bug13439861 end
)
UNION ALL
SELECT jcta.tax_type, jamt.tax_amount tax_amt, aida.invoice_distribution_id,
aida.accounting_date, aia.invoice_date, aida.po_distribution_id
FROM jai_ap_match_inv_taxes jamt
, jai_cmn_taxes_all jcta
, ap_invoice_distributions_all aida
, ap_invoices_all aia
WHERE jamt.invoice_id = pn_invoice_id
--AND jamt.invoice_line_number <> pn_line_number
AND jamt.parent_invoice_line_number = pn_line_number
AND jamt.invoice_distribution_id = aida.invoice_distribution_id
AND jamt.tax_id = jcta.tax_id
AND jamt.recoverable_flag = 'Y'
AND aida.po_distribution_id IS NOT NULL
AND aia.invoice_id = pn_invoice_id
AND aida.invoice_id = pn_invoice_id
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
AND EXISTS (SELECT 1
FROM jai_rgm_trx_records
WHERE SOURCE = 'AP_REVERSAL'
AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND source_document_id = aida.invoice_distribution_id)
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr,
jai_rgm_trx_refs jrtf--Added by Qiong for bug13439861
WHERE jrtr.SOURCE = 'AP_CLAIM'
AND jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
AND jrtr.source_document_id = pn_invoice_payment_id
--Added by Qiong for bug13439861 begin
-----------------------------------------
AND jrtr.reference_id = jrtf.reference_id
AND jrtf.item_line_id = pn_line_number
-----------------------------------------
--Added by Qiong for bug13439861 end
);
SELECT SUM(amount)
FROM ap_invoice_lines_all
WHERE invoice_id = pn_invoice_id
AND line_type_lookup_code IN ('ITEM', 'MISCELLANEOUS')
GROUP BY invoice_id;
SELECT jsir.*
FROM jai_st_invoice_reversal jsir
WHERE jsir.source = 'AP'
--Add by Chong for bug#13259755 on 27-OCT-2011 start
AND jsir.organization_id = p_organization_id
--Add by Chong for bug#13259755 on 27-OCT-2011 end
AND jsir.tax_to_be_adjusted > 0
ORDER BY jsir.invoice_id;
SELECT jsir.*
FROM jai_st_invoice_reversal jsir
WHERE jsir.source = 'AP'
AND jsir.date_of_reversal BETWEEN p_from_date AND p_to_date;
SELECT nvl(aipa.amount, 0) claim_amt, aipa.invoice_payment_id, aipa.accounting_date
FROM ap_invoice_payments_all aipa
WHERE aipa.invoice_id = pn_invoice_id
AND aipa.accounting_date <= p_to_date
-- AND aipa.invoice_payment_id > nvl(pn_max_payment_id, 0) Commented by Chong for bug#13259755 on 27-OCT-2011
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr,
jai_rgm_trx_refs jrtf--Added by Qiong for bug13439861
WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
AND jrtr.source = 'AP_CLAIM'
AND jrtr.source_document_id = aipa.invoice_payment_id
--Added by Qiong for bug13439861 begin
-----------------------------------------
AND jrtr.reference_id = jrtf.reference_id
AND jrtf.item_line_id = pn_line_number
-----------------------------------------
--Added by Qiong for bug13439861 end
)
AND EXISTS (SELECT 1
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = pn_invoice_id
AND aida.invoice_line_number = pn_line_number
AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aipa.accounting_date,
aida.accounting_date, 'AP') = 'Y')
UNION ALL
SELECT abs(nvl(aila.amount, 0)) claim_amt, aipa.invoice_payment_id, aipa.accounting_date
FROM ap_invoice_lines_all aila,
ap_invoice_payments_all aipa
WHERE aila.invoice_id = pn_invoice_id
AND aila.line_type_lookup_code = 'PREPAY'
AND aila.prepay_invoice_id = aipa.invoice_id
AND aila.accounting_date <= p_to_date
AND nvl(aila.amount, 0) <> 0
/*Commented by Chong for bug#13259755 on 09-NOV-2011 Start
AND (aipa.invoice_payment_id > nvl(pn_max_payment_id, 0)
OR (aipa.invoice_payment_id < nvl(pn_max_payment_id, 0)
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr
WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
AND jrtr.source = 'AP_CLAIM'
AND jrtr.source_document_id = aipa.invoice_payment_id)))
Commented by Chong for bug#13259755 on 09-NOV-2011 End*/
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr,
jai_rgm_trx_refs jrtf--Added by Qiong for bug13439861
WHERE jrtr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL'
AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
AND jrtr.source = 'AP_CLAIM'
AND jrtr.source_document_id = aipa.invoice_payment_id
--Added by Qiong for bug13439861 begin
-----------------------------------------
AND jrtr.reference_id = jrtf.reference_id
AND jrtf.item_line_id = pn_line_number
-----------------------------------------
--Added by Qiong for bug13439861 end
)
AND EXISTS (SELECT 1
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = pn_invoice_id
AND aida.invoice_line_number = pn_line_number
AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aila.accounting_date,
aida.accounting_date, 'AP') = 'Y')
ORDER BY 2;
SELECT service_type_code
FROM jai_ap_invoice_lines
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = pn_invoice_line_number
AND parent_invoice_line_number IS NULL;
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_ap_ref_records.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AP',
p_source_trx_type => lv_accounting_event,
p_source_table_name => lv_source_table,
p_source_document_id => rec_ap_dist.invoice_distribution_id,
p_transaction_date => ld_accounting_date, --rec_ap_dist.invoice_date, /* changes made for bug 13242818 by amandali */
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => rec_ap_ref_records.tax_amount,
p_assessable_value => NULL,
p_tax_rate => rec_ap_ref_records.tax_rate,
p_reference_id => rec_ap_ref_records.reference_id,
p_batch_id => p_batch_id,
p_called_from => lv_called_from,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_discounted_amount => ln_discounted_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => ld_accounting_date,
p_currency_code => rec_get_curr_dtls.payment_currency_code,
p_curr_conv_date => rec_get_curr_dtls.exchange_date,
p_curr_conv_type => rec_get_curr_dtls.exchange_rate_type,
p_curr_conv_rate => rec_get_curr_dtls.exchange_rate,
p_trx_amount => rec_ap_ref_records.trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_ap_ref_records.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AP',
p_source_trx_type => lv_accounting_event,
p_source_table_name => lv_source_table,
p_source_document_id => rec_ap_dist.invoice_distribution_id,
p_transaction_date => ld_accounting_date,
p_account_name => lv_account_name,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => rec_ap_ref_records.tax_amount,
p_assessable_value => NULL,
p_tax_rate => rec_ap_ref_records.tax_rate,
p_reference_id => rec_ap_ref_records.reference_id,
p_batch_id => p_batch_id,
p_called_from => lv_called_from,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_discounted_amount => ln_discounted_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => ld_accounting_date,
p_currency_code => rec_get_curr_dtls.payment_currency_code,
p_curr_conv_date => rec_get_curr_dtls.exchange_date,
p_curr_conv_type => rec_get_curr_dtls.exchange_rate_type,
p_curr_conv_rate => rec_get_curr_dtls.exchange_rate,
p_trx_amount => rec_ap_ref_records.trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_reversal_entries.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AP_REVERSAL',
p_source_trx_type => 'REVERSAL_ACCOUNTING',
p_source_table_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
p_source_document_id => rec_reversal_entries.invoice_distribution_id,
--p_transaction_date => rec_reversal_entries.invoice_date,--Commented by zhiwei for POT Bug#12970828 on 20110913
p_transaction_date => reversal_trxn_rec.date_of_reversal, --Added by zhiwei for POT Bug#12970828 on 20110913
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_tax_amount,
p_assessable_value => NULL,
p_tax_rate => lr_trx_refs.tax_rate,
p_reference_id => lr_trx_refs.reference_id,
p_batch_id => p_batch_id,
p_called_from => lv_called_from,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_discounted_amount => ln_discounted_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => ld_accounting_date,
p_currency_code => rec_get_curr_dtls.payment_currency_code,
p_curr_conv_date => rec_get_curr_dtls.exchange_date,
p_curr_conv_type => rec_get_curr_dtls.exchange_rate_type,
p_curr_conv_rate => rec_get_curr_dtls.exchange_rate,
p_trx_amount => ln_tax_amount,--lr_trx_refs.trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_claim_entries.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AP_CLAIM',
p_source_trx_type => 'CLAIM_ACCOUNTING',
p_source_table_name => 'AP_INVOICE_PAYMENTS_ALL',
p_source_document_id => rec_claim_line.invoice_payment_id,
p_transaction_date => ld_accounting_date,
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_tax_amount,
p_assessable_value => NULL,
p_tax_rate => lr_trx_refs.tax_rate,
p_reference_id => lr_trx_refs.reference_id,
p_batch_id => p_batch_id,
p_called_from => lv_called_from,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_discounted_amount => ln_discounted_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => ld_accounting_date,
p_currency_code => rec_get_curr_dtls.payment_currency_code,
p_curr_conv_date => rec_get_curr_dtls.exchange_date,
p_curr_conv_type => rec_get_curr_dtls.exchange_rate_type,
p_curr_conv_rate => rec_get_curr_dtls.exchange_rate,
p_trx_amount => ln_tax_amount,--lr_trx_refs.trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');