1 PACKAGE AS_INTEREST_PUB AUTHID CURRENT_USER as
2 /* $Header: asxpints.pls 115.16 2003/11/06 13:58:28 gbatra ship $ */
3
4 --
5 -- NAME
6 -- AS_INTEREST_PUB
7 --
8 -- PURPOSE
9 -- Provide public interest record and table type to be used by APIs that
10 -- import interests/classifications into OSM
11 --
12 -- Convert the public interest records into private interest records for use by
13 -- the AS_INTEREST_PVT.Create_Interest routine
14 --
15 -- Procedures:
16 -- Convert_Values_To_Ids (
17 -- p_interest_tbl IN INTEREST_TBL_TYPE,
18 -- p_pvt_interest_tbl OUT NOCOPY AS_INTEREST_PVT.INTEREST_TBL_TYPE)
19 --
20 -- Convert_Interest_Values_To_Ids (
21 -- p_interest_type IN VARCHAR2,
22 -- p_interest_type_id IN NUMBER,
23 -- p_primary_interest_code IN VARCHAR2,
24 -- p_primary_interest_code_id IN NUMBER,
25 -- p_secondary_interest_code IN VARCHAR2,
26 -- p_secondary_interest_code_id IN NUMBER,
27 -- p_return_status OUT NOCOPY VARCHAR2,
28 -- p_out_interest_type_id OUT NOCOPY NUMBER,
29 -- p_out_primary_interest_code_id OUT NOCOPY NUMBER,
30 -- p_out_second_interest_code_id OUT NOCOPY NUMBER)
31 --
32 --
33 -- NOTES
34 -- The procedures in this package are not supported for use by anyone outside
35 -- of OSM. The procedures are called from the necessary API's to convert the
36 -- number into the table type excepted by the Private Interest API routine
37 -- (create_interest)
38 --
39 -- HISTORY
40 -- 11/12/96 JKORNBER Created
41 -- 08/28/98 AWU Add update_interest
42 -- Add interest_id, customer_id, address_id,
43 -- contact_id and lead_id into
44 -- interest record
45 -- Changed interest rec default value NULL to
46 -- FND_API.G_MISS for update purpose
47 -- 11/03/03 GBATRA Product Hierarchy Uptake
48 --
49
50
51 -- Start of Comments
52 --
53 -- Interest Record (Account Classification, Contact Interest, Lead Classification): interest_rec_type
54 --
55 -- Parameters:
56 -- Product Category Id Valid category_id from eni_prod_den_hrchy_parents_v denonted as
57 -- correct classification product category
58 -- Product Category Set Id Valid category_set_id from from eni_prod_den_hrchy_parents_v
59 -- Status Code Valid status code from as_interest_statuses
60 -- Status Valid status (from as_interest_statuses)
61 -- Description Free format text
62 -- Attibute Category No validation
63 -- Attibute 1 -15 No validation
64 --
65 -- Required
66 -- Product Category Id and Product Category Set Id
67 --
68 --
69 -- End of Comments
70
71 TYPE interest_rec_type IS RECORD
72 (
73 interest_id NUMBER := FND_API.G_MISS_NUM,
74 customer_id NUMBER := FND_API.G_MISS_NUM,
75 address_id NUMBER := FND_API.G_MISS_NUM,
76 contact_id NUMBER := FND_API.G_MISS_NUM,
77 lead_id NUMBER := FND_API.G_MISS_NUM,
78 interest_type_id NUMBER := FND_API.G_MISS_NUM,
79 last_update_date DATE := FND_API.G_MISS_DATE,
80 last_updated_by NUMBER := FND_API.G_MISS_NUM,
81 creation_date DATE := FND_API.G_MISS_DATE,
82 created_by NUMBER := FND_API.G_MISS_NUM,
83 last_update_login NUMBER := FND_API.G_MISS_NUM,
84 interest_type VARCHAR2(80) := FND_API.G_MISS_CHAR,
85 primary_interest_code_id NUMBER := FND_API.G_MISS_NUM,
86 primary_interest_code VARCHAR2(100) := FND_API.G_MISS_CHAR,
87 secondary_interest_code_id NUMBER := FND_API.G_MISS_NUM,
88 secondary_interest_code VARCHAR2(100) := FND_API.G_MISS_CHAR,
89 status_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
90 status VARCHAR2(80) := FND_API.G_MISS_CHAR,
91 description VARCHAR2(240) := FND_API.G_MISS_CHAR,
92 ATTRIBUTE_CATEGORY VARCHAR2(30) := FND_API.G_MISS_CHAR,
93 ATTRIBUTE1 VARCHAR2(150) := FND_API.G_MISS_CHAR,
94 ATTRIBUTE2 VARCHAR2(150) := FND_API.G_MISS_CHAR,
95 ATTRIBUTE3 VARCHAR2(150) := FND_API.G_MISS_CHAR,
96 ATTRIBUTE4 VARCHAR2(150) := FND_API.G_MISS_CHAR,
97 ATTRIBUTE5 VARCHAR2(150) := FND_API.G_MISS_CHAR,
98 ATTRIBUTE6 VARCHAR2(150) := FND_API.G_MISS_CHAR,
99 ATTRIBUTE7 VARCHAR2(150) := FND_API.G_MISS_CHAR,
100 ATTRIBUTE8 VARCHAR2(150) := FND_API.G_MISS_CHAR,
101 ATTRIBUTE9 VARCHAR2(150) := FND_API.G_MISS_CHAR,
102 ATTRIBUTE10 VARCHAR2(150) := FND_API.G_MISS_CHAR,
103 ATTRIBUTE11 VARCHAR2(150) := FND_API.G_MISS_CHAR,
104 ATTRIBUTE12 VARCHAR2(150) := FND_API.G_MISS_CHAR,
105 ATTRIBUTE13 VARCHAR2(150) := FND_API.G_MISS_CHAR,
106 ATTRIBUTE14 VARCHAR2(150) := FND_API.G_MISS_CHAR,
107 ATTRIBUTE15 VARCHAR2(150) := FND_API.G_MISS_CHAR,
108 product_category_id NUMBER := FND_API.G_MISS_NUM,
109 product_cat_set_id NUMBER := FND_API.G_MISS_NUM
110 );
111
112 G_MISS_INTEREST_REC interest_rec_type;
113
114 -- Start of Comments
115 --
116 -- Interest Table: interest_tbl_type
117 --
118 --
119 -- End of Comments
120
121 TYPE interest_tbl_type IS TABLE OF interest_rec_type
122 INDEX BY BINARY_INTEGER;
123 G_MISS_INTEREST_TBL interest_tbl_type;
124
125 -- Start of Comments
126 --
127 -- Interest Code Record (Account Classification, Contact Interest, Lead Classification): interest_rec_type
128 --
129 -- Parameters:
130 -- interest_code_id Interest Code identifier
131 -- code A code to identify an interest item
132 -- interest_type_id Type of interest
133 -- enabled_flag A flag indicating whether code is enabled
134 -- parent_interest_code_id Parent interest code identifier
135 -- category_id category identifier
136 -- category_set_id category set identifier
137 -- org_id operating unit that performed the transaction
138 -- pf_item_id product family identifier
139 -- pf_organization_id product familiy organization identifier
140 -- price price of secondary interest code
141 -- currency_code currency code for the product family.
142 -- Description Free format text, if null then
143 -- description of most detailed interest
144 -- (i.e. secondary, if no secondary then
145 -- primary, ...)
146 -- Attibute Category No validation
147 -- Attibute 1 -15 No validation
148 --
149 -- Required
150 -- Interest_code_ID
151 --
152 --
153 -- End of Comments
154
155 TYPE interest_code_rec_type IS RECORD
156 ( interest_code_id NUMBER := FND_API.G_MISS_NUM,
157 code VARCHAR2(100) := FND_API.G_MISS_CHAR,
158 interest_type_id NUMBER := FND_API.G_MISS_NUM,
159 revenue_class_id NUMBER := FND_API.G_MISS_NUM,
160 enabled_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
161 parent_interest_code_id NUMBER := FND_API.G_MISS_NUM,
162 description VARCHAR2(240) := FND_API.G_MISS_CHAR,
163 category_id NUMBER := FND_API.G_MISS_NUM,
164 category_set_id NUMBER := FND_API.G_MISS_NUM,
165 org_id NUMBER := FND_API.G_MISS_NUM,
166 pf_item_id NUMBER := FND_API.G_MISS_NUM,
167 pf_organization_id NUMBER := FND_API.G_MISS_NUM,
168 price NUMBER := FND_API.G_MISS_NUM,
169 currency_code VARCHAR2(15) := FND_API.G_MISS_CHAR,
170 attribute_category VARCHAR2(30),
171 attribute1 VARCHAR2(150),
172 attribute2 VARCHAR2(150),
173 attribute3 VARCHAR2(150),
174 attribute4 VARCHAR2(150),
175 attribute5 VARCHAR2(150),
176 attribute6 VARCHAR2(150),
177 attribute7 VARCHAR2(150),
178 attribute8 VARCHAR2(150),
179 attribute9 VARCHAR2(150),
180 attribute10 VARCHAR2(150),
181 attribute11 VARCHAR2(150),
182 attribute12 VARCHAR2(150),
183 attribute13 VARCHAR2(150),
184 attribute14 VARCHAR2(150),
185 attribute15 VARCHAR2(150),
186 product_category_id NUMBER := FND_API.G_MISS_NUM,
187 product_cat_set_id NUMBER := FND_API.G_MISS_NUM
188 );
189
190
191 -- Start of Comments
192 --
193 -- Interest Table: interest_code_tbl_type
194 --
195 --
196 -- End of Comments
197
198 TYPE interest_code_tbl_type IS TABLE OF interest_code_rec_type
199 INDEX BY BINARY_INTEGER;
200 G_MISS_INTEREST_CODE_REC INTEREST_CODE_REC_TYPE;
201 G_MISS_INTEREST_code_TBL interest_code_tbl_type;
202
203
204 -- Start of Comments
205 --
206 -- API name : Create_Interest
207 -- Type : Public
208 -- Function : Create an interest for an existing account/contact/lead.
209 -- Pre-reqs : If associating to customer, account, or opportunity, then
210 -- each must exist
211 -- Parameters :
212 -- IN :
213 -- p_api_version_number IN NUMBER Required
214 -- p_init_msg_list IN VARCHAR2 Optional
215 -- Default = FND_API.G_FALSE
216 -- p_commit IN VARCHAR2 Optional
217 -- Default = FND_API.G_FALSE
218 -- p_interest_tbl IN INTEREST_REC_TYPE Optional
219 --
220 -- OUT :
221 -- p_return_status OUT VARCHAR2(1)
222 -- p_msg_count OUT NUMBER
223 -- p_msg_data OUT VARCHAR2(2000)
224 -- p_interest_id OUT NUMBER
225 --
226 --
227 --
228 -- Version : Current version 1.0
229 -- Initial Version
230 -- Initial version 1.0
231 --
232 -- Notes: OSM API to create interests
233 --
234 -- End of Comments
235 --
236 Procedure create_interest(p_api_version_number in number
237 ,p_init_msg_list in varchar2 := fnd_api.g_false
238 ,p_commit in varchar2 := fnd_api.g_false
239 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
240 ,p_interest_rec in interest_rec_type
241 ,p_customer_id in number
242 ,p_address_id in number
243 ,p_contact_id in number
244 ,p_lead_id in number
245 ,p_interest_use_code in varchar2
246 ,p_check_access_flag in varchar2
247 ,p_admin_flag in varchar2
248 ,p_admin_group_id in number
249 ,p_identity_salesforce_id in number
250 ,p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE
251 ,p_return_status OUT NOCOPY varchar2
252 ,p_msg_count OUT NOCOPY number
253 ,p_msg_data OUT NOCOPY varchar2
254 ,p_interest_out_id OUT NOCOPY number);
255
256
257 -- Start of Comments
258 --
259 -- API name : Update Interest
260 -- Type : Public
261 -- Function : Update Account, Contact, or Lead Classification Interest
262 -- Pre-reqs : Account, contact, or lead exists
263 -- Parameters
264 -- IN :
265 -- p_api_version_number IN NUMBER Required
266 -- p_init_msg_list IN VARCHAR2 Optional
267 -- Default = FND_API.G_FALSE
268 -- p_commit IN VARCHAR2 Optional
269 -- Default = FND_API.G_FALSE
270 -- p_validation_level IN NUMBER Optional
271 -- Default = FND_API.G_VALID_LEVEL_FULL
272 -- p_identity_salesforce_id IN NUMBER Optional
273 -- p_interest_rec IN INTEREST_REC_TYPE Required
274 -- p_interest_use_code IN VARCHAR2 Required
275 -- (LEAD_CLASSIFICATION, COMPANY_CLASSIFICATION,
276 -- CONTACT_INTEREST)
277 --
278 -- OUT :
279 -- x_return_status OUT VARCHAR2(1)
280 -- x_msg_count OUT NUMBER
281 -- x_msg_data OUT VARCHAR2(2000)
282 -- x_interest_id OUT NUMBER
283 --
284 -- Version : Current version 1.0
285 -- Initial Version
286 -- Initial version 1.0
287 --
288 --
289 --
290 -- End of Comments
291 --
292
293 PROCEDURE Update_Interest
294 ( p_api_version_number IN NUMBER,
295 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
296 p_commit IN VARCHAR2 := FND_API.G_FALSE,
297 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
298 p_identity_salesforce_id IN NUMBER := NULL,
299 p_interest_rec IN INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
300 p_interest_use_code IN VARCHAR2,
301 p_check_access_flag in varchar2,
302 p_admin_flag in varchar2,
303 p_admin_group_id in number,
304 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
305 x_return_status OUT NOCOPY VARCHAR2,
306 x_msg_count OUT NOCOPY NUMBER,
307 x_msg_data OUT NOCOPY VARCHAR2,
308 x_interest_id OUT NOCOPY NUMBER
309 );
310
311 -- Start of Comments
312 --
313 -- NAME
314 -- Convert_Values_To_Ids
315 --
316 -- PURPOSE
317 -- Procedure converts public interest record into a private interest
318 -- record for use by the private create_interest API.
319 --
320 -- NOTES
321 -- This procedure is public so that it can be called by other API's.
322 -- It should not be called from sources outside of OSM
323 --
324 --
325 -- End of Comments
326
327 PROCEDURE Convert_Values_To_Ids ( p_interest_tbl IN INTEREST_TBL_TYPE,
328 p_pvt_interest_tbl OUT NOCOPY AS_INTEREST_PVT.INTEREST_TBL_TYPE
329 );
330
331
332
333 -- Start of Comments
334 --
335 -- NAME
336 -- Convert_Interest_Values_To_Ids
337 --
338 -- PURPOSE
339 -- Procedure converts interest type, primary, and secondar values to ids
340 --
341 -- NOTES
342 -- This procedure is public so that it can be called by other API's.
343 -- Currently this procedure is used by the Create_Opportunity API to
344 -- convert the expected purchase values to ids and from the interest
345 -- Convert value to Ids routine found above
346 --
347 --
348 -- End of Comments
349 PROCEDURE Convert_Interest_Values_To_Ids ( p_interest_type IN VARCHAR2,
350 p_interest_type_id IN NUMBER,
351 p_primary_interest_code IN VARCHAR2,
352 p_primary_interest_code_id IN NUMBER,
353 p_secondary_interest_code IN VARCHAR2,
354 p_secondary_interest_code_id IN NUMBER,
355 p_description IN VARCHAR2,
356 p_return_status OUT NOCOPY VARCHAR2,
357 p_out_interest_type_id OUT NOCOPY NUMBER,
358 p_out_primary_interest_code_id OUT NOCOPY NUMBER,
359 p_out_second_interest_code_id OUT NOCOPY NUMBER,
360 p_out_description OUT NOCOPY VARCHAR2
361 );
362
363 PROCEDURE Delete_Interest
364 ( p_api_version_number IN NUMBER,
365 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
366 p_commit IN VARCHAR2 := FND_API.G_FALSE,
367 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
368 p_identity_salesforce_id IN NUMBER,
369 p_interest_rec IN INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
370 p_interest_use_code IN VARCHAR2,
371 p_check_access_flag in varchar2,
372 p_admin_flag in varchar2,
373 p_admin_group_id in number,
374 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_msg_count OUT NOCOPY NUMBER,
377 x_msg_data OUT NOCOPY VARCHAR2
378 );
379
380 END AS_INTEREST_PUB;