[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Insert_Adj
This procedure transfers valid adjustments from QP temp tables to oe_price_adjustments
**************************************************************************************************/
--!!!warning, Insert_Adj look oe_order_pub.g_hdr.header_id will need to change for hvop
--OE_ADV_PRICE_PVT.Insert_Adj;
Procedure Insert_Adj(p_hvop_mode In Boolean Default False)
IS
l_booked_flag varchar2(1) := oe_order_cache.g_header_rec.booked_flag;
select pricing_status_code,LINE_DETAIL_INDEX,LINE_INDEX from QP_PREQ_LINE_ATTRS_TMP;
oe_debug_pub.add( 'INSIDE OE_BULK_PRICEORDER_PVT.INSERT_ADJ' ) ;
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) */
ldets.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
, lines.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/nvl(lines.line_quantity,1)))
, 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 ((nvl(ldets.automatic_flag,'N') = 'Y')
OR (ldets.automatic_flag = 'N' AND ldets.applied_flag = 'Y' AND ldets.updated_flag = 'Y'))
AND ldets.created_from_list_type_code not in ('PRL','AGR')
AND ldets.list_line_type_code<>'PLL'
AND ldets.list_line_type_code<>'IUE'
);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' ) ;
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) */
LDET.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
,NULL
,RLDET.PRICE_ADJUSTMENT_ID
,1
FROM
QP_PREQ_RLTD_LINES_TMP RLTD,
QP_PREQ_LDETS_TMP LDET,
QP_PREQ_LDETS_TMP RLDET
WHERE
LDET.LINE_DETAIL_INDEX = RLTD.LINE_DETAIL_INDEX AND
RLDET.LINE_DETAIL_INDEX = RLTD.RELATED_LINE_DETAIL_INDEX AND
LDET.PRICING_STATUS_CODE = 'N' AND
LDET.PROCESS_CODE IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) AND
nvl(LDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
lDET.CREATED_FROM_LIST_TYPE_CODE NOT IN ('PRL','AGR') AND
lDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
RLDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
RLDET.PRICING_STATUS_CODE = 'N' AND
RLDET.PROCESS_CODE = 'N' AND
nvl(RLDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
-- not in might not be needed
RLDET.PRICE_ADJUSTMENT_ID
NOT IN (SELECT RLTD_PRICE_ADJ_ID
FROM OE_PRICE_ADJ_ASSOCS
WHERE PRICE_ADJUSTMENT_ID = LDET.PRICE_ADJUSTMENT_ID ) AND
RLTD.PRICING_STATUS_CODE = 'N');
oe_debug_pub.add('after inserting assocs');
oe_debug_pub.add('INSERTED '||SQL%ROWCOUNT||' ASSOCIATIONS');
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 LDETS.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
WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.LINE_INDEX = LDETS.LINE_INDEX
AND LDETS.PROCESS_CODE=QP_PREQ_PUB.G_STATUS_NEW
AND LDETS.AUTOMATIC_FLAG = 'Y'
AND LDETS.CREATED_FROM_LIST_TYPE_CODE NOT IN ('PRL','AGR')
);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' PRICE ADJ ATTRIBS' , 3 ) ;
oe_debug_pub.add('ERROR in inserting adjustments and associations'||sqlerrm);
END Insert_Adj;
Procedure Update_Global_Line
This procedure updates global line table based on lastest price info from qp table
**************************************************************************************************/
Procedure Update_Global_Line As
Cursor valid_lines Is
select
lines.order_uom_selling_price UNIT_SELLING_PRICE
, lines.line_unit_price UNIT_LIST_PRICE
, lines.ADJUSTED_UNIT_PRICE UNIT_SELLING_PRICE_PER_PQTY
, lines.UNIT_PRICE UNIT_LIST_PRICE_PER_PQTY
, lines.priced_quantity PRICING_QUANTITY
, lines.priced_uom_code PRICING_QUANTITY_UOM
, lines.price_list_header_id PRICE_LIST_ID
, lines.price_request_code PRICE_REQUEST_CODE
, nvl(lines.percent_price, NULL) UNIT_LIST_PERCENT
, nvl(lines.parent_price, NULL) UNIT_PERCENT_BASE_PRICE
, decode(lines.parent_price, NULL, 0, 0, 0,
lines.adjusted_unit_price/lines.parent_price)
UNIT_SELLING_PERCENT
, lines.line_index line_index
from qp_preq_lines_tmp lines
where 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';
oe_debug_pub.add('inside update_global_line');
UPDATE OE_ORDER_LINES
SET booked_flag = 'N'
,flow_status_code = 'ENTERED'
WHERE header_id = p_header_rec.header_id(p_index);
UPDATE OE_ORDER_HEADERS
SET booked_flag = 'N'
,booked_date = NULL
,flow_status_code = 'ENTERED'
WHERE header_id = p_header_rec.header_id(p_index);
DELETE FROM ONT_DBI_CHANGE_LOG
WHERE header_id = p_header_rec.header_id(p_index);
1. OE_BULK_HEADER_UTIL.Insert_Headers will always insert booked_flag = 'N' for the header.
2. The g_header_rec memory always contains the correct booked_flag.
3. Before process acknowledgment, we call credit_check
4. Credit_Check will one by one loop through the G_HEADER_REC updates the db header book_flag as 'BOOKED' and then perform the credit check for each order
****************************************************************************************************/
PROCEDURE Credit_Check (p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE) As
l_msg_count Number;
update oe_order_headers_all set booked_flag = 'Y'
where header_id = p_header_rec.header_id(i);
, p_calling_action => 'UPDATE'
, p_delayed_request=> FND_API.G_TRUE
, p_msg_count => l_msg_count
, p_msg_data => l_msg_data
, p_return_status => l_return_status);
Procedure Insert_Adjs_From_Iface
(p_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) AS
BEGIN
null;
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 qp.line_id
, qp.line_index
, qp.line_type_code
, qp.processed_code
, qp.pricing_status_code
, qp.PRICING_STATUS_TEXT STATUS_TEXT
, qp.unit_price
, qp.adjusted_unit_price
, qp.priced_quantity
, qp.line_quantity
, qp.priced_uom_code
from qp_preq_lines_tmp qp
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, --uncommented for bug 3716296
'NOT_VALID')
OR (l_allow_negative_price = 'N' AND (unit_price<0 OR adjusted_unit_price<0)));
Select name into l_price_list
from qp_list_headers_vl where
list_header_id = l_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 line_id, PRICING_STATUS_TEXT status_text, line_index
FROM QP_PREQ_LINES_TMP
WHERE LINE_TYPE_CODE='LINE'
AND PROCESS_STATUS <> 'NOT_VALID'
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_GSA_VIOLATION;
SELECT line_id
,price_list_id
,unit_list_price
,unit_selling_price
FROM OE_ORDER_LINES l
WHERE l.header_id = l_header_id;
select CURRENCY_CODE,LINE_INDEX,LINE_ID,LINE_TYPE_CODE from qp_preq_lines_tmp;
SELECT hsecs INTO l_start_time from v$timer;
oe_debug_pub.add('before QP_BULK_PREQ_GRP.Bulk_insert_lines');
QP_BULK_PREQ_GRP.Bulk_insert_lines(p_header_rec => p_header_rec
, p_line_rec => p_line_rec
, x_return_status => x_return_status
, x_return_status_text => x_return_status_text);
oe_debug_pub.add('return status after Bulk_insert_lines : '||x_return_status||' status text : '||x_return_status_text);
select count(*) into l_count from qp_preq_lines_tmp;
SELECT hsecs INTO l_end_time from v$timer;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Bulk_insert_lines is (sec) '||((l_end_time-l_start_time)/100));
oe_debug_pub.add('before QP_BULK_PREQ_GRP.Bulk_insert_adj');
QP_BULK_PREQ_GRP.Bulk_insert_adj(x_return_status,
x_return_status_text);
oe_debug_pub.add('after Bulk_insert_adj return status : '||x_return_status||' status text : '||x_return_status_text);
l_Price_Control_rec.temp_table_insert_flag := 'N';
SELECT hsecs INTO l_start_time from v$timer;
SELECT hsecs INTO l_end_time from v$timer;
SELECT hsecs INTO l_start_time from v$timer;
SELECT hsecs INTO l_end_time from v$timer;
SELECT hsecs INTO l_start_time from v$timer;
Update_Global_Line;
SELECT hsecs INTO l_end_time from v$timer;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Update_Global_Line is (sec) '||((l_end_time-l_start_time)/100));
SELECT hsecs INTO l_start_time from v$timer;
Insert_Adj;
SELECT hsecs INTO l_end_time from v$timer;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Insert_Adj is (sec) '||((l_end_time-l_start_time)/100));