1 PACKAGE BODY IBY_FNDCPT_COMMON_PUB AS
2 /*$Header: ibyfccmb.pls 120.16.12010000.1 2008/07/28 05:40:31 appldev ship $*/
3
4
5 FUNCTION Validate_Payer
6 (
7 p_payer IN PayerContext_rec_type,
8 p_val_level IN VARCHAR2
9 )
10 RETURN VARCHAR2
11 IS
12 l_count NUMBER;
13
14 CURSOR c_cust_acct
15 (ci_party_id IN hz_parties.party_id%TYPE,
16 ci_cust_acct_id IN hz_cust_accounts.cust_account_id%TYPE
17 )
18 IS
19 SELECT COUNT(cust_account_id)
20 FROM hz_cust_accounts
21 WHERE (cust_account_id = ci_cust_acct_id)
22 AND ( (party_id = ci_party_id)
23 OR ci_party_id IN
24 ( SELECT DISTINCT a.party_id
25 FROM hz_cust_account_roles a,
26 hz_cust_accounts b, hz_party_preferences c
27 WHERE (b.cust_account_id = ci_cust_acct_id)
28 AND (NVL(a.status,'A')= 'A')
29 AND (NVL(b.status, 'A') = 'A')
30 AND (a.cust_account_id = b.cust_account_id)
31 AND (a.party_id = c.party_id(+))
32 AND (c.category(+) ='PRIMARY_ACCOUNT')
33 AND (c.preference_code(+) = 'CUSTOMER_ACCOUNT_ID')
34 )
35 );
36
37 CURSOR c_site_use
38 (ci_party_id IN hz_parties.party_id%TYPE,
39 ci_cust_acct_id IN hz_cust_accounts.cust_account_id%TYPE,
40 ci_cust_site_id IN hz_cust_site_uses_all.site_use_id%TYPE
41 )
42 IS
43 SELECT COUNT(site_use_id)
44 FROM hz_cust_site_uses_all u, hz_cust_acct_sites_all s,
45 hz_cust_accounts a
46 WHERE (u.site_use_id = ci_cust_site_id)
47 AND (a.cust_account_id = ci_cust_acct_id)
48 AND (u.cust_acct_site_id = s.cust_acct_site_id)
49 AND (s.cust_account_id = a.cust_account_id);
50
51 BEGIN
52
53 IF (c_cust_acct%ISOPEN) THEN CLOSE c_cust_acct; END IF;
54 IF (c_site_use%ISOPEN) THEN CLOSE c_site_use; END IF;
55
56 -- party id and payment function always mandatory
57 IF ( (p_payer.Party_Id IS NULL) OR
58 (NOT iby_utility_pvt.check_lookup_val(p_payer.Payment_Function,
59 G_LKUP_PMT_FUNCTION))
60 )
61 THEN
62 RETURN G_RC_INVALID_PAYER;
63 END IF;
64
65 IF (p_val_level = FND_API.G_VALID_LEVEL_FULL) THEN
66 IF (NOT iby_utility_pvt.validate_party_id(p_payer.Party_Id)) THEN
67 RETURN G_RC_INVALID_PAYER;
68 END IF;
69 END IF;
70
71 IF (NOT p_payer.Cust_Account_Id IS NULL) THEN
72 IF (p_val_level = FND_API.G_VALID_LEVEL_FULL) THEN
73 OPEN c_cust_acct(p_payer.Party_Id,p_payer.Cust_Account_Id);
74 FETCH c_cust_acct INTO l_count;
75 CLOSE c_cust_acct;
76 IF (l_count<1) THEN RETURN G_RC_INVALID_PAYER; END IF;
77 END IF;
78 ELSE
79 IF (p_payer.Account_Site_Id IS NULL) AND (p_payer.Org_Id IS NULL)
80 AND (p_payer.Org_Type IS NULL)
81 THEN
82 RETURN G_PAYER_LEVEL_PARTY;
83 ELSE
84 RETURN G_RC_INVALID_PAYER;
85 END IF;
86 END IF;
87
88 IF (NOT p_payer.Account_Site_Id IS NULL) THEN
89 -- customer account id is required if account site id is used
90 IF (p_payer.Cust_Account_Id IS NULL) THEN RETURN G_RC_INVALID_PAYER; END IF;
91
92 IF (p_val_level = FND_API.G_VALID_LEVEL_FULL) THEN
93 OPEN c_site_use(p_payer.Party_Id,p_payer.Cust_Account_Id,
94 p_payer.Account_Site_Id);
95 FETCH c_site_use INTO l_count;
96 CLOSE c_site_use;
97 IF (l_count<1) THEN RETURN G_RC_INVALID_PAYER; END IF;
98 END IF;
99
100 -- if account site id is set then payer must be org-striped
101 IF ((p_payer.Org_Type IS NULL) OR (p_payer.Org_Id IS NULL)) THEN
102 RETURN G_RC_INVALID_PAYER;
103 ELSE
104 RETURN G_PAYER_LEVEL_CUSTOMER_SITE;
105 END IF;
106 ELSE
107 IF (p_payer.Org_Id IS NULL) AND (p_payer.Org_Type IS NULL) THEN
108 RETURN G_PAYER_LEVEL_CUSTOMER_ACCT;
109 ELSE
110 RETURN G_RC_INVALID_PAYER;
111 END IF;
112 END IF;
113
114 END Validate_Payer;
115
116 FUNCTION Compare_Payer
117 (
118 p_payer_org_type IN iby_external_payers_all.org_type%TYPE,
119 p_payer_org_id IN iby_external_payers_all.org_id%TYPE,
120 p_payer_cust_acct_id IN iby_external_payers_all.cust_account_id%TYPE,
121 p_payer_acct_site_id IN iby_external_payers_all.acct_site_use_id%TYPE,
122 p_payer_level IN VARCHAR2,
123 p_equiv_type IN VARCHAR2,
124 p_compare_org_type IN iby_external_payers_all.org_type%TYPE,
125 p_compare_org_id IN iby_external_payers_all.org_id%TYPE,
126 p_compare_cust_acct_id IN iby_external_payers_all.cust_account_id%TYPE,
127 p_compare_acct_site_id IN iby_external_payers_all.acct_site_use_id%TYPE
128 )
129 RETURN VARCHAR2
130 IS
131 BEGIN
132 --
133 -- party id and payment function assumed to already match before
134 -- call to function
135 --
136 IF (p_payer_level = G_PAYER_LEVEL_PARTY) THEN
137 IF (p_equiv_type = G_PAYER_EQUIV_FULL) OR
138 (p_equiv_type = G_PAYER_EQUIV_DOWNWARD)
139 THEN
140 RETURN FND_API.G_TRUE;
141 ELSE
142 IF (p_compare_org_type IS NULL) AND (p_compare_org_id IS NULL)
143 AND (p_compare_cust_acct_id IS NULL)
144 AND (p_compare_acct_site_id IS NULL)
145 THEN
146 RETURN FND_API.G_TRUE;
147 END IF;
148 END IF;
149 ELSIF (p_payer_level = G_PAYER_LEVEL_CUSTOMER_ACCT) THEN
150 IF (p_equiv_type = G_PAYER_EQUIV_FULL) OR
151 (p_equiv_type = G_PAYER_EQUIV_DOWNWARD)
152 THEN
153 IF (p_payer_cust_acct_id = p_compare_cust_acct_id) THEN
154 RETURN FND_API.G_TRUE;
155 END IF;
156 END IF;
157 IF (p_equiv_type = G_PAYER_EQUIV_FULL) OR
158 (p_equiv_type = G_PAYER_EQUIV_UPWARD) THEN
159 IF (NVL(p_compare_cust_acct_id,p_payer_cust_acct_id) = p_payer_cust_acct_id)
160 AND (p_compare_acct_site_id IS NULL)
161 THEN RETURN FND_API.G_TRUE; END IF;
162 END IF;
163 IF (p_equiv_type = G_PAYER_EQUIV_IMMEDIATE) THEN
164 IF (p_compare_cust_acct_id = p_compare_cust_acct_id)
165 AND (p_compare_acct_site_id IS NULL)
166 THEN RETURN FND_API.G_TRUE; END IF;
167 END IF;
168 ELSIF (p_payer_level = G_PAYER_LEVEL_CUSTOMER_SITE) THEN
169 IF (p_equiv_type = G_PAYER_EQUIV_FULL) OR
170 (p_equiv_type = G_PAYER_EQUIV_UPWARD)
171 THEN
175 AND (p_payer_acct_site_id = NVL(p_compare_acct_site_id,p_payer_acct_site_id))
172 IF (p_payer_org_type = NVL(p_compare_org_type,p_payer_org_type))
173 AND (p_payer_org_id = NVL(p_compare_org_id,p_payer_org_id))
174 AND (p_payer_cust_acct_id = NVL(p_compare_cust_acct_id,p_payer_cust_acct_id))
176 THEN
177 RETURN FND_API.G_TRUE;
178 END IF;
179 END IF;
180 IF (p_equiv_type = G_PAYER_EQUIV_FULL) OR
181 (p_equiv_type = G_PAYER_EQUIV_DOWNWARD) OR
182 (p_equiv_type = G_PAYER_EQUIV_IMMEDIATE)
183 THEN
184
185 IF (p_payer_org_type = p_compare_org_type)
186 AND (p_payer_org_id = p_compare_org_id)
187 AND (p_payer_cust_acct_id = p_compare_cust_acct_id)
188 AND (p_payer_acct_site_id = p_compare_acct_site_id)
189 THEN
190 RETURN FND_API.G_TRUE;
191 END IF;
192 END IF;
193 END IF;
194
195 RETURN FND_API.G_FALSE;
196 END Compare_Payer;
197
198 PROCEDURE Prepare_Result
199 (
200 p_interface_code IN VARCHAR2,
201 p_existing_msg IN VARCHAR2,
202 p_prev_msg_count IN NUMBER,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2,
206 x_result IN OUT NOCOPY Result_rec_type
207 )
208 IS
209 l_msg_name iby_result_codes.message_name%TYPE;
210 l_category iby_result_codes.result_category%TYPE;
211 l_msg_stack_size NUMBER;
212
213 l_module VARCHAR2(30) := 'Prepare_Result(7 ARG)';
214 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
215
216 CURSOR c_result
217 (ci_result_code IN iby_result_codes.result_code%TYPE)
218 IS
219 SELECT result_category, message_name
220 FROM iby_result_codes
221 WHERE (result_code = ci_result_code)
222 AND (request_interface_code = 'FNDCPT_PUB');
223 BEGIN
224 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
225
226 IF (c_result%ISOPEN) THEN CLOSE c_result; END IF;
227
228 -- map to an equivalent generic result based upon result category
229 IF (p_interface_code <> G_INTERFACE_CODE) THEN
230 l_category := Get_Result_Category(x_result.Result_Code,p_interface_code);
231 IF (l_category = G_RCAT_CONFIG_ERR ) THEN
232 x_result.Result_Code := G_RC_GENERIC_CONFIG_ERROR;
233 ELSIF (l_category = G_RCAT_SYS_ERROR ) THEN
234 x_result.Result_Code := G_RC_GENERIC_SYS_ERROR;
235 ELSIF (l_category = G_RCAT_INV_PARAM ) THEN
236 x_result.Result_Code := G_RC_GENERIC_INVALID_PARAM;
237 ELSIF (l_category = G_RCAT_DATA_CORRUPT ) THEN
238 x_result.Result_Code := G_RC_GENERIC_DATA_CORRUPTION;
239 ELSIF (l_category = G_RCAT_SUCCESS ) THEN
240 x_result.Result_Code := G_RC_SUCCESS;
241 END IF;
242 END IF;
243
244 OPEN c_result(x_result.Result_Code);
245 FETCH c_result INTO x_result.Result_Category, l_msg_name;
246 CLOSE c_result;
247
248 -- put the existing msg on the stack instead of the generic
249 -- one for the result code
250 IF (NOT p_existing_msg IS NULL) THEN
251 l_msg_name := 'IBY_9999';
252 END IF;
253
254 IF (x_result.Result_Category = G_RCAT_SUCCESS) OR
255 (x_result.Result_Category = G_RCAT_SUCCESS_RISK) OR
256 (x_result.Result_Category = G_RCAT_PENDING)
257 THEN
258 x_return_status := FND_API.G_RET_STS_SUCCESS;
259 ELSE
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 END IF;
262
263 -- remove extra messages from the stack added
264 -- by calls to APIs within other APIs; do not
265 -- remove messages already on the stack at the
266 -- time of invocation, though
267 l_msg_stack_size := FND_MSG_PUB.Count_Msg;
268 WHILE (l_msg_stack_size > p_prev_msg_count) LOOP
269 FND_MSG_PUB.Delete_Msg(p_prev_msg_count+1);
270 l_msg_stack_size := l_msg_stack_size - 1;
271 END LOOP;
272
273 IF (p_existing_msg IS NULL) THEN
274 FND_MESSAGE.SET_NAME('IBY',l_msg_name);
278 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT', p_existing_msg);
275 x_result.Result_Message := FND_MESSAGE.GET();
276 ELSE
277 FND_MESSAGE.SET_NAME('IBY','IBY_9999');
279 x_result.Result_Message := FND_MESSAGE.GET();
280 END IF;
281
282 -- IBY_9999 means use the message returned by the engine
283 -- or sub-moudule
284 FND_MESSAGE.SET_NAME('IBY',l_msg_name);
285 IF (l_msg_name = 'IBY_9999') THEN
286 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT',x_result.Result_Message);
287 END IF;
288 FND_MSG_PUB.Add;
289 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
290 p_data => x_msg_data
291 );
292
293 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
294 END Prepare_Result;
295
296 PROCEDURE Prepare_Result
297 (
298 p_prev_msg_count IN NUMBER,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2,
302 x_result IN OUT NOCOPY Result_rec_type
303 )
304 IS
305 l_module VARCHAR2(30) := 'Prepare_Result(5 ARG)';
306 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
307 BEGIN
308 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
309
310 Prepare_Result
311 (G_INTERFACE_CODE,NULL,p_prev_msg_count,x_return_status,x_msg_count,
312 x_msg_data,x_result);
313
314 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
315 END Prepare_Result;
316
317 FUNCTION Get_Result_Category
318 (p_result IN iby_result_codes.result_code%TYPE,
319 p_interface IN iby_result_codes.request_interface_code%TYPE)
320 RETURN iby_result_codes.result_category%TYPE
321 IS
322 l_category iby_result_codes.result_category%TYPE;
323
324 CURSOR c_category
325 (ci_result IN iby_result_codes.result_code%TYPE,
326 ci_interface IN iby_result_codes.request_interface_code%TYPE)
327 IS
328 SELECT result_category
329 FROM iby_result_codes
330 WHERE (result_code = ci_result)
331 AND (request_interface_code = ci_interface);
332 BEGIN
333 IF (c_category%ISOPEN) THEN CLOSE c_category; END IF;
334
335 OPEN c_category(p_result,p_interface);
336 FETCH c_category INTO l_category;
337 CLOSE c_category;
338
339 RETURN l_category;
340 END Get_Result_Category;
341
345 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Clear_Msg_Stack';
342 PROCEDURE Clear_Msg_Stack( p_prev_msg_count IN NUMBER )
343 IS
344 l_msg_stack_size NUMBER;
346 BEGIN
347 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
348
349 l_msg_stack_size := FND_MSG_PUB.Count_Msg;
350 WHILE (l_msg_stack_size > p_prev_msg_count) LOOP
351 FND_MSG_PUB.Delete_Msg(p_prev_msg_count+1);
352 l_msg_stack_size := l_msg_stack_size - 1;
353 END LOOP;
354 END Clear_Msg_Stack;
355
356 END IBY_FNDCPT_COMMON_PUB;