1 PACKAGE BODY PA_PAXBUBSS_XMLP_PKG AS
2 /* $Header: PAXBUBSSB.pls 120.0 2008/01/02 11:21:53 krreddy noship $ */
3 FUNCTION GET_COVER_PAGE_VALUES RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 EXCEPTION
7 WHEN OTHERS THEN
8 RETURN (FALSE);
9 END GET_COVER_PAGE_VALUES;
10 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
11 BEGIN
12 DECLARE
13 INIT_FAILURE EXCEPTION;
14 P_ORG HR_ORGANIZATION_UNITS.NAME%TYPE;
15 P_MGR VARCHAR2(30);
16 P_NUMBER VARCHAR2(30);
17 P_NAME VARCHAR2(30);
18 TSK_NUM VARCHAR2(30);
19 TSK_NAME VARCHAR2(30);
20 EXPLODE VARCHAR2(30);
21 P_COST_BGT_CODE VARCHAR2(30);
22 P_COST_BGT_TYPE VARCHAR2(30);
23 P_REV_BGT_CODE VARCHAR2(30);
24 P_REV_BGT_TYPE VARCHAR2(30);
25 BEGIN
26 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
27 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
28 /*SRW.USER_EXIT('FND GETPROFILE
29 NAME="PA_RULE_BASED_OPTIMIZER"
30 FIELD=":p_rule_optimizer"
31 PRINT_ERROR="N"')*/NULL;
32 P_DEBUG_MODE := FND_PROFILE.VALUE('PA_DEBUG_MODE');
33 IF ORG IS NOT NULL THEN
34 SELECT
35 SUBSTR(NAME
36 ,1
37 ,60)
38 INTO P_ORG
39 FROM
40 HR_ORGANIZATION_UNITS
41 WHERE ORG = ORGANIZATION_ID;
42 END IF;
43 C_ORG := P_ORG;
44 IF MGR IS NOT NULL THEN
45 SELECT
46 SUBSTR(FULL_NAME
47 ,1
48 ,30)
49 INTO P_MGR
50 FROM
51 PER_PEOPLE_F
52 WHERE MGR = PERSON_ID
53 AND sysdate between EFFECTIVE_START_DATE
54 AND NVL(EFFECTIVE_END_DATE
55 ,SYSDATE + 1)
56 AND ( CURRENT_NPW_FLAG = 'Y'
57 OR CURRENT_EMPLOYEE_FLAG = 'Y' )
58 AND DECODE(CURRENT_NPW_FLAG
59 ,'Y'
60 ,NPW_NUMBER
61 ,EMPLOYEE_NUMBER) IS NOT NULL;
62 END IF;
63 C_MGR := P_MGR;
64 IF PROJ IS NOT NULL THEN
65 SELECT
66 SEGMENT1,
67 NAME
68 INTO P_NUMBER,P_NAME
69 FROM
70 PA_PROJECTS
71 WHERE PROJ = PROJECT_ID;
72 END IF;
73 C_PROJ_NUM := P_NUMBER;
74 C_PROJ_NAME := P_NAME;
75 IF TOPTASK IS NOT NULL THEN
76 SELECT
77 TASK_NUMBER,
78 TASK_NAME
79 INTO TSK_NUM,TSK_NAME
80 FROM
81 PA_TASKS
82 WHERE TOPTASK = TASK_ID;
83 END IF;
84 C_TASK_NUM := TSK_NUM;
85 C_TASK_NAME := TSK_NAME;
86 IF EXPLODE_SUB_TASKS IS NOT NULL THEN
87 SELECT
88 MEANING
89 INTO EXPLODE
90 FROM
91 FND_LOOKUPS
92 WHERE LOOKUP_TYPE = 'YES_NO'
93 AND LOOKUP_CODE = EXPLODE_SUB_TASKS;
94 END IF;
95 C_EXPLODE := EXPLODE;
96 IF (COST_BGT_CODE IS NULL) THEN
97 SELECT
98 BUDGET_TYPE_CODE,
99 BUDGET_TYPE
100 INTO P_COST_BGT_CODE,P_COST_BGT_TYPE
101 FROM
102 PA_BUDGET_TYPES
103 WHERE BUDGET_AMOUNT_CODE = 'C'
104 AND PREDEFINED_FLAG = 'Y'
105 AND BUDGET_TYPE_CODE = 'AC';
106 C_COST_BGT_CODE := P_COST_BGT_CODE;
107 C_COST_BGT_NAME := P_COST_BGT_TYPE;
108 ELSE
109 SELECT
110 BUDGET_TYPE
111 INTO P_COST_BGT_TYPE
112 FROM
113 PA_BUDGET_TYPES
114 WHERE BUDGET_TYPE_CODE = COST_BGT_CODE;
115 C_COST_BGT_CODE := COST_BGT_CODE;
116 C_COST_BGT_NAME := P_COST_BGT_TYPE;
117 END IF;
118 IF (REV_BGT_CODE IS NULL) THEN
119 SELECT
120 BUDGET_TYPE_CODE,
121 BUDGET_TYPE
122 INTO P_REV_BGT_CODE,P_REV_BGT_TYPE
123 FROM
124 PA_BUDGET_TYPES
125 WHERE BUDGET_AMOUNT_CODE = 'R'
126 AND PREDEFINED_FLAG = 'Y'
127 AND BUDGET_TYPE_CODE = 'AR';
128 C_REV_BGT_CODE := P_REV_BGT_CODE;
129 C_REV_BGT_NAME := P_REV_BGT_TYPE;
130 ELSE
131 SELECT
132 BUDGET_TYPE
133 INTO P_REV_BGT_TYPE
134 FROM
135 PA_BUDGET_TYPES
136 WHERE BUDGET_TYPE_CODE = REV_BGT_CODE;
137 C_REV_BGT_CODE := REV_BGT_CODE;
138 C_REV_BGT_NAME := P_REV_BGT_TYPE;
139 END IF;
140 IF (GET_COMPANY_NAME <> TRUE) THEN
141 RAISE INIT_FAILURE;
142 END IF;
143 IF (NO_DATA_FOUND_FUNC <> TRUE) THEN
144 RAISE INIT_FAILURE;
145 END IF;
146 END;
147 RETURN (TRUE);
148 END BEFOREREPORT;
149 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
150 L_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
151 BEGIN
152 SELECT
153 GL.NAME
154 INTO L_NAME
155 FROM
156 GL_SETS_OF_BOOKS GL,
157 PA_IMPLEMENTATIONS PI
158 WHERE GL.SET_OF_BOOKS_ID = PI.SET_OF_BOOKS_ID;
159 C_COMPANY_NAME_HEADER := L_NAME;
160 RETURN (TRUE);
161 EXCEPTION
162 WHEN OTHERS THEN
163 RETURN (FALSE);
164 END GET_COMPANY_NAME;
165 FUNCTION NO_DATA_FOUND_FUNC RETURN BOOLEAN IS
166 MESSAGE_NAME VARCHAR2(80);
167 BEGIN
168 SELECT
169 MEANING
170 INTO MESSAGE_NAME
171 FROM
172 PA_LOOKUPS
173 WHERE LOOKUP_TYPE = 'MESSAGE'
174 AND LOOKUP_CODE = 'NO_DATA_FOUND';
175 C_NO_DATA_FOUND := MESSAGE_NAME;
176 RETURN (TRUE);
177 EXCEPTION
178 WHEN OTHERS THEN
179 RETURN (FALSE);
180 END NO_DATA_FOUND_FUNC;
181 FUNCTION GET_AR(PROJECT_ID_1 IN NUMBER) RETURN NUMBER IS
182 ACCREC NUMBER(15,2);
183 BEGIN
184 SELECT
185 SUM(AR.ACCTD_AMOUNT_DUE_REMAINING)
186 INTO ACCREC
187 FROM
188 AR_PAYMENT_SCHEDULES AR,
189 RA_CUSTOMER_TRX TRX,
190 PA_DRAFT_INVOICES PDI
191 WHERE PDI.SYSTEM_REFERENCE = TRX.CUSTOMER_TRX_ID
192 AND TRX.TRX_NUMBER = AR.TRX_NUMBER
193 AND PDI.PROJECT_ID = PROJECT_ID_1;
194 RETURN (ACCREC);
195 END GET_AR;
196 FUNCTION ACCOUNTS_RECEIVABLEFORMULA(PROJECT_ID IN NUMBER) RETURN NUMBER IS
197 BEGIN
198 RETURN (GET_AR(PROJECT_ID));
199 END ACCOUNTS_RECEIVABLEFORMULA;
200 FUNCTION UNBILLED_RECFORMULA(RETN_ACCOUNTING_FLAG IN VARCHAR2
201 ,TOTAL_REVENUE_AMOUNT IN NUMBER
202 ,PFC_TOTAL_INVOICE_AMOUNT IN NUMBER
203 ,UNBILLED_RETENTION IN NUMBER) RETURN NUMBER IS
204 BEGIN
205 IF (RETN_ACCOUNTING_FLAG = 'N') THEN
206 RETURN (GREATEST((TOTAL_REVENUE_AMOUNT - PFC_TOTAL_INVOICE_AMOUNT - UNBILLED_RETENTION)
207 ,0));
208 ELSE
209 RETURN (GREATEST((TOTAL_REVENUE_AMOUNT - PFC_TOTAL_INVOICE_AMOUNT)
210 ,0));
211 END IF;
212 END UNBILLED_RECFORMULA;
213 FUNCTION UNEARNED_REVFORMULA(RETN_ACCOUNTING_FLAG IN VARCHAR2
214 ,PFC_TOTAL_INVOICE_AMOUNT IN NUMBER
215 ,UNBILLED_RETENTION IN NUMBER
216 ,TOTAL_REVENUE_AMOUNT IN NUMBER) RETURN NUMBER IS
217 BEGIN
218 IF (RETN_ACCOUNTING_FLAG = 'N') THEN
219 RETURN (GREATEST((PFC_TOTAL_INVOICE_AMOUNT + UNBILLED_RETENTION - TOTAL_REVENUE_AMOUNT)
220 ,0));
221 ELSE
222 RETURN (GREATEST((PFC_TOTAL_INVOICE_AMOUNT - TOTAL_REVENUE_AMOUNT)
223 ,0));
224 END IF;
225 END UNEARNED_REVFORMULA;
226 FUNCTION AFTERREPORT RETURN BOOLEAN IS
227 BEGIN
228 BEGIN
229 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
230 END;
231 RETURN (TRUE);
232 END AFTERREPORT;
233 FUNCTION C_INDENTED_TASK_NUMBERFORMULA(WBS_LEVEL IN NUMBER
234 ,TASK_NUMBER IN VARCHAR2) RETURN VARCHAR2 IS
235 BEGIN
236 RETURN (LPAD(' '
237 ,2 * (NVL(WBS_LEVEL
238 ,1) - 1)) || TASK_NUMBER);
239 END C_INDENTED_TASK_NUMBERFORMULA;
240 FUNCTION C_INDENTED_TASK_NAMEFORMULA(WBS_LEVEL IN NUMBER
241 ,TASK_NAME IN VARCHAR2) RETURN VARCHAR2 IS
242 BEGIN
243 RETURN (LPAD(' '
244 ,2 * (NVL(WBS_LEVEL
245 ,1) - 1)) || TASK_NAME);
246 END C_INDENTED_TASK_NAMEFORMULA;
247 FUNCTION UNBILLED_RETNFORMULA(RETN_ACCOUNTING_FLAG IN VARCHAR2
248 ,UNBILLED_RETENTION IN NUMBER) RETURN NUMBER IS
249 BEGIN
250 IF (RETN_ACCOUNTING_FLAG = 'N') THEN
251 RETURN (0);
252 ELSE
253 RETURN (-UNBILLED_RETENTION);
254 END IF;
255 END UNBILLED_RETNFORMULA;
256 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
257 BEGIN
258 RETURN C_COMPANY_NAME_HEADER;
259 END C_COMPANY_NAME_HEADER_P;
260 FUNCTION C_NO_DATA_FOUND_P RETURN VARCHAR2 IS
261 BEGIN
262 RETURN C_NO_DATA_FOUND;
263 END C_NO_DATA_FOUND_P;
264 FUNCTION C_ORG_P RETURN VARCHAR2 IS
265 BEGIN
266 RETURN C_ORG;
267 END C_ORG_P;
268 FUNCTION C_PROJ_NUM_P RETURN VARCHAR2 IS
269 BEGIN
270 RETURN C_PROJ_NUM;
271 END C_PROJ_NUM_P;
272 FUNCTION C_PROJ_NAME_P RETURN VARCHAR2 IS
273 BEGIN
274 RETURN C_PROJ_NAME;
275 END C_PROJ_NAME_P;
276 FUNCTION C_MGR_P RETURN VARCHAR2 IS
277 BEGIN
278 RETURN C_MGR;
279 END C_MGR_P;
280 FUNCTION C_TASK_NUM_P RETURN VARCHAR2 IS
281 BEGIN
282 RETURN C_TASK_NUM;
283 END C_TASK_NUM_P;
284 FUNCTION C_TASK_NAME_P RETURN VARCHAR2 IS
285 BEGIN
286 RETURN C_TASK_NAME;
287 END C_TASK_NAME_P;
288 FUNCTION C_EXPLODE_P RETURN VARCHAR2 IS
289 BEGIN
290 RETURN C_EXPLODE;
291 END C_EXPLODE_P;
292 FUNCTION C_PROJ_TYPE_P RETURN VARCHAR2 IS
293 BEGIN
294 RETURN C_PROJ_TYPE;
295 END C_PROJ_TYPE_P;
296 FUNCTION C_COST_BGT_CODE_P RETURN VARCHAR2 IS
297 BEGIN
298 RETURN C_COST_BGT_CODE;
299 END C_COST_BGT_CODE_P;
300 FUNCTION C_REV_BGT_CODE_P RETURN VARCHAR2 IS
301 BEGIN
302 RETURN C_REV_BGT_CODE;
303 END C_REV_BGT_CODE_P;
304 FUNCTION C_COST_BGT_NAME_P RETURN VARCHAR2 IS
305 BEGIN
306 RETURN C_COST_BGT_NAME;
307 END C_COST_BGT_NAME_P;
308 FUNCTION C_REV_BGT_NAME_P RETURN VARCHAR2 IS
309 BEGIN
310 RETURN C_REV_BGT_NAME;
311 END C_REV_BGT_NAME_P;
312 END PA_PAXBUBSS_XMLP_PKG;