[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select interface_header_context
from ra_customer_trx_all
where customer_trx_id = cp_customer_trx_id;
SELECT
nvl(ja.inclusive_tax_flag, 'N') inclusive_tax_flag
FROM
jai_ap_tds_years ja
WHERE ja.legal_entity_id = pn_org_id
AND sysdate between ja.start_date and ja.end_date;
SELECT
cust_trx_type_id
FROM
ra_customer_trx_all
WHERE customer_trx_id = pn_customer_trx_id;
Added the substr function to the update statement that updates the
JAI_AR_TRX_INS_LINES_T table . If due to some reason the error message is
very long ,then it can be a potential problem.Because of this the program
should not halt.
5. 08/04/2003 Sriram - Bug # 2779967
Added logic to see that only records that belong to the current operating unit need
to be picked up for processing.This was done because records are inserted into the
JAI_AR_TRX_INS_LINES_T table from various 'India Local Receivables' responsibility
attached to various org ids , The concurrent program is not scheduled , but run by
the user , it picks up the records not only for the current org id but also for other
org ids as well which causes the problem.
6. 22/08/2003 Sriram - Bug # 3068927.
Added a new parameter P_ORG_ID to the Procedure. This has been done a new parameter
has been added in the concurrent program definition "JAINMREQ" to enable conflict domains.
The Concurrent program 'India Local Concurrent For Processing Order Lines to AR" has
been set incompatible to itself and also to autoinvoice import program . Because of the
previous bugfix , the concurrent has to be scheduled for each org id , hence causing performance
bottleneck because until one concurrent program runs , all others have to wait in pending state.
Hence , by using the conflict domains concept with the domain as org id , we are ensuring that
the concurrent are incompatible to itself only to the extent of those running in the same org id
7. 30/10/2003 Added another parameters P_all_orgs . This parameter is used for indicating whether to process for all
org ids or for the org id entered.
P_Org_id parameter is set as an optional parameter
8. 09/03/2004 ssumaith - bug# 3491600 file version 618.1
incorrect exception handling was done. variable width was smaller than the actual
width of the string assigned to the variable. This was causing the exception
'numeric or value error.'
9. 2004/08/11 Aiyer for bug#3826140. Version#115.1
Issue:-
Lines marked as deleted get reprocessed when a record is submitted for reprocessing from the the India Resubmit Errored OM
Tax Records form.
Reason:-
The current procedure previously used to also consider those records which have been marked as deleted.
Fix:-
The cursor temp_rec has been modified to discard all those lines which have been marked as 'R' or 'D'.
Dependency Due to this Bug:-
None
10. 2004/10/21 Aiyer for bug#3839560. Version#115.2
Issue:-
India Local Concurrent to Process Order Lines To AR corrrupts data in Base AR tables when two instances of this program
are run simultaneously with Process of Orgs = 'Y'
Reason:-
This is because the procedure ja_in_ra_order_lines_insert does not implement locking of records while processing in batch mode with Process of Orgs = 'Y'
Fix:-
This fix has been done in the procedure ja_ar_rec_process_validate.val_revrec_records called from procedure ja_in_ra_order_lines_insert.
Dependency Due to this Bug:-
This version of the file is dependent on the file jai_ar_match_tax_pkg.process_from_order_line version (115.1) due to the additions of a new parameter p_org_id.
It is also dependent on ja_in_ar_rec_prc_val_b.pls (115.0),ja_in_ar_rec_prc_val_s.pls (115.0) as jai_ar_match_tax_pkg.process_from_order_line version (115.1) calls
ja_ar_rec_process_validate.val_revrec_records(115.0).
11. 08-Jun-2005 Version 116.2 jai_ar_match_tax -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
12 14-Jun-2005 rchandan for bug#4428980, Version 116.3
Modified the object to remove literals from DML statements and CURSORS.
13 23-Jun-2005 Ramananda for bug#4468353 ,version 116.4
Issue:
Impact on IL due to SLA Uptake by AR
Reason:
India Localization taxes and charges are inserted into RA_CUSTOIMER_TRX_LINES_ALL and
RA_CUST_LINES_GL_DIST_ALL, as Tax and Freight lines.Since India Localization directly updates
the above-mentioned tables, the accounting happens through the base AR accounting itself.
In R12, since the AR accounting will be handled through SLA IL tax lines that are inserted
in the RA_CUSTOIMER_TRX_LINES_ALL and RA_CUST_TRX_LINE_GL_DIST_ALL will be impacted
Fix:
IL should ensure the following while inserting into RA_CUST_LINES_GL_DIST_ALL table:
1. The tax and freight lines that are inserted should be inserted before the associated base item lines
are posted to GL. This should be achieved by checking the Posting_Status by IL.
A new cursor is created to check the gl_posted_date for the base item. If the gl_posted_date is null,
then it inserts the tax and freight lines
2. Each of the Tax and freight lines should carry the same Accounting event information as the base
line. Event_Id field should be punched with the value as on the Item line. This value can be derived
from the call to 'Event Engine' for each line. IL will call the Event Engine API, and derive the
Event_Id for the base item line. This Event_Id will be punched to all the tax and freight lines related
to the base item line
A call is made to ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event) to update the event_id field
Issue:
Impact on IL due to ebTax Uptake by AR
Reason:
India Localization tax lines are inserted into AR transaction tables with an AR Tax code (Vat_Tax_Id).
In R12, the AR tax engine will be replace by ebTax. Due to this, all the tax code related setups will
be made in ebTax and not in AR. Since India Localization uses the Vat_Tax_Id for populating the
tax lines into the AR transaction tables and it will not uptake ebTax, it would be mandatory for
IL to have setups under the ebTax that can be used in the above transactions.
Fix:
Query logic is changed. Instead of querying vat_Tax_id from ar_vat_tax_all , tax_rate_id of zx_rates_b
is queried
14 25-Apr-2007 cbabu for Bug#6012570 (5876390), File Version 120.5 (115.5)
FP: Project billing implementation.
New concurrent JAINIPTR created for Project taxes to flow into AR and related
changes are made in process_batch
Excise invoice will not be updated in the Referece_field for Project Invoices as it is
giving error in the Invoices Form when Queried for Project Invoice
15. 17-09-2007 sacsethi for Bug#6407648 , File Version 120.3.12000000.3/ 120.11
Problem - R.TST1203.XB2.QA:INCORRECT IL TAXES ON RMA CM
Reason - Variable ln_created_by ,ld_creation_date initialization was missing .
Solution - Procedure maintain_applications is modified with initialization.
16. 18-sep-2007 anujsax for Bug#5636544, File Version 120.11
forward porting R11 bug 5629319 into R12 bug 5636544
17. 26-jan-2008 ssumaith - bug#6776085
following changes are done.
a. removed the code changes done for bug#5636544
b.did the code changes into the mainline for bug#6764386
18. 28-Jan-2009 CSahoo for bug#7645588, File Version 120.19.12010000.2
Issue: TAX ENTRIES ARE NOT VISIBLE IN DISTRIBUTIONS
Fix: Modified the code in the process_from_order_line. added the cursor cur_event_id
to get the event id. This cursor would get called only in case of a credit memo having
accounting rules defined. This would provide the event id of the REC account class.
The tax entries also need to be latched to this event id. so passed this event id to the
procedure insert_trx_line_gl_dist to get stamped in the table ra_cust_trx_line_gl_dist_all
table.
19 06-FEB-2009 JMEENA for bug#8232976
Created cursor c_get_context and to get the interface_header_context of the invoice and checked if it is PROJECT INVOICE.
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
jai_ar_match_tax_pkg.process_batch
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.2 3839560 IN60105D2 jai_ar_match_tax_pkg.process_from_order_line 115.1 Aiyer 21/10/2004 New parameter p_org_id added
ja_in_ar_rec_prc_val_s.pls 115.0 Aiyer 21/10/2004 jai_ar_match_tax_pkg.process_from_order_line calls
ja_in_ar_rec_prc_val_b.pls 115.0 Aiyer 21/10/2004
ja_ar_rec_process_validate.val_revrec_records
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
/* Bug 5243532. Added by Lakshmi Gopalsami
Removed the reference to fnd_profile.value('ORG_ID')
v_org_id := FND_PROFILE.VALUE('ORG_ID');
SELECT DISTINCT customer_trx_id
FROM JAI_AR_TRX_INS_LINES_T
WHERE source = lv_source
AND org_id = nvl(ln_org_id, org_id)
MINUS
SELECT customer_trx_id
FROM JAI_AR_TRX_INS_LINES_T temp_dtl
WHERE source = 'RAXTRX'
AND org_id = nvl(ln_org_id, org_id)
AND error_flag IN ('R','D')
)
LOOP
/*Start commented by kunkumar for bug#6066813
-- Start, bug#6012570 (5876390)
*/ -- Revoked comments for projects 6012570
lv_projects_flag := null;
Rollback all inserts , updates , deletes which have happened in
the procedure and update the temp_lines_insert procedure
setting the error flag to 'R' and err_mesg to ERRBUF
*/
var_error_mesg := 'Error from called unit jai_ar_match_tax_pkg.process_from_order_line';
delete from jai_ar_trx_ins_lines_t
WHERE customer_trx_id = temp_rec.customer_trx_id;
UPDATE JAI_AR_TRX_INS_LINES_T
SET ERROR_FLAG = 'R' ,
ERR_MESG = SUBSTR(ERRBUF,1,230) -- substr added by sriram Bug # 2740546
WHERE CUSTOMER_TRX_ID = var_cust_trx_id;
UPDATE JAI_AR_TRX_INS_LINES_T
SET ERROR_FLAG = 'R' ,
ERR_MESG = SUBSTR(ERRBUF,1,230) -- substr added by sriram Bug # 2740546
WHERE CUSTOMER_TRX_ID = var_cust_trx_id;
SELECT 1
FROM user_procedures
WHERE object_name = cp_object_name
AND procedure_name = cp_procedure_name ;
SELECT payment_schedule_id
FROM ar_payment_schedules_all
WHERE customer_trx_id = cp_customer_trx_id;
SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cp_customer_trx_id
AND account_class = lv_account_class_rec --'REC'
AND latest_rec_flag = jai_constants.yes; --'Y';
p_event_mode => 'UPDATE',
p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
p_mode => 'SINGLE',
p_key_value => ln_gl_dist_id); */
p_event_mode => ''UPDATE'',
p_table_name => ''RA_CUST_TRX_LINE_GL_DIST'',
p_mode => ''SINGLE'',
p_key_value => :1
);
p_event_mode => 'UPDATE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'SINGLE',
p_key_value => rec_mrc.payment_schedule_id); */
p_event_mode => ''UPDATE'',
p_table_name => ''AR_PAYMENT_SCHEDULES'',
p_mode => ''SINGLE'',
p_key_value => :1
);
p_event_mode => ''UPDATE'',
p_table_name => ''AR_PAYMENT_SCHEDULES'',
p_mode => ''SINGLE'',
p_key_value => :1
);
( select receivable_application_id
from ar_receivable_applications_all
where customer_trx_id = p_customer_trx_id
)
LOOP
lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
p_event_mode => ''UPDATE'',
p_table_name => ''AR_RECEIVABLE_APPLICATIONS'',
p_mode => ''SINGLE'',
p_key_value => :1
);
SELECT jai_ar_rec_appl_audits_s.nextval
FROM dual;
ln_last_updated_by jai_ar_payment_audits.last_updated_by%TYPE;
ld_last_update_date jai_ar_payment_audits.last_update_date%TYPE;
ln_last_update_login jai_ar_payment_audits.last_update_login%TYPE;
ln_last_updated_by := TO_NUMBER(fnd_profile.value('USER_ID'));
ld_last_update_date := SYSDATE;
ln_last_update_login := TO_NUMBER(fnd_profile.value('LOGIN_ID'));
IF p_operation_type = 'INSERT' THEN
-- Date 17/09/2007 by sacsethi for bug 6407648
ln_created_by := ln_last_updated_by;
ld_creation_date := ld_last_update_date;
INSERT INTO jai_ar_rec_appl_audits(
rec_appl_audit_id,
concurrent_req_num,
customer_trx_id,
receivable_application_id,
aapp_old,
acctd_aapp_from_old,
acctd_aapp_to_old,
tapp_old,
fapp_old,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT p_rec_appl_audit_id,
p_concurrent_req_num,
p_customer_trx_id,
p_receivable_application_id,
amount_applied,
acctd_amount_applied_from,
acctd_amount_applied_to,
tax_applied,
freight_applied,
ln_created_by,
ld_creation_date,
ln_last_updated_by,
ld_last_update_date,
ln_last_update_login
FROM ar_receivable_applications_all
WHERE customer_trx_id = p_customer_trx_id
AND receivable_application_id = p_receivable_application_id;
ELSIF p_operation_type = 'UPDATE' THEN
UPDATE jai_ar_rec_appl_audits a
SET (aapp_new,
acctd_aapp_applied_from_new,
acctd_aapp_applied_to_new,
tapp_new,
fapplied_new,
last_updated_by,
last_update_date,
last_update_login) =
(SELECT amount_applied,
acctd_amount_applied_from,
acctd_amount_applied_to,
tax_applied,
freight_applied,
ln_last_updated_by,
ld_last_update_date,
ln_last_update_login
FROM ar_receivable_applications_all b
WHERE customer_trx_id = a.customer_trx_id
AND receivable_application_id = a.receivable_application_id)
WHERE customer_trx_id = p_customer_trx_id
AND receivable_application_id = p_receivable_application_id
AND rec_appl_audit_id = p_rec_appl_audit_id;
SELECT jai_ar_payment_audits_s.nextval
FROM dual;
ln_last_updated_by jai_ar_payment_audits.last_updated_by%TYPE;
ld_last_update_date jai_ar_payment_audits.last_update_date%TYPE;
ln_last_update_login jai_ar_payment_audits.last_update_login%TYPE;
ln_last_updated_by := TO_NUMBER(fnd_profile.value('USER_ID'));
ld_last_update_date := SYSDATE;
ln_last_update_login := TO_NUMBER(fnd_profile.value('LOGIN_ID'));
IF p_operation_type = 'INSERT' THEN
ln_created_by := ln_last_updated_by;
ld_creation_date := ld_last_update_date;
INSERT INTO jai_ar_payment_audits(
payment_audit_id,
concurrent_req_num,
payment_schedule_id,
cm_customer_trx_id,
invoice_customer_trx_id,
original_customer_trx_id,
ado_old,
to_old,
fo_old,
aapp_old,
adr_old,
fr_old,
tr_old,
acctd_adr_old,
acred_old,
alio_old,
status_old,
gl_date_closed_old,
actual_date_closed_old,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT p_payment_audit_id,
p_concurrent_req_num,
payment_schedule_id,
p_cm_customer_trx_id,
p_invoice_customer_trx_id,
p_customer_trx_id,
amount_due_original,
tax_original,
freight_original,
amount_applied,
amount_due_remaining,
freight_remaining,
tax_remaining,
acctd_amount_due_remaining,
amount_credited,
amount_line_items_original,
status,
gl_date_closed,
actual_date_closed,
ln_created_by,
ld_creation_date,
ln_last_updated_by,
ld_last_update_date,
ln_last_update_login
FROM ar_payment_schedules_all
WHERE customer_trx_id = p_customer_trx_id
AND payment_schedule_id = NVL(p_payment_schedule_id, payment_schedule_id);
ELSIF p_operation_type = 'UPDATE' THEN
UPDATE jai_ar_payment_audits a
SET (ado_new,
to_new,
fo_new,
aapp_new,
adr_new,
fr_new,
tr_new,
acctd_adr_new,
acred_new,
alio_new,
status_new,
gl_date_closed_new,
actual_date_closed_new,
last_updated_by,
last_update_date,
last_update_login) =
(SELECT amount_due_original,
tax_original,
freight_original,
amount_applied,
amount_due_remaining,
freight_remaining,
tax_remaining,
acctd_amount_due_remaining,
amount_credited,
amount_line_items_original,
status,
gl_date_closed,
actual_date_closed,
ln_last_updated_by,
ld_last_update_date,
ln_last_update_login
FROM ar_payment_schedules_all b
WHERE customer_trx_id = a.original_customer_trx_id
AND payment_schedule_id = a.payment_schedule_id)
WHERE original_customer_trx_id = p_customer_trx_id
AND payment_schedule_id = NVL(p_payment_schedule_id, payment_schedule_id)
AND payment_audit_id = p_payment_audit_id;
PROCEDURE insert_trx_line_gl_dist(p_account_class IN ra_cust_trx_line_gl_dist_all.account_class%TYPE,
p_account_set_flag IN ra_cust_trx_line_gl_dist_all.account_set_flag%TYPE,
p_acctd_amount IN ra_cust_trx_line_gl_dist_all.acctd_amount%TYPE,
p_amount IN ra_cust_trx_line_gl_dist_all.amount%TYPE,
p_code_combination_id IN ra_cust_trx_line_gl_dist_all.code_combination_id%TYPE,
p_cust_trx_line_gl_dist_id IN ra_cust_trx_line_gl_dist_all.cust_trx_line_gl_dist_id%TYPE,
p_cust_trx_line_salesrep_id IN ra_cust_trx_line_gl_dist_all.cust_trx_line_salesrep_id%TYPE,
p_customer_trx_id IN ra_cust_trx_line_gl_dist_all.customer_trx_id%TYPE,
p_customer_trx_line_id IN ra_cust_trx_line_gl_dist_all.customer_trx_line_id%TYPE,
p_gl_date IN ra_cust_trx_line_gl_dist_all.gl_date%TYPE,
p_last_update_date IN ra_cust_trx_line_gl_dist_all.last_update_date%TYPE,
p_last_updated_by IN ra_cust_trx_line_gl_dist_all.last_updated_by%TYPE,
p_creation_date IN ra_cust_trx_line_gl_dist_all.creation_date%TYPE,
p_created_by IN ra_cust_trx_line_gl_dist_all.created_by%TYPE,
p_last_update_login IN ra_cust_trx_line_gl_dist_all.last_update_login%TYPE,
p_org_id IN ra_cust_trx_line_gl_dist_all.org_id%TYPE,
p_percent IN ra_cust_trx_line_gl_dist_all.percent%TYPE,
p_posting_control_id IN ra_cust_trx_line_gl_dist_all.posting_control_id%TYPE,
p_set_of_books_id IN ra_cust_trx_line_gl_dist_all.set_of_books_id%TYPE,
p_seq_id OUT NOCOPY NUMBER,
p_process_status OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2,
p_event_id IN NUMBER DEFAULT NULL) --added for bug#7645588
IS
CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type,
cp_procedure_name user_procedures.procedure_name%type) IS
SELECT 1
FROM user_procedures
WHERE object_name = cp_object_name
AND procedure_name = cp_procedure_name ;
SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL
FROM dual;
INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL(account_class,
account_set_flag,
acctd_amount,
amount,
code_combination_id,
cust_trx_line_gl_dist_id,
cust_trx_line_salesrep_id,
customer_trx_id,
customer_trx_line_id,
gl_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
percent,
posting_control_id,
set_of_books_id,
event_id) --added for bug#7645588
VALUES(p_account_class,
p_account_set_flag,
p_acctd_amount,
p_amount,
p_code_combination_id,
ln_cust_trx_line_gl_dist_id,
p_cust_trx_line_salesrep_id,
p_customer_trx_id,
p_customer_trx_line_id,
p_gl_date,
p_last_update_date,
p_last_updated_by,
p_creation_date,
p_created_by,
p_last_update_login,
p_org_id,
p_percent,
p_posting_control_id,
p_set_of_books_id,
p_event_id); --added for bug#7645588
p_event_mode => ''INSERT'',
p_table_name => ''RA_CUST_TRX_LINE_GL_DIST'',
p_mode => ''SINGLE'',
p_key_value => :1
);
END insert_trx_line_gl_dist;
PROCEDURE insert_trx_lines(p_extended_amount IN ra_customer_trx_lines_all.extended_amount%TYPE,
p_taxable_amount IN ra_customer_trx_lines_all.taxable_amount%TYPE,
p_customer_trx_line_id IN ra_customer_trx_lines_all.customer_trx_line_id%TYPE,
p_last_update_date IN ra_customer_trx_lines_all.last_update_date%TYPE,
p_last_updated_by IN ra_customer_trx_lines_all.last_updated_by%TYPE,
p_creation_date IN ra_customer_trx_lines_all.creation_date%TYPE,
p_created_by IN ra_customer_trx_lines_all.created_by%TYPE,
p_last_update_login IN ra_customer_trx_lines_all.last_update_login%TYPE,
p_customer_trx_id IN ra_customer_trx_lines_all.customer_trx_id%TYPE,
p_line_number IN ra_customer_trx_lines_all.line_number%TYPE,
p_set_of_books_id IN ra_customer_trx_lines_all.set_of_books_id%TYPE,
p_link_to_cust_trx_line_id IN ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE,
p_line_type IN ra_customer_trx_lines_all.line_type%TYPE,
p_org_id IN ra_customer_trx_lines_all.org_id%TYPE,
p_uom_code IN ra_customer_trx_lines_all.uom_code%TYPE,
p_autotax IN ra_customer_trx_lines_all.autotax%TYPE,
p_vat_tax_id IN ra_customer_trx_lines_all.vat_tax_id%TYPE,
p_interface_line_context IN ra_customer_trx_lines_all.interface_line_context%TYPE,
p_interface_line_attribute6 IN ra_customer_trx_lines_all.interface_line_attribute6%TYPE,
p_interface_line_attribute3 IN ra_customer_trx_lines_all.interface_line_attribute3%TYPE,
p_process_status OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2)
IS
BEGIN
p_process_status := jai_constants.successful;
INSERT INTO RA_CUSTOMER_TRX_LINES_ALL ( extended_amount,
taxable_amount,
customer_trx_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
line_number,
set_of_books_id,
link_to_cust_trx_line_id,
line_type,
org_id,
uom_code,
autotax,
vat_tax_id,
interface_line_context,
interface_line_attribute6,
interface_line_attribute3)
VALUES ( p_extended_amount,
p_taxable_amount,
p_customer_trx_line_id,
p_last_update_date,
p_last_updated_by,
p_creation_date,
p_created_by,
p_last_update_login,
p_customer_trx_id,
p_line_number,
p_set_of_books_id,
p_link_to_cust_trx_line_id,
p_line_type,
p_org_id,
p_uom_code,
p_autotax,
p_vat_tax_id,
p_interface_line_context,
p_interface_line_attribute6,
p_interface_line_attribute3);
END insert_trx_lines;
PROCEDURE delete_trx_data(p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
p_link_to_cust_trx_line_id IN ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE DEFAULT NULL,
p_process_status OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2)
IS
/* Ramananda for bug#5219225. */
lv_object_name user_procedures.object_name%type ;
SELECT NVL(SUM(amount),0) amount,
NVL(SUM(acctd_amount),0) acctd_amount,
MAX(acctd_amount) max_acctd_amount
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cp_customer_trx_id
AND account_class IN (lv_account_class_tax,lv_account_class_freight);
CURSOR cur_temp_lines_insert( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
cp_link_to_cust_trx_line_id JAI_AR_TRX_INS_LINES_T.link_to_cust_trx_line_id%TYPE DEFAULT NULL)
IS
SELECT *
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = cp_customer_trx_id
AND link_to_cust_trx_line_id = NVL(cp_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
ORDER BY link_to_cust_trx_line_id,
customer_trx_line_id;
SELECT 1
FROM user_procedures
WHERE object_name = cp_object_name
AND procedure_name = cp_procedure_name ;
( SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = p_customer_trx_id
AND account_class IN ('TAX','FREIGHT')
AND customer_trx_line_id IN
(SELECT customer_trx_line_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = p_customer_trx_id
AND link_to_cust_trx_line_id = NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
AND line_type in ('TAX','FREIGHT')
)
)
LOOP
lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
p_event_mode =>''DELETE'',
p_table_name =>''RA_CUST_TRX_LINE_GL_DIST'',
p_mode =>''SINGLE'',
p_key_value => :1
);
DELETE ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = p_customer_trx_id
AND account_class IN ('TAX','FREIGHT')
AND customer_trx_line_id IN
(SELECT customer_trx_line_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = p_customer_trx_id
AND link_to_cust_trx_line_id = NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
AND line_type in ('TAX','FREIGHT')
);
DELETE ra_customer_trx_lines_all
WHERE customer_trx_id = p_customer_trx_id
AND link_to_cust_trx_line_id = NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
AND line_type IN ('TAX','FREIGHT');
END delete_trx_data;
SELECT allow_overapplication_flag
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = cp_cust_trx_type_id;
SELECT payment_schedule_id,
term_id,
terms_sequence_number,
amount_line_items_original,
amount_line_items_remaining,
tax_original,
tax_remaining,
freight_original,
amount_due_remaining
FROM ar_payment_schedules_all
WHERE customer_trx_id = cp_customer_trx_id
AND payment_schedule_id = NVL(cp_payment_schedule_id, payment_schedule_id);
SELECT NVL(SUM(extended_amount),0) extended_amount,
NVL(SUM(taxable_amount),0) taxable_amount
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = cp_customer_trx_id
AND customer_trx_line_id = NVL(cp_customer_trx_line_id, customer_trx_line_id)
AND line_type = cp_line_type;
SELECT gl_posted_date
from RA_CUST_TRX_LINE_GL_DIST_ALL
where customer_trx_line_id = p_customer_trx_line_id
and account_class = 'REC'
and latest_rec_flag = 'Y';
SELECT accounting_rule_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id = cp_customer_trx_line_id;
SELECT 1
FROM user_procedures
WHERE object_name = cp_object_name
AND procedure_name = cp_procedure_name ;
SELECT NVL(SUM(amount),0) amount,
NVL(SUM(acctd_amount),0) acctd_amount,
MAX(acctd_amount) max_acctd_amount
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cp_customer_trx_id
AND account_class IN (lv_account_class_tax,lv_account_class_freight);
CURSOR cur_temp_lines_insert( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
cp_link_to_cust_trx_line_id JAI_AR_TRX_INS_LINES_T.link_to_cust_trx_line_id%TYPE DEFAULT NULL)
IS
SELECT *
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = cp_customer_trx_id
AND link_to_cust_trx_line_id = NVL(cp_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
ORDER BY link_to_cust_trx_line_id,
customer_trx_line_id;
SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cp_customer_trx_id
AND account_class = 'REC'
AND latest_rec_flag = 'Y';
SELECT NVL(minimum_accountable_unit,NVL(precision,2))
FROM fnd_currencies
WHERE currency_code IN
(
SELECT Currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = cp_set_of_books_id
);
SELECT interface_line_attribute6,
interface_line_attribute3,
interface_line_context,
NVL(extended_amount,0) extended_amount,
NVL(taxable_amount,0) taxable_amount
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = cp_customer_trx_id
AND customer_trx_line_id = cp_customer_trx_line_id
AND line_type = lv_line_type_line; --'LINE'
SELECT MIN(payment_schedule_id) payment_schedule_id,
MIN(term_id) term_id
FROM ar_payment_schedules_all
WHERE customer_trx_id = cp_customer_trx_id;
SELECT org_id,
NVL(exchange_rate,1) exchange_rate,
trx_number,
cust_trx_type_id,
created_from,
set_of_books_id,
previous_customer_trx_id
FROM ra_customer_trx_all
WHERE customer_trx_id = cp_customer_trx_id;
SELECT *
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = cp_customer_trx_id
FOR UPDATE NOWAIT;
SELECT first_installment_code,
DECODE(base_amount, 0, 1, base_amount) base_amount
FROM ra_terms
WHERE term_id = cp_term_id;
SELECT relative_amount
FROM ra_terms_lines
WHERE term_id = cp_term_id
AND sequence_num = cp_sequence_num;
SELECT NVL(SUM(amount),0) amount
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id IN
(
SELECT customer_trx_id
FROM ar_receivable_applications_all
WHERE applied_customer_trx_id = cp_applied_customer_trx_id
AND application_type = 'CM'
AND display = 'Y'
AND status = 'APP'
)
AND account_class = cp_account_class;
SELECT NVL(sum(tax_applied),0) tax_applied,
NVL(sum(freight_applied),0) freight_applied
FROM ar_receivable_applications_all
WHERE applied_customer_trx_id = cp_applied_customer_trx_id
AND application_type = 'CASH'
AND display = 'Y'
AND status = 'APP';
SELECT NVL(sum(line_applied),0) line_applied
FROM ar_receivable_applications_all
WHERE applied_customer_Trx_id = cp_applied_customer_Trx_id
AND application_type = 'CM'
AND display = 'Y'
and status = 'APP'
AND applied_payment_Schedule_id = cp_applied_payment_Schedule_id;
SELECT receivable_application_id
FROM ar_receivable_applications_all
WHERE applied_customer_Trx_id = cp_applied_customer_Trx_id
AND customer_trx_id = cp_customer_trx_id
AND application_type = 'CM'
AND display = 'Y'
and status = 'APP'
AND applied_payment_Schedule_id = cp_applied_payment_Schedule_id;
SELECT amount_line_items_original,
amount_line_items_remaining,
tax_original,
tax_remaining,
freight_original,
amount_due_remaining
FROM ar_payment_schedules_all
WHERE customer_trx_id = cp_customer_trx_id
AND payment_schedule_id = NVL(cp_payment_schedule_id, payment_schedule_id);
SELECT ORG_ID, CREATED_FROM
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
SELECT event_id
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cp_customer_trx_id
AND account_class = 'REC'
AND latest_rec_flag = 'Y'
AND account_set_flag = 'N' ;
SELECT excise_invoice_no
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = cp_customer_trx_id
AND excise_invoice_no is NOT NULL;
fnd_file.put_line(FND_FILE.LOG, 'Before delete_trx_data');
delete_trx_data(p_customer_trx_id => p_customer_trx_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
fnd_file.put_line(FND_FILE.LOG, 'After delete_trx_data');
fnd_file.put_line(FND_FILE.LOG, 'Before cur_temp_lines_insert LOOP');
FOR rec_temp IN cur_temp_lines_insert(p_customer_trx_id)
LOOP
--get the accounting_rule_id from ra_customer_trx_lines_all for customer_trx_line_id
OPEN accounting_set_cur(rec_temp.link_to_cust_trx_line_id);
IF rec_temp.insert_update_flag IN('U','X') THEN
IF lv_amount_includes_tax_flag = 'Y' then
ln_taxable_amount := rec_customer_trx_lines.extended_amount - rec_temp.extended_amount;
fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - before insert_trx_lines');
insert_trx_lines(p_extended_amount => rec_temp.extended_amount,
p_taxable_amount => ln_taxable_amount,
p_customer_trx_line_id => rec_temp.customer_trx_line_id,
p_last_update_date => rec_temp.last_update_date,
p_last_updated_by => rec_temp.last_updated_by,
p_creation_date => rec_temp.creation_date,
p_created_by => rec_temp.created_by,
p_last_update_login => rec_temp.last_update_login,
p_customer_trx_id => rec_temp.customer_trx_id,
p_line_number => rec_temp.line_number,
p_set_of_books_id => rec_temp.set_of_books_id,
p_link_to_cust_trx_line_id => rec_temp.link_to_cust_trx_line_id,
p_line_type => rec_temp.line_type,
p_org_id => rec_customer_trx.org_id,
p_uom_code => rec_temp.uom_code,
p_autotax => 'N',
p_vat_tax_id => ln_vat_tax_id,
p_interface_line_context => rec_customer_trx_lines.interface_line_context,
p_interface_line_attribute6 => rec_customer_trx_lines.interface_line_attribute6,
p_interface_line_attribute3 => rec_customer_trx_lines.interface_line_attribute3,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - after insert_trx_lines');
fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - before insert_trx_line_gl_dist');
insert_trx_line_gl_dist(p_account_class => rec_temp.line_type,
p_account_set_flag => lv_account_set_flag,
p_acctd_amount => ROUND(rec_temp.acctd_amount, ln_precision),
p_amount => rec_temp.amount,
p_code_combination_id => rec_temp.code_combination_id,
p_cust_trx_line_gl_dist_id => NULL,
p_cust_trx_line_salesrep_id => rec_temp.cust_trx_line_sales_rep_id,
p_customer_trx_id => rec_temp.customer_trx_id,
p_customer_trx_line_id => rec_temp.customer_trx_line_id,
p_gl_date => ld_gl_date,
p_last_update_date => rec_temp.last_update_date,
p_last_updated_by => rec_temp.last_updated_by,
p_creation_date => rec_temp.creation_date,
p_created_by => rec_temp.created_by,
p_last_update_login => rec_temp.last_update_login,
p_org_id => rec_customer_trx.org_id,
p_percent => 100,
p_posting_control_id => -3,
p_set_of_books_id => rec_temp.set_of_books_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message ,
p_seq_id => ln_gl_seq,
p_event_id => ln_event_id); --added for bug#7645588
fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - after insert_trx_line_gl_dist');
UPDATE ra_cust_trx_line_gl_dist_all
SET amount = ln_old_amount + ln_tax_amt,
acctd_amount = ROUND( (ln_old_acctd_amount + ln_tax_acctd_amount ), ln_precision)
WHERE customer_trx_id = p_customer_trx_id
AND account_class = lv_account_class_rec --'REC'
AND latest_rec_flag = 'Y';
p_operation_type => 'INSERT',
p_payment_audit_id => ln_payment_audit_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
fnd_file.put_line(FND_FILE.LOG, 'After INSERT call to maintain_schedules');
UPDATE ar_payment_schedules_all
SET amount_due_original = ROUND(NVL(amount_line_items_original,0) + ln_total_tax_amt_for_inv + ln_total_frt_amt_for_inv, ln_precision),
tax_original = ROUND(ln_total_tax_amt_for_inv, ln_precision),
freight_original = ROUND(ln_total_frt_amt_for_inv, ln_precision),
amount_applied = ROUND(NVL(amount_line_items_original,0) + ln_total_tax_amt_for_inv + ln_total_frt_amt_for_inv, ln_precision),
last_update_date = SYSDATE
WHERE customer_trx_id = p_customer_trx_id;
p_operation_type => 'UPDATE',
p_payment_audit_id => ln_payment_audit_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
fnd_file.put_line(FND_FILE.LOG, 'After UPDATE call to maintain_schedules');
p_operation_type => 'INSERT',
p_payment_audit_id => ln_payment_audit_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
UPDATE ar_payment_schedules_all
SET amount_due_remaining = ROUND (ln_amt_due_rem ,ln_precision) ,
tax_remaining = ROUND(tax_original - ln_tax_amt_cashrcpt + NVL(ln_inst_tax_amt_for_inv,0),ln_precision) ,
freight_remaining = ROUND(freight_original - ln_frt_amt_cashrcpt + NVL(ln_inst_frt_amt_for_inv,0),ln_precision) ,
acctd_amount_due_remaining = ROUND(ln_amt_due_rem * ln_inv_curr_conv_rate, ln_precision) ,
amount_credited = (-1) * ROUND( ( NVL(ln_line_Applied,0) - NVL(ln_inst_tax_amt_for_inv,0) - NVL(ln_inst_frt_amt_for_inv,0)),ln_precision),
last_update_date = SYSDATE
WHERE customer_trx_id = ln_previous_customer_trx_id
AND payment_schedule_id = rec_payment.payment_schedule_id;
fnd_file.put_line(FND_FILE.LOG, 'After first update fo ar_payment_schedules_all');
UPDATE ar_payment_schedules_all
SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ), --TO_DATE('31-DEC-4712','DD-MON-YYYY')) ,
actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ),-- TO_DATE('31-DEC-4712','DD-MON-YYYY')),
last_update_date = SYSDATE
WHERE customer_trx_id = ln_previous_customer_trx_id
AND payment_schedule_id = rec_payment.payment_schedule_id;
fnd_file.put_line(FND_FILE.LOG, 'After second update fo ar_payment_schedules_all');
p_operation_type => 'UPDATE',
p_payment_audit_id => ln_payment_audit_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
p_operation_type => 'INSERT',
p_rec_appl_audit_id => ln_rec_appl_audit_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
UPDATE ar_receivable_applications_all
SET amount_applied = ROUND( NVL(line_applied,0)
+ ( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor)
+ ( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor)
,ln_precision),
acctd_amount_applied_from = ROUND( ( NVL(line_applied,0)
+ ( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor)
+ ( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor)
) * ln_cm_curr_conv_rate
,ln_precision),
acctd_amount_applied_to = ROUND( ( NVL(line_applied,0)
+ ( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor)
+ ( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor)
) * ln_inv_curr_conv_rate
,ln_precision),
tax_applied = (ROUND( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor , ln_precision )),
freight_applied = (ROUND( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor , ln_precision )),
last_update_date = SYSDATE
WHERE customer_trx_id = p_customer_trx_id
AND receivable_application_id = ln_recv_appln_id;
fnd_file.put_line(FND_FILE.LOG, 'after update of ar_receivable_applications_all');
p_operation_type => 'UPDATE',
p_rec_appl_audit_id => ln_rec_appl_audit_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
p_operation_type => 'INSERT',
p_payment_audit_id => ln_payment_audit_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
UPDATE ar_payment_schedules_all
SET amount_due_original = ROUND(NVL(amount_line_items_original,0) + NVL(ln_inst_tax_amt_for_inv,0) + NVL(ln_inst_frt_amt_for_inv,0) , ln_precision),
amount_due_remaining = ROUND(NVL(amount_line_items_remaining,0) + NVL(ln_inst_tax_amt_for_inv,0) + NVL(ln_inst_frt_amt_for_inv,0), ln_precision),
tax_original = ROUND(NVL(ln_inst_tax_amt_for_inv,0), ln_precision),
tax_remaining = ROUND(NVL(ln_inst_tax_amt_for_inv,0), ln_precision),
freight_original = ROUND(NVL(ln_inst_frt_amt_for_inv,0), ln_precision),
freight_remaining = ROUND(NVL(ln_inst_frt_amt_for_inv,0), ln_precision),
acctd_amount_due_remaining = ROUND(( NVL(amount_line_items_remaining,0) + NVL(ln_inst_tax_amt_for_inv,0) + NVL(ln_inst_frt_amt_for_inv,0) ) * ln_inv_curr_conv_rate, ln_precision),
last_update_date = SYSDATE
WHERE customer_trx_id = p_customer_trx_id
AND payment_schedule_id = rec_payment.payment_schedule_id;
fnd_file.put_line(FND_FILE.LOG, 'After First update of ar_payment_schedules_all');
UPDATE ar_payment_schedules_all
SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ), --TO_DATE('31-DEC-4712','DD-MON-YYYY')) ,
actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ), --TO_DATE('31-DEC-4712','DD-MON-YYYY')),
last_update_date = SYSDATE
WHERE customer_trx_id = p_customer_trx_id
AND payment_schedule_id = rec_payment.payment_schedule_id;
fnd_file.put_line(FND_FILE.LOG, 'After Second update of ar_payment_schedules_all '||SQL%ROWCOUNT);
p_operation_type => 'UPDATE',
p_payment_audit_id => ln_payment_audit_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
UPDATE JAI_AR_TRX_INS_LINES_T
SET error_flag = 'R',
err_mesg = p_process_message
WHERE customer_trx_id = p_customer_trx_id;
SELECT count(customer_trx_line_id) FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = P_CUSTOMER_TRX_ID;
SELECT ORG_ID, CREATED_FROM
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
SELECT EXTENDED_AMOUNT,CUSTOMER_TRX_LINE_ID,CUSTOMER_TRX_ID ,SET_OF_BOOKS_ID,
LINK_TO_CUST_TRX_LINE_ID,LINE_TYPE ,UOM_CODE,VAT_TAX_ID,ACCTD_AMOUNT,AMOUNT,
CODE_COMBINATION_ID,CUST_TRX_LINE_SALES_REP_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,INSERT_UPDATE_FLAG
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = P_CUSTOMER_TRX_ID and
link_to_cust_trx_line_id = p_link_line_id
order by CUSTOMER_TRX_LINE_ID;
SELECT gl_posted_date
from RA_CUST_TRX_LINE_GL_DIST_ALL
where customer_trx_line_id = p_customer_trx_line_id
and account_class = 'REC'
and latest_rec_flag = 'Y';
SELECT DISTINCT gl_date
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_LINE_ID IN (SELECT LINK_TO_CUST_TRX_LINE_ID FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = P_CUSTOMER_TRX_ID);
SELECT NVL(MAX(line_number),0)
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE link_to_cust_trx_line_id = p_cust_link_line_id
and line_type = p_line_type;
SELECT LINK_TO_CUST_TRX_LINE_ID,ERROR_FLAG --added the error_flag condition to process the records,which got stuck up
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = P_CUSTOMER_TRX_ID AND LINK_TO_CUST_TRX_LINE_ID = p_link_line_id;
SELECT A.AMOUNT , A.ACCTD_AMOUNT
FROM RA_CUST_TRX_LINE_GL_DIST_ALL A, RA_CUSTOMER_TRX_LINES_ALL B, JAI_AR_TRX_INS_LINES_T C
WHERE A.CUSTOMER_TRX_LINE_ID = B.CUSTOMER_TRX_LINE_ID
AND B.LINK_TO_CUST_TRX_LINE_ID = C.LINK_TO_CUST_TRX_LINE_ID
AND C.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND A.ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class)
AND A.CUSTOMER_TRX_LINE_ID = C.CUSTOMER_TRX_LINE_ID;
Select min(payment_schedule_id)
From Ar_Payment_Schedules_All
Where Customer_trx_ID = p_customer_trx_id;
Select accounting_rule_id
From Ra_Customer_Trx_Lines_All
Where Customer_Trx_Line_Id = p_link_line_id;
Select previous_customer_trx_id
from ra_customer_trx_lines_all
where customer_trx_id = P_CUSTOMER_TRX_ID
and line_type = p_line_type;
Select payment_schedule_id
from ar_payment_schedules_all
where customer_trx_id = p_prev_customer_trx_id;
Select interface_line_attribute6
From ra_customer_trx_lines_all
Where customer_trx_id = p_customer_trx_id
and line_type = p_line_type;
Select context,reference_line_id
From oe_order_lines_all
Where line_id = p_line_id;
SELECT CREATED_FROM
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
SELECT 1 from JAI_OM_OE_RMA_LINES
WHERE TO_CHAR(RMA_NUMBER) IN (SELECT INTERFACE_HEADER_ATTRIBUTE1 FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID)
AND Rma_line_id in ( Select RMA_LINE_ID from JAI_OM_OE_RMA_TAXES a,JAI_CMN_TAXES_ALL b
Where a.tax_id = b.tax_id
AND b.tax_type = jai_constants.tax_type_freight );
CURSOR tax_type IS SELECT b.tax_type t_type,a.customer_trx_line_id line_id
FROM JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B
WHERE link_to_cust_trx_line_id = p_link_line_id
and A.tax_id = B.tax_id;
SELECT reason_code FROM
RA_CUSTOMER_TRX_ALL WHERE
CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
CURSOR get_trx_num IS SELECT --21-Mar-2002 for ar tax and freight
trx_number FROM
ra_customer_trx_all WHERE
customer_trx_id = p_customer_trx_id;
is Select extended_amount
from Ra_customer_trx_lines_all
where customer_trx_id = P_CUSTOMER_TRX_ID
and customer_trx_line_id = P_LINK_LINE_ID
and line_type = p_line_type;--rchandan for bug#4428980
Cursor get_ext_amt_tax is Select extended_amount,customer_trx_line_id
from Ra_customer_trx_lines_all
where customer_trx_id = P_CUSTOMER_TRX_ID
and Link_to_cust_trx_line_id = P_LINK_LINE_ID
and line_type = lv_tax_const;--rchandan for bug#4428980
Cursor get_taxable_amt(cust_trx_ln_id number) Is Select nvl(taxable_amount,0)
from ra_customer_trx_lines_all
where customer_trx_line_id = cust_trx_ln_id
and customer_trx_id = P_CUSTOMER_TRX_ID
and line_type = lv_tax_const;--rchandan for bug#4428980
select cust_trx_line_gl_dist_id
from ra_cust_trx_line_gl_dist_all
where customer_trx_id = p_customer_trx_id
and account_class = lv_acc_class_rec --rchandan for bug#4428980
and latest_rec_flag = 'Y';
is failed.That is, the tax lines will be inserted all or none in the
tables ra_customer_trx_lines_all ,ra_cust_trx_line_gl_dist_all.
Also,whenever any record got errored out,then the corresponding invoice
taxes will not be processed to the base table itself and that record will
be updated to 'R'(column error_flag).
Code modified to get the freight lines for the RMA Transactions.
4 2002/04/04 Code merged for the issue of the receipt not getting saved when applied to an
invoice having the discounts attached.
5 2002/04/09 For the BUG:2303830
Added the condition IF v_rec_ctr > 0 to update only if the record is
found in gl_dist table.
6 2002/04/22 RPK
BUG#2247013
Code modified to populate the freight lines for the Credit memo generated
against a RMA transaction and the update the customer balances for the
original invoice against which,this credit memo is generated.
7 2002/04/26 Sriram
For Bug #2316589 for handling duplicate customer trx ids that are
processed from manual invoice that might be stuck in the temp_lines
insert table
8 2002/05/30 RPK
BUG#2247013
Bug re-opened to prevent the duplication of the taxes in the credit memo
for the RMA transaction and also corresponding updation of the balances
of the original invoice.
10. 2003/02/17 Sriram - Bug # 2784431 - The select statement that identifies whether
tax records already exist is not written correctly , it has been
corrected.
11. 2003/12/26 Sriram - bug# 3340594 File Version 618.1
'Localization' tax if is end dates or is not present , it should show a meaningful
error message instead of a cannot insert null into type of error.This has been
acheived by adding an exception 'Localization_tax_not_defined' , raising the exception
and handling the exception with the appropriate error message.
12. 2003/26/12 Sriram - bug# 3326394 File Version 618.2
incorporating code changes for multiple reporting currencies.
api calls to ar_mrc_maintain procedure have been made at appropriate places to
delete data from ra_mc_trx_line_gl_dist table in case of re-processing records.
api calls to ar_mrc_maintain package insert records in the RA_MC_TRX_LINE_GL_DIST table
have been added to insert tax and freight records
api call to update the REC row for the gl dist also has been written .
no calls made for ar_payment_schedules and ar_receivable_applications because
it is taken care when invoice is completed by base apps itself.
--------------------------------------------------------------------------------------------*/
v_sql_num := 0;
DELETE JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = P_CUSTOMER_TRX_ID
and link_to_cust_trx_line_id = P_LINK_LINE_ID
and customer_trx_line_id = tax_type_rec.line_id
and tax_type_rec.t_type <> jai_constants.tax_type_freight;
fnd_file.put_line(FND_FILE.LOG, 'Deletion in the RMA Blk...No. of rows deleted '|| v_sql_count);
SELECT NVL(SUM(AMOUNT),0) INTO V_sum_amt FROM RA_CUST_TRX_LINE_GL_DIST_ALL WHERE
ACCOUNT_CLASS = lv_acc_class_rev AND CUSTOMER_TRX_ID=P_CUSTOMER_TRX_ID; --added on 22-Mar-2002 to get the revenue amount for the invoice
SELECT COUNT(Customer_trx_line_id) INTO v_rec_ctr FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_line_id = P_LINK_LINE_ID
AND Account_class IN ('TAX','FREIGHT'); --Added on 09-Apr-2002 For the BUG#2303830
SELECT COUNT(Customer_trx_line_id) INTO v_rec_ctr
FROM ra_customer_trx_lines_all
where link_to_cust_trx_line_id = p_link_line_id
and line_type in (lv_tax_const,lv_freight_acc_class); --rchandan for bug#4428980
DELETE RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_LINE_ID IN (SELECT CUSTOMER_TRX_LINE_ID
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE LINK_TO_CUST_TRX_LINE_ID = LINK_REC.LINK_TO_CUST_TRX_LINE_ID)
AND ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class); --rchandan for bug#4428980
SELECT CUST_TRX_LINE_GL_DIST_ID
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class) --rchandan for bug#4428980
AND CUSTOMER_TRX_LINE_ID
IN
(SELECT CUSTOMER_TRX_LINE_ID
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID
AND LINE_TYPE IN (lv_tax_const,lv_freight_acc_class) --rchandan for bug#4428980
)
)
Loop
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'DELETE',
p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
p_mode => 'SINGLE',
p_key_value => mrc_rec.CUST_TRX_LINE_GL_DIST_ID
);
DELETE RA_MC_TRX_LINE_GL_DIST
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND CUST_TRX_LINE_GL_DIST_ID IN
(
SELECT CUST_TRX_LINE_GL_DIST_ID
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND ACCOUNT_CLASS IN ('TAX','FREIGHT')
AND CUSTOMER_TRX_LINE_ID
IN
(SELECT CUSTOMER_TRX_LINE_ID
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID
AND LINE_TYPE IN ('TAX','FREIGHT')
)
)
AND ACCOUNT_CLASS IN ('TAX','FREIGHT');
DELETE RA_CUSTOMER_TRX_LINES_ALL
WHERE LINK_TO_CUST_TRX_LINE_ID = LINK_REC.LINK_TO_CUST_TRX_LINE_ID;
Update Ar_Payment_Schedules_All
Set Tax_Original = 0,
Tax_Remaining = 0,
Freight_Original = 0,
Freight_Remaining = 0,
Amount_Due_Original = v_sum_amt,
Amount_Due_Remaining = v_sum_amt,
Acctd_amount_due_remaining = v_sum_amt
Where Customer_Trx_ID = p_customer_trx_id
And Payment_Schedule_ID = v_payment_schedule_id;
IF TEMP_REC.INSERT_UPDATE_FLAG IN('U','X') THEN
v_sql_num :=23;
INSERT INTO RA_CUSTOMER_TRX_LINES_ALL ( extended_amount,
customer_trx_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
line_number,
set_of_books_id,
link_to_cust_trx_line_id,
line_type,
org_id,
uom_code,
autotax,
vat_tax_id)
VALUES ( TEMP_REC.extended_amount,
TEMP_REC.customer_trx_line_id,
TEMP_REC.last_update_date,
TEMP_REC.last_updated_by,
TEMP_REC.creation_date,
TEMP_REC.created_by,
TEMP_REC.last_update_login,
TEMP_REC.customer_trx_id,
v_line_no,
TEMP_REC.set_of_books_id,
TEMP_REC.link_to_cust_trx_line_id,
TEMP_REC.line_type,
v_org_id,
TEMP_REC.uom_code,
'N',
v_vat_tax_id);
INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL(account_class,
account_set_flag,
acctd_amount,
amount,
code_combination_id,
cust_trx_line_gl_dist_id,
cust_trx_line_salesrep_id,
customer_trx_id,
customer_trx_line_id,
gl_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
percent,
posting_control_id,
set_of_books_id )
VALUES( TEMP_REC.line_type,
v_account_set_flag,
TEMP_REC.acctd_amount,
TEMP_REC.amount,
TEMP_REC.CODE_COMBINATION_ID,
RA_CUST_TRX_LINE_GL_DIST_S.nextval,
TEMP_REC.cust_trx_line_sales_rep_id,
TEMP_REC.customer_trx_id,
TEMP_REC.customer_trx_line_id,
v_gl_date,
TEMP_REC.last_update_date,
TEMP_REC.last_updated_by,
TEMP_REC.creation_date,
TEMP_REC.created_by,
TEMP_REC.last_update_login,
v_org_id,
100,
-3,
TEMP_REC.set_of_books_id ) RETURNING cust_trx_line_gl_dist_id INTO v_gl_dist_id;
p_event_mode => 'INSERT',
p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
p_mode => 'SINGLE',
p_key_value => v_gl_dist_id);
UPDATE RA_CUSTOMER_TRX_LINES_ALL
SET EXTENDED_AMOUNT = TEMP_REC.EXTENDED_AMOUNT,
LAST_UPDATE_DATE = TEMP_REC.LAST_UPDATE_DATE,
LAST_UPDATED_BY = TEMP_REC.LAST_UPDATED_BY,
CREATION_DATE = TEMP_REC.CREATION_DATE,
CREATED_BY = TEMP_REC.CREATED_BY,
LAST_UPDATE_LOGIN = TEMP_REC.LAST_UPDATE_LOGIN
WHERE CUSTOMER_TRX_LINE_ID = TEMP_REC.CUSTOMER_TRX_LINE_ID;
UPDATE RA_CUST_TRX_LINE_GL_DIST_ALL
SET ACCTD_AMOUNT = TEMP_REC.ACCTD_AMOUNT,
AMOUNT = TEMP_REC.EXTENDED_AMOUNT,
LAST_UPDATE_DATE = TEMP_REC.LAST_UPDATE_DATE,
LAST_UPDATED_BY = TEMP_REC.LAST_UPDATED_BY,
CREATION_DATE = TEMP_REC.CREATION_DATE,
CREATED_BY = TEMP_REC.CREATED_BY,
LAST_UPDATE_LOGIN = TEMP_REC.LAST_UPDATE_LOGIN
WHERE CUSTOMER_TRX_LINE_ID = TEMP_REC.CUSTOMER_TRX_LINE_ID;
Select SUM(amount),SUM(acctd_amount) into v_old_amount,v_old_acctd_amount --Added this stmt for the above stmt
From RA_CUST_TRX_LINE_GL_DIST_ALL
Where customer_trx_id = P_CUSTOMER_TRX_ID
AND ACCOUNT_CLASS = lv_acc_class_rev; --rchandan for bug#4428980
Select SUM(amount) INTO v_tax_amt
From RA_CUST_TRX_LINE_GL_DIST_ALL
Where customer_trx_id = P_CUSTOMER_TRX_ID
AND ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class); --rchandan for bug#4428980
UPDATE RA_CUST_TRX_LINE_GL_DIST_ALL
SET AMOUNT = NVL(v_old_amount,0) + NVL(v_tax_amt,0),
ACCTD_AMOUNT = NVL(v_old_acctd_amount,0) + NVL(v_tax_amt,0)
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID AND
ACCOUNT_CLASS = lv_acc_class_rec; --rchandan for bug#4428980
p_event_mode => 'UPDATE',
p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
p_mode => 'SINGLE',
p_key_value => v_gl_dist_id);
Update Ar_Payment_Schedules_All
Set Tax_Original = NVL(Tax_Original,0) + NVL(v_tax_amount,0),
Tax_Remaining = NVL(Tax_Remaining,0) + NVL(v_tax_amount,0),
Freight_Original = NVL(Freight_Original,0) + NVL(v_freight_amount,0),
Freight_Remaining = NVL(Freight_Remaining,0) + NVL(v_freight_amount,0),
Amount_Due_Original = NVL(Amount_Due_Original,0) + NVL(v_receivable_amount,0),
Amount_Due_Remaining = NVL(Amount_Due_Remaining,0) + NVL(v_receivable_amount,0),
Acctd_amount_due_remaining = NVL(Acctd_amount_due_remaining,0) + NVL(v_receivable_acctd_amount,0)
Where Customer_Trx_ID = p_customer_trx_id
And Payment_Schedule_ID = v_payment_schedule_id;
DELETE JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = P_CUSTOMER_TRX_ID and
link_to_cust_trx_line_id = P_LINK_LINE_ID;
UPDATE JAI_AR_TRX_INS_LINES_T SET ERROR_FLAG = 'R',ERR_MESG = ERRBUF WHERE
CUSTOMER_TRX_ID=P_CUSTOMER_TRX_ID
AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID;
fnd_file.put_line(FND_FILE.LOG, 'Updated the customer_trx_id error_flag to ...' || 'R');
Update Ar_Payment_Schedules_All
Set Tax_Original = NVL(Tax_Original,0) + NVL(v_tax_amount,0),
Freight_Original = NVL(Freight_Original,0) + NVL(v_freight_amount,0),
Amount_Due_Original = NVL(Amount_Due_Original,0) + NVL(v_receivable_amount,0)
Where Customer_Trx_ID = p_customer_trx_id
And Payment_Schedule_ID = v_payment_schedule_id;
Select sum(amount) INTO v_tax_amount1
FROM ra_cust_trx_line_gl_dist_all
Where customer_trx_id = p_customer_trx_id
And Account_class = lv_tax_const; --rchandan for bug#4428980
Select sum(amount) INTO v_freight_amount1
FROM ra_cust_trx_line_gl_dist_all
Where customer_trx_id = p_customer_trx_id
And Account_class = lv_freight_acc_class; --rchandan for bug#4428980
SELECT SUM(AMOUNT) INTO v_tot_amount
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = p_customer_trx_id
AND account_class = lv_acc_class_rec; --rchandan for bug#4428980
Update Ar_Payment_Schedules_All
Set Tax_Original = NVL(v_tax_amount1,0),
Freight_Original = NVL(v_freight_amount1,0),
Amount_Due_Original = NVL(Amount_line_items_Original,0) + NVL(v_tax_amount1,0) + NVL(v_freight_amount1,0),
Amount_Applied = NVL(Amount_line_items_Original,0) + NVL(v_tax_amount1,0) + NVL(v_freight_amount1,0)
Where Customer_Trx_ID = p_customer_trx_id
And Payment_Schedule_ID = v_payment_schedule_id;
Update Ar_Receivable_Applications_All
Set Amount_Applied = NVL(Amount_Applied,0) - (NVL(v_freight_amount1,0)),
--Tax_Applied = NVL(Tax_Applied,0) - NVL(v_tax_amount1,0),
Freight_Applied = NVL(Freight_Applied,0) - NVL(v_freight_amount1,0),
Acctd_Amount_Applied_From = NVL(Acctd_Amount_Applied_From,0) - ( NVL(v_freight_amount1,0) ),
Acctd_Amount_Applied_To = NVL(Acctd_Amount_Applied_To,0) - ( NVL(v_freight_amount1,0) )
Where Customer_Trx_ID = p_customer_trx_id
And Payment_Schedule_ID = v_payment_schedule_id; --20-Apr-2002
Update Ar_Payment_Schedules_All
Set --Tax_Remaining = NVL(Tax_remaining,0) - NVL(v_tax_amount1,0),
Freight_Remaining = NVL(Freight_Remaining,0) + NVL(v_freight_amount1,0),
Amount_Due_Remaining = NVL(Amount_Due_Remaining,0) + NVL(v_freight_amount1,0),
Amount_Credited = NVL(Amount_Credited,0) + NVL(v_freight_amount1,0),
Acctd_Amount_Due_Remaining = NVL(Acctd_Amount_Due_Remaining,0) + NVL(v_freight_amount1,0)
Where Customer_Trx_Id = v_prev_customer_trx_id
And Payment_Schedule_Id = v_payment_Schedule_id; --18-apr-2002
UPDATE ra_customer_trx_lines_all
SET Taxable_amount = (v_extended_amount_line - get_ext_amt_tax_rec.extended_amount)
WHERE Customer_trx_line_id = get_ext_amt_tax_rec.customer_trx_line_id
and customer_trx_id = P_CUSTOMER_TRX_ID
and link_to_cust_trx_line_id = P_LINK_LINE_ID
and Line_type = lv_tax_const;
UPDATE JAI_AR_TRX_INS_LINES_T SET ERROR_FLAG = 'R',ERR_MESG=ERRBUF WHERE CUSTOMER_TRX_ID=P_CUSTOMER_TRX_ID
AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID;
SELECT
tax_account_id
INTO
ln_tax_def_acc_id
FROM
jai_cmn_taxes_all
WHERE tax_id = pn_tax_id
AND org_id = pn_org_id;
SELECT
TO_NUMBER(acc_rgm.attribute_value)
INTO
ln_tax_rgm_acc_id
FROM
jai_rgm_definitions rgm_def
, jai_rgm_registrations tax_rgm
, jai_rgm_registrations acc_rgm
WHERE regime_code IN (jai_constants.service_regime,jai_constants.vat_regime)
AND tax_rgm.regime_id = rgm_def.regime_id
AND tax_rgm.registration_type = jai_constants.regn_type_tax_types
AND tax_rgm.attribute_code = pv_tax_type
AND tax_rgm.regime_id = acc_rgm.regime_id
AND acc_rgm.registration_type = jai_constants.regn_type_accounts
AND acc_rgm.attribute_code = jai_constants.recovery_interim
AND acc_rgm.parent_registration_id = tax_rgm.registration_id;
SELECT
a.tax_id tax_id
, b.tax_type tax_type
, SUM(a.tax_amount) tax_amount
FROM
jai_cmn_taxes_all b
, jai_ar_trx_tax_lines a
WHERE a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
AND a.link_to_cust_trx_line_id IN (SELECT
customer_trx_line_id
FROM
jai_ar_trx_lines
WHERE customer_trx_id = pn_customer_trx_id)
GROUP BY
a.tax_id
, b.tax_type;
SELECT
jat.trx_number
, jat.exchange_date
, jat.exchange_rate_type
, jat.exchange_rate
, jat.invoice_currency_code
, jat.organization_id
, mp.organization_code
INTO
lv_inv_num
, ld_cur_conversion_date
, lv_cur_conversion_type
, ln_cur_conversion_rate
, lv_currency_code
, ln_inv_org_id
, lv_inv_org_code
FROM
jai_ar_trxs jat
, mtl_parameters mp
WHERE jat.customer_trx_id = pn_customer_trx_id
AND jat.organization_id = mp.organization_id;
SELECT
gl_id_rec
INTO
ln_rec_account_id
FROM
ra_cust_trx_types_all
WHERE org_id = ln_org_id
AND cust_trx_type_id = ln_cust_trx_type_id;
SELECT
set_of_books_id
, gl_date
INTO
ln_set_of_books_id
, ld_gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = pn_customer_trx_id
AND rownum = 1;
INSERT INTO gl_interface
( status
, set_of_books_id
, user_je_source_name
, user_je_category_name
, accounting_date
, currency_code
, date_created
, created_by
, actual_flag
, entered_cr
, entered_dr
, transaction_date
, code_combination_id
, currency_conversion_date
, user_currency_conversion_type
, currency_conversion_rate
, reference1
, reference10
, reference22
, reference23
, reference24
, reference25
, reference26
, reference27
)
VALUES
( 'NEW'
, ln_set_of_books_id -- the set of books id
, 'Receivables India' -- je source name 'Receivables India'
, 'Register India' -- je category name 'Register India'
, ld_gl_date -- accounting date (GL date of the invoice)
, lv_currency_code -- currency code
, sysdate -- standard who column
, TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
, 'A' -- actual flag, hard coded value
, inclu_tax_csr.tax_amount -- credit amt, inclusive tax amount
, null -- debit amt
, sysdate -- invoice date
, ln_tax_account_id -- code combination
, ld_cur_conversion_date
, lv_cur_conversion_type
, ln_cur_conversion_rate
, lv_inv_org_code -- inventory organization code
, 'India Localization Entry for accounting inclusive taxes for invoice'||lv_inv_num
, 'India Localization Entry' -- hard code string
, 'acct_inclu_taxes' -- procedure name that makes the insert into gl_interface hard code string
, 'RA_CUSTOMER_TRX_ALL' -- hard code string
, 'CUSTOMER_TRX_ID' -- hard code string
, pn_customer_trx_id -- value of customer_trx_id
, ln_inv_org_id -- organization id of the inventory organization id
);
, 'Insert tax info: '
|| 'tax_account_id = ' || ln_tax_account_id
|| ' tax_amount = '|| inclu_tax_csr.tax_amount
);
INSERT INTO gl_interface
( status
, set_of_books_id
, user_je_source_name
, user_je_category_name
, accounting_date
, currency_code
, date_created
, created_by
, actual_flag
, entered_cr
, entered_dr
, transaction_date
, code_combination_id
, currency_conversion_date
, user_currency_conversion_type
, currency_conversion_rate
, reference1
, reference10
, reference22
, reference23
, reference24
, reference25
, reference26
, reference27
)
VALUES
( 'NEW'
, ln_set_of_books_id -- the set of books id
, 'Receivables India' -- je source name 'Receivables India'
, 'Register India' -- je category name 'Register India'
, ld_gl_date -- accounting date (GL date of the invoice)
, lv_currency_code -- currency code
, sysdate -- standard who column
, TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
, 'A' -- actual flag, hard coded value
, null -- credit amt, inclusive tax amount
, ln_total_inclu_tax_amt -- debit amt
, sysdate -- invoice date
, ln_rec_account_id -- code combination
, ld_cur_conversion_date
, lv_cur_conversion_type
, ln_cur_conversion_rate
, lv_inv_org_code -- inventory organization code
, 'India Localization Entry for accounting inclusive taxes for invoice'||lv_inv_num
, 'India Localization Entry' -- hard code string
, 'acct_inclu_taxes' -- procedure name that makes the insert into gl_interface hard code string
, 'RA_CUSTOMER_TRX_ALL' -- hard code string
, 'CUSTOMER_TRX_ID' -- hard code string
, pn_customer_trx_id -- value of customer_trx_id
, ln_inv_org_id -- organization id of the inventory organization id
);
, 'Insert debit info: '
|| 'account_id = ' || ln_rec_account_id
|| ' amount = '|| ln_total_inclu_tax_amt
);