1 PACKAGE BODY igf_se_payment_pub AS
2 /* $Header: IGFSE02B.pls 120.0 2005/06/01 15:00:56 appldev noship $ */
3 /*=======================================================================+
4 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | |
8 | DESCRIPTION |
9 | PL/SQL body for package: igf_se_payment_pub |
10 | |
11 | NOTES |
12 | |
13 | |
14 | |
15 | HISTORY |
16 | Who When What |
17 *=======================================================================*/
18
19 /** private procedure
20 * forward declaration here
21 */
22 PROCEDURE do_create_payment(
23 p_payment_record IN payment_rec_type,
24 x_transaction_id OUT NOCOPY NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2
26 );
27
28 /** main procedure create_payment
29 *
30 */
31 PROCEDURE create_payment(
32 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
33 p_payment_rec IN payment_rec_type,
34 x_transaction_id OUT NOCOPY NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2
38 ) IS
39 BEGIN
40
41 -- establish standard save point
42 SAVEPOINT create_payment;
43
44 -- initialize message list if p_init_msg_list is set to TRUE.
45 IF FND_API.to_Boolean(p_init_msg_list) THEN
46 FND_MSG_PUB.initialize;
47 END IF;
48
49 -- initialize API return status to success.
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51
52 -- call local procedure to create the payment record
53 do_create_payment(p_payment_record => p_payment_rec,
54 x_transaction_id => x_transaction_id,
55 x_return_status => x_return_status);
56
57 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
58 RAISE FND_API.G_EXC_ERROR;
59 END IF;
60
61
62 EXCEPTION
63 WHEN FND_API.G_EXC_ERROR THEN
64 ROLLBACK TO create_payment;
65
66 x_return_status := FND_API.G_RET_STS_ERROR;
67
68 fnd_msg_pub.count_and_get(
69 p_encoded => fnd_api.g_false,
70 p_count => x_msg_count,
71 p_data => x_msg_data);
72
73 WHEN OTHERS THEN
74 ROLLBACK TO create_payment;
75
76 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
77
78 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
79 fnd_message.set_token('NAME','IGF_SE_PAYMENT_PUB '||SQLERRM);
80 fnd_msg_pub.add;
81
82 fnd_msg_pub.count_and_get(
83 p_encoded => fnd_api.g_false,
84 p_count => x_msg_count,
85 p_data => x_msg_data);
86
87 END create_payment;
88
89 PROCEDURE do_create_payment(p_payment_record payment_rec_type,
90 x_transaction_id OUT NOCOPY NUMBER,
91 x_return_status OUT NOCOPY VARCHAR2
92 ) IS
93
94 -- cursor to check if the existing payroll id, person id and authorization id
95 -- is already existing in the system. this cursor will have FTS ?
96 CURSOR c_check_payroll (p_payroll_id igf_se_payment.payroll_id%TYPE,
97 p_auth_id igf_se_auth.auth_id%TYPE,
98 p_person_id hz_parties.party_id%TYPE) IS
99 SELECT ROWID, p.*
100 FROM igf_se_payment p
101 WHERE p.payroll_id = p_payroll_id
102 AND p.auth_id = p_auth_id
103 AND p.person_id = p_person_id;
104
105 -- cursor to get the fund id from the supplied parameters
106 CURSOR c_get_fund_id (p_auth_id igf_se_auth.auth_id%TYPE,
107 p_person_id hz_parties.party_id%TYPE) IS
108 SELECT se.fund_id
109 FROM igf_se_auth se
110 WHERE se.flag = 'A'
111 AND se.person_id = p_person_id
112 AND se.auth_id = p_auth_id;
113
114 c_check_payroll_rec c_check_payroll%ROWTYPE;
115 l_fund_id igf_aw_award_all.fund_id%TYPE;
116 l_rowid VARCHAR2(25);
117 l_transaction_id igf_se_payment.transaction_id%TYPE;
118
119 CURSOR c_pers_num(
120 cp_person_id hz_parties.party_id%TYPE
121 ) IS
122 SELECT party_number
123 FROM hz_parties
124 WHERE party_id = cp_person_id;
125 l_pers_num hz_parties.party_number%TYPE;
126
127 BEGIN
128 l_fund_id := NULL;
129 l_transaction_id := NULL;
130
131 -- begin mandatory validations
132 -- a. payroll_id cannot be null. this validation is placed in the api
133 -- and not in the tbh as payroll id from ui is not mandatory
134 IF(p_payment_record.payroll_id IS NULL)THEN
135 fnd_message.set_name('IGF','IGF_SE_PAYROLL_ID_NULL');
136 fnd_msg_pub.add;
137 RAISE FND_API.G_EXC_ERROR;
138 END IF;
139
140 -- check if the payroll id is already existing in the system, if so,
141 -- that payment information record should be updated, else a new payment
142 -- record has to be created. payroll date, paid amount, organization unit name and source
143 -- can be updated.
144 OPEN c_check_payroll(p_payment_record.payroll_id, p_payment_record.authorization_id, p_payment_record.person_id);
145 FETCH c_check_payroll INTO c_check_payroll_rec; CLOSE c_check_payroll;
146 IF(c_check_payroll_rec.transaction_id IS NOT NULL)THEN
147 -- call to update the new payment information
148 igf_se_payment_pkg.update_row(
149 x_rowid => c_check_payroll_rec.rowid,
150 x_transaction_id => c_check_payroll_rec.transaction_id,
151 x_payroll_id => c_check_payroll_rec.payroll_id,
152 x_payroll_date => p_payment_record.payroll_date,
153 x_auth_id => c_check_payroll_rec.auth_id,
154 x_person_id => c_check_payroll_rec.person_id,
155 x_fund_id => c_check_payroll_rec.fund_id,
156 x_paid_amount => p_payment_record.paid_amount,
157 x_org_unit_cd => p_payment_record.organization_unit_name,
158 x_source => p_payment_record.source,
159 x_mode => 'R'
160 );
161 ELSE
162 --derive the fund id value before insert
163 OPEN c_get_fund_id(p_payment_record.authorization_id, p_payment_record.person_id);
164 FETCH c_get_fund_id INTO l_fund_id; CLOSE c_get_fund_id;
165 IF(l_fund_id IS NOT NULL)THEN
166 -- call to record the existing payment information
167
168 igf_se_payment_pkg.insert_row(
169 x_rowid => l_rowid,
170 x_transaction_id => l_transaction_id,
171 x_payroll_id => p_payment_record.payroll_id,
172 x_payroll_date => p_payment_record.payroll_date,
173 x_auth_id => p_payment_record.authorization_id,
174 x_person_id => p_payment_record.person_id,
175 x_fund_id => l_fund_id,
176 x_paid_amount => p_payment_record.paid_amount,
177 x_org_unit_cd => p_payment_record.organization_unit_name,
178 x_source => p_payment_record.source,
179 x_mode => 'R'
180 );
181 IF(l_transaction_id IS NULL)THEN
182
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 ELSE
185 x_transaction_id := l_transaction_id;
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187 END IF; -- end for transaction id is null condition
188 ELSE
189 fnd_message.set_name('IGF','IGF_SE_NO_VALID_FUND');
190
191 l_pers_num := NULL;
192 OPEN c_pers_num(p_payment_record.person_id);
193 FETCH c_pers_num INTO l_pers_num;
194 CLOSE c_pers_num;
195 fnd_message.set_token('PERSON_NUM',l_pers_num);
196 fnd_msg_pub.add;
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 RAISE FND_API.G_EXC_ERROR;
199 END IF;
200 END IF; -- end of payroll exists check
201 END do_create_payment;
202
203 END igf_se_payment_pub;
[Home] [Help]