[Home] [Help]
PACKAGE BODY: APPS.GML_PO_INTERFACE
Source
1 PACKAGE BODY GML_PO_INTERFACE AS
2 /* $Header: GMLIPOIB.pls 115.23 2002/03/01 12:46:02 pkm ship $ */
3
4 /*##########################################################################
5 #
6 # PROC
7 #
8 # insert Insert Data into the PO INTERFACE Table
9 #
10 # DESCRIPTION
11 #
12 # This procedure inserts data into the Oracle Interface Table.
13 #
14 #
15 # MODIFICATION HISTORY
16 #
17 # 08-OCT-97 Ravi Dasani , Rajeshwari Chellam Created.
18 # 11-MAY-98 Tony Ricci changes for GEMMS 5.0 database changes
19 # 07-JUL-98 Tony Ricci changes for GEMMS 5.0 nullable columns
20 # 11/10/98 T.Ricci added shipper_code_cur to retreive correct
21 # shipper_code 4 char value from op_ship_mst
22 # 11/11/98 T.Ricci added fob_code_cur to retreive correct
23 # fob_code 4 char value from op_fobc_mst
24 # 12/24/98 added defaults for who columns in cpg_oragems_mapping
25 # 05/13/99 T.Ricci removed checking of inventory_item_flag and
26 # replaced with call to check_opm_item
27 #
28 ## #######################################################################*/
29 PROCEDURE insert_rec
30 ( p_po_header_id IN NUMBER,
31 p_po_line_id IN NUMBER,
32 p_po_line_location_id IN NUMBER,
33 p_quantity IN NUMBER,
34 p_need_by_date IN DATE,
35 p_promised_date IN DATE,
36 p_last_accept_date IN DATE,
37 p_po_release_id IN NUMBER,
38 p_cancel_flag IN VARCHAR2,
39 p_closed_code IN VARCHAR2,
40 p_source_shipment_id IN NUMBER,
41 p_close_trig_call IN VARCHAR2,
42 p_price_override IN NUMBER,
43 p_ship_to_location_id IN NUMBER,
44 p_shipment_num IN NUMBER
45 ) IS
46
47 /* Definitions for variables that are derived from columns of
48 po_headers_all table
49 T. Ricci 5/11/98 added v_created_by, v_last_updated_by, v_last_update_login
50 for GEMMS 5.0 who columns */
51
52 /* T.Ricci 10/15/98 added v_terms_name for terms code fix for OPM 11.0
53 T.Ricci 11/10/98 added v_shipper_code for OPM 11.0
54 T.Ricci 11/11/98 added v_fob_code for OPM 11.0*/
55 v_po_no PO_HEADERS_ALL.SEGMENT1%TYPE;
56 v_old_po_no PO_HEADERS_ALL.SEGMENT1%TYPE;
57 /* HW BUG#:1107267 - new variable to hold rate */
58 v_exchange_rate PO_HEADERS_ALL.RATE%TYPE;
59 v_type_lookup_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
60 v_last_update_date PO_HEADERS_ALL.LAST_UPDATE_DATE%TYPE;
61 v_creation_date PO_HEADERS_ALL.CREATION_DATE%TYPE;
62 v_print_count PO_HEADERS_ALL.PRINT_COUNT%TYPE;
63 v_revision_num PO_HEADERS_ALL.REVISION_NUM%TYPE;
64 v_printed_date PO_HEADERS_ALL.PRINTED_DATE%TYPE;
65 v_approved_date PO_HEADERS_ALL.APPROVED_DATE%TYPE;
66 v_agent_id PO_HEADERS_ALL.AGENT_ID%TYPE;
67 v_currency_code PO_HEADERS_ALL.CURRENCY_CODE%TYPE;
68 v_bill_to_location_id PO_HEADERS_ALL.BILL_TO_LOCATION_ID%TYPE;
69 v_terms_id PO_HEADERS_ALL.TERMS_ID%TYPE;
70 v_terms_name AP_TERMS.NAME%TYPE;
71 v_org_id PO_HEADERS_ALL.ORG_ID%TYPE;
72 v_start_date PO_HEADERS_ALL.START_DATE%TYPE;
73 v_end_date PO_HEADERS_ALL.END_DATE%TYPE;
74 v_terms_code PO_HEADERS_ALL.SHIP_VIA_LOOKUP_CODE%TYPE;
75 v_gemms_orgn_code VARCHAR2(4);
76 v_opm_rel_orgn_code VARCHAR2(4);
77 v_blanket_total_amount PO_HEADERS_ALL.BLANKET_TOTAL_AMOUNT%TYPE;
78 v_created_by PO_HEADERS_ALL.CREATED_BY%TYPE;
79 v_last_updated_by PO_HEADERS_ALL.LAST_UPDATED_BY%TYPE;
80 v_last_update_login PO_HEADERS_ALL.LAST_UPDATE_LOGIN%TYPE;
81 v_opm_rel_exchg_rate PO_HEADERS_ALL.RATE%TYPE; /* Bug 1427876 */
82
83 v_shipper_code OP_SHIP_MST.SHIPPER_CODE%TYPE;
84 v_fob_code OP_FOBC_MST.FOB_CODE%TYPE;
85 /** MC BUG# 1554088 **/
86 /** new variable to hold order um1. **/
87 v_order_um1 SY_UOMS_MST.UM_CODE%TYPE;
88
89
90 /* Definitions for variables that are derived from columns of
91 po_lines_all table*/
92
93 v_item_id PO_LINES_ALL.ITEM_ID%TYPE;
94 v_unit_meas_lookup_code PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE%TYPE;
95 v_unit_price PO_LINES_ALL.UNIT_PRICE%TYPE;
96 v_qc_grade_wanted VARCHAR2(4);
97
98 /* Definitions for variables that are derived from columns of
99 po_releases_all table*/
100
101 v_release_num PO_RELEASES_ALL.RELEASE_NUM%TYPE;
102
103 /* Definitions for variables that are derived from columns of
104 po_line_locations_all table*/
105
106 v_quantity PO_LINE_LOCATIONS_ALL.QUANTITY%TYPE;
107 v_need_by_date PO_LINE_LOCATIONS_ALL.NEED_BY_DATE%TYPE;
108 v_promised_date PO_LINE_LOCATIONS_ALL.PROMISED_DATE%TYPE;
109 v_db_promised_date PO_LINE_LOCATIONS_ALL.PROMISED_DATE%TYPE;
110 v_last_accept_date PO_LINE_LOCATIONS_ALL.LAST_ACCEPT_DATE%TYPE;
111 v_po_release_id PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID%TYPE;
112 v_cancel_flag PO_LINE_LOCATIONS_ALL.CANCEL_FLAG%TYPE;
113 v_closed_code PO_LINE_LOCATIONS_ALL.CLOSED_CODE%TYPE;
114 v_fob_lookup_code PO_LINE_LOCATIONS_ALL.FOB_LOOKUP_CODE%TYPE;
115 v_ship_via_lookup_code PO_LINE_LOCATIONS_ALL.SHIP_VIA_LOOKUP_CODE%TYPE;
116 v_source_shipment_id PO_LINE_LOCATIONS_ALL.SOURCE_SHIPMENT_ID%TYPE;
117 v_freight_terms_lookup_code
118 PO_LINE_LOCATIONS_ALL.FREIGHT_TERMS_LOOKUP_CODE%TYPE;
119 v_price_override PO_LINE_LOCATIONS_ALL.PRICE_OVERRIDE%TYPE;
120 v_ship_to_location_id PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID%TYPE;
121 v_shipment_num PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM%TYPE;
122
123 /* Bug# 1200791 Added by Preetam Bamb for warehouse changes.*/
124 v_ship_to_organization_id PO_LINE_LOCATIONS_ALL.SHIP_TO_ORGANIZATION_ID%TYPE;
125
126 /* Miscellaneous variables */
127
128 /* H. Wahdani temp. variable to store SY$ZERODATE */
129 bind_date DATE;
130 v_vendor_site_id PO_VENDOR_SITES_ALL.VENDOR_SITE_ID%TYPE;
131 v_cancellation_code VARCHAR2(4);
132 v_location_code IC_WHSE_MST.WHSE_CODE%TYPE ; /*Changed by Preetam for warehouse change HR_LOCATIONS.LOCATION_CODE%TYPE*/
133 v_buyer_code VARCHAR2(35);
134
135 v_po_id number;
136 v_line_id number;
137 v_item_desc VARCHAR2(70);
138 v_item_no VARCHAR2(32);
139 v_inventory_item_flag VARCHAR2(1);
140
141 opmitem NUMBER DEFAULT 0;
142
143 err_num NUMBER;
144 err_msg VARCHAR2(100);
145
146 CURSOR c_checkpll_cur ( p_po_header_id IN VARCHAR2,
147 p_po_line_id IN VARCHAR2,
148 p_po_line_location_id IN VARCHAR2
149 )
150 IS
151 SELECT *
152 FROM CPG_ORAGEMS_MAPPING
153 WHERE po_header_id = p_po_header_id
154 AND po_line_id = p_po_line_id
155 AND po_line_location_id = p_po_line_location_id
156 FOR UPDATE;
157
158 r_checkpll_rec c_checkpll_cur%ROWTYPE;
159
160 /* Uday Phadtare B2038851 */
161 CURSOR c_get_old_po_no ( p_po_header_id IN VARCHAR2
162 )
163 IS
164 SELECT po_no
165 FROM CPG_ORAGEMS_MAPPING
166 WHERE po_header_id = p_po_header_id;
167
168 /* Cursor to select buyer code */
169
170 CURSOR buyer_code_cur
171 IS
172 SELECT upper(substrb(last_name ,1,35))
173 FROM per_people_f
174 WHERE person_id=v_agent_id;
175
176 /* Cursor to select values from po_headers_all
177 T. Ricci 5/11/98 added created_by, last_updated_by, last_update_login
178 for GEMMS 5.0 who columns */
179
180 CURSOR hdr_vars_cur
181 IS
182 SELECT segment1, type_lookup_code,
183 currency_code, agent_id, print_count,
184 revision_num, printed_date, approved_date,
185 terms_id, vendor_site_id,
186 creation_date, last_update_date,
187 start_date, end_date ,
188 fob_lookup_code, ship_via_lookup_code,
189 freight_terms_lookup_code, substrb(attribute15,1,4) gemms_orgn_code,
190 blanket_total_amount, created_by, last_updated_by,
191 last_update_login,rate
192 FROM po_headers_all
193 WHERE po_header_id = p_po_header_id;
194
195 /* Cursor to select location code */
196 /* Bug# 1200791 Previously it was this cursor -- after the warehouse modification this
197 was changed to the below cursor - 17-Feb-2000 - Preetam Bamb
198 CURSOR loc_code_cur
199 IS
200 SELECT location_code
201 FROM hr_locations
202 WHERE location_id = v_ship_to_location_id;
203 */
204 CURSOR loc_code_cur
205 IS
206 SELECT whse_code
207 FROM ic_whse_mst
208 WHERE MTL_ORGANIZATION_ID = v_ship_to_organization_id;
209
210 /* Cursor to select values from po_lines_all table */
211
212 CURSOR line_vars_cur
213 IS
214 SELECT item_id, unit_meas_lookup_code, unit_price, qc_grade qc_grade_wanted
215 -- substrb(attribute11,1,4) qc_grade_wanted
216 FROM po_lines_all
217 WHERE po_header_id = p_po_header_id
218 AND po_line_id = p_po_line_id;
219
220 /* Cursor to select values from po_line_locations_all table */
221 /*Bug# 1200791 */
222 CURSOR lineloc_vars_cur
223 IS
224 SELECT quantity, need_by_date, closed_code,
225 promised_date, last_accept_date,
226 po_release_id, cancel_flag,
227 source_shipment_id,
228 price_override,
229 nvl(SHIP_TO_ORGANIZATION_ID,0), /*Bug# 1200791 nvl(ship_to_location_id,0) Changed by Preetam for warehouse changes */
230 shipment_num
231 FROM po_line_locations_all
232 WHERE po_header_id = p_po_header_id
233 AND po_line_id = p_po_line_id
234 AND line_location_id = p_po_line_location_id;
235
236 /* Cursor to select release_num from po_releases_all table */
237
238 CURSOR rel_num_cur
239 IS
240 SELECT release_num
241 FROM po_releases_all
242 WHERE po_release_id = v_po_release_id;
243
244 /* Cursor to select values from mtl_system_items table */
245
246 CURSOR mtl_vars_cur
247 IS
248 SELECT distinct substrb(segment1,1,32), substrb(description, 1,70),
249 inventory_item_flag
250 FROM mtl_system_items
251 WHERE inventory_item_id = v_item_id;
252
253 /* T.Ricci 10/15/98 added terms_name_cur for terms code fix for OPM 11.0*/
254 CURSOR terms_name_cur
255 IS
256 SELECT name
257 FROM ap_terms
258 WHERE term_id = v_terms_id;
259
260 /* T.Ricci 11/10/98 added shipper_code_cur to retreive correct shipper_code
261 4 char value from op_ship_mst*/
262 CURSOR shipper_code_cur
263 IS
264 SELECT shipper_code
265 FROM op_ship_mst
266 WHERE of_shipper_code = v_ship_via_lookup_code;
267
268 /* T.Ricci 11/11/98 added fob_code_cur to retreive correct fob_code
269 4 char value from op_fobc_mst*/
270 CURSOR fob_code_cur
271 IS
272 SELECT fob_code
273 FROM op_fobc_mst
274 WHERE of_fob_code = v_fob_lookup_code;
275
276 /** MC BUG# 1554088 **/
277 /* Cursor to fetch OPM uom code corr. to 25 char APPS unit of measure **/
278 CURSOR uom_code_cur
279 IS
280 SELECT um_code
281 FROM sy_uoms_mst
282 WHERE unit_of_measure = v_unit_meas_lookup_code;
283
284 /* Uday Phadtare B1410454 Select the orgn code for the release */
285 CURSOR opm_rel_orgn_cur
286 IS
287 SELECT substr(attribute15,1,4) opm_rel_orgn_code
288 FROM po_releases_all
289 WHERE po_header_id = p_po_header_id
290 AND po_release_id = v_po_release_id;
291
292 /* BEGIN - Bug 1427876 */
293 CURSOR opm_rel_exchange_rate
294 IS
295 SELECT RATE
296 FROM po_distributions_all
297 WHERE po_header_id = p_po_header_id
298 AND po_line_id = p_po_line_id
299 AND line_location_id = p_po_line_location_id;
300 /* END - Bug 1427876 */
301 BEGIN
302
303 /* T. Ricci 5/11/98 added v_created_by, v_last_updated_by, v_last_update_login
304 for GEMMS 5.0 who columns */
305
306 OPEN hdr_vars_cur;
307 FETCH hdr_vars_cur
308 INTO v_po_no, v_type_lookup_code, v_currency_code,
309 v_agent_id, v_print_count, v_revision_num,
310 v_printed_date, v_approved_date, v_terms_id,
311 v_vendor_site_id, v_creation_date,
312 v_last_update_date, v_start_date, v_end_date,
313 v_fob_lookup_code, v_ship_via_lookup_code,
314 v_freight_terms_lookup_code, v_gemms_orgn_code, v_blanket_total_amount,
315 v_created_by, v_last_updated_by, v_last_update_login,v_exchange_rate;
316 CLOSE hdr_vars_cur;
317
318
319 OPEN buyer_code_cur;
320 FETCH buyer_code_cur
321 INTO v_buyer_code ;
322 CLOSE buyer_code_cur;
323
324
325 OPEN line_vars_cur;
326 FETCH line_vars_cur
327 INTO v_item_id, v_unit_meas_lookup_code , v_unit_price,
328 v_qc_grade_wanted;
329 CLOSE line_vars_cur;
330
331 /** MC BUG# 1554088 **/
332 OPEN uom_code_cur;
333 FETCH uom_code_cur
334 INTO v_order_um1;
335 CLOSE uom_code_cur;
336
337 IF P_CLOSE_TRIG_CALL = 'Y' THEN
338 v_quantity := p_quantity;
339 v_need_by_date := p_need_by_date;
340 v_closed_code := p_closed_code;
341 v_promised_date := p_promised_date;
342 v_last_accept_date := p_last_accept_date;
343 v_po_release_id := p_po_release_id;
344 v_cancel_flag := p_cancel_flag;
345 v_source_shipment_id := p_source_shipment_id;
346 v_price_override := p_price_override;
347 v_ship_to_organization_id := p_ship_to_location_id;
348 /*Bug# 1224724 Commented the line below as now ship to organizaion is used to determine the warehouse code
349 v_ship_to_location_id := p_ship_to_location_id;*/
350 v_shipment_num := p_shipment_num;
351 ELSE
352 OPEN lineloc_vars_cur;
353 FETCH lineloc_vars_cur
354 INTO v_quantity, v_need_by_date, v_closed_code,
355 v_promised_date, v_last_accept_date,
356 v_po_release_id, v_cancel_flag,
357 v_source_shipment_id, v_price_override, v_ship_to_organization_id,/*Bug# 1200791 */
358 v_shipment_num;
359 CLOSE lineloc_vars_cur;
360 END IF;
361
362 OPEN loc_code_cur;
363 FETCH loc_code_cur
364 INTO v_location_code;
365 CLOSE loc_code_cur;
366
367 OPEN rel_num_cur;
368 FETCH rel_num_cur
369 INTO v_release_num;
370 CLOSE rel_num_cur;
371
372 OPEN mtl_vars_cur;
373 FETCH mtl_vars_cur
374 INTO v_item_no, v_item_desc, v_inventory_item_flag;
375 CLOSE mtl_vars_cur;
376
377 /* T.Ricci 10/15/98 added terms_name_cur for terms code fix for OPM 11.0*/
378 OPEN terms_name_cur;
379 FETCH terms_name_cur
380 INTO v_terms_name;
381 CLOSE terms_name_cur;
382
383 /* T.Ricci 11/10/98 added shipper_code_cur to retreive correct shipper_code
384 4 char value from op_ship_mst*/
385 OPEN shipper_code_cur;
386 FETCH shipper_code_cur
387 INTO v_shipper_code;
388 CLOSE shipper_code_cur;
389
390 /* T.Ricci 11/11/98 added fob_code_cur to retreive correct fob_code
391 4 char value from op_fobc_mst*/
392 OPEN fob_code_cur;
393 FETCH fob_code_cur
394 INTO v_fob_code;
395 CLOSE fob_code_cur;
396
400 v_cancellation_code := fnd_profile.value('PO$CANCEL_CODE');
397 IF v_cancel_flag = 'Y' THEN
398 /* BEGIN - Bug 1228034 Pushkar Upakare */
399 /* v_cancellation_code := fnd_profile.value('OP$HOLDREAS_CODE'); */
401 /* END - Bug 1228034 */
402 ELSE
403 v_cancellation_code := NULL; /* T.Ricci 7/6/98 changed to NULL*/
404 END IF;
405
406 /* H. Wahdani - retrieve SY$ZERODATE */
407 bind_date := gma_core_pkg.get_date_constant('SY$ZERODATE');
408
409 /* T. Ricci 11/12/98 added check before insert - MEGAPATCH fix*/
410 v_db_promised_date := v_promised_date;
411
412 IF v_db_promised_date IS NULL THEN
413 v_db_promised_date := v_need_by_date;
414 END IF;
415
416 -- Begin B1410454
417 /* Select the OPM orgn Code from the PO_RELEASES_ALL table, if it is
418 release, else the v_gemms_orgn_code will be from the PO_HEADERS_ALL
419 table for all other purposes. If the v_opm_rel_orgn_code is NOT NULL
420 then it will be used , else the v_gemms_orgn_code from the PO_HEAD
421 ERS_ALL will be used */
422
423 IF (v_po_release_id is not null) THEN
424 OPEN opm_rel_orgn_cur;
425 FETCH opm_rel_orgn_cur
426 INTO v_opm_rel_orgn_code;
427 CLOSE opm_rel_orgn_cur;
428
429 IF (v_opm_rel_orgn_code IS NOT NULL) THEN
430 v_gemms_orgn_code := v_opm_rel_orgn_code;
431 END IF;
432
433 /* BEGIN - Bug 1427876 */
434 OPEN opm_rel_exchange_rate;
435 FETCH opm_rel_exchange_rate into v_opm_rel_exchg_rate;
436 CLOSE opm_rel_exchange_rate;
437
438 IF (v_opm_rel_exchg_rate IS NOT NULL) THEN
439 v_exchange_rate := v_opm_rel_exchg_rate;
440 END IF;
441 /* END - Bug 1427876 */
442
443 END IF;
444 -- End B1410454
445
446 IF v_gemms_orgn_code IS NULL THEN
447 v_gemms_orgn_code := fnd_profile.value('GEMMS_DEFAULT_ORGN');
448 END IF;
449
450 /* T.Ricci 5/13/99 added call to check for opm item */
451 opmitem := GMF_OPM_ITEM.check_opm_item (v_item_no);
452
453 /* T. Ricci 5/11/98 changed INSERT for 5.0 changes, removed user_class
454 columns and implemented new who columns */
455
456 IF NOT ( nvl(substrb(v_type_lookup_code,1,10), ' ') = 'BLANKET' AND
457 nvl(v_po_release_id, 0) = 0)
458 AND opmitem = 1 THEN
459
460 /* Uday Phadtare B2038851 */
461 OPEN c_get_old_po_no ( p_po_header_id );
462 FETCH c_get_old_po_no into v_old_po_no;
463 IF c_get_old_po_no%FOUND then
464 v_po_no := v_old_po_no;
465 END IF;
466 CLOSE c_get_old_po_no;
467
468
469 INSERT INTO cpg_purchasing_interface
470 ( transaction_id,
471 transaction_type,
472 orgn_code,
473 po_no,
474 po_header_id,
475 po_line_id,
476 po_line_location_id,
477 po_distribution_id,
478 po_status,
479 buyer_code,
480 po_id,
481 bpo_id,
482 bpo_release_number,
483 of_payvend_site_id,
484 of_shipvend_site_id,
485 po_date,
486 po_type,
487 from_whse,
488 to_whse,
489 recv_desc,
490 recv_loct,
491 recvaddr_id,
492 ship_mthd,
493 shipper_code,
494 of_frtbill_mthd,
495 of_terms_code,
496 billing_currency,
497 purchase_exchange_rate,
498 mul_div_sign,
499 currency_bght_fwd,
500 pohold_code,
501 cancellation_code,
502 fob_code,
503 icpurch_class,
504 vendso_no,
505 project_no,
506 requested_dlvdate,
507 sched_shipdate,
508 required_dlvdate,
509 agreed_dlvdate,
510 date_printed,
511 expedite_date,
512 revision_count,
513 in_use,
514 print_count,
515 line_id,
516 bpo_line_id,
517 apinv_line_id,
518 item_no,
519 generic_id,
520 item_desc,
521 order_qty1,
522 order_qty2,
523 order_um1,
524 order_um2,
525 received_qty1,
526 received_qty2,
527 net_price,
528 extended_price,
529 price_um,
530 qc_grade_wanted,
531 match_type,
532 text_code,
533 trans_cnt,
534 exported_date,
535 last_update_date,
536 created_by,
537 creation_date,
538 last_updated_by,
539 last_update_login,
540 delete_mark,
541 contract_value,
542 contract_start_date,
543 contract_end_date,
544 std_qty,
545 max_rels_qty,
546 invalid_ind,
547 po_release_id,
548 release_num,
549 source_shipment_id,
550 line_no
551 )
552 VALUES
553 ( nvl(cpg_potrans.nextval,0),
554 nvl(substrb(v_type_lookup_code,1,10),' '),
555 v_gemms_orgn_code,
556 nvl(v_po_no,' '),
557 nvl(p_po_header_id,0),
558 nvl(p_po_line_id,0),
559 nvl(p_po_line_location_id,0),
560 0, /*distribution id*/
564 NULL, /*bpo_id T.Ricci added NULL,*/
561 nvl(v_closed_code, '0'), /*po_status,*/
562 nvl(v_buyer_code,' '), /*buyer_code, */
563 0, /*po_id,*/
565 nvl(v_release_num, 0), /*bpo_release_number,*/
566 nvl(v_vendor_site_id,1),
567 nvl(v_vendor_site_id,1),
568 nvl(v_approved_date, sysdate), /*po_date, PKU - BUG 1785704 */
569 1, /*po_type, */
570 NULL, /*from_whse T.Ricci added NULL,*/
571 nvl(substrb(v_location_code,1,4),' '), /*to_whse, */
572 ' ', /*recv_desc,*/
573 NULL, /*recv_loct T.Ricci added NULL,*/
574 NULL, /*recvaddr_id T.Ricci added NULL,*/
575 NULL, /*ship_mthd T.Ricci added NULL,
576 T.Ricci 11/10/98 added v_shipper_code to retreive correct shipper_code*/
577 v_shipper_code, /*shipper_code,*/
578 v_freight_terms_lookup_code, /*of_frtbill_mthd,
579 T.Ricci 10/15/98 added terms_name for terms code fix for OPM 11.0*/
580 v_terms_name, /*PKU - Bug 2195821 removed nvl(of_terms_code,'20')*/
581 nvl(substrb(v_currency_code,1,4),'USD'),/*billing_currency,*/
582 /* 1, purchase_exchange_rate,*/
583 nvl(v_exchange_rate,1), /* HW BUG:1107267 purchase_exchang_rate */
584 0, /*mul_div_sign,*/
585 0, /*currency_bght_fwd,*/
586 NULL, /*pohold_code T.Ricci added NULL,*/
587 v_cancellation_code, /*cancellation_code TR remove nvl,
588 T.Ricci 11/10/98 added v_fob_code to retreive correct fob_code*/
589 v_fob_code, /*fob_code,*/
590 NULL, /*icpurch_class T.Ricci add NULL, */
591 ' ', /*vendso_no, */
592 NULL, /*project_no T.Ricci added NULL, */
593 nvl(v_need_by_date, sysdate), /*requested_dlvdate, */
594 nvl(v_promised_date, sysdate), /*sched_shipdate, */
595 nvl(v_last_accept_date, sysdate), /*required_dlvdate, */
596 nvl(v_db_promised_date, sysdate), /*agreed_dlvdate, */
597 nvl(v_printed_date, sysdate), /*date_printed, */
598 /* nvl(fnd_profile.value('SY$ZERODATE'),sysdate), expedite_date, */
599 nvl(bind_date,sysdate), /* expedite_date H. Wahdani */
600 nvl(v_revision_num,0), /*revision_count, */
601 0, /*in_use, */
602 nvl(v_print_count,0), /*print_count, */
603 0, /*v_line_id, --line_id, */
604 0, /*bpo_line_id,*/
605 0, /*apinv_line_id,*/
606 nvl(v_item_no,0), /*item_no, */
607 NULL, /*generic_id T.Ricci added NULL,*/
608 nvl(v_item_desc,'NONE'), /*item_desc, */
609 nvl(v_quantity,0), /*order_qty1,*/
610 0, /*order_qty2, */
611 v_order_um1,/** MC BUG# 1554088 nvl(substrb(v_unit_meas_lookup_code,1,4),' '),**/ /*order_um1, */
612 NULL, /*order_um2 T.Ricci added NULL,*/
613 0, /*received_qty1, */
614 0, /*received_qty2,*/
615 nvl(v_price_override, 0), /*net_price,*/
616 nvl(v_quantity*v_price_override,0), /*extended_price, */
617 v_order_um1,/** MC BUG# 1554088 nvl(substrb(v_unit_meas_lookup_code,1,4),' '),**/ /*price_um, */
618 v_qc_grade_wanted, /*qc_grade_wanted,*/
619 3, /*match_type, */
620 NULL, /*text_code T.Ricci added NULL, */
621 1, /*trans_cnt, */
622 to_date('01/01/1970', 'DD/MM/YYYY'), /*exported_date,H. Wahdani, added 19 */
623 nvl(v_last_update_date,sysdate), /*last_update_date,*/
624 nvl(v_created_by, 0), /*created_by, */
625 nvl(v_creation_date,sysdate), /*creation_date, */
626 nvl(v_last_updated_by, 0), /*last_updated_by, */
627 nvl(v_last_update_login, 0), /*last_update_login, */
628 0, /*delete_mark,*/
629 nvl(v_blanket_total_amount, 0), /*contract_value,*/
630 nvl(v_start_date, v_approved_date), /*contract_start_date,*/
631 nvl(v_end_date, to_date('31/12/2010', 'DD/MM/YYYY')),
632 /*contract_end_date,*/
633 nvl(v_quantity,0), /*std_qty,*/
634 nvl(v_quantity,0), /*max_rels_qty,*/
635 'N', /*invalid_ind*/
636 nvl(v_po_release_id,0), /*po_release_id,*/
637 nvl(v_release_num, 0), /*release_num,*/
638 nvl(v_source_shipment_id, 0), /*source_shipment_id*/
639 nvl(v_shipment_num, 0) /*shipment_num*/
640 );
641
642
643 OPEN c_checkpll_cur(p_po_header_id, p_po_line_id, p_po_line_location_id);
644 FETCH c_checkpll_cur
645 INTO r_checkpll_rec;
646
647 IF c_checkpll_cur%NOTFOUND THEN
648 INSERT INTO cpg_oragems_mapping
649 ( po_header_id,
650 po_line_id,
651 po_line_location_id,
652 po_no,
653 po_status,
654 time_stamp,
655 po_release_id,
656 release_num,
657 transaction_type,
658 last_update_login,
659 last_update_date,
660 last_updated_by,
661 created_by,
662 creation_date
663 )
664 VALUES
665 ( p_po_header_id,
666 p_po_line_id,
667 p_po_line_location_id,
668 nvl(v_po_no,' '),
669 nvl(v_closed_code, 'OPEN'),
670 sysdate,
671 v_po_release_id,
672 v_release_num,
673 v_type_lookup_code,
674 nvl(v_last_update_login, 0),
675 sysdate,
676 nvl(v_last_updated_by, 0),
677 nvl(v_created_by, 0),
678 sysdate
679 );
680
681 ELSIF (v_closed_code = 'FINALLY CLOSED') THEN
682 UPDATE cpg_oragems_mapping
683 SET po_status = v_closed_code,
684 time_stamp = sysdate,
685 last_update_date = sysdate
686 WHERE CURRENT of c_checkpll_cur;
687
688 ELSIF (v_cancel_flag ='Y') THEN
689 UPDATE cpg_oragems_mapping
690 SET po_status = 'CANCELLED',
691 time_stamp = sysdate,
692 last_update_date = sysdate
693 WHERE CURRENT of c_checkpll_cur;
694
695 END IF;
696
697 CLOSE c_checkpll_cur;
698
699 /** MC BUG# 1625573 **/
700 /** changed bug no. from 1527076 to 1625573 **/
701 /** cpg_purchasing_interface should have po_no column with value = OPM PO. Right
702 now
703 it is fetching from segment1 from po_headers_all but for migrated PO's segment1
704 can be different than OPM PO. so pick up the correct po no from cpg_oragems_mapp
705 ing table **/
706
707 /* Uday Phadtare B2038851 following update statement commented */
708
709 /* UPDATE cpg_purchasing_interface a
710 SET a.po_no = ( SELECT b.po_no from cpg_oragems_mapping b
711 WHERE
712 b.po_header_id = a.po_header_id AND
713 b.po_line_id = a.po_line_id AND
714 b.po_line_location_id = a.po_line_location_id ) ; */
715
716
717 END IF; /* Blanket and release_id = 0 and inventory item condition */
718
719 EXCEPTION
720 WHEN OTHERS THEN
721 err_num := SQLCODE;
722 err_msg := SUBSTRB(SQLERRM, 1, 100);
723 RAISE_APPLICATION_ERROR(-20000, err_msg);
724 END insert_rec;
725
726 END GML_PO_INTERFACE;