[Home] [Help]
PACKAGE BODY: APPS.PQH_PQHUSNRW_XMLP_PKG
Source
1 PACKAGE BODY PQH_PQHUSNRW_XMLP_PKG AS
2 /* $Header: PQHUSNRWB.pls 120.1 2007/12/07 06:57:09 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_BODY_TEXT3 VARCHAR2(20);
9 L_BODY_TEXT4 VARCHAR2(20);
10 L_BODY_TEXT5 VARCHAR2(20);
11 L_BODY_TEXT6 VARCHAR2(20);
12 L_BODY_TEXT7 VARCHAR2(20);
13 L_BODY_TEXT8 VARCHAR2(20);
14 L_HIREDATE DATE;
15 L_MANAGER_ID NUMBER;
16 CURSOR EMP_REVU_CUR IS
17 SELECT
18 TO_CHAR(MIN(PE.DATE_START)
19 ,'DD-MON-YYYY')
20 FROM
21 PER_ALL_ASSIGNMENTS_F PAF,
22 PER_EVENTS PE,
23 HR_LOOKUPS HR,
24 PER_ALL_PEOPLE_F PAP
25 WHERE PE.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
26 AND PAP.PERSON_ID = PAF.PERSON_ID
27 AND PE.TYPE = HR.LOOKUP_CODE
28 AND SYSDATE BETWEEN PAF.EFFECTIVE_START_DATE
29 AND PAF.EFFECTIVE_END_DATE
30 AND SYSDATE BETWEEN PAP.EFFECTIVE_START_DATE
31 AND PAP.EFFECTIVE_END_DATE
32 AND PE.EMP_OR_APL = 'E'
33 AND HR.LOOKUP_TYPE = 'EMP_INTERVIEW_TYPE'
34 AND PE.EVENT_OR_INTERVIEW = 'I'
35 AND PE.DATE_START BETWEEN P_START_DATE
36 AND P_END_DATE
37 AND PAP.PERSON_ID = P_PERSON_ID
38 AND PE.BUSINESS_GROUP_ID + 0 = P_BUSINESS_GROUP_ID;
39 BEGIN
40 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
41 CP_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
42 CP_LOGGED_USER_NAME := FND_PROFILE.VALUE('USERNAME');
43 L_MANAGER_ID := PQH_TENURE_DETAIL.GETPERSONSUPERVISOR(P_PERSON_ID);
44 CP_ACADEMIC_MANAGER_ADDRESS := PQH_TENURE_DETAIL.GETPERSONADDRESS(L_MANAGER_ID
45 ,P_BUSINESS_GROUP_ID);
46 CP_FACULTY_MEMBER_ADDRESS := PQH_TENURE_DETAIL.GETPERSONADDRESS(P_PERSON_ID
47 ,P_BUSINESS_GROUP_ID);
48 PQH_TENURE_DETAIL.GETPERSONINFO(P_PERSON_ID
49 ,L_EMPID
50 ,CP_FACULTY_MEMBER_NAME
51 ,CP_FACULTY_MEMBER_LNAME
52 ,CP_FACULTY_MEMBER_TITLE
53 ,CP_FACULTY_MEMBER_EMAIL
54 ,CP_CONTRACT_START_DATE);
55 PQH_TENURE_DETAIL.GETPERSONINFO(L_MANAGER_ID
56 ,L_EMPID
57 ,CP_ACADEMIC_MANAGER_NAME
58 ,L_LNAME
59 ,L_TITLE
60 ,CP_ACADEMIC_MANAGER_EMAIL
61 ,L_HIREDATE);
62 PQH_TENURE_DETAIL.GETREPORTBODYTEXT('RW'
63 ,CP_REGARDS
64 ,CP_BODY_TEXT1
65 ,CP_BODY_TEXT2
66 ,L_BODY_TEXT3
67 ,L_BODY_TEXT4
68 ,L_BODY_TEXT5
69 ,L_BODY_TEXT6
70 ,L_BODY_TEXT7
71 ,L_BODY_TEXT8);
72 OPEN EMP_REVU_CUR;
73 FETCH EMP_REVU_CUR
74 INTO
75 L_REVIEW_DATE;
76 CLOSE EMP_REVU_CUR;
77 CP_BODY_TEXT1 := REPLACE(CP_BODY_TEXT1
78 ,fnd_global.local_chr(10)
79 ,'');
80 CP_BODY_TEXT2 := REPLACE(CP_BODY_TEXT2
81 ,fnd_global.local_chr(10)
82 ,'');
83 CP_BODY_TEXT1 := REPLACE(CP_BODY_TEXT1
84 ,'REVIEW_DATE'
85 ,L_REVIEW_DATE);
86 CP_BODY_TEXT1 := REPLACE(CP_BODY_TEXT1
87 ,fnd_global.local_chr(38)
88 ,'');
89 P_START_DATE_T := to_char(P_START_DATE,'DD-MON-YYYY');
90 P_END_DATE_T := to_char(P_END_DATE,'DD-MON-YYYY');
91
92 RETURN (TRUE);
93 END BEFOREREPORT;
94
95 FUNCTION AFTERREPORT RETURN BOOLEAN IS
96 BEGIN
97 -- HR_STANDARD.EVENT('AFTER REPORT');
98 RETURN (TRUE);
99 END AFTERREPORT;
100
101 FUNCTION CP_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
102 BEGIN
103 RETURN CP_BUSINESS_GROUP_NAME;
104 END CP_BUSINESS_GROUP_NAME_P;
105
106 FUNCTION CP_BODY_TEXT1_P RETURN VARCHAR2 IS
107 BEGIN
108 RETURN CP_BODY_TEXT1;
109 END CP_BODY_TEXT1_P;
110
111 FUNCTION CP_FACULTY_MEMBER_NAME_P RETURN VARCHAR2 IS
112 BEGIN
113 RETURN CP_FACULTY_MEMBER_NAME;
114 END CP_FACULTY_MEMBER_NAME_P;
115
116 FUNCTION CP_FACULTY_MEMBER_EMAIL_P RETURN VARCHAR2 IS
117 BEGIN
118 RETURN CP_FACULTY_MEMBER_EMAIL;
119 END CP_FACULTY_MEMBER_EMAIL_P;
120
121 FUNCTION CP_FACULTY_MEMBER_ADDRESS_P RETURN VARCHAR2 IS
122 BEGIN
123 RETURN CP_FACULTY_MEMBER_ADDRESS;
124 END CP_FACULTY_MEMBER_ADDRESS_P;
125
126 FUNCTION CP_FACULTY_MEMBER_TITLE_P RETURN VARCHAR2 IS
127 BEGIN
128 RETURN CP_FACULTY_MEMBER_TITLE;
129 END CP_FACULTY_MEMBER_TITLE_P;
130
131 FUNCTION CP_ACADEMIC_MANAGER_NAME_P RETURN VARCHAR2 IS
132 BEGIN
133 RETURN CP_ACADEMIC_MANAGER_NAME;
134 END CP_ACADEMIC_MANAGER_NAME_P;
135
136 FUNCTION CP_ACADEMIC_MANAGER_EMAIL_P RETURN VARCHAR2 IS
137 BEGIN
138 RETURN CP_ACADEMIC_MANAGER_EMAIL;
139 END CP_ACADEMIC_MANAGER_EMAIL_P;
140
141 FUNCTION CP_ACADEMIC_MANAGER_ADDRESS_P RETURN VARCHAR2 IS
142 BEGIN
143 RETURN CP_ACADEMIC_MANAGER_ADDRESS;
144 END CP_ACADEMIC_MANAGER_ADDRESS_P;
145
146 FUNCTION CP_LOGGED_USER_NAME_P RETURN VARCHAR2 IS
147 BEGIN
148 RETURN CP_LOGGED_USER_NAME;
149 END CP_LOGGED_USER_NAME_P;
150
151 FUNCTION CP_CONTRACT_START_DATE_P RETURN VARCHAR2 IS
152 BEGIN
153 RETURN CP_CONTRACT_START_DATE;
154 END CP_CONTRACT_START_DATE_P;
155
156 FUNCTION CP_FACULTY_MEMBER_LNAME_P RETURN VARCHAR2 IS
157 BEGIN
158 RETURN CP_FACULTY_MEMBER_LNAME;
159 END CP_FACULTY_MEMBER_LNAME_P;
160
161 FUNCTION CP_REGARDS_P RETURN VARCHAR2 IS
162 BEGIN
163 RETURN CP_REGARDS;
164 END CP_REGARDS_P;
165
166 FUNCTION CP_BODY_TEXT2_P RETURN VARCHAR2 IS
167 BEGIN
168 RETURN CP_BODY_TEXT2;
169 END CP_BODY_TEXT2_P;
170
171
172 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
173 X0 VARCHAR2(2000);
174 BEGIN
175 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
176 RETURN X0;
177 END GET_BUSINESS_GROUP;
178
179 END PQH_PQHUSNRW_XMLP_PKG;