The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF NOT QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.last_updated_by,p_old_PRICE_LIST_LINE_rec.last_updated_by)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_PRICE_LIST_LINE_UTIL.G_LAST_UPDATED_BY;
IF NOT QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.last_update_date,p_old_PRICE_LIST_LINE_rec.last_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_PRICE_LIST_LINE_UTIL.G_LAST_UPDATE_DATE;
IF NOT QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.last_update_login,p_old_PRICE_LIST_LINE_rec.last_update_login)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_PRICE_LIST_LINE_UTIL.G_LAST_UPDATE_LOGIN;
IF NOT QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.program_update_date,p_old_PRICE_LIST_LINE_rec.program_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_PRICE_LIST_LINE_UTIL.G_PROGRAM_UPDATE_DATE;
ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_PRICE_LIST_LINE_UTIL.G_LAST_UPDATED_BY;
ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_PRICE_LIST_LINE_UTIL.G_LAST_UPDATE_DATE;
ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_PRICE_LIST_LINE_UTIL.G_LAST_UPDATE_LOGIN;
ELSIF p_attr_id = G_PROGRAM_UPDATE_DATE THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_PRICE_LIST_LINE_UTIL.G_PROGRAM_UPDATE_DATE;
IF NOT QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.last_updated_by,p_old_PRICE_LIST_LINE_rec.last_updated_by)
THEN
NULL;
IF NOT QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.last_update_date,p_old_PRICE_LIST_LINE_rec.last_update_date)
THEN
NULL;
IF NOT QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.last_update_login,p_old_PRICE_LIST_LINE_rec.last_update_login)
THEN
NULL;
procedure insert_row */
null;
p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND,
x_return_status => l_return_status);
p_request_type =>QP_GLOBALS.G_UPDATE_CHILD_BREAKS,
x_return_status => l_return_status);
IF NOT QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.program_update_date,p_old_PRICE_LIST_LINE_rec.program_update_date)
THEN
NULL;
IF l_PRICE_LIST_LINE_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_PRICE_LIST_LINE_rec.last_updated_by := p_old_PRICE_LIST_LINE_rec.last_updated_by;
IF l_PRICE_LIST_LINE_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_PRICE_LIST_LINE_rec.last_update_date := p_old_PRICE_LIST_LINE_rec.last_update_date;
IF l_PRICE_LIST_LINE_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_PRICE_LIST_LINE_rec.last_update_login := p_old_PRICE_LIST_LINE_rec.last_update_login;
IF l_PRICE_LIST_LINE_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_PRICE_LIST_LINE_rec.program_update_date := p_old_PRICE_LIST_LINE_rec.program_update_date;
IF l_PRICE_LIST_LINE_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_PRICE_LIST_LINE_rec.last_updated_by := NULL;
IF l_PRICE_LIST_LINE_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_PRICE_LIST_LINE_rec.last_update_date := NULL;
IF l_PRICE_LIST_LINE_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_PRICE_LIST_LINE_rec.last_update_login := NULL;
IF l_PRICE_LIST_LINE_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_PRICE_LIST_LINE_rec.program_update_date := NULL;
select rounding_factor
into l_rounding_factor
from qp_list_headers_b
where list_header_id =p_price_list_header_id;
PROCEDURE Update_Row
( p_PRICE_LIST_LINE_rec IN QP_Price_List_PUB.Price_List_Line_Rec_Type
)
IS
l_operand NUMBER;
SELECT ACTIVE_FLAG
INTO l_active_flag
FROM QP_LIST_HEADERS_B
WHERE list_header_id=p_PRICE_LIST_LINE_rec.list_header_id;
UPDATE QP_LIST_LINES
SET ACCRUAL_QTY = p_PRICE_LIST_LINE_rec.accrual_qty
, ACCRUAL_UOM_CODE = p_PRICE_LIST_LINE_rec.accrual_uom_code
, ARITHMETIC_OPERATOR = p_PRICE_LIST_LINE_rec.arithmetic_operator
, ATTRIBUTE1 = p_PRICE_LIST_LINE_rec.attribute1
, ATTRIBUTE10 = p_PRICE_LIST_LINE_rec.attribute10
, ATTRIBUTE11 = p_PRICE_LIST_LINE_rec.attribute11
, ATTRIBUTE12 = p_PRICE_LIST_LINE_rec.attribute12
, ATTRIBUTE13 = p_PRICE_LIST_LINE_rec.attribute13
, ATTRIBUTE14 = p_PRICE_LIST_LINE_rec.attribute14
, ATTRIBUTE15 = p_PRICE_LIST_LINE_rec.attribute15
, ATTRIBUTE2 = p_PRICE_LIST_LINE_rec.attribute2
, ATTRIBUTE3 = p_PRICE_LIST_LINE_rec.attribute3
, ATTRIBUTE4 = p_PRICE_LIST_LINE_rec.attribute4
, ATTRIBUTE5 = p_PRICE_LIST_LINE_rec.attribute5
, ATTRIBUTE6 = p_PRICE_LIST_LINE_rec.attribute6
, ATTRIBUTE7 = p_PRICE_LIST_LINE_rec.attribute7
, ATTRIBUTE8 = p_PRICE_LIST_LINE_rec.attribute8
, ATTRIBUTE9 = p_PRICE_LIST_LINE_rec.attribute9
, AUTOMATIC_FLAG = p_PRICE_LIST_LINE_rec.automatic_flag
, BASE_QTY = p_PRICE_LIST_LINE_rec.base_qty
, BASE_UOM_CODE = p_PRICE_LIST_LINE_rec.base_uom_code
, COMMENTS = p_PRICE_LIST_LINE_rec.comments
, CONTEXT = p_PRICE_LIST_LINE_rec.context
, CREATED_BY = p_PRICE_LIST_LINE_rec.created_by
, CREATION_DATE = p_PRICE_LIST_LINE_rec.creation_date
, EFFECTIVE_PERIOD_UOM = p_PRICE_LIST_LINE_rec.effective_period_uom
, END_DATE_ACTIVE = trunc(p_PRICE_LIST_LINE_rec.end_date_active)
, ESTIM_ACCRUAL_RATE = p_PRICE_LIST_LINE_rec.estim_accrual_rate
, GENERATE_USING_FORMULA_ID = p_PRICE_LIST_LINE_rec.generate_using_formula_id
, INVENTORY_ITEM_ID = p_PRICE_LIST_LINE_rec.inventory_item_id
, LAST_UPDATED_BY = p_PRICE_LIST_LINE_rec.last_updated_by
, LAST_UPDATE_DATE = p_PRICE_LIST_LINE_rec.last_update_date
, LAST_UPDATE_LOGIN = p_PRICE_LIST_LINE_rec.last_update_login
, LIST_HEADER_ID = p_PRICE_LIST_LINE_rec.list_header_id
, LIST_LINE_ID = p_PRICE_LIST_LINE_rec.list_line_id
, LIST_LINE_NO = p_PRICE_LIST_LINE_rec.list_line_no
, LIST_LINE_TYPE_CODE = p_PRICE_LIST_LINE_rec.list_line_type_code
, LIST_PRICE = p_PRICE_LIST_LINE_rec.list_price
, PRODUCT_PRECEDENCE = p_PRICE_LIST_LINE_rec.product_precedence
, MODIFIER_LEVEL_CODE = p_PRICE_LIST_LINE_rec.modifier_level_code
, NUMBER_EFFECTIVE_PERIODS = p_PRICE_LIST_LINE_rec.number_effective_periods
--, OPERAND = p_PRICE_LIST_LINE_rec.operand
, OPERAND = l_operand --Modified for Enhancement 1732601
, ORGANIZATION_ID = p_PRICE_LIST_LINE_rec.organization_id
, OVERRIDE_FLAG = p_PRICE_LIST_LINE_rec.override_flag
, PERCENT_PRICE = p_PRICE_LIST_LINE_rec.percent_price
, PRICE_BREAK_TYPE_CODE = p_PRICE_LIST_LINE_rec.price_break_type_code
, PRICE_BY_FORMULA_ID = p_PRICE_LIST_LINE_rec.price_by_formula_id
, PRIMARY_UOM_FLAG = p_PRICE_LIST_LINE_rec.primary_uom_flag
, PRINT_ON_INVOICE_FLAG = p_PRICE_LIST_LINE_rec.print_on_invoice_flag
, PROGRAM_APPLICATION_ID = p_PRICE_LIST_LINE_rec.program_application_id
, PROGRAM_ID = p_PRICE_LIST_LINE_rec.program_id
, PROGRAM_UPDATE_DATE = p_PRICE_LIST_LINE_rec.program_update_date
, REBATE_TRANSACTION_TYPE_CODE = p_PRICE_LIST_LINE_rec.rebate_trxn_type_code
, RELATED_ITEM_ID = p_PRICE_LIST_LINE_rec.related_item_id
, RELATIONSHIP_TYPE_ID = p_PRICE_LIST_LINE_rec.relationship_type_id
, REPRICE_FLAG = p_PRICE_LIST_LINE_rec.reprice_flag
, REQUEST_ID = p_PRICE_LIST_LINE_rec.request_id
, REVISION = p_PRICE_LIST_LINE_rec.revision
, REVISION_DATE = trunc(p_PRICE_LIST_LINE_rec.revision_date)
, REVISION_REASON_CODE = p_PRICE_LIST_LINE_rec.revision_reason_code
, START_DATE_ACTIVE = trunc(p_PRICE_LIST_LINE_rec.start_date_active)
, SUBSTITUTION_ATTRIBUTE = p_PRICE_LIST_LINE_rec.substitution_attribute
, SUBSTITUTION_CONTEXT = p_PRICE_LIST_LINE_rec.substitution_context
, SUBSTITUTION_VALUE = p_PRICE_LIST_LINE_rec.substitution_value
, PRICING_GROUP_SEQUENCE = 0
, PRICING_PHASE_ID = 1
, INCOMPATIBILITY_GRP_CODE = 'EXCL'
, QUALIFICATION_IND = p_PRICE_LIST_LINE_rec.qualification_ind
, RECURRING_VALUE = p_PRICE_LIST_LINE_rec.recurring_value -- block pricing
, CUSTOMER_ITEM_ID = p_PRICE_LIST_LINE_rec.customer_item_id
, BREAK_UOM_CODE = p_PRICE_LIST_LINE_rec.break_uom_code -- OKS proration
, BREAK_UOM_CONTEXT = p_PRICE_LIST_LINE_rec.break_uom_context -- OKS
, BREAK_UOM_ATTRIBUTE = p_PRICE_LIST_LINE_rec.break_uom_attribute -- OKS proration
, CONTINUOUS_PRICE_BREAK_FLAG = p_PRICE_LIST_LINE_rec.continuous_price_break_flag
-- Continuous price breaks
WHERE LIST_LINE_ID = p_PRICE_LIST_LINE_rec.list_line_id
;
UPDATE qp_pte_segments set used_in_setup='Y'
WHERE nvl(used_in_setup,'N')='N'
AND segment_id IN
(SELECT a.segment_id FROM qp_segments_b a,qp_prc_contexts_b b
WHERE a.segment_mapping_column=p_PRICE_LIST_LINE_rec.break_uom_attribute
AND a.prc_context_id=b.prc_context_id
AND b.prc_context_type = 'PRICING_ATTRIBUTE'
AND b.prc_context_code=p_PRICE_LIST_LINE_rec.break_uom_context);
, 'Update_Row'
);
END Update_Row;
PROCEDURE Insert_Row
( p_PRICE_LIST_LINE_rec IN QP_Price_List_PUB.Price_List_Line_Rec_Type
)
IS
l_return_status varchar2(30);
SELECT ACTIVE_FLAG
INTO l_active_flag
FROM QP_LIST_HEADERS_B
WHERE list_header_id=p_PRICE_LIST_LINE_rec.list_header_id;
INSERT INTO QP_LIST_LINES
( ACCRUAL_QTY
, ACCRUAL_UOM_CODE
, ARITHMETIC_OPERATOR
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, AUTOMATIC_FLAG
, BASE_QTY
, BASE_UOM_CODE
, COMMENTS
, CONTEXT
, CREATED_BY
, CREATION_DATE
, EFFECTIVE_PERIOD_UOM
, END_DATE_ACTIVE
, ESTIM_ACCRUAL_RATE
, GENERATE_USING_FORMULA_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_NO
, LIST_LINE_TYPE_CODE
, LIST_PRICE
, PRODUCT_PRECEDENCE
, MODIFIER_LEVEL_CODE
, NUMBER_EFFECTIVE_PERIODS
, OPERAND
, ORGANIZATION_ID
, OVERRIDE_FLAG
, PERCENT_PRICE
, PRICE_BREAK_TYPE_CODE
, PRICE_BY_FORMULA_ID
, PRIMARY_UOM_FLAG
, PRINT_ON_INVOICE_FLAG
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REBATE_TRANSACTION_TYPE_CODE
, RELATED_ITEM_ID
, RELATIONSHIP_TYPE_ID
, REPRICE_FLAG
, REQUEST_ID
, REVISION
, REVISION_DATE
, REVISION_REASON_CODE
, START_DATE_ACTIVE
, SUBSTITUTION_ATTRIBUTE
, SUBSTITUTION_CONTEXT
, SUBSTITUTION_VALUE
, PRICING_GROUP_SEQUENCE
, PRICING_PHASE_ID
, INCOMPATIBILITY_GRP_CODE
, QUALIFICATION_IND
, RECURRING_VALUE -- block pricing
, CUSTOMER_ITEM_ID
, BREAK_UOM_CODE -- OKS proration
, BREAK_UOM_CONTEXT -- OKS proration
, BREAK_UOM_ATTRIBUTE -- OKS proration
, CONTINUOUS_PRICE_BREAK_FLAG -- Continuous price breaks
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,orig_sys_line_ref
,ORIG_SYS_HEADER_REF
)
VALUES
( p_PRICE_LIST_LINE_rec.accrual_qty
, p_PRICE_LIST_LINE_rec.accrual_uom_code
, p_PRICE_LIST_LINE_rec.arithmetic_operator
, p_PRICE_LIST_LINE_rec.attribute1
, p_PRICE_LIST_LINE_rec.attribute10
, p_PRICE_LIST_LINE_rec.attribute11
, p_PRICE_LIST_LINE_rec.attribute12
, p_PRICE_LIST_LINE_rec.attribute13
, p_PRICE_LIST_LINE_rec.attribute14
, p_PRICE_LIST_LINE_rec.attribute15
, p_PRICE_LIST_LINE_rec.attribute2
, p_PRICE_LIST_LINE_rec.attribute3
, p_PRICE_LIST_LINE_rec.attribute4
, p_PRICE_LIST_LINE_rec.attribute5
, p_PRICE_LIST_LINE_rec.attribute6
, p_PRICE_LIST_LINE_rec.attribute7
, p_PRICE_LIST_LINE_rec.attribute8
, p_PRICE_LIST_LINE_rec.attribute9
, p_PRICE_LIST_LINE_rec.automatic_flag
, p_PRICE_LIST_LINE_rec.base_qty
, p_PRICE_LIST_LINE_rec.base_uom_code
, p_PRICE_LIST_LINE_rec.comments
, p_PRICE_LIST_LINE_rec.context
, p_PRICE_LIST_LINE_rec.created_by
, p_PRICE_LIST_LINE_rec.creation_date
, p_PRICE_LIST_LINE_rec.effective_period_uom
, trunc(p_PRICE_LIST_LINE_rec.end_date_active)
, p_PRICE_LIST_LINE_rec.estim_accrual_rate
, p_PRICE_LIST_LINE_rec.generate_using_formula_id
, p_PRICE_LIST_LINE_rec.inventory_item_id
, p_PRICE_LIST_LINE_rec.last_updated_by
, p_PRICE_LIST_LINE_rec.last_update_date
, p_PRICE_LIST_LINE_rec.last_update_login
, p_PRICE_LIST_LINE_rec.list_header_id
, p_PRICE_LIST_LINE_rec.list_line_id
, p_PRICE_LIST_LINE_rec.list_line_no
, p_PRICE_LIST_LINE_rec.list_line_type_code
, p_PRICE_LIST_LINE_rec.list_price
, p_PRICE_LIST_LINE_rec.product_precedence
, p_PRICE_LIST_LINE_rec.modifier_level_code
, p_PRICE_LIST_LINE_rec.number_effective_periods
-- , p_PRICE_LIST_LINE_rec.operand
, l_operand --Modified for 1732601
, p_PRICE_LIST_LINE_rec.organization_id
, p_PRICE_LIST_LINE_rec.override_flag
, p_PRICE_LIST_LINE_rec.percent_price
, p_PRICE_LIST_LINE_rec.price_break_type_code
, p_PRICE_LIST_LINE_rec.price_by_formula_id
, p_PRICE_LIST_LINE_rec.primary_uom_flag
, p_PRICE_LIST_LINE_rec.print_on_invoice_flag
, p_PRICE_LIST_LINE_rec.program_application_id
, p_PRICE_LIST_LINE_rec.program_id
, p_PRICE_LIST_LINE_rec.program_update_date
, p_PRICE_LIST_LINE_rec.rebate_trxn_type_code
, p_PRICE_LIST_LINE_rec.related_item_id
, p_PRICE_LIST_LINE_rec.relationship_type_id
, p_PRICE_LIST_LINE_rec.reprice_flag
, p_PRICE_LIST_LINE_rec.request_id
, p_PRICE_LIST_LINE_rec.revision
, trunc(p_PRICE_LIST_LINE_rec.revision_date)
, p_PRICE_LIST_LINE_rec.revision_reason_code
, trunc(p_PRICE_LIST_LINE_rec.start_date_active)
, p_PRICE_LIST_LINE_rec.substitution_attribute
, p_PRICE_LIST_LINE_rec.substitution_context
, p_PRICE_LIST_LINE_rec.substitution_value
, 0
, 1
, 'EXCL'
, p_PRICE_LIST_LINE_rec.qualification_ind --Euro Bug 2138996
, p_PRICE_LIST_LINE_rec.recurring_value -- block pricing
, p_PRICE_LIST_LINE_rec.customer_item_id
, p_PRICE_LIST_LINE_rec.break_uom_code -- OKS proration
, p_PRICE_LIST_LINE_rec.break_uom_context -- OKS
, p_PRICE_LIST_LINE_rec.break_uom_attribute -- OKS proration
, p_PRICE_LIST_LINE_rec.continuous_price_break_flag -- Continuous price breaks
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(p_PRICE_LIST_LINE_rec.list_line_id)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_PRICE_LIST_LINE_rec.list_header_id)
);
p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND,
x_return_status => l_return_status);
UPDATE qp_pte_segments set used_in_setup='Y'
WHERE nvl(used_in_setup,'N')='N'
AND segment_id IN
(SELECT a.segment_id FROM qp_segments_b a,qp_prc_contexts_b b
WHERE a.segment_mapping_column=p_PRICE_LIST_LINE_rec.break_uom_attribute
AND a.prc_context_id=b.prc_context_id
AND b.prc_context_type = 'PRICING_ATTRIBUTE'
AND b.prc_context_code=p_PRICE_LIST_LINE_rec.break_uom_context);
, 'Insert_Row'
);
END Insert_Row;
PROCEDURE Delete_Row
( p_list_line_id IN NUMBER
)
IS
L_LINE_TYPE_CODE VARCHAR2(30) := NULL;
select from_rltd_modifier_id, to_rltd_modifier_id
from qp_rltd_modifiers
where from_rltd_modifier_id = line_id;
SELECT LIST_LINE_TYPE_CODE
INTO L_LINE_TYPE_CODE
FROM QP_LIST_LINES
WHERE LIST_LINE_ID = p_list_line_id;
/* delete all the related modifier lines if the line is a PBH,
else delete all references of this line in qp_rltd_modifiers */
IF L_LINE_TYPE_CODE = 'PBH' THEN
for get_rltd_mods_rec in get_rltd_mods(p_list_line_id) loop
QP_PRICE_LIST_LINE_UTIL.DELETE_ROW(get_rltd_mods_rec.to_rltd_modifier_id);
DELETE FROM QP_RLTD_MODIFIERS
WHERE FROM_RLTD_MODIFIER_ID = p_list_line_id;
DELETE FROM QP_RLTD_MODIFIERS
WHERE TO_RLTD_MODIFIER_ID = p_list_line_id;
/* delete all the pricing attributes */
DELETE FROM QP_PRICING_ATTRIBUTES
WHERE LIST_LINE_ID = p_list_line_id; /* done with pricing attributes */
DELETE FROM QP_LIST_LINES
WHERE LIST_LINE_ID = p_list_line_id
;
, 'Delete_Row'
);
END Delete_Row;
SELECT ACCRUAL_QTY
, ACCRUAL_UOM_CODE
, ARITHMETIC_OPERATOR
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, AUTOMATIC_FLAG
, BASE_QTY
, BASE_UOM_CODE
, COMMENTS
, CONTEXT
, CREATED_BY
, CREATION_DATE
, EFFECTIVE_PERIOD_UOM
, END_DATE_ACTIVE
, ESTIM_ACCRUAL_RATE
, GENERATE_USING_FORMULA_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_NO
, LIST_LINE_TYPE_CODE
, LIST_PRICE
, PRODUCT_PRECEDENCE
, MODIFIER_LEVEL_CODE
, NUMBER_EFFECTIVE_PERIODS
, OPERAND
, ORGANIZATION_ID
, OVERRIDE_FLAG
, PERCENT_PRICE
, PRICE_BREAK_TYPE_CODE
, PRICE_BY_FORMULA_ID
, PRIMARY_UOM_FLAG
, PRINT_ON_INVOICE_FLAG
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REBATE_TRANSACTION_TYPE_CODE
, RELATED_ITEM_ID
, RELATIONSHIP_TYPE_ID
, REPRICE_FLAG
, REQUEST_ID
, REVISION
, REVISION_DATE
, REVISION_REASON_CODE
, START_DATE_ACTIVE
, SUBSTITUTION_ATTRIBUTE
, SUBSTITUTION_CONTEXT
, SUBSTITUTION_VALUE
, QUALIFICATION_IND
, RECURRING_VALUE -- block pricing
, CUSTOMER_ITEM_ID
, BREAK_UOM_CODE
, BREAK_UOM_CONTEXT
, BREAK_UOM_ATTRIBUTE
, CONTINUOUS_PRICE_BREAK_FLAG -- Continuous price breaks
FROM QP_LIST_LINES
WHERE LIST_LINE_ID = p_list_line_id;
SELECT ACCRUAL_QTY
, ACCRUAL_UOM_CODE
, ARITHMETIC_OPERATOR
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, AUTOMATIC_FLAG
, BASE_QTY
, BASE_UOM_CODE
, COMMENTS
, CONTEXT
, CREATED_BY
, CREATION_DATE
, EFFECTIVE_PERIOD_UOM
, END_DATE_ACTIVE
, ESTIM_ACCRUAL_RATE
, GENERATE_USING_FORMULA_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_NO
, LIST_LINE_TYPE_CODE
, LIST_PRICE
, PRODUCT_PRECEDENCE
, MODIFIER_LEVEL_CODE
, NUMBER_EFFECTIVE_PERIODS
, OPERAND
, ORGANIZATION_ID
, OVERRIDE_FLAG
, PERCENT_PRICE
, PRICE_BREAK_TYPE_CODE
, PRICE_BY_FORMULA_ID
, PRIMARY_UOM_FLAG
, PRINT_ON_INVOICE_FLAG
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REBATE_TRANSACTION_TYPE_CODE
, RELATED_ITEM_ID
, RELATIONSHIP_TYPE_ID
, REPRICE_FLAG
, REQUEST_ID
, REVISION
, REVISION_DATE
, REVISION_REASON_CODE
, START_DATE_ACTIVE
, SUBSTITUTION_ATTRIBUTE
, SUBSTITUTION_CONTEXT
, SUBSTITUTION_VALUE
, QUALIFICATION_IND
, RECURRING_VALUE -- block pricing
, CUSTOMER_ITEM_ID
, BREAK_UOM_CODE
, BREAK_UOM_CONTEXT
, BREAK_UOM_ATTRIBUTE
, CONTINUOUS_PRICE_BREAK_FLAG -- Continuous price breaks
FROM QP_LIST_LINES
WHERE LIST_HEADER_ID = p_list_header_id;
l_PRICE_LIST_LINE_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_PRICE_LIST_LINE_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_PRICE_LIST_LINE_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
l_PRICE_LIST_LINE_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
SELECT RLTD_MODIFIER_GRP_NO
, RLTD_MODIFIER_GRP_TYPE
, FROM_RLTD_MODIFIER_ID
, TO_RLTD_MODIFIER_ID
, RLTD_MODIFIER_ID
INTO l_PRICE_LIST_LINE_rec.rltd_modifier_group_no
, l_PRICE_LIST_LINE_rec.rltd_modifier_grp_type
, l_PRICE_LIST_LINE_rec.from_rltd_modifier_id
, l_PRICE_LIST_LINE_rec.to_rltd_modifier_id
, l_PRICE_LIST_LINE_rec.rltd_modifier_id
FROM QP_RLTD_MODIFIERS
WHERE ( TO_RLTD_MODIFIER_ID = l_implicit_rec.LIST_LINE_ID );
l_PRICE_LIST_LINE_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_PRICE_LIST_LINE_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_PRICE_LIST_LINE_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
l_PRICE_LIST_LINE_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
SELECT RLTD_MODIFIER_GRP_NO
, RLTD_MODIFIER_GRP_TYPE
, FROM_RLTD_MODIFIER_ID
, TO_RLTD_MODIFIER_ID
, RLTD_MODIFIER_ID
INTO l_PRICE_LIST_LINE_rec.rltd_modifier_group_no
, l_PRICE_LIST_LINE_rec.rltd_modifier_grp_type
, l_PRICE_LIST_LINE_rec.from_rltd_modifier_id
, l_PRICE_LIST_LINE_rec.to_rltd_modifier_id
, l_PRICE_LIST_LINE_rec.rltd_modifier_id
FROM QP_RLTD_MODIFIERS
WHERE ( TO_RLTD_MODIFIER_ID = l_implicit_rec.LIST_LINE_ID );
SELECT ACCRUAL_QTY
, ACCRUAL_UOM_CODE
, ARITHMETIC_OPERATOR
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, AUTOMATIC_FLAG
, BASE_QTY
, BASE_UOM_CODE
, COMMENTS
, CONTEXT
, CREATED_BY
, CREATION_DATE
, EFFECTIVE_PERIOD_UOM
, END_DATE_ACTIVE
, ESTIM_ACCRUAL_RATE
, GENERATE_USING_FORMULA_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_TYPE_CODE
, LIST_PRICE
, PRODUCT_PRECEDENCE
, MODIFIER_LEVEL_CODE
, NUMBER_EFFECTIVE_PERIODS
, OPERAND
, ORGANIZATION_ID
, OVERRIDE_FLAG
, PERCENT_PRICE
, PRICE_BREAK_TYPE_CODE
, PRICE_BY_FORMULA_ID
, PRIMARY_UOM_FLAG
, PRINT_ON_INVOICE_FLAG
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REBATE_TRANSACTION_TYPE_CODE
, RECURRING_VALUE -- block pricing
, RELATED_ITEM_ID
, RELATIONSHIP_TYPE_ID
, REPRICE_FLAG
, REQUEST_ID
, REVISION
, REVISION_DATE
, REVISION_REASON_CODE
, START_DATE_ACTIVE
, SUBSTITUTION_ATTRIBUTE
, SUBSTITUTION_CONTEXT
, SUBSTITUTION_VALUE
, CUSTOMER_ITEM_ID
, BREAK_UOM_CODE
, BREAK_UOM_CONTEXT
, BREAK_UOM_ATTRIBUTE
, CONTINUOUS_PRICE_BREAK_FLAG -- Continuous price breaks
INTO l_PRICE_LIST_LINE_rec.accrual_qty
, l_PRICE_LIST_LINE_rec.accrual_uom_code
, l_PRICE_LIST_LINE_rec.arithmetic_operator
, l_PRICE_LIST_LINE_rec.attribute1
, l_PRICE_LIST_LINE_rec.attribute10
, l_PRICE_LIST_LINE_rec.attribute11
, l_PRICE_LIST_LINE_rec.attribute12
, l_PRICE_LIST_LINE_rec.attribute13
, l_PRICE_LIST_LINE_rec.attribute14
, l_PRICE_LIST_LINE_rec.attribute15
, l_PRICE_LIST_LINE_rec.attribute2
, l_PRICE_LIST_LINE_rec.attribute3
, l_PRICE_LIST_LINE_rec.attribute4
, l_PRICE_LIST_LINE_rec.attribute5
, l_PRICE_LIST_LINE_rec.attribute6
, l_PRICE_LIST_LINE_rec.attribute7
, l_PRICE_LIST_LINE_rec.attribute8
, l_PRICE_LIST_LINE_rec.attribute9
, l_PRICE_LIST_LINE_rec.automatic_flag
, l_PRICE_LIST_LINE_rec.base_qty
, l_PRICE_LIST_LINE_rec.base_uom_code
, l_PRICE_LIST_LINE_rec.comments
, l_PRICE_LIST_LINE_rec.context
, l_PRICE_LIST_LINE_rec.created_by
, l_PRICE_LIST_LINE_rec.creation_date
, l_PRICE_LIST_LINE_rec.effective_period_uom
, l_PRICE_LIST_LINE_rec.end_date_active
, l_PRICE_LIST_LINE_rec.estim_accrual_rate
, l_PRICE_LIST_LINE_rec.generate_using_formula_id
, l_PRICE_LIST_LINE_rec.inventory_item_id
, l_PRICE_LIST_LINE_rec.last_updated_by
, l_PRICE_LIST_LINE_rec.last_update_date
, l_PRICE_LIST_LINE_rec.last_update_login
, l_PRICE_LIST_LINE_rec.list_header_id
, l_PRICE_LIST_LINE_rec.list_line_id
, l_PRICE_LIST_LINE_rec.list_line_type_code
, l_PRICE_LIST_LINE_rec.list_price
, l_PRICE_LIST_LINE_rec.product_precedence
, l_PRICE_LIST_LINE_rec.modifier_level_code
, l_PRICE_LIST_LINE_rec.number_effective_periods
, l_PRICE_LIST_LINE_rec.operand
, l_PRICE_LIST_LINE_rec.organization_id
, l_PRICE_LIST_LINE_rec.override_flag
, l_PRICE_LIST_LINE_rec.percent_price
, l_PRICE_LIST_LINE_rec.price_break_type_code
, l_PRICE_LIST_LINE_rec.price_by_formula_id
, l_PRICE_LIST_LINE_rec.primary_uom_flag
, l_PRICE_LIST_LINE_rec.print_on_invoice_flag
, l_PRICE_LIST_LINE_rec.program_application_id
, l_PRICE_LIST_LINE_rec.program_id
, l_PRICE_LIST_LINE_rec.program_update_date
, l_PRICE_LIST_LINE_rec.rebate_trxn_type_code
, l_PRICE_LIST_LINE_rec.recurring_value -- block pricing
, l_PRICE_LIST_LINE_rec.related_item_id
, l_PRICE_LIST_LINE_rec.relationship_type_id
, l_PRICE_LIST_LINE_rec.reprice_flag
, l_PRICE_LIST_LINE_rec.request_id
, l_PRICE_LIST_LINE_rec.revision
, l_PRICE_LIST_LINE_rec.revision_date
, l_PRICE_LIST_LINE_rec.revision_reason_code
, l_PRICE_LIST_LINE_rec.start_date_active
, l_PRICE_LIST_LINE_rec.substitution_attribute
, l_PRICE_LIST_LINE_rec.substitution_context
, l_PRICE_LIST_LINE_rec.substitution_value
, l_PRICE_LIST_LINE_rec.customer_item_id
, l_PRICE_LIST_LINE_rec.break_uom_code
, l_PRICE_LIST_LINE_rec.break_uom_context
, l_PRICE_LIST_LINE_rec.break_uom_attribute
, l_PRICE_LIST_LINE_rec.continuous_price_break_flag -- Continuous price breaks
FROM QP_LIST_LINES
WHERE LIST_LINE_ID = p_PRICE_LIST_LINE_rec.list_line_id
FOR UPDATE NOWAIT;
oe_debug_pub.add(' last_updated_by:'||l_PRICE_LIST_LINE_rec.last_updated_by||':'||p_PRICE_LIST_LINE_rec.last_updated_by||':');
oe_debug_pub.add(' last_update_date:'||l_PRICE_LIST_LINE_rec.last_update_date||':'||p_PRICE_LIST_LINE_rec.last_update_date||':');
oe_debug_pub.add(' accrual qty:'||l_PRICE_LIST_LINE_rec.last_update_login||':'||p_PRICE_LIST_LINE_rec.last_update_login||':');
oe_debug_pub.add(' program_update_date:'||l_PRICE_LIST_LINE_rec.program_update_date||':'||p_PRICE_LIST_LINE_rec.program_update_date||':');
AND QP_GLOBALS.Equal(trunc(p_PRICE_LIST_LINE_rec.program_update_date),
trunc(l_PRICE_LIST_LINE_rec.program_update_date))
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.rebate_trxn_type_code,
l_PRICE_LIST_LINE_rec.rebate_trxn_type_code)
-- block pricing
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.recurring_value,
l_PRICE_LIST_LINE_rec.recurring_value)
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.related_item_id,
l_PRICE_LIST_LINE_rec.related_item_id)
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.relationship_type_id,
l_PRICE_LIST_LINE_rec.relationship_type_id)
-- AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.reprice_flag,
-- l_PRICE_LIST_LINE_rec.reprice_flag)
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.request_id,
l_PRICE_LIST_LINE_rec.request_id)
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.revision,
l_PRICE_LIST_LINE_rec.revision)
AND QP_GLOBALS.Equal(trunc(p_PRICE_LIST_LINE_rec.revision_date),
trunc(l_PRICE_LIST_LINE_rec.revision_date))
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.revision_reason_code,
l_PRICE_LIST_LINE_rec.revision_reason_code)
AND QP_GLOBALS.Equal(trunc(p_PRICE_LIST_LINE_rec.start_date_active),
trunc(l_PRICE_LIST_LINE_rec.start_date_active))
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.substitution_attribute,
l_PRICE_LIST_LINE_rec.substitution_attribute)
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.substitution_context,
l_PRICE_LIST_LINE_rec.substitution_context)
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.substitution_value,
l_PRICE_LIST_LINE_rec.substitution_value)
AND QP_GLOBALS.Equal(p_PRICE_LIST_LINE_rec.customer_item_id,
l_PRICE_LIST_LINE_rec.customer_item_id)
THEN
-- Row has not changed. Set out parameter.
x_PRICE_LIST_LINE_rec := l_PRICE_LIST_LINE_rec;
oe_debug_pub.ADD('LAST_UPDATED_BY :'||l_PRICE_LIST_LINE_rec.LAST_UPDATED_BY||':'||p_PRICE_LIST_LINE_rec.LAST_UPDATED_BY||':');
oe_debug_pub.ADD('LAST_UPDATE_DATE :'||l_PRICE_LIST_LINE_rec.LAST_UPDATE_DATE||':'||p_PRICE_LIST_LINE_rec.LAST_UPDATE_DATE||':');
oe_debug_pub.ADD('LAST_UPDATE_LOGIN :'||l_PRICE_LIST_LINE_rec.LAST_UPDATE_LOGIN||':'||p_PRICE_LIST_LINE_rec.LAST_UPDATE_LOGIN||':');
oe_debug_pub.ADD('PROGRAM_UPDATE_DATE :'||l_PRICE_LIST_LINE_rec.PROGRAM_UPDATE_DATE||':'||p_PRICE_LIST_LINE_rec.PROGRAM_UPDATE_DATE||':');
FND_MESSAGE.SET_NAME('QP','OE_LOCK_ROW_DELETED');
oe_debug_pub.add('last_updated_by ' || p_counter || ': ' ||p_PRICE_LIST_LINE_rec.last_updated_by);
oe_debug_pub.add('last_update_date ' || p_counter || ': ' ||p_PRICE_LIST_LINE_rec.last_update_date);
oe_debug_pub.add('accrual qty ' || p_counter || ': ' ||p_PRICE_LIST_LINE_rec.last_update_login);
oe_debug_pub.add('program_update_date ' || p_counter || ': ' ||p_PRICE_LIST_LINE_rec.program_update_date);
select to_number(p_qual_attr_value)
into l_qual_attr_value
from dual;
select concatenated_segments
into l_item_name
from mtl_system_items_vl
where inventory_item_id = to_number(p_attr_value)
and organization_id = l_organization_id and rownum=1;
select concatenated_segments
into l_category_name
from mtl_categories_kfv
where category_id = to_number(p_attr_value) and rownum=1;
select category_name
into l_category_name
from qp_item_categories_v
where category_id = to_number(p_attr_value) and rownum=1;
select to_char(organization_id)
into l_name
from mtl_system_items_b
where inventory_item_id = to_number(p_attr_value)
and organization_id = G_ORGANIZATION_ID;
SELECT b.segment_code
FROM qp_segments_tl a, qp_segments_b b,
qp_prc_contexts_b c, qp_pte_segments d
WHERE c.prc_context_type = a_context_type
AND c.prc_context_code = a_context_code
AND c.prc_context_id = b.prc_context_id
AND b.segment_mapping_column = a_attribute
AND b.segment_id = a.segment_id
AND a.language = userenv('LANG')
AND b.segment_id = d.segment_id
AND d.pte_code = a_pte_code;
select end_user_column_name
INTO x_segment_name
from FND_DESCR_FLEX_COL_USAGE_VL
where APPLICATION_ID = 661 and
DESCRIPTIVE_FLEXFIELD_NAME = p_FlexField_Name and
DESCRIPTIVE_FLEX_CONTEXT_CODE = p_Context_Name and
application_column_name = p_attribute and
enabled_flag='Y';