[Home] [Help]
PACKAGE BODY: APPS.CN_NOTIFY_PAYMENTS
Source
1 PACKAGE BODY CN_NOTIFY_PAYMENTS AS
2 -- $Header: cnnopmtb.pls 120.7 2006/02/01 02:52:55 rramakri noship $
3
4
5
6
7 -- Procedure Name
8 -- notify_payments
9 -- Purpose
10 -- This procedure collects data for payments 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 BEGIN
33 IF (debug_pipe IS NOT NULL) THEN
34 cn_debug.init_pipe(debug_pipe, debug_level);
35 END IF;
36 cn_debug.print_msg('>>notify_payments', 1);
37 -- who.set_program_name('notify_payments');
38
39 cn_message_pkg.debug('notify_payments>>');
40 fnd_file.put_line(fnd_file.Log, 'notify_payments>>');
41
42 x_proc_audit_id := NULL; -- Will get a value in the call below
43 cn_process_audits_pkg.insert_row(x_rowid, x_proc_audit_id, NULL,
44 'NOT', 'Notification run', NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
45
46 cn_periods_api.set_dates(x_start_period, x_end_period, x_org_id,
47 x_start_date, x_end_date);
48
49 cn_message_pkg.debug('notify_payments: Is collecting payments for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
50 fnd_file.put_line(fnd_file.Log, 'notify_payments: Is collecting payments for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
51
52 OPEN batch_size;
53 FETCH batch_size INTO l_sys_batch_size;
54 CLOSE batch_size;
55
56
57 INSERT INTO cn_not_trx (
58 not_trx_id,
59 batch_id,
60 notified_date,
61 processed_date,
62 notification_run_id,
63 collected_flag,
64 row_id,
65 source_trx_id,
66 source_doc_type,
67 event_id,
68 org_id)
69 SELECT
70 cn_not_trx_s.NEXTVAL,
71 FLOOR(cn_not_trx_s.CURRVAL/l_sys_batch_size),
72 SYSDATE,
73 ara.gl_date, --AE 02-22-96
74 x_proc_audit_id,
75 'N',
76 ara.rowid,
77 ara.receivable_application_id,
78 'AR',
79 cn_global.pmt_event_id,
80 x_org_id
81 FROM ar_receivable_applications ara,
82 cn_repositories cr
83 WHERE ara.application_type = 'CASH'
84 AND ara.status = 'APP'
85 AND ara.gl_date BETWEEN x_start_date AND x_end_date
86 AND ara.posting_control_id <> -3 --AE 02-22-96
87 AND ara.set_of_books_id = cr.set_of_books_id --AE 02-21-96
88 AND cr.repository_id = 100 --AE 02-21-96
89 AND ara.org_id = x_org_id
90 AND cr.org_id = ara.org_id
91 AND NOT EXISTS (
92 SELECT 1
93 FROM cn_not_trx
94 WHERE source_trx_id = ara.receivable_application_id
95 AND event_id= cn_global.pmt_event_id
96 AND org_id = x_org_id) ;
97
98
99 x_trx_count := SQL%ROWCOUNT;
100
101 cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, 0,
102 'Finished notification run: Notified ' || x_trx_count || ' payments.');
103
104 IF ( x_trx_count = 0 ) THEN
105
106 cn_message_pkg.debug('notify_payments: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to GL and gl_date is null.');
107 fnd_file.put_line(fnd_file.Log, 'notify_payments: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to GL and gl_date is null.');
108
109
110 END IF;
111
112 COMMIT;
113
114 cn_message_pkg.debug('notify_payments: Finished notification run: Notified ' || x_trx_count || ' payments.');
115 fnd_file.put_line(fnd_file.Log, 'notify_payments: Finished notification run: Notified ' || x_trx_count || ' payments.');
116
117 cn_debug.print_msg('<<notify_payments', 1);
118
119 cn_message_pkg.debug('notify_payments<<');
120 fnd_file.put_line(fnd_file.Log, 'notify_payments<<');
121
122 cn_message_pkg.end_batch (x_proc_audit_id);
123
124 EXCEPTION
125 WHEN OTHERS THEN ROLLBACK;
126 cn_debug.print_msg('notify_payments: in exception handler', 1);
127 cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
128 SQLERRM);
129
130 cn_message_pkg.debug('notify_payments: in exception handler');
131 fnd_file.put_line(fnd_file.Log, 'notify_payments: in exception handler');
132
133 cn_message_pkg.debug(SQLCODE||' '||SQLERRM);
134 fnd_file.put_line(fnd_file.Log, SQLCODE||' '||SQLERRM);
135
136 cn_message_pkg.end_batch (x_proc_audit_id);
137
138 app_exception.raise_exception;
139
140 END notify;
141
142 END cn_notify_payments;
143
144