1 PACKAGE PV_PARTNER_UTIL_PVT AUTHID CURRENT_USER AS
2 /* $Header: pvxvputs.pls 120.4 2005/11/14 21:03:45 pinagara ship $ */
3
4 /*----------------------------------------------------------------------------
5 -- HISTORY
6 -- 20-SEP-2002 rdsharma Created
7 -----------------------------------------------------------------------------*/
8
9 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_PARTNER_UTIL_PVT';
10 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvputs.pls';
11
12 geometry_status_code_default CONSTANT VARCHAR2(30) := 'DIRTY';
13 g_miss_content_source_type CONSTANT VARCHAR2(30) := 'USER_ENTERED';
14 /*-----------------------------------------------------------------------------
15 -- Declaration of Location_rec_type
16 ------------------------------------------------------------------------------*/
17
18 TYPE location_rec_type IS RECORD(
19 location_id NUMBER,
20 orig_system_reference VARCHAR2(240),
21 orig_system VARCHAR2(30),
22 country VARCHAR2(60),
23 address1 VARCHAR2(240),
24 address2 VARCHAR2(240),
25 address3 VARCHAR2(240),
26 address4 VARCHAR2(240),
27 city VARCHAR2(60),
28 postal_code VARCHAR2(60),
29 state VARCHAR2(60),
30 province VARCHAR2(60),
31 county VARCHAR2(60),
32 address_key VARCHAR2(500),
33 address_style VARCHAR2(30),
34 validated_flag VARCHAR2(1),
35 address_lines_phonetic VARCHAR2(560),
36 po_box_number VARCHAR2(50),
37 house_number VARCHAR2(50),
38 street_suffix VARCHAR2(50),
39 street VARCHAR2(50),
40 street_number VARCHAR2(50),
41 floor VARCHAR2(50),
42 suite VARCHAR2(50),
43 postal_plus4_code VARCHAR2(10),
44 position VARCHAR2(50),
45 location_directions VARCHAR2(640),
46 address_effective_date DATE,
47 address_expiration_date DATE,
48 clli_code VARCHAR2(60),
49 language VARCHAR2(4) ,
50 short_description VARCHAR2(240),
51 description VARCHAR2(2000),
52 geometry_status_code VARCHAR2(30) := geometry_status_code_default,
53 loc_hierarchy_id NUMBER,
54 sales_tax_geocode VARCHAR2(30),
55 sales_tax_inside_city_limits VARCHAR2(30),
56 fa_location_id NUMBER,
57 content_source_type VARCHAR2(30) := g_miss_content_source_type,
58 attribute_category VARCHAR2(30) ,
59 attribute1 VARCHAR2(150),
60 attribute2 VARCHAR2(150),
61 attribute3 VARCHAR2(150),
62 attribute4 VARCHAR2(150),
63 attribute5 VARCHAR2(150),
64 attribute6 VARCHAR2(150),
65 attribute7 VARCHAR2(150),
66 attribute8 VARCHAR2(150),
67 attribute9 VARCHAR2(150),
68 attribute10 VARCHAR2(150),
69 attribute11 VARCHAR2(150),
70 attribute12 VARCHAR2(150),
71 attribute13 VARCHAR2(150),
72 attribute14 VARCHAR2(150),
73 attribute15 VARCHAR2(150),
74 attribute16 VARCHAR2(150),
75 attribute17 VARCHAR2(150),
76 attribute18 VARCHAR2(150),
77 attribute19 VARCHAR2(150),
78 attribute20 VARCHAR2(150),
79 timezone_id NUMBER,
80 created_by_module VARCHAR2(150),
81 application_id NUMBER,
82 actual_content_source VARCHAR2(30)
83 );
84
85 /*-----------------------------------------------------------------------------
86 -- Declaration of person_rec_type
87 ------------------------------------------------------------------------------*/
88 TYPE person_rec_type IS RECORD(
89 person_pre_name_adjunct VARCHAR2(30),
90 person_first_name VARCHAR2(150),
91 person_middle_name VARCHAR2(60),
92 person_last_name VARCHAR2(150),
93 person_name_suffix VARCHAR2(30),
94 person_title VARCHAR2(60),
95 person_academic_title VARCHAR2(30),
96 person_previous_last_name VARCHAR2(150),
97 person_initials VARCHAR2(6),
98 known_as VARCHAR2(240),
99 known_as2 VARCHAR2(240),
100 known_as3 VARCHAR2(240),
101 known_as4 VARCHAR2(240),
102 known_as5 VARCHAR2(240),
103 person_name_phonetic VARCHAR2(320),
104 person_first_name_phonetic VARCHAR2(60),
105 person_last_name_phonetic VARCHAR2(60),
106 middle_name_phonetic VARCHAR2(60),
107 tax_reference VARCHAR2(50),
108 jgzz_fiscal_code VARCHAR2(20),
109 person_iden_type VARCHAR2(30),
110 person_identifier VARCHAR2(60),
111 date_of_birth DATE,
112 place_of_birth VARCHAR2(60),
113 date_of_death DATE,
114 deceased_flag VARCHAR2(1),
115 gender VARCHAR2(30),
116 declared_ethnicity VARCHAR2(60),
117 marital_status VARCHAR2(30),
118 marital_status_effective_date DATE,
119 personal_income NUMBER,
120 head_of_household_flag VARCHAR2(1),
121 household_income NUMBER,
122 household_size NUMBER,
123 rent_own_ind VARCHAR2(30),
124 last_known_gps VARCHAR2(60),
125 content_source_type VARCHAR2(30):= HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE,
126 internal_flag VARCHAR2(2),
127 attribute_category VARCHAR2(30),
128 attribute1 VARCHAR2(150) ,
129 attribute2 VARCHAR2(150) ,
130 attribute3 VARCHAR2(150) ,
131 attribute4 VARCHAR2(150) ,
132 attribute5 VARCHAR2(150) ,
133 attribute6 VARCHAR2(150) ,
134 attribute7 VARCHAR2(150) ,
135 attribute8 VARCHAR2(150) ,
136 attribute9 VARCHAR2(150) ,
137 attribute10 VARCHAR2(150) ,
138 attribute11 VARCHAR2(150) ,
139 attribute12 VARCHAR2(150) ,
140 attribute13 VARCHAR2(150) ,
141 attribute14 VARCHAR2(150) ,
142 attribute15 VARCHAR2(150) ,
143 attribute16 VARCHAR2(150) ,
144 attribute17 VARCHAR2(150) ,
145 attribute18 VARCHAR2(150) ,
146 attribute19 VARCHAR2(150) ,
147 attribute20 VARCHAR2(150) ,
148 created_by_module VARCHAR2(150),
149 application_id NUMBER,
150 actual_content_source VARCHAR2(30) := HZ_PARTY_V2PUB.G_SST_SOURCE_TYPE,
151 party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE := HZ_PARTY_V2PUB.G_MISS_PARTY_REC
152 );
153
154 G_MISS_PERSON_REC PERSON_REC_TYPE;
155
156 /**** Commented out for 11.5.11 or R12 release.
157 TYPE partner_rec_type IS RECORD
158 (
159 partner_type VARCHAR2(500)
160 );
161
162 TYPE partner_types_tbl_type IS TABLE OF partner_rec_type INDEX BY BINARY_INTEGER;
163 g_miss_partner_types_tbl partner_types_tbl_type;
164 ******/
165
166 /*============================================================================
167 -- Start of comments
168 -- API name : Create_Partner
169 -- Type : Public.
170 -- Function : This API consolidates three operations in single API call. These
171 -- operations are as follows -
172 -- * Create a party
173 -- * Create a location
174 -- * Create a party site
175 -- for a given party. Once the party is successfully created then
176 -- it return the PARTY_ID of that organization.
177 --
178 -- Pre-reqs : None.
179 -- Parameters :
180 -- IN : p_api_version IN NUMBER Required
181 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
182 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
183 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
184 --
185 -- p_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE Required
186 -- p_location_rec IN PV_PARTNER_UTIL_PVT.LOCATION_REC_TYPE Required
187 -- p_party_site_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE Required
188 -- p_partner_types_tbl IN PV_ENTY_ATTR_VALUE_PUB.ATTR_VALUE_TBL_TYPE
189 -- p_vad_partner_id IN NUMBER
190 -- p_member_type IN VARCHAR2
191 -- p_global_partner_id IN NUMBER
192 --
193 -- OUT : x_return_status OUT VARCHAR2(1)
194 -- x_msg_count OUT NUMBER
195 -- x_msg_data OUT VARCHAR2(2000)
196 -- x_party_id OUT NUMBER
197 -- x_default_resp_id OUT NOCOPY NUMBER
198 -- x_resp_map_rule_id OUT NOCOPY NUMBER,
199 -- x_group_id OUT NOCOPY NUMBER
200 --
201 -- Version : Current version 1.0
202 -- Initial version 1.0
203 --
204 -- Notes : Note text
205 --
206 -- End of comments
207 ============================================================================*/
208 PROCEDURE Create_Partner (
209 p_api_version_number IN NUMBER,
210 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
211 p_commit IN VARCHAR2 := FND_API.G_FALSE,
212 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_msg_count OUT NOCOPY NUMBER,
215 x_msg_data OUT NOCOPY VARCHAR2,
216 p_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
217 p_location_rec IN PV_PARTNER_UTIL_PVT.LOCATION_REC_TYPE,
218 p_party_site_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
219 -- p_partner_types_tbl IN partner_types_tbl_type := g_miss_partner_types_tbl,
220 p_partner_types_tbl IN PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type,
221 p_vad_partner_id IN NUMBER,
222 p_member_type IN VARCHAR2 ,
223 p_global_partner_id IN NUMBER ,
224 x_party_id OUT NOCOPY NUMBER,
225 x_default_resp_id OUT NOCOPY NUMBER,
226 x_resp_map_rule_id OUT NOCOPY NUMBER,
227 x_group_id OUT NOCOPY NUMBER);
228
229 /*============================================================================
230 -- Start of comments
231 -- API name : Create_Party_Site_Uses
232 -- Type : Public.
233 -- Function : This api checks if the party already has party sites with
234 -- BILL_TO and SHIP_TO site use type. If it does not exists adds
235 -- these use types to the identifying address
236 --
237 -- Pre-reqs : None.
238 -- Parameters :
239 -- IN :
240 -- p_party_id IN NUMBER Required
241 --
242 -- OUT : x_return_status OUT VARCHAR2(1)
243 -- x_msg_count OUT NUMBER
244 -- x_msg_data OUT VARCHAR2(2000)
245 --
246 -- Version : Current version 1.0
247 -- Initial version 1.0
248 --
249 -- Notes : Note text
250 --
251 -- End of comments
252 ============================================================================*/
253
254 PROCEDURE Create_Party_Site_Uses (
255 p_party_id IN NUMBER
256 ,x_return_status OUT NOCOPY VARCHAR2
257 ,x_msg_data OUT NOCOPY VARCHAR2
258 ,x_msg_count OUT NOCOPY NUMBER
259 );
260 /*============================================================================
261 -- Start of comments
262 -- API name : Create_Relationship
263 -- Type : Public.
264 -- Function : This API consolidates few artner creation related operations in
265 -- single API call. These operations are as follows -
266 -- * Get the vendor Organization based on the default
267 -- responsibility of the logged in user
268 -- * Create a relationship record between Vendor Org and the Partner Org
269 -- * Create Resource for the partner
270 -- * Create role for the above resource
271 -- * Create Group for the above resource
272 -- * Create Partner Profile
273 -- * Get Sales Team for the given partner
274 -- * Add Sales Team for the given Partner
275 -- * Add Channel Manager in Channel Team from TAP
276 -- * Add Partner types to Attributes table
277 -- * If partner type is subsidiary, establish a relationship with the global partner
278 --
279 -- Pre-reqs : None.
280 -- Parameters :
281 -- IN : p_api_version IN NUMBER Required
282 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
283 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
284 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
285 --
286 -- p_party_id IN NUMBER Required
287 -- p_partner_types_tbl IN PV_ENTY_ATTR_VALUE_PUB.ATTR_VALUE_TBL_TYPE
288 -- p_vad_partner_id IN NUMBER
289 -- p_member_type IN VARCHAR2
290 -- p_global_partner_id IN NUMBER
291 --
292 -- OUT : x_return_status OUT VARCHAR2(1)
293 -- x_msg_count OUT NUMBER
294 -- x_msg_data OUT VARCHAR2(2000)
295 -- x_partner_id OUT NOCOPY NUMBER
296 -- x_default_resp_id OUT NOCOPY NUMBER
297 -- x_resp_map_rule_id OUT NOCOPY NUMBER,
298 -- x_group_id OUT NOCOPY NUMBER
299 --
300 -- Version : Current version 1.0
301 -- Initial version 1.0
302 --
303 -- Notes : Note text
304 --
305 -- End of comments
306 ============================================================================*/
307 PROCEDURE Create_Relationship(
308 p_api_version_number IN NUMBER
309 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
310 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
311 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
312 ,x_return_status OUT NOCOPY VARCHAR2
313 ,x_msg_data OUT NOCOPY VARCHAR2
314 ,x_msg_count OUT NOCOPY NUMBER
315 ,p_party_id IN NUMBER
316 -- ,p_partner_types_tbl IN partner_types_tbl_type := g_miss_partner_types_tbl
317 ,p_partner_types_tbl IN PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type
318 ,p_vad_partner_id IN NUMBER
319 ,p_member_type IN VARCHAR2
320 ,p_global_partner_id IN NUMBER
321 ,x_partner_id OUT NOCOPY NUMBER
322 ,x_default_resp_id OUT NOCOPY NUMBER
323 ,x_resp_map_rule_id OUT NOCOPY NUMBER
324 ,x_group_id OUT NOCOPY NUMBER
325 ) ;
326
327
328 /*============================================================================
329 -- Start of comments
330 -- API name : Do_Create_Relationship
331 -- Type : Public.
332 -- Function : This API consolidates few artner creation related operations in
333 -- single API call. These operations are as follows -
334 -- * Get the vendor Organization based on the default
335 -- responsibility of the logged in user
336 -- * Create a relationship record between Vendor Org and the Partner Org
337 -- * Create Resource for the partner
338 -- * Create role for the above resource
339 -- * Create Group for the above resource
340 -- * Create Partner Profile
341 -- * Get Sales Team for the given partner
342 -- * Add Sales Team for the given Partner
343 -- * Add Channel Manager in Channel Team from TAP
344 -- * Add Partner types to Attributes table
345 -- * If partner type is subsidiary, establish a relationship with the global partner
346 --
347 -- Pre-reqs : None.
348 -- Parameters :
349 -- IN : p_api_version IN NUMBER Required
350 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
351 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
352 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
353 --
354 -- p_party_id IN NUMBER Required
355 -- p_partner_types_tbl IN PV_ENTY_ATTR_VALUE_PUB.ATTR_VALUE_TBL_TYPE
356 -- p_vad_partner_id IN NUMBER
357 -- p_member_type IN VARCHAR2
358 -- p_global_partner_id IN NUMBER
359 --
360 -- OUT : x_return_status OUT VARCHAR2(1)
361 -- x_msg_count OUT NUMBER
362 -- x_msg_data OUT VARCHAR2(2000)
363 -- x_partner_id OUT NOCOPY NUMBER
364 -- x_default_resp_id OUT NOCOPY NUMBER
365 -- x_resp_map_rule_id OUT NOCOPY NUMBER,
366 -- x_group_id OUT NOCOPY NUMBER
367 --
368 -- Version : Current version 1.0
369 -- Initial version 1.0
370 --
371 -- Notes : Note text
372 --
373 -- End of comments
374 ============================================================================*/
375 PROCEDURE Do_Create_Relationship(
376 p_api_version_number IN NUMBER
377 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
378 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
379 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
380 ,x_return_status OUT NOCOPY VARCHAR2
381 ,x_msg_data OUT NOCOPY VARCHAR2
382 ,x_msg_count OUT NOCOPY NUMBER
383 ,p_party_id IN NUMBER
384 ,p_partner_types_tbl IN PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type
385 ,p_vad_partner_id IN NUMBER
386 ,p_member_type IN VARCHAR2
387 ,p_global_partner_id IN NUMBER
388 ,p_partner_qualifiers_tbl IN PV_TERR_ASSIGN_PUB.partner_qualifiers_tbl_type
389 ,x_partner_id OUT NOCOPY NUMBER
390 ,x_default_resp_id OUT NOCOPY NUMBER
391 ,x_resp_map_rule_id OUT NOCOPY NUMBER
392 ,x_group_id OUT NOCOPY NUMBER
393 ) ;
394
395
396 /*============================================================================
397 -- Start of comments
398 -- API name : Invite_Partner
399 -- Type : Public.
400 -- Function : This API consolidates few partner creation related operations in
401 --
402 -- Pre-reqs : None.
403 -- Parameters :
404 -- IN : p_api_version IN NUMBER Required
405 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
406 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
407 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
408 --
409 -- p_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE Required
410 -- p_location_rec IN PV_PARTNER_UTIL_PVT.LOCATION_REC_TYPE Required
411 -- p_party_site_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE Required
412 -- p_partner_types_tbl IN PV_ENTY_ATTR_VALUE_PUB.ATTR_VALUE_TBL_TYPE
413 -- p_vad_partner_id IN NUMBER
414 -- p_person_rec IN PV_PARTNER_UTIL_PVT.PERSON_REC_TYPE
415 -- p_phone_rec IN HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE
416 -- p_email_rec IN HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE
417 -- p_member_type IN VARCHAR2
418 -- p_global_partner_id IN NUMBER
419 --
420 -- OUT : x_return_status OUT VARCHAR2(1)
421 -- x_msg_count OUT NUMBER
422 -- x_msg_data OUT VARCHAR2(2000)
423 --
424 -- Version : Current version 1.0
425 -- Initial version 1.0
426 --
427 -- Notes : Note text
428 --
429 -- End of comments
430 ============================================================================*/
431 PROCEDURE Invite_Partner(
432 p_api_version_number IN NUMBER
433 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
434 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
435 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
436 ,x_return_status OUT NOCOPY VARCHAR2
437 ,x_msg_data OUT NOCOPY VARCHAR2
438 ,x_msg_count OUT NOCOPY NUMBER
439 /*
440 ,p_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
441 ,p_location_rec IN PV_PARTNER_UTIL_PVT.LOCATION_REC_TYPE
442 ,p_party_site_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
443 */
444
445 ,p_party_id IN Number
446 ,p_partner_qualifiers_tbl IN PV_TERR_ASSIGN_PUB.partner_qualifiers_tbl_type
447 -- ,p_partner_types_tbl IN partner_types_tbl_type := g_miss_partner_types_tbl
448 ,p_partner_types_tbl IN PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type
449 ,p_vad_partner_id IN NUMBER
450 ,p_person_rec IN PV_PARTNER_UTIL_PVT.PERSON_REC_TYPE
451 ,p_phone_rec IN HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE := HZ_CONTACT_POINT_v2PUB.g_miss_phone_rec
452 ,p_email_rec IN HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE := HZ_CONTACT_POINT_v2PUB.g_miss_email_rec
453 ,p_member_type IN VARCHAR2
454 ,p_global_partner_id IN NUMBER
455 ,x_partner_party_id OUT NOCOPY NUMBER
456 ,x_partner_id OUT NOCOPY NUMBER
457 ,x_cnt_party_id OUT NOCOPY NUMBER
458 ,x_cnt_partner_id OUT NOCOPY NUMBER
459 ,x_cnt_rel_start_date OUT NOCOPY DATE
460 ,x_default_resp_id OUT NOCOPY NUMBER
461 ,x_resp_map_rule_id OUT NOCOPY NUMBER
462 ,x_group_id OUT NOCOPY NUMBER ) ;
463
464
465 -- -------------------------------------------------------------------------------
466 -- FUNCTION: can_see_all_partners
467 --
468 -- Given a resource_id, the function returns
469 --
470 -- 0 - if the resource does not have any of the following permissions:
471 -- PV_UPDATE_ALL_PARTNERS, PV_VIEW_ALL_PARTNERS
472 --
473 -- 1 - if the resource has either or both or the permissions:
474 -- PV_UPDATE_ALL_PARTNERS, PV_VIEW_ALL_PARTNERS
475 --
476 -- -------------------------------------------------------------------------------
477 FUNCTION can_see_all_partners (
478 p_resource_id IN NUMBER
479 )
480 RETURN NUMBER;
481
482
483 END PV_PARTNER_UTIL_PVT;