1 PACKAGE BODY ONT_OEXOECCL_XMLP_PKG AS
2 /* $Header: OEXOECCLB.pls 120.2 2008/05/05 10:14:20 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 EXCEPTION
8 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
9 /*SRW.MESSAGE(1
10 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
11 RETURN (FALSE);
12 END;
13 RETURN (TRUE);
14 END AFTERREPORT;
15 FUNCTION AFTERPFORM RETURN BOOLEAN IS
16 BEGIN
17 BEGIN
18 IF P_CUSTOMER_NAME IS NOT NULL THEN
19 LP_CUSTOMER_NAME := ' and c.name = :p_customer_name';
20 END IF;
21 IF (lp_customer_name IS NULL) THEN
22 lp_customer_name := ' ';
23 END IF;
24 IF P_CUSTOMER_NUMBER IS NOT NULL THEN
25 LP_CUSTOMER_NUMBER := ' and c.customer_number = :p_customer_number';
26 END IF;
27 IF (lp_customer_number IS NULL) THEN
28 lp_customer_number := ' ';
29 END IF;
30 IF P_ORDER_NUMBER IS NOT NULL THEN
31 LP_ORDER_NUMBER := ' and h.order_number = :p_order_number';
32 END IF;
33 IF (lp_order_number IS NULL) THEN
34 lp_order_number := ' ';
35 END IF;
36 IF P_CURRENCY_CODE IS NOT NULL THEN
37 LP_CURRENCY_CODE := ' and h.transactional_curr_code = :p_currency_code';
38 END IF;
39 IF (lp_currency_code IS NULL) THEN
40 lp_currency_code := ' ';
41 END IF;
42 IF (P_DATE_HOLD_APPLIED_LOW IS NOT NULL) AND (P_DATE_HOLD_APPLIED_HIGH IS NOT NULL) THEN
43 LP_DATE_HOLD_APPLIED := 'and (trunc(oh.creation_date) between :p_date_hold_applied_low
44 and :p_date_hold_applied_high) ';
45 ELSIF (P_DATE_HOLD_APPLIED_LOW IS NOT NULL) THEN
46 LP_DATE_HOLD_APPLIED := 'and trunc(oh.creation_date) >= :p_date_hold_applied_low ';
47 ELSIF (P_DATE_HOLD_APPLIED_HIGH IS NOT NULL) THEN
48 LP_DATE_HOLD_APPLIED := 'and trunc(oh.creation_date) <= :p_date_hold_applied_high ';
49 END IF;
50 IF (lp_date_hold_applied IS NULL) THEN
51 lp_date_hold_applied := ' ';
52 END IF;
53 IF P_ORDER_TYPE IS NOT NULL THEN
54 LP_ORDER_TYPE := ' and ot.transaction_type_id = :p_order_type';
55 SELECT
56 OEOT.NAME
57 INTO L_ORDER_TYPE
58 FROM
59 OE_TRANSACTION_TYPES_TL OEOT
60 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE
61 AND OEOT.LANGUAGE = USERENV('LANG');
62 END IF;
63 IF (lp_order_type IS NULL) THEN
64 lp_order_type := ' ';
65 END IF;
66 IF P_LINE_TYPE IS NOT NULL THEN
67 LP_LINE_TYPE := ' and lt.line_type_id = :p_line_type';
68 SELECT
69 OEOT.NAME
70 INTO L_LINE_TYPE
71 FROM
72 OE_TRANSACTION_TYPES_TL OEOT
73 WHERE OEOT.TRANSACTION_TYPE_ID = P_LINE_TYPE
74 AND OEOT.LANGUAGE = USERENV('LANG');
75 END IF;
76 IF (lp_line_type IS NULL) THEN
77 lp_line_type := ' ';
78 END IF;
79 END;
80 RETURN (TRUE);
81 END AFTERPFORM;
82 FUNCTION C_DATA_NOT_FOUNDFORMULA(CUSTOMER_NAME IN VARCHAR2) RETURN NUMBER IS
83 BEGIN
84 RP_DATA_FOUND := CUSTOMER_NAME;
85 RETURN (0);
86 END C_DATA_NOT_FOUNDFORMULA;
87 FUNCTION C_ADDRESSFORMULA(ADDRESS1 IN VARCHAR2
88 ,CITY IN VARCHAR2
89 ,STATE IN VARCHAR2) RETURN VARCHAR2 IS
90 BEGIN
91 /*SRW.REFERENCE(ADDRESS1)*/NULL;
92 /*SRW.REFERENCE(CITY)*/NULL;
93 /*SRW.REFERENCE(STATE)*/NULL;
94 IF ADDRESS1 IS NOT NULL THEN
95 RETURN (ADDRESS1 || ' , ' || CITY || ' , ' || STATE);
96 ELSE
97 RETURN (NULL);
98 END IF;
99 RETURN NULL;
100 END C_ADDRESSFORMULA;
101 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
102 BEGIN
103 RETURN (TRUE);
104 END P_ITEM_FLEX_CODEVALIDTRIGGER;
105 FUNCTION INVENTORY_ITEM_ID_P RETURN NUMBER IS
106 BEGIN
107 RETURN INVENTORY_ITEM_ID;
108 END INVENTORY_ITEM_ID_P;
109 FUNCTION ORDERED_ITEM_P RETURN VARCHAR2 IS
110 BEGIN
111 RETURN ORDERED_ITEM;
112 END ORDERED_ITEM_P;
113 FUNCTION ORDERED_ITEM_ID_P RETURN NUMBER IS
114 BEGIN
115 RETURN ORDERED_ITEM_ID;
116 END ORDERED_ITEM_ID_P;
117 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
118 BEGIN
119 RETURN RP_REPORT_NAME;
120 END RP_REPORT_NAME_P;
121 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
122 BEGIN
123 RETURN RP_SUB_TITLE;
124 END RP_SUB_TITLE_P;
125 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
126 BEGIN
127 RETURN RP_COMPANY_NAME;
128 END RP_COMPANY_NAME_P;
129 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
130 BEGIN
131 RETURN RP_FUNCTIONAL_CURRENCY;
132 END RP_FUNCTIONAL_CURRENCY_P;
133 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
134 BEGIN
135 RETURN RP_DATA_FOUND;
136 END RP_DATA_FOUND_P;
137 FUNCTION RP_DATE_HOLD_APPLIED_RANGE_P RETURN VARCHAR2 IS
138 BEGIN
139 RETURN RP_DATE_HOLD_APPLIED_RANGE;
140 END RP_DATE_HOLD_APPLIED_RANGE_P;
141 FUNCTION RP_SHIP_P RETURN VARCHAR2 IS
142 BEGIN
143 RETURN RP_SHIP;
144 END RP_SHIP_P;
145 FUNCTION RP_ORDER_P RETURN VARCHAR2 IS
146 BEGIN
147 RETURN RP_ORDER;
148 END RP_ORDER_P;
149 FUNCTION RP_VAT_PROFILE_P RETURN VARCHAR2 IS
150 BEGIN
151 RETURN RP_VAT_PROFILE;
152 END RP_VAT_PROFILE_P;
153 FUNCTION RP_PRINT_DESCRIPTION_P RETURN VARCHAR2 IS
154 BEGIN
155 RETURN RP_PRINT_DESCRIPTION;
156 END RP_PRINT_DESCRIPTION_P;
157 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
158 BEGIN
159 RETURN RP_ITEM_FLEX_ALL_SEG;
160 END RP_ITEM_FLEX_ALL_SEG_P;
161 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
162 BEGIN
163 RETURN RP_DUMMY_ITEM;
164 END RP_DUMMY_ITEM_P;
165 function BeforeReport return boolean is
166 begin
167 DECLARE
168 BEGIN
169 /*BEGIN
170 SRW.USER_EXIT('FND SRWINIT');
171 EXCEPTION
172 WHEN SRW.USER_EXIT_FAILURE THEN
173 SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger');
174 return (FALSE);
175 NULL;
176 END;*/
177 BEGIN /*MOAC*/
178 P_ORG_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
179 LP_ORG_ID:=P_ORG_ID;
180 END;
181 /*------------------------------------------------------------------------------
182 Following PL/SQL block gets the company name, functional currency and precision.
183 ------------------------------------------------------------------------------*/
184 DECLARE
185 l_company_name VARCHAR2 (100);
186 l_functional_currency VARCHAR2 (15);
187 BEGIN
188 SELECT sob.name ,
189 sob.currency_code
190 INTO
191 l_company_name ,
192 l_functional_currency
193 FROM gl_sets_of_books sob,
194 fnd_currencies cur
195 WHERE sob.set_of_books_id = p_sob_id
196 AND sob.currency_code = cur.currency_code
197 ;
198 rp_company_name := l_company_name;
199 rp_functional_currency := l_functional_currency ;
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 NULL ;
203 END ;
204 /*------------------------------------------------------------------------------
205 The following block retrieves the profile option value for VAT
206 -------------------------------------------------------------------------------*/
207 BEGIN
208 /*SRW.REFERENCE(:P_VAT_PROFILE);
209 SRW.USER_EXIT('FND GETPROFILE NAME=":P_VAT_PROFILE"
210 FIELD=":RP_VAT_PROFILE"
211 PRINT_ERROR="N"');
212 EXCEPTION
213 WHEN SRW.USER_EXIT_FAILURE THEN
214 srw.message(2000,'Failed in BEFORE REPORT trigger. FND GETPROFILE - VAT USER_EXIT'); */
215 NULL;
216 END;
217 /*------------------------------------------------------------------------------
218 Following PL/SQL block gets the report name for the passed concurrent request Id.
219 ------------------------------------------------------------------------------*/
220 DECLARE
221 l_report_name VARCHAR2(240);
222 BEGIN
223 SELECT cp.user_concurrent_program_name
224 INTO l_report_name
225 FROM FND_CONCURRENT_PROGRAMS_VL cp,
226 FND_CONCURRENT_REQUESTS cr
227 WHERE cr.request_id = P_CONC_REQUEST_ID
228 AND cp.application_id = cr.program_application_id
229 AND cp.concurrent_program_id = cr.concurrent_program_id
230 ;
231 RP_Report_Name := l_report_name;
232 EXCEPTION
233 WHEN NO_DATA_FOUND
234 THEN RP_REPORT_NAME := 'Lines on Credit Check Hold Report';
235 END;
236 /*------------------------------------------------------------------------------
237 Following PL/SQL block builds up the lexical parameters, to be used in the
238 WHERE clause of the query. This also populates the report level variables, used
239 to store the flexfield structure.
240 ------------------------------------------------------------------------------*/
241 BEGIN
242 /*SRW.REFERENCE(:P_item_flex_code);
243 SRW.REFERENCE(:P_ITEM_STRUCTURE_NUM);
244 SRW.USER_EXIT('FND FLEXSQL CODE=":p_item_flex_code"
245 NUM=":p_item_structure_num"
246 APPL_SHORT_NAME="INV"
247 OUTPUT=":rp_item_flex_all_seg"
248 MODE="SELECT"
249 DISPLAY="ALL"
250 TABLEALIAS="SI"
251 ');
252 EXCEPTION
253 WHEN SRW.USER_EXIT_FAILURE THEN
254 srw.message(2000,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT'); */
255 NULL;
256 END;
257 /*------------------------------------------------------------------------------
258 Following PL/SQL fetches the Master Organization Id for the session.
259 Used in the WHERE clause of the query.
260 ------------------------------------------------------------------------------*/
261 BEGIN
262 RP_DUMMY_ITEM := NVL( OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0 );
263 EXCEPTION
264 WHEN OTHERS THEN
265 /*SRW.MESSAGE(1000,'Error in fetching Master Organization Id for the session');*/
266 RP_DUMMY_ITEM := 0;
267 END;
268 DECLARE
269 l_date_hold_applied_low VARCHAR2 (50);
270 l_date_hold_applied_high VARCHAR2 (50);
271 BEGIN
272 if ( p_date_hold_applied_low is NULL) AND ( p_date_hold_applied_high is NULL ) then
273 NULL ;
274 else
275 if p_date_hold_applied_low is NULL then
276 l_date_hold_applied_low := ' ';
277 else
278 l_date_hold_applied_low := substr(to_char(p_date_hold_applied_low,'DD-MON-YYYY'),1,18);
279 end if ;
280 if p_date_hold_applied_high is NULL then
281 l_date_hold_applied_high := ' ';
282 else
283 l_date_hold_applied_high := substr(to_char(p_date_hold_applied_high,'DD-MON-YYYY'),1,18);
284 end if ;
285 rp_date_hold_applied_range := 'From '||l_date_hold_applied_low||' To '||l_date_hold_applied_high ;
286 end if ;
287 DECLARE
288 l_meaning VARCHAR2 (80);
289 BEGIN
290 SELECT MEANING
291 INTO l_meaning
292 FROM OE_LOOKUPS
293 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
294 AND LOOKUP_CODE = substr(upper(p_item_description),1,1)
295 ;
296 rp_print_description := l_meaning ;
297 EXCEPTION WHEN NO_DATA_FOUND THEN
298 rp_print_description := 'Description';
299 END ;
300 END ;
301 END ;
302 return (TRUE);
303 end beforereport;
304
305 FUNCTION ITEM_DSPFORMULA(ITEM_IDENTIFIER_TYPE IN VARCHAR2,ORDERED_ITEM_ID IN NUMBER,ORDERED_ITEM IN VARCHAR2,ORGANIZATION_ID IN NUMBER,INVENTORY_ITEM_ID1 IN NUMBER) RETURN VARCHAR2 IS
306
307 v_item varchar2(2000);
308 v_description varchar2(500);
309 begin
310 if (item_identifier_type is null or item_identifier_type = 'INT')
311 or (p_item_description in ('I','D','F')) then
312
313 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code, p_item_structure_num, Item_DspFormula.ORGANIZATION_ID, Item_DspFormula.INVENTORY_ITEM_ID1, 'ALL', 'Y', 'VALUE');
314
315 select sitems.description description
316 into v_description
317 from mtl_system_items_vl sitems
318 where sitems.customer_order_enabled_flag = 'Y'
319 and sitems.bom_item_type in (1,4)
320 and nvl(sitems.organization_id,0) = RP_DUMMY_ITEM
321 and sitems.inventory_item_id = Item_DspFormula.inventory_item_id1; --Bug2764262
322
323 /* srw.reference (:item_flex);
324 srw.reference (:p_item_flex_code);
325 srw.reference (:Item_dsp);
326 srw.reference (:p_item_structure_num);
327 srw.user_exit (' FND FLEXIDVAL
328 CODE=":p_item_flex_code"
329 NUM=":p_item_structure_num"
330 APPL_SHORT_NAME="INV"
331 DATA= ":item_flex"
332 VALUE=":Item_dsp"
333 DISPLAY="ALL"'
334 );*/
335 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code, p_item_structure_num, Item_DspFormula.ORGANIZATION_ID, Item_DspFormula.INVENTORY_ITEM_ID1, 'ALL', 'Y', 'VALUE');
336 elsif (item_identifier_type = 'CUST' and p_item_description in ('C','P','O')) then
337 select citems.customer_item_number item,
338 nvl(citems.customer_item_desc,sitems.description) description
339 into v_item,v_description
340 from mtl_customer_items citems,
341 mtl_customer_item_xrefs cxref,
342 mtl_system_items_vl sitems
343 where citems.customer_item_id = cxref.customer_item_id
344 and cxref.inventory_item_id = sitems.inventory_item_id
345 and citems.customer_item_id = Item_DspFormula.ordered_item_id
346 and nvl(sitems.organization_id,0) = RP_DUMMY_ITEM
347 and sitems.inventory_item_id = Item_DspFormula.inventory_item_id1;
348 -- and sitems.customer_order_enabled_flag = 'Y'
349 -- and sitems.bom_item_type in (1,4)
350 elsif (p_item_description in ('C','P','O')) then
351 select items.cross_reference item,
352 nvl(items.description,sitems.description) description
353 into v_item,v_description
354 from mtl_cross_reference_types xtypes,
355 mtl_cross_references items,
356 mtl_system_items_vl sitems
357 where xtypes.cross_reference_type = items.cross_reference_type
358 and items.inventory_item_id = sitems.inventory_item_id
359 and items.cross_reference = Item_DspFormula.ordered_item
360 and items.cross_reference_type = Item_DspFormula.item_identifier_type
361 and nvl(sitems.organization_id,0) = RP_DUMMY_ITEM
362 and sitems.inventory_item_id = Item_DspFormula.inventory_item_id1;
363 -- and sitems.customer_order_enabled_flag = 'Y'
364 -- and sitems.bom_item_type in (1,4)
365 end if;
366
367 if (p_item_description in ('I','C')) then
368 return(v_item||' - '||v_description);
369 elsif (p_item_description in ('D','P')) then
370 return(v_description);
371 else
372 return(v_item);
373 end if;
374
375 RETURN NULL;
376 Exception
377 When Others Then
378 return('Item Not Found');
379 end;
380
381 END ONT_OEXOECCL_XMLP_PKG;
382
[Home] [Help]