[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name FROM qp_currency_lists_tl
WHERE currency_header_id = l_currency_header_id AND language = userenv('lang') ;
SELECT custom_setup_id
FROM ams_custom_setups_vl
WHERE object_type = 'PRIC';
ELSIF p_operation = QP_GLOBALS.G_OPR_UPDATE THEN
OZF_PRICE_LIST_PVT.update_price_list(p_api_version_number => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_ozf_PRICE_LIST_rec => p_price_list_attr_rec
,x_object_version_number => l_object_version_number);
ELSIF p_operation = QP_GLOBALS.G_OPR_DELETE THEN
OZF_PRICE_LIST_PVT.delete_price_list( p_api_version_number => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_price_list_attribute_id => p_price_list_attr_rec.price_list_attribute_id
,p_object_version_number => p_price_list_attr_rec.object_version_number );
l_lines_delete VARCHAR2(1) := 'N';
SELECT status_code
FROM OZF_price_lists_v
WHERE list_header_id = p_list_header_id;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = 'OZF_PRICELIST_STATUS'
AND system_status_code = l_status_code
AND TRUNC(sysdate) BETWEEN NVL(start_date_active, TRUNC(SYSDATE)) and NVL(end_date_active, TRUNC(sysdate))
AND default_flag = 'Y'
AND enabled_flag = 'Y';
SELECT user_status_id,custom_setup_id,owner_id
FROM OZF_price_list_attributes
WHERE qp_list_header_id = l_id;
IF AMS_ACCESS_PVT.check_update_access(p_price_list_rec.list_header_id, 'PRIC',l_resource_id,'USER') NOT IN ('F','R') THEN
FND_MESSAGE.SET_NAME('OZF','OZF_EVO_NO_UPDATE_ACCESS');
ELSIF p_price_list_line_tbl(i).operation = QP_GLOBALS.G_OPR_UPDATE THEN
l_price_list_line_tbl(i).operation := p_price_list_line_tbl(i).operation ;
l_lines_delete := 'Y';
IF l_lines_delete = 'Y' THEN
l_price_list_rec := temp_price_list_rec;
l_price_list_line_tbl.delete;
l_pricing_attr_tbl.delete;
IF p_price_list_line_tbl.exists(i) AND p_price_list_line_tbl(i).operation = QP_GLOBALS.G_OPR_DELETE THEN
l_price_list_line_tbl(j).list_line_id := p_price_list_line_tbl(i).list_line_id;
ELSE -- operation = 'update'
-- get old user_status id
IF p_price_list_rec.operation = 'UPDATE'
AND ( p_price_list_rec.list_header_id IS NULL
or p_price_list_rec.list_header_id = FND_API.g_miss_num) THEN
NULL;
ams_access_PVT.update_object_owner(
p_api_version => l_api_version,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
p_validation_level => FND_API.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_type => 'PRIC',
p_object_id => p_price_list_rec.list_header_id,
p_resource_id => p_price_list_rec.owner_id,
p_old_resource_id => l_old_owner_id);
/*-- added by julou 08/16/2001 create or update note info in jtf notes tables.
IF p_price_list_line_tbl.count > 0 THEN
FOR i in p_price_list_line_tbl.first .. p_price_list_line_tbl.last LOOP
IF p_price_list_line_tbl(i).operation = QP_GLOBALS.G_OPR_UPDATE
OR p_price_list_line_tbl(i).operation = QP_GLOBALS.G_OPR_CREATE THEN
IF p_price_list_line_tbl(i).jtf_note_id IS NULL THEN
IF p_price_list_line_tbl(i).note IS NOT NULL THEN
JTF_NOTES_PUB.Create_note(
p_parent_note_id => FND_API.g_miss_num,
p_jtf_note_id => NULL,
p_api_version => l_api_version,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
p_validation_level => FND_API.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_org_id => NULL,
p_source_object_id => v_price_list_line_tbl(i).list_line_id,
p_source_object_code => 'OZF_PRIC_LINE',
p_notes => p_price_list_line_tbl(i).note,
p_notes_detail => NULL,
p_note_status => 'I',
p_entered_by => FND_GLOBAL.user_id,
p_entered_date => SYSDATE,
x_jtf_note_id => l_jtf_note_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.user_id,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.user_id,
p_last_update_login => FND_GLOBAL.login_id,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_context => NULL,
p_note_type => 'OZF_PRICELISTREPORT',
p_jtf_note_contexts_tab => JTF_NOTES_PUB.jtf_note_contexts_tab_dflt);
JTF_NOTES_PUB.Update_note(
p_api_version => l_api_version,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
p_validation_level => FND_API.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_jtf_note_id => p_price_list_line_tbl(i).jtf_note_id,
p_entered_by => FND_GLOBAL.user_id,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => FND_GLOBAL.login_id,
p_notes => p_price_list_line_tbl(i).note,
p_notes_detail => NULL,
p_append_flag => FND_API.g_miss_char,
p_note_status => 'I',
p_note_type => 'OZF_PRICELISTREPORT',
p_jtf_note_contexts_tab => JTF_NOTES_PUB.jtf_note_contexts_tab_dflt);
SELECT aam.market_segment_name
FROM ams_act_mkt_segments_vl aam
WHERE aam.act_market_segment_used_by_id = p_price_list_id
AND aam.arc_act_market_segment_used_by ='PRIC'
AND aam.segment_type = 'MARKET_SEGMENT';
SELECT aam.market_segment_name
FROM ams_act_mkt_segments_vl aam
WHERE aam.act_market_segment_used_by_id = p_price_list_id
AND aam.arc_act_market_segment_used_by = 'PRIC'
AND aam.segment_type = 'CELL';
SELECT description
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_prod_value;
SELECT eni.category_desc Category_Name
FROM eni_prod_den_hrchy_parents_v eni
WHERE eni.category_id = p_catg_id;
SELECT mtl.concatenated_segments Category_Name
FROM mtl_categories_kfv mtl
WHERE mtl.category_id = p_catg_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS(SELECT 1
FROM qp_pricing_attributes
WHERE product_attribute = 'PRICING_ATTRIBUTE1'
AND product_attr_value = p_inv_item_id
AND list_header_id = p_list_header_id);
SELECT DISTINCT qpa.list_header_id, qpa.list_line_id
FROM qp_pricing_attributes qpa, qp_list_lines qll, qp_list_headers_b qlh
WHERE qll.list_line_id = qpa.list_line_id
AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
AND qpa.product_attr_value = TO_CHAR(p_org_inv_item_id)
AND qlh.list_header_id = qpa.list_header_id
AND qlh.list_type_code = 'PRL'
AND qlh.source_system_code = l_source_system_code;
SELECT *
FROM qp_list_headers_vl
WHERE list_header_id = l_list_header_id;
SELECT *
FROM qp_list_lines
WHERE list_line_id = l_list_line_id;
SELECT *
FROM qp_pricing_attributes
WHERE list_line_id = l_list_line_id;
SELECT qp_list_lines_s.NEXTVAL
FROM DUAL;
SELECT qp_pricing_attributes_s.NEXTVAL
FROM DUAL;
INSERT INTO qp_list_lines(list_line_id
,accrual_qty
,accrual_uom_code
,arithmetic_operator
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,automatic_flag
,base_qty
,base_uom_code
,comments
,context
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,effective_period_uom
,end_date_active
,estim_accrual_rate
,inventory_item_id
,list_header_id
,list_line_no
,list_line_type_code
,list_price
,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
,pricing_phase_id
,pricing_group_sequence
,incompatibility_grp_code
,product_precedence)
VALUES(l_list_line_id
,l_list_line_detail.accrual_qty
,l_list_line_detail.accrual_uom_code
,l_list_line_detail.arithmetic_operator
,l_list_line_detail.attribute1
,l_list_line_detail.attribute2
,l_list_line_detail.attribute3
,l_list_line_detail.attribute4
,l_list_line_detail.attribute5
,l_list_line_detail.attribute6
,l_list_line_detail.attribute7
,l_list_line_detail.attribute8
,l_list_line_detail.attribute9
,l_list_line_detail.attribute10
,l_list_line_detail.attribute11
,l_list_line_detail.attribute12
,l_list_line_detail.attribute13
,l_list_line_detail.attribute14
,l_list_line_detail.attribute15
,l_list_line_detail.automatic_flag
,l_list_line_detail.base_qty
,l_list_line_detail.base_uom_code
,l_list_line_detail.comments
,l_list_line_detail.context
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.conc_login_id
,l_list_line_detail.effective_period_uom
,l_list_line_detail.end_date_active
,l_list_line_detail.estim_accrual_rate
,l_list_line_detail.inventory_item_id
,l_list_header_line_id.list_header_id
,l_list_line_id
,l_list_line_detail.list_line_type_code
,l_list_line_detail.list_price
,l_list_line_detail.modifier_level_code
,l_list_line_detail.number_effective_periods
,l_list_line_detail.operand
,l_list_line_detail.organization_id
,l_list_line_detail.override_flag
,l_list_line_detail.percent_price
,l_list_line_detail.price_break_type_code
,l_list_line_detail.price_by_formula_id
,l_list_line_detail.primary_uom_flag
,l_list_line_detail.print_on_invoice_flag
,l_list_line_detail.program_application_id
,l_list_line_detail.program_id
,l_list_line_detail.program_update_date
,l_list_line_detail.rebate_transaction_type_code
,l_list_line_detail.related_item_id
,l_list_line_detail.relationship_type_id
,l_list_line_detail.reprice_flag
,l_list_line_detail.request_id
,l_list_line_detail.revision
,l_list_line_detail.revision_date
,l_list_line_detail.revision_reason_code
,l_list_line_detail.start_date_active
,l_list_line_detail.substitution_attribute
,l_list_line_detail.substitution_context
,l_list_line_detail.substitution_value
,l_list_line_detail.qualification_ind
,l_list_line_detail.pricing_phase_id
,l_list_line_detail.pricing_group_sequence
,l_list_line_detail.incompatibility_grp_code
,l_list_line_detail.product_precedence);
INSERT INTO qp_pricing_attributes(pricing_attribute_id
,accumulate_flag
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_grouping_no
,context
,excluder_flag
,pricing_attribute
,pricing_attribute_context
,pricing_attr_value_from
,pricing_attr_value_to
,product_attribute
,product_attribute_context
,product_attr_value
,product_uom_code
,program_application_id
,program_id
,program_update_date
,request_id
,pricing_attr_value_from_number
,pricing_attr_value_to_number
,qualification_ind
,comparison_operator_code
,product_attribute_datatype
,pricing_attribute_datatype
,pricing_phase_id
,list_header_id
,list_line_id
-- ,list_line_no
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
VALUES(l_pricing_attr_id
,l_pricing_detail.accumulate_flag
,l_pricing_detail.attribute1
,l_pricing_detail.attribute2
,l_pricing_detail.attribute3
,l_pricing_detail.attribute4
,l_pricing_detail.attribute5
,l_pricing_detail.attribute6
,l_pricing_detail.attribute7
,l_pricing_detail.attribute8
,l_pricing_detail.attribute9
,l_pricing_detail.attribute10
,l_pricing_detail.attribute11
,l_pricing_detail.attribute12
,l_pricing_detail.attribute13
,l_pricing_detail.attribute14
,l_pricing_detail.attribute15
,l_pricing_detail.attribute_grouping_no
,l_pricing_detail.context
,l_pricing_detail.excluder_flag
,l_pricing_detail.pricing_attribute
,l_pricing_detail.pricing_attribute_context
,l_pricing_detail.pricing_attr_value_from
,l_pricing_detail.pricing_attr_value_to
,l_pricing_detail.product_attribute
,l_pricing_detail.product_attribute_context
,p_new_inv_item_id(i)
,l_pricing_detail.product_uom_code
,l_pricing_detail.program_application_id
,l_pricing_detail.program_id
,l_pricing_detail.program_update_date
,l_pricing_detail.request_id
,l_pricing_detail.pricing_attr_value_from_number
,l_pricing_detail.pricing_attr_value_to_number
,l_pricing_detail.qualification_ind
,l_pricing_detail.comparison_operator_code
,l_pricing_detail.product_attribute_datatype
,l_pricing_detail.pricing_attribute_datatype
,l_pricing_detail.pricing_phase_id
,l_list_header_line_id.list_header_id
,l_list_line_id
-- ,l_list_line_id
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.conc_login_id);
l_price_list_rec.program_update_date := l_list_header_detail.program_update_date;
l_price_list_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
l_price_list_line_tbl(l_index).program_update_date := l_list_line_detail.program_update_date;
l_pricing_attr_tbl(l_index).program_update_date := l_pricing_detail.program_update_date;