[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE JAI_OM_OE_RMA_LINES
WHERE rma_header_id = v_header_id
AND rma_line_id = v_line_id;
DELETE FROM JAI_OM_OE_RMA_TAXES
WHERE rma_line_id = v_line_id;
DELETE JAI_OM_OE_SO_LINES
WHERE header_id = v_header_id
AND line_id = v_line_id;
DELETE JAI_OM_OE_SO_TAXES
WHERE header_id = v_header_id
AND line_id = v_line_id;
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 rowid, nvl(org_id,0), sold_to_org_id,
source_document_id, order_number, price_list_id,
order_category_code, orig_sys_document_ref, transactional_curr_code,
conversion_type_code, conversion_rate, conversion_rate_date,
nvl(ordered_date, creation_date)
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT order_category_code
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT NVL(cust_acct_site_id, 0) address_id
FROM hz_cust_site_uses_all a /* Removed ra_site_uses_all for Bug# 4434287 */
WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs */
SELECT count(1)
FROM JAI_PO_REQ_LINES
WHERE requisition_header_id IN ( SELECT requisition_header_id
FROM po_requisition_headers_all a, oe_order_headers_all b
WHERE A.segment1 = b.orig_sys_document_ref
AND A.segment1 = p_requisition_number );
SELECT tax_line_no, tax_id, tax_rate, qty_rate, uom,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- precedence 6 to 10 added for bug#6485212
tax_amount, base_tax_amount, func_tax_amount,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_OM_OE_SO_TAXES
WHERE header_id = p_header_id
AND line_id = p_line_id;
select nvl(rounding_factor,0),
nvl(adhoc_flag,'N') --Added by Nagaraj.s for Bug3207633
from JAI_CMN_TAXES_ALL
where tax_id = p_tax_id;
SELECT SUM(A.tax_amount)
FROM JAI_OM_OE_SO_TAXES A,
JAI_CMN_TAXES_ALL b
WHERE A.Header_ID = p_header_id
AND A.line_id = p_line_id
AND b.tax_id = A.tax_id
AND b.tax_type <> jai_constants.tax_type_tds /* 'TDS'; Ramananda for removal of SQL LITERALs */
SELECT SUM(A.tax_amount)
FROM JAI_OM_OE_RMA_TAXES a, JAI_CMN_TAXES_ALL b
WHERE a.rma_line_id = p_line_id
AND b.tax_id = A.tax_id
AND b.tax_type <> jai_constants.tax_type_tds /* 'TDS'; Ramananda for removal of SQL LITERALs */
SELECT COUNT(1)
FROM JAI_OM_OE_SO_LINES
WHERE line_id = p_line_id;
SELECT COUNT(1)
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = v_line_id;
SELECT COUNT(1)
FROM JAI_OM_OE_SO_TAXES
WHERE header_id = p_header_id
AND line_id = p_line_id;
SELECT COUNT(1)
FROM JAI_OM_OE_RMA_TAXES
WHERE rma_line_id = pr_new.line_id;
SELECT b.operand list_price, c.product_uom_code list_price_uom_code
FROM JAI_CMN_CUS_ADDRESSES a, qp_list_lines b, qp_pricing_attributes c
WHERE A.customer_id = p_customer_id
AND A.address_id = p_address_id
AND A.price_list_id = b.LIST_header_ID
AND c.list_line_id = b.list_line_id
AND c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id) --2001/02/14 Manohar Mishra
AND c.product_uom_code = p_uom_code -- Bug# 3210713 Sriram
AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(p_ordered_date);
SELECT NVL(orig_sys_document_ref,'NON_IMPORT')
FROM oe_order_headers_all
WHERE header_id = v_header_id ;
SELECT order_source_id
FROM oe_order_headers_all
WHERE header_id = v_header_id;
SELECT order_category_code
FROM oe_order_headers_all
WHERE header_id = p_source_document_id;
SELECT name
FROM oe_order_sources
WHERE order_source_id = p_source_document_type_id;
SELECT inventory_item_id, unit_code, quantity,
tax_category_id, selling_price, line_amount, assessable_value,
tax_amount, line_tot_amount, shipment_line_number,
excise_exempt_type, excise_exempt_refno, excise_exempt_date, -- added by sriram for Bug # 2672114
vat_exemption_flag,vat_exemption_type,vat_exemption_date ,vat_exemption_refno,vat_assessable_value /* added by ssumaith for vat */,
/*gst_assessable_value,*/--added by peng.zheng for bug 10043656
vat_reversal_price,service_type_code
FROM JAI_OM_OE_SO_LINES
WHERE header_id = p_header_Id
AND line_id = p_Line_Id ;
SELECT count(1)
FROM JAI_OM_OE_SO_LINES
WHERE header_id = p_header_id;
SELECT
*
FROM
JAI_OM_OE_RMA_LINES
WHERE
rma_header_id = p_header_id AND
rma_line_id = p_Line_Id ;
SELECT
'X'
FROM
JAI_OM_OE_SO_LINES
WHERE
line_id = p_line_id AND
header_id = p_header_id ;
SELECT
lc_flag
FROM
JAI_OM_OE_SO_LINES
WHERE
line_id = pr_new.split_from_line_id;
SELECT Location_id
FROM JAI_INV_SUBINV_DTLS
WHERE Sub_Inventory_Name = lv_subinventory
AND organization_id = v_warehouse_id;
select line_amount , tax_amount , selling_price , assessable_value , quantity, -- quantity added to the select clause Bug # 2968360
vat_assessable_value -- added for bug#8924003
/*,gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
from JAI_OM_OE_SO_LINES
where header_id = pr_new.header_id
and shipment_schedule_line_id = pr_new.ato_line_id;
which will not happen if 6th tax is being inserted first.
Resolution: call to ja_in_calc_taxes_ato is moved out of the tax insertion loop
--- Dependancy Introduced: Nothing ------
3. 15-MAY-2007 SSAWANT , File version 120.8
Forward porting the change in 11.5 bug 4439200 to R12 bug no 4660756.
Vat Assessable Value was calculated incorrectly in case of Split line functionality.
Added code to calculate Vat Assessable Value based on the Quantity.
4. 04/06/2007 bduvarag for the bug#6071813,5989740,5256498
Forward ported the 11i bugs 6053462,5907436,5256498
5. 12/06/2007 Bgowrava, for Bug# 6126581 , File Version 120.11
Uncommented the line wdd.delivery_detail_id = TO_NUMBER(pr_new.attribute2) in the
cursor c_get_detail_id
6. 13/06/2007 Bgowrava, for Bug# 6126581 , File Version 120.12
created a cursor cur_get_ddetail_id to get the delivery detail id of the RMA and
used the delivery detail id in the c_get_detail_id instead of the Attribute2 parameter.
7. 14/06/2007 sacsethi for bug 6072461 file version 120.13
This bug is used to fp 11i bug 5183031 for vat reveresal
Problem - Vat Reversal Enhancement not forward ported
Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
8. 08/10/2007 CSahoo for bug#6485212 File Version 120.14
Added the precedences 6 to 10 in the code.
9. 01-JAN-2008 Added by Jia Li
for inclusive tax
10. 25-Sep-2008 CSahoo for bug#7316234 File Version 120.15.12010000.3
Issue:EXCISE RETURN DAYS AND ST/ CST REURN DAYS FUNCTIONALITY NOT WORKING AS DESIRED
Fix: removed the concept of 180 days in return days functionality. Modified the IF condition
for the same.
11. 30-oct-2008 bug#7523501 120.15.12010000.4
forwardported the changes done in 115 bug#7523501
12. 20-Nov-2008 CSahoo for bug#7568194, File Version 120.15.12010000.5
ISSUE: AFTER SAVING THE RMA IT IS REFERENCED TO AN AR INVOICE BUT AN ERROR OCCURS
Fix: Modified the code in the BRIU_T1 procedure. Added jai_constants.UPDATING in the
IF condition.
13. 20-May-2009 CSahoo for bug#8485149, File Version 120.15.12010000.7
ISSUE: ATO MODEL EXCISE ITEM ATTRIBUTES ARE NOT GETTING CREATED FOR STAR(*) ITEM
FIX: Modified the code in the procedure BRIU_T1. Added the curs37. 07-apr-2009
and cur_get_model_line_dtls. Added the code to check for the config item and
create a entry for the item in the table jai_inv_itm_setups
vkaranam for bug#8413915
Issue:
UOM IN RMA ORDER IS COMING INCORRECT IF THE UOM IS CHANGED IN THE BASE FORM
Fix:
In the Update to table ja_in_rma_entry_lines, added the column UOM in the Set Clause
or cur_chk_item_dtls (fwdported the changes done in 115 bug 8403321)
14. 13-aug-09 vkaranam for bug 8356692
Issue:
RMA lines are not flowing on the localized sales order
Fix:
Forwardported the changes done in 115 bug 7568180
added the conversion factor and ROUND function for rma qty
validation
IF ROUND(v_shipped_quantity,2) < ROUND(pr_new.ordered_quantity*
(1/v_conversion_rate),2)
15. 23-Sep-2009 CSahoo for bug#8924003, File Version 120.15.12010000.10
Issue: TAXES AND UNIT RATE COMING WRONGLY FOR THE ATO/PTO ITEM
Fix: forward ported the changes done for bug#6147494. Added the code to
calculate the vat assessable value for config item.
16. 11-Dec-2209 CSahoo for bug#9067808, File Version 120.15.12010000.11
Issue: IN ATO UNIT SELLING PRICE IS NOT UPDATING IN THE SALES ORDER LOCALIZED FORM
Fix: Modified the code in procedure ARU_T1. Added the procedure calc_price_tax_for_config_item
and get_config_item.
17. 28-JAN-2010 CSahoo for bug#9191274, File Version 120.15.12010000.12
Issue: VAT ITEM ATTRIBUTES NOT ASSIGNED AUTOMATICALLY FOR STAR ITEM, AFTER CONFIGURATI
Fix: modified the procedure BRIU_T1. Added a call to jai_inv_items_pkg.copy_items for
populating VAT attributes of the star item.
18. 28-APR-2010 Allen Yang modified for bug 9666476 File Version 120.15.12010000.14
Issue: TST1213.NON SHIPPABLE: SUPPORT FOR RMA AND OTHER CHANGES FOR NON-SHIPPABLE ITEMS
Fix: Added process logic for non-shippables lines when copying RMA lines from normal order lines.
19. 07-MAY-2010 Allen Yang modified for bug 9691880 File Version 120.15.12010000.15
Issue: TST1213.NON SHIPPABLE: CONSOLIDATED PATCH FOR CORE PART AND RMA OF NON-SHIPPABLE
Fix: 1). modified logic of getting shippable flag variable lv_shippable_flag
2). added logic to copy Indian taxes from orginal Sales Order line to RMA Order line when
copy is happening from 'Mixed' order type to 'Return' line type.
3). added logic to validate VAT Return days for Order copying from 'Standard' / 'Mixed'
order type to 'Return' line type.
20. 13-MAY-2010 vkaranam for bug#9436523
issue:
ORA-20001: APP--20110: Taxes are not matching in JAI_OM_OE_SO_LINES and
JA_IN_SO_TAX_LINE FOR LINE_ID 4656 while shipping the splitted line.
Reason:
issue is happening with order having inclusive taxes.
inclusive tax amount is updated in jai_om_oe_so_lines.tax_amount.
ideally jai_om_oe_so_lines.tax_amount shall be 0 for inclusive taxes.
Fix:
updated jai_om_oe_so_lines.tax_amount as the tax amount for exclusive taxes.
21. 27-May-2010 Allen Yang for bug #9722577
Issue: variable v_converted_rate is used without initialization for func_tax_amount
calculation when copying from Order to Order.
Fix: added logic to initialize v_converted_rate
22 09-Jun-2010 Bug 9786306
Issue - Interface trip stop ends in warning for partial (split) shipments.
Cause - In the changes done for bug 9436523, utl_file.put_line was called without
checking v_debug.
Fix - Added IF v_debug = 'Y' condition before calling utl_file.put_line.
15. 03-sep-2010 vkaranam for bug#9963286
Issue: WRONG UOM IN RMA ORDER
Fix: UOM in RMA order shall be same as the base Order.
populated UOM from base so lines instead of populating uom from IL shipment tables.
23. 10-Sep-2010 Jia for GST Bug#10043656.
24. 13-Sep-2012 Bug 11774053
Issue: IMPROPER ERROR WHEN SALES ORDER IS SAVED WITH OUT QUANTITY
Fix: Column 'quantity' is not nullable in both JAI_OM_OE_SO_LINES and JAI_OM_OE_RMA_LINES.
So add a quantity check before inserting lines into JAI table.
17. 03-Oct-2012 mmurtuza for bug 14675557
Issue: ASSESSABLE AMOUNT IS SHOWING WRONG VALUE FOR A COPIED ORDER
Fix: Fected the assessable value again in copy order code. Also passed the same during tax computation.
----------------------------------------------------------------------------------------------------------------*/
pv_return_code := jai_constants.successful ;
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;
update JAI_OM_OE_SO_LINES
set line_number = pr_new.line_number , shipment_line_number = pr_new.shipment_number
where line_id = pr_new.line_id;
v_hook := jai_cmn_hook_pkg.oe_lines_insert(
pr_new.line_id, pr_new.org_id, pr_new.line_type_id, pr_new.ship_from_org_id,
pr_new.ship_to_org_id, pr_new.invoice_to_org_id, pr_new.sold_to_org_id, pr_new.sold_from_org_id,
pr_new.inventory_item_id, pr_new.tax_code, pr_new.price_list_id, pr_new.source_document_type_id,
pr_new.source_document_line_id, pr_new.reference_line_id, pr_new.reference_header_id, pr_new.salesrep_id,
pr_new.order_source_id, pr_new.orig_sys_document_ref, pr_new.orig_sys_line_ref
);
SELECT DECODE(SUBSTR(value,1,INSTR(value,',') -1),
NULL,
value,
SUBSTR (value,1,INSTR(value,',') -1)
)
INTO v_utl_location
FROM v$parameter
WHERE LOWER(name) = 'utl_file_dir';
utl_file.put_line(v_myfilehandle,'** START OF TRIGGER JA_IN_OE_ORDER_LINES_AIU_TRG AFTER INSERT OR UPDATE ON OE_ORDER_LINES_ALL FOR EACH ROW ~ ' || TO_CHAR(SYSDATE,'dd/mm/rrrr hh24:mi:ss'));
DELETE JAI_OM_OE_SO_LINES
WHERE line_id = pr_new.line_id;
DELETE JAI_OM_OE_SO_TAXES
WHERE line_id = pr_new.line_id;
utl_file.put_line(v_myfilehandle,'** END OF TRIGGER jai_oe_ola_ariu_t5 AFTER INSERT OR UPDATE ON OE_ORDER_LINES_ALL FOR EACH ROW ~ ' || TO_CHAR(SYSDATE,'dd/mm/rrrr hh24:mi:ss'));
SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
in the following insert - changes are done to the insert by ssumaith - bug#3959984
- inventory_item_id -> v_inventory_item_id
- shipment_line_number -> pr_new.shipment_number
- unit_selling_price -> pr_new.unit_selling_price
- quantity -> pr_new.ordered_quantity
- line_amount -> nvl(pr_new.unit_selling_price * pr_new.ordered_quantity ,0)
*/
-- Start of changed by bgowrava for forward porting bug#4895477 to recalculate VAT taxes in case of copying order.
/*Start by mmurtuza for bug 14675557*/
l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
(p_org_id => v_warehouse_id);
INSERT INTO JAI_OM_OE_SO_LINES (
line_number, line_id, header_id, inventory_item_id,
unit_code, quantity, tax_category_id, ato_flag,
selling_price, line_amount, assessable_value, tax_amount,
line_tot_amount, shipment_line_number,
excise_exempt_type , excise_exempt_refno ,excise_exempt_date, /* added by ssumaith for vat */
vat_exemption_flag,vat_exemption_type,vat_exemption_date ,vat_exemption_refno,vat_assessable_value, /* added by ssumaith for vat */
/*gst_assessable_value,*/--added by peng.zheng for bug 10043656
vat_reversal_price, --Date 14/06/2007 by sacsethi for bug 6072461
creation_date, created_by,
last_update_date, last_updated_by, last_update_login,service_type_code
) VALUES (
v_line_number, v_line_id, v_header_id, v_inventory_item_id,
copy_rec.unit_code, pr_new.ordered_quantity, copy_rec.tax_category_id, 'Y',
pr_new.unit_selling_price, nvl(pr_new.unit_selling_price * pr_new.ordered_quantity ,0),
v_assessable_value, /*copy_rec.assessable_value*/ /*changed to v_assessable_value by mmurtuza for bug 14675557*/
copy_rec.tax_amount,
copy_rec.line_tot_amount, pr_new.shipment_number,
copy_rec.excise_exempt_type , copy_rec.excise_exempt_refno , copy_rec.excise_exempt_date, /* added by ssumaith for vat */
copy_rec.vat_exemption_flag,copy_rec.vat_exemption_type,copy_rec.vat_exemption_date ,copy_rec.vat_exemption_refno,
ln_vat_assessable_value, /*copy_rec.vat_assessable_value*/ /*changed to ln_vat_assessable_value by mmurtuza for bug 14675557*/ /* added by ssumaith for vat */
/*copy_rec.gst_assessable_value,*/--added by peng.zheng for bug 10043656
nvl(copy_rec.vat_reversal_price,0) * v_line_quantity, --Date 14/06/2007 by sacsethi for bug 6072461
v_creation_date, v_created_by,
v_last_update_date, v_last_updated_by, v_last_update_login,copy_rec.service_type_code
);
IF such a record is not found then then insert a record into the JAI_OM_OE_SO_LINES table.
*/
OPEN cur_get_rma_entry_lines (V_Source_Document_Id, V_Source_Document_Line_Id);
INSERT INTO JAI_OM_OE_SO_LINES (
line_number ,
line_id ,
header_id ,
inventory_item_id ,
unit_code ,
quantity ,
tax_category_id ,
ato_flag ,
selling_price ,
line_amount ,
assessable_value ,
tax_amount ,
line_tot_amount ,
shipment_line_number ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login,service_type_code
)
VALUES (
v_line_number ,
v_line_id ,
v_header_id ,
rec_cur_get_rma_entry_lines.inventory_item_id ,
rec_cur_get_rma_entry_lines.uom ,
rec_cur_get_rma_entry_lines.quantity ,
rec_cur_get_rma_entry_lines.tax_category_id ,
'Y' ,
rec_cur_get_rma_entry_lines.selling_price ,
v_line_amount ,
rec_cur_get_rma_entry_lines.assessable_value ,
rec_cur_get_rma_entry_lines.tax_amount ,
(v_line_amount + rec_cur_get_rma_entry_lines.tax_amount) ,
pr_new.shipment_number ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login,rec_cur_get_rma_entry_lines.service_type_code
);
SELECT
pl.inventory_item_id ,
pl.unit_code ,
pl.quantity ,
pl.tax_category_id ,
pl.selling_price ,
pl.tax_amount ,
pl.delivery_detail_id
FROM
JAI_OM_WSH_LINES_ALL pl
WHERE
pl.order_header_id = p_source_document_id AND
pl.order_line_id = p_source_document_line_id ;
SELECT
pl.inventory_item_id inventory_item_id ,
pl.unit_code unit_code ,
sum(pl.quantity ) quantity ,
pl.tax_category_id tax_category_id ,
pl.selling_price selling_price ,
sum(pl.tax_amount) tax_amount ,
min(pl.delivery_detail_id) delivery_detail_id
FROM
JAI_OM_WSH_LINES_ALL pl
WHERE
pl.order_header_id = p_source_document_id AND
pl.order_line_id = p_source_document_line_id
GROUP BY
pl.inventory_item_id ,
pl.unit_code ,
pl.selling_price ,
pl.tax_category_id ;
select quantity,service_type_code
from JAI_OM_OE_SO_LINES
where line_id=v_reference_line_id;
SELECT
'X'
FROM
JAI_OM_OE_RMA_LINES
WHERE
rma_line_id = p_line_id AND
rma_header_id = p_header_id ;
SELECT
ptl.tax_line_no tax_line_no ,
ptl.tax_id tax_id ,
ptl.tax_rate tax_rate ,
ptl.qty_rate qty_rate ,
ptl.uom uom ,
ptl.precedence_1 precedence_1 ,
ptl.precedence_2 precedence_2 ,
ptl.precedence_3 precedence_3 ,
ptl.precedence_4 precedence_4 ,
ptl.precedence_5 precedence_5 ,
ptl.precedence_6 precedence_6 ,
ptl.precedence_7 precedence_7 ,
ptl.precedence_8 precedence_8 ,
ptl.precedence_9 precedence_9 ,
ptl.precedence_10 precedence_10 ,
jtc.tax_type tax_type ,
nvl(jtc.rounding_factor,0) rounding_factor , /*bduvarag for the bug#6071813*/
SUM (ptl.tax_amount) tax_amount ,
SUM (ptl.base_tax_amount) base_tax_amount ,
SUM (ptl.func_tax_amount) func_tax_amount ,
MIN (ptl.delivery_detail_id) delivery_detail_id
FROM
JAI_OM_WSH_LINES_ALL pl ,
JAI_OM_WSH_LINE_TAXES ptl ,
JAI_CMN_TAXES_ALL jtc
WHERE
ptl.delivery_detail_id = pl.delivery_detail_id AND
pl.order_header_id = p_source_document_id AND
pl.order_line_id = p_source_document_line_id AND
jtc.tax_id = ptl.tax_id
GROUP by ptl.tax_line_no ,
ptl.tax_id ,
ptl.tax_rate ,
ptl.qty_rate ,
ptl.uom ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
jtc.tax_type ,
nvl(jtc.rounding_factor,0) ;/*bduvarag for the bug#6071813*/
SELECT
'X'
FROM
JAI_OM_OE_RMA_TAXES
WHERE
rma_line_id = p_line_id AND
tax_id = p_tax_id ;
SELECT
sum(quantity)
FROM
JAI_OM_WSH_LINES_ALL pl /*,
JAI_OM_WSH_LINE_TAXES ptl*/
WHERE
/*ptl.delivery_detail_id = pl.delivery_detail_id AND*/ /*Commented JAI_OM_WSH_LINE_TAXES and join by mmurtuza for bug 14353666*/
pl.order_header_id = p_source_document_id AND
pl.order_line_id = p_source_document_line_id ;
SELECT
requested_quantity_uom
FROM
wsh_delivery_details
WHERE
delivery_detail_id = p_delivery_detail_id;
SELECT
1
FROM
jai_regime_tax_types_v
WHERE
regime_code = jai_constants.vat_regime
AND tax_type = cp_tax_type;
select delivery_detail_id from JAI_OM_OE_RMA_LINES
where rma_header_id = p_source_document_id AND
rma_line_id = p_source_document_line_id;
SELECT
wdd.delivery_detail_id,
wnd.confirm_date
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE
wdd.delivery_detail_id = p_ddetail_id AND -- Added p_ddetail_id by bgowrava for bug#6126581
wda.delivery_detail_id = wdd.delivery_detail_id AND
wnd.delivery_id = wda.delivery_id ;
SELECT
excise_return_days,
sales_return_days,
vat_return_days ,
nvl(manufacturing,'N') manufacturing,
nvl(trading,'N') trading
FROM
JAI_CMN_INVENTORY_ORGS
WHERE
organization_id = pr_new.ship_from_org_id
AND location_id = 0 ;
SELECT
ordered_date
FROM
oe_order_headers_all
WHERE
header_id = pr_new.header_id ;
SELECT
CREATION_DATE
FROM
JAI_OM_WSH_LINES_ALL
WHERE ORDER_LINE_ID = v_reference_line_id
AND SHIPPABLE_FLAG = 'N';
SELECT
SUM(wdd.shipped_quantity) qty
FROM
wsh_delivery_details wdd
WHERE
wdd.delivery_detail_id in (SELECT delivery_detail_id
FROM JAI_OM_WSH_LINES_ALL
WHERE order_header_id = p_source_document_id
and order_line_id = p_source_document_line_id)
AND wdd.inventory_item_id = pr_new.inventory_item_id ;
SELECT
quantity
FROM
JAI_OM_OE_SO_LINES
WHERE
line_id = pr_new.reference_line_id ;**/
SELECT
tax_amount
FROM
JAI_OM_OE_SO_TAXES
WHERE
line_id = pr_new.reference_line_id
AND tax_id = p_tax_id ;
only then go ahead with the insert
*/
IF cur_rma_entry_line_exists%NOTFOUND THEN
-- Insert a record into JAI_OM_OE_RMA_LINES
INSERT INTO JAI_OM_OE_RMA_LINES
(
rma_line_number ,
rma_line_id ,
rma_header_id ,
rma_number ,
inventory_item_id ,
uom ,
quantity ,
tax_category_id ,
selling_price ,
tax_amount ,
delivery_detail_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login,service_type_code
)
VALUES
(
v_line_number ,
v_line_id ,
v_header_id ,
v_order_number ,
rec_cur_get_picking_lines.inventory_item_id ,
-- rec_cur_get_picking_lines.unit_code , bug#9963286
v_rma_quantity_uom ,
-- rec_cur_get_picking_lines.quantity , bug#9963286
pr_new.ordered_quantity ,
rec_cur_get_picking_lines.tax_category_id ,
--rec_cur_get_picking_lines.selling_price , bug#9963286
pr_new.unit_selling_price,
rec_cur_get_picking_lines.tax_amount ,
rec_cur_get_picking_lines.delivery_detail_id ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login,v_service_type_code
);
|| Following code copied from internal procedure rma_insert of Procedure JA_IN_RMA_MAINTAIN (version 115.5)
|| Added for bug#5256498, Starts --bduvarag
*/
OPEN c_get_detail_id(v_ddetail_id) ;
INSERT INTO JAI_OM_OE_RMA_TAXES
(
rma_line_id ,
tax_line_no ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
base_tax_amount ,
func_tax_amount ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
delivery_detail_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
v_line_id ,
rec_cur_get_picking_tax_lines.tax_line_no ,
rec_cur_get_picking_tax_lines.tax_id ,
rec_cur_get_picking_tax_lines.tax_rate ,
rec_cur_get_picking_tax_lines.qty_rate ,
rec_cur_get_picking_tax_lines.uom ,
v_round_tax,
v_round_base,
v_round_func,
rec_cur_get_picking_tax_lines.precedence_1 ,
rec_cur_get_picking_tax_lines.precedence_2 ,
rec_cur_get_picking_tax_lines.precedence_3 ,
rec_cur_get_picking_tax_lines.precedence_4 ,
rec_cur_get_picking_tax_lines.precedence_5 ,
rec_cur_get_picking_tax_lines.precedence_6 ,
rec_cur_get_picking_tax_lines.precedence_7 ,
rec_cur_get_picking_tax_lines.precedence_8 ,
rec_cur_get_picking_tax_lines.precedence_9 ,
rec_cur_get_picking_tax_lines.precedence_10 ,
rec_cur_get_picking_tax_lines.delivery_detail_id ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
UPDATE JAI_OM_OE_RMA_LINES
SET tax_amount = v_tax_total
WHERE rma_line_id = v_line_id ;
FOR tax_line_rec IN (SELECT tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
sptl.tax_id,
sptl.tax_rate,
sptl.qty_rate,
uom,
sptl.tax_amount,
nvl(jtc.rounding_factor,0) rounding_factor,
base_tax_amount,
func_tax_amount,
jtc.tax_type ,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10
FROM JAI_OM_WSH_LINE_TAXES sptl,
JAI_CMN_TAXES_ALL jtc
WHERE order_line_id = v_reference_line_id
AND jtc.tax_id = sptl.tax_id)
LOOP
-- added by Allen Yang for bug 9691880 10-May-2010, begin
lv_check_vat_type_exists := NULL;
INSERT INTO JAI_OM_OE_RMA_TAXES
(rma_line_id,
delivery_detail_id,
tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
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 ,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10)
VALUES (v_line_id,
NULL, -- delivery_detail_id
tax_line_rec.tax_line_no,
tax_line_rec.precedence_1,
tax_line_rec.precedence_2,
tax_line_rec.precedence_3,
tax_line_rec.precedence_4,
tax_line_rec.precedence_5,
tax_line_rec.tax_id,
tax_line_rec.tax_rate,
tax_line_rec.qty_rate,
tax_line_rec.uom,
-- modified by Allen Yang for bug 9691880 10-May-2010, begin
v_round_tax, --tax_line_rec.tax_amount,
v_round_base, --tax_line_rec.base_tax_amount,
v_round_func, --tax_line_rec.func_tax_amount,
-- modified by Allen Yang for bug 9691880 10-May-2010, end
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login ,
tax_line_rec.precedence_6,
tax_line_rec.precedence_7,
tax_line_rec.precedence_8,
tax_line_rec.precedence_9,
tax_line_rec.precedence_10
);
END LOOP; -- tax_line_rec IN (SELECT tax_line_no ......
SELECT *
FROM JAI_OM_OE_RMA_LINES
WHERE rma_header_id = cp_rma_header_id
AND rma_line_id = cp_rma_line_id;
SELECT order_number
FROM oe_order_headers_all
WHERE header_id = cp_rma_header_id;
insert into JAI_OM_OE_RMA_LINES
(
rma_line_id ,
rma_line_number ,
rma_header_id ,
rma_number ,
picking_line_id ,
uom ,
selling_price ,
quantity ,
tax_category_id ,
tax_amount ,
inventory_item_id ,
received_flag ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
excise_duty_rate ,
rate_per_unit ,
delivery_detail_id
)
values (
pr_new.line_id ,
pr_new.line_number ,
pr_new.header_id ,
lv_rma_number ,
null ,
pr_new.order_quantity_uom ,
pr_new.unit_selling_price,
pr_new.ordered_quantity,
cv_rma_details.tax_category_id ,
(cv_rma_details.tax_amount) ,
pr_new.inventory_item_id ,
cv_rma_details.received_flag,
cv_rma_details.assessable_value,
sysdate,
pr_new.created_by,
sysdate,
pr_new.last_updated_by,
pr_new.last_update_login,
cv_rma_details.excise_duty_rate,
cv_rma_details.rate_per_unit,
null
);
(select *
from JAI_OM_OE_RMA_TAXES
where rma_line_id = pr_new.source_document_line_id
)
Loop
insert into JAI_OM_OE_RMA_TAXES
(
rma_line_id ,
tax_line_no ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
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 ,
delivery_detail_id ,
/*added precedence 6 to 10 for bug#6485212 */
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10
)
values
(
pr_new.line_id,
cv_rma_taxes.tax_line_no ,
cv_rma_taxes.precedence_1,
cv_rma_taxes.precedence_2,
cv_rma_taxes.precedence_3,
cv_rma_taxes.precedence_4,
cv_rma_taxes.precedence_5,
cv_rma_taxes.tax_id ,
cv_rma_taxes.tax_rate,
cv_rma_taxes.qty_rate,
cv_rma_taxes.uom,
(cv_rma_taxes.tax_amount) ,
cv_rma_taxes.base_tax_amount,
cv_rma_taxes.func_Tax_amount,
pr_new.creation_date,
pr_new.created_by,
pr_new.last_update_Date,
pr_new.last_updated_by ,
pr_new.last_update_login,
cv_rma_taxes.delivery_detail_id,
/*added precedence 6 to 10 for bug#6485212 */
cv_rma_taxes.precedence_6,
cv_rma_taxes.precedence_7,
cv_rma_taxes.precedence_8,
cv_rma_taxes.precedence_9,
cv_rma_taxes.precedence_10
);
SELECT order_number
FROM oe_order_headers_all
WHERE header_id = cp_rma_header_id;
SELECT
pl.inventory_item_id inventory_item_id ,
pl.unit_code unit_code ,
sum(pl.quantity ) quantity ,
pl.tax_category_id tax_category_id ,
pl.selling_price selling_price ,
sum(pl.tax_amount) tax_amount ,
min(pl.delivery_detail_id) delivery_detail_id
FROM
JAI_OM_WSH_LINES_ALL pl
WHERE
pl.order_header_id = p_source_document_id AND
pl.order_line_id = p_source_document_line_id
GROUP BY
pl.inventory_item_id ,
pl.unit_code ,
pl.selling_price ,
pl.tax_category_id ;
select quantity,service_type_code
from JAI_OM_OE_SO_LINES
where line_id=v_reference_line_id;
SELECT
'X'
FROM
JAI_OM_OE_RMA_LINES
WHERE
rma_line_id = p_line_id AND
rma_header_id = p_header_id ;
SELECT
ptl.tax_line_no tax_line_no ,
ptl.tax_id tax_id ,
ptl.tax_rate tax_rate ,
ptl.qty_rate qty_rate ,
ptl.uom uom ,
ptl.precedence_1 precedence_1 ,
ptl.precedence_2 precedence_2 ,
ptl.precedence_3 precedence_3 ,
ptl.precedence_4 precedence_4 ,
ptl.precedence_5 precedence_5 ,
ptl.precedence_6 precedence_6 ,
ptl.precedence_7 precedence_7 ,
ptl.precedence_8 precedence_8 ,
ptl.precedence_9 precedence_9 ,
ptl.precedence_10 precedence_10 ,
jtc.tax_type tax_type ,
nvl(jtc.rounding_factor,0) rounding_factor , /*bduvarag for the bug#6071813*/
SUM (ptl.tax_amount) tax_amount ,
SUM (ptl.base_tax_amount) base_tax_amount ,
SUM (ptl.func_tax_amount) func_tax_amount ,
MIN (ptl.delivery_detail_id) delivery_detail_id
FROM
JAI_OM_WSH_LINES_ALL pl ,
JAI_OM_WSH_LINE_TAXES ptl ,
JAI_CMN_TAXES_ALL jtc
WHERE
ptl.delivery_detail_id = pl.delivery_detail_id AND
pl.order_header_id = p_source_document_id AND
pl.order_line_id = p_source_document_line_id AND
jtc.tax_id = ptl.tax_id
GROUP by ptl.tax_line_no ,
ptl.tax_id ,
ptl.tax_rate ,
ptl.qty_rate ,
ptl.uom ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
jtc.tax_type ,
nvl(jtc.rounding_factor,0) ;/*bduvarag for the bug#6071813*/
SELECT
'X'
FROM
JAI_OM_OE_RMA_TAXES
WHERE
rma_line_id = p_line_id AND
tax_id = p_tax_id ;
SELECT
sum(quantity)
FROM
JAI_OM_WSH_LINES_ALL pl /*,
JAI_OM_WSH_LINE_TAXES ptl*/
WHERE
/*ptl.delivery_detail_id = pl.delivery_detail_id AND*/ /*Commented JAI_OM_WSH_LINE_TAXES and join by mmurtuza for bug 14353666*/
pl.order_header_id = p_source_document_id AND
pl.order_line_id = p_source_document_line_id ;
SELECT
requested_quantity_uom
FROM
wsh_delivery_details
WHERE
delivery_detail_id = p_delivery_detail_id;
SELECT
1
FROM
jai_regime_tax_types_v
WHERE
regime_code = jai_constants.vat_regime
AND tax_type = cp_tax_type;
select delivery_detail_id from JAI_OM_OE_RMA_LINES
where rma_header_id = p_source_document_id AND
rma_line_id = p_source_document_line_id;
SELECT
wdd.delivery_detail_id,
wnd.confirm_date
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE
wdd.delivery_detail_id = p_ddetail_id AND -- Added p_ddetail_id by bgowrava for bug#6126581
wda.delivery_detail_id = wdd.delivery_detail_id AND
wnd.delivery_id = wda.delivery_id ;
SELECT
excise_return_days,
sales_return_days,
vat_return_days ,
nvl(manufacturing,'N') manufacturing,
nvl(trading,'N') trading
FROM
JAI_CMN_INVENTORY_ORGS
WHERE
organization_id = pr_new.ship_from_org_id
AND location_id = 0 ;
SELECT
ordered_date
FROM
oe_order_headers_all
WHERE
header_id = pr_new.header_id ;
SELECT
CREATION_DATE
FROM
JAI_OM_WSH_LINES_ALL
WHERE ORDER_LINE_ID = v_reference_line_id
AND SHIPPABLE_FLAG = 'N';
SELECT
SUM(wdd.shipped_quantity) qty
FROM
wsh_delivery_details wdd
WHERE
wdd.delivery_detail_id in (SELECT delivery_detail_id
FROM JAI_OM_WSH_LINES_ALL
WHERE order_header_id = p_source_document_id
and order_line_id = p_source_document_line_id)
AND wdd.inventory_item_id = pr_new.inventory_item_id ;
SELECT
tax_amount
FROM
JAI_OM_OE_SO_TAXES
WHERE
line_id = pr_new.reference_line_id
AND tax_id = p_tax_id ;
only then go ahead with the insert
*/
IF cur_rma_entry_line_exists%NOTFOUND THEN
INSERT INTO JAI_OM_OE_RMA_LINES
(
rma_line_number ,
rma_line_id ,
rma_header_id ,
rma_number ,
inventory_item_id ,
uom ,
quantity ,
tax_category_id ,
selling_price ,
tax_amount ,
delivery_detail_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login,service_type_code
)
VALUES
(
v_line_number ,
v_line_id ,
v_header_id ,
v_order_number ,
rec_cur_get_picking_lines.inventory_item_id ,
-- rec_cur_get_picking_lines.unit_code , bug#9963286
v_rma_quantity_uom ,
-- rec_cur_get_picking_lines.quantity , bug#9963286
pr_new.ordered_quantity ,
rec_cur_get_picking_lines.tax_category_id ,
--rec_cur_get_picking_lines.selling_price , bug#9963286
pr_new.unit_selling_price,
rec_cur_get_picking_lines.tax_amount ,
rec_cur_get_picking_lines.delivery_detail_id ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login,v_service_type_code
);
INSERT INTO JAI_OM_OE_RMA_TAXES
(
rma_line_id ,
tax_line_no ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
base_tax_amount ,
func_tax_amount ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
delivery_detail_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
v_line_id ,
rec_cur_get_picking_tax_lines.tax_line_no ,
rec_cur_get_picking_tax_lines.tax_id ,
rec_cur_get_picking_tax_lines.tax_rate ,
rec_cur_get_picking_tax_lines.qty_rate ,
rec_cur_get_picking_tax_lines.uom ,
v_round_tax,
v_round_base,
v_round_func,
rec_cur_get_picking_tax_lines.precedence_1 ,
rec_cur_get_picking_tax_lines.precedence_2 ,
rec_cur_get_picking_tax_lines.precedence_3 ,
rec_cur_get_picking_tax_lines.precedence_4 ,
rec_cur_get_picking_tax_lines.precedence_5 ,
rec_cur_get_picking_tax_lines.precedence_6 ,
rec_cur_get_picking_tax_lines.precedence_7 ,
rec_cur_get_picking_tax_lines.precedence_8 ,
rec_cur_get_picking_tax_lines.precedence_9 ,
rec_cur_get_picking_tax_lines.precedence_10 ,
rec_cur_get_picking_tax_lines.delivery_detail_id ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
UPDATE JAI_OM_OE_RMA_LINES
SET tax_amount = v_tax_total
WHERE rma_line_id = v_line_id ;
FOR tax_line_rec IN (SELECT tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
sptl.tax_id,
sptl.tax_rate,
sptl.qty_rate,
uom,
sptl.tax_amount,
nvl(jtc.rounding_factor,0) rounding_factor,
base_tax_amount,
func_tax_amount,
jtc.tax_type ,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10
FROM JAI_OM_WSH_LINE_TAXES sptl,
JAI_CMN_TAXES_ALL jtc
WHERE order_line_id = v_reference_line_id
AND jtc.tax_id = sptl.tax_id)
LOOP
lv_check_vat_type_exists := NULL;
INSERT INTO JAI_OM_OE_RMA_TAXES
(rma_line_id,
delivery_detail_id,
tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
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 ,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10)
VALUES (v_line_id,
NULL, -- delivery_detail_id
tax_line_rec.tax_line_no,
tax_line_rec.precedence_1,
tax_line_rec.precedence_2,
tax_line_rec.precedence_3,
tax_line_rec.precedence_4,
tax_line_rec.precedence_5,
tax_line_rec.tax_id,
tax_line_rec.tax_rate,
tax_line_rec.qty_rate,
tax_line_rec.uom,
v_round_tax, --tax_line_rec.tax_amount,
v_round_base, --tax_line_rec.base_tax_amount,
v_round_func, --tax_line_rec.func_tax_amount,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login ,
tax_line_rec.precedence_6,
tax_line_rec.precedence_7,
tax_line_rec.precedence_8,
tax_line_rec.precedence_9,
tax_line_rec.precedence_10
);
END LOOP; -- tax_line_rec IN (SELECT tax_line_no ......
SELECT COUNT(1) INTO v_tax_line_count
FROM JAI_OM_OE_SO_TAXES
WHERE line_id = v_line_id
AND tax_id = rec.tax_id ;
INSERT INTO JAI_OM_OE_SO_TAXES (
header_id, line_id, tax_line_no, tax_id,
tax_rate, qty_rate, uom, precedence_1,
precedence_2, precedence_3, precedence_4, precedence_5,
/*precedence 6 to 10 added by csahoo for bug#6485212 */
precedence_6, precedence_7, precedence_8, precedence_9 ,precedence_10,
tax_amount, base_tax_amount, func_tax_amount, creation_date,
created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
v_header_id, v_line_id, rec.tax_line_no, rec.tax_id,
rec.tax_rate, rec.qty_rate, rec.uom, rec.precedence_1,
rec.precedence_2, rec.precedence_3, rec.precedence_4, rec.precedence_5,
/*precedence 6 to 10 added by csahoo for bug#6485212 */
rec.precedence_6,rec.precedence_7, rec.precedence_8, rec.precedence_9, rec.precedence_10,
rec.tax_amount, rec.base_tax_amount, rec.func_tax_amount, v_creation_date,
v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
rec.tax_category_id -- cbabu for EnhancementBug# 2427465
);
'OE_LINES_UPDATE',NULL,pr_new.header_id , pr_new.line_id ,
v_assessable_value /*r_get_copy_order_line.assessable_value*/ * (pr_new.ordered_quantity) , /*changed to v_assessable_value by mmurtuza for bug 14675557*/
r_get_copy_order_line.line_amount ,
v_converted_rate,pr_new.inventory_item_id,pr_new.ordered_quantity , pr_new.ordered_quantity, pr_new.pricing_quantity_uom,
NULL,NULL,NULL,NULL,pr_new.last_update_date,pr_new.last_updated_by,pr_new.last_update_login ,
ln_vat_assessable_value /*r_get_copy_order_line.vat_assessable_value*/ /*changed to ln_vat_assessable_value by mmurtuza for bug 14675557*/
/*,pn_gst_assessable_Value => r_get_copy_order_line.gst_assessable_value */--Added by zhiwei for bug10043656 GST enhancement 2010/09/14
);
update JAI_OM_OE_SO_LINES
set tax_amount = NVL(r_get_copy_order_line.line_amount,0) ,
line_tot_amount = line_amount + NVL(r_get_copy_order_line.line_amount,0),
assessable_value = v_assessable_value, /*Added v_assessable_value by mmurtuza for bug 14675557*/
vat_assessable_Value = ln_vat_assessable_value /*r_get_copy_order_line.vat_assessable_value*/ /*changed to ln_vat_assessable_value by mmurtuza for bug 14675557*/
/*,gst_assessable_Value = r_get_copy_order_line.gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
where header_id = pr_new.header_id
and line_id = pr_new.line_id;
SELECT
tax_line_no ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
/*precedence 6 to 10 added for bug#6485212 */
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_amount ,
base_tax_amount ,
func_tax_amount
FROM
JAI_OM_OE_RMA_TAXES
WHERE
rma_line_id = p_line_id ;
SELECT
'X'
FROM
JAI_OM_OE_SO_TAXES
WHERE
line_id = p_line_id1 AND
tax_id = p_tax_id;
INSERT INTO JAI_OM_OE_SO_TAXES (
header_id ,
line_id ,
tax_line_no ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
/*precedence 6 to 10 added by csahoo for bug#6485212 */
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_amount ,
base_tax_amount ,
func_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES (
v_header_id ,
v_line_id ,
rec_get_rma_tax_lines.tax_line_no ,
rec_get_rma_tax_lines.tax_id ,
rec_get_rma_tax_lines.tax_rate ,
rec_get_rma_tax_lines.qty_rate ,
rec_get_rma_tax_lines.uom ,
rec_get_rma_tax_lines.precedence_1 ,
rec_get_rma_tax_lines.precedence_2 ,
rec_get_rma_tax_lines.precedence_3 ,
rec_get_rma_tax_lines.precedence_4 ,
rec_get_rma_tax_lines.precedence_5 ,
/*precedence 6 to 10 added by csahoo for bug#6485212 */
rec_get_rma_tax_lines.precedence_6 ,
rec_get_rma_tax_lines.precedence_7 ,
rec_get_rma_tax_lines.precedence_8 ,
rec_get_rma_tax_lines.precedence_9 ,
rec_get_rma_tax_lines.precedence_10 ,
rec_get_rma_tax_lines.tax_amount ,
rec_get_rma_tax_lines.base_tax_amount ,
rec_get_rma_tax_lines.func_tax_amount ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
/*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
);
UPDATE
JAI_OM_OE_SO_LINES
SET
assessable_value = v_assessable_value ,
tax_amount = NVL(v_line_tax_amount,0) ,
line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
last_update_date = v_last_update_date ,
last_updated_by = v_last_updated_by ,
last_update_login = v_last_update_login
WHERE
header_id = v_header_id AND
line_id = v_line_id;
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
/*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
);
UPDATE
JAI_OM_OE_SO_LINES
SET
assessable_value = v_assessable_value ,
tax_amount = NVL(v_line_tax_amount,0) ,
line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
last_update_date = v_last_update_date ,
last_updated_by = v_last_updated_by ,
last_update_login = v_last_update_login
WHERE
header_id = v_header_id AND
line_id = v_line_id;
SELECT
ptl.tax_line_no ,
ptl.tax_id ,
ptl.tax_rate ,
ptl.qty_rate ,
ptl.uom ,
ptl.precedence_1 ,
ptl.precedence_2 ,
ptl.precedence_3 ,
ptl.precedence_4 ,
ptl.precedence_5 ,
ptl.tax_amount ,
ptl.base_tax_amount ,
ptl.func_tax_amount ,
ptl.delivery_detail_id
FROM
JAI_OM_WSH_LINES_ALL pl ,
JAI_OM_WSH_LINE_TAXES ptl
WHERE
ptl.delivery_detail_id = pl.delivery_detail_id AND
pl.order_header_id = p_source_document_id AND
pl.order_line_id = p_source_document_line_id;
SELECT
ptl.tax_line_no tax_line_no ,
ptl.tax_id tax_id ,
ptl.tax_rate tax_rate ,
ptl.qty_rate qty_rate ,
ptl.uom uom ,
ptl.precedence_1 precedence_1 ,
ptl.precedence_2 precedence_2 ,
ptl.precedence_3 precedence_3 ,
ptl.precedence_4 precedence_4 ,
ptl.precedence_5 precedence_5 ,
/*precedence 6 to 10 added for bug#6485212 */
ptl.precedence_6 precedence_6 ,
ptl.precedence_7 precedence_7 ,
ptl.precedence_8 precedence_8 ,
ptl.precedence_9 precedence_9 ,
ptl.precedence_10 precedence_10 ,
SUM (ptl.tax_amount) tax_amount ,
SUM (ptl.base_tax_amount) base_tax_amount ,
SUM (ptl.func_tax_amount) func_tax_amount ,
MIN (ptl.delivery_detail_id) delivery_detail_id
FROM
JAI_OM_WSH_LINES_ALL pl ,
JAI_OM_WSH_LINE_TAXES ptl
WHERE
ptl.delivery_detail_id = pl.delivery_detail_id AND
pl.order_header_id = p_source_document_id AND
pl.order_line_id = p_source_document_line_id
GROUP by tax_line_no ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
/*precedence 6 to 10 added for bug#6485212 */
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ;
SELECT
'X'
FROM
JAI_OM_OE_RMA_TAXES
WHERE
rma_line_id = p_line_id AND
tax_id = p_tax_id;
INSERT INTO JAI_OM_OE_RMA_TAXES
(
rma_line_id ,
tax_line_no ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
base_tax_amount ,
func_tax_amount ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
/*precedence 6 to 10 added for bug#6485212 */
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
delivery_detail_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES (
v_line_id ,
rec_cur_get_picking_tax_lines.tax_line_no ,
rec_cur_get_picking_tax_lines.tax_id ,
rec_cur_get_picking_tax_lines.tax_rate ,
rec_cur_get_picking_tax_lines.qty_rate ,
rec_cur_get_picking_tax_lines.uom ,
rec_cur_get_picking_tax_lines.tax_amount ,
rec_cur_get_picking_tax_lines.base_tax_amount ,
rec_cur_get_picking_tax_lines.func_tax_amount ,
rec_cur_get_picking_tax_lines.precedence_1 ,
rec_cur_get_picking_tax_lines.precedence_2 ,
rec_cur_get_picking_tax_lines.precedence_3 ,
rec_cur_get_picking_tax_lines.precedence_4 ,
rec_cur_get_picking_tax_lines.precedence_5 ,
/*precedence 6 to 10 added for bug#6485212 */
rec_cur_get_picking_tax_lines.precedence_6 ,
rec_cur_get_picking_tax_lines.precedence_7 ,
rec_cur_get_picking_tax_lines.precedence_8 ,
rec_cur_get_picking_tax_lines.precedence_9 ,
rec_cur_get_picking_tax_lines.precedence_10 ,
rec_cur_get_picking_tax_lines.delivery_detail_id ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
INSERT INTO JAI_OM_OE_SO_LINES (
line_number,
line_id,
header_id,
SPLIT_FROM_LINE_ID,
SHIPMENT_LINE_NUMBER,
shipment_schedule_line_id, -- uncommented by sriram - for lmw ATO issue
inventory_item_id,
unit_code,
ato_flag,
quantity,
tax_category_id,
selling_price,
assessable_value,
line_amount,
tax_amount,
line_tot_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
/* following 3 columns added by sriram on 03-nov-2002 bug # 2672114*/
EXCISE_EXEMPT_TYPE,
EXCISE_EXEMPT_REFNO,
EXCISE_EXEMPT_DATE ,
lc_flag ,/* added by aiyer for the bug #3057594 */
VAT_EXEMPTION_FLAG ,
VAT_EXEMPTION_TYPE ,
VAT_EXEMPTION_DATE ,
VAT_EXEMPTION_REFNO,
VAT_ASSESSABLE_VALUE,
VAT_REVERSAL_PRICE,--Added by kunkumar for forward porting to R12
service_type_code --Added by kunkumar for forward porting to R12
/*,GST_ASSESSABLE_VALUE*/
)
VALUES
(
pr_new.line_number,
v_line_id,
v_header_id,
pr_new.SPLIT_FROM_LINE_ID,
pr_new.SHIPMENT_NUMBER,
pr_new.ato_line_id, -- changed this column from pr_new.shipment_schedule_line_id - sriram - LMW issue.
v_inventory_item_id,
pr_new.ORDER_QUANTITY_UOM,
'Y',
pr_new.ordered_quantity,
copy_rec.tax_category_id,
pr_new.UNIT_SELLING_PRICE,
copy_rec.assessable_value,
v_line_new_amount,
0, --v_line_new_tax_amount, commented by Nagaraj.s for Bug3140153
0, --(v_line_new_amount + v_line_new_tax_amount), --v_line_new_tax_amount, commented by Nagaraj.s for Bug3140153
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
Copy_rec.EXCISE_EXEMPT_TYPE, /* following 3 columns added by sriram on 03-nov-2002 bug # 2672114*/
copy_rec.EXCISE_EXEMPT_REFNO,
copy_rec.EXCISE_EXEMPT_DATE ,
l_lc_flag , /* added by aiyer for the bug #3057594 */
Copy_rec.VAT_EXEMPTION_FLAG ,
Copy_rec.VAT_EXEMPTION_TYPE ,
Copy_rec.VAT_EXEMPTION_DATE ,
Copy_rec.VAT_EXEMPTION_REFNO,
v_new_vat_assessable_value, -- added by ssawant for Bug 4660756
(copy_rec.vat_reversal_price/copy_rec.quantity)*(v_line_quantity),--Added by kunkumar for forward porting to R12
copy_rec.service_type_code --Added by kunkumar for forward porting to R12
/* ,v_new_gst_assessable_value*/--added by peng.zheng for bug 10043656
);
SELECT COUNT(1) INTO v_tax_line_count
FROM JAI_OM_OE_SO_TAXES
WHERE line_id = v_line_id
AND tax_id = rec.tax_id ;
INSERT INTO JAI_OM_OE_SO_TAXES (
header_id, line_id, tax_line_no, tax_id,
tax_rate, qty_rate, uom, precedence_1,
precedence_2, precedence_3, precedence_4, precedence_5,
/*precedence 6 to 10 added by csahoo for bug#6485212 */
precedence_6, precedence_7, precedence_8, precedence_9,precedence_10,
tax_amount, base_tax_amount, func_tax_amount, creation_date,
created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
v_header_id, v_line_id, rec.tax_line_no, rec.tax_id,
rec.tax_rate, rec.qty_rate, rec.uom, rec.precedence_1,
rec.precedence_2, rec.precedence_3, rec.precedence_4, rec.precedence_5,
/*precedence 6 to 10 added by csahoo for bug#6485212 */
rec.precedence_6, rec.precedence_7, rec.precedence_8, rec.precedence_9,rec.precedence_10,
v_new_tax_amount, v_new_base_tax_amount, v_new_func_tax_amount, v_creation_date,
v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
rec.tax_category_id -- cbabu for EnhancementBug# 2427465
);
update JAI_OM_OE_SO_LINES
set tax_amount = nvl(v_header_tax_amount,0),
line_tot_amount = nvl(v_header_tax_amount,0) + nvl(line_amount,0)
where header_id = v_header_id
and line_id = v_line_id;
update JAI_OM_OE_SO_LINES
set tax_amount = v_header_tax_amount,
line_tot_amount = v_header_tax_amount + line_amount
where header_id = v_header_id
and line_id = v_line_id;
inserted into the JAI_OM_OE_SO_LINES table.
Even though a rma line is not having the return_context field still the line should be treated as
RMA and not as a sales order line.
Solution:-
Added an NVL clause to the below IF statement .
Now even if the Return_context is null it would be treated as = LEGACY
and the v_transaction_name flag would be set to LEGACY.
Due to this the record would be inserted into the JAI_OM_OE_RMA_LINES table instead of the
JAI_OM_OE_SO_LINES table.
*/
-- Start of bug #2979969
IF pr_new.LINE_CATEGORY_CODE = 'RETURN' THEN
-- Start of Bug # 3344454
/**************
Code modified by aiyer for the bug 3344454
**********/
/*
||Added by aiyer for the bug 5401180
||modified the if statement , original condition
|| IF NVL(pr_new.RETURN_CONTEXT,'LEGACY') = 'LEGACY'
*/
IF pr_new.return_context IS NULL THEN
-- End of bug #2979969
-- overwrite the transaction name
v_transaction_name := 'RMA_LEGACY_INSERT';
IF ( (v_line_category_code='ORDER') OR (v_transaction_name='RMA_LEGACY_INSERT') ) THEN --and V_Order_Source_Type = 'Internal'
IF v_debug = 'Y' THEN
utl_file.put_line(v_myfilehandle, ' inside IF OF v_line_category_code IN (ORDER) OR v_transaction_name = RMA_LEGACY_INSERT');
IF ( v_transaction_name = 'RMA_LEGACY_INSERT') THEN
DELETE JAI_OM_OE_RMA_LINES
WHERE RMA_LINE_ID = V_LINE_ID;
DELETE JAI_OM_OE_RMA_TAXES
WHERE RMA_LINE_ID = V_LINE_ID;
DELETE JAI_OM_OE_SO_LINES
WHERE LINE_ID = v_line_id;
DELETE JAI_OM_OE_SO_TAXES
WHERE Line_ID = v_line_id;
IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
IF v_debug = 'Y' THEN
utl_file.put_line(v_myfilehandle, ' inside IF OF RMA_LEGACY_INSERT ');
utl_file.put_line(v_myfilehandle, ' inside ELSE OF RMA_LEGACY_INSERT ');
p_last_update_date => v_last_update_date,
p_last_updated_by => v_last_updated_by,
p_last_update_login => v_last_update_login,
p_operation_flag => NULL,
p_vat_assessable_value => ln_vat_assessable_value
/*, pn_gst_assessable_value => ln_gst_assessable_value*/ -- Added by Jia for GST Bug#10043656 on 2010/09/10
);
IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
OPEN get_rma_lines_count_cur(v_line_id);
IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
IF v_debug = 'Y' THEN -- added by sriram - because it was causing errors when utl_file is not setup bug # 2687045
utl_file.put_line(v_myfilehandle, 'BEFORE opening return_tax_amount_Cur ');
IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
IF v_debug = 'Y' THEN
utl_file.put_line(v_myfilehandle, ' BEFORE INSERTING RECORD INTO JAI_OM_OE_RMA_LINES ');
INSERT INTO JAI_OM_OE_RMA_LINES (
rma_line_number,
rma_line_id,
rma_header_id,
rma_number,
inventory_item_id,
uom,
quantity,
tax_category_id,
selling_price,
tax_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
assessable_value -- cbabu for Bug# 2687130
)
VALUES
(
v_line_number,
v_line_id,
v_header_id,
v_order_number,
v_inventory_item_id,
pr_new.ORDER_QUANTITY_UOM,
pr_new.ordered_quantity,
v_tax_category_id,
pr_new.UNIT_SELLING_PRICE,
v_line_tax_amount,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
v_assessable_value -- cbabu for Bug# 2687130
);
ELSE /* else if of v_transaction_name = 'RMA_LEGACY_INSERT' */
IF v_debug = 'Y' THEN
utl_file.put_line(v_myfilehandle, ' BEFORE INSERTING RECORD INTO JAI_OM_OE_SO_LINES ');
SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
INSERT INTO JAI_OM_OE_SO_LINES
(
line_number,
line_id,
header_id,
SHIPMENT_LINE_NUMBER,
shipment_schedule_line_id,-- uncommented by sriram - for lmw ato issue
inventory_item_id,
unit_code,
ato_flag,
quantity,
tax_category_id,
selling_price,
assessable_value,
line_amount,
tax_amount,
line_tot_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
vat_assessable_value,
service_type_code/*bduvarag for the bug#5694855*/
/*,gst_assessable_value*/--added by peng.zheng for bug 10043656
)
VALUES
(
pr_new.line_number,
v_line_id,
v_header_id,
pr_new.SHIPMENT_NUMBER,
pr_new.ato_line_id, -- uncommented by sriram - for lmw ato issue
v_inventory_item_id,
pr_new.ORDER_QUANTITY_UOM,
'Y',
pr_new.ordered_quantity,
v_tax_category_id,
pr_new.UNIT_SELLING_PRICE,
v_assessable_value,
v_line_amount,
v_line_tax_amount,
(v_line_amount + v_line_tax_amount),
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
ln_vat_assessable_value,
v_service_type_code/*bduvarag for the bug#5694855*/
/*,ln_gst_assessable_value*/--added by peng.zheng for bug 10043656
);
update JAI_OM_OE_SO_LINES
set line_amount = v_ato_line_amount,
--tax_amount = v_ato_tax_amount -- Not reqired
assessable_value = v_ato_assessable_value,
vat_assessable_value = v_ato_vat_assessable_value, --added for bug#8924003
/*gst_assessable_value = v_ato_gst_assessable_value,*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
selling_price = v_ato_selling_price
where header_id = pr_new.header_id
and line_id = pr_new.line_id;
select *
from JAI_OM_OE_SO_TAXES
where header_id = pr_new.header_id
and line_id = pr_new.ato_line_id ;
select tax_category_id , inventory_item_id , line_amount
from JAI_OM_OE_SO_LINES
where header_id = pr_new.header_id
and line_id = pr_new.ato_line_id ;
Insert into JAI_OM_OE_SO_TAXES
(
tax_line_no ,
line_id ,
header_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
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,
tax_category_id ,
/*precedence 6 to 10 added by csahoo for bug#6485212 */
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10
)
Values
(
model_rec.tax_line_no,
pr_new.line_id,
pr_new.header_id,
model_rec.precedence_1,
model_rec.precedence_2,
model_rec.precedence_3,
model_rec.precedence_4,
model_rec.precedence_5,
model_rec.tax_id,
model_rec.tax_rate,
model_rec.qty_rate,
model_rec.uom,
model_rec.tax_amount,
model_rec.base_tax_amount,
model_rec.func_tax_amount,
model_rec.creation_date,
model_rec.created_by,
model_rec.last_update_date,
model_rec.last_updated_by,
model_rec.last_update_login,
model_rec.tax_category_id ,
/*precedence 6 to 10 added by csahoo for bug#6485212 */
model_rec.precedence_6,
model_rec.precedence_7,
model_rec.precedence_8,
model_rec.precedence_9,
model_rec.precedence_10
);
'OE_LINES_UPDATE',NULL,pr_new.header_id , pr_new.line_id , v_ato_assessable_value, v_ato_line_amount ,
v_converted_rate,pr_new.inventory_item_id,pr_new.ordered_quantity , pr_new.ordered_quantity, pr_new.pricing_quantity_uom,
NULL,NULL,NULL,NULL,pr_new.last_update_date,pr_new.last_updated_by,pr_new.last_update_login
,v_ato_vat_assessable_value --added for bug#8924003
/*,v_ato_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
);
update JAI_OM_OE_SO_LINES
set tax_amount = NVL(v_ato_line_amount,0) ,
line_tot_amount = line_amount + NVL(v_ato_line_amount,0)
where header_id = pr_new.header_id
and line_id = pr_new.line_id;
utl_file.put_line(v_myfilehandle,'** SUCCESSFUL END OF TRIGGER jai_oe_ola_ariu_t5 AFTER INSERT OR UPDATE ON OE_ORDER_LINES_ALL FOR EACH ROW ~ ' || TO_CHAR(SYSDATE,'DD/mm/rrrr hh24:mi:ss'));
utl_file.put_line(v_myfilehandle,'** Error END OF TRIGGER jai_oe_ola_ariu_t5 AFTER INSERT OR UPDATE ON OE_ORDER_LINES_ALL FOR EACH ROW ~ ' || TO_CHAR(SYSDATE,'DD/mm/rrrr hh24:mi:ss'));
ld_last_update_date DATE;
ln_last_updated_by NUMBER;
ln_last_update_login NUMBER;
SELECT
jcta.Tax_Type
, NVL(jcta.Rounding_Factor,2) Rounding_Factor
, joost.Tax_Line_No
, joost.Precedence_1
, joost.Precedence_2
, joost.Precedence_3
, joost.Precedence_4
, joost.Precedence_5
, joost.Precedence_6
, joost.Precedence_7
, joost.Precedence_8
, joost.Precedence_9
, joost.Precedence_10
, joost.Tax_Id
, joost.Tax_Rate
, joost.Qty_Rate
, joost.Uom
, joost.Tax_Amount
, joost.Base_Tax_Amount
, joost.Func_Tax_Amount
FROM
JAI_OM_OE_SO_TAXES joost
, JAI_CMN_TAXES_ALL jcta
WHERE joost.line_id = ln_order_line_id
AND joost.Tax_Id = jcta.Tax_Id
ORDER BY joost.Tax_Line_No;
SELECT COUNT(*)
FROM JAI_OM_WSH_LINES_ALL
WHERE Order_Line_Id = ln_order_line_id
AND Shippable_Flag = 'N';
SELECT COUNT(*)
FROM JAI_OM_WSH_LINE_TAXES
WHERE Order_Line_Id = ln_order_line_id
AND Tax_Id = P_Tax_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
, vat_exemption_flag
, vat_exemption_type
, vat_exemption_date
, vat_exemption_refno
/*, NVL(gst_assessable_value, 0)*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
FROM JAI_OM_OE_SO_LINES
WHERE Line_id = ln_order_line_id;
SELECT SUM(jowlt.Tax_Amount)
FROM JAI_OM_WSH_LINE_TAXES jowlt
, JAI_CMN_TAXES_ALL jcta
WHERE jowlt.Order_Line_Id = ln_order_line_id
AND jcta.Tax_Id = jowlt.Tax_Id
AND jcta.Tax_Type <> 'TDS';
SELECT Location_id
, trading
FROM JAI_INV_SUBINV_DTLS
WHERE Sub_Inventory_Name = lv_subinventory
AND organization_id = ln_organization_Id;
SELECT 1
FROM JAI_OM_WSH_LINE_TAXES jsptl
, JAI_CMN_TAXES_ALL jtc
, jai_regime_tax_types_v tax_types
WHERE jsptl.order_line_id = cp_order_line_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 1
FROM JAI_RGM_INVOICE_GEN_T
WHERE order_line_id = cp_order_line_id;
SELECT 1
FROM JAI_OM_WSH_LINE_TAXES jsptl
, JAI_CMN_TAXES_ALL jtc
WHERE jsptl.order_line_id = cp_order_line_id
AND jtc.tax_id = jsptl.tax_id
AND jtc.tax_type = cp_tax_type;
SELECT regime_id
, attribute_value
FROM JAI_RGM_ORG_REGNS_V orrg
WHERE orrg.organization_id = cp_organization_id
AND orrg.location_id = NVL(cp_location_id, cp_organization_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 order_type_id
, order_number
FROM oe_order_headers_all
WHERE header_id = ln_order_header_id;
SELECT 1
FROM JAI_OM_WSH_LINE_TAXES jsptl
, JAI_CMN_TAXES_ALL jtc
, jai_regime_tax_types_v tax_types
WHERE jsptl.order_line_id = cp_order_line_id
AND jtc.tax_id = jsptl.tax_id
AND jtc.tax_type = tax_types.tax_type
AND tax_types.regime_code = cp_regime_code;*/
ld_last_update_date := pr_new.Last_Update_Date;
ln_last_updated_by := pr_new.Last_Updated_By;
ln_last_update_login := pr_new.Last_Update_Login;
INSERT INTO JAI_OM_WSH_LINE_TAXES(Delivery_Detail_Id
, order_line_id
, Tax_Line_No
, Precedence_1
, Precedence_2
, Precedence_3
, Precedence_4
, Precedence_5
, Precedence_6
, 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 (NULL -- delivery_detail_id
, ln_order_line_id
, rec.Tax_Line_No
, rec.Precedence_1
, rec.Precedence_2
, rec.Precedence_3
, rec.Precedence_4
, rec.Precedence_5
, rec.Precedence_6
, rec.Precedence_7
, rec.Precedence_8
, rec.Precedence_9
, rec.Precedence_10
, rec.Tax_id
, rec.Tax_rate
, rec.Qty_Rate
, rec.Uom
, rec.tax_amount
, rec.base_tax_amount
, rec.func_tax_amount
, ld_creation_date
, ln_created_by
, ld_last_update_date
, ln_last_updated_by
, ln_last_update_login
);
UPDATE JAI_OM_WSH_LINE_TAXES
SET Tax_Amount = rec.tax_amount,
Last_Update_Date = ld_last_update_date,
Last_Updated_By = ln_last_updated_by,
Last_Update_Login = ln_last_update_login
WHERE ORDER_LINE_ID = ln_order_line_id
AND Tax_Id = rec.Tax_Id;
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
, VAT_EXEMPTION_FLAG
, VAT_EXEMPTION_TYPE
, VAT_EXEMPTION_DATE
, VAT_EXEMPTION_REFNO
, Shippable_Flag
/*, gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
)
VALUES (NULL -- delivery_detail_id
, ln_order_header_id
, ln_order_line_id
, NULL -- split_from_delivery_detail_id
, ln_selling_price
, ln_quantity
, ln_assessable_value
, ln_vat_assessable_value
, ln_tax_category_id
, ln_tot_tax_amount
, ln_inventory_item_id
, ln_organization_Id
, NVL(ln_location_id, ln_organization_Id)
, lv_unit_code
, NULL -- excise amount should be 0 for non-shippable item
, NULL -- basic_excise_duty_amount should be 0
, NULL -- add_excise_duty_amount should be 0
, NULL -- oth_excise_duty_amount should be 0
, lv_excise_exempt_type
, lv_excise_exempt_refno
, ld_excise_exempt_date
, ld_creation_date
, ln_created_by
, ld_last_update_date
, ln_last_updated_by
, ln_last_update_login
, ln_org_Id
, ln_customer_id
, ln_ship_to_org_id
, ln_order_type_id
, lv_subinventory
, NULL -- delivery_id
, lv_vat_exemption_flag
, lv_vat_exemption_type
, ld_vat_exemption_date
, lv_vat_exemption_refno
, 'N' -- shippable_flag
/*, ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
);
INSERT INTO JAI_RGM_INVOICE_GEN_T ( regime_id
, delivery_id
, delivery_date
, order_line_id
, order_number
, 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
, NULL -- delivery_id
, ld_creation_date -- delivery_date, for nonshippable it should be fulfilled date
, ln_order_line_id
, ln_order_number
, NULL -- customer_trx_id
, ln_organization_Id
, NVL(ln_location_id, ln_organization_Id)
, lv_regns_num
, lv_vat_invoice_no
, nvl(lv_vat_inv_gen_status, 'P')
, 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
, ln_customer_id -- party_id
, pr_new.invoice_to_org_id -- party_site_id
, jai_constants.party_type_customer
, ld_creation_date
, ln_created_by
, ld_last_update_date
, ln_last_update_login
, ln_last_updated_by
);
INSERT INTO JAI_RGM_GST_INVOICE_GEN_T ( delivery_id
, delivery_date
, order_line_id
, order_number
, customer_trx_id
, organization_id
, location_id
, gst_invoice_no
, gst_inv_gen_status
, gst_inv_gen_err_message
, gst_acct_status
, gst_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 ( NULL -- delivery_id
, ld_creation_date -- delivery_date, for nonshippable it should be fulfilled date
, ln_order_line_id
, ln_order_number
, NULL -- customer_trx_id
, ln_organization_Id
, NVL(ln_location_id, ln_organization_Id)
, NULL
, 'P'
, NULL
, 'P'
, NULL
, NULL
, NULL
, NULL
, NULL
, ln_customer_id -- party_id
, pr_new.invoice_to_org_id -- party_site_id
, jai_constants.party_type_customer
, ld_creation_date
, ln_created_by
, ld_last_update_date
, ln_last_update_login
, ln_last_updated_by
);
UPDATE JAI_OM_WSH_LINES_ALL
SET quantity = ln_fulfilled_quantity,
tax_amount = ln_tot_tax_amount,
order_line_id = ln_order_line_id,
excise_amount = NULL,
basic_excise_duty_amount = NULL,
add_excise_duty_amount = NULL,
oth_excise_duty_amount = NULL,
last_update_date = ld_last_update_date,
last_updated_by = ln_last_updated_by,
last_update_login = ln_last_update_login,
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
WHERE order_line_id = ln_order_line_id;
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 quantity,
--selling_price,
assessable_value,
--line_amount
excise_exempt_type,
excise_exempt_refno,
vat_reversal_price --Date 14/06/2007 by sacsethi for bug 6072461
FROM JAI_OM_OE_SO_LINES
WHERE line_id = p_line_id;
SELECT quantity,
assessable_value
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = p_line_id;
SELECT org_id,
ROWID,
TRANSACTIONAL_CURR_CODE,
conversion_type_code,
conversion_rate,
CONVERSION_RATE_DATE,
SOLD_TO_ORG_ID,
price_list_id,
NVL(ORDERED_DATE, creation_date)
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = p_header_id;
SELECT NVL(cust_acct_site_id, 0) address_id
FROM HZ_CUST_SITE_USES_ALL A /*Removed ra_site_uses_all for Bug# 4434287 */
WHERE A.site_use_id = p_ship_to_org_id; /* Modified by Ramananda for removal of SQL LITERALs */
SELECT b.operand list_price,
c.product_uom_code list_price_uom_code
FROM JAI_CMN_CUS_ADDRESSES a,
QP_LIST_LINES b,
qp_pricing_attributes c
WHERE a.customer_id = p_customer_id
AND a.address_id = p_address_id
AND a.price_list_id = b.LIST_header_ID
AND c.list_line_id = b.list_line_id
AND c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id)
-- AND c.product_uom_code = p_uom_code --2001/10/09 Anuradha Parthasarathy
AND (b.end_date_active is null
OR
b.end_date_active >= p_ordered_date); /* Modified by Ramananda for removal of SQL LITERALs */
SELECT SUM(a.tax_amount)
FROM JAI_OM_OE_SO_TAXES a,
JAI_CMN_TAXES_ALL b
WHERE a.Header_ID = v_header_id
AND a.line_id = v_line_id
AND b.tax_id = a.tax_id
AND b.tax_type <> 'TDS';
SELECT assessable_value,
quantity
FROM JAI_OM_OE_SO_LINES
WHERE shipment_schedule_line_id = v_line_id; /* Modified by Ramananda for removal of SQL LITERALs */
SELECT COUNT(*)
FROM JAI_OM_OE_SO_LINES
WHERE header_id = v_header_id;
select selling_price , assessable_value
from JAI_OM_OE_SO_LINES
where header_id = pr_new.header_id
and line_id = pr_new.line_id ;
SELECT line_amount, tax_amount, selling_price,
assessable_value, decode(quantity,0,1,quantity) quantity, vat_assessable_value
/*,gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
FROM JAI_OM_OE_SO_LINES
WHERE header_id = p_header_id
AND shipment_schedule_line_id = pr_new.ato_line_id
AND line_id <> p_line_id ;
SELECT
line_id, inventory_item_id
FROM
oe_order_lines_all
WHERE
header_id = pr_new.Header_id
and top_model_line_id = pr_new.top_model_line_id
and item_type_code = 'CONFIG';
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
assessable_value
FROM
JAI_OM_OE_SO_LINES
WHERE
line_id = v_line_id;
jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE' ,
NULL,
v_header_id,
v_line_id,
v_assessable_amount,
v_line_tax_amount,
v_conv_factor,
v_inventory_item_id ,
NVL(v_old_quantity,0),
v_quantity,
v_uom_code ,
NULL ,
NULL ,
NULL ,
NULL,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login ,
ln_vat_assessable_value ,
ln_vat_reversal_price -- Date 14/06/2007 by sacsethi for bug 6072461
/*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
);
|| Added the VAT assessable value in the update to jai_om_oe_so_lines table
*/
UPDATE jai_om_oe_so_lines
SET
quantity = v_quantity ,
unit_code = v_uom_code , --Added by Nagaraj.s for Bug#3402260
selling_price = v_selling_price ,
assessable_value = nvl(v_assessable_value,v_selling_price) ,
vat_assessable_value = nvl(ln_vat_assessable_value,0) ,
/*gst_assessable_value = nvl(ln_gst_assessable_value,0) ,*/--added by peng.zheng for bug 10043656
tax_amount = NVL(v_line_tax_amount,0) ,
line_amount = v_line_amount ,
line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
last_update_date = v_last_update_date ,
last_updated_by = v_last_updated_by ,
last_update_login = v_last_update_login
WHERE
line_id = v_line_id;
jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE' ,
NULL,
v_header_id,
ln_line_id,
v_assessable_amount,
v_line_tax_amount,
v_conv_factor,
ln_inventory_item_id ,
NVL(v_quantity,0),
v_quantity,
v_uom_code ,
NULL ,
NULL ,
NULL ,
NULL,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login ,
ln_vat_assessable_value,
ln_vat_reversal_price
/*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
);
UPDATE JAI_OM_OE_SO_LINES
SET quantity = v_quantity,
unit_code = v_uom_code, --Added by Nagaraj.s for Bug#3402260
selling_price = v_selling_price,
assessable_value = nvl(v_assessable_value,v_selling_price),
vat_assessable_value = nvl(ln_vat_assessable_value,0),
/*gst_assessable_value = nvl(ln_gst_assessable_value,0),*/--added by peng.zheng for bug 10043656
tax_amount = NVL(v_line_tax_amount,0),
line_amount = v_ato_line_amount,
line_tot_amount = v_ato_line_amount + NVL(v_line_tax_amount,0),
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE line_id = ln_line_id;
UPDATE
jai_om_oe_rma_lines
SET
quantity = v_quantity ,
uom = v_uom_code, --bug#8413915
selling_price = v_selling_price ,
assessable_value = v_assessable_value ,
tax_amount = NVL(v_line_tax_amount,0) ,
inventory_item_id = v_inventory_item_id , -- Added by Sanjikum for Bug #4029476, as Item was not getting updated
last_update_date = v_last_update_date ,
last_updated_by = v_last_updated_by ,
last_update_login = v_last_update_login
WHERE
rma_line_id = v_line_id;
SELECT
ship_from_org_id, inventory_item_id
FROM
OE_ORDER_LINES_ALL
WHERE header_id = pn_header_id
AND line_id = pn_line_id;
SELECT COUNT(*)
FROM JAI_OM_WSH_LINES_ALL
WHERE order_line_id = pr_new.reference_line_id;
SELECT
delivery_detail_id
FROM
wsh_delivery_details
WHERE
source_header_id = pr_new.reference_header_id AND
source_line_id = pr_new.reference_line_id AND
inventory_item_id = pr_new.inventory_item_id AND
shipped_quantity IS NOT NULL;
select
ordered_date
from
oe_order_headers_all
where
header_id = pr_new.header_id;
select
confirm_date
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE
wdd.delivery_detail_id = (p_delivery_Detail_id)
AND
wda.delivery_detail_id = wdd.delivery_detail_id
AND
wnd.delivery_id = wda.delivery_id;
SELECT
excise_return_days,
sales_return_days
FROM
JAI_CMN_INVENTORY_ORGS
WHERE
organization_id = pr_new.ship_from_org_id
AND location_id = 0;
SELECT 1
-- FROM JAI_INV_ITM_SETUPS
FROM JAI_RGM_ITM_REGNS -- Modified by Bo Li for bug#9789855 on 30-JUN-2010
WHERE ORGANIZATION_ID = cp_organization_id
AND inventory_item_id = cp_inventory_item_id;
SELECT ship_from_org_id, inventory_item_id
FROM OE_ORDER_LINES_ALL
WHERE header_id = cp_header_id
AND line_id = cp_line_id;
SELECT name
FROM oe_order_sources
WHERE order_source_id = cp_source_document_type_id;
select delivery_detail_id, nvl(allow_excise_credit_flag, 'N') allow_excise_credit_flag , nvl(allow_sales_credit_flag, 'N') allow_sales_credit_flag,
rate_per_unit, excise_duty_rate
from JAI_OM_OE_RMA_LINES
where rma_line_id = cp_rma_line_id;
AND pv_action = jai_constants.inserting -- Added by Bo Li for bug#9789855 on 30-JUN-2010
THEN
OPEN cur_chk_item_dtls (pr_new.SHIP_from_ORG_ID, pr_new.inventory_item_id);
Before inserting a record in the oe_order_lines_all table for a return order, check if the delivery detail_id i.e pr_new.attribute2 is null.
IF yes then
1. pick up the delivery_detail_id from the wsh_delivery_details table for records corresponding to the reference_header_id and
reference_line_id in this table and populate the pr_new.attibute2 dff field
2.Set the pr_new.attribute3 = 'Y' and pr_new.attribute4 = 'Y'
3. Populate the context with the following information
if pr_new.return_context pr_new.Context
---------------------- -------------------
'ORDER' 'Sales Order India'
'INVOICE' 'Invoice India'
'PO' 'Customer PO India'
*/
--added jai_constants.UPDATING for bug#7568194
IF pv_action IN (jai_constants.INSERTING, jai_constants.UPDATING) AND pr_new.reference_header_id IS NOT NULL THEN
OPEN cur_get_delivery_detail_id;
/* If the v_order_source_type is Copy then this trigger should not insert the taxes */
OPEN Get_Order_Source_Type(pr_new.SOURCE_DOCUMENT_TYPE_ID);
pr_new.last_update_date,
pr_new.last_updated_by,
pr_new.last_update_login,
pr_new.source_document_type_id,
pr_new.line_category_code /* Parameter added by Aiyer for the bug #3306419
because the new parameter p_line_category_code has been added
to the existing parameter list of the procedure jai_om_rma_pkg.default_taxes_onto_line
*/
);--2001/10/03 Anuradha Parthasarathy