[Home] [Help]
PACKAGE BODY: APPS.GML_SYNCH_BPOS
Source
1 PACKAGE BODY GML_SYNCH_BPOS AS
2 /* $Header: GMLPBPOB.pls 115.12 2002/12/04 19:05:25 gmangari ship $ */
3
4 /*----------------------------------------------------------------------------+
5 | |
6 | PROCEDURE NAME next_bpo_id |
7 | |
8 | DESCRIPTION |
9 | |
10 | Procedure to get the next available bpo_id (For Planned POs only) |
11 | |
12 | MODIFICATION HISTORY |
13 | |
14 | 26-OCT-97 R Chellam Created |
15 | 20-NOV-97 R Chellam Modified to deal only with PPOs |
16 | 14-MAY-98 T Ricci replaced sy_surg_ctl with nextval from |
17 | sys.dual for GEMMS 5.0 |
18 +----------------------------------------------------------------------------*/
19
20 PROCEDURE next_bpo_id ( new_bpo_id OUT NOCOPY PO_BPOS_HDR.BPO_ID%TYPE,
21 p_orgn_code IN SY_ORGN_MST.ORGN_CODE%TYPE,
22 v_next_id_status OUT NOCOPY BOOLEAN)
23 IS
24
25 /* CURSOR NBPO_ID_CUR IS
26 SELECT last_value + 1
27 FROM sy_surg_ctl
28 WHERE key_name = 'bpo_id'
29 FOR UPDATE; */
30
31 CURSOR NBPO_ID_CUR IS
32 SELECT GEM5_BPO_ID_s.nextval
33 FROM sys.dual;
34
35 err_msg VARCHAR2(100);
36
37 BEGIN
38
39 OPEN nbpo_id_cur;
40
41 FETCH nbpo_id_cur into new_bpo_id;
42
43 /* UPDATE sy_surg_ctl
44 SET last_value = new_bpo_id
45 WHERE current of nbpo_id_cur; */
46
47 UPDATE sy_docs_seq
48 SET last_assigned = last_assigned + 1
49 WHERE doc_type = 'PBPO'
50 AND orgn_code = p_orgn_code;
51
52 CLOSE nbpo_id_cur;
53 v_next_id_status :=TRUE;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 v_next_id_status :=FALSE;
58 err_msg := SUBSTRB(SQLERRM, 1, 100);
59 RAISE_APPLICATION_ERROR(-20000, err_msg);
60 END next_bpo_id;
61
62
63 /* Function to get the next available GEMMS line_id for Planned
64 POs is same as for Standard PO's. Hence no different procedure */
65
66 /*-----------------------------------------------------------------------------|
67 | |
68 | FUNCTION NAME bpo_exist |
69 | |
70 | DESCRIPTION |
71 | |
72 | Procedure to check if the Planned PO already exists in bpos tables |
73 | |
74 | MODIFICATION HISTORY |
75 | |
76 | 26-OCT-97 R Chellam Created |
77 | 20-NOV-97 R Chellam Modified to deal only with PPOs |
78 +-----------------------------------------------------------------------------*/
79
80
81 FUNCTION bpo_exist
82 ( v_bpo_no IN PO_BPOS_HDR.BPO_NO%TYPE)
83 RETURN BOOLEAN
84 IS
85
86 v_row_count NUMBER :=0;
87 err_msg VARCHAR2(100);
88
89 CURSOR row_count1_cur IS
90 SELECT COUNT(*)
91 FROM po_bpos_hdr
92 WHERE bpo_no = v_bpo_no;
93
94 BEGIN
95 OPEN row_count1_cur;
96 FETCH row_count1_cur
97 INTO v_row_count;
98 CLOSE row_count1_cur;
99
100 IF v_row_count > 0 THEN
101 RETURN TRUE;
102 ELSE
103 RETURN FALSE;
104 END IF;
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 err_msg := SUBSTRB(SQLERRM, 1, 100);
109 RAISE_APPLICATION_ERROR(-20000, err_msg);
110
111 END bpo_exist;
112
113
114 /*-----------------------------------------------------------------------------|
115 | |
116 | FUNCTION NAME bpo_line_exist |
117 | |
118 | DESCRIPTION |
119 | |
120 | Function to check if bpo_line already exists and is to be updated or is new|
121 | |
122 | MODIFICATION HISTORY |
123 | |
124 | 26-OCT-97 R Chellam Created |
125 | 20-NOV-97 R Chellam Modified to deal only with PPOs |
126 +-----------------------------------------------------------------------------*/
127
128
129 FUNCTION bpo_line_exist
130
131 ( v_po_header_id IN CPG_PURCHASING_INTERFACE.PO_HEADER_ID%TYPE,
132 v_po_line_id IN CPG_PURCHASING_INTERFACE.PO_LINE_ID%TYPE,
133 v_po_line_location_id IN CPG_PURCHASING_INTERFACE.PO_LINE_LOCATION_ID%TYPE)
134
135 RETURN BOOLEAN
136 IS
137
138 CURSOR id_cur (header NUMBER, line NUMBER, location NUMBER) IS
139 SELECT bpo_id, bpo_line_id
140 FROM cpg_oragems_mapping
141 WHERE po_header_id = v_po_header_id
142 AND po_line_id = v_po_line_id
143 AND po_line_location_id = v_po_line_location_id;
144
145 id_rec id_cur%ROWTYPE;
146 err_msg VARCHAR2(100);
147
148 BEGIN
149
150 OPEN id_cur (v_po_header_id, v_po_line_id, v_po_line_location_id);
151 FETCH id_cur INTO id_rec;
152 CLOSE id_cur;
153
154 IF (id_rec.bpo_id IS NULL AND id_rec.bpo_line_id IS NULL) THEN
155 RETURN FALSE;
156 ELSIF (id_rec.bpo_id IS NOT NULL AND id_rec.bpo_line_id IS NOT NULL) THEN
157 RETURN TRUE;
158 END IF;
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 err_msg := SUBSTRB(SQLERRM, 1, 100);
163 RAISE_APPLICATION_ERROR(-20000, err_msg);
164
165 END bpo_line_exist;
166
167
168 /*-----------------------------------------------------------------------------|
169 | |
170 | FUNCTION NAME get_bpo_line_no |
171 | |
172 | DESCRIPTION |
173 | |
174 | Function to get the maximum line number so far for a particular PPO |
175 | |
176 | MODIFICATION HISTORY |
177 | |
178 | 26-OCT-97 R Chellam Created |
179 | 20-NOV-97 R Chellam Modified to deal only with PPOs |
180 | 25-APR-00 N Chekuri Changed to return line_num from po_lines_all instead|
181 | of the max(line_no) from po_bpos_dtl. Bug#1246767. |
182 | Changed parameter accordingly.
183 | 15-MAY-00 N Chekuri Added logic to take care of more than one shipment |
184 | line per PO line. Added parameters.
185 |-----------------------------------------------------------------------------*/
186
187 FUNCTION get_bpo_line_no( v_bpo_id IN NUMBER,v_po_header_id IN NUMBER,
188 v_po_line_id IN NUMBER)
189 RETURN NUMBER
190 IS
191
192 v_bpo_line_no NUMBER;
193 v_line_count NUMBER;
194 v_shipment_count NUMBER;
195 err_msg VARCHAR2(100);
196
197 BEGIN
198
199 /* Retain the line number from apps as long as there is only
200 one shipment per line.We'll generate a number if there is
201 more than one shipment per line */
202
203 SELECT count(*)
204 INTO v_line_count
205 FROM po_lines_all
206 WHERE po_header_id = v_po_header_id;
207
208 SELECT count(*)
209 INTO v_shipment_count
210 FROM po_line_locations_all
211 WHERE po_header_id = v_po_header_id;
212
213 IF v_line_count = v_shipment_count THEN
214 SELECT line_num
215 INTO v_bpo_line_no
216 FROM po_lines_all
217 WHERE po_line_id = v_po_line_id;
218 ELSE
219 SELECT NVL(MAX(line_no),0) +1
220 INTO v_bpo_line_no
221 FROM po_bpos_dtl
222 WHERE bpo_id = v_bpo_id;
223 END IF;
224
225 RETURN v_bpo_line_no;
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 RETURN 0;
230
231 WHEN OTHERS THEN
232 err_msg := SUBSTRB(SQLERRM, 1, 100);
233 RAISE_APPLICATION_ERROR(-20000, err_msg);
234
235 END get_bpo_line_no;
236
237
238 /*-----------------------------------------------------------------------------|
239 | |
240 | PROCEDURE NAME cpg_bint2gms |
241 | |
242 | DESCRIPTION |
243 | |
244 | Procedure to synchronize rows from Oracle to Gemms for Planned PO's |
245 | |
246 | MODIFICATION HISTORY |
247 | |
248 | 26-OCT-97 R Chellam Created |
249 | 20-NOV-97 R Chellam Modified to deal only with PPOs |
250 | 06/NOV/98 Tony Ricci removed call to GML_PO_SYNCH. |
251 | cpg_conv_duom and replaced with |
252 | GMICUOM.icuomcv which is the OPM |
253 | standard uom conversion |
254 | 28-JAN-99 T.Ricci check dualum_ind before calling |
255 | GMICUOM.icuomcv and remove nvl on |
256 | item_um2 before insert to |
257 | po_rels_schBUG#809339 |
258 | 15-MAY-2001 PKU Bug 1776328 - Contract dates for BPO|
259 | are same on OPM and APPS side |
260 | 04-JUN-2001 PKU Bug 1811587 - Contract dates for BPO|
261 | are same on OPM after update on |
262 | APPS side. |
263 |-----------------------------------------------------------------------------*/
264
265 PROCEDURE cpg_bint2gms ( retcode OUT NOCOPY NUMBER)
266 IS
267
268 CURSOR int_cur IS
269 SELECT *
270 FROM cpg_purchasing_interface
271 WHERE invalid_ind = 'N'
272 AND (transaction_type = 'PLANNED' AND release_num = 0);
273
274 int_rec int_cur%ROWTYPE;
275
276 err_num NUMBER;
277 err_msg VARCHAR2(100);
278
279 /* Cursor to select shipvend_id and payvend_id */
280
281 CURSOR vendor_cur (p_of_vendor_site_id PO_VEND_MST.OF_VENDOR_SITE_ID%TYPE)
282 IS
283 SELECT vendor_id
284 FROM po_vend_mst
285 WHERE of_vendor_site_id = p_of_vendor_site_id;
286
287 /* Cursor to select fob_code */
288
289 CURSOR fob_code_cur (p_of_fob_code OP_FOBC_MST.OF_FOB_CODE%TYPE) IS
290 SELECT fob_code
291 FROM op_fobc_mst
292 WHERE of_fob_code = p_of_fob_code;
293
294 /* Cursor to select terms_code */
295
296 CURSOR terms_code_cur (p_of_terms_code OP_TERM_MST.OF_TERMS_CODE%TYPE) IS
297 SELECT terms_code
298 FROM op_term_mst
299 WHERE of_terms_code = p_of_terms_code;
300
301 /* Cursor to select bpo_id */
302
303 CURSOR bpo_id_cur (p_bpo_no PO_BPOS_HDR.BPO_NO%TYPE) IS
304 SELECT bpo_id
305 FROM po_bpos_hdr
306 WHERE bpo_no = p_bpo_no;
307
308 /* Cursor to select bpo_line_id */
309
310 CURSOR bpo_line_id_cur
311 (
312 p_po_header_id CPG_PURCHASING_INTERFACE.PO_HEADER_ID%TYPE,
313 p_po_line_id CPG_PURCHASING_INTERFACE.PO_LINE_ID%TYPE,
314 p_po_line_location_id CPG_PURCHASING_INTERFACE.PO_LINE_LOCATION_ID%TYPE
315 ) IS
316 SELECT bpo_line_id
317 FROM cpg_oragems_mapping
318 WHERE po_header_id = p_po_header_id
319 AND po_line_id = p_po_line_id
320 AND po_line_location_id = p_po_line_location_id;
321
322 /* Cursor to select values from ic_item_mst */
323
324 CURSOR item_cur (p_item_no IC_ITEM_MST.ITEM_NO%TYPE) IS
325 SELECT item_id, nvl(item_desc1,' '), item_um2, dualum_ind
326 FROM ic_item_mst
327 WHERE item_no = p_item_no;
328
329 /* Cursor to select co_code */
330
331 CURSOR co_code_cur (p_orgn_code SY_ORGN_MST.ORGN_CODE%TYPE) IS
332 SELECT co_code
333 FROM sy_orgn_mst
334 WHERE orgn_code = p_orgn_code;
335
336 v_po_status NUMBER;
337 v_order2 NUMBER;
338
339 v_new_bpo_id PO_BPOS_HDR.BPO_ID%TYPE;
340 v_bpo_id PO_BPOS_HDR.BPO_ID%TYPE;
341 v_bpo_line_id PO_BPOS_DTL.LINE_ID%TYPE;
342 v_new_line_id PO_BPOS_DTL.LINE_ID%TYPE;
343 v_bpo_line_no PO_BPOS_DTL.LINE_NO%TYPE;
344
345 v_item_id IC_ITEM_MST.ITEM_ID%TYPE;
346 v_item_um2 IC_ITEM_MST.ITEM_UM%TYPE;
347 v_item_dualum_ind IC_ITEM_MST.DUALUM_IND%TYPE;
348
349 v_item_desc IC_ITEM_MST.ITEM_DESC1%TYPE;
350 v_ship_vendor_id PO_VEND_MST.VENDOR_ID%TYPE;
351 v_pay_vendor_id PO_VEND_MST.VENDOR_ID%TYPE;
352 v_fob_code OP_FOBC_MST.FOB_CODE%TYPE;
353 v_terms_code OP_TERM_MST.TERMS_CODE%TYPE;
354
355 v_exchange_rate GL_XCHG_RTE.EXCHANGE_RATE%TYPE;
356 v_mul_div_sign GL_XCHG_RTE.MUL_DIV_SIGN%TYPE;
357 v_exchange_rate_date GL_XCHG_RTE.EXCHANGE_RATE_DATE%TYPE;
358 v_base_currency GL_XCHG_RTE.TO_CURRENCY_CODE%TYPE;
359 v_gl_source_type GL_SRCE_MST.TRANS_SOURCE_TYPE%TYPE;
360
361 v_next_id_status BOOLEAN;
362
363 BEGIN
364
365
366 retcode := 0;
367
368 OPEN int_cur;
369
370 FETCH int_cur INTO int_rec;
371
372 WHILE int_cur%FOUND
373 LOOP
374 IF GML_PO_SYNCH.gemms_validate
375 (int_rec.orgn_code,
376 int_rec.of_payvend_site_id,
377 int_rec.of_shipvend_site_id,
378 int_rec.to_whse,
379 int_rec.billing_currency,
380 int_rec.item_no,
381 int_rec.order_um1,
382 int_rec.price_um,
383 int_rec.order_um2,
384 int_rec.item_um,
385 int_rec.buyer_code,
386 int_rec.from_whse,
387 int_rec.shipper_code,
388 int_rec.of_frtbill_mthd,
389 int_rec.of_terms_code,
390 int_rec.qc_grade_wanted,
391 int_rec.po_no,
392 int_rec.po_line_id,
393 int_rec.po_line_location_id,
394 int_rec.revision_count,
395 /* T. Ricci 5/12/98 replaced date_modified with */
396 /* last_update_date for GEMMS 5.0*/
397 int_rec.last_update_date
398 ) = FALSE THEN
399
400 UPDATE cpg_purchasing_interface
401 SET invalid_ind = 'Y'
402 WHERE po_header_id = int_rec.po_header_id
403 AND po_line_id = int_rec.po_line_id
404 AND po_line_location_id = int_rec.po_line_location_id;
405
406 retcode := 1;
407
408 ELSE /* all fields are validate*/
409
410 /* T.Ricci 5/12/98 set po_status = 0 for 'CANCEL'*/
411
412 IF int_rec.po_status = 'OPEN' THEN
413 v_po_status := 0;
414 ELSIF int_rec.po_status = 'CANCEL' THEN
415 v_po_status :=0;
416 ELSIF (int_rec.po_status = 'CLOSED' OR
417 int_rec.po_status = 'CLOSED FOR RECEIVING' OR
418 int_rec.po_status = 'CLOSED FOR INVOICE' OR
419 int_rec.po_status = 'FINALLY CLOSED') THEN
420 /*v_po_status := 2;*/
421 v_po_status := 20;
422 END IF;
423
424 GML_VALIDATE_PO.get_base_currency (v_base_currency,
425 int_rec.orgn_code);
426
427 IF (v_base_currency <> int_rec.billing_currency) THEN
428
429 GML_VALIDATE_PO.get_gl_source (v_gl_source_type);
430
431 GML_VALIDATE_PO.get_exchange_rate (v_exchange_rate,
432 v_mul_div_sign,
433 v_exchange_rate_date,
434 v_base_currency, /* to-currency*/
435 int_rec.billing_currency,
436 v_gl_source_type);
437 END IF;
438
439 OPEN vendor_cur(int_rec.of_shipvend_site_id);
440 FETCH vendor_cur
441 INTO v_ship_vendor_id;
442 CLOSE vendor_cur;
443
444 OPEN vendor_cur(int_rec.of_payvend_site_id);
445 FETCH vendor_cur
446 INTO v_pay_vendor_id;
447 CLOSE vendor_cur;
448
449 OPEN fob_code_cur(int_rec.fob_code);
450 FETCH fob_code_cur
451 INTO v_fob_code;
452 CLOSE fob_code_cur;
453
454 OPEN terms_code_cur(int_rec.of_terms_code);
455 FETCH terms_code_cur
456 INTO v_terms_code;
457 CLOSE terms_code_cur;
458
459 IF NOT GML_SYNCH_BPOS.bpo_exist(int_rec.po_no) THEN
460 BEGIN
461 GML_SYNCH_BPOS.next_bpo_id(v_new_bpo_id, int_rec.orgn_code,
462 v_next_id_status);
463 IF v_next_id_status=FALSE THEN
464 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
465 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error getting next bpo_id');
466 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
467 err_num := SQLCODE;
468 err_msg := SUBSTRB (SQLERRM, 1, 100);
469 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
470 retcode :=1;
471 raise_application_error(-20001, err_msg);
472 END IF;
473
474
475 /* T.Ricci 5/11/98 removed user_class and added new who columns*/
476
477 BEGIN
478 INSERT INTO po_bpos_hdr
479 (
480 bpo_id,
481 orgn_code,
482 bpo_no,
483 rel_count,
484 payvend_id,
485 contract_no,
486 contract_value,
487 contract_currency,
488 currency_bght_fwd,
489 contract_exchange_rate,
490 mul_div_sign,
491 amount_purchased,
492 contract_start_date,
493 contract_end_date,
494 shipvend_id,
495 shipper_code,
496 recv_desc,
497 ship_mthd,
498 frtbill_mthd,
499 terms_code,
500 bpo_status,
501 bpohold_code,
502 cancellation_code,
503 closure_code,
504 activity_ind,
505 fob_code,
506 buyer_code,
507 icpurch_class,
508 vendso_no,
509 project_no,
510 date_printed,
511 revision_count,
512 in_use,
513 print_count,
514 creation_date,
515 created_by,
516 last_update_date,
517 last_updated_by,
518 last_update_login,
519 delete_mark,
520 text_code,
521 orgnaddr_id
522 )
523 VALUES /* Insertion */
524 (
525 v_new_bpo_id,
526 nvl(int_rec.orgn_code, '-1'),
527 int_rec.po_no,
528 nvl(int_rec.rel_count,0),
529 nvl(v_pay_vendor_id,0),
530 int_rec.contract_no, /* KYH 24/AUG/98 nullable column*/
531 int_rec.contract_value, /* KYH 24/AUG/98 nullable column*/
532 nvl(int_rec.billing_currency,'USD'),
533 nvl(int_rec.currency_bght_fwd,0),
534 nvl(v_exchange_rate,1),
535 nvl(v_mul_div_sign,0),
536 int_rec.amount_purchased, /* KYH 24/AUG/98 nullable column */
537 nvl(int_rec.contract_start_date, sysdate), /* PKU bug 1776328 */
538 nvl(int_rec.contract_end_date , to_date('31-12-2010','DD-MM-YYYY')),
539 nvl(v_ship_vendor_id, 0),
540 int_rec.shipper_code, /* KYH 24/AUG/98 nullable column */
541 int_rec.recv_desc, /* KYH 24/AUG/98 nullable column*/
542 int_rec.ship_mthd, /* KYH 24/AUG/98 integ constr change*/
543 int_rec.of_frtbill_mthd, /* KYH 24/AUG/98 nullable column*/
544 v_terms_code, /* KYH 24/AUG/98 nullable column*/
545 nvl(v_po_status,0),
546 int_rec.bpohold_code, /* KYH 24/AUG/98 integ constr change */
547 int_rec.cancellation_code, /* KYH 24/AUG/98 nullable column*/
548 int_rec.closure_code, /* KYH 24/AUG/98 integ constr change*/
549 nvl(int_rec.activity_ind,0),
550 v_fob_code, /*int_rec.fob_code, KYH 24/AUG/98 nullable column*/
551 int_rec.buyer_code, /* KYH 24/AUG/98 nullable column*/
552 int_rec.icpurch_class, /* KYH 24/AUG/98 integ constr change*/
553 int_rec.vendso_no, /* KYH 24/AUG/98 nullable column*/
554 int_rec.project_no, /* KYH 24/AUG/98 integ constr change*/
555 nvl(int_rec.date_printed,sysdate),
556 int_rec.revision_count, /* KYH 24/AUG/98 nullable column */
557 nvl(int_rec.in_use,0),
558 nvl(int_rec.print_count,0),
559 nvl(int_rec.creation_date,sysdate),
560 nvl(int_rec.created_by,0),
561 nvl(int_rec.last_update_date,sysdate),
562 nvl(int_rec.last_updated_by,0),
563 nvl(int_rec.last_update_login,0),
564 nvl(int_rec.delete_mark,0),
565 int_rec.text_code, /* KYH 24/AUG/98 integ const change*/
566 int_rec.orgnaddr_id /* KYH 24/AUG/98 integ const change*/
567 );
568 EXCEPTION
569 WHEN OTHERS THEN
570 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
571 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into po_bpos_hdr');
572 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
573 err_num := SQLCODE;
574 err_msg := SUBSTRB (SQLERRM, 1, 100);
575 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
576 retcode :=1;
577 raise_application_error(-20001, err_msg);
578 END;
579 END;
580 END IF;
581
582 /* Fetch bpo_id for record*/
583 OPEN bpo_id_cur(int_rec.po_no);
584 FETCH bpo_id_cur
585 INTO v_bpo_id;
586 CLOSE bpo_id_cur;
587
588 OPEN item_cur(int_rec.item_no);
589 FETCH item_cur
590 INTO v_item_id, v_item_desc, v_item_um2, v_item_dualum_ind;
591 CLOSE item_cur;
592
593
594 /* 11/6/1998 T. Ricci added*/
595 IF v_item_dualum_ind > 0 THEN
596 GMICUOM.icuomcv
597 (v_item_id,0,
598 int_rec.order_qty1,
599 int_rec.order_um1,
600 v_item_um2,
601 v_order2);
602 ELSE
603 v_order2 := 0;
604 END IF;
605
606 IF NOT GML_SYNCH_BPOS.bpo_line_exist
607 ( int_rec.po_header_id,
608 int_rec.po_line_id,
609 int_rec.po_line_location_id) THEN
610
611 GML_PO_SYNCH.next_line_id('BPO', v_new_line_id, v_next_id_status);
612 IF v_next_id_status=FALSE THEN
613 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
614 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error getting next line_id');
615 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
616 err_num := SQLCODE;
617 err_msg := SUBSTRB (SQLERRM, 1, 100);
618 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
619 retcode :=1;
620 raise_application_error(-20001, err_msg);
621 retcode :=1;
622 raise_application_error(-20001, err_msg);
623 END IF;
624
625 /* 03/24/00 NC - Bug#1249797
626 OPM was generating it's own line number regardless of what
627 the line number is in oracle.Modified to take oracle line number
628 instead.
629 */
630
631 /*
632 v_bpo_line_no := GML_SYNCH_BPOS.get_bpo_line_no(v_bpo_id) + 1;
633 */
634 v_bpo_line_no := GML_SYNCH_BPOS.get_bpo_line_no(v_bpo_id,int_rec.po_header_id,int_rec.po_line_id);
635
636
637 /* T.Ricci 5/12/98 removed user_class and added new who columns*/
638
639 BEGIN
640 INSERT INTO po_bpos_dtl
641 (
642 bpo_id,
643 line_no,
644 line_id,
645 item_id,
646 generic_id,
647 item_desc,
648 contract_value,
649 contract_qty,
650 amount_purchased,
651 item_um,
652 qty_purchased,
653 std_qty,
654 release_interval,
655 icpurch_class,
656 bpo_status,
657 net_price,
658 from_whse,
659 to_whse,
660 recv_loct,
661 recvaddr_id,
662 recv_desc,
663 ship_mthd,
664 shipper_code,
665 shipvend_id,
666 qc_grade_wanted,
667 frtbill_mthd,
668 terms_code,
669 bpohold_code,
670 cancellation_code,
671 closure_code,
672 fob_code,
673 vendso_no,
674 buyer_code,
675 project_no,
676 creation_date,
677 created_by,
678 last_update_date,
679 last_updated_by,
680 last_update_login,
681 text_code,
682 trans_cnt,
683 max_rels_qty,
684 match_type
685 )
686 VALUES
687 ( v_bpo_id,
688 v_bpo_line_no,
689 v_new_line_id,
690 nvl(v_item_id, 0), /*int_rec.item_id,*/
691 int_rec.generic_id, /*KYH 24/AUG/98 integ constr*/
692 nvl(v_item_desc,' '), /*int_rec.item_desc,*/
693 int_rec.contract_value, /*KYH 24/AUG/98 nullable column*/
694 int_rec.std_qty, /*contract_qty KYH 24/AUG/98 nullable column*/
695 0, /* amount_purchased,*/
696 nvl(int_rec.order_um1, ' '), /*int_rec.item_um,*/
697 0, /*qty_purchased*/
698 nvl(int_rec.std_qty, 0),
699 nvl(int_rec.release_interval,1),
700 int_rec.icpurch_class, /*KYH 24/AUG/98 integ constr*/
701 nvl(v_po_status,0),
702 nvl(int_rec.net_price,0),
703 int_rec.from_whse, /*KYH 24/AUG/98 integ constr*/
704 nvl(int_rec.to_whse,' '),
705 int_rec.recv_loct, /*KYH 24/AUG/98 integ constr*/
706 int_rec.recvaddr_id, /*KYH 24/AUG/98 integ constr*/
707 int_rec.recv_desc, /*KYH 24/AUG/98 nullable column*/
708 int_rec.ship_mthd, /*KYH 24/AUG/98 integ constr */
709 int_rec.shipper_code, /*KYH 24/AUG/98 nullable column*/
710 nvl(v_ship_vendor_id,0), /*int_rec.of_shipvend_site_id,*/
711 int_rec.qc_grade_wanted, /*KYH 24/AUG/98 nullable column*/
712 int_rec.of_frtbill_mthd, /*KYH 24/AUG/98 nullable column*/
713 v_terms_code,/*int_rec.of_terms_code, --KYH 24/AUG/98 nullable column*/
714 int_rec.bpohold_code, /*KYH 24/AUG/98 nullable column */
715 int_rec.cancellation_code, /*KYH 24/AUG/98 nullable column*/
716 int_rec.closure_code, /*KYH 24/AUG/98 integ constr chnge*/
717 v_fob_code, /*int_rec.fob_code,KYH 24/AUG/98 nullable column*/
718 int_rec.vendso_no, /*KYH 24/AUG/98 nullable column*/
719 int_rec.buyer_code, /*KYH 24/AUG/98 nullable column*/
720 int_rec.project_no, /*KYH 24/AUG/98 integ constr chnge*/
721 nvl(int_rec.creation_date,sysdate),
722 nvl(int_rec.created_by,0),
723 nvl(int_rec.last_update_date,sysdate),
724 nvl(int_rec.last_updated_by,0),
725 nvl(int_rec.last_update_login,0),
726 int_rec.text_code, /*KYH 24/AUG/98 integ constr chnge*/
727 nvl(int_rec.trans_cnt,1),
728 nvl(int_rec.max_rels_qty, 0),
729 nvl(int_rec.match_type,3)
730 );
731 EXCEPTION
732 WHEN OTHERS THEN
733 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
734 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into po_bpos_dtl');
735 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
736 err_num := SQLCODE;
737 err_msg := SUBSTRB (SQLERRM, 1, 100);
738 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
739 retcode :=1;
740 raise_application_error(-20001, err_msg);
741 END;
742
743
744 /* T.Ricci 5/12/98 removed user_class and added new who columns*/
745
746 BEGIN
747 INSERT INTO po_rels_sch
748 (
749 bpoline_id,
750 agreed_dlvdate,
751 line_status,
752 order_qty1,
753 order_qty2,
754 order_um1,
755 order_um2,
756 from_whse,
757 to_whse,
758 recv_loct,
759 recvaddr_id,
760 recv_desc,
761 ship_mthd,
762 shipper_code,
763 poline_id,
764 creation_date,
765 last_update_login,
766 created_by,
767 last_update_date,
768 last_updated_by,
769 text_code,
770 trans_cnt,
771 delete_mark
772 )
773 VALUES
774 (
775 v_new_line_id,
776 nvl(int_rec.agreed_dlvdate, sysdate), /*agreed_dlvdate,*/
777 1, /*line_status,*/
778 nvl(int_rec.order_qty1,0), /*order_qty1,*/
779 nvl(v_order2, 0), /*order_qty2,*/
780 nvl(int_rec.order_um1,' '),
781 v_item_um2,
782 int_rec.from_whse, /*KYH 24/AUG/98 Integ Constr*/
783 nvl(int_rec.to_whse,' '),
784 int_rec.recv_loct, /*KYH 24/AUG/98 Integ Constr*/
785 int_rec.recvaddr_id, /*KYH 24/AUG/98 Integ Constr*/
786 int_rec.recv_desc, /*KYH 24/AUG/98 Nullable Col*/
787 int_rec.ship_mthd, /*KYH 24/AUG/98 Integ Constr*/
788 int_rec.shipper_code, /*KYH 24/AUG/98 Integ Constr*/
789 NULL, /*KYH 24/AUG/98 Integ Constr*/
790 nvl(int_rec.creation_date,sysdate),
791 nvl(int_rec.last_update_login,0),
792 nvl(int_rec.created_by,0),
793 nvl(int_rec.last_update_date,sysdate),
794 nvl(int_rec.last_updated_by,0),
795 int_rec.text_code, /*KYH 24/AUG/98 Integ Constr*/
796 nvl(int_rec.trans_cnt,1),
797 nvl(int_rec.delete_mark,0)
798 );
799 EXCEPTION
800 WHEN OTHERS THEN
801 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
802 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into po_rels_sch');
803 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
804 err_num := SQLCODE;
805 err_msg := SUBSTRB (SQLERRM, 1, 100);
806 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
807 retcode :=1;
808 raise_application_error(-20001, err_msg);
809 END;
810
811
812 UPDATE cpg_oragems_mapping
813 SET bpo_id = v_bpo_id,
814 bpo_line_id = v_new_line_id
815 WHERE po_header_id = int_rec.po_header_id
816 AND po_line_id = int_rec.po_line_id
817 AND po_line_location_id = int_rec.po_line_location_id;
818
819 ELSE /* Else condition for BPO exist */
820 /*Get bpo_line_id*/
821 OPEN bpo_line_id_cur (int_rec.po_header_id, int_rec.po_line_id,
822 int_rec.po_line_location_id);
823 FETCH bpo_line_id_cur INTO v_bpo_line_id;
824 CLOSE bpo_line_id_cur;
825
826
827 /*Updates including closed/cancel*/
828
829 BEGIN
830 UPDATE po_bpos_hdr
831 SET payvend_id = nvl(v_pay_vendor_id,0),
832 contract_no = int_rec.contract_no, /*KYH nullable*/
833 contract_value = int_rec.contract_value,/*KYH nullable*/
834 contract_currency = nvl(int_rec.billing_currency,'USD'),
835 currency_bght_fwd = nvl(int_rec.currency_bght_fwd,0),
836 contract_exchange_rate = nvl(v_exchange_rate,1),
837 mul_div_sign = nvl(v_mul_div_sign,0),
838 contract_start_date = nvl(int_rec.contract_start_date, sysdate), /* PKU bug 1811587 */
839 contract_end_date = nvl(int_rec.contract_end_date , to_date('31-12-2010','DD-MM-YYYY')), /* PKU bug 1811587 */
840 shipvend_id = nvl(v_ship_vendor_id, 0),
841 shipper_code = int_rec.shipper_code, /*KYH nullable*/
842 recv_desc = int_rec.recv_desc, /*KYH nullable*/
843 ship_mthd = int_rec.ship_mthd, /*KYH nullable*/
844 frtbill_mthd = int_rec.of_frtbill_mthd,/* KYH nullable*/
845 terms_code = v_terms_code, /*KYH nullable */
846 /* bpo_status = nvl(v_po_status,0),*/
847 bpohold_code = int_rec.bpohold_code, /*KYH nullable*/
848 cancellation_code = int_rec.cancellation_code,/*KYH nullable*/
849 closure_code = int_rec.closure_code,/*KYH nullable*/
850 activity_ind = nvl(int_rec.activity_ind,0),
851 fob_code = v_fob_code, /*KYH nullable */
852 buyer_code = int_rec.buyer_code, /*KYH nullable*/
853 icpurch_class = int_rec.icpurch_class,/*KYH nullable*/
854 vendso_no = int_rec.vendso_no, /*KYH nullable*/
855 project_no = int_rec.project_no, /*KYH nullable*/
856 date_printed = nvl(int_rec.date_printed,sysdate),
857 revision_count = int_rec.revision_count,/*KYH nullable*/
858 in_use = nvl(int_rec.in_use,0),
859 print_count = nvl(int_rec.print_count,0),
860 creation_date = nvl(int_rec.creation_date,sysdate),
861 last_update_login = nvl(int_rec.last_update_login,0),
862 created_by = nvl(int_rec.created_by,0),
863 last_update_date = nvl(int_rec.last_update_date,sysdate),
864 last_updated_by = nvl(int_rec.last_updated_by,0),
865 delete_mark = nvl(int_rec.delete_mark,0),
866 text_code = int_rec.text_code, /*KYH nullable*/
867 orgnaddr_id = int_rec.orgnaddr_id /*KYH nullable*/
868 WHERE bpo_id = v_bpo_id;
869 EXCEPTION
870 WHEN OTHERS THEN
871 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
872 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_bpos_hdr');
873 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
874 err_num := SQLCODE;
875 err_msg := SUBSTRB (SQLERRM, 1, 100);
876 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
877 retcode :=1;
878 raise_application_error(-20001, err_msg);
879 END;
880
881 BEGIN
882 UPDATE po_bpos_dtl
883 SET item_id = nvl(v_item_id, 0),
884 generic_id = int_rec.generic_id, /* KYH nullable */
885 item_desc = nvl(v_item_desc,' '),
886 contract_value = int_rec.contract_value,/* KYH nullable*/
887 contract_qty = int_rec.std_qty, /* KYH nullable*/
888 item_um = nvl(int_rec.order_um1, ' '),
889 std_qty = nvl(int_rec.std_qty, 0),
890 release_interval = nvl(int_rec.release_interval,1),
891 icpurch_class = int_rec.icpurch_class,/* KYH 24/AUG/98*/
892 bpo_status = nvl(v_po_status,0),
893 net_price = nvl(int_rec.net_price,0),
894 from_whse = int_rec.from_whse, /* KYH nullable*/
895 to_whse = nvl(int_rec.to_whse,' '),
896 recv_loct = int_rec.recv_loct, /* KYH nullable*/
897 recvaddr_id = int_rec.recvaddr_id, /* KYH nullable*/
898 recv_desc = int_rec.recv_desc, /* KYH nullable*/
899 ship_mthd = int_rec.ship_mthd, /* KYH nullable*/
900 shipper_code = int_rec.shipper_code, /* KYH nullable*/
901 shipvend_id = nvl(v_ship_vendor_id,0),
902 qc_grade_wanted = int_rec.qc_grade_wanted,/* KYH nullable*/
903 frtbill_mthd = int_rec.of_frtbill_mthd,/* KYH nullable*/
904 terms_code = v_terms_code, /* KYH nullable*/
905 bpohold_code = int_rec.bpohold_code,/* KYH nullable */
906 cancellation_code = int_rec.cancellation_code,/*KYH nullable*/
907 closure_code = int_rec.closure_code,/* KYH nullable*/
908 fob_code = v_fob_code,/*KYH nullable */
909 vendso_no = int_rec.vendso_no,/* KYH nullable*/
910 buyer_code = int_rec.buyer_code,/* KYH nullable*/
911 project_no = int_rec.project_no,/* KYH nullable*/
912 creation_date = nvl(int_rec.creation_date,sysdate),
913 last_update_login = nvl(int_rec.last_update_login,0),
914 created_by = nvl(int_rec.created_by,0),
915 last_update_date = nvl(int_rec.last_update_date,sysdate),
916 last_updated_by = nvl(int_rec.last_updated_by,0),
917 text_code = int_rec.text_code, /* KYH nullable*/
918 trans_cnt = nvl(int_rec.trans_cnt, 1),
919 max_rels_qty = nvl(int_rec.max_rels_qty, 0),
920 match_type = nvl(int_rec.match_type,3)
921 WHERE bpo_id = v_bpo_id
922 AND line_id = v_bpo_line_id;
923 EXCEPTION
924 WHEN OTHERS THEN
925 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
926 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_bpos_dtl');
927 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
928 err_num := SQLCODE;
929 err_msg := SUBSTRB (SQLERRM, 1, 100);
930 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
931 retcode :=1;
932 raise_application_error(-20001, err_msg);
933 END;
934
935 BEGIN
936 UPDATE po_rels_sch
937 SET agreed_dlvdate = nvl(int_rec.agreed_dlvdate, sysdate),
938 line_status = 1, /*line_status,*/
939 order_qty1 = nvl(int_rec.order_qty1,0),
940 order_qty2 = nvl(v_order2, 0),
941 order_um1 = nvl(int_rec.order_um1,' '),
942 order_um2 = v_item_um2, /* KYH nullable*/
943 from_whse = int_rec.from_whse, /* KYH nullable*/
944 to_whse = nvl(int_rec.to_whse,' '),
945 recv_loct = int_rec.recv_loct, /* KYH nullable*/
946 recvaddr_id = int_rec.recvaddr_id,/* KYH nullable*/
947 recv_desc = int_rec.recv_desc, /* KYH nullable*/
948 ship_mthd = int_rec.ship_mthd, /* KYH nullable*/
949 shipper_code = int_rec.shipper_code,/* KYH nullable*/
950 poline_id = NULL,
951 creation_date = nvl(int_rec.creation_date,sysdate),
952 last_update_login = nvl(int_rec.last_update_login,0),
953 created_by = nvl(int_rec.created_by,0),
954 last_update_date = nvl(int_rec.last_update_date,sysdate),
955 last_updated_by = nvl(int_rec.last_updated_by,0),
956 trans_cnt = nvl(int_rec.trans_cnt,1) + 1
957 WHERE bpoline_id = v_bpo_line_id;
958 EXCEPTION
959 WHEN OTHERS THEN
960 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
961 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error updating po_rels_sch');
962 FND_FILE.NEW_LINE(FND_FILE.LOG, 1 );
963 err_num := SQLCODE;
964 err_msg := SUBSTRB (SQLERRM, 1, 100);
965 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
966 retcode :=1;
967 raise_application_error(-20001, err_msg);
968 END;
969
970 /*Only for Closing/Cancellation of line in Planned PO*/
971 /*for cancelling cancellation_code = 'ORAF' */
972 /*for closing cancellation_code = ' ' */
973 IF int_rec.po_status IN ('CLOSED', 'CLOSED FOR RECEIVING',
974 'CLOSED FOR INVOICE', 'FINALLY CLOSED') THEN
975
976 UPDATE po_bpos_dtl
977 SET bpo_status = v_po_status, /*20, */
978 cancellation_code = int_rec.cancellation_code,
979 trans_cnt = trans_cnt + 1
980 WHERE bpo_id = v_bpo_id
981 AND line_id = v_bpo_line_id;
982
983 END IF; /* Cancel/Close of PPO. */
984
985 END IF; /* End of If condition for bpo_line existing*/
986
987 UPDATE cpg_purchasing_interface
988 SET invalid_ind = 'P' /*row processed*/
989 WHERE po_header_id = int_rec.po_header_id
990 AND po_line_id = int_rec.po_line_id
991 AND po_line_location_id = int_rec.po_line_location_id;
992 END IF; /* of gemms_validate*/
993
994 COMMIT;
995
996 FETCH int_cur INTO int_rec;
997
998
999 END LOOP;
1000
1001 CLOSE int_cur;
1002
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 err_msg := SUBSTRB(SQLERRM, 1, 100);
1006 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1007 FND_FILE.PUT_LINE(FND_FILE.LOG, err_msg);
1008 retcode := 1;
1009 RAISE_APPLICATION_ERROR(-20000, err_msg);
1010
1011 END cpg_bint2gms ;
1012
1013
1014 END GML_SYNCH_BPOS;