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 3586: psp_selection_cardinality_gt WHERE total_count > 0 ORDER BY total_count asc;

3582: l_sql_string VARCHAR2(1000);
3583:
3584: CURSOR get_lowest_cardinality_csr IS
3585: SELECT lookup_code FROM
3586: psp_selection_cardinality_gt WHERE total_count > 0 ORDER BY total_count asc;
3587:
3588:
3589: CURSOR get_zero_cardinality_csr IS
3590: SELECT lookup_code

Line 3591: FROM psp_selection_cardinality_gt

3587:
3588:
3589: CURSOR get_zero_cardinality_csr IS
3590: SELECT lookup_code
3591: FROM psp_selection_cardinality_gt
3592: WHERE total_count=0;
3593:
3594:
3595: CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER) IS

Line 3630: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)

3626:
3627: FOR i IN 1.. template_rec.array_sel_criteria.COUNT
3628: LOOP
3629: IF template_rec.array_sel_criteria(i) = 'PTY' THEN
3630: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
3631: SELECT 'PTY', COUNT(DISTINCT assignment_id)
3632: FROM per_people_f ppf,
3633: per_assignments_f paf
3634: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3647: INSERT INTO psp_selection_cardinality_gt (lookup_code, total_count)

3643: AND ppf.effective_end_date >= p_effort_start
3644: AND paf.effective_start_date <= p_effort_end
3645: AND paf.effective_end_date >= p_effort_start;
3646: ELSIF template_rec.array_sel_criteria(i) ='EMP' THEN
3647: INSERT INTO psp_selection_cardinality_gt (lookup_code, total_count)
3648: SELECT 'EMP', COUNT(DISTINCT paf.assignment_id)
3649: FROM per_all_assignments_f paf
3650: WHERE paf.person_id IN (SELECT TO_NUMBER(criteria_value1)
3651: FROM psp_report_template_details_h prtd

Line 3660: INSERT INTO psp_selection_cardinality_gt

3656: AND paf.assignment_type = 'E'
3657: AND paf.effective_start_date <= p_effort_end
3658: AND paf.effective_end_date >= p_effort_start;
3659: ELSIF template_rec.array_sel_criteria(i) ='SUP' THEN
3660: INSERT INTO psp_selection_cardinality_gt
3661: (lookup_code, total_count)
3662: SELECT 'SUP', COUNT(DISTINCT assignment_id)
3663: FROM per_all_assignments_f paf
3664: WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3674: INSERT INTO psp_selection_cardinality_gt

3670: AND paf.assignment_type = 'E'
3671: AND effective_start_date <= p_effort_end
3672: AND effective_end_date >= p_effort_start;
3673: ELSIF template_rec.array_sel_criteria(i) ='AWD' THEN
3674: INSERT INTO psp_selection_cardinality_gt
3675: (lookup_code, total_count)
3676: SELECT 'AWD', COUNT(DISTINCT psl.assignment_id)
3677: FROM psp_summary_lines psl,
3678: psp_report_template_details_h prtd ,

Line 3712: INSERT INTO psp_selection_cardinality_gt

3708: AND palh.adjustment_batch_name IS NULL
3709: AND palh.reversal_entry_flag IS NULL
3710: AND NVL(palh.original_line_flag, 'N') ='N'));
3711: ELSIF template_rec.array_sel_criteria(i)='ATY' THEN
3712: INSERT INTO psp_selection_cardinality_gt
3713: (lookup_code, total_count)
3714: SELECT 'ATY', COUNT(DISTINCT psl.assignment_id)
3715: FROM psp_summary_lines psl,
3716: psp_report_template_details_h prtd ,

Line 3751: INSERT INTO psp_selection_cardinality_gt

3747: AND palh.adjustment_batch_name IS NULL
3748: AND palh.reversal_entry_flag IS NULL
3749: AND NVL(palh.original_line_flag, 'N') ='N'));
3750: ELSIF template_rec.array_sel_criteria(i)='PRT' THEN
3751: INSERT INTO psp_selection_cardinality_gt
3752: (lookup_code, total_count)
3753: SELECT 'PRT', COUNT(DISTINCT psl.assignment_id)
3754: FROM psp_summary_lines psl,
3755: psp_report_template_details_h prtd ,

Line 3790: INSERT INTO psp_selection_cardinality_gt

3786: AND palh.adjustment_batch_name IS NULL
3787: AND palh.reversal_entry_flag IS NULL AND
3788: NVL(palh.original_line_flag, 'N') ='N'));
3789: ELSIF template_rec.array_sel_criteria(i)='PRJ' THEN
3790: INSERT INTO psp_selection_cardinality_gt
3791: (lookup_code, total_count)
3792: SELECT 'PRJ', COUNT(DISTINCT psl.assignment_id)
3793: FROM psp_summary_lines psl,
3794: psp_report_template_details_h prtd ,

Line 3825: INSERT INTO psp_selection_cardinality_gt

3821: AND palh.adjustment_batch_name IS NULL
3822: AND palh.reversal_entry_flag IS NULL
3823: AND NVL(palh.original_line_flag, 'N') ='N'));
3824: ELSIF template_rec.array_sel_criteria(i)='PAY' THEN
3825: INSERT INTO psp_selection_cardinality_gt
3826: (lookup_code, total_count)
3827: SELECT 'PAY', COUNT(DISTINCT assignment_id)
3828: FROM per_assignments_f paf
3829: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3838: INSERT INTO psp_selection_cardinality_gt

3834: AND paf.assignment_type = 'E'
3835: AND effective_start_date <= p_effort_end
3836: AND effective_end_date >= p_effort_start;
3837: ELSIF template_rec.array_sel_criteria(i)='LOC' THEN
3838: INSERT INTO psp_selection_cardinality_gt
3839: (lookup_code, total_count)
3840: SELECT 'LOC', COUNT(DISTINCT assignment_id)
3841: FROM per_assignments_f paf
3842: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3852: INSERT INTO psp_selection_cardinality_gt

3848: AND paf.assignment_type = 'E'
3849: AND effective_start_date <= p_effort_end
3850: AND effective_end_date >= p_effort_start;
3851: ELSIF template_rec.array_sel_criteria(i)='ORG' THEN
3852: INSERT INTO psp_selection_cardinality_gt
3853: (lookup_code, total_count)
3854: SELECT 'ORG', COUNT(DISTINCT assignment_id)
3855: FROM per_assignments_f paf
3856: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3866: INSERT INTO psp_selection_cardinality_gt

3862: AND paf.assignment_type = 'E'
3863: AND effective_start_date <= p_effort_end
3864: AND effective_end_date >= p_effort_start;
3865: ELSIF template_rec.array_sel_criteria(i)='JOB' THEN
3866: INSERT INTO psp_selection_cardinality_gt
3867: (lookup_code, total_count)
3868: SELECT 'JOB', COUNT(DISTINCT assignment_id)
3869: FROM per_assignments_f paf
3870: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3880: INSERT INTO psp_selection_cardinality_gt

3876: AND paf.assignment_type = 'E'
3877: AND effective_start_date <= p_effort_end
3878: AND effective_end_date >= p_effort_start;
3879: ELSIF template_rec.array_sel_criteria(i)='POS' THEN
3880: INSERT INTO psp_selection_cardinality_gt
3881: (lookup_code, total_count)
3882: SELECT 'POS', COUNT(DISTINCT assignment_id)
3883: FROM per_assignments_f paf
3884: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3894: INSERT INTO psp_selection_cardinality_gt

3890: AND paf.assignment_type = 'E'
3891: AND effective_start_date <= p_effort_end
3892: AND effective_end_date >= p_effort_start;
3893: ELSIF template_rec.array_sel_criteria(i)='ASS' THEN
3894: INSERT INTO psp_selection_cardinality_gt
3895: (lookup_code, total_count)
3896: SELECT 'ASS', COUNT(DISTINCT assignment_id)
3897: FROM per_assignments_f paf
3898: WHERE assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3908: INSERT INTO psp_selection_cardinality_gt

3904: AND paf.assignment_type = 'E'
3905: AND effective_start_date <= p_effort_end
3906: AND effective_end_date >= p_effort_start;
3907: ELSIF template_rec.array_sel_criteria(i)='CST' THEN
3908: INSERT INTO psp_selection_cardinality_gt
3909: (lookup_code, total_count)
3910: SELECT 'CST', COUNT(DISTINCT paf.assignment_id)
3911: FROM per_assignments_f paf,
3912: pay_payrolls_f ppf

Line 3926: INSERT INTO psp_selection_cardinality_gt

3922: AND ppf.effective_end_date >= p_effort_start
3923: AND paf.effective_start_date <= p_effort_end
3924: AND paf.effective_end_date >= p_effort_start;
3925: ELSIF template_rec.array_sel_criteria(i) = 'AST' THEN
3926: INSERT INTO psp_selection_cardinality_gt
3927: (lookup_code, total_count)
3928: SELECT 'AST', COUNT(DISTINCT paf.assignment_id)
3929: FROM per_all_assignments_f paf,
3930: hr_assignment_sets has ,

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

4003: END LOOP;
4004: CLOSE ppg_cursor;
4005:
4006: IF l_sql_string IS NOT NULL THEN
4007: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4008: SELECT '|| '''' || 'PPG' || ''''||' , COUNT(person_id)
4009: FROM per_assignments_f paf,
4010: pay_people_groups ppg
4011: WHERE paf.people_group_id= ppg.people_group_id

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

4041: END LOOP;
4042: CLOSE gla_cursor;
4043:
4044: IF l_sql_string IS NOT NULL THEN
4045: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4046: SELECT '|| '''' || 'GLA' || ''''|| ' , COUNT( DISTINCT psl.assignment_id)
4047: FROM psp_summary_lines psl,
4048: psp_distribution_lines_history pdnh,
4049: psp_pre_gen_dist_lines_history ppg,