The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_rowtype_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
l_rowtype_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
l_rowtype_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
l_rowtype_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
l_rowtype_rec.updated_flag := FND_API.G_MISS_CHAR;
l_rowtype_rec.update_allowed := FND_API.G_MISS_CHAR;
x_rowtype_rec.LAST_UPDATED_BY := p_header_adj_rec.LAST_UPDATED_BY;
x_rowtype_rec.LAST_UPDATE_DATE := p_header_adj_rec.LAST_UPDATE_DATE;
x_rowtype_rec.LAST_UPDATE_LOGIN := p_header_adj_rec.LAST_UPDATE_LOGIN;
x_rowtype_rec.PROGRAM_UPDATE_DATE := p_header_adj_rec.PROGRAM_UPDATE_DATE;
x_rowtype_rec.updated_flag := p_header_adj_rec.updated_flag;
x_rowtype_rec.update_allowed := p_header_adj_rec.update_allowed;
x_api_rec.LAST_UPDATED_BY := p_record.LAST_UPDATED_BY;
x_api_rec.LAST_UPDATE_DATE := p_record.LAST_UPDATE_DATE;
x_api_rec.LAST_UPDATE_LOGIN := p_record.LAST_UPDATE_LOGIN;
x_api_rec.PROGRAM_UPDATE_DATE := p_record.PROGRAM_UPDATE_DATE;
x_api_rec.updated_flag := p_record.updated_flag;
x_api_rec.update_allowed := p_record.update_allowed;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.last_update_date,p_old_Header_Adj_rec.last_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_ADJ_UTIL.G_LAST_UPDATE_DATE;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.last_updated_by,p_old_Header_Adj_rec.last_updated_by)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_ADJ_UTIL.G_LAST_UPDATED_BY;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.last_update_login,p_old_Header_Adj_rec.last_update_login)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_ADJ_UTIL.G_LAST_UPDATE_LOGIN;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.program_update_date,p_old_Header_Adj_rec.program_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_ADJ_UTIL.G_PROGRAM_UPDATE_DATE;
ELSIF l_dep_attr_tbl(I) = OE_HEADER_ADJ_UTIL.G_LAST_UPDATE_DATE THEN
p_x_Header_Adj_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
ELSIF l_dep_attr_tbl(I) = OE_HEADER_ADJ_UTIL.G_LAST_UPDATED_BY THEN
p_x_Header_Adj_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
ELSIF l_dep_attr_tbl(I) = OE_HEADER_ADJ_UTIL.G_LAST_UPDATE_LOGIN THEN
p_x_Header_Adj_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
ELSIF l_dep_attr_tbl(I) = OE_HEADER_ADJ_UTIL.G_PROGRAM_UPDATE_DATE THEN
p_x_Header_Adj_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
select booked_flag into l_booked_flag from oe_order_headers_all
where header_id = p_header_id;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.last_update_date,p_old_Header_Adj_rec.last_update_date)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.last_updated_by,p_old_Header_Adj_rec.last_updated_by)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.last_update_login,p_old_Header_Adj_rec.last_update_login)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.program_update_date,p_old_Header_Adj_rec.program_update_date)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.update_allowed,p_old_Header_Adj_rec.update_allowed)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Header_Adj_rec.updated_flag,p_old_Header_Adj_rec.updated_flag)
THEN
l_price_flag := TRUE;
p_x_Header_Adj_rec.updated_flag = 'Y'
THEN
p_x_Header_adj_rec.estimated_flag := 'N';
p_x_Header_Adj_rec.updated_flag = 'N'
THEN
p_x_Header_adj_rec.estimated_flag := 'Y';
SELECT lookup_code, meaning
INTO p_x_Header_Adj_rec.change_reason_code,
p_x_Header_Adj_rec.change_reason_text
FROM oe_lookups
WHERE lookup_type = 'CHANGE_CODE'
AND lookup_code = 'MANUAL';
IF l_Header_Adj_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_Header_Adj_rec.last_update_date := p_old_Header_Adj_rec.last_update_date;
IF l_Header_Adj_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_Header_Adj_rec.last_updated_by := p_old_Header_Adj_rec.last_updated_by;
IF l_Header_Adj_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_Header_Adj_rec.last_update_login := p_old_Header_Adj_rec.last_update_login;
IF l_Header_Adj_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_Header_Adj_rec.program_update_date := p_old_Header_Adj_rec.program_update_date;
IF l_Header_Adj_rec.updated_flag = FND_API.G_MISS_CHAR THEN
l_Header_Adj_rec.updated_flag := p_old_Header_Adj_rec.updated_flag;
IF l_Header_Adj_rec.update_allowed = FND_API.G_MISS_CHAR THEN
l_Header_Adj_rec.update_allowed := p_old_Header_Adj_rec.update_allowed;
IF l_Header_Adj_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_Header_Adj_rec.last_update_date := NULL;
IF l_Header_Adj_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_Header_Adj_rec.last_updated_by := NULL;
IF l_Header_Adj_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_Header_Adj_rec.last_update_login := NULL;
IF l_Header_Adj_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_Header_Adj_rec.program_update_date := NULL;
IF l_Header_Adj_rec.updated_flag = FND_API.G_MISS_CHAR THEN
l_Header_Adj_rec.updated_flag := NULL;
IF l_Header_Adj_rec.update_allowed = FND_API.G_MISS_CHAR THEN
l_Header_Adj_rec.update_allowed := NULL;
PROCEDURE Update_Row
( p_Header_Adj_rec IN OUT NOCOPY OE_Order_PUB.Header_Adj_Rec_Type
)
IS
l_lock_control NUMBER;
oe_debug_pub.add( 'ENTERING OE_HEADER_ADJ_UTIL.UPDATE_ROW' , 1 ) ;
SELECT lock_control
INTO l_lock_control
FROM oe_price_adjustments
WHERE price_adjustment_id = p_Header_Adj_rec.price_adjustment_id;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Hdr_adj_rec =>p_header_adj_rec,
p_hdr_adj_id => p_header_adj_rec.price_adjustment_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_ADJ_UTIL.UPDATE_ROE IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.UPDATE_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_ADJ_UTIL.UPDATE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.UPDATE_ROW' , 1 ) ;
UPDATE OE_PRICE_ADJUSTMENTS
SET PRICE_ADJUSTMENT_ID = p_Header_Adj_rec.price_adjustment_id
, CREATION_DATE = p_Header_Adj_rec.creation_date
, CREATED_BY = p_Header_Adj_rec.created_by
, LAST_UPDATE_DATE = p_Header_Adj_rec.last_update_date
, LAST_UPDATED_BY = p_Header_Adj_rec.last_updated_by
, LAST_UPDATE_LOGIN = p_Header_Adj_rec.last_update_login
, PROGRAM_APPLICATION_ID = p_Header_Adj_rec.program_application_id
, PROGRAM_ID = p_Header_Adj_rec.program_id
, PROGRAM_UPDATE_DATE = p_Header_Adj_rec.program_update_date
, REQUEST_ID = p_Header_Adj_rec.request_id
, HEADER_ID = p_Header_Adj_rec.header_id
, DISCOUNT_ID = p_Header_Adj_rec.discount_id
, DISCOUNT_LINE_ID = p_Header_Adj_rec.discount_line_id
, AUTOMATIC_FLAG = p_Header_Adj_rec.automatic_flag
, PERCENT = p_Header_Adj_rec.percent
, LINE_ID = p_Header_Adj_rec.line_id
, CONTEXT = p_Header_Adj_rec.context
, ATTRIBUTE1 = p_Header_Adj_rec.attribute1
, ATTRIBUTE2 = p_Header_Adj_rec.attribute2
, ATTRIBUTE3 = p_Header_Adj_rec.attribute3
, ATTRIBUTE4 = p_Header_Adj_rec.attribute4
, ATTRIBUTE5 = p_Header_Adj_rec.attribute5
, ATTRIBUTE6 = p_Header_Adj_rec.attribute6
, ATTRIBUTE7 = p_Header_Adj_rec.attribute7
, ATTRIBUTE8 = p_Header_Adj_rec.attribute8
, ATTRIBUTE9 = p_Header_Adj_rec.attribute9
, ATTRIBUTE10 = p_Header_Adj_rec.attribute10
, ATTRIBUTE11 = p_Header_Adj_rec.attribute11
, ATTRIBUTE12 = p_Header_Adj_rec.attribute12
, ATTRIBUTE13 = p_Header_Adj_rec.attribute13
, ATTRIBUTE14 = p_Header_Adj_rec.attribute14
, ATTRIBUTE15 = p_Header_Adj_rec.attribute15
, ORIG_SYS_DISCOUNT_REF = p_Header_Adj_rec.orig_sys_discount_ref
, LIST_HEADER_ID = p_Header_Adj_rec.list_header_id
, LIST_LINE_ID = p_Header_Adj_rec.list_line_id
, LIST_LINE_TYPE_CODE = p_Header_Adj_rec.list_line_type_code
, MODIFIER_MECHANISM_TYPE_CODE = p_Header_Adj_rec.list_header_id
, MODIFIED_FROM = p_Header_Adj_rec.modified_from
, MODIFIED_TO = p_Header_Adj_rec.modified_to
, UPDATED_FLAG = p_Header_Adj_rec.updated_flag
, UPDATE_ALLOWED = p_Header_Adj_rec.update_allowed
, APPLIED_FLAG = p_Header_Adj_rec.applied_flag
, CHANGE_REASON_CODE = p_Header_Adj_rec.change_reason_code
, CHANGE_REASON_TEXT = p_Header_Adj_rec.change_reason_text
, operand = p_Header_Adj_rec.operand
, arithmetic_operator = p_Header_Adj_rec.arithmetic_operator
, COST_ID = p_Header_Adj_rec.cost_id
, TAX_CODE = p_Header_Adj_rec.tax_code
, TAX_EXEMPT_FLAG = p_Header_Adj_rec.tax_exempt_flag
, TAX_EXEMPT_NUMBER = p_Header_Adj_rec.tax_exempt_number
, TAX_EXEMPT_REASON_CODE = p_Header_Adj_rec.tax_exempt_reason_code
, PARENT_ADJUSTMENT_ID = p_Header_Adj_rec.parent_adjustment_id
, INVOICED_FLAG = p_Header_Adj_rec.invoiced_flag
, ESTIMATED_FLAG = p_Header_Adj_rec.estimated_flag
, INC_IN_SALES_PERFORMANCE = p_Header_Adj_rec.inc_in_sales_performance
, SPLIT_ACTION_CODE = p_Header_Adj_rec.split_action_code
, ADJUSTED_AMOUNT = p_Header_Adj_rec.adjusted_amount
, PRICING_PHASE_ID = p_Header_Adj_rec.pricing_phase_id
, CHARGE_TYPE_CODE = p_Header_Adj_rec.charge_type_code
, CHARGE_SUBTYPE_CODE = p_Header_Adj_rec.charge_subtype_code
, LIST_LINE_NO = p_Header_Adj_rec.list_line_no
, SOURCE_SYSTEM_CODE = p_Header_Adj_rec.source_system_code
, BENEFIT_QTY = p_Header_Adj_rec.benefit_qty
, BENEFIT_UOM_CODE = p_Header_Adj_rec.benefit_uom_code
, PRINT_ON_INVOICE_FLAG = p_Header_Adj_rec.print_on_invoice_flag
, EXPIRATION_DATE = p_Header_Adj_rec.expiration_date
, REBATE_TRANSACTION_TYPE_CODE = p_Header_Adj_rec.rebate_transaction_type_code
, REBATE_TRANSACTION_REFERENCE = p_Header_Adj_rec.rebate_transaction_reference
, REBATE_PAYMENT_SYSTEM_CODE = p_Header_Adj_rec.rebate_payment_system_code
, REDEEMED_DATE = p_Header_Adj_rec.redeemed_date
, REDEEMED_FLAG = p_Header_Adj_rec.redeemed_flag
, ACCRUAL_FLAG = p_Header_Adj_rec.accrual_flag
, RANGE_BREAK_QUANTITY = p_Header_Adj_rec.range_break_quantity
, ACCRUAL_CONVERSION_RATE = p_Header_Adj_rec.accrual_conversion_rate
, PRICING_GROUP_SEQUENCE = p_Header_Adj_rec.pricing_group_sequence
, MODIFIER_LEVEL_CODE = p_Header_Adj_rec.modifier_level_code
, PRICE_BREAK_TYPE_CODE = p_Header_Adj_rec.price_break_type_code
, SUBSTITUTION_ATTRIBUTE = p_Header_Adj_rec.substitution_attribute
, PRORATION_TYPE_CODE = p_Header_Adj_rec.proration_type_code
, CREDIT_OR_CHARGE_FLAG = p_Header_Adj_rec.credit_or_charge_flag
, INCLUDE_ON_RETURNS_FLAG = p_Header_Adj_rec.include_on_returns_flag
, AC_CONTEXT = p_Header_Adj_rec.ac_context
, AC_ATTRIBUTE1 = p_Header_Adj_rec.ac_attribute1
, AC_ATTRIBUTE2 = p_Header_Adj_rec.ac_attribute2
, AC_ATTRIBUTE3 = p_Header_Adj_rec.ac_attribute3
, AC_ATTRIBUTE4 = p_Header_Adj_rec.ac_attribute4
, AC_ATTRIBUTE5 = p_Header_Adj_rec.ac_attribute5
, AC_ATTRIBUTE6 = p_Header_Adj_rec.ac_attribute6
, AC_ATTRIBUTE7 = p_Header_Adj_rec.ac_attribute7
, AC_ATTRIBUTE8 = p_Header_Adj_rec.ac_attribute8
, AC_ATTRIBUTE9 = p_Header_Adj_rec.ac_attribute9
, AC_ATTRIBUTE10 = p_Header_Adj_rec.ac_attribute10
, AC_ATTRIBUTE11 = p_Header_Adj_rec.ac_attribute11
, AC_ATTRIBUTE12 = p_Header_Adj_rec.ac_attribute12
, AC_ATTRIBUTE13 = p_Header_Adj_rec.ac_attribute13
, AC_ATTRIBUTE14 = p_Header_Adj_rec.ac_attribute14
, AC_ATTRIBUTE15 = p_Header_Adj_rec.ac_attribute15
--uom begin
, OPERAND_PER_PQTY = p_Header_Adj_rec.operand_per_pqty
, ADJUSTED_AMOUNT_PER_PQTY = p_Header_Adj_rec.adjusted_amount_per_pqty
--uom end
, INVOICED_AMOUNT = p_Header_Adj_rec.invoiced_amount
, LOCK_CONTROL = l_lock_control
WHERE PRICE_ADJUSTMENT_ID = p_Header_Adj_rec.price_adjustment_id
;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.UPDATE_ROW' , 1 ) ;
, 'Update_Row'
);
END Update_Row;
PROCEDURE Insert_Row
( p_Header_Adj_rec IN OUT NOCOPY OE_Order_PUB.Header_Adj_Rec_Type
)
IS
l_lock_control NUMBER := 1;
oe_debug_pub.add( 'ENTERING OE_HEADER_ADJ_UTIL.INSERT_ROW' , 1 ) ;
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_RATE_ID --TaxER
, 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
--uom begin
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
--uom end
, INVOICED_AMOUNT
, lock_control
)
VALUES
( p_Header_Adj_rec.price_adjustment_id
, p_Header_Adj_rec.creation_date
, p_Header_Adj_rec.created_by
, p_Header_Adj_rec.last_update_date
, p_Header_Adj_rec.last_updated_by
, p_Header_Adj_rec.last_update_login
, p_Header_Adj_rec.program_application_id
, p_Header_Adj_rec.program_id
, p_Header_Adj_rec.program_update_date
, p_Header_Adj_rec.request_id
, p_Header_Adj_rec.header_id
, p_Header_Adj_rec.discount_id
, p_Header_Adj_rec.discount_line_id
, p_Header_Adj_rec.automatic_flag
, p_Header_Adj_rec.percent
, p_Header_Adj_rec.line_id
, p_Header_Adj_rec.context
, p_Header_Adj_rec.attribute1
, p_Header_Adj_rec.attribute2
, p_Header_Adj_rec.attribute3
, p_Header_Adj_rec.attribute4
, p_Header_Adj_rec.attribute5
, p_Header_Adj_rec.attribute6
, p_Header_Adj_rec.attribute7
, p_Header_Adj_rec.attribute8
, p_Header_Adj_rec.attribute9
, p_Header_Adj_rec.attribute10
, p_Header_Adj_rec.attribute11
, p_Header_Adj_rec.attribute12
, p_Header_Adj_rec.attribute13
, p_Header_Adj_rec.attribute14
, p_Header_Adj_rec.attribute15
, p_Header_Adj_rec.orig_sys_discount_ref
, p_Header_Adj_rec.list_header_id
, p_Header_Adj_rec.list_line_id
, p_Header_Adj_rec.list_line_type_code
, p_Header_Adj_rec.modifier_mechanism_type_code
, p_Header_Adj_rec.modified_from
, p_Header_Adj_rec.modified_to
, p_Header_Adj_rec.updated_flag
, p_Header_Adj_rec.update_allowed
, p_Header_Adj_rec.applied_flag
, p_Header_Adj_rec.change_reason_code
, p_Header_Adj_rec.change_reason_text
, p_Header_Adj_rec.operand
, p_Header_Adj_rec.arithmetic_operator
, p_Header_Adj_rec.COST_ID
, p_Header_Adj_rec.TAX_CODE
, p_Header_Adj_rec.TAX_RATE_ID --TaxER
, p_Header_Adj_rec.TAX_EXEMPT_FLAG
, p_Header_Adj_rec.TAX_EXEMPT_NUMBER
, p_Header_Adj_rec.TAX_EXEMPT_REASON_CODE
, p_Header_Adj_rec.PARENT_ADJUSTMENT_ID
, p_Header_Adj_rec.INVOICED_FLAG
, p_Header_Adj_rec.ESTIMATED_FLAG
, p_Header_Adj_rec.INC_IN_SALES_PERFORMANCE
, p_Header_Adj_rec.SPLIT_ACTION_CODE
, p_Header_Adj_rec.adjusted_amount
, p_Header_Adj_rec.pricing_phase_id
, p_Header_Adj_rec.charge_type_code
, p_Header_Adj_rec.charge_subtype_code
, p_Header_Adj_rec.list_line_no
, p_Header_Adj_rec.source_system_code
, p_Header_Adj_rec.benefit_qty
, p_Header_Adj_rec.benefit_uom_code
, p_Header_Adj_rec.print_on_invoice_flag
, p_Header_Adj_rec.expiration_date
, p_Header_Adj_rec.rebate_transaction_type_code
, p_Header_Adj_rec.rebate_transaction_reference
, p_Header_Adj_rec.rebate_payment_system_code
, p_Header_Adj_rec.redeemed_date
, p_Header_Adj_rec.redeemed_flag
, p_Header_Adj_rec.accrual_flag
, p_Header_Adj_rec.range_break_quantity
, p_Header_Adj_rec.accrual_conversion_rate
, p_Header_Adj_rec.pricing_group_sequence
, p_Header_Adj_rec.modifier_level_code
, p_Header_Adj_rec.price_break_type_code
, p_Header_Adj_rec.substitution_attribute
, p_Header_Adj_rec.proration_type_code
, p_Header_Adj_rec.credit_or_charge_flag
, p_Header_Adj_rec.include_on_returns_flag
, p_Header_Adj_rec.ac_context
, p_Header_Adj_rec.ac_attribute1
, p_Header_Adj_rec.ac_attribute2
, p_Header_Adj_rec.ac_attribute3
, p_Header_Adj_rec.ac_attribute4
, p_Header_Adj_rec.ac_attribute5
, p_Header_Adj_rec.ac_attribute6
, p_Header_Adj_rec.ac_attribute7
, p_Header_Adj_rec.ac_attribute8
, p_Header_Adj_rec.ac_attribute9
, p_Header_Adj_rec.ac_attribute10
, p_Header_Adj_rec.ac_attribute11
, p_Header_Adj_rec.ac_attribute12
, p_Header_Adj_rec.ac_attribute13
, p_Header_Adj_rec.ac_attribute14
, p_Header_Adj_rec.ac_attribute15
--uom begin
, p_Header_Adj_rec.OPERAND_PER_PQTY
, p_Header_Adj_rec.ADJUSTED_AMOUNT_PER_PQTY
--uom end
, p_Header_Adj_rec.INVOICED_AMOUNT
, l_lock_control
);
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_old_hdr_adj_rec => NULL,
p_Hdr_adj_rec =>p_header_adj_rec,
p_hdr_adj_id => p_header_adj_rec.price_adjustment_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_ADJ_UTIL.INSERT_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.INSERT_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_ADJ_UTIL.INSERT_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.INSERT_ROW' , 1 ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.INSERT_ROW' , 1 ) ;
, 'Insert_Row'
);
END Insert_Row;
Procedure Delete_Header_Charges
(
p_header_id IN Number
)
IS
l_return_status VARCHAR2(30);
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE HEADER_ID = p_header_id
AND LINE_ID IS NULL
AND LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE';
, 'Delete_Row'
);
END Delete_Header_Charges;
PROCEDURE Delete_Row
( p_price_adjustment_id IN NUMBER :=
FND_API.G_MISS_NUM
, p_header_id IN NUMBER :=
FND_API.G_MISS_NUM
)
IS
l_return_status VARCHAR2(30);
SELECT price_adjustment_id
FROM OE_PRICE_ADJUSTMENTS
WHERE HEADER_ID = p_header_id;
oe_debug_pub.add( 'ENTERING OE_HEADER_ADJ_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'JFC: IN DELETE ROW , PRICE_ADJUSTMENT_ID= '|| L_ADJ.PRICE_ADJUSTMENT_ID ) ;
/* Set the operation on the record so that globals are updated as well */
l_new_header_adj_rec.operation := OE_GLOBALS.G_OPR_DELETE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Hdr_adj_rec =>l_new_header_adj_rec,
p_hdr_adj_id => l_adj.price_adjustment_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_ADJ_UTIL.DELETE_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_ADJ_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.DELETE_ROW' , 1 ) ;
OE_Delayed_Requests_Pvt.Delete_Reqs_for_deleted_entity(
p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_ADJ,
p_entity_id => l_adj.price_adjustment_id,
x_return_status => l_return_status
);
OE_Line_Price_Aattr_Util.delete_row(
p_price_adjustment_id=>l_adj.price_adjustment_id);
OE_Line_Adj_Assocs_Util.delete_row(
p_price_adjustment_id=>l_adj.price_adjustment_id);
DELETE FROM OE_PRICE_ADJS_HISTORY
WHERE HEADER_ID = p_header_id;
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE HEADER_ID = p_header_id;
oe_debug_pub.add( 'JFC:IN DELETE ROW , HEADER_ID IS G_MISS_NUM , PRICE_ADJUSTMENT_ID= '|| P_PRICE_ADJUSTMENT_ID , 1 ) ;
/* Set the operation on the record so that globals are updated as well */
l_new_header_adj_rec.operation := OE_GLOBALS.G_OPR_DELETE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Hdr_adj_rec =>l_new_header_adj_rec,
p_hdr_adj_id => p_price_adjustment_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_ADJ_UTIL.DELETE_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_ADJ_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.DELETE_ROW' , 1 ) ;
OE_Delayed_Requests_Pvt.Delete_Reqs_for_deleted_entity
(p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_ADJ,
p_entity_id => p_price_adjustment_id,
x_return_status => l_return_status
);
OE_Line_Price_Aattr_Util.delete_row(
p_price_adjustment_id=>p_price_adjustment_id);
OE_Line_Adj_Assocs_Util.delete_row(
p_price_adjustment_id=>p_price_adjustment_id);
DELETE FROM OE_PRICE_ADJS_HISTORY
WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id
AND NVL(AUDIT_FLAG, 'Y') = 'Y'
AND NVL(VERSION_FLAG, 'N') = 'N'
AND NVL(PHASE_CHANGE_FLAG, 'N') = 'N';
UPDATE OE_PRICE_ADJS_HISTORY
SET AUDIT_FLAG = 'N'
WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id
AND NVL(AUDIT_FLAG, 'Y') = 'Y'
AND (NVL(VERSION_FLAG, 'N') = 'Y'
OR NVL(PHASE_CHANGE_FLAG, 'N') = 'Y');
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id
;
oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.DELETE_ROW' , 1 ) ;
, 'Delete_Row'
);
END Delete_Row;
SELECT ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, AUTOMATIC_FLAG
, CONTEXT
, CREATED_BY
, CREATION_DATE
, DISCOUNT_ID
, DISCOUNT_LINE_ID
, HEADER_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, PERCENT
, PRICE_ADJUSTMENT_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, 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_ATTRIBUTE1
, AC_ATTRIBUTE10
, AC_ATTRIBUTE11
, AC_ATTRIBUTE12
, AC_ATTRIBUTE13
, AC_ATTRIBUTE14
, AC_ATTRIBUTE15
, AC_ATTRIBUTE2
, AC_ATTRIBUTE3
, AC_ATTRIBUTE4
, AC_ATTRIBUTE5
, AC_ATTRIBUTE6
, AC_ATTRIBUTE7
, AC_ATTRIBUTE8
, AC_ATTRIBUTE9
, AC_CONTEXT
, orig_sys_discount_ref
--uom begin
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, INVOICED_AMOUNT
--uom end
, LOCK_CONTROL
FROM OE_PRICE_ADJUSTMENTS
WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id;
SELECT ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, AUTOMATIC_FLAG
, CONTEXT
, CREATED_BY
, CREATION_DATE
, DISCOUNT_ID
, DISCOUNT_LINE_ID
, HEADER_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, PERCENT
, PRICE_ADJUSTMENT_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, 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_ATTRIBUTE1
, AC_ATTRIBUTE10
, AC_ATTRIBUTE11
, AC_ATTRIBUTE12
, AC_ATTRIBUTE13
, AC_ATTRIBUTE14
, AC_ATTRIBUTE15
, AC_ATTRIBUTE2
, AC_ATTRIBUTE3
, AC_ATTRIBUTE4
, AC_ATTRIBUTE5
, AC_ATTRIBUTE6
, AC_ATTRIBUTE7
, AC_ATTRIBUTE8
, AC_ATTRIBUTE9
, AC_CONTEXT
, orig_sys_discount_ref
--uom begin
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, INVOICED_AMOUNT
--uom end
, LOCK_CONTROL
FROM OE_PRICE_ADJUSTMENTS
WHERE HEADER_ID = p_header_id AND LINE_ID IS NULL;
x_Header_Adj_tbl(l_count).last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
x_Header_Adj_tbl(l_count).last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
x_Header_Adj_tbl(l_count).last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
x_Header_Adj_tbl(l_count).program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
x_Header_Adj_tbl(l_count).updated_flag := l_implicit_rec.updated_flag;
x_Header_Adj_tbl(l_count).update_allowed := l_implicit_rec.update_allowed;
SELECT price_adjustment_id
INTO l_price_adjustment_id
FROM oe_price_adjustments
WHERE price_adjustment_id = l_price_adjustment_id
FOR UPDATE NOWAIT;
oe_debug_pub.add( 'SELECTING FOR UPDATE.' , 1 ) ;
FND_MESSAGE.SET_NAME('OE','OE_LOCK_ROW_DELETED');
SELECT price_adjustment_id
FROM oe_price_adjustments
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
SELECT price_adjustment_id
INTO l_price_adjustment_id
FROM oe_price_adjustments
WHERE price_adjustment_id = p_price_adjustment_id
FOR UPDATE NOWAIT;
fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');
, p_delete_flag IN BOOLEAN DEFAULT FALSE
) IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
p_delete_flag)
THEN
/*
oe_delayed_requests_pvt.log_request(p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_ADJ,
p_entity_id => p_adj_rec.header_id,
p_requesting_entity_code => OE_GLOBALS.G_ENTITY_HEADER_ADJ,
p_requesting_entity_id => p_adj_rec.
price_adjustment_id,
p_request_type => OE_GLOBALS.G_PRICE_ADJ,
x_return_status => x_return_status);
SELECT sum(ROUND(((unit_selling_price - unit_list_price)*
ordered_quantity) ,OE_ORDER_UTIL.G_Precision))
INTO l_adj_total
FROM oe_order_lines_all
WHERE HEADER_ID = p_header_id
AND charge_periodicity_code IS NULL -- addded for recurring charges
AND nvl(cancelled_flag,'N') ='N';
SELECT sum(ROUND(((unit_selling_price - unit_list_price)*
ordered_quantity) ,OE_ORDER_UTIL.G_Precision))
INTO l_adj_total
FROM oe_order_lines_all
WHERE line_id = p_line_id
AND nvl(cancelled_flag,'N') ='N';
( SELECT opa.price_adjustment_id
,opa.adjustment_name
,opa.adjustment_description --Enhancement 3816014
,opa.list_line_no
,opa.adjustment_type_code
,opa.operand
,opa.arithmetic_operator
--Bug 3340636 Starts
,decode(list_line_type_code,'SUR',
-1 * (decode(opa.arithmetic_operator,
null,0,
'%', opa.operand*ool.unit_list_price/100,
'AMT',opa.operand,
'NEWPRICE',ool.unit_list_price - opa.operand)),
decode(opa.arithmetic_operator,null,0,'%',
opa.operand*ool.unit_list_price/100,
'AMT',opa.operand,
'NEWPRICE',ool.unit_list_price-opa.operand))
unit_discount_amount
--Bug 3340636 Ends
FROM oe_price_adjustments_v opa
, oe_order_lines_all ool
WHERE opa.HEADER_ID = p_header_id
and opa.line_id is null
and ool.line_id = p_line_id
and ool.header_id = p_header_id
and nvl(opa.applied_flag,'N') = 'Y'
and nvl(opa.accrual_flag,'N') = 'N'
and list_line_type_code in ('DIS','SUR','PBH')
UNION
-- get line adjustments
SELECT opa.price_adjustment_id
,opa.adjustment_name
,opa.adjustment_description --Enhancement 3816014
,opa.list_line_no
,opa.adjustment_type_code
,opa.operand
,opa.arithmetic_operator
,Nvl(opa.adjusted_amount,0)*(-1) unit_discount_amount --added nvl for bug 13605684
FROM oe_price_adjustments_v opa
, oe_order_lines_all ool
WHERE opa.line_id =p_line_id
and ool.line_id = p_line_id
and ool.header_id = p_header_id
and nvl(opa.applied_flag,'N') = 'Y'
and nvl(opa.accrual_flag,'N') = 'N'
and list_line_type_code in ('DIS','SUR','PBH')
)
loop
l_index:= l_index+1;
if (p_x_header_adj_rec.operation = OE_GLOBALS.G_OPR_UPDATE) then
IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' OR
OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG = 'Y' THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CALL TO RECORD HEADER PRICE ADJ HISTORY' , 5 ) ;
p_hist_type_code => 'UPDATE');
p_hist_type_code => 'UPDATE',
p_reason_code => p_x_header_adj_rec.change_reason_code,
p_comments => p_x_header_adj_rec.change_reason_text,
p_wf_activity_code => null,
p_wf_result_code => null,
x_return_status => l_return_status );
oe_debug_pub.add( 'INSERT INTO HEADER PRICE ADJUSTMENTS AUDIT HISTORY CAUSED ERROR' , 1 ) ;
select order_category_code
from oe_order_headers
where header_id = p_from_header_id;
select order_category_code
from oe_order_headers
where header_id = p_from_header_id;
l_from_header_adj_tbl(i).updated_flag := 'Y';
l_from_header_adj_tbl(i).updated_flag := 'N';
SELECT sum(ROUND(((unit_selling_price - unit_list_price)*
ordered_quantity) ,OE_ORDER_UTIL.G_Precision))
INTO l_adj_total
FROM oe_order_lines_all
WHERE HEADER_ID = p_header_id
AND nvl(charge_periodicity_code,'ONE') = p_charge_periodicity_code
AND nvl(cancelled_flag,'N') ='N';
SELECT header_id INTO l_header_id
FROM oe_price_adjustments
WHERE price_adjustment_id = p_price_adjustment_id;
SELECT nvl(sum(unit_list_price * ordered_quantity), 0) --bug5354658
INTO l_rec_list_price_total
FROM oe_order_lines_all
WHERE HEADER_ID = l_header_id
AND charge_periodicity_code = p_charge_periodicity_code
AND nvl(cancelled_flag,'N') ='N';
SELECT nvl(operand,0) * decode(list_line_type_code, 'DIS', -1, 'SUR', 1)
INTO l_operand
FROM oe_price_adjustments
WHERE price_adjustment_id = p_price_adjustment_id
AND line_id IS NULL
AND arithmetic_operator = '%';
SELECT price_list_id,
transactional_curr_code,
nvl(pricing_date,ordered_date)
INTO l_price_list_id,l_currency_code,l_pricing_date
FROM Oe_Order_Headers_All
WHERE header_id = p_header_id;