DBA Data[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;