[Home] [Help]
PACKAGE: APPS.PQP_GB_PSI_BASIC_DATA
Source
1 PACKAGE PQP_GB_PSI_BASIC_DATA AUTHID CURRENT_USER AS
2 -- /* $Header: pqpgbpsibas.pkh 120.0.12010000.4 2009/09/09 05:12:48 jvaradra ship $ */
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 -- For Bug 8790100
40 g_title_change_exists VARCHAR2(1) := 'N';
41 g_honors_change_exists VARCHAR2(1) := 'N';
42 g_location_change_exists VARCHAR2(1) := 'N';
43 g_prevsur_change_exists VARCHAR2(1) := 'N';
44 g_midname_change_exists VARCHAR2(1) := 'N';
45
46 --
47 -- For location code to be fetched from location EIT
48 --
49 CURSOR csr_location_code (p_location_id NUMBER)
50 IS
51 select hlei.lei_information2
52 from hr_location_extra_info hlei
53 where hlei.location_id = p_location_id
54 and hlei.information_type = 'PQP_GB_PENSERV_LOCATION_INFO';
55
56 --
57 -- For hire date to be fetched from person form
58 --
59 CURSOR csr_person_hire_date
60 (p_business_group_id NUMBER
61 ,p_effective_date DATE
62 ,p_person_id NUMBER
63 ) IS
64 select papf.original_date_of_hire
65 from per_all_people_f papf
66 where papf.person_id = p_person_id
67 and papf.business_group_id = p_business_group_id
68 and p_effective_date
69 between papf.effective_start_date and papf.effective_end_date;
70
71
72 --
73 -- For fetching bank details of an assignment
74 --
75 CURSOR csr_bank_details
76 (p_business_group_id NUMBER
77 ,p_effective_date DATE
78 ,p_assignment_id NUMBER
79 ) IS
80 select pea.segment3, pea.segment4, pea.segment6, pea.segment7 --personal.external_account_id
81 from PAY_PERSONAL_PAYMENT_METHODS_F pppm,pay_external_accounts pea
82 where pppm.external_account_id = pea.external_account_id
83 and pppm.business_group_id = p_business_group_id
84 and pppm.assignment_id = p_assignment_id
85 and p_effective_date
86 between pppm.effective_start_date and pppm.effective_end_date
87 Order by pppm.priority; --Added for Bug 8270421
88
89 -- global to hold bank account details
90 g_asg_bank_details csr_bank_details%ROWTYPE;
91
92 --
93 -- determine multiple_assignments
94 --
95 CURSOR csr_mult_assignment_y_n
96 (p_business_group_id NUMBER
97 ,p_effective_date DATE
98 ,p_person_id NUMBER
99 )
100 IS
101 select count(distinct assignment_id)
102 from per_all_assignments_f paaf
103 where p_effective_date
104 between paaf.effective_start_date and paaf.effective_end_date
105 and assignment_type <> 'A' --Added for bug 7693193
106 and person_id in
107 (select person_id
108 from per_all_people_f
109 where national_identifier = (
110 select national_identifier
111 from per_people_f
112 where person_id = p_person_id and rownum = 1));
113
114
115 --
116 -- determine spouse_dob
117 --
118 CURSOR csr_spouse_dob
119 (p_business_group_id NUMBER
120 ,p_effective_date DATE
121 ,p_person_id NUMBER
122 )
123 IS
124 select papf.date_of_birth
125 from PER_CONTACT_RELATIONSHIPS pcr, per_all_people_f papf
126 where pcr.person_id = p_person_id
127 and pcr.contact_person_id = papf.person_id
128 and papf.business_group_id = p_business_group_id
129 and pcr.business_group_id = p_business_group_id
130 and pcr.contact_type = 'S'
131 and p_effective_date
132 between pcr.date_start and NVL(pcr.date_end,to_date('31/12/4712','DD/MM/YYYY'));
133
134
135 -- ----------------------------------------------------------------------------
136 -- |------------------------< Function Definitions >---------------------------|
137 -- ----------------------------------------------------------------------------
138
139 --
140 -- basic extract main function
141 --
142 FUNCTION basic_extract_main
143 (p_business_group_id IN NUMBER -- context
144 ,p_effective_date IN DATE -- context
145 ,p_assignment_id IN NUMBER -- context
146 ,p_rule_parameter IN VARCHAR2 -- parameter
147 ,p_output OUT NOCOPY VARCHAR2
148 )
149 RETURN number;
150
151
152 FUNCTION chk_basic_data_cutover_crit
153 (p_business_group_id IN NUMBER
154 ,p_effective_date IN DATE
155 ,p_assignment_id IN NUMBER
156 )RETURN VARCHAR2;
157
158 FUNCTION chk_basic_data_periodic_crit
159 (p_business_group_id IN NUMBER
160 ,p_effective_date IN DATE
161 ,p_assignment_id IN NUMBER
162 )RETURN VARCHAR2;
163
164 -- Debug
165 PROCEDURE DEBUG (
166 p_trace_message IN VARCHAR2
167 ,p_trace_location IN NUMBER DEFAULT NULL
168 );
169
170 -- Debug_Enter
171 PROCEDURE debug_enter (
172 p_proc_name IN VARCHAR2
173 ,p_trace_on IN VARCHAR2 DEFAULT NULL
174 );
175
176 -- Debug_Exit
177 PROCEDURE debug_exit (
178 p_proc_name IN VARCHAR2
179 ,p_trace_off IN VARCHAR2 DEFAULT NULL
180 );
181
182 -- Debug Others
183 PROCEDURE debug_others (
184 p_proc_name IN VARCHAR2
185 ,p_proc_step IN NUMBER DEFAULT NULL
186 );
187
188 FUNCTION basic_data_post_processing RETURN VARCHAR2;
189
190
191 END PQP_GB_PSI_BASIC_DATA;