[Home] [Help]
PACKAGE BODY: APPS.PQH_PQHUSASR_XMLP_PKG
Source
1 PACKAGE BODY PQH_PQHUSASR_XMLP_PKG AS
2 /* $Header: PQHUSASRB.pls 120.1 2007/12/07 06:48:56 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 L_LNAME VARCHAR2(150);
5 L_TITLE VARCHAR2(240);
6 L_REVIEW_DATE VARCHAR2(20);
7 L_EMPID VARCHAR2(30);
8 L_DETERMINE_DATE VARCHAR2(20);
9 L_PROJECTED_TENURE_DATE VARCHAR2(20);
10 L_ADJUSTED_TENURE_DATE VARCHAR2(20);
11 L_TENURE_STATUS VARCHAR2(40);
12 L_BODY_TEXT6 VARCHAR2(2000);
13 L_BODY_TEXT7 VARCHAR2(2000);
14 L_BODY_TEXT8 VARCHAR2(20);
15 L_REMAIN_YEAR VARCHAR2(2);
16 L_REMAIN_MONTH VARCHAR2(2);
17 L_LAST_YEAR BOOLEAN;
18 L_HIREDATE DATE;
19 L_COMPLETED_YEAR NUMBER;
20 L_COMPLETED_MONTH NUMBER;
21 L_MANAGER_ID NUMBER;
22 CURSOR EMP_REVU_CUR IS
23 SELECT
24 TO_CHAR(MIN(PE.DATE_START)
25 ,'DD-MON-YYYY')
26 FROM
27 PER_ALL_ASSIGNMENTS_F PAF,
28 PER_EVENTS PE,
29 HR_LOOKUPS HR,
30 PER_ALL_PEOPLE_F PAP
31 WHERE PE.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
32 AND PAP.PERSON_ID = PAF.PERSON_ID
33 AND PE.TYPE = HR.LOOKUP_CODE
34 AND SYSDATE BETWEEN PAF.EFFECTIVE_START_DATE
35 AND PAF.EFFECTIVE_END_DATE
36 AND SYSDATE BETWEEN PAP.EFFECTIVE_START_DATE
37 AND PAP.EFFECTIVE_END_DATE
38 AND PE.EMP_OR_APL = 'E'
39 AND HR.LOOKUP_TYPE = 'EMP_INTERVIEW_TYPE'
40 AND PE.EVENT_OR_INTERVIEW = 'I'
41 AND PE.DATE_START BETWEEN SYSDATE
42 AND ADD_MONTHS(SYSDATE
43 ,12)
44 AND PAP.PERSON_ID = P_PERSON_ID
45 AND PE.BUSINESS_GROUP_ID + 0 = P_BUSINESS_GROUP_ID;
46 BEGIN
47 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
48 CP_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
49 CP_LOGGED_USER_NAME := FND_PROFILE.VALUE('USERNAME');
50 L_MANAGER_ID := PQH_TENURE_DETAIL.GETPERSONSUPERVISOR(P_PERSON_ID);
51 CP_ACADEMIC_MANAGER_ADDRESS := PQH_TENURE_DETAIL.GETPERSONADDRESS(L_MANAGER_ID
52 ,P_BUSINESS_GROUP_ID);
53 CP_FACULTY_MEMBER_ADDRESS := PQH_TENURE_DETAIL.GETPERSONADDRESS(P_PERSON_ID
54 ,P_BUSINESS_GROUP_ID);
55 PQH_TENURE_DETAIL.GETPERSONINFO(P_PERSON_ID
56 ,L_EMPID
57 ,CP_FACULTY_MEMBER_NAME
58 ,CP_FACULTY_MEMBER_LNAME
59 ,CP_FACULTY_MEMBER_TITLE
60 ,CP_FACULTY_MEMBER_EMAIL
61 ,CP_CONTRACT_START_DATE);
62 PQH_TENURE_DETAIL.GETPERSONINFO(L_MANAGER_ID
63 ,L_EMPID
64 ,CP_ACADEMIC_MANAGER_NAME
65 ,L_LNAME
66 ,L_TITLE
67 ,CP_ACADEMIC_MANAGER_EMAIL
68 ,L_HIREDATE);
69 PQH_TENURE_DETAIL.GETREPORTBODYTEXT('AS'
70 ,CP_REGARDS
71 ,CP_BODY_TEXT1
72 ,CP_BODY_TEXT2
73 ,CP_BODY_TEXT3
74 ,CP_BODY_TEXT4
75 ,CP_BODY_TEXT5
76 ,L_BODY_TEXT6
77 ,L_BODY_TEXT7
78 ,L_BODY_TEXT8);
79 PQH_TENURE_DETAIL.GETPERSONTENURE(P_PERSON_ID
80 ,L_TENURE_STATUS
81 ,L_DETERMINE_DATE
82 ,L_ADJUSTED_TENURE_DATE
83 ,L_REMAIN_YEAR
84 ,L_REMAIN_MONTH
85 ,L_COMPLETED_YEAR
86 ,L_COMPLETED_MONTH);
87 CP_ACADEMIC_RANK := PQH_TENURE_DETAIL.GETPERSONRANK(P_PERSON_ID);
88 OPEN EMP_REVU_CUR;
89 FETCH EMP_REVU_CUR
90 INTO
91 L_REVIEW_DATE;
92 CLOSE EMP_REVU_CUR;
93 CP_BODY_TEXT1 := REPLACE(CP_BODY_TEXT1
94 ,'COLLEGE_UNIVERSITY_NAME'
95 ,CP_BUSINESS_GROUP_NAME);
96 CP_BODY_TEXT1 := REPLACE(CP_BODY_TEXT1
97 ,'YEARS_COMPLETED'
98 ,L_COMPLETED_YEAR);
99 CP_BODY_TEXT1 := REPLACE(CP_BODY_TEXT1
100 ,'MONTHS_COMPLETED'
101 ,L_COMPLETED_MONTH);
102 CP_BODY_TEXT1 := REPLACE(CP_BODY_TEXT1
103 ,'TENURE_DATE'
104 ,L_ADJUSTED_TENURE_DATE);
105 CP_BODY_TEXT1 := REPLACE(CP_BODY_TEXT1
106 ,fnd_global.local_chr(38)
107 ,'');
108 IF L_REVIEW_DATE IS NOT NULL THEN
109 CP_BODY_TEXT2 := REPLACE(CP_BODY_TEXT2
110 ,' REVIEW_DATE'
111 ,L_REVIEW_DATE);
112 ELSE
113 CP_BODY_TEXT2 := L_BODY_TEXT6;
114 END IF;
115 IF L_LAST_YEAR THEN
116 CP_BODY_TEXT4 := CP_BODY_TEXT4;
117 ELSE
118 CP_BODY_TEXT4 := L_BODY_TEXT7;
119 END IF;
120 RETURN (TRUE);
121 END BEFOREREPORT;
122
123 FUNCTION AFTERREPORT RETURN BOOLEAN IS
124 BEGIN
125 -- HR_STANDARD.EVENT('AFTER REPORT');
126 RETURN (TRUE);
127 END AFTERREPORT;
128
129 FUNCTION CP_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
130 BEGIN
131 RETURN CP_BUSINESS_GROUP_NAME;
132 END CP_BUSINESS_GROUP_NAME_P;
133
134 FUNCTION CP_BODY_TEXT1_P RETURN VARCHAR2 IS
135 BEGIN
136 RETURN CP_BODY_TEXT1;
137 END CP_BODY_TEXT1_P;
138
139 FUNCTION CP_FACULTY_MEMBER_NAME_P RETURN VARCHAR2 IS
140 BEGIN
141 RETURN CP_FACULTY_MEMBER_NAME;
142 END CP_FACULTY_MEMBER_NAME_P;
143
144 FUNCTION CP_FACULTY_MEMBER_EMAIL_P RETURN VARCHAR2 IS
145 BEGIN
146 RETURN CP_FACULTY_MEMBER_EMAIL;
147 END CP_FACULTY_MEMBER_EMAIL_P;
148
149 FUNCTION CP_FACULTY_MEMBER_ADDRESS_P RETURN VARCHAR2 IS
150 BEGIN
151 RETURN CP_FACULTY_MEMBER_ADDRESS;
152 END CP_FACULTY_MEMBER_ADDRESS_P;
153
154 FUNCTION CP_FACULTY_MEMBER_TITLE_P RETURN VARCHAR2 IS
155 BEGIN
156 RETURN CP_FACULTY_MEMBER_TITLE;
157 END CP_FACULTY_MEMBER_TITLE_P;
158
159 FUNCTION CP_ACADEMIC_MANAGER_NAME_P RETURN VARCHAR2 IS
160 BEGIN
161 RETURN CP_ACADEMIC_MANAGER_NAME;
162 END CP_ACADEMIC_MANAGER_NAME_P;
163
164 FUNCTION CP_ACADEMIC_MANAGER_EMAIL_P RETURN VARCHAR2 IS
165 BEGIN
166 RETURN CP_ACADEMIC_MANAGER_EMAIL;
167 END CP_ACADEMIC_MANAGER_EMAIL_P;
168
169 FUNCTION CP_ACADEMIC_MANAGER_ADDRESS_P RETURN VARCHAR2 IS
170 BEGIN
171 RETURN CP_ACADEMIC_MANAGER_ADDRESS;
172 END CP_ACADEMIC_MANAGER_ADDRESS_P;
173
174 FUNCTION CP_LOGGED_USER_NAME_P RETURN VARCHAR2 IS
175 BEGIN
176 RETURN CP_LOGGED_USER_NAME;
177 END CP_LOGGED_USER_NAME_P;
178
179 FUNCTION CP_CONTRACT_START_DATE_P RETURN VARCHAR2 IS
180 BEGIN
181 RETURN CP_CONTRACT_START_DATE;
182 END CP_CONTRACT_START_DATE_P;
183
184 FUNCTION CP_FACULTY_MEMBER_LNAME_P RETURN VARCHAR2 IS
185 BEGIN
186 RETURN CP_FACULTY_MEMBER_LNAME;
187 END CP_FACULTY_MEMBER_LNAME_P;
188
189 FUNCTION CP_REGARDS_P RETURN VARCHAR2 IS
190 BEGIN
191 RETURN CP_REGARDS;
192 END CP_REGARDS_P;
193
194 FUNCTION CP_BODY_TEXT2_P RETURN VARCHAR2 IS
195 BEGIN
196 RETURN CP_BODY_TEXT2;
197 END CP_BODY_TEXT2_P;
198
199 FUNCTION CP_BODY_TEXT3_P RETURN VARCHAR2 IS
200 BEGIN
201 RETURN CP_BODY_TEXT3;
202 END CP_BODY_TEXT3_P;
203
204 FUNCTION CP_BODY_TEXT4_P RETURN VARCHAR2 IS
205 BEGIN
206 RETURN CP_BODY_TEXT4;
207 END CP_BODY_TEXT4_P;
208
209 FUNCTION CP_BODY_TEXT5_P RETURN VARCHAR2 IS
210 BEGIN
211 RETURN CP_BODY_TEXT5;
212 END CP_BODY_TEXT5_P;
213
214 FUNCTION CP_ACADEMIC_RANK_P RETURN VARCHAR2 IS
215 BEGIN
216 RETURN CP_ACADEMIC_RANK;
217 END CP_ACADEMIC_RANK_P;
218
219 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
220 X0 VARCHAR2(2000);
221 BEGIN
222 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
223 RETURN X0;
224 END GET_BUSINESS_GROUP;
225
226 END PQH_PQHUSASR_XMLP_PKG;