[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_last_update_login NUMBER ; --File.Sql.35 Cbabu := nvl(FND_GLOBAL.LOGIN_ID,- 1);
SELECT enable_trace
FROM fnd_concurrent_programs
WHERE concurrent_program_name = cp_conc_pname ; --'JAINRGRND';
SELECT a.sid, a.serial#, b.spid
FROM v$session a, v$process b
WHERE audsid = userenv('SESSIONID')
AND a.paddr = b.addr;
CURSOR get_dbname IS SELECT name FROM v$database;
SELECT vendor_id, vendor_site_id, receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id;
SELECT slno, closing_balance
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND register_type = p_register_type
AND slno = (SELECT max(slno) slno
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND register_type = p_register_type);
Select nvl(a.closing_balance,0) closing_balance
from JAI_CMN_RG_OTHERS a
Where a.source_type = 1 --1 is for JAI_CMN_RG_23AC_II_TRXS
AND a.source_register = DECODE(p_register_type,'A',jai_constants.reg_rg23a_2, 'C', jai_constants.reg_rg23c_2) --'RG23A_P2','C','RG23C_P2')
and a.tax_type = p_tax_type
AND abs(a.source_register_id) IN (Select max(abs(c.source_register_id))
from JAI_CMN_RG_23AC_II_TRXS b,
JAI_CMN_RG_OTHERS c
Where c.source_type = 1 --1 is for JAI_CMN_RG_23AC_II_TRXS
AND c.source_register = DECODE(p_register_type,'A',jai_constants.reg_rg23a_2, 'C', jai_constants.reg_rg23c_2) --'RG23A_P2','C','RG23C_P2')
AND b.register_id = c.source_register_id
AND b.organization_id = p_organization_id
and b.location_id = p_location_id
and c.tax_type = p_tax_type
and b.register_type = P_register_type);*/
SELECT max(fin_year)
FROM JAI_CMN_FIN_YEARS
WHERE organization_id = p_organization_id
AND fin_active_flag = 'Y';
SELECT rg_rounding_account_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND ( location_id IS NULL OR location_id = 0);
SELECT modvat_rm_account_id, modvat_cg_account_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id;
SELECT qty_received
FROM JAI_RCV_LINES
WHERE shipment_line_id = cp_shipment_line_id;
SELECT --Added the 6 columns by Sanjikum for Bug #4049363
sum(decode(b.tax_type, 'Excise', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) excise_amt,
sum(decode(b.tax_type, 'Addl. Excise', b.tax_amount * nvl(c.mod_cr_percentage, 0), 'CVD', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) additional_excise_amt,
sum(decode(b.tax_type, jai_constants.tax_type_add_cvd, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) additional_cvd , /*Changed by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
sum(decode(b.tax_type, 'Other Excise', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) other_excise_amt,
sum(decode(b.tax_type, jai_constants.tax_type_exc_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) excise_edu_cess_amt,
sum(decode(b.tax_type, jai_constants.tax_type_cvd_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) cvd_edu_cess_amt ,
sum(decode(b.tax_type, jai_constants.tax_type_sh_exc_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) sh_excise_edu_cess_amt, -- Date 16/04/2007 by
sum(decode(b.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) sh_cvd_edu_cess_amt -- sacsethi for Bug#5989740
FROM JAI_RCV_LINE_TAXES b, JAI_CMN_TAXES_ALL c
WHERE b.shipment_line_id = cp_shipment_line_id
AND b.tax_id = c.tax_id
AND b.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_exc_edu_cess, jai_constants.tax_type_cvd_edu_cess ,
jai_constants.tax_type_sh_exc_edu_cess, jai_constants.tax_type_sh_cvd_edu_cess -- Date 16/04/2007 by sacsethi for Bug#5989740
)
--AND b.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', 'CVD', jai_constants.tax_type_exc_edu_cess, jai_constants.tax_type_cvd_edu_cess)
AND b.modvat_flag = 'Y';
SELECT max(rounding_id) rounding_id,
--Added the below 6 by Sanjikum for Bug #4049363
sum(basic_ed - rounded_basic_ed) rounded_basic_amt,
sum(additional_ed - rounded_additional_ed) rounded_addl_amt,
sum(additional_cvd - rounded_additional_cvd) rounded_additional_cvd ,/*Changed by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
sum(other_ed - rounded_other_ed) rounded_other_amt,
sum(excise_edu_cess - rounded_excise_edu_cess) rounded_excise_edu_cess,
sum(cvd_edu_cess - rounded_cvd_edu_cess) rounded_cvd_edu_cess,
sum(sh_excise_edu_cess - rounded_sh_excise_edu_cess) rounded_sh_excise_edu_cess,
sum(sh_cvd_edu_cess - rounded_sh_cvd_edu_cess) rounded_sh_cvd_edu_cess,
count(1)
FROM JAI_CMN_RG_ROUND_HDRS
WHERE source_header_id = p_source_header_id
AND excise_invoice_no = p_excise_invoice_no
AND excise_invoice_date = p_excise_invoice_date
AND src_transaction_type = p_transaction_type
GROUP BY rounding_id, excise_invoice_no, excise_invoice_date;
SELECT count(1) total_cnt, sum( decode(item_class, 'CGIN', 1, 'CGEX', 1, 0) ) cgin_cnt
FROM JAI_CMN_RG_ROUND_LINES
WHERE rounding_id = p_rounding_id
GROUP BY rounding_id;
v_no_of_periods_updated NUMBER(15);
- RG_PART_II, GL Accouting, Update RG balances
- Insert into JAI_CMN_RG_ROUND_HDRS, JAI_CMN_RG_ROUND_LINES tables
- Makes a call to jai_cmn_rg_period_bals_pkg.adjust_rounding to adjust this rounding amount in PERIOD Balance if
the min(ROUNDING_LINE_ID,register_id) of excise invoice is already consolidated in another period
Only for Excise Invoice having all CGIN Items is considered as a case where in two rounding entries were passed.
all the other scenarios only one rounding entry is passed
2 31/08/2004 Vijay Shankar for Bug# 3496408 Version : 115.1
Modified the code to consider CORRECTions of RECEIVE to round the CENVAT amount. Related Selects and Updates
are modified to select JAI_CMN_RG_ROUND_LINES_S.nextval, the all the required data and punch Rounding_id in JAI_CMN_RG_23AC_II_TRXS in CORRECT RG records also
3 21/01/2005 Sanjikum for Bug #4049363 Version 116.0 (115.2)
Modified the Code to consider the rounding for each tax type instead of total excise amount
Changed a lot of code. Can be searched with Bug#4049363
Dependency -
New columns are added in the Tables - JAI_CMN_RG_ROUND_HDRS, JAI_CMN_RG_ROUND_LINES
4 31/08/2004 Vijay Shankar for Bug# 4103161 Version : 116.1
added a new procedure DO_RTV_ROUNDING to take care of RTV Rounding also.
And existing procedure do_rounding is modified to make a call to do_rtv_rounding
* Dependancy for later version of the object *
5. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.2
Code is modified due to the Impact of Receiving Transactions DFF Elimination
* High Dependancy for future Versions of this object *
6. 19/04/2005 rallamse for Bug#4336482, Version 116.3
For SEED there is a change in concurrent "JAINRGRND" to use FND_STANDARD_DATE with STANDARD_DATE format
Procedure ja_in_rg_rounding_pkg.do_rounding signature modified by converting P_EX_INVOICE_TO_DATE, P_EX_INVOICE_TO_DATE
of DATE datatype to PV_EX_INVOICE_FROM_DATE, PV_EX_INVOICE_TO_DATE of varchar2 datatype.
The varchar2 values are converted to DATE fromat using fnd_date.canonical_to_date function.
7. 08-Jun-2005 Version 116.2 jai_rcv_rnd -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
8. 13-Jun-2005 File Version: 116.3
Ramananda for bug#4428980. Removal of SQL LITERALs is done
9. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
GL Sources and GL Categories got changed. Refer bug for the details
10. 31-Jan-2006 Bug 4930048. Added by Lakshmi Gopalsami version 120.2
(1) Changed transaction_source_num to transaction_id in
update of JAI_CMN_RG_23AC_II_TRXS and subquery checking
for the existence in jai_rcv_transactions
(2) Changed transaction_source_num to transaction_id in
update of JAI_CMN_RG_PLA_TRXS and subquery checking
for the existence in jai_rcv_transactions
(3) Changed transaction_source_num to transaction_id in
update of JAI_CMN_RG_23AC_II_TRXS and subquery checking
for the existence in rcv_transactions
(4) Added proper alias names and changed
transaction_source_num to transaction_id while
checking data in rcv_transactions in update to
JAI_CMN_RG_23AC_II_TRXS. This is done in 2 places.
DEPENDENCY
----------
IN60106 + 4146708 + 4103161 + 4346453
11. 20/11/2006 Aiyer for bug#5228046 , File Version 120.3
Issue :- Enhancement to support new tax type called ADDITIONAL_CVD
Fix :- Added the code similar to that of additional_ed. The procedures do_rounding and do_rtv_rounding
have been modified.
Dependencies Due to this Bug:-
There are Datamodel and spec changes done for this bug. So this bug has both
12. 26-FEB-2007 SSAWANT , File version 120.7
Forward porting the change in 11.5 bug 5053992 to R12 bug no 5054176.
Issue:
The rounding entries for CESS are hitting wrong register.
Fix:
While inserting rounding for excise into JAI_CMN_RG_23AC_II_TRXS table the varibale v_register_type was
used for register_type column. But for jai_rg_others while inserting the corresponding CESS the
parameter p_register_type is used for source_register and so the discrepancy. Replaced p_register_type with
v_register_type while inserting into jai_rg_others.
13. 19-Apr-2007 Sacsethi for forward porting Bug#5989740, 11i bug#5907436, file version 120.8
14. 19-Apr-2007 bgowrava for forward porting bug#5674376. File Version 120.9
Issue : Rounding entries are not generated correctly.
Fix : Whenever we fetch parent register id for a rounding entry we use excise_invoice_no and
excise_invoice_date. But these two can be same for different vendors. So added a check to include vendor_id
and vendor_site_id wherever applicable. The cursor to fetch parent register id is also modified to include
shipment header id.
The vendor_id and vendor_site_id for rounding_entries are populated from rcv_shipment_headers
This is now changed to be populated from the parent entry in ja_in_rg23_part_ii.
These changes were made in bug#5478107 at PRE Addl. CVD enh level. The same are
forward ported to the latest code line.
15. 19-Apr-2007 bgowrava for forward porting bug#5674376. File Version 120.9
Issue : Rounding entries are not generated correctly.
Fix : The concurrent was erroring out as the group by clause did not have vendor_id and vendor_site_id. These
were included in the select clause as part of previous fix. Now these are added in group by clause also.
16. 02/05/2007 bduvarag for the Bug#5141459, file version 120.10
Forward porting the changes done in 11i bug#4548378
--------------------------------------------------------------------------------------------------------------------------------*/
P_EX_INVOICE_FROM_DATE := fnd_date.canonical_to_date(PV_EX_INVOICE_FROM_DATE);
v_last_update_login := nvl(FND_GLOBAL.LOGIN_ID,- 1);
FND_FILE.PUT_LINE( FND_FILE.log, 'File Version: 115.2 and LastUpdateDate:08/01/2004, Inputs parameters: p_organization_id->'
||p_organization_id
||', p_ex_invoice_from_date->'||p_ex_invoice_from_date
||', p_ex_invoice_to_date->'||p_ex_invoice_to_date
||', v_enable_trace->'||v_enable_trace
);
select b.shipment_header_id, a.excise_invoice_no, a.excise_invoice_date, b.transaction_type,
a.vendor_id,a.vendor_site_id,/*bgowrava for forward porting bug#5674376*/
min(a.location_id) location_id,
sum(a.cr_basic_ed) cr_basic_ed,
sum(a.cr_additional_ed) cr_additional_ed,
sum(a.cr_additional_cvd) cr_additional_cvd,/*5228046 Additional cvd Enhancement*/
sum(a.cr_other_ed) cr_other_ed,
sum(a.dr_basic_ed) dr_basic_ed,
sum(a.dr_additional_ed) dr_additional_ed,
sum(a.dr_additional_cvd) dr_additional_cvd, /*Column added by SACSETHI for the bug 5228046 - Additional CVD Enhancement */
sum(a.dr_other_ed) dr_other_ed
from JAI_CMN_RG_23AC_II_TRXS a, rcv_transactions b
where a.RECEIPT_REF = b.transaction_id
AND a.organization_id = p_organization_id
AND (
(p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NULL)
OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NULL
AND a.excise_invoice_date >= p_ex_invoice_from_date)
OR (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NOT NULL
AND a.excise_invoice_date <= p_ex_invoice_to_date)
OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NOT NULL
AND a.excise_invoice_date BETWEEN p_ex_invoice_from_date AND p_ex_invoice_to_date)
)
AND a.rounding_id IS NULL
AND a.TRANSACTION_SOURCE_NUM = 18
AND b.transaction_type = v_transaction_type
GROUP BY b.shipment_header_id, b.transaction_type, a.excise_invoice_no, a.excise_invoice_date,
a.vendor_id,a.vendor_site_id /*bgowrava for forward porting bug#5674376*/
)
LOOP
v_exc_inv_rnd_counter := 0;
v_no_of_periods_updated := null;
UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
SET jcrg23ac.rounding_id = v_already_rounded_chk
WHERE jcrg23ac.excise_invoice_no = v_excise_invoice_no
AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND jcrg23ac.organization_id = p_organization_id
AND jcrg23ac.rounding_id IS NULL
AND jcrg23ac.transaction_source_num = 18
AND exists (select 1 from rcv_transactions rt
/* Bug 4930048. Added by Lakshmi Gopalsami
Changed transaction_source_num to transaction_id
*/
where rt.transaction_id = jcrg23ac.receipt_ref
and rt.transaction_type = v_transaction_type);
SELECT JAI_CMN_RG_ROUND_HDRS_S.nextval INTO v_rounding_id FROM dual;
FOR line IN ( SELECT a.shipment_line_id, a.transaction_id, d.item_class
FROM JAI_RCV_LINES a,JAI_RCV_CENVAT_CLAIMS b,RCV_TRANSACTIONS c, JAI_INV_ITM_SETUPS d
WHERE a.organization_id = d.organization_id
AND b.transaction_id = a.transaction_id /*bgowrava for forward porting bug#5674376*/
AND a.inventory_item_id = d.inventory_item_id
AND a.shipment_header_id = v_shipment_header_id
AND a.excise_invoice_no = v_excise_invoice_no
AND a.excise_invoice_date = v_excise_invoice_date
AND ( (nvl(b.vendor_id,-999) = nvl(r.vendor_id,-999)
AND nvl(b.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
AND b.vendor_changed_flag = 'Y' )
OR
(nvl(c.vendor_id,-999) = nvl(r.vendor_id,-999)
AND nvl(c.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
AND b.vendor_changed_flag = 'N' )
) /*bgowrava for forward porting bug#5674376*/
GROUP BY a.shipment_line_id, a.transaction_id, d.item_class
)
LOOP
--Added below 1 statements by Sanjikum for Bug #4049363
ln_receive_amount := v_zero_record;
FOR trx IN (select transaction_id, quantity
from rcv_transactions
where shipment_line_id = line.shipment_line_id
and (
(transaction_type = lv_ttype_correct and parent_transaction_id = line.transaction_id)
OR transaction_id = line.transaction_id
)
)
LOOP
--Added below the 7 statements by Sanjikum for Bug #4049363
ln_cenvat_amount.basic := ln_receive_amount.basic * trx.quantity/ln_receive_qty;
INSERT INTO JAI_CMN_RG_ROUND_LINES (ROUNDING_LINE_ID,
ROUNDING_ID, SRC_LINE_ID, SRC_TRANSACTION_ID,
EXCISE_AMT,
BASIC_ED, ADDITIONAL_ED, OTHER_ED, --Added by Sanjikum for Bug #4049363
EXCISE_EDU_CESS, CVD_EDU_CESS,--Added by Sanjikum for Bug #4049363
sh_excise_edu_cess , -- Date 16/04/2007 by sacsethi for Bug#5989740
sh_cvd_edu_cess ,
ITEM_CLASS, CREATION_DATE, CREATED_BY,
program_application_id, program_id, program_login_id, request_id
)
VALUES ( JAI_CMN_RG_ROUND_LINES_S.nextval,
v_rounding_id,
line.shipment_line_id,
trx.transaction_id,
ln_cenvat_amount.total, --Added by Sanjikum for Bug #4049363
ln_cenvat_amount.basic,
ln_cenvat_amount.additional,
ln_cenvat_amount.other, --Columns Added by Sanjikum for Bug #4049363
ln_cenvat_amount.excise_edu_cess,
ln_cenvat_amount.cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
ln_cenvat_amount.sh_excise_edu_cess,
ln_cenvat_amount.sh_cvd_edu_cess, --Columns Added by Sanjikum for Bug #4049363
line.item_class, SYSDATE, v_created_by,
fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
);
UPDATE JAI_CMN_RG_ROUND_LINES
SET --7 columns added by Sanjikum for Bug #4049363
excise_amt = NVL(basic_ed/2,0) +
NVL(additional_ed/2,0) +
NVL(additional_cvd/2,0) + /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
NVL(other_ed/2,0) +
NVL(excise_edu_cess/2,0) +
NVL(cvd_edu_cess/2,0),
basic_ed = basic_ed/2,
additional_ed = additional_ed/2,
additional_cvd = additional_cvd/2,/*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
other_ed = other_ed/2,
excise_edu_cess = excise_edu_cess/2,
cvd_edu_cess = cvd_edu_cess/2 ,
sh_excise_edu_cess = sh_excise_edu_cess/2,
sh_cvd_edu_cess = sh_cvd_edu_cess/2
WHERE rounding_id = v_rounding_id;
UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
SET jcrg23ac.rounding_id = 0
WHERE jcrg23ac.organization_id = p_organization_id
AND jcrg23ac.excise_invoice_no = v_excise_invoice_no
AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND jcrg23ac.rounding_id IS NULL
AND jcrg23ac.TRANSACTION_SOURCE_NUM = 18
AND EXISTS (
SELECT 1 FROM rcv_transactions rt
WHERE rt.shipment_header_id = v_shipment_header_id
AND rt.transaction_id = jcrg23ac.receipt_ref
AND ( rt.transaction_type = r.transaction_type
OR ( rt.transaction_type = lv_ttype_correct AND exists
(select 1 from rcv_transactions rt1
where rt1.transaction_id = rt.parent_transaction_id
and rt1.transaction_type = r.transaction_type)
)
)
);
DELETE FROM JAI_CMN_RG_ROUND_LINES WHERE rounding_id = v_rounding_id;
INSERT INTO JAI_CMN_RG_23AC_II_TRXS(
REGISTER_ID, ORGANIZATION_ID, LOCATION_ID, FIN_YEAR, INVENTORY_ITEM_ID, SLNO,
CR_BASIC_ED, DR_BASIC_ED,
CR_ADDITIONAL_ED, DR_ADDITIONAL_ED, --Added by Sanjikum for Bug #4049363
CR_ADDITIONAL_CVD,DR_ADDITIONAL_CVD, /* ADDED THE COLUMNS CR_ADDITIONAL_CVD,DR_ADDITIONAL_CVD FOR THE ENHANCEMENT 5228046 */
CR_OTHER_ED, DR_OTHER_ED, --Added by Sanjikum for Bug #4049363
ROUNDING_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
TRANSACTION_SOURCE_NUM, RECEIPT_REF, REGISTER_TYPE, REMARKS,
VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_ID,
OPENING_BALANCE, CLOSING_BALANCE, CHARGE_ACCOUNT_ID,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
, TRANSACTION_DATE, OTHER_TAX_CREDIT, OTHER_TAX_DEBIT--Added by Sanjikum
) VALUES (
JAI_CMN_RG_23AC_II_TRXS_S.nextval, p_organization_id, r.location_id , v_fin_year, 0, v_slno,
--Added by Sanjikum for Bug #4049363
v_rounded_cr_rg23_amount.basic, v_rounded_dr_rg23_amount.basic,
v_rounded_cr_rg23_amount.additional, v_rounded_dr_rg23_amount.additional,
v_rounded_cr_rg23_amount.additional_cvd, v_rounded_dr_rg23_amount.additional_cvd,/* added for the enhancement 5228046 */
v_rounded_cr_rg23_amount.other, v_rounded_dr_rg23_amount.other,
-1, r.excise_invoice_no, r.excise_invoice_date,
18, r.shipment_header_id, v_register_type, 'Rounding Entry for Receipt No:'||v_receipt_num,
v_vendor_id, v_vendor_site_id, NULL, NULL,
v_rg23_balance, v_rg23_balance + v_rounded_amount_rg23, v_rg_account_id,
SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login
, v_today, v_rounded_cr_oth_amount.total, v_rounded_dr_oth_amount.total
) RETURNING register_id INTO v_register_id_part_ii;
SELECT DECODE(i, 1, jai_constants.tax_type_exc_edu_cess, 2, jai_constants.tax_type_cvd_edu_cess) tax_type,
DECODE(i, 1, v_rounded_dr_oth_amount.excise_edu_cess, 2, v_rounded_dr_oth_amount.cvd_edu_cess) dr_cess_amount,
DECODE(i, 1, v_rounded_cr_oth_amount.excise_edu_cess, 2, v_rounded_cr_oth_amount.cvd_edu_cess) cr_cess_amount
INTO v_tax_type, v_dr_amt, v_cr_amt
FROM dual;
INSERT INTO JAI_CMN_RG_OTHERS
(RG_OTHER_ID,
SOURCE_TYPE,
SOURCE_REGISTER,
SOURCE_REGISTER_ID,
TAX_TYPE,
DEBIT,
CREDIT,
OPENING_BALANCE,
CLOSING_BALANCE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES
(JAI_CMN_RG_OTHERS_S.nextval,
1,
DECODE(v_register_type,'A','RG23A_P2','C','RG23C_P2'),/*for bug 5054176*/
v_register_id_part_ii,
v_tax_type,
v_dr_amt,
v_cr_amt,
NULL,
NULL,
v_created_by,
sysdate,
v_created_by,
sysdate,
v_last_update_login);
jai_cmn_rg_period_bals_pkg.adjust_rounding(v_register_id_part_ii, v_period_balance_id, v_no_of_periods_updated);
INSERT INTO JAI_CMN_RG_ROUND_HDRS(
ROUNDING_ID, SOURCE_HEADER_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
EXCISE_AMT_BEFORE_ROUNDING, EXCISE_AMT_AFTER_ROUNDING,
--Added the below 3 by Sanjikum for Bug #4049363
BASIC_ED, ROUNDED_BASIC_ED,
ADDITIONAL_ED, ROUNDED_ADDITIONAL_ED,
ADDITIONAL_CVD, ROUNDED_ADDITIONAL_CVD, /* ADDED FOR THE BUG 5228046 -ADDITIONAL CVD ENHANCEMENT*/
OTHER_ED, ROUNDED_OTHER_ED,
EXCISE_EDU_CESS, ROUNDED_EXCISE_EDU_CESS,
sh_excise_edu_cess, rounded_sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
CVD_EDU_CESS, ROUNDED_CVD_EDU_CESS,
sh_cvd_edu_cess, rounded_sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
REGISTER_ID, SOURCE, SRC_TRANSACTION_TYPE,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
, register, -- Vijay Shankar for Bug#4103161
program_application_id, program_id, program_login_id, request_id
) VALUES (
v_rounding_id, v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date,
v_tot_amount.total, v_tot_rounded_amount.total,
--Added the below 6 by Sanjikum for Bug #4049363
v_tot_amount.basic, v_tot_rounded_amount.basic,
v_tot_amount.additional, v_tot_rounded_amount.additional,
v_tot_amount.additional_cvd, v_tot_rounded_amount.additional_cvd,/* added for the bug 5228046 -additional cvd enhancement*/
v_tot_amount.other, v_tot_rounded_amount.other,
v_tot_amount.excise_edu_cess, v_tot_rounded_amount.excise_edu_cess,
v_tot_amount.sh_excise_edu_cess, v_tot_rounded_amount.sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
v_tot_amount.cvd_edu_cess, v_tot_rounded_amount.cvd_edu_cess,
v_tot_amount.sh_cvd_edu_cess, v_tot_rounded_amount.sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
v_register_id_part_ii, 'PO', v_transaction_type,
SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login
, LV_RG23_REGISTER, -- Vijay Shankar for Bug#4103161
fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
);
UPDATE JAI_CMN_RG_BALANCES
SET rg23a_balance = nvl(rg23a_balance, 0) + v_rounded_amount_rg23
WHERE organization_id = p_organization_id
AND location_id = r.location_id;
UPDATE JAI_CMN_RG_BALANCES
SET rg23c_balance = nvl(rg23c_balance, 0) + v_rounded_amount_rg23
WHERE organization_id = p_organization_id
AND location_id = r.location_id;
UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
SET jcrg23ac.rounding_id = v_rounding_id
WHERE jcrg23ac.organization_id = p_organization_id
AND jcrg23ac.excise_invoice_no = v_excise_invoice_no
AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND jcrg23ac.rounding_id IS NULL
AND jcrg23ac.TRANSACTION_SOURCE_NUM = 18
AND jcrg23ac.receipt_ref IN (
SELECT rt.transaction_id
FROM rcv_transactions rt
WHERE rt.shipment_header_id = v_shipment_header_id
AND ( rt.transaction_type = r.transaction_type
OR ( rt.transaction_type = lv_ttype_correct
AND exists (select 1
from rcv_transactions rt1
where rt1.transaction_id = rt.parent_transaction_id
and rt1.transaction_type = r.transaction_type)
)
)
)
AND register_id IN (
select min(register_id) from JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND excise_invoice_no = v_excise_invoice_no
AND excise_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND rounding_id IS NULL
AND TRANSACTION_SOURCE_NUM = 18
group by RECEIPT_REF);
UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
SET jcrg23ac.rounding_id = v_rounding_id
WHERE jcrg23ac.organization_id = p_organization_id
AND jcrg23ac.excise_invoice_no = v_excise_invoice_no
AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999)= nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND jcrg23ac.rounding_id IS NULL
AND jcrg23ac.TRANSACTION_SOURCE_NUM = 18
AND EXISTS (
SELECT rt.transaction_id
FROM rcv_transactions rt
WHERE rt.shipment_header_id = v_shipment_header_id
AND rt.transaction_id = jcrg23ac.receipt_ref
AND ( rt.transaction_type = r.transaction_type
OR ( rt.transaction_type = lv_ttype_correct
AND exists --'CORRECT'
(select 1
from rcv_transactions rt1
where rt1.transaction_id= rt.parent_transaction_id
and rt1.transaction_type = r.transaction_type)
)
)
);
SELECT
register_type, organization_id, location_id, excise_invoice_no, excise_invoice_date,
receipt_ref shipment_header_id,
vendor_id,vendor_site_id/*bgowrava for forward porting bug#5674376*/
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE register_id = cp_register_id;
SELECT min(register_id) register_id
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id
AND register_type = cp_register_type
AND excise_invoice_no = cp_excise_invoice_no
AND excise_invoice_date = cp_excise_invoice_date
AND inventory_item_id <> 0;
select min(register_id)
from JAI_CMN_RG_23AC_II_TRXS a
where organization_id = cp_organization_id
and location_id = cp_location_id
and excise_invoice_no = cp_excise_invoice_no
and excise_invoice_date = cp_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(cp_vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(cp_vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
and inventory_item_id <> 0
and exists (select '1'
from JAI_CMN_RG_23AC_II_TRXS
where organization_id = a.organization_id
and location_id = a.location_id
and excise_invoice_no = a.excise_invoice_no
and excise_invoice_date = a.excise_invoice_date
AND nvl(vendor_id,-999) = nvl(a.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(a.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
and inventory_item_id = a.inventory_item_id
and receipt_ref = a.RECEIPT_REF
and register_id < a.register_id
);
select min(register_id) minimum_rounding_id, max(register_id) maximum_rounding_id
from JAI_CMN_RG_23AC_II_TRXS
where inventory_item_id = 0
and excise_invoice_no = cp_excise_invoice_no
and excise_invoice_date = cp_excise_invoice_date
and nvl(vendor_id,-999) = nvl(cp_vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
and nvl(vendor_site_id,-999) = nvl(cp_vendor_site_id,-999);/*bgowrava for forward porting bug#5674376*/
FND_FILE.put_line(fnd_file.log, '2nd Claim Rounding Register_id is Selected');
FND_FILE.put_line(fnd_file.log, '1st Claim Rounding Register_id is Selected');
end if; /* 2nd claim rounding id is selected */
FND_FILE.put_line(fnd_file.log, 'Minimum Register_id is Selected');
SELECt source_header_id shipment_header_id
FROM JAI_CMN_RG_ROUND_HDRS
WHERE rounding_id = cp_rounding_id;
SELECt receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = cp_shipment_header_id;
SELECT gd.period_name
FROM gl_ledgers gle,gl_periods gd
WHERE gle.ledger_id = cp_set_of_books_id
AND gd.period_set_name = gle.period_set_name
AND trunc(p_transaction_date) BETWEEN gd.start_date and gd.end_date
AND adjustment_period_flag='N';
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
) VALUES ( JAI_RCV_JOURNAL_ENTRIES_S.nextval,
v_organization_code, p_receipt_num, p_transaction_id, sysdate, p_transaction_date,
p_shipment_line_id, p_acct_type, p_acct_nature, p_source, p_category,
p_code_combination_id, p_entered_dr, p_entered_cr, v_transaction_type, v_period_name,
p_created_by, p_currency_code, p_currency_conversion_type, p_currency_conversion_date,
p_currency_conversion_rate
);
v_last_update_login NUMBER ;--File.Sql.35 Cbabu := nvl(FND_GLOBAL.LOGIN_ID,- 1);
SELECT vendor_id, vendor_site_id, receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id;
SELECT slno, closing_balance
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND register_type = p_register_type
AND slno = (SELECT max(slno) slno
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND register_type = p_register_type);
SELECT max(fin_year)
FROM JAI_CMN_FIN_YEARS
WHERE organization_id = p_organization_id
AND fin_active_flag = 'Y';
SELECT rg_rounding_account_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND ( location_id IS NULL OR location_id = 0);
SELECT modvat_rm_account_id, modvat_cg_account_id, modvat_pla_account_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id;
SELECT qty_received
FROM JAI_RCV_LINES
WHERE shipment_line_id = cp_shipment_line_id;
SELECT --Added the 6 columns by Sanjikum for Bug #4049363
sum(decode(upper(b.tax_type), 'EXCISE', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) excise_amt,
sum(decode(upper(b.tax_type), 'ADDL. EXCISE', b.tax_amount * nvl(c.mod_cr_percentage, 0),
'CVD', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) additional_excise_amt,
sum(decode(upper(b.tax_type), jai_constants.tax_type_add_cvd, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) additional_cvd,/*5228046 Addtional cvd Enhancement*/
sum(decode(upper(b.tax_type), 'OTHER EXCISE', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) other_excise_amt,
sum(decode(b.tax_type, jai_constants.tax_type_exc_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) excise_edu_cess_amt,
sum(decode(b.tax_type, jai_constants.tax_type_cvd_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) cvd_edu_cess_amt ,
sum(decode(b.tax_type, jai_constants.tax_type_sh_exc_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) sh_excise_edu_cess_amt, -- Date 16/04/2007 by sacsethi for Bug#5989740
sum(decode(b.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) sh_cvd_edu_cess_amt
FROM JAI_RCV_LINE_TAXES b, JAI_CMN_TAXES_ALL c
WHERE b.shipment_line_id = cp_shipment_line_id
AND b.tax_id = c.tax_id
AND upper(b.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',JAI_CONSTANTS.tax_type_add_cvd,
jai_constants.tax_type_exc_edu_cess, jai_constants.tax_type_cvd_edu_cess,
jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess -- Date 16/04/2007 by sacsethi for Bug#5989740
)
AND b.modvat_flag = 'Y';
SELECT max(rounding_id) rounding_id,
count(1)
FROM JAI_CMN_RG_ROUND_HDRS
WHERE source_header_id = p_source_header_id
AND excise_invoice_no = p_excise_invoice_no
AND excise_invoice_date = p_excise_invoice_date
AND src_transaction_type = p_transaction_type
GROUP BY rounding_id, excise_invoice_no, excise_invoice_date;
select excise_batch_group_id
from jai_rcv_rtv_batch_trxs
where transaction_id = cpn_transaction_id;
v_no_of_periods_updated NUMBER(15);
v_last_update_login := nvl(FND_GLOBAL.LOGIN_ID,- 1);
select
LV_RG23_REGISTER register,
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. b.shipment_header_id shipment_header_id,
min(b.shipment_header_id) min_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
count( distinct shipment_header_id) cnt_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
a.excise_invoice_no excise_invoice_no,
a.excise_invoice_date excise_invoice_date,
a.vendor_id Vendor_id,/*bgowrava for forward porting bug#5674376*/
a.vendor_site_id vendor_site_id,/*bgowrava for forward porting bug#5674376*/
p_transaction_type transaction_type,
max(to_number(a.RECEIPT_REF)) rcv_transaction_id,
min(a.location_id) location_id,
nvl(sum(a.cr_basic_ed), 0) cr_basic_ed,
nvl(sum(a.cr_additional_ed), 0) cr_additional_ed,
nvl(sum(a.cr_additional_cvd),0) cr_additional_cvd,/*5228046 Addtional cvd Enhancement*/
nvl(sum(a.cr_other_ed), 0) cr_other_ed,
nvl(sum(a.dr_basic_ed), 0) dr_basic_ed,
nvl(sum(a.dr_additional_ed), 0) dr_additional_ed,
nvl(sum(a.dr_additional_cvd),0) dr_additional_cvd,/*5228046 Addtional cvd Enhancement*/
nvl(sum(a.dr_other_ed), 0) dr_other_ed,
nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.credit, 0)), 0) cr_exc_edu_cess,
nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.credit, 0)), 0) cr_cvd_edu_cess,
nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_exc_edu_cess, c.credit, 0)), 0) cr_sh_exc_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, c.credit, 0)), 0) cr_sh_cvd_edu_cess,
nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.debit, 0)), 0) dr_exc_edu_cess,
nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.debit, 0)), 0) dr_cvd_edu_cess,
nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_exc_edu_cess, c.debit, 0)), 0) dr_sh_exc_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, c.debit, 0)), 0) dr_sh_cvd_edu_cess,
null transaction_date
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. from JAI_CMN_RG_23AC_II_TRXS a, rcv_transactions b, JAI_CMN_RG_OTHERS c
from JAI_CMN_RG_23AC_II_TRXS a, JAI_RCV_TRANSACTIONS b, JAI_CMN_RG_OTHERS c
where a.RECEIPT_REF = b.transaction_id
AND a.organization_id = p_organization_id
AND c.source_type(+) = 1 -- this means register is JAI_CMN_RG_23AC_II_TRXS
AND a.register_id = c.source_register_id(+)
AND (
(p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NULL)
OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NULL
AND a.excise_invoice_date >= p_ex_invoice_from_date)
OR (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NOT NULL
AND a.excise_invoice_date <= p_ex_invoice_to_date)
OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NOT NULL
AND a.excise_invoice_date BETWEEN p_ex_invoice_from_date AND p_ex_invoice_to_date)
)
AND a.rounding_id IS NULL
AND a.TRANSACTION_SOURCE_NUM = 18
AND ( (b.transaction_type = p_transaction_type)
or (b.transaction_type = lv_ttype_correct and b.parent_transaction_type = p_transaction_type --'CORRECT'
/* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. exists (select 1 from JAI_RCV_TRANSACTIONS
where transaction_id = b.parent_transaction_id
and transaction_type = p_transaction_type)*/
)
)
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. GROUP BY b.shipment_header_id, a.excise_invoice_no, a.excise_invoice_date
GROUP BY a.excise_invoice_no, a.excise_invoice_date,
a.vendor_id , /*bgowrava for forward porting bug#5674376*/
a.vendor_site_id /*bgowrava for forward porting bug#5674376*/
UNION ALL
select
LV_PLA_REGISTER register,
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. b.shipment_header_id shipment_header_id,
min(b.shipment_header_id) min_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
count( distinct shipment_header_id) cnt_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
a.DR_INVOICE_NO excise_invoice_no,
a.dr_invoice_date excise_invoice_date,
a.vendor_id Vendor_id,/*bgowrava for forward porting bug#5674376*/
a.vendor_site_id vendor_site_id,/*bgowrava for forward porting bug#5674376*/
p_transaction_type transaction_type,
max(to_number(ref_document_id)) rcv_transaction_id,
min(a.location_id) location_id,
nvl(sum(a.cr_basic_ed),0) cr_basic_ed,
nvl(sum(a.cr_additional_ed), 0) cr_additional_ed,
to_number(null) cr_additional_cvd, /*5228046 Addtional cvd Enhancement*/
nvl(sum(a.cr_other_ed), 0) cr_other_ed,
nvl(sum(a.dr_basic_ed), 0) dr_basic_ed,
nvl(sum(a.dr_additional_ed), 0) dr_additional_ed,
to_number(null) dr_additional_cvd, /*5228046 Addtional cvd Enhancement*/
nvl(sum(a.dr_other_ed), 0) dr_other_ed,
nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.credit, 0)), 0) cr_exc_edu_cess,
nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.credit, 0)), 0) cr_cvd_edu_cess,
-- Date 16/04/2007 by sacsethi for Bug#5989740
nvl(sum( decode(c.tax_type, 'EXCISE_SH_EDU_CESS', c.credit, 0)), 0) cr_sh_exc_edu_cess,
nvl(sum( decode(c.tax_type, 'CVD_SH_EDU_CESS', c.credit, 0)), 0) cr_sh_cvd_edu_cess,
-- end
nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.debit, 0)), 0) dr_exc_edu_cess,
nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.debit, 0)), 0) dr_cvd_edu_cess,
-- Date 16/04/2007 by sacsethi for Bug#5989740
nvl(sum( decode(c.tax_type, 'EXCISE_SH_EDU_CESS', c.debit, 0)), 0) dr_sh_exc_edu_cess,
nvl(sum( decode(c.tax_type, 'CVD_SH_EDU_CESS', c.debit, 0)), 0) dr_sh_cvd_edu_cess,
-- end
max(a.transaction_date) transaction_date
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. from JAI_CMN_RG_PLA_TRXS a, rcv_transactions b, JAI_CMN_RG_OTHERS c
from JAI_CMN_RG_PLA_TRXS a, JAI_RCV_TRANSACTIONS b, JAI_CMN_RG_OTHERS c
where a.ref_document_id = b.transaction_id
AND a.organization_id = p_organization_id
AND c.source_type(+) = 2 -- this means register is JAI_CMN_RG_PLA_TRXS
AND a.register_id = c.source_register_id(+)
AND (
(p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NULL)
OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NULL
AND a.dr_invoice_date >= p_ex_invoice_from_date)
OR (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NOT NULL
AND a.dr_invoice_date <= p_ex_invoice_to_date)
OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NOT NULL
AND a.dr_invoice_date BETWEEN p_ex_invoice_from_date AND p_ex_invoice_to_date)
)
AND a.rounding_id IS NULL
AND a.TRANSACTION_SOURCE_NUM = 19
AND ( (b.transaction_type = p_transaction_type)
or (b.transaction_type = 'CORRECT' and b.parent_transaction_type = p_transaction_type
/*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. exists (select 1 from JAI_RCV_TRANSACTIONS
where transaction_id = b.parent_transaction_id
and transaction_type = p_transaction_type)*/
)
)
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. GROUP BY b.shipment_header_id, a.DR_INVOICE_NO, a.dr_invoice_date
GROUP BY a.DR_INVOICE_NO, a.dr_invoice_date,
a.vendor_id , /*bgowrava for forward porting bug#5674376*/
a.vendor_site_id /*bgowrava for forward porting bug#5674376*/
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. ORDER BY shipment_header_id
ORDER BY rcv_transaction_id
)
LOOP
BEGIN
if gb_debug then
fnd_file.put_line(fnd_file.log, '0-Amts: crBas:'||r.cr_basic_ed
||', crAdd:'||r.cr_additional_ed||', crOth:'||r.cr_other_ed
||', crExcEdu:'||r.cr_exc_edu_cess||', crCvdEdu:'||r.cr_cvd_edu_cess
||', drBas:'||r.dr_basic_ed
||', drAdd:'||r.dr_additional_ed||', drOth:'||r.dr_other_ed
||', drExcEdu:'||r.dr_exc_edu_cess||', drCvdEdu:'||r.dr_cvd_edu_cess
);
v_no_of_periods_updated := null;
fnd_file.put_line(fnd_file.log, '1-Before Update of Register');
UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
SET jcrg23ac.rounding_id = v_already_rounded_chk
WHERE jcrg23ac.excise_invoice_no = v_excise_invoice_no
AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND jcrg23ac.organization_id = p_organization_id
AND jcrg23ac.rounding_id IS NULL
AND jcrg23ac.transaction_source_num = 18
AND exists (select 1 from JAI_RCV_TRANSACTIONS jrt
/* Bug 4930048. Added by Lakshmi Gopalsami
Changed transaction_source_num to transaction_id
*/
where jrt.transaction_id = jcrg23ac.receipt_ref
and (jrt.transaction_type = p_transaction_type or
jrt.parent_transaction_type = p_transaction_type) );
UPDATE JAI_CMN_RG_PLA_TRXS jcpla
SET jcpla.rounding_id = v_already_rounded_chk
WHERE jcpla.DR_INVOICE_NO = v_excise_invoice_no
AND jcpla.dr_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND jcpla.organization_id = p_organization_id
AND jcpla.rounding_id IS NULL
AND jcpla.TRANSACTION_SOURCE_NUM = 19
AND exists (select 1 from JAI_RCV_TRANSACTIONS jrt
/* Bug 4930048. Added by Lakshmi Gopalsami
Changed transaction_source_num to transaction_id
*/
where jrt.transaction_id = jcpla.ref_document_id
and (jrt.transaction_type = p_transaction_type or
jrt.parent_transaction_type = p_transaction_type) );
SELECT JAI_CMN_RG_ROUND_HDRS_S.nextval INTO v_rounding_id FROM dual;
FOR line IN ( SELECT a.shipment_line_id, a.transaction_id, a.item_class,
a.transaction_type, a.parent_transaction_type, a.quantity
FROM JAI_RCV_TRANSACTIONS a,
JAI_RCV_CENVAT_CLAIMS b,/*bgowrava for forward porting bug#5674376*/
RCV_TRANSACTIONS c,/*bgowrava for forward porting bug#5674376*/
jai_rcv_rtv_batch_trxs d
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE a.shipment_header_id = v_shipment_header_id
WHERE a.transaction_id = d.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
and d.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
AND (a.transaction_type = p_transaction_type
or (a.transaction_type = 'CORRECT' and a.parent_transaction_type = p_transaction_type))
AND (
(nvl(b.vendor_id,-999) = nvl(r.vendor_id,-999)
AND nvl(b.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
AND b.vendor_changed_flag = 'Y' )
OR
(nvl(c.vendor_id,-999) = nvl(r.vendor_id,-999)
AND nvl(c.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
AND b.vendor_changed_flag = 'N' )
) /*bgowrava for forward porting bug#5674376*/
AND a.excise_invoice_no = v_excise_invoice_no
AND a.excise_invoice_date = v_excise_invoice_date
)
LOOP
ln_receive_amount := v_zero_record;
fnd_file.put_line(fnd_file.log, '2-Before Insert into Entry Lines');
INSERT INTO JAI_CMN_RG_ROUND_LINES (ROUNDING_LINE_ID,
ROUNDING_ID, SRC_LINE_ID, SRC_TRANSACTION_ID,
EXCISE_AMT,
BASIC_ED, ADDITIONAL_ED,ADDITIONAL_CVD , OTHER_ED,
EXCISE_EDU_CESS, CVD_EDU_CESS,
sh_excise_edu_cess, sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
ITEM_CLASS, CREATION_DATE, CREATED_BY,
program_application_id, program_id, program_login_id, request_id
) VALUES ( JAI_CMN_RG_ROUND_LINES_S.nextval,
v_rounding_id, line.shipment_line_id, line.transaction_id,
ln_cenvat_amount.total,
ln_cenvat_amount.basic,
ln_cenvat_amount.additional,
ln_cenvat_amount.additional_cvd,/*5228046 Addtional cvd Enhancement*/
ln_cenvat_amount.other,
ln_cenvat_amount.excise_edu_cess, ln_cenvat_amount.cvd_edu_cess,
ln_cenvat_amount.sh_excise_edu_cess, ln_cenvat_amount.sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
line.item_class, SYSDATE, v_created_by,
fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
);
fnd_file.put_line(fnd_file.log, '3-Before Update of Register with 0 Rounding');
UPDATE JAI_CMN_RG_23AC_II_TRXS aa
SET rounding_id = 0
WHERE organization_id = p_organization_id
AND excise_invoice_no = v_excise_invoice_no
AND excise_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND rounding_id IS NULL
AND TRANSACTION_SOURCE_NUM = 18
AND EXISTS (
SELECT 1 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE BB.shipment_header_id = v_shipment_header_id
WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
AND bb.transaction_id = aa.receipt_ref
AND ( bb.transaction_type = r.transaction_type or bb.parent_transaction_type = r.transaction_type)
);
UPDATE JAI_CMN_RG_PLA_TRXS aa
SET rounding_id = 0
WHERE organization_id = p_organization_id
AND DR_INVOICE_NO = v_excise_invoice_no
AND dr_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND rounding_id IS NULL
AND TRANSACTION_SOURCE_NUM = 19
AND EXISTS (
SELECT 1 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE BB.shipment_header_id = v_shipment_header_id
WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
AND bb.transaction_id = aa.ref_document_id
AND ( bb.transaction_type = r.transaction_type or bb.parent_transaction_type = r.transaction_type)
);
DELETE FROM JAI_CMN_RG_ROUND_LINES WHERE rounding_id = v_rounding_id;
fnd_file.put_line(fnd_file.log, '4-Before Insert of Rounding Entry into Register:'||r.register );
jai_cmn_rg_pla_trxs_pkg.insert_row(
p_register_id => v_register_id_part_ii,
p_tr6_challan_no => NULL,
p_tr6_challan_date => NULL,
p_cr_basic_ed => v_rounded_cr_rg23_amount.basic,
p_cr_additional_ed => v_rounded_cr_rg23_amount.additional,
p_cr_other_ed => v_rounded_cr_rg23_amount.other,
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_ref_document_id => r.shipment_header_id,
p_ref_document_id => ln_rtv_excise_batch_group_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
p_ref_document_date => trunc(sysdate), -- CHK
p_dr_invoice_id => r.excise_invoice_no,
p_dr_invoice_date => r.excise_invoice_date,
p_dr_basic_ed => v_rounded_dr_rg23_amount.basic,
p_dr_additional_ed => v_rounded_dr_rg23_amount.additional,
p_dr_other_ed => v_rounded_dr_rg23_amount.other,
p_organization_id => p_organization_id,
p_location_id => r.location_id,
p_bank_branch_id => NULL,
p_entry_date => NULL,
p_inventory_item_id => 0,
p_vendor_cust_flag => 'V',
p_vendor_id => v_vendor_id,
p_vendor_site_id => v_vendor_site_id,
p_excise_invoice_no => r.excise_invoice_no,
p_remarks => 'Rounding Entry for Receipt No:'||v_receipt_num,
p_transaction_date => r.transaction_date,
p_charge_account_id => NULL,
p_other_tax_credit => v_rounded_cr_oth_amount.total,
p_other_tax_debit => v_rounded_dr_oth_amount.total,
p_transaction_type => 'RETURN TO VENDOR',
p_transaction_source => null,
p_called_from => 'rg_rounding_pkg.do_rtv_rounding',
p_simulate_flag => 'N',
p_process_status => lv_process_status,
p_process_message => lv_process_message,
p_rounding_id => -1
);
INSERT INTO JAI_CMN_RG_23AC_II_TRXS(
REGISTER_ID, ORGANIZATION_ID, LOCATION_ID, FIN_YEAR, INVENTORY_ITEM_ID, SLNO,
CR_BASIC_ED, DR_BASIC_ED,
CR_ADDITIONAL_ED, DR_ADDITIONAL_ED,
CR_ADDITIONAL_CVD, DR_ADDITIONAL_CVD,/*5228046 ADDITIONAL CVD ENHANCEMENT*/
CR_OTHER_ED, DR_OTHER_ED,
ROUNDING_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
TRANSACTION_SOURCE_NUM, RECEIPT_REF, REGISTER_TYPE, REMARKS,
VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_ID,
OPENING_BALANCE, CLOSING_BALANCE, CHARGE_ACCOUNT_ID,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
, TRANSACTION_DATE, OTHER_TAX_CREDIT, OTHER_TAX_DEBIT
) VALUES (
JAI_CMN_RG_23AC_II_TRXS_S.nextval, p_organization_id, r.location_id , v_fin_year, 0, v_slno,
v_rounded_cr_rg23_amount.basic, v_rounded_dr_rg23_amount.basic,
v_rounded_cr_rg23_amount.additional, v_rounded_dr_rg23_amount.additional,
v_rounded_cr_rg23_amount.additional_cvd, v_rounded_dr_rg23_amount.additional_cvd,/*5228046 Additional cvd Enhancement*/
v_rounded_cr_rg23_amount.other, v_rounded_dr_rg23_amount.other,
-1, r.excise_invoice_no, r.excise_invoice_date,
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. 18, r.shipment_header_id, v_register_type, 'Rounding Entry for Receipt No:'||v_receipt_num,
18, ln_rtv_excise_batch_group_id, v_register_type, 'Rounding Entry for Receipt No:'||v_receipt_num,
v_vendor_id, v_vendor_site_id, NULL, NULL,
v_rg23_balance, v_rg23_balance + v_rounded_amount_rg23, v_rg_account_id,
SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login
, r.transaction_date, v_rounded_cr_oth_amount.total, v_rounded_dr_oth_amount.total
) RETURNING register_id INTO v_register_id_part_ii;
fnd_file.put_line(fnd_file.log, '5-Before Insert of RG Others.Amts:dr-'||v_dr_amt||',cr-'||v_cr_amt );
INSERT INTO JAI_CMN_RG_OTHERS (
RG_OTHER_ID,
SOURCE_TYPE,
SOURCE_REGISTER,
SOURCE_REGISTER_ID,
TAX_TYPE,
DEBIT,
CREDIT,
OPENING_BALANCE,
CLOSING_BALANCE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES (
JAI_CMN_RG_OTHERS_S.nextval,
DECODE(r.register, LV_PLA_REGISTER, 2, 1),
DECODE(r.register, LV_PLA_REGISTER, 'PLA', decode(v_register_type,'A','RG23A_P2','C','RG23C_P2')),/*for bug 5054176*/
v_register_id_part_ii,
v_tax_type,
v_dr_amt,
v_cr_amt,
NULL,
NULL,
v_created_by,
sysdate,
v_created_by,
sysdate,
v_last_update_login
);
jai_cmn_rg_period_bals_pkg.adjust_rounding(v_register_id_part_ii, v_period_balance_id, v_no_of_periods_updated);
fnd_file.put_line(fnd_file.log, '6-Before Insert into Rounding Entries. Id:'||v_rounding_id);
INSERT INTO JAI_CMN_RG_ROUND_HDRS(
ROUNDING_ID, SOURCE_HEADER_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
EXCISE_AMT_BEFORE_ROUNDING, EXCISE_AMT_AFTER_ROUNDING,
BASIC_ED, ROUNDED_BASIC_ED,
ADDITIONAL_ED, ROUNDED_ADDITIONAL_ED,
ADDITIONAL_CVD, ROUNDED_ADDITIONAL_CVD,/*5228046 ADDITIONAL CVD ENHANCEMENT*/
OTHER_ED, ROUNDED_OTHER_ED,
EXCISE_EDU_CESS, ROUNDED_EXCISE_EDU_CESS,
CVD_EDU_CESS, ROUNDED_CVD_EDU_CESS,
sh_excise_edu_cess, rounded_sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
sh_cvd_edu_cess, rounded_sh_cvd_edu_cess, -- SH Cess column is added
register, REGISTER_ID, SOURCE, SRC_TRANSACTION_TYPE,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
program_application_id, program_id, program_login_id, request_id
) VALUES (
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. v_rounding_id, v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date,
v_rounding_id, ln_rtv_excise_batch_group_id, v_excise_invoice_no, v_excise_invoice_date,
v_tot_amount.total, v_tot_rounded_amount.total,
v_tot_amount.basic, v_tot_rounded_amount.basic,
v_tot_amount.additional, v_tot_rounded_amount.additional,
V_TOT_AMOUNT.ADDITIONAL_CVD, V_TOT_ROUNDED_AMOUNT.ADDITIONAL_CVD, /*5228046 ADDITIONAL CVD ENHANCEMENT*/
v_tot_amount.other, v_tot_rounded_amount.other,
v_tot_amount.excise_edu_cess, v_tot_rounded_amount.excise_edu_cess,
v_tot_amount.cvd_edu_cess, v_tot_rounded_amount.cvd_edu_cess,
v_tot_amount.sh_excise_edu_cess, v_tot_rounded_amount.sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
v_tot_amount.sh_cvd_edu_cess, v_tot_rounded_amount.sh_cvd_edu_cess,
r.register, v_register_id_part_ii, 'PO', p_transaction_type,
SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login,
fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
);
UPDATE JAI_CMN_RG_BALANCES
SET rg23a_balance = nvl(rg23a_balance, 0) + v_rounded_amount_rg23
WHERE organization_id = p_organization_id
AND location_id = r.location_id;
UPDATE JAI_CMN_RG_BALANCES
SET rg23c_balance = nvl(rg23c_balance, 0) + v_rounded_amount_rg23
WHERE organization_id = p_organization_id
AND location_id = r.location_id;
/* this update is already taken in jai_cmn_rg_pla_trxs_pkg.insert_row call*/
NULL;
UPDATE JAI_CMN_RG_23AC_II_TRXS aa
SET rounding_id = v_rounding_id
WHERE organization_id = p_organization_id
AND excise_invoice_no = v_excise_invoice_no
AND excise_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND rounding_id IS NULL
AND TRANSACTION_SOURCE_NUM = 18
AND EXISTS (
SELECT BB.transaction_id
FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE bb.shipment_header_id = v_shipment_header_id
WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
AND bb.transaction_id = aa.receipt_ref
AND ( bb.transaction_type = r.transaction_type
OR (bb.transaction_type = lv_ttype_correct and bb.parent_transaction_type = r.transaction_type) --'CORRECT'
)
);
UPDATE JAI_CMN_RG_PLA_TRXS aa
SET rounding_id = v_rounding_id
WHERE organization_id = p_organization_id
AND DR_INVOICE_NO = v_excise_invoice_no
AND dr_invoice_date = v_excise_invoice_date
AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
AND rounding_id IS NULL
AND TRANSACTION_SOURCE_NUM = 19
AND exists (
SELECT BB.transaction_id
FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE bb.shipment_header_id = v_shipment_header_id
WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
AND bb.transaction_id = aa.ref_document_id
AND ( bb.transaction_type = r.transaction_type
OR (bb.transaction_type = lv_ttype_correct and bb.parent_transaction_type = r.transaction_type)
)
);