The following lines contain the word 'select', 'insert', 'update' or 'delete':
02/14/2001 Cursor Sales Group SELECT is modified as per Thanh
Huynh(02/11). The new statement looks very small and neat.
Refer the previous version for old SQL.
02/27/2001 Thanh adviced us to check for delete flag = 'N' while
using group members table in order to pickup only the
valid members.
*/
TYPE day_table IS
TABLE OF VARCHAR2(2000)
INDEX BY BINARY_INTEGER;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY')
INTO v_date_time
FROM DUAL;
htp.tableData('' || htf.bold('Please specify the criteria and select OK. ') || '', cAlign => 'center', cRowSpan => '2', cColSpan => '110', cAttributes => ' width=346');
select probability_value win_probability
from as_forecast_prob
where UPPER(enabled_flag) = 'Y'
and trunc(SYSDATE) between nvl(start_date_active, trunc(SYSDATE))
and nvl(end_date_active, trunc(SYSDATE))
order by 1;
select lookup_code
from fnd_lookup_values
where lookup_type='AST_INTERNAL_PROCESS_STATUS'
and enabled_flag='Y';
SELECT grpd.group_id sgi
, DECODE(grpd.group_id
, grpd.parent_group_id
, DECODE(topgrp.manager_flag
, 'Y'
, grptl.group_name, ' *'||grptl.group_name
)
, DECODE(topgrp.manager_flag
, 'Y'
, DECODE(grpd.immediate_parent_flag
, 'Y'
, '-'||grptl.group_name, '--'||grptl.group_name
)
, DECODE(grpd.immediate_parent_flag
, 'Y'
, ' -'||grptl.group_name, ' --'||grptl.group_name
)
)
) name
FROM Jtf_rs_groups_denorm grpd
, Jtf_rs_groups_tl grptl
, (SELECT DISTINCT grpb.group_id
, rrb2.manager_flag
FROM Jtf_rs_groups_b grpb
, Jtf_rs_role_relations rrel2
, Jtf_rs_roles_b rrb2
, Jtf_rs_resource_extns rsc2
, Jtf_rs_group_members mem
, Fnd_user fnu
WHERE grpb.group_id = mem.group_id
AND TRUNC(SYSDATE) BETWEEN grpb.start_date_active
AND NVL(grpb.end_date_active, TRUNC(SYSDATE))
AND rrb2.role_type_code IN ('SALES', 'TELESALES')
AND (rrb2.manager_flag = 'Y'
OR rrb2.admin_flag = 'Y')
AND rrel2.role_id = rrb2.role_id
AND TRUNC(SYSDATE) BETWEEN rrel2.start_date_active
AND NVL(rrel2.end_date_active, TRUNC(SYSDATE))
AND rrel2.role_resource_type = 'RS_GROUP_MEMBER'
AND rrel2.role_resource_id = mem.group_member_id
-- Begin Mod Raam on 02.27.2001
AND mem.delete_flag = 'N'
-- End Mod.
AND mem.resource_id = rsc2.resource_id
AND rsc2.source_id = fnu.employee_id
AND fnu.user_id = P_Userid) topgrp
WHERE grptl.group_id = grpd.group_id
AND grpd.parent_group_id = topgrp.group_id
AND TRUNC(SYSDATE) BETWEEN grpd.start_date_active
AND NVL(grpd.end_date_active, TRUNC(SYSDATE))
ORDER BY 2 DESC;
SELECT currency_code
FROM Fnd_currencies
WHERE UPPER(enabled_flag) = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE))
AND NVL(end_date_active, SYSDATE)
ORDER BY 1;
htp.p(' var s_day = objform.p_sd_date.selectedIndex;');
htp.p(' var s_month = objform.p_sm_date.selectedIndex;');
htp.p(' var s_year = objform.p_sy_date.selectedIndex;');
htp.p(' var e_day = objform.p_ed_date.selectedIndex;');
htp.p(' var e_month = objform.p_em_date.selectedIndex;');
htp.p(' var e_year = objform.p_ey_date.selectedIndex;');
htp.p(' var low_prob = objform.p_low_prob.selectedIndex;');
htp.p(' var high_prob = objform.p_high_prob.selectedIndex;');
htp.p('Select Output Format ');
htp.p('
htp.formSelectOption('Excel');
htp.formSelectOption('HTML', cSelected => 'TRUE');
htp.formSelectClose;
htp.p('
htp.formSelectOption(day_data(i),
cAttributes => ' value= ' || day_data(i),
cSelected => 'TRUE' );
htp.formSelectClose;
htp.p('
htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'),
cSelected => 'TRUE');
htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'));
htp.formSelectClose;
htp.p('
htp.formSelectOption(year_data(i),
cAttributes => ' value= ' || year_data(i),
cSelected => 'TRUE');
htp.formSelectOption(year_data(i),
cAttributes => ' value= ' || year_data(i));
htp.formSelectClose;
htp.p('
htp.formSelectOption(day_data(i),
cAttributes => ' value= ' || day_data(i),
cSelected => 'TRUE' );
htp.formSelectClose;
htp.p('
htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'),
cSelected => 'TRUE');
htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'));
htp.formSelectClose;
htp.p ('
htp.formSelectOption(year_data(i),
cAttributes => ' value= ' || year_data(i),
cSelected => 'TRUE' );
htp.formSelectOption(year_data(i),
cAttributes => ' value= ' || year_data(i));
htp.formSelectClose;
htp.p ('
htp.formSelectOption('Creation Dates',
cAttributes => ' value= ' || 'ENTRY');
htp.formSelectOption('Close Dates',
cAttributes => ' value= ' || 'CLOSE',
cSelected => 'TRUE' );
htp.formSelectClose;
htp.p('
htp.formSelectOption(rec_sales_group.name, cAttributes => ' value= ' || rec_sales_group.sgi);
htp.formSelectClose;
htp.p('
htp.formSelectOption(' ');
htp.formSelectOption(cur_rec.win_probability, cAttributes => ' value= ' || cur_rec.win_probability);
htp.formSelectClose;
htp.p('
htp.formSelectOption(' ');
htp.formSelectOption(cur_rec.win_probability, cAttributes => ' value= ' || cur_rec.win_probability);
htp.formSelectClose;
htp.p('
htp.formSelectOption(' ');
htp.formSelectOption(cur_rec.lookup_code, cAttributes => ' value= ' || cur_rec.lookup_code);
htp.formSelectClose;
htp.p('
htp.formSelectOption(rec_currencies.currency_code, cSelected => 'TRUE');
htp.formSelectOption(rec_currencies.currency_code);
htp.formSelectClose;