1 PACKAGE amv_match_pvt AS
2 /* $Header: amvvmats.pls 120.1 2005/06/30 12:49:54 appldev ship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMV_MATCH_PVT
7 --
8 -- PURPOSE
9 -- This package is the (content) item-channel match engine api.
10 -- It will provide procedure for the matching engine and other api to call.
11 -- It will use package amv_aq_utility_pvt for handling AQ queue details.
12 --
13 -- PROCEDURES:
14 --
15 -- Stop_MatchingEngine;
16 -- Start_MatchingEngine;
17 -- Request_ItemMatch;
18 -- Request_ChannelMatch;
19 -- Match_ItemWithChannels;
20 -- Match_ChannelWithItems;
21 -- Check_ExistItemChlMatch;
22 -- Check_MatchingCondition;
23 -- Do_ItemChannelMatch;
24 -- Remove_ItemChannelMatch;
25 --
26 -- NOTES
27 --
28 --
29 -- HISTORY
30 -- 09/30/1999 PWU created
31 --
32 -- 06/23/2000 SHITIJ VATSA updated
33 -- (svatsa) Changed the spec of the following APIs to
34 -- incorporate the territory intg logic :
35 -- 1) Do_ItemChannelMatch(Overloaded) - Added a territory_tbl_type param
36 -- 2) Remove_ItemChannelMatch - Added a territory_id param
37 -- Also created a table variable terriotry_tbl_type
38 -- Added two new APIs
39 -- 1. Get_UserTerritory
40 -- 2. Get_PublishedTerritories
41 --
42 -- End of Comments
43 --
44 --
45 -- The following constants are to be finalized.
46 --
47 G_VERSION CONSTANT NUMBER := 1.0;
48 G_MATCH_ITEM_TABLE CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_TABLE_NAME_CODE;
49 G_AMV_APP_ID CONSTANT NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID;
50 --
51 -- Record and Table Type variable for Do_ItemChannelMatch
52
53
54 TYPE terr_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
55 TYPE terr_name_tbl_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
56 --
57 -- This package contains the following procedure
58 --
59 --------------------------------------------------------------------------------
60 -- Start of comments
61 -- API name : Request_ItemMatch
62 -- Type : Private
63 -- Pre-reqs : None
64 -- Function : Make a request to matching engine,
65 -- to match channels with the passed item.
66 -- Parameters :
67 -- IN p_api_version NUMBER Required
68 -- p_init_msg_list VARCHAR2 Optional
69 -- Default = FND_API.G_FALSE
70 -- p_commit VARCHAR2 Optional
71 -- Default = FND_API.G_FALSE
72 -- p_validation_level NUMBER Optional
73 -- Default = FND_API.G_VALID_LEVEL_FULL
74 -- p_check_login_user VARCHAR2 Optional
75 -- Default = FND_API.G_TRUE
76 -- Flag for checking user privilege and if user is login.
77 -- p_item_id NUMBER Required
78 -- the id of the item to be matched with channels.
79 -- OUT : x_return_status VARCHAR2
80 -- x_msg_count NUMBER
81 -- x_msg_data VARCHAR2
82 -- Notes :
83 --
84 -- End of comments
85 --
86 PROCEDURE Request_ItemMatch
87 (
88 p_api_version IN NUMBER,
89 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
90 p_commit IN VARCHAR2 := FND_API.G_FALSE,
91 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_msg_count OUT NOCOPY NUMBER,
94 x_msg_data OUT NOCOPY VARCHAR2,
95 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
96 p_item_id IN NUMBER
97 );
98 --
99 -- Algorithm:
100 -- This is simply a wrapper to AMV_AQ_UTILITY_PVT.Enqueue_Message.
101 --
102 --------------------------------------------------------------------------------
103 -- Start of comments
104 -- API name : Request_ChannelMatch
105 -- Type : Private
106 -- Pre-reqs : None
107 -- Function : Make a request to matching engine:
108 -- to match items with the passed channel.
109 -- Parameters :
110 -- IN p_api_version NUMBER Required
111 -- p_init_msg_list VARCHAR2 Optional
112 -- Default = FND_API.G_FALSE
113 -- p_commit VARCHAR2 Optional
114 -- Default = FND_API.G_FALSE
115 -- p_validation_level NUMBER Optional
116 -- Default = FND_API.G_VALID_LEVEL_FULL
117 -- p_check_login_user VARCHAR2 Optional
118 -- Default = FND_API.G_TRUE
119 -- Flag for checking user privilege and if user is login.
120 -- p_channel_id NUMBER Required
121 -- the id of the channel to be matched with items.
122 -- OUT : x_return_status VARCHAR2
123 -- x_msg_count NUMBER
124 -- x_msg_data VARCHAR2
125 -- Notes :
126 --
127 -- End of comments
128 --
129 PROCEDURE Request_ChannelMatch
130 (
131 p_api_version IN NUMBER,
132 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
133 p_commit IN VARCHAR2 := FND_API.G_FALSE,
134 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2,
138 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
139 p_channel_id IN NUMBER
140 );
141 --
142 -- Algorithm:
143 -- This is simply a wrapper to AMV_AQ_UTILITY_PVT.Enqueue_Message.
144 --
145 --
146 --------------------------------------------------------------------------------
147 -- Start of comments
148 -- API name : Stop_MatchingEngine
149 -- Type : Private
150 -- Pre-reqs : None
151 -- Function : Stop the matching engine.
152 -- matching engine normally runs all the time. This procedure
153 -- provide a way to stop the engine.
154 -- Parameters :
155 -- IN p_api_version NUMBER Required
156 -- p_init_msg_list VARCHAR2 Optional
157 -- Default = FND_API.G_FALSE
158 -- p_commit VARCHAR2 Optional
159 -- Default = FND_API.G_FALSE
160 -- p_validation_level NUMBER Optional
161 -- Default = FND_API.G_VALID_LEVEL_FULL
162 -- p_check_login_user VARCHAR2 Optional
163 -- Default = FND_API.G_TRUE
164 -- Flag for checking user privilege and if user is login.
165 -- OUT : x_return_status VARCHAR2
166 -- x_msg_count NUMBER
167 -- x_msg_data VARCHAR2
168 -- Notes :
169 --
170 -- End of comments
171 --
172 PROCEDURE Stop_MatchingEngine
173 (
174 p_api_version IN NUMBER,
175 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
176 p_commit IN VARCHAR2 := FND_API.G_FALSE,
177 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
178 x_return_status OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER,
180 x_msg_data OUT NOCOPY VARCHAR2,
181 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE
182 );
183 --
184 --------------------------------------------------------------------------------
185 -- Start of comments
186 -- API name : Start_MatchingEngine
187 -- Type : Private
188 -- Pre-reqs : None
189 -- Function : Start the matching engine and process any request in queue.
190 -- This process cannot guarantees that only one engine instance
191 -- is running.
192 -- Parameters :
193 -- IN p_api_version NUMBER Required
194 -- OUT : x_return_status VARCHAR2
195 -- x_msg_count NUMBER
196 -- x_msg_data VARCHAR2
197 -- Notes :
198 --
199 -- End of comments
200 --
201 PROCEDURE Start_MatchingEngine
202 (
203 errbuf OUT NOCOPY VARCHAR2,
204 retcode OUT NOCOPY NUMBER
205 );
206 --
207 --------------------------------------------------------------------------------
208 -- Start of comments
209 -- API name : Match_ItemWithChannels
210 -- Type : Private
211 -- Pre-reqs : None
212 -- Function : For the passed particular (content) item,
213 -- Search for all the matching channels.
214 -- and do the matching, process approval.
215 -- Parameters :
216 -- IN p_api_version NUMBER Required
217 -- p_init_msg_list VARCHAR2 Optional
218 -- Default = FND_API.G_FALSE
219 -- p_commit VARCHAR2 Optional
220 -- Default = FND_API.G_FALSE
221 -- p_validation_level NUMBER Optional
222 -- Default = FND_API.G_VALID_LEVEL_FULL
223 -- p_check_login_user VARCHAR2 Optional
224 -- Default = FND_API.G_TRUE
225 -- Flag for checking user privilege and if user is login.
226 -- p_item_id NUMBER Required
227 -- the id of the item to be matched with channels.
228 -- OUT : x_return_status VARCHAR2
229 -- x_msg_count NUMBER
230 -- x_msg_data VARCHAR2
231 -- Notes :
232 --
233 -- End of comments
234 --
235 PROCEDURE Match_ItemWithChannels
236 (
237 p_api_version IN NUMBER,
238 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
239 p_commit IN VARCHAR2 := FND_API.G_FALSE,
240 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
241 x_return_status OUT NOCOPY VARCHAR2,
242 x_msg_count OUT NOCOPY NUMBER,
243 x_msg_data OUT NOCOPY VARCHAR2,
244 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
245 p_item_id IN NUMBER
246 );
247 --
248 -- Algorithm:
249 -- BEGIN
250 -- Verify API version compatibility
251 -- Check that login user's status is active
252 -- IF user not active THEN
253 -- Return error 'user account currently suspended'
254 -- ENDIF
255 -- Set rollback SAVEPOINT
256 -- ...
257 -- Commit transaction if requested
258 -- ENDIF
259 -- END
260 --
261 --------------------------------------------------------------------------------
262 -- Start of comments
263 -- API name : Match_ChannelWithItems
264 -- Type : Private
265 -- Pre-reqs : None
266 -- Function : For the passed particular channel,
267 -- Search for all the matching items.
268 -- and do the matching, process approval....
269 -- Parameters :
270 -- IN p_api_version NUMBER Required
271 -- p_init_msg_list VARCHAR2 Optional
272 -- Default = FND_API.G_FALSE
273 -- p_commit VARCHAR2 Optional
274 -- Default = FND_API.G_FALSE
275 -- p_validation_level NUMBER Optional
276 -- Default = FND_API.G_VALID_LEVEL_FULL
277 -- p_check_login_user VARCHAR2 Optional
278 -- Default = FND_API.G_TRUE
279 -- Flag for checking user privilege and if user is login.
280 -- p_channel_id NUMBER Required
281 -- the id of the channel to be matched with items.
282 -- OUT : x_return_status VARCHAR2
283 -- x_msg_count NUMBER
284 -- x_msg_data VARCHAR2
285 -- Notes :
286 --
287 -- End of comments
288 --
289 PROCEDURE Match_ChannelWithItems
290 (
291 p_api_version IN NUMBER,
292 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
293 p_commit IN VARCHAR2 := FND_API.G_FALSE,
294 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_count OUT NOCOPY NUMBER,
297 x_msg_data OUT NOCOPY VARCHAR2,
298 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
299 p_channel_id IN NUMBER
300 );
301 --
302 -- Algorithm:
303 -- BEGIN
304 -- Verify API version compatibility
305 -- Check that login user's status is active
306 -- IF user not active THEN
307 -- Return error 'user account currently suspended'
308 -- ENDIF
309 -- Set rollback SAVEPOINT
310 -- ...
311 -- Commit transaction if requested
312 -- ENDIF
313 -- END
314 --
315 --------------------------------------------------------------------------------
316 -- Start of comments
317 -- API name : Check_ExistItemChlMatch
318 -- Type : Private
319 -- Pre-reqs : None
320 -- Function : Check if the passed item and the passed channel
321 -- has the match. If so, also return the approval status.
322 -- Parameters :
323 -- IN p_api_version NUMBER Required
324 -- p_init_msg_list VARCHAR2 Optional
325 -- Default = FND_API.G_FALSE
326 -- p_validation_level NUMBER Optional
327 -- Default = FND_API.G_VALID_LEVEL_FULL
328 -- p_check_login_user VARCHAR2 Optional
329 -- Default = FND_API.G_TRUE
330 -- Flag for checking user privilege and if user is login.
331 -- p_channel_id NUMBER Required
332 -- the id of the channel to be matched.
333 -- p_item_id NUMBER Required
334 -- the id of the item to be matched.
335 -- OUT : x_return_status VARCHAR2
336 -- x_msg_count NUMBER
337 -- x_msg_data VARCHAR2
338 -- x_match_exist_flag VARCHAR2
339 -- the flag of existence of the match.
340 -- return FND_API.G_TRUE if exists,
341 -- Otherwise return FND_API.G_FALSE.
342 -- x_approval_status VARCHAR2
343 -- return the approval status if the match exists.
344 -- Notes :
345 --
346 -- End of comments
347 --
348 PROCEDURE Check_ExistItemChlMatch
349 (
350 p_api_version IN NUMBER,
351 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
352 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2,
356 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
357 p_channel_id IN NUMBER,
358 p_item_id IN NUMBER,
359 x_match_exist_flag OUT NOCOPY VARCHAR2,
360 x_approval_status OUT NOCOPY VARCHAR2
361 );
362 --
366 -- Check that login user's status is active
363 -- Algorithm:
364 -- BEGIN
365 -- Verify API version compatibility
367 -- IF user not active THEN
368 -- Return error 'user account currently suspended'
369 -- ENDIF
370 --
371 -- ENDIF
372 -- END
373 --------------------------------------------------------------------------------
374 -- Start of comments
375 -- API name : Check_MatchingCondition
376 -- Type : Private
377 -- Pre-reqs : None
378 -- Function : Apply matching rule to the passed item and passed channel.
379 -- This will completely check if the channel match the item.
380 -- Parameters :
381 -- IN p_api_version NUMBER Required
382 -- p_init_msg_list VARCHAR2 Optional
383 -- Default = FND_API.G_FALSE
384 -- p_validation_level NUMBER Optional
385 -- Default = FND_API.G_VALID_LEVEL_FULL
386 -- p_check_login_user VARCHAR2 Optional
387 -- Default = FND_API.G_TRUE
388 -- Flag for checking user privilege and if user is login.
389 -- p_item_id NUMBER Required
390 -- the id of the item to be matched.
391 -- p_channel_id NUMBER Required
392 -- the id of the channel to be matched.
393 -- p_match_on_author_flag VARCHAR2 Required
394 -- the (pre-query) match_on_author_flag of the channel.
395 -- p_match_on_keyword_flag VARCHAR2 Required
396 -- the (pre-query) match_on_keyword_flag of the channel.
397 -- p_match_on_perspective_flag VARCHAR2 Required
398 -- the (pre-query) match_on_perspective_flag of the channel.
399 -- p_match_on_content_type_flag VARCHAR2 Required
400 -- the (prequery) p_match_on_content_type_flag of the channel.
401 -- match_on_item_type_flag VARCHAR2 Required
402 -- the (pre-query) p_match_on_item_type_flag of the channel.
403 -- OUT : x_return_status VARCHAR2
404 -- x_msg_count NUMBER
405 -- x_msg_data VARCHAR2
406 -- x_match_flag VARCHAR2
407 -- The matching result: return FND_API.G_TRUE if matched
408 -- return FND_API.G_FALSE otherwise
409 -- Notes : Compare procedure Check_MatchingCondition
410 -- with Check_MatchingCondition2.
411 -- Check_MatchingCondition: Use variable static sql statments.
412 -- Check_MatchingCondition: Use native dynamic sql statments.
413 -- We will test and see which performs better.
414 --
415 --
416 -- End of comments
417 --
418 PROCEDURE Check_MatchingCondition
419 (
420 p_api_version IN NUMBER,
421 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
422 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
423 x_return_status OUT NOCOPY VARCHAR2,
424 x_msg_count OUT NOCOPY NUMBER,
425 x_msg_data OUT NOCOPY VARCHAR2,
426 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
427 p_item_id IN NUMBER,
428 p_channel_id IN NUMBER,
429 p_match_on_author_flag IN VARCHAR2,
430 p_match_on_keyword_flag IN VARCHAR2,
431 p_match_on_perspective_flag IN VARCHAR2,
432 p_match_on_content_type_flag IN VARCHAR2,
433 p_match_on_item_type_flag IN VARCHAR2,
434 x_match_flag OUT NOCOPY VARCHAR2
435 );
436 --
437 -- Algorithm:
438 -- BEGIN
439 -- Verify API version compatibility
440 -- Check that login user's status is active
441 -- IF user not active THEN
442 -- Return error 'user account currently suspended'
443 -- ENDIF
444 --
445 -- ENDIF
446 -- END
447 --
448 --------------------------------------------------------------------------------
449 -- Start of comments
450 -- API name : Check_MatchingCondition2
451 -- Type : Private
452 -- Pre-reqs : None
453 -- Function : Apply matching rule to the passed item and passed channel.
454 -- This will completely check if the channel match the item.
455 -- Parameters :
456 -- IN p_api_version NUMBER Required
457 -- p_init_msg_list VARCHAR2 Optional
458 -- Default = FND_API.G_FALSE
459 -- p_validation_level NUMBER Optional
460 -- Default = FND_API.G_VALID_LEVEL_FULL
461 -- p_check_login_user VARCHAR2 Optional
462 -- Default = FND_API.G_TRUE
463 -- Flag for checking user privilege and if user is login.
464 -- p_item_id NUMBER Required
465 -- the id of the item to be matched.
466 -- p_channel_id NUMBER Required
467 -- the id of the channel to be matched.
468 -- p_match_on_author_flag VARCHAR2 Required
472 -- p_match_on_perspective_flag VARCHAR2 Required
469 -- the (pre-query) match_on_author_flag of the channel.
470 -- p_match_on_keyword_flag VARCHAR2 Required
471 -- the (pre-query) match_on_keyword_flag of the channel.
473 -- the (pre-query) match_on_perspective_flag of the channel.
474 -- p_match_on_content_type_flag VARCHAR2 Required
475 -- the (prequery) p_match_on_content_type_flag of the channel.
476 -- match_on_item_type_flag VARCHAR2 Required
477 -- the (pre-query) p_match_on_item_type_flag of the channel.
478 -- OUT : x_return_status VARCHAR2
479 -- x_msg_count NUMBER
480 -- x_msg_data VARCHAR2
481 -- x_match_flag VARCHAR2
482 -- The matching result: return FND_API.G_TRUE if matched
483 -- return FND_API.G_FALSE otherwise
484 -- Notes : Compare procedure Check_MatchingCondition
485 -- with Check_MatchingCondition2.
486 -- Check_MatchingCondition: Use variable static sql statments.
487 -- Check_MatchingCondition: Use native dynamic sql statments.
488 -- We will test and see which performs better.
489 --
490 --
491 -- End of comments
492 --
493 PROCEDURE Check_MatchingCondition2
494 (
495 p_api_version IN NUMBER,
496 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
497 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
498 x_return_status OUT NOCOPY VARCHAR2,
499 x_msg_count OUT NOCOPY NUMBER,
500 x_msg_data OUT NOCOPY VARCHAR2,
501 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
502 p_item_id IN NUMBER,
503 p_channel_id IN NUMBER,
504 p_match_on_author_flag IN VARCHAR2,
505 p_match_on_keyword_flag IN VARCHAR2,
506 p_match_on_perspective_flag IN VARCHAR2,
507 p_match_on_content_type_flag IN VARCHAR2,
508 p_match_on_item_type_flag IN VARCHAR2,
509 x_match_flag OUT NOCOPY VARCHAR2
510 );
511 --
512 -- Algorithm:
513 -- BEGIN
514 -- Verify API version compatibility
515 -- Check that login user's status is active
516 -- IF user not active THEN
517 -- Return error 'user account currently suspended'
518 -- ENDIF
519 --
520 -- ENDIF
521 -- END
522 --
523 --------------------------------------------------------------------------------
524 -- Start of comments
525 -- API name : Do_ItemChannelMatch
526 -- Type : Private
527 -- Pre-reqs : None
528 -- Function : do match the passed item with the passed channel.
529 -- Check the reqirement of the channel.
530 -- Call approval process procedure if needed.
531 -- Parameters :
532 -- IN p_api_version NUMBER Required
533 -- p_init_msg_list VARCHAR2 Optional
534 -- Default = FND_API.G_FALSE
535 -- p_commit VARCHAR2 Optional
536 -- Default = FND_API.G_FALSE
537 -- p_validation_level NUMBER Optional
538 -- Default = FND_API.G_VALID_LEVEL_FULL
539 -- p_check_login_user VARCHAR2 Optional
540 -- Default = FND_API.G_TRUE
541 -- Flag for checking user privilege and if user is login.
542 -- p_category_id NUMBER Optional
543 -- Default = FND_API.G_MISS_NUM
544 -- p_channel_id NUMBER Optional
545 -- Default = FND_API.G_MISS_NUM
546 -- the id of the channel to be matched. If missing,
547 -- the item is pushed to the category identified by
548 -- p_category_id.
549 -- p_item_id NUMBER Required
550 -- the id of the item to be matched.
551 -- p_match_type VARCHAR2 Required
552 -- matching type: by force or by rule.
553 -- OUT : x_return_status VARCHAR2
554 -- x_msg_count NUMBER
555 -- x_msg_data VARCHAR2
556 -- Notes : Assume caller has check the validation of item id
557 -- and channel id. And the item has NOT yet been match
558 -- with the channel.
559 --
560 -- End of comments
561 --
562 PROCEDURE Do_ItemChannelMatch
563 (
564 p_api_version IN NUMBER,
565 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
566 p_commit IN VARCHAR2 := FND_API.G_FALSE,
567 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
568 x_return_status OUT NOCOPY VARCHAR2,
569 x_msg_count OUT NOCOPY NUMBER,
570 x_msg_data OUT NOCOPY VARCHAR2,
571 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
572 p_application_id IN NUMBER := G_AMV_APP_ID,
573 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
577 p_match_type IN VARCHAR2
574 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
575 p_item_id IN NUMBER,
576 p_table_name_code IN VARCHAR2,
578 );
579 --
580 -- Algorithm:
581 -- BEGIN
582 -- Verify API version compatibility
583 -- Check that login user's status is active
584 -- IF user not active THEN
585 -- Return error 'user account currently suspended'
586 -- ENDIF
587 -- Set rollback SAVEPOINT
588 --
589 -- Commit transaction if requested
590 -- ENDIF
591 -- END
592 --
593 --------------------------------------------------------------------------------
594 -- Start of comments
595 -- API name : Do_ItemChannelMatch (Overloaded)
596 -- Type : Private
597 -- Pre-reqs : None
598 -- Function : do match the passed item with the passed channel.
599 -- Check the reqirement of the channel.
600 -- Call approval process procedure if needed.
601 -- Parameters :
602 -- IN p_api_version NUMBER Required
603 -- p_init_msg_list VARCHAR2 Optional
604 -- Default = FND_API.G_FALSE
605 -- p_commit VARCHAR2 Optional
606 -- Default = FND_API.G_FALSE
607 -- p_validation_level NUMBER Optional
608 -- Default = FND_API.G_VALID_LEVEL_FULL
609 -- p_check_login_user VARCHAR2 Optional
610 -- Default = FND_API.G_TRUE
611 -- Flag for checking user privilege and if user is login.
612 -- p_category_id NUMBER Optional
613 -- Default = FND_API.G_MISS_NUM
614 -- p_channel_id NUMBER Optional
615 -- Default = FND_API.G_MISS_NUM
616 -- the id of the channel to be matched. If missing,
617 -- the item is pushed to the category identified by
618 -- p_category_id.
619 -- p_item_id NUMBER Required
620 -- the id of the item to be matched.
621 -- p_match_type VARCHAR2 Required
622 -- matching type: by force or by rule.
623 -- p_territory_tbl territory_tbl_type Optional
624 -- territory to which the item is published.
625 -- OUT : x_return_status VARCHAR2
626 -- x_msg_count NUMBER
627 -- x_msg_data VARCHAR2
628 -- Notes : Assume caller has check the validation of item id
629 -- and channel id. And the item has NOT yet been match
630 -- with the channel.
631 --
632 -- End of comments
633 --
634 PROCEDURE Do_ItemChannelMatch
635 (
636 p_api_version IN NUMBER,
637 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
638 p_commit IN VARCHAR2 := FND_API.G_FALSE,
639 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
640 x_return_status OUT NOCOPY VARCHAR2,
641 x_msg_count OUT NOCOPY NUMBER,
642 x_msg_data OUT NOCOPY VARCHAR2,
643 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
644 p_application_id IN NUMBER := G_AMV_APP_ID,
645 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
646 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
647 p_item_id IN NUMBER,
648 p_table_name_code IN VARCHAR2,
649 p_match_type IN VARCHAR2,
650 p_territory_tbl IN terr_id_tbl_type
651 );
652 --
653 -- Algorithm:
654 -- BEGIN
655 -- Verify API version compatibility
656 -- Check that login user's status is active
657 -- IF user not active THEN
658 -- Return error 'user account currently suspended'
659 -- ENDIF
660 -- Set rollback SAVEPOINT
661 --
662 -- Commit transaction if requested
663 -- ENDIF
664 -- END
665 --
666 --------------------------------------------------------------------------------
667 --------------------------------------------------------------------------------
668 -- Start of comments
669 -- API name : Remove_ItemChannelMatch
670 -- Type : Private
671 -- Pre-reqs : None
672 -- Function : do match the passed item with the passed channel.
673 -- Check the reqirement of the channel.
674 -- Call approval process procedure if needed.
675 -- Parameters :
676 -- IN p_api_version NUMBER Required
677 -- p_init_msg_list VARCHAR2 Optional
678 -- Default = FND_API.G_FALSE
679 -- p_commit VARCHAR2 Optional
680 -- Default = FND_API.G_FALSE
681 -- p_validation_level NUMBER Optional
682 -- Default = FND_API.G_VALID_LEVEL_FULL
683 -- p_check_login_user VARCHAR2 Optional
684 -- Default = FND_API.G_TRUE
685 -- Flag for checking user privilege and if user is login.
689 -- Default = FND_API.G_MISS_NUM
686 -- p_category_id NUMBER Optional
687 -- Default = FND_API.G_MISS_NUM
688 -- p_channel_id NUMBER Optional
690 -- the id of the channel to be matched. If missing,
691 -- the item is pushed to the category identified by
692 -- p_category_id.
693 -- p_item_id NUMBER Required
694 -- the id of the item to be matched.
695 -- p_match_type VARCHAR2 Required
696 -- matching type: by force or by rule.
697 -- p_territory_id NUMBER Optional
698 -- territory to which the item is published.
699 -- OUT : x_return_status VARCHAR2
700 -- x_msg_count NUMBER
701 -- x_msg_data VARCHAR2
702 -- Notes : Assume caller has check the validation of item id
703 -- and channel id. And the item has NOT yet been match
704 -- with the channel.
705 --
706 -- End of comments
707 --
708 PROCEDURE Remove_ItemChannelMatch
709 (
710 p_api_version IN NUMBER,
711 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
712 p_commit IN VARCHAR2 := FND_API.G_FALSE,
713 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
714 x_return_status OUT NOCOPY VARCHAR2,
715 x_msg_count OUT NOCOPY NUMBER,
716 x_msg_data OUT NOCOPY VARCHAR2,
717 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
718 p_application_id IN NUMBER := G_AMV_APP_ID,
719 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
720 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
721 p_item_id IN NUMBER,
722 p_table_name_code IN VARCHAR2,
723 p_territory_id IN NUMBER := FND_API.G_MISS_NUM
724 );
725 --
726 -- Algorithm:
727 -- BEGIN
728 -- Verify API version compatibility
729 -- Check that login user's status is active
730 -- IF user not active THEN
731 -- Return error 'user account currently suspended'
732 -- ENDIF
733 -- Set rollback SAVEPOINT
734 --
735 -- Commit transaction if requested
736 -- ENDIF
737 -- END
738 --
739 --------------------------------------------------------------------------------
740 --------------------------------------------------------------------------------
741 --
742 --
743 --------------------------------------------------------------------------------
744 --------------------------------------------------------------------------------
745 -- Start of comments
746 -- API name : Get_UserTerritory
747 -- Type : Private
748 -- Pre-reqs : None
749 -- Function : return all the territories(id and name) for the specified user.
750 -- Parameters :
751 -- IN p_api_version NUMBER Required
752 -- p_init_msg_list VARCHAR2 Optional
753 -- Default = FND_API.G_FALSE
754 -- p_check_login_user VARCHAR2 Optional
755 -- Default = FND_API.G_TRUE
756 -- Flag for checking user privilege and if user is login.
757 -- p_resource_id NUMBER Required
758 -- This should be the resource id.
759 -- p_resource_type VARCHAR2 optional
760 -- If not passed will be defaulted to 'EMPLOYEE'
761 -- OUT : x_return_status VARCHAR2
762 -- x_msg_count NUMBER
763 -- x_msg_data VARCHAR2
764 -- x_terr_id_varray AMV_NUMBER_VARRAY_TYPE
765 -- x_territory_varray AMV_CHAR_VARRAY_TYPE
766 -- Notes :
767 --
768 -- End of comments
769 --
770 PROCEDURE Get_UserTerritory
771 (
772 p_api_version IN NUMBER,
773 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
774 x_return_status OUT NOCOPY VARCHAR2,
775 x_msg_count OUT NOCOPY NUMBER,
776 x_msg_data OUT NOCOPY VARCHAR2,
777 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
778 p_resource_id IN NUMBER,
779 p_resource_type IN VARCHAR2 := 'RS_EMPLOYEE',
780 x_terr_id_tbl OUT NOCOPY terr_id_tbl_type,
781 x_terr_name_tbl OUT NOCOPY terr_name_tbl_type
782 );
783 --
784 --------------------------------------------------------------------------------
785 --------------------------------------------------------------------------------
786 -- Start of comments
787 -- API name : Get_PublishedTerritories
788 -- Type : Private
789 -- Pre-reqs : None
790 -- Function : return all the territories(id and name) for the specified user.
791 -- Parameters :
792 -- IN p_api_version NUMBER Required
793 -- p_init_msg_list VARCHAR2 Optional
794 -- Default = FND_API.G_FALSE
795 -- p_check_login_user VARCHAR2 Optional
796 -- Default = FND_API.G_TRUE
800 -- OUT : x_return_status VARCHAR2
797 -- Flag for checking user privilege and if user is login.
798 -- p_terr_id NUMBER Required
799 -- The should be the resource id.
801 -- x_msg_count NUMBER
802 -- x_msg_data VARCHAR2
803 -- x_item_id_tbl territory_tbl_type
804 -- Notes :
805 --
806 -- End of comments
807 --
808 PROCEDURE Get_PublishedTerritories
809 (
810 p_api_version IN NUMBER,
811 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
812 x_return_status OUT NOCOPY VARCHAR2,
813 x_msg_count OUT NOCOPY NUMBER,
814 x_msg_data OUT NOCOPY VARCHAR2,
815 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
816 p_terr_id IN NUMBER,
817 p_table_name_code IN VARCHAR2,
818 x_item_id_tbl OUT NOCOPY terr_id_tbl_type
819 );
820 --
821 --------------------------------------------------------------------------------
822 -- Algorithm:
823 -- BEGIN
824 -- Verify API version compatibility
825 -- Check that login user's status is active
826 -- IF user not active THEN
827 -- Return error 'user account currently suspended'
828 -- ENDIF
829 -- Set rollback SAVEPOINT
830 --
831 -- Commit transaction if requested
832 -- ENDIF
833 -- END
834 --
835 --------------------------------------------------------------------------------
836 --------------------------------------------------------------------------------
837 --
838 END amv_match_pvt;
[Home] [Help]