[Home] [Help]
PACKAGE BODY: APPS.CSM_ORDERS_PKG
Source
1 PACKAGE BODY CSM_ORDERS_PKG AS
2 /* $Header: csmuordb.pls 120.0.12020000.2 2013/04/09 11:04:50 saradhak noship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- ravir 28-05-2012 Created
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14
15 /*** Globals ***/
16
17 g_object_name CONSTANT VARCHAR2(30) := 'CSM_ORDERS_PKG'; -- package name
18 g_pub_name CONSTANT VARCHAR2(30) := 'CSM_ORDER_LINES'; -- publication item name
19 g_debug_level NUMBER; -- debug level
20 g_user_name VARCHAR2(300);
21
22 /***
23 Cursor to retrieve all order lines from INQ.
24 ***/
25 CURSOR c_order_lines ( b_user_name VARCHAR2, b_tranid NUMBER) is
26 SELECT *
27 FROM CSM_ORDER_LINES_INQ l
28 WHERE l.tranid$$ = b_tranid
29 AND l.clid$$cs = b_user_name;
30
31
32
33 /***
34 This prcedure RECEIVE_ORDER calls CSP API to receive parts for a given line_id and quantiry
35 This can be user a Mobile Query: pre-req need to to fnd_global.apps_initialize();
36 ***/
37
38 PROCEDURE RECEIVE_ORDER(
39 p_line_id NUMBER,
40 p_received_qty NUMBER,
41 p_result OUT nocopy CLOB,
42 x_return_status OUT nocopy VARCHAR2,
43 x_error_message OUT nocopy VARCHAR2
44 ) IS
45
46 CURSOR c_part_line ( p_line_id NUMBER) is
47 select distinct hdr.source_type,
48 hdr.source_organization_id ,
49 hdr.source_subinventory,
50 hdr.destination_organization_id ,
51 hdr.destination_subinventory,
52 hdr.header_id,
53 line.line_id,
54 line.inventory_item_id,
55 line.revision,
56 line.uom_code,
57 line.serial_number,
58 line.order_quantity,
59 line.req_line_detail_id,
60 line.shipment_number,
61 line.shipment_line_id
62 from csp_receiving_headers_v hdr,
63 csp_receiving_lines_v line
64 where line.line_id = p_line_id
65 and line.header_id = hdr.header_id
66 and rownum<p_received_qty+1;
67
68 l_trans_items_rec_type CSP_TRANSACTIONS_PUB.Trans_Items_Rec_Type;
69 l_null_trans_rec CSP_TRANSACTIONS_PUB.Trans_Items_Rec_Type;
70
71 r_part_line c_part_line%rowtype;
72 l_msg_count NUMBER:=0;
73 l_msg_data VARCHAR2(4000);
74 l_src_type VARCHAR2(100);
75 l_dummy boolean;
76 BEGIN
77
78 CSM_UTIL_PKG.LOG
79 ( 'Entering CSM_ORDERS_PKG.RECEIVE_ORDER'|| 'for PK '|| p_line_id ,
80 'CSM_ORDERS_PKG.RECEIVE_ORDER',
81 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
82
83 FOR r_part_line IN c_part_line(p_line_id)
84 LOOP
85 l_src_type :=r_part_line.source_type;
86
87 l_trans_items_rec_type := l_null_trans_rec;
88
89 l_trans_items_rec_type.INVENTORY_ITEM_ID := r_part_line.INVENTORY_ITEM_ID;
90 l_trans_items_rec_type.REVISION := r_part_line.REVISION;
91 l_trans_items_rec_type.SERIAL_NUMBER := r_part_line.serial_number;
92 IF r_part_line.serial_number IS NOT NULL THEN
93 l_trans_items_rec_type.QUANTITY := p_received_qty;
94 ELSE
95 l_trans_items_rec_type.QUANTITY := 1;
96 END IF;
97 l_trans_items_rec_type.UOM_CODE := r_part_line.uom_code;
98 l_trans_items_rec_type.FRM_ORGANIZATION_ID := r_part_line.source_organization_id;
99 l_trans_items_rec_type.FRM_SUBINVENTORY_CODE := r_part_line.source_subinventory;
100 l_trans_items_rec_type.TO_ORGANIZATION_ID := r_part_line.destination_organization_id;
101 l_trans_items_rec_type.TO_SUBINVENTORY_CODE := r_part_line.destination_subinventory;
102 l_trans_items_rec_type.SHIPMENT_NUMBER := r_part_line.shipment_number;
103 l_trans_items_rec_type.SHIPMENT_LINE_ID := r_part_line.shipment_line_id;
104
105 CSM_UTIL_PKG.LOG
106 ( 'Calling API CSP_TRANSACTIONS_PUB.receive_requirement_trans for PK '||p_line_id ,
107 'CSM_ORDERS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_STATEMENT ); -- put PK column here
108
109 CSP_TRANSACTIONS_PUB.receive_requirement_trans (
110 p_trans_header_id => r_part_line.header_id,
111 p_trans_line_id => r_part_line.line_id,
112 p_trans_record => l_trans_items_rec_type,
113 p_trans_type => r_part_line.source_type,
114 p_req_line_detail_id => r_part_line.req_line_detail_id,
115 p_close_short => NULL,
116 x_return_status => x_return_status,
117 x_msg_count => l_msg_count,
118 x_msg_data => l_msg_data
119 );
120
121 IF x_return_status <> FND_API.G_RET_STS_SUCCESS OR l_msg_data IS NOT NULL OR l_msg_count<>0 THEN
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 l_msg_data := replace(fnd_msg_pub.get(l_msg_count,'F'),fnd_global.local_chr(10), ' ');
124 x_error_message := 'Error in CSP_TRANSACTIONS_PUB.receive_requirement_trans' || ' for PK : ' || r_part_line.line_id || ' Details:' || l_msg_data ;
125 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
126 RETURN ;
127 END IF;
128
129 CSM_SYSTEM_ITEM_EVENT_PKG.RECEIVED_MTL_ONHAND(r_part_line.destination_organization_id,r_part_line.destination_subinventory,
130 r_part_line.INVENTORY_ITEM_ID,asg_base.get_user_id(g_user_name));
131 IF(r_part_line.serial_number IS NOT NULL) THEN
132 CSM_SERIAL_NUMBERS_EVENT_PKG.RECEIVED_MTL_SERIAL_NUM(r_part_line.destination_organization_id,r_part_line.destination_subinventory
133 ,r_part_line.INVENTORY_ITEM_ID,r_part_line.serial_number,asg_base.get_user_id(g_user_name));
134 END IF;
135 END LOOP;
136
137 IF l_src_type='IO' THEN
138 FOR rec IN (SELECT USER_ID FROM CSP_REQ_LINE_DETAILS rld ,CSM_REQ_LINE_DETAILS_ACC acc
139 WHERE rld.SOURCE_TYPE='IO' AND rld.SOURCE_ID=p_line_id AND acc.REQ_LINE_DETAIL_ID=rld.REQ_LINE_DETAIL_ID)
140 LOOP --to update received qty
141 l_dummy := asg_download.mark_dirty(
142 p_pub_item => 'CSM_ORDER_LINES'
143 , p_accessid => p_line_id
144 , p_userid => rec.user_id
145 , p_dml => 'U'
146 , p_timestamp => sysdate
147 );
148 END LOOP;
149 END IF;
150 CSM_UTIL_PKG.LOG('Leaving CSM_ORDERS_PKG.RECEIVE_ORDER for : ' || r_part_line.line_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
151
152 p_result := TO_CLOB('<NODATA></NODATA>');
153
154 END RECEIVE_ORDER;
155
156
157 PROCEDURE APPLY_UPDATE
158 (
159 p_record IN c_order_lines%ROWTYPE,
160 p_error_msg OUT NOCOPY VARCHAR2,
161 x_return_status IN OUT NOCOPY VARCHAR2,
162 x_reject_row OUT NOCOPY BOOLEAN
163 ) IS
164
165 l_result CLOB;
166
167 BEGIN
168
169 CSM_UTIL_PKG.LOG
170 ( 'Entering CSM_ORDERS_PKG.APPLY_UPDATE'|| 'for PK '||p_record.line_id ,
171 'CSM_ORDERS_PKG.APPLY_UPDATE',
172 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
173
174 RECEIVE_ORDER(
175 p_line_id => p_record.line_id,
176 p_received_qty => p_record.received_qty,
177 p_result => l_result,
178 x_return_status => x_return_status,
179 x_error_message => p_error_msg
180 );
181
182 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
183 /*** exception occurred in API -> return errmsg ***/
184 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
185 (
186 p_api_error => TRUE
187 );
188 END IF;
189
190
191
192
193 CSM_UTIL_PKG.LOG
194 ( 'Leaving CSM_ORDERS_PKG.APPLY_UPDATE'|| 'for PK '||p_record.line_id ,
195 'CSM_ORDERS_PKG.APPLY_UPDATE',
196 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
197
198
199 EXCEPTION WHEN OTHERS THEN
200
201 CSM_UTIL_PKG.LOG
202 ( 'Exception occurred in CSM_ORDERS_PKG.APPLY_UPDATE'|| 'for PK '||p_record.line_id || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
203 'CSM_ORDERS_PKG.APPLY_UPDATE',
204 FND_LOG.LEVEL_EXCEPTION); -- put PK column here
205
206
207 fnd_msg_pub.Add_Exc_Msg( 'CSM_ORDERS_PKG', 'APPLY_UPDATE', sqlerrm);
208 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
209 (
210 p_api_error => TRUE
211 );
212
213 x_return_status := FND_API.G_RET_STS_ERROR;
214 END APPLY_UPDATE;
215
216 /***
217 This procedure is called by APPLY_CLIENT_CHANGES for every record in inqueue that needs to be processed.
218 ***/
219 PROCEDURE APPLY_RECORD
220 (
221 p_record IN c_order_lines%ROWTYPE
222 , p_error_msg OUT NOCOPY VARCHAR2
223 , x_return_status IN OUT NOCOPY VARCHAR2
224 , x_reject_row OUT NOCOPY BOOLEAN
225 ) IS
226 BEGIN
227 /*** initialize return status and message list ***/
228 x_return_status := FND_API.G_RET_STS_SUCCESS;
229
230 FND_MSG_PUB.INITIALIZE;
231
232 CSM_UTIL_PKG.LOG
233 ( 'Entering CSM_ORDERS_PKG.APPLY_RECORD'|| 'for PK '||p_record.line_id,
234 'CSM_ORDERS_PKG.APPLY_RECORD',
235 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
236
237 CSM_UTIL_PKG.LOG
238 ( 'Processing order '|| 'for PK '||p_record.line_id ||'DMLTYPE = ' || p_record.dmltype$$,
239 'CSM_ORDERS_PKG.APPLY_RECORD',
240 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
241
242 IF p_record.dmltype$$ ='U' THEN
243 -- Process update
244 APPLY_UPDATE
245 (
246 p_record,
247 p_error_msg,
248 x_return_status,
249 x_reject_row
250 );
251 ELSE
252 -- invalid dml type
253
254 CSM_UTIL_PKG.LOG
255 ( 'Invalid or unsupported DML type: ' || p_record.dmltype$$|| 'for PK '||p_record.line_id,
256 'CSM_ORDERS_PKG.APPLY_RECORD',
257 FND_LOG.LEVEL_ERROR ); -- put PK column here
258
259 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
260 (
261 p_message => 'CSM_DML_OPERATION'
262 , p_token_name1 => 'DML'
263 , p_token_value1 => p_record.dmltype$$
264 );
265
266 x_return_status := FND_API.G_RET_STS_ERROR;
267 END IF;
268
269 CSM_UTIL_PKG.LOG
270 ( 'Leaving CSM_ORDERS_PKG.APPLY_RECORD'|| 'for PK '||p_record.line_id,
271 'CSM_ORDERS_PKG.APPLY_RECORD',
272 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
273
274 EXCEPTION WHEN OTHERS THEN
275 /*** defer record when any process exception occurs ***/
276 CSM_UTIL_PKG.LOG
277 ( 'Exception occurred in CSM_ORDERS_PKG.APPLY_RECORD'|| 'for PK '||p_record.line_id || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
278 'CSM_ORDERS_PKG.APPLY_RECORD',
279 FND_LOG.LEVEL_EXCEPTION); -- put PK column here
280
281
282 fnd_msg_pub.Add_Exc_Msg( 'CSM_ORDERS_PKG', 'APPLY_RECORD', sqlerrm);
283 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
284 (
285 p_api_error => TRUE
286 );
287
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 END APPLY_RECORD;
290
291 /***
292 This procedure is called by CSM_SERVICEP_WRAPPER_PKG when publication item CSM_REQ_LINES
293 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
294 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
295 public APIs.
296 ***/
297 PROCEDURE APPLY_CLIENT_CHANGES
298 (
299 p_user_name IN VARCHAR2,
300 p_tranid IN NUMBER,
301 p_debug_level IN NUMBER,
302 x_return_status IN OUT NOCOPY VARCHAR2
303 ) IS
304
305 l_process_status VARCHAR2(1);
306 l_error_msg VARCHAR2(4000);
307 l_reject_row boolean;
308 l_return_status VARCHAR2(1);
309 BEGIN
310
311 CSM_UTIL_PKG.LOG
312 ( 'Entering CSM_ORDERS_PKG.APPLY CLIENT CHANGES',
313 'CSM_ORDERS_PKG.APPLY_CLIENT_CHANGES',
314 p_debug_level); -- put PK column here
315
316 g_debug_level := p_debug_level;
317 g_user_name := p_user_name;
318 x_return_status := FND_API.G_RET_STS_SUCCESS;
319
320 /*** loop through records in inqueue ***/
321 FOR r_order_lines IN c_order_lines( p_user_name, p_tranid) LOOP
322
323 SAVEPOINT save_rec;
324
325 /*** apply record ***/
326 APPLY_RECORD
327 (
328 r_order_lines
329 , l_error_msg
330 , l_process_status
331 , l_reject_row
332 );
333
334 /*** was record processed successfully? ***/
335 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
336 /*** If Yes -> delete record from inqueue ***/
337 IF l_reject_row THEN
338 CSM_UTIL_PKG.REJECT_RECORD
339 (
340 p_user_name,
341 p_tranid,
342 r_order_lines.seqno$$,
343 r_order_lines.line_id,
344 g_object_name,
345 g_pub_name,
346 l_error_msg,
347 l_return_status
348 );
349 ELSE
350 CSM_UTIL_PKG.DELETE_RECORD
351 (
352 p_user_name,
353 p_tranid,
354 r_order_lines.seqno$$,
355 r_order_lines.line_id,
356 g_object_name,
357 g_pub_name,
358 l_error_msg,
359 l_return_status --Introduced new variable l_return_status since Defer
360 ); --process doesn't depend on this delete_record API
361 END IF;
362
363 /*** was delete/reject successful? ***/
364 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
365 /*** If No -> rollback ***/
366 CSM_UTIL_PKG.LOG
367 ( 'Deleting from inqueue failed, rolling back to savepoint'
368 || ' for PK ' || r_order_lines.line_id ,
369 g_object_name || '.APPLY_CLIENT_CHANGES',
370 FND_LOG.LEVEL_ERROR); -- put PK column here
371 ROLLBACK TO save_rec;
372 x_return_status := FND_API.G_RET_STS_ERROR;
373 END IF;
374 END IF; /*** end of IF l_process_status = FND_API.G_RET_STS_SUCCESS ***/
375
376 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
377 /*** Record was not processed successfully or delete failed
378 -> defer and reject record ***/
379 CSM_UTIL_PKG.LOG
380 ( 'Record not processed successfully, deferring and rejecting record'
381 || ' for PK ' || r_order_lines.line_id ,
382 g_object_name || '.APPLY_CLIENT_CHANGES',
383 FND_LOG.LEVEL_ERROR); -- put PK column here
384
385 CSM_UTIL_PKG.DEFER_RECORD
386 ( p_user_name
387 , p_tranid
388 , r_order_lines.seqno$$
389 , r_order_lines.line_id
390 , g_object_name
391 , g_pub_name
392 , l_error_msg
393 , l_process_status
394 , r_order_lines.dmltype$$
395 );
396
397 /*** Was defer successful? ***/
398 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
399 /*** no -> rollback ***/
400 CSM_UTIL_PKG.LOG
401 ( 'Defer record failed, rolling back to savepoint'
402 || ' for PK ' || r_order_lines.line_id ,
403 g_object_name || '.APPLY_CLIENT_CHANGES',
404 FND_LOG.LEVEL_ERROR); -- put PK column here
405 ROLLBACK TO save_rec;
406 x_return_status := FND_API.G_RET_STS_ERROR;
407 END IF;
408 END IF;
409
410 END LOOP;
411
412 CSM_UTIL_PKG.LOG
413 ( 'Leaving CSM_ORDERS_PKG.APPLY CLIENT CHANGES',
414 'CSM_ORDERS_PKG.APPLY_CLIENT_CHANGES',
415 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
416
417 END APPLY_CLIENT_CHANGES;
418
419
420 -- Enter further code below as specified in the Package spec.
421 END;