[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
jai_cmn_rg_23d_trxs_pkg.insert_row
27-Jul-2005 4516678 Added by Lakshmi Gopalsami Version 120.3
Issue :
a.Whenever a user creates a receipt for a CGIN or
CGEX item, 50% cenvat is claimed. If he/she intends to
return the entire quantity in the receipt, he/she must
claim the remaining 50% cenvat first and then do the
RTV. Else, the system should throw an error.
b.After creating a receipt for a CGIN or CGEX item,
if the user does a partial RTV on that receipt,
the system should allow it although the remaining
50% CENVAT has not been claimed.
Fix :
a. Added code to check this in Package jai_rcv_tax_pkg
(1) Created new procedure pick_register_type to get the
register_type depending on the item_class
(2)Created two new cursors c_fetch_receive_quantity
and c_fetch_transaction_Quantity to get the
quantity received for the receipt and RTV transactions
(3) Added nvl(cenvat_amount,0) in
cursor c_fetch_unclaim_cenvat
Dependencies(Functional)
------------------------
jai_rcv_tax.plb Version 120.2
b. The cenvat receivable accounts were not getting passed
in case of a CGIN/CGEX item.
Thus, the system is throwing an error.
Commented the generic assignment for cenvat
accounting entries and added the condition for
CGIN and CGEX item class in procedure
accounting_entries.
02-May-06 5176133 Added by rallamse version 120.4
Issue : Object not getting compiled
Fix:
Modified the multi-line statement :
r_diff_tax.basic_excise := r_rtv_dtls.excise_basis_amt
to a single line statement
17-Jul-2006 5378630 Aiyer, File Version 120.6
Issue:-
India Receiving transaction processor fails during validation phase for RMA
type of transactions.
Fix:-
During DFF elimination one elsif statment in the procedure validate_transaction got missed out
due to which for RMA TYPE of either "PRODUCTION INPUT" or "GOODS RETURN" the code used to fail.
To restrict this added back the if condition to check that the failure should happen only for PO receipt with FGIN or FGEX type
of item class. The new IF condition now checks for source_document_type in PO or REQUISITION
Also converted the reference of RMA TYPE "FG RETURN" into "GOODS RETURN" as FG return is not as per the abbreviation
standard
14-feb-2007 4704957,5841749 vkaranam,File version 120.11
4704957-Forward porting the changes in 11i bug 4683156(return to vendor report shows 50% cenvat amount for partial rtv)
5841749-Forward porting the changes in 11i bug 5647216(doing rtv for partial quantity, after claiming 50%, is giving error)
22-Feb-2007 5155138 srjayara, file version 120.12
Forward port for 11i bug 5110511
Issue: No accounting should be passed for Excise and Cess in case of RMA receipt for a Trading
organization.
Fix: When call to accounting_entries is made for Trading organization a check is added so that
the call is made only if the attribute category is not India RMA Receipt.
16-APR-2007 Bug 5989740 Vkaranam for bug 5989740, File version 120.13
Forward porting the changes in 115 bug 5907436(Enh:Handling Secondary And Higher Education Cess)
17 14-may-07 kunkumar made changes for Budget and ST by IO and Build issues resolved .
04-Jun-2007 ssawant for bug#6084771,File Version 120.18
Issue: UNABLE TO CLAIM CENVAT
Fix: Added reference_id while jai_rcv_accounting_pkg.process_transaction to avoid
the Duplicate accounting error.
08-Jun-2007 CSahoo for bug#6078460, File Version 120.20
Issue:Excise Expense entry is getting generated for Excise and Education cess but
not for the SHE cess. The accounting entrty needs to be generated for the
same.
Fix: Added code for SH Education Cess Acccounting Entries in the procedure rtv_processing_for_ssi.
9-Nov-2008 Bug 5752026 (FP for bug 5747435) File version 120.25
Issue : Total duty amount includes addl. CVD for the invoices flown from receipts.
This is not consistent with what we have for manual entries.
Fix : Excluded the addl. CVD when calculating the total duty amount in procedure
rg23_d_entry.
--------------------------------------------------------------------------------------*/
FUNCTION get_apportioned_tax(
pr_tax IN TAX_BREAKUP,
p_factor IN NUMBER,
p_claim_type IN VARCHAR2 -- Date 30/10/2006 Bug 5228046 added by SACSETHI
) RETURN TAX_BREAKUP IS
r_tax TAX_BREAKUP;
SELECT vendor_changed_flag, vendor_id, vendor_site_id
FROM JAI_RCV_CENVAT_CLAIMS
WHERE transaction_id = cp_transaction_id;
UPDATE JAI_RCV_CENVAT_CLAIMS
SET cenvat_claimed_ptg = p_cenvat_claimed_ptg,
cenvat_sequence = nvl(cenvat_sequence, 0) + 1,
cenvat_claimed_amt = nvl(cenvat_claimed_amt, 0) + p_cenvat_claimed_amt,
other_cenvat_claimed_amt = nvl(other_cenvat_claimed_amt,0) + p_other_cenvat_claimed_amt,
quantity_for_2nd_claim = nvl(quantity_for_2nd_claim,0) + nvl(ln_trx_qty_for_2nd_claim, 0),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE transaction_id = p_transaction_id;
update JAI_RCV_LINES
set claim_modvat_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where transaction_id = p_transaction_id;
UPDATE JAI_RCV_CENVAT_CLAIMS
SET quantity_for_2nd_claim = nvl(quantity_for_2nd_claim,0) + ln_trx_qty_for_2nd_claim,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE transaction_id = r_trx.tax_transaction_id;
PROCEDURE update_RTV_Diff_value
(pr_base_trx IN jai_rcv_excise_processing_pkg.c_base_trx%ROWTYPE,
pr_tax IN jai_rcv_excise_processing_pkg.c_trx%ROWTYPE,
pr_diff_tax IN TAX_BREAKUP,
p_source_reg IN VARCHAR2 ,
p_register_entry_type IN VARCHAR2 ,
p_register_id IN OUT NOCOPY NUMBER,
p_simulate_flag IN VARCHAR2,
p_codepath IN OUT NOCOPY VARCHAR2,
p_process_status OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2
) IS
ln_tr_amount NUMBER ;
'jai_rcv_excise_processing_pkg.update_RTV_Diff_value', 'START');
fnd_file.put_line(FND_FILE.LOG, ' Inside Update RTV Diff value ->statement id '
|| lv_statement_id);
(SELECT register_id ,
cr_basic_ed,
cr_additional_ed,
cr_other_ed,
dr_basic_ed,
dr_additional_ed,
dr_other_ed,
other_tax_credit,
other_tax_debit,
remarks
FROM jai_cmn_rg_pla_trxs
WHERE organization_id = pr_tax.organization_id
AND location_id = pr_tax.location_id
AND inventory_item_id = pr_tax.inventory_item_id
AND ref_document_id = pr_tax.transaction_id
AND transaction_source_num = ln_transaction_id
)
LOOP
lv_statement_id := '4';
/* Calculate the transaction amount difference to be updated */
ln_tr_amount := nvl(ln_reg_cr,0) - nvl(ln_reg_dr,0);
fnd_file.put_line(FND_FILE.LOG, 'Opening balance to be updated ' || ln_opening_balance);
/* Update PLA with the latest difference amount to the existing amount */
JAI_CMN_RG_PLA_TRXS_PKG.update_row(
P_REGISTER_ID => ln_register_id,
P_CR_BASIC_ED => ln_cr_basic,
P_CR_ADDITIONAL_ED => ln_cr_addl,
P_CR_OTHER_ED => ln_cr_other,
P_DR_BASIC_ED => ln_dr_basic,
P_DR_ADDITIONAL_ED => ln_dr_addl,
P_DR_OTHER_ED => ln_dr_other,
P_REMARKS => lv_remarks,
P_OPENING_BALANCE => ln_opening_balance,
P_CLOSING_BALANCE => ln_closing_balance,
P_OTHER_TAX_CREDIT => ln_other_tax_credit,
P_OTHER_TAX_DEBIT => ln_other_tax_debit
);
/* Update the balances with the latest amount*/
jai_cmn_rg_balances_pkg.update_row(
p_organization_id => pr_tax.organization_id,
p_location_id => pr_tax.location_id,
p_register_type => 'PLA',
p_amount_to_be_added=> ln_tr_amount,
p_simulate_flag => p_simulate_flag,
p_process_status => lv_process_status,
p_process_message => lv_process_message
);
(SELECT register_id ,
cr_basic_ed,
cr_additional_ed,
cr_other_ed,
dr_basic_ed,
dr_additional_ed,
dr_other_ed,
other_tax_credit,
other_tax_debit,
remarks
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = pr_tax.organization_id
AND location_id = pr_tax.location_id
AND inventory_item_id = pr_tax.inventory_item_id
AND receipt_ref = pr_tax.transaction_id
AND transaction_source_num = ln_transaction_id
)
LOOP
lv_statement_id := '4';
/* Calculate the transaction amount difference to be updated */
ln_tr_amount := nvl(ln_reg_cr,0) - nvl(ln_reg_dr,0);
fnd_file.put_line(FND_FILE.LOG, 'Opening balance to be updated ' || ln_opening_balance);
/* Update RG 23 Part II with the latest difference amount to the existing amount */
jai_cmn_rg_23ac_ii_pkg.update_row(
P_REGISTER_ID => ln_register_id,
P_CR_BASIC_ED => ln_cr_basic,
P_CR_ADDITIONAL_ED => ln_cr_addl,
P_CR_OTHER_ED => ln_cr_other,
P_DR_BASIC_ED => ln_dr_basic,
P_DR_ADDITIONAL_ED => ln_dr_addl,
P_DR_OTHER_ED => ln_dr_other,
P_REMARKS => lv_remarks,
P_OPENING_BALANCE => ln_opening_balance,
P_CLOSING_BALANCE => ln_closing_balance,
P_OTHER_TAX_CREDIT => ln_other_tax_credit,
P_OTHER_TAX_DEBIT => ln_other_tax_debit,
p_simulate_flag => p_simulate_flag,
p_process_status => lv_process_status,
p_process_message => lv_process_message
);
/* Update the balances with the latest amount*/
jai_cmn_rg_balances_pkg.update_row(
p_organization_id => pr_tax.organization_id,
p_location_id => pr_tax.location_id,
p_register_type => lv_register_type,
p_amount_to_be_added=> ln_tr_amount,
p_simulate_flag => p_simulate_flag,
p_process_status => lv_process_status,
p_process_message => lv_process_message
);
/* Update CESS balances */
IF pr_diff_tax.excise_edu_cess <> 0 THEN
IF p_register_entry_type = CENVAT_DEBIT THEN
ln_other_tax_debit := pr_diff_tax.excise_edu_cess;
UPDATE JAI_CMN_RG_OTHERS
SET credit = credit + ln_other_tax_credit,
debit = debit + ln_other_tax_debit,
opening_balance = opening_balance,
closing_balance = closing_balance + ln_ex_cess_diff
WHERE source_register = decode(p_source_reg,'PLA', jai_constants.reg_pla,
decode(lv_register_type,
jai_constants.register_type_a,
jai_constants.reg_rg23a_2,
jai_constants.reg_rg23c_2
)
)
AND source_register_id = p_register_id
AND tax_type = jai_constants.tax_type_exc_edu_cess;
UPDATE JAI_CMN_RG_OTHERS
SET credit = credit + ln_other_tax_credit,
debit = debit + ln_other_tax_debit,
opening_balance = opening_balance,
closing_balance = closing_balance + ln_cvd_cess_diff
WHERE source_register = decode(p_source_reg,'PLA', jai_constants.reg_pla,
decode(lv_register_type,
jai_constants.register_type_a,
jai_constants.reg_rg23a_2,
jai_constants.reg_rg23c_2
)
)
AND source_register_id = p_register_id
AND tax_type = jai_constants.tax_type_cvd_edu_cess;
UPDATE JAI_CMN_RG_OTHERS
SET credit = credit + ln_other_tax_credit,
debit = debit + ln_other_tax_debit,
opening_balance = opening_balance,
closing_balance = closing_balance + ln_ex_sh_cess_diff
WHERE source_register = decode(p_source_reg,'PLA', jai_constants.reg_pla,
decode(lv_register_type,
jai_constants.register_type_a,
jai_constants.reg_rg23a_2,
jai_constants.reg_rg23c_2
)
)
AND source_register_id = p_register_id
AND tax_type = jai_constants.tax_type_sh_exc_edu_cess;
UPDATE JAI_CMN_RG_OTHERS
SET credit = credit + ln_other_tax_credit,
debit = debit + ln_other_tax_debit,
opening_balance = opening_balance,
closing_balance = closing_balance + ln_cvd_sh_cess_diff
WHERE source_register = decode(p_source_reg,'PLA', jai_constants.reg_pla,
decode(lv_register_type,
jai_constants.register_type_a,
jai_constants.reg_rg23a_2,
jai_constants.reg_rg23c_2
)
)
AND source_register_id = p_register_id
AND tax_type = jai_constants.tax_type_sh_cvd_edu_cess;
lv_process_message := 'EXC_PRC_PKG.update_RTV_Diff_value'||SQLERRM
||', StmtId->'||lv_statement_id;
END update_RTV_Diff_value;
SELECT register_id
FROM JAI_CMN_RG_I_TRXS
WHERE TRANSACTION_SOURCE_NUM = 18
AND register_id > 0 /*This check excludes master org records*/
AND ref_doc_no = p_transaction_id;
SELECT register_id
FROM JAI_CMN_RG_23AC_I_TRXS
WHERE TRANSACTION_SOURCE_NUM = 18
AND register_id > 0 /*This check excludes master org records*/
AND RECEIPT_REF = p_transaction_id;
SELECT SUM(nvl(quantity,0))
FROM jai_rcv_transactions jrt
WHERE
-- jrt.transaction_id > p_transaction_id AND
jrt.transaction_type = 'RETURN TO VENDOR'
AND jrt.shipment_header_id = cp_shipment_header_id
AND jrt.shipment_line_id = cp_shipment_line_id;
This Package is coded for Corrections Enhancement to invoke CENVAT and RG related insert APIs for PO Functionality.
- PROCESS_TRANSACTION
This is the driving procedure that calls different internal API's which further calls CENVAT and RG APIs for data insertion
- VALIDATE_TRANSACTION
Validates whether CENVAT and RG entries needs to be passed for receipt transaction.
Returns a value based on which the main procedure either proceeds or returns with a error message
- DERIVE_CGIN_SCENARIO
Returns a CGIN Scenario Code based on which Accounting and RG entry APIs are invoked
valid values: (1) 'REGULAR-FULL + REVERSAL-HALF' (2) 'REGULAR-HALF'
(3) 'REGULAR-FULL' (4) 'REGULAR-FULL + PARENT-REGULAR-HALF'
- RG_I_ENTRY
Has the RG1 Entry related data fetching logic. Invokes API to pass an RG1 Entry
- RG23_PART_I_ENTRY
Has the RG23 Part1 Entry related data fetching logic. Invokes API to pass RG23(A or C) Entry for quantity
- RG23_D_ENTRY
Has the RG23D Entry related data fetching logic. Invokes API to pass an RG23D Entry
- RG23_PART_II_ENTRY
Has the RG23 Part2 Entry related data fetching logic. Invokes API to pass RG23(A or C) Entry for Amount
- PLA_ENTRY
Has data fetching logic related to PLA Entry. Invokes API to pass a PLA Entry
- ACCOUNTING_ENTRIES
Determines the accounts that needs to be hit based on input params. Call Receipt Accounting API to pass accounting entries
- GENERATE_EXCISE_INVOICE
Invoices excise invoice no generation API and returns a value to caller
Other Procedure and Functions are used for the processing of the transaction purpose
2 26/10/2004 Vijay Shankar for Bug# 3927371, Version:115.1
IF Condition for Trading related transaction processing is corrected, which is previously wrong in PROCESS_TRANSACTION procedure
3 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.2, 115.3
Following are the changes done as part of RECEIPTS DEPLUG and Education Cess Enhancement
- Added a RECORD Definition for tax breakup as TAX_BREAUP and modified to use this as a parameter in all the calls
that passes cenvat accounting and register entries. this change is made from an extensible perspective of breakup
- added the following procedures
- get_vendor_changed_dtls : Retuns the changed excise vendor details as OUT Parameters
- post_cenvat_processor : Updates JAI_RCV_CENVAT_CLAIMS, JAI_RCV_LINES tables with claim details
- other_cenvat_rg_recording : Inserts a record into JAI_CMN_RG_OTHERS table with the parameters passed to the call
- added the parameters p_process_special_reason and qty in PROCESS_TRANSACTION. These are used incase the accounting
and rg entries should consider these values for TAX_BREAUP instead of main transaction values.
These parameters are mainly added for RECEIPTS DEPLUG to support second Claim functionality of CGIN items
- COMMENTED the code related to AUTO Claim of 2nd 50% of RECEIVE transaction to the tune of RETURN TO VENDOR
that is being processed. this redundant as the 2nd 50% claim of RECEIVE will happen only to the tune of remaining
quantity of RECEIVE after RTV. Previous functionality is that, it claims whole of RECEIVE quantity for 2nd 50% also
Incase of CGIN items, CGIN_CODE returned from derive_cgin_code for RTV previously is 'REGULAR-FULL + PARENT-REGULAR-HALF'
and now it returns 'REGULAR-FULL + REVERSAL-HALF' incase the parent RECEIVE is only 50% claimed
- Call to get_vendor_changed_dtls is added in all RG Entries related procedures like rg23_part_i_entry etc. this
is used to insert RG Entries with a different Vendor other than excise if the user indicated the same through
Claim Cenvat Screen of India Localization
- Modified all RG Entries related procedures to make calls to other_cenvat_rg_recording incase EXCISE or CVD
EDUCATION_CESS are attached to receipt lines. the call inturn makes relevant CESS entries into RG tables
- Modified ACCOUTING_ENTRIES procedure to pass accounting for CESS Amounts to relevant CESS accounts of
Organization Additional information. this passes different accounting entries for EXCISE and CVD EUDCATION_CESS
incase they have different accounts defined in Organization Addl. Info Setup
- Modified get_tax_amount_breakup to break the amount for EXCISE_EDUCATION_CESS and CVD_EDUCATION_CESS also
4. 16/02/2005 - bug#4187859 - File Version - 115.4
Even when RTV did not have cess, Validation to cess was done and an error was thrown to the uset that cess amount is
zero. This has been stopped by adding code not to call the ja_in_rg_others_pkg.insert_row procedure only if
the cess amount is not zero.
Dependency due to this bug:-
None
5. 16/03/2005 - Vijay Shankar for Bug#4211045 - File Version - 115.5
Incase of RMA Accounting for CESS, we are hitting Cr. Excise Receivable A/C and Dr. Cess RM A/C for Cess Amount.
this set of accounting for cess is wrong, instead it should be Cr. Cess Paid Payable Account and Dr. Cess RM A/C for Cess Amount
to fix this, changes are made in accounting_entries procedure and a call to jai_rcv_accounting_pkg.process_transaction
is made to pass an extra entry as Cr. Cess Paid Payable Accnt for Cess amt
6. 15/04/2005 Sanjikum for Bug #4293421, File Version 116.0(115.7)
Problem
-------
During Cenvat Claim, while Inserting the Record into JAI_CMN_RG_23AC_II_TRXS, the Transaction date is inserted as Transaction Date of
the JAI_RCV_TRANSACTIONS.transaction_date. It should be the date on which claim is being made.
Fix
---
In the Procedure rg23_part_ii_entry, while calling jai_cmn_rg_23ac_ii_pkg.insert_row, value of the parameter
p_transaction_date is changed to SYSDATE from r_trx.transaction_date
7. 19/04/2005 Vijay Shankar for Bug #4103161, File Version 116.1(115.8)
Rounding of RTV Excise amounts to nearest rupee is removed as it will be done separately through RG Rounding
Concurrrent program
* Dependancy for later versions of this object *
8 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.2
Code is modified due to the Impact of Receiving Transactions DFF Elimination
added a procedure rtv_processing_for_ssi that will be invoked for SSI processing
* High Dependancy for future Versions of this object *
9 10-may-2007 bgowrava for forward porting Bug#5756676, 11i bug#5747013. File Version 120.15
Issue : QTY REGISTER SHOULD BE UPDATED ON RECEIVE DEPENDING ON SETUP
Fix : Changes are made to check if the Qty register is already hit. If it is then
the Qty register is updated with the amounts. Otherwise call to api is made
to insert into Qty register
Dependancy due to this bug : Yes
10 10-may-2007 bgowrava for forward porting Bug#5756676, 11i bug#5747013. File Version 120.15
Issue : QTY REGISTER SHOULD BE UPDATED ON RECEIVE DEPENDING ON SETUP
Fix : The excise_invoice_no and excise_invoice_date should also be updated in Qty registers
in case of deferred claim also. So modified the update statement to update these columns also
11. 04/06/2007 sacsethi for bug 6109941 file version #120.18
CODE REVIEW COMMENTS FOR ENHANCEMENTS
Problem found for forward porting for Enhancement Additional cvd ( 5228046) and budget 2007 ( 5989740)
Dependancy:
-----------
IN60105D2 + 3496408
IN60106 + 3940588 + 4146708/4239736 + 4103161 + 4346453
/* IMPORTANT NOTE:
For Receiving Transactions: In case of CGIN Claim a value needs to be passed for JAI_CMN_RG_23AC_II_TRXS.REFERENCE_NUM column
that will be used for Duplicate Checking.
Incase of RECEIVE transaction value passed for 1st 50% Claim is '1st Claim'. During 2nd 50% Claim '2nd Claim' is passed
If 2nd Claim is happening from RTV transaction then TRANSACTION_ID of RECEIVE is passed as the value towards REFERENCE_NUM
In all Other transactions value passed for REFERENCE_NUM column is NULL
----------------------------------------------------------------------------------------------------------------------------*/
lv_procedure_name := 'CENVAT_RG_PKG.process_transaction';
SELECT 'jai_rcv_excise_processing_pkg-'||p_transaction_id INTO lv_temp FROM DUAL;
INSERT INTO JAI_RCV_RTV_DTLS(
transaction_id, parent_transaction_id, shipment_line_id,
excise_invoice_no, excise_invoice_date, rg_register_part_i,
creation_date, created_by, last_update_date, last_updated_by, last_update_login
) VALUES (
p_transaction_id, r_trx.parent_transaction_id, r_trx.shipment_line_id,
lv_excise_invoice_no, ld_excise_invoice_date, NULL,
SYSDATE, FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.user_id, FND_GLOBAL.login_id
);
jai_rcv_transactions_pkg.update_excise_invoice_no(
p_transaction_id => p_transaction_id,
p_excise_invoice_no => lv_excise_invoice_no,
p_excise_invoice_date => ld_excise_invoice_date
);
/*Added by nprashar for bug 6710747 The following condition is added to support register update for Inter Org Transfer*/
OR ( r_base_trx.source_document_Code = 'INVENTORY' AND lv_transaction_type <>'DELIVER' AND r_trx.item_class IN ('FGIN', 'FGEX', 'CCIN', 'CCEX'))
THEN
/*bgowrava for forward porting Bug#5756676..start*/
IF nvl(r_trx.quantity_register_flag,'N') = 'Y' THEN
OPEN cur_rg1_register_id;
UPDATE JAI_CMN_RG_I_TRXS
SET basic_ed = r_tax.basic_excise,
additional_ed = r_tax.addl_excise + r_tax.cvd,
other_ed = r_tax.other_excise,
excise_duty_amount = r_tax.basic_excise + r_tax.addl_excise + r_tax.cvd + r_tax.other_excise,
excise_invoice_number = r_trx.excise_invoice_no,
excise_invoice_date = r_trx.excise_invoice_date
WHERE register_id = ln_part_i_register_id;
UPDATE JAI_CMN_RG_23AC_I_TRXS
SET basic_ed = r_tax.basic_excise,
additional_ed = r_tax.addl_excise + r_tax.cvd,
additional_cvd = r_tax.addl_cvd,
other_ed = r_tax.other_excise,
EXCISE_INVOICE_NO = r_trx.excise_invoice_no,
excise_invoice_date = r_trx.excise_invoice_date
WHERE register_id = ln_part_i_register_id;
UPDATE JAI_RCV_CENVAT_CLAIMS
SET cenvat_claimed_amt = nvl(cenvat_claimed_amt,0)
+ (r_half_tax.basic_excise +
r_half_tax.addl_excise +
r_half_tax.other_excise +
r_half_tax.cvd +
r_tax.addl_cvd -- Date 30/10/2006 Bug 5228046 added by sacsethi
),
other_cenvat_claimed_amt = nvl(other_cenvat_claimed_amt,0)
+ (r_half_tax.excise_edu_cess + r_half_tax.cvd_edu_cess+nvl(r_half_tax.sh_exc_edu_cess,0)
+ nvl(r_half_tax.sh_cvd_edu_cess,0)),
/*added nvl(r_half_tax.sh_exc_edu_cess,0) + nvl(r_half_tax.sh_cvd_edu_cess,0) by vkaranam for budget 07 impact - bug#5989740*/
cenvat_sequence = cenvat_sequence + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE transaction_id = ln_receive_trx_id;
jai_cmn_rg_23ac_ii_pkg.update_payment_details(
p_register_id => ln_receive_part_ii_reg_id,
p_register_id_part_i => NULL,
p_charge_account_id => ln_receive_charge_accnt
);
update_registers(
p_quantity_register_id => ln_part_i_register_id,
p_quantity_register => lv_part_i_register,
p_payment_register_id => ln_part_ii_register_id,
p_payment_register => lv_part_ii_register,
p_charge_account_id => ln_charge_account_id,
p_process_status => p_process_status,
p_process_message => p_process_message,
p_simulate_flag => p_simulate_flag,
p_codepath => p_codepath
);
UPDATE JAI_RCV_RTV_DTLS
SET rg_register_part_i = ln_part_i_register_id
WHERE transaction_id = p_transaction_id;
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_called_from => 'RECEIPTS',
p_cess_amount => ln_exc_edu_cess ,/*added by vkaranam for budget 07 impact - bug#5989740*/
p_sh_cess_amount => ln_sh_exc_edu_cess --added by vkaranam for budget 07 impact - bug#5989740
);
UPDATE JAI_RCV_TRANSACTIONS
SET quantity_register_flag = 'Y',
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE transaction_id = p_transaction_id ;
SELECT po_header_id, creation_date
FROM po_headers_all
WHERE po_header_id = cp_po_header_id;
FND_FILE.put_line(FND_FILE.log,'Before call to jai_cmn_rg_23ac_i_trxs_pkg.insert_row');
jai_cmn_rg_23ac_i_trxs_pkg.insert_row(
p_register_id => p_register_id,
p_inventory_item_id => r_trx.inventory_item_id,
p_organization_id => r_trx.organization_id,
p_quantity_received => ln_quantity,
p_receipt_id => r_trx.transaction_id,
p_transaction_type => lv_transaction_type,
p_receipt_date => r_trx.transaction_date, -- Why cant this be ShipmentHeader.Receipt_date
p_po_header_id => r_base_trx.po_header_id,
p_po_header_date => r_po_header.creation_date,
p_po_line_id => r_base_trx.po_line_id,
p_po_line_location_id => r_base_trx.po_line_location_id,
p_vendor_id => ln_vendor_id,
p_vendor_site_id => ln_vendor_site_id,
p_customer_id => ln_customer_id,
p_customer_site_id => ln_customer_site_id,
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 => lv_excise_invoice_no,
p_excise_invoice_date => ld_excise_invoice_date,
p_oth_receipt_quantity => NULL,
p_oth_receipt_id => NULL,
p_oth_receipt_date => NULL,
p_register_type => jai_general_pkg.get_rg_register_type(p_item_class => r_trx.item_class),
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 => NULL,
p_location_id => r_trx.location_id,
p_transaction_uom_code => r_trx.uom_code,
p_transaction_date => r_trx.transaction_date,
p_basic_ed => ln_basic_ed,
p_additional_ed => ln_additional_ed,
p_additional_cvd => ln_additional_cvd, -- Date 30/10/2006 Bug 5228046 added by sacsethi
p_other_ed => ln_other_ed,
p_charge_account_id => NULL,
p_transaction_source => r_base_trx.source_document_code,
p_called_from => 'CENVAT_RG_PKG.rg23_part_i_entry',
p_simulate_flag => p_simulate_flag,
p_process_status => p_process_status,
p_process_message => p_process_message
);
UPDATE JAI_RCV_TRANSACTIONS
SET quantity_register_flag = 'Y',
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE transaction_id = p_transaction_id ;
SELECT rel.excise_duty_rate
FROM JAI_OM_OE_RMA_LINES rel
WHERE rel.rma_line_id = cp_oe_order_line_id;
SELECT register_id
FROM JAI_CMN_RG_23D_TRXS
WHERE receipt_ref = cp_receipt_id
ANd transaction_source_num = cp_transaction_id;
SELECT tax_rate
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = cp_shipment_line_id
AND tax_type IN (jai_constants.tax_type_excise,
jai_constants.tax_type_exc_additional,
jai_constants.tax_type_exc_other,
jai_constants.tax_type_cvd,
jai_constants.tax_type_add_cvd)
--AND upper(tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD')
-- we need not include the CESS here, because the rate should correspond to actual tax instead of cess
ORDER BY tax_line_no;
FND_FILE.put_line(FND_FILE.log,'Calling jai_cmn_rg_23d_trxs_pkg.insert_row');
jai_cmn_rg_23d_trxs_pkg.insert_row(
p_register_id => p_register_id,
p_organization_id => r_trx.organization_id,
p_location_id => r_trx.location_id,
p_transaction_type => lv_transaction_type,
p_receipt_id => p_transaction_id,
p_quantity_received => ln_quantity_received,
p_inventory_item_id => r_trx.inventory_item_id,
p_subinventory => r_base_trx.subinventory,
p_reference_line_id => r_trx.shipment_line_id,
p_transaction_uom_code => lv_transaction_uom_code,
p_customer_id => ln_customer_id,
p_bill_to_site_id => NULL,
p_ship_to_site_id => ln_customer_site_id,
p_quantity_issued => NULL,
p_register_code => NULL,
p_released_date => NULL,
p_comm_invoice_no => lv_excise_invoice_no,
p_comm_invoice_date => ld_excise_invoice_date,
p_receipt_boe_num => jai_general_pkg.get_matched_boe_no(p_transaction_id),
p_oth_receipt_id => NULL,
p_oth_receipt_date => NULL,
p_oth_receipt_quantity => NULL,
p_remarks => 'Live-Trx/PrntTrx:'||r_trx.transaction_id||'/'||r_trx.parent_transaction_id,
p_qty_to_adjust => ln_qty_to_adjust,
/* Bug 4516667.
As per discussion with Vikram and Gadde rounding to 4 decimal places
for rate per unit */
p_rate_per_unit => round(ln_duty_amount/ln_quantity_received,4),
p_excise_duty_rate => ln_excise_duty_rate,
p_charge_account_id => NULL, -- this will be updated later by calling update procedure
p_duty_amount => ln_duty_amount,
p_receipt_date => r_trx.transaction_date,
p_goods_issue_id => NULL,
p_goods_issue_date => NULL,
p_goods_issue_quantity => NULL,
p_transaction_date => r_trx.transaction_date,
p_basic_ed => ln_basic_ed,
p_additional_ed => ln_additional_ed,
p_additional_cvd => ln_additional_cvd, -- Date 30/10/2006 Bug 5228046 added by sacsethi
p_other_ed => ln_other_ed,
p_cvd => ln_cvd,
p_vendor_id => ln_vendor_id,
p_vendor_site_id => ln_vendor_site_id,
p_receipt_num => r_trx.receipt_num,
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 => ln_other_tax_credit,
p_other_tax_debit => ln_other_tax_debit,
p_transaction_source => r_base_trx.source_document_code,
p_called_from => 'CENVAT_RG_PKG.rg23_d_entry',
p_simulate_flag => p_simulate_flag,
p_process_status => p_process_status,
p_process_message => p_process_message
);
FND_FILE.put_line(FND_FILE.log, 'To Update Qty_To_Adjust field of RG23D Table' );
FND_FILE.put_line(FND_FILE.log,'Calling jai_cmn_rg_23d_trxs_pkg.update_qty_to_adjust');
jai_cmn_rg_23d_trxs_pkg.update_qty_to_adjust(
p_register_id => ln_ancestor_reg_id,
p_quantity => ln_quantity_received ,
p_simulate_flag => p_simulate_flag,
p_process_status => p_process_status,
p_process_message => p_process_message
);
FND_FILE.put_line(FND_FILE.log,'Calling jai_cmn_rg_23ac_ii_pkg.insert_row');
jai_cmn_rg_23ac_ii_pkg.insert_row(
p_register_id => p_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_cr_basic,
p_cr_additional_ed => ln_cr_addl,
p_cr_additional_cvd => ln_cr_addl_cvd, -- Date 30/10/2006 Bug 5228046 added by sacsethi
p_cr_other_ed => ln_cr_other,
p_dr_basic_ed => ln_dr_basic,
p_dr_additional_ed => ln_dr_addl,
p_dr_additional_cvd => ln_dr_addl_cvd, -- Date 30/10/2006 Bug 5228046 added by sacsethi
p_dr_other_ed => ln_dr_other,
p_excise_invoice_no => lv_excise_invoice_no,
p_excise_invoice_date => ld_excise_invoice_date,
p_register_type => lv_register_type,
p_remarks => 'Live-Trx/PrntTrx:'||r_trx.transaction_id||'/'||r_trx.parent_transaction_id,
p_vendor_id => ln_vendor_id,
p_vendor_site_id => ln_vendor_site_id,
p_customer_id => ln_customer_id,
p_customer_site_id => ln_customer_site_id,
p_location_id => r_trx.location_id,
p_transaction_date => SYSDATE, --r_trx.transaction_date, --Changed by Sanjikum for Bug #4293421
p_charge_account_id => NULL,
p_register_id_part_i => p_part_i_register_id,
p_reference_num => p_reference_num,
p_rounding_id => NULL,
p_other_tax_credit => ln_other_tax_credit,
p_other_tax_debit => ln_other_tax_debit,
p_transaction_type => lv_transaction_type,
p_transaction_source => r_base_trx.source_document_code,
p_called_from => 'CENVAT_RG_PKG.rg23_part_ii_entry',
p_simulate_flag => p_simulate_flag,
p_process_status => p_process_status,
p_process_message => p_process_message
);
FND_FILE.put_line(FND_FILE.log,'Calling jai_cmn_rg_pla_trxs_pkg.insert_row');
jai_cmn_rg_pla_trxs_pkg.insert_row(
p_register_id => p_register_id,
p_tr6_challan_no => NULL,
p_tr6_challan_date => NULL,
p_cr_basic_ed => ln_cr_basic,
p_cr_additional_ed => ln_cr_addl,
p_cr_other_ed => ln_cr_other,
p_ref_document_id => r_trx.transaction_id,
p_ref_document_date => r_trx.transaction_date,
p_dr_invoice_id => lv_excise_invoice_no,
p_dr_invoice_date => ld_excise_invoice_date,
p_dr_basic_ed => ln_dr_basic,
p_dr_additional_ed => ln_dr_addl,
p_dr_other_ed => ln_dr_other,
p_organization_id => r_trx.organization_id,
p_location_id => r_trx.location_id,
p_bank_branch_id => NULL,
p_entry_date => NULL,
p_inventory_item_id => r_trx.inventory_item_id,
p_vendor_cust_flag => 'V',
p_vendor_id => ln_vendor_id,
p_vendor_site_id => ln_vendor_site_id,
p_excise_invoice_no => lv_excise_invoice_no,
p_remarks => 'Live-Trx/PrntTrx:'||r_trx.transaction_id||'/'||r_trx.parent_transaction_id,
p_transaction_date => r_trx.transaction_date,
p_charge_account_id => NULL,
p_other_tax_credit => ln_other_tax_credit,
p_other_tax_debit => ln_other_tax_debit,
p_transaction_type => lv_transaction_type,
p_transaction_source => r_base_trx.source_document_code,
p_called_from => 'cenvat_RG_PKG.pla_entry',
p_simulate_flag => p_simulate_flag,
p_process_status => p_process_status,
p_process_message => p_process_message
);
PROCEDURE update_registers(
p_quantity_register_id IN NUMBER,
p_quantity_register IN VARCHAR2,
p_payment_register_id IN NUMBER,
p_payment_register IN VARCHAR2,
p_charge_account_id IN NUMBER,
p_process_status OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2,
p_simulate_flag IN VARCHAR2,
p_codepath IN OUT NOCOPY VARCHAR2
) IS
lv_statement_id VARCHAR2(5);
p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'cenvat_rg.update_reg' , 'START'); /* 1 */
jai_cmn_rg_23ac_i_trxs_pkg.update_payment_details(
p_register_id => p_quantity_register_id,
p_register_id_part_ii => p_payment_register_id,
p_charge_account_id => p_charge_account_id
);
jai_cmn_rg_23d_trxs_pkg.update_payment_details(
p_register_id => p_quantity_register_id,
p_charge_account_id => p_charge_account_id
);
UPDATE JAI_CMN_RG_I_TRXS
SET
register_id_part_ii = p_payment_register_id,
charge_account_id = p_charge_account_id,
payment_register = p_payment_register
WHERE register_id = p_quantity_register_id;
FND_FILE.put_line( FND_FILE.log, 'JA_IN_RECEIPT_MODVAT_RG_PKG.update_registers: No Quantity Register Updated');
jai_cmn_rg_23ac_ii_pkg.update_payment_details(
p_register_id => p_payment_register_id,
p_register_id_part_i => p_quantity_register_id,
p_charge_account_id => p_charge_account_id
);
jai_cmn_rg_pla_trxs_pkg.update_payment_details(
p_register_id => p_payment_register_id,
p_charge_account_id => p_charge_account_id
);
FND_FILE.put_line( FND_FILE.log, 'JA_IN_RECEIPT_MODVAT_RG_PKG.update_registers: No Payment Register Updated');
p_process_message := 'CENVAT_RG_PKG.update_registers->'||SQLERRM||', StmtId->'||lv_statement_id;
END update_registers;
SELECT count(1)
FROM JAI_CMN_RG_23AC_II_TRXS;
SELECT excise_invoice_no, excise_invoice_date
FROM JAI_RCV_RTV_DTLS
WHERE transaction_id = cp_transaction_id;
SELECT nvl(cenvat_claimed_ptg,0)
FROM JAI_RCV_CENVAT_CLAIMS
WHERE shipment_line_id = cp_shipment_line_id;
FOR tax_rec IN (SELECT rtl.tax_type ,
nvl(rtl.tax_amount, 0) tax_amount ,
nvl(rtl.modvat_flag, 'N') modvat_flag ,
nvl(rtl.currency, jai_rcv_trx_processing_pkg.gv_func_curr) currency ,
nvl(decode(p_breakup_type, 'RG23D', 100, jtc.mod_cr_percentage), 0) mod_cr_percentage,
nvl(jtc.rounding_factor, 0) rnd
FROM JAI_RCV_LINE_TAXES rtl, JAI_CMN_TAXES_ALL jtc
WHERE rtl.shipment_line_id = p_shipment_line_id
AND jtc.tax_id = rtl.tax_id)
LOOP
p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
INSERT INTO JAI_CMN_RG_OTHERS(
rg_other_id, source_type, source_register,
source_register_id, tax_type, credit, debit,
created_by, creation_date, last_updated_by, last_update_date
) VALUES (
JAI_CMN_RG_OTHERS_S.nextval, ln_source_type, p_source_register,
p_source_register_id, p_tax_type, p_credit, p_debit,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
);
SELECT decode( cp_register_type,
jai_constants.register_type_pla, pla_balance,
jai_constants.register_type_a, rg23a_balance,
jai_constants.register_type_c, rg23c_balance
)
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id;
select receipt_excise_rate, rtv_excise_rate, excise_basis_amt
from jai_rcv_rtv_batch_trxs
where transaction_id = cp_transaction_id;
select a.tax_rate, b.tax_account_id, a.tax_id
from JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
where a.shipment_line_id = cp_shipment_line_id
and a.tax_id = b.tax_id
and ( a.tax_type = jai_constants.tax_type_exc_edu_cess
-- following is to take care of Initial solution(Year2004) for Excise Cess func.
or (a.tax_type = jai_constants.tax_type_other and b.stform_type = lv_stform_type) --'EXCISE - CESS') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
--or (a.tax_type = 'Other' and b.stform_type = 'EXCISE - CESS')
);
select a.tax_rate, b.tax_account_id, a.tax_id
from JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
where a.shipment_line_id = cp_shipment_line_id
and a.tax_id = b.tax_id
and ( a.tax_type = jai_constants.tax_type_sh_exc_edu_cess
-- following is to take care of Initial solution(Year2004) for Excise sh Cess func.
or (a.tax_type = jai_constants.tax_type_other and b.stform_type = lv_stform_type)
);
select accrual_account_id
from po_distributions_all a,
( select po_line_location_id
from rcv_transactions
where transaction_id = cp_transaction_id) b
where a.line_location_id = b.po_line_location_id;
select ap_accrual_account
from mtl_parameters
where organization_id = cp_organization_id;
update_RTV_Diff_value
( pr_base_trx => r_base_trx,
pr_tax => r_trx,
pr_diff_tax => r_diff_tax,
p_source_reg => 'RG23II',
p_register_entry_type => lv_register_entry_type,
p_register_id => ln_part_ii_register_id,
p_simulate_flag => lv_simulate_flag,
p_codepath => pv_codepath,
p_process_status => pv_process_status,
p_process_message => pv_process_message
);
update_RTV_Diff_value
( pr_base_trx => r_base_trx,
pr_tax => r_trx,
pr_diff_tax => r_diff_tax,
p_source_reg => 'PLA',
p_register_entry_type => lv_register_entry_type,
p_register_id => ln_part_ii_register_id,
p_simulate_flag => lv_simulate_flag,
p_codepath => pv_codepath,
p_process_status => pv_process_status,
p_process_message => pv_process_message
);