[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_CONTRACT_DATA(
p_contract_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
DELETE FROM oks_headers_temp
WHERE chr_id = p_contract_id;
DELETE FROM oks_lines_temp
WHERE dnz_chr_id = p_contract_id;
DELETE FROM oks_sublines_temp
WHERE dnz_chr_id = p_contract_id;
END DELETE_CONTRACT_DATA;
l_mass_update_status_tbl oks_mass_upd_pvt.mass_update_status_tbl;
select ste_code from okc_statuses_b where code = p_sts_code;
select S.STE_CODE STE_CODE1
from okc_statuses_v S, fnd_lookups ST
where S.STE_CODE in (NVL(p_old_ste_code, 'ENTERED'),
decode(p_old_ste_code,
NULL,
'CANCELLED',
'ENTERED',
'CANCELLED',
'ACTIVE',
'HOLD',
'SIGNED',
'HOLD'
))
and sysdate between s.start_date and nvl(s.end_date, sysdate)
and st.lookup_type = 'OKC_STATUS_TYPE'
and st.lookup_code = s.ste_code
and sysdate between st.start_date_active and
nvl(st.end_date_active, sysdate)
and ST.enabled_flag = 'Y'
and S.code not like 'QA%HOLD'
AND p_old_ste_code <> 'CANCELLED';
SELECT * BULK COLLECT
INTO l_mass_update_status_tbl
FROM (SELECT ID line_id,
To_Number(NULL) subline_id,
dnz_chr_id,
lse_id,
line_number,
reason_code,
status,
new_status,
comments
FROM oks_lines_temp
WHERE status_change_flag = 'Y'
AND dnz_chr_id = To_Number(p_contract_id)
UNION ALL
SELECT cle_id line_id,
id subline_id,
dnz_chr_id,
lse_id,
line_number,
reason_code,
status,
new_status,
comments
FROM oks_sublines_temp
WHERE status_change_flag = 'Y'
AND dnz_chr_id = to_number(p_contract_id));
IF l_mass_update_status_tbl.Count > 0 THEN
for i in l_mass_update_status_tbl.first .. l_mass_update_status_tbl.last loop
open get_stecode(l_mass_update_status_tbl(i).old_sts_code);
open get_stecode(l_mass_update_status_tbl(i).new_sts_code);
IF l_mass_update_status_tbl(i).sub_line_id is null then
UPDATE oks_lines_temp
SET status_error_flag = 'Y',
status_err_message = l_new_ste_code ||
' is not a valid status for the existing status type ' ||
l_old_ste_code
WHERE id = l_mass_update_status_tbl(i).line_id;
UPDATE oks_sublines_temp
SET status_error_flag = 'Y',
status_err_message = l_new_ste_code ||
' is not a valid status for the existing status type ' ||
l_old_ste_code
WHERE id = l_mass_update_status_tbl(i).sub_line_id;
IF l_mass_update_status_tbl(i).sub_line_id is null then
UPDATE oks_lines_temp
SET status_error_flag = 'N', error_message = NULL
WHERE id = l_mass_update_status_tbl(i).line_id;
UPDATE oks_sublines_temp
SET status_error_flag = 'N', error_message = NULL
WHERE id = l_mass_update_status_tbl(i).sub_line_id;
l_mass_update_eff_tbl oks_mass_upd_pvt.mass_update_effectivity_tbl;
SELECT * BULK COLLECT
INTO l_mass_update_eff_tbl
FROM (SELECT ID line_id,
To_Number(NULL) sub_line_id,
dnz_chr_id chr_id,
lse_id,
line_number,
start_date,
end_date
FROM oks_lines_temp
WHERE (start_dt_eff_change_flag = 'Y' OR
end_dt_eff_change_flag = 'Y')
AND dnz_chr_id = to_number(p_contract_id)
UNION ALL
SELECT cle_id line_id,
id sub_line_id,
dnz_chr_id chr_id,
lse_id,
line_number,
start_date,
end_date
FROM oks_sublines_temp
WHERE (start_dt_eff_change_flag = 'Y' OR
end_dt_eff_change_flag = 'Y')
AND dnz_chr_id = to_number(p_contract_id));
IF l_mass_update_eff_tbl.Count > 0 THEN
FOR i IN l_mass_update_eff_tbl.first .. l_mass_update_eff_tbl.last LOOP
IF l_mass_update_eff_tbl(i).sub_line_id IS NULL THEN
SELECT start_date, end_date
INTO l_k_start_date, l_k_end_date
FROM okc_k_headers_all_b
where id = l_mass_update_eff_tbl(i).chr_id;
IF (l_mass_update_eff_tbl(i)
.start_date < l_k_start_date OR l_mass_update_eff_tbl(i)
.start_date > l_k_end_date) THEN
UPDATE oks_lines_temp
SET start_dt_error_flag = 'Y',
st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message ||'- Line Start Date is out of Contract''s Effectivity, line_number: ' || l_mass_update_eff_tbl(i).line_number,
'Line Start Date is out of Contract''s Effectivity, line_number: ' || l_mass_update_eff_tbl(i).line_number)
WHERE id = l_mass_update_eff_tbl(i).line_id;
IF (l_mass_update_eff_tbl(i)
.end_date < l_k_start_date OR l_mass_update_eff_tbl(i)
.end_date > l_k_end_date) THEN
UPDATE oks_lines_temp
SET end_dt_error_flag = 'Y',
end_dt_err_message = Nvl2(end_dt_err_message, end_dt_err_message || ' - Line End Date is out of Contract''s Effectivity, line_number: '|| l_mass_update_eff_tbl(i).line_number,
'Line End Date is out of Contract''s Effectivity, line_number: '|| l_mass_update_eff_tbl(i).line_number)
WHERE id = l_mass_update_eff_tbl(i).line_id;
SELECT l.START_DATE, l.END_DATE
INTO l_kl_start_date, l_kl_end_date
FROM oks_lines_temp l
WHERE l.id = l_mass_update_eff_tbl(i).line_id;
IF (l_mass_update_eff_tbl(i)
.start_date < l_kl_start_date OR l_mass_update_eff_tbl(i)
.start_date > l_kl_end_date) THEN
UPDATE oks_sublines_temp
SET start_dt_error_flag = 'Y',
st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message || ' - Subline Start Date is out of Contract line''s Effectivity, subline_number: ' || l_mass_update_eff_tbl(i).line_number,
'Subline Start Date is out of Contract line''s Effectivity, subline_number: ' || l_mass_update_eff_tbl(i).line_number )
WHERE id = l_mass_update_eff_tbl(i).sub_line_id;
IF (l_mass_update_eff_tbl(i)
.end_date < l_kl_start_date OR l_mass_update_eff_tbl(i)
.end_date > l_kl_end_date) THEN
UPDATE oks_sublines_temp
SET end_dt_error_flag = 'Y',
end_dt_err_message = Nvl2(end_dt_err_message , end_dt_err_message || ' - Subline End Date is out of Contract line''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number,
'Subline End Date is out of Contract line''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number)
WHERE id = l_mass_update_eff_tbl(i).sub_line_id;
SELECT start_date, end_date
INTO l_k_start_date, l_k_end_date
FROM okc_k_headers_all_b
WHERE id = l_mass_update_eff_tbl(i).chr_id;
IF (l_mass_update_eff_tbl(i)
.start_date < l_k_start_date OR l_mass_update_eff_tbl(i)
.start_date > l_k_end_date) THEN
UPDATE oks_sublines_temp
SET start_dt_error_flag = 'Y',
st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message ||' - Subline Start Date is out of Contract''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number,
'Subline Start Date is out of Contract''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number)
WHERE id = l_mass_update_eff_tbl(i).sub_line_id;
IF (l_mass_update_eff_tbl(i)
.end_date < l_k_start_date OR l_mass_update_eff_tbl(i)
.end_date > l_k_end_date) THEN
UPDATE oks_sublines_temp
SET end_dt_error_flag = 'Y',
end_dt_err_message = Nvl2(end_dt_err_message, end_dt_err_message || ' - Subline End Date is out of Contract''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number,
'Subline End Date is out of Contract''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number)
WHERE id = l_mass_update_eff_tbl(i).sub_line_id;
END IF; --l_mass_update_eff_tbl(i).subline_id
/*validate the mass update data*/
PROCEDURE vld_massupd_data_prc(RETCODE OUT NOCOPY NUMBER,
p_contract_id IN VARCHAR2) IS
l_contract_id VARCHAR2(50) := p_contract_id;
This procedure is a concurrent program, that trigger mass update wrapper for all
the three changes done through mass update UI
*/
PROCEDURE launch_massupd_conc_prog(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_contract_id IN VARCHAR2) IS
l_massupd_return_status VARCHAR2(1);
SELECT Decode(contract_number_modifier, NULL, contract_number, contract_number ||' - '||contract_number_modifier) contract_number
FROM okc_k_headers_all_b
WHERE id = p_contract_id;
l_msg := 'Service Contracts Mass Update Report for Contract '||l_k_n_display;
mass_update_status_wrapper(p_contract_id,l_return_status);
fnd_file.put_line (fnd_file.log,'mass_update_status_wrapper: '||l_overall_status);
mass_update_eff_wrapper(p_contract_id,l_return_status);
fnd_file.put_line (fnd_file.log,'mass_update_eff_wrapper: '||l_overall_status);
mass_update_price_wrapper(p_contract_id,l_return_status);
fnd_file.put_line (fnd_file.log,'mass_update_price_wrapper: '||l_overall_status);
DELETE_CONTRACT_DATA(p_contract_id,l_return_status);
procedure mass_update_price_prc(p_mass_update_price_tbl in mass_update_price_tbl,
x_return_status out nocopy VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER) IS
CURSOR header_details(p_contract_id NUMBER) IS
SELECT authoring_org_id, inv_organization_id
FROM okc_k_headers_all_b
WHERE id = p_contract_id;
SELECT cleb.price_negotiated, kln.tax_amount
FROM okc_k_lines_b cleb, oks_k_lines_B kln
WHERE cleb.id = p_subline_id
AND cleb.id = kln.cle_id;
SELECT SUM(kln.tax_amount)
FROM okc_k_lines_b cle, oks_k_lines_b kln
WHERE cle.dnz_chr_id = p_chr_id
AND cle.id = kln.cle_id
AND cle.lse_id IN (7, 8, 9, 10, 11, 13, 35, 25, 46)
AND cle.date_cancelled IS NULL;
SELECT SUM(tax_amount)
FROM okc_k_lines_b cle, oks_k_lines_b kln
WHERE cle.cle_id = p_cle_id
AND cle.id = kln.cle_id
AND cle.lse_id IN (7, 8, 9, 10, 11, 13, 35, 25, 46)
AND cle.date_cancelled IS NULL;
SELECT id, object_version_number
FROM OKS_K_HEADERS_B
WHERE chr_id = p_chr_id;
SELECT 'Y'
FROM OKC_K_LINES_B
WHERE dnz_chr_id = p_hdr_id
AND id = p_line_id
AND (date_terminated IS NULL OR date_terminated > TRUNC(SYSDATE))
AND date_cancelled IS NULL
AND sts_code NOT IN ('CANCELLED', 'TERMINATED') ;
l_api_name Varchar2(100) := 'Mass_Update_Price_prc';
l_mu_price_tbl OKS_MASS_UPD_PVT.mass_update_price_tbl;
'Entered mass_update_price_prc');
err_mu_price_tbl.DELETE;
l_mu_price_tbl := p_mass_update_price_tbl;
DBMS_TRANSACTION.SAVEPOINT('MASS_UPDATE_PRICE');
'before call to okc_contract_pub.update_contract_line ');
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);
'after call to okc_contract_pub.update_contract_line ' ||
'l_return_status -' || l_return_status);
FND_FILE.PUT_LINE(FND_FILE.Log,'after call to okc_contract_pub.update_contract_line AND l_return_status - ' ||l_return_status);
'before call to oks_contract_line_pub.update_line ');
oks_contract_line_pub.update_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_kln_rec_in,
x_klnv_rec => l_kln_rec_out,
p_validate_yn => 'N');
' after call to oks_contract_line_pub.update_line ' ||
'l_return_status ' || l_return_status);
FND_FILE.PUT_LINE(FND_FILE.Log,' after call to oks_contract_line_pub.update_line AND l_return_status IS ' ||l_return_status);
'before call to oks_contract_hdr_pub.update_header ');
oks_contract_hdr_pub.update_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_tbl => l_khrv_tbl_type_in,
x_khrv_tbl => l_khrv_tbl_type_out,
p_validate_yn => 'N');
' after call to oks_contract_hdr_pub.update_header ' ||
'l_return_status ' || l_return_status);
FND_FILE.PUT_LINE(FND_FILE.Log,' after call to oks_contract_hdr_pub.update_header AND l_return_status IS ' ||l_return_status);
DBMS_TRANSACTION.rollback_savepoint('MASS_UPDATE_PRICE');
'Leaving mass_update_price_prc');
'Leaving mass_update_price_prc because of EXCEPTION: ' ||
sqlerrm);
end mass_update_price_prc;
procedure mass_update_price_wrapper(p_contract_id IN VARCHAR2,
x_return_status out nocopy varchar2) IS
--x_return_status VARCHAR2(1);
l_mass_update_price_tbl oks_mass_upd_pvt.mass_update_price_tbl;
SELECT cle_id, id, dnz_chr_id, lse_id,line_number,price_negotiated BULK COLLECT
INTO l_mass_update_price_tbl
FROM oks_sublines_temp
WHERE price_change_flag = 'Y'
AND dnz_chr_id = To_Number(p_contract_id);
IF l_mass_update_price_tbl.Count > 0 THEN
fnd_file.put_line (fnd_file.Log, 'Subtotal Change: Total number of Lines/Sublines to process: '||l_mass_update_price_tbl.Count);
fnd_file.put_line (fnd_file.output, 'Subtotal Change: Total number of Lines/Sublines to process: '||l_mass_update_price_tbl.Count);
mass_update_price_prc(l_mass_update_price_tbl,
x_return_status,
x_msg_data,
x_msg_count);
procedure mass_update_status_wrapper(p_contract_id IN VARCHAR2,
x_return_status out nocopy varchar2) IS
--x_return_status VARCHAR2(1);
l_mass_update_status_tbl oks_mass_upd_pvt.mass_update_status_tbl;
FOR i IN (SELECT line_number,
status_err_message
FROM oks_lines_temp
WHERE (status_change_flag = 'Y' AND status_error_flag = 'Y')
AND dnz_chr_id = To_Number(p_contract_id)
UNION ALL
SELECT line_number,
status_err_message
FROM oks_sublines_temp
WHERE (status_change_flag = 'Y' AND status_error_flag = 'Y')
AND dnz_chr_id = to_number(p_contract_id)) LOOP
IF l_count = 0 THEN
FND_FILE.PUT_LINE( FND_FILE.output, 'Records rejected during validation') ;
SELECT * BULK COLLECT
INTO l_mass_update_status_tbl
FROM (SELECT ID line_id,
To_Number(NULL) subline_id,
dnz_chr_id,
lse_id,
line_number,
reason_code,
status,
new_status,
comments
FROM oks_lines_temp
WHERE (status_change_flag = 'Y' AND status_error_flag = 'N')
AND dnz_chr_id = To_Number(p_contract_id)
UNION ALL
SELECT cle_id line_id,
id subline_id,
dnz_chr_id,
lse_id,
line_number,
reason_code,
status,
new_status,
comments
FROM oks_sublines_temp
WHERE (status_change_flag = 'Y' AND status_error_flag = 'N')
AND dnz_chr_id = to_number(p_contract_id));
IF l_mass_update_status_tbl.Count > 0 THEN
IF l_count <> 0 THEN
fnd_file.put_line (fnd_file.output, 'Status Change: Total Number of Rejected Lines during validation: ' || l_count);
fnd_file.put_line (fnd_file.Log, 'Status Change: Total Number of Lines/Sublines to process: '||l_mass_update_status_tbl.count);
fnd_file.put_line (fnd_file.output, 'Status Change: Total Number of Lines/Sublines to process: '||l_mass_update_status_tbl.count);
mass_update_status_prc(l_mass_update_status_tbl,
x_return_status,
x_msg_data,
x_msg_count);
procedure mass_update_eff_wrapper(p_contract_id IN VARCHAR2,
x_return_status out nocopy varchar2) IS
--x_return_status VARCHAR2(1);
l_mass_update_eff_tbl oks_mass_upd_pvt.mass_update_effectivity_tbl;
FOR I IN (SELECT line_number,
DECODE(start_dt_error_flag,
'Y',
'Start Date Error: ' || st_dt_err_message,
'') st_dt_err,
DECODE(end_dt_error_flag,
'Y',
'End Date Error: ' || end_dt_err_message,
'') end_dt_err
FROM oks_lines_temp
WHERE ((start_dt_eff_change_flag = 'Y' AND
start_dt_error_flag = 'Y') OR (end_dt_eff_change_flag = 'Y' AND
end_dt_error_flag = 'Y'))
AND dnz_chr_id = to_number(p_contract_id)
UNION ALL
SELECT line_number,
DECODE(start_dt_error_flag,
'Y',
'Start Date Error: ' || st_dt_err_message,
'') st_dt_err,
DECODE(end_dt_error_flag,
'Y',
'End Date Error: ' || end_dt_err_message,
'') end_dt_err
FROM oks_sublines_temp
WHERE ((start_dt_eff_change_flag = 'Y' AND
start_dt_error_flag = 'Y') OR (end_dt_eff_change_flag = 'Y' AND
end_dt_error_flag = 'Y'))
AND dnz_chr_id = to_number(p_contract_id)) LOOP
IF l_count = 0 THEN
FND_FILE.PUT_LINE(FND_FILE.output, 'Records rejected during validation');
SELECT * BULK COLLECT
INTO l_mass_update_eff_tbl
FROM (SELECT ID line_id,
To_Number(NULL) subline_id,
dnz_chr_id,
lse_id,
line_number,
start_date,
end_date
FROM oks_lines_temp
WHERE ((start_dt_eff_change_flag = 'Y' AND
start_dt_error_flag = 'N') OR
(end_dt_eff_change_flag = 'Y' AND end_dt_error_flag = 'N'))
AND dnz_chr_id = to_number(p_contract_id)
UNION ALL
SELECT cle_id line_id,
id subline_id,
dnz_chr_id,
lse_id,
line_number,
start_date,
end_date
FROM oks_sublines_temp lin
WHERE ((start_dt_eff_change_flag = 'Y' AND
start_dt_error_flag = 'N') OR
(end_dt_eff_change_flag = 'Y' AND end_dt_error_flag = 'N'))
AND dnz_chr_id = to_number(p_contract_id));
IF l_mass_update_eff_tbl.Count > 0 THEN
IF l_count <> 0 THEN
FND_FILE.PUT_LINE(FND_FILE.output, ' ');
mass_update_effectivity_prc(l_mass_update_eff_tbl, x_return_status);
procedure mass_update_status_prc(p_mass_update_status_tbl in mass_update_status_tbl,
x_return_status out nocopy VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER) IS
--PRAGMA autonomous_transaction;
l_api_name Varchar2(100) := 'Mass_Update_Status_prc';
p_lines_tbl OKS_MASS_UPD_PVT.mass_update_status_tbl;
SELECT authoring_org_id, inv_organization_id
FROM okc_k_headers_all_b
WHERE id = p_contract_id;
select ste_code from okc_statuses_b where code = p_sts_code;
'Entered Mass_Update_Status_Prc');
/*Set the apps user_id which is useful to update the who columns in OKS tables.
FND_GLOBAL.apps_initialize(p_user_id,p_resp_id,p_pgm_appl_id); */
p_lines_tbl := p_mass_update_status_tbl;
DBMS_TRANSACTION.SAVEPOINT('MASS_UPDATE_STATUS');
OKS_CHANGE_STATUS_PVT.UPDATE_LINE_STATUS(x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_init_msg_list => 'T',
p_id => p_lines_tbl(i)
.chr_id,
p_cle_id => p_lines_tbl(i)
.line_id,
p_new_sts_code => p_lines_tbl(i)
.new_sts_code,
p_canc_reason_code => p_lines_tbl(i)
.canc_reason_code,
p_old_sts_code => p_lines_tbl(i)
.old_sts_code,
p_old_ste_code => l_old_ste_code,
p_new_ste_code => l_new_ste_code,
p_term_cancel_source => 'MANUAL',
p_date_cancelled => sysdate,
p_comments => p_lines_tbl(i)
.comments,
p_validate_status => 'N');
'After Update_Line_Status' || 'l_return_status -' ||
l_return_status);
FND_FILE.PUT_LINE(FND_FILE.LOG,'After Update_Line_Status - l_return_status is ' ||l_return_status);
DBMS_TRANSACTION.rollback_savepoint('MASS_UPDATE_STATUS');
l_success_count := p_mass_update_status_tbl.Count - Nvl(err_mu_status_tbl.Count,0);
FND_FILE.PUT_LINE(FND_FILE.Log,'Return Status in Mass_Update_Status_Prc is '||x_return_status);
'Leaving mass_update_status_prc because of EXCEPTION: ' ||
sqlerrm);
SELECT TRUNC(MIN(BCL.DATE_BILLED_FROM)) DATE_BILLED_FROM,
TRUNC(MAX(BCL.DATE_BILLED_TO)) DATE_BILLED_TO
FROM OKS_BILL_CONT_LINES BCL
WHERE BCL.CLE_ID = P_TOP_LINE_ID;
SELECT TRUNC(START_DATE) START_DATE, TRUNC(END_DATE) END_DATE
FROM okc_k_headers_all_b
WHERE ID = P_HDR_ID;
SELECT TRUNC(MIN(BSL.DATE_BILLED_FROM)) DATE_BILLED_FROM,
TRUNC(MAX(BSL.DATE_BILLED_TO)) DATE_BILLED_TO
FROM OKS_BILL_SUB_LINES BSL
WHERE BSL.CLE_ID = P_SUB_LINE_ID;
SELECT TRUNC(START_DATE) START_DATE, TRUNC(END_DATE) END_DATE
FROM OKC_K_LINES_B
WHERE ID = P_TOP_LINE_ID;
SELECT start_date, end_date
INTO l_k_start_date, l_k_end_date
FROM okc_k_headers_all_b
where id = p_chr_id;
UPDATE oks_lines_temp
SET start_dt_error_flag = 'Y',
st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message ||'- Line Start Date is out of Contract''s Effectivity, line_number: ' || p_line_number,
'Line Start Date is out of Contract''s Effectivity, line number: ' || p_line_number)
WHERE id = p_line_id;
UPDATE oks_lines_temp
SET end_dt_error_flag = 'Y',
end_dt_err_message = Nvl2(end_dt_err_message, end_dt_err_message || ' - Line End Date is out of Contract''s Effectivity, line_number: '|| p_line_number,
'Line End Date is out of Contract''s Effectivity, line number: '|| p_line_number)
WHERE id = p_line_id;
select l.chr_id, l.START_DATE, l.END_DATE
INTO l_chr_id, l_kl_start_date, l_kl_end_date
FROM OKC_K_LINES_B sl, okc_k_lines_b l
WHERE sl.id = p_subline_id
AND sl.cle_id = l.id;
UPDATE oks_sublines_temp
SET start_dt_error_flag = 'Y',
st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message || ' - Subline Start Date is out of Contract line''s Effectivity, subline number: ' || p_line_number,
'Subline Start Date is out of Contract line''s Effectivity, subline number: ' || p_line_number )
WHERE id = p_subline_id;
UPDATE oks_sublines_temp
SET end_dt_error_flag = 'Y',
end_dt_err_message = Nvl2(end_dt_err_message , end_dt_err_message || ' - Subline End Date is out of Contract line''s Effectivity, subline number: ' || p_line_number,
'Subline End Date is out of Contract line''s Effectivity, subline number: ' || p_line_number)
WHERE id = p_subline_id;
SELECT start_date, end_date
INTO l_k_start_date, l_k_end_date
FROM okc_k_headers_all_b
WHERE id = l_chr_id;
UPDATE oks_sublines_temp
SET start_dt_error_flag = 'Y',
st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message ||' - Subline Start Date is out of Contract''s Effectivity, subline number: ' || p_line_number,
'Subline Start Date is out of Contract''s Effectivity, subline number: ' || p_line_number)
WHERE id = p_subline_id;
UPDATE oks_sublines_temp
SET end_dt_error_flag = 'Y',
end_dt_err_message = Nvl2(end_dt_err_message, end_dt_err_message || ' - Subline End Date is out of Contract''s Effectivity, subline number: ' || p_line_number,
'Subline End Date is out of Contract''s Effectivity, subline number: ' || p_line_number)
WHERE id = p_subline_id;
procedure mass_update_effectivity_prc(p_mass_update_effectivity_tbl in mass_update_effectivity_tbl,
x_return_status out nocopy varchar2) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name VARCHAR2(100) := 'oks_mass_upd_pvt.mass_update_effectivity_prc';
Select id From OKC_K_LINES_b Where cle_id = p_line_id;
SELECT 'Y'
FROM OKC_K_LINES_B
WHERE dnz_chr_id = p_hdr_id
AND id = p_line_id
AND (date_terminated IS NULL OR date_terminated > TRUNC(SYSDATE))
AND date_cancelled IS NULL
AND sts_code NOT IN ('CANCELLED', 'TERMINATED')
AND end_date > NVL(oks_bill_util_pub.get_billed_upto(id, p_intent),
end_date - 1);
fnd_file.put_line (fnd_file.output, 'Effectivity Change: ' || ' Total Number of Lines/Sublines to process: ' || p_mass_update_effectivity_tbl.Count);
FOR i IN p_mass_update_effectivity_tbl.FIRST .. p_mass_update_effectivity_tbl.LAST LOOP
IF p_mass_update_effectivity_tbl(i).sub_line_id IS NULL THEN
BEGIN
SELECT sts_code
INTO l_clev_tbl_kl_in(p) .sts_code
FROM okc_k_lines_b
where id = p_mass_update_effectivity_tbl(i).line_id;
l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
' Error while fetching data from okc_k_lines_b, line number: ' || p_mass_update_effectivity_tbl(i).line_number);
OPEN check_eligiblity_line(p_mass_update_effectivity_tbl(i).chr_id,
p_mass_update_effectivity_tbl(i).line_id,
'T');
fnd_file.put_line (fnd_file.log, l_api_name || ' Line number : ' || p_mass_update_effectivity_tbl(i).line_number || 'is eligible');
l_clev_tbl_kl_in(p).id := p_mass_update_effectivity_tbl(i).line_id;
l_clev_tbl_kl_in(p).start_date := p_mass_update_effectivity_tbl(i).START_DATE;
l_clev_tbl_kl_in(p).end_date := p_mass_update_effectivity_tbl(i).end_date;
l_clev_tbl_kl_in(p).lse_id := p_mass_update_effectivity_tbl(i).lse_id;
check_new_line_effectivity(p_mass_update_effectivity_tbl(i).chr_id,
p_mass_update_effectivity_tbl(i).line_id,
p_mass_update_effectivity_tbl(i).line_number,
l_clev_tbl_kl_in(p).start_date,
l_clev_tbl_kl_in(p).end_date,
l_clev_tbl_kl_in(p).exception_yn);
p_cle_id => p_mass_update_effectivity_tbl(i)
.line_id,
p_lse_id => p_mass_update_effectivity_tbl(i)
.lse_id,
p_end_date => null) THEN
fnd_file.put_line (fnd_file.log,
l_api_name ||
' Entering Validate_date for Line number: ' || p_mass_update_effectivity_tbl(i).line_number);
p_hdr_id => p_mass_update_effectivity_tbl(i).chr_id,
p_top_line_id => p_mass_update_effectivity_tbl(i).line_id,
p_sub_line_id => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_flag => l_flag);
' After Validate_date for Line number: ' || p_mass_update_effectivity_tbl(i).line_number);
l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number ;
l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
p_msg_name => 'OKS_BA_UPDATE_NOT_ALLOWED',
p_token1 => 'Line No ',
p_token1_value => l_line_number);
l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
' Line number: ' || p_mass_update_effectivity_tbl(i).line_number ||
', Status would be: ' || l_clev_tbl_kl_in(p).STS_CODE ||
', Start Date and End Date are: ' || l_clev_tbl_kl_in(p).start_date || ' and ' || l_clev_tbl_kl_in(p).end_date);
ELSIF p_mass_update_effectivity_tbl(i).sub_line_id IS NOT NULL THEN
BEGIN
SELECT sts_code
INTO l_clev_tbl_sl_in(q).sts_code
FROM okc_k_lines_b
where id = p_mass_update_effectivity_tbl(i).sub_line_id;
l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
OPEN check_eligiblity_line(p_mass_update_effectivity_tbl(i).chr_id,
p_mass_update_effectivity_tbl(i)
.sub_line_id,
'S');
l_clev_tbl_sl_in(q).id := p_mass_update_effectivity_tbl(i).sub_line_id;
l_clev_tbl_sl_in(q).cle_id := p_mass_update_effectivity_tbl(i).line_id;
l_clev_tbl_sl_in(q).start_date := p_mass_update_effectivity_tbl(i).start_date;
l_clev_tbl_sl_in(q).end_date := p_mass_update_effectivity_tbl(i).end_date;
l_clev_tbl_sl_in(q).lse_id := p_mass_update_effectivity_tbl(i).lse_id;
l_clev_tbl_sl_in(q).CHR_id := p_mass_update_effectivity_tbl(i).chr_id;
check_new_subline_effectivity(p_mass_update_effectivity_tbl(i).chr_id,
p_mass_update_effectivity_tbl(i).line_id,
p_mass_update_effectivity_tbl(i).sub_line_id,
p_mass_update_effectivity_tbl(i).line_number,
l_clev_tbl_sl_in(q).start_date,
l_clev_tbl_sl_in(q).end_date,
l_clev_tbl_sl_in(q).exception_yn);
p_cle_id => p_mass_update_effectivity_tbl(i).line_id,
p_lse_id => p_mass_update_effectivity_tbl(i).lse_id,
p_end_date => null) THEN
fnd_file.put_line (fnd_file.log,
l_api_name ||
' Entering Validate_date for Sub Line Id: ' ||
p_mass_update_effectivity_tbl(i).sub_line_id);
p_top_line_id => p_mass_update_effectivity_tbl(i).line_id,
p_sub_line_id => p_mass_update_effectivity_tbl(i).sub_line_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_flag => l_flag);
l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number ;
l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
p_msg_name => 'OKS_BA_UPDATE_NOT_ALLOWED',
p_token1 => 'Line No ',
p_token1_value => l_line_number);
l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
END IF; --p_mass_update_effectivity_tbl(i).sub_line_id
DBMS_TRANSACTION.SAVEPOINT('MASS_UPDATE_EFFECTIVITY');
fnd_file.put_line (fnd_file.log,l_api_name || ' Before Calling : okc_contract_pub.update_contract_line');
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_restricted_update => 'F',
p_clev_rec => l_clev_tbl_kl_in(j),
X_CLEV_rec => l_clev_tbl_kl_out(j));
fnd_file.put_line (fnd_file.log,l_api_name || ' After Calling : okc_contract_pub.update_contract_line');
l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('MASS_UPDATE_EFFECTIVITY');
' Error from okc_contract_pub.update_contract_line procedure' ||
SQLERRM);
DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('MASS_UPDATE_EFFECTIVITY');
' Before Calling : OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity');
OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity(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 => l_clev_tbl_kl_in(j).cle_id,
p_New_Start_Date => l_clev_tbl_kl_in(j).start_date,
P_NEW_END_DATE => l_clev_tbl_kl_in(j).END_DATE);
fnd_file.put_line (fnd_file.log,l_api_name || ' After Calling : OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity');
fnd_file.put_line (fnd_file.log,l_api_name || ' Before Calling okc_contract_pub.update_contract_line');
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_restricted_update => 'F',
p_clev_rec => l_clev_tbl_sl_in(k),
X_CLEV_rec => l_clev_tbl_sl_out(k));
fnd_file.put_line (fnd_file.log,l_api_name || ' After Calling okc_contract_pub.update_contract_line');
' Exception from okc_contract_pub.update_contract_line, Error is: ' ||
SQLERRM);
END mass_update_effectivity_prc;
SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS NULL
START WITH a.customer_product_id = p_customer_prod_id
CONNECT BY PRIOR a.config_parent_id = a.customer_product_id
AND EXISTS
(SELECT 1
FROM okc_k_items itm
where a.customer_product_id = itm.object1_id1
AND itm.dnz_chr_id = p_contract_id);
SELECT b.customer_product_id
FROM oks_ib_config_v b
WHERE LEVEL = 2
START WITH b.customer_product_id =
(SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS NULL
START WITH a.customer_product_id = p_customer_prod_id
CONNECT BY PRIOR a.config_parent_id = a.customer_product_id)
CONNECT BY b.config_parent_id = PRIOR b.customer_product_id
AND EXISTS
(SELECT 1
FROM okc_k_items itm
where b.customer_product_id = itm.object1_id1
AND itm.dnz_chr_id = p_contract_id);
SELECT b.customer_product_id
FROM oks_ib_config_v b
WHERE LEVEL = 2
START WITH b.customer_product_id =
(SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS NULL
START WITH a.customer_product_id = p_customer_prod_id
CONNECT BY PRIOR a.config_parent_id = a.customer_product_id)
CONNECT BY b.config_parent_id = PRIOR b.customer_product_id
AND EXISTS
(SELECT 1
FROM okc_k_items itm
where b.customer_product_id = itm.object1_id1
AND itm.dnz_chr_id = p_contract_id);
SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS NULL
START WITH a.customer_product_id = p_customer_prod_id
CONNECT BY PRIOR a.config_parent_id = a.customer_product_id
AND EXISTS
(SELECT 1
FROM okc_k_items itm
where a.customer_product_id = itm.object1_id1
AND itm.dnz_chr_id = p_contract_id);
SELECT saved_flag FROM oks_headers_temp WHERE chr_id = p_contract_id;
DELETE_CONTRACT_DATA(p_contract_id,l_return_status);
INSERT INTO oks_headers_temp
(chr_id,chr_id_char,
contract_number,
contract_number_modifier,
contract_start_date,
contract_end_date,
contract_status,
contract_amount,
cascade_selection,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT id, To_Char(id),
contract_number,
contract_number_modifier,
start_date,
end_date,
sts_code,
ESTIMATED_AMOUNT,
'Y',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
FROM okc_k_headers_all_b
WHERE id = p_contract_id;
INSERT INTO oks_lines_temp
(id,id_char,
dnz_chr_id, dnz_chr_id_char,
line_number,
lse_id,
name,
description,
old_status,
undo_status,
old_ste_code,
status,
ste_code,
new_status,
new_ste_code,
start_date,
end_date,
old_start_date,
old_end_date,
Price_negotiated,
undo_start_date,
undo_end_date,
undo_Price_negotiated)
SELECT lin.id, To_Char(lin.id),
lin.dnz_chr_id, To_Char(lin.dnz_chr_id),
line_number,
lse_id,
SI.description NAME,
SI.name DESCRIPTION,
lin.sts_code old_Status,
lin.sts_code undo_Status,
sts.ste_code old_ste_code,
lin.sts_code Status,
sts.ste_code ste_code,
lin.sts_code new_status,
sts.ste_code new_ste_code,
lin.start_date,
lin.end_date,
lin.start_date,
lin.end_date,
Price_negotiated,
lin.start_date,
lin.end_date,
Price_negotiated
FROM okc_k_lines_b lin,
okc_k_items itm,
OKX_SYSTEM_ITEMS_V SI,
okc_statuses_b sts
WHERE lin.id = itm.cle_id
AND itm.object1_id1 = SI.id1
AND SI.organization_id = itm.object1_id2
AND lin.sts_code = sts.code
AND lin.lse_id IN (1, 12, 14, 19)
AND lin.dnz_chr_id = p_contract_id
ORDER BY To_Number(lin.line_number);
INSERT INTO oks_sublines_temp
(id,
cle_id,
dnz_chr_id,
id_char,
cle_id_char,
dnz_chr_id_char,
lse_id,
line_number,
customer_product_id,
name,
description,
serial_number,
system_name,
old_status,
undo_status,
old_ste_code,
status,
ste_code,
new_status,
new_ste_code,
reason_code,
start_date,
end_date,
old_start_date,
old_end_date,
old_price_negotiated,
undo_start_date,
undo_end_date,
undo_price_negotiated,
price_negotiated,
top_lvl_parent)
SELECT lin.id,
lin.cle_id,
lin.dnz_chr_id,
To_Char(lin.id),
To_Char(lin.cle_id),
To_Char(lin.dnz_chr_id),
lin.lse_id,
(SELECT line_number
FROM okc_k_lines_b line
WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
cs_item.customer_product_id,
SI.name,
SI.description,
cs_item.serial_number,
CSTL.NAME system_name,
lin.sts_code old_Status,
lin.sts_code undo_Status,
sts.ste_code old_ste_code,
lin.sts_code Status,
sts.ste_code ste_code,
lin.sts_code new_status,
sts.ste_code new_ste_code,
null,
lin.start_date,
lin.end_date,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.price_negotiated,
DECODE((SELECT a1.customer_product_id
FROM oks_ib_config_v a1
WHERE a1.config_parent_id IS NULL
START WITH a1.customer_product_id =
CS_ITEM.CUSTOMER_PRODUCT_ID
CONNECT BY PRIOR a1.config_parent_id = a1.customer_product_id
AND EXISTS
(SELECT 1
FROM okc_k_items itm1
WHERE a1.customer_product_id = itm1.object1_id1
AND itm1.dnz_chr_id = ITM.DNZ_CHR_ID)),
CS_ITEM.CUSTOMER_PRODUCT_ID,
-99,
NULL,
DECODE((SELECT b1.customer_product_id
FROM oks_ib_config_v b1
WHERE LEVEL = 2
START WITH b1.customer_product_id =
(SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS NULL
START WITH a.customer_product_id =
CS_ITEM.CUSTOMER_PRODUCT_ID
CONNECT BY PRIOR a.config_parent_id =
a.customer_product_id)
CONNECT BY b1.config_parent_id = PRIOR b1.customer_product_id
AND EXISTS
(SELECT 1
FROM okc_k_items itm1
where b1.customer_product_id =
itm1.object1_id1
AND itm1.dnz_chr_id = ITM.DNZ_CHR_ID)
AND ROWNUM = 1)
,
CS_ITEM.CUSTOMER_PRODUCT_ID,
-99,
NULL,
(SELECT A.CUSTOMER_PRODUCT_ID
FROM OKS_IB_CONFIG_V A
WHERE A.CONFIG_PARENT_ID IS NULL
START WITH A.CUSTOMER_PRODUCT_ID =
CS_ITEM.CUSTOMER_PRODUCT_ID
CONNECT BY A.CUSTOMER_PRODUCT_ID = PRIOR
A.CONFIG_PARENT_ID),
(SELECT b1.customer_product_id
FROM oks_ib_config_v b1
WHERE LEVEL = 2
START WITH b1.customer_product_id =
(SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS NULL
START WITH a.customer_product_id =
CS_ITEM.CUSTOMER_PRODUCT_ID
CONNECT BY PRIOR a.config_parent_id =
a.customer_product_id)
CONNECT BY b1.config_parent_id = PRIOR
b1.customer_product_id
AND EXISTS
(SELECT 1
FROM okc_k_items itm1
where b1.customer_product_id =
itm1.object1_id1
AND itm1.dnz_chr_id = ITM.DNZ_CHR_ID)
AND ROWNUM = 1)),
(SELECT a1.customer_product_id
FROM oks_ib_config_v a1
WHERE a1.config_parent_id IS NULL
START WITH a1.customer_product_id =
CS_ITEM.CUSTOMER_PRODUCT_ID
CONNECT BY PRIOR a1.config_parent_id = a1.customer_product_id
AND EXISTS
(SELECT 1
FROM okc_k_items itm1
WHERE a1.customer_product_id = itm1.object1_id1
AND itm1.dnz_chr_id = ITM.DNZ_CHR_ID)
AND ROWNUM = 1)) TOP_LVL_PARENT
/*commented and modified above for bug14470110
CASE
WHEN oks_model_item_check(itm.dnz_chr_id,
cs_item.customer_product_id) = 'Y' THEN
-99
WHEN (oks_model_check(itm.dnz_chr_id,
cs_item.customer_product_id) = 'N' AND
oks_config_item_check(itm.dnz_chr_id,
cs_item.customer_product_id) = 'Y') THEN
-99
ELSE
(CASE
WHEN oks_model_check(itm.dnz_chr_id,
cs_item.customer_product_id) = 'Y' THEN
(SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS null
START WITH a.customer_product_id =
cs_item.customer_product_id
CONNECT BY a.customer_product_id = PRIOR a.config_parent_id)
WHEN oks_config_check(itm.dnz_chr_id,
cs_item.customer_product_id) = 'Y' THEN
(SELECT b.customer_product_id
FROM oks_ib_config_v b
WHERE LEVEL = 2
START WITH b.customer_product_id =
(SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS NULL
START WITH a.customer_product_id =
cs_item.customer_product_id
CONNECT BY PRIOR a.config_parent_id =
a.customer_product_id)
CONNECT BY b.config_parent_id = PRIOR b.customer_product_id
AND EXISTS (SELECT 1 FROM okc_k_items
WHERE object1_id1 = cs_item.customer_product_id
AND cle_id = itm.cle_id)
AND ROWNUM = 1)
ELSE
(SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS null
START WITH a.customer_product_id =
cs_item.customer_product_id
CONNECT BY a.customer_product_id = PRIOR a.config_parent_id)
END) END top_lvl_parent*/
/*CASE
WHEN cs_item.config_parent_id IS NULL THEN
-99
ELSE
(SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS null
START WITH a.customer_product_id =
cs_item.customer_product_id
CONNECT BY a.customer_product_id = PRIOR
a.config_parent_id)
END top_lvl_parent,*/
FROM okc_k_items itm,
okc_k_lines_b lin,
OKX_SYSTEM_ITEMS_V SI,
oks_ib_config_v cs_item,
okc_statuses_b sts,
CSI_SYSTEMS_TL CSTL
WHERE itm.object1_id1 = cs_item.customer_product_id
AND cs_item.system_id = cstl.system_id(+)
AND CSTL.LANGUAGE(+) = UserEnv('LANG')
AND lin.id = itm.cle_id
AND lin.dnz_chr_id = p_contract_id
AND SI.id1 = cs_item.INVENTORY_ITEM_ID
AND SI.ORGANIZATION_ID = cs_item.inv_master_organization_id
AND lin.sts_code = sts.code
AND lin.lse_id NOT IN (1, 7, 8, 11, 35, 10, 12, 14, 19)
UNION
SELECT lin.id,
lin.cle_id,
lin.dnz_chr_id,
To_Char(lin.id),
To_Char(lin.cle_id),
To_Char(lin.dnz_chr_id),
lin.lse_id,
(SELECT line_number
FROM okc_k_lines_b line
WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
To_Number(NULL) customer_product_id,
SI.name,
SI.description,
To_char(NULL) serial_number,
To_Char(NULL) system_name,
lin.sts_code old_Status,
lin.sts_code undo_Status,
sts.ste_code old_ste_code,
lin.sts_code Status,
sts.ste_code ste_code,
lin.sts_code new_status,
sts.ste_code new_ste_code,
null,
lin.start_date,
lin.end_date,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.price_negotiated,
-99 "top_lvl_parent"
FROM okc_k_items itm,
okc_k_lines_b lin,
OKX_SYSTEM_ITEMS_V SI,
okc_statuses_b sts
WHERE lin.id = itm.cle_id
AND lin.dnz_chr_id = p_contract_id
AND itm.object1_id1 = SI.id1
AND SI.id2 = itm.object1_id2
AND lin.sts_code = sts.code
AND lin.lse_id = 7
UNION
SELECT lin.id,
lin.cle_id,
lin.dnz_chr_id,
To_Char(lin.id),
To_Char(lin.cle_id),
To_Char(lin.dnz_chr_id),
lin.lse_id,
(SELECT line_number
FROM okc_k_lines_b line
WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
To_Number(NULL) customer_product_id,
SI.name,
SI.description,
To_char(NULL) serial_number,
To_Char(NULL) system_name,
lin.sts_code old_Status,
lin.sts_code undo_Status,
sts.ste_code old_ste_code,
lin.sts_code Status,
sts.ste_code ste_code,
lin.sts_code new_status,
sts.ste_code new_ste_code,
null,
lin.start_date,
lin.end_date,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.price_negotiated,
-99 "top_lvl_parent"
FROM okc_k_items itm,
okc_k_lines_b lin,
okx_parties_v SI,
okc_statuses_b sts
WHERE lin.id = itm.cle_id
AND lin.dnz_chr_id = p_contract_id
AND itm.object1_id1 = SI.id1
AND SI.id2 = '#'
AND lin.sts_code = sts.code
AND lin.lse_id = 8
UNION
SELECT lin.id,
lin.cle_id,
lin.dnz_chr_id,
To_Char(lin.id),
To_Char(lin.cle_id),
To_Char(lin.dnz_chr_id),
lin.lse_id,
(SELECT line_number
FROM okc_k_lines_b line
WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
To_Number(NULL) customer_product_id,
SI.party_site_number || '-' || NVL(SI.name, ' '),
SI.description,
To_char(NULL) serial_number,
To_Char(NULL) system_name,
lin.sts_code old_Status,
lin.sts_code undo_Status,
sts.ste_code old_ste_code,
lin.sts_code Status,
sts.ste_code ste_code,
lin.sts_code new_status,
sts.ste_code new_ste_code,
null,
lin.start_date,
lin.end_date,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.price_negotiated,
-99 "top_lvl_parent"
FROM okc_k_items itm,
okc_k_lines_b lin,
okx_party_sites_v SI,
okc_statuses_b sts
WHERE lin.id = itm.cle_id
AND lin.dnz_chr_id = p_contract_id
AND itm.object1_id1 = SI.id1
AND SI.id2 = '#'
AND lin.sts_code = sts.code
AND lin.lse_id = 10
UNION
SELECT lin.id,
lin.cle_id,
lin.dnz_chr_id,
To_Char(lin.id),
To_Char(lin.cle_id),
To_Char(lin.dnz_chr_id),
lin.lse_id,
(SELECT line_number
FROM okc_k_lines_b line
WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
To_Number(NULL) customer_product_id,
SI.name,
SI.description,
To_char(NULL) serial_number,
cstl.name system_name,
lin.sts_code old_Status,
lin.sts_code undo_Status,
sts.ste_code old_ste_code,
lin.sts_code Status,
sts.ste_code ste_code,
lin.sts_code new_status,
sts.ste_code new_ste_code,
null,
lin.start_date,
lin.end_date,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.price_negotiated,
-99 "top_lvl_parent"
FROM okc_k_items itm,
okc_k_lines_b lin,
okx_systems_v SI,
okc_statuses_b sts,
CSI_SYSTEMS_TL CSTL
WHERE lin.id = itm.cle_id
AND lin.dnz_chr_id = p_contract_id
AND itm.object1_id1 = SI.id1
AND SI.id2 = '#'
AND lin.sts_code = sts.code
AND lin.lse_id = 11
AND si.id1 = cstl.system_id
AND CSTL.LANGUAGE = UserEnv('LANG')
UNION
SELECT lin.id,
lin.cle_id,
lin.dnz_chr_id,
To_Char(lin.id),
To_Char(lin.cle_id),
To_Char(lin.dnz_chr_id),
lin.lse_id,
(SELECT line_number
FROM okc_k_lines_b line
WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
To_Number(NULL) customer_product_id,
DECODE(c.account_name, NULL, p.party_name, c.account_name) name,
c.account_number description,
To_char(NULL) serial_number,
To_Char(NULL) system_name,
lin.sts_code old_Status,
lin.sts_code undo_Status,
sts.ste_code old_ste_code,
lin.sts_code Status,
sts.ste_code ste_code,
lin.sts_code new_status,
sts.ste_code new_ste_code,
null,
lin.start_date,
lin.end_date,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.start_date,
lin.end_date,
lin.price_negotiated,
lin.price_negotiated,
-99 "top_lvl_parent"
FROM okc_k_items itm,
okc_k_lines_b lin,
hz_cust_accounts c,
hz_parties p,
okc_statuses_b sts
WHERE p.party_id = c.party_id
AND c.cust_account_id = itm.object1_id1
AND lin.id = itm.cle_id
AND lin.dnz_chr_id = p_contract_id
AND lin.sts_code = sts.code
AND lin.lse_id = 35
ORDER BY line_number;
/*SELECT ROWNUM, CASE
WHEN cs_item.config_parent_id IS NULL THEN
'Star Item'
ELSE
(SELECT To_Char(a.customer_product_id)
FROM oks_ib_config_v a
WHERE a.config_parent_id IS null
START WITH a.customer_product_id =
cs_item.customer_product_id
CONNECT BY a.customer_product_id = PRIOR a.config_parent_id)
END config_parent_id,
lin.id,
lin.cle_id,
lin.dnz_chr_id,
(SELECT line_number
FROM okc_k_lines_b line
WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
cs_item.customer_product_id,
SI.name,
SI.description,
cs_item.serial_number,
lin.sts_code Status,
sts.ste_code ste_code,
lin.sts_code new_status,
sts.ste_code new_ste_code,
null,
lin.start_date,
lin.end_date,
lin.price_negotiated,
CASE
WHEN cs_item.config_parent_id IS NULL THEN
-99
ELSE
(SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS null
START WITH a.customer_product_id =
cs_item.customer_product_id
CONNECT BY a.customer_product_id = PRIOR a.config_parent_id)
END top_lvl_parent,
'N',
'N'
FROM okc_k_items itm,
okc_k_lines_b lin,
OKX_SYSTEM_ITEMS_V SI,
oks_ib_config_v cs_item,
okc_statuses_b sts
WHERE itm.object1_id1 = cs_item.customer_product_id
AND lin.id = itm.cle_id
AND lin.dnz_chr_id = p_contract_id
AND SI.id1 = cs_item.INVENTORY_ITEM_ID
AND SI.ORGANIZATION_ID = cs_item.inv_master_organization_id
AND lin.sts_code = sts.code
ORDER BY To_Number((SELECT line_number
FROM okc_k_lines_b line
WHERE line.id = lin.cle_id)),To_Number(lin.line_number); */
UPDATE oks_sublines_temp
SET top_lvl_parent = -99
WHERE top_lvl_parent <> -99
AND dnz_chr_id = p_contract_id
AND top_lvl_parent NOT IN
(SELECT customer_product_id
FROM oks_sublines_temp
WHERE dnz_chr_id = p_contract_id
AND Nvl(top_lvl_parent, -99) = -99
AND customer_product_id IS NOT NULL);
PROCEDURE MASS_UPDATE_CASCADE_SEL(p_contract_id NUMBER, x_error_records OUT NOCOPY VARCHAR2 ) IS
CURSOR c_get_hdr_attrs IS
SELECT cascade_selection,
global_start_date,
global_end_date,
global_price_negotiated,
global_status,
(SELECT ste_code FROM okc_statuses_v WHERE code = global_status AND ROWNUM=1) global_status_code,
global_reason_code,
global_comments
FROM oks_headers_temp
WHERE chr_id = p_contract_id;
SELECT id, start_date, end_date, new_status, reason_code, comments, old_start_date, old_end_date, old_status
FROM oks_lines_temp
WHERE dnz_chr_id = p_contract_id
AND (Nvl(select_flag,'N') = 'Y' OR Nvl(cascade_flag,'N') = 'Y');
SELECT id,
customer_product_id,
start_date,
end_date,
price_negotiated,
new_status,
reason_code,
comments,
old_start_date,
old_end_date,
old_status,
old_price_negotiated
FROM oks_sublines_temp sublines
WHERE dnz_chr_id = p_contract_id
AND top_lvl_parent = -99
AND (
Nvl(select_flag,'N') = 'Y'
OR Nvl(cascade_flag,'N') = 'Y'
OR EXISTS (SELECT 1 FROM oks_lines_temp lines
WHERE lines.id = sublines.cle_id
AND lines.dnz_chr_id = sublines.dnz_chr_id
AND Nvl(lines.cascade_flag,'N') = 'Y' )
);
SELECT id,
customer_product_id,
start_date,
end_date,
price_negotiated,
new_status,
reason_code,
comments,
old_start_date,
old_end_date,
old_status,
old_price_negotiated
FROM oks_sublines_temp sublineschild
WHERE dnz_chr_id = p_contract_id
AND top_lvl_parent <> -99
AND (
Nvl(select_flag,'N') = 'Y'
OR EXISTS (SELECT 1 FROM oks_lines_temp lines
WHERE lines.id = sublineschild.cle_id
AND lines.dnz_chr_id = sublineschild.dnz_chr_id
AND Nvl(lines.cascade_flag,'N') = 'Y' )
OR EXISTS (SELECT 1 FROM oks_sublines_temp sublines
WHERE sublines.customer_product_id = sublineschild.top_lvl_parent
AND sublines.dnz_chr_id = sublineschild.dnz_chr_id
AND sublines.cle_id = sublineschild.cle_id
AND Nvl(sublines.cascade_flag,'N') = 'Y' )
);
WITH a AS (select Nvl(PRECISION,2) prc
from FND_CURRENCIES c
where currency_code = (SELECT currency_code
FROM okc_k_headers_all_b WHERE ID = p_contract_id AND rownum=1))
SELECT tmp.id,
Decode(tmp.start_date, lin.start_date, 'N', 'Y') start_date_change,
Decode(tmp.end_date, lin.end_date, 'N', 'Y') end_date_change,
Decode(Round(tmp.price_negotiated,(SELECT prc FROM a)), Round(lin.price_negotiated,(SELECT prc FROM a)), 'N', 'Y') Price_change,
Decode(tmp.new_status,
(SELECT sts.code
FROM okc_statuses_b sts
WHERE sts.code = lin.sts_code),
'N',
'Y') status_change
FROM oks_sublines_temp tmp, okc_k_lines_b lin
WHERE tmp.id = lin.id
AND tmp.dnz_chr_id = p_contract_id;
SELECT tmp.id,
Decode(tmp.start_date, lin.start_date, 'N', 'Y') start_date_change,
Decode(tmp.end_date, lin.end_date, 'N', 'Y') end_date_change,
Decode(tmp.new_status,
(SELECT sts.code
FROM okc_statuses_b sts
WHERE sts.code = lin.sts_code),
'N',
'Y') status_change
FROM oks_lines_temp tmp, okc_k_lines_b lin
WHERE tmp.id = lin.id
AND tmp.dnz_chr_id = p_contract_id;
SELECT 'SOME_WITH_ERROR'
FROM dual
WHERE EXISTS (SELECT 1
FROM oks_lines_temp
WHERE dnz_chr_id = p_contract_id
AND (start_dt_error_flag = 'Y' OR end_dt_error_flag = 'Y' OR status_error_flag = 'Y')
UNION
SELECT 1
FROM oks_sublines_temp
WHERE dnz_chr_id = p_contract_id
AND (start_dt_error_flag = 'Y' OR end_dt_error_flag = 'Y' OR status_error_flag = 'Y'));
UPDATE oks_lines_temp
SET start_date = nvl(l_global_start_date,start_date),
end_date = Nvl(l_global_end_date,end_date),
new_status = Nvl(l_global_new_status,new_status),
new_ste_code = Nvl(l_global_status_code,new_ste_code),
reason_code = Nvl(l_global_reason_code, reason_code),
comments = Nvl(l_global_comments, comments)
WHERE dnz_chr_id = p_contract_id
AND id = line.id;
UPDATE oks_sublines_temp
SET start_date = nvl(l_global_start_date,start_date),
end_date = Nvl(l_global_end_date,end_date),
price_negotiated = Nvl(l_global_price, price_negotiated),
new_status = Nvl(l_global_new_status,new_status),
new_ste_code = Nvl(l_global_status_code,new_ste_code),
reason_code = Nvl(l_global_reason_code, reason_code),
comments = Nvl(l_global_comments, comments)
WHERE dnz_chr_id = p_contract_id
AND id = subline.id;
UPDATE oks_sublines_temp
SET start_date = nvl(l_global_start_date,start_date),
end_date = Nvl(l_global_end_date,end_date),
price_negotiated = Nvl(l_global_price, price_negotiated),
new_status = Nvl(l_global_new_status,new_status),
new_ste_code = Nvl(l_global_status_code,new_ste_code),
reason_code = Nvl(l_global_reason_code, reason_code),
comments = Nvl(l_global_comments, comments)
WHERE dnz_chr_id = p_contract_id
AND id = sublinechild.id;
UPDATE oks_sublines_temp tmp
SET start_dt_eff_change_flag = modified.start_date_change,
end_dt_eff_change_flag = modified.end_date_change,
price_change_flag = modified.price_change,
status_change_flag = modified.status_change
WHERE tmp.dnz_chr_id = p_contract_id
AND tmp.id = modified.id;
UPDATE oks_lines_temp tmp
SET start_dt_eff_change_flag = modified.start_date_change,
end_dt_eff_change_flag = modified.end_date_change,
status_change_flag = modified.status_change
WHERE tmp.dnz_chr_id = p_contract_id
AND tmp.id = modified.id;
UPDATE oks_lines_temp
SET start_dt_error_flag = 'N',
end_dt_error_flag = 'N',
status_error_flag = 'N',
st_dt_err_message = NULL,
end_dt_err_message = NULL,
status_err_message = NULL
WHERE dnz_chr_id = p_contract_id;
UPDATE oks_sublines_temp
SET start_dt_error_flag = 'N',
end_dt_error_flag = 'N',
status_error_flag = 'N',
st_dt_err_message = NULL,
end_dt_err_message = NULL,
status_err_message = NULL
WHERE dnz_chr_id = p_contract_id;
UPDATE oks_lines_temp
SET undo_start_date = old_start_date,
old_start_date = start_date
WHERE dnz_chr_id = p_contract_id
AND start_dt_eff_change_flag = 'Y'
AND start_dt_error_flag = 'N';
UPDATE oks_lines_temp
SET undo_start_date = old_start_date
WHERE dnz_chr_id = p_contract_id
AND start_dt_eff_change_flag = 'Y'
AND start_dt_error_flag = 'Y';
UPDATE oks_lines_temp
SET undo_end_date = old_end_date,
old_end_date = end_date
WHERE dnz_chr_id = p_contract_id
AND end_dt_eff_change_flag = 'Y'
AND end_dt_error_flag = 'N';
UPDATE oks_lines_temp
SET undo_end_date = old_end_date
WHERE dnz_chr_id = p_contract_id
AND end_dt_eff_change_flag = 'Y'
AND end_dt_error_flag = 'Y';
UPDATE oks_lines_temp
SET undo_status = old_status,
old_status = status
WHERE dnz_chr_id = p_contract_id
AND status_change_flag = 'Y'
AND status_error_flag = 'N';
UPDATE oks_lines_temp
SET undo_status = old_status
WHERE dnz_chr_id = p_contract_id
AND status_change_flag = 'Y'
AND status_error_flag = 'Y';
UPDATE oks_sublines_temp
SET undo_start_date = old_start_date,
old_start_date = start_date
WHERE dnz_chr_id = p_contract_id
AND start_dt_eff_change_flag = 'Y'
AND start_dt_error_flag = 'N';
UPDATE oks_sublines_temp
SET undo_start_date = old_start_date
WHERE dnz_chr_id = p_contract_id
AND start_dt_eff_change_flag = 'Y'
AND start_dt_error_flag = 'Y';
UPDATE oks_sublines_temp
SET undo_end_date = old_end_date,
old_end_date = end_date
WHERE dnz_chr_id = p_contract_id
AND end_dt_eff_change_flag = 'Y'
AND end_dt_error_flag = 'N';
UPDATE oks_sublines_temp
SET undo_end_date = old_end_date
WHERE dnz_chr_id = p_contract_id
AND end_dt_eff_change_flag = 'Y'
AND end_dt_error_flag = 'Y';
UPDATE oks_sublines_temp
SET undo_status = old_status,
old_status = status
WHERE dnz_chr_id = p_contract_id
AND status_change_flag = 'Y'
AND status_error_flag = 'N';
UPDATE oks_sublines_temp
SET undo_status = old_status
WHERE dnz_chr_id = p_contract_id
AND status_change_flag = 'Y'
AND status_error_flag = 'Y';
UPDATE oks_sublines_temp
SET undo_price_negotiated = old_price_negotiated,
old_price_negotiated = price_negotiated
WHERE dnz_chr_id = p_contract_id
AND price_change_flag = 'Y'
AND top_lvl_parent <> -99;
UPDATE oks_headers_temp
SET global_start_date = NULL,
global_end_date = NULL,
global_price_negotiated = NULL,
global_status = NULL,
global_reason_code = NULL,
global_comments = NULL,
saved_flag = 'Y'
WHERE chr_id = p_contract_id;
UPDATE oks_sublines_temp
SET select_flag = NULL,
cascade_flag = NULL
WHERE dnz_chr_id = p_contract_id;
UPDATE oks_lines_temp lin
SET select_flag = NULL,
cascade_flag = NULL,
price_negotiated = (SELECT Sum(price_negotiated)
FROM oks_sublines_temp sub
WHERE sub.cle_id = lin.id)
WHERE dnz_chr_id = p_contract_id;
END MASS_UPDATE_CASCADE_SEL;
PROCEDURE MASS_UPDATE_SUBMIT(p_contract_id IN NUMBER, x_req_id OUT NOCOPY VARCHAR2) IS
l_request_id NUMBER;
description => 'Service Contracts Mass Update',
start_time => NULL,
sub_request => FALSE,
argument1 => p_contract_id
);
UPDATE oks_headers_temp
SET saved_flag = 'N', request_id = l_request_id
WHERE chr_id = p_contract_id;
END MASS_UPDATE_SUBMIT;
PROCEDURE delete_changes(p_contract_id NUMBER) IS
BEGIN
DELETE FROM oks_lines_temp WHERE dnz_chr_id = p_contract_id;
DELETE FROM oks_sublines_temp WHERE dnz_chr_id = p_contract_id;
DELETE FROM oks_headers_temp WHERE Chr_id = p_contract_id;
END delete_changes;