[Home] [Help]
PACKAGE BODY: APPS.GMI_TRANS_ENGINE_PVT
Source
1 PACKAGE BODY GMI_TRANS_ENGINE_PVT AS
2 /* $Header: GMIVTXNB.pls 115.38 2004/06/17 23:04:23 txyu ship $ */
3 /* ***************************************************************
4 * *
5 * Package GMI_TRANS_ENGINE_PVT *
6 * *
7 * Contents CREATE_PENDING_TRANSACTION *
8 * DELETE_PENDING_TRANSACTION *
9 * UPDATE_PENDING_TRANSACTION *
10 * UPDATE_PENDING_TO_COMPLETED *
11 * CREATE_COMPLETED_TRANSACTION *
12 * *
13 * Use This is the top level of the private layer for the *
14 * Inventory Transaction Processor. *
15 * *
16 * History *
17 * Written by Harminder Verdding, OPM Development *
18 * *
19 * 23-May-00 P.J.Schofield for B1294915. Added Completed *
20 * Transaction support and also logic for XFER *
21 * pending transactions *
22 * 13-OCT-00 Jalaj Srivastava Bug 1427922.
23 * Grade and status txns can happen in closed
24 * periods.
25 * Added logic for grade changes *
26 * 14-JUN-01 H Verdding Bug 1834369 .
27 * Encapsulated Validate Trans Date Logic With new
28 * Function CHECK_PERIOD_CLOSE.
29 * 24-AUG-01 Added line_detail_id BUG#1675561
30 * Added NVL(p_tran_rec.trans_date,SYSDATE) for *
31 * creation_date in COMPLETED_TRANSACTION_BUILD per *
32 * Karen's request. *
33 * 03-OCT-01 H Verdding Bug 2025933
34 * Added Fetch to get noninv Value For Item *
35 *================================================
36 * Joe DiIorio 10/22/2001 11.5.1H BUG#2064443
37 * Added reason code assigment.
38 *================================================
39 * Joe DiIorio 04/08/2002 11.5.1I BUG#2248778
40 * Added Whse code to message ic_api_txn_post_closed.
41 * Jatinder 4/11/2002 - removed extra comments character
42 * which were causing the compilation error.
43 * Thomas Daniel 04/18/2002 11.5.1I BUG#2322973
44 * In the close_period_check function added code to invoke the
45 * trans date validate routine with the sysdate only if the
46 * period was closed for the passed in trans date. Also added
47 * specific messaging to the return codes from trans date validate.
48 *
49 * VRA Srinivas 26/Apr/2002 BUG#2341493
50 * Changed the code to not to insert into IC_TRAN_CMP when
51 * DOC_TYPE is STSI and STATUS_CTL is No Inventory.
52 * Jalaj Srivastava 07/24/02 Bug 2483644
53 * Modified create completed transaction to accept/process
54 * journal transactions doc types also.
55 * Jalaj Srivastava Bug 2519568
56 * Removed DML code for ic_summ_inv since, now ic_summ_inv is
57 * a view created from the data in ic_loct_inv and ic_tran_pnd
58 * Joe DiIorio Bug 2643440 11.5.1J
59 * Added nocopy.
60 * Joe DiIorio Bug 3090255 11.5.10L 08/15/2003
61 * Added field intorder_posted_ind.
62 * Jeff Baird Bug #3409615 02/05/2004
63 * Added who columns to update of ic_lots_mst.
64 * Jeff Baird Bug #3434156 02/10/2004
65 * Corrected column name in above fix.
66 * V.Anitha BUG#3526733 14-APR-2004
67 * Added colum reverse_id column in create_completed_transactions
68 * and COMPLETED_TRANSACTION_BUILD procedures.
72 * to ensure the completed transaction reflected
69 * Teresa Wong B3415691 6/7/2004
70 * Enhancement for Serono (Pls refer to B3599127)
71 * Modified update_pending_to_completed
73 * the lot status and qc grade of the lot in ic_loct_inv
74 * at the time the transaction took place
75 *********************************************************************
76 */
77 /* Global variables */
78 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_TRANS_ENGINE_PVT';
79
80 PROCEDURE CREATE_PENDING_TRANSACTION
81 ( p_api_version IN NUMBER
82 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
83 , p_commit IN VARCHAR2 := FND_API.G_FALSE
84 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
85 , p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
86 , x_tran_row OUT NOCOPY IC_TRAN_PND%ROWTYPE
87 , x_return_status OUT NOCOPY VARCHAR2
88 , x_msg_count OUT NOCOPY NUMBER
89 , x_msg_data OUT NOCOPY VARCHAR2
90 )
91 IS
92 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_PENDING_TRANSACTION';
93 l_api_version CONSTANT NUMBER := 1.0;
94 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
95 l_tran_rec_val GMI_TRANS_ENGINE_PUB.ictran_rec;
96 l_tran_row IC_TRAN_PND%ROWTYPE;
97 l_msg_count NUMBER :=0;
98 l_return_val NUMBER :=0;
99 l_retry_flag NUMBER :=1;
100 l_msg_data VARCHAR2(2000);
101 l_return_status VARCHAR2(1);
102 BEGIN
103 /* Standard Start OF API savepoint*/
104 SAVEPOINT create_pending_transaction;
105
106 /* Initialize API return status to sucess*/
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108
109 /* Move transaction Table record to local*/
110 /* This has to be done to add new trans id record */
111 l_tran_rec := p_tran_rec;
112
113 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
114 THEN
115 SET_DEFAULTS (p_tran_rec => p_tran_rec, x_tran_rec => l_tran_rec);
116 END IF;
117
118 /* Validate Trans Date For Posting Into Closed Periods.*/
119
120 IF NOT CLOSE_PERIOD_CHECK
121 ( p_tran_rec => l_tran_rec,
122 p_retry_flag => l_retry_flag,
123 x_tran_rec => l_tran_rec
124 )
125 THEN
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END IF;
128
129 /* ***********************************************************
130 Jalaj Srivastava Bug 2519568
131 Removed DML code for ic_summ_inv since, now ic_summ_inv is
132 a view created from the data in ic_loct_inv and ic_tran_pnd
133 *********************************************************** */
134
135 PENDING_TRANSACTION_BUILD
136 ( p_tran_rec => l_tran_rec
137 , x_tran_row => l_tran_row
138 , x_return_status => l_return_status
139 );
140
141 /* Call the IC_TRAN_PND INSERT procedure to insert this record. */
142
143 IF NOT GMI_TRAN_PND_DB_PVT.INSERT_IC_TRAN_PND
144 ( p_tran_row => l_tran_row, x_tran_row => x_tran_row)
145 THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END IF;
148
149
150 /* Standard Check of p_commit. */
151
152 IF FND_API.to_boolean(p_commit)
153 THEN
154 COMMIT WORK;
155 END IF;
156
157 EXCEPTION
158 WHEN FND_API.G_EXC_ERROR THEN
159 ROLLBACK TO create_pending_transaction;
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
162 , p_count => x_msg_count
163 , p_data => x_msg_data
164 );
165
166 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
167 ROLLBACK TO create_pending_transaction;
168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
170 , p_count => x_msg_count
171 , p_data => x_msg_data
172 );
173 WHEN OTHERS THEN
174 ROLLBACK TO create_pending_transaction;
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176
177 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
178 , l_api_name
179 );
180
181 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
182 , p_count => x_msg_count
183 , p_data => x_msg_data
184 );
185
186 END CREATE_PENDING_TRANSACTION;
187
188
189
190 PROCEDURE CREATE_COMPLETED_TRANSACTION
191 ( p_api_version IN NUMBER
192 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
193 , p_commit IN VARCHAR2 := FND_API.G_FALSE
194 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
195 , p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
196 , x_tran_row OUT NOCOPY IC_TRAN_CMP%ROWTYPE
197 , x_return_status OUT NOCOPY VARCHAR2
198 , x_msg_count OUT NOCOPY NUMBER
199 , x_msg_data OUT NOCOPY VARCHAR2
200 , p_table_name IN VARCHAR2 := 'IC_TRAN_CMP'
201 )
202 IS
203 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_COMPLETED_TRANSACTION';
204 l_api_version CONSTANT NUMBER := 1.0;
205 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
206 l_tran_cmp IC_TRAN_CMP%ROWTYPE;
207 l_tran_pnd IC_TRAN_PND%ROWTYPE;
208 l_msg_count NUMBER :=0;
209 l_return_val NUMBER :=0;
210 l_retry_flag NUMBER :=0;
211 l_msg_data VARCHAR2(2000);
212 l_return_status VARCHAR2(1);
213 -- BEGIN Bug# 2341493 VRA Srinivas 26/04/2002
217 l_ic_loct_inv_row_out ic_loct_inv%ROWTYPE;
214 l_status_ctl NUMBER;
215 --END Bug# 2341493
216 l_ic_loct_inv_row_in ic_loct_inv%ROWTYPE;
218
219 -- BEGIN Bug# 2341493 VRA Srinivas 26/04/2002
220 CURSOR Cur_status_ctl(pitem_id NUMBER) IS
221 SELECT
222 status_ctl
223 FROM
224 ic_item_mst
225 WHERE
226 item_id = pitem_id;
227 --END Bug# 2341493
228
229 BEGIN
230 /* Standard Start OF API savepoint */
231 SAVEPOINT create_completed_transaction;
232 /* Standard call to check for call compatibility. */
233 IF NOT FND_API.Compatible_API_CALL
234 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
235 THEN
236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
237 END IF;
238
239 /* Initialize message list if p_int_msg_list is set TRUE. */
240 IF FND_API.to_boolean(p_init_msg_list) THEN
241 FND_MSG_PUB.Initialize;
242 END IF;
243
244 /* Initialize API return status to sucess */
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246
247 /* Move transaction Table record to local */
248 /* This has to be done to add new trans id record */
249 l_tran_rec := p_tran_rec;
250
251 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
252 SET_DEFAULTS ( p_tran_rec => p_tran_rec ,x_tran_rec => l_tran_rec);
253 END IF;
254
255 /* Jalaj Srivastava Bug 1427922 */
256 /* Grade and status txns do not require trans date validation. */
257
258 --Jalaj Srivastava Bug 2483644
259 --Now journal txns would also be posted using these APIs
260 IF (substr(l_tran_rec.doc_type,1,3) NOT IN ('STS','GRD')) THEN
261
262
263 IF NOT CLOSE_PERIOD_CHECK
264 ( p_tran_rec => l_tran_rec,
265 p_retry_flag => l_retry_flag,
266 x_tran_rec => l_tran_rec
267 )
268 THEN
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270 END IF;
271
272 END IF;
273
274 /* Jalaj Srivastava Bug 1427922 */
275 --Jalaj Srivastava Bug 2483644
276 --Now journal txns would also be posted using these APIs
277 IF ( (l_tran_rec.non_inv = 0) AND (substr(l_tran_rec.doc_type,1,3) <> 'GRD') )
278 THEN
279 GMI_LOCT_INV_PVT.UPDATING_IC_LOCT_INV
280 (
281 p_tran_rec => l_tran_rec,
282 x_return_status =>l_return_status
283 );
284
285 /* if errors were found then Raise Exception */
286 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
287 THEN
288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
289 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
290 RAISE FND_API.G_EXC_ERROR;
291 END IF;
292 END IF;
293
294 /* Update Inventory Balances ( Actuals). */
295 /* ***********************************************************
296 Jalaj Srivastava Bug 2519568
297 Removed DML code for ic_summ_inv since, now ic_summ_inv is
298 a view created from the data in ic_loct_inv and ic_tran_pnd
299 *********************************************************** */
300
301
302 /* Jalaj Srivastava Bug 1427922 */
303 /* Added logic for grade txns and to update ic_lots_mst for change in gradeREM */
304
305 --Jalaj Srivastava Bug 2483644
306 --Now journal txns would also be posted using these APIs
307 IF (substr(l_tran_rec.doc_type,1,3) = 'GRD') THEN
308
309 /* Update ic_lots_mst with the new grade */
310
311 update ic_lots_mst set
312 qc_grade = l_tran_rec.qc_grade,
313 last_update_date = SYSDATE,
314 last_updated_by = p_tran_rec.user_id
315 where item_id = l_tran_rec.item_id
316 and lot_id = l_tran_rec.lot_id
317 and delete_mark = 0;
318
319 -- Bug #3409615 (JKB) Added who columns above.
320
321 END IF; /* IF (l_tran_rec.doc_type = 'GRDI') */
322
323 /* end of bug 1427922REM ---------------------------- */
324
325 /* Call the IC_TRAN_CMP INSERT procedure to insert this record. */
326
327 IF p_table_name = 'IC_TRAN_CMP'
328 THEN
329 --BEGIN Bug# 2341493 VRA Srinivas 26/04/2002
330 --Do not insert into ic_tran_cmp if the status control
331 --of the item is set to 'No Inventory'
332 OPEN Cur_status_ctl(p_tran_rec.item_id);
333 FETCH Cur_status_ctl INTO l_status_ctl;
334 CLOSE Cur_status_ctl;
335 --Jalaj Srivastava Bug 2483644
336 --Now journal txns would also be posted using these APIs
337 IF (l_status_ctl = 2 AND substr(p_tran_rec.doc_type,1,3) = 'STS')
338 THEN
339 NULL;
340 ELSE
341 -- END Bug# 2341493
342
343 /* Call the IC_TRAN_CMP INSERT procedure to insert this record. */
344 l_tran_cmp.trans_id := p_tran_rec.trans_id;
345 l_tran_cmp.item_id := p_tran_rec.item_id;
346 l_tran_cmp.line_id := p_tran_rec.line_id;
347 l_tran_cmp.co_code := p_tran_rec.co_code;
348 l_tran_cmp.orgn_code := p_tran_rec.orgn_code;
349 l_tran_cmp.whse_code := p_tran_rec.whse_code;
350 l_tran_cmp.reason_code := p_tran_rec.reason_code;
351 l_tran_cmp.lot_id := p_tran_rec.lot_id;
352 l_tran_cmp.location := p_tran_rec.location;
353 l_tran_cmp.doc_type := p_tran_rec.doc_type;
354 l_tran_cmp.doc_id := p_tran_rec.doc_id;
355 l_tran_cmp.doc_line := NVL(p_tran_rec.doc_line,0);
356 l_tran_cmp.line_type := NVL(p_tran_rec.line_type,0);
357 l_tran_cmp.creation_date := SYSDATE;
358 l_tran_cmp.trans_date := NVL(p_tran_rec.trans_date,SYSDATE);
359 l_tran_cmp.trans_qty := p_tran_rec.trans_qty;
360 l_tran_cmp.trans_qty2 := p_tran_rec.trans_qty2;
364 l_tran_cmp.trans_um := p_tran_rec.trans_um;
361 l_tran_cmp.qc_grade := p_tran_rec.qc_grade;
362 l_tran_cmp.lot_status := p_tran_rec.lot_status;
363 l_tran_cmp.trans_stat := p_tran_rec.trans_stat;
365 l_tran_cmp.trans_um2 := p_tran_rec.trans_um2;
366 l_tran_cmp.op_code := p_tran_rec.user_id;
367 l_tran_cmp.gl_posted_ind := 0; /* Always 0 */
368 l_tran_cmp.event_id := NVL(p_tran_rec.event_id,0);
369 l_tran_cmp.text_code := p_tran_rec.text_code;
370 l_tran_cmp.last_update_date := SYSDATE;
371 l_tran_cmp.last_updated_by := p_tran_rec.user_id;
372 l_tran_cmp.created_by := p_tran_rec.user_id;
373 l_tran_cmp.line_detail_id := NVL(p_tran_rec.line_detail_id,NULL);
374 /*============================================
375 BUG#3090255 Populated intorder_posted_ind
376 ==========================================*/
377 l_tran_cmp.intorder_posted_ind := NVL(p_tran_rec.intorder_posted_ind,0);
378
379 IF NOT GMI_TRAN_CMP_PVT.INSERT_IC_TRAN_CMP
380 ( p_tran_row => l_tran_cmp, x_tran_row => x_tran_row)
381 THEN
382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
383 END IF;
384 -- BEGIN Bug# 2341493 VRA Srinivas 26/04/2002
385 END IF; -- l_status_ctl =2 and p_tran_rec.doc_type='STSI')
386 -- END Bug# 2341493
387
388 ELSE
389 /* Call the IC_TRAN_PND INSERT procedure to insert this record. */
390 l_tran_pnd.trans_id := p_tran_rec.trans_id;
391 l_tran_pnd.item_id := p_tran_rec.item_id;
392 l_tran_pnd.line_id := p_tran_rec.line_id;
393 l_tran_pnd.co_code := p_tran_rec.co_code;
394 l_tran_pnd.orgn_code := p_tran_rec.orgn_code;
395 l_tran_pnd.whse_code := p_tran_rec.whse_code;
396 l_tran_pnd.reason_code := p_tran_rec.reason_code;
397 l_tran_pnd.lot_id := p_tran_rec.lot_id;
398 l_tran_pnd.location := p_tran_rec.location;
399 l_tran_pnd.doc_type := p_tran_rec.doc_type;
400 l_tran_pnd.doc_id := p_tran_rec.doc_id;
401 l_tran_pnd.doc_line := NVL(p_tran_rec.doc_line,0);
402 l_tran_pnd.line_type := NVL(p_tran_rec.line_type,0);
403 l_tran_pnd.creation_date := SYSDATE;
404 l_tran_pnd.trans_date := NVL(p_tran_rec.trans_date,SYSDATE);
405 l_tran_pnd.trans_qty := p_tran_rec.trans_qty;
406 l_tran_pnd.trans_qty2 := p_tran_rec.trans_qty2;
407 l_tran_pnd.qc_grade := p_tran_rec.qc_grade;
408 l_tran_pnd.lot_status := p_tran_rec.lot_status;
409 l_tran_pnd.trans_stat := p_tran_rec.trans_stat;
410 l_tran_pnd.trans_um := p_tran_rec.trans_um;
411 l_tran_pnd.trans_um2 := p_tran_rec.trans_um2;
412 l_tran_pnd.op_code := p_tran_rec.user_id;
413 l_tran_pnd.gl_posted_ind := 0; /* Always 0 */
414 l_tran_pnd.event_id := NVL(p_tran_rec.event_id,0);
415 l_tran_pnd.text_code := p_tran_rec.text_code;
416 l_tran_pnd.last_update_date := SYSDATE;
417 l_tran_pnd.last_updated_by := p_tran_rec.user_id;
418 l_tran_pnd.created_by := p_tran_rec.user_id;
419 l_tran_pnd.staged_ind := p_tran_rec.staged_ind;
420 l_tran_pnd.completed_ind := 1;
421 l_tran_pnd.delete_mark := 0;
422 l_tran_pnd.line_detail_id := NVL(p_tran_rec.line_detail_id,NULL);
423 /*============================================
424 BUG#3090255 Populated intorder_posted_ind
425 ==========================================*/
426 l_tran_pnd.intorder_posted_ind := NVL(p_tran_rec.intorder_posted_ind,0);
427 /*============================================
428 BUG#3526733 Populated reverse_id
429 ==========================================*/
430 l_tran_pnd.reverse_id := p_tran_rec.reverse_id;
431
432 IF NOT GMI_TRAN_PND_DB_PVT.INSERT_IC_TRAN_PND
433 ( p_tran_row => l_tran_pnd, x_tran_row => l_tran_pnd)
434 THEN
435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
436 END IF;
437
438 /* Need to sort out the return row to the caller.... */
439 x_tran_row.trans_id := l_tran_pnd.trans_id;
440 x_tran_row.item_id := l_tran_pnd.item_id;
441 x_tran_row.line_id := l_tran_pnd.line_id;
442 x_tran_row.co_code := l_tran_pnd.co_code;
443 x_tran_row.orgn_code := l_tran_pnd.orgn_code;
444 x_tran_row.whse_code := l_tran_pnd.whse_code;
445 x_tran_row.reason_code := l_tran_pnd.reason_code;
446 x_tran_row.lot_id := l_tran_pnd.lot_id;
447 x_tran_row.location := l_tran_pnd.location;
448 x_tran_row.doc_type := l_tran_pnd.doc_type;
449 x_tran_row.doc_id := l_tran_pnd.doc_id;
450 x_tran_row.doc_line := l_tran_pnd.doc_line;
451 x_tran_row.line_type := l_tran_pnd.line_type;
452 x_tran_row.creation_date := l_tran_pnd.creation_date;
453 x_tran_row.trans_date := l_tran_pnd.trans_date;
454 x_tran_row.trans_qty := l_tran_pnd.trans_qty;
455 x_tran_row.trans_qty2 := l_tran_pnd.trans_qty2;
456 x_tran_row.qc_grade := l_tran_pnd.qc_grade;
457 x_tran_row.lot_status := l_tran_pnd.lot_status;
458 x_tran_row.trans_stat := l_tran_pnd.trans_stat;
459 x_tran_row.trans_um := l_tran_pnd.trans_um;
460 x_tran_row.trans_um2 := l_tran_pnd.trans_um2;
461 x_tran_row.op_code := l_tran_pnd.op_code;
462 x_tran_row.gl_posted_ind := l_tran_pnd.gl_posted_ind;
463 x_tran_row.event_id := l_tran_pnd.event_id;
464 x_tran_row.text_code := l_tran_pnd.text_code;
468 x_tran_row.line_detail_id := NVL(l_tran_pnd.line_detail_id,NULL);
465 x_tran_row.last_update_date := l_tran_pnd.last_update_date;
466 x_tran_row.last_updated_by := l_tran_pnd.op_code;
467 x_tran_row.created_by := l_tran_pnd.op_code;
469 /*============================================
470 BUG#3090255 Populated intorder_posted_ind
471 ==========================================*/
472 x_tran_row.intorder_posted_ind := NVL(l_tran_pnd.intorder_posted_ind,0);
473 /*============================================
474 BUG#3526733 Populated reverse_id
475 ==========================================*/
476 -- 3575580
477 -- Commented the lines below as reverse id does not exist in ic_tran_cmp.
478 -- x_tran_row.reverse_id := l_tran_pnd.reverse_id;
479
480 END IF;
481
482
483 /* Standard Check of p_commit. */
484 IF FND_API.to_boolean(p_commit)
485 THEN
486 COMMIT WORK;
487 END IF;
488
489 EXCEPTION
490 WHEN FND_API.G_EXC_ERROR THEN
491 ROLLBACK TO create_completed_transaction;
492 x_return_status := FND_API.G_RET_STS_ERROR;
493 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
494 , p_count => x_msg_count
495 , p_data => x_msg_data
496 );
497
498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
499 ROLLBACK TO create_completed_transaction;
500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
501 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
502 , p_count => x_msg_count
503 , p_data => x_msg_data
504 );
505 WHEN OTHERS THEN
506 ROLLBACK TO create_completed_transaction;
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508
509 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
510 , l_api_name
511 );
512
513 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
514 , p_count => x_msg_count
515 , p_data => x_msg_data
516 );
517
518 END CREATE_COMPLETED_TRANSACTION;
519
520
521 PROCEDURE DELETE_PENDING_TRANSACTION
522 ( p_api_version IN NUMBER
523 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
524 , p_commit IN VARCHAR2 := FND_API.G_FALSE
525 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
526 , p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
527 , x_tran_row OUT NOCOPY IC_TRAN_PND%ROWTYPE
528 , x_return_status OUT NOCOPY VARCHAR2
529 , x_msg_count OUT NOCOPY NUMBER
530 , x_msg_data OUT NOCOPY VARCHAR2
531 )
532 IS
533 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_PENDING_TRANSACTION';
534 l_api_version CONSTANT NUMBER := 1.0;
535 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
536 l_tran_fetch_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
537 l_msg_count NUMBER :=0;
538 l_return_val NUMBER :=0;
539 l_msg_data VARCHAR2(2000);
540 l_return_status VARCHAR2(1);
541 l_trans_id IC_TRAN_PND.TRANS_ID%TYPE;
542 l_tran_row IC_TRAN_PND%ROWTYPE;
543 BEGIN
544 /* Standard Start OF API savepoint */
545 SAVEPOINT delete_pending_transaction;
546
547 /* Initialize API return status to sucess */
548 x_return_status := FND_API.G_RET_STS_SUCCESS;
549
550 /* Move transaction Table record to local*/
551 /* This has to be done to add new trans id record */
552 l_tran_rec := p_tran_rec;
553
554 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
555 THEN
556 SET_DEFAULTS ( p_tran_rec => p_tran_rec ,x_tran_rec => l_tran_rec);
557 END IF;
558
559 /* COMMENTED OUT Validate Trans Date For Posting Into Closed Periods.
560
561 l_return_val := GMICCAL.trans_date_validate ( l_tran_rec.trans_date
562 , l_tran_rec.orgn_code
563 , l_tran_rec.whse_code
564 );
565 ==============================================
566 Joe DiIorio 04/08/2002 11.5.1I BUG#2248778
567 Jatinder - Removed the comment characters from
568 this code.
569 ============================================
570 IF (l_return_val <> 0)
571 THEN
572 FND_MESSAGE.SET_NAME('GMI','IC_API_TXN_POST_CLOSED');
573 FND_MESSAGE.SET_TOKEN('DATE',l_tran_rec.trans_date);
574 FND_MESSAGE.SET_TOKEN('WAREH',l_tran_rec.whse_code);
575 FND_MSG_PUB.Add;
576 RAISE FND_API.G_EXC_ERROR;
577 END IF;
578 */
579
580 /* Call the IC_TRAN_PND FETCH procedure to FETCH this record. */
581
582 IF NOT GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
583 ( p_tran_rec => l_tran_rec, x_tran_fetch_rec => l_tran_fetch_rec )
584 THEN
585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
586 END IF;
587
588 /* Check If this is A Non-Inventory Item */
589
590 IF l_tran_rec.non_inv = 0
591 THEN
592 /* Negate The Quantities */
593
594 l_tran_fetch_rec.trans_qty := l_tran_fetch_rec.trans_qty *-1;
595 l_tran_fetch_rec.trans_qty2 := l_tran_fetch_rec.trans_qty2 *-1;
596
597 /* ***********************************************************
598 Jalaj Srivastava Bug 2519568
599 Removed DML code for ic_summ_inv since, now ic_summ_inv is
600 a view created from the data in ic_loct_inv and ic_tran_pnd
601 *********************************************************** */
602 END IF;
606 PENDING_TRANSACTION_BUILD
603
604 /* Call Create_pending_transaction to build ic_tran_pnd%rowtype; */
605
607 ( p_tran_rec => l_tran_fetch_rec
608 , x_tran_row => l_tran_row
609 , x_return_status => l_return_status
610 );
611
612 /* Call the IC_TRAN_PND DELETE procedure to Logically Delete this record. */
613
614 IF NOT GMI_TRAN_PND_DB_PVT.DELETE_IC_TRAN_PND
615 ( p_tran_row => l_tran_row )
616 THEN
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618 END IF;
619
620 /* Standard Check of p_commit. */
621 IF FND_API.to_boolean(p_commit)
622 THEN
623 COMMIT WORK;
624 END IF;
625
626 x_tran_row := l_tran_row;
627
628 x_return_status := l_return_status;
629
630 EXCEPTION
631 WHEN FND_API.G_EXC_ERROR THEN
632 ROLLBACK TO delete_pending_transaction;
633 x_return_status := FND_API.G_RET_STS_ERROR;
634 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
635 , p_count => x_msg_count
636 , p_data => x_msg_data
637 );
638
639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
640 ROLLBACK TO delete_pending_transaction;
641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
642 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
643 , p_count => x_msg_count
644 , p_data => x_msg_data
645 );
646 WHEN OTHERS THEN
647 ROLLBACK TO delete_pending_transaction;
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649
650 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
651 , l_api_name
652 );
653
654 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
655 , p_count => x_msg_count
656 , p_data => x_msg_data
657 );
658
659 END DELETE_PENDING_TRANSACTION;
660
661 PROCEDURE UPDATE_PENDING_TRANSACTION
662 ( p_api_version IN NUMBER
663 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
664 , p_commit IN VARCHAR2 := FND_API.G_FALSE
665 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
666 , p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
667 , x_tran_row OUT NOCOPY IC_TRAN_PND%ROWTYPE
668 , x_return_status OUT NOCOPY VARCHAR2
669 , x_msg_count OUT NOCOPY NUMBER
670 , x_msg_data OUT NOCOPY VARCHAR2
671 )
672 IS
673 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_PENDING_TRANSACTION';
674 l_api_version CONSTANT NUMBER := 1.0;
675 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
676 l_tran_fetch_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
677 l_msg_count NUMBER :=0;
678 l_return_val NUMBER :=0;
679 l_retry_flag NUMBER :=1;
680 l_msg_data VARCHAR2(2000);
681 l_return_status VARCHAR2(1);
682 l_trans_id IC_TRAN_PND.TRANS_ID%TYPE;
683 l_tran_row IC_TRAN_PND%ROWTYPE;
684 l_old_qc_grade VARCHAR2(4);
685 l_new_qc_grade VARCHAR2(4);
686 BEGIN
687 /* Standard Start OF API savepoint */
688 SAVEPOINT update_pending_transaction;
689
690 /* Initialize API return status to sucess */
691 x_return_status := FND_API.G_RET_STS_SUCCESS;
692
693 /* Move transaction Table record to local */
694 l_tran_rec := p_tran_rec;
695
696 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
697 THEN
698 SET_DEFAULTS
699 ( p_tran_rec => p_tran_rec
700 ,x_tran_rec => l_tran_rec
701 );
702 END IF;
703
704 /* Validate Trans Date For Posting Into Closed Periods. */
705
706 IF NOT CLOSE_PERIOD_CHECK
707 ( p_tran_rec => l_tran_rec,
708 p_retry_flag => l_retry_flag,
709 x_tran_rec => l_tran_rec
710 )
711 THEN
712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
713 END IF;
714
715
716
717 IF l_tran_rec.non_inv = 0
718 THEN
719 /* Get Previous Transaction Using Input Tran rec. */
720
721 IF NOT GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
722 ( p_tran_rec => l_tran_rec, x_tran_fetch_rec => l_tran_fetch_rec )
723 THEN
724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
725 END IF;
726
727 /* Compare the old tran rec QC_GRADE AND WHSE_CODE */
728 /* to determine If we are still updating same RECORD */
729 /* or we have changed record charateristics. */
730
731 /* Add Following To Get NULL QC_GRADE Logic */
732 l_old_qc_grade := NVL(l_tran_fetch_rec.qc_grade,FND_API.g_miss_char);
733 l_new_qc_grade := NVL(l_tran_fetch_rec.qc_grade,FND_API.g_miss_char);
734
735 IF l_tran_fetch_rec.whse_code <> l_tran_rec.whse_code
736 OR l_old_qc_grade <> l_new_qc_grade
737 THEN
738 /* Negate Out Previous Transactions */
739 l_tran_fetch_rec.trans_qty := l_tran_fetch_rec.trans_qty * -1;
740 l_tran_fetch_rec.trans_qty2 := l_tran_fetch_rec.trans_qty2 * -1;
741
742 /* Reset OLD Demand */
743
744 /* ***********************************************************
745 Jalaj Srivastava Bug 2519568
746 Removed DML code for ic_summ_inv since, now ic_summ_inv is
747 a view created from the data in ic_loct_inv and ic_tran_pnd
748 *********************************************************** */
749
750 ELSE
751 /* Update Previous Demand */
752
756 - l_tran_fetch_rec.trans_qty2;
753 l_tran_fetch_rec.trans_qty := l_tran_rec.trans_qty
754 - l_tran_fetch_rec.trans_qty;
755 l_tran_fetch_rec.trans_qty2 := l_tran_rec.trans_qty2
757
758
759 END IF;
760 END IF;
761
762 /* Call the IC_TRAN_PND INSERT procedure to insert this record. */
763 /* Call Create_pending_transaction to build ic_tran_pnd%rowtype; */
764
765 PENDING_TRANSACTION_BUILD
766 ( p_tran_rec => l_tran_rec
767 , x_tran_row => l_tran_row
768 , x_return_status => l_return_status
769 );
770
771
772 IF NOT GMI_TRAN_PND_DB_PVT.UPDATE_IC_TRAN_PND
773 ( p_tran_row => l_tran_row)
774 THEN
775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
776 END IF;
777
778 /* Standard Check of p_commit. */
779 IF FND_API.to_boolean(p_commit)
780 THEN
781 COMMIT WORK;
782 END IF;
783
784 x_tran_row := l_tran_row;
785
786 x_return_status := l_return_status;
787
788 EXCEPTION
789 WHEN FND_API.G_EXC_ERROR THEN
790 ROLLBACK TO update_pending_transaction;
791 x_return_status := FND_API.G_RET_STS_ERROR;
792 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
793 , p_count => x_msg_count
794 , p_data => x_msg_data
795 );
796
797 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
798 ROLLBACK TO update_pending_transaction;
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
801 , p_count => x_msg_count
802 , p_data => x_msg_data
803 );
804 WHEN OTHERS THEN
805 ROLLBACK TO update_pending_transaction;
806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
807
808 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
809 , l_api_name
810 );
811
812 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
813 , p_count => x_msg_count
814 , p_data => x_msg_data
815 );
816 END UPDATE_PENDING_TRANSACTION;
817
818 PROCEDURE UPDATE_PENDING_TO_COMPLETED
819 ( p_api_version IN NUMBER
820 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
821 , p_commit IN VARCHAR2 := FND_API.G_FALSE
822 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
823 , p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
824 , x_tran_row OUT NOCOPY IC_TRAN_PND%ROWTYPE
825 , x_return_status OUT NOCOPY VARCHAR2
826 , x_msg_count OUT NOCOPY NUMBER
827 , x_msg_data OUT NOCOPY VARCHAR2
828 )
829 IS
830 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_PENDING_TO_TRANSACTION';
831 l_api_version CONSTANT NUMBER := 1.0;
832 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
833 l_tran_fetch_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
834 l_msg_count NUMBER :=0;
835 l_return_val NUMBER :=0;
836 l_retry_flag NUMBER :=0;
837 l_msg_data VARCHAR2(2000);
838 l_return_status VARCHAR2(1);
839 l_trans_id IC_TRAN_PND.TRANS_ID%TYPE;
840 l_tran_row IC_TRAN_PND%ROWTYPE;
841 -- TKW B3415691 (Pls refer to 3599127)
842 l_item_mst_rec ic_item_mst%ROWTYPE;
843
844 /* TKW B3415691 - Enhancement for Serono (Pls refer to B3599127) */
845 /* Added two cursors to get lot_status and qc_grade */
846 CURSOR Cur_lot_status(
847 v_item_id IN NUMBER,
848 v_whse IN VARCHAR2,
849 v_lot_id IN NUMBER,
850 v_location IN VARCHAR2) IS
851 SELECT
852 lot_status
853 FROM
854 ic_loct_inv
855 WHERE
856 item_id = v_item_id
857 AND whse_code = v_whse
858 AND lot_id = v_lot_id
859 AND location = v_location;
860
861 CURSOR Cur_qc_grade(v_lot_id IN NUMBER) IS
862 SELECT
863 DECODE(i.lot_id, 0, NULL, m.qc_grade)
864 FROM
865 ic_loct_inv i,
866 ic_lots_mst m
867 WHERE
868 m.lot_id = i.lot_id
869 AND i.lot_id = v_lot_id;
870
871 BEGIN
872 /* Standard Start OF API savepoint */
873 SAVEPOINT complete_pending_transaction;
874
875 /* Initialize API return status to sucess */
876 x_return_status := FND_API.G_RET_STS_SUCCESS;
877
878 /* 1.Move transaction Table record to local */
879 l_tran_rec := p_tran_rec;
880
881 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
882 THEN
883 SET_DEFAULTS
884 (
885 p_tran_rec => p_tran_rec
886 ,x_tran_rec => l_tran_rec
887 );
888
889 END IF;
890
891 /* Validate Trans Date For Posting Into Closed Periods. */
892
893
894 IF NOT CLOSE_PERIOD_CHECK
895 ( p_tran_rec => l_tran_rec,
896 p_retry_flag => l_retry_flag,
897 x_tran_rec => l_tran_rec
898 )
899 THEN
900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
901 END IF;
902
903
904 IF l_tran_rec.non_inv = 0
905 THEN
906 /* Get Previous Transaction Using Input Tran rec. */
907
908 IF NOT GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
909 (
910 p_tran_rec => l_tran_rec,
911 x_tran_fetch_rec => l_tran_fetch_rec
912 )
913 THEN
914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
915 END IF;
916
920 l_tran_fetch_rec.trans_qty2 := l_tran_fetch_rec.trans_qty2 * -1;
917 /* Negate Out Previous Transactions */
918
919 l_tran_fetch_rec.trans_qty := l_tran_fetch_rec.trans_qty * -1;
921
922 /* Reset OLD Demand */
923
924 /* ***********************************************************
925 Jalaj Srivastava Bug 2519568
926 Removed DML code for ic_summ_inv since, now ic_summ_inv is
927 a view created from the data in ic_loct_inv and ic_tran_pnd
928 *********************************************************** */
929
930 GMI_LOCT_INV_PVT.UPDATING_IC_LOCT_INV
931 (
932 p_tran_rec => l_tran_rec,
933 x_return_status =>l_return_status
934 );
935
936 /* if errors were found then Raise Exception */
937
938 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
939 THEN
940 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
941 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
942 RAISE FND_API.G_EXC_ERROR;
943 END IF;
944
945 END IF;
946
947 /* TKW B3415691 - Enhancement for Serono (Pls refer to B3599127) */
948 /* Added code to ensure the completed transaction reflected
949 the lot status and qc grade of the lot in ic_loct_inv
950 at the time the transaction took place */
951 l_item_mst_rec.item_id := l_tran_rec.item_id;
952 IF NOT gmivdbl.ic_item_mst_select (
953 p_ic_item_mst_row => l_item_mst_rec,
954 x_ic_item_mst_row => l_item_mst_rec
955 ) THEN
956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
957 END IF;
958
959 IF (l_item_mst_rec.status_ctl > 0 ) THEN
960 OPEN Cur_lot_status(
961 l_tran_rec.item_id,
962 l_tran_rec.whse_code,
963 l_tran_rec.lot_id,
964 l_tran_rec.location);
965 FETCH Cur_lot_status INTO l_tran_rec.lot_status;
966 CLOSE Cur_lot_status;
967 END IF;
968
969 IF (l_item_mst_rec.grade_ctl > 0 ) THEN
970 OPEN Cur_qc_grade(l_tran_rec.lot_id);
971 FETCH Cur_qc_grade INTO l_tran_rec.qc_grade;
972 CLOSE Cur_qc_grade;
973 END IF;
974
975 /* Call Create_pending_transaction to build ic_tran_pnd%rowtype; */
976
977 COMPLETED_TRANSACTION_BUILD
978 ( p_tran_rec => l_tran_rec
979 , x_tran_row => l_tran_row
980 , x_return_status => l_return_status
981 );
982
983 IF NOT GMI_TRAN_PND_DB_PVT.UPDATE_IC_TRAN_PND ( p_tran_row => l_tran_row)
984 THEN
985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
986 END IF;
987
988 /* Standard Check of p_commit.*/
989 IF FND_API.to_boolean(p_commit)
990 THEN
991 COMMIT WORK;
992 END IF;
993
994 x_tran_row := l_tran_row;
995
996 EXCEPTION
997 WHEN FND_API.G_EXC_ERROR THEN
998 ROLLBACK TO complete_pending_transaction;
999 x_return_status := FND_API.G_RET_STS_ERROR;
1000 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1001 , p_count => x_msg_count
1002 , p_data => x_msg_data
1003 );
1004
1005 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1006 ROLLBACK TO complete_pending_transaction;
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1009 , p_count => x_msg_count
1010 , p_data => x_msg_data
1011 );
1012 WHEN OTHERS THEN
1013 ROLLBACK TO complete_pending_transaction;
1014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1015
1016 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1017 , l_api_name
1018 );
1019
1020 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1021 , p_count => x_msg_count
1022 , p_data => x_msg_data
1023 );
1024
1025 END UPDATE_PENDING_TO_COMPLETED;
1026
1027
1028 PROCEDURE PENDING_TRANSACTION_BUILD
1029 ( p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
1030 , x_tran_row OUT NOCOPY IC_TRAN_PND%ROWTYPE
1031 , x_return_status OUT NOCOPY VARCHAR2
1032 )
1033 IS
1034 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
1035 l_tran_row IC_TRAN_PND%ROWTYPE;
1036 BEGIN
1037 x_tran_row.trans_id := p_tran_rec.trans_id;
1038 x_tran_row.item_id := p_tran_rec.item_id;
1039 x_tran_row.line_id := p_tran_rec.line_id;
1040 x_tran_row.co_code := p_tran_rec.co_code;
1041 x_tran_row.orgn_code := p_tran_rec.orgn_code;
1042 x_tran_row.whse_code := p_tran_rec.whse_code;
1043 x_tran_row.lot_id := NVL(p_tran_rec.lot_id,0);
1044 x_tran_row.location := NVL(p_tran_rec.location,
1045 GMIGUTL.IC$DEFAULT_LOCT);
1046 x_tran_row.doc_type := p_tran_rec.doc_type;
1047 x_tran_row.doc_id := p_tran_rec.doc_id;
1048 x_tran_row.doc_line := NVL(p_tran_rec.doc_line,0);
1049 x_tran_row.line_type := NVL(p_tran_rec.line_type,0);
1050 x_tran_row.creation_date := SYSDATE;
1051 x_tran_row.trans_date := NVL(p_tran_rec.trans_date,SYSDATE);
1052 x_tran_row.trans_qty := p_tran_rec.trans_qty;
1053 x_tran_row.trans_qty2 := p_tran_rec.trans_qty2;
1054 x_tran_row.qc_grade := p_tran_rec.qc_grade;
1055 x_tran_row.lot_status := p_tran_rec.lot_status;
1056 x_tran_row.trans_stat := p_tran_rec.trans_stat;
1057 x_tran_row.trans_um := p_tran_rec.trans_um;
1058 x_tran_row.trans_um2 := p_tran_rec.trans_um2;
1059 x_tran_row.op_code := p_tran_rec.user_id;
1063 x_tran_row.event_id := NVL(p_tran_rec.event_id,0);
1060 x_tran_row.completed_ind := 0; /* Always 0 For Pending */
1061 x_tran_row.staged_ind := p_tran_rec.staged_ind;
1062 x_tran_row.gl_posted_ind := 0; /* Always 0 */
1064 x_tran_row.delete_mark := 0; /* Always 0 */
1065 x_tran_row.text_code := p_tran_rec.text_code;
1066 x_tran_row.last_update_date := SYSDATE;
1067 x_tran_row.last_updated_by := p_tran_rec.user_id;
1068 x_tran_row.created_by := p_tran_rec.user_id;
1069 x_tran_row.line_detail_id := NVL(p_tran_rec.line_detail_id,NULL);
1070 /*================================================
1071 Joe DiIorio 10/22/2001 11.5.1H BUG#2064443
1072 Added reason code assigment.
1073 =============================================*/
1074 x_tran_row.reason_code := p_tran_rec.reason_code;
1075 /*============================================
1076 BUG#3090255 Populated intorder_posted_ind
1077 ==========================================*/
1078 x_tran_row.intorder_posted_ind := NVL(p_tran_rec.intorder_posted_ind,0);
1079 x_return_status := l_return_status;
1080
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083
1084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1085
1086 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1087 ,'PENDING_TRANSACTION_BUILD'
1088 );
1089
1090
1091 END PENDING_TRANSACTION_BUILD;
1092
1093 PROCEDURE COMPLETED_TRANSACTION_BUILD
1094 ( p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
1095 , x_tran_row OUT NOCOPY IC_TRAN_PND%ROWTYPE
1096 , x_return_status OUT NOCOPY VARCHAR2
1097 )
1098 IS
1099 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
1100 l_tran_row IC_TRAN_PND%ROWTYPE;
1101
1102 BEGIN
1103
1104 x_tran_row.trans_id := p_tran_rec.trans_id;
1105 x_tran_row.item_id := p_tran_rec.item_id;
1106 x_tran_row.line_id := p_tran_rec.line_id;
1107 x_tran_row.co_code := p_tran_rec.co_code;
1108 x_tran_row.orgn_code := p_tran_rec.orgn_code;
1109 x_tran_row.whse_code := p_tran_rec.whse_code;
1110 x_tran_row.lot_id := NVL(p_tran_rec.lot_id,0);
1111 x_tran_row.location := NVL(p_tran_rec.location,
1112 GMIGUTL.IC$DEFAULT_LOCT);
1113 x_tran_row.doc_type := p_tran_rec.doc_type;
1114 x_tran_row.doc_id := p_tran_rec.doc_id;
1115 x_tran_row.doc_line := NVL(p_tran_rec.doc_line,0);
1116 x_tran_row.line_type := NVL(p_tran_rec.line_type,0);
1117 /* NC 8/16/02 Commenting the below line. There's no need to update the
1118 creation date when completing the pending transaction. The creation_date
1119 should always reflect the date when the transaction is created.The DML
1120 update statement had already been commented in GMI_TRAN_PND_DB_PVT.update_ic_tran_pnd for bug#2385934)
1121 x_tran_row.creation_date := NVL(p_tran_rec.trans_date,SYSDATE);
1122 */
1123 x_tran_row.trans_date := NVL(p_tran_rec.trans_date,SYSDATE);
1124 x_tran_row.trans_qty := p_tran_rec.trans_qty;
1125 x_tran_row.trans_qty2 := p_tran_rec.trans_qty2;
1126 x_tran_row.qc_grade := p_tran_rec.qc_grade;
1127 x_tran_row.lot_status := p_tran_rec.lot_status;
1128 x_tran_row.trans_stat := p_tran_rec.trans_stat;
1129 x_tran_row.trans_um := p_tran_rec.trans_um;
1130 x_tran_row.trans_um2 := p_tran_rec.trans_um2;
1131 x_tran_row.op_code := p_tran_rec.user_id;
1132 x_tran_row.completed_ind := 1; /* Always 1 For Completed */
1133 x_tran_row.staged_ind := p_tran_rec.staged_ind;
1134 x_tran_row.gl_posted_ind := 0; /* Always 0 */
1135 x_tran_row.event_id := NVL(p_tran_rec.event_id,0);
1136 x_tran_row.delete_mark := 0; /* NVL(p_tran_rec.delete_mark,0);*/
1137 x_tran_row.text_code := p_tran_rec.text_code;
1138 x_tran_row.last_update_date := SYSDATE;
1139 x_tran_row.last_updated_by := p_tran_rec.user_id;
1140 x_tran_row.created_by := p_tran_rec.user_id;
1141 x_tran_row.line_detail_id := NVL(p_tran_rec.line_detail_id,NULL);
1142 /*================================================
1143 Joe DiIorio 10/22/2001 11.5.1H BUG#2064443
1144 Added reason code assigment.
1145 =============================================*/
1146 x_tran_row.reason_code := p_tran_rec.reason_code;
1147 /*============================================
1148 BUG#3090255 Populated intorder_posted_ind
1149 ==========================================*/
1150 x_tran_row.intorder_posted_ind := NVL(p_tran_rec.intorder_posted_ind,0);
1151 x_return_status := l_return_status;
1152 /*============================================
1153 BUG#3526733 Populated reverse_id
1154 ==========================================*/
1155 x_tran_row.reverse_id := p_tran_rec.reverse_id;
1156
1157
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160
1161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1162
1163 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1164 ,'COMPLETED_TRANSACTION_BUILD'
1165 );
1166
1167
1168
1169
1170 END COMPLETED_TRANSACTION_BUILD;
1171
1172 PROCEDURE SET_DEFAULTS
1173 ( p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
1174 , x_tran_rec OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
1175 )
1176 IS
1177
1178 CURSOR c_get_noninv_ind ( p_item_id IN NUMBER)
1179 IS
1180 SELECT noninv_ind
1181 FROM ic_item_mst
1182 WHERE item_id = p_item_id;
1183
1184 l_noninv NUMBER;
1185
1186 BEGIN
1187 /* This Procedure will default Values Into Rec Type */
1188 /* If declared as NULL: */
1189
1190 x_tran_rec := p_tran_rec;
1191
1192 IF p_tran_rec.trans_date is NULL THEN
1193 x_tran_rec.trans_date :=SYSDATE;
1194 END IF;
1195
1199 --Jalaj Srivastava Bug 2483644
1196 IF p_tran_rec.user_id is NULL THEN
1197 x_tran_rec.user_id :=FND_GLOBAL.user_id;
1198 END IF;
1200 --C routines were using non_inv as 2 for grade txns
1201 --that is no longer required as the code will check for
1202 --GRDI, GRDR doc type
1203 --this is only for records created before PL/SQL APIs
1204 --were used for posting the journals.
1205 IF (p_tran_rec.non_inv is NULL OR p_tran_rec.non_inv = 2) THEN
1206 -- derive the value
1207 -- H Verdding Bug 2025933
1208
1209 OPEN c_get_noninv_ind(p_tran_rec.item_id);
1210 FETCH c_get_noninv_ind INTO l_noninv;
1211 CLOSE c_get_noninv_ind;
1212
1213 x_tran_rec.non_inv := l_noninv;
1214
1215 END IF;
1216
1217 IF p_tran_rec.staged_ind is NULL THEN
1218 x_tran_rec.staged_ind :=0;
1219 END IF;
1220
1221 IF p_tran_rec.trans_qty2 = FND_API.G_MISS_NUM THEN
1222 x_tran_rec.trans_qty2 := NULL;
1223 END IF;
1224
1225 END SET_DEFAULTS;
1226
1227
1228 FUNCTION CLOSE_PERIOD_CHECK
1229 (
1230 p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec,
1231 p_retry_flag IN NUMBER,
1232 x_tran_rec IN OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
1233 )
1234 RETURN BOOLEAN
1235 IS
1236
1237 l_return_val NUMBER :=0;
1238 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
1239
1240 BEGIN
1241
1242 /* This is a Encapsulated version of the period close
1243 validaton routine that exists in GMI, it currently only
1244 returns a number rather that populating the message stack
1245 I will only check for and report closed period posting
1246 of transactions. However we can add other validations later
1247 when required.
1248 */
1249
1250 /* Assume that all values for Rec are correct , this is validated
1251 in the public layer */
1252
1253 l_tran_rec := p_tran_rec;
1254
1255 /* Validate Trans Date For Posting Into Closed Periods */
1256
1257
1258 l_return_val := GMICCAL.trans_date_validate ( l_tran_rec.trans_date,
1259 l_tran_rec.orgn_code,
1260 l_tran_rec.whse_code
1261 );
1262
1263 /*==============================================
1264 Thomas Daniel 04/17/2002 11.5.1I BUG#2322973
1265 Only check for the sysdate if the calendar is
1266 closed for the trans date passed in
1267 ============================================*/
1268 IF (l_return_val IN (GMICCAL.INVCAL_PERIOD_CLOSED,
1269 GMICCAL.INVCAL_WHSE_CLOSED)) THEN
1270
1271
1272 IF p_retry_flag = 1 THEN
1273
1274 -- Set the trans date to sysdate and re-try
1275
1276 l_tran_rec.trans_date := SYSDATE;
1277
1278 l_return_val := GMICCAL.trans_date_validate
1279 (l_tran_rec.trans_date,
1280 l_tran_rec.orgn_code,
1281 l_tran_rec.whse_code
1282 );
1283 END IF;
1284
1285 END IF;
1286
1287 IF l_return_val <> 0 THEN
1288 /*==============================================
1289 Thomas Daniel 04/17/2002 11.5.1I BUG#2322973
1290 Added specific messages for each return from
1291 the calendar validation.
1292 ============================================*/
1293 IF l_return_val = GMICCAL.INVCAL_FISCALYR_ERR THEN
1294 FND_MESSAGE.SET_NAME('GMI','IC_CAL_FISCALYR_ERR');
1295 FND_MSG_PUB.Add;
1296 ELSIF l_return_val = GMICCAL.INVCAL_PERIOD_ERR THEN
1297 FND_MESSAGE.SET_NAME('GMI','IC_CAL_CLOSED_IND_ERR');
1298 FND_MSG_PUB.Add;
1299 ELSIF l_return_val = GMICCAL.INVCAL_ORGN_PARM_ERR THEN
1300 FND_MESSAGE.SET_NAME('GMI','IC_INVALID_ORGN_ERR');
1301 FND_MSG_PUB.Add;
1302 ELSIF l_return_val = GMICCAL.INVCAL_CO_ERR THEN
1303 FND_MESSAGE.SET_NAME('GMI','IC_COCODEERR');
1304 FND_MSG_PUB.Add;
1305 ELSIF l_return_val = GMICCAL.INVCAL_WHSE_PARM_ERR THEN
1306 FND_MESSAGE.SET_NAME('GMI','IC_BLANKWHSE');
1307 FND_MSG_PUB.Add;
1308 ELSIF l_return_val = GMICCAL.INVCAL_WHSE_ERR THEN
1309 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_WHSE_CODE');
1310 FND_MESSAGE.SET_TOKEN('WHSE_CODE',l_tran_rec.whse_code);
1311 FND_MSG_PUB.Add;
1312 ELSIF l_return_val IN (GMICCAL.INVCAL_PERIOD_CLOSED,
1313 GMICCAL.INVCAL_WHSE_CLOSED) THEN
1314 /*==============================================
1315 Joe DiIorio 04/08/2002 11.5.1I BUG#2248778
1316 ============================================*/
1317 FND_MESSAGE.SET_NAME('GMI','IC_API_TXN_POST_CLOSED');
1318 FND_MESSAGE.SET_TOKEN('DATE',l_tran_rec.trans_date);
1319 FND_MESSAGE.SET_TOKEN('WAREH',l_tran_rec.whse_code);
1320 FND_MSG_PUB.Add;
1321 ELSE
1322 FND_MESSAGE.SET_NAME('GMI','ICCAL_GENL_ERR');
1323 FND_MSG_PUB.Add;
1324 END IF;
1325
1326 RETURN FALSE;
1327 END IF;
1328
1329 x_tran_rec := l_tran_rec;
1330
1331 RETURN TRUE;
1332
1333 EXCEPTION
1334
1335 WHEN OTHERS THEN
1336
1337 FND_MESSAGE.SET_NAME('GMI','UNEXPECTED ERROR CHECK MISSING');
1338
1339 RETURN FALSE;
1340
1341
1342 END close_period_check;
1343
1344
1345
1346 END GMI_TRANS_ENGINE_PVT;