1 PACKAGE BODY PA_BL_UTILS AS
2 /* $Header: PAXCCBLB.pls 115.0 99/09/29 14:25:53 porting shi $*/
3
4
5 --===========================================================================
6 -- Function Name : get_supplier_or_emp_name
7 -- Description : Gets the supplier name if the expenditure system linkage
8 -- function is of type 'VI'. Otherwise, gets the employee
9 -- full name.
10 -- Parameters : v_expenditure_item_id - Expenditure Item ID
11 -- v_sys_linkage_func - system linkage function
12 -- v_creation_date - Expenditure Item Date
13 -- v_person_id - Incurred by Person ID
14 -- Return : either the supplier name or the employee full name.
15 --===========================================================================
16 FUNCTION get_supplier_or_emp_name
17 (v_expenditure_item_id IN pa_expenditure_items_all.expenditure_item_id%TYPE,
18 v_sys_linkage_func IN pa_expenditure_items_all.system_linkage_function%TYPE,
19 v_creation_date IN pa_expenditure_items_all.expenditure_item_date%TYPE,
20 v_person_id IN pa_expenditures_all.incurred_by_person_id%TYPE)
21 RETURN VARCHAR2
22 IS
23 v_emp_name per_people_f.full_name%TYPE := NULL;
24 v_vendor_id po_vendors.vendor_id%TYPE;
25 v_vendor_name po_vendors.vendor_name%TYPE := NULL;
26 v_sup_err_msg VARCHAR2(20) := 'NO SUPPLIER FOUND';
27 v_emp_err_msg VARCHAR2(20) := 'NO EMPLOYEE FOUND';
28 BEGIN
29 IF v_sys_linkage_func = 'VI' THEN
30 BEGIN
31 SELECT TO_NUMBER(system_reference1)
32 INTO v_vendor_id
33 FROM pa_cost_distribution_lines_all
34 WHERE expenditure_item_id = v_expenditure_item_id
35 AND line_num = 1;
36
37 IF v_vendor_id IS NOT NULL THEN
38 SELECT vendor_name
39 INTO v_vendor_name
40 FROM po_vendors
41 WHERE vendor_id = v_vendor_id;
42 END IF;
43
44 RETURN v_vendor_name;
45 EXCEPTION
46 WHEN NO_DATA_FOUND THEN
47 RETURN v_sup_err_msg;
48 END;
49 ELSE
50 BEGIN
51 SELECT full_name
52 INTO v_emp_name
53 FROM per_people_f
54 WHERE v_person_id = person_id
55 AND v_creation_date BETWEEN effective_start_date
56 AND NVL(effective_end_date, v_creation_date)
57 AND ROWNUM < 2;
58
59 RETURN v_emp_name;
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 RETURN v_emp_err_msg;
63 END;
64 END IF;
65
66 END get_supplier_or_emp_name;
67
68
69 --===========================================================================
70 -- Function Name : get_organization_name
71 -- Description : Get the non-labor organization name if the system linkage
72 -- function is of type 'USG'. Otherwise, gets either the
73 -- incurred by organization name or the override to
74 -- organization name (depending on whether override to
75 -- organization has been specified).
76 -- Parameters : v_sys_linkage_fn - system linkage function
77 -- v_non_labor_orgid - Non Labor Organization ID
78 -- v_override_orgid - Override To Organization ID
79 -- v_incurred_by_orgid - Incurred by Organization ID
80 -- Return : organization name
81 --===========================================================================
82 FUNCTION get_organization_name
83 (v_sys_linkage_fn IN pa_expenditure_items_all.system_linkage_function%TYPE,
84 v_non_labor_orgid IN pa_expenditure_items_all.organization_id%TYPE,
85 v_override_orgid IN pa_expenditure_items_all.override_to_organization_id%TYPE,
86 v_incurred_by_orgid IN pa_expenditures_all.incurred_by_organization_id%TYPE)
87 RETURN VARCHAR2
88 IS
89 v_org_id hr_all_organization_units_tl.organization_id%TYPE;
90 v_org_name hr_all_organization_units_tl.name%TYPE;
91 v_error_msg VARCHAR2(25) := 'NO ORGANIZATION FOUND';
92 BEGIN
93
94 -- get the appropriate organization id
95 IF v_sys_linkage_fn = 'USG' THEN
96 v_org_id := v_non_labor_orgid;
97 ELSE
98 v_org_id := NVL(v_override_orgid, v_incurred_by_orgid);
99 END IF;
100
101 -- get organization name
102 SELECT name
103 INTO v_org_name
104 FROM hr_all_organization_units_tl
105 WHERE organization_id = v_org_id
106 AND decode(organization_id, null, '1', language)
107 = decode(organization_id, null, '1', userenv('lang'));
108
109 RETURN v_org_name;
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112 RETURN v_error_msg;
113
114 END get_organization_name;
115
116
117 END PA_BL_UTILS;
[Home] [Help]