[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Also removed a delete statement which was deleting from jai_rgm_trm_schedules_t if
there were any installments generated already.
2 26/01/2007 cbabu for bug#2942973 Version: 115.9 (INTER ORG Impl.)
Added the procedures
default_taxes - This defaults or redefaults the taxes. Incase of redefaultation, deletes the
entire data that is saved before base transaction is saved
sync_with_base_trx - This deletes IL data where in record PKs do not exist in base transaction tables
this internally uses delete_trx_autonomous or delete_trx procedures
These changes are done as part of ReArch. Inter Org Form
3 29-Jan-2007 rchandan for #2942973 for File Version 115.11 (INTER ORG Impl.)
Modified procedure DEFAULT_TAXES to recalculate taxes if the quantity alone
is changed. In other cases it would redefault taxes
4 30-Jan-2007 rchandan for bug#942973 for File Version 115.11 (INTER ORG Impl.)
Deletion from JAI_CMN_MATCH_TAXES was missing when quantity is changed after matching.
So added this statement now
5. 5-mar-2007 kunkumar - bug#5907436 -file branch 6107 on 115 main code line.
Added higher education cess needed for implementing requirements imposed by budget of 2007.
Introduces huge dependencies as there are data model changes associated with this bug.
6. 14-apr-2007 Vkaranam for bug #5907436 ,File version #115.16.6107.4 / 115.19
ENH:Handling Secondary And Higher Education Cess.
Fix:
Code changes are done in do_cenvat_acctg and cenvat_process procedures.
7. 01-08-2007 rchandan for bug#6030615 , Version 120.0
Issue : Inter org Forward porting
This is a new file in R12 now.
8. 21-aug-2007 vkaranam for bug#6030615,File version 120.3
1.Changed std_cost_entry procedure.
2.added journal_entry_id column in the insert stmt of jai_mtl_trx_jrnls
9. 21-aug-2007 vkaranam for bug#6030615,File version 120.4
Changes are done as part of the performance issue.
10. 27-sep-2007 forward porting of bug 6377964
11. 09-Oct-2007 rchandan for bug#6487364,File version 120.8
Issue : QA observations for Inter org
Fix : For receiving organization no cess entries were made For RG23D.
Added calls to do this
12 10/10/2007 rchandan for bug#6487803, File version 120.10
Issue : R12RUP04.I/ORG.QA.ST1:NOT ABLE TO DO RECEIVING IN NON BONDED SUB INVENTORY
Fix : When Direct org transfer is done to a Non Bonded Subinventory, the excise processing
should not happen for receiving org , but the transaction should go through fine.
Added an elsif condiftion to do nothing for receiving org.
For accounting as well added condition so that the excise entries will be accounted
only for trading organization or Manufacturing org for a Bonded subinventory.
13. 12/10/2007 rchandan for bug#6497301,6487489. File version 120.12
Issue : R12RUP04.I/ORG.QA.ST1: NOT ABLE TO MAKE A CAPTIAL GOODS TRANSFER FOR INTRANSIT
Fix : It is identified that there are new columns added to table jai_rcv_journal_entries
and these are not null. As the impact of this is not taken care in cenvat_auto_claim
procedure this issue was coming.
Now added these columns in the insert into JAI_RCV_JOURNAL_ENTRIES
Issue : R12RUP04.I/ORG.QA.ST1: PPV ENTRY TO BE GENERATED FOR STD COST REC ORG
Fix : For standard costing , Purchase price variance entry needs to generated
for the non - recoverable tax amount.
Made changes in std_cost_entry procedure to this effect.
PPV account is debited and Inventory receiving account is credited.
14. 15/10/2007 rchandan for bug#6487489. File version 120.13
Issue : R12RUP04.I/ORG.QA.ST1: PPV ENTRY TO BE GENERATED FOR STD COST REC ORG
Fix : The PPV generated should be for the sum of all non recoverable taxes and excisable
recoverable taxes in case of non bonded sub inventory. In case of Bonded it should be
generated only for Non receoverable tax only. Changes for this effect are made.
15. 15/10/2007 rchandan for bug#6501436, File Version 120.14
Issue : R12RUP04.I/ORG.QA.ST1:PART 2 SHOULD NOT BE UPDATED FOR REC ORG IN CASE OF FGIN
Fix : For receiving org when the item class is FGIN or FGEX, no register updates should
haappen and also accounting for this amount should also not happen.
A check for the item class is put wherever applicable.
Fix for the previous bug#6487489 is also made. In this ln_oth_modvat_amt is removed
from the calculation of ln_cost_amount.
Moreover for FGIN and FGEX clas also PPV entry needs to be modified to include the
recoverable tax which was not hitting the register and also not included in the accounting.
16. 16/10/2007 rchandan for bug#6504150, File Version 120.15
Issue : R12RUP04.I/ORG.QA.ST1:USER_JE_CATEGORY_NAM TO BE CHANGED FOR PPV ENTRY
Fix : In the gl_interface table the user_je_category_name should be populated as
'MTL' for PPV entry which is being generated for the receiving org in the
Direct Org Transfer. Made a change to this effect.
17. 12-May-2008 Changes by nprashar for bug # 6710747. Forward ported from 11i bug#6086452.
Issue:
When trying to save the IL interorg transfer a error message pops up and also
not allowing close the form. Which makes to close the application and login again.
The above mentioned issue is happening because of the deadlock on jai_mtl_trxs table.
1.on the key commit trigger of mtl_trx_line in interorg form(JAINVMTX.fmb),there is an update stmt on jai_mtl_trxs table.
If any error occurs in this trigger and the user tries to comeout of the form there is a call to sync_with_base_trx
which will delete the error record in jai_mtl_trxs table.
since it is trying to delete the same record which has been locked by the update stmt,deadlock error occurs waiting for the resource .
Fix:
changes are done in cenvat_process procedure
18. 01-JUL-2009 Bgowrava for Bug#8414075 , File Version 120.15.12000000.3
Addded round condition to the ln_amount value according to the rounding factor mentioned in tax setup and the same is passed
to jai_cmn_rgm_terms_pkg.generate_term_schedules.
19. 18-aug-2009 vkaranam for bug#8800063,file version 120.15.12000000.6
Issue:
IL INTER ORGANIZATION TRANSFER IS GIVING DEADLOCK ERROR
issue is happening with the delete_trx_autonomous (Pragma autonomous
transaction) procedure.
The below stmt has been executed from (JAINVMTX.KEY-COMMIT)-->
jai_mtl_trxs_pkg. sync_trx_with_base (delete_trx) procedure.
Delete JAI_MTL_TRXS WHERE TRANSACTION_HEADER_ID = :B2 AND TRANSACTION_TEMP_ID
= NVL(:B1 , TRANSACTION_TEMP_ID ) AND TRANSACTION_COMMIT_DATE IS NULL.
Lock is acquired by the current session and it will be removed once the
transaction gets commited/rollbacked.
When any error comes during the forms commit processing the changes will not
get applied to the database..
Here some base error "lot/serial number does not match" is coming and the
changes are not getting applied due to which lock exists on jai_mtl_trxs..
Now when we try to close the form then the below stmt will be executed.
( JAINVMTX.KEY-EXIT --->sync_with_base_trx (delete_trx_autonomous)).
Delete JAI_MTL_TRXS WHERE TRANSACTION_HEADER_ID = :B2 AND TRANSACTION_TEMP_ID
= NVL(:B1 , TRANSACTION_TEMP_ID ) AND TRANSACTION_COMMIT_DATE IS NULL.
This delete is waiting for the lock acquired for the previous delete stmt.
hence the deadlock issue is coming
Fix:
Modified sync_with_base_trx such that delete_trx_autonomous will not be used
anymore.
13-oct-2009 vkaranam for bug#8882785
Issue:
TST1212.XB1.QA.INCLUSIVE TAX IS NOT RIGHT FOR INTER-ORG TRANSFER TRANSACTION
if the interorg transfer has the inclusive taxes and the assessable price list is not \
attached to the internal ct,then the taxes are calculated wrongly.
issue is with the assessable_value being rounded.
In package jai_mtl_trxs_pkg.default_taxes, the variable is defined as
following:
ln_assessable_value number(15);
Change the parameters for the procedure insert_vat_repository_entry .
2010/09/06 Peng Zheng For bug10043656
New Tax Category Default logic for GST
2010/09/10 Jia for GST Bug#10043656.
03-dec-2010 vkaranam for bug#10242419
Issue:WRONG ACCOUNTING FOR IL INTER ORG
Fix: modified the do_cenvat_accounting procedure for cess and shecess
Correct accounting entries are as follows :
Direct org transfer :
Receiving organization is trading
Set1 :
Inventory Receiving Dr
Interorg Payable Account Cr
Set2:
Excise RG23D account Dr
Inventory Receiving Cr
In the ct case Set2 accounting entries are not getting generated for Basic
excise,But the same is happening for cess and shecess.
05-dec-2010 vkaranam for bug#10242419
Issue:WRONG ACCOUNTING FOR IL INTER ORG
Direct transfer ,sending organization accounting entry for excise is hitting "excise rcvble account" instead of excise paid accnt.
Fix:
Modified do_cenvat_Acctg to pass the excise paid accnt for sending org direct transfer excise entry
05-jan-2011 vkaranam for bug#10607819
Issue:
GL IMPORT FOR SOURCE "PURCHASING" COMPLETING WITH WARNING-ERROR CODE EU02
Reason:
CG auto claim entries for excise cess has 2 debit entries instead of 1 debit and 1 credit.
Also the SHE cess accounting is not done during the CG auto claim.
Fix:
changes are done in cenvat_auto_claim.
Accounting entries shall be in sync with the basic excise:
Credit Excise cess receivable account
Debit Excise Cess CG account
SHECESS:
Credit Excise SHE cess receivable account
Debit Excise SHE Cess CG account
5. 30-AUG-2011 vkaranam for bug#12807591
Issue:In Trading Organization, RG23D reversal accounting for Sales order shipment is hitting the OSP Excise account
provided in the Organization additional Info screen instead of the excise paid payble account.
fix:
Replace excise_paid_account with excise_rcvble_account for excise tax in do_cenvat_Acctg procedure.
changes are done in cenvat_auto_claim procedure
(replaced excise_rcvble_account with cenvat_rcvble_Account in cenvat_auto_claim).
------------------------------------------------------------------------------------------------------------------------
*/
TYPE gl_params IS RECORD (
amount NUMBER,
credit_account gl_interface.code_combination_id%TYPE,
debit_account gl_interface.code_combination_id%TYPE,
organization_id NUMBER,
organization_code gl_interface.reference1%TYPE,
remarks VARCHAR2(64));
SELECT excise_in_RG23D excise_in_trading , NVL(manufacturing,'N') , NVL(trading ,'N')
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id;
select item_Trading_flag , excise_flag
from JAI_INV_ITM_SETUPS
where inventory_item_id = p_item_id
and organization_id = p_organization_id;
SELECT
rtl.tax_type,
nvl(rtl.tax_amt, 0) tax_amount,
nvl(rtl.modvat_flag, 'N') modvat_flag,
nvl(rtl.currency_code, 'INR') currency,
nvl(jtc.mod_cr_percentage, 0) mod_cr_percentage
FROM
jai_cmn_document_taxes rtl,
jai_cmn_taxes_all jtc
WHERE
source_doc_line_id = p_source_line_id
AND jtc.tax_id = rtl.tax_id
AND source_doc_type = 'INTERORG_XFER'
)
LOOP
ln_converted_tax_amt := tax_rec.tax_amount;
SELECT trx.transaction_date,
trx.inventory_item_id,
trx.transaction_uom,
trx.transaction_type_id,
trx.from_organization,
trx.to_organization,
trx.to_subinventory,
trx.transaction_temp_id,
--trx.excise_invoice_no,/*Added by nprashar for bug 6710747*/
trx.assessable_value,
subinv.bonded,
itm.excise_flag,
itm.item_class,
itm.item_trading_flag,
subinv.trading,
trx.location_id,
trx.quantity,
trx.creation_date,
trx.created_by,
trx.last_update_date,
trx.last_update_login
FROM jai_mtl_trxs trx,
JAI_INV_SUBINV_DTLS subinv,
JAI_INV_ITM_SETUPS itm
WHERE subinv.organization_id = trx.to_organization
AND itm.organization_id = trx.to_organization
AND itm.inventory_item_id = trx.inventory_item_id
AND subinv.sub_inventory_name = trx.to_subinventory
AND trx.transaction_temp_id = p_transaction_temp_id
AND trx.quantity > 0 ;
SELECT trading, manufacturing, excise_duty_range, excise_duty_division
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id
AND location_id = p_loc_id;
SELECT SUM(decode(tax_type, 'Excise', round(tax_amt), 0)) exc,
SUM(decode(tax_type, 'Addl. Excise', round(tax_amt), 0)) additional_ed,
SUM(decode(tax_type, 'Other Excise', round(tax_amt), 0)) other_ed,
SUM(decode(tax_type, jai_constants.tax_type_exc_edu_cess, round(tax_amt), 0)) other_cess,
sum(decode(tax_type, jai_constants.tax_type_sh_exc_edu_cess, round(tax_amt), 0)) other_sh_cess --Added higher education cess constants by vkaranam for bug#5907436
FROM jai_cmn_document_taxes tax,
jai_mtl_trxs trx
WHERE tax.source_doc_line_id = trx.transaction_temp_id
AND trx.transaction_header_id = tax.source_doc_id
AND trx.transaction_temp_id = trx_temp_id;
SELECT NVL(sum(tax_rate),0) , count(1)
FROM jai_cmn_document_taxes
WHERE source_doc_line_id = cp_temp_id
AND TAX_TYPE in ('Addl. Excise','Excise','Other Excise');
select sum(a.quantity_applied) quantity_applied , sum(b.excise_duty_rate) excise_duty_rate
from JAI_CMN_MATCH_RECEIPTS a ,JAI_CMN_RG_23D_TRXS b
where a.receipt_id = b.register_id
and a.ref_line_id = cp_temp_id
and a.order_invoice = 'X';
stmt_name := 'Selecting fin_year from JAI_CMN_FIN_YEARS';
SELECT fin_year
INTO l_fin_year
FROM JAI_CMN_FIN_YEARS
WHERE organization_id = main_rec.to_organization
AND fin_active_flag = 'Y';
P_LAST_UPDATE_DATE => sysdate,
P_LAST_UPDATED_BY => fnd_global.user_id,
P_LAST_UPDATE_LOGIN => main_rec.last_update_login,
P_CALLED_FROM => 'XFER',
P_CESS_AMOUNT => ln_cess_amount ,
P_SH_CESS_AMOUNT =>ln_other_sh_cess
);
stmt_name := 'Calling jai_cmn_rg_23ac_i_trxs_pkg.insert_row';
jai_cmn_rg_23ac_i_trxs_pkg.insert_row(
P_REGISTER_ID => l_register_id,
P_INVENTORY_ITEM_ID => main_rec.inventory_item_id,
P_ORGANIZATION_ID => main_rec.to_organization,
P_QUANTITY_RECEIVED => main_rec.quantity,
P_RECEIPT_ID => main_rec.transaction_temp_id,
P_TRANSACTION_TYPE => 'RECEIVE', --Changes by nprashar for bug # 11886787,replaced transaction type R with RECEIVE
P_RECEIPT_DATE => main_rec.transaction_date,
P_PO_HEADER_ID => null,
P_PO_HEADER_DATE => null,
P_PO_LINE_ID => null,
P_PO_LINE_LOCATION_ID => null,
P_VENDOR_ID => main_rec.from_organization,
P_VENDOR_SITE_ID => NULL,
P_CUSTOMER_ID => null,
P_CUSTOMER_SITE_ID => null,
P_GOODS_ISSUE_ID => null,
P_GOODS_ISSUE_DATE => null,
P_GOODS_ISSUE_QUANTITY => null,
P_SALES_INVOICE_ID => null,
P_SALES_INVOICE_DATE => null,
P_SALES_INVOICE_QUANTITY => null,
P_EXCISE_INVOICE_ID => p_excise_inv_no /*Replacing main_rec.excise_invoice_no for bug # 6710747*/,
P_EXCISE_INVOICE_DATE => sysdate,
P_OTH_RECEIPT_QUANTITY => null,
P_OTH_RECEIPT_ID => null,
P_OTH_RECEIPT_DATE => null,
P_REGISTER_TYPE => l_register_type,
P_IDENTIFICATION_NO => null,
P_IDENTIFICATION_MARK => null,
P_BRAND_NAME => null,
P_DATE_OF_VERIFICATION => null,
P_DATE_OF_INSTALLATION => null,
P_DATE_OF_COMMISSION => null,
P_REGISER_ID_PART_II => null,
P_PLACE_OF_INSTALL => null,
P_REMARKS => 'Inter Org transfer from '||main_rec.from_organization||' To '||main_rec.to_organization,
P_LOCATION_ID => main_rec.location_id,
P_TRANSACTION_UOM_CODE => main_rec.transaction_uom,
P_TRANSACTION_DATE => main_rec.transaction_date,
P_BASIC_ED => l_excise_duty,
P_ADDITIONAL_ED => l_additional_ed,
P_ADDITIONAL_CVD => null,
P_OTHER_ED => l_other_ed,
P_CHARGE_ACCOUNT_ID => null,
P_TRANSACTION_SOURCE => null,
P_CALLED_FROM => 'XFER',
P_SIMULATE_FLAG => null,
P_PROCESS_STATUS => l_process_status,
P_PROCESS_MESSAGE => l_process_message
);
stmt_name := 'Calling jai_cmn_rg_23d_trxs_pkg.insert_row';
jai_cmn_rg_23d_trxs_pkg.insert_row(
P_REGISTER_ID => l_register_id,
P_ORGANIZATION_ID => main_rec.to_organization,
P_LOCATION_ID => main_rec.location_id,
P_TRANSACTION_TYPE => 'R',
P_RECEIPT_ID => main_rec.transaction_temp_id,
P_QUANTITY_RECEIVED => main_rec.quantity,
P_INVENTORY_ITEM_ID => main_rec.inventory_item_id,
P_SUBINVENTORY => main_rec.to_subinventory,
P_REFERENCE_LINE_ID => null,
P_TRANSACTION_UOM_CODE => main_rec.transaction_uom,
P_CUSTOMER_ID => null,
P_BILL_TO_SITE_ID => null,
P_SHIP_TO_SITE_ID => null,
P_QUANTITY_ISSUED => null,
P_REGISTER_CODE => null,
P_RELEASED_DATE => null,
P_COMM_INVOICE_NO => p_excise_inv_no /*Replacing main_rec.excise_invoice_no for bug # 6710747*/,
P_COMM_INVOICE_DATE => sysdate,
P_RECEIPT_BOE_NUM => null,
P_OTH_RECEIPT_ID => null,
P_OTH_RECEIPT_DATE => null,
P_OTH_RECEIPT_QUANTITY => null,
P_REMARKS => 'Inter Org transfer from '||main_rec.from_organization||' To '||main_rec.to_organization,
P_QTY_TO_ADJUST => main_rec.quantity,
P_RATE_PER_UNIT => v_tax_rate,
P_EXCISE_DUTY_RATE => ln_total_tax_rate,
P_CHARGE_ACCOUNT_ID => null,
P_DUTY_AMOUNT => round(l_duty_amt,0),
P_RECEIPT_DATE => sysdate,
P_GOODS_ISSUE_ID => null,
P_GOODS_ISSUE_DATE => null,
P_GOODS_ISSUE_QUANTITY => null,
P_TRANSACTION_DATE => main_rec.transaction_date,
P_BASIC_ED => round(l_excise_duty,0),
P_ADDITIONAL_ED => round(l_additional_ed,0),
P_ADDITIONAL_CVD => null,
P_OTHER_ED => round(l_other_ed,0),
P_CVD => null,
P_VENDOR_ID => main_rec.from_organization,
P_VENDOR_SITE_ID => NULL,
P_RECEIPT_NUM => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_CONSIGNEE => null,
P_MANUFACTURER_NAME => null,
P_MANUFACTURER_ADDRESS => null,
P_MANUFACTURER_RATE_AMT_PER_UN => null,
P_QTY_RECEIVED_FROM_MANUFACTUR => null,
P_TOT_AMT_PAID_TO_MANUFACTURER => null,
P_OTHER_TAX_CREDIT => NVL(ln_cess_amount,0)+NVL(ln_other_sh_cess,0) ,--ADDED ln_other_sh_cessby vkaranam for bug #5907436
P_OTHER_TAX_DEBIT => null,
P_TRANSACTION_SOURCE => p_transaction_type,
P_CALLED_FROM => 'XFER',
P_SIMULATE_FLAG => null,
P_PROCESS_STATUS => l_process_status,
P_PROCESS_MESSAGE => l_process_message
);
stmt_name := 'Calling jai_cmn_rg_others_pkg.insert_row for Cess of RG23D';
jai_cmn_rg_others_pkg.insert_row(p_source_type => 3,
p_source_name => 'RG23D',
p_source_id => l_register_id,
p_tax_type => jai_constants.tax_type_exc_edu_cess,
debit_amt => NULL,
credit_amt => ln_cess_amount,
p_process_flag => p_process_status,
p_process_msg => p_process_message
);
stmt_name := 'Calling jai_cmn_rg_others_pkg.insert_row for SH Cess of RG23D';
jai_cmn_rg_others_pkg.insert_row(p_source_type => 3,
p_source_name => 'RG23D',
p_source_id => l_register_id,
p_tax_type => jai_constants.tax_type_sh_exc_edu_cess,
debit_amt => NULL,
credit_amt => ln_other_sh_cess,
p_process_flag => p_process_status,
p_process_msg => p_process_message
);
stmt_name := 'Calling jai_cmn_rg_23ac_ii_pkg.insert_row';
jai_cmn_rg_23ac_ii_pkg.insert_row(
P_REGISTER_ID => l_register_id_ii,
P_INVENTORY_ITEM_ID => main_rec.inventory_item_id,
P_ORGANIZATION_ID => main_rec.to_organization,
P_RECEIPT_ID => main_rec.transaction_temp_id,
P_RECEIPT_DATE => null,
P_CR_BASIC_ED => l_excise_duty,
P_CR_ADDITIONAL_ED => l_additional_ed,
P_CR_ADDITIONAL_CVD => null,
P_CR_OTHER_ED => l_other_ed,
P_DR_BASIC_ED => null,
P_DR_ADDITIONAL_ED => null,
P_DR_ADDITIONAL_CVD => null,
P_DR_OTHER_ED => null,
P_EXCISE_INVOICE_NO => p_excise_inv_no /*Replacing main_rec.excise_invoice_no for bug # 6710747*/,
P_EXCISE_INVOICE_DATE => sysdate,
P_REGISTER_TYPE => l_register_type,
P_REMARKS => 'Inter Org transfer from '||main_rec.from_organization||' To '||main_rec.to_organization,
P_VENDOR_ID => main_rec.from_organization,
P_VENDOR_SITE_ID => NULL,
P_CUSTOMER_ID => null,
P_CUSTOMER_SITE_ID => null,
P_LOCATION_ID => main_rec.location_id,
P_TRANSACTION_DATE => main_rec.transaction_date,
P_CHARGE_ACCOUNT_ID => null,
P_REGISTER_ID_PART_I => l_register_id ,
P_REFERENCE_NUM => null,
P_ROUNDING_ID => null,
P_OTHER_TAX_CREDIT =>nvl(ln_cess_amount,0)+NVL(ln_other_sh_cess,0) ,--ADDED ln_other_sh_cessby vkaranam for bug #5907436,
P_OTHER_TAX_DEBIT => null,
p_transaction_type => 'R',
P_TRANSACTION_SOURCE => null,
P_CALLED_FROM => null,
P_SIMULATE_FLAG => null,
P_PROCESS_STATUS => l_process_status,
P_PROCESS_MESSAGE => l_process_message
);
UPDATE JAI_CMN_RG_I_TRXS
SET register_id_part_ii = l_register_id_ii,cess_amt = ln_Cess_amount
WHERE register_id = l_register_id;
UPDATE JAI_CMN_RG_23AC_I_TRXS SET register_id_part_ii = l_register_id_ii WHERE register_id = l_register_id;
stmt_name := 'UPDATE register_id = '||l_register_id||': processed_flag = '||processed_flag;
SELECT JAI_CMN_RG_23AC_I_TRXSI_S.CURRVAL INTO v_register_id FROM dual;
SELECT JAI_CMN_RG_23AC_I_TRXSI_S.CURRVAL INTO v_register_id FROM dual;
stmt_name := 'Calling jai_cmn_rg_others_pkg.insert_row';
jai_cmn_rg_others_pkg.insert_row(
P_SOURCE_TYPE => 1 ,
P_SOURCE_NAME => lv_source_name ,
P_SOURCE_ID => l_register_id_ii ,
P_TAX_TYPE => 'EXCISE_EDUCATION_CESS' ,
DEBIT_AMT =>null ,
CREDIT_AMT =>ln_cess_amount ,
P_PROCESS_FLAG =>l_process_status ,
P_PROCESS_MSG =>l_process_message
);
stmt_name := 'Calling jai_cmn_rg_others_pkg.insert_row';
jai_cmn_rg_others_pkg.insert_row(
P_SOURCE_TYPE => 1 ,
P_SOURCE_NAME => lv_source_name ,
P_SOURCE_ID => l_register_id_ii ,
P_TAX_TYPE => jai_constants.tax_type_sh_exc_edu_cess ,
DEBIT_AMT =>null ,
CREDIT_AMT =>ln_other_sh_cess ,
P_PROCESS_FLAG =>l_process_status ,
P_PROCESS_MSG =>l_process_message
);
SELECT regime_id
FROM jai_rgm_definitions
WHERE regime_code = 'VAT';
SELECT 1
FROM
jai_rgm_trx_records jrtr
WHERE
jrtr.attribute1 = cp_transaction_header_id AND
jrtr.source_document_id = cp_transaction_temp_id AND
jrtr.reference_id = cp_tax_id AND
jrtr.organization_id=p_organization_id AND
jrtr.location_id=p_location_id;
select tax_id
from jai_cmn_document_taxes
where source_doc_id=cp_transaction_header_id
and source_doc_line_id=cp_transaction_temp_id
and source_doc_type='INTERORG_XFER';
SELECT
jtc.tax_type,
jtc.tax_rate,
--jtc.tax_id,
jmt.transaction_temp_id,
jmt.transaction_header_id,
--jmt.creation_date,
sum(jcdt.tax_amt) tax_amt
FROM
jai_mtl_trxs jmt,
jai_cmn_document_taxes jcdt,
jai_cmn_taxes_all jtc,
jai_rgm_registrations jrg,
jai_rgm_definitions jrr
WHERE
jmt.to_organization = p_organization_id
AND jmt.location_id = p_location_id
AND jmt.transaction_header_id = p_transaction_header_id
AND jmt.transaction_temp_id=p_transaction_temp_id
AND jmt.transaction_header_id=jcdt.source_doc_id
AND jmt.transaction_temp_id=jcdt.source_doc_line_id
AND jcdt.tax_id=jtc.tax_id
AND jtc.tax_type= jrg.attribute_code -- bug 6436781
AND jrr.regime_code = jai_constants.vat_regime
AND jrg.regime_id = jrr.regime_id
AND jrg.registration_type = 'TAX_TYPES'
AND upper(jrg.attribute_code) <> 'VAT REVERSAL'
GROUP BY jtc.tax_type,
jtc.tax_rate,
jmt.transaction_temp_id,
jmt.transaction_header_id;
stmt_name:='Calling insert vat repository entry';
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => ln_regime_id,
pv_tax_type => rec_claims.tax_type,
pv_organization_type => jai_constants.orgn_type_io,
pn_organization_id => p_organization_id,
pn_location_id => p_location_id,
pv_source => jai_constants.source_rcv,
pv_source_trx_type => lv_source_trx_type,
pv_source_table_name => table_rcv_transactions,
pn_source_id => p_transaction_temp_id,
pd_transaction_date => trunc(sysdate),
pv_account_name => lv_account_name,
pn_charge_account_id => ln_code_combination_id,
pn_balancing_account_id => ln_interim_recovery_account,
pn_credit_amount => ln_entered_cr,
pn_debit_amount => ln_entered_dr,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => p_transaction_temp_id,/*r_claim_schedule.claim_schedule_id,*/
pn_batch_id => NULL,
pn_inv_organization_id => P_organization_id,
pv_invoice_no => p_vat_invoice_no,
pd_invoice_date => trunc(sysdate),
pv_called_from => 'JAINVMTX',
pv_process_flag => lv_process_status,
pv_process_message => lv_process_message,
--Added by Bo Li for bug9305067 2010-4-14 BEGIN
--------------------------------------------------
pv_trx_reference_context => NULL,
pv_trx_reference1 => NULL,
pv_trx_reference2 => NULL,
pv_trx_reference3 => NULL,
pv_trx_reference4 => NULL,
pv_trx_reference5 => NULL
--------------------------------------------------
--Added by Bo Li for bug9305067 2010-4-14 END
);
select rcv.receipt_num,
rcv.quantity,
cen.cenvat_claimed_ptg,
cen.quantity_for_2nd_claim,
cen.cenvat_amt_for_2nd_claim,
cen.shipment_line_id,
cen.cenvat_claimed_amt,
cen.other_cenvat_claimed_amt ,
cen.other_cenvat_amt_for_2nd_claim,
cen.cenvat_amount,
cen.other_cenvat_amt,
cen.transaction_id ,
rcv.excise_invoice_no ,
rcv.excise_invoice_date
from
JAI_RCV_TRANSACTIONS rcv,
JAI_RCV_CENVAT_CLAIMS cen
where
rcv.shipment_line_id=cen.shipment_line_id
and rcv.item_class in ('CGIN','CGEX')
and rcv.transaction_type='RECEIVE'
and cen.cenvat_claimed_ptg<>0
and cen.quantity_for_2nd_claim is not null
and organization_id=p_organization_id
order by receipt_num;
stmt_name := 'Calling insert into gl_interface debit';
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,
reference23,
reference24,
reference25,
reference26,
reference27,
reference22
)
VALUES
('NEW',
p_set_of_books_id,
p_je_source_name,
p_je_category_name,
sysdate,
p_currency_code,
sysdate,
fnd_global.user_id,
'A',
null,
p_params(i).amount,
sysdate,
p_params(i).debit_account, -- Derived Value from JAI_CMN_INVENTORY_ORGS / mtl_interorg_parameters
null,
null,
null,
p_params(i).organization_code, -- From mtl_parameters
'India Localization Entry for Interorg-XFER ',
'jai_mtl_trx_pkg.do_cenvat_Acctg',
'jai_mtl_trxs',
p_transaction_temp_id,
'transaction_temp_id',
to_char(p_params(i).organization_id),
'India Localization Entry' -- bug 6487405
);
stmt_name := 'Calling insert into jai_mtl_trx_jrnls debit';
insert into jai_mtl_trx_jrnls
(journal_entry_id,
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
entered_cr,
entered_dr,
transaction_date,
code_combination_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
reference1,
reference10,
reference23,
reference24,
reference25,
reference26,
reference27,
creation_Date,
last_updated_by,
last_update_date,
last_update_login,
transaction_temp_id
)
VALUES
(jai_mtl_trx_jrnls_s.nextval,
'NEW',
p_set_of_books_id,
p_je_source_name,
p_je_category_name,
sysdate,
p_currency_code,
sysdate,
fnd_global.user_id,
null,
p_params(i).amount,
sysdate,
p_params(i).debit_account, -- Derived Value from JAI_CMN_INVENTORY_ORGS / mtl_interorg_parameters
null,
null,
null,
p_params(i).organization_code, -- From mtl_parameters
'India Localization Entry for Interorg-XFER ',
'jai_mtl_trx_pkg.do_cenvat_Acctg',
'jai_mtl_trxs',
p_transaction_temp_id,
'transaction_temp_id',
to_char(p_params(i).organization_id),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_transaction_temp_id
);
stmt_name := 'Calling insert int gl_interface credit';
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,
reference23,
reference24,
reference25,
reference26,
reference27,
reference22
)
VALUES
('NEW',
p_set_of_books_id,
p_je_source_name,
p_je_category_name,
sysdate,
p_currency_code,
sysdate,
fnd_global.user_id,
'A',
p_params(i).amount,
null,
sysdate,
p_params(i).credit_account,
null,
null,
null,
p_params(i).organization_code,
'India Localization Entry for Interorg-XFER ',
'jai_mtl_trx_pkg.do_cenvat_Acctg',
'jai_mtl_trxs',
p_transaction_temp_id,
'transaction_temp_id',
to_char(p_params(i).organization_id),
'India Localization Entry' -- bug 6487405
);
stmt_name := 'Calling insert int jai_mtl_trx_jrnls credit';
insert into jai_mtl_trx_jrnls
(journal_entry_id,
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
entered_cr,
entered_dr,
transaction_date,
code_combination_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
reference1,
reference10,
reference23,
reference24,
reference25,
reference26,
reference27,
creation_Date,
last_updated_by,
last_update_date,
last_update_login,
transaction_temp_id
)
VALUES
(jai_mtl_trx_jrnls_s.nextval,
'NEW',
p_set_of_books_id,
p_je_source_name,
p_je_category_name,
sysdate,
p_currency_code,
sysdate,
fnd_global.user_id,
p_params(i).amount,
null,
sysdate,
p_params(i).credit_account,
null,
null,
null,
p_params(i).organization_code,
'India Localization Entry for Interorg-XFER ',
'jai_mtl_trx_pkg.do_cenvat_Acctg',
'jai_mtl_trxs',
p_transaction_temp_id,
'transaction_temp_id',
to_char(p_params(i).organization_id),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_transaction_temp_id
);
SELECT A.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
jai_constants.tax_type_exc_edu_cess, 5,jai_constants.tax_type_sh_exc_edu_cess,6, /*changed taxtype_val to 6 for sh_cess by vkaranam for bug #5907436*/--Added higher education cess by kundan kumar for bug#5907436
'TDS', 2, 0)) tax_type_val,
A.Tax_Amt tax_amount,
b.tax_account_id ,
A.Tax_Type tax_type,
d.from_organization ,
d.from_subinventory ,
d.to_organization ,
d.to_subinventory ,
d.location_id,
d.inventory_item_id
FROM Jai_cmn_document_Taxes A,
jai_cmn_taxes_all B,
jai_mtl_trxs D,
jai_regime_tax_types_v aa
WHERE source_doc_line_id = p_transaction_temp_id
AND d.transaction_temp_id = p_transaction_temp_id
AND a.source_doc_type = 'INTERORG_XFER'
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type;
SELECT location_id
FROM JAI_INV_SUBINV_DTLS
WHERE organization_id = p_organization_id
AND sub_inventory_name = p_subinventory;
SELECT *
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id;
SELECT *
FROM mtl_interorg_parameters
WHERE from_organization_id = p_from_organization_id
AND to_organization_id = p_to_organization_id ;
SELECT * FROM jai_mtl_trxs
WHERE transaction_temp_id = p_transaction_temp_id;
select receiving_account_id
FROM rcv_parameters
WHERE organization_id = p_organization_id;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = p_org_id;
select item_class
from JAI_INV_ITM_SETUPS
where inventory_item_id = cp_inv_item_id
and organization_id = cp_orgn_id ;
SELECT bonded
FROM JAI_INV_SUBINV_DTLS
WHERE organization_id = p_organization_id
AND sub_inventory_name = p_subinventory;
stmt_name := 'Selecting From_organization_code';
stmt_name := 'Selecting to_organization_code';
stmt_name := 'Selecting regime id';
SELECT regime_id
INTO l_regime_id
FROM jai_rgm_definitions
WHERE regime_Code = 'VAT';
SELECT transaction_type_id, transaction_source_type_id, transaction_action_id
FROM mtl_transaction_types
WHERE transaction_type_name = cp_transaction_type_name;
SELECT mp.default_cost_group_id
FROM mtl_parameters mp
WHERE mp.organization_id = cp_organization_id
AND mp.primary_cost_method = 2; --Average
SELECT * from rcv_parameters
WHERE organization_id = cp_organization_id;
SELECT *
FROM mtl_parameters
WHERE organization_id = cp_organization_id;
SELECT * from jai_mtl_Trxs
WHERE transaction_Temp_id = cp_Trx_temp_id;
SELECT 1
FROM user_procedures
WHERE object_name = cp_object_name
AND procedure_name = cp_procedure_name ;
SELECT INTERORG_PRICE_VAR_ACCOUNT
FROM mtl_interorg_parameters
WHERE from_organization_id = cp_from_organization_id
AND to_organization_id = cp_to_organization_id ;
SELECT bonded
FROM jai_inv_subinv_dtls
WHERE organization_id = cp_organization_id
AND sub_inventory_name = cp_sub_inventory;
SELECT jiis.item_class
FROM jai_mtl_trxs jmt,
JAI_INV_ITM_SETUPS jiis
WHERE jmt.inventory_item_id = jiis.inventory_item_id
AND jmt.transaction_temp_id = p_trx_temp_id;
lv_trx_type_name := 'Average cost update' ;--'Direct Org Transfer';
lv_transaction_source_name VARCHAR2(30) := 'Avg Cost Update Conversion';
INSERT INTO mtl_transactions_interface (
source_code ,
source_line_id ,
source_header_id ,
process_flag ,
transaction_mode ,
transaction_interface_id ,
transaction_header_id ,
inventory_item_id ,
organization_id ,
revision ,
transaction_quantity ,
transaction_uom ,
transaction_date ,
transaction_source_name ,
transaction_type_id ,
transaction_source_type_Id ,
rcv_transaction_id ,
transaction_reference ,-- mtl_transaction Id.
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
cost_group_id ,
material_account ,
material_overhead_account ,--overhead absorption account
resource_account ,
overhead_account ,
outside_processing_account ,
lock_flag ,
transaction_action_id ,
transfer_organization,
transfer_subinventory ,
subinventory_code ,
value_change
)
VALUES (
lv_source_code ,
ln_src_line_id ,
ln_src_header_id ,
ln_process_flag ,
ln_transaction_mode ,
mtl_material_transactions_s.nextval ,
decode( p_txn_header_id, null ,
mtl_material_transactions_s.currval ,
p_txn_header_id
) ,
p_item_id ,
p_organization_id ,
null ,
ln_quantity , -- No Qty
p_uom_code ,
p_transaction_date ,
lv_transaction_source_name ,
p_transaction_type_id , -- Avg Cost Update
p_transaction_source_type_id , -- Inventory
p_transaction_id ,
to_char(p_transaction_id) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_cost_group_id ,
p_receiving_account_id ,
p_absorption_account_id ,
p_receiving_account_id ,
p_receiving_account_id ,
p_receiving_account_id ,
ln_lock_flag ,
p_transaction_action_id ,
p_from_organization_id ,
p_from_subinventory,
p_to_subinventory ,
p_value_change
)
RETURNING transaction_interface_id ,
transaction_header_id
INTO ln_txn_interface_id ,
p_txn_header_id ;
INSERT INTO JAI_MTL_TXN_CST_HDR_T (
source_code ,
source_line_id ,
source_header_id ,
process_flag ,
transaction_mode ,
transaction_interface_id ,
transaction_header_id ,
inventory_item_id ,
organization_id ,
revision ,
transaction_quantity ,
transaction_uom ,
transaction_date ,
transaction_source_name ,
transaction_type_id ,
transaction_source_type_Id , --PVI
rcv_transaction_id ,
transaction_reference , -- rcv_transaction Id.
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
cost_group_id ,
material_account ,
material_overhead_account , --overhead absorption account
resource_account ,
overhead_account ,
outside_processing_account ,
lock_flag ,
transaction_id
)
VALUES (
lv_source_code ,
ln_src_line_id ,
ln_src_header_id ,
ln_process_flag ,
ln_transaction_mode ,
ln_txn_interface_id ,
p_txn_header_id ,
p_item_id ,
p_organization_id ,
null ,
ln_quantity , -- No Qty
p_uom_code ,
p_transaction_date ,
lv_transaction_source_name ,
p_transaction_type_id , -- Avg Cost Update
p_transaction_source_type_id , -- Inventory
p_transaction_id ,
to_char(p_transaction_id) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_cost_group_id ,
p_receiving_account_id ,
p_absorption_account_id ,
p_receiving_account_id ,
p_receiving_account_id ,
p_receiving_account_id ,
ln_lock_flag ,
ln_txn_interface_id
) ;
INSERT INTO mtl_txn_cost_det_interface (
transaction_interface_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
organization_id ,
cost_element_id ,
level_type ,
value_change
)
VALUES (
ln_txn_interface_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_organization_id ,
ln_material_cost_element_id ,
ln_level_type ,
p_value_change
);
INSERT INTO JAI_MTL_TXN_CST_DTL_T(
transaction_interface_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
organization_id ,
cost_element_id ,
level_type ,
value_change
)
VALUES (
ln_txn_interface_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_organization_id ,
ln_material_cost_element_id ,
ln_level_type ,
p_value_change
);
INSERT INTO mtl_txn_cost_det_interface
(
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
cost_element_id,
level_type,
value_change
)
(SELECT
ln_txn_interface_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_organization_id ,
clcd.cost_element_id ,
clcd.level_type ,
0
FROM
cst_layer_cost_details clcd,
cst_quantity_layers cql
WHERE
cql.organization_id = p_organization_id
and cql.inventory_item_id = p_item_id
and cql.cost_group_id = p_cost_group_id
and clcd.layer_id = cql.layer_id
and (clcd.cost_element_id,clcd.level_type) NOT IN
( SELECT
mctcd1.cost_element_id,
mctcd1.level_type
FROM
mtl_txn_cost_det_interface mctcd1
WHERE
mctcd1.transaction_interface_id = ln_txn_interface_id
)
);
INSERT INTO JAI_MTL_TXN_CST_DTL_T
(
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
cost_element_id,
level_type,
value_change
)
(SELECT
ln_txn_interface_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_organization_id ,
clcd.cost_element_id ,
clcd.level_type ,
0
FROM
cst_layer_cost_details clcd,
cst_quantity_layers cql
WHERE
cql.organization_id = p_organization_id
and cql.inventory_item_id = p_item_id
and cql.cost_group_id = p_cost_group_id
and clcd.layer_id = cql.layer_id
and (clcd.cost_element_id,clcd.level_type) NOT IN
( SELECT
mctcd1.cost_element_id,
mctcd1.level_type
FROM
jai_mtl_txn_cst_dtl_t mctcd1
WHERE
mctcd1.transaction_interface_id = ln_txn_interface_id
)
);
select receiving_account_id
from rcv_parameters
Where organization_id = cp_organization_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
(
lv_status , --'NEW',
ln_set_of_books_id,
lv_source_name,
lv_category_name,
trunc(sysdate),
lv_currency_code,
sysdate,
fnd_global.user_id,
'A',
p_cost_amount,
NULL,
sysdate,
ln_receiving_accnt_id,/*Inventory receiving Account*/
NULL,
NULL,
NULL,
lv_organization_code,
lv_reference_10,
lv_reference_entry,
lv_reference_23,
lv_reference_24,
p_transaction_id,
lv_reference_26,
to_char(p_organization_id)
);
insert into jai_mtl_trx_jrnls
(journal_entry_id,
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
entered_cr,
entered_dr,
transaction_date,
code_combination_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
reference1,
reference10,
reference23,
reference24,
reference25,
reference26,
reference27,
creation_Date,
last_updated_by,
last_update_date,
last_update_login,
transaction_temp_id
)
VALUES
(jai_mtl_trx_jrnls_s.nextval,
lv_status,
ln_set_of_books_id,
lv_source_name,
lv_category_name,
sysdate,
lv_currency_code,
sysdate,
fnd_global.user_id,
p_cost_amount,
NULL,
sysdate,
ln_receiving_accnt_id,
null,
null,
null,
lv_organization_code,
lv_reference_10,
lv_reference_23,
lv_reference_24,
p_transaction_id,
lv_reference_26,
to_char(p_organization_id),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_transaction_id
);
/*removed the insert to MTA by vkaranam for bug #6030615 as part of SLA uptake*/
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
(
lv_status , --'NEW',
ln_set_of_books_id,
lv_source_name,
lv_category_name,
trunc(sysdate),
lv_currency_code,
sysdate,
fnd_global.user_id,
'A',
NULL,
p_cost_amount,
sysdate,
p_reference_account,/*PPV Account*/
NULL,
NULL,
NULL,
lv_organization_code,
lv_reference_10,
lv_reference_entry,
lv_reference_23,
lv_reference_24,
p_transaction_id,
lv_reference_26,
to_char(p_organization_id)
);
insert into jai_mtl_trx_jrnls
(journal_entry_id,
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
entered_cr,
entered_dr,
transaction_date,
code_combination_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
reference1,
reference10,
reference23,
reference24,
reference25,
reference26,
reference27,
creation_Date,
last_updated_by,
last_update_date,
last_update_login,
transaction_temp_id
)
VALUES
(jai_mtl_trx_jrnls_s.nextval,
lv_status,
ln_set_of_books_id,
lv_source_name,
lv_category_name,
sysdate,
lv_currency_code,
sysdate,
fnd_global.user_id,
NULL,
p_cost_amount,
sysdate,
p_reference_account,
null,
null,
null,
lv_organization_code,
lv_reference_10,
lv_reference_23,
lv_reference_24,
p_transaction_id,
lv_reference_26,
to_char(p_organization_id),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_transaction_id
);
SELECT * from jai_rcv_lines
WHERE shipment_line_id = cp_shipment_line_id;
SELECT * from JAI_RCV_LINE_TAXES
WHERE shipment_line_id = cp_shipment_line_id;
SELECT * from JAI_RCV_TRANSACTIONS
WHERE transaction_id = cp_transaction_id ;
SELECT vendor_id , vendor_site_id , transaction_id , shipment_line_id from rcv_transactions
WHERE transaction_id = cp_transaction_id;
SELECT * from mtl_parameters
WHERE organization_id = cp_organization_id;
select *
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id ;
SELECT period_name
FROM gl_period_statuses
where set_of_books_id = cp_set_of_books_id
and sysdate between start_Date and end_Date
and application_id = 101;
jai_cmn_rg_23ac_ii_pkg.insert_row
(
P_REGISTER_ID => ln_register_id ,
P_INVENTORY_ITEM_ID => r_trx.inventory_item_id,
P_ORGANIZATION_ID => r_trx.organization_id,
P_RECEIPT_ID => r_Trx.transaction_id,
P_RECEIPT_DATE => r_Trx.transaction_date,
P_CR_BASIC_ED => ln_Excise_amt,
P_CR_ADDITIONAL_ED => ln_addl_excise_amt,
P_CR_ADDITIONAL_CVD => ln_add_cvd_amt,
P_CR_OTHER_ED => ln_oth_excise_amt,
P_DR_BASIC_ED => Null,
P_DR_ADDITIONAL_ED => null,
P_DR_ADDITIONAL_CVD => null,
P_DR_OTHER_ED => null,
P_EXCISE_INVOICE_NO => r_Trx.excise_invoice_no,
P_EXCISE_INVOICE_DATE => r_trx.excise_invoice_date,
P_REGISTER_TYPE => 'C',
P_REMARKS => 'AutoClaim of remaining 50% for CG in interorg XFER',
P_VENDOR_ID => r_base_Trx.vendor_id,
P_VENDOR_SITE_ID => r_base_trx.vendor_site_id,
P_CUSTOMER_ID => null,
P_CUSTOMER_SITE_ID => null,
P_LOCATION_ID => r_trx.location_id,
P_TRANSACTION_DATE => trunc(sysdate),
P_CHARGE_ACCOUNT_ID => null,
P_REGISTER_ID_PART_I => null,
P_REFERENCE_NUM => lv_reference_num ,
P_ROUNDING_ID => null,
P_OTHER_TAX_CREDIT => ln_Cess_amt+ ln_shCess_amt, --added + ln_shCess_amt for bug#10607819
P_OTHER_TAX_DEBIT => null,
P_TRANSACTION_TYPE => 'R',
P_TRANSACTION_SOURCE => 'INTERORG_XFER',
P_CALLED_FROM => 'INTERORG_XFER',
P_SIMULATE_FLAG => 'N',
P_PROCESS_STATUS => lv_process_status,
P_PROCESS_MESSAGE => lv_process_message
);
insert into JAI_RCV_JOURNAL_ENTRIES
(
JOURNAL_ENTRY_ID,
ORGANIZATION_CODE,
RECEIPT_NUM,
TRANSACTION_ID ,
CREATION_DATE ,
TRANSACTION_DATE ,
SHIPMENT_LINE_ID ,
ACCT_TYPE ,
ACCT_NATURE ,
SOURCE_NAME ,
CATEGORY_NAME ,
CODE_COMBINATION_ID ,
ENTERED_DR ,
ENTERED_CR ,
TRANSACTION_TYPE ,
PERIOD_NAME ,
CREATED_BY ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
REFERENCE_ID ,
REFERENCE_NAME ,
last_update_date ,/*6497301*/
last_updated_by ,/*6497301*/
last_update_login /*6497301*/
)
VALUES
(jai_rcv_journal_entries_s.nextval,/*6497301*/
r_mtl_params.organization_code ,
r_trx.receipt_num ,
r_trx.transaction_id ,
sysdate ,
sysdate ,
r_Trx.shipment_line_id,
'REGULAR',
'CENVAT-AUTOCLAIM-INTERORG-XFER',
'Purchasing',
'Receiving India',
-- r_cenvat_accts.excise_rcvble_account, --12807591
r_cenvat_accts.cenvat_rcvble_account, --12807591
Null, -- 10607819
ln_tot_cenvat_amt, --10607819
'Receive',
lv_period_name,
ln_created_by,
'INR',
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate, /*6497301*/
fnd_global.user_id,/*6497301*/
fnd_global.login_id/*6497301*/
);
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,
'Purchasing',
'Receiving India',
trunc(sysdate),
'INR',
sysdate,
ln_created_by,
'A',
ln_tot_cenvat_amt,
NULL,
sysdate,
-- r_cenvat_accts.excise_rcvble_account, --12807591
r_cenvat_accts.cenvat_rcvble_account, --12807591
Null,
NULL,
NULL,
r_mtl_params.organization_code,
lv_reference_10,
'India Localization Entry',
'jai_mtl_trxs_pkg.auto_claim',
'rcv_transactions',
'rcv_Transaction_id',
r_Trx.transaction_id,
r_trx.organization_id
);
INSERT INTO JAI_RCV_JOURNAL_ENTRIES
(
JOURNAL_ENTRY_ID ,
ORGANIZATION_CODE ,
RECEIPT_NUM ,
TRANSACTION_ID ,
CREATION_DATE ,
TRANSACTION_DATE ,
SHIPMENT_LINE_ID ,
ACCT_TYPE ,
ACCT_NATURE ,
SOURCE_NAME ,
CATEGORY_NAME ,
CODE_COMBINATION_ID ,
ENTERED_DR ,
ENTERED_CR ,
TRANSACTION_TYPE ,
PERIOD_NAME ,
CREATED_BY ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
REFERENCE_ID ,
REFERENCE_NAME ,
last_update_date ,/*6497301*/
last_updated_by ,/*6497301*/
last_update_login /*6497301*/
)
VALUES
(jai_rcv_journal_entries_s.nextval,
r_mtl_params.organization_code ,
r_trx.receipt_num ,
r_trx.transaction_id ,
sysdate ,
sysdate ,
r_Trx.shipment_line_id,
'REGULAR',
'CENVAT-AUTOCLAIM-INTERORG-XFER',
'Purchasing',
'Receiving India',
r_cenvat_accts.modvat_cg_account_id ,
ln_tot_cenvat_amt,-- 10607819
NULL, -- 10607819
'Receive',
lv_period_name,
ln_created_by,
'INR',
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate, /*6497301*/
fnd_global.user_id,/*6497301*/
fnd_global.login_id/*6497301*/
);
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,
'Purchasing',
'Receiving India',
trunc(sysdate),
'INR',
sysdate,
ln_created_by,
'A',
NULL,
ln_tot_cenvat_amt,
sysdate,
r_cenvat_accts.modvat_cg_account_id,
Null,
NULL,
NULL,
r_mtl_params.organization_code,
lv_reference_10,
'India Localization Entry',
'jai_mtl_trxs_pkg.auto_claim',
'rcv_transactions',
'rcv_Transaction_id',
r_Trx.transaction_id,
r_trx.organization_id
);
jai_cmn_rg_others_pkg.insert_row
(
P_SOURCE_TYPE => 1,
P_SOURCE_NAME => 'RG23C_P2',
P_SOURCE_ID => ln_register_id,
P_TAX_TYPE => 'CVD_EDUCATION_CESS',
DEBIT_AMT => null,
CREDIT_AMT => ln_cvd_cess_amt * ln_factor,
P_PROCESS_FLAG => lv_process_status,
P_PROCESS_MSG => lv_process_message
);
jai_cmn_rg_others_pkg.insert_row
(
P_SOURCE_TYPE => 1,
P_SOURCE_NAME => 'RG23C_P2',
P_SOURCE_ID => ln_register_id,
P_TAX_TYPE => 'EXCISE_EDUCATION_CESS',
DEBIT_AMT => null,
CREDIT_AMT => ln_excise_cess_amt * ln_factor,
P_PROCESS_FLAG => lv_process_status,
P_PROCESS_MSG => lv_process_message
);
jai_cmn_rg_others_pkg.insert_row
(
P_SOURCE_TYPE => 1,
P_SOURCE_NAME => 'RG23C_P2',
P_SOURCE_ID => ln_register_id,
P_TAX_TYPE => 'CVD_SH_EDU_CESS',
DEBIT_AMT => null,
CREDIT_AMT => ln_cvd_sh_cess_amt * ln_factor,
P_PROCESS_FLAG => lv_process_status,
P_PROCESS_MSG => lv_process_message
);
jai_cmn_rg_others_pkg.insert_row
(
P_SOURCE_TYPE => 1,
P_SOURCE_NAME => 'RG23C_P2',
P_SOURCE_ID => ln_register_id,
P_TAX_TYPE => 'EXCISE_SH_EDU_CESS',
DEBIT_AMT => null,
CREDIT_AMT => ln_excise_sh_cess_amt * ln_factor,
P_PROCESS_FLAG => lv_process_status,
P_PROCESS_MSG => lv_process_message
);
INSERT INTO JAI_RCV_JOURNAL_ENTRIES
(
JOURNAL_ENTRY_ID,
ORGANIZATION_CODE ,
RECEIPT_NUM ,
TRANSACTION_ID ,
CREATION_DATE ,
TRANSACTION_DATE ,
SHIPMENT_LINE_ID ,
ACCT_TYPE ,
ACCT_NATURE ,
SOURCE_NAME ,
CATEGORY_NAME ,
CODE_COMBINATION_ID ,
ENTERED_DR ,
ENTERED_CR ,
TRANSACTION_TYPE ,
PERIOD_NAME ,
CREATED_BY ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
CURRENCY_CONVERSION_RATE ,
REFERENCE_ID ,
REFERENCE_NAME ,
last_update_date ,/*6497301*/
last_updated_by ,/*6497301*/
last_update_login /*6497301*/
)
VALUES
(jai_rcv_journal_entries_s.nextval,/*6497301*/
r_mtl_params.organization_code ,
r_trx.receipt_num ,
r_trx.transaction_id ,
sysdate ,
sysdate ,
r_Trx.shipment_line_id,
'REGULAR',
'CENVAT-AUTOCLAIM-INTERORG-XFER',
'Purchasing',
'Receiving India',
r_cenvat_accts.excise_edu_cess_rcvble_accnt,
Null,--10607819
ln_Cess_amt,--10607819
'Receive',
lv_period_name,
ln_created_by,
'INR',
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate, /*6497301*/
fnd_global.user_id,/*6497301*/
fnd_global.login_id/*6497301*/
);
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,
'Purchasing',
'Receiving India',
trunc(sysdate),
'INR',
sysdate,
ln_created_by,
'A',
ln_cess_amt,--10607819
NULL,--10607819
sysdate,
r_cenvat_accts.excise_edu_cess_rcvble_accnt,
Null,
NULL,
NULL,
r_mtl_params.organization_code,
lv_reference_10,
'India Localization Entry',
'jai_mtl_trxs_pkg.auto_claim',
'rcv_transactions',
'rcv_Transaction_id',
r_Trx.transaction_id,
r_trx.organization_id
);
INSERT INTO JAI_RCV_JOURNAL_ENTRIES
(
JOURNAL_ENTRY_ID,
ORGANIZATION_CODE ,
RECEIPT_NUM ,
TRANSACTION_ID ,
CREATION_DATE ,
TRANSACTION_DATE ,
SHIPMENT_LINE_ID ,
ACCT_TYPE ,
ACCT_NATURE ,
SOURCE_NAME ,
CATEGORY_NAME ,
CODE_COMBINATION_ID ,
ENTERED_DR ,
ENTERED_CR ,
TRANSACTION_TYPE ,
PERIOD_NAME ,
CREATED_BY ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
CURRENCY_CONVERSION_RATE ,
REFERENCE_ID ,
REFERENCE_NAME ,
last_update_date ,/*6497301*/
last_updated_by ,/*6497301*/
last_update_login /*6497301*/
)
VALUES
(
jai_rcv_journal_entries_s.nextval,/*6497301*/
r_mtl_params.organization_code ,
r_trx.receipt_num,
r_trx.transaction_id ,
sysdate ,
sysdate ,
r_Trx.shipment_line_id,
'REGULAR',
'CENVAT-AUTOCLAIM-INTERORG-XFER',
'Purchasing',
'Receiving India',
r_cenvat_accts.excise_edu_cess_cg_account,
ln_Cess_amt,--10607819
NULL,--10607819
'Receive',
lv_period_name,
ln_created_by,
'INR',
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate, /*6497301*/
fnd_global.user_id,/*6497301*/
fnd_global.login_id/*6497301*/
);
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,
'Purchasing',
'Receiving India',
trunc(sysdate),
'INR',
sysdate,
ln_created_by,
'A',
NULL,
ln_cess_amt,
sysdate,
r_cenvat_accts.excise_edu_cess_cg_account,
Null,
NULL,
NULL,
r_mtl_params.organization_code,
lv_reference_10,
'India Localization Entry',
'jai_mtl_trxs_pkg.auto_claim',
'rcv_transactions',
'rcv_Transaction_id',
r_Trx.transaction_id,
r_trx.organization_id
);
INSERT INTO JAI_RCV_JOURNAL_ENTRIES
(
JOURNAL_ENTRY_ID,
ORGANIZATION_CODE ,
RECEIPT_NUM ,
TRANSACTION_ID ,
CREATION_DATE ,
TRANSACTION_DATE ,
SHIPMENT_LINE_ID ,
ACCT_TYPE ,
ACCT_NATURE ,
SOURCE_NAME ,
CATEGORY_NAME ,
CODE_COMBINATION_ID ,
ENTERED_DR ,
ENTERED_CR ,
TRANSACTION_TYPE ,
PERIOD_NAME ,
CREATED_BY ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
CURRENCY_CONVERSION_RATE ,
REFERENCE_ID ,
REFERENCE_NAME ,
last_update_date ,/*6497301*/
last_updated_by ,/*6497301*/
last_update_login /*6497301*/
)
VALUES
(jai_rcv_journal_entries_s.nextval,/*6497301*/
r_mtl_params.organization_code ,
r_trx.receipt_num ,
r_trx.transaction_id ,
sysdate ,
sysdate ,
r_Trx.shipment_line_id,
'REGULAR',
'CENVAT-AUTOCLAIM-INTERORG-XFER',
'Purchasing',
'Receiving India',
r_cenvat_accts.sh_cess_rcvble_acct_id,
Null,
ln_shCess_amt,
'Receive',
lv_period_name,
ln_created_by,
'INR',
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate, /*6497301*/
fnd_global.user_id,/*6497301*/
fnd_global.login_id/*6497301*/
);
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,
'Purchasing',
'Receiving India',
trunc(sysdate),
'INR',
sysdate,
ln_created_by,
'A',
ln_shCess_amt,
NULL,
sysdate,
r_cenvat_accts.sh_cess_rcvble_acct_id,
Null,
NULL,
NULL,
r_mtl_params.organization_code,
lv_reference_10,
'India Localization Entry',
'jai_mtl_trxs_pkg.auto_claim',
'rcv_transactions',
'rcv_Transaction_id',
r_Trx.transaction_id,
r_trx.organization_id
);
INSERT INTO JAI_RCV_JOURNAL_ENTRIES
(
JOURNAL_ENTRY_ID,
ORGANIZATION_CODE ,
RECEIPT_NUM ,
TRANSACTION_ID ,
CREATION_DATE ,
TRANSACTION_DATE ,
SHIPMENT_LINE_ID ,
ACCT_TYPE ,
ACCT_NATURE ,
SOURCE_NAME ,
CATEGORY_NAME ,
CODE_COMBINATION_ID ,
ENTERED_DR ,
ENTERED_CR ,
TRANSACTION_TYPE ,
PERIOD_NAME ,
CREATED_BY ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
CURRENCY_CONVERSION_RATE ,
REFERENCE_ID ,
REFERENCE_NAME ,
last_update_date ,/*6497301*/
last_updated_by ,/*6497301*/
last_update_login /*6497301*/
)
VALUES
(
jai_rcv_journal_entries_s.nextval,/*6497301*/
r_mtl_params.organization_code ,
r_trx.receipt_num,
r_trx.transaction_id ,
sysdate ,
sysdate ,
r_Trx.shipment_line_id,
'REGULAR',
'CENVAT-AUTOCLAIM-INTERORG-XFER',
'Purchasing',
'Receiving India',
r_cenvat_accts.sh_cess_cg_account_id,
ln_shCess_amt,
NULL,
'Receive',
lv_period_name,
ln_created_by,
'INR',
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate, /*6497301*/
fnd_global.user_id,/*6497301*/
fnd_global.login_id/*6497301*/
);
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,
'Purchasing',
'Receiving India',
trunc(sysdate),
'INR',
sysdate,
ln_created_by,
'A',
NULL,
ln_shCess_amt,
sysdate,
r_cenvat_accts.sh_cess_cg_account_id,
Null,
NULL,
NULL,
r_mtl_params.organization_code,
lv_reference_10,
'India Localization Entry',
'jai_mtl_trxs_pkg.auto_claim',
'rcv_transactions',
'rcv_Transaction_id',
r_Trx.transaction_id,
r_trx.organization_id
);
SELECT regime_id
FROM jai_rgm_definitions
WHERE regime_code = 'VAT';
stmt_name:='Calling insert vat repository entry';
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => ln_regime_id,
pv_tax_type => p_tax_type,
pv_organization_type => jai_constants.orgn_type_io,
pn_organization_id => p_organization_id,
pn_location_id => p_location_id,
pv_source => jai_constants.source_rcv,
pv_source_trx_type => lv_source_trx_type,
pv_source_table_name => table_rcv_transactions,
pn_source_id => p_transaction_id,
pd_transaction_date => trunc(sysdate),
pv_account_name => lv_account_name,
pn_charge_account_id => ln_code_combination_id,
pn_balancing_account_id => ln_interim_recovery_account,
pn_credit_amount =>ln_entered_cr ,
pn_debit_amount =>ln_entered_dr ,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => p_claim_schedule_id,/*r_claim_schedule.claim_schedule_id,*/
pn_batch_id => NULL,
pn_inv_organization_id => P_organization_id,
pv_invoice_no => p_vat_invoice_no,
pd_invoice_date => trunc(sysdate),
pv_called_from => 'JAINVMTX',
pv_process_flag => lv_process_status,
pv_process_message => lv_process_message,
--Added by Bo Li for bug9305067 2010-4-14 BEGIN
--------------------------------------------------
pv_trx_reference_context => NULL,
pv_trx_reference1 => NULL,
pv_trx_reference2 => NULL,
pv_trx_reference3 => NULL,
pv_trx_reference4 => NULL,
pv_trx_reference5 => NULL
--------------------------------------------------
--Added by Bo Li for bug9305067 2010-4-14 END
);
SELECT shipment_header_id, shipment_line_id
FROM jai_rcv_lines
WHERE shipment_header_id = NVL(cp_shipment_header_id, shipment_header_id)
AND shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id)
AND receipt_num=p_receipt_num
and organization_id=p_organization_id
and inventory_item_id=p_inventory_item_id
ORDER BY shipment_line_id;
SELECT transaction_id,
transaction_type,
transaction_date,
tax_transaction_id,
parent_transaction_type,
currency_conversion_rate,
quantity
FROM JAI_RCV_TRANSACTIONS
WHERE shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id)
AND transaction_id = NVL(cp_transaction_id, transaction_id)
and organization_id=p_organization_id
and inventory_item_id=p_inventory_item_id
AND (
transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
OR
( transaction_type = 'CORRECT'
AND parent_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
)
)
ORDER BY transaction_id;
SELECT DECODE(a.currency, jai_constants.func_curr, a.tax_amount, a.tax_amount*cp_currency_conversion_rate) tax_amount, --Removed Round condition by Bgowrava for Bug#8414075
a.tax_type,
a.tax_id,
NVL(b.rounding_factor,0) rounding_factor,
c.qty_received
FROM JAI_RCV_LINE_TAXES a,
jai_cmn_taxes_all b,
jai_rcv_lines c
WHERE a.shipment_line_id =c.shipment_line_id
AND a.shipment_line_id = cp_shipment_line_id
AND a.tax_type IN ( select tax_type
from jai_regime_tax_types_v
where regime_code = jai_constants.vat_regime
)
AND a.tax_id = b.tax_id
AND a.modvat_flag = 'Y'
AND NVL(a.tax_amount,0) <> 0;
SELECT term_id, rcv_rgm_line_id, receipt_date
FROM jai_rcv_rgm_lines
WHERE shipment_line_id = cp_shipment_line_id
AND receipt_num=p_receipt_num
and organization_id=p_organization_id
and inventory_item_id=p_inventory_item_id
AND regime_code=jai_constants.vat_regime;
SELECT SUM(installment_amount) total_installment_amount, MAX(installment_no) max_installment_no
FROM jai_rgm_trm_schedules_t
WHERE schedule_id = cp_schedule_id;
SELECT COUNT(*) count
FROM jai_rcv_rgm_claims
WHERE rcv_rgm_line_id = cp_rcv_rgm_line_id
AND transaction_id = cp_transaction_id
AND tax_id = cp_tax_id
AND installment_no IN ( SELECT installment_no
FROM jai_rgm_trm_schedules_t
WHERE schedule_id = cp_schedule_id);
SELECT COUNT(*) count
FROM jai_rcv_rgm_claims
WHERE rcv_rgm_line_id = cp_rcv_rgm_line_id
AND transaction_id = cp_transaction_id
AND tax_id = cp_tax_id
AND installment_amount <> 0
and status='Y';
select * from jai_rgm_trm_schedules_t
where schedule_id=cp_schedule_id
order by installment_no;
DELETE jai_rgm_trm_schedules_t
WHERE schedule_id = ln_schedule_id;
UPDATE jai_rgm_trm_schedules_t
SET installment_amount = ROUND(installment_amount, tax_rec.rounding_factor)
WHERE schedule_id = ln_schedule_id;
UPDATE jai_rgm_trm_schedules_t
SET installment_amount = installment_amount + ln_amount - r_sum_schedules.total_installment_amount
WHERE installment_no = r_sum_schedules.max_installment_no
AND schedule_id = ln_schedule_id;
UPDATE jai_rgm_trm_schedules_t
SET installment_amount = installment_amount * (-1) /*This is to reduce the quantity available for claim*/
WHERE schedule_id = ln_schedule_id;
select jai_rcv_rgm_claims_s.NEXTVAL into ln_claim_schedule_id from dual;
INSERT
INTO jai_rcv_rgm_claims
(
CLAIM_SCHEDULE_ID,
RCV_RGM_LINE_ID,
Shipment_header_id,
Shipment_line_id,
Regime_code,
Tax_transaction_id,
Transaction_type,
Transaction_id,
Parent_transaction_type,
Installment_no,
Installment_amount,
Claimed_amount,
Scheduled_date,
claimed_date,
Status,
Manual_claim_flag,
Remarks,
Tax_type,
Tax_id,
Trx_tax_id,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)values
(ln_claim_schedule_id,
r_term.rcv_rgm_line_id,
rec_lines.shipment_header_id,
rec_lines.shipment_line_id,
jai_constants.vat_regime,
rec_txns.tax_transaction_id,
rec_txns.transaction_type,
rec_txns.transaction_id,
rec_txns.parent_transaction_type,
sch_det.installment_no,
sch_det.installment_amount,
NULL,
sch_det.installment_date,
NULL,
'N',
NULL,
NULL,
tax_rec.tax_type,
tax_rec.tax_id,
NULL,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id);
update jai_rcv_rgm_claims
set claimed_amount=0,
claimed_date=sysdate,
status='Y',
installment_amount=0
where claim_schedule_id=ln_claim_schedule_id;
update jai_rcv_rgm_lines
set recoverable_amount = nvl(recoverable_amount,0) + nvl(ln_debit_amt,0)
where rcv_rgm_line_id = r_term.rcv_rgm_line_id;
DELETE jai_rgm_trm_schedules_t
WHERE schedule_id = ln_schedule_id;
SELECT REPOSITORY_ID,
REGIME_CODE,
TAX_TYPE,
SOURCE_DOCUMENT_ID,
DEBIT_AMOUNT,
CREDIT_AMOUNT,
ORGANIZATION_ID,
LOCATION_ID
FROM jai_rgm_trx_records
WHERE repository_id = p_repository_id;
SELECT regime_id
FROM jai_rgm_definitions
WHERE regime_code = cp_regime_code;
PROCEDURE delete_trx(p_transaction_header_id IN NUMBER,
p_transaction_temp_id IN NUMBER)
IS
BEGIN
DELETE jai_mtl_match_receipts
WHERE transaction_temp_id in
(
SELECT transaction_temp_id
FROM jai_mtl_trxs
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
AND transaction_commit_date is null
);
DELETE jai_cmn_document_taxes
WHERE source_doc_type = 'INTERORG_XFER'
and source_table_name = 'MTL_MATERIAL_TRANSACTIONS_TEMP'
AND source_doc_line_id IN
(
SELECT transaction_temp_id
FROM jai_mtl_trxs
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
AND transaction_commit_date is null
);
DELETE JAI_CMN_MATCH_TAXES
WHERE ref_line_id IN
(
SELECT transaction_temp_id
FROM jai_mtl_trxs
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
AND transaction_commit_date is null
)
AND order_invoice='X';
DELETE JAI_CMN_MATCH_RECEIPTS
WHERE ref_line_id IN
(
SELECT transaction_temp_id
FROM jai_mtl_trxs
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
AND transaction_commit_date is null
)
AND order_invoice='X';
DELETE jai_mtl_trxs
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
AND transaction_commit_date is null;
END delete_trx;
SELECT su.site_use_id,cas.cust_account_id
FROM
hz_cust_acct_sites_all cas,
hz_cust_site_uses_all su,
po_location_associations_all pla,
hr_locations hrl
WHERE cas.cust_acct_site_id = su.cust_acct_site_id
AND su.site_use_id = pla.site_use_id(+)
AND pla.location_id = hrl.location_id(+)
AND su.site_use_code = 'SHIP_TO'
AND RTRIM(Ltrim(hrl.location_code)) = LTRIM(Rtrim(p_to_location_code))
AND hrl.inventory_organization_id = p_to_organization_id ; -- bug 6444945
SELECT to_subinventory ,
inventory_item_id ,
quantity ,
transaction_uom
FROM jai_mtl_trxs
WHERE transaction_header_id = p_header_id
AND transaction_temp_id = p_line_id;
SELECT Location_id
FROM JAI_INV_SUBINV_DTLS
WHERE Sub_Inventory_Name = p_subinventory_code
AND organization_id = p_organization_id; */
||If any match information is present this would also be deleted
*/
IF r_jai_mtl_trxs.quantity <> p_line_quantity THEN
IF r_jai_mtl_trxs.inventory_item_id = p_inventory_item_id AND
r_jai_mtl_trxs.transaction_uom = p_uom_code AND
r_jai_mtl_trxs.to_subinventory = p_transfer_subinventory THEN
UPDATE jai_cmn_document_taxes
SET tax_amt = ( p_line_quantity / r_jai_mtl_trxs.quantity ) * tax_amt,
FUNC_TAX_AMT = ( p_line_quantity / r_jai_mtl_trxs.quantity ) * func_tax_amt,
last_update_date = sysdate,
last_updated_by = ln_user_id
WHERE source_doc_id = p_header_id
AND source_doc_line_id = p_line_id;
UPDATE jai_mtl_trxs
SET quantity = p_line_quantity,
last_update_date = sysdate,
last_updated_by = ln_user_id
WHERE transaction_header_id = p_header_id
AND transaction_temp_id = p_line_id;
DELETE JAI_CMN_MATCH_RECEIPTS
WHERE ref_line_id = p_line_id
AND order_invoice = 'X';
DELETE jai_mtl_match_receipts
WHERE transaction_temp_id = p_line_id ;
DELETE JAI_CMN_MATCH_TAXES
WHERE ref_line_id = p_line_id
AND order_invoice = 'X' ;
/* following deletes all the IL data sofar saved as the taxes are going to redefault */
delete_trx(
p_transaction_header_id => p_header_id,
p_transaction_temp_id => p_line_id
);
INSERT INTO jai_mtl_trxs(
transaction_id,
transaction_header_id ,
transaction_temp_id ,
transaction_type_id ,
from_organization ,
to_organization ,
inventory_item_id ,
from_subinventory ,
to_subinventory ,
location_id ,
quantity ,
taxcategory_id ,
selling_price ,
assessable_value ,
vat_assessable_value ,
--gst_assessable_value , -- Added by Jia for GST Bug#10043656 on 2010/09/10 /*Commented by mmurtuza for removing GST Changes*/
transaction_date,
transaction_uom ,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
jai_mtl_trxs_s.nextval,
p_header_id, -- :mtl_trx_line.transaction_header_id,
p_line_id, -- :mtl_trx_line.transaction_temp_id,
p_transaction_type_id, -- :mtl_trx_line.transaction_type_id,
p_organization_id, -- :mtl_trx_line.organization_id,
p_to_organization_id, -- :org.to_org_id,
p_inventory_item_id, -- :mtl_trx_line.inventory_item_id,
p_subinventory_code, -- :mtl_trx_line.subinventory_code,
p_transfer_subinventory, -- :mtl_trx_line.transfer_subinventory,
p_toorg_location_id, -- :mtl_trx_line.toorg_location_id
p_line_quantity, -- :mtl_trx_line.transaction_quantity
ln_tax_category_id, -- :ja_in_tax.tax_category_id,
p_item_cost, -- :mtl_trx_line.item_cost,
ln_assessable_value, -- v_assessable_value,
ln_vat_assessable_value, -- :mtl_trx_line.vat_assessable_value,
--ln_gst_assessable_value, -- :Mtl_trx_line.gst_assessable_value. Added by Jia for GST Bug#10043656 on 2010/09/10 /*Commented by mmurtuza for removing GST Changes*/
sysdate, -- p_date_order
p_uom_code, -- :mtl_trx_line.transaction_uom,
sysdate,
ln_user_id,
sysdate,
ln_user_id,
fnd_global.login_id
);
p_last_update_date => sysdate ,
p_last_updated_by => ln_user_id,
p_last_update_login => fnd_global.login_id ,
p_operation_flag => NULL,
p_vat_assessable_value => ln_vat_assessable_value
--, pn_gst_assessable_value => ln_gst_assessable_value -- Added by Jia for GST Bug#10043656 on 2010/09/10 /*Commented by mmurtuza for removing GST Changes*/
) ;
PROCEDURE delete_trx_autonomous(p_transaction_header_id IN NUMBER,
p_transaction_temp_id IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
delete_trx(p_transaction_header_id,p_transaction_temp_id) ;
END delete_trx_autonomous;
delete_trx_autonomous is giving the deadlock if any error occurs
during the forms commit processing*
sync_with_base_trx will be called from key-exit,key-clrrec,key-clrblk,when-window-closed,key-clrform,
hence added the commit stmt such that the delete_trx stmts will get commited.*
IF p_event <> 'KEY-COMMIT' THEN
delete_trx_autonomous(p_transaction_header_id ,
p_transaction_temp_id);
delete_trx(
p_transaction_header_id ,
p_transaction_temp_id
);