1 PACKAGE BODY hri_opl_dbi_senior_mgr AS
2 /* $Header: hriodmgr.pkb 120.1 2005/10/10 01:53:43 anmajumd noship $ */
3
4 g_max_period_length NUMBER := 365;
5
6 PROCEDURE load_senior_mgrs IS
7
8 l_senior_mgr_threshold NUMBER;
9 l_sql_stmt VARCHAR2(1000);
10 l_dummy1 VARCHAR2(2000);
11 l_dummy2 VARCHAR2(2000);
12 l_schema VARCHAR2(400);
13
14 BEGIN
15
16 /* Truncate tables */
17 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
18 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CL_PER_SNRMGR_CT';
19 EXECUTE IMMEDIATE(l_sql_stmt);
20 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CL_PER_SNAP_PRDS_CT';
21 EXECUTE IMMEDIATE(l_sql_stmt);
22 END IF;
23
24 /* If snapshots are enabled then reload the tables */
25 IF (fnd_profile.value('HRI_DBI_PER_SNRMGR_SNPSHTS') = 'Y') THEN
26
27 /* Get threshold from profile option */
28 l_senior_mgr_threshold := fnd_profile.value('HRI_DBI_PER_SNRMGR_THRSHLD');
29
30 /* Default the value if the profile option is not populated */
31 IF (l_senior_mgr_threshold IS NULL) THEN
32 l_senior_mgr_threshold := 2500;
33 END IF;
34
35 /* Populate the senior manager list of values */
36 INSERT INTO hri_cl_per_snrmgr_ct
37 (id
38 ,value
39 ,start_date
40 ,end_date
41 ,snapshot_start_date
42 ,snapshot_end_date)
43 SELECT
44 hsal.supervisor_person_id
45 ,hsal.supervisor_person_id
46 ,MIN(effective_start_date)
47 ,MAX(effective_end_date)
48 ,MIN(effective_start_date)
49 ,MAX(effective_end_date)
50 FROM
51 hri_mdp_sup_wcnt_sup_mv hsal
52 WHERE hsal.total_headcount > l_senior_mgr_threshold
53 GROUP BY
54 hsal.supervisor_person_id;
55
56 /* Commit */
57 commit;
58
59 /* Populate the snapshot periods table with the senior managers and */
60 /* their direct reports and the date ranges in which to snapshot */
61 INSERT INTO hri_cl_per_snap_prds_ct
62 (id
63 ,value
64 ,snapshot_start_date
65 ,snapshot_end_date
66 ,senior_manager_flag)
67 SELECT
68 id
69 ,value
70 ,MIN(snapshot_start_date) snapshot_start_date
71 ,MAX(snapshot_end_date) snapshot_end_date
72 ,DECODE(SUM(senior_manager_ind), 1, 'Y', 'N')
73 senior_manager_flag
74 FROM
75 (SELECT
76 tab.person_id id
77 ,to_char(tab.person_id) VALUE
78 ,GREATEST(MIN(sub_mgr.effective_start_date),
79 tab.snapshot_start_date) snapshot_start_date
80 /* Snapshot period should continue for the length of the longest period */
81 /* beyond the subordinate having manager status so that snapshots */
82 /* are available - bug 4300189 */
83 ,LEAST(MAX(sub_mgr.effective_end_date) + g_max_period_length,
84 tab.snapshot_end_date) snapshot_end_date
85 ,0 senior_manager_ind
86 FROM
87 (SELECT /*+ ORDERED USE_NL(sub) */
88 sub.person_id
89 ,GREATEST(MIN(snrmgr.snapshot_start_date),
90 MIN(sub.effective_change_date))
91 snapshot_start_date
92 ,LEAST(MAX(snrmgr.snapshot_end_date),
93 MAX(sub.effective_change_end_date))
94 snapshot_end_date
95 FROM
96 hri_cl_per_snrmgr_ct snrmgr
97 ,hri_mb_asgn_events_ct sub
98 WHERE sub.supervisor_id = snrmgr.id
99 /* Non-terminated primary subordinates */
100 AND sub.worker_term_ind = 0
101 AND sub.primary_flag = 'Y'
102 /* Slicing date join */
103 AND (sub.effective_change_date BETWEEN snrmgr.snapshot_start_date
104 AND snrmgr.snapshot_end_date
105 OR snrmgr.snapshot_start_date BETWEEN sub.effective_change_date
106 AND sub.effective_change_end_date)
107 GROUP BY
108 sub.person_id
109 ) tab
110 ,hri_cl_wkr_sup_status_ct sub_mgr
111 WHERE tab.person_id = sub_mgr.person_id
112 /* Who are supervisors during the snapshot period or in the year preceding */
113 AND sub_mgr.supervisor_flag = 'Y'
114 /* Slicing Date Join including rolling year (365 days) preceding */
115 AND (tab.snapshot_start_date - g_max_period_length
116 BETWEEN sub_mgr.effective_start_date
117 AND sub_mgr.effective_end_date
118 OR sub_mgr.effective_start_date
119 BETWEEN tab.snapshot_start_date - g_max_period_length
120 AND tab.snapshot_end_date)
121 GROUP BY
122 tab.person_id
123 ,tab.snapshot_start_date
124 ,tab.snapshot_end_date
125 UNION ALL
126 SELECT
127 id
128 ,VALUE
129 ,snapshot_start_date
130 ,snapshot_end_date
131 ,1
132 FROM hri_cl_per_snrmgr_ct
133 )
134 GROUP BY
135 id
136 ,value;
137
138 /* Commit */
139 commit;
140
141 END IF;
142
143 END load_senior_mgrs;
144
145 PROCEDURE load_senior_mgrs(errbuf OUT NOCOPY VARCHAR2,
146 retcode OUT NOCOPY VARCHAR2) IS
147
148 BEGIN
149
150 load_senior_mgrs;
151
152 END load_senior_mgrs;
153
154 END hri_opl_dbi_senior_mgr;
[Home] [Help]