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_POXSERPR_XMLP_PKG

Source


1 PACKAGE BODY PO_POXSERPR_XMLP_PKG AS
2 /* $Header: POXSERPRB.pls 120.1 2007/12/25 12:24:54 krreddy noship $ */
3 
4 function BeforeReport return boolean is
5 
6 l_person_id   PER_ALL_PEOPLE_F.person_id%TYPE;
7 l_inv_org_id  FINANCIALS_SYSTEM_PARAMETERS.inventory_organization_id%TYPE;
8 l_vendor_name_where   VARCHAR2(1000);
9 l_buyer_where_h       VARCHAR2(400);
10 l_buyer_where_r       VARCHAR2(400);
11 l_creation_date_where_h VARCHAR2(400);
12 l_creation_date_where_r VARCHAR2(400);
13 l_item_query_filter   VARCHAR2(2000);
14 begin
15 
16 
17 
18 
19 BEGIN
20 QTY_PRECISION:=po_common_xmlp_pkg.GET_PRECISION(P_qty_precision);
21   /*SRW.USER_EXIT('FND SRWINIT');*/null;
22 
23   IF (get_p_struct_num <> TRUE)
24     THEN /*SRW.MESSAGE('1','P Struct Num init failed');*/null;
25 
26   END IF;
27 
28 
29  null;
30 
31  null;
32 
33  null;
34 
35 
36 
37 l_vendor_name_where := ' ';
38 l_creation_date_where_h := ' ';
39 l_creation_date_where_r := ' ';
40 l_buyer_where_h := ' ';
41 l_buyer_where_r := ' ';
42 
43 
44 
45 
46 IF ((P_VENDOR_FROM IS NOT NULL)
47    AND (P_VENDOR_TO IS NOT NULL)) THEN
48 
49   l_vendor_name_where := ' AND pov.vendor_name BETWEEN ' || ''''
50                        || replace(P_VENDOR_FROM,'''','''''') || '''' || ' AND '
51                        || '''' || replace(P_VENDOR_TO,'''','''''') || '''' || ' ';
52 
53 ELSIF (P_VENDOR_FROM IS NOT NULL) THEN
54 
55   l_vendor_name_where := ' AND pov.vendor_name >= ' || ''''
56                        || replace(P_VENDOR_FROM,'''','''''') || '''' || ' ';
57 
58 ELSIF (P_VENDOR_TO IS NOT NULL) THEN
59 
60   l_vendor_name_where := ' AND pov.vendor_name <= ' || ''''
61                        || replace(P_VENDOR_TO,'''','''''') || '''' || ' ';
62 
63 END IF;
64 
65 IF ((P_CREATION_DATE_FROM IS NOT NULL)
66    AND (P_CREATION_DATE_FROM IS NOT NULL)) THEN
67 
68   l_creation_date_where_h := ' AND poh.creation_date BETWEEN trunc(to_date('
69                 || '''' || P_CREATION_DATE_FROM || ''''
70                 || ',' || '''' || 'YYYY/MM/DD HH24:MI:SS' || ''''
71                 || ')) AND trunc(to_date('
72                 || '''' || P_CREATION_DATE_TO || ''''
73                 || ',' || '''' || 'YYYY/MM/DD HH24:MI:SS' || ''''
74                 || ')) + 0.99999 ';
75 
76   l_creation_date_where_r := ' AND por.creation_date BETWEEN trunc(to_date('
77                 || '''' || P_CREATION_DATE_FROM || ''''
78                 || ',' || '''' || 'YYYY/MM/DD HH24:MI:SS' || ''''
79                 || ')) AND trunc(to_date('
80                 || '''' || P_CREATION_DATE_TO || ''''
81                 || ',' || '''' || 'YYYY/MM/DD HH24:MI:SS' || ''''
82                 || ')) + 0.99999 ';
83 
84 
85 
86 ELSIF (P_CREATION_DATE_FROM IS NOT NULL) THEN
87 
88   l_creation_date_where_h := ' AND poh.creation_date >= trunc(to_date('
89                 || '''' || P_CREATION_DATE_FROM || ''''
90                 || ',' || '''' || 'YYYY/MM/DD HH24:MI:SS' || ''''
91                 || ')) ';
92   l_creation_date_where_r := ' AND por.creation_date >= trunc(to_date('
93                 || '''' || P_CREATION_DATE_FROM || ''''
94                 || ',' || '''' || 'YYYY/MM/DD HH24:MI:SS' || ''''
95                 || ')) ';
96 
97 
98 ELSIF (P_CREATION_DATE_TO IS NOT NULL) THEN
99 
100   l_creation_date_where_h := ' AND poh.creation_date <= trunc(to_date('
101                 || '''' || P_CREATION_DATE_TO || ''''
102                 || ',' || '''' || 'YYYY/MM/DD HH24:MI:SS' || ''''
103                 || ')) + 0.99999 ';
104   l_creation_date_where_r := ' AND por.creation_date <= trunc(to_date('
105                 || '''' || P_CREATION_DATE_TO || ''''
106                 || ',' || '''' || 'YYYY/MM/DD HH24:MI:SS' || ''''
107                 || ')) + 0.99999 ';
108 
109 END IF;
110 
111 
112 IF (P_BUYER IS NOT NULL) THEN
113 
114   SELECT papf.person_id
115   INTO l_person_id
116   FROM per_all_people_f  papf
117   WHERE papf.business_group_id = (
118                     SELECT nvl(max(fsp.business_group_id),0)
119                     FROM financials_system_parameters fsp)
120     AND trunc(sysdate) BETWEEN
121                 papf.effective_start_date and papf.effective_end_date
122     AND decode(hr_general.get_xbg_profile,'Y', papf.business_group_id
123             , hr_general.get_business_group_id) = papf.business_group_id
124     AND papf.full_name = nvl(P_BUYER,papf.full_name);
125 
126   l_buyer_where_h := 'AND poh.agent_id = ' || l_person_id;
127   l_buyer_where_r := 'AND por.agent_id = ' || l_person_id;
128 
129 END IF;
130 
131 
132 If ((P_ITEM_FROM IS NOT NULL) or (P_ITEM_TO IS NOT NULL) or (P_CATEGORY_FROM IS NOT NULL) or (P_CATEGORY_TO IS NOT NULL))
133 THEN
134 
135     SELECT fsp.inventory_organization_id
136   INTO l_inv_org_id
137   FROM financials_system_parameters fsp;
138 
139   IF ((P_ITEM_FROM IS NOT NULL) or (P_ITEM_TO IS NOT NULL))
140   THEN
141 
142     l_item_query_filter := ' pol.item_id IN '
143        || '            ( '
144        || '               SELECT msi.inventory_item_id '
145        || '               FROM mtl_system_items msi '
146        || '               WHERE nvl(msi.organization_id, ' || l_inv_org_id || ' ) = ' || l_inv_org_id
147        || '               AND ' || P_WHERE_ITEM
148        || '            )';
149 
150 
151     IF ((P_CATEGORY_FROM IS NOT NULL) or (P_CATEGORY_TO IS NOT NULL))
152     THEN
153 
154       l_item_query_filter := l_item_query_filter || ' AND '
155        || '            pol.category_id IN '
156        || '            ( '
157        || '              SELECT mca.category_id FROM mtl_categories mca '
158        || '              WHERE ' || P_WHERE_CAT
159        || '            ) ';
160 
161     END IF;
162 
163   ELSE
164 
165       l_item_query_filter :=  '            pol.category_id IN '
166        || '            ( '
167        || '              SELECT mca.category_id FROM mtl_categories mca '
168        || '              WHERE ' || P_WHERE_CAT
169        || '            ) ';
170 
171   END IF;
172 
173 
174 
175   P_VENDOR_QUERY := ' UNION '
176        || ' SELECT DISTINCT pov.vendor_name Vendor '
177        || '               , pov.segment1    Vendor_Number '
178        || '               , pov.vendor_id   Parent_vendor_id '
179        || ' FROM po_headers poh, po_vendors pov '
180        || ' WHERE poh.vendor_id = pov.vendor_id '
181        || l_buyer_where_h
182        || l_creation_date_where_h
183        || l_vendor_name_where
184        || '   AND EXISTS '
185        || '       ( '
186        || '         SELECT 1 '
187        || '         FROM rcv_transactions rct '
188        || '         WHERE rct.po_header_id = poh.po_header_id '
189        || '           AND rct.transaction_type = ''RECEIVE'' '
190        || '           AND rct.po_release_id IS NULL '
191        || '       ) '
192        || '   AND poh.po_header_id IN '
193        || '       ( '
194        || '         SELECT DISTINCT pol.po_header_id '
195        || '         FROM po_lines pol '
196        || '         WHERE ' || l_item_query_filter
197        || '        ) '
198        || ' UNION '
199        || ' SELECT DISTINCT pov.vendor_name Vendor '
200        || '               , pov.segment1    Vendor_Number '
201        || '               , pov.vendor_id   Parent_vendor_id '
202        || ' FROM po_headers poh, po_releases_all por, po_vendors pov '
203        || ' WHERE poh.vendor_id = pov.vendor_id '
204        || '   AND poh.org_id = por.org_id '
205        || '   AND por.po_header_id = poh.po_header_id '
206        || l_buyer_where_r
207        || l_creation_date_where_r
208        || l_vendor_name_where
209        || '   AND EXISTS '
210        || '       ( '
211        || '         SELECT 1 '
212        || '         FROM rcv_transactions rct '
213        || '         WHERE rct.po_header_id = poh.po_header_id '
214        || '           AND rct.transaction_type = ''RECEIVE'' '
215        || '           AND rct.po_release_id IS NOT NULL '
216        || '       ) '
217        || '   AND por.po_release_id IN '
218        || '       ( '
219        || '         SELECT DISTINCT pll.po_release_id '
220        || '         FROM po_lines_all pol, po_line_locations pll '
221        || '         WHERE pol.po_line_id = pll.po_line_id '
222        || '           AND pol.org_id = pll.org_id '
223        || '           AND ' || l_item_query_filter
224        || '        ) ';
225 
226 
227 ELSIF ((P_BUYER IS NOT NULL) or (P_CREATION_DATE_FROM IS NOT NULL) or (P_CREATION_DATE_TO IS NOT NULL))
228 THEN
229     P_VENDOR_QUERY := ' UNION '
230        || ' SELECT DISTINCT pov.vendor_name Vendor  '
231        || '               , pov.segment1    Vendor_Number '
232        || '               , pov.vendor_id   Parent_vendor_id '
233        || ' FROM po_headers poh, po_vendors pov '
234        || ' WHERE poh.vendor_id = pov.vendor_id '
235        || l_buyer_where_h
236        || l_creation_date_where_h
237        || l_vendor_name_where
238        || '   AND EXISTS '
239        || '     ( '
240        || '       SELECT 1 '
241        || '       FROM rcv_transactions rct '
242        || '       WHERE rct.po_header_id = poh.po_header_id '
243        || '         AND rct.transaction_type = ''RECEIVE'' '
244        || '         AND rct.po_release_id IS NULL '
245        || '     ) '
246        || ' UNION '
247        || ' SELECT DISTINCT pov.vendor_name Vendor '
248        || '               , pov.segment1    Vendor_Number '
249        || '               , pov.vendor_id   Parent_vendor_id '
250        || ' FROM po_releases por, po_headers poh, po_vendors pov '
251        || ' WHERE poh.vendor_id = pov.vendor_id '
252        || ' AND por.po_header_id = poh.po_header_id '
253        || l_buyer_where_r
254        || l_creation_date_where_r
255        || l_vendor_name_where
256        || '   AND EXISTS '
257        || '     ( '
258        || '       SELECT 1 '
259        || '       FROM rcv_transactions rct '
260        || '       WHERE rct.po_header_id = poh.po_header_id '
261        || '         AND rct.transaction_type = ''RECEIVE'' '
262        || '         AND rct.po_release_id IS NOT NULL '
263        || '     ) ';
264 
265 
266 ELSE
267     P_VENDOR_QUERY := ' UNION '
268        || ' SELECT pov.vendor_name Vendor, pov.segment1 Vendor_Number, pov.vendor_id Parent_vendor_id'
269        || ' FROM po_vendors pov'
270        || ' WHERE'
271        || ' ( '
272        || '   EXISTS  '
273        || '   (  '
274        || '    SELECT 1 '
275        || '    FROM po_headers poh '
276        || '    WHERE poh.vendor_id = pov.vendor_id '
277        || '     AND EXISTS '
278        || '        ( '
279        || '          SELECT 1 '
280        || '          FROM rcv_transactions rct '
281        || '          WHERE rct.po_header_id = poh.po_header_id '
282        || '          AND rct.transaction_type = ''RECEIVE'' '
283        || '          AND rct.po_release_id IS NULL '
284        || '        ) '
285        || '    ) '
286        || ' OR EXISTS '
287        || '    ( '
288        || '     SELECT 1 '
289        || '     FROM po_releases por, po_headers poh '
290        || '     WHERE poh.vendor_id = pov.vendor_id '
291        || '     AND por.po_header_id = poh.po_header_id '
292        || '     AND EXISTS '
293        || '        ( '
294        || '         SELECT 1 '
295        || '         FROM rcv_transactions rct '
296        || '         WHERE rct.po_header_id = poh.po_header_id '
297        || '         AND rct.transaction_type = ''RECEIVE'' '
298        || '         AND rct.po_release_id IS NOT NULL '
299        || '        ) '
300        || '     ) '
301        || '  ) '
302        || l_vendor_name_where;
303 
304 END IF;
305 
306 
307 
308   RETURN TRUE;
309 END;  return (TRUE);
310 end;
311 
312 function AfterReport return boolean is
313 begin
314 
315 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
316   return (TRUE);
317 end;
318 
319 procedure get_precision is
320 begin
321 /*srw.attr.mask        :=  SRW.FORMATMASK_ATTR;*/null;
322 
323 if P_qty_precision = 0 then /*srw.attr.formatmask  := '-NNN,NNN,NNN,NN0';*/null;
324 
325 else
326 if P_qty_precision = 1 then /*srw.attr.formatmask  := '-NNN,NNN,NNN,NN0.0';*/null;
327 
328 else
329 if P_qty_precision = 3 then /*srw.attr.formatmask  :=  '-NN,NNN,NNN,NN0.000';*/null;
330 
331 else
332 if P_qty_precision = 4 then /*srw.attr.formatmask  :=   '-N,NNN,NNN,NN0.0000';*/null;
333 
334 else
335 if P_qty_precision = 5 then /*srw.attr.formatmask  :=     '-NNN,NNN,NN0.00000';*/null;
336 
337 else
338 if P_qty_precision = 6 then /*srw.attr.formatmask  :=      '-NN,NNN,NN0.000000';*/null;
339 
340 else /*srw.attr.formatmask  :=  '-NNN,NNN,NN0.00';*/null;
341 
342 end if; end if; end if; end if; end if; end if;
343 /*srw.set_attr(0,srw.attr);*/null;
344 
345 end;
346 
347 function get_p_struct_num return boolean is
348 
349 l_p_struct_num number;
350 
351 begin
352         select structure_id
353         into l_p_struct_num
354         from mtl_default_sets_view
355         where functional_area_id = 2 ;
356 
357         P_STRUCT_NUM := l_p_struct_num ;
358 
359         return(TRUE) ;
360 
361         RETURN NULL; exception
362         when others then return(FALSE) ;
363 end;
364 
365 function locationformula(PO_RECEIVED in number, PO_WRONG_LOCATION in number) return number is
366 begin
367 
368 /*SRW.REFERENCE(PO_RECEIVED);*/null;
369 
370 /*SRW.REFERENCE(PO_WRONG_LOCATION) ;*/null;
371 
372 if (PO_RECEIVED = 0 ) then return(0) ;
373 else return (round((PO_WRONG_LOCATION / PO_RECEIVED),P_qty_precision+2) * 100 ) ;
374 end if;
375 RETURN NULL; end;
376 
377 function on_timeformula(PO_ORDERED in number, PO_RECEIVED in number, PO_ON_TIME in number) return number is
378 begin
379 
380 /*SRW.REFERENCE(PO_ORDERED) ;*/null;
381 
382 /*SRW.REFERENCE(PO_ON_TIME) ;*/null;
383 
384 /*SRW.REFERENCE(PO_RECEIVED) ;*/null;
385 
386 IF (PO_ORDERED > PO_RECEIVED) then
387 begin
388 	if (PO_ORDERED = 0 ) then return (0) ;
389 	else return ( round((PO_ON_TIME / PO_ORDERED ),P_qty_precision+2) * 100 ) ;
390 	end if;
391 end;
392 else begin
393 	if (PO_RECEIVED = 0 ) then return (0) ;
394 	else return ( round((PO_ON_TIME / PO_RECEIVED ),P_qty_precision+2) * 100 ) ;
395 	end if;
396 end ;
397 end if;
398 
399 RETURN NULL; end;
400 
401 function lateformula(PO_ORDERED in number, PO_RECEIVED in number, PO_LATE in number) return number is
402 begin
403 
404 /*SRW.REFERENCE(PO_ORDERED) ;*/null;
405 
406 /*SRW.REFERENCE(PO_LATE) ;*/null;
407 
408 /*SRW.REFERENCE(PO_RECEIVED) ;*/null;
409 
410 IF (PO_ORDERED > PO_RECEIVED) then
411 begin
412 	if (PO_ORDERED = 0 ) then return (0) ;
413 	else return ( round((PO_LATE / PO_ORDERED ),P_qty_precision+2) * 100 ) ;
414 	end if;
415 end;
416 else begin
417 	if (PO_RECEIVED = 0 ) then return (0) ;
418 	else return ( round((PO_LATE / PO_RECEIVED ),P_qty_precision+2) * 100 ) ;
419 	end if;
420 end ;
421 end if;
422 
423 RETURN NULL; end;
424 
425 function earlyformula(PO_ORDERED in number, PO_RECEIVED in number, PO_EARLY in number) return number is
426 begin
427 
428 /*SRW.REFERENCE(PO_ORDERED) ;*/null;
429 
430 /*SRW.REFERENCE(PO_EARLY) ;*/null;
431 
432 /*SRW.REFERENCE(PO_RECEIVED) ;*/null;
433 
434 IF (PO_ORDERED > PO_RECEIVED) then
435 begin
436 	if (PO_ORDERED = 0 ) then return (0) ;
437 	else return ( round((PO_EARLY / PO_ORDERED ),P_qty_precision+2) * 100 ) ;
438 	end if;
439 end;
440 else begin
441 	if (PO_RECEIVED = 0 ) then return (0) ;
442 	else return ( round((PO_EARLY / PO_RECEIVED ),P_qty_precision+2) * 100 ) ;
443 	end if;
444 end ;
445 end if;
446 
447 RETURN NULL; end;
448 
449 function varianceformula(PO_RECEIVED in number, PO_DAYS_QTY in number) return number is
450 begin
451 
452 /*SRW.REFERENCE(PO_DAYS_QTY) ;*/null;
453 
454 /*SRW.REFERENCE(PO_RECEIVED) ;*/null;
455 
456 if (PO_RECEIVED = 0 ) then return (0) ;
457 else return ( round(( PO_DAYS_QTY / PO_RECEIVED),P_qty_precision)  ) ;
458 end if;
459 RETURN NULL; end;
460 
461 function c_per_item_earlyformula(C_item_sum_ord in number, C_item_sum_rec in number, C_item_sum_early in number) return number is
462 begin
463 
464 /*SRW.REFERENCE(C_ITEM_SUM_ORD) ;*/null;
465 
466 /*SRW.REFERENCE(C_ITEM_SUM_REC) ;*/null;
467 
468 /*SRW.REFERENCE(C_ITEM_SUM_EARLY) ;*/null;
469 
470 if (C_item_sum_ord > C_item_sum_rec) then
471 begin
472 	if (C_item_sum_ord = 0 ) then return (0) ;
473 	else return ( round((C_item_sum_early / C_item_sum_ord ),P_qty_precision+2) * 100 ) ;
474 	end if;
475 end;
476 else
477 begin
478 	if (C_item_sum_rec = 0 ) then return (0) ;
479 	else return ( round((C_item_sum_early / C_item_sum_rec ),P_qty_precision+2) * 100 ) ;
480 	end if;
481 end;
482 end if;
483 
484 RETURN NULL; end;
485 
486 function c_per_item_lateformula(C_item_sum_ord in number, C_item_sum_rec in number, C_item_sum_late in number) return number is
487 begin
488 
489 /*SRW.REFERENCE(C_ITEM_SUM_ORD) ;*/null;
490 
491 /*SRW.REFERENCE(C_ITEM_SUM_REC) ;*/null;
492 
493 /*SRW.REFERENCE(C_ITEM_SUM_LATE) ;*/null;
494 
495 if (C_item_sum_ord > C_item_sum_rec) then
496 begin
497 	if (C_item_sum_ord = 0 ) then return (0) ;
498 	else return ( round((C_item_sum_late / C_item_sum_ord ),P_qty_precision+2) * 100 ) ;
499 	end if;
500 end;
501 else
502 begin
503 	if (C_item_sum_rec = 0 ) then return (0) ;
504 	else return ( round((C_item_sum_late / C_item_sum_rec ),P_qty_precision+2) * 100 ) ;
505 	end if;
506 end;
507 end if;
508 RETURN NULL; end;
509 
510 function c_per_item_on_timeformula(C_item_sum_ord in number, C_item_sum_rec in number, C_item_sum_on_time in number) return number is
511 begin
512 
513 /*SRW.REFERENCE(C_ITEM_SUM_ORD) ;*/null;
514 
515 /*SRW.REFERENCE(C_ITEM_SUM_REC) ;*/null;
516 
517 /*SRW.REFERENCE(C_ITEM_SUM_ON_TIME) ;*/null;
518 
519 if (C_item_sum_ord > C_item_sum_rec) then
520 begin
521 	if (C_item_sum_ord = 0 ) then return (0) ;
522 	else return ( round((C_item_sum_on_time / C_item_sum_ord ),P_qty_precision+2) * 100 ) ;
523 	end if;
524 end;
525 else
526 begin
527 	if (C_item_sum_rec = 0 ) then return (0) ;
528 	else return ( round((C_item_sum_on_time / C_item_sum_rec ),P_qty_precision+2) * 100 ) ;
529 	end if;
530 end;
531 end if;
532 RETURN NULL; end;
533 
534 function c_per_item_locformula(C_item_sum_rec in number, C_item_sum_w_loc in number) return number is
535 begin
536 
537 /*srw.reference(C_item_sum_w_loc) ;*/null;
538 
539 /*srw.reference(C_item_sum_rec) ;*/null;
540 
541 if (C_item_sum_rec = 0 ) then return(0) ;
542 else return (round((C_item_sum_w_loc / C_item_sum_rec),P_qty_precision+2) * 100 ) ;
543 end if;
544 RETURN NULL; end;
545 
546 function c_per_item_rejformula(C_item_sum_rec in number, C_item_sum_rej in number) return number is
547 begin
548 
549 /*srw.reference(C_item_sum_rej) ;*/null;
550 
551 /*srw.reference(C_item_sum_rec) ;*/null;
552 
553 if (C_item_sum_rec = 0 ) then return (0) ;
554 else return ( round(( C_item_sum_rej / C_item_sum_rec ),P_qty_precision+2) * 100 ) ;
555 end if;
556 RETURN NULL; end;
557 
558 function c_per_item_varformula(C_item_sum_rec in number, C_item_sum_days_qty in number) return number is
559 begin
560 
561 /*srw.reference(C_item_sum_days_qty) ;*/null;
562 
563 /*srw.reference(C_item_sum_rec) ;*/null;
564 
565 if (C_item_sum_rec = 0 ) then return (0) ;
566 else return ( round(( (C_item_sum_days_qty) / C_item_sum_rec ),P_qty_precision) ) ;
567 end if;
568 RETURN NULL; end;
569 
570 function orderedformula(shipment_conversion_rate in varchar2, pll_quantity_ordered in number) return number is
571 begin
572 
573 /*srw.reference(shipment_conversion_rate) ;*/null;
574 
575 /*srw.reference(pll_quantity_ordered) ;*/null;
576 
577 return (shipment_conversion_rate * pll_quantity_ordered) ;
578 end;
579 
580 function rejectedformula(shipment_conversion_rate in varchar2, pll_quantity_rejected in number) return number is
581 begin
582 
583 /*srw.reference(shipment_conversion_rate) ;*/null;
584 
585 /*srw.reference(pll_quantity_rejected) ;*/null;
586 
587 return (shipment_conversion_rate * pll_quantity_rejected) ;
588 end;
589 
590 function per_rejectedformula(PO_received in number, PO_rejected in number) return number is
591 begin
592 
593 /*srw.reference(PO_received) ;*/null;
594 
595 if (PO_received = 0 ) then return (0) ;
596 else return ( round(( PO_rejected / PO_received ),P_qty_precision+2) * 100 ) ;
597 end if;
598 RETURN NULL; end;
599 
600 function openformula(Received in number, Ordered in number, cutoff_date in date) return number is
601 begin
602 
603 /*srw.reference(Ordered) ;*/null;
604 
605 /*srw.reference(Received) ;*/null;
606 
607 /*srw.reference(promised_date) ;*/null;
608 
609 if (Received >= Ordered) then return 0;
610 else begin
611 	if (trunc(sysdate) > trunc(nvl(cutoff_date,sysdate)))
612 	then return 0;
613 	else return(Ordered - Received) ;
614 	end if;
615 end;
616 end if;
617 
618 RETURN NULL; end;
619 
620 function past_dueformula(Received in number, Ordered in number, cutoff_date in date) return number is
621 begin
622 
623 /*srw.reference(Ordered) ;*/null;
624 
625 /*srw.reference(Received) ;*/null;
626 
627 /*srw.reference(promised_date) ;*/null;
628 
629 if (Received >= Ordered) then return 0;
630 else begin
631 	if (trunc(sysdate) <= trunc(nvl(cutoff_date,sysdate)))
632 	then return 0;
633 	else return(Ordered - Received) ;
634 	end if;
635 end;
636 end if;
637 
638 RETURN NULL; end;
639 
640 function p_openformula(PO_ORDERED in number, PO_RECEIVED in number, PO_OPEN in number) return number is
641 begin
642 
643 /*SRW.REFERENCE(PO_ORDERED) ;*/null;
644 
645 /*SRW.REFERENCE(PO_OPEN) ;*/null;
646 
647 /*SRW.REFERENCE(PO_RECEIVED) ;*/null;
648 
649 IF (PO_ORDERED > PO_RECEIVED) then
650 begin
651 	if (PO_ORDERED = 0 ) then return (0) ;
652 	else return ( round((PO_OPEN / PO_ORDERED ),P_qty_precision+2) * 100 ) ;
653 	end if;
654 end;
655 else begin
656 	if (PO_RECEIVED = 0 ) then return (0) ;
657 	else return ( round((PO_OPEN / PO_RECEIVED ),P_qty_precision+2) * 100 ) ;
658 	end if;
659 end ;
660 end if;
661 
662 RETURN NULL; end;
663 
664 function p_past_dueformula(PO_ORDERED in number, PO_RECEIVED in number, PO_PAST_DUE in number) return number is
665 begin
666 
667 /*SRW.REFERENCE(PO_ORDERED) ;*/null;
668 
669 /*SRW.REFERENCE(PO_PAST_DUE) ;*/null;
670 
671 /*SRW.REFERENCE(PO_RECEIVED) ;*/null;
672 
673 IF (PO_ORDERED > PO_RECEIVED) then
674 begin
675 	if (PO_ORDERED = 0 ) then return (0) ;
676 	else return ( round((PO_PAST_DUE / PO_ORDERED ),P_qty_precision+2) * 100 ) ;
677 	end if;
678 end;
679 else begin
680 	if (PO_RECEIVED = 0 ) then return (0) ;
681 	else return ( round((PO_PAST_DUE / PO_RECEIVED ),P_qty_precision+2) * 100 ) ;
682 	end if;
683 end ;
684 end if;
685 
686 RETURN NULL; end;
687 
688 function c_per_item_openformula(C_item_sum_ord in number, C_item_sum_rec in number, C_item_sum_open in number) return number is
689 begin
690 
691 /*SRW.REFERENCE(C_ITEM_SUM_ORD) ;*/null;
692 
693 /*SRW.REFERENCE(C_ITEM_SUM_REC) ;*/null;
694 
695 /*SRW.REFERENCE(C_ITEM_SUM_OPEN) ;*/null;
696 
697 if (C_item_sum_ord > C_item_sum_rec) then
698 begin
699 	if (C_item_sum_ord = 0 ) then return (0) ;
700 	else return ( round((C_item_sum_open / C_item_sum_ord ),P_qty_precision+2) * 100 ) ;
701 	end if;
702 end;
703 else
704 begin
705 	if (C_item_sum_rec = 0 ) then return (0) ;
706 	else return ( round((C_item_sum_open / C_item_sum_rec ),P_qty_precision+2) * 100 ) ;
707 	end if;
708 end;
709 end if;
710 RETURN NULL; end;
711 
712 function c_per_item_past_dueformula(C_item_sum_ord in number, C_item_sum_rec in number, C_item_sum_past_due in number) return number is
713 begin
714 
715 /*SRW.REFERENCE(C_ITEM_SUM_ORD) ;*/null;
716 
717 /*SRW.REFERENCE(C_ITEM_SUM_REC) ;*/null;
718 
719 /*SRW.REFERENCE(C_ITEM_SUM_PAST_DUE) ;*/null;
720 
721 if (C_item_sum_ord > C_item_sum_rec) then
722 begin
723 	if (C_item_sum_ord = 0 ) then return (0) ;
724 	else return ( round((C_item_sum_past_due / C_item_sum_ord ),P_qty_precision+2) * 100 ) ;
725 	end if;
726 end;
727 else
728 begin
729 	if (C_item_sum_rec = 0 ) then return (0) ;
730 	else return ( round((C_item_sum_past_due / C_item_sum_rec ),P_qty_precision+2) * 100 ) ;
731 	end if;
732 end;
733 end if;
734 RETURN NULL; end;
735 
736 function quantity_received_on_timeformu(quantity_received_total in number, quantity_received_early in number, quantity_received_late in number) return number is
737 begin
738 
739 /*srw.reference(quantity_received_total) ;*/null;
740 
741 /*srw.reference(quantity_received_early) ;*/null;
742 
743 /*srw.reference(quantity_received_late) ;*/null;
744 
745 return (quantity_received_total - quantity_received_early - quantity_received_late );
746 
747 end;
748 
749 function receivedformula(quantity_received_total in number) return number is
750 begin
751 
752 /*srw.reference(quantity_received_total);*/null;
753 
754 return(quantity_received_total);
755 end;
756 
757 function days_total_late_or_earlyformul(days_received_early in number, days_received_late in number) return number is
758 begin
759 
760 /*srw.reference(days_received_early);*/null;
761 
762 /*srw.reference(days_received_late);*/null;
763 
764 
765 return(days_received_early + days_received_late);
766 
767 end;
768 
769 --Functions to refer Oracle report placeholders--
770 
771 END PO_POXSERPR_XMLP_PKG ;
772