[Home] [Help]
PACKAGE BODY: APPS.GML_PO_SYNCH
Source
1 PACKAGE BODY GML_PO_SYNCH AS
2 /* $Header: GMLPOSYB.pls 120.1 2005/06/21 00:19:19 appldev ship $ */
3
4 v_lang VARCHAR2(10) := 'ENG';
5
6 /*============================================================================+
7 | |
8 | PROCEDURE NAME next_po_id |
9 | |
10 | DESCRIPTION Procedure to get the next available GEMMS po_id |
11 | |
12 | MODIFICATION HISTORY |
13 | 10/15/97 Rajeshwari Chellam created |
14 | 14-MAY-98 T Ricci replaced sy_surg_ctl with nextval from |
15 | sys.dual for GEMMS 5.0 |
16 | |
17 +============================================================================*/
18
19 -- yannamal GSCC b4403407
20 PROCEDURE next_po_id ( new_po_id OUT NOCOPY PO_ORDR_HDR.PO_ID%TYPE,
21 p_doc_type IN VARCHAR2,
22 p_orgn_code IN VARCHAR2,
23 v_next_id_status OUT NOCOPY BOOLEAN)
24 AS
25 /* CURSOR NPO_ID_CUR IS
26 SELECT last_value + 1
27 FROM sy_surg_ctl
28 WHERE key_name = 'po_id'
29 FOR UPDATE;*/
30
31 CURSOR NPO_ID_CUR IS
32 SELECT GEM5_PO_ID_s.nextval
33 FROM sys.dual;
34
35 err_msg VARCHAR2(100);
36
37 BEGIN
38
39 OPEN npo_id_cur;
40 FETCH npo_id_cur INTO new_po_id;
41
42 /* UPDATE sy_surg_ctl
43 SET last_value = new_po_id
44 WHERE current of npo_id_cur; */
45
46 UPDATE sy_docs_seq
47 SET last_assigned = last_assigned + 1
48 WHERE doc_type = p_doc_type
49 AND orgn_code = p_orgn_code;
50
51 CLOSE npo_id_cur;
52 v_next_id_status :=TRUE;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 v_next_id_status := FALSE;
57 END next_po_id;
58
59 /*============================================================================+
60 | |
61 | PROCEDURE NAME next_line_id |
62 | |
63 | DESCRIPTION Procedure to get the next available GEMMS line_id |
64 | |
65 | MODIFICATION HISTORY |
66 | 10/15/97 Rajeshwari Chellam created |
67 | 14-MAY-98 T Ricci replaced sy_surg_ctl with nextval from |
68 | sys.dual for GEMMS 5.0 |
69 | 23-OCT-98 T Ricci added IN parm line_type |
70 | |
71 +============================================================================*/
72
73 PROCEDURE next_line_id
74 (line_type IN VARCHAR2,new_line_id OUT NOCOPY PO_ORDR_DTL.LINE_ID%TYPE,
75 v_next_id_status OUT NOCOPY BOOLEAN) -- yannamal GSCC b4403407
76 AS
77
78 /* CURSOR nline_id_cur IS
79 SELECT last_value + 1
80 FROM sy_surg_ctl
81 WHERE key_name = 'line_id'
82 FOR UPDATE; */
83
84 CURSOR npoline_id_cur IS
85 SELECT GEM5_PO_LINE_ID_s.nextval
86 FROM sys.dual;
87
88 CURSOR nbpoline_id_cur IS
89 SELECT GEM5_BPO_LINE_ID_s.nextval
90 FROM sys.dual;
91
92 err_msg VARCHAR2(100);
93
94 BEGIN
95
96 IF line_type = 'PO' THEN
97 OPEN npoline_id_cur;
98
99 FETCH npoline_id_cur INTO new_line_id;
100
101 CLOSE npoline_id_cur;
102 v_next_id_status :=TRUE;
103
104 ELSIF line_type = 'BPO' THEN
105 OPEN nbpoline_id_cur;
106
107 FETCH nbpoline_id_cur INTO new_line_id;
108
109 CLOSE nbpoline_id_cur;
110 v_next_id_status :=TRUE;
111 ELSE
112 v_next_id_status :=FALSE;
113
114 END IF;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 v_next_id_status :=FALSE;
119
120 END next_line_id;
121
122
123 /*============================================================================+
124 | |
125 | |
126 | PROCEDURE NAME next_trans_id |
127 | |
128 | DESCRIPTION Procedure to get the next available GEMMS trans_id |
129 | |
130 | MODIFICATION HISTORY |
131 | 10/22/97 Kenny Jiang created |
132 | 14-MAY-98 T Ricci replaced sy_surg_ctl with nextval from |
133 | sys.dual for GEMMS 5.0 |
134 | |
135 +============================================================================*/
136
137 PROCEDURE next_trans_id
138 (v_new_trans_id OUT NOCOPY IC_TRAN_PND.TRANS_ID%TYPE,
139 v_next_id_status OUT NOCOPY BOOLEAN) -- yannamal GSCC b4403407
140 AS
141
142 /* CURSOR NTRANS_ID_CUR IS
143 SELECT last_value + 1
144 FROM sy_surg_ctl
145 WHERE key_name = 'trans_id'
146 FOR UPDATE; */
147
148 CURSOR NTRANS_ID_CUR IS
149 SELECT GEM5_TRANS_ID_s.nextval
150 FROM sys.dual;
151
152 err_msg VARCHAR2(100);
153
154 BEGIN
155
156 OPEN ntrans_id_cur;
157 FETCH ntrans_id_cur INTO v_new_trans_id;
158
159 /* UPDATE sy_surg_ctl
160 SET last_value = v_new_trans_id
161 WHERE current of ntrans_id_cur; */
162
163 CLOSE ntrans_id_cur;
164 v_next_id_status :=TRUE;
165
166 EXCEPTION
167
168 WHEN OTHERS THEN
169 v_next_id_status := FALSE;
170
171 END next_trans_id;
172
173
174 /*============================================================================+
175 | |
176 | |
177 | FUNCTION NAME new_po_hdr |
178 | |
179 | DESCRIPTION |
180 | function to check if the given PO already exists in GEMMS|
181 | |
182 | MODIFICATION HISTORY |
183 | 10/22/97 Kenny Jiang created |
184 | 18-JUL-98 Liz Enstone GEMMS 5.0 Include orgn_code in select |
185 | |
186 +============================================================================*/
187
188 FUNCTION new_po_hdr
189 (v_po_no IN PO_ORDR_HDR.PO_NO%TYPE,
190 v_orgn_code IN CPG_PURCHASING_INTERFACE.ORGN_CODE%TYPE)
191 RETURN BOOLEAN
192 IS
193 v_row_count NUMBER :=0;
194 err_msg VARCHAR2(100);
195
196 BEGIN
197 SELECT COUNT(*)
198 INTO v_row_count
199 FROM po_ordr_hdr
200 WHERE po_no = v_po_no
201 AND orgn_code = v_orgn_code;
202
203 IF v_row_count > 0 THEN
204 RETURN FALSE;
205 ELSE
206 RETURN TRUE;
207 END IF;
208
209 EXCEPTION
210
211 WHEN OTHERS THEN
212 err_msg := SUBSTRB (SQLERRM, 1, 100);
213 RAISE_APPLICATION_ERROR(-20001, err_msg);
214
215 END new_po_hdr;
216
217
218 /*============================================================================+
219 | |
220 | |
221 | FUNCTION NAME new_line |
222 | |
223 | DESCRIPTION function to check if the record in the interface table|
224 | is the submission of a new line location |
225 | |
226 | MODIFICATION HISTORY |
227 | 10/27/97 Kenny Jiang created |
228 | |
229 +============================================================================*/
230
231 FUNCTION new_line
232 ( v_po_header_id IN NUMBER,
233 v_po_line_id IN NUMBER,
234 v_po_line_location_id IN NUMBER )
235
236 RETURN BOOLEAN
237 IS
238
239 CURSOR id_cur(header NUMBER, line NUMBER, location NUMBER) IS
240 SELECT po_id, line_id
241 FROM cpg_oragems_mapping
242 WHERE po_header_id = header
243 AND po_line_id = line
244 AND po_line_location_id = location;
245
246 id_rec id_cur%ROWTYPE;
247 err_msg VARCHAR2(100);
248
249 BEGIN
250
251 OPEN id_cur(v_po_header_id, v_po_line_id, v_po_line_location_id);
252 FETCH id_cur INTO id_rec;
253
254 IF (id_rec.po_id IS NULL AND id_rec.line_id IS NULL) THEN
255 RETURN TRUE;
256 ELSIF (id_rec.po_id IS NOT NULL AND id_rec.line_id IS NOT NULL) THEN
257 RETURN FALSE;
258 END IF;
259
260 CLOSE id_cur;
261
262 EXCEPTION
263
264 WHEN OTHERS THEN
265 err_msg := SUBSTRB (SQLERRM, 1, 100);
266 RAISE_APPLICATION_ERROR(-20001, err_msg);
267
268 END new_line;
269
270 /*============================================================================+
271 | |
272 | FUNCTION NAME get_line_no |
273 | |
274 | DESCRIPTION function to get the maximum line number so far |
275 | for a particular PO |
276 | |
277 | MODIFICATION HISTORY |
278 | 10/27/97 Kenny Jiang created |
279 | 03/24/00 NC - modified the procedure to accept po_line_id instead of
280 | po_id and return line_num from po_lines all instead of the
281 | max(line_no) from po_ordr_dtl.TAR #12693733.6
282 | (Bug#1249797 base bug#1247332.
283 | 15/05/00 NC - Added code to take care of line numbering for Planned and|
284 | Blanket POS and in case of more than one shipment per |
285 | line.Added new parameters. |
286 +============================================================================*/
287
288 FUNCTION get_line_no(v_po_id IN NUMBER ,
289 v_po_header_id IN NUMBER,
290 v_po_line_id IN NUMBER,
291 v_po_line_location_id IN NUMBER,
292 v_transaction_type IN VARCHAR2) RETURN NUMBER
293 IS
294 v_line_no NUMBER;
295 v_line_count NUMBER;
296 v_shipment_count NUMBER;
297 err_msg VARCHAR2(100);
298
299 BEGIN
300
301 /* If it is a blanket or a planned purchase order we'll retain the line
302 number from apps. */
303
304 IF v_transaction_type in ( 'BLANKET','PLANNED') THEN
305 SELECT shipment_num
306 INTO v_line_no
307 FROM po_line_locations_all
308 WHERE line_location_id = v_po_line_location_id;
309
310 /* If it is a standard PO, we'll retain the line number from apps as long as
311 there is only one shipment per line.We'll generate a number if there is
312 more than one shipment per line */
313
314 ELSE /* STANDARD */
315 SELECT count(*)
316 INTO v_line_count
317 FROM po_lines_all
318 WHERE po_header_id = v_po_header_id;
319
320 SELECT count(*)
321 INTO v_shipment_count
322 FROM po_line_locations_all
323 WHERE po_header_id = v_po_header_id;
324
325 IF v_line_count = v_shipment_count THEN
326 SELECT line_num
327 INTO v_line_no
328 FROM po_lines_all
329 WHERE po_line_id = v_po_line_id;
330 ELSE
331 SELECT NVL(MAX(line_no),0) +1
332 INTO v_line_no
333 FROM po_ordr_dtl
334 WHERE po_id = v_po_id;
335 END IF;
336
337 END IF;
338
339 RETURN v_line_no;
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 RETURN 0;
344
345 WHEN OTHERS THEN
346 err_msg := SUBSTRB (SQLERRM, 1, 100);
347 RAISE_APPLICATION_ERROR(-20001, err_msg);
348
349 END get_line_no;
350
351
352 /*============================================================================+
353 | |
354 | |
355 | PROCEDURE NAME errlog_header |
356 | |
357 | DESCRIPTION procedure to print the header for this shipment |
358 | in the error log |
359 | |
360 | MODIFICATION HISTORY |
361 | 11/19/97 Kenny Jiang created |
362 | 05/12/98 Tony Ricci changed date modified to |
363 | last_update_date for GEMMS 5.0 |
364 | 04/15/99 Hasan Wahdani added app_date to v_last_update_date |
365 +============================================================================*/
366
367
368 PROCEDURE errlog_header(
369 v_po_no IN VARCHAR2,
370 v_line_num IN NUMBER,
371 v_shipment_num IN NUMBER,
372 v_revision_count IN NUMBER,
373 v_last_update_date IN DATE)
374 IS
375 err_msg VARCHAR2(100);
376
377 BEGIN
378
379 /* H. Wahdani removed format from v_last_update_date and placed a call to fnd_date.date_tocharDT */
380
381 FND_FILE.NEW_LINE(FND_FILE.LOG, 2 );
382 FND_FILE.PUT_LINE(FND_FILE.LOG,
383 '------------------------------------------------------------------------');
384 FND_FILE.PUT_LINE(FND_FILE.LOG,
385 'PO: ' ||v_po_no || 'Line: '||TO_CHAR(v_line_num) || ' Shipment:
386 '|| TO_CHAR(v_shipment_num)||' Revision: '||TO_CHAR(v_revision_count)|| ' Time:
387 '|| fnd_date.date_to_charDT(v_last_update_date));
388 FND_FILE.PUT_LINE(FND_FILE.LOG,
389 '-------------------------------------------------------------------------');
390
391 EXCEPTION
392
393 WHEN OTHERS THEN
394 err_msg := SUBSTRB (SQLERRM, 1, 100);
395 RAISE_APPLICATION_ERROR(-20001, err_msg);
396
397 END errlog_header;
398
399 /*============================================================================+
400 | |
401 | PROCEDURE NAME cpg_conv_duom |
402 | |
403 | DESCRIPTION Procedure to calculate order qty in dual UOM |
404 | |
405 | MODIFICATION HISTORY |
406 | 11/21/97 Kenny Jiang created |
407 | |
408 +============================================================================*/
409
410 PROCEDURE cpg_conv_duom
411 ( v_item_id IN NUMBER,
412 v_um1 IN VARCHAR2,
413 v_order1 IN NUMBER,
414 v_um2 IN VARCHAR2,
415 v_order2 OUT NOCOPY NUMBER) -- yannamal GSCC b4403407
416 IS
417 CURSOR uom_mst_cur(v_um VARCHAR2) IS
418 SELECT std_factor, um_type
419 FROM sy_uoms_mst
420 WHERE um_code = v_um;
421
422 CURSOR tf_cur(v_um_type VARCHAR2) IS
423 SELECT type_factor
424 FROM ic_item_cnv
425 WHERE item_id = v_item_id
426 AND um_type = v_um_type;
427
428 CURSOR item_cur IS
429 SELECT item_um
430 FROM ic_item_mst
431 WHERE item_id = v_item_id;
432
433 v_std_factor1 NUMBER;
434 v_std_factor2 NUMBER;
435 v_type1 VARCHAR2(4);
436 v_type2 VARCHAR2(4);
437 v_std_factor NUMBER;
438 v_type VARCHAR2(4);
439 v_type_factor02 NUMBER;
440 v_type_factor01 NUMBER;
441
442 v_um VARCHAR2(4);
443
444 err_num NUMBER;
445 err_msg VARCHAR2(100);
446
447 BEGIN
448
449 IF v_um2 IS NULL THEN
450 v_order2 := NULL;
451
452 ELSE
453
454 OPEN uom_mst_cur(v_um1);
455 FETCH uom_mst_cur INTO v_std_factor1, v_type1;
456 CLOSE uom_mst_cur;
457
458 OPEN uom_mst_cur(v_um2);
459 FETCH uom_mst_cur INTO v_std_factor2, v_type2;
460 CLOSE uom_mst_cur;
461
462 OPEN item_cur;
463 FETCH item_cur INTO v_um;
464 CLOSE item_cur;
465
466 OPEN uom_mst_cur(v_um);
467 FETCH uom_mst_cur INTO v_std_factor, v_type;
468 CLOSE uom_mst_cur;
469
470 OPEN tf_cur(v_um1);
471 FETCH tf_cur INTO v_type_factor01;
472 CLOSE tf_cur;
473
474 OPEN tf_cur(v_um2);
475 FETCH tf_cur INTO v_type_factor02;
476 CLOSE tf_cur;
477
478 IF v_type1 = v_type2 THEN
479 v_order2 := v_order1 * v_std_factor1 / v_std_factor2;
480
481 ELSIF v_type = v_type1 THEN
482 v_order2 := v_order1 * v_std_factor1 / v_type_factor02 / v_std_factor2;
483
484 ELSE
485 v_order2 := v_order1 * v_std_factor1 / v_std_factor2 * v_type_factor01
486 / v_type_factor02;
487 END IF;
488
489 END IF;
490
491 EXCEPTION
492
493 WHEN OTHERS THEN
494 err_num := SQLCODE;
495 err_msg := SUBSTRB (SQLERRM, 1, 100);
496 RAISE_APPLICATION_ERROR(-20000, err_msg);
497
498 END cpg_conv_duom;
499
500 /*============================================================================+
501 | |
502 | FUNCTION NAME gemms_validate |
503 | |
504 | DESCRIPTION function to validate all the necessary values in |
505 | each row of the purchasing interface table |
506 | |
507 | MODIFICATION HISTORY |
508 | 10/27/97 Kenny Jiang created |
509 | 05/12/98 Tony Ricci changed date modified to |
510 | last_update_datefor GEMMS 5.0 |
511 | |
512 +============================================================================*/
513
514
515 FUNCTION gemms_validate
516 ( v_orgn_code IN VARCHAR2,
517 v_of_payvend_site_id IN NUMBER,
518 v_of_shipvend_site_id IN NUMBER,
519 v_to_whse IN VARCHAR2,
520 v_billing_currency IN VARCHAR2,
521 v_item_no IN VARCHAR2,
522 v_order_um1 IN VARCHAR2,
523 v_price_um IN VARCHAR2,
524 v_order_um2 IN VARCHAR2,
525 v_item_um IN VARCHAR2,
526 v_buyer_code IN VARCHAR2,
527 v_from_whse IN VARCHAR2,
528 v_shipper_code IN VARCHAR2,
529 v_of_frtbill_mthd IN VARCHAR2,
530 v_of_terms_code IN VARCHAR2,
531 v_qc_grade_wanted IN VARCHAR2,
532 v_po_no IN VARCHAR2,
533 v_line_id IN NUMBER,
534 v_line_location_id IN NUMBER,
535 v_revision_count IN NUMBER,
536 v_last_update_date IN DATE)
537
538 RETURN BOOLEAN
539 IS
540 v_result BOOLEAN := TRUE;
541 v_co_code SY_ORGN_MST.CO_CODE%TYPE;
542 v_line_num NUMBER;
543 v_shipment_num NUMBER;
544
545 CURSOR co_code_cur(v_orgn_code SY_ORGN_MST.ORGN_CODE%TYPE) IS
546 SELECT co_code
547 FROM sy_orgn_mst
548 WHERE orgn_code = v_orgn_code;
549
550 CURSOR line_no_cur IS
551 SELECT line_num
552 FROM po_lines_all
553 WHERE po_line_id = v_line_id;
554
555 CURSOR shipment_cur IS
556 SELECT shipment_num
557 FROM po_line_locations_all
558 WHERE line_location_id = v_line_location_id;
559
560 v_err_message VARCHAR2(2000);
561
562 err_num NUMBER;
563 err_msg VARCHAR2(100);
564
565 BEGIN
566
567 OPEN co_code_cur(v_orgn_code);
568 FETCH co_code_cur
569 INTO v_co_code;
570 CLOSE co_code_cur;
571
572 OPEN shipment_cur;
573 FETCH shipment_cur
574 INTO v_shipment_num;
575 CLOSE shipment_cur;
576
577 OPEN line_no_cur;
578 FETCH line_no_cur
579 INTO v_line_num;
580 CLOSE line_no_cur;
581
582 /* These are mandatory fields*/
583
584 /* Validate the GEMMS Organization on the Purchase Order */
585
586 IF (v_orgn_code IS NULL) OR
587 (GML_VALIDATE_PO.val_orgn_code(v_orgn_code) = FALSE) THEN
588
589 IF v_result = TRUE THEN /*if this is the first validation error*/
590 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
591 v_revision_count, v_last_update_date);
592 END IF;
593
594 FND_MESSAGE.set_name('GML', 'PO_BAD_ORG');
595 FND_MESSAGE.set_token('v_orgn_code',v_orgn_code);
596 v_err_message := FND_MESSAGE.GET;
597 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
598 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
599 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
600 v_result := FALSE;
601 END IF;
602
603
604 IF (v_of_payvend_site_id IS NULL) OR
605 (GML_VALIDATE_PO.val_vendor(v_of_payvend_site_id, v_co_code) = FALSE) THEN
606
607 IF v_result = TRUE THEN /*if this is the first validation error*/
608 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
609 v_revision_count, v_last_update_date);
610 END IF;
611
612 FND_MESSAGE.set_name('GML', 'PO_BAD_PAY_VEND');
613 FND_MESSAGE.set_token('v_of_payvend_site_id',v_of_payvend_site_id);
614 v_err_message := FND_MESSAGE.GET;
615
616 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
617 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
618 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
619
620 v_result := FALSE;
621 END IF;
622
623 IF (v_of_shipvend_site_id IS NULL) OR
624 (GML_VALIDATE_PO.val_vendor(v_of_shipvend_site_id, v_co_code) = FALSE) THEN
625
626 IF v_result = TRUE THEN /*if this is the first validation error*/
627 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
628 v_revision_count, v_last_update_date);
629 END IF;
630
631 FND_MESSAGE.set_name('GML', 'PO_BAD_SHIP_VEND');
632 FND_MESSAGE.set_token('v_of_shipvend_site_id',v_of_shipvend_site_id);
633 v_err_message := FND_MESSAGE.GET;
634 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
635 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
636 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
637 v_result := FALSE;
638 END IF;
639
640 IF (v_to_whse IS NULL) OR (v_to_whse = ' ') OR
641 (GML_VALIDATE_PO.val_warehouse(v_to_whse,v_orgn_code) = FALSE) THEN
642
643
644 IF v_result = TRUE THEN /*if this is the first validation error*/
645 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
646 v_revision_count, v_last_update_date);
647 END IF;
648
649 FND_MESSAGE.set_name('GML', 'PO_BAD_TO_WHSE');
650 FND_MESSAGE.set_token('v_to_whse',v_to_whse);
651 v_err_message := FND_MESSAGE.GET;
652 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
653 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
654 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
655 v_result := FALSE;
656 END IF;
657
658 IF (v_billing_currency IS NULL) OR (v_billing_currency = ' ') OR
659 (GML_VALIDATE_PO.val_currency (v_billing_currency)=FALSE) THEN
660
661 IF v_result = TRUE THEN /*if this is the first validation error*/
662 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
663 v_revision_count, v_last_update_date);
664 END IF;
665
666 FND_MESSAGE.set_name('GML', 'PO_BAD_BILL_CURR');
667 FND_MESSAGE.set_token('v_billing_currency',v_billing_currency);
668 v_err_message := FND_MESSAGE.GET;
669 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
670 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
671 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
672 v_result := FALSE;
673
674 END IF;
675
676 IF (v_item_no IS NULL) OR (v_item_no = ' ') OR
677 (GML_VALIDATE_PO.val_item (v_item_no) = FALSE) THEN
678
679 IF v_result = TRUE THEN /*if this is the first validation error*/
680 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
681 v_revision_count, v_last_update_date);
682 END IF;
683
684 FND_MESSAGE.set_name('GML', 'PO_BAD_ITEM');
685 FND_MESSAGE.set_token('v_item_no',v_item_no);
686 v_err_message := FND_MESSAGE.GET;
687 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
688 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
689 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
690 v_result := FALSE;
691
692 END IF;
693
694 IF (v_order_um1 IS NULL) OR (v_order_um1 = ' ') OR
695 (GML_VALIDATE_PO.val_uom (v_order_um1) = FALSE) THEN
696
697 IF v_result = TRUE THEN /*if this is the first validation error*/
698 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
699 v_revision_count, v_last_update_date);
700 END IF;
701
702 FND_MESSAGE.set_name('GML', 'PO_BAD_UM1');
703 FND_MESSAGE.set_token('v_order_um1',v_order_um1);
704 v_err_message := FND_MESSAGE.GET;
705 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
706 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
707 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
708 v_result := FALSE;
709
710 END IF;
711
712 IF (v_price_um IS NULL) OR (v_price_um = ' ') OR
713 (GML_VALIDATE_PO.val_uom (v_price_um)=FALSE) THEN
714
715 IF v_result = TRUE THEN /*if this is the first validation error*/
716 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
717 v_revision_count, v_last_update_date);
718 END IF;
719
720 FND_MESSAGE.set_name('GML', 'PO_BAD_PRICE');
721 FND_MESSAGE.set_token('v_price_um',v_price_um);
722 v_err_message := FND_MESSAGE.GET;
723 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
724 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
725 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
726 v_result := FALSE;
727
728 END IF;
729
730 /* all the following are not mandatory*/
731
732 IF (v_order_um2 IS NOT NULL) AND (v_order_um2 <> ' ') AND
733 (GML_VALIDATE_PO.val_uom( v_order_um2) = FALSE ) THEN
734
735 IF v_result = TRUE THEN /*if this is the first validation error*/
736 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
737 v_revision_count, v_last_update_date);
738 END IF;
739
740 FND_MESSAGE.set_name('GML', 'PO_BAD_UM2');
741 FND_MESSAGE.set_token('v_order_um2',v_order_um2);
742 v_err_message := FND_MESSAGE.GET;
743 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
744 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
745 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
746 v_result := FALSE;
747
748 END IF;
749
750 IF (v_item_um IS NOT NULL) AND (v_item_um <> ' ') AND
751 (GML_VALIDATE_PO.val_uom( v_item_um) = FALSE) THEN
752
753 IF v_result = TRUE THEN /*if this is the first validation error*/
754 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
755 v_revision_count, v_last_update_date);
756 END IF;
757
758 FND_MESSAGE.set_name('GML', 'PO_BAD_ITEM_UM');
759 FND_MESSAGE.set_token('v_item_um',v_item_um);
760 v_err_message := FND_MESSAGE.GET;
761 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
762 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
763 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
764 v_result := FALSE;
765
766 END IF;
767
768 IF (v_from_whse IS NOT NULL) AND (v_from_whse <> ' ') AND
769 (GML_VALIDATE_PO.val_warehouse(v_from_whse,v_orgn_code) = FALSE) THEN
770
771 IF v_result = TRUE THEN /*if this is the first validation error*/
772 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
773 v_revision_count, v_last_update_date);
774 END IF;
775
776 FND_MESSAGE.set_name('GML', 'PO_BAD_FROM_WHSE');
777 FND_MESSAGE.set_token('v_from_whse',v_from_whse);
778 v_err_message := FND_MESSAGE.GET;
779 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
780 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
781 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
782 v_result := FALSE;
783
784 END IF;
785
786 IF (v_shipper_code IS NOT NULL) AND (v_shipper_code <> ' ') AND
787 (GML_VALIDATE_PO.val_shipper_code(v_shipper_code)= FALSE ) THEN
788
789 IF v_result = TRUE THEN /*if this is the first validation error*/
790 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
791 v_revision_count, v_last_update_date);
792 END IF;
793
794 FND_MESSAGE.set_name('GML', 'PO_BAD_SHIPPER');
795 FND_MESSAGE.set_token('v_shipper_code',v_shipper_code);
796 v_err_message := FND_MESSAGE.GET;
797 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
798 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
799 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
800 v_result := FALSE;
801
802 END IF;
803
804 IF (v_of_frtbill_mthd IS NOT NULL) AND (v_of_frtbill_mthd <> ' ') AND
805 (GML_VALIDATE_PO.val_frtbill_mthd( v_of_frtbill_mthd) = FALSE ) THEN
806
807 IF v_result = TRUE THEN /*if this is the first validation error*/
808 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
809 v_revision_count, v_last_update_date);
810 END IF;
811
812 FND_MESSAGE.set_name('GML', 'PO_BAD_FRTB');
813 FND_MESSAGE.set_token('v_of_frtbill_mthd',v_of_frtbill_mthd);
814 v_err_message := FND_MESSAGE.GET;
815 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
816 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
817 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
818 v_result := FALSE;
819
820 END IF;
821
822 IF (v_of_terms_code IS NOT NULL) AND (v_of_terms_code <> ' ') AND
823 (GML_VALIDATE_PO.val_terms_code(v_of_terms_code) = FALSE ) THEN
824
825 IF v_result = TRUE THEN /*if this is the first validation error*/
826 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
827 v_revision_count, v_last_update_date);
828 END IF;
829
830 FND_MESSAGE.set_name('GML', 'PO_BAD_TERMS');
831 FND_MESSAGE.set_token('v_of_terms_code',v_of_terms_code);
832 v_err_message := FND_MESSAGE.GET;
833 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
834 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
835 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
836 v_result := FALSE;
837
838 END IF;
839
840 /* Validate the qc_grade_wanted*/
841
842 IF (v_qc_grade_wanted IS NOT NULL) AND (v_qc_grade_wanted <> ' ') AND
843 (GML_VALIDATE_PO.val_qc_grade_wanted(v_qc_grade_wanted) = FALSE ) THEN
844
845 IF v_result = TRUE THEN /*if this is the first validation error*/
846 GML_PO_SYNCH.errlog_header( v_po_no, v_line_num, v_shipment_num,
847 v_revision_count, v_last_update_date);
848 END IF;
849
850 FND_MESSAGE.set_name('GML', 'PO_BAD_QC');
851 FND_MESSAGE.set_token('v_qc_grade_wanted',v_qc_grade_wanted);
852 v_err_message := FND_MESSAGE.GET;
853 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
854 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
855 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
856 v_result := FALSE;
857
858 END IF;
859
860 RETURN v_result;
861
862 EXCEPTION
863 WHEN OTHERS THEN
864 err_num := SQLCODE;
865 err_msg := SUBSTRB (SQLERRM, 1, 100);
866 RAISE_APPLICATION_ERROR(-20001, err_msg);
867
868 END gemms_validate;
869
870
871 /*=============================================================================+
872 | |
873 | |
874 | FUNCTION Get_total_Received_qty |
875 | |
876 | DESCRIPTION procedure to get the total received quantity against a po|
877 | line where the receipt is not voided or deleted and then |
878 | Convert the received quantity to the base uom of the item|
879 | MODIFICATION HISTORY |
880 | 07/05/2000 Preetam Bamb created |
881 | |
882 | Uday Phadtare B1845881 deduct return qty from received qty to get correct |
883 | total received qty |
884 +==============================================================================*/
885
886 FUNCTION Get_total_Received_qty(p_po_id IN NUMBER, p_line_id IN NUMBER,p_item_id IN NUMBER,item_um1 IN VARCHAR2)
887 RETURN NUMBER
888 IS
889
890 cursor total_rcvd_qty_cur IS
891 Select sum(recv_qty1) qty,RECV_UM1
892 from po_Recv_dtl d
893 where po_id = p_po_id
894 and poline_id = p_line_id
895 and recv_status <> -1
896 group by recv_um1;
897
898 /* Uday Phadtare BB1845881 */
899 cursor total_rtrn_qty_cur IS
900 Select sum(d.return_qty1) qty, d.RETURN_UM1
901 from po_Rtrn_hdr h,
902 po_Rtrn_dtl d
903 where h.return_id = d.return_id
904 and d.po_id = p_po_id
905 and d.poline_id = p_line_id
906 and h.delete_mark <> -1
907 group by d.return_um1;
908
909 v_total_rcvd_qty NUMBER :=0;
910 v_total_rtrn_qty NUMBER :=0;
911 v_rcvd_qty NUMBER :=0;
912 v_return_qty NUMBER :=0;
913
914 BEGIN
915
916 for tot_rec in total_rcvd_qty_cur
917 loop
918 if tot_rec.recv_um1 = item_um1
919 then
920 v_total_rcvd_qty := v_total_rcvd_qty + nvl(tot_rec.qty,0);
921 else
922 v_rcvd_qty := GMICUOM.uom_conversion
923 (p_item_id,0,
924 tot_rec.qty,
925 tot_rec.recv_um1,
926 item_um1,0);
927
928 v_total_rcvd_qty := v_total_rcvd_qty + nvl(v_rcvd_qty,0);
929 end if;
930
931 end loop;
932
933 /* Uday Phadtare BB1845881 */
934 for tot_ret in total_rtrn_qty_cur
935 loop
936 if tot_ret.return_um1 = item_um1
937 then
938 v_total_rtrn_qty := v_total_rtrn_qty + nvl(tot_ret.qty,0);
939 else
940 v_return_qty := GMICUOM.uom_conversion
941 (p_item_id,0,
942 tot_ret.qty,
943 tot_ret.return_um1,
944 item_um1,0);
945
946 v_total_rtrn_qty := v_total_rtrn_qty + nvl(v_return_qty,0);
947 end if;
948
949 end loop;
950
951 RETURN nvl(v_total_rcvd_qty,0) - nvl(v_total_rtrn_qty,0);
952
953 END;
954
955
956
957 /*============================================================================+
958 | |
959 | |
960 | FUNCTION NAME new_aqcst_line |
961 | |
962 | DESCRIPTION This function checks if the given aquisition cost line|
963 | already exists for the shipment on GEMMS |
964 | |
965 | MODIFICATION HISTORY |
966 | 11/6/97 Kenny Jiang created |
967 | |
968 +============================================================================*/
969
970
971 FUNCTION new_aqcst_line
972 ( v_type VARCHAR2,
973 v_pos_id NUMBER,
974 v_line_id NUMBER,
975 v_cost_id NUMBER)
976
977 RETURN BOOLEAN
978 IS
979 CURSOR cost_line_cur IS
980 SELECT cost_amount
981 FROM po_cost_dtl
982 WHERE doc_type = v_type
983 AND pos_id = v_pos_id
984 AND line_id = v_line_id
985 AND aqui_cost_id = v_cost_id
986 AND delete_mark = 0;
987
988 v_amount NUMBER;
989
990 err_num NUMBER;
991 err_msg VARCHAR2(100);
992
993 BEGIN
994 OPEN cost_line_cur;
995 FETCH cost_line_cur
996 INTO v_amount;
997 CLOSE cost_line_cur;
998
999 IF v_amount IS NULL THEN
1000 RETURN TRUE;
1001 ELSE
1002 RETURN FALSE;
1003 END IF;
1004
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 err_num := SQLCODE;
1008 err_msg := SUBSTRB (SQLERRM, 1, 100);
1009 RAISE_APPLICATION_ERROR(-20001, err_msg);
1010
1011 END new_aqcst_line;
1012
1013
1014 /*============================================================================+
1015 | |
1016 | |
1017 | PROCEDURE NAME cpg_aqcst_mv |
1018 | |
1019 | DESCRIPTION This procedure moves the acquisition cost information |
1020 | from Oracle to Gemms |
1021 | |
1022 | MODIFICATION HISTORY |
1023 | 11/3/97 Kenny Jiang created |
1024 | 11/10/98 Tony Ricci set trans_cnt on insert to '0' as per |
1025 | OPM 11.0 |
1026 | |
1027 +============================================================================*/
1028
1029 PROCEDURE cpg_aqcst_mv
1030 ( v_po_header_id IN NUMBER,
1031 v_po_line_id IN NUMBER,
1032 v_line_location_id IN NUMBER,
1033 v_po_id IN NUMBER,
1034 v_line_id IN NUMBER,
1035 v_doc_type IN VARCHAR2,
1036 v_aqcst_status OUT NOCOPY BOOLEAN) -- yannamal GSCC b4403407
1037 IS
1038 CURSOR aqcst_cur IS
1039 SELECT aqui_cost_id, cost_amount, incl_ind,
1040 last_update_date, created_by, creation_date, last_updated_by,
1041 last_update_login
1042 FROM cpg_cost_dtl
1043 WHERE po_header_id = v_po_header_id
1044 AND po_line_id = v_po_line_id
1045 AND line_location_id = v_line_location_id;
1046
1047 CURSOR gemms_acq_cur IS
1048 SELECT aqui_cost_id, cost_amount
1049 FROM po_cost_dtl a
1050 WHERE pos_id = v_po_id
1051 AND line_id = v_line_id
1052 AND NOT EXISTS (SELECT 'Y'
1053 FROM cpg_cost_dtl b
1054 WHERE b.po_header_id = v_po_header_id
1055 AND b.po_line_id = v_po_line_id
1056 AND b.line_location_id = v_line_location_id
1057 AND b.aqui_cost_id = a.aqui_cost_id
1058 AND b.cost_amount = a.cost_amount);
1059
1060 invalid_action EXCEPTION;
1061
1062 v_new_aqcst_line BOOLEAN;
1063 p_aqui_cost_id NUMBER;
1064 p_cost_amount NUMBER;
1065
1066 err_num NUMBER;
1067 err_msg VARCHAR2(100);
1068
1069 BEGIN
1070
1071
1072 FOR aqcst_rec IN aqcst_cur LOOP
1073
1074 v_new_aqcst_line := GML_PO_SYNCH.new_aqcst_line
1075 (v_doc_type,v_po_id, v_line_id, aqcst_rec.aqui_cost_id);
1076 /* T. Ricci 5/12/98 GEMMS 5.0 changes for who columns*/
1077
1078 IF v_new_aqcst_line THEN
1079 INSERT INTO po_cost_dtl
1080 (
1081 doc_type,
1082 pos_id,
1083 line_id,
1084 aqui_cost_id,
1085 cost_amount,
1086 incl_ind,
1087 last_update_date,
1088 last_updated_by,
1089 last_update_login,
1090 created_by,
1091 creation_date,
1092 text_code,
1093 trans_cnt,
1094 delete_mark
1095 )
1096 VALUES
1097 ( v_doc_type,
1098 v_po_id,
1099 v_line_id,
1100 aqcst_rec.aqui_cost_id,
1101 aqcst_rec.cost_amount,
1102 aqcst_rec.incl_ind,
1103 aqcst_rec.last_update_date,
1104 aqcst_rec.last_updated_by,
1105 aqcst_rec.last_update_login,
1106 aqcst_rec.created_by,
1107 aqcst_rec.creation_date,
1108 NULL,
1109 0, /* T. Ricci 11/10/98 set trans_cnt to '0'*/
1110 0
1111 );
1112
1113 ELSE
1114
1115 UPDATE po_cost_dtl
1116 SET cost_amount = aqcst_rec.cost_amount,
1117 incl_ind = aqcst_rec.incl_ind,
1118 last_update_date = aqcst_rec.last_update_date
1119 WHERE doc_type = v_doc_type
1120 AND pos_id = v_po_id
1121 AND line_id = v_line_id
1122 AND aqui_cost_id = aqcst_rec.aqui_cost_id;
1123
1124 END IF;
1125
1126 END LOOP;
1127
1128 /*Commenting out the following code as its not clear what its doing - Preetam Bamb */
1129 /*
1130 OPEN gemms_acq_cur;
1131 LOOP
1132 FETCH gemms_acq_cur INTO p_aqui_cost_id, p_cost_amount;
1133 EXIT WHEN gemms_acq_cur%NOTFOUND;
1134 UPDATE po_cost_dtl
1135 SET delete_mark = 1
1136 WHERE pos_id = v_po_id
1137 AND line_id = v_line_id
1138 AND aqui_cost_id = p_aqui_cost_id
1139 AND cost_amount = p_cost_amount;
1140 END LOOP;
1141 CLOSE gemms_acq_cur;
1142 */
1143
1144
1145 v_aqcst_status :=TRUE;
1146
1147 EXCEPTION
1148
1149 WHEN OTHERS THEN
1150 v_aqcst_status :=FALSE;
1151 err_num := SQLCODE;
1152 err_msg := SUBSTRB (SQLERRM, 1, 100);
1153 RAISE_APPLICATION_ERROR(-20001, err_msg);
1154
1155 END cpg_aqcst_mv;
1156
1157
1158
1159 /*=============================================================================+
1160 | |
1161 | |
1162 | PROCEDURE Update_header_status |
1163 | |
1164 | DESCRIPTION procedure to update the header status to close or calcel |
1165 | in case where the po has |
1166 | only one line and that line is closed or calcelled or all the |
1167 | lines are closed or cancelled. |
1168 | MODIFICATION HISTORY |
1169 | 07/26/2000 Preetam Bamb created |
1170 | |
1171 | |
1172 | |
1173 | 11/15/2001 Uday Phadtare B2068007 procedure Update_header_status. Cancel the PO
1174 | header only if all PO lines are cancelled.
1175 +==============================================================================*/
1176
1177 PROCEDURE Update_header_status(p_po_id IN NUMBER,p_cancellation_code IN VARCHAR2)
1178 IS
1179
1180 cursor total_lines_cur IS
1181 Select count(*)
1182 From cpg_oragems_mapping
1183 Where po_id = p_po_id;
1184
1185 Cursor get_line_status IS
1186 Select po_status
1187 From po_ordr_dtl
1188 Where po_id = p_po_id
1189 and po_status = 0;
1190
1191 Cursor get_uncancelled_lines IS
1192 Select count(*)
1193 From po_ordr_dtl
1194 Where po_id = p_po_id
1195 and cancellation_code IS NULL;
1196
1197 v_total_lines NUMBER :=0 ;
1198 v_po_status NUMBER :=0;
1199 v_count NUMBER :=0;
1200 v_cancellation_code PO_ORDR_DTL.CANCELLATION_CODE%TYPE := p_cancellation_code;
1201
1202 BEGIN
1203
1204 Open total_lines_cur;
1205 Fetch total_lines_cur into v_total_lines;
1206 Close total_lines_cur;
1207
1208 IF v_total_lines = 1
1209 THEN
1210 update po_ordr_hdr
1211 set po_status = 20,
1212 cancellation_code = p_cancellation_code
1213 where po_id = p_po_id;
1214 ELSE
1215 OPEN get_line_status;
1216 FETCH get_line_status into v_po_status;
1217 IF get_line_status%FOUND
1218 THEN
1219 CLOSE get_line_status;
1220 ELSE
1221 CLOSE get_line_status;
1222 IF v_cancellation_code IS NOT NULL THEN
1223 OPEN get_uncancelled_lines;
1224 FETCH get_uncancelled_lines INTO v_count;
1225 CLOSE get_uncancelled_lines;
1226 IF v_count > 0 THEN
1227 v_cancellation_code := NULL;
1228 END IF;
1229 END IF;
1230 update po_ordr_hdr
1231 set po_status = 20,
1232 cancellation_code = v_cancellation_code
1233 where po_id = p_po_id;
1234 END IF; /* %FOUND */
1235 END IF; /*v_total_lines = 1 */
1236
1237 END;
1238
1239
1240 /*============================================================================+
1241 | |
1242 | PROCEDURE NAME cpg_int2gms |
1243 | |
1244 | DESCRIPTION procedure to move records from the purchasing interface|
1245 | table to the gemms base tables |
1246 | |
1247 | MODIFICATION HISTORY |
1248 | 10/22/97 Kenny Jiang created |
1249 | 11/01/97 Rajeshwari Chellam modified for bpo/ppo releases |
1250 | 05/12/98 Tony Ricci GEMMS 5.0 database changes |
1251 | 07/06/98 Tony Ricci GEMMS 5.0 integrity constraint |
1252 | changes where NULL is allowed |
1253 | insert one. |
1254 | 17/AUG/98 KYH Replace hard coded values with |
1255 | appropriate system constants |
1256 | 03/NOV/98 Tony Ricci added calls to |
1257 | GML_PO_GLDIST.receive_data to |
1258 | perform GL mapping |
1259 | 06/NOV/98 Tony Ricci removed call to GML_PO_SYNCH. |
1260 | cpg_conv_duom and replaced with |
1261 | GMICUOM.icuomcv which is the OPM |
1262 | standard uom conversion |
1263 | 11/10/98 Tony Ricci set trans_cnt on insert to '0' |
1264 | as per OPM 11.0 |
1265 | 11/10/98 Tony Ricci added frtbill_mthd_cur so OPM |
1266 | value will be used |
1267 | 11/11/98 Tony Ricci removed fob_code_cur so OPM |
1268 | value will be used (already in |
1269 | int_rec.fob_code |
1270 | 11/24/98 Tony Ricci added call to GMICCAL.trans_date |
1271 | _validate to check for a valid |
1272 | Inventory calendar |
1273 | 02/04/99 Tony Ricci use correct variable for um2 |
1274 | when inserting/updating |
1275 | ic_tran_pnd BUG#814841 |
1276 | 04/19/00 N Chekuri Added appropriate error messages |
1277 | for Inventory calendar validation|
1278 | routine GMICAL.trans_date_validate
1279 | ().Bug#1274130. |
1280 | 14/18/2001 Pushkar Upakare In ic_tran_pnd(TRANS_UM) |
1281 | inserted primary UOM instead of |
1282 | order UOM. |
1283 | 05/21/2001 Uday Phadtare B1795095 In ic_tran_pnd set delete_mark|
1284 | to 1 if po is closed and set to |
1285 | if PO is opened. |
1286 | 05/22/2001 P. Arvind Dath Added code to synch Attributes to|
1287 | OPM |
1288 | 06/28/2001 Pushkar Upakare Bug 1854280 |
1289 | BPO table updates are corrected. |
1290 | 10/12/01 V. Ajay Kumar BUG#2041468 Modified the update |
1291 | statement to get the PO |
1292 | numbers in the LOV for PO |
1293 | NUMBER field in Receipt |
1294 | Selection Screen. |
1295 | 11/13/2001 Pushkar Upakare Bug 2031029 |
1296 | Exclude PPO template record from |
1297 | being picked up in int_cur cursor|
1298 | 06/27/2003 Mohit Kapoor Bug 3019986 |
1299 | Made the insertion/updation of |
1300 | ic_summ_inv conditional, only if |
1301 | the table exists |
1302 +============================================================================*/
1303
1304 PROCEDURE cpg_int2gms( retcode out Nocopy number) -- yannamal GSCC b4403407
1305 IS
1306
1307 /* T. Ricci 5/12/98 removed user_class and added new who columns*/
1308
1309 CURSOR int_cur IS
1310 SELECT rowid,
1311 item_um,
1312 transaction_id,
1313 transaction_type,
1314 orgn_code,
1315 po_no,
1316 po_header_id,
1317 po_line_id,
1318 po_line_location_id,
1319 po_distribution_id,
1320 po_status,
1321 buyer_code,
1322 po_id,
1323 bpo_id,
1324 bpo_release_number,
1325 of_payvend_site_id,
1326 of_shipvend_site_id,
1327 po_date,
1328 po_type,
1329 from_whse,
1330 to_whse,
1331 recv_desc,
1332 recv_loct,
1333 recvaddr_id,
1334 ship_mthd,
1335 shipper_code,
1336 of_frtbill_mthd,
1337 of_terms_code,
1338 billing_currency,
1339 purchase_exchange_rate,
1340 mul_div_sign,
1341 currency_bght_fwd,
1342 pohold_code,
1343 cancellation_code,
1344 fob_code,
1345 icpurch_class,
1346 vendso_no,
1347 project_no,
1348 requested_dlvdate,
1349 sched_shipdate,
1350 required_dlvdate,
1351 agreed_dlvdate,
1352 date_printed,
1353 expedite_date,
1354 revision_count,
1355 in_use,
1356 print_count,
1357 line_id,
1358 bpo_line_id,
1359 apinv_line_id,
1360 item_no,
1361 generic_id,
1362 item_desc,
1363 order_qty1,
1364 order_qty2,
1365 order_um1,
1366 order_um2,
1367 received_qty1,
1368 received_qty2,
1369 net_price,
1370 extended_price,
1371 price_um,
1372 qc_grade_wanted,
1373 match_type,
1374 text_code,
1375 trans_cnt,
1376 exported_date,
1377 last_update_date,
1378 created_by,
1379 creation_date,
1380 last_updated_by,
1381 last_update_login,
1382 delete_mark,
1383 contract_value,
1384 contract_start_date,
1385 contract_end_date,
1386 std_qty,
1387 max_rels_qty,
1388 invalid_ind,
1389 po_release_id,
1390 release_num,
1391 source_shipment_id,
1392 line_no
1393 FROM cpg_purchasing_interface
1394 WHERE invalid_ind ='N'
1395 AND (transaction_type IN ('STANDARD','BLANKET') /* Bug 2031029 - Do not select TEMPLATE PPOs */
1396 OR
1397 (transaction_type = 'PLANNED' AND nvl(po_release_id,0) <> 0 AND nvl(release_num,0) <> 0)
1398 )
1399 ORDER BY transaction_id;
1400
1401 int_rec int_cur%ROWTYPE;
1402
1403 /* BEGIN BUG#1731582 P. Arvind Dath */
1404
1405 CURSOR attr_hdr_cur(v_po_ordr_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE)
1406 IS
1407 SELECT attribute1,
1408 attribute2,
1409 attribute3,
1410 attribute4,
1411 attribute5,
1412 attribute6,
1413 attribute7,
1414 attribute8,
1415 attribute9,
1416 attribute10,
1417 attribute11,
1418 attribute12,
1419 attribute13,
1420 attribute14
1421 FROM PO_HEADERS_ALL
1422 WHERE PO_HEADER_ID = v_po_ordr_header_id;
1423
1424 CURSOR attr_dtl_cur(v_line_locations_id PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID%TYPE,
1425 v_ordr_header_id PO_LINE_LOCATIONS_ALL.PO_HEADER_ID%TYPE,
1426 v_po_line_id PO_LINE_LOCATIONS_ALL.PO_LINE_ID%TYPE)
1427 IS
1428 SELECT attribute1,
1429 attribute2,
1430 attribute3,
1431 attribute4,
1432 attribute5,
1433 attribute6,
1434 attribute7,
1435 attribute8,
1436 attribute9,
1437 attribute10,
1438 attribute11,
1439 attribute12,
1440 attribute13,
1441 attribute14,
1442 attribute15
1443 FROM PO_LINE_LOCATIONS_ALL
1444 WHERE LINE_LOCATION_ID = v_line_locations_id
1445 AND PO_HEADER_ID = v_ordr_header_id
1446 AND PO_LINE_ID = v_po_line_id;
1447
1448 attr_hdr_rec attr_hdr_cur%ROWTYPE;
1449 attr_dtl_rec attr_dtl_cur%ROWTYPE;
1450
1451 /* END BUG#1731582 */
1452
1453 /* Uday Phadtare B2085936 */
1454 CURSOR po_lines_all_cur
1455 ( v_ordr_header_id PO_LINE_LOCATIONS_ALL.PO_HEADER_ID%TYPE,
1456 v_line_id PO_LINES_ALL.po_line_id%TYPE) IS
1457 SELECT substrb(item_description,1,70)
1458 FROM po_lines_all
1459 WHERE po_header_id = v_ordr_header_id
1460 AND po_line_id = v_line_id;
1461
1462
1463 v_po_no PO_ORDR_HDR.PO_NO%TYPE;
1464 v_bpo_id PO_ORDR_HDR.BPO_ID%TYPE;
1465 v_bpo_line_id PO_ORDR_DTL.BPO_LINE_ID%TYPE;
1466 v_po_status NUMBER;
1467 v_doc_type VARCHAR2(4);
1468 v_amt_purchased NUMBER;
1469 v_qty_purchased NUMBER;
1470 v_order2 NUMBER;
1471 /* Added by PPB-UOM */
1472 v_order1 NUMBER;
1473
1474 v_exchange_rate GL_XCHG_RTE.EXCHANGE_RATE%TYPE;
1475 v_mul_div_sign GL_XCHG_RTE.MUL_DIV_SIGN%TYPE;
1476 v_exchange_rate_date GL_XCHG_RTE.EXCHANGE_RATE_DATE%TYPE;
1477 v_base_currency GL_XCHG_RTE.TO_CURRENCY_CODE%TYPE;
1478 v_gl_source_type GL_SRCE_MST.TRANS_SOURCE_TYPE%TYPE;
1479
1480
1481 err_num NUMBER;
1482 err_msg VARCHAR2(100);
1483 v_retval NUMBER DEFAULT 0;
1484 v_err_message VARCHAR2(2000);
1485
1486 /* variables to fetch values of columns*/
1487
1488 v_new_po_id PO_ORDR_HDR.PO_ID%TYPE;
1489 v_po_id PO_ORDR_HDR.PO_ID%TYPE;
1490 v_line_id PO_ORDR_DTL.LINE_id%TYPE;
1491 v_new_line_id PO_ORDR_DTL.LINE_id%TYPE;
1492 v_line_no PO_ORDR_DTL.LINE_no%TYPE;
1493 v_dtl_line_no PO_ORDR_DTL.LINE_no%TYPE;
1494 v_item_id IC_ITEM_MST.ITEM_ID%TYPE;
1495 v_item_um2 IC_ITEM_MST.ITEM_UM2%TYPE;
1496 v_item_um1 IC_ITEM_MST.ITEM_UM%TYPE; /* PPB */
1497 v_item_desc IC_ITEM_MST.ITEM_DESC1%TYPE;
1498 v_dualum_ind IC_ITEM_MST.DUALUM_IND%TYPE;
1499 v_ship_vendor_id PO_VEND_MST.VENDOR_ID%TYPE;
1500 v_pay_vendor_id PO_VEND_MST.VENDOR_ID%TYPE;
1501 v_frtbill_mthd OP_FRGT_MTH.FRTBILL_MTHD%TYPE;
1502 v_terms_code OP_TERM_MST.TERMS_CODE%TYPE;
1503 v_new_trans_id IC_TRAN_PND.TRANS_ID%TYPE;
1504 v_co_code SY_ORGN_MST.CO_CODE%TYPE;
1505 v_old_order_qty1 PO_ORDR_DTL.ORDER_QTY1%TYPE;
1506 v_old_order_qty2 PO_ORDR_DTL.ORDER_QTY2%TYPE;
1507 v_old_order_um1 PO_ORDR_DTL.ORDER_UM1%TYPE;
1508 v_old_order_um2 PO_ORDR_DTL.ORDER_UM2%TYPE;
1509 v_old_extended_price PO_ORDR_DTL.EXTENDED_PRICE%TYPE;
1510 v_acct_map_ind NUMBER DEFAULT 0;
1511 v_map_retcode NUMBER DEFAULT 0;
1512 uomcv_item_id NUMBER;
1513
1514 /*PPB - Bug# 1365777 */
1515 v_noninv_ind IC_ITEM_MST.NONINV_IND%TYPE;
1516
1517 v_old_po_status PO_ORDR_DTL.PO_STATUS%TYPE;
1518 v_old_cancellation_code PO_ORDR_DTL.CANCELLATION_CODE%TYPE;
1519
1520 v_old_order_base_qty PO_ORDR_DTL.ORDER_QTY1%TYPE;
1521 v_old_order_sec_qty PO_ORDR_DTL.ORDER_QTY1%TYPE;
1522 v_total_received_qty NUMBER DEFAULT 0;
1523 v_total_received_qty2 NUMBER DEFAULT 0;
1524
1525 /* BEGIN - BUG 1785704 */
1526 v_release_date DATE;
1527 v_po_date DATE;
1528 v_creation_date DATE;
1529
1530 /* Bug 1829102 - Declaring variables and
1531 modifying cursor to fetch more fields */
1532
1533 v_created_by PO_RELEASES_ALL.created_by%TYPE;
1534 v_last_updated_by PO_RELEASES_ALL.last_updated_by%TYPE;
1535 v_last_update_date DATE;
1536 v_buyer_code VARCHAR2(35);
1537 v_agent_id PO_RELEASES_ALL.agent_id%TYPE;
1538
1539 CURSOR release_date_cur (
1540 v_po_header_id CPG_PURCHASING_INTERFACE.PO_HEADER_ID%TYPE,
1541 v_release_num CPG_PURCHASING_INTERFACE.RELEASE_NUM%TYPE)
1542 IS
1543 SELECT release_date,created_by, last_updated_by, last_update_date,agent_id
1544 FROM po_releases_all
1545 WHERE po_header_id = v_po_header_id
1546 AND release_num = v_release_num;
1547
1548 CURSOR buyer_code_cur ( v_agent_id PO_RELEASES_ALL.agent_id%TYPE )
1549 IS
1550 SELECT upper(substrb(last_name ,1,35))
1551 FROM per_people_f
1552 WHERE person_id=v_agent_id;
1553
1554 /* END - BUG 1829102,BUG 1785704 */
1555
1556 CURSOR bpo_cur (
1557 v_po_header_id CPG_PURCHASING_INTERFACE.PO_HEADER_ID%TYPE,
1558 v_po_line_id CPG_PURCHASING_INTERFACE.PO_LINE_ID%TYPE,
1559 v_source_shipment_id CPG_PURCHASING_INTERFACE.SOURCE_SHIPMENT_ID%TYPE
1560 )
1561 IS
1562 SELECT bpo_id, bpo_line_id
1563 FROM cpg_oragems_mapping
1564 WHERE po_header_id = v_po_header_id
1565 AND po_line_id = v_po_line_id
1566 AND po_line_location_id = v_source_shipment_id;
1567
1568 /* BUG#:1231038 match vendor_id with co_code */
1569 CURSOR vendor_cur(v_of_shipvend_site_id PO_VEND_MST.OF_VENDOR_SITE_ID%TYPE,
1570 v_co_code PO_VEND_MST.CO_CODE%TYPE)
1571 IS
1572 SELECT vendor_id
1573 FROM po_vend_mst
1574 WHERE of_vendor_site_id = v_of_shipvend_site_id
1575 AND co_code = v_co_code;
1576
1577 CURSOR terms_code_cur(v_of_terms_code OP_TERM_MST.OF_TERMS_CODE%TYPE) IS
1578 SELECT terms_code
1579 FROM op_term_mst
1580 WHERE of_terms_code = v_of_terms_code;
1581 /* Liz Enstone 18/AUG/98 Add orgn_code parameter*/
1582 CURSOR po_id_cur(v_po_no PO_ORDR_HDR.PO_NO%TYPE,
1583 v_orgn_code int_rec.orgn_code%TYPE) IS
1584 SELECT po_id
1585 FROM po_ordr_hdr
1586 WHERE po_no = v_po_no
1587 AND orgn_code = v_orgn_code;
1588
1589 CURSOR line_id_cur(v_po_header_id NUMBER, v_po_line_id NUMBER,
1590 v_po_line_location_id NUMBER) IS
1591 SELECT line_id
1592 FROM cpg_oragems_mapping
1593 WHERE po_header_id = v_po_header_id
1594 AND po_line_id = v_po_line_id
1595 AND po_line_location_id = v_po_line_location_id;
1596
1597 CURSOR item_cur(v_item_no IC_ITEM_MST.ITEM_NO%TYPE) IS
1598 SELECT item_id, nvl(item_desc1,' '), item_um, item_um2, dualum_ind, noninv_ind
1599 FROM ic_item_mst
1600 WHERE item_no = v_item_no;
1601
1602 CURSOR co_code_cur(v_orgn_code SY_ORGN_MST.ORGN_CODE%TYPE) IS
1603 SELECT co_code
1604 FROM sy_orgn_mst
1605 WHERE orgn_code = v_orgn_code;
1606
1607 CURSOR order_qty_price_cur(v_po_id NUMBER, v_line_id NUMBER) IS
1608 SELECT order_qty1, order_qty2,order_um1,order_um2, extended_price, line_no
1609 FROM po_ordr_dtl
1610 WHERE po_id = v_po_id
1611 AND line_id = v_line_id;
1612
1613 CURSOR Old_po_line_status_cur(v_po_id NUMBER, v_line_id NUMBER) IS
1614 SELECT po_status,cancellation_code
1615 FROM po_ordr_dtl
1616 WHERE po_id = v_po_id
1617 AND line_id = v_line_id;
1618
1619
1620 /* T. Ricci 11/10/98 added frtbill_mthd_cur so OPM value will be used*/
1621 CURSOR frtbill_mthd_cur(c_of_frtbill_mthd OP_FRGT_MTH.OF_FRTBILL_MTHD%TYPE) IS
1622 SELECT frtbill_mthd
1623 FROM op_frgt_mth
1624 WHERE of_frtbill_mthd = c_of_frtbill_mthd;
1625
1626 complete_msg VARCHAR2(2000);
1627
1628 v_next_id_status BOOLEAN;
1629 v_aqcst_status BOOLEAN;
1630 l_iret NUMBER := 0;
1631 v_date_flag NUMBER := 0;
1632
1633
1634 -- Bug 1882830, lswamy
1635 -- Should not check for inventory period, if po is closed at Apps end.
1636
1637 v_closed_code PO_LINE_LOCATIONS_ALL.CLOSED_CODE%TYPE;
1638
1639 CURSOR po_line_locns_cur(v_po_line_locations_id PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID%TYPE,
1640 v_po_header_id PO_LINE_LOCATIONS_ALL.PO_HEADER_ID%TYPE,
1641 v_po_line_id PO_LINE_LOCATIONS_ALL.PO_LINE_ID%TYPE) IS
1642
1643 SELECT closed_code
1644 FROM po_line_locations_all
1645 WHERE po_header_id = v_po_header_id
1646 AND po_line_id = v_po_line_id
1647 AND line_location_id = v_po_line_locations_id;
1648 -- End bug1882830.
1649
1650 /* Begin B2043468 */
1651
1652 CURSOR opm_po_line_dtl(v_po_id NUMBER, v_line_id NUMBER) IS
1653 SELECT order_qty1,received_qty1
1654 FROM po_ordr_dtl
1655 WHERE po_id = v_po_id
1656 AND line_id = v_line_id;
1657
1658 rec_opm_po_line_dtl opm_po_line_dtl%ROWTYPE;
1659
1660 CURSOR item_dtl(v_item_no IC_ITEM_MST.ITEM_NO%TYPE) IS
1661 SELECT item_id,item_um2
1662 FROM ic_item_mst
1663 WHERE item_no = v_item_no;
1664
1665 rec_item_dtl item_dtl%ROWTYPE;
1666
1667 /* End B2043468 */
1668
1669 /* Begin B2594000 */
1670 cursor check_dup is
1671 select 1
1672 from po_ordr_dtl
1673 where po_id = v_po_id
1674 and line_no = v_line_no;
1675
1676 v_check NUMBER := 0;
1677 error_ind NUMBER := 0;
1678 /* End B2594000 */
1679
1680 ic_summ_inv_view_exists number:=0; /* Bug 3019986 Mohit Kapoor */
1681 p_view_owner VARCHAR2(30) ;
1682
1683 BEGIN
1684
1685 -- GSCC b4403407 fix for File.Sql.47
1686 select oracle_username
1687 into p_view_owner
1688 from fnd_oracle_userid
1689 where read_only_flag = 'U';
1690
1691 /* Begin B3019986 Mohit Kapoor */
1692 SELECT COUNT(*) INTO ic_summ_inv_view_exists
1693 FROM ALL_VIEWS
1694 WHERE VIEW_NAME = 'IC_SUMM_INV_V' and
1695 owner = p_view_owner ;
1696
1697 /* End B3019986 */
1698
1699 OPEN int_cur;
1700
1701 FETCH int_cur INTO int_rec;
1702
1703 -- Begin bug1882830
1704 OPEN po_line_locns_cur(int_rec.po_line_location_id,int_rec.po_header_id,int_rec.po_line_id);
1705 FETCH po_line_locns_cur INTO v_closed_code;
1706 CLOSE po_line_locns_cur;
1707 -- End bug188283
1708
1709
1710 WHILE int_cur%FOUND
1711 LOOP
1712 retcode := 0;
1713 v_retval := 0;
1714 error_ind := 0; /* B2594000 */
1715
1716 -- bug1882830, Introduced the following condition.
1717
1718 IF (v_closed_code NOT IN ('CLOSED','FINALLY CLOSED')) THEN
1719
1720 /* T Ricci 11/24/98 added inventory calendar check*/
1721 v_retval := GMICCAL.trans_date_validate(int_rec.agreed_dlvdate,
1722 int_rec.orgn_code,int_rec.to_whse);
1723 END IF;
1724
1725 IF v_retval < 0 THEN
1726 IF int_rec.transaction_type IN('PLANNED','BLANKET') AND int_rec.release_num <> 0 THEN
1727 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1728 FND_FILE.PUT_LINE(FND_FILE.LOG,
1729 'Inventory Calendar not set up for PO: '||int_rec.po_no||'-'||TO_CHAR(int_rec.release_num));
1730 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1731 ELSE
1732 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1733 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inventory Calendar not set up for PO: '|| int_rec.po_no);
1734 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1735 END IF;
1736
1737 /* Bug#1274130 */
1738
1739 IF v_retval = -21 THEN /* Fiscal Yr and Fiscal Yr beginning date
1740 not found. */
1741 FND_MESSAGE.SET_NAME('GMI','INVCAL_FISCALYR_ERR');
1742 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1743 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1744
1745 ELSIF v_retval = -22 THEN /* Period end date and close indicator
1746 not found. */
1747 FND_MESSAGE.SET_NAME('GMI','INVCAL_PERIOD_ERR');
1748 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1749 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1750
1751 ELSIF v_retval = -23 THEN /* Date is within a closed Inventory
1752 calendar period */
1753 /*
1754 FND_MESSAGE.SET_NAME('GMI','INVCAL_CLOSED_PERIOD_ERR');
1755 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1756 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1757 */
1758 v_retval := 0;
1759
1760 ELSIF v_retval = -24 THEN /* Company Code not found. */
1761
1762 FND_MESSAGE.SET_NAME('GMI','INVCAL_INVALIDCO_ERR');
1763 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1764 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1765
1766 ELSIF v_retval = -25 THEN /* Warehouse has been closed for the period */
1767 /*
1768 FND_MESSAGE.SET_NAME('GMI','INVCAL_WHSE_CLOSED_ERR');
1769 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1770 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1771 */
1772 v_retval := 0;
1773
1774 ELSIF v_retval = -26 THEN /* Transaction not passed in as
1775 a parameter.*/
1776 FND_MESSAGE.SET_NAME('GMI','INVCAL_TRANS_DATE_ERR');
1777 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1778 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1779
1780 ELSIF v_retval = -27 THEN /* Organization code not passed as
1781 a parameter.*/
1782 FND_MESSAGE.SET_NAME('GMI','INVCAL_INVALIDORGN_ERR');
1783 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1784 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1785
1786 ELSIF v_retval = -28 THEN /* Warehouse code not passed as
1787 a parameter.*/
1788 FND_MESSAGE.SET_NAME('GMI','INVCAL_WHSEPARM_ERR');
1789 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1790 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1791
1792 ELSIF v_retval = -29 THEN /* Warehouse code is not found. */
1793
1794 FND_MESSAGE.SET_NAME('GMI','INVCAL_WHSE_ERR ');
1795 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1796 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1797
1798 ELSIF v_retval < -29 THEN /* Log a general message */
1799 FND_MESSAGE.SET_NAME('GMI','INVCAL_GENL_ERR ');
1800 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1801 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
1802
1803 END IF;
1804
1805 IF (v_retval NOT IN (-23, -25)) THEN
1806 retcode :=1;
1807 END IF;
1808
1809 END IF;
1810
1811 /* T Ricci 5/12/98 changed date_modified to last_update_date*/
1812
1813 IF GML_PO_SYNCH.gemms_validate(
1814 int_rec.orgn_code,
1815 int_rec.of_payvend_site_id,
1816 int_rec.of_shipvend_site_id,
1817 int_rec.to_whse,
1818 int_rec.billing_currency,
1819 int_rec.item_no,
1820 int_rec.order_um1,
1821 int_rec.price_um,
1822 int_rec.order_um2,
1823 int_rec.item_um,
1824 int_rec.buyer_code,
1825 int_rec.from_whse,
1826 int_rec.shipper_code,
1827 int_rec.of_frtbill_mthd,
1828 int_rec.of_terms_code,
1829 int_rec.qc_grade_wanted,
1830 int_rec.po_no,
1831 int_rec.po_line_id,
1832 int_rec.po_line_location_id,
1833 int_rec.revision_count,
1834 int_rec.last_update_date) = FALSE OR
1835 v_retval < 0 THEN
1836
1837 UPDATE cpg_purchasing_interface
1838 SET invalid_ind = 'Y'
1839 WHERE rowid = int_rec.rowid;
1840
1841 retcode :=1;
1842
1843 ELSE /* all fields are validate*/
1844
1845 /*IF int_rec.transaction_type = 'STANDARD' THEN
1846 v_doc_type := 'PORD';
1847 ELSE
1848 v_doc_type := 'PBPO';
1849 END IF;*/
1850 v_doc_type := 'PORD';
1851
1852 /* BEGIN - BUG 1785704 */
1853 v_po_date := int_rec.po_date;
1854 /* END - BUG 1785704 */
1855
1856 /* Begin - Bug 1781846 */
1857 v_date_flag := FND_PROFILE.VALUE('PO$DEF_DATE');
1858
1859 IF (v_date_flag = 1) THEN
1860 v_creation_date := int_rec.po_date; -- approved date
1861 ELSIF (v_date_flag = 2) THEN
1862 v_creation_date := int_rec.creation_date;
1863 ELSE
1864 v_creation_date := SYSDATE;
1865 END IF;
1866
1867 /* End - Bug 1781846 */
1868
1869
1870
1871 /* Begin bug 1829102 */
1872
1873 v_created_by := nvl(int_rec.created_by,0);
1874 v_last_updated_by := nvl(int_rec.last_updated_by,0);
1875 v_last_update_date := nvl(int_rec.last_update_date,sysdate);
1876 v_buyer_code := int_rec.buyer_code;
1877
1878 /* End bug 1829102 */
1879
1880
1881 IF int_rec.transaction_type = 'PLANNED' THEN
1882 OPEN bpo_cur (int_rec.po_header_id, int_rec.po_line_id,
1883 int_rec.source_shipment_id);
1884 FETCH bpo_cur INTO v_bpo_id, v_bpo_line_id;
1885 CLOSE bpo_cur;
1886
1887 /* BEGIN - BUG 1829102, BUG 1785704 */
1888 OPEN release_date_cur (int_rec.po_header_id,int_rec.release_num);
1889 FETCH release_date_cur into v_release_date,v_created_by,v_last_updated_by,v_last_update_date,v_agent_id;
1890 CLOSE release_date_cur;
1891
1892 IF (v_agent_id is NOT NULL) THEN
1893 OPEN buyer_code_cur (v_agent_id);
1894 FETCH buyer_code_cur INTO v_buyer_code;
1895 CLOSE buyer_code_cur;
1896 END IF;
1897
1898 v_po_date := v_release_date;
1899
1900 /* END - BUG 1829102,BUG 1785704 */
1901
1902 v_po_no := CONCAT (CONCAT(int_rec.po_no, '-'),
1903 TO_CHAR(int_rec.release_num)
1904 );
1905
1906 ELSIF int_rec.transaction_type = 'BLANKET' THEN
1907 v_bpo_id :=NULL; /* KYH 25/AUG/98 Foreign key constraints*/
1908 v_bpo_line_id :=NULL; /* KYH 25/AUG/98 Foreign key constraints*/
1909
1910 /* BEGIN - BUG 1829102, BUG 1785704 */
1911 OPEN release_date_cur (int_rec.po_header_id,int_rec.release_num);
1912 FETCH release_date_cur into v_release_date ,v_created_by,v_last_updated_by,v_last_update_date, v_agent_id;
1913 CLOSE release_date_cur;
1914
1915 IF (v_agent_id is NOT NULL) THEN
1916 OPEN buyer_code_cur (v_agent_id);
1917 FETCH buyer_code_cur INTO v_buyer_code;
1918 CLOSE buyer_code_cur;
1919 END IF;
1920
1921 v_po_date := v_release_date;
1922
1923 /* END - BUG 1829102,BUG 1785704 */
1924
1925 v_po_no := CONCAT (CONCAT(int_rec.po_no, '-'),
1926 TO_CHAR(int_rec.release_num)
1927 );
1928
1929 ELSE /* Standard PO's */
1930 v_bpo_id :=NULL; /* T.Ricci 7/6/98 integ constraint change*/
1931 v_bpo_line_id :=NULL; /* T.Ricci 7/7/98 integ constraint change*/
1932 v_po_no := int_rec.po_no;
1933 END IF;
1934
1935 /* T. Ricci 5/12/98 set po_status to '0' for cancelled PO's */
1936 /* Uday Phadtare B1820461 do not change v_po_status to 20 if CLOSED FOR INVOICE */
1937 IF (int_rec.po_status = 'OPEN' OR int_rec.po_status = 'CLOSED FOR INVOICE') THEN
1938 v_po_status := 0;
1939 ELSIF int_rec.po_status = 'CANCEL' THEN
1940 v_po_status := 0;
1941 ELSIF (int_rec.po_status = 'CLOSED' OR
1942 int_rec.po_status = 'CLOSED FOR RECEIVING' OR
1943 /* int_rec.po_status = 'CLOSED FOR INVOICE' OR B1820461 */
1944 int_rec.po_status = 'FINALLY CLOSED') THEN
1945 v_po_status := 20;
1946 END IF;
1947
1948 GML_VALIDATE_PO.get_base_currency (v_base_currency,
1949 int_rec.orgn_code);
1950
1951 /* HW BUG#:1107267 don't get exchange rate, rate is already in cpg_purchasing_interface */
1952 /* IF (v_base_currency <> int_rec.billing_currency) THEN
1953
1954 GML_VALIDATE_PO.get_gl_source (v_gl_source_type);
1955
1956 GML_VALIDATE_PO.get_exchange_rate (v_exchange_rate,
1957 v_mul_div_sign,
1958 v_exchange_rate_date,
1959 v_base_currency,
1960 int_rec.billing_currency,
1961 v_gl_source_type);
1962 END IF;
1963 */
1964
1965 /* HW BUG#:1231038 match payvendor_id and shipvend_id with correct co_code */
1966 OPEN co_code_cur(int_rec.orgn_code);
1967 FETCH co_code_cur
1968 INTO v_co_code;
1969 CLOSE co_code_cur;
1970
1971 OPEN vendor_cur(int_rec.of_shipvend_site_id,v_co_code);
1972 FETCH vendor_cur
1973 INTO v_ship_vendor_id;
1974 CLOSE vendor_cur;
1975
1976 OPEN vendor_cur(int_rec.of_payvend_site_id,v_co_code);
1977 FETCH vendor_cur
1978 INTO v_pay_vendor_id;
1979 CLOSE vendor_cur;
1980
1981 OPEN frtbill_mthd_cur(int_rec.of_frtbill_mthd);
1982 FETCH frtbill_mthd_cur
1983 INTO v_frtbill_mthd;
1984 CLOSE frtbill_mthd_cur;
1985
1986 OPEN terms_code_cur(int_rec.of_terms_code);
1987 FETCH terms_code_cur
1988 INTO v_terms_code;
1989 CLOSE terms_code_cur;
1990
1991 /* if the first encounter with a PO, insert the header*/
1992 /* Liz Enstone 18/AUG/98 Include orgn code parameter*/
1993 IF GML_PO_SYNCH.new_po_hdr(v_po_no,int_rec.orgn_code) THEN
1994 BEGIN
1995
1996
1997 GML_PO_SYNCH.next_po_id(v_new_po_id, v_doc_type, int_rec.orgn_code,
1998 v_next_id_status);
1999 IF v_next_id_status=FALSE THEN
2000 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2001 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error getting next po_id');
2002 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2003 err_num := SQLCODE;
2004 err_msg := SUBSTRB (SQLERRM, 1, 100);
2005 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2006 retcode :=1;
2007 raise_application_error(-20001, err_msg);
2008 END IF;
2009
2010 /* BEGIN BUG#1731582 P. Arvind Dath */
2011
2012 OPEN attr_hdr_cur(int_rec.PO_HEADER_ID);
2013 FETCH attr_hdr_cur INTO attr_hdr_rec;
2014 CLOSE attr_hdr_cur;
2015
2016
2017 /* T. Ricci 5/12/98 removed user_class and changed who */
2018 /* columns for GEMMS 5.0*/
2019 BEGIN
2020 INSERT INTO po_ordr_hdr
2021 ( po_id,
2022 orgn_code,
2023 po_no,
2024 bpo_id,
2025 bpo_release_no,
2026 po_type,
2027 payvend_id,
2028 shipvend_id,
2029 recvaddr_id,
2030 shipper_code,
2031 recv_desc,
2032 from_whse,
2033 to_whse,
2034 recv_loct,
2035 ship_mthd,
2036 frtbill_mthd,
2037 purchase_exchange_rate,
2038 mul_div_sign,
2039 billing_currency,
2040 currency_bght_fwd,
2041 terms_code,
2042 po_status,
2043 pohold_code,
2044 cancellation_code,
2045 fob_code,
2046 buyer_code,
2047 icpurch_class,
2048 vendso_no,
2049 project_no,
2050 po_date,
2051 requested_dlvdate,
2052 sched_shipdate,
2053 required_dlvdate,
2054 agreed_dlvdate,
2055 date_printed,
2056 expedite_date,
2057 revision_count,
2058 in_use,
2059 print_count,
2060 creation_date,
2061 created_by,
2062 last_update_date,
2063 last_updated_by,
2064 last_update_login,
2065 delete_mark,
2066 text_code,
2067 exported_date,
2068 attribute1,
2069 attribute2,
2070 attribute3,
2071 attribute4,
2072 attribute5,
2073 attribute6,
2074 attribute7,
2075 attribute8,
2076 attribute9,
2077 attribute10,
2078 attribute11,
2079 attribute12,
2080 attribute13,
2081 attribute14
2082 )
2083 VALUES /* Insertion */
2084 ( v_new_po_id,
2085 nvl(int_rec.orgn_code,'-1'), /* orgn_code,*/
2086 v_po_no, /* modified int_rec.po_no to v_po_no*/
2087 v_bpo_id, /* modified int_rec.bpo_id to v_bpo_id*/
2088 /* T. Ricci 7/6/98 NULL OK.*/
2089 int_rec.bpo_release_number, /* LE 25/08/98 NULL OK*/
2090 nvl(int_rec.po_type,0),
2091 nvl(v_pay_vendor_id,0),
2092 nvl(v_ship_vendor_id,0),
2093 int_rec.recvaddr_id, /* T. Ricci 7/6/98 integ constraint chg*/
2094 /* LE 25/08/98 NULL OK*/
2095 int_rec.shipper_code,
2096 int_rec.recv_desc, /* LE 25/08/98 NULL OK*/
2097 int_rec.from_whse, /* T.Ricci 7/6/98 integ constraint change*/
2098 int_rec.to_whse, /* mandatory field, checked not null*/
2099 int_rec.recv_loct, /* T.Ricci 7/7/98 integ constraint change*/
2100 int_rec.ship_mthd, /* T.Ricci 7/6/98 integ constraint change*/
2101 v_frtbill_mthd, /* LE 25/08/98 NULL OK*/
2102 /* nvl(v_exchange_rate, 1), BUG#:1107267. Get value from cpg_purchasing_interface */
2103 nvl(int_rec.purchase_exchange_rate, 1),
2104 /* nvl(v_mul_div_sign, 0), BUG#:1107267. Get value from cpg_purchasing_interface */
2105 nvl(int_rec.mul_div_sign, 0),
2106 int_rec.billing_currency, /*mandatory field, not null*/
2107 nvl(int_rec.currency_bght_fwd, 0),
2108 v_terms_code, /* KYH 28/AUG/98 nullable column */
2109 nvl(v_po_status, 0),
2110 int_rec.pohold_code, /* T.Ricci 7/6/98 integ constraint chg*/
2111 int_rec.cancellation_code, /* T.Ricci 7/6/98 integ constraint chg*/
2112 int_rec.fob_code, /* LE 25/08/98 NULL OK*/
2113 v_buyer_code, /*LE 25/08/98 NULL OK,Lswamy - BUG 1829102*/
2114 int_rec.icpurch_class, /* T.Ricci 7/6/98 integ constraint chg*/
2115 int_rec.vendso_no, /* LE 25/08/98 NULL OK */
2116 int_rec.project_no, /* T.Ricci 7/6/98 integ constraint chg*/
2117 nvl(v_po_date, SYSDATE), /* PKU - BUG 1785704 */
2118 nvl(int_rec.requested_dlvdate, SYSDATE),
2119 nvl(int_rec.sched_shipdate, SYSDATE),
2120 nvl(int_rec.required_dlvdate, SYSDATE),
2121 nvl(int_rec.agreed_dlvdate, SYSDATE),
2122 nvl(int_rec.date_printed, SYSDATE),
2123 int_rec.expedite_date, /* LE 25/AUG/98 NULL OK*/
2124 int_rec.revision_count, /* LE 25/AUG/98 NULL OK*/
2125 nvl(int_rec.in_use, 0),
2126 int_rec.print_count, /* LE 25/AUG/98 NULL OK*/
2127 nvl(v_creation_date,sysdate), /* PKU - BUG 1785704 */
2128 nvl(v_created_by,0), /* lswamy - BUG 1829102 */
2129 nvl(v_last_update_date,sysdate), /* lswamy - BUG 1829102 */
2130 nvl(v_last_updated_by,0), /* lswamy - BUG 1829102 */
2131 int_rec.last_update_login, /* LE 25/08/98 NULL OK*/
2132 nvl(int_rec.delete_mark,0),
2133 int_rec.text_code, /* T. Ricci 7/6/98 integ constraint chg*/
2134 int_rec.exported_date, /* LE 25/08/98 NULL OK*/
2135 attr_hdr_rec.attribute1,
2136 attr_hdr_rec.attribute2,
2137 attr_hdr_rec.attribute3,
2138 attr_hdr_rec.attribute4,
2139 attr_hdr_rec.attribute5,
2140 attr_hdr_rec.attribute6,
2141 attr_hdr_rec.attribute7,
2142 attr_hdr_rec.attribute8,
2143 attr_hdr_rec.attribute9,
2144 attr_hdr_rec.attribute10,
2145 attr_hdr_rec.attribute11,
2146 attr_hdr_rec.attribute12,
2147 attr_hdr_rec.attribute13,
2148 attr_hdr_rec.attribute14
2149 );
2150 EXCEPTION
2151 WHEN OTHERS THEN
2152 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2153 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into po_ordr_hdr')
2154 ;
2155 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2156 err_num := SQLCODE;
2157 err_msg := SUBSTRB (SQLERRM, 1, 100);
2158 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2159 retcode :=1;
2160 raise_application_error(-20001, err_msg);
2161 END;
2162 END;
2163 /* END BUG#1731582 */
2164
2165 /* PO ordr header OK*/
2166 END IF; /* new PO*/
2167
2168 OPEN item_cur(int_rec.item_no);
2169 FETCH item_cur INTO v_item_id, v_item_desc,v_item_um1, v_item_um2, v_dualum_ind,v_noninv_ind;
2170 CLOSE item_cur;
2171
2172 /* Bug 1857224 */
2173 OPEN po_lines_all_cur (int_rec.po_header_id, int_rec.po_line_id);
2174 FETCH po_lines_all_cur INTO v_item_desc;
2175 CLOSE po_lines_all_cur;
2176 /* End Bug 1857224 */
2177
2178
2179 /*modified int_rec.po_no to v_po_no*/
2180 /* Liz Enstone 18/AUG/98 Add orgn_code to parameters */
2181 OPEN po_id_cur(v_po_no,int_rec.orgn_code);
2182 FETCH po_id_cur INTO v_po_id;
2183 CLOSE po_id_cur;
2184
2185 /* 11/6/1998 T. Ricci commented*/
2186 /* GML_PO_SYNCH.cpg_conv_duom
2187 (v_item_id,
2188 int_rec.order_um1,
2189 int_rec.order_qty1,
2190 v_item_um2,
2191 v_order2); */
2192
2193 uomcv_item_id := v_item_id;
2194
2195
2196 /* Added by PPB for UOM changes */
2197 IF (int_rec.order_um1 <> v_item_um1) THEN
2198 v_order1 := GMICUOM.uom_conversion
2199 (v_item_id,0,
2200 int_rec.order_qty1,
2201 int_rec.order_um1,
2202 v_item_um1,0);
2203 ELSE
2204 v_order1 := int_rec.order_qty1;
2205 END IF;
2206
2207 /* 11/6/1998 T. Ricci added*/
2208 IF v_dualum_ind > 0 THEN
2209 v_order2 :=GMICUOM.uom_conversion
2210 (v_item_id,0,
2211 int_rec.order_qty1,
2212 int_rec.order_um1,
2213 v_item_um2,0);
2214 ELSE
2215 v_order2 := 0;
2216 END IF;
2217
2218
2219 IF GML_PO_SYNCH.new_line(int_rec.po_header_id, int_rec.po_line_id,
2220 int_rec.po_line_location_id) THEN
2221 BEGIN
2222
2223 /* insert the line location information*/
2224
2225 GML_PO_SYNCH.next_line_id('PO', v_new_line_id, v_next_id_status);
2226 IF (v_next_id_status=FALSE) THEN
2227 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2228 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error getting next line_id');
2229 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2230 err_num := SQLCODE;
2231 err_msg := SUBSTRB (SQLERRM, 1, 100);
2232 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2233 retcode :=1;
2234 raise_application_error(-20001, err_msg);
2235 END IF;
2236
2237 /* 03/24/00 NC - TAR# 12693733.6 (Bug# 1249797 ; Base Bug#1247332)
2238 OPM was generating it's own line number regardless of what
2239 the line number is in oracle.Modified to take oracle line number
2240 instead.
2241 */
2242
2243 /* v_line_no := GML_PO_SYNCH.get_line_no(v_po_id) + 1; */
2244
2245 v_line_no := GML_PO_SYNCH.get_line_no( v_po_id,
2246 int_rec.po_header_id,
2247 int_rec.po_line_id,
2248 int_rec.po_line_location_id,
2249 int_rec.transaction_type);
2250
2251 /* BEGIN BUG#1731582 P. Arvind Dath */
2252
2253 OPEN attr_dtl_cur(int_rec.PO_LINE_LOCATION_ID,
2254 int_rec.PO_HEADER_ID,
2255 int_rec.PO_LINE_ID);
2256 FETCH attr_dtl_cur INTO attr_dtl_rec;
2257 CLOSE attr_dtl_cur;
2258
2259 /* T. Ricci 5/12/98 removed user_class and changed who */
2260 /* columns for GEMMS 5.0*/
2261
2262 /* Begin B2594000 */
2263 OPEN check_dup;
2264 FETCH check_dup into v_check;
2265 IF check_dup%FOUND THEN
2266 error_ind := 1;
2267 ELSE
2268 error_ind := 0;
2269 END IF;
2270 CLOSE check_dup;
2271 /* End B2594000 */
2272
2273 IF error_ind = 0 THEN /* B2594000 */
2274 BEGIN
2275 INSERT INTO po_ordr_dtl
2276 ( po_id,
2277 line_no,
2278 line_id,
2279 bpo_line_id,
2280 apinv_line_id,
2281 item_id,
2282 generic_id,
2283 item_desc,
2284 icpurch_class,
2285 order_qty1,
2286 order_qty2,
2287 order_um1,
2288 order_um2,
2289 received_qty1,
2290 received_qty2,
2291 net_price,
2292 extended_price,
2293 price_um,
2294 recvaddr_id,
2295 ship_mthd,
2296 shipper_code,
2297 shipvend_id,
2298 to_whse,
2299 from_whse,
2300 recv_loct,
2301 recv_desc,
2302 qc_grade_wanted,
2303 frtbill_mthd,
2304 terms_code,
2305 pohold_code,
2306 cancellation_code,
2307 fob_code,
2308 vendso_no,
2309 buyer_code,
2310 project_no,
2311 agreed_dlvdate,
2312 requested_dlvdate,
2313 required_dlvdate,
2314 sched_shipdate,
2315 expedite_date,
2316 po_status,
2317 creation_date,
2318 created_by,
2319 last_update_date,
2320 last_updated_by,
2321 last_update_login,
2322 text_code,
2323 trans_cnt,
2324 match_type,
2325 exported_date,
2326 attribute1,
2327 attribute2,
2328 attribute3,
2329 attribute4,
2330 attribute5,
2331 attribute6,
2332 attribute7,
2333 attribute8,
2334 attribute9,
2335 attribute10,
2336 attribute11,
2337 attribute12,
2338 attribute13,
2339 attribute14,
2340 attribute15
2341 )
2342 VALUES
2343 ( v_po_id,
2344 v_line_no,
2345 v_new_line_id,
2346 v_bpo_line_id, /* T.Ricci 7/7/98 integ constraint change*/
2347 int_rec.apinv_line_id, /* LE 25/AUG/98 NULL OK*/
2348 v_item_id, /*mandatory field, already checked not null*/
2349 int_rec.generic_id, /* T.Ricci 7/7/98 integ constraint change*/
2350 nvl(v_item_desc, ' '),
2351 int_rec.icpurch_class, /* T.Ricci 7/7/98 integ constraint change*/
2352 nvl(int_rec.order_qty1,0),
2353 v_order2, /* LE 25/AUG/98 NULL OK*/
2354 int_rec.order_um1, /*mandatory field, not null*/
2355 v_item_um2, /* LE 25/AUG/98 NULL OK*/
2356 int_rec.received_qty1, /* LE 25/AUG/98 NULL OK*/
2357 int_rec.received_qty2, /* LE 25/AUG/98 NULL OK*/
2358 nvl(int_rec.net_price,0),
2359 nvl(int_rec.extended_price,0),
2360 nvl(int_rec.price_um,' '),
2361 int_rec.recvaddr_id, /* T.Ricci 7/7/98 integ constraint change*/
2362 int_rec.ship_mthd, /* T.Ricci 7/7/98 integ constraint change*/
2363 int_rec.shipper_code, /* LE 25/AUG/98 NULL OK*/
2364 v_ship_vendor_id, /*mandatory, checked not null*/
2365 nvl(int_rec.to_whse, ' '),
2366 int_rec.from_whse, /* T.Ricci 7/7/98 integ constraint change*/
2367 int_rec.recv_loct, /* T.Ricci 7/7/98 integ constraint change*/
2368 int_rec.recv_desc, /* LE 25/AUG/98 NULL OK*/
2369 int_rec.qc_grade_wanted, /* LE 25/AUG/98 NULL OK*/
2370 v_frtbill_mthd, /* LE 25/AUG/98 NULL OK*/
2371 v_terms_code, /* Bug 2237409 - PKU */
2372 int_rec.pohold_code, /* T.Ricci 7/7/98 integ constraint change*/
2373 int_rec.cancellation_code, /* T.Ricci 7/7/98 integ constraint change */
2374 int_rec.fob_code, /* LE 25/AUG/98 NULL OK*/
2375 int_rec.vendso_no, /* LE 25/AUG/98 NULL OK */
2376 v_buyer_code, /* LE 25/AUG/98 NULL OK ,Lswamy - BUG 1829102*/
2377 int_rec.project_no, /* T.Ricci 7/7/98 integ constraint change*/
2378 nvl(int_rec.agreed_dlvdate, sysdate),
2379 nvl(int_rec.requested_dlvdate, sysdate),
2380 nvl(int_rec.required_dlvdate, sysdate),
2381 nvl(int_rec.sched_shipdate, sysdate),
2382 int_rec.expedite_date, /* LE 25/AUG/98 NULL OK*/
2383 nvl(v_po_status,0), /*modified int_rec.po_status to v_po_status*/
2384 nvl(v_creation_date,sysdate), /* PKU - BUG 1785704 */
2385 nvl(v_created_by,0), /* Lswamy - BUG 1829102 */
2386 nvl(v_last_update_date,sysdate), /* Lswamy - BUG 1829102 */
2387 nvl(v_last_updated_by,0), /* Lswamy - BUG 1829102 */
2388 int_rec.last_update_login, /* LE 25/AUG/98 NULL OK*/
2389 int_rec.text_code, /* T.Ricci 7/7/98 integ constraint change*/
2390 0, /* T. Ricci 11/10/98 set trans_cnt to '0'*/
2391 nvl(int_rec.match_type, 3),
2392 int_rec.exported_date, /* LE 25/AUG/98 NULL OK*/
2393 attr_dtl_rec.attribute1,
2394 attr_dtl_rec.attribute2,
2395 attr_dtl_rec.attribute3,
2396 attr_dtl_rec.attribute4,
2397 attr_dtl_rec.attribute5,
2398 attr_dtl_rec.attribute6,
2399 attr_dtl_rec.attribute7,
2400 attr_dtl_rec.attribute8,
2401 attr_dtl_rec.attribute9,
2402 attr_dtl_rec.attribute10,
2403 attr_dtl_rec.attribute11,
2404 attr_dtl_rec.attribute12,
2405 attr_dtl_rec.attribute13,
2406 attr_dtl_rec.attribute14,
2407 attr_dtl_rec.attribute15
2408 );
2409 EXCEPTION
2410 WHEN OTHERS THEN
2411 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2412 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into po_ordr_dtl');
2413 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2414 err_num := SQLCODE;
2415 err_msg := SUBSTRB (SQLERRM, 1, 100);
2416 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2417 retcode :=1;
2418 raise_application_error(-20001, err_msg);
2419 END;
2420 /* END BUG#1731582 */
2421
2422 /* BEGIN - Bug 1854280 Pushkar Upakare */
2423 IF int_rec.transaction_type = 'PLANNED' THEN
2424
2425 BEGIN
2426 UPDATE po_bpos_hdr
2427 SET amount_purchased = nvl(amount_purchased,0) + nvl(int_rec.extended_price,0),
2428 activity_ind = 1,
2429 rel_count = nvl(rel_count,0) + 1,
2430 last_update_date = nvl(int_rec.last_update_date, SYSDATE),
2431 last_updated_by = nvl(int_rec.last_updated_by, 0),
2432 last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
2433 WHERE bpo_id = v_bpo_id;
2434 EXCEPTION
2435 WHEN OTHERS THEN
2436 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2437 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_bpos_hdr');
2438 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2439 err_num := SQLCODE;
2440 err_msg := SUBSTRB (SQLERRM, 1, 100);
2441 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2442 retcode :=1;
2443 raise_application_error(-20001, err_msg);
2444 END;
2445
2446 BEGIN
2447 UPDATE po_bpos_dtl
2448 SET amount_purchased = nvl(amount_purchased,0) + nvl(int_rec.extended_price,0),
2449 qty_purchased = nvl(qty_purchased,0) + nvl(int_rec.order_qty1,0),
2450 last_update_date = nvl(int_rec.last_update_date, SYSDATE),
2451 last_updated_by = nvl(int_rec.last_updated_by, 0),
2452 last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
2453 WHERE bpo_id = v_bpo_id
2454 AND line_id = v_bpo_line_id;
2455 EXCEPTION
2456 WHEN OTHERS THEN
2457 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2458 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_bpos_dtl');
2459 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2460 err_num := SQLCODE;
2461 err_msg := SUBSTRB (SQLERRM, 1, 100);
2462 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2463 retcode :=1;
2464 raise_application_error(-20001, err_msg);
2465 END;
2466
2467 END IF; /*int_rec.transaction_type = 'PLANNED' */
2468 /* END - Bug 1854280*/
2469
2470 /*insert into the ic_tran_pnd table*/
2471
2472
2473 OPEN co_code_cur(int_rec.orgn_code);
2474 FETCH co_code_cur
2475 INTO v_co_code;
2476 CLOSE co_code_cur;
2477
2478 GML_PO_SYNCH.next_trans_id(v_new_trans_id, v_next_id_status);
2479 IF v_next_id_status=FALSE THEN
2480 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2481 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error getting next trans_id');
2482 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2483 err_num := SQLCODE;
2484 err_msg := SUBSTRB (SQLERRM, 1, 100);
2485 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2486 retcode :=1;
2487 raise_application_error(-20001, err_msg);
2488 END IF;
2489
2490 BEGIN /*insert into ic_tran_pnd*/
2491 /* T. Ricci 5/12/98 changed who columns for GEMMS 5.0*/
2492
2493 INSERT INTO ic_tran_pnd
2494 ( item_id,
2495 line_id,
2496 trans_id,
2497 co_code,
2498 orgn_code,
2499 whse_code,
2500 lot_id,
2501 location,
2502 doc_id,
2503 doc_type,
2504 doc_line,
2505 line_type,
2506 reason_code,
2507 trans_date,
2508 trans_qty,
2509 trans_qty2,
2510 qc_grade,
2511 lot_status,
2512 trans_stat,
2513 trans_um,
2514 trans_um2,
2515 op_code,
2516 completed_ind,
2517 staged_ind,
2518 gl_posted_ind,
2519 event_id,
2520 delete_mark,
2521 text_code,
2522 last_update_date,
2523 last_updated_by,
2524 last_update_login,
2525 created_by,
2526 creation_date
2527 )
2528 VALUES
2529 ( v_item_id, /*mandatory, checked not null*/
2530 v_new_line_id,
2531 v_new_trans_id,
2532 nvl(v_co_code, '-1'),
2533 nvl(int_rec.orgn_code, '-1'), /*GEMMS Organization Code*/
2534 int_rec.to_whse, /*mandatory*/
2535 0, /*lot_id always initialized to 0*/
2536 fnd_profile.value('IC$DEFAULT_LOCT'), /*location KYH 17/AUG/98*/
2537 v_po_id, /*doc_id,*/
2538 v_doc_type, /*doc_type,*/
2539 0, /*doc_line, not used*/
2540 0, /*line_type,*/
2541 NULL, /*reason_code T.Ricci added NULL,*/
2542 nvl(int_rec.agreed_dlvdate, SYSDATE), /*trans_date T.Ricci changed to*/
2543 /* agreed_dlvdate 7/27,*/
2544 nvl(v_order1,0), /* PPB-UOM nvl(int_rec.order_qty1, 0), trans_qty,*/
2545 v_order2, /*trans_qty2, LE 26/AUG/98 NULL OK*/
2546 int_rec.qc_grade_wanted, /* LE 26/AUG/98 NULL OK */
2547 NULL, /*lot_status T.Ricci added NULL,*/
2548 NULL, /*trans_stat, LE 26/AUG/98 NULL OK*/
2549 v_item_um1, /* PKU - bug 1516895, not null*/
2550 v_item_um2, /*um2 T.Ricci integ constraint change */
2551 /*1004, op_code T.Ricci change to number,*/
2552 nvl(int_rec.created_by, 0), /*op_code T.Ricci change to number,*/
2553 0, /*completed_ind,*/
2554 0, /*staged_ind,*/
2555 0, /*gl_posted_ind,*/
2556 0, /*event_id,*/
2557 0, /*delete_mark,*/
2558 NULL, /*text_code,*/
2559 nvl(int_rec.last_update_date,sysdate), /* last_update_date,*/
2560 nvl(int_rec.last_updated_by,0), /* last_updated_by,*/
2561 int_rec.last_update_login, /* last_update_login, LE 26/AUG/98 NULL OK*/
2562 nvl(int_rec.created_by,0), /* created_by,*/
2563 nvl(v_creation_date,sysdate) /* PKU - BUG 1785704 */
2564 );
2565
2566 EXCEPTION
2567 WHEN OTHERS THEN
2568 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2569 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into ic_tran_pnd');
2570 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2571 err_num := SQLCODE;
2572 err_msg := SUBSTRB (SQLERRM, 1, 100);
2573 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2574 /** MC BUG# 1360002**/
2575 /** removed the comment from the below 2 lines **/
2576 retcode :=1;
2577 raise_application_error(-20001, err_msg);
2578 END; /* insert into ic_tran_pnd;*/
2579
2580
2581 /* update the ic_summ_inv table*/
2582
2583 IF ic_summ_inv_view_exists = 0 THEN /* Bug 3019986 Mohit Kapoor */
2584 /*Bug 1365777 - If item is a non-inventory item then do not update or insert
2585 into ic_summ_inv table - PPB */
2586
2587 If v_noninv_ind = 0
2588 then
2589 BEGIN
2590
2591
2592 if int_rec.qc_grade_wanted is NULL
2593 then
2594 UPDATE ic_summ_inv
2595 SET onpurch_qty = onpurch_qty + nvl(v_order1,0),
2596 onpurch_qty2 = onpurch_qty2 + nvl(v_order2,0)
2597 WHERE item_id=v_item_id
2598 AND whse_code=int_rec.to_whse;
2599 else
2600 UPDATE ic_summ_inv
2601 SET onpurch_qty = onpurch_qty + nvl(v_order1,0),
2602 onpurch_qty2 = onpurch_qty2 + nvl(v_order2,0)
2603 WHERE item_id=v_item_id
2604 AND whse_code=int_rec.to_whse
2605 AND qc_grade = int_rec.qc_grade_wanted;
2606 end if;/*int_rec.qc_grade_wanted is NULL*/
2607
2608
2609 /*Preetam Bamb Bug# 1288128 03/May/00
2610 If there is no entry for the item/warehouse/qc grade combn
2611 in ic_summ_inv table then insert a row in it.*/
2612 IF (SQL%ROWCOUNT = 0) THEN
2613 /* Get the next sequence number */
2614 select gem5_summ_inv_id_s.nextval into l_iret from dual;
2615
2616 /* Since a row for this item does not already exists enter a row */
2617 INSERT INTO ic_summ_inv
2618 (summ_inv_id, item_id, whse_code, qc_grade,
2619 onhand_qty, onhand_qty2, onhand_prod_qty,
2620 onhand_prod_qty2, onhand_order_qty, onhand_order_qty2,
2621 onhand_ship_qty, onhand_ship_qty2, onpurch_qty,
2622 onpurch_qty2, onprod_qty, onprod_qty2,
2623 committedsales_qty, committedsales_qty2,
2624 committedprod_qty,
2625 committedprod_qty2, intransit_qty, intransit_qty2,
2626 last_updated_by, created_by, last_update_date,
2627 creation_date)
2628 VALUES (l_iret, v_item_id, int_rec.to_whse,
2629 int_rec.qc_grade_wanted,
2630 0, 0, 0, 0, 0, 0, 0, 0,
2631 nvl(v_order1,0), nvl(v_order2,0),
2632 0, 0, 0, 0, 0, 0, 0, 0,
2633 0, 0, SYSDATE, SYSDATE);
2634
2635
2636 END IF; /* SQL%ROWCOUNT = 0 */
2637
2638
2639
2640 EXCEPTION
2641 WHEN OTHERS THEN
2642 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2643 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating ic_summ_inv');
2644 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2645 err_num := SQLCODE;
2646 err_msg := SUBSTRB (SQLERRM, 1, 100);
2647 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2648 retcode :=1;
2649 raise_application_error(-20001, err_msg);
2650 END; /* update ic_summ_inv*/
2651
2652 end if; /* if v_noninv_ind = 0 */
2653 END IF; /* Bug 3019986 ic_summ_inv_view_exists */
2654
2655 /* Synchronize Acquisition Costs from Oracle */
2656
2657 GML_PO_SYNCH.cpg_aqcst_mv
2658 (int_rec.po_header_id,
2659 int_rec.po_line_id,
2660 int_rec.po_line_location_id,
2661 v_po_id,
2662 v_new_line_id,
2663 v_doc_type,
2664 v_aqcst_status);
2665
2666 IF v_aqcst_status=FALSE THEN
2667 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2668 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error with cpg_aqcst_mv procedure');
2669 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2670 FND_FILE.PUT_LINE(FND_FILE.LOG,'po_header_id='|| int_rec.po_header_id
2671 );
2672 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2673 FND_FILE.PUT_LINE(FND_FILE.LOG,'po_line_id='|| int_rec.po_line_id);
2674 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2675 FND_FILE.PUT_LINE(FND_FILE.LOG,'lineloc_id='|| int_rec.po_line_location_id);
2676 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2677 FND_FILE.PUT_LINE(FND_FILE.LOG,'po_id=' || v_po_id);
2678 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2679 FND_FILE.PUT_LINE(FND_FILE.LOG,'new_line_id=' || v_new_line_id);
2680 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2681 FND_FILE.PUT_LINE(FND_FILE.LOG,'doc_type=' || v_doc_type);
2682 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2683 err_num := SQLCODE;
2684 err_msg := SUBSTRB (SQLERRM, 1, 100);
2685 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2686 retcode :=1;
2687 raise_application_error(-20001, err_msg);
2688 END IF;
2689
2690 /* add the GEMMS id's to the record in mapping table*/
2691
2692 /* After getting a new line, Open the status of the PO */
2693
2694 BEGIN
2695 /* BEGIN BUG#2041468 V. Ajay Kumar */
2696 /* Modified the following update statement by adding 'cancellation_code = NULL' */
2697 UPDATE po_ordr_hdr
2698 SET po_status = 0, cancellation_code = NULL
2699 WHERE po_id = v_po_id;
2700 /* END BUG#2041468 */
2701 EXCEPTION
2702 WHEN OTHERS THEN
2703 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2704 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating status in po_ordr_hdr');
2705 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2706 err_num := SQLCODE;
2707 err_msg := SUBSTRB (SQLERRM, 1, 100);
2708 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2709 retcode :=1;
2710 raise_application_error(-20001, err_msg);
2711 END;
2712
2713 BEGIN
2714 UPDATE cpg_oragems_mapping
2715 SET po_id = v_po_id,
2716 line_id = v_new_line_id
2717 WHERE po_header_id = int_rec.po_header_id
2718 AND po_line_id = int_rec.po_line_id
2719 AND po_line_location_id = int_rec.po_line_location_id;
2720 EXCEPTION
2721 WHEN OTHERS THEN
2722 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2723 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating cpg_oragems_mapping
2724 ');
2725 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2726 err_num := SQLCODE;
2727 err_msg := SUBSTRB (SQLERRM, 1, 100);
2728 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2729 retcode :=1;
2730 raise_application_error(-20001, err_msg);
2731 END;
2732
2733 /* HW BUG#:1178415 - Pass int_rec.mul_div_sign and int_rec.purchase_exchange_rate */
2734 /* Call GL Mapping and insert into po_dist_dtl*/
2735
2736 GML_PO_GLDIST.P_row_num_upd := 0 ;
2737
2738 GML_PO_GLDIST.receive_data ('PORD', v_po_id,
2739 v_new_line_id, int_rec.orgn_code,
2740 int_rec.po_date, v_ship_vendor_id,
2741 v_base_currency, int_rec.billing_currency,
2742 int_rec.to_whse, v_line_no,
2743 int_rec.item_no, int_rec.extended_price,
2744 int_rec.project_no, int_rec.order_qty1,
2745 int_rec.order_um1, v_item_id,
2746 int_rec.mul_div_sign,int_rec.purchase_exchange_rate,
2747 int_rec.net_price,1, FALSE,
2748 v_map_retcode,
2749 /*Bug# 1324319 Added code to pass the AAP and PPV accts generated at the OPM side
2750 over to the APPS side.Just added one paramerter passed to receive_data procedure.
2751 P_transaction_type */
2752 int_rec.transaction_type );
2753
2754 IF v_map_retcode = 0 THEN
2755 v_acct_map_ind := 1;
2756 ELSIF v_map_retcode = 1 THEN
2757 FND_MESSAGE.SET_NAME ('GML', 'PO_ACCT_NOT_DEFINED');
2758 v_err_message := FND_MESSAGE.GET;
2759 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2760 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
2761 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2762 retcode :=1;
2763 ELSIF v_map_retcode = 2 THEN
2764 FND_MESSAGE.SET_NAME ('GMF', 'GL_NO_ACCTG_MAP2');
2765 FND_MESSAGE.SET_TOKEN ('S1', int_rec.orgn_code );
2766 FND_MESSAGE.SET_TOKEN ('S2', int_rec.orgn_code );
2767 FND_MESSAGE.SET_TOKEN ('S3', int_rec.to_whse );
2768 v_err_message := FND_MESSAGE.GET;
2769 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2770 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
2771 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2772 retcode :=1;
2773 ELSIF v_map_retcode = 3 THEN
2774 FND_MESSAGE.SET_NAME ('GMF', 'GL_INVALID_FISCAL_YEAR_PERIOD');
2775 v_err_message := FND_MESSAGE.GET;
2776 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2777 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
2778 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2779 retcode :=1;
2780 ELSIF v_map_retcode = 4 THEN
2781 FND_MESSAGE.SET_NAME ('GMF', 'GL_NO_LEDGER_MAP2');
2782 FND_MESSAGE.SET_TOKEN ('S1', int_rec.orgn_code );
2783 FND_MESSAGE.SET_TOKEN ('S2', int_rec.orgn_code );
2784 v_err_message := FND_MESSAGE.GET;
2785 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2786 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
2787 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2788 retcode :=1;
2789 END IF;
2790
2791 IF v_map_retcode > 0 THEN
2792 UPDATE cpg_purchasing_interface
2793 SET invalid_ind = 'Y'
2794 WHERE rowid = int_rec.rowid;
2795 EXIT;
2796 END IF;
2797 END IF; /* B2594000 */
2798 END; /*new line*/
2799
2800 /* cancelling or closing a line */
2801
2802 ELSIF (int_rec.po_status IN ('CLOSED', 'CLOSED FOR RECEIVING',
2803 /* 'CLOSED FOR INVOICE', B1820461 */
2804 'FINALLY CLOSED')
2805 /* B2113809 following or condition added */
2806 OR (int_rec.cancellation_code IS NOT NULL AND int_rec.po_status = 'CLOSED FOR INVOICE')) THEN
2807 BEGIN
2808 OPEN line_id_cur(int_rec.po_header_id, int_rec.po_line_id,
2809 int_rec.po_line_location_id);
2810 FETCH line_id_cur
2811 INTO v_line_id;
2812 CLOSE line_id_cur;
2813
2814
2815 OPEN old_po_line_status_cur(v_po_id, v_line_id);
2816 FETCH old_po_line_status_cur INTO v_old_po_status,v_old_cancellation_code;
2817 CLOSE old_po_line_status_cur;
2818
2819
2820 /* for cancelling, cancellation_code = 'ORAF'*/
2821 /* for closing, cancellation_code = ' '*/
2822 /* T. Ricci 5/12/98 changed who columns for GEMMS 5.0*/
2823 /* T. Ricci 11/10/98 removed trans_cnt = trans_cnt + 1 from update*/
2824
2825 UPDATE po_ordr_dtl
2826 SET po_status = 20,
2827 cancellation_code = int_rec.cancellation_code,
2828 last_update_date = nvl(int_rec.last_update_date, SYSDATE),
2829 last_updated_by = nvl(int_rec.last_updated_by, 0),
2830 last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
2831 WHERE po_id = v_po_id
2832 AND line_id = v_line_id;
2833
2834
2835 Update_header_status(v_po_id,int_rec.cancellation_code);
2836
2837
2838 /* Bug# 1357575 - The delete_mark is set to one only of the po is cancelled added the if condition */
2839 /* Uday Phadtare B1795095 commented the if condition */
2840 --IF int_rec.cancellation_code IS NOT NULL THEN
2841 UPDATE ic_tran_pnd
2842 SET delete_mark = 1 /* only column changed by GEMMS */
2843 WHERE doc_type = v_doc_type
2844 AND doc_id = v_po_id
2845 AND line_id = v_line_id;
2846 --END IF;
2847
2848 /* Begin B2043468 */
2849 IF (int_rec.po_status IN ('CLOSED','CLOSED FOR RECEIVING') AND nvl(v_old_po_status,0) = 20) THEN
2850 OPEN opm_po_line_dtl(v_po_id, v_line_id);
2851 FETCH opm_po_line_dtl INTO rec_opm_po_line_dtl;
2852 CLOSE opm_po_line_dtl;
2853
2854 OPEN item_dtl(int_rec.item_no);
2855 FETCH item_dtl INTO rec_item_dtl.item_id, rec_item_dtl.item_um2;
2856 CLOSE item_dtl;
2857
2858 IF int_rec.order_qty1 <> rec_opm_po_line_dtl.order_qty1 THEN
2859 IF v_dualum_ind > 0 THEN
2860 v_order2 := GMICUOM.uom_conversion
2861 (rec_item_dtl.item_id,0,
2862 int_rec.order_qty1,
2863 int_rec.order_um1,
2864 rec_item_dtl.item_um2,0);
2865 ELSE
2866 v_order2 := 0;
2867 END IF;
2868 UPDATE po_ordr_dtl
2869 SET order_qty1 = int_rec.order_qty1, order_qty2 = v_order2
2870 WHERE po_id = v_po_id
2871 AND line_id = v_line_id;
2872 END IF;
2873
2874 END IF;
2875
2876 /* End B2043468 */
2877
2878
2879 /* PPB */
2880
2881 IF nvl(v_old_po_status,0) <> 20
2882 THEN
2883
2884 v_total_received_qty := Get_total_Received_qty(v_po_id,v_line_id,v_item_id,v_item_um1);
2885
2886 IF v_item_um2 IS NOT NULL
2887 THEN
2888 v_total_received_qty2 := GMICUOM.uom_conversion
2889 (v_item_id,0,
2890 v_total_received_qty,
2891 v_item_um1,
2892 v_item_um2,0);
2893 ELSE
2894 v_total_received_qty2 := 0;
2895 END IF;
2896
2897 IF ic_summ_inv_view_exists = 0 THEN /* Bug 3019986 Mohit Kapoor */
2898 /*Bug 1365777 - If item is a non-inventory item then do not update or insert
2899 into ic_summ_inv table - PPB */
2900
2901 IF v_noninv_ind = 0
2902 THEN
2903 /* RVK */
2904 IF (int_rec.qc_grade_wanted is not null) THEN
2905 UPDATE ic_summ_inv
2906 SET onpurch_qty = onpurch_qty - (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
2907 onpurch_qty2 = onpurch_qty2- (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
2908 WHERE item_id = v_item_id
2909 AND whse_code = int_rec.to_whse
2910 AND qc_grade = int_rec.qc_grade_wanted;
2911
2912
2913 ELSE
2914 UPDATE ic_summ_inv
2915 SET onpurch_qty = onpurch_qty - (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
2916 onpurch_qty2 = onpurch_qty2- (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
2917 WHERE item_id = v_item_id
2918 AND whse_code = int_rec.to_whse
2919 AND qc_grade is null;
2920
2921
2922 END IF; /*(int_rec.qc_grade_wanted is not null) */
2923 END IF; /* If noninv_ind = 0 */
2924 END IF; /* Bug 3019986 ic_summ_inv_view_exists */
2925 /* BEGIN - Bug 1854280 Pushkar Upakare */
2926 IF int_rec.transaction_type = 'PLANNED' THEN
2927 IF int_rec.cancellation_code IS NOT NULL THEN /* Cancel*/
2928
2929 BEGIN
2930 UPDATE po_bpos_hdr
2931 SET amount_purchased = amount_purchased -
2932 nvl(int_rec.extended_price,0),
2933 activity_ind = 1,
2934 rel_count = rel_count + 1,
2935 last_update_date = nvl(int_rec.last_update_date, SYSDATE),
2936 last_updated_by = nvl(int_rec.last_updated_by, 0),
2937 last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
2938 WHERE bpo_id = v_bpo_id;
2939 EXCEPTION
2940 WHEN OTHERS THEN
2941 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2942 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_bpos_hdr');
2943 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2944 err_num := SQLCODE;
2945 err_msg := SUBSTRB (SQLERRM, 1, 100);
2946 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2947 retcode :=1;
2948 raise_application_error(-20001, err_msg);
2949 END;
2950
2951 BEGIN
2952 UPDATE po_bpos_dtl
2953 SET amount_purchased = amount_purchased -
2954 nvl(int_rec.extended_price,0),
2955 qty_purchased = qty_purchased -
2956 nvl(int_rec.order_qty1,0),
2957 last_update_date = nvl(int_rec.last_update_date, SYSDATE),
2958 last_updated_by = nvl(int_rec.last_updated_by, 0),
2959 last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
2960 WHERE bpo_id = v_bpo_id
2961 AND line_id = v_bpo_line_id; /* Bug 1854280 Pushkar Upakare */
2962 EXCEPTION
2963 WHEN OTHERS THEN
2964 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2965 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_bpos_dtl');
2966 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2967 err_num := SQLCODE;
2968 err_msg := SUBSTRB (SQLERRM, 1, 100);
2969 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2970 retcode :=1;
2971 raise_application_error(-20001, err_msg);
2972 END;
2973 END IF; /* CANCELLATION_CODE not null */
2974 END IF; /* PLANNED */
2975 /* END - Bug 1854280 */
2976
2977 END IF; /*nvl(v_old_po_status,0) <> 20*/
2978
2979
2980 GML_PO_SYNCH.cpg_aqcst_mv
2981 (int_rec.po_header_id,
2982 int_rec.po_line_id,
2983 int_rec.po_line_location_id,
2984 v_po_id,
2985 v_line_id,
2986 v_doc_type,
2987 v_aqcst_status);
2988
2989 IF v_aqcst_status=FALSE THEN
2990 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2991 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error with cpg_aqcst_mv procedure');
2992 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
2993 err_num := SQLCODE;
2994 err_msg := SUBSTRB (SQLERRM, 1, 100);
2995 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
2996 retcode :=1;
2997 raise_application_error(-20001, err_msg);
2998 END IF;
2999
3000
3001 /* Begin Bug 2817410 */
3002 GML_PO_GLDIST.P_row_num_upd := 0 ;
3003
3004 GML_PO_GLDIST.receive_data ('PORD', v_po_id,
3005 v_line_id, int_rec.orgn_code,
3006 int_rec.po_date, v_ship_vendor_id,
3007 v_base_currency, int_rec.billing_currency,
3008 int_rec.to_whse, v_dtl_line_no,
3009 int_rec.item_no, int_rec.extended_price,
3010 int_rec.project_no, int_rec.order_qty1,
3011 int_rec.order_um1, v_item_id,
3012 int_rec.mul_div_sign,int_rec.purchase_exchange_rate,
3013 int_rec.net_price,4, FALSE,
3014 v_map_retcode,
3015 int_rec.transaction_type );
3016
3017 IF v_map_retcode = 0 THEN
3018 v_acct_map_ind := 1;
3019 END IF;
3020
3021 /* End Bug 2817410 */
3022
3023 END; /* cancelling or closing a line */
3024
3025 ELSE /* updates including cancel/close */
3026 BEGIN
3027
3028 /* pure updates, not cancel/close*/
3029 OPEN line_id_cur(int_rec.po_header_id, int_rec.po_line_id,
3030 int_rec.po_line_location_id);
3031 FETCH line_id_cur
3032 INTO v_line_id;
3033 CLOSE line_id_cur;
3034
3035
3036 OPEN old_po_line_status_cur(v_po_id, v_line_id);
3037 FETCH old_po_line_status_cur INTO v_old_po_status,v_old_cancellation_code;
3038 CLOSE old_po_line_status_cur;
3039
3040 /* BEGIN BUG#1731582 P. Arvind Dath */
3041
3042 OPEN attr_hdr_cur(int_rec.PO_HEADER_ID);
3043 FETCH attr_hdr_cur INTO attr_hdr_rec;
3044 CLOSE attr_hdr_cur;
3045
3046 /* T. Ricci 5/12/98 changed who columns for GEMMS 5.0*/
3047
3048 BEGIN
3049 UPDATE po_ordr_hdr
3050 SET payvend_id = nvl(v_pay_vendor_id,0),
3051 shipvend_id = nvl(v_ship_vendor_id,0),
3052 /* HW BUG#:1095846 */
3053 po_status = nvl(v_po_status,0),
3054 shipper_code = int_rec.shipper_code,
3055 to_whse = int_rec.to_whse,
3056 /* LE 26/AUG/98 NULL OK*/
3057 /* LE 26/AUG/98 NULL OK*/
3058 frtbill_mthd = v_frtbill_mthd,
3059 billing_currency = int_rec.billing_currency,
3060 /* LE 26/AUG/98 NULL OK*/
3061 purchase_exchange_rate =
3062 nvl(int_rec.purchase_exchange_rate, 1), /* Bug 1427876 */
3063 terms_code = v_terms_code,
3064 fob_code = int_rec.fob_code,
3065 buyer_code = v_buyer_code, /* Lswamy - BUG 1829102 */
3066 /* Bug# 1357575 PB*/
3067 /* po_date = nvl(int_rec.po_date, SYSDATE), */
3068 date_printed = nvl(int_rec.date_printed, SYSDATE),
3069 /* LE 26/AUG/98 NULL OK*/
3070 revision_count = int_rec.revision_count,
3071 /* LE 26/AUG/98 NULL OK*/
3072 print_count = int_rec.print_count,
3073 last_update_date = nvl(v_last_update_date, SYSDATE), /* Lswamy - BUG 1829102 */
3074 last_updated_by = nvl(v_last_updated_by, 0), /* Lswamy - BUG 1829102 */
3075 /* LE 26/AUG/98 NULL OK*/
3076 last_update_login = int_rec.last_update_login,
3077 attribute1 = attr_hdr_rec.attribute1,
3078 attribute2 = attr_hdr_rec.attribute2,
3079 attribute3 = attr_hdr_rec.attribute3,
3080 attribute4 = attr_hdr_rec.attribute4,
3081 attribute5 = attr_hdr_rec.attribute5,
3082 attribute6 = attr_hdr_rec.attribute6,
3083 attribute7 = attr_hdr_rec.attribute7,
3084 attribute8 = attr_hdr_rec.attribute8,
3085 attribute9 = attr_hdr_rec.attribute9,
3086 attribute10 = attr_hdr_rec.attribute10,
3087 attribute11 = attr_hdr_rec.attribute11,
3088 attribute12 = attr_hdr_rec.attribute12,
3089 attribute13 = attr_hdr_rec.attribute13,
3090 attribute14 = attr_hdr_rec.attribute14
3091 WHERE po_id = v_po_id;
3092 EXCEPTION
3093 WHEN OTHERS THEN
3094 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3095 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_ordr_hdr');
3096 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3097 err_num := SQLCODE;
3098 err_msg := SUBSTRB (SQLERRM, 1, 100);
3099 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3100 retcode :=1;
3101 raise_application_error(-20001, err_msg);
3102 END;
3103 OPEN order_qty_price_cur(v_po_id, v_line_id);
3104 FETCH order_qty_price_cur INTO v_old_order_qty1,
3105 v_old_order_qty2,
3106 v_old_order_um1,
3107 v_old_order_um2,
3108 v_old_extended_price,
3109 v_dtl_line_no;
3110 CLOSE order_qty_price_cur;
3111
3112
3113 /* PPB */
3114 IF (v_old_order_um1 <> v_item_um1) THEN
3115 v_old_order_base_qty := GMICUOM.uom_conversion
3116 (v_item_id,0,
3117 v_old_order_qty1,
3118 v_old_order_um1,
3119 v_item_um1,0);
3120 ELSE
3121 v_old_order_base_qty := v_old_order_qty1;
3122 END IF;
3123
3124 IF v_dualum_ind > 0 THEN
3125 v_order2 := GMICUOM.uom_conversion
3126 (v_item_id,0,
3127 int_rec.order_qty1,
3128 int_rec.order_um1,
3129 v_item_um2,0);
3130
3131 v_old_order_sec_qty := GMICUOM.uom_conversion
3132 (v_item_id,0,
3133 v_old_order_base_qty,
3134 v_item_um1,
3135 v_item_um2,0);
3136 ELSE
3137
3138 v_order2 := 0;
3139 v_old_order_sec_qty := 0;
3140
3141 END IF;
3142
3143 /* BEGIN BUG#1731582 P. Arvind Dath */
3144
3145 OPEN attr_dtl_cur(int_rec.PO_LINE_LOCATION_ID,
3146 int_rec.PO_HEADER_ID,
3147 int_rec.PO_LINE_ID);
3148
3149 FETCH attr_dtl_cur INTO attr_dtl_rec;
3150 CLOSE attr_dtl_cur;
3151
3152
3153 /* T. Ricci 5/12/98 changed who columns for GEMMS 5.0*/
3154 /* T. Ricci 11/10/98 removed trans_cnt = trans_cnt + 1 from update*/
3155 /* T. Ricci 12/01/98 removed line_no from update*/
3156
3157 BEGIN
3158 UPDATE po_ordr_dtl
3159 SET item_id = v_item_id,
3160 item_desc = nvl(v_item_desc, ' '),
3161 order_qty1 = nvl(int_rec.order_qty1,0),
3162 /* LE 26/AUG/98 NULL OK*/
3163 order_qty2 = v_order2,
3164 order_um1 = int_rec.order_um1,
3165 /* LE 26/AUG/98 NULL OK*/
3166 order_um2 = v_item_um2,
3167 net_price = nvl(int_rec.net_price,0),
3168 extended_price = nvl(int_rec.extended_price,0),
3169 price_um = nvl(int_rec.price_um,' '),
3170 /* LE 26/AUG/98 NULL OK*/
3171 shipper_code = int_rec.shipper_code,
3172 shipvend_id = v_ship_vendor_id,
3173 to_whse = nvl(int_rec.to_whse,' '),
3174 /* LE 26/AUG/98 NULL OK*/
3175 qc_grade_wanted = int_rec.qc_grade_wanted,
3176 /* LE 26/AUG/98 NULL OK*/
3177 frtbill_mthd = v_frtbill_mthd,
3178 /* LE 26/AUG/98 NULL OK*/
3179 terms_code = v_terms_code,
3180 /* LE 26/AUG/98 NULL OK*/
3181 fob_code = int_rec.fob_code,
3182 /* LE 26/AUG/98 NULL OK*/
3183 buyer_code = v_buyer_code, /* Lswamy - BUG 1829102 */
3184 agreed_dlvdate = nvl(int_rec.agreed_dlvdate, sysdate),
3185 requested_dlvdate = nvl(int_rec.requested_dlvdate, sysdate),
3186 required_dlvdate = nvl(int_rec.required_dlvdate, sysdate),
3187 sched_shipdate = nvl(int_rec.sched_shipdate, sysdate),
3188 last_update_date = nvl(v_last_update_date, SYSDATE), /* Lswamy - BUG 1829102 */
3189 last_updated_by = nvl(v_last_updated_by, 0), /* Lswamy - BUG 1829102 */
3190 /* LE 26/AUG/98 NULL OK*/
3191 last_update_login = int_rec.last_update_login,
3192 po_status = nvl(v_po_status, 0),
3193 /* T.Ricci 7/8/98 integrity constraint change NULL's allowed*/
3194 cancellation_code = int_rec.cancellation_code,
3195 attribute1 = attr_dtl_rec.attribute1,
3196 attribute2 = attr_dtl_rec.attribute2,
3197 attribute3 = attr_dtl_rec.attribute3,
3198 attribute4 = attr_dtl_rec.attribute4,
3199 attribute5 = attr_dtl_rec.attribute5,
3200 attribute6 = attr_dtl_rec.attribute6,
3201 attribute7 = attr_dtl_rec.attribute7,
3202 attribute8 = attr_dtl_rec.attribute8,
3203 attribute9 = attr_dtl_rec.attribute9,
3204 attribute10 = attr_dtl_rec.attribute10,
3205 attribute11 = attr_dtl_rec.attribute11,
3206 attribute12 = attr_dtl_rec.attribute12,
3207 attribute13 = attr_dtl_rec.attribute13,
3208 attribute14 = attr_dtl_rec.attribute14,
3209 attribute15 = attr_dtl_rec.attribute15
3210 WHERE po_id = v_po_id
3211 AND line_id = v_line_id;
3212 EXCEPTION
3213 WHEN OTHERS THEN
3214 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3215 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_ordr_dtl');
3216 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3217 err_num := SQLCODE;
3218 err_msg := SUBSTRB (SQLERRM, 1, 100);
3219 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3220 retcode :=1;
3221 raise_application_error(-20001, err_msg);
3222 END;
3223 /* regular updates, not cancel/close*/
3224 IF (int_rec.po_status NOT IN ('CLOSED', 'CLOSED FOR RECEIVING',
3225 /* 'CLOSED FOR INVOICE', B1820461 */
3226 'FINALLY CLOSED')) THEN
3227 BEGIN
3228
3229
3230
3231 /* Added the code below to get the total received qty since only the delta should
3232 be added to the ic_tran_pnd table - Preetam Bamb */
3233
3234 v_total_received_qty := Get_total_Received_qty(v_po_id,v_line_id,v_item_id,v_item_um1);
3235
3236 IF v_item_um2 IS NOT NULL
3237 THEN
3238 v_total_received_qty2 := GMICUOM.uom_conversion
3239 (v_item_id,0,
3240 v_total_received_qty,
3241 v_item_um1,
3242 v_item_um2,0);
3243 ELSE
3244 v_total_received_qty2 := 0;
3245 END IF;
3246
3247
3248 /* T. Ricci 5/12/98 changed who columns for GEMMS 5.0*/
3249 BEGIN
3250 UPDATE ic_tran_pnd
3251 SET item_id = v_item_id,
3252 /* co_code = nvl(v_co_code, '-1'),
3253 orgn_code = nvl(int_rec.orgn_code, '-1'), */
3254 whse_code = int_rec.to_whse,
3255 /* trans_qty = nvl(v_order1,0) - nvl(v_total_received_qty,0), /* PPB nvl(int_rec.order_qty1, 0), */
3256 /* trans_qty2 = nvl(v_order2,0) - nvl(v_total_received_qty2,0),/*PPB Substracted old received qty */
3257 /* LE 26/AUG/98 NULL OK*/
3258 /* B1878034 update trans_qty to zero if recv_qty is more than order_qty */
3259 trans_qty = DECODE(SIGN(nvl(v_order1,0) - nvl(v_total_received_qty,0)),
3260 -1,0,nvl(v_order1,0) - nvl(v_total_received_qty,0)),
3261 trans_qty2 = DECODE(SIGN(nvl(v_order2,0) - nvl(v_total_received_qty2,0)),
3262 -1,0,nvl(v_order2,0) - nvl(v_total_received_qty2,0)),
3263 qc_grade = int_rec.qc_grade_wanted, /* LE 26/AUG/98 NULL OK*/
3264 trans_um = int_rec.order_um1,
3265 trans_um2 = v_item_um2, /* LE 26/AUG/98 NULL OK*/
3266 /** MC BUG# 1491754 update trans_date **/
3267 trans_date = nvl(int_rec.agreed_dlvdate,SYSDATE),
3268 last_update_date = nvl(int_rec.last_update_date, SYSDATE),
3269 last_updated_by = nvl(int_rec.last_updated_by, 0),
3270 last_update_login = int_rec.last_update_login, /* LE 26/AUG/98 NULL OK*/
3271 delete_mark = 0 /* Uday Phadtare B1795095 */
3272 WHERE doc_type = v_doc_type
3273 AND doc_id = v_po_id
3274 AND line_id = v_line_id;
3275 EXCEPTION
3276 WHEN OTHERS THEN
3277 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3278 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating ic_tran_pnd');
3279 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3280 err_num := SQLCODE;
3281 err_msg := SUBSTRB (SQLERRM, 1, 100);
3282 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3283 /* retcode :=1;
3284 raise_application_error(-20001, err_msg);*/
3285 END; /*update ic_tran_pnd*/
3286
3287 /* For Standard/PPO release/Blanket release*/
3288
3289 IF ic_summ_inv_view_exists = 0 THEN /* Bug 3019986 Mohit Kapoor */
3290 /*Bug 1365777 - If item is a non-inventory item then do not update or insert
3291 into ic_summ_inv table - PPB */
3292
3293 If v_noninv_ind = 0
3294 then
3295 BEGIN
3296
3297
3298 IF v_old_po_status = 20
3299 THEN
3300
3301 /* PPB */
3302 IF (int_rec.qc_grade_wanted is not null) THEN
3303 UPDATE ic_summ_inv
3304 SET onpurch_qty = onpurch_qty + (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
3305 onpurch_qty2 = onpurch_qty2+ (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
3306 WHERE item_id = v_item_id
3307 AND whse_code = int_rec.to_whse
3308 AND qc_grade = int_rec.qc_grade_wanted;
3309
3310
3311 ELSE
3312 UPDATE ic_summ_inv
3313 SET onpurch_qty = onpurch_qty + (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
3314 onpurch_qty2 = onpurch_qty2+ (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
3315 WHERE item_id = v_item_id
3316 AND whse_code = int_rec.to_whse
3317 AND qc_grade is null;
3318
3319
3320 END IF; /*(int_rec.qc_grade_wanted is not null)*/
3321
3322 ELSE
3323 IF int_rec.qc_grade_wanted IS NULL
3324 THEN
3325 UPDATE ic_summ_inv
3326 SET onpurch_qty = onpurch_qty
3327 - nvl(v_old_order_base_qty,0) /*RVK nvl(v_old_order_qty1,0) */
3328 + v_order1 /* RVK int_rec.order_qty1 */,
3329 onpurch_qty2 = onpurch_qty2
3330 - nvl(v_old_order_sec_qty,0) /* RVK v_old_order_qty2 */
3331 + nvl(v_order2,0)
3332 WHERE item_id = v_item_id
3333 AND whse_code = int_rec.to_whse
3334 AND qc_grade IS NULL;
3335 ELSE
3336 UPDATE ic_summ_inv
3337 SET onpurch_qty = onpurch_qty
3338 - nvl(v_old_order_base_qty,0) /*RVK nvl(v_old_order_qty1,0) */
3339 + v_order1 /* RVK int_rec.order_qty1 */,
3340 onpurch_qty2 = onpurch_qty2
3341 - nvl(v_old_order_sec_qty,0) /* RVK v_old_order_qty2 */
3342 + nvl(v_order2,0)
3343 WHERE item_id = v_item_id
3344 AND whse_code = int_rec.to_whse
3345 AND qc_grade = int_rec.qc_grade_wanted;
3346 END IF; /*int_rec.qc_grade_wanted IS NULL*/
3347
3348 END IF; /*If v_po_old_status = 20 */
3349
3350
3351
3352 EXCEPTION
3353 WHEN OTHERS THEN
3354 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3355 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating ic_summ_inv');
3356 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3357 err_num := SQLCODE;
3358 err_msg := SUBSTRB (SQLERRM, 1, 100);
3359 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3360 retcode :=1;
3361 raise_application_error(-20001, err_msg);
3362 END; /*update ic_summ_inv*/
3363
3364 End If; /* If noninv_ind = 0 */
3365 END IF; /* Bug 3019986 ic_summ_inv_view_exists */
3366
3367 IF int_rec.transaction_type = 'PLANNED' THEN
3368
3369 BEGIN
3370 UPDATE po_bpos_hdr
3371 SET amount_purchased =
3372 amount_purchased - nvl(v_old_extended_price,0) +
3373 nvl(int_rec.extended_price,0),
3374 activity_ind = 1,
3375 rel_count = rel_count + 1,
3376 last_update_date = nvl(int_rec.last_update_date, SYSDATE),
3377 last_updated_by = nvl(int_rec.last_updated_by, 0),
3378 last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
3379 WHERE bpo_id = v_bpo_id;
3380 EXCEPTION
3381 WHEN OTHERS THEN
3382 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3383 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_bpos_hdr');
3384 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3385 err_num := SQLCODE;
3386 err_msg := SUBSTRB (SQLERRM, 1, 100);
3387 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3388 retcode :=1;
3389 raise_application_error(-20001, err_msg);
3390 END;
3391
3392 /* T. Ricci 11/10/98 removed trans_cnt = trans_cnt + 1 from update*/
3393
3394 BEGIN
3395 UPDATE po_bpos_dtl
3396 SET amount_purchased =
3397 amount_purchased - nvl(v_old_extended_price,0) +
3398 nvl(int_rec.extended_price,0),
3399 qty_purchased =
3400 qty_purchased - nvl(v_old_order_qty1,0) +
3401 nvl(int_rec.order_qty1,0),
3402 last_update_date = nvl(int_rec.last_update_date, SYSDATE),
3403 last_updated_by = nvl(int_rec.last_updated_by, 0),
3404 last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
3405 WHERE bpo_id = v_bpo_id
3406 AND line_id = v_bpo_line_id; /* Bug 1854280 Pushkar Upakare */
3407 EXCEPTION
3408 WHEN OTHERS THEN
3409 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3410 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_bpos_dtl');
3411 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3412 err_num := SQLCODE;
3413 err_msg := SUBSTRB (SQLERRM, 1, 100);
3414 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3415 retcode :=1;
3416 raise_application_error(-20001, err_msg);
3417 END;
3418
3419 BEGIN
3420 UPDATE po_ordr_hdr
3421 SET revision_count = revision_count + 1,
3422 last_update_date = nvl(int_rec.last_update_date, SYSDATE),
3423 last_updated_by = nvl(int_rec.last_updated_by, 0),
3424 last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
3425 WHERE po_id = v_po_id
3426 AND revision_count = 0;
3427 EXCEPTION
3428 WHEN OTHERS THEN
3429 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3430 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_ordr_hdr');
3431 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3432 err_num := SQLCODE;
3433 err_msg := SUBSTRB (SQLERRM, 1, 100);
3434 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3435 retcode :=1;
3436 raise_application_error(-20001, err_msg);
3437 END;
3438 END IF; /*int_rec.transaction_type = 'PLANNED' */
3439
3440 /* HW BUG#:1178415 - Pass int_rec.mul_div_sign and int_rec.purchase_exchange_rate */
3441
3442 /*PPB reset this varaible for every new line. */
3443 GML_PO_GLDIST.P_row_num_upd := 0 ;
3444
3445 GML_PO_GLDIST.receive_data ('PORD', v_po_id,
3446 v_line_id, int_rec.orgn_code,
3447 int_rec.po_date, v_ship_vendor_id,
3448 v_base_currency, int_rec.billing_currency,
3449 int_rec.to_whse, v_dtl_line_no,
3450 int_rec.item_no, int_rec.extended_price,
3451 int_rec.project_no, int_rec.order_qty1,
3452 int_rec.order_um1, v_item_id,
3453 int_rec.mul_div_sign,int_rec.purchase_exchange_rate,
3454 int_rec.net_price,4, FALSE,
3455 v_map_retcode,
3456 /*Bug# 1324319 Added code to pass the AAP and PPV accts generated at the OPM side
3457 over to the APPS side.Just added one paramerter passed to receive_data procedure.
3458 P_transaction_type */
3459 int_rec.transaction_type );
3460
3461 IF v_map_retcode = 0 THEN
3462 v_acct_map_ind := 1;
3463 END IF;
3464
3465 END; /*regular update*/
3466
3467 ELSE /* cancel/close*/
3468 BEGIN
3469
3470 OPEN old_po_line_status_cur(v_po_id, v_line_id);
3471 FETCH old_po_line_status_cur INTO v_old_po_status,v_old_cancellation_code;
3472 CLOSE old_po_line_status_cur;
3473
3474 BEGIN
3475 UPDATE ic_tran_pnd
3476 SET delete_mark = 1 /* only column changed by GEMMS */
3477 WHERE doc_type = v_doc_type
3478 AND doc_id = v_po_id
3479 AND line_id = v_line_id;
3480 EXCEPTION
3481 WHEN OTHERS THEN
3482 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3483 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into ic_tran_pnd')
3484 ;
3485 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3486 err_num := SQLCODE;
3487 err_msg := SUBSTRB (SQLERRM, 1, 100);
3488 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3489 /* retcode :=1;
3490 raise_application_error(-20001, err_msg);*/
3491 END; /*update ic_tran_pnd*/
3492
3493 BEGIN
3494 IF v_old_po_status <> 20
3495 THEN
3496
3497 v_total_received_qty := Get_total_Received_qty(v_po_id,v_line_id,v_item_id,v_item_um1);
3498
3499 IF v_item_um2 IS NOT NULL
3500 THEN
3501 v_total_received_qty2 := GMICUOM.uom_conversion
3502 (v_item_id,0,
3503 v_total_received_qty,
3504 v_item_um1,
3505 v_item_um2,0);
3506 ELSE
3507 v_total_received_qty2 := 0 ;
3508 END IF; /*IF v_item_um2 IS NOT NULL */
3509
3510 IF ic_summ_inv_view_exists = 0 THEN /* Bug 3019986 Mohit Kapoor */
3511 /*Bug 1365777 - If item is a non-inventory item then do not update or insert
3512 into ic_summ_inv table - PPB */
3513
3514 If v_noninv_ind = 0
3515 then
3516 IF int_rec.qc_grade_wanted IS NULL
3517 THEN
3518 UPDATE ic_summ_inv
3519 SET onpurch_qty = onpurch_qty - (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
3520 onpurch_qty2 = onpurch_qty2 - (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
3521 WHERE item_id = v_item_id
3522 AND whse_code = int_rec.to_whse
3523 AND qc_grade IS NULL;
3524 ELSE
3525 UPDATE ic_summ_inv
3526 SET onpurch_qty = onpurch_qty - (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
3527 onpurch_qty2 = onpurch_qty2 - (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
3528 WHERE item_id = v_item_id
3529 AND whse_code = int_rec.to_whse
3530 AND qc_grade = int_rec.qc_grade_wanted;
3531 END IF;/*If int_rec.qc_grade_wanted IS NULL*/
3532
3533 End If; /* If noninv_ind = 0 */
3534 END IF; /* Bug 3019986 ic_summ_inv_view_exists */
3535 END IF; /*IF v_old_po_status <> 20 */
3536
3537 EXCEPTION
3538 WHEN OTHERS THEN
3539 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3540 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating ic_summ_inv');
3541 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3542 err_num := SQLCODE;
3543 err_msg := SUBSTRB (SQLERRM, 1, 100);
3544 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3545 retcode :=1;
3546 raise_application_error(-20001, err_msg);
3547 END;/*update ic_summ_inv*/
3548
3549 END; /*cancel/close*/
3550
3551 END IF; /* int_rec.po_status NOT IN (...) */
3552
3553 GML_PO_SYNCH.cpg_aqcst_mv
3554 (int_rec.po_header_id,
3555 int_rec.po_line_id,
3556 int_rec.po_line_location_id,
3557 v_po_id,
3558 v_line_id,
3559 v_doc_type,
3560 v_aqcst_status);
3561
3562 IF v_aqcst_status=FALSE THEN
3563 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3564 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error with cpg_aqcst_mv procedure');
3565 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3566 err_num := SQLCODE;
3567 err_msg := SUBSTRB (SQLERRM, 1, 100);
3568 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3569 retcode :=1;
3570 raise_application_error(-20001, err_msg);
3571 END IF;
3572
3573 END; /* end update including cancel/close*/
3574
3575 END IF;
3576
3577 IF error_ind = 0 THEN /* B2594000 */
3578 BEGIN
3579 UPDATE po_ordr_dtl /* For standard, blanket and PPOs */
3580 SET acct_map_ind = v_acct_map_ind,
3581 last_update_date = nvl(v_last_update_date, SYSDATE),/* lswamy - BUG 1829102 */
3582 last_updated_by = nvl(v_last_updated_by, 0), /* lswamy - BUG 1829102 */
3583 last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
3584 WHERE po_id = v_po_id;
3585 EXCEPTION
3586 WHEN OTHERS THEN
3587 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3588 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_ordr_dtl');
3589 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3590 err_num := SQLCODE;
3591 err_msg := SUBSTRB (SQLERRM, 1, 100);
3592 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
3593 retcode :=1;
3594 raise_application_error(-20001, err_msg);
3595 END;
3596 /* set the record's flag as 'P', for processed*/
3597
3598 UPDATE cpg_purchasing_interface
3599 SET invalid_ind = 'P' /*row processed*/
3600 WHERE rowid = int_rec.rowid;
3601
3602 FND_MESSAGE.set_name('GML', 'PO_NUM_OK');
3603 FND_MESSAGE.set_token('v_po_no',v_po_no);
3604 v_err_message := FND_MESSAGE.GET;
3605 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3606 FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_message ); /*message */
3607 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3608 ELSE
3609 UPDATE cpg_purchasing_interface
3610 SET invalid_ind = 'E' /* This line can not be inserted in po_ordr_dtl */
3611 WHERE rowid = int_rec.rowid;
3612 END IF; /* B2594000 */
3613
3614 END IF; /* if gemms_validate is true*/
3615
3616 /* fetch the next record in interface table*/
3617
3618 COMMIT;
3619
3620 FETCH int_cur INTO int_rec;
3621
3622 END LOOP;
3623
3624 CLOSE int_cur;
3625
3626 EXCEPTION
3627
3628 WHEN OTHERS THEN
3629 err_num := SQLCODE;
3630 err_msg := SUBSTRB (SQLERRM, 1, 100);
3631 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
3632 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg ); /*message */
3633 retcode := 1;
3634 RAISE_APPLICATION_ERROR(-20001, err_msg);
3635
3636 END cpg_int2gms;
3637
3638
3639 END GML_PO_SYNCH;