1 PACKAGE BODY GMI_ICR03_XMLP_PKG AS
2 /* $Header: ICR03B.pls 120.0 2007/12/24 13:14:51 nchinnam noship $ */
3 FUNCTION DOCUMENT_NOCFFORMULA(DOC_TYPE IN VARCHAR2
4 ,DOC_ID IN NUMBER
5 ,LINE_ID1 IN NUMBER
6 ,ITEM_ID1 IN NUMBER) RETURN VARCHAR2 IS
7 D_NO VARCHAR2(40);
8 BEGIN
9 IF DOC_TYPE = 'PROD' THEN
10 SELECT
11 H.BATCH_NO
12 INTO D_NO
13 FROM
14 PM_MATL_DTL D,
15 PM_BTCH_HDR H,
16 FM_FORM_MST F
17 WHERE D.BATCH_ID = DOC_ID
18 AND D.LINE_ID = LINE_ID1
19 AND D.ITEM_ID = ITEM_ID1
20 AND D.BATCH_ID = H.BATCH_ID
21 AND H.FORMULA_ID = F.FORMULA_ID
22 AND H.DELETE_MARK = 0
23 AND F.DELETE_MARK = 0;
24 IF SQL%NOTFOUND THEN
25 RETURN (NULL);
26 END IF;
27 ELSIF DOC_TYPE = 'PORD' THEN
28 SELECT
29 H.PO_NO
30 INTO D_NO
31 FROM
32 PO_ORDR_HDR H,
33 PO_ORDR_DTL D,
34 PO_VEND_MST V,
35 OP_SHIP_MST S
36 WHERE D.PO_ID = DOC_ID
37 AND D.LINE_ID = LINE_ID1
38 AND D.ITEM_ID = ITEM_ID1
39 AND D.PO_ID = H.PO_ID
40 AND V.VENDOR_ID = D.SHIPVEND_ID
41 AND S.SHIPPER_CODE = D.SHIPPER_CODE
42 AND H.DELETE_MARK = 0
43 AND S.DELETE_MARK = 0;
44 IF SQL%NOTFOUND THEN
45 RETURN (NULL);
46 END IF;
47 ELSIF DOC_TYPE = 'OPSO' THEN
48 SELECT
49 H.ORDER_NO
50 INTO D_NO
51 FROM
52 OP_ORDR_HDR H,
53 OP_ORDR_DTL D,
54 OP_CUST_MST V,
55 OP_SHIP_MST S
56 WHERE D.ORDER_ID = DOC_ID
57 AND D.LINE_ID = LINE_ID1
58 AND D.ITEM_ID = ITEM_ID1
59 AND D.ORDER_ID = H.ORDER_ID
60 AND V.CUST_ID = D.SHIPCUST_ID
61 AND S.SHIPPER_CODE = D.SHIPPER_CODE
62 AND H.DELETE_MARK = 0
63 AND S.DELETE_MARK = 0
64 AND V.DELETE_MARK = 0;
65 IF SQL%NOTFOUND THEN
66 RETURN (NULL);
67 END IF;
68 ELSIF DOC_TYPE = 'RECV' THEN
69 SELECT
70 H.RECV_NO
71 INTO D_NO
72 FROM
73 PO_RECV_HDR H,
74 PO_RECV_DTL D,
75 PO_VEND_MST V,
76 OP_SHIP_MST S
77 WHERE D.RECV_ID = DOC_ID
78 AND D.LINE_ID = LINE_ID1
79 AND D.ITEM_ID = ITEM_ID1
80 AND D.RECV_ID = H.RECV_ID
81 AND V.VENDOR_ID = D.SHIPVEND_ID
82 AND S.SHIPPER_CODE = D.SHIPPER_CODE
83 AND H.DELETE_MARK = 0
84 AND S.DELETE_MARK = 0
85 AND V.DELETE_MARK = 0;
86 IF SQL%NOTFOUND THEN
87 RETURN (NULL);
88 END IF;
89 ELSIF DOC_TYPE = 'OPSP' THEN
90 SELECT
91 B1.BOL_NO
92 INTO D_NO
93 FROM
94 OP_BILL_LAD B1,
95 OP_BILL_LAD B2,
96 OP_ORDR_DTL D,
97 OP_CUST_MST V,
98 OP_SHIP_MST S
99 WHERE B1.BOL_ID = DOC_ID
100 AND D.LINE_ID = LINE_ID1
101 AND D.ITEM_ID = ITEM_ID1
102 AND D.BOL_ID = B2.BOL_ID
103 AND V.CUST_ID = D.SHIPCUST_ID
104 AND S.SHIPPER_CODE = D.SHIPPER_CODE
105 AND B1.DELETE_MARK = 0
106 AND B2.DELETE_MARK = 0
107 AND S.DELETE_MARK = 0
108 AND V.DELETE_MARK = 0;
109 IF SQL%NOTFOUND THEN
110 RETURN (NULL);
111 END IF;
112 ELSIF DOC_TYPE = 'OPCR' THEN
113 SELECT
114 B1.BOL_NO
115 INTO D_NO
116 FROM
117 OP_BILL_LAD B1,
118 OP_ORDR_DTL D,
119 OP_CUST_MST V,
120 OP_SHIP_MST S
121 WHERE D.INVOICE_ID = DOC_ID
122 AND D.LINE_ID = LINE_ID1
123 AND D.ITEM_ID = ITEM_ID1
124 AND D.INVOICE_ID = B1.BOL_ID
125 AND V.CUST_ID = D.SHIPCUST_ID
126 AND S.SHIPPER_CODE = B1.SHIPPER_CODE
127 AND B1.DELETE_MARK = 0
128 AND S.DELETE_MARK = 0
129 AND V.DELETE_MARK = 0;
130 IF SQL%NOTFOUND THEN
131 RETURN (NULL);
132 END IF;
133 ELSIF DOC_TYPE in ('CREI','ADJI','STSI','TRNI','GRDI','CRER','ADJR','STSR','TRNR','GRDR') THEN
134 SELECT
135 M.JOURNAL_NO
136 INTO D_NO
137 FROM
138 IC_JRNL_MST M,
139 IC_ADJS_JNL D
140 WHERE D.DOC_ID = DOC_ID
141 AND D.ITEM_ID = ITEM_ID1
142 AND D.LINE_ID = LINE_ID1
143 AND D.JOURNAL_ID = M.JOURNAL_ID;
144 IF SQL%NOTFOUND THEN
145 RETURN (NULL);
146 END IF;
147 ELSIF DOC_TYPE = 'XFER' THEN
148 SELECT
149 TRANSFER_NO
150 INTO D_NO
151 FROM
152 IC_XFER_MST
153 WHERE TRANSFER_ID = DOC_ID
154 AND ITEM_ID = ITEM_ID1;
155 IF SQL%NOTFOUND THEN
156 RETURN (NULL);
157 END IF;
158 ELSIF DOC_TYPE = 'PORC' THEN
159 SELECT
160 DISTINCT
161 RSH.RECEIPT_NUM
162 INTO D_NO
163 FROM
164 RCV_SHIPMENT_LINES RSL,
165 RCV_SHIPMENT_HEADERS RSH,
166 RCV_TRANSACTIONS RT
167 WHERE RT.TRANSACTION_ID = LINE_ID1
168 AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
169 AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID;
170 IF SQL%NOTFOUND THEN
171 RETURN (NULL);
172 END IF;
173 ELSIF DOC_TYPE = 'OMSO' THEN
174 SELECT
175 DISTINCT
176 OOH.ORDER_NUMBER
177 INTO D_NO
178 FROM
179 OE_ORDER_HEADERS_ALL OOH,
180 OE_ORDER_LINES_ALL OOL
181 WHERE OOL.LINE_ID = LINE_ID1
182 AND OOH.HEADER_ID = OOL.HEADER_ID;
183 IF SQL%NOTFOUND THEN
184 RETURN (NULL);
185 END IF;
186 END IF;
187 RETURN (D_NO);
188 RETURN NULL;
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 RETURN (NULL);
192 END DOCUMENT_NOCFFORMULA;
193 FUNCTION FORMULA_NOCFFORMULA(DOC_TYPE IN VARCHAR2
194 ,DOC_ID IN NUMBER
195 ,LINE_ID1 IN NUMBER
196 ,ITEM_ID1 IN NUMBER) RETURN VARCHAR2 IS
197 F_NO VARCHAR2(40);
198 BEGIN
199 IF DOC_TYPE = 'PROD' THEN
200 SELECT
201 F.FORMULA_NO
202 INTO F_NO
203 FROM
204 PM_MATL_DTL D,
205 PM_BTCH_HDR H,
206 FM_FORM_MST F
207 WHERE D.BATCH_ID = DOC_ID
208 AND D.LINE_ID = LINE_ID1
209 AND D.ITEM_ID = ITEM_ID1
210 AND D.BATCH_ID = H.BATCH_ID
211 AND H.FORMULA_ID = F.FORMULA_ID
212 AND H.DELETE_MARK = 0
213 AND F.DELETE_MARK = 0;
214 IF SQL%NOTFOUND THEN
215 RETURN (NULL);
216 END IF;
217 END IF;
218 RETURN (F_NO);
219 RETURN NULL;
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 RETURN (NULL);
223 END FORMULA_NOCFFORMULA;
224 FUNCTION VERSCFFORMULA(DOC_TYPE IN VARCHAR2
225 ,DOC_ID IN NUMBER
226 ,LINE_ID1 IN NUMBER
227 ,ITEM_ID1 IN NUMBER) RETURN NUMBER IS
228 V_NO NUMBER(5);
229 BEGIN
230 IF DOC_TYPE = 'PROD' THEN
231 SELECT
232 F.FORMULA_VERS
233 INTO V_NO
234 FROM
235 PM_MATL_DTL D,
236 PM_BTCH_HDR H,
237 FM_FORM_MST F
238 WHERE D.BATCH_ID = DOC_ID
239 AND D.LINE_ID = LINE_ID1
240 AND D.ITEM_ID = ITEM_ID1
241 AND D.BATCH_ID = H.BATCH_ID
242 AND H.FORMULA_ID = F.FORMULA_ID
243 AND H.DELETE_MARK = 0
244 AND F.DELETE_MARK = 0;
245 IF SQL%NOTFOUND THEN
246 RETURN NULL;
247 END IF;
248 END IF;
249 RETURN (V_NO);
250 RETURN NULL;
251 EXCEPTION
252 WHEN NO_DATA_FOUND THEN
253 RETURN (NULL);
254 END VERSCFFORMULA;
255 FUNCTION VEND_SHIP_LOCATIONCFFORMULA(DOC_TYPE IN VARCHAR2
256 ,DOC_ID IN NUMBER
257 ,LINE_ID1 IN NUMBER
258 ,ITEM_ID1 IN NUMBER) RETURN VARCHAR2 IS
259 V_S_L VARCHAR2(32);
260 BEGIN
261 IF DOC_TYPE = 'PORD' THEN
262 SELECT
263 V.VENDOR_NO
264 INTO V_S_L
265 FROM
266 PO_ORDR_HDR H,
267 PO_ORDR_DTL D,
268 PO_VEND_MST V,
269 OP_SHIP_MST S
270 WHERE D.PO_ID = DOC_ID
271 AND D.LINE_ID = LINE_ID1
272 AND D.ITEM_ID = ITEM_ID1
273 AND D.PO_ID = H.PO_ID
274 AND V.VENDOR_ID = D.SHIPVEND_ID
275 AND S.SHIPPER_CODE = D.SHIPPER_CODE
276 AND H.DELETE_MARK = 0
277 AND S.DELETE_MARK = 0;
278 IF SQL%NOTFOUND THEN
279 RETURN (NULL);
280 END IF;
281 ELSIF DOC_TYPE = 'OPSO' THEN
282 SELECT
283 V.CUST_NO
284 INTO V_S_L
285 FROM
286 OP_ORDR_HDR H,
287 OP_ORDR_DTL D,
288 OP_CUST_MST V,
289 OP_SHIP_MST S
290 WHERE D.ORDER_ID = DOC_ID
291 AND D.LINE_ID = LINE_ID1
292 AND D.ITEM_ID = ITEM_ID1
293 AND D.ORDER_ID = H.ORDER_ID
294 AND V.CUST_ID = D.SHIPCUST_ID
295 AND S.SHIPPER_CODE = D.SHIPPER_CODE
296 AND H.DELETE_MARK = 0
297 AND S.DELETE_MARK = 0
298 AND V.DELETE_MARK = 0;
299 IF SQL%NOTFOUND THEN
300 RETURN (NULL);
301 END IF;
302 ELSIF DOC_TYPE = 'RECV' THEN
303 SELECT
304 V.VENDOR_NO
305 INTO V_S_L
306 FROM
307 PO_RECV_HDR H,
308 PO_RECV_DTL D,
309 PO_VEND_MST V,
310 OP_SHIP_MST S
311 WHERE D.RECV_ID = DOC_ID
312 AND D.LINE_ID = LINE_ID1
313 AND D.ITEM_ID = ITEM_ID1
314 AND D.RECV_ID = H.RECV_ID
315 AND V.VENDOR_ID = D.SHIPVEND_ID
316 AND S.SHIPPER_CODE = D.SHIPPER_CODE
317 AND H.DELETE_MARK = 0
318 AND S.DELETE_MARK = 0
319 AND V.DELETE_MARK = 0;
320 IF SQL%NOTFOUND THEN
321 RETURN (NULL);
322 END IF;
323 ELSIF DOC_TYPE = 'OPSP' THEN
324 SELECT
325 V.CUST_NO
326 INTO V_S_L
327 FROM
328 OP_BILL_LAD B1,
329 OP_BILL_LAD B2,
330 OP_ORDR_DTL D,
331 OP_CUST_MST V,
332 OP_SHIP_MST S
333 WHERE B1.BOL_ID = DOC_ID
334 AND D.LINE_ID = LINE_ID1
335 AND D.ITEM_ID = ITEM_ID1
336 AND D.BOL_ID = B2.BOL_ID
337 AND V.CUST_ID = D.SHIPCUST_ID
338 AND S.SHIPPER_CODE = D.SHIPPER_CODE
339 AND B1.DELETE_MARK = 0
340 AND B1.DELETE_MARK = 0
341 AND S.DELETE_MARK = 0
342 AND V.DELETE_MARK = 0;
343 IF SQL%NOTFOUND THEN
344 RETURN (NULL);
345 END IF;
346 ELSIF DOC_TYPE = 'OPCR' THEN
347 SELECT
348 V.CUST_NO
349 INTO V_S_L
350 FROM
351 OP_BILL_LAD B1,
352 OP_ORDR_DTL D,
353 OP_CUST_MST V,
354 OP_SHIP_MST S
355 WHERE D.INVOICE_ID = DOC_ID
356 AND D.LINE_ID = LINE_ID1
357 AND D.ITEM_ID = ITEM_ID1
358 AND D.INVOICE_ID = B1.BOL_ID
359 AND V.CUST_ID = D.SHIPCUST_ID
360 AND S.SHIPPER_CODE = B1.SHIPPER_CODE
361 AND B1.DELETE_MARK = 0
362 AND S.DELETE_MARK = 0
363 AND V.DELETE_MARK = 0;
364 IF SQL%NOTFOUND THEN
365 RETURN (NULL);
366 END IF;
367 END IF;
368 RETURN (V_S_L);
369 RETURN NULL;
370 EXCEPTION
371 WHEN NO_DATA_FOUND THEN
372 RETURN (NULL);
373 END VEND_SHIP_LOCATIONCFFORMULA;
374 FUNCTION FRTBILL_MTHDCFFORMULA(DOC_TYPE IN VARCHAR2
375 ,DOC_ID IN NUMBER
376 ,LINE_ID1 IN NUMBER
377 ,ITEM_ID1 IN NUMBER) RETURN VARCHAR2 IS
378 F_M VARCHAR2(4);
379 BEGIN
380 IF DOC_TYPE = 'PORD' THEN
381 SELECT
382 D.FRTBILL_MTHD
383 INTO F_M
384 FROM
385 PO_ORDR_HDR H,
386 PO_ORDR_DTL D,
387 PO_VEND_MST V,
388 OP_SHIP_MST S
389 WHERE D.PO_ID = DOC_ID
390 AND D.LINE_ID = LINE_ID1
391 AND D.ITEM_ID = ITEM_ID1
392 AND D.PO_ID = H.PO_ID
393 AND V.VENDOR_ID = D.SHIPVEND_ID
394 AND S.SHIPPER_CODE = D.SHIPPER_CODE
395 AND H.DELETE_MARK = 0
396 AND S.DELETE_MARK = 0;
397 IF SQL%NOTFOUND THEN
398 RETURN (NULL);
399 END IF;
400 ELSIF DOC_TYPE = 'OPSO' THEN
401 SELECT
402 D.FRTBILL_MTHD
403 INTO F_M
404 FROM
405 OP_ORDR_HDR H,
406 OP_ORDR_DTL D,
407 OP_CUST_MST V,
408 OP_SHIP_MST S
409 WHERE D.ORDER_ID = DOC_ID
410 AND D.LINE_ID = LINE_ID1
411 AND D.ITEM_ID = ITEM_ID1
412 AND D.ORDER_ID = H.ORDER_ID
413 AND V.CUST_ID = D.SHIPCUST_ID
414 AND S.SHIPPER_CODE = D.SHIPPER_CODE
415 AND H.DELETE_MARK = 0
416 AND S.DELETE_MARK = 0
417 AND V.DELETE_MARK = 0;
418 IF SQL%NOTFOUND THEN
419 RETURN (NULL);
420 END IF;
421 ELSIF DOC_TYPE = 'RECV' THEN
422 SELECT
423 D.FRTBILL_MTHD
424 INTO F_M
425 FROM
426 PO_RECV_HDR H,
427 PO_RECV_DTL D,
428 PO_VEND_MST V,
429 OP_SHIP_MST S
430 WHERE D.RECV_ID = DOC_ID
431 AND D.LINE_ID = LINE_ID1
432 AND D.ITEM_ID = ITEM_ID1
433 AND D.RECV_ID = H.RECV_ID
434 AND V.VENDOR_ID = D.SHIPVEND_ID
435 AND S.SHIPPER_CODE = D.SHIPPER_CODE
436 AND H.DELETE_MARK = 0
437 AND S.DELETE_MARK = 0
438 AND V.DELETE_MARK = 0;
439 IF SQL%NOTFOUND THEN
440 RETURN (NULL);
441 END IF;
442 ELSIF DOC_TYPE = 'OPSP' THEN
443 SELECT
444 D.FRTBILL_MTHD
445 INTO F_M
446 FROM
447 OP_BILL_LAD B1,
448 OP_BILL_LAD B2,
449 OP_ORDR_DTL D,
450 OP_CUST_MST V,
451 OP_SHIP_MST S
452 WHERE B1.BOL_ID = DOC_ID
453 AND D.LINE_ID = LINE_ID1
454 AND D.ITEM_ID = ITEM_ID1
455 AND D.BOL_ID = B2.BOL_ID
456 AND V.CUST_ID = D.SHIPCUST_ID
457 AND S.SHIPPER_CODE = D.SHIPPER_CODE
458 AND B1.DELETE_MARK = 0
459 AND B1.DELETE_MARK = 0
460 AND S.DELETE_MARK = 0
461 AND V.DELETE_MARK = 0;
462 IF SQL%NOTFOUND THEN
463 RETURN (NULL);
464 END IF;
465 ELSIF DOC_TYPE = 'OPCR' THEN
466 SELECT
467 D.FRTBILL_MTHD
468 INTO F_M
469 FROM
470 OP_BILL_LAD B1,
471 OP_ORDR_DTL D,
472 OP_CUST_MST V,
473 OP_SHIP_MST S
474 WHERE D.INVOICE_ID = DOC_ID
475 AND D.LINE_ID = LINE_ID1
476 AND D.ITEM_ID = ITEM_ID1
477 AND D.INVOICE_ID = B1.BOL_ID
478 AND V.CUST_ID = D.SHIPCUST_ID
479 AND S.SHIPPER_CODE = B1.SHIPPER_CODE
480 AND B1.DELETE_MARK = 0
481 AND S.DELETE_MARK = 0
482 AND V.DELETE_MARK = 0;
483 IF SQL%NOTFOUND THEN
484 RETURN (NULL);
485 END IF;
486 END IF;
487 RETURN (F_M);
488 RETURN NULL;
489 EXCEPTION
490 WHEN NO_DATA_FOUND THEN
491 RETURN (NULL);
492 END FRTBILL_MTHDCFFORMULA;
493 FUNCTION SHIPPING_MTHDCFFORMULA(DOC_TYPE IN VARCHAR2
494 ,DOC_ID IN NUMBER
495 ,LINE_ID1 IN NUMBER
496 ,ITEM_ID1 IN NUMBER) RETURN VARCHAR2 IS
497 S_M VARCHAR2(4);
498 BEGIN
499 IF DOC_TYPE = 'PORD' THEN
500 SELECT
501 D.SHIP_MTHD
502 INTO S_M
503 FROM
504 PO_ORDR_HDR H,
505 PO_ORDR_DTL D,
506 PO_VEND_MST V,
507 OP_SHIP_MST S
508 WHERE D.PO_ID = DOC_ID
509 AND D.LINE_ID = LINE_ID1
510 AND D.ITEM_ID = ITEM_ID1
511 AND D.PO_ID = H.PO_ID
512 AND V.VENDOR_ID = D.SHIPVEND_ID
513 AND S.SHIPPER_CODE = D.SHIPPER_CODE
514 AND H.DELETE_MARK = 0
515 AND S.DELETE_MARK = 0;
516 IF SQL%NOTFOUND THEN
517 RETURN (NULL);
518 END IF;
519 ELSIF DOC_TYPE = 'OPSO' THEN
520 SELECT
521 D.SHIP_MTHD
522 INTO S_M
523 FROM
524 OP_ORDR_HDR H,
525 OP_ORDR_DTL D,
526 OP_CUST_MST V,
527 OP_SHIP_MST S
528 WHERE D.ORDER_ID = DOC_ID
529 AND D.LINE_ID = LINE_ID1
530 AND D.ITEM_ID = ITEM_ID1
531 AND D.ORDER_ID = H.ORDER_ID
532 AND V.CUST_ID = D.SHIPCUST_ID
533 AND S.SHIPPER_CODE = D.SHIPPER_CODE
534 AND H.DELETE_MARK = 0
535 AND S.DELETE_MARK = 0
536 AND V.DELETE_MARK = 0;
537 IF SQL%NOTFOUND THEN
538 RETURN (NULL);
539 END IF;
540 ELSIF DOC_TYPE = 'RECV' THEN
541 SELECT
542 D.SHIP_MTHD
543 INTO S_M
544 FROM
545 PO_RECV_HDR H,
546 PO_RECV_DTL D,
547 PO_VEND_MST V,
548 OP_SHIP_MST S
549 WHERE D.RECV_ID = DOC_ID
550 AND D.LINE_ID = LINE_ID1
551 AND D.ITEM_ID = ITEM_ID1
552 AND D.RECV_ID = H.RECV_ID
553 AND V.VENDOR_ID = D.SHIPVEND_ID
554 AND S.SHIPPER_CODE = D.SHIPPER_CODE
555 AND H.DELETE_MARK = 0
556 AND S.DELETE_MARK = 0
557 AND V.DELETE_MARK = 0;
558 IF SQL%NOTFOUND THEN
559 RETURN (NULL);
560 END IF;
561 ELSIF DOC_TYPE = 'OPSP' THEN
562 SELECT
563 D.SHIP_MTHD
564 INTO S_M
565 FROM
566 OP_BILL_LAD B1,
567 OP_BILL_LAD B2,
568 OP_ORDR_DTL D,
569 OP_CUST_MST V,
570 OP_SHIP_MST S
571 WHERE B1.BOL_ID = DOC_ID
572 AND D.LINE_ID = LINE_ID1
573 AND D.ITEM_ID = ITEM_ID1
574 AND D.BOL_ID = B2.BOL_ID
575 AND V.CUST_ID = D.SHIPCUST_ID
576 AND S.SHIPPER_CODE = D.SHIPPER_CODE
577 AND B1.DELETE_MARK = 0
578 AND B1.DELETE_MARK = 0
579 AND S.DELETE_MARK = 0
580 AND V.DELETE_MARK = 0;
581 IF SQL%NOTFOUND THEN
582 RETURN (NULL);
583 END IF;
584 ELSIF DOC_TYPE = 'OPCR' THEN
585 SELECT
586 D.SHIP_MTHD
587 INTO S_M
588 FROM
589 OP_BILL_LAD B1,
590 OP_ORDR_DTL D,
591 OP_CUST_MST V,
592 OP_SHIP_MST S
593 WHERE D.INVOICE_ID = DOC_ID
594 AND D.LINE_ID = LINE_ID1
595 AND D.ITEM_ID = ITEM_ID1
596 AND D.INVOICE_ID = B1.BOL_ID
597 AND V.CUST_ID = D.SHIPCUST_ID
598 AND S.SHIPPER_CODE = B1.SHIPPER_CODE
599 AND B1.DELETE_MARK = 0
600 AND S.DELETE_MARK = 0
601 AND V.DELETE_MARK = 0;
602 IF SQL%NOTFOUND THEN
603 RETURN (NULL);
604 END IF;
605 END IF;
606 RETURN (S_M);
607 RETURN NULL;
608 EXCEPTION
609 WHEN NO_DATA_FOUND THEN
610 RETURN (NULL);
611 END SHIPPING_MTHDCFFORMULA;
612 FUNCTION SHIPPER_NAMECFFORMULA(DOC_TYPE IN VARCHAR2
613 ,DOC_ID IN NUMBER
614 ,LINE_ID1 IN NUMBER
615 ,ITEM_ID1 IN NUMBER) RETURN VARCHAR2 IS
616 S_N VARCHAR2(40);
617 BEGIN
618 IF DOC_TYPE = 'PORD' THEN
619 SELECT
620 S.SHIPPER_NAME
621 INTO S_N
622 FROM
623 PO_ORDR_HDR H,
624 PO_ORDR_DTL D,
625 PO_VEND_MST V,
626 OP_SHIP_MST S
627 WHERE D.PO_ID = DOC_ID
628 AND D.LINE_ID = LINE_ID1
629 AND D.ITEM_ID = ITEM_ID1
630 AND D.PO_ID = H.PO_ID
631 AND V.VENDOR_ID = D.SHIPVEND_ID
632 AND S.SHIPPER_CODE = D.SHIPPER_CODE
633 AND H.DELETE_MARK = 0
634 AND S.DELETE_MARK = 0;
635 IF SQL%NOTFOUND THEN
636 RETURN (NULL);
637 END IF;
638 ELSIF DOC_TYPE = 'OPSO' THEN
639 SELECT
640 S.SHIPPER_NAME
641 INTO S_N
642 FROM
643 OP_ORDR_HDR H,
644 OP_ORDR_DTL D,
645 OP_CUST_MST V,
646 OP_SHIP_MST S
647 WHERE D.ORDER_ID = DOC_ID
648 AND D.LINE_ID = LINE_ID1
649 AND D.ITEM_ID = ITEM_ID1
650 AND D.ORDER_ID = H.ORDER_ID
651 AND V.CUST_ID = D.SHIPCUST_ID
652 AND S.SHIPPER_CODE = D.SHIPPER_CODE
653 AND H.DELETE_MARK = 0
654 AND S.DELETE_MARK = 0
655 AND V.DELETE_MARK = 0;
656 IF SQL%NOTFOUND THEN
657 RETURN (NULL);
658 END IF;
659 ELSIF DOC_TYPE = 'RECV' THEN
660 SELECT
661 S.SHIPPER_NAME
662 INTO S_N
663 FROM
664 PO_RECV_HDR H,
665 PO_RECV_DTL D,
666 PO_VEND_MST V,
667 OP_SHIP_MST S
668 WHERE D.RECV_ID = DOC_ID
669 AND D.LINE_ID = LINE_ID1
670 AND D.ITEM_ID = ITEM_ID1
671 AND D.RECV_ID = H.RECV_ID
672 AND V.VENDOR_ID = D.SHIPVEND_ID
673 AND S.SHIPPER_CODE = D.SHIPPER_CODE
674 AND H.DELETE_MARK = 0
675 AND S.DELETE_MARK = 0
676 AND V.DELETE_MARK = 0;
677 IF SQL%NOTFOUND THEN
678 RETURN (NULL);
679 END IF;
680 ELSIF DOC_TYPE = 'OPSP' THEN
681 SELECT
682 S.SHIPPER_NAME
683 INTO S_N
684 FROM
685 OP_BILL_LAD B1,
686 OP_BILL_LAD B2,
687 OP_ORDR_DTL D,
688 OP_CUST_MST V,
689 OP_SHIP_MST S
690 WHERE B1.BOL_ID = DOC_ID
691 AND D.LINE_ID = LINE_ID1
692 AND D.ITEM_ID = ITEM_ID1
693 AND D.BOL_ID = B2.BOL_ID
694 AND V.CUST_ID = D.SHIPCUST_ID
695 AND S.SHIPPER_CODE = D.SHIPPER_CODE
696 AND B1.DELETE_MARK = 0
697 AND B1.DELETE_MARK = 0
698 AND S.DELETE_MARK = 0
699 AND V.DELETE_MARK = 0;
700 IF SQL%NOTFOUND THEN
701 RETURN (NULL);
702 END IF;
703 ELSIF DOC_TYPE = 'OPCR' THEN
704 SELECT
705 S.SHIPPER_NAME
706 INTO S_N
707 FROM
708 OP_BILL_LAD B1,
709 OP_ORDR_DTL D,
710 OP_CUST_MST V,
711 OP_SHIP_MST S
712 WHERE D.INVOICE_ID = DOC_ID
713 AND D.LINE_ID = LINE_ID1
714 AND D.ITEM_ID = ITEM_ID1
715 AND D.INVOICE_ID = B1.BOL_ID
716 AND V.CUST_ID = D.SHIPCUST_ID
717 AND S.SHIPPER_CODE = B1.SHIPPER_CODE
718 AND B1.DELETE_MARK = 0
719 AND S.DELETE_MARK = 0
720 AND V.DELETE_MARK = 0;
721 IF SQL%NOTFOUND THEN
722 RETURN (NULL);
723 END IF;
724 END IF;
725 RETURN (S_N);
726 RETURN NULL;
727 EXCEPTION
728 WHEN NO_DATA_FOUND THEN
729 RETURN (NULL);
730 END SHIPPER_NAMECFFORMULA;
731 FUNCTION DOC_TYPECFFORMULA(DOC_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
732 DOC_TYPE1 VARCHAR2(4);
733 BEGIN
734 SELECT
735 DOC_TYPE
736 INTO DOC_TYPE1
737 FROM
738 DUAL;
739 IF SQL%NOTFOUND THEN
740 RETURN (NULL);
741 ELSE
742 RETURN (DOC_TYPE1);
743 END IF;
744 RETURN NULL;
745 END DOC_TYPECFFORMULA;
746 FUNCTION RANGE1CFFORMULA RETURN VARCHAR2 IS
747 RANGEV VARCHAR2(100);
748 BEGIN
749 SELECT
750 DECODE(FROM_WHSE
751 ,NULL
752 ,DECODE(TO_WHSE
753 ,NULL
754 ,'Warehouse Range : All '
755 ,' Warehouse: All - ' || TO_WHSE)
756 ,DECODE(TO_WHSE
757 ,NULL
758 ,'Warehouse Range: ' || FROM_WHSE || ' - All '
759 ,' Warehouse Range: ' || FROM_WHSE || ' - ' || TO_WHSE))
760 INTO RANGEV
761 FROM
762 DUAL;
763 RETURN (RANGEV);
764 END RANGE1CFFORMULA;
765 FUNCTION RANGE2CFFORMULA RETURN VARCHAR2 IS
766 BEGIN
767 IF FROM_TRANSDATE IS NULL AND TO_TRANSDATE IS NULL THEN
768 RETURN ('Trans Date Range: All');
769 ELSIF FROM_TRANSDATE IS NOT NULL AND TO_TRANSDATE IS NOT NULL THEN
770 RETURN (' Trans Date Range: ' || FROM_TRANSDATE1 || ' - ' || TO_TRANSDATE1);
771 ELSIF FROM_TRANSDATE IS NULL AND TO_TRANSDATE IS NOT NULL THEN
772 RETURN (' Trans Date Range: All - ' || TO_TRANSDATE1);
773 ELSIF FROM_TRANSDATE IS NOT NULL AND TO_TRANSDATE IS NULL THEN
774 RETURN (' Trans Date Range: ' || FROM_TRANSDATE1 || ' - All ');
775 END IF;
776 RETURN NULL;
777 END RANGE2CFFORMULA;
778 FUNCTION UNIT_COSTCFFORMULA(ITEM_ID1 IN NUMBER
779 ,WAREHOUSE IN VARCHAR2
780 ,TRANS_DATE IN DATE) RETURN NUMBER IS
781 UNIT_COST NUMBER;
782 X_RET NUMBER;
783 X_CMPNTCLSID NUMBER;
784 X_ANALYSISCODE VARCHAR2(100);
785 X_COSTMTHD VARCHAR2(100);
786 X_NOROWS NUMBER;
787 BEGIN
788 X_RET := GMF_CMCOMMON.CMCOMMON_GET_COST(ITEM_ID1
789 ,WAREHOUSE
790 ,P_ORGN
791 ,TRANS_DATE
792 ,COST_METHOD
793 ,X_CMPNTCLSID
794 ,X_ANALYSISCODE
795 ,1
796 ,UNIT_COST
797 ,X_NOROWS);
798 IF X_RET = 1 THEN
799 RETURN (UNIT_COST);
800 END IF;
801 RETURN NULL;
802 EXCEPTION
803 WHEN OTHERS THEN
804 RETURN (NULL);
805 END UNIT_COSTCFFORMULA;
806 FUNCTION COST_ACCUMCFFORMULA(UNIT_COSTCF IN NUMBER
807 ,COUNT_QTYSC IN NUMBER) RETURN NUMBER IS
808 COSTACCUM NUMBER;
809 BEGIN
810 SELECT
811 UNIT_COSTCF * COUNT_QTYSC
812 INTO COSTACCUM
813 FROM
814 DUAL;
815 RETURN (COSTACCUM);
816 RETURN NULL;
817 EXCEPTION
818 WHEN OTHERS THEN
819 RETURN (NULL);
820 END COST_ACCUMCFFORMULA;
821 FUNCTION DEFAULTCURRCFFORMULA(COST_ACCUMCF IN NUMBER) RETURN VARCHAR2 IS
822 CONSTANT VARCHAR2(40);
823 BEGIN
824 IF COST_ACCUMCF IS NOT NULL THEN
825 CONSTANT := DEFAULT_CURR;
826 ELSE
827 CONSTANT := NULL;
828 END IF;
829 RETURN (CONSTANT);
830 END DEFAULTCURRCFFORMULA;
831 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
832 BEGIN
833 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
834 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
835 RETURN (TRUE);
836 END BEFOREREPORT;
837 FUNCTION AFTERPFORM RETURN BOOLEAN IS
838 BEGIN
839 DEFAULT_CURR := FND_PROFILE.VALUE('SY$DEFAULT_CURR');
840 FROM_TRANSDATE1 := to_char(FROM_TRANSDATE,'DD-MON-YY');
841 TO_TRANSDATE1 := to_char(TO_TRANSDATE,'DD-MON-YY');
842 RETURN (TRUE);
843 END AFTERPFORM;
844 PROCEDURE GMI_ICR03_XMLP_PKG_HEADER IS
845 BEGIN
846 NULL;
847 END GMI_ICR03_XMLP_PKG_HEADER;
848 FUNCTION AFTERREPORT RETURN BOOLEAN IS
849 BEGIN
850 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
851 RETURN (TRUE);
852 END AFTERREPORT;
853 FUNCTION DOC_TYPECP_P RETURN VARCHAR2 IS
854 BEGIN
855 RETURN DOC_TYPECP;
856 END DOC_TYPECP_P;
857 FUNCTION DOCUMENT_NOCP_P RETURN VARCHAR2 IS
858 BEGIN
859 RETURN DOCUMENT_NOCP;
860 END DOCUMENT_NOCP_P;
861 FUNCTION FORMULA_NOCP_P RETURN VARCHAR2 IS
862 BEGIN
863 RETURN FORMULA_NOCP;
864 END FORMULA_NOCP_P;
865 FUNCTION VERSCP_P RETURN NUMBER IS
866 BEGIN
867 RETURN VERSCP;
868 END VERSCP_P;
869 FUNCTION VEND_SHIP_LOCATIONCP_P RETURN VARCHAR2 IS
870 BEGIN
871 RETURN VEND_SHIP_LOCATIONCP;
872 END VEND_SHIP_LOCATIONCP_P;
873 FUNCTION FRTBILL_MTHDCP_P RETURN VARCHAR2 IS
874 BEGIN
875 RETURN FRTBILL_MTHDCP;
876 END FRTBILL_MTHDCP_P;
877 FUNCTION SHIPPING_MTHDCP_P RETURN VARCHAR2 IS
878 BEGIN
879 RETURN SHIPPING_MTHDCP;
880 END SHIPPING_MTHDCP_P;
881 FUNCTION SHIPPER_NAMECP_P RETURN VARCHAR2 IS
882 BEGIN
883 RETURN SHIPPER_NAMECP;
884 END SHIPPER_NAMECP_P;
885 END GMI_ICR03_XMLP_PKG;
886
[Home] [Help]