1 PACKAGE BODY INV_INVSTMVT_XMLP_PKG AS
2 /* $Header: INVSTMVTB.pls 120.1.12020000.2 2013/02/18 07:42:49 abhissri ship $ */
3 FUNCTION CF_PARENT_MVT_TERR_CODEFORMULA(C_DISPATCH_TERR_EU_CODE IN VARCHAR2
4 ,C_DESTINATION_TERR_EU_CODE IN VARCHAR2) RETURN VARCHAR2 IS
5 BEGIN
6 IF P_MOVEMENT_TYPE = 'A' OR P_MOVEMENT_TYPE = 'AA' THEN
7 RETURN (C_DISPATCH_TERR_EU_CODE);
8 ELSIF P_MOVEMENT_TYPE = 'D' OR P_MOVEMENT_TYPE = 'DA' THEN
9 RETURN (C_DESTINATION_TERR_EU_CODE);
10 ELSE
11 RETURN (NULL);
12 END IF;
13 RETURN NULL;
14 END CF_PARENT_MVT_TERR_CODEFORMULA;
15
16 PROCEDURE FORMAT_QUANTITY(P_PRECISION IN NUMBER) IS
17 BEGIN
18 /* SRW.ATTR.MASK := SRW.FORMATMASK_ATTR;
19 IF P_PRECISION = 0 THEN
20 --SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0';
21 ELSIF P_PRECISION = 1 THEN
22 --SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0.0';
23 ELSIF P_PRECISION = 2 THEN
24 --SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0.00';
25 ELSIF P_PRECISION = 3 THEN
26 --SRW.ATTR.FORMATMASK := '-NN,NNN,NNN,NN0.000';
27 ELSIF P_PRECISION = 4 THEN
28 --SRW.ATTR.FORMATMASK := '-N,NNN,NNN,NN0.0000';
29 ELSIF P_PRECISION = 5 THEN
30 --SRW.ATTR.FORMATMASK := '-NNN,NNN,NN0.00000';
31 ELSIF P_PRECISION = 6 THEN
32 --SRW.ATTR.FORMATMASK := '-NN,NNN,NN0.000000';
33 ELSIF P_PRECISION = 7 THEN
34 --SRW.ATTR.FORMATMASK := '-NNNNNNNNNNN0';
35 ELSIF P_PRECISION = 8 THEN
36 --SRW.ATTR.FORMATMASK := '-NNNNNNNNNNN0.0';
37 ELSIF P_PRECISION = 9 THEN
38 --SRW.ATTR.FORMATMASK := '-NNNNNNNNNN0.00';
39 ELSIF P_PRECISION = 10 THEN
40 --SRW.ATTR.FORMATMASK := '-NNNNNNNNNN0.000';
41 ELSIF P_PRECISION = 11 THEN
42 -- SRW.ATTR.FORMATMASK := '-NNNNNNNNN0.0000';
43 ELSIF P_PRECISION = 12 THEN
44 -- SRW.ATTR.FORMATMASK := '-NNNNNNNN0.00000';
45 ELSIF P_PRECISION = 13 THEN
46 -- SRW.ATTR.FORMATMASK := '-NNNNNNN0.000000';
47 ELSE
48 -- SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0.00';
49 END IF;
50 */
51 /*SRW.SET_ATTR(0
52 ,SRW.ATTR)*/NULL;
53 END FORMAT_QUANTITY;
54
55 FUNCTION CF_COMMODITY_FIELDFORMULA RETURN VARCHAR2 IS
56 BEGIN
57 RETURN (NULL);
58 END CF_COMMODITY_FIELDFORMULA;
59
60 FUNCTION CF_1FORMULA RETURN VARCHAR2 IS
61 BEGIN
62 IF ((P_REPORT_OPTION = 'S/S' OR P_REPORT_OPTION = 'O/S') AND P_MOVEMENT_TYPE = 'A') THEN
63 CP_REPORT_TITLE := 'Arrival Movement Statistics Summary';
64 ELSIF (P_REPORT_OPTION = 'S/D' AND P_MOVEMENT_TYPE = 'A') THEN
65 CP_REPORT_TITLE := ' Arrival Movement Statistics Detail';
66 ELSIF ((P_REPORT_OPTION = 'S/S' OR P_REPORT_OPTION = 'O/S') AND P_MOVEMENT_TYPE = 'AA') THEN
67 CP_REPORT_TITLE := 'Arrival Adjustment Movement Statistics Summary';
68 ELSIF (P_REPORT_OPTION = 'S/D' AND P_MOVEMENT_TYPE = 'AA') THEN
69 CP_REPORT_TITLE := 'Arrival Adjustment Movement Statistics Detail';
70 ELSIF ((P_REPORT_OPTION = 'S/S' OR P_REPORT_OPTION = 'O/S') AND P_MOVEMENT_TYPE = 'D') THEN
71 CP_REPORT_TITLE := 'Dispatch Movement Statistics Summary';
72 ELSIF (P_REPORT_OPTION = 'S/D' AND P_MOVEMENT_TYPE = 'D') THEN
73 CP_REPORT_TITLE := 'Dispatch Movement Statistics Detail';
74 ELSIF ((P_REPORT_OPTION = 'S/S' OR P_REPORT_OPTION = 'O/S') AND P_MOVEMENT_TYPE = 'DA') THEN
75 CP_REPORT_TITLE := 'Dispatch Adjustment Movement Statistics Summary';
76 ELSIF (P_REPORT_OPTION = 'S/D' AND P_MOVEMENT_TYPE = 'DA') THEN
77 CP_REPORT_TITLE := 'Dispatch Adjustment Movement Statistics Detail';
78 END IF;
79 RETURN NULL;
80 END CF_1FORMULA;
81
82 FUNCTION CF_INVOICEFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
83 ,C_INVOICE_REFERENCE IN VARCHAR2
84 ,C_INVOICE_ID IN NUMBER
85 ,C_MOVEMENT_TYPE IN VARCHAR2) RETURN CHAR IS
86 BEGIN
87 DECLARE
88 L_INVOICE VARCHAR2(50);
89 BEGIN
90 IF P_REPORT_OPTION = 'S/D' THEN
91 IF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
92 L_INVOICE := C_INVOICE_REFERENCE;
93 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') AND C_INVOICE_ID IS NOT NULL THEN
94 BEGIN
95 SELECT
96 INVOICE_NUM
97 INTO L_INVOICE
98 FROM
99 AP_INVOICES_ALL
100 WHERE INVOICE_ID = C_INVOICE_ID
101 AND ROWNUM < 2;
102 EXCEPTION
103 WHEN NO_DATA_FOUND THEN
104 L_INVOICE := NULL;
105 END;
106 ELSIF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') AND C_INVOICE_ID IS NOT NULL THEN
107 BEGIN
108 SELECT
109 TRX_NUMBER
110 INTO L_INVOICE
111 FROM
112 RA_CUSTOMER_TRX_ALL
113 WHERE CUSTOMER_TRX_ID = C_INVOICE_ID
114 AND ROWNUM < 2;
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 IF C_MOVEMENT_TYPE = 'A' THEN
118 BEGIN
119 SELECT
120 INVOICE_NUM
121 INTO L_INVOICE
122 FROM
123 AP_INVOICES_ALL
124 WHERE INVOICE_ID = C_INVOICE_ID
125 AND ROWNUM < 2;
126 EXCEPTION
127 WHEN OTHERS THEN
128 L_INVOICE := NULL;
129 END;
130 ELSE
131 L_INVOICE := NULL;
132 END IF;
133 END;
134 ELSE
135 L_INVOICE := NULL;
136 END IF;
137 ELSE
138 L_INVOICE := NULL;
139 END IF;
140 RETURN (L_INVOICE);
141 END;
142 RETURN NULL;
143 END CF_INVOICEFORMULA;
144
145 FUNCTION CF_INVOICE_BATCHFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
146 ,C_INVOICE_BATCH_REFERENCE IN VARCHAR2
147 ,C_INVOICE_BATCH_ID IN NUMBER) RETURN CHAR IS
148 BEGIN
149 DECLARE
150 L_INVOICE_BATCH VARCHAR2(50);
151 BEGIN
152 IF P_REPORT_OPTION = 'S/D' THEN
153 IF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
154 L_INVOICE_BATCH := C_INVOICE_BATCH_REFERENCE;
155 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') AND C_INVOICE_BATCH_ID IS NOT NULL THEN
156 BEGIN
157 SELECT
158 BATCH_NAME
159 INTO L_INVOICE_BATCH
160 FROM
161 AP_BATCHES_ALL
162 WHERE BATCH_ID = C_INVOICE_BATCH_ID
163 AND ROWNUM < 2;
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 L_INVOICE_BATCH := NULL;
167 END;
168 ELSIF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') AND C_INVOICE_BATCH_ID IS NOT NULL THEN
169 BEGIN
170 SELECT
171 NAME
172 INTO L_INVOICE_BATCH
173 FROM
174 RA_BATCHES_ALL
175 WHERE BATCH_ID = C_INVOICE_BATCH_ID
176 AND ROWNUM < 2;
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 L_INVOICE_BATCH := NULL;
180 END;
181 ELSE
182 L_INVOICE_BATCH := NULL;
183 END IF;
184 ELSE
185 L_INVOICE_BATCH := NULL;
186 END IF;
187 RETURN (L_INVOICE_BATCH);
188 END;
189 RETURN NULL;
190 END CF_INVOICE_BATCHFORMULA;
191
192 FUNCTION CF_INVOICE_LINEFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
193 ,C_INVOICE_LINE_REFERENCE IN VARCHAR2
194 ,C_MOVEMENT_TYPE IN VARCHAR2
195 ,C_DISTRIBUTION_LINE_NUMBER IN NUMBER
196 ,C_CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN CHAR IS
197 BEGIN
198 DECLARE
199 L_INVOICE_LINE VARCHAR2(50);
200 BEGIN
201 IF P_REPORT_OPTION = 'S/D' THEN
202 IF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
203 L_INVOICE_LINE := C_INVOICE_LINE_REFERENCE;
204 ELSIF (C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') OR (C_DOCUMENT_SOURCE_TYPE = 'SO' AND C_MOVEMENT_TYPE = 'A')) THEN
205 L_INVOICE_LINE := C_DISTRIBUTION_LINE_NUMBER;
206 ELSIF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
207 BEGIN
208 SELECT
209 LINE_NUMBER
210 INTO L_INVOICE_LINE
211 FROM
212 RA_CUSTOMER_TRX_LINES_ALL
213 WHERE CUSTOMER_TRX_LINE_ID = C_CUSTOMER_TRX_LINE_ID
214 AND ROWNUM < 2;
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 L_INVOICE_LINE := NULL;
218 END;
219 ELSE
220 L_INVOICE_LINE := NULL;
221 END IF;
222 ELSE
223 L_INVOICE_LINE := NULL;
224 END IF;
225 RETURN (L_INVOICE_LINE);
226 END;
227 RETURN NULL;
228 END CF_INVOICE_LINEFORMULA;
229
230 FUNCTION CF_INVOICE_UOM_CODEFORMULA(C_CUSTOMER_TRX_LINE_ID IN NUMBER
231 ,C_TRANSACTION_UOM_CODE IN VARCHAR2) RETURN VARCHAR2 IS
232 BEGIN
233 DECLARE
234 L_INVOICE_UOM_CODE VARCHAR2(3);
235 BEGIN
236 IF P_REPORT_OPTION = 'S/D' THEN
237 IF C_CUSTOMER_TRX_LINE_ID IS NOT NULL THEN
238 BEGIN
239 SELECT
240 UOM_CODE
241 INTO L_INVOICE_UOM_CODE
242 FROM
243 RA_CUSTOMER_TRX_LINES_ALL
244 WHERE CUSTOMER_TRX_LINE_ID = C_CUSTOMER_TRX_LINE_ID;
245 EXCEPTION
246 WHEN NO_DATA_FOUND THEN
247 L_INVOICE_UOM_CODE := NULL;
248 END;
249 ELSE
250 L_INVOICE_UOM_CODE := C_TRANSACTION_UOM_CODE;
251 END IF;
252 ELSE
253 L_INVOICE_UOM_CODE := NULL;
254 END IF;
255 RETURN (L_INVOICE_UOM_CODE);
256 END;
257 RETURN NULL;
258 END CF_INVOICE_UOM_CODEFORMULA;
259
260 FUNCTION CF_RECEIPT_NUMBERFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
261 ,C_RECEIPT_REFERENCE IN VARCHAR2
262 ,C_PICK_SLIP_REFERENCE IN VARCHAR2
263 ,C_SHIPMENT_HEADER_ID IN NUMBER
264 ,C_SHIPMENT_REFERENCE IN VARCHAR2) RETURN VARCHAR2 IS
265 BEGIN
266 DECLARE
267 L_RECEIPT_NUMBER VARCHAR2(50);
268 BEGIN
269 IF P_REPORT_OPTION = 'S/D' THEN
270 IF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
271 IF P_MOVEMENT_TYPE = 'A' OR P_MOVEMENT_TYPE = 'AA' THEN
272 L_RECEIPT_NUMBER := C_RECEIPT_REFERENCE;
273 ELSE
274 L_RECEIPT_NUMBER := C_PICK_SLIP_REFERENCE;
275 END IF;
276 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV','RMA') AND C_SHIPMENT_HEADER_ID IS NOT NULL THEN
277 BEGIN
278 SELECT
279 RECEIPT_NUM
280 INTO L_RECEIPT_NUMBER
281 FROM
282 RCV_SHIPMENT_HEADERS
283 WHERE SHIPMENT_HEADER_ID = C_SHIPMENT_HEADER_ID
284 AND ROWNUM < 2;
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN
287 L_RECEIPT_NUMBER := NULL;
288 END;
289 ELSIF C_DOCUMENT_SOURCE_TYPE = 'SO' THEN
290 L_RECEIPT_NUMBER := C_SHIPMENT_REFERENCE;
291 ELSE
292 L_RECEIPT_NUMBER := NULL;
293 END IF;
294 ELSE
295 L_RECEIPT_NUMBER := NULL;
296 END IF;
297 RETURN (L_RECEIPT_NUMBER);
298 END;
299 RETURN NULL;
300 END CF_RECEIPT_NUMBERFORMULA;
301
302 FUNCTION CF_SHIPMENT_LINEFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
303 ,C_SHIPMENT_LINE_REFERENCE IN VARCHAR2
304 ,C_SHIPMENT_LINE_ID IN NUMBER) RETURN VARCHAR2 IS
305 BEGIN
306 DECLARE
307 L_SHIPMENT_LINE VARCHAR2(30);
308 BEGIN
309 IF P_REPORT_OPTION = 'S/D' THEN
310 IF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
311 L_SHIPMENT_LINE := C_SHIPMENT_LINE_REFERENCE;
312 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
313 BEGIN
314 SELECT
315 LINE_NUM
316 INTO L_SHIPMENT_LINE
317 FROM
318 RCV_SHIPMENT_LINES
319 WHERE SHIPMENT_LINE_ID = C_SHIPMENT_LINE_ID
320 AND ROWNUM < 2;
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN
323 L_SHIPMENT_LINE := NULL;
324 END;
325 ELSE
326 L_SHIPMENT_LINE := NULL;
327 END IF;
328 ELSE
329 L_SHIPMENT_LINE := NULL;
330 END IF;
331 RETURN (L_SHIPMENT_LINE);
332 END;
333 RETURN NULL;
334 END CF_SHIPMENT_LINEFORMULA;
335
336 FUNCTION CF_SHIPMENT_NUMBERFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
337 ,C_SHIPMENT_REFERENCE IN VARCHAR2
338 ,C_PO_LINE_LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
339 BEGIN
340 DECLARE
341 L_SHIPMENT_NUMBER VARCHAR2(50);
342 BEGIN
343 IF P_REPORT_OPTION = 'S/D' THEN
344 IF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
345 L_SHIPMENT_NUMBER := C_SHIPMENT_REFERENCE;
346 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
347 BEGIN
348 SELECT
349 SHIPMENT_NUM
350 INTO L_SHIPMENT_NUMBER
351 FROM
352 PO_LINE_LOCATIONS_ALL
353 WHERE LINE_LOCATION_ID = C_PO_LINE_LOCATION_ID
354 AND ROWNUM < 2;
355 EXCEPTION
356 WHEN NO_DATA_FOUND THEN
357 L_SHIPMENT_NUMBER := NULL;
358 END;
359 ELSE
360 L_SHIPMENT_NUMBER := NULL;
361 END IF;
362 ELSE
363 L_SHIPMENT_NUMBER := NULL;
364 END IF;
365 RETURN (L_SHIPMENT_NUMBER);
366 END;
367 RETURN NULL;
368 END CF_SHIPMENT_NUMBERFORMULA;
369
370 FUNCTION CF_SOURCE_LINE_NUMBERFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
371 ,C_DOCUMENT_LINE_REFERENCE IN VARCHAR2
372 ,C_PO_LINE_ID IN NUMBER
373 ,C_ORDER_LINE_ID IN NUMBER
374 ,C_MOVEMENT_TYPE IN VARCHAR2
375 ,C_REQUISITION_LINE_ID IN NUMBER) RETURN VARCHAR2 IS
376 BEGIN
377 DECLARE
378 L_SOURCE_LINE_NUMBER VARCHAR2(30);
379 BEGIN
380 IF P_REPORT_OPTION = 'S/D' THEN
381 IF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
382 L_SOURCE_LINE_NUMBER := C_DOCUMENT_LINE_REFERENCE;
383 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
384 BEGIN
385 SELECT
386 LINE_NUM
387 INTO L_SOURCE_LINE_NUMBER
388 FROM
389 PO_LINES_ALL
390 WHERE PO_LINE_ID = C_PO_LINE_ID
391 AND ROWNUM < 2;
392 EXCEPTION
393 WHEN NO_DATA_FOUND THEN
394 L_SOURCE_LINE_NUMBER := NULL;
395 END;
396 ELSIF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
397 -- Bugfix 16214456: OE Schema will be obsoleted now.
398 -- IF P_OE_OR_OM = 'OE' THEN
399 -- BEGIN
400 -- SELECT
401 -- LINE_NUMBER
402 -- INTO L_SOURCE_LINE_NUMBER
403 -- FROM
404 -- SO_LINES_ALL
405 -- WHERE LINE_ID = C_ORDER_LINE_ID
406 -- AND ROWNUM < 2;
407 -- EXCEPTION
408 -- WHEN NO_DATA_FOUND THEN
409 -- L_SOURCE_LINE_NUMBER := NULL;
410 -- END;
411 -- ELSE
412 BEGIN
413 SELECT
414 LINE_NUMBER
415 INTO L_SOURCE_LINE_NUMBER
416 FROM
417 OE_ORDER_LINES_ALL
418 WHERE LINE_ID = C_ORDER_LINE_ID
419 AND ROWNUM < 2;
420 EXCEPTION
421 WHEN NO_DATA_FOUND THEN
422 L_SOURCE_LINE_NUMBER := NULL;
423 END;
424 -- END IF;
425 ELSIF C_DOCUMENT_SOURCE_TYPE = 'IO' THEN
426 IF C_MOVEMENT_TYPE in ('A','AA') THEN
427 BEGIN
428 SELECT
429 LINE_NUM
430 INTO L_SOURCE_LINE_NUMBER
431 FROM
432 PO_REQUISITION_LINES_ALL
433 WHERE REQUISITION_LINE_ID = C_REQUISITION_LINE_ID
434 AND ROWNUM < 2;
435 EXCEPTION
436 WHEN NO_DATA_FOUND THEN
437 L_SOURCE_LINE_NUMBER := NULL;
438 END;
439 ELSE
440 -- Bugfix 16214456: OE Schema will be obsoleted now.
441 -- IF P_OE_OR_OM = 'OE' THEN
442 -- BEGIN
443 -- SELECT
444 -- LINE_NUMBER
445 -- INTO L_SOURCE_LINE_NUMBER
446 -- FROM
447 -- SO_LINES_ALL
448 -- WHERE LINE_ID = C_ORDER_LINE_ID
449 -- AND ROWNUM < 2;
450 -- EXCEPTION
451 -- WHEN NO_DATA_FOUND THEN
452 -- L_SOURCE_LINE_NUMBER := NULL;
453 -- END;
454 -- ELSE
455 BEGIN
456 SELECT
457 LINE_NUMBER
458 INTO L_SOURCE_LINE_NUMBER
459 FROM
460 OE_ORDER_LINES_ALL
461 WHERE LINE_ID = C_ORDER_LINE_ID
462 AND ROWNUM < 2;
463 EXCEPTION
464 WHEN NO_DATA_FOUND THEN
465 L_SOURCE_LINE_NUMBER := NULL;
466 END;
467 -- END IF;
468 END IF;
469 ELSE
470 L_SOURCE_LINE_NUMBER := NULL;
471 END IF;
472 ELSE
473 L_SOURCE_LINE_NUMBER := NULL;
474 END IF;
475 RETURN (L_SOURCE_LINE_NUMBER);
476 END;
477 RETURN NULL;
478 END CF_SOURCE_LINE_NUMBERFORMULA;
479
480 FUNCTION CF_SOURCE_NUMBERFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
481 ,C_DOCUMENT_REFERENCE IN VARCHAR2
482 ,C_PO_HEADER_ID IN NUMBER
483 ,C_ORDER_HEADER_ID IN NUMBER
484 ,C_MOVEMENT_TYPE IN VARCHAR2
485 ,C_REQUISITION_HEADER_ID IN NUMBER
486 ,C_MTL_TRANSACTION_ID IN NUMBER) RETURN VARCHAR2 IS
487 BEGIN
488 DECLARE
489 L_SOURCE_NUMBER VARCHAR2(50);
490 BEGIN
491 IF P_REPORT_OPTION = 'S/D' THEN
492 IF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
493 L_SOURCE_NUMBER := C_DOCUMENT_REFERENCE;
494 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
495 BEGIN
496 SELECT
497 SEGMENT1
498 INTO L_SOURCE_NUMBER
499 FROM
500 PO_HEADERS_ALL
501 WHERE PO_HEADER_ID = C_PO_HEADER_ID
502 AND ROWNUM < 2;
503 EXCEPTION
504 WHEN NO_DATA_FOUND THEN
505 L_SOURCE_NUMBER := NULL;
506 END;
507 ELSIF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
508 -- Bugfix 16214456: OE Schema will be obsoleted now.
509 -- IF P_OE_OR_OM = 'OE' THEN
510 -- BEGIN
511 -- SELECT
512 -- ORDER_NUMBER
513 -- INTO L_SOURCE_NUMBER
514 -- FROM
515 -- SO_HEADERS_ALL
516 -- WHERE HEADER_ID = C_ORDER_HEADER_ID
517 -- AND ROWNUM < 2;
518 -- EXCEPTION
519 -- WHEN NO_DATA_FOUND THEN
520 -- L_SOURCE_NUMBER := NULL;
521 -- END;
522 -- ELSE
523 BEGIN
524 SELECT
525 ORDER_NUMBER
526 INTO L_SOURCE_NUMBER
527 FROM
528 OE_ORDER_HEADERS_ALL
529 WHERE HEADER_ID = C_ORDER_HEADER_ID
530 AND ROWNUM < 2;
531 EXCEPTION
532 WHEN NO_DATA_FOUND THEN
533 L_SOURCE_NUMBER := NULL;
534 END;
535 -- END IF;
536 ELSIF C_DOCUMENT_SOURCE_TYPE = 'IO' THEN
537 IF C_MOVEMENT_TYPE in ('A','AA') THEN
538 BEGIN
539 SELECT
540 SEGMENT1
541 INTO L_SOURCE_NUMBER
542 FROM
543 PO_REQUISITION_HEADERS_ALL
544 WHERE REQUISITION_HEADER_ID = C_REQUISITION_HEADER_ID
545 AND ROWNUM < 2;
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 L_SOURCE_NUMBER := NULL;
549 END;
550 ELSE
551 -- Bugfix 16214456: OE Schema will be obsoleted now.
552 -- IF P_OE_OR_OM = 'OE' THEN
553 -- BEGIN
554 -- SELECT
555 -- ORDER_NUMBER
556 -- INTO L_SOURCE_NUMBER
557 -- FROM
558 -- SO_HEADERS_ALL
559 -- WHERE HEADER_ID = C_ORDER_HEADER_ID
560 -- AND ROWNUM < 2;
561 -- EXCEPTION
562 -- WHEN NO_DATA_FOUND THEN
563 -- L_SOURCE_NUMBER := NULL;
564 -- END;
565 -- ELSE
566 BEGIN
567 SELECT
568 ORDER_NUMBER
569 INTO L_SOURCE_NUMBER
570 FROM
571 OE_ORDER_HEADERS_ALL
572 WHERE HEADER_ID = C_ORDER_HEADER_ID
573 AND ROWNUM < 2;
574 EXCEPTION
575 WHEN NO_DATA_FOUND THEN
576 L_SOURCE_NUMBER := NULL;
577 END;
578 -- END IF;
579 END IF;
580 ELSIF C_DOCUMENT_SOURCE_TYPE = 'INV' THEN
581 L_SOURCE_NUMBER := TO_CHAR(C_MTL_TRANSACTION_ID);
582 ELSE
583 L_SOURCE_NUMBER := NULL;
584 END IF;
585 ELSE
586 L_SOURCE_NUMBER := NULL;
587 END IF;
588 RETURN (L_SOURCE_NUMBER);
589 END;
590 RETURN NULL;
591 END CF_SOURCE_NUMBERFORMULA;
592
593 FUNCTION CF_TRADER_NAMEFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
594 ,C_CUSTOMER_NAME IN VARCHAR2
595 ,C_SHIP_TO_CUSTOMER_ID IN NUMBER
596 ,C_VENDOR_NAME IN VARCHAR2
597 ,C_FROM_ORGANIZATION_ID IN NUMBER
598 ,C_TO_ORGANIZATION_ID IN NUMBER
599 ,C_TRANSACTING_FROM_ORG IN VARCHAR2
600 ,C_TRANSACTING_TO_ORG IN VARCHAR2) RETURN VARCHAR2 IS
601 BEGIN
602 DECLARE
603 L_TRADER_NAME VARCHAR2(240);
604 BEGIN
605 IF P_REPORT_OPTION = 'S/D' THEN
606 IF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
607 IF C_CUSTOMER_NAME IS NOT NULL THEN
608 L_TRADER_NAME := C_CUSTOMER_NAME;
609 ELSE
610 IF C_SHIP_TO_CUSTOMER_ID IS NOT NULL THEN
611 BEGIN
612 SELECT
613 HP.PARTY_NAME
614 INTO L_TRADER_NAME
615 FROM
616 HZ_PARTIES HP,
617 HZ_CUST_ACCOUNTS HCA
618 WHERE HP.PARTY_ID = HCA.PARTY_ID
619 AND HCA.CUST_ACCOUNT_ID = C_SHIP_TO_CUSTOMER_ID;
620 EXCEPTION
621 WHEN NO_DATA_FOUND THEN
622 L_TRADER_NAME := NULL;
623 END;
624 ELSE
625 L_TRADER_NAME := NULL;
626 END IF;
627 END IF;
628 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
629 L_TRADER_NAME := C_VENDOR_NAME;
630 ELSIF C_DOCUMENT_SOURCE_TYPE in ('IO','INV') THEN
631 IF P_MOVEMENT_TYPE in ('A','AA') THEN
632 IF C_FROM_ORGANIZATION_ID IS NOT NULL THEN
633 BEGIN
634 SELECT
635 NAME
636 INTO L_TRADER_NAME
637 FROM
638 HR_ORGANIZATION_UNITS
639 WHERE ORGANIZATION_ID = C_FROM_ORGANIZATION_ID
640 AND ROWNUM < 2;
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 L_TRADER_NAME := NULL;
644 END;
645 ELSE
646 L_TRADER_NAME := NULL;
647 END IF;
648 ELSE
649 IF C_TO_ORGANIZATION_ID IS NOT NULL THEN
650 BEGIN
651 SELECT
652 NAME
653 INTO L_TRADER_NAME
654 FROM
655 HR_ORGANIZATION_UNITS
656 WHERE ORGANIZATION_ID = C_TO_ORGANIZATION_ID
657 AND ROWNUM < 2;
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN
660 L_TRADER_NAME := NULL;
661 END;
662 ELSE
663 L_TRADER_NAME := NULL;
664 END IF;
665 END IF;
666 ELSIF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
667 IF C_CUSTOMER_NAME IS NOT NULL THEN
668 L_TRADER_NAME := C_CUSTOMER_NAME;
669 ELSIF C_VENDOR_NAME IS NOT NULL THEN
670 L_TRADER_NAME := C_VENDOR_NAME;
671 ELSIF C_TRANSACTING_FROM_ORG IS NOT NULL THEN
672 L_TRADER_NAME := C_TRANSACTING_FROM_ORG;
673 ELSIF C_TRANSACTING_TO_ORG IS NOT NULL THEN
674 L_TRADER_NAME := C_TRANSACTING_TO_ORG;
675 ELSE
676 L_TRADER_NAME := NULL;
677 END IF;
678 ELSE
679 L_TRADER_NAME := NULL;
680 END IF;
681 ELSE
682 L_TRADER_NAME := NULL;
683 END IF;
684 RETURN (L_TRADER_NAME);
685 END;
686 RETURN NULL;
687 END CF_TRADER_NAMEFORMULA;
688
689 FUNCTION CF_TRADER_NUMBERFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
690 ,C_CUSTOMER_NUMBER IN VARCHAR2
691 ,C_SHIP_TO_CUSTOMER_ID IN NUMBER
692 ,C_VENDOR_NUMBER IN VARCHAR2
693 ,C_TRANSACTING_FROM_ORG IN VARCHAR2
694 ,C_TRANSACTING_TO_ORG IN VARCHAR2) RETURN VARCHAR2 IS
695 BEGIN
696 DECLARE
697 L_TRADER_NUMBER VARCHAR2(50);
698 BEGIN
699 IF P_REPORT_OPTION = 'S/D' THEN
700 IF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
701 IF C_CUSTOMER_NUMBER IS NOT NULL THEN
702 L_TRADER_NUMBER := C_CUSTOMER_NUMBER;
703 ELSE
704 IF C_SHIP_TO_CUSTOMER_ID IS NOT NULL THEN
705 BEGIN
706 SELECT
707 HCA.ACCOUNT_NUMBER
708 INTO L_TRADER_NUMBER
709 FROM
710 HZ_PARTIES HP,
711 HZ_CUST_ACCOUNTS HCA
712 WHERE HP.PARTY_ID = HCA.PARTY_ID
713 AND HCA.CUST_ACCOUNT_ID = C_SHIP_TO_CUSTOMER_ID;
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 L_TRADER_NUMBER := NULL;
717 END;
718 ELSE
719 L_TRADER_NUMBER := NULL;
720 END IF;
721 END IF;
722 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
723 L_TRADER_NUMBER := C_VENDOR_NUMBER;
724 ELSIF C_DOCUMENT_SOURCE_TYPE in ('IO','INV') THEN
725 L_TRADER_NUMBER := NULL;
726 ELSIF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
727 IF C_CUSTOMER_NUMBER IS NOT NULL THEN
728 L_TRADER_NUMBER := C_CUSTOMER_NUMBER;
729 ELSIF C_VENDOR_NUMBER IS NOT NULL THEN
730 L_TRADER_NUMBER := C_VENDOR_NUMBER;
731 ELSIF C_TRANSACTING_FROM_ORG IS NOT NULL THEN
732 L_TRADER_NUMBER := NULL;
733 ELSIF C_TRANSACTING_TO_ORG IS NOT NULL THEN
734 L_TRADER_NUMBER := NULL;
735 ELSE
736 L_TRADER_NUMBER := NULL;
737 END IF;
738 ELSE
739 L_TRADER_NUMBER := NULL;
740 END IF;
741 ELSE
742 L_TRADER_NUMBER := NULL;
743 END IF;
744 RETURN (L_TRADER_NUMBER);
745 END;
746 RETURN NULL;
747 END CF_TRADER_NUMBERFORMULA;
748
749 FUNCTION CF_TRADER_SITEFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
750 ,C_CUSTOMER_LOCATION IN VARCHAR2
751 ,C_SHIP_TO_SITE_USE_ID IN NUMBER
752 ,C_VENDOR_SITE IN VARCHAR2
753 ,C_CUSTOMER_NAME IN VARCHAR2
754 ,C_VENDOR_NAME IN VARCHAR2
755 ,C_TRANSACTING_FROM_ORG IN VARCHAR2
756 ,C_TRANSACTING_TO_ORG IN VARCHAR2) RETURN VARCHAR2 IS
757 BEGIN
758 DECLARE
759 L_TRADER_SITE VARCHAR2(50);
760 BEGIN
761 IF P_REPORT_OPTION = 'S/D' THEN
762 IF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
763 IF C_CUSTOMER_LOCATION IS NOT NULL THEN
764 L_TRADER_SITE := C_CUSTOMER_LOCATION;
765 ELSIF C_SHIP_TO_SITE_USE_ID IS NOT NULL THEN
766 BEGIN
767 SELECT
768 LOCATION
769 INTO L_TRADER_SITE
770 FROM
771 HZ_CUST_SITE_USES_ALL
772 WHERE SITE_USE_ID = C_SHIP_TO_SITE_USE_ID;
773 EXCEPTION
774 WHEN NO_DATA_FOUND THEN
775 L_TRADER_SITE := NULL;
776 END;
777 ELSE
778 L_TRADER_SITE := NULL;
779 END IF;
780 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
781 L_TRADER_SITE := C_VENDOR_SITE;
782 ELSIF C_DOCUMENT_SOURCE_TYPE in ('IO','INV') THEN
783 L_TRADER_SITE := NULL;
784 ELSIF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
785 IF C_CUSTOMER_NAME IS NOT NULL THEN
786 L_TRADER_SITE := C_CUSTOMER_LOCATION;
787 ELSIF C_VENDOR_NAME IS NOT NULL THEN
788 L_TRADER_SITE := C_VENDOR_SITE;
789 ELSIF C_TRANSACTING_FROM_ORG IS NOT NULL THEN
790 L_TRADER_SITE := NULL;
791 ELSIF C_TRANSACTING_TO_ORG IS NOT NULL THEN
792 L_TRADER_SITE := NULL;
793 ELSE
794 L_TRADER_SITE := NULL;
795 END IF;
796 ELSE
797 L_TRADER_SITE := NULL;
798 END IF;
799 ELSE
800 L_TRADER_SITE := NULL;
801 END IF;
802 RETURN (L_TRADER_SITE);
803 END;
804 RETURN NULL;
805 END CF_TRADER_SITEFORMULA;
806
807 FUNCTION CF_TRADER_TYPEFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
808 ,C_CUSTOMER_NAME IN VARCHAR2
809 ,C_VENDOR_NAME IN VARCHAR2
810 ,C_TRANSACTING_FROM_ORG IN VARCHAR2
811 ,C_TRANSACTING_TO_ORG IN VARCHAR2) RETURN VARCHAR2 IS
812 BEGIN
813 DECLARE
814 L_TRADER_TYPE VARCHAR2(20);
815 BEGIN
816 IF P_REPORT_OPTION = 'S/D' THEN
817 IF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
818 L_TRADER_TYPE := 'Customer';
819 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
820 L_TRADER_TYPE := 'Vendor';
821 ELSIF C_DOCUMENT_SOURCE_TYPE in ('IO','INV') THEN
822 L_TRADER_TYPE := 'Organization';
823 ELSIF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
824 IF C_CUSTOMER_NAME IS NOT NULL THEN
825 L_TRADER_TYPE := 'Customer';
826 ELSIF C_VENDOR_NAME IS NOT NULL THEN
827 L_TRADER_TYPE := 'Vendor';
828 ELSIF C_TRANSACTING_FROM_ORG IS NOT NULL OR C_TRANSACTING_TO_ORG IS NOT NULL THEN
829 L_TRADER_TYPE := 'Organization';
830 ELSE
831 L_TRADER_TYPE := NULL;
832 END IF;
833 ELSE
834 L_TRADER_TYPE := NULL;
835 END IF;
836 ELSE
837 L_TRADER_TYPE := NULL;
838 END IF;
839 RETURN (L_TRADER_TYPE);
840 END;
841 RETURN NULL;
842 END CF_TRADER_TYPEFORMULA;
843
844 FUNCTION AFTERREPORT(CS_NO_COUNT IN NUMBER) RETURN BOOLEAN IS
845 BEGIN
846 IF P_REPORT_OPTION = 'O/S' THEN
847 IF CS_NO_COUNT <> 0 THEN
848 UPDATE
849 MTL_MOVEMENT_STATISTICS
850 SET
851 MOVEMENT_STATUS = 'F'
852 ,REPORT_REFERENCE = TO_CHAR(P_LEGAL_ENTITY_ID) || P_ZONE_CODE || P_PERIOD_NAME || P_USAGE_TYPE || P_STAT_TYPE || P_MOVEMENT_TYPE
853 WHERE ENTITY_ORG_ID = P_LEGAL_ENTITY_ID
854 AND ZONE_CODE = P_ZONE_CODE
855 AND PERIOD_NAME = P_PERIOD_NAME
856 AND USAGE_TYPE = P_USAGE_TYPE
857 AND STAT_TYPE = P_STAT_TYPE
858 AND MOVEMENT_TYPE = P_MOVEMENT_TYPE
859 AND MOVEMENT_STATUS = 'V';
860 END IF;
861 COMMIT;
862 END IF;
863 BEGIN
864 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
865 EXCEPTION
866 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
867 /*SRW.MESSAGE(1
868 ,'Failed in srwexit')*/NULL;
869 END;
870 RETURN (TRUE);
871 END AFTERREPORT;
872
873 FUNCTION CF_PARENT_MVT_QUANTITYFORMULA(CS_ALTERNATE_QUANTITY IN VARCHAR2
874 ,CS_TRANSACTION_QUANTITY IN VARCHAR2) RETURN NUMBER IS
875 BEGIN
876 IF CS_ALTERNATE_QUANTITY IS NULL THEN
877 RETURN (CS_TRANSACTION_QUANTITY);
878 ELSE
879 RETURN (CS_ALTERNATE_QUANTITY);
880 END IF;
881 END CF_PARENT_MVT_QUANTITYFORMULA;
882
883 FUNCTION CF_PARENT_MVT_UOMFORMULA(C_ALTERNATE_UOM_CODE IN VARCHAR2
884 ,C_TRANSACTION_UOM_CODE IN VARCHAR2) RETURN CHAR IS
885 BEGIN
886 IF C_ALTERNATE_UOM_CODE IS NULL THEN
887 RETURN (C_TRANSACTION_UOM_CODE);
888 ELSE
889 RETURN (C_ALTERNATE_UOM_CODE);
890 END IF;
891 END CF_PARENT_MVT_UOMFORMULA;
892
893 FUNCTION CF_STATISTICAL_VALUE_RFORMUL(CS_STATISTICAL_VALUE IN VARCHAR2) RETURN NUMBER IS
894 L_STAT_VALUE NUMBER;
895 BEGIN
896 L_STAT_VALUE := INV_MGD_MVT_UTILS_PKG.ROUND_NUMBER(P_NUMBER => CS_STATISTICAL_VALUE
897 ,P_PRECISION => P_REP_PRECISION
898 ,P_ROUNDING_METHOD => P_REP_ROUNDING);
899 RETURN (L_STAT_VALUE);
900 END CF_STATISTICAL_VALUE_RFORMUL;
901
902 FUNCTION CF_TRANSACTION_VALUE_RFORMUL(CS_TRANSACTION_VALUE IN VARCHAR2) RETURN NUMBER IS
903 L_TRANS_VALUE NUMBER;
904 BEGIN
905 L_TRANS_VALUE := INV_MGD_MVT_UTILS_PKG.ROUND_NUMBER(P_NUMBER => CS_TRANSACTION_VALUE
906 ,P_PRECISION => P_REP_PRECISION
907 ,P_ROUNDING_METHOD => P_REP_ROUNDING);
908 RETURN (L_TRANS_VALUE);
909 END CF_TRANSACTION_VALUE_RFORMUL;
910
911 FUNCTION CF_INVOICE_LINE_EXT_VAL_RFORMU(CS_INVOICE_VALUE IN VARCHAR2
912 ,CF_PRECISION_TRX IN NUMBER) RETURN NUMBER IS
913 BEGIN
914 RETURN (ROUND(CS_INVOICE_VALUE
915 ,CF_PRECISION_TRX));
916 END CF_INVOICE_LINE_EXT_VAL_RFORMU;
917
918 FUNCTION CF_DOCUMENT_LINE_EXT_VAL_RFORM(CS_DOC_VALUE IN VARCHAR2
919 ,CF_PRECISION_TRX IN NUMBER) RETURN NUMBER IS
920 BEGIN
921 RETURN (ROUND(CS_DOC_VALUE
922 ,CF_PRECISION_TRX));
923 END CF_DOCUMENT_LINE_EXT_VAL_RFORM;
924
925 FUNCTION CF_REPORT_TRANSACTION_VAL_RFOR(CS_REPORT_TRANSACTION_VAL IN NUMBER) RETURN NUMBER IS
926 L_REP_TRANS_VALUE NUMBER;
927 BEGIN
928 L_REP_TRANS_VALUE := INV_MGD_MVT_UTILS_PKG.ROUND_NUMBER(P_NUMBER => CS_REPORT_TRANSACTION_VAL
929 ,P_PRECISION => P_REP_PRECISION
930 ,P_ROUNDING_METHOD => P_REP_ROUNDING);
931 RETURN (L_REP_TRANS_VALUE);
932 END CF_REPORT_TRANSACTION_VAL_RFOR;
933
934 FUNCTION CF_REPORT_STATISTICAL_VAL_RFOR(CS_REPORT_STATISTICAL_VAL IN NUMBER) RETURN NUMBER IS
935 L_REP_STAT_VALUE NUMBER;
936 BEGIN
937 L_REP_STAT_VALUE := INV_MGD_MVT_UTILS_PKG.ROUND_NUMBER(P_NUMBER => CS_REPORT_STATISTICAL_VAL
938 ,P_PRECISION => P_REP_PRECISION
939 ,P_ROUNDING_METHOD => P_REP_ROUNDING);
940 RETURN (L_REP_STAT_VALUE);
941 END CF_REPORT_STATISTICAL_VAL_RFOR;
942
943 FUNCTION CF_OUTSIDE_VALUE_RFORMULA(CS_OUTSIDE_VALUE IN VARCHAR2
944 ,CF_PRECISION_TRX IN NUMBER) RETURN NUMBER IS
945 BEGIN
946 RETURN (ROUND(CS_OUTSIDE_VALUE
947 ,CF_PRECISION_TRX));
948 END CF_OUTSIDE_VALUE_RFORMULA;
949
950 FUNCTION AFTERPFORM RETURN BOOLEAN IS
951 BEGIN
952 --Bugfix 16214456: OE schema is getting obsoleted. Hard coding to ONT.
953 --P_OE_OR_OM := OE_INSTALL.GET_ACTIVE_PRODUCT;
954 P_OE_OR_OM := 'ONT';
955 RETURN (TRUE);
956 END AFTERPFORM;
957
958 FUNCTION CF_PRECISION_TRXFORMULA(C_MOVEMENT_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
959 L_PRECISION_TRX NUMBER;
960 BEGIN
961 SELECT
962 PRECISION
963 INTO L_PRECISION_TRX
964 FROM
965 FND_CURRENCIES
966 WHERE CURRENCY_CODE = C_MOVEMENT_CURRENCY_CODE;
967 RETURN L_PRECISION_TRX;
968 END CF_PRECISION_TRXFORMULA;
969
970 FUNCTION CF_RELEASE_NUMBERFORMULA(C_RCV_TRANSACTION_ID IN NUMBER) RETURN NUMBER IS
971 BEGIN
972 DECLARE
973 L_PO_RELEASE_ID NUMBER;
974 L_RELEASE_NUM NUMBER;
975 BEGIN
976 IF C_RCV_TRANSACTION_ID IS NOT NULL THEN
977 BEGIN
978 SELECT
979 PO_RELEASE_ID
980 INTO L_PO_RELEASE_ID
981 FROM
982 RCV_TRANSACTIONS
983 WHERE TRANSACTION_ID = C_RCV_TRANSACTION_ID;
984 EXCEPTION
985 WHEN NO_DATA_FOUND THEN
986 L_RELEASE_NUM := NULL;
987 RETURN (L_RELEASE_NUM);
988 END;
989 IF L_PO_RELEASE_ID IS NOT NULL THEN
990 SELECT
991 RELEASE_NUM
992 INTO L_RELEASE_NUM
993 FROM
994 PO_RELEASES_ALL
995 WHERE PO_RELEASE_ID = L_PO_RELEASE_ID;
996 END IF;
997 ELSE
998 L_RELEASE_NUM := NULL;
999 END IF;
1000 RETURN (L_RELEASE_NUM);
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 /*SRW.MESSAGE(100
1004 ,'Failed in CF_RELEASE_NUMBER ')*/NULL;
1005 END;
1006 RETURN NULL;
1007 END CF_RELEASE_NUMBERFORMULA;
1008
1009 FUNCTION CP_REQUESTED_BY_P RETURN VARCHAR2 IS
1010 BEGIN
1011 RETURN CP_REQUESTED_BY;
1012 END CP_REQUESTED_BY_P;
1013
1014 FUNCTION CP_APPLICATION_P RETURN VARCHAR2 IS
1015 BEGIN
1016 RETURN CP_APPLICATION;
1017 END CP_APPLICATION_P;
1018
1019 FUNCTION CP_RESPONSIBILITY_P RETURN VARCHAR2 IS
1020 BEGIN
1021 RETURN CP_RESPONSIBILITY;
1022 END CP_RESPONSIBILITY_P;
1023
1024 FUNCTION CP_REQUEST_TIME_P RETURN VARCHAR2 IS
1025 BEGIN
1026 RETURN CP_REQUEST_TIME;
1027 END CP_REQUEST_TIME_P;
1028
1029 FUNCTION CP_LEGAL_ENTITY_NAME_P RETURN VARCHAR2 IS
1030 BEGIN
1031 RETURN CP_LEGAL_ENTITY_NAME;
1032 END CP_LEGAL_ENTITY_NAME_P;
1033
1034 FUNCTION CP_ENTITY_ADDRESS_LINE_1_P RETURN VARCHAR2 IS
1035 BEGIN
1036 RETURN CP_ENTITY_ADDRESS_LINE_1;
1037 END CP_ENTITY_ADDRESS_LINE_1_P;
1038
1039 FUNCTION CP_ENTITY_ADDRESS_LINE_2_P RETURN VARCHAR2 IS
1040 BEGIN
1041 RETURN CP_ENTITY_ADDRESS_LINE_2;
1042 END CP_ENTITY_ADDRESS_LINE_2_P;
1043
1044 FUNCTION CP_ENTITY_ADDRESS_LINE_3_P RETURN VARCHAR2 IS
1045 BEGIN
1046 RETURN CP_ENTITY_ADDRESS_LINE_3;
1047 END CP_ENTITY_ADDRESS_LINE_3_P;
1048
1049 FUNCTION CP_TELEPHONE_NUMBER_1_P RETURN VARCHAR2 IS
1050 BEGIN
1051 RETURN CP_TELEPHONE_NUMBER_1;
1052 END CP_TELEPHONE_NUMBER_1_P;
1053
1054 FUNCTION CP_TELEPHONE_NUMBER_2_P RETURN VARCHAR2 IS
1055 BEGIN
1056 RETURN CP_TELEPHONE_NUMBER_2;
1057 END CP_TELEPHONE_NUMBER_2_P;
1058
1059 FUNCTION CP_CONTACT_PERSON_NAME_P RETURN VARCHAR2 IS
1060 BEGIN
1061 RETURN CP_CONTACT_PERSON_NAME;
1062 END CP_CONTACT_PERSON_NAME_P;
1063
1064 FUNCTION CP_VAT_REGISTRATION_NUMBER_P RETURN VARCHAR2 IS
1065 BEGIN
1066 RETURN CP_VAT_REGISTRATION_NUMBER;
1067 END CP_VAT_REGISTRATION_NUMBER_P;
1068
1069 FUNCTION CP_SET_OF_BOOKS_NAME_P RETURN VARCHAR2 IS
1070 BEGIN
1071 RETURN CP_SET_OF_BOOKS_NAME;
1072 END CP_SET_OF_BOOKS_NAME_P;
1073
1074 FUNCTION CP_TAX_OFFICE_NAME_P RETURN VARCHAR2 IS
1075 BEGIN
1076 RETURN CP_TAX_OFFICE_NAME;
1077 END CP_TAX_OFFICE_NAME_P;
1078
1079 FUNCTION CP_TAX_OFFICE_ADDRESS_LINE_1_P RETURN VARCHAR2 IS
1080 BEGIN
1081 RETURN CP_TAX_OFFICE_ADDRESS_LINE_1;
1082 END CP_TAX_OFFICE_ADDRESS_LINE_1_P;
1083
1084 FUNCTION CP_TAX_OFFICE_ADDRESS_LINE_2_P RETURN VARCHAR2 IS
1085 BEGIN
1086 RETURN CP_TAX_OFFICE_ADDRESS_LINE_2;
1087 END CP_TAX_OFFICE_ADDRESS_LINE_2_P;
1088
1089 FUNCTION CP_TAX_OFFICE_ADDRESS_LINE_3_P RETURN VARCHAR2 IS
1090 BEGIN
1091 RETURN CP_TAX_OFFICE_ADDRESS_LINE_3;
1092 END CP_TAX_OFFICE_ADDRESS_LINE_3_P;
1093
1094 FUNCTION CP_START_DATE_P RETURN DATE IS
1095 BEGIN
1096 RETURN CP_START_DATE;
1097 END CP_START_DATE_P;
1098
1099 FUNCTION CP_END_DATE_P RETURN DATE IS
1100 BEGIN
1101 RETURN CP_END_DATE;
1102 END CP_END_DATE_P;
1103
1104 FUNCTION CP_STANDARD_UOM_P RETURN VARCHAR2 IS
1105 BEGIN
1106 RETURN CP_STANDARD_UOM;
1107 END CP_STANDARD_UOM_P;
1108
1109 FUNCTION CP_SYSDATE_P RETURN VARCHAR2 IS
1110 BEGIN
1111 RETURN CP_SYSDATE;
1112 END CP_SYSDATE_P;
1113
1114 FUNCTION CP_EXT_PRECISION_FUNC_P RETURN NUMBER IS
1115 BEGIN
1116 RETURN CP_EXT_PRECISION_FUNC;
1117 END CP_EXT_PRECISION_FUNC_P;
1118
1119 FUNCTION CP_ZONE_NAME_COV_P RETURN VARCHAR2 IS
1120 BEGIN
1121 RETURN CP_ZONE_NAME_COV;
1122 END CP_ZONE_NAME_COV_P;
1123
1124 FUNCTION CP_MV_TYPE_COV_P RETURN VARCHAR2 IS
1125 BEGIN
1126 RETURN CP_MV_TYPE_COV;
1127 END CP_MV_TYPE_COV_P;
1128
1129 FUNCTION CP_REP_OPTION_COV_P RETURN VARCHAR2 IS
1130 BEGIN
1131 RETURN CP_REP_OPTION_COV;
1132 END CP_REP_OPTION_COV_P;
1133
1134 FUNCTION CP_USAGE_TYPE_COV_P RETURN VARCHAR2 IS
1135 BEGIN
1136 RETURN CP_USAGE_TYPE_COV;
1137 END CP_USAGE_TYPE_COV_P;
1138
1139 FUNCTION CP_STAT_TYPE_COV_P RETURN VARCHAR2 IS
1140 BEGIN
1141 RETURN CP_STAT_TYPE_COV;
1142 END CP_STAT_TYPE_COV_P;
1143
1144 FUNCTION CP_PRECISION_FUNC_P RETURN NUMBER IS
1145 BEGIN
1146 RETURN CP_PRECISION_FUNC;
1147 END CP_PRECISION_FUNC_P;
1148
1149 FUNCTION CP_REPORT_TITLE_P RETURN VARCHAR2 IS
1150 BEGIN
1151 RETURN CP_REPORT_TITLE;
1152 END CP_REPORT_TITLE_P;
1153
1154 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
1155 BEGIN
1156 QTY_PRECISION:= inv_common_xmlp_pkg.get_precision(P_QUANTITY_PRECISION);
1157 /*
1158 BEGIN
1159 SRW.USER_EXIT('FND SRWINIT');
1160 EXCEPTION
1161 WHEN SRW.USER_EXIT_FAILURE THEN
1162 SRW.Message(10,'Failed in SRWINIT');
1163 RAISE;
1164 END;
1165 */
1166
1167 -------------------------------------------------
1168 --Convert exchange rate from char to number
1169 -------------------------------------------------
1170 BEGIN
1171 p_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1172 P_EXCHANGE_RATE := fnd_number.canonical_to_number(P_EXCHANGE_RATE_CHAR);
1173 END;
1174
1175
1176 -------------------------------------------------
1177 --if report option is official summary, check if
1178 --movement status is verified
1179 -------------------------------------------------
1180 BEGIN
1181 DECLARE
1182 l_invalid_status EXCEPTION;
1183 l_nnverified_status_count NUMBER;
1184 l_message VARCHAR2(2000);
1185 BEGIN
1186 IF p_report_option = 'O/S'
1187 THEN
1188 SELECT
1189 COUNT(*)
1190 INTO
1191 l_nnverified_status_count
1192 FROM
1193 MTL_MOVEMENT_STATISTICS mms
1194 WHERE mms.entity_org_id = p_legal_entity_id
1195 AND mms.zone_code = p_zone_code
1196 AND mms.period_name = p_period_name
1197 AND mms.usage_type = p_usage_type
1198 AND mms.stat_type = p_stat_type
1199 AND mms.movement_type = p_movement_type
1200 AND movement_status = 'O';
1201 IF l_nnverified_status_count > 0
1202 THEN
1203 RAISE l_invalid_status;
1204 END IF;
1205 END IF;
1206 EXCEPTION
1207 WHEN l_invalid_status THEN
1208 FND_MESSAGE.SET_NAME('INV', 'INV_MGD_MVT_STAT_INVALID_STS');
1209 l_message := FND_MESSAGE.Get;
1210 --SRW.Message(1, l_message);
1211 RAISE;
1212 WHEN OTHERS THEN
1213 --SRW.Message(31, 'Failed in check movement status');
1214 null;
1215 END;
1216
1217 END;
1218
1219
1220
1221 -------------------------------------------------
1222 -- Initialization for Request By, Request time --
1223 -- Responsibility and Application --
1224 -------------------------------------------------
1225 BEGIN
1226 SELECT
1227 frv.responsibility_name
1228 , TO_CHAR(fcr.request_date, 'YYYY/MM/DD HH24:MI:SS')
1229 , fav.application_name
1230 , fu.user_name
1231 INTO
1232 cp_responsibility
1233 , cp_request_time
1234 , cp_application
1235 , cp_requested_by
1236 FROM
1237 fnd_concurrent_requests fcr
1238 , fnd_responsibility_vl frv
1239 , fnd_application_vl fav
1240 , fnd_user fu
1241 WHERE fcr.request_id = p_conc_request_id
1242 AND fcr.responsibility_application_id = frv.application_id
1243 AND fcr.responsibility_id = frv.responsibility_id
1244 AND frv.application_id = fav.application_id
1245 AND fu.user_id = fcr.requested_by;
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 -- SRW.Message(31, 'Failed Request By and Request time Init.');
1249 null;
1250 END;
1251
1252 --------------------------------------------------
1253 --Change sysdate formatting
1254 --------------------------------------------------
1255 SELECT
1256 TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI')
1257 INTO
1258 CP_SYSDATE
1259 FROM
1260 DUAL;
1261
1262
1263 --------------------------------------
1264 -- Initialization for Set of Books --
1265 -- and Legal Entity --
1266 --------------------------------------
1267 BEGIN
1268 --Get VAT number
1269 CP_VAT_REGISTRATION_NUMBER :=
1270 INV_MGD_MVT_UTILS_PKG.Get_Org_VAT_Number
1271 ( p_entity_org_id => p_legal_entity_id
1272 , p_date => sysdate);
1273
1274 --Get other values
1275 SELECT
1276 gllv.legal_entity_name, hl.address_line_1,
1277 hl.address_line_2, hl.address_line_3,
1278 hl.telephone_number_1, hl.telephone_number_2,
1279 ppf.full_name, -- hle.vat_registration_number,
1280 gllv.ledger_name
1281 INTO
1282 CP_LEGAL_ENTITY_NAME, CP_ENTITY_ADDRESS_LINE_1,
1283 CP_ENTITY_ADDRESS_LINE_2, CP_ENTITY_ADDRESS_LINE_3,
1284 CP_TELEPHONE_NUMBER_1, CP_TELEPHONE_NUMBER_2,
1285 CP_CONTACT_PERSON_NAME, --CP_VAT_REGISTRATION_NUMBER,
1286 CP_SET_OF_BOOKS_NAME
1287 FROM
1288 hr_locations_no_join hl,
1289 per_people_f ppf,
1290 gl_ledger_le_v gllv
1291 WHERE gllv.legal_entity_id = P_LEGAL_ENTITY_ID
1292 AND gllv.ledger_category_code = 'PRIMARY'
1293 AND gllv.location_id = hl.location_id
1294 AND hl.designated_receiver_id = ppf.person_id(+)
1295 AND rownum < 2;
1296
1297 EXCEPTION
1298 WHEN OTHERS THEN
1299 -- SRW.Message(31, 'Failed in Set_of_books and Legal_Entity Init.');
1300 null;
1301
1302 END;
1303
1304
1305 ----------------------------------------
1306 -- Initialize currency precision for
1307 -- functional currency and transaction
1308 -- currency
1309 ----------------------------------------
1310 BEGIN
1311 SELECT
1312 nvl(fc.extended_precision,1)
1313 , nvl(fc.precision,1)
1314 INTO
1315 CP_EXT_PRECISION_FUNC
1316 , CP_PRECISION_FUNC
1317 FROM
1318 fnd_currencies fc
1319 WHERE fc.currency_code = P_CURRENCY_CODE
1320 AND rownum < 2;
1321
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 -- SRW.Message(31, 'Failed in currency precision.');
1325 null;
1326 END;
1327
1328
1329 ----------------------------------------
1330 -- Initialization for Tax information --
1331 ----------------------------------------
1332 BEGIN
1333 DECLARE
1334 l_location_id NUMBER;
1335 BEGIN
1336 SELECT
1337 tax_office_name
1338 , weight_uom_code
1339 , tax_office_location_id
1340 INTO
1341 CP_TAX_OFFICE_NAME
1342 , CP_STANDARD_UOM
1343 , l_location_id
1344 FROM
1345 mtl_stat_type_usages
1346 WHERE legal_entity_id = P_LEGAL_ENTITY_ID
1347 AND zone_code = P_ZONE_CODE
1348 AND stat_type = P_STAT_TYPE
1349 AND usage_type = P_USAGE_TYPE;
1350
1351 BEGIN
1352 SELECT
1353 address_line_1
1354 , address_line_2
1355 , address_line_3
1356 INTO
1357 CP_TAX_OFFICE_ADDRESS_LINE_1
1358 , CP_TAX_OFFICE_ADDRESS_LINE_2
1359 , CP_TAX_OFFICE_ADDRESS_LINE_3
1360 FROM
1361 hr_locations_no_join
1362 WHERE location_id = l_location_id;
1363
1364 EXCEPTION
1365 WHEN NO_DATA_FOUND THEN
1366 BEGIN
1367 SELECT
1368 address1
1369 , address2
1370 , address3
1371 INTO
1372 CP_TAX_OFFICE_ADDRESS_LINE_1
1373 , CP_TAX_OFFICE_ADDRESS_LINE_2
1374 , CP_TAX_OFFICE_ADDRESS_LINE_3
1375 FROM
1376 hz_locations
1377 WHERE location_id = l_location_id;
1378
1379 EXCEPTION
1380 WHEN OTHERS THEN
1381 -- SRW.Message(31, 'Failed in Tax Office Init');
1382 null;
1383 END;
1384
1385 WHEN OTHERS THEN
1386 --SRW.Message(31, 'Failed in Tax Office Init');
1387 null;
1388 END;
1389 END;
1390 END;
1391
1392
1393 ------------------------------------------------
1394 -- Initialization for Start Date, End Date of --
1395 -- one period --
1396 ------------------------------------------------
1397 BEGIN
1398 SELECT
1399 start_date
1400 , end_date
1401 INTO
1402 CP_start_date
1403 , CP_end_date
1404 FROM
1405 GL_PERIODS
1406 WHERE period_name = P_period_name
1407 AND rownum < 2;
1408
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 -- SRW.Message(31, 'Failed in start_date and end_date Init');
1412 null;
1413 END;
1414
1415
1416 /*
1417 Fix bug 2230047, get commodity code from mtl_movement_statistics table directly
1418 not use flexfield anymore
1419 ------------------------------------------------
1420 -- Initialization for Commodity Structure id --
1421 -- of one Legal Entity --
1422 ------------------------------------------------
1423 BEGIN
1424 SELECT
1425 mcs.structure_id
1426 INTO
1427 PLEX_COMMODITY_STRUCT_NUM
1428 FROM
1429 mtl_stat_type_usages mstu
1430 ,mtl_category_sets mcs
1431 WHERE mstu.legal_entity_id = P_LEGAL_ENTITY_ID
1432 AND mstu.zone_code = P_ZONE_CODE
1433 AND mstu.category_set_id = mcs.category_set_id
1434 AND rownum < 2;
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437 SRW.Message(31, 'Failed in conversion Init');
1438 END;
1439 */
1440 ------------------------
1441 -- Flexfield for ITEM --
1442 ------------------------
1443 /*
1444 BEGIN
1445 SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" NUM="PLEX_ITEM_STRUCT_NUM" APPL_SHORT_NAME="INV"
1446 OUTPUT="PLEX_ITEM_FLEX"
1447 MODE="SELECT"
1448 DISPLAY="ALL"
1449 TABLEALIAS="MSI"');
1450 EXCEPTION
1451 WHEN SRW.USER_EXIT_FAILURE THEN
1452 SRW.Message(60,'Failed in ITEM Flexfield');
1453 WHEN OTHERS THEN
1454 SRW.Message(31, 'Failed in ITEM Flexfield');
1455 END;
1456 */
1457 /*
1458 Fix bug 2230047, get commodity code from mtl_movement_statistics table directly
1459 not use flexfield anymore
1460 ----------------------------------
1461 -- Flexfield for COMMODITY_CODE --
1462 ----------------------------------
1463 BEGIN
1464 SRW.USER_EXIT('FND FLEXSQL CODE="MCAT"
1465 NUM="PLEX_COMMODITY_STRUCT_NUM"
1466 APPL_SHORT_NAME="INV"
1467 OUTPUT="PLEX_COMMODITY_FLEX"
1468 MODE="SELECT"
1469 DISPLAY="ALL"
1470 TABLEALIAS="MC"');
1471 EXCEPTION
1472 WHEN SRW.USER_EXIT_FAILURE THEN
1473 SRW.Message(70,'Failed in COMMODITY_CODE Flexfield');
1474 WHEN OTHERS THEN
1475 SRW.Message(31, 'Failed in COMMODITY_CODE Flexfield ');
1476 END;
1477
1478 BEGIN
1479 SRW.USER_EXIT('FND FLEXSQL CODE="MCAT"
1480 NUM="PLEX_COMMODITY_STRUCT_NUM"
1481 APPL_SHORT_NAME="INV"
1482 OUTPUT="PLEX_COMMODITY_ORDER_BY"
1483 MODE="ORDER BY"
1484 DISPLAY="ALL"
1485 TABLEALIAS="MC"');
1486 EXCEPTION
1487 WHEN SRW.USER_EXIT_FAILURE THEN
1488 SRW.Message(70,'Failed in COMMODITY_CODE Flexfield');
1489 WHEN OTHERS THEN
1490 SRW.Message(31, 'Failed in COMMODITY_CODE Flexfield');
1491 END;
1492 */
1493 -------------------------------------------
1494 -- Populate report parameter placeholders
1495 -- Displayed on cover page
1496 -------------------------------------------
1497 BEGIN
1498 SELECT zone_display_name
1499 INTO cp_zone_name_cov
1500 FROM mtl_economic_zones_vl
1501 WHERE zone_code = p_zone_code
1502 AND rownum<2;
1503
1504 SELECT meaning
1505 INTO cp_rep_option_cov
1506 FROM fnd_lookup_values
1507 WHERE lookup_type = 'INTRASTAT_REPORT_OPTION'
1508 AND lookup_code = p_report_option
1509 AND enabled_flag = 'Y'
1510 AND language = userenv('LANG')
1511 AND rownum<2;
1512
1513 SELECT meaning
1514 INTO cp_mv_type_cov
1515 FROM fnd_lookups
1516 WHERE lookup_type = 'MVT_MOVEMENT_TYPE'
1517 AND lookup_code = p_movement_type
1518 AND enabled_flag = 'Y'
1519 AND rownum<2;
1520
1521 SELECT meaning
1522 INTO cp_usage_type_cov
1523 FROM fnd_lookups
1524 WHERE lookup_type = 'MVT_USAGE_TYPES'
1525 AND lookup_code = p_usage_type
1526 AND enabled_flag = 'Y'
1527 AND rownum<2;
1528
1529 SELECT meaning
1530 INTO cp_stat_type_cov
1531 FROM fnd_lookups
1532 WHERE lookup_type = 'MVT_STAT_TYPES'
1533 AND lookup_code = p_stat_type
1534 AND enabled_flag = 'Y'
1535 AND rownum<2;
1536
1537
1538 EXCEPTION
1539 WHEN OTHERS THEN
1540 -- SRW.Message(31, 'Failed cover page parameters initialization.');
1541 null;
1542 END;
1543
1544 -------------------------------------------
1545 -- Populate p_mvt_id_where, used in main
1546 -- query
1547 -------------------------------------------
1548 BEGIN
1549 IF P_MOVEMENT_TYPE IN ('A', 'D')
1550 THEN
1551 P_MVT_ID_WHERE := 'and mms.parent_movement_id = mms.movement_id';
1552 ELSE
1553 P_MVT_ID_WHERE :='and movement_id = (select min(movement_id)
1554 from mtl_movement_statistics
1555 where parent_movement_id = mms.parent_movement_id
1556 and movement_type = '''||P_MOVEMENT_TYPE||''' group by mms.parent_movement_id)';
1557
1558 END IF;
1559 END;
1560 --srw.message(01,'in bef rep');
1561
1562 --------------------------------------------------------
1563 --Populate reporting precision and rounding method
1564 --from setup form. Can not create place holder in May-06
1565 --somehow, so create user parameters to hold the value
1566 --Fix bug 5203245
1567 --------------------------------------------------------
1568 BEGIN
1569 INV_MGD_MVT_UTILS_PKG.Get_Weight_Precision
1570 (p_legal_entity_id => p_legal_entity_id
1571 , p_zone_code => p_zone_code
1572 , p_usage_type => p_usage_type
1573 , p_stat_type => p_stat_type
1574 , x_weight_precision => p_rep_precision
1575 , x_rep_rounding => p_rep_rounding);
1576
1577 IF p_amount_display = 'W'
1578 THEN
1579 p_rep_precision := 0;
1580 ELSE
1581 --same as before, report according to currency precision
1582 p_rep_precision := cp_precision_func;
1583 END IF;
1584
1585 END;
1586
1587 RETURN (TRUE);
1588 END BEFOREREPORT;
1589 END INV_INVSTMVT_XMLP_PKG;
1590
[Home] [Help]