1 PACKAGE BODY PSB_PSBRPPSV_XMLP_PKG AS
2 /* $Header: PSBRPPSVB.pls 120.0 2008/01/07 10:46:34 vijranga noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
7 RETURN (TRUE);
8 END BEFOREREPORT;
9
10 FUNCTION CF_STAGENAME1FORMULA RETURN CHAR IS
11 BEGIN
12 DECLARE
13 CURSOR STAGENAME_CUR IS
14 SELECT
15 NAME
16 FROM
17 PSB_BUDGET_STAGES
18 WHERE BUDGET_STAGE_ID = P_STAGE1;
19 STAGENAME PSB_BUDGET_STAGES.NAME%TYPE;
20 BEGIN
21 OPEN STAGENAME_CUR;
22 FETCH STAGENAME_CUR
23 INTO STAGENAME;
24 CLOSE STAGENAME_CUR;
25 RETURN STAGENAME;
26 END;
27 END CF_STAGENAME1FORMULA;
28
29 FUNCTION CF_STAGENAME2FORMULA RETURN CHAR IS
30 BEGIN
31 DECLARE
32 CURSOR STAGENAME_CUR IS
33 SELECT
34 NAME
35 FROM
36 PSB_BUDGET_STAGES
37 WHERE BUDGET_STAGE_ID = P_STAGE2;
38 STAGENAME PSB_BUDGET_STAGES.NAME%TYPE;
39 BEGIN
40 OPEN STAGENAME_CUR;
41 FETCH STAGENAME_CUR
42 INTO STAGENAME;
43 CLOSE STAGENAME_CUR;
44 RETURN STAGENAME;
45 END;
46 END CF_STAGENAME2FORMULA;
47
48 FUNCTION CF_STAGENAME3FORMULA RETURN CHAR IS
49 BEGIN
50 DECLARE
51 CURSOR STAGENAME_CUR IS
52 SELECT
53 NAME
54 FROM
55 PSB_BUDGET_STAGES
56 WHERE BUDGET_STAGE_ID = P_STAGE3;
57 STAGENAME PSB_BUDGET_STAGES.NAME%TYPE;
58 BEGIN
59 OPEN STAGENAME_CUR;
60 FETCH STAGENAME_CUR
61 INTO STAGENAME;
62 CLOSE STAGENAME_CUR;
63 RETURN STAGENAME;
64 END;
65 END CF_STAGENAME3FORMULA;
66
67 FUNCTION CF_AMOUNTFORMULA RETURN CHAR IS
68 BEGIN
69 IF (P_STAGE3 IS NOT NULL) THEN
70 RETURN 'Amount';
71 ELSE
72 RETURN '';
73 END IF;
74 END CF_AMOUNTFORMULA;
75
76 FUNCTION CF_FTEAMOUNTFORMULA RETURN CHAR IS
77 BEGIN
78 IF (P_STAGE3 IS NOT NULL) THEN
79 RETURN 'FTE Amount';
80 ELSE
81 RETURN '';
82 END IF;
83 END CF_FTEAMOUNTFORMULA;
84
85 FUNCTION CF_AMOUNTLINEFORMULA RETURN CHAR IS
86 BEGIN
87 IF (P_STAGE3 IS NOT NULL) THEN
88 RETURN '------------';
89 ELSE
90 RETURN '';
91 END IF;
92 END CF_AMOUNTLINEFORMULA;
93
94 FUNCTION CF_FTEAMOUNTLINEFORMULA RETURN CHAR IS
95 BEGIN
96 IF (P_STAGE3 IS NOT NULL) THEN
97 RETURN '--------------';
98 ELSE
99 RETURN '';
100 END IF;
101 END CF_FTEAMOUNTLINEFORMULA;
102
103 FUNCTION CF_DATAFOUNDFORMULA RETURN NUMBER IS
104 BEGIN
105 DECLARE
106 CURSOR DATA_FOUND_CUR IS
107 SELECT
108 count(*)
109 FROM
110 PSB_WORKSHEETS PW,
111 PSB_WS_LINES PWL,
112 PSB_WS_ACCOUNT_LINES PWAL,
113 PSB_SERVICE_PACKAGES PSP,
114 PSB_BUDGET_GROUPS PBG,
115 PSB_BUDGET_STAGE_SETS PBSS,
116 PSB_BUDGET_STAGES PBS,
117 PSB_WS_FTE_LINES PWFL,
118 PSB_WS_POSITION_LINES PWPL,
119 PSB_POSITIONS PP,
120 PSB_BUDGET_PERIODS PBP
121 WHERE PW.WORKSHEET_ID = PWL.WORKSHEET_ID
122 AND PWL.ACCOUNT_LINE_ID = PWAL.ACCOUNT_LINE_ID
123 AND PW.BUDGET_GROUP_ID = PBG.BUDGET_GROUP_ID
124 AND PWAL.SERVICE_PACKAGE_ID = PSP.SERVICE_PACKAGE_ID
125 AND PW.STAGE_SET_ID = PBSS.BUDGET_STAGE_SET_ID
126 AND PBS.BUDGET_STAGE_SET_ID = PBSS.BUDGET_STAGE_SET_ID
127 AND PWAL.POSITION_LINE_ID = PWPL.POSITION_LINE_ID
128 AND PWPL.POSITION_ID = PP.POSITION_ID
129 AND PWPL.POSITION_ID = PWFL.POSITION_LINE_ID
130 AND PBP.BUDGET_PERIOD_ID = PWAL.BUDGET_YEAR_ID
131 AND PBS.SEQUENCE_NUMBER BETWEEN PWAL.START_STAGE_SEQ
132 AND NVL(PWAL.END_STAGE_SEQ
133 ,PWAL.CURRENT_STAGE_SEQ)
134 AND BALANCE_TYPE = 'E'
135 AND PW.WORKSHEET_ID = P_WORKSHEET_ID
136 AND PBS.BUDGET_STAGE_ID IN ( P_STAGE1 , P_STAGE2 , P_STAGE3 )
137 AND PWAL.BUDGET_YEAR_ID LIKE NVL(P_BUDGET_YEAR_ID
138 ,PWAL.BUDGET_YEAR_ID);
139 DATA_COUNT NUMBER;
140 BEGIN
141 OPEN DATA_FOUND_CUR;
142 FETCH DATA_FOUND_CUR
143 INTO DATA_COUNT;
144 CLOSE DATA_FOUND_CUR;
145 RETURN DATA_COUNT;
146 END;
147 END CF_DATAFOUNDFORMULA;
148
149 FUNCTION CF_VARIANCE_AMOUNTFORMULA(YEAR_AMOUNT2 IN NUMBER
150 ,YEAR_AMOUNT1 IN NUMBER) RETURN NUMBER IS
151 L_VARIANCE NUMBER;
152 BEGIN
153 L_VARIANCE := NVL(YEAR_AMOUNT2
154 ,0) - NVL(YEAR_AMOUNT1
155 ,0);
156 RETURN (L_VARIANCE);
157 END CF_VARIANCE_AMOUNTFORMULA;
158
159 FUNCTION CF_VARIANCE_PERCENTFORMULA(CF_VARIANCE_AMOUNT IN NUMBER
160 ,YEAR_AMOUNT1 IN NUMBER) RETURN NUMBER IS
161 L_PERCENT NUMBER;
162 BEGIN
163 L_PERCENT := (CF_VARIANCE_AMOUNT / (NVL(YEAR_AMOUNT1
164 ,0))) * 100;
165 RETURN (L_PERCENT);
166 EXCEPTION
167 WHEN OTHERS THEN
168 L_PERCENT := NULL;
169 RETURN (L_PERCENT);
170 END CF_VARIANCE_PERCENTFORMULA;
171
172 FUNCTION AFTERREPORT RETURN BOOLEAN IS
173 BEGIN
174 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
175 RETURN (TRUE);
176 END AFTERREPORT;
177
178 END PSB_PSBRPPSV_XMLP_PKG;
179
180
181
182
183
[Home] [Help]