1 PACKAGE BODY ONT_OEXCRDIS_XMLP_PKG AS
2 /* $Header: OEXCRDISB.pls 120.4 2008/06/05 11:54:15 dwkrishn noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 IF P_ORDER_BY = 'CUSTOMER' THEN
7 LP_ORDER_BY := 'order by oe_customer_name, oe_number, line_shipment_option_number ';
8 ELSIF P_ORDER_BY = 'ORDER_TYPE' THEN
9 LP_ORDER_BY := 'order by oe_order_type, oe_line_type, oe_customer_name, oe_number, line_shipment_option_number ';
10 ELSIF P_ORDER_BY = 'ORDER_NUM' THEN
11 LP_ORDER_BY := 'order by oe_number, line_shipment_option_number ';
12 ELSE
13 LP_ORDER_BY := 'order by oe_number ';
14 END IF;
15 IF P_ORDER_NUM_LOW IS NOT NULL THEN
16 LP_ORDER_NUM_LOW := 'and oeh.order_number >= :p_order_num_low ';
17 ELSE
18 LP_ORDER_NUM_LOW := ' ';
19 END IF;
20 IF P_ORDER_NUM_HIGH IS NOT NULL THEN
21 LP_ORDER_NUM_HIGH := 'and oeh.order_number <= :p_order_num_high ';
22 ELSE
23 LP_ORDER_NUM_HIGH := ' ';
24 END IF;
25 IF P_ORDER_DATE_LOW IS NOT NULL THEN
26 LP_ORDER_DATE_LOW := 'and oeh.ordered_date >= :p_order_date_low ';
27 ELSE
28 LP_ORDER_DATE_LOW := ' ';
29 END IF;
30 IF P_ORDER_DATE_HIGH IS NOT NULL THEN
31 LP_ORDER_DATE_HIGH := 'and oeh.ordered_date <= :p_order_date_high ';
32 ELSE
33 LP_ORDER_DATE_HIGH := ' ';
34 END IF;
35 IF P_RECEIPT_DATE_LOW IS NOT NULL THEN
36 LP_RECEIPT_DATE_LOW := 'and om_reports_common_pkg.oexoeors_get_workflow_date(oel.line_id) >= :p_receipt_date_low ';
37 ELSE
38 LP_RECEIPT_DATE_LOW := ' ';
39 END IF;
40 IF P_RECEIPT_DATE_HIGH IS NOT NULL THEN
41 LP_RECEIPT_DATE_HIGH := 'and om_reports_common_pkg.oexoeors_get_workflow_date(oel.line_id) <= :p_receipt_date_high ';
42 ELSE
43 LP_RECEIPT_DATE_HIGH := ' ';
44 END IF;
45 IF P_CUSTOMER_NAME_LOW IS NOT NULL THEN
46 LP_CUSTOMER_NAME_LOW := 'and c.name >= :p_customer_name_low ';
47 ELSE
48 LP_CUSTOMER_NAME_LOW := ' ';
49 END IF;
50 IF P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
51 LP_CUSTOMER_NAME_HIGH := 'and c.name <= :p_customer_name_high ';
52 ELSE
53 LP_CUSTOMER_NAME_HIGH := ' ';
54 END IF;
55 IF P_CUSTOMER_NUMBER_LOW IS NOT NULL THEN
56 LP_CUSTOMER_NUMBER_LOW := 'and c.customer_number >= ''' || P_CUSTOMER_NUMBER_LOW || '''';
57 ELSE
58 LP_CUSTOMER_NUMBER_LOW := ' ';
59 END IF;
60 IF P_CUSTOMER_NUMBER_HIGH IS NOT NULL THEN
61 LP_CUSTOMER_NUMBER_HIGH := ' and c.customer_number <= ''' || P_CUSTOMER_NUMBER_HIGH || '''';
62 ELSE
63 LP_CUSTOMER_NUMBER_HIGH := ' ';
64 END IF;
65 /*SRW.MESSAGE(1
66 ,'Error -1 ')*/NULL;
67 IF P_ORDER_TYPE_LOW IS NOT NULL THEN
68 LP_ORDER_TYPE_LOW := 'and oeot.transaction_type_id >= ' || P_ORDER_TYPE_LOW;
69 SELECT
70 OEOT.NAME
71 INTO L_ORDER_TYPE_LOW
72 FROM
73 OE_TRANSACTION_TYPES_TL OEOT
74 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
75 AND OEOT.LANGUAGE = USERENV('LANG');
76 ELSE
77 LP_ORDER_TYPE_LOW := ' ';
78 END IF;
79 IF P_ORDER_TYPE_HIGH IS NOT NULL THEN
80 LP_ORDER_TYPE_HIGH := 'and oeot.transaction_type_id <=' || P_ORDER_TYPE_HIGH;
81 SELECT
82 OEOT.NAME
83 INTO L_ORDER_TYPE_HIGH
84 FROM
85 OE_TRANSACTION_TYPES_TL OEOT
86 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
87 AND OEOT.LANGUAGE = USERENV('LANG');
88 ELSE
89 LP_ORDER_TYPE_HIGH := ' ';
90 END IF;
91 /*SRW.MESSAGE(1
92 ,'l_order_type_low-->' || L_ORDER_TYPE_LOW)*/NULL;
93 END;
94 RETURN (TRUE);
95 END AFTERPFORM;
96
97 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
98 BEGIN
99 BEGIN
100 BEGIN
101 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
102 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
103 EXCEPTION
104 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
105 /*SRW.MESSAGE(1000
106 ,'Failed in BEFORE REPORT trigger')*/NULL;
107 RETURN (FALSE);
108 END;
109 BEGIN
110 P_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
111 END;
112 DECLARE
113 L_COMPANY_NAME VARCHAR2(30);
114 BEGIN
115 SELECT
116 NAME
117 INTO L_COMPANY_NAME
118 FROM
119 GL_SETS_OF_BOOKS
120 WHERE SET_OF_BOOKS_ID = P_SOB_ID;
121 RP_COMPANY_NAME := L_COMPANY_NAME;
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 NULL;
125 END;
126 DECLARE
127 L_REPORT_NAME VARCHAR2(240);
128 BEGIN
129 SELECT
130 CP.USER_CONCURRENT_PROGRAM_NAME
131 INTO L_REPORT_NAME
132 FROM
133 FND_CONCURRENT_PROGRAMS_VL CP,
134 FND_CONCURRENT_REQUESTS CR
135 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
136 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
137 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
138 RP_REPORT_NAME := L_REPORT_NAME;
139 EXCEPTION
140 WHEN NO_DATA_FOUND THEN
141 RP_REPORT_NAME := 'Credit Order Discrepancy Report';
142 END;
143 BEGIN
144 /*SRW.REFERENCE(P_ITEM_FLEX_CODE)*/NULL;
145 /*SRW.REFERENCE(P_ITEM_STRUCTURE_NUM)*/NULL;
146 /*SRW.REFERENCE(P_ITEM_LOW)*/NULL;
147 /*SRW.REFERENCE(P_ITEM_HIGH)*/NULL;
148 IF P_ITEM_LOW IS NOT NULL OR P_ITEM_HIGH IS NOT NULL THEN
149 LP_ITEM1 := ' and ' ;
150 END IF;
151 EXCEPTION
152 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
153 /*SRW.MESSAGE(2000
154 ,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT')*/NULL;
155 END;
156 END;
157 RETURN (TRUE);
158 END BEFOREREPORT;
159
160 FUNCTION C_BUILD_LBLFORMULA RETURN VARCHAR2 IS
161 BEGIN
162 DECLARE
163 L_DATE VARCHAR2(11);
164 H_DATE VARCHAR2(11);
165 LR_DATE VARCHAR2(11);
166 HR_DATE VARCHAR2(11);
167 BEGIN
168 IF P_ORDER_NUM_LOW IS NOT NULL OR P_ORDER_NUM_HIGH IS NOT NULL THEN
169 RP_ORDER_RANGE := 'From ' || NVL(P_ORDER_NUM_LOW
170 ,' ') || ' To ' || NVL(P_ORDER_NUM_HIGH
171 ,' ');
172 END IF;
173 IF P_CUSTOMER_NAME_LOW IS NOT NULL OR P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
174 RP_CUSTOMER_RANGE := 'From ' || NVL(P_CUSTOMER_NAME_LOW
175 ,' ') || ' To ' || NVL(P_CUSTOMER_NAME_HIGH
176 ,' ');
177 END IF;
178 IF P_CUSTOMER_NUMBER_LOW IS NOT NULL OR P_CUSTOMER_NUMBER_HIGH IS NOT NULL THEN
179 RP_CUSTOMER_NUMBER_RANGE := 'From ' || P_CUSTOMER_NUMBER_LOW || ' To ' || P_CUSTOMER_NUMBER_HIGH;
180 END IF;
181 L_DATE := ' ';
182 H_DATE := ' ';
183 IF P_ORDER_DATE_LOW IS NOT NULL THEN
184 L_DATE := TO_CHAR(P_ORDER_DATE_LOW);
185 END IF;
186 IF P_ORDER_DATE_HIGH IS NOT NULL THEN
187 H_DATE := TO_CHAR(P_ORDER_DATE_HIGH);
188 END IF;
189 IF P_ORDER_DATE_LOW IS NOT NULL OR P_ORDER_DATE_HIGH IS NOT NULL THEN
190 RP_ORDER_DATE_RANGE := 'From ' || L_DATE || ' To ' || H_DATE;
191 END IF;
192 IF P_ORDER_TYPE_LOW IS NOT NULL OR P_ORDER_TYPE_HIGH IS NOT NULL THEN
193 RP_ORDER_TYPE_RANGE := 'From ' || NVL(L_ORDER_TYPE_LOW
194 ,' ') || ' To ' || NVL(L_ORDER_TYPE_HIGH
195 ,' ');
196 END IF;
197 IF P_ITEM_LOW IS NOT NULL OR P_ITEM_HIGH IS NOT NULL THEN
198 RP_ITEM_RANGE := 'From ' || NVL(P_ITEM_LOW
199 ,' ') || ' To ' || NVL(P_ITEM_HIGH
200 ,' ');
201 END IF;
202 LR_DATE := ' ';
203 HR_DATE := ' ';
204 IF P_RECEIPT_DATE_LOW IS NOT NULL THEN
205 LR_DATE := TO_CHAR(P_RECEIPT_DATE_LOW);
206 END IF;
207 IF P_RECEIPT_DATE_HIGH IS NOT NULL THEN
208 HR_DATE := TO_CHAR(P_RECEIPT_DATE_HIGH);
209 END IF;
210 IF P_RECEIPT_DATE_LOW IS NOT NULL OR P_RECEIPT_DATE_HIGH IS NOT NULL THEN
211 RP_RECEIPT_DATE_RANGE := 'From ' || LR_DATE || ' To ' || HR_DATE;
212 END IF;
213 DECLARE
214 ITEM_DISPLAY_MEANING VARCHAR2(80);
215 BEGIN
216 SELECT
217 MEANING
218 INTO ITEM_DISPLAY_MEANING
219 FROM
220 OE_LOOKUPS
221 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
222 AND LOOKUP_CODE = P_PRINT_DESCRIPTION;
223 RP_ITEM_DISPLAY_METHOD := ITEM_DISPLAY_MEANING;
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 RP_ITEM_DISPLAY_METHOD := NULL;
227 END;
228 DECLARE
229 ORDER_BY VARCHAR2(80);
230 BEGIN
231 SELECT
232 MEANING
233 INTO ORDER_BY
234 FROM
235 OE_LOOKUPS
236 WHERE LOOKUP_TYPE = 'OEXCRDIS ORDER BY'
237 AND LOOKUP_CODE = P_ORDER_BY;
238 RP_ORDER_BY := ORDER_BY;
239 EXCEPTION
240 WHEN NO_DATA_FOUND THEN
241 RP_ORDER_BY := P_ORDER_BY;
242 END;
243 RETURN (1);
244 END;
245 RETURN NULL;
246 END C_BUILD_LBLFORMULA;
247
248 FUNCTION AFTERREPORT RETURN BOOLEAN IS
249 BEGIN
250 BEGIN
251 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
252 EXCEPTION
253 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
254 /*SRW.MESSAGE(1
255 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
256 RETURN (FALSE);
257 END;
258 RETURN (TRUE);
259 END AFTERREPORT;
260
261 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
262 BEGIN
263 RETURN RP_DUMMY_ITEM;
264 END RP_DUMMY_ITEM_P;
265
266 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
267 BEGIN
268 RETURN RP_REPORT_NAME;
269 END RP_REPORT_NAME_P;
270
271 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
272 BEGIN
273 RETURN RP_COMPANY_NAME;
274 END RP_COMPANY_NAME_P;
275
276 FUNCTION RP_ITEM_FLEX_P RETURN VARCHAR2 IS
277 BEGIN
278 RETURN RP_ITEM_FLEX;
279 END RP_ITEM_FLEX_P;
280
281 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
282 BEGIN
283 RETURN RP_SUB_TITLE;
284 END RP_SUB_TITLE_P;
285
286 FUNCTION RP_ORDER_RANGE_P RETURN VARCHAR2 IS
287 BEGIN
288 RETURN RP_ORDER_RANGE;
289 END RP_ORDER_RANGE_P;
290
291 FUNCTION RP_CUSTOMER_RANGE_P RETURN VARCHAR2 IS
292 BEGIN
293 RETURN RP_CUSTOMER_RANGE;
294 END RP_CUSTOMER_RANGE_P;
295
296 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
297 BEGIN
298 RETURN RP_ORDER_DATE_RANGE;
299 END RP_ORDER_DATE_RANGE_P;
300
301 FUNCTION RP_ITEM_DISPLAY_METHOD_P RETURN VARCHAR2 IS
302 BEGIN
303 RETURN RP_ITEM_DISPLAY_METHOD;
304 END RP_ITEM_DISPLAY_METHOD_P;
305
306 FUNCTION RP_ORDER_TYPE_RANGE_P RETURN VARCHAR2 IS
307 BEGIN
308 RETURN RP_ORDER_TYPE_RANGE;
309 END RP_ORDER_TYPE_RANGE_P;
310
311 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
312 BEGIN
313 RETURN RP_ORDER_BY;
314 END RP_ORDER_BY_P;
315
316 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
317 BEGIN
318 RETURN RP_ITEM_FLEX_ALL_SEG;
319 END RP_ITEM_FLEX_ALL_SEG_P;
320
321 FUNCTION RP_CUSTOMER_NUMBER_RANGE_P RETURN VARCHAR2 IS
322 BEGIN
323 RETURN RP_CUSTOMER_NUMBER_RANGE;
324 END RP_CUSTOMER_NUMBER_RANGE_P;
325
326 FUNCTION RP_ITEM_RANGE_P RETURN VARCHAR2 IS
327 BEGIN
328 RETURN RP_ITEM_RANGE;
329 END RP_ITEM_RANGE_P;
330
331 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
332 BEGIN
333 RETURN RP_DATA_FOUND;
334 END RP_DATA_FOUND_P;
335
336 FUNCTION RP_RECEIPT_DATE_RANGE_P RETURN VARCHAR2 IS
337 BEGIN
338 RETURN RP_RECEIPT_DATE_RANGE;
339 END RP_RECEIPT_DATE_RANGE_P;
340
341 FUNCTION ITEM_DSPFORMULA(item_identifier_type in varchar2,oe_inventory_item_id1 in number ,rp_dummy_item in varchar2,
342 item_dsp in varchar2, ORDERED_ITEM_ID in number, ordered_item in varchar2, ORGANIZATION_ID in number, INVENTORY_ITEM_ID in number)
343 RETURN VARCHAR2 IS
344 v_item varchar2(2000);
345 v_description varchar2(500);
346 begin
347
348 if (item_identifier_type is null or item_identifier_type = 'INT')
349 or (p_print_description in ('I','D','F')) then
350 select
351 -- sitems.concatenated_segments item,
352 sitems.description description
353 into
354 -- v_item,
355 v_description
356 from mtl_system_items_vl sitems
357 where
358 sitems.customer_order_enabled_flag = 'Y' and
359 sitems.bom_item_type in (1,4)
360 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
361 and sitems.inventory_item_id = oe_inventory_item_id1;
362 -- rp_dummy_item := v_item;
363
364 /* srw.reference (:ITEM_FLEX);
365 srw.reference (:p_item_flex_code);
366 srw.reference (:Item_dsp);
367 srw.reference (:p_item_structure_num);
368 srw.user_exit (' FND FLEXIDVAL
369 CODE=":p_item_flex_code"
370 NUM=":p_item_structure_num"
371 APPL_SHORT_NAME="INV"
372 DATA= ":ITEM_FLEX"
373 VALUE=":Item_dsp"
374 DISPLAY="ALL"'
375 );*/
376 -- rp_dummy_item := '';
377 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_ID, 'ALL', 'Y', 'VALUE');
378 elsif (item_identifier_type = 'CUST' and p_print_description in ('C','P','O')) then
379 select citems.customer_item_number item,
380 nvl(citems.customer_item_desc,sitems.description) description
381 into v_item,v_description
382 from mtl_customer_items citems,
383 mtl_customer_item_xrefs cxref,
384 mtl_system_items_vl sitems
385 where citems.customer_item_id = cxref.customer_item_id
386 and cxref.inventory_item_id = sitems.inventory_item_id
387 and citems.customer_item_id = ordered_item_id
388 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
389 and sitems.inventory_item_id = oe_inventory_item_id1;
390 -- and sitems.customer_order_enabled_flag = 'Y'
391 -- and sitems.bom_item_type in (1,4)
392 elsif (p_print_description in ('C','P','O')) then
393 select items.cross_reference item,
394 nvl(items.description,sitems.description) description
395 into v_item,v_description
396 from mtl_cross_reference_types xtypes,
397 mtl_cross_references items,
398 mtl_system_items_vl sitems
399 where xtypes.cross_reference_type = items.cross_reference_type
400 and items.inventory_item_id = sitems.inventory_item_id
401 and items.cross_reference = ordered_item
402 and items.cross_reference_type = item_identifier_type
403 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
404 and sitems.inventory_item_id = oe_inventory_item_id1;
405 -- and sitems.customer_order_enabled_flag = 'Y'
406 -- and sitems.bom_item_type in (1,4)
407 end if;
408
409 if (p_print_description in ('I','C')) then
410 return(v_item||' - '||v_description);
411 elsif (p_print_description in ('D','P')) then
412 return(v_description);
413 else
414 return(v_item);
415 end if;
416
417 RETURN NULL;
418 Exception
419 When Others Then
420 return('Item Not Found');
421 END ITEM_DSPFORMULA;
422
423
424
425 END ONT_OEXCRDIS_XMLP_PKG;
426
[Home] [Help]