1 PACKAGE BODY igs_fi_prc_apint AS
2 /* $Header: IGSFI78B.pls 120.4 2006/06/27 14:21:57 skharida noship $ */
3 /****************************************************************
4 || Created By : [email protected]
5 || Created On : 18-FEB-2003
6 || Purpose :
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 || (reverse chronological order - newest change first)
11 || skharida 26-Jun-2006 Bug# 5208136 -Modified update_refund_rec, removed the obsoleted columns
12 || from igs_fi_refunds_pkg.update_row.
13 || abshriva 12-MAY-2006 Bug 5217319 Amount precision change in create_ap_int_rec
14 || agairola 20-Feb-2006 Bug 5046245: Commenting out of the Vendor API
15 || shtatiko 30-APR-2003 Enh# 2831569, Modified validate_parameters
16 *****************************************************************/
17
18 g_v_other CONSTANT VARCHAR2(10) := 'OTHER';
19 g_v_item CONSTANT VARCHAR2(10) := 'ITEM';
20 g_v_vendor_type CONSTANT VARCHAR2(20) := 'VENDOR TYPE';
21 g_v_pay_group CONSTANT VARCHAR2(20) := 'PAY GROUP';
22 g_v_stdnt_system CONSTANT VARCHAR2(30) := 'STUDENT SYSTEM';
23 g_v_automatic CONSTANT VARCHAR2(10) := 'AUTOMATIC';
24 g_v_credit CONSTANT VARCHAR2(10) := 'CREDIT';
25 g_v_standard CONSTANT VARCHAR2(10) := 'STANDARD';
26 g_v_todo CONSTANT VARCHAR2(30) := 'TODO';
27 g_v_offset CONSTANT VARCHAR2(30) := 'OFFSET';
28 g_v_transferred CONSTANT VARCHAR2(30) := 'TRANSFERRED';
29 g_v_ind_a CONSTANT VARCHAR2(5) := 'A';
30 g_v_pay_to CONSTANT VARCHAR2(10) := 'PAY_TO';
31
32 g_v_lbl_error igs_lookup_values.meaning%TYPE;
33 g_v_lbl_person_group igs_lookup_values.meaning%TYPE;
34 g_v_lbl_create_suppl igs_lookup_values.meaning%TYPE;
35 g_v_lbl_suppl_type igs_lookup_values.meaning%TYPE;
36 g_v_lbl_pay_grp igs_lookup_values.meaning%TYPE;
37 g_v_lbl_inv_term igs_lookup_values.meaning%TYPE;
38 g_v_lbl_test_run igs_lookup_values.meaning%TYPE;
39 g_v_lbl_party igs_lookup_values.meaning%TYPE;
40 g_v_lbl_pay igs_lookup_values.meaning%TYPE;
41 g_v_lbl_vchr igs_lookup_values.meaning%TYPE;
42 g_v_lbl_status igs_lookup_values.meaning%TYPE;
43 g_v_lbl_todo igs_lookup_values.meaning%TYPE;
44 g_v_lbl_offset igs_lookup_values.meaning%TYPE;
45 g_v_lbl_transferred igs_lookup_values.meaning%TYPE;
46 g_n_org_id igs_fi_control.ap_org_id%TYPE;
47 g_v_cur_code igs_fi_control.currency_cd%TYPE;
48 g_v_sup_num po_vendors.segment1%TYPE;
49 g_v_sup_name po_vendors.vendor_name%TYPE;
50 g_v_pay_rfnd_vchr igs_lookup_values.lookup_code%TYPE;
51 g_v_dflt_sup_site igs_fi_control.dflt_supplier_site_name%TYPE;
52 g_v_supplier_type po_vendors.vendor_type_lookup_code%TYPE;
53 g_v_ven_num_code financials_system_params_all.user_defined_vendor_num_code%TYPE;
54 g_v_create_supplier VARCHAR2(1);
55 g_b_data_found BOOLEAN;
56
57 e_resource_busy EXCEPTION;
58 PRAGMA exception_init(e_resource_busy,-0054);
59
60 CURSOR c_refunds IS
61 SELECT rfnd.rowid, rfnd.*
62 FROM igs_fi_refunds rfnd;
63
64 TYPE r_party_rel_rec IS RECORD (p_n_party_id hz_parties.party_id%TYPE,
65 p_n_vendor_id igs_fi_party_vendrs.vendor_id%TYPE,
66 p_n_vendor_site_id igs_fi_party_vendrs.vendor_site_id%TYPE);
67
68 TYPE t_party_rel IS TABLE OF r_party_rel_rec INDEX BY BINARY_INTEGER;
69
70 t_party_vendors t_party_rel;
71 t_party_dummy t_party_rel;
72
73 PROCEDURE log_transaction(p_n_party_id PLS_INTEGER,
74 p_n_payee_id PLS_INTEGER,
75 p_n_refund_id PLS_INTEGER) AS
76 /********************************************************
77 || Created By : [email protected]
78 || Created On : 18-FEB-2003
79 || Purpose : Procedure for logging the transaction data
80 || Known limitations, enhancements or remarks :
81 || Change History :
82 || Who When What
83 || (reverse chronological order - newest change first)
84 *********************************************************/
85 CURSOR c_party(cp_n_party_id hz_parties.party_id%TYPE) IS
86 SELECT party_number
87 FROM hz_parties
88 WHERE party_id = cp_n_party_id;
89
90 l_n_party_num hz_parties.party_number%TYPE;
91
92 l_v_status igs_lookup_values.meaning%TYPE;
93
94 BEGIN
95 l_n_party_num := NULL;
96 OPEN c_party(p_n_party_id);
97 FETCH c_party INTO l_n_party_num;
98 CLOSE c_party;
99
100 fnd_file.new_line(fnd_file.log);
101
102 fnd_file.put_line(fnd_file.log,
103 g_v_lbl_party||' : '||l_n_party_num);
104
105 l_n_party_num := NULL;
106 OPEN c_party(p_n_payee_id);
107 FETCH c_party INTO l_n_party_num;
108 CLOSE c_party;
109
110 fnd_file.put_line(fnd_file.log,
111 g_v_lbl_pay||' : '||l_n_party_num);
112 fnd_file.put_line(fnd_file.log,
113 g_v_lbl_vchr||' : '||p_n_refund_id);
114
115 END log_transaction;
116
117 PROCEDURE update_refund_rec(p_r_rfnd_rec c_refunds%ROWTYPE) AS
118 /****************************************************************
119 || Created By : [email protected]
120 || Created On : 18-FEB-2003
121 || Purpose : Updates the Refund transaction status
122 || Known limitations, enhancements or remarks :
123 || Change History :
124 || Who When What
125 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_REFUNDS
126 || (reverse chronological order - newest change first)
127 *****************************************************************/
128 BEGIN
129 igs_fi_refunds_pkg.update_row(x_rowid => p_r_rfnd_rec.rowid,
130 x_refund_id => p_r_rfnd_rec.refund_id,
131 x_voucher_date => p_r_rfnd_rec.voucher_date,
132 x_person_id => p_r_rfnd_rec.person_id,
133 x_pay_person_id => p_r_rfnd_rec.pay_person_id,
134 x_dr_gl_ccid => p_r_rfnd_rec.dr_gl_ccid,
135 x_cr_gl_ccid => p_r_rfnd_rec.cr_gl_ccid,
136 x_dr_account_cd => p_r_rfnd_rec.dr_account_cd,
137 x_cr_account_cd => p_r_rfnd_rec.cr_account_cd,
138 x_refund_amount => p_r_rfnd_rec.refund_amount,
139 x_fee_type => p_r_rfnd_rec.fee_type,
140 x_fee_cal_type => p_r_rfnd_rec.fee_cal_type,
141 x_fee_ci_sequence_number => p_r_rfnd_rec.fee_ci_sequence_number,
142 x_source_refund_id => p_r_rfnd_rec.source_refund_id,
143 x_invoice_id => p_r_rfnd_rec.invoice_id,
144 x_transfer_status => p_r_rfnd_rec.transfer_status,
145 x_reversal_ind => p_r_rfnd_rec.reversal_ind,
146 x_reason => p_r_rfnd_rec.reason,
147 x_attribute_category => p_r_rfnd_rec.attribute_category,
148 x_attribute1 => p_r_rfnd_rec.attribute1,
149 x_attribute2 => p_r_rfnd_rec.attribute2,
150 x_attribute3 => p_r_rfnd_rec.attribute3,
151 x_attribute4 => p_r_rfnd_rec.attribute4,
152 x_attribute5 => p_r_rfnd_rec.attribute5,
153 x_attribute6 => p_r_rfnd_rec.attribute6,
154 x_attribute7 => p_r_rfnd_rec.attribute7,
155 x_attribute8 => p_r_rfnd_rec.attribute8,
156 x_attribute9 => p_r_rfnd_rec.attribute9,
157 x_attribute10 => p_r_rfnd_rec.attribute10,
158 x_attribute11 => p_r_rfnd_rec.attribute11,
159 x_attribute12 => p_r_rfnd_rec.attribute12,
160 x_attribute13 => p_r_rfnd_rec.attribute13,
161 x_attribute14 => p_r_rfnd_rec.attribute14,
162 x_attribute15 => p_r_rfnd_rec.attribute15,
163 x_attribute16 => p_r_rfnd_rec.attribute16,
164 x_attribute17 => p_r_rfnd_rec.attribute17,
165 x_attribute18 => p_r_rfnd_rec.attribute18,
166 x_attribute19 => p_r_rfnd_rec.attribute19,
167 x_attribute20 => p_r_rfnd_rec.attribute20,
168 x_gl_date => p_r_rfnd_rec.gl_date,
169 x_reversal_gl_date => p_r_rfnd_rec.reversal_gl_date);
170 END update_refund_rec;
171
172 PROCEDURE initialize AS
173 /******************************************************************
174 || Created By : [email protected]
175 || Created On : 18-FEB-2003
176 || Purpose : Procedure for initializing the global variables
177 || Known limitations, enhancements or remarks :
178 || Change History :
179 || Who When What
180 || agairola 11-Mar-2003 Bug 2838757: Initialized g_b_data_found
181 || to false
182 || (reverse chronological order - newest change first)
183 ******************************************************************/
184 CURSOR c_lkp(cp_lookup_type ap_lookup_codes.lookup_type%TYPE,
185 cp_lookup_code ap_lookup_codes.lookup_code%TYPE) IS
186 SELECT displayed_field
187 FROM ap_lookup_codes
188 WHERE lookup_type = cp_lookup_type
189 AND lookup_code = cp_lookup_code;
190 BEGIN
191
192 -- Initialize all the constant lables/translatable text for the process
193 g_v_lbl_party := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
194 'PARTY');
195 g_v_lbl_person_group := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
196 'PERSON_GROUP');
197 g_v_lbl_create_suppl := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
198 'CREATE_SUPPLIER');
199 g_v_lbl_suppl_type := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
200 'SUPPLIER_TYPE');
201 g_v_lbl_pay_grp := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
202 'INV_PAY_GROUP');
203 g_v_lbl_inv_term := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
204 'INV_PAY_TERM');
205 g_v_lbl_test_run := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
206 'TEST_RUN');
207 g_v_lbl_pay := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
208 'PAYEE_PARTY_NUMBER');
209 g_v_lbl_vchr := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
210 'REFUND_ID');
211 g_v_lbl_status := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
212 'STATUS');
213 g_v_lbl_todo := igs_fi_gen_gl.get_lkp_meaning('REFUND_TRANSFER_STATUS',
214 g_v_todo);
215 g_v_lbl_offset := igs_fi_gen_gl.get_lkp_meaning('REFUND_TRANSFER_STATUS',
216 g_v_offset);
217 g_v_lbl_transferred := igs_fi_gen_gl.get_lkp_meaning('REFUND_TRANSFER_STATUS',
218 g_v_transferred);
219
220 g_v_lbl_error := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','ERROR');
221
222 g_b_data_found := FALSE;
223
224 t_party_vendors := t_party_dummy;
225 END initialize;
226
227 PROCEDURE get_payto_add(p_n_party_id PLS_INTEGER,
228 p_v_party_number VARCHAR2,
229 p_n_location_id OUT NOCOPY PLS_INTEGER,
230 p_b_status OUT NOCOPY BOOLEAN) AS
231 /******************************************************************
232 || Created By : [email protected]
233 || Created On : 18-FEB-2003
234 || Purpose : Procedure for determining the pay to address
235 || Known limitations, enhancements or remarks :
236 || Change History :
237 || Who When What
238 || gmaheswa 19-nov-2003 Bug 3227107 address changes. Modified cursor c_hz_site to select dates
239 || from igs_pe_hz_pty_sites instead of hz_party_sites.
240 || (reverse chronological order - newest change first)
241 ******************************************************************/
242
243 -- Cursor for selecting an Active Pay to Usage address for a party
244 CURSOR c_hz_site(cp_n_party_id hz_parties.party_id%TYPE) IS
245 SELECT ps.location_id
246 FROM hz_party_sites ps,
247 hz_party_site_uses psu,
248 igs_pe_hz_pty_sites ips
249 WHERE ps.party_site_id = ips.party_site_id(+)
250 AND ps.party_id = cp_n_party_id
251 AND (ps.status = g_v_ind_a AND
252 (SYSDATE BETWEEN NVL(ips.start_date,SYSDATE) AND NVL(ips.end_date,SYSDATE)) )
253 AND psu.party_site_id = ps.party_site_id
254 AND psu.site_use_type = g_v_pay_to
255 AND psu.status = g_v_ind_a;
256
257 l_n_hz_cntr NUMBER := 0;
258 BEGIN
259 l_n_hz_cntr := 0;
260
261 -- Loop across all the active Pay To usage address for the
262 -- party
263 FOR l_c_hz_site IN c_hz_site(p_n_party_id) LOOP
264 p_n_location_id := l_c_hz_site.location_id;
265 l_n_hz_cntr := l_n_hz_cntr + 1;
266 END LOOP;
267
268 -- If there are no active pay to usage addresses for the
269 -- party, then a supplier site cannot be created and hence is an error
270 -- condition
271 IF l_n_hz_cntr = 0 THEN
272 fnd_file.put_line(fnd_file.log,
273 g_v_lbl_status||' : '||g_v_lbl_todo);
274 fnd_message.set_name('IGS','IGS_FI_HZ_NO_PAY_ADD');
275 fnd_message.set_token('PAYEE_NUM',p_v_party_number);
276 fnd_file.put_line(fnd_file.log,
277 fnd_message.get);
278 p_b_status := FALSE;
279 RETURN;
280 END IF;
281
282 -- If there are more than one active pay to usage addresses for the
283 -- party, then a supplier site cannot be created and hence is an error
284 -- condition
285 IF l_n_hz_cntr > 1 THEN
286 fnd_file.put_line(fnd_file.log,
287 g_v_lbl_status||' : '||g_v_lbl_todo);
288 fnd_message.set_name('IGS','IGS_FI_HZ_UNQ_PAY_ADD');
289 fnd_message.set_token('PAYEE_NUM',p_v_party_number);
290 fnd_file.put_line(fnd_file.log,
291 fnd_message.get);
292 p_b_status := FALSE;
293 RETURN;
294 END IF;
295
296 -- Return True
297 p_b_status := TRUE;
298 END get_payto_add;
299
300 FUNCTION validate_parameters(p_n_party_id IN NUMBER,
301 p_n_person_group_id IN NUMBER,
302 p_v_create_supplier IN VARCHAR2,
303 p_v_supplier_type IN VARCHAR2,
304 p_v_inv_pay_group IN VARCHAR2,
305 p_n_inv_pay_term IN NUMBER,
306 p_v_test_run IN VARCHAR2) RETURN BOOLEAN AS
307 /******************************************************************
308 || Created By : [email protected]
309 || Created On : 18-FEB-2003
310 || Purpose : Procedure for validating the input parameters are the conditions for the process to
311 || run
312 || Known limitations, enhancements or remarks :
313 || Change History :
314 || Who When What
315 || (reverse chronological order - newest change first)
316 || shtatiko 30-APR-2003 Enh# 2831569, Added check for Manage Accounts System Option.
317 || If its value is NULL or OTHER then log the message and return
318 || back without executing other validations.
319 ******************************************************************/
320
321 -- Cursor for fetching the Org Id and the default
322 -- supplier site name
323 CURSOR c_ctrl IS
324 SELECT ap_org_id, dflt_supplier_site_name
325 FROM igs_fi_control;
326
327 -- Cursor for selecting the party number from hz_parties
328 -- for a party id
329 CURSOR c_party(cp_n_party_id hz_parties.party_id%TYPE) IS
330 SELECT party_number
331 FROM hz_parties
332 WHERE party_id = cp_n_party_id;
333
334 l_c_party c_party%ROWTYPE;
335
336 -- Cursor for validating the Person Group
337 CURSOR c_pers_id_grp(cp_n_pers_grp_id igs_pe_all_persid_group_v.group_id%TYPE) IS
338 SELECT group_cd, closed_ind
339 FROM igs_pe_all_persid_group_v
340 WHERE group_id = cp_n_pers_grp_id;
341
342 l_c_pers_id_grp c_pers_id_grp%ROWTYPE;
343
344 -- Cursor for validating a valid lookup code for a lookup type in PO Lookups
345 CURSOR c_po_lkp(cp_v_lookup_type igs_lookup_values.lookup_type%TYPE,
346 cp_v_lookup_code igs_lookup_values.lookup_code%TYPE) IS
347 SELECT displayed_field meaning, inactive_date
348 FROM po_lookup_codes
349 WHERE lookup_type = cp_v_lookup_type
350 AND lookup_code = cp_v_lookup_code;
351
352 -- Cursor foe selecting the User Defined Vendor Numbering Code
353 -- from Financials System Parameters all
354 CURSOR c_fsp IS
355 SELECT user_defined_vendor_num_code
356 FROM financials_system_params_all
357 WHERE ((org_id = g_n_org_id) OR (org_id IS NULL AND g_n_org_id IS NULL));
358
359 l_c_sup_type c_po_lkp%ROWTYPE;
360 l_c_grp_type c_po_lkp%ROWTYPE;
361
362 -- Cursor for selecting the data from AP terms for the input parameter
363 CURSOR c_ap_term(cp_n_term_id ap_terms.term_id%TYPE) IS
364 SELECT name,
365 start_date_active,
366 end_date_active
367 FROM ap_terms
368 WHERE term_id = cp_n_term_id;
369
370 -- Cursor for selecting the multi org flag from the FND_PRODUCT_GROUPS
371 CURSOR c_fnd_prod IS
372 SELECT multi_org_flag
373 FROM fnd_product_groups;
374
375 l_v_rfnd_destination igs_fi_control.rfnd_destination%TYPE;
376 l_c_ap_term c_ap_term%ROWTYPE;
377 l_c_fnd_prod c_fnd_prod%ROWTYPE;
378 l_v_curr_desc igs_fi_control_v.name%TYPE;
379 l_v_message_name fnd_new_messages.message_name%TYPE;
380 l_b_term_flag BOOLEAN := TRUE;
381 l_b_party_flag BOOLEAN := TRUE;
382 l_b_pers_grp_flag BOOLEAN := TRUE;
383 l_b_sup_type_flag BOOLEAN := TRUE;
384 l_b_inv_pay_grp_flag BOOLEAN := TRUE;
385 l_b_inv_pay_term_flag BOOLEAN := TRUE;
386 l_b_val_parm BOOLEAN := TRUE;
387 l_v_manage_accounts igs_fi_control_all.manage_accounts%TYPE;
388 l_b_run_process BOOLEAN := TRUE;
389
390 BEGIN
391
392 -- Get the value of "Manage Accounts" System Option value.
393 -- If this value is NULL or OTHER then this process should error out.
394 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
395 p_v_message_name => l_v_message_name );
396 IF l_v_manage_accounts IS NULL OR l_v_manage_accounts = 'OTHER' THEN
397 fnd_message.set_name ( 'IGS', l_v_message_name );
398 -- mark that process shouldn't run anymore.
399 l_b_run_process := FALSE;
400 END IF;
401
402 -- Fetch the details from AP terms for the term id passed as input
403 -- Incase no data is found, then this is an error condition
404 OPEN c_ap_term(p_n_inv_pay_term);
405 FETCH c_ap_term INTO l_c_ap_term;
406 IF c_ap_term%NOTFOUND THEN
407 l_b_term_flag := FALSE;
408 END IF;
409 CLOSE c_ap_term;
410
411 -- validate if the party exists.
412 IF p_n_party_id IS NOT NULL THEN
413 OPEN c_party(p_n_party_id);
414 FETCH c_party INTO l_c_party;
415 IF c_party%NOTFOUND THEN
416 l_b_party_flag := FALSE;
417 END IF;
418 CLOSE c_party;
419 END IF;
420
421 -- validate if the person group if passed is
422 -- a valid person group
423 IF p_n_person_group_id IS NOT NULL THEN
424 OPEN c_pers_id_grp(p_n_person_group_id);
425 FETCH c_pers_id_grp INTO l_c_pers_id_grp;
426 IF c_pers_id_grp%NOTFOUND THEN
427 l_b_pers_grp_flag := FALSE;
428 END IF;
429 CLOSE c_pers_id_grp;
430 END IF;
431
432 -- Validate if the supplier type is a valid
433 -- supplier type
434 IF p_v_supplier_type IS NOT NULL THEN
435 OPEN c_po_lkp(g_v_vendor_type,
436 p_v_supplier_type);
437 FETCH c_po_lkp INTO l_c_sup_type;
438 IF c_po_lkp%NOTFOUND THEN
439 l_b_sup_type_flag := FALSE;
440 END IF;
441 CLOSE c_po_lkp;
442 END IF;
443
444 -- Validate if the Invoice Pay Group is a valid
445 -- Invoice Pay group in AP
446 IF p_v_inv_pay_group IS NOT NULL THEN
447 OPEN c_po_lkp(g_v_pay_group,
448 p_v_inv_pay_group);
449 FETCH c_po_lkp INTO l_c_grp_type;
450 IF c_po_lkp%NOTFOUND THEN
451 l_b_inv_pay_grp_flag := FALSE;
452 END IF;
453 CLOSE c_po_lkp;
454 END IF;
455
456 -- Logging the parameters
457 fnd_file.put_line(fnd_file.log,
458 igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PROCESS_PARM'));
459 fnd_file.put_line(fnd_file.log,
460 RPAD('-',80,'-'));
461 fnd_file.new_line(fnd_file.log);
462
463 fnd_file.put_line(fnd_file.log,
464 g_v_lbl_party||' : '||NVL(l_c_party.party_number, p_n_party_id));
465 fnd_file.put_line(fnd_file.log,
466 g_v_lbl_person_group||' : '||NVL(l_c_pers_id_grp.group_cd, p_n_person_group_id));
467 fnd_file.put_line(fnd_file.log,
468 g_v_lbl_create_suppl||' : '||NVL(igs_fi_gen_gl.get_lkp_meaning('YES_NO', p_v_create_supplier),p_v_create_supplier));
469 fnd_file.put_line(fnd_file.log,
470 g_v_lbl_suppl_type||' : '||NVL(l_c_sup_type.meaning,p_v_supplier_type));
471 fnd_file.put_line(fnd_file.log,
472 g_v_lbl_pay_grp||' : '||NVL(l_c_grp_type.meaning,g_v_lbl_pay_grp));
473 fnd_file.put_line(fnd_file.log,
474 g_v_lbl_inv_term||' : '||NVL(l_c_ap_term.name,p_n_inv_pay_term));
475 fnd_file.put_line(fnd_file.log,
476 g_v_lbl_test_run||' : '||NVL(igs_fi_gen_gl.get_lkp_meaning('YES_NO', p_v_test_run),p_v_test_run));
477
478 fnd_file.new_line(fnd_file.log);
479
480 -- If Manage Accounts validation fails then log the message and return false
481 IF NOT l_b_run_process THEN
482 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
483 RETURN FALSE;
484 END IF;
485
486 -- get the refund destination
487 l_v_rfnd_destination := igs_fi_gen_apint.get_rfnd_destination;
488
489 -- If the Refund Destination is OTHER or is NULL then log the error message
490 IF l_v_rfnd_destination = g_v_other OR l_v_rfnd_destination IS NULL THEN
491 l_b_val_parm := FALSE;
492 fnd_message.set_name('IGS',
493 'IGS_FI_RFND_DST_PAY');
494 fnd_file.put_line(fnd_file.log,
495 fnd_message.get);
496 l_b_val_parm := FALSE;
497 RETURN l_b_val_parm;
498 END IF;
499
500 -- Fetch the Payables Org Id and the default supplier Site Name
501 OPEN c_ctrl;
502 FETCH c_ctrl INTO g_n_org_id, g_v_dflt_sup_site;
503 IF c_ctrl%NOTFOUND THEN
504 l_b_val_parm := FALSE;
505 fnd_message.set_name('IGS',
506 'IGS_FI_SYSTEM_OPT_SETUP');
507 fnd_file.put_line(fnd_file.log,
508 fnd_message.get);
509 END IF;
510 CLOSE c_ctrl;
511
512 -- If the payables operating unit is not set up, then check if the
513 -- application is Multi-Org enabled. If the application is Multi-Org
514 -- enabled and the Payables Operating Unit is not setup, then the
515 -- error should be logged in the log file.
516 IF g_n_org_id IS NULL THEN
517 OPEN c_fnd_prod;
518 FETCH c_fnd_prod INTO l_c_fnd_prod;
519 CLOSE c_fnd_prod;
520
521 IF l_c_fnd_prod.multi_org_flag = 'Y' THEN
522 l_b_val_parm := FALSE;
523 fnd_message.set_name('IGS',
524 'IGS_FI_AP_ORG_ID_NOTSETUP');
525 fnd_file.put_line(fnd_file.log,
526 fnd_message.get);
527 END IF;
528 END IF;
529
530 -- Set the Org Id
531 igs_ge_gen_003.set_org_id(g_n_org_id);
532
533 -- If the create supplier parameter is Null or the test run parameter is null or
534 -- Invoice Payment Term parameter is NULL, then log an error message in the log file
535 IF p_v_create_supplier IS NULL OR p_v_test_run IS NULL OR p_n_inv_pay_term IS NULL THEN
536 l_b_val_parm := FALSE;
537 fnd_message.set_name('IGS',
538 'IGS_FI_PARAMETER_NULL');
539 fnd_file.put_line(fnd_file.log,
540 fnd_message.get);
541 END IF;
542
543 -- If the Supplier Type parameter is not null but the create supplier parameter
544 -- has value as N, then error message should be logged.
545 IF p_v_supplier_type IS NOT NULL and p_v_create_supplier = 'N' THEN
546 l_b_val_parm := FALSE;
547 fnd_message.set_name('IGS',
548 'IGS_FI_INV_SUP_TYPE');
549 fnd_file.put_line(fnd_file.log,
550 fnd_message.get);
551 END IF;
552
553 -- If party validation has failed earlier
554 IF NOT l_b_party_flag THEN
555 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
556 fnd_message.set_token('PARAMETER',g_v_lbl_party);
557 fnd_file.put_line(fnd_file.log, fnd_message.get);
558 END IF;
559
560 -- If the person group validation has failed earlier or the person group
561 -- is closed, then error message is logged
562 IF NOT l_b_pers_grp_flag OR NVL(l_c_pers_id_grp.closed_ind,'N') = 'Y' THEN
563 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
564 fnd_message.set_token('PARAMETER',g_v_lbl_person_group);
565 fnd_file.put_line(fnd_file.log, fnd_message.get);
566 END IF;
567
568 -- If the Supplier Type validation has failed earlier or the inactive date of the
569 -- supplier type is less than System Date, then error message is logged
570 IF (NOT l_b_sup_type_flag) OR NOT (TRUNC(NVL(l_c_sup_type.inactive_date,sysdate)) >= TRUNC(sysdate)) THEN
571 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
572 fnd_message.set_token('PARAMETER',g_v_lbl_suppl_type);
573 fnd_file.put_line(fnd_file.log, fnd_message.get);
574 END IF;
575
576 -- The Invoice Pay group parameter validation has failed earlier or the inactive date of the
577 -- Invoice Pay group is less than the current date, then error message is logged
578 IF (NOT l_b_inv_pay_grp_flag) OR NOT (TRUNC(NVL(l_c_grp_type.inactive_date,sysdate)) >= TRUNC(sysdate)) THEN
579 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
580 fnd_message.set_token('PARAMETER',g_v_lbl_pay_grp);
581 fnd_file.put_line(fnd_file.log, fnd_message.get);
582 END IF;
583
584 -- If the Term Id passed as input parameter does not exist
585 IF NOT l_b_term_flag THEN
586 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
587 fnd_message.set_token('PARAMETER',g_v_lbl_pay_grp);
588 fnd_file.put_line(fnd_file.log, fnd_message.get);
589 END IF;
590
591 -- If the sysdate is not within the Start date active and end date active
592 -- of the Term passed as input, log an error message in the log file
593 IF NOT (TRUNC(sysdate) BETWEEN TRUNC(NVL(l_c_ap_term.start_date_active, sysdate)) AND
594 TRUNC(NVL(l_c_ap_term.end_date_active, sysdate))) THEN
595 l_b_val_parm := FALSE;
596 fnd_message.set_name('IGS','IGS_FI_AP_TERM_INACTIVE');
597 fnd_message.set_token('TERM_NAME',l_c_ap_term.name);
598 fnd_file.put_line(fnd_file.log,
599 fnd_message.get);
600 END IF;
601
602 -- If the create supplier parameter is not in Y/N, then log the
603 -- error message in the log file
604 IF p_v_create_supplier NOT IN ('Y','N') THEN
605 l_b_val_parm := FALSE;
606 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
607 fnd_message.set_token('PARAMETER',g_v_lbl_create_suppl);
608 fnd_file.put_line(fnd_file.log,
609 fnd_message.get);
610 END IF;
611
612 -- If the test run parameter is not in Y/N, then log the
613 -- error message in the log file
614 IF p_v_test_run NOT IN ('Y','N') THEN
615 l_b_val_parm := FALSE;
616 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
617 fnd_message.set_token('PARAMETER',g_v_lbl_test_run);
618 fnd_file.put_line(fnd_file.log,
619 fnd_message.get);
620 END IF;
621
622 -- call the procedure for getting the currency code
623 igs_fi_gen_gl.finp_get_cur(g_v_cur_code,
624 l_v_curr_desc,
625 l_v_message_name);
626
627 -- If the currency code is not null, then log the
628 -- error message in the log file
629 IF g_v_cur_code IS NULL THEN
630 l_b_val_parm := FALSE;
631 fnd_message.set_name('IGS',l_v_message_name);
632 fnd_file.put_line(fnd_file.log,
633 fnd_message.get);
634 END IF;
635
636 -- Get the value of the Supplier Number parameter
637 g_v_sup_num := fnd_profile.value('IGS_FI_SUPPLIER_NUMBER');
638
639 -- Get the value of the User Defined Vendor Numbering Code
640 -- from Financial System params all
641 OPEN c_fsp;
642 FETCH c_fsp INTO g_v_ven_num_code;
643 CLOSE c_fsp;
644
645 -- If the value of the profile for Supplier Number is null, then check
646 -- if the value for the Vendor Number Code in AP is set to other than
647 -- Automatic. If yes, then log error message in the log file.
648 IF g_v_sup_num IS NULL THEN
649 IF NVL(g_v_ven_num_code,g_v_automatic) <> g_v_automatic THEN
650 l_b_val_parm := FALSE;
651 fnd_message.set_name('IGS','IGS_FI_PROFL_SUP_NUM_NOT_SET');
652 fnd_file.put_line(fnd_file.log,
653 fnd_message.get);
654 END IF;
655 END IF;
656
657 -- Get the value of the Supplier Name profile.
658 g_v_sup_name := fnd_profile.value('IGS_FI_SUPPLIER_NAME');
659
660 -- If the profile value for the supplier name is not set up
661 -- then log the error message in the log file.
662 IF g_v_sup_name IS NULL THEN
663 l_b_val_parm := FALSE;
664 fnd_message.set_name('IGS','IGS_FI_PROFL_SUP_NAME_NOT_SET');
665 fnd_file.put_line(fnd_file.log,
666 fnd_message.get);
667 END IF;
668
669 -- Get the value of the profile 'Student Finance Pay Voucher Alone'
670 g_v_pay_rfnd_vchr := fnd_profile.value('IGS_FI_PAY_RFND_VOUCHER');
671
672 -- If the value of the profile is not set up, then log error message
673 -- in the log file
674 IF g_v_pay_rfnd_vchr IS NULL THEN
675 l_b_val_parm := FALSE;
676 fnd_message.set_name('IGS','IGS_FI_PROFL_PAY_RFND_NOT_SET');
677 fnd_file.put_line(fnd_file.log,
678 fnd_message.get);
679 END IF;
680
681 -- If the Party Id and the Person Group both are passed as input to
682 -- the process, then log error message
683 IF p_n_party_id IS NOT NULL AND p_n_person_group_id IS NOT NULL THEN
684 l_b_val_parm := FALSE;
685 fnd_message.set_name('IGS','IGS_FI_NO_PERS_PGRP');
686 fnd_file.put_line(fnd_file.log,
687 fnd_message.get);
688 END IF;
689
690 -- If any of the validations has failed, then RETURN False
691 IF l_b_term_flag AND l_b_party_flag AND l_b_pers_grp_flag AND
692 l_b_sup_type_flag AND l_b_inv_pay_grp_flag AND l_b_inv_pay_term_flag AND
693 l_b_val_parm THEN
694 RETURN TRUE;
695 ELSE
696 RETURN FALSE;
697 END IF;
698
699 END validate_parameters;
700
701 FUNCTION derive_vendor_name(p_v_party_name VARCHAR2,
702 p_v_party_type VARCHAR2,
703 p_v_first_name VARCHAR2,
704 p_v_last_name VARCHAR2) RETURN VARCHAR2 AS
705 /******************************************************************
706 || Created By : [email protected]
707 || Created On : 18-FEB-2003
708 || Purpose : Procedure for deriving the supplier name
709 || Known limitations, enhancements or remarks :
710 || Change History :
711 || Who When What
712 || (reverse chronological order - newest change first)
713 ******************************************************************/
714 l_v_supplier_name po_vendors.vendor_name%TYPE;
715 BEGIN
716
717 -- If the party type of the party is Organization or
718 -- if the party type is person and the profile value is set to First Name, Last Name
719 -- derive the supplier name from party name
720 IF ((p_v_party_type = 'ORGANIZATION') OR (p_v_party_type = 'PERSON' AND g_v_sup_name = 'FIRST_LAST'))THEN
721 l_v_supplier_name := p_v_party_name;
722 ELSIF (p_v_party_type = 'PERSON' AND g_v_sup_name = 'LAST_FIRST') THEN
723
724 -- Else if the profile is set to Last Name, First Name, then derive
725 -- the supplier name by appending the first name to the last name
726 IF g_v_sup_name = 'LAST_FIRST' THEN
727 l_v_supplier_name := p_v_last_name||' '||p_v_first_name;
728 END IF;
729 END IF;
730
731 RETURN l_v_supplier_name;
732 END derive_vendor_name;
733
734 PROCEDURE derive_vendor_num(p_n_party_id PLS_INTEGER,
735 p_v_party_number VARCHAR2,
736 p_v_sup_num OUT NOCOPY po_vendors.segment1%TYPE,
737 p_b_status OUT NOCOPY BOOLEAN) AS
738 /******************************************************************
739 || Created By : [email protected]
740 || Created On : 18-FEB-2003
741 || Purpose : Procedure for deriving the supplier number
742 || Known limitations, enhancements or remarks :
743 || Change History :
744 || Who When What
745 || (reverse chronological order - newest change first)
746 ******************************************************************/
747
748 -- cursor for fetching the alternate person id from igs_pe_alt_pers_id
749 CURSOR c_api_pers(cp_n_party_id hz_parties.party_id%TYPE,
750 cp_v_pers_id_type igs_pe_alt_pers_id.person_id_type%TYPE) IS
751 SELECT api_person_id
752 FROM igs_pe_alt_pers_id
753 WHERE pe_person_id = cp_n_party_id
754 AND person_id_type = g_v_sup_num
755 AND sysdate BETWEEN start_dt AND NVL(end_dt,sysdate);
756
757 l_v_sup_num po_vendors.segment1%TYPE;
758 l_v_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE;
759
760 BEGIN
761 p_b_status := TRUE;
762
763 -- If the User Defined Vendor Code is not automatic in Financial
764 -- System parameters, then
765 IF NVL(g_v_ven_num_code,g_v_automatic) <> g_v_automatic THEN
766
767 -- Validate if the value of the profile is PARTY. If the value
768 -- is party, then the party number is the vendor number
769 IF g_v_sup_num = 'PARTY' THEN
770 l_v_sup_num := p_v_party_number;
771 ELSE
772
773 -- Else, derive the vendor number from the Alternate Person Id
774 OPEN c_api_pers(p_n_party_id,
775 p_v_party_number);
776 FETCH c_api_pers INTO l_v_api_person_id;
777 IF c_api_pers%NOTFOUND THEN
778 p_b_status := FALSE;
779 ELSE
780 l_v_sup_num := l_v_api_person_id;
781 END IF;
782 CLOSE c_api_pers;
783 END IF;
784 ELSE
785 l_v_sup_num := NULL;
786 END IF;
787
788 p_v_sup_num := l_v_sup_num;
789 END derive_vendor_num;
790
791 PROCEDURE create_supplier(p_n_party_id PLS_INTEGER,
792 p_n_vendor_id OUT NOCOPY PLS_INTEGER,
793 p_n_vendor_site_id OUT NOCOPY PLS_INTEGER,
794 p_b_status OUT NOCOPY BOOLEAN) AS
795 /******************************************************************
796 || Created By : [email protected]
797 || Created On : 18-FEB-2003
798 || Purpose : Procedure for creating the supplier and the supplier site
799 || Known limitations, enhancements or remarks :
800 || Change History :
801 || Who When What
802 || agairola 10-Mar-2003 Bug# 2838892: Initialized the local
803 || variables for the Vendor and vendor site
804 || id
805 || (reverse chronological order - newest change first)
806 agairola 20-Feb-2006 Bug 5046245: Commenting out of the Vendor API
807 ******************************************************************/
808 CURSOR c_prty_rel(cp_n_party_id hz_parties.party_id%TYPE) IS
809 SELECT a.rowid, a.*
810 FROM igs_fi_party_vendrs a
811 WHERE a.party_id = cp_n_party_id;
812
813 CURSOR c_po_active(cp_n_vendor_id igs_fi_party_vendrs.vendor_id%TYPE) IS
814 SELECT 'x'
815 FROM po_vendors
816 WHERE vendor_id = cp_n_vendor_id
817 AND TRUNC(SYSDATE) <= TRUNC(NVL(END_DATE_ACTIVE,sysdate));
818
819 CURSOR c_vendor_sites(cp_vendor_site_id igs_fi_party_vendrs.vendor_site_id%TYPE) IS
820 SELECT 'x'
821 FROM po_vendor_sites_all po
822 WHERE ((po.org_id = g_n_org_id) OR (po.org_id IS NULL AND g_n_org_id IS NULL))
823 AND po.vendor_site_id = cp_vendor_site_id
824 AND TRUNC(SYSDATE) <= TRUNC(NVL(po.inactive_date, sysdate));
825
826 CURSOR c_hz_party(cp_n_party_id hz_parties.party_id%TYPE) IS
827 SELECT party_type,
828 party_number,
829 party_name,
830 person_first_name,
831 person_last_name
832 FROM hz_parties
833 WHERE party_id = cp_n_party_id;
834
835 CURSOR c_hz_loc_addr(cp_n_location_id hz_locations.location_id%TYPE) IS
836 SELECT substr (address1,1,35) address1,
837 substr (address2,1,35) address2,
838 substr (address3,1,35) address3,
839 substr (address4,1,35) address4,
840 substr (city,1,25) city,
841 substr (state,1,25) state,
842 substr (postal_code,1,20) postal_code,
843 substr (province,1,25) province,
844 substr (county,1,25) county,
845 substr (country,1,25) country
846 FROM hz_locations
847 WHERE location_id = cp_n_location_id;
848
849 l_c_hz_party c_hz_party%ROWTYPE;
850 l_c_prty_rel c_prty_rel%ROWTYPE;
851 l_b_prty_rel BOOLEAN := TRUE;
852 l_c_hz_loc_addr c_hz_loc_addr%ROWTYPE;
853
854 l_v_var VARCHAR2(1);
855 l_n_location_id hz_locations.location_id%TYPE;
856
857 l_b_addr_stat BOOLEAN := TRUE;
858 l_b_vendor_site BOOLEAN := TRUE;
859 l_b_status BOOLEAN := TRUE;
860 l_v_msg VARCHAR2(2000);
861 l_n_vendor_id igs_fi_party_vendrs.vendor_id%TYPE;
862 l_n_vendor_site_id igs_fi_party_vendrs.vendor_id%TYPE;
863 l_v_vendor_name po_vendors.vendor_name%TYPE;
864 l_v_sup_num po_vendors.segment1%TYPE;
865 l_v_site_status VARCHAR2(100);
866 l_v_vendor_status VARCHAR2(100);
867
868 l_v_rowid VARCHAR2(50);
869
870 l_n_cntr NUMBER(10);
871
872 BEGIN
873 p_b_status := TRUE;
874
875 l_n_cntr:= null;
876
877 IF t_party_vendors.COUNT > 0 THEN
878 FOR l_n_cntr IN t_party_vendors.FIRST..t_party_vendors.LAST LOOP
879 IF t_party_vendors.EXISTS(l_n_cntr) THEN
880 IF t_party_vendors(l_n_cntr).p_n_party_id = p_n_party_id THEN
881 p_n_vendor_id := t_party_vendors(l_n_cntr).p_n_vendor_id;
882 p_n_vendor_site_id := t_party_vendors(l_n_cntr).p_n_vendor_site_id;
883 p_b_status := TRUE;
884 RETURN;
885 END IF;
886 END IF;
887 END LOOP;
888 END IF;
889
890 -- Get the party details from the hz_parties
891 OPEN c_hz_party(p_n_party_id);
892 FETCH c_hz_party INTO l_c_hz_party;
893 CLOSE c_hz_party;
894
895 -- Get the party relationships from the Supplier Relationship
896 -- table
897 OPEN c_prty_rel(p_n_party_id);
898 FETCH c_prty_rel INTO l_c_prty_rel;
899 IF c_prty_rel%FOUND THEN
900 l_b_prty_rel := TRUE;
901 ELSE
902 l_b_prty_rel := FALSE;
903 END IF;
904 CLOSE c_prty_rel;
905
906 -- If the party relationship exists, then
907 IF l_b_prty_rel THEN
908
909 l_n_vendor_id := l_c_prty_rel.vendor_id;
910 l_n_vendor_site_id := l_c_prty_rel.vendor_site_id;
911
912 -- Validate if the vendor is active in AP
913 -- If the vendor is not active in AP, then log error and return false
914 OPEN c_po_active(l_c_prty_rel.vendor_id);
915 FETCH c_po_active INTO l_v_var;
916 IF c_po_active%NOTFOUND THEN
917 p_b_status := FALSE;
918 fnd_file.put_line(fnd_file.log,
919 g_v_lbl_status||' : '||g_v_lbl_todo);
920 fnd_message.set_name('IGS','IGS_FI_SUPPLIER_INACTIVE');
921 fnd_file.put_line(fnd_file.log,
922 fnd_message.get);
923 END IF;
924 CLOSE c_po_active;
925
926 IF NOT p_b_status THEN
927 RETURN;
928 END IF;
929
930 -- Get the Pay To Address for the Party
931 get_payto_add(p_n_party_id => p_n_party_id,
932 p_v_party_number => l_c_hz_party.party_number,
933 p_n_location_id => l_n_location_id,
934 p_b_status => l_b_addr_stat);
935
936 -- If the Pay To Address procedure returns FALSE, then exit
937 IF NOT l_b_addr_stat THEN
938 p_b_status := FALSE;
939 RETURN;
940 END IF;
941
942 -- Get the location details
943 OPEN c_hz_loc_addr(l_n_location_id);
944 FETCH c_hz_loc_addr INTO l_c_hz_loc_addr;
945 CLOSE c_hz_loc_addr;
946
947 l_b_vendor_site := TRUE;
948
949 -- Get the vendor site details. If the Vendor Site is not active
950 -- a new vendor site needs to be created.
951 OPEN c_vendor_sites(l_c_prty_rel.vendor_site_id);
952 FETCH c_vendor_sites INTO l_v_var;
953 IF c_vendor_sites%NOTFOUND THEN
954 l_b_vendor_site := FALSE;
955 END IF;
956 CLOSE c_vendor_sites;
957
958 -- If the vendor site is active, then update the address of the
959 -- vendor site from the address details in TCA
960 IF l_b_vendor_site THEN
961 BEGIN
962 l_v_site_status := null;
963 l_v_msg := null;
964 /*
965 ap_po_vendors_apis_pkg.update_vendor_site(p_vendor_site_code => NULL,
966 p_vendor_site_id => l_c_prty_rel.vendor_site_id,
967 p_address_line1 => l_c_hz_loc_addr.address1,
968 p_address_line2 => l_c_hz_loc_addr.address2,
969 p_address_line3 => l_c_hz_loc_addr.address3,
970 p_address_line4 => l_c_hz_loc_addr.address4,
971 p_city => l_c_hz_loc_addr.city,
972 p_state => l_c_hz_loc_addr.state,
973 p_zip => l_c_hz_loc_addr.postal_code,
974 p_province => l_c_hz_loc_addr.province,
975 p_county => l_c_hz_loc_addr.county,
976 p_country => l_c_hz_loc_addr.country,
977 p_area_code => null,
978 p_phone => null,
979 p_fax_area_code => null,
980 p_fax => null,
981 p_email_address => null,
982 x_status => l_v_site_status,
983 x_exception_msg => l_v_msg); */
984 EXCEPTION
985 WHEN OTHERS THEN
986 l_v_site_status := 'F';
987 fnd_file.put_line(fnd_file.log,
988 g_v_lbl_status||' : '||g_v_lbl_todo);
989 fnd_file.put_line(fnd_file.log, l_v_msg||sqlerrm);
990 END;
991 IF l_v_site_status <> 'S' THEN
992 fnd_message.set_name('IGS','IGS_FI_VEN_SITE_NOT_UPD');
993 fnd_message.set_token('PARTY_NUM',l_c_hz_party.party_number);
994 fnd_file.put_line(fnd_file.log, fnd_message.get);
995 p_b_status := FALSE;
996 RETURN;
997 END IF;
998 ELSE
999
1000 -- The vendor site is inactive in AP. Hence, a new vendor site is created with the
1001 -- details of the address from TCA. This site has the Pay_Site_flag set.
1002 BEGIN
1003 l_n_vendor_site_id := null;
1004 l_v_site_status := null;
1005 l_v_msg := null;
1006 /*
1007 ap_po_vendors_apis_pkg.insert_new_vendor_site(p_vendor_site_code => g_v_dflt_sup_site,
1008 p_vendor_id => l_c_prty_rel.vendor_id,
1009 p_org_id => g_n_org_id,
1010 p_address_line1 => l_c_hz_loc_addr.address1,
1011 p_address_line2 => l_c_hz_loc_addr.address2,
1012 p_address_line3 => l_c_hz_loc_addr.address3,
1013 p_address_line4 => l_c_hz_loc_addr.address4,
1014 p_city => l_c_hz_loc_addr.city,
1015 p_state => l_c_hz_loc_addr.state,
1016 p_zip => l_c_hz_loc_addr.postal_code,
1017 p_province => l_c_hz_loc_addr.province,
1018 p_county => l_c_hz_loc_addr.county,
1019 p_country => l_c_hz_loc_addr.country,
1020 p_area_code => null,
1021 p_phone => null,
1022 p_fax_area_code => null,
1023 p_fax => null,
1024 p_email_address => null,
1025 p_purchasing_site_flag => null,
1026 p_pay_site_flag => 'Y',
1027 p_rfq_only_site_flag => null,
1028 x_vendor_site_id => l_n_vendor_site_id,
1029 x_status => l_v_site_status,
1030 x_exception_msg => l_v_msg);
1031 */
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 l_v_site_status := 'F';
1035 fnd_file.put_line(fnd_file.log,
1036 g_v_lbl_status||' : '||g_v_lbl_todo);
1037 fnd_file.put_line(fnd_file.log, l_v_msg||sqlerrm);
1038 END;
1039 IF l_v_site_status <> 'S' THEN
1040 fnd_message.set_name('IGS','IGS_FI_VEN_SITE_NOT_CREATED');
1041 fnd_message.set_name('PARTY_NUM',l_c_hz_party.party_number);
1042 fnd_file.put_line(fnd_file.log, fnd_message.get);
1043 p_b_status := FALSE;
1044 RETURN;
1045 END IF;
1046
1047 -- Update the vendor site in the Party vendors table
1048 igs_fi_party_vendrs_pkg.update_row(x_rowid => l_c_prty_rel.rowid,
1049 x_party_id => l_c_prty_rel.party_id,
1050 x_vendor_id => l_c_prty_rel.vendor_id,
1051 x_vendor_site_id => l_n_vendor_site_id);
1052
1053 END IF;
1054 ELSE
1055
1056 -- For the party, if the vendor relationship does not exist and the
1057 -- create supplier parameter is N, then exit
1058 IF g_v_create_supplier = 'N' THEN
1059 fnd_file.put_line(fnd_file.log,
1060 g_v_lbl_status||' : '||g_v_lbl_todo);
1061 fnd_message.set_name('IGS','IGS_FI_CREATE_SUPL_NO');
1062 fnd_message.set_token('PAYEE_NUM',l_c_hz_party.party_number);
1063 fnd_file.put_line(fnd_file.log, fnd_message.get);
1064 p_b_status := FALSE;
1065 RETURN;
1066 END IF;
1067
1068 -- get the pay to address for the party
1069 l_n_location_id := null;
1070 l_b_addr_stat := TRUE;
1071 get_payto_add(p_n_party_id => p_n_party_id,
1072 p_v_party_number => l_c_hz_party.party_number,
1073 p_n_location_id => l_n_location_id,
1074 p_b_status => l_b_addr_stat);
1075
1076 -- If the Pay To Address procedure returns FALSE, then exit
1077 IF NOT l_b_addr_stat THEN
1078 p_b_status := FALSE;
1079 RETURN;
1080 END IF;
1081
1082 -- Get the location details from HZ
1083 OPEN c_hz_loc_addr(l_n_location_id);
1084 FETCH c_hz_loc_addr INTO l_c_hz_loc_addr;
1085 CLOSE c_hz_loc_addr;
1086
1087 -- Derive the Vendor Name
1088 l_v_vendor_name := derive_vendor_name(p_v_party_name => l_c_hz_party.party_name,
1089 p_v_party_type => l_c_hz_party.party_type,
1090 p_v_first_name => l_c_hz_party.person_first_name,
1091 p_v_last_name => l_c_hz_party.person_last_name);
1092
1093 -- Derive the Vendor Number
1094 l_v_sup_num := null;
1095 l_b_status := TRUE;
1096 derive_vendor_num(p_n_party_id => p_n_party_id,
1097 p_v_party_number => l_c_hz_party.party_number,
1098 p_v_sup_num => l_v_sup_num,
1099 p_b_status => l_b_status);
1100
1101 -- If the vendor number cannot be derived, then exit.
1102 IF NOT l_b_status THEN
1103 fnd_file.put_line(fnd_file.log,
1104 g_v_lbl_status||' : '||g_v_lbl_todo);
1105 fnd_message.set_name('IGS','IGS_FI_SUP_NUM_NOT_DERIVED');
1106 fnd_message.set_token('PARTY_NUM',l_c_hz_party.party_number);
1107 fnd_file.put_line(fnd_file.log,
1108 fnd_message.get);
1109 p_b_status := FALSE;
1110 RETURN;
1111 END IF;
1112
1113 -- Create a New vendor
1114 BEGIN
1115 l_n_vendor_id := null;
1116 l_v_vendor_status := null;
1117 l_v_msg := null;
1118 /*
1119 ap_po_vendors_apis_pkg.insert_new_vendor(p_vendor_name => l_v_vendor_name,
1120 p_taxpayer_id => null,
1121 p_tax_registration_id => null,
1122 p_women_owned_flag => null,
1123 p_small_business_flag => null,
1124 p_minority_group_lookup_code => null,
1125 p_vendor_type_lookup_code => g_v_supplier_type,
1126 p_supplier_number => l_v_sup_num,
1127 x_vendor_id => l_n_vendor_id,
1128 x_status => l_v_vendor_status,
1129 x_exception_msg => l_v_msg); */
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 l_v_vendor_status := 'F';
1133 fnd_file.put_line(fnd_file.log,
1134 g_v_lbl_status||' : '||g_v_lbl_todo);
1135 fnd_file.put_line(fnd_file.log, l_v_msg||sqlerrm);
1136 END;
1137 IF l_v_vendor_status <> 'S' THEN
1138 fnd_message.set_name('IGS','IGS_FI_VENDOR_NOT_CREATED');
1139 fnd_message.set_token('PARTY_NUM',l_c_hz_party.party_number);
1140 fnd_file.put_line(fnd_file.log, fnd_message.get);
1141 p_b_status := FALSE;
1142 RETURN;
1143 END IF;
1144
1145 -- Create a new vendor site based on the address details from HZ
1146 BEGIN
1147 l_n_vendor_site_id := null;
1148 l_v_site_status := null;
1149 l_v_msg := null;
1150 /*
1151 ap_po_vendors_apis_pkg.insert_new_vendor_site(p_vendor_site_code => g_v_dflt_sup_site,
1152 p_vendor_id => l_n_vendor_id,
1153 p_org_id => g_n_org_id,
1154 p_address_line1 => l_c_hz_loc_addr.address1,
1155 p_address_line2 => l_c_hz_loc_addr.address2,
1156 p_address_line3 => l_c_hz_loc_addr.address3,
1157 p_address_line4 => l_c_hz_loc_addr.address4,
1158 p_city => l_c_hz_loc_addr.city,
1159 p_state => l_c_hz_loc_addr.state,
1160 p_zip => l_c_hz_loc_addr.postal_code,
1161 p_province => l_c_hz_loc_addr.province,
1162 p_county => l_c_hz_loc_addr.county,
1163 p_country => l_c_hz_loc_addr.country,
1164 p_area_code => null,
1165 p_phone => null,
1166 p_fax_area_code => null,
1167 p_fax => null,
1168 p_email_address => null,
1169 p_purchasing_site_flag => null,
1170 p_pay_site_flag => 'Y',
1171 p_rfq_only_site_flag => null,
1172 x_vendor_site_id => l_n_vendor_site_id,
1173 x_status => l_v_site_status,
1174 x_exception_msg => l_v_msg); */
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 l_v_site_status := 'F';
1178 fnd_file.put_line(fnd_file.log,
1179 g_v_lbl_status||' : '||g_v_lbl_todo);
1180 fnd_file.put_line(fnd_file.log, l_v_msg||sqlerrm);
1181 END;
1182 IF l_v_site_status <> 'S' THEN
1183 fnd_message.set_name('IGS','IGS_FI_VEN_SITE_NOT_CREATED');
1184 fnd_message.set_token('PARTY_NUM',l_c_hz_party.party_number);
1185 fnd_file.put_line(fnd_file.log, fnd_message.get);
1186 p_b_status := FALSE;
1187 RETURN;
1188 END IF;
1189
1190 -- Create a new record in the vendor relationships table
1191 l_v_rowid := null;
1192 igs_fi_party_vendrs_pkg.insert_row(x_rowid => l_v_rowid,
1193 x_party_id => p_n_party_id,
1194 x_vendor_id => l_n_vendor_id,
1195 x_vendor_site_id => l_n_vendor_site_id);
1196 END IF;
1197
1198 l_n_cntr := t_party_vendors.COUNT + 1;
1199 t_party_vendors(l_n_cntr).p_n_party_id := p_n_party_id;
1200 t_party_vendors(l_n_cntr).p_n_vendor_id := l_n_vendor_id;
1201 t_party_vendors(l_n_cntr).p_n_vendor_site_id := l_n_vendor_site_id;
1202 p_n_vendor_id := l_n_vendor_id;
1203 p_n_vendor_site_id := l_n_vendor_site_id;
1204 p_b_status := TRUE;
1205
1206 END create_supplier;
1207
1208 PROCEDURE create_ap_int_rec(p_n_refund_id PLS_INTEGER,
1209 p_d_vchr_date DATE,
1210 p_n_vendor_id PLS_INTEGER,
1211 p_n_vendor_site_id PLS_INTEGER,
1212 p_n_rfnd_amnt NUMBER,
1213 p_n_terms_id PLS_INTEGER,
1214 p_v_grp_code VARCHAR2,
1215 p_d_gl_date DATE,
1216 p_gl_dr_ccid PLS_INTEGER,
1217 p_gl_cr_ccid PLS_INTEGER) AS
1218 /******************************************************************
1219 || Created By : [email protected]
1220 || Created On : 18-FEB-2003
1221 || Purpose : Procedure for creating a record in the AP_INVOICES_INTERFACE and
1222 || AP_INVOICE_LINES_INTERFACE
1223 || Known limitations, enhancements or remarks :
1224 || Change History :
1225 || Who When What
1226 || abshriva 12-MAy-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
1227 || (reverse chronological order - newest change first)
1228 ******************************************************************/
1229 CURSOR c_inv_int IS
1230 SELECT AP_INVOICES_INTERFACE_S.NEXTVAL
1231 FROM dual;
1232
1233 l_n_inv_id ap_invoices_interface.invoice_id%TYPE;
1234 l_v_inv_lkp igs_lookup_values.lookup_code%TYPE;
1235 l_n_rfnd_amt NUMBER := 0;
1236 BEGIN
1237
1238 -- Get the new Invoice Id from the AP_INVOICES_INTERFACE_S sequence
1239 OPEN c_inv_int;
1240 FETCH c_inv_int INTO l_n_inv_id;
1241 CLOSE c_inv_int;
1242
1243 -- If the Refund Amount is negative, then the Invoice Type is
1244 -- Credit else it is standard
1245 IF p_n_rfnd_amnt < 0 THEN
1246 l_v_inv_lkp := g_v_credit;
1247 ELSE
1248 l_v_inv_lkp := g_v_standard;
1249 END IF;
1250 l_n_rfnd_amt :=igs_fi_gen_gl.get_formatted_amount(p_n_rfnd_amnt);
1251 -- Create a transaction in AP_INVOICES_INTERFACE
1252 INSERT INTO ap_invoices_interface(invoice_id,
1253 invoice_num,
1254 invoice_type_lookup_code,
1255 invoice_date,
1256 vendor_id,
1257 vendor_site_id,
1258 invoice_amount,
1259 invoice_currency_code,
1260 terms_id,
1261 source,
1262 pay_group_lookup_code,
1263 gl_date,
1264 accts_pay_code_combination_id,
1265 exclusive_payment_flag,
1266 org_id,
1267 terms_date)
1268 VALUES(l_n_inv_id,
1269 p_n_refund_id,
1270 l_v_inv_lkp,
1271 p_d_vchr_date,
1272 p_n_vendor_id,
1273 p_n_vendor_site_id,
1274 l_n_rfnd_amt,
1275 g_v_cur_code,
1276 p_n_terms_id,
1277 g_v_stdnt_system,
1278 p_v_grp_code,
1279 p_d_gl_date,
1280 p_gl_cr_ccid,
1281 g_v_pay_rfnd_vchr,
1282 g_n_org_id,
1283 p_d_vchr_date);
1284
1285 -- Create a transaction in AP_INVOICE_LINES_INTERFACE
1286 INSERT INTO ap_invoice_lines_interface(invoice_id,
1287 invoice_line_id,
1288 line_number,
1289 line_type_lookup_code,
1290 accounting_date,
1291 amount,
1292 dist_code_combination_id,
1293 org_id)
1294 VALUES(l_n_inv_id,
1295 NULL,
1296 1,
1297 g_v_item,
1298 p_d_gl_date,
1299 l_n_rfnd_amt,
1300 p_gl_dr_ccid,
1301 g_n_org_id);
1302
1303
1304 END create_ap_int_rec;
1305
1306
1307 PROCEDURE process_refunds(p_n_party_id PLS_INTEGER,
1308 p_v_inv_pay_group VARCHAR2,
1309 p_n_inv_pay_term NUMBER) AS
1310 /******************************************************************
1311 || Created By : [email protected]
1312 || Created On : 18-FEB-2003
1313 || Purpose : Procedure for processing the refund transactions
1314 || Known limitations, enhancements or remarks :
1315 || Change History :
1316 || Who When What
1317 || agairola 11-Mar-2003 Bug# 2838757: Set the variable
1318 || g_b_data_found to true when records
1319 || are processed
1320 || (reverse chronological order - newest change first)
1321 ******************************************************************/
1322
1323 -- Cursor to get all the refund transactions that have been
1324 -- created due to reversal of a refund transaction and have TODO status
1325 CURSOR c_rfnd(cp_n_party_id hz_parties.party_id%TYPE) IS
1326 SELECT rfnd.rowid, rfnd.*
1327 FROM igs_fi_refunds rfnd
1328 WHERE rfnd.person_id = cp_n_party_id
1329 AND rfnd.transfer_status = g_v_todo
1330 AND rfnd.source_refund_id IS NOT NULL
1331 ORDER BY rfnd.pay_person_id
1332 FOR UPDATE NOWAIT;
1333
1334 -- Get the refund voucher details for the Refund Id
1335 CURSOR c_rfnd_org(cp_n_refund_id igs_fi_refunds.refund_id%TYPE) IS
1336 SELECT rfnd.rowid, rfnd.*
1337 FROM igs_fi_refunds rfnd
1338 WHERE rfnd.refund_id = cp_n_refund_id
1339 FOR UPDATE NOWAIT;
1340
1341 -- Get all the refund transactions that have not been reversed and have
1342 -- status of TODO
1343 CURSOR c_rfnd1(cp_n_party_id hz_parties.party_id%TYPE) IS
1344 SELECT rfnd.rowid, rfnd.*
1345 FROM igs_fi_refunds rfnd
1346 WHERE rfnd.person_id = cp_n_party_id
1347 AND rfnd.transfer_status = g_v_todo
1348 AND rfnd.source_refund_id IS NULL
1349 ORDER BY rfnd.pay_person_id
1350 FOR UPDATE NOWAIT;
1351
1352 l_c_rfnd_org c_rfnd_org%ROWTYPE;
1353 l_b_status BOOLEAN := TRUE;
1354 l_n_vendor_id igs_fi_party_vendrs.vendor_id%TYPE;
1355 l_n_vendor_site_id igs_fi_party_vendrs.vendor_site_id%TYPE;
1356 BEGIN
1357
1358 -- Loop across all the refund transactions
1359 FOR l_c_rfnd_rec IN c_rfnd(p_n_party_id) LOOP
1360
1361 g_b_data_found := TRUE;
1362 -- Get the status of the original refund transaction
1363 OPEN c_rfnd_org(l_c_rfnd_rec.source_refund_id);
1364 FETCH c_rfnd_org INTO l_c_rfnd_org;
1365 CLOSE c_rfnd_org;
1366
1367 -- If the status is TODO, then both the current transaction
1368 -- and the original transaction have to be updated to status OFFSET
1369 IF l_c_rfnd_org.transfer_status = g_v_todo THEN
1370 l_c_rfnd_rec.transfer_status := g_v_offset;
1371 log_transaction(p_n_party_id => l_c_rfnd_rec.person_id,
1372 p_n_payee_id => l_c_rfnd_rec.pay_person_id,
1373 p_n_refund_id => l_c_rfnd_rec.refund_id);
1374 fnd_file.put_line(fnd_file.log,
1375 g_v_lbl_status||' : '||g_v_lbl_offset);
1376 fnd_file.new_line(fnd_file.log);
1377 update_refund_rec(l_c_rfnd_rec);
1378
1379 l_c_rfnd_org.transfer_status := g_v_offset;
1380 log_transaction(p_n_party_id => l_c_rfnd_org.person_id,
1381 p_n_payee_id => l_c_rfnd_org.pay_person_id,
1382 p_n_refund_id => l_c_rfnd_org.refund_id);
1383 fnd_file.put_line(fnd_file.log,
1384 g_v_lbl_status||' : '||g_v_lbl_offset);
1385 fnd_file.new_line(fnd_file.log);
1386 update_refund_rec(l_c_rfnd_org);
1387
1388 ELSIF l_c_rfnd_org.transfer_status = 'TRANSFERRED' THEN
1389
1390 -- Else if the original transaction was transferred, this refund transaction
1391 -- also needs to be transferred.
1392 log_transaction(p_n_party_id => l_c_rfnd_rec.person_id,
1393 p_n_payee_id => l_c_rfnd_rec.pay_person_id,
1394 p_n_refund_id => l_c_rfnd_rec.refund_id);
1395
1396 -- Call the create supplier procedure for creating a supplier for the refund payee
1397 create_supplier(p_n_party_id => l_c_rfnd_rec.pay_person_id,
1398 p_n_vendor_id => l_n_vendor_id,
1399 p_n_vendor_site_id => l_n_vendor_site_id,
1400 p_b_status => l_b_status);
1401
1402 BEGIN
1403 SAVEPOINT SP_PROCESS;
1404
1405 -- If the create supplier procedure returns TRUE, then
1406 IF l_b_status THEN
1407
1408 -- Create a transaction in AP
1409 create_ap_int_rec(p_n_refund_id => l_c_rfnd_rec.refund_id,
1410 p_d_vchr_date => l_c_rfnd_rec.voucher_date,
1411 p_n_vendor_id => l_n_vendor_id,
1412 p_n_vendor_site_id => l_n_vendor_site_id,
1413 p_n_rfnd_amnt => l_c_rfnd_rec.refund_amount,
1414 p_n_terms_id => p_n_inv_pay_term,
1415 p_v_grp_code => p_v_inv_pay_group,
1416 p_d_gl_date => l_c_rfnd_rec.gl_date,
1417 p_gl_dr_ccid => l_c_rfnd_rec.dr_gl_ccid,
1418 p_gl_cr_ccid => l_c_rfnd_rec.cr_gl_ccid);
1419 l_c_rfnd_rec.transfer_status := g_v_transferred;
1420
1421 -- Update the refund transaction to TRANSFERRED status
1422 update_refund_rec(l_c_rfnd_rec);
1423 fnd_file.put_line(fnd_file.log,
1424 g_v_lbl_status||' : '||g_v_lbl_transferred);
1425 fnd_file.new_line(fnd_file.log);
1426 END IF;
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 ROLLBACK TO SP_PROCESS;
1430 fnd_file.put_line(fnd_file.log,
1431 g_v_lbl_status||' : '||g_v_lbl_todo);
1432 fnd_file.put_line(fnd_file.log,
1433 g_v_lbl_error||':'||sqlerrm);
1434 fnd_file.new_line(fnd_file.log);
1435 END;
1436 END IF;
1437 END LOOP;
1438
1439 -- Loop across all the refund transactions that have not been reversed.
1440 FOR l_c_rfnd_rec IN c_rfnd1(p_n_party_id) LOOP
1441
1442 g_b_data_found := TRUE;
1443 log_transaction(p_n_party_id => l_c_rfnd_rec.person_id,
1444 p_n_payee_id => l_c_rfnd_rec.pay_person_id,
1445 p_n_refund_id => l_c_rfnd_rec.refund_id);
1446
1447 -- call the supplier creation procedure
1448 create_supplier(p_n_party_id => l_c_rfnd_rec.pay_person_id,
1449 p_n_vendor_id => l_n_vendor_id,
1450 p_n_vendor_site_id => l_n_vendor_site_id,
1451 p_b_status => l_b_status);
1452
1453 BEGIN
1454 SAVEPOINT SP_PROCESS;
1455 -- If the supplier creation is sucessful, then
1456 IF l_b_status THEN
1457
1458 -- Create a transaction in AP
1459 create_ap_int_rec(p_n_refund_id => l_c_rfnd_rec.refund_id,
1460 p_d_vchr_date => l_c_rfnd_rec.voucher_date,
1461 p_n_vendor_id => l_n_vendor_id,
1462 p_n_vendor_site_id => l_n_vendor_site_id,
1463 p_n_rfnd_amnt => l_c_rfnd_rec.refund_amount,
1464 p_n_terms_id => p_n_inv_pay_term,
1465 p_v_grp_code => p_v_inv_pay_group,
1466 p_d_gl_date => l_c_rfnd_rec.gl_date,
1467 p_gl_dr_ccid => l_c_rfnd_rec.dr_gl_ccid,
1468 p_gl_cr_ccid => l_c_rfnd_rec.cr_gl_ccid);
1469
1470 -- Update the refund transaction to the status of Transferred
1471 l_c_rfnd_rec.transfer_status := g_v_transferred;
1472 update_refund_rec(l_c_rfnd_rec);
1473 fnd_file.put_line(fnd_file.log,
1474 g_v_lbl_status||' : '||g_v_lbl_transferred);
1475 END IF;
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 ROLLBACK TO SP_PROCESS;
1479 fnd_file.put_line(fnd_file.log,
1480 g_v_lbl_status||' : '||g_v_lbl_todo);
1481 fnd_file.put_line(fnd_file.log,
1482 g_v_lbl_error||':'||sqlerrm);
1483 fnd_file.new_line(fnd_file.log);
1484 END;
1485 END LOOP;
1486 END process_refunds;
1487
1488 PROCEDURE transfer(errbuf OUT NOCOPY VARCHAR2,
1489 retcode OUT NOCOPY NUMBER,
1490 p_n_party_id IN NUMBER,
1491 p_n_person_group_id IN NUMBER,
1492 p_v_create_supplier IN VARCHAR2,
1493 p_v_supplier_type IN VARCHAR2,
1494 p_v_inv_pay_group IN VARCHAR2,
1495 p_n_inv_pay_term IN NUMBER,
1496 p_v_test_run IN VARCHAR2) AS
1497 /******************************************************************
1498 || Created By : [email protected]
1499 || Created On : 18-FEB-2003
1500 || Purpose : Main procedure for the concurrent manager
1501 || Known limitations, enhancements or remarks :
1502 || Change History :
1503 || Who When What
1504 || ridas 14-Feb-2006 Bug #5021084. Added new parameter lv_group_type
1505 || in call to igf_ap_ss_pkg.get_pid
1506 || agairola 11-Mar-2003 Bug 2838757: Modified the code to
1507 || log the message no data found when
1508 || no records are found
1509 || (reverse chronological order - newest change first)
1510 ******************************************************************/
1511 CURSOR c_rfnd_per IS
1512 SELECT DISTINCT person_id
1513 FROM igs_fi_refunds
1514 WHERE transfer_status = g_v_todo;
1515
1516 TYPE c_per_grp_cur IS REF CURSOR;
1517
1518 l_v_stmnt VARCHAR2(32767);
1519
1520 l_c_per_grp_cur c_per_grp_cur;
1521 l_b_val_parm BOOLEAN ;
1522
1523 l_n_party_id hz_parties.party_id%TYPE;
1524
1525 l_v_status VARCHAR2(10);
1526 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
1527
1528 BEGIN
1529
1530 -- Create a save point for the process
1531 SAVEPOINT SP_TRANSFER;
1532
1533 retcode := 0;
1534 errbuf := null;
1535
1536 l_b_val_parm := TRUE;
1537 g_v_create_supplier := p_v_create_supplier;
1538
1539 -- call the initialization procedure
1540 initialize;
1541
1542 -- validate input parameters
1543 l_b_val_parm := validate_parameters(p_n_party_id => p_n_party_id,
1544 p_n_person_group_id => p_n_person_group_id,
1545 p_v_create_supplier => p_v_create_supplier,
1546 p_v_supplier_type => p_v_supplier_type,
1547 p_v_inv_pay_group => p_v_inv_pay_group,
1548 p_n_inv_pay_term => p_n_inv_pay_term,
1549 p_v_test_run => p_v_test_run);
1550
1551 IF NOT l_b_val_parm THEN
1552 retcode := 2;
1553 RETURN;
1554 END IF;
1555
1556 g_v_supplier_type := p_v_supplier_type;
1557
1558 -- If the party id passed as input is NOT NULL, then pass
1559 -- the party id to the process_refunds procedure
1560 IF p_n_party_id IS NOT NULL THEN
1561
1562 process_refunds(p_n_party_id => p_n_party_id,
1563 p_v_inv_pay_group => p_v_inv_pay_group,
1564 p_n_inv_pay_term => p_n_inv_pay_term);
1565
1566 ELSIF p_n_person_group_id IS NOT NULL THEN
1567 -- If the Person Group is Not null, then
1568 -- fetch the query for the dynamic person groups
1569 --Bug #5021084
1570 l_v_stmnt := igf_ap_ss_pkg.get_pid(p_pid_grp => p_n_person_group_id,
1571 p_status => l_v_status,
1572 p_group_type => lv_group_type);
1573
1574 IF l_v_status <> 'S' THEN
1575 fnd_file.put_line(fnd_file.log, l_v_stmnt);
1576 RETURN;
1577 END IF;
1578
1579 -- Execute the query returned by the procedure and for all the person id
1580 -- returned by the query, transfer the refund transactions
1581
1582 --Bug #5021084. Passing Group ID if the group type is STATIC.
1583 IF lv_group_type = 'STATIC' THEN
1584 OPEN l_c_per_grp_cur FOR l_v_stmnt USING p_n_person_group_id;
1585 ELSIF lv_group_type = 'DYNAMIC' THEN
1586 OPEN l_c_per_grp_cur FOR l_v_stmnt;
1587 END IF;
1588
1589 LOOP
1590 FETCH l_c_per_grp_cur INTO l_n_party_id;
1591 EXIT WHEN l_c_per_grp_cur%NOTFOUND;
1592 process_refunds(p_n_party_id => l_n_party_id,
1593 p_v_inv_pay_group => p_v_inv_pay_group,
1594 p_n_inv_pay_term => p_n_inv_pay_term);
1595 END LOOP;
1596 CLOSE l_c_per_grp_cur;
1597
1598 ELSIF p_n_person_group_id IS NULL and p_n_party_id IS NULL THEN
1599
1600 -- Else if the person group is null and the party id is also null, the
1601 -- process refunds for all the parties having a TODO record in the
1602 -- Refunds
1603 FOR l_c_rfnd_per IN c_rfnd_per LOOP
1604 process_refunds(p_n_party_id => l_c_rfnd_per.person_id,
1605 p_v_inv_pay_group => p_v_inv_pay_group,
1606 p_n_inv_pay_term => p_n_inv_pay_term);
1607 END LOOP;
1608 END IF;
1609
1610 -- If the test run parameter = 'Y', then rollback all the transactions
1611 -- and exit.
1612 IF (p_v_test_run = 'Y' AND g_b_data_found) THEN
1613 ROLLBACK TO SP_TRANSFER;
1614 fnd_message.set_name('IGS',
1615 'IGS_FI_PRC_TEST_RUN');
1616 fnd_file.put_line(fnd_file.log,
1617 fnd_message.get);
1618 ELSE
1619 COMMIT;
1620 END IF;
1621
1622 -- If there are no records found then log the message No Data Found
1623 IF NOT g_b_data_found THEN
1624 fnd_message.set_name('IGS',
1625 'IGS_GE_NO_DATA_FOUND');
1626 fnd_file.put_line(fnd_file.log,
1627 fnd_message.get);
1628 END IF;
1629
1630 EXCEPTION
1631 WHEN e_resource_busy THEN
1632 retcode := 2;
1633 fnd_message.set_name('IGS',
1634 'IGS_FI_RFND_REC_LOCK');
1635 fnd_file.put_line(fnd_file.log, fnd_message.get);
1636 WHEN OTHERS THEN
1637 retcode := 2;
1638 ROLLBACK TO SP_TRANSFER;
1639 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1640 fnd_file.put_line(fnd_file.log,
1641 fnd_message.get||' - '||sqlerrm);
1642 END transfer;
1643
1644 END igs_fi_prc_apint;
[Home] [Help]