[Home] [Help]
PACKAGE BODY: APPS.AMS_DMSELECTION_PVT
Source
1 PACKAGE BODY AMS_DMSelection_PVT AS
2 /* $Header: amsvdslb.pls 120.3 2006/07/17 12:03:37 kbasavar noship $ */
3 ---------------------------------------------------------------
4 -- Note
5 -- Need to add handling of size constraints: min, max, random
6 --
7 -- History
8 -- 22-Feb-2001 choang Created.
9 -- 23-Feb-2001 choang Added schedule preview and aggregation.
10 -- 25-Feb-2001 choang Fixed join condition for CSCH.
11 -- 03-Mar-2001 choang Added implementation of min, max, nth
12 -- row, and random selection options.
13 -- 05-Mar-2001 choang 1) decreased size of seed number for random
14 -- num generator. 2) call purge procedure before
15 -- and after main process. 3) fixed nth row by
16 -- passing nth row instead of pct random. 4)
17 -- used bind vars in populate_using_sql.
18 -- 06-Mar-2001 choang Added workbook_owner_name so query uses
19 -- index against ams_discoverer_sql.
20 -- 18-Apr-2001 choang Added semicolon after exit for standards.
21 -- 22-Jul-2001 choang Replaced select party_id from list_entries with
22 -- select list_entry_source_system_id.
23 -- 31-Aug-2001 choang Changed logic for getting responses.
24 -- 10-Oct-2001 choang Changed logic for CSCH to use ams_act_lists
25 -- to identify the schedule using a specific list.
26 -- 21-Nov-2001 choang Fixed problem with using wrong arc qualifier
27 -- when updating list select actions with preview
28 -- results.
29 -- 26-Nov-2001 choang arc qualifier needs to use both object used by
30 -- and included object in preview.
31 -- 22-Jan-2002 choang Fixed bug 2190920: target group filtered with
32 -- enabled_flag.
33 -- 07-Jun-2002 choang Modified to support data mining data sources.
34 -- 04-Jul-2002 choang Fixed target value update in populate target
35 -- staging and changed logic for loyalty indicator.
36 -- 14-Jul-2002 choang Modified populate source to include total records
37 -- and total positives (for models).
38 -- 01-Aug-2002 choang - Fixed get where when checking for nulls.
39 -- - Added perz filter logic
40 -- 08-Oct-2002 nyostos Added get_target_positive_values to take
41 -- care of multiple positive target values with
42 -- comparison operators.
43 -- 18-Oct-2002 nyostos Fixed a problem with get_where_clause for
44 -- Scoring Runs with Alternative Data Sources
45 -- 21-Oct-2002 choang Fixed problem updating total records and total
46 -- positives.
47 -- 22-Oct-2002 nyostos Added data checks in order to stop Model Build/
48 -- Score Run if there is no data or if the data
49 -- is invalid (e.g. no positive targets)
50 -- 27-Oct-2002 choang Moved get_target_positive_values to spec
51 -- 06-Dec-2002 choang Fixed get_where_clause comparison with null.
52 -- 19-Jun-2003 rosharma Bug # 3004453.
53 -- 18-Jul-2003 kbasavar Bug # 3004437.
54 -- 06-Aug-2003 kbasavar For Customer Profitability model.
55 -- 20-Aug-2003 rosharma Bug # 3102421.
56 -- 12-Sep-2003 kbasavar For Product Affinity.
57 -- 15-Sep-2003 nyostos Changes for parallel mining operations using Global
58 -- Temporary Tables.
59 -- 19-Sep-2003 rosharma Changes for Audience Data Sources Uptake
60 -- 22-Sep-2003 nyostos Fixed GSCC Failure (line longer than 255 characters).
61 -- 19-Sep-2003 rosharma Changes to is_b2b_data_source
62 -- 31-Oct-2003 kbasavar Changes to populate_targets to handle B2B Customer
63 -- Profitbility model for List data Source uptake
64 -- 02-Nov-2003 kbasavar Changed to use categories table instead of view
65 -- for performance
66 -- 06-Nov-2003 rosharma Renamed ams_dm_org_contacts_stg to ams_dm_org_contacts
67 -- 21-Nov-2003 choang bug 3275817 - changed to not exists and having clause
68 -- 27-Nov-2003 rosharma Fixed ambiguous column issue when cell or diwb is included in training data
69 -- 02-Dec-2003 rosharma Bug # 3290898
70 -- 09-Dec-2003 kbasavar Added is_org_prod_affn for Org Product Affinity Model
71 -- 23-Jan-2004 rosharma Bug # 3390720
72 -- 26-Jan-2004 choang Fixed date format mask introduced in bug 3004453
73 -- 05-Feb-2004 rosharma Fixed bug # 3390720
74 -- 12-Feb-2004 rosharma Fixed bug # 3436093
75 -- 18-Feb-2004 rosharma Fixed bug # 3448905
76 -- 13-May-2004 rosharma Fixed bug # 3619647
77 -- 16-Jul-2004 rosharma Fixed bug # 3771444
78 -- 28-Jul-2004 rosharma Fixed bug # 3762677. Changed all instances of ams_list_entries.list_entry_source_system_id
79 -- to ams_list_entries.party_id
80 -- 23-Dec-2004 kbasavar For bug 3935517Taken care of purging the underlying org. contacts
81 -- table for Customer Profitability Score.
82 -- 03-Jan-2005 kbasavar Fixed appsperf bug # 4099354
83 -- 22-Feb-2005 srivikri Fixed bug # 4196941. handled the scenario for no where clause.
84 -- 17-Mar-2005 srivikri bug # 4196941. removed redundancy in get_where_clause and get_wb_filter
85 -------------------------------------------------------------
86 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_DMSelection_PVT';
87 G_OBJECT_TYPE_MODEL CONSTANT VARCHAR2(30) := 'MODL';
88 G_OBJECT_TYPE_SCORE CONSTANT VARCHAR2(30) := 'SCOR';
89 G_ALTERNATIVE_DATA_SOURCE CONSTANT VARCHAR2(30) := 'ADS';
90
91 G_STATUS_BUILDING CONSTANT VARCHAR2(30) := 'BUILDING';
92 G_STATUS_SCORING CONSTANT VARCHAR2(30) := 'SCORING';
93
94 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
95 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
96 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
97
98 ---- forward procedure declarations ----
99
100 ---------------------------------------------------------------
101 -- Purpose:
102 -- Insert parties into ams_dm_target_stg_gt using
103 -- different methods based on the data source
104 -- type.
105 -- Parameter:
106 --
107 ---------------------------------------------------------------
108 PROCEDURE populate_target_staging (
109 p_arc_object_for IN VARCHAR2,
110 p_object_for_id IN NUMBER,
111 p_seeded_data_source IN BOOLEAN,
112 x_return_status OUT NOCOPY VARCHAR2
113 );
114
115 ---------------------------------------------------------------
116 -- Purpose:
117 -- Purge records from the staging table for
118 -- the specified object.
119 -- Parameter:
120 --
121 ---------------------------------------------------------------
122 PROCEDURE purge_target_staging (
123 p_arc_object_for IN VARCHAR2,
124 p_object_for_id IN NUMBER,
125 p_arc_object IN VARCHAR2,
126 p_object_id IN NUMBER,
127 p_count IN VARCHAR2,
128 x_return_status OUT NOCOPY VARCHAR2
129 );
130
131 ---------------------------------------------------------------
132 -- Purpose:
133 -- Update the selections with the summarized results
134 -- for total selected and total targets.
135 -- Parameter:
136 --
137 ---------------------------------------------------------------
138 PROCEDURE update_action_summary (
139 p_arc_object_for IN VARCHAR2,
140 p_object_for_id IN NUMBER,
141 p_seeded_data_source IN BOOLEAN,
142 x_return_status OUT NOCOPY VARCHAR2
143 );
144
145 ---------------------------------------------------------------
146 -- Purpose:
147 -- Calculate the target field value and update
148 -- the staging table.
149 -- Parameter:
150 --
151 ---------------------------------------------------------------
152 PROCEDURE populate_targets (
153 p_model_id IN NUMBER,
154 p_seeded_data_source IN BOOLEAN,
155 x_return_status OUT NOCOPY VARCHAR2
156 );
157
158 ---------------------------------------------------------------
159 -- Purpose:
160 -- Insert ams_dm_source with the parties and
161 -- their respective target values, if the
162 -- operation is model building.
163 -- Parameter:
164 --
165 ---------------------------------------------------------------
166 PROCEDURE populate_source (
167 p_arc_object_for IN VARCHAR2,
168 p_object_for_id IN NUMBER,
169 p_seeded_data_source IN BOOLEAN,
170 x_return_status OUT NOCOPY VARCHAR2
171 );
172
173 ---------------------------------------------------------------
174 -- Purpose:
175 -- Insert into the staging table using SQL from
176 -- a Discoverer workbook.
177 -- Parameter:
178 --
179 ---------------------------------------------------------------
180 PROCEDURE populate_using_sql (
181 p_arc_object_for IN VARCHAR2,
182 p_object_for_id IN NUMBER,
183 p_discoverer_sql_id IN NUMBER, -- used to get workbook name and worksheet
184 x_return_status OUT NOCOPY VARCHAR2
185 );
186
187 ---------------------------------------------------------------
188 -- Purpose:
189 -- Extract the customer field from a SQL statement
190 -- formatted for use in list generation. The customer
191 -- field is associated to the source type of the SQL.
192 -- Parameter:
193 --
194 ---------------------------------------------------------------
195 PROCEDURE get_customer_field (
196 p_workbook_owner_name IN VARCHAR2,
197 p_workbook_name IN VARCHAR2,
198 p_worksheet_name IN VARCHAR2,
199 x_customer_field OUT NOCOPY VARCHAR2,
200 x_return_status OUT NOCOPY VARCHAR2
201 );
202
203 ---------------------------------------------------------------
204 -- Purpose:
205 -- Extract the from and where clause of a SQL
206 -- statement.
207 -- Parameter:
208 --
209 ---------------------------------------------------------------
210 PROCEDURE get_from_sql (
211 p_workbook_owner_name IN VARCHAR2,
212 p_workbook_name IN VARCHAR2,
213 p_worksheet_name IN VARCHAR2,
214 x_from_sql OUT NOCOPY VARCHAR2,
215 x_found OUT NOCOPY VARCHAR2,
216 x_return_status OUT NOCOPY VARCHAR2
217 );
218
219
220 ---------------------------------------------------------------
221 -- Purpose:
222 -- Update ams_dm_source with the target value of the
223 -- parties selected for model training.
224 -- Parameter:
225 --
226 ---------------------------------------------------------------
227 PROCEDURE update_source_target (
228 p_object_type IN VARCHAR2,
229 p_object_id IN NUMBER,
230 x_return_status OUT NOCOPY VARCHAR2
231 );
232
233
234 ---------------------------------------------------------------
235 -- Purpose:
236 -- Apply sizing options for the source selections.
237 ---------------------------------------------------------------
238 PROCEDURE apply_sizing_options (
239 p_arc_object_for IN VARCHAR2,
240 p_object_for_id IN NUMBER,
241 x_return_status OUT NOCOPY VARCHAR2
242 );
243
244
245 ---------------------------------------------------------------
246 -- Purpose:
247 -- Randomize records by returning a percent of the total
248 -- records or the maximum records to return, whichever
249 -- is smaller.
250 -- Parameter:
251 -- IN
252 -- p_arc_object_for - the arc sys qualifier of the object using the
253 -- source selections.
254 -- p_object_for_id - the id of the object using the source selections.
255 -- p_min_rows - the minimum number of records to populate in the
256 -- source table for the datamining engine.
257 -- p_max_rows - the maximum number of records to populate in the
258 -- source table for the datamining engine.
259 -- p_total_rows - the total number of rows available to be processed.
260 -- p_pct_random - the percent of rows to return after randomization.
261 -- OUT
262 -- x_return_status - return status of the procedure.
263 --
264 ---------------------------------------------------------------
265 PROCEDURE randomize_by_pct (
266 p_arc_object_for IN VARCHAR2,
267 p_object_for_id IN NUMBER,
268 p_min_rows IN NUMBER,
269 p_max_rows IN NUMBER,
270 p_total_rows IN NUMBER,
271 p_pct_random IN NUMBER,
272 x_return_status OUT NOCOPY VARCHAR2
273 );
274
275
276 ---------------------------------------------------------------
277 -- Purpose:
278 -- Randomize records by returning every nth row of the original
279 -- dataset up until the max number of rows.
280 --
281 -- Parameter:
282 -- IN
283 -- p_arc_object_for - the arc sys qualifier of the object using the
284 -- source selections.
285 -- p_object_for_id - the id of the object using the source selections.
286 -- p_min_rows - the minimum number of records to populate in the
287 -- source table for the datamining engine.
288 -- p_max_rows - the maximum number of records to populate in the
289 -- source table for the datamining engine.
290 -- p_total_rows - the total number of rows available to be processed.
291 -- p_every_nth_row - the nth row to select for populate of the
292 -- source table.
293 -- OUT
294 -- x_return_status - return status of the procedure.
295 --
296 ---------------------------------------------------------------
297 PROCEDURE randomize_nth_rows (
298 p_arc_object_for IN VARCHAR2,
299 p_object_for_id IN NUMBER,
300 p_min_rows IN NUMBER,
301 p_max_rows IN NUMBER,
302 p_total_rows IN NUMBER,
303 p_every_nth_row IN NUMBER,
304 x_return_status OUT NOCOPY VARCHAR2
305 );
306
307
308 ---------------------------------------------------------------
309 -- Purpose:
310 -- Retrieve the selected fields for model building.
311 --
312 -- Parameter:
313 -- p_select_object_type - ADS is for alternative data source
314 -- p_select_object_id - if ADS, then data source ID
315 -- p_workbook_owner
316 -- p_workbook_name
317 -- p_worksheet_name
318 -- x_insert_fields
319 -- x_return_status
320 ---------------------------------------------------------------
321 PROCEDURE get_insert_fields (
322 p_select_object_type IN VARCHAR2,
323 p_select_object_id IN NUMBER,
324 p_workbook_owner IN VARCHAR2,
325 p_workbook_name IN VARCHAR2,
326 p_worksheet_name IN VARCHAR2,
327 x_insert_fields OUT NOCOPY VARCHAR2,
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_pk_field OUT NOCOPY VARCHAR2
330 );
331
332
333 ---------------------------------------------------------------
334 -- Purpose:
335 -- Get the listing of tables where the data selection
336 -- is retrieved.
337 --
338 -- Parameter:
339 -- p_select_object_type - ADS is for alternative data source
340 -- p_select_object_id - if ADS, then data source ID
341 -- p_workbook_owner
342 -- p_workbook_name
343 -- p_worksheet_name
344 -- x_from_clause
345 -- x_return_status
346 ---------------------------------------------------------------
347 PROCEDURE get_from_clause (
348 p_select_object_type IN VARCHAR2,
349 p_select_object_id IN NUMBER,
350 p_workbook_owner IN VARCHAR2,
351 p_workbook_name IN VARCHAR2,
352 p_worksheet_name IN VARCHAR2,
353 p_is_b2b_custprof IN BOOLEAN,
354 x_from_clause OUT NOCOPY VARCHAR2,
355 x_return_status OUT NOCOPY VARCHAR2
356 );
357
358
359 ---------------------------------------------------------------
360 -- Purpose:
361 -- Get the filter for one selected data source.
362 --
363 -- Parameter:
364 -- p_object_type
365 -- p_object_id
366 -- p_select_object_type
367 -- p_select_object_id
368 -- x_where_clause
369 -- x_return_status
370 ---------------------------------------------------------------
371 PROCEDURE get_where_clause (
372 p_object_type IN VARCHAR2,
373 p_object_id IN NUMBER,
374 p_select_object_type IN VARCHAR2,
375 p_select_object_id IN NUMBER,
376 p_workbook_owner IN VARCHAR2,
377 p_workbook_name IN VARCHAR2,
378 p_worksheet_name IN VARCHAR2,
379 p_is_b2b_custprof IN BOOLEAN,
380 x_where_clause OUT NOCOPY VARCHAR2,
381 x_return_status OUT NOCOPY VARCHAR2
382 );
383
384
385 ---------------------------------------------------------------
386 -- Purpose:
387 -- Get filter conditions based on saved filters
388 -- using the personzliation framework.
389 --
390 -- Parameter:
391 -- p_object_type
392 -- p_object_id
393 -- x_filter
394 -- x_return_status OUT VARCHAR2
395 ---------------------------------------------------------------
396 PROCEDURE get_perz_filter (
397 p_object_type IN VARCHAR2,
398 p_object_id IN NUMBER,
399 p_data_source_id IN NUMBER,
400 x_filter OUT NOCOPY VARCHAR2,
401 x_return_status OUT NOCOPY VARCHAR2
402 );
403
404
405 ---------------------------------------------------------------
406 -- Purpose:
407 -- Get filter conditions as defined by a Discoverer
408 -- worksheet.
409 --
410 -- NOTE
411 -- Discoverer SQL statements could span across multiple
412 -- ams_discoverer_sql records. Must use combination of
413 -- owner, workbook, and worksheet to query table for
414 -- complete sql statement.
415 --
416 -- Parameter:
417 -- p_workbook_owner
418 -- p_workbook_name
419 -- p_worksheet_name
420 -- x_filter
421 -- x_return_status
422 ---------------------------------------------------------------
423 PROCEDURE get_wb_filter (
424 p_workbook_owner IN VARCHAR2,
425 p_workbook_name IN VARCHAR2,
426 p_worksheet_name IN VARCHAR2,
427 x_filter OUT NOCOPY VARCHAR2,
428 x_return_status OUT NOCOPY VARCHAR2
429 );
430
431 ---------------------------------------------------------------
432 -- Purpose:
433 -- Determine whether a target is attached to
434 -- a seeded DM data source
435 --
436 -- Parameter:
437 -- p_target_id IN NUMBER
438 -- x_is_seeded OUT BOOLEAN
439 ---------------------------------------------------------------
440
441 PROCEDURE is_target_attached_to_seeded (
442 p_target_id IN NUMBER,
443 x_is_seeded OUT NOCOPY BOOLEAN
444 );
445
446 ---------------------------------------------------------------
447 -- Purpose:
448 -- Check the status of selections to ensure that they are
449 -- still valid. Only called for seeded models.
450 --
451 -- NOTE:
452 --
453 --
454 -- Parameter:
455 -- p_model_id
456 -- p_model_type
457 -- p_workbook_owner
458 -- p_workbook_name
459 -- p_worksheet_name
460 -- p_select_object_type
461 -- p_select_object_id
462 -- x_return_status
463 ---------------------------------------------------------------
464 PROCEDURE validate_selection_status (
465 p_model_id IN NUMBER,
466 p_model_type IN VARCHAR2,
467 p_workbook_owner IN VARCHAR2,
468 p_workbook_name IN VARCHAR2,
469 p_worksheet_name IN VARCHAR2,
470 p_select_object_type IN VARCHAR2,
471 p_select_object_id IN NUMBER,
472 x_return_status OUT NOCOPY VARCHAR2
473 );
474
475 ---------------------------------------------------------------
476 -- Purpose:
477 -- Check the status of product selections for prod affn model to ensure that they are
478 -- still valid. Only called for seeded models.
479 --
480 -- NOTE:
481 --
482 --
483 -- Parameter:
484 -- p_model_id
485 -- x_return_status
486 ---------------------------------------------------------------
487 PROCEDURE validate_product_selections (
488 p_model_id IN NUMBER,
489 x_return_status OUT NOCOPY VARCHAR2
490 );
491 ---- procedure code -----
492
493 ---------------------------------------------------------------
494 -- History
495 -- 15-Feb-2001 choang Created.
496 ---------------------------------------------------------------
497 PROCEDURE Preview_Selections (
498 p_arc_object IN VARCHAR2,
499 p_object_id IN NUMBER,
500 x_return_status OUT NOCOPY VARCHAR2
501 )
502 IS
503 L_API_NAME CONSTANT VARCHAR2(30) := 'Preview Selections';
504 L_SEEDED_ID_THRESHOLD CONSTANT NUMBER := 10000;
505
506 CURSOR c_model (p_model_id IN NUMBER) IS
507 SELECT target_id
508 FROM ams_dm_models_all_b
509 WHERE model_id = p_model_id
510 ;
511
512 CURSOR c_score (p_score_id IN NUMBER) IS
513 SELECT model.target_id
514 FROM ams_dm_scores_all_b score, ams_dm_models_all_b model
515 WHERE score.score_id = p_score_id
516 AND model.model_id = score.model_id
517 ;
518
519 l_target_id NUMBER;
520 l_seeded_data_source BOOLEAN := FALSE;
521 l_target_attached_to_seeded BOOLEAN := FALSE;
522 l_msg_count NUMBER;
523 l_msg_data VARCHAR2(2000);
524
525 l_return_status VARCHAR2(1);
526 l_return_status_log VARCHAR2(1);
527 BEGIN
528 -- Initialize API return status to SUCCESS
529 x_return_status := FND_API.G_RET_STS_SUCCESS;
530
531 AMS_Utility_PVT.create_log (
532 x_return_status => l_return_status_log,
533 p_arc_log_used_by => p_arc_object,
534 p_log_used_by_id => p_object_id,
535 p_msg_data => L_API_NAME || ': Begin'
536 );
537
538 FND_MSG_PUB.initialize;
539
540 IF (AMS_DEBUG_HIGH_ON) THEN
541 AMS_Utility_PVT.debug_message ('OBJECT TYPE: ' || p_arc_object || ' OBJECT ID: ' || p_object_id);
542 END IF;
543
544 -- determine if the data source used is
545 -- a seeded data source
546 IF p_arc_object = 'MODL' THEN
547 OPEN c_model(p_object_id);
548 FETCH c_model INTO l_target_id;
549 CLOSE c_model;
550 ELSE
551 OPEN c_score(p_object_id);
552 FETCH c_score INTO l_target_id;
553 CLOSE c_score;
554 END IF;
555
556 IF l_target_id < L_SEEDED_ID_THRESHOLD THEN
557 l_seeded_data_source := TRUE;
558 END IF;
559
560 AMS_Utility_PVT.create_log (
561 x_return_status => l_return_status_log,
562 p_arc_log_used_by => p_arc_object,
563 p_log_used_by_id => p_object_id,
564 p_msg_data => L_API_NAME || ': Before call to purge_target_staging '
565 );
566
567 -- Initialize staging tables
568 -- If previous execution fails, the purge procedure
569 -- doesn't get called, so the old data remains, which
570 -- may cause some problems in processing.
571 purge_target_staging (
572 p_arc_object_for => p_arc_object,
573 p_object_for_id => p_object_id,
574 p_arc_object => p_arc_object,
575 p_object_id => p_object_id,
576 p_count => 'INITIAL',
577 x_return_status => l_return_status
578 );
579
580 AMS_Utility_PVT.create_log (
581 x_return_status => l_return_status_log,
582 p_arc_log_used_by => p_arc_object,
583 p_log_used_by_id => p_object_id,
584 p_msg_data => L_API_NAME || ': After purge_target_staging Status= ' || l_return_status
585 );
586
587 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
589 END IF;
590
591 -- populate the staging table by going
592 -- against all the different data sources.
593 populate_target_staging (
594 p_arc_object_for => p_arc_object,
595 p_object_for_id => p_object_id,
596 p_seeded_data_source => l_seeded_data_source,
597 x_return_status => l_return_status
598 );
599
600 AMS_Utility_PVT.create_log (
601 x_return_status => l_return_status_log,
602 p_arc_log_used_by => p_arc_object,
603 p_log_used_by_id => p_object_id,
604 p_msg_data => L_API_NAME || ': After populate_target_staging Status= ' || l_return_status
605 );
606
607 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609 END IF;
610
611 -- Only need to summarize the targeted column if
612 -- process is model building -- target value is
613 -- not needed when scoring.
614 IF p_arc_object = G_OBJECT_TYPE_MODEL THEN
615
616 AMS_Utility_PVT.create_log (
617 x_return_status => l_return_status_log,
618 p_arc_log_used_by => p_arc_object,
619 p_log_used_by_id => p_object_id,
620 p_msg_data => L_API_NAME || ': MODL Going to call populate_targets '
621 );
622
623 is_target_attached_to_seeded (
624 p_target_id => l_target_id,
625 x_is_seeded => l_target_attached_to_seeded
626 );
627 IF l_seeded_data_source = FALSE AND l_target_attached_to_seeded = TRUE THEN
628 AMS_DMExtract_pvt.ExtractMain (
629 p_api_version => 1.0
630 , p_init_msg_list => FND_API.g_false
631 , p_commit => FND_API.g_true
632 , x_return_status => l_return_status
633 , x_msg_count => l_msg_count
634 , x_msg_data => l_msg_data
635 , p_mode => 'I'
636 , p_model_id => p_object_id
637 , p_model_type => p_arc_object
638 );
639 END IF;
640 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 END IF;
643 populate_targets (
644 p_model_id => p_object_id,
645 p_seeded_data_source => l_seeded_data_source,
646 x_return_status => l_return_status
647 );
648
649 AMS_Utility_PVT.create_log (
650 x_return_status => l_return_status_log,
651 p_arc_log_used_by => p_arc_object,
652 p_log_used_by_id => p_object_id,
653 p_msg_data => L_API_NAME || ': After populate_targets Status= ' || l_return_status
654 );
655
656 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
658 END IF;
659 END IF;
660
661 -- Update the list select actions table
662 -- with the summarized data, specifically,
663 -- the no_of_rows_used and no_of_rows_targeted.
664 update_action_summary (
665 p_arc_object_for => p_arc_object,
666 p_object_for_id => p_object_id,
667 p_seeded_data_source => l_seeded_data_source,
668 x_return_status => l_return_status
669 );
670
671
672 AMS_Utility_PVT.create_log (
673 x_return_status => l_return_status_log,
674 p_arc_log_used_by => p_arc_object,
675 p_log_used_by_id => p_object_id,
676 p_msg_data => L_API_NAME || ': After update_action_summary Status= ' || l_return_status
677 );
678
679 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
681 END IF;
682
683 -- Insert into ams_dm_source a distinct set of parties
684 -- with target values where applicable. If a party has
685 -- different target values, the positive takes precedence.
686 populate_source (
687 p_arc_object_for => p_arc_object,
688 p_object_for_id => p_object_id,
689 p_seeded_data_source => l_seeded_data_source,
690 x_return_status => l_return_status
691 );
692 AMS_Utility_PVT.create_log (
693 x_return_status => l_return_status_log,
694 p_arc_log_used_by => p_arc_object,
695 p_log_used_by_id => p_object_id,
696 p_msg_data => L_API_NAME || ': After populate_source Status= ' || l_return_status
697 );
698
699
700 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702 END IF;
703
704 -- purge the data from all staging tables.
705 purge_target_staging (
706 p_arc_object_for => p_arc_object,
707 p_object_for_id => p_object_id,
708 p_arc_object => p_arc_object,
709 p_object_id => p_object_id,
710 p_count => 'FINAL',
711 x_return_status => l_return_status
712 );
713
714 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
715 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
716 END IF;
717
718
719 EXCEPTION
720 WHEN FND_API.G_EXC_ERROR THEN
721 x_return_status := FND_API.G_RET_STS_ERROR;
722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
724 WHEN OTHERS THEN
725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
727 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
728 END IF;
729 END Preview_Selections;
730
731
732 ---------------------------------------------------------------
733 -- History
734 -- 15-Feb-2001 choang Created.
735 ---------------------------------------------------------------
736 PROCEDURE Aggregate_Selections (
737 p_arc_object IN VARCHAR2,
738 p_object_id IN NUMBER,
739 x_return_status OUT NOCOPY VARCHAR2
740 )
741 IS
742 L_API_NAME CONSTANT VARCHAR2(30) := 'Aggregate Selections';
743 BEGIN
744 -- Initialize API return status to SUCCESS
745 x_return_status := FND_API.G_RET_STS_SUCCESS;
746
747 -- Re-perform the preview selections to
748 -- generate the source data set. If user
749 -- has removed some selections, we cannot
750 -- currently detect that, so to be safe,
751 -- call the process again.
752 Preview_Selections (
753 p_arc_object => p_arc_object,
754 p_object_id => p_object_id,
755 x_return_status => x_return_status
756 );
757 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759 END IF;
760
761 /* - choang - 04-jun-2002 - callout replaced with logic in populate_source
762 and populate_targets
763 update_source_target (
764 p_object_type => p_arc_object,
765 p_object_id => p_object_id,
766 x_return_status => x_return_status
767 );
768 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
770 END IF;
771 */
772 EXCEPTION
773 /*
774 WHEN FND_API.G_EXC_ERROR THEN
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
778 */
779 WHEN OTHERS THEN
780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
781 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
782 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
783 END IF;
784 END Aggregate_Selections;
785
786
787 --
788 -- NOTE
789 -- ams_dm_target_stg_gt used for calculating the summary of all the selected
790 -- data sources.
791 -- History
792 -- 16-Feb-2001 choang Created.
793 -- 06-Jun-2002 choang Alternative data source has different filter
794 -- conditions.
795 PROCEDURE populate_target_staging (
796 p_arc_object_for IN VARCHAR2,
797 p_object_for_id IN NUMBER,
798 p_seeded_data_source IN BOOLEAN,
799 x_return_status OUT NOCOPY VARCHAR2
800 )
801 IS
802 L_API_NAME CONSTANT VARCHAR2(30) := 'populate_target_staging';
803
804 CURSOR c_objects (p_arc_object_for IN VARCHAR2, p_object_for_id IN NUMBER) IS
805 SELECT arc_incl_object_from,
806 incl_object_id
807 FROM ams_list_select_actions
808 WHERE arc_action_used_by = p_arc_object_for
809 AND action_used_by_id = p_object_for_id
810 ;
811 l_object_rec c_objects%ROWTYPE;
812
813 CURSOR c_workbook (p_discoverer_sql_id IN NUMBER) IS
814 SELECT workbook_name,
815 worksheet_name,
816 workbook_owner_name
817 FROM ams_discoverer_sql
818 WHERE discoverer_sql_id = p_discoverer_sql_id
819 ;
820 l_workbook_rec c_workbook%ROWTYPE;
821
822 CURSOR c_model (p_model_id IN NUMBER) IS
823 SELECT target.data_source_id , target.target_id
824 FROM ams_dm_models_all_b model, ams_dm_targets_b target
825 WHERE model.model_id = p_model_id
826 AND target.target_id = model.target_id
827 ;
828
829 CURSOR c_score (p_score_id IN NUMBER) IS
830 SELECT target.data_source_id , target.target_id
831 FROM ams_dm_scores_all_b score, ams_dm_models_all_b model, ams_dm_targets_b target
832 WHERE score.score_id = p_score_id
833 AND model.model_id = score.model_id
834 AND target.target_id = model.target_id
835 ;
836
837 CURSOR c_model_type(p_model_id IN NUMBER) is
838 SELECT model_type
839 FROM ams_dm_models_vl
840 WHERE model_id=p_model_id
841 ;
842
843 CURSOR c_model_type_scor(p_scor_id IN NUMBER) is
844 SELECT model_id, model_type
845 FROM ams_dm_models_vl
846 WHERE model_id=(select model_id from ams_dm_scores_vl where score_id=p_scor_id)
847 ;
848
849 l_data_source_id NUMBER;
850 l_target_id NUMBER;
851 l_seeded_data_source BOOLEAN := FALSE;
852
853 l_insert_clause VARCHAR2(16000);
854 l_insert_fields VARCHAR2(16000);
855 l_from_clause VARCHAR2(4000);
856 l_where_clause VARCHAR2(32767);
857
858 l_sql_statement VARCHAR2(32767);
859
860 l_model_id NUMBER;
861 l_model_type VARCHAR2(30);
862 l_is_b2b BOOLEAN;
863 l_pk_field VARCHAR2(200);
864 l_insert_string VARCHAR2(32000);
865 l_is_b2b_custprof BOOLEAN := FALSE;
866 BEGIN
867 -- Initialize API return status to SUCCESS
868 x_return_status := FND_API.G_RET_STS_SUCCESS;
869
870 l_model_id:=p_object_for_id;
871
872 IF p_arc_object_for = 'MODL' THEN
873 OPEN c_model_type(p_object_for_id);
874 FETCH c_model_type INTO l_model_type;
875 CLOSE c_model_type;
876 ELSE
877 OPEN c_model_type_scor(p_object_for_id);
878 FETCH c_model_type_scor INTO l_model_id,l_model_type;
879 CLOSE c_model_type_scor;
880 END IF;
881
882 is_b2b_data_source(
883 p_model_id => l_model_id,
884 x_is_b2b => l_is_b2b
885 );
886
887
888 -- nyostos - Sep 15, 2003 - Use Global Temporart Table
889 --l_insert_clause := 'INSERT INTO ams_dm_target_stg (arc_object_used_by, ';
890 l_insert_clause := 'INSERT INTO ams_dm_target_stg_gt (arc_object_used_by, ';
891 l_insert_clause := l_insert_clause || 'object_used_by_id, arc_object, ';
892 l_insert_clause := l_insert_clause || 'object_id, party_id) SELECT ';
893
894 IF p_seeded_data_source THEN
895
896 IF l_is_b2b AND l_model_type='CUSTOMER_PROFITABILITY' THEN
897 -- nyostos - Sep 15, 2003 - Use Global Temporart Table
898 -- l_insert_clause := 'INSERT INTO ams_dm_org_contacts_stg (arc_object_used_by, ';
899 -- l_insert_clause := 'INSERT INTO ams_dm_orgcont_stg_gt (arc_object_used_by, ';
900 l_insert_clause := 'INSERT INTO ams_dm_org_contacts (arc_object_used_by, ';
901 l_insert_clause := l_insert_clause || 'object_used_by_id, arc_object, ';
902 l_insert_clause := l_insert_clause || 'object_id, party_id, org_party_id) SELECT ';
903 l_is_b2b_custprof := TRUE;
904 END IF;
905
906
907 IF l_model_type='PRODUCT_AFFINITY' THEN
908 validate_product_selections (
909 p_model_id => l_model_id,
910 x_return_status => x_return_status
911 );
912 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
913 RAISE FND_API.G_EXC_ERROR;
914 END IF;
915 END IF;
916 -- process all the source selections
917 FOR l_object_rec IN c_objects (p_arc_object_for, p_object_for_id) LOOP
918 -- get_wb_filter relies on l_workbook_owner to identify if
919 -- filters are returned based on workbook (non-seeded data sources)
920 l_workbook_rec.workbook_owner_name := NULL;
921 l_workbook_rec.workbook_name := NULL;
922 l_workbook_rec.worksheet_name := NULL;
923 IF l_object_rec.arc_incl_object_from = 'DIWB' THEN
924 OPEN c_workbook (l_object_rec.incl_object_id);
925 FETCH c_workbook INTO l_workbook_rec;
926 CLOSE c_workbook;
927 END IF;
928
929 validate_selection_status (
930 p_model_id => l_model_id,
931 p_model_type => l_model_type,
932 p_workbook_owner => l_workbook_rec.workbook_owner_name,
933 p_workbook_name => l_workbook_rec.workbook_name,
934 p_worksheet_name => l_workbook_rec.worksheet_name,
935 p_select_object_type => l_object_rec.arc_incl_object_from,
936 p_select_object_id => l_object_rec.incl_object_id,
937 x_return_status => x_return_status
938 );
939 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
940 RAISE FND_API.G_EXC_ERROR;
941 END IF;
942
943 get_insert_fields (
944 p_select_object_type => l_object_rec.arc_incl_object_from,
945 p_select_object_id => l_object_rec.incl_object_id,
946 p_workbook_owner => l_workbook_rec.workbook_owner_name,
947 p_workbook_name => l_workbook_rec.workbook_name,
948 p_worksheet_name => l_workbook_rec.worksheet_name,
949 x_insert_fields => l_insert_fields,
950 x_return_status => x_return_status,
951 x_pk_field => l_pk_field
952 );
953 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
955 END IF;
956
957 get_from_clause (
958 p_select_object_type => l_object_rec.arc_incl_object_from,
959 p_select_object_id => l_object_rec.incl_object_id,
960 p_workbook_owner => l_workbook_rec.workbook_owner_name,
961 p_workbook_name => l_workbook_rec.workbook_name,
962 p_worksheet_name => l_workbook_rec.worksheet_name,
963 p_is_b2b_custprof => l_is_b2b_custprof,
964 x_from_clause => l_from_clause,
965 x_return_status => x_return_status
966 );
967 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
969 END IF;
970
971 get_where_clause (
972 p_object_type => p_arc_object_for,
973 p_object_id => p_object_for_id,
974 p_select_object_type => l_object_rec.arc_incl_object_from,
975 p_select_object_id => l_object_rec.incl_object_id,
976 p_workbook_owner => l_workbook_rec.workbook_owner_name,
977 p_workbook_name => l_workbook_rec.workbook_name,
978 p_worksheet_name => l_workbook_rec.worksheet_name,
979 p_is_b2b_custprof => l_is_b2b_custprof,
980 x_where_clause => l_where_clause,
981 x_return_status => x_return_status
982 );
983 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
985 END IF;
986
987 -- kbasavar For Customer Profitability
988 IF l_is_b2b AND l_model_type='CUSTOMER_PROFITABILITY' THEN
989 l_insert_fields := l_insert_fields || ', hpr.object_id ';
990 l_from_clause :=l_from_clause || ',hz_relationships hpr';
991 IF l_where_clause IS NOT NULL THEN
992 l_where_clause :=l_where_clause || ' AND hpr.party_id='|| l_pk_field;
993 ELSE
994 l_where_clause :=' hpr.party_id='|| l_pk_field;
995 END IF;
996 l_where_clause := l_where_clause || ' AND hpr.directional_flag=''F'' AND hpr.subject_table_name = ''HZ_PARTIES''';
997 l_where_clause := l_where_clause || ' AND hpr.object_table_name = ''HZ_PARTIES'' AND hpr.directional_flag = ''F''';
998 l_where_clause := l_where_clause || ' AND hpr.relationship_code IN (''CONTACT_OF'' , ''EMPLOYEE_OF'')';
999 END IF;
1000
1001 l_sql_statement := l_insert_clause || l_insert_fields ||
1002 ' FROM ' || l_from_clause;
1003 IF l_where_clause IS NOT NULL THEN
1004 l_sql_statement := l_sql_statement || ' WHERE ' || l_where_clause;
1005 END IF;
1006
1007 IF (AMS_DEBUG_HIGH_ON) THEN
1008 AMS_Utility_PVT.debug_message (substr(L_API_NAME || ' - SQL [' || l_object_rec.arc_incl_object_from || ', ' || l_object_rec.incl_object_id || ']: ' || l_sql_statement,1,4000));
1009 END IF;
1010
1011 IF l_object_rec.arc_incl_object_from <> 'DIWB' THEN
1012 EXECUTE IMMEDIATE l_sql_statement
1013 USING p_arc_object_for, p_object_for_id, l_object_rec.arc_incl_object_from,
1014 l_object_rec.incl_object_id, l_object_rec.incl_object_id;
1015 ELSE
1016 EXECUTE IMMEDIATE l_sql_statement
1017 USING p_arc_object_for, p_object_for_id, l_object_rec.arc_incl_object_from,
1018 l_object_rec.incl_object_id;
1019 END IF;
1020 END LOOP; -- for cursor
1021
1022 IF l_is_b2b AND l_model_type='CUSTOMER_PROFITABILITY' THEN
1023 -- l_insert_string := 'INSERT INTO ams_dm_target_stg_gt(arc_object_used_by,object_used_by_id,arc_object,object_id,party_id) ';
1024 -- l_insert_string := l_insert_string || 'SELECT arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id ';
1025 -- l_insert_string := l_insert_string || 'FROM ams_dm_orgcont_stg_gt ';
1026 -- l_insert_string := l_insert_string || 'GROUP BY arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id ';
1027 EXECUTE IMMEDIATE 'INSERT INTO ams_dm_target_stg_gt(arc_object_used_by,object_used_by_id,arc_object,object_id,party_id)
1028 SELECT arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id
1029 FROM ams_dm_org_contacts GROUP BY arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id';
1030 END IF;
1031
1032 ELSE -- alternative data source
1033 IF p_arc_object_for = 'MODL' THEN
1034 OPEN c_model(p_object_for_id);
1035 FETCH c_model INTO l_data_source_id , l_target_id;
1036 CLOSE c_model;
1037 ELSE
1038 OPEN c_score(p_object_for_id);
1039 FETCH c_score INTO l_data_source_id , l_target_id;
1040 CLOSE c_score;
1041 END IF;
1042
1043 get_insert_fields (
1044 p_select_object_type => G_ALTERNATIVE_DATA_SOURCE,
1045 p_select_object_id => l_data_source_id,
1046 p_workbook_owner => l_workbook_rec.workbook_owner_name,
1047 p_workbook_name => l_workbook_rec.workbook_name,
1048 p_worksheet_name => l_workbook_rec.worksheet_name,
1049 x_insert_fields => l_insert_fields,
1050 x_return_status => x_return_status,
1051 x_pk_field => l_pk_field
1052 );
1053 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1055 END IF;
1056
1057 get_from_clause (
1058 p_select_object_type => G_ALTERNATIVE_DATA_SOURCE,
1059 p_select_object_id => l_target_id,
1060 p_workbook_owner => l_workbook_rec.workbook_owner_name,
1061 p_workbook_name => l_workbook_rec.workbook_name,
1062 p_worksheet_name => l_workbook_rec.worksheet_name,
1063 p_is_b2b_custprof => l_is_b2b_custprof,
1064 x_from_clause => l_from_clause,
1065 x_return_status => x_return_status
1066 );
1067 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1069 END IF;
1070
1071 -- discoverer workbook can be used as a filter
1072 OPEN c_objects (p_arc_object_for, p_object_for_id);
1073 FETCH c_objects INTO l_object_rec;
1074 IF c_objects%ROWCOUNT = 1 THEN
1075 l_workbook_rec.workbook_owner_name := NULL;
1076 l_workbook_rec.workbook_name := NULL;
1077 l_workbook_rec.worksheet_name := NULL;
1078 IF l_object_rec.arc_incl_object_from = 'DIWB' THEN
1079 OPEN c_workbook (l_object_rec.incl_object_id);
1080 FETCH c_workbook INTO l_workbook_rec;
1081 CLOSE c_workbook;
1082 END IF;
1083 END IF;
1084 CLOSE c_objects;
1085
1086 get_where_clause (
1087 p_object_type => p_arc_object_for,
1088 p_object_id => p_object_for_id,
1089 p_select_object_type => G_ALTERNATIVE_DATA_SOURCE,
1090 p_select_object_id => l_data_source_id,
1091 p_workbook_owner => l_workbook_rec.workbook_owner_name,
1092 p_workbook_name => l_workbook_rec.workbook_name,
1093 p_worksheet_name => l_workbook_rec.worksheet_name,
1094 p_is_b2b_custprof => l_is_b2b_custprof,
1095 x_where_clause => l_where_clause,
1096 x_return_status => x_return_status
1097 );
1098 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100 END IF;
1101
1102 l_sql_statement := l_insert_clause || l_insert_fields ||
1103 ' FROM ' || l_from_clause;
1104 IF l_where_clause IS NOT NULL THEN
1105 l_sql_statement := l_sql_statement || ' WHERE ' || l_where_clause;
1106 END IF;
1107
1108 IF (AMS_DEBUG_HIGH_ON) THEN
1109 AMS_Utility_PVT.debug_message (L_API_NAME || ' - SQL: ' || l_sql_statement);
1110 END IF;
1111
1112 EXECUTE IMMEDIATE l_sql_statement
1113 USING p_arc_object_for, p_object_for_id, G_ALTERNATIVE_DATA_SOURCE, l_data_source_id;
1114 END IF;
1115 EXCEPTION
1116 WHEN FND_API.G_EXC_ERROR THEN
1117 x_return_status := FND_API.G_RET_STS_ERROR;
1118 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1120 WHEN OTHERS THEN
1121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1122 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1123 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1124 END IF;
1125 END populate_target_staging;
1126
1127
1128 --
1129 -- History
1130 -- 16-Feb-2001 choang Created.
1131 PROCEDURE populate_using_sql (
1132 p_arc_object_for IN VARCHAR2,
1133 p_object_for_id IN NUMBER,
1134 p_discoverer_sql_id IN NUMBER, -- used to get workbook name and worksheet
1135 x_return_status OUT NOCOPY VARCHAR2
1136 )
1137 IS
1138 L_API_NAME CONSTANT VARCHAR2(30) := 'Populate Using SQL';
1139
1140 l_workbook_owner_name VARCHAR2(100);
1141 l_workbook_name VARCHAR2(254);
1142 l_worksheet_name VARCHAR2(254);
1143 l_source_pk_field VARCHAR2(61);
1144
1145 -- variable used to capture the from and
1146 -- where clause of the sql statement.
1147 l_from_and_where VARCHAR2(32000);
1148 l_found VARCHAR2(1);
1149
1150 -- dynamic sql handler
1151 l_cursor INTEGER;
1152
1153 CURSOR c_workbook (p_discoverer_sql_id IN NUMBER) IS
1154 SELECT workbook_name,
1155 worksheet_name,
1156 workbook_owner_name
1157 FROM ams_discoverer_sql
1158 WHERE discoverer_sql_id = p_discoverer_sql_id
1159 ;
1160 BEGIN
1161 -- Initialize API return status to SUCCESS
1162 x_return_status := FND_API.G_RET_STS_SUCCESS;
1163
1164 OPEN c_workbook (p_discoverer_sql_id);
1165 FETCH c_workbook INTO l_workbook_name, l_worksheet_name, l_workbook_owner_name;
1166 CLOSE c_workbook;
1167
1168 get_customer_field (
1169 p_workbook_owner_name => l_workbook_owner_name,
1170 p_workbook_name => l_workbook_name,
1171 p_worksheet_name => l_worksheet_name,
1172 x_customer_field => l_source_pk_field,
1173 x_return_status => x_return_status
1174 );
1175 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177 END IF;
1178
1179 get_from_sql (
1180 p_workbook_owner_name => l_workbook_owner_name,
1181 p_workbook_name => l_workbook_name,
1182 p_worksheet_name => l_worksheet_name,
1183 x_from_sql => l_from_and_where,
1184 x_found => l_found,
1185 x_return_status => x_return_status
1186 );
1187 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1189 END IF;
1190
1191 IF (AMS_DEBUG_HIGH_ON) THEN
1192 AMS_Utility_PVT.debug_message (L_API_NAME || ': ' || l_from_and_where);
1193 END IF;
1194
1195 -- construct the entire sql statement
1196 -- which selects the source_pk_field
1197 -- from the from_and_where
1198 -- Note: SQL must not exceed 32K. Shouldn't
1199 -- have that problem because we are only
1200 -- using the from and where clauses of
1201 -- the SQL from ams_discoverer_sql.
1202 EXECUTE IMMEDIATE
1203 -- 'INSERT INTO ams_dm_target_stg ' ||
1204 'INSERT INTO ams_dm_target_stg_gt ' ||
1205 '(arc_object_used_by, object_used_by_id, arc_object, object_id, party_id) ' ||
1206 'SELECT :arc_object' || ', :object_id' || ', :disco_wb, :disco_id' || ', ' || l_source_pk_field ||
1207 ' ' || l_from_and_where
1208 USING p_arc_object_for, p_object_for_id, 'DIWB', p_discoverer_sql_id;
1209 EXCEPTION
1210 WHEN FND_API.G_EXC_ERROR THEN
1211 x_return_status := FND_API.G_RET_STS_ERROR;
1212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1214 WHEN OTHERS THEN
1215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1216 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1217 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1218 END IF;
1219 END populate_using_sql;
1220
1221
1222 --
1223 -- NOTE
1224 -- use truncate to avoid fragmentation in db tablespace.
1225 --
1226 -- History
1227 -- 16-Feb-2001 choang Created.
1228 -- 06-Jun-2002 choang Use truncate for performance
1229 PROCEDURE purge_target_staging (
1230 p_arc_object_for IN VARCHAR2,
1231 p_object_for_id IN NUMBER,
1232 p_arc_object IN VARCHAR2,
1233 p_object_id IN NUMBER,
1234 p_count IN VARCHAR2,
1235 x_return_status OUT NOCOPY VARCHAR2
1236 )
1237 IS
1238
1239 CURSOR c_model_type(p_model_id IN NUMBER) is
1240 SELECT model_type
1241 FROM ams_dm_models_vl
1242 WHERE model_id=p_model_id
1243 ;
1244
1245 CURSOR c_model_id (p_score_id IN NUMBER) IS
1246 SELECT model_id
1247 FROM ams_dm_scores_all_b
1248 WHERE score_id = p_score_id
1249 ;
1250
1251 L_API_NAME CONSTANT VARCHAR2(30) := 'Purge Target Staging';
1252 l_result BOOLEAN;
1253 l_status VARCHAR2(10);
1254 l_industry VARCHAR2(10);
1255 l_ams_schema VARCHAR2(30);
1256
1257 l_model_id NUMBER;
1258
1259 l_model_type VARCHAR2(30);
1260 BEGIN
1261 -- Initialize API return status to SUCCESS
1262 x_return_status := FND_API.G_RET_STS_SUCCESS;
1263
1264 l_result := fnd_installation.get_app_info(
1265 'AMS',
1266 l_status,
1267 l_industry,
1268 l_ams_schema
1269 );
1270
1271 IF (AMS_DEBUG_HIGH_ON) THEN
1272 AMS_Utility_PVT.debug_message (L_API_NAME || ': ' || p_arc_object_for || ': '||p_object_for_id || ': '|| p_count);
1273 END IF;
1274
1275 if p_arc_object_for = G_OBJECT_TYPE_SCORE then
1276 open c_model_id(p_object_for_id);
1277 fetch c_model_id into l_model_id;
1278 close c_model_id;
1279 else
1280 l_model_id:=p_object_for_id;
1281 end if;
1282
1283 OPEN c_model_type(l_model_id);
1284 FETCH c_model_type into l_model_type;
1285 CLOSE c_model_type;
1286
1287
1288
1289 if l_model_type='CUSTOMER_PROFITABILITY' THEN
1290 -- DELETE FROM ams_dm_org_contacts_stg WHERE ARC_OBJECT_USED_BY = p_arc_object_for AND OBJECT_USED_BY_ID = p_object_for_id;
1291 -- DELETE FROM ams_dm_orgcont_stg_gt WHERE ARC_OBJECT_USED_BY = p_arc_object_for AND OBJECT_USED_BY_ID = p_object_for_id;
1292 if p_count = 'FINAL' and p_arc_object_for = G_OBJECT_TYPE_SCORE then
1293 null;
1294 else
1295 DELETE FROM ams_dm_org_contacts WHERE ARC_OBJECT_USED_BY = p_arc_object_for AND OBJECT_USED_BY_ID = p_object_for_id;
1296 end if;
1297 END IF;
1298
1299 -- EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_ams_schema || '.ams_dm_target_stg';
1300 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_ams_schema || '.ams_dm_target_stg_gt';
1301
1302 -- EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_ams_schema || '.ams_dm_inter_source_stg';
1303 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_ams_schema || '.ams_dm_int_src_stg_gt ';
1304
1305 EXCEPTION
1306 WHEN FND_API.G_EXC_ERROR THEN
1307 x_return_status := FND_API.G_RET_STS_ERROR;
1308 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1310 WHEN OTHERS THEN
1311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1312 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1313 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1314 END IF;
1315 END purge_target_staging;
1316
1317 --
1318 -- History
1319 -- 16-Feb-2001 choang Created.
1320 PROCEDURE get_customer_field (
1321 p_workbook_owner_name IN VARCHAR2,
1322 p_workbook_name IN VARCHAR2,
1323 p_worksheet_name IN VARCHAR2,
1324 x_customer_field OUT NOCOPY VARCHAR2,
1325 x_return_status OUT NOCOPY VARCHAR2
1326 )
1327 IS
1328 L_API_NAME CONSTANT VARCHAR2(30) := 'Get Customer Field';
1329
1330 l_customer_pk_field VARCHAR2(61);
1331 l_source_type_code VARCHAR2(30);
1332
1333 -- variables used to call search_sql_string
1334 l_found VARCHAR2(1);
1335 l_found_in_str NUMBER;
1336 l_position NUMBER;
1337 l_overflow NUMBER;
1338
1339 CURSOR c_master_types (p_workbook_owner_name IN VARCHAR2, p_workbook_name IN VARCHAR2, p_worksheet_name IN VARCHAR2) IS
1340 SELECT a.source_type_code,
1341 a.source_object_name || '.' || a.source_object_pk_field
1342 FROM ams_list_src_types a , ams_discoverer_sql b
1343 WHERE a.master_source_type_flag = 'Y'
1344 AND a.enabled_flag = 'Y'
1345 AND b.workbook_owner_name = p_workbook_owner_name
1346 AND b.workbook_name = p_workbook_name
1347 AND b.worksheet_name = p_worksheet_name
1348 AND a.source_type_code = b.source_type_code
1349 ;
1350 BEGIN
1351 -- Initialize API return status to SUCCESS
1352 x_return_status := FND_API.G_RET_STS_SUCCESS;
1353
1354 -- find the master source type which is used
1355 -- in the workbook. the master source type is
1356 -- needed to identify the pk field to select
1357 -- parties - list gen allows for customer
1358 -- records which are not from TCA, so they
1359 -- do not necessarily have party_id.
1360 OPEN c_master_types(p_workbook_owner_name, p_workbook_name, p_worksheet_name);
1361 LOOP
1362 l_source_type_code := NULL;
1363 l_customer_pk_field := NULL;
1364
1365 FETCH c_master_types INTO l_source_type_code, l_customer_pk_field;
1366
1367 AMS_DiscovererSQL_PVT.search_sql_string (
1368 p_search_string => l_source_type_code,
1369 p_workbook_name => p_workbook_name,
1370 p_worksheet_name => p_worksheet_name,
1371 x_found => l_found,
1372 x_found_in_str => l_found_in_str,
1373 x_position => l_position,
1374 x_overflow => l_overflow
1375 );
1376
1377 EXIT WHEN c_master_types%NOTFOUND OR l_found = FND_API.G_TRUE;
1378 END LOOP;
1379 CLOSE c_master_types;
1380
1381 IF l_found = FND_API.G_FALSE THEN
1382 AMS_Utility_PVT.error_message ('AMS_DM_DIWB_NO_SOURCE_TYPE', 'WORKBOOKSHEET', p_workbook_name || '.' || p_worksheet_name);
1383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1384 END IF;
1385
1386 x_customer_field := l_customer_pk_field;
1387 EXCEPTION
1388 WHEN FND_API.G_EXC_ERROR THEN
1389 x_return_status := FND_API.G_RET_STS_ERROR;
1390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1392 WHEN OTHERS THEN
1393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1394 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1395 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1396 END IF;
1397 END get_customer_field;
1398
1399
1400 --
1401 -- NOTE
1402 -- - add target_value column to AMS_DM_INT_SRC_STG_GT to capture
1403 -- the calculated target_value from populate_target_staging to avoid
1404 -- duplicate effort in update_source_target
1405 -- History
1406 -- 16-Feb-2001 choang Created.
1407 -- 06-jun-2002 choang added support of target_value in AMS_DM_INT_SRC_STG_GT
1408 -- to replace logic of update_source(); added calculation
1409 -- of target value for alternative data sources.
1410 PROCEDURE populate_source (
1411 p_arc_object_for IN VARCHAR2,
1412 p_object_for_id IN NUMBER,
1413 p_seeded_data_source IN BOOLEAN,
1414 x_return_status OUT NOCOPY VARCHAR2
1415 )
1416 IS
1417 L_API_NAME VARCHAR2(30) := 'Populate Source Table';
1418
1419 l_target_value VARCHAR2(30);
1420 l_row_count NUMBER;
1421
1422 CURSOR c_sources (p_arc_object_for IN VARCHAR2, p_object_for_id IN NUMBER) IS
1423 SELECT list_action_type, arc_incl_object_from, incl_object_id
1424 FROM ams_list_select_actions
1425 WHERE arc_action_used_by = p_arc_object_for
1426 AND action_used_by_id = p_object_for_id
1427 ORDER BY order_number
1428 ;
1429 l_sources_rec c_sources%ROWTYPE;
1430
1431 CURSOR c_target_value (p_model_id IN NUMBER) IS
1432 SELECT target_positive_value
1433 FROM ams_dm_models_all_b
1434 WHERE model_id = p_model_id
1435 ;
1436
1437 CURSOR c_recs IS
1438 SELECT COUNT(*),
1439 NVL (SUM (DECODE (target_value, l_target_value, 1, 0)), 0)
1440 -- FROM ams_dm_inter_source_stg
1441 FROM ams_dm_int_src_stg_gt
1442 WHERE arc_object_used_by = p_arc_object_for
1443 AND object_used_by_id = p_object_for_id
1444 AND enabled_flag = 'Y'
1445 ;
1446
1447 CURSOR c_model_status (p_model_id IN NUMBER) IS
1448 SELECT status_code
1449 FROM ams_dm_models_all_b
1450 WHERE model_id = p_model_id;
1451
1452 CURSOR c_score_status (p_score_id IN NUMBER) IS
1453 SELECT status_code
1454 FROM ams_dm_scores_all_b
1455 WHERE score_id = p_score_id;
1456
1457 CURSOR c_model_id (p_score_id IN NUMBER) IS
1458 SELECT model_id
1459 FROM ams_dm_scores_all_b
1460 WHERE score_id = p_score_id
1461 ;
1462
1463 CURSOR c_model_type(p_model_id IN NUMBER) is
1464 SELECT model_type
1465 FROM ams_dm_models_vl
1466 WHERE model_id=p_model_id
1467 ;
1468
1469 l_total_records NUMBER := 0;
1470 l_total_positives NUMBER := 0;
1471 l_status_code VARCHAR2(30);
1472 l_party_type VARCHAR2(30);
1473 l_is_b2b BOOLEAN;
1474 l_is_org_prod_affn BOOLEAN;
1475
1476 l_model_id NUMBER;
1477
1478 l_model_type VARCHAR2(30);
1479
1480 l_userId NUMBER := FND_GLOBAL.user_id;
1481 l_concUserId NUMBER := FND_GLOBAL.conc_login_id;
1482 BEGIN
1483 -- Initialize API return status to SUCCESS
1484 x_return_status := FND_API.G_RET_STS_SUCCESS;
1485
1486 -- purge the existing records
1487 DELETE /*+ index(AMS_DM_SOURCE AMS_DM_SOURCE_U2) */ FROM ams_dm_source
1488 WHERE arc_used_for_object = p_arc_object_for
1489 AND used_for_object_id = p_object_for_id;
1490
1491 -- get the target value if model building
1492 -- and reset the total count
1493 IF p_arc_object_for = G_OBJECT_TYPE_MODEL THEN
1494 OPEN c_target_value (p_object_for_id);
1495 FETCH c_target_value INTO l_target_value;
1496 CLOSE c_target_value;
1497
1498 UPDATE ams_dm_models_all_b
1499 SET total_records = 0,
1500 total_positives = 0
1501 WHERE model_id = p_object_for_id
1502 ;
1503
1504 -- get the Model status code
1505 OPEN c_model_status (p_object_for_id);
1506 FETCH c_model_status INTO l_status_code;
1507 CLOSE c_model_status;
1508
1509 ELSE
1510 UPDATE ams_dm_scores_all_b
1511 SET total_records = 0,
1512 total_positives = 0
1513 WHERE score_id = p_object_for_id
1514 ;
1515
1516 -- get the Score status code
1517 OPEN c_score_status (p_object_for_id);
1518 FETCH c_score_status INTO l_status_code;
1519 CLOSE c_score_status;
1520
1521 END IF;
1522
1523 IF p_seeded_data_source THEN
1524 -- Apply include, exclude and intersect rules
1525 OPEN c_sources (p_arc_object_for, p_object_for_id);
1526 FETCH c_sources INTO l_sources_rec;
1527
1528 l_model_id := p_object_for_id;
1529 IF p_arc_object_for = 'SCOR' THEN
1530 OPEN c_model_id(p_object_for_id);
1531 FETCH c_model_id INTO l_model_id;
1532 CLOSE c_model_id;
1533 END IF;
1534
1535 is_b2b_data_source(
1536 p_model_id => l_model_id,
1537 x_is_b2b => l_is_b2b
1538 );
1539
1540 OPEN c_model_type(l_model_id);
1541 FETCH c_model_type into l_model_type;
1542 CLOSE c_model_type;
1543
1544 is_org_prod_affn (
1545 p_model_id => l_model_id,
1546 x_is_org_prod => l_is_org_prod_affn
1547 );
1548
1549 IF l_is_b2b THEN
1550 IF l_model_type='CUSTOMER_PROFITABILITY' OR l_is_org_prod_affn THEN
1551 l_party_type := 'ORGANIZATION';
1552 ELSE
1553 l_party_type := 'PARTY_RELATIONSHIP';
1554 END IF;
1555 ELSE
1556 l_party_type := 'PERSON';
1557 END IF;
1558
1559
1560 -- The first should be type INCLUDE
1561 -- INSERT INTO ams_dm_inter_source_stg (
1562 INSERT INTO ams_dm_int_src_stg_gt (
1563 arc_object_used_by,
1564 object_used_by_id,
1565 party_id,
1566 enabled_flag,
1567 random_generated_num,
1568 target_value
1569 )
1570 SELECT p_arc_object_for
1571 , p_object_for_id
1572 , t.party_id
1573 , 'N'
1574 , NULL
1575 , DECODE (t.target_flag, 'Y', l_target_value, '0')
1576 -- FROM ams_dm_target_stg t, HZ_PARTIES p
1577 FROM ams_dm_target_stg_gt t, HZ_PARTIES p
1578 WHERE t.arc_object_used_by = p_arc_object_for
1579 AND t.object_used_by_id = p_object_for_id
1580 AND t.arc_object = l_sources_rec.arc_incl_object_from
1581 AND t.object_id = l_sources_rec.incl_object_id
1582 AND t.party_id = p.party_id
1583 AND p.party_type = l_party_type;
1584
1585 LOOP
1586 FETCH c_sources INTO l_sources_rec;
1587 EXIT WHEN c_sources%NOTFOUND;
1588
1589 -- handle include
1590 IF l_sources_rec.list_action_type = 'INCLUDE' THEN
1591 --INSERT INTO ams_dm_inter_source_stg (
1592 INSERT INTO ams_dm_int_src_stg_gt (
1593 arc_object_used_by,
1594 object_used_by_id,
1595 party_id,
1596 enabled_flag,
1597 random_generated_num,
1598 target_value
1599 )
1600 SELECT p_arc_object_for
1601 , p_object_for_id
1602 , t.party_id
1603 , 'N'
1604 , NULL
1605 , DECODE (t.target_flag, 'Y', l_target_value, '0')
1606 -- FROM ams_dm_target_stg t , HZ_PARTIES p
1607 FROM ams_dm_target_stg_gt t , HZ_PARTIES p
1608 WHERE t.arc_object_used_by = p_arc_object_for
1609 AND t.object_used_by_id = p_object_for_id
1610 AND t.arc_object = l_sources_rec.arc_incl_object_from
1611 AND t.object_id = l_sources_rec.incl_object_id
1612 AND t.party_id = p.party_id
1613 AND p.party_type = l_party_type
1614 AND NOT EXISTS (SELECT 1
1615 -- FROM ams_dm_inter_source_stg i
1616 FROM ams_dm_int_src_stg_gt i
1617 WHERE i.arc_object_used_by = t.arc_object_used_by
1618 AND i.object_used_by_id = t.object_used_by_id
1619 AND i.party_id = t.party_id)
1620 ;
1621 -- handle exclude
1622 ELSIF l_sources_rec.list_action_type = 'EXCLUDE' THEN
1623 -- DELETE FROM ams_dm_inter_source_stg i
1624 DELETE FROM ams_dm_int_src_stg_gt i
1625 WHERE arc_object_used_by = p_arc_object_for
1626 AND object_used_by_id = p_object_for_id
1627 AND EXISTS (SELECT 1
1628 FROM ams_dm_target_stg_gt t -- ams_dm_target_stg t
1629 WHERE t.arc_object_used_by = p_arc_object_for
1630 AND t.object_used_by_id = p_object_for_id
1631 AND t.arc_object = l_sources_rec.arc_incl_object_from
1632 AND t.object_id = l_sources_rec.incl_object_id
1633 AND t.party_id = i.party_id)
1634 ;
1635 -- handle intersect
1636 ELSIF l_sources_rec.list_action_type = 'INTERSECT' THEN
1637 -- DELETE FROM ams_dm_inter_source_stg i
1638 DELETE FROM ams_dm_int_src_stg_gt i
1639 WHERE arc_object_used_by = p_arc_object_for
1640 AND object_used_by_id = p_object_for_id
1641 AND NOT EXISTS (SELECT 1
1642 -- FROM ams_dm_target_stg t
1643 FROM ams_dm_target_stg_gt t
1644 WHERE t.arc_object_used_by = p_arc_object_for
1645 AND t.object_used_by_id = p_object_for_id
1646 AND t.arc_object = l_sources_rec.arc_incl_object_from
1647 AND t.object_id = l_sources_rec.incl_object_id
1648 AND t.party_id = i.party_id)
1649 ;
1650 ELSE
1651 AMS_Utility_PVT.error_message ('AMS_DM_UNSUPPORTED_ACTION');
1652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1653 END IF;
1654 END LOOP;
1655 CLOSE c_sources;
1656 ELSE
1657 -- INSERT INTO ams_dm_inter_source_stg (
1658 INSERT INTO ams_dm_int_src_stg_gt (
1659 arc_object_used_by,
1660 object_used_by_id,
1661 party_id,
1662 enabled_flag,
1663 random_generated_num,
1664 target_value
1665 )
1666 SELECT p_arc_object_for
1667 , p_object_for_id
1668 , party_id
1669 , 'N'
1670 , NULL
1671 , DECODE (target_flag, 'Y', l_target_value, '0')
1672 -- FROM ams_dm_target_stg
1673 FROM ams_dm_target_stg_gt
1674 WHERE arc_object_used_by = p_arc_object_for
1675 AND object_used_by_id = p_object_for_id
1676 ;
1677 END IF;
1678
1679 -- Apply size options for selections
1680 apply_sizing_options (
1681 p_arc_object_for => p_arc_object_for,
1682 p_object_for_id => p_object_for_id,
1683 x_return_status => x_return_status
1684 );
1685 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1687 END IF;
1688
1689 -- ASSUMPTION: source_id only comes from ams_dm_source_s
1690
1691 --added rosharma 20-aug-2003 bug # 3102421
1692 BEGIN
1693 --end add rosharma 20-aug-2003 bug # 3102421
1694 INSERT INTO ams_dm_source (
1695 source_id,
1696 last_update_date,
1697 last_updated_by,
1698 creation_date,
1699 created_by,
1700 last_update_login,
1701 object_version_number,
1702 arc_used_for_object,
1703 used_for_object_id,
1704 party_id,
1705 target_value
1706 )
1707 SELECT ams_dm_source_s.NEXTVAL,
1708 SYSDATE,
1709 l_concUserId,
1710 SYSDATE,
1711 l_userId,
1712 l_concUserId,
1713 1,
1714 p_arc_object_for,
1715 p_object_for_id,
1716 party_id,
1717 target_value
1718 -- FROM ams_dm_inter_source_stg
1719 FROM ams_dm_int_src_stg_gt
1720 WHERE arc_object_used_by = p_arc_object_for
1721 AND object_used_by_id = p_object_for_id
1722 AND enabled_flag = 'Y'
1723 ;
1724 --added rosharma 20-aug-2003 bug # 3102421
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 AMS_Utility_PVT.error_message ('AMS_DM_INVALID_PRIMARY_KEY');
1728 RAISE FND_API.G_EXC_ERROR;
1729 END;
1730 --end add rosharma 20-aug-2003 bug # 3102421
1731
1732 -- choang - 12-jul-2002 - logic to populate total records
1733 IF p_arc_object_for = G_OBJECT_TYPE_MODEL THEN
1734 OPEN c_recs;
1735 FETCH c_recs INTO l_total_records, l_total_positives;
1736 CLOSE c_recs;
1737
1738 UPDATE ams_dm_models_all_b
1739 SET total_records = l_total_records
1740 , total_positives = l_total_positives
1741 WHERE model_id = p_object_for_id
1742 ;
1743
1744 -- If model is building, then check that the data can be used for building
1745 IF l_status_code = G_STATUS_BUILDING THEN
1746 IF l_total_records <= 0 THEN
1747 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_MODEL_SELECTIONS_EMPTY');
1748 x_return_status := FND_API.G_RET_STS_ERROR;
1749 RAISE FND_API.G_EXC_ERROR;
1750 END IF;
1751
1752 IF l_total_positives <= 0 THEN
1753 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_MODEL_NO_POSITIVE_TGTS');
1754 x_return_status := FND_API.G_RET_STS_ERROR;
1755 RAISE FND_API.G_EXC_ERROR;
1756 END IF;
1757
1758 IF l_total_positives = l_total_records THEN
1759 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_MODEL_ALL_POSITIVE_TGTS');
1760 x_return_status := FND_API.G_RET_STS_ERROR;
1761 RAISE FND_API.G_EXC_ERROR;
1762 END IF;
1763 END IF;
1764 ELSE
1765 -- use the results of the previous insert statement
1766 l_row_count := SQL%rowcount;
1767 UPDATE ams_dm_scores_all_b
1768 SET total_records = l_row_count
1769 WHERE score_id = p_object_for_id
1770 ;
1771
1772 -- If Scoring Run is scoring, then check that the data can be used for scoring
1773 IF l_status_code = G_STATUS_SCORING THEN
1774 IF l_row_count <= 0 THEN
1775 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORE_SELECTIONS_EMPTY');
1776 x_return_status := FND_API.G_RET_STS_ERROR;
1777 RAISE FND_API.G_EXC_ERROR;
1778 END IF;
1779 END IF;
1780 END IF;
1781
1782 --kbasavar contents of ams_dm_org_contacts will be synchronized with the organizations in ams_dm_source
1783 IF l_is_b2b AND l_model_type='CUSTOMER_PROFITABILITY' AND p_seeded_data_source THEN
1784 -- DELETE FROM ams_dm_org_contacts_stg
1785 -- DELETE FROM ams_dm_orgcont_stg_gt
1786 DELETE FROM ams_dm_org_contacts
1787 -- WHERE org_party_id NOT IN (SELECT distinct party_id from ams_dm_inter_source_stg WHERE arc_object_used_by = p_arc_object_for AND
1788 WHERE org_party_id NOT IN (SELECT distinct party_id from ams_dm_int_src_stg_gt WHERE arc_object_used_by = p_arc_object_for AND
1789 object_used_by_id = p_object_for_id);
1790
1791 IF (AMS_DEBUG_HIGH_ON) THEN
1792 AMS_Utility_PVT.debug_message ('In populate_source synchronize org_cotacts with org');
1793 END IF;
1794
1795 END IF;
1796
1797 END populate_source;
1798
1799
1800 --
1801 -- History
1802 -- 16-Feb-2001 choang Created.
1803 PROCEDURE get_from_sql (
1804 p_workbook_owner_name IN VARCHAR2,
1805 p_workbook_name IN VARCHAR2,
1806 p_worksheet_name IN VARCHAR2,
1807 x_from_sql OUT NOCOPY VARCHAR2,
1808 x_found OUT NOCOPY VARCHAR2,
1809 x_return_status OUT NOCOPY VARCHAR2
1810 )
1811 IS
1812 -- spaces included to ensure searched string is part of from clause
1813 L_FROM_KEYWORD CONSTANT VARCHAR2(30) := 'FROM';
1814 L_API_NAME CONSTANT VARCHAR2(30) := 'Get FROM SQL';
1815
1816 -- variables used to call search_sql_string
1817 l_found VARCHAR2(1);
1818 l_found_in_str NUMBER;
1819 l_position NUMBER;
1820 l_overflow NUMBER;
1821
1822 l_from_sql VARCHAR2(4000);
1823 l_temp_sql VARCHAR2(4000);
1824
1825 CURSOR c_sql (p_str_num IN NUMBER) IS
1826 SELECT sql_string
1827 FROM ams_discoverer_sql
1828 WHERE workbook_owner_name = p_workbook_owner_name
1829 AND workbook_name = p_workbook_name
1830 AND worksheet_name = p_worksheet_name
1831 AND sequence_order >= p_str_num
1832 ORDER BY sequence_order
1833 ;
1834 BEGIN
1835 -- Initialize API return status to SUCCESS
1836 x_return_status := FND_API.G_RET_STS_SUCCESS;
1837
1838 AMS_DiscovererSQL_PVT.search_sql_string (
1839 p_search_string => L_FROM_KEYWORD,
1840 p_workbook_name => p_workbook_name,
1841 p_worksheet_name => p_worksheet_name,
1842 x_found => l_found,
1843 x_found_in_str => l_found_in_str,
1844 x_position => l_position,
1845 x_overflow => l_overflow
1846 );
1847 IF l_found <> FND_API.G_TRUE THEN
1848 AMS_Utility_PVT.error_message ('AMS_SQL_NO_FROM', 'WORKBOOKSHEET', p_workbook_name || '.' || p_worksheet_name);
1849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1850 END IF;
1851
1852 -- fetch the sql string from ams_discoverer_sql
1853 -- and start from the position of FROM. append
1854 -- all subsequent strings to the out variable.
1855 OPEN c_sql (l_found_in_str);
1856 FETCH c_sql INTO l_from_sql;
1857 l_from_sql := SUBSTR (l_from_sql, l_position);
1858 LOOP
1859 FETCH c_sql INTO l_temp_sql;
1860 EXIT WHEN c_sql%NOTFOUND;
1861
1862 l_from_sql := l_from_sql || l_temp_sql;
1863 END LOOP;
1864 CLOSE c_sql;
1865
1866 x_from_sql := l_from_sql;
1867 EXCEPTION
1868 WHEN FND_API.G_EXC_ERROR THEN
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1872 WHEN OTHERS THEN
1873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1874 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1875 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1876 END IF;
1877 END get_from_sql;
1878
1879
1880 --
1881 -- NOTE
1882 -- The original table and process design for data mining data preparation
1883 -- used binary targets, so a Y/N binary set was used. After design was
1884 -- completed, we found out the ODM engine could only handle numeric target
1885 -- values, so the value in ams_dm_source contains 0/1. We convert the Y/N
1886 -- into 0/1 in populate_source().
1887 --
1888 -- History
1889 -- 16-Feb-2001 choang Created.
1890 -- 05-Jun-2002 choang Added logic to handle alternative targets.
1891 --
1892 PROCEDURE populate_targets (
1893 p_model_id IN NUMBER,
1894 p_seeded_data_source IN BOOLEAN,
1895 x_return_status OUT NOCOPY VARCHAR2
1896 )
1897 IS
1898 L_API_NAME CONSTANT VARCHAR2(30) := 'Populate Targets in Staging';
1899 -- NOTE: We don't know the actual code to
1900 -- use because this function is not
1901 -- implemented.
1902 L_NON_LOYAL_CODE CONSTANT VARCHAR2(30) := 'LEFT_FOR_COMPETITOR'; -- replace this with the correct; follow bug 1657447
1903 L_POSITIVE_TARGET_VALUE CONSTANT VARCHAR2(30) := 'Y';
1904 L_NEGATIVE_TARGET_VALUE CONSTANT VARCHAR2(30) := 'N';
1905
1906 CURSOR c_model_details (p_model_id IN NUMBER) IS
1907 SELECT m.model_id
1908 , m.model_type
1909 , m.target_positive_value
1910 , t.target_id
1911 , t.data_source_id
1912 FROM ams_dm_models_all_b m, ams_dm_targets_b t
1913 WHERE m.model_id = p_model_id
1914 AND t.target_id = m.target_id
1915 ;
1916 l_model_rec c_model_details%ROWTYPE;
1917
1918 CURSOR c_target (p_target_id IN NUMBER) IS
1919 SELECT field.source_column_name
1920 , source1.source_object_name
1921 , source2.source_object_name
1922 , source2.source_object_pk_field
1923 , source1.source_object_name||decode(UPPER(source1.remote_flag),'Y','@'||source1.database_link,'')
1924 , source2.source_object_name||decode(UPPER(source2.remote_flag),'Y','@'||source2.database_link,'')
1925 , target.data_source_id
1926 , target.target_source_id
1927 , field.enabled_flag
1928 FROM ams_dm_targets_b target, ams_list_src_fields field, ams_list_src_types source1, ams_list_src_types source2
1929 WHERE target.target_id = p_target_id
1930 AND field.list_source_field_id = source_field_id
1931 AND source2.list_source_type_id = target.data_source_id
1932 AND source1.list_source_type_id = target.target_source_id
1933 ;
1934
1935
1936 l_target_source_object_full VARCHAR2(151);
1937 l_pk_source_object_full VARCHAR2(151);
1938 l_target_source_object VARCHAR2(30);
1939 l_pk_source_object VARCHAR2(30);
1940 l_pk_field VARCHAR2(30);
1941 l_target_field VARCHAR2(30);
1942 l_target_source_id NUMBER;
1943 l_data_source_id NUMBER;
1944 l_target_enabled VARCHAR2(1);
1945
1946 l_seeded_data_source BOOLEAN;
1947 l_sql VARCHAR2(32000);
1948 l_positive_values_sql VARCHAR2(4000);
1949 l_relation_cond VARCHAR2(4000) := '';
1950 -- added rosharma 19-jun-2003 bug # 3004453
1951 l_date DATE := TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, 1),'DD-MM-YYYY'), 'DD-MM-YYYY'), 'MONTH');
1952 -- end add rosharma 19-jun-2003 bug # 3004453
1953
1954 l_is_b2b BOOLEAN;
1955 l_model_id NUMBER;
1956
1957 l_is_org_prod_affn BOOLEAN;
1958 BEGIN
1959 -- Initialize API return status to SUCCESS
1960 x_return_status := FND_API.G_RET_STS_SUCCESS;
1961
1962 l_model_id := p_model_id;
1963
1964 IF (AMS_DEBUG_HIGH_ON) THEN
1965 AMS_Utility_PVT.debug_message (L_API_NAME || ': L_DATE = ' || l_date);
1966 AMS_Utility_PVT.debug_message ('MODEL ID: ' || p_model_id);
1967 END IF;
1968
1969 OPEN c_model_details (p_model_id);
1970 FETCH c_model_details INTO l_model_rec;
1971 CLOSE c_model_details;
1972
1973 IF p_seeded_data_source THEN
1974 IF l_model_rec.model_type = 'LOYALTY' THEN
1975
1976 --Check for model type
1977 is_b2b_data_source(
1978 p_model_id => l_model_id,
1979 x_is_b2b => l_is_b2b
1980 );
1981 -- choang - 04-jul-2002 - change loyalty logic
1982 -- use days since last ordered as loyalty indicator
1983 -- business reasoning: if customer has not ordered in the
1984 -- last n days, then he may have gone to do business with
1985 -- a competitor.
1986
1987 IF l_is_b2b THEN
1988 --UPDATE ams_dm_target_stg t
1989 UPDATE ams_dm_target_stg_gt t
1990 SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
1991 FROM dual
1992 -- choang - 21-nov-2003 - bug 3275817
1993 -- changed to not exists and having clause
1994 WHERE NOT EXISTS (SELECT 1
1995 -- changed rosharma 19-jun-2003 bug # 3004453
1996 --FROM ams_dm_party_details_time p
1997 --WHERE p.party_id = t.party_id
1998 --AND p.tot_num_order_3_months < 1
1999 FROM bic_party_summ p , hz_relationships hpr
2000 WHERE hpr.party_id = t.party_id
2001 AND hpr.status = 'A'
2002 AND hpr.subject_table_name = 'HZ_PARTIES'
2003 AND hpr.object_table_name = 'HZ_PARTIES'
2004 AND hpr.directional_flag = 'F'
2005 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
2006 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
2007 AND p.party_id = hpr.object_id --the org's party id
2008 HAVING SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, p.period_start_date)) - 3),1,0,p.order_num)) >= 1
2009 -- end change rosharma 19-jun-2003 bug # 3004453
2010 )
2011 )
2012 WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
2013 AND object_used_by_id = p_model_id;
2014 ELSE
2015 -- UPDATE ams_dm_target_stg t
2016 UPDATE ams_dm_target_stg_gt t
2017 SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
2018 FROM dual
2019 -- choang - 21-nov-2003 - bug 3275817
2020 -- changed to not exists and having clause
2021 WHERE NOT EXISTS (SELECT 1
2022 -- changed rosharma 19-jun-2003 bug # 3004453
2023 --FROM ams_dm_party_details_time p
2024 --WHERE p.party_id = t.party_id
2025 --AND p.tot_num_order_3_months < 1
2026 FROM bic_party_summ p
2027 WHERE p.party_id = t.party_id
2028 HAVING SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, p.period_start_date)) - 3),1,0,p.order_num)) >= 1
2029 -- end change rosharma 19-jun-2003 bug # 3004453
2030 )
2031 )
2032 WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
2033 AND object_used_by_id = p_model_id;
2034 END IF;
2035 ELSIF l_model_rec.model_type IN ('EMAIL', 'DIRECTMAIL', 'TELEMARKETING') THEN
2036 -- UPDATE ams_dm_target_stg t
2037 UPDATE ams_dm_target_stg_gt t
2038 SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
2039 FROM dual
2040 WHERE EXISTS (SELECT 1
2041 FROM ams_campaign_schedules_b c, jtf_ih_interactions i,
2042 jtf_ih_results_b r, ams_list_select_actions l
2043 WHERE c.schedule_id = l.incl_object_id
2044 AND l.arc_action_used_by = t.arc_object_used_by
2045 AND l.action_used_by_id = t.object_used_by_id
2046 AND i.party_id = t.party_id
2047 AND i.source_code = c.source_code
2048 AND r.result_id = i.result_id
2049 AND r.positive_response_flag = 'Y'
2050 ))
2051 WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
2052 AND object_used_by_id = p_model_id;
2053 ELSIF l_model_rec.model_type = 'CUSTOMER_PROFITABILITY' THEN
2054 OPEN c_target (l_model_rec.target_id);
2055 FETCH c_target
2056 INTO l_target_field, l_target_source_object, l_pk_source_object, l_pk_field, l_target_source_object_full, l_pk_source_object_full, l_data_source_id, l_target_source_id, l_target_enabled;
2057 CLOSE c_target;
2058
2059 IF l_target_enabled <> 'Y' THEN
2060 IF (AMS_DEBUG_HIGH_ON) THEN
2061 AMS_Utility_PVT.debug_message ('Target Field has been disabled. Raising Error.');
2062 END IF;
2063 AMS_Utility_PVT.error_message ('AMS_DM_TARGET_FIELD_DISABLED');
2064 RAISE FND_API.G_EXC_ERROR;
2065 END IF;
2066
2067 get_target_positive_values ( p_target_id => l_model_rec.target_id,
2068 p_target_field => l_target_source_object || '.' || l_target_field,
2069 x_sql_stmt => l_positive_values_sql);
2070
2071 IF l_positive_values_sql IS NULL THEN
2072 l_positive_values_sql := ' ' || l_target_source_object || '.' || l_target_field || ' = tv.target_value';
2073 END IF;
2074
2075 -- l_sql := 'UPDATE ams_dm_target_stg t';
2076 l_sql := 'UPDATE ams_dm_target_stg_gt t';
2077 l_sql := l_sql || ' SET t.target_flag = (SELECT ''' || L_POSITIVE_TARGET_VALUE || '''';
2078 l_sql := l_sql || ' FROM dual WHERE EXISTS (SELECT 1';
2079
2080 IF l_data_source_id = l_target_source_id THEN
2081 l_sql := l_sql || ' FROM ams_dm_target_values_b tv, ' || l_target_source_object_full;
2082 ELSE
2083 l_sql := l_sql || ' FROM ams_dm_target_values_b tv, ' || l_target_source_object_full || ', ' || l_pk_source_object_full;
2084 END IF;
2085
2086 l_sql := l_sql || ' WHERE tv.target_id = :target_id';
2087
2088 is_b2b_data_source(
2089 p_model_id => l_model_id,
2090 x_is_b2b => l_is_b2b
2091 );
2092
2093 IF l_is_b2b THEN
2094 l_sql := l_sql || ' AND ' || l_pk_source_object || '.organization_id = t.party_id';
2095 ELSE
2096 l_sql := l_sql || ' AND ' || l_pk_source_object || '.party_id = t.party_id';
2097 END IF;
2098
2099 l_sql := l_sql || ' AND (' || l_positive_values_sql || ')';
2100 IF l_data_source_id <> l_target_source_id THEN
2101 get_related_ds_condition ( p_master_ds_id => l_data_source_id,
2102 p_child_ds_id => l_target_source_id,
2103 x_sql_stmt => l_relation_cond);
2104 IF LENGTH(l_relation_cond) > 0 THEN
2105 l_sql := l_sql || ' AND (' || l_relation_cond || ')';
2106 END IF;
2107 END IF;
2108 l_sql := l_sql || '))';
2109 l_sql := l_sql || ' WHERE t.arc_object_used_by = :object_type';
2110 l_sql := l_sql || ' AND t.object_used_by_id = :model_id';
2111
2112 IF (AMS_DEBUG_HIGH_ON) THEN
2113 AMS_Utility_PVT.debug_message ('Customer Profitability-target sql: ' || l_sql);
2114 END IF;
2115
2116 EXECUTE IMMEDIATE l_sql
2117 USING l_model_rec.target_id, G_OBJECT_TYPE_MODEL, p_model_id;
2118
2119 ELSIF l_model_rec.model_type ='PRODUCT_AFFINITY' THEN
2120
2121 OPEN c_target (l_model_rec.target_id);
2122 FETCH c_target
2123 INTO l_target_field, l_target_source_object, l_pk_source_object, l_pk_field, l_target_source_object_full, l_pk_source_object_full, l_data_source_id, l_target_source_id, l_target_enabled;
2124 CLOSE c_target;
2125
2126 IF l_target_enabled <> 'Y' THEN
2127 IF (AMS_DEBUG_HIGH_ON) THEN
2128 AMS_Utility_PVT.debug_message ('Target Field has been disabled. Raising Error.');
2129 END IF;
2130 AMS_Utility_PVT.error_message ('AMS_DM_TARGET_FIELD_DISABLED');
2131 RAISE FND_API.G_EXC_ERROR;
2132 END IF;
2133
2134 is_b2b_data_source(
2135 p_model_id => l_model_id,
2136 x_is_b2b => l_is_b2b
2137 );
2138
2139 is_org_prod_affn (
2140 p_model_id => l_model_id,
2141 x_is_org_prod => l_is_org_prod_affn
2142 );
2143
2144
2145 IF l_is_b2b AND l_is_org_prod_affn = false THEN
2146 -- UPDATE ams_dm_target_stg t
2147 UPDATE ams_dm_target_stg_gt t
2148 SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
2149 FROM dual
2150 WHERE EXISTS (SELECT 1
2151 FROM AMS_ACT_PRODUCTS aa, OE_ORDER_HEADERS_ALL oh, OE_ORDER_LINES_ALL ol,
2152 hz_cust_account_roles hcr, hz_relationships hpr
2153 WHERE aa.ARC_ACT_PRODUCT_USED_BY = 'MODL'
2154 and aa.ACT_PRODUCT_USED_BY_ID = p_model_id
2155 and ol.INVENTORY_ITEM_ID IN
2156 (SELECT DISTINCT ic.INVENTORY_ITEM_ID
2157 FROM MTL_ITEM_CATEGORIES ic
2158 WHERE ic.CATEGORY_ID = aa.CATEGORY_ID
2159 AND (ic.INVENTORY_ITEM_ID = aa.INVENTORY_ITEM_ID OR aa.INVENTORY_ITEM_ID IS NULL))
2160 --and (oh.ORG_ID = aa.ORGANIZATION_ID OR aa.ORGANIZATION_ID IS NULL)
2161 --and ol.ORG_ID = nvl(aa.ORGANIZATION_ID,ol.ORG_ID)
2162 and oh.SHIP_TO_CONTACT_ID = hcr.CUST_ACCOUNT_ROLE_ID
2163 and hcr.PARTY_ID=hpr.PARTY_ID
2164 and hpr.party_id = t.party_id
2165 and hpr.status = 'A'
2166 and hpr.subject_table_name = 'HZ_PARTIES'
2167 and hpr.object_table_name = 'HZ_PARTIES'
2168 and hpr.directional_flag = 'F'
2169 and hpr.relationship_code IN ('CONTACT_OF','EMPLOYEE_OF')
2170 and (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
2171 and oh.header_id = ol.header_id
2172 and nvl(oh.cancelled_flag,'N') <> 'Y'
2173 and nvl(oh.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
2174 and nvl(ol.cancelled_flag,'N') <> 'Y'
2175 and nvl(ol.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
2176 and oh.order_category_code <> 'RETURN'
2177 and ol.line_category_code <> 'RETURN'
2178 )
2179 )
2180 WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
2181 AND object_used_by_id = p_model_id;
2182 ELSE
2183 -- UPDATE ams_dm_target_stg t
2184 UPDATE ams_dm_target_stg_gt t
2185 SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
2186 FROM dual
2187 WHERE EXISTS (SELECT 1
2188 FROM AMS_ACT_PRODUCTS aa,
2189 OE_ORDER_HEADERS_ALL oh,
2190 OE_ORDER_LINES_ALL ol,
2191 --hz_cust_site_uses_all hcsu,
2192 --hz_cust_acct_sites_all hcs,
2193 HZ_CUST_ACCOUNTS hc
2194 WHERE aa.ARC_ACT_PRODUCT_USED_BY = 'MODL'
2195 and aa.ACT_PRODUCT_USED_BY_ID = p_model_id
2196 and ol.INVENTORY_ITEM_ID IN
2197 (SELECT DISTINCT ic.INVENTORY_ITEM_ID
2198 FROM MTL_ITEM_CATEGORIES ic
2199 WHERE ic.CATEGORY_ID = aa.CATEGORY_ID
2200 AND (ic.INVENTORY_ITEM_ID = aa.INVENTORY_ITEM_ID OR aa.INVENTORY_ITEM_ID IS NULL))
2201 --and (oh.ORG_ID = aa.ORGANIZATION_ID OR aa.ORGANIZATION_ID IS NULL)
2202 --and ol.ORG_ID = nvl(aa.ORGANIZATION_ID,ol.ORG_ID)
2203 --and oh.SHIP_TO_ORG_ID = hcsu.site_use_id
2204 --and hcsu.cust_acct_site_id = hcs.cust_acct_site_id
2205 --and hcs.cust_account_id = hc.cust_account_id
2206 and oh.SOLD_TO_ORG_ID = hc.cust_account_id
2207 and hc.PARTY_ID = t.party_id
2208 and oh.header_id = ol.header_id
2209 and nvl(oh.cancelled_flag,'N') <> 'Y'
2210 and nvl(oh.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
2211 and nvl(ol.cancelled_flag,'N') <> 'Y'
2212 and nvl(ol.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
2213 and oh.order_category_code <> 'RETURN'
2214 and ol.line_category_code <> 'RETURN'
2215 )
2216 )
2217 WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
2218 AND object_used_by_id = p_model_id;
2219 END IF;
2220
2221 ELSE
2222 AMS_Utility_PVT.error_message ('AMS_DM_UNSUPPORTED_MODEL', 'MODEL_TYPE', l_model_rec.model_type);
2223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2224 END IF;
2225
2226 ELSE
2227 OPEN c_target (l_model_rec.target_id);
2228 FETCH c_target INTO l_target_field, l_target_source_object, l_pk_source_object, l_pk_field, l_target_source_object_full, l_pk_source_object_full, l_data_source_id, l_target_source_id, l_target_enabled;
2229 CLOSE c_target;
2230
2231 IF l_target_enabled <> 'Y' THEN
2232 IF (AMS_DEBUG_HIGH_ON) THEN
2233 AMS_Utility_PVT.debug_message ('Target Field has been disabled. Raising Error.');
2234 END IF;
2235 AMS_Utility_PVT.error_message ('AMS_DM_TARGET_FIELD_DISABLED');
2236 RAISE FND_API.G_EXC_ERROR;
2237 END IF;
2238
2239 -- get the sql statement that ANDs all the positive values defined for the target
2240 -- and the comparison operators.
2241 get_target_positive_values ( p_target_id => l_model_rec.target_id,
2242 p_target_field => l_target_source_object || '.' || l_target_field,
2243 x_sql_stmt => l_positive_values_sql);
2244
2245 IF l_positive_values_sql IS NULL THEN
2246 l_positive_values_sql := ' ' || l_target_source_object || '.' || l_target_field || ' = tv.target_value';
2247 END IF;
2248
2249 -- if target field is defined for numeric data
2250 -- but the data contains alphanumeric data, a
2251 -- database error could be raised
2252 BEGIN
2253 -- l_sql := 'UPDATE ams_dm_target_stg t';
2254 l_sql := 'UPDATE ams_dm_target_stg_gt t';
2255 l_sql := l_sql || ' SET t.target_flag = (SELECT ''' || L_POSITIVE_TARGET_VALUE || '''';
2256 IF l_data_source_id = l_target_source_id THEN
2257 l_sql := l_sql || ' FROM ' || l_target_source_object_full;
2258 ELSE
2259 l_sql := l_sql || ' FROM ' || l_target_source_object_full || ', ' || l_pk_source_object_full;
2260 END IF;
2261 l_sql := l_sql || ' WHERE ' || l_pk_source_object || '.' || l_pk_field || ' = t.party_id';
2262 l_sql := l_sql || ' AND (' || l_positive_values_sql || ')';
2263 l_sql := l_sql || ' AND ROWNUM = 1 ';
2264 IF l_data_source_id <> l_target_source_id THEN
2265 get_related_ds_condition ( p_master_ds_id => l_data_source_id,
2266 p_child_ds_id => l_target_source_id,
2267 x_sql_stmt => l_relation_cond);
2268 IF LENGTH(l_relation_cond) > 0 THEN
2269 l_sql := l_sql || ' AND (' || l_relation_cond || ')';
2270 END IF;
2271 END IF;
2272 l_sql := l_sql || ')';
2273 l_sql := l_sql || ' WHERE t.arc_object_used_by = :object_type';
2274 l_sql := l_sql || ' AND t.object_used_by_id = :model_id';
2275
2276 EXCEPTION
2277 WHEN VALUE_ERROR THEN
2278 AMS_Utility_PVT.error_message ('AMS_DM_INVALID_DATA_CONVERT');
2279 RAISE FND_API.G_EXC_ERROR;
2280 END;
2281
2282 IF (AMS_DEBUG_HIGH_ON) THEN
2283 AMS_Utility_PVT.debug_message ('target sql: ' || l_sql);
2284 END IF;
2285
2286 EXECUTE IMMEDIATE l_sql
2287 USING G_OBJECT_TYPE_MODEL, p_model_id;
2288 END IF;
2289
2290 -- UPDATE ams_dm_target_stg
2291 UPDATE ams_dm_target_stg_gt
2292 SET target_flag = L_NEGATIVE_TARGET_VALUE
2293 WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
2294 AND object_used_by_id = p_model_id
2295 AND target_flag IS NULL;
2296 EXCEPTION
2297 WHEN FND_API.G_EXC_ERROR THEN
2298 x_return_status := FND_API.G_RET_STS_ERROR;
2299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2301 WHEN OTHERS THEN
2302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2303 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2304 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2305 END IF;
2306 END populate_targets;
2307
2308
2309
2310 --
2311 -- History
2312 -- 16-Feb-2001 choang Created.
2313 PROCEDURE update_action_summary (
2314 p_arc_object_for IN VARCHAR2,
2315 p_object_for_id IN NUMBER,
2316 p_seeded_data_source IN BOOLEAN,
2317 x_return_status OUT NOCOPY VARCHAR2
2318 )
2319 IS
2320 L_API_NAME CONSTANT VARCHAR2(30) := 'Update Select Actions Summary';
2321
2322 l_action_rec AMS_ListAction_PVT.action_rec_type;
2323 l_action_id NUMBER;
2324 l_obj_version_num NUMBER;
2325
2326 l_msg_data VARCHAR2(32767);
2327 l_msg_count NUMBER;
2328 l_return_status VARCHAR2(1);
2329
2330 CURSOR c_action_version (p_arc_incl_object IN VARCHAR2, p_incl_object_id IN NUMBER) IS
2331 SELECT list_select_action_id, object_version_number
2332 FROM ams_list_select_actions
2333 WHERE arc_action_used_by = p_arc_object_for
2334 AND action_used_by_id = p_object_for_id
2335 AND arc_incl_object_from = p_arc_incl_object
2336 AND incl_object_id = p_incl_object_id
2337 ;
2338
2339 CURSOR c_summary IS
2340 SELECT arc_object,
2341 object_id,
2342 COUNT(*) total_selected, -- total selected
2343 SUM (DECODE (target_flag, 'Y', 1, 0)) total_targeted
2344 -- FROM ams_dm_target_stg
2345 FROM ams_dm_target_stg_gt
2346 WHERE arc_object_used_by = p_arc_object_for
2347 AND object_used_by_id = p_object_for_id
2348 GROUP BY arc_object, object_id
2349 ;
2350 BEGIN
2351 -- Initialize API return status to SUCCESS
2352 x_return_status := FND_API.G_RET_STS_SUCCESS;
2353
2354 -- only perform the summarization for seeded data sources
2355 IF NOT p_seeded_data_source THEN
2356 RETURN;
2357 END IF;
2358
2359 AMS_ListAction_PVT.init_action_rec (l_action_rec);
2360
2361 FOR l_summary_rec IN c_summary LOOP
2362 OPEN c_action_version (l_summary_rec.arc_object, l_summary_rec.object_id);
2363 FETCH c_action_version INTO l_action_rec.list_select_action_id, l_action_rec.object_version_number;
2364 CLOSE c_action_version;
2365
2366 l_action_rec.no_of_rows_used := l_summary_rec.total_selected;
2367 l_action_rec.no_of_rows_targeted := l_summary_rec.total_targeted;
2368
2369 AMS_ListAction_PVT.Update_ListAction (
2370 p_api_version => 1.0,
2371 p_init_msg_list => FND_API.G_FALSE,
2372 p_commit => FND_API.G_TRUE,
2373 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2374 x_return_status => l_return_status,
2375 x_msg_count => l_msg_count,
2376 x_msg_data => l_msg_data,
2377 p_action_rec => l_action_rec
2378 );
2379 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2380 x_return_status := l_return_status;
2381 END IF;
2382 END LOOP;
2383 EXCEPTION
2384 WHEN FND_API.G_EXC_ERROR THEN
2385 x_return_status := FND_API.G_RET_STS_ERROR;
2386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2388 WHEN OTHERS THEN
2389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2390 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2391 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2392 END IF;
2393 END update_action_summary;
2394
2395 -- Note
2396 -- Different between this procedure and populate_target
2397 -- is that this procedure updates amd_dm_source versus
2398 -- ams_dm_target_stg_gt. This procedure also populates
2399 -- 0 or 1 for the target value due to ODM limitations
2400 -- in creating a balanced data set -- they can't use
2401 -- Y or N in the calculation of the balanced data set.
2402 --
2403 -- ***** OBSELETED ********
2404 --
2405 -- History
2406 -- 19-Feb-2001 choang Created.
2407 -- 05-Jun-2002 choang Obseleted. Logic was incorporated into populate_source
2408 -- and populate_targets.
2409 --
2410 PROCEDURE update_source_target (
2411 p_object_type IN VARCHAR2,
2412 p_object_id IN NUMBER,
2413 x_return_status OUT NOCOPY VARCHAR2
2414 )
2415 IS
2416 L_API_NAME CONSTANT VARCHAR2(30) := 'Update Source Target';
2417
2418 L_NON_LOYAL_CODE CONSTANT VARCHAR2(30) := 'LEFT_FOR_COMPETITOR'; -- replace this with the correct; follow bug 1657447
2419 L_POSITIVE_TARGET_VALUE CONSTANT VARCHAR2(30) := '1';
2420 L_NEGATIVE_TARGET_VALUE CONSTANT VARCHAR2(30) := '0';
2421 L_SEEDED_ID_THRESHOLD CONSTANT NUMBER := 10000;
2422
2423 CURSOR c_model_details (p_model_id IN NUMBER) IS
2424 SELECT m.model_id
2425 , m.model_type
2426 , m.target_positive_value
2427 , t.target_id
2428 , t.data_source_id
2429 FROM ams_dm_models_all_b m, ams_dm_targets_b t
2430 WHERE m.model_id = p_model_id
2431 AND t.target_id = m.target_id
2432 ;
2433 l_object_rec c_model_details%ROWTYPE;
2434
2435 CURSOR c_score_details (p_score_id IN NUMBER) IS
2436 SELECT m.model_id
2437 , m.model_type
2438 , m.target_positive_value
2439 , t.target_id
2440 , t.data_source_id
2441 FROM ams_dm_models_all_b m, ams_dm_scores_all_b s, ams_dm_targets_b t
2442 WHERE m.model_id = s.model_id
2443 AND s.score_id = p_score_id
2444 AND t.target_id = m.target_id
2445 ;
2446
2447 l_seeded_flag BOOLEAN;
2448 BEGIN
2449 -- Initialize API return status to SUCCESS
2450 x_return_status := FND_API.G_RET_STS_SUCCESS;
2451
2452 IF p_object_type = G_OBJECT_TYPE_MODEL THEN
2453 OPEN c_model_details (p_object_id);
2454 FETCH c_model_details INTO l_object_rec;
2455 CLOSE c_model_details;
2456 ELSE
2457 OPEN c_score_details (p_object_id);
2458 FETCH c_score_details INTO l_object_rec;
2459 CLOSE c_score_details;
2460 END IF;
2461
2462 IF l_object_rec.data_source_id < L_SEEDED_ID_THRESHOLD THEN
2463 l_seeded_flag := TRUE;
2464 END IF;
2465
2466 IF l_object_rec.model_type = 'LOYALTY' THEN
2467 UPDATE /*+ index(t AMS_DM_SOURCE_U2) */ ams_dm_source t
2468 SET target_value = (SELECT L_POSITIVE_TARGET_VALUE
2469 FROM dual
2470 WHERE EXISTS (SELECT 1
2471 FROM hz_cust_accounts c, hz_suspension_activity s
2472 WHERE c.party_id = t.party_id
2473 AND s.cust_account_id = c.cust_account_id
2474 AND s.action_type = L_NON_LOYAL_CODE
2475 ))
2476 WHERE arc_used_for_object = p_object_type
2477 AND used_for_object_id = p_object_id;
2478 ELSIF l_object_rec.model_type IN ('EMAIL', 'DIRECTMAIL', 'TELEMARKETING') THEN
2479 UPDATE /*+ index(t AMS_DM_SOURCE_U2) */ ams_dm_source t
2480 SET target_value = (SELECT L_POSITIVE_TARGET_VALUE
2481 FROM dual
2482 WHERE EXISTS (SELECT 1
2483 FROM ams_campaign_schedules_b c,
2484 jtf_ih_interactions i,
2485 ams_list_select_actions l
2486 WHERE c.schedule_id = l.incl_object_id
2487 AND l.arc_action_used_by = t.arc_used_for_object
2488 AND l.action_used_by_id = t.used_for_object_id
2489 AND i.party_id = t.party_id
2490 AND i.source_code = c.source_code
2491 /*** enable this code when positive responses are captured
2492 WHERE EXISTS (SELECT 1
2493 FROM ams_campaign_schedules_b c, jtf_ih_interactions i,
2494 jtf_ih_results_b r, ams_list_select_actions l
2495 WHERE c.schedule_id = l.incl_object_id
2496 AND l.arc_action_used_by = t.arc_used_for_object
2497 AND l.action_used_by_id = t.used_for_object_id
2498 AND i.party_id = t.party_id
2499 AND i.source_code = c.source_code
2500 AND r.result_id = i.result_id
2501 AND r.positive_response_flag = 'Y'
2502 ***/
2503 ))
2504 WHERE arc_used_for_object = p_object_type
2505 AND used_for_object_id = p_object_id;
2506 ELSE
2507 AMS_Utility_PVT.error_message ('AMS_DM_UNSUPPORTED_MODEL', 'MODEL_TYPE', l_object_rec.model_type);
2508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2509 END IF;
2510
2511 UPDATE /*+ index(AMS_DM_SOURCE AMS_DM_SOURCE_U2) */ ams_dm_source
2512 SET target_value = L_NEGATIVE_TARGET_VALUE
2513 WHERE arc_used_for_object = p_object_type
2514 AND used_for_object_id = p_object_id
2515 AND target_value IS NULL;
2516 EXCEPTION
2517 WHEN FND_API.G_EXC_ERROR THEN
2518 x_return_status := FND_API.G_RET_STS_ERROR;
2519 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521 WHEN OTHERS THEN
2522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2523 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2524 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2525 END IF;
2526 END update_source_target;
2527
2528
2529 ---------------------------------------------------------------
2530 PROCEDURE schedule_preview (
2531 errbuf OUT NOCOPY VARCHAR2,
2532 retcode OUT NOCOPY VARCHAR2,
2533 p_arc_object IN VARCHAR2,
2534 p_object_id IN NUMBER
2535 )
2536 IS
2537 l_return_status VARCHAR2(1);
2538 l_msg_count NUMBER;
2539 BEGIN
2540 retcode := 0;
2541
2542 Preview_Selections (
2543 p_arc_object => p_arc_object,
2544 p_object_id => p_object_id,
2545 x_return_status => l_return_status
2546 );
2547 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2548 l_msg_count := FND_MSG_PUB.count_msg;
2549 FOR i IN 1 .. l_msg_count LOOP
2550 AMS_Utility_PVT.create_log (
2551 x_return_status => l_return_status,
2552 p_arc_log_used_by => p_arc_object,
2553 p_log_used_by_id => p_object_id,
2554 p_msg_data => FND_MSG_PUB.get(i, FND_API.g_false),
2555 p_msg_type => 'ERROR'
2556 );
2557 END LOOP;
2558 retcode := 2;
2559 END IF;
2560
2561 -- write a complete message to log
2562 AMS_Utility_PVT.create_log (
2563 x_return_status => l_return_status,
2564 p_arc_log_used_by => p_arc_object,
2565 p_log_used_by_id => p_object_id,
2566 p_msg_data => 'Schedule Preview: COMPLETE',
2567 p_msg_type => 'INFO'
2568 );
2569 END schedule_preview;
2570
2571
2572 ---------------------------------------------------------------
2573 PROCEDURE schedule_aggregation (
2574 errbuf OUT NOCOPY VARCHAR2,
2575 retcode OUT NOCOPY VARCHAR2,
2576 p_arc_object IN VARCHAR2,
2577 p_object_id IN NUMBER
2578 )
2579 IS
2580 l_return_status VARCHAR2(1);
2581 l_msg_count NUMBER;
2582 BEGIN
2583 retcode := 0;
2584
2585 Aggregate_Selections (
2586 p_arc_object => p_arc_object,
2587 p_object_id => p_object_id,
2588 x_return_status => l_return_status
2589 );
2590 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2591 l_msg_count := FND_MSG_PUB.count_msg;
2592 FOR i IN 1 .. l_msg_count LOOP
2593 AMS_Utility_PVT.create_log (
2594 x_return_status => l_return_status,
2595 p_arc_log_used_by => p_arc_object,
2596 p_log_used_by_id => p_object_id,
2597 p_msg_data => FND_MSG_PUB.get(i, FND_API.g_false)
2598 );
2599 END LOOP;
2600 retcode := 2;
2601 END IF;
2602 END schedule_aggregation;
2603
2604
2605 ---------------------------------------------------------------
2606 -- History
2607 -- 03-Mar-2001 choang Created.
2608 ---------------------------------------------------------------
2609 PROCEDURE apply_sizing_options (
2610 p_arc_object_for IN VARCHAR2,
2611 p_object_for_id IN NUMBER,
2612 x_return_status OUT NOCOPY VARCHAR2
2613 )
2614 IS
2615 L_SELECTION_TYPE_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
2616 L_SELECTION_TYPE_NTH_ROW CONSTANT VARCHAR2(30) := 'NTH_RECORD';
2617 L_SELECTION_TYPE_PCT CONSTANT VARCHAR2(30) := 'RANDOM';
2618
2619 l_total_records NUMBER;
2620
2621 l_min_records NUMBER;
2622 l_max_records NUMBER;
2623 l_row_selection_type VARCHAR2(30);
2624 l_every_nth_row NUMBER;
2625 l_pct_random NUMBER;
2626
2627 CURSOR c_model_details (p_model_id IN NUMBER) IS
2628 SELECT min_records,
2629 max_records,
2630 row_selection_type,
2631 every_nth_row,
2632 pct_random
2633 FROM ams_dm_models_all_b
2634 WHERE model_id = p_model_id
2635 ;
2636 CURSOR c_score_details (p_score_id IN NUMBER) IS
2637 SELECT min_records,
2638 max_records,
2639 row_selection_type,
2640 every_nth_row,
2641 pct_random
2642 FROM ams_dm_scores_all_b
2643 WHERE score_id = p_score_id
2644 ;
2645
2646 CURSOR c_total_records (p_arc_object IN VARCHAR2, p_object_id IN NUMBER) IS
2647 SELECT COUNT(*)
2648 -- FROM ams_dm_inter_source_stg
2649 FROM ams_dm_int_src_stg_gt
2650 WHERE arc_object_used_by = p_arc_object
2651 AND object_used_by_id = p_object_id
2652 ;
2653 BEGIN
2654 -- Initialize API return status to SUCCESS
2655 x_return_status := FND_API.G_RET_STS_SUCCESS;
2656
2657 OPEN c_total_records (p_arc_object_for, p_object_for_id);
2658 FETCH c_total_records INTO l_total_records;
2659 CLOSE c_total_records;
2660
2661 -- get score or model details to be used
2662 -- in the subsequent processing.
2663 IF p_arc_object_for = G_OBJECT_TYPE_MODEL THEN
2664 OPEN c_model_details (p_object_for_id);
2665 FETCH c_model_details INTO l_min_records, l_max_records, l_row_selection_type, l_every_nth_row, l_pct_random;
2666 CLOSE c_model_details;
2667 ELSE
2668 OPEN c_score_details (p_object_for_id);
2669 FETCH c_score_details INTO l_min_records, l_max_records, l_row_selection_type, l_every_nth_row, l_pct_random;
2670 CLOSE c_score_details;
2671 END IF;
2672
2673 IF l_total_records < NVL (l_min_records, 0) THEN
2674 AMS_Utility_PVT.error_message ('AMS_DM_NOT_ENOUGH_RECORDS', 'NUM_RECORDS', l_total_records);
2675 RAISE FND_API.G_EXC_ERROR;
2676 END IF;
2677
2678 IF l_row_selection_type = L_SELECTION_TYPE_STANDARD THEN
2679 -- UPDATE ams_dm_inter_source_stg
2680 UPDATE ams_dm_int_src_stg_gt
2681 SET enabled_flag = 'Y'
2682 WHERE arc_object_used_by = p_arc_object_for
2683 AND object_used_by_id = p_object_for_id
2684 AND rownum <= NVL (l_max_records, l_total_records)
2685 ;
2686 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message (l_row_selection_type || ': ' || SQL%ROWCOUNT); END IF;
2687 ELSIF l_row_selection_type = L_SELECTION_TYPE_NTH_ROW THEN
2688 randomize_nth_rows (
2689 p_arc_object_for => p_arc_object_for,
2690 p_object_for_id => p_object_for_id,
2691 p_min_rows => l_min_records,
2692 p_max_rows => l_max_records,
2693 p_total_rows => l_total_records,
2694 p_every_nth_row => l_every_nth_row,
2695 x_return_status => x_return_status
2696 );
2697 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2699 END IF;
2700 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message (l_row_selection_type || ': ' || SQL%ROWCOUNT); END IF;
2701 ELSIF l_row_selection_type = L_SELECTION_TYPE_PCT THEN
2702 randomize_by_pct (
2703 p_arc_object_for => p_arc_object_for,
2704 p_object_for_id => p_object_for_id,
2705 p_min_rows => l_min_records,
2706 p_max_rows => l_max_records,
2707 p_total_rows => l_total_records,
2708 p_pct_random => l_pct_random,
2709 x_return_status => x_return_status
2710 );
2711 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2713 END IF;
2714 ELSE
2715 AMS_Utility_PVT.error_message ('AMS_DM_BAD_SELECTION_TYPE', 'SELECTION_TYPE', l_row_selection_type);
2716 x_return_status := FND_API.G_RET_STS_ERROR;
2717 END IF;
2718 END apply_sizing_options;
2719
2720
2721 ---------------------------------------------------------------
2722 -- History
2723 -- 03-Mar-2001 choang Created.
2724 ---------------------------------------------------------------
2725 PROCEDURE randomize_by_pct (
2726 p_arc_object_for IN VARCHAR2,
2727 p_object_for_id IN NUMBER,
2728 p_min_rows IN NUMBER,
2729 p_max_rows IN NUMBER,
2730 p_total_rows IN NUMBER,
2731 p_pct_random IN NUMBER,
2732 x_return_status OUT NOCOPY VARCHAR2
2733 )
2734 IS
2735 TYPE id_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2736
2737 l_object_ids id_table_type;
2738 l_total_random_rows NUMBER := FLOOR ((p_total_rows * p_pct_random) / 100);
2739
2740 CURSOR c_randomized_sources (p_arc_object IN VARCHAR2, p_object_id IN NUMBER) IS
2741 SELECT party_id
2742 -- FROM ams_dm_inter_source_stg
2743 FROM ams_dm_int_src_stg_gt
2744 WHERE arc_object_used_by = p_arc_object
2745 AND object_used_by_id = p_object_id
2746 ORDER BY random_generated_num
2747 ;
2748 BEGIN
2749 -- Initialize API return status to SUCCESS
2750 x_return_status := FND_API.G_RET_STS_SUCCESS;
2751
2752 IF l_total_random_rows < NVL (p_min_rows, 0) THEN
2753 AMS_Utility_PVT.error_message ('AMS_DM_NOT_ENOUGH_RECORDS', 'NUM_RECORDS', l_total_random_rows);
2754 RAISE FND_API.G_EXC_ERROR;
2755 END IF;
2756
2757 -- Randomize the records
2758 -- Seed the random number generator with the Julian
2759 -- days and seconds representation of sysdate.
2760 DBMS_RANDOM.initialize (TO_NUMBER (TO_CHAR (SYSDATE, 'DDSSSS')));
2761
2762 -- UPDATE ams_dm_inter_source_stg
2763 UPDATE ams_dm_int_src_stg_gt
2764 SET random_generated_num = DBMS_RANDOM.random
2765 WHERE arc_object_used_by = p_arc_object_for
2766 AND object_used_by_id = p_object_for_id
2767 ;
2768
2769 DBMS_RANDOM.terminate;
2770
2771 -- Bulk fetch the first l_total_random_rows up to the l_max_rows
2772 OPEN c_randomized_sources (p_arc_object_for, p_object_for_id);
2773 FETCH c_randomized_sources BULK COLLECT INTO l_object_ids LIMIT NVL (p_max_rows, l_total_random_rows);
2774 CLOSE c_randomized_sources;
2775
2776 -- Bulk update the records ordered by the randomly generated number.
2777 -- This simulates a random order of the records.
2778 FORALL i IN l_object_ids.FIRST..l_object_ids.LAST
2779 -- UPDATE ams_dm_inter_source_stg
2780 UPDATE ams_dm_int_src_stg_gt
2781 SET enabled_flag = 'Y'
2782 WHERE arc_object_used_by = p_arc_object_for
2783 AND object_used_by_id = p_object_for_id
2784 AND party_id = l_object_ids(i);
2785 END randomize_by_pct;
2786
2787
2788 ---------------------------------------------------------------
2789 -- History
2790 -- 03-Mar-2001 choang Created.
2791 ---------------------------------------------------------------
2792 PROCEDURE randomize_nth_rows (
2793 p_arc_object_for IN VARCHAR2,
2794 p_object_for_id IN NUMBER,
2795 p_min_rows IN NUMBER,
2796 p_max_rows IN NUMBER,
2797 p_total_rows IN NUMBER,
2798 p_every_nth_row IN NUMBER,
2799 x_return_status OUT NOCOPY VARCHAR2
2800 )
2801 IS
2802 l_total_random_rows NUMBER := FLOOR (p_total_rows / p_every_nth_row);
2803
2804 l_local_max_rows NUMBER := NVL (p_max_rows, p_total_rows);
2805 BEGIN
2806 -- Initialize API return status to SUCCESS
2807 x_return_status := FND_API.G_RET_STS_SUCCESS;
2808
2809 IF l_total_random_rows < NVL (p_min_rows, 0) THEN
2810 AMS_Utility_PVT.error_message ('AMS_DM_NOT_ENOUGH_RECORDS', 'NUM_RECORDS', l_total_random_rows);
2811 RAISE FND_API.G_EXC_ERROR;
2812 END IF;
2813
2814 -- two phase updates
2815 -- 1) update all records but only set enabled_flag = Y for the nth row
2816 -- 2) update all records with enabled_flag = Y, but set all records
2817 -- greater than max to N
2818 -- UPDATE ams_dm_inter_source_stg
2819 UPDATE ams_dm_int_src_stg_gt
2820 SET enabled_flag = DECODE (MOD (rownum, p_every_nth_row), 0, 'Y', 'N')
2821 WHERE arc_object_used_by = p_arc_object_for
2822 AND object_used_by_id = p_object_for_id
2823 ;
2824
2825 -- if total number of randomized rows is less than
2826 -- or equal to the max requested rows, then no records
2827 -- need to be updated with N
2828 IF l_total_random_rows > l_local_max_rows THEN
2829 -- UPDATE ams_dm_inter_source_stg
2830 UPDATE ams_dm_int_src_stg_gt
2831 SET enabled_flag = DECODE (SIGN (l_local_max_rows - rownum), -1, 'N', 'Y')
2832 WHERE arc_object_used_by = p_arc_object_for
2833 AND object_used_by_id = p_object_for_id
2834 AND enabled_flag = 'Y'
2835 ;
2836 END IF;
2837 END randomize_nth_rows;
2838
2839
2840 ---------------------------------------------------------------
2841 -- Purpose:
2842 -- Retrieve the selected fields for model building.
2843 --
2844 -- NOTE:
2845 -- Assume list src type is enabled.
2846 --
2847 -- When executing the dynamic SQL, the following fields
2848 -- must be bound:
2849 -- p_object_type
2850 -- p_object_id
2851 -- p_select_object_type
2852 -- p_select_object_id
2853 --
2854 -- Parameter:
2855 -- p_object_type
2856 -- p_object_id
2857 -- p_select_object_type - ADS is for alternative data source
2858 -- p_select_object_id - if ADS, then data source ID
2859 -- p_workbook_owner
2860 -- p_workbook_name
2861 -- p_worksheet_name
2862 -- x_insert_fields
2863 -- x_return_status
2864 ---------------------------------------------------------------
2865 PROCEDURE get_insert_fields (
2866 p_select_object_type IN VARCHAR2,
2867 p_select_object_id IN NUMBER,
2868 p_workbook_owner IN VARCHAR2,
2869 p_workbook_name IN VARCHAR2,
2870 p_worksheet_name IN VARCHAR2,
2871 x_insert_fields OUT NOCOPY VARCHAR2,
2872 x_return_status OUT NOCOPY VARCHAR2,
2873 x_pk_field OUT NOCOPY VARCHAR2
2874 )
2875 IS
2876 CURSOR c_pk_field (p_list_source_type_id IN NUMBER) IS
2877 SELECT source_object_name || '.' || source_object_pk_field
2878 FROM ams_list_src_types
2879 WHERE list_source_type_id = p_list_source_type_id
2880 ;
2881
2882 l_source_pk_field VARCHAR2(61);
2883 BEGIN
2884 x_return_status := FND_API.G_RET_STS_SUCCESS;
2885
2886 x_insert_fields := ':p_object_type, :p_object_id , :p_select_object_type, ';
2887 x_insert_fields := x_insert_fields || ':p_select_object_id , ';
2888
2889 IF p_select_object_type = G_ALTERNATIVE_DATA_SOURCE THEN
2890 OPEN c_pk_field (p_select_object_id);
2891 FETCH c_pk_field INTO l_source_pk_field;
2892 CLOSE c_pk_field;
2893
2894 x_insert_fields := x_insert_fields || l_source_pk_field;
2895 x_pk_field := l_source_pk_field;--kbasavar
2896 ELSIF p_select_object_type = 'LIST' THEN
2897 x_insert_fields := x_insert_fields || 'e.party_id';
2898 x_pk_field := 'e.party_id';--kbasavar
2899 ELSIF p_select_object_type = 'CSCH' THEN
2900 x_insert_fields := x_insert_fields || 'e.party_id';
2901 x_pk_field := 'e.party_id';--kbasavar
2902 ELSIF p_select_object_type = 'CELL' THEN
2903 x_insert_fields := x_insert_fields || 'aps.party_id';
2904 x_pk_field := 'aps.party_id';--kbasavar
2905 ELSIF p_select_object_type = 'DIWB' THEN
2906 get_customer_field (
2907 p_workbook_owner_name => p_workbook_owner,
2908 p_workbook_name => p_workbook_name,
2909 p_worksheet_name => p_worksheet_name,
2910 x_customer_field => l_source_pk_field,
2911 x_return_status => x_return_status
2912 );
2913 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2915 END IF;
2916
2917 x_insert_fields := x_insert_fields || l_source_pk_field;
2918 x_pk_field := l_source_pk_field;--kbasavar
2919 END IF;
2920 END get_insert_fields;
2921
2922
2923 ---------------------------------------------------------------
2924 -- Purpose:
2925 -- Get the listing of tables where the data selection
2926 -- is retrieved.
2927 --
2928 -- NOTE:
2929 -- Assume list src type is enabled.
2930 --
2931 -- Parameter:
2932 -- p_select_object_type - ADS is for alternative data source
2933 -- p_select_object_id - if ADS, then data source ID
2934 -- p_workbook_owner
2935 -- p_workbook_name
2936 -- p_worksheet_name
2937 -- x_from_clause
2938 -- x_return_status
2939 ---------------------------------------------------------------
2940 PROCEDURE get_from_clause (
2941 p_select_object_type IN VARCHAR2,
2942 p_select_object_id IN NUMBER,
2943 p_workbook_owner IN VARCHAR2,
2944 p_workbook_name IN VARCHAR2,
2945 p_worksheet_name IN VARCHAR2,
2946 p_is_b2b_custprof IN BOOLEAN,
2947 x_from_clause OUT NOCOPY VARCHAR2,
2948 x_return_status OUT NOCOPY VARCHAR2
2949 )
2950 IS
2951 L_API_NAME CONSTANT VARCHAR2(30) := 'get_from_clause';
2952 L_FROM_OFFSET CONSTANT NUMBER := 5;
2953
2954 CURSOR c_source_object (p_target_id IN NUMBER) IS
2955 SELECT a.source_object_name||decode(UPPER(a.remote_flag),'Y','@'||a.database_link,'')
2956 FROM ams_list_src_types a, ams_dm_targets_b b
2957 WHERE a.list_source_type_id = b.data_source_id
2958 AND b.target_id = p_target_id
2959 UNION
2960 SELECT a.source_object_name||decode(UPPER(a.remote_flag),'Y','@'||a.database_link,'')
2961 FROM ams_list_src_types a, ams_dm_target_sources b
2962 WHERE a.list_source_type_id = b.data_source_id
2963 AND a.enabled_flag = 'Y'
2964 AND b.target_id = p_target_id
2965 AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
2966 WHERE d.target_id = p_target_id
2967 AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
2968 AND c.SUB_SOURCE_TYPE_ID = b.data_source_id
2969 AND c.enabled_flag = 'Y')
2970 ;
2971
2972 l_source_object VARCHAR2(151);
2973 l_from_and_where VARCHAR2(16000);
2974 l_found VARCHAR2(1);
2975 l_first VARCHAR2(1) := 'T';
2976 l_where_pos NUMBER;
2977 BEGIN
2978 x_return_status := FND_API.G_RET_STS_SUCCESS;
2979
2980 IF p_select_object_type = G_ALTERNATIVE_DATA_SOURCE THEN
2981 x_from_clause := '';
2982 OPEN c_source_object (p_select_object_id);
2983 LOOP
2984 FETCH c_source_object INTO l_source_object;
2985 EXIT WHEN c_source_object%NOTFOUND;
2986
2987 IF l_first = 'F' THEN
2988 x_from_clause := x_from_clause || ' , ';
2989 ELSE
2990 l_first := 'F';
2991 END IF;
2992 x_from_clause := x_from_clause || l_source_object;
2993 END LOOP;
2994 CLOSE c_source_object;
2995 ELSIF p_select_object_type = 'LIST' THEN
2996 IF p_is_b2b_custprof THEN
2997 x_from_clause := 'ams_list_entries e';
2998 ELSE
2999 x_from_clause := 'ams_list_headers_all l, ams_list_entries e';
3000 END IF;
3001 ELSIF p_select_object_type = 'CSCH' THEN
3002 x_from_clause := 'ams_act_lists l, ams_list_entries e';
3003 ELSIF p_select_object_type = 'CELL' THEN
3004 x_from_clause := 'ams_party_market_segments aps';
3005 ELSIF p_select_object_type = 'DIWB' THEN
3006 get_from_sql (
3007 p_workbook_owner_name => p_workbook_owner,
3008 p_workbook_name => p_workbook_name,
3009 p_worksheet_name => p_worksheet_name,
3010 x_from_sql => l_from_and_where,
3011 x_found => l_found,
3012 x_return_status => x_return_status
3013 );
3014 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3016 END IF;
3017
3018 l_where_pos := INSTR (UPPER (l_from_and_where), 'WHERE');
3019
3020 -- no where clause
3021 IF l_where_pos = 0 THEN
3022 x_from_clause := SUBSTR (l_from_and_where, L_FROM_OFFSET);
3023 ELSE
3024 x_from_clause := SUBSTR (l_from_and_where, L_FROM_OFFSET, l_where_pos - L_FROM_OFFSET);
3025 END IF;
3026 END IF;
3027 END get_from_clause;
3028
3029
3030 ---------------------------------------------------------------
3031 -- Purpose:
3032 -- Get the filter for one selected data source.
3033 --
3034 -- Parameter:
3035 -- p_object_type
3036 -- p_object_id
3037 -- p_select_object_type
3038 -- p_select_object_id
3039 -- x_where_clause
3040 -- x_return_status
3041 ---------------------------------------------------------------
3042 PROCEDURE get_where_clause (
3043 p_object_type IN VARCHAR2,
3044 p_object_id IN NUMBER,
3045 p_select_object_type IN VARCHAR2,
3046 p_select_object_id IN NUMBER,
3047 p_workbook_owner IN VARCHAR2,
3048 p_workbook_name IN VARCHAR2,
3049 p_worksheet_name IN VARCHAR2,
3050 p_is_b2b_custprof IN BOOLEAN,
3051 x_where_clause OUT NOCOPY VARCHAR2,
3052 x_return_status OUT NOCOPY VARCHAR2
3053 )
3054 IS
3055 L_API_NAME CONSTANT VARCHAR2(30) := 'get_where_clause';
3056 L_WHERE_OFFSET CONSTANT NUMBER := 6;
3057
3058 CURSOR c_target_field (p_model_id IN NUMBER) IS
3059 SELECT ds.source_object_name || '.' || field.source_column_name
3060 FROM ams_dm_models_all_b model, ams_dm_targets_b target, ams_list_src_fields field , ams_list_src_types ds
3061 WHERE model.model_id = p_model_id
3062 AND target.target_id = model.target_id
3063 AND field.list_source_field_id = target.source_field_id
3064 AND ds.list_source_type_id = target.target_source_id
3065 ;
3066
3067 CURSOR c_model_child_ds (p_model_id IN NUMBER) IS
3068 SELECT source.data_source_id
3069 FROM ams_dm_models_all_b model, ams_dm_target_sources source, ams_list_src_types lst
3070 WHERE model.model_id = p_model_id
3071 AND source.target_id = model.target_id
3072 AND lst.list_source_type_id = source.data_source_id
3073 AND lst.enabled_flag = 'Y'
3074 AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
3075 WHERE d.target_id = model.target_id
3076 AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
3077 AND c.SUB_SOURCE_TYPE_ID = source.data_source_id
3078 AND c.enabled_flag = 'Y')
3079 ;
3080
3081 CURSOR c_score_child_ds (p_score_id IN NUMBER) IS
3082 SELECT source.data_source_id
3083 FROM ams_dm_scores_all_b score, ams_dm_models_all_b model, ams_dm_target_sources source, ams_list_src_types lst
3084 WHERE score.score_id = p_score_id
3085 AND model.model_id = score.model_id
3086 AND source.target_id = model.target_id
3087 AND lst.list_source_type_id = source.data_source_id
3088 AND lst.enabled_flag = 'Y'
3089 AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
3090 WHERE d.target_id = model.target_id
3091 AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
3092 AND c.SUB_SOURCE_TYPE_ID = source.data_source_id
3093 AND c.enabled_flag = 'Y')
3094 ;
3095
3096 l_perz_filter VARCHAR2(15000);
3097 l_relation_cond VARCHAR2(15000);
3098 l_composite_relation_cond VARCHAR2(15000) := '';
3099 l_wb_filter VARCHAR2(15000);
3100 l_from_and_where VARCHAR2(15000);
3101 l_found VARCHAR2(1);
3102 l_where_pos NUMBER;
3103 l_child_ds_id NUMBER;
3104 BEGIN
3105 x_return_status := FND_API.G_RET_STS_SUCCESS;
3106
3107 IF p_select_object_type = G_ALTERNATIVE_DATA_SOURCE THEN
3108 IF p_object_type = G_OBJECT_TYPE_MODEL THEN
3109 OPEN c_target_field (p_object_id);
3110 FETCH c_target_field INTO x_where_clause;
3111 CLOSE c_target_field;
3112
3113 x_where_clause := x_where_clause || ' IS NOT NULL ';
3114 END IF;
3115
3116 get_perz_filter (
3117 p_object_type => p_object_type,
3118 p_object_id => p_object_id,
3119 p_data_source_id => p_select_object_id,
3120 x_filter => l_perz_filter,
3121 x_return_status => x_return_status
3122 );
3123 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3125 END IF;
3126
3127 l_relation_cond := '';
3128 IF p_object_type = G_OBJECT_TYPE_MODEL THEN
3129 OPEN c_model_child_ds (p_object_id);
3130 LOOP
3131 FETCH c_model_child_ds INTO l_child_ds_id;
3132 EXIT WHEN c_model_child_ds%NOTFOUND;
3133
3134 get_related_ds_condition ( p_master_ds_id => p_select_object_id,
3135 p_child_ds_id => l_child_ds_id,
3136 x_sql_stmt => l_relation_cond);
3137 IF LENGTH(l_composite_relation_cond) > 0 THEN
3138 l_composite_relation_cond := l_composite_relation_cond || ' AND ';
3139 END IF;
3140 l_composite_relation_cond := l_composite_relation_cond || l_relation_cond;
3141 END LOOP;
3142 CLOSE c_model_child_ds;
3143 ELSE
3144 OPEN c_score_child_ds (p_object_id);
3145 LOOP
3146 FETCH c_score_child_ds INTO l_child_ds_id;
3147 EXIT WHEN c_score_child_ds%NOTFOUND;
3148
3149 get_related_ds_condition ( p_master_ds_id => p_select_object_id,
3150 p_child_ds_id => l_child_ds_id,
3151 x_sql_stmt => l_relation_cond);
3152 IF LENGTH(l_composite_relation_cond) > 0 THEN
3153 l_composite_relation_cond := l_composite_relation_cond || ' AND ';
3154 END IF;
3155 l_composite_relation_cond := l_composite_relation_cond || l_relation_cond;
3156 END LOOP;
3157 CLOSE c_score_child_ds;
3158 END IF;
3159
3160 IF (AMS_DEBUG_HIGH_ON) THEN
3161 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: relation condition : ' || l_composite_relation_cond);
3162 END IF;
3163
3164 get_wb_filter (
3165 p_workbook_owner => p_workbook_owner,
3166 p_workbook_name => p_workbook_name,
3167 p_worksheet_name => p_worksheet_name,
3168 x_filter => l_wb_filter,
3169 x_return_status => x_return_status
3170 );
3171 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3173 END IF;
3174
3175 IF l_perz_filter IS NOT NULL THEN
3176 IF x_where_clause IS NOT NULL THEN
3177 x_where_clause := x_where_clause || ' AND ' || l_perz_filter ;
3178 ELSE
3179 x_where_clause := l_perz_filter;
3180 END IF;
3181 END IF;
3182
3183 IF LENGTH(l_composite_relation_cond) > 0 THEN
3184 IF (AMS_DEBUG_HIGH_ON) THEN
3185 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: relation condition being appended to where clause :: ' || l_composite_relation_cond);
3186 END IF;
3187 IF x_where_clause IS NOT NULL THEN
3188 x_where_clause := x_where_clause || ' AND ' || l_composite_relation_cond ;
3189 ELSE
3190 x_where_clause := l_composite_relation_cond;
3191 END IF;
3192 END IF;
3193
3194 IF l_wb_filter IS NOT NULL THEN
3195 IF x_where_clause IS NOT NULL THEN
3196 x_where_clause := x_where_clause || ' AND ' || l_wb_filter ;
3197 ELSE
3198 x_where_clause := l_wb_filter;
3199 END IF;
3200 END IF;
3201
3202 --IF l_perz_filter IS NOT NULL AND l_wb_filter IS NOT NULL THEN
3203 -- x_where_clause := x_where_clause || ' AND ' || l_perz_filter || ' AND ' || l_wb_filter;
3204 --ELSIF l_perz_filter IS NOT NULL AND l_wb_filter IS NULL THEN
3205 -- x_where_clause := x_where_clause || ' AND ' || l_perz_filter;
3206 --ELSIF l_perz_filter IS NULL AND l_wb_filter IS NOT NULL THEN
3207 -- x_where_clause := x_where_clause || ' AND ' || l_wb_filter;
3208 --END IF;
3209 ELSIF p_select_object_type = 'LIST' THEN
3210 IF p_is_b2b_custprof THEN
3211 x_where_clause := 'e.list_header_id = :list_header_id ' ||
3212 'AND e.enabled_flag = ''Y''';
3213 ELSE
3214 x_where_clause := 'l.list_header_id = :list_header_id ' ||
3215 'AND e.list_header_id = l.list_header_id ' ||
3216 'AND e.enabled_flag = ''Y''';
3217 END IF;
3218 ELSIF p_select_object_type = 'CSCH' THEN
3219 x_where_clause := 'l.list_used_by = ''CSCH'' ' ||
3220 'AND l.list_used_by_id = :incl_object_id ' ||
3221 'AND l.list_act_type = ''TARGET'' ' ||
3222 'AND e.list_header_id = l.list_header_id ' ||
3223 'AND e.enabled_flag = ''Y''';
3224 ELSIF p_select_object_type = 'CELL' THEN
3225 x_where_clause := 'aps.market_segment_id = :object_id';
3226 ELSIF p_select_object_type = 'DIWB' THEN
3227 get_wb_filter (
3228 p_workbook_owner => p_workbook_owner,
3229 p_workbook_name => p_workbook_name,
3230 p_worksheet_name => p_worksheet_name,
3231 x_filter => l_from_and_where,
3232 x_return_status => x_return_status
3233 );
3234 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3236 END IF;
3237
3238 x_where_clause := l_from_and_where;
3239 END IF;
3240 IF (AMS_DEBUG_HIGH_ON) THEN
3241 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: where clause :: ' || x_where_clause);
3242 END IF;
3243 END get_where_clause;
3244
3245
3246 ---------------------------------------------------------------
3247 -- Purpose:
3248 -- Get filter conditions based on saved filters
3249 -- using the personzliation framework.
3250 --
3251 -- Note:
3252 -- Parameter_name in advanced filter is saved
3253 -- as the list source field id, so the id
3254 -- needs to be de-referenced to get the filter
3255 -- column name.
3256 --
3257 -- Parameter:
3258 -- p_object_type
3259 -- p_object_id
3260 -- x_filter
3261 -- x_return_status OUT VARCHAR2
3262 ---------------------------------------------------------------
3263 PROCEDURE get_perz_filter (
3264 p_object_type IN VARCHAR2,
3265 p_object_id IN NUMBER,
3266 p_data_source_id IN NUMBER,
3267 x_filter OUT NOCOPY VARCHAR2,
3268 x_return_status OUT NOCOPY VARCHAR2
3269 )
3270 IS
3271 L_API_NAME CONSTANT VARCHAR2(30) := 'get_perz_filter';
3272
3273 l_filters_tab AMS_Adv_Filter_PVT.filter_rec_tbl_type;
3274 l_column_name VARCHAR2(30);
3275
3276 l_msg_count NUMBER;
3277 l_msg_data VARCHAR2(4000);
3278 BEGIN
3279 x_return_status := FND_API.G_RET_STS_SUCCESS;
3280
3281 AMS_Adv_Filter_PVT.get_filter_data (
3282 p_objType => p_object_type,
3283 p_objectId => p_object_id,
3284 p_dataSourceId => p_data_source_id,
3285 x_return_status => x_return_status,
3286 x_msg_count => l_msg_count,
3287 x_msg_data => l_msg_data,
3288 x_filters => l_filters_tab
3289 );
3290 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3292 END IF;
3293
3294 IF l_filters_tab.COUNT = 0 THEN
3295 x_filter := NULL;
3296 RETURN;
3297 END IF;
3298
3299 --
3300 -- initialize the filter, so the result is like
3301 -- - filter a
3302 -- - in the loop
3303 -- AND filter b AND filter c...
3304 x_filter := l_filters_tab(1).parameter_name || ' ' || l_filters_tab(1).parameter_condition || ' ''' || l_filters_tab(1).parameter_value || '''';
3305 FOR i IN 2..l_filters_tab.COUNT LOOP
3306 x_filter := x_filter || ' AND ' || l_filters_tab(i).parameter_name || ' ' || l_filters_tab(i).parameter_condition || ' ''' || l_filters_tab(i).parameter_value || '''';
3307 END LOOP;
3308
3309 IF (AMS_DEBUG_HIGH_ON) THEN
3310 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: perz filter : ' || x_filter);
3311 END IF;
3312 END get_perz_filter;
3313
3314
3315 ---------------------------------------------------------------
3316 -- Purpose:
3317 -- Get filter conditions as defined by a Discoverer
3318 -- worksheet.
3319 --
3320 -- NOTE
3321 -- Discoverer SQL statements could span across multiple
3322 -- ams_discoverer_sql records. Must use combination of
3323 -- owner, workbook, and worksheet to query table for
3324 -- complete sql statement.
3325 --
3326 -- Parameter:
3327 -- p_workbook_owner
3328 -- p_workbook_name
3329 -- p_worksheet_name
3330 -- x_filter
3331 -- x_return_status
3332 ---------------------------------------------------------------
3333 PROCEDURE get_wb_filter (
3334 p_workbook_owner IN VARCHAR2,
3335 p_workbook_name IN VARCHAR2,
3336 p_worksheet_name IN VARCHAR2,
3337 x_filter OUT NOCOPY VARCHAR2,
3338 x_return_status OUT NOCOPY VARCHAR2
3339 )
3340 IS
3341 L_API_NAME CONSTANT VARCHAR2(30) := 'get_wb_filter';
3342 L_WHERE_OFFSET CONSTANT NUMBER := 6;
3343
3344 l_from_and_where VARCHAR2(16000);
3345 l_found VARCHAR2(1);
3346 l_where_pos NUMBER;
3347 BEGIN
3348 x_return_status := FND_API.G_RET_STS_SUCCESS;
3349
3350 IF p_workbook_owner IS NULL THEN
3351 -- no workbook filter selected
3352 x_filter := NULL;
3353 RETURN;
3354 END IF;
3355
3356 get_from_sql (
3357 p_workbook_owner_name => p_workbook_owner,
3358 p_workbook_name => p_workbook_name,
3359 p_worksheet_name => p_worksheet_name,
3360 x_from_sql => l_from_and_where,
3361 x_found => l_found,
3362 x_return_status => x_return_status
3363 );
3364 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3366 END IF;
3367
3368 l_where_pos := INSTR (UPPER (l_from_and_where), 'WHERE');
3369 -- get the filter condition only if where clause is specified
3370 IF l_where_pos <> 0 THEN
3371 x_filter := SUBSTR (l_from_and_where, l_where_pos + L_WHERE_OFFSET);
3372 END IF;
3373 END get_wb_filter;
3374
3375
3376 ---------------------------------------------------------------
3377 -- Purpose:
3378 -- Get Target Positive Values
3379 --
3380 -- Note:
3381 -- A Data Mining Target field may have multiple positive
3382 -- target values defined in AMS_DM_TARGET_VALUES_B along with
3383 -- value comparison conditions. For example, the target column
3384 -- is considered positive if it is >= 10 AND <= 20
3385 -- This procedure constructs the sql statement that combines all
3386 -- positive values defined for the target..
3387 --
3388 -- Parameter:
3389 -- p_target_id IN NUMBER
3390 -- p_target_field IN VARCHAR2
3391 -- x_sql_stmt OUT VARCHAR2
3392 ---------------------------------------------------------------
3393 PROCEDURE get_target_positive_values (
3394 p_target_id IN NUMBER,
3395 p_target_field IN VARCHAR2,
3396 x_sql_stmt OUT NOCOPY VARCHAR2
3397 )
3398 IS
3399 L_API_NAME CONSTANT VARCHAR2(30) := 'get_target_positive_values';
3400
3401
3402 CURSOR c_target (p_target_id IN NUMBER) IS
3403 SELECT target_value,
3404 target_operator,
3405 range_value
3406 FROM ams_dm_target_values_b
3407 WHERE target_id = p_target_id;
3408
3409 l_target_rec c_target%ROWTYPE;
3410
3411 l_str_not_equals VARCHAR2(4000);
3412 l_str_filter VARCHAR2(4000);
3413 BEGIN
3414 -- Fetch all the positive target value records for the target
3415 OPEN c_target(p_target_id);
3416 LOOP
3417 FETCH c_target INTO l_target_rec;
3418 EXIT WHEN c_target%NOTFOUND;
3419
3420 -- append <> conditions as AND conditions at the end
3421 IF l_target_rec.target_operator = '<>' THEN
3422 IF l_str_not_equals IS NULL THEN
3423 l_str_not_equals := p_target_field || ' ' || l_target_rec.target_operator || ' ''' || l_target_rec.target_value || '''';
3424 ELSE
3425 l_str_not_equals := l_str_not_equals || ' AND ' || p_target_field || ' ' || l_target_rec.target_operator || ' ''' || l_target_rec.target_value || '''';
3426 END IF;
3427 ELSIF l_target_rec.target_operator = 'BETWEEN' THEN
3428 --
3429 -- initialize the sql statement, so the result is like
3430 -- target_field = x
3431 -- OR target_field > y
3432 -- OR target_field <= z
3433 -- OR target_field between a and b
3434 DECLARE
3435 l_low NUMBER;
3436 l_high NUMBER;
3437 BEGIN
3438 -- try to convert to numbers to do
3439 -- between numbers, else use chars
3440 -- if invalid number exception thrown
3441 l_low := TO_NUMBER (l_target_rec.target_value);
3442 l_high := TO_NUMBER (l_target_rec.range_value);
3443
3444 IF l_str_filter IS NULL THEN
3445 l_str_filter := p_target_field || ' ' || l_target_rec.target_operator || ' ' || l_low || ' AND ' || l_high;
3446 ELSE
3447 l_str_filter := l_str_filter || ' OR ' || p_target_field || ' ' || l_target_rec.target_operator || ' ' || l_low || ' AND ' || l_high;
3448 END IF;
3449 EXCEPTION
3450 WHEN VALUE_ERROR THEN
3451 IF l_str_filter IS NULL THEN
3452 l_str_filter := p_target_field || ' ' || l_target_rec.target_operator || ' ''' || l_target_rec.target_value || ''' AND ''' || l_target_rec.range_value || '''';
3453 ELSE
3454 l_str_filter := l_str_filter || ' OR ' || p_target_field || ' ' || l_target_rec.target_operator || ' ''' || l_target_rec.target_value || ''' AND ''' || l_target_rec.range_value || '''';
3455 END IF;
3456 END;
3457 ELSE
3458 IF l_str_filter IS NULL THEN
3459 l_str_filter := p_target_field || ' ' || l_target_rec.target_operator || ' ''' || l_target_rec.target_value || '''';
3460 ELSE
3461 l_str_filter := l_str_filter || ' OR ' || p_target_field || ' ' || l_target_rec.target_operator || ' ''' || l_target_rec.target_value || '''';
3462 END IF;
3463 END IF;
3464 END LOOP;
3465 CLOSE c_target;
3466
3467 IF l_str_not_equals IS NOT NULL AND l_str_filter IS NOT NULL THEN
3468 x_sql_stmt := '(' || l_str_filter || ') AND (' || l_str_not_equals || ')';
3469 ELSIF l_str_not_equals IS NULL THEN
3470 x_sql_stmt := l_str_filter;
3471 ELSE
3472 x_sql_stmt := l_str_not_equals;
3473 END IF;
3474
3475 IF (AMS_DEBUG_HIGH_ON) THEN
3476 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: target positive values: ' || x_sql_stmt);
3477 END IF;
3478 END get_target_positive_values;
3479
3480 ---------------------------------------------------------------
3481 -- Purpose:
3482 -- Determine whether a data source attached to
3483 -- a of model is B2B or B2C.
3484 --
3485 -- Parameter:
3486 -- p_model_id IN NUMBER
3487 -- x_is_b2b OUT BOOLEAN
3488 ---------------------------------------------------------------
3489
3490 PROCEDURE is_b2b_data_source (
3491 p_model_id IN NUMBER,
3492 x_is_b2b OUT NOCOPY BOOLEAN
3493 )
3494 IS
3495 L_API_NAME VARCHAR2(30) := 'Is B2B Data Source';
3496
3497 CURSOR c_data_source (p_model_id IN NUMBER) IS
3498 --SELECT d.SOURCE_TYPE_CODE
3499 SELECT d.SOURCE_CATEGORY
3500 FROM ams_dm_models_all_b m,ams_dm_targets_b t,ams_list_src_types d
3501 WHERE m.model_id = p_model_id
3502 AND m.target_id = t.target_id
3503 AND t.DATA_SOURCE_ID=d.LIST_SOURCE_TYPE_ID;
3504
3505 --l_ds_code VARCHAR2(30);
3506 l_ds_cat VARCHAR2(30);
3507
3508 BEGIN
3509 x_is_b2b := TRUE;
3510
3511 OPEN c_data_source (p_model_id);
3512 FETCH c_data_source INTO l_ds_cat;
3513 CLOSE c_data_source;
3514
3515 IF SUBSTR(l_ds_cat , 0 , 3) = 'B2C' then
3516 x_is_b2b := FALSE;
3517 END IF;
3518
3519 IF (AMS_DEBUG_HIGH_ON) THEN
3520 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: '|| p_model_id || ' :: ' || l_ds_cat );
3521 END IF;
3522
3523
3524 END is_b2b_data_source;
3525
3526 ---------------------------------------------------------------
3527 -- Purpose:
3528 -- To get the workflow URL.
3529 --
3530 -- Parameter:
3531 -- p_item_key IN VARCHAR2,
3532 -- x_monitor_url OUT NOCOPY VARCHAR2
3533 ---------------------------------------------------------------
3534
3535 PROCEDURE get_wf_url (
3536 p_item_key IN VARCHAR2,
3537 x_monitor_url OUT NOCOPY VARCHAR2
3538 )
3539 IS
3540 l_item_key VARCHAR2(240);
3541
3542 BEGIN
3543 l_item_key := p_item_key;
3544 x_monitor_url := wf_monitor.geturl(wf_core.TRANSLATE('WF_WEB_AGENT'), AMS_WFMOD_PVT.G_DEFAULT_ITEMTYPE, l_item_key, 'NO');
3545
3546 END get_wf_url;
3547
3548
3549 ---------------------------------------------------------------
3550 -- Purpose:
3551 -- Get the relation condition between a master data source and given child data source
3552 --
3553 -- Note:
3554 --
3555 -- Parameter:
3556 -- p_master_ds_id IN NUMBER
3557 -- p_child_ds_id IN NUMBER
3558 -- x_sql_stmt OUT VARCHAR2
3559 ---------------------------------------------------------------
3560 PROCEDURE get_related_ds_condition (
3561 p_master_ds_id IN NUMBER,
3562 p_child_ds_id IN NUMBER,
3563 x_sql_stmt OUT NOCOPY VARCHAR2
3564 )
3565 IS
3566 L_API_NAME CONSTANT VARCHAR2(30) := 'get_related_ds_condition';
3567
3568
3569 CURSOR c_cond (p_master_ds_id IN NUMBER , p_child_ds_id IN NUMBER) IS
3570 SELECT a.SOURCE_OBJECT_NAME as CHILD_SOURCE,
3571 b.SUB_SOURCE_TYPE_PK_COLUMN as CHILD_COLUMN,
3572 c.SOURCE_OBJECT_NAME as PARENT_SOURCE,
3573 NVL(b.MASTER_SOURCE_TYPE_PK_COLUMN , c.SOURCE_OBJECT_PK_FIELD) as PARENT_COLUMN
3574 FROM ams_list_src_types a, ams_list_src_type_assocs b, ams_list_src_types c
3575 where b.MASTER_SOURCE_TYPE_ID = p_master_ds_id
3576 and b.SUB_SOURCE_TYPE_ID = p_child_ds_id
3577 and b.ENABLED_FLAG = 'Y'
3578 and a.LIST_SOURCE_TYPE_ID=b.SUB_SOURCE_TYPE_ID
3579 and c.LIST_SOURCE_TYPE_ID = b.MASTER_SOURCE_TYPE_ID
3580 and a.enabled_flag = 'Y'
3581 and c.enabled_flag = 'Y'
3582 ;
3583
3584 l_cond_rec c_cond%ROWTYPE;
3585 l_first VARCHAR2(1) := 'T';
3586 l_length NUMBER;
3587
3588 BEGIN
3589
3590 -- Fetch all the positive target value records for the target
3591 OPEN c_cond(p_master_ds_id , p_child_ds_id);
3592 LOOP
3593 FETCH c_cond INTO l_cond_rec;
3594 EXIT WHEN c_cond%NOTFOUND;
3595
3596 IF l_first = 'F' THEN
3597 x_sql_stmt := x_sql_stmt || ' AND ';
3598 END IF;
3599 x_sql_stmt := x_sql_stmt || l_cond_rec.CHILD_SOURCE || '.' || l_cond_rec.CHILD_COLUMN || ' = ' || l_cond_rec.PARENT_SOURCE || '.' || l_cond_rec.PARENT_COLUMN;
3600
3601 l_first := 'F';
3602
3603 END LOOP;
3604 CLOSE c_cond;
3605
3606 IF (AMS_DEBUG_HIGH_ON) THEN
3607 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: relation condition : ' || x_sql_stmt);
3608 END IF;
3609 END get_related_ds_condition;
3610
3611 ---------------------------------------------------------------
3612 -- Purpose:
3613 -- Determine whether a target is attached to
3614 -- a seeded DM data source
3615 --
3616 -- Parameter:
3617 -- p_target_id IN NUMBER
3618 -- x_is_seeded OUT BOOLEAN
3619 ---------------------------------------------------------------
3620
3621 PROCEDURE is_target_attached_to_seeded (
3622 p_target_id IN NUMBER,
3623 x_is_seeded OUT NOCOPY BOOLEAN
3624 )
3625 IS
3626 L_API_NAME VARCHAR2(30) := 'is_target_attached_to_seeded';
3627
3628 CURSOR c_data_source (p_target_id IN NUMBER) IS
3629 SELECT 1
3630 FROM ams_dm_targets_b t,ams_list_src_types d
3631 WHERE t.target_id = p_target_id
3632 AND t.DATA_SOURCE_ID=d.LIST_SOURCE_TYPE_ID
3633 AND d.SOURCE_TYPE_CODE = 'AMS_DM_PARTY_ATTRIBUTES_V'
3634 UNION
3635 SELECT 1
3636 FROM ams_dm_target_sources t,ams_list_src_types d
3637 WHERE t.target_id = p_target_id
3638 AND t.DATA_SOURCE_ID=d.LIST_SOURCE_TYPE_ID
3639 AND d.SOURCE_TYPE_CODE = 'AMS_DM_PARTY_ATTRIBUTES_V'
3640 ;
3641
3642 l_dummy NUMBER;
3643
3644 BEGIN
3645 x_is_seeded := FALSE;
3646
3647 OPEN c_data_source (p_target_id);
3648 FETCH c_data_source INTO l_dummy;
3649 CLOSE c_data_source;
3650
3651 IF l_dummy IS NOT NULL THEN
3652 x_is_seeded := TRUE;
3653 IF (AMS_DEBUG_HIGH_ON) THEN
3654 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: IS SEEDED :: TRUE FOR TARGET ID : '|| to_char(p_target_id));
3655 END IF;
3656 END IF;
3657
3658
3659
3660 END is_target_attached_to_seeded;
3661
3662 ---------------------------------------------------------------
3663 -- Purpose:
3664 -- Determine whether a model is Org Product Affinity
3665 --
3666 -- Parameter:
3667 -- p_model_id IN NUMBER
3668 -- x_is_org_prod OUT BOOLEAN
3669 ---------------------------------------------------------------
3670
3671 PROCEDURE is_org_prod_affn (
3672 p_model_id IN NUMBER,
3673 x_is_org_prod OUT NOCOPY BOOLEAN
3674 )
3675 IS
3676 L_API_NAME VARCHAR2(30) := 'Is Org Prod Affinity';
3677
3678 CURSOR c_data_source_type_code (p_model_id IN NUMBER) IS
3679 SELECT d.source_type_code
3680 FROM ams_dm_models_all_b m,ams_dm_targets_b t,ams_list_src_types d
3681 WHERE m.model_id = p_model_id
3682 AND m.target_id = t.target_id
3683 AND t.data_source_id=d.list_source_type_id;
3684
3685 l_ds_type_code VARCHAR2(30);
3686
3687 BEGIN
3688 x_is_org_prod := FALSE;
3689
3690 OPEN c_data_source_type_code(p_model_id);
3691 FETCH c_data_source_type_code INTO l_ds_type_code;
3692 CLOSE c_data_source_type_code;
3693
3694 IF l_ds_type_code = 'ORGANIZATION_LIST' then
3695 x_is_org_prod := TRUE;
3696 END IF;
3697
3698 IF (AMS_DEBUG_HIGH_ON) THEN
3699 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: '|| p_model_id || ' :: ' || l_ds_type_code );
3700 END IF;
3701
3702 END is_org_prod_affn;
3703
3704 ---------------------------------------------------------------
3705 -- Purpose:
3706 -- Check the status of selections to ensure that they are
3707 -- still valid. Only called for seeded models.
3708 --
3709 -- NOTE:
3710 --
3711 --
3712 -- Parameter:
3713 -- p_model_id
3714 -- p_model_type
3715 -- p_select_object_type
3716 -- p_select_object_id
3717 -- x_return_status
3718 ---------------------------------------------------------------
3719 PROCEDURE validate_selection_status (
3720 p_model_id IN NUMBER,
3721 p_model_type IN VARCHAR2,
3722 p_workbook_owner IN VARCHAR2,
3723 p_workbook_name IN VARCHAR2,
3724 p_worksheet_name IN VARCHAR2,
3725 p_select_object_type IN VARCHAR2,
3726 p_select_object_id IN NUMBER,
3727 x_return_status OUT NOCOPY VARCHAR2
3728 )
3729 IS
3730 L_API_NAME VARCHAR2(30) := 'Validate Selection Status';
3731
3732 CURSOR c_valid_list (p_list_id IN NUMBER) IS
3733 SELECT list_name, status_code
3734 FROM ams_list_headers_vl
3735 WHERE list_header_id = p_list_id
3736 ;
3737
3738 CURSOR c_valid_cell (p_cell_id IN NUMBER) IS
3739 SELECT cell_name, status_code
3740 FROM ams_cells_vl
3741 WHERE cell_id = p_cell_id
3742 ;
3743
3744 CURSOR c_discoverer_sql (p_workbook_name IN VARCHAR2,
3745 p_worksheet_name IN VARCHAR2,
3746 p_workbook_owner_name IN VARCHAR2) IS
3747 SELECT sql_string, sequence_order
3748 FROM ams_discoverer_sql
3749 WHERE workbook_name = p_workbook_name
3750 AND worksheet_name = p_worksheet_name
3751 AND workbook_owner_name = p_workbook_owner_name
3752 ORDER BY sequence_order;
3753
3754 l_discoverer_sql_rec c_discoverer_sql%ROWTYPE;
3755 l_sql_string VARCHAR2(32767) := '';
3756 l_list_name VARCHAR2(300);
3757 l_status_code VARCHAR2(30);
3758 l_cell_name VARCHAR2(120);
3759
3760 BEGIN
3761 x_return_status := FND_API.G_RET_STS_SUCCESS;
3762
3763 IF p_select_object_type = 'LIST' THEN
3764 OPEN c_valid_list (p_select_object_id);
3765 FETCH c_valid_list INTO l_list_name, l_status_code;
3766 CLOSE c_valid_list;
3767
3768 IF (AMS_DEBUG_HIGH_ON) THEN
3769 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Selected List Name : '|| l_list_name || ', Status : ' || l_status_code );
3770 END IF;
3771
3772 IF l_status_code NOT IN ('AVAILABLE','LOCKED') THEN
3773 AMS_Utility_PVT.error_message ('AMS_DM_LIST_NOT_AVAILABLE', 'LISTNAME', l_list_name);
3774 x_return_status := FND_API.G_RET_STS_ERROR;
3775 RETURN;
3776 END IF;
3777 ELSIF p_select_object_type = 'CELL' THEN
3778 OPEN c_valid_cell (p_select_object_id);
3779 FETCH c_valid_cell INTO l_cell_name, l_status_code;
3780 CLOSE c_valid_cell;
3781
3782 IF (AMS_DEBUG_HIGH_ON) THEN
3783 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Selected Segment Name : '|| l_cell_name || ', Status : ' || l_status_code );
3784 END IF;
3785
3786 IF l_status_code <> 'AVAILABLE' THEN
3787 AMS_Utility_PVT.error_message ('AMS_DM_CELL_NOT_AVAILABLE', 'CELLNAME', l_cell_name);
3788 x_return_status := FND_API.G_RET_STS_ERROR;
3789 RETURN;
3790 END IF;
3791 ELSIF p_select_object_type = 'DIWB' THEN
3792 IF (AMS_DEBUG_HIGH_ON) THEN
3793 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Checking Workbook : '|| p_workbook_name || '::' || p_worksheet_name );
3794 END IF;
3795
3796 OPEN c_discoverer_sql (p_workbook_name,
3797 p_worksheet_name,
3798 p_workbook_owner);
3799 FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
3800 WHILE c_discoverer_sql%FOUND
3801 LOOP
3802 l_sql_string := l_sql_string || l_discoverer_sql_rec.sql_string;
3803 FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
3804 END LOOP;
3805 CLOSE c_discoverer_sql;
3806
3807 l_sql_string := upper(l_sql_string);
3808 -- Don't support "order by" and "group by" in query
3809 -- Check if query has these clauses
3810 IF instr(l_sql_string, 'ORDER BY') > 0
3811 OR instr(l_sql_string, 'GROUP BY') > 0
3812 THEN
3813 AMS_Utility_PVT.error_message ('AMS_DM_COMPLEX_WORKBOOK_SQL', 'WORKBOOKNAME', p_workbook_name || '::' || p_worksheet_name);
3814 x_return_status := FND_API.G_RET_STS_ERROR;
3815 RETURN;
3816 END IF;
3817 END IF;
3818 END validate_selection_status;
3819
3820
3821 ---------------------------------------------------------------
3822 -- Purpose:
3823 -- Check the status of product selections for prod affn model to ensure that they are
3824 -- still valid. Only called for seeded models.
3825 --
3826 -- NOTE:
3827 --
3828 --
3829 -- Parameter:
3830 -- p_model_id
3831 -- x_return_status
3832 ---------------------------------------------------------------
3833 PROCEDURE validate_product_selections (
3834 p_model_id IN NUMBER,
3835 x_return_status OUT NOCOPY VARCHAR2
3836 )
3837 IS
3838 L_API_NAME VARCHAR2(60) := 'Validate Product Affinity Selection Status';
3839
3840 CURSOR c_cats_for_model (p_model_id IN NUMBER) IS
3841 SELECT a.category_id, a.inventory_item_id, a.organization_id, a.category_set_id, b.padded_concatenated_segments
3842 FROM ams_act_products a, mtl_system_items_kfv b
3843 WHERE a.ARC_ACT_PRODUCT_USED_BY = 'MODL'
3844 AND a.ACT_PRODUCT_USED_BY_ID = p_model_id
3845 AND a.inventory_item_id = b.inventory_item_id(+)
3846 AND a.organization_id = b.organization_id(+)
3847 AND a.category_set_id in (select distinct category_set_id from ENI_PROD_DEN_HRCHY_PARENTS_V)
3848 ;
3849
3850 CURSOR c_valid_cat (p_cat_id IN NUMBER) IS
3851 SELECT 1
3852 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
3853 WHERE category_id = p_cat_id
3854 AND (disable_date IS NULL OR disable_date > SYSDATE)
3855 ;
3856
3857 CURSOR c_valid_prod (p_cat_id IN NUMBER, p_prod_id IN NUMBER, p_org_id IN NUMBER, p_cat_set_id IN NUMBER) IS
3858 SELECT 1
3859 FROM mtl_system_items_kfv items, mtl_item_categories_v cats
3860 WHERE cats.category_id = p_cat_id
3861 AND cats.inventory_item_id = p_prod_id
3862 AND items.inventory_item_id = p_prod_id
3863 AND UPPER(items.INVENTORY_ITEM_STATUS_CODE) <> 'INACTIVE'
3864 AND items.organization_id = p_org_id
3865 AND cats.organization_id = p_org_id
3866 AND cats.category_set_id = p_cat_set_id
3867 ;
3868
3869 l_prod_name VARCHAR2(151);
3870 l_cat_id NUMBER;
3871 l_prod_id NUMBER;
3872 l_dummy NUMBER;
3873 l_org_id NUMBER;
3874 l_cat_set_id NUMBER;
3875
3876 BEGIN
3877 x_return_status := FND_API.G_RET_STS_SUCCESS;
3878
3879 -- check for product affinity model that category belongs to the product reporting category set
3880 -- and product belongs to that category
3881 OPEN c_cats_for_model (p_model_id);
3882 LOOP
3883 FETCH c_cats_for_model INTO l_cat_id, l_prod_id, l_org_id, l_cat_set_id, l_prod_name;
3884 EXIT WHEN c_cats_for_model%NOTFOUND;
3885 l_dummy := NULL;
3886 --validate category stll belongs to the product reporting category set
3887 OPEN c_valid_cat (l_cat_id);
3888 FETCH c_valid_cat INTO l_dummy;
3889 CLOSE c_valid_cat;
3890
3891 IF (AMS_DEBUG_HIGH_ON) THEN
3892 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Checking category ID: '|| l_cat_id || ' and Product name: ' || l_prod_name);
3893 END IF;
3894
3895 IF l_dummy IS NULL THEN
3896 AMS_Utility_PVT.error_message ('AMS_DM_CAT_NOT_IN_SET');
3897 x_return_status := FND_API.G_RET_STS_ERROR;
3898 RETURN;
3899 END IF;
3900
3901 --check the product still belongs to this category
3902 IF l_prod_id IS NOT NULL THEN
3903 l_dummy := NULL;
3904 OPEN c_valid_prod (l_cat_id, l_prod_id, l_org_id, l_cat_set_id);
3905 FETCH c_valid_prod INTO l_dummy;
3906 CLOSE c_valid_prod;
3907
3908 IF l_dummy IS NULL THEN
3909 AMS_Utility_PVT.error_message ('AMS_DM_PROD_NOT_IN_CAT', 'PRODNAME', l_prod_name);
3910 x_return_status := FND_API.G_RET_STS_ERROR;
3911 RETURN;
3912 END IF;
3913 END IF;
3914 END LOOP;
3915 IF c_cats_for_model%ROWCOUNT = 0 THEN
3916 IF (AMS_DEBUG_HIGH_ON) THEN
3917 AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Raising error...No categories/products selected.');
3918 END IF;
3919 AMS_Utility_PVT.error_message ('AMS_DM_PROD_SEL_EMPTY');
3920 x_return_status := FND_API.G_RET_STS_ERROR;
3921 CLOSE c_cats_for_model;
3922 RETURN;
3923 END IF;
3924 CLOSE c_cats_for_model;
3925 END validate_product_selections;
3926 END AMS_DMSelection_PVT;