The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
LINE.ID LINE_ID,
Line.Created_By Line_Created_By,
Line.Creation_Date Line_Creation_Date,
Line.Last_Updated_By Line_Last_Updated_By,
Line.Last_Update_Date Line_Last_Update_Date,
Line.Last_Update_Login Line_Last_Update_Login,
RGP.ID LINE_RGP_ID,
Rul.RowID Rul_Row_ID ,
Rul.ID Rule_ID,
LINE.LSE_ID LINE_LSE_ID,
LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
OBJECT1_ID1 LINE_OBJECT1_ID1,
OBJECT2_ID1 LINE_OBJECT2_ID1,
RULE_INFORMATION1 COV_RULE_INFO1,
RULE_INFORMATION2 COV_RULE_INFO2,
RULE_INFORMATION3 COV_RULE_INFO3,
RULE_INFORMATION4 COV_RULE_INFO4,
RULE_INFORMATION5 COV_RULE_INFO5,
RULE_INFORMATION6 COV_RULE_INFO6,
RULE_INFORMATION7 COV_RULE_INFO7,
RULE_INFORMATION8 COV_RULE_INFO8,
RULE_INFORMATION9 COV_RULE_INFO9,
RULE_INFORMATION10 COV_RULE_INFO10,
RULE_INFORMATION11 COV_RULE_INFO11,
RULE_INFORMATION12 COV_RULE_INFO12,
RULE_INFORMATION13 COV_RULE_INFO13,
RULE_INFORMATION14 COV_RULE_INFO14,
RULE_INFORMATION15 COV_RULE_INFO15,
RULE_INFORMATION_CATEGORY COV_RULE_INFO,
RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER
FROM
OKC_RULE_GROUPS_B RGP,
OKC_RULES_B RUL,
OKC_K_LINES_B LINE
WHERE LINE.ID = RGP.CLE_ID
AND RGP.ID = RUL.RGP_ID
AND LINE.LSE_ID IN (2,15,20)
-- AND RUL.RULE_INFORMATION_CATEGORY IN ('ECE','WHE','UGE','STR','CVE','PMP')
AND LINE.DNZ_CHR_ID = RGP.DNZ_CHR_ID
-- AND RUL.RULE_INFORMATION15 IS NULL
AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_B WHERE CLE_ID = LINE.ID)
AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
ORDER BY LINE.ID;
Line_Last_Updated_By_TBL Num_Tbl_Type;
Line_Last_Update_Date_TBL Date_Tbl_Type;
Line_Last_Update_Login_TBL Num_Tbl_Type;
SELECT UOM_CODE,Duration
FROM OKC_TIMEVALUES_V
WHERE id = l_id;
l_clev_tbl_in.DELETE;
Line_Last_Updated_By_TBL ,
Line_Last_Update_Date_TBL ,
Line_Last_Update_Login_TBL ,
LINE_RGP_ID_TBL ,
Rul_Row_ID_TBl ,
Rule_Id_Tbl ,
LINE_LSE_ID_TBL ,
LINE_DNZ_CHR_ID_TBL ,
LINE_OBJECT1_ID1_TBL ,
LINE_OBJECT2_ID1_TBL ,
COV_RULE_INFO1_TBL ,
COV_RULE_INFO2_TBL ,
COV_RULE_INFO3_TBL ,
COV_RULE_INFO4_TBL ,
COV_RULE_INFO5_TBL ,
COV_RULE_INFO6_TBL ,
COV_RULE_INFO7_TBL ,
COV_RULE_INFO8_TBL ,
COV_RULE_INFO9_TBL ,
COV_RULE_INFO10_TBL ,
COV_RULE_INFO11_TBL ,
COV_RULE_INFO12_TBL ,
COV_RULE_INFO13_TBL ,
COV_RULE_INFO14_TBL ,
COV_RULE_INFO15_TBL ,
COV_RULE_INFO_TBL ,
COV_OBJ_VER_NUMBER_TBL
LIMIT 1000;--20;
l_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
l_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
l_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
oks_kln_pvt.insert_row
(
x_return_status => l_return_status,
p_klnv_tbl => l_clev_tbl_in,
p_api_version => 1,
p_init_msg_list => null,-- Could Not Found use standard
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_klnv_tbl => x_clev_tbl_in
);
SELECT
LINE.ID LINE_ID,
Line.Created_By Line_Created_By,
Line.Creation_Date Line_Creation_Date,
Line.Last_Updated_By Line_Last_Updated_By,
Line.Last_Update_Date Line_Last_Update_Date,
Line.Last_Update_Login Line_Last_Update_Login,
Rul.ID Rule_Id,
RGP.ID LINE_RGP_ID,
Rul.RowId Rul_Row_Id,
LINE.LSE_ID LINE_LSE_ID,
LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
OBJECT1_ID1 LINE_OBJECT1_ID1,
OBJECT2_ID1 LINE_OBJECT2_ID1,
RULE_INFORMATION1 COV_RULE_INFO1,
RULE_INFORMATION2 COV_RULE_INFO2,
RULE_INFORMATION3 COV_RULE_INFO3,
RULE_INFORMATION4 COV_RULE_INFO4,
RULE_INFORMATION5 COV_RULE_INFO5,
RULE_INFORMATION6 COV_RULE_INFO6,
RULE_INFORMATION7 COV_RULE_INFO7,
RULE_INFORMATION8 COV_RULE_INFO8,
RULE_INFORMATION9 COV_RULE_INFO9,
RULE_INFORMATION10 COV_RULE_INFO10,
RULE_INFORMATION11 COV_RULE_INFO11,
RULE_INFORMATION12 COV_RULE_INFO12,
RULE_INFORMATION13 COV_RULE_INFO13,
RULE_INFORMATION14 COV_RULE_INFO14,
RULE_INFORMATION15 COV_RULE_INFO15,
RULE_INFORMATION_CATEGORY COV_RULE_INFO,
RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER
FROM
OKC_RULE_GROUPS_B RGP,
OKC_RULES_B RUL,
OKC_K_LINES_B LINE
WHERE LINE.ID = RGP.CLE_ID
AND RGP.ID = RUL.RGP_ID
AND LINE.LSE_ID in (3,16,21)
AND RUL.RULE_INFORMATION_CATEGORY IN ('OFS','CVR','DST','PRE','BTD')
AND LINE.DNZ_CHR_ID = RGP.DNZ_CHR_ID
-- AND RUL.RULE_INFORMATION15 IS NULL
AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_Lines_B where cle_id = LINE.ID)
ORDER BY LINE.ID;
Line_Last_Updated_By_TBL Num_Tbl_Type;
Line_Last_Update_Date_TBL Date_Tbl_Type;
Line_Last_Update_Login_TBL Num_Tbl_Type;
SELECT UOM_CODE,Duration
FROM OKC_TIMEVALUES_V
WHERE id = l_id;
SELECT Times.tze_id tze_id,
Times.Created_By Times_Created_By,
Times.Last_Updated_By Times_Last_Updated_By,
Times.Last_Update_Date Times_Last_Update_Date,
Times.Last_Update_Login Times_Last_Update_Login
FROM okc_timevalues_v times,
okc_cover_times cvt
WHERE CVT.tve_ID = TIMES.id
AND CVT.rul_id = rule_id
AND rownum = 1;
SELECT COUNT(*) NCOUNT
FROM OKS_COVERAGE_TIMEZONES
WHERE cle_id = k_cle_Id
AND dnz_chr_Id = k_dnz_Id;
l_ctzv_tbl_in(l_ctz_rec).Last_Updated_By := get_time_zone_ID_Rec.Times_Last_Updated_By;
l_ctzv_tbl_in(l_ctz_rec).Last_Update_Date := get_time_zone_ID_Rec.Times_Last_Update_Date;
l_ctzv_tbl_in(l_ctz_rec).Last_Update_Login := get_time_zone_ID_Rec.Times_Last_Update_Login;
Line_Last_Updated_By_TBL ,
Line_Last_Update_Date_TBL ,
Line_Last_Update_Login_TBL ,
RUL_ID_TBL ,
LINE_RGP_ID_TBL ,
Rul_Row_ID_TBl ,
LINE_LSE_ID_TBL ,
LINE_DNZ_CHR_ID_TBL ,
LINE_OBJECT1_ID1_TBL ,
LINE_OBJECT2_ID1_TBL ,
COV_RULE_INFO1_TBL ,
COV_RULE_INFO2_TBL ,
COV_RULE_INFO3_TBL ,
COV_RULE_INFO4_TBL ,
COV_RULE_INFO5_TBL ,
COV_RULE_INFO6_TBL ,
COV_RULE_INFO7_TBL ,
COV_RULE_INFO8_TBL ,
COV_RULE_INFO9_TBL ,
COV_RULE_INFO10_TBL ,
COV_RULE_INFO11_TBL ,
COV_RULE_INFO12_TBL ,
COV_RULE_INFO13_TBL ,
COV_RULE_INFO14_TBL ,
COV_RULE_INFO15_TBL ,
COV_RULE_INFO_TBL ,
COV_OBJ_VER_NUMBER_TBL
LIMIT 1000;
l_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
l_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
l_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
UPDATE OKC_K_LINES_B
SET PRICE_LIST_ID = LINE_OBJECT1_ID1_TBL(i),
CURRENCY_CODE = l_OU_CURRENCY
WHERE ID = LINE_ID_TBL(i);
oks_kln_pvt.insert_row
(
x_return_status => l_return_status,
p_klnv_tbl => l_clev_tbl_in,
p_api_version =>1,
p_init_msg_list => null,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_klnv_tbl => x_clev_tbl_in
);
OKS_CTZ_PVT.insert_row
(
x_return_status => l_return_status,
p_oks_coverage_timezones_v_tbl => l_ctzv_tbl_in,
p_api_version =>1,
p_init_msg_list => null, --standard
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_oks_coverage_timezones_v_tbl => x_ctzv_tbl_in
);
l_ctzv_tbl_in.delete;
l_clev_tbl_in.delete;
l_ctzv_tbl_in.delete;
l_clev_tbl_in.delete;
l_cvtv_tbl_in.delete;
l_ctzv_tbl_in.delete;
l_clev_tbl_in.delete;
l_cvtv_tbl_in.delete;
l_ctzv_tbl_in.delete;
l_clev_tbl_in.delete;
l_cvtv_tbl_in.delete;
SELECT CTZ.ID TimeZone_ID,
CTZ.DNZ_CHR_ID DNZ_CHR_ID,
TIMES.ROWID TIMES_ROW_ID,
TIMES.TVE_ID_STARTED TVE_ID_STARTED,
TIMES.TVE_ID_ENDED TVE_ID_ENDED,
Times.Created_By Times_Created_By,
Times.Last_Updated_By Times_Last_Updated_By,
Times.Last_Update_Date Times_Last_Update_Date,
Times.Last_Update_Login Times_Last_Update_Login,
Times.Attribute15 Times_Attribute15
FROM OKS_COVERAGE_TIMEZONES CTZ,
OKC_RULE_GROUPS_B RGP,
OKC_RULES_B RUL,
OKC_COVER_TIMES CVT,
OKC_TIMEVALUES_B TIMES
WHERE CTZ.Cle_Id = RGP.CLE_ID
AND CTZ.DNZ_CHR_ID = RGP.DNZ_CHR_ID
AND RGP.ID = RUL.RGP_ID
AND RGP.DNZ_CHR_ID = RUL.DNZ_CHR_ID
AND RUL.RULE_INFORMATION_CATEGORY = 'CVR'
AND RUL.ID = CVT.RUL_ID
AND CVT.TVE_ID = TIMES.ID
--AND Times.Attribute15 IS NULL
AND Times.rowid BETWEEN l_start_rowid and l_end_rowid
AND NOT EXISTS (Select COV_TZE_LINE_ID from OKS_COVERAGE_TIMES where COV_TZE_LINE_ID= CTZ.ID);
SELECT tve_type,day_of_week,hour,minute
FROM okc_timevalues_v
WHERE ID = l_tve_id;
TIMES_Last_Updated_By_TBL Num_Tbl_Type;
TIMES_Last_Update_Date_TBL Date_Tbl_Type;
TIMES_Last_Update_Login_TBL Num_Tbl_Type;
TIMES_Last_Updated_By_TBL,
TIMES_Last_Update_Date_TBL,
TIMES_Last_Update_Login_TBL,
Times_Attribute15_TBL
LIMIT 1000;
l_cvtv_tbl_in(l_cvt_rec).Last_Updated_By := TIMES_Last_Updated_By_TBL(i);
l_cvtv_tbl_in(l_cvt_rec).Last_Update_Date := TIMES_Last_Update_Date_TBL(i);
l_cvtv_tbl_in(l_cvt_rec).Last_Update_Login := TIMES_Last_Update_Login_TBL(i);
OKS_Insert_Row_Upg.INSERT_ROW_UPG_CVTV_TBL( x_return_status => l_return_status,
P_CVTV_TBL =>l_cvtv_tbl_in);
SELECT
LINE.ID LINE_ID,
Line.Created_By Line_Created_By,
Line.Creation_Date Line_Creation_Date,
Line.Last_Updated_By Line_Last_Updated_By,
Line.Last_Update_Date Line_Last_Update_Date,
Line.Last_Update_Login Line_Last_Update_Login,
Rul.ROWID RUL_ROW_ID,
Rul.ID Rul_Id,
RGP.ID LINE_RGP_ID,
LINE.LSE_ID LINE_LSE_ID,
LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
OBJECT1_ID1 LINE_OBJECT1_ID1,
OBJECT2_ID1 LINE_OBJECT2_ID1,
RULE_INFORMATION1 COV_RULE_INFO1,
RULE_INFORMATION2 COV_RULE_INFO2,
RULE_INFORMATION3 COV_RULE_INFO3,
RULE_INFORMATION4 COV_RULE_INFO4,
RULE_INFORMATION5 COV_RULE_INFO5,
RULE_INFORMATION6 COV_RULE_INFO6,
RULE_INFORMATION7 COV_RULE_INFO7,
RULE_INFORMATION8 COV_RULE_INFO8,
RULE_INFORMATION9 COV_RULE_INFO9,
RULE_INFORMATION10 COV_RULE_INFO10,
RULE_INFORMATION11 COV_RULE_INFO11,
RULE_INFORMATION12 COV_RULE_INFO12,
RULE_INFORMATION13 COV_RULE_INFO13,
RULE_INFORMATION14 COV_RULE_INFO14,
RULE_INFORMATION15 COV_RULE_INFO15,
RULE_INFORMATION_CATEGORY COV_RULE_INFO,
RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER
FROM
OKC_RULE_GROUPS_B RGP,
OKC_RULES_B RUL,
OKC_K_LINES_B LINE
WHERE LINE.ID = RGP.CLE_ID
AND RGP.ID = RUL.RGP_ID
AND LINE.LSE_ID IN (4,17,22)
AND RUL.RULE_INFORMATION_CATEGORY IN ('RCN','RSN')
-- AND RUL.RULE_INFORMATION15 IS NULL
AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_B WHERE CLE_ID = LINE.ID)
ORDER BY LINE.ID;
Line_Last_Updated_By_TBL Num_Tbl_Type;
Line_Last_Update_Date_TBL Date_Tbl_Type;
Line_Last_Update_Login_TBL Num_Tbl_Type;
Line_Last_Updated_By_TBL ,
Line_Last_Update_Date_TBL ,
Line_Last_Update_Login_TBL ,
RUL_ROW_ID_TBL ,
RUL_ID_TBL ,
LINE_RGP_ID_TBL ,
LINE_LSE_ID_TBL ,
LINE_DNZ_CHR_ID_TBL ,
LINE_OBJECT1_ID1_TBL ,
LINE_OBJECT2_ID1_TBL ,
COV_RULE_INFO1_TBL ,
COV_RULE_INFO2_TBL ,
COV_RULE_INFO3_TBL ,
COV_RULE_INFO4_TBL ,
COV_RULE_INFO5_TBL ,
COV_RULE_INFO6_TBL ,
COV_RULE_INFO7_TBL ,
COV_RULE_INFO8_TBL ,
COV_RULE_INFO9_TBL ,
COV_RULE_INFO10_TBL ,
COV_RULE_INFO11_TBL ,
COV_RULE_INFO12_TBL ,
COV_RULE_INFO13_TBL ,
COV_RULE_INFO14_TBL ,
COV_RULE_INFO15_TBL ,
COV_RULE_INFO_TBL ,
COV_OBJ_VER_NUMBER_TBL
LIMIT 1000;
l_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
l_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
l_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
l_actv_tbl_in(l_act_ctr).Last_Updated_By := -9999;
l_actv_tbl_in(l_act_ctr).Last_Update_Date := sysdate;
l_actv_tbl_in(l_act_ctr).Last_Update_Login := -9999;
oks_kln_pvt.insert_row
(
x_return_status => l_return_status,
p_klnv_tbl => l_clev_tbl_in,
p_api_version =>1,
p_init_msg_list => null,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_klnv_tbl => x_clev_tbl_in
);
OKS_ACT_PVT.insert_row
(
x_return_status => l_return_status,
p_oks_action_time_types_v_tbl => l_actv_tbl_in,
p_api_version =>1,
p_init_msg_list => null,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_oks_action_time_types_v_tbl => X_actv_tbl_in
);
l_actv_tbl_in.DELETE;
l_clev_tbl_in.DELETE;
l_actv_tbl_in.DELETE;
l_clev_tbl_in.DELETE;
l_actv_tbl_in.DELETE;
l_clev_tbl_in.DELETE;
SELECT TYP.id Action_Type_ID,
TYP.cle_id Action_Type_Cle_ID ,
TYP.dnz_chr_id Action_Type_Dnz_ID ,
TYP.Created_By Created_By,
TYP.Last_Updated_By Last_Updated_By,
TYP.Last_Update_Date Last_Update_Date,
TYP.Last_Update_Login Last_Update_Login,
RIN.UOM_CODE UOM_CODE,
RIN.DURATION DURATION,
TIM.DAY_OF_WEEK DAY_OF_WEEK,
TIM.TVE_TYPE TVE_TYPE,
Rul.ID RUL_ID
FROM oks_action_time_types_v TYP,
okc_rule_groups_V RGP,okc_rules_v RUL,
okc_timevalues_v TIM,okc_react_intervals RIN
WHERE RGP.CLE_ID = TYP.CLE_ID
AND RGP.DNZ_CHR_ID = TYP.DNZ_CHR_ID
AND RGP.ID = RUL.RGP_ID
-- AND typ.id = 308454467546072904212144662892351929683
AND RGP.DNZ_CHR_ID = RUL.DNZ_CHR_ID
AND RUL.DNZ_CHR_ID = TYP.DNZ_CHR_ID
AND RUL.ID = RIN.RUL_ID
AND rul.RULE_INFORMATION_CATEGORY = typ.ACTION_TYPE_CODE
AND RIN.DNZ_CHR_ID = TYP.DNZ_CHR_ID
AND TIM.ID = RIN.TVE_ID
AND TIM.DNZ_CHR_ID = RIN.DNZ_CHR_ID
AND TIM.DNZ_CHR_ID = RGP.DNZ_CHR_ID
AND TIM.DNZ_CHR_ID = RUL.DNZ_CHR_ID
AND TIM.DNZ_CHR_ID = TYP.DNZ_CHR_ID
AND NOT EXISTS (Select cov_action_type_id FROM OKS_ACTION_TIMES WHERE COV_ACTION_TYPE_ID =TYP.id)
ORDER BY RUL.ID;
Last_Updated_By_TBL Num_Tbl_Type;
Last_Update_Date_TBL Date_Tbl_Type;
Last_Update_Login_TBL Num_Tbl_Type;
Last_Updated_By_TBL,
Last_Update_Date_TBL,
Last_Update_Login_TBL,
UOM_CODE_TBL,
DURATION_TBL,
DAY_OF_WEEK_TBL,
TVE_TYPE_TBL,
RUL_ID_TBL
LIMIT 1000;
l_acmv_tbl_in(l_acm_ctr).Last_Updated_By := Last_Updated_By_TBL(i);
l_acmv_tbl_in(l_acm_ctr).Last_Update_Date := Last_Update_Date_TBL(i);
l_acmv_tbl_in(l_acm_ctr).Last_Update_Login := Last_Update_Login_TBL(i);
l_acmv_tbl_in(l_acm_ctr).PROGRAM_UPDATE_DATE := NULL;
OKS_Insert_Row_Upg.INSERT_ROW_UPG_ACMV_TBL
(x_return_status => l_return_status,
P_ACMV_TBL => l_acmv_tbl_in);
l_acmv_tbl_in.DELETE;
SELECT
LINE.ID LINE_ID,
Line.Created_By Line_Created_By,
Line.Creation_Date Line_Creation_Date,
Line.Last_Updated_By Line_Last_Updated_By,
Line.Last_Update_Date Line_Last_Update_Date,
Line.Last_Update_Login Line_Last_Update_Login,
Rul.ROWID RUL_ROW_ID,
RGP.ID LINE_RGP_ID,
LINE.LSE_ID LINE_LSE_ID,
LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
OBJECT1_ID1 LINE_OBJECT1_ID1,
OBJECT2_ID1 LINE_OBJECT2_ID1,
RULE_INFORMATION1 COV_RULE_INFO1,
RULE_INFORMATION2 COV_RULE_INFO2,
RULE_INFORMATION3 COV_RULE_INFO3,
RULE_INFORMATION4 COV_RULE_INFO4,
RULE_INFORMATION5 COV_RULE_INFO5,
RULE_INFORMATION6 COV_RULE_INFO6,
RULE_INFORMATION7 COV_RULE_INFO7,
RULE_INFORMATION8 COV_RULE_INFO8,
RULE_INFORMATION9 COV_RULE_INFO9,
RULE_INFORMATION10 COV_RULE_INFO10,
RULE_INFORMATION11 COV_RULE_INFO11,
RULE_INFORMATION12 COV_RULE_INFO12,
RULE_INFORMATION13 COV_RULE_INFO13,
RULE_INFORMATION14 COV_RULE_INFO14,
RULE_INFORMATION15 COV_RULE_INFO15,
RULE_INFORMATION_CATEGORY COV_RULE_INFO,
RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER
FROM
OKC_RULE_GROUPS_B RGP,
OKC_RULES_B RUL,
OKC_K_LINES_B LINE
WHERE LINE.ID = RGP.CLE_ID
AND RGP.ID = RUL.RGP_ID
AND LINE.LSE_ID IN (5,59,23)
--AND RUL.RULE_INFORMATION_CATEGORY IN ('LMT')
-- AND RUL.RULE_INFORMATION15 IS NULL
AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_B WHERE CLE_ID = LINE.ID)
ORDER BY LINE.ID;
Line_Last_Updated_By_TBL Num_Tbl_Type;
Line_Last_Update_Date_TBL Date_Tbl_Type;
Line_Last_Update_Login_TBL Num_Tbl_Type;
Line_Last_Updated_By_TBL ,
Line_Last_Update_Date_TBL ,
Line_Last_Update_Login_TBL ,
RUL_ROW_ID_TBL ,
LINE_RGP_ID_TBL ,
LINE_LSE_ID_TBL ,
LINE_DNZ_CHR_ID_TBL ,
LINE_OBJECT1_ID1_TBL ,
LINE_OBJECT2_ID1_TBL ,
COV_RULE_INFO1_TBL ,
COV_RULE_INFO2_TBL ,
COV_RULE_INFO3_TBL ,
COV_RULE_INFO4_TBL ,
COV_RULE_INFO5_TBL ,
COV_RULE_INFO6_TBL ,
COV_RULE_INFO7_TBL ,
COV_RULE_INFO8_TBL ,
COV_RULE_INFO9_TBL ,
COV_RULE_INFO10_TBL ,
COV_RULE_INFO11_TBL ,
COV_RULE_INFO12_TBL ,
COV_RULE_INFO13_TBL ,
COV_RULE_INFO14_TBL ,
COV_RULE_INFO15_TBL ,
COV_RULE_INFO_TBL ,
COV_OBJ_VER_NUMBER_TBL
LIMIT 1000;
l_clev_tbl_in(l_cle_ctr).Last_Updated_By :=
Line_Last_Updated_By_TBL(i);
l_clev_tbl_in(l_cle_ctr).Last_Update_Date :=
Line_Last_Update_Date_TBL(i);
l_clev_tbl_in(l_cle_ctr).Last_Update_Login :=
Line_Last_Update_Login_TBL(i);
oks_kln_pvt.insert_row
(
x_return_status => l_return_status,
p_klnv_tbl => l_clev_tbl_in,
p_api_version =>1,
p_init_msg_list => null,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_klnv_tbl => x_clev_tbl_in
);
l_clev_tbl_in.DELETE;
l_clev_tbl_in.DELETE;
PROCEDURE Insert_Into_Klines( p_clev_tbl_in IN klnv_tbl_type,
p_clet_tbl_in IN klt_tbl_type,
x_return_Status OUT NOCOPY VARCHAR2) IS
i_clev_tbl_in klnv_tbl_type := p_clev_tbl_in;
G_APP_NAME := 'Insert_Into_Klines';
In_LAST_UPDATED_BY(J):= i_clev_tbl_in(I).LAST_UPDATED_BY ;
In_LAST_UPDATE_DATE (J):= i_clev_tbl_in(I).LAST_UPDATE_DATE ;
In_LAST_UPDATE_LOGIN (J):= i_clev_tbl_in(I).LAST_UPDATE_LOGIN ;
tln_last_updated_by(k) :=l_clet_tbl_in(I).last_updated_by;
tln_last_update_date(k) :=l_clet_tbl_in(I).last_update_date;
tln_last_update_login(k) :=l_clet_tbl_in(I).last_update_login;
INSERT INTO OKS_K_LINES_BH
(
ID,
MAJOR_VERSION ,
CLE_ID,
DNZ_CHR_ID,
DISCOUNT_LIST ,
ACCT_RULE_ID,
PAYMENT_TYPE,
CC_NO ,
CC_EXPIRY_DATE,
CC_BANK_ACCT_ID ,
CC_AUTH_CODE,
LOCKED_PRICE_LIST_ID,
USAGE_EST_YN,
USAGE_EST_METHOD,
USAGE_EST_START_DATE,
TERMN_METHOD,
UBT_AMOUNT,
CREDIT_AMOUNT ,
SUPPRESSED_CREDIT ,
OVERRIDE_AMOUNT ,
CUST_PO_NUMBER_REQ_YN ,
CUST_PO_NUMBER,
GRACE_DURATION,
GRACE_PERIOD,
INV_PRINT_FLAG,
PRICE_UOM ,
TAX_AMOUNT,
TAX_INCLUSIVE_YN,
TAX_STATUS,
TAX_CODE,
TAX_EXEMPTION_ID,
IB_TRANS_TYPE ,
IB_TRANS_DATE ,
PROD_PRICE,
SERVICE_PRICE ,
CLVL_LIST_PRICE ,
CLVL_QUANTITY ,
CLVL_EXTENDED_AMT ,
CLVL_UOM_CODE ,
TOPLVL_OPERAND_CODE ,
TOPLVL_OPERAND_VAL,
TOPLVL_QUANTITY ,
TOPLVL_UOM_CODE ,
TOPLVL_ADJ_PRICE,
TOPLVL_PRICE_QTY,
AVERAGING_INTERVAL,
SETTLEMENT_INTERVAL ,
MINIMUM_QUANTITY,
DEFAULT_QUANTITY,
AMCV_FLAG ,
FIXED_QUANTITY,
USAGE_DURATION,
USAGE_PERIOD,
LEVEL_YN,
USAGE_TYPE,
UOM_QUANTIFIED,
BASE_READING,
BILLING_SCHEDULE_TYPE ,
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 ,
OBJECT_VERSION_NUMBER ,
SECURITY_GROUP_ID ,
REQUEST_ID,
CREATED_BY,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN ,
COMMITMENT_ID ,
FULL_CREDIT)
VALUES
(
In_ID(I),
In_MAJOR_VERSION (I),
In_CLE_ID(I),
In_DNZ_CHR_ID(I),
In_DISCOUNT_LIST (I),
In_ACCT_RULE_ID(I),
In_PAYMENT_TYPE(I),
In_CC_NO (I),
In_CC_EXPIRY_DATE(I),
In_CC_BANK_ACCT_ID (I),
In_CC_AUTH_CODE(I),
In_LOCKED_PRICE_LIST_ID(I),
In_USAGE_EST_YN(I),
In_USAGE_EST_METHOD(I),
In_USAGE_EST_START_DATE(I),
In_TERMN_METHOD(I),
In_UBT_AMOUNT(I),
In_CREDIT_AMOUNT (I),
In_SUPPRESSED_CREDIT (I),
In_OVERRIDE_AMOUNT (I),
In_CUST_PO_NUMBER_REQ_YN (I),
In_CUST_PO_NUMBER(I),
In_GRACE_DURATION(I),
In_GRACE_PERIOD(I),
In_INV_PRINT_FLAG(I),
In_PRICE_UOM (I),
In_TAX_AMOUNT(I),
In_TAX_INCLUSIVE_YN(I),
In_TAX_STATUS(I),
In_TAX_CODE(I),
In_TAX_EXEMPTION_ID(I),
In_IB_TRANS_TYPE (I),
In_IB_TRANS_DATE (I),
In_PROD_PRICE(I),
In_SERVICE_PRICE (I),
In_CLVL_LIST_PRICE (I),
In_CLVL_QUANTITY (I),
In_CLVL_EXTENDED_AMT (I),
In_CLVL_UOM_CODE (I),
In_TOPLVL_OPERAND_CODE (I),
In_TOPLVL_OPERAND_VAL(I),
In_TOPLVL_QUANTITY (I),
In_TOPLVL_UOM_CODE (I),
In_TOPLVL_ADJ_PRICE(I),
In_TOPLVL_PRICE_QTY(I),
In_AVERAGING_INTERVAL(I),
In_SETTLEMENT_INTERVAL (I),
In_MINIMUM_QUANTITY(I),
In_DEFAULT_QUANTITY(I),
In_AMCV_FLAG (I),
In_FIXED_QUANTITY(I),
In_USAGE_DURATION(I),
In_USAGE_PERIOD(I),
In_LEVEL_YN(I),
In_USAGE_TYPE(I),
In_UOM_QUANTIFIED(I),
In_BASE_READING(I),
In_BILLING_SCHEDULE_TYPE (I),
In_COVERAGE_TYPE (I),
In_EXCEPTION_COV_ID(I),
In_LIMIT_UOM_QUANTIFIED(I),
In_DISCOUNT_AMOUNT (I),
In_DISCOUNT_PERCENT(I),
In_OFFSET_DURATION (I),
In_OFFSET_PERIOD (I),
In_INCIDENT_SEVERITY_ID(I),
In_PDF_ID(I),
In_WORK_THRU_YN(I),
In_REACT_ACTIVE_YN (I),
In_TRANSFER_OPTION (I),
In_PROD_UPGRADE_YN (I),
In_INHERITANCE_TYPE(I),
In_PM_PROGRAM_ID (I),
In_PM_CONF_REQ_YN(I),
In_PM_SCH_EXISTS_YN(I),
In_ALLOW_BT_DISCOUNT (I),
In_APPLY_DEFAULT_TIMEZONE(I),
In_SYNC_DATE_INSTALL (I),
In_OBJECT_VERSION_NUMBER (I),
In_SECURITY_GROUP_ID (I),
In_REQUEST_ID(I),
In_CREATED_BY(I),
In_CREATION_DATE (I),
In_LAST_UPDATED_BY (I),
In_LAST_UPDATE_DATE(I),
In_LAST_UPDATE_LOGIN (I),
In_COMMITMENT_ID (I),
In_FULL_CREDIT(I));
INSERT INTO OKS_K_LINES_TLH(
ID,
MAJOR_VERSION,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
INVOICE_TEXT,
IB_TRX_DETAILS,
STATUS_TEXT,
REACT_TIME_NAME,
SECURITY_GROUP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN) VALUES
(
TLn_ID(I),
Tln_MAJOR_VERSION(I),
tln_language(I),
tln_source_lang(I) ,
tln_sfwt_flag(I) ,
tln_invoice_text(I) ,
NULL,
NULL,
NULL,
NULL,
tln_created_by(I),
tln_creation_date(I) ,
tln_last_updated_by(I) ,
tln_last_update_date(I) ,
tln_last_update_login(I) );
SELECT
LINE.ID LINE_ID,
Line.Created_By Line_Created_By,
Line.Creation_Date Line_Creation_Date,
Line.Last_Updated_By Line_Last_Updated_By,
Line.Last_Update_Date Line_Last_Update_Date,
Line.Last_Update_Login Line_Last_Update_Login,
RGP.ID LINE_RGP_ID,
Rul.RowID Rul_Row_ID ,
Rul.ID Rule_ID,
LINE.LSE_ID LINE_LSE_ID,
LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
OBJECT1_ID1 LINE_OBJECT1_ID1,
OBJECT2_ID1 LINE_OBJECT2_ID1,
RULE_INFORMATION1 COV_RULE_INFO1,
RULE_INFORMATION2 COV_RULE_INFO2,
RULE_INFORMATION3 COV_RULE_INFO3,
RULE_INFORMATION4 COV_RULE_INFO4,
RULE_INFORMATION5 COV_RULE_INFO5,
RULE_INFORMATION6 COV_RULE_INFO6,
RULE_INFORMATION7 COV_RULE_INFO7,
RULE_INFORMATION8 COV_RULE_INFO8,
RULE_INFORMATION9 COV_RULE_INFO9,
RULE_INFORMATION10 COV_RULE_INFO10,
RULE_INFORMATION11 COV_RULE_INFO11,
RULE_INFORMATION12 COV_RULE_INFO12,
RULE_INFORMATION13 COV_RULE_INFO13,
RULE_INFORMATION14 COV_RULE_INFO14,
RULE_INFORMATION15 COV_RULE_INFO15,
RULE_INFORMATION_CATEGORY COV_RULE_INFO,
RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER,
RUL.MAJOR_VERSION MAJOR_VERSION,
KINE.ID k_line_id
FROM
OKC_RULE_GROUPS_BH RGP,
OKC_RULES_BH RUL,
OKC_K_LINES_BH LINE,
OKS_K_LINES_B KINE
WHERE LINE.ID = KINE.CLE_ID
AND LINE.DNZ_CHR_ID = KINE.DNZ_CHR_ID
AND LINE.ID = RGP.CLE_ID
AND RGP.ID = RUL.RGP_ID
AND LINE.LSE_ID IN (2,15,20)
-- AND RUL.RULE_INFORMATION_CATEGORY IN ('ECE','WHE','UGE','STR','CVE','PMP')
AND LINE.DNZ_CHR_ID = RGP.DNZ_CHR_ID
-- AND RUL.RULE_INFORMATION15 IS NULL
AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_BH WHERE CLE_ID = LINE.ID)
AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
AND LINE.major_version = RGP.major_version
AND RGP.major_version = RUL.major_version
ORDER BY LINE.ID,RUL.major_version;
SELECT ID,
MAJOR_VERSION,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
COMMENTS,
TEXT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID
FROM OKC_RULES_TLH
WHERE ID = P_ID;
Line_Last_Updated_By_TBL Num_Tbl_Type;
Line_Last_Update_Date_TBL Date_Tbl_Type;
Line_Last_Update_Login_TBL Num_Tbl_Type;
SELECT UOM_CODE,Duration
FROM OKC_TIMEVALUES_V
WHERE id = l_id;
i_clev_tbl_in.DELETE;
l_clet_tbl_in.DELETE;
Line_Last_Updated_By_TBL ,
Line_Last_Update_Date_TBL ,
Line_Last_Update_Login_TBL ,
LINE_RGP_ID_TBL ,
Rul_Row_ID_TBl ,
Rule_Id_Tbl ,
LINE_LSE_ID_TBL ,
LINE_DNZ_CHR_ID_TBL ,
LINE_OBJECT1_ID1_TBL ,
LINE_OBJECT2_ID1_TBL ,
COV_RULE_INFO1_TBL ,
COV_RULE_INFO2_TBL ,
COV_RULE_INFO3_TBL ,
COV_RULE_INFO4_TBL ,
COV_RULE_INFO5_TBL ,
COV_RULE_INFO6_TBL ,
COV_RULE_INFO7_TBL ,
COV_RULE_INFO8_TBL ,
COV_RULE_INFO9_TBL ,
COV_RULE_INFO10_TBL ,
COV_RULE_INFO11_TBL ,
COV_RULE_INFO12_TBL ,
COV_RULE_INFO13_TBL ,
COV_RULE_INFO14_TBL ,
COV_RULE_INFO15_TBL ,
COV_RULE_INFO_TBL ,
COV_OBJ_VER_NUMBER_TBL,
MAJOR_VERSION_TBL,
k_line_id_TBL
LIMIT 1000;--20;
i_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
i_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
i_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
l_clet_tbl_in(l_clt_ctr).last_updated_by := Get_Rule_TlH_REC.last_updated_by;
l_clet_tbl_in(l_clt_ctr).last_update_date := Get_Rule_TlH_REC.last_update_date;
l_clet_tbl_in(l_clt_ctr).last_update_login := Get_Rule_TlH_REC.last_update_login;
In_LAST_UPDATED_BY(J):= i_clev_tbl_in(I).LAST_UPDATED_BY ;
In_LAST_UPDATE_DATE (J):= i_clev_tbl_in(I).LAST_UPDATE_DATE ;
In_LAST_UPDATE_LOGIN (J):= i_clev_tbl_in(I).LAST_UPDATE_LOGIN ;
tln_last_updated_by(k) :=l_clet_tbl_in(I).last_updated_by;
tln_last_update_date(k) :=l_clet_tbl_in(I).last_update_date;
tln_last_update_login(k) :=l_clet_tbl_in(I).last_update_login;
INSERT INTO OKS_K_LINES_BH
(
ID,
MAJOR_VERSION ,
CLE_ID,
DNZ_CHR_ID,
DISCOUNT_LIST ,
ACCT_RULE_ID,
PAYMENT_TYPE,
CC_NO ,
CC_EXPIRY_DATE,
CC_BANK_ACCT_ID ,
CC_AUTH_CODE,
LOCKED_PRICE_LIST_ID,
USAGE_EST_YN,
USAGE_EST_METHOD,
USAGE_EST_START_DATE,
TERMN_METHOD,
UBT_AMOUNT,
CREDIT_AMOUNT ,
SUPPRESSED_CREDIT ,
OVERRIDE_AMOUNT ,
CUST_PO_NUMBER_REQ_YN ,
CUST_PO_NUMBER,
GRACE_DURATION,
GRACE_PERIOD,
INV_PRINT_FLAG,
PRICE_UOM ,
TAX_AMOUNT,
TAX_INCLUSIVE_YN,
TAX_STATUS,
TAX_CODE,
TAX_EXEMPTION_ID,
IB_TRANS_TYPE ,
IB_TRANS_DATE ,
PROD_PRICE,
SERVICE_PRICE ,
CLVL_LIST_PRICE ,
CLVL_QUANTITY ,
CLVL_EXTENDED_AMT ,
CLVL_UOM_CODE ,
TOPLVL_OPERAND_CODE ,
TOPLVL_OPERAND_VAL,
TOPLVL_QUANTITY ,
TOPLVL_UOM_CODE ,
TOPLVL_ADJ_PRICE,
TOPLVL_PRICE_QTY,
AVERAGING_INTERVAL,
SETTLEMENT_INTERVAL ,
MINIMUM_QUANTITY,
DEFAULT_QUANTITY,
AMCV_FLAG ,
FIXED_QUANTITY,
USAGE_DURATION,
USAGE_PERIOD,
LEVEL_YN,
USAGE_TYPE,
UOM_QUANTIFIED,
BASE_READING,
BILLING_SCHEDULE_TYPE ,
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 ,
OBJECT_VERSION_NUMBER ,
SECURITY_GROUP_ID ,
REQUEST_ID,
CREATED_BY,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN ,
COMMITMENT_ID ,
FULL_CREDIT)
VALUES
(
In_ID(I),
In_MAJOR_VERSION (I),
In_CLE_ID(I),
In_DNZ_CHR_ID(I),
In_DISCOUNT_LIST (I),
In_ACCT_RULE_ID(I),
In_PAYMENT_TYPE(I),
In_CC_NO (I),
In_CC_EXPIRY_DATE(I),
In_CC_BANK_ACCT_ID (I),
In_CC_AUTH_CODE(I),
In_LOCKED_PRICE_LIST_ID(I),
In_USAGE_EST_YN(I),
In_USAGE_EST_METHOD(I),
In_USAGE_EST_START_DATE(I),
In_TERMN_METHOD(I),
In_UBT_AMOUNT(I),
In_CREDIT_AMOUNT (I),
In_SUPPRESSED_CREDIT (I),
In_OVERRIDE_AMOUNT (I),
In_CUST_PO_NUMBER_REQ_YN (I),
In_CUST_PO_NUMBER(I),
In_GRACE_DURATION(I),
In_GRACE_PERIOD(I),
In_INV_PRINT_FLAG(I),
In_PRICE_UOM (I),
In_TAX_AMOUNT(I),
In_TAX_INCLUSIVE_YN(I),
In_TAX_STATUS(I),
In_TAX_CODE(I),
In_TAX_EXEMPTION_ID(I),
In_IB_TRANS_TYPE (I),
In_IB_TRANS_DATE (I),
In_PROD_PRICE(I),
In_SERVICE_PRICE (I),
In_CLVL_LIST_PRICE (I),
In_CLVL_QUANTITY (I),
In_CLVL_EXTENDED_AMT (I),
In_CLVL_UOM_CODE (I),
In_TOPLVL_OPERAND_CODE (I),
In_TOPLVL_OPERAND_VAL(I),
In_TOPLVL_QUANTITY (I),
In_TOPLVL_UOM_CODE (I),
In_TOPLVL_ADJ_PRICE(I),
In_TOPLVL_PRICE_QTY(I),
In_AVERAGING_INTERVAL(I),
In_SETTLEMENT_INTERVAL (I),
In_MINIMUM_QUANTITY(I),
In_DEFAULT_QUANTITY(I),
In_AMCV_FLAG (I),
In_FIXED_QUANTITY(I),
In_USAGE_DURATION(I),
In_USAGE_PERIOD(I),
In_LEVEL_YN(I),
In_USAGE_TYPE(I),
In_UOM_QUANTIFIED(I),
In_BASE_READING(I),
In_BILLING_SCHEDULE_TYPE (I),
In_COVERAGE_TYPE (I),
In_EXCEPTION_COV_ID(I),
In_LIMIT_UOM_QUANTIFIED(I),
In_DISCOUNT_AMOUNT (I),
In_DISCOUNT_PERCENT(I),
In_OFFSET_DURATION (I),
In_OFFSET_PERIOD (I),
In_INCIDENT_SEVERITY_ID(I),
In_PDF_ID(I),
In_WORK_THRU_YN(I),
In_REACT_ACTIVE_YN (I),
In_TRANSFER_OPTION (I),
In_PROD_UPGRADE_YN (I),
In_INHERITANCE_TYPE(I),
In_PM_PROGRAM_ID (I),
In_PM_CONF_REQ_YN(I),
In_PM_SCH_EXISTS_YN(I),
In_ALLOW_BT_DISCOUNT (I),
In_APPLY_DEFAULT_TIMEZONE(I),
In_SYNC_DATE_INSTALL (I),
In_OBJECT_VERSION_NUMBER (I),
In_SECURITY_GROUP_ID (I),
In_REQUEST_ID(I),
In_CREATED_BY(I),
In_CREATION_DATE (I),
In_LAST_UPDATED_BY (I),
In_LAST_UPDATE_DATE(I),
In_LAST_UPDATE_LOGIN (I),
In_COMMITMENT_ID (I),
In_FULL_CREDIT(I));
INSERT INTO OKS_K_LINES_TLH
(
ID,
MAJOR_VERSION,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
INVOICE_TEXT,
IB_TRX_DETAILS,
STATUS_TEXT,
REACT_TIME_NAME,
SECURITY_GROUP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN) VALUES
(
TLn_ID(I),
Tln_MAJOR_VERSION(I),
tln_language(I),
tln_source_lang(I) ,
tln_sfwt_flag(I) ,
tln_invoice_text(I) ,
NULL,
NULL,
NULL,
NULL,
tln_created_by(I),
tln_creation_date(I) ,
tln_last_updated_by(I) ,
tln_last_update_date(I) ,
tln_last_update_login(I) );
SELECT
LINE.ID LINE_ID,
Line.Created_By Line_Created_By,
Line.Creation_Date Line_Creation_Date,
Line.Last_Updated_By Line_Last_Updated_By,
Line.Last_Update_Date Line_Last_Update_Date,
Line.Last_Update_Login Line_Last_Update_Login,
Rul.ID Rule_Id,
RGP.ID LINE_RGP_ID,
Rul.RowId Rul_Row_Id,
LINE.LSE_ID LINE_LSE_ID,
LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
OBJECT1_ID1 LINE_OBJECT1_ID1,
OBJECT2_ID1 LINE_OBJECT2_ID1,
RULE_INFORMATION1 COV_RULE_INFO1,
RULE_INFORMATION2 COV_RULE_INFO2,
RULE_INFORMATION3 COV_RULE_INFO3,
RULE_INFORMATION4 COV_RULE_INFO4,
RULE_INFORMATION5 COV_RULE_INFO5,
RULE_INFORMATION6 COV_RULE_INFO6,
RULE_INFORMATION7 COV_RULE_INFO7,
RULE_INFORMATION8 COV_RULE_INFO8,
RULE_INFORMATION9 COV_RULE_INFO9,
RULE_INFORMATION10 COV_RULE_INFO10,
RULE_INFORMATION11 COV_RULE_INFO11,
RULE_INFORMATION12 COV_RULE_INFO12,
RULE_INFORMATION13 COV_RULE_INFO13,
RULE_INFORMATION14 COV_RULE_INFO14,
RULE_INFORMATION15 COV_RULE_INFO15,
RULE_INFORMATION_CATEGORY COV_RULE_INFO,
RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER,
RUL.MAJOR_VERSION MAJOR_VERSION,
KINE.ID k_line_id
FROM
OKC_RULE_GROUPS_BH RGP,
OKC_RULES_BH RUL,
OKC_K_LINES_BH LINE,
OKS_K_LINES_B KINE
WHERE KINE.ID > -1
AND LINE.ID = KINE.CLE_ID
AND LINE.DNZ_CHR_ID = KINE.DNZ_CHR_ID
AND LINE.ID = RGP.CLE_ID
AND RGP.ID = RUL.RGP_ID
AND LINE.LSE_ID in (3,16,21)
AND RUL.RULE_INFORMATION_CATEGORY IN ('OFS','CVR','DST','PRE','BTD')
AND LINE.DNZ_CHR_ID = RGP.DNZ_CHR_ID
-- AND RUL.RULE_INFORMATION15 IS NULL
AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_Lines_BH where cle_id = LINE.ID)
AND LINE.MAJOR_VERSION = RGP.MAJOR_VERSION
AND RGP.MAJOR_VERSION = RUL.MAJOR_VERSION
ORDER BY LINE.ID,RUL.MAJOR_VERSION;
SELECT ID,
MAJOR_VERSION,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
COMMENTS,
TEXT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID
FROM OKC_RULES_TLH
WHERE ID = P_ID;
Line_Last_Updated_By_TBL Num_Tbl_Type;
Line_Last_Update_Date_TBL Date_Tbl_Type;
Line_Last_Update_Login_TBL Num_Tbl_Type;
SELECT TAL.UOM_CODE,TAL.Duration
FROM OKC_TIMEVALUES_BH ISE, OKC_TIMEVALUES_BH TAL
WHERE ISE.id = l_id
AND ISE.TVE_ID_STARTED = TAL.ID
AND ISE.TVE_TYPE = 'ISE'
AND TAL.TVE_TYPE = 'TAL'
AND ISE.DNZ_CHR_ID = TAL.DNZ_CHR_ID;
SELECT Times.tze_id tze_id,
Times.Created_By Times_Created_By,
Times.Last_Updated_By Times_Last_Updated_By,
Times.Last_Update_Date Times_Last_Update_Date,
Times.Last_Update_Login Times_Last_Update_Login,
Times.Object_Version_Number Times_Object_Version_Number,
Times.Major_Version Times_Major_Version
FROM okc_timevalues_bh times,
okc_cover_times_h cvt
WHERE CVT.tve_ID = TIMES.id
AND CVT.rul_id = rule_id
AND rownum = 1;
SELECT COUNT(*) NCOUNT
FROM OKS_COVERAGE_TIMEZONES
WHERE cle_id = cle_Id
AND dnz_chr_Id = dnz_Id;
i_ctzv_tbl_in(l_ctz_rec).Last_Updated_By := get_time_zone_ID_Rec.Times_Last_Updated_By;
i_ctzv_tbl_in(l_ctz_rec).Last_Update_Date := get_time_zone_ID_Rec.Times_Last_Update_Date;
i_ctzv_tbl_in(l_ctz_rec).Last_Update_Login := get_time_zone_ID_Rec.Times_Last_Update_Login;
Line_Last_Updated_By_TBL ,
Line_Last_Update_Date_TBL ,
Line_Last_Update_Login_TBL ,
RUL_ID_TBL ,
LINE_RGP_ID_TBL ,
Rul_Row_ID_TBl ,
LINE_LSE_ID_TBL ,
LINE_DNZ_CHR_ID_TBL ,
LINE_OBJECT1_ID1_TBL ,
LINE_OBJECT2_ID1_TBL ,
COV_RULE_INFO1_TBL ,
COV_RULE_INFO2_TBL ,
COV_RULE_INFO3_TBL ,
COV_RULE_INFO4_TBL ,
COV_RULE_INFO5_TBL ,
COV_RULE_INFO6_TBL ,
COV_RULE_INFO7_TBL ,
COV_RULE_INFO8_TBL ,
COV_RULE_INFO9_TBL ,
COV_RULE_INFO10_TBL ,
COV_RULE_INFO11_TBL ,
COV_RULE_INFO12_TBL ,
COV_RULE_INFO13_TBL ,
COV_RULE_INFO14_TBL ,
COV_RULE_INFO15_TBL ,
COV_RULE_INFO_TBL ,
COV_OBJ_VER_NUMBER_TBL,
MAJOR_VERSION_TBL,
k_line_id_TBL
LIMIT 1000;
i_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
i_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
i_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
UPDATE OKC_K_LINES_BH
SET PRICE_LIST_ID = LINE_OBJECT1_ID1_TBL(i),
CURRENCY_CODE = l_OU_CURRENCY
WHERE ID = LINE_ID_TBL(i);
l_clet_tbl_in(l_clt_ctr).last_updated_by := Get_Rule_TlH_REC.last_updated_by;
l_clet_tbl_in(l_clt_ctr).last_update_date := Get_Rule_TlH_REC.last_update_date;
l_clet_tbl_in(l_clt_ctr).last_update_login := Get_Rule_TlH_REC.last_update_login;
Insert_Into_Klines( p_clev_tbl_in => i_clev_tbl_in,
p_clet_tbl_in => l_clet_tbl_in,
x_return_Status => l_return_status);
IN_PROGRAM_UPDATE_DATE(k) := i_ctzv_tbl_in(i).PROGRAM_UPDATE_DATE;
IN_LAST_UPDATED_BY(k) := i_ctzv_tbl_in(i).LAST_UPDATED_BY;
IN_LAST_UPDATE_DATE(k) := i_ctzv_tbl_in(i).LAST_UPDATE_DATE;
IN_LAST_UPDATE_LOGIN(k) := i_ctzv_tbl_in(i).LAST_UPDATE_LOGIN;
l_status := 'Before Insert';
INSERT INTO OKS_COVERAGE_TIMEZONES_H(
ID,
DNZ_CHR_ID,
CLE_ID,
DEFAULT_YN,
TIMEZONE_ID,
SECURITY_GROUP_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
MAJOR_VERSION)
VALUES
(IN_ID(i),
IN_DNZ_CHR_ID(i),
IN_CLE_ID(i),
IN_DEFAULT_YN(i),
IN_TIMEZONE_ID(i),
IN_SECURITY_GROUP_ID(i),
IN_PROGRAM_APPLICATION_ID(i),
IN_PROGRAM_ID(i),
IN_PROGRAM_UPDATE_DATE(i),
IN_REQUEST_ID(i),
IN_CREATED_BY(i),
IN_CREATION_DATE(i),
IN_LAST_UPDATED_BY(i),
IN_LAST_UPDATE_DATE(i),
IN_LAST_UPDATE_LOGIN(i),
IN_OBJECT_VERSION_NUMBER(i),
IN_MAJOR_VERSION(i));
i_ctzv_tbl_in.delete;
i_clev_tbl_in.delete;
i_ctzv_tbl_in.delete;
i_clev_tbl_in.delete;
i_cvtv_tbl_in.delete;
i_ctzv_tbl_in.delete;
i_clev_tbl_in.delete;
i_cvtv_tbl_in.delete;
i_ctzv_tbl_in.delete;
i_clev_tbl_in.delete;
i_cvtv_tbl_in.delete;
SELECT CTZ.ID TimeZone_ID,
CTZ.DNZ_CHR_ID DNZ_CHR_ID,
Times.ID Times_Id,
TIMES.ROWID TIMES_ROW_ID,
TIMES.TVE_ID_STARTED TVE_ID_STARTED,
TIMES.TVE_ID_ENDED TVE_ID_ENDED,
Times.Created_By Times_Created_By,
Times.Last_Updated_By Times_Last_Updated_By,
Times.Last_Update_Date Times_Last_Update_Date,
Times.Last_Update_Login Times_Last_Update_Login,
Times.Attribute15 Times_Attribute15,
Times.object_version_number Times_object_version_number,
Times.major_version Times_major_version
FROM OKS_COVERAGE_TIMEZONES CTZ,
OKC_RULE_GROUPS_BH RGP,
OKC_RULES_BH RUL,
OKC_COVER_TIMES_H CVT,
OKC_TIMEVALUES_BH TIMES
WHERE CTZ.Cle_Id = RGP.CLE_ID
AND CTZ.DNZ_CHR_ID = RGP.DNZ_CHR_ID
AND RGP.ID = RUL.RGP_ID
AND RGP.DNZ_CHR_ID = RUL.DNZ_CHR_ID
AND RUL.RULE_INFORMATION_CATEGORY = 'CVR'
AND RUL.ID = CVT.RUL_ID
AND CVT.TVE_ID = TIMES.ID
--AND times.id = 304783990308709311929711428529893238167
AND RGP.MAJOR_VERSION = RUL.MAJOR_VERSION
AND RUL.MAJOR_VERSION = CVT.MAJOR_VERSION
AND CVT.MAJOR_VERSION = TIMES.MAJOR_VERSION
AND Times.rowid BETWEEN l_start_rowid and l_end_rowid
AND NOT EXISTS (Select COV_TZE_LINE_ID from OKS_COVERAGE_TIMES_H where COV_TZE_LINE_ID= CTZ.ID);
SELECT tve_type,day_of_week,hour,minute
FROM okc_timevalues_BH
WHERE ID = l_tve_id;
TIMES_Last_Updated_By_TBL Num_Tbl_Type;
TIMES_Last_Update_Date_TBL Date_Tbl_Type;
TIMES_Last_Update_Login_TBL Num_Tbl_Type;
IN_PROGRAM_UPDATE_DATE OKC_DATATYPES.DateTabTyp;
IN_LAST_UPDATED_BY OKC_DATATYPES.NumberTabTyp;
IN_LAST_UPDATE_DATE OKC_DATATYPES.DateTabTyp;
IN_LAST_UPDATE_LOGIN OKC_DATATYPES.NumberTabTyp;
i_cvtv_tbl_in.DELETE;
TIMES_Last_Updated_By_TBL,
TIMES_Last_Update_Date_TBL,
TIMES_Last_Update_Login_TBL,
Times_Attribute15_TBL,
Times_obj_version_number_tbl,
Times_major_version_TBL
LIMIT 1000;
i_cvtv_tbl_in(l_cvt_rec).Last_Updated_By := TIMES_Last_Updated_By_TBL(i);
i_cvtv_tbl_in(l_cvt_rec).Last_Update_Date := TIMES_Last_Update_Date_TBL(i);
i_cvtv_tbl_in(l_cvt_rec).Last_Update_Login := TIMES_Last_Update_Login_TBL(i);
IN_PROGRAM_UPDATE_DATE(k) := i_cvtv_tbl_in(i).PROGRAM_UPDATE_DATE;
IN_LAST_UPDATED_BY(k) := i_cvtv_tbl_in(i).LAST_UPDATED_BY;
IN_LAST_UPDATE_DATE(k) := i_cvtv_tbl_in(i).LAST_UPDATE_DATE;
IN_LAST_UPDATE_LOGIN(k) := i_cvtv_tbl_in(i).LAST_UPDATE_LOGIN;
l_status := 'Before Insert';
INSERT INTO OKS_COVERAGE_TIMES_H(
ID,
DNZ_CHR_ID,
COV_TZE_LINE_ID,
START_HOUR,
START_MINUTE,
END_HOUR,
END_MINUTE,
MONDAY_YN,
TUESDAY_YN,
WEDNESDAY_YN,
THURSDAY_YN,
FRIDAY_YN,
SATURDAY_YN,
SUNDAY_YN,
SECURITY_GROUP_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
MAJOR_VERSION)VALUES
(
IN_ID (i),
IN_DNZ_CHR_ID (i),
IN_COV_TZE_LINE_ID (i),
IN_START_HOUR (i),
IN_START_MINUTE (i),
IN_END_HOUR (i),
IN_END_MINUTE (i),
IN_MONDAY_YN (i),
IN_TUESDAY_YN (i),
IN_WEDNESDAY_YN (i),
IN_THURSDAY_YN (i),
IN_FRIDAY_YN (i),
IN_SATURDAY_YN (i),
IN_SUNDAY_YN (i),
IN_SECURITY_GROUP_ID (i),
IN_PROGRAM_APPLICATION_ID (i),
IN_PROGRAM_ID (i),
IN_PROGRAM_UPDATE_DATE (i),
IN_REQUEST_ID (i),
IN_CREATED_BY (i),
IN_CREATION_DATE (i),
IN_LAST_UPDATED_BY (i),
IN_LAST_UPDATE_DATE (i),
IN_LAST_UPDATE_LOGIN (i),
IN_OBJECT_VERSION_NUMBER (i),
IN_MAJOR_VERSION (i));
SELECT
LINE.ID LINE_ID,
Line.Created_By Line_Created_By,
Line.Creation_Date Line_Creation_Date,
Line.Last_Updated_By Line_Last_Updated_By,
Line.Last_Update_Date Line_Last_Update_Date,
Line.Last_Update_Login Line_Last_Update_Login,
Rul.ROWID RUL_ROW_ID,
Rul.ID Rul_Id,
RGP.ID LINE_RGP_ID,
LINE.LSE_ID LINE_LSE_ID,
LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
OBJECT1_ID1 LINE_OBJECT1_ID1,
OBJECT2_ID1 LINE_OBJECT2_ID1,
RULE_INFORMATION1 COV_RULE_INFO1,
RULE_INFORMATION2 COV_RULE_INFO2,
RULE_INFORMATION3 COV_RULE_INFO3,
RULE_INFORMATION4 COV_RULE_INFO4,
RULE_INFORMATION5 COV_RULE_INFO5,
RULE_INFORMATION6 COV_RULE_INFO6,
RULE_INFORMATION7 COV_RULE_INFO7,
RULE_INFORMATION8 COV_RULE_INFO8,
RULE_INFORMATION9 COV_RULE_INFO9,
RULE_INFORMATION10 COV_RULE_INFO10,
RULE_INFORMATION11 COV_RULE_INFO11,
RULE_INFORMATION12 COV_RULE_INFO12,
RULE_INFORMATION13 COV_RULE_INFO13,
RULE_INFORMATION14 COV_RULE_INFO14,
RULE_INFORMATION15 COV_RULE_INFO15,
RULE_INFORMATION_CATEGORY COV_RULE_INFO,
RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER,
KINE.ID k_line_id,
Rul.major_version Rul_major_version
FROM
OKC_RULE_GROUPS_BH RGP,
OKC_RULES_BH RUL,
OKC_K_LINES_BH LINE,
OKS_K_LINES_B KINE
WHERE LINE.ID = KINE.CLE_ID
AND LINE.DNZ_CHR_ID = KINE.DNZ_CHR_ID
AND LINE.ID = RGP.CLE_ID
AND RGP.ID = RUL.RGP_ID
AND LINE.LSE_ID IN (4,17,22)
AND RUL.RULE_INFORMATION_CATEGORY IN ('RCN','RSN')
AND RGP.MAJOR_VERSION = RUL.MAJOR_VERSION
AND LINE.MAJOR_VERSION = RGP.MAJOR_VERSION
-- AND RUL.RULE_INFORMATION15 IS NULL
AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_BH WHERE CLE_ID = LINE.ID)
ORDER BY LINE.ID;
SELECT ID,
MAJOR_VERSION,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
COMMENTS,
TEXT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID
FROM OKC_RULES_TLH
WHERE ID = P_ID;
Select ID
FROM OKS_ACTION_TIME_TYPES
WHERE CLE_ID = p_Cle_ID;
SELECT CLE_ID,object_version_number --CLE_ID
FROM OKS_K_LINES_B
WHERE ID = P_ID;
Line_Last_Updated_By_TBL Num_Tbl_Type;
Line_Last_Update_Date_TBL Date_Tbl_Type;
Line_Last_Update_Login_TBL Num_Tbl_Type;
Line_Last_Updated_By_TBL ,
Line_Last_Update_Date_TBL ,
Line_Last_Update_Login_TBL ,
RUL_ROW_ID_TBL ,
RUL_ID_TBL ,
LINE_RGP_ID_TBL ,
LINE_LSE_ID_TBL ,
LINE_DNZ_CHR_ID_TBL ,
LINE_OBJECT1_ID1_TBL ,
LINE_OBJECT2_ID1_TBL ,
COV_RULE_INFO1_TBL ,
COV_RULE_INFO2_TBL ,
COV_RULE_INFO3_TBL ,
COV_RULE_INFO4_TBL ,
COV_RULE_INFO5_TBL ,
COV_RULE_INFO6_TBL ,
COV_RULE_INFO7_TBL ,
COV_RULE_INFO8_TBL ,
COV_RULE_INFO9_TBL ,
COV_RULE_INFO10_TBL ,
COV_RULE_INFO11_TBL ,
COV_RULE_INFO12_TBL ,
COV_RULE_INFO13_TBL ,
COV_RULE_INFO14_TBL ,
COV_RULE_INFO15_TBL ,
COV_RULE_INFO_TBL ,
COV_OBJ_VER_NUMBER_TBL ,
k_line_id_tbl ,
Rul_major_version_tbl
LIMIT 1000;
i_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
i_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
i_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
l_clet_tbl_in(l_clt_ctr).last_updated_by := Get_Rule_TlH_REC.last_updated_by;
l_clet_tbl_in(l_clt_ctr).last_update_date := Get_Rule_TlH_REC.last_update_date;
l_clet_tbl_in(l_clt_ctr).last_update_login := Get_Rule_TlH_REC.last_update_login;
Insert_Into_Klines( p_clev_tbl_in => i_clev_tbl_in,
p_clet_tbl_in => l_clet_tbl_in,
x_return_Status => l_return_status);
i_clev_tbl_in.DELETE;
i_actv_tbl_in.DELETE;
i_clev_tbl_in.DELETE;
i_actv_tbl_in.DELETE;
i_clev_tbl_in.DELETE;
SELECT TYP.id Action_Type_ID,
TYP.cle_id Action_Type_Cle_ID ,
TYP.dnz_chr_id Action_Type_Dnz_ID ,
TYP.Created_By Created_By,
TYP.Last_Updated_By Last_Updated_By,
TYP.Last_Update_Date Last_Update_Date,
TYP.Last_Update_Login Last_Update_Login,
RIN.UOM_CODE UOM_CODE,
RIN.DURATION DURATION,
TIM.DAY_OF_WEEK DAY_OF_WEEK,
TIM.TVE_TYPE TVE_TYPE,
Rul.ID RUL_ID,
Rul.major_version Rul_major_version
FROM oks_action_time_types_h TYP,
okc_rule_groups_bh RGP,okc_rules_bh RUL,
okc_timevalues_bh TIM,okc_react_intervals_h RIN
WHERE RGP.CLE_ID = TYP.CLE_ID
AND RGP.DNZ_CHR_ID = TYP.DNZ_CHR_ID
AND RGP.ID = RUL.RGP_ID
AND RGP.DNZ_CHR_ID = RUL.DNZ_CHR_ID
AND RUL.DNZ_CHR_ID = TYP.DNZ_CHR_ID
AND RUL.ID = RIN.RUL_ID
AND rul.RULE_INFORMATION_CATEGORY = typ.ACTION_TYPE_CODE
AND RIN.DNZ_CHR_ID = TYP.DNZ_CHR_ID
AND TIM.ID = RIN.TVE_ID
AND TIM.DNZ_CHR_ID = RIN.DNZ_CHR_ID
AND TIM.DNZ_CHR_ID = RGP.DNZ_CHR_ID
AND TIM.DNZ_CHR_ID = RUL.DNZ_CHR_ID
AND TIM.DNZ_CHR_ID = TYP.DNZ_CHR_ID
AND RGP.major_version = TYP.major_version
AND RGP.major_version = rul.major_version
AND rul.major_version = TYP.major_version
AND RIN.major_version = TYP.major_version
AND RIN.major_version = RUL.major_version
AND TIM.major_version = RIN.major_version
AND NOT EXISTS (Select cle_id FROM OKS_ACTION_TIMES_H WHERE CLE_ID = TYP.cle_id and major_version = typ.major_version)
ORDER BY RUL.ID;
Last_Updated_By_TBL Num_Tbl_Type;
Last_Update_Date_TBL Date_Tbl_Type;
Last_Update_Login_TBL Num_Tbl_Type;
Last_Updated_By_TBL,
Last_Update_Date_TBL,
Last_Update_Login_TBL,
UOM_CODE_TBL,
DURATION_TBL,
DAY_OF_WEEK_TBL,
TVE_TYPE_TBL,
RUL_ID_TBL,
Rul_major_version_tbl
LIMIT 1000;
i_acmv_tbl_in(l_acm_ctr).Last_Updated_By := Last_Updated_By_TBL(i);
i_acmv_tbl_in(l_acm_ctr).Last_Update_Date := Last_Update_Date_TBL(i);
i_acmv_tbl_in(l_acm_ctr).Last_Update_Login := Last_Update_Login_TBL(i);
i_acmv_tbl_in(l_acm_ctr).PROGRAM_UPDATE_DATE := NULL;
IN_PROGRAM_UPDATE_DATE(K) := i_acmv_tbl_in(I).PROGRAM_UPDATE_DATE ;
IN_LAST_UPDATED_BY(K) := i_acmv_tbl_in(I).LAST_UPDATED_BY ;
IN_LAST_UPDATE_DATE(K) := i_acmv_tbl_in(I).LAST_UPDATE_DATE ;
IN_LAST_UPDATE_LOGIN(K) := i_acmv_tbl_in(I).LAST_UPDATE_LOGIN ;
INSERT INTO OKS_ACTION_TIMES_H(
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,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
MAJOR_VERSION)
VALUES (
IN_ID(I),
IN_COV_ACTION_TYPE_ID(I),
IN_CLE_ID(I),
IN_DNZ_CHR_ID(I),
IN_UOM_CODE(I),
IN_SUN_DURATION(I),
IN_MON_DURATION(I),
IN_TUE_DURATION(I),
IN_WED_DURATION(I),
IN_THU_DURATION(I),
IN_FRI_DURATION(I),
IN_SAT_DURATION(I),
IN_SECURITY_GROUP_ID(I),
IN_PROGRAM_APPLICATION_ID(I),
IN_PROGRAM_ID(I),
IN_PROGRAM_UPDATE_DATE(I),
IN_REQUEST_ID(I),
IN_CREATED_BY(I),
IN_CREATION_DATE(I),
IN_LAST_UPDATED_BY(I),
IN_LAST_UPDATE_DATE(I),
IN_LAST_UPDATE_LOGIN(I),
IN_OBJECT_VERSION_NUMBER(I),
IN_MAJOR_VERSION(I));
i_acmv_tbl_in.DELETE;
SELECT
LINE.ID LINE_ID,
Line.Created_By Line_Created_By,
Line.Creation_Date Line_Creation_Date,
Line.Last_Updated_By Line_Last_Updated_By,
Line.Last_Update_Date Line_Last_Update_Date,
Line.Last_Update_Login Line_Last_Update_Login,
Rul.ROWID RUL_ROW_ID,
Rul.ID Rul_Id,
RGP.ID LINE_RGP_ID,
LINE.LSE_ID LINE_LSE_ID,
LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
OBJECT1_ID1 LINE_OBJECT1_ID1,
OBJECT2_ID1 LINE_OBJECT2_ID1,
RULE_INFORMATION1 COV_RULE_INFO1,
RULE_INFORMATION2 COV_RULE_INFO2,
RULE_INFORMATION3 COV_RULE_INFO3,
RULE_INFORMATION4 COV_RULE_INFO4,
RULE_INFORMATION5 COV_RULE_INFO5,
RULE_INFORMATION6 COV_RULE_INFO6,
RULE_INFORMATION7 COV_RULE_INFO7,
RULE_INFORMATION8 COV_RULE_INFO8,
RULE_INFORMATION9 COV_RULE_INFO9,
RULE_INFORMATION10 COV_RULE_INFO10,
RULE_INFORMATION11 COV_RULE_INFO11,
RULE_INFORMATION12 COV_RULE_INFO12,
RULE_INFORMATION13 COV_RULE_INFO13,
RULE_INFORMATION14 COV_RULE_INFO14,
RULE_INFORMATION15 COV_RULE_INFO15,
RULE_INFORMATION_CATEGORY COV_RULE_INFO,
RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER,
RUL.MAJOR_VERSION RUL_MAJOR_VERSION
FROM
OKC_RULE_GROUPS_BH RGP,
OKC_RULES_BH RUL,
OKC_K_LINES_BH LINE
WHERE LINE.ID = RGP.CLE_ID
AND RGP.ID = RUL.RGP_ID
AND LINE.LSE_ID IN (5,59,23)
--AND RUL.RULE_INFORMATION_CATEGORY IN ('LMT')
AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
AND RGP.MAJOR_VERSION = LINE.MAJOR_VERSION
AND RGP.MAJOR_VERSION = RUL.MAJOR_VERSION
AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_BH WHERE CLE_ID = LINE.ID)
ORDER BY LINE.ID;
SELECT ID,
MAJOR_VERSION,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
COMMENTS,
TEXT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID
FROM OKC_RULES_TLH
WHERE ID = P_ID;
Line_Last_Updated_By_TBL Num_Tbl_Type;
Line_Last_Update_Date_TBL Date_Tbl_Type;
Line_Last_Update_Login_TBL Num_Tbl_Type;
Line_Last_Updated_By_TBL ,
Line_Last_Update_Date_TBL ,
Line_Last_Update_Login_TBL ,
RUL_ROW_ID_TBL ,
Rul_Id_Tbl ,
LINE_RGP_ID_TBL ,
LINE_LSE_ID_TBL ,
LINE_DNZ_CHR_ID_TBL ,
LINE_OBJECT1_ID1_TBL ,
LINE_OBJECT2_ID1_TBL ,
COV_RULE_INFO1_TBL ,
COV_RULE_INFO2_TBL ,
COV_RULE_INFO3_TBL ,
COV_RULE_INFO4_TBL ,
COV_RULE_INFO5_TBL ,
COV_RULE_INFO6_TBL ,
COV_RULE_INFO7_TBL ,
COV_RULE_INFO8_TBL ,
COV_RULE_INFO9_TBL ,
COV_RULE_INFO10_TBL ,
COV_RULE_INFO11_TBL ,
COV_RULE_INFO12_TBL ,
COV_RULE_INFO13_TBL ,
COV_RULE_INFO14_TBL ,
COV_RULE_INFO15_TBL ,
COV_RULE_INFO_TBL ,
COV_OBJ_VER_NUMBER_TBL ,
RUL_MAJOR_VERSION_TBL
LIMIT 1000;
i_clev_tbl_in(l_cle_ctr).Last_Updated_By :=
Line_Last_Updated_By_TBL(i);
i_clev_tbl_in(l_cle_ctr).Last_Update_Date :=
Line_Last_Update_Date_TBL(i);
i_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
l_clet_tbl_in(l_clt_ctr).last_updated_by := Get_Rule_TlH_REC.last_updated_by;
l_clet_tbl_in(l_clt_ctr).last_update_date := Get_Rule_TlH_REC.last_update_date;
l_clet_tbl_in(l_clt_ctr).last_update_login := Get_Rule_TlH_REC.last_update_login;
Insert_Into_Klines( p_clev_tbl_in => i_clev_tbl_in,
p_clet_tbl_in => l_clet_tbl_in,
x_return_Status => l_return_status);
i_clev_tbl_in.DELETE;
i_clev_tbl_in.DELETE;