[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.
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.
----------------------------------------------------------------------------------------------------------------------------*/
/* ~~~~~~~~~~~~~~~ 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
p_attribute_context IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL,
p_attribute4 IN VARCHAR2 DEFAULT NULL,
p_attribute5 IN VARCHAR2 DEFAULT NULL,
p_service_type_code IN VARCHAR2 DEFAULT NULL/* added by ssawant for bug 5989740 */
) 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;
lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'Insert_Repository_Entry', 'START');
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,
attribute_context, attribute1, attribute2, attribute3, attribute4, attribute5
, inv_organization_id, regime_primary_regno ,service_type_code /* added by ssawant for bug 5879769 */
) 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, p_account_name, 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_attribute_context, p_attribute1, p_attribute2, p_attribute3, p_attribute4, p_attribute5
, p_inv_organization_id , lv_primary_regime_regno ,p_service_type_code /* added by ssawant for bug 5879769 */
) 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 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 ;
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_reference_name, 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 cp_accounting_date BETWEEN start_date AND end_date;
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,
pv_attribute_context IN VARCHAR2 DEFAULT NULL,
pv_attribute1 IN VARCHAR2 DEFAULT NULL,
pv_attribute2 IN VARCHAR2 DEFAULT NULL,
pv_attribute3 IN VARCHAR2 DEFAULT NULL,
pv_attribute4 IN VARCHAR2 DEFAULT NULL,
pv_attribute5 IN VARCHAR2 DEFAULT NULL
) 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,
attribute_context, attribute1, attribute2, attribute3, attribute4, attribute5
, 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, null, pv_organization_type,
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_attribute_context, pv_attribute1, pv_attribute2, pv_attribute3, pv_attribute4, pv_attribute5
, 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)
);