1 PACKAGE AMS_CELL_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvcels.pls 120.0 2005/05/31 15:48:15 appldev noship $ */
3
4
5 TYPE cell_rec_type IS RECORD(
6 CELL_ID NUMBER,
7 SEL_TYPE VARCHAR2(30),
8 LAST_UPDATE_DATE DATE,
9 LAST_UPDATED_BY NUMBER,
10 CREATION_DATE DATE,
11 CREATED_BY NUMBER,
12 LAST_UPDATE_LOGIN NUMBER,
13 OBJECT_VERSION_NUMBER NUMBER,
14 CELL_CODE VARCHAR2(30),
15 ENABLED_FLAG VARCHAR2(1),
16 ORIGINAL_SIZE NUMBER,
17 PARENT_CELL_ID NUMBER,
18 ORG_ID NUMBER,
19 OWNER_ID NUMBER,
20 CELL_NAME VARCHAR2(120),
21 DESCRIPTION VARCHAR2(4000),
22 STATUS_CODE VARCHAR2(30),
23 STATUS_DATE DATE,
24 USER_STATUS_ID NUMBER
25 );
26
27 TYPE sqlcell_rec_type IS RECORD(
28 CELL_ID NUMBER,
29 SEL_TYPE VARCHAR2(30),
30 LAST_UPDATE_DATE DATE,
31 LAST_UPDATED_BY NUMBER,
32 CREATION_DATE DATE,
33 CREATED_BY NUMBER,
34 LAST_UPDATE_LOGIN NUMBER,
35 OBJECT_VERSION_NUMBER NUMBER,
36 CELL_CODE VARCHAR2(30),
37 ENABLED_FLAG VARCHAR2(1),
38 ORIGINAL_SIZE NUMBER,
39 PARENT_CELL_ID NUMBER,
40 ORG_ID NUMBER,
41 OWNER_ID NUMBER,
42 CELL_NAME VARCHAR2(120),
43 DESCRIPTION VARCHAR2(4000),
44 STATUS_CODE VARCHAR2(30),
45 STATUS_DATE DATE,
46 USER_STATUS_ID NUMBER,
47 DISCOVERER_SQL_ID NUMBER,
48 WORKBOOK_OWNER VARCHAR2(100),
49 WORKBOOK_NAME VARCHAR2(254),
50 WORKSHEET_NAME VARCHAR2(254),
51 ACTIVITY_DISCOVERER_ID NUMBER,
52 ACT_DISC_VERSION_NUMBER NUMBER,
53 LIST_QUERY_ID NUMBER,
54 LIST_SQL_STRING VARCHAR2(4000),
55 SOURCE_OBJECT_NAME VARCHAR2(60),
56 LIST_QUERY_VERSION_NUMBER NUMBER
57 );
58
59 TYPE t_number is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
60
61 ---------------------------------------------------------------------
62 -- PROCEDURE
63 -- create_cell
64 --
65 -- PURPOSE
66 -- Create a new cell
67 --
68 -- PARAMETERS
69 -- p_cell_rec: the new record to be inserted
70 -- x_cell_id: return the cell_id of the new cell
71 --
72 -- NOTES
73 -- 1. Please don't pass in any FND_API.g_mess_char/num/date.
74 -- 2. object_version_number will be set to 1.
75 -- 3. If cell_id is passed in, the uniqueness will be checked.
76 -- Raise exception in case of duplicates. If cell_id is not
77 -- passed in, generate a unique one from the sequence.
78 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
79 -- Raise exception for invalid flag. If a flag column is not
80 -- passed in, default it to 'Y' or 'N'.
81 -- 5. If the market segmentflag is passed null then it will be defaulted to N
82 -- 6. If the market segmentflag is passed null then it will be defaulted to Y
83 ---------------------------------------------------------------------
84 PROCEDURE create_cell(
85 p_api_version IN NUMBER,
86 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
87 p_commit IN VARCHAR2 := FND_API.g_false,
88 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
89
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2,
93
94 p_cell_rec IN cell_rec_type,
95 x_cell_id OUT NOCOPY NUMBER
96 );
97
98
99 --------------------------------------------------------------------
100 -- PROCEDURE
101 -- delete_cell
102 --
103 -- PURPOSE
104 -- Set the cell to be disabled so that it won't be available
105 -- to users.
106 --
107 -- PARAMETERS
108 -- p_cell_id: the cell_id
109 -- p_object_version: the object_version_number
110 --
111 -- NOTES
112 -- 1. Raise exception if the object_version_number doesn't match.
113 -- 2. Will set the cells to be disabled, instead of remove it
114 -- from database.
115 --------------------------------------------------------------------
116 PROCEDURE delete_cell(
117 p_api_version IN NUMBER,
118 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
119 p_commit IN VARCHAR2 := FND_API.g_false,
120
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2,
124
125 p_cell_id IN NUMBER,
126 p_object_version IN NUMBER
127 );
128
129
130 -------------------------------------------------------------------
131 -- PROCEDURE
132 -- lock_cell
133 --
134 -- PURPOSE
135 -- Lock a celln
136 --
137 -- PARAMETERS
138 -- p_cell_id: the cell_id
139 -- p_object_version: the object_version_number
140 --
141 -- NOTES
142 -- 1. Raise exception if the object_version_number doesn't match.
143 --------------------------------------------------------------------
144 PROCEDURE lock_cell(
145 p_api_version IN NUMBER,
146 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
147
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2,
151
152 p_cell_id IN NUMBER,
153 p_object_version IN NUMBER
154 );
155
156
157 ---------------------------------------------------------------------
158 -- PROCEDURE
159 -- update_cell
160 --
161 -- PURPOSE
162 -- Update a cell
163 --
164 -- PARAMETERS
165 -- p_cell_rec: the record with new items
166 --
167 -- NOTES
168 -- 1. Raise exception if the object_version_number doesn't match.
169 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
170 -- that column won't be updated.
171 ----------------------------------------------------------------------
172 PROCEDURE update_cell(
173 p_api_version IN NUMBER,
174 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
175 p_commit IN VARCHAR2 := FND_API.g_false,
176 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
177
178 x_return_status OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER,
180 x_msg_data OUT NOCOPY VARCHAR2,
181
182 p_cell_rec IN cell_rec_type
183 );
184
185
186 ---------------------------------------------------------------------
187 -- PROCEDURE
188 -- validate_cell
189 --
190 -- PURPOSE
191 -- Validate a cell record.
192 --
193 -- PARAMETERS
194 -- p_cell_rec: the record to be validated
195 --
196 -- NOTES
197 -- 1. p_cell_rec should be the complete cell record wothout
198 -- any FND_API.g_miss_char/num/date items.
199 ----------------------------------------------------------------------
200 PROCEDURE validate_cell(
201 p_api_version IN NUMBER,
202 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
203 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
204
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2,
208
209 p_cell_rec IN cell_rec_type
210 );
211
212
213 ---------------------------------------------------------------------
214 -- PROCEDURE
215 -- check_cell_items
216 --
217 -- PURPOSE
218 -- Perform the item level checking including unique keys,
219 -- required columns, foreign keys, domain constraints.
220 --
221 -- PARAMETERS
222 -- p_cell_rec: the record to be validated
223 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
224 ---------------------------------------------------------------------
225 PROCEDURE check_cell_items(
226 p_cell_rec IN cell_rec_type,
227 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
228 x_return_status OUT NOCOPY VARCHAR2
229 );
230
231
232
233 ---------------------------------------------------------------------
234 -- PROCEDURE
235 -- init_cell_rec
236 --
237 -- PURPOSE
238 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
239 ---------------------------------------------------------------------
240 PROCEDURE init_cell_rec(
241 x_cell_rec OUT NOCOPY cell_rec_type
242 );
243
244
245 ---------------------------------------------------------------------
246 -- PROCEDURE
247 -- complete_cell_rec
248 --
249 -- PURPOSE
250 -- For update_cell, some attributes may be passed in as
251 -- FND_API.g_miss_char/num/date if the user doesn't want to
252 -- update those attributes. This procedure will replace the
253 -- "g_miss" attributes with current database values.
254 --
255 -- PARAMETERS
256 -- p_cell_rec: the record which may contain attributes as
257 -- FND_API.g_miss_char/num/date
258 -- x_complete_rec: the complete record after all "g_miss" items
259 -- have been replaced by current database values
260 --
261 -- NOTES
262 -- 1. If a valid status_date is provided, use it. If not, set it
263 -- to be the original value or SYSDATE depending on whether
264 -- the user_status_id is cellged or not.
265 ---------------------------------------------------------------------
266 PROCEDURE complete_cell_rec(
267 p_cell_rec IN cell_rec_type,
268 x_complete_rec OUT NOCOPY cell_rec_type
269 );
270
271
272 ---------------------------------------------------------------------
273 -- PROCEDURE
274 -- add_sel_workbook
275 --
276 -- PURPOSE
277 -- insert a new entry into table ams_act_discoverer_all
278 -- if the segment selection type is Workbook
279 --
280 -- PARAMETERS
281 -- p_cell_id: the cell_id of the segment
282 -- p_discoverer_sql_id: the discoverer_sql_id of the workbook/worksheet
283 --
284 -- NOTES
285 --
286 ---------------------------------------------------------------------
287 PROCEDURE add_sel_workbook(
288 p_api_version IN NUMBER,
289 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
290 p_commit IN VARCHAR2 := FND_API.g_false,
291 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
292
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_count OUT NOCOPY NUMBER,
295 x_msg_data OUT NOCOPY VARCHAR2,
296
297 p_cell_id IN NUMBER,
298 p_discoverer_sql_id IN NUMBER
299 );
300
301 ---------------------------------------------------------------------
302 -- PROCEDURE
303 -- add_sel_sql
304 --
305 -- HISTORY
306 -- 02/02/01 yxliu Created.
307 ---------------------------------------------------------------------
308 PROCEDURE add_sel_sql(
309 p_api_version IN NUMBER,
310 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
311 p_commit IN VARCHAR2 := FND_API.g_false,
312 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
313
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2,
317
318 p_cell_id IN NUMBER,
319 p_cell_name IN VARCHAR2,
320 p_cell_code IN VARCHAR2,
321 p_sql_string IN VARCHAR2,
322 p_source_object_name IN VARCHAR2
323 );
324
325 ---------------------------------------------------------------------
326 -- PROCEDURE
327 -- get_single_sql
328 --
329 -- PURPOSE
330 -- Get all the sql statements for one segment
331 --
332 -- PARAMETERS
333 -- p_cell_id: the cell_id of the segment
334 -- x_sql_string: return the sql statements for the segment
335 --
336 -- NOTES
337 -- 1. Select type from ams_cells_all_b where cell_id = p_cell_id
338 -- 2. if type is DISCOVERER, select workbook_name, worksheet_name from
339 -- ams_act_discoverer_all where ac_discoverer_used_by_id = p_cell_id,
340 -- Then use the workbook info to get the sql string from
341 -- ams_discoverer_sql
342 -- 3. If type is SQL, get SQL string from ams_list_queries_all where
343 -- act_list_query_used_by = p_cell_id
344 ---------------------------------------------------------------------
345 PROCEDURE get_single_sql(
346 p_api_version IN NUMBER,
347 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
348 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
349
350 x_return_status OUT NOCOPY VARCHAR2,
354 p_cell_id IN NUMBER,
351 x_msg_count OUT NOCOPY NUMBER,
352 x_msg_data OUT NOCOPY VARCHAR2,
353
355 x_sql_string OUT NOCOPY VARCHAR2
356 );
357
358 ---------------------------------------------------------------------
359 -- PROCEDURE
360 -- get_comp_sql
361 --
362 -- PURPOSE
363 -- Get intersection sql statement for one segment and all its
364 -- ancestors
365 --
366 -- PARAMETERS
367 -- p_cell_id: the cell_id of the segment
368 -- p_party_id_only: only keep the party_id column in the select part of
369 -- current cell's sql? TRUE is yes, FALSE is no.
370 -- x_sql_tbl: return a PL/SQL table hold the compose sql statements
371 -- for the segment and all its ancestors
372 --
373 -- NOTES
374 -- comment out for rosetta's sake
375 ---------------------------------------------------------------------
376 PROCEDURE get_comp_sql(
377 p_api_version IN NUMBER,
378 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
379 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
380
381 x_return_status OUT NOCOPY VARCHAR2,
382 x_msg_count OUT NOCOPY NUMBER,
383 x_msg_data OUT NOCOPY VARCHAR2,
384
385 p_cell_id IN NUMBER,
386 p_party_id_only IN VARCHAR2 := FND_API.g_false,
387 x_sql_tbl OUT NOCOPY DBMS_SQL.VARCHAR2S
388 );
389
390 ---------------------------------------------------------------------
391 -- PROCEDURE
392 -- format_sql_string
393 --
394 -- PURPOSE
395 -- Take the input sql string, manipulate it so that party_id is the
396 -- only column in the select clause, all the substring after FROM
397 -- of the original string is not altered.
398 --
399 -- PARAMETERS
400 -- p_string: the input sql string
401 -- x_string: the output sql string is keeps all the part after 'FROM'
402 -- but have only PARTY_ID or ams_table.PARTY_ID in the
403 -- select clause. i.e., SELECT *.party_id FROM ....
404 -- for the segment and all its ancestors
405 --
406 -- NOTES
407 ---------------------------------------------------------------------
408
409 procedure format_sql_string
410 (
411 p_string IN VARCHAR2,
412 x_string OUT NOCOPY VARCHAR2
413 );
414
415
416 ---------------------------------------------------------------------
417 -- PROCEDURE
418 -- format_sql_string
419 --
420 -- PURPOSE
421 -- Take the input sql string, manipulate it so that party_id is the
422 -- only column in the select clause, all the substring after FROM
423 -- of the original string is not altered.
424 --
425 -- PARAMETERS
426 -- p_string: the input sql string
427 -- x_string: the output sql string is keeps all the part after 'FROM'
428 -- but have only PARTY_ID or ams_table.PARTY_ID in the
429 -- select clause. i.e., SELECT *.party_id FROM ....
430 -- for the segment and all its ancestors
431 ---- x_party_id_string:
432 -- ams_table.PARTY_ID
433 -- for the segment and all its ancestors
434 -- NOTES
435 ---------------------------------------------------------------------
436
437 procedure format_sql_string
438 (
439 p_string IN VARCHAR2,
440 x_string OUT NOCOPY VARCHAR2,
441 x_party_id_string OUT NOCOPY VARCHAR2
442 );
443 ---------------------------------------------------------------------
444 -- PROCEDURE
445 -- get_workbook_sql
446 --
447 -- HISTORY
448 -- 03/01/2001 yxliu Created.
449 ---------------------------------------------------------------------
450 PROCEDURE get_workbook_sql(
451 p_api_version IN NUMBER,
452 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
453 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
454
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2,
458
459 p_disc_sql_id IN NUMBER,
460 x_sql_string OUT NOCOPY VARCHAR2
461 );
462 ---------------------------------------------------------------------
463 -- PROCEDURE
464 -- get_segment_size
465 --
466 -- HISTORY
467 -- 03/01/2001 yxliu Created.
468 ---------------------------------------------------------------------
469 PROCEDURE get_segment_size(
470 p_api_version IN NUMBER,
471 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
472 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
473
474 x_return_status OUT NOCOPY VARCHAR2,
475 x_msg_count OUT NOCOPY NUMBER,
476 x_msg_data OUT NOCOPY VARCHAR2,
477
478 p_sql_string IN VARCHAR2,
479 x_segment_size OUT NOCOPY NUMBER
480 );
481
482 ---------------------------------------------------------------------
483 -- PROCEDURE
484 -- get_comp_segment_size
485 --
486 -- HISTORY
487 -- 04/16/2001 yxliu Created. using the get_comp_sql to get the segment
488 -- size which means segment and all its ancestors'
492 p_api_version IN NUMBER,
489 -- criteria.
490 ---------------------------------------------------------------------
491 PROCEDURE get_comp_segment_size(
493 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
494 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
495
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER,
498 x_msg_data OUT NOCOPY VARCHAR2,
499
500 p_cell_id IN NUMBER,
501 x_segment_size OUT NOCOPY NUMBER
502 );
503
504 ---------------------------------------------------------------------
505 -- PROCEDURE
506 -- create_sql_cell
507 --
508 -- HISTORY
509 -- 03/01/01 yxliu created, create a segment and add entries into
510 -- corresponding mapping tables
511 ---------------------------------------------------------------------
512 PROCEDURE create_sql_cell(
513 p_api_version IN NUMBER,
514 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
515 p_commit IN VARCHAR2 := FND_API.g_false,
516 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
517
518 x_return_status OUT NOCOPY VARCHAR2,
519 x_msg_count OUT NOCOPY NUMBER,
520 x_msg_data OUT NOCOPY VARCHAR2,
521
522 p_sql_cell_rec IN sqlcell_rec_type,
523 x_cell_id OUT NOCOPY NUMBER
524 );
525
526 ---------------------------------------------------------------------
527 -- PROCEDURE
528 -- update_sql_cell
529 --
530 -- HISTORY
531 -- 03/01/01 yxliu created, update the segment and based on the sel_type,
532 -- update the corresponding mapping tables
533 ---------------------------------------------------------------------
534 PROCEDURE update_sql_cell(
535 p_api_version IN NUMBER,
536 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
537 p_commit IN VARCHAR2 := FND_API.g_false,
538 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
539
540 x_return_status OUT NOCOPY VARCHAR2,
541 x_msg_count OUT NOCOPY NUMBER,
542 x_msg_data OUT NOCOPY VARCHAR2,
543
544 p_sql_cell_rec IN sqlcell_rec_type
545 );
546
547 /*****************************************************************************/
548 -- Procedure
549 -- Update_Segment_Size
550 --
551 -- Purpose
552 -- This procedure will calculate the segment size for
553 --
554 -- Notes
555 --
556 --
557 -- History
558 -- 04/09/2001 yxliu created
559 ------------------------------------------------------------------------------
560 PROCEDURE Update_Segment_Size
561 ( p_cell_id IN NUMBER DEFAULT NULL,
562 x_return_status OUT NOCOPY VARCHAR2,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_msg_data OUT NOCOPY VARCHAR2
565 );
566
567 /*****************************************************************************/
568 -- Procedure
569 -- Refresh_Segment_Size
570 --
571 -- Purpose
572 -- This procedure is created to as a concurrent program which
573 -- will call the update_segment_size and will return errors if any
574 --
575 -- Notes
576 --
577 --
578 -- History
579 -- 04/09/2001 yxliu created
580 -- 06/20/2001 yxliu moved to package AMS_Party_Mkt_Seg_Loader_PVT
581 ------------------------------------------------------------------------------
582
583 --PROCEDURE Refresh_Segment_Size
584 --( errbuf OUT NOCOPY VARCHAR2,
585 -- retcode OUT NUMBER,
586 -- p_cell_id IN NUMBER DEFAULT NULL
587 --);
588
589 END AMS_CEll_PVT;
[Home] [Help]