[Home] [Help]
PACKAGE BODY: APPS.AP_IBY_UTILITY_PKG
Source
1 PACKAGE BODY AP_IBY_UTILITY_PKG as
2 /* $Header: apibexub.pls 120.5 2006/02/24 02:38:06 mswamina noship $ */
3
4 -- Package global
5 -- FND_LOG related variables to enable logging for this package
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_IBY_UTILITY_PKG';
8 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
9 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
10 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
11 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
12 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
13 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
14 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
15
16 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
17 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
18 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
19 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
20 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
21 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
22 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
23 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'AP.PLSQL.AP_IBY_UTILITY_PKG.';
24
25 FUNCTION Get_Default_Iby_Bank_Acct_Id (
26 X_Vendor_Id IN NUMBER,
27 X_Vendor_Site_Id IN NUMBER DEFAULT NULL,
28 X_Payment_Function IN VARCHAR2 DEFAULT NULL, /* bug 5000194 */
29 X_Org_Id IN NUMBER DEFAULT NULL,
30 X_Currency_Code IN VARCHAR2,
31 X_Calling_Sequence IN VARCHAR2 DEFAULT NULL )
32 RETURN NUMBER
33 IS
34
35 Iby_Trxn_Attributes_Rec IBY_DISBURSEMENT_COMP_PUB.Trxn_Attributes_Rec_Type;
36 Iby_Payee_BankAccount_Rec IBY_DISBURSEMENT_COMP_PUB.Payee_BankAccount_Rec_Type;
37
38 l_default_bank_acct_id NUMBER;
39 l_current_calling_sequence VARCHAR2(2000);
40 l_debug_info VARCHAR2(2000);
41 l_return_status VARCHAR2(1);
42 l_msg_count NUMBER(15);
43 l_msg_data VARCHAR2(2000);
44 l_api_name CONSTANT VARCHAR2(100) := 'GET_DEFAULT_IBY_BANK_ACCT_ID';
45 l_error_msg VARCHAR2(2000);
46 l_vendor_type_lookup_code VARCHAR2(30); -- bug 5000194
47
48 IBY_API_ERROR EXCEPTION;
49
50 BEGIN
51
52 l_current_calling_sequence := X_calling_sequence||'->'||
53 'AP_IBY_UTILITY_PKG.Get_Default_Iby_Bank_Acct_Id';
54 --
55 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
56 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
57 ||' X_Vendor_Id: '|| X_Vendor_Id
58 ||', X_Vendor_Site_Id: '||X_Vendor_Site_Id
59 ||', X_Org_Id: '||X_Org_Id
60 ||', X_Currency_Code: '||X_Currency_Code);
61 END IF;
62
63 l_debug_info := 'Populating IBY_Txn_Attriibuytes_Rec';
64
65 Iby_Trxn_Attributes_Rec.Application_Id := 200;
66 Iby_Trxn_Attributes_Rec.Payer_Org_Id := X_org_id;
67 Iby_Trxn_Attributes_Rec.Payer_Org_Type := 'OPERATING_UNIT';
68 Iby_Trxn_Attributes_Rec.Supplier_Site_Id := X_Vendor_Site_Id;
69 Iby_Trxn_Attributes_Rec.Payment_Currency := X_Currency_Code;
70 --
71 /* Bug 5000194 */
72
73 -- As per the discussion with Omar/Jayanta, we will only
74 -- have payables payment function and no more employee expenses
75 -- payment function.
76
77 Begin
78 Select party_id, 'PAYABLES_DISB',
79 vendor_type_lookup_code
80 Into Iby_Trxn_Attributes_Rec.Payee_Party_Id,
81 Iby_Trxn_Attributes_Rec.Payment_Function,
82 l_vendor_type_lookup_code
83 From Ap_Suppliers
84 Where vendor_id = X_Vendor_Id;
85 Exception
86 WHEN NO_DATA_FOUND THEN
87 l_debug_info := 'Supplier Does not exists';
88 --
89 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
90 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
91 END IF;
92
93 Raise IBY_API_ERROR;
94 End;
95
96
97 /* Bug 500194. Based on bug 4965233 party_site_id will be null
98 for employee */
99 IF X_Vendor_Site_Id IS NOT NULL AND l_vendor_type_lookup_code <> 'EMPLOYEE' THEN
100
101 Select party_site_id
102 Into Iby_Trxn_Attributes_Rec.Payee_Party_Site_Id
103 From Ap_Supplier_Sites_All
104 Where vendor_site_id = X_Vendor_Site_Id;
105
106 END IF;
107
108 l_debug_info := 'Calling IBY API';
109
110 IBY_DISBURSEMENT_COMP_PUB.Get_Default_Payee_Bank_Acc
111 (p_api_version => 1.0,
112 p_init_msg_list => FND_API.G_TRUE,
113 p_trxn_attributes_rec => Iby_Trxn_Attributes_Rec,
114 x_return_status => l_return_status,
115 x_msg_count => l_msg_count,
116 x_msg_data => l_msg_data,
117 x_payee_bankaccount => Iby_Payee_BankAccount_Rec);
118
119 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
120 l_debug_info := 'Sucessfull IBY API Call ';
121 --
122 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
123 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
124 END IF;
125
126 l_default_bank_acct_id := Iby_Payee_BankAccount_Rec.Payee_BankAccount_Id;
127
128 ELSE
129
130 Raise IBY_API_ERROR;
131
132 END IF;
133
134 RETURN(l_default_bank_acct_id);
135
136 EXCEPTION
137 --
138 WHEN IBY_API_ERROR THEN
139 RETURN (NULL);
140 IF (NVL(l_msg_count, 0) > 1) THEN
141 FOR I IN 1..l_msg_count
142 LOOP
143 l_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
144 p_encoded => 'T');
145 FND_MESSAGE.Set_Encoded(l_error_msg);
146 END LOOP;
147 END IF;
148
149 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
150 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
151 FND_MESSAGE.SET_TOKEN('MSG_COUNT', l_msg_count );
152 FND_MESSAGE.SET_TOKEN('MSG_DATA', l_debug_info );
153 FND_MESSAGE.SET_TOKEN('PARAMETERS','X_Vendor_Id: '||X_Vendor_Id
154 ||',X_vendor_Site_id: '||X_vendor_site_id);
155 --
156 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
157 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'IBY_API_ERROR');
158 END IF;
159 --
160 WHEN OTHERS THEN
161 IF (SQLCODE <> -20001 ) THEN
162 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
163 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM );
164 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
165 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
166 FND_MESSAGE.SET_TOKEN('PARAMETERS','X_Vendor_Id: '||X_Vendor_Id
167 ||',X_vendor_Site_id: '||X_vendor_site_id);
168 END IF;
169 --
170 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
171 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,SQLERRM);
172 END IF;
173
174 END Get_Default_Iby_Bank_Acct_Id;
175
176 FUNCTION Get_Default_Iby_Bank_Acct_Id (
177 X_Party_Id IN NUMBER,
178 X_Payment_Function IN VARCHAR2,
179 X_Party_Site_Id IN NUMBER DEFAULT NULL,
180 X_Org_Id IN NUMBER DEFAULT NULL,
181 X_Currency_Code IN VARCHAR2,
182 X_Calling_Sequence IN VARCHAR2 DEFAULT NULL )
183 RETURN NUMBER
184 IS
185
186 Iby_Trxn_Attributes_Rec IBY_DISBURSEMENT_COMP_PUB.Trxn_Attributes_Rec_Type;
187 Iby_Payee_BankAccount_Rec IBY_DISBURSEMENT_COMP_PUB.Payee_BankAccount_Rec_Type;
188
189 l_default_bank_acct_id NUMBER;
190 l_current_calling_sequence VARCHAR2(2000);
191 l_debug_info VARCHAR2(2000);
192 l_return_status VARCHAR2(1);
193 l_msg_count NUMBER(15);
194 l_msg_data VARCHAR2(2000);
195 l_api_name CONSTANT VARCHAR2(100) := 'GET_DEFAULT_IBY_BANK_ACCT_ID';
196 l_error_msg VARCHAR2(2000);
197 IBY_API_ERROR EXCEPTION;
198
199 BEGIN
200
201 l_current_calling_sequence := X_calling_sequence||'->'||
202 'AP_IBY_UTILITY_PKG.Get_Default_Iby_Bank_Acct_Id';
203 --
204 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
205 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
206 ||' X_Party_Id: '|| X_Party_Id
207 ||', X_Party_Site_Id: '||X_Party_Site_Id
208 ||', X_Org_Id: '||X_Org_Id
209 ||', X_Payment_Function: '||X_Payment_Function
210 ||', X_Currency_Code: '||X_Currency_Code);
211 END IF;
212
213 l_debug_info := 'Populating IBY_Txn_Attriibuytes_Rec';
214
215 Iby_Trxn_Attributes_Rec.Application_Id := 200;
216 Iby_Trxn_Attributes_Rec.Payer_Org_Id := X_org_id;
217 Iby_Trxn_Attributes_Rec.Payer_Org_Type := 'OPERATING_UNIT';
218 Iby_Trxn_Attributes_Rec.Payment_Currency := X_Currency_Code;
219 Iby_Trxn_Attributes_Rec.Payee_Party_Id := X_Party_Id;
220 Iby_Trxn_Attributes_Rec.Payee_Party_Site_Id := X_Party_Site_Id;
221 Iby_Trxn_Attributes_Rec.Payment_Function := X_Payment_Function;
222 --
223 l_debug_info := 'Calling IBY API';
224
225 IBY_DISBURSEMENT_COMP_PUB.Get_Default_Payee_Bank_Acc
226 (p_api_version => 1.0,
227 p_init_msg_list => FND_API.G_TRUE,
228 p_trxn_attributes_rec => Iby_Trxn_Attributes_Rec,
229 x_return_status => l_return_status,
230 x_msg_count => l_msg_count,
231 x_msg_data => l_msg_data,
232 x_payee_bankaccount => Iby_Payee_BankAccount_Rec);
233
234 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
235 l_debug_info := 'Sucessfull IBY API Call ';
236 --
237 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
238 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
239 END IF;
240
241 l_default_bank_acct_id := Iby_Payee_BankAccount_Rec.Payee_BankAccount_Id;
242
243 ELSE
244
245 Raise IBY_API_ERROR;
246
247 END IF;
248
249 RETURN(l_default_bank_acct_id);
250
251 EXCEPTION
252 --
253 WHEN IBY_API_ERROR THEN
254 RETURN (NULL);
255 IF (NVL(l_msg_count, 0) > 1) THEN
256 FOR I IN 1..l_msg_count
257 LOOP
258 l_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
259 p_encoded => 'T');
260 FND_MESSAGE.Set_Encoded(l_error_msg);
261 END LOOP;
262 END IF;
263
264 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
265 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
266 FND_MESSAGE.SET_TOKEN('MSG_COUNT', l_msg_count );
267 FND_MESSAGE.SET_TOKEN('MSG_DATA', l_debug_info );
268 FND_MESSAGE.SET_TOKEN('PARAMETERS','X_Party_Id: '||X_Party_Id
269 ||',X_Party_Site_id: '||X_Party_site_id);
270 --
271 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
272 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'IBY_API_ERROR');
273 END IF;
274 --
275 WHEN OTHERS THEN
276 IF (SQLCODE <> -20001 ) THEN
277 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
278 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM );
279 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
280 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
281 FND_MESSAGE.SET_TOKEN('PARAMETERS','X_Party_Id: '||X_Party_Id
282 ||',X_party_Site_id: '||X_party_site_id);
283 END IF;
284 --
285 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
286 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,SQLERRM);
287 END IF;
288
289 END Get_Default_Iby_Bank_Acct_Id;
290
291 END AP_IBY_UTILITY_PKG;