1 PACKAGE BODY INV_INVSTMVT_XMLP_PKG AS
2 /* $Header: INVSTMVTB.pls 120.1 2007/12/25 11:01:56 dwkrishn noship $ */
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 IF P_OE_OR_OM = 'OE' THEN
398 BEGIN
399 SELECT
400 LINE_NUMBER
401 INTO L_SOURCE_LINE_NUMBER
402 FROM
403 SO_LINES_ALL
404 WHERE LINE_ID = C_ORDER_LINE_ID
405 AND ROWNUM < 2;
406 EXCEPTION
407 WHEN NO_DATA_FOUND THEN
408 L_SOURCE_LINE_NUMBER := NULL;
409 END;
410 ELSE
411 BEGIN
412 SELECT
413 LINE_NUMBER
414 INTO L_SOURCE_LINE_NUMBER
415 FROM
416 OE_ORDER_LINES_ALL
417 WHERE LINE_ID = C_ORDER_LINE_ID
418 AND ROWNUM < 2;
419 EXCEPTION
420 WHEN NO_DATA_FOUND THEN
421 L_SOURCE_LINE_NUMBER := NULL;
422 END;
423 END IF;
424 ELSIF C_DOCUMENT_SOURCE_TYPE = 'IO' THEN
425 IF C_MOVEMENT_TYPE in ('A','AA') THEN
426 BEGIN
427 SELECT
428 LINE_NUM
429 INTO L_SOURCE_LINE_NUMBER
430 FROM
431 PO_REQUISITION_LINES_ALL
432 WHERE REQUISITION_LINE_ID = C_REQUISITION_LINE_ID
433 AND ROWNUM < 2;
434 EXCEPTION
435 WHEN NO_DATA_FOUND THEN
436 L_SOURCE_LINE_NUMBER := NULL;
437 END;
438 ELSE
439 IF P_OE_OR_OM = 'OE' THEN
440 BEGIN
441 SELECT
442 LINE_NUMBER
443 INTO L_SOURCE_LINE_NUMBER
444 FROM
445 SO_LINES_ALL
446 WHERE LINE_ID = C_ORDER_LINE_ID
447 AND ROWNUM < 2;
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 L_SOURCE_LINE_NUMBER := NULL;
451 END;
452 ELSE
453 BEGIN
454 SELECT
455 LINE_NUMBER
456 INTO L_SOURCE_LINE_NUMBER
457 FROM
458 OE_ORDER_LINES_ALL
459 WHERE LINE_ID = C_ORDER_LINE_ID
460 AND ROWNUM < 2;
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 L_SOURCE_LINE_NUMBER := NULL;
464 END;
465 END IF;
466 END IF;
467 ELSE
468 L_SOURCE_LINE_NUMBER := NULL;
469 END IF;
470 ELSE
471 L_SOURCE_LINE_NUMBER := NULL;
472 END IF;
473 RETURN (L_SOURCE_LINE_NUMBER);
474 END;
475 RETURN NULL;
476 END CF_SOURCE_LINE_NUMBERFORMULA;
477
478 FUNCTION CF_SOURCE_NUMBERFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
479 ,C_DOCUMENT_REFERENCE IN VARCHAR2
480 ,C_PO_HEADER_ID IN NUMBER
481 ,C_ORDER_HEADER_ID IN NUMBER
482 ,C_MOVEMENT_TYPE IN VARCHAR2
483 ,C_REQUISITION_HEADER_ID IN NUMBER
484 ,C_MTL_TRANSACTION_ID IN NUMBER) RETURN VARCHAR2 IS
485 BEGIN
486 DECLARE
487 L_SOURCE_NUMBER VARCHAR2(50);
488 BEGIN
489 IF P_REPORT_OPTION = 'S/D' THEN
490 IF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
491 L_SOURCE_NUMBER := C_DOCUMENT_REFERENCE;
492 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
493 BEGIN
494 SELECT
495 SEGMENT1
496 INTO L_SOURCE_NUMBER
497 FROM
498 PO_HEADERS_ALL
499 WHERE PO_HEADER_ID = C_PO_HEADER_ID
500 AND ROWNUM < 2;
501 EXCEPTION
502 WHEN NO_DATA_FOUND THEN
503 L_SOURCE_NUMBER := NULL;
504 END;
505 ELSIF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
506 IF P_OE_OR_OM = 'OE' THEN
507 BEGIN
508 SELECT
509 ORDER_NUMBER
510 INTO L_SOURCE_NUMBER
511 FROM
512 SO_HEADERS_ALL
513 WHERE HEADER_ID = C_ORDER_HEADER_ID
514 AND ROWNUM < 2;
515 EXCEPTION
516 WHEN NO_DATA_FOUND THEN
517 L_SOURCE_NUMBER := NULL;
518 END;
519 ELSE
520 BEGIN
521 SELECT
522 ORDER_NUMBER
523 INTO L_SOURCE_NUMBER
524 FROM
525 OE_ORDER_HEADERS_ALL
526 WHERE HEADER_ID = C_ORDER_HEADER_ID
527 AND ROWNUM < 2;
528 EXCEPTION
529 WHEN NO_DATA_FOUND THEN
530 L_SOURCE_NUMBER := NULL;
531 END;
532 END IF;
533 ELSIF C_DOCUMENT_SOURCE_TYPE = 'IO' THEN
534 IF C_MOVEMENT_TYPE in ('A','AA') THEN
535 BEGIN
536 SELECT
537 SEGMENT1
538 INTO L_SOURCE_NUMBER
539 FROM
540 PO_REQUISITION_HEADERS_ALL
541 WHERE REQUISITION_HEADER_ID = C_REQUISITION_HEADER_ID
542 AND ROWNUM < 2;
543 EXCEPTION
544 WHEN NO_DATA_FOUND THEN
545 L_SOURCE_NUMBER := NULL;
546 END;
547 ELSE
548 IF P_OE_OR_OM = 'OE' THEN
549 BEGIN
550 SELECT
551 ORDER_NUMBER
552 INTO L_SOURCE_NUMBER
553 FROM
554 SO_HEADERS_ALL
555 WHERE HEADER_ID = C_ORDER_HEADER_ID
556 AND ROWNUM < 2;
557 EXCEPTION
558 WHEN NO_DATA_FOUND THEN
559 L_SOURCE_NUMBER := NULL;
560 END;
561 ELSE
562 BEGIN
563 SELECT
564 ORDER_NUMBER
565 INTO L_SOURCE_NUMBER
566 FROM
567 OE_ORDER_HEADERS_ALL
568 WHERE HEADER_ID = C_ORDER_HEADER_ID
569 AND ROWNUM < 2;
570 EXCEPTION
571 WHEN NO_DATA_FOUND THEN
572 L_SOURCE_NUMBER := NULL;
573 END;
574 END IF;
575 END IF;
576 ELSIF C_DOCUMENT_SOURCE_TYPE = 'INV' THEN
577 L_SOURCE_NUMBER := TO_CHAR(C_MTL_TRANSACTION_ID);
578 ELSE
579 L_SOURCE_NUMBER := NULL;
580 END IF;
581 ELSE
582 L_SOURCE_NUMBER := NULL;
583 END IF;
584 RETURN (L_SOURCE_NUMBER);
585 END;
586 RETURN NULL;
587 END CF_SOURCE_NUMBERFORMULA;
588
589 FUNCTION CF_TRADER_NAMEFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
590 ,C_CUSTOMER_NAME IN VARCHAR2
591 ,C_SHIP_TO_CUSTOMER_ID IN NUMBER
592 ,C_VENDOR_NAME IN VARCHAR2
593 ,C_FROM_ORGANIZATION_ID IN NUMBER
594 ,C_TO_ORGANIZATION_ID IN NUMBER
595 ,C_TRANSACTING_FROM_ORG IN VARCHAR2
596 ,C_TRANSACTING_TO_ORG IN VARCHAR2) RETURN VARCHAR2 IS
597 BEGIN
598 DECLARE
599 L_TRADER_NAME VARCHAR2(240);
600 BEGIN
601 IF P_REPORT_OPTION = 'S/D' THEN
602 IF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
603 IF C_CUSTOMER_NAME IS NOT NULL THEN
604 L_TRADER_NAME := C_CUSTOMER_NAME;
605 ELSE
606 IF C_SHIP_TO_CUSTOMER_ID IS NOT NULL THEN
607 BEGIN
608 SELECT
609 HP.PARTY_NAME
610 INTO L_TRADER_NAME
611 FROM
612 HZ_PARTIES HP,
613 HZ_CUST_ACCOUNTS HCA
614 WHERE HP.PARTY_ID = HCA.PARTY_ID
615 AND HCA.CUST_ACCOUNT_ID = C_SHIP_TO_CUSTOMER_ID;
616 EXCEPTION
617 WHEN NO_DATA_FOUND THEN
618 L_TRADER_NAME := NULL;
619 END;
620 ELSE
621 L_TRADER_NAME := NULL;
622 END IF;
623 END IF;
624 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
625 L_TRADER_NAME := C_VENDOR_NAME;
626 ELSIF C_DOCUMENT_SOURCE_TYPE in ('IO','INV') THEN
627 IF P_MOVEMENT_TYPE in ('A','AA') THEN
628 IF C_FROM_ORGANIZATION_ID IS NOT NULL THEN
629 BEGIN
630 SELECT
631 NAME
632 INTO L_TRADER_NAME
633 FROM
634 HR_ORGANIZATION_UNITS
635 WHERE ORGANIZATION_ID = C_FROM_ORGANIZATION_ID
636 AND ROWNUM < 2;
637 EXCEPTION
638 WHEN NO_DATA_FOUND THEN
639 L_TRADER_NAME := NULL;
640 END;
641 ELSE
642 L_TRADER_NAME := NULL;
643 END IF;
644 ELSE
645 IF C_TO_ORGANIZATION_ID IS NOT NULL THEN
646 BEGIN
647 SELECT
648 NAME
649 INTO L_TRADER_NAME
650 FROM
651 HR_ORGANIZATION_UNITS
652 WHERE ORGANIZATION_ID = C_TO_ORGANIZATION_ID
653 AND ROWNUM < 2;
654 EXCEPTION
655 WHEN NO_DATA_FOUND THEN
656 L_TRADER_NAME := NULL;
657 END;
658 ELSE
659 L_TRADER_NAME := NULL;
660 END IF;
661 END IF;
662 ELSIF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
663 IF C_CUSTOMER_NAME IS NOT NULL THEN
664 L_TRADER_NAME := C_CUSTOMER_NAME;
665 ELSIF C_VENDOR_NAME IS NOT NULL THEN
666 L_TRADER_NAME := C_VENDOR_NAME;
667 ELSIF C_TRANSACTING_FROM_ORG IS NOT NULL THEN
668 L_TRADER_NAME := C_TRANSACTING_FROM_ORG;
669 ELSIF C_TRANSACTING_TO_ORG IS NOT NULL THEN
670 L_TRADER_NAME := C_TRANSACTING_TO_ORG;
671 ELSE
672 L_TRADER_NAME := NULL;
673 END IF;
674 ELSE
675 L_TRADER_NAME := NULL;
676 END IF;
677 ELSE
678 L_TRADER_NAME := NULL;
679 END IF;
680 RETURN (L_TRADER_NAME);
681 END;
682 RETURN NULL;
683 END CF_TRADER_NAMEFORMULA;
684
685 FUNCTION CF_TRADER_NUMBERFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
686 ,C_CUSTOMER_NUMBER IN VARCHAR2
687 ,C_SHIP_TO_CUSTOMER_ID IN NUMBER
688 ,C_VENDOR_NUMBER IN VARCHAR2
689 ,C_TRANSACTING_FROM_ORG IN VARCHAR2
690 ,C_TRANSACTING_TO_ORG IN VARCHAR2) RETURN VARCHAR2 IS
691 BEGIN
692 DECLARE
693 L_TRADER_NUMBER VARCHAR2(50);
694 BEGIN
695 IF P_REPORT_OPTION = 'S/D' THEN
696 IF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
697 IF C_CUSTOMER_NUMBER IS NOT NULL THEN
698 L_TRADER_NUMBER := C_CUSTOMER_NUMBER;
699 ELSE
700 IF C_SHIP_TO_CUSTOMER_ID IS NOT NULL THEN
701 BEGIN
702 SELECT
703 HCA.ACCOUNT_NUMBER
704 INTO L_TRADER_NUMBER
705 FROM
706 HZ_PARTIES HP,
707 HZ_CUST_ACCOUNTS HCA
708 WHERE HP.PARTY_ID = HCA.PARTY_ID
709 AND HCA.CUST_ACCOUNT_ID = C_SHIP_TO_CUSTOMER_ID;
710 EXCEPTION
711 WHEN NO_DATA_FOUND THEN
712 L_TRADER_NUMBER := NULL;
713 END;
714 ELSE
715 L_TRADER_NUMBER := NULL;
716 END IF;
717 END IF;
718 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
719 L_TRADER_NUMBER := C_VENDOR_NUMBER;
720 ELSIF C_DOCUMENT_SOURCE_TYPE in ('IO','INV') THEN
721 L_TRADER_NUMBER := NULL;
722 ELSIF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
723 IF C_CUSTOMER_NUMBER IS NOT NULL THEN
724 L_TRADER_NUMBER := C_CUSTOMER_NUMBER;
725 ELSIF C_VENDOR_NUMBER IS NOT NULL THEN
726 L_TRADER_NUMBER := C_VENDOR_NUMBER;
727 ELSIF C_TRANSACTING_FROM_ORG IS NOT NULL THEN
728 L_TRADER_NUMBER := NULL;
729 ELSIF C_TRANSACTING_TO_ORG IS NOT NULL THEN
730 L_TRADER_NUMBER := NULL;
731 ELSE
732 L_TRADER_NUMBER := NULL;
733 END IF;
734 ELSE
735 L_TRADER_NUMBER := NULL;
736 END IF;
737 ELSE
738 L_TRADER_NUMBER := NULL;
739 END IF;
740 RETURN (L_TRADER_NUMBER);
741 END;
742 RETURN NULL;
743 END CF_TRADER_NUMBERFORMULA;
744
745 FUNCTION CF_TRADER_SITEFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
746 ,C_CUSTOMER_LOCATION IN VARCHAR2
747 ,C_SHIP_TO_SITE_USE_ID IN NUMBER
748 ,C_VENDOR_SITE IN VARCHAR2
749 ,C_CUSTOMER_NAME IN VARCHAR2
750 ,C_VENDOR_NAME IN VARCHAR2
751 ,C_TRANSACTING_FROM_ORG IN VARCHAR2
752 ,C_TRANSACTING_TO_ORG IN VARCHAR2) RETURN VARCHAR2 IS
753 BEGIN
754 DECLARE
755 L_TRADER_SITE VARCHAR2(50);
756 BEGIN
757 IF P_REPORT_OPTION = 'S/D' THEN
758 IF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
759 IF C_CUSTOMER_LOCATION IS NOT NULL THEN
760 L_TRADER_SITE := C_CUSTOMER_LOCATION;
761 ELSIF C_SHIP_TO_SITE_USE_ID IS NOT NULL THEN
762 BEGIN
763 SELECT
764 LOCATION
765 INTO L_TRADER_SITE
766 FROM
767 HZ_CUST_SITE_USES_ALL
768 WHERE SITE_USE_ID = C_SHIP_TO_SITE_USE_ID;
769 EXCEPTION
770 WHEN NO_DATA_FOUND THEN
771 L_TRADER_SITE := NULL;
772 END;
773 ELSE
774 L_TRADER_SITE := NULL;
775 END IF;
776 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
777 L_TRADER_SITE := C_VENDOR_SITE;
778 ELSIF C_DOCUMENT_SOURCE_TYPE in ('IO','INV') THEN
779 L_TRADER_SITE := NULL;
780 ELSIF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
781 IF C_CUSTOMER_NAME IS NOT NULL THEN
782 L_TRADER_SITE := C_CUSTOMER_LOCATION;
783 ELSIF C_VENDOR_NAME IS NOT NULL THEN
784 L_TRADER_SITE := C_VENDOR_SITE;
785 ELSIF C_TRANSACTING_FROM_ORG IS NOT NULL THEN
786 L_TRADER_SITE := NULL;
787 ELSIF C_TRANSACTING_TO_ORG IS NOT NULL THEN
788 L_TRADER_SITE := NULL;
789 ELSE
790 L_TRADER_SITE := NULL;
791 END IF;
792 ELSE
793 L_TRADER_SITE := NULL;
794 END IF;
795 ELSE
796 L_TRADER_SITE := NULL;
797 END IF;
798 RETURN (L_TRADER_SITE);
799 END;
800 RETURN NULL;
801 END CF_TRADER_SITEFORMULA;
802
803 FUNCTION CF_TRADER_TYPEFORMULA(C_DOCUMENT_SOURCE_TYPE IN VARCHAR2
804 ,C_CUSTOMER_NAME IN VARCHAR2
805 ,C_VENDOR_NAME IN VARCHAR2
806 ,C_TRANSACTING_FROM_ORG IN VARCHAR2
807 ,C_TRANSACTING_TO_ORG IN VARCHAR2) RETURN VARCHAR2 IS
808 BEGIN
809 DECLARE
810 L_TRADER_TYPE VARCHAR2(20);
811 BEGIN
812 IF P_REPORT_OPTION = 'S/D' THEN
813 IF C_DOCUMENT_SOURCE_TYPE in ('SO','RMA') THEN
814 L_TRADER_TYPE := 'Customer';
815 ELSIF C_DOCUMENT_SOURCE_TYPE in ('PO','RTV') THEN
816 L_TRADER_TYPE := 'Vendor';
817 ELSIF C_DOCUMENT_SOURCE_TYPE in ('IO','INV') THEN
818 L_TRADER_TYPE := 'Organization';
819 ELSIF C_DOCUMENT_SOURCE_TYPE = 'MISC' THEN
820 IF C_CUSTOMER_NAME IS NOT NULL THEN
821 L_TRADER_TYPE := 'Customer';
822 ELSIF C_VENDOR_NAME IS NOT NULL THEN
823 L_TRADER_TYPE := 'Vendor';
824 ELSIF C_TRANSACTING_FROM_ORG IS NOT NULL OR C_TRANSACTING_TO_ORG IS NOT NULL THEN
825 L_TRADER_TYPE := 'Organization';
826 ELSE
827 L_TRADER_TYPE := NULL;
828 END IF;
829 ELSE
830 L_TRADER_TYPE := NULL;
831 END IF;
832 ELSE
833 L_TRADER_TYPE := NULL;
834 END IF;
835 RETURN (L_TRADER_TYPE);
836 END;
837 RETURN NULL;
838 END CF_TRADER_TYPEFORMULA;
839
840 FUNCTION AFTERREPORT(CS_NO_COUNT IN NUMBER) RETURN BOOLEAN IS
841 BEGIN
842 IF P_REPORT_OPTION = 'O/S' THEN
843 IF CS_NO_COUNT <> 0 THEN
844 UPDATE
845 MTL_MOVEMENT_STATISTICS
846 SET
847 MOVEMENT_STATUS = 'F'
848 ,REPORT_REFERENCE = TO_CHAR(P_LEGAL_ENTITY_ID) || P_ZONE_CODE || P_PERIOD_NAME || P_USAGE_TYPE || P_STAT_TYPE || P_MOVEMENT_TYPE
849 WHERE ENTITY_ORG_ID = P_LEGAL_ENTITY_ID
850 AND ZONE_CODE = P_ZONE_CODE
851 AND PERIOD_NAME = P_PERIOD_NAME
852 AND USAGE_TYPE = P_USAGE_TYPE
853 AND STAT_TYPE = P_STAT_TYPE
854 AND MOVEMENT_TYPE = P_MOVEMENT_TYPE
855 AND MOVEMENT_STATUS = 'V';
856 END IF;
857 COMMIT;
858 END IF;
859 BEGIN
860 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
861 EXCEPTION
862 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
863 /*SRW.MESSAGE(1
864 ,'Failed in srwexit')*/NULL;
865 END;
866 RETURN (TRUE);
867 END AFTERREPORT;
868
869 FUNCTION CF_PARENT_MVT_QUANTITYFORMULA(CS_ALTERNATE_QUANTITY IN VARCHAR2
870 ,CS_TRANSACTION_QUANTITY IN VARCHAR2) RETURN NUMBER IS
871 BEGIN
872 IF CS_ALTERNATE_QUANTITY IS NULL THEN
873 RETURN (CS_TRANSACTION_QUANTITY);
874 ELSE
875 RETURN (CS_ALTERNATE_QUANTITY);
876 END IF;
877 END CF_PARENT_MVT_QUANTITYFORMULA;
878
879 FUNCTION CF_PARENT_MVT_UOMFORMULA(C_ALTERNATE_UOM_CODE IN VARCHAR2
880 ,C_TRANSACTION_UOM_CODE IN VARCHAR2) RETURN CHAR IS
881 BEGIN
882 IF C_ALTERNATE_UOM_CODE IS NULL THEN
883 RETURN (C_TRANSACTION_UOM_CODE);
884 ELSE
885 RETURN (C_ALTERNATE_UOM_CODE);
886 END IF;
887 END CF_PARENT_MVT_UOMFORMULA;
888
889 FUNCTION CF_STATISTICAL_VALUE_RFORMUL(CS_STATISTICAL_VALUE IN VARCHAR2) RETURN NUMBER IS
890 L_STAT_VALUE NUMBER;
891 BEGIN
892 L_STAT_VALUE := INV_MGD_MVT_UTILS_PKG.ROUND_NUMBER(P_NUMBER => CS_STATISTICAL_VALUE
893 ,P_PRECISION => P_REP_PRECISION
894 ,P_ROUNDING_METHOD => P_REP_ROUNDING);
895 RETURN (L_STAT_VALUE);
896 END CF_STATISTICAL_VALUE_RFORMUL;
897
898 FUNCTION CF_TRANSACTION_VALUE_RFORMUL(CS_TRANSACTION_VALUE IN VARCHAR2) RETURN NUMBER IS
899 L_TRANS_VALUE NUMBER;
900 BEGIN
901 L_TRANS_VALUE := INV_MGD_MVT_UTILS_PKG.ROUND_NUMBER(P_NUMBER => CS_TRANSACTION_VALUE
902 ,P_PRECISION => P_REP_PRECISION
903 ,P_ROUNDING_METHOD => P_REP_ROUNDING);
904 RETURN (L_TRANS_VALUE);
905 END CF_TRANSACTION_VALUE_RFORMUL;
906
907 FUNCTION CF_INVOICE_LINE_EXT_VAL_RFORMU(CS_INVOICE_VALUE IN VARCHAR2
908 ,CF_PRECISION_TRX IN NUMBER) RETURN NUMBER IS
909 BEGIN
910 RETURN (ROUND(CS_INVOICE_VALUE
911 ,CF_PRECISION_TRX));
912 END CF_INVOICE_LINE_EXT_VAL_RFORMU;
913
914 FUNCTION CF_DOCUMENT_LINE_EXT_VAL_RFORM(CS_DOC_VALUE IN VARCHAR2
915 ,CF_PRECISION_TRX IN NUMBER) RETURN NUMBER IS
916 BEGIN
917 RETURN (ROUND(CS_DOC_VALUE
918 ,CF_PRECISION_TRX));
919 END CF_DOCUMENT_LINE_EXT_VAL_RFORM;
920
921 FUNCTION CF_REPORT_TRANSACTION_VAL_RFOR(CS_REPORT_TRANSACTION_VAL IN NUMBER) RETURN NUMBER IS
922 L_REP_TRANS_VALUE NUMBER;
923 BEGIN
924 L_REP_TRANS_VALUE := INV_MGD_MVT_UTILS_PKG.ROUND_NUMBER(P_NUMBER => CS_REPORT_TRANSACTION_VAL
925 ,P_PRECISION => P_REP_PRECISION
926 ,P_ROUNDING_METHOD => P_REP_ROUNDING);
927 RETURN (L_REP_TRANS_VALUE);
928 END CF_REPORT_TRANSACTION_VAL_RFOR;
929
930 FUNCTION CF_REPORT_STATISTICAL_VAL_RFOR(CS_REPORT_STATISTICAL_VAL IN NUMBER) RETURN NUMBER IS
931 L_REP_STAT_VALUE NUMBER;
932 BEGIN
933 L_REP_STAT_VALUE := INV_MGD_MVT_UTILS_PKG.ROUND_NUMBER(P_NUMBER => CS_REPORT_STATISTICAL_VAL
934 ,P_PRECISION => P_REP_PRECISION
935 ,P_ROUNDING_METHOD => P_REP_ROUNDING);
936 RETURN (L_REP_STAT_VALUE);
937 END CF_REPORT_STATISTICAL_VAL_RFOR;
938
939 FUNCTION CF_OUTSIDE_VALUE_RFORMULA(CS_OUTSIDE_VALUE IN VARCHAR2
940 ,CF_PRECISION_TRX IN NUMBER) RETURN NUMBER IS
941 BEGIN
942 RETURN (ROUND(CS_OUTSIDE_VALUE
943 ,CF_PRECISION_TRX));
944 END CF_OUTSIDE_VALUE_RFORMULA;
945
946 FUNCTION AFTERPFORM RETURN BOOLEAN IS
947 BEGIN
948 P_OE_OR_OM := OE_INSTALL.GET_ACTIVE_PRODUCT;
949 RETURN (TRUE);
950 END AFTERPFORM;
951
952 FUNCTION CF_PRECISION_TRXFORMULA(C_MOVEMENT_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
953 L_PRECISION_TRX NUMBER;
954 BEGIN
955 SELECT
956 PRECISION
957 INTO L_PRECISION_TRX
958 FROM
959 FND_CURRENCIES
960 WHERE CURRENCY_CODE = C_MOVEMENT_CURRENCY_CODE;
961 RETURN L_PRECISION_TRX;
962 END CF_PRECISION_TRXFORMULA;
963
964 FUNCTION CF_RELEASE_NUMBERFORMULA(C_RCV_TRANSACTION_ID IN NUMBER) RETURN NUMBER IS
965 BEGIN
966 DECLARE
967 L_PO_RELEASE_ID NUMBER;
968 L_RELEASE_NUM NUMBER;
969 BEGIN
970 IF C_RCV_TRANSACTION_ID IS NOT NULL THEN
971 BEGIN
972 SELECT
973 PO_RELEASE_ID
974 INTO L_PO_RELEASE_ID
975 FROM
976 RCV_TRANSACTIONS
977 WHERE TRANSACTION_ID = C_RCV_TRANSACTION_ID;
978 EXCEPTION
979 WHEN NO_DATA_FOUND THEN
980 L_RELEASE_NUM := NULL;
981 RETURN (L_RELEASE_NUM);
982 END;
983 IF L_PO_RELEASE_ID IS NOT NULL THEN
984 SELECT
985 RELEASE_NUM
986 INTO L_RELEASE_NUM
987 FROM
988 PO_RELEASES_ALL
989 WHERE PO_RELEASE_ID = L_PO_RELEASE_ID;
990 END IF;
991 ELSE
992 L_RELEASE_NUM := NULL;
993 END IF;
994 RETURN (L_RELEASE_NUM);
995 EXCEPTION
996 WHEN OTHERS THEN
997 /*SRW.MESSAGE(100
998 ,'Failed in CF_RELEASE_NUMBER ')*/NULL;
999 END;
1000 RETURN NULL;
1001 END CF_RELEASE_NUMBERFORMULA;
1002
1003 FUNCTION CP_REQUESTED_BY_P RETURN VARCHAR2 IS
1004 BEGIN
1005 RETURN CP_REQUESTED_BY;
1006 END CP_REQUESTED_BY_P;
1007
1008 FUNCTION CP_APPLICATION_P RETURN VARCHAR2 IS
1009 BEGIN
1010 RETURN CP_APPLICATION;
1011 END CP_APPLICATION_P;
1012
1013 FUNCTION CP_RESPONSIBILITY_P RETURN VARCHAR2 IS
1014 BEGIN
1015 RETURN CP_RESPONSIBILITY;
1016 END CP_RESPONSIBILITY_P;
1017
1018 FUNCTION CP_REQUEST_TIME_P RETURN VARCHAR2 IS
1019 BEGIN
1020 RETURN CP_REQUEST_TIME;
1021 END CP_REQUEST_TIME_P;
1022
1023 FUNCTION CP_LEGAL_ENTITY_NAME_P RETURN VARCHAR2 IS
1024 BEGIN
1025 RETURN CP_LEGAL_ENTITY_NAME;
1026 END CP_LEGAL_ENTITY_NAME_P;
1027
1028 FUNCTION CP_ENTITY_ADDRESS_LINE_1_P RETURN VARCHAR2 IS
1029 BEGIN
1030 RETURN CP_ENTITY_ADDRESS_LINE_1;
1031 END CP_ENTITY_ADDRESS_LINE_1_P;
1032
1033 FUNCTION CP_ENTITY_ADDRESS_LINE_2_P RETURN VARCHAR2 IS
1034 BEGIN
1035 RETURN CP_ENTITY_ADDRESS_LINE_2;
1036 END CP_ENTITY_ADDRESS_LINE_2_P;
1037
1038 FUNCTION CP_ENTITY_ADDRESS_LINE_3_P RETURN VARCHAR2 IS
1039 BEGIN
1040 RETURN CP_ENTITY_ADDRESS_LINE_3;
1041 END CP_ENTITY_ADDRESS_LINE_3_P;
1042
1043 FUNCTION CP_TELEPHONE_NUMBER_1_P RETURN VARCHAR2 IS
1044 BEGIN
1045 RETURN CP_TELEPHONE_NUMBER_1;
1046 END CP_TELEPHONE_NUMBER_1_P;
1047
1048 FUNCTION CP_TELEPHONE_NUMBER_2_P RETURN VARCHAR2 IS
1049 BEGIN
1050 RETURN CP_TELEPHONE_NUMBER_2;
1051 END CP_TELEPHONE_NUMBER_2_P;
1052
1053 FUNCTION CP_CONTACT_PERSON_NAME_P RETURN VARCHAR2 IS
1054 BEGIN
1055 RETURN CP_CONTACT_PERSON_NAME;
1056 END CP_CONTACT_PERSON_NAME_P;
1057
1058 FUNCTION CP_VAT_REGISTRATION_NUMBER_P RETURN VARCHAR2 IS
1059 BEGIN
1060 RETURN CP_VAT_REGISTRATION_NUMBER;
1061 END CP_VAT_REGISTRATION_NUMBER_P;
1062
1063 FUNCTION CP_SET_OF_BOOKS_NAME_P RETURN VARCHAR2 IS
1064 BEGIN
1065 RETURN CP_SET_OF_BOOKS_NAME;
1066 END CP_SET_OF_BOOKS_NAME_P;
1067
1068 FUNCTION CP_TAX_OFFICE_NAME_P RETURN VARCHAR2 IS
1069 BEGIN
1070 RETURN CP_TAX_OFFICE_NAME;
1071 END CP_TAX_OFFICE_NAME_P;
1072
1073 FUNCTION CP_TAX_OFFICE_ADDRESS_LINE_1_P RETURN VARCHAR2 IS
1074 BEGIN
1075 RETURN CP_TAX_OFFICE_ADDRESS_LINE_1;
1076 END CP_TAX_OFFICE_ADDRESS_LINE_1_P;
1077
1078 FUNCTION CP_TAX_OFFICE_ADDRESS_LINE_2_P RETURN VARCHAR2 IS
1079 BEGIN
1080 RETURN CP_TAX_OFFICE_ADDRESS_LINE_2;
1081 END CP_TAX_OFFICE_ADDRESS_LINE_2_P;
1082
1083 FUNCTION CP_TAX_OFFICE_ADDRESS_LINE_3_P RETURN VARCHAR2 IS
1084 BEGIN
1085 RETURN CP_TAX_OFFICE_ADDRESS_LINE_3;
1086 END CP_TAX_OFFICE_ADDRESS_LINE_3_P;
1087
1088 FUNCTION CP_START_DATE_P RETURN DATE IS
1089 BEGIN
1090 RETURN CP_START_DATE;
1091 END CP_START_DATE_P;
1092
1093 FUNCTION CP_END_DATE_P RETURN DATE IS
1094 BEGIN
1095 RETURN CP_END_DATE;
1096 END CP_END_DATE_P;
1097
1098 FUNCTION CP_STANDARD_UOM_P RETURN VARCHAR2 IS
1099 BEGIN
1100 RETURN CP_STANDARD_UOM;
1101 END CP_STANDARD_UOM_P;
1102
1103 FUNCTION CP_SYSDATE_P RETURN VARCHAR2 IS
1104 BEGIN
1105 RETURN CP_SYSDATE;
1106 END CP_SYSDATE_P;
1107
1108 FUNCTION CP_EXT_PRECISION_FUNC_P RETURN NUMBER IS
1109 BEGIN
1110 RETURN CP_EXT_PRECISION_FUNC;
1111 END CP_EXT_PRECISION_FUNC_P;
1112
1113 FUNCTION CP_ZONE_NAME_COV_P RETURN VARCHAR2 IS
1114 BEGIN
1115 RETURN CP_ZONE_NAME_COV;
1116 END CP_ZONE_NAME_COV_P;
1117
1118 FUNCTION CP_MV_TYPE_COV_P RETURN VARCHAR2 IS
1119 BEGIN
1120 RETURN CP_MV_TYPE_COV;
1121 END CP_MV_TYPE_COV_P;
1122
1123 FUNCTION CP_REP_OPTION_COV_P RETURN VARCHAR2 IS
1124 BEGIN
1125 RETURN CP_REP_OPTION_COV;
1126 END CP_REP_OPTION_COV_P;
1127
1128 FUNCTION CP_USAGE_TYPE_COV_P RETURN VARCHAR2 IS
1129 BEGIN
1130 RETURN CP_USAGE_TYPE_COV;
1131 END CP_USAGE_TYPE_COV_P;
1132
1133 FUNCTION CP_STAT_TYPE_COV_P RETURN VARCHAR2 IS
1134 BEGIN
1135 RETURN CP_STAT_TYPE_COV;
1136 END CP_STAT_TYPE_COV_P;
1137
1138 FUNCTION CP_PRECISION_FUNC_P RETURN NUMBER IS
1139 BEGIN
1140 RETURN CP_PRECISION_FUNC;
1141 END CP_PRECISION_FUNC_P;
1142
1143 FUNCTION CP_REPORT_TITLE_P RETURN VARCHAR2 IS
1144 BEGIN
1145 RETURN CP_REPORT_TITLE;
1146 END CP_REPORT_TITLE_P;
1147
1148 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
1149 BEGIN
1150 QTY_PRECISION:= inv_common_xmlp_pkg.get_precision(P_QUANTITY_PRECISION);
1151 /*
1152 BEGIN
1153 SRW.USER_EXIT('FND SRWINIT');
1154 EXCEPTION
1155 WHEN SRW.USER_EXIT_FAILURE THEN
1156 SRW.Message(10,'Failed in SRWINIT');
1157 RAISE;
1158 END;
1159 */
1160
1161 -------------------------------------------------
1162 --Convert exchange rate from char to number
1163 -------------------------------------------------
1164 BEGIN
1165 p_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1166 P_EXCHANGE_RATE := fnd_number.canonical_to_number(P_EXCHANGE_RATE_CHAR);
1167 END;
1168
1169
1170 -------------------------------------------------
1171 --if report option is official summary, check if
1172 --movement status is verified
1173 -------------------------------------------------
1174 BEGIN
1175 DECLARE
1176 l_invalid_status EXCEPTION;
1177 l_nnverified_status_count NUMBER;
1178 l_message VARCHAR2(2000);
1179 BEGIN
1180 IF p_report_option = 'O/S'
1181 THEN
1182 SELECT
1183 COUNT(*)
1184 INTO
1185 l_nnverified_status_count
1186 FROM
1187 MTL_MOVEMENT_STATISTICS mms
1188 WHERE mms.entity_org_id = p_legal_entity_id
1189 AND mms.zone_code = p_zone_code
1190 AND mms.period_name = p_period_name
1191 AND mms.usage_type = p_usage_type
1192 AND mms.stat_type = p_stat_type
1193 AND mms.movement_type = p_movement_type
1194 AND movement_status = 'O';
1195 IF l_nnverified_status_count > 0
1196 THEN
1197 RAISE l_invalid_status;
1198 END IF;
1199 END IF;
1200 EXCEPTION
1201 WHEN l_invalid_status THEN
1202 FND_MESSAGE.SET_NAME('INV', 'INV_MGD_MVT_STAT_INVALID_STS');
1203 l_message := FND_MESSAGE.Get;
1204 --SRW.Message(1, l_message);
1205 RAISE;
1206 WHEN OTHERS THEN
1207 --SRW.Message(31, 'Failed in check movement status');
1208 null;
1209 END;
1210
1211 END;
1212
1213
1214
1215 -------------------------------------------------
1216 -- Initialization for Request By, Request time --
1217 -- Responsibility and Application --
1218 -------------------------------------------------
1219 BEGIN
1220 SELECT
1221 frv.responsibility_name
1222 , TO_CHAR(fcr.request_date, 'YYYY/MM/DD HH24:MI:SS')
1223 , fav.application_name
1224 , fu.user_name
1225 INTO
1226 cp_responsibility
1227 , cp_request_time
1228 , cp_application
1229 , cp_requested_by
1230 FROM
1231 fnd_concurrent_requests fcr
1232 , fnd_responsibility_vl frv
1233 , fnd_application_vl fav
1234 , fnd_user fu
1235 WHERE fcr.request_id = p_conc_request_id
1236 AND fcr.responsibility_application_id = frv.application_id
1237 AND fcr.responsibility_id = frv.responsibility_id
1238 AND frv.application_id = fav.application_id
1239 AND fu.user_id = fcr.requested_by;
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 -- SRW.Message(31, 'Failed Request By and Request time Init.');
1243 null;
1244 END;
1245
1246 --------------------------------------------------
1247 --Change sysdate formatting
1248 --------------------------------------------------
1249 SELECT
1250 TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI')
1251 INTO
1252 CP_SYSDATE
1253 FROM
1254 DUAL;
1255
1256
1257 --------------------------------------
1258 -- Initialization for Set of Books --
1259 -- and Legal Entity --
1260 --------------------------------------
1261 BEGIN
1262 --Get VAT number
1263 CP_VAT_REGISTRATION_NUMBER :=
1264 INV_MGD_MVT_UTILS_PKG.Get_Org_VAT_Number
1265 ( p_entity_org_id => p_legal_entity_id
1266 , p_date => sysdate);
1267
1268 --Get other values
1269 SELECT
1270 gllv.legal_entity_name, hl.address_line_1,
1271 hl.address_line_2, hl.address_line_3,
1272 hl.telephone_number_1, hl.telephone_number_2,
1273 ppf.full_name, -- hle.vat_registration_number,
1274 gllv.ledger_name
1275 INTO
1276 CP_LEGAL_ENTITY_NAME, CP_ENTITY_ADDRESS_LINE_1,
1277 CP_ENTITY_ADDRESS_LINE_2, CP_ENTITY_ADDRESS_LINE_3,
1278 CP_TELEPHONE_NUMBER_1, CP_TELEPHONE_NUMBER_2,
1279 CP_CONTACT_PERSON_NAME, --CP_VAT_REGISTRATION_NUMBER,
1280 CP_SET_OF_BOOKS_NAME
1281 FROM
1282 hr_locations_no_join hl,
1283 per_people_f ppf,
1284 gl_ledger_le_v gllv
1285 WHERE gllv.legal_entity_id = P_LEGAL_ENTITY_ID
1286 AND gllv.ledger_category_code = 'PRIMARY'
1287 AND gllv.location_id = hl.location_id
1288 AND hl.designated_receiver_id = ppf.person_id(+)
1289 AND rownum < 2;
1290
1291 EXCEPTION
1292 WHEN OTHERS THEN
1293 -- SRW.Message(31, 'Failed in Set_of_books and Legal_Entity Init.');
1294 null;
1295
1296 END;
1297
1298
1299 ----------------------------------------
1300 -- Initialize currency precision for
1301 -- functional currency and transaction
1302 -- currency
1303 ----------------------------------------
1304 BEGIN
1305 SELECT
1306 nvl(fc.extended_precision,1)
1307 , nvl(fc.precision,1)
1308 INTO
1309 CP_EXT_PRECISION_FUNC
1310 , CP_PRECISION_FUNC
1311 FROM
1312 fnd_currencies fc
1313 WHERE fc.currency_code = P_CURRENCY_CODE
1314 AND rownum < 2;
1315
1316 EXCEPTION
1317 WHEN OTHERS THEN
1318 -- SRW.Message(31, 'Failed in currency precision.');
1319 null;
1320 END;
1321
1322
1323 ----------------------------------------
1324 -- Initialization for Tax information --
1325 ----------------------------------------
1326 BEGIN
1327 DECLARE
1328 l_location_id NUMBER;
1329 BEGIN
1330 SELECT
1331 tax_office_name
1332 , weight_uom_code
1333 , tax_office_location_id
1334 INTO
1335 CP_TAX_OFFICE_NAME
1336 , CP_STANDARD_UOM
1337 , l_location_id
1338 FROM
1339 mtl_stat_type_usages
1340 WHERE legal_entity_id = P_LEGAL_ENTITY_ID
1341 AND zone_code = P_ZONE_CODE
1342 AND stat_type = P_STAT_TYPE
1343 AND usage_type = P_USAGE_TYPE;
1344
1345 BEGIN
1346 SELECT
1347 address_line_1
1348 , address_line_2
1349 , address_line_3
1350 INTO
1351 CP_TAX_OFFICE_ADDRESS_LINE_1
1352 , CP_TAX_OFFICE_ADDRESS_LINE_2
1353 , CP_TAX_OFFICE_ADDRESS_LINE_3
1354 FROM
1355 hr_locations_no_join
1356 WHERE location_id = l_location_id;
1357
1358 EXCEPTION
1359 WHEN NO_DATA_FOUND THEN
1360 BEGIN
1361 SELECT
1362 address1
1363 , address2
1364 , address3
1365 INTO
1366 CP_TAX_OFFICE_ADDRESS_LINE_1
1367 , CP_TAX_OFFICE_ADDRESS_LINE_2
1368 , CP_TAX_OFFICE_ADDRESS_LINE_3
1369 FROM
1370 hz_locations
1371 WHERE location_id = l_location_id;
1372
1373 EXCEPTION
1374 WHEN OTHERS THEN
1375 -- SRW.Message(31, 'Failed in Tax Office Init');
1376 null;
1377 END;
1378
1379 WHEN OTHERS THEN
1380 --SRW.Message(31, 'Failed in Tax Office Init');
1381 null;
1382 END;
1383 END;
1384 END;
1385
1386
1387 ------------------------------------------------
1388 -- Initialization for Start Date, End Date of --
1389 -- one period --
1390 ------------------------------------------------
1391 BEGIN
1392 SELECT
1393 start_date
1394 , end_date
1395 INTO
1396 CP_start_date
1397 , CP_end_date
1398 FROM
1399 GL_PERIODS
1400 WHERE period_name = P_period_name
1401 AND rownum < 2;
1402
1403 EXCEPTION
1404 WHEN OTHERS THEN
1405 -- SRW.Message(31, 'Failed in start_date and end_date Init');
1406 null;
1407 END;
1408
1409
1410 /*
1411 Fix bug 2230047, get commodity code from mtl_movement_statistics table directly
1412 not use flexfield anymore
1413 ------------------------------------------------
1414 -- Initialization for Commodity Structure id --
1415 -- of one Legal Entity --
1416 ------------------------------------------------
1417 BEGIN
1418 SELECT
1419 mcs.structure_id
1420 INTO
1421 PLEX_COMMODITY_STRUCT_NUM
1422 FROM
1423 mtl_stat_type_usages mstu
1424 ,mtl_category_sets mcs
1425 WHERE mstu.legal_entity_id = P_LEGAL_ENTITY_ID
1426 AND mstu.zone_code = P_ZONE_CODE
1427 AND mstu.category_set_id = mcs.category_set_id
1428 AND rownum < 2;
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 SRW.Message(31, 'Failed in conversion Init');
1432 END;
1433 */
1434 ------------------------
1435 -- Flexfield for ITEM --
1436 ------------------------
1437 /*
1438 BEGIN
1439 SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" NUM="PLEX_ITEM_STRUCT_NUM" APPL_SHORT_NAME="INV"
1440 OUTPUT="PLEX_ITEM_FLEX"
1441 MODE="SELECT"
1442 DISPLAY="ALL"
1443 TABLEALIAS="MSI"');
1444 EXCEPTION
1445 WHEN SRW.USER_EXIT_FAILURE THEN
1446 SRW.Message(60,'Failed in ITEM Flexfield');
1447 WHEN OTHERS THEN
1448 SRW.Message(31, 'Failed in ITEM Flexfield');
1449 END;
1450 */
1451 /*
1452 Fix bug 2230047, get commodity code from mtl_movement_statistics table directly
1453 not use flexfield anymore
1454 ----------------------------------
1455 -- Flexfield for COMMODITY_CODE --
1456 ----------------------------------
1457 BEGIN
1458 SRW.USER_EXIT('FND FLEXSQL CODE="MCAT"
1459 NUM="PLEX_COMMODITY_STRUCT_NUM"
1460 APPL_SHORT_NAME="INV"
1461 OUTPUT="PLEX_COMMODITY_FLEX"
1462 MODE="SELECT"
1463 DISPLAY="ALL"
1464 TABLEALIAS="MC"');
1465 EXCEPTION
1466 WHEN SRW.USER_EXIT_FAILURE THEN
1467 SRW.Message(70,'Failed in COMMODITY_CODE Flexfield');
1468 WHEN OTHERS THEN
1469 SRW.Message(31, 'Failed in COMMODITY_CODE Flexfield ');
1470 END;
1471
1472 BEGIN
1473 SRW.USER_EXIT('FND FLEXSQL CODE="MCAT"
1474 NUM="PLEX_COMMODITY_STRUCT_NUM"
1475 APPL_SHORT_NAME="INV"
1476 OUTPUT="PLEX_COMMODITY_ORDER_BY"
1477 MODE="ORDER BY"
1478 DISPLAY="ALL"
1479 TABLEALIAS="MC"');
1480 EXCEPTION
1481 WHEN SRW.USER_EXIT_FAILURE THEN
1482 SRW.Message(70,'Failed in COMMODITY_CODE Flexfield');
1483 WHEN OTHERS THEN
1484 SRW.Message(31, 'Failed in COMMODITY_CODE Flexfield');
1485 END;
1486 */
1487 -------------------------------------------
1488 -- Populate report parameter placeholders
1489 -- Displayed on cover page
1490 -------------------------------------------
1491 BEGIN
1492 SELECT zone_display_name
1493 INTO cp_zone_name_cov
1494 FROM mtl_economic_zones_vl
1495 WHERE zone_code = p_zone_code
1496 AND rownum<2;
1497
1498 SELECT meaning
1499 INTO cp_rep_option_cov
1500 FROM fnd_lookup_values
1501 WHERE lookup_type = 'INTRASTAT_REPORT_OPTION'
1502 AND lookup_code = p_report_option
1503 AND enabled_flag = 'Y'
1504 AND language = userenv('LANG')
1505 AND rownum<2;
1506
1507 SELECT meaning
1508 INTO cp_mv_type_cov
1509 FROM fnd_lookups
1510 WHERE lookup_type = 'MVT_MOVEMENT_TYPE'
1511 AND lookup_code = p_movement_type
1512 AND enabled_flag = 'Y'
1513 AND rownum<2;
1514
1515 SELECT meaning
1516 INTO cp_usage_type_cov
1517 FROM fnd_lookups
1518 WHERE lookup_type = 'MVT_USAGE_TYPES'
1519 AND lookup_code = p_usage_type
1520 AND enabled_flag = 'Y'
1521 AND rownum<2;
1522
1523 SELECT meaning
1524 INTO cp_stat_type_cov
1525 FROM fnd_lookups
1526 WHERE lookup_type = 'MVT_STAT_TYPES'
1527 AND lookup_code = p_stat_type
1528 AND enabled_flag = 'Y'
1529 AND rownum<2;
1530
1531
1532 EXCEPTION
1533 WHEN OTHERS THEN
1534 -- SRW.Message(31, 'Failed cover page parameters initialization.');
1535 null;
1536 END;
1537
1538 -------------------------------------------
1539 -- Populate p_mvt_id_where, used in main
1540 -- query
1541 -------------------------------------------
1542 BEGIN
1543 IF P_MOVEMENT_TYPE IN ('A', 'D')
1544 THEN
1545 P_MVT_ID_WHERE := 'and mms.parent_movement_id = mms.movement_id';
1546 ELSE
1547 P_MVT_ID_WHERE :='and movement_id = (select min(movement_id)
1548 from mtl_movement_statistics
1549 where parent_movement_id = mms.parent_movement_id
1550 and movement_type = '''||P_MOVEMENT_TYPE||''' group by mms.parent_movement_id)';
1551
1552 END IF;
1553 END;
1554 --srw.message(01,'in bef rep');
1555
1556 --------------------------------------------------------
1557 --Populate reporting precision and rounding method
1558 --from setup form. Can not create place holder in May-06
1559 --somehow, so create user parameters to hold the value
1560 --Fix bug 5203245
1561 --------------------------------------------------------
1562 BEGIN
1563 INV_MGD_MVT_UTILS_PKG.Get_Weight_Precision
1564 (p_legal_entity_id => p_legal_entity_id
1565 , p_zone_code => p_zone_code
1566 , p_usage_type => p_usage_type
1567 , p_stat_type => p_stat_type
1568 , x_weight_precision => p_rep_precision
1569 , x_rep_rounding => p_rep_rounding);
1570
1571 IF p_amount_display = 'W'
1572 THEN
1573 p_rep_precision := 0;
1574 ELSE
1575 --same as before, report according to currency precision
1576 p_rep_precision := cp_precision_func;
1577 END IF;
1578
1579 END;
1580
1581 RETURN (TRUE);
1582 END BEFOREREPORT;
1583 END INV_INVSTMVT_XMLP_PKG;
1584
[Home] [Help]