[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*) FROM OKC_K_LINES_V
WHERE id=P_Line_Id;
SELECT count(*) FROM OKC_K_LINES_B
WHERE id = p_cov_id
AND lse_id in (2,15,20)
and dnz_chr_id < 0;
p_clev_tbl_IN_out(v_Index).last_updated_by :=NULL;
p_clev_tbl_IN_out(v_Index).last_update_date :=NULL;
p_clev_tbl_IN_out(v_Index).last_update_login :=NULL;
p_rgpv_tbl_IN_out(v_Index).last_updated_by :=NULL;
p_rgpv_tbl_IN_out(v_Index).last_update_date :=NULL;
p_rgpv_tbl_IN_out(v_Index).last_update_login :=NULL;
p_rgpv_rec_IN_out.last_updated_by :=NULL;
p_rgpv_rec_IN_out.last_update_date :=NULL;
p_rgpv_rec_IN_out.last_update_login :=NULL;
p_rulv_tbl_IN_out(v_Index).last_updated_by:=NULL;
p_rulv_tbl_IN_out(v_Index).last_update_date:=NULL;
p_rulv_tbl_IN_out(v_Index).last_update_login:=NULL;
P_CTCV_tbl_In_Out(v_Index).LAST_UPDATED_BY := NULL;
P_CTCV_tbl_In_Out(v_Index).LAST_UPDATE_DATE := NULL;
P_CTCV_tbl_In_Out(v_Index).LAST_UPDATE_LOGIN := NULL;
P_CIMV_tbl_In_Out(v_Index).LAST_UPDATED_BY := NULL;
P_CIMV_tbl_In_Out(v_Index).LAST_UPDATE_DATE := NULL;
P_CIMV_tbl_In_Out(v_Index).LAST_UPDATE_LOGIN := NULL;
P_CIMV_tbl_In_Out(v_Index).PROGRAM_UPDATE_DATE := NULL;
p_restricted_update IN VARCHAR2 DEFAULT 'F',
x_Actual_coverage_id OUT NOCOPY NUMBER) IS
CURSOR Cur_LineDet (P_Line_Id IN NUMBER) IS
SELECT SFWT_FLAG,
CHR_ID,
START_DATE,
END_DATE,
LSE_ID,
LINE_NUMBER,
DISPLAY_SEQUENCE,
NAME,
ITEM_DESCRIPTION,
EXCEPTION_YN,
PRICE_LIST_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM OKC_K_LINES_v
WHERE ID= P_Line_Id;
SELECT SFWT_FLAG,
CHR_ID,
START_DATE,
END_DATE,
LSE_ID,
LINE_NUMBER,
DISPLAY_SEQUENCE,
NAME,
ITEM_DESCRIPTION,
EXCEPTION_YN,
PRICE_LIST_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM OKC_K_LINES_v
WHERE ID= P_Line_Id;
SELECT ID
FROM OKC_K_LINES_B
WHERE cle_Id=P_cle_Id
AND lse_Id=P_lse_Id;
SELECT ID,
EXCEPTION_YN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM OKC_K_LINES_B
WHERE cle_Id=P_cle_Id
AND lse_Id=P_lse_Id;
SELECT ID
FROM OKC_K_LINES_B
WHERE cle_Id=P_cle_Id
AND lse_Id=P_lse_Id;
SELECT ID
FROM OKC_K_LINES_B
WHERE cle_Id=P_cle_Id
AND lse_Id=P_lse_Id;
SELECT
pr.ID,
pr.SFWT_FLAG,
pr.OBJECT1_ID1,
pr.OBJECT1_ID2,
pr.JTOT_OBJECT1_CODE,
pr.CODE,
pr.FACILITY,
pr.MINORITY_GROUP_LOOKUP_CODE,
pr.SMALL_BUSINESS_FLAG,
pr.WOMEN_OWNED_FLAG
FROM OKC_K_Party_Roles_V pr,
OKC_K_LINES_B lv
WHERE pr.cle_ID=p_cle_Id
AND pr.Rle_Code=P_Role_Code
AND pr.cle_id = lv.id
AND pr.dnz_chr_id = lv.dnz_chr_id ;
SELECT
CRO_CODE,
CONTACT_SEQUENCE,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
resource_class
FROM OKC_CONTACTS_V
WHERE cpl_id=P_cpl_Id;
SELECT object1_id1,
object1_id2,
JTOT_OBJECT1_CODE,
number_of_items,
exception_yn
FROM OKC_K_ITEMS_V
WHERE cle_Id=P_Id;
SELECT ID,
CLE_ID,
BT_CLE_ID,
DNZ_CHR_ID,
START_HOUR,
START_MINUTE,
END_HOUR,
END_MINUTE,
MONDAY_FLAG,
TUESDAY_FLAG,
WEDNESDAY_FLAG,
THURSDAY_FLAG,
FRIDAY_FLAG,
SATURDAY_FLAG,
SUNDAY_FLAG,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
BILL_RATE_CODE,
FLAT_RATE,
UOM,
HOLIDAY_YN,
PERCENT_OVER_LIST_PRICE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
object_version_number --Added
FROM OKS_BILLRATE_SCHEDULES
WHERE CLE_ID = p_cle_id ;
SELECT
id
,cle_id
,dnz_chr_id
,discount_list
,coverage_type
,exception_cov_id
,limit_uom_quantified
,discount_amount
,discount_percent
,offset_duration
,offset_period
,incident_severity_id
,pdf_id
,work_thru_yn
,react_active_yn
,transfer_option
,prod_upgrade_yn
,inheritance_type
,pm_program_id
,pm_conf_req_yn
,pm_sch_exists_yn
,allow_bt_discount
,apply_default_timezone
,sync_date_install
,sfwt_flag
,react_time_name
,object_version_number
,security_group_id
,request_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM OKS_K_LINES_V
WHERE cle_id = p_cle_id;
SELECT jtf_note_id,
parent_note_id,
source_object_code,
source_number,
notes, --
notes_detail,
note_status,
source_object_meaning,
note_type,
note_type_meaning,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
note_status_meaning,
decoded_source_code,
decoded_source_meaning,
context
FROM JTF_NOTES_VL
WHERE source_object_id = p_source_object_id
AND source_object_code = 'OKS_COVTMPL_NOTE'
AND note_status <> 'P';
SELECT PM_PROGRAM_ID
FROM OKS_K_LINES_B
WHERE cle_id =p_contract_line_id;
SELECT
ID
,CLE_ID
,DEFAULT_YN
,TIMEZONE_ID
FROM oks_coverage_timezones
WHERE cle_id = p_cle_id;
SELECT
ID
,COV_TZE_LINE_ID
,DNZ_CHR_ID
,START_HOUR
,START_MINUTE
,END_HOUR
,END_MINUTE
,MONDAY_YN
,TUESDAY_YN
,WEDNESDAY_YN
,THURSDAY_YN
,FRIDAY_YN
,SATURDAY_YN
,SUNDAY_YN
FROM oks_coverage_times
WHERE COV_TZE_LINE_ID = p_cov_tz_id;
SELECT ID,
ACTION_TYPE_CODE
FROM OKS_ACTION_TIME_TYPES
WHERE cle_id = p_cle_id ;
SELECT ID,
UOM_CODE,
SUN_DURATION,
MON_DURATION,
TUE_DURATION,
WED_DURATION,
THU_DURATION,
FRI_DURATION,
SAT_DURATION
FROM OKS_ACTION_TIMES
WHERE cov_action_type_id = p_cov_act_type_id ;
SELECT ORG_ID
FROM okc_k_headers_all_b
WHERE id=p_contract_id;
SELECT COUNT(1) FROM OKC_K_LINES_B
WHERE cle_Id=P_Line_Id
AND lse_Id IN (2,15,20);
SELECT Currency_Code FROM OKC_K_LINES_B
WHERE ID=P_Id;
SELECT sts_Code FROM OKC_K_LINES_B
WHERE ID=P_Id;
p_restricted_update => p_restricted_update,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out
);
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_attribute1 => notes_rec.ATTRIBUTE1,
p_attribute2 => notes_rec.ATTRIBUTE2,
p_attribute3 => notes_rec.ATTRIBUTE3,
p_attribute4 => notes_rec.ATTRIBUTE4,
p_attribute5 => notes_rec.ATTRIBUTE5,
p_attribute6 => notes_rec.ATTRIBUTE6,
p_attribute7 => notes_rec.ATTRIBUTE7,
p_attribute8 => notes_rec.ATTRIBUTE8,
p_attribute9 => notes_rec.ATTRIBUTE9,
p_attribute10 => notes_rec.ATTRIBUTE10,
p_attribute11 => notes_rec.ATTRIBUTE11,
p_attribute12 => notes_rec.ATTRIBUTE12,
p_attribute13 => notes_rec.ATTRIBUTE13,
p_attribute14 => notes_rec.ATTRIBUTE14,
p_attribute15 => notes_rec.ATTRIBUTE15,
p_context => notes_rec.CONTEXT,
p_note_type => notes_rec.NOTE_TYPE);
l_klnv_tbl_in.delete;
p_restricted_update => p_restricted_update,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
l_klnv_tbl_in.delete;
OKS_CTZ_PVT.insert_row(
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_oks_coverage_timezones_v_tbl => l_covtz_tbl_in,
x_oks_coverage_timezones_v_tbl => l_covtz_tbl_out);
okc_debug.log('After OKS_CTZ_PVT insert_row'||l_return_status, 2);
l_covtim_tbl_in(covtim_ctr).program_update_date := NULL;
OKS_CVT_PVT.insert_row(
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_oks_coverage_times_v_tbl => l_covtim_tbl_in,
x_oks_coverage_times_v_tbl => l_covtim_tbl_out);
okc_debug.log('After OKS_CVT_PVT insert_row'||l_return_status, 2);
p_restricted_update => p_restricted_update,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
OKS_ACT_PVT.INSERT_ROW(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_oks_action_time_types_v_tbl => l_act_type_tbl_in,
x_oks_action_time_types_v_tbl => l_act_type_tbl_out);
okc_debug.log('After OKS_ACT_PVT INSERT_ROW'||l_return_status, 2);
OKS_ACM_PVT.insert_row(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_oks_action_times_v_tbl => l_act_time_tbl_in,
x_oks_action_times_v_tbl => l_act_time_tbl_out);
okc_debug.log('After OKS_ACM_PVT insert_row'||l_return_status, 2);
p_restricted_update => p_restricted_update,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
p_restricted_update => p_restricted_update,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
l_bill_rate_tbl_in(1).program_update_date := brs_rec.program_update_date;
l_bill_rate_tbl_in(1).last_updated_by := NULL;
l_bill_rate_tbl_in(1).last_update_date := NULL;
l_bill_rate_tbl_in(1).last_update_login := NULL;
OKS_BRS_PVT.INSERT_ROW(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_oks_billrate_schedules_v_tbl => l_bill_rate_tbl_in,
x_oks_billrate_schedules_v_tbl => x_bill_rate_tbl_out);
SELECT ID FROM OKC_K_Lines_v
WHERE chr_ID=p_chr_Id;
SELECT ID FROM OKC_Governances_v
WHERE dnz_chr_ID=p_chr_Id
And cle_id Is Null;
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);
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);
IS SELECT id, start_date, lse_id
FROM Okc_K_Lines_b
WHERE Id=P_cle_Id;
IS SELECT DNZ_CHR_ID, ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_cle_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_ITEMS
WHERE cle_Id=P_Line_Id;
IS SELECT Tve_ID
FROM OKC_React_Intervals
WHERE Rul_Id =P_Rule_Id;
SELECT pr.ID FROM OKC_K_PARTY_ROLES_B pr
,OKC_K_LINES_V lv
WHERE pr.cle_Id=P_cle_Id
And pr.cle_id = lv.id
And pr.dnz_chr_id = lv.dnz_chr_id;
SELECT ID FROM OKC_CONTACTS
WHERE cpl_Id=P_cpl_Id;
SELECT ID FROM OKC_RULES_B
WHERE Rgp_Id=P_Rgp_Id
AND Rule_Information_category=P_rule_Type;
SELECT ID FROM OKC_RULES_B
WHERE Rgp_Id=P_Rgp_Id;
SELECT ID FROM OKC_RULE_GROUPS_B
WHERE cle_Id=P_Cle_Id;
SELECT Id FROM OKC_K_REL_OBJS_V
WHERE cle_Id = P_cle_Id;
SELECT Id FROM OKS_K_ORDER_DETAILS_V
WHERE chr_id = p_chr_id
AND cle_Id = P_cle_Id;
SELECT Id FROM OKS_K_SALES_CREDITS_V
WHERE cle_Id = P_cle_Id;
SELECT Id FROM OKS_K_Order_Contacts_V
WHERE cod_Id = P_cod_Id;
SELECT ID FROM OKS_PM_SCHEDULES
WHERE CLE_ID = p_cov_id;
SELECT BRS.ID BRS_LINE_ID
FROM OKC_K_LINES_B LINES1,
OKC_K_LINES_B LINES2,
OKC_K_LINES_B LINES3,
OKC_K_LINES_B LINES4,
OKS_BILLRATE_SCHEDULES BRS
WHERE LINES1.CLE_ID = p_service_line_id
AND lines2.cle_id = lines1.id
AND lines3.cle_id = lines2.id
AND lines4.cle_id = lines3.id
AND lines1.lse_id in (2,15,20)
AND lines2.lse_id in (3,16,21)
AND lines3.lse_id in (5,23,59)
AND lines4.lse_id in (6,24,60)
AND brs.cle_id = lines4.id
AND brs.dnz_chr_id = lines1.dnz_chr_id ;
SELECT cle_id
from okc_k_lines_b
where id=p_cle_id
and lse_id in (2,15,20);
SELECT jtf_note_id
FROM JTF_NOTES_VL
WHERE source_object_id = p_source_object_id
AND source_object_code = 'OKS_COV_NOTE';
SELECT ID,DNZ_CHR_ID
FROM oks_k_lines_b
WHERE cle_id = p_Id
AND dnz_chr_id = p_dnz_chr_id;
SELECT ID,CLE_ID,DNZ_CHR_ID
FROM oks_coverage_timezones
WHERE cle_id = p_iD
AND dnz_chr_id =p_dnz_chr_id;
SELECT ID,DNZ_CHR_ID
FROM OKS_COVERAGE_TIMES
WHERE COV_TZE_LINE_ID = p_id
AND dnz_chr_id =p_dnz_chr_id;
SELECT ID,DNZ_CHR_ID
FROM OKS_ACTION_TIME_TYPES
WHERE cle_id = p_iD
AND dnz_chr_id =p_dnz_chr_id;
SELECT ID,DNZ_CHR_ID
FROM OKS_ACTION_TIMES
WHERE COV_ACTION_TYPE_ID = p_id
AND dnz_chr_id =p_dnz_chr_id;
SELECT ID,DNZ_CHR_ID
FROM OKS_BILLRATE_SCHEDULES
WHERE CLE_ID = p_id
AND dnz_chr_id =p_dnz_chr_id;
SELECT kl.lse_ID
from okc_k_Lines_v kl,
okc_rule_groups_v rg
where kl.id = rg.cle_id
and rg.id = P_rgp_Id;
JTF_NOTES_PUB.Secure_Delete_note
( p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list, -- VARCHAR2 DEFAULT 'F'
p_commit => 'F', --IN VARCHAR2 DEFAULT 'F'
p_validation_level => 100, --IN NUMBER DEFAULT 100
x_return_status => l_return_status , -- OUT NOCOPY VARCHAR2
x_msg_count => l_msg_count, -- OUT NOCOPY NUMBER
x_msg_data => l_msg_data , -- OUT NOCOPY VARCHAR2
p_jtf_note_id => note_rec.jtf_note_id,
p_use_AOL_security => 'F' --IN VARCHAR2 DEFAULT 'T'
);
OKS_BRS_PVT.delete_row(
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_oks_billrate_schedules_v_tbl => l_brs_tbl_in);
okc_debug.log('After OKS_BRS_PVT delete_row', 2);
OKS_BRS_PVT.delete_row(
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_oks_billrate_schedules_v_tbl => l_brsv_tbl_in);
OKS_KLN_PVT.delete_row(
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_tbl => l_klev_tbl_in);
OKS_CVT_PVT.delete_row(
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_oks_coverage_times_v_tbl =>l_cvtv_tbl_in);
OKS_CTZ_PVT.delete_row(
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_oks_coverage_timezones_v_tbl => l_tzev_tbl_in);
okc_debug.log('AFTER OKS_CTZ_PVT delete_row', 2);
OKS_ACM_PVT.delete_row(
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_oks_action_times_v_tbl => l_acmv_tbl_in);
okc_debug.log('AFTER OKS_ACM_PVT delete_row', 2);
OKS_ACT_PVT.delete_row(
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_oks_action_time_types_v_tbl => l_actv_tbl_in);
okc_debug.log('AFTER OKS_ACT_PVT delete_row', 2);
OKS_ORDER_CONTACTS_PUB.Delete_Order_Contact(
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_cocv_tbl => l_cocv_tbl_in);
okc_debug.log('AFTER OKS_ORDER_CONTACTS_PUB delete_order_contract', 2);
OKS_ORDER_DETAILS_PUB.Delete_Order_Detail(
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_codv_tbl => l_codv_tbl_in);
okc_debug.log('AFTER OKS_ORDER_DETAILS_PUB delete_order_detail', 2);
OKS_SALES_CREDIT_PUB.Delete_Sales_Credit(
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_scrv_tbl => l_scrv_tbl_in);
okc_debug.log('AFTER OKS_SALES_CREDIT_PUB Delete_Sales_Credit', 2);
OKC_K_REL_OBJS_PUB.Delete_Row(
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_crjv_tbl => l_crjv_tbl_in);
okc_debug.log('AFTER OKC_K_REL_OBJS_PUB Delete_Row', 2);
OKC_CONTRACT_PARTY_PUB.Delete_Contact(
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_ctcv_tbl => l_ctcv_tbl_in);
OKC_CONTRACT_PARTY_PUB.Delete_k_Party_Role(
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_cplv_tbl => l_cplv_tbl_in);
OKS_PMS_PVT.delete_row(
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_oks_pm_schedules_v_tbl => l_pm_schedules_v_tbl);
okc_contract_ITEM_pub.delete_Contract_ITEM (
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_cimv_tbl => l_cimv_tbl_in);
okc_debug.log('AFTER okc_contract_ITEM_pub.delete_Contract_ITEM', 2);
okc_contract_pub.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_tbl => l_clev_tbl_in);
SELECT id FROM OKC_Condition_Headers_v where Object_Id=P_KLine_Id
and JTOT_Object_Code='OKC_K_LINE';
SELECT id FROM OKC_Condition_Occurs_v where Cnh_Id=P_Cnh_Id;
SELECT aae_Id,coe_id FROM Okc_Action_att_vals_v WHERE coe_id=P_Coe_Id;
SELECT id FROM OKC_Condition_Lines_v WHERE Cnh_Id = P_Cnh_Id;
SELECT id FROM OKC_OUTCOMES_V WHERE cnh_Id=P_Cnh_Id;
SELECT id FROM okc_outcome_arguments_v WHERE oce_id=P_Oce_Id;
OKC_OUTCOME_PUB.delete_out_arg(
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_oatv_tbl => l_Oatv_tbl);
OKC_OUTCOME_PUB. delete_outcome(
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_ocev_tbl => l_Ocev_tbl);
OKC_CONDITIONS_PUB.delete_act_att_vals(
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_aavv_tbl => l_aavv_tbl);
OKC_CONDITIONS_PUB. delete_cond_occurs(
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_coev_tbl => l_Coev_tbl);
OKC_CONDITIONS_PUB.delete_cond_lines(
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_cnlv_tbl => l_cnlv_tbl);
OKC_CONDITIONS_PUB. delete_cond_hdrs(
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_cnhv_tbl => l_cnhv_tbl);
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
);
PROCEDURE Update_COVERAGE_Effectivity(
p_api_version 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,
p_service_Line_Id IN NUMBER,
p_New_Start_Date IN DATE,
p_New_End_Date IN DATE) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_cov_eff';
SELECT ID,
LSE_ID,
START_DATE,
END_DATE
FROM OKC_K_LINES_V
WHERE cle_ID= P_Id and lse_id in (2,14,15,20,13,19);
SELECT ID, START_DATE, END_DATE
FROM OKC_K_LINES_V
WHERE cle_ID= P_Id ;
SELECT ID, OFFSET_DURATION, OFFSET_PERIOD
FROM OKS_K_LINES_B
WHERE CLE_ID = p_cle_id ;
SELECT ID FROM OKC_K_LINES_V
WHERE cle_ID= P_Id;
SELECT ID FROM OKC_K_LINES_V
WHERE cle_ID= P_Id;
SELECT Chr_Id FROM OKC_K_LINES_V
WHERE Id=P_Service_Line_ID;
okc_debug.Set_Indentation('Update Coverage Effectivity');
okc_debug.log('Entered Update Coverage Effectivity', 2);
okc_debug.log('Before okc_contract_pub.Update_Contract_Line', 2);
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 => 'T',
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
okc_debug.log('After okc_contract_pub.Update_Contract_Line'||l_return_status, 2);
okc_debug.log('End of Update_Coverage_Effectivity'||l_return_status, 2);
okc_debug.log('Exp of Update_Coverage_Effectivity'||SQLERRM, 2);
okc_debug.log('Exp of Update_Coverage_Effectivity e_Error'||SQLERRM, 2);
okc_debug.log('Exp of Update_Coverage_Effectivity '||SQLERRM, 2);
okc_debug.log('Exp of Update_Coverage_Effectivity '||SQLERRM, 2);
okc_debug.log('Exp of Update_Coverage_Effectivity '||SQLERRM, 2);
END Update_Coverage_Effectivity;
IS SELECT id, start_date, lse_id
FROM Okc_K_Lines_b
WHERE Id=P_cle_Id;
IS SELECT DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_cle_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID,DNZ_CHR_ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_ITEMS
WHERE cle_Id=P_Line_Id;
IS SELECT Tve_ID
FROM OKC_React_Intervals
WHERE Rul_Id =P_Rule_Id;
SELECT pr.ID FROM OKC_K_PARTY_ROLES_B pr
,OKC_K_LINES_V lv
WHERE pr.cle_Id=P_cle_Id
And pr.cle_id = lv.id
And pr.dnz_chr_id = lv.dnz_chr_id;
SELECT ID FROM OKC_CONTACTS
WHERE cpl_Id=P_cpl_Id;
SELECT ID FROM OKC_RULES_B
WHERE Rgp_Id=P_Rgp_Id
AND Rule_Information_category=P_rule_Type;
SELECT ID FROM OKC_RULES_B
WHERE Rgp_Id=P_Rgp_Id;
SELECT ID FROM OKC_RULE_GROUPS_B
WHERE cle_Id=P_Cle_Id;
SELECT Id FROM OKC_K_REL_OBJS_V
WHERE cle_Id = P_cle_Id;
SELECT Id FROM OKS_K_ORDER_DETAILS_V
WHERE chr_id = p_chr_id
AND cle_Id = P_cle_Id;
SELECT Id FROM OKS_K_SALES_CREDITS_V
WHERE cle_Id = P_cle_Id;
SELECT Id FROM OKS_K_Order_Contacts_V
WHERE cod_Id = P_cod_Id;
SELECT kl.lse_ID
from okc_k_Lines_v kl,
okc_rule_groups_v rg
where kl.id = rg.cle_id
and rg.id = P_rgp_Id;
OKS_ORDER_CONTACTS_PUB.Delete_Order_Contact(
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_cocv_tbl => l_cocv_tbl_in);
OKS_ORDER_DETAILS_PUB.Delete_Order_Detail(
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_codv_tbl => l_codv_tbl_in);
OKS_SALES_CREDIT_PUB.Delete_Sales_Credit(
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_scrv_tbl => l_scrv_tbl_in);
OKC_K_REL_OBJS_PUB.Delete_Row(
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_crjv_tbl => l_crjv_tbl_in);
OKC_CONTRACT_PARTY_PUB.Delete_Contact(
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_ctcv_tbl => l_ctcv_tbl_in);
OKC_CONTRACT_PARTY_PUB.Delete_k_Party_Role(
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_cplv_tbl => l_cplv_tbl_in);
okc_Rule_pub.delete_Rule (
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_rulv_tbl => l_rulv_tbl_in);
okc_Rule_pub.delete_Rule_group (
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_rgpv_tbl => l_rgpv_tbl_in);
okc_contract_ITEM_pub.delete_Contract_ITEM (
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_cimv_tbl => l_cimv_tbl_in);
okc_contract_pub.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_tbl => l_clev_tbl_in);
delete okc_k_lines_tl where
id =l_clev_tbl_in(i).id;
delete okc_k_lines_b where
id =l_clev_tbl_in(i).id;
SELECT coverage_id, standard_cov_yn
FROM OKS_K_LINES_B
WHERE cle_id = p_contract_line_id;
SELECT NAME, ITEM_DESCRIPTION,
EXCEPTION_YN
FROM OKC_K_LINES_V
WHERE id = p_cov_line_id
AND lse_id in (2,15,20);
Select COVERAGE_TYPE,
EXCEPTION_COV_ID,
INHERITANCE_TYPE,
TRANSFER_OPTION,
PROD_UPGRADE_YN
/* COmmented by Jvorugan for Bug:4610449 NVL(PM_PROGRAM_ID,0) PM_PROGRAM_ID,
NVL(PM_CONF_REQ_YN,0) PM_CONF_REQ_YN,
NVL(PM_SCH_EXISTS_YN,0) PM_SCH_EXISTS_YN */
FROM OKS_K_LINES_B
WHERE CLE_ID = p_cov_line_id;
Select NVL(PM_PROGRAM_ID,0) PM_PROGRAM_ID,
NVL(PM_CONF_REQ_YN,0) PM_CONF_REQ_YN,
NVL(PM_SCH_EXISTS_YN,0) PM_SCH_EXISTS_YN
FROM OKS_K_LINES_B
WHERE CLE_ID = p_contract_line_id;
SELECT lines.id,
-- lines.start_date start_date,
-- lines.end_date end_date,
items.object1_id1
FROM OKC_K_LINES_V lines, OKC_K_ITEMS_V items
WHERE lines.cle_id = p_cle_id
AND lines.lse_id IN(3,16,21)
AND items.JTOT_OBJECT1_CODE = 'OKX_BUSIPROC'
AND items.cle_id = lines.id ;
SELECT lines.id bp_line_id,
NVL(lines.price_list_id,0) price_list_id,
items.object1_id1 object1_id1,
NVL(kines.discount_list,0) discount_list,
NVL(kines.offset_duration,0) offset_duration,
NVL(kines.offset_period,0) offset_period,
NVL(kines.allow_bt_discount,0) allow_bt_discount,
NVL(kines.apply_default_timezone,0) apply_default_timezone
FROM OKC_K_LINES_B lines,
OKC_K_ITEMS items,
OKS_K_LINES_B kines
WHERE lines.cle_id = p_cle_id
AND lines.id = items.cle_id
AND items.jtot_object1_code = 'OKX_BUSIPROC'
AND lines.lse_id in (3,16,21)
AND kines.cle_id = lines.id
AND lines.dnz_chr_id = kines.dnz_chr_id
AND lines.dnz_chr_id = items.dnz_chr_id;
SELECT lines.id,
items.object1_id1 object1_id1,
covtz.timezone_id timezone_id,
covtz.default_yn default_yn,
covtm.start_hour start_hour,
covtm.start_minute start_minute,
covtm.end_hour end_hour,
covtm.end_minute end_minute,
covtm.monday_yn monday_yn,
covtm.tuesday_yn tuesday_yn,
covtm.wednesday_yn wednesday_yn,
covtm.thursday_yn thursday_yn,
covtm.friday_yn friday_yn,
covtm.saturday_yn saturday_yn,
covtm.sunday_yn sunday_yn
FROM OKC_K_LINES_B lines,
OKC_K_ITEMS items,
OKS_COVERAGE_TIMEZONES covtz,
OKS_COVERAGE_TIMES covtm
WHERE lines.id = p_id
AND lines.lse_id IN (3,16,21)
AND lines.dnz_chr_id = items.dnz_chr_id
AND items.cle_id = lines.id
AND items.object1_id1 = p_bp_id
AND items.jtot_object1_code = 'OKX_BUSIPROC'
AND items.dnz_chr_id = lines.dnz_chr_id
AND covtz.cle_id = lines.id
AND covtz.dnz_chr_id = lines.dnz_chr_id
AND covtm.cov_tze_line_id = covtz.id
ORDER BY to_number(items.object1_id1), covtz.timezone_id ;
SELECT lines.id react_time_line_id,
oksl.id oks_react_line_id,
NVL(oksl.incident_severity_id,0) incident_severity_id,
NVL(oksl.pdf_id,0) pdf_id,
NVL(oksl.work_thru_yn,'N') work_thru_yn,
NVL(oksl.react_active_yn, 'N') react_active_yn,
oksl.react_time_name react_time_name,
act.id act_type_line_id,
act.action_type_code action_type_code,
acm.uom_code uom_code,
NVL(acm.sun_duration,0) sun_duration,
NVL(acm.mon_duration,0) mon_duration,
NVL(acm.tue_duration,0) tue_duration,
NVL(acm.wed_duration,0) wed_duration,
NVL(acm.thu_duration,0) thu_duration,
NVL(acm.fri_duration,0) fri_duration,
NVL(acm.sat_duration,0) sat_duration
FROM OKC_K_LINES_B lines,
OKS_K_LINES_V oksl,
OKS_ACTION_TIME_TYPES act,
OKS_ACTION_TIMES acm
WHERE lines.cle_id = p_cle_id
AND lines.lse_id IN (4,17,22)
AND oksl.cle_id = lines.id
AND act.cle_id = lines.id
AND act.dnz_chr_id = lines.dnz_chr_id
AND acm.cov_action_type_id = act.id
AND acm.dnz_chr_id = act.dnz_chr_id;
SELECT lines.id lines_id,
party.id party_id,
items.object1_id1 BP_ID,
ocv.cro_code cro_code,
ocv.object1_id1 res_id,
ocv.resource_class resource_class
FROM OKC_K_LINES_V lines,
OKC_K_PARTY_ROLES_B party,
OKC_CONTACTS_V ocv,
OKC_K_ITEMS_V items
WHERE lines.id = p_bp_line_id
AND lines.lse_id IN (3,16,21)
AND party.cle_id = lines.id
AND items.cle_id = lines.id
AND items.object1_id1 = p_bp_id
AND items.jtot_object1_code = 'OKX_BUSIPROC'
AND party.id = ocv.cpl_id
AND lines.dnz_chr_id = party.dnz_chr_id ;
SELECT lines.id bill_type_line_id,
items.object1_id1 object1_id1,
oksl.discount_amount discount_amount,
oksl.discount_percent discount_percent,
txn.billing_type billing_type
FROM OKC_K_LINES_V lines,
OKS_K_LINES_B oksl,
OKC_K_ITEMS_V items,
okx_txn_billing_types_v txn
WHERE lines.cle_id = p_cle_id
AND oksl.cle_id = lines.id
AND oksl.dnz_chr_id = lines.dnz_chr_id
AND items.cle_id = lines.id
AND lines.lse_id IN (5,23,59)
AND items.jtot_object1_code = 'OKX_BILLTYPE'
AND items.object1_id1 = txn.id1;
SELECT NVL(brs.START_HOUR,0) START_HOUR,
NVL(brs.START_MINUTE,0) START_MINUTE,
NVL(brs.END_HOUR,0) END_HOUR,
NVL(brs.END_MINUTE,0)END_MINUTE,
NVL(brs.MONDAY_FLAG,'N') MONDAY_FLAG,
NVL(brs.TUESDAY_FLAG,'N')TUESDAY_FLAG,
NVL(brs.WEDNESDAY_FLAG,'N')WEDNESDAY_FLAG,
NVL(brs.THURSDAY_FLAG,'N')THURSDAY_FLAG,
NVL(brs.FRIDAY_FLAG,'N')FRIDAY_FLAG,
NVL(brs.SATURDAY_FLAG,'N')SATURDAY_FLAG,
NVL(brs.SUNDAY_FLAG,'N')SUNDAY_FLAG,
NVL(brs.OBJECT1_ID1,'N')OBJECT1_ID1,
NVL(brs.OBJECT1_ID2,'N')OBJECT1_ID2,
NVL(brs.JTOT_OBJECT1_CODE,'N')JTOT_OBJECT1_CODE,
NVL(brs.BILL_RATE_CODE,'N')BILL_RATE_CODE,
NVL(brs.FLAT_RATE,0)FLAT_RATE,
NVL(brs.UOM,'N') UOM,
NVL(brs.HOLIDAY_YN,'N') HOLIDAY_YN,
NVL(brs.PERCENT_OVER_LIST_PRICE,0) PERC_OVER_LIST_PRICE
FROM OKS_BILLRATE_SCHEDULES brs
WHERE brs.bt_cle_id = p_bt_cle_id ;
x_source_bp_tbl.DELETE;
x_target_bp_tbl.DELETE;
x_source_res_tbl_type.DELETE;
x_target_res_tbl_type.DELETE;
x_source_bp_cover_time_tbl.DELETE;
x_target_bp_cover_time_tbl.DELETE;
x_source_react_time_tbl.DELETE;
x_target_react_time_tbl.DELETE;
x_source_bill_tbl.DELETE;
x_target_bill_tbl.DELETE;
x_source_brs_tbl.DELETE;
x_target_brs_tbl.DELETE;
SELECT 'X'
FROM OKS_COVERAGE_TIMEZONES
WHERE cle_id = l_cle_id
AND timezone_id = l_timezone_id;
SELECT ID FROM OKC_K_LINES_v
WHERE CLE_ID= P_Line_Id
AND LSE_ID in (2,15,20);
SELECT Start_Date,End_Date FROM OKC_K_LINES_v
WHERE ID= P_Line_Id;
p_restricted_update => 'T', -- 'F', modified based on bug 5493713
x_Actual_coverage_id => l_Actual_coverage_id);
x_bill_rate_tbl(1).program_update_date := okc_api.g_miss_date ;
x_bill_rate_tbl(1).last_updated_by := okc_api.g_miss_num ;
x_bill_rate_tbl(1).last_update_date := okc_api.g_miss_date ;
x_bill_rate_tbl(1).last_update_login := okc_api.g_miss_num ;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_BILLRATE_SCHEDULES_V
WHERE BT_CLE_ID = l_bt_id
AND MONDAY_FLAG = 'Y'
AND holiday_yn = l_holiday;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_BILLRATE_SCHEDULES_V
WHERE BT_CLE_ID = l_bt_id
AND TUESDAY_FLAG = 'Y'
AND holiday_yn = l_holiday;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_BILLRATE_SCHEDULES_V
WHERE BT_CLE_ID = l_bt_id
AND WEDNESDAY_FLAG = 'Y'
AND holiday_yn = l_holiday;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_BILLRATE_SCHEDULES_V
WHERE BT_CLE_ID = l_bt_id
AND THURSDAY_FLAG = 'Y'
AND holiday_yn = l_holiday;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_BILLRATE_SCHEDULES_V
WHERE BT_CLE_ID = l_bt_id
AND FRIDAY_FLAG = 'Y'
AND holiday_yn = l_holiday;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_BILLRATE_SCHEDULES_V
WHERE BT_CLE_ID = l_bt_id
AND SATURDAY_FLAG = 'Y'
AND holiday_yn = l_holiday;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_BILLRATE_SCHEDULES_V
WHERE BT_CLE_ID = l_bt_id
AND SUNDAY_FLAG = 'Y'
AND holiday_yn = l_holiday;
SELECT meaning from fnd_lookups where lookup_type = 'DAY_NAME'
and lookup_code = p_day_code;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
x_clev_tbl(1).last_updated_by := NULL ;
x_clev_tbl(1).last_update_date := NULL ;
x_clev_tbl(1).last_update_login := NULL ;
SELECT lines1.id billtype_line_id,
lines2.id billrate_line_id,
orgb.id rule_group_id,
rules.id rule_id,
rules.dnz_chr_id rule_dnz_chr_id,
rules.created_by,
rules.creation_date,
rules.last_updated_by,
rules.last_update_date,
rules.last_update_login,
rules.rule_information_category,
rules.rule_information1 uom, -- uom
rules.rule_information2 flat_rate, -- flat_rate
rules.rule_information3 percent_over_list_price, -- %over_list_price
rules.rule_information4 bill_rate_code, -- bill_rate_code
NVL(rules.TEMPLATE_YN,'N') TEMPLATE_YN
FROM
OKC_K_LINES_B lines1,
OKC_K_LINES_B lines2,
OKC_RULE_GROUPS_B orgb,
OKC_RULES_B rules
WHERE
lines1.lse_id IN (5,23,59)
AND lines2.lse_id IN (6,24,60)
AND lines1.dnz_chr_id = lines2.dnz_chr_id
AND lines2.cle_id = lines1.id
AND lines2.id = orgb.cle_id
AND lines2.dnz_chr_id = orgb.dnz_chr_id
AND rules.rgp_id = orgb.id
AND rules.dnz_chr_id = orgb.dnz_chr_id
AND rules.rule_information_category = 'RSL'
AND rules.rule_information9 IS NULL ; -- upgrade_flag
l_bill_rate_tbl_in(1).program_update_date := NULL;
l_bill_rate_tbl_in(1).last_updated_by := br_rec.last_updated_by;
l_bill_rate_tbl_in(1).last_update_date := br_rec.last_update_date;
l_bill_rate_tbl_in(1).last_update_login := br_rec.last_update_login;
OKS_BRS_PVT.INSERT_ROW(
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_oks_billrate_schedules_v_tbl => l_bill_rate_tbl_in,
x_oks_billrate_schedules_v_tbl => x_bill_rate_tbl_out);
UPDATE okc_rules_b
SET rule_information9 = 'Y'
WHERE id = br_rec.rule_id ;
UPDATE OKS_BILLRATE_SCHEDULES
SET object_version_number = 1 ;
WHEN G_EXCEPTION_RULE_UPDATE THEN
x_return_status := OKC_API.HANDLE_EXCEPTIONS
( l_api_name,
G_PKG_NAME,
'OKC_API.G_RET_STS_ERROR',
x_msg_count,
x_msg_data,
'_PVT'
);
l_program_update_date DATE;
SELECT lines1.id bp_line_id, lines2.id bill_type_line_id,lines2.lse_id bt_lse_id,
lines3.id bill_rate_line_id, lines3.lse_id br_lse_id,lines3.sts_code sts_code,
lines3.dnz_chr_id br_dnz_chr_id,
orgb.id rule_group_id,
rules.id rule_id,
rules.template_yn template_yn,
rules.rule_information4 rule_information4,
items.object1_id1, -- bp_id
bills.id billrate_sch_id,
bills.cle_id br_line_id,
bills.bt_cle_id bt_cle_id,
bills.bill_rate_code bill_rate_code,
bills.flat_rate flat_rate,
bills.uom uom,
bills.percent_over_list_price percent_over_list_price,
bills.created_by created_by,
bills.creation_date creation_date,
bills.last_updated_by last_updated_by,
bills.last_update_date last_update_date,
bills.last_update_login last_update_login,
bills.security_group_id security_group_id,
bills.object_version_number object_version_number--Added
FROM
OKC_K_LINES_B lines1,
OKC_K_LINES_B lines2,
OKC_K_LINES_B lines3,
OKC_K_ITEMS items,
OKC_RULE_GROUPS_B orgb,
OKC_RULES_B rules,
OKS_BILLRATE_SCHEDULES bills
WHERE
lines1.lse_id IN (3,16,21)
and lines2.lse_id IN (5,23,59)
and lines3.lse_id IN (6,24,60)
and lines2.cle_id = lines1.id
and lines3.cle_id = lines2.id
and orgb.cle_id = lines3.id
and rules.rgp_id = orgb.id
and items.cle_id = lines1.id
and bills.bt_cle_id = lines2.id
and bills.cle_id = lines3.id
and lines2.dnz_chr_id = lines1.dnz_chr_id
and lines3.dnz_chr_id = lines2.dnz_chr_id
and rules.rule_information10 IS NULL
and items.object1_id1 = to_char(p_business_process_id) ;
SELECT TO_CHAR(START_TIME,'HH24') START_HOUR,TO_CHAR(START_TIME,'MI')START_MINUTE,
TO_CHAR(END_TIME,'HH24')END_HOUR,TO_CHAR(END_TIME,'MI')END_MINUTE,
MONDAY_FLAG,TUESDAY_FLAG,WEDNESDAY_FLAG,THURSDAY_FLAG,FRIDAY_FLAG,
SATURDAY_FLAG,SUNDAY_FLAG, INVENTORY_ITEM_ID, LABOR_CODE
FROM CS_TM_LABOR_SCHEDULES
WHERE BUSINESS_PROCESS_ID = p_bus_proc_id
AND HOLIDAY_FLAG = p_holiday_flag
AND LABOR_CODE = p_labor_code; */
If (OKC_ASSENT_PUB.HEADER_OPERATION_ALLOWED(bill_rate_rec.br_dnz_chr_id, 'UPDATE') = 'T') then -- status and operations check
i:= i + 1 ;
l_program_update_date := sysdate;
l_program_update_date := NULL;
fnd_file.put_line(FND_FILE.LOG, 'PROGRAM_UPDATE_DATE...'||l_program_update_date);
l_bill_rate_tbl_in(1).program_update_date := l_program_update_date;
l_bill_rate_tbl_in(1).last_updated_by := bill_rate_rec.last_updated_by;
l_bill_rate_tbl_in(1).last_update_date := bill_rate_rec.last_update_date;
l_bill_rate_tbl_in(1).last_update_login := bill_rate_rec.last_update_login;
OKS_BRS_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oks_billrate_schedules_v_tbl => l_bill_rate_tbl_in,
x_oks_billrate_schedules_v_tbl => x_bill_rate_tbl_out);
fnd_file.put_line(FND_FILE.LOG, 'AFTER OKS_BRS_PVT.UPDATE_ROW......');
fnd_file.put_line(FND_FILE.LOG, 'Return Status from OKS_BRS_PVT.UPDATE_ROW API...'||l_return_status);
RAISE G_EXCEPTION_BRS_UPDATE ;
p_restricted_update => 'F',
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out );
l_bill_rate_tbl_in(1).program_update_date := l_program_update_date;
l_bill_rate_tbl_in(1).last_updated_by := bill_rate_rec.last_updated_by;
l_bill_rate_tbl_in(1).last_update_date := bill_rate_rec.last_update_date;
l_bill_rate_tbl_in(1).last_update_login := bill_rate_rec.last_update_login;
OKS_BRS_PVT.INSERT_ROW(
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_oks_billrate_schedules_v_tbl => l_bill_rate_tbl_in,
x_oks_billrate_schedules_v_tbl => x_bill_rate_tbl_out);
fnd_file.put_line(FND_FILE.LOG, 'AFTER OKS_BRS_PVT.INSERT_ROW......');
fnd_file.put_line(FND_FILE.LOG, 'Return Status from OKS_BRS_PVT.INSERT_ROW API...'||l_return_status);
UPDATE okc_rules_b
SET rule_information9 = 'Y'
WHERE id = bill_rate_rec.rule_id ;
OKC_RULE_PUB.UPDATE_RULE(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_rulv_tbl => l_rulv_tbl_in,
x_rulv_tbl => l_rulv_tbl_out); */
fnd_file.put_line(FND_FILE.LOG, 'AFTER OKC_RULE_PUB.UPDATE_RULE......Updating RULE_INFORMATION10 to Y');
WHEN G_EXCEPTION_RULE_UPDATE THEN
x_return_status := OKC_API.HANDLE_EXCEPTIONS
( l_api_name,
G_PKG_NAME,
'OKC_API.G_RET_STS_ERROR',
x_msg_count,
x_msg_data,
'_PVT'
);
fnd_file.put_line(FND_FILE.LOG, 'Raised Exception...||G_EXCEPTION_RULE_UPDATE');
WHEN G_EXCEPTION_BRS_UPDATE THEN
x_return_status := OKC_API.HANDLE_EXCEPTIONS
( l_api_name,
G_PKG_NAME,
'OKC_API.G_RET_STS_ERROR',
x_msg_count,
x_msg_data,
'_PVT'
);
fnd_file.put_line(FND_FILE.LOG, 'Raised Exception...||G_EXCEPTION_BRS_UPDATE');
SELECT B.JTF_NOTE_ID JTF_NOTE_ID,
B.SOURCE_OBJECT_CODE SOURCE_OBJECT_CODE,
B.NOTE_STATUS NOTE_STATUS,
B.NOTE_TYPE NOTE_TYPE,
B.NOTES NOTES,
B.NOTES_DETAIL NOTES_DETAIL,
-- Modified by Jvorugan for Bug:4489214 who columns not to be populated from old contract
B.Entered_by Entered_by,
B.Entered_date Entered_date
-- End of changes for Bug:4489214
FROM JTF_NOTES_VL B
WHERE B.SOURCE_OBJECT_ID = l_id
AND B.SOURCE_OBJECT_CODE = P_source_object_code ; -- Bug:5944200
L_Notes_TBL.DELETE;
l_last_updated_by Number := NULL;
l_last_update_login Number := NULL;
SELECT lines2.id orig_line_id,lines2.dnz_chr_id orig_dnz_chr_id,
lines1.Id New_line_ID,lines1.chr_Id New_Chr_Id,
lines1.created_by,
lines1.last_updated_by,
lines1.last_update_login
FROM -- okc_k_lines_v lines1, --new_id
-- okc_k_lines_v lines2 -- old_id
okc_k_lines_b lines1, --Modified by Jvorugan for Bug:4560735
okc_k_lines_b lines2
WHERE lines1.id =l_id
AND lines1.ORIG_SYSTEM_ID1 = lines2.ID;
l_last_updated_by := get_orig_contract_REC.last_updated_by;
l_last_update_login := get_orig_contract_REC.last_update_login; */
, p_last_update_date => SYSDATE
, p_last_updated_by => FND_GLOBAL.USER_ID -- l_last_updated_by Modified for Bug:4489214
, p_last_update_login => FND_GLOBAL.LOGIN_ID -- l_last_update_login Modified for Bug:4489214
, p_attribute1 => NULL
, p_attribute2 => NULL
, p_attribute3 => NULL
, p_attribute4 => NULL
, p_attribute5 => NULL
, p_attribute6 => NULL
, p_attribute7 => NULL
, p_attribute8 => NULL
, p_attribute9 => NULL
, p_attribute10 => NULL
, p_attribute11 => NULL
, p_attribute12 => NULL
, p_attribute13 => NULL
, p_attribute14 => NULL
, p_attribute15 => NULL
, p_context => NULL
, p_jtf_note_contexts_tab => l_jtf_note_contexts_tab);--l_jtf_note_contexts_tab );
SELECT ORIG_SYSTEM_ID1
FROM OKC_K_LINES_B
WHERE ID = p_id ;
SELECT ID,DNZ_CHR_ID, START_DATE, END_DATE
FROM OKC_K_LINES_B
WHERE id = p_id;
SELECT PM_PROGRAM_ID
FROM OKS_K_LINES_B
WHERE cle_id =p_contract_line_id;
SELECT count(*) FROM OKC_K_LINES_B
WHERE id = p_cov_id
AND lse_id in (2,15,20)
and dnz_chr_id < 0;
SELECT ID,DNZ_CHR_ID, START_DATE, END_DATE
FROM OKC_K_LINES_B
WHERE cle_id = p_contract_line_id
AND lse_id IN (2,15,20);
SELECT ID, CLE_ID,
COVERAGE_TYPE,EXCEPTION_COV_ID,SYNC_DATE_INSTALL,
TRANSFER_OPTION, PROD_UPGRADE_YN,INHERITANCE_TYPE,
PM_PROGRAM_ID,
PM_CONF_REQ_YN,PM_SCH_EXISTS_YN,object_version_number
FROM OKS_K_LINES_B
WHERE cle_id = p_cle_id ;
SELECT lines1.id bp_line_id, lines1.start_date start_date, lines1.end_date end_date,
to_number(items.object1_id1) object1_id1,
oks.discount_list discount_list,
oks.offset_period offset_period,
oks.offset_duration offset_duration,
oks.allow_bt_discount allow_bt_discount,
oks.apply_default_timezone apply_default_timezone,
oks.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
FROM okc_k_lines_b lines1,
oks_k_lines_b oks,
okc_k_items items
WHERE lines1.cle_id = p_cle_id
AND items.cle_id = lines1.id
AND items.jtot_object1_code = 'OKX_BUSIPROC'
AND oks.cle_id = lines1.id
AND lines1.lse_id IN (3,16,21)
ORDER BY items.object1_id1, lines1.start_date, lines1.end_date;
SELECT lines1.id bp_line_id,
lines1.start_date start_date,
lines1.end_date end_date,
oks.discount_list discount_list,
oks.offset_period offset_period,
oks.offset_duration offset_duration,
oks.allow_bt_discount allow_bt_discount,
oks.apply_default_timezone apply_default_timezone,
oks.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
FROM okc_k_lines_b lines1,
oks_k_lines_b oks
WHERE lines1.cle_id = p_cle_id
AND oks.cle_id = lines1.id
AND lines1.lse_id IN (3,16,21);
SELECT lines1.id bp_line_id, lines1.dnz_chr_id dnz_chr_id,lines1.start_date start_date, lines1.end_date end_date,
to_number(items.object1_id1) object1_id1
FROM okc_k_lines_b lines1,
okc_k_items items
WHERE lines1.cle_id = p_cle_id
AND items.cle_id = lines1.id
AND items.jtot_object1_code = 'OKX_BUSIPROC'
AND to_number(items.object1_id1) = p_object1_id1
AND lines1.lse_id IN (3,16,21)
AND lines1.orig_system_id1 = p_old_bp_id --New check added to allow duplicate BP
ORDER BY items.object1_id1, lines1.start_date, lines1.end_date;
SELECT lines1.id bp_line_id, lines1.dnz_chr_id dnz_chr_id,lines1.start_date start_date, lines1.end_date end_date
FROM okc_k_lines_b lines1
WHERE lines1.cle_id = p_cle_id
AND lines1.lse_id IN (3,16,21)
AND lines1.orig_system_id1 = p_old_bp_id ;
SELECT lines1.id rt_line_id,
lines1.dnz_chr_id rt_dnz_chr_id,
lines1.line_number rt_line_number,
lines1.start_date start_date,
lines1.end_date end_date,
oks.INCIDENT_SEVERITY_ID,
oks.WORK_THRU_YN,
oks.REACT_ACTIVE_YN,
oks.SFWT_FLAG,
oks.REACT_TIME_NAME,
oks.discount_list discount_list,
oks.offset_period offset_period,
oks.offset_duration offset_duration,
oks.allow_bt_discount allow_bt_discount,
oks.apply_default_timezone apply_default_timezone,
oks.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
FROM okc_k_lines_b lines1,
oks_k_lines_v oks
WHERE lines1.cle_id = p_cle_id
AND oks.cle_id = lines1.id
AND lines1.lse_id IN (4,17,22)
ORDER BY lines1.line_number,lines1.start_date, lines1.end_date;
SELECT RT.ID,
RT.DNZ_CHR_ID
FROM okc_k_lines_b BT,
okc_k_lines_B RT,
okc_k_items BT_Item
WHERE BT.id = P_Bp_line_Id
AND BT.dnz_chr_id = P_new_dnz_chr_id
AND BT.lse_id IN (3,16,21)
AND BT.ID = RT.cle_id
AND RT.lse_id in(4,17,22)
AND BT_ITEM.cle_id = BT.id
AND BT_ITEM.DNZ_CHR_ID =BT.DNZ_CHR_ID
AND to_number(BT_ITEM.Object1_id1) =l_new_Bp_Object1_ID1 --1000
AND RT.dnz_chr_id = BT_ITEM.dnz_chr_id
AND RT.orig_system_id1 = l_orig_system_id1;
SELECT RT.ID,
RT.DNZ_CHR_ID
FROM okc_k_lines_b RT
WHERE RT.cle_id = p_bp_line_id
AND RT.lse_id in(4,17,22)
AND RT.orig_system_id1 = p_old_rt_id;
SELECT ID,
DNZ_CHR_ID,
ACTION_TYPE_CODE,
OBJECT_VERSION_NUMBER
FROM OKS_ACTION_TIME_TYPES
WHERE CLE_ID = P_RT_ID
AND DNZ_CHR_ID = P_DNZ_CHR_ID;
SELECT ID,
COV_ACTION_TYPE_ID,
CLE_ID,
DNZ_CHR_ID,
UOM_CODE,
SUN_DURATION,
MON_DURATION,
TUE_DURATION,
WED_DURATION,
THU_DURATION,
FRI_DURATION,
SAT_DURATION,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER
FROM OKS_ACTION_TIMES
WHERE COV_ACTION_TYPE_ID = p_act_pvt_ID
AND DNZ_CHR_ID = P_Act_Dnz_Chr_ID;
SELECT lines1.id bp_line_id, lines1.start_date start_date, lines1.end_date end_date,
lines2.id bt_line_id, lines2.dnz_chr_id dnz_chr_id,
lines2.object_version_number object_version_number,
lines2.line_number line_number,
to_number(items1.object1_id1) busi_proc_id,
to_number(items2.object1_id1) bill_type_id,
oks.discount_amount discount_amount,
oks.discount_percent discount_percent
FROM okc_k_lines_b lines1,
okc_k_lines_b lines2,
oks_k_lines_b oks,
okc_k_items items1,
okc_k_items items2
WHERE lines1.id = p_id
AND lines2.cle_id = lines1.id
AND items1.cle_id = lines1.id
AND items2.cle_id = lines2.id
AND items1.jtot_object1_code = 'OKX_BUSIPROC'
AND items2.jtot_object1_code = 'OKX_BILLTYPE'
AND oks.cle_id = lines2.id
AND items1.dnz_chr_id = lines1.dnz_chr_id
AND items2.dnz_chr_id = lines2.dnz_chr_id
AND lines1.lse_id IN (3,16,21)
AND lines2.lse_id IN (5,23,59)
ORDER BY busi_proc_id, bill_type_id, lines1.start_date, lines1.end_date;
SELECT lines1.id bt_line_id, lines1.start_date start_date, lines1.end_date end_date,
lines2.dnz_chr_id dnz_chr_id,
lines2.object_version_number object_version_number,
lines1.line_number line_number,
lines2.discount_amount discount_amount,
lines2.discount_percent discount_percent
FROM okc_k_lines_b lines1,
oks_k_lines_b lines2
WHERE lines1.cle_id = p_bp_id
AND lines2.cle_id = lines1.id
AND lines1.lse_id IN (5,23,59);
SELECT lines1.id bp_line_id, lines1.start_date start_date, lines1.end_date end_date,
lines2.id bt_line_id, lines2.dnz_chr_id dnz_chr_id,
lines2.object_version_number object_version_number,
to_number(items1.object1_id1) busi_proc_id,
to_number(items2.object1_id1) bill_type_id
FROM okc_k_lines_b lines1,
okc_k_lines_b lines2,
okc_k_items items1,
okc_k_items items2
WHERE lines1.id = p_id
AND lines2.cle_id = lines1.id
-- AND lines2.line_number = p_line_number
AND items1.cle_id = lines1.id
AND items2.cle_id = lines2.id
AND items1.jtot_object1_code = 'OKX_BUSIPROC'
AND items2.jtot_object1_code = 'OKX_BILLTYPE'
AND to_number(items2.object1_id1) = p_object2_id
AND items1.dnz_chr_id = lines1.dnz_chr_id
AND items2.dnz_chr_id = lines2.dnz_chr_id
AND lines1.lse_id IN (3,16,21)
AND lines2.lse_id IN (5,23,59)
ORDER BY busi_proc_id, bill_type_id, lines1.start_date, lines1.end_date;
SELECT BT.id bt_line_id, BT.start_date start_date,BT.dnz_chr_id,
BT.end_date end_date,BT.object_version_number
FROM okc_k_lines_b BT
WHERE BT.cle_id = p_bp_line_id
AND BT.lse_id IN (5,23,59)
AND BT.orig_system_id1 = p_old_bt_id;
SELECT BTY.id BT_LINE_ID,
BTY.start_date start_date,
BTY.end_date end_date,
BRT.id BR_LINE_ID,
BRT.line_number line_number,
TO_NUMBER(BTY_ITEM.object1_id1) bill_type_id,
BRS.start_hour start_hour,
BRS.start_minute start_minute,
BRS.end_hour end_hour,
BRS.end_minute end_minute,
BRS.monday_flag monday_flag,
BRS.tuesday_flag tuesday_flag,
BRS.wednesday_flag wednesday_flag,
BRS.thursday_flag thursday_flag,
BRS.friday_flag friday_flag,
BRS.saturday_flag saturday_flag,
BRS.sunday_flag sunday_flag,
BRS.object1_id1 object1_id1,
BRS.object1_id2 object1_id2,
BRS.jtot_object1_code jtot_object1_code,
BRS.bill_rate_code bill_rate_code,
BRS.flat_rate flat_rate,
BRS.uom uom,
BRS.holiday_yn holiday_yn,
BRS.percent_over_list_price percent_over_list_price,
BRS.object_version_number object_version_number --Added
FROM okc_k_lines_b BTY,
okc_k_lines_b BRT,
okc_k_items BTY_ITEM,
oks_billrate_schedules BRS
WHERE BTY.id = p_id --274672627862321176435113785401106834939--
AND BTY.lse_id IN (5,23,59)
-- AND BRT.line_number = p_line_number
AND BTY_ITEM.cle_id = BTY.id
AND BTY_ITEM.dnz_chr_id = BTY.dnz_chr_id
AND BTY_ITEM.jtot_object1_code = 'OKX_BILLTYPE'
AND TO_NUMBER(BTY_ITEM.object1_id1) = p_object1_id1
AND BTY.id = BRT.cle_id
AND BRT.lse_id IN (6,24,60)
AND BRS.cle_id = BRT.id
ORDER BY BRT.line_number ,BTY.start_date,BTY.end_date,bill_type_id;
SELECT
BRS.cle_id,
BRS.start_hour start_hour,
BRS.start_minute start_minute,
BRS.end_hour end_hour,
BRS.end_minute end_minute,
BRS.monday_flag monday_flag,
BRS.tuesday_flag tuesday_flag,
BRS.wednesday_flag wednesday_flag,
BRS.thursday_flag thursday_flag,
BRS.friday_flag friday_flag,
BRS.saturday_flag saturday_flag,
BRS.sunday_flag sunday_flag,
BRS.object1_id1 object1_id1,
BRS.object1_id2 object1_id2,
BRS.jtot_object1_code jtot_object1_code,
BRS.bill_rate_code bill_rate_code,
BRS.flat_rate flat_rate,
BRS.uom uom,
BRS.holiday_yn holiday_yn,
BRS.percent_over_list_price percent_over_list_price,
BRS.object_version_number object_version_number --Added
FROM oks_billrate_schedules BRS
WHERE BRS.bt_cle_id = p_id ;
SELECT BTY.ID BT_LINE_ID,
BTY.start_date start_date,
BTY.end_date end_date,
BTY.line_number line_number,
BRT.ID BR_LINE_ID,
BRT.dnz_chr_id dnz_chr_id,
TO_NUMBER(BTY_ITEM.object1_id1) bill_type_id
FROM okc_k_lines_b BTY,
okc_k_lines_b BRT,
okc_k_items BTY_ITEM
WHERE BTY.id =p_id
-- AND BTY.line_Number = p_line_number
AND BTY.lse_id IN (5,23,59)
AND BTY.id = BTY_ITEM.cle_id
AND BTY_ITEM.dnz_chr_id = BTY.dnz_chr_id
AND BTY_ITEM.jtot_object1_code = 'OKX_BILLTYPE'
AND BRT.cle_id = BTY.id
AND BRT.lse_id IN (6,24,60)
AND TO_NUMBER(BTY_ITEM.object1_id1) = p_object1_id1
ORDER BY BTY.start_date,BTY.end_date,bill_type_id;
SELECT BRT.ID BRS_LINE_ID,
BRT.CLE_ID BRS_CLE_LINE_ID,
BRT.dnz_chr_id dnz_chr_id
FROM okc_k_lines_b BRT
WHERE BRT.cle_id = p_bt_id
AND BRT.lse_id IN (6,24,60)
AND BRT.orig_system_id1 = p_old_brs_id;
SELECT lines1.id OLD_BP_LINE_ID,
to_number(items.object1_id1) old_busi_proc_id
FROM okc_k_lines_b lines1,
okc_k_items items
WHERE lines1.cle_id = p_cle_id
AND lines1.lse_id IN (3,16,21)
AND items.cle_id = lines1.id
AND items.jtot_object1_code = 'OKX_BUSIPROC'
AND items.dnz_chr_id = lines1.dnz_chr_id;
SELECT tze.id timezone_line_id, tze.cle_id timezone_cle_id,
tze.timezone_id timezone_id,tze.default_yn default_yn,
tze.dnz_chr_id tze_dnz_chr_id,tze.object_version_number tze_object_version_number,
to_number(items.object1_id1) busi_proc_id
FROM OKC_K_LINES_B lines1,
okc_k_items items,
oks_coverage_timezones tze
WHERE lines1.id = p_id
AND lines1.cle_id = p_cle_id
AND lines1.lse_id IN (3,16,21)
AND items.cle_id = lines1.id
and items.jtot_object1_code = 'OKX_BUSIPROC'
And items.dnz_chr_id = lines1.dnz_chr_id
And lines1.dnz_chr_id = tze.dnz_chr_id
AND tze.cle_id = lines1.id
ORDER BY to_number(items.object1_id1),
lines1.start_date, lines1.end_date, tze.timezone_id;
SELECT tze.id timezone_line_id, tze.cle_id timezone_cle_id,
tze.timezone_id timezone_id,tze.default_yn default_yn,
tze.dnz_chr_id tze_dnz_chr_id,tze.object_version_number tze_object_version_number
FROM oks_coverage_timezones tze
WHERE tze.cle_id = p_bp_line_id;
SELECT times.id cover_time_line_id,
times.dnz_chr_id times_dnz_chr_id,
times.start_hour start_hour,
times.start_minute start_minute,
times.end_hour end_hour,
times.end_minute end_minute,
times.monday_yn monday_yn,
times.tuesday_yn tuesday_yn,
times.wednesday_yn wednesday_yn,
times.thursday_yn thursday_yn,
times.friday_yn friday_yn,
times.saturday_yn saturday_yn,
times.sunday_yn sunday_yn,
times.object_version_number object_version_number
FROM OKS_COVERAGE_TIMES TIMES
WHERE TIMES.COV_TZE_LINE_ID = p_cle_id
AND TIMES.DNZ_CHR_ID = p_dnz_chr_id;
SELECT lines1.id NEW_BP_LINE_ID, lines1.dnz_chr_id new_dnz_chr_id,
to_number(items1.object1_id1) busi_proc_id
FROM okc_k_lines_b lines1,
okc_k_items items1
WHERE lines1.cle_id = p_cle_id
AND lines1.lse_id IN (3,16,21)
AND items1.cle_id = lines1.id
AND items1.jtot_object1_code = 'OKX_BUSIPROC'
AND to_number(items1.object1_id1) = p_busi_proc_id
ORDER BY to_number(items1.object1_id1), lines1.start_date, lines1.end_date ;
SELECT id,
dnz_chr_id
FROM okc_k_lines_b
WHERE cle_id = p_id
AND dnz_chr_id = P_Dnz_Chr_ID;
SELECT 1 FROM OKS_K_LINES_B
WHERE cle_id = p_new_cle_id
AND rownum=1;
l_klnv_tbl_in.DELETE;
OKS_CTZ_PVT.INSERT_ROW(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_oks_coverage_timezones_v_tbl => l_timezone_tbl_in,
x_oks_coverage_timezones_v_tbl => l_timezone_tbl_out);
okc_debug.log('After OKS_CTZ_PVT INSERT_ROW'||l_return_status, 2);
OKS_CVT_PVT.INSERT_ROW(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_oks_coverage_times_v_tbl => l_cover_time_tbl_in,
x_oks_coverage_times_v_tbl => l_cover_time_tbl_out);
l_timezone_tbl_in.DELETE;
l_cover_time_tbl_in.DELETE;
l_klnv_tbl_in.DELETE;
l_klnv_tbl_in.DELETE;
l_act_pvt_tbl_in.DELETE;
oks_act_pvt.insert_row(
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_oks_action_time_types_v_tbl => l_act_pvt_tbl_in,
x_oks_action_time_types_v_tbl => l_act_pvt_tbl_out);
okc_debug.log('After oks_act_pvt insert_row'||l_return_status, 2);
l_acm_pvt_tbl_in.DELETE;
OKS_ACM_PVT.insert_row(
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_oks_action_times_v_tbl => l_acm_pvt_tbl_in,
x_oks_action_times_v_tbl => l_acm_pvt_tbl_out);
okc_debug.log('After OKS_ACM_PVT insert_row'||l_return_status, 2);
l_acm_pvt_tbl_in.DELETE;
l_act_pvt_tbl_in.DELETE;
l_klnv_tbl_in.DELETE;
l_klnv_tbl_in.DELETE;
l_klnv_tbl_in.DELETE;
l_klnv_tbl_in.DELETE;
l_billrate_sch_tbl_in.DELETE;
oks_brs_pvt.insert_row(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_oks_billrate_schedules_v_tbl => l_billrate_sch_tbl_in,
x_oks_billrate_schedules_v_tbl => l_billrate_sch_tbl_out);
okc_debug.log('After oks_brs_pvt insert_row'||l_return_status, 2);
l_klnv_tbl_in.DELETE;
l_billrate_sch_tbl_in.DELETE;
l_timezone_tbl_in.DELETE;
l_cover_time_tbl_in.DELETE;
x_klnv_tbl(1).LAST_UPDATED_BY := OKC_API.G_MISS_NUM;
x_klnv_tbl(1).LAST_UPDATE_DATE := OKC_API.G_MISS_DATE;
x_klnv_tbl(1).LAST_UPDATE_LOGIN := OKC_API.G_MISS_NUM;
x_timezone_tbl(1).LAST_UPDATED_BY := OKC_API.G_MISS_NUM;
x_timezone_tbl(1).LAST_UPDATE_DATE := OKC_API.G_MISS_DATE;
x_timezone_tbl(1).LAST_UPDATE_LOGIN := OKC_API.G_MISS_NUM;
x_timezone_tbl(1).PROGRAM_UPDATE_DATE := OKC_API.G_MISS_DATE;
x_cover_time_tbl(1).LAST_UPDATED_BY := OKC_API.G_MISS_NUM;
x_cover_time_tbl(1).LAST_UPDATE_DATE := OKC_API.G_MISS_DATE;
x_cover_time_tbl(1).LAST_UPDATE_LOGIN := OKC_API.G_MISS_NUM;
x_cover_time_tbl(1).PROGRAM_UPDATE_DATE := OKC_API.G_MISS_DATE;
x_act_time_tbl(1).program_update_date := OKC_API.G_MISS_DATE;
x_act_time_tbl(1).last_updated_by := OKC_API.G_MISS_NUM;
x_act_time_tbl(1).last_update_date := OKC_API.G_MISS_DATE;
x_act_time_tbl(1).last_update_login := OKC_API.G_MISS_NUM;
x_act_type_tbl(1).program_update_date := OKC_API.G_MISS_DATE;
x_act_type_tbl(1).last_updated_by := OKC_API.G_MISS_NUM;
x_act_type_tbl(1).last_update_date := OKC_API.G_MISS_DATE;
x_act_type_tbl(1).last_update_login := OKC_API.G_MISS_NUM;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_COVERAGE_TIMES_V
WHERE COV_TZE_LINE_ID = l_tze_id
AND MONDAY_YN = 'Y' ;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_COVERAGE_TIMES_V
WHERE COV_TZE_LINE_ID = l_tze_id
AND TUESDAY_YN = 'Y' ;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_COVERAGE_TIMES_V
WHERE COV_TZE_LINE_ID = l_tze_id
AND WEDNESDAY_YN = 'Y' ;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_COVERAGE_TIMES_V
WHERE COV_TZE_LINE_ID = l_tze_id
AND THURSDAY_YN = 'Y' ;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_COVERAGE_TIMES_V
WHERE COV_TZE_LINE_ID = l_tze_id
AND FRIDAY_YN = 'Y' ;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_COVERAGE_TIMES_V
WHERE COV_TZE_LINE_ID = l_tze_id
AND SATURDAY_YN = 'Y' ;
SELECT to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) start_time,
to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) end_time
FROM OKS_COVERAGE_TIMES_V
WHERE COV_TZE_LINE_ID = l_tze_id
AND SUNDAY_YN = 'Y' ;
SELECT meaning from fnd_lookups where lookup_type = 'DAY_NAME'
and lookup_code = p_day_code;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
l_time_tbl.DELETE;
PROCEDURE Delete_History(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER) IS
l_chr_id CONSTANT NUMBER := p_chr_id;
DELETE OKS_ACTION_TIME_TYPES_H
WHERE dnz_chr_id = l_chr_id;
DELETE OKS_ACTION_TIMES_H
WHERE dnz_chr_id = l_chr_id;
DELETE OKS_COVERAGE_TIMES_H
WHERE dnz_chr_id = l_chr_id;
DELETE OKS_COVERAGE_TIMEZONES_H
WHERE dnz_chr_id = l_chr_id;
DELETE OKS_BILLRATE_SCHEDULES_H
WHERE dnz_chr_id = l_chr_id;
OKS_PM_PROGRAMS_PVT.Delete_PMHistory(
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 => l_chr_id);
END Delete_History;
PROCEDURE Delete_Saved_Version(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER) IS
l_api_version NUMBER := 1;
l_api_name VARCHAR2(30):= 'Delete_Saved_Version';
DELETE OKS_ACTION_TIME_TYPES_H
WHERE dnz_chr_id = l_chr_id
And major_version = -1;
DELETE OKS_ACTION_TIMES_H
WHERE dnz_chr_id = l_chr_id
And major_version = -1;
DELETE OKS_COVERAGE_TIMES_H
WHERE dnz_chr_id = l_chr_id
And major_version = -1;
DELETE OKS_COVERAGE_TIMEZONES_H
WHERE dnz_chr_id = l_chr_id
And major_version = -1;
DELETE OKS_BILLRATE_SCHEDULES_H
WHERE dnz_chr_id = l_chr_id
And major_version = -1;
OKS_PM_PROGRAMS_PVT.Delete_PMSaved_Version(
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 => l_chr_id);
END Delete_Saved_Version;
l_last_updated_by NUMBER := NULL;
l_last_update_login NUMBER := NULL;
SELECT ORIG_SYSTEM_ID1, Created_By,LAst_Updated_By,Last_Update_Login
FROM OKC_K_HEADERS_B
WHERE ID = l_chr_id;
l_last_updated_by := Get_Orig_System_Id_Rec.last_updated_by;
l_last_update_login := Get_Orig_System_Id_Rec.last_update_login; */
, p_last_update_date => SYSDATE
, p_last_updated_by => FND_GLOBAL.USER_ID -- l_last_updated_by Modified for Bug:4489214
, p_last_update_login => FND_GLOBAL.LOGIN_ID -- l_last_update_login Modified for Bug:4489214
, p_attribute1 => NULL
, p_attribute2 => NULL
, p_attribute3 => NULL
, p_attribute4 => NULL
, p_attribute5 => NULL
, p_attribute6 => NULL
, p_attribute7 => NULL
, p_attribute8 => NULL
, p_attribute9 => NULL
, p_attribute10 => NULL
, p_attribute11 => NULL
, p_attribute12 => NULL
, p_attribute13 => NULL
, p_attribute14 => NULL
, p_attribute15 => NULL
, p_context => NULL
, p_jtf_note_contexts_tab => l_jtf_note_contexts_tab);
PROCEDURE Update_dnz_chr_id
(p_coverage_id IN NUMBER ,
p_dnz_chr_id IN NUMBER) IS
-- coverage --
l_clev_tbl_in okc_contract_pub.clev_tbl_type;
Select id,dnz_chr_id from OKC_K_LINES_B
Where cle_id = p_id
And Lse_id in (4,17);
Select id,dnz_chr_id,object_version_number from OKS_ACTION_TIMES
where COV_ACTION_TYPE_ID = p_id;
Select id,dnz_chr_id,object_version_number from OKS_ACTION_TIME_TYPES
where cle_id = p_id
AND ACTION_TYPE_CODE IN ('RCN', 'RSN');
Select id,dnz_chr_id,cle_id,sfwt_flag,object_version_number from OKS_K_LINES_V
where cle_id = p_id;
SELECT id,dnz_chr_id,object_version_number from OKS_COVERAGE_TIMEZONES
WHERE cle_id = l_cle_id;
SELECT id,dnz_chr_id,object_version_number from OKS_COVERAGE_TIMES
WHERE cov_tze_line_id = l_cle_id;
SELECT id,dnz_chr_id from okc_k_lines_b
WHERE cle_id = l_cle_id
AND LSE_ID IN (3,16);
Select id,dnz_chr_id,chr_id,cpl_id,primary_yn,small_business_flag,women_owned_flag,cle_id,
JTOT_OBJECT1_CODE,object1_id1,rle_code from OKC_K_PARTY_ROLES_V
Where cle_id = p_id
And dnz_chr_id = p_dnz_chr_id;
Select id,dnz_chr_id,cro_code,cpl_id,primary_yn,resource_class from OKC_CONTACTS
Where cpl_id = p_id;
Select id,dnz_chr_id from OKC_K_LINES_B
where cle_id=p_id
and lse_id in ( 5,59,23);
Select id,dnz_chr_id,jtot_object1_code,object1_id1,cle_id,chr_id,cle_id_for,exception_yn,
PRICED_ITEM_YN,uom_code from OKC_K_ITEMS
where cle_id = p_id;
Select id,dnz_chr_id,object_version_number from OKS_BILLRATE_SCHEDULES
Where CLE_ID =p_id;
Select id,dnz_chr_id from OKC_K_LINES_B
where cle_id=p_id
and lse_id in (6,60,24);
Select dnz_chr_id from OKC_K_LINES_B
where id =p_id ;
Select id,dnz_chr_id ,object_version_number
from OKS_PM_ACTIVITIES
Where cle_id=p_id;
Select id , dnz_chr_id,OBJECT_VERSION_NUMBER
from OKS_PM_SCHEDULES
Where cle_id=p_id;
Select id,dnz_chr_id ,OBJECT_VERSION_NUMBER
from OKS_PM_STREAM_LEVELS
Where cle_id=p_id;
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_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out
);
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_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => l_validate_yn);
oks_pma_pvt.update_row (
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_pmav_tbl => l_pmav_tbl_in,
x_pmav_tbl => l_pmav_tbl_out);
OKS_PMS_PVT.update_row(
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_oks_pm_schedules_v_tbl => l_pms_tbl_in,
x_oks_pm_schedules_v_tbl =>l_pms_tbl_out);
oks_pml_pvt.update_row (
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_pmlv_tbl => l_pmlv_tbl_in,
x_pmlv_tbl => l_pmlv_tbl_out
);
okc_contract_item_pub.update_contract_item (
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_cimv_tbl => l_cimv_tbl_in,
x_cimv_tbl => l_cimv_tbl_out );
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_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => l_validate_yn);
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_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
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_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => l_validate_yn);
OKS_ACT_PVT.Update_ROW(
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_oks_action_time_types_v_tbl => l_act_type_tbl_in,
x_oks_action_time_types_v_tbl => l_act_type_tbl_out);
OKS_ACM_PVT.UPDATE_ROW( 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_oks_action_times_v_tbl => l_act_time_tbl_in,
x_oks_action_times_v_tbl => l_act_time_tbl_out);
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_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out );
okc_contract_party_pub.update_contact (
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_ctcv_tbl => l_ctcv_tbl_in,
x_ctcv_tbl => l_ctcv_tbl_out );
okc_contract_party_pub.update_k_party_role (
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_cplv_tbl => l_cplv_tbl_in,
x_cplv_tbl => l_cplv_tbl_out );
okc_contract_item_pub.update_contract_item (
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_cimv_tbl => l_cimv_tbl_in,
x_cimv_tbl => l_cimv_tbl_out );
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_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => l_validate_yn);
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_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out );
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_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => l_validate_yn);
OKS_BRS_PVT.UPDATE_ROW(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_oks_billrate_schedules_v_tbl => l_bill_rate_tbl_in ,
x_oks_billrate_schedules_v_tbl => l_bill_rate_tbl_out);
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_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
OKS_CTZ_PVT.UPDATE_ROW(
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_oks_coverage_timezones_v_tbl => l_timezone_tbl_in,
x_oks_coverage_timezones_v_tbl => l_timezone_tbl_out);
OKS_CVT_PVT.update_row(
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_oks_coverage_times_v_tbl => l_cov_time_tbl_in,
x_oks_coverage_times_v_tbl =>l_cov_time_tbl_out);
End Update_dnz_chr_id;
SELECT jtf_note_id,
parent_note_id,
source_object_code,
source_number,
notes, --
notes_detail,
note_status,
source_object_meaning,
note_type,
note_type_meaning,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
note_status_meaning,
decoded_source_code,
decoded_source_meaning,
context
FROM JTF_NOTES_VL
WHERE source_object_id = p_source_object_id
AND source_object_code = p_source_object_code -- 'OKS_COVTMPL_NOTE'
AND note_status <> (case when p_source_object_code ='OKS_COVTMPL_NOTE' THEN 'P' ELSE '!' END);
SELECT start_date,
end_date
FROM OKC_K_LINES_B
WHERE ID = p_tgt_line_id; --p_contract_line_id;
SELECT pm_program_id,pm_sch_exists_yn,pm_conf_req_yn
FROM OKS_K_LINES_B
WHERE CLE_ID = p_src_line_id; --p_standard_cov_id; -- modified by Jvorugan Bug:4535339 ID = p_standard_cov_id;
SELECT lse_id
FROM OKC_K_LINES_B
WHERE ID = p_src_line_id; --p_standard_cov_id;
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_attribute1 => notes_rec.ATTRIBUTE1,
p_attribute2 => notes_rec.ATTRIBUTE2,
p_attribute3 => notes_rec.ATTRIBUTE3,
p_attribute4 => notes_rec.ATTRIBUTE4,
p_attribute5 => notes_rec.ATTRIBUTE5,
p_attribute6 => notes_rec.ATTRIBUTE6,
p_attribute7 => notes_rec.ATTRIBUTE7,
p_attribute8 => notes_rec.ATTRIBUTE8,
p_attribute9 => notes_rec.ATTRIBUTE9,
p_attribute10 => notes_rec.ATTRIBUTE10,
p_attribute11 => notes_rec.ATTRIBUTE11,
p_attribute12 => notes_rec.ATTRIBUTE12,
p_attribute13 => notes_rec.ATTRIBUTE13,
p_attribute14 => notes_rec.ATTRIBUTE14,
p_attribute15 => notes_rec.ATTRIBUTE15,
p_context => notes_rec.CONTEXT,
p_note_type => notes_rec.NOTE_TYPE);
update oks_k_lines_b set pm_program_id =l_pm_prog_id,
pm_sch_exists_yn=l_pm_sch_exists_yn,
pm_conf_req_yn =l_pm_conf_req_yn
where cle_id = p_tgt_line_id; --p_contract_line_id;
SELECT id from okc_k_lines_b
where cle_id=p_cle_id;
SELECT ID,lse_id
FROM OKC_K_LINES_B
WHERE cle_Id=P_cle_Id;
SELECT ID
FROM OKC_K_ITEMS_V
WHERE cle_Id=P_Id;
SELECT ID,lse_id
FROM OKC_K_LINES_B
WHERE cle_Id=P_cle_Id ;
SELECT pr.ID
FROM OKC_K_Party_Roles_V pr,
OKC_K_LINES_B lv
WHERE pr.cle_ID=p_cle_Id
AND pr.Rle_Code=P_Role_Code
AND pr.cle_id = lv.id
AND pr.dnz_chr_id = lv.dnz_chr_id ;
SELECT ID
FROM OKC_CONTACTS_V
WHERE cpl_id=P_cpl_Id;
SELECT ID
FROM OKC_K_LINES_B
WHERE cle_Id=P_cle_Id;
INSERT INTO okc_k_lines_b
(
ID,
LINE_NUMBER,
CHR_ID,
CLE_ID,
CLE_ID_RENEWED,
DNZ_CHR_ID,
DISPLAY_SEQUENCE,
STS_CODE,
TRN_CODE,
LSE_ID,
EXCEPTION_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
HIDDEN_IND,
PRICE_NEGOTIATED,
PRICE_LEVEL_IND,
PRICE_UNIT,
PRICE_UNIT_PERCENT,
INVOICE_LINE_LEVEL_IND,
DPAS_RATING,
TEMPLATE_USED,
PRICE_TYPE,
CURRENCY_CODE,
LAST_UPDATE_LOGIN,
DATE_TERMINATED,
START_DATE,
END_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SECURITY_GROUP_ID,
CLE_ID_RENEWED_TO,
PRICE_NEGOTIATED_RENEWED,
CURRENCY_CODE_RENEWED,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
DATE_RENEWED,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
PRICE_LIST_ID,
PRICE_LIST_LINE_ID,
LINE_LIST_PRICE,
ITEM_TO_PRICE_YN,
PRICING_DATE,
PRICE_BASIS_YN,
CONFIG_HEADER_ID,
CONFIG_REVISION_NUMBER,
CONFIG_COMPLETE_YN,
CONFIG_VALID_YN,
CONFIG_TOP_MODEL_LINE_ID,
CONFIG_ITEM_TYPE,
CONFIG_ITEM_ID,
SERVICE_ITEM_YN,
PH_PRICING_TYPE,
PH_PRICE_BREAK_BASIS,
PH_MIN_QTY,
PH_MIN_AMT,
PH_QP_REFERENCE_ID,
PH_VALUE,
PH_ENFORCE_PRICE_LIST_YN,
PH_ADJUSTMENT,
PH_INTEGRATED_WITH_QP,
CUST_ACCT_ID,
BILL_TO_SITE_USE_ID,
INV_RULE_ID,
LINE_RENEWAL_TYPE_CODE,
SHIP_TO_SITE_USE_ID,
PAYMENT_TERM_ID,
DATE_CANCELLED,
-- CANC_REASON_CODE,
-- TRXN_EXTENSION_ID,
TERM_CANCEL_SOURCE,
ANNUALIZED_FACTOR )
SELECT
p_new_line_id ID,
LINE_NUMBER,
CHR_ID,
p_cle_id CLE_ID,
CLE_ID_RENEWED,
DNZ_CHR_ID,
DISPLAY_SEQUENCE,
STS_CODE,
TRN_CODE,
LSE_ID,
EXCEPTION_YN,
1 OBJECT_VERSION_NUMBER,
FND_GLOBAL.USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
FND_GLOBAL.USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
HIDDEN_IND,
PRICE_NEGOTIATED,
PRICE_LEVEL_IND,
PRICE_UNIT,
PRICE_UNIT_PERCENT,
INVOICE_LINE_LEVEL_IND,
DPAS_RATING,
TEMPLATE_USED,
PRICE_TYPE,
CURRENCY_CODE,
FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
DATE_TERMINATED,
l_start_date START_DATE,
NULL END_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SECURITY_GROUP_ID,
CLE_ID_RENEWED_TO,
PRICE_NEGOTIATED_RENEWED,
CURRENCY_CODE_RENEWED,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
DATE_RENEWED,
ORIG_SYSTEM_SOURCE_CODE, -- CHECK IF THIS NEED TO BE POPULATED
p_old_line_id ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
PRICE_LIST_ID,
PRICE_LIST_LINE_ID,
LINE_LIST_PRICE,
ITEM_TO_PRICE_YN,
PRICING_DATE,
PRICE_BASIS_YN,
CONFIG_HEADER_ID,
CONFIG_REVISION_NUMBER,
CONFIG_COMPLETE_YN,
CONFIG_VALID_YN,
CONFIG_TOP_MODEL_LINE_ID,
CONFIG_ITEM_TYPE,
CONFIG_ITEM_ID,
SERVICE_ITEM_YN,
PH_PRICING_TYPE,
PH_PRICE_BREAK_BASIS,
PH_MIN_QTY,
PH_MIN_AMT,
PH_QP_REFERENCE_ID,
PH_VALUE,
PH_ENFORCE_PRICE_LIST_YN,
PH_ADJUSTMENT,
PH_INTEGRATED_WITH_QP,
CUST_ACCT_ID,
BILL_TO_SITE_USE_ID,
INV_RULE_ID,
LINE_RENEWAL_TYPE_CODE,
SHIP_TO_SITE_USE_ID,
PAYMENT_TERM_ID,
DATE_CANCELLED,
-- CANC_REASON_CODE,
-- TRXN_EXTENSION_ID,
TERM_CANCEL_SOURCE,
ANNUALIZED_FACTOR
FROM okc_k_lines_b
WHERE id = p_old_line_id;
INSERT INTO okc_k_lines_tl
( ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
NAME,
COMMENTS,
ITEM_DESCRIPTION,
BLOCK23TEXT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
OKE_BOE_DESCRIPTION,
COGNOMEN )
SELECT
p_new_line_id ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
l_coverage_name NAME,
COMMENTS,
ITEM_DESCRIPTION,
BLOCK23TEXT,
FND_GLOBAL.USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
FND_GLOBAL.USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
OKE_BOE_DESCRIPTION,
COGNOMEN
FROM okc_k_lines_tl
where id=p_old_line_id;
INSERT INTO okc_k_items
( ID,
CLE_ID,
CHR_ID,
CLE_ID_FOR,
DNZ_CHR_ID,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
UOM_CODE,
EXCEPTION_YN,
NUMBER_OF_ITEMS,
PRICED_ITEM_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID )
SELECT
p_new_item_id ID,
p_cle_id CLE_ID,
CHR_ID,
CLE_ID_FOR,
DNZ_CHR_ID,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
UOM_CODE,
EXCEPTION_YN,
NUMBER_OF_ITEMS,
PRICED_ITEM_YN,
1 OBJECT_VERSION_NUMBER,
FND_GLOBAL.USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
FND_GLOBAL.USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
UPG_ORIG_SYSTEM_REF,
p_old_item_id UPG_ORIG_SYSTEM_REF_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID
FROM OKC_K_ITEMS
WHERE ID= p_old_item_id;
INSERT INTO okc_k_party_roles_b
( ID,
CHR_ID,
CLE_ID,
DNZ_CHR_ID,
RLE_CODE,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CODE,
FACILITY,
MINORITY_GROUP_LOOKUP_CODE,
SMALL_BUSINESS_FLAG,
WOMEN_OWNED_FLAG,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SECURITY_GROUP_ID,
CPL_ID,
PRIMARY_YN,
BILL_TO_SITE_USE_ID,
CUST_ACCT_ID,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
ORIG_SYSTEM_SOURCE_CODE)
SELECT
p_new_party_id ID,
CHR_ID,
p_cle_id CLE_ID,
DNZ_CHR_ID,
RLE_CODE,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
OBJECT_VERSION_NUMBER,
FND_GLOBAL.USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
FND_GLOBAL.USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
CODE,
FACILITY,
MINORITY_GROUP_LOOKUP_CODE,
SMALL_BUSINESS_FLAG,
WOMEN_OWNED_FLAG,
FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SECURITY_GROUP_ID,
CPL_ID,
PRIMARY_YN,
BILL_TO_SITE_USE_ID,
CUST_ACCT_ID,
p_old_party_id ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
ORIG_SYSTEM_SOURCE_CODE
FROM OKC_K_PARTY_ROLES_B
WHERE ID= p_old_party_id;
INSERT INTO okc_k_party_roles_tl
( ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
COGNOMEN,
ALIAS,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID
)
SELECT
p_new_party_id ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
FND_GLOBAL.USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
FND_GLOBAL.USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
COGNOMEN,
ALIAS,
FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID
FROM okc_k_party_roles_tl
where id=p_old_party_id;
INSERT INTO okc_contacts
( ID,
CPL_ID,
CRO_CODE,
DNZ_CHR_ID,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CONTACT_SEQUENCE,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SECURITY_GROUP_ID,
START_DATE,
END_DATE,
PRIMARY_YN,
RESOURCE_CLASS,
SALES_GROUP_ID,
DNZ_STE_CODE)
--ORIG_SYSTEM_ID)
SELECT
okc_p_util.raw_to_number(sys_guid()),
p_new_cpl_id CPL_ID, -- new party id (CPL_ID)
CRO_CODE,
DNZ_CHR_ID,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
OBJECT_VERSION_NUMBER,
FND_GLOBAL.USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
FND_GLOBAL.USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
CONTACT_SEQUENCE,
FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SECURITY_GROUP_ID,
START_DATE,
END_DATE,
PRIMARY_YN,
RESOURCE_CLASS,
SALES_GROUP_ID,
DNZ_STE_CODE
--ID --ORIG_SYSTEM_ID
FROM OKC_CONTACTS
WHERE cpl_id = p_old_cpl_id;