[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_source_document_type_id NUMBER,
p_line_category_code OE_ORDER_LINES_ALL.LINE_CATEGORY_CODE%TYPE
)
IS
v_category oe_order_headers_all.order_category_code % TYPE;
SELECT SHIPPABLE_ITEM_FLAG
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = c_inv_item_id
AND ORGANIZATION_ID = c_organization_id;
SELECT 1
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_detail_id = c_delivery_detail_id
OR (SHIPPABLE_FLAG = 'N' AND ORDER_LINE_ID = c_order_line_id);
SELECT requested_quantity_uom FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
select quantity ,service_type_code /* added by ssawant for bug 5879769 */
from JAI_OM_OE_SO_LINES
where line_id = p_new_reference_line_id;
select tax_amount
from JAI_OM_OE_SO_TAXES
where line_id = p_new_reference_line_id
and tax_id = p_tax_id;
select
ordered_date
from
oe_order_headers_all
where
header_id = v_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 vat_return_days
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = v_organization_id
AND location_id = 0;
SELECT 1
FROM jai_regime_tax_types_v
WHERE regime_code = jai_constants.vat_regime
AND tax_type = cp_tax_type;
PROCEDURE rma_insert IS
BEGIN
IF v_delivery_detail_id IS NOT NULL
THEN
v_ord_inv_quantity := p_new_ordered_quantity;
FOR pick_rec IN (SELECT quantity,
tax_category_id
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_detail_id = v_delivery_detail_id)
LOOP
v_quantity := pick_rec.quantity;
FOR duty_rec IN (SELECT rgd.excise_duty_rate,
rgd.rate_per_unit,
rgd.quantity_received
FROM JAI_CMN_RG_23D_TRXS rgd,
JAI_CMN_MATCH_RECEIPTS rm
WHERE rm.ref_line_id = v_delivery_detail_id
AND rgd.register_id = rm.receipt_id)
LOOP
v_excise_total := NVL(v_excise_total, 0) + NVL(duty_rec.excise_duty_rate, 0) *
NVL(duty_rec.quantity_received, 0);
INSERT INTO JAI_OM_OE_RMA_LINES
(rma_line_id,
rma_line_number,
rma_header_id,
rma_number,
delivery_detail_id,
uom,
selling_price,
quantity,
tax_category_id,
tax_amount,
inventory_item_id,
received_flag,
assessable_value,
excise_duty_rate,
rate_per_unit,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
/* Added for DFF Elimination by Ramananda */
allow_excise_credit_flag,
allow_sales_credit_flag,
service_type_code /* added by ssawant for bug 5879769 */
)
VALUES (p_line_id,
p_line_number,
p_header_id,
v_order_number,
v_delivery_detail_id,
P_uom,
p_new_selling_price,
v_ord_inv_quantity,
v_tax_category_id,
NULL,
p_inventory_item_id,
NULL, --received_flag
NULL, --assessable value
v_excise_duty_rate,
v_rate_per_unit,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
pv_allow_excise_flag,
pv_allow_sales_flag,
v_service_type_code /* added by ssawant for bug 5879769 */
);
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,/*Bug 5989740 bduvarag*//*bduvarag for the bug#6071813*/
-- interfaced_flag,
base_tax_amount,
func_tax_amount,
jtc.tax_type ,
precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
precedence_7,
precedence_8,
precedence_9,
precedence_10
FROM JAI_OM_WSH_LINE_TAXES sptl,
JAI_CMN_TAXES_ALL jtc
WHERE delivery_detail_id = v_delivery_detail_id
AND jtc.tax_id = sptl.tax_id)
LOOP
IF tax_line_rec.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', 'TDS', 'CVD')
THEN
v_round_tax := ROUND((v_cor_amount * tax_line_rec.tax_amount),tax_line_rec.rounding_factor);/*Bug 5989740 bduvarag*/
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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
precedence_7,
precedence_8,
precedence_9,
precedence_10)
VALUES (p_line_id,
v_delivery_detail_id,
tax_line_rec.tax_line_no,
tax_line_rec.precedence_1,
tax_line_rec.precedence_2,
tax_line_rec.precedence_3,
tax_line_rec.precedence_4,
tax_line_rec.precedence_5,
tax_line_rec.tax_id,
tax_line_rec.tax_rate,
tax_line_rec.qty_rate,
tax_line_rec.uom,
v_round_tax,
v_round_base,
v_round_func,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login ,
tax_line_rec.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
tax_line_rec.precedence_7,
tax_line_rec.precedence_8,
tax_line_rec.precedence_9,
tax_line_rec.precedence_10
);
UPDATE JAI_OM_OE_RMA_LINES
SET tax_amount = NVL(tax_amount, 0) + v_tax_total
WHERE rma_line_id = p_line_id;
FOR hr_rec IN (SELECT vat_return_days
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_warehouse_id
AND location_id = 0)
LOOP
v_vat_return_days := hr_rec.vat_return_days ;
FOR date_rec IN (SELECT ordered_date
FROM oe_order_headers_all
WHERE header_id = p_header_id)
LOOP
v_date_ordered := date_rec.ordered_date;
FOR pick_rec IN (SELECT tax_category_id
-- added by Allen Yang for bug 9691880 10-May-2010, begin
,creation_date
-- added by Allen Yang for bug 9691880 10-May-2010, end
FROM JAI_OM_WSH_LINES_ALL
WHERE shippable_flag = 'N'
AND order_line_id = p_new_reference_line_id)
LOOP
v_tax_category_id := pick_rec.tax_category_id;
INSERT INTO JAI_OM_OE_RMA_LINES
(rma_line_id,
rma_line_number,
rma_header_id,
rma_number,
delivery_detail_id,
uom,
selling_price,
quantity,
tax_category_id,
tax_amount,
inventory_item_id,
received_flag,
assessable_value,
excise_duty_rate,
rate_per_unit,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
allow_excise_credit_flag,
allow_sales_credit_flag,
service_type_code
)
VALUES (p_line_id,
p_line_number,
p_header_id,
v_order_number,
NULL, -- delivery_detail_id
P_uom,
p_new_selling_price,
p_new_ordered_quantity,
v_tax_category_id,
NULL, -- tax_amount
p_inventory_item_id,
NULL, --received_flag
NULL, --assessable value
NULL, -- excise_duty_rate
NULL, -- rate_per_unit,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
pv_allow_excise_flag,
pv_allow_sales_flag,
v_service_type_code
);
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 = p_new_reference_line_id
AND jtc.tax_id = sptl.tax_id)
LOOP
-- added by Allen Yang for bug 9691880 10-May-2010, begin
lv_check_vat_type_exists := NULL;
INSERT INTO JAI_OM_OE_RMA_TAXES
(rma_line_id,
delivery_detail_id,
tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
tax_id,
tax_rate,
qty_rate,
uom,
tax_amount,
base_tax_amount,
func_tax_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login ,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10)
VALUES (p_line_id,
NULL, -- delivery_detail_id
tax_line_rec.tax_line_no,
tax_line_rec.precedence_1,
tax_line_rec.precedence_2,
tax_line_rec.precedence_3,
tax_line_rec.precedence_4,
tax_line_rec.precedence_5,
tax_line_rec.tax_id,
tax_line_rec.tax_rate,
tax_line_rec.qty_rate,
tax_line_rec.uom,
-- modified by Allen Yang for bug 9691880 10-May-2010, begin
v_round_tax, --tax_line_rec.tax_amount,
v_round_base, --tax_line_rec.base_tax_amount,
v_round_func, --tax_line_rec.func_tax_amount,
-- modified by Allen Yang for bug 9691880 10-May-2010, end
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_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 ......
UPDATE JAI_OM_OE_RMA_LINES
SET tax_amount = NVL(tax_amount, 0) + v_tax_total
WHERE rma_line_id = p_line_id;
END rma_insert;
FOR conf_rec IN (SELECT NVL(wdd.delivery_detail_id, 0) pic
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = TO_NUMBER(pn_delivery_detail_id)
AND (wdd.inventory_item_id IN
(SELECT inventory_item_id
FROM mtl_system_items
WHERE base_item_id = p_inventory_item_id
AND bom_item_type = 4) OR
wdd.inventory_item_id = p_inventory_item_id)
AND wdd.shipped_quantity IS NOT NULL)
LOOP
v_conf := conf_rec.pic;
FOR conf_rec IN (SELECT NVL(wdd.delivery_detail_id, 0) pic
FROM wsh_delivery_details wdd,
ra_customer_trx_lines_all rctla
WHERE wdd.delivery_detail_id = TO_NUMBER(pn_delivery_detail_id)
AND rctla.customer_trx_line_id = p_new_ref_customer_trx_line_id
AND rctla.inventory_item_id = p_inventory_item_id
AND wdd.delivery_detail_id = TO_NUMBER(rctla.interface_line_attribute3)
AND (wdd.inventory_item_id IN
(SELECT inventory_item_id
FROM mtl_system_items
WHERE base_item_id = p_inventory_item_id
AND bom_item_type = 4) OR
wdd.inventory_item_id = p_inventory_item_id)
AND wdd.shipped_quantity IS NOT NULL)
LOOP
v_conf := conf_rec.pic;
FOR hr_rec IN (SELECT excise_return_days,
sales_return_days,
vat_return_days , -- added, Harshita for bug#4245062
nvl(manufacturing,'N') manufacturing, --Added Manufacturing and Trading by Nagaraj.s for bug3113027
nvl(trading,'N') trading
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_warehouse_id
AND location_id = 0)
LOOP
v_excise_return_days := hr_rec.excise_return_days;
FOR date_rec IN (SELECT ordered_date
FROM oe_order_headers_all
WHERE header_id = p_header_id)
LOOP
v_date_ordered := date_rec.ordered_date;
FOR pick_rec IN (SELECT SUM(wdd.shipped_quantity) qty
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = v_delivery_detail_id
AND wdd.inventory_item_id = p_inventory_item_id)
LOOP
v_shipped_quantity := pick_rec.qty;
PROCEDURE delete_data IS
BEGIN
DELETE FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = p_line_id;
DELETE FROM JAI_OM_OE_RMA_TAXES
WHERE rma_line_id = p_line_id;
END delete_data;
FOR order_rec IN(SELECT order_category_code,
order_number,
ROWID
FROM oe_order_headers_all
WHERE header_id = P_header_id)
LOOP
v_category := order_rec.order_category_code;
FOR pick_lrec IN (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 = TO_NUMBER(pn_delivery_detail_id)
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id)
LOOP
v_delivery_detail_id := pick_lrec.delivery_detail_id;
delete_data;
rma_insert;
FOR rma_rec IN (SELECT rma_line_id
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = p_line_id)
LOOP
v_test_id := rma_rec.rma_line_id;
delete_data;
rma_insert;
inserting
and (
v_chk_form IS NOT NULL
OR (
p_item_type_code = 'STANDARD' AND
p_serviced_quantity IS NULL AND
p_new_reference_line_id IS NOT NULL
)
)
then
-- modified by Allen Yang for bug 9666476 28-apr-2010, begin
--check_dff;
rma_insert;
SELECT
NVL(quantity, 0) quantity
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = p_rma_line_id ;
For tax_line_rec IN (SELECT rtl.tax_line_no,
rtl.precedence_1,
rtl.precedence_2,
rtl.precedence_3,
rtl.precedence_4,
rtl.precedence_5,
rtl.tax_id,
rtl.tax_rate,
rtl.qty_rate,
rtl.uom,
rtl.tax_amount,
jtc.tax_type,
NVL(jtc.mod_cr_percentage, 0) modcp,
NVL(jtc.rounding_factor, 0) rounding_factor ,
rtl.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
rtl.precedence_7,
rtl.precedence_8,
rtl.precedence_9,
rtl.precedence_10
FROM JAI_OM_OE_RMA_TAXES rtl,
JAI_CMN_TAXES_ALL jtc
WHERE rtl.rma_line_id = p_rma_line_id
AND jtc.tax_id = rtl.tax_id)
LOOP
ln_rma_amt := 0 ;