[Home] [Help]
PACKAGE BODY: APPS.CN_NOTIFY_AIA
Source
1 PACKAGE BODY cn_notify_aia AS
2 -- $Header: CNNOAIAB.pls 120.2 2009/09/09 06:28:38 rajukum noship $
3
4
5
6
7 -- Procedure Name
8 -- notify_aia
9 -- Purpose
10 -- This procedure collects data for aia records 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_aia', 1);
37 -- who.set_program_name('notify_aia');
38
39 cn_message_pkg.debug('notify_aia>>');
40 fnd_file.put_line(fnd_file.Log, 'notify_aia>>');
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_aia: Is collecting aia records for CN_NOT_TRX from period '||x_start_date ||' to period '||x_end_date ||'.');
50 fnd_file.put_line(fnd_file.Log, 'notify_aia: Is collecting aia records 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 processed_date,
61 notified_date,
62 notification_run_id,
63 collected_flag,
64 event_id,
65 source_trx_id,
66 source_trx_line_id,
67 source_doc_type,
68 org_id)
69 SELECT
70 cn_not_trx_s.NEXTVAL,
71 x_proc_audit_id,
72 cco10143.processed_date,
73 SYSDATE,
74 x_proc_audit_id,
75 'N',
76 -1020,
77 NULL,
78 cco10143.trans_seq_id, --*** Line Table Key Column
79 'AIA', --*** Source Type
80 x_org_id
81 FROM
82 cn_collection_aia cco10143
83 WHERE --*** Header.Primary_Key = Line.Foreign_Key
84 1 = 1
85 AND TRUNC(processed_date) BETWEEN x_start_date AND x_end_date
86 AND NOT EXISTS (
87 SELECT 1
88 FROM cn_not_trx
89 WHERE source_trx_line_id = cco10143.trans_seq_id --*** Line.Primary_Key
90 AND event_id = -1020
91 AND org_id = x_org_id);
92 --END Notification Insert Block
93
94
95 x_trx_count := SQL%ROWCOUNT;
96
97 cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, 0,
98 'Finished notification run: Notified ' || x_trx_count || ' aia records.');
99
100 IF ( x_trx_count = 0 ) THEN
101
102 cn_message_pkg.debug('notify_aia: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to AIA Interface table and processed date is null.');
103 fnd_file.put_line(fnd_file.Log, 'notify_aia: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to AIA Interface table and processed date is null.');
104
105
106 END IF;
107
108 COMMIT;
109
110 cn_message_pkg.debug('notify_aia: Finished notification run: Notified ' || x_trx_count || ' aia records.');
111 fnd_file.put_line(fnd_file.Log, 'notify_aia: Finished notification run: Notified ' || x_trx_count || ' aia records.');
112
113 cn_debug.print_msg('<<notify_aia', 1);
114
115 cn_message_pkg.debug('notify_aia<<');
116 fnd_file.put_line(fnd_file.Log, 'notify_aia<<');
117
118 cn_message_pkg.end_batch (x_proc_audit_id);
119
120 EXCEPTION
121 WHEN OTHERS THEN ROLLBACK;
122 cn_debug.print_msg('notify_aia: in exception handler', 1);
123 cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
124 SQLERRM);
125
126 cn_message_pkg.debug('notify_aia: in exception handler');
127 fnd_file.put_line(fnd_file.Log, 'notify_aia: in exception handler');
128
129 cn_message_pkg.debug(SQLCODE||' '||SQLERRM);
130 fnd_file.put_line(fnd_file.Log, SQLCODE||' '||SQLERRM);
131
132 cn_message_pkg.end_batch (x_proc_audit_id);
133
134 app_exception.raise_exception;
135
136 END notify;
137
138 END cn_notify_aia;
139
140