1 PACKAGE BODY FUN_NET_APAR_UTILS_GRP AS
2 /* $Header: funntutilb.pls 120.0.12010000.3 2008/10/29 10:12:26 ychandra noship $ */
3
4 PROCEDURE generic_error(routine in varchar2,
5 errcode in number,
6 errmsg in varchar2) IS
7 BEGIN
8 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
9 fnd_message.set_token('ROUTINE', routine);
10 fnd_message.set_token('ERRNO', errcode);
11 fnd_message.set_token('REASON', errmsg);
12 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
13 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, routine, FALSE);
14 END IF;
15 /*fnd_message.raise_error; */
16 END;
17
18 /* This Procedure Returns Y if the Netting batch is complete else returns N '.
19 If there is any system error then return 'E'*/
20 /*p_invoice_id - This is an in parameter which holds the invoice_id.*/
21
22 FUNCTION Get_Invoice_Netted_status (p_invoice_id IN Number)
23 RETURN VARCHAR2
24 IS
25 l_batch_id Number;
26 l_batch_status_code Varchar2(30);
27 x_msg_data VARCHAR2(100);
28 BEGIN
29 if(p_invoice_id IS NOT NULL) then
30 BEGIN
31 select batch_id into l_batch_id
32 from FUN_NET_AP_INVS_ALL
33 where invoice_id=p_invoice_id;
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 RETURN 'N';
37 WHEN OTHERS THEN
38 x_msg_data:='Unknown error in Get_Invoice_Netted_status procedure';
39 generic_error('FUN_NET_APAR_UTILS_GRP.Get_Invoice_Netted_status', sqlcode, sqlerrm || ':' || x_msg_data );
40 RETURN 'E';
41
42 END;
43 if(l_batch_id IS NOT NULL) then
44 select batch_status_code into l_batch_status_code
45 from FUN_NET_BATCHES_ALL
46 where batch_id=l_batch_id;
47 if l_batch_status_code='COMPLETE' then
48 RETURN 'Y';
49 else
50 RETURN 'N';
51 end if;
52 else
53 RETURN 'N';
54 end if;
55 else
56 RETURN 'N'; -- if p_invoice_id is null
57 end if;
58 EXCEPTION
59 WHEN OTHERS THEN
60
61 x_msg_data:='Unknown error in Get_Invoice_Netted_status procedure';
62 generic_error('FUN_NET_APAR_UTILS_GRP.Get_Invoice_Netted_status', sqlcode, sqlerrm || ':' || x_msg_data );
63 RETURN 'E';
64 END Get_Invoice_Netted_status;
65 /* This Procedure Returns batch status and corresponding batch id if the passed invoice_id is valid, else
66 it returns it returns the appropriate error message */
67 /*
68 p_invoice_id - This is an in parameter which holds the invoice_id.
69 x_batch_id - This holds the batch_id of the corresponding invoice_id.
70 x_batch_status - This is an out parameter which holds the status of the batch.
71 x_return_status - This returns S if the procedure call is success else returns E.
72 x_msg_data - This hold the error message in case of failure of this procedure call.
73 */
74 PROCEDURE Get_Netting_Batch_Info(p_invoice_id IN Number,
75 x_batch_id OUT NOCOPY Number,
76 x_batch_status OUT NOCOPY VARCHAR2,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_data OUT NOCOPY VARCHAR2) AS
79 BEGIN
80 if(p_invoice_id IS NOT NULL) then
81 Begin
82 select batch_id into x_batch_id from FUN_NET_AP_INVS_ALL where invoice_id=p_invoice_id;
83 exception
84 when NO_DATA_FOUND then
85 x_return_status:='E'; /* In this case the batch is not netted*/
86 x_msg_data:='Invoice ID was not found in any of the Netting Batch';
87 generic_error('FUN_NET_APAR_UTILS_GRP.Get_Netting_Batch_Info', sqlcode, sqlerrm || ':' || x_msg_data );
88 when OTHERS then
89 x_return_status:='E';
90 x_msg_data:='Unknown error in Get_Netting_Batch_Info procedure';
91 generic_error('FUN_NET_APAR_UTILS_GRP.Get_Netting_Batch_Info', sqlcode, sqlerrm || ':' || x_msg_data );
92 End;
93 if(x_batch_id IS NOT NULL) then
94 select batch_status_code into x_batch_status from FUN_NET_BATCHES_ALL where batch_id=x_batch_id;
95 x_return_status:='S';
96 return;
97 end if;
98 else
99 x_return_status:='E';
100 x_msg_data:='Message from FUN_NET_APAR_UTILS_GRP package : Invoice_id is Null';
101 generic_error('FUN_NET_APAR_UTILS_GRP.Get_Netting_Batch_Info', sqlcode, sqlerrm || ':' || x_msg_data );
102 RETURN;
103 end if;
104 exception
105 when OTHERS then
106 x_return_status:='E';
107 x_msg_data:='Unknown error in Get_Netting_Batch_Info procedure';
108 generic_error('FUN_NET_APAR_UTILS_GRP.Get_Netting_Batch_Info', sqlcode, sqlerrm || ':' || x_msg_data );
109 end Get_Netting_Batch_Info;
110 end FUN_NET_APAR_UTILS_GRP;
[Home] [Help]