eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PO_POXQUAPR_XMLP_PKG

Source


1 PACKAGE BODY PO_POXQUAPR_XMLP_PKG AS
2 /* $Header: POXQUAPRB.pls 120.1.12020000.2 2012/11/29 07:25:33 kahe ship $ */
3 
4 function BeforeReport return boolean is
5 begin
6 
7 BEGIN
8   /*SRW.USER_EXIT('FND SRWINIT');*/null;
9 
10   IF (get_p_struct_num <> TRUE)
11     then /*SRW.MESSAGE('1','P Struct Num init failed.');*/null;
12 
13   END IF;
14 
15   If (P_detail_summary = 'Y' ) then P_report_type := 1 ;
16    else P_report_type := 0 ;
17   end if;
18 
19 
20  null;
21 
22 
23  null;
24 
25 
26  null;
27 FORMAT_MASK := PO_common_xmlp_pkg.GET_PRECISION(P_QTY_PRECISION);
28   RETURN TRUE;
29 
30 END;  return (TRUE);
31 end;
32 
33 function AfterReport return boolean is
34 begin
35 
36 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
37   return (TRUE);
38 end;
39 
40 procedure get_precision is
41 begin
42 /*srw.attr.mask        :=  SRW.FORMATMASK_ATTR;*/null;
43 
44 if P_qty_precision = 0 then /*srw.attr.formatmask  := '-NNN,NNN,NNN,NN0';*/null;
45 
46 else
47 if P_qty_precision = 1 then /*srw.attr.formatmask  := '-NNN,NNN,NNN,NN0.0';*/null;
48 
49 else
50 if P_qty_precision = 3 then /*srw.attr.formatmask  :=  '-NN,NNN,NNN,NN0.000';*/null;
51 
52 else
53 if P_qty_precision = 4 then /*srw.attr.formatmask  :=   '-N,NNN,NNN,NN0.0000';*/null;
54 
55 else
56 if P_qty_precision = 5 then /*srw.attr.formatmask  :=     '-NNN,NNN,NN0.00000';*/null;
57 
58 else
59 if P_qty_precision = 6 then /*srw.attr.formatmask  :=      '-NN,NNN,NN0.000000';*/null;
60 
61 else /*srw.attr.formatmask  :=  '-NNN,NNN,NN0.00';*/null;
62 
63 end if; end if; end if; end if; end if; end if;
64 /*srw.set_attr(0,srw.attr);*/null;
65 
66 end;
67 
68 function get_p_struct_num return boolean is
69 
70 l_p_struct_num number;
71 
72 begin
73         select structure_id
74         into l_p_struct_num
75         from mtl_default_sets_view
76         where functional_area_id = 2 ;
77 
78         P_STRUCT_NUM := l_p_struct_num ;
79 
80         return(TRUE) ;
81 
82         RETURN NULL; exception
83         when others then return(FALSE) ;
84 end;
85 
86 function per_acceptedformula(Received in number, Accepted in number) return number is
87 begin
88 
89 /*SRW.reference(Accepted) ;*/null;
90 
91 /*SRW.reference(Received) ;*/null;
92 
93 if (Received = 0) then return(0) ;
94 else return ROUND(((Accepted / Received) * 100 ), P_qty_precision) ;
95 end if;
96 RETURN NULL; end;
97 
98 function per_rejectedformula(Received in number, Rejected in number) return number is
99 begin
100 
101 /*SRW.reference(Received) ;*/null;
102 
103 /*SRW.reference(Rejected) ;*/null;
104 
105 if (Received = 0) then return (0) ;
106 else return ROUND(((Rejected / Received) * 100 ), P_qty_precision) ;
107 end if;
108 RETURN NULL; end;
109 
110 function per_returnedformula(Received in number, Returned in number) return number is
111 begin
112 
113 /*SRW.reference(Received) ;*/null;
114 
115 /*SRW.reference(Returned) ;*/null;
116 
117 if (Received = 0) then return(0) ;
118 else return ROUND(((Returned / Received ) * 100 ), P_qty_precision) ;
119 end if;
120 RETURN NULL; end;
121 
122 function c_item_per_acceptedformula(C_item_sum_rec in number, C_item_sum_acc in number) return number is
123 begin
124 
125 /*SRW.reference(C_item_sum_rec) ;*/null;
126 
127 /*SRW.reference(C_item_sum_acc) ;*/null;
128 
129 if (C_item_sum_rec = 0) then return(0) ;
130 else return ROUND(((C_item_sum_acc / C_item_sum_rec ) * 100 ), P_qty_precision );
131 end if;
132 RETURN NULL; end;
133 
134 function c_item_per_rejectedformula(C_item_sum_rec in number, C_item_sum_rej in number) return number is
135 begin
136 
137 /*SRW.reference(C_item_sum_rec) ;*/null;
138 
139 /*SRW.reference(C_item_sum_rej) ;*/null;
140 
141 if (C_item_sum_rec = 0) then return(0) ;
142 else return ROUND(((C_item_sum_rej / C_item_sum_rec ) * 100 ), P_qty_precision) ;
143 end if;
144 RETURN NULL; end;
145 
146 function c_item_per_returnedformula(C_item_sum_rec in number, C_item_sum_ret in number) return number is
147 begin
148 
149 /*SRW.reference(C_item_sum_rec) ;*/null;
150 
151 /*SRW.reference(C_item_sum_ret) ;*/null;
152 
153 if (C_item_sum_rec = 0) then return(0) ;
154 else return ROUND(((C_item_sum_ret / C_item_sum_rec) * 100 ), P_qty_precision) ;
155 end if;
156 RETURN NULL; end;
157 
158 function uninspectedformula(Received in number, Accepted in number, Rejected in number) return number is
159 begin
160 
161 /*srw.reference(Received) ;*/null;
162 
163 /*srw.reference(Accepted) ;*/null;
164 
165 /*srw.reference(Rejected) ;*/null;
166 
167 return (Received - Accepted - Rejected ) ;
168 end;
169 
170 function per_uninspectedformula(Received in number, Uninspected in number) return number is
171 begin
172 
173 /*SRW.reference(Received) ;*/null;
174 
175 /*SRW.reference(Uninspected) ;*/null;
176 
177 if (Received = 0) then return (0) ;
178 else return ROUND(((Uninspected / Received) * 100 ), P_qty_precision) ;
179 end if;
180 RETURN NULL; end;
181 
182 function per_rtv_wout_inspectformula(Received in number, Rtv_wout_inspect in number) return number is
183 begin
184 
185 /*SRW.reference(Received) ;*/null;
186 
187 /*SRW.reference(Rtv_wout_inspect) ;*/null;
188 
189 if (Received = 0) then return(0) ;
190 else return ROUND(((Rtv_wout_inspect / Received ) * 100 ), P_qty_precision) ;
191 end if;
192 RETURN NULL; end;
193 
194 function c_item_per_uninsformula(C_item_sum_rec in number, C_item_sum_unins in number) return number is
195 begin
196 
197 /*SRW.reference(C_item_sum_rec) ;*/null;
198 
199 /*SRW.reference(C_item_sum_unins) ;*/null;
200 
201 if (C_item_sum_rec = 0) then return(0) ;
202 else return ROUND(((C_item_sum_unins / C_item_sum_rec ) * 100 ), P_qty_precision) ;
203 end if;
204 RETURN NULL; end;
205 
206 function c_item_per_rtv_wout_insformula(C_item_sum_rec in number, C_item_sum_rtv_wout_ins in number) return number is
207 begin
208 
209 /*SRW.reference(C_item_sum_rec) ;*/null;
210 
211 /*SRW.reference(C_item_sum_rtv_wout_ins) ;*/null;
212 
213 if (C_item_sum_rec = 0) then return(0) ;
214 else return ROUND(((C_item_sum_rtv_wout_ins / C_item_sum_rec) * 100 ), P_qty_precision) ;
215 end if;
216 RETURN NULL; end;
217 
218 function quantity_acceptedformula(pll_quantity_accepted in number, conversion_rate in varchar2) return number is
219 begin
220 
221 /*srw.reference(conversion_rate) ;*/null;
222 
223 /*srw.reference(pll_quantity_accepted) ;*/null;
224 
225 return(pll_quantity_accepted * conversion_rate ) ;
226 end;
227 
228 function quantity_rejectedformula(conversion_rate in varchar2, pll_quantity_rejected in number) return number is
229 begin
230 
231 /*srw.reference(conversion_rate) ;*/null;
232 
233 /*srw.reference(pll_quantity_rejected) ;*/null;
234 
235 return(conversion_rate * pll_quantity_rejected ) ;
236 end;
237 
238 function quantity_orderedformula(conversion_rate in varchar2, pll_quantity_ordered in number) return number is
239 begin
240 
241 /*srw.reference(conversion_rate) ;*/null;
242 
243 /*srw.reference(pll_quantity_ordered) ;*/null;
244 
245 return(conversion_rate * pll_quantity_ordered ) ;
246 end;
247 
248 function c_tot_returnedformula(parent_line_location_id in number, Item_id in number) return number is
249 begin
250 
251 begin
252 declare
253 
254 ret_qty number;
255 cor_qty number;
256 
257 quantity_returned number;
258 
259 begin
260 
261 select sum(rct.primary_quantity) into ret_qty
262 from rcv_transactions     rct
263 where rct.transaction_type = 'RETURN TO VENDOR'
264 and   rct.po_line_location_id = parent_line_location_id
265 UNION
266 select sum(rct.primary_quantity)
267 from rcv_transactions     rct
268 where rct.transaction_type = 'RETURN TO VENDOR'
269 and   rct.po_line_location_id = parent_line_location_id
270 and Item_id is NULL
271 group by rct.po_line_location_id;
272 
273 select sum(rct.primary_quantity) into cor_qty
274 from rcv_transactions     rct, rcv_transactions rct1
275 where rct.transaction_type = 'CORRECT'
276 and   rct.po_line_location_id = parent_line_location_id
277 and   rct.parent_transaction_id = rct1.transaction_id
278 and   rct1.transaction_type = 'RETURN TO VENDOR'
279 UNION
280 select sum(rct.primary_quantity)
281 from rcv_transactions     rct, rcv_transactions rct1
282 where rct.transaction_type = 'CORRECT'
283 and   rct.po_line_location_id = parent_line_location_id
284 and Item_id is NULL
285 and   rct.parent_transaction_id = rct1.transaction_id
286 and   rct1.transaction_type = 'RETURN TO VENDOR'
287 group by rct.po_line_location_id;
288 
289 quantity_returned := nvl(ret_qty,0) + nvl(cor_qty,0);
290 return(quantity_returned);
291 
292 end;
293 end;
294 RETURN NULL; end;
295 
296 function c_tot_inspectedformula(parent_line_location_id in number, Item_id in number) return number is
297 begin
298 
299 begin
300 declare
301 
302 ins_qty number;
303 cor_qty number;
304 
305 quantity_inspected number;
306 
307 begin
308 
309 select sum(rct.primary_quantity) into ins_qty
310 from rcv_transactions     rct
311 where rct.transaction_type = 'RETURN TO VENDOR'
312 and nvl(rct.inspection_status_code,'NOT INSPECTED') = 'NOT INSPECTED'
313 and   rct.po_line_location_id = parent_line_location_id
314 UNION
315 select sum(rct.primary_quantity)
316 from rcv_transactions     rct
317 where rct.transaction_type = 'RETURN TO VENDOR'
318 and nvl(rct.inspection_status_code,'NOT INSPECTED') = 'NOT INSPECTED'
319 and   rct.po_line_location_id = parent_line_location_id
320 and Item_id is NULL
321 group by rct.po_line_location_id;
322 
323 select sum(rct.primary_quantity) into cor_qty
324 from rcv_transactions     rct, rcv_transactions rct1
325 where rct.transaction_type = 'CORRECT'
326 and   rct.po_line_location_id = parent_line_location_id
327 and   rct.parent_transaction_id = rct1.transaction_id
328 and   rct1.transaction_type = 'RETURN TO VENDOR'
329 and nvl(rct1.inspection_status_code,'NOT INSPECTED') = 'NOT INSPECTED'
330 UNION
331 select sum(rct.primary_quantity)
332 from rcv_transactions     rct, rcv_transactions rct1
333 where rct.transaction_type = 'CORRECT'
334 and   rct.po_line_location_id = parent_line_location_id
335 and Item_id is NULL
336 and   rct.parent_transaction_id = rct1.transaction_id
337 and   rct1.transaction_type = 'RETURN TO VENDOR'
338 and nvl(rct1.inspection_status_code,'NOT INSPECTED') = 'NOT INSPECTED'
339 group by rct.po_line_location_id;
340 
341 quantity_inspected := nvl(ins_qty,0) + nvl(cor_qty,0);
342 return(quantity_inspected);
343 
344 end;
345 end;
346 RETURN NULL; end;
347 
348 function c_tot_receivedformula(parent_line_location_id in number, Item_id in number) return number is
349 begin
350 
351 begin
352 declare
353 
354 rec_qty number;
355 cor_qty number;
356 
357 quantity_received number;
358 
359 begin
360 
361 select sum(rct.primary_quantity) into rec_qty
362 from rcv_transactions     rct
363 where rct.transaction_type = 'RECEIVE'
364 and   rct.po_line_location_id = parent_line_location_id
365 UNION
366 select sum(rct.primary_quantity)
367 from rcv_transactions     rct
368 where rct.transaction_type = 'RECEIVE'
369 and   rct.po_line_location_id = parent_line_location_id
370 and Item_id is NULL
371 group by rct.po_line_location_id;
372 
373 select sum(rct.primary_quantity) into cor_qty
374 from rcv_transactions     rct, rcv_transactions rct1
375 where rct.transaction_type = 'CORRECT'
376 and   rct.po_line_location_id = parent_line_location_id
377 and   rct.parent_transaction_id = rct1.transaction_id
378 and   rct1.transaction_type = 'RECEIVE'
379 UNION
380 select sum(rct.primary_quantity)
381 from rcv_transactions     rct, rcv_transactions rct1
382 where rct.transaction_type = 'CORRECT'
383 and   rct.po_line_location_id = parent_line_location_id
384 and Item_id is NULL
385 and   rct.parent_transaction_id = rct1.transaction_id
386 and   rct1.transaction_type = 'RECEIVE'
387 group by rct.po_line_location_id;
388 
389 quantity_received := nvl(rec_qty,0) + nvl(cor_qty,0);
390 return(quantity_received);
391 
392 end;
393 end;
394 RETURN NULL; end;
395 
396 --bug#11840167
397 function conversion_rate(item_id in number,c_organization_id in number,Item_Unit in varchar2,PO_unit_of_measure in varchar2) return number is
398 begin
399 
400 begin
401 declare
402 
403 con_rate number;
404 
405 begin
406  select        conversion_rate    into con_rate
407 from
408 	mtl_uom_conversions_view
409 where
410 	inventory_item_id = item_id
411 and           organization_id = c_organization_id
412 and           primary_uom_code = Item_Unit
413 and           uom_code = PO_unit_of_measure
414  and mtl_uom_conversions_view.uom_code=PO_unit_of_measure;
415 
416 return(con_rate);
417 
418 end;
419 end;
420 RETURN NULL; end;
421 
422 --Functions to refer Oracle report placeholders--
423 
424 END PO_POXQUAPR_XMLP_PKG ;
425