1 package body pay_sa_user_function as
2 /* $Header: pysarunf.pkb 120.0.12010000.2 2008/10/31 11:59:17 bkeshary ship $ */
3 g_formula_name ff_formulas_f.formula_name%TYPE;
4 PROCEDURE run_formula(p_formula_id IN NUMBER
5 ,p_effective_date IN DATE
6 ,p_inputs IN ff_exec.inputs_t
7 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
8 l_inputs ff_exec.inputs_t;
9 l_outputs ff_exec.outputs_t;
10 BEGIN
11 hr_utility.set_location('--In Formula ',20);
12 --
13 -- Initialize the formula
14 --
15 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
16 --
17 hr_utility.trace('after ff_exec');
18 -- Set up the input values
19 --
20 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
21 FOR i IN l_inputs.first..l_inputs.last LOOP
22 FOR j IN p_inputs.first..p_inputs.last LOOP
23 IF l_inputs(i).name = p_inputs(j).name THEN
24 l_inputs(i).value := p_inputs(j).value;
25 exit;
26 END IF;
27 END LOOP;
28 END LOOP;
29 END IF;
30 --
31 -- Run the formula
32 --
33 hr_utility.trace('about to exec');
34 ff_exec.run_formula(l_inputs,l_outputs);
35 --
36 -- Populate the output table
37 --
38 IF l_outputs.count > 0 and p_inputs.count > 0 then
39 FOR i IN l_outputs.first..l_outputs.last LOOP
40 FOR j IN p_outputs.first..p_outputs.last LOOP
41 IF l_outputs(i).name = p_outputs(j).name THEN
42 p_outputs(j).value := l_outputs(i).value;
43 exit;
44 END IF;
45 END LOOP;
46 END LOOP;
47 END IF;
48 EXCEPTION
49 /*WHEN hr_formula_error THEN
50 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
51 fnd_message.set_token('1', g_formula_name);
52 fnd_message.raise_error;*/
53 WHEN OTHERS THEN
54 raise;
55 --
56 END run_formula;
57
58 function run_gosi_formula
59 (p_assignment_id IN NUMBER
60 ,p_date_earned IN DATE
61 ,p_business_group_id IN NUMBER
62 ,p_payroll_id IN NUMBER
63 ,p_payroll_action_id IN NUMBER
64 ,p_assignment_action_id IN NUMBER
65 ,p_tax_unit_id IN NUMBER
66 --,p_balance_date IN DATE
67 ,p_element_entry_id IN NUMBER
68 ,p_element_type_id IN NUMBER
69 ,p_original_entry_id IN NUMBER
70 --,p_jurisdiction_code IN VARCHAR2
71 --,p_tax_group IN VARCHAR2
72 --,p_source_id IN NUMBER
73 --,p_source_text IN VARCHAR2
74 )
75 return NUMBER is
76 cursor csr_get_formula_id is
77 select HOI2.org_information1
78 from hr_organization_units HOU
79 ,hr_organization_information HOI1
80 ,hr_organization_information HOI2
81 ,hr_soft_coding_keyflex HSCK
82 ,per_all_assignments_f PAA
83 where HOU.business_group_id = p_business_group_id
84 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
85 to_date('4712/12/31','YYYY/MM/DD'))
86 and HOU.organization_id = HOI1.organization_id
87 and HOI1.org_information_context = 'CLASS'
88 and HOI1.org_information1 = 'HR_LEGAL'
89 and HOI1.organization_id = HOI2.organization_id
90 and PAA.assignment_id = p_assignment_id
91 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
92 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
93 and HSCK.id_flex_num = 20
94 and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id
95 and HOI2.org_information_context = 'SA_GOSI_REFERENCE_FORMULA';
96 l_formula_id NUMBER;
97 l_inputs ff_exec.inputs_t;
98 l_outputs ff_exec.outputs_t;
99 l_value NUMBER;
100 begin
101 open csr_get_formula_id;
102 fetch csr_get_formula_id into l_formula_id;
103 close csr_get_formula_id;
104 l_inputs(1).name := 'ASSIGNMENT_ID';
105 l_inputs(1).value := p_assignment_id;
106 l_inputs(2).name := 'DATE_EARNED';
107 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
108 l_inputs(3).name := 'BUSINESS_GROUP_ID';
109 l_inputs(3).value := p_business_group_id;
110 l_inputs(4).name := 'PAYROLL_ID';
111 l_inputs(4).value := p_payroll_id;
112 l_inputs(5).name := 'PAYROLL_ACTION_ID';
113 l_inputs(5).value := p_payroll_action_id;
114 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
115 l_inputs(6).value := p_assignment_action_id;
116 l_inputs(7).name := 'TAX_UNIT_ID';
117 l_inputs(7).value := p_tax_unit_id;
118 --l_inputs(8).name := 'BALANCE_DATE';
119 --l_inputs(8).value := fnd_date.date_to_canonical(p_balance_date);
120 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
121 l_inputs(8).value := p_element_entry_id;
122 l_inputs(9).name := 'ELEMENT_TYPE_ID';
123 l_inputs(9).value := p_element_type_id;
124 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
125 l_inputs(10).value := p_original_entry_id;
126 --l_inputs(11).name := 'JURISDICTION_CODE';
127 --l_inputs(11).value := p_jurisdiction_code;
128 --l_inputs(11).name := 'TAX_GROUP';
129 --l_inputs(11).value := p_tax_group;
130 --l_inputs(12).name := 'SOURCE_ID';
131 --l_inputs(12).value := p_source_id;
132 --l_inputs(12).name := 'SOURCE_TEXT';
133 --l_inputs(12).value := p_source_text;
134 l_outputs(1).name := 'GOSI_REFERENCE';
135 if l_formula_id is not null then
136 run_formula (l_formula_id
137 ,p_date_earned
138 ,l_inputs
139 ,l_outputs);
140 /* Modified for the bug 7526068 */
141 l_value := NVL(fnd_number.canonical_to_number(l_outputs(l_outputs.first).value),0);
142 else
143 l_value := 0;
144 end if;
145 return(l_value);
146 end run_gosi_formula;
147
148 end pay_sa_user_function;
[Home] [Help]