[Home] [Help]
PACKAGE BODY: APPS.AR_AME_CM_ATTRIBUTES_API
Source
1 PACKAGE BODY ar_ame_cm_attributes_api AS
2 /* $Header: ARAMEATB.pls 120.7 2005/09/06 17:16:01 vcrisost noship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7
8
9 --- Get the customer id
10 FUNCTION get_customer_id (p_transaction_id IN NUMBER)
11 RETURN NUMBER IS
12
13 CURSOR cust_id (p_txn_id NUMBER) IS
14 SELECT rct.bill_to_customer_id
15 FROM hz_cust_accounts bill_to_cust,
16 hz_parties party,
17 ra_customer_trx rct
18 WHERE rct.bill_to_customer_id = bill_to_cust.cust_account_id
19 AND bill_to_cust.party_id = party.party_id
20 AND rct.customer_trx_id =
21 (SELECT r.customer_trx_id
22 FROM ar_lookups l, ra_cm_requests r
23 WHERE r.request_id = p_txn_id
24 AND r.cm_reason_code = l.lookup_code
25 AND l.lookup_type = 'CREDIT_MEMO_REASON');
26
27
28 l_customer_id ra_customer_trx.bill_to_customer_id%TYPE;
29
30 BEGIN
31
32 OPEN cust_id (p_transaction_id);
33 FETCH cust_id INTO l_customer_id;
34 CLOSE cust_id;
35
36 RETURN l_customer_id;
37
38 --- Exception Handling code
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 RAISE;
42
43 WHEN OTHERS THEN
44 RAISE;
45
46 END get_customer_id;
47
48
49 -- Get the customer transaction id
50 FUNCTION get_customer_trx_id (p_transaction_id IN NUMBER)
51 RETURN NUMBER IS
52
53 CURSOR cust_t_id (p_txn_id NUMBER) IS
54 SELECT r.customer_trx_id
55 FROM ar_lookups l,
56 ra_cm_requests r
57 WHERE r.request_id = p_txn_id
58 AND r.cm_reason_code = l.lookup_code
59 AND l.lookup_type = 'CREDIT_MEMO_REASON';
60
61 l_customer_trx_id ra_cm_requests.customer_trx_id%TYPE;
62
63 BEGIN
64
65 OPEN cust_t_id(p_transaction_id);
66 FETCH cust_t_id INTO l_customer_trx_id;
67 CLOSE cust_t_id;
68
69 RETURN l_customer_trx_id;
70
71 --- Exception Handling code
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 RAISE;
75
76 WHEN OTHERS THEN
77 RAISE;
78
79 END get_customer_trx_id;
80
81
82 --- Get the bill to use id
83 FUNCTION get_bill_to_use_id (p_transaction_id IN NUMBER)
84 RETURN NUMBER IS
85
86 CURSOR bill_id (p_txn_id NUMBER) IS
87 SELECT rct.bill_to_site_use_id
88 FROM hz_cust_accounts bill_to_cust,
89 hz_parties party,
90 ra_customer_trx rct
91 WHERE rct.bill_to_customer_id = bill_to_cust.cust_account_id
92 AND bill_to_cust.party_id = party.party_id
93 AND rct.customer_trx_id =
94 (SELECT r.customer_trx_id
95 FROM ar_lookups l,ra_cm_requests r
96 WHERE r.request_id = p_txn_id
97 AND r.cm_reason_code = l.lookup_code
98 AND l.lookup_type = 'CREDIT_MEMO_REASON');
99
100 l_bill_to_use_id ra_customer_trx.bill_to_site_use_id%TYPE;
101
102 BEGIN
103
104 OPEN bill_id(p_transaction_id);
105 FETCH bill_id INTO l_bill_to_use_id;
106 CLOSE bill_id;
107
108 RETURN l_bill_to_use_id;
109
110 --- Exception Handling code
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 RAISE;
114
115 WHEN OTHERS THEN
116 RAISE;
117
118 END get_bill_to_use_id;
119
120
121 --- Get the collector id
122 FUNCTION get_collector_id (p_transaction_id IN NUMBER)
123 RETURN VARCHAR2 IS
124
125 -- 4566177 : pick up collector at site and header level, the order by clause will cause the
126 -- cursor to return site level first in cases where both site and header, the code will use
127 -- the first value returned
128 CURSOR coll_id (p_customer_id NUMBER, p_bill_to_use_id NUMBER) IS
129 SELECT 'person_id:' || col.employee_id, cp.site_use_id
130 FROM ar_collectors col,
131 hz_customer_profiles cp
132 WHERE cp.collector_id = col.collector_id
133 AND cp.cust_account_id = p_customer_id
134 AND (cp.site_use_id = p_bill_to_use_id
135 OR cp.site_use_id is NULL)
136 ORDER by cp.site_use_id;
137
138 -- In the cursor above the syntax can be changed
139 -- as shown below to work with dynamic pre-approver
140 -- SELECT 'first:pre:person_id:' || col.employee_id
141
142
143 l_customer_id ra_customer_trx.bill_to_customer_id%TYPE;
144 l_bill_to_use_id ra_customer_trx.bill_to_site_use_id%TYPE;
145 -- l_collector_id ar_collectors.employee_id%TYPE;
146 l_collector_id VARCHAR2(50);
147 l_site_use_id VARCHAR2(50);
148
149 BEGIN
150
151 l_customer_id := get_customer_id (p_transaction_id);
152 l_bill_to_use_id := get_bill_to_use_id (p_transaction_id);
153
154 OPEN coll_id(l_customer_id, l_bill_to_use_id);
155 FETCH coll_id INTO l_collector_id, l_site_use_id;
156 CLOSE coll_id;
157
158 RETURN l_collector_id;
159
160 --- Exception Handling code
161 EXCEPTION
162 WHEN NO_DATA_FOUND THEN
163 RAISE;
164
165 WHEN OTHERS THEN
166 RAISE;
167
168 END get_collector_id;
169
170
171 /* All of these functions are very similar in structure and style.
172 * They all are used to get the value of certain attributes. We
173 * do not know the type of the attributes, so we use the values in
174 * the tables where the data is obtained.
175 */
176
177 --- Get the non default person id
178 FUNCTION get_non_default_person_id (p_transaction_id IN NUMBER)
179 RETURN NUMBER IS
180
181 l_person_id wf_item_attribute_values.number_value%TYPE;
182
183 BEGIN
184
185
186 l_person_id := wf_engine.GetItemAttrNumber(
187 itemtype => c_item_type,
188 itemkey => p_transaction_id,
189 aname => 'NON_DEFAULT_START_PERSON_ID');
190
191 RETURN l_person_id;
192
193 END get_non_default_person_id;
194
195
196 --- Get requestor person id
197 FUNCTION get_requestor_person_id (p_transaction_id IN NUMBER)
198 RETURN NUMBER IS
199
200 l_person_id wf_item_attribute_values.number_value%TYPE;
201
202 BEGIN
203
204 l_person_id := wf_engine.GetItemAttrNumber(
205 itemtype => c_item_type,
206 itemkey => p_transaction_id,
207 aname => 'REQUESTOR_ID');
208
209 RETURN l_person_id;
210
211 END get_requestor_person_id;
212
213
214 --- Get the approval path
215 FUNCTION get_approval_path (p_transaction_id IN NUMBER)
216 RETURN VARCHAR2 IS
217
218 l_approval_path wf_item_attribute_values.text_value%TYPE;
219
220 BEGIN
221
222 l_approval_path := wf_engine.GetItemAttrText(
223 itemtype => c_item_type,
224 itemkey => p_transaction_id,
225 aname => 'APPROVAL_PATH');
226
227 RETURN l_approval_path;
228
229 END get_approval_path;
230
231
232 --- Get the reason code
233 FUNCTION get_reason_code (p_transaction_id IN NUMBER)
234 RETURN VARCHAR2 IS
235
236 l_reason_code wf_item_attribute_values.text_value%TYPE;
237
238 BEGIN
239
240 l_reason_code := wf_engine.GetItemAttrText(
241 itemtype => c_item_type,
242 itemkey => p_transaction_id,
243 aname => 'REASON');
244
245 RETURN l_reason_code;
246
247 END get_reason_code;
248
249
250 --- Get the transaction amount
251 FUNCTION get_transaction_amount (p_transaction_id IN NUMBER)
252 RETURN NUMBER IS
253
254 l_txn_amount wf_item_attribute_values.number_value%TYPE;
255
256 BEGIN
257
258 l_txn_amount := wf_engine.GetItemAttrNumber(
259 itemtype => c_item_type,
260 itemkey => p_transaction_id,
261 aname => 'TOTAL_CREDIT_TO_INVOICE');
262
263 RETURN l_txn_amount;
264
265 END get_transaction_amount;
266
267 -- Get Approver ID
268 FUNCTION get_approver_id (p_transaction_id IN NUMBER)
269 RETURN NUMBER IS
270
271 l_approver_id wf_item_attribute_values.number_value%TYPE;
272
273 BEGIN
274
275 l_approver_id := wf_engine.GetItemAttrNumber(
276 itemtype => c_item_type,
277 itemkey => p_transaction_id,
278 aname => 'APPROVER_ID');
279
280 RETURN l_approver_id;
281
282 END get_approver_id;
283
284
285 -- Get Approver User Name
286 FUNCTION get_approver_user_name (p_transaction_id IN NUMBER)
287 RETURN VARCHAR2 IS
288
289 l_approver_user_name wf_item_attribute_values.text_value%TYPE;
290
291 BEGIN
292
293 l_approver_user_name := wf_engine.GetItemAttrText(
294 itemtype => c_item_type,
295 itemkey => p_transaction_id,
296 aname => 'APPROVER_USER_NAME');
297
298 RETURN l_approver_user_name;
299
300 END get_approver_user_name;
301
302
303 -- Get Batch Source Name
304 FUNCTION get_batch_source_name (p_transaction_id IN NUMBER)
305 RETURN VARCHAR2 IS
306
307 l_batch_source_name wf_item_attribute_values.text_value%TYPE;
308
309 BEGIN
310
311 l_batch_source_name := wf_engine.GetItemAttrText(
312 itemtype => c_item_type,
313 itemkey => p_transaction_id,
314 aname => 'BATCH_SOURCE_NAME');
315
316 RETURN l_batch_source_name;
317
318 END get_batch_source_name;
319
320
321 -- Get Bill to Customer Name
322 FUNCTION get_bill_to_customer_name (p_transaction_id IN NUMBER)
323 RETURN VARCHAR2 IS
324
325 l_bill_to_customer_name wf_item_attribute_values.text_value%TYPE;
326
327 BEGIN
328
329 l_bill_to_customer_name := wf_engine.GetItemAttrText(
330 itemtype => c_item_type,
331 itemkey => p_transaction_id,
332 aname => 'BILL_TO_CUSTOMER_NAME');
333
334 RETURN l_bill_to_customer_name;
335
336 END get_bill_to_customer_name;
337
338
339 -- Get Bill to Customer Number
340 FUNCTION get_bill_to_customer_number (p_transaction_id IN NUMBER)
341 RETURN VARCHAR2 IS
342
343 l_bill_to_customer_number wf_item_attribute_values.text_value%TYPE;
344
345 BEGIN
346
347 l_bill_to_customer_number := wf_engine.GetItemAttrText(
348 itemtype => c_item_type,
349 itemkey => p_transaction_id,
350 aname => 'BILL_TO_CUSTOMER_NUMBER');
351
352 RETURN l_bill_to_customer_number;
353
354 END get_bill_to_customer_number;
355
356
357 -- Get Collector Employee Id
358 FUNCTION get_collector_employee_id (p_transaction_id IN NUMBER)
359 RETURN NUMBER IS
360
361 l_collector_employee_id wf_item_attribute_values.number_value%TYPE;
362
363 BEGIN
364
365 l_collector_employee_id := wf_engine.GetItemAttrNumber(
366 itemtype => c_item_type,
367 itemkey => p_transaction_id,
368 aname => 'COLLECTOR_EMPLOYEE_ID');
369
370 RETURN l_collector_employee_id;
371
372 END get_collector_employee_id;
373
374
375 -- Get Collector Name
376 FUNCTION get_collector_name (p_transaction_id IN NUMBER)
377 RETURN VARCHAR2 IS
378
379 l_collector_name wf_item_attribute_values.text_value%TYPE;
380
381 BEGIN
382
383 l_collector_name := wf_engine.GetItemAttrText(
384 itemtype => c_item_type,
385 itemkey => p_transaction_id,
386 aname => 'COLLECTOR_NAME');
387
388 RETURN l_collector_name;
389
390 END get_collector_name;
391
392
393 -- Get Collector User Name
394 FUNCTION get_collector_user_name (p_transaction_id IN NUMBER)
395 RETURN VARCHAR2 IS
396
397 l_collector_user_name wf_item_attribute_values.text_value%TYPE;
398
399 BEGIN
400
401 l_collector_user_name := wf_engine.GetItemAttrText(
402 itemtype => c_item_type,
403 itemkey => p_transaction_id,
407
404 aname => 'COLLECTOR_USER_NAME');
405
406 RETURN l_collector_user_name;
408 END get_collector_user_name;
409
410
411 -- Get Currency Code
412 FUNCTION get_currency_code (p_transaction_id IN NUMBER)
413 RETURN VARCHAR2 IS
414
415 l_currency_code wf_item_attribute_values.text_value%TYPE;
416
417 BEGIN
418
419 l_currency_code := wf_engine.GetItemAttrText(
420 itemtype => c_item_type,
421 itemkey => p_transaction_id,
422 aname => 'CURRENCY_CODE');
423
424 RETURN l_currency_code;
425
426 END get_currency_code;
427
428
429 -- Get Customer Name
430 FUNCTION get_customer_name (p_transaction_id IN NUMBER)
431 RETURN VARCHAR2 IS
432
433 l_customer_name wf_item_attribute_values.text_value%TYPE;
434
435 BEGIN
436
437 l_customer_name := wf_engine.GetItemAttrText(
438 itemtype => c_item_type,
439 itemkey => p_transaction_id,
440 aname => 'CUSTOMER_NAME');
441
442 RETURN l_customer_name;
443
444 END get_customer_name;
445
446
447 -- Get Original Transaction Number
448 FUNCTION get_orig_trx_number (p_transaction_id IN NUMBER)
449 RETURN VARCHAR2 IS
450
451 l_orig_trx_number wf_item_attribute_values.text_value%TYPE;
452
453 BEGIN
454
455 l_orig_trx_number := wf_engine.GetItemAttrText(
456 itemtype => c_item_type,
457 itemkey => p_transaction_id,
458 aname => 'ORIG_TRX_NUMBER');
459
460 RETURN l_orig_trx_number;
461
462 END get_orig_trx_number;
463
464
465 -- Get Requestor Id
466 FUNCTION get_requestor_id (p_transaction_id IN NUMBER)
467 RETURN NUMBER IS
468
469 l_requestor_id wf_item_attribute_values.number_value%TYPE;
470
471 BEGIN
472
473 l_requestor_id := wf_engine.GetItemAttrNumber(
474 itemtype => c_item_type,
475 itemkey => p_transaction_id,
476 aname => 'REQUESTOR_ID');
477
478 RETURN l_requestor_id;
479
480 END get_requestor_id;
481
482
483 -- Get Requestor User Name
484 FUNCTION get_requestor_user_name (p_transaction_id IN NUMBER)
485 RETURN VARCHAR2 IS
486
487 l_requestor_user_name wf_item_attribute_values.text_value%TYPE;
488
489 BEGIN
490
491 l_requestor_user_name := wf_engine.GetItemAttrText(
492 itemtype => c_item_type,
493 itemkey => p_transaction_id,
494 aname => 'REQUESTOR_USER_NAME');
495
496 RETURN l_requestor_user_name;
497
498 END get_requestor_user_name;
499
500
501 -- Get Ship to Customer Name
502 FUNCTION get_ship_to_customer_name (p_transaction_id IN NUMBER)
503 RETURN VARCHAR2 IS
504
505 l_ship_to_customer_name wf_item_attribute_values.text_value%TYPE;
506
507 BEGIN
508
509 l_ship_to_customer_name := wf_engine.GetItemAttrText(
510 itemtype => c_item_type,
511 itemkey => p_transaction_id,
512 aname => 'SHIP_TO_CUSTOMER_NAME');
513
514 RETURN l_ship_to_customer_name;
515
516 END get_ship_to_customer_name;
517
518
519 -- Get Ship to Customer Number
520 FUNCTION get_ship_to_customer_number (p_transaction_id IN NUMBER)
521 RETURN VARCHAR2 IS
522
523 l_ship_to_customer_number wf_item_attribute_values.text_value%TYPE;
524
525 BEGIN
526
527 l_ship_to_customer_number := wf_engine.GetItemAttrText(
528 itemtype => c_item_type,
529 itemkey => p_transaction_id,
530 aname => 'SHIP_TO_CUSTOMER_NUMBER');
531
532 RETURN l_ship_to_customer_number;
533
534 END get_ship_to_customer_number;
535
536
537 -- Tax Ex Certification Number
538 FUNCTION get_tax_ex_cert_num (p_transaction_id IN NUMBER)
539 RETURN VARCHAR2 IS
540
541 l_tax_ex_cert_num wf_item_attribute_values.text_value%TYPE;
542
543 BEGIN
544
545 l_tax_ex_cert_num := wf_engine.GetItemAttrText(
546 itemtype => c_item_type,
547 itemkey => p_transaction_id,
548 aname => 'TAX_EX_CERT_NUM');
549
550 RETURN l_tax_ex_cert_num;
551
552 END get_tax_ex_cert_num;
553
554
555 -- primary salesperson user name
556 FUNCTION get_salesrep_user_name (p_transaction_id IN NUMBER)
557 RETURN VARCHAR2 IS
558
559 l_salesrep_user_name wf_item_attribute_values.text_value%TYPE;
560
561 BEGIN
562
563 l_salesrep_user_name := wf_engine.GetItemAttrText(
564 itemtype => c_item_type,
565 itemkey => p_transaction_id,
569
566 aname => 'SALESREP_USER_NAME');
567
568 RETURN l_salesrep_user_name;
570 END get_salesrep_user_name;
571
572 -- transaction type name
573 FUNCTION get_transaction_type_name (p_transaction_id IN NUMBER)
574 RETURN VARCHAR2 IS
575
576
577 CURSOR trx_types IS
578 SELECT tt.name
579 FROM ar_lookups l,
580 ra_cm_requests r,
581 ra_customer_trx t,
582 ra_cust_trx_types tt
583 WHERE r.request_id = p_transaction_id
584 AND r.cm_reason_code = l.lookup_code
585 AND l.lookup_type = 'CREDIT_MEMO_REASON'
586 AND t.customer_trx_id = r.customer_trx_id
587 AND t.cust_trx_type_id = tt.cust_trx_type_id;
588
589 l_trx_type_name wf_item_attribute_values.text_value%TYPE;
590
591 BEGIN
592
593 OPEN trx_types;
594 FETCH trx_types INTO l_trx_type_name;
595 CLOSE trx_types;
596
597 RETURN l_trx_type_name;
598
599 END get_transaction_type_name;
600
601 -- transaction context
602 FUNCTION get_transaction_context (p_transaction_id IN NUMBER)
603 RETURN VARCHAR2 IS
604
605 CURSOR trx_types IS
606 SELECT interface_line_context
607 FROM ar_lookups l,
608 ra_cm_requests r,
609 ra_customer_trx_lines tl
610 WHERE r.request_id = p_transaction_id
611 AND r.cm_reason_code = l.lookup_code
612 AND l.lookup_type = 'CREDIT_MEMO_REASON'
613 AND tl.customer_trx_id = r.customer_trx_id
614 AND tl.interface_line_context IS NOT NULL
615 AND rownum = 1;
616
617 l_trx_type_name wf_item_attribute_values.text_value%TYPE;
618
619 BEGIN
620
621 OPEN trx_types;
622 FETCH trx_types INTO l_trx_type_name;
623 CLOSE trx_types;
624
625 RETURN l_trx_type_name;
626
627 END get_transaction_context;
628
629
630 END ar_ame_cm_attributes_api;