1 PACKAGE AMS_CampaignRules_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvcbrs.pls 115.36 2004/07/15 21:56:48 asaha ship $ */
3
4
5 -----------------------------------------------------------------------
6 -- PROCEDURE
7 -- handle_camp_status
8 --
9 -- PURPOSE
10 -- Validate the campaign status.
11 --
12 -- NOTES
13 -- 1. If the user status is not specified, default it;
14 -- otherwise validate it.
15 -- 2. The system status code will be determined by the user status.
16 -----------------------------------------------------------------------
17 PROCEDURE handle_camp_status(
18 p_user_status_id IN NUMBER,
19 x_status_code OUT NOCOPY VARCHAR2,
20 x_return_status OUT NOCOPY VARCHAR2
21 );
22
23
24 -----------------------------------------------------------------------
25 -- PROCEDURE
26 -- handle_camp_inherit_flag
27 --
28 -- PURPOSE
29 -- Validate the inherit_attributes_flag.
30 --
31 -- NOTES
32 -- 1. The inherit_attributes_flag will be set to 'Y' only for
33 -- execution campaigns under multi-channel campaigns.
34 -- 2. The parent_campaign_id will be validated if not null, and
35 -- the rollup_type of the parent campaign will also be checked.
36 -----------------------------------------------------------------------
37 PROCEDURE handle_camp_inherit_flag(
38 p_parent_id IN NUMBER,
39 p_rollup_type IN VARCHAR2,
40 x_inherit_flag OUT NOCOPY VARCHAR2,
41 x_return_status OUT NOCOPY VARCHAR2
42 );
43
44
45 -----------------------------------------------------------------------
46 -- PROCEDURE
47 -- create_camp_association
48 --
49 -- PURPOSE
50 -- Create an object association record when an event is associated
51 -- to a campaign.
52 --
53 -- NOTES
54 -- 1. May need to delete the old association for update.
55 -----------------------------------------------------------------------
56 PROCEDURE create_camp_association(
57 p_campaign_id IN NUMBER,
58 p_event_id IN NUMBER,
59 p_event_type IN VARCHAR2,
60 x_return_status OUT NOCOPY VARCHAR2
61 );
62
63
64 -----------------------------------------------------------------------
65 -- PROCEDURE
66 -- udpate_camp_source_code
67 --
68 -- PURPOSE
69 -- Handle the business rules regarding source code update.
70 --
71 -- NOTES
72 -- 1. cascade_source_code_flag cannot be changed if schedules exist.
73 -- 2. When global_flag is updated, a new source code will be
74 -- generated if source code is not cascaded to existing schedules.
75 -- 3. source_code cannot be updated if it is cascaded to existing
76 -- schedules.
77 -----------------------------------------------------------------------
78 PROCEDURE update_camp_source_code(
79 p_campaign_id IN NUMBER,
80 p_source_code IN VARCHAR2,
81 p_global_flag IN VARCHAR2,
82 x_source_code OUT NOCOPY VARCHAR2,
83 p_related_source_object IN VARCHAR2 := NULL,
84 p_related_source_id IN NUMBER := NULL,
85 x_return_status OUT NOCOPY VARCHAR2
86 );
87
88
89 -----------------------------------------------------------------------
90 -- PROCEDURE
91 -- check_camp_update
92 --
93 -- PURPOSE
94 -- Check if campaign record can be updated.
95 --
96 -- NOTES
97 -- 1. Lock certain fields after available.
98 -----------------------------------------------------------------------
99 PROCEDURE check_camp_update(
100 p_camp_rec IN AMS_Campaign_PVT.camp_rec_type,
101 p_complete_rec IN AMS_Campaign_PVT.camp_rec_type,
102 x_return_status OUT NOCOPY VARCHAR2
103 );
104
105
106 -----------------------------------------------------------------------
107 -- PROCEDURE
108 -- check_camp_template_flag
109 --
110 -- PURPOSE
111 -- Check the rules related to template campaigns.
112 --
113 -- NOTES
114 -- 1. Channel is required before SUBMITTED-TA for non-template campaigns.
115 -- 2. Template campaigns can only be associated to template campaigns.
116 -----------------------------------------------------------------------
117 PROCEDURE check_camp_template_flag(
118 p_parent_id IN NUMBER,
119 p_channel_id IN NUMBER,
120 p_template_flag IN VARCHAR2,
121 p_status_code IN VARCHAR2,
122 p_rollup_type IN VARCHAR2,
123 p_media_type IN VARCHAR2,
124 x_return_status OUT NOCOPY VARCHAR2
125 );
126
127
128 -----------------------------------------------------------------------
129 -- PROCEDURE
130 -- check_camp_media_type
131 --
132 -- PURPOSE
133 -- Check the columns related to media type.
134 --
135 -- NOTES
136 -- 1. For rollup campaigns, media information is optional;
137 -- for execution campaigns, media information is mandatory.
138 -- 2. Event_type can be defined only when the media type is events.
139 -- 3. Events associated to a campaign cannot be associated to other
140 -- campaigns.
141 -----------------------------------------------------------------------
142 PROCEDURE check_camp_media_type(
143 p_campaign_id IN NUMBER,
144 p_parent_id IN NUMBER,
145 p_rollup_type IN VARCHAR2,
146 p_media_type IN VARCHAR2,
147 p_media_id IN NUMBER,
148 p_channel_id IN NUMBER,
149 p_event_type IN VARCHAR2,
150 p_arc_channel_from IN VARCHAR2,
151 x_return_status OUT NOCOPY VARCHAR2
152 );
153
154
155 -----------------------------------------------------------------------
156 -- PROCEDURE
157 -- check_camp_fund_source
158 --
159 -- PURPOSE
160 -- Check campaign fund_source_type and fund_source_id.
161 --
162 -- NOTES
163 -- 1. fund_source_type could be 'FUND', 'CAMP', 'EVEH', 'EVEO'.
164 -- 2. fund_source_type can't be null if fund_source_id is provided.
165 -----------------------------------------------------------------------
166 PROCEDURE check_camp_fund_source(
167 p_fund_source_type IN VARCHAR2,
168 p_fund_source_id IN NUMBER,
169 x_return_status OUT NOCOPY VARCHAR2
170 );
171
172
173 -----------------------------------------------------------------------
174 -- PROCEDURE
175 -- check_camp_calendar
176 --
177 -- PURPOSE
178 -- Check campaign_calendar, start_period_name, end_period_name.
179 --
180 -- NOTES
181 -- 1. The start date of the start period should be no later than
182 -- the end date of the end period.
183 -----------------------------------------------------------------------
184 PROCEDURE check_camp_calendar(
185 p_campaign_calendar IN VARCHAR2,
186 p_start_period_name IN VARCHAR2,
187 p_end_period_name IN VARCHAR2,
188 p_start_date IN DATE,
189 p_end_date IN DATE,
190 x_return_status OUT NOCOPY VARCHAR2
191 );
192
193
194 -----------------------------------------------------------------------
195 -- PROCEDURE
196 -- check_camp_version
197 --
198 -- PURPOSE
199 -- Check the business rules related to campaign_version.
200 --
201 -- NOTES
202 -- 1. Pass p_campaign_id as NULL for creating.
203 -----------------------------------------------------------------------
204 PROCEDURE check_camp_version(
205 p_campaign_id IN NUMBER,
206 p_campaign_name IN VARCHAR2,
207 p_status_code IN VARCHAR2,
208 p_start_date IN DATE,
209 p_city_id IN NUMBER,
210 p_version_no IN NUMBER,
211 x_return_status OUT NOCOPY VARCHAR2
212 );
213
214 ---------------------------------------------------------------------
215 -- PROCEDURE
216 -- check_camp_status_vs_parent
217 --
218 -- PURPOSE
219 -- Check campaign status against its parent campaign.
220 --
221 -- NOTES
222 ---------------------------------------------------------------------
223 PROCEDURE check_camp_status_vs_parent(
224 p_parent_id IN NUMBER,
225 p_status_code IN VARCHAR2,
226 x_return_status OUT NOCOPY VARCHAR2
227 );
228
229 ---------------------------------------------------------------------
230 -- PROCEDURE
231 -- check_camp_dates_vs_parent
232 --
233 -- PURPOSE
234 -- Check campaign dates against its parent campaign.
235 --
236 -- NOTES
237 -- 1. The actual_exec_start_date and actual_exec_end_date will
238 -- be checked. Other dates exist only for upgrade purpose.
239 -- 2. It's part of the inter-record level validation for creating
240 -- and updating.
241 -- 3. For any error, write the error message to message list,
242 -- and continue to check others.
243 ---------------------------------------------------------------------
244 PROCEDURE check_camp_dates_vs_parent(
245 p_parent_id IN NUMBER,
246 p_rollup_type IN VARCHAR2,
247 p_start_date IN DATE,
248 p_end_date IN DATE,
249 x_return_status OUT NOCOPY VARCHAR2
250 );
251
252
253 ---------------------------------------------------------------------
254 -- PROCEDURE
255 -- check_camp_dates_vs_child
256 --
257 -- PURPOSE
258 -- Validate campaign dates against its sub-campaigns.
259 --
260 -- NOTES
261 -- 1. The actual_exec_start_date and actual_exec_end_date will
262 -- be checked. Other dates exist only for upgrade purpose.
263 -- 2. It's part of the inter-record level validation for updating.
264 -- 3. For any error, write the error message to message list,
265 -- and continue to check others.
266 ---------------------------------------------------------------------
267 PROCEDURE check_camp_dates_vs_child(
268 p_camp_id IN NUMBER,
269 p_start_date IN DATE,
270 p_end_date IN DATE,
271 x_return_status OUT NOCOPY VARCHAR2
272 );
273
274 --==============================================================================
275 -- PROCEDURE
276 -- Check_BU_Vs_Parent
277 --
278 -- PURPOSE
279 -- Check if the Business unit of campaign/program is same as that of parent
280 --
281 -- HISTORY
282 -- 23-May-2001 ptendulk Created.
283 --===============================================================================
284 PROCEDURE Check_BU_Vs_Parent(
285 p_program_id IN NUMBER,
286 p_business_unit_id IN NUMBER,
287 x_return_status OUT NOCOPY VARCHAR2
288 ) ;
289
290 --=====================================================================
291 -- PROCEDURE
292 -- Check_BU_Vs_Child
293 --
294 -- PURPOSE
295 -- Check if the Business unit of children is same as that of parent
296 --
297 -- HISTORY
298 -- 23-May-2001 ptendulk Created.
299 --=====================================================================
300 PROCEDURE Check_BU_Vs_Child(
301 p_camp_id IN NUMBER,
302 p_business_unit_id IN NUMBER,
303 x_return_status OUT NOCOPY VARCHAR2
304 );
305 ---------------------------------------------------------------------
306 -- PROCEDURE
307 -- check_camp_dates_vs_csch
308 --
309 -- PURPOSE
310 -- Check campaign dates against campaign schedule dates.
311 --
312 -- NOTES
313 -- 1. The actual_exec_start_date and actual_exec_end_date will
314 -- be checked. Other dates exist only for upgrade purpose.
315 -- 2. It's part of the inter-record level validation for updating.
316 -- 3. For any error, write the error message to message list,
317 -- and continue to check others.
318 ---------------------------------------------------------------------
319 PROCEDURE check_camp_dates_vs_csch(
320 p_camp_id IN NUMBER,
321 p_start_date IN DATE,
322 p_end_date IN DATE,
323 x_return_status OUT NOCOPY VARCHAR2
324 );
325
326
327 ---------------------------------------------------------------------
328 -- PROCEDURE
329 -- handle_csch_source_code
330 --
331 -- PURPOSE
332 -- Check if the schedule source code is valid.
333 --
334 -- NOTES
335 -- 1. If the parent campaign has its cascade_source_code_flag set
336 -- to 'Y', then schedules should use the campaign source code.
337 -- Otherwise schedules must have their own unique source codes.
338 ---------------------------------------------------------------------
339 PROCEDURE handle_csch_source_code(
340 p_source_code IN VARCHAR2,
341 p_camp_id IN NUMBER,
342 x_cascade_flag OUT NOCOPY VARCHAR2,
343 x_source_code OUT NOCOPY VARCHAR2,
344 x_return_status OUT NOCOPY VARCHAR2
345 );
346
347 -- 10/02/2002
348 -- Commented this proc because this method is not being used any where and it refers to
349 -- old AMS_CampaignSchedule_PVT which is no more there. Please refer Bug# 2605184
350 ---------------------------------------------------------------------
351 -- PROCEDURE
352 -- check_csch_update
353 --
354 -- PURPOSE
355 -- Check if schedule record can be updated.
356 --
357 -- NOTES
358 -- 1. Source code can't be updated.
359 ---------------------------------------------------------------------
360 -- PROCEDURE check_csch_update(
361 -- p_csch_rec IN AMS_CampaignSchedule_PVT.csch_rec_type,
362 -- x_return_status OUT VARCHAR2
363 -- );
364
365
366 ---------------------------------------------------------------------
367 -- PROCEDURE
368 -- check_csch_camp_id
369 --
370 -- PURPOSE
371 -- Check if the schedule can be attached to a campaign.
372 --
373 -- NOTES
374 -- 1. Schedules can be attached to execution campaigns only.
375 -- 2. If the campaign media type is 'EVENTS', schedules can not
376 -- be attached.
377 ---------------------------------------------------------------------
378 PROCEDURE check_csch_camp_id(
379 p_camp_id IN NUMBER,
380 x_return_status OUT NOCOPY VARCHAR2
381 );
382
383
384 ---------------------------------------------------------------------
385 -- PROCEDURE
386 -- check_csch_deliv_id
387 --
388 -- PURPOSE
389 -- Check the deliverable_id column in ams_campaign_schedules.
390 --
391 -- NOTES
392 -- 1. The deliverable used by a campaign schedule has to be one
393 -- of the deliverables associated to its parent campaign.
394 ---------------------------------------------------------------------
395 PROCEDURE check_csch_deliv_id(
396 p_deliv_id IN NUMBER,
397 p_camp_id IN NUMBER,
398 x_return_status OUT NOCOPY VARCHAR2
399 );
400
401
402 ---------------------------------------------------------------------
403 -- PROCEDURE
404 -- check_csch_offer_id
405 --
406 -- PURPOSE
407 -- Check the activity_offer_id column in ams_campaign_schedules.
408 --
409 -- NOTES
410 -- 1. Schedule can promote an offer from the list of the offers
411 -- for the campaign found in ams_act_offers table.
412 ---------------------------------------------------------------------
413 PROCEDURE check_csch_offer_id(
414 p_offer_id IN NUMBER,
415 p_camp_id IN NUMBER,
416 x_return_status OUT NOCOPY VARCHAR2
417 );
418
419
420 ---------------------------------------------------------------------
421 -- PROCEDURE
422 -- check_csch_dates_vs_camp
423 --
424 -- PURPOSE
425 -- Check campaign schedule dates against campaign dates.
426 --
427 -- NOTES
431 -- and updating schedules.
428 -- 1. The actual_start_date_time and actual_end_date_time will
429 -- be checked. Others exist only for upgrade purpose.
430 -- 2. It's part of the inter-record level validation for creating
432 -- 3. For any error, write the error message to message list,
433 -- and continue to check others.
434 ---------------------------------------------------------------------
435 PROCEDURE check_csch_dates_vs_camp(
436 p_camp_id IN NUMBER,
437 p_start_date IN DATE,
438 p_end_date IN DATE,
439 x_return_status OUT NOCOPY VARCHAR2
440 );
441
442
443 ---------------------------------------------------------------------
444 -- PROCEDURE
445 -- activate_campaign
446 --
447 -- PURPOSE
448 -- Perform the following tasks when campaigns become active:
449 -- 1. Change the show_campaign_flag of all other versions to 'N'.
450 ---------------------------------------------------------------------
451 PROCEDURE activate_campaign(
452 p_campaign_id IN NUMBER
453 );
454
455
456 -----------------------------------------------------------------------
457 -- PROCEDURE
458 -- udpate_camp_status
459 --
460 -- PURPOSE
461 -- Update campaign status through workflow.
462 -----------------------------------------------------------------------
463 PROCEDURE update_camp_status(
464 p_campaign_id IN NUMBER,
465 p_user_status_id IN NUMBER,
466 p_budget_amount IN NUMBER,
467 p_parent_id IN NUMBER
468 );
469
470
471 ---------------------------------------------------------------------
472 -- PROCEDURE
473 -- push_source_code
474 --
475 -- PURPOSE
476 -- After creating campaigns or schedules, push the source code
477 -- into ams_source_codes table.
478 ---------------------------------------------------------------------
479 PROCEDURE push_source_code(
480 p_source_code IN VARCHAR2,
481 p_arc_object IN VARCHAR2,
482 p_object_id IN NUMBER,
483 p_related_source_code IN VARCHAR2 := NULL,
484 p_related_source_object IN VARCHAR2 := NULL,
485 p_related_source_id IN NUMBER := NULL
486 );
487
488
489 -----------------------------------------------------------------------
490 -- FUNCTION
491 -- get_parent_media_type
492 --
493 -- PURPOSE
494 -- Used to enforce that all children under a rollup campaign share
495 -- the same media type if it is specified for the rollup.
496 -----------------------------------------------------------------------
497 FUNCTION get_parent_media_type(
498 p_parent_id IN NUMBER
499 )
500 RETURN VARCHAR2;
501
502
503 -----------------------------------------------------------------------
504 -- FUNCTION
505 -- check_camp_parent
506 --
507 -- PURPOSE
508 -- Check if a campaign can be the parent of another campaign.
509 -----------------------------------------------------------------------
510 FUNCTION check_camp_parent(
511 p_camp_id IN NUMBER,
512 p_parent_id IN NUMBER
513 )
514 RETURN VARCHAR2;
515
516
517 -----------------------------------------------------------------------
518 -- FUNCTION
519 -- check_camp_attribute
520 --
521 -- PURPOSE
522 -- Check if an attribute can be attached to a campaign.
523 --
524 -- NOTES
525 -- 1. The valid values for p_attribute are: 'ACCESS', 'ATTACHMENT',
526 -- 'METRIC', 'OFFER', 'PRODUCT', 'SCHEDULE', 'TRIGGER'.
527 -- 2. Raise FND_API.g_exc_error if p_camp_id is invalid.
528 -- 3. Rollup campaigns won't have any attributes except
529 -- access and metric.
530 -- 4. Execution campaigns under a multi-channel campaign won't
531 -- have any attributes.
532 -- 5. Only direct marketing campaigns can have triggers.
533 -- 6. If the media type is 'EVENTS', no schedule can be attached.
534 -----------------------------------------------------------------------
535 FUNCTION check_camp_attribute(
536 p_camp_id IN NUMBER,
537 p_attribute IN VARCHAR2
538 )
539 RETURN VARCHAR2;
540
541 --=======================================================================
542 -- PROCEDURE
543 -- Convert_Camp_Currency
544 -- NOTES
545 -- This procedure is created to convert the transaction currency into
546 -- functional currency.
547 -- HISTORY
548 -- 09/27/2000 PTENDULK Created.
549 --=======================================================================
550 PROCEDURE Convert_Camp_Currency(
551 p_tc_curr IN VARCHAR2,
552 p_tc_amt IN NUMBER,
553 x_fc_curr OUT NOCOPY VARCHAR2,
554 x_fc_amt OUT NOCOPY NUMBER
555 ) ;
556
557 --=======================================================================
558 -- PROCEDURE
559 -- Get_Camp_Child_Count
560 -- NOTES
561 -- This function is created to return the child count given a campaign
562 -- id . It is used to tune Campaign Hierarchy tree.
563 --
564 -- HISTORY
565 -- 04-Feb-2001 PTENDULK Created.
566 --=======================================================================
567 FUNCTION Get_Camp_Child_Count( p_campaign_id IN VARCHAR2 )
568 RETURN NUMBER ;
572 -- Check_Prog_Dates_Vs_Eveh
569
570 --=====================================================================
571 -- PROCEDURE
573 --
574 -- PURPOSE
575 -- The api is created to check the dates of program vs dates of
576 -- events. Events dates has to be between program dates.
577 --
578 -- HISTORY
579 -- 07-Feb-2001 ptendulk Created.
580 --=====================================================================
581 PROCEDURE Check_Prog_Dates_Vs_Eveh(
582 p_camp_id IN NUMBER,
583 p_start_date IN DATE,
584 p_end_date IN DATE,
585 x_return_status OUT NOCOPY VARCHAR2
586 ) ;
587
588 --=====================================================================
589 -- PROCEDURE
590 -- Update_Owner
591 --
592 -- PURPOSE
593 -- The api is created to update the owner of the campaign from the
594 -- access table if the owner is changed in update.
595 --
596 -- HISTORY
597 -- 04-Mar-2001 ptendulk Created.
598 --=====================================================================
599 PROCEDURE Update_Owner(
600 p_api_version IN NUMBER,
601 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
602 p_commit IN VARCHAR2 := FND_API.g_false,
603 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
604 x_return_status OUT NOCOPY VARCHAR2,
605 x_msg_count OUT NOCOPY NUMBER,
606 x_msg_data OUT NOCOPY VARCHAR2,
607 p_object_type IN VARCHAR2 := NULL ,
608 p_campaign_id IN NUMBER,
609 p_owner_id IN NUMBER );
610
611
612 -----------------------------------------------------------------------
613 -- PROCEDURE
614 -- validate_realted_event
615 --
616 -- PURPOSE
617 -- Validate the realted event. Check the foreign key against the
618 -- event tables depending on the event_type passed
619 --
620 -- NOTES
621 -- HISTORY
622 -- 12-Apr-2001 rrajesh Created.
623 -----------------------------------------------------------------------
624 PROCEDURE validate_realted_event(
625 p_related_event_id IN NUMBER,
626 p_related_event_type IN VARCHAR2,
627 x_return_status OUT NOCOPY VARCHAR2
628 );
629
630 --=====================================================================
631 -- PROCEDURE
632 -- Update_Rollup
633 --
634 -- PURPOSE
635 -- The api is created to update the rollup for the metrics if the
636 -- parent of the campaign is changed
637 --
638 -- HISTORY
639 -- 31-May-2001 ptendulk Created.
640 --=====================================================================
641 PROCEDURE Update_Rollup(
642 p_api_version IN NUMBER,
643 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
644 p_commit IN VARCHAR2 := FND_API.g_false,
645 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
646 x_return_status OUT NOCOPY VARCHAR2,
647 x_msg_count OUT NOCOPY NUMBER,
648 x_msg_data OUT NOCOPY VARCHAR2,
649 p_campaign_id IN NUMBER,
650 p_parent_id IN NUMBER ) ;
651
652
653 --========================================================================
654 -- PROCEDURE
655 -- update_status
656 --
657 -- NOTE
658 --
659 -- HISTORY
660 -- 02-oct-2001 soagrawa Created.
661 --
662 --========================================================================
663
664 PROCEDURE update_status( p_campaign_id IN NUMBER,
665 p_new_status_id IN NUMBER,
666 p_new_status_code IN VARCHAR2
667 );
668
669 --========================================================================
670 -- Function
671 -- Get_Event_Source_Code
672 --
673 -- PURPOSE
674 -- Get the source code for the related event associated to the campaign.
675 --
676 -- NOTES
677 -- HISTORY
678 -- 22-May-2001 ptendulk Created.
679 -- 08-Oct-2001 ptendulk Modified cursor queries for event offers and one off.
680 --========================================================================
681
682 FUNCTION Get_Event_Source_Code(
683 p_event_type VARCHAR2,
684 p_event_id NUMBER
685 ) RETURN VARCHAR2 ;
686
687 --========================================================================
688 -- PROCEDURE
689 -- Check_Children_Tree
690 --
691 -- PURPOSE
692 -- This api is to check if the hierarchy for the parent child camp is
693 -- valid. It validates that parent campaign is not one of the
694 -- childrens of the campaign.
695 --
696 -- NOTE
697 --
698 -- HISTORY
699 -- 25-Oct-2001 ptendulk Created.
700 --
701 --========================================================================
702 PROCEDURE Check_Children_Tree(p_campaign_id IN NUMBER,
703 p_parent_campaign_id IN NUMBER
704 ) ;
705 END AMS_CampaignRules_PVT;
[Home] [Help]