PACKAGE BODY: APPS.PA_PJI_UTIL_PKG

Source

1 PACKAGE BODY pa_pji_util_pkg AS
2 /* $Header: PAPJIUTB.pls 120.2 2011/09/20 12:14:33 arbandyo ship $ */
3 
4 ---------------------------------------------------------------------
5 --This package replaces the PA package created from file PAPJIUTB.pls
6 --The utilization details are got from PJI data model
7 ---------------------------------------------------------------------
8 
9 PROCEDURE get_utilization_dtls
10   ( p_org_id               IN pa_implementations_all.org_id%TYPE
11                               := NULL
12    ,p_organization_id      IN hr_organization_units.organization_id%TYPE
13                               := NULL
14    ,p_period_type          IN pa_forecasting_options_all.org_fcst_period_type%TYPE
15                               := NULL
16    ,p_period_set_name      IN gl_periods.period_set_name%TYPE
17                               := NULL
18    ,p_period_name          IN gl_periods.period_name%TYPE
19                               := NULL
20    ,x_utl_hours           OUT NOCOPY /* file.sql.39 change */ NUMBER
21    ,x_utl_capacity        OUT NOCOPY /* file.sql.39 change */ NUMBER
22    ,x_utl_percent         OUT NOCOPY /* file.sql.39 change */ NUMBER
23    ,x_return_status       OUT NOCOPY /* file.sql.39 change */ VARCHAR2
24    ,x_err_code            OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
25 IS
26 
27     l_calendar_id NUMBER;
28 	l_period_id NUMBER;
29 	l_capacity_hrs NUMBER;
30 	l_reduce_capacity_hrs NUMBER;
31 
32 BEGIN
33      x_return_status := FND_API.G_RET_STS_SUCCESS;
34 
35      -----------------
36 	 --Get calendar ID
37 	 -----------------
38 	 BEGIN
39 
40 	     SELECT calendar_id
41 		 INTO   l_calendar_id
42 		 FROM pa_time_cal_name /* Modified for bug 12979524 */
43 		 WHERE period_set_name = p_period_set_name AND
44 		       period_type = p_period_type;
45 
46 	 EXCEPTION
47 	    WHEN NO_DATA_FOUND THEN
48 		    RETURN;
49 	 END;
50 
51 	 --------------------------------
52 	 --Get period_id for the calendar
53 	 --------------------------------
54 	 BEGIN
55 
56 	     SELECT cal_period_id
57 		 INTO   l_period_id
58 		 FROM pji_time_cal_period
59 		 WHERE calendar_id = l_calendar_id AND
60 		       name = p_period_name;
61 
62 	 EXCEPTION
63 	    WHEN NO_DATA_FOUND THEN
64 		    x_return_status := FND_API.G_RET_STS_ERROR;
65 			x_err_code      := SQLERRM;
66 		    RETURN;
67 	 END;
68 
69 
70 	 --------------------------------
71 	 --Get Utilization details
72 	 --------------------------------
73 	 BEGIN
74 
75 	     SELECT NVL(conf_wtd_org_hrs_s,0),
76 		        NVL(capacity_hrs,0),
77 				NVL(reduce_capacity_hrs_s,0)
78 		 INTO   x_utl_hours,
79 		        l_capacity_hrs,
80 				l_reduce_capacity_hrs
81 		 FROM pji_rm_org_f_mv
82 		 WHERE expenditure_organization_id = p_organization_id AND
83 		       expenditure_org_id          = p_org_id          AND
84 			   time_id                     = l_period_id       AND
85                period_type_id              = 32                AND
86                ROWNUM = 1;
87 
88 	 EXCEPTION
89 	    WHEN NO_DATA_FOUND THEN
90 		    x_return_status := FND_API.G_RET_STS_ERROR;
91 			x_err_code      := SQLERRM;
92 		    RETURN;
93 	 END;
94 
95 	 x_utl_capacity := l_capacity_hrs - l_reduce_capacity_hrs;
96 
97      IF x_utl_capacity = 0 THEN
98          x_utl_percent := null;
99      ELSE
100    	     x_utl_percent  := (x_utl_hours * 100)/x_utl_capacity;
101      END IF;
102 
103 	 x_return_status := FND_API.G_RET_STS_SUCCESS;
104 
105 EXCEPTION
106      WHEN OTHERS THEN
107           x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
108           x_err_code      := SQLERRM;
109 
110 END get_utilization_dtls;
111 
112 
113 END pa_pji_util_pkg;