[Home] [Help]
PACKAGE BODY: APPS.CN_NOTIFY_WRITEOFFS
Source
1 PACKAGE BODY CN_NOTIFY_WRITEOFFS AS
2 -- $Header: cnnowob.pls 120.8 2006/02/01 10:16:41 rramakri noship $
3
4
5
6 -- Procedure Name
7 -- notify_writeoffs
8 -- Purpose
9 -- This procedure collects data for writeoffs 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_rowid ROWID;
26 l_sys_batch_size NUMBER;
27
28 CURSOR batch_size IS SELECT system_batch_size FROM cn_repositories WHERE org_id = x_org_id;
29
30
31 BEGIN
32 IF (debug_pipe IS NOT NULL) THEN
33 cn_debug.init_pipe(debug_pipe, debug_level);
34 END IF;
35 cn_debug.print_msg('>>notify_writeoffs', 1);
36 -- who.set_program_name('notify_writeoffs');
37
38 cn_message_pkg.debug('notify_writeoffs>>');
39 fnd_file.put_line(fnd_file.Log, 'notify_writeoffs>>');
40
41 x_proc_audit_id := NULL; -- Will get a value in the call below
42 cn_process_audits_pkg.insert_row(x_rowid, x_proc_audit_id, NULL,
43 'NOT', 'Notification run', NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
44
45 cn_periods_api.set_dates(x_start_period, x_end_period, x_org_id,
46 x_start_date, x_end_date);
47
48 cn_message_pkg.debug('notify_writeoffs: Is collecting writeoffs for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
49 fnd_file.put_line(fnd_file.Log, 'notify_writeoffs: Is collecting writeoffs for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
50
51 OPEN batch_size;
52 FETCH batch_size INTO l_sys_batch_size;
53 CLOSE batch_size;
54
55
56 INSERT INTO cn_not_trx (
57 not_trx_id,
58 batch_id,
59 notified_date,
60 processed_date,
61 notification_run_id,
62 collected_flag,
63 row_id,
64 source_trx_id,
65 source_doc_type,
66 event_id,
67 org_id)
68 SELECT
69 cn_not_trx_s.NEXTVAL,
70 FLOOR(cn_not_trx_s.CURRVAL/l_sys_batch_size),
71 SYSDATE,
72 aa.gl_date, --AE 02-22-96
73 x_proc_audit_id,
74 'N',
75 aa.rowid,
76 aa.adjustment_id,
77 'AR',
78 cn_global.wo_event_id,
79 x_org_id
80 FROM ar_adjustments aa,
81 cn_repositories cr
82 WHERE aa.type in ('LINE', 'INVOICE') --AE 02-22-96
83 AND aa.status = 'A' --AE 02-23-96
84 AND aa.line_adjusted is not NULL --AE 02-06-96
85 AND aa.gl_date BETWEEN x_start_date AND x_end_date
86 AND aa.posting_control_id <> -3 --AE 02-22-96
87 AND aa.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 aa.org_id = x_org_id
90 AND cr.org_id = aa.org_id
91 AND NOT EXISTS (
92 SELECT 1
93 FROM cn_not_trx
94 WHERE source_trx_id = aa.adjustment_id
95 AND event_id= cn_global.wo_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 || ' writeoffs.');
103
104 IF ( x_trx_count = 0 ) THEN
105
106 cn_message_pkg.debug('notify_writeoffs: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to GL or they have already been collected.');
107 fnd_file.put_line(fnd_file.Log, 'notify_writeoffs: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to GL or they have already been collected.');
108
109 END IF;
110
111 COMMIT;
112
113 cn_message_pkg.debug('notify_writeoffs: Finished notification run: Notified ' || x_trx_count || ' writeoffs.');
114 fnd_file.put_line(fnd_file.Log, 'notify_writeoffs: Finished notification run: Notified ' || x_trx_count || ' writeoffs.');
115
116 cn_debug.print_msg('<<notify_writeoffs', 1);
117
118 cn_message_pkg.debug('notify_writeoffs<<');
119 fnd_file.put_line(fnd_file.Log, 'notify_writeoffs<<');
120
121 cn_message_pkg.end_batch (x_proc_audit_id);
122
123 EXCEPTION
124 WHEN OTHERS THEN ROLLBACK;
125
126 cn_message_pkg.debug('notify_writeoffs: in exception handler');
127 fnd_file.put_line(fnd_file.Log, 'notify_writeoffs: in exception handler');
128
129 cn_message_pkg.debug(SQLCODE||' '||SQLERRM);
130 fnd_file.put_line(fnd_file.Log, SQLCODE||' '||SQLERRM);
131
132 cn_debug.print_msg('notify_writeoffs: in exception handler', 1);
133 cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
134 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_writeoffs;
143
144