1 PACKAGE BODY ams_phone_utils AS
2 /* $Header: amsvlphb.pls 115.8 2002/11/22 08:55:46 jieli ship $ */
3 --
4 -- Copyright (c) 2001 by Oracle Corporation
5 --
6 -- NAME
7 -- ams_phone_utils_body.sql - Functions to retrieve a desired phone number
8 -- based on order of creation date.
9 --
10 -- DESCRIPTION
11 -- These functions return the phone number for the desired preferred
12 -- order based on the order of creation date.
13 --
14 -- NOTES
15 -- This is to statisfy the Advanced Outbound requirements to see phone
16 -- numbers 1 through 6 and because the HZ tables can not handle this
17 -- type of quering at it's present state.
18 --
19 -- REQUIREMENTS
20 -- This package MUST be created with the authority of the definer.
21 -- (authid definer)
22 -- All variables passed in must start with: p_
23 -- All out variables must start with: x_
24 --
25 --------------------------------------------------------------------------------
26 -- FUNCTIONS:
27 -- get_phone - Return the phone number
28 -- get_raw_phone - Return the raw phone number
29 -- get_creation_date - Return the creation date
30 --
31 -- PROCEDURES:
32 -- None at this time
33 --
34 -- PRIVATE FUNCTIONS/PROCEDURES:
35 -- None at this time
36 --
37 --------------------------------------------------------------------------------
38 -- MODIFIED (MM/DD/YYYY) DESCRIPTION
39 -- jmanzell 02/14/2001 Initial creation
40 --
41 --------------------------------------------------------------------------------
42 ------------- FUNCTIONS ------------------------------------------------
43 --------------------------------------------------------------------------------
44
45 --
46 -- get_phone_number
47 --
48 -- This function returns the phone number for the specified PARTY_ID and
49 -- preferred order.
50 --
51 -- Arguments: number (Party ID)
52 -- number (Perferred order)
53 --
54 -- Returns: varchar2 (Phone Number)
55 --
56 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
57 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
58 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
59
60 FUNCTION get_phone(p_party_id in number, p_phone_pref_order in number)
61 RETURN varchar2
62 IS
63 -- Get records
64 cursor phones (c_party_id number) is
65 select phone_number
66 from hz_contact_points
67 where owner_table_id = c_party_id
68 and owner_table_name = 'HZ_PARTIES'
69 order by creation_date;
70
71 -- Party id
72 n_party_id number := p_party_id;
73
74 -- Phone number preferred order number
75 n_phone_pref_order number := p_phone_pref_order;
76
77 -- Phone Number
78 n_phone_number varchar2(60);
79
80 BEGIN
81
82 OPEN phones(n_party_id);
83
84 -- Loop only the desired number of times
85 -- or until out of records
86 for x in 1..n_phone_pref_order loop
87
88 FETCH phones into n_phone_number;
89 EXIT WHEN x = n_phone_pref_order + 1 OR phones%NOTFOUND;
90
91 end loop;
92
93 -- If we ran out of records, then return null
94 -- otherwise, return the phone number
95 if phones%NOTFOUND then
96 return null;
97 else
98 return n_phone_number;
99 end if;
100
101 CLOSE phones;
102
103 END get_phone;
104
105 --
106 -- get_raw_phone
107 --
108 -- This function returns the raw phone number for the specified PARTY_ID
109 -- and preferred order.
110 --
111 -- Arguments: number (Party ID)
112 -- number (Perferred order)
113 --
114 -- Returns: varchar2 (Raw Phone Number)
115 --
116 FUNCTION get_raw_phone(p_party_id in number, p_phone_pref_order in number)
117 RETURN varchar2
118 IS
119 -- Get records
120 cursor phones (c_party_id number) is
121 select raw_phone_number
122 from hz_contact_points
123 where owner_table_id = c_party_id
124 and owner_table_name = 'HZ_PARTIES'
125 order by creation_date;
126
127 -- Party id
128 n_party_id number := p_party_id;
129
130 -- Phone number preferred order number
131 n_phone_pref_order number := p_phone_pref_order;
132
133 -- Phone Number
134 n_phone_number varchar2(60);
135
136 BEGIN
137
138 OPEN phones(n_party_id);
139
140 -- Loop only the desired number of times
141 -- or until out of records
142 for x in 1..n_phone_pref_order loop
143
144 FETCH phones into n_phone_number;
145 EXIT WHEN x = n_phone_pref_order + 1 OR phones%NOTFOUND;
146
147 end loop;
148
149 -- If we ran out of records, then return null
150 -- otherwise, return the phone number
151 if phones%NOTFOUND then
152 return null;
153 else
154 return n_phone_number;
155 end if;
156
157 CLOSE phones;
158
159 END get_raw_phone;
160
161 --
162 -- get_creation_date
163 --
164 -- This function returns the creation date for the specified PARTY_ID and
165 -- preferred order.
166 --
167 -- Arguments: number (Party ID)
168 -- number (Perferred order)
169 --
170 -- Returns: date (Creation Date)
171 --
172 FUNCTION get_creation_date(p_party_id in number, p_phone_pref_order in number)
173 RETURN date
174 IS
175 -- Get records
176 cursor phones (c_party_id number) is
177 select creation_date
178 from hz_contact_points
179 where owner_table_id = c_party_id
180 and owner_table_name = 'HZ_PARTIES'
181 order by creation_date;
182
183 -- Party id
184 n_party_id number := p_party_id;
185
186 -- Phone number preferred order number
187 n_phone_pref_order number := p_phone_pref_order;
188
189 -- Creation Date
190 n_cr8_date date;
191
192
193 BEGIN
194
195 OPEN phones(n_party_id);
196
197 -- Loop only the desired number of times
198 -- or until out of records
199 for x in 1..n_phone_pref_order loop
200
201 FETCH phones into n_cr8_date;
202 EXIT WHEN x = n_phone_pref_order + 1 OR phones%NOTFOUND;
203
204 end loop;
205
206 -- If we ran out of records, then return null
207 -- otherwise, return the creation date
208 if phones%NOTFOUND then
209 return null;
210 else
211 return n_cr8_date;
212 end if;
213
214 CLOSE phones;
215
216 END get_creation_date;
217
218 END ams_phone_utils;
[Home] [Help]