1 PACKAGE BODY CSI_TRANSACTION_LINES_PVT as
2 /* $Header: CSIVTLWB.pls 120.0.12000000.2 2007/07/11 16:32:16 ngoutam noship $ */
3 -- Start of comments
4
5 -- HISTORY
6
7 -- End of comments
8
9 procedure debug(p_message in varchar2) is
10 begin
11 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
12 --csi_t_gen_utility_pvt.add(p_message);
13 fnd_log.string(fnd_log.level_statement,'csi.plsql.csi_transaction_lines_pvt.process_txn_lines' ,p_message);
14 END IF;
15 end debug;
16
17
18 PROCEDURE process_txn_lines
19 (
20 errbuf OUT NOCOPY VARCHAR2,
21 retcode OUT NOCOPY NUMBER,
22 p_batch_id IN NUMBER
23 ,p_purge_option IN VARCHAR2
24 ) IS
25 l_return_status varchar2(1);
26 l_error_code number;
27 l_error_message varchar2(4000);
28 l_error_rec csi_datastructures_pub.transaction_error_rec;
29 L_CONC_STATUS BOOLEAN;
30 l_worker_start_date date;
31 l_start_date date;
32 l_request_id NUMBER;
33 l_start_timestamp TIMESTAMP;
34 l_end_timestamp TIMESTAMP;
35
36 TYPE header_id_array_type is TABLE OF CSI_BATCH_TXN_LINES.ORDER_HEADER_ID%TYPE ;
37 TYPE line_id_array_type is TABLE OF CSI_BATCH_TXN_LINES.ORDER_LINE_ID%TYPE ;
38 TYPE transaction_id_type is TABLE of CSI_BATCH_TXN_LINES.transaction_id%type;
39 TYPE transaction_type is TABLE of CSI_BATCH_TXN_LINES.transaction_type%type;
40
41 l_header_id_tbl header_id_array_type;
42 l_line_id_tbl line_id_array_type;
43 l_transaction_id_tbl transaction_id_type;
44 l_transaction_type_tbl transaction_type;
45
46 CURSOR transaction_lines_cur IS
47 SELECT transaction_type,transaction_id,order_header_id,order_line_id
48 FROM CSI_BATCH_TXN_LINES
49 WHERE batch_id = p_batch_id
50 AND processed_flag = 2
51 order by order_header_id,order_line_id;
52 --l_txn_lines_rec transaction_lines_cur%rowtype;
53
54 BEGIN
55
56 --EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''HIMAL'' ';
57 --EXECUTE IMMEDIATE 'alter session set events=''10046 trace name context forever, level 12'' ';
58
59 l_worker_start_date := SYSDATE;
60 l_request_id := FND_GLOBAL.conc_request_id;
61
62 debug('BEGIN CSI_TRANSACTION_LINES_PVT.Process_Txn_Lines, Batch : '||p_batch_id);
63
64 -- mark the rows as being processed by worker
65 update CSI_BATCH_TXN_LINES
66 set processed_flag = 2,last_update_date = sysdate,last_updated_by = fnd_global.user_id
67 where batch_id = p_batch_id
68 and processed_flag = 1;
69
70 debug(' Updated '||SQL%ROWCOUNT ||' rows with batch id '||p_batch_id);
71 commit;
72
73 SAVEPOINT process_txn_lines;
74
75 --store all rows to be processed in an array
76 Open transaction_lines_cur;
77
78 -- Fetch LIMIT is implicitly given by the number of order lines assigned in CSIVTXPB (main program)
79 Fetch transaction_lines_cur bulk collect into l_transaction_type_tbl,l_transaction_id_tbl,l_header_id_tbl,l_line_id_tbl;
80 debug(l_line_id_tbl.count || ' order lines fetched');
81
82 For i in l_line_id_tbl.first..l_line_id_tbl.count Loop
83 l_start_date := SYSDATE;
84 l_start_timestamp := SYSTIMESTAMP;
85 debug('Started Processing Order Line: '||l_line_id_tbl(i)||' and Transaction: '||l_transaction_type_tbl(i));
86
87 if l_transaction_type_tbl(i) = 'CSISOFUL' then
88 debug(' Calling csi_inv_txnstub_pkg.execute_trx_dpl for Order Line '||l_line_id_tbl(i));
89 csi_inv_txnstub_pkg.execute_trx_dpl(
90 p_transaction_type => l_transaction_type_tbl(i),
91 p_transaction_id => l_line_id_tbl(i),
92 x_trx_return_status => l_return_status,
93 x_trx_error_rec => l_error_rec);
94
95 debug(' After Calling csi_inv_txnstub_pkg.execute_trx_dpl for Order Line '||l_line_id_tbl(i)||' with Return Status '||l_return_status);
96 IF (l_return_status is null OR l_return_status <> fnd_api.g_ret_sts_success) THEN
97
98 fnd_file.put_line(fnd_file.log,'Error Occured while processing Order Line '||l_line_id_tbl(i));
99 debug('Call to csi_inv_txnstub_pkg.execute_trx_dpl fails with status '||l_return_status);
100
101 UPDATE CSI_BATCH_TXN_LINES
102 SET processed_flag = 4,last_update_date = sysdate,last_updated_by = fnd_global.user_id
103 WHERE batch_id = p_batch_id
104 AND processed_flag = 2
105 and order_line_id = l_line_id_tbl(i);
106
107 l_error_rec.inv_material_transaction_id := null;
108 csi_inv_trxs_pkg.log_csi_error(l_error_rec);
109
110 END IF;
111 elsif (l_transaction_type_tbl(i) = 'CSISOSHP') then
112
113 debug(' Calling csi_order_ship_pub.order_shipment for Order Line '||l_line_id_tbl(i));
114 csi_order_ship_pub.order_shipment(
115 p_mtl_transaction_id => l_transaction_id_tbl(i),
116 p_message_id => NULL,
117 x_return_status => l_return_status,
118 px_trx_error_rec => l_error_rec);
119
120 debug(' After Calling csi_order_ship_pub.order_shipment for Order Line: '||l_line_id_tbl(i)||' with Return Status '||l_return_status);
121 IF (l_return_status is null OR l_return_status <> fnd_api.g_ret_sts_success) THEN
122 fnd_file.put_line(fnd_file.log,'Error Occured while processing Order Line '||l_line_id_tbl(i));
123 debug('Call to csi_order_ship_pub.order_shipment fails with status '||l_return_status);
124
125 UPDATE CSI_BATCH_TXN_LINES
126 SET processed_flag = 4,last_update_date = sysdate,last_updated_by = fnd_global.user_id
127 WHERE batch_id = p_batch_id
128 AND processed_flag = 2
129 AND order_line_id = l_line_id_tbl(i);
130
131 END IF;
132 else
133 debug('Error! Transaction type ' || l_transaction_type_tbl(i) || ' is not supported by CSI_TRANSACTION_LINES_PVT.Process_Txn_Lines');
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135
136 end if;
137 l_end_timestamp := SYSTIMESTAMP;
138
139 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
140
141 --debug('Timing information for order line '||l_line_id_tbl(i)||': '|| (SYSDATE - l_start_date)*(60*60*24) || ' seconds.');
142 debug('Timing information for order line '||l_line_id_tbl(i)||' using Timestamp: '||TO_CHAR(l_end_timestamp - l_start_timestamp));
143
144 --INSERT INTO csi_batch_processing_times (worker_id, order_header_id, order_line_id, start_date, end_date,request_id,creation_date,created_by,last_update_date,last_updated_by)
145 --VALUES( p_batch_id, l_header_id_tbl(i), l_line_id_tbl(i), l_start_date, sysdate,l_request_id,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id);
146 end if;
147
148 END Loop;
149 close transaction_lines_cur;
150 --COMMIT;
151
152 if p_purge_option = 'Y' then
153
154 --delete processed rows from the table
155 delete from csi_batch_txn_lines
156 where batch_id = p_batch_id
157 AND processed_flag = 2;
158 else
159
160 -- mark the processed rows as success
161 UPDATE csi_batch_txn_lines
162 SET processed_flag = 3,last_update_date = sysdate,last_updated_by = fnd_global.user_id
163 where batch_id = p_batch_id
164 and processed_flag = 2;
165
166 end if;
167
168 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', null);
169 debug('END CSI_TRANSACTION_LINES_PVT.Process_Txn_Lines, Batch : '||p_batch_id);
170
171 /* commenting as timing information was introduced for prototyping purpose only
172 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
173 INSERT INTO csi_batch_processing_times
174 (worker_id, start_date, end_date,creation_date,created_by,last_update_date,last_updated_by)
175 VALUES
176 (p_batch_id, l_worker_start_date, sysdate,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id);
177 END IF;*/
178
179 --EXECUTE IMMEDIATE 'alter session set events=''10046 trace name context OFF'' ';
180
181 EXCEPTION
182 WHEN txn_line_error THEN
183 csi_t_gen_utility_pvt.build_file_name(
184 p_file_segment1 => 'csiinv',
185 p_file_segment2 => 'hook');
186 l_error_rec.inv_material_transaction_id := null;
187 csi_inv_trxs_pkg.log_csi_error(l_error_rec);
188
189 WHEN OTHERS THEN
190 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level THEN
191 fnd_log.string(fnd_log.level_unexpected,'csi.plsql.csi_transaction_lines_pvt.process_txn_lines', 'WHEN OTHERS: ' ||SQLERRM);
192 END IF;
193
194
195 ROLLBACK TO process_txn_lines;
196
197 update csi_batch_txn_lines
198 set batch_id = -1,processed_flag = 0,last_update_date = sysdate,last_updated_by = fnd_global.user_id
199 where batch_id = p_batch_id;
200
201 commit;
202 RAISE;
203 END process_txn_lines;
204
205 END CSI_TRANSACTION_LINES_PVT;
[Home] [Help]