[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
NVL(Location_id,0),
trading,
NVL(bonded,'N') bonded
FROM
JAI_INV_SUBINV_DTLS
WHERE
Sub_Inventory_Name = v_subinventory AND
organization_id = v_organization_id;
select exc_shpt_wo_exc_tax_flag
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_rec_new.ship_from_org_id
and location_id = 0;
SELECT
A.register_code
FROM
JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
WHERE
a.organization_id = p_organization_id AND
a.location_id = p_location_id AND
a.register_id = b.register_id AND
b.order_flag = 'Y' AND
b.order_type_id = p_order_type_id;
SELECT
excise_flag ,
modvat_flag ,
nvl(container_item_flag,'N'),
nvl(inventory_item_flag,'N')
FROM
mtl_system_items msi,
JAI_INV_ITM_SETUPS jmsi
WHERE msi.organization_id = jmsi.organization_id
AND msi.inventory_item_id = jmsi.inventory_item_id
AND jmsi.inventory_item_id = v_Inventory_Item_Id
AND jmsi.organization_id = v_organization_id;
SELECT
tax_amount
FROM
JAI_OM_OE_SO_LINES
WHERE
line_id = p_rec_new.line_id AND
header_id = p_rec_new.header_id;
SELECT
nvl(sum(so_tax.tax_amount),0)
FROM
JAI_OM_OE_SO_TAXES so_tax
, jai_cmn_taxes_all tax
WHERE
line_id = p_rec_new.line_id AND
header_id = p_rec_new.header_id
AND so_tax.tax_id = tax.tax_id
AND NVL(tax.inclusive_tax_flag,'N') = 'N' ;
SELECT
a.excise_exempt_type ,
a.line_number ,
a.shipment_line_number ,
quantity
FROM
JAI_OM_OE_SO_LINES a
WHERE
a.line_id = p_rec_new.line_id AND
a.header_id = p_rec_new.header_id ;
SELECT 1
FROM JAI_OM_OE_SO_TAXES jstl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v tax_types
WHERE jstl.line_id = cp_line_id
AND jstl.header_id = cp_header_id
AND jtc.tax_id = jstl.tax_id
AND jtc.tax_type = tax_types.tax_type
AND tax_types.regime_code = jai_constants.vat_regime;
SELECT 1
FROM JAI_OM_OE_SO_TAXES jstl ,
JAI_CMN_TAXES_ALL jtc
WHERE jstl.line_id = cp_line_id
AND jstl.header_id = cp_header_id
AND jtc.tax_id = jstl.tax_id
AND jtc.tax_type in ( jai_constants.tax_type_excise,
jai_constants.tax_type_exc_additional,
jai_constants.tax_type_exc_other,
jai_constants.tax_type_exc_edu_cess,
jai_constants.tax_type_sh_exc_edu_cess);
SELECT 1
FROM jai_rgm_parties rgmpt,
JAI_RGM_DEFINITIONS rgms
WHERE rgmpt.regime_id = rgms.regime_id
AND rgmpt.location_id = cp_location_id
AND rgmpt.organization_id = cp_organization_id
AND rgms.regime_code = jai_constants.vat_regime;
SELECT
*
FROM
oe_order_headers_all
WHERE
header_id = cp_header_id ;
SELECT
oel.ato_line_id
FROM
oe_order_headers_all oeh,
oe_order_lines_all oel
WHERE
oeh.header_id = p_rec_new.header_id
AND oeh.header_id = oel.header_id
AND item_type_code = 'CONFIG' ;
SELECT
oel.inventory_item_id
FROM
oe_order_lines_all oel
WHERE
oel.line_id = cp_ato_line_id
AND item_type_code = 'MODEL' ;
SELECT COUNT(1)
FROM JAI_OM_OE_SO_TAXES
WHERE header_id = p_header_id
AND line_id = p_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,
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_amount,
base_tax_amount,
func_tax_amount,
tax_category_id
FROM JAI_OM_OE_SO_TAXES
WHERE header_id = p_header_id
AND line_id = p_line_id;
SELECT COUNT(*)
INTO c_source_line_id
FROM JAI_OM_OE_SO_LINES
WHERE LINE_ID = pr_order_line.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,
excise_exempt_type,
excise_exempt_refno,
excise_exempt_date,
vat_exemption_flag,
vat_exemption_type,
vat_exemption_date,
vat_exemption_refno,
vat_assessable_value,
vat_reversal_price,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
service_type_code,
ship_to_org_id)
VALUES
(pr_order_line.line_number,
pr_order_line.line_id,
pr_order_line.header_id,
pr_order_line.inventory_item_id,
pr_copy.unit_code,
pr_order_line.ordered_quantity,
pr_copy.tax_category_id,
'Y',
pr_order_line.unit_selling_price,
nvl(pr_order_line.unit_selling_price * pr_order_line.ordered_quantity, 0),
pr_copy.assessable_value,
pr_copy.tax_amount,
pr_copy.line_tot_amount,
pr_order_line.shipment_number,
pr_copy.excise_exempt_type,
pr_copy.excise_exempt_refno,
pr_copy.excise_exempt_date,
pr_copy.vat_exemption_flag,
pr_copy.vat_exemption_type,
pr_copy.vat_exemption_date,
pr_copy.vat_exemption_refno,
pr_copy.vat_assessable_value,
nvl(pr_copy.vat_reversal_price, 0) * NVL(pr_order_line.ordered_quantity, 0),
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
pr_copy.service_type_code,
pr_order_line.ship_to_org_id);
SELECT COUNT(1)
INTO v_tax_line_count
FROM JAI_OM_OE_SO_TAXES
WHERE line_id = pr_order_line.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,
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)
VALUES
(pr_order_line.header_id,
pr_order_line.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,
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,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
rec.tax_category_id);
jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE',
NULL,
pr_order_line.header_id,
pr_order_line.line_id,
r_get_copy_order_line.assessable_value *
(pr_order_line.ordered_quantity),
r_get_copy_order_line.line_amount,
v_converted_rate,
pr_order_line.inventory_item_id,
pr_order_line.ordered_quantity,
pr_order_line.ordered_quantity,
pr_order_line.pricing_quantity_uom,
NULL,
NULL,
NULL,
NULL,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.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_order_line.header_id
and line_id = pr_order_line.line_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 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,
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;
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(pr_order_line.SOURCE_DOCUMENT_ID,
pr_order_line.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,
ship_to_org_id)
VALUES
(pr_order_line.line_number,
pr_order_line.line_id,
pr_order_line.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_order_line.shipment_number,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
rec_cur_get_rma_entry_lines.service_type_code,
pr_order_line.ship_to_org_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,
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
(pr_order_line.header_id,
pr_order_line.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,
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,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login);
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.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 = pr_order_line.last_update_date,
last_updated_by = pr_order_line.last_updated_by,
last_update_login = pr_order_line.last_update_login
WHERE header_id = pr_order_line.header_id
AND line_id = pr_order_line.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 = pr_order_line.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,
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);
/*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 pl.order_header_id = p_source_document_id
AND pl.order_line_id = p_source_document_line_id;*/
SELECT quantity
FROM JAI_OM_WSH_LINES_ALL
WHERE order_header_id = p_source_document_id
AND 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 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_order_line.ship_from_org_id
AND location_id = 0;
SELECT ordered_date
FROM oe_order_headers_all
WHERE header_id = pr_order_line.header_id;
SELECT CREATION_DATE
FROM JAI_OM_WSH_LINES_ALL
WHERE ORDER_LINE_ID = pr_order_line.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_order_line.inventory_item_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,
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,
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;
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,
ship_to_org_id)
VALUES
(pr_order_line.line_number,
pr_order_line.line_id,
pr_order_line.header_id,
p_order_number,
rec_cur_get_picking_lines.inventory_item_id,
v_rma_quantity_uom,
pr_order_line.ordered_quantity,
rec_cur_get_picking_lines.tax_category_id,
pr_order_line.unit_selling_price,
rec_cur_get_picking_lines.tax_amount,
rec_cur_get_picking_lines.delivery_detail_id,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
v_service_type_code,
pr_order_line.ship_to_org_id);
|| Following code copied from internal procedure rma_insert of Procedure JA_IN_RMA_MAINTAIN (version 115.5)
*/
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
(pr_order_line.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,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login);
UPDATE JAI_OM_OE_RMA_LINES
SET tax_amount = v_tax_total
WHERE rma_line_id = pr_order_line.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 =
pr_order_line.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
(pr_order_line.line_id,
NULL,
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,
v_round_base,
v_round_func,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.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 ......
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
(pr_order_line.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,
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,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login);
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;
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 = pr_order_line.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,
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);
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 pl.order_header_id = p_source_document_id
AND pl.order_line_id = p_source_document_line_id;*/
SELECT quantity
FROM JAI_OM_WSH_LINES_ALL
WHERE order_header_id = p_source_document_id
AND 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 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_order_line.ship_from_org_id
AND location_id = 0;
SELECT ordered_date
FROM oe_order_headers_all
WHERE header_id = pr_order_line.header_id;
SELECT CREATION_DATE
FROM JAI_OM_WSH_LINES_ALL
WHERE ORDER_LINE_ID = pr_order_line.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_order_line.inventory_item_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,
ship_to_org_id)
values
(pr_order_line.line_id,
pr_order_line.line_number,
pr_order_line.header_id,
lv_rma_number,
null,
pr_order_line.order_quantity_uom,
pr_order_line.unit_selling_price,
pr_order_line.ordered_quantity,
cv_rma_details.tax_category_id,
(cv_rma_details.tax_amount),
pr_order_line.inventory_item_id,
cv_rma_details.received_flag,
cv_rma_details.assessable_value,
sysdate,
pr_order_line.created_by,
sysdate,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
cv_rma_details.excise_duty_rate,
cv_rma_details.rate_per_unit,
null,
pr_order_line.ship_to_org_id);
FOR cv_rma_taxes in (select *
from JAI_OM_OE_RMA_TAXES
where rma_line_id =
pr_order_line.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,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10)
values
(pr_order_line.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_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_Date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
cv_rma_taxes.delivery_detail_id,
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);
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,
ship_to_org_id)
VALUES
(pr_order_line.line_number,
pr_order_line.line_id,
pr_order_line.header_id,
p_order_number,
rec_cur_get_picking_lines.inventory_item_id,
v_rma_quantity_uom,
pr_order_line.ordered_quantity,
rec_cur_get_picking_lines.tax_category_id,
pr_order_line.unit_selling_price,
rec_cur_get_picking_lines.tax_amount,
rec_cur_get_picking_lines.delivery_detail_id,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
v_service_type_code,
pr_order_line.ship_to_org_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
(pr_order_line.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,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login);
UPDATE JAI_OM_OE_RMA_LINES
SET tax_amount = v_tax_total
WHERE rma_line_id = pr_order_line.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 =
pr_order_line.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
(pr_order_line.line_id,
NULL,
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,
v_round_base,
v_round_func,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.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 order_category_code
FROM oe_order_headers_all
WHERE header_id = p_source_document_id;
SELECT order_source_id
FROM oe_order_headers_all
WHERE header_id = pr_order_line.header_id;
SELECT COUNT(1)
FROM JAI_OM_OE_RMA_TAXES
WHERE rma_line_id = pr_order_line.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_LINES
WHERE rma_line_id = pr_order_line.line_id;
SELECT COUNT(1)
FROM JAI_OM_OE_SO_LINES
WHERE line_id = p_line_id;
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 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
AND NVL(b.inclusive_tax_flag, 'N') = 'N';
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.
*/
IF pr_order_line.LINE_CATEGORY_CODE = 'RETURN' THEN
IF pr_order_line.return_context IS NULL THEN
v_transaction_name := 'RMA_LEGACY_INSERT';
(v_transaction_name = 'RMA_LEGACY_INSERT')) THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,
G_MODULE_NAME || v_api_name,
' 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 = pr_order_line.line_id;
DELETE JAI_OM_OE_RMA_TAXES WHERE RMA_LINE_ID = pr_order_line.line_id;
DELETE JAI_OM_OE_SO_LINES WHERE LINE_ID = pr_order_line.line_id;
DELETE JAI_OM_OE_SO_TAXES WHERE Line_ID = pr_order_line.line_id;
IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,
G_MODULE_NAME || v_api_name,
' inside IF OF RMA_LEGACY_INSERT ');
' inside ELSE OF RMA_LEGACY_INSERT ');
p_last_update_date => pr_order_line.last_update_date,
p_last_updated_by => pr_order_line.last_updated_by,
p_last_update_login => pr_order_line.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(pr_order_line.line_id);
IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,
G_MODULE_NAME || v_api_name,
'BEFORE opening return_tax_amount_Cur ');
IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,
G_MODULE_NAME || v_api_name,
' 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,
ship_to_org_id)
VALUES
(pr_order_line.line_number,
pr_order_line.line_id,
pr_order_line.header_id,
pr_header_info.order_number,
pr_order_line.inventory_item_id,
pr_order_line.ORDER_QUANTITY_UOM,
pr_order_line.ordered_quantity,
v_tax_category_id,
pr_order_line.UNIT_SELLING_PRICE,
v_line_tax_amount,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
v_assessable_value,
pr_order_line.ship_to_org_id);
/* else if of v_transaction_name = 'RMA_LEGACY_INSERT' */
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,
G_MODULE_NAME || v_api_name,
' 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 = pr_order_line.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,
ship_to_org_id)
VALUES
(pr_order_line.line_number,
pr_order_line.line_id,
pr_order_line.header_id,
pr_order_line.SHIPMENT_NUMBER,
pr_order_line.ato_line_id, -- uncommented by sriram - for lmw ato issue
pr_order_line.inventory_item_id,
pr_order_line.ORDER_QUANTITY_UOM,
'Y',
pr_order_line.ordered_quantity,
v_tax_category_id,
pr_order_line.UNIT_SELLING_PRICE,
v_assessable_value,
v_line_amount,
v_line_tax_amount,
(v_line_amount + v_line_tax_amount),
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
ln_vat_assessable_value,
v_service_type_code,
pr_order_line.ship_to_org_id);
select *
from JAI_OM_OE_SO_TAXES
where header_id = pr_order_line.header_id
and line_id = pr_order_line.ato_line_id;
select tax_category_id, inventory_item_id, line_amount
from JAI_OM_OE_SO_LINES
where header_id = pr_order_line.header_id
and line_id = pr_order_line.ato_line_id;
select line_amount,
tax_amount,
selling_price,
assessable_value,
quantity,
vat_assessable_value
from JAI_OM_OE_SO_LINES
where header_id = pr_order_line.header_id
and shipment_schedule_line_id = pr_order_line.ato_line_id;
update JAI_OM_OE_SO_LINES
set line_amount = v_ato_line_amount,
assessable_value = v_ato_assessable_value,
vat_assessable_value = v_ato_vat_assessable_value,
selling_price = v_ato_selling_price
where header_id = pr_order_line.header_id
and line_id = pr_order_line.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,
precedence_7,
precedence_8,
precedence_9,
precedence_10)
Values
(model_rec.tax_line_no,
pr_order_line.line_id,
pr_order_line.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,
model_rec.precedence_6,
model_rec.precedence_7,
model_rec.precedence_8,
model_rec.precedence_9,
model_rec.precedence_10);
jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE',
NULL,
pr_order_line.header_id,
pr_order_line.line_id,
v_ato_assessable_value,
v_ato_line_amount,
v_converted_rate,
pr_order_line.inventory_item_id,
pr_order_line.ordered_quantity,
pr_order_line.ordered_quantity,
pr_order_line.pricing_quantity_uom,
NULL,
NULL,
NULL,
NULL,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
v_ato_vat_assessable_value);
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_order_line.header_id
and line_id = pr_order_line.line_id;
SELECT lc_flag
FROM JAI_OM_OE_SO_LINES
WHERE line_id = pr_order_line.split_from_line_id;
SELECT COUNT(1)
FROM JAI_OM_OE_SO_TAXES
WHERE header_id = p_header_id
AND line_id = p_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,
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_amount,
base_tax_amount,
func_tax_amount,
tax_category_id
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')
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
AND NVL(b.inclusive_tax_flag, 'N') = 'N';
SELECT COUNT(*)
INTO c_source_line_id
FROM JAI_OM_OE_SO_LINES
WHERE LINE_ID = pr_order_line.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,
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,
EXCISE_EXEMPT_TYPE,
EXCISE_EXEMPT_REFNO,
EXCISE_EXEMPT_DATE,
lc_flag,
VAT_EXEMPTION_FLAG,
VAT_EXEMPTION_TYPE,
VAT_EXEMPTION_DATE,
VAT_EXEMPTION_REFNO,
VAT_ASSESSABLE_VALUE,
VAT_REVERSAL_PRICE,
service_type_code,
ship_to_org_id)
VALUES
(pr_order_line.line_number,
pr_order_line.line_id,
pr_order_line.header_id,
pr_order_line.SPLIT_FROM_LINE_ID,
pr_order_line.SHIPMENT_NUMBER,
pr_order_line.ato_line_id,
pr_order_line.inventory_item_id,
pr_order_line.ORDER_QUANTITY_UOM,
'Y',
pr_order_line.ordered_quantity,
copy_rec.tax_category_id,
pr_order_line.UNIT_SELLING_PRICE,
copy_rec.assessable_value,
v_line_new_amount,
0,
0,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
Copy_rec.EXCISE_EXEMPT_TYPE,
copy_rec.EXCISE_EXEMPT_REFNO,
copy_rec.EXCISE_EXEMPT_DATE,
l_lc_flag,
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,
(copy_rec.vat_reversal_price / copy_rec.quantity) *
(v_line_quantity),
copy_rec.service_type_code,
pr_order_line.ship_to_org_id);
SELECT COUNT(1)
INTO v_tax_line_count
FROM JAI_OM_OE_SO_TAXES
WHERE line_id = pr_order_line.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,
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)
VALUES
(pr_order_line.header_id,
pr_order_line.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,
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,
pr_order_line.creation_date,
pr_order_line.created_by,
pr_order_line.last_update_date,
pr_order_line.last_updated_by,
pr_order_line.last_update_login,
rec.tax_category_id);
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 = pr_order_line.header_id
and line_id = pr_order_line.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 name
FROM oe_order_sources
WHERE order_source_id = p_source_document_type_id;
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);
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;
REM NAME UPDATE_TAX
REM
REM DESCRIPTION Called from procdure JAI_TAX_PROCESSING_PKG
REM
REM NOTES Come from procdure JAI_OE_OLA_TRIGGER_PKG.ARU_T1
REM
REM +======================================================================+
*/
PROCEDURE UPDATE_TAX(pr_old t_rec%type,
pr_new t_rec%type,
pv_action varchar2,
pv_return_code out nocopy varchar2,
pv_return_message out nocopy varchar2) IS
v_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TAX';
v_last_update_date DATE;
v_last_updated_by NUMBER;
v_last_update_login NUMBER;
SELECT quantity,
assessable_value,
excise_exempt_type,
excise_exempt_refno,
vat_reversal_price
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
WHERE A.site_use_id = p_ship_to_org_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
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);
UPDATE jai_om_oe_so_lines
SET quantity = v_quantity,
unit_code = v_uom_code,
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;
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);
UPDATE JAI_OM_OE_SO_LINES
SET quantity = v_quantity,
unit_code = v_uom_code,
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_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,
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,
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;
Pv_return_message := 'Encountered an error in JAI_OM_TAX_PROCESSING_PKG.UPDATE_TAX ' ||
substr(sqlerrm, 1, 1900);
END UPDATE_TAX;
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_RGM_ITM_REGNS
WHERE ORGANIZATION_ID = cp_organization_id
AND inventory_item_id = cp_inventory_item_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;
pv_action = jai_constants.inserting 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'
*/
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
);