[Home] [Help]
PACKAGE BODY: APPS.GCS_CONS_ENG_RUN_DTLS_PKG
Source
1 PACKAGE BODY GCS_CONS_ENG_RUN_DTLS_PKG AS
2 /* $Header: gcs_eng_run_dtlb.pls 120.2 2005/12/07 02:23:58 skamdar noship $ */
3
4 g_api VARCHAR2(80) := 'gcs.plsql.GCS_CONS_ENG_RUN_DTLS_PKG';
5
6 PROCEDURE insert_row ( p_run_detail_id OUT NOCOPY NUMBER,
7 p_run_name IN VARCHAR2,
8 p_consolidation_entity_id IN NUMBER,
9 p_category_code IN VARCHAR2,
10 p_child_entity_id IN NUMBER,
11 p_contra_child_entity_id IN NUMBER,
12 p_rule_id IN NUMBER,
13 p_entry_id IN NUMBER,
14 p_stat_entry_id IN NUMBER,
15 p_request_error_code IN VARCHAR2,
16 p_bp_request_error_code IN VARCHAR2,
17 p_pre_prop_entry_id IN NUMBER,
18 p_pre_prop_stat_entry_id IN NUMBER,
19 p_cons_relationship_id IN NUMBER)
20
21 IS PRAGMA AUTONOMOUS_TRANSACTION;
22
23 BEGIN
24
25 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
26 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.INSERT_ROW', '<<Enter>>');
27 END IF;
28
29
30 INSERT INTO gcs_cons_eng_run_dtls
31 (
32 RUN_DETAIL_ID,
33 RUN_NAME,
34 CONSOLIDATION_ENTITY_ID,
35 CATEGORY_CODE,
36 CHILD_ENTITY_ID,
37 CONTRA_CHILD_ENTITY_ID,
38 RULE_ID,
39 ENTRY_ID,
40 STAT_ENTRY_ID,
41 REQUEST_ERROR_CODE,
42 BP_REQUEST_ERROR_CODE,
43 PRE_PROP_ENTRY_ID,
44 PRE_PROP_STAT_ENTRY_ID,
45 CONS_RELATIONSHIP_ID,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_LOGIN
51 )
52 VALUES
53 (
54 gcs_cons_eng_run_dtls_s.nextval,
55 p_run_name,
56 p_consolidation_entity_id,
57 p_category_code,
58 p_child_entity_id,
59 p_contra_child_entity_id,
60 p_rule_id,
61 p_entry_id,
62 p_stat_entry_id,
63 p_request_error_code,
64 p_bp_request_error_code,
65 p_pre_prop_entry_id,
66 p_pre_prop_stat_entry_id,
67 p_cons_relationship_id,
68 sysdate,
69 FND_GLOBAL.USER_ID,
70 sysdate,
71 FND_GLOBAL.USER_ID,
72 FND_GLOBAL.LOGIN_ID
73 )
74 RETURNING run_detail_id INTO p_run_detail_id;
75
76 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
77 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.INSERT_ROW', '<<Exit>>');
78 END IF;
79
80
81 COMMIT;
82
83 END;
84
85 PROCEDURE update_entry_headers( p_run_detail_id IN NUMBER,
86 p_entry_id IN NUMBER,
87 p_stat_entry_id IN NUMBER DEFAULT NULL,
88 p_pre_prop_entry_id IN NUMBER DEFAULT NULL,
89 p_pre_prop_stat_entry_id IN NUMBER DEFAULT NULL,
90 p_request_error_code IN VARCHAR2 DEFAULT NULL,
91 p_bp_request_error_code IN VARCHAR2 DEFAULT NULL
92 )
93 IS
94
95 BEGIN
96
97 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
98 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_ENTRY_HEADERS', '<<Enter>>');
99 END IF;
100
101 UPDATE gcs_cons_eng_run_dtls
102 SET entry_id = NVL(p_entry_id, entry_id),
103 stat_entry_id = NVL(p_stat_entry_id, stat_entry_id),
104 pre_prop_entry_id = NVL(p_pre_prop_entry_id, pre_prop_entry_id),
105 pre_prop_stat_entry_id = NVL(p_pre_prop_stat_entry_id, pre_prop_stat_entry_id),
106 request_error_code = NVL(p_request_error_code, request_error_code),
107 bp_request_error_code = NVL(p_bp_request_error_code, bp_request_error_code),
108 last_update_date = sysdate,
109 last_updated_by = FND_GLOBAL.user_id
110 WHERE run_detail_id = p_run_detail_id;
111
112 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
113 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_ENTRY_HEADERS', '<<Exit>>');
114 END IF;
115
116
117 END;
118
119 PROCEDURE update_entry_headers_async( p_run_detail_id IN NUMBER,
120 p_entry_id IN NUMBER DEFAULT NULL,
121 p_stat_entry_id IN NUMBER DEFAULT NULL,
122 p_pre_prop_entry_id IN NUMBER DEFAULT NULL,
123 p_pre_prop_stat_entry_id IN NUMBER DEFAULT NULL,
124 p_request_error_code IN VARCHAR2 DEFAULT NULL,
125 p_bp_request_error_code IN VARCHAR2 DEFAULT NULL
126 )
127 IS PRAGMA AUTONOMOUS_TRANSACTION;
128
129 BEGIN
130 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
131 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_ENTRY_HEADERS_ASYNC', '<<Enter>>');
132 END IF;
133
134 UPDATE gcs_cons_eng_run_dtls
135 SET entry_id = NVL(p_entry_id, entry_id),
136 stat_entry_id = NVL(p_stat_entry_id, stat_entry_id),
137 pre_prop_entry_id = NVL(p_pre_prop_entry_id, pre_prop_entry_id),
138 pre_prop_stat_entry_id = NVL(p_pre_prop_stat_entry_id, pre_prop_stat_entry_id),
139 request_error_code = NVL(p_request_error_code, request_error_code),
140 bp_request_error_code = NVL(p_bp_request_error_code, bp_request_error_code),
141 last_update_date = sysdate,
142 last_updated_by = FND_GLOBAL.USER_ID
143 WHERE run_detail_id = p_run_detail_id;
144
145 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
146 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_ENTRY_HEADERS_ASYNC', '<<Exit>>');
147 END IF;
148
149
150 COMMIT;
151 END;
152
153 FUNCTION retrieve_status_code ( p_consolidation_entity_id IN NUMBER,
154 p_category_code IN VARCHAR2,
155 p_run_name IN VARCHAR2) RETURN VARCHAR2
156
157 IS
158 l_row_count NUMBER(15);
159 l_warning_row_count NUMBER(15);
160 l_status_code VARCHAR2(30);
161
162 BEGIN
163
164 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
165 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RETRIEVE_STATUS_CODE.begin', '<<Enter>>');
166 END IF;
167
168 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
169 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RETRIEVE_STATUS_CODE', 'Consolidation Entity Id : ' || p_consolidation_entity_id);
170 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RETRIEVE_STATUS_CODE', 'Run Name : ' || p_run_name);
171 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RETRIEVE_STATUS_CODE', 'Category Code : ' || p_category_code);
172 END IF;
173
174 SELECT count(request_error_code)
175 INTO l_row_count
176 FROM gcs_cons_eng_run_dtls
177 WHERE run_name = p_run_name
178 AND consolidation_entity_id = p_consolidation_entity_id
179 AND child_entity_id IS NOT NULL
180 AND category_code = p_category_code;
181
182 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
183 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_CATEGORY_STATUS', 'Number of rows : ' || l_row_count);
184 END IF;
185
186 IF (l_row_count = 0) THEN
187 l_status_code := 'NOT_APPLICABLE';
188 ELSE
189 SELECT count(request_error_code)
190 INTO l_warning_row_count
191 FROM gcs_cons_eng_run_dtls
192 WHERE run_name = p_run_name
193 AND consolidation_entity_id = p_consolidation_entity_id
194 AND child_entity_id IS NOT NULL
195 AND category_code = p_category_code
196 AND NVL(request_error_code,'X') NOT IN ('COMPLETED','NOT_APPLICABLE');
197
198 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
199 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_CATEGORY_STATUS', 'Warning Row Count : ' || l_warning_row_count);
200 END IF;
201
202 IF (l_warning_row_count <> 0) THEN
203 l_status_code := 'WARNING';
204 ELSE
205 l_status_code := 'COMPLETED';
206 END IF;
207 END IF;
208
209 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
210 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_CATEGORY_STATUS', 'Status Code : ' || l_status_code);
211 END IF;
212 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
213 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RETRIEVE_STATUS_CODE.end', '<<Exit>>');
214 END IF;
215 return(l_status_code);
216
217 END;
218
219 PROCEDURE update_category_status( p_run_name IN VARCHAR2,
220 p_consolidation_entity_id IN NUMBER,
221 p_category_code IN VARCHAR2,
222 p_status IN VARCHAR2)
223 IS PRAGMA AUTONOMOUS_TRANSACTION;
224
225 l_request_error_code VARCHAR2(30);
226 l_row_count NUMBER(15);
227 l_warning_row_count NUMBER(15);
228 l_status_code VARCHAR2(30);
229 l_category_count NUMBER(15);
230 l_category_code VARCHAR2(30);
231
232 BEGIN
233 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
234 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_CATEGORY_STATUS', '<<Enter>>');
235 END IF;
236
237 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
238 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_CATEGORY_STATUS', 'Run Name : ' || p_run_name);
239 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_CATEGORY_STATUS', 'Consolidation Entity : ' || p_consolidation_entity_id);
240 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_CATEGORY_STATUS', 'Category Code : ' || p_category_code);
241 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_CATEGORY_STATUS', 'Status : ' || p_status);
242 END IF;
243
244 IF (p_category_code = 'DATAPREPARATION') THEN
245 FOR l_category_count IN -1..gcs_categories_pkg.g_oper_category_info.COUNT LOOP
246 IF (l_category_count = -1) THEN
247 l_category_code := 'DATAPREPARATION';
248 ELSIF (l_category_count = 0) THEN
249 l_category_code := 'TRANSLATION';
250 ELSE
251 l_category_code := gcs_categories_pkg.g_oper_category_info(l_category_count).category_code;
252 END IF;
253
254 l_status_code := retrieve_status_code ( p_consolidation_entity_id => p_consolidation_entity_id,
255 p_category_code => l_category_code,
256 p_run_name => p_run_name);
257
258 UPDATE gcs_cons_eng_run_dtls
259 SET request_error_code = l_status_code,
260 last_update_date = sysdate,
261 last_updated_by = FND_GLOBAL.LOGIN_ID
262 WHERE run_name = p_run_name
263 AND category_code = l_category_code
264 AND consolidation_entity_id = p_consolidation_entity_id
265 AND child_entity_id IS NULL;
266 END LOOP;
267 ELSE
268 IF (p_status = 'COMPLETED' AND p_category_code <> 'AGGREGATION') THEN
269 l_status_code := retrieve_status_code ( p_consolidation_entity_id => p_consolidation_entity_id,
270 p_category_code => p_category_code,
271 p_run_name => p_run_name);
272 ELSE
273 l_status_code := p_status;
274 END IF;
275
276 UPDATE gcs_cons_eng_run_dtls
277 SET request_error_code = l_status_code,
278 last_update_date = sysdate,
279 last_updated_by = FND_GLOBAL.LOGIN_ID
280 WHERE run_name = p_run_name
281 AND category_code = p_category_code
282 AND consolidation_entity_id = p_consolidation_entity_id
283 AND child_entity_id IS NULL;
284 END IF;
285 COMMIT;
286
287 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
288 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_CATEGORY_STATUS', '<<Exit>>');
289 END IF;
290
291 END;
292
293 PROCEDURE update_detail_requests( p_run_detail_id IN NUMBER,
294 p_run_process_code IN VARCHAR2
295 )
296
297 IS
298
299 BEGIN
300
301 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
302 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_DETAIL_REQUESTS', '<<Enter>>');
303 END IF;
304
305 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
306 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_DETAIL_REQUESTS', '<<Exit>>');
307 END IF;
308
309 END;
310
311 PROCEDURE copy_prior_run_dtls( p_prior_run_name IN VARCHAR2,
312 p_current_run_name IN VARCHAR2,
313 p_itemtype IN VARCHAR2,
314 p_entity_id IN NUMBER)
315
316 IS PRAGMA AUTONOMOUS_TRANSACTION;
317
318 BEGIN
319
320 IF (p_itemtype = 'GCSOPRWF') THEN
321 INSERT INTO gcs_cons_eng_run_dtls
322 (
323 run_detail_id,
324 run_name,
325 consolidation_entity_id,
326 category_code,
327 child_entity_id,
328 contra_child_entity_id,
329 rule_id,
330 entry_id,
331 stat_entry_id,
332 request_error_code,
333 bp_request_error_code,
334 cons_relationship_id,
335 last_update_date,
336 last_updated_by,
337 creation_date,
338 created_by,
339 last_update_login
340 )
341 SELECT gcs_cons_eng_run_dtls_s.nextval,
342 p_current_run_name,
343 consolidation_entity_id,
344 category_code,
345 child_entity_id,
346 contra_child_entity_id,
347 rule_id,
348 entry_id,
349 stat_entry_id,
350 request_error_code,
351 bp_request_error_code,
352 cons_relationship_id,
353 sysdate,
354 FND_GLOBAL.USER_ID,
355 sysdate,
356 FND_GLOBAL.USER_ID,
357 FND_GLOBAL.LOGIN_ID
358 FROM gcs_cons_eng_run_dtls
359 WHERE run_name = p_prior_run_name
360 AND child_entity_id = p_entity_id
361 AND category_code IN (select category_code
362 from gcs_categories_b
363 where target_entity_code = 'CHILD'
364 and category_type_code <> 'PROCESS');
365 ELSE
366
367 DELETE FROM gcs_cons_eng_run_dtls
368 WHERE run_name = p_current_run_name
369 AND consolidation_entity_id = p_entity_id
370 AND category_code IN (select category_code
371 from gcs_categories_b
372 where target_entity_code IN ('ELIMINATION', 'PARENT'))
373 AND child_entity_id IS NULL;
374
375 INSERT INTO gcs_cons_eng_run_dtls
376 (
377 run_detail_id,
378 run_name,
379 consolidation_entity_id,
380 category_code,
381 child_entity_id,
382 contra_child_entity_id,
383 rule_id,
384 entry_id,
385 stat_entry_id,
386 request_error_code,
387 bp_request_error_code,
388 pre_prop_entry_id,
389 pre_prop_stat_entry_id,
390 cons_relationship_id,
391 last_update_date,
392 last_updated_by,
393 creation_date,
394 created_by,
395 last_update_login
396 )
397 SELECT gcs_cons_eng_run_dtls_s.nextval,
398 p_current_run_name,
399 consolidation_entity_id,
400 category_code,
401 child_entity_id,
402 contra_child_entity_id,
403 rule_id,
404 entry_id,
405 stat_entry_id,
406 request_error_code,
407 bp_request_error_code,
408 pre_prop_entry_id,
409 pre_prop_stat_entry_id,
410 cons_relationship_id,
411 sysdate,
412 FND_GLOBAL.USER_ID,
413 sysdate,
414 FND_GLOBAL.USER_ID,
415 FND_GLOBAL.LOGIN_ID
416 FROM gcs_cons_eng_run_dtls
417 WHERE run_name = p_prior_run_name
418 AND consolidation_entity_id = p_entity_id
419 AND category_code IN (select category_code
420 from gcs_categories_b
421 where target_entity_code IN ('ELIMINATION', 'PARENT'));
422 END IF;
423
424 COMMIT;
425
426 END;
427 END GCS_CONS_ENG_RUN_DTLS_PKG;