[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_CHAR(TZ1.Offset_Time,'HH24') Offset_Hours,
TO_CHAR(TZ1.Offset_Time,'MI') Offset_Mts,
TZ1.Offset_Indicator Offset_Indicator
FROM CS_TIME_ZONES TZ1
WHERE TZ1.Time_Zone_Id = p_time_zone_id;
SELECT TO_CHAR(TZ1.Offset_Time,'HH24') Offset_Hours,
TO_CHAR(TZ1.Offset_Time,'MI') Offset_Mts,
TZ1.Offset_Indicator Offset_Indicator
FROM CS_TIME_ZONES TZ1
WHERE TZ1.Time_Zone_Id = p_time_zone_id;
SELECT Decode(p_coverage_day,
1, To_Char(TXN.Sunday_Start_Time ,'HH24'),
2, To_Char(TXN.Monday_Start_Time ,'HH24'),
3, To_Char(TXN.Tuesday_Start_Time ,'HH24'),
4, To_Char(TXN.Wednesday_Start_Time,'HH24'),
5, To_Char(TXN.Thursday_Start_Time ,'HH24'),
6, To_Char(TXN.Friday_Start_Time ,'HH24'),
7, To_Char(TXN.Saturday_Start_Time ,'HH24'))
Cov_Start_Time_Hours,
Decode(p_coverage_day,
1, To_Char(TXN.Sunday_Start_Time ,'MI'),
2, To_Char(TXN.Monday_Start_Time ,'MI'),
3, To_Char(TXN.Tuesday_Start_Time ,'MI'),
4, To_Char(TXN.Wednesday_Start_Time,'MI'),
5, To_Char(TXN.Thursday_Start_Time ,'MI'),
6, To_Char(TXN.Friday_Start_Time ,'MI'),
7, To_Char(TXN.Saturday_Start_Time ,'MI'))
Cov_Start_Time_Mts,
Decode(p_coverage_day,
1, To_Char(TXN.Sunday_End_Time ,'HH24'),
2, To_Char(TXN.Monday_End_Time ,'HH24'),
3, To_Char(TXN.Tuesday_End_Time ,'HH24'),
4, To_Char(TXN.Wednesday_End_Time,'HH24'),
5, To_Char(TXN.Thursday_End_Time ,'HH24'),
6, To_Char(TXN.Friday_End_Time ,'HH24'),
7, To_Char(TXN.Saturday_End_Time ,'HH24'))
Cov_End_Time_Hours,
Decode(p_coverage_day,
1, To_Char(TXN.Sunday_End_Time ,'MI'),
2, To_Char(TXN.Monday_End_Time ,'MI'),
3, To_Char(TXN.Tuesday_End_Time ,'MI'),
4, To_Char(TXN.Wednesday_End_Time,'MI'),
5, To_Char(TXN.Thursday_End_Time ,'MI'),
6, To_Char(TXN.Friday_End_Time ,'MI'),
7, To_Char(TXN.Saturday_End_Time ,'MI'))
Cov_End_Time_Mts
FROM CS_COVERAGE_TXN_GROUPS TXN
WHERE TXN.Coverage_Txn_Group_Id = P_Coverage_Txn_Group_Id;
SELECT CRT.Reaction_time_id,
CRT.Name Reaction_Time,
Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
1, CRT.Reaction_Time_Sunday ,
2, CRT.Reaction_Time_Monday ,
3, CRT.Reaction_Time_Tuesday ,
4, CRT.Reaction_Time_Wednesday,
5, CRT.Reaction_Time_Thursday ,
6, CRT.Reaction_Time_Friday ,
7, CRT.Reaction_Time_Saturday) Reaction_Time,
To_Char(p_start_date_time,'HH24') Start_Time_Hours,
To_Char(p_start_date_time,'MI') Start_Time_Mts,
Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
1, SUBSTR(CRT.Reaction_Time_Sunday,1,
LENGTH(CRT.Reaction_Time_Sunday) - 2),
2, SUBSTR(CRT.Reaction_Time_Monday,1,
LENGTH(CRT.Reaction_Time_Monday) - 2),
3, SUBSTR(CRT.Reaction_Time_Tuesday,1,
LENGTH(CRT.Reaction_Time_Tuesday) - 2),
4, SUBSTR(CRT.Reaction_Time_Wednesday,1,
LENGTH(CRT.Reaction_Time_Wednesday) - 2),
5, SUBSTR(CRT.Reaction_Time_Thursday,1,
LENGTH(CRT.Reaction_Time_Thursday ) - 2),
6, SUBSTR(CRT.Reaction_Time_Friday,1,
LENGTH(CRT.Reaction_Time_Friday ) - 2),
7, SUBSTR(CRT.Reaction_Time_Saturday,1,
LENGTH(CRT.Reaction_Time_Saturday) - 2))
Reaction_Time_Hours,
Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
1, SUBSTR(CRT.Reaction_Time_Sunday,
LENGTH(CRT.Reaction_Time_Sunday ) - 1,2),
2, SUBSTR(CRT.Reaction_Time_Monday,
LENGTH(CRT.Reaction_Time_Monday ) - 1,2),
3, SUBSTR(CRT.Reaction_Time_Tuesday,
LENGTH(CRT.Reaction_Time_Tuesday ) - 1,2),
4, SUBSTR(CRT.Reaction_Time_Wednesday,
LENGTH(CRT.Reaction_Time_Wednesday) - 1,2),
5, SUBSTR(CRT.Reaction_Time_Thursday,
LENGTH(CRT.Reaction_Time_Thursday ) - 1,2),
6, SUBSTR(CRT.Reaction_Time_Friday,
LENGTH(CRT.Reaction_Time_Friday ) - 1,2),
7, SUBSTR(CRT.Reaction_Time_Saturday,
LENGTH(CRT.Reaction_Time_Saturday) - 1,2))
Reaction_Time_Mts,
CRT.Reaction_Time_Sunday ,
CRT.Reaction_Time_Monday ,
CRT.Reaction_Time_Tuesday ,
CRT.Reaction_Time_Wednesday,
CRT.Reaction_Time_Thursday ,
CRT.Reaction_Time_Friday ,
CRT.Reaction_Time_Saturday ,
CRT.Workflow ,
INS.Name Incident_Severity,
CRT.Always_Covered ,
CRT.Use_for_SR_Date_Calc ,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, To_Char(TXN.Sunday_Start_Time ,'HH24'),
2, To_Char(TXN.Monday_Start_Time ,'HH24'),
3, To_Char(TXN.Tuesday_Start_Time ,'HH24'),
4, To_Char(TXN.Wednesday_Start_Time,'HH24'),
5, To_Char(TXN.Thursday_Start_Time ,'HH24'),
6, To_Char(TXN.Friday_Start_Time ,'HH24'),
7, To_Char(TXN.Saturday_Start_Time ,'HH24'))
Cov_Start_Time_Hours,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, To_Char(TXN.Sunday_Start_Time ,'MI'),
2, To_Char(TXN.Monday_Start_Time ,'MI'),
3, To_Char(TXN.Tuesday_Start_Time ,'MI'),
4, To_Char(TXN.Wednesday_Start_Time,'MI'),
5, To_Char(TXN.Thursday_Start_Time ,'MI'),
6, To_Char(TXN.Friday_Start_Time ,'MI'),
7, To_Char(TXN.Saturday_Start_Time ,'MI'))
Cov_Start_Time_Mts,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, To_Char(TXN.Sunday_End_Time ,'HH24'),
2, To_Char(TXN.Monday_End_Time ,'HH24'),
3, To_Char(TXN.Tuesday_End_Time ,'HH24'),
4, To_Char(TXN.Wednesday_End_Time,'HH24'),
5, To_Char(TXN.Thursday_End_Time ,'HH24'),
6, To_Char(TXN.Friday_End_Time ,'HH24'),
7, To_Char(TXN.Saturday_End_Time ,'HH24'))
Cov_End_Time_Hours,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, To_Char(TXN.Sunday_End_Time ,'MI'),
2, To_Char(TXN.Monday_End_Time ,'MI'),
3, To_Char(TXN.Tuesday_End_Time ,'MI'),
4, To_Char(TXN.Wednesday_End_Time,'MI'),
5, To_Char(TXN.Thursday_End_Time ,'MI'),
6, To_Char(TXN.Friday_End_Time ,'MI'),
7, To_Char(TXN.Saturday_End_Time ,'MI'))
Cov_End_Time_Mts,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, 1,
2, 2,
3, 3,
4, 4,
5, 5,
6, 6,
7, 7),
TXN.Time_Zone_Id ,
TXN.Coverage_Txn_Group_Id
FROM CS_COV_REACTION_TIMES CRT,
CS_INCIDENT_SEVERITIES INS,
CS_COVERAGES COV,
CS_COVERAGE_TXN_GROUPS TXN
WHERE COV.Coverage_id = l_coverage_id
AND COV.Coverage_id = TXN.Coverage_id
AND TXN.Business_Process_Id = p_business_process_id
AND TXN.Coverage_Txn_Group_Id = CRT.Coverage_Txn_Group_Id
AND CRT.Incident_Severity_Id = p_incident_severity_id
AND CRT.Use_For_SR_Date_Calc = 'Y'
AND CRT.Incident_Severity_Id = INS.Incident_Severity_Id(+);
SELECT CRT.Name Reaction_Time,
Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
1, CRT.Reaction_Time_Sunday ,
2, CRT.Reaction_Time_Monday ,
3, CRT.Reaction_Time_Tuesday ,
4, CRT.Reaction_Time_Wednesday,
5, CRT.Reaction_Time_Thursday ,
6, CRT.Reaction_Time_Friday ,
7, CRT.Reaction_Time_Saturday) Reaction_Time,
To_Char(p_start_date_time,'HH24') Start_Time_Hours,
To_Char(p_start_date_time,'MI') Start_Time_Mts,
Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
1, SUBSTR(CRT.Reaction_Time_Sunday,1,
LENGTH(CRT.Reaction_Time_Sunday) - 2),
2, SUBSTR(CRT.Reaction_Time_Monday,1,
LENGTH(CRT.Reaction_Time_Monday) - 2),
3, SUBSTR(CRT.Reaction_Time_Tuesday,1,
LENGTH(CRT.Reaction_Time_Tuesday) - 2),
4, SUBSTR(CRT.Reaction_Time_Wednesday,1,
LENGTH(CRT.Reaction_Time_Wednesday) - 2),
5, SUBSTR(CRT.Reaction_Time_Thursday,1,
LENGTH(CRT.Reaction_Time_Thursday ) - 2),
6, SUBSTR(CRT.Reaction_Time_Friday,1,
LENGTH(CRT.Reaction_Time_Friday ) - 2),
7, SUBSTR(CRT.Reaction_Time_Saturday,1,
LENGTH(CRT.Reaction_Time_Saturday) - 2))
Reaction_Time_Hours,
Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
1, SUBSTR(CRT.Reaction_Time_Sunday,
LENGTH(CRT.Reaction_Time_Sunday ) - 1,2),
2, SUBSTR(CRT.Reaction_Time_Monday,
LENGTH(CRT.Reaction_Time_Monday ) - 1,2),
3, SUBSTR(CRT.Reaction_Time_Tuesday,
LENGTH(CRT.Reaction_Time_Tuesday ) - 1,2),
4, SUBSTR(CRT.Reaction_Time_Wednesday,
LENGTH(CRT.Reaction_Time_Wednesday) - 1,2),
5, SUBSTR(CRT.Reaction_Time_Thursday,
LENGTH(CRT.Reaction_Time_Thursday ) - 1,2),
6, SUBSTR(CRT.Reaction_Time_Friday,
LENGTH(CRT.Reaction_Time_Friday ) - 1,2),
7, SUBSTR(CRT.Reaction_Time_Saturday,
LENGTH(CRT.Reaction_Time_Saturday) - 1,2))
Reaction_Time_Mts,
CRT.Reaction_Time_Sunday ,
CRT.Reaction_Time_Monday ,
CRT.Reaction_Time_Tuesday ,
CRT.Reaction_Time_Wednesday,
CRT.Reaction_Time_Thursday ,
CRT.Reaction_Time_Friday ,
CRT.Reaction_Time_Saturday ,
CRT.Workflow ,
INS.Name Incident_Severity,
CRT.Always_Covered ,
CRT.Use_for_SR_Date_Calc ,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, To_Char(TXN.Sunday_Start_Time ,'HH24'),
2, To_Char(TXN.Monday_Start_Time ,'HH24'),
3, To_Char(TXN.Tuesday_Start_Time ,'HH24'),
4, To_Char(TXN.Wednesday_Start_Time,'HH24'),
5, To_Char(TXN.Thursday_Start_Time ,'HH24'),
6, To_Char(TXN.Friday_Start_Time ,'HH24'),
7, To_Char(TXN.Saturday_Start_Time ,'HH24'))
Cov_Start_Time_Hours,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, To_Char(TXN.Sunday_Start_Time ,'MI'),
2, To_Char(TXN.Monday_Start_Time ,'MI'),
3, To_Char(TXN.Tuesday_Start_Time ,'MI'),
4, To_Char(TXN.Wednesday_Start_Time,'MI'),
5, To_Char(TXN.Thursday_Start_Time ,'MI'),
6, To_Char(TXN.Friday_Start_Time ,'MI'),
7, To_Char(TXN.Saturday_Start_Time ,'MI'))
Cov_Start_Time_Mts,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, To_Char(TXN.Sunday_End_Time ,'HH24'),
2, To_Char(TXN.Monday_End_Time ,'HH24'),
3, To_Char(TXN.Tuesday_End_Time ,'HH24'),
4, To_Char(TXN.Wednesday_End_Time,'HH24'),
5, To_Char(TXN.Thursday_End_Time ,'HH24'),
6, To_Char(TXN.Friday_End_Time ,'HH24'),
7, To_Char(TXN.Saturday_End_Time ,'HH24'))
Cov_End_Time_Hours,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, To_Char(TXN.Sunday_End_Time ,'MI'),
2, To_Char(TXN.Monday_End_Time ,'MI'),
3, To_Char(TXN.Tuesday_End_Time ,'MI'),
4, To_Char(TXN.Wednesday_End_Time,'MI'),
5, To_Char(TXN.Thursday_End_Time ,'MI'),
6, To_Char(TXN.Friday_End_Time ,'MI'),
7, To_Char(TXN.Saturday_End_Time ,'MI'))
Cov_End_Time_Mts,
Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
1, 1,
2, 2,
3, 3,
4, 4,
5, 5,
6, 6,
7, 7),
TXN.Time_Zone_Id ,
TXN.Coverage_Txn_Group_Id
FROM CS_COV_REACTION_TIMES CRT,
CS_INCIDENT_SEVERITIES INS,
CS_COVERAGES COV,
CS_COVERAGE_TXN_GROUPS TXN
WHERE COV.Coverage_id = l_coverage_id
AND COV.Coverage_id = TXN.Coverage_id
AND TXN.Business_Process_Id = p_business_process_id
AND TXN.Coverage_Txn_Group_Id = CRT.Coverage_Txn_Group_Id
AND CRT.Reaction_time_id = p_reaction_time_id
AND CRT.Use_For_SR_Date_Calc = 'Y'
AND CRT.Incident_Severity_Id = INS.Incident_Severity_Id(+);