[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select e.Pricing_Phase_Id,
nvl(p.user_freeze_override_flag,p.freeze_override_flag) pof
from qp_event_Phases e, qp_pricing_phases p
where e.pricing_phase_id = p.pricing_phase_id and
trunc(sysdate) between Trunc(nvl(start_date_active,sysdate)) and
trunc(nvl(End_Date_Active,sysdate))
and e.pricing_event_code IN
(SELECT decode(rownum
,1 ,substr(p_pricing_event,1,instr(l_event_code1,',',1,1)-1)
,2 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1))
,3 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1))
,4 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1))
,5 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1))
,6 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1)))
FROM qp_event_phases
WHERE rownum < 7);
Select c.name,
a.list_line_no
From qp_preq_ldets_tmp a,
qp_preq_lines_tmp b,
qp_list_headers_vl c
Where b.line_id = p_line_id
And b.line_index = a.line_index
And a.created_from_list_header_id = c.list_header_id
And a.automatic_flag = 'Y'
And a.pricing_status_code = 'N'
And b.process_status <> 'NOT_VALID'
And a.created_from_list_line_type <> 'PLL';
SELECT AGREEMENT_ID
, CUST_PO_NUMBER
, FREIGHT_TERMS_CODE
, HEADER_ID
, INVOICE_TO_ORG_ID
, ORDER_CATEGORY_CODE
, ORDER_TYPE_ID
, ORDERED_DATE
, PAYMENT_TERM_ID
, PAYMENT_TYPE_CODE
, PRICE_LIST_ID
, PRICE_REQUEST_CODE
, PRICING_DATE
, REQUEST_DATE
, SHIP_FROM_ORG_ID
, SHIP_TO_ORG_ID
, SHIPMENT_PRIORITY_CODE
, SHIPPING_METHOD_CODE
, SOLD_TO_ORG_ID
, TRANSACTIONAL_CURR_CODE
, LOCK_CONTROL
INTO x_header_rec.AGREEMENT_ID
, x_header_rec.CUST_PO_NUMBER
, x_header_rec.FREIGHT_TERMS_CODE
, x_header_rec.HEADER_ID
, x_header_rec.INVOICE_TO_ORG_ID
, x_header_rec.ORDER_CATEGORY_CODE
, x_header_rec.ORDER_TYPE_ID
, x_header_rec.ORDERED_DATE
, x_header_rec.PAYMENT_TERM_ID
, x_header_rec.PAYMENT_TYPE_CODE
, x_header_rec.PRICE_LIST_ID
, x_header_rec.PRICE_REQUEST_CODE
, x_header_rec.PRICING_DATE
, x_header_rec.REQUEST_DATE
, x_header_rec.SHIP_FROM_ORG_ID
, x_header_rec.SHIP_TO_ORG_ID
, x_header_rec.SHIPMENT_PRIORITY_CODE
, x_header_rec.SHIPPING_METHOD_CODE
, x_header_rec.SOLD_TO_ORG_ID
, x_header_rec.TRANSACTIONAL_CURR_CODE
, x_header_rec.LOCK_CONTROL
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = p_header_id;
SELECT AGREEMENT_ID
, BOOKED_FLAG
, CANCELLED_FLAG
, CUST_PO_NUMBER
, COMMITMENT_ID
, FREIGHT_TERMS_CODE
, HEADER_ID
, INVENTORY_ITEM_ID
, INVOICE_TO_ORG_ID
, ITEM_IDENTIFIER_TYPE
, ITEM_TYPE_CODE
, ORDERED_ITEM_ID
, LINE_CATEGORY_CODE
, LINE_ID
, LINE_TYPE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, ORDERED_QUANTITY2 -- OPM 2434270
, ORDERED_QUANTITY_UOM2 -- OPM 2434270
, ORG_ID
, PAYMENT_TERM_ID
, PLANNING_PRIORITY
, PREFERRED_GRADE --OPM 02/JUN/00
, PRICE_LIST_ID
, PRICE_REQUEST_CODE
, PRICING_DATE
, PRICING_QUANTITY
, PRICING_QUANTITY_UOM
, PROMISE_DATE
, REQUEST_DATE
, SHIPMENT_PRIORITY_CODE
, SHIPPING_METHOD_CODE
, SHIP_FROM_ORG_ID
, SHIPPABLE_FLAG
, SHIPPED_QUANTITY
, SHIP_SET_ID
, SHIP_TO_ORG_ID
, SOLD_TO_ORG_ID
, SOLD_FROM_ORG_ID
, SOURCE_TYPE_CODE
, SPLIT_FROM_LINE_ID
, TAX_EXEMPT_FLAG
, UNIT_LIST_PRICE
, UNIT_LIST_PRICE_PER_PQTY
, UNIT_SELLING_PRICE
, UNIT_SELLING_PRICE_PER_PQTY
, UNIT_LIST_PERCENT
, UNIT_SELLING_PERCENT
, UNIT_PERCENT_BASE_PRICE
, CALCULATE_PRICE_FLAG
, upgraded_flag
, CHARGE_PERIODICITY_CODE --rc
, LOCK_CONTROL
FROM OE_ORDER_LINES /* MOAC SQL NO CHANGE */
WHERE l_entity = 'L'
-- AND ORDERED_QUANTITY <> 0 (--bug 3018537) commented for the FP bug 3335024
AND LINE_ID = p_line_id
UNION
SELECT AGREEMENT_ID
, BOOKED_FLAG
, CANCELLED_FLAG
, CUST_PO_NUMBER
, COMMITMENT_ID
, FREIGHT_TERMS_CODE
, HEADER_ID
, INVENTORY_ITEM_ID
, INVOICE_TO_ORG_ID
, ITEM_IDENTIFIER_TYPE
, ITEM_TYPE_CODE
, ORDERED_ITEM_ID
, LINE_CATEGORY_CODE
, LINE_ID
, LINE_TYPE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, ORDERED_QUANTITY2 -- OPM 2434270
, ORDERED_QUANTITY_UOM2 -- OPM 2434270
, ORG_ID
, PAYMENT_TERM_ID
, PLANNING_PRIORITY
, PREFERRED_GRADE --OPM 02/JUN/00
, PRICE_LIST_ID
, PRICE_REQUEST_CODE
, PRICING_DATE
, PRICING_QUANTITY
, PRICING_QUANTITY_UOM
, PROMISE_DATE
, REQUEST_DATE
, SHIPMENT_PRIORITY_CODE
, SHIPPING_METHOD_CODE
, SHIP_FROM_ORG_ID
, SHIPPABLE_FLAG
, SHIPPED_QUANTITY
, SHIP_SET_ID
, SHIP_TO_ORG_ID
, SOLD_TO_ORG_ID
, SOLD_FROM_ORG_ID
, SOURCE_TYPE_CODE
, SPLIT_FROM_LINE_ID
, TAX_EXEMPT_FLAG
, UNIT_LIST_PRICE
, UNIT_LIST_PRICE_PER_PQTY
, UNIT_SELLING_PRICE
, UNIT_SELLING_PRICE_PER_PQTY
, UNIT_LIST_PERCENT
, UNIT_SELLING_PERCENT
, UNIT_PERCENT_BASE_PRICE
, CALCULATE_PRICE_FLAG
, CHARGE_PERIODICITY_CODE --rc
, upgraded_flag
, LOCK_CONTROL
FROM OE_ORDER_LINES /* MOAC SQL NO CHANGE */
WHERE l_entity = 'H'
--AND ORDERED_QUANTITY <> 0 (--bug 3018537) commented for the FP bug 3335024
AND HEADER_ID = p_header_id;
x_line_tbl.delete(i);
/* select nvl(enforce_line_prices_flag,'N') into l_enforce_price_flag
from oe_line_types_v where line_type_id=OE_Order_PUB.G_Line.Line_Type_id; */
/*select nvl(enforce_line_prices_flag,'N') into l_enforce_price_flag
from oe_Order_types_v where Order_type_id=OE_Order_PUB.g_hdr.Order_Type_Id;*/
select rounding_factor into g_rounding_factor_rec.rounding_factor from
qp_list_headers_b where list_header_id=p_list_header_id;
select flex_title, pricing_context, pricing_attribute1,
pricing_attribute2 , pricing_attribute3 , pricing_attribute4 , pricing_attribute5 ,
pricing_attribute6 , pricing_attribute7 , pricing_attribute8 , pricing_attribute9 ,
pricing_attribute10 , pricing_attribute11 , pricing_attribute12 , pricing_attribute13 ,
pricing_attribute14 , pricing_attribute15 , pricing_attribute16 , pricing_attribute17 ,
pricing_attribute18 , pricing_attribute19 , pricing_attribute20 , pricing_attribute21 ,
pricing_attribute22 , pricing_attribute23 , pricing_attribute24 , pricing_attribute25 ,
pricing_attribute26 , pricing_attribute27 , pricing_attribute28 , pricing_attribute29 ,
pricing_attribute30 , pricing_attribute31 , pricing_attribute32 , pricing_attribute33 ,
pricing_attribute34 , pricing_attribute35 , pricing_attribute36 , pricing_attribute37 ,
pricing_attribute38 , pricing_attribute39 , pricing_attribute40 , pricing_attribute41 ,
pricing_attribute42 , pricing_attribute43 , pricing_attribute44 , pricing_attribute45 ,
pricing_attribute46 , pricing_attribute47 , pricing_attribute48 , pricing_attribute49 ,
pricing_attribute50 , pricing_attribute51 , pricing_attribute52 , pricing_attribute53 ,
pricing_attribute54 , pricing_attribute55 , pricing_attribute56 , pricing_attribute57 ,
pricing_attribute58 , pricing_attribute59 , pricing_attribute60 , pricing_attribute61 ,
pricing_attribute62 , pricing_attribute63 , pricing_attribute64 , pricing_attribute65 ,
pricing_attribute66 , pricing_attribute67 , pricing_attribute68 , pricing_attribute69 ,
pricing_attribute70 , pricing_attribute71 , pricing_attribute72 , pricing_attribute73 ,
pricing_attribute74 , pricing_attribute75 , pricing_attribute76 , pricing_attribute77 ,
pricing_attribute78 , pricing_attribute79 , pricing_attribute80 , pricing_attribute81 ,
pricing_attribute82 , pricing_attribute83 , pricing_attribute84 , pricing_attribute85 ,
pricing_attribute86 , pricing_attribute87 , pricing_attribute88 , pricing_attribute89 ,
pricing_attribute90 , pricing_attribute91 , pricing_attribute92 , pricing_attribute93 ,
pricing_attribute94 , pricing_attribute95 , pricing_attribute96 , pricing_attribute97 ,
pricing_attribute98 , pricing_attribute99 , pricing_attribute100
,Override_Flag
from oe_order_price_attribs a
where (a.line_id is null and a.header_id = p_header_id )
union all
select flex_title, pricing_context, pricing_attribute1,
pricing_attribute2 , pricing_attribute3 , pricing_attribute4 , pricing_attribute5 ,
pricing_attribute6 , pricing_attribute7 , pricing_attribute8 , pricing_attribute9 ,
pricing_attribute10 , pricing_attribute11 , pricing_attribute12 , pricing_attribute13 ,
pricing_attribute14 , pricing_attribute15 , pricing_attribute16 , pricing_attribute17 ,
pricing_attribute18 , pricing_attribute19 , pricing_attribute20 , pricing_attribute21 ,
pricing_attribute22 , pricing_attribute23 , pricing_attribute24 , pricing_attribute25 ,
pricing_attribute26 , pricing_attribute27 , pricing_attribute28 , pricing_attribute29 ,
pricing_attribute30 , pricing_attribute31 , pricing_attribute32 , pricing_attribute33 ,
pricing_attribute34 , pricing_attribute35 , pricing_attribute36 , pricing_attribute37 ,
pricing_attribute38 , pricing_attribute39 , pricing_attribute40 , pricing_attribute41 ,
pricing_attribute42 , pricing_attribute43 , pricing_attribute44 , pricing_attribute45 ,
pricing_attribute46 , pricing_attribute47 , pricing_attribute48 , pricing_attribute49 ,
pricing_attribute50 , pricing_attribute51 , pricing_attribute52 , pricing_attribute53 ,
pricing_attribute54 , pricing_attribute55 , pricing_attribute56 , pricing_attribute57 ,
pricing_attribute58 , pricing_attribute59 , pricing_attribute60 , pricing_attribute61 ,
pricing_attribute62 , pricing_attribute63 , pricing_attribute64 , pricing_attribute65 ,
pricing_attribute66 , pricing_attribute67 , pricing_attribute68 , pricing_attribute69 ,
pricing_attribute70 , pricing_attribute71 , pricing_attribute72 , pricing_attribute73 ,
pricing_attribute74 , pricing_attribute75 , pricing_attribute76 , pricing_attribute77 ,
pricing_attribute78 , pricing_attribute79 , pricing_attribute80 , pricing_attribute81 ,
pricing_attribute82 , pricing_attribute83 , pricing_attribute84 , pricing_attribute85 ,
pricing_attribute86 , pricing_attribute87 , pricing_attribute88 , pricing_attribute89 ,
pricing_attribute90 , pricing_attribute91 , pricing_attribute92 , pricing_attribute93 ,
pricing_attribute94 , pricing_attribute95 , pricing_attribute96 , pricing_attribute97 ,
pricing_attribute98 , pricing_attribute99 , pricing_attribute100
,Override_Flag
from oe_order_price_attribs a
where (p_line_id is not null and a.line_id = p_line_id);
SELECT transactional_curr_code,
conversion_type_code,
conversion_rate,
conversion_rate_date
INTO l_transactional_curr_code,
l_conversion_type_code,
l_conversion_rate,
l_conversion_rate_date
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = l_line_rec.header_id;
oe_debug_pub.add( 'INSERT INTO RLTD LINES TBL FOR SERVICE' , 3 ) ;
QP_PREQ_GRP.INSERT_RLTD_LINES2(
p_LINE_INDEX => l_line_index
,p_LINE_DETAIL_INDEX => l_line_detail_index
,p_RELATIONSHIP_TYPE_CODE => l_relationship_type_code
,p_RELATED_LINE_INDEX => l_related_line_index
,p_RELATED_LINE_DETAIL_INDEX => l_related_line_detail_index
,x_status_code => l_status_code
,x_status_text => l_status_text
);
oe_debug_pub.add('QP_PREQ_GRP.INSERT_RLTD_LINES2 has reported errors:'||SQLERRM);
oe_debug_pub.add('QP_PREQ_GRP.INSERT_RLTD_LINES2 has reported errors:'||SQLERRM);
INSERT INTO QP_PREQ_RLTD_LINES_TMP
(LINE_INDEX,
LINE_DETAIL_INDEX,
RELATIONSHIP_TYPE_CODE,
RELATED_LINE_INDEX,
RELATED_LINE_DETAIL_INDEX,
REQUEST_TYPE_CODE,
PRICING_STATUS_CODE)
VALUES ( line_Tbl_Index /*px_line_tbl(line_tbl_index).header_id
+ px_line_tbl(line_tbl_index).line_id*/,
NULL,
QP_PREQ_GRP.G_SERVICE_LINE,
p_line_tbl_index /*px_line_tbl(p_line_tbl_index).header_id
+ px_line_tbl(p_line_tbl_index).line_id*/,
NULL,
'NULL',
QP_PREQ_PUB.G_STATUS_UNCHANGED
);
oe_debug_pub.add( 'ERROR INSERTING'||SQLERRM , 3 ) ;
select adj1.line_id prg_line_id, assoc.rltd_price_adj_id
from oe_price_adjustments adj1,
oe_price_adj_assocs assoc,
oe_price_adjustments adj2
where adj1.price_adjustment_id = assoc.rltd_price_adj_id AND
assoc.price_adjustment_id = adj2.price_adjustment_id AND
adj2.list_line_type_code = 'PRG' AND
adj2.line_id = p_line_id;
select line_id from oe_order_lines_all
where
service_reference_line_id=p_line_Id and
service_reference_type_code='ORDER'and
header_id=p_header_id and
original_list_price is NULL;
select modified_from from oe_price_adjustments
where line_id=px_line_rec.line_id
and list_line_type_code='IUE';
SELECT concatenated_segments
INTO px_line_rec.ordered_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = px_line_rec.inventory_item_id
AND organization_id = l_org_id;
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_CONTRACT_START_DATE_TBL.delete;
G_CONTRACT_END_DATE_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_CHARGE_PERIODICITY_CODE_TBL.delete;
G_ADDED_PARENT_TBL.delete;
PROCEDURE UPDATE_GLOBAL(p_old_line_rec IN OE_ORDER_PUB.LINE_REC_TYPE,
p_line_rec IN OE_ORDER_PUB.LINE_REC_TYPE)
IS
l_notify_index NUMBER;
oe_debug_pub.add(' Calling update_global_picture to register line changes');
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_header_id=>l_line_rec.header_id,
--p_old_line_rec=>p_old_line_rec,
--p_line_rec =>p_line_rec,
p_line_id => l_line_rec.line_id,
x_index => l_notify_index,
x_return_status => l_return_status);
oe_debug_pub.add(' Update_global_price called from oe_order_price_pvt.populate_line_tbl reports errors');
select /*+ INDEX(lines qp_preq_lines_tmp_n1) */
nvl(lines.order_uom_selling_price, lines.ADJUSTED_UNIT_PRICE * nvl(lines.priced_quantity,l_line_rec.ordered_quantity)/l_line_rec.ordered_quantity)
, nvl(lines.line_unit_price, lines.UNIT_PRICE * nvl(lines.priced_quantity,l_line_rec.ordered_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 lines.process_status <> 'NOT_VALID'
and lines.pricing_status_code in
(QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
QP_PREQ_GRP.G_STATUS_UPDATED);
IF l_line_rec.operation NOT IN (OE_GLOBALS.G_OPR_CREATE, OE_GLOBALS.G_OPR_INSERT) THEN
l_check_sec := TRUE;
select /*+ INDEX(lines qp_preq_lines_tmp_n1) */
nvl(lines.order_uom_selling_price, lines.ADJUSTED_UNIT_PRICE * nvl(lines.priced_quantity,l_line_rec.ordered_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 l_line_rec.open_flag <> 'N'
and lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
QP_PREQ_GRP.G_STATUS_UPDATED);
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
IF l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
-- bug 3554060
l_old_change_reason := l_line_rec.change_reason;
oe_debug_Pub.add( 'BEFORE UPDATE GLOBAL');
UPDATE_GLOBAL(p_old_line_rec=>l_old_line_rec,p_line_rec=>l_line_rec);
oe_debug_pub.add(' AFTER UPDATE GLOBAL');
SELECT ql.LINE_ID, l.ordered_item, l.inventory_item_id, ql.pricing_status_text
FROM QP_PREQ_LINES_TMP ql
, OE_ORDER_LINES l
WHERE ql.line_id=l.line_id
AND ql.LINE_TYPE_CODE='LINE'
AND ql.PROCESS_STATUS <> 'NOT_VALID'
AND ql.PRICING_STATUS_CODE =
QP_PREQ_GRP.G_STATUS_GSA_VIOLATION
AND l.ITEM_TYPE_CODE NOT IN ('INCLUDED','CONFIG')
AND l.TRANSACTION_PHASE_CODE ='F'; -- Bug 6617462;
CURSOR updated_lines IS
SELECT LINE_ID FROM QP_PREQ_LINES_TMP
WHERE LINE_TYPE_CODE='LINE'
AND PROCESS_STATUS <> 'NOT_VALID'
AND PRICING_STATUS_CODE = (QP_PREQ_GRP.G_STATUS_UPDATED);
select 'Y' into l_gsa_released from
oe_order_holds ooh,oe_hold_sources ohs,oe_hold_releases ohr
where ooh.line_id = i.line_id
and ooh.hold_source_id = ohs.hold_source_id
and ohr.hold_release_id = ooh.hold_release_id
and ohs.hold_id = l_hold_source_rec.hold_id
and ohr.created_by <> 1
and ohr.release_reason_code <> 'PASS_GSA' ;
FOR i in updated_lines LOOP
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'UPDATING LINE:'||I.LINE_ID||' RELEASING HOLD IF ANY' , 3 ) ;
PROCEDURE UPDATE_ORDER_HEADER(
px_header_rec IN OE_ORDER_PUB.HEADER_REC_TYPE
)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add('update order header with price_request_code');
update oe_order_headers
set price_request_code
= (select price_request_code
from qp_preq_lines_tmp
where line_type_code='ORDER'
and line_id=px_header_rec.header_id);
END UPDATE_ORDER_HEADER;
cursor updated_lines IS
SELECT l.LINE_ID
, l.UNIT_SELLING_PRICE_PER_PQTY
, l.unit_selling_price usp
, LINES.ADJUSTED_UNIT_PRICE
, lines.order_uom_Selling_price ousp
, l.COMMITMENT_ID
, l.BOOKED_FLAG
, l.LINE_CATEGORY_CODE
, l.line_type_id
, l.shippable_flag
, l.shipped_quantity
, l.tax_exempt_flag
, l.org_id
, l.header_id
, l.reference_line_id
, l.return_context
, l.reference_customer_trx_line_id
-- BLANKETS: select following fields for logging request
-- to update blanket amount
, nvl(lines.order_uom_selling_price, lines.ADJUSTED_UNIT_PRICE*nvl(lines.priced_quantity,l.ordered_quantity)/l.ordered_quantity) new_selling_price
, l.blanket_number
, l.blanket_line_number
, l.unit_selling_price
, l.ordered_quantity
, l.pricing_quantity
, l.order_quantity_uom
, l.fulfilled_flag
, l.line_set_id
, l.inventory_item_id
, l.sold_to_org_id
, l.transaction_phase_code--for bug 3108881
, l.order_source_id
FROM QP_PREQ_LINES_TMP lines
, OE_ORDER_LINES l
WHERE lines.pricing_status_code IN
( QP_PREQ_GRP.G_STATUS_UPDATED
, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
AND lines.process_status <> 'NOT_VALID'
AND lines.line_type_code='LINE'
AND (lines.adjusted_unit_price <> nvl(l.unit_selling_price_per_pqty,0)
or nvl(lines.order_uom_Selling_price,0) <> nvl(l.unit_selling_price,0))
AND l.ordered_quantity <> 0 -- bug 3958480
AND l.line_id = lines.line_id;
/*SELECT DECODE( TAX_CALCULATION_EVENT_CODE, 'ENTERING', 0,
'BOOKING', 1,
'SHIPPING', 2,
'INVOICING', 3,
-1)
into l_tax_event_code
from oe_transaction_types_all
where transaction_type_id = OE_Order_pub.g_hdr.order_type_id;*/
for update_line in updated_lines loop
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'LOG REQUEST FOR UPDATED LINE '||UPDATE_LINE.LINE_ID ) ;
oe_debug_pub.add('adjusted_unit_price : '||update_line.adjusted_unit_price);
oe_debug_pub.add('order_uom_selling_price : '||update_line.ousp);
oe_debug_pub.add('unit_selling_price_per_pqty : '||update_line.unit_selling_price_per_pqty);
oe_debug_pub.add('unit_selling_price : '||update_line.usp);
l_line_rec.line_id := update_line.line_id;
l_line_rec.header_id := update_line.header_id;
l_line_rec.reference_line_id := update_line.reference_line_id;
l_line_rec.line_type_id := update_line.line_type_id;
l_line_rec.org_id := update_line.org_id;
l_line_rec.line_category_code := update_line.line_category_code;
l_line_rec.return_context := update_line.return_context;
:= update_line.reference_customer_trx_line_id;
(update_line.line_type_id,
l_line_rec);
/*if update_line.commitment_id is not null
and update_line.commitment_id <> FND_API.G_MISS_NUM then
begin
select nvl(tax_calculation_flag,'N') into l_commt_tax_flag
from ra_cust_trx_types_all ract where ract.cust_trx_type_id =
(
select nvl(cust_type.subsequent_trx_type_id,cust_type.cust_trx_type_id)
from ra_cust_trx_types cust_type,ra_customer_trx_all cust_trx where
cust_type.cust_trx_type_id = cust_trx.cust_trx_type_id
and cust_trx.customer_trx_id = update_line.commitment_id
);
(l_tax_event_code = 1 AND nvl(update_line.booked_flag,'X') = 'Y') OR
(l_tax_event_code = 2 AND (update_line.shippable_flag = 'N' OR (update_line.shippable_flag = 'Y' and update_line.shipped_quantity IS NOT NULL))) OR
l_tax_event_code = -1)
--AND 7306510 (l_tax_calculation_flag = 'Y' OR
--update_line.tax_exempt_flag = 'R' OR l_commt_tax_flag = 'Y' )
) THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR TAXING' ) ;
p_entity_id => update_line.line_id,
p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
p_requesting_entity_id => update_line.line_id,
p_request_type => OE_GLOBALS.g_tax_line,
x_return_status => l_return_status);
p_entity_id => update_line.line_id,
p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
p_requesting_entity_id => update_line.line_id,
p_request_type => OE_GLOBALS.g_tax_line,
--p_param1 => l_param1,
x_return_status => l_return_status);
oe_debug_pub.add( 'CREDIT CARD:'||UPDATE_LINE.ADJUSTED_UNIT_PRICE||'>'||UPDATE_LINE.UNIT_SELLING_PRICE_PER_PQTY||'?' ) ;
IF update_line.adjusted_unit_price > update_line.unit_selling_price_per_pqty or
nvl(update_line.ousp,0) > nvl(update_line.usp,0)
THEN
-- Log Request if commitment id is NULL
IF update_line.commitment_id is NULL THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'LOG VERIFY PAYMENT DELAYED REQUSET IN SELLING PRICE' ) ;
oe_debug_pub.add( 'COMMITMENT:'||UPDATE_LINE.COMMITMENT_ID ) ;
oe_debug_pub.add( 'PAYMENT TYPE:'||OE_ORDER_PUB.G_HDR.PAYMENT_TYPE_CODE||' BOOKED?'||UPDATE_LINE.BOOKED_FLAG ) ;
IF nvl(update_line.booked_flag,'X') = 'Y' THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'LOG VERIFY PAYMENT REQUEST' ) ;
IF (l_verify_payment = 'Y' AND update_line.line_category_code <> 'RETURN') THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR VERIFY PAYMENT' ) ;
p_requesting_entity_id => update_line.line_id,
p_request_type => OE_GLOBALS.G_VERIFY_PAYMENT,
x_return_status => l_return_status);
oe_debug_pub.add( 'COMMITMENT '||UPDATE_LINE.COMMITMENT_ID ) ;
IF l_commitment_sequencing AND update_line.commitment_id IS NOT NULL THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR COMMITMENT.' , 2 ) ;
p_entity_id => update_line.line_id,
p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
p_requesting_entity_id => update_line.line_id,
p_request_type => OE_GLOBALS.G_CALCULATE_COMMITMENT,
x_return_status => l_return_status);
oe_debug_pub.add('old SP :'||update_line.unit_selling_price);
oe_debug_pub.add('new SP :'||update_line.new_selling_price);
AND update_line.line_category_code = 'ORDER'
AND update_line.blanket_number IS NOT NULL
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('OEXVOPRB log blanket request');
oe_debug_pub.add('old SP :'||update_line.unit_selling_price);
oe_debug_pub.add('new SP :'||update_line.new_selling_price);
OE_Order_Cache.Load_Order_Header(update_line.header_id);
IF nvl(update_line.transaction_phase_code,'F') = 'F' THEN
OE_Delayed_Requests_Pvt.Log_Request
(p_entity_code => OE_GLOBALS.G_ENTITY_ALL
,p_entity_id => update_line.line_id
,p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE
,p_requesting_entity_id => update_line.line_id
,p_request_type => OE_GLOBALS.G_PROCESS_RELEASE
-- Old values
,p_param1 => update_line.blanket_number
,p_param2 => update_line.blanket_line_number
,p_param3 => update_line.ordered_quantity
,p_param4 => update_line.order_quantity_uom
,p_param5 => update_line.unit_selling_price
,p_param6 => update_line.inventory_item_id
-- New values
,p_param11 => update_line.blanket_number
,p_param12 => update_line.blanket_line_number
,p_param13 => update_line.ordered_quantity
,p_param14 => update_line.order_quantity_uom
,p_param15 => update_line.new_selling_price
,p_param16 => update_line.inventory_item_id
-- Other parameters
,p_param8 => update_line.fulfilled_flag
,p_param9 => update_line.line_set_id
,p_request_unique_key1 =>
OE_Order_Cache.g_header_rec.transactional_curr_code
,x_return_status => l_return_status
);
IF update_line.line_set_id IS NOT NULL THEN
OE_Delayed_Requests_Pvt.Log_Request
(p_entity_code => OE_GLOBALS.G_ENTITY_ALL
,p_entity_id => update_line.line_set_id
,p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE
,p_requesting_entity_id => update_line.line_id
,p_request_type => 'VALIDATE_RELEASE_SHIPMENTS'
,p_request_unique_key1 => update_line.blanket_number
,p_request_unique_key2 => update_line.blanket_line_number
,p_param1 =>
OE_Order_Cache.g_header_rec.transactional_curr_code
,x_return_status => l_return_status
);
IF update_line.booked_flag = 'Y' THEN
IF OE_FEATURES_PVT.Is_Margin_Avail THEN
IF Oe_Sys_Parameters.Value('COMPUTE_MARGIN') <> 'N' Then
l_log_mrg_hold_req := 'Y';
l_header_id := update_line.header_id;
SELECT processed_flag
INTO l_order_adj_changed_flag
FROM qp_preq_lines_tmp
WHERE line_type_code='ORDER' and price_flag='Y';
oe_debug_pub.add( 'LOGGING REQUEST TO UPDATE ALL LINES FOR HEADER LEVEL ADJUSTMENT.' , 1 ) ;
PROCEDURE UPDATE_ORDER_LINES(
px_line_tbl IN OUT NOCOPY OE_ORDER_PUB.LINE_TBL_TYPE
, x_num_changed_lines OUT NOCOPY NUMBER
,p_write_to_db BOOLEAN DEFAULT FALSE
)
IS
l_return_status varchar2(1);
Select Waiting_Session,Holding_session,lock_type,MODE_HELD,MODE_REQUESTED,LOCK_ID1,LOCK_ID2
From dba_waiters;
oe_debug_pub.add('Calling update retrobill lines');
Oe_Retrobill_Pvt.UPDATE_RETROBILL_LINES(G_RETROBILL_OPERATION);
oe_debug_pub.add( ' ENTERING OE_ORDER_PRICE_PVT.UPDATE_ORDER_LINES' ) ;
UPDATE OE_ORDER_LINES_all l
SET (UNIT_SELLING_PRICE
, UNIT_LIST_PRICE
,UNIT_SELLING_PRICE_PER_PQTY
,UNIT_LIST_PRICE_PER_PQTY
, PRICING_QUANTITY
, PRICING_QUANTITY_UOM
, PRICE_LIST_ID
, PRICE_REQUEST_CODE
, UNIT_LIST_PERCENT
, UNIT_PERCENT_BASE_PRICE
, UNIT_SELLING_PERCENT
, LOCK_CONTROL
, LAST_UPDATE_DATE -- Added WHO columns for the bug 3105197
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN)
=
(select
nvl(lines.order_uom_selling_price, lines.ADJUSTED_UNIT_PRICE*nvl(lines.priced_quantity,l.ordered_quantity)/l.ordered_quantity)
, nvl(lines.line_unit_price, lines.UNIT_PRICE*nvl(lines.priced_quantity,l.ordered_quantity)/l.ordered_quantity)
, lines.ADJUSTED_UNIT_PRICE
, lines.UNIT_PRICE
, decode(lines.priced_quantity,-99999,l.ordered_quantity,lines.priced_quantity)
, decode(lines.priced_quantity,-99999,l.order_quantity_uom,lines.priced_uom_code)
, decode(lines.price_list_header_id,-9999,NULL,lines.price_list_header_id) --Bug#2830609
, lines.price_request_code
, 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)
, l.lock_control + 1
, SYSDATE
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
from qp_preq_lines_tmp lines
where lines.line_id=l.line_id
and l.open_flag <> 'N'
and lines.line_type_code='LINE'
and lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
and lines.process_status <> 'NOT_VALID'
)
where
--l.header_id=oe_order_pub.g_hdr.header_id
--and
l.ordered_quantity <> 0
and l.open_flag <> 'N'
and l.line_id in (select line_id from qp_preq_lines_tmp lines
where
lines.pricing_status_code in
(QP_PREQ_GRP.G_STATUS_UPDATED,
QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
and lines.process_status <> 'NOT_VALID'
and
lines.line_type_code='LINE');
oe_debug_pub.add( ' LEAVING UPDATE_ORDER_LINES:'||SQL%ROWCOUNT , 3 ) ;
oe_debug_pub.add( 'WRONG IN UPDATE_ORDER_LINES'||SQLERRM , 1 ) ;
oe_debug_pub.add( 'WRONG IN UPDATE_ORDER_LINES'||SQLERRM , 1 ) ;
End UPDATE_ORDER_LINES;
PROCEDURE DELETE_DEPENDENTS(
p_adj_id_tbl IN OUT NOCOPY NUMBER_TYPE,
p_header_id_tbl IN OUT NOCOPY NUMBER_TYPE,
p_line_id_tbl IN OUT NOCOPY NUMBER_TYPE,
p_list_line_id_tbl IN OUT NOCOPY NUMBER_TYPE,
p_list_header_id_tbl IN OUT NOCOPY NUMBER_TYPE,
p_list_line_type_code_tbl IN OUT NOCOPY VARCHAR_TYPE,
p_applied_flag_tbl IN OUT NOCOPY VARCHAR_TYPE,
p_adjusted_amount_tbl IN OUT NOCOPY NUMBER_TYPE
) IS
i NUMBER;
oe_debug_pub.add('l_booked_flag in delete dependents = '||l_booked_flag);
oe_debug_pub.add('Delete adjustments notify to OC');
l_Line_Adj_rec.operation := OE_GLOBALS.G_OPR_DELETE;
l_Line_Adj_rec.last_update_date := sysdate;
oe_debug_pub.add('last_update_date:'||l_Line_Adj_rec.last_update_date);
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_line_adj_rec =>l_line_adj_rec,
p_line_adj_id => l_line_adj_rec.price_adjustment_id,
p_old_line_adj_rec =>l_line_adj_rec,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS', 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS' ) ;
oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS', 1 ) ;
DELETE FROM OE_PRICE_ADJ_ATTRIBS WHERE price_adjustment_id = p_adj_id_tbl(i);
oe_debug_pub.add( ' DELETED '||SQL%ROWCOUNT||' ATTRIBS' , 3 ) ;
DELETE FROM OE_PRICE_ADJ_ASSOCS WHERE price_adjustment_id = p_adj_id_tbl(i);
oe_debug_pub.add( ' DELETED '||SQL%ROWCOUNT||' ASSOCS' , 3 ) ;
DELETE FROM OE_PRICE_ADJ_ASSOCS WHERE rltd_price_adj_id = p_adj_id_tbl(i);
oe_debug_pub.add( ' DELETED '||SQL%ROWCOUNT||' RLTD ASSOCS' , 3 ) ;
p_adj_id_tbl.delete;
END DELETE_DEPENDENTS;
PROCEDURE DELETE_HDR_ADJS( p_pricing_events varchar2
,p_hdr_line_id number
,p_hdr_line_index number
,p_hdr_price_flag varchar2
,p_hdr_pricing_status_code varchar2)
IS
l_adj_id_tbl Number_Type;
(QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
)
THEN
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE HEADER_ID=oe_order_pub.g_hdr.header_id
AND LINE_ID IS NULL
AND LIST_LINE_TYPE_CODE NOT IN ('TAX')
AND NVL(UPDATED_FLAG, 'N')='N'
AND PRICING_PHASE_ID IN (select b.pricing_phase_id
from qp_event_phases a,
qp_pricing_phases b
where instr(p_pricing_events, a.pricing_event_code||',') > 0
and b.pricing_phase_id = a.pricing_phase_id
and nvl(b.user_freeze_override_flag,freeze_override_flag)
= decode(p_hdr_price_flag, 'Y', nvl(b.user_freeze_override_flag,b.freeze_override_flag), 'P', 'Y'))
AND HEADER_ID = p_hdr_line_id
AND list_line_id not in (select list_line_id from qp_ldets_v ld
where ld.process_code in (QP_PREQ_GRP.G_STATUS_NEW, --bug 4190357
QP_PREQ_GRP.G_STATUS_UPDATED,
QP_PREQ_GRP.G_STATUS_UNCHANGED)
and ld.line_index = p_hdr_line_index
and p_hdr_line_id = oe_order_pub.g_hdr.header_id)
returning price_adjustment_id,
header_id,
line_id,
list_line_id,
list_header_id,
list_line_type_code,
applied_flag,
adjusted_amount
bulk collect into
l_adj_id_tbl,
l_header_id_tbl,
l_line_id_tbl,
l_list_line_id_tbl,
l_list_header_id_tbl,
l_list_line_type_code_tbl,
l_applied_flag_tbl,
l_adjusted_amount_tbl;
oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||' HEADER LEVEL ADJUSTMENTS/CHARGES' , 3 ) ;
DELETE_DEPENDENTS(l_adj_id_tbl,l_header_id_tbl,l_line_id_tbl,l_list_line_id_tbl,l_list_header_id_tbl,l_list_line_type_code_tbl,l_applied_flag_tbl,l_adjusted_amount_tbl);
END DELETE_HDR_ADJS;
PROCEDURE DELETE_LINES_ADJS(p_pricing_events IN varchar2)
IS
l_line_type_code varchar2(6);
cursor updated_order_lines(l_line_type_code in varchar2) is
select line_id, price_flag, line_index from qp_preq_lines_tmp
where price_flag IN ('Y','P')
and line_type_code = l_line_type_code
and process_status <> 'NOT_VALID'
and pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED
, QP_PREQ_PUB.G_STATUS_GSA_VIOLATION);
For one_line in updated_order_lines(l_line_type_code) loop
DELETE /*+ index (adj oe_price_adjustments_n2) */
FROM OE_PRICE_ADJUSTMENTS adj
WHERE --HEADER_ID=oe_order_pub.g_hdr.header_id
LINE_ID = one_line.line_id
AND LIST_LINE_TYPE_CODE NOT IN ('TAX','IUE') --bug 2858712
AND NVL(UPDATED_FLAG, 'N')='N'
AND PRICING_PHASE_ID IN (select b.pricing_phase_id
from qp_event_phases a,
qp_pricing_phases b
where instr(p_pricing_events, a.pricing_event_code||',') > 0
and b.pricing_phase_id = a.pricing_phase_id
and nvl(b.user_freeze_override_flag,freeze_override_flag)
= decode(one_line.price_flag, 'Y', nvl(b.user_freeze_override_flag,b.freeze_override_flag), 'P', 'Y'))
AND list_line_id not in (select list_line_id from qp_ldets_v ld
where ld. process_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_NEW)
and ld.line_index = one_line.line_index
and (ld.applied_flag = decode(one_line.price_flag, 'Y', 'Y', 'P', ld.applied_flag)
OR
((nvl(ld.applied_flag,'N') = decode(one_line.price_flag, 'Y', 'N', 'P', nvl(ld.applied_flag,'N'))
AND
nvl(ld.line_detail_type_code,'x') = decode(one_line.price_flag, 'Y', 'CHILD_DETAIL_LINE', 'P', nvl(ld.line_detail_type_code,'x'))
))
))
returning price_adjustment_id,
header_id,
line_id,
list_line_id,
list_header_id,
list_line_type_code,
applied_flag,
adjusted_amount
bulk collect into
l_adj_id_tbl,
l_header_id_tbl,
l_line_id_tbl,
l_list_line_id_tbl,
l_list_header_id_tbl,
l_list_line_type_code_tbl,
l_applied_flag_tbl,
l_adjusted_amount_tbl;
oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||' LINE LEVEL ADJUSTMENTS/CHARGES FOR LINE:'||ONE_LINE.LINE_ID , 3 ) ;
DELETE_DEPENDENTS(l_adj_id_tbl,l_header_id_tbl,l_line_id_tbl,l_list_line_id_tbl,l_list_header_id_tbl,l_list_line_type_code_tbl,l_applied_flag_tbl,l_adjusted_amount_tbl);
END DELETE_LINES_ADJS;
PROCEDURE DELETE_ONE_LINE_ADJS(p_line_id in number, p_pricing_events IN varchar2)
IS
l_line_id Number;
select line_id,
price_flag
into l_line_id,
l_price_flag
from qp_preq_lines_tmp
where line_id = p_line_id
and line_type_code = 'LINE'
and price_flag in ('Y', 'P')
and process_status <> 'NOT_VALID'
and pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION);
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE HEADER_ID=oe_order_pub.g_hdr.header_id
AND LINE_ID=p_line_id
AND NVL(UPDATED_FLAG, 'N')='N'
AND PRICING_PHASE_ID IN (select b.pricing_phase_id
from qp_event_phases a,
qp_pricing_phases b
where instr(p_pricing_events, a.pricing_event_code||',') > 0
and b.pricing_phase_id = a.pricing_phase_id
and nvl(b.user_freeze_override_flag,b.freeze_override_flag)
= decode(l_price_flag, 'Y', nvl(b.user_freeze_override_flag,b.freeze_override_flag), 'P', 'Y'))
AND LINE_ID = p_line_id
AND LIST_LINE_TYPE_CODE NOT IN ('TAX','IUE') --bug 2858712
AND list_line_id not in (select list_line_id
from qp_ldets_v ld
where ld.process_code in
(QP_PREQ_GRP.G_STATUS_UPDATED,
QP_PREQ_GRP.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_NEW)
and (ld.applied_flag = 'Y'
OR
(nvl(ld.applied_flag,'N') = 'N'
AND
nvl(ld.line_detail_type_code,'x') = 'CHILD_DETAIL_LINE'
)
)
)
returning price_adjustment_id,
header_id,
line_id,
list_line_id,
list_header_id,
list_line_type_code,
applied_flag,
adjusted_amount
bulk collect into
l_adj_id_tbl,
l_header_id_tbl,
l_line_id_tbl,
l_list_line_id_tbl,
l_list_header_id_tbl,
l_list_line_type_code_tbl,
l_applied_flag_tbl,
l_adjusted_amount_tbl;
oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||' LINE LEVEL ADJUSTMENTS/CHARGES' , 3 ) ;
DELETE_DEPENDENTS(l_adj_id_tbl,l_header_id_tbl,l_line_id_tbl,l_list_line_id_tbl,l_list_header_id_tbl,l_list_line_type_code_tbl,l_applied_flag_tbl,l_adjusted_amount_tbl);
END DELETE_ONE_LINE_ADJS;
PROCEDURE update_adj(
p_price_adjustment_id IN NUMBER
,p_line_detail_index IN NUMBER
,px_debug_upd_adj_tbl OUT NOCOPY NUMBER_TYPE
)
IS
l_price_adjustment_id NUMBER;
oe_debug_pub.add('entering procedure update_adj');
SELECT price_adjustment_id
INTO l_price_adjustment_id
FROM oe_price_adjustments
WHERE price_adjustment_id = p_price_adjustment_id
FOR UPDATE NOWAIT;
UPDATE OE_PRICE_ADJUSTMENTS adj
SET ( operand
, operand_per_pqty
, adjusted_amount
, adjusted_amount_per_pqty
, arithmetic_operator
, pricing_phase_id
, pricing_group_sequence
, automatic_flag
, list_line_type_code
--, applied_flag
, modified_from
, modified_to
, update_allowed
--, modifier_mechanism_type_code
--, updated_flag
, charge_type_code
, charge_subtype_code
, range_break_quantity
, accrual_conversion_rate
, accrual_flag
, list_line_no
--, source_system_code
, benefit_qty
, benefit_uom_code
, print_on_invoice_flag
, expiration_date
, rebate_transaction_type_code
--, rebate_transaction_reference
--, rebate_payment_system_code
--, redeemed_date
--, redeemed_Flag
, modifier_level_code
, price_break_type_code
, substitution_attribute
, proration_type_code
, include_on_returns_flag
, lock_control
--bug#7369643
--This code is added to update the last update information in who columns
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
--bug#7369643
)
=
(select
ldets.order_qty_operand
, ldets.operand_value
, ldets.order_qty_adj_amt
, ldets.adjustment_amount
, ldets.operand_calculation_code
, ldets.pricing_phase_id
, ldets.pricing_group_sequence
, ldets.automatic_flag
, ldets.list_line_type_code
--, ldets.applied_flag
, decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute,
'IUE', to_char(ldets.inventory_item_id), NULL)
, decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to,
'IUE',to_char(ldets.related_item_id), NULL)
, ldets.override_flag
--, modifier_mechanism_type_code
--, ldets.updated_flag
, ldets.charge_type_code
, ldets.charge_subtype_code
, ldets.line_quantity --range_break_quantity (?)
, ldets.accrual_conversion_rate
, ldets.accrual_flag
, ldets.list_line_no
--, source_system_code
, ldets.benefit_qty
, ldets.benefit_uom_code
, ldets.print_on_invoice_flag
, ldets.expiration_date
, ldets.rebate_transaction_type_code
--, rebate_transaction_reference
--, rebate_payment_system_code
--, redeemed_date
--, redeemed_Flag
, ldets.modifier_level_code
, ldets.price_break_type_code
, ldets.substitution_attribute
, ldets.proration_type_code
, ldets.include_on_returns_flag
, adj.lock_control + 1
--bug#7369643
--This code is added to update the last update information in who columns
, sysdate
, fnd_global.user_id
, fnd_global.LOGIN_ID
--bug#7369643
from
QP_LDETS_v ldets
where ldets.line_detail_index = p_line_detail_index
)
where adj.price_adjustment_id = p_price_adjustment_id
returning adj.list_line_id bulk collect into px_debug_upd_adj_tbl;
oe_debug_pub.add('exiting update_adj procedure');
END update_adj;
Select /*+ ORDERED USE_NL(ADJ LDETS) index(QPLINES QP_PREQ_LINES_TMP_N2) index(ADJ OE_PRICE_ADJUSTMENTS_N2)*/
adj.price_adjustment_id, ldets.line_detail_index
From QP_PREQ_LINES_TMP QPLINES
,OE_PRICE_ADJUSTMENTS ADJ
,QP_LDETS_V LDETS
Where LDETS.LIST_LINE_ID = ADJ.LIST_LINE_ID
AND LDETS.LINE_INDEX = QPLINES.LINE_INDEX
AND ADJ.PRICING_PHASE_ID in (select pricing_phase_id from qp_event_phases
-- where pricing_event_code = p_pricing_events)
--changes to enable multiple events passed as a string
where instr(l_pricing_events, pricing_event_code||',') > 0)
AND LDETS.PROCESS_CODE in (QP_PREQ_GRP.G_STATUS_UNCHANGED,
QP_PREQ_GRP.G_STATUS_UPDATED)
AND nvl(ADJ.updated_flag,'N') = 'N'
AND QPLINES.LINE_ID = ADJ.LINE_ID
AND QPLINES.PROCESS_STATUS <> 'NOT_VALID'
AND QPLINES.LINE_TYPE_CODE = 'LINE';
Select ADJ.PRICE_ADJUSTMENT_ID, LDETS.LINE_DETAIL_INDEX
From
OE_PRICE_ADJUSTMENTS ADJ
,QP_LDETS_V LDETS
Where LDETS.LIST_LINE_ID = ADJ.LIST_LINE_ID
AND LDETS.LINE_INDEX = p_hdr_line_index
AND ADJ.PRICING_PHASE_ID in (select pricing_phase_id from qp_event_phases
-- where pricing_event_code = p_pricing_events)
--changes to enable multiple events passed as a string
where instr(l_pricing_events, pricing_event_code||',') > 0)
AND LDETS.PROCESS_CODE in (QP_PREQ_GRP.G_STATUS_UNCHANGED,
QP_PREQ_GRP.G_STATUS_UPDATED)
AND nvl(ADJ.updated_flag,'N') = 'N'
AND ADJ.HEADER_ID = p_hdr_line_id;
select ldets.list_line_id,
ldets.adjustment_amount,
ldets.order_qty_adj_amt,
ldets.order_qty_operand,
ldets.operand_calculation_code,
ldets.operand_value,
lines.priced_quantity,
lines.line_quantity,
ldets.pricing_phase_id,
ldets.pricing_group_sequence,
ldets.automatic_flag,
ldets.list_line_type_code,
ldets.applied_flag,
ldets.substitution_attribute,
ldets.inventory_item_id,
ldets.substitution_value_to,
ldets.related_item_id,
ldets.override_flag,
ldets.updated_flag,
ldets.charge_type_code,
ldets.charge_subtype_code,
ldets.accrual_conversion_rate,
ldets.accrual_flag,
ldets.list_line_no,
ldets.benefit_qty,
ldets.benefit_uom_code,
ldets.print_on_invoice_flag,
ldets.expiration_date,
ldets.rebate_transaction_type_code,
ldets.modifier_level_code,
ldets.price_break_type_code,
ldets.proration_type_code,
ldets.include_on_returns_flag,
adj.lock_control + 1 adj_lock_control
from OE_PRICE_ADJUSTMENTS adj
, QP_LDETS_v ldets
, QP_PREQ_LINES_TMP lines
WHERE
adj.header_id=oe_order_pub.g_hdr.header_id
and lines.line_index = ldets.line_index
and lines.process_status <> 'NOT_VALID'
and ldets.list_line_id = adj.list_line_id
and lines.line_type_code='ORDER' and lines.line_id=adj.header_id
and ldets.process_code = QP_PREQ_GRP.G_STATUS_UPDATED;
select ldets.list_line_id,
ldets.adjustment_amount,
ldets.order_qty_adj_amt,
ldets.order_qty_operand,
ldets.operand_calculation_code,
ldets.operand_value,
lines.priced_quantity,
lines.line_quantity,
ldets.pricing_phase_id,
ldets.pricing_group_sequence,
ldets.automatic_flag,
ldets.list_line_type_code,
ldets.applied_flag,
ldets.substitution_attribute,
ldets.inventory_item_id,
ldets.substitution_value_to,
ldets.related_item_id,
ldets.override_flag,
ldets.updated_flag,
ldets.charge_type_code,
ldets.charge_subtype_code,
ldets.accrual_conversion_rate,
ldets.accrual_flag,
ldets.list_line_no,
ldets.benefit_qty,
ldets.benefit_uom_code,
ldets.print_on_invoice_flag,
ldets.expiration_date,
ldets.rebate_transaction_type_code,
ldets.modifier_level_code,
ldets.price_break_type_code,
ldets.proration_type_code,
ldets.include_on_returns_flag,
adj.lock_control + 1 adj_lock_control
from OE_PRICE_ADJUSTMENTS adj
, QP_LDETS_v ldets
, QP_PREQ_LINES_TMP lines
WHERE
adj.header_id=oe_order_pub.g_hdr.header_id
and lines.line_index = ldets.line_index
and lines.process_status <> 'NOT_VALID'
and ldets.list_line_id = adj.list_line_id
and lines.line_type_code='LINE' and lines.line_id=adj.line_id
and ldets.process_code = QP_PREQ_GRP.G_STATUS_UPDATED;
select ldets2.price_adjustment_id, ldets2.line_detail_index
from qp_ldets_v ldets2, QP_PREQ_LINES_TMP lines2
where ldets2.process_code=QP_PREQ_GRP.G_STATUS_UPDATED
AND lines2.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
and lines2.process_status <> 'NOT_VALID'
and lines2.line_index = ldets2.line_index
and lines2.line_type_code = l_line_type
AND (l_booked_flag = 'N' or ldets2.list_line_type_code<>'IUE');
select price_flag,
line_index,
line_id,
pricing_status_code
into l_hdr_price_flag,
l_hdr_line_index,
l_hdr_line_id,
l_hdr_pricing_status_code
from
qp_preq_lines_tmp
where line_type_code = 'ORDER'
and process_status <> 'NOT_VALID';
delete_hdr_adjs(l_pricing_events,l_hdr_line_id,l_hdr_line_index,l_hdr_price_flag,l_hdr_pricing_status_code);
delete_lines_adjs(l_pricing_events);
delete_one_line_Adjs(p_line_id,l_pricing_events);
OE_RETROBILL_PVT.Update_Invalid_Diff_Adj;
update_adj(i.price_adjustment_id, i.line_detail_index , l_debug_upd_order_adj_tbl);
oe_debug_pub.add('after update_adj order');
oe_debug_pub.add( 'UPDATED '||SQL%ROWCOUNT||' ORDER LEVEL ADJUSTMENTS' , 3 ) ;
update_adj(i.price_adjustment_id, i.line_detail_index, l_debug_upd_line_adj_tbl);
oe_debug_pub.add('after update_adj for line');
oe_debug_pub.add( 'UPDATED '||SQL%ROWCOUNT||' LINE LEVEL ADJUSTMENTS' , 3 ) ;
oe_debug_pub.add('UPDATED ORDER LEVEL ADJ LIST LINE IDS ARE:');
oe_debug_pub.add('UPDATED LINE LEVEL ADJ LIST LINE IDS ARE:');
OE_ADV_PRICE_PVT.Insert_Adj(p_header_id);
INSERT INTO OE_PRICE_ADJUSTMENTS
( PRICE_ADJUSTMENT_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HEADER_ID
, DISCOUNT_ID
, DISCOUNT_LINE_ID
, AUTOMATIC_FLAG
, PERCENT
, LINE_ID
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORIG_SYS_DISCOUNT_REF
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_TYPE_CODE
, MODIFIER_MECHANISM_TYPE_CODE
, MODIFIED_FROM
, MODIFIED_TO
, UPDATED_FLAG
, UPDATE_ALLOWED
, APPLIED_FLAG
, CHANGE_REASON_CODE
, CHANGE_REASON_TEXT
, operand
, Arithmetic_operator
, COST_ID
, TAX_CODE
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, PARENT_ADJUSTMENT_ID
, INVOICED_FLAG
, ESTIMATED_FLAG
, INC_IN_SALES_PERFORMANCE
, SPLIT_ACTION_CODE
, ADJUSTED_AMOUNT
, PRICING_PHASE_ID
, CHARGE_TYPE_CODE
, CHARGE_SUBTYPE_CODE
, list_line_no
, source_system_code
, benefit_qty
, benefit_uom_code
, print_on_invoice_flag
, expiration_date
, rebate_transaction_type_code
, rebate_transaction_reference
, rebate_payment_system_code
, redeemed_date
, redeemed_flag
, accrual_flag
, range_break_quantity
, accrual_conversion_rate
, pricing_group_sequence
, modifier_level_code
, price_break_type_code
, substitution_attribute
, proration_type_code
, CREDIT_OR_CHARGE_FLAG
, INCLUDE_ON_RETURNS_FLAG
, AC_CONTEXT
, AC_ATTRIBUTE1
, AC_ATTRIBUTE2
, AC_ATTRIBUTE3
, AC_ATTRIBUTE4
, AC_ATTRIBUTE5
, AC_ATTRIBUTE6
, AC_ATTRIBUTE7
, AC_ATTRIBUTE8
, AC_ATTRIBUTE9
, AC_ATTRIBUTE10
, AC_ATTRIBUTE11
, AC_ATTRIBUTE12
, AC_ATTRIBUTE13
, AC_ATTRIBUTE14
, AC_ATTRIBUTE15
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, LOCK_CONTROL
)
( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
, sysdate --p_Line_Adj_rec.creation_date
, fnd_global.user_id --p_Line_Adj_rec.created_by
, sysdate --p_Line_Adj_rec.last_update_date
, fnd_global.user_id --p_Line_Adj_rec.last_updated_by
, fnd_global.login_id --p_Line_Adj_rec.last_update_login
, NULL --p_Line_Adj_rec.program_application_id
, NULL --p_Line_Adj_rec.program_id
, NULL --p_Line_Adj_rec.program_update_date
, NULL --p_Line_Adj_rec.request_id
, oe_order_pub.g_hdr.header_id --p_Line_Adj_rec.header_id
, NULL --p_Line_Adj_rec.discount_id
, NULL --p_Line_Adj_rec.discount_line_id
, ldets.automatic_flag
, NULL --p_Line_Adj_rec.percent
, decode(ldets.modifier_level_code,'ORDER',NULL,lines.line_id)
, NULL --p_Line_Adj_rec.context
, NULL --p_Line_Adj_rec.attribute1
, NULL --p_Line_Adj_rec.attribute2
, NULL --p_Line_Adj_rec.attribute3
, NULL --p_Line_Adj_rec.attribute4
, NULL --p_Line_Adj_rec.attribute5
, NULL --p_Line_Adj_rec.attribute6
, NULL --p_Line_Adj_rec.attribute7
, NULL --p_Line_Adj_rec.attribute8
, NULL --p_Line_Adj_rec.attribute9
, NULL --p_Line_Adj_rec.attribute10
, NULL --p_Line_Adj_rec.attribute11
, NULL --p_Line_Adj_rec.attribute12
, NULL --p_Line_Adj_rec.attribute13
, NULL --p_Line_Adj_rec.attribute14
, NULL --p_Line_Adj_rec.attribute15
, NULL --p_Line_Adj_rec.orig_sys_discount_ref
, ldets.LIST_HEADER_ID
, ldets.LIST_LINE_ID
, ldets.LIST_LINE_TYPE_CODE
, NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
, decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL)
, decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE', to_char(ldets.related_item_id), NULL)
, 'N' --p_Line_Adj_rec.UPDATED_FLAG
, ldets.override_flag
, ldets.APPLIED_FLAG
, NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
, NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
, nvl(ldets.order_qty_operand, decode(ldets.operand_calculation_code,
'%', ldets.operand_value,
'LUMPSUM', ldets.operand_value,
ldets.operand_value*lines.priced_quantity/lines.line_quantity))
, ldets.operand_calculation_code --p_Line_Adj_rec.arithmetic_operator
, NULl --p_line_Adj_rec.COST_ID
, NULL --p_line_Adj_rec.TAX_CODE
, NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
, NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
, NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
, NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
, NULL --p_line_Adj_rec.INVOICED_FLAG
, NULL --p_line_Adj_rec.ESTIMATED_FLAG
, NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
, NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
, nvl(ldets.order_qty_adj_amt, ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))
, ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
, ldets.CHARGE_TYPE_CODE
, ldets.CHARGE_SUBTYPE_CODE
, ldets.list_line_no
, qh.source_system_code
, ldets.benefit_qty
, ldets.benefit_uom_code
, NULL --p_Line_Adj_rec.print_on_invoice_flag
, ldets.expiration_date
, ldets.rebate_transaction_type_code
, NULL --p_Line_Adj_rec.rebate_transaction_reference
, NULL --p_Line_Adj_rec.rebate_payment_system_code
, NULL --p_Line_Adj_rec.redeemed_date
, NULL --p_Line_Adj_rec.redeemed_flag
, ldets.accrual_flag
, ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
, ldets.accrual_conversion_rate
, ldets.pricing_group_sequence
, ldets.modifier_level_code
, ldets.price_break_type_code
, ldets.substitution_attribute
, ldets.proration_type_code
, NULL --p_Line_Adj_rec.credit_or_charge_flag
, ldets.include_on_returns_flag
, NULL -- p_Line_Adj_rec.ac_context
, NULL -- p_Line_Adj_rec.ac_attribute1
, NULL -- p_Line_Adj_rec.ac_attribute2
, NULL -- p_Line_Adj_rec.ac_attribute3
, NULL -- p_Line_Adj_rec.ac_attribute4
, NULL -- p_Line_Adj_rec.ac_attribute5
, NULL -- p_Line_Adj_rec.ac_attribute6
, NULL -- p_Line_Adj_rec.ac_attribute7
, NULL -- p_Line_Adj_rec.ac_attribute8
, NULL -- p_Line_Adj_rec.ac_attribute9
, NULL -- p_Line_Adj_rec.ac_attribute10
, NULL -- p_Line_Adj_rec.ac_attribute11
, NULL -- p_Line_Adj_rec.ac_attribute12
, NULL -- p_Line_Adj_rec.ac_attribute13
, NULL -- p_Line_Adj_rec.ac_attribute14
, NULL -- p_Line_Adj_rec.ac_attribute15
, ldets.OPERAND_value
, ldets.adjustment_amount
, 1
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=QP_PREQ_GRP.G_STATUS_NEW
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')
);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' , 3 ) ;
/*Insert ASSO for header level adj
* Comment out--Not possible to have header level adjustments with associations
INSERT INTO OE_PRICE_ADJ_ASSOCS
( PRICE_ADJUSTMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PRICE_ADJ_ASSOC_ID
,LINE_ID
,RLTD_PRICE_ADJ_ID
,LOCK_CONTROL
)
(SELECT *+ ORDERED USE_NL(ADJ RADJ) *
adj.price_adjustment_id
,sysdate --p_Line_Adj_Assoc_Rec.creation_date
,fnd_global.user_id --p_Line_Adj_Assoc_Rec.CREATED_BY
,sysdate --p_Line_Adj_Assoc_Rec.LAST_UPDATE_DATE
,fnd_global.user_id --p_Line_Adj_Assoc_Rec.LAST_UPDATED_BY
,fnd_global.login_id --p_Line_Adj_Assoc_Rec.LAST_UPDATE_LOGIN
,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_APPLICATION_ID
,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_ID
,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_UPDATE_DATE
,NULL --p_Line_Adj_Assoc_Rec.REQUEST_ID
,OE_PRICE_ADJ_ASSOCS_S.nextval
,ADJ.LINE_ID
,RADJ.PRICE_ADJUSTMENT_ID
,1
FROM QP_PREQ_RLTD_LINES_TMP RLTD ,
OE_PRICE_ADJUSTMENTS ADJ,
OE_PRICE_ADJUSTMENTS RADJ,
QP_PREQ_LINES_TMP QPL
WHERE
ADJ.HEADER_ID = RLTD.LINE_INDEX AND
ADJ.LIST_LINE_ID = RLTD.LIST_LINE_ID AND
RADJ.HEADER_ID = RLTD.RELATED_LINE_INDEX AND
RADJ.LIST_LINE_ID = RLTD.RELATED_LIST_LINE_ID AND
RADJ.HEADER_ID = ADJ.HEADER_ID AND
RLTD.PRICING_STATUS_CODE = 'N' AND
QPL.LINE_INDEX = RLTD.LINE_INDEX AND
QPL.LINE_TYPE_CODE = 'ORDER');
INSERT INTO OE_PRICE_ADJ_ASSOCS
( PRICE_ADJUSTMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PRICE_ADJ_ASSOC_ID
,LINE_ID
,RLTD_PRICE_ADJ_ID
,LOCK_CONTROL
)
(SELECT /*+ ORDERED USE_NL(QPL ADJ RADJ) */
adj.price_adjustment_id
,sysdate --p_Line_Adj_Assoc_Rec.creation_date
,fnd_global.user_id --p_Line_Adj_Assoc_Rec.CREATED_BY
,sysdate --p_Line_Adj_Assoc_Rec.LAST_UPDATE_DATE
,fnd_global.user_id --p_Line_Adj_Assoc_Rec.LAST_UPDATED_BY
,fnd_global.login_id --p_Line_Adj_Assoc_Rec.LAST_UPDATE_LOGIN
,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_APPLICATION_ID
,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_ID
,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_UPDATE_DATE
,NULL --p_Line_Adj_Assoc_Rec.REQUEST_ID
,OE_PRICE_ADJ_ASSOCS_S.nextval
,ADJ.LINE_ID
,RADJ.PRICE_ADJUSTMENT_ID
,1
FROM
QP_PREQ_RLTD_LINES_TMP RLTD,
QP_PREQ_LINES_TMP QPL,
OE_PRICE_ADJUSTMENTS ADJ,
OE_PRICE_ADJUSTMENTS RADJ
WHERE QPL.LINE_INDEX = RLTD.LINE_INDEX AND
QPL.LINE_ID = ADJ.LINE_ID AND
QPL.LINE_TYPE_CODE = 'LINE' AND
QPL.PROCESS_STATUS <> 'NOT_VALID' AND
RLTD.LIST_LINE_ID = ADJ.LIST_LINE_ID AND
RLTD.RELATED_LINE_INDEX = QPL.LINE_INDEX AND
RLTD.RELATED_LIST_LINE_ID = RADJ.LIST_LINE_ID AND
ADJ.LINE_ID = RADJ.LINE_ID AND
RADJ.PRICE_ADJUSTMENT_ID
NOT IN (SELECT RLTD_PRICE_ADJ_ID
FROM OE_PRICE_ADJ_ASSOCS
WHERE PRICE_ADJUSTMENT_ID = ADJ.PRICE_ADJUSTMENT_ID ) AND
RLTD.PRICING_STATUS_CODE = 'N');
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' PRICE ADJ ASSOCS' , 3 ) ;
End If; -- insert_adj
l_adj_id_tbl.delete;
DELETE FROM OE_PRICE_ADJ_ATTRIBS WHERE price_adjustment_id = l_adj_id_tbl(i)
AND ( pricing_context
, pricing_attribute
, pricing_attr_value_from
, pricing_attr_value_to)
not in (select qplat.context
, qplat.attribute
, qplat.setup_value_from
, qplat.setup_value_to
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
-- , QP_PREQ_LDETS_TMP LDETS
-- , OE_PRICE_ADJUSTMENTS ADJ
WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
--AND QPLAT.LINE_INDEX = ADJ.HEADER_ID + nvl(ADJ.LINE_ID, 0)
--AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
--AND QPLAT.LINE_INDEX = LDETS.LINE_INDEX
--AND LDETS.LIST_LINE_ID=ADJ.LIST_LINE_ID
--AND LDETS.LIST_LINE_ID = l_list_line_id_tbl(i)
--AND LDETS.PROCESS_CODE IN (QP_PREQ_PUB.G_STATUS_UNCHANGED,
-- QP_PREQ_PUB.G_STATUS_UPDATED)
--AND LDETS.LINE_INDEX = ADJ.HEADER_ID + ADJ.LINE_ID
--AND LDETS.LINE_INDEX = oe_order_pub.g_hdr.header_id + l_line_id_tbl(i)
--AND ADJ.PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i)
) ;
oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||' ATTRIBUTES' ) ;
INSERT INTO OE_PRICE_ADJ_ATTRIBS
( PRICE_ADJUSTMENT_ID
,PRICING_CONTEXT
,PRICING_ATTRIBUTE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PRICING_ATTR_VALUE_FROM
,PRICING_ATTR_VALUE_TO
,COMPARISON_OPERATOR
,FLEX_TITLE
,PRICE_ADJ_ATTRIB_ID
,LOCK_CONTROL
)
(SELECT /*+ index (QPLAT QP_PREQ_LINE_ATTRS_TMP_N3) */
l_adj_id_tbl(i) --ADJ.PRICE_ADJUSTMENT_ID
,QPLAT.CONTEXT
,QPLAT.ATTRIBUTE
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,NULL
,QPLAT.SETUP_VALUE_FROM --VALUE_FROM
,QPLAT.SETUP_VALUE_TO --VALUE_TO
,QPLAT.COMPARISON_OPERATOR_TYPE_CODE
,decode(QPLAT.ATTRIBUTE_TYPE,
'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
,OE_PRICE_ADJ_ATTRIBS_S.nextval
,1
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
-- , QP_LDETS_v LDETS
-- , OE_PRICE_ADJUSTMENTS ADJ
WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
--AND LDETS.LINE_INDEX = ADJ.HEADER_ID + ADJ.LINE_ID
--AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
--AND QPLAT.LINE_INDEX = LDETS.LINE_INDEX
--AND LDETS.LIST_LINE_ID=ADJ.LIST_LINE_ID
--AND LDETS.PROCESS_CODE in (QP_PREQ_PUB.G_STATUS_UNCHANGED,
-- QP_PREQ_PUB.G_STATUS_UPDATED)
--AND ADJ.PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i)
AND ( qplat.context
, qplat.attribute
, qplat.setup_value_from
, qplat.setup_value_to)
not in (select pricing_context
, pricing_attribute
, pricing_attr_value_from
, pricing_attr_value_to
FROM OE_PRICE_ADJ_ATTRIBS
WHERE PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i)
));
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' CHANGED ATTRIBS' ) ;
l_adj_id_tbl.delete;
l_line_detail_index_tbl.delete;
DELETE FROM OE_PRICE_ADJ_ATTRIBS WHERE price_adjustment_id = l_adj_id_tbl(i)
AND ( pricing_context
, pricing_attribute
, pricing_attr_value_from
, pricing_attr_value_to)
not in (select qplat.context
, qplat.attribute
, qplat.setup_value_from
, qplat.setup_value_to
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
-- , QP_LDETS_v LDETS
-- , OE_PRICE_ADJUSTMENTS ADJ
WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
--AND QPLAT.LINE_INDEX = ADJ.HEADER_ID + nvl(ADJ.LINE_ID, 0)
--AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
--AND QPLAT.LINE_INDEX = LDETS.LINE_INDEX
--AND LDETS.LIST_LINE_ID=ADJ.LIST_LINE_ID
--AND LDETS.PROCESS_CODE IN (QP_PREQ_PUB.G_STATUS_UNCHANGED,
-- QP_PREQ_PUB.G_STATUS_UPDATED)
--AND LDETS.LINE_INDEX = ADJ.HEADER_ID
--AND ADJ.PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i)
) ;
oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||'ORDER ADJ LEVEL ATTRIBUTES' ) ;
INSERT INTO OE_PRICE_ADJ_ATTRIBS
( PRICE_ADJUSTMENT_ID
,PRICING_CONTEXT
,PRICING_ATTRIBUTE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PRICING_ATTR_VALUE_FROM
,PRICING_ATTR_VALUE_TO
,COMPARISON_OPERATOR
,FLEX_TITLE
,PRICE_ADJ_ATTRIB_ID
,LOCK_CONTROL
)
(SELECT /*+ index (QPLAT QP_PREQ_LINE_ATTRS_TMP_N3) */
l_adj_id_tbl(i) --ADJ.PRICE_ADJUSTMENT_ID
,QPLAT.CONTEXT
,QPLAT.ATTRIBUTE
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,NULL
,QPLAT.SETUP_VALUE_FROM --VALUE_FROM
,QPLAT.SETUP_VALUE_TO --VALUE_TO
,QPLAT.COMPARISON_OPERATOR_TYPE_CODE
,decode(QPLAT.ATTRIBUTE_TYPE,
'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
,OE_PRICE_ADJ_ATTRIBS_S.nextval
,1
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
--, QP_LDETS_v LDETS
--, OE_PRICE_ADJUSTMENTS ADJ
--, QP_PREQ_LINES_TMP QPL
WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
--AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
--AND QPLAT.LINE_INDEX = LDETS.LINE_INDEX
--AND LDETS.LIST_LINE_ID=ADJ.LIST_LINE_ID
--AND LDETS.PROCESS_CODE in (QP_PREQ_PUB.G_STATUS_UNCHANGED,
-- QP_PREQ_PUB.G_STATUS_UPDATED)
--AND LDETS.LINE_INDEX = ADJ.HEADER_ID
--AND ADJ.PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i)
AND ( qplat.context
, qplat.attribute
, qplat.setup_value_from
, qplat.setup_value_to)
not in (select pricing_context
, pricing_attribute
, pricing_attr_value_from
, pricing_attr_value_to
FROM OE_PRICE_ADJ_ATTRIBS
WHERE PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i)
));
/* Delete price_adj_attribs for order level adj*/
/*DELETE FROM Oe_Price_Adj_Attribs adjattrs
WHERE exists
(Select ADJ.PRICE_ADJUSTMENT_ID
From QP_LDETS_V LDETS
,OE_PRICE_ADJUSTMENTS ADJ
,QP_PREQ_LINES_TMP QPLINES
Where LDETS.LIST_LINE_ID = ADJ.LIST_LINE_ID
AND LDETS.LINE_INDEX = QPLINES.LINE_INDEX
AND QPLINES.LINE_TYPE_CODE = 'ORDER'
AND QPLINES.LINE_ID = ADJ.HEADER_ID
AND ADJ.price_adjustment_id = adjattrs.price_adjustment_id);*/
/* Delete price_adj_attribs for line level adj*/
/*DELETE FROM Oe_Price_Adj_Attribs adjattrs
WHERE exists
(Select ADJ.PRICE_ADJUSTMENT_ID
From QP_LDETS_V LDETS
,OE_PRICE_ADJUSTMENTS ADJ
,QP_PREQ_LINES_TMP QPLINES
Where LDETS.LIST_LINE_ID = ADJ.LIST_LINE_ID
AND LDETS.LINE_INDEX = QPLINES.LINE_INDEX
AND QPLINES.LINE_ID = ADJ.LINE_ID
AND QPLINES.LINE_TYPE_CODE = 'LINE'
AND ADJ.price_adjustment_id = adjattrs.price_adjustment_id);*/
/* insert header level adjustment attributes */
INSERT INTO OE_PRICE_ADJ_ATTRIBS
( PRICE_ADJUSTMENT_ID
,PRICING_CONTEXT
,PRICING_ATTRIBUTE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PRICING_ATTR_VALUE_FROM
,PRICING_ATTR_VALUE_TO
,COMPARISON_OPERATOR
,FLEX_TITLE
,PRICE_ADJ_ATTRIB_ID
,LOCK_CONTROL
)
(SELECT ADJ.PRICE_ADJUSTMENT_ID
,QPLAT.CONTEXT
,QPLAT.ATTRIBUTE
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,NULL
,QPLAT.SETUP_VALUE_FROM --VALUE_FROM
,QPLAT.SETUP_VALUE_TO --VALUE_TO
,QPLAT.COMPARISON_OPERATOR_TYPE_CODE
,decode(QPLAT.ATTRIBUTE_TYPE,
'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
,OE_PRICE_ADJ_ATTRIBS_S.nextval
,1
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
, QP_LDETS_v LDETS
, OE_PRICE_ADJUSTMENTS ADJ
WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
-- AND LDETS.LINE_INDEX = ADJ.HEADER_ID
AND ADJ.LINE_ID IS NULL
AND ADJ.HEADER_ID = oe_order_pub.g_hdr.header_id
AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.LINE_INDEX = LDETS.LINE_INDEX
AND LDETS.LIST_LINE_ID=ADJ.LIST_LINE_ID
AND LDETS.PROCESS_CODE=QP_PREQ_PUB.G_STATUS_NEW
AND LDETS.LINE_INDEX = l_hdr_line_index
AND l_hdr_line_id = oe_order_pub.g_hdr.header_id
--AND QPL.PRICING_STATUS_CODE IN (QP_PREQ_PUB.G_STATUS_UPDATED,
-- QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
);
INSERT INTO OE_PRICE_ADJ_ATTRIBS
( PRICE_ADJUSTMENT_ID
,PRICING_CONTEXT
,PRICING_ATTRIBUTE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PRICING_ATTR_VALUE_FROM
,PRICING_ATTR_VALUE_TO
,COMPARISON_OPERATOR
,FLEX_TITLE
,PRICE_ADJ_ATTRIB_ID
,LOCK_CONTROL
)
(SELECT ADJ.PRICE_ADJUSTMENT_ID
,QPLAT.CONTEXT
,QPLAT.ATTRIBUTE
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,NULL
,QPLAT.SETUP_VALUE_FROM --VALUE_FROM
,QPLAT.SETUP_VALUE_TO --VALUE_TO
,QPLAT.COMPARISON_OPERATOR_TYPE_CODE
,decode(QPLAT.ATTRIBUTE_TYPE,
'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
,OE_PRICE_ADJ_ATTRIBS_S.nextval
,1
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
, QP_LDETS_v LDETS
, OE_PRICE_ADJUSTMENTS ADJ
, QP_PREQ_LINES_TMP QPLINE
WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
-- AND QPLAT.LINE_INDEX = ADJ.HEADER_ID+nvl(ADJ.LINE_ID,0)
AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.LINE_INDEX = LDETS.LINE_INDEX
AND LDETS.LIST_LINE_ID=ADJ.LIST_LINE_ID
AND LDETS.PROCESS_CODE=QP_PREQ_PUB.G_STATUS_NEW
AND LDETS.LINE_INDEX = QPLINE.LINE_INDEX
-- AND ADJ.HEADER_ID = oe_order_pub.g_hdr.header_id
AND QPLINE.LINE_ID = ADJ.LINE_ID
AND QPLINE.LINE_TYPE_CODE = 'LINE'
AND QPLINE.PRICING_STATUS_CODE IN (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
AND QPLINE.PROCESS_STATUS <> 'NOT_VALID'
);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ATTRIBS' , 3 ) ;
oe_debug_pub.add( 'POPULATING BULK INSERT TABLES' , 4 ) ;
oe_debug_pub.add('inserting line index:'||l_req_line_rec.line_index);
oe_debug_pub.add( 'setting process code as UPDATED for override list price');
G_PRICING_STATUS_CODE_tbl(px_line_index) := QP_PREQ_GRP.G_STATUS_UPDATED;
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_contract_start_date => G_CONTRACT_START_DATE_tbl,
p_contract_end_date => G_CONTRACT_END_DATE_tbl,
p_catchweight_qty => G_CATCHWEIGHT_QTY_tbl,
p_actual_order_qty => G_ACTUAL_ORDER_QTY_TBL,
p_LINE_UNIT_PRICE => G_LINE_UNIT_PRICE_TBL,
p_LIST_PRICE_OVERRIDE_FLAG => G_LIST_PRICE_OVERRIDE_FLAG_TBL,
p_CHARGE_PERIODICITY_CODE => G_CHARGE_PERIODICITY_CODE_TBL, --rc
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 ) ;
Select count(header_id) into l_total_lines from oe_order_lines
where header_id = p_header_id;
Select 'x' into l_dummy from dual
where exists (select 'x' from oe_agreements_vl where
agreement_id = l_line_tbl(line_Tbl_Index).agreement_id and
( trunc(nvl(l_line_tbl(line_Tbl_Index).PRICING_DATE,sysdate))
between
trunc(nvl(start_date_active, nvl(l_line_tbl(line_Tbl_Index).PRICING_DATE,sysdate)))
and
trunc(nvl(end_date_active, nvl(l_line_tbl(line_Tbl_Index).PRICING_DATE, sysdate)))));
select name, revision into l_agreement_name, l_revision
from oe_agreements_vl where agreement_id =
l_line_tbl(line_Tbl_Index).agreement_id;
SELECT 'Y' INTO l_event_in_phase1
FROM qp_pricing_phases p,
qp_event_phases e
WHERE p.pricing_phase_id=e.pricing_phase_id
AND p.pricing_phase_id = 1
AND e.pricing_event_code= p_pricing_events
AND trunc(sysdate) BETWEEN trunc(nvl(e.end_date_active,sysdate))
AND trunc(nvl(e.end_date_active,sysdate));
Select 'x' into l_dummy from dual
Where exists
(select 'x' from
oe_order_price_attribs oopa
where
nvl(oopa.line_id,l_Line_Tbl(line_Tbl_Index).line_id)
= l_Line_Tbl(line_Tbl_Index).line_id
and oopa.header_id = l_header_id2);
Select 'x' into l_dummy
from dual where
exists(select 'x' from oe_order_lines
Where header_id = l_header_id
and calculate_price_flag in ('Y','P')
and item_type_code not in ('CONFIG', 'INCLUDED'));
Select 'p' into l_dummy
from dual where
exists (select 'x' from oe_order_lines
where header_id = l_header_id
and calculate_price_flag in ('N', 'P')
and cancelled_flag = 'N'
and item_type_code not in ('CONFIG', 'INCLUDED') );
Select 'x' into l_dummy from dual
where exists(
Select 'X' from oe_order_price_attribs oopa
where oopa.header_id = l_header_id2 and oopa.line_id is null);
l_control_rec.temp_table_insert_flag := 'N';
Select f.formula
From qp_ldets_v ldet, qp_list_lines qpll, qp_price_formulas_vl f
Where ldet.line_index = p_line_index
AND ldet.list_line_id = qpll.list_line_id
AND f.price_formula_id = nvl(qpll.price_by_formula_id,qpll.generate_using_formula_id);
select line_id
from qp_preq_lines_tmp
where pricing_status_code <> QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST;
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
, lines.line_index
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'
--bug 3968023
and lines.pricing_status_code <> QP_PREQ_PUB.G_STATUS_UNCHANGED
;
G_IPL_ERRORS_TBL.delete(MOD(I.line_id,G_BINARY_LIMIT));
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 ;
select oe_msg_request_id_s.nextval into g_request_id from dual;
OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, G_REQUEST_ID,'U');
select oe_msg_request_id_s.nextval into g_request_id from dual;
OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, G_REQUEST_ID,'U');
select oe_msg_request_id_s.nextval into g_request_id from dual;
OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, G_REQUEST_ID,'U');
select oe_msg_request_id_s.nextval into g_request_id from dual;
OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, G_REQUEST_ID,'U');
UPDATE_ORDER_HEADER(p_header_rec);
/* UPDATE_ORDER_LINES(px_line_tbl
, l_num_changed_lines); moved after oe_adv_price_pvt.process_adv_modifiers */
UPDATE_ORDER_LINES(px_line_tbl
,l_num_changed_lines
,p_control_rec.p_write_to_db);