eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.RCI_ORG_CERT_SUMM_PKG dependencies on FII_TIME_DAY

Line 32: ,fii_time_day ftd

28: bug 4880422 fix ***/
29: l_getcnt := 'select count(1) from (
30: select distinct rocsf.fin_certification_id, organization_id
31: from rci_org_cert_summ_f rocsf
32: ,fii_time_day ftd
33: where 1=1 and rocsf.report_date_julian = ftd.report_date_julian ';
34:
35: ---looping through the parameters
36: FOR i in 1..p_page_parameter_tbl.COUNT LOOP

Line 110: from rci_org_cert_summ_f rocsf, fii_time_day ftd

106: from ';
107:
108: l_qry1 := '(select count(1) as certified
109: from (select distinct organization_id,fin_certification_id
110: from rci_org_cert_summ_f rocsf, fii_time_day ftd
111: where rocsf.report_date_julian = ftd.report_date_julian
112: and rocsf.org_certification_status=''EFFECTIVE'''||l_from_clause||')) a3, ';
113:
114: l_qry2 := '(select count(1) as certified_w_issues

Line 116: from rci_org_cert_summ_f rocsf, fii_time_day ftd

112: and rocsf.org_certification_status=''EFFECTIVE'''||l_from_clause||')) a3, ';
113:
114: l_qry2 := '(select count(1) as certified_w_issues
115: from (select distinct organization_id,fin_certification_id
116: from rci_org_cert_summ_f rocsf, fii_time_day ftd
117: where rocsf.report_date_julian = ftd.report_date_julian
118: and org_certification_status <> ''EFFECTIVE'''||l_from_clause||')) a2, ';
119:
120: l_qry3 := '(select count(1) as not_certified

Line 122: from rci_org_cert_summ_f rocsf, fii_time_day ftd

118: and org_certification_status <> ''EFFECTIVE'''||l_from_clause||')) a2, ';
119:
120: l_qry3 := '(select count(1) as not_certified
121: from (select distinct organization_id,fin_certification_id
122: from rci_org_cert_summ_f rocsf, fii_time_day ftd
123: where rocsf.report_date_julian = ftd.report_date_julian
124: and org_certification_status is null '||l_from_clause||')) a1';
125:
126: p_exp_source_sql := l_sqlstmt || l_qry1 || l_qry2 || l_qry3;

Line 230: ,fii_time_day ftd

226: ,rocsf.organization_id org_id
227: ,rocsf.report_date_julian
228: from rci_org_cert_summ_f rocsf
229: ,amw_audit_units_v aauv
230: ,fii_time_day ftd
231: where rocsf.organization_id = aauv.organization_id
232: and rocsf.report_date_julian = ftd.report_date_julian ';
233:
234:

Line 386: from rci_org_cert_summ_f rocsf,fii_time_day ftd

382: l_inner_from := l_inner_from || ') rocsf, ';
383:
384: l_org_statuses := ' (select organization_id,count(fin_certification_id) as org_certified_with_issues from (
385: select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
386: from rci_org_cert_summ_f rocsf,fii_time_day ftd
387: where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status=''INEFFECTIVE'' '||l_org_status_where1||' ) group by organization_id) ro1,
388: (select organization_id,count(fin_certification_id) as org_certified from (
389: select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
390: from rci_org_cert_summ_f rocsf,fii_time_day ftd

Line 390: from rci_org_cert_summ_f rocsf,fii_time_day ftd

386: from rci_org_cert_summ_f rocsf,fii_time_day ftd
387: where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status=''INEFFECTIVE'' '||l_org_status_where1||' ) group by organization_id) ro1,
388: (select organization_id,count(fin_certification_id) as org_certified from (
389: select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
390: from rci_org_cert_summ_f rocsf,fii_time_day ftd
391: where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status=''EFFECTIVE'' '||l_org_status_where2||' ) group by organization_id) ro2,
392: (select organization_id,count(fin_certification_id) as org_not_certified from (
393: select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
394: from rci_org_cert_summ_f rocsf,fii_time_day ftd

Line 394: from rci_org_cert_summ_f rocsf,fii_time_day ftd

390: from rci_org_cert_summ_f rocsf,fii_time_day ftd
391: where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status=''EFFECTIVE'' '||l_org_status_where2||' ) group by organization_id) ro2,
392: (select organization_id,count(fin_certification_id) as org_not_certified from (
393: select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
394: from rci_org_cert_summ_f rocsf,fii_time_day ftd
395: where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status is null '||l_org_status_where3||' ) group by organization_id) ro3,';
396:
397: l_issues_sql := l_issues_sql ||' group by roif.organization_id) op, ';
398: l_def_proc_sql := '(select organization_id,count(process_id) as processes from (

Line 400: from rci_org_proc_dfcy_f ropdf, fii_time_day ftd

396:
397: l_issues_sql := l_issues_sql ||' group by roif.organization_id) op, ';
398: l_def_proc_sql := '(select organization_id,count(process_id) as processes from (
399: select distinct process_id,organization_id,fin_certification_id,certification_result_code,evaluation_result_code
400: from rci_org_proc_dfcy_f ropdf, fii_time_day ftd
401: where ropdf.report_date_julian=ftd.report_date_julian
402: '||l_def_proc_where||' ) group by organization_id) def, ';
403: l_ctrls_sql := '(select organization_id,count(distinct control_id) as controls
404: from RCI_ORG_CERT_CTRLS_F roccf,fii_time_day ftd where 1=1 and roccf.report_date_julian = ftd.report_date_julian '||l_ctrls_from||'

Line 404: from RCI_ORG_CERT_CTRLS_F roccf,fii_time_day ftd where 1=1 and roccf.report_date_julian = ftd.report_date_julian '||l_ctrls_from||'

400: from rci_org_proc_dfcy_f ropdf, fii_time_day ftd
401: where ropdf.report_date_julian=ftd.report_date_julian
402: '||l_def_proc_where||' ) group by organization_id) def, ';
403: l_ctrls_sql := '(select organization_id,count(distinct control_id) as controls
404: from RCI_ORG_CERT_CTRLS_F roccf,fii_time_day ftd where 1=1 and roccf.report_date_julian = ftd.report_date_julian '||l_ctrls_from||'
405: group by organization_id) ctrls, ';
406: l_risks_sql := '(select organization_id,count(risk_id) as risk_id from (select distinct organization_id,process_id,risk_id
407: from RCI_ORG_CERT_RISKS_F roccf,fii_time_day ftd
408: where roccf.report_date_julian = ftd.report_date_julian and audit_result_code <> ''EFFECTIVE'' and audit_result_code is not null

Line 407: from RCI_ORG_CERT_RISKS_F roccf,fii_time_day ftd

403: l_ctrls_sql := '(select organization_id,count(distinct control_id) as controls
404: from RCI_ORG_CERT_CTRLS_F roccf,fii_time_day ftd where 1=1 and roccf.report_date_julian = ftd.report_date_julian '||l_ctrls_from||'
405: group by organization_id) ctrls, ';
406: l_risks_sql := '(select organization_id,count(risk_id) as risk_id from (select distinct organization_id,process_id,risk_id
407: from RCI_ORG_CERT_RISKS_F roccf,fii_time_day ftd
408: where roccf.report_date_julian = ftd.report_date_julian and audit_result_code <> ''EFFECTIVE'' and audit_result_code is not null
409: '||l_risks_from||' )
410: group by organization_id) risk
411: where rocsf.organization_id = op.organization_id(+)

Line 528: ,fii_time_day ftd

524: select distinct nvl(rocsf.fin_certification_id,0) certification_id,
525: nvl(rocsf.organization_id,0) organization_id,
526: nvl(rocsf.org_certification_status,''NOT_CERTIFIED'') org_certification_status
527: from rci_org_cert_summ_f rocsf
528: ,fii_time_day ftd
529: where 1=1 and rocsf.report_date_julian = ftd.report_date_julian ';
530:
531: l_sqlstmt := 'select rbcrv.value VIEWBY
532: ,count(rocsf.organization_id) RCI_ORG_CERT_MEASURE1 ';

Line 540: ,fii_time_day ftd

536: nvl(rocsf.organization_id,0) organization_id,
537: /**nvl(rocsf.org_certification_status,''NOT_CERTIFIED'') org_certification_status**/
538: decode(rocsf.org_certification_status,null,''NOT_CERTIFIED'',''EFFECTIVE'',''EFFECTIVE'',''INEFFECTIVE'') org_certification_status
539: from rci_org_cert_summ_f rocsf
540: ,fii_time_day ftd
541: where 1=1 and rocsf.report_date_julian = ftd.report_date_julian ';
542:
543:
544: FOR i in 1..p_page_parameter_tbl.COUNT LOOP

Line 719: ,fii_time_day ftd

715: ,RCI_FS_ACCT_EVAL_V rfaev
716: ,PER_ALL_PEOPLE_F papf
717: ,FND_USER fu
718: /** 10.20.2005 npanandi begin ***/
719: ,fii_time_day ftd
720: /** 10.20.2005 npanandi end ***/
721: /**01.31.2006 npanandi: changing reference to fnd_lookups below because
722: of lang issues **/
723: ,/*fnd_lookup_values*/fnd_lookups flv

Line 925: ,fii_time_day ftd

921: ,RCI_FS_ACCT_EVAL_V rfaev
922: ,PER_ALL_PEOPLE_F papf
923: ,FND_USER fu
924: /** 10.20.2005 npanandi begin ***/
925: ,fii_time_day ftd
926: /** 10.20.2005 npanandi end ***/
927: where acb.control_rev_id = act.control_rev_id
928: and upper(acb.curr_approved_flag) = ''Y''
929: and act.language = userenv(''LANG'')

Line 1152: from fii_time_day

1148: p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1149: v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'M');
1150: select min(distinct last_day(to_date(to_char(ent_period_end_date,'YYYYMM'),'YYYYMM')))
1151: into l_end_date /*gives in the form 30-SEP-06*/
1152: from fii_time_day
1153: where ent_period_id=p_page_parameter_tbl(i).parameter_id;
1154: ---l_from_clause := l_from_clause || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
1155: l_from_clause := l_from_clause || ' and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
1156: ) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';

Line 1164: from fii_time_day

1160: p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1161: v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Q');
1162: select min(distinct last_day(to_date(to_char(ent_qtr_end_date,'YYYYMM'),'YYYYMM')))
1163: into l_end_date /*gives in the form 30-SEP-06*/
1164: from fii_time_day
1165: where ent_qtr_id=p_page_parameter_tbl(i).parameter_id;
1166: ---l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
1167: l_from_clause := l_from_clause || ' and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
1168: ) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';

Line 1176: from fii_time_day

1172: p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1173: v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Y');
1174: select min(distinct last_day(to_date(to_char(ent_year_end_date,'YYYYMM'),'YYYYMM')))
1175: into l_end_date /*gives in the form 30-SEP-06*/
1176: from fii_time_day
1177: where ent_year_id=p_page_parameter_tbl(i).parameter_id;
1178:
1179: ---l_from_clause := l_from_clause || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
1180: l_from_clause := l_from_clause || ' and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))

Line 1246: fii_time_day ftd

1242: eng_engineering_changes eec,
1243: amw_employees_current_v aecv,
1244: eng_change_statuses_tl ecst,
1245: eng_change_priorities ecp,
1246: fii_time_day ftd
1247: where /*aauv.organization_id=open_issues.organization_id(+)
1248: and*/ open_issues.change_id=eec.change_id
1249: and aecv.party_id = eec.assignee_id
1250: and eec.status_code = ecst.status_code

Line 1329: ,fii_time_day ftd

1325: ,rci_fs_acct_eval_v rfaev
1326: ,(select full_name,person_id from PER_ALL_PEOPLE_F where employee_number is not null and (trunc(sysdate) between effective_start_date and effective_end_date)) papf2
1327: ,FND_USER fu2
1328: /** 10.20.2005 npanandi begin ***/
1329: ,fii_time_day ftd
1330: /** 10.20.2005 npanandi end ***/
1331: where ropdf.process_id = alrv.process_id
1332: and ropdf.organization_id = aauv.organization_id
1333: and ropdf.significant_process_flag = fl1.lookup_code

Line 1523: ,fii_time_day ftd

1519: and papf.employee_number is not null) papf
1520: ,RCI_BP_ORG_CERT_RESULT_V rbocrv
1521: ,hr_all_organization_units_tl aauv
1522: /** 10.20.2005 npanandi begin ***/
1523: ,fii_time_day ftd
1524: /** 10.20.2005 npanandi end ***/
1525: where rocsf.fin_certification_id = acv.certification_id
1526: and acv.object_type = ''FIN_STMT''
1527: and rocsf.certification_type = al1.lookup_code

Line 1710: ,fii_time_day ftd

1706: ,(select papf.full_name,fu.user_id from PER_ALL_PEOPLE_F papf,fnd_user fu where fu.employee_id = papf.person_id
1707: and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
1708: and papf.employee_number is not null) papf
1709: /** 10.19.2005 npanandi begin ***/
1710: ,fii_time_day ftd
1711: /** 10.19.2005 npanandi end ***/
1712: where rpdf.process_org_rev_id = apov.process_org_rev_id
1713: and rpdf.process_id = apov.process_id
1714: and rpdf.organization_id = apov.organization_id