[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fnd_profile.value('BIX_DM_PREFERRED_CURRENCY')
INTO l_global_curr
FROM dual;
SELECT fnd_profile.value('JTF_PROFILE_DEFAULT_CURRENCY')
INTO l_user_curr
FROM dual;
SELECT fnd_profile.value('BIX_DM_CURR_CONVERSION_TYPE')
INTO l_conv_type
FROM dual;
/* Form the SQL string to insert the rows into the temp table */
g_sqlstmt := '
INSERT /*+ PARALLEL(tr,2) */ INTO bix_dm_report tr (
session_id
, report_code
, col3
, col4
, col5
, col6
, col8
, col10
, col12
, col14
, col16
, col18
, col20
, col22
, col24
, col26
, col28
, col30
, col32
, col34
, col36
, col38
, col40 )
(SELECT /*+ PARALLEL(a,2) */
:session_id
, ''BIX_KPI_AGENT_RPT'' ';
, trunc(SUM(leads_updated))
, to_char(((SUM(leads_amount)) / :denom_rate) * :num_rate, :format_mask)
, trunc(SUM(opportunities_created))
, trunc(SUM(opportunities_updated))
, trunc(SUM(opportunities_won))
, to_char(((SUM(opportunities_won_amount)) / :denom_rate) * :num_rate, :format_mask) ';
/* Form the SQL string to insert the rows into the temp table */
g_sqlstmt := '
INSERT /*+ PARALLEL(tr,2) */ INTO bix_dm_report tr (
session_id
, report_code
, col2
, col3
, col4
, col5
, col8
, col10
, col12
, col14
, col16
, col18
, col20
, col22
, col24
, col26
, col28
, col30
, col32
, col34
, col36
, col38
, col40 )
(SELECT /*+ PARALLEL(a,2) */
:session_id
, ''BIX_KPI_AGENT_RPT'' ';
, trunc(SUM(leads_updated))
, to_char(((SUM(leads_amount)) / :denom_rate) * :num_rate, :format_mask)
, trunc(SUM(opportunities_created))
, trunc(SUM(opportunities_updated))
, trunc(SUM(opportunities_won))
, to_char(((SUM(opportunities_won_amount)) / :denom_rate) * :num_rate, :format_mask) ';
/* This procedure form and execute the SQL statement to insert into */
/* BIX temp table all the detail rows of a resource */
PROCEDURE insert_rs_detail_temp_table(p_agent_id IN NUMBER)
IS
BEGIN
/* Form and execute the SQL statement to insert into the temp table */
/* all the detail rows corresponding to the agent p_agent_id */
form_rs_sqlstmt(FALSE);
END insert_rs_detail_temp_table;
/* insert into BIX temp table the total row of a resource */
PROCEDURE insert_rs_total_temp_table(p_group_id IN NUMBER)
IS
BEGIN
/* Form and execute the SQL statement to insert into the temp table one */
/* row for each of the agents which belong to the agent group p_group_id */
form_rs_sqlstmt(TRUE);
END insert_rs_total_temp_table;
/* This procedure form and execute the SQL statement to insert both */
/* the detail and total row for the agent group */
PROCEDURE insert_group_temp_table(p_group_id IN NUMBER)
IS
BEGIN
/* Form and execute the SQL statement to insert into the temp table */
/* the total row corresponding to the agent group "p_group_id" */
form_group_sqlstmt(TRUE);
/* Form and execute the SQL statement to insert into the temp table one row for each */
/* of the agent groups which are immediate children of the group p_group_id */
form_group_sqlstmt(FALSE);
END insert_group_temp_table;
SELECT MAX(period_start_date)
INTO l_temp_date
FROM bix_dm_agent_call_sum;
/* Period Indicator = 7 indicates that user has selected today as reporting period */
g_from_date := to_date(to_char(g_sysdate, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
/* Period Indicator = 8 indicates that user has selected yesterday as reporting period */
g_from_date := to_date(to_char(g_sysdate-1, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
SELECT icx_sec.g_session_id
INTO g_session_id
FROM dual;
/* Delete the rows from the table bix_dm_report for the current icx session and report */
/* so that we donot display the leftover rows from the previous execution of the report */
DELETE bix_dm_report
WHERE report_code = 'BIX_KPI_AGENT_RPT'
AND session_id = g_session_id;
SELECT sysdate
INTO g_sysdate
FROM dual;
SELECT fnd_profile.value('BIX_DM_RPT_TIME_RANGE')
INTO g_time_range
FROM dual;
SELECT TO_NUMBER(substr(g_parent, 2, decode(instr(g_parent,'c'), 0, length(g_parent), instr(g_parent,'c')-2)))
INTO l_group_id
FROM dual;
/* If the user has selected "All" for agent group paramter , display the default group of the user */
IF (l_group_id = -999) THEN
SELECT fnd_profile.value('BIX_DM_DEFAULT_GROUP')
INTO l_group_id
FROM dual;
/* l_group_id = "null" : user has selected "All" as agent group paramter and (s)he is not assigned to any default group */
IF (l_group_id IS NULL) THEN
RETURN;
insert_group_temp_table(l_group_id);
insert_rs_total_temp_table(l_group_id);
/* to insert the detail rows of the agent into the temp table */
IF (instr(g_parent,'c') <> 0) THEN
l_agent_id := TO_NUMBER(substr(g_parent, instr(g_parent, 'c')+1, length(g_parent) - (instr(g_parent,'c')+1)));
/* Update the temp table so that next time the user clicked on the */
/* same agent we donot display the detail rows of the agent again */
UPDATE bix_dm_report
SET col3 = 'p' || l_group_id || 'c' || to_char(l_agent_id) || 'y'
WHERE col3 = 'p' || l_group_id || 'c' || to_char(l_agent_id) || 'n'
AND report_code = 'BIX_KPI_AGENT_RPT'
AND session_id = g_session_id;
/* summary table and insert them into bix temp table */
insert_rs_detail_temp_table(l_agent_id);