The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(nvl(ROUND(
DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',-1,1) *
DECODE(P.LINE_ID, NULL,
P.OPERAND,
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
DECODE(L.ORDERED_QUANTITY,0,0,NULL,NULL,P.OPERAND),
L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
,OE_ORDER_UTIL.G_Precision),0))
INTO l_charge_amount
FROM OE_PRICE_ADJUSTMENTS P,
OE_ORDER_LINES_ALL L
WHERE P.HEADER_ID = p_header_id
AND P.LINE_ID = L.LINE_ID(+)
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND L.charge_periodicity_code(+) IS NULL -- added for recurring charge
AND P.APPLIED_FLAG = 'Y';
SELECT SUM(CHARGE_AMOUNT)
INTO l_charge_amount
FROM OE_CHARGE_LINES_V
WHERE header_id = p_header_id;
SELECT SUM(ROUND(
DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',-P.OPERAND,P.OPERAND)
,OE_ORDER_UTIL.G_Precision)
)
INTO l_charge_amount
FROM OE_PRICE_ADJUSTMENTS P
WHERE P.HEADER_ID = p_header_id
AND P.LINE_ID IS NULL
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y';
SELECT SUM(CHARGE_AMOUNT)
INTO l_charge_amount
FROM OE_CHARGE_LINES_V
WHERE header_id = p_header_id
AND line_id IS NULL;
SELECT SUM(ROUND(
DECODE(P.CREDIT_OR_CHARGE_FLAG,'C', -1, 1) *
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
(L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
,OE_ORDER_UTIL.G_Precision)
)
INTO l_charge_amount
FROM OE_PRICE_ADJUSTMENTS P,
OE_ORDER_LINES_ALL L
WHERE P.HEADER_ID = p_header_id
AND P.LINE_ID = p_line_id
AND P.LINE_ID = L.LINE_ID
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y';
SELECT SUM(CHARGE_AMOUNT)
INTO l_charge_amount
FROM OE_CHARGE_LINES_V
WHERE header_id = p_header_id
AND line_id = p_line_id;
SELECT SUM(ADJUSTED_AMOUNT)
INTO l_cost_amount
FROM OE_PRICE_ADJUSTMENTS_V
WHERE LINE_ID = l_line_rec.line_id
AND LIST_LINE_TYPE_CODE = 'COST'
AND CHARGE_TYPE_CODE = p_cost_type_code;
SELECT DISTINCT CHARGE_TYPE_CODE
FROM OE_PRICE_ADJUSTMENTS_V
WHERE LINE_ID = p_line_id
AND LIST_LINE_TYPE_CODE = 'COST';
(OE_GLOBALS.G_OPR_UPDATE, OE_GLOBALS.G_OPR_CREATE)
AND l_line_adj_tbl(l_charge_index).line_id =
p_line_tbl(l_line_index).line_id
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'THE SELECTED CHARGE TYPE IS ' || L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .CHARGE_TYPE_CODE ) ;
oe_debug_pub.add( 'THE SELECTED OPERATION IS ' || L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .OPERATION ) ;
oe_debug_pub.add( 'THE SELECTED ADJUSTED_AMOUNT IS ' ||TO_CHAR ( L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .ADJUSTED_AMOUNT ) ) ;
(OE_GLOBALS.G_OPR_UPDATE, OE_GLOBALS.G_OPR_CREATE)
AND l_line_adj_tbl(l_tmp_index).list_line_type_code
= 'FREIGHT_CHARGE'
AND NVL(l_line_adj_tbl(l_tmp_index).applied_flag,'N') = 'Y'
AND l_line_adj_tbl(l_charge_index).charge_type_code
= l_line_adj_tbl(l_tmp_index).charge_type_code
AND NVL(l_line_adj_tbl(l_charge_index).charge_subtype_code,'SUB') =
NVL(l_line_adj_tbl(l_tmp_index).charge_subtype_code,'SUB')
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'THE MATCHING CHARGE TYPE IS ' || L_LINE_ADJ_TBL ( L_TMP_INDEX ) .CHARGE_TYPE_CODE ) ;
OE_GLOBALS.G_OPR_UPDATE
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'THE SELECTED OPERATION IS SET TO DELETE' ) ;
OE_GLOBALS.G_OPR_DELETE;
oe_debug_pub.add( 'THE SELECTED OPERATION IS SET TO NONE' ) ;
OE_GLOBALS.G_OPR_UPDATE
AND l_line_adj_tbl(l_charge_index).price_adjustment_id =
l_line_adj_att_tbl(l_att_index).price_adjustment_id
THEN
l_line_adj_att_tbl(l_att_index).operation :=
OE_GLOBALS.G_OPR_DELETE;
l_line_adj_tbl.delete(l_charge_index);
(OE_GLOBALS.G_OPR_UPDATE, OE_GLOBALS.G_OPR_CREATE)
AND l_Header_adj_tbl(l_charge_index).Header_id = l_header_id
AND (l_Header_adj_tbl(l_charge_index).line_id IS NULL OR
l_Header_adj_tbl(l_charge_index).line_id = FND_API.G_MISS_NUM)
THEN
l_tmp_index := l_Header_adj_tbl.FIRST;
(OE_GLOBALS.G_OPR_UPDATE, OE_GLOBALS.G_OPR_CREATE)
AND l_Header_adj_tbl(l_tmp_index).list_Line_type_code
= 'FREIGHT_CHARGE'
AND NVL(l_Header_adj_tbl(l_tmp_index).applied_flag,'N') = 'Y'
AND l_Header_adj_tbl(l_charge_index).charge_type_code
= l_Header_adj_tbl(l_tmp_index).charge_type_code
AND NVL(l_Header_adj_tbl(l_charge_index).charge_subtype_code,'SUB')
= NVL(l_Header_adj_tbl(l_tmp_index).charge_subtype_code,'SUB')
THEN
IF l_Header_adj_tbl(l_tmp_index).adjusted_amount >=
l_Header_adj_tbl(l_charge_index).adjusted_amount
THEN
IF l_Header_adj_tbl(l_charge_index).operation =
OE_GLOBALS.G_OPR_UPDATE
THEN
l_Header_adj_tbl(l_charge_index).operation :=
OE_GLOBALS.G_OPR_DELETE;
OE_GLOBALS.G_OPR_UPDATE
AND l_Header_adj_tbl(l_charge_index).price_adjustment_id =
l_Header_adj_att_tbl(l_att_index).price_adjustment_id
THEN
l_Header_adj_att_tbl(l_att_index).operation :=
OE_GLOBALS.G_OPR_DELETE;
select b.list_header_id,
b.list_line_id,
b.list_line_type_code,
b.start_date_active,
b.end_date_active,
b.modifier_level_code,
b.pricing_phase_id,
b.incompatibility_grp_code,
b.price_break_type_code,
b.operand,
b.arithmetic_operator,
b.qualification_ind,
b.product_precedence
from qp_list_headers_vl a,
qp_list_lines b
where a.name = p_header_name
and a.list_header_id = b.list_header_id;
select b.list_header_id,
b.list_line_id,
b.list_line_type_code,
b.start_date_active,
b.end_date_active,
b.modifier_level_code,
b.pricing_phase_id,
b.incompatibility_grp_code,
b.price_break_type_code,
b.operand,
b.arithmetic_operator,
b.qualification_ind,
b.product_precedence
From qp_list_lines b
where list_line_id = p_list_line_id;
select list_line_id
, list_header_id
, pricing_phase_id
, product_attribute_context
, product_attribute
, product_attr_value
, product_uom_code
, comparison_operator_code
, pricing_attribute_context
, pricing_attribute
, pricing_attr_value_from
, pricing_attr_value_to
, attribute_grouping_no
, qualification_ind
, excluder_flag
from qp_pricing_attributes
where list_line_id = p_list_line_id;
/*select dl.delivery_id,
pa.line_id,
pa.cost_id,
pa.list_line_type_code,
pa.adjusted_amount,
pa.operand
from oe_price_adjustments pa,
wsh_delivery_details dd,
wsh_delivery_assignments da,
wsh_new_deliveries dl
where dl.name = 'delivery_name'
and dl.delivery_id = da.delivery_id
and da.delivery_detail_id = dd.delivery_detail_id
and dd.source_code = 'OE'
and dd.source_line_id = pa.line_id
and pa.list_line_type_code = 'COST'; */
Select CHARGE_TYPE_CODE,Adjusted_amount
From oe_price_adjustments
Where line_id = l_line_rec.line_id
and list_line_type_code = 'COST';
Select list_header_id,
pricing_phase_id
Into l_list_header_id,l_pricing_phase_id
from qp_list_header_phases
where list_header_id = l_list_line_info.list_header_id;
Select list_line_id into l_dummy
From qp_qualifiers
Where list_header_id = l_list_line_info.list_header_id
and nvl(list_line_id,-1) = l_list_line_id
and rownum = 1;
select a.freeze_override_flag
into l_freeze_override_flag
from qp_pricing_phases a, qp_event_phases b
where a.pricing_phase_id = b.pricing_phase_id
and b.pricing_event_code='SHIP'
and a.pricing_phase_id =l_list_line_info.pricing_phase_id;
SELECT SUM(ADJUSTED_AMOUNT)
INTO l_cost_amount
FROM OE_PRICE_ADJUSTMENTS_V
WHERE LINE_ID = l_line_rec.line_id
AND LIST_LINE_TYPE_CODE = 'COST'
AND CHARGE_TYPE_CODE = l_cost_type_code;
oe_debug_pub.add( ' COST RECORD INSERTED WITH VALUE:'||L_COST_AMOUNT ) ;
SELECT SUM(ROUND(
DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
DECODE(L.ORDERED_QUANTITY,0,0,(-P.OPERAND)),
(-L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0))),
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
(L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
)
,OE_ORDER_UTIL.G_Precision)
)
INTO l_charge_amount
FROM OE_PRICE_ADJUSTMENTS P,
OE_ORDER_LINES_ALL L
WHERE P.HEADER_ID = p_header_id
AND P.LINE_ID = L.LINE_ID(+)
AND nvl(L.CHARGE_PERIODICITY_CODE,'ONE') = p_charge_periodicity_code
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y';