[Home] [Help]
PACKAGE BODY: APPS.INL_ATTRIBUTES_PKG
Source
1 PACKAGE BODY INL_ATTRIBUTES_PKG as
2 /* $Header: INLHPATB.pls 120.0.12020000.10 2013/03/28 13:50:52 acferrei noship $ */
3 -- Procedure name : insert_row
4 -- Type : Table Handler
5 -- Function : Insert data on ATTRIBUTES_B and ATTRIBUTES_TL tables
6 --
7 -- Pre-reqs : None
8 -- Parameters :
9 -- IN : p_attribute_code IN VARCHAR2
10 -- p_entity_code IN VARCHAR2
11 -- p_attribute_name IN VARCHAR2
12 -- p_description IN VARCHAR2
13 -- p_datatype_code IN VARCHAR2
14 -- p_creation_date IN DATE
15 -- p_created_by IN NUMBER
16 -- p_last_update_date IN DATE
17 -- p_last_updated_by IN NUMBER
18 -- p_last_update_login IN NUMBER,
19 --
20 -- OUT x_rowid IN OUT NOCOPY VARCHAR2
21 --
22 -- Version : Current version 1.0
23 --
24 -- Notes :
25 PROCEDURE insert_row(
26 p_attribute_code IN VARCHAR2,
27 p_entity_code IN VARCHAR2,
28 p_attribute_name IN VARCHAR2,
29 p_description IN VARCHAR2,
30 p_datatype_code IN VARCHAR2,
31 p_creation_date IN DATE,
32 p_created_by IN NUMBER,
33 p_last_update_date IN DATE,
34 p_last_updated_by IN NUMBER,
35 p_last_update_login IN NUMBER,
36 x_rowid IN OUT NOCOPY VARCHAR2)IS
37
38 CURSOR C IS
39 SELECT ROWID
40 FROM inl_attributes_b
41 WHERE attribute_code = p_attribute_code
42 AND entity_code = p_entity_code ;
43
44 l_last_update_date DATE;
45
46 BEGIN
47
48 -- Translate char last_update_date to date using TO_DATE
49 l_last_update_date := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'),sysdate);
50
51 INSERT INTO inl_attributes_b (
52 attribute_code,
53 entity_code,
54 datatype_code,
55 creation_date,
56 created_by,
57 last_update_date,
58 last_updated_by,
59 last_update_login)
60 VALUES ( p_attribute_code,
61 p_entity_code,
62 p_datatype_code,
63 sysdate,
64 p_created_by,
65 sysdate,
66 p_last_updated_by,
67 p_last_update_login
68 );
69
70 INSERT INTO inl_attributes_tl(
71 attribute_code,
72 entity_code,
73 attribute_name,
74 description,
75 language,
76 source_lang,
77 creation_date,
78 created_by,
79 last_update_date,
80 last_updated_by,
81 last_update_login
82 ) SELECT
83 p_attribute_code,
84 p_entity_code,
85 p_attribute_name,
86 p_description,
87 l.language_code,
88 userenv('LANG'),
89 sysdate,
90 p_created_by,
91 sysdate,
92 p_last_updated_by,
93 p_last_update_login
94 FROM FND_LANGUAGES L
95 WHERE L.INSTALLED_FLAG IN ('I', 'B')
96 AND NOT EXISTS
97 (SELECT NULL
98 FROM inl_attributes_tl a
99 WHERE a.attribute_code = p_attribute_code
100 AND a.entity_code = p_entity_code
101 AND a.LANGUAGE = L.LANGUAGE_CODE);
102
103 OPEN c;
104 FETCH c INTO X_ROWID;
105 IF (c%notfound) THEN
106 CLOSE c;
107 RAISE no_data_found;
108 END IF;
109 CLOSE c;
110
111 END insert_row;
112
113 -- Procedure name : update_row
114 -- Type : Table Handler
115 -- Function : Update data on ATTRIBUTES_B and ATTRIBUTES_TL tables
116 --
117 -- Pre-reqs : None
118 -- Parameters :
119 -- IN : p_attribute_code IN VARCHAR2
120 -- p_entity_code IN VARCHAR2
121 -- p_attribute_name IN VARCHAR2
122 -- p_description IN VARCHAR2
123 -- p_datatype_code IN VARCHAR2
124 -- p_last_update_date IN DATE
125 -- p_last_updated_by IN NUMBER
126 -- p_last_update_login IN NUMBER
127 --
128 -- Version : Current version 1.0
129 --
130 -- Notes :
131 PROCEDURE update_row (
132 p_attribute_code IN VARCHAR2,
133 p_entity_code IN VARCHAR2,
134 p_attribute_name IN VARCHAR2,
135 p_description IN VARCHAR2,
136 p_datatype_code IN VARCHAR2,
137 p_last_update_date IN DATE,
138 p_last_updated_by IN NUMBER,
139 p_last_update_login IN NUMBER) IS
140
141 l_last_update_date DATE;
142
143 BEGIN
144
145 -- Translate char last_update_date to date using TO_DATE
146 l_last_update_date := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'),sysdate);
147
148 UPDATE inl_attributes_b SET
149 datatype_code = p_datatype_code,
150 last_update_date = sysdate,
151 last_updated_by = p_last_updated_by,
152 last_update_login = p_last_update_login
153 WHERE attribute_code = p_attribute_code
154 AND entity_code = p_entity_code;
155
156 IF (SQL%NOTFOUND) THEN
157 RAISE no_data_found;
158 END IF;
159
160 UPDATE inl_attributes_tl SET
161 attribute_name = p_attribute_name,
162 description = p_description,
163 last_update_date = sysdate,
164 last_updated_by = p_last_updated_by,
165 last_update_login = p_last_update_login,
166 source_lang = userenv('lang')
167 WHERE attribute_code = p_attribute_code
168 AND entity_code = p_entity_code
169 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
170
171 IF (SQL%NOTFOUND) THEN
172 RAISE no_data_found;
173 END IF;
174
175 END update_row;
176
177 -- Procedure name : translate_row
178 -- Type : Table Handler
179 -- Function : Update data on ATTRIBUTES_TL table
180 --
181 -- Pre-reqs : None
182 -- Parameters :
183 -- IN : p_attribute_code IN VARCHAR2
184 -- p_entity_code IN VARCHAR2
185 -- p_attribute_name IN VARCHAR2
186 -- p_description IN VARCHAR2
187 -- p_owner IN VARCHAR2
188 --
189 -- Version : Current version 1.0
190 --
191 -- Notes :
192 PROCEDURE translate_row(
193 p_attribute_code IN VARCHAR2,
194 p_entity_code IN VARCHAR2,
195 p_attribute_name IN VARCHAR2,
196 p_description IN VARCHAR2,
197 p_owner IN VARCHAR2,
198 p_last_update_date IN VARCHAR2 ) IS
199
200 l_owner VARCHAR2(50);
201 l_last_update_date DATE;
202 BEGIN
203
204 -- Translate owner
205 l_owner := fnd_load_util.owner_id(p_owner);
206
207 -- Translate char last_update_date to date using TO_DATE
208 l_last_update_date := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'),sysdate);
209
210 UPDATE inl_attributes_tl SET
211 attribute_name = p_attribute_name,
212 description = p_description,
213 last_update_date = sysdate,
214 last_updated_by = l_owner, --Decode(p_owner,'SEED',1,0),
215 last_update_login = 0,
216 source_lang = userenv('LANG')
217 WHERE entity_code = p_entity_code
218 AND attribute_code = p_attribute_code
219 AND userenv('LANG') IN (language,source_lang);
220 END translate_row;
221
222 -- Procedure name : load_seed_row
223 -- Type : Table Handler
224 -- Function : Load data on ATTRIBUTES_B and ATTRIBUTES_TL tables
225 --
226 -- Pre-reqs : None
227 -- Parameters :
228 -- IN : p_attribute_code IN VARCHAR2
229 -- p_entity_code IN VARCHAR2
230 -- p_attribute_name IN VARCHAR2
231 -- p_description IN VARCHAR2
232 -- p_datatype_code IN VARCHAR2
233 -- p_owner IN VARCHAR2
234 --
235 -- Version : Current version 1.0
236 --
237 -- Notes :
238 PROCEDURE load_seed_row
239 (p_attribute_code IN VARCHAR2,
240 p_entity_code IN VARCHAR2,
241 p_attribute_name IN VARCHAR2,
242 p_description IN VARCHAR2,
243 p_datatype_code IN VARCHAR2,
244 p_owner IN VARCHAR2,
245 p_last_update_date IN VARCHAR2) IS
246 BEGIN
247 DECLARE
248 l_owner VARCHAR2(100);
249 l_rowid VARCHAR2(64);
250
251 BEGIN
252
253 -- Translate owner
254 l_owner := fnd_load_util.owner_id(p_owner);
255
256 inl_attributes_pkg.update_row(
257 p_attribute_code => p_attribute_code,
258 p_entity_code => p_entity_code,
259 p_attribute_name => p_attribute_name,
260 p_description => p_description,
261 p_datatype_code => p_datatype_code,
262 p_last_update_date => SYSDATE ,
263 p_last_updated_by => l_owner,
264 p_last_update_login => 0);
265
266 EXCEPTION
267 WHEN no_data_found THEN
268 inl_attributes_pkg.insert_row(
269 p_attribute_code => p_attribute_code,
270 p_entity_code => p_entity_code,
271 p_attribute_name => p_attribute_name,
272 p_description => p_description,
273 p_datatype_code => p_datatype_code,
274 p_creation_date => SYSDATE,
275 p_created_by => l_owner ,
276 p_last_update_date => SYSDATE,
277 p_last_updated_by => l_owner,
278 p_last_update_login => 0,
279 x_rowid => l_rowid
280 );
281 END;
282 END load_seed_row;
283 END INL_ATTRIBUTES_PKG;