1 PACKAGE BODY PSB_PSBRPACV_XMLP_PKG AS
2 /* $Header: PSBRPACVB.pls 120.0 2008/01/07 10:29:56 vijranga noship $ */
3 FUNCTION CF_STAGENAME1FORMULA RETURN CHAR IS
4 BEGIN
5 DECLARE
6 CURSOR STAGENAME_CUR IS
7 SELECT
8 NAME
9 FROM
10 PSB_BUDGET_STAGES
11 WHERE BUDGET_STAGE_ID = P_STAGE1;
12 STAGENAME PSB_BUDGET_STAGES.NAME%TYPE;
13 BEGIN
14 OPEN STAGENAME_CUR;
15 FETCH STAGENAME_CUR
16 INTO STAGENAME;
17 CLOSE STAGENAME_CUR;
18 RETURN STAGENAME;
19 END;
20 END CF_STAGENAME1FORMULA;
21
22 FUNCTION CF_STAGENAME2FORMULA RETURN CHAR IS
23 BEGIN
24 DECLARE
25 CURSOR STAGENAME_CUR IS
26 SELECT
27 NAME
28 FROM
29 PSB_BUDGET_STAGES
30 WHERE BUDGET_STAGE_ID = P_STAGE2;
31 STAGENAME PSB_BUDGET_STAGES.NAME%TYPE;
32 BEGIN
33 OPEN STAGENAME_CUR;
34 FETCH STAGENAME_CUR
35 INTO STAGENAME;
36 CLOSE STAGENAME_CUR;
37 RETURN STAGENAME;
38 END;
39 END CF_STAGENAME2FORMULA;
40
41 FUNCTION CF_STAGENAME3FORMULA RETURN CHAR IS
42 BEGIN
43 DECLARE
44 CURSOR STAGENAME_CUR IS
45 SELECT
46 NAME
47 FROM
48 PSB_BUDGET_STAGES
49 WHERE BUDGET_STAGE_ID = P_STAGE3;
50 STAGENAME PSB_BUDGET_STAGES.NAME%TYPE;
51 BEGIN
52 OPEN STAGENAME_CUR;
53 FETCH STAGENAME_CUR
54 INTO STAGENAME;
55 CLOSE STAGENAME_CUR;
56 RETURN STAGENAME;
57 EXCEPTION
58 WHEN NO_DATA_FOUND THEN
59 STAGENAME := NULL;
60 RETURN STAGENAME;
61 END;
62 END CF_STAGENAME3FORMULA;
63
64 FUNCTION CF_STAGENAME4FORMULA RETURN CHAR IS
65 BEGIN
66 DECLARE
67 CURSOR STAGENAME_CUR IS
68 SELECT
69 NAME
70 FROM
71 PSB_BUDGET_STAGES
72 WHERE BUDGET_STAGE_ID = P_STAGE4;
73 STAGENAME PSB_BUDGET_STAGES.NAME%TYPE;
74 BEGIN
75 OPEN STAGENAME_CUR;
76 FETCH STAGENAME_CUR
77 INTO STAGENAME;
78 CLOSE STAGENAME_CUR;
79 RETURN STAGENAME;
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 STAGENAME := NULL;
83 RETURN STAGENAME;
84 END;
85 END CF_STAGENAME4FORMULA;
86
87 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
88 BEGIN
89 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
90 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
91 RETURN (TRUE);
92 END BEFOREREPORT;
93
94 FUNCTION CF_NODATAFOUNDFORMULA RETURN CHAR IS
95 VDATAFOUND NUMBER := 0;
96 VNODATAMESG VARCHAR2(100);
97 BEGIN
98 SELECT
99 count(*)
100 INTO VDATAFOUND
101 FROM
102 PSB_WORKSHEETS PW,
103 PSB_WS_LINES PWL,
104 PSB_WS_ACCOUNT_LINES PWAL,
105 PSB_SERVICE_PACKAGES PSP,
106 PSB_BUDGET_GROUPS PBG,
107 PSB_BUDGET_STAGE_SETS PBSS,
108 PSB_BUDGET_STAGES PBS,
109 PSB_BUDGET_PERIODS PBP
110 WHERE PW.WORKSHEET_ID = PWL.WORKSHEET_ID
111 AND PWL.ACCOUNT_LINE_ID = PWAL.ACCOUNT_LINE_ID
112 AND PW.BUDGET_GROUP_ID = PBG.BUDGET_GROUP_ID
113 AND PWAL.SERVICE_PACKAGE_ID = PSP.SERVICE_PACKAGE_ID
114 AND PW.STAGE_SET_ID = PBSS.BUDGET_STAGE_SET_ID
115 AND PBS.BUDGET_STAGE_SET_ID = PBSS.BUDGET_STAGE_SET_ID
116 AND PBP.BUDGET_PERIOD_ID = PWAL.BUDGET_YEAR_ID
117 AND PBS.SEQUENCE_NUMBER BETWEEN PWAL.START_STAGE_SEQ
118 AND NVL(PWAL.END_STAGE_SEQ
119 ,PWAL.CURRENT_STAGE_SEQ)
120 AND BALANCE_TYPE = 'E'
121 AND PW.WORKSHEET_ID = P_WORKSHEET_ID
122 AND PBS.BUDGET_STAGE_ID IN ( P_STAGE1 , P_STAGE2 , P_STAGE3 , P_STAGE4 )
123 AND PWAL.BUDGET_YEAR_ID LIKE NVL(P_BUDGET_YEAR_ID
124 ,PWAL.BUDGET_YEAR_ID);
125 IF VDATAFOUND = 0 THEN
126 VNODATAMESG := ' ****No Data Found**** ';
127 ELSE
128 VNODATAMESG := NULL;
129 END IF;
130 RETURN VNODATAMESG;
131 END CF_NODATAFOUNDFORMULA;
132
133 FUNCTION CF_ACCOUNTINGFLEXFIELDFORMULA(CC_ID IN NUMBER) RETURN CHAR IS
134 CURSOR ACCTFLEX_CUR IS
135 SELECT
136 CONCATENATED_SEGMENTS
137 FROM
138 GL_CODE_COMBINATIONS_KFV
139 WHERE CODE_COMBINATION_ID = CC_ID;
140 VACCOUNTINGFLEXFIELD GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
141 BEGIN
142 OPEN ACCTFLEX_CUR;
143 FETCH ACCTFLEX_CUR
144 INTO VACCOUNTINGFLEXFIELD;
145 CLOSE ACCTFLEX_CUR;
146 RETURN VACCOUNTINGFLEXFIELD;
147 END CF_ACCOUNTINGFLEXFIELDFORMULA;
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_PSBRPACV_XMLP_PKG;
179
180
181
182
183