[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 */,
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 line_amount , tax_amount , selling_price , assessable_value , quantity -- quantity added to the select clause Bug # 2968360
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.
----------------------------------------------------------------------------------------------------------------*/
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.
ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
(
p_party_id => v_customer_id ,
p_party_site_id => v_ship_to_site_use_id ,
p_inventory_item_id => v_inventory_item_id ,
p_uom_code => v_uom_code ,
p_default_price => pr_new.unit_selling_price,
p_ass_value_date => v_date_ordered ,
p_party_type => 'C'
);
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 */
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),
copy_rec.assessable_value, 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,copy_rec.vat_assessable_value, /* added by ssumaith for vat */
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
quantity
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
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
SUM(wdd.shipped_quantity) qty
FROM
wsh_delivery_details wdd
WHERE
wdd.delivery_detail_id = cp_delivery_detail_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 ,
rec_cur_get_picking_lines.quantity ,
rec_cur_get_picking_lines.tax_category_id ,
rec_cur_get_picking_lines.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 ;
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 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 ,
r_get_copy_order_line.assessable_value * (pr_new.ordered_quantity) ,
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 , r_get_copy_order_line.vat_assessable_value
);
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),
vat_assessable_Value = r_get_copy_order_line.vat_assessable_value
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
);
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
);
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
)
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
);
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 = 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;
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
);
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*/
)
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*/
);
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,
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
);
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'));
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 ;
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
);
|| 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) ,
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;
UPDATE
jai_om_oe_rma_lines
SET
quantity = v_quantity ,
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 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 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;
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