[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CUSTOM_SOURCES
Source
1 package body JA_CN_CUSTOM_SOURCES AS
2 --$Header: JACNSCSB.pls 120.10.12020000.2 2013/02/21 06:57:37 chongwan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNSCSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used to create customer source. |
13 --| |
14 --| PROCEDURE LIST |
15 --| |
16 --| PROCEDURE Invoice_Category PUBLIC |
17 --| |
18 --| HISTORY |
19 --| 01/08/2007 yanbo liu Created |
20 --| 05/18/2009 Chaoqun Wu Fixing bug# 8402674 |
21 --| 01/06/2009 Chaoqun Wu Fixing bug#8478003
22 --| |
23 --+======================================================================*/
24
25 --==========================================================================
26 -- FUNCTION NAME:
27 -- Refund_Item Private
28 --
29 -- DESCRIPTION:
30 -- This procedure is used to return source value when the invoice is
31 -- 'Refund' association.
32 --
33 -- PARAMETERS:
34 -- p_invoice_id invoice id
35 --
36 -- DESIGN REFERENCES:
37 -- None
38 --
39 -- CHANGE HISTORY:
40 -- 05/18/2009 Chaoqun Wu Added for fixing bug# 8402674
41 --===========================================================================
42 FUNCTION Refund_Item(p_invoice_id IN NUMBER)
43 RETURN VARCHAR2 IS
44 l_refund_item VARCHAR2(10) := '';
45 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
46 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
47 l_proc_name VARCHAR2(100) :='Refund_Item';
48
49 BEGIN
50 IF (l_proc_level >= l_dbg_level)
51 THEN
52 FND_LOG.String( l_proc_level
53 ,l_module_prefix||'.'||l_proc_name||'.begin'
54 ,'Enter procedure'
55 );
56 FND_LOG.String( l_proc_level
57 ,l_module_prefix||'.'||l_proc_name||'.parameters'
58 ,'p_invoice_id '||p_invoice_id
59 );
60
61 END IF; --(l_proc_level >= l_dbg_level)
62
63 SELECT 'Refund'
64 INTO l_refund_item
65 FROM AP_INVOICES_ALL aia
66 WHERE aia.INVOICE_ID = P_INVOICE_ID
67 and aia.PAY_PROC_TRXN_TYPE_CODE='AR_CUSTOMER_REFUND'
68 and aia.INVOICE_TYPE_LOOKUP_CODE = 'PAYMENT REQUEST';
69
70 RETURN l_refund_item;
71
72 EXCEPTION
73
74 WHEN OTHERS THEN
75 IF (l_proc_level >= l_dbg_level)
76 THEN
77 FND_LOG.String( l_proc_level
78 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
79 ,SQLCODE||':'||SQLERRM);
80 END IF; --(l_proc_level >= l_dbg_level)
81 RAISE;
82 END Refund_Item;
83
84 --==========================================================================
85 -- FUNCTION NAME:
86 -- GDF_Item Private
87 --
88 -- DESCRIPTION:
89 -- This procedure is used to return source value when the invoice source is
90 -- 'Manual Invoice Entry'. The return value is GDF in Invoice distribution
91 -- or header.The priority of distribution is over than header.
92 --
93 -- PARAMETERS:
94 -- p_invoice_id invoice id
95 -- p_invoice_distribution_id invoice distribution id
96 --
97 -- DESIGN REFERENCES:
98 -- None
99 --
100 -- CHANGE HISTORY:
101 -- 06/08/2007 yanbo liu updated
102 --===========================================================================
103 FUNCTION GDF_Item(p_invoice_id IN NUMBER,
104 p_invoice_distribution_id IN NUMBER)
105 RETURN VARCHAR2 IS
106 GDF_Item ap_invoice_distributions_all.global_attribute1%type:=null;
107 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
108 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
109 l_proc_name VARCHAR2(100) :='GDF_Item';
110
111 BEGIN
112 IF (l_proc_level >= l_dbg_level)
113 THEN
114 FND_LOG.String( l_proc_level
115 ,l_module_prefix||'.'||l_proc_name||'.begin'
116 ,'Enter procedure'
117 );
118 FND_LOG.String( l_proc_level
119 ,l_module_prefix||'.'||l_proc_name||'.parameters'
120 ,'p_invoice_id '||p_invoice_id
121 );
122
123 FND_LOG.String(l_proc_level
124 ,l_module_prefix||'.'||l_proc_name||'.parameters'
125 ,'p_invoice_distribution_id'||p_invoice_distribution_id
126 );
127
128 END IF; --(l_proc_level >= l_dbg_level)
129
130 --First get custom source value from distribution
131
132 SELECT GLOBAL_ATTRIBUTE1
133 INTO GDF_ITEM
134 FROM AP_INVOICE_DISTRIBUTIONS_ALL
135 WHERE INVOICE_DISTRIBUTION_ID=P_INVOICE_DISTRIBUTION_ID;
136
137 --IF THE VALUE IN DISTRIBUTION IS NULL,
138 --THEN GET THE VALUE FROM HEADER.
139 IF GDF_ITEM IS NULL THEN
140 SELECT GLOBAL_ATTRIBUTE1
141 INTO GDF_ITEM
142 FROM AP_INVOICES_ALL
143 WHERE INVOICE_ID=P_INVOICE_ID;
144 END IF;
145
146 RETURN GDF_ITEM;
147
148 -- RETURN nvl(GDF_ITEM,'gdf_item_0') ;
149
150 EXCEPTION
151
152 WHEN OTHERS THEN
153 IF (l_proc_level >= l_dbg_level)
154 THEN
155 FND_LOG.String( l_proc_level
156 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
157 ,SQLCODE||':'||SQLERRM);
158 END IF; --(l_proc_level >= l_dbg_level)
159 RAISE;
160 -- return('gdf_item0');
161 END GDF_Item;
162 --==========================================================================
163 -- FUNCTION NAME:
164 -- Master_Item Public
165 --
166 -- DESCRIPTION:
167 -- This procedure is used to return source value when the invoice source is
168 -- 'ERS'.The return value is Category Set In Master Item
169 --
170 -- PARAMETERS:
171 -- p_invoice_distribution_id invoice distribution id
172 --
173 -- DESIGN REFERENCES:
174 -- None
175 --
176 -- CHANGE HISTORY:
177 -- 06/08/2007 yanbo liu updated
178 -- 09/08/2007 yanbo liu updated
179 -- 07/12/2007 xiao lv updated
180 -- get item logic change
181 --===========================================================================
182 FUNCTION MASTER_ITEM_UPDATE(P_INVOICE_ID IN NUMBER,
183 P_INVOICE_DISTRIBUTION_ID IN NUMBER)
184 RETURN VARCHAR2 IS
185 MASTER_ITEM VARCHAR(100):=null;
186 L_INVOICE_LINE_NUMBER NUMBER;
187 L_INVOICE_ID NUMBER;
188 --new variable
189 L_PO_HEADER_ID NUMBER;
190 L_PO_LINE_ID NUMBER;
191 --
192 L_DBG_LEVEL NUMBER :=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
193 L_PROC_LEVEL NUMBER :=FND_LOG.LEVEL_PROCEDURE;
194 L_PROC_NAME VARCHAR2(100) :='MASTER_ITEM';
195 a EXCEPTION;
196
197
198 BEGIN
199 IF (l_proc_level >= l_dbg_level)
200 THEN
201 FND_LOG.String( l_proc_level
202 ,l_module_prefix||'.'||l_proc_name||'.begin'
203 ,'Enter procedure'
204 );
205
206 FND_LOG.String(l_proc_level
207 ,l_module_prefix||'.'||l_proc_name||'.parameters'
208 ,'p_invoice_distribution_id '||p_invoice_distribution_id
209 );
210
211 END IF; --(l_proc_level >= l_dbg_level)
212
213 -- get the invoice line number and invoice id from ap_invoice_distributions_all
214 SELECT INVOICE_LINE_NUMBER,INVOICE_ID
215 INTO L_INVOICE_LINE_NUMBER,L_INVOICE_ID
216 FROM AP_INVOICE_DISTRIBUTIONS_ALL
217 WHERE INVOICE_DISTRIBUTION_ID=P_INVOICE_DISTRIBUTION_ID ;
218
219 --get key flexfield concatenated_segments as source value
220 --CATEGORY SET NAME is 'Cash Flow Category'
221 BEGIN
222 SELECT MC.CONCATENATED_SEGMENTS
223 INTO MASTER_ITEM
224 FROM MTL_CATEGORIES_B_KFV MC
225 WHERE CATEGORY_ID IN(
226 SELECT CATEGORY_ID
227 FROM MTL_ITEM_CATEGORIES MIC
228 WHERE MIC.CATEGORY_SET_ID IN (
229 SELECT CATEGORY_SET_ID
230 FROM MTL_CATEGORY_SETS_TL MCST
231 WHERE MCST.LANGUAGE = USERENV('LANG')
232 AND MCST.CATEGORY_SET_NAME = 'Cash Flow Category')
233 AND INVENTORY_ITEM_ID IN(
234 SELECT ITEM_ID
235 FROM PO_LINES_ALL
236 WHERE PO_HEADER_ID = (
237 SELECT PO_HEADER_ID
238 FROM AP_INVOICE_LINES_ALL AP
239 WHERE AP.INVOICE_ID=L_INVOICE_ID
240 AND AP.LINE_NUMBER=L_INVOICE_LINE_NUMBER)
241 AND PO_LINE_ID = (
242 SELECT PO_LINE_ID
243 FROM AP_INVOICE_LINES_ALL AP
244 WHERE AP.INVOICE_ID=L_INVOICE_ID
245 AND AP.LINE_NUMBER=L_INVOICE_LINE_NUMBER)
246 )
247 AND ORGANIZATION_ID IN(
248 SELECT ORG_ID
249 FROM AP_INVOICE_LINES_ALL AP
250 WHERE AP.INVOICE_ID=L_INVOICE_ID
251 AND AP.LINE_NUMBER=L_INVOICE_LINE_NUMBER)
252 );
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 --updated by lyb, for custom source for tax 6666473
257 IF MASTER_ITEM IS NULL
258 THEN
259 SELECT PO_HEADER_ID,
260 PO_LINE_ID
261 INTO L_PO_HEADER_ID,
262 L_PO_LINE_ID
263 FROM ap_invoice_lines_all al
264 WHERE RCV_TRANSACTION_ID = (
265 SELECT RCV_TRANSACTION_ID
266 FROM ap_invoice_lines_all ap
267 WHERE ap.invoice_id = P_INVOICE_ID
268 AND ap.LINE_NUMBER = L_INVOICE_LINE_NUMBER)
269
270 AND INVOICE_ID = P_INVOICE_ID
271 AND PO_HEADER_ID IS NOT NULL
272 AND PO_LINE_ID IS NOT NULL;
273
274 --get key flexfield concatenated_segments as source value
275 --CATEGORY SET NAME is 'Cash Flow Category'
276 SELECT MC.CONCATENATED_SEGMENTS
277 INTO MASTER_ITEM
278 FROM MTL_CATEGORIES_B_KFV MC
279 WHERE CATEGORY_ID IN(
280 SELECT CATEGORY_ID
281 FROM MTL_ITEM_CATEGORIES MIC
282 WHERE MIC.CATEGORY_SET_ID IN (
283 SELECT CATEGORY_SET_ID
284 FROM MTL_CATEGORY_SETS_TL MCST
285 WHERE MCST.LANGUAGE = USERENV('LANG')
286 AND MCST.CATEGORY_SET_NAME = 'Cash Flow Category')
287 AND INVENTORY_ITEM_ID IN(
288 SELECT ITEM_ID
289 FROM PO_LINES_ALL
290 WHERE PO_HEADER_ID = L_PO_HEADER_ID
291 AND PO_LINE_ID = L_PO_LINE_ID
292 )
293 AND ORGANIZATION_ID IN(
294 SELECT ORG_ID
295 FROM AP_INVOICE_LINES_ALL AP
296 WHERE AP.INVOICE_ID=L_INVOICE_ID
297 AND AP.LINE_NUMBER=L_INVOICE_LINE_NUMBER)
298 );
299
300
301 END IF;
302
303 IF MASTER_ITEM IS NOT NULL THEN
304 RETURN MASTER_ITEM;
305 END IF;
306
307 RAISE A;
308 END;
309 --==========================================================================
310
311 --==========================================================================
312
313 RETURN MASTER_ITEM;
314 --RETURN nvl(MASTER_ITEM,'master_item_0');
315
316 EXCEPTION
317
318 WHEN OTHERS THEN
319 IF (l_proc_level >= l_dbg_level)
320 THEN
321 FND_LOG.String( l_proc_level
322 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
323 ,SQLCODE||':'||SQLERRM);
324 END IF; --(l_proc_level >= l_dbg_level)
325 IF MASTER_ITEM IS NULL THEN
326 MASTER_ITEM := GDF_Item(p_invoice_id,
327 p_invoice_distribution_id
328 );
329 RETURN MASTER_ITEM;
330 END IF;
331 RAISE;
332 -- return('master_item_exp');
333 END MASTER_ITEM_UPDATE;
334
335
336
337 --==========================================================================
338 -- FUNCTION NAME:
339 -- Expense_Item Public
340 --
341 -- DESCRIPTION:
342 -- This procedure is used to return source value when the invoice source is
343 -- 'Oracle Internet Expenses' or 'Payables Expense Reports'.
344 -- The return value is Expenses Item.
345 --
346 --
347 -- PARAMETERS:
348 -- invoice_distribution_id invoice distribution id
349 --
350 -- DESIGN REFERENCES:
351 -- None
352 --
353 -- CHANGE HISTORY:
354 -- 06/08/2007 yanbo liu updated
355 --===========================================================================
356 FUNCTION Expense_Item(P_INVOICE_ID IN NUMBER,
357 p_invoice_distribution_id IN NUMBER)
358 RETURN VARCHAR2 IS
359 Expense_Item_desc ap_invoice_distributions_all.description%type:=null;
360 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
361 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
362 l_proc_name VARCHAR2(100) :='Expense_Item';
363
364 BEGIN
365 IF (l_proc_level >= l_dbg_level)
366 THEN
367 FND_LOG.String( l_proc_level
368 ,l_module_prefix||'.'||l_proc_name||'.begin'
369 ,'Enter procedure'
370 );
371
372 FND_LOG.String(l_proc_level
373 ,l_module_prefix||'.'||l_proc_name||'.parameters'
374 ,'invoice_distribution_id'||p_invoice_distribution_id
375 );
376
377 END IF; --(l_proc_level >= l_dbg_level)
378
379 --get the custom value from destribution
380 SELECT description
381 INTO Expense_Item_desc
382 FROM ap_invoice_distributions_all
383 WHERE invoice_distribution_id=p_invoice_distribution_id ;
384 RETURN Expense_Item_desc;
385 --RETURN nvl(Expense_Item_desc,'expense_item_desc_0');
386 EXCEPTION
387 WHEN OTHERS THEN
388 IF (l_proc_level >= l_dbg_level)
389 THEN
390 FND_LOG.String( l_proc_level
391 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
392 ,SQLCODE||':'||SQLERRM);
393 END IF; --(l_proc_level >= l_dbg_level)
394 IF Expense_Item_desc IS NULL THEN
395 Expense_Item_desc := GDF_Item(p_invoice_id,
396 p_invoice_distribution_id
397 );
398 RETURN Expense_Item_desc;
399 END IF;
400 RAISE;
401 -- return('expense_item0');
402 END Expense_Item;
403 --==========================================================================
404 -- FUNCTION NAME:
405 -- Master_Item Public
406 --
407 -- DESCRIPTION:
408 -- This procedure is used to return source value when the invoice source is
409 -- 'ERS'.The return value is Category Set In Master Item
410 --
411 -- PARAMETERS:
412 -- p_invoice_distribution_id invoice distribution id
413 --
414 -- DESIGN REFERENCES:
415 -- None
416 --
417 -- CHANGE HISTORY:
418 -- 06/08/2007 yanbo liu updated
419 -- 09/08/2007 yanbo liu updated
420 -- get item logic change
421 --===========================================================================
422 FUNCTION MASTER_ITEM(P_INVOICE_ID IN NUMBER,
423 P_INVOICE_DISTRIBUTION_ID IN NUMBER)
424 RETURN VARCHAR2 IS
425 MASTER_ITEM VARCHAR(100):=null;
426 L_INVOICE_LINE_NUMBER NUMBER;
427 L_INVOICE_ID NUMBER;
428 L_DBG_LEVEL NUMBER :=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
429 L_PROC_LEVEL NUMBER :=FND_LOG.LEVEL_PROCEDURE;
430 L_PROC_NAME VARCHAR2(100) :='MASTER_ITEM';
431 a EXCEPTION;
432
433
434 BEGIN
435 IF (l_proc_level >= l_dbg_level)
436 THEN
437 FND_LOG.String( l_proc_level
438 ,l_module_prefix||'.'||l_proc_name||'.begin'
439 ,'Enter procedure'
440 );
441
442 FND_LOG.String(l_proc_level
443 ,l_module_prefix||'.'||l_proc_name||'.parameters'
444 ,'p_invoice_distribution_id '||p_invoice_distribution_id
445 );
446
447 END IF; --(l_proc_level >= l_dbg_level)
448
449 -- get the invoice line number and invoice id from ap_invoice_distributions_all
450 SELECT INVOICE_LINE_NUMBER,INVOICE_ID
451 INTO L_INVOICE_LINE_NUMBER,L_INVOICE_ID
452 FROM AP_INVOICE_DISTRIBUTIONS_ALL
453 WHERE INVOICE_DISTRIBUTION_ID=P_INVOICE_DISTRIBUTION_ID ;
454
455 --get key flexfield concatenated_segments as source value
456 --CATEGORY SET NAME is 'Cash Flow Category'
457 SELECT MC.CONCATENATED_SEGMENTS
458 INTO MASTER_ITEM
459 FROM MTL_CATEGORIES_B_KFV MC
460 WHERE CATEGORY_ID IN(
461 SELECT CATEGORY_ID
462 FROM MTL_ITEM_CATEGORIES MIC
463 WHERE MIC.CATEGORY_SET_ID IN (
464 SELECT CATEGORY_SET_ID
465 FROM MTL_CATEGORY_SETS_TL MCST
466 WHERE MCST.LANGUAGE = USERENV('LANG')
467 AND MCST.CATEGORY_SET_NAME = 'Cash Flow Category')
468 AND INVENTORY_ITEM_ID IN(
469 SELECT ITEM_ID
470 FROM PO_LINES_ALL
471 WHERE PO_HEADER_ID = (
472 SELECT PO_HEADER_ID
473 FROM AP_INVOICE_LINES_ALL AP
474 WHERE AP.INVOICE_ID=L_INVOICE_ID
475 AND AP.LINE_NUMBER=L_INVOICE_LINE_NUMBER)
476 AND PO_LINE_ID = (
477 SELECT PO_LINE_ID
478 FROM AP_INVOICE_LINES_ALL AP
479 WHERE AP.INVOICE_ID=L_INVOICE_ID
480 AND AP.LINE_NUMBER=L_INVOICE_LINE_NUMBER)
481 )
482 AND ORGANIZATION_ID IN(
483 SELECT ORG_ID
484 FROM AP_INVOICE_LINES_ALL AP
485 WHERE AP.INVOICE_ID=L_INVOICE_ID
486 AND AP.LINE_NUMBER=L_INVOICE_LINE_NUMBER)
487 );
488
489 RETURN MASTER_ITEM;
490 --RETURN nvl(MASTER_ITEM,'master_item_0');
491
492 EXCEPTION
493
494 WHEN OTHERS THEN
495 IF (l_proc_level >= l_dbg_level)
496 THEN
497 FND_LOG.String( l_proc_level
498 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
499 ,SQLCODE||':'||SQLERRM);
500 END IF; --(l_proc_level >= l_dbg_level)
501 IF MASTER_ITEM IS NULL THEN
502 MASTER_ITEM := GDF_Item(p_invoice_id,
503 p_invoice_distribution_id
504 );
505 RETURN MASTER_ITEM;
506 END IF;
507 RAISE;
508 -- return('master_item_exp');
509 END Master_Item;
510
511 --==========================================================================
512 -- FUNCTION NAME:
513 -- Invoice_Category Public
514 --
515 -- DESCRIPTION:
516 -- This procedure is used to return different source value according to
517 -- invoice source input.
518 --
519 -- PARAMETERS:
520 -- p_Invoice_Source invoice source
521 -- p_invoice_id invoice id
522 -- p_invoice_line_number invoice line number
523 -- p_distribution_line_number distribution line number
524 --
525 -- DESIGN REFERENCES:
526 -- None
527 --
528 -- CHANGE HISTORY:
529 -- 06/08/2007 yanbo liu updated
530 --===========================================================================
531 FUNCTION Invoice_Category(p_Invoice_Source IN VARCHAR2,
532 p_invoice_id IN NUMBER,
533 p_invoice_distribution_id IN NUMBER)
534 RETURN VARCHAR2 IS
535 Source_Value VARCHAR2(200):=NULL;
536 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
537 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
538 l_proc_name VARCHAR2(100) :='Invoice Category';
539 BEGIN
540
541 IF (l_proc_level >= l_dbg_level)
542 THEN
543 FND_LOG.String( l_proc_level
544 ,l_module_prefix||'.'||l_proc_name||'.begin'
545 ,'Enter procedure'
546 );
547 FND_LOG.String( l_proc_level
548 ,l_module_prefix||'.'||l_proc_name||'.parameters'
549 ,'p_Invoice_Source '||p_Invoice_Source
550 );
551 FND_LOG.String( l_proc_level
552 ,l_module_prefix||'.'||l_proc_name||'.parameters'
553 ,'p_invoice_id '||p_invoice_id
554 );
555
556 FND_LOG.String(l_proc_level
557 ,l_module_prefix||'.'||l_proc_name||'.parameters'
558 ,'p_invoice_distribution_id'||p_invoice_distribution_id
559 );
560
561 END IF; --(l_proc_level >= l_dbg_level)
562
563 -- This function will call different function and return different value
564 -- according to the parameter Invoice Source.
565
566 IF p_Invoice_Source IS NULL OR p_invoice_id IS NULL OR p_invoice_distribution_id IS NULL THEN
567 RETURN NULL;
568 ELSE
569 CASE p_Invoice_Source
570 WHEN 'Manual Invoice Entry'
571 THEN
572 Source_Value := GDF_Item(p_invoice_id,
573 p_invoice_distribution_id
574 );
575 WHEN 'SelfService'
576 THEN
577 Source_Value := Expense_Item(p_invoice_id,
578 p_invoice_distribution_id);
579 WHEN 'XpenseXpress'
580 THEN
581 Source_Value := Expense_Item(p_invoice_id,
582 p_invoice_distribution_id);
583 WHEN 'ERS'
584 THEN
585 Source_Value := MASTER_ITEM_UPDATE(p_invoice_id,
586 p_invoice_distribution_id);
587 --Begin: Added for fixing bug# 8402674
588 WHEN 'Receivables'
589 THEN
590 Source_Value := Refund_ITEM(p_invoice_id);
591 --End: Added for fixing bug# 8402674
592 ELSE
593 Source_Value := GDF_Item(p_invoice_id,
594 p_invoice_distribution_id
595 );
596 END CASE ;
597
598 IF (l_proc_level >= l_dbg_level)
599 THEN
600 FND_LOG.STRING(l_proc_level,
601 l_module_prefix|| '.' || l_proc_name || '.end',
602 'end procedure');
603 END IF;
604 RETURN Source_Value;
605 -- RETURN nvl(Source_Value,'0');
606 END IF;
607 EXCEPTION
608 WHEN OTHERS THEN
609 IF (l_proc_level >= l_dbg_level)
610 THEN
611 FND_LOG.String( l_proc_level
612 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
613 ,SQLCODE||':'||SQLERRM);
614 END IF; --(l_proc_level >= l_dbg_level)
615
616 RETURN NULL;
617 RAISE;
618 -- return('invoice_category');
619 END Invoice_Category;
620 --==========================================================================
621 -- FUNCTION NAME:
622 -- Invoice_GainLoss_Category Public
623 --
624 -- DESCRIPTION:
625 -- This procedure is used to return different source value according to
626 -- invoice payment id.
627 --
628 -- PARAMETERS:
629 -- p_invoice_payment_id
630 --
631 --
632 -- DESIGN REFERENCES:
633 -- None
634 --
635 -- CHANGE HISTORY:
636 -- 06/08/2007 Chongwu Li updated
637 --===========================================================================
638 FUNCTION Invoice_GainLoss_Category(p_invoice_payment_id IN NUMBER)
639 RETURN VARCHAR2 IS
640 Source_Value VARCHAR2(200):=NULL;
641 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
642 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
643 l_proc_name VARCHAR2(100) :='Invoice_Payment_Category';
644 ln_invoice_id NUMBER;
645 lv_invoice_source VARCHAR2(100);
646 BEGIN
647
648 IF (l_proc_level >= l_dbg_level)
649 THEN
650 FND_LOG.String( l_proc_level
651 ,l_module_prefix||'.'||l_proc_name||'.begin'
652 ,'Enter procedure'
653 );
654 FND_LOG.String( l_proc_level
655 ,l_module_prefix||'.'||l_proc_name||'.parameters'
656 ,'p_Invoice_Source '||p_invoice_payment_id
657 );
658 END IF; --(l_proc_level >= l_dbg_level)
659
660 -- This function will call different function and return different value
661 -- according to the parameter Invoice Source.
662
663 IF p_invoice_payment_id IS NOT NULL THEN
664
665 BEGIN
666
667
668 SELECT apa.invoice_id, aia.source
669 INTO ln_invoice_id, lv_invoice_source
670 FROM ap_invoice_payments_all apa, AP_INVOICES_ALL aia
671 WHERE apa.invoice_payment_id = p_invoice_payment_id
672 AND aia.invoice_id = apa.invoice_id;
673
674 CASE lv_invoice_source
675 WHEN 'Manual Invoice Entry'
676 THEN
677 Source_Value := GDF_Item(ln_invoice_id,
678 ''
679 );
680 WHEN 'SelfService'
681 THEN
682 Source_Value := Expense_Item(ln_invoice_id,
683 '');
684 WHEN 'XpenseXpress'
685 THEN
686 Source_Value := Expense_Item(ln_invoice_id,
687 '');
688 WHEN 'ERS'
689 THEN
690 Source_Value := MASTER_ITEM_UPDATE(ln_invoice_id,
691 '');
692 WHEN 'Receivables'
693 THEN
694 Source_Value := Refund_ITEM(ln_invoice_id);
695 ELSE
696 Source_Value := GDF_Item(ln_invoice_id,
697 ''
698 );
699 END CASE ;
700
701 return Source_Value;
702 EXCEPTION
703 WHEN OTHERS THEN
704 IF (l_proc_level >= l_dbg_level)
705 THEN
706 FND_LOG.String( l_proc_level
707 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
708 ,SQLCODE||':'||SQLERRM);
709 END IF; --(l_proc_level >= l_dbg_level)
710 RETURN 'EXCEPTION';
711 END;
712
713 END IF;
714
715
716 EXCEPTION
717 WHEN OTHERS THEN
718 IF (l_proc_level >= l_dbg_level)
719 THEN
720 FND_LOG.String( l_proc_level
721 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
722 ,SQLCODE||':'||SQLERRM);
723 END IF; --(l_proc_level >= l_dbg_level)
724 RETURN 'EXCEPTION';
725 -- return('invoice_category');
726 END Invoice_GainLoss_Category;
727 --==========================================================================
728 -- FUNCTION NAME:
729 -- GET_PROJECT_NUM Public
730 --
731 -- DESCRIPTION:
732 -- This procedure is used to return project number according to project id
733 --
734 --
735 -- PARAMETERS:
736 -- Project_id Project id
737 --
738 -- DESIGN REFERENCES:
739 -- None
740 --
741 -- CHANGE HISTORY:
742 -- 09/08/2007 yanbo liu updated
743 -- 01/06/2009 Chaoqun Wu Fixing bug#8478003
744 --===========================================================================
745
746 FUNCTION GET_PROJECT_NUM(p_project_id IN NUMBER) --Fixing bug#8478003
747 RETURN VARCHAR2 IS
748 l_project_num PA_PROJECTS_ALL.Segment1%type:=null;
749 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
750 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
751 l_proc_name VARCHAR2(100) :='GET_PROJECT_NUM';
752 BEGIN
753
754 IF (l_proc_level >= l_dbg_level)
755 THEN
756 FND_LOG.String( l_proc_level
757 ,l_module_prefix||'.'||l_proc_name||'.begin'
758 ,'Enter procedure'
759 );
760 FND_LOG.String( l_proc_level
761 ,l_module_prefix||'.'||l_proc_name||'.parameters'
762 ,'Project_id '||p_project_id
763 );
764
765 END IF; --(l_proc_level >= l_dbg_level)
766
767 --get project number by project id.
768 IF p_project_id IS NULL THEN
769 RETURN NULL;
770 ELSE
771 SELECT ppa.SEGMENT1
772 INTO l_project_num
773 FROM PA_PROJECTS_ALL ppa
774 WHERE ppa.PROJECT_ID = p_project_id;
775 RETURN l_project_num;
776 END IF;
777
778 EXCEPTION
779
780 WHEN OTHERS THEN
781 IF (l_proc_level >= l_dbg_level)
782 THEN
783 FND_LOG.String( l_proc_level
784 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
785 ,SQLCODE||':'||SQLERRM);
786 END IF; --(l_proc_level >= l_dbg_level)
787 RETURN NULL;
788 RAISE;
789 END GET_PROJECT_NUM;
790
791
792 BEGIN
793 NULL;
794 -- Initialization
795 -- <Statement>
796 end JA_CN_CUSTOM_SOURCES;