[Home] [Help]
PACKAGE BODY: APPS.CN_PAYMENT_WORKSHEETS_PKG
Source
1 PACKAGE BODY cn_payment_worksheets_pkg AS
2 -- $Header: cntwkshb.pls 120.4 2005/09/24 14:11:13 fmburu ship $
3
4 --============================================================================
5 -- Procedure Name : Insert_Record
6 -- Purpose : Main insert procedure
7 -- Called from : cn_prepost_pvt
8 --============================================================================
9 PROCEDURE INSERT_RECORD (
10 x_payment_worksheet_id cn_payment_worksheets.payment_worksheet_id%TYPE := NULL,
11 x_payrun_id cn_payment_worksheets.payrun_id%TYPE,
12 x_salesrep_id cn_payment_worksheets.salesrep_id%TYPE,
13 x_quota_id cn_payment_worksheets.quota_id%TYPE := NULL,
14 x_cost_center_id cn_payment_worksheets.cost_center_id%TYPE := NULL,
15 x_role_id cn_payment_worksheets.role_id%TYPE := NULL,
16 x_credit_type_id cn_payment_worksheets.credit_type_id%TYPE,
17 x_calc_pmt_amount cn_payment_worksheets.pmt_amount_calc%TYPE := 0,
18 x_adj_pmt_amount_rec cn_payment_worksheets.pmt_amount_calc%TYPE := 0,
19 x_adj_pmt_amount_nrec cn_payment_worksheets.pmt_amount_calc%TYPE := 0,
20 x_adj_pmt_amount cn_payment_worksheets.pmt_amount_calc%TYPE := 0,
21 x_held_amount cn_payment_worksheets.held_amount%TYPE := 0,
22 x_pmt_amount_recovery cn_payment_worksheets.pmt_amount_calc%TYPE := 0,
23 x_comm_paid cn_payment_worksheets.draw_ptd%TYPE := 0,
24 x_bonus_paid cn_payment_worksheets.draw_ptd%TYPE := 0,
25 x_draw_paid cn_payment_worksheets.draw_ptd%TYPE := 0,
26 x_comm_nrec cn_payment_worksheets.draw_ptd%TYPE := 0,
27 x_created_by cn_payment_worksheets.created_by%TYPE,
28 x_creation_date cn_payment_worksheets.creation_date%TYPE,
29 x_worksheet_status cn_payment_worksheets.worksheet_status%TYPE,
30 p_org_id cn_payment_worksheets.org_id%TYPE,
31 p_object_version_number cn_payment_worksheets.object_version_number%TYPE
32 )
33 IS
34 BEGIN
35 INSERT INTO cn_payment_worksheets
36 (payment_worksheet_id,
37 payrun_id,
38 salesrep_id,
39 cost_center_id,
40 quota_id,
41 role_id,
42 credit_type_id,
43 pmt_amount_calc,
44 pmt_amount_adj_rec,
45 pmt_amount_adj_nrec,
46 pmt_amount_adj,
47 pmt_amount_recovery,
48 held_amount,
49 draw_paid,
50 bonus_paid,
51 comm_paid,
52 comm_nrec,
53 worksheet_status,
54 created_by,
55 creation_date,
56 --R12
57 org_id,
58 object_version_number
59 )
60 VALUES (NVL (x_payment_worksheet_id, cn_payment_worksheets_s.NEXTVAL),
61 x_payrun_id,
62 x_salesrep_id,
63 x_cost_center_id,
64 x_quota_id,
65 x_role_id,
66 x_credit_type_id,
67 x_calc_pmt_amount,
68 x_adj_pmt_amount_rec,
69 x_adj_pmt_amount_nrec,
70 x_adj_pmt_amount,
71 x_pmt_amount_recovery,
72 x_held_amount,
73 x_draw_paid,
74 x_bonus_paid,
75 x_comm_paid,
76 x_comm_nrec,
77 x_worksheet_status,
78 x_created_by,
79 x_creation_date,
80 --R12
81 p_org_id,
82 p_object_version_number
83 );
84 END INSERT_RECORD;
85
86 --============================================================================
87 -- Procedure Name : Lock_Record
88 -- Purpose : Lock db row after form record is changed
89 -- Notes : Only called from the form
90 --============================================================================
91 PROCEDURE LOCK_RECORD (
92 x_payment_worksheet_id NUMBER
93 )
94 IS
95 CURSOR c
96 IS
97 SELECT *
98 FROM cn_payment_worksheets
99 WHERE payment_worksheet_id = x_payment_worksheet_id
100 FOR UPDATE OF payment_worksheet_id NOWAIT;
101
102 recinfo c%ROWTYPE;
103 BEGIN
104 OPEN c;
105
106 FETCH c
107 INTO recinfo;
108
109 IF (c%NOTFOUND)
110 THEN
111 CLOSE c;
112
113 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 END IF;
116
117 CLOSE c;
118
119 IF recinfo.payment_worksheet_id = x_payment_worksheet_id
120 THEN
121 RETURN;
122 ELSE
123 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 END IF;
126 END LOCK_RECORD;
127
128 --============================================================================
129 -- Procedure Name : Update Record
130 -- Purpose : To Update the Payment worksheet
131 --============================================================================
132 PROCEDURE UPDATE_RECORD (
133 x_payment_worksheet_id cn_payment_worksheets.payment_worksheet_id%TYPE,
134 x_payrun_id cn_payment_worksheets.payrun_id%TYPE := cn_api.g_miss_id,
135 x_salesrep_id cn_payment_worksheets.salesrep_id%TYPE := cn_api.g_miss_id,
136 x_cost_center_id cn_payment_worksheets.cost_center_id%TYPE := cn_api.g_miss_id,
137 x_role_id cn_payment_worksheets.role_id%TYPE := cn_api.g_miss_id,
138 x_credit_type_id cn_payment_worksheets.credit_type_id%TYPE := cn_api.g_miss_id,
139 x_returned_funds_flag cn_payment_worksheets.returned_funds_flag%TYPE := '~',
140 x_post_subledger_flag cn_payment_worksheets.post_subledger_flag%TYPE := '~',
141 x_pay_cap cn_payment_worksheets.pay_cap%TYPE := fnd_api.g_miss_num,
142 x_minimum_amount cn_payment_worksheets.minimum_amount%TYPE := fnd_api.g_miss_num,
143 x_comm_due_bb cn_payment_worksheets.comm_due_bb%TYPE := fnd_api.g_miss_num,
144 x_comm_ptd cn_payment_worksheets.comm_ptd%TYPE := fnd_api.g_miss_num,
145 x_draw_paid cn_payment_worksheets.draw_paid%TYPE := fnd_api.g_miss_num,
146 x_comm_nrec cn_payment_worksheets.comm_nrec%TYPE := fnd_api.g_miss_num,
147 x_comm_draw cn_payment_worksheets.comm_draw%TYPE := fnd_api.g_miss_num,
148 x_comm_paid cn_payment_worksheets.comm_paid%TYPE := fnd_api.g_miss_num,
149 x_reg_bonus_due_bb cn_payment_worksheets.reg_bonus_due_bb%TYPE := fnd_api.g_miss_num,
150 x_reg_bonus_ptd cn_payment_worksheets.reg_bonus_ptd%TYPE := fnd_api.g_miss_num,
151 x_reg_bonus_rec cn_payment_worksheets.reg_bonus_rec%TYPE := fnd_api.g_miss_num,
152 x_reg_bonus_to_rec cn_payment_worksheets.reg_bonus_to_rec%TYPE := fnd_api.g_miss_num,
153 x_reg_bonus_paid cn_payment_worksheets.reg_bonus_paid%TYPE := fnd_api.g_miss_num,
154 x_bonus_due_bb cn_payment_worksheets.bonus_due_bb%TYPE := fnd_api.g_miss_num,
155 x_bonus_ptd cn_payment_worksheets.bonus_ptd%TYPE := fnd_api.g_miss_num,
156 x_bonus_paid cn_payment_worksheets.bonus_paid%TYPE := fnd_api.g_miss_num,
157 x_payee_comm_due_bb cn_payment_worksheets.payee_comm_due_bb%TYPE := fnd_api.g_miss_num,
158 x_payee_comm_ptd cn_payment_worksheets.payee_comm_ptd%TYPE := fnd_api.g_miss_num,
159 x_payee_comm_paid cn_payment_worksheets.payee_comm_paid%TYPE := fnd_api.g_miss_num,
160 x_payee_bonus_due_bb cn_payment_worksheets.payee_bonus_due_bb%TYPE := fnd_api.g_miss_num,
161 x_payee_bonus_ptd cn_payment_worksheets.payee_bonus_ptd%TYPE := fnd_api.g_miss_num,
162 x_payee_bonus_paid cn_payment_worksheets.payee_bonus_paid%TYPE := fnd_api.g_miss_num,
163 x_convert_to_type_id cn_payment_worksheets.convert_to_type_id%TYPE := cn_api.g_miss_id,
164 x_credit_conv_fct_id cn_payment_worksheets.credit_conv_fct_id%TYPE := cn_api.g_miss_id,
165 x_convert_to_paid cn_payment_worksheets.convert_to_paid%TYPE := fnd_api.g_miss_num,
166 x_reviewed_by_analyst cn_payment_worksheets_all.reviewed_by_analyst%TYPE := fnd_api.g_miss_char,
167 x_analyst_notes cn_payment_worksheets_all.analyst_notes%TYPE := fnd_api.g_miss_char,
168 x_posting_status cn_payment_worksheets.posting_status%TYPE := fnd_api.g_miss_char,
169 x_draw_recoverable_begin cn_payment_worksheets.draw_recoverable_begin%TYPE := fnd_api.g_miss_num,
170 x_adjust_paid cn_payment_worksheets.adjust_paid%TYPE := fnd_api.g_miss_num,
171 x_bonus_draw cn_payment_worksheets.bonus_draw%TYPE := fnd_api.g_miss_num,
172 x_reason cn_payment_worksheets.reason%TYPE := fnd_api.g_miss_char,
173 x_bonus_reason cn_payment_worksheets.bonus_reason%TYPE := fnd_api.g_miss_char,
174 x_recovery_method cn_payment_worksheets.recovery_method%TYPE := fnd_api.g_miss_char,
175 x_draw_ptd cn_payment_worksheets.draw_ptd%TYPE := fnd_api.g_miss_num,
176 x_bonus_given cn_payment_worksheets.bonus_given%TYPE := fnd_api.g_miss_num,
177 x_guarantee cn_payment_worksheets.guarantee%TYPE := fnd_api.g_miss_num,
178 x_worksheet_status cn_payment_worksheets.worksheet_status%TYPE := fnd_api.g_miss_char,
179 x_last_update_date cn_payment_worksheets.last_update_date%TYPE,
180 x_last_updated_by cn_payment_worksheets.last_updated_by%TYPE,
181 x_last_update_login cn_payment_worksheets.last_update_login%TYPE
182 )
183 IS
184 l_payment_worksheet_id cn_payment_worksheets.payment_worksheet_id%TYPE;
185 l_payrun_id cn_payment_worksheets.payrun_id%TYPE;
186 l_salesrep_id cn_payment_worksheets.salesrep_id%TYPE;
187 l_cost_center_id cn_payment_worksheets.cost_center_id%TYPE;
188 l_role_id cn_payment_worksheets.role_id%TYPE;
189 l_credit_type_id cn_payment_worksheets.credit_type_id%TYPE;
190 l_returned_funds_flag cn_payment_worksheets.returned_funds_flag%TYPE;
191 l_post_subledger_flag cn_payment_worksheets.post_subledger_flag%TYPE;
192 l_pay_cap cn_payment_worksheets.pay_cap%TYPE;
193 l_minimum_amount cn_payment_worksheets.minimum_amount%TYPE;
194 l_comm_due_bb cn_payment_worksheets.comm_due_bb%TYPE;
195 l_comm_ptd cn_payment_worksheets.comm_ptd%TYPE;
196 l_draw_paid cn_payment_worksheets.draw_paid%TYPE;
197 l_comm_nrec cn_payment_worksheets.comm_nrec%TYPE;
198 l_comm_draw cn_payment_worksheets.comm_draw%TYPE;
199 l_comm_paid cn_payment_worksheets.comm_paid%TYPE;
200 l_reg_bonus_due_bb cn_payment_worksheets.reg_bonus_due_bb%TYPE;
201 l_reg_bonus_ptd cn_payment_worksheets.reg_bonus_ptd%TYPE;
202 l_reg_bonus_rec cn_payment_worksheets.reg_bonus_rec%TYPE;
203 l_reg_bonus_to_rec cn_payment_worksheets.reg_bonus_to_rec%TYPE;
204 l_reg_bonus_paid cn_payment_worksheets.reg_bonus_paid%TYPE;
205 l_bonus_due_bb cn_payment_worksheets.bonus_due_bb%TYPE;
206 l_bonus_ptd cn_payment_worksheets.bonus_ptd%TYPE;
207 l_bonus_paid cn_payment_worksheets.bonus_paid%TYPE;
208 l_payee_comm_due_bb cn_payment_worksheets.payee_comm_due_bb%TYPE;
209 l_payee_comm_ptd cn_payment_worksheets.payee_comm_ptd%TYPE;
210 l_payee_comm_paid cn_payment_worksheets.payee_comm_paid%TYPE;
211 l_payee_bonus_due_bb cn_payment_worksheets.payee_bonus_due_bb%TYPE;
212 l_payee_bonus_ptd cn_payment_worksheets.payee_bonus_ptd%TYPE;
213 l_payee_bonus_paid cn_payment_worksheets.payee_bonus_paid%TYPE;
214 l_convert_to_type_id cn_payment_worksheets.convert_to_type_id%TYPE;
215 l_credit_conv_fct_id cn_payment_worksheets.credit_conv_fct_id%TYPE;
216 l_convert_to_paid cn_payment_worksheets.convert_to_paid%TYPE;
217 l_posting_status cn_payment_worksheets.posting_status%TYPE;
218 l_draw_recoverable_begin cn_payment_worksheets.draw_recoverable_begin%TYPE;
219 l_adjust_paid cn_payment_worksheets.adjust_paid%TYPE;
220 l_bonus_draw cn_payment_worksheets.bonus_draw%TYPE;
221 l_reason cn_payment_worksheets.reason%TYPE;
222 l_bonus_reason cn_payment_worksheets.bonus_reason%TYPE;
223 l_recovery_method cn_payment_worksheets.recovery_method%TYPE;
224 l_draw_ptd cn_payment_worksheets.draw_ptd%TYPE;
225 l_bonus_given cn_payment_worksheets.bonus_given%TYPE;
226 l_guarantee cn_payment_worksheets.guarantee%TYPE;
227 l_worksheet_status cn_payment_worksheets.worksheet_status%TYPE;
228 l_reviewed_by_analyst cn_payment_worksheets.reviewed_by_analyst%TYPE;
229 l_analyst_notes cn_payment_worksheets.analyst_notes%TYPE;
230
231 CURSOR payment_worksheet_cur
232 IS
233 SELECT *
234 FROM cn_payment_worksheets
235 WHERE payment_worksheet_id = x_payment_worksheet_id;
236
237 l_payment_worksheet_rec payment_worksheet_cur%ROWTYPE;
238 BEGIN
239 OPEN payment_worksheet_cur;
240
241 FETCH payment_worksheet_cur
242 INTO l_payment_worksheet_rec;
243
244 CLOSE payment_worksheet_cur;
245
246 SELECT DECODE (x_salesrep_id, cn_api.g_miss_id, l_payment_worksheet_rec.salesrep_id, x_salesrep_id),
247 DECODE (x_cost_center_id, cn_api.g_miss_id, l_payment_worksheet_rec.cost_center_id, x_cost_center_id),
248 DECODE (x_role_id, cn_api.g_miss_id, l_payment_worksheet_rec.role_id, x_role_id),
249 DECODE (x_credit_type_id, cn_api.g_miss_id, l_payment_worksheet_rec.credit_type_id, x_credit_type_id),
250 DECODE (x_returned_funds_flag, '~', l_payment_worksheet_rec.returned_funds_flag, x_returned_funds_flag),
251 DECODE (x_post_subledger_flag, '~', l_payment_worksheet_rec.post_subledger_flag, x_post_subledger_flag),
252 DECODE (x_pay_cap, fnd_api.g_miss_num, l_payment_worksheet_rec.pay_cap, x_pay_cap),
253 DECODE (x_minimum_amount, fnd_api.g_miss_num, l_payment_worksheet_rec.minimum_amount, x_minimum_amount),
254 DECODE (x_comm_due_bb, fnd_api.g_miss_num, l_payment_worksheet_rec.comm_due_bb, x_comm_due_bb),
255 DECODE (x_comm_ptd, fnd_api.g_miss_num, l_payment_worksheet_rec.comm_ptd, x_comm_ptd),
256 DECODE (x_draw_paid, fnd_api.g_miss_num, l_payment_worksheet_rec.draw_paid, x_draw_paid),
257 DECODE (x_comm_nrec, fnd_api.g_miss_num, l_payment_worksheet_rec.comm_nrec, x_comm_nrec),
258 DECODE (x_comm_draw, fnd_api.g_miss_num, l_payment_worksheet_rec.comm_draw, x_comm_draw),
259 DECODE (x_comm_paid, fnd_api.g_miss_num, l_payment_worksheet_rec.comm_paid, x_comm_paid),
260 DECODE (x_reg_bonus_due_bb, fnd_api.g_miss_num, l_payment_worksheet_rec.reg_bonus_due_bb, x_reg_bonus_due_bb),
261 DECODE (x_reg_bonus_ptd, fnd_api.g_miss_num, l_payment_worksheet_rec.reg_bonus_ptd, x_reg_bonus_ptd),
262 DECODE (x_reg_bonus_rec, fnd_api.g_miss_num, l_payment_worksheet_rec.reg_bonus_rec, x_reg_bonus_rec),
266 DECODE (x_bonus_ptd, fnd_api.g_miss_num, l_payment_worksheet_rec.bonus_ptd, x_bonus_ptd),
263 DECODE (x_reg_bonus_to_rec, fnd_api.g_miss_num, l_payment_worksheet_rec.reg_bonus_to_rec, x_reg_bonus_to_rec),
264 DECODE (x_reg_bonus_paid, fnd_api.g_miss_num, l_payment_worksheet_rec.reg_bonus_paid, x_reg_bonus_paid),
265 DECODE (x_bonus_due_bb, fnd_api.g_miss_num, l_payment_worksheet_rec.bonus_due_bb, x_bonus_due_bb),
267 DECODE (x_bonus_paid, fnd_api.g_miss_num, l_payment_worksheet_rec.bonus_paid, x_bonus_paid),
268 DECODE (x_payee_comm_due_bb, fnd_api.g_miss_num, l_payment_worksheet_rec.payee_comm_due_bb, x_payee_comm_due_bb),
269 DECODE (x_payee_comm_ptd, fnd_api.g_miss_num, l_payment_worksheet_rec.payee_comm_ptd, x_payee_comm_ptd),
270 DECODE (x_payee_comm_paid, fnd_api.g_miss_num, l_payment_worksheet_rec.payee_comm_paid, x_payee_comm_paid),
271 DECODE (x_payee_bonus_due_bb, fnd_api.g_miss_num, l_payment_worksheet_rec.payee_bonus_due_bb, x_payee_bonus_due_bb),
272 DECODE (x_payee_bonus_ptd, fnd_api.g_miss_num, l_payment_worksheet_rec.payee_bonus_ptd, x_payee_bonus_ptd),
273 DECODE (x_payee_bonus_paid, fnd_api.g_miss_num, l_payment_worksheet_rec.payee_bonus_paid, x_payee_bonus_paid),
274 DECODE (x_convert_to_type_id, cn_api.g_miss_id, l_payment_worksheet_rec.convert_to_type_id, x_convert_to_type_id),
275 DECODE (x_credit_conv_fct_id, cn_api.g_miss_id, l_payment_worksheet_rec.credit_conv_fct_id, x_credit_conv_fct_id),
276 DECODE (x_convert_to_paid, fnd_api.g_miss_num, l_payment_worksheet_rec.convert_to_paid, x_convert_to_paid),
277 DECODE (x_reviewed_by_analyst, fnd_api.g_miss_char, l_payment_worksheet_rec.reviewed_by_analyst, x_reviewed_by_analyst),
278 DECODE (x_analyst_notes, fnd_api.g_miss_char, l_payment_worksheet_rec.analyst_notes, x_analyst_notes),
279 DECODE (x_posting_status, fnd_api.g_miss_char, l_payment_worksheet_rec.posting_status, x_posting_status),
280 DECODE (x_draw_recoverable_begin, fnd_api.g_miss_num, l_payment_worksheet_rec.draw_recoverable_begin, x_draw_recoverable_begin),
281 DECODE (x_adjust_paid, fnd_api.g_miss_num, l_payment_worksheet_rec.adjust_paid, x_adjust_paid),
282 DECODE (x_bonus_draw, fnd_api.g_miss_num, l_payment_worksheet_rec.bonus_draw, x_bonus_draw),
283 DECODE (x_reason, fnd_api.g_miss_char, l_payment_worksheet_rec.reason, x_reason),
284 DECODE (x_bonus_reason, fnd_api.g_miss_char, l_payment_worksheet_rec.bonus_reason, x_bonus_reason),
285 DECODE (x_recovery_method, fnd_api.g_miss_char, l_payment_worksheet_rec.recovery_method, x_recovery_method),
286 DECODE (x_draw_ptd, fnd_api.g_miss_num, l_payment_worksheet_rec.draw_ptd, x_draw_ptd),
287 DECODE (x_bonus_given, fnd_api.g_miss_num, l_payment_worksheet_rec.bonus_given, x_bonus_given),
288 DECODE (x_guarantee, fnd_api.g_miss_num, l_payment_worksheet_rec.guarantee, x_guarantee),
289 DECODE (x_worksheet_status, fnd_api.g_miss_char, l_payment_worksheet_rec.worksheet_status, x_worksheet_status)
290 INTO l_salesrep_id,
291 l_cost_center_id,
292 l_role_id,
293 l_credit_type_id,
294 l_returned_funds_flag,
295 l_post_subledger_flag,
296 l_pay_cap,
297 l_minimum_amount,
298 l_comm_due_bb,
299 l_comm_ptd,
300 l_draw_paid,
301 l_comm_nrec,
302 l_comm_draw,
303 l_comm_paid,
304 l_reg_bonus_due_bb,
305 l_reg_bonus_ptd,
306 l_reg_bonus_rec,
307 l_reg_bonus_to_rec,
308 l_reg_bonus_paid,
309 l_bonus_due_bb,
310 l_bonus_ptd,
311 l_bonus_paid,
312 l_payee_comm_due_bb,
313 l_payee_comm_ptd,
314 l_payee_comm_paid,
315 l_payee_bonus_due_bb,
316 l_payee_bonus_ptd,
317 l_payee_bonus_paid,
318 l_convert_to_type_id,
319 l_credit_conv_fct_id,
320 l_convert_to_paid,
321 l_reviewed_by_analyst,
322 l_analyst_notes,
323 l_posting_status,
324 l_draw_recoverable_begin,
325 l_adjust_paid,
326 l_bonus_draw,
327 l_reason,
328 l_bonus_reason,
329 l_recovery_method,
330 l_draw_ptd,
331 l_bonus_given,
332 l_guarantee,
333 l_worksheet_status
334 FROM DUAL;
335
336 UPDATE cn_payment_worksheets
337 SET salesrep_id = l_salesrep_id,
338 cost_center_id = l_cost_center_id,
339 role_id = l_role_id,
340 credit_type_id = l_credit_type_id,
341 returned_funds_flag = l_returned_funds_flag,
342 post_subledger_flag = l_post_subledger_flag,
343 pay_cap = l_pay_cap,
344 minimum_amount = l_minimum_amount,
345 comm_due_bb = l_comm_due_bb,
346 comm_ptd = l_comm_ptd,
347 draw_paid = l_draw_paid,
348 comm_nrec = l_comm_nrec,
349 comm_draw = l_comm_draw,
350 comm_paid = l_comm_paid,
351 reg_bonus_due_bb = l_reg_bonus_due_bb,
352 reg_bonus_ptd = l_reg_bonus_ptd,
353 reg_bonus_rec = l_reg_bonus_rec,
354 reg_bonus_to_rec = l_reg_bonus_to_rec,
358 bonus_paid = l_bonus_paid,
355 reg_bonus_paid = l_reg_bonus_paid,
356 bonus_due_bb = l_bonus_due_bb,
357 bonus_ptd = l_bonus_ptd,
359 payee_comm_due_bb = l_payee_comm_due_bb,
360 payee_comm_ptd = l_payee_comm_ptd,
361 payee_comm_paid = l_payee_comm_paid,
362 payee_bonus_due_bb = l_payee_bonus_due_bb,
363 payee_bonus_ptd = l_payee_bonus_ptd,
364 payee_bonus_paid = l_payee_bonus_paid,
365 convert_to_type_id = l_convert_to_type_id,
366 credit_conv_fct_id = l_credit_conv_fct_id,
367 convert_to_paid = l_convert_to_paid,
368 reviewed_by_analyst = l_reviewed_by_analyst,
369 analyst_notes = l_analyst_notes,
370 posting_status = l_posting_status,
371 draw_recoverable_begin = l_draw_recoverable_begin,
372 adjust_paid = l_adjust_paid,
373 bonus_draw = l_bonus_draw,
374 reason = l_reason,
375 bonus_reason = l_bonus_reason,
376 recovery_method = l_recovery_method,
377 draw_ptd = l_draw_ptd,
378 bonus_given = l_bonus_given,
379 guarantee = l_guarantee,
380 worksheet_status = l_worksheet_status,
381 last_update_date = x_last_update_date,
382 last_update_login = x_last_update_login,
383 last_updated_by = x_last_updated_by
384 WHERE payment_worksheet_id = x_payment_worksheet_id;
385
386 IF (SQL%NOTFOUND)
387 THEN
388 RAISE NO_DATA_FOUND;
389 END IF;
390 END UPDATE_RECORD;
391
392
393 --============================================================================
394 -- Procedure Name : Update_Record
395 -- Purpose : Update the Payment Worksheets ( batch Update )
396 -- : Called from Payment Transactions cnvpmtrb.pls
397 --============================================================================
398 PROCEDURE UPDATE_RECORD (
399 p_salesrep_id NUMBER,
400 p_payrun_id NUMBER,
401 p_quota_id NUMBER,
402 p_pmt_amount_calc NUMBER := 0,
403 p_pmt_amount_adj_rec NUMBER := 0,
404 p_pmt_amount_adj_nrec NUMBER := 0,
405 p_pmt_amount_recovery NUMBER := 0,
406 p_pmt_amount_adj NUMBER := 0,
407 x_object_version_number OUT NOCOPY cn_payment_worksheets.object_version_number%TYPE
408 )
409 IS
410 BEGIN
411 SELECT NVL(object_version_number,0) + 1
412 INTO x_object_version_number
413 FROM cn_payment_worksheets
414 WHERE salesrep_id = p_salesrep_id
415 AND payrun_id = p_payrun_id;
416
417 UPDATE cn_payment_worksheets
418 SET pmt_amount_adj_nrec = NVL (pmt_amount_adj_nrec, 0) + NVL (p_pmt_amount_adj_nrec, 0),
419 pmt_amount_adj_rec = NVL (pmt_amount_adj_rec, 0) + NVL (p_pmt_amount_adj_rec, 0),
420 pmt_amount_adj = NVL (pmt_amount_adj, 0) + NVL (p_pmt_amount_adj, 0),
421 pmt_amount_calc = NVL (pmt_amount_calc, 0) + NVL (p_pmt_amount_calc, 0),
422 pmt_amount_recovery = NVL (pmt_amount_recovery, 0) + NVL (p_pmt_amount_recovery, 0),
423 last_updated_by = fnd_global.user_id,
424 last_update_date = SYSDATE,
425 last_update_login = fnd_global.login_id,
426 object_version_number = x_object_version_number
427 WHERE salesrep_id = p_salesrep_id
428 AND payrun_id = p_payrun_id
429 AND (quota_id = p_quota_id OR quota_id IS NULL);
430 END UPDATE_RECORD;
431
432 --============================================================================
433 -- Procedure Name : Update_Record
434 -- Purpose : Update the Payment Worksheets ( batch Update )
435 -- : Called from Update Worksheets
436 --============================================================================
437 PROCEDURE UPDATE_STATUS (
438 p_salesrep_id NUMBER,
439 p_payrun_id NUMBER,
440 p_worksheet_status VARCHAR2
441 )
442 IS
443 BEGIN
444
445 UPDATE cn_payment_worksheets
446 SET worksheet_status = p_worksheet_status,
447 last_updated_by = fnd_global.user_id,
448 last_update_date = SYSDATE,
449 last_update_login = fnd_global.login_id,
450 object_version_number = nvl(object_version_number,0) + 1
451 WHERE salesrep_id = p_salesrep_id
452 AND payrun_id = p_payrun_id;
453
454 END UPDATE_STATUS;
455
456 --============================================================================
457 -- Procedure Name : Delete_Record
458 -- Purpose : Delete Worksheet
459 --============================================================================
460 PROCEDURE DELETE_RECORD (
461 p_payrun_id NUMBER,
462 p_salesrep_id NUMBER
463 )
464 IS
465 BEGIN
466 DELETE
467 FROM cn_payment_worksheets
468 WHERE salesrep_id = p_salesrep_id
469 AND payrun_id = p_payrun_id;
470
471 END DELETE_RECORD;
472
473 --============================================================================
474 -- Procedure Name : Delete_Record
475 -- Purpose : Delete the Payment Worksheets
479 )
476 --============================================================================
477 PROCEDURE DELETE_RECORD (
478 x_payment_worksheet_id NUMBER
480 IS
481 BEGIN
482 DELETE
483 FROM cn_payment_worksheets
484 WHERE payment_worksheet_id = x_payment_worksheet_id;
485
486 END DELETE_RECORD;
487
488
489 END cn_payment_worksheets_pkg;