1 PACKAGE BODY ast_ams_source_pkg AS
2 /* $Header: astmsrcb.pls 115.2 2002/02/05 18:03:48 pkm ship $ */
3 -- Start of Comments
4 -- Package name : ast_ams_source_pkg
5 -- Purpose : Function to provide source code name in AST_AMS_SOURCE_CODES_V view
6 -- History :
7 -- NOTE :
8 -- End of Comments
9 function fetch_source_code_name (
10 p_source_code_type IN VARCHAR2,
11 p_source_code IN VARCHAR2)
12 return VARCHAR2
13 is
14
15 cursor c_campaign(a_source_code VARCHAR2)
16 is
17 SELECT campaign_name
18 FROM AMS_CAMPAIGNS_VL
19 WHERE source_code=a_source_code;
20
21 cursor c_campaign_sched(a_source_code VARCHAR2)
22 is
23 SELECT schedule_name
24 FROM AMS_CAMPAIGN_SCHEDULES_VL
25 WHERE source_code=a_source_code;
26
27 CURSOR c_event_hdr(a_source_code VARCHAR2)
28 is
29 SELECT event_header_name
30 FROM AMS_EVENT_HEADERS_VL
31 WHERE source_code=a_source_code;
32
33 CURSOR c_event_ofr(a_source_code VARCHAR2)
34 is
35 SELECT event_offer_name
36 FROM AMS_EVENT_OFFERS_VL
37 WHERE source_code=a_source_code;
38
39 l_source_code_name VARCHAR2(240);
40
41 BEGIN
42 IF (p_source_code_type='CAMP') THEN
43 OPEN c_campaign(p_source_code);
44 FETCH c_campaign INTO l_source_code_name;
45 CLOSE c_campaign;
46 ELSIF (p_source_code_type='CSCH') THEN
47 OPEN c_campaign_sched(p_source_code);
48 FETCH c_campaign_sched INTO l_source_code_name;
49 CLOSE c_campaign_sched;
50 ELSIF (p_source_code_type='EVEH') THEN
51 OPEN c_event_hdr(p_source_code);
52 FETCH c_event_hdr INTO l_source_code_name;
53 CLOSE c_event_hdr;
54 ELSIF (p_source_code_type='EVEO') THEN
55 OPEN c_event_ofr(p_source_code);
56 FETCH c_event_ofr INTO l_source_code_name;
57 CLOSE c_event_ofr;
58 END IF;
59 RETURN l_source_code_name;
60 END fetch_source_code_name;
61 END ast_ams_source_pkg;
[Home] [Help]