[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
insert into qp_preq_ldets_tmp_t
(REQUEST_ID,
LINE_DETAIL_INDEX,
LINE_DETAIL_TYPE_CODE,
PRICE_BREAK_TYPE_CODE,
LINE_INDEX,
CREATED_FROM_LIST_HEADER_ID,
CREATED_FROM_LIST_LINE_ID,
CREATED_FROM_LIST_LINE_TYPE,
CREATED_FROM_LIST_TYPE_CODE,
PRICING_GROUP_SEQUENCE,
PRICING_PHASE_ID,
OPERAND_CALCULATION_CODE,
OPERAND_VALUE,
SUBSTITUTION_TYPE_CODE,
SUBSTITUTION_VALUE_FROM,
SUBSTITUTION_VALUE_TO,
PRICE_FORMULA_ID,
PRICING_STATUS_CODE,
PRODUCT_PRECEDENCE,
INCOMPATABILITY_GRP_CODE,
APPLIED_FLAG,
AUTOMATIC_FLAG,
OVERRIDE_FLAG,
MODIFIER_LEVEL_CODE,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
LIST_LINE_NO,
ACCRUAL_FLAG,
ACCRUAL_CONVERSION_RATE,
ESTIM_ACCRUAL_RATE,
CHARGE_TYPE_CODE,
CHARGE_SUBTYPE_CODE,
PROCESS_CODE,
LINE_QUANTITY,
UPDATED_FLAG, -- begin shu, fix Bug 2599822
CALCULATION_CODE,
CHANGE_REASON_CODE,
CHANGE_REASON_TEXT,
ADJUSTMENT_AMOUNT)
(select QP_PREQ_GRP.G_REQUEST_ID,
i,
'NULL',
'NULL', --ll.PRICE_BREAK_TYPE_CODE,
px_line_adj_tbl(i).header_id + nvl(px_line_adj_tbl(i).line_id, 0), --line.LINE_INDEX,
px_line_adj_tbl(i).LIST_HEADER_ID,
px_line_adj_tbl(i).LIST_LINE_ID,
px_line_adj_tbl(i).LIST_LINE_TYPE_CODE,
lhdr.LIST_TYPE_CODE,
ql.PRICING_GROUP_SEQUENCE,
ql.PRICING_PHASE_ID,
ql.ARITHMETIC_OPERATOR,
px_line_adj_tbl(i).operand, --nvl(adj_iface.OPERAND_PER_PQTY,adj_iface.OPERAND),
NULL, --ll.SUBSTITUTION_ATTRIBUTE,
NULL, --ll.SUBSTITUTION_VALUE,
NULL,
NULL, --ll.PRICE_BY_FORMULA_ID,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
ql.PRODUCT_PRECEDENCE,
ql.INCOMPATIBILITY_GRP_CODE,
'Y', --adj_iface.APPLIED_FLAG,
'Y', --ll.AUTOMATIC_FLAG,
'Y', --ll.OVERRIDE_FLAG,
px_line_adj_tbl(i).MODIFIER_LEVEL_CODE,
NULL, --ll.BENEFIT_QTY,
NULL, --ll.BENEFIT_UOM_CODE,
ql.LIST_LINE_NO,
ql.ACCRUAL_FLAG,
ql.ACCRUAL_CONVERSION_RATE,
ql.ESTIM_ACCRUAL_RATE,
ql.CHARGE_TYPE_CODE,
ql.CHARGE_SUBTYPE_CODE,
QP_PREQ_PUB.G_STATUS_NEW,
NULL,
px_line_adj_tbl(i).updated_flag,
NULL,
NULL,
NULL,
px_line_adj_tbl(i).adjusted_amount --adj_iface.ADJUSTED_AMOUNT_PER_PQTY
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);
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 ) ;
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;
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
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 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);