[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
FROM okc_k_party_roles_b
WHERE dnz_chr_id =p_chr_id
AND rle_code in ('CUSTOMER','SUBSCRIBER')
AND cle_id is null;
select
id
,chr_id
,cc_no
,cc_expiry_date
,cc_bank_acct_id
,cc_auth_code
,object_version_number
from oks_k_headers_b
where chr_id=p_chr_id;
l_khrv_tbl.delete;
l_khrv_tbl(1).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
l_khrv_tbl(1).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
l_khrv_tbl(1).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
OKS_CONTRACT_HDR_PUB.update_header (
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_tbl => l_khrv_tbl,
x_khrv_tbl => x_khrv_tbl,
p_validate_yn => 'N');
select
id
,cle_id
,dnz_chr_id
,cc_no
,cc_expiry_date
,cc_bank_acct_id
,cc_auth_code
,object_version_number
from oks_k_lines_b
where dnz_chr_id=p_chr_id
and cle_id =p_cle_id;
l_klnv_tbl(1).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
l_klnv_tbl(1).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
l_klnv_tbl(1).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
OKS_CONTRACT_LINE_PUB.update_line (
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl,
x_klnv_tbl => x_klnv_tbl,
p_validate_yn => 'N');
SELECT id from okc_k_lines_b
WHERE cle_id = p_cle_id
and lse_id in (2,20);
select count(*) from oks_k_lines_b
where cle_id=p_line_id;
SELECT *
FROM oks_k_headers_b
WHERE chr_id = p_chr_id;
SELECT org_id
FROM okc_k_headers_b
WHERE id = p_new_chr_id;
l_khrv_tbl.DELETE;
x_khrv_tbl.DELETE;
l_khrv_tbl(ctr).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
l_khrv_tbl(ctr).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
l_khrv_tbl(ctr).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
SELECT *
FROM oks_k_lines_v
WHERE cle_id = p_cle_id;
SELECT date_terminated, price_list_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
select contract_number
from okc_k_headers_b
where id = l_chr_id;
l_klnv_tbl.DELETE;
x_klnv_tbl.DELETE;
l_klnv_tbl(ctr).LAST_UPDATED_BY :=get_lines_attr_rec.LAST_UPDATED_BY;
l_klnv_tbl(ctr).LAST_UPDATE_DATE :=get_lines_attr_rec.LAST_UPDATE_DATE;
l_klnv_tbl(ctr).LAST_UPDATE_LOGIN :=get_lines_attr_rec.LAST_UPDATE_LOGIN;
Procedure Update_Hdr_Amount
(
p_api_version IN Number,
p_init_msg_list IN Varchar2,
p_chr_id IN 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_Hdr_Amount';
Cursor l_line_csr Is Select Sum(Nvl(PRICE_NEGOTIATED,0))
From OKC_K_LINES_B
Where dnz_chr_id = p_chr_id And
lse_id in (7,8,9,10,11,35,25);
okc_contract_pub.update_contract_header
(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_tbl => l_chrv_tbl_in,
x_chrv_tbl => l_chrv_tbl_out
);
END Update_Hdr_Amount;
SELECT ID
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,SEQUENCE_NO
,UOM_CODE
,START_DATE
,LEVEL_PERIODS
,UOM_PER_PERIOD
,ADVANCE_PERIODS
,LEVEL_AMOUNT
,INVOICE_OFFSET_DAYS
,INTERFACE_OFFSET_DAYS
,COMMENTS
,DUE_ARR_YN
,AMOUNT
,LINES_DETAILED_YN
FROM oks_stream_levels_b
WHERE chr_id=p_chr_id
ORDER BY SEQUENCE_NO;/*BUG 7450286 */
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_chr_id;
l_strlvl_tbl.delete;
SELECT account_class,code_combination_id,percent,object_version_number
security_group_id
FROM oks_rev_distributions
WHERE cle_id = p_cle_id;
SELECT id
FROM oks_rev_distributions
WHERE cle_id = cleId and chr_id = chrId;
l_rev_tbl.DELETE;
l_rev_tbl(ctr).last_updated_by := OKC_API.G_MISS_NUM;
l_rev_tbl(ctr).last_update_date := OKC_API.G_MISS_DATE;
l_rev_tbl(ctr).last_update_login := OKC_API.G_MISS_NUM;
OKS_REV_DISTR_PUB.delete_Revenue_Distr(
p_api_version => l_api_version,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rdsv_tbl => l_rev_tbl);
OKS_REV_DISTR_PUB.insert_Revenue_Distr(
p_api_version => l_api_version,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rdsv_tbl => l_rev_tbl,
x_rdsv_tbl => x_rev_tbl);
SELECT
percent,
chr_id,
ctc_id,
sales_group_id,
sales_credit_type_id1,
sales_credit_type_id2
FROM oks_k_sales_credits
WHERE chr_id = p_chr_id
AND cle_id IS NULL;
l_scrv_tbl.DELETE;
x_scrv_tbl.DELETE;
l_scrv_tbl(ctr).last_updated_by := OKC_API.G_MISS_NUM;
l_scrv_tbl(ctr).last_update_date := OKC_API.G_MISS_DATE;
OKS_SALES_CREDIT_PUB.insert_Sales_credit(
p_api_version => l_api_version,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_scrv_tbl => l_scrv_tbl,
x_scrv_tbl => x_scrv_tbl);
SELECT
percent,
chr_id,
ctc_id,
sales_group_id,
sales_credit_type_id1,
sales_credit_type_id2
FROM oks_k_sales_credits
WHERE cle_id = p_cle_id;
SELECT percent
FROM oks_k_sales_credits
WHERE cle_id = p_new_cle_id;
l_scrv_tbl.DELETE;
x_scrv_tbl.DELETE;
l_scrv_tbl(ctr).last_updated_by := OKC_API.G_MISS_NUM;
l_scrv_tbl(ctr).last_update_date := OKC_API.G_MISS_DATE;
OKS_SALES_CREDIT_PUB.insert_Sales_credit(
p_api_version => l_api_version,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_scrv_tbl => l_scrv_tbl,
x_scrv_tbl => x_scrv_tbl);
SELECT id, NVL(orig_system_id1, cle_id_renewed) old_line_id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_new_chr_id and lse_id = 46
AND id=p_cle_id;
SELECT id, NVL(orig_system_id1, cle_id_renewed) old_line_id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_new_chr_id and lse_id = 46;
SELECT orig_system_id1
FROM okc_k_headers_b
WHERE id = p_new_chr_id;
SELECT subject_chr_id new_chr_id
FROM okc_operation_lines
WHERE subject_chr_id =new_chrId;
SELECT b.instance_id
FROM oks_subscr_header_b b
WHERE b.dnz_chr_id = chrId and b.cle_id = cleId;
SELECT b.id
FROM okc_k_lines_b a, okc_k_items b
WHERE b.cle_id = a.id and a.lse_id = 9 and b.object1_id1 = oldItemInst
AND a.dnz_chr_id = p_new_chr_id;
OKC_CONTRACT_ITEM_PUB.update_contract_item(l_api_version,
l_init_msg_list,
l_return_status,
l_msg_count,
l_msg_data,
l_cimv_rec,
x_cimv_rec);
OKC_CONTRACT_ITEM_PUB.update_contract_item(l_api_version,
l_init_msg_list,
l_return_status,
l_msg_count,
l_msg_data,
l_cimv_rec,
x_cimv_rec);
SELECT okc.inv_rule_id,
oks.billing_schedule_type
FROM okc_k_lines_b okc,
oks_k_lines_b oks
WHERE okc.id=p_cle_id
AND okc.id=oks.cle_id;
SELECT TRUNC(date_terminated) line_term_dt,
(nvl(line.price_negotiated,0) +nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = dtl.cle_id AND line.Id = l_orig_line_id;
select price_negotiated
from okc_k_lines_b
where id = l_new_line_id;
SELECT orig_system_id1
FROM okc_k_lines_b
WHERE id=p_cle_id;
select date_terminated
from okc_k_lines_b
where id = p_old_cle_id ;
SELECT ID
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,SEQUENCE_NO
,UOM_CODE
,START_DATE
,END_DATE
,LEVEL_PERIODS
,UOM_PER_PERIOD
,ADVANCE_PERIODS
,LEVEL_AMOUNT
,INVOICE_OFFSET_DAYS
,INTERFACE_OFFSET_DAYS
,COMMENTS
,DUE_ARR_YN
,AMOUNT
,LINES_DETAILED_YN
FROM oks_stream_levels_b
WHERE cle_id=p_cle_id
ORDER BY SEQUENCE_NO;/*BUG 7450286 */
SELECT
id
,dnz_chr_id
,cc_no
,cc_expiry_date
,cc_bank_acct_id
,cc_auth_code
,object_version_number
FROM oks_k_lines_b
WHERE cle_id=p_cle_id;
l_strlvl_tbl.delete;
l_bil_sch_out_tbl.delete;
l_klnv_tbl(1).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
l_klnv_tbl(1).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
l_klnv_tbl(1).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
OKS_CONTRACT_LINE_PUB.update_line (
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl,
x_klnv_tbl => x_klnv_tbl,
p_validate_yn => 'N');
p_perf_msg =>'After Update CCR Rule ');
SELECT
lines.id,
lines.start_date,lines.dnz_chr_id,lines.orig_system_id1
FROM okc_k_lines_b lines
WHERE lines.dnz_chr_id = p_chr_id
AND lines.cle_id = p_cle_id
AND lines.lse_id in (7,8,9,10,11,13,18,25,35);
SELECT inv_rule_id,cle_id
FROM okc_k_lines_b
WHERE id=p_cle_id;
SELECT Billing_schedule_type
FROM oks_k_lines_b
WHERE cle_id =p_cle_id;
UPDATE okc_k_lines_b set
price_negotiated = (SELECT sum(price_negotiated) FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id AND chr_id is null
AND cle_id = p_cle_id)
WHERE lse_id in (1, 19, 12) -- added 12 in the IN clause for bug # 3534513
AND chr_id = p_chr_id and id = p_cle_id;
SELECT count(*)
FROM oks_stream_levels_b
WHERE cle_ID = p_cle_id;
SELECT count(*) from oks_k_lines_b
WHERE cle_id = p_cle_id;
SELECT start_date,end_date,orig_system_id1
FROM okc_k_headers_b
WHERE id = l_new_chr_id;
SELECT start_date,end_date
FROM okc_k_headers_b
WHERE id = l_old_chr_id;
select
id
,chr_id
,quote_to_contact_id
,quote_to_site_id
,quote_to_email_id
,quote_to_phone_id
,quote_to_fax_id
,object_version_number
from oks_k_headers_b
where chr_id=p_chr_id;
SELECT id,object_version_number
FROM OKC_K_LINES_B
WHERE chr_id = p_chr_id
AND lse_id IN (1,12,19,46);
SELECT id,object_version_number
FROM OKS_k_LINES_B
WHERE cle_id = p_cle_id;
SELECT id
FROM okc_k_party_roles_b
WHERE dnz_chr_id = p_chr_id
AND cle_id is not null
AND rle_code in ('CUSTOMER','SUBSCRIBER');
SELECT id
FROM Okc_contacts
WHERE cpl_id = p_cpl_id;
okc_contract_pub.update_contract_header
(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_tbl => l_chrv_tbl_in,
x_chrv_tbl => l_chrv_tbl_out
);
l_khrv_tbl.delete;
l_khrv_tbl(1).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
l_khrv_tbl(1).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
l_khrv_tbl(1).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
OKS_CONTRACT_HDR_PUB.update_header (
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_tbl => l_khrv_tbl,
x_khrv_tbl => x_khrv_tbl,
p_validate_yn => 'N');
OKC_CONTRACT_PUB.UPDATE_CONTRACT_LINE (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_rec => l_clev_rec_in,
x_clev_rec => l_clev_rec_out
);
OKS_CONTRACT_LINE_PUB.UPDATE_LINE(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_rec => l_kln_rec_in,
x_klnv_rec => l_kln_rec_out,
p_validate_yn => 'N'
);
Okc_contract_party_pub.delete_contact
(
p_api_version => 1,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_tbl => l_ctcv_tbl_in
);
SELECT ID FROM OKC_K_LINES_B
WHERE CHR_ID = P_CHR_ID AND LSE_ID IN (1,12,19,46);
SELECT
lines.id,
lines.start_date,
lines.orig_system_id1,
lines.dnz_chr_id,
lines.lse_id
FROM okc_k_lines_b lines
WHERE lines.dnz_chr_id = p_chr_id
AND lines.cle_id IS NULL
AND lines.lse_id IN (1,12,19, 46)
ORDER BY lines.id;
SELECT
lines.id,
lines.start_date,
lines.orig_system_id1,
lines.dnz_chr_id,
lines.lse_id,
price_list_id
FROM okc_k_lines_b lines
WHERE lines.dnz_chr_id = p_chr_id
AND lines.cle_id IS NULL
AND lines.lse_id IN (1,12,19, 46)
ORDER BY lines.id;
SELECT
lines.id,
lines.start_date,
lines.orig_system_id1,
lines.dnz_chr_id,
lines.lse_id
FROM okc_k_lines_b lines
WHERE lines.dnz_chr_id = p_chr_id
AND lines.id = p_cle_id
AND lines.lse_id IN (1,12,19, 46,7,8,9,10,11,35,13,18,25)
ORDER BY lines.id;
SELECT billing_schedule_type
FROM oks_k_headers_b
where chr_id =p_chr_id;
SELECT *
FROM oks_k_lines_b
WHERE cle_id = p_cle_id
and (LOCKED_PRICE_LIST_ID is not null or
LOCKED_PRICE_LIST_LINE_ID is not null);
select PRICE_LIST_ID
from okc_k_lines_b
where id = l_cle_id;
SELECT
lines.id,
lines.start_date,
lines.orig_system_id1,
lines.dnz_chr_id,
lines.lse_id,
lines.price_list_id
FROM okc_k_lines_b lines
WHERE lines.dnz_chr_id = p_chr_id
AND lines.lse_id IN (12,13);
select contract_number
from okc_k_headers_b
where id = l_chr_id;
SELECT Billing_schedule_type
FROM oks_k_lines_b
WHERE cle_id =p_cle_id;
SELECT COUNT(id) cnt
FROM okc_k_items
WHERE cle_id = p_cle_id;
SELECT orig_system_id1
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT b.ste_code, orig_system_id1
FROM okc_k_headers_b a, okc_statuses_b b
WHERE a.id = l_chr_id
and a.sts_code = b.code;
SELECT id
FROM okc_k_lines_b
WHERE chr_id = p_chr_id
AND cle_id is null;
SELECT object_chr_id old_chr_id
FROM okc_operation_lines
WHERE subject_chr_id =l_new_chr_id and object_chr_id is not null;
select line_number
from okc_k_lines_b
where dnz_chr_id = l_old_chr_id
and date_terminated is not null;
select chr_id,cle_id
from okc_price_adjustments
where chr_id =p_chr_id
and cle_id is not null;
select id from okc_k_lines_b
where dnz_chr_id = l_new_chr_id and
orig_system_id1 not in (select id from okc_k_lines_b where dnz_chr_id = l_old_chr_id);
l_update_top_line boolean;
SELECT orig_system_id1
FROM okc_k_headers_b
WHERE id = p_new_chr_id;
OKC_CONTRACT_PUB.update_contract_header(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => 'N',
p_chrv_tbl => l_okc_hdr_tbl,
x_chrv_tbl => x_okc_hdr_tbl
);
'Error in update_contract_header');
update okc_price_adjustments
set chr_id = null
where chr_id =cur_pradj_rec.chr_id
and cle_id =cur_pradj_rec.cle_id;
UPDATE okc_k_lines_b set
price_negotiated = (SELECT sum(price_negotiated) FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id AND chr_id is null
AND cle_id = top_lines_rec.id)
WHERE lse_id in (1, 19, 12) -- added lse id 12 for bug # 3534513
AND chr_id = p_chr_id AND id = top_lines_rec.id;
update okc_k_headers_b set
estimated_amount =
(select sum(price_negotiated) from okc_k_lines_b
where dnz_chr_id = p_chr_id and cle_id is null)
where id = p_chr_id;
l_update_top_line := false;
l_update_top_line := true;
update_line_numbers (p_chr_id => p_chr_id
,p_update_top_line => l_update_top_line
,x_return_status => l_return_status);
,p_perf_msg =>'Update Line Number Status'||l_return_status);
select id
into l_oks_line_id
from oks_k_lines_b where cle_id = get_lines_rec.id;
Update oks_k_lines_b set
LOCKED_PRICE_LIST_ID = l_locked_price_list_id,
LOCKED_PRICE_LIST_LINE_ID = l_locked_price_list_line_id,
break_uom = get_oks_line_attr.break_uom
where id = l_oks_line_id;
p_perf_msg =>'After update');
/** Update_line_number procedure overloaded
-- aiyengar
-- 10/10/2001
**/
PROCEDURE Update_Line_Numbers
(
p_chr_id IN NUMBER,
p_cle_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
SELECT id
FROM OKC_K_LINES_B
where dnz_chr_id = p_chr_id
and cle_id = p_cle_id
and lse_id IN (7,8,9,10,11,35,13,18,25);
UPDATE okc_k_lines_b
SET line_number = l_line_seq_no
WHERE id = l_subline_id;
PROCEDURE Update_Line_Numbers
(
p_chr_id IN NUMBER,
p_update_top_line IN BOOLEAN,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
SELECT id from okc_k_headers_b
WHERE scs_code in ('SERVICE','WARRANTY')
AND id =p_chr_id;
SELECT id
FROM OKC_K_LINES_B
Where dnz_chr_id = p_chr_id
and lse_id IN (1,12,14,19, 46)
order by line_number;
SELECT id
FROM OKC_K_LINES_B
where cle_id = p_top_line_id
and lse_id IN (7,8,9,10,11,35,13,18,25);
If p_update_top_line Then
UPDATE OKC_K_LINES_B
SET line_number = l_topline_seq
WHERE id = l_topline_id;
UPDATE okc_k_lines_b
SET line_number = l_line_seq_no
WHERE id = l_subline_id;
END Update_Line_Numbers;
SELECT
QUOTE_TO_CONTACT_ID
,QUOTE_TO_SITE_ID
,QUOTE_TO_EMAIL_ID
,QUOTE_TO_PHONE_ID
,QUOTE_TO_FAX_ID
FROM OKS_K_HEADERS_B
WHERE chr_id = p_chr_id;
SELECT inv_organization_id,
authoring_org_id
FROM okc_k_headers_b
WHERE id=p_chr_id;
SELECT ctc.object1_id1 ,
pt.name contact_name,
pt.party_id,
hz.party_name party_name
FROM okc_contacts_v ctc,
okx_party_contacts_v pt,
hz_parties hz
WHERE ctc.cro_code = 'SVC_ADMIN'
AND ctc.dnz_chr_id = p_chr_id
AND pt.id1 = ctc.object1_id1
AND pt.id2 = ctc.object1_id2
AND pt.party_id = hz.party_id;
SELECT lower(email_address)
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = p_email_id;
SELECT DECODE(PHONE_AREA_CODE,NULL,NULL,PHONE_AREA_CODE||'-')
||PHONE_NUMBER phone_number
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = p_phone_id;
SELECT DECODE(PHONE_AREA_CODE, NULL,NULL,PHONE_AREA_CODE||'-')
||PHONE_NUMBER phone_number
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = p_fax_id;
SELECT b.party_name
FROM okx_cust_contacts_v a, hz_parties b
WHERE a.id1=p_contact_id
AND a.party_id=b.party_id;
SELECT ltrim(rtrim(substr(pt.name,instr(pt.name,',')+1)))||' '||
ltrim(rtrim(substr(pt.name,1,instr(pt.name,',')-1))) contact_name
,InitCap(ltrim(rtrim(SUBSTR(pt.name,INSTR(pt.name,',')+1)))) contact_first_name
FROM okx_cust_contacts_v pt
WHERE pt.id1 = p_contact_id;
SELECT
loc.ADDRESS1||''||loc.ADDRESS2||''||loc.ADDRESS3||''||loc.ADDRESS4 Address
,loc.CITY||' '||loc.state||' '||loc.postal_code city
,loc.country
FROM okx_cust_sites_v loc
WHERE id1 = l_site_id ;
select a.id1
from okx_cust_sites_v a,
okx_cust_contacts_v b
where b.id1 = p_contact_id
and a.id1 = b.cust_acct_site_id;
select contact_point_id
-- from okx_contact_points_v
from hz_contact_points
where contact_point_type = 'EMAIL'
and primary_flag = 'Y'
and owner_table_id = p_contact_id;
select contact_point_id
from hz_contact_points
where contact_point_type = 'PHONE'
and NVL(phone_line_type,'GEN') = 'GEN'
and primary_flag = 'Y'
and owner_table_id = p_contact_id;
select contact_point_id
from hz_contact_points
where contact_point_type = 'PHONE'
and phone_line_type = 'FAX'
and owner_table_id = p_contact_id;
OKS_CONTRACT_HDR_PUB.update_header (
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_tbl => l_khrv_tbl,
x_khrv_tbl => x_khrv_tbl,
p_validate_yn => 'Y');
SELECT Counter_Group_id FROM OKX_Counter_Groups_V WHERE Source_Object_Id=P_KLine_Id
and Source_Object_Code='CONTRACT_LINE';
SELECT Object_Version_Number FROM Cs_Counter_Groups
WHERE Counter_group_Id=P_CtrGrp_Id;
CS_Counters_PUB.Update_Ctr_Grp(
p_api_version =>l_api_version,
p_init_msg_list =>l_init_msg_list,
p_commit =>l_commit,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_ctr_grp_id =>l_ctr_grp_id,
p_object_version_number => l_object_version_number,
p_ctr_grp_rec =>l_ctr_grp_rec,
p_cascade_upd_to_instances =>l_cascade_upd_to_instances,
x_object_version_number => x_object_version_number
);
'Error in update counter.'
);
PROCEDURE Delete_OKS_Line(
p_cle_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(1);
select id from okc_k_lines_b where cle_id = l_cle_id;
select id from oks_k_lines_b where cle_id = l_cle_id;
l_temp_tbl.delete(l_first_index);
oks_contract_line_pub.delete_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_rec => l_klnv_rec);
l_cle_tbl.delete(l_temp_counter);
End Delete_OKS_Line;
PROCEDURE Delete_Contract (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
p_chr_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
-- Get all lines
CURSOR Cur_Line (P_Chr_Id IN NUMBER) IS
SELECT ID FROM OKC_K_Lines_b
WHERE chr_ID=p_chr_Id;
SELECT ID FROM OKC_GOVERNANCES
WHERE dnz_chr_ID=p_chr_Id
And cle_id Is Null;
select id from oks_k_headers_b where chr_id = p_chr_id;
select a.id, a.lse_id
from okc_k_lines_b a
where a.dnz_chr_id = p_chr_id and a.cle_id IS NULL;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract';
'Header id passed to Delete_Contract is Null'
);
Delete_OKS_Line(
p_cle_id => topline_rec.id,
x_return_status => l_return_status);
'Error from Delete_OKS_Line'
);
okc_Contract_pub.delete_governance(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_gvev_tbl => l_gvev_tbl_in);
oks_contract_hdr_pub.delete_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_rec => l_khrv_rec);
oks_contract_hdr_pub.delete_history(
p_api_version =>l_api_version,
p_init_msg_list =>l_init_msg_list,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_chr_id => p_chr_id);
OKC_DELETE_CONTRACT_PUB.delete_contract(
p_api_version =>l_api_version,
p_init_msg_list =>l_init_msg_list,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_chrv_rec =>l_chrv_rec);
End Delete_Contract;
PROCEDURE Delete_Contract_Line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
p_line_id IN 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);
select lse_id, cle_id
from okc_k_lines_b
where id = l_line_id;
OKS_COVERAGES_PUB.DELETE_COVERAGE(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
P_service_Line_Id => p_line_id);
Delete_OKS_Line(
p_cle_id => p_line_id,
x_return_status => l_return_status);
'Error from Delete_OKS_Line'
);
OKC_CONTRACT_PVT.delete_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
--p_clev_rec => l_clev_rec);
End Delete_Contract_Line;
PROCEDURE Delete_Transfer_Contract(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
p_chr_id IN NUMBER,
p_cle_id IN NUMBER DEFAULT NULL,
p_intent IN VARCHAR2, -- new
x_contract_number OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR cur_has_lines_been_renewed_h IS -- header
SELECT 1
FROM okc_operation_lines a, okc_operation_instances b, okc_class_operations c
where a.object_chr_id= p_chr_id and
c.id=b.cop_id and
c.opn_code in('RENEWAL', 'REN_CON') and
a.oie_id=b.id and
a.active_yn='Y';
SELECT subject_cle_id
FROM okc_operation_lines a,
okc_operation_instances b,
okc_class_operations c,
okc_k_lines_b d
where a.object_cle_id = d.id and
d.cle_id = p_cle_id and -- should be a top line id
a.object_chr_id = p_chr_id and
c.id=b.cop_id and
c.opn_code in('RENEWAL', 'REN_CON') and
a.oie_id=b.id and
a.active_yn='Y' and
a.object_chr_id = d.dnz_chr_id;
SELECT subject_chr_id
FROM okc_operation_lines a, okc_operation_instances b, okc_class_operations c
where a.object_cle_id= p_cle_id and -- subline id
c.id=b.cop_id and
c.opn_code in('RENEWAL', 'REN_CON') and
a.oie_id=b.id and
a.active_yn='Y' and
a.object_chr_id = p_chr_id;
SELECT contract_number , contract_number_modifier
From okc_k_headers_b b
where id = p_subject_chr_id;
END Delete_Transfer_Contract;
New procedure to delete toplines an sublines for OKS. This builds on
OKS_SETUP_UTIL_PUB.Delete_Contract_Line and adds stuff that authoring does and some other
stuff that nobody seems to be doing
Parameters
p_line_id : id of the top line/subline from OKC_K_LINES_B table
*/
PROCEDURE DELETE_TOP_SUB_LINE
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_line_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TOP_SUB_LINE';
SELECT id
FROM okc_k_lines_b a
WHERE (a.id = cp_line_id OR a.cle_id = cp_line_id)
AND lse_id IN (1,12,14,19,46, 7,8,9,10,11,35, 13, 18, 25)
AND nvl(term_cancel_source, 'X') IN ('IBTRANSFER', 'IBTERMINATE', 'IBRETURN');
SELECT a.lse_id, a.cle_id, a.cust_acct_id, a.bill_to_site_use_id,
b.locked_price_list_line_id, b.trxn_extension_id
FROM okc_k_lines_b a, oks_k_lines_b b
WHERE a.id = cp_line_id
AND b.cle_id = a.id;
SELECT b.locked_price_list_line_id
FROM okc_k_lines_b a, oks_k_lines_b b
WHERE a.cle_id = cp_line_id
AND b.cle_id = a.id;
SELECT id
FROM okc_k_lines_b
CONNECT BY PRIOR id = cle_id
START WITH id = cp_line_id;
SELECT cas.cust_account_id cust_account_id, ca.party_id party_id
FROM hz_cust_site_uses_all csu, hz_cust_acct_sites_all cas, hz_cust_accounts_all ca
WHERE csu.site_use_id = cp_bill_to_site_use_id
AND cas.cust_acct_site_id = csu.cust_acct_site_id
AND ca.cust_account_id = cas.cust_account_id;
SELECT ca.party_id party_id
FROM hz_cust_accounts_all ca
WHERE ca.cust_account_id = cp_cust_acct_id;
SELECT jtf_note_id
FROM JTF_NOTES_VL
WHERE source_object_id = cp_source_object_id
AND source_object_code = 'OKS_COV_NOTE';
SAVEPOINT delete_top_sub_line_PUB;
FND_MESSAGE.set_NAME(G_OKS_APP_NAME, 'OKS_TRANSFER_LINE_NO_DELETE');
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.del_trxn_extn', 'calling IBY_FNDCPT_TRXN_PUB.delete_transaction_extension, p_payer.party_id='||l_party_id||' ,p_payer.cust_account_id='||l_cust_account_id||
' ,p_entity_id='||l_trxn_extension_id);
IBY_FNDCPT_TRXN_PUB.delete_transaction_extension(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payer => l_payer,
--p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD, -- UPWARD
p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_FULL, -- FULL, bug 5439978
p_entity_id => l_trxn_extension_id,
x_response => l_response);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.del_trxn_extn', 'after call to IBY_FNDCPT_TRXN_PUB.delete_transaction_extension, x_return_status='||x_return_status||
' ,result_code='||l_response.result_code||' ,result_category='||l_response.result_category||' ,result_message='||l_response.result_message);
OKS_COVERAGES_PUB.delete_coverage(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_service_line_id => p_line_id);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_coverage', 'after call to OKS_COVERAGES_PUB.delete_coverage, x_return_status='||x_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_cov_notes', 'calling JTF_NOTES_PUB.secure_delete_note, p_jtf_note_id='||l_jtf_note_id_tbl(i));
JTF_NOTES_PUB.secure_delete_note(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => 100,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_jtf_note_id => l_jtf_note_id_tbl(i),
p_use_AOL_security => FND_API.G_FALSE);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_cov_notes', 'after call to JTF_NOTES_PUB.secure_delete_note, x_return_status='||x_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_cov_notes', 'calling OKS_PM_PROGRAMS_PVT.undo_pm_line, p_cle_id='||p_line_id);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_cov_notes', 'after call to OKS_PM_PROGRAMS_PVT.undo_pm_line, x_return_status='||x_return_status);
OKS_QP_PKG.delete_locked_pricebreaks(
p_api_version => 1.0,
p_list_line_id => l_lock_pl_line_id,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_locked_pricebreaks', 'after call to OKS_QP_PKG.delete_locked_pricebreaks, x_return_status='||x_return_status);
OKS_QP_PKG.delete_locked_pricebreaks(
p_api_version => 1.0,
p_list_line_id => l_lock_pl_line_id_tbl(i),
p_init_msg_list => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_locked_pricebreaks_sub', 'after call to OKS_QP_PKG.delete_locked_pricebreaks, x_return_status='||x_return_status);
DELETE FROM oks_k_lines_tl WHERE id IN
(SELECT id FROM oks_k_lines_b WHERE cle_id = l_id_tbl(i));
DELETE FROM oks_k_lines_b WHERE cle_id = l_id_tbl(i);
DELETE FROM oks_k_sales_credits WHERE cle_id = l_id_tbl(i);
DELETE FROM oks_rev_distributions WHERE cle_id = l_id_tbl(i);
DELETE FROM OKS_QUALIFIERS WHERE list_line_id = l_id_tbl(i);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_oks_entities', 'done');
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_okc_entities', 'calling OKC_CONTRACT_PVT.delete_contract_line, p_line_id='||p_line_id);
OKC_CONTRACT_PVT.delete_contract_line(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_id => p_line_id);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_okc_entities', 'after call to OKC_CONTRACT_PVT.delete_contract_line, x_return_status='||x_return_status);
ROLLBACK TO delete_top_sub_line_PUB;
ROLLBACK TO delete_top_sub_line_PUB;
ROLLBACK TO delete_top_sub_line_PUB;
END DELETE_TOP_SUB_LINE;
SELECT (ADD_MONTHS(v_start_date, (nyears+1)*12) - v_start_date -
DECODE(ADD_MONTHS(v_end_date, -12),( v_end_date-366), 0,
DECODE(ADD_MONTHS(v_start_date, (nyears+1)*12) - ADD_MONTHS(v_start_date, nyears*12), 366, 1, 0)))
/ (nyears+1) /(v_end_date-v_start_date+1)
FROM (SELECT trunc(MONTHS_BETWEEN(v_end_date, v_start_date)/12) nyears FROM dual) dual ;
PROCEDURE Update_Annualized_Factor_BMGR(X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
P_batch_size in number,
P_Num_Workers in number)
IS
BEGIN
--
-- Manager processing for OKC_K_LINES_B table
--
fnd_file.put_line(FND_FILE.LOG, 'Start of Update_Annualized_Factor_BMGR ');
fnd_file.put_line(FND_FILE.LOG, 'starting okc_k_lines_b update worker ');
END Update_Annualized_Factor_BMGR;
PROCEDURE Update_Annualized_Factor_HMGR(X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
P_batch_size in number,
P_Num_Workers in number)
IS
BEGIN
--
-- Manager processing for OKC_K_LINES_BH table
--
fnd_file.put_line(FND_FILE.LOG, 'Start of Update_Annualized_Factor_HMGR ');
fnd_file.put_line(FND_FILE.LOG, 'starting okc_k_lines_bh update worker ');
END Update_Annualized_Factor_HMGR;
PROCEDURE Update_Annualized_Factor_BWKR(X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
P_batch_size in number,
P_Worker_Id in number,
P_Num_Workers in number)
IS
l_worker_id number;
l_update_name varchar2(30) := 'OKCLNUPG_CP';
ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_update_name,
P_worker_id,
P_num_workers,
P_batch_size,
0);
ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_batch_size,
TRUE);
UPDATE (Select /*+ rowid(hdr) leading(hdr) use_nl_with_index(cle) */
cle.payment_instruction_type,
cle.annualized_factor,
hdr.payment_instruction_type hdr_payment_instruction_type,
case
when cle.lse_id in (1, 12, 14, 19, 46, 7, 8, 9, 10, 11, 13, 18, 25, 35)
then (add_months (cle.start_date, (trunc (months_between
(cle.end_date, cle.start_date) / 12) + 1) * 12) -
cle.start_date - decode (add_months (cle.end_date, -12),
(cle.end_date-366), 0, decode ( add_months(cle.start_date,
(trunc(months_between(cle.end_date, cle.start_date)
/ 12) + 1) * 12) - add_months(cle.start_date,
trunc(months_between(cle.end_date, cle.start_date) / 12)
* 12), 366, 1, 0)))
/ (trunc (months_between (cle.end_date, cle.start_date) / 12) + 1)
/ (cle.end_date - cle.start_date + 1)
ELSE cle.annualized_factor
end new_annualized_factor
from okc_k_headers_all_b hdr,
okc_k_lines_b cle
where hdr.rowid between l_start_rowid and l_end_rowid
and hdr.id = cle.dnz_chr_id
and hdr.scs_code in ('SERVICE', 'WARRANTY', 'SUBSCRIPTION'))
set payment_instruction_type = hdr_payment_instruction_type,
annualized_factor = new_annualized_factor;
ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_batch_size,
FALSE);
END Update_Annualized_Factor_BWKR;
PROCEDURE Update_Annualized_Factor_HWKR(X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
P_batch_size in number,
P_Worker_Id in number,
P_Num_Workers in number)
IS
l_worker_id number;
l_update_name varchar2(30) := 'OKCLNUPH_CP';
ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_update_name,
P_worker_id,
P_num_workers,
P_batch_size,
0);
ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_batch_size,
TRUE);
UPDATE (Select /*+ rowid(hdr) leading(hdr) use_nl_with_index(cle) */
cle.payment_instruction_type,
cle.annualized_factor,
hdr.payment_instruction_type hdr_payment_instruction_type,
case
when cle.lse_id in (1, 12, 14, 19, 46, 7, 8, 9, 10, 11, 13, 18, 25, 35)
then (add_months (cle.start_date, (trunc (months_between
(cle.end_date, cle.start_date) / 12) + 1) * 12) -
cle.start_date - decode (add_months (cle.end_date, -12),
(cle.end_date-366), 0, decode ( add_months(cle.start_date,
(trunc(months_between(cle.end_date, cle.start_date)
/ 12) + 1) * 12) - add_months(cle.start_date,
trunc(months_between(cle.end_date, cle.start_date) / 12)
* 12), 366, 1, 0)))
/ (trunc (months_between (cle.end_date, cle.start_date) / 12) + 1)
/ (cle.end_date - cle.start_date + 1)
ELSE cle.annualized_factor
end new_annualized_factor
from okc_k_headers_all_bh hdr,
okc_k_lines_bh cle
where hdr.rowid between l_start_rowid and l_end_rowid
and hdr.id = cle.dnz_chr_id
and hdr.major_version = cle.major_version
and hdr.scs_code in ('SERVICE', 'WARRANTY', 'SUBSCRIPTION'))
set payment_instruction_type = hdr_payment_instruction_type,
annualized_factor = new_annualized_factor;
ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_batch_size,
FALSE);
END Update_Annualized_Factor_HWKR;