[Home] [Help]
PACKAGE BODY: APPS.XTR_XTRTMODR_XMLP_PKG
Source
1 PACKAGE BODY XTR_XTRTMODR_XMLP_PKG AS
2 /* $Header: XTRTMODRB.pls 120.1 2007/12/31 12:26:20 vjaganat noship $ */
3 FUNCTION INT_COMPONENT1FORMULA RETURN VARCHAR2 IS
4 BEGIN
5 EXECUTE IMMEDIATE
6 'SELECT DECODE(SIGN(:INT_OD_RUNNING - :INT_OVERDUE2 - :INT_OVERDUE),-1,DECODE(SIGN(:INT_OD_RUNNING - :INT_OVERDUE),1,0,round(:INT_OVERDUE2,2)),0) INTO :INT_COMPONENT FROM DUAL';
7 COMMIT;
8 RETURN ('');
9 END INT_COMPONENT1FORMULA;
10
11 FUNCTION BAL_COMPONENT1FORMULA RETURN VARCHAR2 IS
12 BEGIN
13 EXECUTE IMMEDIATE
14 'SELECT DECODE(SIGN(:BAL_RUNNING - :BAL_OVERDUE2 - :BAL_OVERDUE),-1,DECODE(SIGN(:BAL_RUNNING - :BAL_OVERDUE),1,round(:BAL_OVERDUE - :BAL_RUNNING + :BAL_OVERDUE2,2),0,0,round(:BAL_OVERDUE2,2)),0) INTO :BAL_COMPONENT FROM DUAL';
15 COMMIT;
16 RETURN ('');
17 END BAL_COMPONENT1FORMULA;
18
19 FUNCTION TOT_OVERDUE1FORMULA RETURN VARCHAR2 IS
20 BEGIN
21 EXECUTE IMMEDIATE
22 'SELECT NVL(round(:INT_COMPONENT,2),0) + nvl(round(:BAL_COMPONENT,2),0) INTO :TOT_OVERDUE from DUAL';
23 COMMIT;
24 RETURN ('');
25 END TOT_OVERDUE1FORMULA;
26
27 FUNCTION CURR1FORMULA RETURN VARCHAR2 IS
28 BEGIN
29 EXECUTE IMMEDIATE
30 'SELECT DECODE(:DAY30P + :DAY60P + :DAY90P,0,:TOT_OVERDUE,0) INTO :CURR FROM DUAL';
31 COMMIT;
32 RETURN ('');
33 END CURR1FORMULA;
34
35 FUNCTION DAY30P1FORMULA RETURN VARCHAR2 IS
36 BEGIN
37 EXECUTE IMMEDIATE
38 'SELECT DECODE(:DAY60P + :DAY90P,0,DECODE(SIGN(:NUMDAY - 1),1,:TOT_OVERDUE,0),0) INTO :DAY30P FROM DUAL';
39 COMMIT;
40 RETURN ('');
41 END DAY30P1FORMULA;
42
43 FUNCTION DAY60P1FORMULA RETURN VARCHAR2 IS
44 BEGIN
45 EXECUTE IMMEDIATE
46 'SELECT DECODE(:DAY90P,0,DECODE(SIGN(:NUMDAY - 2),1,:TOT_OVERDUE,0),0) INTO :DAY60P FROM DUAL';
47 COMMIT;
48 RETURN ('');
49 END DAY60P1FORMULA;
50
51 FUNCTION DAY90P1FORMULA RETURN VARCHAR2 IS
52 BEGIN
53 EXECUTE IMMEDIATE
54 'SELECT DECODE(SIGN(:NUMDAY - 3),1,:TOT_OVERDUE,0) INTO :DAY90P FROM DUAL';
55 COMMIT;
56 RETURN ('');
57 END DAY90P1FORMULA;
58
59 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
60 L_ERROR NUMBER;
61 BEGIN
62 AS_AT_DATE2 := TRUNC(SYSDATE);
63 RETURN (TRUE);
64 END BEFOREPFORM;
65
66 FUNCTION CF_SET_PARAFORMULA RETURN VARCHAR2 IS
67 BEGIN
68 SELECT
69 SUBSTR(USER
70 ,1
71 ,10)
72 INTO
73 CP_PARA
74 FROM
75 DUAL;
76 RETURN (CP_PARA);
77 END CF_SET_PARAFORMULA;
78
79 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
80 L_DMMY_NUM NUMBER;
81 L_MESSAGE FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
82 apf boolean;
83 CURSOR GET_LANGUAGE_DESC(P_MODULE IN VARCHAR2) IS
84 SELECT
85 ITEM_NAME,
86 SUBSTR(TEXT
87 ,1
88 ,100) LANG_NAME
89 FROM
90 XTR_SYS_LANGUAGES_VL
91 WHERE MODULE_NAME = P_MODULE;
92 BEGIN
93 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
94 apf := AFTERPFORM;
95
96 BEGIN
97 COMPANY_NAME_HEADER := CEP_STANDARD.GET_WINDOW_SESSION_TITLE;
98 EXCEPTION
99 WHEN OTHERS THEN
100 FND_MESSAGE.SET_NAME('XTR'
101 ,'XTR_LOOKUP_ERR');
102 L_MESSAGE := FND_MESSAGE.GET;
103 RAISE_APPLICATION_ERROR(-20101
104 ,NULL);
105 END;
106 IF (P_DISPLAY_DEBUG = 'Y') THEN
107 NULL;
108 END IF;
109 FOR c IN GET_LANGUAGE_DESC('XTRTMODR') LOOP
110 IF C.ITEM_NAME = 'Z1AS_AT_DATE' THEN
111 Z1AS_AT_DATE := C.LANG_NAME;
112 ELSIF C.ITEM_NAME = 'Z1DEAL_NUMBER' THEN
113 Z1DEAL_NUMBER := C.LANG_NAME;
114 ELSIF C.ITEM_NAME = 'Z1ORDER_BY_COLUMN' THEN
115 Z1ORDER_BY_COLUMN := C.LANG_NAME;
116 ELSIF C.ITEM_NAME = 'Z1PARAMETERS' THEN
117 Z1PARAMETERS := C.LANG_NAME;
118 ELSIF C.ITEM_NAME = 'Z1PARTY_CODE' THEN
119 Z1PARTY_CODE := C.LANG_NAME;
120 ELSIF C.ITEM_NAME = 'Z1PARTY_NAME' THEN
121 Z1PARTY_NAME := C.LANG_NAME;
122 ELSIF C.ITEM_NAME = 'Z1PRODUCT' THEN
123 IF P_SORT_BY in ('PRODUCT','AMOUNT') THEN
124 Z1PRODUCT := C.LANG_NAME;
125 ELSE
126 Z2CLIENT := C.LANG_NAME;
127 END IF;
128 ELSIF C.ITEM_NAME = 'Z2CLIENT' THEN
129 IF P_SORT_BY in ('PRODUCT','AMOUNT') THEN
130 Z2CLIENT := C.LANG_NAME;
131 ELSE
132 Z1PRODUCT := C.LANG_NAME;
133 END IF;
134 ELSIF C.ITEM_NAME = 'Z2END_OF_REPORT' THEN
135 Z2END_OF_REPORT := C.LANG_NAME;
136 ELSIF C.ITEM_NAME = 'Z2INTEREST' THEN
137 Z2INTEREST := C.LANG_NAME;
138 ELSIF C.ITEM_NAME = 'Z2NAME' THEN
139 Z2NAME := C.LANG_NAME;
140 ELSIF C.ITEM_NAME = 'Z2NINTY_PLUS_DAYS' THEN
141 Z2NINTY_PLUS_DAYS := C.LANG_NAME;
142 ELSIF C.ITEM_NAME = 'Z2NUMBER' THEN
143 Z2NUMBER := C.LANG_NAME;
144 ELSIF C.ITEM_NAME = 'Z2OVERDUE' THEN
145 Z2OVERDUE := C.LANG_NAME;
146 ELSIF C.ITEM_NAME = 'Z2OVERDUE_AMOUNTS' THEN
147 Z2OVERDUE_AMOUNTS := C.LANG_NAME;
148 ELSIF C.ITEM_NAME = 'Z2PRINCIPAL' THEN
149 Z2PRINCIPAL := C.LANG_NAME;
150 ELSIF C.ITEM_NAME = 'Z2PRODUCT_TOTALS' THEN
151 Z2PRODUCT_TOTALS := C.LANG_NAME;
152 ELSIF C.ITEM_NAME = 'Z2REF' THEN
153 Z2REF := C.LANG_NAME;
154 ELSIF C.ITEM_NAME = 'Z2REPORT_TOTALS' THEN
155 Z2REPORT_TOTALS := C.LANG_NAME;
156 ELSIF C.ITEM_NAME = 'Z2SIXTY_DAYS' THEN
157 Z2SIXTY_DAYS := C.LANG_NAME;
158 ELSIF C.ITEM_NAME = 'Z2THIRTY_DAYS' THEN
159 Z2THIRTY_DAYS := C.LANG_NAME;
160 ELSIF C.ITEM_NAME = 'Z2TOTAL_DUE' THEN
161 Z2TOTAL_DUE := C.LANG_NAME;
162 ELSIF C.ITEM_NAME = 'Z2UNDER' THEN
163 Z2UNDER := C.LANG_NAME;
164 ELSIF C.ITEM_NAME = 'Z2PAGE' THEN
165 Z2PAGE := C.LANG_NAME;
166 ELSIF C.ITEM_NAME = 'REPORT_DATE' THEN
167 REPORT_DATE := C.LANG_NAME;
168 ELSIF C.ITEM_NAME = 'Z1P_FACTOR' THEN
169 Z1P_FACTOR := C.LANG_NAME;
170 END IF;
171 END LOOP;
172 RETURN (TRUE);
173 END BEFOREREPORT;
174
175 FUNCTION AFTERPFORM RETURN BOOLEAN IS
176 TEMP NUMBER(15) := 1000;
177 BEGIN
178 IF P_FACTOR IS NOT NULL THEN
179 SELECT
180 SUBSTR(DESCRIPTION
181 ,1
182 ,20),
183 DECODE(SUBSTR(P_FACTOR
184 ,1
185 ,1)
186 ,'U'
187 ,1
188 ,'T'
189 ,1000
190 ,'M'
191 ,1000000
192 ,'B'
193 ,100000000)
194 INTO
195 LP_FACTOR_DESC
196 ,TEMP
197 FROM
198 FND_LOOKUPS
199 WHERE LOOKUP_TYPE = 'XTR_FACTOR'
200 AND LOOKUP_CODE = SUBSTR(P_FACTOR
201 ,1
202 ,1);
203 ELSE
204 SELECT
205 SUBSTR(DESCRIPTION
206 ,1
207 ,20)
208 INTO
209 LP_FACTOR_DESC
210 FROM
211 FND_LOOKUPS
212 WHERE LOOKUP_TYPE = 'XTR_FACTOR'
213 AND LOOKUP_CODE = 'T';
214 TEMP := 1000;
215 END IF;
216 AMT_UNIT2 := TEMP;
217 LP_AS_OF_DATE := TO_CHAR(TO_DATE(P_AS_OF_DATE
218 ,'YYYY/MM/DD HH24:MI:SS')
219 ,'DD-MON-YYYY');
220 AS_AT_DATE2 := to_date(LP_AS_OF_DATE,'DD-MM-YYYY');
221 CPARTY_NAME2 := P_CPARTY_NAME;
222 PRODUCT_TYPE2 := P_PRODUCT_TYPE;
223 DEAL_NO2 := P_DEAL_NUMBER;
224 CPARTY_CODE2 := P_CPARTY;
225 ORDER_BY_COLUMN2 := P_SORT_BY;
226 IF P_SORT_BY = 'AMOUNT' THEN
227 ORDER_BY_CLAUSE := ' order by group_1,group_3 desc ';
228 ELSIF P_SORT_BY = 'PRODUCT' THEN
229 ORDER_BY_CLAUSE := ' order by group_1,group_2 ';
230 ELSIF P_SORT_BY = 'CLIENT' THEN
231 ORDER_BY_CLAUSE := ' order by group_1, group_2';
232 ELSE
233 ORDER_BY_CLAUSE := ' order by group_1, group_2';
234 END IF;
235 SELECT
236 CP.USER_CONCURRENT_PROGRAM_NAME
237 INTO
238 REPORT_SHORT_NAME
239 FROM
240 FND_CONCURRENT_PROGRAMS_VL CP,
241 FND_CONCURRENT_REQUESTS CR
242 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
243 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
244 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
245
246 REPORT_SHORT_NAME:= substr(REPORT_SHORT_NAME,1,instr(REPORT_SHORT_NAME,' (XML)'));
247
248 RETURN (TRUE);
249 END AFTERPFORM;
250
251 FUNCTION AFTERREPORT RETURN BOOLEAN IS
252 BEGIN
253 RETURN (TRUE);
254 END AFTERREPORT;
255
256 FUNCTION INT_COMPONENT_P RETURN NUMBER IS
257 BEGIN
258 RETURN INT_COMPONENT;
259 END INT_COMPONENT_P;
260
261 FUNCTION BAL_COMPONENT_P RETURN NUMBER IS
262 BEGIN
263 RETURN BAL_COMPONENT;
264 END BAL_COMPONENT_P;
265
266 FUNCTION TOT_OVERDUE_P RETURN NUMBER IS
267 BEGIN
268 RETURN TOT_OVERDUE;
269 END TOT_OVERDUE_P;
270
271 FUNCTION CURR_P RETURN NUMBER IS
272 BEGIN
273 RETURN CURR;
274 END CURR_P;
275
276 FUNCTION DAY30P_P RETURN NUMBER IS
277 BEGIN
278 RETURN DAY30P;
279 END DAY30P_P;
280
281 FUNCTION DAY60P_P RETURN NUMBER IS
282 BEGIN
283 RETURN DAY60P;
284 END DAY60P_P;
285
286 FUNCTION DAY90P_P RETURN NUMBER IS
287 BEGIN
288 RETURN DAY90P;
289 END DAY90P_P;
290
291 FUNCTION CP_PARA_P RETURN VARCHAR2 IS
292 BEGIN
293 RETURN CP_PARA;
294 END CP_PARA_P;
295
296 END XTR_XTRTMODR_XMLP_PKG;
297
298