[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;
SELECT rctl.interface_line_attribute3, rctl.interface_line_attribute6
FROM ra_customer_trx_all trx,
ra_customer_trx_lines_all rctl,
jai_ar_trx_lines jrctl,
JAI_AR_TRXS jrct
WHERE trx.customer_trx_id = rctl.customer_trx_id
AND jrct.customer_trx_id = trx.customer_trx_id
AND rctl.line_type = 'LINE'
AND trx.customer_trx_id =
nvl(pn_customer_trx_id, trx.customer_trx_id)
AND trx.org_id = nvl(pn_org_id, trx.org_id)
AND trx.created_from = 'RAXTRX'
AND rctl.customer_trx_line_id = jrctl.customer_trx_line_id;
SELECT
excise_invoice_no
, vat_invoice_no
FROM
JAI_OM_WSH_LINES_ALL
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
-- WHERE delivery_id = p_delivery_id
WHERE (--delivery_id IS NULL OR /*commented condition by mmurtuza for bug 16040831*/
delivery_id = p_delivery_id)
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
AND ( order_line_id = p_order_line_id
OR order_line_id IN
( SELECT
line_id
FROM
oe_order_lines_all
WHERE header_id IN
( SELECT
header_id
FROM
oe_order_lines_all
WHERE line_id = p_order_line_id
)
AND item_type_code = 'CONFIG'
)
)
AND (excise_invoice_no IS NOT NULL OR vat_invoice_no IS NOT NULL);
SELECT
excise_invoice_no
, vat_invoice_no
FROM
JAI_OM_WSH_LINES_ALL
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
-- WHERE delivery_id = p_delivery_id
WHERE (delivery_id IS NULL)
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
AND ( order_line_id = p_order_line_id
OR order_line_id IN
( SELECT
line_id
FROM
oe_order_lines_all
WHERE header_id IN
( SELECT
header_id
FROM
oe_order_lines_all
WHERE line_id = p_order_line_id
)
AND item_type_code = 'CONFIG'
)
)
AND (excise_invoice_no IS NOT NULL OR vat_invoice_no IS NOT NULL);
SELECT
st_inv_number
FROM JAI_AR_TRXS
WHERE customer_trx_id = pn_customer_trx_id;
SELECT
regime_id ,
regime_code
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
jctl.customer_trx_id = pn_customer_trx_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.tax_type = jrttv.tax_type AND
regime_code = jai_constants.service_regime AND
jtc.org_id = pn_org_id ;
SELECT
jat.organization_id,
jat.LOCATION_ID,
jat.BATCH_SOURCE_ID
FROM jai_ar_trxs jat
where jat.CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT
rcta.SHIP_TO_CUSTOMER_ID,
rcta.SHIP_TO_SITE_USE_ID,
rcta.bill_to_customer_id,
rcta.bill_to_site_use_id,
rcta.TRX_DATE
FROM ra_customer_trx_all rcta
WHERE rcta.CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT l.excise_invoice_no
FROM JAI_AR_TRX_LINES L
WHERE l.customer_trx_id = pn_customer_trx_id;
SELECT h.vat_invoice_no
FROM JAI_AR_TRXS h
WHERE h.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 --|ove 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. --|oved 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.
20. 27-Feb-2009 CSahoo for bug#8276902, File Version 120.19.12010000.4
Issue: UNABLE TO ACCOUNT CREDIT MEMOS IN AR JAN-09
Fix: Added the following OR condition in the procedure process_from_order_line
"OR rec_customer_trx_lines.interface_line_context = gv_projects_invoices"
21. 02-Nov-2009 CSahoo for bug#8325824, File Version 120.19.12010000.11
Issue: REW:ROOTCAUSE: UNABLE TO POST AR TRANSACTIONS UPTO GL.
Fix: added the code to populate the AR distribution table. added the procedure
insert_ar_dist_entries.
21 09-dec-2009 vkaranam for bug#9177024,file version 120.19.12010000.12
Issue:Taxes doesn't become zero in Base AR Transaction screen even after deleting
the taxes in Localized AR Screen
Fix:
Added the call to delete_trx_data in process_manual_invoice procedure for
manual transactions.
22 23-MAR-2009 vkaranam for bug#9230409
Issue:
AR INVOICE IS SHOWING WRONG BALANCE WHENEVER A RMA CREDIT MEMO IS APPLIED.
Reason:
The problem is that when the credit memo
is created, it automatically unapplies the Receipt and applies the Credit Memo and
then re-applies the receipt for the remaining amount. This happens
fine for the base amount. But the same is not happening for the tax amount.
Hence the incorrect invoice balance issue.
The procedure jai_ar_match_tax_pkg.process_from_order_line
is responsible for populating the IL taxes in base AR table. Here we do not
check if a cash receipt is already applied to the invoice. The code just
directly applies the tax amount of the credit memo to the invoice. it does
not unapply the tax amount of the cash receipt applied before. Thats the
reason why this issue is coming.
fix:
changes are done in process_from_order_line procedure.
used base AR APIs to unapply the receipt and then appying the remaining.
23 19-jan-2011 vkaranam for bug#11652823
Issue: INCORRECT ACCOUNTING FOR INCLUSIVE SERVICE TAX
The interim liability account picked is at the regime registration level instead
of the organization level.
Fix:
changes are done in acct_inclu_taxes procedure.
added the code to fetch the accounts at the regime organization level.
24. 24-May-2011 Xiao for POT change, reg bug#12533434.
Fixed: Fetch gl_date from AR REV lines as accounting date. --Added by Chong.Lei for POT code port
25. 16-Feb-2012 mmurtuza for bug 13557242
Description: A/R - RMA AUTOINVOICE CRTD WITH ALLOW OVER APPLICATION, TAXES NOT INBASE TRN, HO
Fix: Added cursor c_period_dtl in procedure process_from_order_line to check period status. Added code to fetch start date of next first
open perioed if the gl_date of receipt application is closed. Passed this date while unapplyinh and reapplying the receipt.
26. 13-APr-2012 mmurtuza for bug 13958098
Description: INDIA LOCAL CONCURRENT IS ERRORING OUT WITH ERROR after application of patch 13557242
Fix: The assignment to variable lt_sob_id_tab was missing. Assgined the value to this variable
27. 26-Dec-2012 mmurtuza for bug 16040831
Description: INDIA - LOCAL CONCURRENT FOR PROCESSING ORDER LINES TO AR RUNNING MORE THAN NORM
Fix: Defined cursor c_ex_inv_no_2. Modified cursor c_ex_inv_no. cursor c_ex_inv_no is opened when p_delivery_id is not null and
cursor c_ex_inv_no_2 is opened when p_delivery_id is null
28. 17-Jan-2013 mmurtuza for bug 16181417
Description: AR CREATE ACCOUNTING PROGRAM ENDING IN ERROR IN JAI_AR_TRX_INS_LINES_T
Fix: Added condition adjustment_period_flag = 'N' for fetching next open period if receipt application period is closed.
Also added the logic done for bug 14194526 to avoid the charcter to number conversion and also to avoid performance issue
in cursors get_rma_flag_cur and inclu_rma_tax.
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 --|arks
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
--|oved the reference to fnd_profile.value('ORG_ID')
v_org_id := FND_PROFILE.VALUE('ORG_ID');
SELECT DISTINCT customer_trx_id, org_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, org_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_trxs set st_inv_number = lv_st_inv_number
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;
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 insert_ar_dist_entries (p_customer_trx_id IN NUMBER,
p_receivable_appl_id IN NUMBER,
p_debug IN VARCHAR2 DEFAULT 'N',
p_process_status OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2)
IS
BEGIN
IF p_debug = 'Y' THEN
fnd_file.put_line(FND_FILE.LOG, 'Before deleting ar_distributions : p_receivable_appl_id '|| p_receivable_appl_id);
DELETE ar_distributions
where source_id = p_receivable_appl_id
and source_table = 'RA' ;
END insert_ar_dist_entries;
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 jatilt
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)
AND EXISTS (select '1'
from JAI_AR_TRX_TAX_LINES jattl
where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
and exists (select '1'
from JAI_CMN_TAXES_ALL
where jattl.tax_id = tax_id
and NVL(inclusive_tax_flag,'N') = 'N'))
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
--Commented by Chong.Lei for POT code port begin
/*
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;
SELECT max(apply_date)
FROM ar_receivable_applications_all
WHERE cash_receipt_id = ln_cash_receipt_id
AND applied_customer_trx_id = ln_customer_trx_id;
SELECT gl_date
FROM ar_receivable_applications_all
WHERE cash_receipt_id = ln_cash_receipt_id
AND applied_customer_trx_id = ln_customer_trx_id
AND receivable_application_id = (Select max(receivable_application_id)
from ar_receivable_applications_all
where cash_receipt_id = ln_cash_receipt_id
and applied_customer_trx_id = ln_customer_trx_id
and apply_date = ld_apply_date);
SELECT period_name,
start_date,
end_date,
closing_status
FROM gl_period_statuses
WHERE application_id = jai_constants.gl_application_id
AND set_of_books_id = cp_sob_id
AND closing_status IN('O', 'F')
AND cp_accounting_date BETWEEN start_date
AND end_date
ORDER BY period_year,
period_num;
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';
FOR rec_get_receipt_id IN ( select distinct cash_receipt_id, set_of_books_id /*Added set_of_books_id by mmurtuza for bug 13557242*/
from ar_receivable_applications_all
where status = 'APP'
and application_type = 'CASH'
and applied_customer_trx_id = ln_previous_customer_trx_id
group by cash_receipt_id, set_of_books_id /*Added set_of_books_id by mmurtuza for bug 13557242*/
having sum(acctd_amount_applied_from) > 0)
LOOP
lt_receipt_id_tab(ln_row_count) := rec_get_receipt_id.cash_receipt_id;
(SELECT period_name,
start_date,
end_date,
closing_status
FROM gl_period_statuses
WHERE application_id = jai_constants.gl_application_id
AND set_of_books_id = lt_sob_id_tab(ln_row_count)
AND start_date > lt_gl_date(ln_row_count)
AND adjustment_period_flag = 'N' -- mmurtuza for bug 16181417
ORDER BY period_year,
period_num)
LOOP
IF period.closing_status IN('O', 'F') THEN
ld_accounting_date(ln_row_count) := period.start_date;
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);
fnd_file.put_line(FND_FILE.LOG, 'before insert into ar_distributions_all');
insert_ar_dist_entries( p_customer_trx_id => p_customer_trx_id,
p_receivable_appl_id => ln_recv_appln_id,
p_debug => p_debug,
p_process_status => lv_process_status,
p_process_message => lv_process_message
);
fnd_file.put_line(FND_FILE.LOG, 'after insert into ar_distributions_all');
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 jatilt
WHERE customer_trx_id = P_CUSTOMER_TRX_ID
AND EXISTS (select '1'
from JAI_AR_TRX_TAX_LINES jattl
where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
and exists (select '1'
from JAI_CMN_TAXES_ALL
where jattl.tax_id = tax_id
and NVL(inclusive_tax_flag,'N') = 'N'));
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 jatilt
WHERE customer_trx_id = P_CUSTOMER_TRX_ID and
link_to_cust_trx_line_id = p_link_line_id
AND EXISTS (select '1'
from JAI_AR_TRX_TAX_LINES jattl
where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
and exists (select '1'
from JAI_CMN_TAXES_ALL
where jattl.tax_id = tax_id
and NVL(inclusive_tax_flag,'N') = 'N'))
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 jatilt
WHERE customer_trx_id = P_CUSTOMER_TRX_ID
AND EXISTS (select '1'
from JAI_AR_TRX_TAX_LINES jattl
where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
and exists (select '1'
from JAI_CMN_TAXES_ALL
where jattl.tax_id = tax_id
and NVL(inclusive_tax_flag,'N') = 'N')));
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 jatilt
WHERE customer_trx_id = P_CUSTOMER_TRX_ID AND LINK_TO_CUST_TRX_LINE_ID = p_link_line_id
AND EXISTS (select '1'
from JAI_AR_TRX_TAX_LINES jattl
where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
and exists (select '1'
from JAI_CMN_TAXES_ALL
where jattl.tax_id = tax_id
and NVL(inclusive_tax_flag,'N') = 'N'));
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
AND EXISTS (select '1'
from JAI_AR_TRX_TAX_LINES jattl
where jattl.link_to_cust_trx_line_id = c.link_to_cust_trx_line_id
and jattl.customer_trx_line_id = c.customer_trx_line_id
and exists (select '1'
from JAI_CMN_TAXES_ALL
where jattl.tax_id = tax_id
and NVL(inclusive_tax_flag,'N') = 'N'));
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,
NVL(exchange_rate,1) exchange_rate --9177024
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;
SELECT 1
FROM jai_ar_trx_tax_lines
WHERE customer_trx_line_id = cp_customer_trx_line_id
AND link_to_cust_trx_line_id = cp_link_to_cust_trx_line_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';
SELECT NVL(SUM(extended_amount),0) extended_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 NVL(SUM(amount),0) amount,
NVL(SUM(acctd_amount),0) 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);
Select nvl(Max(Request_ID),-1)
From Fnd_Concurrent_Programs FCP,
Fnd_Application FA ,
Fnd_Concurrent_Requests FCR
Where FCR.Program_Application_ID = FA.Application_ID
AND FCR.Concurrent_Program_ID = FCP.Concurrent_Program_ID
AND FA.Application_ID = FCP.Application_ID
AND Concurrent_Program_Name = 'JAILINEGL'
AND FA.Application_Short_Name = 'JA'
AND FCR.argument1 = to_char(pn_customer_trx_id) -- added tochar 10385256
AND FCR.argument2 = to_char(pn_customer_trx_line_id);--added tochar 10385266
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.
13. 28-May-2009 JMEENA for bug#8476512
Modified the code in the procedure process_manual_invoice. Added the cursor cur_chk_jai_tax_dtls.
14 14-feb-2011 vkaranam for bug#10385256
Issue:
ERRATIC BEHAVIOUR IN INDIA AR TRANSACIONS TAX ACCOUNTING
Reason :
Issue is happening if the concurrent is running simultaneously.
then the modifications for the second concurrent will be lost and will
not be processed.
process_manual_invoice will fetch the transactions from temp table (jai_ar_trx_ins_lines_t)
and process the taxes to distributions and then flushout the temp table.
During this the second concurrent fires and will find no data in temp table and will not process
the modified data.
hence the issue.
fix:
Modified the code in process_manual_invoice
if maxreqid then
delete temp table;
update temp table
error_flag='D';
during insertion for temp table error_flag will be 'P' for auto import transactions
for manual invoice error_flag is NULL. if we dont update the error_flag,while completion
it will popup an error message saying il tax not applied...
please wait for ar tax and freight defaultation concurrent...
PROCESSED tax lines will be always flushed by the concurrent
updating to 'D' will only happen with concurrents firing simultaneously for ct_trx_id..
--------------------------------------------------------------------------------------------*/
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);
delete_trx_data(p_customer_trx_id => p_customer_trx_id,
p_link_to_cust_trx_line_id=>p_link_line_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
UPDATE ra_cust_trx_line_gl_dist_all
SET amount = ln_old_amount + ln_tax_amt,
acctd_amount = ROUND( ( ln_old_amount *ln_exchange_rate + 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';
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;
Delete JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_line_id = temp_rec.customer_trx_line_id
AND link_to_cust_trx_line_id = temp_rec.link_to_cust_trx_line_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;
/*Bug 11936630 - Delete the Inclusive Tax Lines inserted into JAI_AR_TRX_INS_LINES_T*/
DELETE JAI_AR_TRX_INS_LINES_T jatilt
WHERE customer_trx_id = P_CUSTOMER_TRX_ID and
link_to_cust_trx_line_id = p_link_line_id
AND EXISTS (select '1'
from JAI_AR_TRX_TAX_LINES jattl
where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
and exists (select '1'
from JAI_CMN_TAXES_ALL
where jattl.tax_id = tax_id
and NVL(inclusive_tax_flag,'N') = 'Y'));
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 -- --|oved by Jia for fixed bug#7205349 on 20-Apr-2009
-- AND acc_rgm.attribute_code = jai_constants.liability -- Modified by Jia for fixed bug#8474445 on 30-Apr-2009, use liability account to replace liability interim account
AND acc_rgm.attribute_code = jai_constants.liability_interim --Added by Bo Li for bug#9771955 on 2010-06-04
AND acc_rgm.parent_registration_id = tax_rgm.registration_id;
SELECT trx_date
FROM ra_customer_trx_all
WHERE customer_trx_id = pn_customer_trx_id;
SELECT gl_date
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE customer_trx_id = pn_customer_trx_id
AND account_class = 'REV'
AND ROWNUM = 1;
SELECT
a.tax_id tax_id
, b.tax_type tax_type
, SUM(a.tax_amount) tax_amount
, nvl(b.rounding_factor,0) rounding_factor --Add by Xiao for bug#11936390, for rounding issue.
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
, nvl(b.rounding_factor,0);--Added by zhiwei for bug#12433891 20110509;
select code_combination_id
from ra_cust_trx_line_gl_dist_all
where customer_trx_id = pn_customer_trx_id
and account_class = lv_acc_class_rev;
SELECT COUNT(customer_trx_line_id)
FROM (SELECT rctl.customer_trx_line_id
FROM OE_ORDER_HEADERS_ALL oh,
OE_ORDER_LINES_ALL ol,
OE_TRANSACTION_TYPES_TL ot,
oe_workflow_assignments owf,
ra_customer_trx_lines_all rctl
WHERE oh.header_id = ol.header_id
AND oh.order_type_id = ot.transaction_type_id
AND oh.order_type_id = owf.order_type_id
AND ol.line_type_id = owf.line_type_id
AND ot.LANGUAGE = userenv('LANG')
/* AND to_char(ol.line_id) = rctl.interface_line_attribute6 */ /*Commented and added below by mmurtuza for bug 14194526*/
AND ol.line_id = decode(replace(translate(rctl.interface_line_attribute6,'123456789','000000000'),'0',''),
NULL, rctl.interface_line_attribute6, NULL)
AND owf.process_name IN
('R_RMA_CREDIT_APP_HDR_INV', 'R_RMA_CREDIT_WO_SHIP_APPROVE',
'R_RMA_CREDIT_WO_SHIP_HDR_INV',
'R_RMA_FOR_CREDIT_WO_SHIPMENT', 'R_RMA_FOR_OTA_CREDIT')
AND rctl.customer_trx_id = pn_customer_trx_id
UNION
SELECT rctl.customer_trx_line_id
FROM MTL_SYSTEM_ITEMS msi,
JAI_OM_OE_RMA_LINES l,
ra_customer_trx_lines_all rctl
WHERE msi.inventory_item_id = rctl.inventory_item_id
AND msi.inventory_item_id = l.inventory_item_id
/*AND l.rma_line_id = rctl.interface_line_attribute6*/ /*Commented and added below by mmurtuza for bug 16181417*/
AND l.rma_line_id = decode(replace(translate(rctl.interface_line_attribute6,'123456789','000000000'),'0',''),
NULL, rctl.interface_line_attribute6, NULL)
AND msi.shippable_item_flag = 'N'
/*AND msi.organization_id = rctl.interface_line_attribute10*/ /*Commented and added below by mmurtuza for bug 16181417*/
AND msi.organization_id = decode(replace(translate(rctl.interface_line_attribute10,'123456789','000000000'),'0',''),
NULL, rctl.interface_line_attribute10, NULL)
AND rctl.customer_trx_id = pn_customer_trx_id);
SELECT
a.tax_id tax_id
, b.tax_type tax_type
, SUM(a.tax_amount) tax_amount
, nvl(b.rounding_factor,0) rounding_factor --Add by Xiao for bug#11936390, for rounding issue.
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 rctl.customer_trx_line_id
FROM OE_ORDER_HEADERS_ALL oh,
OE_ORDER_LINES_ALL ol,
OE_TRANSACTION_TYPES_TL ot,
oe_workflow_assignments owf,
ra_customer_trx_lines_all rctl
WHERE oh.header_id = ol.header_id
AND oh.order_type_id = ot.transaction_type_id
AND oh.order_type_id = owf.order_type_id
AND ol.line_type_id = owf.line_type_id
AND ot.LANGUAGE = userenv('LANG')
/* AND to_char(ol.line_id) = rctl.interface_line_attribute6 */ /*Commented and added below by mmurtuza for bug 14194526*/
AND ol.line_id = decode(replace(translate(rctl.interface_line_attribute6,'123456789','000000000'),'0',''),
NULL, rctl.interface_line_attribute6, NULL)
AND owf.process_name IN
('R_RMA_CREDIT_APP_HDR_INV', 'R_RMA_CREDIT_WO_SHIP_APPROVE',
'R_RMA_CREDIT_WO_SHIP_HDR_INV',
'R_RMA_FOR_CREDIT_WO_SHIPMENT', 'R_RMA_FOR_OTA_CREDIT')
AND rctl.customer_trx_id = pn_customer_trx_id
UNION
SELECT rctl.customer_trx_line_id
FROM MTL_SYSTEM_ITEMS msi,
JAI_OM_OE_RMA_LINES l,
ra_customer_trx_lines_all rctl
WHERE msi.inventory_item_id = rctl.inventory_item_id
AND msi.inventory_item_id = l.inventory_item_id
/*AND l.rma_line_id = rctl.interface_line_attribute6*/ /*Commented and added below by mmurtuza for bug 16181417*/
AND l.rma_line_id = decode(replace(translate(rctl.interface_line_attribute6,'123456789','000000000'),'0',''),
NULL, rctl.interface_line_attribute6, NULL)
AND msi.shippable_item_flag = 'N'
/*AND msi.organization_id = rctl.interface_line_attribute10*/ /*Commented and added below by mmurtuza for bug 16181417*/
AND msi.organization_id = decode(replace(translate(rctl.interface_line_attribute10,'123456789','000000000'),'0',''),
NULL, rctl.interface_line_attribute10, NULL)
AND rctl.customer_trx_id = pn_customer_trx_id)
GROUP BY
a.tax_id
, b.tax_type;
select regime_id, regime_code /*Bug 11821537 - Added Regime Code*/
from jai_regime_tax_types_v
where regime_code in (jai_constants.service_regime,jai_constants.vat_regime)
and tax_type=cp_tax_type;
SELECT to_date(attribute_value, 'DD/MM/YYYY')
FROM jai_rgm_registrations
WHERE regime_id = p_regime_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS';
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
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 tax_account_id
FROM jai_cmn_taxes_all
WHERE tax_id = p_tax_id;
select 1
from dual
where exists
( select customer_trx_line_id
from jai_ar_trx_lines
where customer_trx_id = pn_customer_trx_id
AND NVL(interface_flag,'#') = 'Y'
AND NVL(interface_event,'#')= JAI_OPEN_API_PKG.GV_TAXABLE_EVENT_EXTERNAL
);
SELECT
jat.trx_number
, jat.exchange_date
, jat.exchange_rate_type
, jat.exchange_rate
, jat.invoice_currency_code
, jat.organization_id
,jat.location_id --11652823
, 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
,ln_inv_loc_id -- 11652823
, 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_rev -- Modified by Jia for fixed bug#8474445, use gl_id_rev to replace 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;
SELECT
TO_NUMBER(acc_rgm.attribute_value)
INTO
ln_tax_account_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 = inclu_tax_csr.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
AND acc_rgm.parent_registration_id = tax_rgm.registration_id;
SELECT tax_account_id
INTO ln_tax_account_id
FROM jai_cmn_taxes_all
WHERE tax_id = inclu_tax_csr.tax_id
AND org_id = pn_org_id;
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
, ln_amount -- modified by Xiao for bug#11936390.
, 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
--, inclu_tax_csr.tax_amount -- credit amt, inclusive tax amount
, ln_amount -- modified by Xiao for bug#11936390
, 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
);
/*9892611 - Check if Inclusive Tax Amount is non zero before inserting into gl_interface
Dummy records are inserted into GL_INTERFACE with zero amounts otherwise*/
IF ln_total_inclu_tax_amt <> 0 THEN
-- Insert revenue amount into GL Interface table
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
);
PROCEDURE update_all_invoice_num
( pn_customer_trx_id IN NUMBER
, pv_excise_invoice_no IN VARCHAR2
, pv_vat_invoice_no IN VARCHAR2
, pv_st_invoice_num IN VARCHAR2
)
IS
cv_seperator CONSTANT VARCHAR2(30) := ';';
SELECT ct_reference
FROM ra_customer_trx_all
WHERE customer_trx_id = pn_customer_trx_id;
UPDATE ra_customer_trx_all
SET ct_reference = lv_reference
WHERE customer_trx_id = pn_customer_trx_id;
END update_all_invoice_num;
SELECT st_inv_number
FROM JAI_AR_TRXS
WHERE customer_trx_id = pn_customer_trx_id;
update_all_invoice_num(pn_customer_trx_id,
pv_excise_invoice_no,
pv_vat_invoice_no,
lv_st_inv_number);