[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
first Cursor is used to select all the attrib
to be passed to FTE. The second cursor is
is used to select all the included item
parents to process the included items.
we process the included items if they are not
frozen already. We will exclude all the non
eligible lines and prepate the fte input tab
at the same time we will also insert the
elgible lines in the oe_order_pub line table
to be used later.
Now we have line table with all the lines
beginnning with config lines.
The Same Procedure will be used by freight
rating also to prepare the Fte input table.
All non shippable lines not part of ATO
configuration should be marked as not
eligible for freight rate calculations.
This procedure is called from Process_FTE_Actions
API and p_action paramter can not have a NULL value.
Change Record :
+--------------------------------------------------------------*/
PROCEDURE Prepare_Freight_Choices_Input
( p_header_id IN NUMBER
,p_x_fte_source_line_tab IN OUT NOCOPY
FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab
,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
,p_action IN VARCHAR2
,x_config_count OUT NOCOPY /* file.sql.39 change */ NUMBER
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
CURSOR C_SHIP_METHOD_LINES IS
SELECT line_id
,ship_from_org_id
,ship_to_org_id
,sold_to_org_id
,inventory_item_id
,ordered_quantity
,order_quantity_uom
,request_date
,schedule_ship_date
,schedule_arrival_date
,delivery_lead_time
,DECODE(schedule_status_code,NULL,'N',
'SCHEDULED','Y','N') scheduled_flag
,ship_set_id
,arrival_set_id
,ship_model_complete_flag
,ato_line_id
,top_model_line_id
,shipping_method_code
,freight_carrier_code
,freight_terms_code
,intmed_ship_to_org_id
,fob_point_code
,source_type_code
,line_category_code
,item_type_code
,shipped_quantity
,NVL(fulfilled_flag,'N') fulfilled_flag
,open_flag
,nvl(shippable_flag, 'N') shippable_flag
,order_source_id
,orig_sys_document_ref
,orig_sys_line_ref
,orig_sys_shipment_ref
,change_sequence
,source_document_type_id
,source_document_id
,source_document_line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND p_action <> 'R'
ORDER BY top_model_line_id, ato_line_id, sort_order;
SELECT line_id
FROM oe_order_lines_all
WHERE item_type_code IN ('MODEL', 'CLASS', 'KIT')
AND ato_line_id is NULL
AND explosion_date is NULL
AND NVL(fulfilled_flag,'N') <> 'Y'
AND open_flag = 'Y'
AND shipped_quantity IS NULL
AND source_type_code = 'INTERNAL'
AND header_id = p_header_id;
SELECT open_flag,order_category_code,transactional_curr_code,conversion_type_code
INTO l_open_flag,l_order_category_code,l_transactional_curr_code,l_conversion_type_code
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT count(*)
INTO l_config_count
FROM oe_order_lines_all
WHERE item_type_code = 'CONFIG'
AND ato_line_id IS NOT NULL
AND header_id = p_header_id;
select oe_price_adjustments_s.nextval into l_price_adjustment_id
from dual;
x_line_adj_rec.last_update_date := sysdate;
x_line_adj_rec.last_updated_by := 1;
x_line_adj_rec.last_update_login := 1;
select oe_price_adjustments_s.nextval
into l_price_adjustment_id
from dual;
INSERT INTO oe_price_adjustments
(PRICE_ADJUSTMENT_ID
,HEADER_ID
,LINE_ID
,PRICING_PHASE_ID
,LIST_LINE_TYPE_CODE
,LIST_HEADER_ID
,LIST_LINE_ID
,ADJUSTED_AMOUNT
,AUTOMATIC_FLAG
,UPDATED_FLAG
,APPLIED_FLAG
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
)
VALUES
(l_price_adjustment_id
,p_header_id
,NULL
,-1
,'OM_CALLED_CHOOSE_SHIP_METHOD'
,-1*p_header_id
,NULL
,-1
,'N'
,'Y'
,NULL
,sysdate
,1
,sysdate
,1
);
Select Count(*) into l_count from
oe_price_adjustments
where header_id = p_key
and LIST_LINE_TYPE_CODE = 'OM_CALLED_CHOOSE_SHIP_METHOD';
deleted_costs number_type;
g_freight_choices_tbl.DELETE;
IF deleted_costs.EXISTS(l_fte_line_rate_tab(M).source_line_id) THEN
NULL;
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE line_ID = l_fte_line_rate_tab(M).source_line_id
AND CHARGE_TYPE_CODE IN ('FTEPRICE','FTECHARGE')
AND list_line_type_code = 'COST'
AND ESTIMATED_FLAG = 'Y';
deleted_costs(l_fte_line_rate_tab(M).source_line_id) := l_fte_line_rate_tab(M).source_line_id;
END IF; -- if deleted_costs...
OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
deleted_costs.DELETE;
Select sum(nvl(l.ADJUSTMENT_AMOUNT,0)) into q_Adjusted_amount from QP_ldets_v l,QP_preq_lines_tmp q
where l.line_index = q.line_index
and q.line_id = l_line_tbl(K).line_id
and q.line_type_code = 'LINE'
AND nvl(l.automatic_flag,'N') = 'Y'
AND l.list_line_type_code = 'FREIGHT_CHARGE';
Select MEANING into l_meaning from oe_ship_methods_v
where LOOKUP_CODE= l_fte_header_rate_tab(I).ship_method_code
and LOOKUP_TYPE='SHIP_METHOD';
l_line_tbl.DELETE;
deleted_costs number_type;
SELECT opa.price_adjustment_id,ool.line_id,
opa.adjusted_amount, opa.list_line_type_code,
opa.charge_type_code
FROM oe_order_lines_all ool
,oe_price_adjustments opa
WHERE opa.charge_type_code IN ('FTEPRICE','FTECHARGE')
AND list_line_type_code = 'COST' -- For bug 7043225
AND ool.line_id = opa.line_id
AND ool.item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
AND ool.ato_line_id = p_ato_line_id;
SELECT 1 from oe_order_lines_all
WHERE ato_line_id = p_line_id
AND item_type_code = OE_GLOBALS.G_ITEM_CONFIG;
IF deleted_costs.EXISTS(g_fte_line_rate_tab(M).source_line_id) THEN
NULL;
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE line_ID = g_fte_line_rate_tab(M).source_line_id
AND CHARGE_TYPE_CODE IN ('FTEPRICE','FTECHARGE')
AND list_line_type_code = 'COST'
AND ESTIMATED_FLAG = 'Y';
deleted_costs(g_fte_line_rate_tab(M).source_line_id) := g_fte_line_rate_tab(M).source_line_id;
query_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
select count(*) into l_config_line_exists
from oe_order_lines_all
where ato_line_id = query_line_tbl(1).ato_line_id
and item_type_code = 'CONFIG';
IF query_line_tbl(1).operation = OE_GLOBALS.G_OPR_UPDATE
THEN
g_line_tbl(l_index).shipping_method_code :=
query_line_tbl(1).shipping_method_code;
p_operation => OE_GLOBALS.G_OPR_UPDATE);
END IF; -- if deleted_costs...
OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
p_operation => OE_GLOBALS.G_OPR_UPDATE);
DELETE FROM oe_price_adjustments
WHERE price_adjustment_id = l_bulk_adj_rec.price_adjustment_id(i);
--l_line_tbl.operation := OE_GLOBALS.G_OPR_UPDATE;