[Home] [Help]
PACKAGE BODY: APPS.CN_NOTIFY_INVOICES
Source
1 PACKAGE BODY CN_NOTIFY_INVOICES AS
2 -- $Header: cnnoinvb.pls 120.8 2006/02/01 10:17:28 rramakri noship $
3
4
5
6
7 -- Procedure Name
8 -- notify_invoices
9 -- Purpose
10 -- This procedure collects data for invoices for cn_not_trx
11 -- History
12 -- 01-05-96 A. Erickson Created
13
14
15 PROCEDURE notify (
16 x_start_period cn_periods.period_id%TYPE,
17 x_end_period cn_periods.period_id%TYPE,
18 debug_pipe VARCHAR2 DEFAULT NULL,
19 debug_level NUMBER DEFAULT NULL,
20 x_org_id NUMBER ) IS
21
22 x_trx_count NUMBER;
23 x_proc_audit_id NUMBER;
24 x_start_date DATE;
25 x_end_date DATE;
26 x_rowid ROWID;
27 l_sys_batch_size NUMBER;
28
29 CURSOR batch_size IS SELECT system_batch_size FROM cn_repositories WHERE org_id = x_org_id;
30
31
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_invoices', 1);
38 -- who.set_program_name('notify_invoices');
39
40
41 cn_message_pkg.debug('notify_invoices>>');
42 fnd_file.put_line(fnd_file.Log, 'notify_invoices>>');
43
44
45 x_proc_audit_id := NULL; -- Will get a value in the call below
46 cn_process_audits_pkg.insert_row(x_rowid, x_proc_audit_id, NULL, 'NOT', 'Notification run', NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
47
48 cn_periods_api.set_dates(x_start_period, x_end_period, x_org_id,
49 x_start_date, x_end_date);
50
51 cn_message_pkg.debug('notify_invoices: Is collecting invoices for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
52 fnd_file.put_line(fnd_file.Log, 'notify_invoices: Is collecting invoices for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
53
54
55 -- Check if the profile option CN_COLLECT_ON_ACCT_CREDITS is turned on
56 -- or not
57 OPEN batch_size;
58 FETCH batch_size INTO l_sys_batch_size;
59 CLOSE batch_size;
60
61 IF CN_SYSTEM_PARAMETERS.value('CN_COLLECT_ON_ACCT_CREDITS', x_org_id) = 'Y' THEN
62
63 -- We removed the constraint of "prev_cust_trx_id IS NOT NULL" for
64 -- collecting On Account Credits purpose. So basicly, we do not
65 -- distinguish among INV, CM or On Account Credits here. We will be
66 -- collecting all of these three when we collect invoices.
67
68 INSERT INTO cn_not_trx (
69 not_trx_id,
70 batch_id,
71 notified_date,
72 processed_date,
73 notification_run_id,
74 collected_flag,
75 row_id,
76 source_trx_id,
77 source_doc_type,
78 event_id,
79 org_id)
80 SELECT
81 cn_not_trx_s.NEXTVAL,
82 FLOOR(cn_not_trx_s.CURRVAL/l_sys_batch_size),
83 SYSDATE,
84 rctlgd.gl_date, --AE 02-22-96
85 x_proc_audit_id,
86 'N',
87 rct.rowid,
88 rct.customer_trx_id,
89 'AR',
90 cn_global.inv_event_id,
91 x_org_id
92 FROM ra_customer_trx rct,
93 ra_cust_trx_types rctt,
94 ra_cust_trx_line_gl_dist rctlgd,
95 cn_repositories cr
96 WHERE rct.customer_trx_id = rctlgd.customer_trx_id
97 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
98 AND rct.complete_flag = 'Y'
99 AND rctt.type in ('INV', 'CM','DM')
100 AND rctlgd.account_class = 'REC'
101 AND rctlgd.latest_rec_flag = 'Y'
102 AND rctlgd.gl_date BETWEEN x_start_date AND x_end_date
103 AND rctlgd.posting_control_id <> -3 --AE 02-22-96
104 AND rct.set_of_books_id = cr.set_of_books_id --AE 02-21-96
105 AND cr.repository_id = 100 --AE 02-21-96
106 AND rct.org_id = x_org_id
107 AND rctt.org_id = rct.org_id
108 AND rctlgd.org_id = rctt.org_id
109 AND cr.org_id = rctlgd.org_id
110 AND NOT EXISTS (
111 SELECT 1
112 FROM cn_not_trx
113 WHERE source_trx_id = rct.customer_trx_id
114 AND event_id= cn_global.inv_event_id
115 AND org_id = x_org_id) ;
116
117 ELSE -- CN_COLLECT_ON_ACCT_CREDITS = 'N'
118
119 INSERT INTO cn_not_trx (
120 not_trx_id,
121 batch_id,
122 notified_date,
123 processed_date,
124 notification_run_id,
125 collected_flag,
126 row_id,
127 source_trx_id,
128 source_doc_type,
129 event_id,
130 org_id)
131 SELECT
132 cn_not_trx_s.NEXTVAL,
133 FLOOR(cn_not_trx_s.CURRVAL/l_sys_batch_size),
134 SYSDATE,
135 rctlgd.gl_date, --AE 02-22-96
136 x_proc_audit_id,
137 'N',
138 rct.rowid,
139 rct.customer_trx_id,
140 'AR',
141 cn_global.inv_event_id,
142 x_org_id
143 FROM ra_customer_trx rct,
144 ra_cust_trx_types rctt,
145 ra_cust_trx_line_gl_dist rctlgd,
146 cn_repositories cr
147 WHERE rct.customer_trx_id = rctlgd.customer_trx_id
148 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
149 AND rct.complete_flag = 'Y'
150 --AE AND rctt.type in ('INV', 'CM','DM')
151 AND ((rctt.type IN ('INV','DM')) OR --AE 07-29-96
152 (rctt.type = 'CM' AND --AE
153 rct.previous_customer_trx_id IS NOT NULL)) --AE
154 AND rctlgd.account_class = 'REC'
155 AND rctlgd.latest_rec_flag = 'Y'
156 AND rctlgd.gl_date BETWEEN x_start_date AND x_end_date
157 AND rctlgd.posting_control_id <> -3 --AE 02-22-96
158 AND rct.set_of_books_id = cr.set_of_books_id --AE 02-21-96
159 AND cr.repository_id = 100 --AE 02-21-96
160 AND rct.org_id = x_org_id
161 AND rctt.org_id = rct.org_id
162 AND rctlgd.org_id = rctt.org_id
163 AND cr.org_id = rctlgd.org_id
164 AND NOT EXISTS (
165 SELECT 1
166 FROM cn_not_trx
167 WHERE source_trx_id = rct.customer_trx_id
168 AND event_id= cn_global.inv_event_id
169 AND org_id = x_org_id) ;
170
171 END IF;
172
173 x_trx_count := SQL%ROWCOUNT;
174
175 cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, 0,
176 'Finished notification run: Notified ' || x_trx_count || ' invoices.');
177
178
179
180 IF ( x_trx_count = 0 ) THEN
181
182 cn_message_pkg.debug('notify_invoices: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to GL or they have already been collected.');
183 fnd_file.put_line(fnd_file.Log, 'notify_invoices: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to GL or they have already been collected.');
184
185 END IF;
186
187 COMMIT;
188
189 cn_message_pkg.debug('notify_invoices: Finished notification run: Notified ' || x_trx_count || ' invoices.');
190 fnd_file.put_line(fnd_file.Log, 'notify_invoices: Finished notification run: Notified ' || x_trx_count || ' invoices.');
191
192 cn_debug.print_msg('<<notify_invoices', 1);
193 cn_message_pkg.debug('notify_invoices<<');
194 fnd_file.put_line(fnd_file.Log, 'notify_invoices<<');
195
196 -- cn_message_pkg.end_batch (x_proc_audit_id);
197
198
199 EXCEPTION
200 WHEN OTHERS THEN ROLLBACK;
201
202 cn_message_pkg.debug('notify_invoices: in exception handler');
203 fnd_file.put_line(fnd_file.Log, 'notify_invoices: in exception handler');
204
205 cn_message_pkg.debug(SQLCODE||' '||SQLERRM);
206 fnd_file.put_line(fnd_file.Log, SQLCODE||' '||SQLERRM);
207
208 cn_debug.print_msg('notify_invoices: in exception handler', 1);
209 cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
210 SQLERRM);
211 -- cn_message_pkg.end_batch (x_proc_audit_id);
212
213 app_exception.raise_exception;
214
215 END notify;
216
217 END cn_notify_invoices;
218
219
220