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