1 PACKAGE BODY PAY_PYAUAPL_XMLP_PKG AS
2 /* $Header: PYAUAPLB.pls 120.1 2008/03/25 06:23:59 amakrish noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 END AFTERREPORT;
7
8 FUNCTION CF_BUSINESS_GROUPFORMULA RETURN VARCHAR2 IS
9 V_BUSINESS_GROUP HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
10 BEGIN
11 V_BUSINESS_GROUP := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
12 RETURN V_BUSINESS_GROUP;
13 END CF_BUSINESS_GROUPFORMULA;
14
15 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
16 CODE VARCHAR2(1);
17 FLAG HR_LOOKUPS.MEANING%TYPE;
18 CURSOR C_CUR_FUT IS
19 SELECT
20 LOOKUP_CODE,
21 MEANING
22 FROM
23 HR_LOOKUPS
24 WHERE LOOKUP_CODE in ( 'C' , 'F' )
25 AND LOOKUP_TYPE = 'AU_ADV_FLAG';
26 CURSOR C_PROCESS IS
27 SELECT
28 LOOKUP_CODE,
29 MEANING
30 FROM
31 HR_LOOKUPS
32 WHERE LOOKUP_CODE in ( 'Y' , 'N' )
33 AND LOOKUP_TYPE = 'YES_NO';
34 CURSOR C_GET_EMP_NO IS
35 SELECT
36 EMPLOYEE_NUMBER
37 FROM
38 PER_PEOPLE_F
39 WHERE PERSON_ID = P_PERSON_ID;
40 CURSOR C_GET_PAY_NAME IS
41 SELECT
42 PAYROLL_NAME
43 FROM
44 PAY_PAYROLLS_F
45 WHERE PAYROLL_ID = P_PAYROLL_ID;
46 CP_DATE_FORMAT varchar2(20);
47 BEGIN
48 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
49 IF (P_PERSON_ID IS NULL) THEN
50 P_EMPLOYEE_NUMBER := NULL;
51 ELSE
52 OPEN C_GET_EMP_NO;
53 FETCH C_GET_EMP_NO
54 INTO
55 P_EMPLOYEE_NUMBER;
56 CLOSE C_GET_EMP_NO;
57 END IF;
58 OPEN C_GET_PAY_NAME;
59 FETCH C_GET_PAY_NAME
60 INTO
61 P_PAYROLL_NAME;
62 CLOSE C_GET_PAY_NAME;
63 OPEN C_CUR_FUT;
64 FOR i IN 1 .. 2 LOOP
65 FETCH C_CUR_FUT
66 INTO
67 CODE
68 ,FLAG;
69 IF CODE = 'C' THEN
70 CP_CURRENT := FLAG;
71 ELSIF CODE = 'F' THEN
72 CP_FUTURE := FLAG;
73 END IF;
74 END LOOP;
75 CLOSE C_CUR_FUT;
76 OPEN C_PROCESS;
77 FOR i IN 1 .. 2 LOOP
78 FETCH C_PROCESS
79 INTO
80 CODE
81 ,FLAG;
82 IF CODE = 'Y' THEN
83 CP_YES := FLAG;
84 ELSIF CODE = 'N' THEN
85 CP_NO := FLAG;
86 END IF;
87 END LOOP;
88 CLOSE C_PROCESS;
89 CP_LV_ADV_m := CF_END_DATEFORMULA;
90 CP_DATE_FORMAT := 'DD-MON-YYYY';
91 P_START_DATE1 := TO_DATE(P_START_DATE,CP_DATE_FORMAT);
92 RETURN (TRUE);
93 END BEFOREREPORT;
94
95 FUNCTION CF_CURRENT_FUTURE_FLAGFORMULA RETURN CHAR IS
96 FLAG VARCHAR2(10);
97 BEGIN
98 IF (P_END_DATE IS NULL) THEN
99 FLAG := CP_CURRENT;
100 ELSIF (CP_LEAVE_START_DATE > P_END_DATE) THEN
101 FLAG := CP_FUTURE;
102 ELSE
103 FLAG := CP_CURRENT;
104 END IF;
105 RETURN (FLAG);
106 END CF_CURRENT_FUTURE_FLAGFORMULA;
107
108 FUNCTION CF_PROCESS_FLAGFORMULA(P_CP_LEAVE_START_DATE in DATE,P_CP_LEAVE_END_DATE in DATE) RETURN CHAR IS
109 PROCESS_STATUS VARCHAR2(30);
110 CURSOR C_STATUS IS
111 SELECT
112 PPA7.ACTION_STATUS
113 FROM
114 PAY_PAYROLLS_F PPF7,
115 PER_ASSIGNMENTS_F PAA7,
116 PAY_PAYROLL_ACTIONS PPA7,
117 PAY_ASSIGNMENT_ACTIONS PAC7
118 WHERE PPF7.PAYROLL_ID = PAA7.PAYROLL_ID
119 AND PPA7.ACTION_TYPE = 'W'
120 AND PPA7.PAYROLL_ACTION_ID = PAC7.PAYROLL_ACTION_ID
121 AND PAA7.ASSIGNMENT_ID = PAC7.ASSIGNMENT_ID
122 AND P_CP_LEAVE_START_DATE between PPA7.START_DATE
123 AND PPA7.END_DATE
124 AND P_CP_LEAVE_END_DATE between PPA7.START_DATE
125 AND PPA7.END_DATE;
126 BEGIN
127 OPEN C_STATUS;
128 FETCH C_STATUS
129 INTO
130 PROCESS_STATUS;
131 CLOSE C_STATUS;
132 IF (PROCESS_STATUS = 'C') THEN
133 RETURN (CP_YES);
134 ELSE
135 RETURN (CP_NO);
136 END IF;
137 END CF_PROCESS_FLAGFORMULA;
138
139 FUNCTION CF_END_DATEFORMULA RETURN varchar2 IS
140 TEMP1 VARCHAR2(40);
141 BEGIN
142 CP_LV_ADV := P_START_DATE + P_ADVANCE_PERIOD;
143 TEMP1 := TO_CHAR(CP_LV_ADV,'DD-MON-YYYY');
144
145 RETURN (TEMP1);
146 END CF_END_DATEFORMULA;
147
148 FUNCTION CF_DATE_CONVERSIONFORMULA(LEAVE_START_DATE IN VARCHAR2
149 ,LEAVE_END_DATE IN VARCHAR2
150 ,PAY_DATE IN VARCHAR2) RETURN NUMBER IS
151 pragma autonomous_transaction;
152 BEGIN
153 CP_LEAVE_START_DATE := TO_DATE(LEAVE_START_DATE
154 ,'YYYY/MM/DD HH24:MI:SS');
155 CP_LEAVE_END_DATE := TO_DATE(LEAVE_END_DATE
156 ,'YYYY/MM/DD HH24:MI:SS');
157 CP_PAY_DATE := TO_DATE(PAY_DATE
158 ,'YYYY/MM/DD HH24:MI:SS');
159
160 CP_PAY_DATE1 := CP_PAY_DATE;
161
162 RETURN (1);
163 END CF_DATE_CONVERSIONFORMULA;
164
165 FUNCTION CP_LEAVE_START_DATE_P RETURN DATE IS
166 BEGIN
167 RETURN CP_LEAVE_START_DATE;
168 END CP_LEAVE_START_DATE_P;
169
170 FUNCTION CP_PAY_DATE_P RETURN DATE IS
171 BEGIN
172 RETURN CP_PAY_DATE;
173 END CP_PAY_DATE_P;
174
175 FUNCTION CP_LEAVE_END_DATE_P RETURN DATE IS
176 BEGIN
177 RETURN CP_LEAVE_END_DATE;
178 END CP_LEAVE_END_DATE_P;
179
180 FUNCTION CP_LV_ADV_P RETURN DATE IS
181 BEGIN
182 RETURN CP_LV_ADV;
183 END CP_LV_ADV_P;
184
185 FUNCTION CP_CURRENT_P RETURN VARCHAR2 IS
186 BEGIN
187 RETURN CP_CURRENT;
188 END CP_CURRENT_P;
189
190 FUNCTION CP_FUTURE_P RETURN VARCHAR2 IS
191 BEGIN
192 RETURN CP_FUTURE;
193 END CP_FUTURE_P;
194
195 FUNCTION CP_YES_P RETURN VARCHAR2 IS
196 BEGIN
197 RETURN CP_YES;
198 END CP_YES_P;
199
200 FUNCTION CP_NO_P RETURN VARCHAR2 IS
201 BEGIN
202 RETURN CP_NO;
203 END CP_NO_P;
204
205 END PAY_PYAUAPL_XMLP_PKG;