1 PACKAGE BODY CSC_SERVICE_KEY_PVT AS
2 /* $Header: cscvpskb.pls 120.6 2006/04/17 21:14:32 kjhamb noship $ */
3
4 -- This procedure accepts service key name, service key value and returns all
5 -- ID information that are found when search is made on a servcie key name and
6 -- value.
7 PROCEDURE Service_Key_Search (
8 p_skey_name IN VARCHAR2,
9 p_skey_value IN VARCHAR2,
10 x_hdr_info_tbl OUT NOCOPY HDR_INFO_TBL_TYPE )
11 IS
12 -- Local Variable declaration
13 l_skey_value VARCHAR2(2000);
14 l_skey_name VARCHAR2(40);
15 l_party_id NUMBER;
16 l_object_id NUMBER;
17 l_incident_id NUMBER;
18 l_contact_point_type VARCHAR2(30);
19 l_contact_type VARCHAR2(100);
20 l_count NUMBER :=0;
21 l_hdr_info_tbl CSC_SERVICE_KEY_PVT.hdr_info_tbl_type;
22 l_sold_to_contact_id NUMBER;
23
24 -- Cursor declaration for Service Keys
25
26 -- Service Request
27 -- Querying Customer information using service request number
28 CURSOR SR_Customer_Cur IS
29 SELECT incident_id, customer_id, customer_phone_id, customer_email_id,
30 account_id
31 FROM cs_incidents_all_b
32 WHERE incident_number = l_skey_value;
33
34 -- Querying Contact Information using Incident Id
35 CURSOR SR_Contact_Cur IS
36 SELECT party_id, contact_point_id, contact_point_type, contact_type
37 FROM cs_hz_sr_contact_points
38 WHERE primary_flag = 'Y' AND
39 --contact_type <> 'EMPLOYEE' AND
40 incident_id = l_incident_id;
41
42 -- Querying secondary contact point for a contact
43 CURSOR SR_sec_cont_pt_cur IS
44 SELECT contact_point_id, contact_point_type
45 FROM cs_hz_sr_contact_points
46 WHERE incident_id = l_incident_id AND
47 party_id = l_party_id AND
48 contact_point_type = l_contact_point_type AND
49 rownum < 2;
50
51 -- Querying subject party id for a contact
52 CURSOR party_cont_sub_id_cur IS
53 SELECT subject_id
54 FROM hz_relationships
55 WHERE party_id = l_party_id AND
56 subject_type = 'PERSON' AND
57 object_id = l_object_id;
58
59 -- Invoice Number
60 CURSOR invoice_num_cur IS
61 SELECT cust_acct.party_id, cust_acct.cust_account_id, customer_trx_id, ra.org_id org_id
62 FROM hz_cust_accounts cust_acct, ra_customer_trx_all ra
63 WHERE cust_acct.cust_account_id = ra.bill_to_customer_id AND
64 ra.complete_flag = 'Y' AND
65 ra.cust_trx_type_id IN
66 ( SELECT cust_trx_type_id FROM ra_cust_trx_types_all
67 WHERE type = 'INV') AND
68 ra.trx_number = l_skey_value;
69
70 -- Invoice Number -- MOAC
71 CURSOR invoice_num_moac_cur IS
72 SELECT cust_acct.party_id, cust_acct.cust_account_id, customer_trx_id, ra.org_id org_id
73 FROM hz_cust_accounts cust_acct, ra_customer_trx ra
74 WHERE cust_acct.cust_account_id = ra.bill_to_customer_id AND
75 ra.complete_flag = 'Y' AND
76 ra.cust_trx_type_id IN
77 ( SELECT cust_trx_type_id FROM ra_cust_trx_types
78 WHERE type = 'INV') AND
79 ra.trx_number = l_skey_value;
80
81 -- Order Number
82 CURSOR order_num_cur IS
83 SELECT hza.party_id, hza.cust_account_id, oe.header_id, oe.org_id, oe.sold_to_contact_id
84 FROM oe_order_headers_all oe, hz_cust_accounts hza
85 WHERE oe.sold_to_org_id = hza.cust_account_id AND
86 oe.order_number = l_skey_value;
87
88 CURSOR order_contact_cur IS
89 SELECT party_id
90 FROM hz_cust_account_roles
91 WHERE cust_account_role_id = l_sold_to_contact_id;
92
93
94 -- Order Number -- MOAC
95 CURSOR order_num_moac_cur IS
96 SELECT hza.party_id, hza.cust_account_id, oe.header_id, oe.org_id, oe.sold_to_contact_id
97 FROM oe_order_headers oe, hz_cust_accounts hza
98 WHERE oe.sold_to_org_id = hza.cust_account_id AND
99 oe.order_number = l_skey_value;
100
101 -- RMA Number
102 CURSOR rma_num_cur IS
103 SELECT hza.party_id, hza.cust_account_id, oe.header_id, oe.org_id, oe.sold_to_contact_id
104 FROM oe_order_headers_all oe, hz_cust_accounts hza
105 WHERE oe.sold_to_org_id = hza.cust_account_id AND
106 -- Bug Fix 5136678 Added 'Mixed' Category Code
107 oe.order_category_code IN ('RETURN','MIXED') AND
108 oe.order_number = l_skey_value;
109
110 -- RMA Number MOAC
111 CURSOR rma_num_moac_cur IS
112 SELECT hza.party_id, hza.cust_account_id, oe.header_id, oe.org_id, oe.sold_to_contact_id
113 FROM oe_order_headers oe, hz_cust_accounts hza
114 WHERE oe.sold_to_org_id = hza.cust_account_id AND
115 --Bug Fix 5136678 Added 'Mixed' Category Code
116 oe.order_category_code IN ('RETURN','MIXED') AND
117 oe.order_number = l_skey_value;
118
119 -- Contract Number
120 CURSOR contract_num_cur IS
121 SELECT party_id, contract_id
122 FROM oks_ent_hdr_summary_v
123 WHERE contract_number = l_skey_value;
124
125 -- System
126 CURSOR system_cur IS
127 SELECT hca.party_id, hca.cust_account_id, csb.system_id
128 FROM csi_systems_vl csb, hz_cust_accounts hca
129 WHERE csb.customer_id = hca.cust_account_id
130 AND csb.name = l_skey_value;
131
132 -- Tag Number
133 CURSOR tag_num_cur IS
134 SELECT item.owner_party_id, item.owner_party_account_id, instance_id
135 FROM csi_item_instances item
136 WHERE item.external_reference IS NOT NULL
137 AND item.external_reference = l_skey_value;
138
139 -- Serial Number
140 CURSOR serial_num_cur IS
141 SELECT item.owner_party_id, item.owner_party_account_id, instance_id
142 FROM csi_item_instances item
143 WHERE item.serial_number IS NOT NULL
144 AND item.serial_number = l_skey_value;
145
146 -- Instance Name
147 CURSOR instance_name_cur IS
148 SELECT owner_party_id, owner_party_account_id, instance_id
149 FROM csi_item_instances
150 WHERE owner_party_source_table = 'HZ_PARTIES'
151 AND instance_description IS NOT NULL
152 AND instance_description = l_skey_value;
153
154 -- Instance Number
155 CURSOR instance_num_cur IS
156 SELECT owner_party_id, owner_party_account_id, instance_id
157 FROM csi_item_instances
158 WHERE owner_party_source_table = 'HZ_PARTIES'
159 AND instance_number IS NOT NULL
160 AND instance_number = l_skey_value;
161
162 --SSN
163 CURSOR SSN_cur IS
164 SELECT party_id
165 FROM hz_parties
166 WHERE jgzz_fiscal_code = l_skey_value;
167
168
169 BEGIN
170
171 l_skey_value := p_skey_value;
172 l_skey_name := p_skey_name;
173 IF JTF_USR_HKS.ok_to_Execute('CSC_SERVICE_KEY_PVT', 'SERVICE_KEY_SEARCH', 'B', 'C') THEN
174 CSC_Service_Key_CUHK.Service_Key_Search_Pre(p_skey_name => l_skey_name,
175 p_skey_value => l_skey_value,
176 x_hdr_info_tbl => l_hdr_info_tbl);
177 x_hdr_info_tbl := l_hdr_info_tbl;
178 ELSE
179 IF p_skey_name = 'SERVICE_REQUEST_NUMBER' THEN
180 FOR sr_customer_rec IN sr_customer_cur
181 LOOP
182 l_count := l_count + 1;
183 x_hdr_info_tbl(l_count).cust_party_id := sr_customer_rec.customer_id;
184 l_object_id := x_hdr_info_tbl(l_count).cust_party_id;
185 x_hdr_info_tbl(l_count).cust_phone_id :=
186 sr_customer_rec.customer_phone_id;
187 x_hdr_info_tbl(l_count).cust_email_id :=
188 sr_customer_rec.customer_email_id;
189 x_hdr_info_tbl(l_count).account_id := sr_customer_rec.account_id;
190 l_incident_id := sr_customer_rec.incident_id;
191 x_hdr_info_tbl(l_count).service_key_id := l_incident_id;
192
193 --IF l_incident_id IS NOT NULL THEN
194 FOR sr_contact_rec IN sr_contact_cur
195 LOOP
196 l_contact_type := sr_contact_rec.contact_type;
197 IF l_contact_type = 'EMPLOYEE' THEN
198 x_hdr_info_tbl(l_count).employee_id :=
199 sr_contact_rec.party_id;
200 ELSE
201 x_hdr_info_tbl(l_count).rel_party_id :=
202 sr_contact_rec.party_id;
203 END IF;
204 l_party_id := sr_contact_rec.party_id;
205 l_contact_point_type := sr_contact_rec.contact_point_type;
206
207 IF l_contact_point_type = 'PHONE' THEN
208 x_hdr_info_tbl(l_count).rel_phone_id :=
209 sr_contact_rec.contact_point_id;
210 l_contact_point_type := 'EMAIL';
211 FOR sr_sec_cont_pt_rec IN sr_sec_cont_pt_cur
212 LOOP
213 x_hdr_info_tbl(l_count).rel_email_id :=
214 sr_sec_cont_pt_rec.contact_point_id;
215 END LOOP;
216 ELSIF l_contact_point_type = 'EMAIL' THEN
217 x_hdr_info_tbl(l_count).rel_email_id :=
218 sr_contact_rec.contact_point_id;
219 l_contact_point_type := 'PHONE';
220 FOR sr_sec_cont_pt_rec IN sr_sec_cont_pt_cur
221 LOOP
222 x_hdr_info_tbl(l_count).rel_phone_id :=
223 sr_sec_cont_pt_rec.contact_point_id;
224 END LOOP;
225 END IF;
226 l_party_id := x_hdr_info_tbl(l_count).rel_party_id;
227 FOR sr_cont_sub_id_rec IN party_cont_sub_id_cur
228 LOOP
229 x_hdr_info_tbl(l_count).per_party_id :=
230 sr_cont_sub_id_rec.subject_id;
231 END LOOP;
232 END LOOP;
233 IF x_hdr_info_tbl(l_count).rel_party_id = x_hdr_info_tbl(l_count).cust_party_id AND
234 x_hdr_info_tbl(l_count).per_party_id IS NULL THEN
235 x_hdr_info_tbl(l_count).per_party_id := x_hdr_info_tbl(l_count).cust_party_id;
236 END IF;
237
238 --END IF;
239
240 END LOOP;
241
242 ELSIF p_skey_name = 'INVOICE_NUMBER' THEN
243 if mo_global.get_access_mode = 'M' then
244 FOR invoice_num_rec IN invoice_num_moac_cur
245 LOOP
246 l_count := l_count + 1;
247 x_hdr_info_tbl(l_count).cust_party_id := invoice_num_rec.party_id;
248 x_hdr_info_tbl(l_count).account_id := invoice_num_rec.cust_account_id;
249 x_hdr_info_tbl(l_count).service_key_id := invoice_num_rec.customer_trx_id;
250 -- Added org_id as part of the MOAC project. This org_id will be used to
251 -- populate the oeprating unit in the header.
252 x_hdr_info_tbl(l_count).org_id := invoice_num_rec.org_id;
253 END LOOP;
254 else
255 FOR invoice_num_rec IN invoice_num_cur
256 LOOP
257 l_count := l_count + 1;
258 x_hdr_info_tbl(l_count).cust_party_id := invoice_num_rec.party_id;
259 x_hdr_info_tbl(l_count).account_id := invoice_num_rec.cust_account_id;
260 x_hdr_info_tbl(l_count).service_key_id := invoice_num_rec.customer_trx_id;
261 -- Added org_id as part of the MOAC project. This org_id will be used to
262 -- populate the oeprating unit in the header.
263 x_hdr_info_tbl(l_count).org_id := invoice_num_rec.org_id;
264 END LOOP;
265 end if;
266
267 ELSIF p_skey_name = 'ORDER_NUMBER' THEN
268 IF mo_global.get_access_mode = 'M' THEN
269 FOR order_num_rec IN order_num_moac_cur
270 LOOP
271 l_count := l_count + 1;
272 x_hdr_info_tbl(l_count).cust_party_id := order_num_rec.party_id;
273 x_hdr_info_tbl(l_count).account_id := order_num_rec.cust_account_id;
274 x_hdr_info_tbl(l_count).service_key_id := order_num_rec.header_id;
275 -- Added org_id as part of the MOAC project. This org_id will be used to
276 -- populate the oeprating unit in the header.
277 x_hdr_info_tbl(l_count).org_id := order_num_rec.org_id;
278
279 -- Included following logic to identify Contact for the order
280 l_sold_to_contact_id := order_num_rec.sold_to_contact_id;
281 FOR order_contact_rec IN order_contact_cur
282 LOOP
283 x_hdr_info_tbl(l_count).rel_party_id := order_contact_rec.party_id;
284 END LOOP;
285 l_party_id := x_hdr_info_tbl(l_count).rel_party_id;
286 l_object_id := x_hdr_info_tbl(l_count).cust_party_id;
287 FOR order_cont_sub_id_rec IN party_cont_sub_id_cur
288 LOOP
289 x_hdr_info_tbl(l_count).per_party_id := order_cont_sub_id_rec.subject_id;
290 END LOOP;
291 IF x_hdr_info_tbl(l_count).rel_party_id = x_hdr_info_tbl(l_count).cust_party_id AND
292 x_hdr_info_tbl(l_count).per_party_id IS NULL THEN
293 x_hdr_info_tbl(l_count).per_party_id := x_hdr_info_tbl(l_count).cust_party_id;
294 END IF;
295
296 END LOOP;
297 ELSE
298 FOR order_num_rec IN order_num_cur
299 LOOP
300 l_count := l_count + 1;
301 x_hdr_info_tbl(l_count).cust_party_id := order_num_rec.party_id;
302 x_hdr_info_tbl(l_count).account_id := order_num_rec.cust_account_id;
303 x_hdr_info_tbl(l_count).service_key_id := order_num_rec.header_id;
304 -- Added org_id as part of the MOAC project. This org_id will be used to
305 -- populate the oeprating unit in the header.
306 x_hdr_info_tbl(l_count).org_id := order_num_rec.org_id;
307
308 -- Included following logic to identify Contact for the order
309 l_sold_to_contact_id := order_num_rec.sold_to_contact_id;
310 FOR order_contact_rec IN order_contact_cur
311 LOOP
312 x_hdr_info_tbl(l_count).rel_party_id := order_contact_rec.party_id;
313 END LOOP;
314 l_party_id := x_hdr_info_tbl(l_count).rel_party_id;
315 l_object_id := x_hdr_info_tbl(l_count).cust_party_id;
316 FOR order_cont_sub_id_rec IN party_cont_sub_id_cur
317 LOOP
318 x_hdr_info_tbl(l_count).per_party_id := order_cont_sub_id_rec.subject_id;
319 END LOOP;
320 IF x_hdr_info_tbl(l_count).rel_party_id = x_hdr_info_tbl(l_count).cust_party_id AND
321 x_hdr_info_tbl(l_count).per_party_id IS NULL THEN
322 x_hdr_info_tbl(l_count).per_party_id := x_hdr_info_tbl(l_count).cust_party_id;
323 END IF;
324
325 END LOOP;
326 END IF;
327
328 ELSIF p_skey_name = 'RMA_NUMBER' THEN
329 IF mo_global.get_access_mode = 'M' THEN
330 FOR rma_num_rec IN rma_num_moac_cur
331 LOOP
332 l_count := l_count + 1;
333 x_hdr_info_tbl(l_count).cust_party_id := rma_num_rec.party_id;
334 x_hdr_info_tbl(l_count).account_id := rma_num_rec.cust_account_id;
335 x_hdr_info_tbl(l_count).service_key_id := rma_num_rec.header_id;
336 -- Added org_id as part of the MOAC project. This org_id will be used to
337 -- populate the oeprating unit in the header.
338 x_hdr_info_tbl(l_count).org_id := rma_num_rec.org_id;
339
340 -- Included following logic to identify Contact for the order
341 l_sold_to_contact_id := rma_num_rec.sold_to_contact_id;
342 FOR rma_contact_rec IN order_contact_cur
343 LOOP
344 x_hdr_info_tbl(l_count).rel_party_id := rma_contact_rec.party_id;
345 END LOOP; -- end loop for order_contact_rec
346 l_party_id := x_hdr_info_tbl(l_count).rel_party_id;
347 l_object_id := x_hdr_info_tbl(l_count).cust_party_id;
348 FOR rma_cont_sub_id_rec IN party_cont_sub_id_cur
349 LOOP
350 x_hdr_info_tbl(l_count).per_party_id := rma_cont_sub_id_rec.subject_id;
351 END LOOP; -- end loop for order_cont_sub_id_rec
352 IF x_hdr_info_tbl(l_count).rel_party_id = x_hdr_info_tbl(l_count).cust_party_id AND
353 x_hdr_info_tbl(l_count).per_party_id IS NULL THEN
354 x_hdr_info_tbl(l_count).per_party_id := x_hdr_info_tbl(l_count).cust_party_id;
355 END IF;
356
357 END LOOP; -- end loop for rma_num_rec
358
359 ELSE -- else for mo_global.get_access_mode
360
361 FOR rma_num_rec IN rma_num_cur
362 LOOP
363 l_count := l_count + 1;
364 x_hdr_info_tbl(l_count).cust_party_id := rma_num_rec.party_id;
365 x_hdr_info_tbl(l_count).account_id := rma_num_rec.cust_account_id;
366 x_hdr_info_tbl(l_count).service_key_id := rma_num_rec.header_id;
367 -- Added org_id as part of the MOAC project. This org_id will be used to
368 -- populate the oeprating unit in the header.
369 x_hdr_info_tbl(l_count).org_id := rma_num_rec.org_id;
370
371 -- Included following logic to identify Contact for the order
372 l_sold_to_contact_id := rma_num_rec.sold_to_contact_id;
373 FOR rma_contact_rec IN order_contact_cur
374 LOOP
375 x_hdr_info_tbl(l_count).rel_party_id := rma_contact_rec.party_id;
376 END LOOP; -- end loop for rma_contact_rec
377 l_party_id := x_hdr_info_tbl(l_count).rel_party_id;
378 l_object_id := x_hdr_info_tbl(l_count).cust_party_id;
379 FOR rma_cont_sub_id_rec IN party_cont_sub_id_cur
380 LOOP
381 x_hdr_info_tbl(l_count).per_party_id := rma_cont_sub_id_rec.subject_id;
382 END LOOP; -- end loop for rma_cont_sub_id_rec
383 IF x_hdr_info_tbl(l_count).rel_party_id = x_hdr_info_tbl(l_count).cust_party_id AND
384 x_hdr_info_tbl(l_count).per_party_id IS NULL THEN
385 x_hdr_info_tbl(l_count).per_party_id := x_hdr_info_tbl(l_count).cust_party_id;
386 END IF;
387
388 END LOOP; -- end loop for rma_num_rec
389 END IF; -- end if for mo_global.get_access_mode
390
391 ELSIF p_skey_name = 'CONTRACT_NUMBER' THEN
392 FOR contract_num_rec IN contract_num_cur
393 LOOP
394 l_count := l_count + 1;
395 x_hdr_info_tbl(l_count).cust_party_id := contract_num_rec.party_id;
396 x_hdr_info_tbl(l_count).service_key_id := contract_num_rec.contract_id;
397 END LOOP;
398 ELSIF p_skey_name = 'SYSTEM_NUMBER' THEN
399 FOR system_rec IN system_cur
400 LOOP
401 l_count := l_count + 1;
402 x_hdr_info_tbl(l_count).cust_party_id := system_rec.party_id;
403 x_hdr_info_tbl(l_count).account_id := system_rec.cust_account_id;
404 x_hdr_info_tbl(l_count).service_key_id := system_rec.system_id;
405 END LOOP;
406 ELSIF p_skey_name = 'EXTERNAL_REFERENCE' THEN
407 FOR tag_num_rec IN tag_num_cur
408 LOOP
409 l_count := l_count + 1;
410 x_hdr_info_tbl(l_count).cust_party_id := tag_num_rec.owner_party_id;
411 x_hdr_info_tbl(l_count).account_id := tag_num_rec.owner_party_account_id;
412 x_hdr_info_tbl(l_count).service_key_id := tag_num_rec.instance_id;
413 END LOOP;
414 ELSIF p_skey_name = 'SERIAL_NUMBER' THEN
415 FOR serial_num_rec IN serial_num_cur
416 LOOP
417 l_count := l_count + 1;
418 x_hdr_info_tbl(l_count).cust_party_id := serial_num_rec.owner_party_id;
419 x_hdr_info_tbl(l_count).account_id := serial_num_rec.owner_party_account_id;
420 x_hdr_info_tbl(l_count).service_key_id := serial_num_rec.instance_id;
421 END LOOP;
422 ELSIF p_skey_name = 'INSTANCE_NAME' THEN
423 FOR instance_name_rec IN instance_name_cur
424 LOOP
425 l_count := l_count + 1;
426 x_hdr_info_tbl(l_count).cust_party_id := instance_name_rec.owner_party_id;
427 x_hdr_info_tbl(l_count).account_id := instance_name_rec.owner_party_account_id;
428 x_hdr_info_tbl(l_count).service_key_id := instance_name_rec.instance_id;
429 END LOOP;
430 ELSIF p_skey_name = 'INSTANCE_NUMBER' THEN
431 FOR instance_num_rec IN instance_num_cur
432 LOOP
433 l_count := l_count + 1;
434 x_hdr_info_tbl(l_count).cust_party_id := instance_num_rec.owner_party_id;
435 x_hdr_info_tbl(l_count).account_id := instance_num_rec.owner_party_account_id;
436 x_hdr_info_tbl(l_count).service_key_id := instance_num_rec.instance_id;
437 END LOOP;
438 ELSIF p_skey_name = 'SSN' THEN
439 FOR SSN_rec in SSN_cur
440 LOOP
441 l_count := l_count + 1;
442 x_hdr_info_tbl(l_count).cust_party_id := SSN_rec.party_id;
443 END LOOP;
444 END IF;
445
446 END IF;
447 EXCEPTION
448 --WHEN INVALID_NUMBER THEN
449 --FND_MESSAGE.Set_Name('CSC', 'CSC_INVALID_NUMBER');
450 --FND_MESSAGE.Set_Token('PARAMETER', 'Order');
451 --APP_EXCEPTION.raise_exception;
452 WHEN OTHERS THEN
453 FND_MSG_PUB.Build_Exc_Msg( p_pkg_name => 'CSC_SERVICE_KEY_PVT',
454 p_procedure_name => 'Service_Key_Search');
455 APP_EXCEPTION.raise_exception;
456 END Service_Key_Search;
457
458 END CSC_SERVICE_KEY_PVT;
[Home] [Help]