[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LDET_UPDATED_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
G_PASS_LINE_TBL.delete;
Procedure Insert_Manual_Adj(px_Line_Adj_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Adj_Tbl_Type
)
IS
i pls_integer;
select
lhdr.LIST_TYPE_CODE,
ql.PRICING_GROUP_SEQUENCE,
ql.PRICING_PHASE_ID,
ql.ARITHMETIC_OPERATOR,
ql.PRODUCT_PRECEDENCE,
ql.INCOMPATIBILITY_GRP_CODE,
ql.LIST_LINE_NO,
ql.ACCRUAL_FLAG,
ql.ACCRUAL_CONVERSION_RATE,
ql.ESTIM_ACCRUAL_RATE,
ql.CHARGE_TYPE_CODE,
ql.CHARGE_SUBTYPE_CODE,
ql.OVERRIDE_FLAG
INTO
G_LDET_LIST_TYPE_CODE(j),
G_LDET_PRICING_GROUP_SEQUENCE(j),
G_LDET_PRICING_PHASE_ID(j),
G_LDET_OPERATOR(j),
G_LDET_PRODUCT_PRECEDENCE(j),
G_LDET_INCOM_GRP_CODE(j),
G_LDET_LIST_LINE_NO(j),
G_LDET_ACCRUAL_FLAG(j),
G_LDET_ACCRUAL_CONVERSION_RATE(j),
G_LDET_ESTIM_ACCRUAL_RATE(j),
G_LDET_CHARGE_TYPE_CODE(j),
G_LDET_CHARGE_SUBTYPE_CODE(j),
G_LDET_OVERRIDE_FLAG(j)
From
qp_list_lines ql,
qp_list_headers_b lhdr
Where ql.list_line_id = px_line_adj_tbl(i).list_line_id
and lhdr.list_header_id = px_line_adj_tbl(i).list_header_id;
G_LDET_UPDATED_FLAG(j) := px_line_adj_tbl(i).updated_flag;
oe_debug_pub.add('wrong in insert manual adj'||SQLERRM);
END Insert_Manual_Adj;
Procedure Insert_lines(
p_Header_Rec IN OE_ORDER_PUB.Header_Rec_Type
, px_Line_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
, p_order_status_rec QP_UTIL_PUB.ORDER_LINES_STATUS_REC_TYPE
, p_pricing_events IN VARCHAR2
) IS
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
OR l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE)
)
THEN
l_source_line_flag := 'Y';
l_line_rec.operation <> OE_GLOBALS.G_OPR_DELETE)
THEN
l_check_line_flag := 'Y';
END Insert_Lines;
oe_debug_pub.add( 'BEFORE DIRECT INSERT INTO TEMP TABLE: BULK INSERT'||G_LINE_INDEX_TBL.COUNT , 1 ) ;
QP_PREQ_GRP.INSERT_LINES2
(p_LINE_INDEX => G_LINE_INDEX_TBL,
p_LINE_TYPE_CODE => G_LINE_TYPE_CODE_TBL,
p_PRICING_EFFECTIVE_DATE =>G_PRICING_EFFECTIVE_DATE_TBL,
p_ACTIVE_DATE_FIRST =>G_ACTIVE_DATE_FIRST_TBL,
p_ACTIVE_DATE_FIRST_TYPE =>G_ACTIVE_DATE_FIRST_TYPE_TBL,
p_ACTIVE_DATE_SECOND =>G_ACTIVE_DATE_SECOND_TBL,
p_ACTIVE_DATE_SECOND_TYPE =>G_ACTIVE_DATE_SECOND_TYPE_TBL,
p_LINE_QUANTITY => G_LINE_QUANTITY_TBL,
p_LINE_UOM_CODE => G_LINE_UOM_CODE_TBL,
p_REQUEST_TYPE_CODE => G_REQUEST_TYPE_CODE_TBL,
p_PRICED_QUANTITY => G_PRICED_QUANTITY_TBL,
p_PRICED_UOM_CODE => G_PRICED_UOM_CODE_TBL,
p_CURRENCY_CODE => G_CURRENCY_CODE_TBL,
p_UNIT_PRICE => G_UNIT_PRICE_TBL,
p_PERCENT_PRICE => G_PERCENT_PRICE_TBL,
p_UOM_QUANTITY => G_UOM_QUANTITY_TBL,
p_ADJUSTED_UNIT_PRICE =>G_ADJUSTED_UNIT_PRICE_TBL,
p_UPD_ADJUSTED_UNIT_PRICE =>G_UPD_ADJUSTED_UNIT_PRICE_TBL,
p_PROCESSED_FLAG =>G_PROCESSED_FLAG_TBL,
p_PRICE_FLAG =>G_PRICE_FLAG_TBL,
p_LINE_ID =>G_LINE_ID_TBL,
p_PROCESSING_ORDER =>G_PROCESSING_ORDER_TBL,
p_PRICING_STATUS_CODE =>G_PRICING_STATUS_CODE_tbl,
p_PRICING_STATUS_TEXT =>G_PRICING_STATUS_TEXT_tbl,
p_ROUNDING_FLAG =>G_ROUNDING_FLAG_TBL,
p_ROUNDING_FACTOR =>G_ROUNDING_FACTOR_TBL,
p_QUALIFIERS_EXIST_FLAG => G_QUALIFIERS_EXIST_FLAG_TBL,
p_PRICING_ATTRS_EXIST_FLAG =>G_PRICING_ATTRS_EXIST_FLAG_TBL,
p_PRICE_LIST_ID => G_PRICE_LIST_ID_TBL,
p_VALIDATED_FLAG => G_PL_VALIDATED_FLAG_TBL,
p_PRICE_REQUEST_CODE => G_PRICE_REQUEST_CODE_TBL,
p_USAGE_PRICING_TYPE => G_USAGE_PRICING_TYPE_tbl,
p_line_category => G_LINE_CATEGORY_tbl,
p_catchweight_qty => G_CATCHWEIGHT_QTY_tbl,
p_actual_order_qty => G_ACTUAL_ORDER_QTY_TBL,
x_status_code =>l_return_status,
x_status_text =>l_return_status_text);
oe_debug_pub.add( 'WRONG IN INSERT_LINES2'||L_RETURN_STATUS_TEXT , 1 ) ;
oe_debug_pub.add( 'inserting adjustments:'||G_LDET_LINE_DETAIL_INDEX.count);
oe_debug_pub.add('32:'||G_LDET_UPDATED_FLAG(i));
QP_PREQ_GRP.INSERT_LDETS2
(p_LINE_DETAIL_INDEX => G_LDET_LINE_DETAIL_INDEX,
p_LINE_DETAIL_TYPE_CODE => G_LDET_LINE_DETAIL_TYPE_CODE,
p_PRICE_BREAK_TYPE_CODE => G_LDET_PRICE_BREAK_TYPE_CODE,
p_LINE_INDEX => G_LDET_LINE_INDEX,
p_LIST_HEADER_ID => G_LDET_LIST_HEADER_ID,
p_LIST_LINE_ID => G_LDET_LIST_LINE_ID,
p_LIST_LINE_TYPE_CODE => G_LDET_LIST_LINE_TYPE,
p_LIST_TYPE_CODE => G_LDET_LIST_TYPE_CODE,
p_PRICING_GROUP_SEQUENCE => G_LDET_PRICING_GROUP_SEQUENCE,
p_PRICING_PHASE_ID => G_LDET_PRICING_PHASE_ID,
p_OPERAND_CALCULATION_CODE => G_LDET_OPERATOR,
p_OPERAND_VALUE => G_LDET_OPERAND_VALUE,
p_SUBSTITUTION_TYPE_CODE => G_LDET_SUBSTITUTION_TYPE_CODE,
p_SUBSTITUTION_VALUE_FROM => G_LDET_SUBSTITUTION_VALUE_FROM,
p_SUBSTITUTION_VALUE_TO => G_LDET_SUBSTITUTION_vALUE_TO,
p_PRICE_FORMULA_ID => G_LDET_PRICE_FORMULA_ID,
p_PRODUCT_PRECEDENCE => G_LDET_PRODUCT_PRECEDENCE,
p_INCOMPATABLILITY_GRP_CODE => G_LDET_INCOM_GRP_CODE,
p_APPLIED_FLAG => G_LDET_APPLIED_FLAG,
p_AUTOMATIC_FLAG => G_LDET_AUTOMATIC_FLAG,
p_OVERRIDE_FLAG => G_LDET_OVERRIDE_FLAG,
p_MODIFIER_LEVEL_CODE => G_LDET_MODIFIER_LEVEL_CODE,
p_BENEFIT_QTY => G_LDET_BENEFIT_QTY,
p_BENEFIT_UOM_CODE => G_LDET_BENEFIT_UOM_CODE,
p_LIST_LINE_NO => G_LDET_LIST_LINE_NO,
p_ACCRUAL_FLAG => G_LDET_ACCRUAL_FLAG,
p_ACCRUAL_CONVERSION_RATE => G_LDET_ACCRUAL_CONVERSION_RATE,
p_ESTIM_ACCRUAL_RATE => G_LDET_ESTIM_ACCRUAL_RATE,
p_CHARGE_TYPE_CODE => G_LDET_CHARGE_TYPE_CODE,
p_CHARGE_SUBTYPE_CODE => G_LDET_CHARGE_SUBTYPE_CODE,
p_LINE_QUANTITY => G_LDET_LINE_QUANTITY,
p_UPDATED_FLAG => G_LDET_UPDATED_FLAG,
p_CALCULATION_CODE => G_LDET_CALCULATION_CODE,
p_CHANGE_REASON_CODE => G_LDET_CHANGE_REASON_CODE,
p_CHANGE_REASON_TEXT => G_LDET_CHANGE_REASON_TEXT,
p_ACCUM_CONTEXT => G_LDET_ACCUM_CONTEXT,
p_ACCUM_ATTRIBUTE => G_LDET_ACCUM_ATTRIBUTE,
p_ACCUM_FLAG => G_LDET_ACCUM_FLAG,
p_BREAK_UOM_CODE => G_LDET_BREAK_UOM_CODE,
p_BREAK_UOM_CONTEXT => G_LDET_BREAK_UOM_CONTEXT,
p_BREAK_UOM_ATTRIBUTE => G_LDET_BREAK_UOM_ATTRIBUTE,
x_status_code => l_return_status,
x_status_text => l_return_status_text);
oe_debug_pub.add( 'ERROR INSERTING INTO LINE DETAILS'||SQLERRM ) ;
QP_PREQ_GRP.INSERT_LINE_ATTRS2
( G_ATTR_LINE_INDEX_tbl,
G_ATTR_LINE_DETAIL_INDEX_tbl ,
G_ATTR_ATTRIBUTE_LEVEL_tbl ,
G_ATTR_ATTRIBUTE_TYPE_tbl ,
G_ATTR_LIST_HEADER_ID_tbl ,
G_ATTR_LIST_LINE_ID_tbl ,
G_ATTR_PRICING_CONTEXT_tbl ,
G_ATTR_PRICING_ATTRIBUTE_tbl ,
G_ATTR_VALUE_FROM_tbl ,
G_ATTR_SETUP_VALUE_FROM_tbl ,
G_ATTR_VALUE_TO_tbl ,
G_ATTR_SETUP_VALUE_TO_tbl ,
G_ATTR_GROUPING_NUMBER_tbl ,
G_ATTR_NO_QUAL_IN_GRP_tbl ,
G_ATTR_COMP_OPERATOR_TYPE_tbl ,
G_ATTR_VALIDATED_FLAG_tbl ,
G_ATTR_APPLIED_FLAG_tbl ,
G_ATTR_PRICING_STATUS_CODE_tbl ,
G_ATTR_PRICING_STATUS_TEXT_tbl ,
G_ATTR_QUAL_PRECEDENCE_tbl ,
G_ATTR_DATATYPE_tbl ,
G_ATTR_PRICING_ATTR_FLAG_tbl ,
G_ATTR_QUALIFIER_TYPE_tbl ,
G_ATTR_PRODUCT_UOM_CODE_TBL ,
G_ATTR_EXCLUDER_FLAG_TBL ,
G_ATTR_PRICING_PHASE_ID_TBL ,
G_ATTR_INCOM_GRP_CODE_TBL,
G_ATTR_LDET_TYPE_CODE_TBL,
G_ATTR_MODIFIER_LEVEL_CODE_TBL,
G_ATTR_PRIMARY_UOM_FLAG_TBL,
l_return_status ,
l_return_status_text );
oe_debug_pub.add( 'ERROR INSERTING INTO LINE ATTRS'||SQLERRM ) ;
oe_debug_pub.add( 'AFTER DIRECT INSERT INTO TEMP TABLE: BULK INSERT' , 1 ) ;
G_LINE_INDEX_tbl.delete;
G_LINE_TYPE_CODE_TBL.delete ;
G_PRICING_EFFECTIVE_DATE_TBL.delete ;
G_ACTIVE_DATE_FIRST_TBL.delete ;
G_ACTIVE_DATE_FIRST_TYPE_TBL.delete ;
G_ACTIVE_DATE_SECOND_TBL.delete ;
G_ACTIVE_DATE_SECOND_TYPE_TBL.delete ;
G_LINE_QUANTITY_TBL.delete ;
G_LINE_UOM_CODE_TBL.delete ;
G_REQUEST_TYPE_CODE_TBL.delete ;
G_PRICED_QUANTITY_TBL.delete ;
G_UOM_QUANTITY_TBL.delete ;
G_PRICED_UOM_CODE_TBL.delete ;
G_CURRENCY_CODE_TBL.delete ;
G_UNIT_PRICE_TBL.delete ;
G_PERCENT_PRICE_TBL.delete ;
G_ADJUSTED_UNIT_PRICE_TBL.delete ;
G_PROCESSED_FLAG_TBL.delete ;
G_PRICE_FLAG_TBL.delete ;
G_LINE_ID_TBL.delete ;
G_PROCESSING_ORDER_TBL.delete ;
G_ROUNDING_FLAG_TBL.delete;
G_ROUNDING_FACTOR_TBL.delete ;
G_PRICING_STATUS_CODE_TBL.delete ;
G_PRICING_STATUS_TEXT_TBL.delete ;
G_ATTR_LINE_INDEX_tbl.delete;
G_ATTR_ATTRIBUTE_LEVEL_tbl.delete;
G_ATTR_VALIDATED_FLAG_tbl.delete;
G_ATTR_ATTRIBUTE_TYPE_tbl.delete;
G_ATTR_PRICING_CONTEXT_tbl.delete;
G_ATTR_PRICING_ATTRIBUTE_tbl.delete;
G_ATTR_APPLIED_FLAG_tbl.delete;
G_ATTR_PRICING_STATUS_CODE_tbl.delete;
G_ATTR_PRICING_ATTR_FLAG_tbl.delete;
G_ATTR_LIST_HEADER_ID_tbl.delete;
G_ATTR_LIST_LINE_ID_tbl.delete;
G_ATTR_VALUE_FROM_tbl.delete;
G_ATTR_SETUP_VALUE_FROM_tbl.delete;
G_ATTR_VALUE_TO_tbl.delete;
G_ATTR_SETUP_VALUE_TO_tbl.delete;
G_ATTR_GROUPING_NUMBER_tbl.delete;
G_ATTR_NO_QUAL_IN_GRP_tbl.delete;
G_ATTR_COMP_OPERATOR_TYPE_tbl.delete;
G_ATTR_VALIDATED_FLAG_tbl.delete;
G_ATTR_APPLIED_FLAG_tbl.delete;
G_ATTR_PRICING_STATUS_CODE_tbl.delete;
G_ATTR_PRICING_STATUS_TEXT_tbl.delete;
G_ATTR_QUAL_PRECEDENCE_tbl.delete;
G_ATTR_DATATYPE_tbl.delete;
G_ATTR_PRICING_ATTR_FLAG_tbl.delete ;
G_ATTR_QUALIFIER_TYPE_tbl.delete;
G_ATTR_PRODUCT_UOM_CODE_TBL.delete;
G_ATTR_EXCLUDER_FLAG_TBL.delete;
G_ATTR_PRICING_PHASE_ID_TBL.delete;
G_ATTR_INCOM_GRP_CODE_TBL.delete;
G_ATTR_LDET_TYPE_CODE_TBL.delete;
G_ATTR_MODIFIER_LEVEL_CODE_TBL.delete;
G_ATTR_PRIMARY_UOM_FLAG_TBL.delete;
G_LDET_LINE_DETAIL_INDEX.delete;
G_LDET_LINE_DETAIL_TYPE_CODE.delete;
G_LDET_PRICE_BREAK_TYPE_CODE.delete;
G_LDET_LINE_INDEX.delete;
G_LDET_LIST_HEADER_ID.delete;
G_LDET_LIST_LINE_ID.delete;
G_LDET_LIST_LINE_TYPE.delete;
G_LDET_LIST_TYPE_CODE.delete;
G_LDET_PRICING_GROUP_SEQUENCE.delete;
G_LDET_PRICING_PHASE_ID.delete;
G_LDET_OPERATOR.delete;
G_LDET_OPERAND_VALUE.delete;
G_LDET_SUBSTITUTION_TYPE_CODE.delete;
G_LDET_SUBSTITUTION_VALUE_FROM.delete;
G_LDET_SUBSTITUTION_VALUE_TO.delete;
G_LDET_PRICE_FORMULA_ID.delete;
G_LDET_PRODUCT_PRECEDENCE.delete;
G_LDET_INCOM_GRP_CODE.delete;
G_LDET_APPLIED_FLAG.delete;
G_LDET_AUTOMATIC_FLAG.delete;
G_LDET_OVERRIDE_FLAG.delete;
G_LDET_MODIFIER_LEVEL_CODE.delete;
G_LDET_BENEFIT_QTY.delete;
G_LDET_BENEFIT_UOM_CODE.delete;
G_LDET_LIST_LINE_NO.delete;
G_LDET_ACCRUAL_FLAG.delete;
G_LDET_ACCRUAL_CONVERSION_RATE.delete;
G_LDET_ESTIM_ACCRUAL_RATE.delete;
G_LDET_CHARGE_TYPE_CODE.delete;
G_LDET_CHARGE_SUBTYPE_CODE.delete;
G_LDET_LINE_QUANTITY.delete;
G_LDET_UPDATED_FLAG.delete; -- begin shu, fix Bug 2599822
G_LDET_CALCULATION_CODE.delete;
G_LDET_CHANGE_REASON_CODE.delete;
G_LDET_CHANGE_REASON_TEXT.delete;
G_LDET_ADJUSTMENT_AMOUNT.delete;
select line_id
, line_index
, line_type_code
, processed_code
, pricing_status_code
, pricing_status_text status_text
, unit_price
, adjusted_unit_price
, priced_quantity
, line_quantity
, priced_uom_code
from qp_preq_lines_tmp
where process_status <> 'NOT_VALID' and
(pricing_status_code not in
(QP_PREQ_GRP.G_STATUS_UNCHANGED,
QP_PREQ_GRP.G_STATUS_UPDATED,
QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
'NOT_VALID')
OR (l_allow_negative_price = 'N' AND (unit_price<0 OR adjusted_unit_price<0)));
select lines.line_id
, lines.unit_price
, lines.adjusted_unit_price
, lines.price_list_header_id
, lines.priced_quantity
, lines.line_quantity
, l.shipped_quantity
, l.header_id
from oe_order_lines l
, qp_preq_lines_tmp lines
where lines.line_id = l.line_id
and lines.line_type_code='LINE'
and l.booked_flag = 'Y'
and l.item_type_code NOT IN ('INCLUDED','CONFIG')
and (lines.unit_price is NULL
or lines.adjusted_unit_price is NULL
or lines.price_list_header_id is NULL)
and lines.process_status <> 'NOT_VALID'
;
Select name into l_price_list
from qp_list_headers_vl where
list_header_id = l_line_rec.price_list_id;
Select name into l_price_list
from qp_list_headers_vl a,qp_list_lines b where
b.list_line_id = to_number(substr(wrong_line.status_text,1,
instr(wrong_line.status_text,',')-1))
and a.list_header_id=b.list_header_id
;
Select name into l_price_list
from qp_list_headers_vl a,qp_list_lines b where
b.list_line_id =
to_number(substr(wrong_line.status_text,
instr(wrong_line.status_text,',')+1))
and a.list_header_id=b.list_header_id ;
Insert_Lines( px_Header_Rec
, px_Line_Tbl
, l_order_status_rec
, p_Pricing_Events);
Insert_Manual_Adj(px_Line_Adj_Tbl);
SELECT /*+ ORDERED USE_NL(ldets lines qh) */
oe_price_adjustments_s.nextval price_adjustment_id
, oe_order_pub.g_hdr.header_id header_id
, ldets.automatic_flag automatic_flag
, decode(ldets.modifier_level_code,'ORDER',NULL,lines.line_id) line_id
, ldets.LIST_HEADER_ID list_header_id
, ldets.LIST_LINE_ID list_line_id
, ldets.LIST_LINE_TYPE_CODE list_line_type_code
, NULL MODIFIER_MECHANISM_TYPE_CODE
, decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL) modified_from
, decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE', to_char(ldets.related_item_id), NULL) modified_to
, ldets.UPDATED_FLAG
, ldets.override_flag update_allowed
, ldets.APPLIED_FLAG applied_flag
, NULL CHANGE_REASON_CODE
, NULL CHANGE_REASON_TEXT
, ldets.order_qty_operand operand
, ldets.operand_calculation_code arithmetic_operator
, nvl(ldets.order_qty_adj_amt, 99) adjusted_amount
, ldets.pricing_phase_id PRICING_PHASE_ID
, ldets.CHARGE_TYPE_CODE charge_type_code
, ldets.CHARGE_SUBTYPE_CODE charge_subtype_code
, ldets.list_line_no list_line_no
, qh.source_system_code source_system_code
, ldets.benefit_qty benefit_qty
, ldets.benefit_uom_code benefit_uom_code
, ldets.expiration_date expiration_date
, ldets.rebate_transaction_type_code rebate_transaction_Type_code
, ldets.accrual_flag accrual_flag
, ldets.line_quantity range_break_quantity
, ldets.accrual_conversion_rate accrual_conversion_rate
, ldets.pricing_group_sequence pricing_group_sequence
, ldets.modifier_level_code modifier_level_code
, ldets.price_break_type_code price_break_type_code
, ldets.substitution_attribute substitution_attribute
, ldets.proration_type_code proration_type_code
, ldets.include_on_returns_flag include_on_returns_flag
, ldets.OPERAND_value operand_per_pqty
, ldets.adjustment_amount adjusted_amount_per_pqty
, nvl(ldets.line_detail_type_code, 'DIS') line_detail_type_code
FROM
QP_LDETS_v ldets
, QP_PREQ_LINES_TMP lines
, QP_LIST_HEADERS_B QH
WHERE
ldets.list_header_id=qh.list_header_id
AND ldets.process_code in (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
AND lines.process_status <> 'NOT_VALID'
AND ldets.line_index=lines.line_index
--AND ldets.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
--AND nvl(ldets.automatic_flag,'N') = 'Y'
-- or
-- (ldets.list_line_type_code = 'FREIGHT_CHARGE'))
AND ldets.created_from_list_type_code not in ('PRL','AGR')
AND ldets.list_line_type_code<>'PLL'
-- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
;
l_adj_tbl(i).UPDATED_FLAG := adj.updated_Flag;
l_adj_tbl(i).UPDATE_ALLOWED := adj.update_allowed;
SELECT meaning
INTO l_adj_tbl(i).change_reason_text
FROM oe_lookups
WHERE lookup_type = 'CHANGE_CODE'
AND lookup_code = l_adj_tbl(i).change_reason_code;
SELECT name
INTO l_adj_tbl(i).attribute1
from qp_list_headers_vl
where list_header_id = l_adj_tbl(i).list_header_id;
SELECT meaning
into l_adj_tbl(i).attribute2
from qp_lookups
where lookup_code = l_adj_tbl(i).arithmetic_operator
and lookup_type = 'ARITHMETIC_OPERATOR';
SELECT meaning
into l_adj_tbl(i).attribute3
from qp_lookups
where lookup_code = l_adj_tbl(i).list_line_type_code
and lookup_type = 'LIST_LINE_TYPE_CODE';
SELECT name
into l_adj_tbl(i).attribute4
from qp_pricing_phases
where pricing_phase_id = l_adj_tbl(i).pricing_phase_id;
SELECT meaning
into l_adj_tbl(i).attribute5
from qp_lookups
where lookup_code = l_adj_tbl(i).automatic_flag
and lookup_type = 'YES_NO';
l_control_rec.temp_table_insert_flag := 'N';
Procedure Update_Lines(px_Line_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type)
IS
l_line_index pls_integer;
select /*+ INDEX(lines qp_preq_lines_tmp_n1) */
nvl(lines.order_uom_selling_price, lines.ADJUSTED_UNIT_PRICE * lines.priced_quantity /l_line_rec.ordered_quantity)
, nvl(lines.line_unit_price, lines.UNIT_PRICE * lines.priced_quantity/l_line_rec.ordered_quantity)
, lines.ADJUSTED_UNIT_PRICE
, lines.UNIT_PRICE
, decode(lines.priced_quantity,-99999,l_line_rec.ordered_quantity
,lines.priced_quantity)
, decode(lines.priced_quantity,-99999,l_line_rec.order_quantity_uom
,lines.priced_uom_code)
, decode(lines.price_list_header_id,-9999,NULL,lines.price_list_header_id) --Bug#2830609
, nvl(lines.percent_price, NULL)
, nvl(lines.parent_price, NULL)
, decode(lines.parent_price, NULL, 0, 0, 0,
lines.adjusted_unit_price/lines.parent_price)
INTO
l_line_rec.UNIT_SELLING_PRICE
, l_line_rec.UNIT_LIST_PRICE
, l_line_rec.UNIT_SELLING_PRICE_PER_PQTY
, l_line_rec.UNIT_LIST_PRICE_PER_PQTY
, l_line_rec.PRICING_QUANTITY
, l_line_rec.PRICING_QUANTITY_UOM
, l_line_rec.PRICE_LIST_ID
, l_line_rec.UNIT_LIST_PERCENT
, l_line_rec.UNIT_PERCENT_BASE_PRICE
, l_line_rec.UNIT_SELLING_PERCENT
from qp_preq_lines_tmp lines
where lines.line_id=l_line_rec.line_id
and lines.line_type_code='LINE'
and l_line_rec.ordered_quantity <> 0
and lines.process_status <> 'NOT_VALID'
and lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
QP_PREQ_GRP.G_STATUS_UPDATED)
;
END Update_Lines;
Update_Lines(l_Line_Tbl);
SELECT Count(1)
INTO l_hdr_chg_count
FROM oe_price_adjustments
WHERE header_id= l_Line_Tbl(1).header_id
AND list_line_type_code='FREIGHT_CODE'
AND line_id IS NULL;