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.PSP_TEMPLATE_SELECTION dependencies on PSP_SELECTION_CARDINALITY_GT

Line 428: psp_selection_cardinality_gt where total_count > 0 ORDER BY total_count asc;

424:
425: l_sql_string varchar2(1000);
426:
427: CURSOR get_lowest_cardinality_csr is select lookup_code from
428: psp_selection_cardinality_gt where total_count > 0 ORDER BY total_count asc;
429:
430:
431: CURSOR get_zero_cardinality_csr is select lookup_code from
432: psp_selection_cardinality_gt where total_count=0;

Line 432: psp_selection_cardinality_gt where total_count=0;

428: psp_selection_cardinality_gt where total_count > 0 ORDER BY total_count asc;
429:
430:
431: CURSOR get_zero_cardinality_csr is select lookup_code from
432: psp_selection_cardinality_gt where total_count=0;
433:
434:
435: CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER)
436: IS

Line 473: insert into psp_selection_cardinality_gt(lookup_code, total_count) (

469: LOOP
470:
471: IF template_rec.array_sel_criteria(i) = 'PTY' THEN
472:
473: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
474: select 'PTY', count(distinct ppf.person_id) from per_people_f ppf, per_assignments_f paf where
475: person_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
476: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='PTY'
477: and include_exclude_flag='I'

Line 490: insert into psp_selection_cardinality_gt(lookup_code, total_count) (

486:
487:
488: ELSIF template_rec.array_sel_criteria(i) ='EMP' THEN
489:
490: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
491: select 'EMP', count(distinct ppf.person_id) from per_all_people_f ppf, per_assignments_f paf where
492: ppf.person_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
493: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='EMP' and
494: include_exclude_flag='I'

Line 506: insert into psp_selection_cardinality_gt(lookup_code, total_count) (

502: ppf.effective_end_date >= p_effort_start) ;
503:
504: ELSIF template_rec.array_sel_criteria(i) ='SUP' THEN
505:
506: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
507: select 'SUP', count(distinct person_id) from per_all_assignments_f paf where
508: supervisor_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
509: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='SUP'
510: and request_id = p_request_id

Line 521: insert into psp_selection_cardinality_gt(lookup_code, total_count)(

517:
518: ELSIF template_rec.array_sel_criteria(i) ='AWD' THEN
519:
520: --- replaced non-performant insert with this for 4429787
521: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
522: SELECT 'AWD', COUNT(DISTINCT psl.person_id)
523: FROM psp_summary_lines psl,
524: psp_report_template_details_h prtd ,
525: per_time_periods ptp

Line 559: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(

555: ELSIF template_rec.array_sel_criteria(i)='ATY' THEN
556: --- replaced non-performant insert with this for 4429787
557:
558:
559: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
560: SELECT 'ATY', COUNT(DISTINCT psl.person_id)
561: FROM psp_summary_lines psl,
562: psp_report_template_details_h prtd ,
563: gms_awards_all gaa,

Line 596: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(

592: NVL(palh.original_line_flag, 'N') ='N')));
593:
594: ELSIF template_rec.array_sel_criteria(i)='PRT' THEN
595: --- replaced non-performant insert with this for 4429787
596: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
597: SELECT 'PRT', COUNT(DISTINCT psl.person_id)
598: FROM psp_summary_lines psl,
599: psp_report_template_details_h prtd ,
600: pa_projects_all ppa ,

Line 633: insert into psp_selection_cardinality_gt(lookup_code, total_count)(

629: NVL(palh.original_line_flag, 'N') ='N')));
630:
631: ELSIF template_rec.array_sel_criteria(i)='PRJ' THEN
632: --- replaced non-performant insert with this for 4429787
633: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
634: SELECT 'PRJ', COUNT(DISTINCT psl.person_id)
635: FROM psp_summary_lines psl,
636: psp_report_template_details_h prtd ,
637: per_time_periods ptp

Line 669: insert into psp_selection_cardinality_gt(lookup_code, total_count)(

665: NVL(palh.original_line_flag, 'N') ='N')));
666:
667: ELSIF template_rec.array_sel_criteria(i)='PAY' THEN
668:
669: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
670: select 'PAY', count(distinct person_id) from per_assignments_f paf where
671: payroll_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
672: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='PAY'
673: and include_exclude_flag='I' and request_id = p_request_id

Line 682: insert into psp_selection_cardinality_gt(lookup_code, total_count)(

678: effective_end_date >= p_effort_start) ;
679:
680: ELSIF template_rec.array_sel_criteria(i)='LOC' THEN
681:
682: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
683: select 'LOC', count(distinct person_id) from per_assignments_f paf where
684: location_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
685: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='LOC'
686: and include_exclude_flag='I' and request_id = p_request_id

Line 695: insert into psp_selection_cardinality_gt(lookup_code, total_count)(

691: effective_end_date >= p_effort_start) ;
692:
693: ELSIF template_rec.array_sel_criteria(i)='ORG' THEN
694:
695: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
696: select 'ORG', count(distinct person_id) from per_assignments_f paf where
697: organization_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd
698: where
699: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='ORG'

Line 708: insert into psp_selection_cardinality_gt(lookup_code, total_count)(

704: effective_end_date >= p_effort_start);
705:
706: ELSIF template_rec.array_sel_criteria(i)='JOB' THEN
707:
708: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
709: select 'JOB', count(distinct person_id) from per_assignments_f paf where
710: job_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
711: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='JOB'
712: and include_exclude_flag='I' and request_id=p_request_id

Line 721: insert into psp_selection_cardinality_gt(lookup_code, total_count) (

717: effective_end_date >= p_effort_start) ;
718:
719: ELSIF template_rec.array_sel_criteria(i)='POS' THEN
720:
721: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
722: select 'POS', count(distinct person_id) from per_assignments_f paf where
723: position_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
724: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='POS'
725: and include_exclude_flag='I' and request_id = p_request_id

Line 734: insert into psp_selection_cardinality_gt(lookup_code, total_count) (

730: effective_end_date >= p_effort_start) ;
731:
732: ELSIF template_rec.array_sel_criteria(i)='ASS' THEN
733:
734: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
735: select 'ASS', count(distinct person_id) from per_assignments_f paf where
736: assignment_status_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h
737: prtd where
738: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='ASS'

Line 748: insert into psp_selection_cardinality_gt(lookup_code, total_count) (

744: effective_end_date >= p_effort_start) ;
745:
746: ELSIF template_rec.array_sel_criteria(i)='CST' THEN
747:
748: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
749: select 'CST', count(distinct paf.person_id) from per_assignments_f paf
750: , pay_payrolls_f ppf where
751: ppf.payroll_id = paf.payroll_id and
752: ppf.consolidation_set_id in

Line 768: insert into psp_selection_cardinality_gt(lookup_code, total_count) (

764:
765:
766: ELSIF template_rec.array_sel_criteria(i) = 'AST' THEN
767:
768: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
769: select 'AST', count(distinct paf.person_id) from per_all_assignments_f paf, hr_assignment_sets has ,
770: hr_assignment_Set_amendments hasa
771: where
772: has.assignment_set_id in (

Line 905: g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)

901:
902: -- l_sql_string:= g_exec_string;
903:
904: IF l_sql_string is not null then
905: g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)
906: (select '|| '''' || 'PPG' || ''''||' , count(person_id)
907: from per_assignments_f paf,
908: pay_people_groups ppg
909: where paf.people_group_id= ppg.people_group_id

Line 963: g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)

959: -- l_sql_string:= g_exec_string;
960:
961: -- fnd_file.put_line(fnd_file.log,' before isnerting g_exec_string in GL criteria ');
962: if l_sql_string is not null then
963: g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)
964: ( select '|| '''' || 'GLA' || ''''|| ' , count( distinct psl.person_id)
965: from psp_summary_lines psl, psp_distribution_lines_history pdnh, psp_pre_gen_dist_lines_history ppg,
966: psp_adjustment_lines_history palh
967: , gl_code_combinations gcc

Line 3619: psp_selection_cardinality_gt WHERE total_count > 0 ORDER BY total_count asc;

3615: l_sql_string VARCHAR2(1000);
3616:
3617: CURSOR get_lowest_cardinality_csr IS
3618: SELECT lookup_code FROM
3619: psp_selection_cardinality_gt WHERE total_count > 0 ORDER BY total_count asc;
3620:
3621:
3622: CURSOR get_zero_cardinality_csr IS
3623: SELECT lookup_code

Line 3624: FROM psp_selection_cardinality_gt

3620:
3621:
3622: CURSOR get_zero_cardinality_csr IS
3623: SELECT lookup_code
3624: FROM psp_selection_cardinality_gt
3625: WHERE total_count=0;
3626:
3627:
3628: CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER) IS

Line 3663: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)

3659:
3660: FOR i IN 1.. template_rec.array_sel_criteria.COUNT
3661: LOOP
3662: IF template_rec.array_sel_criteria(i) = 'PTY' THEN
3663: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
3664: SELECT 'PTY', COUNT(DISTINCT assignment_id)
3665: FROM per_people_f ppf,
3666: per_assignments_f paf
3667: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3680: INSERT INTO psp_selection_cardinality_gt (lookup_code, total_count)

3676: AND ppf.effective_end_date >= p_effort_start
3677: AND paf.effective_start_date <= p_effort_end
3678: AND paf.effective_end_date >= p_effort_start;
3679: ELSIF template_rec.array_sel_criteria(i) ='EMP' THEN
3680: INSERT INTO psp_selection_cardinality_gt (lookup_code, total_count)
3681: SELECT 'EMP', COUNT(DISTINCT paf.assignment_id)
3682: FROM per_all_assignments_f paf
3683: WHERE paf.person_id IN (SELECT TO_NUMBER(criteria_value1)
3684: FROM psp_report_template_details_h prtd

Line 3693: INSERT INTO psp_selection_cardinality_gt

3689: AND paf.assignment_type = 'E'
3690: AND paf.effective_start_date <= p_effort_end
3691: AND paf.effective_end_date >= p_effort_start;
3692: ELSIF template_rec.array_sel_criteria(i) ='SUP' THEN
3693: INSERT INTO psp_selection_cardinality_gt
3694: (lookup_code, total_count)
3695: SELECT 'SUP', COUNT(DISTINCT assignment_id)
3696: FROM per_all_assignments_f paf
3697: WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3707: INSERT INTO psp_selection_cardinality_gt

3703: AND paf.assignment_type = 'E'
3704: AND effective_start_date <= p_effort_end
3705: AND effective_end_date >= p_effort_start;
3706: ELSIF template_rec.array_sel_criteria(i) ='AWD' THEN
3707: INSERT INTO psp_selection_cardinality_gt
3708: (lookup_code, total_count)
3709: SELECT 'AWD', COUNT(DISTINCT psl.assignment_id)
3710: FROM psp_summary_lines psl,
3711: psp_report_template_details_h prtd ,

Line 3745: INSERT INTO psp_selection_cardinality_gt

3741: AND palh.adjustment_batch_name IS NULL
3742: AND palh.reversal_entry_flag IS NULL
3743: AND NVL(palh.original_line_flag, 'N') ='N'));
3744: ELSIF template_rec.array_sel_criteria(i)='ATY' THEN
3745: INSERT INTO psp_selection_cardinality_gt
3746: (lookup_code, total_count)
3747: SELECT 'ATY', COUNT(DISTINCT psl.assignment_id)
3748: FROM psp_summary_lines psl,
3749: psp_report_template_details_h prtd ,

Line 3784: INSERT INTO psp_selection_cardinality_gt

3780: AND palh.adjustment_batch_name IS NULL
3781: AND palh.reversal_entry_flag IS NULL
3782: AND NVL(palh.original_line_flag, 'N') ='N'));
3783: ELSIF template_rec.array_sel_criteria(i)='PRT' THEN
3784: INSERT INTO psp_selection_cardinality_gt
3785: (lookup_code, total_count)
3786: SELECT 'PRT', COUNT(DISTINCT psl.assignment_id)
3787: FROM psp_summary_lines psl,
3788: psp_report_template_details_h prtd ,

Line 3823: INSERT INTO psp_selection_cardinality_gt

3819: AND palh.adjustment_batch_name IS NULL
3820: AND palh.reversal_entry_flag IS NULL AND
3821: NVL(palh.original_line_flag, 'N') ='N'));
3822: ELSIF template_rec.array_sel_criteria(i)='PRJ' THEN
3823: INSERT INTO psp_selection_cardinality_gt
3824: (lookup_code, total_count)
3825: SELECT 'PRJ', COUNT(DISTINCT psl.assignment_id)
3826: FROM psp_summary_lines psl,
3827: psp_report_template_details_h prtd ,

Line 3858: INSERT INTO psp_selection_cardinality_gt

3854: AND palh.adjustment_batch_name IS NULL
3855: AND palh.reversal_entry_flag IS NULL
3856: AND NVL(palh.original_line_flag, 'N') ='N'));
3857: ELSIF template_rec.array_sel_criteria(i)='PAY' THEN
3858: INSERT INTO psp_selection_cardinality_gt
3859: (lookup_code, total_count)
3860: SELECT 'PAY', COUNT(DISTINCT assignment_id)
3861: FROM per_assignments_f paf
3862: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3871: INSERT INTO psp_selection_cardinality_gt

3867: AND paf.assignment_type = 'E'
3868: AND effective_start_date <= p_effort_end
3869: AND effective_end_date >= p_effort_start;
3870: ELSIF template_rec.array_sel_criteria(i)='LOC' THEN
3871: INSERT INTO psp_selection_cardinality_gt
3872: (lookup_code, total_count)
3873: SELECT 'LOC', COUNT(DISTINCT assignment_id)
3874: FROM per_assignments_f paf
3875: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3885: INSERT INTO psp_selection_cardinality_gt

3881: AND paf.assignment_type = 'E'
3882: AND effective_start_date <= p_effort_end
3883: AND effective_end_date >= p_effort_start;
3884: ELSIF template_rec.array_sel_criteria(i)='ORG' THEN
3885: INSERT INTO psp_selection_cardinality_gt
3886: (lookup_code, total_count)
3887: SELECT 'ORG', COUNT(DISTINCT assignment_id)
3888: FROM per_assignments_f paf
3889: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3899: INSERT INTO psp_selection_cardinality_gt

3895: AND paf.assignment_type = 'E'
3896: AND effective_start_date <= p_effort_end
3897: AND effective_end_date >= p_effort_start;
3898: ELSIF template_rec.array_sel_criteria(i)='JOB' THEN
3899: INSERT INTO psp_selection_cardinality_gt
3900: (lookup_code, total_count)
3901: SELECT 'JOB', COUNT(DISTINCT assignment_id)
3902: FROM per_assignments_f paf
3903: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3913: INSERT INTO psp_selection_cardinality_gt

3909: AND paf.assignment_type = 'E'
3910: AND effective_start_date <= p_effort_end
3911: AND effective_end_date >= p_effort_start;
3912: ELSIF template_rec.array_sel_criteria(i)='POS' THEN
3913: INSERT INTO psp_selection_cardinality_gt
3914: (lookup_code, total_count)
3915: SELECT 'POS', COUNT(DISTINCT assignment_id)
3916: FROM per_assignments_f paf
3917: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3927: INSERT INTO psp_selection_cardinality_gt

3923: AND paf.assignment_type = 'E'
3924: AND effective_start_date <= p_effort_end
3925: AND effective_end_date >= p_effort_start;
3926: ELSIF template_rec.array_sel_criteria(i)='ASS' THEN
3927: INSERT INTO psp_selection_cardinality_gt
3928: (lookup_code, total_count)
3929: SELECT 'ASS', COUNT(DISTINCT assignment_id)
3930: FROM per_assignments_f paf
3931: WHERE assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3941: INSERT INTO psp_selection_cardinality_gt

3937: AND paf.assignment_type = 'E'
3938: AND effective_start_date <= p_effort_end
3939: AND effective_end_date >= p_effort_start;
3940: ELSIF template_rec.array_sel_criteria(i)='CST' THEN
3941: INSERT INTO psp_selection_cardinality_gt
3942: (lookup_code, total_count)
3943: SELECT 'CST', COUNT(DISTINCT paf.assignment_id)
3944: FROM per_assignments_f paf,
3945: pay_payrolls_f ppf

Line 3959: INSERT INTO psp_selection_cardinality_gt

3955: AND ppf.effective_end_date >= p_effort_start
3956: AND paf.effective_start_date <= p_effort_end
3957: AND paf.effective_end_date >= p_effort_start;
3958: ELSIF template_rec.array_sel_criteria(i) = 'AST' THEN
3959: INSERT INTO psp_selection_cardinality_gt
3960: (lookup_code, total_count)
3961: SELECT 'AST', COUNT(DISTINCT paf.assignment_id)
3962: FROM per_all_assignments_f paf,
3963: hr_assignment_sets has ,

Line 4040: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)

4036: END LOOP;
4037: CLOSE ppg_cursor;
4038:
4039: IF l_sql_string IS NOT NULL THEN
4040: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4041: SELECT '|| '''' || 'PPG' || ''''||' , COUNT(person_id)
4042: FROM per_assignments_f paf,
4043: pay_people_groups ppg
4044: WHERE paf.people_group_id= ppg.people_group_id

Line 4078: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)

4074: END LOOP;
4075: CLOSE gla_cursor;
4076:
4077: IF l_sql_string IS NOT NULL THEN
4078: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4079: SELECT '|| '''' || 'GLA' || ''''|| ' , COUNT( DISTINCT psl.assignment_id)
4080: FROM psp_summary_lines psl,
4081: psp_distribution_lines_history pdnh,
4082: psp_pre_gen_dist_lines_history ppg,