eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.PAY_GB_ENROLL_PENSION

Source


1 PACKAGE PAY_GB_ENROLL_PENSION
2 /* $Header: pygbpaep.pkh 120.16.12020000.3 2013/03/28 10:22:13 rsadhana noship $ */
3 AUTHID CURRENT_USER AS
4   G_END_OF_TIME DATE :=fnd_date.canonical_to_date('4712/12/31 00:00:00');
5   G_ELEMENT_NAME           CONSTANT PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE := 'Pensions Information';
6   G_QUALIFYING_SCHEME_NAME CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Qualifying Scheme Name';
7   G_AGG_EARNINGS_PENSIONS  CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Aggregate Earnings';
8   G_AUTO_ENROLLMENT_DATE   CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Auto Enrollment Date';
9   G_QUALIFYING_PS_EXISTS   CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Qualifying scheme exists';
10   G_POSTPONEMENT_TYPE      CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Postponement Type';
11   G_POSTPONEMENT_END_DATE  CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Postponement End Date';
12   G_EMPLOYEE_CLASS         CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Pension Classification';
13   G_OPT_IN_DATE            CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Opt in Date';
14   G_OPT_OUT_DATE           CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Opt Out Date';
15   G_OPT_OUT_END_DATE       CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Opt Out Period End Date';
16   G_MAIN_ENTRY             CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Main Entry for Aggregation';
17   G_TOTAL_EARNING_PRP      CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Total Earning PRP';
18   G_ELIGIBLE_JH_DATE       CONSTANT PAY_INPUT_VALUES_F.NAME%TYPE          := 'Eligible Job Holder Date';
19 
20 G_EXCEPTION_NO_UPDATE EXCEPTION;-- To be used only if error is to be raised, to be used after populate_run_msg
21 G_ERROR VARCHAR2(1):='N';
22 --Payroll Period
23 G_PAYROLL_START_DATE DATE;
24 G_PAYROLL_END_DATE DATE;
25 G_PAYROLL_DATE_PAID DATE;
26 
27 G_PRP_START_DATE DATE;
28 G_PRP_END_DATE DATE;
29 G_PRP_DATE_PAID DATE;
30 
31 G_EARN_START_DATE DATE;
32 G_EARN_END_DATE DATE;
33 G_EARN_DATE_PAID DATE;
34 
35 G_MODE VARCHAR2(20);
36 G_TIME_PERIOD_ID PER_TIME_PERIODS.TIME_PERIOD_ID%TYPE;
37 G_PAYROLL_ACTION_ID NUMBER;
38 G_PAYROLL_PERIOD_TYPE PAY_PAYROLLS_F.PERIOD_TYPE%TYPE;
39 G_PAYROLL_ID PER_TIME_PERIODS.PAYROLL_ID%TYPE;
40 G_PAYROLL_NAME   PAY_PAYROLLS_F.PAYROLL_NAME%TYPE;
41 G_PERIOD_NAME  PER_TIME_PERIODS.PERIOD_NAME%TYPE;
42 G_BUSINESS_GROUP_ID PAY_PAYROLLS_F.BUSINESS_GROUP_ID%TYPE;
43 G_STATE_PENSION_AGE_MEN NUMBER := 65; -- Check
44 G_PENSION_STAGING_DATE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION2%TYPE;
45 G_WORKER_POSTPONEMENT_RULE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION3%TYPE;
46 G_ELIGIBLE_POSTPONEMENT_RULE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION4%TYPE;
47 G_DEFINED_BALANCE_ID NUMBER := NULL;
48 
49 G_EMPLOYER_COMPONENT PQP_GB_PENSION_SCHEMES_V.ELEMENT_NAME%TYPE       :=NULL;
50 G_DEFAULT_SCHEME_NAME PQP_GB_PENSION_SCHEMES_V.ELEMENT_NAME%TYPE             :=NULL;
51 G_DEFAULT_SCH_ELEMENT_NAME PQP_GB_PENSION_SCHEMES_V.PENSION_SCHEME_NAME%TYPE :=NULL;
52 --------------------
53 CURSOR C_DEF_BAL IS
54 SELECT PDB.DEFINED_BALANCE_ID
55 FROM
56 PAY_BALANCE_TYPES PBT,
57 PAY_DEFINED_BALANCES PDB,
58 PAY_BALANCE_DIMENSIONS PBD
59 WHERE PDB.BALANCE_TYPE_ID=PBT.BALANCE_TYPE_ID
60 AND   PBT.BALANCE_NAME LIKE 'Pension Qualifying Earnings'
61 AND   PBD.DATABASE_ITEM_SUFFIX='_ASG_RUN'
62 AND   PBD.BALANCE_DIMENSION_ID=PDB.BALANCE_DIMENSION_ID;
63 
64 
65   --To fetch Pension information element data
66   CURSOR C_PENSION_ELEMENT_INF
67   IS
68     SELECT PETF.ELEMENT_TYPE_ID G_ELEMENT_TYPE_ID,
69       MAX(DECODE(TRIM(PIVF.NAME),G_QUALIFYING_SCHEME_NAME,PIVF.INPUT_VALUE_ID, NULL)) G_QUALIFYING_SCHEME_NAME_ID,
70       MAX(DECODE(TRIM(PIVF.NAME),G_AGG_EARNINGS_PENSIONS,PIVF.INPUT_VALUE_ID, NULL)) G_AGG_EARNINGS_PENSIONS_ID,
71       MAX(DECODE(TRIM(PIVF.NAME),G_AUTO_ENROLLMENT_DATE   ,PIVF.INPUT_VALUE_ID, NULL)) G_AUTO_ENROLLMENT_DATE_ID,
72       MAX(DECODE(TRIM(PIVF.NAME),G_QUALIFYING_PS_EXISTS,PIVF.INPUT_VALUE_ID, NULL)) G_QUALIFYING_PS_EXISTS_ID,
73       MAX(DECODE(TRIM(PIVF.NAME),G_POSTPONEMENT_END_DATE,PIVF.INPUT_VALUE_ID, NULL)) G_POSTPONEMENT_END_DATE_ID,
74       MAX(DECODE(TRIM(PIVF.NAME),G_POSTPONEMENT_TYPE ,PIVF.INPUT_VALUE_ID, NULL)) G_POSTPONEMENT_TYPE_ID,
75       MAX(DECODE(TRIM(PIVF.NAME),G_EMPLOYEE_CLASS,PIVF.INPUT_VALUE_ID, NULL)) G_EMPLOYEE_CLASS_ID,
76       MAX(DECODE(TRIM(PIVF.NAME),G_OPT_IN_DATE,PIVF.INPUT_VALUE_ID, NULL)) G_OPT_IN_DATE_ID,
77       MAX(DECODE(TRIM(PIVF.NAME),G_OPT_OUT_DATE,PIVF.INPUT_VALUE_ID, NULL)) G_OPT_OUT_DATE_ID,
78 	  MAX(DECODE(TRIM(PIVF.NAME),G_OPT_OUT_END_DATE,PIVF.INPUT_VALUE_ID, NULL)) G_OPT_OUT_END_DATE_ID,
79 	  MAX(DECODE(TRIM(PIVF.NAME),G_MAIN_ENTRY,PIVF.INPUT_VALUE_ID, NULL)) G_MAIN_ENTRY_ID,
80 	  MAX(DECODE(TRIM(PIVF.NAME),G_TOTAL_EARNING_PRP,PIVF.INPUT_VALUE_ID, NULL)) G_TOTAL_EARNING_PRP_ID,
81       MAX(DECODE(TRIM(PIVF.NAME),G_ELIGIBLE_JH_DATE,PIVF.INPUT_VALUE_ID, NULL)) G_ELIGIBLE_JH_DATE_ID
82     FROM PAY_ELEMENT_TYPES_F PETF,
83       PAY_INPUT_VALUES_F PIVF
84     WHERE PETF.ELEMENT_NAME  = G_ELEMENT_NAME
85     AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
86 --	AND G_PRP_END_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
87 --	AND G_PRP_END_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
88   AND G_PRP_END_DATE >= ALL(PETF.EFFECTIVE_START_DATE,PIVF.EFFECTIVE_START_DATE )
89   AND G_PRP_START_DATE <= ALL(PETF.EFFECTIVE_END_DATE,PIVF.EFFECTIVE_END_DATE )
90     GROUP BY PETF.ELEMENT_TYPE_ID;
91 
92 	-- Element Details -- Input Values' Id
93   G_ELEMENT_DETAILS C_PENSION_ELEMENT_INF%ROWTYPE;
94 
95   CURSOR C_PENSION_INPUT_VALUES(L_ELEMENT_ENTRY_ID NUMBER)
96   IS
97     SELECT MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID,SCREEN_ENTRY_VALUE, NULL)) G_QUALIFYING_SCHEME_NAME,
98       MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID,SCREEN_ENTRY_VALUE, NULL)) G_AGG_EARNINGS_PENSIONS,
99       MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID,SCREEN_ENTRY_VALUE, NULL)) G_AUTO_ENROLLMENT_DATE   ,
100       MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID,SCREEN_ENTRY_VALUE, NULL)) G_QUALIFYING_PS_EXISTS,
101       MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID,SCREEN_ENTRY_VALUE, NULL)) G_POSTPONEMENT_END_DATE,
102       MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID,HR_GENERAL.DECODE_LOOKUP('GB_PENSION_POSTPONEMENT_TYPES',SCREEN_ENTRY_VALUE), NULL)) G_POSTPONEMENT_TYPE ,
103       MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID,HR_GENERAL.DECODE_LOOKUP('GB_PENSION_WORKER_TYPES',SCREEN_ENTRY_VALUE), NULL)) G_EMPLOYEE_CLASS,
104       MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID,SCREEN_ENTRY_VALUE, NULL)) G_OPT_IN_DATE,
105       MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_OPT_OUT_DATE_ID,SCREEN_ENTRY_VALUE, NULL)) G_OPT_OUT_DATE,
106 	  MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_OPT_OUT_END_DATE_ID,SCREEN_ENTRY_VALUE, NULL)) G_OPT_OUT_END_DATE,
107 	  MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID,SCREEN_ENTRY_VALUE, NULL)) G_MAIN_ENTRY,
108 	  MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_TOTAL_EARNING_PRP_ID,SCREEN_ENTRY_VALUE, NULL)) G_TOTAL_EARNING_PRP,
109       MAX(DECODE(TRIM(PEEVF.INPUT_VALUE_ID),G_ELEMENT_DETAILS.G_ELIGIBLE_JH_DATE_ID,SCREEN_ENTRY_VALUE, NULL)) G_ELIGIBLE_JH_DATE
110     FROM PAY_ELEMENT_ENTRY_VALUES_F PEEVF
111     WHERE PEEVF.INPUT_VALUE_ID   IN (G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID ,
112 	G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID ,
113 	G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID ,
114 	G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID ,
115 	G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID ,
116 	G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID ,
117 	G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID ,
118 	G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID ,
119 	G_ELEMENT_DETAILS.G_OPT_OUT_DATE_ID ,
120 	G_ELEMENT_DETAILS.G_ELIGIBLE_JH_DATE_ID,
121 	G_ELEMENT_DETAILS.G_OPT_OUT_END_DATE_ID,
122 	G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID,
123 	G_ELEMENT_DETAILS.G_TOTAL_EARNING_PRP_ID)
124   AND PEEVF.ELEMENT_ENTRY_ID   = L_ELEMENT_ENTRY_ID
125 --	AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
126 	  AND G_PRP_END_DATE >= PEEVF.EFFECTIVE_START_DATE
127 	AND G_PRP_START_DATE <= PEEVF.EFFECTIVE_END_DATE
128     GROUP BY PEEVF.ELEMENT_ENTRY_ID;
129 
130 	--Entry values for the assignment
131   P_PENSION_INPUT_VALUES C_PENSION_INPUT_VALUES%ROWTYPE;
132   P_PENSION_INPUT_VALUES_DUMMY C_PENSION_INPUT_VALUES%ROWTYPE;
133 
134   --To get Global Values
135     CURSOR C_GET_GLOBAL_VALUE(P_EFFECTIVE_DATE DATE)
136   IS
137     SELECT MAX(DECODE(GLOBAL_NAME,'QE_WEEKLY_LT', GLOBAL_VALUE ,NULL)) G_QE_WEEKLY_LT ,
138       MAX(DECODE(GLOBAL_NAME,'QE_MONTHLY_LT', GLOBAL_VALUE ,NULL)) G_QE_MONTHLY_LT ,
139       MAX(DECODE(GLOBAL_NAME,'QE_ANNUAL_LT', GLOBAL_VALUE ,NULL)) G_QE_ANNUAL_LT ,
140       MAX(DECODE(GLOBAL_NAME,'QE_WEEKLY_UT', GLOBAL_VALUE ,NULL)) G_QE_WEEKLY_UT ,
141       MAX(DECODE(GLOBAL_NAME,'QE_MONTHLY_UT', GLOBAL_VALUE ,NULL)) G_QE_MONTHLY_UT ,
142       MAX(DECODE(GLOBAL_NAME,'QE_ANNUAL_UT', GLOBAL_VALUE ,NULL)) G_QE_ANNUAL_UT ,
143       MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_WEEKLY', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_WEEKLY ,
144       MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_MONTHLY', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_MONTHLY ,
145       MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_ANNUAL', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_ANNUAL,
146       MAX(DECODE(GLOBAL_NAME,'QE_BIWEEK_LT', GLOBAL_VALUE ,NULL))  G_QE_BIWEEK_LT,
147       MAX(DECODE(GLOBAL_NAME,'QE_LUNAR_LT', GLOBAL_VALUE ,NULL)) G_QE_LUNAR_LT,
148       MAX(DECODE(GLOBAL_NAME,'QE_QUARTERLY_LT', GLOBAL_VALUE ,NULL)) G_QE_QUARTERLY_LT,
149       MAX(DECODE(GLOBAL_NAME,'QE_SEMIYEAR_LT', GLOBAL_VALUE ,NULL)) G_QE_SEMIYEAR_LT,
150       MAX(DECODE(GLOBAL_NAME,'QE_BIWEEK_UT', GLOBAL_VALUE ,NULL))  G_QE_BIWEEK_UT,
151       MAX(DECODE(GLOBAL_NAME,'QE_LUNAR_UT', GLOBAL_VALUE ,NULL))  G_QE_LUNAR_UT,
152       MAX(DECODE(GLOBAL_NAME,'QE_QUARTERLY_UT', GLOBAL_VALUE ,NULL))  G_QE_QUARTERLY_UT,
153       MAX(DECODE(GLOBAL_NAME,'QE_SEMIYEAR_UT', GLOBAL_VALUE ,NULL))  G_QE_SEMIYEAR_UT,
154       MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_BIWEEK', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_BIWEEK,
155       MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_LUNAR', GLOBAL_VALUE ,NULL))  G_AUTO_ENROL_TRIG_LUNAR,
156       MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_QUARTERLY', GLOBAL_VALUE ,NULL))  G_AUTO_ENROL_TRIG_QUARTERLY,
157       MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_SEMIYEAR', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_SEMIYEAR
158     FROM FF_GLOBALS_F FGF
159     WHERE GLOBAL_NAME IN ('QE_ANNUAL_LT', 'QE_WEEKLY_LT', 'QE_MONTHLY_LT', 'QE_ANNUAL_UT', 'QE_WEEKLY_UT', 'QE_MONTHLY_UT', 'AUTO_ENROL_TRIG_ANNUAL', 'AUTO_ENROL_TRIG_WEEKLY', 'AUTO_ENROL_TRIG_MONTHLY',
160 'QE_BIWEEK_LT','QE_LUNAR_LT','QE_QUARTERLY_LT','QE_SEMIYEAR_LT',
161 'QE_BIWEEK_UT','QE_LUNAR_UT','QE_QUARTERLY_UT','QE_SEMIYEAR_UT',
162 'AUTO_ENROL_TRIG_BIWEEK','AUTO_ENROL_TRIG_LUNAR','AUTO_ENROL_TRIG_QUARTERLY','AUTO_ENROL_TRIG_SEMIYEAR')
163 --AND G_PRP_END_DATE BETWEEN FGF.EFFECTIVE_START_DATE AND FGF.EFFECTIVE_END_DATE;
164 --  AND G_PRP_END_DATE >= FGF.EFFECTIVE_START_DATE
165 --  AND G_PRP_START_DATE <= FGF.EFFECTIVE_END_DATE;
166 AND P_EFFECTIVE_DATE BETWEEN FGF.EFFECTIVE_START_DATE AND FGF.EFFECTIVE_END_DATE;
167 
168    --Fetch the Elements details that will contribute to the Seeded Balance for the given assignment
169   CURSOR C_ELEMENT_DETAILS(P_DATE_EARNED DATE , P_ASSIGNMENT_ID NUMBER, P_ELEMENT_TYPE_ID NUMBER)
170   IS
171     SELECT PEEF.ELEMENT_ENTRY_ID,
172       PEEF.ELEMENT_TYPE_ID
173     FROM PAY_ELEMENT_ENTRIES_F PEEF
174     WHERE PEEF.ASSIGNMENT_ID  = P_ASSIGNMENT_ID
175     AND PEEF.ELEMENT_TYPE_ID IN
176       ( SELECT DISTINCT ELEMENT_TYPE_ID
177       FROM pay_sub_classification_rules_f PSCR ,
178         PAY_ELEMENT_CLASSIFICATIONS PEC
179       WHERE PSCR.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
180       AND PEC.CLASSIFICATION_NAME  = 'Pension Qualifying Earnings'
181       AND P_DATE_EARNED BETWEEN PSCR.EFFECTIVE_START_DATE AND PSCR.EFFECTIVE_END_DATE
182       )
183    AND NVL(P_ELEMENT_TYPE_ID,PEEF.ELEMENT_TYPE_ID)=PEEF.ELEMENT_TYPE_ID
184   AND P_DATE_EARNED BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE;
185   --Fetch Screen entry value, if any, attached to the element
186   CURSOR C_SCREEN_ENTRY_VALUE(P_ELEMENT_ENTRY_ID VARCHAR2,P_EFFECTIVE_DATE DATE)
187   IS
188     SELECT PEEVF.SCREEN_ENTRY_VALUE
189     FROM PAY_ELEMENT_ENTRY_VALUES_F PEEVF ,
190       PAY_INPUT_VALUES_F PIVF
191     WHERE PEEVF.ELEMENT_ENTRY_ID = P_ELEMENT_ENTRY_ID
192     AND PIVF.NAME                ='Pay Value'
193     AND PEEVF.INPUT_VALUE_ID     =PIVF.INPUT_VALUE_ID
194     AND P_EFFECTIVE_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
195     AND P_EFFECTIVE_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE;
196   --Fetch formula, if any, attached to the element
197   CURSOR C_PENSIONS_FORMULA_ID(P_ELEMENT_TYPE_ID VARCHAR2,P_EFFECTIVE_DATE DATE)
198   IS
199     SELECT PSPF.FORMULA_ID, PSPF.STATUS_PROCESSING_RULE_ID
200     FROM PAY_STATUS_PROCESSING_RULES_F PSPF
201     WHERE PSPF.ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
202     AND PSPF.ASSIGNMENT_STATUS_TYPE_ID is NULL
203     AND P_EFFECTIVE_DATE BETWEEN PSPF.EFFECTIVE_START_DATE AND PSPF.EFFECTIVE_END_DATE;
204 --Fetch Screen Entry value for an input value
205 CURSOR ELE_SCREEN_VALUE (P_ELEM_ENTRY_ID NUMBER)
206 IS
207 SELECT PEEF.SCREEN_ENTRY_VALUE, PEEF.INPUT_VALUE_ID, UPPER(REPLACE(PIVF.NAME,' ','_')) NAME
208 FROM PAY_ELEMENT_ENTRY_VALUES_F PEEF, PAY_INPUT_VALUES_F PIVF
209 WHERE PEEF.ELEMENT_ENTRY_ID = P_ELEM_ENTRY_ID
210 AND PEEF.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID
211 AND G_PRP_END_DATE >= ALL(PEEF.EFFECTIVE_START_DATE,PIVF.EFFECTIVE_START_DATE )
212 AND G_PRP_START_DATE <= ALL(PEEF.EFFECTIVE_END_DATE,PIVF.EFFECTIVE_END_DATE ) ;
213 --and G_PRP_END_DATE BETWEEN peef.EFFECTIVE_START_DATE and peef.EFFECTIVE_END_DATE
214 --and G_PRP_END_DATE BETWEEN pivf.EFFECTIVE_START_DATE and pivf.EFFECTIVE_END_DATE ;
215 	--To fetch Earnings Period Dates
216   CURSOR C_EARN_PERIOD_DETAILS (P_PAYROLL_ID NUMBER)
217   IS
218     SELECT TIME_PERIOD_ID,
219       START_DATE,
220       END_DATE
221 		,	REGULAR_PAYMENT_DATE
222     FROM PER_TIME_PERIODS PTP
223     WHERE PTP.PAYROLL_ID = P_PAYROLL_ID
224 	AND REGULAR_PAYMENT_DATE BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE
225 ORDER BY START_DATE;
226 
227   G_GLOBAL_VALUE C_GET_GLOBAL_VALUE%ROWTYPE;
228 
229   --To get all the Pension associated Element names that are classified as QUALIFYING
230   CURSOR C_GET_QUALIFYING_PENSION_INFO
231   IS
232     SELECT DISTINCT PPSV.ELEMENT_NAME
233     FROM PQP_GB_PENSION_SCHEMES_V PPSV,
234       PQP_CONFIGURATION_VALUES PCV
235     WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
236     AND PCV.BUSINESS_GROUP_ID      = G_BUSINESS_GROUP_ID
237     AND PCV.PCV_INFORMATION2       = PPSV.ELEMENT_NAME ; --CHECK
238 
239 
240 	--To get the Default Pension details
241   CURSOR C_GET_DEFAULT_PENSION_INFO
242   IS
243     SELECT PCV.PCV_INFORMATION1,
244       PCV.PCV_INFORMATION2,PCV.PCV_INFORMATION3
245     FROM PQP_CONFIGURATION_VALUES PCV
246     WHERE PCV.PCV_INFORMATION_CATEGORY = 'PAY_GB_DEFAULT_PENSIONS_INFO'
247     AND PCV.BUSINESS_GROUP_ID          = G_BUSINESS_GROUP_ID ;
248 
249   G_DEFAULT_PENSION PQP_CONFIGURATION_VALUES.PCV_INFORMATION2%TYPE; -- To hold Default Pension Scheme Name
250 	G_DEFAULT_ELEMENT_ER PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;--Employer Element
251   G_DEFAULT_ELEMENT_EE PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE; --To hold Default Pension Scheme's Element Name
252   G_DEFAULT_PENSION_DUMMY PAY_ELEMENT_TYPE_EXTRA_INFO.EEI_INFORMATION1%TYPE;
253   G_DEFAULT_ELEMENT_DUMMY PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
254 
255 CURSOR get_assignment_number (p_assignment_id number)
256 is
257 select assignment_number from per_all_assignments_f
258 where assignment_id=p_assignment_id
259 AND G_PRP_END_DATE >= EFFECTIVE_START_DATE
260 AND G_PRP_START_DATE <= EFFECTIVE_END_DATE;
261 --and G_PRP_END_DATE between effective_start_date and effective_end_date;
262 
263 PROCEDURE archinit ( p_payroll_action_id IN NUMBER);
264 
265 PROCEDURE range_cursor (pactid IN NUMBER,
266                         sqlstr OUT NOCOPY VARCHAR2);
267 
268 PROCEDURE action_creation   (pactid    in number,
269                                  stperson  in number,
270                                  endperson in number,
271                                  chunk     in number);
272 
273 PROCEDURE archive_code (p_assactid         IN   NUMBER,
274                         p_effective_date   IN   DATE);
275 
276 PROCEDURE deinitialization_code(pactid IN NUMBER);
277 FUNCTION GET_PROFILE_OPTION(p_profile_option_name in varchar)
278 RETURN VARCHAR;
279 
280 FUNCTION GET_AGGREGATED_EARNINGS(
281     P_ASSIGNMENT_ID IN NUMBER,
282     P_REGULAR_PAYMENT_DATE IN DATE,
283     P_PQE_ASG_RUN IN NUMBER)
287   IS
284   RETURN NUMBER;
285   -- Cursor to get Pension Scheme's Elements' Names for the Pension Scheme Name
286   CURSOR C_GET_PENSION_ELEMENTS(P_PENSION_SCHEME_NAME VARCHAR2)
288     SELECT PCV.PCV_INFORMATION2,PCV.PCV_INFORMATION3
289     FROM PQP_CONFIGURATION_VALUES PCV
290     WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
291     AND PCV.BUSINESS_GROUP_ID      = G_BUSINESS_GROUP_ID
292     AND PCV.PCV_INFORMATION1       = P_PENSION_SCHEME_NAME;
293 --ER 14126545
294 TYPE eligiblity_table IS TABLE OF
295 VARCHAR2(2000)
296 INDEX BY VARCHAR2(150);
297 
298 TYPE seg_collection_table IS TABLE OF
299 VARCHAR2(150)
300 INDEX BY BINARY_INTEGER;
301 
302 TYPE collection_table IS TABLE OF
303 eligiblity_table
304 INDEX BY BINARY_INTEGER;
305 
306 TYPE people_group_id_tab IS TABLE OF
307 number
308 INDEX BY BINARY_INTEGER;
309 g_collecttab collection_table;
310 G_DEF_FLAG VARCHAR2(1);
311 --ER 14126545
312 END PAY_GB_ENROLL_PENSION;