[Home] [Help]
PACKAGE BODY: APPS.WIP_BIS_COMMON
Source
1 PACKAGE BODY WIP_BIS_COMMON AS
2 /* $Header: wipbcomb.pls 120.0 2005/05/24 18:04:52 appldev noship $ */
3
4
5 /* Private Global Variables */
6
7 g_legal_entity NUMBER := 204 ;
8
9 /* Public Procedures */
10
11 FUNCTION GET_SEGMENT( str IN VARCHAR2,
12 delim IN VARCHAR2,
13 segment_num IN NUMBER ) RETURN VARCHAR2 IS
14 -- first segment is segment #0
15 l_str VARCHAR2(1000);
16 BEGIN
17 l_str := str || delim;
18 if segment_num = 0 then
19 return substr(str, 1, instr(l_str, delim, 1, 1)-1);
20 else
21 return substr(str, instr(l_str, delim, 1, segment_num)+1,
22 instr(l_str, delim, 1, segment_num+1) -
23 instr(l_str, delim, 1, segment_num) - 1 );
24 end if;
25
26 END GET_SEGMENT;
27
28
29 function Avg_Employee_Num
30 (p_acct_period_id IN NUMBER,
31 p_organization_id IN NUMBER)
32 return NUMBER is
33
34 Cursor Employee_Num(p_date DATE, p_organization_id NUMBER) is
35 select count(assignment_id)
36 from per_all_assignments_f
37 where primary_flag = 'Y'
38 and assignment_type = 'E'
39 and trunc(p_date) between effective_start_date and effective_end_date
40 and organization_id = p_organization_id;
41
42 x_start_date DATE;
43 x_close_date DATE;
44 x_initial_count NUMBER;
45 x_final_count NUMBER;
46
47 begin
48
49 begin
50
51 select PERIOD_START_DATE, SCHEDULE_CLOSE_DATE
52 into x_start_date, x_close_date
53 from org_acct_periods
54 where ACCT_PERIOD_ID = p_acct_period_id
55 and ORGANIZATION_ID = p_organization_id ;
56
57 exception
58
59 when NO_DATA_FOUND then
60
61 return null ;
62 end ;
63
64
65 OPEN Employee_Num (x_start_date, p_organization_id) ;
66 FETCH Employee_Num INTO x_initial_count;
67 if (Employee_Num%NOTFOUND) then
68 CLOSE Employee_Num;
69 end if;
70 CLOSE Employee_Num;
71
72 OPEN Employee_Num (x_close_date, p_organization_id) ;
73 FETCH Employee_Num INTO x_final_count;
74 if (Employee_Num%NOTFOUND) then
75 CLOSE Employee_Num;
76 end if;
77 CLOSE Employee_Num;
78
79
80 return (x_initial_count+x_final_count)/2 ;
81
82
83 end Avg_Employee_Num;
84
85
86 function get_Legal_Entity
87 return NUMBER is
88
89 begin
90
91 return g_legal_entity;
92
93 end get_Legal_Entity ;
94
95
96 procedure set_Legal_Entity(p_legal_entity in NUMBER ) is
97 begin
98
99 g_legal_entity := p_legal_entity ;
100 return ;
101
102 end set_Legal_Entity ;
103
104 function get_Period_Target
105 (p_calendar IN VARCHAR2,
106 p_period_value IN VARCHAR2,
107 p_organization_id IN NUMBER,
108 p_indicator IN VARCHAR2)
109 return NUMBER
110 IS
111 x_target NUMBER;
112 x_organization VARCHAR2(250);
113 s_cursor NUMBER;
114 ignore NUMBER;
115 selstmt VARCHAR2(2000);
116 Begin
117
118 /*
119 begin
120
121 select organization_name into x_organization
122 from org_organization_definitions
123 where organization_id = p_organization_id ;
124
125 exception
126 when others then
127
128 x_organization := null ;
129
130 end ;
131
132 */
133
134
135 if (p_indicator = 'WIPBIIT') then
136 select target
137 into x_target
138 from bis_wipbiitorgprd_v
139 where organization = p_organization_id
140 and calendar = p_calendar
141 and period_value = p_period_value;
142 elsif (p_indicator = 'WIPBIPA') then
143 select target
144 into x_target
145 from bis_wipbipaorgprd_v
146 where organization = p_organization_id
147 and calendar = p_calendar
148 and period_value = p_period_value;
149 elsif (p_indicator = 'WIPBIUZ') then
150 select target
151 into x_target
152 from bis_wipbiuzorgprd_v
153 where organization = p_organization_id
154 and calendar = p_calendar
155 and period_value = p_period_value;
156 elsif (p_indicator = 'WIPBIEF') then
157 select target
158 into x_target
159 from bis_wipbieforgprd_v
160 where organization = p_organization_id
161 and calendar = p_calendar
162 and period_value = p_period_value;
163 end if;
164
165 return x_target ;
166
167 exception
168 when no_data_found then
169 x_target := null;
170 return x_target ;
171
172
173 End get_Period_Target ;
174
175 END WIP_BIS_COMMON ;