[Home] [Help]
PACKAGE BODY: APPS.CN_REPOSITORIES_PKG
Source
1 PACKAGE BODY cn_repositories_pkg AS
2 -- $Header: cnrepob.pls 120.3 2005/11/22 00:58:27 raramasa noship $
3 --+
4 -- Public Functions
5 --+
6
7
8 PROCEDURE LOAD_SEED_ROW (
9 x_UPLOAD_MODE in varchar2,
10 x_REPOSITORY_ID in varchar2,
11 x_VERSION in varchar2,
12 x_SCHEMA in varchar2,
13 x_STATUS in varchar2,
14 x_APPLICATION_TYPE in varchar2,
15 x_LAST_UPDATE_DATE in varchar2,
16 x_LAST_UPDATED_BY in varchar2,
17 x_CREATION_DATE in varchar2,
18 x_CREATED_BY in varchar2,
19 x_LAST_UPDATE_LOGIN in varchar2,
20 x_DESCRIPTION in varchar2,
21 x_DATABASE_LINK in varchar2,
22 x_USAGE_FLAG in varchar2,
23 x_EMAIL_OWNER in varchar2,
24 x_EMAIL_DBA in varchar2,
25 x_CURRENT_PERIOD_ID in varchar2,
26 x_SYSTEM_START_DATE in varchar2,
27 x_SYSTEM_START_PERIOD_ID in varchar2,
28 x_SYSTEM_END_DATE in varchar2,
29 x_SYSTEM_END_PERIOD_ID in varchar2,
30 x_REV_CLASS_HIERARCHY_ID in varchar2,
31 x_SRP_ROLLUP_HIERARCHY_ID in varchar2,
32 x_SYSTEM_BATCH_SIZE in varchar2,
33 x_TRANSFER_BATCH_SIZE in varchar2,
34 x_SRP_ROLLUP_FLAG in varchar2,
35 x_SET_OF_BOOKS_ID in varchar2,
36 x_CURRENT_PERIOD in varchar2,
37 x_SYSTEM_START_PERIOD in varchar2,
38 x_SYSTEM_END_PERIOD in varchar2,
39 x_CLAWBACK_GRACE_DAYS in varchar2,
40 x_TRX_ROLLUP_METHOD in varchar2,
41 x_SRP_BATCH_SIZE in varchar2,
42 x_NAME in varchar2,
43 x_APPLICATION_ID in varchar2,
44 x_REPOSITORY_TYPE in varchar2,
45 x_ORG_ID in varchar2,
46 x_CLS_PACKAGE_SIZE in varchar2,
47 x_SALESREP_BATCH_SIZE in varchar2,
48 x_LATEST_PROCESSED_DATE in varchar2,
49 x_PERIOD_SET_ID in varchar2,
50 x_PERIOD_TYPE_ID in varchar2,
51 x_PAYABLES_FLAG in varchar2,
52 x_PAYROLL_FLAG in varchar2,
53 x_PAYABLES_CCID_LEVEL in varchar2,
54 x_INCOME_PLANNER_DISCLAIMER in varchar2,
55 x_OBJECT_VERSION_NUMBER in varchar2,
56 x_SECURITY_GROUP_ID in varchar2,
57 x_SCA_MAPPING_STATUS in varchar2,
58 x_CN_ROLL_SUM_TRX in varchar2,
59 x_CN_CUSTOM_AGGR_TRX in varchar2,
60 x_CN_PRIOR_ADJUSTMENT in varchar2,
61 x_CN_COMM_RATE_PRECISION in varchar2,
62 x_CN_CONVERSION_TYPE in varchar2,
63 x_CN_CUSTOM_FLAG in varchar2,
64 x_CN_COLLECT_ON_ACCT_CREDITS in varchar2,
65 x_CN_RAM_NEGATE in varchar2,
66 x_CN_REPORTING_HIERARCHY in varchar2,
67 x_CN_NON_REVENUE_SPLIT in varchar2,
68 x_CN_RESET_ERROR_TRX in varchar2,
69 x_CN_CUST_DISCLAIMER in varchar2,
70 x_CN_DISPLAY_DRAW in varchar2,
71 x_APPLICATION_SHORT_NAME in varchar2,
72 x_OWNER in varchar2 )
73 IS
74 BEGIN
75 if (x_upload_mode = 'NLS') then
76 --CN_REPOSITORIES_PKG.TRANSLATE_ROW(x_owner);
77 -- As this ldt is not required to loaded translated data, you could leave it blank.
78 null;
79 else
80 CN_REPOSITORIES_PKG.LOAD_ROW(
81 x_REPOSITORY_ID,
82 x_VERSION,
83 x_SCHEMA,
84 x_STATUS,
85 x_APPLICATION_TYPE,
86 x_LAST_UPDATE_DATE,
87 x_LAST_UPDATED_BY,
88 x_CREATION_DATE,
89 x_CREATED_BY,
90 x_LAST_UPDATE_LOGIN,
91 x_DESCRIPTION,
92 x_DATABASE_LINK ,
93 x_USAGE_FLAG,
94 x_EMAIL_OWNER,
95 x_EMAIL_DBA,
96 x_CURRENT_PERIOD_ID,
97 x_SYSTEM_START_DATE,
98 x_SYSTEM_START_PERIOD_ID,
99 x_SYSTEM_END_DATE,
100 x_SYSTEM_END_PERIOD_ID,
101 x_REV_CLASS_HIERARCHY_ID,
102 x_SRP_ROLLUP_HIERARCHY_ID,
103 x_SYSTEM_BATCH_SIZE,
104 x_TRANSFER_BATCH_SIZE,
105 x_SRP_ROLLUP_FLAG,
106 x_SET_OF_BOOKS_ID,
107 x_CURRENT_PERIOD,
108 x_SYSTEM_START_PERIOD,
109 x_SYSTEM_END_PERIOD,
110 x_CLAWBACK_GRACE_DAYS,
111 x_TRX_ROLLUP_METHOD,
112 x_SRP_BATCH_SIZE,
113 x_NAME,
114 x_APPLICATION_ID,
115 x_REPOSITORY_TYPE,
116 x_ORG_ID,
117 x_CLS_PACKAGE_SIZE,
118 x_SALESREP_BATCH_SIZE,
119 x_LATEST_PROCESSED_DATE,
120 x_PERIOD_SET_ID,
121 x_PERIOD_TYPE_ID,
122 x_PAYABLES_FLAG,
123 x_PAYROLL_FLAG,
124 x_PAYABLES_CCID_LEVEL,
125 x_INCOME_PLANNER_DISCLAIMER,
126 x_OBJECT_VERSION_NUMBER,
127 x_SECURITY_GROUP_ID,
128 x_SCA_MAPPING_STATUS,
129 x_CN_ROLL_SUM_TRX,
130 x_CN_CUSTOM_AGGR_TRX,
131 x_CN_PRIOR_ADJUSTMENT,
132 x_CN_COMM_RATE_PRECISION,
133 x_CN_CONVERSION_TYPE,
134 x_CN_CUSTOM_FLAG,
135 x_CN_COLLECT_ON_ACCT_CREDITS,
136 x_CN_RAM_NEGATE,
137 x_CN_REPORTING_HIERARCHY,
138 x_CN_NON_REVENUE_SPLIT,
139 x_CN_RESET_ERROR_TRX,
140 x_CN_CUST_DISCLAIMER,
141 x_CN_DISPLAY_DRAW,
142 x_APPLICATION_SHORT_NAME,
143 x_OWNER);
144 null;
145 end if;
146 END LOAD_SEED_ROW;
147
148
149 PROCEDURE LOAD_ROW (
150 x_REPOSITORY_ID in varchar2,
151 x_VERSION in varchar2,
152 x_SCHEMA in varchar2,
153 x_STATUS in varchar2,
154 x_APPLICATION_TYPE in varchar2,
155 x_LAST_UPDATE_DATE in varchar2,
156 x_LAST_UPDATED_BY in varchar2,
157 x_CREATION_DATE in varchar2,
158 x_CREATED_BY in varchar2,
159 x_LAST_UPDATE_LOGIN in varchar2,
160 x_DESCRIPTION in varchar2,
161 x_DATABASE_LINK in varchar2,
162 x_USAGE_FLAG in varchar2,
163 x_EMAIL_OWNER in varchar2,
164 x_EMAIL_DBA in varchar2,
165 x_CURRENT_PERIOD_ID in varchar2,
166 x_SYSTEM_START_DATE in varchar2,
167 x_SYSTEM_START_PERIOD_ID in varchar2,
168 x_SYSTEM_END_DATE in varchar2,
169 x_SYSTEM_END_PERIOD_ID in varchar2,
170 x_REV_CLASS_HIERARCHY_ID in varchar2,
171 x_SRP_ROLLUP_HIERARCHY_ID in varchar2,
172 x_SYSTEM_BATCH_SIZE in varchar2,
173 x_TRANSFER_BATCH_SIZE in varchar2,
174 x_SRP_ROLLUP_FLAG in varchar2,
175 x_SET_OF_BOOKS_ID in varchar2,
176 x_CURRENT_PERIOD in varchar2,
177 x_SYSTEM_START_PERIOD in varchar2,
178 x_SYSTEM_END_PERIOD in varchar2,
179 x_CLAWBACK_GRACE_DAYS in varchar2,
180 x_TRX_ROLLUP_METHOD in varchar2,
181 x_SRP_BATCH_SIZE in varchar2,
182 x_NAME in varchar2,
183 x_APPLICATION_ID in varchar2,
184 x_REPOSITORY_TYPE in varchar2,
185 x_ORG_ID in varchar2,
186 x_CLS_PACKAGE_SIZE in varchar2,
187 x_SALESREP_BATCH_SIZE in varchar2,
188 x_LATEST_PROCESSED_DATE in varchar2,
189 x_PERIOD_SET_ID in varchar2,
190 x_PERIOD_TYPE_ID in varchar2,
191 x_PAYABLES_FLAG in varchar2,
192 x_PAYROLL_FLAG in varchar2,
193 x_PAYABLES_CCID_LEVEL in varchar2,
194 x_INCOME_PLANNER_DISCLAIMER in varchar2,
195 x_OBJECT_VERSION_NUMBER in varchar2,
196 x_SECURITY_GROUP_ID in varchar2,
197 x_SCA_MAPPING_STATUS in varchar2,
198 x_CN_ROLL_SUM_TRX in varchar2,
199 x_CN_CUSTOM_AGGR_TRX in varchar2,
200 x_CN_PRIOR_ADJUSTMENT in varchar2,
201 x_CN_COMM_RATE_PRECISION in varchar2,
202 x_CN_CONVERSION_TYPE in varchar2,
203 x_CN_CUSTOM_FLAG in varchar2,
204 x_CN_COLLECT_ON_ACCT_CREDITS in varchar2,
205 x_CN_RAM_NEGATE in varchar2,
206 x_CN_REPORTING_HIERARCHY in varchar2,
207 x_CN_NON_REVENUE_SPLIT in varchar2,
208 x_CN_RESET_ERROR_TRX in varchar2,
209 x_CN_CUST_DISCLAIMER in varchar2,
210 x_CN_DISPLAY_DRAW in varchar2,
211 x_APPLICATION_SHORT_NAME in varchar2,
212 x_OWNER in varchar2 )
213 IS
214 USER_ID NUMBER;
215 BEGIN
216 NULL;
217
218 -- Proceed only when the repository_id is not null
219 if (x_REPOSITORY_ID is NOT NULL) then
220
221 -- Check whether SEED Data or Custom Data you are uploading
222 IF (x_OWNER IS NOT NULL) AND (x_OWNER = 'SEED') THEN
223 USER_ID := 1;
224 ELSE
225 USER_ID := 0;
226 END IF;
227
228 update cn_repositories_all set
229 REPOSITORY_ID = to_number(x_REPOSITORY_ID),
230 VERSION = x_VERSION,
231 SCHEMA = x_SCHEMA,
232 STATUS = x_STATUS,
233 APPLICATION_TYPE = x_APPLICATION_TYPE,
234 LAST_UPDATE_DATE = SYSDATE,
235 LAST_UPDATED_BY = USER_ID,
236 CREATION_DATE = to_date(x_CREATION_DATE,'DD/MM/YYYY'),
237 CREATED_BY = to_number(x_CREATED_BY),
238 LAST_UPDATE_LOGIN = 0,
239 DESCRIPTION = x_DESCRIPTION,
240 DATABASE_LINK = x_DATABASE_LINK ,
241 USAGE_FLAG = x_USAGE_FLAG,
242 EMAIL_OWNER = x_EMAIL_OWNER,
243 EMAIL_DBA = x_EMAIL_DBA,
244 CURRENT_PERIOD_ID = to_number(x_CURRENT_PERIOD_ID),
245 SYSTEM_START_DATE = to_date(x_SYSTEM_START_DATE,'DD/MM/YYYY'),
246 SYSTEM_START_PERIOD_ID = to_number(x_SYSTEM_START_PERIOD_ID),
247 SYSTEM_END_DATE = to_date(x_SYSTEM_END_DATE,'DD/MM/YYYY'),
248 SYSTEM_END_PERIOD_ID = to_number(x_SYSTEM_END_PERIOD_ID),
249 REV_CLASS_HIERARCHY_ID = to_number(x_REV_CLASS_HIERARCHY_ID),
250 SRP_ROLLUP_HIERARCHY_ID = to_number(x_SRP_ROLLUP_HIERARCHY_ID),
251 SYSTEM_BATCH_SIZE = to_number(x_SYSTEM_BATCH_SIZE),
252 TRANSFER_BATCH_SIZE = to_number(x_TRANSFER_BATCH_SIZE),
253 SRP_ROLLUP_FLAG = x_SRP_ROLLUP_FLAG,
254 SET_OF_BOOKS_ID = to_number(x_SET_OF_BOOKS_ID),
255 CURRENT_PERIOD = x_CURRENT_PERIOD,
256 SYSTEM_START_PERIOD = x_SYSTEM_START_PERIOD,
257 SYSTEM_END_PERIOD = x_SYSTEM_END_PERIOD,
258 CLAWBACK_GRACE_DAYS = to_number(x_CLAWBACK_GRACE_DAYS),
259 TRX_ROLLUP_METHOD = x_TRX_ROLLUP_METHOD,
260 SRP_BATCH_SIZE = to_number(x_SRP_BATCH_SIZE),
261 NAME = x_NAME,
262 APPLICATION_ID = to_number(x_APPLICATION_ID),
263 REPOSITORY_TYPE = x_REPOSITORY_TYPE,
264 ORG_ID = to_number(x_ORG_ID),
265 CLS_PACKAGE_SIZE = to_number(x_CLS_PACKAGE_SIZE),
266 SALESREP_BATCH_SIZE = to_number(x_SALESREP_BATCH_SIZE),
267 LATEST_PROCESSED_DATE = to_date(x_LATEST_PROCESSED_DATE,'DD/MM/YYYY'),
268 PERIOD_SET_ID = to_number(x_PERIOD_SET_ID),
269 PERIOD_TYPE_ID = to_number(x_PERIOD_TYPE_ID),
270 PAYABLES_FLAG = x_PAYABLES_FLAG,
271 PAYROLL_FLAG = x_PAYROLL_FLAG,
272 PAYABLES_CCID_LEVEL = x_PAYABLES_CCID_LEVEL,
273 INCOME_PLANNER_DISCLAIMER = x_INCOME_PLANNER_DISCLAIMER,
274 OBJECT_VERSION_NUMBER = to_number(x_OBJECT_VERSION_NUMBER),
275 SECURITY_GROUP_ID = to_number(x_SECURITY_GROUP_ID),
276 SCA_MAPPING_STATUS = x_SCA_MAPPING_STATUS,
277 CN_ROLL_SUM_TRX = x_CN_ROLL_SUM_TRX,
278 CN_CUSTOM_AGGR_TRX = x_CN_CUSTOM_AGGR_TRX,
279 CN_PRIOR_ADJUSTMENT = x_CN_PRIOR_ADJUSTMENT,
280 CN_COMM_RATE_PRECISION = to_number(x_CN_COMM_RATE_PRECISION),
281 CN_CONVERSION_TYPE = x_CN_CONVERSION_TYPE,
282 CN_CUSTOM_FLAG = x_CN_CUSTOM_FLAG,
283 CN_COLLECT_ON_ACCT_CREDITS = x_CN_COLLECT_ON_ACCT_CREDITS,
284 CN_RAM_NEGATE = x_CN_RAM_NEGATE,
285 CN_REPORTING_HIERARCHY = x_CN_REPORTING_HIERARCHY,
286 CN_NON_REVENUE_SPLIT = x_CN_NON_REVENUE_SPLIT,
287 CN_RESET_ERROR_TRX = x_CN_RESET_ERROR_TRX,
288 CN_CUST_DISCLAIMER = x_CN_CUST_DISCLAIMER,
289 CN_DISPLAY_DRAW = x_CN_DISPLAY_DRAW
290 where
291 REPOSITORY_ID = X_REPOSITORY_ID
292 and ORG_ID = X_ORG_ID;
293
294 IF (SQL%NOTFOUND) THEN
295 -- Insert new record to CN_REPOSITORIES_ALL table
296 insert into CN_REPOSITORIES_ALL
297 (
298 REPOSITORY_ID,
299 VERSION,
300 SCHEMA,
301 STATUS,
302 APPLICATION_TYPE,
303 LAST_UPDATE_DATE,
304 LAST_UPDATED_BY,
305 CREATION_DATE,
306 CREATED_BY,
307 LAST_UPDATE_LOGIN,
308 DESCRIPTION,
309 DATABASE_LINK ,
310 USAGE_FLAG,
311 EMAIL_OWNER,
312 EMAIL_DBA,
313 CURRENT_PERIOD_ID,
314 SYSTEM_START_DATE,
315 SYSTEM_START_PERIOD_ID,
316 SYSTEM_END_DATE,
317 SYSTEM_END_PERIOD_ID,
318 REV_CLASS_HIERARCHY_ID,
319 SRP_ROLLUP_HIERARCHY_ID,
320 SYSTEM_BATCH_SIZE,
321 TRANSFER_BATCH_SIZE,
322 SRP_ROLLUP_FLAG,
323 SET_OF_BOOKS_ID,
324 CURRENT_PERIOD,
325 SYSTEM_START_PERIOD,
326 SYSTEM_END_PERIOD,
327 CLAWBACK_GRACE_DAYS,
328 TRX_ROLLUP_METHOD,
329 SRP_BATCH_SIZE,
330 NAME,
331 APPLICATION_ID,
332 REPOSITORY_TYPE,
333 ORG_ID,
334 CLS_PACKAGE_SIZE,
335 SALESREP_BATCH_SIZE,
336 LATEST_PROCESSED_DATE,
337 PERIOD_SET_ID,
338 PERIOD_TYPE_ID,
339 PAYABLES_FLAG,
340 PAYROLL_FLAG,
341 PAYABLES_CCID_LEVEL,
342 INCOME_PLANNER_DISCLAIMER,
343 OBJECT_VERSION_NUMBER,
344 SECURITY_GROUP_ID,
345 SCA_MAPPING_STATUS,
346 CN_ROLL_SUM_TRX,
347 CN_CUSTOM_AGGR_TRX,
348 CN_PRIOR_ADJUSTMENT,
349 CN_COMM_RATE_PRECISION,
350 CN_CONVERSION_TYPE,
351 CN_CUSTOM_FLAG,
352 CN_COLLECT_ON_ACCT_CREDITS,
353 CN_RAM_NEGATE,
354 CN_REPORTING_HIERARCHY,
355 CN_NON_REVENUE_SPLIT,
356 CN_RESET_ERROR_TRX,
357 CN_CUST_DISCLAIMER,
358 CN_DISPLAY_DRAW)
359
360 values
361 (
362 to_number(x_REPOSITORY_ID),
363 x_VERSION,
364 x_SCHEMA,
365 x_STATUS,
366 x_APPLICATION_TYPE,
367 SYSDATE,
368 USER_ID,
369 SYSDATE,
370 USER_ID,
371 0,
372 x_DESCRIPTION,
373 x_DATABASE_LINK ,
374 x_USAGE_FLAG,
375 x_EMAIL_OWNER,
376 x_EMAIL_DBA,
377 to_number(x_CURRENT_PERIOD_ID),
378 to_date(x_SYSTEM_START_DATE,'DD/MM/YYYY'),
379 to_number(x_SYSTEM_START_PERIOD_ID),
380 to_date(x_SYSTEM_END_DATE,'DD/MM/YYYY'),
381 to_number(x_SYSTEM_END_PERIOD_ID),
382 to_number(x_REV_CLASS_HIERARCHY_ID),
383 to_number(x_SRP_ROLLUP_HIERARCHY_ID),
384 to_number(x_SYSTEM_BATCH_SIZE),
385 to_number(x_TRANSFER_BATCH_SIZE),
386 x_SRP_ROLLUP_FLAG,
387 to_number(x_SET_OF_BOOKS_ID),
388 x_CURRENT_PERIOD,
389 x_SYSTEM_START_PERIOD,
390 x_SYSTEM_END_PERIOD,
391 to_number(x_CLAWBACK_GRACE_DAYS),
392 x_TRX_ROLLUP_METHOD,
393 to_number(x_SRP_BATCH_SIZE),
394 x_NAME,
395 to_number(x_APPLICATION_ID),
396 x_REPOSITORY_TYPE,
397 to_number(x_ORG_ID),
398 to_number(x_CLS_PACKAGE_SIZE),
399 to_number(x_SALESREP_BATCH_SIZE),
400 to_date(x_LATEST_PROCESSED_DATE,'DD/MM/YYYY'),
401 to_number(x_PERIOD_SET_ID),
402 to_number(x_PERIOD_TYPE_ID),
403 x_PAYABLES_FLAG,
404 x_PAYROLL_FLAG,
405 x_PAYABLES_CCID_LEVEL,
406 x_INCOME_PLANNER_DISCLAIMER,
407 to_number(x_OBJECT_VERSION_NUMBER),
408 to_number(x_SECURITY_GROUP_ID),
409 x_SCA_MAPPING_STATUS,
410 x_CN_ROLL_SUM_TRX,
411 x_CN_CUSTOM_AGGR_TRX,
412 x_CN_PRIOR_ADJUSTMENT,
413 to_number(x_CN_COMM_RATE_PRECISION),
414 x_CN_CONVERSION_TYPE,
415 x_CN_CUSTOM_FLAG,
416 x_CN_COLLECT_ON_ACCT_CREDITS,
417 x_CN_RAM_NEGATE,
418 x_CN_REPORTING_HIERARCHY,
419 x_CN_NON_REVENUE_SPLIT,
420 x_CN_RESET_ERROR_TRX,
421 x_CN_CUST_DISCLAIMER,
422 x_CN_DISPLAY_DRAW);
423
424
425 END IF;
426 end if;
427
428 END LOAD_ROW;
429
430
431
432 END cn_repositories_pkg;