[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Added the NVL for column sum(jsptl.func_tax_amount) in the Select
(Functional) Dependency Due to This Bug
--------------------------
jai_rcv_rcv_rtv.plb (120.3)
jai_rcv_trx_prc.plb (120.2)
19-aUG-2005 Bug4562791. Added by Lakshmi Gopalsami Version 120.3
Added gl_accounting_date as a package variable.
Passing this variable insted of NULL for gl accounting date.
Dependencies(Functional nd Technical)
------------
jai_om_rg.pls 120.2
jai_om_wsh.plb 120.3
02-DEC-2005 Bug 4765347, Added by aiyer for Version 120.4
Added few more fnd_file statements.
Dependencies Due to this issue :-
Yes, please refer the future dependencies section.
30-OCT-2006 SACSETHI for bug 5228046, File version 120.9
Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
26-FEB-2007 SSAWANT , File version 120.11
Forward porting the change in 11.5 bug 4714518 to R12 bug no 4724137
27. 16/04/2007 bduvarag for the Bug#5989740, file version 120.12
Forward porting the changes done in 11i bug#5907436
28. 04/06/2007 sacsethi for bug 6109941 , File version 120.13
1. Cursor c_get_ar_cess_rate is removed for bug 5228046 forward porting bug
2. in procedure ja_in_pla_entry , sh_cess_amoumnt was missing
29 17/06/2007 ssumaith - bug# 6131804 - bond register is not gettnig hit in INR for foreign currency trxs.
Code changes are done in this package for handling the cess amount.
30. 28/06/2007 CSahoo - BUG#6155839, File Version 120.16
replaced RG Register Data Entry by jai_constants.je_category_rg_entry
31. 02/07/2007 vkaranam - BUG#6159579, File Version 120.17
1.In Procedure ja_in_cess_entries while calling ja_in_om_cess_register_entries p_delivery_detail_id parameter is not passed.
32. 05/07/2007 kunkumar for Bug#5745729 file version 120.18
Modified the cursors in the procedure ja_in_om_cess_register_entries so as to be in sync with the latest
version in R11i.Also there are changes to the body of the procedure.
33. 04/12/2007 ssumaith - bug# 6650203 - file version 120.8.12000000.4
Issue :
When the excise invoice number is having characters in it,the bond register transaction is failing as
the excise invoice number was being inserted into the picking_header_id field in the JAI_OM_OE_BOND_TRXS table.
the picking header id field was of type number and hence a character insert is causing an invalid number error.
Fix :
Made the following changes
a) in the jai_om_rg_pkg, when the insert into the JAI_OM_OE_BOND_TRXS table happens through the ja_in_register_txn_entry procedure , insert of excise invoice number into picking header id has been removed.
Instead the picking_line_id is stamped with the delivery_id / customer_trx_line_id in case of OM / AR respectively.
34. 14-May-2008 Changes by nprashar for bug # 6710747.
Issue:INTER-ORGANIZATION TRANSFER WITH EXCISE TAXES FAILS
Reason:
p_header_id parameter is used to insert the excise_invoice_id value of ja_in_rg23_part_i table.
if excise_invoice_no generated contains characters then while calling ja_in_rg23_part_i procedure ,the calling prg
errors out.
Fix:
Changed the ja_in_rg23_part_i entry procedure p_header_id parameter to varchar2 type.
35 23-Jun-2008 Changed by JMEENA for bug#7172215
1. Added condition IF NVL(ln_Cess_amount,0) > 0 before calling ja_in_cess_acctg_entries in the procedure ja_in_cess_register_entries.
2. Added log messages before RAISE_APPLICATION_ERROR to print in the log file.
36. 01-Aug-2008 Changed by JMEENA for bug#7277543
Added log messages to print in log file for missing accounts setup informations.
37 13-oct-2008 bug#7479016
Forward ported the changes done in 5597403
File Version : 120.8.12000000.8/120.24
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
----------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
jai_om_rg_pkg.plb
----------------------------------------------------------------------------------------------------------------------------------------------------
120.4 4765347 JAIITMCL.fmb 120.9 Aiyer 02-DEC-2005
jai_om_wsh.plb 120.5 Aiyer 02-DEC-2005
---------------------------------------------------------------------------------------------------------------------------------------------------- */
-- start additions by ssumaith - bug#3817625
/***************************** START JA_IN_CESS_ENTRIES *******************************************************************/
Procedure JA_IN_CESS_ENTRIES
(
p_organization_id number ,
p_location_id number ,
p_delivery_detail_id number ,
p_intransit_inv_acct number ,
p_intercompany_rcvbl number ,
p_intercompany_paybl number ,
p_fob_point number ,
p_currency_code varchar2 ,
p_source_name varchar2 ,
p_category_name varchar2 ,
p_created_by Number ,
P_REF_10 varchar2 ,
P_REF_23 varchar2 ,
P_REF_24 varchar2 ,
P_REF_25 varchar2 ,
P_REF_26 varchar2
)
IS
-- A/c Entries for CESS needs to be passed as follows:
-- Dr Intransit Material Value
-- Cr CESS Payable Paid
-- It will be called from ja_in_pla_entry and ja_in_rg23_part_ii_entry because only they have the
-- amount impact for excise registers
--If the FOB point is set to SHIPMENT, the following entry also needs to be passed :
-- Intercompany Receivable Cess amt
-- Intercompany Payables cess amt
cursor c_delivery_cur(cp_delivery_Detail_id number) is
select delivery_id , org_id
from JAI_OM_WSH_LINES_ALL
where delivery_detail_id = cp_delivery_Detail_id;
select sum(a.tax_amount)
from JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
where delivery_detail_id in
(select delivery_detail_id
from JAI_OM_WSH_LINES_ALL
where delivery_id = cp_delivery_id
)
and a.tax_id = b.tax_id
and upper(b.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS);
select sum(a.tax_amount)
from JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
where delivery_detail_id in
(select delivery_detail_id
from JAI_OM_WSH_LINES_ALL
where delivery_id = cp_delivery_id
)
and a.tax_id = b.tax_id
and upper(b.tax_type) in (JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS);
select cess_paid_payable_account_id
from JAI_CMN_INVENTORY_ORGS
where organization_id = cp_organization_id
and location_id = cp_location_id;
select sh_cess_paid_payable_acct_id
from JAI_CMN_INVENTORY_ORGS
where organization_id = cp_organization_id
and location_id = cp_location_id;
This was done because if there is no cess amount, there is no need to call the insert row procedure
*/
CURSOR c_tax_type_rec IS
SELECT jtc.tax_type , round(sum(jsptl.func_tax_amount),0) tax_amount --rchandan for bug#4388950
FROM JAI_OM_WSH_LINE_TAXES jsptl ,
JAI_CMN_TAXES_ALL jtc
WHERE jtc.tax_id = jsptl.tax_id
--Modified for bug5747126
AND delivery_detail_id = p_delivery_detail_id
AND (( upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS) and p_cess_type = 'EXC') --Date 12/03/2007 by SACSETHI for bug#5907436
OR
( upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_sh_EXC_EDU_CESS) and p_cess_type = 'SH') )
GROUP BY jtc.tax_type
HAVING SUM(jsptl.func_tax_amount) <> 0;
(SELECT delivery_detail_id
FROM ja_in_so_picking_lines jspl
WHERE jspl.delivery_id = p_delivery_id
)
AND p_source_type <> 3
)
)*/
-- ended, Harshita for Bug 4714518
-- foll cursor added by ssumaith - bug# 5747126 - one off
CURSOR c_tax_type_for_delivery_rec IS
SELECT jtc.tax_type , round(sum(jsptl.func_tax_amount),0) tax_amount
FROM JAI_OM_WSH_LINE_TAXES jsptl ,
JAI_OM_WSH_LINES_ALL jspl ,
JAI_CMN_TAXES_ALL jtc ,
JAI_INV_ITM_SETUPS jmsi /* Added by Ramananda for bug#5912620*/
WHERE jtc.tax_id = jsptl.tax_id
AND jspl.delivery_id = p_delivery_id
AND jspl.delivery_detail_id = jsptl.delivery_detail_id
AND (( upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS) and p_cess_type = 'EXC') --Date 12/03/2007 by SACSETHI for bug#5907436
OR
( upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_sh_EXC_EDU_CESS) and p_cess_type = 'SH') )
/* Added for bug#5912620, Starts */
AND jmsi.inventory_item_id = jspl.inventory_item_id
AND jmsi.organization_id = jspl.organization_id
AND jmsi.excise_flag = 'Y'
/* Added for bug#5912620, Endseft */
GROUP BY jtc.tax_type
HAVING SUM(jsptl.func_tax_amount) <> 0;
Fnd_File.PUT_LINE(Fnd_File.LOG, 'before calling jai_Rg_others_pkg.insert_row');
jai_cmn_rg_others_pkg.insert_row(
P_SOURCE_TYPE => p_source_type ,
P_SOURCE_NAME => p_source_name ,
P_SOURCE_ID => p_source_id ,
P_TAX_TYPE => tax_type_rec.tax_type ,
DEBIT_AMT => tax_type_rec.tax_amount,
CREDIT_AMT => NULL ,
P_PROCESS_FLAG => lv_process_flag ,
P_PROCESS_MSG => lv_process_msg
);
Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling jai_Rg_others_pkg.insert_row with P_PROCESS_FLAG => ' || lv_process_flag);
Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling jai_Rg_others_pkg.insert_row with P_PROCESS_MSG => ' || lv_process_msg);
Fnd_File.PUT_LINE(Fnd_File.LOG, 'before calling jai_Rg_others_pkg.insert_row');
jai_cmn_rg_others_pkg.insert_row(
P_SOURCE_TYPE => p_source_type ,
P_SOURCE_NAME => p_source_name ,
P_SOURCE_ID => p_source_id ,
P_TAX_TYPE => tax_type_rec.tax_type ,
DEBIT_AMT => tax_type_rec.tax_amount,
CREDIT_AMT => NULL ,
P_PROCESS_FLAG => lv_process_flag ,
P_PROCESS_MSG => lv_process_msg
);
Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling jai_Rg_others_pkg.insert_row with P_PROCESS_FLAG => ' || lv_process_flag);
Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling jai_Rg_others_pkg.insert_row with P_PROCESS_MSG => ' || lv_process_msg);
This was done because if there is no cess amount, there is no need to call the insert row procedure
*/
CURSOR c_tax_type_rec IS
SELECT jtc.tax_type , sum(jrctl.func_tax_amount) tax_amount
FROM JAI_AR_TRX_TAX_LINES jrctl ,
JAI_CMN_TAXES_ALL jtc
WHERE jtc.tax_id = jrctl.tax_id
AND link_to_cust_trx_line_id = p_customer_trx_id -- added, aiyer for Bug 4541303 /*Bug 5989740 bduvarag*/
AND (( upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS) and p_cess_type = 'EXC')
OR
( upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_sh_EXC_EDU_CESS) and p_cess_type = 'SH') )
GROUP BY jtc.tax_type
HAVING SUM(jrctl.func_tax_amount) <> 0;
jai_cmn_rg_others_pkg.insert_row(
P_SOURCE_TYPE => p_source_type ,
P_SOURCE_NAME => p_source_name ,
P_SOURCE_ID => p_source_id ,
P_TAX_TYPE => tax_type_rec.tax_type ,
DEBIT_AMT => tax_type_rec.tax_amount,
CREDIT_AMT => NULL ,
P_PROCESS_FLAG => lv_process_flag ,
P_PROCESS_MSG => lv_process_msg
);
select delivery_id , organization_id
from JAI_OM_WSH_LINES_ALL
where delivery_detail_id = p_transaction_hdr_id;
select customer_trx_id
from JAI_AR_TRX_LINES
where customer_trx_line_id = p_transaction_hdr_id;
SELECT source_header_id, source_line_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT ship_from_org_id, order_source_id, source_document_id, source_document_line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND line_id = p_line_id;
SELECT destination_organization_id , deliver_to_location_id /* deliver_to_location_id added by ssumaith - to handle trading to trading ISO */
FROM po_requisition_lines_all
WHERE requisition_header_id = p_requisition_header_id
AND requisition_line_id = p_requisition_line_id;
SELECT intransit_type, fob_point, interorg_receivables_account, interorg_payables_account, intransit_inv_account
FROM mtl_interorg_parameters
WHERE from_organization_id = p_from_org_id
AND to_organization_id = p_to_org_id;
SELECT excise_rcvble_account ,
excise_23d_account,
excise_in_rg23d,
Trading,
Manufacturing
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id;
SELECT delivery_id
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_detail_id = cp_delivery_detail_id;
SELECT sum(a.tax_amount)
FROM JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
WHERE delivery_detail_id in
(SELECt delivery_detail_id
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_id = cp_delivery_id
)
AND a.tax_id = b.tax_id
AND upper(b.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS);
p_last_update_date DATE ,
p_last_updated_by NUMBER ,
p_last_update_login NUMBER ,
p_assessable_value NUMBER ,
p_cess_amt JAI_CMN_RG_I_TRXS.CESS_AMT%TYPE DEFAULT NULL ,
p_sh_cess_amt JAI_CMN_RG_I_TRXS.SH_CESS_AMT%TYPE DEFAULT NULL , /*Bug 5989740 bduvarag*/
p_source JAI_CMN_RG_I_TRXS.SOURCE%TYPE DEFAULT NULL /*Parameters p_cess_amt and p_source added by aiyer for the bug 4566054 */
) IS
--parameter for assessable value added
v_serial_no NUMBER := 0;
SELECT excise_exempt_type
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id AND
organization_id = p_org_id;
SELECT NVL(MAX(slno),0), (NVL(MAX(slno),0) + 1)
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = p_org_id AND
location_id = p_location_id AND
inventory_item_id = p_inventory_item_id AND
fin_year = p_fin_year;
SELECT NVL(balance_packed,0), NVL(balance_loose,0)
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = p_org_id AND
location_id = p_location_id AND
inventory_item_id = p_inventory_item_id AND
fin_year = p_fin_year AND
slno = p_previous_serial_no;
SELECT excise_duty_range,excise_duty_division
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = p_customer_id
AND address_id = (SELECT cust_acct_site_id -- address_id
FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
WHERE A.site_use_id = p_customer_site_id);
SELECT item_class -- Commented attribute3 by Brathod, For Bug# 4299606 (DFF Elimination)
FROM JAI_INV_ITM_SETUPS -- Commneted mtl_system_items by Brathod for Bug# 4299606 (DFF Elimination)
WHERE inventory_item_id=p_inventory_item_id
AND organization_id = p_org_id;
JAI_OM_WSH_LINES_ALL through a cursor c_exc_exempt_dtls. Insert into JAI_CMN_RG_I_TRXS is modified to populate specified columns
in case of CT3 transaction. following fields are added in the procedure
v_to_other_fact_n_pay_ed_qty NUMBER;
Even when cess amount is zero , still call to insert row for cess record was being done.
This was not necessary , hence call to the procedure JAI_CMN_RG_OTHERS_pkg.insert_row was done only
if the cess amount is a non zero value.
Changes are made in the following places
1. procedure ja_in_om_cess_register_entries - code change done is to add a having condition to get only those taxes where cess amount <> 0
2. procedure ja_in_ar_cess_register_entries - same as above.
Dependency due to this bug:-
None
15. 16/03/2005 ssumaith - For VAT -bug#4245053 - File Version - 115.5
For Excise Exempted transactions , cenvat reversal account is being used to hit the cess reversal entries also.
This is in line with the discussion with product management and support , that cenvat reversal account needs
to be used for cess as well .
16 26/04/2005 Brathod for Bug# 4299606 File Version 116.1
Issue:-
Item DFF Elimination
Fix:-
Changed the code that references attributeN (where N=1,2,3,4,5,15) of
mtl_system_items to corrosponding columns in JAI_INV_ITM_SETUPS
Dependency :-
IN60106 + 4239736 (Service Tax) + 4245089 (VAT)
17. 23-Aug-2005 Aiyer - Bug 4566054 (Forward porting for the 11.5 bug 4346220 ),Version 120.4
Issue :-
Rg does not show correct cess value in case of Shipment transactions.
Fix:-
Two fields cess_amt and source have been added in JAI_CMN_RG_I_TRXS table.
The cess amt and source would be populated from jai_jar_t_aru_t1 (Source -> 'AR' ) and
as 'WSH' from jai_om_wsh.plb procedure Shipment.
Corresponding changes have been done in the form JAINIRGI.fmb and JAFRMRG1.rdf .
For shipment and Ar receivable transaction currently the transaction_id is 33 and in some cases where the jai_cmn_rg_i_trxs.ref_doc_id
exactly matches the jai_om_wsh_lines_all.delivery_detail_id and jai_ar_trxs.customer_trx_id the tracking of the source
becomes very difficult hence to have a clear demarcation between WSh and AR sources hence the source field has been added.
Added 2 new parametes p_cess_amt and p_source to jai_om_rg_pkg.ja_in_rg_i_entry package.
This has been populated from this and jai_om_wsh_pkg.process_delivery procedure.
A migration script has been provided to migrate the value for cess and source.
Dependency due to this bug:-
1. Datamodel change in table JAI_CMN_RG_I_TRXS, added the cess_amt and source fields
2. Added two new parameters in jai_om_rg_pkg.ja_in_rg_i_entry procedure to insert data into JAI_CMN_RG_I_TRXS table
3. Modified the trigger jai_jar_t_aru_t1
4. Procedure jai_om_wsh_pkg.process_delivery
5. Report JAICMNRG1.rdf
6. Created a migration script to populate cess_amt and source for Shipment and Receivable transactions.
Both functional and technical dependencies exists
18. 15-Feb-2007 CSahoo Bug#5390583, File Version - 120.12
Forward Porting of 11i Bug 5357400
Issue : Excise amount not hitting bond register in functional currency.
Fix : Excise and cess amounts would hit bond register in functional currency.
Changes are done in three objects.
1. Package jai_om_rg_pkg. - Added a parameter to the ja_in_register_txn_entry called p_currency_rate
It holds the currency conversion rate which would be multiplied by the transaction amts to
get the functional amounts.
2. Package jai_jar_t.plb - In the call to the ja_in_register_txn_entry procedure
added the parameter called p_currency_code.
3. Package - jai_ract_trg_pkg - When a change is done in the invoice currency code from the front end
the change is being reflected in the JAI_AR_TRXS table.
Future Dependency due to this Bug
------------------------
YES - A new parameter is added to the procedure - ja_in_register_txn_entry in the package jai_om_rg_pkg.
It has a technical dependency on jai_om_rg_pkg and Package jai_jar_t.plb.
It has functional dependency on jai_ract_trg.plb
19. 09/10/2007 ssumaith - bug#6487667 - File version - 120.19
When a sales order trx is done that hits bond register , if only excise tax is present and cess , she_cess is not present,
the register balance was becoming 0. It was because of an incorrect handling of null values.
Added nvls to the variables ln_cess_amount and ln_sh_cess_amount in the ja_in_register_txn_entry procedure.
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
----------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
jai_om_rg_pkg.sql
------------------------------------------------------------------------------------------------------------------------------------------------
616.2 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
2801751 +
2769440
115.2 4136981 4146708 ssumaith 27/01/2005 Education Cess Enhancement
115.9 4299606 IN60106 brathod 26/04/2005 Item DFF Elimination
+ 4239736 (Service Tax)
+ 4245089 (VAT)
12.0 4566054 jai_om_rg.pls 120.3 Aiyer 24-Aug-2005
jai_om_rg.plb 120.4
jai_om_wsh.plb (jai_om_wsh_pkg.process_delivery) 120.4
JAINIRGI.fmb 120.2
jain14.odf 120.3
jain14reg.ldt 120.3
New migration script to port data into new tables 120.0
JAICMNRG1.rdf 120.3
jai_jai_t.sql (trigger jai_jar_t_aru_t1) 120.1
--------------------------------------------------------------------------------------------------------------*/
BEGIN
v_excise_duty_amount := p_excise_amount; --Ramananda for File.Sql.35
INSERT INTO JAI_CMN_RG_I_TRXS(
Register_ID ,
Fin_Year ,
SLNO ,
Organization_id ,
Location_id ,
Inventory_Item_id ,
TRANSACTION_SOURCE_NUM ,
Transaction_Type ,
Transaction_date ,
REF_DOC_NO ,
manufactured_qty ,
manufactured_packed_qty ,
manufactured_loose_qty ,
other_purpose_n_pay_ed_qty ,
other_purpose_n_pay_ed_val ,
for_export_pay_ed_qty ,
for_export_pay_ed_val ,
for_export_n_pay_ed_qty ,
for_export_n_pay_ed_val ,
for_home_use_pay_ed_qty ,
for_home_use_pay_ed_val ,
primary_uom_code ,
transaction_uom_code ,
balance_packed ,
balance_loose ,
issue_type ,
payment_register ,
excise_invoice_number ,
excise_invoice_date ,
excise_duty_amount ,
basic_ed ,
additional_ed ,
other_ed ,
excise_duty_rate ,
customer_id ,
customer_site_id ,
range_no ,
division_no ,
creation_date ,
created_by ,
last_update_login ,
last_update_date ,
last_updated_by ,
other_purpose ,
to_other_factory_n_pay_ed_qty ,
to_other_factory_n_pay_ed_val , -- Vijay Shankar for Bug# 3408210
cess_amt , /* The columns cess_amt and source have been added by aiyer for the bug 4566054*/
sh_cess_amt , /*Bug 5989740 bduvarag*/
source
) VALUES (
jai_cmn_rg_i_trxs_s.nextval ,
p_fin_year ,
v_serial_no ,
p_org_id ,
p_location_id ,
p_inventory_item_id ,
p_transaction_id ,
p_transaction_type ,
TRUNC(p_transaction_date) ,
p_header_id ,
v_manufactured_qty ,
v_manufactured_packed_qty ,
v_manufactured_loose_qty ,
v_other_purpose_n_pay_ed_qty ,
v_other_purpose_n_pay_ed_val ,
v_for_export_pay_ed_qty ,
v_for_export_pay_ed_val ,
v_for_export_n_pay_ed_qty ,
v_for_export_n_pay_ed_val ,
v_home_use_pay_ed_qty ,
v_home_use_pay_ed_val ,
v_primary_uom_code ,
p_uom_code ,
v_balance_packed ,
v_balance_loose ,
v_issue_type ,
p_payment_register ,
p_excise_invoice_no ,
p_excise_invoice_date ,
v_excise_duty_amount ,
v_basic_ed ,
v_additional_ed ,
v_other_ed ,
p_excise_duty_rate ,
p_customer_id ,
p_customer_site_id ,
v_range_no ,
v_division_no ,
p_creation_date ,
p_created_by ,
p_last_update_login ,
p_last_update_date ,
p_last_updated_by ,
v_other_purpose ,
v_to_other_fact_n_pay_ed_qty ,
v_to_other_fact_n_pay_ed_val , -- Vijay Shankar for Bug# 3408210
p_cess_amt , /* The columns cess_amt and source have been added by aiyer for the bug 4566054*/
p_sh_cess_amt ,/*Bug 5989740 bduvarag*/
p_source
);
p_creation_date DATE, p_created_by NUMBER,p_last_update_date DATE,
p_last_updated_by NUMBER, p_last_update_login NUMBER
) IS
v_opening_quantity NUMBER := 0;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id AND
organization_id = p_org_id;
SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
FROM JAI_CMN_RG_23AC_I_TRXS
WHERE organization_id = p_org_id AND
location_id = p_location_id AND
inventory_item_id = p_inventory_item_id AND
fin_year = p_fin_year AND
register_type = p_register_type;
SELECT NVL(opening_balance_qty,0), NVL(closing_balance_qty,0)
FROM JAI_CMN_RG_23AC_I_TRXS
WHERE slno = p_previous_serial_no AND
organization_id = p_org_id AND
location_id = p_location_id AND
register_type = p_register_type AND
fin_year = p_fin_year AND
inventory_item_id = p_inventory_item_id;
SELECT excise_duty_range,excise_duty_division
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = p_customer_id
AND address_id = (SELECT cust_acct_site_id -- address_id
FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for BUg# 4434287
WHERE A.site_use_id = p_customer_site_id);
INSERT INTO JAI_CMN_RG_23AC_I_TRXS (register_id, fin_year, slno, inventory_item_id, organization_id,
location_id, TRANSACTION_SOURCE_NUM, transaction_type, transaction_date,
customer_id, customer_site_id, range_no, division_no,
SALES_INVOICE_NO, sales_invoice_quantity, sales_invoice_date,
EXCISE_INVOICE_NO, excise_invoice_date, register_type,
transaction_uom_code, primary_uom_code, basic_ed, additional_ed,
other_ed, opening_balance_qty, closing_balance_qty,
creation_date,created_by,last_update_login,
last_update_date,last_updated_by)
VALUES(JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, p_fin_year, v_serial_no, p_inventory_item_id, p_org_id,/*rchandan for bug#4487676*/
p_location_id, p_transaction_id, p_transaction_type, TRUNC(p_transaction_date),
p_customer_id,p_customer_site_id,v_range_no,v_division_no,
p_excise_invoice_id, v_excise_quantity, p_excise_invoice_date,
p_header_id, p_sales_invoice_date, p_register_type,
p_uom_code, v_primary_uom_code, v_basic_ed, v_additional_ed,
v_other_ed, v_opening_quantity, v_closing_quantity,
p_creation_date, p_created_by, p_last_update_login,
p_last_update_date, p_last_updated_by );
p_last_update_date DATE , p_last_updated_by NUMBER ,
p_last_update_login NUMBER , p_picking_line_id NUMBER DEFAULT NULL,
p_excise_exempt_type VARCHAR2 DEFAULT NULL,
p_remarks VARCHAR2 DEFAULT NULL ,
P_REF_10 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_23 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_24 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_25 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_26 VARCHAR2 DEFAULT NULL -- added by sriram - bug # 2769440
) IS
v_opening_balance NUMBER := 0;
SELECT NVL(opening_balance,0),NVL(closing_balance,0)
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_org_id AND
location_id = p_location_id AND
slno = p_previous_serial_no AND
register_type = p_register_type AND
fin_year = p_fin_year;
SELECT NVL(rg23a_balance,0)
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = p_org_id AND
location_id = p_location_id;
SELECT NVL(rg23c_balance,0)
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = p_org_id AND
location_id = p_location_id;
SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_org_id AND
location_id = p_location_id AND
fin_year = p_fin_year AND
register_type = p_register_type;
SELECT excise_duty_range, excise_duty_division
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = p_customer_id
AND address_id = (SELECT cust_acct_site_id -- address_id
FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
WHERE A.site_use_id = p_customer_site_id);
SELECT modvat_rm_account_id , excise_edu_cess_rm_account ,SH_CESS_RM_ACCOUNT/*Bug 5989740 bduvarag*/
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND location_id = p_location_id;
SELECT modvat_cg_account_id , excise_edu_cess_cg_account ,SH_CESS_CG_ACCOUNT_ID/*Bug 5989740 bduvarag*/
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND location_id = p_location_id;
SELECT EXCISE_RCVBLE_ACCOUNT , CESS_PAID_PAYABLE_ACCOUNT_ID , /* CESS_PAID_PAYABLE_ACCOUNT_ID added by ssumaith */
SH_CESS_PAID_PAYABLE_ACCT_ID/*Bug 5989740 bduvarag*/
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND location_id = p_location_id;
SELECT MODVAT_REVERSE_ACCOUNT_ID
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id
AND location_id = p_location_id;
SELECT ssi_unit_flag
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND
location_id = p_location_id;
Fnd_File.PUT_LINE(Fnd_File.LOG, 'before insert into JAI_CMN_RG_23AC_II_TRXS');
select JAI_CMN_RG_23AC_II_TRXS_S.NEXTVAL
into ln_register_id
from dual;
INSERT INTO JAI_CMN_RG_23AC_II_TRXS (register_id,
fin_year,
slno,
inventory_item_id,
organization_id,
location_id,
TRANSACTION_SOURCE_NUM,
transaction_date,
customer_id,
customer_site_id,
range_no,
division_no,
excise_invoice_no,
excise_invoice_date,
register_type,
dr_basic_ed,
dr_additional_ed,
dr_other_ed,
opening_balance,
closing_balance,
charge_account_id,
register_id_part_i,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by,
remarks
)
VALUES(
ln_register_id,
p_fin_year,
v_serial_no,
p_inventory_item_id,
p_org_id,
p_location_id,
p_transaction_id,
p_transaction_date,
p_customer_id,
p_customer_site_id,
v_range_no,
v_division_no,
p_excise_invoice_no,
p_excise_invoice_date,
p_register_type,
p_dr_basic_ed,
p_dr_additional_ed,
p_dr_other_ed,
v_opening_balance,
v_closing_balance,
v_debit_account_id,
p_part_i_register_id,
p_creation_date,
p_created_by,
p_last_update_login,
p_last_update_date,
p_last_updated_by,
p_remarks
);
Fnd_File.PUT_LINE(Fnd_File.LOG, 'after insert into JAI_CMN_RG_23AC_II_TRXS');
update JAI_CMN_RG_23AC_II_TRXS
set other_tax_debit = ln_cess_amount + ln_sh_cess_amount/*Bug 5989740 bduvarag*/
where register_id = ln_register_id;
UPDATE JAI_CMN_RG_BALANCES
SET rg23a_balance = rg23a_balance - v_excise_amount
WHERE organization_id = p_org_id AND
location_id = p_location_id;
UPDATE JAI_CMN_RG_BALANCES
SET rg23c_balance = rg23c_balance - v_excise_amount
WHERE organization_id = p_org_id AND
location_id = p_location_id;
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER ,
P_REF_10 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_23 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_24 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_25 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_26 VARCHAR2 DEFAULT NULL -- added by sriram - bug # 2769440
) IS
v_opening_balance NUMBER := 0;
SELECT NVL(opening_balance,0),NVL(closing_balance,0),
NVL(basic_opening_balance,0) ,NVL(basic_closing_balance,0),
NVL(additional_opening_balance,0) ,NVL(additional_closing_balance,0),
NVL(other_opening_balance,0), NVL(other_closing_balance,0)
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_org_id AND
location_id = p_location_id AND
slno = p_previous_serial_no AND
fin_year = p_fin_year;
SELECT NVL(pla_balance,0),NVL(basic_pla_balance,0),
NVL(additional_pla_balance,0), NVL(other_pla_balance,0)
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = p_org_id AND
location_id = p_location_id;
SELECT excise_duty_range, excise_duty_division
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = p_customer_id
AND address_id = (SELECT cust_acct_site_id -- address_id
FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
WHERE A.site_use_id = p_customer_site_id);
SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_org_id AND
location_id = p_location_id AND
fin_year = p_fin_year;
SELECT MODVAT_PLA_ACCOUNT_ID,NVL(export_oriented_unit,'N')
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id
AND location_id = p_location_id;
SELECT EXCISE_RCVBLE_ACCOUNT , CESS_PAID_PAYABLE_ACCOUNT_ID, /* CESS_PAID_PAYABLE_ACCOUNT_ID added by ssumaith */
SH_CESS_PAID_PAYABLE_ACCT_ID/*Bug 5989740 bduvarag*/
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND location_id = p_location_id;
SELECT NVL(ssi_unit_flag, 'N')
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND
location_id = p_location_id;
SELECT order_type_id
FROM JAI_OM_WSH_LINES_ALL
WHERE Organization_id=p_org_id AND
location_id = p_location_id AND
delivery_detail_id = p_header_id;
SELECT A.register_code
FROM JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
WHERE A.organization_id = p_org_id
AND A.location_id = p_location_id
AND A.register_id = b.register_id
AND b.order_flag = 'Y'
AND b.order_type_id = v_order_type_id ;
SELECT
jai_cmn_rg_pla_trxs_s1.nextval
FROM
dual;
fnd_file.put_line(fnd_file.log, '15 before insert into update into JAI_CMN_RG_PLA_TRXS , slno ' || v_serial_no
||' ,register_id ->' || ln_register_id
||' ,organization_id ->' || p_org_id
||' ,location_id ->' || p_location_id
||' ,inventory_item_id ->' || p_inventory_item_id
||' ,fin_year ->' || p_fin_year
||' ,TRANSACTION_SOURCE_NUM ->' || p_transaction_id
||' ,transaction_date ->' || p_ref_document_date
||' ,ref_document_id ->' || p_header_id
||' ,ref_document_date ->' || p_ref_document_date
||' ,DR_INVOICE_NO ->' || p_excise_invoice_no
||' ,dr_invoice_date ->' || p_excise_invoice_date
||' ,dr_basic_ed ->' || p_dr_basic_ed
||' ,dr_additional_ed ->' || p_dr_additional_ed
||' ,dr_other_ed ->' || p_dr_other_ed
||' ,vendor_cust_flag ->' || 'C'
||' ,vendor_id ->' || p_customer_id
||' ,vendor_site_id ->' || p_customer_site_id
||' ,range_no ->' || v_range_no
||' ,division_no ->' || v_division_no
||' ,opening_balance ->' || v_opening_balance
||' ,closing_balance ->' || v_closing_balance
||' ,charge_account_id ->' || v_debit_account_id
||' ,creation_date ->' || p_creation_date
||' ,created_by ->' || p_created_by
||' ,last_update_login ->' || p_last_update_login
||' ,last_update_date ->' || p_last_update_date
||' ,last_updated_by ->' || p_last_updated_by
||' ,basic_opening_balance ->' || v_basic_opening_balance
||' ,basic_closing_balance ->' || v_basic_closing_balance
||' ,additional_opening_balance ->' || v_additional_opening_balance
||' ,additional_closing_balance ->' || v_additional_closing_balance
||' ,other_opening_balance ->' || v_other_opening_balance
||' ,other_closing_balance ->' || v_other_closing_balance
);
INSERT INTO JAI_CMN_RG_PLA_TRXS(register_id,
slno,
organization_id,
location_id,
inventory_item_id,
fin_year,
TRANSACTION_SOURCE_NUM,
transaction_date,
ref_document_id,
ref_document_date,
DR_INVOICE_NO,
dr_invoice_date,
dr_basic_ed,
dr_additional_ed,
dr_other_ed,
vendor_cust_flag,
vendor_id,
vendor_site_id,
range_no,
division_no,
opening_balance,
closing_balance,
charge_account_id,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by,
basic_opening_balance,
basic_closing_balance,
additional_opening_balance,
additional_closing_balance,
other_opening_balance,
other_closing_balance)
VALUES
(
ln_register_id ,
v_serial_no,
p_org_id,
p_location_id,
p_inventory_item_id,
p_fin_year,
p_transaction_id,
p_ref_document_date,
p_header_id ,
p_ref_document_date,
p_excise_invoice_no,
p_excise_invoice_date,
p_dr_basic_ed,
p_dr_additional_ed,
p_dr_other_ed,
'C',
p_customer_id,
p_customer_site_id ,
v_range_no,
v_division_no,
v_opening_balance,
v_closing_balance,
v_debit_account_id,
p_creation_date,
p_created_by,
p_last_update_login,
p_last_update_date,
p_last_updated_by,
v_basic_opening_balance,
v_basic_closing_balance,
v_additional_opening_balance,
v_additional_closing_balance,
v_other_opening_balance,
v_other_closing_balance
) returning register_id into ln_register_id;
fnd_file.put_line(fnd_file.log, '16 after insert into JAI_CMN_RG_PLA_TRXS');
UPDATE JAI_CMN_RG_BALANCES
SET pla_balance = pla_balance - v_excise_amount,
basic_pla_balance = basic_pla_balance - p_dr_basic_ed,
additional_pla_balance = NVL(additional_pla_balance,0) - NVL(p_dr_additional_ed,0),
other_pla_balance = NVL(other_pla_balance,0) - NVL(p_dr_other_ed,0)
WHERE organization_id = p_org_id
AND location_id = p_location_id;
fnd_file.put_line(fnd_file.log, '17 after update into JAI_CMN_RG_BALANCES');
fnd_file.put_line(fnd_file.log, '18 before insert into update into JAI_CMN_RG_PLA_TRXS , slno ' || v_serial_no
||' , register_id ->' || ln_register_id
||' , organization_id ->' || p_org_id
||' , location_id ->' || p_location_id
||' , inventory_item_id ->' || p_inventory_item_id
||' , fin_year ->' || p_fin_year
||' , transaction_source_num ->' || p_transaction_id
||' , transaction_date ->' || p_ref_document_date
||' , ref_document_id ->' || p_header_id
||' , ref_document_date ->' || p_ref_document_date
||' , DR_INVOICE_NO ->' || p_excise_invoice_no
||' , dr_invoice_date ->' || p_excise_invoice_date
||' , dr_basic_ed ->' || p_dr_basic_ed
||' , dr_additional_ed ->' || p_dr_additional_ed
||' , dr_other_ed ->' || p_dr_other_ed
||' , vendor_cust_flag ->' || 'C'
||' , vendor_id ->' || p_customer_id
||' , vendor_site_id ->' || p_customer_site_id
||' , range_no ->' || v_range_no
||' , division_no ->' || v_division_no
||' , opening_balance ->' || v_opening_balance
||' , closing_balance ->' || v_closing_balance
||' , charge_account_id ->' || v_debit_account_id
||' , creation_date ->' || p_creation_date
||' , created_by ->' || p_created_by
||' , last_update_login ->' || p_last_update_login
||' , last_update_date ->' || p_last_update_date
||' , last_updated_by ->' || p_last_updated_by
);
INSERT INTO JAI_CMN_RG_PLA_TRXS(
register_id,
slno,
organization_id,
location_id,
inventory_item_id,
fin_year,
TRANSACTION_SOURCE_NUM,
transaction_date,
ref_document_id,
ref_document_date,
DR_INVOICE_NO,
dr_invoice_date,
dr_basic_ed,
dr_additional_ed,
dr_other_ed,
vendor_cust_flag,
vendor_id,
vendor_site_id,
range_no,
division_no,
opening_balance,
closing_balance,
charge_account_id,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by
)
VALUES(
ln_register_id,
v_serial_no,
p_org_id,
p_location_id,
p_inventory_item_id,
p_fin_year,
p_transaction_id,
p_ref_document_date,
p_header_id ,
p_ref_document_date,
p_excise_invoice_no ,
p_excise_invoice_date,
p_dr_basic_ed,
p_dr_additional_ed,
p_dr_other_ed,
'C',
p_customer_id,
p_customer_site_id ,
v_range_no,
v_division_no,
v_opening_balance,
v_closing_balance,
v_debit_account_id,
p_creation_date,
p_created_by,
p_last_update_login,
p_last_update_date,
p_last_updated_by
) returning register_id into ln_register_id;
fnd_file.put_line(fnd_file.log, '19 after insert into JAI_CMN_RG_PLA_TRXS');
UPDATE JAI_CMN_RG_BALANCES
SET pla_balance = pla_balance - v_excise_amount
WHERE organization_id = p_org_id
AND location_id = p_location_id;
fnd_file.put_line(fnd_file.log, '20 after update of JAI_CMN_RG_BALANCES');
update JAI_CMN_RG_PLA_TRXS
set other_tax_debit = ln_cess_amount + ln_sh_cess_amount -- Date 04/06/2007 by Sacsethi for bug 6109941
where register_id = ln_register_id;
p_creation_date DATE,p_created_by NUMBER,p_last_update_date DATE,p_last_update_login NUMBER,
p_last_updated_by NUMBER,p_dr_basic_ed NUMBER,p_dr_additional_ed NUMBER,p_dr_other_ed NUMBER,
p_comm_invoice_no VARCHAR2,p_comm_invoice_date DATE,
P_REF_10 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_23 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_24 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_25 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
P_REF_26 VARCHAR2 DEFAULT NULL -- added by sriram - bug # 2769440
) IS
v_opening_balance NUMBER;
SELECT NVL(ROUND(opening_balance_qty,0),0),NVL(ROUND(closing_balance_qty,0),0)
FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_org_id AND
location_id = p_location_id AND
slno = p_previous_serial_no AND
fin_year = p_fin_year
AND inventory_item_id = p_inventory_item_id;
SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_org_id AND
location_id = p_location_id AND
fin_year = p_fin_year
AND inventory_item_id = p_inventory_item_id;
SELECT max(register_id)
FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_orgn_id
AND location_id = p_loc_id
AND inventory_item_id = p_inv_item_id;
SELECT fin_year, slno, opening_balance_qty, closing_balance_qty
FROM JAI_CMN_RG_23D_TRXS
WHERE register_id = p_register_id;
SELECT EXCISE_23D_ACCOUNT
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND location_id = p_location_id;
SELECT EXCISE_RCVBLE_ACCOUNT , CESS_PAID_PAYABLE_ACCOUNT_ID
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND location_id = p_location_id;
SELECT source_header_id, source_line_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_reference_line_id;
SELECT ship_from_org_id, order_source_id, source_document_id, source_document_line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND line_id = p_line_id;
SELECT destination_organization_id , deliver_to_location_id /* deliver_to_location_id added by ssumaith - to handle trading to trading ISO */
FROM po_requisition_lines_all
WHERE requisition_header_id = p_requisition_header_id
AND requisition_line_id = p_requisition_line_id;
SELECT intransit_type, fob_point, interorg_receivables_account, interorg_payables_account, intransit_inv_account
FROM mtl_interorg_parameters
WHERE from_organization_id = p_from_org_id
AND to_organization_id = p_to_org_id;
SELECT excise_rcvble_account , excise_23d_account ,excise_in_rg23d , Trading, manufacturing
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id;
SELECT sum(a.tax_amount)
FROM JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
WHERE delivery_detail_id = p_reference_line_id
AND a.tax_id = b.tax_id
AND upper(b.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS);
SELECT sum(a.tax_amount)
FROM JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
WHERE delivery_detail_id = p_reference_line_id
AND a.tax_id = b.tax_id
AND upper(b.tax_type) in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess);
INSERT INTO JAI_CMN_RG_23D_TRXS (
register_id, organization_id, location_id, slno, fin_year,
transaction_type, inventory_item_id, reference_line_id, primary_uom_code,
transaction_uom_code, customer_id, bill_to_site_id, ship_to_site_id,
quantity_issued, register_code, charge_account_id, rate_per_unit, excise_duty_rate,duty_amount, TRANSACTION_SOURCE_NUM,
basic_ed, additional_ed, other_ed, opening_balance_qty, closing_balance_qty,
RECEIPT_REF, OTH_RECEIPT_ID_REF,
creation_date, created_by,last_update_login,
last_update_date,last_updated_by,comm_invoice_no,comm_invoice_date
) VALUES (
p_register_id, p_org_id, p_location_id, v_srno1, p_fin_year,
p_transaction_type, p_inventory_item_id, p_reference_line_id, p_primary_uom_code,
p_transaction_uom_code, p_customer_id, p_bill_to_site_id, p_ship_to_site_id,
p_quantity_issued, p_register_code, v_rg23d_account, p_rate_per_unit, p_excise_duty_rate,p_duty_amount, 33,
p_dr_basic_ed, p_dr_additional_ed, p_dr_other_ed, NVL(v_closing_balance,0), NVL(v_closing_balance,0) - NVL(p_quantity_issued,0),
p_receipt_id, p_oth_receipt_id,
p_creation_date, p_created_by, p_last_update_login,
p_last_update_date, p_last_updated_by,p_comm_invoice_no,p_comm_invoice_date
);
update JAI_CMN_RG_23D_TRXS
set other_tax_debit = ln_cess_amount + ln_sh_cess_amount/*Bug 5989740 bduvarag*/
where register_id = p_register_id;
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER ,
p_order_invoice_type_id IN NUMBER,
p_currency_rate IN NUMBER DEFAULT 1 /* added by CSahoo - bug#5390583 */
)IS
v_register_id NUMBER;
SELECT register_balance
FROM JAI_OM_OE_BOND_TRXS
WHERE transaction_id = (SELECT MAX(transaction_id) FROM JAI_OM_OE_BOND_TRXS
WHERE register_id = (SELECT register_id FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_org_id AND location_id = p_location_id
AND register_code = p_register_code));
SELECT rg23d_register_balance
FROM JAI_OM_OE_BOND_TRXS
WHERE transaction_id = (SELECT MAX(transaction_id) FROM JAI_OM_OE_BOND_TRXS
WHERE register_id = (SELECT register_id FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_org_id AND location_id = p_location_id
AND register_code = p_register_code));
SELECT register_ID
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_org_id
AND location_id = p_location_id
AND register_code = p_register_code;
select order_type_id
from oe_order_headers_all
where header_id = cp_header_id;
select batch_source_id
from ra_customer_trx_all
where customer_trx_id = cp_order_header_id;
select order_header_id
from JAI_OM_WSH_LINES_ALL
where delivery_id = p_header_id;
select customer_trx_id
from JAI_AR_TRX_LINES
where customer_trx_line_id = cp_header_id;
select SUM(NVL(jsptl.func_tax_amount,0)) tax_amount --NVL(sum(jsptl.func_tax_amount),0) tax_amount -- added , Ramananda NVL condition for bug #4516577
from JAI_OM_WSH_LINE_TAXES jsptl ,
JAI_CMN_TAXES_ALL jtc
where jtc.tax_id = jsptl.tax_id
and delivery_detail_id in
(select delivery_detail_id
from JAI_OM_WSH_LINES_ALL
where delivery_id = cp_delivery_id
)
and upper(jtc.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS);
select NVL(sum(jsptl.func_tax_amount),0) tax_amount
from JAI_OM_WSH_LINE_TAXES jsptl ,
JAI_CMN_TAXES_ALL jtc
where jtc.tax_id = jsptl.tax_id
and delivery_detail_id in
(select delivery_detail_id
from JAI_OM_WSH_LINES_ALL
where delivery_id = cp_delivery_id
)
and upper(jtc.tax_type) in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess);
select nvl(sum(jrctl.func_tax_amount),0) tax_amount
from JAI_AR_TRX_TAX_LINES jrctl ,
JAI_CMN_TAXES_ALL jtc
where jtc.tax_id = jrctl.tax_id
and link_to_cust_trx_line_id in
(select customer_trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_id = cp_customer_trx_id
)
and upper(jtc.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS) ;
select nvl(sum(jrctl.func_tax_amount),0) tax_amount
from JAI_AR_TRX_TAX_LINES jrctl ,
JAI_CMN_TAXES_ALL jtc
where jtc.tax_id = jrctl.tax_id
and link_to_cust_trx_line_id in
(select customer_trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_id = cp_customer_trx_id
)
and upper(jtc.tax_type) in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess) ;
select count(1)
from JAI_AR_TRX_TAX_LINES jrctl ,
JAI_CMN_TAXES_ALL jtc
where jtc.tax_id = jrctl.tax_id
and link_to_cust_trx_line_id in
(select customer_trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_id = cp_customer_trx_id
)
and upper(jtc.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS) ;
select count(1)
from JAI_AR_TRX_TAX_LINES jrctl ,
JAI_CMN_TAXES_ALL jtc
where jtc.tax_id = jrctl.tax_id
and link_to_cust_trx_line_id in
(select customer_trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_id = cp_customer_trx_id
)
and upper(jtc.tax_type) in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess) ;
select customer_trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_line_id = p_header_id
)
Loop
for cess_rec in
(
select jrctl.link_to_cust_trx_line_id,
jrctl.customer_trx_line_id,
jrctl.tax_rate,
jrctl.precedence_1,
jrctl.precedence_2,
jrctl.precedence_3,
jrctl.precedence_4,
jrctl.precedence_5 ,
jrctl.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
jrctl.precedence_7,
jrctl.precedence_8,
jrctl.precedence_9,
jrctl.precedence_10
from JAI_AR_TRX_TAX_LINES jrctl ,
JAI_CMN_TAXES_ALL jtc
where link_to_cust_trx_line_id = line_rec.customer_trx_line_id
and jtc.tax_id = jrctl.tax_id
and jtc.tax_type in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS)
)
Loop
for cess_amt_rec in
(
select
decode(tax_amount, 0,
(base_tax_amount * ( tax_rate / 100) )
, tax_amount
) cess_amt
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id = line_rec.customer_trx_line_id
and tax_line_no in
(cess_rec.precedence_1,
cess_rec.precedence_2,
cess_rec.precedence_3,
cess_rec.precedence_4,
cess_rec.precedence_5 ,
cess_rec.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
cess_rec.precedence_7,
cess_rec.precedence_8,
cess_rec.precedence_9,
cess_rec.precedence_10
)
)
Loop
ln_cess_amount := nvl(ln_cess_amount,0) + ( nvl(cess_amt_rec.cess_amt,0) * (cess_rec.tax_rate/100 ));
select customer_trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_line_id = p_header_id
)
Loop
for cess_rec in
(
select jrctl.link_to_cust_trx_line_id,
jrctl.customer_trx_line_id,
jrctl.tax_rate,
jrctl.precedence_1,
jrctl.precedence_2,
jrctl.precedence_3,
jrctl.precedence_4,
jrctl.precedence_5,
jrctl.precedence_6,
jrctl.precedence_7,
jrctl.precedence_8,
jrctl.precedence_9,
jrctl.precedence_10
from JAI_AR_TRX_TAX_LINES jrctl ,
JAI_CMN_TAXES_ALL jtc
where link_to_cust_trx_line_id = line_rec.customer_trx_line_id
and jtc.tax_id = jrctl.tax_id
and jtc.tax_type in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess)
)
Loop
for cess_amt_rec in
(
select
decode(tax_amount, 0,
(base_tax_amount * ( tax_rate / 100) )
, tax_amount
) sh_cess_amt
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id = line_rec.customer_trx_line_id
and tax_line_no in
(cess_rec.precedence_1,
cess_rec.precedence_2,
cess_rec.precedence_3,
cess_rec.precedence_4,
cess_rec.precedence_5,
cess_rec.precedence_6,
cess_rec.precedence_7,
cess_rec.precedence_8,
cess_rec.precedence_9,
cess_rec.precedence_10
)
)
Loop
ln_sh_cess_amount := nvl(ln_sh_cess_amount,0) + ( nvl(cess_amt_rec.sh_cess_amt,0) * (cess_rec.tax_rate/100 ));
INSERT INTO JAI_OM_OE_BOND_TRXS(transaction_id,
register_id ,
transaction_name,
order_flag,
order_header_id,
transaction_amount,
edu_cess_amount , /* added by ssumaith - bug# 4136981*/
SH_CESS_AMOUNT ,/*Bug 5989740 bduvarag*/
register_balance,
rg23d_register_balance,
-- picking_header_id, -- bug#6650203
picking_line_id, -- ssumaith bug#6650203
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by
)
VALUES (
JAI_OM_OE_BOND_TRXS_S.NEXTVAL,
v_register_id,
p_transaction_name,
p_order_flag,
ln_header_id ,
p_transaction_amount,
round(NVL(ln_cess_amount,0),2),
round(NVL(ln_sh_cess_amount,0),2) ,/*Bug 5989740 bduvarag*/
/* added by ssumaith - bug# 4136981*/
/* added round(2) based on support feedback for cess CSahoo - bug# 5390583 */
NVL(v_register_balance - v_reg_transaction_amount,0),
NVL(v_rg23d_register_balance - v_rg23d_transaction_amount, 0),
-- p_excise_invoice_no, -- bug#6650203
p_header_id, -- ssumaith - bug#6650203
p_creation_date,
p_created_by,
p_last_update_login,
p_last_update_date,
p_last_updated_by );