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