DBA Data[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;