The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF NOT OE_GLOBALS.Equal(p_PRICE_LIST_rec.last_updated_by,p_old_PRICE_LIST_rec.last_updated_by)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_PRICE_LIST_UTIL.G_LAST_UPDATED_BY;
IF NOT OE_GLOBALS.Equal(p_PRICE_LIST_rec.last_update_date,p_old_PRICE_LIST_rec.last_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_PRICE_LIST_UTIL.G_LAST_UPDATE_DATE;
IF NOT OE_GLOBALS.Equal(p_PRICE_LIST_rec.last_update_login,p_old_PRICE_LIST_rec.last_update_login)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_PRICE_LIST_UTIL.G_LAST_UPDATE_LOGIN;
IF NOT OE_GLOBALS.Equal(p_PRICE_LIST_rec.program_update_date,p_old_PRICE_LIST_rec.program_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_PRICE_LIST_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) := OE_PRICE_LIST_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) := OE_PRICE_LIST_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) := OE_PRICE_LIST_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) := OE_PRICE_LIST_UTIL.G_PROGRAM_UPDATE_DATE;
IF NOT OE_GLOBALS.Equal(p_PRICE_LIST_rec.last_updated_by,p_old_PRICE_LIST_rec.last_updated_by)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_PRICE_LIST_rec.last_update_date,p_old_PRICE_LIST_rec.last_update_date)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_PRICE_LIST_rec.last_update_login,p_old_PRICE_LIST_rec.last_update_login)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_PRICE_LIST_rec.program_update_date,p_old_PRICE_LIST_rec.program_update_date)
THEN
NULL;
IF l_PRICE_LIST_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_PRICE_LIST_rec.last_updated_by := p_old_PRICE_LIST_rec.last_updated_by;
IF l_PRICE_LIST_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_PRICE_LIST_rec.last_update_date := p_old_PRICE_LIST_rec.last_update_date;
IF l_PRICE_LIST_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_PRICE_LIST_rec.last_update_login := p_old_PRICE_LIST_rec.last_update_login;
IF l_PRICE_LIST_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_PRICE_LIST_rec.program_update_date := p_old_PRICE_LIST_rec.program_update_date;
IF l_PRICE_LIST_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_PRICE_LIST_rec.last_updated_by := NULL;
IF l_PRICE_LIST_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_PRICE_LIST_rec.last_update_date := NULL;
IF l_PRICE_LIST_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_PRICE_LIST_rec.last_update_login := NULL;
IF l_PRICE_LIST_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_PRICE_LIST_rec.program_update_date := NULL;
PROCEDURE Update_Row
( p_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type
)
IS
l_sec_price_list_id number;
QP_LIST_HEADERS_PKG.UPDATE_ROW (
X_LIST_HEADER_ID => p_PRICE_LIST_rec.price_list_id,
X_CONTEXT => p_PRICE_LIST_rec.context,
X_ATTRIBUTE1 => p_PRICE_LIST_rec.attribute1,
X_ATTRIBUTE2 => p_PRICE_LIST_rec.attribute2,
X_ATTRIBUTE3 => p_PRICE_LIST_rec.attribute3,
X_ATTRIBUTE4 => p_PRICE_LIST_rec.attribute4,
X_ATTRIBUTE5 => p_PRICE_LIST_rec.attribute5,
X_ATTRIBUTE6 => p_PRICE_LIST_rec.attribute6,
X_ATTRIBUTE7 => p_PRICE_LIST_rec.attribute7,
X_ATTRIBUTE8 => p_PRICE_LIST_rec.attribute8,
X_ATTRIBUTE9 => p_PRICE_LIST_rec.attribute9,
X_ATTRIBUTE10 => p_PRICE_LIST_rec.attribute10,
X_ATTRIBUTE11 => p_PRICE_LIST_rec.attribute11,
X_ATTRIBUTE12 => p_PRICE_LIST_rec.attribute12,
X_ATTRIBUTE13 => p_PRICE_LIST_rec.attribute13,
X_ATTRIBUTE14 => p_PRICE_LIST_rec.attribute14,
X_ATTRIBUTE15 => p_PRICE_LIST_rec.attribute15,
X_CURRENCY_CODE => p_PRICE_LIST_rec.currency_code,
X_SHIP_METHOD_CODE => p_PRICE_LIST_rec.ship_method_code,
X_FREIGHT_TERMS_CODE => p_PRICE_LIST_rec.freight_terms_code,
X_START_DATE_ACTIVE => p_PRICE_LIST_rec.start_date_active,
X_END_DATE_ACTIVE => p_PRICE_LIST_rec.end_date_active,
X_AUTOMATIC_FLAG => 'N',
X_LIST_TYPE_CODE => 'PRL',
X_TERMS_ID => p_PRICE_LIST_rec.terms_id,
X_ROUNDING_FACTOR => p_PRICE_LIST_rec.rounding_factor,
X_REQUEST_ID => p_PRICE_LIST_rec.request_id,
X_NAME => p_PRICE_LIST_rec.name,
X_DESCRIPTION => p_PRICE_LIST_rec.description,
X_LAST_UPDATE_DATE => p_PRICE_LIST_rec.last_update_date,
X_LAST_UPDATED_BY => p_PRICE_LIST_rec.last_updated_by,
X_LAST_UPDATE_LOGIN => p_PRICE_LIST_rec.last_update_login );
/* delete qualifier for secondary price list and create a new qualifier */
QP_UTIL.Get_Context_Attribute('PRICE_LIST_ID', l_context, l_attribute);
delete from qp_qualifiers
where qualifier_context = l_context
and qualifier_attribute = l_attribute
and qualifier_attr_value = p_PRICE_LIST_rec.price_list_id
and list_header_id = l_sec_price_list_id
and qualifier_rule_id is null;
INSERT INTO QP_QUALIFIERS (
QUALIFIER_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LIST_HEADER_ID,
COMPARISON_OPERATOR_CODE,
QUALIFIER_CONTEXT,
QUALIFIER_ATTRIBUTE,
QUALIFIER_ATTR_VALUE,
QUALIFIER_GROUPING_NO,
EXCLUDER_FLAG
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_HEADER_REF
)
VALUES (
QP_QUALIFIERS_S.nextval,
sysdate,
1,
sysdate,
1,
1,
p_PRICE_LIST_rec.secondary_price_list_id,
'=',
l_context,
l_attribute,
to_char(p_PRICE_LIST_rec.price_list_id),
qp.qp_qualifier_group_no_s.nextval,
'N'
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(QP_QUALIFIERS_S.CURRVAL)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_PRICE_LIST_rec.secondary_price_list_id)
);
, 'Update_Row'
);
END Update_Row;
PROCEDURE Insert_Row
( p_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type
)
IS
-- new added
-- cursor not added to get rowid
l_rowid varchar2(240); /* should it be varchar2(100)? */
oe_debug_pub.add('Entering OE_Price_List_Util.Insert_Row');
and tp_attributes to the insert_row procedure */
QP_LIST_HEADERS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_LIST_HEADER_ID => p_PRICE_LIST_rec.price_list_id,
X_CONTEXT => p_PRICE_LIST_rec.context,
X_ATTRIBUTE1 => p_PRICE_LIST_rec.attribute1,
X_ATTRIBUTE2 => p_PRICE_LIST_rec.attribute2,
X_ATTRIBUTE3 => p_PRICE_LIST_rec.attribute3,
X_ATTRIBUTE4 => p_PRICE_LIST_rec.attribute4,
X_ATTRIBUTE5 => p_PRICE_LIST_rec.attribute5,
X_ATTRIBUTE6 => p_PRICE_LIST_rec.attribute6,
X_ATTRIBUTE7 => p_PRICE_LIST_rec.attribute7,
X_ATTRIBUTE8 => p_PRICE_LIST_rec.attribute8,
X_ATTRIBUTE9 => p_PRICE_LIST_rec.attribute9,
X_ATTRIBUTE10 => p_PRICE_LIST_rec.attribute10,
X_ATTRIBUTE11 => p_PRICE_LIST_rec.attribute11,
X_ATTRIBUTE12 => p_PRICE_LIST_rec.attribute12,
X_ATTRIBUTE13 => p_PRICE_LIST_rec.attribute13,
X_ATTRIBUTE14 => p_PRICE_LIST_rec.attribute14,
X_ATTRIBUTE15 => p_PRICE_LIST_rec.attribute15,
X_CURRENCY_CODE => p_PRICE_LIST_rec.currency_code,
X_SHIP_METHOD_CODE => p_PRICE_LIST_rec.ship_method_code,
X_FREIGHT_TERMS_CODE => p_PRICE_LIST_rec.freight_terms_code,
X_START_DATE_ACTIVE => p_PRICE_LIST_rec.start_date_active,
X_END_DATE_ACTIVE => p_PRICE_LIST_rec.end_date_active,
X_AUTOMATIC_FLAG => 'N',
X_LIST_TYPE_CODE => 'PRL',
X_TERMS_ID => p_PRICE_LIST_rec.terms_id,
X_ROUNDING_FACTOR => p_PRICE_LIST_rec.rounding_factor,
X_REQUEST_ID => p_PRICE_LIST_rec.request_id,
X_NAME => p_PRICE_LIST_rec.name,
X_DESCRIPTION => p_PRICE_LIST_rec.description,
X_CREATION_DATE => p_PRICE_LIST_rec.creation_date,
X_CREATED_BY => p_PRICE_LIST_rec.created_by,
X_LAST_UPDATE_DATE => p_PRICE_LIST_rec.last_update_date,
X_LAST_UPDATED_BY => p_PRICE_LIST_rec.last_updated_by,
X_LAST_UPDATE_LOGIN => p_PRICE_LIST_rec.last_update_login );
INSERT INTO QP_QUALIFIERS (
QUALIFIER_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LIST_HEADER_ID,
COMPARISON_OPERATOR_CODE,
QUALIFIER_CONTEXT,
QUALIFIER_ATTRIBUTE,
QUALIFIER_ATTR_VALUE,
QUALIFIER_GROUPING_NO,
EXCLUDER_FLAG
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_HEADER_REF)
VALUES (
QP_QUALIFIERS_S.nextval,
sysdate,
1,
sysdate,
1,
1,
p_PRICE_LIST_rec.price_list_id,
'=',
l_context,
l_attribute,
to_char(p_PRICE_LIST_rec.price_list_id),
qp_qualifier_group_no_s.nextval,
'N'
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(QP_QUALIFIERS_S.CURRVAL)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_PRICE_LIST_rec.price_list_id)
);
INSERT INTO QP_QUALIFIERS (
QUALIFIER_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LIST_HEADER_ID,
COMPARISON_OPERATOR_CODE,
QUALIFIER_CONTEXT,
QUALIFIER_ATTRIBUTE,
QUALIFIER_ATTR_VALUE,
QUALIFIER_GROUPING_NO,
EXCLUDER_FLAG
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_HEADER_REF
)
VALUES (
QP_QUALIFIERS_S.nextval,
sysdate,
1,
sysdate,
1,
1,
p_PRICE_LIST_rec.secondary_price_list_id,
'=',
l_context,
l_attribute,
to_char(p_PRICE_LIST_rec.price_list_id),
qp_qualifier_group_no_s.nextval,
'N'
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(QP_QUALIFIERS_S.CURRVAL)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_PRICE_LIST_rec.secondary_price_list_id)
);
INSERT INTO OE_PRICE_LISTS_B
( ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMMENTS
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CURRENCY_CODE
, DESCRIPTION
, END_DATE_ACTIVE
, FREIGHT_TERMS_CODE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, NAME
, PRICE_LIST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, ROUNDING_FACTOR
, SECONDARY_PRICE_LIST_ID
, SHIP_METHOD_CODE
, START_DATE_ACTIVE
, TERMS_ID
, TP_ATTRIBUTE1
, TP_ATTRIBUTE2
, TP_ATTRIBUTE3
, TP_ATTRIBUTE4
, TP_ATTRIBUTE5
, TP_ATTRIBUTE6
, TP_ATTRIBUTE7
, TP_ATTRIBUTE8
, TP_ATTRIBUTE9
, TP_ATTRIBUTE10
, TP_ATTRIBUTE11
, TP_ATTRIBUTE12
, TP_ATTRIBUTE13
, TP_ATTRIBUTE14
, TP_ATTRIBUTE15
, TP_ATTRIBUTE_CATEGORY
)
VALUES
( p_PRICE_LIST_rec.attribute1
, p_PRICE_LIST_rec.attribute10
, p_PRICE_LIST_rec.attribute11
, p_PRICE_LIST_rec.attribute12
, p_PRICE_LIST_rec.attribute13
, p_PRICE_LIST_rec.attribute14
, p_PRICE_LIST_rec.attribute15
, p_PRICE_LIST_rec.attribute2
, p_PRICE_LIST_rec.attribute3
, p_PRICE_LIST_rec.attribute4
, p_PRICE_LIST_rec.attribute5
, p_PRICE_LIST_rec.attribute6
, p_PRICE_LIST_rec.attribute7
, p_PRICE_LIST_rec.attribute8
, p_PRICE_LIST_rec.attribute9
, p_PRICE_LIST_rec.comments
, p_PRICE_LIST_rec.context
, p_PRICE_LIST_rec.created_by
, p_PRICE_LIST_rec.creation_date
, p_PRICE_LIST_rec.currency_code
, p_PRICE_LIST_rec.description
, p_PRICE_LIST_rec.end_date_active
, p_PRICE_LIST_rec.freight_terms_code
, p_PRICE_LIST_rec.last_updated_by
, p_PRICE_LIST_rec.last_update_date
, p_PRICE_LIST_rec.last_update_login
, p_PRICE_LIST_rec.name
, p_PRICE_LIST_rec.price_list_id
, p_PRICE_LIST_rec.program_application_id
, p_PRICE_LIST_rec.program_id
, p_PRICE_LIST_rec.program_update_date
, p_PRICE_LIST_rec.request_id
, p_PRICE_LIST_rec.rounding_factor
, p_PRICE_LIST_rec.secondary_price_list_id
, p_PRICE_LIST_rec.ship_method_code
, p_PRICE_LIST_rec.start_date_active
, p_PRICE_LIST_rec.terms_id
, p_PRICE_LIST_rec.tp_attribute1
, p_PRICE_LIST_rec.tp_attribute2
, p_PRICE_LIST_rec.tp_attribute3
, p_PRICE_LIST_rec.tp_attribute4
, p_PRICE_LIST_rec.tp_attribute5
, p_PRICE_LIST_rec.tp_attribute6
, p_PRICE_LIST_rec.tp_attribute7
, p_PRICE_LIST_rec.tp_attribute8
, p_PRICE_LIST_rec.tp_attribute9
, p_PRICE_LIST_rec.tp_attribute10
, p_PRICE_LIST_rec.tp_attribute11
, p_PRICE_LIST_rec.tp_attribute12
, p_PRICE_LIST_rec.tp_attribute13
, p_PRICE_LIST_rec.tp_attribute14
, p_PRICE_LIST_rec.tp_attribute15
, p_PRICE_LIST_rec.tp_attribute_category
);
insert into OE_PRICE_LISTS_TL (
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PRICE_LIST_ID,
LANGUAGE,
SOURCE_LANG )
select p_PRICE_LIST_rec.creation_date ,
p_PRICE_LIST_rec.created_by,
p_PRICE_LIST_rec.last_update_login ,
p_PRICE_LIST_rec.name ,
p_PRICE_LIST_rec.last_update_date ,
p_PRICE_LIST_rec.last_updated_by ,
p_PRICE_LIST_rec.price_list_id ,
L.language_code ,
userenv('LANG')
from FND_LANGUAGES L
where L.installed_flag in ('I','B')
and not exists
( select NULL from oe_price_lists_tl T
where T.price_list_id = p_PRICE_LIST_rec.price_list_id
and T.language = L.language_code ) ;
oe_debug_pub.add('Exiting OE_Price_List_Util.Insert_Row');
, 'Insert_Row'
);
END Insert_Row;
PROCEDURE Delete_Row
( p_name IN VARCHAR2
, p_price_list_id IN NUMBER
)
IS
l_sec_price_list_id number;
QP_LIST_HEADERS_PKG.Delete_Row(p_price_list_id);
delete from qp_qualifiers
where qualifier_context = l_context
and qualifier_attribute = l_attribute
and qualifier_attr_value = p_price_list_id
and list_header_id = l_sec_price_list_id
and qualifier_rule_id is null;
, 'Delete_Row'
);
END Delete_Row;
SELECT ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMMENTS
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CURRENCY_CODE
, DESCRIPTION
, END_DATE_ACTIVE
, FREIGHT_TERMS_CODE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, NAME
, LIST_HEADER_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, ROUNDING_FACTOR
, SHIP_METHOD_CODE
, START_DATE_ACTIVE
, TERMS_ID
INTO l_PRICE_LIST_rec.attribute1
, l_PRICE_LIST_rec.attribute10
, l_PRICE_LIST_rec.attribute11
, l_PRICE_LIST_rec.attribute12
, l_PRICE_LIST_rec.attribute13
, l_PRICE_LIST_rec.attribute14
, l_PRICE_LIST_rec.attribute15
, l_PRICE_LIST_rec.attribute2
, l_PRICE_LIST_rec.attribute3
, l_PRICE_LIST_rec.attribute4
, l_PRICE_LIST_rec.attribute5
, l_PRICE_LIST_rec.attribute6
, l_PRICE_LIST_rec.attribute7
, l_PRICE_LIST_rec.attribute8
, l_PRICE_LIST_rec.attribute9
, l_PRICE_LIST_rec.comments
, l_PRICE_LIST_rec.context
, l_PRICE_LIST_rec.created_by
, l_PRICE_LIST_rec.creation_date
, l_PRICE_LIST_rec.currency_code
, l_PRICE_LIST_rec.description
, l_PRICE_LIST_rec.end_date_active
, l_PRICE_LIST_rec.freight_terms_code
, l_PRICE_LIST_rec.last_updated_by
, l_PRICE_LIST_rec.last_update_date
, l_PRICE_LIST_rec.last_update_login
, l_PRICE_LIST_rec.name
, l_PRICE_LIST_rec.price_list_id
, l_PRICE_LIST_rec.program_application_id
, l_PRICE_LIST_rec.program_id
, l_PRICE_LIST_rec.program_update_date
, l_PRICE_LIST_rec.request_id
, l_PRICE_LIST_rec.rounding_factor
, l_PRICE_LIST_rec.ship_method_code
, l_PRICE_LIST_rec.start_date_active
, l_PRICE_LIST_rec.terms_id
FROM QP_LIST_HEADERS_VL
WHERE LIST_HEADER_ID = p_price_list_id;
FND_MESSAGE.SET_NAME('OE','OE_LOCK_ROW_DELETED');