[Home] [Help]
PACKAGE: APPS.PQP_GB_PSI_BASIC_DATA
Source
1 PACKAGE PQP_GB_PSI_BASIC_DATA AS
2 -- /* $Header: pqpgbpsibas.pkh 120.0.12000000.1 2007/01/16 04:09:50 appldev noship $ */
3
4 --
5 -- Debug Variables.
6 --
7 g_legislation_code per_business_groups.legislation_code%TYPE := 'GB';
8 g_debug BOOLEAN := hr_utility.debug_enabled;
9 g_effective_date DATE;
10 g_extract_type VARCHAR2(100);
11
12 g_proc_name VARCHAR2(61):= 'PQP_GB_PSI_BASIC_DATA.';
13
14 --
15 -- Global Varibales
16 --
17 g_business_group_id NUMBER := NULL; -- IMPORTANT TO KEEP NULL
18 g_assignment_id NUMBER := NULL; -- IMPORTANT TO KEEP NULL
19
20 g_person_id NUMBER := NULL;
21 g_person_dtl per_all_people_f%rowtype;
22 g_assignment_dtl per_all_assignments_f%rowtype;
23
24 g_bank_detail_report_y_n VARCHAR2(2) := NULL;
25 g_bank_details_found VARCHAR2(1) := NULL;
26 g_current_run VARCHAR2(10):= NULL;
27 g_altkey VARCHAR2(12):= NULL;
28
29 -- for include_events
30 g_pay_proc_evt_tab ben_ext_person.t_detailed_output_table;
31
32 -- globals set by set_shared_globals
33 g_paypoint VARCHAR2(5) := NULL;
34 g_cutover_date DATE;
35 g_ext_dfn_id NUMBER;
36 --
37 g_marital_status_mapping pqp_utilities.t_config_values;
38
39 --
40 -- For location code to be fetched from location EIT
41 --
42 CURSOR csr_location_code (p_location_id NUMBER)
43 IS
44 select hlei.lei_information2
45 from hr_location_extra_info hlei
46 where hlei.location_id = p_location_id
47 and hlei.information_type = 'PQP_GB_PENSERV_LOCATION_INFO';
48
49 --
50 -- For hire date to be fetched from person form
51 --
52 CURSOR csr_person_hire_date
53 (p_business_group_id NUMBER
54 ,p_effective_date DATE
55 ,p_person_id NUMBER
56 ) IS
57 select papf.original_date_of_hire
58 from per_all_people_f papf
59 where papf.person_id = p_person_id
60 and papf.business_group_id = p_business_group_id
61 and p_effective_date
62 between papf.effective_start_date and papf.effective_end_date;
63
64
65 --
66 -- For fetching bank details of an assignment
67 --
68 CURSOR csr_bank_details
69 (p_business_group_id NUMBER
70 ,p_effective_date DATE
71 ,p_assignment_id NUMBER
72 ) IS
73 select pea.segment3, pea.segment4, pea.segment6, pea.segment7 --personal.external_account_id
74 from PAY_PERSONAL_PAYMENT_METHODS_F pppm,pay_external_accounts pea
75 where pppm.external_account_id = pea.external_account_id
76 and pppm.business_group_id = p_business_group_id
77 and pppm.assignment_id = p_assignment_id
78 and p_effective_date
79 between pppm.effective_start_date and pppm.effective_end_date;
80
81 -- global to hold bank account details
82 g_asg_bank_details csr_bank_details%ROWTYPE;
83
84 --
85 -- determine multiple_assignments
86 --
87 CURSOR csr_mult_assignment_y_n
88 (p_business_group_id NUMBER
89 ,p_effective_date DATE
90 ,p_person_id NUMBER
91 )
92 IS
93 select count(distinct assignment_id)
94 from per_all_assignments_f paaf
95 where p_effective_date
96 between paaf.effective_start_date and paaf.effective_end_date
97 and person_id in
98 (select person_id
99 from per_all_people_f
100 where national_identifier = (
101 select national_identifier
102 from per_people_f
103 where person_id = p_person_id and rownum = 1));
104
105
106 --
107 -- determine spouse_dob
108 --
109 CURSOR csr_spouse_dob
110 (p_business_group_id NUMBER
111 ,p_effective_date DATE
112 ,p_person_id NUMBER
113 )
114 IS
115 select papf.date_of_birth
116 from PER_CONTACT_RELATIONSHIPS pcr, per_all_people_f papf
117 where pcr.person_id = p_person_id
118 and pcr.contact_person_id = papf.person_id
119 and papf.business_group_id = p_business_group_id
120 and pcr.business_group_id = p_business_group_id
121 and pcr.contact_type = 'S'
122 and p_effective_date
123 between pcr.date_start and NVL(pcr.date_end,to_date('31/12/4712','DD/MM/YYYY'));
124
125
126 -- ----------------------------------------------------------------------------
127 -- |------------------------< Function Definitions >---------------------------|
128 -- ----------------------------------------------------------------------------
129
130 --
131 -- basic extract main function
132 --
133 FUNCTION basic_extract_main
134 (p_business_group_id IN NUMBER -- context
135 ,p_effective_date IN DATE -- context
136 ,p_assignment_id IN NUMBER -- context
137 ,p_rule_parameter IN VARCHAR2 -- parameter
138 ,p_output OUT NOCOPY VARCHAR2
139 )
140 RETURN number;
141
142
143 FUNCTION chk_basic_data_cutover_crit
144 (p_business_group_id IN NUMBER
145 ,p_effective_date IN DATE
146 ,p_assignment_id IN NUMBER
147 )RETURN VARCHAR2;
148
149 FUNCTION chk_basic_data_periodic_crit
150 (p_business_group_id IN NUMBER
151 ,p_effective_date IN DATE
152 ,p_assignment_id IN NUMBER
153 )RETURN VARCHAR2;
154
155 -- Debug
156 PROCEDURE DEBUG (
157 p_trace_message IN VARCHAR2
158 ,p_trace_location IN NUMBER DEFAULT NULL
159 );
160
161 -- Debug_Enter
162 PROCEDURE debug_enter (
163 p_proc_name IN VARCHAR2
164 ,p_trace_on IN VARCHAR2 DEFAULT NULL
165 );
166
167 -- Debug_Exit
168 PROCEDURE debug_exit (
169 p_proc_name IN VARCHAR2
170 ,p_trace_off IN VARCHAR2 DEFAULT NULL
171 );
172
173 -- Debug Others
174 PROCEDURE debug_others (
175 p_proc_name IN VARCHAR2
176 ,p_proc_step IN NUMBER DEFAULT NULL
177 );
178
179 FUNCTION basic_data_post_processing RETURN VARCHAR2;
180
181
182 END PQP_GB_PSI_BASIC_DATA;