[Home] [Help]
PACKAGE BODY: APPS.GML_RECV_TRANS_PKG
Source
1 PACKAGE BODY GML_RECV_TRANS_PKG AS
2 /* $Header: GMLRTRNB.pls 120.1 2005/08/15 09:23:47 rakulkar noship $ */
3
4
5 /*========================================================================+
6 | PROCEDURE gml_insert_recv_interface |
7 | |
8 | DESCRIPTION The procedure inserts data into the |
9 | RCV_HEADERS_INTERFACE and |
10 | RCV_TRANSACTIONS_INTERFACE tables |
11 | |
12 | MODIFICATION HISTORY |
13 | 10-MAR-99 Tony Ricci Created. |
14 | 03-NOV-99 NC Modified the opm_vendor_cur to get of_vendor_id based|
15 | on shipvend_id from po_ordr_hdr instead of po_recv_hdr.
16 | The original code was populating the rcv_headers_interface
17 | table with blank vendor_id and causing the Receipt |
18 | Transaction Processor to fail. |
19 | 31-MAY-00 NC Added code to create deliveries on apps side. |
23 | effort to eliminated redundancy in code.
20 | A delivery transaction is now created automatically | | with each receipt transaction. Bug#1098066 |
21 | 12-OCT-00 NC Pay on receipt enhancements. Bug#1518114.Replaced the | | INSERTS into rcv_headers_interface and rcv_transactions_
22 | interface by calles to gml_new_rcv_trans_insert in an |
24 | 10-DEC-01 Uday Phadtare Bug#2007945 Added parameter p_dtl_recv_date to procedure
25 | gml_insert_recv_interface so that transaction_date in rcv_transactions_interface
26 | is populated with this date.
27 +========================================================================*/
28
29 PROCEDURE gml_insert_recv_interface(p_recv_id IN NUMBER, p_line_id IN NUMBER,
30 p_po_id IN NUMBER, p_poline_id IN NUMBER,
31 p_opm_item_id IN NUMBER, p_recv_qty1 IN NUMBER,
32 p_recv_um1 IN VARCHAR2, p_dtl_recv_date IN DATE DEFAULT SYSDATE) AS
33
34 v_po_id po_ordr_hdr.po_id%TYPE;
35 v_poline_id po_ordr_dtl.line_id%TYPE;
36
37 v_recv_id po_recv_hdr.recv_id%TYPE;
38 v_recv_no po_recv_hdr.recv_no%TYPE;
39 v_created_by po_recv_hdr.created_by%TYPE;
40 v_last_updated_by po_recv_hdr.last_updated_by%TYPE;
41
42 v_line_id po_recv_dtl.line_id%TYPE;
43 v_recv_qty1 po_recv_dtl.recv_qty1%TYPE;
44 v_recv_um1 po_recv_dtl.recv_um1%TYPE;
45 v_dtl_recv_date po_recv_dtl.recv_date%TYPE;
46
47 v_apps_po_header_id po_headers_all.po_header_id%TYPE;
48 v_apps_po_line_id po_lines_all.po_line_id%TYPE;
49 v_apps_po_line_location_id po_line_locations_all.line_location_id%TYPE;
50 v_ship_to_organization_id po_line_locations_all.ship_to_organization_id%TYPE;
51
52 new_header_interface_id rcv_headers_interface.header_interface_id%TYPE;
53 new_group_id rcv_headers_interface.group_id%TYPE;
54 new_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
55
56 retcode NUMBER;
57
58 err_num NUMBER;
59 err_msg VARCHAR2(100);
60
61 CURSOR RCV_HEADER_INT_CUR IS
62 SELECT RCV_HEADERS_INTERFACE_S.nextval
63 FROM sys.dual;
64
65 CURSOR RCV_TRANS_INT_CUR IS
66 SELECT RCV_TRANSACTIONS_INTERFACE_S.nextval
67 FROM sys.dual;
68
69 CURSOR RCV_INT_GROUPS_CUR IS
70 SELECT RCV_INTERFACE_GROUPS_S.nextval
71 FROM sys.dual;
72
73 v_rcv_receipt_num po_recv_hdr.recv_no%TYPE;
74 v_recv_exists VARCHAR2(100);
75
76 CURSOR opm_oragems_cur(vc_po_id NUMBER, vc_poline_id NUMBER) IS
77 SELECT po_header_id, po_line_id, po_line_location_id
78 FROM cpg_oragems_mapping
79 WHERE po_id = vc_po_id AND
80 line_id = vc_poline_id;
81
82 CURSOR po_line_loc_cur(vc_apps_po_header_id NUMBER,
83 vc_apps_po_line_id NUMBER,
84 vc_apps_po_line_location_id NUMBER) IS
85 SELECT ship_to_organization_id
86 FROM po_line_locations_all
87 WHERE po_header_id = vc_apps_po_header_id AND
88 po_line_id = vc_apps_po_line_id AND
89 line_location_id = vc_apps_po_line_location_id;
90
91 CURSOR check_map_table IS
92 SELECT group_id,rcv_receipt_num
93 FROM gml_recv_trans_map
94 WHERE recv_id = v_recv_id
95 AND organization_id = v_ship_to_organization_id
96 AND rcv_receipt_num is not null;
97
98 CURSOR Get_recv_no IS
99 SELECT to_char(next_receipt_num + 1 )
100 FROM rcv_parameters
101 WHERE organization_id = v_ship_to_organization_id;
102
103 x_header_interface_id NUMBER;
104 x_group_id NUMBER;
105 NO_MAPPING EXCEPTION;
106
107 BEGIN
108
109 v_po_id := p_po_id;
110 v_poline_id := p_poline_id;
111 v_recv_id := p_recv_id;
112 v_line_id := p_line_id;
113 v_recv_qty1 := p_recv_qty1;
114 v_recv_um1 := p_recv_um1;
115 v_dtl_recv_date := p_dtl_recv_date;
116
117 v_created_by := FND_PROFILE.VALUE ('USER_ID');
118 v_last_updated_by := FND_PROFILE.VALUE ('USER_ID');
119
120 /* Do not process Stock Receipts */
121 IF v_po_id IS NULL THEN
122 RETURN;
123 END IF;
124
125 OPEN opm_oragems_cur(v_po_id, v_poline_id);
126 FETCH opm_oragems_cur INTO v_apps_po_header_id, v_apps_po_line_id,
127 v_apps_po_line_location_id;
128 CLOSE opm_oragems_cur;
129
130
131 OPEN po_line_loc_cur(v_apps_po_header_id, v_apps_po_line_id,
132 v_apps_po_line_location_id);
133 FETCH po_line_loc_cur INTO v_ship_to_organization_id;
134 CLOSE po_line_loc_cur;
135
136 BEGIN
137 IF G_recv_id = v_recv_id THEN
138
139 Open check_map_table;
140 Fetch check_map_table into x_group_id,v_rcv_receipt_num;
141 if check_map_table%NOTFOUND
142 then
143 Close check_map_table ;
144
145 OPEN get_recv_no ;
146 FETCH get_recv_no into v_rcv_receipt_num;
147 IF get_recv_no%NOTFOUND
148 THEN
149 Close get_recv_no;
150 Raise NO_MAPPING;
151 end if;
152 Close get_recv_no;
153
154
155 UPDATE rcv_parameters
156 set next_receipt_num = v_rcv_receipt_num
160 Close check_map_table ;
157 where organization_id = v_ship_to_organization_id ;
158
159 else
161
162 select header_interface_id
163 into x_header_interface_id
164 from rcv_headers_interface
165 where group_id = x_group_id;
166
167 /* B2007945 v_dtl_recv_date added to call */
168 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,
169 p_line_id,
170 p_po_id, p_poline_id,
171 p_opm_item_id, p_recv_qty1,0,
172 p_recv_um1,NULL,0,NULL,
173 x_header_interface_id,
174 x_group_id,
175 v_rcv_receipt_num,0,v_dtl_recv_date);
176 RETURN;
177 end if;
178
179 ELSE
180
181 OPEN get_recv_no ;
182 FETCH get_recv_no into v_rcv_receipt_num;
183 IF get_recv_no%NOTFOUND
184 THEN
185 Close get_recv_no;
186 Raise NO_MAPPING;
187 end if;
188 Close get_recv_no;
189
190
191 UPDATE rcv_parameters
192 set next_receipt_num = v_rcv_receipt_num
193 where organization_id = v_ship_to_organization_id ;
194
195
196 END IF;
197
198 Exception
199 When NO_MAPPING then
200 err_msg := 'Receiving Parameters not setup for this Inventory Organization '|| to_char(v_ship_to_organization_id);
201 RAISE_APPLICATION_ERROR(-20000, err_msg);
202
203 When Others then
204 err_num := SQLCODE;
205 err_msg := SUBSTRB(SQLERRM, 1, 100);
206 RAISE_APPLICATION_ERROR(-20000, err_msg);
207
208 END;
209
210 OPEN RCV_HEADER_INT_CUR;
211 FETCH RCV_HEADER_INT_CUR INTO new_header_interface_id;
212 CLOSE RCV_HEADER_INT_CUR;
213
214 OPEN RCV_INT_GROUPS_CUR;
215 FETCH RCV_INT_GROUPS_CUR INTO new_group_id;
216 CLOSE RCV_INT_GROUPS_CUR;
217
218 /* Header Insert */
219 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,
220 p_line_id,
221 p_po_id, p_poline_id,
222 p_opm_item_id, p_recv_qty1,0,
223 p_recv_um1,NULL,0,NULL,
224 new_header_interface_id,
225 new_group_id,
226 v_rcv_receipt_num,1);
227 /* Transaction Insert */
228 /* B2007945 v_dtl_recv_date added to call */
229
230 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,
231 p_line_id,
232 p_po_id, p_poline_id,
233 p_opm_item_id, p_recv_qty1,0,
234 p_recv_um1,NULL,0,NULL,
235 new_header_interface_id,
236 new_group_id,
237 v_rcv_receipt_num,0,v_dtl_recv_date);
238
239 /* IF G_rows_inserted = 1 THEN
240 gml_recv_trans_pkg.gml_process_adjust_errors(retcode);
241 END IF; */
242
243 EXCEPTION
244
245 WHEN OTHERS THEN
246 err_num := SQLCODE;
247 err_msg := SUBSTRB(SQLERRM, 1, 100);
248 RAISE_APPLICATION_ERROR(-20000, err_msg);
249
250 END gml_insert_recv_interface;
251
252 /*========================================================================+
253 | PROCEDURE gml_adjust_recv_trans |
254 | |
255 | DESCRIPTION The procedure updates data into the |
256 | RCV_TRANSACTIONS, RCV_SHIPMENT_LINES and |
257 | RCV_SUPPLY tables |
258 | |
259 | MODIFICATION HISTORY |
260 | 04-MAY-99 Tony Ricci Created. |
261 | 23-NOV-99 NC Modified.
262 | 12-OCT-00 NC Pay on receipt enhancements Bug#1518144.
263 | Replaced direct INSERT into rcv_transactions by calls
264 | to gml_new_rcv_trans_insert() by passing appropriate |
265 | parameters for different types of adjustments.
266 +========================================================================*/
267
268 PROCEDURE gml_adjust_recv_trans(p_recv_id IN NUMBER, p_line_id IN NUMBER,
269 p_po_id IN NUMBER, p_poline_id IN NUMBER,
270 p_opm_item_id IN NUMBER, p_recv_qty1 IN NUMBER,
271 p_old_recv_qty1 IN NUMBER,
272 p_recv_um1 IN VARCHAR2,
273 p_return_ind IN NUMBER,
274 p_recv_status IN NUMBER,
275 p_net_price IN NUMBER,
276 p_rtrn_void_ind IN NUMBER) AS
277
278
279 X_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
283 v_return_quantity rcv_transactions.quantity%TYPE;
280 v_deliver_transaction_id rcv_transactions.transaction_id%TYPE;
281 v_transaction_id rcv_transactions.transaction_id%TYPE;
282 v_void_ret_parent_id rcv_transactions.transaction_id%TYPE;
284 v_return_adj_qty rcv_transactions.quantity%TYPE DEFAULT 0;
285 v_trans_id1 rcv_transactions.transaction_id%TYPE;
286 v_trans_id2 rcv_transactions.transaction_id%TYPE;
287 v_destination_type1 rcv_transactions.destination_type_code%TYPE;
288 v_destination_type2 rcv_transactions.destination_type_code%TYPE;
289 v_recv_qty1 rcv_transactions.quantity%TYPE;
290 v_old_recv_qty1 rcv_transactions.quantity%TYPE;
291
292 adjust_err_ind NUMBER DEFAULT 0;
293 X_progress VARCHAR2(4);
294
295 CURSOR get_trans_id_cur(vc_recv_id NUMBER, vc_line_id NUMBER) IS
296 SELECT interface_transaction_id
297 FROM gml_recv_trans_map
298 WHERE recv_id = vc_recv_id AND
299 line_id = vc_line_id;
300
301 CURSOR RCV_TRANS_DELIVER_CUR(v_transaction_id NUMBER,
302 v_transaction_type VARCHAR2,
303 v_interface_transaction_id NUMBER) IS
304 SELECT transaction_id
305 FROM rcv_transactions
306 WHERE parent_transaction_id = v_transaction_id AND
307 transaction_type = v_transaction_type AND
308 interface_transaction_id = v_interface_transaction_id;
309
310 CURSOR RCV_TRANS_VOID_RET_CUR(v_transaction_id NUMBER,
311 v_transaction_type VARCHAR2) IS
312 SELECT transaction_id,quantity
313 FROM rcv_transactions
314 WHERE parent_transaction_id = v_transaction_id AND
315 transaction_type = v_transaction_type;
316
317 CURSOR trans_cur(X_interface_transaction_id NUMBER) IS
318 SELECT transaction_id
319 FROM rcv_transactions
320 WHERE interface_transaction_id = X_interface_transaction_id AND
321 transaction_type = 'RECEIVE';
322
323 BEGIN
324
325 /* Do not process Stock Receipts */
326 IF p_po_id IS NULL THEN
327 RETURN;
328 END IF;
329
330 OPEN get_trans_id_cur(p_recv_id, p_line_id);
331 FETCH get_trans_id_cur INTO X_interface_transaction_id;
332
333 IF get_trans_id_cur%NOTFOUND THEN
334 adjust_err_ind := 1;
335 END IF;
336
337 CLOSE get_trans_id_cur;
338
339 OPEN trans_cur(X_interface_transaction_id);
340 FETCH trans_cur INTO v_transaction_id;
341 IF trans_cur%NOTFOUND THEN
342 adjust_err_ind := 1;
343 END IF;
344
345 CLOSE trans_cur;
346
347 IF adjust_err_ind = 1 THEN
348 IF G_adjust_mode = 'NORMAL' THEN
349
350 gml_recv_trans_pkg.gml_insert_adjust_error(p_recv_id, p_line_id,
351 p_recv_qty1, p_old_recv_qty1,
352 p_recv_um1,p_return_ind,
353 p_recv_status,
354 p_rtrn_void_ind);
355 END IF;
356
357 RETURN;
358 END IF;
359
360 X_progress := '010';
361
362 fnd_global.APPS_INITIALIZE (1001, 50003, 201);
363 /* fnd_global.APPS_INITIALIZE (X_created_by, 50003, 201); */
364
365 OPEN RCV_TRANS_DELIVER_CUR(v_transaction_id, 'DELIVER', X_interface_transaction_id);
366 FETCH RCV_TRANS_DELIVER_CUR INTO v_deliver_transaction_id;
367 CLOSE RCV_TRANS_DELIVER_CUR;
368
369 IF(p_return_ind = 2) THEN /* If this is a correction to return */
370 v_return_adj_qty := p_old_recv_qty1 - p_recv_qty1;
371 END IF;
372
373 /* If this is a void return or a -ve adjustment to the return */
374
375 IF(p_rtrn_void_ind = 1 OR (p_return_ind = 2 AND v_return_adj_qty > 0 ) ) THEN
376
377 OPEN RCV_TRANS_VOID_RET_CUR(v_transaction_id, 'RETURN TO VENDOR');
378 FETCH RCV_TRANS_VOID_RET_CUR INTO v_void_ret_parent_id, v_return_quantity;
379 CLOSE RCV_TRANS_VOID_RET_CUR;
380
381 IF(v_return_adj_qty >0 ) THEN
382 v_return_quantity := v_return_adj_qty;
383 END IF;
384
385 /* We will in this case, post Two transactions,
386 1) One As a -ve correction to return back to Receiving.
387 2) Second to deliver that correction back to Inventory. */
388
389 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
390 p_po_id,p_poline_id,
391 p_opm_item_id,-(v_return_quantity),0,
392 p_recv_um1,'CORRECT',v_void_ret_parent_id,
393 'RECEIVING',0,0,NULL,0);
394 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
395 p_po_id,p_poline_id,
396 p_opm_item_id,v_return_quantity,0,
397 p_recv_um1,'DELIVER',v_transaction_id,
398 'INVENTORY',0,0,NULL,0);
399
400 /* For voiding of receipts we'll post two transactions.
401 1) First a -ve correction from Inventory
402 2) Second, a -ve 'Correction' for Receiving.
403 */
404 ELSIF (p_recv_status = -1 ) THEN
405 v_recv_qty1 := - (p_recv_qty1);
406 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
407 p_po_id,p_poline_id,
408 p_opm_item_id,v_recv_qty1,0,
409 p_recv_um1,'CORRECT',v_deliver_transaction_id,
410 'INVENTORY',0,0,NULL,0);
411 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
412 p_po_id,p_poline_id,
413 p_opm_item_id,v_recv_qty1,0,
414 p_recv_um1,'CORRECT',v_transaction_id,
415 'RECEIVING',0,0,NULL,0);
416
417 /* Return is pretty straight forward. We insert one
418 transaction 'Return To Vendor' from Inventory. and This
422
419 creates 1) Return to Receiving from Inventory and
420 2) Return to vendor from Receiving in rcv_transactions.
421 */
423 ELSIF (p_return_ind = 1) THEN
424
425 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
426 p_po_id,p_poline_id,
427 p_opm_item_id,p_recv_qty1,0,
428 p_recv_um1,'RETURN TO VENDOR',v_deliver_transaction_id,
429 'INVENTORY',0,0,NULL,0);
430
431 ELSE /* Normal Correction to receipt and deliver transactions */
432 /* Or a +ve adjustment to return */
433 /* If the correction is -ve then post the Inventory Correction
434 first and Receiving Correction Later */
435
436 IF ( (p_recv_qty1 - p_old_recv_qty1) < 0 OR p_return_ind = 2 ) THEN
437 v_destination_type1 := 'INVENTORY';
438 v_trans_id1 := v_deliver_transaction_id;
439 v_destination_type2 := 'RECEIVING';
440 v_trans_id2 := v_transaction_id;
441 ELSE
442 v_destination_type1 := 'RECEIVING';
443 v_trans_id1 := v_transaction_id;
444 v_destination_type2 := 'INVENTORY';
445 v_trans_id2 := v_deliver_transaction_id;
446 END IF;
447
448 IF(p_return_ind = 2) THEN /* Negate the quantities if this a a return */
449 v_recv_qty1 := -(p_recv_qty1);
450 v_old_recv_qty1 := -(p_old_recv_qty1);
451 ELSE
452 v_recv_qty1 := p_recv_qty1;
453 v_old_recv_qty1 := p_old_recv_qty1;
454 END IF;
455
456 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
457 p_po_id,p_poline_id,
458 p_opm_item_id,v_recv_qty1,v_old_recv_qty1,
459 p_recv_um1,'CORRECT',v_trans_id1,
460 v_destination_type1,0,0,NULL,0);
461
462 gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
463 p_po_id,p_poline_id,
464 p_opm_item_id,v_recv_qty1,v_old_recv_qty1,
465 p_recv_um1,'CORRECT',v_trans_id2,
466 v_destination_type2,0,0,NULL,0 );
467 END IF;
468
469 EXCEPTION
470
471 WHEN others THEN
472 po_message_s.sql_error('gml_adjust_recv_trans', X_progress,
473 sqlcode);
474 RAISE;
475
476 END gml_adjust_recv_trans;
477
478 /*========================================================================+
479 | PROCEDURE gml_store_return_qty |
480 | |
481 | DESCRIPTION The procedure stores the return_qty1 and |
482 | return_um1 values into global package vars for use in |
483 | gml_adjust_recv_trans proc |
484 | |
485 | MODIFICATION HISTORY |
486 | 17-MAY-99 Tony Ricci Created. |
487 | |
488 +========================================================================*/
489
490 PROCEDURE gml_store_return_qty(p_return_qty1 IN NUMBER,
491 p_return_um1 IN VARCHAR2) AS
492
493 X_progress VARCHAR2(4);
494
495 BEGIN
496 X_progress := '010';
497 G_return_qty1 := p_return_qty1;
498 G_return_um1 := p_return_um1;
499
500 EXCEPTION
501
502 WHEN others THEN
503 po_message_s.sql_error('gml_store_return_qty', X_progress, sqlcode);
504
505 RAISE;
506
507 END gml_store_return_qty;
508
509 /*========================================================================+
510 | PROCEDURE gml_insert_adjust_error |
511 | |
512 | DESCRIPTION The procedure inserts a row into the |
513 | GML_RECV_ADJUST_ERRORS table to indicate that an |
514 | adjustment/return was made in OPM to a receipt that does |
515 | not have a corresponding transaction in Oracle Receiving |
516 | |
517 | MODIFICATION HISTORY |
518 | 18-MAY-99 Tony Ricci Created. |
519 | 12-OCT-00 NC Pay on receipt enhancements.Bug#1518114 Added new |
520 | parameters and columns in insert statement to reflect |
521 | the added columns in gml_recv_adjust_errors table. |
522 | 20-AUG-01 P. Arvind Dath BUG#1938430 |
523 | Modified code to retrieve the max sequence number for a |
524 | given recv_id and line_id combination, to avoid primary |
525 | key voilation errors on the 'gml_recv_adjust_errors' table|
526 +========================================================================*/
527
528 PROCEDURE gml_insert_adjust_error(p_recv_id IN NUMBER, p_line_id IN NUMBER,
529 p_recv_qty1 IN NUMBER,
530 p_old_recv_qty1 IN NUMBER,
531 p_recv_um1 IN VARCHAR2,
532 p_return_ind IN NUMBER,
533 p_recv_status IN NUMBER,
534 p_rtrn_void_ind IN NUMBER) AS
535
536 X_progress VARCHAR2(4);
537 X_seq_no gml_recv_adjust_errors.seq_no%TYPE DEFAULT 0;
538
539 v_created_by gml_recv_adjust_errors.created_by%TYPE;
540 v_last_updated_by gml_recv_adjust_errors.last_updated_by%TYPE;
544 CURSOR get_seq_no_cur(vc_recv_id NUMBER, vc_line_id NUMBER) IS
541
542 -- BEGIN BUG#1938430 P. Arvind Dath
543
545 SELECT nvl(max(seq_no),0)
546 FROM gml_recv_adjust_errors
547 WHERE recv_id = vc_recv_id AND
548 line_id = vc_line_id;
549
550 -- END BUG#1938430
551
552 BEGIN
553 X_progress := '010';
554
555 OPEN get_seq_no_cur(p_recv_id, p_line_id);
556 FETCH get_seq_no_cur INTO X_seq_no;
557
558
559 -- BEGIN BUG#1938430 P. Arvind Dath
560
561 /*IF get_seq_no_cur%NOTFOUND THEN
562 X_seq_no := 0;
563 END IF;*/
564
565 -- END BUG#1938430
566
567 CLOSE get_seq_no_cur;
568
569 v_created_by := FND_PROFILE.VALUE ('USER_ID');
570 v_last_updated_by := FND_PROFILE.VALUE ('USER_ID');
571
572 X_seq_no := X_seq_no + 1;
573
574 INSERT INTO gml_recv_adjust_errors
575 (recv_id, line_id, seq_no,recv_qty1, old_recv_qty1, recv_um1,
576 return_ind,recv_status,void_return_ind,
577 creation_date,created_by,last_update_date,last_updated_by,
578 last_update_login,processed_ind)
579 VALUES
580 (p_recv_id, p_line_id, X_seq_no, p_recv_qty1, p_old_recv_qty1, p_recv_um1,
581 p_return_ind,p_recv_status,p_rtrn_void_ind,
582 SYSDATE,v_created_by, SYSDATE,v_last_updated_by,NULL,'N');
583
584 EXCEPTION
585
586 WHEN others THEN
587 po_message_s.sql_error('gml_insert_adjust_error', X_progress, sqlcode);
588
589 RAISE;
590
591 END gml_insert_adjust_error;
592
593 /*========================================================================+
594 | PROCEDURE gml_process_adjust_errors |
595 | |
596 | DESCRIPTION The procedure attempts to process rows in the |
597 | GML_RECV_ADJUST_ERRORS table to indicate that an |
598 | adjustment/return was made in OPM to a receipt that does |
599 | not have a corresponding transaction in Oracle Receiving |
600 | |
601 | MODIFICATION HISTORY |
602 | 27-MAY-99 Tony Ricci Created. |
603 | 12-OCT-00 NC Pay on receipt enhancements Bug#1518114. Added new |
604 | variables and modified the cursors and procedure call |
605 | to reflect the new columns added to gml_recv_adjust_errors
606 | |
607 | 19-OCT-01 Uday Phadtare Bug# 2065300 If get_trans_id_cur fails by any chance
608 | no more records after that are getting processed because adjust_err_ind
609 | is not getting reset. setting back adjust_err_ind to zero.
610 +========================================================================*/
611
612 PROCEDURE gml_process_adjust_errors(retcode OUT NOCOPY NUMBER) AS
613
614 X_progress VARCHAR2(4);
615 X_seq_no gml_recv_adjust_errors.seq_no%TYPE DEFAULT 0;
616
617 adjust_err_ind NUMBER;
618
619 X_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
620 X_po_id po_recv_dtl.po_id%TYPE;
621 X_poline_id po_recv_dtl.poline_id%TYPE;
622 X_opm_item_id po_recv_dtl.item_id%TYPE;
623 X_return_ind po_recv_dtl.return_ind%TYPE;
624 X_recv_status po_recv_dtl.recv_status%TYPE;
625 X_net_price po_recv_dtl.net_price%TYPE;
626
627 CURSOR adjust_error_cur IS
628 select recv_id,
629 line_id,
630 seq_no,
631 recv_qty1,
632 old_recv_qty1,
633 recv_um1,
634 return_ind,
635 recv_status,
636 void_return_ind
637 from GML_RECV_ADJUST_ERRORS
638 where processed_ind = 'N'
639 /*PB 22-JUN-2000 order by added*/
640 order by seq_no;
641
642 error_rec adjust_error_cur%ROWTYPE;
643
644 CURSOR get_trans_id_cur(vc_recv_id NUMBER, vc_line_id NUMBER) IS
645 SELECT interface_transaction_id
646 FROM gml_recv_trans_map
647 WHERE recv_id = vc_recv_id AND
648 line_id = vc_line_id;
649
650 CURSOR get_recv_dtl_cur(vc_recv_id NUMBER, vc_line_id NUMBER) IS
651 SELECT po_id, poline_id, item_id, recv_status, return_ind, net_price
652 FROM po_recv_dtl
653 WHERE recv_id = vc_recv_id AND
654 line_id = vc_line_id;
655
656 BEGIN
657 X_progress := '010';
658 adjust_err_ind := 0;
659
660
661 OPEN adjust_error_cur;
662
663 FETCH adjust_error_cur INTO error_rec;
664
665 WHILE adjust_error_cur%FOUND
666 LOOP
667
668 /* Uday Phadtare Bug 2065300 */
669 adjust_err_ind := 0;
670
671 OPEN get_trans_id_cur(error_rec.recv_id, error_rec.line_id);
672 FETCH get_trans_id_cur INTO X_interface_transaction_id;
673
674 IF get_trans_id_cur%NOTFOUND THEN
675 adjust_err_ind := 1;
676 END IF;
677
678 CLOSE get_trans_id_cur;
679
680 IF adjust_err_ind = 0 THEN
681
682 OPEN get_recv_dtl_cur(error_rec.recv_id, error_rec.line_id);
683 FETCH get_recv_dtl_cur INTO X_po_id, X_poline_id, X_opm_item_id,
684 X_return_ind, X_recv_status, X_net_price;
685 CLOSE get_recv_dtl_cur;
686
687 G_adjust_mode := 'ERRORS';
688 gml_recv_trans_pkg.gml_adjust_recv_trans(error_rec.recv_id,
689 error_rec.line_id,
693 error_rec.recv_um1,
690 X_po_id, X_poline_id,
691 X_opm_item_id, error_rec.recv_qty1,
692 error_rec.old_recv_qty1,
694 error_rec.return_ind,
695 error_rec.recv_status,
696 X_net_price,
697 error_rec.void_return_ind);
698
699 G_adjust_mode := 'NORMAL';
700
701 update gml_recv_adjust_errors
702 set processed_ind = 'Y',
703 last_update_date = SYSDATE
704 where recv_id = error_rec.recv_id
705 and line_id = error_rec.line_id
706 and seq_no = error_rec.seq_no;
707
708 END IF;
709
710 FETCH adjust_error_cur INTO error_rec;
711
712 END LOOP;
713
714 CLOSE adjust_error_cur;
715
716 EXCEPTION
717
718 WHEN others THEN
719 po_message_s.sql_error('gml_process_adjust_errors', X_progress,sqlcode);
720
721 RAISE;
722
723 END gml_process_adjust_errors;
724
725 /*========================================================================+
726 PROCEDURE
727 gml_new_recv_trans_insert()
728 DESCRIPTION The procedure inserts data into the
729 RCV_HEADERS_INTERFACE ,
730 RCV_TRANSACTIONS_INTERFACE and
731 gml_recv_trans_map tables
732
733 PARAMETERS p_recv_id recv_id
734 ....
735 p_transaction_type Transaction_type could be 'DELIVER',
736 'RECEIVE', 'CORRECTION','RETURN TO VENDOR' etc.
737 p_transaction_id This is the parent_transaction_id used
738 mainly for corrections, This value is 0
739 otherwise.
740 p_destination_type_code Destination type of the transaction.Also
741 primarily used for corrections. NULL otherwise.
742 It could be either 'INVENTORY' or 'RECEIVING'.
743
744 p_header_interface_id This has a valid value if the transaction is
745 associated with a header.
746 0 - For corrections,
747
748 p_group_id Same as above.
749 0 - for corrections.
750
751 p_rcv_receipt_num Apps receipt number.
752 p_header_flag 1 - If this is a header insert
753 0 - Otherwise.
754
755 MODIFICATION HISTORY
756
757 12-OCT-00 NC Created.
758
759 This procedure inserts records into both rcv_headers_interface and
760 rcv_transactions_interface for receipts and all types of corrections
761 to receipts (adjustment to receipts/returns, voiding of receipts/returns
762 etc.) depending on the parameters passed.
763 Bug#1518114.
764
765 26-DEC-00 NC Bug#1554124 Added v_vendor_site_id in the insert clause
766 for rcv_transactions_interface. Auto invoices
767 were failing as this column was getting
768 populated with NULL.
769
770 10-DEC-01 Uday Phadtare Bug#2007945 Added parameter p_dtl_recv_date to procedure
771 gml_new_rcv_trans_insert so that transaction_date in rcv_transactions_interface
772 is populated with this date.
773 26-JUL-02 Pushkar Upakare Bug 2458366
774 Added waybill_no to the rcv_header_interface from po_recv_hdr
775 +========================================================================*/
776
777 PROCEDURE gml_new_rcv_trans_insert(p_recv_id IN NUMBER,
778 p_line_id IN NUMBER,
779 p_po_id IN NUMBER,
780 p_poline_id IN NUMBER,
781 p_opm_item_id IN NUMBER,
782 p_recv_qty1 IN NUMBER,
783 p_old_recv_qty1 IN NUMBER,
784 p_recv_um1 IN VARCHAR2,
785
786 p_transaction_type IN VARCHAR2,
787 p_transaction_id IN NUMBER,
788 p_destination_type_code IN VARCHAR2,
789
790 p_header_interface_id IN NUMBER,
791 p_group_id IN NUMBER,
792 p_rcv_receipt_num IN po_recv_hdr.recv_no%TYPE,
793 p_header_flag IN NUMBER,
794 p_dtl_recv_date IN DATE DEFAULT SYSDATE) AS
795 v_po_id po_ordr_hdr.po_id%TYPE;
796 v_poline_id po_ordr_dtl.line_id%TYPE;
797 v_recv_id po_recv_hdr.recv_id%TYPE;
798 v_recv_no po_recv_hdr.recv_no%TYPE;
799 v_shipvend_id po_recv_hdr.shipvend_id%TYPE;
800 v_created_by po_recv_hdr.created_by%TYPE;
801 v_last_updated_by po_recv_hdr.last_updated_by%TYPE;
802 v_to_whse po_recv_hdr.to_whse%TYPE;
803 v_recv_date po_recv_hdr.recv_date%TYPE;
804 v_gross_wt po_recv_hdr.gross_wt%TYPE;
805 v_net_wt po_recv_hdr.net_wt%TYPE;
806 v_tare_wt po_recv_hdr.tare_wt%TYPE;
807 v_bol_um po_recv_hdr.bol_um%TYPE;
808 v_billing_currency po_recv_hdr.billing_currency%TYPE;
809 v_waybill_no po_recv_hdr.waybill_no%TYPE;
810 --v_of_vendor_id po_vend_mst.of_vendor_id%TYPE;
811 v_of_vendor_id po_headers_all.vendor_id%TYPE;
812
813 v_line_id po_recv_dtl.line_id%TYPE;
814 v_recv_qty1 po_recv_dtl.recv_qty1%TYPE;
815 v_recv_um1 po_recv_dtl.recv_um1%TYPE;
816 v_dtl_recv_date po_recv_dtl.recv_date%TYPE;
817
818 v_line_no po_ordr_dtl.line_no%TYPE;
819 v_orgn_code po_ordr_hdr.orgn_code%TYPE;
820 v_returned_qty po_rtrn_dtl.return_qty1%TYPE;
821
822 v_opm_item_id ic_item_mst.item_id%TYPE;
823 v_opm_item_no ic_item_mst.item_no%TYPE;
824
828 v_currency_code po_headers_all.currency_code%TYPE;
825 v_apps_po_header_id po_headers_all.po_header_id%TYPE;
826 v_vendor_site_id po_headers_all.vendor_site_id%TYPE;
827 v_freight_terms po_headers_all.freight_terms_lookup_code%TYPE;
829 v_rate_type po_headers_all.rate_type%TYPE;
830 v_rate_date po_headers_all.rate_date%TYPE;
831 v_rate po_headers_all.rate%TYPE;
832 v_po_revision_num po_headers_all.revision_num%TYPE;
833 v_apps_po_line_id po_lines_all.po_line_id%TYPE;
834 v_item_rev po_lines_all.item_revision%TYPE;
835 v_org_id po_lines_all.org_id%TYPE;
836 v_unit_price po_lines_all.unit_price%TYPE;
837
838 v_apps_po_line_location_id po_line_locations_all.line_location_id%TYPE;
839 v_ship_to_location_id po_line_locations_all.ship_to_location_id%TYPE;
840 v_ship_to_organization_id po_line_locations_all.ship_to_organization_id%TYPE;
841 v_po_release_id po_line_locations_all.po_release_id%TYPE;
842
843 new_header_interface_id rcv_headers_interface.header_interface_id%TYPE;
844 new_group_id rcv_headers_interface.group_id%TYPE;
845 tmp_group_id rcv_headers_interface.group_id%TYPE;
846 new_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
847 tmp_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
848
849 v_deliver_to_person_id po_distributions.deliver_to_person_id%TYPE;
850 v_po_distribution_id po_distributions.po_distribution_id%TYPE;
851 v_auto_transact_code rcv_transactions.transaction_type%TYPE;
852 v_destination_type_code rcv_transactions.transaction_type%TYPE;
853 v_transacion_id rcv_transactions.transaction_id%TYPE;
854 v_header_interface_id rcv_transactions_interface.header_interface_id%TYPE;
855
856 v_item_id mtl_system_items.inventory_item_id%TYPE;
857 v_item_no mtl_system_items.segment1%TYPE;
858 v_item_desc mtl_system_items.description%TYPE;
859 v_subinventory mtl_secondary_inventories.secondary_inventory_name%TYPE;
860 v_transaction_type rcv_transactions.transaction_type%TYPE;
861
862 v_shipment_header_id rcv_transactions.shipment_header_id%TYPE;
863 v_shipment_line_id rcv_transactions.shipment_line_id%TYPE;
864 v_interface_transaction_id rcv_transactions.interface_transaction_id%TYPE;
865 v_transaction_id rcv_transactions.transaction_id%TYPE;
866 v_attribute1 rcv_transactions.attribute1%TYPE;
867 v_comment rcv_transactions_interface.comments%TYPE;
868 v_trans_quantity rcv_transactions.quantity%TYPE;
869 v_document_num rcv_transactions_interface.document_num%TYPE;
870 v_document_line_num rcv_transactions_interface.document_line_num%TYPE;
871 v_document_shipment_line_num rcv_transactions_interface.document_shipment_line_num%TYPE;
872 v_revision_control_code NUMBER;
873
874
875 insert_trans_row NUMBER;
876
877 err_num NUMBER;
878 err_msg VARCHAR2(100);
879 v_group_id NUMBER;
880
881 /** MC BUG# 1554088 **/
882 /** create a variable and a cursor to select apps side uom code **/
883 v_bol_uom_code mtl_units_of_measure.uom_code%TYPE :=NULL;
884 v_recv_unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE;
885 CURSOR UOM_CODE(v_um_code VARCHAR2 ) IS
886 SELECT b.UOM_CODE
887 FROM sy_uoms_mst a,mtl_units_of_measure b
888 WHERE a.um_code = v_um_code
889 AND a.unit_of_measure = b.unit_of_measure;
890
891 CURSOR UNIT_OF_MEASURE(v_um_code VARCHAR2 ) IS
892 SELECT UNIT_OF_MEASURE
893 FROM sy_uoms_mst
894 WHERE um_code = v_um_code;
895
896
897 CURSOR RCV_TRANS_INT_CUR IS
898 SELECT RCV_TRANSACTIONS_INTERFACE_S.nextval
899 FROM sys.dual;
900
901 CURSOR RCV_INT_GROUPS_CUR IS
902 SELECT RCV_INTERFACE_GROUPS_S.nextval
903 FROM sys.dual;
904
905 CURSOR opm_recv_no_cur(vc_recv_id NUMBER) IS
906 SELECT recv_no, shipvend_id,to_whse,recv_date,gross_wt,net_wt,tare_wt, substrb(waybill_no, 1, 20), /* Bug 2458366 - added waybill_no*/
907 bol_um,billing_currency
908 FROM po_recv_hdr
909 WHERE recv_id = vc_recv_id;
910 /*
911 CURSOR opm_vendor_cur(vc_shipvend_id NUMBER) IS
912 SELECT of_vendor_id
913 FROM po_vend_mst
914 WHERE vendor_id = vc_shipvend_id;
915 */
916 CURSOR opm_vendor_cur(vc_po_id NUMBER) IS
917 SELECT of_vendor_id
918 FROM po_vend_mst
919 WHERE vendor_id = (SELECT shipvend_id
920 FROM po_ordr_hdr
921 WHERE po_id = vc_po_id);
922
923 CURSOR opm_oragems_cur(vc_po_id NUMBER, vc_poline_id NUMBER) IS
924 SELECT po_header_id, po_line_id, po_line_location_id
925 FROM cpg_oragems_mapping
926 WHERE po_id = vc_po_id AND
927 line_id = vc_poline_id;
928
929 CURSOR headers_all_cur(vc_apps_po_header_id NUMBER) IS
930 SELECT vendor_site_id,freight_terms_lookup_code,currency_code,rate_type,
931 rate_date,rate,revision_num
932 FROM po_headers_all
933 WHERE po_header_id = vc_apps_po_header_id;
934
935 CURSOR po_dist_cur(vc_apps_po_header_id NUMBER,
936 vc_apps_po_line_id NUMBER,
937 vc_apps_po_line_location_id NUMBER) IS
938 SELECT deliver_to_person_id,po_distribution_id
939 FROM po_distributions
940 WHERE po_header_id = vc_apps_po_header_id AND
944 CURSOR opm_item_cur(vc_opm_item_id NUMBER) IS
941 po_line_id = vc_apps_po_line_id AND
942 line_location_id = vc_apps_po_line_location_id;
943
945 SELECT item_no
946 FROM ic_item_mst
947 WHERE item_id = vc_opm_item_id;
948
949 CURSOR app_item_cur(vc_opm_item_no VARCHAR2) IS
950 SELECT inventory_item_id,description
951 FROM mtl_system_items
952 WHERE segment1 = vc_opm_item_no;
953
954 /* Uday Phadtare B1785880 removed unit_price from select */
955 CURSOR app_po_line_cur(vc_apps_po_header_id NUMBER,
956 vc_apps_po_line_id NUMBER) IS
957 SELECT item_revision,org_id
958 FROM po_lines_all
959 WHERE po_header_id = vc_apps_po_header_id AND
960 po_line_id = vc_apps_po_line_id;
961
962 /* Uday Phadtare B1785880 select price_override from po_line_locations_all */
963 CURSOR po_line_loc_cur(vc_apps_po_header_id NUMBER,
964 vc_apps_po_line_id NUMBER,
965 vc_apps_po_line_location_id NUMBER) IS
966 SELECT ship_to_location_id,po_release_id,ship_to_organization_id,price_override
967 FROM po_line_locations_all
968 WHERE po_header_id = vc_apps_po_header_id AND
969 po_line_id = vc_apps_po_line_id AND
970 line_location_id = vc_apps_po_line_location_id;
971
972 /* Bug #1470411*/
973 CURSOR rev_control_code_cur(vc_inventory_item_id IN NUMBER,
974 vc_ship_to_organization_id IN NUMBER) IS
975 SELECT revision_qty_control_code
976 FROM mtl_system_items
977 WHERE inventory_item_id = vc_inventory_item_id AND
978 organization_id = vc_ship_to_organization_id;
979
980
981 CURSOR rcv_transactions_cur(v_transaction_id NUMBER) IS
982 SELECT shipment_header_id,
983 shipment_line_id,
984 po_distribution_id,
985 attribute1,
986 interface_transaction_id
987 FROM rcv_transactions
988 WHERE transaction_id = v_transaction_id;
989
990 /* Bug#1098066 */
991 CURSOR app_subinventory_cur(vc_ship_to_organization_id VARCHAR2) IS
992 SELECT secondary_inventory_name
993 FROM mtl_secondary_inventories
994 WHERE organization_id = vc_ship_to_organization_id AND
995 NVL(disable_date,sysdate+1) > sysdate ;
996
997 /* Bug 1969740 */
998 CURSOR get_subinventory_code_cur(vc_ship_to_organization_id NUMBER,
999 vc_inventory_location_id NUMBER) IS
1000 SELECT subinventory_code
1001 FROM mtl_item_locations
1002 WHERE organization_id = vc_ship_to_organization_id
1003 AND inventory_location_id = vc_inventory_location_id
1004 AND (disable_date > sysdate or disable_date is null);
1005
1006
1007 /* PB */
1008 v_rcv_receipt_num NUMBER;
1009
1010 /* Begin Bug 1685307 */
1011 CURSOR get_location IS
1012 SELECT whse_code, loct_ctl
1013 FROM ic_whse_mst
1014 WHERE mtl_organization_id = v_ship_to_organization_id
1015 AND delete_mark = 0;
1016
1017 CURSOR locid_whse(p_whse_code VARCHAR2) IS
1018 SELECT inventory_location_id
1019 FROM ic_loct_mst
1020 WHERE whse_code = p_whse_code
1021 AND inventory_location_id is not null
1022 AND delete_mark = 0
1023 ORDER BY inventory_location_id;
1024
1025 /* Uday Phadtare B1858899 */
1026 CURSOR doc_nums_cur(vc_apps_po_header_id NUMBER,
1027 vc_apps_po_line_id NUMBER,
1028 vc_apps_po_line_location_id NUMBER) IS
1029 select po.segment1, lines.line_num, shipments.shipment_num
1030 from po_headers_all po, po_lines_all lines, po_line_locations_all shipments
1031 where po.po_header_id = vc_apps_po_header_id
1032 and lines.po_header_id = po.po_header_id
1033 and lines.po_line_id = vc_apps_po_line_id
1034 and shipments.po_header_id = po.po_header_id
1035 and shipments.po_line_id = lines.po_line_id
1036 and shipments.line_location_id = vc_apps_po_line_location_id;
1037
1038 v_get_location get_location%ROWTYPE;
1039 v_loct_id NUMBER := NULL;
1040 /* End Bug 1685307 */
1041
1042 BEGIN
1043 v_po_id := p_po_id;
1044 v_poline_id := p_poline_id;
1045 v_recv_id := p_recv_id;
1046 v_line_id := p_line_id;
1047 v_recv_qty1 := p_recv_qty1;
1048 v_recv_um1 := p_recv_um1;
1049 v_opm_item_id := p_opm_item_id;
1050 v_dtl_recv_date := p_dtl_recv_date;
1051
1052 v_created_by := FND_PROFILE.VALUE ('USER_ID');
1053 v_last_updated_by := FND_PROFILE.VALUE ('USER_ID');
1054
1055 v_rcv_receipt_num := p_rcv_receipt_num;
1056
1057 v_transaction_type := p_transaction_type;
1058
1059 v_trans_quantity := (p_recv_qty1 - p_old_recv_qty1);
1060
1061 OPEN opm_recv_no_cur(v_recv_id);
1062 FETCH opm_recv_no_cur INTO v_recv_no, v_shipvend_id, v_to_whse,
1063 v_recv_date,v_gross_wt,v_net_wt,v_tare_wt, v_waybill_no,
1064 v_bol_um,v_billing_currency;
1065 CLOSE opm_recv_no_cur;
1066
1067 /** MC BUG# 1554088 **/
1068 OPEN uom_code(v_bol_um);
1069 FETCH uom_code INTO v_bol_uom_code;
1070 CLOSE uom_code;
1071
1072 OPEN unit_of_measure(v_recv_um1);
1073 FETCH unit_of_measure INTO v_recv_unit_of_measure;
1074 CLOSE unit_of_measure;
1075
1076
1077 /*
1078 OPEN opm_vendor_cur(v_shipvend_id);
1082 CLOSE opm_vendor_cur;
1079 */
1080 OPEN opm_vendor_cur(v_po_id);
1081 FETCH opm_vendor_cur INTO v_of_vendor_id;
1083
1084 OPEN opm_oragems_cur(v_po_id, v_poline_id);
1085 FETCH opm_oragems_cur INTO v_apps_po_header_id, v_apps_po_line_id,
1086 v_apps_po_line_location_id;
1087 CLOSE opm_oragems_cur;
1088
1089 OPEN headers_all_cur(v_apps_po_header_id);
1090 FETCH headers_all_cur INTO v_vendor_site_id,v_freight_terms,
1091 v_currency_code,v_rate_type,v_rate_date,
1092 v_rate,v_po_revision_num;
1093 CLOSE headers_all_cur;
1094
1095 OPEN po_dist_cur(v_apps_po_header_id, v_apps_po_line_id,
1096 v_apps_po_line_location_id);
1097 FETCH po_dist_cur INTO v_deliver_to_person_id,v_po_distribution_id;
1098 CLOSE po_dist_cur;
1099
1100 OPEN opm_item_cur(v_opm_item_id);
1101 FETCH opm_item_cur INTO v_opm_item_no;
1102 CLOSE opm_item_cur;
1103
1104 OPEN app_item_cur(v_opm_item_no);
1105 FETCH app_item_cur INTO v_item_id,v_item_desc;
1106 CLOSE app_item_cur;
1107
1108 OPEN app_po_line_cur(v_apps_po_header_id, v_apps_po_line_id);
1109 FETCH app_po_line_cur INTO v_item_rev, v_org_id;
1110 CLOSE app_po_line_cur;
1111
1112 /* Uday Phadtare B1785880 v_unit_price fetched from po_line_locations_all */
1113 OPEN po_line_loc_cur(v_apps_po_header_id, v_apps_po_line_id,
1114 v_apps_po_line_location_id);
1115 FETCH po_line_loc_cur INTO v_ship_to_location_id,v_po_release_id,
1116 v_ship_to_organization_id,v_unit_price;
1117 CLOSE po_line_loc_cur;
1118
1119 /* Bug #1470411 */
1120 OPEN rev_control_code_cur(v_item_id,v_ship_to_organization_id);
1121 FETCH rev_control_code_cur INTO v_revision_control_code;
1122 CLOSE rev_control_code_cur;
1123
1124 /* Bug#1098066 */
1125 OPEN app_subinventory_cur(v_ship_to_organization_id);
1126 FETCH app_subinventory_cur INTO v_subinventory;
1127 CLOSE app_subinventory_cur;
1128
1129 /* Begin Bug 1685307 */
1130 OPEN get_location;
1131 FETCH get_location INTO v_get_location;
1132 CLOSE get_location;
1133
1134 IF v_get_location.loct_ctl IN(1,2) THEN
1135 OPEN locid_whse(v_get_location.whse_code);
1136 FETCH locid_whse INTO v_loct_id;
1137 CLOSE locid_whse;
1138
1139 /* Begin Bug# 1969740 */
1140 OPEN get_subinventory_code_cur(v_ship_to_organization_id, v_loct_id);
1141 FETCH get_subinventory_code_cur INTO v_subinventory;
1142 CLOSE get_subinventory_code_cur;
1143 /* End Bug# 1969740 */
1144 END IF;
1145 /* End Bug 1685307 */
1146
1147 /* Uday Phadtare B1858899 */
1148 OPEN doc_nums_cur(v_apps_po_header_id, v_apps_po_line_id, v_apps_po_line_location_id);
1149 FETCH doc_nums_cur INTO v_document_num, v_document_line_num, v_document_shipment_line_num;
1150 CLOSE doc_nums_cur;
1151
1152 IF (p_header_flag = 1) THEN /* This is a header insert */
1153 /** MC BUG# 1554088 **/
1154 /** replace v_bol_um with v_bol_uom_code in the insert **/
1155 INSERT INTO rcv_headers_interface
1156 (header_interface_id,
1157 group_id,
1158 processing_status_code,
1159 receipt_source_code,
1160 transaction_type,
1161 auto_transact_code,
1162 last_update_date,
1163 last_updated_by,
1164 creation_date,
1165 created_by,
1166 shipment_num,
1167 receipt_num,
1168 vendor_id,
1169 vendor_site_id,
1170 ship_to_organization_id,
1171 expected_receipt_date,
1172 waybill_airbill_num, /* Bug 2458366 */
1173 comments,
1174 gross_weight,
1175 gross_weight_uom_code,
1176 net_weight,
1177 net_weight_uom_code,
1178 tar_weight,
1179 tar_weight_uom_code,
1180 freight_terms,
1181 currency_code,
1182 conversion_rate_type,
1183 conversion_rate,
1184 conversion_rate_date,
1185 employee_id,
1186 validation_flag)
1187 VALUES
1188 (p_header_interface_id,
1189 p_group_id,
1190 'PENDING',
1191 'VENDOR',
1192 'NEW',
1193 'RECEIVE',
1194 SYSDATE,
1195 v_last_updated_by,
1196 SYSDATE,
1197 v_created_by,
1198 to_char(p_header_interface_id), /* 2540428 insert p_header_interface_id instead of '1' */
1199 v_rcv_receipt_num, /* v_recv_no */
1200 v_of_vendor_id,
1201 v_vendor_site_id,
1202 v_ship_to_organization_id,
1203 v_recv_date,
1204 v_waybill_no, /* Bug 2458366 */
1205 'OPM RECEIPT',
1206 v_gross_wt,
1207 v_bol_uom_code,
1208 v_net_wt,
1209 v_bol_uom_code,
1210 v_tare_wt,
1211 v_bol_uom_code,
1212 v_freight_terms,
1213 v_currency_code,
1214 v_rate_type,
1215 v_rate,
1216 v_rate_date,
1217 v_deliver_to_person_id,
1218 'Y');
1219
1220 RETURN;
1221
1222 END IF;
1223
1224 OPEN RCV_TRANS_INT_CUR;
1225 FETCH RCV_TRANS_INT_CUR INTO new_interface_transaction_id;
1226 CLOSE RCV_TRANS_INT_CUR;
1227
1228 /* Only if this record is an adjustment to an existing transaction
1229 in rcv_transactions,will we open the following two cursors. One
1230 to fetch the shipment and distribution ids and the other to
1231 fetch a new group id */
1232
1236 FETCH rcv_transactions_cur INTO v_shipment_header_id,v_shipment_line_id,
1233 IF (p_header_interface_id = 0 AND p_header_flag = 0 ) THEN
1234
1235 OPEN rcv_transactions_cur(p_transaction_id);
1237 v_po_distribution_id,v_attribute1,
1238 v_interface_transaction_id;
1239 CLOSE rcv_transactions_cur;
1240
1241 OPEN RCV_INT_GROUPS_CUR;
1242 FETCH RCV_INT_GROUPS_CUR INTO new_group_id;
1243 CLOSE RCV_INT_GROUPS_CUR;
1244
1245 v_auto_transact_code := NULL;
1246 v_subinventory := NULL;
1247 v_comment := 'OPM Receipt Correction';
1248 v_header_interface_id := NULL;
1249 v_transaction_id := p_transaction_id;
1250 v_destination_type_code := p_destination_type_code;
1251
1252 ELSE
1253 /* Bug2228634 : Changed destination type code to 'Inventory'
1254 as directed by Discrete po */
1255 /* This transaction is for an existing header. We'll use the passed
1256 parameters */
1257
1258 new_group_id := p_group_id;
1259 new_header_interface_id := p_header_interface_id;
1260 v_shipment_header_id := NULL;
1261 v_shipment_line_id := NULL;
1262 v_attribute1 := new_interface_transaction_id;
1263 v_transaction_type := 'RECEIVE';
1264 v_trans_quantity := v_recv_qty1;
1265 v_auto_transact_code := 'DELIVER';
1266 v_transaction_id := NULL; /* parent transaction id */
1267 v_destination_type_code := 'INVENTORY'; /* Changing from NULL */
1268 v_comment := 'OPM RECEIPT';
1269 v_header_interface_id := p_header_interface_id;
1270
1271 END IF;
1272
1273 /* Bug #1470411 */
1274 IF (v_revision_control_code = 1) THEN /* This item is not revision controled, we'll pass NULL */
1275 v_item_rev := NULL;
1276 END IF;
1277
1278 /** MC BUG# 1554088 **/
1279 /** unit_of_measure was going as v_recv_um1.Replace it with v_recv_unit_of_measure **/
1280 INSERT INTO rcv_transactions_interface
1281 (INTERFACE_TRANSACTION_ID,
1282 GROUP_ID,
1283 LAST_UPDATE_DATE,
1284 LAST_UPDATED_BY,
1285 CREATION_DATE,
1286 CREATED_BY,
1287 TRANSACTION_TYPE,
1288 TRANSACTION_DATE,
1289 PROCESSING_STATUS_CODE,
1290 PROCESSING_MODE_CODE,
1291 TRANSACTION_STATUS_CODE,
1292 QUANTITY,
1293 UNIT_OF_MEASURE,
1294 ITEM_ID,
1295 ITEM_DESCRIPTION,
1296 ITEM_REVISION,
1297 AUTO_TRANSACT_CODE,
1298 SHIPMENT_HEADER_ID,
1299 SHIPMENT_LINE_ID,
1300 SHIP_TO_LOCATION_ID,
1301 RECEIPT_SOURCE_CODE,
1302 VENDOR_ID,
1303 VENDOR_SITE_ID,
1304 TO_ORGANIZATION_ID,
1305 SOURCE_DOCUMENT_CODE,
1306 PARENT_TRANSACTION_ID,
1307 PO_HEADER_ID,
1308 PO_REVISION_NUM,
1309 PO_RELEASE_ID,
1310 PO_LINE_ID,
1311 PO_LINE_LOCATION_ID,
1312 PO_UNIT_PRICE,
1313 CURRENCY_CODE,
1314 CURRENCY_CONVERSION_TYPE,
1315 CURRENCY_CONVERSION_RATE,
1316 CURRENCY_CONVERSION_DATE,
1317 PO_DISTRIBUTION_ID,
1318 DESTINATION_TYPE_CODE,
1319 DELIVER_TO_LOCATION_ID,
1320 SUBINVENTORY,
1321 LOCATOR_ID,
1322 SHIPMENT_NUM,
1323 EXPECTED_RECEIPT_DATE,
1324 COMMENTS,
1325 ATTRIBUTE1,
1326 HEADER_INTERFACE_ID,
1327 DOCUMENT_NUM,
1328 DOCUMENT_LINE_NUM,
1329 DOCUMENT_SHIPMENT_LINE_NUM,
1330 VALIDATION_FLAG)
1331 VALUES
1332 (new_interface_transaction_id,
1333 new_group_id,
1334 SYSDATE,
1335 v_last_updated_by,
1336 SYSDATE,
1337 v_created_by,
1338 v_transaction_type,
1339 v_dtl_recv_date, /* B2007945 replaced SYSDATE with v_dtl_recv_date SYSDATE */
1340 'PENDING',
1341 'BATCH',
1342 'PENDING',
1343 v_trans_quantity,
1344 v_recv_unit_of_measure,
1345 v_item_id,
1346 v_item_desc,
1347 v_item_rev,
1348 v_auto_transact_code,
1349 v_shipment_header_id,
1350 v_shipment_line_id,
1351 v_ship_to_location_id,
1352 'VENDOR',
1353 v_of_vendor_id,
1354 v_vendor_site_id,
1355 v_ship_to_organization_id,
1356 'PO',
1357 v_transaction_id,
1358 v_apps_po_header_id,
1359 v_po_revision_num,
1360 v_po_release_id,
1361 v_apps_po_line_id,
1362 v_apps_po_line_location_id,
1363 v_unit_price,
1364 v_currency_code,
1365 v_rate_type,
1366 v_rate,
1367 v_rate_date,
1368 v_po_distribution_id,
1369 v_destination_type_code,
1370 v_ship_to_location_id, /* B1766557 */
1371 v_subinventory,
1372 v_loct_id, /* B1685307 */
1373 '1',
1374 v_recv_date,
1375 v_comment,
1376 v_attribute1,
1377 v_header_interface_id,
1378 v_document_num,
1379 v_document_line_num,
1380 v_document_shipment_line_num,
1381 'Y');
1382
1383 /* Each time a new transaction (except for adjustments ) is inserted,
1384 insert a row in the mapping table.
1385 */
1386 IF (p_header_interface_id <> 0 AND p_header_flag = 0 ) THEN
1387
1388 INSERT INTO gml_recv_trans_map
1389 (recv_id, line_id, interface_transaction_id,group_id,
1390 creation_date,created_by,last_update_date,last_updated_by,
1391 last_update_login,organization_id,rcv_receipt_num)
1392 VALUES
1393 (v_recv_id, v_line_id,new_interface_transaction_id,new_group_id,
1394 SYSDATE,v_created_by, SYSDATE,v_last_updated_by,NULL,v_ship_to_organization_id,
1395 v_rcv_receipt_num);
1396
1397 G_header_interface_id := new_header_interface_id;
1398 G_group_id := new_group_id;
1399 G_interface_transaction_id := new_interface_transaction_id;
1400 G_recv_id := v_recv_id;
1401 G_rows_inserted := G_rows_inserted +1;
1402 G_ship_to_organization_id := v_ship_to_organization_id;
1403
1404 END IF;
1405
1406 EXCEPTION
1407
1408 WHEN OTHERS THEN
1409 err_num := SQLCODE;
1410 err_msg := SUBSTRB(SQLERRM, 1, 100);
1411 RAISE_APPLICATION_ERROR(-20000, err_msg);
1412
1413 END gml_new_rcv_trans_insert;
1414
1415 END GML_RECV_TRANS_PKG;