[Home] [Help]
PACKAGE BODY: APPS.JL_JLCOFAAR_XMLP_PKG
Source
1 PACKAGE BODY JL_JLCOFAAR_XMLP_PKG AS
2 /* $Header: JLCOFAARB.pls 120.1 2007/12/25 16:44:01 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 CURRENCY_VALIDATION;
6 GET_BASE_CURR_DATA;
7 RETURN (TRUE);
8 END BEFOREREPORT;
9
10 PROCEDURE GET_BASE_CURR_DATA IS
11 COMPANY_NAME FA_SYSTEM_CONTROLS.COMPANY_NAME%TYPE;
12 BEGIN
13 COMPANY_NAME := '';
14 BEGIN
15 SELECT
16 FS.COMPANY_NAME
17 INTO COMPANY_NAME
18 FROM
19 FA_SYSTEM_CONTROLS FS;
20 EXCEPTION
21 WHEN OTHERS THEN
22 RAISE_ORA_ERR('JL_CO_FA_GENERAL_ERROR');
23 END;
24 C_COMPANY_NAME := COMPANY_NAME;
25 IF P_ALL_ROWS = 'Y' THEN
26 C_STATUS := ' ';
27 ELSIF P_ALL_ROWS = 'N' THEN
28 C_STATUS := 'AND aspa.status not in (''V'', ''P'')';
29 END IF;
30 P_MIN_PRECISION := 2;
31 END GET_BASE_CURR_DATA;
32
33 FUNCTION AFTERREPORT RETURN BOOLEAN IS
34 BEGIN
35 BEGIN
36 NULL;
37 EXCEPTION
38 WHEN OTHERS THEN
39 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
40 END;
41 RETURN (TRUE);
42 END AFTERREPORT;
43
44 PROCEDURE RAISE_ORA_ERR(MSGNAME IN VARCHAR2) IS
45 ERRMSG VARCHAR2(1000);
46 ERRNUM NUMBER;
47 BEGIN
48 FND_MESSAGE.SET_NAME('JL'
49 ,MSGNAME);
50 ERRMSG := FND_MESSAGE.GET;
51 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
52 ,MSGNAME)
53 ,ERRMSG)*/NULL;
54 ERRNUM := SQLCODE;
55 ERRMSG := SQLERRM;
56 /*SRW.MESSAGE(ERRNUM
57 ,ERRMSG)*/NULL;
58 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
59 END RAISE_ORA_ERR;
60
61 PROCEDURE RAISE_ERR(MSGNAME IN VARCHAR2) IS
62 ERRMSG VARCHAR2(1000);
63 BEGIN
64 FND_MESSAGE.SET_NAME('JL'
65 ,MSGNAME);
66 ERRMSG := FND_MESSAGE.GET;
67 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
68 ,MSGNAME)
69 ,ERRMSG)*/NULL;
70 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
71 END RAISE_ERR;
72
73 PROCEDURE CURRENCY_VALIDATION IS
74 CURSOR CURRENCY IS
75 SELECT
76 DISTINCT
77 CURRENCY_CODE
78 FROM
79 JL_CO_FA_APPRAISALS
80 WHERE APPRAISAL_ID = P_APPRAISAL_ID;
81 CURRVAR VARCHAR2(15);
82 DUMMY VARCHAR2(15);
83 ERRMSG VARCHAR2(1000);
84 BEGIN
85 OPEN CURRENCY;
86 LOOP
87 FETCH CURRENCY
88 INTO CURRVAR;
89 EXIT WHEN CURRENCY%NOTFOUND;
90 CP_VALID_CURR_CODE := 0;
91 BEGIN
92 SELECT
93 1
94 INTO DUMMY
95 FROM
96 FND_CURRENCIES
97 WHERE CURRENCY_CODE = CURRVAR;
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 FND_MESSAGE.SET_NAME('JL'
101 ,'JL_CO_FA_INVALID_CURRENCY_CODE');
102 FND_MESSAGE.SET_TOKEN('APPRAISAL_NUMBER'
103 ,TO_CHAR(P_APPRAISAL_ID)
104 ,FALSE);
105 ERRMSG := FND_MESSAGE.GET;
106 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
107 ,'JL_CO_FA_INVALID_CURRENCY_CODE')
108 ,ERRMSG)*/NULL;
109 CP_VALID_CURR_CODE := 1;
110 WHEN OTHERS THEN
111 RAISE_ORA_ERR('JL_CO_FA_GENERAL_ERROR');
112 END;
113 END LOOP;
114 END CURRENCY_VALIDATION;
115
116 FUNCTION CF_1FORMULA RETURN NUMBER IS
117 X NUMBER;
118 BEGIN
119 SELECT
120 COUNT(*)
121 INTO X
122 FROM
123 JL_CO_FA_APPRAISALS AP,
124 JL_CO_FA_ASSET_APPRS ASP,
125 FND_LOOKUPS FLH,
126 FND_LOOKUPS FLD
127 WHERE AP.APPRAISAL_ID = P_APPRAISAL_ID
128 AND ASP.APPRAISAL_ID = AP.APPRAISAL_ID
129 AND DECODE(P_ALL_ROWS
130 ,NULL
131 ,'1'
132 ,'Y'
133 ,'1'
134 ,ASP.STATUS) NOT IN ( DECODE(P_ALL_ROWS
135 ,NULL
136 ,'2'
137 ,'Y'
138 ,'2'
139 ,'P') )
140 AND FLH.LOOKUP_TYPE = 'JLCO_FA_ASSET_APPRAISAL_STATUS'
141 AND FLH.LOOKUP_CODE = AP.APPRAISAL_STATUS
142 AND FLD.LOOKUP_TYPE = FLH.LOOKUP_TYPE
143 AND FLD.LOOKUP_CODE = ASP.STATUS
144 AND UPPER(FLD.ENABLED_FLAG) = 'Y'
145 AND SYSDATE BETWEEN NVL(FLD.START_DATE_ACTIVE
146 ,SYSDATE - 1)
147 AND NVL(FLD.END_DATE_ACTIVE
148 ,SYSDATE + 1);
149 RETURN X;
150 END CF_1FORMULA;
151
152 FUNCTION CF_2FORMULA(STATUS_CODE IN VARCHAR2) RETURN NUMBER IS
153 X NUMBER;
154 BEGIN
155 SELECT
156 COUNT(*)
157 INTO X
158 FROM
159 JL_CO_FA_APPRAISALS AP,
160 JL_CO_FA_ASSET_APPRS ASP,
161 FND_LOOKUPS FLH,
162 FND_LOOKUPS FLD
163 WHERE AP.APPRAISAL_ID = P_APPRAISAL_ID
164 AND ASP.APPRAISAL_ID = AP.APPRAISAL_ID
165 AND DECODE(P_ALL_ROWS
166 ,NULL
167 ,'1'
168 ,'Y'
169 ,'1'
170 ,ASP.STATUS) NOT IN ( DECODE(P_ALL_ROWS
171 ,NULL
172 ,'2'
173 ,'Y'
174 ,'2'
175 ,'P') )
176 AND ASP.STATUS = STATUS_CODE
177 AND FLH.LOOKUP_TYPE = 'JLCO_FA_ASSET_APPRAISAL_STATUS'
178 AND FLH.LOOKUP_CODE = AP.APPRAISAL_STATUS
179 AND FLD.LOOKUP_TYPE = FLH.LOOKUP_TYPE
180 AND FLD.LOOKUP_CODE = ASP.STATUS
181 AND UPPER(FLD.ENABLED_FLAG) = 'Y'
182 AND SYSDATE BETWEEN NVL(FLD.START_DATE_ACTIVE
183 ,SYSDATE - 1)
184 AND NVL(FLD.END_DATE_ACTIVE
185 ,SYSDATE + 1);
186 RETURN X;
187 END CF_2FORMULA;
188
189 FUNCTION CF_3FORMULA(STATUS_DESC IN VARCHAR2) RETURN VARCHAR2 IS
190 MSG_TEXT VARCHAR2(2000);
191 BEGIN
192 FND_MESSAGE.SET_NAME('JL'
193 ,'JL_CO_FA_ASSET_SUMMARY_TEXT');
194 MSG_TEXT := FND_MESSAGE.GET || ' ' || STATUS_DESC;
195 RETURN (MSG_TEXT);
196 END CF_3FORMULA;
197
198 FUNCTION C_COMPANY_NAME_P RETURN VARCHAR2 IS
199 BEGIN
200 RETURN C_COMPANY_NAME;
201 END C_COMPANY_NAME_P;
202
203 FUNCTION C_STATUS_P RETURN VARCHAR2 IS
204 BEGIN
205 RETURN C_STATUS;
206 END C_STATUS_P;
207
208 FUNCTION CP_VALID_CURR_CODE_P RETURN NUMBER IS
209 BEGIN
210 RETURN CP_VALID_CURR_CODE;
211 END CP_VALID_CURR_CODE_P;
212
213 END JL_JLCOFAAR_XMLP_PKG;
214
215
216