[Home] [Help]
PACKAGE BODY: APPS.CN_NOTIFY_AIA_OM
Source
1 PACKAGE BODY cn_notify_aia_om AS
2 -- $Header: CNNOAIAOMB.pls 120.1 2010/10/26 10:39:53 sseshaiy noship $
3
4
5
6
7 -- Procedure Name
8 -- notify_aia_om
9 -- Purpose
10 -- This procedure collects data for aia order records for cn_not_trx
11 -- History
12 --
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_aia_om', 1);
37 -- who.set_program_name('notify_aia_om');
38
39 cn_message_pkg.debug('notify_aia_om>>');
40 fnd_file.put_line(fnd_file.Log, 'notify_aia_om>>');
41 --
42 x_proc_audit_id := NULL; -- Will get a value in the call below
43 cn_process_audits_pkg.insert_row(x_rowid,
44 x_proc_audit_id,
45 NULL,
46 'NOT',
47 'Notification run',
48 NULL,
49 NULL,
50 NULL,
51 NULL,
52 NULL,
53 SYSDATE,
54 NULL,
55 x_org_id);
56 --
57 cn_periods_api.set_dates(x_start_period,
58 x_end_period,
59 x_org_id,
60 x_start_date,
61 x_end_date);
62 --
63 cn_message_pkg.debug('notify_aia_om: Is collecting aia order records for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
64 fnd_file.put_line(fnd_file.Log, 'notify_aia_om: Is collecting aia order records for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
65 --
66 cn_message_pkg.debug('notify_aia_om: Is collecting aia order records for notification batch id '|| x_proc_audit_id ||'.');
67 fnd_file.put_line(fnd_file.Log, 'notify_aia_om: Is collecting aia order records for notification batch id '|| x_proc_audit_id ||'.');
68
69
70 OPEN batch_size;
71 FETCH batch_size INTO l_sys_batch_size;
72 CLOSE batch_size;
73
74
75 INSERT INTO cn_not_trx (
76 not_trx_id,
77 batch_id,
78 processed_date,
79 notified_date,
80 notification_run_id,
81 collected_flag,
82 event_id,
83 source_trx_id,
84 source_trx_line_id,
85 source_doc_type,
86 org_id)
87 SELECT
88 cn_not_trx_s.NEXTVAL,
89 cnot.t_batch_id,
90 cnot.t_processed_date,
91 cnot.t_notified_date,
92 cnot.t_notification_run_id,
93 cnot.t_collected_flag,
94 cnot.t_event_id,
95 cnot.t_source_trx_id, --*** Header.Primary_Key
96 cnot.t_source_trx_line_id, --*** Line Table Key Column
97 cnot.t_source_doc_type, --*** Source Type
98 cnot.t_org_id
99 FROM
100 (SELECT
101 distinct
102 x_proc_audit_id t_batch_id,
103 processed_date t_processed_date,
104 SYSDATE t_notified_date,
105 x_proc_audit_id t_notification_run_id,
106 'N' t_collected_flag,
107 -1030 t_event_id,
108 null t_source_trx_id, --*** Header.Primary_Key
109 cco10145.trans_seq_id t_source_trx_line_id, --*** Line Table Key Column
110 'AIA_OM' t_source_doc_type, --*** Source Type
111 x_org_id t_org_id
112 FROM
113 cn_aia_order_capture cco10145
114 WHERE --*** Header.Primary_Key = Line.Foreign_Key
115 1 = 1
116 AND TRUNC(processed_date) BETWEEN x_start_date AND x_end_date
117 AND preprocess_flag = FND_API.G_FALSE
118 AND org_id = x_org_id
119 AND NOT EXISTS (
120 SELECT 1
121 FROM cn_not_trx
122 WHERE source_trx_line_id = cco10145.trans_seq_id --*** Line.Primary_Key
123 AND event_id = -1030
124 AND org_id = x_org_id)
125 ) cnot;
126 --END Notification Insert Block
127
128
129 x_trx_count := SQL%ROWCOUNT;
130
131 cn_process_audits_pkg.update_row(x_proc_audit_id,
132 NULL,
133 SYSDATE,
134 0,
135 'Finished notification run: Notified ' || x_trx_count || ' aia order records.');
136
137 IF ( x_trx_count = 0 ) THEN
138
139 cn_message_pkg.debug('notify_aia_om: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to AIA Interface table and processed date is null.');
140 fnd_file.put_line(fnd_file.Log, 'notify_aia_om: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to AIA Interface table and processed date is null.');
141
142
143 END IF;
144
145 COMMIT;
146
147 cn_message_pkg.debug('notify_aia_om: Finished notification run: Notified ' || x_trx_count || ' aia order records.');
148 fnd_file.put_line(fnd_file.Log, 'notify_aia_om: Finished notification run: Notified ' || x_trx_count || ' aia order records.');
149
150 cn_debug.print_msg('<<notify_aia_om', 1);
151
152 cn_message_pkg.debug('notify_aia_om<<');
153 fnd_file.put_line(fnd_file.Log, 'notify_aia_om<<');
154
155 cn_message_pkg.end_batch (x_proc_audit_id);
156
157 EXCEPTION
158 WHEN OTHERS THEN ROLLBACK;
159 cn_debug.print_msg('notify_aia_om: in exception handler', 1);
160 cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
161 SQLERRM);
162
163 cn_message_pkg.debug('notify_aia_om: in exception handler');
164 fnd_file.put_line(fnd_file.Log, 'notify_aia_om: in exception handler');
165
166 cn_message_pkg.debug(SQLCODE||' '||SQLERRM);
167 fnd_file.put_line(fnd_file.Log, SQLCODE||' '||SQLERRM);
168
169 cn_message_pkg.end_batch (x_proc_audit_id);
170
171 app_exception.raise_exception;
172
173 END notify;
174
175 -- Procedure Name
176 -- notify_failed_trx
177 -- Purpose
178 -- This procedure collects failed records for aia order for cn_not_trx
179 -- History
180 --
181
182 PROCEDURE notify_failed_trx (
183 p_batch_id cn_not_trx_all.batch_id%TYPE,
184 x_start_period cn_periods.period_id%TYPE,
185 x_end_period cn_periods.period_id%TYPE,
186 debug_pipe VARCHAR2 DEFAULT NULL,
187 debug_level NUMBER DEFAULT 1,
188 x_org_id NUMBER ) IS
189
190 CURSOR get_failed_trx_cr(p_start_date Date, p_end_date Date) IS
191 SELECT distinct original_order_number, ln_num
192 FROM CN_AIA_ORDER_CAPTURE
193 WHERE org_id = x_org_id and trans_seq_id in
194 ( SELECT source_trx_line_id
195 FROM cn_not_trx_all
196 WHERE batch_id = p_batch_id and org_id = x_org_id and source_trx_line_id not in
197 (SELECT source_trx_line_id
198 FROM cn_comm_lines_api_all
199 WHERE process_batch_id = p_batch_id and org_id = x_org_id)
200 ) AND TRUNC(processed_date) BETWEEN p_start_date AND p_end_date;
201
202 type fl_trx_Ord_tbl_type IS TABLE OF get_failed_trx_cr % rowtype INDEX BY pls_integer;
203 fl_trx_Ord_tbl fl_trx_Ord_tbl_type;
204
205 x_trx_count NUMBER;
206 x_start_date DATE;
207 x_end_date DATE;
208
209
210 BEGIN
211 IF (debug_pipe IS NOT NULL) THEN
212 cn_debug.init_pipe(debug_pipe, debug_level);
213 END IF;
214 cn_debug.print_msg('>>notify_aia_om : notify_failed_trx', 1);
215
216 cn_message_pkg.debug('notify_aia_om : notify_failed_trx>>');
217 fnd_file.put_line(fnd_file.Log, 'notify_aia_om : notify_failed_trx>>');
218
219 --
220 cn_periods_api.set_dates(x_start_period,
221 x_end_period,
222 x_org_id,
223 x_start_date,
224 x_end_date);
225
226 --
227 cn_message_pkg.debug('notify_aia_om: Is collecting failed aia orders records which exist in CN_NOT_TRX but not collected
228 in CN_COMM_LINES_API from period '||x_start_date ||' to period '||x_end_date ||'.');
229 fnd_file.put_line(fnd_file.Log, 'notify_aia_om: Is collecting failed aia orders records which exist in CN_NOT_TRX but not collected
230 in CN_COMM_LINES_API from period '||x_start_date ||' to period '||x_end_date ||'.');
231
232 --
233
234 cn_message_pkg.debug('notify_aia_om: Is collecting failed aia order records for notification batch id '|| p_batch_id ||'.');
235 fnd_file.put_line(fnd_file.Log, 'notify_aia_om: Is collecting failed aia order records for notification batch id '|| p_batch_id ||'.');
236
237
238 OPEN get_failed_trx_cr(x_start_date,x_end_date);
239
240 LOOP
241 FETCH get_failed_trx_cr bulk collect
242 INTO fl_trx_Ord_tbl limit 1000;
243
244 FOR indx IN 1 .. fl_trx_Ord_tbl.COUNT
245 LOOP
246
247 cn_message_pkg.debug('notify_aia_om : notify_failed_trx : Original_order_number : ' || fl_trx_Ord_tbl(indx).original_order_number ||
248 ' : Line Number : ' || fl_trx_Ord_tbl(indx).ln_num);
249 fnd_file.put_line(fnd_file.Log, 'notify_aia_om :notify_failed_trx : Original_order_number : ' || fl_trx_Ord_tbl(indx).original_order_number ||
250 ' : Line Number : ' || fl_trx_Ord_tbl(indx).ln_num);
251
252 END LOOP;
253
254 EXIT
255 WHEN get_failed_trx_cr % NOTFOUND;
256 END LOOP;
257
258 CLOSE get_failed_trx_cr;
259
260
261 cn_message_pkg.debug('notify_aia_om : notify_failed_trx<<');
262 fnd_file.put_line(fnd_file.Log, 'notify_aia_om : notify_failed_trx<<');
263
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 cn_debug.print_msg('notify_aia_om: notify_failed_trx : in exception handler', 1);
268
269 cn_message_pkg.debug('notify_aia_om: notify_failed_trx : in exception handler');
270 fnd_file.put_line(fnd_file.Log, 'notify_aia_om: notify_failed_trx : in exception handler');
271
272 cn_message_pkg.debug(SQLCODE||' '||SQLERRM);
273 fnd_file.put_line(fnd_file.Log, SQLCODE||' '||SQLERRM);
274
275 app_exception.raise_exception;
276
277 END notify_failed_trx;
278
279 END cn_notify_aia_om;
280
281