1 PACKAGE BODY ONT_OEXAUDHR_XMLP_PKG AS
2 /* $Header: OEXAUDHRB.pls 120.1 2007/12/25 07:08:10 npannamp noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 IF P_END_DATE IS NOT NULL THEN
6 P_WHERE := P_WHERE || ' and trunc(a.hist_creation_date,''DD'') <= trunc(:p_end_date,''DD'' ) ';
7 END IF;
8 IF P_ENTITY_ID IS NOT NULL THEN
9 P_WHERE := P_WHERE || ' and a.entity_id = :p_entity_id ';
10 END IF;
11 IF P_ATTRIBUTE_ID IS NOT NULL THEN
12 P_WHERE := P_WHERE || ' and a.attribute_id = :p_attribute_id ';
13 END IF;
14 IF P_ORDER_FROM IS NOT NULL AND P_ORDER_TO IS NOT NULL THEN
15 P_WHERE := P_WHERE || ' and a.order_number between :p_order_from and :p_order_to ';
16 ELSIF P_ORDER_FROM IS NOT NULL AND P_ORDER_TO IS NULL THEN
17 P_WHERE := P_WHERE || ' and a.order_number >= :p_order_from ';
18 ELSIF P_ORDER_FROM IS NULL AND P_ORDER_TO IS NOT NULL THEN
19 P_WHERE := P_WHERE || ' and a.order_number <= :p_order_to ';
20 END IF;
21 IF P_USER_ID IS NOT NULL THEN
22 P_WHERE := P_WHERE || ' and a.user_id = :p_user_id ';
23 END IF;
24 IF P_RESP_ID IS NOT NULL THEN
25 P_WHERE := P_WHERE || ' and a.responsibility_id = :p_resp_id ';
26 END IF;
27 RETURN (TRUE);
28 END AFTERPFORM;
29
30 FUNCTION CF_NO_DATA_FOUND1FORMULA(ORDER_NUMBER1 IN NUMBER) RETURN NUMBER IS
31 BEGIN
32 CP_NO_DATA_FOUND := ORDER_NUMBER1;
33 RETURN (0);
34 END CF_NO_DATA_FOUND1FORMULA;
35
36 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
37 BEGIN
38 BEGIN
39 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
40 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
41 EXCEPTION
42 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
43 BEGIN
44 /*SRW.MESSAGE(1
45 ,'FAILED IN BEFORE REPORT TRIGGER')*/NULL;
46 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
47 RETURN (FALSE);
48 END;
49 END;
50 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL < '110508' THEN
51 /*SRW.MESSAGE(90000
52 ,'This report is currently not available')*/NULL;
53 RETURN (FALSE);
54 END IF;
55 BEGIN
56 DECLARE
57 L_REPORT_NAME VARCHAR2(240);
58 BEGIN
59 SELECT
60 CP.USER_CONCURRENT_PROGRAM_NAME
61 INTO L_REPORT_NAME
62 FROM
63 FND_CONCURRENT_PROGRAMS_VL CP,
64 FND_CONCURRENT_REQUESTS CR
65 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
66 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
67 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
68 CP_REPORT_NAME := L_REPORT_NAME;
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 CP_REPORT_NAME := 'Audit History Report';
72 END;
73 DECLARE
74 L_ENTITY_DISPLAY_NAME VARCHAR2(100);
75 BEGIN
76 SELECT
77 ENTITY_DISPLAY_NAME
78 INTO L_ENTITY_DISPLAY_NAME
79 FROM
80 OE_PC_ENTITIES_V
81 WHERE ENTITY_ID = P_ENTITY_ID;
82 CP_ENTITY_NAME := '(' || L_ENTITY_DISPLAY_NAME || ')';
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 CP_ENTITY_NAME := NULL;
86 END;
87 DECLARE
88 L_USER_NAME VARCHAR2(80);
89 BEGIN
90 SELECT
91 USER_NAME
92 INTO L_USER_NAME
93 FROM
94 FND_USER
95 WHERE USER_ID = P_USER_ID;
96 CP_USER_NAME := L_USER_NAME;
97 EXCEPTION
98 WHEN NO_DATA_FOUND THEN
99 CP_USER_NAME := NULL;
100 END;
101 DECLARE
102 L_RESPONSIBILITY_NAME VARCHAR2(240);
103 BEGIN
104 SELECT
105 RESPONSIBILITY_NAME
106 INTO L_RESPONSIBILITY_NAME
107 FROM
108 FND_RESPONSIBILITY_TL
109 WHERE RESPONSIBILITY_ID = P_RESP_ID
110 AND LANGUAGE = USERENV('LANG');
111 CP_RESPONSIBILITY_NAME := L_RESPONSIBILITY_NAME;
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
114 CP_RESPONSIBILITY_NAME := NULL;
115 END;
116 DECLARE
117 L_ATTRIBUTE_DISPLAY_NAME VARCHAR2(100);
118 BEGIN
119 SELECT
120 ATTRIBUTE_DISPLAY_NAME
121 INTO L_ATTRIBUTE_DISPLAY_NAME
122 FROM
123 OE_PC_ATTRIBUTES_V
124 WHERE ATTRIBUTE_ID = P_ATTRIBUTE_ID;
125 CP_ATTRIBUTE_NAME := '(' || L_ATTRIBUTE_DISPLAY_NAME || ')';
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 CP_ATTRIBUTE_NAME := NULL;
129 END;
130 DECLARE
131 L_SOB_NAME VARCHAR2(240);
132 BEGIN
133 SELECT
134 SOB.NAME
135 INTO L_SOB_NAME
136 FROM
137 GL_SETS_OF_BOOKS SOB
138 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID;
139 RP_COMPANY_NAME := L_SOB_NAME;
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142 RP_COMPANY_NAME := NULL;
143 END;
144 END;
145 LP_END_DATE:=to_char(P_END_DATE,'DD-MON-YY');
146 LP_START_DATE:=to_char(P_START_DATE,'DD-MON-YY');
147 RETURN (TRUE);
148 END BEFOREREPORT;
149
150 FUNCTION CF_1FORMULA(EID IN NUMBER
151 ,LINEID1 IN NUMBER) RETURN VARCHAR2 IS
152 T_LINE_NO NUMBER;
153 T_SHIP_NO NUMBER;
154 T_OPTION_NO NUMBER;
155 T_COMPONENT_NO NUMBER;
156 T_SERVICE_NO NUMBER;
157 T_CONCATED_VALUE VARCHAR2(20);
158 BEGIN
159 IF EID = 2 THEN
160 BEGIN
161 SELECT
162 LINE_NUMBER,
163 SHIPMENT_NUMBER,
164 OPTION_NUMBER,
165 COMPONENT_NUMBER,
166 SERVICE_NUMBER
167 INTO T_LINE_NO,T_SHIP_NO,T_OPTION_NO,T_COMPONENT_NO,T_SERVICE_NO
168 FROM
169 OE_ORDER_LINES_ALL
170 WHERE LINE_ID = LINEID1;
171 EXCEPTION
172 WHEN OTHERS THEN
173 RETURN NULL;
174 END;
175 ELSIF EID = 7 THEN
176 BEGIN
177 SELECT
178 LINE_NUMBER,
179 SHIPMENT_NUMBER,
180 OPTION_NUMBER,
181 COMPONENT_NUMBER,
182 SERVICE_NUMBER
183 INTO T_LINE_NO,T_SHIP_NO,T_OPTION_NO,T_COMPONENT_NO,T_SERVICE_NO
184 FROM
185 OE_ORDER_LINES_ALL
186 WHERE LINE_ID = (
187 SELECT
188 DISTINCT
189 LINE_ID
190 FROM
191 OE_SALES_CREDITS
192 WHERE SALES_CREDIT_ID = LINEID1 );
193 EXCEPTION
194 WHEN OTHERS THEN
195 RETURN NULL;
196 END;
197 ELSIF EID = 8 THEN
198 BEGIN
199 SELECT
200 LINE_NUMBER,
201 SHIPMENT_NUMBER,
202 OPTION_NUMBER,
203 COMPONENT_NUMBER,
204 SERVICE_NUMBER
205 INTO T_LINE_NO,T_SHIP_NO,T_OPTION_NO,T_COMPONENT_NO,T_SERVICE_NO
206 FROM
207 OE_ORDER_LINES_ALL
208 WHERE LINE_ID = (
209 SELECT
210 DISTINCT
211 LINE_ID
212 FROM
213 OE_PRICE_ADJUSTMENTS
214 WHERE PRICE_ADJUSTMENT_ID = LINEID1 );
215 EXCEPTION
216 WHEN OTHERS THEN
217 RETURN NULL;
218 END;
219 ELSE
220 RETURN NULL;
221 END IF;
222 IF T_SERVICE_NO IS NOT NULL THEN
223 IF T_OPTION_NO IS NOT NULL THEN
224 IF T_COMPONENT_NO IS NOT NULL THEN
225 T_CONCATED_VALUE := T_LINE_NO || '.' || T_SHIP_NO || '.' || T_OPTION_NO || '.' || T_COMPONENT_NO || '.' || T_SERVICE_NO;
226 ELSE
227 T_CONCATED_VALUE := T_LINE_NO || '.' || T_SHIP_NO || '.' || T_OPTION_NO || '..' || T_SERVICE_NO;
228 END IF;
229 ELSE
230 IF T_COMPONENT_NO IS NOT NULL THEN
231 T_CONCATED_VALUE := T_LINE_NO || '.' || T_SHIP_NO || '..' || T_COMPONENT_NO || '.' || T_SERVICE_NO;
232 ELSE
233 T_CONCATED_VALUE := T_LINE_NO || '.' || T_SHIP_NO || '...' || T_SERVICE_NO;
234 END IF;
235 END IF;
236 ELSE
237 IF T_OPTION_NO IS NOT NULL THEN
238 IF T_COMPONENT_NO IS NOT NULL THEN
239 T_CONCATED_VALUE := T_LINE_NO || '.' || T_SHIP_NO || '.' || T_OPTION_NO || '.' || T_COMPONENT_NO;
240 ELSE
241 T_CONCATED_VALUE := T_LINE_NO || '.' || T_SHIP_NO || '.' || T_OPTION_NO;
242 END IF;
243 ELSE
244 IF T_COMPONENT_NO IS NOT NULL THEN
245 T_CONCATED_VALUE := T_LINE_NO || '.' || T_SHIP_NO || '..' || T_COMPONENT_NO;
246 ELSE
247 IF (T_LINE_NO IS NULL AND T_SHIP_NO IS NULL) THEN
248 T_CONCATED_VALUE := NULL;
249 ELSE
250 T_CONCATED_VALUE := T_LINE_NO || '.' || T_SHIP_NO;
251 END IF;
252 END IF;
253 END IF;
254 END IF;
255 RETURN T_CONCATED_VALUE;
256 END CF_1FORMULA;
257
258 FUNCTION AFTERREPORT RETURN BOOLEAN IS
259 BEGIN
260 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
261 RETURN (TRUE);
262 EXCEPTION
263 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
264 /*SRW.MESSAGE(1
265 ,'FAILED IN AFTER REPORT TRIGGER')*/NULL;
266 RETURN (FALSE);
267 END AFTERREPORT;
268
269 FUNCTION CP_NO_DATA_FOUND_P RETURN NUMBER IS
270 BEGIN
271 RETURN CP_NO_DATA_FOUND;
272 END CP_NO_DATA_FOUND_P;
273
274 FUNCTION CP_ATTRIBUTE_NAME_P RETURN VARCHAR2 IS
275 BEGIN
276 RETURN CP_ATTRIBUTE_NAME;
277 END CP_ATTRIBUTE_NAME_P;
278
279 FUNCTION CP_ENTITY_NAME_P RETURN VARCHAR2 IS
280 BEGIN
281 RETURN CP_ENTITY_NAME;
282 END CP_ENTITY_NAME_P;
283
284 FUNCTION CP_REPORT_NAME_P RETURN VARCHAR2 IS
285 BEGIN
286 RETURN CP_REPORT_NAME;
287 END CP_REPORT_NAME_P;
288
289 FUNCTION CP_USER_NAME_P RETURN VARCHAR2 IS
290 BEGIN
291 RETURN CP_USER_NAME;
292 END CP_USER_NAME_P;
293
294 FUNCTION CP_RESPONSIBILITY_NAME_P RETURN VARCHAR2 IS
295 BEGIN
296 RETURN CP_RESPONSIBILITY_NAME;
297 END CP_RESPONSIBILITY_NAME_P;
298
299 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
300 BEGIN
301 RETURN RP_COMPANY_NAME;
302 END RP_COMPANY_NAME_P;
303
304 END ONT_OEXAUDHR_XMLP_PKG;
305
[Home] [Help]