[Home] [Help]
PACKAGE BODY: APPS.PO_POXRRCVV_XMLP_PKG
Source
1 PACKAGE BODY PO_POXRRCVV_XMLP_PKG AS
2 /* $Header: POXRRCVVB.pls 120.2 2007/12/25 13:27:29 krreddy noship $ */
3
4 function AfterReport return boolean is
5 begin
6
7 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
8
9
10 /*SRW.MESSAGE(1, 'Report finished ' ||
11 TO_CHAR(sysdate, 'fmMonth DD YYYY, HH:MIam'));*/null;
12
13 --raise_application_error(-20001,'After report');
14 return (TRUE);
15 end;
16
17 function BeforeReport return boolean is
18 begin
19
20 /*srw.message(1,'before report');*/null;
21
22 DECLARE
23 L_ORGANIZATION VARCHAR2(60);
24 L_EXT_PREC NUMBER;
25 L_ROUND_UNIT NUMBER;
26 L_SORT_BY VARCHAR2(80);
27 L_CAT_SET_NAME VARCHAR2(30);
28 L_DEF_COST_TYPE NUMBER;
29 L_PRIMARY_COST_METHOD NUMBER;
30 L_COST_TYPE VARCHAR2(30);
31 L_DETAIL_LEVEL VARCHAR2(80);
32 L_DOCUMENT_TYPE_DISPLAYED VARCHAR2(80) ;
33 L_FCN_CURRENCY VARCHAR2(15);
34 invalid_option EXCEPTION;
35 l_stmt_num NUMBER;
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(8000);
38 l_return_status VARCHAR2(1);
39 l_as_of_date VARCHAR2(30);
40 l_cst_inv_val EXCEPTION;
41 l_tmp number;-- tmp
42
43 BEGIN
44 l_stmt_num := 0;
45
46
47
48
49
50 begin
51 select primary_cost_method
52 into l_primary_cost_method
53 from mtl_parameters
54 where organization_id = P_org_id ;
55
56 P_COST_TYPE_ID := l_primary_cost_method ;
57 end;
58 if P_SORT_OPTION is null then
59 P_SORT_OPTION:= 'ITEM';
60 end if;
61 l_stmt_num :=2;
62 IF (P_SORT_OPTION = 'ITEM' ) THEN P_SORT_OPTION1 := 1;
63 ELSIF (P_SORT_OPTION = 'CATEGORY' ) THEN P_SORT_OPTION1 := 2;
64 ELSIF (P_SORT_OPTION = 'LOCATION' ) THEN P_SORT_OPTION1 := 3;
65 ELSIF (P_SORT_OPTION = 'VENDOR' ) THEN P_SORT_OPTION1 := 4;
66 ELSE
67 RAISE_application_error(-20101,'Invalid Sort Option');/*SRW.PROGRAM_ABORT;*/null;
68
69 END IF;
70
71 SELECT PLC.DISPLAYED_FIELD
72 INTO L_SORT_BY
73 FROM PO_LOOKUP_CODES PLC
74 WHERE PLC.LOOKUP_TYPE (+) = 'SRS ORDER BY'
75 AND PLC.LOOKUP_CODE (+) = P_SORT_OPTION ;
76
77 P_SORT_HEADER_DISPLAYED := L_SORT_BY;
78
79
80
81
82 l_stmt_num :=20;
83
84 SELECT O.ORGANIZATION_NAME,
85 NVL(EXTENDED_PRECISION, PRECISION),
86 NVL(MINIMUM_ACCOUNTABLE_UNIT, POWER(10,NVL(-PRECISION,0))),
87 MCS.CATEGORY_SET_NAME,
88 DEFAULT_COST_TYPE_ID,
89 COST_TYPE,
90 PLC.DISPLAYED_FIELD,
91 LU2.MEANING
92 INTO L_ORGANIZATION,
93 L_EXT_PREC,
94 L_ROUND_UNIT,
95 L_CAT_SET_NAME,
96 L_DEF_COST_TYPE,
97 L_COST_TYPE,
98 L_SORT_BY,
99 L_DETAIL_LEVEL
100 FROM ORG_ORGANIZATION_DEFINITIONS O,
101 FND_CURRENCIES FC,
102 MTL_CATEGORY_SETS MCS,
103 CST_COST_TYPES,
104 PO_LOOKUP_CODES PLC,
105 MFG_LOOKUPS LU2
106 WHERE FC.CURRENCY_CODE = P_CURRENCY_CODE
107 AND O.ORGANIZATION_ID = P_ORG_ID
108 AND MCS.CATEGORY_SET_ID = P_CATEGORY_SET
109 AND COST_TYPE_ID = P_COST_TYPE_ID
110 AND PLC.LOOKUP_TYPE (+) = 'SRS ORDER BY'
111 AND PLC.LOOKUP_CODE (+) = P_SORT_OPTION
112 AND LU2.LOOKUP_TYPE (+) = 'CST_BICR_DETAIL_OPTION'
113 AND LU2.LOOKUP_CODE (+) = P_RPT_OPTION;
114
115 l_stmt_num := 30;
116 P_ORGANIZATION := L_ORGANIZATION;
117 ROUND_UNIT := L_ROUND_UNIT;
118 P_CAT_SET_NAME := L_CAT_SET_NAME;
119 P_SORT_BY := L_SORT_BY;
120 P_DETAIL_LEVEL := L_DETAIL_LEVEL;
121 P_DEF_COST_TYPE := L_DEF_COST_TYPE;
122 P_COST_TYPE := L_COST_TYPE;
123 P_DOCUMENT_TYPE_DISPLAYED := '' ;
124
125
126 l_stmt_num := 40;
127 /*SRW.USER_EXIT('FND SRWINIT');*/null;
128
129
130
131
132 SELECT currency_code
133 INTO l_fcn_currency
134 FROM cst_organization_definitions cod
135 WHERE cod.organization_id = P_org_id;
136
137 l_stmt_num := 50;
138 IF L_FCN_CURRENCY = P_CURRENCY_CODE THEN
139 P_CURRENCY_DSP := P_CURRENCY_CODE;
140 ELSE
141 P_CURRENCY_DSP := P_CURRENCY_CODE || ' @ ' ||
142 TO_CHAR(ROUND(1/P_EXCHANGE_RATE,5)) || L_FCN_CURRENCY;
143 END IF;
144
145 l_stmt_num := 70;
146
147 null;
148
149
150 l_stmt_num := 80;
151
152 null;
153
154 l_stmt_num := 120;
155 CST_Inventory_PUB.Calculate_InventoryValue(
156 p_api_version => 1.0,
157 p_init_msg_list => CST_Utility_PUB.get_true,
158 p_organization_id => P_ORG_ID,
159 p_onhand_value => 0,
160 p_intransit_value => 0,
161 p_receiving_value => 1,
162 p_valuation_date => to_Date(P_AS_OF_DATE,'YYYY/MM/DD HH24:MI:SS'),
163 p_cost_type_id => P_COST_TYPE_ID,
164 p_item_from => P_ITEM_FROM,
165 p_item_to => P_ITEM_TO,
166 p_category_set_id => P_CATEGORY_SET,
167 p_category_from => P_CAT_FROM,
168 p_category_to => P_CAT_TO,
169 p_cost_group_from => NULL,
170 p_cost_group_to => NULL,
171 p_subinventory_from => NULL,
172 p_subinventory_to => NULL,
173 p_qty_by_revision => P_ITEM_REVISION,
174 p_zero_cost_only => 0,
175 p_zero_qty => NULL,
176 p_expense_item => NULL,
177 p_expense_sub => NULL,
178 p_unvalued_txns => NULL,
179 p_receipt => NULL,
180 p_shipment => NULL,
181 p_own => 1,
182 p_detail => NULL,
183 p_cost_enabled_only => 0,
184 p_one_time_item => P_ONE_TIME,
185 p_include_period_end => P_PERIOD_END,
186 x_return_status => l_return_status,
187 x_msg_count => l_msg_count,
188 x_msg_data => l_msg_data
189 );
190
191 l_stmt_num := 130;
192 IF l_return_status <> CST_Utility_PUB.get_ret_sts_success
193 THEN
194 RAISE l_cst_inv_val;
195 END IF;
196
197 l_stmt_num := 140;
198 FND_MSG_PUB.count_and_get(
199 p_encoded => CST_Utility_PUB.get_false,
200 p_count => l_msg_count,
201 p_data => l_msg_data
202 );
203
204 l_stmt_num := 150;
205 IF l_msg_count > 0
206 THEN
207 FOR i IN 1 ..l_msg_count
208 LOOP
209 l_msg_data := FND_MSG_PUB.get(i, CST_Utility_PUB.get_false);
210 FND_FILE.PUT_LINE(CST_Utility_PUB.get_log, i ||'-'||l_msg_data);
211 END LOOP;
212 END IF;
213
214 select to_char(to_date(P_AS_OF_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
215 into l_as_of_date
216 from dual;
217
218 P_AS_OF_DATE1 :=l_as_of_date;
219 FORMAT_MASK := PO_COMMON_XMLP_PKG.GET_PRECISION(P_qty_precision);
220
221 /*SRW.MESSAGE(0, 'Report started ' ||
222 TO_CHAR(sysdate, 'fmMonth DD YYYY, HH:MIam'));*/null;
223
224
225 EXCEPTION
226 WHEN invalid_option THEN
227 raise_application_error(-20101,null);null;
228
229 WHEN OTHERS THEN
230 raise_application_error(-20101,SQLERRM);
231
232
233 FND_MSG_PUB.count_and_get(
234 p_encoded => CST_Utility_PUB.get_false,
235 p_count => l_msg_count,
236 p_data => l_msg_data
237 );
238 IF l_msg_count > 0 THEN
239 FOR i IN 1 ..l_msg_count
240 LOOP
241 l_msg_data := FND_MSG_PUB.get(i, CST_Utility_PUB.get_false);
242 FND_FILE.PUT_LINE(CST_Utility_PUB.get_log, i ||'-'||l_msg_data);
243 END LOOP;
244 END IF;
245 -- RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
246
247
248 END;
249 return (TRUE);
250 end;
251
252 function itemcatformula(CATEGORY_PSEG in varchar2) return varchar2 is
253 begin
254
255 /*SRW.REFERENCE(CATEGORY);*/null;
256
257 /*SRW.REFERENCE(CATEGORY_SEGMENT);*/null;
258
259 /*SRW.REFERENCE(CATEGORY_PSEG);*/null;
260
261 IF P_SORT_OPTION1 = 2 OR P_SORT_OPTION1 = 6 THEN
262 RETURN(CATEGORY_PSEG);
263 ELSE
264 RETURN('I have absolutely no idea why I am doing this');
265 END IF;
266 RETURN NULL; end;
267
268 function comp_avg_unit_price (ITEM_QUANTITY in number, ITEM_TOTAL_PUR_VALUE in number, c_ext_precision in number) return number is
269 comp NUMBER;
270 BEGIN
271 if ITEM_QUANTITY > 0 then
272 comp := ROUND(nvl(ITEM_TOTAL_PUR_VALUE,0) /
273 ITEM_QUANTITY,c_ext_precision);
274 else
275 comp := ROUND(nvl(ITEM_TOTAL_PUR_VALUE,0),
276 c_ext_precision);
277 end if;
278 return (comp);
279 end;
280
281 procedure get_precision1 is
282 begin
283 /*srw.attr.mask := SRW.FORMATMASK_ATTR;*/null;
284
285 if P_qty_precision = 0 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0';*/null;
286
287 else
288 if P_qty_precision = 1 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.0';*/null;
289
290 else
291 if P_qty_precision = 3 then /*srw.attr.formatmask := '-NN,NNN,NNN,NN0.000';*/null;
292
293 else
294 if P_qty_precision = 4 then /*srw.attr.formatmask := '-N,NNN,NNN,NN0.0000';*/null;
295
296 else
297 if P_qty_precision = 5 then /*srw.attr.formatmask := '-NNN,NNN,NN0.00000';*/null;
298
299 else
300 if P_qty_precision = 6 then /*srw.attr.formatmask := '-NN,NNN,NN0.000000';*/null;
301
302 else /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.00';*/null;
303
304 end if; end if; end if; end if; end if; end if;
305 /*srw.set_attr(0,srw.attr);*/null;
306
307 end;
308
309 function total_pur_valueformula(total_purchase_value in number) return number is
310 begin
311
312 return total_purchase_value;
313 end;
314
315 function c_quantityformula(quantity in number) return number is
316 begin
317 return round(quantity, P_qty_precision);
318 end;
319
320 function c_total_pur_valueformula(total_pur_value in number) return number is
321 begin
322 return round(total_pur_value*p_exchange_rate/round_unit)*round_unit;
323 end;
324
325 function CF_SORT_HEADER_DISPLAYEDFormul return Char is
326 begin
327 return p_sort_header_displayed;
328 end;
329
330 function CF_cat_range_dispFormula return Char is
331 begin
332 if ((P_CAT_FROM IS NOT NULL) OR (P_CAT_TO IS NOT NULL)) then
333 return 'Y';
334 else
335 return 'N';
336 end if;
337 end;
338
339 function CF_item_range_dispFormula return Char is
340 begin
341 if ((P_ITEM_FROM IS NOT NULL) OR (P_ITEM_TO IS NOT NULL)) then
342 return 'Y';
343 else
344 return 'N';
345 end if;
346 end;
347
348 function cf_item_cost_dispformula(SORT_COLUMN in varchar2) return char is
349 begin
350 if ((P_SORT_OPTION1 = 5 OR P_SORT_OPTION1 = 6) AND (SORT_COLUMN = 'Expense')) THEN
351 return 'N';
352 end if;
353 return 'Y';
354 end;
355
356 function CF_MAIN_DISPFormula return Char is
357 begin
358 if (P_RPT_OPTION = 1) then
359 return 'Y';
360 else
361 return 'N';
362 end if;
363 end;
364
365 function CF_REV_DISPFormula return Char is
366 begin
367 if (P_ITEM_REVISION = 1) then
368 return 'Y';
369 else
370 return 'N';
371 end if;
372 end;
373
374 function CF_CAT_DISPFormula return Char is
375 begin
376 if (P_SORT_OPTION1 = 2 OR P_SORT_OPTION1 = 6) then
377 return 'Y';
378 else
379 return 'N';
380 end if;
381 end;
382
383 function CF_cat_fromFormula return Char is
384 begin
385 return p_cat_from;
386 end;
387
388 function CF_cat_toFormula return Char is
389 begin
390 return p_cat_to;
391 end;
392
393 function CF_cost_typeFormula return Char is
394 begin
395 return p_cost_type;
396 end;
397
398 function CF_currency_dspFormula return Char is
399 begin
400 return p_currency_dsp;
401 end;
402
403 function CF_detail_levelFormula return Char is
404 begin
405 return p_detail_level;
406 end;
407
408 function CF_item_fromFormula return Char is
409 begin
410 return p_item_from;
411 end;
412
413 function CF_item_toFormula return Char is
414 begin
415 return p_item_to;
416 end;
417
418 function CF_titleFormula return Char is
419 begin
420 return p_title;
421 end;
422
423 function CF_cat_set_nameFormula return Char is
424 begin
425 return p_cat_set_name;
426 end;
427
428 function CF_SORT_DISPFormula return Char is
429 begin
430 if (P_SORT_OPTION1 > 2) then
431 return 'Y';
432 else
433 return 'N';
434 end if;
435 end;
436
437 --Functions to refer Oracle report placeholders--
438
439 END PO_POXRRCVV_XMLP_PKG ;
440