[Home] [Help]
PACKAGE BODY: APPS.PAY_KW_USER_FUNCTION
Source
1 package body pay_kw_user_function as
2 /* $Header: pykwrunf.pkb 120.0 2005/05/29 06:39:31 appldev noship $ */
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_SI_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
77 cursor csr_get_id_flex_num is
78 SELECT id_flex_num
79 FROM FND_ID_FLEX_STRUCTURES_VL
80 WHERE (ID_FLEX_STRUCTURE_CODE = 'KW_STATUTORY_INFO')
81 and (APPLICATION_ID=800)
82 and (ID_FLEX_CODE='SCL') ;
83
84 cursor csr_get_formula_id ( p_id_flex_num number) is
85 select HOI2.org_information1
86 from hr_organization_units HOU
87 ,hr_organization_information HOI1
88 ,hr_organization_information HOI2
89 ,hr_soft_coding_keyflex HSCK
90 ,per_all_assignments_f PAA
91 where HOU.business_group_id = p_business_group_id
92 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
93 to_date('4712/12/31','YYYY/MM/DD'))
94 and HOU.organization_id = HOI1.organization_id
95 and HOI1.org_information_context = 'CLASS'
96 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
97 and HOI1.organization_id = HOI2.organization_id
98 and PAA.assignment_id = p_assignment_id
99 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
100 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
101 and HSCK.id_flex_num = p_id_flex_num
102 and decode(HSCK.id_flex_num,p_id_flex_num,to_number(HSCK.segment1),-9999) = HOU.organization_id
103 and HOI2.org_information_context = 'KW_CONTRIBUTORY_WAGE_REF_FF';
104 l_formula_id NUMBER;
105 l_inputs ff_exec.inputs_t;
106 l_outputs ff_exec.outputs_t;
107 l_value NUMBER;
108 l_id_flex_num FND_ID_FLEX_STRUCTURES_VL.id_flex_num %type;
109
110 begin
111
112 open csr_get_id_flex_num;
113 fetch csr_get_id_flex_num into l_id_flex_num;
114 close csr_get_id_flex_num;
115
116 open csr_get_formula_id(l_id_flex_num);
117 fetch csr_get_formula_id into l_formula_id;
118 close csr_get_formula_id;
119
120 l_inputs(1).name := 'ASSIGNMENT_ID';
121 l_inputs(1).value := p_assignment_id;
122 l_inputs(2).name := 'DATE_EARNED';
123 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
124 l_inputs(3).name := 'BUSINESS_GROUP_ID';
125 l_inputs(3).value := p_business_group_id;
126 l_inputs(4).name := 'PAYROLL_ID';
127 l_inputs(4).value := p_payroll_id;
128 l_inputs(5).name := 'PAYROLL_ACTION_ID';
129 l_inputs(5).value := p_payroll_action_id;
130 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
131 l_inputs(6).value := p_assignment_action_id;
132 l_inputs(7).name := 'TAX_UNIT_ID';
133 l_inputs(7).value := p_tax_unit_id;
134 --l_inputs(8).name := 'BALANCE_DATE';
135 --l_inputs(8).value := fnd_date.date_to_canonical(p_balance_date);
136 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
137 l_inputs(8).value := p_element_entry_id;
138 l_inputs(9).name := 'ELEMENT_TYPE_ID';
139 l_inputs(9).value := p_element_type_id;
140 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
141 l_inputs(10).value := p_original_entry_id;
142 --l_inputs(11).name := 'JURISDICTION_CODE';
143 --l_inputs(11).value := p_jurisdiction_code;
144 --l_inputs(11).name := 'TAX_GROUP';
145 --l_inputs(11).value := p_tax_group;
146 --l_inputs(12).name := 'SOURCE_ID';
147 --l_inputs(12).value := p_source_id;
148 --l_inputs(12).name := 'SOURCE_TEXT';
149 --l_inputs(12).value := p_source_text;
150 l_outputs(1).name := 'CONTRIBUTORY_WAGE_REFERENCE';
151 if l_formula_id is not null then
152 run_formula (l_formula_id
153 ,p_date_earned
154 ,l_inputs
155 ,l_outputs);
156 l_value := NVL(l_outputs(l_outputs.first).value,0);
157 else
158 l_value := 0;
159 end if;
160 return(l_value);
161 end run_SI_formula;
162
163 end pay_kw_user_function;