[Home] [Help]
PACKAGE BODY: APPS.WIP_BIS_COMMON
Source
1 PACKAGE BODY WIP_BIS_COMMON AS
2 /* $Header: wipbcomb.pls 120.1 2011/08/08 17:35:44 hliew ship $ */
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 /*Fix bug 12805996: Commented out the following function due to DBI obsolescence
105 function get_Period_Target
106 (p_calendar IN VARCHAR2,
107 p_period_value IN VARCHAR2,
108 p_organization_id IN NUMBER,
109 p_indicator IN VARCHAR2)
110 return NUMBER
111 IS
112 x_target NUMBER;
113 x_organization VARCHAR2(250);
114 s_cursor NUMBER;
115 ignore NUMBER;
116 selstmt VARCHAR2(2000);
117 Begin*/
118
119 /*
120 begin
121
122 select organization_name into x_organization
123 from org_organization_definitions
124 where organization_id = p_organization_id ;
125
126 exception
127 when others then
128
129 x_organization := null ;
130
131 end ;
132
133 */
134
135 /*
136 if (p_indicator = 'WIPBIIT') then
137 select target
138 into x_target
139 from bis_wipbiitorgprd_v
140 where organization = p_organization_id
141 and calendar = p_calendar
142 and period_value = p_period_value;
143 elsif (p_indicator = 'WIPBIPA') then
144 select target
145 into x_target
146 from bis_wipbipaorgprd_v
147 where organization = p_organization_id
148 and calendar = p_calendar
149 and period_value = p_period_value;
150 elsif (p_indicator = 'WIPBIUZ') then
151 select target
152 into x_target
153 from bis_wipbiuzorgprd_v
154 where organization = p_organization_id
155 and calendar = p_calendar
156 and period_value = p_period_value;
157 elsif (p_indicator = 'WIPBIEF') then
158 select target
159 into x_target
160 from bis_wipbieforgprd_v
161 where organization = p_organization_id
162 and calendar = p_calendar
163 and period_value = p_period_value;
164 end if;
165
166 return x_target ;
167
168 exception
169 when no_data_found then
170 x_target := null;
171 return x_target ;
172
173
174 End get_Period_Target ;*/
175
176 END WIP_BIS_COMMON ;