[Home] [Help]
PACKAGE BODY: APPS.PJI_PMV_DFLT_PARAMS_PVT
Source
1 PACKAGE BODY PJI_PMV_DFLT_PARAMS_PVT AS
2 /* $Header: PJIRX03B.pls 120.6 2007/11/16 15:25:39 vvjoshi ship $ */
3
4 G_Period_Type_Code VARCHAR2(30); -- PJI_SYSTEM_PARAMETERS.DFLT_PRJPIP_PERIOD_TYPE%TYPE;
5 G_Period_Type_ID VARCHAR2(30); -- PJI_SYSTEM_PARAMETERS.DFLT_PRJPIP_PERIOD_TYPE%TYPE;
6 G_Cycle_ID_Code NUMBER (15); -- PJI_SYSTEM_PARAMETERS.DFLT_PRJPIP_CYCLE_ID%TYPE;
7 G_Cycle_ID_Value NUMBER (15); -- PJI_SYSTEM_PARAMETERS.DFLT_PRJPIP_CYCLE_ID%TYPE;
8 G_Currency_ID VARCHAR (30):= 'FII_GLOBAL1'; --
9 G_Org_Version_Id NUMBER (15); -- PJI_SYSTEM_PARAMETERS.ORG_STRUCTURE_VERSION_ID;
10 G_Org_Structure_Id NUMBER (15); -- PJI_SYSTEM_PARAMETERS.ORGANIZATION_STRUCTURE_ID;
11 G_Org_ID VARCHAR2(30);
12 -- G_Exception_Msg VARCHAR2(2000);
13 G_Avail_Threshold VARCHAR2(30);
14 G_EntPeriod_ID VARCHAR2(30);
15 G_EntWeek_ID VARCHAR2(30);
16
17 -- Variable to extract ID for the sysdate from PJI_SYSTEM_SETTTINGS
18
19 G_Period_Id_Value VARCHAR2(30);
20
21 -- Variables for drrive any valid ID from FII_TIME_DAY table
22
23 G_Month_ID_Value NUMBER;
24 G_Ent_Period_ID_Value NUMBER;
25 G_Ent_Quarter_ID_Value NUMBER;
26 G_Ent_Year_ID_Value NUMBER;
27 G_Week_ID_Value NUMBER;
28
29 TYPE InitParams IS RECORD (
30
31 ORGANIZATION_STRUCTURE_ID NUMBER(15)
32 ,ORG_STRUCTURE_VERSION_ID NUMBER(15)
33 ,DFLT_PRJPIP_PERIOD_TYPE VARCHAR2(30)
34 ,DFLT_PRJPIP_CYCLE_ID NUMBER(15)
35 ,DFLT_PRJBAB_PERIOD_TYPE VARCHAR2(30)
36 ,DFLT_PRJBAB_CYCLE_ID NUMBER(15)
37 ,DFLT_RESUTL_PERIOD_TYPE VARCHAR2(30)
38 ,DFLT_RESUTL_CYCLE_ID NUMBER(15)
39 ,DFLT_RESAVL_PERIOD_TYPE VARCHAR2(30)
40 ,DFLT_RESAVL_CYCLE_ID NUMBER(15)
41 ,DFLT_RESPLN_PERIOD_TYPE VARCHAR2(30)
42 ,DFLT_RESPLN_CYCLE_ID NUMBER(15)
43 ,DFLT_PRJHLT_PERIOD_TYPE VARCHAR2(30)
44 ,DFLT_PRJHLT_CYCLE_ID NUMBER(15)
45 ,DFLT_PRJACT_PERIOD_TYPE VARCHAR2(30)
46 ,DFLT_PRJACT_CYCLE_ID NUMBER(15)
47 ,DFLT_PRJPRF_PERIOD_TYPE VARCHAR2(30)
48 ,DFLT_PRJPRF_CYCLE_ID NUMBER(15)
49 ,DFLT_PRJCST_PERIOD_TYPE VARCHAR2(30)
50 ,DFLT_PRJCST_CYCLE_ID NUMBER(15));
51
52 InitialValues InitParams;
53
54
55 -- *************************************************************************
56 -- API called to read the initialization parameters from PJI_SYSTEM_SETINGS
57 -- *************************************************************************
58
59 PROCEDURE InitEnvironment(p_Calling_Context VARCHAR2 DEFAULT NULL) IS
60 BEGIN
61 SELECT ORGANIZATION_STRUCTURE_ID,
62 ORG_STRUCTURE_VERSION_ID,
63 DFLT_PRJPIP_PERIOD_TYPE,
64 DFLT_PRJPIP_CYCLE_ID,
65 DFLT_PRJBAB_PERIOD_TYPE,
66 DFLT_PRJBAB_CYCLE_ID,
67 DFLT_RESUTL_PERIOD_TYPE,
68 DFLT_RESUTL_CYCLE_ID,
69 DFLT_RESAVL_PERIOD_TYPE,
70 DFLT_RESAVL_CYCLE_ID,
71 DFLT_RESPLN_PERIOD_TYPE,
72 DFLT_RESPLN_CYCLE_ID,
73 DFLT_PRJHLT_PERIOD_TYPE,
74 DFLT_PRJHLT_CYCLE_ID,
75 DFLT_PRJACT_PERIOD_TYPE,
76 DFLT_PRJACT_CYCLE_ID,
77 DFLT_PRJPRF_PERIOD_TYPE,
78 DFLT_PRJPRF_CYCLE_ID,
79 DFLT_PRJCST_PERIOD_TYPE,
80 DFLT_PRJCST_CYCLE_ID
81 INTO InitialValues
82 FROM PJI_SYSTEM_SETTINGS;
83
84 select MONTH_ID,
85 ENT_PERIOD_ID,
86 ENT_QTR_ID,
87 ENT_YEAR_ID,
88 WEEK_ID
89 into G_Month_ID_Value,
90 G_Ent_Period_ID_Value,
91 G_Ent_Quarter_ID_Value,
92 G_Ent_Year_ID_Value,
93 G_Week_ID_Value
94 from fii_time_day
95 where report_date = trunc(sysdate);
96
97 EXCEPTION
98 WHEN NO_DATA_FOUND THEN
99 -- Bug 5391217 If the call is from Discoverer ignore the exception
100 IF p_Calling_Context IS NULL THEN
101 raise_application_error(-20010,
102 'Parametrization table contains no data. Please, check tables PJI_SYSTEM_SETTINGS and FII_TIME_DAY');
103 ELSE
104 RETURN;
105 END IF;
106 WHEN TOO_MANY_ROWS
107 THEN
108 raise_application_error(-20010,
109 'Parametrization table contains more than 1 row. Please, check tables PJI_SYSTEM_SETTINGS and FII_TIME_DAY');
110
111 END InitEnvironment ;
112
113 -- ********************************************************
114 -- API to be called from each report type Procedure
115 -- ********************************************************
116
117 PROCEDURE InitParameters (p_Report_Type VARCHAR2) IS
118
119 l_Report_Type VARCHAR2(150);
120
121 BEGIN
122 l_Report_Type:=p_Report_Type;
123
124 CASE WHEN l_Report_Type ='Project Pipeline'
125 THEN G_Period_Type_Code :=InitialValues.DFLT_PRJPIP_PERIOD_TYPE;
126 G_Cycle_ID_Code :=InitialValues.DFLT_PRJPIP_CYCLE_ID;
127
128 WHEN l_Report_Type ='Project Bookings and Backlog'
129 THEN G_Period_Type_Code :=InitialValues.DFLT_PRJBAB_PERIOD_TYPE;
130 G_Cycle_ID_Code :=InitialValues.DFLT_PRJBAB_CYCLE_ID;
131
132 WHEN l_Report_Type ='Resource Utilization'
133 THEN G_Period_Type_Code :=InitialValues.DFLT_RESUTL_PERIOD_TYPE;
134 G_Cycle_ID_Code :=InitialValues.DFLT_RESUTL_CYCLE_ID;
135
136 WHEN l_Report_Type ='Resource Availability'
137 THEN G_Period_Type_Code :=InitialValues.DFLT_RESAVL_PERIOD_TYPE;
138 G_Cycle_ID_Code :=InitialValues.DFLT_RESAVL_CYCLE_ID;
139
140 WHEN l_Report_Type ='Resource Planning'
141 THEN G_Period_Type_Code :=InitialValues.DFLT_RESPLN_PERIOD_TYPE;
142 G_Cycle_ID_Code :=InitialValues.DFLT_RESPLN_CYCLE_ID;
143
144 WHEN l_Report_Type ='Project Health'
145 THEN G_Period_Type_Code :=InitialValues.DFLT_PRJHLT_PERIOD_TYPE;
146 G_Cycle_ID_Code :=InitialValues.DFLT_PRJHLT_CYCLE_ID;
147
148 WHEN l_Report_Type ='Project Activity'
149 THEN G_Period_Type_Code :=InitialValues.DFLT_PRJACT_PERIOD_TYPE;
150 G_Cycle_ID_Code :=InitialValues.DFLT_PRJACT_CYCLE_ID;
151
152 WHEN l_Report_Type ='Project Profitability'
153 THEN G_Period_Type_Code :=InitialValues.DFLT_PRJPRF_PERIOD_TYPE;
154 G_Cycle_ID_Code :=InitialValues.DFLT_PRJPRF_CYCLE_ID;
155
156 WHEN l_Report_Type ='Project Cost'
157 THEN G_Period_Type_Code :=InitialValues.DFLT_PRJCST_PERIOD_TYPE;
158 G_Cycle_ID_Code :=InitialValues.DFLT_PRJCST_CYCLE_ID;
159 END CASE;
160
161 EXCEPTION
162 WHEN OTHERS
163 THEN RAISE;
164
165 END InitParameters;
166
167 --*************************************************************
168 -- Derive Default Organization
169 --*************************************************************
170
171 FUNCTION Derive_Organization_ID(p_Calling_Context VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
172 IS
173 l_top_organization_id per_security_profiles.organization_id%TYPE;
174 l_top_org_name hr_all_organization_units_tl.name%TYPE;
175 l_user_assmt_flag VARCHAR2(1);
176 l_insert_top_org_flag VARCHAR2(1);
177 BEGIN
178
179 G_Org_Structure_Id :=InitialValues.ORGANIZATION_STRUCTURE_ID;
180 G_Org_Version_Id :=InitialValues.ORG_STRUCTURE_VERSION_ID;
181
182 PJI_PMV_UTIL.get_top_org_details(
183 x_top_org_id => l_top_organization_id,
184 x_top_org_name => l_top_org_name,
185 x_user_assmt_flag => l_user_assmt_flag,
186 x_insert_top_org_flag => l_insert_top_org_flag);
187
188 --Bug 4599990. View All Orgz
189 IF l_top_organization_id = 0 THEN
190 SELECT DISTINCT first_value(ID) over (ORDER BY lvl) into G_Org_ID
191 FROM
192 (
193 SELECT organization_level Lvl
194 , organization_id id
195 FROM
196 hri_org_hrchy_summary org_roll
197 WHERE org_roll.org_structure_version_id = G_Org_Version_Id
198 AND organization_level - sub_organization_level =0
199 );
200
201 Else
202 SELECT DISTINCT first_value(ID) over (ORDER BY lvl) into G_Org_ID
203 FROM
204 (
205 SELECT org_roll.sup_org_absolute_level Lvl
206 , org_list.id id
207 , org_list.value value
208 FROM
209 hri_cs_orghro_v org_roll
210 , pji_organizations_v org_list
211 WHERE
212 org_roll.org_hierarchy_version_id = G_Org_Version_Id
213 AND org_roll.sup_organization_id = org_list.id
214 AND org_roll.sup_org_absolute_level - org_roll.sub_org_absolute_level = 0
215 AND org_roll.subro_sup_org_relative_level = 0
216 AND org_roll.subro_sub_org_relative_level = 0
217 );
218 END IF;
219 RETURN G_Org_ID;
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 -- Bug 5086074 This check will not happen for discoverer implementation
223 IF p_Calling_Context IS NULL THEN
224 raise_application_error(-20010,
225 'Following views HRI_CS_ORGHRO_V,
226 PJI_ORGANIZATIONS_V may contain no data. Please, verify');
227 END IF;
228 RETURN NULL; /* added for bug 6034422 */
229 END Derive_Organization_ID;
230
231 --******************************************************
232 -- Derive Default Period ID
233 --******************************************************
234
235 FUNCTION Derive_Period_ID RETURN VARCHAR2 IS
236
237 BEGIN
238 SELECT prjpip.ID into G_Period_Type_ID
239 -- ,prjbab.value
240 FROM
241 pji_system_settings syssetting
242 , pji_period_types_v prjpip
243 , pji_period_types_v prjbab
244 WHERE
245 prjbab.global_disp_flag = 0
246 AND prjpip.global_disp_flag = 0
247 AND prjpip.ID='FII_TIME_'||DECODE(G_Period_Type_Code,
248 'YEAR','ENT_YEAR',
249 'QUARTER','ENT_QTR',
250 'MONTH','ENT_PERIOD',
251 'WEEK','WEEK')
252 AND prjbab.ID='FII_TIME_'||DECODE(G_Period_Type_Code,
253 'YEAR','ENT_YEAR',
254 'QUARTER','ENT_QTR',
255 'MONTH','ENT_PERIOD',
256 'WEEK','WEEK');
257 RETURN G_Period_Type_ID;
258 END Derive_Period_ID;
259
260 --******************************************************
261 -- Derive Enterprise Period ID
262 --******************************************************
263
264 FUNCTION Derive_EntPeriod_ID RETURN VARCHAR2 IS
265 BEGIN
266 SELECT LOOKUP_CODE into G_EntPeriod_ID
267 FROM
268 pji_lookups
269 WHERE LOOKUP_TYPE='PJI_PERIOD_TYPE_LIST'
270 AND LOOKUP_CODE='FII_TIME_ENT_PERIOD';
271 RETURN G_EntPeriod_ID;
272 END Derive_EntPeriod_ID;
273
274 --******************************************************
275 -- Derive Enterprise Week ID
276 --******************************************************
277
278 FUNCTION Derive_EntWeek_ID RETURN VARCHAR2 IS
279 BEGIN
280 SELECT LOOKUP_CODE into G_EntWeek_ID
281 FROM
282 pji_lookups
283 WHERE LOOKUP_TYPE='PJI_PERIOD_TYPE_LIST'
284 AND LOOKUP_CODE='FII_TIME_WEEK';
285 RETURN G_EntWeek_ID;
286 END Derive_EntWeek_ID;
287
288 --*******************************************************
289 --Derive Default Curency ID
290 --*******************************************************
291
292 FUNCTION Derive_Currency_ID RETURN VARCHAR2 IS
293
294 BEGIN
295 RETURN G_Currency_ID;
296 END Derive_Currency_ID;
297
298 --********************************************************
299 --Derive Derive_Period_ID_Value Paramater
300 --********************************************************
301
302 FUNCTION Derive_Period_ID_Value RETURN VARCHAR2 IS
303
304 BEGIN
305 select DECODE(G_Period_Type_Code,'WEEK', WEEK_ID
306 ,'MONTH', MONTH_ID
307 ,'PERIOD',ENT_PERIOD_ID
308 ,'QUARTER',ENT_QTR_ID
309 ,'YEAR',ENT_YEAR_ID)
310
311 into G_Period_Id_Value from
312 fii_time_day
313 where report_date = trunc(sysdate);
314 RETURN G_Period_Id_Value;
315
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 RAISE;
319 END Derive_Period_ID_Value;
320
321 --********************************************************
322 --Derive Ent Qtr ID Value Paramater (Portal)
323 --********************************************************
324
325 FUNCTION Derive_Ent_Quarter_ID_Value RETURN NUMBER IS
326 BEGIN
327 RETURN G_Ent_Quarter_ID_Value;
328 END Derive_Ent_Quarter_ID_Value;
329
330 --********************************************************
331 --Derive Ent Period ID Value
332 --********************************************************
333
334 FUNCTION Derive_Ent_Period_ID_Value RETURN NUMBER IS
335 BEGIN
336 RETURN G_Ent_Period_ID_Value;
337 END Derive_Ent_Period_ID_Value;
338
339 --********************************************************
340 --Derive Week ID Value Paramater
341 --********************************************************
342
343 FUNCTION Derive_Week_ID_Value RETURN NUMBER IS
344 BEGIN
345 RETURN G_Week_ID_Value;
346 END Derive_Week_ID_Value;
347
348
349 --*******************************************************
350 --Derive Availability Threshold
351 --*******************************************************
352
353 FUNCTION Derive_Avail_Threshold RETURN VARCHAR2 IS
354
355 BEGIN
356 select TO_CHAR(seq) into G_Avail_Threshold
357 from pji_mt_buckets where bucket_set_code ='PJI_RESOURCE_AVAILABILITY'
358 and default_flag='Y';
359 RETURN G_Avail_Threshold;
360 END Derive_Avail_Threshold;
361
362 END PJI_PMV_DFLT_PARAMS_PVT;
363