[Home] [Help]
PACKAGE BODY: APPS.AS_ACCESSES_PKG
Source
1 PACKAGE BODY AS_ACCESSES_PKG as
2 /* $Header: asxacacb.pls 120.1 2005/12/18 23:26:16 subabu noship $ */
3
4 --
5 -- HISTORY
6 -- 30-JUN-94 J Sondergaard Created Territory_Access_Exists
7 -- Created Access_For_Person_Exists
8 -- 09-SEP-94 J Sondergaard Modified Access_For_Person_Exists
9 -- to Access Exists. Function now
10 -- takes more arguments
11 -- 12-OCT-94 J Sondergaard Access_Exists renamed to
12 -- Emp_Access_Exists
13 -- Created Ptr_Access_Exists
14 -- 13-OCT-94 J Sondergaard Added Lead_Id so Emp_Access_Exists
15 -- and Ptr_Access_Exists can be used in
16 -- Leads form also.
17 --
18 -- 16-JUN-95 J Kornberg Removed Function Territory_Access_Exists
19 --
20 -- 21-JUN-95 J Kornberg Added partner_customer_id and partner_address_id
21 -- to the access table handlers
22 -- 23-AUG-95 K Lee Changed due to ACCESS_TYPE becoming obsolete
23 -- in AS_ACCESSES
24 -- 12-JUL-96 J Kornberg Added salesforce_role and salesforce_relationship
25 -- column to table handlers
26 --
27
28 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
29 X_Access_Id IN OUT NOCOPY NUMBER,
30 X_Last_Update_Date DATE,
31 X_Last_Updated_By NUMBER,
32 X_Creation_Date DATE,
33 X_Created_By NUMBER,
34 X_Last_Update_Login NUMBER,
35 X_Access_Type VARCHAR2,
36 X_Freeze_Flag VARCHAR2,
37 X_Reassign_Flag VARCHAR2,
38 X_Team_Leader_Flag VARCHAR2,
39 X_Person_Id NUMBER,
40 X_Customer_Id NUMBER,
41 X_Address_Id NUMBER,
42 X_Salesforce_Id NUMBER,
43 X_Partner_Customer_Id NUMBER,
44 X_Partner_Address_Id NUMBER,
45 X_Created_Person_Id NUMBER,
46 X_Lead_Id NUMBER,
47 X_Freeze_Date DATE,
48 X_Reassign_Reason VARCHAR2,
49 x_reassign_request_date DATE,
50 x_reassign_requested_person_id NUMBER,
51 X_Attribute_Category VARCHAR2,
52 X_Attribute1 VARCHAR2,
53 X_Attribute2 VARCHAR2,
54 X_Attribute3 VARCHAR2,
55 X_Attribute4 VARCHAR2,
56 X_Attribute5 VARCHAR2,
57 X_Attribute6 VARCHAR2,
58 X_Attribute7 VARCHAR2,
59 X_Attribute8 VARCHAR2,
60 X_Attribute9 VARCHAR2,
61 X_Attribute10 VARCHAR2,
62 X_Attribute11 VARCHAR2,
63 X_Attribute12 VARCHAR2,
64 X_Attribute13 VARCHAR2,
65 X_Attribute14 VARCHAR2,
66 X_Attribute15 VARCHAR2,
67 X_Salesforce_Role_Code VARCHAR2,
68 X_Salesforce_Relationship_Code VARCHAR2,
69 X_Internal_update_access NUMBER ,
70 X_Sales_lead_id NUMBER ,
71 X_Sales_group_id NUMBER ,
72 X_Partner_Cont_Party_Id NUMBER,
73 X_owner_flag VARCHAR2,
74 X_created_by_tap_flag VARCHAR2,
75 X_prm_keep_flag VARCHAR2,
76 X_open_flag VARCHAR2,
77 X_lead_rank_score NUMBER,
78 X_object_creation_date DATE,
79 X_contributor_flag VARCHAR2 -- Added for ASNB
80
81 ) IS
82 CURSOR C IS SELECT rowid FROM as_accesses_all
83 WHERE access_id = X_Access_Id;
84 CURSOR C2 IS SELECT as_accesses_s.nextval FROM sys.dual;
85 BEGIN
86 if (X_Access_Id is NULL or x_access_id = fnd_api.g_miss_num) then
87 OPEN C2;
88 FETCH C2 INTO X_Access_Id;
89 CLOSE C2;
90 end if;
91 INSERT INTO as_accesses_all(
92 access_id,
93 last_update_date,
94 last_updated_by,
95 creation_date,
96 created_by,
97 last_update_login,
98 access_type,
99 freeze_flag,
100 reassign_flag,
101 team_leader_flag,
102 person_id,
103 customer_id,
104 address_id,
105 salesforce_id,
106 partner_customer_id,
107 partner_address_id,
108 created_person_id,
109 lead_id,
110 freeze_date,
111 reassign_reason,
112 reassign_request_date,
113 reassign_requested_person_id,
114 attribute_category,
115 attribute1,
116 attribute2,
117 attribute3,
118 attribute4,
119 attribute5,
120 attribute6,
121 attribute7,
122 attribute8,
123 attribute9,
124 attribute10,
125 attribute11,
126 attribute12,
127 attribute13,
128 attribute14,
129 attribute15,
130 salesforce_role_code,
131 salesforce_relationship_code,
132 internal_update_access,
133 sales_lead_id,
134 sales_group_id,
135 partner_cont_party_id,
136 owner_flag,
137 created_by_tap_flag,
138 prm_keep_flag,
139 open_flag,
140 lead_rank_score,
141 object_creation_date,
142 contributor_flag -- Added for ASNB
143 ) VALUES (
144 X_Access_Id,
145 X_Last_Update_Date,
146 X_Last_Updated_By,
147 X_Creation_Date,
148 X_Created_By,
149 X_Last_Update_Login,
150 decode(X_Access_Type,FND_API.G_MISS_CHAR,NULL,X_Access_Type),
151 decode(X_Freeze_Flag,FND_API.G_MISS_CHAR,'Y',X_Freeze_Flag),
152 decode(X_Reassign_Flag,FND_API.G_MISS_CHAR,'N',X_Reassign_Flag),
153 decode(X_Team_Leader_Flag,FND_API.G_MISS_CHAR,'N',X_Team_Leader_Flag),
154 decode(X_Person_Id, FND_API.G_MISS_NUM, NULL, X_person_id),
155 decode(X_Customer_Id,FND_API.G_MISS_NUM, NULL, X_customer_id),
156 decode(X_Address_Id,FND_API.G_MISS_NUM, NULL, X_address_id ),
157 decode(X_Salesforce_Id,FND_API.G_MISS_NUM, NULL, X_salesforce_id),
158 decode(X_Partner_Customer_Id,FND_API.G_MISS_NUM, NULL, X_partner_customer_id),
159 decode(X_Partner_Address_Id,FND_API.G_MISS_NUM, NULL, X_partner_address_id),
160 decode(X_Created_Person_Id,FND_API.G_MISS_NUM, NULL, X_created_person_id ),
161 decode(X_Lead_Id,FND_API.G_MISS_NUM, NULL, X_lead_id),
162 decode(X_Freeze_Date,FND_API.G_MISS_DATE, NULL,X_Freeze_Date),
163 decode(X_Reassign_Reason,FND_API.G_MISS_CHAR,NULL,X_Reassign_Reason),
164 decode(X_reassign_request_date,FND_API.G_MISS_DATE,
165 NULL,X_reassign_request_date),
166 decode(X_reassign_requested_person_id ,FND_API.G_MISS_NUM,
167 NULL,X_reassign_requested_person_id ),
168 decode(X_Attribute_Category,FND_API.G_MISS_CHAR,NULL,X_Attribute_Category),
169 decode(X_Attribute1,FND_API.G_MISS_CHAR,NULL,X_Attribute1),
170 decode(X_Attribute2,FND_API.G_MISS_CHAR,NULL,X_Attribute2),
171 decode(X_Attribute3,FND_API.G_MISS_CHAR,NULL,X_Attribute3),
172 decode(X_Attribute4,FND_API.G_MISS_CHAR,NULL,X_Attribute4),
173 decode(X_Attribute5,FND_API.G_MISS_CHAR,NULL,X_Attribute5),
174 decode(X_Attribute6,FND_API.G_MISS_CHAR,NULL,X_Attribute6),
175 decode(X_Attribute7,FND_API.G_MISS_CHAR,NULL,X_Attribute7),
176 decode(X_Attribute8,FND_API.G_MISS_CHAR,NULL,X_Attribute8),
177 decode(X_Attribute9,FND_API.G_MISS_CHAR,NULL,X_Attribute9),
178 decode(X_Attribute10,FND_API.G_MISS_CHAR,NULL,X_Attribute10),
179 decode(X_Attribute11,FND_API.G_MISS_CHAR,NULL,X_Attribute11),
180 decode(X_Attribute12,FND_API.G_MISS_CHAR,NULL,X_Attribute12),
181 decode(X_Attribute13,FND_API.G_MISS_CHAR,NULL,X_Attribute13),
182 decode(X_Attribute14,FND_API.G_MISS_CHAR,NULL,X_Attribute14),
183 decode(X_Attribute15,FND_API.G_MISS_CHAR,NULL,X_Attribute15),
184 decode(X_Salesforce_Role_Code,FND_API.G_MISS_CHAR,NULL,X_Salesforce_Role_Code),
185 decode(X_Salesforce_Relationship_Code,FND_API.G_MISS_CHAR,NULL,X_Salesforce_Relationship_Code),
186 decode(X_Internal_update_access,FND_API.G_MISS_NUM,NULL,X_Internal_update_access),
187 decode(X_Sales_lead_id,FND_API.G_MISS_NUM,NULL,X_Sales_lead_id),
188 decode(X_Sales_group_id,FND_API.G_MISS_NUM,NULL,X_Sales_group_id),
189 decode(X_Partner_Cont_Party_id,FND_API.G_MISS_NUM,NULL,X_Partner_Cont_Party_Id),
190 decode(X_owner_flag,FND_API.G_MISS_CHAR,'N',X_owner_flag),
191 decode(X_created_by_tap_flag,FND_API.G_MISS_CHAR,NULL,X_created_by_tap_flag),
192 decode(X_prm_keep_flag,FND_API.G_MISS_CHAR,NULL,X_prm_keep_flag),
193 decode(X_open_flag, FND_API.G_MISS_CHAR, 'N', X_open_flag),
194 decode(X_lead_rank_score, FND_API.G_MISS_NUM, NULL, X_lead_rank_score),
195 decode(X_object_creation_date, FND_API.G_MISS_DATE, TO_DATE(NULL), X_object_creation_date),
196 decode(X_contributor_flag, FND_API.G_MISS_CHAR, 'N', X_contributor_flag) -- Added for ASNB
197 );
198
199 OPEN C;
200 FETCH C INTO X_Rowid;
201 if (C%NOTFOUND) then
202 CLOSE C;
203 RAISE NO_DATA_FOUND;
204 end if;
205 CLOSE C;
206 END Insert_Row;
207
208 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
209 X_Access_Id NUMBER,
210 X_Access_Type VARCHAR2,
211 X_Freeze_Flag VARCHAR2,
212 X_Reassign_Flag VARCHAR2,
213 X_Team_Leader_Flag VARCHAR2,
214 X_Person_Id NUMBER,
215 X_Customer_Id NUMBER,
216 X_Address_Id NUMBER,
217 X_Salesforce_id NUMBER,
218 X_Partner_Customer_Id NUMBER,
219 X_Partner_Address_Id NUMBER,
220 X_Created_Person_Id NUMBER,
221 X_Lead_Id NUMBER,
222 X_Freeze_Date DATE,
223 X_Reassign_Reason VARCHAR2,
224 x_reassign_request_date DATE,
225 x_reassign_requested_person_id NUMBER,
226 X_Attribute_Category VARCHAR2,
227 X_Attribute1 VARCHAR2,
228 X_Attribute2 VARCHAR2,
229 X_Attribute3 VARCHAR2,
230 X_Attribute4 VARCHAR2,
231 X_Attribute5 VARCHAR2,
232 X_Attribute6 VARCHAR2,
233 X_Attribute7 VARCHAR2,
234 X_Attribute8 VARCHAR2,
235 X_Attribute9 VARCHAR2,
236 X_Attribute10 VARCHAR2,
237 X_Attribute11 VARCHAR2,
238 X_Attribute12 VARCHAR2,
239 X_Attribute13 VARCHAR2,
240 X_Attribute14 VARCHAR2,
241 X_Attribute15 VARCHAR2,
242 X_Salesforce_Role_Code VARCHAR2,
243 X_Salesforce_Relationship_Code VARCHAR2,
244 X_Sales_lead_id NUMBER ,
245 X_Sales_group_id NUMBER ,
246 X_Partner_Cont_Party_id NUMBER,
247 X_owner_flag VARCHAR2,
248 X_created_by_tap_flag VARCHAR2,
249 X_prm_keep_flag VARCHAR2,
250 X_contributor_flag VARCHAR2 -- Added for ASNB
251 ) IS
252 CURSOR C IS
253 SELECT *
254 FROM as_accesses_all
255 WHERE rowid = X_Rowid
256 FOR UPDATE of Access_Id NOWAIT;
257 Recinfo C%ROWTYPE;
258 BEGIN
259 OPEN C;
260 FETCH C INTO Recinfo;
261 if (C%NOTFOUND) then
262 CLOSE C;
263 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
264 APP_EXCEPTION.RAISE_EXCEPTION;
265 end if;
266 CLOSE C;
267 if (
268 ( (Recinfo.access_id = X_Access_Id)
269 OR ( (Recinfo.access_id IS NULL)
270 AND (X_Access_Id IS NULL)))
271 AND ( (Recinfo.access_type = X_Access_Type)
272 OR ( (Recinfo.access_type IS NULL)
273 AND (X_Access_Type IS NULL)))
274 AND ( (Recinfo.freeze_flag = X_Freeze_Flag)
275 OR ( (Recinfo.freeze_flag IS NULL)
276 AND (X_Freeze_Flag IS NULL)))
277 AND ( (Recinfo.reassign_flag = X_Reassign_Flag)
278 OR ( (Recinfo.reassign_flag IS NULL)
279 AND (X_Reassign_Flag IS NULL)))
280 AND ( (Recinfo.team_leader_flag = X_Team_Leader_Flag)
281 OR ( (Recinfo.team_leader_flag IS NULL)
282 AND (X_Team_Leader_Flag IS NULL)))
283 AND ( (Recinfo.person_id = X_Person_Id)
284 OR ( (Recinfo.person_id IS NULL)
285 AND (X_Person_Id IS NULL)))
286 AND ( (Recinfo.customer_id = X_Customer_Id)
287 OR ( (Recinfo.customer_id IS NULL)
288 AND (X_Customer_Id IS NULL)))
289 AND ( (Recinfo.address_id = X_Address_Id)
290 OR ( (Recinfo.address_id IS NULL)
291 AND (X_Address_Id IS NULL)))
292 AND ( (Recinfo.salesforce_id = X_Salesforce_Id)
293 OR ( (Recinfo.Salesforce_id IS NULL)
294 AND (X_Salesforce_Id IS NULL)))
295 AND ( (Recinfo.partner_customer_id = X_Partner_Customer_Id)
296 OR ( (Recinfo.partner_customer_id IS NULL)
297 AND (X_Partner_Customer_Id IS NULL)))
298 AND ( (Recinfo.partner_address_id = X_Partner_Address_Id)
299 OR ( (Recinfo.partner_address_id IS NULL)
300 AND (X_Partner_Address_Id IS NULL)))
301 AND ( (Recinfo.created_person_id = X_Created_Person_Id)
302 OR ( (Recinfo.created_person_id IS NULL)
303 AND (X_Created_Person_Id IS NULL)))
304 AND ( (Recinfo.lead_id = X_Lead_Id)
305 OR ( (Recinfo.lead_id IS NULL)
306 AND (X_Lead_Id IS NULL)))
307 AND ( (Recinfo.freeze_date = X_Freeze_Date)
308 OR ( (Recinfo.freeze_date IS NULL)
309 AND (X_Freeze_Date IS NULL)))
310 AND ( (Recinfo.reassign_reason = X_Reassign_Reason)
311 OR ( (Recinfo.reassign_reason IS NULL)
312 AND (X_Reassign_Reason IS NULL)))
313 AND ( (Recinfo.reassign_request_date = x_reassign_request_date)
314 OR ( (Recinfo.reassign_request_date IS NULL)
315 AND (x_reassign_request_date IS NULL)))
316 AND ( (Recinfo.reassign_requested_person_id = X_reassign_requested_person_id)
317 OR ( (Recinfo.reassign_requested_person_id IS NULL)
318 AND (X_reassign_requested_person_id IS NULL)))
319 AND ( (Recinfo.attribute_category = X_Attribute_Category)
320 OR ( (Recinfo.attribute_category IS NULL)
321 AND (X_Attribute_Category IS NULL)))
322 AND ( (Recinfo.attribute1 = X_Attribute1)
323 OR ( (Recinfo.attribute1 IS NULL)
324 AND (X_Attribute1 IS NULL)))
325 AND ( (Recinfo.attribute2 = X_Attribute2)
326 OR ( (Recinfo.attribute2 IS NULL)
330 AND (X_Attribute3 IS NULL)))
327 AND (X_Attribute2 IS NULL)))
328 AND ( (Recinfo.attribute3 = X_Attribute3)
329 OR ( (Recinfo.attribute3 IS NULL)
331 AND ( (Recinfo.attribute4 = X_Attribute4)
332 OR ( (Recinfo.attribute4 IS NULL)
333 AND (X_Attribute4 IS NULL)))
334 AND ( (Recinfo.attribute5 = X_Attribute5)
335 OR ( (Recinfo.attribute5 IS NULL)
336 AND (X_Attribute5 IS NULL)))
337 AND ( (Recinfo.attribute6 = X_Attribute6)
338 OR ( (Recinfo.attribute6 IS NULL)
339 AND (X_Attribute6 IS NULL)))
340 AND ( (Recinfo.attribute7 = X_Attribute7)
341 OR ( (Recinfo.attribute7 IS NULL)
342 AND (X_Attribute7 IS NULL)))
343 AND ( (Recinfo.attribute8 = X_Attribute8)
344 OR ( (Recinfo.attribute8 IS NULL)
345 AND (X_Attribute8 IS NULL)))
346 AND ( (Recinfo.attribute9 = X_Attribute9)
347 OR ( (Recinfo.attribute9 IS NULL)
348 AND (X_Attribute9 IS NULL)))
349 AND ( (Recinfo.attribute10 = X_Attribute10)
350 OR ( (Recinfo.attribute10 IS NULL)
351 AND (X_Attribute10 IS NULL)))
352 AND ( (Recinfo.attribute11 = X_Attribute11)
353 OR ( (Recinfo.attribute11 IS NULL)
354 AND (X_Attribute11 IS NULL)))
355 AND ( (Recinfo.attribute12 = X_Attribute12)
356 OR ( (Recinfo.attribute12 IS NULL)
357 AND (X_Attribute12 IS NULL)))
358 AND ( (Recinfo.attribute13 = X_Attribute13)
359 OR ( (Recinfo.attribute13 IS NULL)
360 AND (X_Attribute13 IS NULL)))
361 AND ( (Recinfo.attribute14 = X_Attribute14)
362 OR ( (Recinfo.attribute14 IS NULL)
363 AND (X_Attribute14 IS NULL)))
364 AND ( (Recinfo.attribute15 = X_Attribute15)
365 OR ( (Recinfo.attribute15 IS NULL)
366 AND (X_Attribute15 IS NULL)))
367 AND ( (Recinfo.salesforce_role_code = X_Salesforce_Role_Code)
368 OR ( (Recinfo.salesforce_role_code IS NULL)
369 AND (X_Salesforce_Role_Code IS NULL)))
370 AND ( (Recinfo.salesforce_relationship_code = X_Salesforce_Relationship_Code)
371 OR ( (Recinfo.salesforce_relationship_code IS NULL)
372 AND (X_Salesforce_Relationship_Code IS NULL)))
373 AND ( (Recinfo.sales_lead_id = X_Sales_lead_id)
374 OR ( (Recinfo.Sales_lead_id IS NULL)
375 AND (X_Sales_lead_id IS NULL)))
376 AND ( (Recinfo.sales_group_id = X_Sales_group_id)
377 OR ( (Recinfo.Sales_group_id IS NULL)
378 AND (X_Sales_group_id IS NULL)))
379 AND ( (Recinfo.partner_cont_party_id = X_Partner_Cont_Party_Id)
380 OR ( (Recinfo.partner_cont_party_id IS NULL)
381 AND (X_Partner_Cont_Party_Id IS NULL)))
382 AND ( (Recinfo.owner_flag = X_owner_flag)
383 OR ( (Recinfo.owner_flag IS NULL)
384 AND (X_owner_flag IS NULL)))
385 AND ( (Recinfo.created_by_tap_flag =X_created_by_tap_flag )
386 OR ( (Recinfo.created_by_tap_flag IS NULL)
387 AND (X_created_by_tap_flag IS NULL)))
388 AND ( (Recinfo.prm_keep_flag = X_prm_keep_flag)
389 OR ( (Recinfo.prm_keep_flag IS NULL)
390 AND (X_prm_keep_flag IS NULL)))
391 -- Added for ASNB
392 AND ( (Recinfo.contributor_flag = X_contributor_flag)
393 OR ( (Recinfo.contributor_flag IS NULL)
394 AND (X_contributor_flag IS NULL)))
395 ) then
396 return;
397 else
398 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
399 APP_EXCEPTION.RAISE_EXCEPTION;
400 end if;
401 END Lock_Row;
402
403 PROCEDURE Update_Row(X_Rowid VARCHAR2,
404 X_Access_Id NUMBER,
405 X_Last_Update_Date DATE,
406 X_Last_Updated_By NUMBER,
407 X_Last_Update_Login NUMBER,
408 X_Access_Type VARCHAR2,
409 X_Freeze_Flag VARCHAR2,
410 X_Reassign_Flag VARCHAR2,
411 X_Team_Leader_Flag VARCHAR2,
412 X_Person_Id NUMBER,
413 X_Customer_Id NUMBER,
414 X_Address_Id NUMBER,
415 X_Salesforce_Id NUMBER,
416 X_Partner_Customer_Id NUMBER,
417 X_Partner_Address_Id NUMBER,
418 X_Created_Person_Id NUMBER,
419 X_Lead_Id NUMBER,
420 X_Freeze_Date DATE,
421 X_Reassign_Reason VARCHAR2,
422 x_reassign_request_date DATE,
423 x_reassign_requested_person_id NUMBER,
424 X_Attribute_Category VARCHAR2,
425 X_Attribute1 VARCHAR2,
426 X_Attribute2 VARCHAR2,
427 X_Attribute3 VARCHAR2,
428 X_Attribute4 VARCHAR2,
432 X_Attribute8 VARCHAR2,
429 X_Attribute5 VARCHAR2,
430 X_Attribute6 VARCHAR2,
431 X_Attribute7 VARCHAR2,
433 X_Attribute9 VARCHAR2,
434 X_Attribute10 VARCHAR2,
435 X_Attribute11 VARCHAR2,
436 X_Attribute12 VARCHAR2,
437 X_Attribute13 VARCHAR2,
438 X_Attribute14 VARCHAR2,
439 X_Attribute15 VARCHAR2,
440 X_Salesforce_Role_Code VARCHAR2,
441 X_Salesforce_Relationship_Code VARCHAR2,
442 X_Internal_update_access NUMBER ,
443 X_Sales_lead_id NUMBER ,
444 X_Sales_group_id NUMBER ,
445 X_Partner_Cont_Party_Id NUMBER ,
446 X_owner_flag VARCHAR2,
447 X_created_by_tap_flag VARCHAR2,
448 X_prm_keep_flag VARCHAR2,
449 X_contributor_flag VARCHAR2 -- Added for ASNB
450 ) IS
451 BEGIN
452 UPDATE as_accesses_all
453 SET object_version_number = nvl(object_version_number,0) + 1,
454 access_id = X_Access_Id,
455 last_update_date = X_Last_Update_Date,
456 last_updated_by = X_Last_Updated_By,
457 last_update_login = X_Last_Update_Login,
458 access_type = decode(X_Access_Type,FND_API.G_MISS_CHAR,access_type,X_Access_Type),
459 freeze_flag = decode(X_Freeze_Flag,FND_API.G_MISS_CHAR,freeze_flag,X_Freeze_Flag),
460 reassign_flag = decode(X_Reassign_Flag,FND_API.G_MISS_CHAR,reassign_flag,X_Reassign_Flag),
461 team_leader_flag = decode(X_Team_Leader_Flag,FND_API.G_MISS_CHAR,team_leader_flag,X_Team_Leader_Flag),
462 person_id = decode(X_Person_Id,FND_API.G_MISS_NUM, person_id,X_Person_Id ),
463 customer_id = decode(X_Customer_Id,FND_API.G_MISS_NUM,customer_id,X_Customer_Id),
464 address_id = decode(X_Address_Id,FND_API.G_MISS_NUM,address_id,X_Address_Id ),
465 salesforce_id = decode(X_Salesforce_Id,FND_API.G_MISS_NUM,salesforce_id,X_Salesforce_Id),
466 partner_customer_id =decode(X_Partner_Customer_Id,FND_API.G_MISS_NUM,partner_customer_id,X_Partner_Customer_Id),
467 partner_address_id = decode(X_Partner_Address_Id,FND_API.G_MISS_NUM,partner_address_id,X_Partner_Address_Id),
468 created_person_id = decode(X_Created_Person_Id,FND_API.G_MISS_NUM,created_person_id,X_Created_Person_Id ),
469 lead_id = decode(X_Lead_Id,FND_API.G_MISS_NUM,lead_id,X_Lead_Id ),
470 freeze_date = decode(X_Freeze_Date,FND_API.G_MISS_DATE, freeze_date,X_Freeze_Date),
471 reassign_reason = decode(X_Reassign_Reason,FND_API.G_MISS_CHAR,reassign_reason,X_Reassign_Reason),
472 reassign_request_date = decode(X_reassign_request_date,FND_API.G_MISS_DATE,
473 reassign_request_date,X_reassign_request_date),
474 reassign_requested_person_id = decode(X_reassign_requested_person_id ,FND_API.G_MISS_NUM,
475 reassign_requested_person_id ,X_reassign_requested_person_id ),
476 attribute_category = decode(X_Attribute_Category,FND_API.G_MISS_CHAR,attribute_category,X_Attribute_Category),
477 attribute1 = decode(X_Attribute1,FND_API.G_MISS_CHAR,attribute1,X_Attribute1),
478 attribute2 = decode(X_Attribute2,FND_API.G_MISS_CHAR,attribute2,X_Attribute2),
479 attribute3 = decode(X_Attribute3,FND_API.G_MISS_CHAR,attribute3,X_Attribute3),
480 attribute4 = decode(X_Attribute4,FND_API.G_MISS_CHAR,attribute4,X_Attribute4),
481 attribute5 = decode(X_Attribute5,FND_API.G_MISS_CHAR,attribute5,X_Attribute5),
482 attribute6 = decode(X_Attribute6,FND_API.G_MISS_CHAR,attribute6,X_Attribute6),
483 attribute7 = decode(X_Attribute7,FND_API.G_MISS_CHAR,attribute7,X_Attribute7),
484 attribute8 = decode(X_Attribute8,FND_API.G_MISS_CHAR,attribute8,X_Attribute8),
485 attribute9 = decode(X_Attribute9,FND_API.G_MISS_CHAR,attribute9,X_Attribute9),
486 attribute10 = decode(X_Attribute10,FND_API.G_MISS_CHAR,attribute10,X_Attribute10),
487 attribute11 = decode(X_Attribute11,FND_API.G_MISS_CHAR,attribute11,X_Attribute11),
488 attribute12 = decode(X_Attribute12,FND_API.G_MISS_CHAR,attribute12,X_Attribute12),
489 attribute13 = decode(X_Attribute13,FND_API.G_MISS_CHAR,attribute13,X_Attribute13),
490 attribute14 = decode(X_Attribute14,FND_API.G_MISS_CHAR,attribute14,X_Attribute14),
491 attribute15 = decode(X_Attribute15,FND_API.G_MISS_CHAR,attribute15,X_Attribute15),
492 salesforce_role_code = decode(X_Salesforce_Role_Code,FND_API.G_MISS_CHAR,salesforce_role_code,X_Salesforce_Role_Code),
493 salesforce_relationship_code = decode(X_Salesforce_Relationship_Code,FND_API.G_MISS_CHAR,salesforce_relationship_code,X_Salesforce_Relationship_Code),
494 internal_update_access = decode(X_Internal_update_access,FND_API.G_MISS_NUM,internal_update_access,X_Internal_update_access),
495 sales_lead_id = decode(X_Sales_lead_id,FND_API.G_MISS_NUM,sales_lead_id,X_Sales_lead_id),
496 sales_group_id = decode(X_Sales_group_id,FND_API.G_MISS_NUM,sales_group_id,X_Sales_group_id),
500 prm_keep_flag = decode(X_prm_keep_flag,FND_API.G_MISS_CHAR,prm_keep_flag,X_prm_keep_flag),
497 partner_cont_party_id =decode(X_Partner_Cont_Party_id,FND_API.G_MISS_NUM,partner_cont_party_id,X_Partner_Cont_Party_Id),
498 owner_flag = decode(X_owner_flag,FND_API.G_MISS_CHAR,owner_flag,X_owner_flag),
499 created_by_tap_flag = decode(X_created_by_tap_flag,FND_API.G_MISS_CHAR,created_by_tap_flag,X_created_by_tap_flag),
501 contributor_flag = decode(X_contributor_flag,FND_API.G_MISS_CHAR,contributor_flag,X_contributor_flag) -- Added for ASNB
502 WHERE rowid = X_rowid;
503
504 if (SQL%NOTFOUND) then
505 RAISE NO_DATA_FOUND;
506 end if;
507 END Update_Row;
508
509 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
510 BEGIN
511 DELETE FROM as_accesses_all
512 WHERE rowid = X_Rowid;
513
514 if (SQL%NOTFOUND) then
515 RAISE NO_DATA_FOUND;
516 end if;
517 END Delete_Row;
518
519 --
520 -- NAME
521 -- Emp_Access_Exists
522 --
523 -- ARGUMENTS
524 -- Access_Id
525 -- Customer_Id
526 -- Address_Id
527 -- Person_Id
528 -- Salesforce_Id
529 -- Lead_Id
530 -- Access_Type
531 -- HISTORY
532 -- 30-JUN-94 J Sondergaard Created
533 -- 09-SEP-94 J Sondergaard Now called Access_Exists
534 -- More arguments added after
535 -- as_salesforce got involved
536 -- 12-OCT-94 J Sondergaard Renamed to Emp_Access_Exists
537 -- Partner columns removed, and
538 -- access_type added
539 -- 13-OCT-94 J Sondergaard Added Lead_Id
540 -- 23-AUG-95 K Lee removed ACCESS_TYPE clause
541 --
542 FUNCTION Emp_Access_Exists(X_Access_Id NUMBER,
543 X_Customer_Id NUMBER,
544 X_Address_Id NUMBER,
545 X_Person_Id NUMBER,
546 X_Salesforce_Id NUMBER,
547 X_Lead_Id NUMBER,
548 X_Access_Type VARCHAR2) RETURN BOOLEAN IS
549 X_Acc_Id NUMBER;
550 BEGIN
551 select access_id
552 into X_Acc_Id
553 from as_accesses_all
554 where customer_id = X_Customer_Id
555 -- and address_id = X_Address_Id
556 and salesforce_id = X_Salesforce_Id
557 and person_id = X_Person_Id
558 and ( (lead_id = X_Lead_Id) or
559 (lead_id is null and X_Lead_Id is null)
560 )
561 and ( (access_id <> X_Access_Id) or
562 (X_Access_Id is NULL) );
563 return TRUE;
564 EXCEPTION
565 when NO_DATA_FOUND then return FALSE;
566 when TOO_MANY_ROWS then return TRUE;
567 END;
568
569 -- NAME
570 -- Emp_Access_Exists
571 -- (Same as the other function just with an extra out parameter (X_Team_Leader_Flag).
572 -- This returns the team leader flag if access row is found else 'N'.
573 -- HISTORY
574 -- 12-SEP-96 M Chatterjee Created
575 --
576 FUNCTION Emp_Access_Exists(X_Access_Id NUMBER,
577 X_Customer_Id NUMBER,
578 X_Address_Id NUMBER,
579 X_Person_Id NUMBER,
580 X_Salesforce_Id NUMBER,
581 X_Lead_Id NUMBER,
582 X_Access_Type VARCHAR2,
583 X_Team_Leader_FLag OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
584 cursor Get_Access_Row (x_customer_id number, x_address_id number, x_salesforce_id number,
585 x_person_id number, x_lead_id number) IS
586 select Team_Leader_Flag
587 from as_accesses_all
588 where customer_id = X_Customer_Id
589 -- and address_id = X_Address_Id
590 and salesforce_id = X_Salesforce_Id
591 and person_id = X_Person_Id
592 and ( (lead_id = X_Lead_Id) or
593 (lead_id is null and X_Lead_Id is null)
594 );
595 T_Team_Leader_Flag Varchar2(1);
596 BEGIN
597 X_Team_Leader_Flag := 'N';
598 Open Get_Access_Row (x_customer_id, x_address_id, x_salesforce_id, x_person_id, x_lead_id);
599 Fetch Get_Access_Row Into T_Team_Leader_Flag;
600 If (Get_Access_Row%NOTFOUND)
601 Then
602 Close Get_Access_Row;
603 return FALSE;
604 End If;
605 Close Get_Access_Row;
606 X_Team_Leader_Flag := Nvl(T_Team_Leader_Flag,'N');
607 return TRUE;
608 EXCEPTION
609 when NO_DATA_FOUND then return FALSE;
610 when TOO_MANY_ROWS then return TRUE;
611 END Emp_Access_Exists;
612
613
614 --
615 -- NAME
616 -- Ptr_Access_Exists
617 --
618 -- ARGUMENTS
619 -- Access_Id
620 -- Customer_Id
621 -- Address_Id
622 -- Partner_Customer_Id
623 -- Partner_Address_Id
624 -- Salesforce_Id
625 -- Lead_Id
626 -- Access_Type
627 -- HISTORY
628 -- 12-OCT-94 J Sondergaard Created
629 -- 13-OCT-94 J Sondergaard Added Lead_Id
630 -- 23-AUG-95 K Lee removed ACCESS_TYPE clause
631 --
632 FUNCTION Ptr_Access_Exists(X_Access_Id NUMBER,
633 X_Customer_Id NUMBER,
634 X_Address_Id NUMBER,
635 X_Partner_Customer_Id NUMBER,
636 X_Partner_Address_Id NUMBER,
637 X_Salesforce_Id NUMBER,
638 X_Lead_Id NUMBER,
642 select access_id
639 X_Access_Type VARCHAR2) RETURN BOOLEAN IS
640 X_Acc_Id NUMBER;
641 BEGIN
643 into X_Acc_Id
644 from as_accesses_all
645 where customer_id = X_Customer_Id
646 -- and address_id = X_Address_Id
647 and salesforce_id = X_Salesforce_Id
648 and partner_customer_id = X_Partner_Customer_Id
649 -- and partner_address_id = X_Partner_Address_Id
650 and ( (lead_id = X_Lead_Id) or
651 (lead_id is null and X_Lead_Id is null)
652 )
653 and ( (access_id <> X_Access_Id) or
654 (X_Access_Id is NULL) );
655 return TRUE;
656 EXCEPTION
657 when NO_DATA_FOUND then return FALSE;
658 when TOO_MANY_ROWS then return TRUE;
659 END;
660
661
662 END AS_ACCESSES_PKG;