1 PACKAGE BODY PO_POXRRVDR_XMLP_PKG AS
2 /* $Header: POXRRVDRB.pls 120.1.12010000.5 2008/11/30 10:19:12 mpuranik ship $ */
3 USER_EXIT_FAILURE EXCEPTION;
4
5 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
6 BEGIN
7 DECLARE
8 L_ORG ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
9 L_CURRENCY_CODE VARCHAR2(20);
10 RECEIPT_NUMBERING_TYPE VARCHAR2(240);
11 BEGIN
12 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
13 QTY_PRECISION:=PO_COMMON_xmlp_pkg.GET_PRECISION(P_QTY_PRECISION);
14 IF P_ORG_ID IS NOT NULL THEN
15 SELECT
16 ORGANIZATION_NAME
17 INTO
18 L_ORG
19 FROM
20 ORG_ORGANIZATION_DEFINITIONS
21 WHERE ORGANIZATION_ID = P_ORG_ID;
22 P_ORG_DISPLAYED := L_ORG;
23 ELSE
24 P_ORG_DISPLAYED := '';
25 END IF;
26 SELECT
27 CURRENCY_CODE
28 INTO
29 L_CURRENCY_CODE
30 FROM
31 GL_SETS_OF_BOOKS GSB,
32 FINANCIALS_SYSTEM_PARAMETERS FSP
33 WHERE GSB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID;
34 P_BASE_CURRENCY := L_CURRENCY_CODE;
35 IF (P_SORT_OPTION = 'ACCOUNT AND ITEM') THEN
36 P_SORT_ID := 1;
37 ELSIF (P_SORT_OPTION = 'ACCOUNT AND VENDOR') THEN
38 P_SORT_ID := 2;
39 ELSIF (P_SORT_OPTION = 'ACCOUNT AND RECEIPT') THEN
40 P_SORT_ID := 3;
41 ELSE
42 P_SORT_ID := 1;
43 END IF;
44
45 P_ACCT_DATE_FROM1 := to_char(P_DATE_FROM,'dd-mon-yy');
46 P_ACCT_DATE_TO1 := to_char(P_DATE_TO,'dd-mon-yy');
47
48
49 P_ACCT_DATE_FROM := P_DATE_FROM;
50 P_ACCT_DATE_TO := P_DATE_TO;
51 P_CAT_SET_ID := P_CATEGORY_SET;
52 IF (P_ACCT_DATE_FROM IS NOT NULL) THEN
53 P_WHERE_ACCOUNTING_DATE_FROM := 'rdv.accounting_date >= ' || '''' || TO_CHAR(P_ACCT_DATE_FROM) || '''';
54 ELSE
55 P_WHERE_ACCOUNTING_DATE_FROM := '1=1';
56 END IF;
57 IF (P_ACCT_DATE_TO IS NOT NULL) THEN
58 P_WHERE_ACCOUNTING_DATE_TO := 'rdv.accounting_date <= ' || '''' || TO_CHAR(P_ACCT_DATE_TO) || '''';
59 ELSE
60 P_WHERE_ACCOUNTING_DATE_TO := '1=1';
61 END IF;
62 IF (P_ACCT_DATE_FROM IS NOT NULL) THEN
63 P_WHERE_TRX_DATE_FROM := 'rdv.transaction_date >= ' || '''' || TO_CHAR(P_ACCT_DATE_FROM - 1) || '''';
64 ELSE
65 P_WHERE_TRX_DATE_FROM := '1=1';
66 END IF;
67 IF (P_ACCT_DATE_TO IS NOT NULL) THEN
68 P_WHERE_TRX_DATE_TO := 'rdv.transaction_date <= ' || '''' || TO_CHAR(P_ACCT_DATE_TO + 1) || '''';
69 ELSE
70 P_WHERE_TRX_DATE_TO := '1=1';
71 END IF;
72 IF (P_VENDOR_FROM IS NOT NULL) THEN
73 IF (P_VENDOR_TO IS NOT NULL) THEN
74 P_WHERE_VENDOR := 'pov.vendor_name BETWEEN ''' || P_VENDOR_FROM || ''' AND ''' || P_VENDOR_TO || '''';
75 ELSE
76 P_WHERE_VENDOR := 'pov.vendor_name >= ''' || P_VENDOR_FROM || '''';
77 END IF;
78 ELSE
79 IF (P_VENDOR_TO IS NOT NULL) THEN
80 P_WHERE_VENDOR := 'pov.vendor_name <= ''' || P_VENDOR_TO || '''';
81 ELSE
82 P_WHERE_VENDOR := '1=1';
83 END IF;
84 END IF;
85 END;
86 BEGIN
87 RETURN TRUE;
88 END;
89 RETURN (TRUE);
90 END BEFOREREPORT;
91
92 FUNCTION C_FLEX_ACC_DESCFORMULA(C_FLEX_ACC_DESC IN VARCHAR2) RETURN VARCHAR2 IS
93 BEGIN
94 RETURN (C_FLEX_ACC_DESC);
95 END C_FLEX_ACC_DESCFORMULA;
96
97 FUNCTION CALC_TRX_VALUE(CURRENCY_CODE IN VARCHAR2
98 ,C_PRECISION IN NUMBER
99 ,EVENT_TYPE_ID IN NUMBER
100 ,C_PRIMARY_UNIT_PRICE1 IN NUMBER
101 ,PRIMARY_QTY IN NUMBER
102 ,CURR_CONV_RATE IN NUMBER
103 /* Support for Landed Cost Management */
104 ,PRIOR_UNIT_PRICE IN NUMBER
105 ,PRIMARY_LANDED_COST NUMBER
106 ,ACCOUNTING_LINE_TYPE VARCHAR2) RETURN NUMBER IS
107 L_DOC_CURR_PRECISION NUMBER;
108 BEGIN
109 BEGIN
110 SELECT
111 PRECISION
112 INTO
113 L_DOC_CURR_PRECISION
114 FROM
115 FND_CURRENCIES
116 WHERE CURRENCY_CODE = CURRENCY_CODE;
117 EXCEPTION
118 WHEN OTHERS THEN
119 L_DOC_CURR_PRECISION := C_PRECISION;
120 END;
121
122 /* Support for Landed Cost Management */
123 IF EVENT_TYPE_ID IN (7,8) THEN
124 RETURN (ROUND(ROUND(ROUND((C_PRIMARY_UNIT_PRICE1 * PRIMARY_QTY)
125 ,L_DOC_CURR_PRECISION) * CURR_CONV_RATE
126 ,C_PRECISION) - ROUND(ROUND((PRIOR_UNIT_PRICE * PRIMARY_QTY)
127 ,L_DOC_CURR_PRECISION) * CURR_CONV_RATE
128 ,C_PRECISION)
129 ,C_PRECISION));
130
131 ELSIF EVENT_TYPE_ID IN (15,16,17) THEN
132 RETURN (ROUND(ROUND(ROUND((C_PRIMARY_UNIT_PRICE1 * PRIMARY_QTY)
133 ,L_DOC_CURR_PRECISION)
134 ,C_PRECISION) - ROUND(ROUND((PRIOR_UNIT_PRICE * PRIMARY_QTY)
135 ,L_DOC_CURR_PRECISION)
136 ,C_PRECISION)
137 ,C_PRECISION));
138
139 ELSIF PRIMARY_LANDED_COST IS NOT NULL THEN
140 IF ACCOUNTING_LINE_TYPE = 'Receiving Inspection' THEN
141 RETURN (ROUND(ROUND((PRIMARY_LANDED_COST * PRIMARY_QTY)
142 ,L_DOC_CURR_PRECISION)
143 ,C_PRECISION));
144
145 ELSIF ACCOUNTING_LINE_TYPE = 'Accrual' THEN
146 RETURN (ROUND(ROUND((C_PRIMARY_UNIT_PRICE1 * PRIMARY_QTY)
147 ,L_DOC_CURR_PRECISION) * CURR_CONV_RATE
148 ,C_PRECISION));
149
150 ELSIF ACCOUNTING_LINE_TYPE = 'Landed Cost Absorption' THEN
151 RETURN (ROUND(ROUND(ROUND((C_PRIMARY_UNIT_PRICE1 * PRIMARY_QTY)
152 ,L_DOC_CURR_PRECISION) * CURR_CONV_RATE
153 ,C_PRECISION) - ROUND(ROUND((PRIMARY_LANDED_COST * PRIMARY_QTY)
154 ,L_DOC_CURR_PRECISION)
155 ,C_PRECISION)
156 ,C_PRECISION));
157
158 END IF;
159
160 ELSE
161 RETURN (ROUND(ROUND((C_PRIMARY_UNIT_PRICE1 * PRIMARY_QTY)
162 ,L_DOC_CURR_PRECISION) * CURR_CONV_RATE
163 ,C_PRECISION));
164
165 END IF;
166 END CALC_TRX_VALUE;
167
168 FUNCTION C_SORTFORMULA RETURN VARCHAR2 IS
169 BEGIN
170 RETURN ('Sorted by account and item');
171 END C_SORTFORMULA;
172
173 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
174 BEGIN
175 DECLARE
176 CAT_SET_ID NUMBER;
177 CAT_SET_NAME VARCHAR2(30);
178 BEGIN
179 IF P_CAT_SET_ID IS NULL THEN
180 RETURN ('');
181 ELSE
182 CAT_SET_ID := P_CAT_SET_ID;
183 SELECT
184 CATEGORY_SET_NAME
185 INTO
186 CAT_SET_NAME
187 FROM
188 MTL_CATEGORY_SETS
189 WHERE CATEGORY_SET_ID = CAT_SET_ID;
190 RETURN (CAT_SET_NAME);
191 END IF;
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 RETURN ('');
195 WHEN OTHERS THEN
196 RETURN ('Error');
197 END;
198 RETURN NULL;
199 END C_CAT_SET_NAMEFORMULA;
200
201 FUNCTION C_FUNC_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
202 BEGIN
203 RETURN ('(' || R_CURRENCY_CODE || ')');
204 END C_FUNC_CURRENCY_CODEFORMULA;
205
206 FUNCTION PRICE(ENTERED_DR IN NUMBER
207 ,PRIMARY_UNIT_PRICE IN NUMBER) RETURN NUMBER IS
208 PRICE NUMBER;
209 BEGIN
210 IF (ENTERED_DR IS NULL) THEN
211 PRICE := (-1) * PRIMARY_UNIT_PRICE;
212 RETURN (PRICE);
213 ELSE
214 RETURN (PRIMARY_UNIT_PRICE);
215 END IF;
216 RETURN NULL;
217 END PRICE;
218
219 FUNCTION GET_P_STRUCT_NUM RETURN BOOLEAN IS
220 L_P_STRUCT_NUM NUMBER;
221 BEGIN
222 SELECT
223 STRUCTURE_ID
224 INTO
225 L_P_STRUCT_NUM
226 FROM
227 MTL_DEFAULT_SETS_VIEW
228 WHERE FUNCTIONAL_AREA_ID = 2;
229 P_STRUCT_NUM := L_P_STRUCT_NUM;
230 RETURN (TRUE);
231 RETURN NULL;
232 EXCEPTION
233 WHEN OTHERS THEN
234 RETURN (FALSE);
235 END GET_P_STRUCT_NUM;
236
237 FUNCTION C_SORT_MAIN_DISPFORMULA(C_FLEX_ITEM_DISP IN VARCHAR2) RETURN VARCHAR2 IS
238 BEGIN
239 IF (P_SORT_ID = 1) THEN
240 RETURN (C_FLEX_ITEM_DISP);
241 END IF;
242 RETURN NULL;
243 END C_SORT_MAIN_DISPFORMULA;
244
245 FUNCTION C_CAT_DISPFORMULA(C_FLEX_CAT_DISP IN VARCHAR2) RETURN VARCHAR2 IS
246 BEGIN
247 IF (P_SORT_ID = 1) THEN
248 RETURN (C_FLEX_CAT_DISP);
249 END IF;
250 RETURN NULL;
251 END C_CAT_DISPFORMULA;
252
253 FUNCTION C_VENDORFORMULA(VENDOR_NAME IN VARCHAR2) RETURN VARCHAR2 IS
254 BEGIN
255 IF (P_SORT_ID = 2) THEN
256 RETURN (VENDOR_NAME);
257 END IF;
258 RETURN NULL;
259 END C_VENDORFORMULA;
260
261 FUNCTION C_VENDOR_NUMBERFORMULA(VENDOR_NUMBER IN VARCHAR2) RETURN VARCHAR2 IS
262 BEGIN
263 IF (P_SORT_ID = 2) THEN
264 RETURN (VENDOR_NUMBER);
265 END IF;
266 RETURN NULL;
267 END C_VENDOR_NUMBERFORMULA;
268
269 FUNCTION DISPLAY_COLUMN(DELIVER_TO_LOCATION IN VARCHAR2
270 ,C_FLEX_ITEM_DISP IN VARCHAR2) RETURN CHARACTER IS
271 BEGIN
272 IF (P_SORT_ID = 1) THEN
273 RETURN (DELIVER_TO_LOCATION);
274 ELSE
275 RETURN (C_FLEX_ITEM_DISP);
276 END IF;
277 RETURN NULL;
278 END DISPLAY_COLUMN;
279
280 FUNCTION AFTERPFORM RETURN BOOLEAN IS
281 RECEIPT_NUMBERING_TYPE VARCHAR2(240);
282 BEGIN
283 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
284 IF (P_ORG_ID IS NULL) THEN
285 BEGIN
286 SELECT
287 PSP.MANUAL_RECEIPT_NUM_TYPE
288 INTO
289 RECEIPT_NUMBERING_TYPE
290 FROM
291 PO_SYSTEM_PARAMETERS PSP;
292 END;
293 ELSE
294 BEGIN
295 SELECT
296 DISTINCT
297 MANUAL_RECEIPT_NUM_TYPE
298 INTO
299 RECEIPT_NUMBERING_TYPE
300 FROM
301 RCV_RECEIVING_PARAMETERS_V
302 WHERE ORGANIZATION_ID = P_ORG_ID;
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 RECEIPT_NUMBERING_TYPE := 'ALPHANUMERIC';
306 END;
307 END IF;
308 IF P_RECEIPT_NUM_FROM = P_RECEIPT_NUM_TO THEN
309 P_WHERE_RECEIPT_NUM_FROM := ' rdv.receipt_number = :p_receipt_num_from ';
310 P_WHERE_RECEIPT_NUM_TO := '1=1';
311 ELSE
312 IF (P_RECEIPT_NUM_FROM IS NOT NULL) THEN
313 IF (RECEIPT_NUMBERING_TYPE = 'ALPHANUMERIC') THEN
314 P_WHERE_RECEIPT_NUM_FROM := ' rdv.receipt_number >= :P_receipt_num_from ';
315 ELSE
316 P_WHERE_RECEIPT_NUM_FROM := ' decode(ltrim(rdv.receipt_number, ''0123456789''), NULL, to_number(rdv.receipt_number), -1) >= :P_receipt_num_from ';
317 END IF;
318 ELSE
319 P_WHERE_RECEIPT_NUM_FROM := '1=1';
320 END IF;
321 IF (P_RECEIPT_NUM_TO IS NOT NULL) THEN
322 IF (RECEIPT_NUMBERING_TYPE = 'ALPHANUMERIC') THEN
323 P_WHERE_RECEIPT_NUM_TO := ' rdv.receipt_number <= :P_receipt_num_to ';
324 ELSE
325 P_WHERE_RECEIPT_NUM_TO := ' decode(ltrim(rdv.receipt_number, ''0123456789''), NULL, to_number(rdv.receipt_number), -1) <= :P_receipt_num_to ';
326 END IF;
327 ELSE
328 P_WHERE_RECEIPT_NUM_TO := '1=1';
329 END IF;
330 END IF;
331 RETURN (TRUE);
332 END AFTERPFORM;
333
334 FUNCTION C_PRIMARY_UNIT_PRICE1FORMULA(ACC_EVENT_ID IN NUMBER
335 ,TRX_ID IN NUMBER
336 ,PRIMARY_UNIT_PRICE IN NUMBER
337 ,PO_TO_PR_UOM_RATE IN NUMBER) RETURN NUMBER IS
338 L_TOT_TAX NUMBER;
339 BEGIN
340 IF ACC_EVENT_ID = -1 THEN
341 SELECT
342 NVL((SUM(NVL(PO_TAX_SV.GET_TAX('PO'
343 ,POD.PO_DISTRIBUTION_ID)
344 ,0)) / SUM(POD.QUANTITY_ORDERED))
345 ,0)
346 INTO
347 L_TOT_TAX
348 FROM
349 PO_DISTRIBUTIONS_ALL POD,
350 RCV_TRANSACTIONS RT
351 WHERE RT.TRANSACTION_ID = TRX_ID
352 AND ( ( RT.PO_DISTRIBUTION_ID IS NOT NULL
353 AND RT.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID )
354 OR ( RT.PO_DISTRIBUTION_ID IS NULL
355 AND RT.PO_LINE_LOCATION_ID = POD.LINE_LOCATION_ID ) );
356 SELECT
357 DECODE(SIGN(NVL(SUM(ACCOUNTED_NR_TAX)
358 ,0))
359 ,0
360 ,0
361 ,1) * L_TOT_TAX
362 INTO
363 L_TOT_TAX
364 FROM
365 RCV_RECEIVING_SUB_LEDGER
366 WHERE RCV_TRANSACTION_ID = TRX_ID;
367 RETURN ((PRIMARY_UNIT_PRICE + (L_TOT_TAX * PO_TO_PR_UOM_RATE)));
368 ELSE
369 RETURN (PRIMARY_UNIT_PRICE);
370 END IF;
371 END C_PRIMARY_UNIT_PRICE1FORMULA;
372
373 FUNCTION C_PRIMARY_UNIT_PRICE2FORMULA(EVENT_TYPE_ID IN NUMBER
374 ,C_PRIMARY_UNIT_PRICE1 IN NUMBER
375 /* Support for Landed Cost Management */
376 ,PRIMARY_LANDED_COST IN NUMBER
377 ,PRIOR_UNIT_PRICE IN NUMBER
378 ,ACCOUNTING_LINE_TYPE IN VARCHAR2
379 ,CURR_CONV_RATE IN NUMBER) RETURN NUMBER IS
380 BEGIN
381
382 /* Support for Landed Cost Management */
383 IF (EVENT_TYPE_ID in (7,8)) THEN
384 RETURN (NULL);
385
386 ELSIF (EVENT_TYPE_ID in (15,16,17)) THEN
387 RETURN (ROUND((C_PRIMARY_UNIT_PRICE1 * CURR_CONV_RATE - PRIOR_UNIT_PRICE * CURR_CONV_RATE)
388 ,8));
389
390 ELSIF PRIMARY_LANDED_COST IS NOT NULL THEN
391 IF ACCOUNTING_LINE_TYPE = 'Receiving Inspection' THEN
392 RETURN (ROUND(PRIMARY_LANDED_COST
393 ,8));
394 ELSIF ACCOUNTING_LINE_TYPE = 'Accrual' THEN
395 RETURN (ROUND(C_PRIMARY_UNIT_PRICE1 * CURR_CONV_RATE
396 ,8));
397 ELSIF ACCOUNTING_LINE_TYPE = 'Landed Cost Absorption' THEN
398 RETURN (ROUND((C_PRIMARY_UNIT_PRICE1 * CURR_CONV_RATE - PRIMARY_LANDED_COST)
399 ,8));
400 END IF;
401
402 ELSE
403 RETURN (ROUND(C_PRIMARY_UNIT_PRICE1 * CURR_CONV_RATE
404 ,8));
405
406 END IF;
407 END C_PRIMARY_UNIT_PRICE2FORMULA;
408
409 FUNCTION C_QUANTITYFORMULA(EVENT_TYPE_ID IN NUMBER
410 ,QTY IN NUMBER) RETURN NUMBER IS
411 BEGIN
412 IF (EVENT_TYPE_ID in (7,8)) THEN
413 RETURN NULL;
414 ELSE
415 RETURN QTY;
416 END IF;
417 END C_QUANTITYFORMULA;
418
419 FUNCTION AFTERREPORT RETURN BOOLEAN IS
420 BEGIN
421 RETURN (TRUE);
422 END AFTERREPORT;
423
424
425
426 END PO_POXRRVDR_XMLP_PKG;
427
[Home] [Help]