1 PACKAGE BODY AST_ROUTING_PUB AS
2 /* $Header: asttmrtb.pls 120.2 2006/03/25 05:53:03 savadhan noship $ */
3
4 PROCEDURE getResourcesForParty (
5 p_party_id IN NUMBER,
6 p_resources OUT NOCOPY resource_access_tbl_type) is
7
8 cursor get_resources_for_party (p_cust_id NUMBER) is
9 select distinct salesforce_id
10 from as_accesses_all
11 where lead_id is null
12 and sales_lead_id is null
13 and team_leader_flag = 'Y'
14 and customer_id = p_cust_id;
15
16 cursor get_partytype (p_cust_id NUMBER) is
17 select party_type
18 from hz_parties
19 where party_id = p_cust_id;
20
21 cursor get_subject_object (p_rel_party_id NUMBER) is
22 select subject_id, object_id
23 from hz_relationships
24 where party_id = p_rel_party_id;
25
26 cursor get_resources_for_rel (p_subject_id NUMBER, p_object_id NUMBER) is
27 select distinct salesforce_id
28 from as_accesses_all
29 where lead_id is null
30 and sales_lead_id is null
31 and team_leader_flag = 'Y'
32 and customer_id in (p_subject_id, p_object_id);
33
34 l_party_type VARCHAR2(30);
35 l_subject_id NUMBER;
36 l_object_id NUMBER;
37
38 l_index BINARY_INTEGER := 0;
39 l_resource_rec resource_access_rec_type;
40
41 begin
42 open get_partytype(p_party_id);
43 fetch get_partytype into l_party_type;
44 close get_partytype;
45
46 if l_party_type = 'PARTY_RELATIONSHIP' then
47 open get_subject_object(p_party_id);
48 fetch get_subject_object into l_subject_id, l_object_id;
49 close get_subject_object;
50
51 -- all resources with update access on the sales team of either
52 -- the subject or the object of the relationship
53 for res_rec in get_resources_for_rel(l_subject_id,l_object_id) loop
54 l_index := l_index + 1;
55 l_resource_rec.resource_id := res_rec.salesforce_id;
56 p_resources(l_index) := l_resource_rec;
57 end loop;
58 else
59 -- all resources with update access on the sales team of the party
60 for res_rec in get_resources_for_party(p_party_id) loop
61 l_index := l_index + 1;
62 l_resource_rec.resource_id := res_rec.salesforce_id;
63 p_resources(l_index) := l_resource_rec;
64 end loop;
65 end if;
66
67 exception
68 when others then
69 return;
70 end getResourcesForParty;
71
72 PROCEDURE getResourcesForSourceCode (
73 p_source_code IN VARCHAR2,
74 p_resources OUT NOCOPY resource_access_tbl_type) is
75
76 cursor get_resources_for_source_code (p_source_code VARCHAR2) is
77 select distinct arc.resource_id
78 from ast_rs_campaigns arc, ams_campaign_schedules_b cs
79 where arc.campaign_id = cs.schedule_id
80 and cs.source_code = p_source_code
81 and arc.status = 'A'
82 and arc.enabled_flag = 'Y'
83 union
84 select distinct gm.resource_id
85 from ast_grp_campaigns agc, ams_campaign_schedules_b cs, jtf_rs_group_members gm, jtf_rs_groups_denorm gd
86 where agc.campaign_id = cs.schedule_id
87 and cs.source_code = p_source_code
88 and agc.enabled_flag = 'Y'
89 and agc.group_id = gd.parent_group_id
90 and gd.group_id = gm.group_id;
91
92 l_index BINARY_INTEGER := 0;
93 l_resource_rec resource_access_rec_type;
94
95 begin
96
97 for res_rec in get_resources_for_source_code(p_source_code) loop
98 l_index := l_index + 1;
99 l_resource_rec.resource_id := res_rec.resource_id;
100 p_resources(l_index) := l_resource_rec;
101 end loop;
102
103 exception
104 when others then
105 return;
106 end getResourcesForSourceCode;
107
108 FUNCTION getPartyfromANI(p_object_value IN VARCHAR2) return NUMBER is
109 l_filtered_ANI VARCHAR2(60);
110 l_transposed_ANI VARCHAR2(60);
111 l_partyID NUMBER;
112 l_more_partyID NUMBER;
113
114 cursor getPartyIDfromANI (p_ANI VARCHAR2) is
115 select owner_table_id
116 from hz_contact_points
117 where transposed_phone_number like p_ANI
118 and owner_table_name = 'HZ_PARTIES'
119 and status = 'A';
120
121 begin
122 l_filtered_ANI := translate(p_object_value,
123 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()- .+''~`\/@#$^&*_,|}{[]?<>=";:%',
124 '0123456789');
125 if l_filtered_ANI is null or l_filtered_ANI = '' then
126 return G_NO_PARTY;
127 end if;
128
129 l_transposed_ANI := null;
130 for c in reverse 1..length(l_filtered_ANI) loop
131 l_transposed_ANI := l_transposed_ANI || substr(l_filtered_ANI, c, 1);
132 end loop;
133 l_transposed_ANI := RTRIM(l_transposed_ANI, 0) || '%'; -- Added for bug#4043234
134
135 open getPartyIDfromANI(l_transposed_ANI);
136 fetch getPartyIDfromANI into l_partyID;
137 if getPartyIDfromANI%FOUND then
138 fetch getPartyIDfromANI into l_more_partyID;
139 if getPartyIDfromANI%FOUND then
140 l_partyID := G_MULTIPLE_PARTY;
141 end if;
142 else
143 l_partyID := G_NO_PARTY;
144 end if;
145 close getPartyIDfromANI;
146
147 return l_partyID;
148 end getPartyfromANI;
149
150 FUNCTION getPartyfromPartyID(p_object_value IN VARCHAR2) return NUMBER is
151 l_partyID NUMBER;
152 l_more_partyID NUMBER;
153 l_object_value NUMBER;
154
155 cursor getPartyIDfromPartyID (p_PartyID NUMBER) is
156 select party_id
157 from hz_parties
158 where party_id = p_PartyID;
159
160 begin
161 if p_object_value is null or p_object_value = '' then
162 return G_NO_PARTY;
163 end if;
164
165 begin
166 l_object_value := to_number(p_object_value);
167 exception
168 when OTHERS then
169 return G_NO_PARTY;
170 end;
171
172 open getPartyIDfromPartyID(l_object_value);
173 fetch getPartyIDfromPartyID into l_partyID;
174 if getPartyIDfromPartyID%FOUND then
175 fetch getPartyIDfromPartyID into l_more_partyID;
176 if getPartyIDfromPartyID%FOUND then
177 l_partyID := G_MULTIPLE_PARTY;
178 end if;
179 else
180 l_partyID := G_NO_PARTY;
181 end if;
182 close getPartyIDfromPartyID;
183
184 return l_partyID;
185 end getPartyfromPartyID;
186
187 FUNCTION getPartyfromPartyNum(p_object_value IN VARCHAR2) return NUMBER is
188 l_partyID NUMBER;
189 l_more_partyID NUMBER;
190
191 cursor getPartyIDfromPartyNum (p_PartyNum VARCHAR2) is
192 select party_id
193 from hz_parties
194 where party_number = p_PartyNum;
195
196 begin
197 if p_object_value is null or p_object_value = '' then
198 return G_NO_PARTY;
199 end if;
200
201 open getPartyIDfromPartyNum(p_object_value);
202 fetch getPartyIDfromPartyNum into l_partyID;
203 if getPartyIDfromPartyNum%FOUND then
204 fetch getPartyIDfromPartyNum into l_more_partyID;
205 if getPartyIDfromPartyNum%FOUND then
206 l_partyID := G_MULTIPLE_PARTY;
207 end if;
208 else
209 l_partyID := G_NO_PARTY;
210 end if;
211 close getPartyIDfromPartyNum;
212
213 return l_partyID;
214 end getPartyfromPartyNum;
215
216 FUNCTION getPartyfromQuoteNum(p_object_value IN VARCHAR2) return NUMBER is
217 l_quote_number NUMBER;
218 l_partyID NUMBER;
219 l_more_partyID NUMBER;
220
221 cursor getPartyIDfromQuoteNum (p_QuoteNum NUMBER) is
222 select party_id
223 from aso_quote_headers_all
224 where quote_number = p_QuoteNum
225 ;
226 -- commented out as this column is introduced only in ASO.J
227 --and max_version_flag = 'Y';
228
229 begin
230 if p_object_value is null or p_object_value = '' then
231 return G_NO_PARTY;
232 end if;
233
234 begin
235 l_quote_number := to_number(p_object_value);
236 exception
237 when OTHERS then
238 return G_NO_PARTY;
239 end;
240
241 open getPartyIDfromQuoteNum(l_quote_number);
242 fetch getPartyIDfromQuoteNum into l_partyID;
243 if getPartyIDfromQuoteNum%FOUND then
244 fetch getPartyIDfromQuoteNum into l_more_partyID;
245 if getPartyIDfromQuoteNum%FOUND then
246 l_partyID := G_MULTIPLE_PARTY;
247 end if;
248 else
249 l_partyID := G_NO_PARTY;
250 end if;
251 close getPartyIDfromQuoteNum;
252
253 return l_partyID;
254 end getPartyfromQuoteNum;
255
256 FUNCTION getPartyfromOrderNum(p_object_value IN VARCHAR2) return NUMBER is
257 l_order_number NUMBER;
258 l_partyID NUMBER;
259 l_more_partyID NUMBER;
260
261 cursor getPartyIDfromOrderNum (p_OrderNum NUMBER) is
262 select a.party_id
263 from oe_order_headers_all o, hz_cust_accounts a
264 where o.order_number = p_OrderNum
265 and a.cust_account_id = o.sold_to_org_id;
266
267 begin
268 if p_object_value is null or p_object_value = '' then
269 return G_NO_PARTY;
270 end if;
271
272 begin
273 l_order_number := to_number(p_object_value);
274 exception
275 when OTHERS then
276 return G_NO_PARTY;
277 end;
278
279 open getPartyIDfromOrderNum(l_order_number);
280 fetch getPartyIDfromOrderNum into l_partyID;
281 if getPartyIDfromOrderNum%FOUND then
282 fetch getPartyIDfromOrderNum into l_more_partyID;
283 if getPartyIDfromOrderNum%FOUND then
284 l_partyID := G_MULTIPLE_PARTY;
285 end if;
286 else
287 l_partyID := G_NO_PARTY;
288 end if;
289 close getPartyIDfromOrderNum;
290
291 return l_partyID;
292 end getPartyfromOrderNum;
293
294 FUNCTION getPartyfromCollRequest(p_object_value IN VARCHAR2) return NUMBER is
295 l_coll_req NUMBER;
296 l_partyID NUMBER;
297 l_more_partyID NUMBER;
298
299 cursor getPartyIDfromCollRequest (p_CollRequest NUMBER) is
300 select nvl(car.party_id, ca.party_id)
301 -- from ams_request_history
302 -- where order_id = p_CollRequest;
303 -- commented out lines above as table not available yet
304 -- from aso_quote_headers_all
305 -- where quote_number = p_CollRequest;
306 -- 01/13/2003 - not using AMS or ASO tables, going straight to OE tables
307 from oe_order_headers_all o, hz_cust_account_roles car, hz_cust_accounts ca
308 where o.order_number = p_CollRequest
309 and o.sold_to_contact_id = car.cust_account_role_id(+)
310 and o.sold_to_org_id = ca.cust_account_id;
311
312 begin
313 if p_object_value is null or p_object_value = '' then
314 return G_NO_PARTY;
315 end if;
316
317 begin
318 l_coll_req := to_number(p_object_value);
319 exception
320 when OTHERS then
321 return G_NO_PARTY;
322 end;
323
324 open getPartyIDfromCollRequest(l_coll_req);
325 fetch getPartyIDfromCollRequest into l_partyID;
326 if getPartyIDfromCollRequest%FOUND then
327 fetch getPartyIDfromCollRequest into l_more_partyID;
328 if getPartyIDfromCollRequest%FOUND then
329 l_partyID := G_MULTIPLE_PARTY;
330 end if;
331 else
332 l_partyID := G_NO_PARTY;
333 end if;
334 close getPartyIDfromCollRequest;
335
336 return l_partyID;
337 end getPartyfromCollRequest;
338
339 FUNCTION getPartyfromAccountNum(p_object_value IN VARCHAR2) return NUMBER is
340 l_partyID NUMBER;
341 l_custAccountID NUMBER;
342 l_more_partyID NUMBER;
343
344 cursor getPartyIDfromAccountNum (p_AccountNum VARCHAR2) is
345 select party_id, cust_account_id
346 from hz_cust_accounts
347 where account_number = p_AccountNum;
348
349 -- kmahajan 03/26/03 - changed cursor to fix bug 2872318
350 cursor getAccountRoles (p_AccountID NUMBER) is
351 select cust_account_id
352 from hz_cust_account_roles
353 where cust_account_id = p_AccountID;
354
355 begin
356 if p_object_value is null or p_object_value = '' then
357 return G_NO_PARTY;
358 end if;
359
360 open getPartyIDfromAccountNum(p_object_value);
361 fetch getPartyIDfromAccountNum into l_partyID, l_custAccountID;
362 if getPartyIDfromAccountNum%FOUND then
363 fetch getPartyIDfromAccountNum into l_more_partyID, l_custAccountID;
364 if getPartyIDfromAccountNum%FOUND then
365 l_partyID := G_MULTIPLE_PARTY;
366 l_custAccountID := null;
367 end if;
368 else
369 l_partyID := G_NO_PARTY;
370 l_custAccountID := null;
371 end if;
372 close getPartyIDfromAccountNum;
373
374 if l_custAccountID is not null then
375 open getAccountRoles(l_custAccountID);
376 fetch getAccountRoles into l_custAccountID;
377 if getAccountRoles%FOUND then
378 l_partyID := G_MULTIPLE_PARTY;
379 end if;
380 end if;
381
382 return l_partyID;
383 end getPartyfromAccountNum;
384
385 FUNCTION getPartyfromEvRegCode(p_object_value IN VARCHAR2) return NUMBER is
386 l_partyID NUMBER;
387 l_more_partyID NUMBER;
388
389 cursor getPartyIDfromEvRegCode (p_EvRegCode VARCHAR2) is
390 select registrant_party_id
391 from ams_event_registrations
392 where confirmation_code = p_EvRegCode;
393
394 begin
395 if p_object_value is null or p_object_value = '' then
396 return G_NO_PARTY;
397 end if;
398
399 open getPartyIDfromEvRegCode(p_object_value);
400 fetch getPartyIDfromEvRegCode into l_partyID;
401 if getPartyIDfromEvRegCode%FOUND then
402 fetch getPartyIDfromEvRegCode into l_more_partyID;
403 if getPartyIDfromEvRegCode%FOUND then
404 l_partyID := G_MULTIPLE_PARTY;
405 end if;
406 else
407 l_partyID := G_NO_PARTY;
408 end if;
409 close getPartyIDfromEvRegCode;
410
411 return l_partyID;
412 end getPartyfromEvRegCode;
413
414 FUNCTION getPartyfromMPin(p_object_value IN VARCHAR2) return NUMBER is
415 l_partyID NUMBER;
416 l_more_partyID NUMBER;
417
418 cursor getPartyIDfromMPin (p_MPin VARCHAR2) is
419 select party_id
420 from ams_list_entries
421 where pin_code = p_MPin;
422
423 begin
424 if p_object_value is null or p_object_value = '' then
425 return G_NO_PARTY;
426 end if;
427
428 open getPartyIDfromMPin(p_object_value);
429 fetch getPartyIDfromMPin into l_partyID;
430 if getPartyIDfromMPin%FOUND then
431 fetch getPartyIDfromMPin into l_more_partyID;
432 if getPartyIDfromMPin%FOUND then
433 l_partyID := G_MULTIPLE_PARTY;
434 end if;
435 else
436 l_partyID := G_NO_PARTY;
437 end if;
438 close getPartyIDfromMPin;
439
440 return l_partyID;
441 end getPartyfromMPin;
442
443 FUNCTION getPartyfromContractNum(p_object_value IN VARCHAR2, p_object2_value IN VARCHAR2 default null) return NUMBER is
444 l_partyID NUMBER;
445 l_more_partyID NUMBER;
446
447 cursor getPartyIDfromContractNum (p_ContractNum VARCHAR2, p_ContractNumMod VARCHAR2 default null) is
448 select to_number(p.object1_id1)
449 from okc_k_party_roles_b p , okc_k_headers_b k
450 where k.contract_number = p_ContractNum
451 and k.contract_number_modifier = nvl(p_ContractNumMod, k.contract_number_modifier)
452 and k.id = p.dnz_chr_id
453 and p.primary_yn = 'Y'
454 and p.jtot_object1_code = 'OKX_PARTY';
455 --and p.object1_id2 = '#';
456
457 begin
458 if p_object_value is null or p_object_value = '' then
459 return G_NO_PARTY;
460 end if;
461
462 open getPartyIDfromContractNum(p_object_value, p_object2_value);
463 fetch getPartyIDfromContractNum into l_partyID;
464 if getPartyIDfromContractNum%FOUND then
465 fetch getPartyIDfromContractNum into l_more_partyID;
466 if getPartyIDfromContractNum%FOUND then
467 l_partyID := G_MULTIPLE_PARTY;
468 end if;
469 else
470 l_partyID := G_NO_PARTY;
471 end if;
472 close getPartyIDfromContractNum;
473
474 return l_partyID;
475 end getPartyfromContractNum;
476
477 FUNCTION getPartyfromServiceKey(p_object_value IN VARCHAR2) return NUMBER is
478 l_partyID NUMBER;
479 l_more_partyID NUMBER;
480
481 cursor getPartyIDfromServiceKey (p_ServiceKey VARCHAR2) is
482 select owner_party_id
483 from csi_item_instances
484 where instance_number = p_ServiceKey
485 and owner_party_source_table = 'HZ_PARTIES';
486
487 begin
488 if p_object_value is null or p_object_value = '' then
489 return G_NO_PARTY;
490 end if;
491
492 open getPartyIDfromServiceKey(p_object_value);
493 fetch getPartyIDfromServiceKey into l_partyID;
494 if getPartyIDfromServiceKey%FOUND then
495 fetch getPartyIDfromServiceKey into l_more_partyID;
496 if getPartyIDfromServiceKey%FOUND then
497 l_partyID := G_MULTIPLE_PARTY;
498 end if;
499 else
500 l_partyID := G_NO_PARTY;
501 end if;
502 close getPartyIDfromServiceKey;
503
504 return l_partyID;
505 end getPartyfromServiceKey;
506
507 FUNCTION getPartyfromSRNum(p_object_value IN VARCHAR2) return NUMBER is
508 l_partyID NUMBER;
509 l_more_partyID NUMBER;
510
511 cursor getPartyIDfromSRNum (p_SRNum VARCHAR2) is
512 select customer_id
513 from cs_incidents_all_b
514 where incident_number = p_SRNum;
515
516 begin
517 if p_object_value is null or p_object_value = '' then
518 return G_NO_PARTY;
519 end if;
520
521 open getPartyIDfromSRNum(p_object_value);
522 fetch getPartyIDfromSRNum into l_partyID;
523 if getPartyIDfromSRNum%FOUND then
524 fetch getPartyIDfromSRNum into l_more_partyID;
525 if getPartyIDfromSRNum%FOUND then
526 l_partyID := G_MULTIPLE_PARTY;
527 end if;
528 else
529 l_partyID := G_NO_PARTY;
530 end if;
531 close getPartyIDfromSRNum;
532
533 return l_partyID;
534 end getPartyfromSRNum;
535
536 PROCEDURE getPartyForObject (
537 p_object_type IN VARCHAR2,
538 p_object_value IN VARCHAR2,
539 p_party_name OUT NOCOPY VARCHAR2,
540 p_party_id OUT NOCOPY NUMBER) is
541
542 l_object_type VARCHAR2(100) := null;
543 l_object_value VARCHAR2(100) := null;
544 begin
545 getPartyForObject(p_object_type, p_object_value, l_object_type, l_object_value, p_party_name, p_party_id);
546 end getPartyForObject;
547
548 PROCEDURE getPartyForObject (
549 p_object_type IN VARCHAR2,
550 p_object_value IN VARCHAR2,
551 p_object2_type IN OUT NOCOPY VARCHAR2,
552 p_object2_value IN OUT NOCOPY VARCHAR2,
553 p_party_name OUT NOCOPY VARCHAR2,
554 p_party_id OUT NOCOPY NUMBER) is
555
556 cursor getPartyName (p_PartyID NUMBER) is
557 select party_name
558 from hz_parties
559 where party_id = p_PartyID;
560
561 begin
562 p_party_id := G_NO_PARTY;
563 p_party_name := null;
564
565 if p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_ANI then
566 p_party_id := getPartyfromANI(p_object_value);
567 --elsif p_object_type = 'DNIS' then
568 --p_party_id := getPartyfromDNIS(p_object_value);
569 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_PARTY_NUMBER then
570 p_party_id := getPartyfromPartyNum(p_object_value);
571 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_QUOTE_NUMBER then
572 p_party_id := getPartyfromQuoteNum(p_object_value);
573 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_ORDER_NUMBER then
574 p_party_id := getPartyfromOrderNum(p_object_value);
575 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_COLLATERAL_REQUEST_NUMBER then
576 p_party_id := getPartyfromCollRequest(p_object_value);
577 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_ACCOUNT_NUMBER then
578 p_party_id := getPartyfromAccountNum(p_object_value);
579 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_EVENT_REGISTRATION_CODE then
580 p_party_id := getPartyfromEvRegCode(p_object_value);
581 --elsif p_object_type = 'SOURCE_CODE' then
582 --p_party_id := getPartyfromSourceCode(p_object_value);
583 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_MARKETING_PIN then
584 p_party_id := getPartyfromMPin(p_object_value);
585 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_CONTRACT_NUMBER then
586 p_party_id := getPartyfromContractNum(p_object_value, p_object2_value);
587 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_SERVICE_KEY then
588 p_party_id := getPartyfromServiceKey(p_object_value);
589 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_SERVICE_REQUEST_NUMBER then
590 p_party_id := getPartyfromSRNum(p_object_value);
591 -- kmahajan - 08/29/2002 - added for update in bug 2540033
592 elsif p_object_type = CCT_INTERACTIONKEYS_PUB.KEY_CUSTOMER_ID then
593 p_party_id := getPartyfromPartyID(p_object_value);
594 else
595 null;
596 end if;
597
598 if p_party_id in (G_NO_PARTY, G_MULTIPLE_PARTY) then
599 p_party_name := null;
600 else
601 open getPartyName(p_party_id);
602 fetch getPartyName into p_party_name;
603 close getPartyName;
604 end if;
605
606 end getPartyForObject;
607
608 END AST_ROUTING_PUB;
[Home] [Help]