[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
If px_control_rec.qp_control_rec.TEMP_TABLE_INSERT_FLAG is null then
px_control_rec.qp_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';
my_debug('160 :Temp table insert flag'||px_control_rec.qp_control_rec.TEMP_TABLE_INSERT_FLAG, 1);
PROCEDURE Update_LINE_price(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_CHR_ID IN NUMBER,
p_cle_id IN NUMBER ,
p_lowest_level IN NUMBER ,
px_Control_Rec IN OUT NOCOPY OKC_CONTROL_REC_TYPE,
px_chr_list_price IN OUT NOCOPY NUMBER,
px_chr_net_price IN OUT NOCOPY NUMBER,
px_cle_amt IN OUT NOCOPY NUMBER,
x_CLE_PRICE_TBL OUT NOCOPY CLE_PRICE_TBL_TYPE,
--???? x_cle_list_price OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_return_status varchar2(1) :=OKC_API.G_RET_STS_SUCCESS;
l_api_name constant VARCHAR2(30) := 'Update_line_PRICE';
okc_debug.Set_Indentation('Update_line_PRICE');
my_debug('1000 : Entering Update_line_PRICE', 2);
select level,cle_id,id,object_version_number,price_level_ind,price_basis_yn
bulk collect into l_lvl_tbl,l_cle_id_tbl,l_id_tbl,l_obj_tbl,l_p_tbl,l_bpi_tbl
from OKC_K_LINES_B
--where config_item_type is null
connect by (prior id = cle_id and config_item_type is null)
start with id=p_cle_id;
my_debug('1050 : select rowcount:'||SQL%ROWCOUNT, 1);
my_debug('1240 : number of lines to be updated:'||l_cle_tbl.count);
select cle_id,id,object_version_number,nvl(price_negotiated,0),nvl(line_list_price,0)
bulk collect into l_cle_id_tbl,l_id_tbl,l_obj_tbl,l_amt_tbl,l_list_tbl
from OKC_K_LINES_B
--where dnz_chr_id=p_chr_id
connect by prior cle_id = id
start with id=p_cle_id;
my_debug('1250 : select rowcount for the parents:'||SQL%ROWCOUNT, 1);
Select object_version_number,estimated_amount,total_line_list_price
into l_obj,l_amt,l_list_price
From okc_k_headers_b
where id=p_chr_id;
my_debug('1300 : select rowcount for header should be 1: '||SQL%ROWCOUNT, 1);
my_debug('1350 : Before calling update contract header', 1);
okc_contract_pub.update_contract_header (
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
-- p_restricted_update => okc_api.g_true,
p_chrv_rec => l_chr_rec,
x_chrv_rec => x_chr_rec);
my_debug('1355 : after calling update contract header status :'||l_return_status, 1);
my_debug('1365 : before calling update contract line, count :'||l_cle_tbl.count, 1);
okc_contract_pub.update_contract_line (
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
-- p_restricted_update => okc_api.g_true,
p_clev_tbl => l_cle_tbl,
x_clev_tbl => x_cle_tbl);
my_debug('1366 : after calling update contract line status :'||l_return_status, 1);
select id,operand,arithmetic_operator,object_version_number,adjusted_amount
bulk collect into l_pat_id_tbl,l_operand_tbl,l_operator_tbl,l_obj_tbl,l_value_tbl
from OKC_PRICE_ADJUSTMENTS
where chr_id=p_chr_id and cle_id is null;
my_debug('1370 : header adjustments select rowcount'||SQL%ROWCOUNT, 1);
my_debug('1378 : Before calling update price adjustment '||l_return_status, 1);
OKC_PRICE_ADJUSTMENT_PUB.update_price_adjustment(
p_api_version => p_api_version,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_patv_tbl => l_patv_tbl,
x_patv_tbl => lx_patv_tbl );
my_debug('1379 : after calling update price adjustment '||l_return_status, 1);
my_debug('1390:--------------Priced line tbl returned from update line price---------------');
my_debug('1600 : Exiting Update_line_PRICE', 2);
my_debug('1700 : Exiting Update_line_PRICE', 4);
my_debug('1800 : Exiting Update_line_PRICE', 4);
my_debug('1900 : Exiting Update_line_PRICE', 4);
END Update_line_price;
SELECT object1_id1, number_of_items qty
FROM okc_k_items
WHERE cle_id = b_cle_id
AND dnz_chr_id = p_chr_id
AND jtot_object1_code = 'OKX_COVLINE';
SELECT number_of_items qty
FROM okc_k_items
WHERE cle_id = b_cle_id
AND dnz_chr_id = p_chr_id;
PROCEDURE Update_CONTRACT_price(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_CHR_ID IN NUMBER,
px_Control_Rec IN OUT NOCOPY OKC_CONTROL_REC_TYPE,
x_CLE_PRICE_TBL OUT NOCOPY CLE_PRICE_TBL_TYPE,
x_chr_net_price OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_return_status varchar2(1) :=OKC_API.G_RET_STS_SUCCESS;
l_api_name constant VARCHAR2(30) := 'Update_Contract_PRICE';
okc_debug.Set_Indentation('Update_CONTRACT_price');
my_debug('2000 : Entering Update_CONTRACT_price', 2);
select level,cle_id,id,object_version_number,price_level_ind,price_basis_yn,config_top_model_line_id,
line_number, lse_id
bulk collect into l_lvl_tbl,l_cle_id_tbl,l_id_tbl,l_obj_tbl,l_p_tbl,l_bpi_tbl,l_top_model_tbl,
l_line_number_tbl, l_lse_id_tbl
from OKC_K_LINES_B
connect by prior id = cle_id
start with chr_id = p_chr_id;
my_debug('2100 : select rowcount'||SQL%ROWCOUNT, 1);
Select object_version_number,estimated_amount,total_line_list_price
into l_obj,l_amt,l_list_price
From okc_k_headers_b
where id=p_chr_id;
my_debug('2200 : header select rowcount should be 1: '||SQL%ROWCOUNT, 1);
my_debug('2400 : Before calling update contract line', 1);
okc_contract_pub.update_contract_line (
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
-- p_restricted_update => okc_api.g_true,
p_clev_tbl => l_cle_tbl,
x_clev_tbl => x_cle_tbl);
my_debug('2410 : after calling update contract line status :'||l_return_status, 1);
my_debug('2420 : Before calling update contract header', 1);
okc_contract_pub.update_contract_header (
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
-- p_restricted_update => okc_api.g_true,
p_chrv_rec => l_chr_rec,
x_chrv_rec => x_chr_rec);
my_debug('2430 : after calling update contract header status :'||l_return_status, 1);
my_debug('2450:--------------Priced line tbl returned from update contract price---------------');
my_debug('2600 : Exiting Update_Contract_price', 2);
my_debug('2690 : Exiting Update_Contract_price', 4);
my_debug('2700 : Exiting Update_Contract_price', 4);
my_debug('2800 : Exiting Update_Contract_price', 4);
my_debug('2900 : Exiting Update_Contract_price', 4);
END Update_Contract_price;
select rul.jtot_object1_code,rul.object1_id1,rul.object1_id2
,rul.jtot_object2_code,rul.object2_id1,rul.object2_id2
,rul.jtot_object3_code,rul.object3_id1,rul.object3_id2
,rul.rule_information_category
from okc_rules_b rul, okc_rule_groups_b rgp
where rul.rgp_id = rgp.id and
rgp.rgd_code in ('BILLING','SHIPPING','PAYMENT') and
rul.rule_information_category in ('BTO','STO','PTR','CAN') and
rgp.dnz_chr_id = p_chr_id and rgp.chr_id is not null;
/* select rul.jtot_object1_code,rul.object1_id1,rul.object1_id2,rul.jtot_object2_code,
rul.object2_id1,rul.object2_id2, rul.jtot_object3_code,rul.object3_id1,rul.object3_id2
,rul.rule_information_category
-- ,rul.rule_information1,rul.rule_information2,
--rul.rule_information3,rul.rule_information4,rul.rule_information5,rul.rule_information6,
--rul.rule_information7,rul.rule_information8,rul.rule_information9,rul.rule_information10,
--rul.rule_information11,rul.rule_information12,rul.rule_information13,rul.rule_information14,
--rul.rule_information15
from okc_rules_b rul, okc_rule_groups_b rgp
where rul.rgp_id = rgp.id and rgp.chr_id = p_chr_id;*/
select rul.jtot_object1_code,rul.object1_id1,rul.object1_id2
,rul.jtot_object2_code,rul.object2_id1,rul.object2_id2
,rul.jtot_object3_code,rul.object3_id1,rul.object3_id2
,rul.rule_information_category
from okc_rules_b rul, okc_rule_groups_b rgp
where rul.rgp_id = rgp.id and rgp.dnz_chr_id = p_chr_id and
rgp.cle_id=p_cle_id and
rgp.rgd_code in ('BILLING','SHIPPING','PAYMENT') and
rul.rule_information_category in ('BTO','STO','PTR','CAN');
/* select rul.jtot_object1_code,rul.object1_id1,rul.object1_id2,rul.jtot_object2_code,
rul.object2_id1,rul.object2_id2, rul.jtot_object3_code,rul.object3_id1,rul.object3_id2
,rul.rule_information_category
-- ,rul.rule_information1,rul.rule_information2,
--rul.rule_information3,rul.rule_information4,rul.rule_information5,rul.rule_information6,
--rul.rule_information7,rul.rule_information8,rul.rule_information9,rul.rule_information10,
--rul.rule_information11,rul.rule_information12,rul.rule_information13,rul.rule_information14,
--rul.rule_information15
from okc_rules_b rul, okc_rule_groups_b rgp
where rul.rgp_id = rgp.id and rgp.dnz_chr_id = p_chr_id and rgp.cle_id=p_cle_id;*/
select jtot_object1_code, object1_id1,object1_id2,rle_code
from okc_k_party_roles_b
where dnz_chr_id = p_chr_id and chr_id is not null;
select jtot_object1_code,object1_id1,object1_id2,rle_code
from okc_k_party_roles_b
where dnz_chr_id = p_chr_id and cle_id=p_cle_id;
select pricing_context,
pricing_attribute1, pricing_attribute2, pricing_attribute3, pricing_attribute4,
pricing_attribute5, pricing_attribute6, pricing_attribute7, pricing_attribute8,
pricing_attribute9, pricing_attribute10, pricing_attribute11, pricing_attribute12,
pricing_attribute13, pricing_attribute14, pricing_attribute15, pricing_attribute16,
pricing_attribute17, pricing_attribute18, pricing_attribute19, pricing_attribute20,
pricing_attribute21, pricing_attribute22, pricing_attribute23, pricing_attribute24,
pricing_attribute25, pricing_attribute26, pricing_attribute27, pricing_attribute28,
pricing_attribute29, pricing_attribute30, pricing_attribute31, pricing_attribute32,
pricing_attribute33, pricing_attribute34, pricing_attribute35, pricing_attribute36,
pricing_attribute37, pricing_attribute38, pricing_attribute39, pricing_attribute40,
pricing_attribute41, pricing_attribute42, pricing_attribute43, pricing_attribute44,
pricing_attribute45, pricing_attribute46, pricing_attribute47, pricing_attribute48,
pricing_attribute49, pricing_attribute50, pricing_attribute51, pricing_attribute52,
pricing_attribute53, pricing_attribute54, pricing_attribute55, pricing_attribute56,
pricing_attribute57, pricing_attribute58, pricing_attribute59, pricing_attribute60,
pricing_attribute61, pricing_attribute62, pricing_attribute63, pricing_attribute64,
pricing_attribute65, pricing_attribute66, pricing_attribute67, pricing_attribute68,
pricing_attribute69, pricing_attribute70, pricing_attribute71, pricing_attribute72,
pricing_attribute73, pricing_attribute74, pricing_attribute75, pricing_attribute76,
pricing_attribute77, pricing_attribute78, pricing_attribute79, pricing_attribute80,
pricing_attribute81, pricing_attribute82, pricing_attribute83, pricing_attribute84,
pricing_attribute85, pricing_attribute86, pricing_attribute87, pricing_attribute88,
pricing_attribute89, pricing_attribute90, pricing_attribute91, pricing_attribute92,
pricing_attribute93, pricing_attribute94, pricing_attribute95, pricing_attribute96,
pricing_attribute97, pricing_attribute98, pricing_attribute99, pricing_attribute100
,Qualifier_context,
qualifier_attribute1, qualifier_attribute2, qualifier_attribute3, qualifier_attribute4,
qualifier_attribute5, qualifier_attribute6, qualifier_attribute7, qualifier_attribute8,
qualifier_attribute9, qualifier_attribute10, qualifier_attribute11, qualifier_attribute12,
qualifier_attribute13, qualifier_attribute14, qualifier_attribute15, qualifier_attribute16,
qualifier_attribute17, qualifier_attribute18, qualifier_attribute19, qualifier_attribute20,
qualifier_attribute21, qualifier_attribute22, qualifier_attribute23, qualifier_attribute24,
qualifier_attribute25, qualifier_attribute26, qualifier_attribute27, qualifier_attribute28,
qualifier_attribute29, qualifier_attribute30, qualifier_attribute31, qualifier_attribute32,
qualifier_attribute33, qualifier_attribute34, qualifier_attribute35, qualifier_attribute36,
qualifier_attribute37, qualifier_attribute38, qualifier_attribute39, qualifier_attribute40,
qualifier_attribute41, qualifier_attribute42, qualifier_attribute43, qualifier_attribute44,
qualifier_attribute45, qualifier_attribute46, qualifier_attribute47, qualifier_attribute48,
qualifier_attribute49, qualifier_attribute50, qualifier_attribute51, qualifier_attribute52,
qualifier_attribute53, qualifier_attribute54, qualifier_attribute55, qualifier_attribute56,
qualifier_attribute57, qualifier_attribute58, qualifier_attribute59, qualifier_attribute60,
qualifier_attribute61, qualifier_attribute62, qualifier_attribute63, qualifier_attribute64,
qualifier_attribute65, qualifier_attribute66, qualifier_attribute67, qualifier_attribute68,
qualifier_attribute69, qualifier_attribute70, qualifier_attribute71, qualifier_attribute72,
qualifier_attribute73, qualifier_attribute74, qualifier_attribute75, qualifier_attribute76,
qualifier_attribute77, qualifier_attribute78, qualifier_attribute79, qualifier_attribute80,
qualifier_attribute81, qualifier_attribute82, qualifier_attribute83, qualifier_attribute84,
qualifier_attribute85, qualifier_attribute86, qualifier_attribute87, qualifier_attribute88,
qualifier_attribute89, qualifier_attribute90, qualifier_attribute91, qualifier_attribute92,
qualifier_attribute93, qualifier_attribute94, qualifier_attribute95, qualifier_attribute96,
qualifier_attribute97, qualifier_attribute98, qualifier_attribute99, qualifier_attribute100
from okc_price_att_values_v
where chr_id = p_chr_id and cle_id is null;
select pricing_context,
pricing_attribute1, pricing_attribute2, pricing_attribute3, pricing_attribute4,
pricing_attribute5, pricing_attribute6, pricing_attribute7, pricing_attribute8,
pricing_attribute9, pricing_attribute10, pricing_attribute11, pricing_attribute12,
pricing_attribute13, pricing_attribute14, pricing_attribute15, pricing_attribute16,
pricing_attribute17, pricing_attribute18, pricing_attribute19, pricing_attribute20,
pricing_attribute21, pricing_attribute22, pricing_attribute23, pricing_attribute24,
pricing_attribute25, pricing_attribute26, pricing_attribute27, pricing_attribute28,
pricing_attribute29, pricing_attribute30, pricing_attribute31, pricing_attribute32,
pricing_attribute33, pricing_attribute34, pricing_attribute35, pricing_attribute36,
pricing_attribute37, pricing_attribute38, pricing_attribute39, pricing_attribute40,
pricing_attribute41, pricing_attribute42, pricing_attribute43, pricing_attribute44,
pricing_attribute45, pricing_attribute46, pricing_attribute47, pricing_attribute48,
pricing_attribute49, pricing_attribute50, pricing_attribute51, pricing_attribute52,
pricing_attribute53, pricing_attribute54, pricing_attribute55, pricing_attribute56,
pricing_attribute57, pricing_attribute58, pricing_attribute59, pricing_attribute60,
pricing_attribute61, pricing_attribute62, pricing_attribute63, pricing_attribute64,
pricing_attribute65, pricing_attribute66, pricing_attribute67, pricing_attribute68,
pricing_attribute69, pricing_attribute70, pricing_attribute71, pricing_attribute72,
pricing_attribute73, pricing_attribute74, pricing_attribute75, pricing_attribute76,
pricing_attribute77, pricing_attribute78, pricing_attribute79, pricing_attribute80,
pricing_attribute81, pricing_attribute82, pricing_attribute83, pricing_attribute84,
pricing_attribute85, pricing_attribute86, pricing_attribute87, pricing_attribute88,
pricing_attribute89, pricing_attribute90, pricing_attribute91, pricing_attribute92,
pricing_attribute93, pricing_attribute94, pricing_attribute95, pricing_attribute96,
pricing_attribute97, pricing_attribute98, pricing_attribute99, pricing_attribute100
,Qualifier_context,
qualifier_attribute1, qualifier_attribute2, qualifier_attribute3, qualifier_attribute4,
qualifier_attribute5, qualifier_attribute6, qualifier_attribute7, qualifier_attribute8,
qualifier_attribute9, qualifier_attribute10, qualifier_attribute11, qualifier_attribute12,
qualifier_attribute13, qualifier_attribute14, qualifier_attribute15, qualifier_attribute16,
qualifier_attribute17, qualifier_attribute18, qualifier_attribute19, qualifier_attribute20,
qualifier_attribute21, qualifier_attribute22, qualifier_attribute23, qualifier_attribute24,
qualifier_attribute25, qualifier_attribute26, qualifier_attribute27, qualifier_attribute28,
qualifier_attribute29, qualifier_attribute30, qualifier_attribute31, qualifier_attribute32,
qualifier_attribute33, qualifier_attribute34, qualifier_attribute35, qualifier_attribute36,
qualifier_attribute37, qualifier_attribute38, qualifier_attribute39, qualifier_attribute40,
qualifier_attribute41, qualifier_attribute42, qualifier_attribute43, qualifier_attribute44,
qualifier_attribute45, qualifier_attribute46, qualifier_attribute47, qualifier_attribute48,
qualifier_attribute49, qualifier_attribute50, qualifier_attribute51, qualifier_attribute52,
qualifier_attribute53, qualifier_attribute54, qualifier_attribute55, qualifier_attribute56,
qualifier_attribute57, qualifier_attribute58, qualifier_attribute59, qualifier_attribute60,
qualifier_attribute61, qualifier_attribute62, qualifier_attribute63, qualifier_attribute64,
qualifier_attribute65, qualifier_attribute66, qualifier_attribute67, qualifier_attribute68,
qualifier_attribute69, qualifier_attribute70, qualifier_attribute71, qualifier_attribute72,
qualifier_attribute73, qualifier_attribute74, qualifier_attribute75, qualifier_attribute76,
qualifier_attribute77, qualifier_attribute78, qualifier_attribute79, qualifier_attribute80,
qualifier_attribute81, qualifier_attribute82, qualifier_attribute83, qualifier_attribute84,
qualifier_attribute85, qualifier_attribute86, qualifier_attribute87, qualifier_attribute88,
qualifier_attribute89, qualifier_attribute90, qualifier_attribute91, qualifier_attribute92,
qualifier_attribute93, qualifier_attribute94, qualifier_attribute95, qualifier_attribute96,
qualifier_attribute97, qualifier_attribute98, qualifier_attribute99, qualifier_attribute100
from okc_price_att_values_v
where cle_id = p_cle_id;
select INV_ORGANIZATION_ID,price_list_id,authoring_org_id,nvl(pricing_date,sysdate)
into OKC_PRICE_PUB.G_CONTRACT_INFO.INV_ORG_ID , l_price_list_id,g_authoring_org_id,OKC_PRICE_PUB.G_CONTRACT_INFO.PRICING_DATE
from okc_k_headers_b
where id = p_chr_id;
select chr_id_referred
into OKC_PRICE_PUB.G_CONTRACT_INFO.governing_contract_id
from okc_governances
where dnz_chr_id = p_chr_id
and cle_id is null
and rownum=1;
okc_price_pub.g_rul_tbl.DELETE;
okc_price_pub.g_prle_tbl.DELETE;
SELECT object1_id1, object1_id2, jtot_object1_code, uom_code,number_of_items
FROM okc_k_items
where cle_id = p_cle_id and dnz_chr_id=p_chr_id;
SELECT number_of_items
FROM okc_k_items
where Cle_id = p_cle_id and dnz_chr_id=p_chr_id;
Select price_list_id from okc_k_lines_b
where id = p_cle_id;
select id,currency_code,price_level_ind
,item_to_price_yn,price_basis_yn,nvl(service_item_yn,'N'),price_list_id,price_negotiated,line_list_price,price_unit,config_top_model_line_id,nvl(pricing_date,g_hdr_pricing_date),end_date,cle_id
BULK COLLECT INTO l_id,l_cur, l_p
,l_pi, l_bpi,l_srvc,l_pricelist_id,l_price_tbl,l_list_price_tbl,l_unit_price_tbl, l_top_model_tbl,l_prc_date_tbl,l_end_date_tbl,l_cle_id_tbl
from okc_k_lines_b
connect by prior cle_id = id
start with id=p_cle_id;
my_debug('10005 : select rowcount for lines fetched'||SQL%ROWCOUNT, 1);
Update okc_k_items
set number_of_items=l_item_row1.number_of_items
where cle_id = x_line_tbl(i).id
and dnz_chr_id = p_chr_id;
my_debug ('Updated with Service qty'||l_item_row1.number_of_items);
x_line_tbl(i).updated_price := nvl(l_price_tbl(i),0)/x_line_tbl(i).qty;
Select inventory_item_id
from okx_customer_products_v
where id1=p_id
and status='A'
and organization_id=g_authoring_org_id;
select name into l_lse_rec.source_value
FROM okc_k_lines_v
where id=p_line_tbl(i).id;
select object1_id1 into inv_id1
from okc_k_items
where cle_id = p_line_tbl(1).id1;
select chr_id_referred
into OKC_PRICE_PUB.G_CONTRACT_INFO.governing_contract_id
from okc_governances
where dnz_chr_id = p_chr_id
and cle_id =p_line_tbl(i).id
and rownum=1;
select 'OKX_MTL_SYSTEM_ITEM' into l_lse_rec.current_source
from JTF_OBJECT_USAGES
where object_code= p_line_tbl(i).object_code
and OBJECT_USER_CODE = 'OKX_MTL_SYSTEM_ITEM';
select price_list_id into pricelist from okc_k_lines_b
where id = p_line_tbl(i).id1;
select INV_ORGANIZATION_ID
into OKC_PRICE_PUB.G_CONTRACT_INFO.INV_ORG_ID
from okc_k_headers_b
where id = p_chr_id;
okc_price_pub.g_rul_tbl.DELETE;
okc_price_pub.g_prle_tbl.DELETE;
okc_price_pub.g_lse_tbl.DELETE;
l_line_tbl.delete(l_bpi_ind);
my_debug('12035 : deleted BPI entry-'||l_bpi_ind, 1);
px_req_line_tbl(l_line_index).UPDATED_ADJUSTED_UNIT_PRICE:= l_line_tbl(1).updated_price;
l_line_tbl.delete(p_pi_ind);
px_req_line_tbl(l_line_index).UPDATED_ADJUSTED_UNIT_PRICE:= l_line_tbl(i).updated_price;
l_line_tbl.delete(p_pi_ind);
l_line_tbl.delete(l_line_tbl.first);
select id
bulk collect into l_id_tbl
from okc_price_adj_assocs
where pat_id = p_id_tbl(k) or pat_id_from = p_id_tbl(k);
my_debug('13310 : select rowcount'||SQL%ROWCOUNT, 1);
OKC_PRICE_ADJUSTMENT_PUB.delete_price_adj_assoc(
p_api_version => p_api_version,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pacv_tbl => l_pacv_tbl);
l_pacv_tbl.delete;
select id
bulk collect into l_id_tbl
from okc_price_adj_attribs
where pat_id = p_id_tbl(k);
my_debug('13610 : select rowcount'||SQL%ROWCOUNT, 1);
OKC_PRICE_ADJUSTMENT_PUB.delete_price_adj_attrib(
p_api_version => p_api_version,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_paav_tbl => l_paav_tbl);
l_paav_tbl.delete;
l_paav_tbl.delete;
px_patv_rec.UPDATE_ALLOWED := p_Req_LINE_DETAIL_rec.override_flag;
px_patv_rec.UPDATED_FLAG := p_Req_LINE_DETAIL_rec.updated_flag;
my_debug('line index '||p_req_line_rec.line_index||'No updates to id'||p_req_line_rec.line_id);
my_debug('line index '||p_req_line_rec.line_index||'No updates to id'||p_req_line_rec.line_id);
select cle_id,id,list_line_no,list_header_id,object_version_number
bulk collect into l_id_tmp_tbl,l_pat_tmp_tbl,l_line_tmp_tbl,l_list_tmp_tbl,l_obj_tmp_tbl
from OKC_PRICE_ADJUSTMENTS
where cle_id=p_cle_price_tbl(i).id and chr_id=p_chr_id;
my_debug('14710 :line adjustments select rowcount'||SQL%ROWCOUNT, 1);
select chr_id,id,list_line_no,list_header_id,object_version_number
bulk collect into l_id_tmp_tbl,l_pat_tmp_tbl,l_line_tmp_tbl,l_list_tmp_tbl,l_obj_tmp_tbl
from OKC_PRICE_ADJUSTMENTS
where chr_id=p_chr_id and cle_id is null;
my_debug('14750 : header adjustments select rowcount'||SQL%ROWCOUNT, 1);
select cle_id,id,list_line_no,list_header_id,object_version_number
bulk collect into l_id_tmp_tbl,l_pat_tmp_tbl,l_line_tmp_tbl,l_list_tmp_tbl,l_obj_tmp_tbl
from OKC_PRICE_ADJUSTMENTS
where cle_id=p_control_rec.p_top_model_id and chr_id=p_chr_id;
my_debug('14745 : top dummy line adjustments select rowcount'||SQL%ROWCOUNT, 1);
QP_PREQ_GRP.G_STATUS_UPDATED
,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION
,QP_PREQ_GRP.G_STATUS_NEW
,QP_PREQ_GRP.G_STATUS_UNCHANGED
,QP_PREQ_GRP.G_STATUS_SYSTEM_GENERATED )
and (p_req_line_tbl(p_req_line_detail_Tbl(i).line_index).line_Type_code ='LINE'
and nvl(p_req_line_tbl( p_req_line_detail_Tbl(i).line_index).processed_code,'0')
<> QP_PREQ_GRP.G_BY_ENGINE
--commented out to support negative pricing
-- and ( p_req_line_tbl(p_req_line_detail_Tbl(i).line_index).unit_price >= 0
-- and p_req_line_tbl(p_req_line_detail_Tbl(i).line_index).Adjusted_unit_price >= 0))
OR
(p_req_line_tbl(p_req_line_detail_Tbl(i).line_index).line_Type_code ='ORDER'
and p_control_rec.p_level='H'))
--????? If we plan a UI for Price breaks, this condition might go
-- tope pbh changes
and ( p_req_line_detail_tbl(i).applied_flag= 'Y'
OR nvl(lx_process_child_yn,'N')='Y' )
-- do not process adjustment if request line created just for pricing service only
--and is_serviced_req_line(p_req_line_tbl( p_req_line_detail_Tbl(i).line_index),p_req_related_line_tbl) = 'N'
and nvl(lx_serviced_rec_yn,'N') = 'N'
then
if p_req_line_detail_Tbl(i).created_from_list_type_code <> 'PRL' and --#2
p_req_line_detail_Tbl(i).created_from_list_type_code <> 'AGR' and
p_req_line_detail_Tbl(i).list_line_type_code <> 'PLL' and
--Bug 2452258 Do not display freight charges as we donot handle them
p_req_line_detail_Tbl(i).list_line_type_code <> 'FREIGHT_CHARGE'
then
k:= l_id_tbl.first;
my_debug('14774 : Update the record as found the adjustment at k '||k, 1);
l_pat_id_tbl.delete(k);
my_debug('14776 : Before calling update price adjustment '||x_return_status, 1);
OKC_PRICE_ADJUSTMENT_PUB.update_price_adjustment(
p_api_version => p_api_version,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_patv_rec => l_patv_rec,
x_patv_rec => lx_patv_rec );
my_debug('14778 : after calling update price adjustment '||x_return_status, 1);
my_debug('14779 : Insert the record as not found the adjustment', 1);
select id from okc_price_adj_assocs_v
where pat_id_from = p_pat_id_from
and pat_id =p_pat_id
and cle_id = p_cle_id;
my_debug('14784 : delete the left over old price adjustments '||l_pat_id_tbl.count, 1);
my_debug('14785 : id of the deleted adjustment '||l_patv_tbl(i).id, 1);
my_debug('14786 :before calling delete price adjustments'||x_return_status, 1);
OKC_PRICE_ADJUSTMENT_PUB.delete_price_adjustment(
p_api_version => p_api_version,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_patv_tbl => l_patv_tbl);
my_debug('14787 :after calling delete price adjustments'||x_return_status, 1);
Select name
from okx_list_headers_v a,okx_qp_list_lines_v b
where
(b.id1 = to_number(substr(p_req_line_tbl(i).status_text,1,
instr(p_req_line_tbl(i).status_text,',')-1))
OR
b.id1 = to_number(substr(p_req_line_tbl(i).status_text,
instr(p_req_line_tbl(i).status_text,',')+1)))
and a.id1=b.list_header_id;
Select nvl(service_item_yn,'N') service_item_yn into result
From okc_k_lines_b
where id = (Select cle_id from okc_k_lines_b where id =p_id);
Select name
into l_prc1
from okx_list_headers_v
where id1 = l_line_rec.pricelist_id;
my_debug('15050 : select rowcount for pricelist name'||SQL%ROWCOUNT, 1);
( QP_PREQ_GRP.G_STATUS_UPDATED,
QP_PREQ_GRP.G_STATUS_NEW,
QP_PREQ_GRP.G_STATUS_SYSTEM_GENERATED,
QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
QP_PREQ_GRP.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_BACK_CALCULATION_STS) and
--???? change later nvl(p_req_line_tbl(i).processed_code,'0') = QP_PREQ_PUB.G_BACK_CALCULATION_STS then
-- right now this way because of a bug in QP. Later on QP might return this error in retcode on request line
-- also QP_PREQ_PUB.G_BACK_CALCULATION_STS might hold 'BACK CALCULATION ERROR' instaed of
-- 'BACK_CALCULATION_ERROR'. Right now processed code returns 'BACK CALCULATION ERROR'
-- while QP_PREQ_PUB.G_BACK_CALCULATION_STS has value 'BACK_CALCULATION_ERROR'
nvl(p_req_line_tbl(i).processed_code,'0') = 'BACK CALCULATION ERROR' then
IF l_line_rec.line_num is null then
-- get the concatenated line number to show in error message
l_line_rec.line_num := OKC_CONTRACT_PUB.Get_concat_line_no(
p_cle_id => l_line_rec.id ,
x_return_status => l_return_status
);
( QP_PREQ_GRP.G_STATUS_UPDATED,
QP_PREQ_GRP.G_STATUS_NEW,
QP_PREQ_GRP.G_STATUS_SYSTEM_GENERATED,
QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
QP_PREQ_GRP.G_STATUS_UNCHANGED) and
nvl(p_req_line_tbl(i).processed_code,'0') <> QP_PREQ_PUB.G_BY_ENGINE--????check this one
and p_req_line_tbl(i).price_flag IN ('Y','P')
then
--??? update line's unit price to null in case of error
--dbms_output.put_line('a2.5');
select *
from okc_price_adjustments a
where
cle_id = p_id and chr_id=pchr_id
and applied_flag='Y'
--do not process child detail lines -- tope pbh changes
and id not in (Select pat_id
from okc_price_adj_assocs_v
where cle_id =p_id
and pat_id_from is not null
and pat_id = a.id);
select *
from okc_price_adjustments
where
chr_id = p_id and cle_id is null --???ask sripriya do we just send manual.(what about overrides)
and applied_flag='Y';
select b.*
from okc_price_adj_assocs_v a, okc_price_adjustments b
where a.pat_id_from =p_pat_id
and a.pat_id = b.id
and a.cle_id =p_cle_id
and a.cle_id = b.cle_id;
select flex_title,pricing_context,pricing_attribute,
pricing_attr_value_from, pricing_attr_value_to,comparison_operator
From okc_price_adj_attribs
where pat_id=p_id;
l_req_line_detail_tbl(j).updated_flag := l_row.updated_flag;
l_req_line_detail_tbl(j).override_flag := l_row.UPDATE_ALLOWED ;
---Update the qualifying child line with new operand
---Set updated flag for all child lines to 'Y'
---Remove in phase 3
/******************************
If l_row.updated_flag = 'Y' and l_row.operand is not null Then
For l_related_row in l_related_lines(l_row.id,l_row.cle_id) Loop
IF (l_debug = 'Y') THEN
my_debug(' adjusted_amount line ' || l_related_row.adjusted_amount);
Update okc_price_adjustments
set applied_flag = 'Y',
operand = l_row.operand,
updated_flag = 'Y'
where id = l_related_row.id;
Update okc_price_adjustments
set updated_flag = 'Y'
where id = l_related_row.id;
l_req_line_detail_tbl(j).updated_flag := l_related_row.updated_flag;
l_req_line_detail_tbl(j).override_flag := l_related_row.UPDATE_ALLOWED ;
select id
BULK COLLECT into l_priced_tbl
from okc_k_lines_b where dnz_chr_id = p_chr_id
and price_level_ind ='Y';
my_debug('16550 : select rowcount'||SQL%ROWCOUNT, 1);
px_cle_price_tbl.delete;
l_prc_Tbl.delete;
l_qual_Tbl.delete;
l_bpi_prc_Tbl.delete;
l_bpi_qual_Tbl.delete;
l_prc_Tbl.delete(l_index);
l_qual_Tbl.delete(l_index);
l_line_tbl.delete;
select id
BULK COLLECT INTO l_id_tbl
from okc_k_lines_b
connect by prior cle_id = id
start with id=l_okc_control_rec.p_top_model_id;
my_debug('16650 : select rowcount'||SQL%ROWCOUNT, 1);
SELECT object1_id1, object1_id2, jtot_object1_code
into l_id1,l_id2,l_jtot1_code
FROM okc_k_items
where cle_id = l_id_tbl(i) and dnz_chr_id=p_chr_id;
my_debug('16700 : select rowcount'||SQL%ROWCOUNT, 1);
select currency_code
into l_curr
from okc_k_headers_b
where id = p_chr_id;
my_debug('16750 : select rowcount'||SQL%ROWCOUNT, 1);
my_debug('16826:UPDATED_ADJUSTED_UNIT_PRICE: '||l_req_line_tbl(I).UPDATED_ADJUSTED_UNIT_PRICE);
my_debug('16864:Updated Flag: '||l_req_line_detail_tbl(I).UPDATED_FLAG);
my_debug('16942:UPDATED_ADJUSTED_UNIT_PRICE: '||px_req_line_tbl(I).UPDATED_ADJUSTED_UNIT_PRICE);
my_debug('17060:Updated Flag: '||px_req_line_detail_tbl(I).UPDATED_FLAG);
g_hdr_rul_tbl.delete;
g_hdr_prle_tbl.delete;
select id
bulk collect into l_id_tbl
from OKC_K_LINES_B
where price_level_ind='Y' and dnz_chr_id = p_chr_id;
my_debug('18010 : select rowcount'||SQL%ROWCOUNT, 1);