1 PACKAGE pay_dk_soe AUTHID CURRENT_USER AS
2 /* $Header: pydksoe.pkh 120.0.12000000.2 2007/05/08 07:12:56 saurai noship $ */
3
4 --
5
6 -- cursor to get the CVR Number
7
8 cursor csr_get_cvr_number (p_assignment_id per_all_assignments_f.assignment_id%TYPE, p_effective_date Date ) is
9 select hoi.org_information1
10 from hr_soft_coding_keyflex sck
11 ,HR_ORGANIZATION_INFORMATION hoi
12 ,per_all_assignments_f paa
13 where paa.assignment_id = p_assignment_id
14 and p_effective_date between paa.effective_start_date and paa.effective_end_date
15 and paa.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
16 and hoi.organization_id = to_number(sck.segment1)
17 and hoi.org_information_context = 'DK_LEGAL_ENTITY_DETAILS';
18
19 -- cursor to get the Pension Provider
20
21 cursor csr_get_pension_provider (p_assignment_id per_all_assignments_f.assignment_id%TYPE, p_effective_date Date ) is
22 select hou.name
23 from hr_soft_coding_keyflex sck
24 ,HR_ORGANIZATION_INFORMATION hoi
25 ,per_all_assignments_f paa
26 ,HR_ORGANIZATION_UNITS hou
27 where paa.assignment_id = p_assignment_id
28 and p_effective_date between paa.effective_start_date and paa.effective_end_date
29 and paa.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
30 and hoi.organization_id = to_number(sck.segment2)
31 and hoi.org_information_context = 'DK_PENSION_PROVIDER_DETAILS'
32 and hoi.organization_id = hou.organization_id;
33
34
35 -- function for fetching the Legal Entity CVR Number or Pension Provider
36
37 FUNCTION get_cvr_or_pension
38 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
39 p_effective_date IN Date,
40 p_org_information_context IN VARCHAR2 )
41 return VARCHAR2;
42
43 -- function for fetching the Union Membership
44
45 FUNCTION get_union_membership
46 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
47 p_effective_date IN Date )
48 return varchar2;
49
50
51 -- function for fetching the Bank Registration Number
52
53 FUNCTION get_bank_reg_number
54 ( p_external_account_id IN NUMBER)
55 return varchar2;
56
57
58 -- Returns SQL string for retrievening Employee information
59 function Employee(p_assignment_action_id number) return long;
60
61 -- Returns Payroll Period Information
62 function Period(p_assignment_action_id number) return long;
63
64 -- Returns Payment Information
65 function PrePayments(p_assignment_action_id number) return long;
66
67 /* Added for Pension changes */
68
69 function get_pp_name(p_effective_date date
70 ,p_run_result_id number)
71 return varchar2;
72
73 function getElements(p_assignment_action_id number
74 ,p_element_set_name varchar2)
75 return long;
76
77 function Elements1(p_assignment_action_id number )
78 --,p_element_set_name varchar2)
79 return long;
80 function Elements2(p_assignment_action_id number )
81 --,p_element_set_name varchar2)
82 return long;
83
84 function Elements3(p_assignment_action_id number )
85 --,p_element_set_name varchar2)
86 return long;
87 /* Added for display of Pension Provider balances */
88 function getBalances(p_assignment_action_id number)
89 return long;
90
91
92
93 -- end of package
94 END pay_dk_soe;