1 PACKAGE BODY PO_POXPRIPR_XMLP_PKG AS
2 /* $Header: POXPRIPRB.pls 120.3 2008/01/22 07:43:20 dwkrishn noship $ */
3 USER_EXIT_FAILURE EXCEPTION;
4 function BeforeReport return boolean is
5 begin
6 declare
7 l_sort po_lookup_codes.displayed_field%type ;
8 begin
9 /*SRW.USER_EXIT('FND SRWINIT');*/null;
10 if P_orderby is not null then
11 select displayed_field
12 into l_sort
13 from po_lookup_codes
14 where lookup_code = P_orderby
15 and lookup_type = 'SRS ORDER BY';
16 P_orderby_displayed := l_sort ;
17 if P_orderby = 'ITEM' then
18 P_orderby_clause := 'MSI.SEGMENT1';
19 else
20 P_orderby_clause := 'MCA.SEGMENT1';
21 end if;
22 else
23 P_orderby_displayed := '' ;
24 P_orderby_clause := 'MCA.SEGMENT1';
25 end if;
26 QTY_PRECISION:= po_common_xmlp_pkg.get_precision(P_qty_precision);
27 if P_BASE_CURRENCY is null then P_BASE_CURRENCY:='USD'; end if;
28 if (get_p_struct_num <> TRUE )
29 then /*SRW.MESSAGE('1','Init failed');*/null;
30 end if;
31 LP_orderby_clause:=P_orderby_clause;
32 null;
33 null;
34 null;
35 null;
36 null;
37 null;
38 RETURN TRUE;
39 END;
40 return (TRUE);
41 end;
42 function AfterReport return boolean is
43 begin
44 /*srw.do_sql('alter session set sql_trace=false');*/null;
45 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
46 return (TRUE);
47 end;
48 function ipvformula(Average_Purchase in number, Average_Invoice in number) return number is
49 begin
50 /*srw.reference(Average_Invoice) ;*/null;
51 /*srw.reference(Average_Purchase) ;*/null;
52 if Average_Purchase = 0 then return 0 ;
53 else return (round(((Average_Invoice-Average_Purchase)/Average_Purchase)*100,8)) ;
54 end if;
55 RETURN NULL; end;
56 procedure get_precision is
57 begin
58 /*srw.attr.mask := SRW.FORMATMASK_ATTR;*/null;
59 if P_qty_precision = 0 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0';*/null;
60 else
61 if P_qty_precision = 1 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.0';*/null;
62 else
63 if P_qty_precision = 3 then /*srw.attr.formatmask := '-NN,NNN,NNN,NN0.000';*/null;
64 else
65 if P_qty_precision = 4 then /*srw.attr.formatmask := '-N,NNN,NNN,NN0.0000';*/null;
66 else
67 if P_qty_precision = 5 then /*srw.attr.formatmask := '-NNN,NNN,NN0.00000';*/null;
68 else
69 if P_qty_precision = 6 then /*srw.attr.formatmask := '-NN,NNN,NN0.000000';*/null;
70 else /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.00';*/null;
71 end if; end if; end if; end if; end if; end if;
72 /*srw.set_attr(0,srw.attr);*/null;
73 end;
74 function orderby_clauseFormula return VARCHAR2 is
75 begin
76 if P_orderby = 'ITEM' then
77 return(P_ORDERBY_ITEM);
78 elsif P_orderby = 'CATEGORY' then
79 return(P_ORDERBY_CAT);
80 end if;
81 RETURN NULL; end;
82 function get_p_struct_num return boolean is
83 l_p_struct_num number;
84 begin
85 select structure_id
86 into l_p_struct_num
87 from mtl_default_sets_view
88 where functional_area_id = 2 ;
89 P_STRUCT_NUM1 := l_p_struct_num ;
90 return(TRUE) ;
91 RETURN NULL; exception
92 when others then return(FALSE) ;
93 end;
94 function item_average_purchase_roundfor(item_average_purchase in number, c_fnd_precision in number) return number is
95 begin
96 /*srw.reference(item_average_purchase);*/null;
97 /*SRW.REFERENCE(c_fnd_precision);*/null;
98 return(round(item_average_purchase, c_fnd_precision));
99 end;
100 function item_average_invoice_roundform(item_average_invoice in number, c_fnd_precision in number) return number is
101 begin
102 /*srw.reference(item_average_invoice);*/null;
103 /*SRW.REFERENCE(c_fnd_precision);*/null;
104 return(round(item_average_invoice, c_fnd_precision ));
105 end;
106 function AfterPForm return boolean is
107 begin
108 /*SRW.USER_EXIT('FND SRWINIT');*/null;
109 if (p_period_from is NOT NULL) then
110 select start_date
111 into P_period_start_date
112 from gl_period_statuses gps,
113 financials_system_parameters fps
114 where period_name = P_period_from and
115 gps.set_of_books_id=fps.set_of_books_id and
116 application_id=201;
117 end if;
118 if (p_period_to is NOT NULL) then
119 select end_date
120 into P_period_end_date from gl_period_statuses gps,
121 financials_system_parameters fps
122 where period_name = P_period_to and
123 gps.set_of_books_id=fps.set_of_books_id and
124 application_id=201;
125 end if;
126 if (p_period_from is NOT NULL and p_period_to is NOT NULL ) then
127 period_where:=' trunc(aid.accounting_date) BETWEEN (:P_period_start_date ) AND (:P_period_end_date) ';
128 elsif (p_period_from is NULL and p_period_to is NOT NULL ) then
129 period_where:=' trunc(aid.accounting_date) <= (:P_period_end_date) ';
130 elsif (p_period_from is NOT NULL and p_period_to is NULL ) then
131 period_where:= ' trunc(aid.accounting_date) >= (:P_period_start_date) ';
132 else
133 period_where:=' 1=1 ';
134 end if;
135 return (TRUE);
136 end;
137 function average_purchase_roundformula(AVERAGE_PURCHASE in number, c_fnd_precision in number) return number is
138 begin
139 /*SRW.REFERENCE(AVERAGE_PURCHASE);*/null;
140 /*SRW.REFERENCE(c_fnd_precision);*/null;
141 RETURN(round(AVERAGE_PURCHASE,c_fnd_precision));
142 end;
143 function c_amount_roundformula(C_AMOUNT in number, c_fnd_precision in number) return number is
144 begin
145 /*SRW.REFERENCE(C_AMOUNT);*/null;
146 /*SRW.REFERENCE(c_fnd_precision);*/null;
147 RETURN(round(C_AMOUNT,c_fnd_precision));
148 end;
149 function average_invoice_roundformula(AVERAGE_INVOICE in number, c_fnd_precision in number) return number is
150 begin
151 /*SRW.REFERENCE(AVERAGE_INVOICE);*/null;
152 /*SRW.REFERENCE(c_fnd_precision);*/null;
153 RETURN(round(AVERAGE_INVOICE,c_fnd_precision));
154 end;
155 function c_amount_tot_roundformula(C_AMOUNT_TOT in number, c_fnd_precision in number) return number is
156 begin
157 /*SRW.REFERENCE(C_AMOUNT_TOT);*/null;
158 /*SRW.REFERENCE(c_fnd_precision);*/null;
159 RETURN(round(C_AMOUNT_TOT,c_fnd_precision));
160 end;
161 function rateformula(Unit_Of_measure in varchar2, Unit in varchar2, Item_id in number) return number is
162 begin
163 if ( Unit_Of_measure <> Unit ) then
164 return(PO_UOM_S.po_uom_convert(Unit_of_Measure, Unit, Item_id ));
165 else
166 return (1);
167 end if;
168 end;
169 function po_primary_qtyformula(PO_Quantity in number, conv_Rate in number) return number is
170 begin
171 return(round((PO_Quantity * conv_Rate),P_qty_precision));
172 end;
173 function ap_primary_qtyformula(AP_Quantity in number, conv_Rate in number) return number is
174 begin
175 return(round((AP_Quantity * conv_Rate),P_qty_precision));
176 end;
177 function average_invoiceformula(Q_Invoiced in number, Total_amount_Invoiced in number) return number is
178 begin
179 if Q_Invoiced = 0 then
180 return (0);
181 else
182 return (Total_amount_Invoiced/Q_Invoiced);
183 end if;
184 end;
185 function average_purchaseformula(C_amount in number, Q_Purchased in number) return number is
186 begin
187 if Q_Purchased = 0 then
188 return (0);
189 else
190 return (C_amount/Q_Purchased);
191 end if;
192 end;
193 --Functions to refer Oracle report placeholders--
194 END PO_POXPRIPR_XMLP_PKG ;
195