[Home] [Help]
PACKAGE BODY: APPS.PO_POXPOBPS_XMLP_PKG
Source
1 PACKAGE BODY PO_POXPOBPS_XMLP_PKG AS
2 /* $Header: POXPOBPSB.pls 120.1 2007/12/25 11:08:38 krreddy noship $ */
3
4 USER_EXIT_FAILURE EXCEPTION;
5
6 function BeforeReport return boolean is
7 begin
8
9 DECLARE
10 l_sort po_lookup_codes.displayed_field%type;
11 BEGIN
12 /*SRW.USER_EXIT('FND SRWINIT');*/null;
13
14 QTY_PRECISION:=PO_COMMON_XMLP_PKG.GET_PRECISION(P_qty_precision);
15 IF P_ORDERBY is not null THEN
16 SELECT displayed_field
17 INTO l_sort
18 FROM po_lookup_codes
19 WHERE lookup_code = P_ORDERBY
20 AND lookup_type = 'SRS ORDER BY';
21
22 P_ORDERBY_DISPLAYED := l_sort;
23
24 ELSE
25
26 P_ORDERBY_DISPLAYED := '';
27
28 END IF;
29
30 EXCEPTION WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
31 /*SRW.MESSAGE(1,'srw_init');*/null;
32
33 END;
34 BEGIN
35 if (get_p_struct_num <> TRUE )
36 then /*SRW.MESSAGE('1','P Struct Num Init failed');*/null;
37
38 end if;
39 END;
40 BEGIN
41
42 null;
43 EXCEPTION WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
44 /*SRW.MESSAGE(1,'Before Item Flex');*/null;
45
46 END;
47 BEGIN
48
49 null;
50 EXCEPTION WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
51 /*SRW.MESSAGE(1,'Before Cat Flex');*/null;
52
53 END;
54 BEGIN
55
56 null;
57 EXCEPTION WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
58 /*SRW.MESSAGE(1,'Before Category Where');*/null;
59
60 END;
61 RETURN TRUE; return (TRUE);
62 end;
63
64 function AfterReport return boolean is
65 begin
66
67 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
68
69 RETURN TRUE; return (TRUE);
70 end;
71
72 procedure get_precision is
73 begin
74 /*srw.attr.mask := SRW.FORMATMASK_ATTR;*/null;
75
76 if P_qty_precision = 0 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0';*/null;
77
78 else
79 if P_qty_precision = 1 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.0';*/null;
80
81 else
82 if P_qty_precision = 3 then /*srw.attr.formatmask := '-NN,NNN,NNN,NN0.000';*/null;
83
84 else
85 if P_qty_precision = 4 then /*srw.attr.formatmask := '-N,NNN,NNN,NN0.0000';*/null;
86
87 else
88 if P_qty_precision = 5 then /*srw.attr.formatmask := '-NNN,NNN,NN0.00000';*/null;
89
90 else
91 if P_qty_precision = 6 then /*srw.attr.formatmask := '-NN,NNN,NN0.000000';*/null;
92
93 else /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.00';*/null;
94
95 end if; end if; end if; end if; end if; end if;
96 /*srw.set_attr(0,srw.attr);*/null;
97
98 end;
99
100 function orderby_clauseFormula return VARCHAR2 is
101 begin
102
103 if upper(P_ORDERBY) = 'VENDOR'
104 then return('pov.vendor_name');
105 elsif upper(P_ORDERBY) = 'PO NUMBER'
106 then return('decode(psp1.manual_po_num_type, ''NUMERIC'',
107 null, poh.segment1),
108 decode(psp1.manual_po_num_type, ''NUMERIC'',
109 to_number(poh.segment1), null)');
110 end if;
111 RETURN 'decode(psp1.manual_po_num_type,''NUMERIC'',null,poh.segment1), decode(psp1.manual_po_num_type,''NUMERIC'',to_number(poh.segment1),null)';
112 end;
113
114 function get_p_struct_num return boolean is
115
116 l_p_struct_num number;
117
118 begin
119 select structure_id
120 into l_p_struct_num
121 from mtl_default_sets_view
122 where functional_area_id = 2 ;
123
124 P_STRUCT_NUM := l_p_struct_num ;
125
126 return(TRUE) ;
127
128 RETURN NULL; exception
129 when others then return(FALSE) ;
130 end;
131
132 function cur_planned_amt_agreed(PO_type in varchar2, po_header_id1 in number) return number is
133
134 X_TOTAL NUMBER;
135 BEGIN
136
137 /*SRW.REFERENCE(PO_TYPE);*/null;
138
139
140 IF PO_type = 'PLANNED' THEN
141
142 SELECT SUM( (nvl(PLL.quantity,0) -
143 nvl(PLL.quantity_cancelled,0) ) *
144 nvl(PLL.price_override,0))
145 INTO X_TOTAL
146 FROM PO_LINE_LOCATIONS PLL
147 WHERE PLL.po_header_id = po_header_id1
148 AND PLL.shipment_type = 'PLANNED';
149
150 ELSE
151 X_TOTAL:= 0;
152 END IF;
153
154 IF X_TOTAL IS NOT NULL THEN
155 RETURN(X_TOTAL);
156 ELSE
157 RETURN(0);
158 END IF;
159
160 RETURN NULL; EXCEPTION
161
162 WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
163 RETURN(0);
164 END;
165
166 function cur_planned_amt_released(PO_type in varchar2, PO_HEADER_ID1 in number) return number is
167
168 X_TOTAL NUMBER;
169 BEGIN
170
171
172 /*SRW.REFERENCE(PO_TYPE);*/null;
173
174
175 IF PO_type = 'PLANNED' THEN
176
177
178 SELECT sum((pll.quantity -
179 nvl(pll.quantity_cancelled,0))*
180 nvl(pll.price_override,0))
181 INTO X_TOTAL
182 FROM PO_LINE_LOCATIONS PLL
183 WHERE PLL.po_header_id = PO_HEADER_ID1
184 AND PLL.shipment_type = 'SCHEDULED';
185
186
187 ELSE
188 X_TOTAL:= 0;
189 END IF;
190
191 IF X_TOTAL IS NOT NULL THEN
192 RETURN(X_TOTAL);
193 ELSE
194 RETURN(0);
195 END IF;
196
197 RETURN NULL; EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 RETURN(0);
200
201 WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
202 RETURN(0);
203 END;
204
205 function c_amount_rel(po_header_id1 in number) return number is
206
207 X_RELEASED_AMT NUMBER;
208 X_GA_FLAG VARCHAR2(1);
209
210 BEGIN
211
212 /*srw.reference(PO_HEADER_ID1);*/null;
213
214
215 SELECT nvl(global_agreement_flag,'N')
216 INTO X_GA_FLAG
217 FROM po_headers_all
218 WHERE po_header_id = po_header_id1;
219
220 IF X_GA_FLAG = 'N' THEN
221
222
223 SELECT SUM (DECODE (POL.order_type_lookup_code,
224 'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
225 'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
226 (NVL(PLL.quantity, 0) - NVL(PLL.quantity_cancelled, 0))
227 * NVL(PLL.price_override, 0)))
228 INTO X_RELEASED_AMT
229 FROM po_line_locations pll,
230 po_headers poh,
231 po_lines POL
232 WHERE poh.po_header_id = POL.po_header_id
233 AND POL.po_line_id = PLL.po_line_id
234 AND pll.shipment_type not in ('PRICE BREAK')
235 AND poh.po_header_id = po_header_id1;
236
237 ELSE
238
239
240
241 SELECT SUM (DECODE (POL.order_type_lookup_code,
242 'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
243 'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
244 (NVL(PLL.quantity, 0) - NVL(PLL.quantity_cancelled, 0))
245 * NVL(PLL.price_override, 0)))
246 INTO X_RELEASED_AMT
247 FROM po_line_locations_all pll,
248 po_headers_all poh,
249 po_lines_all POL
250 WHERE poh.po_header_id = pll.from_header_id
251 AND POH.po_header_id = POL.po_header_id
252 AND POL.po_line_id = PLL.from_line_id
253 AND pll.shipment_type not in ('PRICE BREAK')
254 AND poh.po_header_id = po_header_id1;
255
256
257
258 END IF;
259
260 IF X_RELEASED_AMT IS NOT NULL THEN
261 RETURN(X_RELEASED_AMT);
262 ELSE
263 RETURN(0);
264 END IF;
265
266 EXCEPTION
267
268 WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
269 RETURN(0);
270
271 end;
272
273 function c_amount_rem(po_header_id1 in number) return number is
274
275 X_REMAIN_AMT NUMBER;
276 l_ga_flag varchar2(25);
277
278
279
280 BEGIN
281
282
283 /*srw.reference(PO_HEADER_ID1);*/null;
284
285 begin
286
287 select global_agreement_flag into l_ga_flag
288 from po_headers
289 where po_header_id=po_header_id1;
290 exception
291 when others then
292 null;
293 end;
294 if(l_ga_flag='Y')then
295
296
297 begin
298 SELECT (min(poh.blanket_total_amount)- sum( round(
299 (decode (pol.quantity, null, (pod.amount_ordered - pod.amount_cancelled),
300 (( pod.quantity_ordered - pod.quantity_cancelled ) * poll.price_override)))
301 ))) REMAIN into x_remain_amt
302 FROM po_distributions_all pod, po_line_locations_all poll, po_lines_all pol,po_headers poh
303 WHERE pod.line_location_id = poll.line_location_id AND
304 poll.po_line_id = pol.po_line_id AND
305 pol.from_header_id =po_header_id1
306 and poh.po_header_id=po_header_id1;
307
308 exception
309 when others then
310 null;
311 end;
312
313
314
315 else
316
317 SELECT (MIN(POH.blanket_total_amount) -
318 SUM( DECODE (POL.order_type_lookup_code,
319 'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
320 'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
321 (nvl(pll.quantity,0) - nvl(pll.quantity_cancelled,0))
322 * nvl(price_override,0)))) REMAIN
323 INTO X_REMAIN_AMT
324 FROM po_line_locations pll
325 , po_headers poh
326 , po_lines POL
327 WHERE poh.po_header_id = POL.po_header_id(+)
328 AND POL.po_line_id = PLL.po_line_id (+)
329 AND pll.shipment_type not in ('PRICE BREAK')
330 AND poh.po_header_id = po_header_id1;
331 end if;
332
333 IF X_REMAIN_AMT IS NOT NULL AND X_REMAIN_AMT >=0 THEN
334 RETURN(X_REMAIN_AMT);
335 ELSE
336 RETURN(0);
337 END IF;
338
339 EXCEPTION
340
341 WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
342 RETURN(0);
343 end;
344
345 function c_po_relformula(global_agreement_flag in varchar2, std_po in varchar2, Release in number) return char is
346
347 begin
348
349 /*srw.reference(global_agreement_flag);*/null;
350
351
352
353 IF nvl(global_agreement_flag,'N') = 'Y' THEN
354 RETURN(std_po);
355 ELSE
356 RETURN(to_number(Release));
357 END IF;
358
359 end;
360
361 function c_org_nameformula(po_org_id in number, global_agreement_flag in varchar2) return char is
362 X_ORG_NAME varchar2(240);
363 begin
364
365 /*srw.reference(global_agreement_flag);*/null;
366
367 /*srw.reference(po_org_id);*/null;
368
369
370 SELECT name
371 INTO X_ORG_NAME
372 FROM hr_organization_units
373 WHERE organization_id = po_org_id;
374
375 IF nvl(global_agreement_flag,'N') = 'Y' THEN
376 RETURN(X_ORG_NAME);
377 ELSE
378 RETURN(null);
379 END IF;
380 end;
381
382 --Functions to refer Oracle report placeholders--
383
384 END PO_POXPOBPS_XMLP_PKG ;
385