[Home] [Help]
PACKAGE BODY: APPS.GCS_DATASUB_UTILITY_PKG
Source
1 PACKAGE BODY GCS_DATASUB_UTILITY_PKG AS
2
3 --API Name
4 g_api VARCHAR2(50) := 'gcs.plsql.GCS_DATASUB_UTILITY_PKG';
5
6 -- Action types for writing module information to the log file. Used for
7 -- the procedure log_file_module_write.
8 g_module_enter VARCHAR2(2) := '>>';
9 g_module_success VARCHAR2(2) := '<<';
10 g_module_failure VARCHAR2(2) := '<x';
11
12 -- Beginning of private procedures
13
14 PROCEDURE update_ytd_balances (p_load_id IN NUMBER,
15 p_source_system_code IN NUMBER,
16 p_dataset_code IN NUMBER,
17 p_cal_period_id IN NUMBER,
18 p_ledger_id IN NUMBER,
19 p_currency_type IN VARCHAR2,
20 p_currency_code IN VARCHAR2)
21 IS PRAGMA AUTONOMOUS_TRANSACTION;
22 BEGIN
23
24 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
25 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_YTD_BALANCES.begin', '<<Enter>>' );
26 END IF;
27
28 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
29 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_YTD_BALANCES',
30 'Load Id : ' || p_load_id );
31 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_YTD_BALANCES',
32 'Source System : ' || p_source_system_code );
33 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_YTD_BALANCES',
34 'Dataset Code : ' || p_dataset_code );
35 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_YTD_BALANCES',
36 'Cal Period Id : ' || p_cal_period_id );
37 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_YTD_BALANCES',
38 'Ledger Id : ' || p_ledger_id );
39 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_YTD_BALANCES',
40 'Currency Type : ' || p_currency_type );
41 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_YTD_BALANCES',
42 'Currency Code : ' || p_currency_code );
43
44 END IF;
45
46 UPDATE gcs_bal_interface_t gbit
47 SET (ytd_debit_balance_e,
48 ytd_credit_balance_e,
49 ytd_balance_e,
50 ytd_balance_f) = (
51 SELECT fb.ytd_debit_balance_e + NVL(gbit.ptd_debit_balance_e,0) ,
52 fb.ytd_credit_balance_e + NVL(gbit.ptd_credit_balance_e,0) ,
53 fb.ytd_balance_e + NVL(gbit.ptd_debit_balance_e, 0) -
54 NVL(gbit.ptd_credit_balance_e, 0),
55 fb.ytd_balance_f + NVL(gbit.ptd_debit_balance_f, 0) -
56 NVL(gbit.ptd_credit_balance_f, 0)
57 FROM fem_balances fb
58 ,fem_cctr_orgs_b fcob
59 ,fem_ln_items_b flib
60 ,fem_cctr_orgs_b fcib
61 WHERE fb.ledger_id = p_ledger_id
62 AND fb.cal_period_id = p_cal_period_id
63 AND fb.dataset_code = p_dataset_code
64 AND fb.source_system_code = p_source_system_code
65 AND fb.currency_type_code = p_currency_type
66 AND fb.currency_code = DECODE(p_currency_code, NULL,
67 gbit.currency_code,
68 p_currency_code)
69 AND fb.company_cost_center_org_id = fcob.company_cost_center_org_id
70 AND fcob.cctr_org_display_code = gbit.cctr_org_display_code
71 AND fb.line_item_id = flib.line_item_id
72 AND flib.line_item_display_code = gbit.line_item_display_code
73 AND fb.intercompany_id = fcib.company_cost_center_org_id
74 AND fcib.cctr_org_display_code = gbit.intercompany_display_code
75 )
76 WHERE load_id = p_load_id;
77
78 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
79 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_YTD_BALANCES.end', '<<Exit>>');
80 END IF;
81
82 COMMIT;
83 END update_ytd_balances;
84 --
85 --
86 PROCEDURE update_ptd_balances (p_load_id IN NUMBER,
87 p_source_system_code IN NUMBER,
88 p_dataset_code IN NUMBER,
89 p_cal_period_id IN NUMBER,
90 p_ledger_id IN NUMBER,
91 p_currency_type IN VARCHAR2,
92 p_currency_code IN VARCHAR2)
93 IS PRAGMA AUTONOMOUS_TRANSACTION;
94 BEGIN
95
96 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
97 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_PTD_BALANCES.begin',
98 '<<Enter>>' );
99 END IF;
100
101 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
102 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCES',
103 'Load Id : ' || p_load_id );
104 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCES',
105 'Source System : ' || p_source_system_code );
106 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCES',
107 'Dataset Code : ' || p_dataset_code );
108 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCES',
109 'Cal Period Id : ' || p_cal_period_id );
110 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCES',
111 'Ledger Id : ' || p_ledger_id );
112 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCES',
113 'Currency Type : ' || p_currency_type );
114 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCES',
115 'Currency Code : ' || p_currency_code );
116
117 END IF;
118
119 UPDATE gcs_bal_interface_t gbit
120 SET (ptd_debit_balance_e,
121 ptd_credit_balance_e,
122 ptd_balance_e,
123 ptd_balance_f) = (
124 SELECT gbit.ytd_debit_balance_e - NVL(fb.ytd_debit_balance_e,0) ,
125 gbit.ytd_credit_balance_e - NVL(fb.ytd_credit_balance_e,0) ,
126 gbit.ytd_balance_e - NVL(fb.ytd_balance_e, 0),
127 gbit.ytd_balance_f - NVL(fb.ytd_balance_f, 0)
128 FROM fem_balances fb
129 ,fem_cctr_orgs_b fcob
130 ,fem_ln_items_b flib
131 ,fem_cctr_orgs_b fcib
132 WHERE fb.ledger_id = p_ledger_id
133 AND fb.cal_period_id = p_cal_period_id
134 AND fb.dataset_code = p_dataset_code
135 AND fb.source_system_code = p_source_system_code
136 AND fb.currency_type_code = p_currency_type
137 AND fb.currency_code = DECODE(p_currency_code, NULL,
138 gbit.currency_code,
139 p_currency_code)
140 AND fb.company_cost_center_org_id = fcob.company_cost_center_org_id
141 AND fcob.cctr_org_display_code = gbit.cctr_org_display_code
142 AND fb.line_item_id = flib.line_item_id
143 AND flib.line_item_display_code = gbit.line_item_display_code
144 AND fb.intercompany_id = fcib.company_cost_center_org_id
145 AND fcib.cctr_org_display_code = gbit.intercompany_display_code
146 )
147 WHERE gbit.load_id = p_load_id;
148
149 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
150 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_PTD_BALANCES.end', '<<Exit>>');
151 END IF;
152
153 COMMIT;
154 END update_ptd_balances;
155 --
156 PROCEDURE update_ptd_balance_sheet ( p_load_id IN NUMBER,
157 p_source_system_code IN NUMBER,
158 p_dataset_code IN NUMBER,
159 p_cal_period_id IN NUMBER,
160 p_ledger_id IN NUMBER,
161 p_currency_type IN VARCHAR2,
162 p_currency_code IN VARCHAR2)
163 IS PRAGMA AUTONOMOUS_TRANSACTION;
164 l_line_item_vs_id NUMBER;
165 l_ledger_vs_combo_attr NUMBER(15) :=
166 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO').attribute_id;
167 l_ledger_vs_combo_version NUMBER(15) :=
168 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO').version_id;
169 l_line_item_type_attr NUMBER(15) :=
170 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').attribute_id;
171 l_line_item_type_version NUMBER(15) :=
172 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').version_id;
173 l_acct_type_attr NUMBER(15) :=
174 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').attribute_id;
175 l_acct_type_version NUMBER(15) :=
176 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').version_id;
177
178 BEGIN
179
180 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
181 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_PTD_BALANCE_SHEET.begin',
182 '<<Enter>>' );
183 END IF;
184
185 SELECT fgvcd.value_set_id
186 INTO l_line_item_vs_id
187 FROM fem_ledgers_attr fla,
188 fem_global_vs_combo_defs fgvcd
189 WHERE fla.ledger_id = p_ledger_id
190 AND fgvcd.global_vs_combo_id = fla.dim_attribute_numeric_member
191 AND fla.attribute_id = l_ledger_vs_combo_attr
192 AND fla.version_id = l_ledger_vs_combo_version
193 AND fgvcd.dimension_id = 14;
194
195 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
196 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCE_SHEET',
197 'Load Id : ' || p_load_id );
198 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCE_SHEET',
199 'Source System : ' || p_source_system_code );
200 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCE_SHEET',
201 'Dataset Code : ' || p_dataset_code );
202 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCE_SHEET',
203 'Cal Period Id : ' || p_cal_period_id );
204 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCE_SHEET',
205 'Ledger Id : ' || p_ledger_id );
206 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCE_SHEET',
207 'Currency Type : ' || p_currency_type );
208 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PTD_BALANCE_SHEET',
209 'Currency Code : ' || p_currency_code );
210
211 END IF;
212
213 UPDATE gcs_bal_interface_t gbit
214 SET (ptd_debit_balance_e,
215 ptd_credit_balance_e,
216 ptd_balance_e,
217 ptd_balance_f) = (
218 SELECT gbit.ytd_debit_balance_e - NVL(fb.ytd_debit_balance_e,0) ,
219 gbit.ytd_credit_balance_e - NVL(fb.ytd_credit_balance_e,0) ,
220 gbit.ytd_balance_e - NVL(fb.ytd_balance_e, 0),
221 gbit.ytd_balance_f - NVL(fb.ytd_balance_f, 0)
222 FROM fem_balances fb
223 ,fem_cctr_orgs_b fcob
224 ,fem_ln_items_b flib
225 ,fem_cctr_orgs_b fcib
226 WHERE fb.ledger_id = p_ledger_id
227 AND fb.cal_period_id = p_cal_period_id
228 AND fb.dataset_code = p_dataset_code
229 AND fb.source_system_code = p_source_system_code
230 AND fb.currency_type_code = p_currency_type
231 AND fb.currency_code = DECODE(p_currency_code, NULL,
232 gbit.currency_code,
233 p_currency_code)
234 AND fb.company_cost_center_org_id = fcob.company_cost_center_org_id
235 AND fcob.cctr_org_display_code = gbit.cctr_org_display_code
236 AND fb.line_item_id = flib.line_item_id
237 AND flib.line_item_display_code = gbit.line_item_display_code
238 AND fb.intercompany_id = fcib.company_cost_center_org_id
239 AND fcib.cctr_org_display_code = gbit.intercompany_display_code
240 )
241 WHERE gbit.load_id = p_load_id
242 AND EXISTS (SELECT 'X'
243 FROM fem_ln_items_b flib,
244 fem_ln_items_attr flia,
245 fem_ext_acct_types_attr fea_attr
246 WHERE gbit.line_item_display_code = flib.line_item_display_code
247 AND flib.line_item_id = flia.line_item_id
248 AND flib.value_set_id = l_line_item_vs_id
249 AND flib.value_set_id = flia.value_set_id
250 AND flia.attribute_id = l_line_item_type_attr
251 AND flia.version_id = l_line_item_type_version
252 AND flia.dim_attribute_varchar_member = fea_attr.ext_account_type_code
253 AND fea_attr.attribute_id = l_acct_type_attr
254 AND fea_attr.version_id = l_acct_type_version
255 AND fea_attr.dim_attribute_varchar_member IN ('EQUITY',
256 'ASSET',
257 'LIABILITY'
258 )
259 );
260
261 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
262 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_PTD_BALANCE_SHEET.end', '<<Exit>>');
263 END IF;
264
265 COMMIT;
266 END update_ptd_balance_sheet;
267 --
268
269 PROCEDURE validate_dimension_members(p_load_id IN NUMBER )
270 IS PRAGMA AUTONOMOUS_TRANSACTION;
271 TYPE dim_vs_info_rec_type IS RECORD(
272 vs_id NUMBER ,
273 dim_display_code VARCHAR2(50),
274 dim_id_col VARCHAR2(50) ,
275 dim_name VARCHAR2(50) );
276 TYPE t_dim_vs_info IS TABLE OF dim_vs_info_rec_type;
277 l_dim_vs_info t_dim_vs_info;
278 l_ledger_id NUMBER(10);
279 l_dim_id_col VARCHAR2(50);
280 l_invalid_err_msg VARCHAR2(2000);
281 l_null_err_msg VARCHAR2(2000);
282 BEGIN
283
284 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
285 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.VALIDATE_DIMENSION_MEMBERS.begin',
286 '<<Enter>>' );
287 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.VALIDATE_DIMENSION_MEMBERS',
288 'Load Id : ' || p_load_id );
289 END IF;
290
291
292 UPDATE
293 gcs_bal_interface_t
294 SET error_message_code = NULL
295 WHERE load_id = p_load_id;
296
297 SELECT fea.dim_attribute_numeric_member
298 INTO l_ledger_id
299 FROM fem_entities_attr fea,
300 gcs_data_sub_dtls gdsd
301 WHERE gdsd.load_id = p_load_id
302 AND fea.entity_id = gdsd.entity_id
303 AND fea.attribute_id = gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID').attribute_id
304 AND fea.version_id = gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID').version_id;
305
306
307 SELECT fgvcd.value_set_id,
308 fxd.member_display_code_col,
309 fxd.member_col,
310 fdt.dimension_name
311 BULK COLLECT
312 INTO l_dim_vs_info
313 FROM fem_global_vs_combo_defs fgvcd,
314 fem_ledgers_attr fla,
315 fem_xdim_dimensions fxd,
316 fem_dimensions_tl fdt
317 WHERE gcs_utility_pkg.get_fem_dim_required(fxd.MEMBER_COL) = 'Y'
318 AND global_vs_combo_id = fla.dim_attribute_numeric_member
319 AND fla.ledger_id = l_ledger_id
320 AND fla.attribute_id = gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO').attribute_id
321 AND fla.version_id = gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO').version_id
322 AND fgvcd.dimension_id = fxd.dimension_id
323 AND fxd.member_col IN ('COMPANY_COST_CENTER_ORG_ID','FINANCIAL_ELEM_ID','PRODUCT_ID','NATURAL_ACCOUNT_ID','CHANNEL_ID',
324 'LINE_ITEM_ID','PROJECT_ID','CUSTOMER_ID','TASK_ID','USER_DIM1_ID','USER_DIM10_ID',
325 'USER_DIM2_ID','USER_DIM3_ID', 'USER_DIM4_ID','USER_DIM5_ID',
326 'USER_DIM6_ID','USER_DIM7_ID','USER_DIM8_ID','USER_DIM9_ID')
327 AND fdt.dimension_id = fxd.dimension_id
328 AND fdt.language = userenv('LANG');
329
330
331 IF l_dim_vs_info.FIRST IS NOT NULL THEN
332 FOR l_counter IN l_dim_vs_info.FIRST..l_dim_vs_info.LAST LOOP
333 FND_MESSAGE.set_name( 'GCS', 'GCS_DS_DIM_INVALID_MSG' );
334 FND_MESSAGE.set_token('DIM_NAME', l_dim_vs_info(l_counter).dim_name) ;
335 l_invalid_err_msg := FND_MESSAGE.get ;
336
337 FND_MESSAGE.set_name( 'GCS', 'GCS_DS_DIM_NULL_MSG' );
338 FND_MESSAGE.set_token('COLUMN_NAME', l_dim_vs_info(l_counter).dim_display_code) ;
339 l_null_err_msg := FND_MESSAGE.get ;
340 l_dim_id_col := l_dim_vs_info(l_counter).dim_id_col ;
341
342 IF ( l_dim_id_col = 'COMPANY_COST_CENTER_ORG_ID' ) THEN
343 UPDATE gcs_bal_interface_t gbit
344 SET error_message_code = error_message_code
345 || DECODE ( cctr_org_display_code, NULL , l_null_err_msg ,
346 l_invalid_err_msg || '(' || cctr_org_display_code || ').' )
347 WHERE load_id = p_load_id
348 AND NOT EXISTS (SELECT 'X'
349 FROM fem_cctr_orgs_b fcob
350 WHERE fcob.cctr_org_display_code = gbit.cctr_org_display_code
351 AND fcob.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
352 ELSIF ( l_dim_id_col = 'FINANCIAL_ELEM_ID' ) THEN
353 UPDATE gcs_bal_interface_t gbit
354 SET error_message_code = error_message_code
355 || DECODE ( financial_elem_display_code, NULL , l_null_err_msg ,
356 l_invalid_err_msg || '(' || financial_elem_display_code || ').' )
357 WHERE load_id = p_load_id
358 AND NOT EXISTS (SELECT 'X'
359 FROM fem_fin_elems_b ffeb
360 WHERE ffeb.financial_elem_display_code = gbit.financial_elem_display_code
361 AND ffeb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
362 ELSIF ( l_dim_id_col = 'LINE_ITEM_ID' ) THEN
363 UPDATE gcs_bal_interface_t gbit
364 SET error_message_code = error_message_code
365 || DECODE ( line_item_display_code, NULL , l_null_err_msg ,
366 l_invalid_err_msg || '(' || line_item_display_code || ').' )
367 WHERE load_id = p_load_id
368 AND NOT EXISTS (SELECT 'X'
369 FROM fem_ln_items_b flib
370 WHERE flib.line_item_display_code = gbit.line_item_display_code
371 AND flib.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
372 ELSIF ( l_dim_id_col = 'PRODUCT_ID') THEN
373 UPDATE gcs_bal_interface_t gbit
374 SET error_message_code = error_message_code
375 || DECODE ( product_display_code, NULL , l_null_err_msg ,
376 l_invalid_err_msg || '(' || product_display_code || ').' )
377 WHERE load_id = p_load_id
378 AND NOT EXISTS (SELECT 'X'
379 FROM fem_products_b fpb
380 WHERE fpb.product_display_code = gbit.product_display_code
381 AND fpb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
382 ELSIF ( l_dim_id_col = 'NATURAL_ACCOUNT_ID' ) THEN
383 UPDATE gcs_bal_interface_t gbit
384 SET error_message_code = error_message_code
385 || DECODE ( natural_account_display_code, NULL , l_null_err_msg ,
386 l_invalid_err_msg || '(' || natural_account_display_code || ').' )
387 WHERE load_id = p_load_id
388 AND NOT EXISTS (SELECT 'X'
389 FROM fem_nat_accts_b fnab
390 WHERE fnab.natural_account_display_code = gbit.natural_account_display_code
391 AND fnab.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
392 ELSIF ( l_dim_id_col = 'CHANNEL_ID') THEN
393 UPDATE gcs_bal_interface_t gbit
394 SET error_message_code = error_message_code
395 || DECODE ( channel_display_code, NULL , l_null_err_msg ,
396 l_invalid_err_msg || '(' || channel_display_code || ').' )
397 WHERE load_id = p_load_id
398 AND NOT EXISTS (SELECT 'X'
399 FROM fem_channels_b fcb
400 WHERE fcb.channel_display_code = gbit.channel_display_code
401 AND fcb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
402 ELSIF ( l_dim_id_col = 'PROJECT_ID') THEN
403 UPDATE gcs_bal_interface_t gbit
404 SET error_message_code = error_message_code
405 || DECODE ( project_display_code, NULL , l_null_err_msg ,
406 l_invalid_err_msg || '(' || project_display_code || ').' )
407 WHERE load_id = p_load_id
408 AND NOT EXISTS (SELECT 'X'
409 FROM fem_projects_b fpb
410 WHERE fpb.project_display_code = gbit.project_display_code
411 AND fpb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
412 ELSIF ( l_dim_id_col = 'CUSTOMER_ID' ) THEN
413 UPDATE gcs_bal_interface_t gbit
414 SET error_message_code = error_message_code
415 || DECODE ( customer_display_code, NULL , l_null_err_msg ,
416 l_invalid_err_msg || '(' || customer_display_code || ').' )
417 WHERE load_id = p_load_id
418 AND NOT EXISTS (SELECT 'X'
419 FROM fem_customers_b fcb
420 WHERE fcb.customer_display_code = gbit.customer_display_code
421 AND fcb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
422 ELSIF ( l_dim_id_col = 'TASK_ID') THEN
423 UPDATE gcs_bal_interface_t gbit
424 SET error_message_code = error_message_code
425 || DECODE ( task_display_code, NULL , l_null_err_msg ,
426 l_invalid_err_msg || '(' || task_display_code || ').' )
427 WHERE load_id = p_load_id
428 AND NOT EXISTS (SELECT 'X'
429 FROM fem_tasks_b ftb
430 WHERE ftb.task_display_code = gbit.task_display_code
431 AND ftb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
432 ELSIF ( l_dim_id_col = 'USER_DIM1_ID' ) THEN
433 UPDATE gcs_bal_interface_t gbit
434 SET error_message_code = error_message_code
435 || DECODE ( user_dim1_display_code, NULL , l_null_err_msg ,
436 l_invalid_err_msg || '(' || user_dim1_display_code || ').' )
437 WHERE load_id = p_load_id
438 AND NOT EXISTS (SELECT 'X'
439 FROM fem_user_dim1_b fub
440 WHERE fub.user_dim1_display_code = gbit.user_dim1_display_code
441 AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
442 ELSIF ( l_dim_id_col = 'USER_DIM2_ID' ) THEN
443 UPDATE gcs_bal_interface_t gbit
444 SET error_message_code = error_message_code
445 || DECODE ( user_dim2_display_code, NULL , l_null_err_msg ,
446 l_invalid_err_msg || '(' || user_dim2_display_code || ').' )
447 WHERE load_id = p_load_id
448 AND NOT EXISTS (SELECT 'X'
449 FROM fem_user_dim2_b fub
450 WHERE fub.user_dim2_display_code = gbit.user_dim2_display_code
451 AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
452 ELSIF ( l_dim_id_col = 'USER_DIM3_ID' ) THEN
453 UPDATE gcs_bal_interface_t gbit
454 SET error_message_code = error_message_code
455 || DECODE ( user_dim3_display_code, NULL , l_null_err_msg ,
456 l_invalid_err_msg || '(' || user_dim3_display_code || ').' )
457 WHERE load_id = p_load_id
458 AND NOT EXISTS (SELECT 'X'
459 FROM fem_user_dim3_b fub
460 WHERE fub.user_dim3_display_code = gbit.user_dim3_display_code
461 AND fub.value_set_id =l_dim_vs_info(l_counter).vs_id ) ;
462 ELSIF ( l_dim_id_col = 'USER_DIM4_ID' ) THEN
463 UPDATE gcs_bal_interface_t gbit
464 SET error_message_code = error_message_code
465 || DECODE ( user_dim4_display_code, NULL , l_null_err_msg ,
466 l_invalid_err_msg || '(' || user_dim4_display_code || ').' )
467 WHERE load_id = p_load_id
468 AND NOT EXISTS (SELECT 'X'
469 FROM fem_user_dim4_b fub
470 WHERE fub.user_dim4_display_code = gbit.user_dim4_display_code
471 AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
472 ELSIF ( l_dim_id_col = 'USER_DIM5_ID' ) THEN
473 UPDATE gcs_bal_interface_t gbit
474 SET error_message_code = error_message_code
475 || DECODE ( user_dim5_display_code, NULL , l_null_err_msg ,
476 l_invalid_err_msg || '(' || user_dim5_display_code || ').' )
477 WHERE load_id = p_load_id
478 AND NOT EXISTS (SELECT 'X'
479 FROM fem_user_dim5_b fub
480 WHERE fub.user_dim5_display_code = gbit.user_dim5_display_code
481 AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
482 ELSIF ( l_dim_id_col = 'USER_DIM6_ID' ) THEN
483 UPDATE gcs_bal_interface_t gbit
484 SET error_message_code = error_message_code
485 || DECODE ( user_dim6_display_code, NULL , l_null_err_msg ,
486 l_invalid_err_msg || '(' || user_dim6_display_code || ').' )
487 WHERE load_id = p_load_id
488 AND NOT EXISTS (SELECT 'X'
489 FROM fem_user_dim6_b fub
490 WHERE fub.user_dim6_display_code = gbit.user_dim6_display_code
491 AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
492 ELSIF ( l_dim_id_col = 'USER_DIM7_ID' ) THEN
493 UPDATE gcs_bal_interface_t gbit
494 SET error_message_code = error_message_code
495 || DECODE ( user_dim7_display_code, NULL , l_null_err_msg ,
496 l_invalid_err_msg || '(' || user_dim7_display_code || ').' )
497 WHERE load_id = p_load_id
498 AND NOT EXISTS (SELECT 'X'
499 FROM fem_user_dim7_b fub
500 WHERE fub.user_dim7_display_code = gbit.user_dim7_display_code
501 AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
502 ELSIF ( l_dim_id_col = 'USER_DIM8_ID' ) THEN
503 UPDATE gcs_bal_interface_t gbit
504 SET error_message_code = error_message_code
505 || DECODE ( user_dim8_display_code, NULL , l_null_err_msg ,
506 l_invalid_err_msg || '(' || user_dim8_display_code || ').' )
507 WHERE load_id = p_load_id
508 AND NOT EXISTS (SELECT 'X'
509 FROM fem_user_dim8_b fub
510 WHERE fub.user_dim8_display_code = gbit.user_dim8_display_code
511 AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
512 ELSIF ( l_dim_id_col = 'USER_DIM9_ID' ) THEN
513 UPDATE gcs_bal_interface_t gbit
514 SET error_message_code = error_message_code
515 || DECODE ( user_dim9_display_code, NULL , l_null_err_msg ,
516 l_invalid_err_msg || '(' || user_dim9_display_code || ').' )
517 WHERE load_id = p_load_id
518 AND NOT EXISTS (SELECT 'X'
519 FROM fem_user_dim9_b fub
520 WHERE fub.user_dim9_display_code = gbit.user_dim9_display_code
521 AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
522 ELSIF ( l_dim_id_col = 'USER_DIM10_ID' ) THEN
523 UPDATE gcs_bal_interface_t gbit
524 SET error_message_code = error_message_code
525 || DECODE ( user_dim10_display_code, NULL , l_null_err_msg ,
526 l_invalid_err_msg || '(' || user_dim10_display_code || ').' )
527 WHERE load_id = p_load_id
528 AND NOT EXISTS (SELECT 'X'
529 FROM fem_user_dim10_b fub
530 WHERE fub.user_dim10_display_code = gbit.user_dim10_display_code
531 AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ;
532 END IF;
533 END LOOP;
534 END IF;
535
536 COMMIT;
537
538 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
539 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.VALIDATE_DIMENSION_MEMBERS.end', '<<Exit>>');
540 END IF;
541
542 END validate_dimension_members;
543 --
544
545 END GCS_DATASUB_UTILITY_PKG;