[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
- INSERT_REPOSITORY_ENTRY : Based on the input source, this procedure derives the type of entry that has to be made into
the repository. Also accounting entries related to repository entry are passed only if p_accntg_required_flag parameter
is 'Y'. This also passes discount accounting if the input parameter p_discounted_amount has a value
- GET_ACCOUNT : Returns the CODE_COMBINATION_ID related to the inputs passed. This returns values from regime setup
incase of service tax and from Organization Addl. info incase of inventory organization and from Base Setup incase of
AP discounts
- GET_PERIOD_NAME : Returns the period_name for which the entry is being made. This also returns the accounting_date as
first_date of next open period, if the period corresponding to input accounting date is closed
- POST_ACCOUNTING : Inserts an Entry into GL_INTERFACE and Localization Subledger for the inputs passed to the call
- INSERT_REFERENCE : Called from AP and AR Processing to insert data related to related Invoices
- UPDATE_REFERENCE : Called from AP and AR Processing to update revocovered and discounted amounts for the invoice
2. Bug# 4193633 - Aiyer - 15-feb-2005
Issue
The tax earned and unearned discount are not getting apportioned properly of service type of taxes and hence the India - Service Tax concurrent
ends up in a warning for records with these issues
Reason:-
In case of invoices having Service taxes and other type of taxes, the tax earned and unearned discounts should be approtioned across all the type of taxes
(Both Service and Non Service).
This apportionment logic was not present initially. This needs to be added
Fix: -
Modified the procedure. Did the following :-
1. Added a extra parameter p_total_disc_amount to the procedure.
2. used this parameter to apportion the tax earned discount amount and tax unearned discount amount
Dependency :-
In this procedure the added parameter is added to the procedure and hence causes a dependency issue.
The following objects should be sent together
1. jai_rgm_process_ar_taxes_pkg_s.sql (115.1)
2. jai_rgm_process_ar_taxes_pkg_b.sql (115.1)
3. jai_rgm_trx_recording_pkg_s.sql version (115.1)
4. jai_rgm_trx_recording_pkg_b.sql version (115.1)
3. Bug# 4204880 - ssumaith - 20-feb-2005 - File version 115.2
A new column has been added into the table jai_Rgm_trx_Records called regime_primary_regno and it has been
included in the insert column list in the table jai_Rgm_trx_Records
A cursor has been added to fetch the primary registration number.
Dependency :-
High . A new column has been added into the jai_rgm_trx_records table which also needs data to be populated
If this file is sent alone , it will cause a dependency issue.
Need to ensure that the new column needs to be part of the table.
4 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.3
.added two new procedure insert_vat_repository_entry and do_vat_accounting, a function get_rgm_attribute_value
as part of VAT Impl.
.user_je_category_name that is populated into GL_INTERFACE as jai_constants.je_category_rg_entry ('Register India')
* This is a Dependant Bug for future Versions of the Object *
5. 08-Jun-2005 File Version 116.2 Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
6. 14-Jun-2005 rchandan for bug#4428980, Version 116.3
Modified the object to remove literals from DML statements and CURSORS.
7. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
GL Sources and GL Categories got changed. Refer bug for the details
DEPENDANCY:
-----------
IN60106 + 4239736 + 4245089
COMMON API that will be called from DIFFERENT Transactions of the Regime
This will call APIS to insert data into regime repository and GL Tables
Transactions that are calling this procedure are
1) AP Invoice Payments
2) AR Receipt applications onto Invoice
3) Service Tax Manual Entry form
4) Settlement Process
5) Distribution Process
8. 11-Aug-2005 Ramananda for Bug#4546114. File Version 120.2
In case of distribution from IO to OU , the accounting for cess transferred is hitting
the cenvat RM or Cenvat CG account instead of the cess account.
After this fix, the accounts that will be hit are the cenvat rm a/c / cenvat cg a/c
for the excise amt and the edu cess rm a/c / edu cess cg a/c for the cess amt.
Dependency due to this fix:
None
9. 30-JAN-2007 CSahoo for bug#5631784. File Version 120.4
Forward Porting of Bug#4742259 (TCS solution)
Function get_account is modified to give the accounts for TCS regime also.
10. 16/04/2007 kunkumar for forward porting to R12 bugnos 5003538 5051541 and 4543358
11. 14-05-2007 ssawant for bug 5879769,
Objects was not compiling. so changes are done to make it compiling.
12. 18-may-2009 vkaranam for bug#7010029 120.14.12010000.7/120.20
Issue: VAT ACCOUNTING ENTRIES FOR AR INVOICE GENERATED IN FUTRE PERIOD
Fix: Modified the cursor c_period_dtl in the procedure get_period_name.
Added the following AND condition
AND closing_status in ('O','F')
Added a order by clause also.
13. 05-Feb-2009 CSahoo for bug#9350172
ISSUE: FPBUG:CAN NOT ADD TAXES SUCCESSFULLY IN ENTER TXN INDIA LOCALIZATION FORM
FIX: Added an input parameter pn_settlement_id to the procedure insert_vat_repository_entry
This parameter pn_settlement_id is used to populate the settlement_id in the table
jai_rgm_trx_records.
14. 4-Apr-2010 Bo Li for Bug9305067
Modify the procedure insert_repository_entry and insert_vat_repository_entry.
Replace the attribute parameters with new meaningful parameters
15. 18-Apr-2012 mmurtuza for bug 12641455
Description: FOR TCS SETTLEMENT ORGANIZATION AND LOCATION FIELD IS DISABLED IN SETTLEMENT FORM
Fix: Added code for TCS in post_accounting procedure
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 )
jai_rgm_trx_recording_pkg_b.sql
----------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
115.1 4204880 IN60106 + 4146708 ja_in_alter_table_4204880.sql 115.0 ssumaith 20-feb-05 New column added to the table.
jai_rgm_process_ar_taxes_pkg_s.sql 115.1 aiyer 21-feb-05 signature change in parameters.
jai_rgm_process_ar_taxes_pkg_b.sql 115.1 aiyer 21-feb-05 signature change in parameters.
115.3 4245089 IN60106 + 4146708 ja_in_alter_table_4204880.sql 115.0 ssumaith 20-feb-05 New column added to the table.
+ 4204880
10. 01-MAR-2007 SSAWANT , File version 120.5
Forward porting the change in 11.5 bug 5642053 to R12 bug no 5662296.
Issue : PROCESSING SETTLEMENT (INDIA LOCAL) ON THE CURRENT DATE AND AT ORG LEVEL
Fix : Previously whenever transaction_date was less than or equal to last_settlement_date
it was modified to last_settlement_date + 1. Now this would be done only if
transaction_date is less than last_settlement_date as transactions can be
done on last_settlement_date.
11. 03-MAR-07 bduvarag, File version 120.5
Forward porting the change in 11.5 bug 5051541 to R12 bug
12 25-April-2007 ssawant for bug 5879769 ,File version 120.6
Forward porting of
ENH : SERVICE TAX BY INVENTORY ORGANIZATION AND SERVICE TYPE SOLUTION from 11.5( bug no 5694855) to R12 (bug no 5879769).
Fix : A new parameter p_service_type_code is added to insert_repository procedure.
This is used to insert into jai_rgm_trx_records.
A new column repository_id is added to jai_sla_entries and so the insert statement
is modified to insert the repository id
The procedure get_account is modified to return the account if the regime is SERVICE, Org Type is IO and the
tax is not of EXCISE or EXCISE CESS types
13 02/05/2006 vkaranam bug#5989740 - File version 120.8
Forward porting of 115 bug #5907436
ENH : HANDLING SECONDARY AND HIGHER EDUCATION CESS
additional cess of 1% on all taxes to be levied to fund secondary education and higher
education .
Code Changes - Cursor c_orgn_sh_cess_account is added to get code_combination_id for secondary and higher cess types .
14. 07/06/2007 sacsethi for bug 6109941
R12RUP03-ST1: CODE REVIEW COMMENTS FOR ENHANCEMENTS
Problem - when we trying to get code combination id for discount in AP , then we were passing
organization id but we defined code conbination id at OU Level in AP ,
wheich was resulting in error
Solution - 1. Now passing ln_org_id instead of organziation_id for discounts .
2. procedure post_accounting is changed to return if both credit and debit amount is zero
instead of generating oracle error.
15. 27/06/2007 CSahoo for bug#6155839, File Version 120.11
added the lv_source_name variable to get the service tax source or vat source depending on the value of the regime.
16. 07/12/2007 ssumaith - bug# 6664855 - file version - 120.3.12000000.5.
Issue :-
When service tax distribution is done between two inventory organizations,
it was causing the unbalances gl entries.
Reason being - organziation id was inserted in the reference1 column of gl_interface table.
The organization id was entered as source orgn id for source org entries and destination orgn id
for des orgn entries as a result, there was only debits or credits for one orgn because referenc1 column
is also in the used in the grouping logic.
Fix:
This was a forward port issue of the R11i bug# 5410587.
It has been forward ported.
Changes done are to pass the combination of source org and destination org into reference1 column.
17. 06-Dec-2009 Bug 7692977 File version 120.14.12010000.3 / 120.16
Issue - Duplicate accounting entries are created for service tax distribution.
Cause - During distribution, the Dr entry due to SERVICE_DISTRIBUTE_IN will be
balanced by the Cr entry due to SERVICE_DISTRIBUTE_OUT. But balancing
entries are separately passed for each of these, therefore creating
duplicate accounting.
Fix - Stopped balancing entry to be passed when the source is SERVICE_DISTRIBUTE_IN or
SERVICE_DISTRIBUTE_OUT. Also reverted the changes done for bug 7525691 earlier
because it is causing dependency. The same fix can be done without causing the
dependency. Refer bug for more details.
18. 18-Mar-2009 Bug 7525691 File version 120.14.12010000.4/120.17
Issue - 1. Both credit and debit entries passed during service tax settlement hit
the same account (recovery).
2. Duplicate accounting entries are generated during settlement.
Fix - This is forward port for bug 7518230. Details:
1. Debit entry (source is settle_in) should hit the liability account.
Credit entry (source is settle_out) should hit the recovery account.
2. Balancing entries should not be passed when source is settle_in or settle_out
Along this, bug 8329634 is also fixed. After this fix, balancing entry will not
be passed when
- source is SETTLE_IN or SETTLE_OUT
- source is SERVICE_DISTRIBUTE_IN or SERVICE_DISTRIBUTE_OUT, with
distribution type as Service to Service.
19. 17-May-2009 Bug 7522584
Issue : Service Tax entered in foreign currency for AR Invoice is not converted to Functional Currency
Fix: Modified the code in the proc insert_repository_entry. Added a multipier to the discount amount
so as to calulate the discount amount in functional currency.
20. 22-May-2009 Bug 8294236
Issue: Service Tax Transaction created Fr Exchange Balances on Tax Accounts after Settlement
Fix: Created a new procedure exc_gain_loss_accounting for creating the accounting
entries for foreign exchange gain or loss amount.
21. 20-Jul-2010 Bug 9883352
Description: Service Tax Processing fails for Bill Only SO. Code Combination ID is not
fetched from Regime Registration
Fix: Bill Only Sale Orders do not have Shipment. Location ID is passed to Function get_account
from the Shipment. get_account function changes Organization Type to OU if Location ID
is NULL. But Regime Registration Setup for Service Tax is always based on IO and not on OU
Hence removing the piece of code which changes the Organization type.
22. 24-May-2011 Xiao for pot change, reg bug#12533434.
Add parameter p_accrual_basis for procedure insert_repository_entry, and it indicates accrual basis.
If accrual basis, do not generate accounting.
23. 29-May-2011 Xiao for POT change, reg bug#12533434.
Fix: Rollback code to 120.14.12010000.11. Use parameter p_accntg_required_flag as no.
Add parameter p_accrual_basis.
Add accounting derive logic for cancellation event in procedure insert_repository_entry.
24. 23-Jun-2011 Xiao for bug#12598609.
Issue: Parent document details not shown in the journal description
Fixed: Attach invoice number to variable lv_reference10 when post_accounting, so that parent document
details can be shown in journal description.
Add AUTONOMOUS_TRANSACTION PROCEDURE get_inv_num_std to get invoice num, invoice type.
25. 22-Aug-2011 Xiao for POT Phase III changes, reg bug#12895841
Fixed: Add logic for showing transactions for AP reversal, AP claim, AR reversal, AR claim.
26. 14-dec-2011 Qiong for Bug 13456083 - PRE POT INVOICE UPDATE REPOSITORY INCORRECT
27. 23-Jan-2012 amandali for bug 13430127
Issue:ACCOUNTING ENTRIES ARE NOT VISIBLE IN SERVICE TAX REPOSITORY
Fix:in post_Accounting, while inserting into jai_cmn_journal_entries, the reference_name is now
populated with source transaction type instead of source table name as we do not have repository_id
reference in jai_cmn_journal_entries populated post POT
15. 20-jan-2012 vkaranam for bug#13593694
Issue:
Service tax distribution out is updating the repost
Fix :
For Distribute_out transaction type
jai_rgm_trx_records.trx_credit_amount has to hit with the -ve distribution
changes are done in insert_repository_entry procedure for "SERVICE_dISTRIBUTE_OUT" transaction.
----------------------------------------------------------------------------------------------------------------------------*/
/* ~~~~~~~~~~~~~~~ Start of Repository Entry ~~~~~~~~~~~~~~~~~ */
PROCEDURE insert_repository_entry(
p_repository_id OUT NOCOPY NUMBER,
p_regime_id IN NUMBER,
p_tax_type IN VARCHAR2,
p_organization_type IN VARCHAR2,
p_organization_id IN NUMBER,
p_location_id IN NUMBER,
p_source IN VARCHAR2,
p_source_trx_type IN VARCHAR2,
p_source_table_name IN VARCHAR2,
p_source_document_id IN NUMBER,
p_transaction_date IN DATE,
p_account_name IN VARCHAR2,
p_charge_account_id IN NUMBER,
p_balancing_account_id IN NUMBER,
p_amount IN OUT NOCOPY NUMBER, -- Recovered/Liable Service Tax Amount in INR Currency i.e functional
p_assessable_value IN NUMBER,
p_tax_rate IN NUMBER,
p_reference_id IN NUMBER,
p_batch_id IN NUMBER,
p_called_from IN VARCHAR2,
p_process_flag OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2,
p_discounted_amount IN OUT NOCOPY NUMBER,
p_inv_organization_id IN NUMBER DEFAULT NULL,
p_settlement_id IN NUMBER DEFAULT NULL,
-- Following all parameters are required for GL Accounting if p_balancing_account_id value is not passed to this procedure call
p_accntg_required_flag IN VARCHAR2, -- DEFAULT jai_constants.yes File.Sql.35 by Brathod
p_accounting_date IN DATE , -- DEFAULT sysdate File.Sql.35 by Brathod
p_balancing_orgn_type IN VARCHAR2 DEFAULT NULL,
p_balancing_orgn_id IN NUMBER DEFAULT NULL,
p_balancing_location_id IN NUMBER DEFAULT NULL,
p_balancing_tax_type IN VARCHAR2 DEFAULT NULL,
p_balancing_accnt_name IN VARCHAR2 DEFAULT NULL,
p_currency_code IN VARCHAR2 , -- DEFAULT jai_constants.func_curr File.Sql.35 by Brathod
p_curr_conv_date IN VARCHAR2 DEFAULT NULL,
p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
p_curr_conv_rate IN VARCHAR2 DEFAULT NULL,
p_trx_amount IN NUMBER DEFAULT NULL, -- recovered/liable service tax amount in foreign currency
--Added by Bo Li for Bug9305067 BEGIN
------------------------------------------------------------
p_trx_reference_context IN VARCHAR2 DEFAULT NULL,
p_trx_reference1 IN VARCHAR2 DEFAULT NULL,
p_trx_reference2 IN VARCHAR2 DEFAULT NULL,
p_trx_reference3 IN VARCHAR2 DEFAULT NULL,
p_trx_reference4 IN VARCHAR2 DEFAULT NULL,
p_trx_reference5 IN VARCHAR2 DEFAULT NULL,
------------------------------------------------------------
--Added by Bo Li for Bug9305067 End
p_service_type_code IN VARCHAR2 DEFAULT NULL, /* added by ssawant for bug 5989740 */
p_distribution_type IN VARCHAR2 DEFAULT NULL,
p_accrual_basis IN VARCHAR2 DEFAULT NULL, --Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
p_invoice_no IN VARCHAR2 DEFAULT NULL --Added by Qiong for advanced receipts bug#13361952
) IS
lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
It will be inserted into jai_Rgm_trx_records table.
Bug# 4204880
*/
CURSOR c_primary_regno( p_att_type_code jai_rgm_registrations.attribute_Type_code%TYPE ) IS --rchandan for bug#4428980
SELECT attribute_value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_id = p_regime_id
AND organization_id = p_organization_id
AND organization_type = p_organization_type
AND registration_type = jai_constants.regn_type_others
AND attribute_Type_code = p_att_type_code; --rchandan for bug#4428980
SELECT org_information3
FROM hr_organization_information
WHERE upper(ORG_INFORMATION_CONTEXT) = 'ACCOUNTING INFORMATION'
AND organization_id = cp_organization_id;
SELECT apa.invoice_type_lookup_code
FROM ap_invoices_all apa
, ap_invoice_distributions_all ada
WHERE apa.invoice_id = ada.invoice_id
AND ada.invoice_distribution_id = cp_invoice_distribution_id;
SELECT apa.invoice_type_lookup_code
FROM ap_invoices_all apa
, ap_invoice_payments_all ada
WHERE apa.invoice_id = ada.invoice_id
AND ada.invoice_payment_id = cp_invoice_payment_id;
SELECT trx_types.type
FROM
jai_ar_trx_tax_lines jattl --Added by Qiong for bug14253668
, ra_customer_trx_lines_all trxl
, ra_customer_trx_all trx
, ra_cust_trx_types_all trx_types
WHERE trxl.customer_trx_id = trx.customer_trx_id
AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
AND jattl.link_to_cust_trx_line_id = trxl.customer_trx_line_id --Added by Qiong for bug14253668
AND jattl.customer_trx_line_id = cp_customer_trx_line_id; --Added by Qiong for bug142536
lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'Insert_Repository_Entry', 'START');
IF p_source = jai_constants.source_ap AND lv_invoice_type NOT IN ('DEBIT', 'CREDIT') --Updated for POT for CM/DM issue
--Added by Qiong for reverse charge bug#16001407
AND p_source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING')
OR ( nvl(lv_ar_trx_type,'#') = jai_constants.ar_invoice_type_cm)--Added by Qiong for reverse charge bug#16001407
THEN--Updated for POT for CM/DM issue
lv_statement := '3';
ELSIF p_source = jai_constants.source_ar OR lv_invoice_type IN ('DEBIT', 'CREDIT') --Updated for POT for CM/DM issue
AND nvl(lv_ar_trx_type,'#') <> jai_constants.ar_invoice_type_cm --Added by Qiong for reverse charge bug#16001407
--Added by Qiong for reverse charge bug#16001407
OR p_source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING') THEN
lv_statement := '4';
Added the column regime_primary_regno in the insert column list of the table jai_rgm_trx_records table.
*/
OPEN c_primary_regno('PRIMARY'); --rchandan for bug#4428980
INSERT INTO jai_rgm_trx_records(
repository_id, regime_code, tax_type, source,
source_document_id, source_table_name, transaction_date, debit_amount, credit_amount,
settled_amount, settled_flag, settlement_id, organization_type,
organization_id, location_id, account_name, charge_account_id, balancing_account_id,
reference_id, source_trx_type, tax_rate, assessable_value, batch_id,
trx_currency, curr_conv_date, curr_conv_rate, trx_credit_amount, trx_debit_amount,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
trx_reference_context, trx_reference1, trx_reference2, trx_reference3, trx_reference4, trx_reference5
, inv_organization_id, regime_primary_regno ,service_type_code /* added by ssawant for bug 5879769 */
, invoice_no --Add by Qiong for Advanced Receipts
) VALUES (
jai_rgm_trx_records_s.nextval, lv_regime_code, p_tax_type, p_source,
p_source_document_id, p_source_table_name, ld_transaction_date, ln_debit, ln_credit,
null, null, p_settlement_id, p_organization_type,
p_organization_id, p_location_id, lv_account_name,--Modified by Xiao from p_account_name for POT change, reg bug#12533434
ln_charge_account_id, ln_balancing_account_id,
p_reference_id, p_source_trx_type, p_tax_rate, p_assessable_value, p_batch_id,
p_currency_code, p_curr_conv_date, p_curr_conv_rate, ln_trx_credit, ln_trx_debit,
sysdate, FND_GLOBAL.user_id, sysdate, FND_GLOBAL.user_id, fnd_global.login_id,
p_trx_reference_context, p_trx_reference1, p_trx_reference2, p_trx_reference3, p_trx_reference4, p_trx_reference5
, p_inv_organization_id , lv_primary_regime_regno ,p_service_type_code /* added by ssawant for bug 5879769 */
,p_invoice_no --Added by Qiong for Advanced receipts
) RETURNING repository_id INTO p_repository_id;
lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath, 'Insert_Repository_entry', 'END');
Fnd_file.put_line( fnd_file.log, 'Error in Insert_Repository_entry. Codepath:'||lv_codepath);
END insert_repository_entry;
SELECT aia.invoice_num
, aia.invoice_type_lookup_code
FROM ap_invoices_all aia
, ap_invoice_distributions_all aida
WHERE aia.invoice_id = aida.invoice_id
AND aida.invoice_distribution_id = pn_invoice_distribution_id;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = cp_organization_id;
select party_id
from jai_rgm_dis_src_hdrs
where transfer_id = cp_transfer_id ;
select destination_party_id
from jai_rgm_dis_des_hdrs
where transfer_id = cp_transfer_id ;
select source
from jai_rgm_trx_records
where repository_id = cp_repository_id ;
SELECT aia.invoice_num
, aia.invoice_type_lookup_code
FROM ap_invoices_all aia
, ap_invoice_payments_all aipa
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.invoice_payment_id = p_source_document_id;
SELECT receipt_number
FROM AR_CASH_RECEIPTS_ALL
WHERE cash_receipt_id = c_cash_receipt_id;
SELECT cash_receipt_id, APPLIED_CUSTOMER_TRX_ID
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE RECEIVABLE_APPLICATION_ID = p_source_document_id;
SELECT trx_number
FROM RA_customer_trx_all
WHERE customer_trx_id = c_customer_trx_id;
jai_cmn_utils_pkg.print_log('6395039.log', 'before insert inot gl_interface');
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, accounted_cr, accounted_dr, transaction_date,
code_combination_id, currency_conversion_date, user_currency_conversion_type, currency_conversion_rate,
reference10, reference22, reference23, reference1,
reference24, reference25, reference26, reference27
) VALUES (
lv_status, ln_sob_id, lv_source_name, lv_gl_je_category,
ld_accounting_date, p_currency_code, sysdate, FND_GLOBAL.user_id,
'A', ln_entered_cr, ln_entered_dr, ln_accounted_cr, ln_accounted_dr, p_transaction_date,
p_code_combination_id, p_curr_conv_date, p_curr_conv_type, p_curr_conv_rate,
lv_reference10, jai_constants.gl_je_source_name, p_calling_object, lv_reference1,
-- commented lv_organization_code and passed refererence1 ssumaith bug#6664855
--Bug 5051541 kunkumar
p_source_table_name, p_source_document_id, p_repository_name, p_organization_id
);
jai_cmn_utils_pkg.print_log('6395039.log', 'after insert inot gl_interface');
jai_cmn_utils_pkg.print_log('6395039.log', 'before insert inot JAI_CMN_JOURNAL_ENTRIES');
INSERT INTO JAI_CMN_JOURNAL_ENTRIES(JOURNAL_ENTRY_ID,
regime_code, organization_id, set_of_books_id, tax_type, period_name,
code_combination_id, accounted_dr, accounted_cr, transaction_date,
source, source_table_name, source_trx_id, reference_name, reference_id, repository_id,/* added by ssawant for bug 5879769 */
currency_code, curr_conv_rate, creation_date, created_by, last_update_date, last_updated_by, last_update_login
) VALUES ( JAI_CMN_JOURNAL_ENTRIES_S.nextval,
p_regime_code, p_organization_id, ln_sob_id, p_tax_type, lv_period_name,
p_code_combination_id, ln_accounted_dr, ln_accounted_cr, p_transaction_date,
p_source, p_source_table_name, p_source_document_id, p_source_trx_type /* p_reference_name- Bug 13430127 */, p_reference_id,p_repository_id,/* added by ssawant for bug 5879769 */
p_currency_code, p_curr_conv_rate, sysdate, FND_GLOBAL.user_id, sysdate, fnd_global.user_id, fnd_global.login_id
);
jai_cmn_utils_pkg.print_log('6395039.log', 'after insert inot JAI_CMN_JOURNAL_ENTRIES');
PROCEDURE insert_reference(
p_reference_id OUT NOCOPY NUMBER,
p_organization_id IN NUMBER, /* Operating Unit */
p_source IN VARCHAR2,
p_invoice_id IN NUMBER,
p_line_id IN NUMBER,
p_tax_type IN VARCHAR2,
p_tax_id IN NUMBER,
p_tax_rate IN NUMBER,
p_recoverable_ptg IN NUMBER,
p_party_type IN VARCHAR2,
p_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_trx_tax_amount IN NUMBER,
p_trx_currency IN VARCHAR2,
p_curr_conv_date IN DATE,
p_curr_conv_rate IN NUMBER,
p_tax_amount IN NUMBER,
p_recoverable_amount IN NUMBER,
p_recovered_amount IN NUMBER,
p_item_line_id IN NUMBER,
p_item_id IN NUMBER,
p_taxable_basis IN NUMBER,
p_parent_reference_id IN NUMBER,
p_reversal_flag IN VARCHAR2,
p_batch_id IN NUMBER,
p_process_flag OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2
/* Location_Id Required for VAT??? */
) IS
BEGIN
INSERT INTO jai_rgm_trx_refs(
reference_id,
organization_id,
source,
invoice_id,
line_id,
tax_type,
tax_id,
tax_rate,
recoverable_ptg,
trx_tax_amount,
trx_currency,
curr_conv_date,
curr_conv_rate,
tax_amount,
recoverable_amount,
recovered_amount,
taxable_basis,
party_type,
party_id,
party_site_id,
item_line_id,
item_id,
parent_reference_id,
reversal_flag,
batch_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
jai_rgm_trx_refs_s.nextval,
p_organization_id,
p_source,
p_invoice_id,
p_line_id,
p_tax_type,
p_tax_id,
p_tax_rate,
p_recoverable_ptg,
p_trx_tax_amount,
p_trx_currency,
p_curr_conv_date,
p_curr_conv_rate,
p_tax_amount,
p_recoverable_amount,
p_recovered_amount,
p_taxable_basis,
p_party_type,
p_party_id,
p_party_site_id,
p_item_line_id,
p_item_id,
p_parent_reference_id,
p_reversal_flag,
p_batch_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
) RETURNING reference_id INTO p_reference_id;
p_process_message := 'jai_cmn_rgm_recording_pkg.insert_reference failed with error - '||SQLERRM;
END insert_reference;
SELECT decode(cp_register_type,
jai_constants.register_type_a, modvat_rm_account_id,
jai_constants.register_type_c, modvat_cg_account_id,
jai_constants.register_type_pla, modvat_pla_account_id
)
FROM JAI_CMN_INVENTORY_ORGS a
WHERE organization_id = cp_organization_id
AND ( (cp_location_id IS NOT NULL AND a.location_id = cp_location_id)
OR (cp_location_id IS NULL AND (a.location_id IS NULL OR a.location_id = 0))
);
SELECT decode(cp_register_type,
jai_constants.register_type_a, excise_edu_cess_rm_account ,
jai_constants.register_type_c, excise_edu_cess_cg_account ,
jai_constants.register_type_pla, modvat_pla_account_id
)
FROM JAI_CMN_INVENTORY_ORGS a
WHERE organization_id = cp_organization_id
AND ( (cp_location_id IS NOT NULL AND a.location_id = cp_location_id)
OR (cp_location_id IS NULL AND (a.location_id IS NULL OR a.location_id = 0))
);
SELECT decode(cp_register_type,
jai_constants.register_type_a, SH_CESS_RM_ACCOUNT ,
jai_constants.register_type_c, SH_CESS_CG_ACCOUNT_ID ,
jai_constants.register_type_pla, modvat_pla_account_id
)
FROM JAI_CMN_INVENTORY_ORGS a
WHERE organization_id = cp_organization_id
AND ( (cp_location_id IS NOT NULL AND a.location_id = cp_location_id)
OR (cp_location_id IS NULL AND (a.location_id IS NULL OR a.location_id = 0))
);
SELECT to_number(accnts.attribute_value)
FROM JAI_RGM_REGISTRATIONS tax_types, JAI_RGM_ORG_REGNS_V accnts
WHERE tax_types.regime_id = cp_regime_id
AND tax_types.registration_type = jai_constants.regn_type_tax_types
AND tax_types.attribute_code = cp_tax_type
AND accnts.regime_id = tax_types.regime_id
AND accnts.registration_type = jai_constants.regn_type_accounts
AND accnts.parent_registration_id = tax_types.registration_id
AND accnts.attribute_code = cp_account_name
AND accnts.organization_type = cp_organization_type
AND accnts.organization_id = cp_organization_id
AND (cp_location_id IS NULL OR location_id = cp_location_id);
SELECT to_number(accnts.attribute_value)
FROM JAI_RGM_REGISTRATIONS tax_types,
jai_rgm_parties jrp ,
JAI_RGM_REGISTRATIONS accnts
WHERE tax_types.regime_id = cp_regime_id
AND jrp.regime_id = -accnts.regime_id
AND tax_types.registration_type = jai_constants.regn_type_tax_types
AND tax_types.attribute_code = cp_tax_type
AND accnts.regime_id = tax_types.regime_id
AND accnts.registration_type = jai_constants.regn_type_accounts
AND accnts.parent_registration_id = tax_types.registration_id
AND accnts.attribute_code = cp_account_name
AND jrp.organization_type = cp_organization_type
AND jrp.organization_id = cp_organization_id ;
SELECT to_number(operating_unit) org_id
FROM org_organization_definitions
WHERE organization_id = cp_organization_id;
SELECT disc_taken_code_combination_id
FROM ap_system_parameters_all
WHERE org_id = cp_org_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 = cp_sob_id
AND closing_status IN ('O','F') -- added for bug#7010029
AND cp_accounting_date BETWEEN start_date AND end_date
ORDER BY period_year, period_num; -- added for bug#7010029
FOR period IN ( 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 = ln_sob_id
AND start_date > p_accounting_date
ORDER BY period_year, period_num
)
LOOP
IF period.closing_status IN('O','F') THEN
p_sob_id := ln_sob_id;
PROCEDURE update_reference(
p_source IN VARCHAR2,
p_reference_id IN NUMBER,
p_recovered_amount IN NUMBER,
p_discounted_amount IN NUMBER DEFAULT NULL,
p_process_flag OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2
) IS
lv_statement VARCHAR2(2); -- := '1' File.Sql.35 by Brathod
UPDATE jai_rgm_trx_refs
SET recovered_amount = nvl(recovered_amount,0) + nvl(p_recovered_amount, 0),
discounted_amount = nvl(discounted_amount,0) + nvl(p_discounted_amount,0),
-- recoverable_amount = recoverable_amount - nvl(p_amount, 0),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE reference_id = p_reference_id;
p_process_message := 'jai_cmn_rgm_recording_pkg.update_reference (Stmt'||lv_statement||') Error Occured:'||SQLERRM;
END update_reference;
PROCEDURE insert_vat_repository_entry(
pn_repository_id OUT NOCOPY NUMBER,
pn_regime_id IN NUMBER,
pv_tax_type IN VARCHAR2,
pv_organization_type IN VARCHAR2,
pn_organization_id IN NUMBER,
pn_location_id IN NUMBER,
pv_source IN VARCHAR2,
pv_source_trx_type IN VARCHAR2,
pv_source_table_name IN VARCHAR2,
pn_source_id IN NUMBER,
pd_transaction_date IN DATE,
pv_account_name IN VARCHAR2,
pn_charge_account_id IN NUMBER,
pn_balancing_account_id IN NUMBER,
pn_credit_amount IN OUT NOCOPY NUMBER,
pn_debit_amount IN OUT NOCOPY NUMBER,
pn_assessable_value IN NUMBER,
pn_tax_rate IN NUMBER,
pn_reference_id IN NUMBER,
pn_batch_id IN NUMBER,
pn_inv_organization_id IN NUMBER,
pv_invoice_no IN VARCHAR2, /* this holds either generated VAT Invoice Number or Vendor Inovice Number */
pd_invoice_date IN DATE, /* this holds VAT Invoice Date or Vendor VAT Inovice Date */
pv_called_from IN VARCHAR2,
pv_process_flag OUT NOCOPY VARCHAR2,
pv_process_message OUT NOCOPY VARCHAR2,
--Added by Bo Li for Bug9305067 BEGIN
-------------------------------------------------------------
pv_trx_reference_context IN VARCHAR2 DEFAULT NULL,
pv_trx_reference1 IN VARCHAR2 DEFAULT NULL,
pv_trx_reference2 IN VARCHAR2 DEFAULT NULL,
pv_trx_reference3 IN VARCHAR2 DEFAULT NULL,
pv_trx_reference4 IN VARCHAR2 DEFAULT NULL,
pv_trx_reference5 IN VARCHAR2 DEFAULT NULL,
------------------------------------------------------------------
--Added by Bo Li for Bug9305067 END
pn_settlement_id IN NUMBER DEFAULT NULL --added for bug#9350172
) IS
CURSOR c_primary_regno(cp_regime_id IN NUMBER, cp_orgn_type in varchar2,
cp_orgn_id in number, cp_location_id in number,
p_att_type_code jai_rgm_registrations.attribute_Type_code%TYPE) IS --rchandan for bug#4428980
SELECT attribute_value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_id = cp_regime_id
AND organization_type = cp_orgn_type
AND organization_id = cp_orgn_id
and (cp_location_id is null or location_id = cp_location_id)
AND registration_type = jai_constants.regn_type_others
AND attribute_type_code = p_att_type_code;
INSERT INTO jai_rgm_trx_records(
repository_id, regime_code, tax_type, source,
source_document_id, source_table_name, transaction_date, debit_amount, credit_amount,
settled_amount, settled_flag, settlement_id, organization_type,
organization_id, location_id, account_name, charge_account_id, balancing_account_id,
reference_id, source_trx_type, tax_rate, assessable_value, batch_id,
trx_currency, curr_conv_date, curr_conv_rate, trx_credit_amount, trx_debit_amount,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
trx_reference_context, trx_reference1, trx_reference2, trx_reference3, trx_reference4, trx_reference5
, inv_organization_id, regime_primary_regno, invoice_no, invoice_date
) VALUES (
jai_rgm_trx_records_s.nextval, lv_regime_code, pv_tax_type, pv_source,
pn_source_id, pv_source_table_name, ld_transaction_date, pn_debit_amount, pn_credit_amount,
null, null, pn_settlement_id, pv_organization_type, --added pn_settlement_id for bug#9350172
pn_organization_id, pn_location_id, pv_account_name, pn_charge_account_id, pn_balancing_account_id,
pn_reference_id, pv_source_trx_type, pn_tax_rate, pn_assessable_value, pn_batch_id,
jai_constants.func_curr, null, null, pn_credit_amount, pn_debit_amount,
sysdate, FND_GLOBAL.user_id, sysdate, FND_GLOBAL.user_id, fnd_global.login_id,
pv_trx_reference_context, pv_trx_reference1, pv_trx_reference2, pv_trx_reference3, pv_trx_reference4, pv_trx_reference5
, pn_inv_organization_id, lv_primary_regime_regno, pv_invoice_no, pd_invoice_date
) RETURNING repository_id INTO pn_repository_id;
pv_process_message := 'insert_vat_repository_entry Error(Stmt:'||lv_statement_id||') Occured:'||SQLERRM;
Fnd_file.put_line( fnd_file.log, 'Error in insert_vat_repository_entry. Stmt:'||lv_statement_id);
END insert_vat_repository_entry;
are taken while passing inserting into GL
Incase a single entry needs to be passed, then pass the relevant ccid and amount
*/
PROCEDURE do_vat_accounting(
pn_regime_id IN NUMBER,
pn_repository_id IN NUMBER,
pv_organization_type IN VARCHAR2,
pn_organization_id IN NUMBER,
pd_accounting_date IN DATE,
pd_transaction_date IN DATE,
pn_credit_amount IN NUMBER,
pn_debit_amount IN NUMBER,
pn_credit_ccid IN NUMBER,
pn_debit_ccid IN NUMBER,
pv_called_from IN VARCHAR2,
pv_process_flag OUT NOCOPY VARCHAR2,
pv_process_message OUT NOCOPY VARCHAR2,
pv_tax_type IN VARCHAR2 DEFAULT NULL,
pv_source IN VARCHAR2 DEFAULT NULL,
pv_source_trx_type IN VARCHAR2 DEFAULT NULL,
pv_source_table_name IN VARCHAR2 DEFAULT NULL,
pn_source_id IN NUMBER DEFAULT NULL,
pv_reference_name IN VARCHAR2 DEFAULT NULL,
pn_reference_id IN NUMBER DEFAULT NULL
) IS
r_repo_dtl c_repository_dtl%ROWTYPE;
select get_rgm_attribute_value('VAT', 'IO', 2832, 10023, 'OTHERS', 'PRIMARY', null) from dual;
select jai_rgm_trx_recording_pkgget_rgm_attribute_value('VAT', 'IO', 2832, 10023, 'OTHERS', null, 'SAME_INVOICE_NO') from dual;
SELECT attribute_value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_code = cp_regime_code
AND organization_type = cp_orgn_type
AND organization_id = cp_orgn_id
and (cp_location_id is null or location_id = cp_location_id)
AND registration_type = cp_registration_type
AND ( (cp_attribute_code IS NOT NULL AND attribute_code = cp_attribute_code)
or (cp_attribute_code IS NULL AND attribute_type_code = cp_attribute_type_code)
);
SELECT org_information3
FROM hr_organization_information
WHERE upper(ORG_INFORMATION_CONTEXT) = 'ACCOUNTING INFORMATION'
AND organization_id = cp_organization_id;
SELECT code_combination_id_gain,
code_combination_id_loss
FROM AR_SYSTEM_PARAMETERS_ALL
WHERE org_id = p_org_id;
SELECT gain_code_combination_id,
loss_code_combination_id
FROM AP_SYSTEM_PARAMETERS_ALL
WHERE org_id = p_org_id;