[Home] [Help]
PACKAGE BODY: APPS.PQP_VEH_ALLOC_INFO_TYPES_PKG
Source
1 PACKAGE BODY pqp_veh_alloc_info_types_pkg as
2 /* $Header: pqpvehalloceit.pkb 120.0 2005/05/29 02:23:27 appldev noship $ */
3 ------------------------------------------------------------------------------
4 /*
5 ==============================================================================
6
7 03-dec-04 sshetty Created.
8 ==============================================================================
9 */
10 ------------------------------------------------------------------------------+
11 --------------------------------------------------------------------------------
12 g_dummy number(1); -- Dummy for cursor returns which are not needed
13 g_business_group_id number(15); -- For validating translation;
14 g_legislation_code varchar2(150); -- For validating translation;
15 --------------------------------------------------------------------------------
16 --
17 PROCEDURE UNIQUENESS_CHECK(P_INFORMATION_TYPE VARCHAR2,
18 P_ACTIVE_INACTIVE_FLAG VARCHAR2,
19 P_LEGISLATION_CODE VARCHAR2,
20 P_ROWID VARCHAR2,
21 P_DESCRIPTION VARCHAR2)
22 IS
23 L_DUMMY1 number;
24 CURSOR C1 IS
25 select 1
26 from pqp_veh_alloc_info_types t
27 where upper(t.description) = upper(P_DESCRIPTION)
28 and nvl(t.legislation_code, nvl(P_LEGISLATION_CODE, 'XXX') )
29 = nvl(P_LEGISLATION_CODE, 'XXX')
30 and (P_ROWID is null
31 or P_ROWID <> t.rowid);
32 BEGIN
33 OPEN C1;
34 FETCH C1 INTO L_DUMMY1;
35 IF C1%NOTFOUND THEN
36 CLOSE C1;
37 ELSE
38 CLOSE C1;
39 hr_utility.set_message('801','HR_7777_DEF_DESCR_EXISTS');
40 hr_utility.raise_error;
41 END IF;
42 end UNIQUENESS_CHECK;
43 --
44 procedure INSERT_ROW (
45 X_ROWID in out nocopy VARCHAR2,
46 X_INFORMATION_TYPE in VARCHAR2,
47 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
48 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
49 X_LEGISLATION_CODE in VARCHAR2,
50 X_OBJECT_VERSION_NUMBER in NUMBER,
51 X_DESCRIPTION in VARCHAR2,
52 X_CREATION_DATE in DATE,
53 X_CREATED_BY in NUMBER,
54 X_LAST_UPDATE_DATE in DATE,
55 X_LAST_UPDATED_BY in NUMBER,
56 X_LAST_UPDATE_LOGIN in NUMBER
57 ) is
58 cursor C is select ROWID from pqp_veh_alloc_info_types
59 where INFORMATION_TYPE = X_INFORMATION_TYPE
60 ;
61 begin
62 insert into pqp_veh_alloc_info_types (
63 INFORMATION_TYPE,
64 ACTIVE_INACTIVE_FLAG,
65 MULTIPLE_OCCURENCES_FLAG,
66 LEGISLATION_CODE,
67 OBJECT_VERSION_NUMBER,
68 DESCRIPTION,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN
74 ) values (
75 X_INFORMATION_TYPE,
76 X_ACTIVE_INACTIVE_FLAG,
77 X_MULTIPLE_OCCURENCES_FLAG,
78 X_LEGISLATION_CODE,
79 X_OBJECT_VERSION_NUMBER,
80 X_DESCRIPTION,
81 X_CREATION_DATE,
82 X_CREATED_BY,
83 X_LAST_UPDATE_DATE,
84 X_LAST_UPDATED_BY,
85 X_LAST_UPDATE_LOGIN
86 );
87
88 open c;
89 fetch c into X_ROWID;
90 if (c%notfound) then
91 close c;
92 raise no_data_found;
93 end if;
94 close c;
95
96 end INSERT_ROW;
97
98 procedure LOCK_ROW (
99 X_INFORMATION_TYPE in VARCHAR2,
100 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
101 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
102 X_LEGISLATION_CODE in VARCHAR2,
103 X_OBJECT_VERSION_NUMBER in NUMBER,
104 X_DESCRIPTION in VARCHAR2
105 ) is
106 cursor c is select
107 ACTIVE_INACTIVE_FLAG,
108 MULTIPLE_OCCURENCES_FLAG,
109 LEGISLATION_CODE,
110 OBJECT_VERSION_NUMBER
111 from pqp_veh_alloc_info_types
112 where INFORMATION_TYPE = X_INFORMATION_TYPE
113 for update of INFORMATION_TYPE nowait;
114 recinfo c%rowtype;
115
116 begin
117 open c;
118 fetch c into recinfo;
119 if (c%notfound) then
120 close c;
121 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
122 app_exception.raise_exception;
123 end if;
124 close c;
125 if ( (recinfo.ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG)
126 AND (recinfo.MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG)
127 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
128 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
129 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
130 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
131 ) then
132 null;
133 else
134 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135 app_exception.raise_exception;
136 end if;
137
138 return;
139 end LOCK_ROW;
140
141 procedure UPDATE_ROW (
142 X_INFORMATION_TYPE in VARCHAR2,
143 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
144 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
145 X_LEGISLATION_CODE in VARCHAR2,
146 X_OBJECT_VERSION_NUMBER in NUMBER,
147 X_DESCRIPTION in VARCHAR2,
148 X_LAST_UPDATE_DATE in DATE,
149 X_LAST_UPDATED_BY in NUMBER,
150 X_LAST_UPDATE_LOGIN in NUMBER
151 ) is
152 begin
153 update pqp_veh_alloc_info_types set
154 ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
155 MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG,
156 LEGISLATION_CODE = X_LEGISLATION_CODE,
157 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
158 DESCRIPTION = X_DESCRIPTION,
159 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
160 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
161 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
162 where INFORMATION_TYPE = X_INFORMATION_TYPE;
163
164 if (sql%notfound) then
165 raise no_data_found;
166 end if;
167
168 end UPDATE_ROW;
169
170 procedure DELETE_ROW (
171 X_INFORMATION_TYPE in VARCHAR2
172 ) is
173 begin
174
175 delete from pqp_veh_alloc_info_types
176 where INFORMATION_TYPE = X_INFORMATION_TYPE;
177
178 if (sql%notfound) then
179 raise no_data_found;
180 end if;
181 end DELETE_ROW;
182
183 procedure LOAD_ROW
184 (X_INFORMATION_TYPE in varchar2
185 ,X_ACTIVE_INACTIVE_FLAG in varchar2
186 ,X_MULTIPLE_OCCURENCES_FLAG in varchar2
187 ,X_DESCRIPTION in varchar2
188 ,X_LEGISLATION_CODE in varchar2
189 ,X_OBJECT_VERSION_NUMBER in number
190 ,X_OWNER in varchar2
191 ,X_LAST_UPDATE_DATE in date
192 )
193 is
194 l_proc VARCHAR2(61) := 'pqp_veh_alloc_info_types_PKG.LOAD_ROW';
195 l_rowid rowid;
196 l_created_by pqp_veh_alloc_info_types.created_by%TYPE := 0;
197 l_creation_date pqp_veh_alloc_info_types.creation_date%TYPE := SYSDATE;
198 l_last_update_date pqp_veh_alloc_info_types.last_update_date%TYPE := SYSDATE;
199 l_last_updated_by pqp_veh_alloc_info_types.last_updated_by%TYPE := 0;
200 l_last_update_login pqp_veh_alloc_info_types.last_update_login%TYPE := 0;
201 begin
202 -- Translate developer keys to internal parameters
203 if X_OWNER = 'ORACLE' then
204 l_created_by := 2;
205 l_last_updated_by := 2;
206 end if;
207 -- Update or insert row as appropriate
208 begin
209 UPDATE_ROW
210 (X_INFORMATION_TYPE => X_INFORMATION_TYPE
211 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
212 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
213 ,X_DESCRIPTION => X_DESCRIPTION
214 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
215 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
216 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE --l_last_update_date
217 ,X_LAST_UPDATED_BY => l_last_updated_by
218 ,X_LAST_UPDATE_LOGIN => l_last_update_login
219 );
220 exception
221 when no_data_found then
222 INSERT_ROW
223 (X_ROWID => l_rowid
224 ,X_INFORMATION_TYPE => X_INFORMATION_TYPE
225 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
226 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
227 ,X_DESCRIPTION => X_DESCRIPTION
228 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
229 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
230 ,X_CREATED_BY => l_created_by
231 ,X_CREATION_DATE => l_creation_date
232 ,X_LAST_UPDATE_DATE => l_last_update_date
233 ,X_LAST_UPDATED_BY => l_last_updated_by
234 ,X_LAST_UPDATE_LOGIN => l_last_update_login
235 );
236 end;
237 --
238 end LOAD_ROW;
239
240 procedure TRANSLATE_ROW
241 (X_INFORMATION_TYPE in varchar2
242 ,X_DESCRIPTION in varchar2
243 ,X_OWNER in varchar2
244 )
245 is
246 begin
247 UPDATE pqp_veh_alloc_info_types
248 SET description = X_DESCRIPTION
249 ,last_update_date = SYSDATE
250 ,last_updated_by = DECODE(X_OWNER,'ORACLE',2,1)
251 ,last_update_login = 0
252 WHERE USERENV('LANG') = (select language_code from fnd_languages
253 where installed_flag = 'B')
254 AND information_type = X_INFORMATION_TYPE;
255 end TRANSLATE_ROW;
256
257
258 END pqp_veh_alloc_info_types_pkg;