1 PACKAGE BODY CN_NOTIFY_CLAWBACKS AS
2 -- $Header: cnnocbkb.pls 120.9 2006/02/16 21:23:21 rramakri noship $
3
4
5
6 -- Procedure Name
7 -- notify_clawbacks
8 -- Purpose
9 -- This procedure collects data for clawbacks for cn_not_trx
10 -- History
11 -- 01-05-96 A. Erickson Created
12
13
14 PROCEDURE notify (
15 x_start_period cn_periods.period_id%TYPE,
16 x_end_period cn_periods.period_id%TYPE,
17 debug_pipe VARCHAR2 DEFAULT NULL,
18 debug_level NUMBER DEFAULT NULL,
19 x_org_id NUMBER) IS
20
21 x_trx_count NUMBER;
22 x_proc_audit_id NUMBER;
23 x_start_date DATE;
24 x_end_date DATE;
25 x_clb_grace_period NUMBER;
26 x_start_due_date DATE;
27 x_end_due_date DATE;
28 x_rowid ROWID;
29 l_sys_batch_size NUMBER;
30
31 CURSOR batch_size IS SELECT system_batch_size FROM cn_repositories WHERE org_id = x_org_id;
32
33 BEGIN
34 IF (debug_pipe IS NOT NULL) THEN
35 cn_debug.init_pipe(debug_pipe, debug_level);
36 END IF;
37 cn_debug.print_msg('>>notify_clawbacks', 1);
38 -- who.set_program_name('notify_clawbacks');
39
40 cn_message_pkg.debug('notify_clawbacks>>');
41 fnd_file.put_line(fnd_file.Log, '>>notify_clawbacks');
42
43 x_proc_audit_id := NULL; -- Will get a value in the call below
44 cn_process_audits_pkg.insert_row(x_rowid, x_proc_audit_id, NULL,
45 'NOT', 'Notification run', NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
46
47 cn_periods_api.set_dates(x_start_period, x_end_period, x_org_id,
48 x_start_date, x_end_date);
49
50 -- Set end_due_date for clawbacks. 02-26-96
51 -- This is the end_date minus the clawback grace period.
52
53 --bug 513940 -J.C
54 SELECT r.clawback_grace_days
55 INTO x_clb_grace_period
56 FROM cn_periods p
57 ,cn_repositories r
58 ,gl_sets_of_books s
59 WHERE r.current_period_id = p.period_id(+)
60 AND r.application_id = 283
61 AND r.set_of_books_id = s.set_of_books_id
62 AND r.org_id = x_org_id
63 AND r.org_id=p.org_id(+);
64
65 x_start_due_date := x_start_date - nvl(x_clb_grace_period, cn_global.cbk_grace_period);
66 x_end_due_date := x_end_date - nvl(x_clb_grace_period,cn_global.cbk_grace_period);
67
68
69 -- Insert notification records for clawbacks for those payment schedules
70 -- that went past due between the start date of the start period and the
71 -- end date of the end period.
72 cn_debug.print_msg('notify_clawbacks: Collecting from ar_payment_schedules', 1);
73 fnd_file.put_line(fnd_file.Log, 'notify_clawbacks: Collecting from ar_payment_schedules');
74
75 -- Note: Here we are looking for payment schedules against transactions
76 -- that would have initially been picked up by the invoice notification
77 -- code= cnnoinvb.pls.
78
79 cn_message_pkg.debug('notify_clawbacks: Is collecting clawbacks for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
80 fnd_file.put_line(fnd_file.Log, 'notify_clawbacks: Is collecting clawbacks for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
81
82 OPEN batch_size;
83 FETCH batch_size INTO l_sys_batch_size;
84 CLOSE batch_size;
85
86
87 INSERT INTO cn_not_trx (
88 not_trx_id,
89 batch_id,
90 notified_date,
91 processed_date,
92 notification_run_id,
93 collected_flag,
94 row_id,
95 source_trx_id,
96 source_doc_type,
97 event_id,
98 org_id)
99 SELECT
100 cn_not_trx_s.NEXTVAL,
101 FLOOR(cn_not_trx_s.CURRVAL/l_sys_batch_size),
102 SYSDATE,
103 x_end_date,
104 x_proc_audit_id,
105 'N',
106 aps.rowid,
107 aps.payment_schedule_id,
108 'AR',
109 cn_global.cbk_event_id,
110 x_org_id
111 FROM ar_payment_schedules aps,
112 ra_customer_trx rct,
113 ra_cust_trx_types rctt,
114 ra_cust_trx_line_gl_dist rctlgd,
115 cn_repositories cr
116 WHERE aps.due_date BETWEEN x_start_due_date AND x_end_due_date
117 AND aps.amount_line_items_remaining > 0
118 --AE AND aps.class in ('INV', 'CM') --AE 02-06-96
119 AND aps.customer_trx_id = rct.customer_trx_id --AE 02-07-96
120 AND rct.customer_trx_id = rctlgd.customer_trx_id
121 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
122 AND rct.complete_flag = 'Y'
123 AND rctt.type in ('INV', 'CM')
124 AND rctlgd.account_class = 'REC'
125 AND rctlgd.latest_rec_flag = 'Y'
126 AND rctlgd.posting_control_id <> -3
127 AND rct.set_of_books_id = cr.set_of_books_id --AE 02-21-96
128 AND cr.repository_id = 100 --AE 02-21-96
129 AND aps.org_id = x_org_id
130 AND rct.org_id = aps.org_id
131 AND rctt.org_id = rct.org_id
132 AND rctlgd.org_id = rctt.org_id
133 AND cr.org_id = rctlgd.org_id
134 AND NOT EXISTS (
135 SELECT 1
136 FROM cn_not_trx
137 WHERE source_trx_id = aps.payment_schedule_id
138 AND event_id= cn_global.cbk_event_id
139 AND org_id = x_org_id) ;
140
141
142 x_trx_count := SQL%ROWCOUNT;
143
144 cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, 0,
145 'Finished notification run: Notified ' || x_trx_count || ' clawbacks.');
146
147 IF ( x_trx_count = 0 ) THEN
148
149 cn_message_pkg.debug('notify_clawbacks: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to GL or they have already been collected.');
150 fnd_file.put_line(fnd_file.Log, 'notify_clawbacks: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to GL or they have already been collected.');
151
152 END IF;
153
154 COMMIT;
155
156 cn_message_pkg.debug('notify_clawbacks: Finished notification run: Notified ' || x_trx_count || ' clawbacks.');
157 fnd_file.put_line(fnd_file.Log, 'notify_clawbacks: Finished notification run: Notified ' || x_trx_count || ' clawbacks.');
158
159 cn_debug.print_msg('<<notify_clawbacks', 1);
160
161 cn_message_pkg.debug('notify_clawbacks<<');
162 fnd_file.put_line(fnd_file.Log, 'notify_clawbacks<<');
163
164 cn_message_pkg.end_batch (x_proc_audit_id);
165
166 EXCEPTION
167 WHEN OTHERS THEN ROLLBACK;
168
169 cn_message_pkg.debug('notify_clawbacks: in exception handler');
170 fnd_file.put_line(fnd_file.Log, 'notify_clawbacks: in exception handler');
171
172 cn_message_pkg.debug(SQLCODE||' '||SQLERRM);
173 fnd_file.put_line(fnd_file.Log, SQLCODE||' '||SQLERRM);
174
175 cn_debug.print_msg('notify_clawbacks: in exception handler', 1);
176 cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
177 SQLERRM);
178 cn_message_pkg.end_batch (x_proc_audit_id);
179
180 app_exception.raise_exception;
181
182 END notify;
183
184 END cn_notify_clawbacks;
185
186
187
[Home] [Help]