[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ID INTO V_ID FROM OKC_RULE_GROUPS_V WHERE Dnz_CHR_ID = p_chr_id And cle_id Is Null;
SELECT ID INTO V_ID FROM OKC_RULE_GROUPS_V WHERE CLE_ID = p_cle_id;
Select ID Into V_ID From OKC_RULES_V
Where rgp_id = p_rgp_id
And Rule_information_category = p_rule_type;
SELECT 1
FROM MTL_UNITS_OF_MEASURE_TL TL, okc_time_code_units_v okc
WHERE TL.uom_code = okc.uom_code
AND TL.uom_code = p_uom_code
--AND TL.uom_class = 'Time' commented for bug#5585356
AND okc.active_flag = 'Y'
AND TL.LANGUAGE = USERENV('LANG');
SELECT 1
FROM MTL_UNITS_OF_MEASURE_TL TL, okc_time_code_units_v okc
WHERE TL.uom_code = okc.uom_code
AND TL.uom_code = p_uom_code
-- AND TL.uom_class = 'Time' commednted for bug#5585356
AND okc.active_flag = 'Y'
AND TL.LANGUAGE = USERENV('LANG') ;
SELECT tce_code, quantity
FROM okc_time_code_units_b
WHERE uom_code = p_uom_code
AND active_flag = 'Y';
SELECT le.date_start date_start,
le.date_end date_end,
le.id id,
le.amount amount ,
le.date_revenue_rule_start date_revenue_rule_start,
le.date_receivable_gl date_receivable_gl,
le.date_transaction date_transaction,
nvl(le.date_to_interface,sysdate) date_to_interface,
le.date_due date_due,
le.date_completed date_completed,
le.rul_id rul_id,
le.date_print date_print,
le.sequence_number sequence_number,
str.uom_code advance_period,
str.uom_per_period tuom_per_period,
str.start_date tp_start_date
FROM oks_stream_levels_b str
,oks_level_elements le
WHERE le.cle_id = p_id
AND le.rul_id = str.id
AND le.date_completed IS NULL
AND trunc(nvl(le.date_to_interface,sysdate)) <= trunc(p_date)
AND not exists
(select /*+ push_subq no_unnest */ 1 from oks_bill_sub_lines bsl /* Added hint for Bug#12416004*/
where le.cle_id = bsl.cle_id
and trunc(le.date_start) >= trunc(bsl.date_billed_from)
and trunc(le.date_end) <= trunc(bsl.date_billed_to))
ORDER BY le.date_start;
SELECT lvl.date_start
FROM oks_level_elements lvl
WHERE lvl.cle_id = p_cle_id
AND lvl.date_start > p_date
ORDER BY lvl.date_start;
SELECT date_terminated,end_date ,start_date
FROM okc_k_lines_b
WHERE id = p_id;
These two selects were included to ensure that
there wont be any duplicate bills
--- Hari 11/30/2001
********/
/*
Cursor l_bcl_csr(p_cle_id IN NUMBER) is
Select max(date_billed_to)
From oks_bill_cont_lines
WHERE cle_id = p_cle_id;
SELECT max(date_billed_to)
FROM oks_bill_sub_lines
WHERE cle_id = p_cle_id;
Select count(id)
From oks_level_elements
Where rul_id = p_rule_id
And date_completed IS NOT NULL;
Procedure delete_row_level_elements( p_rul_id IN Number,
p_seq_no IN Number,
x_return_status OUT NOCOPY Varchar2)
IS
Begin
--delete level elements for given sll id
DELETE from OKS_LEVEL_ELEMENTS
where rul_id = p_rul_id ;
DELETE FROM oks_stream_levels_b
WHERE id = p_rul_id;
SELECT id from okc_k_lines_b
WHERE cle_id = p_line_id
AND lse_id in (7,8,9,10,11,35,18,13,25);
oks_bill_util_pub.delete_level_elements
(
p_api_version => 1.0,
p_terminated_date => p_terminated_date,
p_chr_id => NULL,
p_cle_id => p_id,
p_init_msg_list => 'T',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
oks_bill_util_pub.delete_level_elements
(
p_api_version => 1.0,
p_terminated_date => p_terminated_date,
p_chr_id => NULL,
p_cle_id => cov_cur.id,
p_init_msg_list => 'T',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
oks_bill_util_pub.delete_level_elements
(
p_api_version => 1.0,
p_terminated_date => p_terminated_date,
p_chr_id => NULL,
p_cle_id => p_id,
p_init_msg_list => 'T',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
Procedure delete_level_elements (p_api_version IN NUMBER,
p_rule_id IN Number,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY Varchar2 )
IS
----modified by upma for rules re-arch.
/*** This cursor will get sll info */
Cursor l_get_line_sll_csr IS
Select id, cle_id , sequence_no
From Oks_stream_levels_b
where id = p_rule_id;
Select id
From Okc_k_lines_b
Where cle_id = p_cle_id
and lse_id in (35,7,8,9,10,11,13,18,25);
Select id , sequence_no
From oks_stream_levels_b
Where cle_id = p_cp_line_id
And sequence_no = l_seq_no;
SELECT nvl(billing_schedule_type,'T') billing_schedule_type
FROM oks_k_lines_b
WHERE cle_id = p_line_id;
/** If rule type is 'P' then do not delete covered level rule sll level elements
Else Delete ***/
FOR l_get_cp_rec IN l_get_cp_csr(l_get_line_sll_rec.cle_id)
Loop
Open l_get_cp_sll_csr(l_get_cp_rec.id,l_get_line_sll_rec.sequence_no) ;
/*** Delete level elements ****/
Delete_row_level_elements (l_get_cp_sll_rec.id,
l_get_cp_sll_rec.sequence_no,
x_return_status);
/*** Delete rule and level elements of the rule id that was passed ****/
Delete_row_level_elements (p_rule_id,
l_get_line_sll_rec.cle_id,
x_return_status);
End Delete_level_elements;
PROCEDURE delete_level_elements(
p_api_version IN NUMBER,
p_terminated_date IN DATE,
p_chr_id IN NUMBER,
p_cle_id IN NUMBER ,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
---Modified by upma for re-arch.
--deltes lvl elements for line and sub line.
Cursor l_subLine_Csr(l_line_id number) Is
SELECT id , TRUNC(date_terminated) cp_term_dt
FROM okc_k_lines_b
WHERE cle_id = l_line_id
AND lse_id in (35,7,8,9,10,11,13,18,25);
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE Date_Completed is NULL
AND TRUNC(date_start) >= TRUNC(p_terminated_date)
AND dnz_chr_id = p_chr_id;
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE Date_Completed is NULL
AND TRUNC(date_start) >= TRUNC(p_terminated_date)
AND cle_id = p_cle_id;
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE Date_Completed is NULL
AND TRUNC(date_start) >= nvl(TRUNC(l_subline_rec.cp_term_dt),TRUNC(p_terminated_date))
AND cle_id = l_subline_rec.id;
END delete_level_elements;
PROCEDURE delete_rule(
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
l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
select rul.id
from okc_rules_b rul
where rul.rule_information_category in ('IRE') and
dnz_chr_id = p_chr_id;
OKC_RULE_PUB.delete_rule(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_tbl => p_rulv_tbl);
END Delete_Rule;
PROCEDURE delete_slh_rule(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
p_cle_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
BEGIN
x_return_status := OKC_API.G_RET_STS_SUCCESS;
UPDATE oks_k_lines_b SET billing_schedule_type = NULL
WHERE cle_id =p_cle_id;
DELETE FROM oks_stream_levels_b where cle_id = P_CLE_ID;
END Delete_SLH_Rule;
select
chr_id, cle_id,
account_class,
code_combination_id,
percent
from oks_rev_distributions
where cle_id = p_cle_id;
x_rev_tbl(i).last_updated_by := OKC_API.G_MISS_NUM;
x_rev_tbl(i).last_update_date := OKC_API.G_MISS_DATE;
x_rev_tbl(i).last_update_login := OKC_API.G_MISS_NUM;
OKS_REV_DISTR_PUB.insert_Revenue_Distr(
p_api_version => l_api_version,
x_return_status => x_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rdsv_tbl => p_rev_tbl,
x_rdsv_tbl => l_rev_tbl);
select
percent,
chr_id,
ctc_id,
sales_credit_type_id1,
sales_credit_type_id2
from OKS_K_SALES_CREDITS
where cle_id = p_cle_id;
x_scrv_tbl(i).last_updated_by := OKC_API.G_MISS_NUM;
x_scrv_tbl(i).last_update_date := OKC_API.G_MISS_DATE;
OKS_SALES_CREDIT_PUB.insert_Sales_credit(
p_api_version => l_api_version,
x_return_status => x_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_scrv_tbl => p_scrv_tbl,
x_scrv_tbl => l_scrv_tbl);
procedure update_line_item(p_cle_id IN NUMBER,
p_item_id IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2) IS
l_api_version NUMBER := 1.0;
select id
from okc_k_items_v
where cle_id = p_cle_id;
OKC_CONTRACT_ITEM_PUB.update_contract_item(
p_api_version => l_api_version,
x_return_status => x_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cimv_rec => l_cimv_rec_in,
x_cimv_rec => l_cimv_rec_out);
end update_line_item;
select count(*)
from okc_k_lines_b
where cle_id = p_cle_id
and lse_id in (7,8,9,10,11,35, 18,25);
select id, price_negotiated
from okc_k_lines_b
where cle_id = p_cle_id
and lse_id in (7,8,9,10,11,35, 18,25);
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
x_return_status => x_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
select id, dnz_chr_id, inv_rule_id
from okc_k_lines_b
where id = p_cle_id;
SELECT *
FROM oks_stream_levels_b
WHERE cle_id = p_cle_id;
SELECT nvl(billing_schedule_type,'T') billing_schedule_type
FROM oks_k_lines_b
WHERE cle_id = p_cle_id;
l_sll_tbl.DELETE;
select id
from okc_rules_b
where rgp_id = p_rgp_id
and rule_information_category = 'USV';
l_rulv_tbl_in.DELETE;
l_rulv_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
l_rulv_tbl_in(1).last_update_date := SYSDATE;
l_rulv_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
l_rulv_tbl_in(1).last_update_date := OKC_API.G_MISS_DATE;
OKC_RULE_PUB.update_rule(
p_api_version => l_api_version,
x_return_status => x_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rulv_tbl => l_rulv_tbl_in,
x_rulv_tbl => l_rulv_tbl_out);
procedure update_header_amount(p_cle_id IN NUMBER,
x_status OUT NOCOPY VARCHAR2) IS
l_api_version CONSTANT NUMBER := 1.0;
select sum(price_negotiated) sum
from okc_k_lines_b
where dnz_chr_id = p_chr_id
and lse_id in (7,8,9,10,11,35,13,18,25);
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 );
End update_header_amount;
select dnz_chr_id
from okc_k_lines_b
where id = p_source_rec.cle_id;
select id
from okc_rule_groups_b
where cle_id = p_cle_id;
Select nvl(max(to_number(line_number)),0)
INTO l_top_line_number
FROM OKC_K_LINES_B
WHERE dnz_chr_id = g_chr_id
and cle_id is null;
Update okc_k_lines_b Set line_number = l_top_line_number
Where id = p_target_tbl(idx).cle_id;
OKS_SETUP_UTIL_PUB.update_line_numbers(p_chr_id => g_chr_id,
p_cle_id => p_target_tbl(idx).cle_id,
x_return_status => l_return_status);
update_line_item(p_target_tbl(idx).cle_id, p_target_tbl(idx).item_id, l_return_status);
update_header_amount(p_source_rec.cle_id, l_return_status);
SELECT ccr.net_reading last_reading
FROM Cs_ctr_counter_values_v ccr
WHERE ccr.counter_id = p_counter_id
ORDER BY value_timestamp desc;
SELECT ccr.initial_reading last_reading
FROM cs_counters ccr
WHERE ccr.counter_id = p_counter_id;
Select itm.uom_code, line.cle_id usage_id
From Okc_K_items Itm, okc_k_lines_b line
Where itm.cle_id = line.id
And itm.object1_id1 = p_counter_id
And itm.jtot_object1_code = 'OKX_COUNTER';
SELECT nvl(SUM(bsl.amount),0) tot_credit_amt
FROM Oks_bill_cont_lines bcl, Oks_bill_sub_lines bsl
WHERE bsl.Cle_id = p_cp_line_id
AND bcl.id = bsl.bcl_id
AND bcl.bill_action = 'TR';
PROCEDURE delete_duplicate_lines (p_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
,x_lines_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
,x_return_status OUT NOCOPY Varchar2
) IS
l_lines_rec_tmp OKS_BILL_REC_PUB.line_report_rec_type;
l_lines_tbl_tmp.DELETE(l_tbl_idx) ;
FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: delete_duplicate_lines Error ' || sqlerrm);
End delete_duplicate_lines ;
PROCEDURE delete_duplicate_sub_lines (p_sub_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
,x_sub_lines_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
,x_return_status OUT NOCOPY Varchar2
) IS
l_sub_lines_rec_tmp OKS_BILL_REC_PUB.line_report_rec_type;
l_sub_lines_tbl_tmp.DELETE(l_tbl_idx) ;
FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: delete_duplicate_sub_lines Error ' || sqlerrm);
End delete_duplicate_sub_lines ;
Select nvl(rul.rule_information13,'N')
From okc_rules_b rul
,okc_rule_groups_b rgp
Where rgp.dnz_chr_id = p_dnz_chr_id
And rgp.id = rul.rgp_id
And rul.rule_information_category = 'SBG';
Select name
From OKX_PARTIES_V
Where id1 = p_object1_id1
And id2 = p_object1_id2 ;
Select name
From OKX_CUSTOMER_ACCOUNTS_V
Where id1 = p_object1_id1
And id2 = p_object1_id2 ;
Select name
From OKX_CUSTOMER_PRODUCTS_V
Where id1 = p_object1_id1
and id2 = p_object1_id2
and organization_id = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID') ;*/
Select SIT.description
FROM CSI_ITEM_INSTANCES cp,
MTL_SYSTEM_ITEMS_TL SIT
WHERE cp.instance_ID=p_object1_id1
and SIT.inventory_item_id = cp.inventory_item_id
and SIT.LANGUAGE = userenv('LANG')
and SIT.organization_id = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID');
Select name
From OKX_SYSTEM_ITEMS_V
Where id1 = p_object1_id1
and id2 = p_object1_id2
and organization_id = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID')
and serviceable_product_flag='Y' ;
Select name
From OKX_CUST_SITE_USES_V
Where id1 = p_object1_id1
and id2 = p_object1_id2
and NVL(ORG_ID, -99) = SYS_CONTEXT('OKC_CONTEXT','ORG_ID') ;
Select name
From OKX_SYSTEMS_V
Where id1 = p_object1_id1
and id2 = p_object1_id2
and NVL(ORG_ID, -99) = SYS_CONTEXT('OKC_CONTEXT','ORG_ID') ;
PROCEDURE delete_duplicate_currency_code (p_currency_table IN OKS_BILL_REC_PUB.line_report_tbl_type
,x_currency_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
,x_return_status OUT NOCOPY Varchar2
) IS
l_currency_rec_tmp OKS_BILL_REC_PUB.line_report_rec_type;
l_currency_table_tmp.DELETE(l_tbl_idx) ;
FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: delete_duplicate_currency_code Error ' || sqlerrm);
End delete_duplicate_currency_code ;
l_currency_table_tmp.DELETE ;
l_currency_table_tmp.DELETE ;*/
DELETE_DUPLICATE_CURRENCY_CODE (p_currency_table => l_currency_table_in
,x_currency_table => l_currency_table_tmp
,x_return_status => l_return_status
) ;
SELECT name FROM fnd_currencies_tl
WHERE currency_code = p_currency_code
AND language = USERENV('LANG');
SELECT Hdr.Contract_number
,Hdr.Contract_number_modifier
,Hdr.Currency_code
,Hdr.Inv_organization_id
,Hdr.authoring_org_id
,line.dnz_chr_id
,line.cle_id
,line.lse_id
,line.start_date
,line.end_date
,line.price_negotiated
,line.date_terminated
,okp.object1_id1
,okp.object1_id2
,line.line_number
FROM
OKC_K_PARTY_ROLES_B okp
,OKC_K_LINES_B line
,OKC_K_HEADERS_B Hdr
WHERE Hdr.id = line.dnz_chr_id
AND line.id = p_line_id
AND okp.dnz_chr_id = hdr.id
AND okp.rle_code in ( 'CUSTOMER','SUBSCRIBER');
Select cst.PARTY_NUMBER
,cst.NAME
From OKX_PARTIES_V cst
Where cst.id1 = p_object1_id1
and cst.id2 = p_object1_id2 ;
Select grp.name
From OKC_K_GROUPS_V grp
,OKC_K_GRPINGS gpg
Where gpg.included_chr_id = p_dnz_chr_id
and grp.id = gpg.cgp_parent_id
and rownum < 2 ;
Select cst.PARTY_ID||' - '||cst.NAME ServiceAccount
From OKC_RULE_GROUPS_V rgp
,OKC_RULES_V rul
,OKX_CUSTOMER_ACCOUNTS_V cst
Where rgp.CLE_ID = p_line_id
and rgp.ID = rul.RGP_ID
and rul.RULE_INFORMATION_CATEGORY = 'CAN'
and cst.ID1 = rul.OBJECT1_ID1
and cst.ID2 = rul.OBJECT1_ID2 ;
Select sys.NAME LineName
From OKX_SYSTEM_ITEMS_V sys
,OKC_K_ITEMS itm
Where itm.CLE_ID = p_line_id
and sys.ID1 = itm.OBJECT1_ID1
and sys.ID2 = itm.OBJECT1_ID2;
Select decode(itm.JTOT_OBJECT1_CODE,
'OKX_CUSTPROD', 'Covered Product',
'OKX_COVITEM', 'Covered Item',
'OKX_COVSITE', 'Covered Site',
'OKX_COVSYST', 'Covered System',
'OKX_CUSTACCT', 'Customer Account',
'OKX_PARTY', 'Covered Party',
itm.JTOT_OBJECT1_CODE) CoveredLine
,itm.JTOT_OBJECT1_CODE
,itm.object1_id1
,itm.object1_id2
From OKC_K_ITEMS itm
Where itm.CLE_ID = p_sub_line_id;
select line_number into l_line_num from okc_k_lines_b
where id=p_billrep_error_tbl(l_sub_line_idx).Top_Line_id;
SELECT Count(Distinct Chr_id)
FROM oks_process_billing
where currency_code= p_code
and line_no between p_process_from and p_process_to;
PROCEDURE UPDATE_OKS_LEVEL_ELEMENTS
( p_line_id IN number ,
x_return_status OUT NOCOPY varchar2 ) IS
CURSOR L_OKS_LEVEL_ELEMENTS_CSR ( P_LINE_ID in NUMBER ) IS
SELECT LEVL.ID
FROM OKS_LEVEL_ELEMENTS LEVL ,
OKC_RULES_B RULES ,
OKC_RULE_GROUPS_B RGP
WHERE LEVL.RUL_ID = RULES.ID
AND RULES.RGP_ID = RGP.ID
AND RULE_INFORMATION_CATEGORY = 'SLL'
AND RGP.CLE_ID = P_LINE_ID
AND LEVL.DATE_COMPLETED IS NULL ;
SELECT LINES.ID
FROM OKC_K_LINES_V LINES
WHERE LINES.CLE_ID = P_TOP_LINE_ID
AND LINES.LSE_ID in (9, 25 );
oks_bill_level_elements_pvt.update_row
(p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_letv_tbl => L_LEVEL_ID_TBL_IN,
x_letv_tbl => L_LEVEL_ID_TBL_OUT);
Update oks_level_elements
set date_completed = SYSDATE
where parent_cle_id = p_line_id
and date_completed is null;
END UPDATE_OKS_LEVEL_ELEMENTS ;
SELECT TRUNC(LINE.START_DATE) LINE_START_DATE
,TRUNC(LINE.END_DATE) LINE_END_DATE
,LINE.ID
,LINE.DNZ_CHR_ID
FROM OKC_K_LINES_B LINE
WHERE LINE.ID = P_LINE_ID ;
SELECT BCL.ID ,
TRUNC(BCL.DATE_BILLED_FROM) DATE_BILLED_FROM ,
TRUNC(BCL.DATE_BILLED_TO) DATE_BILLED_TO,
AMOUNT BCL_AMOUNT
FROM OKS_BILL_CONT_LINES BCL
WHERE BCL.CLE_ID = P_LINE_ID ;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_HDR_id;
L_BCL_DATES_UPDATE BOOLEAN ;
L_SUB_LINES_INSERTED NUMBER ;
L_BCL_DATES_UPDATE := TRUE ;
L_BCL_DATES_UPDATE := FALSE ;
OKS_BILLCONTLINE_PUB.INSERT_BILL_CONT_LINE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_CNT,
X_MSG_DATA => L_MSG_DATA,
P_BCLV_REC => L_BCLV_REC_IN,
X_BCLV_REC => L_BCLV_REC_OUT
);
X_SUB_LINES_INSERTED => L_SUB_LINES_INSERTED,
X_TOTAL_AMOUNT => L_TOTAL_AMOUNT) ;
IF L_SUB_LINES_INSERTED > 0 OR L_BCL_DATES_UPDATE THEN
IF L_SUB_LINES_INSERTED > 0 THEN
L_BCLV_REC_UPD_IN.ID := L_BCL_ID;
IF L_BCL_DATES_UPDATE THEN
L_BCLV_REC_UPD_IN.DATE_BILLED_FROM :=L_GET_OKS_LINES_REC.LINE_START_DATE;
OKS_BILLCONTLINE_PUB.UPDATE_BILL_CONT_LINE
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_CNT,
X_MSG_DATA => L_MSG_DATA,
P_BCLV_REC => L_BCLV_REC_UPD_IN,
X_BCLV_REC => L_BCLV_REC_UPD_OUT
);
UPDATE_OKS_LEVEL_ELEMENTS(L_GET_OKS_LINES_REC.id ,
X_RETURN_STATUS );
X_SUB_LINES_INSERTED OUT NOCOPY NUMBER ,
X_TOTAL_AMOUNT OUT NOCOPY NUMBER ) IS
CURSOR L_GET_COVERED_LEVELS_CSR (P_LINE_ID NUMBER ) IS
SELECT LINES.ID ,
LINES.START_DATE COVERED_LEVEL_START_DATE,
LINES.END_DATE COVERED_LEVEL_END_DATE,
LINES.PRICE_NEGOTIATED
FROM OKC_K_LINES_B LINES
WHERE LINES.CLE_ID = P_LINE_ID
AND LINES.LSE_ID in (9, 25) ;
SELECT uom_code
FROM okc_k_items
WHERE cle_id = p_cp_id;
SELECT 1
FROM OKS_BILL_SUB_LINES
WHERE CLE_ID = P_ID ;
SELECT lse_id,start_date,end_date
FROM okc_k_lines_b
WHERE id=k_line_id;
SELECT LINES.AMOUNT
FROM OKS_LEVEL_ELEMENTS LINES
WHERE LINES.CLE_ID = L_LINE_ID;
L_SUB_LINES_INSERTED NUMBER := 0;
OKS_BILLSUBLINE_PUB.INSERT_BILL_SUBLINE_PUB
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_CNT,
X_MSG_DATA => L_MSG_DATA,
P_BSLV_REC => L_BSLV_REC_IN,
X_BSLV_REC => L_BSLV_REC_OUT
);
OKS_BSL_det_PUB.insert_bsl_det_Pub
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_RETURN_STATUS,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
p_bsdv_rec => l_bsdv_rec_in,
x_bsdv_rec => l_bsdv_rec_out);
X_SUB_LINES_INSERTED :=1;
OKS_BILLSUBLINE_PUB.INSERT_BILL_SUBLINE_PUB
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_CNT,
X_MSG_DATA => L_MSG_DATA,
P_BSLV_REC => L_BSLV_REC_IN,
X_BSLV_REC => L_BSLV_REC_OUT
);
L_SUB_LINES_INSERTED := L_SUB_LINES_INSERTED + 1 ;
OKS_BSL_det_PUB.insert_bsl_det_Pub
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_RETURN_STATUS,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
p_bsdv_rec => l_bsdv_rec_in,
x_bsdv_rec => l_bsdv_rec_out);
X_SUB_LINES_INSERTED := L_SUB_LINES_INSERTED ;
SELECT TRUNC(LINE.START_DATE) LINE_START_DATE
,TRUNC(LINE.END_DATE) LINE_END_DATE
,LINE.ID
,LINE.DNZ_CHR_ID
,LINE.LSE_ID
FROM OKC_K_LINES_B LINE
WHERE LINE.ID = P_LINE_ID ;
SELECT BCL.ID ,
TRUNC(BCL.DATE_BILLED_FROM) DATE_BILLED_FROM ,
TRUNC(BCL.DATE_BILLED_TO) DATE_BILLED_TO,
AMOUNT BCL_AMOUNT
FROM OKS_BILL_CONT_LINES BCL
WHERE BCL.CLE_ID = P_LINE_ID ;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_HDR_id;
L_BCL_DATES_UPDATE BOOLEAN ;
L_SUB_LINES_INSERTED NUMBER ;
L_BCL_DATES_UPDATE := TRUE ;
L_BCL_DATES_UPDATE := FALSE ;
OKS_BILLCONTLINE_PUB.INSERT_BILL_CONT_LINE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_CNT,
X_MSG_DATA => L_MSG_DATA,
P_BCLV_REC => L_BCLV_REC_IN,
X_BCLV_REC => L_BCLV_REC_OUT
);
X_SUB_LINES_INSERTED => L_SUB_LINES_INSERTED,
X_TOTAL_AMOUNT => L_TOTAL_AMOUNT) ;
IF L_SUB_LINES_INSERTED > 0 OR L_BCL_DATES_UPDATE THEN
IF L_SUB_LINES_INSERTED > 0 THEN
L_BCLV_REC_UPD_IN.ID := L_BCL_ID;
IF L_BCL_DATES_UPDATE THEN
L_BCLV_REC_UPD_IN.DATE_BILLED_FROM :=L_GET_OKS_LINES_REC.LINE_START_DATE;
OKS_BILLCONTLINE_PUB.UPDATE_BILL_CONT_LINE
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_CNT,
X_MSG_DATA => L_MSG_DATA,
P_BCLV_REC => L_BCLV_REC_UPD_IN,
X_BCLV_REC => L_BCLV_REC_UPD_OUT
);
Update oks_level_elements
set date_completed = SYSDATE
where parent_cle_id = p_line_id
and date_completed is NULL
AND date_start =L_BCLV_REC_IN.DATE_BILLED_FROM
AND date_end =L_BCLV_REC_IN.DATE_BILLED_TO;
X_SUB_LINES_INSERTED OUT NOCOPY NUMBER,
X_TOTAL_AMOUNT OUT NOCOPY NUMBER ) IS
CURSOR L_GET_COVERED_LEVELS_CSR (P_LINE_ID NUMBER ) IS
SELECT LINES.ID ,
LINES.START_DATE COVERED_LEVEL_START_DATE,
LINES.END_DATE COVERED_LEVEL_END_DATE,
LINES.PRICE_NEGOTIATED
FROM OKC_K_LINES_B LINES
WHERE LINES.CLE_ID = P_LINE_ID
AND LINES.LSE_ID in (9, 25) ;
SELECT LINES.AMOUNT
FROM OKS_LEVEL_ELEMENTS LINES
WHERE LINES.CLE_ID = L_LINE_ID
AND LINES.DATE_START = L_DATE_FROM
AND LINES.DATE_END =P_DATE_TO;
SELECT uom_code
FROM okc_k_items
WHERE cle_id = p_cp_id;
SELECT 1
FROM OKS_BILL_SUB_LINES
WHERE CLE_ID = P_ID ;
SELECT lse_id
FROM OKC_K_LINES_B
WHERE id=P_LINE_ID;
L_SUB_LINES_INSERTED NUMBER := 0;
OKS_BILLSUBLINE_PUB.INSERT_BILL_SUBLINE_PUB
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_CNT,
X_MSG_DATA => L_MSG_DATA,
P_BSLV_REC => L_BSLV_REC_IN,
X_BSLV_REC => L_BSLV_REC_OUT
);
OKS_BSL_det_PUB.insert_bsl_det_Pub
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_RETURN_STATUS,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
p_bsdv_rec => l_bsdv_rec_in,
x_bsdv_rec => l_bsdv_rec_out);
X_SUB_LINES_INSERTED :=1;
OKS_BILLSUBLINE_PUB.INSERT_BILL_SUBLINE_PUB
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_CNT,
X_MSG_DATA => L_MSG_DATA,
P_BSLV_REC => L_BSLV_REC_IN,
X_BSLV_REC => L_BSLV_REC_OUT
);
L_SUB_LINES_INSERTED := L_SUB_LINES_INSERTED + 1 ;
OKS_BSL_det_PUB.insert_bsl_det_Pub
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_RETURN_STATUS,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
p_bsdv_rec => l_bsdv_rec_in,
x_bsdv_rec => l_bsdv_rec_out);
X_SUB_LINES_INSERTED := L_SUB_LINES_INSERTED ;
'Create_Contract :- CREATE_BSL_FOR_OM_OKS_SUBSCRIPTION L_SUB_LINES_INSERTED'
|| L_SUB_LINES_INSERTED
);
SELECT nvl(price_negotiated,0) amt
FROM okc_k_lines_b
WHERE id = p_line_id ;
SELECT nvl(SUM(AMOUNT),0) tot_amt
FROM OKS_BILL_CONT_LINES
WHERE cle_id = p_line_id ;
SELECT SUM(QUANTITY) qty
FROM OKS_SUBSCR_ELEMENTS
WHERE dnz_cle_id = p_line_id ;
SELECT item_type
FROM OKS_SUBSCR_HEADER_B
WHERE cle_id = p_line_id;
Select Trunc(Max(date_billed_to))
From oks_bill_cont_lines
Where cle_id In
( Select id
From okc_k_lines_b
Where dnz_chr_id = p_id
And lse_id In(1,12,14,19,46)
);
Select Trunc(Max(date_billed_to))
From oks_bill_cont_lines
Where cle_id = p_id;
Select Trunc(Max(date_billed_to))
From oks_bill_sub_lines
Where cle_id = p_id;
select nvl(allow_sales_credit_flag,'N') sc_flag
FROM ra_batch_sources_All
WHERE name = 'OKS_CONTRACTS'
AND org_id = p_org_id;
SELECT count(id)
FROM oks_level_elements
WHERE dnz_chr_id = p_header_id
AND date_completed IS NOT NULL;
SELECT id, cle_id, date_billed_from, date_billed_to,
bcl_id, amount, average, date_to_interface,
attribute_category,attribute1,attribute2,attribute3,attribute4 ,
attribute5,attribute6,attribute7,attribute8,attribute9,
attribute10,attribute11,attribute12,attribute13,attribute14,attribute15
FROM oks_bill_sub_lines
WHERE cle_id = p_old_cp_id
ORDER BY date_billed_from;
SELECT id, bsl_id, bsl_id_averaged, bsd_id, bsd_id_applied,
unit_of_measure , amcv_yn, result, amount, fixed, actual,
default_default , adjustment_level ,adjustment_minimum,
start_reading, end_reading,ccr_id,cgr_id,
attribute_category,attribute1,attribute2,attribute3,attribute4 ,
attribute5,attribute6,attribute7,attribute8,attribute9,
attribute10,attribute11,attribute12,attribute13,attribute14,attribute15
FROM oks_bill_sub_line_dtls
WHERE bsl_id = p_bsl_id;
SELECT id ,btn_id, bsl_id,bcl_id,
bill_instance_number, trx_line_tax_amount,
trx_date, trx_number, trx_class, split_flag,
attribute_category,attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,attribute9,
attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
trx_amount,cycle_refrence
FROM oks_bill_txn_lines
WHERE bcl_id = p_bcl_id
AND bsl_id = p_old_bsl_id;
OKS_BILLSUBLINE_PUB.INSERT_BILL_SUBLINE_PUB
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA,
P_BSLV_REC => L_BSLV_REC_IN,
X_BSLV_REC => L_BSLV_REC_OUT
);
'oks_billsubline_pub.insert_bill_subline_pub(x_return_status = '||x_return_status
||', bsl id = '|| L_BSLV_REC_OUT.id ||')');
UPDATE oks_bill_sub_lines
SET amount = nvl(amount,0) - nvl(p_new_cp_lvl_tbl(l_index).amount, 0)
WHERE id = l_bsl_rec.id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.update_bsl',
'update_old_bsl_amt id = ' || l_bsl_rec.id
);
l_bsdv_tbl_in.DELETE;
OKS_BSL_det_PUB.insert_bsl_det_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_bsdv_tbl => l_bsdv_tbl_in,
x_bsdv_tbl => l_bsdv_tbl_out
);
'OKS_BSL_det_PUB.insert_bsl_det_Pub(x_return_status = '||x_return_status
||', bsd id = '|| l_bsdv_tbl_out(1).id ||')');
UPDATE oks_bill_sub_line_dtls
set amount = nvl(amount,0) - p_new_cp_lvl_tbl(l_index).amount
where id = l_bsd_rec.id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.update_bsl',
'update_old_bsd_amt id = ' || l_bsd_rec.id
);
l_btlv_tbl_in.DELETE;
OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_btlv_tbl => l_btlv_tbl_in,
x_btlv_tbl => l_btlv_tbl_out
);
'OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub(x_return_status = '||x_return_status
||', btl id = '|| l_btlv_tbl_out(1).id ||')');
UPDATE oks_bill_txn_lines
SET trx_line_tax_amount = l_btl_rec.trx_line_tax_amount - l_btlv_tbl_in(1).trx_line_tax_amount,
trx_amount = NVL(l_btl_rec.trx_amount,0) - NVL(l_btlv_tbl_in(1).trx_amount,0),
trx_line_amount = NVL(l_btl_rec.trx_amount,0) - NVL(l_btlv_tbl_in(1).trx_amount,0),
split_flag = 'P'
WHERE id = l_btl_rec.id;
UPDATE oks_bill_txn_lines
SET trx_line_tax_amount = l_btl_rec.trx_line_tax_amount - l_btlv_tbl_in(1).trx_line_tax_amount,
trx_amount = NVL(l_btl_rec.trx_amount,0) - NVL(l_btlv_tbl_in(1).trx_amount,0),
trx_line_amount = NVL(l_btl_rec.trx_amount,0) - NVL(l_btlv_tbl_in(1).trx_amount,0)
WHERE id = l_btl_rec.id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.update_btl',
'update_old_btl_amt id = ' || l_btl_rec.id
);
UPDATE oks_level_elements
SET date_completed = SYSDATE
WHERE TRUNC(date_start) <= TRUNC(l_max_billed_dt)
AND cle_id =p_new_cp_id ;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.update_lvl_new',
'update date_completed of new cp level elements up to date = ' || l_max_billed_dt
);
SELECT id , TRUNC(end_date) end_dt,
price_negotiated cp_amt
FROM okc_k_lines_b
WHERE cle_id = p_top_line_id
AND lse_id in (35,7,8,9,10,11,13,18,25)
AND date_terminated IS NULL;
SELECT max(bsl.date_billed_to) max_billed_to , nvl(SUM(bsl.amount),0) bill_amt
FROM oks_bill_sub_lines bsl, oks_bill_cont_lines bcl
WHERE bsl.cle_id = p_cp_id
AND bsl.bcl_id = bcl.id
AND bcl.bill_action = 'RI';
SELECT nvl(SUM(price_negotiated),0) tot_amt
FROM okc_k_lines_b
where cle_id = p_top_line_id
and lse_id in (35,7,8,9,10,11,13,18,25);
SELECT nvl(SUM(price_negotiated),0) tot_amount
FROM okc_k_lines_b
where dnz_chr_id = p_dnz_chr_id
and lse_id in (35,7,8,9,10,11,13,18,25,46);
l_subline_update NUMBER;
l_subline_update := 0;
UPDATE okc_k_lines_b SET price_negotiated = l_bsl_rec.bill_amt
WHERE id = l_SubLine_rec.id;
l_subline_update := l_subline_update + 1;
END IF; ---update decision chk
UPDATE okc_k_lines_b SET price_negotiated = l_bsl_rec.bill_amt
WHERE id = p_sub_line_id;
l_subline_update := l_subline_update + 1;
END IF; ---update decision chk
IF l_subline_update > 0 THEN ---sub line updated
OPEN l_top_line_Amt_csr;
UPDATE okc_k_lines_b SET price_negotiated = l_top_line_amt
WHERE id = p_top_line_id;
UPDATE okc_k_headers_b SET estimated_amount = l_hdr_amt
WHERE id = p_dnz_chr_id;