1 PACKAGE BODY ONT_OEXOHOHS_XMLP_PKG AS
2 /* $Header: OEXOHOHSB.pls 120.2 2008/05/05 06:39:36 dwkrishn noship $ */
3 function Item_dspFormula(item_identifier_type_L varchar2,
4 c_master_org_L varchar2 ,
5 inventory_item_id_L number,
6 ordered_item_id_L number,
7 ordered_item_L varchar2,
8 ORGANIZATION_ID_L number) return Char is
9 v_item varchar2(2000);
10 v_description varchar2(500);
11 begin
12
13 if (item_identifier_type_L is null or item_identifier_type_L = 'INT')
14 or (p_flex_or_desc in ('I','D','F')) then
15 select sitems.description description
16 into v_description
17 from mtl_system_items_vl sitems
18 Where nvl(sitems.organization_id,0) = c_master_org_L
19 and sitems.inventory_item_id = inventory_item_id_L;
20 /* srw.reference (:item_flex);
21 srw.reference (:p_item_flex_code);
22 srw.reference (:Item_dsp);
23 srw.reference (:p_item_structure_num);
24 srw.user_exit (' FND FLEXIDVAL
25 CODE=":p_item_flex_code"
26 NUM=":p_item_structure_num"
27 APPL_SHORT_NAME="INV"
28 DATA= ":item_flex"
29 VALUE=":Item_dsp"
30 DISPLAY="ALL"'
31 ); */
32 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV',p_item_flex_code,p_item_structure_num,ORGANIZATION_ID_L,INVENTORY_ITEM_ID_L, 'ALL', 'Y', 'VALUE');
33 elsif (item_identifier_type_L = 'CUST' and p_flex_or_desc in ('C','P','O')) then
34 select citems.customer_item_number item,
35 nvl(citems.customer_item_desc,sitems.description) description
36 into v_item,v_description
37 from mtl_customer_items citems,
38 mtl_customer_item_xrefs cxref,
39 mtl_system_items_vl sitems
40 where citems.customer_item_id = cxref.customer_item_id
41 and cxref.inventory_item_id = sitems.inventory_item_id
42 and citems.customer_item_id = ordered_item_id_L
43 and nvl(sitems.organization_id,0) = c_master_org_L
44 and sitems.inventory_item_id = inventory_item_id_L;
45 -- and sitems.customer_order_enabled_flag = 'Y'
46 -- and sitems.bom_item_type in (1,4)
47 elsif (p_flex_or_desc in ('C','P','O')) then
48 Begin
49 select items.cross_reference item,
50 nvl(items.description,sitems.description) description
51 into v_item,v_description
52 from mtl_cross_reference_types xtypes,
53 mtl_cross_references items,
54 mtl_system_items_vl sitems
55 where xtypes.cross_reference_type = items.cross_reference_type
56 and items.inventory_item_id = sitems.inventory_item_id
57 and items.cross_reference = ordered_item_L
58 and items.cross_reference_type = item_identifier_type_L
59 and nvl(sitems.organization_id,0) = c_master_org_L
60 and sitems.inventory_item_id = inventory_item_id_L
61 -- Bug 3433353 Begin
62 and items.org_independent_flag = 'N'
63 and items.organization_id = c_master_org_L;
64 -- and sitems.customer_order_enabled_flag = 'Y'
65 -- and sitems.bom_item_type in (1,4)
66 Exception When NO_DATA_FOUND Then
67 select items.cross_reference item,
68 nvl(items.description,sitems.description) description
69 into v_item,v_description
70 from mtl_cross_reference_types xtypes,
71 mtl_cross_references items,
72 mtl_system_items_vl sitems
73 where xtypes.cross_reference_type =
74 items.cross_reference_type
75 and items.inventory_item_id =
76 sitems.inventory_item_id
77 and items.cross_reference = ordered_item_L
78 and items.cross_reference_type = item_identifier_type_L
79 and nvl(sitems.organization_id,0) = c_master_org_L
80 and sitems.inventory_item_id = inventory_item_id_L
81 and items.org_independent_flag = 'Y';
82 End;
83 -- Bug 3422253 End
84 end if;
85
86 if (p_flex_or_desc in ('I','C')) then
87 return(v_item||' - '||v_description);
88 elsif (p_flex_or_desc in ('D','P')) then
89 return(v_description);
90 else
91 return(v_item);
92 end if;
93 RETURN NULL;
94 Exception
95 When Others Then
96 return('Item Not Found');
97 end;
98 FUNCTION SOB_NAMEFORMULA RETURN VARCHAR2 IS
99 BEGIN
100 DECLARE
101 SOB_NAME VARCHAR2(30);
102 BEGIN
103 SELECT
104 NAME
105 INTO SOB_NAME
106 FROM
107 GL_SETS_OF_BOOKS
108 WHERE SET_OF_BOOKS_ID = P_SOB_ID;
109 RETURN (SOB_NAME);
110 END;
111 RETURN NULL;
112 END SOB_NAMEFORMULA;
113
114 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
115 BEGIN
116 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
117 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
118 BEGIN
119 P_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
120 END;
121 BEGIN
122 /*SRW.REFERENCE(P_ITEM_FLEX_CODE)*/NULL;
123 /*SRW.REFERENCE(P_ITEM_STRUCTURE_NUM)*/NULL;
124 EXCEPTION
125 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
126 /*SRW.MESSAGE(1
127 ,'Failed in before report trigger:MSTK')*/NULL;
128 END;
129 IF P_ITEM_HI IS NULL AND P_ITEM_LO IS NULL THEN
130 NULL;
131 ELSE
132 BEGIN
133 NULL;
134 EXCEPTION
135 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
136 /*SRW.MESSAGE(2
137 ,'Failed in before report trigger:where:MSTK')*/NULL;
138 END;
139 END IF;
140 RETURN (TRUE);
141 END BEFOREREPORT;
142
143 FUNCTION AFTERREPORT RETURN BOOLEAN IS
144 BEGIN
145 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
146 RETURN (TRUE);
147 EXCEPTION
148 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
149 /*SRW.MESSAGE(1
150 ,'FAILED IN AFTER REPORT TRIGGER')*/NULL;
151 RETURN (FALSE);
152 END AFTERREPORT;
153
154 FUNCTION ITEM_DISPLAY_MEANINGFORMULA RETURN VARCHAR2 IS
155 BEGIN
156 DECLARE
157 ITEM_DISPLAY_MEANING VARCHAR2(80);
158 BEGIN
159 SELECT
160 MEANING
161 INTO ITEM_DISPLAY_MEANING
162 FROM
163 OE_LOOKUPS
164 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
165 AND LOOKUP_CODE = P_FLEX_OR_DESC;
166 RETURN (ITEM_DISPLAY_MEANING);
167 END;
168 RETURN NULL;
169 END ITEM_DISPLAY_MEANINGFORMULA;
170
171 FUNCTION C_CUSTOMER_WHERE RETURN VARCHAR2 IS
172 BEGIN
173 IF P_CUST_NAME_LO IS NOT NULL AND P_CUST_NAME_HI IS NOT NULL THEN
174 RETURN ('and org.name between ''' || P_CUST_NAME_LO || '''
175 and ''' || P_CUST_NAME_HI || ''' ');
176 ELSE
177 IF P_CUST_NAME_LO IS NULL AND P_CUST_NAME_HI IS NOT NULL THEN
178 RETURN ('and org.name <= ''' || P_CUST_NAME_HI || ''' ');
179 ELSE
180 IF P_CUST_NAME_LO IS NOT NULL AND P_CUST_NAME_HI IS NULL THEN
181 RETURN ('and org.name >= ''' || P_CUST_NAME_LO || ''' ');
182 ELSE
183 RETURN (NULL);
184 END IF;
185 END IF;
186 END IF;
187 RETURN NULL;
188 END C_CUSTOMER_WHERE;
189
190 FUNCTION C_HOLD_WHERE RETURN VARCHAR2 IS
191 BEGIN
192 IF P_HOLD_NAME_LO IS NOT NULL AND P_HOLD_NAME_HI IS NOT NULL THEN
193 RETURN ('and nvl(ho.name, ''ZZZ'') between :P_hold_name_lo
194 and :P_hold_name_hi ');
195 ELSE
196 IF P_HOLD_NAME_LO IS NULL AND P_HOLD_NAME_HI IS NOT NULL THEN
197 RETURN ('and nvl(ho.name, ''zzz'') <= :P_hold_name_hi');
198 ELSE
199 IF P_HOLD_NAME_LO IS NOT NULL AND P_HOLD_NAME_HI IS NULL THEN
200 RETURN ('and nvl(ho.name, ''zzz'') >= :P_hold_name_lo ');
201 ELSE
202 RETURN (NULL);
203 END IF;
204 END IF;
205 END IF;
206 RETURN NULL;
207 END C_HOLD_WHERE;
208
209 FUNCTION AFTERPFORM RETURN BOOLEAN IS
210 BEGIN
211 /*SRW.MESSAGE(99999
212 ,'$Header: ONT_OEXOHOHS_XMLP_PKG.rdf 120.4 2005/08/26 05:29 maysriva ship
213 $')*/NULL;
214 BEGIN
215 IF P_HOLD_NAME_LO IS NOT NULL AND P_HOLD_NAME_HI IS NOT NULL THEN
216 LP_HOLD_WHERE := 'and ho.name between :P_hold_name_lo and :P_hold_name_hi';
217 IF (P_HOLD_NAME_LO = P_HOLD_NAME_HI) THEN
218 LP_HOLD_WHERE := 'and ho.name = :P_hold_name_lo ';
219 END IF;
220 ELSIF P_HOLD_NAME_LO IS NULL AND P_HOLD_NAME_HI IS NOT NULL THEN
221 LP_HOLD_WHERE := 'and ho.name <= :P_hold_name_hi';
222 ELSIF P_HOLD_NAME_LO IS NOT NULL AND P_HOLD_NAME_HI IS NULL THEN
223 LP_HOLD_WHERE := 'and ho.name >= :P_hold_name_lo';
224 ELSE
225 LP_HOLD_WHERE := NULL;
226 END IF;
227 IF (LP_HOLD_WHERE IS NULL) THEN
228 LP_HOLD_WHERE := ' ';
229 END IF;
230
231 IF P_CUST_NAME_LO IS NOT NULL AND P_CUST_NAME_HI IS NOT NULL THEN
232 LP_CUSTOMER_WHERE := 'and org.name between :P_cust_name_lo and :P_cust_name_hi';
233 IF (P_CUST_NAME_LO = P_CUST_NAME_HI) THEN
234 LP_CUSTOMER_WHERE := 'and org.name = :P_cust_name_lo ';
235 END IF;
236 ELSIF P_CUST_NAME_LO IS NULL AND P_CUST_NAME_HI IS NOT NULL THEN
237 LP_CUSTOMER_WHERE := 'and org.name <= :P_cust_name_hi';
238 ELSIF P_CUST_NAME_LO IS NOT NULL AND P_CUST_NAME_HI IS NULL THEN
239 LP_CUSTOMER_WHERE := 'and org.name >= :P_cust_name_lo';
240 ELSE
241 LP_CUSTOMER_WHERE := NULL;
242 END IF;
243 END;
244 IF (LP_CUSTOMER_WHERE IS NULL) THEN
245 LP_CUSTOMER_WHERE := ' ';
246 END IF;
247 RETURN (TRUE);
248 END AFTERPFORM;
249
250 FUNCTION C_LINE_OR_ORDERFORMULA(P_ORDER_HOLD_ID IN NUMBER) RETURN VARCHAR2 IS
251 BEGIN
252 /*SRW.REFERENCE(ORDER_HOLD_ID)*/NULL;
253 DECLARE
254 LINE_ID NUMBER;
255 BEGIN
256 SELECT
257 LINE_ID
258 INTO LINE_ID
259 FROM
260 OE_ORDER_HOLDS
261 WHERE ORDER_HOLD_ID = P_ORDER_HOLD_ID;
262 IF LINE_ID IS NOT NULL THEN
263 RETURN ('LINE');
264 ELSE
265 RETURN ('ORDER');
266 END IF;
267 END;
268 RETURN NULL;
269 END C_LINE_OR_ORDERFORMULA;
270
271 FUNCTION C_SHOW_AMOUNTFORMULA(HEADER_ID IN NUMBER
272 ,C_LINE_OR_ORDER IN VARCHAR2) RETURN VARCHAR2 IS
273 BEGIN
274 DECLARE
275 HEADER_ID_VAR NUMBER;
276 BEGIN
277 /*SRW.REFERENCE(HEADER_ID)*/NULL;
278 /*SRW.REFERENCE(C_LINE_OR_ORDER)*/NULL;
279 IF C_LINE_OR_ORDER = 'LINE' THEN
280 SELECT
281 HEADER_ID
282 INTO HEADER_ID_VAR
283 FROM
284 OE_ORDER_HOLDS
285 WHERE HEADER_ID = HEADER_ID
286 AND LINE_ID is NULL
287 AND HOLD_RELEASE_ID is NULL;
288 END IF;
289 IF HEADER_ID_VAR IS NOT NULL THEN
290 RETURN ('N');
291 ELSE
292 RETURN ('Y');
293 END IF;
294 EXCEPTION
295 WHEN NO_DATA_FOUND THEN
296 RETURN ('Y');
297 WHEN TOO_MANY_ROWS THEN
298 RETURN ('N');
299 END;
300 RETURN NULL;
301 END C_SHOW_AMOUNTFORMULA;
302
303 FUNCTION C_MASTER_ORGFORMULA RETURN CHAR IS
304 V_MASTER_ORG VARCHAR2(20);
305 BEGIN
306 SELECT
307 NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
308 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
309 ,0)
310 INTO V_MASTER_ORG
311 FROM
312 DUAL;
313 RETURN V_MASTER_ORG;
314 END C_MASTER_ORGFORMULA;
315
316 FUNCTION C_AMOUNTFORMULA(AMOUNT IN NUMBER) RETURN NUMBER IS
317 BEGIN
318 RETURN (NVL(AMOUNT
319 ,0));
320 END C_AMOUNTFORMULA;
321
322 FUNCTION C_FORMATTED_FLEX_VALUE_P RETURN VARCHAR2 IS
323 BEGIN
324 RETURN C_FORMATTED_FLEX_VALUE;
325 END C_FORMATTED_FLEX_VALUE_P;
326
327 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
328 BEGIN
329 RETURN RP_ITEM_FLEX_ALL_SEG;
330 END RP_ITEM_FLEX_ALL_SEG_P;
331
332 END ONT_OEXOHOHS_XMLP_PKG;
333
[Home] [Help]