[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYKRHCL_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYKRHCL_XMLP_PKG AS
2 /* $Header: PAYKRHCLB.pls 120.0 2007/12/13 12:19:58 amakrish noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5
6 select
7 SUBSTR(argument1,INSTR(argument1,'=',1)+1,LENGTH(argument1)),
8 SUBSTR(argument2,INSTR(argument2,'=',1)+1,LENGTH(argument2)),
9 SUBSTR(argument3,INSTR(argument3,'=',1)+1,LENGTH(argument3))
10 into
11 LP_PAYROLL_ACTION_ID, LP_BP_HI_NUMBER , LP_REPORTED_DATE
12 from FND_CONCURRENT_REQUESTS
13 where request_id= FND_GLOBAL.conc_request_id;
14
15 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
16 X_HI_PREM_EE_WO_ADJ := GET_USER_ENTITY_ID('X_HI_PREM_EE_WO_ADJ');
17 X_EARNINGS_SUBJ_HI := GET_USER_ENTITY_ID('X_EARNINGS_SUBJ_HI');
18 X_HI_WORKING_MONTHS := GET_USER_ENTITY_ID('X_HI_WORKING_MONTHS');
19 X_KR_HIA_BUSINESS_PLACE_CODE := GET_USER_ENTITY_ID('X_KR_HIA_BUSINESS_PLACE_CODE');
20 X_KR_HIA_HI_NUMBER := GET_USER_ENTITY_ID('X_KR_HIA_HI_NUMBER');
21 X_KR_HIA_EMPLOYEE_NAME := GET_USER_ENTITY_ID('X_KR_HIA_EMPLOYEE_NAME');
22 X_KR_HIA_REGISTRATION_NUMBER := GET_USER_ENTITY_ID('X_KR_HIA_REGISTRATION_NUMBER');
23 X_KR_HIA_QUALIFIED_DATE := GET_USER_ENTITY_ID('X_KR_HIA_QUALIFIED_DATE');
24 X_KR_HI_NUM_OF_MTHS_PREM_PAID := GET_USER_ENTITY_ID('X_KR_HI_NUM_OF_MTHS_PREM_PAID');
25
26
27
28 RETURN (TRUE);
29 END BEFOREREPORT;
30
31 FUNCTION AFTERREPORT RETURN BOOLEAN IS
32 BEGIN
33 RETURN (TRUE);
34 END AFTERREPORT;
35
36 FUNCTION CF_BUSINESS_GROUPFORMULA RETURN VARCHAR2 IS
37 V_BUSINESS_GROUP HR_ORGANIZATION_UNITS.NAME%TYPE;
38 BEGIN
39 V_BUSINESS_GROUP := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
40 RETURN V_BUSINESS_GROUP;
41 END CF_BUSINESS_GROUPFORMULA;
42
43 FUNCTION CF_LEGISLATION_CODEFORMULA RETURN VARCHAR2 IS
44 V_LEGISLATION_CODE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION9%TYPE := NULL;
45 CURSOR LEGISLATION_CODE(C_BUSINESS_GROUP_ID IN HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE) IS
46 SELECT
47 ORG_INFORMATION9
48 FROM
49 HR_ORGANIZATION_INFORMATION
50 WHERE ORGANIZATION_ID = C_BUSINESS_GROUP_ID
51 AND ORG_INFORMATION9 is not null
52 AND ORG_INFORMATION_CONTEXT = 'Business Group Information';
53 BEGIN
54 OPEN LEGISLATION_CODE(P_BUSINESS_GROUP_ID);
55 FETCH LEGISLATION_CODE
56 INTO
57 V_LEGISLATION_CODE;
58 CLOSE LEGISLATION_CODE;
59 RETURN V_LEGISLATION_CODE;
60 END CF_LEGISLATION_CODEFORMULA;
61
62 FUNCTION CF_CURRENCY_FORMAT_MASKFORMULA(CF_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
63 V_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
64 V_FORMAT_MASK VARCHAR2(100) := NULL;
65 V_FIELD_LENGTH NUMBER(3) := 14;
66 CURSOR CURRENCY_FORMAT_MASK(C_TERRITORY_CODE IN FND_CURRENCIES.ISSUING_TERRITORY_CODE%TYPE) IS
67 SELECT
68 CURRENCY_CODE
69 FROM
70 FND_CURRENCIES
71 WHERE ISSUING_TERRITORY_CODE = C_TERRITORY_CODE;
72 BEGIN
73 OPEN CURRENCY_FORMAT_MASK(CF_LEGISLATION_CODE);
74 FETCH CURRENCY_FORMAT_MASK
75 INTO
76 V_CURRENCY_CODE;
77 CLOSE CURRENCY_FORMAT_MASK;
78 V_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(V_CURRENCY_CODE
79 ,V_FIELD_LENGTH);
80 RETURN V_FORMAT_MASK;
81 END CF_CURRENCY_FORMAT_MASKFORMULA;
82
83 PROCEDURE SET_CURRENCY_FORMAT_MASK IS
84 BEGIN
85 NULL;
86 END SET_CURRENCY_FORMAT_MASK;
87
88 FUNCTION P_BUSINESS_GROUP_IDVALIDTRIGGE RETURN BOOLEAN IS
89 BEGIN
90 RETURN (TRUE);
91 END P_BUSINESS_GROUP_IDVALIDTRIGGE;
92
93 FUNCTION GET_USER_ENTITY_ID(P_USER_ENTITY_NAME IN FF_USER_ENTITIES.USER_ENTITY_NAME%TYPE) RETURN NUMBER IS
94 L_USER_ENTITY_ID FF_USER_ENTITIES.USER_ENTITY_ID%TYPE;
95 CURSOR GET_USER_ENTITY_ID IS
96 SELECT
97 USER_ENTITY_ID
98 FROM
99 FF_USER_ENTITIES
100 WHERE LEGISLATION_CODE = 'KR'
101 AND USER_ENTITY_NAME = P_USER_ENTITY_NAME;
102 BEGIN
103 OPEN GET_USER_ENTITY_ID;
104 FETCH GET_USER_ENTITY_ID
105 INTO
106 L_USER_ENTITY_ID;
107 CLOSE GET_USER_ENTITY_ID;
108 RETURN (L_USER_ENTITY_ID);
109 EXCEPTION
110 WHEN NO_DATA_FOUND THEN
111 RETURN (0);
112 WHEN OTHERS THEN
113 IF GET_USER_ENTITY_ID%ISOPEN THEN
114 CLOSE GET_USER_ENTITY_ID;
115 END IF;
116 RAISE;
117 END GET_USER_ENTITY_ID;
118
119 FUNCTION CF_SEQUENCE_COUNTERFORMULA(CF_SEQUENCE_COUNTER IN NUMBER) RETURN NUMBER IS
120 BEGIN
121 RETURN (CF_SEQUENCE_COUNTER + 1);
122 END CF_SEQUENCE_COUNTERFORMULA;
123
124 FUNCTION CF_GET_DATAFORMULA(ASSIGNMENT_ACTION_ID IN NUMBER
125 ,ACTUAL_TERMINATION_DATE IN DATE) RETURN NUMBER IS
126 BEGIN
127 CP_HIA_BUSINESS_PLACE_CODE := RPAD(SUBSTR(PAY_KR_REPORT_PKG.GET_ARCHIVE_ITEMS(ASSIGNMENT_ACTION_ID
128 ,X_KR_HIA_BUSINESS_PLACE_CODE)
129 ,1
130 ,8)
131 ,8
132 ,' ');
133 CP_HIA_HI_NUMBER := PAY_KR_REPORT_PKG.GET_ARCHIVE_ITEMS(ASSIGNMENT_ACTION_ID
134 ,X_KR_HIA_HI_NUMBER);
135 CP_HIA_EMPLOYEE_NAME := PAY_KR_REPORT_PKG.GET_ARCHIVE_ITEMS(ASSIGNMENT_ACTION_ID
136 ,X_KR_HIA_EMPLOYEE_NAME);
137 CP_HIA_REGISTRATION_NUMBER := REPLACE(PAY_KR_REPORT_PKG.GET_ARCHIVE_ITEMS(ASSIGNMENT_ACTION_ID
138 ,X_KR_HIA_REGISTRATION_NUMBER)
139 ,'-');
140 CP_HIA_QUALIFIED_DATE := TO_CHAR(FND_DATE.CANONICAL_TO_DATE(PAY_KR_REPORT_PKG.GET_ARCHIVE_ITEMS(ASSIGNMENT_ACTION_ID
141 ,X_KR_HIA_QUALIFIED_DATE))
142 ,'YYYYMMDD');
143 CP_HI_PREM_EE_WO_ADJ_ASG_YTD := TO_NUMBER(PAY_KR_REPORT_PKG.GET_ARCHIVE_ITEMS(ASSIGNMENT_ACTION_ID
144 ,X_HI_PREM_EE_WO_ADJ));
145 CP_EARNINGS_SUBJ_HI_ASG_YTD := TO_NUMBER(PAY_KR_REPORT_PKG.GET_ARCHIVE_ITEMS(ASSIGNMENT_ACTION_ID
146 ,X_EARNINGS_SUBJ_HI));
147 CP_HI_WORKING_MONTHS := TO_NUMBER(PAY_KR_REPORT_PKG.GET_ARCHIVE_ITEMS(ASSIGNMENT_ACTION_ID
148 ,X_HI_WORKING_MONTHS));
149 CP_TERMINATION_DATE := ACTUAL_TERMINATION_DATE;
150 CP_SEQUENCE_NUMBER := CP_SEQUENCE_NUMBER + 1;
151 CP_NUMBER_OF_MONTHS_PAID := TO_NUMBER(PAY_KR_REPORT_PKG.GET_ARCHIVE_ITEMS(ASSIGNMENT_ACTION_ID
152 ,X_KR_HI_NUM_OF_MTHS_PREM_PAID));
153 RETURN (1);
154 END CF_GET_DATAFORMULA;
155
156 FUNCTION CP_CONCAT_BP_NAMESFORMULA RETURN VARCHAR2 IS
157 CURSOR BP_NAMES IS
158 SELECT
159 HOU1.NAME
160 FROM
161 HR_ORGANIZATION_UNITS HOU1,
162 PAY_PAYROLL_ACTIONS PPA,
163 HR_ORGANIZATION_INFORMATION HOI2
164 WHERE PPA.PAYROLL_ACTION_ID = LP_PAYROLL_ACTION_ID
165 AND HOU1.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID
166 AND HOU1.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
167 AND HOI2.ORG_INFORMATION_CONTEXT = 'KR_HI_INFORMATION'
168 AND HOI2.ORG_INFORMATION1 = LP_BP_HI_NUMBER;
169 L_CONCAT VARCHAR2(300);
170 BEGIN
171 L_CONCAT := NULL;
172 FOR rec IN BP_NAMES LOOP
173 L_CONCAT := SUBSTR(L_CONCAT || REC.NAME
174 ,0
175 ,300);
176 L_CONCAT := SUBSTR(L_CONCAT || ', '
177 ,0
178 ,300);
179 END LOOP;
180 L_CONCAT := RTRIM(L_CONCAT
181 ,', ');
182 RETURN L_CONCAT;
183 END CP_CONCAT_BP_NAMESFORMULA;
184
185 FUNCTION CP_HIA_BUSINESS_PLACE_CODE_P RETURN VARCHAR2 IS
186 BEGIN
187 RETURN CP_HIA_BUSINESS_PLACE_CODE;
188 END CP_HIA_BUSINESS_PLACE_CODE_P;
189
190 FUNCTION CP_HIA_HI_NUMBER_P RETURN VARCHAR2 IS
191 BEGIN
192 RETURN CP_HIA_HI_NUMBER;
193 END CP_HIA_HI_NUMBER_P;
194
195 FUNCTION CP_HIA_EMPLOYEE_NAME_P RETURN VARCHAR2 IS
196 BEGIN
197 RETURN CP_HIA_EMPLOYEE_NAME;
198 END CP_HIA_EMPLOYEE_NAME_P;
199
200 FUNCTION CP_HIA_REGISTRATION_NUMBER_P RETURN VARCHAR2 IS
201 BEGIN
202 RETURN CP_HIA_REGISTRATION_NUMBER;
203 END CP_HIA_REGISTRATION_NUMBER_P;
204
205 FUNCTION CP_HIA_QUALIFIED_DATE_P RETURN VARCHAR2 IS
206 BEGIN
207 RETURN CP_HIA_QUALIFIED_DATE;
208 END CP_HIA_QUALIFIED_DATE_P;
209
210 FUNCTION CP_HI_PREM_EE_WO_ADJ_ASG_YTD_P RETURN NUMBER IS
211 BEGIN
212 RETURN CP_HI_PREM_EE_WO_ADJ_ASG_YTD;
213 END CP_HI_PREM_EE_WO_ADJ_ASG_YTD_P;
214
215 FUNCTION CP_EARNINGS_SUBJ_HI_ASG_YTD_P RETURN NUMBER IS
216 BEGIN
217 RETURN CP_EARNINGS_SUBJ_HI_ASG_YTD;
218 END CP_EARNINGS_SUBJ_HI_ASG_YTD_P;
219
220 FUNCTION CP_HI_WORKING_MONTHS_P RETURN NUMBER IS
221 BEGIN
222 RETURN CP_HI_WORKING_MONTHS;
223 END CP_HI_WORKING_MONTHS_P;
224
225 FUNCTION CP_NUMBER_OF_MONTHS_PAID_P RETURN NUMBER IS
226 BEGIN
227 RETURN CP_NUMBER_OF_MONTHS_PAID;
228 END CP_NUMBER_OF_MONTHS_PAID_P;
229
230 FUNCTION CP_TERMINATION_DATE_P RETURN DATE IS
231 BEGIN
232 RETURN CP_TERMINATION_DATE;
233 END CP_TERMINATION_DATE_P;
234
235 FUNCTION CP_SEQUENCE_NUMBER_P RETURN NUMBER IS
236 BEGIN
237 RETURN CP_SEQUENCE_NUMBER;
238 END CP_SEQUENCE_NUMBER_P;
239
240 END PAY_PAYKRHCL_XMLP_PKG;