1 PACKAGE BODY AMS_MKT_SEGMENTS_PVT AS
2 /* $Header: amsvmktb.pls 115.9 2001/12/14 16:27:26 pkm ship $ */
3 ---------------------------------------------------------------------
4 -- function
5 -- Get_Mkt_Segments
6 --
7 -- HISTORY
8 -- 10/14/99 abhola Create.
9 ---------------------------------------------------------------------
10
11 FUNCTION Get_Mkt_Segments ( p_party_id ams_party_market_segments.party_id%TYPE := NULL ,
12 p_target_segment_id ams_party_market_segments.market_segment_id%TYPE := NULL)
13
14 RETURN PartyMktSegTabTyp IS
15
16 x_PartyMktSegTabTyp PartyMktSegTabTyp;
17 i BINARY_INTEGER := 0;
18
19 CURSOR c_party (c_party_id IN ams_party_market_segments.party_id%TYPE)
20 IS
21 SELECT a.market_segment_id, a.act_market_segment_used_by_id
22 FROM ams_act_market_segments a,
23 ams_party_market_segments b
24 WHERE b.party_id = c_party_id
25 AND b.market_segment_id = a.market_segment_id
26 -- AND b.market_segment_flag = 'N'
27 AND a.arc_act_market_segment_used_by = 'CAMP';
28
29 CURSOR c_target ( c_target_id IN ams_party_market_segments.market_segment_id%TYPE)
30 IS
31 SELECT a.market_segment_id, a.act_market_segment_used_by_id
32 FROM ams_act_market_segments a
33 WHERE a.market_segment_id = c_target_id
34 AND a.arc_act_market_segment_used_by = 'CAMP';
35
36 CURSOR c_party_target ( c_party_id IN ams_party_market_segments.party_id%TYPE,
37 c_target_id IN ams_party_market_segments.market_segment_id%TYPE )
38 IS
39 SELECT a.market_segment_id, a.act_market_segment_used_by_id
40 FROM ams_act_market_segments a,
41 ams_party_market_segments b
42 WHERE b.party_id = c_party_id
43 AND b.market_segment_id = c_target_id
44 AND b.market_segment_id = a.market_segment_id
45 -- AND b.market_segment_flag = 'N'
46 AND a.arc_act_market_segment_used_by = 'CAMP';
47
48 BEGIN
49 if (p_party_id IS NOT NULL) AND
50 ( p_target_segment_id IS NULL ) then
51 OPEN c_party (p_party_id);
52 LOOP
53 i := i + 1;
54 FETCH c_party INTO x_PartyMktSegTabTyp(i);
55 EXIT WHEN c_party%NOTFOUND;
56 END LOOP;
57 CLOSE c_party;
58
59 return( x_PartyMktSegTabTyp );
60
61 elsif (p_party_id IS NULL) AND
62 (p_target_segment_id IS NOT NULL ) then
63
64 OPEN c_target (p_target_segment_id);
65 LOOP
66 i := i + 1;
67 FETCH c_target INTO x_PartyMktSegTabTyp(i);
68 EXIT WHEN c_target%NOTFOUND;
69 END LOOP;
70 CLOSE c_target;
71
72 return( x_PartyMktSegTabTyp );
73
74 elsif (p_party_id IS NOT NULL) AND
75 (p_target_segment_id IS NOT NULL ) then
76 OPEN c_party_target (p_party_id,p_target_segment_id);
77 LOOP
78 i := i + 1;
79 FETCH c_party_target INTO x_PartyMktSegTabTyp(i);
80 EXIT WHEN c_party_target%NOTFOUND;
81 END LOOP;
82 CLOSE c_party_target;
83
84 return( x_PartyMktSegTabTyp );
85 else
86 return( x_PartyMktSegTabTyp );
87 end if;
88 END Get_Mkt_Segments;
89
90 END AMS_MKT_SEGMENTS_PVT;
[Home] [Help]