[Home] [Help]
PACKAGE BODY: APPS.GCS_DATASUB_DYNAMIC_PKG
Source
1 PACKAGE BODY GCS_DATASUB_DYNAMIC_PKG as
2 /* $Header: gcs_datasub_dynb.pls 120.6 2006/06/28 05:08:30 vkosuri noship $ */
3
4 --Global Variables
5 g_api VARCHAR2(50) := 'gcs.plsql.GCS_DATASUB_DYNAMIC_PKG';
6 g_nl VARCHAR2(1) := '''';
7 --
8 -- Procedure
9 -- Write_To_Log
10 -- Purpose
11 -- Write the text given to the log in 3500 character increments
12 -- this happened. Write it to the log repository.
13 -- Arguments
14 -- p_module Name of the module
15 -- p_level Logging level
16 -- p_text Text to write
17 -- Example
18 --
19 -- Notes
20 --
21 PROCEDURE write_to_log
22 (p_module VARCHAR2,
23 p_level NUMBER,
24 p_text VARCHAR2)
25 IS
26 api_module_concat VARCHAR2(200);
27 text_with_date VARCHAR2(32767);
28 text_with_date_len NUMBER;
29 curr_index NUMBER;
30 BEGIN
31 -- Only print if the log level is set at the appropriate level
32 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
33 api_module_concat := g_api || '.' || p_module;
34 text_with_date := to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||g_nl||p_text;
35 text_with_date_len := length(text_with_date);
36 curr_index := 1;
37 WHILE curr_index <= text_with_date_len LOOP
38 fnd_log.string(p_level, api_module_concat,
39 substr(text_with_date, curr_index, 3500));
40 curr_index := curr_index + 3500;
41 END LOOP;
42 END IF;
43 END write_to_log;
44
45
46
47 PROCEDURE add_clause_to_list (p_dimension_required IN VARCHAR2,
48 p_id_column_name IN VARCHAR2,
49 p_disp_code_col_name IN VARCHAR2,
50 p_table_alias IN VARCHAR2,
51 p_rownum IN OUT NOCOPY NUMBER) IS
52
53 BEGIN
54 IF (p_dimension_required = 'Y') THEN
55
56
57 ad_ddl.build_statement(' AND fb.'||p_id_column_name||' = '||p_table_alias|| '.' || p_id_column_name, p_rownum); p_rownum:=p_rownum+1;
58 ad_ddl.build_statement(' AND gbit.' || p_disp_code_col_name || ' = ' || p_table_alias || '.' || p_disp_code_col_name, p_rownum); p_rownum:=p_rownum+1;
59 END IF;
60 END add_clause_to_list;
61
62 PROCEDURE build_where_clause_list (p_rownum IN OUT NOCOPY NUMBER) IS
63
64 l_dim_info gcs_utility_pkg.t_hash_gcs_dimension_info :=
65 gcs_utility_pkg.g_gcs_dimension_info;
66
67 BEGIN
68 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'), 'FINANCIAL_ELEM_ID',
69 'FINANCIAL_ELEM_DISPLAY_CODE',
70 'ffeb',
71 p_rownum);
72 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'), 'PRODUCT_ID',
73 'PRODUCT_DISPLAY_CODE',
74 'fpb',
75 p_rownum);
76 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'), 'NATURAL_ACCOUNT_ID',
77 'NATURAL_ACCOUNT_DISPLAY_CODE',
78 'fnab', p_rownum);
79 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'), 'CHANNEL_ID',
80 'CHANNEL_DISPLAY_CODE',
81 'fchb', p_rownum);
82 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'), 'PROJECT_ID',
83 'PROJECT_DISPLAY_CODE',
84 'fpjb', p_rownum);
85 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'), 'CUSTOMER_ID',
86 'CUSTOMER_DISPLAY_CODE',
87 'fcb', p_rownum);
88 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'), 'TASK_ID',
89 'TASK_DISPLAY_CODE',
90 'ftb', p_rownum);
91 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
92 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'), l_dim_info('USER_DIM1_ID').dim_member_col,
93 l_dim_info('USER_DIM1_ID').dim_member_display_code,
94 'fud1', p_rownum);
95 END IF;
96
97 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
98 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'), l_dim_info('USER_DIM2_ID').dim_member_col,
99 l_dim_info('USER_DIM2_ID').dim_member_display_code,
100 'fud2', p_rownum);
101 END IF;
102
103 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
104 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'), l_dim_info('USER_DIM3_ID').dim_member_col,
105 l_dim_info('USER_DIM3_ID').dim_member_display_code,
106 'fud3', p_rownum);
107 END IF;
108
109 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
110 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'), l_dim_info('USER_DIM4_ID').dim_member_col,
111 l_dim_info('USER_DIM4_ID').dim_member_display_code,
112 'fud4', p_rownum);
113 END IF;
114 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
115 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'), l_dim_info('USER_DIM5_ID').dim_member_col,
116 l_dim_info('USER_DIM5_ID').dim_member_display_code,
117 'fud5', p_rownum);
118 END IF;
119 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
120 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'), l_dim_info('USER_DIM6_ID').dim_member_col,
121 l_dim_info('USER_DIM6_ID').dim_member_display_code,
122 'fud6', p_rownum);
123 END IF;
124 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
125 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'), l_dim_info('USER_DIM7_ID').dim_member_col,
126 l_dim_info('USER_DIM7_ID').dim_member_display_code,
127 'fud7', p_rownum);
128 END IF;
129 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
130 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'), l_dim_info('USER_DIM8_ID').dim_member_col,
131 l_dim_info('USER_DIM8_ID').dim_member_display_code,
132 'fud8', p_rownum);
133 END IF;
134 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
135 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'), l_dim_info('USER_DIM9_ID').dim_member_col,
136 l_dim_info('USER_DIM9_ID').dim_member_display_code,
137 'fud9', p_rownum);
138 END IF;
139 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
140 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),l_dim_info('USER_DIM10_ID').dim_member_col,
141 l_dim_info('USER_DIM10_ID').dim_member_display_code,
142 'fud10', p_rownum);
143 END IF;
144 END build_where_clause_list;
145
146 PROCEDURE add_table_to_list (p_dimension_required IN VARCHAR2,
147 p_table_name IN VARCHAR2,
148 p_table_alias IN VARCHAR2,
149 p_rownum IN OUT NOCOPY NUMBER) IS
150
151 BEGIN
152 IF (p_dimension_required = 'Y') THEN
153 ad_ddl.build_statement(' ,' || p_table_name || ' ' || p_table_alias , p_rownum); p_rownum:=p_rownum+1;
154 END IF;
155 END add_table_to_list;
156
157 PROCEDURE build_table_list (p_rownum IN OUT NOCOPY NUMBER) IS
158
159 l_dim_info gcs_utility_pkg.t_hash_gcs_dimension_info :=
160 gcs_utility_pkg.g_gcs_dimension_info;
161
162 BEGIN
163 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'), 'fem_fin_elems_b', 'ffeb', p_rownum);
164 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'), 'fem_products_b', 'fpb', p_rownum);
165 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'), 'fem_nat_accts_b', 'fnab', p_rownum);
166 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'), 'fem_channels_b', 'fchb', p_rownum);
167 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'), 'fem_projects_b', 'fpjb', p_rownum);
168 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'), 'fem_customers_b', 'fcb', p_rownum);
169 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'), 'fem_tasks_b', 'ftb', p_rownum);
170 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
171 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'), l_dim_info('USER_DIM1_ID').dim_b_table_name,
172 'fud1', p_rownum);
173 END IF;
174 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
175 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'), l_dim_info('USER_DIM2_ID').dim_b_table_name,
176 'fud2', p_rownum);
177 END IF;
178 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
179 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'), l_dim_info('USER_DIM3_ID').dim_b_table_name,
180 'fud3', p_rownum);
181 END IF;
182 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
183 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'), l_dim_info('USER_DIM4_ID').dim_b_table_name,
184 'fud4', p_rownum);
185 END IF;
186 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
187 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'), l_dim_info('USER_DIM5_ID').dim_b_table_name,
188 'fud5', p_rownum);
189 END IF;
190 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
191 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'), l_dim_info('USER_DIM6_ID').dim_b_table_name,
192 'fud6', p_rownum);
193 END IF;
194 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
195 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'), l_dim_info('USER_DIM7_ID').dim_b_table_name,
196 'fud7', p_rownum);
197 END IF;
198 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
199 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'), l_dim_info('USER_DIM8_ID').dim_b_table_name,
200 'fud8', p_rownum);
201 END IF;
202 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
203 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'), l_dim_info('USER_DIM9_ID').dim_b_table_name,
204 'fud9', p_rownum);
205 END IF;
206 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
207 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),l_dim_info('USER_DIM10_ID').dim_b_table_name,
208 'fud10', p_rownum);
209 END IF;
210 END;
211
212 PROCEDURE create_datasub_utility_pkg (p_retcode NUMBER,
213 p_errbuf VARCHAR2) IS
214
215 r NUMBER(15) := 1;
216 comp_err VARCHAR2(200) := NULL;
217
218 BEGIN
219
220 ad_ddl.build_statement('CREATE OR REPLACE PACKAGE BODY GCS_DATASUB_UTILITY_PKG AS', r); r := r+1;
221 ad_ddl.build_statement(' ', r); r := r+1;
222 ad_ddl.build_statement('--API Name', r); r := r+1;
223 ad_ddl.build_statement(' g_api VARCHAR2(50) := ''gcs.plsql.GCS_DATASUB_UTILITY_PKG'';', r); r := r+1;
224 ad_ddl.build_statement(' ', r); r := r+1;
225 ad_ddl.build_statement(' -- Action types for writing module information to the log file. Used for', r); r:=r+1;
226 ad_ddl.build_statement(' -- the procedure log_file_module_write.', r); r:=r+1;
227 ad_ddl.build_statement(' g_module_enter VARCHAR2(2) := ''>>'';', r); r:=r+1;
228 ad_ddl.build_statement(' g_module_success VARCHAR2(2) := ''<<'';', r); r:=r+1;
229 ad_ddl.build_statement(' g_module_failure VARCHAR2(2) := ''<x'';', r); r:=r+1;
230 ad_ddl.build_statement(' ', r); r:=r+1;
231 ad_ddl.build_statement('-- Beginning of private procedures ', r); r:=r+1;
232 ad_ddl.build_statement(' ', r); r:=r+1;
233 ad_ddl.build_statement(' PROCEDURE update_ytd_balances (p_load_id IN NUMBER, ' , r); r:=r+1;
234 ad_ddl.build_statement(' p_source_system_code IN NUMBER, ' , r); r:=r+1;
235 ad_ddl.build_statement(' p_dataset_code IN NUMBER, ' , r); r:=r+1;
239 ad_ddl.build_statement(' p_currency_code IN VARCHAR2) ' , r); r:=r+1;
236 ad_ddl.build_statement(' p_cal_period_id IN NUMBER, ' , r); r:=r+1;
237 ad_ddl.build_statement(' p_ledger_id IN NUMBER, ' , r); r:=r+1;
238 ad_ddl.build_statement(' p_currency_type IN VARCHAR2, ' , r); r:=r+1;
240 ad_ddl.build_statement(' IS PRAGMA AUTONOMOUS_TRANSACTION; ', r); r:=r+1;
241 ad_ddl.build_statement(' BEGIN ', r); r:=r+1;
242 ad_ddl.build_statement(' ', r); r:=r+1;
243 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN ', r); r:=r+1;
244 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.UPDATE_YTD_BALANCES.begin'', ''<<Enter>>'' ); ', r); r:=r+1;
245 ad_ddl.build_statement(' END IF; ', r); r:=r+1;
246 ad_ddl.build_statement(' ', r); r:=r+1;
247 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN ', r); r:=r+1;
248 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_YTD_BALANCES'',
249 ''Load Id : '' || p_load_id );', r); r:=r+1;
250 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_YTD_BALANCES'',
251 ''Source System : '' || p_source_system_code );', r); r:=r+1;
252 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_YTD_BALANCES'',
253 ''Dataset Code : '' || p_dataset_code );', r); r:=r+1;
254 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_YTD_BALANCES'',
255 ''Cal Period Id : '' || p_cal_period_id );', r); r:=r+1;
256 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_YTD_BALANCES'',
257 ''Ledger Id : '' || p_ledger_id );', r); r:=r+1;
258 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_YTD_BALANCES'',
259 ''Currency Type : '' || p_currency_type );', r); r:=r+1;
260 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_YTD_BALANCES'',
261 ''Currency Code : '' || p_currency_code );', r); r:=r+1;
262 ad_ddl.build_statement(' ', r); r:=r+1;
263 ad_ddl.build_statement(' END IF;', r); r:=r+1;
264 ad_ddl.build_statement(' ', r); r:=r+1;
265 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
266 ad_ddl.build_statement(' SET (ytd_debit_balance_e, ', r); r:=r+1;
267 ad_ddl.build_statement(' ytd_credit_balance_e, ', r); r:=r+1;
268 ad_ddl.build_statement(' ytd_balance_e, ', r); r:=r+1;
269 ad_ddl.build_statement(' ytd_balance_f) = (', r); r:=r+1;
270 ad_ddl.build_statement(' SELECT fb.ytd_debit_balance_e + NVL(gbit.ptd_debit_balance_e,0) , ', r); r:=r+1;
271 ad_ddl.build_statement(' fb.ytd_credit_balance_e + NVL(gbit.ptd_credit_balance_e,0) , ', r); r:=r+1;
272 ad_ddl.build_statement(' fb.ytd_balance_e + NVL(gbit.ptd_debit_balance_e, 0) - ', r); r:=r+1;
273 ad_ddl.build_statement(' NVL(gbit.ptd_credit_balance_e, 0), ', r); r:=r+1;
274 ad_ddl.build_statement(' fb.ytd_balance_f + NVL(gbit.ptd_debit_balance_f, 0) - ', r); r:=r+1;
275 ad_ddl.build_statement(' NVL(gbit.ptd_credit_balance_f, 0) ', r); r:=r+1;
276 ad_ddl.build_statement(' FROM fem_balances fb ', r); r:=r+1;
277 ad_ddl.build_statement(' ,fem_cctr_orgs_b fcob ', r); r:=r+1;
278 ad_ddl.build_statement(' ,fem_ln_items_b flib ', r); r:=r+1;
279 ad_ddl.build_statement(' ,fem_cctr_orgs_b fcib ', r); r:=r+1;
280 -- Build the additional components of the from clause
281 build_table_list(r);
282 ad_ddl.build_statement(' WHERE fb.ledger_id = p_ledger_id ', r); r:=r+1;
283 ad_ddl.build_statement(' AND fb.cal_period_id = p_cal_period_id ', r); r:=r+1;
284 ad_ddl.build_statement(' AND fb.dataset_code = p_dataset_code ' , r); r:=r+1;
285 ad_ddl.build_statement(' AND fb.source_system_code = p_source_system_code ', r); r:=r+1;
286 ad_ddl.build_statement(' AND fb.currency_type_code = p_currency_type ' , r); r:=r+1;
287 ad_ddl.build_statement(' AND fb.currency_code = DECODE(p_currency_code, NULL, ', r); r:=r+1;
288 ad_ddl.build_statement(' gbit.currency_code, ', r); r:=r+1;
289 ad_ddl.build_statement(' p_currency_code)', r); r:=r+1;
290 ad_ddl.build_statement(' AND fb.company_cost_center_org_id = fcob.company_cost_center_org_id', r); r:=r+1;
291
292 ad_ddl.build_statement(' AND fcob.cctr_org_display_code = gbit.cctr_org_display_code', r); r:=r+1;
293 ad_ddl.build_statement(' AND fb.line_item_id = flib.line_item_id', r); r:=r+1;
294 ad_ddl.build_statement(' AND flib.line_item_display_code = gbit.line_item_display_code', r); r:=r+1;
295 ad_ddl.build_statement(' AND fb.intercompany_id = fcib.company_cost_center_org_id', r); r:=r+1;
296 ad_ddl.build_statement(' AND fcib.cctr_org_display_code = gbit.intercompany_display_code', r); r:=r+1;
297 -- Build the additiona components of the where clause
298 build_where_clause_list(r);
299 ad_ddl.build_statement(' )', r); r:=r+1;
300 ad_ddl.build_statement(' WHERE load_id = p_load_id;', r); r:=r+1;
301 ad_ddl.build_statement(' ', r); r:=r+1;
302 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN ', r); r:=r+1;
303 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.UPDATE_YTD_BALANCES.end'', ''<<Exit>>''); ', r); r:=r+1;
304 ad_ddl.build_statement(' END IF; ', r); r:=r+1;
305 ad_ddl.build_statement(' ', r); r:=r+1;
306 ad_ddl.build_statement(' COMMIT;', r); r:=r+1;
307 ad_ddl.build_statement(' END update_ytd_balances;', r); r:=r+1;
308 ad_ddl.build_statement(' -- ', r); r:=r+1;
309 ad_ddl.build_statement(' -- ', r); r:=r+1;
310 ad_ddl.build_statement(' PROCEDURE update_ptd_balances (p_load_id IN NUMBER, ' , r); r:=r+1;
311 ad_ddl.build_statement(' p_source_system_code IN NUMBER, ' , r); r:=r+1;
312 ad_ddl.build_statement(' p_dataset_code IN NUMBER, ' , r); r:=r+1;
313 ad_ddl.build_statement(' p_cal_period_id IN NUMBER, ' , r); r:=r+1;
314 ad_ddl.build_statement(' p_ledger_id IN NUMBER, ' , r); r:=r+1;
315 ad_ddl.build_statement(' p_currency_type IN VARCHAR2, ' , r); r:=r+1;
316 ad_ddl.build_statement(' p_currency_code IN VARCHAR2) ' , r); r:=r+1;
317 ad_ddl.build_statement(' IS PRAGMA AUTONOMOUS_TRANSACTION; ', r); r:=r+1;
318 ad_ddl.build_statement(' BEGIN ', r); r:=r+1;
319 ad_ddl.build_statement(' ', r); r:=r+1;
320 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN ', r); r:=r+1;
321 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.UPDATE_PTD_BALANCES.begin'',
322 ''<<Enter>>'' ); ', r); r:=r+1;
323 ad_ddl.build_statement(' END IF; ', r); r:=r+1;
324 ad_ddl.build_statement(' ', r); r:=r+1;
325 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN ', r); r:=r+1;
326 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCES'',
327 ''Load Id : '' || p_load_id );', r); r:=r+1;
328 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCES'',
329 ''Source System : '' || p_source_system_code );', r); r:=r+1;
330 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCES'',
331 ''Dataset Code : '' || p_dataset_code );', r); r:=r+1;
332 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCES'',
333 ''Cal Period Id : '' || p_cal_period_id );', r); r:=r+1;
337 ''Currency Type : '' || p_currency_type );', r); r:=r+1;
334 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCES'',
335 ''Ledger Id : '' || p_ledger_id );', r); r:=r+1;
336 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCES'',
338 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCES'',
339 ''Currency Code : '' || p_currency_code );', r); r:=r+1;
340 ad_ddl.build_statement(' ', r); r:=r+1;
341 ad_ddl.build_statement(' END IF;', r); r:=r+1;
342 ad_ddl.build_statement(' ', r); r:=r+1;
343 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
344 ad_ddl.build_statement(' SET (ptd_debit_balance_e, ', r); r:=r+1;
345 ad_ddl.build_statement(' ptd_credit_balance_e, ', r); r:=r+1;
346 ad_ddl.build_statement(' ptd_balance_e, ', r); r:=r+1;
347 ad_ddl.build_statement(' ptd_balance_f) = (', r); r:=r+1;
348 ad_ddl.build_statement(' SELECT gbit.ytd_debit_balance_e - NVL(fb.ytd_debit_balance_e,0) , ', r); r:=r+1;
349 ad_ddl.build_statement(' gbit.ytd_credit_balance_e - NVL(fb.ytd_credit_balance_e,0) , ', r); r:=r+1;
350 ad_ddl.build_statement(' gbit.ytd_balance_e - NVL(fb.ytd_balance_e, 0), ', r); r:=r+1;
351 ad_ddl.build_statement(' gbit.ytd_balance_f - NVL(fb.ytd_balance_f, 0) ', r); r:=r+1;
352 ad_ddl.build_statement(' FROM fem_balances fb ', r); r:=r+1;
353 ad_ddl.build_statement(' ,fem_cctr_orgs_b fcob ', r); r:=r+1;
354 ad_ddl.build_statement(' ,fem_ln_items_b flib ', r); r:=r+1;
355 ad_ddl.build_statement(' ,fem_cctr_orgs_b fcib ', r); r:=r+1;
356 -- Build the additional components of the from clause
357 build_table_list(r);
358 ad_ddl.build_statement(' WHERE fb.ledger_id = p_ledger_id ', r); r:=r+1;
359 ad_ddl.build_statement(' AND fb.cal_period_id = p_cal_period_id ', r); r:=r+1;
360 ad_ddl.build_statement(' AND fb.dataset_code = p_dataset_code ' , r); r:=r+1;
361 ad_ddl.build_statement(' AND fb.source_system_code = p_source_system_code ', r); r:=r+1;
362 ad_ddl.build_statement(' AND fb.currency_type_code = p_currency_type ' , r); r:=r+1;
363 ad_ddl.build_statement(' AND fb.currency_code = DECODE(p_currency_code, NULL, ', r); r:=r+1;
364 ad_ddl.build_statement(' gbit.currency_code, ', r); r:=r+1;
365 ad_ddl.build_statement(' p_currency_code)', r); r:=r+1;
366 ad_ddl.build_statement(' AND fb.company_cost_center_org_id = fcob.company_cost_center_org_id', r); r:=r+1;
367
368 ad_ddl.build_statement(' AND fcob.cctr_org_display_code = gbit.cctr_org_display_code', r); r:=r+1;
369 ad_ddl.build_statement(' AND fb.line_item_id = flib.line_item_id', r); r:=r+1;
370 ad_ddl.build_statement(' AND flib.line_item_display_code = gbit.line_item_display_code', r); r:=r+1;
371 ad_ddl.build_statement(' AND fb.intercompany_id = fcib.company_cost_center_org_id', r); r:=r+1;
372 ad_ddl.build_statement(' AND fcib.cctr_org_display_code = gbit.intercompany_display_code', r); r:=r+1;
373 -- Build the additiona components of the where clause
374 build_where_clause_list(r);
375 ad_ddl.build_statement(' )', r); r:=r+1;
376 ad_ddl.build_statement(' WHERE gbit.load_id = p_load_id;', r); r:=r+1;
377 ad_ddl.build_statement(' ', r); r:=r+1;
378 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN', r); r:=r+1;
379 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.UPDATE_PTD_BALANCES.end'', ''<<Exit>>''); ', r); r:=r+1;
380 ad_ddl.build_statement(' END IF; ', r); r:=r+1;
381 ad_ddl.build_statement(' ', r); r:=r+1;
382 ad_ddl.build_statement(' COMMIT; ' , r); r:=r+1;
383 ad_ddl.build_statement(' END update_ptd_balances;', r); r:=r+1;
384 ad_ddl.build_statement(' -- ', r); r:=r+1;
385 ad_ddl.build_statement(' PROCEDURE update_ptd_balance_sheet ( p_load_id IN NUMBER, ' , r); r:=r+1;
386 ad_ddl.build_statement(' p_source_system_code IN NUMBER, ' , r); r:=r+1;
387 ad_ddl.build_statement(' p_dataset_code IN NUMBER, ' , r); r:=r+1;
388 ad_ddl.build_statement(' p_cal_period_id IN NUMBER, ' , r); r:=r+1;
389 ad_ddl.build_statement(' p_ledger_id IN NUMBER, ' , r); r:=r+1;
390 ad_ddl.build_statement(' p_currency_type IN VARCHAR2, ' , r); r:=r+1;
391 ad_ddl.build_statement(' p_currency_code IN VARCHAR2) ' , r); r:=r+1;
392 ad_ddl.build_statement(' IS PRAGMA AUTONOMOUS_TRANSACTION; ', r); r:=r+1;
393 ad_ddl.build_statement(' l_line_item_vs_id NUMBER; ', r); r:=r+1;
394 ad_ddl.build_statement(' l_ledger_vs_combo_attr NUMBER(15) := ', r); r:=r+1;
395 ad_ddl.build_statement(' gcs_utility_pkg.g_dimension_attr_info(''LEDGER_ID-GLOBAL_VS_COMBO'').attribute_id;', r); r:=r+1;
396 ad_ddl.build_statement(' l_ledger_vs_combo_version NUMBER(15) := ', r); r:=r+1;
397 ad_ddl.build_statement(' gcs_utility_pkg.g_dimension_attr_info(''LEDGER_ID-GLOBAL_VS_COMBO'').version_id;', r); r:=r+1;
398 ad_ddl.build_statement(' l_line_item_type_attr NUMBER(15) := ', r); r:=r+1;
399 ad_ddl.build_statement(' gcs_utility_pkg.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id;', r); r:=r+1;
400 ad_ddl.build_statement(' l_line_item_type_version NUMBER(15) := ', r); r:=r+1;
401 ad_ddl.build_statement(' gcs_utility_pkg.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id;', r); r:=r+1;
402 ad_ddl.build_statement(' l_acct_type_attr NUMBER(15) := ', r); r:=r+1;
403 ad_ddl.build_statement(' gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id;', r); r:=r+1;
404 ad_ddl.build_statement(' l_acct_type_version NUMBER(15) := ', r); r:=r+1;
408 ad_ddl.build_statement(' ', r); r:=r+1;
405 ad_ddl.build_statement(' gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id;', r); r:=r+1;
406 ad_ddl.build_statement(' ', r); r:=r+1;
407 ad_ddl.build_statement(' BEGIN ', r); r:=r+1;
409 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN ', r); r:=r+1;
410 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.UPDATE_PTD_BALANCE_SHEET.begin'',
411 ''<<Enter>>'' ); ', r); r:=r+1;
412 ad_ddl.build_statement(' END IF; ', r); r:=r+1;
413 ad_ddl.build_statement(' ', r); r:=r+1;
414 ad_ddl.build_statement(' SELECT fgvcd.value_set_id ', r); r:=r+1;
415 ad_ddl.build_statement(' INTO l_line_item_vs_id ', r); r:=r+1;
416 ad_ddl.build_statement(' FROM fem_ledgers_attr fla, ', r); r:=r+1;
417 ad_ddl.build_statement(' fem_global_vs_combo_defs fgvcd ', r); r:=r+1;
418 ad_ddl.build_statement(' WHERE fla.ledger_id = p_ledger_id ', r); r:=r+1;
419 ad_ddl.build_statement(' AND fgvcd.global_vs_combo_id = fla.dim_attribute_numeric_member ', r); r:=r+1;
420 ad_ddl.build_statement(' AND fla.attribute_id = l_ledger_vs_combo_attr ', r); r:=r+1;
421 ad_ddl.build_statement(' AND fla.version_id = l_ledger_vs_combo_version ', r); r:=r+1;
422 ad_ddl.build_statement(' AND fgvcd.dimension_id = 14; ', r); r:=r+1;
423 ad_ddl.build_statement(' ', r); r:=r+1;
424 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN ', r); r:=r+1;
425 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCE_SHEET'',
426 ''Load Id : '' || p_load_id );', r); r:=r+1;
427 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCE_SHEET'',
428 ''Source System : '' || p_source_system_code );', r); r:=r+1;
429 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCE_SHEET'',
430 ''Dataset Code : '' || p_dataset_code );', r); r:=r+1;
431 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCE_SHEET'',
432 ''Cal Period Id : '' || p_cal_period_id );', r); r:=r+1;
433 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCE_SHEET'',
434 ''Ledger Id : '' || p_ledger_id );', r); r:=r+1;
435 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCE_SHEET'',
436 ''Currency Type : '' || p_currency_type );', r); r:=r+1;
437 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.UPDATE_PTD_BALANCE_SHEET'',
438 ''Currency Code : '' || p_currency_code );', r); r:=r+1;
439 ad_ddl.build_statement(' ', r); r:=r+1;
440 ad_ddl.build_statement(' END IF;', r); r:=r+1;
441 ad_ddl.build_statement(' ', r); r:=r+1;
442 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
443 ad_ddl.build_statement(' SET (ptd_debit_balance_e, ', r); r:=r+1;
444 ad_ddl.build_statement(' ptd_credit_balance_e, ', r); r:=r+1;
445 ad_ddl.build_statement(' ptd_balance_e, ', r); r:=r+1;
446 ad_ddl.build_statement(' ptd_balance_f) = (', r); r:=r+1;
447 ad_ddl.build_statement(' SELECT gbit.ytd_debit_balance_e - NVL(fb.ytd_debit_balance_e,0) , ', r); r:=r+1;
448 ad_ddl.build_statement(' gbit.ytd_credit_balance_e - NVL(fb.ytd_credit_balance_e,0) , ', r); r:=r+1;
449 ad_ddl.build_statement(' gbit.ytd_balance_e - NVL(fb.ytd_balance_e, 0), ', r); r:=r+1;
450 ad_ddl.build_statement(' gbit.ytd_balance_f - NVL(fb.ytd_balance_f, 0) ', r); r:=r+1;
451 ad_ddl.build_statement(' FROM fem_balances fb ', r); r:=r+1;
452 ad_ddl.build_statement(' ,fem_cctr_orgs_b fcob ', r); r:=r+1;
453 ad_ddl.build_statement(' ,fem_ln_items_b flib ', r); r:=r+1;
454 ad_ddl.build_statement(' ,fem_cctr_orgs_b fcib ', r); r:=r+1;
455 -- Build the additional components of the from clause
456 build_table_list(r);
457 ad_ddl.build_statement(' WHERE fb.ledger_id = p_ledger_id ', r); r:=r+1;
458 ad_ddl.build_statement(' AND fb.cal_period_id = p_cal_period_id ', r); r:=r+1;
459 ad_ddl.build_statement(' AND fb.dataset_code = p_dataset_code ' , r); r:=r+1;
460 ad_ddl.build_statement(' AND fb.source_system_code = p_source_system_code ', r); r:=r+1;
461 ad_ddl.build_statement(' AND fb.currency_type_code = p_currency_type ' , r); r:=r+1;
462 ad_ddl.build_statement(' AND fb.currency_code = DECODE(p_currency_code, NULL, ', r); r:=r+1;
463 ad_ddl.build_statement(' gbit.currency_code, ', r); r:=r+1;
464 ad_ddl.build_statement(' p_currency_code)', r); r:=r+1;
465 ad_ddl.build_statement(' AND fb.company_cost_center_org_id = fcob.company_cost_center_org_id', r); r:=r+1;
466 ad_ddl.build_statement(' AND fcob.cctr_org_display_code = gbit.cctr_org_display_code', r); r:=r+1;
467 ad_ddl.build_statement(' AND fb.line_item_id = flib.line_item_id', r); r:=r+1;
468 ad_ddl.build_statement(' AND flib.line_item_display_code = gbit.line_item_display_code', r); r:=r+1;
469 ad_ddl.build_statement(' AND fb.intercompany_id = fcib.company_cost_center_org_id', r); r:=r+1;
470 ad_ddl.build_statement(' AND fcib.cctr_org_display_code = gbit.intercompany_display_code', r); r:=r+1;
471 -- Build the additiona components of the where clause
472 build_where_clause_list(r);
473 ad_ddl.build_statement(' )', r); r:=r+1;
474 ad_ddl.build_statement(' WHERE gbit.load_id = p_load_id ', r); r:=r+1;
475 ad_ddl.build_statement(' AND EXISTS (SELECT ''X'' ', r); r:=r+1;
476 ad_ddl.build_statement(' FROM fem_ln_items_b flib, ', r); r:=r+1;
477 ad_ddl.build_statement(' fem_ln_items_attr flia, ', r); r:=r+1;
478 ad_ddl.build_statement(' fem_ext_acct_types_attr fea_attr ', r); r:=r+1;
479 ad_ddl.build_statement(' WHERE gbit.line_item_display_code = flib.line_item_display_code ', r); r:=r+1;
480 ad_ddl.build_statement(' AND flib.line_item_id = flia.line_item_id ', r); r:=r+1;
481 ad_ddl.build_statement(' AND flib.value_set_id = l_line_item_vs_id ', r); r:=r+1;
482 ad_ddl.build_statement(' AND flib.value_set_id = flia.value_set_id ', r); r:=r+1;
483 ad_ddl.build_statement(' AND flia.attribute_id = l_line_item_type_attr ', r); r:=r+1;
484 ad_ddl.build_statement(' AND flia.version_id = l_line_item_type_version ', r); r:=r+1;
485 ad_ddl.build_statement(' AND flia.dim_attribute_varchar_member = fea_attr.ext_account_type_code ', r); r:=r+1;
486 ad_ddl.build_statement(' AND fea_attr.attribute_id = l_acct_type_attr ', r); r:=r+1;
487 ad_ddl.build_statement(' AND fea_attr.version_id = l_acct_type_version ', r); r:=r+1;
488 ad_ddl.build_statement(' AND fea_attr.dim_attribute_varchar_member IN (''EQUITY'', ', r); r:=r+1;
489 ad_ddl.build_statement(' ''ASSET'', ', r); r:=r+1;
490 ad_ddl.build_statement(' ''LIABILITY''', r); r:=r+1;
491 ad_ddl.build_statement(' ) ', r); r:=r+1;
492 ad_ddl.build_statement(' ); ', r); r:=r+1;
493 ad_ddl.build_statement(' ', r); r:=r+1;
497 ad_ddl.build_statement(' ', r); r:=r+1;
494 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN', r); r:=r+1;
495 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.UPDATE_PTD_BALANCE_SHEET.end'', ''<<Exit>>''); ', r); r:=r+1;
496 ad_ddl.build_statement(' END IF; ', r); r:=r+1;
498 ad_ddl.build_statement(' COMMIT; ' , r); r:=r+1;
499 ad_ddl.build_statement(' END update_ptd_balance_sheet;', r); r:=r+1;
500 ad_ddl.build_statement(' -- ', r); r:=r+1;
501 ad_ddl.build_statement(' ', r); r:=r+1;
502 -- Bug Fix : 5234796, Start
503 ad_ddl.build_statement(' PROCEDURE validate_dimension_members(p_load_id IN NUMBER ) ' , r); r:=r+1;
504 ad_ddl.build_statement(' IS PRAGMA AUTONOMOUS_TRANSACTION; ', r); r:=r+1;
505 ad_ddl.build_statement(' TYPE dim_vs_info_rec_type IS RECORD( ', r); r:=r+1;
506 ad_ddl.build_statement(' vs_id NUMBER , ', r); r:=r+1;
507 ad_ddl.build_statement(' dim_display_code VARCHAR2(50),', r); r:=r+1;
508 ad_ddl.build_statement(' dim_id_col VARCHAR2(50) , ', r); r:=r+1;
509 ad_ddl.build_statement(' dim_name VARCHAR2(50) ); ', r); r:=r+1;
510 ad_ddl.build_statement(' TYPE t_dim_vs_info IS TABLE OF dim_vs_info_rec_type; ', r); r:=r+1;
511 ad_ddl.build_statement(' l_dim_vs_info t_dim_vs_info; ', r); r:=r+1;
512 ad_ddl.build_statement(' l_ledger_id NUMBER(10); ', r); r:=r+1;
513 ad_ddl.build_statement(' l_dim_id_col VARCHAR2(50); ', r); r:=r+1;
514 -- Bugfix 5358633, Start
515 ad_ddl.build_statement(' l_invalid_err_msg VARCHAR2(2000); ', r); r:=r+1;
516 ad_ddl.build_statement(' l_null_err_msg VARCHAR2(2000); ', r); r:=r+1;
517 -- Bugfix 5358633, End
518 ad_ddl.build_statement(' BEGIN ', r); r:=r+1;
519 ad_ddl.build_statement(' ', r); r:=r+1;
520 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN ', r); r:=r+1;
521 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.VALIDATE_DIMENSION_MEMBERS.begin'',
522 ''<<Enter>>'' ); ', r); r:=r+1;
523 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.VALIDATE_DIMENSION_MEMBERS'',
524 ''Load Id : '' || p_load_id );', r); r:=r+1;
525 ad_ddl.build_statement(' END IF;', r); r:=r+1;
526 ad_ddl.build_statement(' ', r); r:=r+1;
527 ad_ddl.build_statement(' ', r); r:=r+1;
528 -- Bugfix 5358633, Start
529 ad_ddl.build_statement(' UPDATE ', r); r:=r+1;
530 ad_ddl.build_statement(' gcs_bal_interface_t ', r); r:=r+1;
531 ad_ddl.build_statement(' SET error_message_code = NULL ', r); r:=r+1;
532 ad_ddl.build_statement(' WHERE load_id = p_load_id; ', r); r:=r+1;
533 -- Bugfix 5358633, End
534 ad_ddl.build_statement('', r); r:=r+1;
535 ad_ddl.build_statement(' ', r); r:=r+1;
536 ad_ddl.build_statement(' SELECT fea.dim_attribute_numeric_member ', r); r:=r+1;
537 ad_ddl.build_statement(' INTO l_ledger_id ', r); r:=r+1;
538 ad_ddl.build_statement(' FROM fem_entities_attr fea, ', r); r:=r+1;
539 ad_ddl.build_statement(' gcs_data_sub_dtls gdsd ', r); r:=r+1;
540 ad_ddl.build_statement(' WHERE gdsd.load_id = p_load_id ', r); r:=r+1;
541 ad_ddl.build_statement(' AND fea.entity_id = gdsd.entity_id ', r); r:=r+1;
542 ad_ddl.build_statement(' AND fea.attribute_id = gcs_utility_pkg.g_dimension_attr_info(''ENTITY_ID-LEDGER_ID'').attribute_id ', r); r:=r+1;
543 ad_ddl.build_statement(' AND fea.version_id = gcs_utility_pkg.g_dimension_attr_info(''ENTITY_ID-LEDGER_ID'').version_id; ', r); r:=r+1;
544 ad_ddl.build_statement(' ', r); r:=r+1;
545 ad_ddl.build_statement(' ', r); r:=r+1;
546 ad_ddl.build_statement(' SELECT fgvcd.value_set_id, ', r); r:=r+1;
547 ad_ddl.build_statement(' fxd.member_display_code_col, ', r); r:=r+1;
548 ad_ddl.build_statement(' fxd.member_col, ', r); r:=r+1;
549 ad_ddl.build_statement(' fdt.dimension_name ', r); r:=r+1;
550 ad_ddl.build_statement(' BULK COLLECT ', r); r:=r+1;
551 ad_ddl.build_statement(' INTO l_dim_vs_info ', r); r:=r+1;
552 ad_ddl.build_statement(' FROM fem_global_vs_combo_defs fgvcd, ', r); r:=r+1;
553 ad_ddl.build_statement(' fem_ledgers_attr fla, ', r); r:=r+1;
554 ad_ddl.build_statement(' fem_xdim_dimensions fxd, ', r); r:=r+1;
555 ad_ddl.build_statement(' fem_dimensions_tl fdt ', r); r:=r+1;
556 ad_ddl.build_statement(' WHERE gcs_utility_pkg.get_fem_dim_required(fxd.MEMBER_COL) = ''Y'' ', r); r:=r+1;
557 ad_ddl.build_statement(' AND global_vs_combo_id = fla.dim_attribute_numeric_member ', r); r:=r+1;
558 ad_ddl.build_statement(' AND fla.ledger_id = l_ledger_id ', r); r:=r+1;
559 ad_ddl.build_statement(' AND fla.attribute_id = gcs_utility_pkg.g_dimension_attr_info(''LEDGER_ID-GLOBAL_VS_COMBO'').attribute_id ', r); r:=r+1;
560 ad_ddl.build_statement(' AND fla.version_id = gcs_utility_pkg.g_dimension_attr_info(''LEDGER_ID-GLOBAL_VS_COMBO'').version_id ', r); r:=r+1;
561 ad_ddl.build_statement(' AND fgvcd.dimension_id = fxd.dimension_id ', r); r:=r+1;
562 ad_ddl.build_statement(' AND fxd.member_col IN (''COMPANY_COST_CENTER_ORG_ID'',''FINANCIAL_ELEM_ID'',''PRODUCT_ID'',''NATURAL_ACCOUNT_ID'',''CHANNEL_ID'', ', r); r:=r+1;
563 ad_ddl.build_statement(' ''LINE_ITEM_ID'',''PROJECT_ID'',''CUSTOMER_ID'',''TASK_ID'',''USER_DIM1_ID'',''USER_DIM10_ID'', ', r); r:=r+1;
564 ad_ddl.build_statement(' ''USER_DIM2_ID'',''USER_DIM3_ID'', ''USER_DIM4_ID'',''USER_DIM5_ID'', ', r); r:=r+1;
565 ad_ddl.build_statement(' ''USER_DIM6_ID'',''USER_DIM7_ID'',''USER_DIM8_ID'',''USER_DIM9_ID'') ', r); r:=r+1;
566 ad_ddl.build_statement(' AND fdt.dimension_id = fxd.dimension_id ', r); r:=r+1;
567 ad_ddl.build_statement(' AND fdt.language = userenv(''LANG''); ', r); r:=r+1;
571 ad_ddl.build_statement(' FOR l_counter IN l_dim_vs_info.FIRST..l_dim_vs_info.LAST LOOP ', r); r:=r+1;
568 ad_ddl.build_statement(' ', r); r:=r+1;
569 ad_ddl.build_statement(' ', r); r:=r+1;
570 ad_ddl.build_statement(' IF l_dim_vs_info.FIRST IS NOT NULL THEN ', r); r:=r+1;
572 ad_ddl.build_statement(' FND_MESSAGE.set_name( ''GCS'', ''GCS_DS_DIM_INVALID_MSG'' ); ', r); r:=r+1;
573 ad_ddl.build_statement(' FND_MESSAGE.set_token(''DIM_NAME'', l_dim_vs_info(l_counter).dim_name) ; ', r); r:=r+1;
574 -- Bugfix 5358633, Start
575 ad_ddl.build_statement(' l_invalid_err_msg := FND_MESSAGE.get ;', r); r:=r+1;
576 ad_ddl.build_statement(' ', r); r:=r+1;
577 ad_ddl.build_statement(' FND_MESSAGE.set_name( ''GCS'', ''GCS_DS_DIM_NULL_MSG'' ); ', r); r:=r+1;
578 ad_ddl.build_statement(' FND_MESSAGE.set_token(''COLUMN_NAME'', l_dim_vs_info(l_counter).dim_display_code) ; ', r); r:=r+1;
579 ad_ddl.build_statement(' l_null_err_msg := FND_MESSAGE.get ; ', r); r:=r+1;
580 -- Bugfix 5358633, End , Added messages(Null and Invalid cases) in all the below queries
581 ad_ddl.build_statement(' l_dim_id_col := l_dim_vs_info(l_counter).dim_id_col ; ', r); r:=r+1;
582 ad_ddl.build_statement(' ', r); r:=r+1;
583 ad_ddl.build_statement(' IF ( l_dim_id_col = ''COMPANY_COST_CENTER_ORG_ID'' ) THEN ', r); r:=r+1;
584 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
585 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
586 ad_ddl.build_statement(' || DECODE ( cctr_org_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
587 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || cctr_org_display_code || '').'' )', r); r:=r+1;
588 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
589 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
590 ad_ddl.build_statement(' FROM fem_cctr_orgs_b fcob ', r); r:=r+1;
591 ad_ddl.build_statement(' WHERE fcob.cctr_org_display_code = gbit.cctr_org_display_code ', r); r:=r+1;
592 ad_ddl.build_statement(' AND fcob.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
593 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''FINANCIAL_ELEM_ID'' ) THEN ', r); r:=r+1;
594 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
595 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
596 ad_ddl.build_statement(' || DECODE ( financial_elem_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
597 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || financial_elem_display_code || '').'' )', r); r:=r+1;
598 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
599 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
600 ad_ddl.build_statement(' FROM fem_fin_elems_b ffeb ', r); r:=r+1;
601 ad_ddl.build_statement(' WHERE ffeb.financial_elem_display_code = gbit.financial_elem_display_code ', r); r:=r+1;
602 ad_ddl.build_statement(' AND ffeb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
603 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''LINE_ITEM_ID'' ) THEN ', r); r:=r+1;
604 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
605 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
606 ad_ddl.build_statement(' || DECODE ( line_item_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
607 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || line_item_display_code || '').'' )', r); r:=r+1;
608 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
609 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
610 ad_ddl.build_statement(' FROM fem_ln_items_b flib ', r); r:=r+1;
611 ad_ddl.build_statement(' WHERE flib.line_item_display_code = gbit.line_item_display_code ', r); r:=r+1;
612 ad_ddl.build_statement(' AND flib.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
613 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''PRODUCT_ID'') THEN ', r); r:=r+1;
614 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
615 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
616 ad_ddl.build_statement(' || DECODE ( product_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
617 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || product_display_code || '').'' )', r); r:=r+1;
618 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
619 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
620 ad_ddl.build_statement(' FROM fem_products_b fpb ', r); r:=r+1;
621 ad_ddl.build_statement(' WHERE fpb.product_display_code = gbit.product_display_code ', r); r:=r+1;
622 ad_ddl.build_statement(' AND fpb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
623 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''NATURAL_ACCOUNT_ID'' ) THEN ', r); r:=r+1;
624 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
625 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
626 ad_ddl.build_statement(' || DECODE ( natural_account_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
627 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || natural_account_display_code || '').'' )', r); r:=r+1;
628 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
632 ad_ddl.build_statement(' AND fnab.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
629 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
630 ad_ddl.build_statement(' FROM fem_nat_accts_b fnab ', r); r:=r+1;
631 ad_ddl.build_statement(' WHERE fnab.natural_account_display_code = gbit.natural_account_display_code ', r); r:=r+1;
633 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''CHANNEL_ID'') THEN ', r); r:=r+1;
634 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
635 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
636 ad_ddl.build_statement(' || DECODE ( channel_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
637 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || channel_display_code || '').'' )', r); r:=r+1;
638 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
639 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
640 ad_ddl.build_statement(' FROM fem_channels_b fcb ', r); r:=r+1;
641 ad_ddl.build_statement(' WHERE fcb.channel_display_code = gbit.channel_display_code ', r); r:=r+1;
642 ad_ddl.build_statement(' AND fcb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
643 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''PROJECT_ID'') THEN ', r); r:=r+1;
644 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
645 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
646 ad_ddl.build_statement(' || DECODE ( project_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
647 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || project_display_code || '').'' )', r); r:=r+1;
648 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
649 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
650 ad_ddl.build_statement(' FROM fem_projects_b fpb ', r); r:=r+1;
651 ad_ddl.build_statement(' WHERE fpb.project_display_code = gbit.project_display_code ', r); r:=r+1;
652 ad_ddl.build_statement(' AND fpb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
653 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''CUSTOMER_ID'' ) THEN ', r); r:=r+1;
654 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
655 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
656 ad_ddl.build_statement(' || DECODE ( customer_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
657 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || customer_display_code || '').'' )', r); r:=r+1;
658 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
659 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
660 ad_ddl.build_statement(' FROM fem_customers_b fcb ', r); r:=r+1;
661 ad_ddl.build_statement(' WHERE fcb.customer_display_code = gbit.customer_display_code ', r); r:=r+1;
662 ad_ddl.build_statement(' AND fcb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
663 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''TASK_ID'') THEN ', r); r:=r+1;
664 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
665 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
666 ad_ddl.build_statement(' || DECODE ( task_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
667 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || task_display_code || '').'' )', r); r:=r+1;
668 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
669 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
670 ad_ddl.build_statement(' FROM fem_tasks_b ftb ', r); r:=r+1;
671 ad_ddl.build_statement(' WHERE ftb.task_display_code = gbit.task_display_code ', r); r:=r+1;
672 ad_ddl.build_statement(' AND ftb.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
673 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM1_ID'' ) THEN ', r); r:=r+1;
674 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
675 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
676 ad_ddl.build_statement(' || DECODE ( user_dim1_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
677 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim1_display_code || '').'' )', r); r:=r+1;
678 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
679 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
680 ad_ddl.build_statement(' FROM fem_user_dim1_b fub ', r); r:=r+1;
681 ad_ddl.build_statement(' WHERE fub.user_dim1_display_code = gbit.user_dim1_display_code ', r); r:=r+1;
682 ad_ddl.build_statement(' AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
683 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM2_ID'' ) THEN ', r); r:=r+1;
684 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
685 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
686 ad_ddl.build_statement(' || DECODE ( user_dim2_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
687 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim2_display_code || '').'' )', r); r:=r+1;
688 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
689 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
690 ad_ddl.build_statement(' FROM fem_user_dim2_b fub ', r); r:=r+1;
691 ad_ddl.build_statement(' WHERE fub.user_dim2_display_code = gbit.user_dim2_display_code ', r); r:=r+1;
692 ad_ddl.build_statement(' AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
693 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM3_ID'' ) THEN ', r); r:=r+1;
694 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
695 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
699 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
696 ad_ddl.build_statement(' || DECODE ( user_dim3_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
697 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim3_display_code || '').'' )', r); r:=r+1;
698 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
700 ad_ddl.build_statement(' FROM fem_user_dim3_b fub ', r); r:=r+1;
701 ad_ddl.build_statement(' WHERE fub.user_dim3_display_code = gbit.user_dim3_display_code ', r); r:=r+1;
702 ad_ddl.build_statement(' AND fub.value_set_id =l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
703 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM4_ID'' ) THEN ', r); r:=r+1;
704 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
705 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
706 ad_ddl.build_statement(' || DECODE ( user_dim4_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
707 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim4_display_code || '').'' )', r); r:=r+1;
708 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
709 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
710 ad_ddl.build_statement(' FROM fem_user_dim4_b fub ', r); r:=r+1;
711 ad_ddl.build_statement(' WHERE fub.user_dim4_display_code = gbit.user_dim4_display_code ', r); r:=r+1;
712 ad_ddl.build_statement(' AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
713 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM5_ID'' ) THEN ', r); r:=r+1;
714 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
715 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
716 ad_ddl.build_statement(' || DECODE ( user_dim5_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
717 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim5_display_code || '').'' )', r); r:=r+1;
718 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
719 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
720 ad_ddl.build_statement(' FROM fem_user_dim5_b fub ', r); r:=r+1;
721 ad_ddl.build_statement(' WHERE fub.user_dim5_display_code = gbit.user_dim5_display_code ', r); r:=r+1;
722 ad_ddl.build_statement(' AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
723 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM6_ID'' ) THEN ', r); r:=r+1;
724 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
725 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
726 ad_ddl.build_statement(' || DECODE ( user_dim6_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
727 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim6_display_code || '').'' )', r); r:=r+1;
728 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
729 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
730 ad_ddl.build_statement(' FROM fem_user_dim6_b fub ', r); r:=r+1;
731 ad_ddl.build_statement(' WHERE fub.user_dim6_display_code = gbit.user_dim6_display_code ', r); r:=r+1;
732 ad_ddl.build_statement(' AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
733 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM7_ID'' ) THEN ', r); r:=r+1;
734 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
735 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
736 ad_ddl.build_statement(' || DECODE ( user_dim7_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
737 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim7_display_code || '').'' )', r); r:=r+1;
738 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
739 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
740 ad_ddl.build_statement(' FROM fem_user_dim7_b fub ', r); r:=r+1;
744 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
741 ad_ddl.build_statement(' WHERE fub.user_dim7_display_code = gbit.user_dim7_display_code ', r); r:=r+1;
742 ad_ddl.build_statement(' AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
743 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM8_ID'' ) THEN ', r); r:=r+1;
745 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
746 ad_ddl.build_statement(' || DECODE ( user_dim8_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
747 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim8_display_code || '').'' )', r); r:=r+1;
748 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
749 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
750 ad_ddl.build_statement(' FROM fem_user_dim8_b fub ', r); r:=r+1;
751 ad_ddl.build_statement(' WHERE fub.user_dim8_display_code = gbit.user_dim8_display_code ', r); r:=r+1;
752 ad_ddl.build_statement(' AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
753 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM9_ID'' ) THEN ', r); r:=r+1;
754 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
755 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
756 ad_ddl.build_statement(' || DECODE ( user_dim9_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
757 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim9_display_code || '').'' )', r); r:=r+1;
758 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
759 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
760 ad_ddl.build_statement(' FROM fem_user_dim9_b fub ', r); r:=r+1;
761 ad_ddl.build_statement(' WHERE fub.user_dim9_display_code = gbit.user_dim9_display_code ', r); r:=r+1;
762 ad_ddl.build_statement(' AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
763 ad_ddl.build_statement(' ELSIF ( l_dim_id_col = ''USER_DIM10_ID'' ) THEN ', r); r:=r+1;
764 ad_ddl.build_statement(' UPDATE gcs_bal_interface_t gbit ', r); r:=r+1;
765 ad_ddl.build_statement(' SET error_message_code = error_message_code ', r); r:=r+1;
766 ad_ddl.build_statement(' || DECODE ( user_dim10_display_code, NULL , l_null_err_msg , ', r); r:=r+1;
767 ad_ddl.build_statement(' l_invalid_err_msg || ''('' || user_dim10_display_code || '').'' )', r); r:=r+1;
768 ad_ddl.build_statement(' WHERE load_id = p_load_id ', r); r:=r+1;
769 ad_ddl.build_statement(' AND NOT EXISTS (SELECT ''X'' ', r); r:=r+1;
770 ad_ddl.build_statement(' FROM fem_user_dim10_b fub ', r); r:=r+1;
771 ad_ddl.build_statement(' WHERE fub.user_dim10_display_code = gbit.user_dim10_display_code ', r); r:=r+1;
772 ad_ddl.build_statement(' AND fub.value_set_id = l_dim_vs_info(l_counter).vs_id ) ; ', r); r:=r+1;
773 ad_ddl.build_statement(' END IF; ', r); r:=r+1;
774 ad_ddl.build_statement(' END LOOP; ', r); r:=r+1;
775 ad_ddl.build_statement(' END IF; ', r); r:=r+1;
776 ad_ddl.build_statement(' ', r); r:=r+1;
777 ad_ddl.build_statement(' COMMIT; ' , r); r:=r+1;
778 ad_ddl.build_statement(' ', r); r:=r+1;
779 ad_ddl.build_statement(' IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN', r); r:=r+1;
780 ad_ddl.build_statement(' FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.VALIDATE_DIMENSION_MEMBERS.end'', ''<<Exit>>''); ', r); r:=r+1;
781 ad_ddl.build_statement(' END IF; ', r); r:=r+1;
782 ad_ddl.build_statement(' ', r); r:=r+1;
783 ad_ddl.build_statement(' END validate_dimension_members; ', r); r:=r+1;
784 -- Bug Fix : 5234796, End
785 ad_ddl.build_statement(' -- ', r); r:=r+1;
786 ad_ddl.build_statement(' ', r); r:=r+1;
787 ad_ddl.build_statement('END GCS_DATASUB_UTILITY_PKG; ', r);
788
789 ad_ddl.create_plsql_object(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
790 'GCS', 'GCS_DATASUB_UTILITY_PKG',
791 1, r , 'TRUE', comp_err);
792
793 END;
794
795
796 END GCS_DATASUB_DYNAMIC_PKG;
797