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