1 PACKAGE BODY PO_POXRVXRV_XMLP_PKG AS
2 /* $Header: POXRVXRVB.pls 120.4 2011/11/21 09:29:39 liayang ship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 Declare
8 l_org_displayed org_organization_definitions.organization_name%type;
9
10 Begin
11 QTY_PRECISION:= PO_common_xmlp_pkg.GET_PRECISION(P_QTY_PRECISION);
12 /*Bug 12998409 to pass the location id once location entered*/
13 if (P_location is not null) then
14
15 select location_id
16 into p_location_id
17 from hr_locations_all
18 where location_code = P_location;
19
20 end if;
21 /*End Bug 12998409*/
22 If (P_org_id is not null) then
23 begin
24 select organization_name
25 into l_org_displayed
26 from org_organization_definitions
27 where organization_id = P_org_id ;
28
29 P_org_displayed := l_org_displayed ;
30 end;
31 else begin
32 P_org_displayed := '' ;
33 end;
34 End if;
35 End;
36 BEGIN
37 if (get_p_struct_num <> TRUE )
38 then /*SRW.MESSAGE('1','P Struct Num Init failed');*/null;
39
40 end if;
41
42 null;
43
44
45 null;
46
47
48 null;
49
50
51 null;
52 RETURN TRUE;
53 END; return (TRUE);
54 end;
55
56 function AfterReport return boolean is
57 begin
58 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
59 return (TRUE);
60 end;
61
62 function orderby_clauseFormula return VARCHAR2 is
63 begin
64
65 if P_orderby = 'PROMISED DATE' then
66 return('1');
67 elsif P_orderby = 'VENDOR' then
68 return('2');
69 ELSE
70 return('1');
71 end if;
72 RETURN NULL; end;
73
74 procedure get_precision is
75 begin
76 /*srw.attr.mask := SRW.FORMATMASK_ATTR;*/null;
77
78 if P_qty_precision = 0 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0';*/null;
79
80 else
81 if P_qty_precision = 1 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.0';*/null;
82
83 else
84 if P_qty_precision = 3 then /*srw.attr.formatmask := '-NN,NNN,NNN,NN0.000';*/null;
85
86 else
87 if P_qty_precision = 4 then /*srw.attr.formatmask := '-N,NNN,NNN,NN0.0000';*/null;
88
89 else
90 if P_qty_precision = 5 then /*srw.attr.formatmask := '-NNN,NNN,NN0.00000';*/null;
91
92 else
93 if P_qty_precision = 6 then /*srw.attr.formatmask := '-NN,NNN,NN0.000000';*/null;
94
95 else
96
97
98
99 if P_qty_precision = 7 then /*srw.attr.formatmask := '-NNNNNNNNNNNNNN0';*/null;
100
101 else
102 if P_qty_precision = 8 then /*srw.attr.formatmask := '-NNNNNNNNNNNN0.0';*/null;
103
104 else
105 if P_qty_precision = 9 then /*srw.attr.formatmask := '-NNNNNNNNNNN0.00';*/null;
106
107 else
108 if P_qty_precision = 10 then /*srw.attr.formatmask := '-NNNNNNNNNN0.000';*/null;
109
110 else
111 if P_qty_precision = 11 then /*srw.attr.formatmask := '-NNNNNNNNN0.0000';*/null;
112
113 else
114 if P_qty_precision = 12 then /*srw.attr.formatmask := '-NNNNNNNN0.00000';*/null;
115
116 else
117 if P_qty_precision = 13 then /*srw.attr.formatmask := '-NNNNNNN0.000000';*/null;
118
119 else
120 /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.00';*/null;
121
122 end if; end if; end if; end if; end if; end if;
123 end if; end if; end if; end if; end if; end if; end if;
124 /*srw.set_attr(0,srw.attr);*/null;
125
126 end;
127
128 function get_p_struct_num return boolean is
129
130 l_p_struct_num number;
131
132 begin
133 select structure_id
134 into l_p_struct_num
135 from mtl_default_sets_view
136 where functional_area_id = 2 ;
137
138 LP_STRUCT_NUM := l_p_struct_num ;
139
140 return(TRUE) ;
141
142 RETURN NULL; exception
143 when others then return(FALSE) ;
144 end;
145
146 function AfterPForm return boolean is
147 begin
148 LP_VENDOR := P_VENDOR;
149 LP_STRUCT_NUM := P_STRUCT_NUM;
150 LP_CUSTOMER := P_CUSTOMER;
151
152 declare
153 req_numbering_type varchar2(240);
154 po_numbering_type varchar2(240);
155 Begin
156 /*SRW.USER_EXIT('FND SRWINIT');*/null;
157
158
159
160 SELECT manual_po_num_type
161 , manual_req_num_type
162 INTO po_numbering_type
163 , req_numbering_type
164 FROM po_system_parameters;
165
166
167
168
169
170 if ((P_po_num_from is not null) and (po_numbering_type = 'ALPHANUMERIC')) then
171 P_where_po_num_from := 'poh.segment1 >= '|| ''''|| P_po_num_from || '''';
172 elsif
173 ((P_po_num_from is not null) and (po_numbering_type = 'NUMERIC')) then
174 P_where_po_num_from := 'decode(rtrim(poh.segment1,''0123456789''),NULL,to_number(poh.segment1),null) >= '|| P_po_num_from;
175 else
176 P_where_po_num_from := '1=1';
177 end if;
178
179 if ((P_po_num_to is not null) and (po_numbering_type = 'ALPHANUMERIC')) then
180 P_where_po_num_to := 'poh.segment1 <= '|| ''''|| P_po_num_to|| '''';
181 elsif
182 ((P_po_num_to is not null) and (po_numbering_type = 'NUMERIC')) then
183 P_where_po_num_to := 'decode(rtrim(poh.segment1,''0123456789''),NULL,to_number(poh.segment1),null)
184 <= '|| P_po_num_to;
185 else
186 P_where_po_num_to := '1=1';
187 end if;
188
189 if ((P_po_num_to is not null)
190 and (P_po_num_from = P_po_num_to)
191 and (po_numbering_type = 'ALPHANUMERIC')) then
192 P_where_po_num_from := 'poh.segment1 = '|| ''''|| P_po_num_from || '''';
193 P_where_po_num_to := '1=1';
194 elsif
195 ((P_po_num_to is not null)
196 and (P_po_num_from = P_po_num_to)
197 and (po_numbering_type = 'NUMERIC')) then
198 P_where_po_num_from := 'decode(rtrim(poh.segment1,''0123456789''),NULL,to_number(poh.segment1),null)
199 = '|| P_po_num_from;
200 P_where_po_num_to := '1=1';
201 end if;
202
203 --Bug 12980455 The LP Vendor name was not in single quotes previously which was causing
204 -- ORA-00920: invalid relational operator error.
205
206 if (P_vendor is not null) then
207 LP_vendor := replace(P_vendor,'''','''''');
208 LP_vendor:=''''||LP_vendor ||'''';
209 P_where_vendor := 'pov.vendor_name = '||LP_vendor;
210 else
211 P_where_vendor := '1=1';
212 end if;
213
214 --Bug 13412373 REQ NUMBER INVALID
215 --Using decode clause instead of to_number which is same as PO Number
216 if ((P_req_num_from is not null) and (req_numbering_type = 'ALPHANUMERIC')) then
217 P_where_req_num_from := 'prh.segment1 >= ' ||''''||P_req_num_from||'''';
218 elsif
219 ((P_req_num_from is not null) and (req_numbering_type = 'NUMERIC')) then
220 P_where_req_num_from := 'decode(rtrim(prh.segment1,''0123456789''),NULL,to_number(prh.segment1),null) >= ' ||P_req_num_from;
221 else
222 P_where_req_num_from := '1=1';
223 end if;
224
225 if ((P_req_num_to is not null) and (req_numbering_type = 'ALPHANUMERIC')) then
226 P_where_req_num_to := 'prh.segment1 <= '|| ''''||P_req_num_to||'''';
227 elsif
228 ((P_req_num_to is not null) and (req_numbering_type = 'NUMERIC')) then
229 P_where_req_num_to := 'decode(rtrim(prh.segment1,''0123456789''),NULL,to_number(prh.segment1),null) <= '|| P_req_num_to;
230 else
231 P_where_req_num_to := '1=1';
232 end if;
233
234 if ((P_req_num_from is not null)
235 and (P_req_num_from = P_req_num_to)
236 and (req_numbering_type = 'ALPHANUMERIC')) then
237 P_where_req_num_from := 'prh.segment1 = ' ||''''||P_req_num_from||'''';
238 P_where_req_num_to := '1=1';
239 elsif
240 ((P_req_num_from is not null)
241 and (P_req_num_from = P_req_num_to)
242 and (req_numbering_type = 'NUMERIC')) then
243 P_where_req_num_from := 'decode(rtrim(prh.segment1,''0123456789''),NULL,to_number(prh.segment1),null) ='|| P_req_num_from;
244 P_where_req_num_to := '1=1';
245 end if;
246 -- End Bug 13412373
247
248 if (P_rma_num_from is not null) then
249 P_where_rma_num_from := 'to_number(rcv.oe_order_num) >= ' || P_rma_num_from;
250 else
251 P_where_rma_num_from := '1=1';
252 end if;
253
254 if (P_rma_num_to is not null) then
255 P_where_rma_num_to := 'to_number(rcv.oe_order_num) <= ' || P_rma_num_to;
256 else
257 P_where_rma_num_to := '1=1';
258 end if;
259
260 if (P_rma_num_from is not null
261 and (P_rma_num_from = P_rma_num_to)) then
262 P_where_rma_num_from := 'to_number(rcv.oe_order_num) = ' || P_rma_num_from;
263 P_where_rma_num_to := '1=1';
264 end if;
265
266 --Bug 12980455 The LP Customer was not there in single quotes which was causing
267 -- ORA-00920: invalid relational operator error.
268
269 if (P_customer is not null) then
270 LP_customer := replace(P_customer,'''','''''');
271 LP_customer := ''''||LP_customer ||'''';
272 P_where_customer := 'rcv.source = '||LP_customer;
273 else
274 P_where_customer := '1=1';
275 end if;
276
277 if( (P_po_num_from is NULL and P_po_num_to is NULL and
278 (P_req_num_from is NOT NULL or P_req_num_to is NOT NULL or P_rma_num_from is NOT NULL or P_rma_num_to is NOT NULL))) then
279 P_where_no_po_num := 'poh.po_header_id = -0';
280 end if;
281
282 if ( (P_req_num_from is NULL and P_req_num_to is NULL and
283 (P_po_num_from is NOT NULL or P_po_num_to is NOT NULL or P_rma_num_from is NOT NULL or P_rma_num_to is NOT NULL))) then
284 P_where_no_req_num := 'prh.requisition_header_id = -0';
285 end if;
286
287 if ( (P_rma_num_from is NULL and P_rma_num_to is NULL and
288 (P_po_num_from is NOT NULL or P_po_num_to is NOT NULL or P_req_num_from is NOT NULL or P_req_num_to is NOT NULL))) then
289 P_where_no_rma_num := 'rcv.oe_order_num = -0';
290 end if;
291
292
293
294 if ( P_org_id is not null ) then
295 P_PO_ORG := ' AND pll.ship_to_organization_id = :P_org_id ';
296 P_REQ_ORG := ' AND prl.destination_organization_id = :P_org_id ' ;
297 P_RMA_ORG := ' AND rcv.to_organization_id = :P_org_id ';
298 end if;
299
300
301
302
303
304
305
306
307
308 if (P_location is not null) then
309
310 if (P_org_id is not null) then
311
312
313 P_PO_ORG := P_PO_ORG || ' AND pll.ship_to_location_id = :p_location_id ';
314 P_REQ_ORG := P_REQ_ORG || ' AND prl.deliver_to_location_id = :p_location_id ';
315
316 else
317
318 P_PO_ORG := ' AND pll.ship_to_location_id = :p_location_id ';
319 P_REQ_ORG := ' AND prl.deliver_to_location_id = :p_location_id ';
320
321 end if;
322 end if;
323
324 End; return (TRUE);
325 end;
326
327 function P_STRUCT_NUMValidTrigger return boolean is
328 begin
329
330 return (TRUE);
331 end;
332
333 function BeforePForm return boolean is
334 begin
335 return (TRUE);
336 end;
337
338 function BetweenPage return boolean is
339 begin
340
341 return (TRUE);
342 end;
343
344 function P_org_displayedValidTrigger return boolean is
345 begin
346
347 return (TRUE);
348 end;
349
350 function location_code1formula(location in varchar2, Shipment_type in varchar2, location_id1 in number) return char is
351 x_location_code hr_locations_all.location_code%TYPE := NULL ;
352
353 begin
354
355 x_location_code := location;
356
357
358 if (x_location_code = 'ABC') then
359
360
361
362 IF (Shipment_type in ('STANDARD','BLANKET','SCHEDULED') ) THEN
363
364 BEGIN
365
366 select hrtl.location_code
367 into x_location_code
368 from hr_locations_all hrl,
369 hr_locations_all_tl hrtl
370 where hrl.location_id = location_id1
371 and hrl.location_id = hrtl.location_id
372 and hrtl.language = userenv('LANG');
373
374 EXCEPTION
375 when no_data_found then
376
377 select substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20) location_code
378 into x_location_code
379 from hz_locations hz
380 where hz.location_id = location_id1;
381 END;
382
383 end if;
384 end if;
385
386 return(x_location_code);
387 end;
388
389 function P_LOCATIONValidTrigger return boolean is
390 begin
391
392 if (P_location is not null) then
393
394 select location_id
395 into p_location_id
396 from hr_locations_all
397 where location_code = P_location;
398
399 end if;
400
401 return (TRUE);
402 end;
403
404 --Functions to refer Oracle report placeholders--
405
406 END PO_POXRVXRV_XMLP_PKG ;
407
[Home] [Help]