1 PACKAGE BODY PAY_PYAUSGC_XMLP_PKG AS
2 /* $Header: PYAUSGCB.pls 120.0 2007/12/13 12:16:36 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 BEGIN
17 declare
18 l_date_format varchar2(30) := 'DD-MON-YYYY';
19 begin
20 P_START_DATE := TO_DATE('01-' || TO_CHAR(P_DATE
21 ,'MON-YYYY')
22 ,'DD-MON-YYYY');
23 SELECT
24 LAST_DAY(P_DATE)
25 INTO
26 P_END_DATE
27 FROM
28 DUAL;
29 SELECT
30 'For' || ' ' || TO_CHAR(P_DATE
31 ,'MON-YYYY')
32 INTO
33 P_MON_YYYY
34 FROM
35 DUAL;
36 CP_SGC_RATE := P_SGC_RATE || ' ' || '%';
37 PAY_AU_SGC_PKG.GLOBAL_SUPER_VALUES(P_START_DATE
38 ,'AU');
39 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
40
41 P_DATE1 := TO_DATE(P_DATE,l_date_format);
42 RETURN (TRUE);
43 end;
44 END BEFOREREPORT;
45
46 FUNCTION CF_LEGISLATION_CODEFORMULA RETURN VARCHAR2 IS
47 V_LEGISLATION_CODE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION9%TYPE := NULL;
48 CURSOR LEGISLATION_CODE(C_BUSINESS_GROUP_ID IN HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE) IS
49 SELECT
50 ORG_INFORMATION9
51 FROM
52 HR_ORGANIZATION_INFORMATION
53 WHERE ORGANIZATION_ID = C_BUSINESS_GROUP_ID
54 AND ORG_INFORMATION9 is not null;
55 BEGIN
56 OPEN LEGISLATION_CODE(P_BUSINESS_GROUP_ID);
57 FETCH LEGISLATION_CODE
58 INTO
59 V_LEGISLATION_CODE;
60 CLOSE LEGISLATION_CODE;
61 RETURN V_LEGISLATION_CODE;
62 END CF_LEGISLATION_CODEFORMULA;
63
64 FUNCTION CF_CURRENCY_FORMAT_MASKFORMULA(CF_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
65 V_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
66 V_FORMAT_MASK VARCHAR2(100) := NULL;
67 V_FIELD_LENGTH NUMBER(3) := 15;
68 CURSOR CURRENCY_FORMAT_MASK(C_TERRITORY_CODE IN FND_CURRENCIES.ISSUING_TERRITORY_CODE%TYPE) IS
69 SELECT
70 CURRENCY_CODE
71 FROM
72 FND_CURRENCIES
73 WHERE ISSUING_TERRITORY_CODE = C_TERRITORY_CODE;
74 BEGIN
75 OPEN CURRENCY_FORMAT_MASK(CF_LEGISLATION_CODE);
76 FETCH CURRENCY_FORMAT_MASK
77 INTO
78 V_CURRENCY_CODE;
79 CLOSE CURRENCY_FORMAT_MASK;
80 CP_CURRENCY_CODE := '(' || V_CURRENCY_CODE || ')';
81 V_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(V_CURRENCY_CODE
82 ,V_FIELD_LENGTH);
83 RETURN V_FORMAT_MASK;
84 END CF_CURRENCY_FORMAT_MASKFORMULA;
85
86 FUNCTION CF_PAYAUSGC_PKBFORMULA(ASSIGNMENT_ID IN NUMBER
87 ,AGE IN NUMBER) RETURN NUMBER IS
88 BEGIN
89 PAY_AU_SGC_PKG.EMPLOYEE_SUPER_DETAILS(ASSIGNMENT_ID
90 ,P_REGISTERED_EMPLOYER
91 ,AGE
92 ,P_END_DATE
93 ,P_SGC_RATE
94 ,P_BUSINESS_GROUP_ID
95 ,'AU'
96 ,CP_SUPER_SAL
97 ,CP_SGC_CONTR
98 ,CP_COMPLIANCE_MESG
99 ,CP_WARNING_MESG);
100 RETURN NULL;
101 END CF_PAYAUSGC_PKBFORMULA;
102
103 FUNCTION CF_END_OF_REPORTFORMULA(CS_CNT_ASG_ID IN NUMBER) RETURN CHAR IS
104 END_OF_REPORT VARCHAR2(40) := '***** End Of Report *****';
105 NO_DATA_FOUND VARCHAR2(40) := '***** No Data Found *****';
106 BEGIN
107 IF CS_CNT_ASG_ID > 0 THEN
108 RETURN (END_OF_REPORT);
109 ELSIF CS_CNT_ASG_ID = 0 THEN
110 RETURN (NO_DATA_FOUND);
111 END IF;
112 END CF_END_OF_REPORTFORMULA;
113
114 FUNCTION CF_SGC_RATEFORMULA RETURN CHAR IS
115 BEGIN
116 CP_SGC_RATE_1 := P_SGC_RATE || ' ' || '%';
117 RETURN (CP_SGC_RATE_1);
118 END CF_SGC_RATEFORMULA;
119
120 FUNCTION CF_COST_CENTREFORMULA RETURN VARCHAR2 IS
121 CURSOR C_COST_CENTRE IS
122 SELECT
123 CONCATENATED_SEGMENTS
124 FROM
125 PAY_COST_ALLOCATION_KEYFLEX
126 WHERE COST_ALLOCATION_KEYFLEX_ID = P_COST_CENTRE;
127 L_CONCATENATED_SEGMENTS PAY_COST_ALLOCATION_KEYFLEX.CONCATENATED_SEGMENTS%TYPE := NULL;
128 BEGIN
129 IF P_COST_CENTRE IS NOT NULL THEN
130 OPEN C_COST_CENTRE;
131 FETCH C_COST_CENTRE
132 INTO
133 L_CONCATENATED_SEGMENTS;
134 IF SQL%NOTFOUND THEN
135 RAISE NO_DATA_FOUND;
136 END IF;
137 CLOSE C_COST_CENTRE;
138 END IF;
139 RETURN (L_CONCATENATED_SEGMENTS);
140 EXCEPTION
141 WHEN OTHERS THEN
142 NULL;
143 END CF_COST_CENTREFORMULA;
144
145 FUNCTION CF_ALL_EXCEPFORMULA RETURN CHAR IS
146 L_ALL_EXCEP HR_LOOKUPS.MEANING%TYPE;
147 CURSOR ALL_EXCEP IS
148 SELECT
149 MEANING
150 FROM
151 HR_LOOKUPS
152 WHERE LOOKUP_CODE = P_ALL_EXCEPTION
153 AND LOOKUP_TYPE = 'AU_SGC_COMPLIANCE'
154 AND ENABLED_FLAG = 'Y';
155 BEGIN
156 OPEN ALL_EXCEP;
157 FETCH ALL_EXCEP
158 INTO
159 L_ALL_EXCEP;
160 IF ALL_EXCEP%NOTFOUND THEN
161 RAISE NO_DATA_FOUND;
162 END IF;
163 CLOSE ALL_EXCEP;
164 RETURN (L_ALL_EXCEP);
165 EXCEPTION
166 WHEN OTHERS THEN
167 NULL;
168 END CF_ALL_EXCEPFORMULA;
169
170 FUNCTION CP_SGC_RATE_1_P RETURN VARCHAR2 IS
171 BEGIN
172 RETURN CP_SGC_RATE_1;
173 END CP_SGC_RATE_1_P;
174
175 FUNCTION CP_START_DATE_P RETURN DATE IS
176 BEGIN
177 RETURN CP_START_DATE;
178 END CP_START_DATE_P;
179
180 FUNCTION CP_END_DATE_P RETURN DATE IS
181 BEGIN
182 RETURN CP_END_DATE;
183 END CP_END_DATE_P;
184
185 FUNCTION CP_SUPER_SAL_P RETURN NUMBER IS
186 BEGIN
187 RETURN CP_SUPER_SAL;
188 END CP_SUPER_SAL_P;
189
190 FUNCTION CP_COMPLIANCE_MESG_P RETURN VARCHAR2 IS
191 BEGIN
192 RETURN CP_COMPLIANCE_MESG;
193 END CP_COMPLIANCE_MESG_P;
194
195 FUNCTION CP_WARNING_MESG_P RETURN VARCHAR2 IS
196 BEGIN
197 RETURN CP_WARNING_MESG;
198 END CP_WARNING_MESG_P;
199
200 FUNCTION CP_SGC_CONTR_P RETURN NUMBER IS
201 BEGIN
202 RETURN CP_SGC_CONTR;
203 END CP_SGC_CONTR_P;
204
205 FUNCTION CP_CURRENCY_CODE_P RETURN VARCHAR2 IS
206 BEGIN
207 RETURN CP_CURRENCY_CODE;
208 END CP_CURRENCY_CODE_P;
209
210 FUNCTION CP_SGC_RATE_P RETURN VARCHAR2 IS
211 BEGIN
212 RETURN CP_SGC_RATE;
213 END CP_SGC_RATE_P;
214
215 END PAY_PYAUSGC_XMLP_PKG;