[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 1
From JAI_OM_WSH_LINES_ALL
Where delivery_detail_id = v_delivery_detail_id;
DELETE JAI_OM_WSH_LINES_ALL
WHERE delivery_detail_id = v_DELIVERY_DETAIL_ID;
DELETE JAI_OM_WSH_LINE_TAXES
WHERE delivery_detail_id = v_DELIVERY_DETAIL_ID;
Trigger written to delete data from tables
JAI_CMN_MATCH_RECEIPTS, JAI_CMN_MATCH_TAXES.
After Match Receipt is done, when back order is done,
this trigger enables to match again.
2. 29/11/2005 Aparajita for bug#4036241. Version#115.1
Introduced the call to centralized packaged procedure,
jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
3. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE. Version 116.1
4. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
Removal of SQL LITERALs is done
Dependency:
----------
Sl No. Bug Dependent on
Bug/Patch set Details
-------------------------------------------------------------------------------------------------
1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
4033992.
ja_in_util_pkg_s.sql 115.0
ja_in_util_pkg_b.sql 115.0
--------------------------------------------------------------------------------------------------*/
--if
-- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_WSH_BACKORDER_AU_TRG', p_inventory_orgn_id => pr_new.organization_id)
-- =
-- FALSE
--then
/* India Localization funtionality is not required */
-- return;
Delete from JAI_CMN_MATCH_RECEIPTS
Where ref_line_id = pr_new.delivery_detail_id;
Delete from JAI_CMN_MATCH_TAXES
Where ref_line_id = pr_new.delivery_detail_id;
SELECT
NVL(Location_id,0),
trading
FROM
JAI_INV_SUBINV_DTLS
WHERE
Sub_Inventory_Name = v_subinventory AND
organization_id = v_organization_id ;
SELECT
Item_Trading_Flag
FROM
JAI_INV_ITM_SETUPS
WHERE
organization_id = v_organization_id AND
inventory_item_id = v_inventory_item_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
FROM
JAI_CMN_MATCH_RECEIPTS a
WHERE
a.ref_line_id = v_delivery_detail_id
AND a.order_invoice = 'O' ;
SELECT
excise_flag ,-- Commented attribute1 by Brathod for Bug# 4299606 (DFF Elimination)
modvat_flag ,-- Commented attribute2 by Brathod for Bug# 4299606 (DFF Elimination)
nvl(container_item_flag,'N') --Added by Nagaraj.s for Bug3123613.
FROM
mtl_system_items msi,
JAI_INV_ITM_SETUPS jmsi -- Added by Brathod for Bug# 4299606 (DFF Elimination)
WHERE msi.organization_id = jmsi.organization_id
AND msi.inventory_item_id = jmsi.inventory_item_id
AND jmsi.inventory_item_id = v_Inventory_Item_Id -- Added by Brathod for Bug# 4299606 (DFF Elimination)
AND jmsi.organization_id = v_organization_id; -- Added by Brathod for Bug# 4299606 (DFF Elimination)
SELECT
lc_flag
FROM
JAI_OM_OE_SO_LINES
WHERE
lc_flag = 'Y' AND
header_id = pr_new.source_header_id;
SELECT
sum(qty_matched)
FROM
JAI_OM_LC_MATCHINGS
WHERE
order_header_id = pr_new.source_header_id AND
-- order_line_id = pr_new.source_line_id AND --commented by csahoo for bug#5686360
delivery_detail_id = v_delivery_detail_id AND
release_flag is null;
select split_from_line_id --, split_by
from oe_order_lines_all
where line_id = pr_new.source_line_id;
select order_line_id
from JAI_OM_LC_MATCHINGS
where delivery_detail_id = v_delivery_detail_id;
ln_lc_update_cnt number;
SELECT
tax_amount
FROM
JAI_OM_OE_SO_LINES
WHERE
line_id = pr_new.source_line_id AND
header_id = pr_new.source_header_id;
SELECT
nvl(sum(so_tax.tax_amount),0)
FROM
JAI_OM_OE_SO_TAXES so_tax
, jai_cmn_taxes_all tax -- Added by Jia Li for inclusive tax on 2008/01/07
WHERE
line_id = pr_new.source_line_id AND
header_id = pr_new.source_header_id
AND so_tax.tax_id = tax.tax_id -- Added by Jia Li for inclusive tax on 2008/01/07
AND NVL(tax.inclusive_tax_flag,'N') = 'N' ; -- Added by Jia Li for inclusive tax on 2008/01/07
SELECT
a.excise_exempt_type ,
a.line_number ,
a.shipment_line_number ,
quantity -- added by sriram bug# 3441684
FROM
JAI_OM_OE_SO_LINES a
WHERE
a.line_id = pr_new.source_line_id AND
a.header_id = pr_new.source_header_id ;
select trading
from JAI_CMN_INVENTORY_ORGS
where organization_id = pr_new.organization_id
and location_id = pr_new.ship_from_location_id ;
SELECT 1
FROM JAI_OM_OE_SO_TAXES jstl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v tax_types
WHERE jstl.line_id = cp_line_id
AND jstl.header_id = cp_header_id
AND jtc.tax_id = jstl.tax_id
AND jtc.tax_type = tax_types.tax_type
AND tax_types.regime_code = jai_constants.vat_regime;
SELECT 1
FROM jai_rgm_parties rgmpt,
JAI_RGM_DEFINITIONS rgms
WHERE rgmpt.regime_id = rgms.regime_id
AND rgmpt.location_id = cp_location_id
AND rgmpt.organization_id = cp_organization_id
AND rgms.regime_code = jai_constants.vat_regime;
SELECT
*
FROM
oe_order_headers_all
WHERE
header_id = cp_header_id ;
on updates of other fields at which point the releaed_status is 'C'.
Also the update statement , which updates the released_flag in the JAI_OM_LC_MATCHINGS table
has been commented , because the update should happen after the shipping has completed.
Also an error message has to be thrown , when shipping is done , for an order
where the tax amounts in the JAI_OM_OE_SO_TAXES and JAI_OM_OE_SO_LINES do not tally.
3. Avishek - Bug # 2928261
Added a RAISE_APPLICATION_ERROR to give an error message when a sub-inventory is not
associated properly with a location ID.
4. Aiyer - Bug # 3039521, File Version 616.1 Date 09-Jul-2003
Issue:-
1. Orders which are partially matched and shipped post splitting of lines without full matching get shipped and are not stopped by
this trigger as it does when the lines are not split.
2. Another issue reported with this trigger was that it used to get fired even in case for non Indian Shipments.
These shipments need not have the Localization sub-inventory setup - Mandatory for all Indian Shipments
(using localization). However as this trigger used to get fired in those cases also hence the error
"Mandatory Localization sub-inventory setup is not made" used to get invoked and the execution used
to stop.
Solution:-
1. While checking that for a line being shipped, if the LC flag is enabled , then the amount being shipped should be
lc matched. However it was not being checked that, in case of a split line also the amount pertaining to the
same delivery_detail_id should be lc matched.
Added a where clause in the cursor c_matched_qty_cur to also include the check for the delivery_detail_id.
2.Put a check in the beginning of the trigger that if the functional currency is NOT 'INR' then
return from the trigger, i.e this trigger should get bypassed in case of Non Indian Shipments
(Global Scenario).
5. Aiyer - Bug #3032569, File Version 616.2 Date 24-Jul-2003
Issue:-
The trigger checks that the match receipt functionality is performed in scenario's of
Trading Domestic Without Excise and Export Without excise.
This check is not required. The match receipts only needs to be done for
'Trading Domestic With Excise' and 'Export with Excise' type of Scenario's
Solution:-
Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and 23D_EXPORT_EXCISE.
The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have been removed as matched receipts
is not relevant in this case.
Dependency Introduced Due to this Bug : -
None
6. Nagaraj.s -Bug#3123613, File Version : 616.3 Date:04-Sep-2003
Added the container_item_flag also in the cursor get_item_attributes and the
check for Mandatory Location is added with one more condition of :
v_container_item_flag ='N' , so that in case of containerization, the check
does not hold good.
7. Aiyer -Bug# 3392528 , File Version : 618.1 Date:11-Feb-2004
Issue
=====
An sales order In India Localization Order Management should not be allowed to be shipped on the following
conditions : -
1. A Sales order with excise exemption types like EXCISE_EXEMPT_CERT,
CT2,CT3 has modvat type of taxes attached
2. A Sales order with excise exemption types like EXCISE_EXEMPT_CERT_OTH,
CT2_OTH does not have modvat recovery type of tax attached
3. A sales order does not have any excise exemptions specified and still
has Modvat Recovery type of taxes.
These check also needs to be implemented at shipping level
Solution:-
Created the procedure jai_om_utils_pkg.validate_excise_exemption to validate all the above conditions and called the same
with the relevent parameters. This has resolved the issue
Dependency Due To The Current Bug :
1. This trigger ja_in_wsh_dlry_au_rel_stat_trg.sql (618.1) call the
function ->jai_om_utils_pkg.validate_excise_exemption(618.1) and hence has dependencies
8. ssumaith - Bug#3441684 file version 618.2 : 20-feb-04
After match receipts is done for a delivery , when back-ordering is performed, the delivery
is back-ordered , but the matched info is not removed from the match receipts table.
The requirement is to raise an error when backordering is done without unmatching the delivery.
Dependency Due to this bug:
None
9. Aiyer 23/08/2004 Bug# 3844145 file version 115.2
Issue:-
The inventory trip stop concurrent program errors out for a Internal sales order with qty being matched to two different subinventories.
Reason :-
The existing code was considering matched quantites as per the subinventories. This should not be considered.
Matched quantities should be considered irrespective of the subinventories.
Solution:-
Modified the cursor matched_receipt_cur1 in the current trigger.Removed the group by subinventory clause from the query.
Dependency Due to this bug:
None
10. Aparajita 30/11/2004. Bug#4036241. Version#115.3
Introduced the call to centralized packaged procedure,
jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
11. Brathod 21/03/2005. Bug#4245073. Version# 115.4, 115.5
Trigger modified to check whether vat type of tax exists or not.
If vat type of tax exits and organization, location setup does not
exits for vat rigme, trigger will throw an exeception.
Also if vat type of tax exits but the item is not vatable trigger
will throw an exception
12 Brathod 26/04/2005for 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)
13 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE. Version 116.1
14. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
15. Aiyer 22/08/2005. Bug#4566002(Forward Porting for bug 4426615), File Version# 120.3
Issue : -
After LC Matching, the user is not being allowed to Backorder the quantity.
Fix:-
The matched quantity = shipped quantity should be checked only in case of ship confirm release_status = 'C'.
This check should not happen on Backordering release_status = 'B'.
16. 30-Jan-2007 bgowrava for forward porting bug#5631784(4742259), File Version 120.4
Added the call to jai_ar_tcs_rep_pkg.process_transaction. for TCS related validations.
Dependencies Due to this bug:-
This bug has lots of datamodel abd specification changes.
17. 17-May-2007 CSahoo for bug#5686360 File Version 120.5
Forward porting of 11i BUG#5665937
modified the cursor c_matched_qty_cur to filter the data only by delivery_detail_id
and order header_id and removed the filter by order line_id
Added the cursors c_order_line and c_lc_mtch_dlry_line, to check if the order line attached
the delivery detail in LC matching table is same or not. If not, then the related delivery_detail
line is updated with the :new.source_line_id
Appended SQLERRM to the fnd_message.set_token value parameter to display the error
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 Dependency On Files Version Author Date Remarks
Of File On Bug/Patchset
ja_in_wsh_dlry_au_rel_stat_trg.sql
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
618.1 3392528 IN60105D2 ja_in_val_exc_exmpt_f.sql 618.1 Aiyer 11-Feb-2004 This trigger calls the function ja_in_val_exc_exmpt_f.sql
115.3 4036241 4033992 ja_in_util_pkg_s.sql 115.0 115.0 Apdas 30-nov-04
ja_in_util_pkg_b.sql 115.0 115.0 apdas 30-11-2004
115.5 4245073 4245089 ALL VAT Objects
115.6 4299606 IN60106
+ 4239736 (Service Tax)
+ 4245089 (VAT)
18. 01-JAN-2008 Added by Jia Li
for Inclusive tax Computation
-------------------------------------------------------------------------------------------------------------------*/
--File.Sql.35 Cbabu
v_inventory_item_id :=pr_new.inventory_item_id ;
ln_lc_update_cnt := -1;
update JAI_OM_LC_MATCHINGS
set order_line_id = pr_new.source_line_id
where delivery_detail_id = v_delivery_detail_id
-- and order_line_id = r_order_line.split_from_line_id
and release_flag is null;
ln_lc_update_cnt := sql%rowcount;
v_last_update_date DATE ; --File.Sql.35 Cbabu :=pr_new.Last_Update_Date;
v_last_updated_by NUMBER; --File.Sql.35 Cbabu :=pr_new.Last_Updated_By;
v_last_update_login NUMBER; --File.Sql.35 Cbabu :=pr_new.Last_Update_Login;
SELECT A.delivery_id,
A.confirm_date,
A.status_code
FROM Wsh_Delivery_Assignments B,
Wsh_New_deliveries A
WHERE B.Delivery_Id = A.Delivery_Id
AND B.Delivery_Detail_id = v_delivery_detail_id;
SELECT attribute1 ,attribute2
FROM mtl_system_items
WHERE inventory_item_id = v_Inventory_Item_Id
AND organization_id = v_organization_id;
SELECT excise_flag, modvat_flag
FROM JAI_INV_ITM_SETUPS
WHERE inventory_item_id = v_Inventory_Item_Id
AND organization_id = v_organization_id;
SELECT COUNT(*)
FROM JAI_OM_OE_SO_LINES
WHERE Line_id = v_source_line_id;
SELECT NVL(Selling_Price,0),
NVL(Quantity,0),
NVL(Tax_Category_Id,0),
NVL(Assessable_Value,0),
NVL(vat_assessable_value,0),
Excise_Exempt_Type,
Excise_Exempt_Refno,
Excise_Exempt_Date,
-- Added by Brathod for Bug#4215808
vat_exemption_flag,
vat_exemption_type,
vat_exemption_date,
vat_exemption_refno
-- End of Bug#4215808
FROM JAI_OM_OE_SO_LINES
WHERE Line_id = v_source_line_id;
SELECT
transactional_curr_code ,
conversion_type_code ,
conversion_rate ,
nvl(b.actual_shipment_date,sysdate) 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 = v_source_line_id AND
a.header_id = v_source_header_id ;
SELECT b.Tax_Type,
NVL(b.Rounding_Factor,2) Rounding_Factor, --changed the rounding factor to 2 if it is null, JMEENA bug#6280735 (FP 6164922)
A.Tax_Line_No,
A.Precedence_1,
A.Precedence_2,
A.Precedence_3,
A.Precedence_4,
A.Precedence_5,
A.Precedence_6, -- CHANGE ON 02/11/2006 BY SACSETHI FOR BUG 5228046
A.Precedence_7,
A.Precedence_8,
A.Precedence_9,
A.Precedence_10,
A.Tax_Id,
A.Tax_Rate,
A.Qty_Rate,
A.Uom,
A.Tax_Amount,
A.Base_Tax_Amount,
A.Func_Tax_Amount
FROM JAI_OM_OE_SO_TAXES A,
JAI_CMN_TAXES_ALL b
WHERE Line_id = v_source_line_id
AND A.Tax_Id = b.Tax_Id
ORDER BY A.Tax_Line_No;
SELECT COUNT(*)
FROM JAI_OM_WSH_LINE_TAXES
WHERE Delivery_Detail_Id = v_delivery_detail_id
AND Tax_Id = P_Tax_Id;
SELECT SUM(A.Tax_Amount)
FROM JAI_OM_WSH_LINE_TAXES A,
JAI_CMN_TAXES_ALL b
WHERE A.Delivery_Detail_Id = v_delivery_detail_id
AND b.Tax_Id = A.Tax_Id
AND b.Tax_Type <> 'TDS';
SELECT COUNT(*)
FROM JAI_OM_WSH_LINES_ALL
WHERE Delivery_Detail_Id = v_delivery_detail_id;
SELECT NVL(Location_id,0),
trading
FROM JAI_INV_SUBINV_DTLS
WHERE Sub_Inventory_Name = v_subinventory
AND organization_id = v_organization_id;
SELECT ship_to_org_id
FROM Oe_order_lines_all
WHERE line_id = p_line_id;
SELECT nvl(sum(tax_amount),0) ,
nvl(sum(base_tax_amount),0),
nvl(sum(func_tax_amount),0)
FROM
JAI_CMN_MATCH_TAXES
WHERE
ref_line_id = v_delivery_detail_id AND
receipt_id IS NOT NULL AND
tax_id = p_tax_id;
SELECT
nvl(sum(a.func_tax_amount),0) -- cbabu for Bug# 2736191
FROM
JAI_CMN_MATCH_TAXES a,
JAI_CMN_TAXES_ALL b
WHERE
a.tax_id = b.tax_id AND
b.tax_type = p_tax_type AND
A.ref_line_id = v_delivery_detail_id AND
A.receipt_id IS NOT NULL;
SELECT DISTINCT register
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_id = p_delivery_id;
SELECT
item_trading_flag
FROM
JAI_INV_ITM_SETUPS
WHERE
organization_id = v_organization_id AND
inventory_item_id = v_inventory_item_id;
SELECT order_quantity_uom
FROM oe_order_lines_all
WHERE line_id = v_source_line_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
FROM
JAI_CMN_MATCH_RECEIPTS a
WHERE
a.ref_line_id = v_delivery_detail_id;
SELECT
excise_flag,
item_class
FROM
JAI_INV_ITM_SETUPS
WHERE
organization_id = p_organization_id AND
inventory_item_id = p_item_id;
SELECT NVL(A.bonded,'Y') bonded
FROM JAI_INV_SUBINV_DTLS A
WHERE A.sub_inventory_name = p_subinventory
AND A.organization_id = p_organization_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 MAX(A.fin_year)
FROM JAI_CMN_FIN_YEARS A
WHERE organization_id = p_organization_id
AND fin_active_flag = 'Y';
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 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 A.Tax_Rate, NVL(b.Rounding_Factor,0) Rounding_Factor
FROM JAI_OM_OE_SO_TAXES A, JAI_CMN_TAXES_ALL b
WHERE Line_id = v_source_line_id
AND A.Tax_Id = b.Tax_Id
AND b.tax_type = 'Modvat Recovery'
ORDER BY A.Tax_Line_No;
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 */
SELECT LC_FLAG
FROM JAI_OM_OE_SO_LINES
WHERE LC_FLAG = 'Y' AND
HEADER_ID = pr_new.SOURCE_HEADER_ID;
SELECT SUM(QTY_MATCHED)
FROM JAI_OM_LC_MATCHINGS
WHERE ORDER_HEADER_ID = pr_new.SOURCE_HEADER_ID
-- AND ORDER_LINE_ID = pr_new.SOURCE_LINE_ID --commented by csahoo for bug#5680459
AND delivery_detail_id = pr_new.delivery_detail_id -- bug# 3541960
AND RELEASE_FLAG IS NULL;
select split_from_line_id --, split_by
from oe_order_lines_all
where line_id = pr_new.source_line_id;
select order_line_id
from JAI_OM_LC_MATCHINGS
where delivery_detail_id = pr_new.delivery_detail_id;
ln_lc_update_cnt number;
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 (upper(jai_constants.tax_type_cvd_edu_cess), upper(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 (upper(jai_constants.tax_type_sh_exc_edu_cess),
upper(jai_constants.tax_type_sh_cvd_edu_cess)
);
SELECT 1
FROM JAI_OM_WSH_LINE_TAXES jsptl,
JAI_CMN_TAXES_ALL jtc
, jai_regime_tax_types_v tax_types
WHERE jsptl.delivery_detail_id = cp_del_det_id
AND jtc.tax_id = jsptl.tax_id
AND jtc.tax_type = tax_types.tax_type
AND tax_types.regime_code = jai_constants.vat_regime;
SELECT regime_id,
attribute_value
FROM JAI_RGM_ORG_REGNS_V orrg
WHERE orrg.organization_id = cp_organization_id
AND orrg.location_id = cp_location_id
AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
AND attribute_code = jai_constants.attr_code_regn_no
AND regime_code = jai_constants.vat_regime;
SELECT 1
FROM JAI_RGM_INVOICE_GEN_T
WHERE delivery_id = cp_delivery_id;
SELECT 1
FROM JAI_OM_WSH_LINE_TAXES jsptl,
JAI_CMN_TAXES_ALL jtc
WHERE jsptl.delivery_detail_id = cp_del_det_id
AND jtc.tax_id = jsptl.tax_id
AND jtc.tax_type = cp_tax_type ;
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
SELECT COUNT(1)
FROM JAI_REGIME_TAX_TYPES_V JRTTV
, JAI_OM_WSH_LINE_TAXES JSPT
, JAI_CMN_TAXES_ALL JTC
WHERE JTC.TAX_ID = JSPT.TAX_ID
AND JTC.TAX_TYPE = JRTTV.TAX_TYPE
AND REGIME_CODE = CP_REGIME_CODE
AND JSPT.DELIVERY_DETAIL_ID = CP_DELIVERY_DETAIL_ID;
Code added for correct tax insertions in case of trading orders.
4 23/06/2001 Anuradha Parthasarathy
nvl function to v_location_id commented,because this is a necessary setup.
5 10/07/2001 Anuradha Parthasarathy
Taxes should be picked from ja_in_rg23d_shipping_taxes only when a trading item is transacted
from a trading subinventory.
6 03/10/2001 Anuradha Parthasarathy
Tax Calculation as per the Inventory Uom
7 08/05/2002 Sriram SJ Bug # 2330055
Insert of non zero selling price and assessable value in JAI_OM_WSH_LINES_ALL even though
tax lines are not there.
8 03/07/2002 Nagaraj.s - For Enh#2415656.
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.
9 24/08/2002 Sriram SJ bug # 2531013
Made the changes , to take care as to when the backordering functionality should be allowed.
10 01/11/2002 Sriram - Bug # 2165355
LC Functionality. Added the Lc checks.
11 13/12/2002 Sriram - Bug # 2689417 - File Version 615.3
Changed the WHEN clause in the trigger because after the ONT Patchset 'G'
OM interface executes first and then Inventory interface . If the inventory interface
errors out due to some reason other than Localization issue , then the line information
is carried over to AR , but the taxes are not present in Shiping Localization tables ,
causing lot of Data fix requirements.
12 09/01/2003 cbabu for Bug# 2736191 - File Version# 615.4
For trading functionality JAI_OM_WSH_LINES_ALL.excise_amount is getting populated NULL always, if one
of 'Excise', 'Addl. Excise', 'Other Excise' tax components is missing. ED_CUR cursor is modified to fetch
0 if query does not retreive any data.
13. 12/03/2003 Arun Iyer Bug # 2828927 615.5
v_Debug_flag = 'Y' was causing problems owing to reasons such as /usr/tmp folder etc.
Hence making it 'N' .
14. 24/07/2003 Aiyer Bug #3032569, File Version 616.1
Issue:-
The trigger validates that the match receipt functionality is performed in scenario's where an order is associated
to bond registers - 'Trading Domestic Without Excise' and 'Export Without Excise'.
This check is not required. The match receipts only needs to be done for
'Trading Domestic With Excise' and 'Export with Excise' type of Scenario's
Solution:-
Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and
23D_EXPORT_EXCISE.
The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have
been removed as matched receipts is not relevant in this case.
Fix of bug #2988829 along with the current bug
----------------------------------------------
Issue:-
Initial code used to check that if the organization is a Trading organization,item is tradable and excisable
then used to assume that a match receipt has been done and get the sum (tax_amount), sum(base_tax_amount),
sum(func_tax_amount) for a delivery_detail_id and tax_id from the JAI_CMN_MATCH_TAXES table and
populate this into the tax_amount, base_tax_amount and func_tax_amount columns of the table
JAI_OM_WSH_LINE_TAXES.
This approach used to fail, as many a times a record never used to exists in the JAI_CMN_MATCH_TAXES table
for the delivery_detail_id and tax_id even though the organization is declared as a trading organization,
Item is tradable and excisable.
This happens in scenario's where an order assigned to a Bond register_type is either 'Trading domestic without excise' or
'Export Without Excise', the match receipt functionaity would not be performed by client and consequently no
data gets populated in JAI_CMN_MATCH_TAXES.
Solution: -
Added the additional check :-
v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
is all the above cases.
With this the check becomes that if a organization is trdable, item is tradable , excisable and the order
associated to the bond register is Trading Domestic With Excise and Export With Excise only then
it can be assumed that there would be data in table JAI_CMN_MATCH_TAXES (Match receipts has been performed).
In such a case take the tax_amoutn ,base_tax_amount and func_tax_amoutn for the above table.
Else take it from JAI_OM_OE_SO_LINES and JAI_OM_OE_SO_TAXES table.
Dependency Introduced Due to this Bug : -
None
15. 22/08/2003 Bug # 3021588 (Bond Register Enhancement) Version 616.2
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. Letter of undetaking is 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 .
This fix has introduced huge dependency . All future changes in this object should have this bug as a prereq
16.17/09/2003 Bug #3148621 File Version : 616.3
For a trading organization , none of the taxes were proportioned based on the split quantity ,
The tax amount in the sales order is applied as it is to each of the split portions causing these tax amounts to be duplicated many times.
As per the requirement , all taxes other than excisable taxes need to be proportioned based on split quantity , as excise taxes will be picked from receipt against which this shipment is done.
This issue has been resolved by adding a check that tax amount should be proportionally calculated based on quantity shipped for all non-excise taxes.
17.24/09/2003 Aiyer Bug #3139718 File Version : 616.4
Added 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 considerd for all processing rather than the creation
date of the Sales order.
Added the procedure call jai_cmn_utils_pkg.currency_conversion to calculated the currency conversion rate.
Also changed the population logic of v_func_tax_amt variable, such that
during shipping the functional tax amount gets recalculated from the tax_amount column in JAI_OM_OE_SO_TAXES
and hence logic is ->
v_func_tax_amt = v_tax_amt * nvl((v_curr_conv_rate ,1)
Fix Of Bug#3158282:-
Issue:-
In case of non INR type of transactions with Excise type of tax the rounding precision should be maintained at 0
and in all other cases the rounding factor should be picked up from JAI_CMN_TAXES_ALL.
Solution:-
Modified the rounding factor to reflect the above scenario. variable v_func_tax_amount gets
rounded of to zero in case of non INR type of transactions ( v_curr_conv_rate <> 1) with Excise type of tax
and for all other cases rounding precession is picked up from JAI_CMN_TAXES_ALL table.
Dependency Introduced Due to this Bug : -
None
18. 21/01/2004 ssumaith bug # 3390174 618.1
Issue :- In a trading organzation , when match receipts is done, taxes which are
dependent on adhoc excise taxes was not getting recalculated based on the
value of the excise tax retreived from matching.
This issue has been resolved by commenting out the condition which is
documented by this bug number.
19.31/03/2004 ssumaith - bug# 3541960 file version 619.1
Issue :- when an lc enabled order is split and shipped , interface trip stop was going into error.
The reason for this error is because for a delivery , the sum of matched quantity in the
JAI_OM_LC_MATCHINGS table is compared to the quantity shipped for the delivery detail id being processed.
Solution :- This issue is solved by comparing the delivery_detail_id also when getting the matched quantity.
By including the delivery detail in the where clause , ensuring that in case of split orders
also the shipment can go through without any errors.
code change has been done in the cursor - c_matched_qty_cur
20.04/05/2004 ssumaith - bug# 3609172 file version 619.2
issue :- In a trading scenario, when matching happens post split of a delivery either intentionally
or due to lot controls , the non-excise taxes are getting incorrectly calculated.
Analysis is that post split when matching happens, there are sets of tax records for each split
in the delivery.When this trigger processess the delivery detail , it again apportions based
on the quantity causing the taxes to be calculated as less than that of the actual value
Solution :- Issue has been resolved by doing the following :
The apportion based on quantity shipped has been removed and instead the tax amount from
the JAI_CMN_MATCH_TAXES table is used for population into JAI_OM_WSH_LINES_ALL
and jain_so_picking_tax_lines table
Dependency : None
21.23/08/2004 Aiyer - bug# 3844145 file version 115.1
Issue:-
The inventory trip stop concurrent program errors out for a Internal sales order with qty being matched to two different subinventories.
Reason :-
The existing code was considering matched quantites as per the subinventories. This should not be considered.
Matched quantities should be considered irrespective of the subinventories.
Solution:-
Modified the cursor matched_receipt_cur1 in the current trigger.Removed the group by subinventory clause from the query.
Dependency Introduced Due to this Bug : -
None
22. 29/Nov/2004 Aiyer for bug#4035566. Version#115.2
Issue:-
The trigger should not get fired when the non-INR based set of books is attached to the current operating unit
where transaction is being done.
Fix:-
Function jai_cmn_utils_pkg.check_jai_exists is being called which returns the TRUE if the currency is INR and FALSE if the currency is
NON-INR
Dependency Due to this Bug:-
The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0 introduced through the bug 4033992
23. 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 precedence setup at the organization additional information
needs to be considered for picking up the correct register order.
This code object calls the functions ja_in_exc_balance_amt_f and ja_in_exc_exempt_balance_amt_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 trigger 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.
23. 2005/03/15 brathod - Bug#4215808- File version 115.5
Trigger modified for VAT Implementation.
New VAT fields in JAI_OM_WSH_LINES_ALL are populated by fetching them
from JAI_OM_OE_SO_LINES table. Also populated jai_vat_preocessing_t temporery
table for VAT invoice number generation
24 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)
25 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE. Version 116.1
26. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
27 30/Jan/2007 bgowrava, forward porting Bug#5631784 (4742259) - File Version 116.2
Modified the trigger for TCS Enahancement.
Changes are made to support following functionalities required for TCS
1. Whenver a Sales Order has TCS type of tax, depending upon the setup done for threshold
Surcharge type of taxes needs to be defaulted at the time of shipping
The following are the logical steps
a. Check if tcs type of taxes exists
b. If yes, check the current threshold slab
c. If threshold up (not null threshold_slab_id) then derrive the tax_category_id attached
in the threshold setup for the slab
d. Based on the tax category (p_threshold_tax_cat_id) defaul the additional taxes by calling
JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES
e. Added the call to jai_ar_tcs_rep_pkg.wsh_interim_accounting to do the interim accounting for TCS type of taxes.
28. 16/Apr/2007 Bgowrava for forward porting bug#5989740 11i Bug#5907436 - File version
ENH : HANDLING SECONDARY AND HIGHER EDUCATION CESS
additional cess of 1% on all taxes to be levied to fund secondary education and higher
education .
Changes - -
Object Type Object Name Changes
-----------------------------------------------------------------------
Cursor c_sh_cess_amount Cursor is added to get cess amount for seconday and higher cess
Code is added to check balances for secondary and higher educat
29. 17/May/2007 CSahoo for bug 5680459, File Version 120.5
Forward porting of 11i BUG#5645003
modified the cursor c_matched_qty_cur to filter the data only by delivery_detail_id
and order header_id and removed the filter by order line_id
Added the cursors c_order_line and c_lc_mtch_dlry_line, to check if the order line attached
the delivery detail in LC matching table is same or not. If not, then the related delivery_detail
line is updated with the pr_new.source_line_id
Added the cursors c_chk_vat_reversal.
modified the code in ARU_T3
30 09/10/2007 ssumaith - bug#6487667 - File version - 120.6
When comparing the register balance, the amount in INR is not compared. Instead the amount in the Fc is compared.
This has been corrected by multiplying the v_tot_excise_amt with the currency conversion factor.
31. 16/10/2007 CSahoo for bug#6498072, File Version 120.8
Modified the p_assessable_value parameter during call to JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES
p_assessable_value => nvl(v_assessable_value * v_shipped_quantity, 0)
Moved cursor uom_code to the start of the loop to fetch v_order_quantity_uom
32. 16/10/2008 CSahoo for bug#5189432, File Version 120.9.12010000.2
Assigned the sysdate to the variable v_creation_date instead of pr_new.creation_date
33. 13/11/2008 JMEENA for bug#6280735( FP6164922)
Issue:WRONG EXCISE DUTY PASSED TO DELY WHILE MATCHING RG23D REGISTER.
Fix: 1. The issue was beacuse a conversion factor was getting multiplied to the tax amount while matching.
Hence the excise duty reflected was wrong. so while matching the receipts the conversion factor should be 1
and for other cases it should be as it is. Thus modified the code for the same.
assigned the v_conversion_rate to 1 in case of matching and for
manufacturing organization Inv_Convert.inv_um_conversion should be executed. so included this logic in the else part.
2. Modified the Get_Tax_Lines_Details_Cur cursor. changed the rounding factor to 2 when the
rounding factor is null.
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
ja_in_wsh_dlry_dtls_au_trg.sql
----------------------------------------------------------------------------------------------------------------------------------------------------
616.2 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
2801751 +
2769440
115.2 4035566 IN60105D2 +
4033992 ja_in_util_pkg_s.sql 115.0 Aiyer 29-Nov-2004 Call to this function.
ja_in_util_pkg_b.sql 115.0
115.3 4171272 IN60106 +
4147608 ja_in_exc_exempt_balance_amt_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
ja_in_exc_balance_amt_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
115.5 4215808 IN60106 All VAT Objects
+4245089
------------------------------------------------------------------------------------------------------------------------------------------------*/
--File.Sql.35 Cbabu
v_creation_date := sysdate; -- replaced pr_new.Creation_Date by sysdate for bug#5189432
v_last_update_date :=pr_new.Last_Update_Date;
v_last_updated_by :=pr_new.Last_Updated_By;
v_last_update_login :=pr_new.Last_Update_Login;
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';
ln_lc_update_cnt := -1;
update JAI_OM_LC_MATCHINGS
set order_line_id = pr_new.source_line_id
where delivery_detail_id = pr_new.Delivery_Detail_Id
-- and order_line_id = r_order_line.split_from_line_id
and release_flag is null;
ln_lc_update_cnt := sql%rowcount;
UPDATE JAI_OM_LC_MATCHINGS
SET RELEASE_FLAG = 'Y'
WHERE DELIVERY_DETAIL_ID = pr_new.Delivery_Detail_id;
Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINE_TAXES for Bond register with
'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
*/
IF NVL(v_trading_flag,'N') = 'Y' AND
NVL(v_item_trading_flag,'N') = 'Y' AND
NVL(v_exe_flag,'N') = 'Y' AND
v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
THEN
OPEN rg23d_amount_cur(rec.tax_id);
INSERT INTO JAI_OM_WSH_LINE_TAXES(Delivery_Detail_Id,
Tax_Line_No,
Precedence_1,
Precedence_2,
Precedence_3,
Precedence_4,
Precedence_5,
Precedence_6, -- CHANGE ON 02/11/2006 BY SACSETHI FOR BUG 5228046
Precedence_7,
Precedence_8,
Precedence_9,
Precedence_10,
Tax_Id,
Tax_Rate,
Qty_Rate,
Uom,
Tax_Amount,
Base_Tax_Amount,
Func_Tax_Amount,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login)
VALUES (
v_delivery_detail_id,
rec.Tax_Line_No,
rec.Precedence_1,
rec.Precedence_2,
rec.Precedence_3,
rec.Precedence_4,
rec.Precedence_5,
rec.Precedence_6, -- CHANGE ON 02/11/2006 BY SACSETHI FOR BUG 5228046
rec.Precedence_7,
rec.Precedence_8,
rec.Precedence_9,
rec.Precedence_10,
rec.Tax_id,
rec.Tax_rate,
rec.Qty_Rate,
rec.Uom,
v_tax_amount,
v_base_tax_amount,
v_func_tax_amount,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login
);
UPDATE JAI_OM_WSH_LINE_TAXES
SET Tax_Amount = v_tax_amount,
Last_Update_Date = v_last_update_date,
Last_Updated_By = v_last_updated_by,
Last_Update_Login = v_last_update_login
WHERE Delivery_Detail_Id = v_delivery_detail_id
AND Tax_Id = rec.Tax_Id;
select max(tax_line_no)
into ln_last_line_no
from JAI_OM_WSH_LINE_TAXES
where delivery_detail_id = v_delivery_detail_id;
select max(tax_line_no)
into ln_base_line_no
from JAI_OM_WSH_LINE_TAXES jsptl
, JAI_CMN_TAXES_ALL jtc
where jsptl.delivery_detail_id = v_delivery_detail_id
and jsptl.tax_id = jtc.tax_id
and jtc.tax_type = jai_constants.tax_type_tcs;
|| The api jai_rgm_thhold_proc_pkg.default_thhold_taxes inserts lines as per the same specified in the TCS tax category
|| into the JAI_OM_WSH_LINE_TAXES table
*/
/* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Calling JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES');*/ --commented by bgowrava for bug#5631784
insert into debug_data (str) values ('AFTER - the value OF v_conversion_rate IS ' || to_char(v_conversion_rate));
insert into debug_data (str) values ('AFTER - the value OF v_Requested_Quantity_Uom IS ' || v_Requested_Quantity_Uom);
insert into debug_data (str) values ('AFTER - the value OF v_order_quantity_uom IS ' || v_order_quantity_uom);
Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINES_ALL for Bond register with
'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
*/
IF nvl(v_trading_flag,'N') = 'Y' AND
nvl(v_item_trading_flag,'N') = 'Y' AND
nvl(v_excise_flag,'N') = 'Y' AND
v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
THEN
OPEN ed_cur('Excise');
INSERT INTO JAI_OM_WSH_LINES_ALL(
Delivery_Detail_Id,
Order_Header_Id,
Order_Line_Id,
split_from_delivery_detail_id,
Selling_Price,
Quantity,
Assessable_value,
vat_assessable_value,
Tax_Category_Id,
Tax_Amount,
Inventory_Item_Id,
Organization_Id,
Location_Id,
Unit_Code,
Excise_Amount,
Basic_Excise_Duty_Amount,
Add_Excise_Duty_Amount,
Oth_Excise_Duty_Amount,
Excise_Exempt_Type,
Excise_Exempt_Refno,
Excise_Exempt_Date,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
ORG_ID,
CUSTOMER_ID,
SHIP_TO_ORG_ID,
ORDER_TYPE_ID,
SUBINVENTORY,
DELIVERY_ID,
-- Added by Brathod for Bug#4215808
VAT_EXEMPTION_FLAG,
VAT_EXEMPTION_TYPE,
VAT_EXEMPTION_DATE,
VAT_EXEMPTION_REFNO
-- End of Bug#4215808
)
VALUES ( v_delivery_detail_id,
v_source_header_id,
v_source_line_id,
pr_new.split_from_delivery_detail_id,
v_selling_price * v_conversion_rate,
v_shipped_quantity,
v_assessable_value * v_conversion_rate,
ln_vat_assessable_value * v_conversion_rate,
v_tax_category_id,
v_tot_tax_amount,
v_Inventory_Item_Id,
v_Organization_Id,
v_location_id,-- 2001/06/23 Anuradha Parthasarathy
v_Requested_Quantity_Uom,
v_excise_amount,
v_basic_excise_duty_amount,
v_add_excise_duty_amount,
v_oth_excise_duty_amount,
v_excise_exempt_type,
v_excise_exempt_refno,
v_excise_exempt_date,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
v_org_Id,
v_customer_id,
v_ship_to_org_id,
v_source_header_type_id,
v_subinventory,
v_DELIVERY_ID,
-- Added by Brathod for Bug#4215808
lv_vat_exemption_flag,
lv_vat_exemption_type,
ld_vat_exemption_date,
lv_vat_exemption_refno
-- End of Bug#4215808
);
INSERT INTO JAI_OM_OE_GEN_TAXINV_T(
date_released,
date_confirmed,
delivery_detail_id,
order_header_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
delivery_id
)
VALUES (
SYSDATE,
v_date_confirmed,
v_delivery_detail_id,
v_source_header_id,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
v_delivery_id
);
INSERT INTO JAI_RGM_INVOICE_GEN_T ( regime_id ,
delivery_id ,
delivery_date ,
customer_trx_id ,
organization_id ,
location_id ,
registration_num ,
vat_invoice_no ,
vat_inv_gen_status ,
vat_inv_gen_err_message ,
vat_acct_status ,
vat_acct_err_message ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
party_id ,
party_site_id ,
party_type ,
creation_date ,
created_by ,
last_update_date ,
last_update_login ,
last_updated_by
)
VALUES (ln_regime_id ,
v_delivery_id ,
v_creation_date ,
null , -- customer_trx_id
v_organization_id ,
v_location_id ,
lv_regns_num ,
lv_vat_invoice_no , -- vat_invoice_no --Replaced NULL with lv_vat_invoice_no for bug#5680459
nvl(lv_vat_inv_gen_status, 'P'), -- vat_inv_gen_status --Added nvl() for bug#5680459
null , -- vat_inv_gen_err_message
'P' , -- vat_acct_status
null , -- vat_acct_err_message
null , -- request_id
null , -- program_application_id
null , -- program_id
null , -- program_update_date
pr_new.customer_id ,
v_ship_to_org_id ,
jai_constants.party_type_customer,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_update_login ,
v_last_updated_by
);
UPDATE JAI_OM_WSH_LINES_ALL
SET quantity = v_shipped_quantity,
tax_amount = v_tot_tax_amount,
order_line_id = v_source_line_id,
excise_amount = v_excise_amount,
basic_excise_duty_amount = v_basic_excise_duty_amount,
add_excise_duty_amount = v_add_excise_duty_amount,
oth_excise_duty_amount = v_oth_excise_duty_amount,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login,
-- Added by Brathod for Bug#4215808
VAT_EXEMPTION_FLAG = lv_vat_exemption_flag,
VAT_EXEMPTION_TYPE = lv_vat_exemption_type,
VAT_EXEMPTION_DATE = ld_vat_exemption_date,
VAT_EXEMPTION_REFNO = lv_vat_exemption_refno
-- End of Bug#4215808
WHERE Delivery_id = v_delivery_id
AND Delivery_Detail_id = v_delivery_detail_id;