[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
38 01-Jun-2009 Bug 8537295 File version 120.8.12000000.9 / 120.25
Issue - Accounting entries for cess / sh cess taxes are not rounded for Internal Sales Orders
Fix - Changed the value of v_precision variable in JA_IN_CESS_ENTRIES procedure from the
currency setup value to zero.
39. 31-mar-2010 vkaranam for bug#9539924
Issue:
Cess/Shcess amounts in RG register are not rounded to nearest rupee for Manual AR invoice.
Fix:
Changes are done in ja_in_Ar_cess_Register_entries procedure.
added round to func_tax_amount in c_tax_type_rec cursor.
26 05-Apr-2010 Bug 9550254
The opening balance for the RG23 Part I and RG I has been derived from the previous
financial year closing balance, if no entries found for the current year.
27-apr-2010 bug#9466919
issue :quantity in rg registers are not in sync with the inventory.
fix:
added the rounding precision of 5 to the quantity fields while inserting.
28. 03-Mar-2011 Xiao for Open Interface ER bug#11683927.
Fixed: For 'External' event, only accounting is updated with tax ccid and tax amount
from JAI Interface table. Do not update RG register.
29. 10-May-2011 Zhiwei for Open Interface ER Bug#12537533 .
Fixed: For External event, check put to JAI_JAR_TRXS_TRIGGER_PKG where the procedure was invoked .
6. 27-Dec-2012 mmurtuza for bug 16022303
Issue: RG23D REVERSAL ENTRY DEBITS COGS IN CASE OF INCLUSIVE TAX
Fix: Added code in Ja_In_Rg23d_Entry to bifurcate amount to excise, cess and she cess paid accounts.
Also ported the cahnges done for bug 14307860
44. 11-JAN-2013 nkodakan for the bug 16014871
Issue : bond register cess and sh cess are reflected with Zero values
Fix : Issue is cess and sh cess amouts are picking from the AR table. So made some changes mentioned below.
commented the cursors c_get_ar_exc_amount ,c_get_ar_cess_amount, c_ar_cess_ctr, c_ar_sh_cess_ctr which are picking the data from the AR tables for bond register. Actually it should pick from shipment tables
Added cursor c_get_om_cess_amount, c_get_om_sh_cess_amount to get the cess and sh cess amount for the particular delivery id. Added a cursor get_ref_header_line_id to get the rma header id, rma line id using rma_line_id as a parameter.
Added a cursor get_rma_delivery_id to get the delivery id using header id and line id
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 , sum(jsptl.func_tax_amount) tax_amount --rchandan for bug#4388950 /*Removed rounding factor by mmurtuza for bug 16534065*/
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 , sum(jsptl.func_tax_amount) tax_amount /*Removed rounding factor by mmurtuza for bug 16534065*/
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 --added round for bug#9539924 /*Removed rounding factor by mmurtuza for bug 16534065*/
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
order_number ,
transactional_curr_code ,
conversion_type_code ,
conversion_rate ,
b.actual_shipment_date
FROM
oe_order_headers_all a ,
oe_order_lines_all b
WHERE
a.header_id = b.header_id AND
b.line_id = p_line_id AND
a.header_id = p_header_id ;
Select temp.order_header_id, oola.line_id from
JAI_OM_OE_GEN_TAXINV_T temp , oe_order_lines_all oola
where temp.order_header_id = oola.header_id
and temp.delivery_detail_id = p_delivery_detail_id;
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;
SELECT nvl(allow_negative_rg_flag,'N')
FROM jai_cmn_inventory_orgs
WHERE organization_id = cp_organization_id
AND location_id = cp_location_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.
20. 27-Nov-2009 Bug 9122545 File version 120.8.12000000.14 / 120.20.12010000.8 / 120.30
Description - Checked the setup option to allow negative quantity in RG register before raising the
error "Enough RG1 balance is not available to Issue the Goods".
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 ,
round(v_manufactured_qty,5) ,
round(v_manufactured_packed_qty ,5) ,
round(v_manufactured_loose_qty ,5) ,
round(v_other_purpose_n_pay_ed_qty ,5) ,
v_other_purpose_n_pay_ed_val ,
round(v_for_export_pay_ed_qty ,5) ,
v_for_export_pay_ed_val ,
round(v_for_export_n_pay_ed_qty ,5) ,
v_for_export_n_pay_ed_val ,
round(v_home_use_pay_ed_qty ,5) ,
v_home_use_pay_ed_val ,
v_primary_uom_code ,
p_uom_code ,
round(v_balance_packed ,5) ,
round(v_balance_loose ,5) ,
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 ,
round(v_to_other_fact_n_pay_ed_qty ,5) ,
round(v_to_other_fact_n_pay_ed_val ,5) , -- 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, round(v_excise_quantity,5), 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,round( v_opening_quantity,5), round(v_closing_quantity,5),
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;
SELECT COUNT(lines.customer_trx_id)
FROM jai_ar_trx_lines lines,
jai_interface_lines_all intfs
WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
AND lines.customer_trx_id IN (SELECT customer_trx_id
FROM jai_ar_trx_lines
WHERE customer_trx_line_id = p_picking_line_id)
AND lines.interface_flag = 'Y'
AND intfs.taxable_event = 'EXTERNAL';
SELECT tax.code_combination_id
, tax.tax_amount
FROM jai_ar_trx_lines jatl
, jai_ar_trx_tax_lines jattl
, jai_interface_lines_all lines
, jai_interface_tax_lines_all tax
WHERE jatl.customer_trx_line_id = p_picking_line_id
AND jattl.customer_trx_line_id = p_picking_line_id
AND tax.tax_line_no = jattl.tax_line_no
AND tax.interface_line_id = lines.interface_line_id
AND lines.internal_trx_id = jatl.customer_trx_id
AND lines.internal_trx_line_id = p_picking_line_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;
SELECT COUNT(lines.customer_trx_id)
FROM jai_ar_trx_lines lines,
jai_interface_lines_all intfs
WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
AND lines.customer_trx_id IN (SELECT customer_trx_id
FROM jai_ar_trx_lines
WHERE customer_trx_line_id = p_header_id)
AND lines.interface_flag = 'Y'
AND intfs.taxable_event = 'EXTERNAL';
SELECT tax.code_combination_id
, tax.tax_amount
FROM jai_ar_trx_lines jatl
, jai_ar_trx_tax_lines jattl
, jai_interface_lines_all lines
, jai_interface_tax_lines_all tax
WHERE jatl.customer_trx_line_id = p_header_id
AND jattl.customer_trx_line_id = p_header_id
AND tax.tax_line_no = jattl.tax_line_no
AND tax.interface_line_id = lines.interface_line_id
AND lines.internal_trx_id = jatl.customer_trx_id
AND lines.internal_trx_line_id = p_header_id;
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
p_dr_cvd_amt NUMBER DEFAULT NULL, --Added by nprashar for bug # 5735284 added for bug#6199766
p_dr_additional_cvd_amt NUMBER DEFAULT NULL --Added by nprashar for bug # 5735284 added for bug#6199766
) 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
,cess_paid_payable_account_id, sh_cess_paid_payable_acct_id /*Added two columns by mmurtuza for bug 16022303*/
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 ,cvd, additional_cvd, opening_balance_qty, closing_balance_qty, /*Added CVD columns by nprashar for bug # 5735284*/
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, p_dr_cvd_amt,p_dr_additional_cvd_amt, /*Added by nprashar for bug # 5735284*/
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 reference_header_id , reference_line_id
from oe_order_lines_all
where line_id= cp_line_id;
select delivery_id
from JAI_OM_WSH_LINES_ALL
where order_header_id=cp_ref_header_id
and order_line_id=cp_ref_line_id;
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 );
select fin_year,max(slno)
from JAI_CMN_RG_I_TRXS
where organization_id = p_organization_id
and location_id = p_location_id
and inventory_item_id = p_inventory_item_id
group by fin_year
order by fin_year desc;
select NVL(balance_packed,0), NVL(balance_loose,0)
from JAI_CMN_RG_I_TRXS
where organization_id = p_organization_id
and location_id = p_location_id
and inventory_item_id = p_inventory_item_id
and fin_year = cp_balyear
and slno = cp_slno;
select fin_year,max(slno) from JAI_CMN_RG_23AC_I_TRXS
where organization_id = p_organization_id
and location_id = p_location_id
and inventory_item_id = p_inventory_item_id
and register_type = p_register_type
group by fin_year
order by fin_year desc;
select NVL(closing_balance_qty, 0) closing_balance_qty
from JAI_CMN_RG_23AC_I_TRXS
where organization_id = p_organization_id
and location_id = p_location_id
and inventory_item_id = p_inventory_item_id
and register_type = p_register_type
and fin_year = cp_balyear
and slno = cp_slno;