[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_last_update_date DATE ;
v_last_updated_by NUMBER ;
v_last_update_login NUMBER ;
SELECT A.order_line_id, A.organization_id, A.location_id, A.register,
A.inventory_item_id, A.unit_code uom_code, A.quantity,
b.item_class, b.excise_flag ,A.basic_excise_duty_amount,
A.add_excise_duty_amount, A.oth_excise_duty_amount, A.excise_amount,
A.excise_exempt_type, A.selling_price, A.customer_id, A.ship_to_org_id,
A.order_type_id, A.subinventory, A.assessable_value,
A.EXCISE_EXEMPT_REFNO, A.org_id -- added a.org_id by vijay for multi org support
FROM JAI_OM_WSH_LINES_ALL A,
JAI_INV_ITM_SETUPS B
WHERE A.delivery_detail_id = p_delivery_detail_id
AND A.organization_id = b.organization_id
AND A.inventory_item_id = b.inventory_item_id
ORDER BY b.item_class;
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 = cp_line_id AND
a.header_id = cp_order_header_id ;
SELECT NVL(A.bonded,'Y') bonded,NVL(A.trading,'Y') trading
FROM JAI_INV_SUBINV_DTLS A
WHERE A.sub_inventory_name = p_subinventory
AND A.organization_id = p_organization_id;
SELECT pref_rg23a, pref_rg23c, pref_pla,
NVL(Export_oriented_unit ,'N')
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance,NVL(pla_balance,0) pla_balance,
NVL(basic_pla_balance,0) basic_pla_balance,
NVL(additional_pla_balance,0) additional_pla_balance,
NVL(other_pla_balance,0) other_pla_balance
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
SELECT ssi_unit_flag
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id AND
location_id = p_location_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_organization_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 = p_order_type_id ;
SELECT A.tax_rate, b.rounding_factor
FROM JAI_OM_WSH_LINE_TAXES A, JAI_CMN_TAXES_ALL b
WHERE A.tax_id = b.tax_id
AND A.delivery_detail_id = p_delivery_detail_id
AND b.tax_type = jai_constants.tax_type_modvat_recovery ; /* --'Modvat Recovery'; Ramananda for removal of SQL LITERALs */
SELECT MODVAT_REVERSE_PERCENT
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND (location_id = p_location_id
OR
(location_id is NULL AND p_location_id is NULL)); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
SELECT register_balance
FROM JAI_OM_OE_BOND_TRXS
WHERE transaction_id = (SELECT MAX(A.transaction_id)
FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
WHERE A.register_id = B.register_id
AND B.organization_id = p_organization_id
AND B.location_id = p_location_id);
SELECT meaning
FROM ja_lookups
WHERE lookup_code = p_register_code
AND lookup_type = lv_lookup_type ; /*'JAI_REGISTER_TYPE'; Ramananda for removal of SQL LITERALs */
SELECT MAX(A.fin_year)
FROM JAI_CMN_FIN_YEARS A
WHERE organization_id = p_organization_id
AND fin_active_flag = 'Y';
SELECT name
FROM oe_transaction_types_tl
WHERE transaction_type_id = p_order_type_id;
SELECT start_number, end_number, jump_by, prefix
FROM JAI_CMN_RG_EXC_INV_NOS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND order_invoice_type = p_batch_name
AND register_code = p_register_code; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
SELECT NVL(MAX(GP1),0),NVL(MAX(GP2),0)
FROM JAI_CMN_RG_EXC_INV_NOS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND order_invoice_type IS NULL
AND register_code IS NULL;
SELECT A.Organization_Id, A.Location_Id
FROM JAI_CMN_INVENTORY_ORGS A
WHERE A.Ec_Code IN (SELECT B.Ec_Code
FROM JAI_CMN_INVENTORY_ORGS B
WHERE B.Organization_Id = p_organization_id
AND B.Location_Id = p_location_id);
SELECT A.Organization_Id, A.Location_Id
FROM JAI_CMN_INVENTORY_ORGS A
WHERE A.Tr_Ec_Code IN (SELECT B.Tr_Ec_Code
FROM JAI_CMN_INVENTORY_ORGS B
WHERE B.Organization_Id = p_organization_id
AND B.Location_Id = p_location_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_organization_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 = p_order_type_id
AND A.REGISTER_CODE LIKE '23D%';
SELECT SUM(A.quantity_applied) quantity_applied, A.subinventory
FROM JAI_CMN_MATCH_RECEIPTS A
WHERE A.ref_line_id = p_reference_line_id
AND ORDER_INVOICE = 'O'; -- cbabu for Bug# 2736191
SELECT SUM(quantity) shipped_quantity
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_detail_id = p_picking_line_id;
SELECT source_line_id, source_header_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_picking_line_id;
SELECT A.receipt_id, A.quantity_applied
FROM JAI_CMN_MATCH_RECEIPTS A
WHERE A.ref_line_id = p_reference_line_id
AND ORDER_INVOICE = 'O' -- cbabu for Bug# 2736191
AND A.quantity_applied > 0;
SELECT INVOICE_TO_ORG_ID,SHIP_TO_ORG_ID
FROM oe_order_headers_all
WHERE header_id = p_order_header_id;
SELECT qty_to_adjust,excise_duty_rate,rate_per_unit,quantity_received --added quantity_received for bug#5735284
FROM JAI_CMN_RG_23D_TRXS
WHERE register_id = p_receipt_id;
SELECT duty_amount,basic_ed, additional_ed,other_ed, cvd,additional_cvd
FROM JAI_CMN_RG_23D_TRXS
WHERE register_id = p_receipt_id;
SELECT
round(nvl((sum(basic_excise_duty_amount * cp_conversion_rate)),0),2) , /*Removed round function from all columns by mmurtuza for bug 16534065*/
round(nvl((sum(add_excise_duty_amount * cp_conversion_rate)),0),2) ,
round(nvl((sum(oth_excise_duty_amount * cp_conversion_rate)),0),2) ,
round(nvl((sum(excise_amount * cp_conversion_rate)),0),2) /*Added round function by mmurtuza for bug 16343067 / 16611281*/
FROM
JAI_OM_WSH_LINES_ALL
WHERE
delivery_id = p_delivery_id
and excise_exempt_type is null; -- sriram - 5th nov - bug # 3207685
select delivery_detail_id , quantity , assessable_Value , excise_exempt_type
from JAI_OM_WSH_LINES_ALL
where delivery_id = p_delivery_id
and excise_exempt_type is not null;
SELECT register, EXCISE_INVOICE_NO
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_id = p_delivery_id
AND register IS NOT NULL;
SELECT status_code,initial_pickup_date
FROM Wsh_New_deliveries
WHERE delivery_id = p_delivery_id;
SELECT SOURCE_LINE_ID FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT actual_shipment_date
FROM Oe_Order_Lines_All
WHERE line_id = p_order_line_id;
SELECT order_number
FROM oe_order_headers_all
WHERE header_id = p_hdr_id;
SELECT
count(1)
FROM
JAI_OM_WSH_LINES_ALL
WHERE
delivery_id = p_delivery_id AND
nvl(excise_exempt_type,'$$') <> 'CT3';
SELECT count(1)
FROM JAI_OM_WSH_LINE_TAXES JSPTL ,
JAI_CMN_TAXES_ALL JTC
WHERE JSPTL.TAX_ID = JTC.TAX_ID
AND JSPTL.delivery_detail_id = cp_delivery_detail_id
AND upper(jtc.tax_type) like '%EXCISE%';
SELECT SUM(NVL(JSPTL.tax_rate,0)) , count(1) --NVL(sum(JSPTL.tax_rate),0)
FROM JAI_OM_WSH_LINE_TAXES JSPTL ,
JAI_CMN_TAXES_ALL JTC
WHERE JSPTL.TAX_ID = JTC.TAX_ID
AND JSPTL.delivery_detail_id = cp_delivery_detail_id
AND UPPER(JTC.TAX_TYPE) = 'EXCISE';
SELECT sum(NVL(JSPTL.base_tax_amount,0) / JOWLA.quantity)
FROM JAI_OM_WSH_LINE_TAXES JSPTL ,
JAI_CMN_TAXES_ALL JTC,
JAI_OM_WSH_LINES_ALL JOWLA
WHERE JSPTL.TAX_ID = JTC.TAX_ID
AND JSPTL.delivery_detail_id = cp_delivery_detail_id
AND JSPTL.delivery_detail_id = JOWLA.delivery_detail_id
AND UPPER(JTC.TAX_TYPE) = 'EXCISE';
SELECT sum(jsptl.func_tax_amount) 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_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
SELECT sum(jsptl.func_tax_amount) 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
sum(func_tax_amount) cess_amount
FROM
jai_om_wsh_lines_all jspl ,
jai_om_wsh_line_taxes jsptl,
jai_cmn_taxes_all jtc
WHERE
jspl.delivery_detail_id = jsptl.delivery_detail_id AND
jsptl.tax_id = jtc.tax_id AND
upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess) AND
jspl.delivery_detail_id = cp_delivery_detail_id;
SELECT
sum(func_tax_amount) cess_amount
FROM
JAI_OM_WSH_LINES_ALL jspl,
JAI_OM_WSH_LINE_TAXES jsptl,
JAI_CMN_TAXES_ALL jtc
WHERE
jspl.delivery_detail_id = jsptl.delivery_detail_id AND
jsptl.tax_id = jtc.tax_id AND
upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,
jai_constants.tax_type_sh_exc_edu_cess)
AND
jspl.delivery_detail_id = cp_delivery_detail_id;
SELECT REQUEST_DATE
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = cp_Request_id;
Cursors pref_cur - Incorporated v_export_oriented_unit also in the select clause
RG Bal Cur- Incorporated basic,additional,other pla balance also in the select clause.
Functions jai_om_wsh_processing_pkg.excise_balance_check - for preference checks in case of EOU and Non-EOU for total excise amount
jai_om_wsh_pkg.get_excise_register_with_bal - for preference checks in case of EOU and Non-EOU
for exempted amount.
Before sending this patch it has to be taken care that, the alter scripts,functions should also
accompany the patch otherwise the patch would certainly fail.
20 2002/07/20 SSUMAITH Bug # 2454978 Added code for ensuring the tax target amount is not 0 for 0% CST.
21 2002/10/24 SSUMAITH Bug # 2638797 - Added code to see that the register is gettig hit for a trading organization
and the item a trading item , then the register to be hit is RG23D.
22 2003/01/02 SSUMAITH Bug # 2731434 - File Version - 615.3
Added the (NVL(v_bonded_flag,'Y') = 'Y' to the condition which checks if the exise_flag is 'Y' for the item
so that excise invoice number gets generated only if item is excisable and subinventory is bonded.
23 2003/01/13 SSUMAITH Bug # 2746921 . File Version - 615.4
Excise invoice number was not getting generated for a trading subinventory.This was reported after the patch
associated with the Bug # 2731434 was applied. It was noticed that , an earlier bug Bug # 2392099 was the reason
as it was considering only "item being excisable" to be the constraint for excise invoice generation .This has been
supplemented by the condition that the subinventory should also be bonded , which caused that for a trading
subinventory , excise invoice number not getting generated. This issue has been fixed by adding the following
check.
Item should be excisable AND (Subinventory is either Bonded or Trading) for excise invoice num to be generated.
23 2003/01/15 cbabu for Bug# 2736191, File Version# 615.5 (Obsoleted with 2803409)
When a trading transaction is done with delivery detail matched to multiple receipts, then RG23D register is not being hit properly.
Code changes are made to hit the register with proper quantity and reduce the balances of receipts as per the matched quantity
24. 2003/01/27 ssumaith Bug # 2769436 File version 616.6 (Obsoleted with 2803409)
When a transaction is done with the register code as DOM_WITHOUT_EXCISE , still excise invoice number was getting
generated based on gp2 instead of gp1.This was because , this transaction type was excluded in the if condition .
adding this condition to the if which takes care of this issue.Also taken care of register type '23D_DOM_WITHOUT_EXCISE'
which was not handled till now.
25 2003/02/19 cbabu for Bug# 2803409, FileVersion# 615.7
DELETE from JAI_OM_OE_GEN_TAXINV_T statement got deleted in file version 615.5(Bug# 2736191) somehow. This statement is
reincorporated with his bug. Bugs 2736191 and 2769436 were made obsolete and this bug needs to be send instead of them
26. 2003/02/20 ssumaith - Bug # 2663211 File Version # 615.8
Excise invoice generation logic in this procedure has been removed and instead a call to the
excise invoice generation procedure has been made.
This has dependency on the jai_cmn_setup_pkg.generate_excise_invoice_no procedure . Hence this bug
is a pre-requisite for future bugs.
27. 2003/07/24 Aiyer - Bug #3032569, File Version 616.1
The Excise Invoice number is being generated for non excisable RG23D transactions.
This needs to be stopped for Trading Domestic Without Excise and Export Without excise scenario's.
Modified the IF statment to remove the check that the trading register_codes should be in
'23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' .
Now the excise invoice number would be generated only for orders with Bond register types as Domestic
Trading With Excise and Export with Excise.
Dependency Introduced Due to this Bug : -
None
28. 2003/07/28 Aiyer - Bug#3071342, File Version 616.2
As the excise invoice generation should not be done in case Domestic Without Excise for trading and manufacturing
organizations and hence modified the if statement to validate that
excise invoice generation procedure is called only in case where v_register_code is in '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE'
,'DOMESTIC_EXCISE', 'EXPORT_EXCISE' ,'BOND_REG'.
This would ensure that the excise invoice generation would not happen in case where v_register_code in
'DOM_WITHOUT_EXCISE','23D_DOM_WITHOUT_EXCISE' i.e Domestic Without Excise for Trading and manufacturing organizations.
Dependency Introduced Due to this Bug : -
None
29. 2003/07/31 SSUMAITH Bug # 2769440 File Version 616.3 (GL Link)
As part of the GL Link Enhancement , added parameters in call to the jai_om_rg_pkg's procedures ja_in_rg23_part_II_entry ,
ja_in_pla_entry and Ja_In_Rg23d_Entry.
Dependency Introduced Due to this Bug : -
This patch has dependency on all further patches using this object.
30. 2003/08/22 SSUMAITH Bug # 3021588 File Version 616.4 (Bond Register Enhancement)
For Multiple Bond Register Enhancement,
Instead of using the cursors for fetching the register associated with the order type , a call has been made to the procedures
of the jai_cmn_bond_register_pkg package. There enhancement has created dependency because of the
introduction of 3 new columns in the JAI_OM_OE_BOND_REG_HDRS table and also call to the new package jai_cmn_bond_register_pkg.
New Validations for checking the bond expiry date and to check the balance based on the call to the jai_cmn_bond_register_pkg has been added
Provision for letter of undertaking has been incorporated. In the case of the letter of undetaking , its also a type of bond register
but without validation for the balances.
This has been done by checking if the LOU_FLAG is 'Y' in the JAI_OM_OE_BOND_REG_HDRS table for the
associated register id , if yes , then validation is only based on bond expiry date .
Dependency Introduced Due to this Bug : -
This fix has introduced huge dependency . All future changes in this object should have this bug as a prereq
31. 2003/09/24 Aiyer - Bug#3139718, File Version 616.5
Modified the cursor get_conv_detail_cur to get the actual_shipment_date from oe_order_lines_all instead of the
conversion_date from oe_order_headers_all.
As a Sales order shipment date can be different from its creation date, hence the conversion rate
applicable on the date of shipment should be considered for all processing rather than the creation
date of the Sales order.
Added a call to jai_cmn_utils_pkg.currency_conversion procedure to get the conversion rate based on the actual shipment date.
Fix of Bug 3158282: -
1. The values passed to the parameters p_basic_ed/p_dr_basic_ed,p_additional_ed/p_dr_additional_ed and p_other_ed/p_dr_other_ed
in the procedures ja_in_rg_I_entry and ja_in_rg23_part_I_entry of the package
jai_om_rg_pkg was rounded off (using a round function to 0 decimal values) so that all values of columns basic,
additional and other amounts passed to the tables JAI_CMN_RG_I_TRXS, JAI_CMN_RG_23AC_I_TRXS table
get rounded off.
2. For the calls to the procedure procedures ja_in_rg23_part_II_entry,ja_in_pla_entry,ja_in_regsiter_txn_entry in the jai_om_rg_pkg
the values of fields being passed i.e basic_excise_duty_amount, add_excise_duty_amount, oth_excise_duty_amount and excise_amount
have been rounded off at the cursor get_total_excise_amt level itself, as these values should always be in INR.
Dependency Due to this bug:-
None
32. 11/10/2003 ssumaith - Bug # 3158976 File Version 616.6
Sales order number is appended to the variable v_ref_10 which holds a standard text
in a loop of delivery details for a particular delivery.
If the number of delivery details are huge , the appending is causing the width of the concatenated text
to exceed beyond 250 characters.
It causes PL/SQL Numeric or value error.
33. 11/10/2003 ssumaith - bug # 3138194 File Version 616.7
Population of ST forms related functionality is removed from this procedure and instead moved into a
new concurrent program. All other logic remains same , with respect to hitting the RG registers.
34. 4-nov-03 ssumaith - bug # 3207685
For excise exempted transactions, modvat entry was happening only for the first line.
The reason this happens because the value being fetched was only of the first record.
This has been corrected by calculating the excise exempted amount correctly and passing
it to the jai_om_rg_pkg .
35. 11-Nov-2003 Aiyer - Bug #3249375 File Version 617.1
References to JA_IN_OE_ST_FORMS_HDR table, which has been obsolete post IN60105D1 patchset, was found
in this file in some cursors.
As these tables do not exists in the database any more, post application of the above mentioned patchset
hence deleting the cursors.
Dependency Due to This Bug:-
Can be applied only post application of IN60105D1.
36.05-Dec-2003 ssumaith - bug # 3229697 version 617.2
Performance improvement done in 'Excise invoice genration program'
Dependency Due to This Bug:-
None
37.30-jan-04 ssumaith bug# 3368475 file version 618.1
CENVAT Reversal Entries should not be passed for CT3 Transaction.
On Shipping goods to an 100% EOU against a CT 3 Form at an exempted rate,
India Localization reverses the CENVAT at 8% of the Base Amount. This
is incorrect. The CENVAT should not be reversed.
This has been acheived by making code changes:
1) if CT3 type of excise exemption is chosen in the sales order :
only quantity register gets hit
amount register does not get hit.
2) if other than CT3 type of excise exemption is chosen in the sales order
both quantity and amount registers get hit.
3) Please note that in cases where amount registers are not hit because of CT3 excise exemption, the
register column in the JAI_OM_WSH_LINES_ALL table will show NULL. this is
also the change which i have incorporated.
Dependency Due to This Bug:-
None
38. 18-Feb-2004 Aiyer Bug #3448674, File Version 618.2
Issue:-
======
Amount registers are hit with excise amoutns which are getting round at Shipping Line level
instead of at a Delivery level.
Solution:-
==========
Changed the cursor get_total_excise_amt to do rounding at a paticualr delivery level rather
than the line level.
For this , we are first summing the taxes and then rounding them. This prevents line level
rounding and enforces delivery level rounding.
Dependency Due to This Bug:-
None
38. 09-Mar-2004 Aiyer Bug #3446362, File Version 618.3(reopened) fixed in 618.4.
Issue:-
==========
When a Order has multiple lines out of which one line is Excise Exempted and the other are not,
then the behaviour expected is that the item which is not excise exempt should hit Excise registers.
However, the same is not currently happening.
Reason: -
======
This was happening as the code that calls the jai_om_rg_pkg package to insert records into any of the amount
registers i.e either of JAI_CMN_RG_23AC_II_TRXS or JAI_CMN_RG_PLA_TRXS was bypassed if the first one was found to contain
a 'CT3' type of exemption.
Fix :-
======
Modified the code to check whether any of the delivery details for the given delivery has a excise exemption
type other than 'CT3' (this includes null rows also). Only if one or more such records exist, then
hit the amount registers, else bypass the call.
Cursor c_ct3_flag_exists has been added to the code to take the count of records which do not have excise exemption
of type CT3.
38. 19-Mar-2004 Aiyer Bug #3446362, File Version 618.4(reopened) fixed in 619.1
Issue:-
========
The bug 3446362 version 618.4 of this file did not work correctly on the clients instance.
It was still not entering the excise amount line in the amount registers when the order conatined mulitple lines with the first line excise exempted.
Reason:-
The amount register is hit based on the which amount register needs to be hit.The value of amount register to be hit is stored in the variable v_reg_type.Now initially the v_reg_type
would be set to Null for the line which had 'CT3'type of exemption.
Now if this line happens to be the first line then and the register are also hit only once, so the code ignores the other lines as v_reg_type is set to null.
Fix:-
=====
The fix done is that instead of setting the v_reg_type to null when a 'CT3' exemption is found and later
updating this variable into JAI_OM_WSH_LINES_ALL.register, handled this condition though a decode
statement, instead of directly updating the v_reg_type variable.
The v_reg_type now still holds the value for the register, where as the table is updated with null when ever the exemption is 'CT3'.
This has rectified the problem.
Dependency Due to This Bug:-
None
39. 24-Aug-2004 Sanjikum Bug #3849638, File Version 115.1
Issue:-
========
Excise invoice number is generated even when inventory interface ends in warning
Reason:-
For selecting the cases where Inventory Interface has failed, the following condition was being used -
NVL(wdd.inv_interfaced_flag,'N') = 'N'
For the failure there can be one more status - 'P'. Which is being missed in this case.
Fix:-
=====
The fix done is that instead of condition - NVL(wdd.inv_interfaced_flag,'N') = 'N'
The new condition is used - NVL(wdd.inv_interfaced_flag,'N') <> 'Y'
This has rectified the problem.
While updating the JAI_OM_WSH_LINES_ALL, new columns are added -
last_update_date, last_updated_by, last_update_login, as these were not previously updated
Dependency Due to This Bug:-
None
40. 20-JAN-2005 - ssumaith - Bug#4136981 - Corrected the call to the JAI_OM_OE_BOND_TRXS entry.
It was not consistent with the other calls , such as rg1 entry , rg23_part_ii entry , pla entry.
This fix introduces no dependency.
41. 2005/02/11 ssumaith - bug# 4171272 - File version 115.3
Shipment needs to be stopped if education cess is not available.
The basic business logic validation is that both cess and excise should be available as
part of the same register type and the register preference setup at the organization additional information
needs to be considered for picking up the correct register .
This code object calls the functions jai_om_wsh_processing_pkg.excise_balance_check_f and jai_om_wsh_pkg.get_excise_register_with_bal_f
which have had changes in their signature and hence the caller also needs to pass the correct
parameters.
The change done in this object is to pass the additional parameters correctly to the functions.
Dependency Due to this Bug:-
The current procedure becomes dependent on the functions jai_om_wsh_processing_pkg.excise_balance_check (version 115.1) and
jai_om_wsh_pkg.get_excise_register_with_bal (version 115.1) also packaged as part of this bug.
42. 2005/02/16 ssumaith - bug# 4185392 - File version 115.4
Excise Duty rate was going in as zero in JAI_CMN_RG_I_TRXS table. This was because the variable
corresponding to the excise_duty_rate parameter in the ja_in_rg_i_entry procedure was
explicitly set to zero.
This has been changed and made as excise_duty_amount divided by (assessable value * shipped quantity).
Care has been taken to ensure that zero divide error does not come by checking for non zero values
for the elements in the denominator of the fraction.
As expected by IL support , rounding the tax_rate to two decimals.
Dependency Due to this Bug:-
None.
43. 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
44. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.4
Removal of SQL LITERALs is done
45. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.5
GL Sources and GL Categories got changed. Refer bug for the details
46. 9-Aug-2005 Ramananda for bug#4543424. File version 120.2
Excise Duty rate was going as fractions in JAI_CMN_RG_I_TRXS table.
This is because the excise amount is rounded off at the shipment level and when the rate is recalculated
it is calculated as excise amount divided by assessable value * 100. This results in rounding issue.
This has been resolved by making the following changes.
The excise rate is calculated as a sum of total 'EXCISE' tax rates divided by the number of 'EXCISE' taxes.
Dependency Due to this Bug:-
None.
47. 19-Aug-2005 Bug4562791. Added by Lakshmi Gopalsami Version 120.3
The excise invoice date , the date on which rg registers are hit should be the
date when the concurent request is submitted. This is as per the product
management requirement.
Hence added a cursor that gets the request submitted date and punched that
date in the calls to the ja_in_rg_pkg.ja_in_rg23_part_ii_entry ,
ja_in_Rg_pkg.pla_entry and the same gets carried forward
to the gl interface as well.
Also changed the creation_date and last_update_date with v_date.
Dependencies (Compilation and Functional Dependencies)
------------
jai_om_rg.pls 120.2
jai_om_rg.plb 120.3
48. 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
49. 01-DEC-2005 Aiyer - Bug 4765347 ,Version 120.5
Issue :- Excise invoice program runs into error.
Fix :- Changed the form JAIITMCl.fmb to insert into the jai_inv_items_setups form
also changed the current procedure to add who column into jai_cmn_errors_t.
Dependencies introduced due to this bug:-
Yes, please refer the future dependencies section.
50. 13-Feb-2007 bgowrava for forward porting bug#5554420 (11i bug#5531051). File Version 120.7
Issue: Excise invoice/register not getting updated properly in ja_in_so_picking_lines
Also observed that if excise invoice is not generated for first line of delivery,
then it is not getting generated at all.
Resolution: introduced the flag to know whether excise invoice is generated for the delivery.
if not generated for 1st line, added the code to execute the generation code again for next lines for delivery
- added new cursor c_excise_tax_cnt to know the excise taxes count and generate exc invoice number
only if count > 0
- Added the logic to execute amount register hitting logic once for every delivery (usually this
will be done for the first line that has excise implication)
51. 23/02/07 bduvarag for bug#5403048,File version 120.8
Forward porting the changes done in 11i bug 5401533
52. 13-April-2007 ssawant for bug 5989740 ,File version 120.9
Forward porting Budget07-08 changes of handling secondary and
Higher Secondary Education Cess from 11.5( bug no 5907436) to R12 (bug no 5989740).
53. 18/Apr/2007 Bgowrava for forward porting bug#5989740, 11i BUG#5907436 File Version 120.9
ENH: Handling Secondary and Higher Education Cess
Added the new cess types jai_constants.tax_type_sh_exc_edu_cess, jai_constants.tax_type_sh_cvd_edu_cess
54. 28/05/2007 CSahoo for bug#6077065, File version 120.10
Issue: The excise invoice num and excise_invoice_date was not getting updated in the JAI_OM_WSH_LINES_ALL table.
Fix:added the following line in the code lv_exc_inv_gen_for_dlry_flag := 'Y'.
55. 05/07/2007 kunkumar for Bug#6121833 File version 120.11
Added an if condition After open-fetch-close in lv_statement_no :=18
56. 20-MAY-2008 JMEENA bug#7043292
Issue :- Excise Invoice Generation completes with ORA-01476
Fix :- The calculation of v_proportionate_edr was using v_total_base_duty_amount fetched
from matched_receipt_cur which was comming as zero.
A check is introduced to verify that v_proportionate_edr is calculated
only in the case when v_total_base_duty_amount is non-zero.
57. 26-Aug-2008 vkaranam for bug#7354983, File Version 115.20.6107.11
Issue: EXCISE INVOICE DATE AND AR INVOICE DATE FOR DELIVERY ID NOT IN SYNCHRONIZATION
FIX: Modified the code to populate the excise invoice date with the actual shipment date.
Further the gl accounting date and the transaction dates for all the register
updates is also populated by the actual shipment date.
58. 30-dec-2008 CSahoo for bug#7647742, File Version 120.6.12000000.7
Reverted the changes done in the file version 120.6.12000000.6
Further removed the nvl condition in IF condition for checking modvat tax rate.
59. 10-nov-2009 vkaranam for bug#8904363
Issue:
TST1212 XB1:QA: UNABLE TO GENERATE EXCISABLE INVOICE
If the subinventory is neither bonded nor tradable ,conncurrent request is not showing the user message.
Fix:
Added the user log message
"Subinventory is neither bonded nor tradable .henc eexcise invoice number cannot be generated".
this will be displayed in the India excise invocie generation log file only if bonded_flag='N' and trading_flag='N'.
60 24-jun-2010 vkaranam for bug#9839132
issue:
rg23d register is hitting the wrong duty for foreign currency transactions.
fix:
passed v_tot_duty_amt instead of v_excise_amount*conversion rate.
61. 17-Dec-2012 mmurtuza for bug 15971482
Description: INDIA - RG1 REGISTER REPORT SHOWING THE EXCISE PERCENTAGE WRONGLY
Fix: Commented the fix done for bug 4185392. Also added cursor c_excise_base_tax_amt to fetch base tax amount of excise tax
and pass the same to jai_om_rg_pkg.ja_in_rg_i_entry.
62. 31-Dec-2012 nkodkan for the bug 16014905
Description: RG1 REGISTER ENTRY FORM SHOWING THE ASSESSABLE VALUE IN FOREIGN CURRENCY
Fix : Multiplied the Assessable Value and excise amount with conversion rate (v_converted_rate)
63. 21-Jan-2013 nkodakan for the bug 16197185
Description: RG1 REGISTER ENTRY FORM SHOWING THE ASSESSABLE VALUE IN FOREIGN CURRENCY
Fix : removed the v_converted_rate which is multiplying with ln_total_of_base_tax in a call jai_om_rg_pkg.ja_in_rg_i_entry
64. 19-FEB-2013 nkodakan for the bug 16353242
Description : TIME STAMP FOR EXCISE INVOICE IS NOT COMING IN JAI_OM_WSH_LINES_ALL TABLE
Fix : Removed the TRUNC function for v_actual_shipment_date
65. 05-Mar-2013 mmurtuza for bug 16308603
Description: INDIA EXCISE INVOICE GENERATION PROGRAM ,PICKS ALL DELIVERIES ACROSS OU
Fix: Changed the pick to pick the delivery ids when the delivery id is not provided as input
66. 22-Mar-2013 mmurtuza for bug 16534065
Description: EXCISE AMOUNT IS ROUNDED IN REGISTERS
Fix: Removed the rounding
07-Apr-2013 mmurtuza for bug 16343067
Description: EXCISE AMOUNT IS ROUNDED IN REGISTERS
Fix: Added the rounding for get_total_excise_amount
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
process_delivery.sql
----------------------------------------------------------------------------------------------------------------------------------------------------
616.1 3032569 IN60104D1 None -- Aiyer 24/07/2003 Row introduces to start dependency tracking
616.3 2769440 IN60104D1 + jai_om_rg_pkg.sql Ssumaith 31/07/2003 GL Link Enhancement.
2801751 + ja_in_gl_interface_new.sql
2769440
616.4 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
2801751 +
2769440
617.1 3249375 IN60105D1 Aiyer 11/Nov/2003 Can be applied only after IN60105D1 patchset
has been applied.
619.1 3446362 IN60105D2 None -- Aiyer 19/03/2004 Row introduces to start dependency tracking
115.3 4171272 IN60106 +
4147608 jai_om_wsh_pkg.get_excise_register_with_bal_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
jai_om_wsh_processing_pkg.excise_balance_check_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
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
120.4 4765347 JAIITMCL.fmb 120.9
jai_om_rg.plb 120.4
------------------------------------------------------------------------------------------------------------------------------------------------*/
/* Ramananda for File.Sql.35 */
lv_debug := jai_constants.yes ;
SELECT *
FROM JAI_OM_OE_GEN_TAXINV_T ja_tmp
WHERE delivery_id = p_delivery_id
and /*The idea of putting the exists is to see that do not process a delivery ,if it has
at least one delivery detail with inv_interfaced_flag = 'N' */
not exists
( select 1
FROM
wsh_delivery_details wdd ,
wsh_new_deliveries wnd ,
wsh_delivery_assignments wda
WHERE
wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.Delivery_Id = wnd.Delivery_Id AND
wnd.Delivery_Id = ja_tmp.delivery_id AND
wdd.source_code = 'OE' AND
--NVL(wdd.inv_interfaced_flag,'N') = 'N'
--Commented the above and added the below for bug #3849638
NVL(wdd.inv_interfaced_flag,'N') <> 'Y'
)
)
LOOP
v_ref_26 := p_delivery_id ;
v_last_update_date := Each_record.last_update_date;
v_last_updated_by := Each_record.last_updated_by;
v_last_update_login := Each_record.last_update_login;
SELECT NVL(Item_Trading_Flag,'N') INTO V_item_trading_flag
FROM JAI_INV_ITM_SETUPS
WHERE organization_id = v_organization_id
AND inventory_item_id = v_inventory_item_id;
UPDATE
JAI_OM_WSH_LINES_ALL
SET
excise_invoice_no = v_exc_invoice_no,
--excise_invoice_date = TRUNC(v_actual_shipment_date), --replaced v_date for bug#7354983
-- commented the above and added below for the bug 16353242
excise_invoice_date = v_actual_shipment_date,
register = DECODE(nvl(v_excise_exempt_type,'$$$'), 'CT3',NULL,v_reg_type), /*register should be updated as null incase of CT3 excise exemption Bug 3446362*/
order_line_id = v_source_line_id_pick, /*2001/09/13 Jagdish */
--added the next 3 columns for Bug #3849638, as these were not updated previously
last_update_date = sysdate,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE
organization_id = v_organization_id AND
location_id = v_location_id AND
delivery_detail_id = each_record.delivery_detail_id;
v_register_code , /* Bug 4562791. Added by Lakshmi Gopalsami Commented creation_date and last_update_date and passing v_date v_creation_date ,*/
v_date ,
v_created_by ,
--v_last_update_date ,
v_date ,
v_last_updated_by ,
v_last_update_login ,
--v_assessable_value , /*Commented and added below by mmurtuza for bug 15971482*/
ln_total_of_base_tax , /* multiplied with v_converted_rate for the bug 16014905 */
/* removed the v_converted_rate which is multiplying with ln_total_of_base_tax for bug 16197185 */
ln_del_det_totcess_amt , /*Parameters p_cess_amt and p_source added by aiyer for the bug 4566054 */
ln_del_det_totshcess_amt , --Bgowrava for forward porting bug#5989740
jai_constants.source_wsh
);
SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_part_i_register_id FROM dual;
Commented creation_date and last_update_date
and passing v_date
v_creation_date ,*/
v_date ,
v_created_by ,
--v_last_update_date ,
v_date ,
v_last_updated_by ,
v_last_update_login
);
SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_part_i_register_id FROM dual;
Commented creation_date and last_update_date
and passing v_date
v_creation_date ,*/
v_date ,
v_created_by ,
--v_last_update_date ,
v_date ,
v_last_updated_by ,
v_last_update_login ,
each_record.delivery_detail_id ,
v_excise_exempt_type ,
v_remarks ,
v_ref_10 , -- bug # 2769440
v_ref_23 , -- bug # 2769440
v_ref_24 , -- bug # 2769440
v_ref_25 , -- bug # 2769440
v_ref_26 -- bug # 2769440
);
Commented creation_date and last_update_date
and passing v_date
v_creation_date ,*/
v_date ,
v_created_by ,
--v_last_update_date ,
v_date ,
v_last_updated_by ,
v_last_update_login ,
v_ref_10 , -- bug # 2769440
v_ref_23 , -- bug # 2769440
v_ref_24 , -- bug # 2769440
v_ref_25 , -- bug # 2769440
v_ref_26 -- bug # 2769440
);
SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no FROM dual;
SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no FROM dual;
UPDATE
JAI_CMN_RG_I_TRXS
SET
register_id_part_ii = v_rg23_part_ii_no,
charge_account_id = (SELECT
charge_account_id
FROM
JAI_CMN_RG_23AC_II_TRXS
WHERE
register_id = v_rg23_part_ii_no
)
WHERE
register_id = v_rg23_part_i_no;
SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no FROM dual;
UPDATE
JAI_CMN_RG_I_TRXS
SET
register_id_part_ii = v_pla_register_no,
charge_account_id = (
SELECT CHARGE_ACCOUNT_ID
FROM
JAI_CMN_RG_PLA_TRXS
WHERE
register_id = v_pla_register_no
)
WHERE
register_id = v_rg23_part_i_no;
SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no FROM dual;
SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no FROM dual;
UPDATE JAI_CMN_RG_23AC_I_TRXS
SET REGISTER_ID_PART_II = v_rg23_part_ii_no,
CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE register_id = v_rg23_part_ii_no)
WHERE register_id = v_rg23_part_i_no;
SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no FROM dual;
UPDATE
JAI_CMN_RG_23AC_I_TRXS
SET
REGISTER_ID_PART_II = v_pla_register_no,
charge_account_id = (
SELECT
charge_account_id
FROM JAI_CMN_RG_PLA_TRXS
WHERE
register_id = v_pla_register_no
)
WHERE register_id = v_rg23_part_i_no;
Commented creation_date and last_update_date
and passing v_date
v_creation_date ,*/
v_date ,
v_created_by ,
--v_last_update_date ,
v_Date ,
v_last_updated_by ,
v_last_update_login
);
SELECT NVL(Item_Trading_Flag,'N') INTO V_item_trading_flag
FROM JAI_INV_ITM_SETUPS
WHERE organization_id = v_organization_id
AND inventory_item_id = v_inventory_item_id;
UPDATE JAI_CMN_MATCH_RECEIPTS
SET ship_status='CLOSED'
WHERE ref_line_id = each_record.delivery_detail_id
AND ORDER_INVOICE = 'O'; -- cbabu for Bug# 2736191
SELECT JAI_CMN_RG_23D_TRXS_S.NEXTVAL INTO v_register_id FROM Dual;
Commented creation_date and last_update_date
and passing v_date
v_creation_date ,*/
v_date ,
v_created_by ,
--v_last_update_date ,
v_date ,
v_last_update_login ,
v_last_updated_by ,
--added for bug#6199766 ,start
v_tot_basic_ed_amt ,
v_tot_addl_ed_amt ,
v_tot_oth_ed_amt ,
--added for bug#6199766 ,end
v_exc_invoice_no ,
TRUNC(v_actual_shipment_date) ,--replaced v_date for bug#7354983
v_ref_10 , -- bug # 2769440
v_ref_23 , -- bug # 2769440
v_ref_24 , -- bug # 2769440
v_ref_25 , -- bug # 2769440
v_ref_26 , -- bug # 2769440
v_tot_cvd_amt,
- v_tot_addl_cvd_amt /*Added by nprashar for bug # 5735284 */
);
DELETE JAI_OM_OE_GEN_TAXINV_T WHERE delivery_detail_id = each_record.delivery_detail_id;
ln_last_update_login NUMBER ; /* added by aiyer for the bug 4765347*/
ln_last_update_login := fnd_global.conc_login_id ;
INSERT INTO JAI_CMN_ERRORS_T (
APPLICATION_SOURCE ,
error_message ,
additional_error_mesg ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
lv_procedure_name ,
lv_error_mesg ,
'EXCEPTION captured BY WHEN OTHERS IN the PROCEDURE. BLOCK No/STATEMENT No:' || lv_block_no || '/' || lv_statement_no,
v_date ,
ln_created_by ,
ln_created_by , /* added by aiyer for the bug 4765347*/
v_date ,/* added by aiyer for the bug 4765347*/
ln_last_update_login /* added by aiyer for the bug 4765347*/
);
SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
Value,SUBSTR (value,1,INSTR(value,',') -1))
INTO v_utl_location
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT trip_id
INTO v_trip_id
FROM wsh_delivery_trips_v
WHERE delivery_id=p_delivery_id;
FOR temp_rec IN ( Select distinct joogtt.delivery_id
From JAI_OM_OE_GEN_TAXINV_T joogtt,
jai_om_wsh_lines_all jowla
where joogtt.delivery_id = jowla.delivery_id
and mo_global.check_access(jowla.org_id) = 'Y'
) /*Added join of table jai_om_wsh_lines_all and check_access by mmurtuza for bug 16308603*/
LOOP
BEGIN
fnd_file.put_line ( fnd_file.log , ' Calling jai_om_wsh_pkg.process_delivery with delivery id => ' || temp_rec.delivery_id );