1 PACKAGE BODY ONT_OEXOEORR_XMLP_PKG AS
2 /* $Header: OEXOEORRB.pls 120.3 2008/05/05 12:41:22 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 CP_ORDER_DATE_LOW :=to_char(P_ORDER_DATE_LOW,'DD-MON-YY');
9 CP_ORDER_DATE_HIGH :=to_char(P_ORDER_DATE_HIGH,'DD-MON-YY');
10 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
11 EXCEPTION
12 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
13 BEGIN
14 /*SRW.MESSAGE(1000
15 ,'Failed in BEFORE REPORT trigger')*/NULL;
16 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
17 END;
18 END;
19 BEGIN
20 P_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
21 END;
22 BEGIN
23 IF P_ITEM IS NOT NULL THEN
24 SELECT
25 CONCATENATED_SEGMENTS
26 INTO P_ITEM_NAME
27 FROM
28 MTL_SYSTEM_ITEMS_KFV
29 WHERE INVENTORY_ITEM_ID = P_ITEM
30 AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
31 AND BOM_ITEM_TYPE in ( 1 , 4 )
32 AND ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
33 END IF;
34 END;
35 DECLARE
36 L_COMPANY_NAME VARCHAR2(100);
37 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
38 BEGIN
39 SELECT
40 SOB.NAME,
41 SOB.CURRENCY_CODE
42 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
43 FROM
44 GL_SETS_OF_BOOKS SOB,
45 FND_CURRENCIES CUR
46 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
47 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
48 RP_COMPANY_NAME := L_COMPANY_NAME;
49 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 NULL;
53 END;
54 BEGIN
55 /*SRW.REFERENCE(P_ITEM_FLEX_CODE)*/NULL;
56 /*SRW.REFERENCE(P_ITEM_STRUCTURE_NUM)*/NULL;
57 EXCEPTION
58 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
59 /*SRW.MESSAGE(1
60 ,'Failed in before report trigger:MSTK')*/NULL;
61 END;
62 IF P_ITEM IS NOT NULL THEN
63 LP_ITEM_FLEX_ALL_SEG := ' and ' || RP_ITEM_FLEX_ALL_SEG_WHERE;
64 END IF;
65 DECLARE
66 L_REPORT_NAME VARCHAR2(240);
67 BEGIN
68 SELECT
69 CP.USER_CONCURRENT_PROGRAM_NAME
70 INTO L_REPORT_NAME
71 FROM
72 FND_CONCURRENT_PROGRAMS_VL CP,
73 FND_CONCURRENT_REQUESTS CR
74 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
75 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
76 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
77 l_report_name := substr(l_report_name,1,instr(l_report_name,' (XML)'));
78 RP_REPORT_NAME := L_REPORT_NAME;
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN
81 RP_REPORT_NAME := 'Returns by Reason';
82 END;
83 END;
84 RETURN (TRUE);
85 END BEFOREREPORT;
86
87 FUNCTION AFTERREPORT RETURN BOOLEAN IS
88 BEGIN
89 BEGIN
90 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
91 EXCEPTION
92 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
93 /*SRW.MESSAGE(1
94 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
95 RETURN (FALSE);
96 END;
97 RETURN (TRUE);
98 END AFTERREPORT;
99
100 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
101 BEGIN
102 RETURN (TRUE);
103 END P_ITEM_FLEX_CODEVALIDTRIGGER;
104
105 FUNCTION AFTERPFORM RETURN BOOLEAN IS
106 BEGIN
107 /*SRW.MESSAGE(99999
108 ,'$Header: ONT_OEXOEORR_XMLP_PKG.rdf 120.6 2005/11/09 05:27 maysriva ship
109 $')*/NULL;
110 BEGIN
111 IF P_ORDER_DATE_LOW IS NOT NULL AND P_ORDER_DATE_HIGH IS NOT NULL THEN
112 LP_ORDER_DATE := ' AND trunc(h.ordered_date) between trunc(:p_order_date_low) and trunc(:p_order_date_high) ';
113 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
114 LP_ORDER_DATE := ' and trunc(h.ordered_date) >= trunc(:p_order_date_low)';
115 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
116 LP_ORDER_DATE := ' and trunc(h.ordered_date) <= trunc(:p_order_date_high)';
117 END IF;
118 IF P_RETURN_REASON IS NOT NULL THEN
119 LP_RETURN_REASON := ' and l.return_reason_code = :p_return_reason ';
120 END IF;
121 IF P_CREDIT_ORDER_TYPE IS NOT NULL THEN
122 LP_CREDIT_ORDER_TYPE := ' and otype.transaction_type_id =' || P_CREDIT_ORDER_TYPE;
123 SELECT
124 OEOT.NAME
125 INTO L_ORDER_TYPE
126 FROM
127 OE_TRANSACTION_TYPES_TL OEOT
128 WHERE OEOT.TRANSACTION_TYPE_ID = P_CREDIT_ORDER_TYPE
129 AND OEOT.LANGUAGE = USERENV('LANG');
130 END IF;
131 IF P_CREDIT_ORDER_LINE_TYPE IS NOT NULL THEN
132 LP_CREDIT_ORDER_LINE_TYPE := ' and ltype.transaction_type_id =' || P_CREDIT_ORDER_LINE_TYPE;
133 SELECT
134 OEOT.NAME
135 INTO L_LINE_TYPE
136 FROM
137 OE_TRANSACTION_TYPES_TL OEOT
138 WHERE OEOT.TRANSACTION_TYPE_ID = P_CREDIT_ORDER_LINE_TYPE
139 AND OEOT.LANGUAGE = USERENV('LANG');
140 END IF;
141 END;
142 RETURN (TRUE);
143 END AFTERPFORM;
144
145 FUNCTION C_SET_LBLFORMULA RETURN VARCHAR2 IS
146 BEGIN
147 BEGIN
148 DECLARE
149 L_ORDER_DATE_LOW VARCHAR2(50);
150 L_ORDER_DATE_HIGH VARCHAR2(50);
151 BEGIN
152 IF (P_ORDER_DATE_LOW IS NULL) AND (P_ORDER_DATE_HIGH IS NULL) THEN
153 NULL;
154 ELSE
155 IF P_ORDER_DATE_LOW IS NULL THEN
156 L_ORDER_DATE_LOW := ' ';
157 ELSE
158 L_ORDER_DATE_LOW := TO_CHAR(P_ORDER_DATE_LOW
159 ,'DD-MON-RRRR');
160 END IF;
161 IF P_ORDER_DATE_HIGH IS NULL THEN
162 L_ORDER_DATE_HIGH := ' ';
163 ELSE
164 L_ORDER_DATE_HIGH := TO_CHAR(P_ORDER_DATE_HIGH
165 ,'DD-MON-RRRR');
166 END IF;
167 RP_ORDER_DATE_RANGE := 'From ' || L_ORDER_DATE_LOW || ' To ' || L_ORDER_DATE_HIGH;
168 END IF;
169 END;
170 IF P_RETURN_REASON IS NOT NULL THEN
171 DECLARE
172 MEANING VARCHAR2(80);
173 BEGIN
174 SELECT
175 MEANING
176 INTO MEANING
177 FROM
178 AR_LOOKUPS
179 WHERE LOOKUP_TYPE = 'CREDIT_MEMO_REASON'
180 AND LOOKUP_CODE = P_RETURN_REASON;
181 RP_RETURN_REASON := MEANING;
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 RP_RETURN_REASON := P_RETURN_REASON;
185 END;
186 END IF;
187 IF P_CREDIT_ORDER_TYPE IS NOT NULL THEN
188 RP_CREDIT_ORDER_TYPE := L_ORDER_TYPE;
189 END IF;
190 IF P_CREDIT_ORDER_LINE_TYPE IS NOT NULL THEN
191 RP_CREDIT_LINE_TYPE := L_LINE_TYPE;
192 END IF;
193 DECLARE
194 ITEM_DISPLAY_MEANING VARCHAR2(80);
195 BEGIN
196 SELECT
197 MEANING
198 INTO ITEM_DISPLAY_MEANING
199 FROM
200 OE_LOOKUPS
201 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
202 AND LOOKUP_CODE = P_PRINT_DESCRIPTION;
203 RP_FLEX_OR_DESC := ITEM_DISPLAY_MEANING;
204 END;
205 RETURN (1);
206 END;
207 RETURN NULL;
208 END C_SET_LBLFORMULA;
209
210 FUNCTION C_VALUEFORMULA(UNIT_SELLING_PRICE IN NUMBER
211 ,FULFILLED_QTY IN NUMBER) RETURN NUMBER IS
212 BEGIN
213 /*SRW.REFERENCE(UNIT_SELLING_PRICE)*/NULL;
214 /*SRW.REFERENCE(FULFILLED_QTY)*/NULL;
215 RP_DATA_FOUND := 'X';
216 RETURN (NVL(FULFILLED_QTY
217 ,0) * NVL(UNIT_SELLING_PRICE
218 ,0));
219 END C_VALUEFORMULA;
220
221 FUNCTION C_MASTER_ORGFORMULA RETURN CHAR IS
222 V_MASTER_ORG VARCHAR2(20);
223 BEGIN
224 V_MASTER_ORG := NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
225 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
226 ,0);
227 RETURN V_MASTER_ORG;
228 END C_MASTER_ORGFORMULA;
229
230 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
231 BEGIN
232 RETURN RP_REPORT_NAME;
233 END RP_REPORT_NAME_P;
234
235 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
236 BEGIN
237 RETURN RP_SUB_TITLE;
238 END RP_SUB_TITLE_P;
239
240 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
241 BEGIN
242 RETURN RP_COMPANY_NAME;
243 END RP_COMPANY_NAME_P;
244
245 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
246 BEGIN
247 RETURN RP_FUNCTIONAL_CURRENCY;
248 END RP_FUNCTIONAL_CURRENCY_P;
249
250 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
251 BEGIN
252 RETURN RP_DATA_FOUND;
253 END RP_DATA_FOUND_P;
254
255 FUNCTION RP_ITEM_FLEX_LPROMPT_P RETURN VARCHAR2 IS
256 BEGIN
257 RETURN RP_ITEM_FLEX_LPROMPT;
258 END RP_ITEM_FLEX_LPROMPT_P;
259
260 FUNCTION RP_ITEM_FLEX_CODE_P RETURN VARCHAR2 IS
261 BEGIN
262 RETURN RP_ITEM_FLEX_CODE;
263 END RP_ITEM_FLEX_CODE_P;
264
265 FUNCTION RP_ITEM_FLEX_APROMPT_P RETURN VARCHAR2 IS
266 BEGIN
267 RETURN RP_ITEM_FLEX_APROMPT;
268 END RP_ITEM_FLEX_APROMPT_P;
269
270 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
271 BEGIN
272 RETURN RP_ORDER_DATE_RANGE;
273 END RP_ORDER_DATE_RANGE_P;
274
275 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
276 BEGIN
277 RETURN RP_ORDER_BY;
278 END RP_ORDER_BY_P;
279
280 FUNCTION RP_RETURN_REASON_P RETURN VARCHAR2 IS
281 BEGIN
282 RETURN RP_RETURN_REASON;
283 END RP_RETURN_REASON_P;
284
285 FUNCTION RP_FLEX_OR_DESC_P RETURN VARCHAR2 IS
286 BEGIN
287 RETURN RP_FLEX_OR_DESC;
288 END RP_FLEX_OR_DESC_P;
289
290 FUNCTION RP_ITEM_FLEX_ALL_SEG_WHERE_P RETURN VARCHAR2 IS
291 BEGIN
292 RETURN RP_ITEM_FLEX_ALL_SEG_WHERE;
293 END RP_ITEM_FLEX_ALL_SEG_WHERE_P;
294
295 FUNCTION RP_ITEM_ORDER_BY_P RETURN VARCHAR2 IS
296 BEGIN
297 RETURN RP_ITEM_ORDER_BY;
298 END RP_ITEM_ORDER_BY_P;
299
300 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
301 BEGIN
302 RETURN RP_ITEM_FLEX_ALL_SEG;
303 END RP_ITEM_FLEX_ALL_SEG_P;
304
305 FUNCTION RP_CREDIT_ORDER_TYPE_P RETURN VARCHAR2 IS
306 BEGIN
307 RETURN RP_CREDIT_ORDER_TYPE;
308 END RP_CREDIT_ORDER_TYPE_P;
309
310 FUNCTION RP_CREDIT_LINE_TYPE_P RETURN VARCHAR2 IS
311 BEGIN
312 RETURN RP_CREDIT_LINE_TYPE;
313 END RP_CREDIT_LINE_TYPE_P;
314
315 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
316 BEGIN
317 RETURN RP_DUMMY_ITEM;
318 END RP_DUMMY_ITEM_P;
319
320 FUNCTION ITEM_DSPFORMULA(ITEM_IDENTIFIER_TYPE IN VARCHAR2,INVENTORY_ITEM_ID1 IN NUMBER,ORDERED_ITEM_ID IN NUMBER,ORDERED_ITEM IN VARCHAR2,C_ORGANIZATION_ID IN VARCHAR2,C_INVENTORY_ITEM_ID IN VARCHAR2) return Char is
321 v_item varchar2(2000);
322 v_description varchar2(500);
323 begin
324 if (item_identifier_type is null or item_identifier_type = 'INT')
325 or (p_print_description in ('I','D','F')) then
326 select sitems.concatenated_segments item,
327 sitems.description description
328 into v_item,v_description
329 from mtl_system_items_vl sitems
330 -- where sitems.customer_order_enabled_flag = 'Y'
331 -- and sitems.bom_item_type in (1,4)
332 where nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID) ,0)
333 and sitems.inventory_item_id = inventory_item_id1;
334
335 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code,p_item_structure_num, C_ORGANIZATION_ID, C_INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ;
336 elsif (item_identifier_type = 'CUST' and p_print_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 = ordered_item_id
346 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID) ,0)
347 and sitems.inventory_item_id = inventory_item_id1;
348 -- and sitems.customer_order_enabled_flag = 'Y'
349 -- and sitems.bom_item_type in (1,4)
350 elsif (p_print_description in ('C','P','O')) then
351 Begin
352 select items.cross_reference item,
353 nvl(items.description,sitems.description) description
354 into v_item,v_description
355 from mtl_cross_reference_types xtypes,
356 mtl_cross_references items,
357 mtl_system_items_vl sitems
358 where xtypes.cross_reference_type = items.cross_reference_type
359 and items.inventory_item_id = sitems.inventory_item_id
360 and items.cross_reference = ordered_item
361 and items.cross_reference_type = item_identifier_type
362 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID) ,0)
363 and sitems.inventory_item_id = inventory_item_id1
364 --Bug 3433353 Begin
365 and items.org_independent_flag = 'N'
366 and items.organization_id = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID) ,0);
367 -- and sitems.customer_order_enabled_flag = 'Y'
368 -- and sitems.bom_item_type in (1,4)
369 Exception When NO_DATA_FOUND Then
370 Select items.cross_reference item, nvl(items.description,sitems.description) description
371 into v_item,v_description
372 from mtl_cross_reference_types xtypes,
373 mtl_cross_references items,
374 mtl_system_items_vl sitems
375 where xtypes.cross_reference_type =
376 items.cross_reference_type
377 and items.inventory_item_id = sitems.inventory_item_id
378 and items.cross_reference = ordered_item
379 and items.cross_reference_type = item_identifier_type
380 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID) ,0)
381 and sitems.inventory_item_id = inventory_item_id1
382 and items.org_independent_flag = 'Y';
383 End;
384 end if;
385
386 if (p_print_description in ('I','C')) then
387 return(v_item||' - '||v_description);
388 elsif (p_print_description in ('D','P')) then
389 return(v_description);
390 else
391 return(v_item);
392 end if;
393
394 RETURN NULL;
395 Exception
396 When Others Then
397 return('Item Not Found');
398 end;
399
400
401 END ONT_OEXOEORR_XMLP_PKG;
402
[Home] [Help]