[Home] [Help]
PACKAGE BODY: APPS.PON_VALIDATE_SHIPMENTS_INT
Source
1 PACKAGE BODY pon_validate_shipments_int as
2 -- $Header: pon_validate_shipments_int.plb 120.5.12020000.6 2013/05/22 05:55:38 nrayi noship $
3
4 -- These will be used for debugging the code
5 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_VALIDATE_PAYMENTS_INT';
7 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
8
9
10 /** =============Start declaration of private functions and procedures =========*/
11 PROCEDURE print_debug_log(p_module IN VARCHAR2,
12 p_message IN VARCHAR2);
13
14 PROCEDURE print_error_log(p_module IN VARCHAR2,
15 p_message IN VARCHAR2);
16
17 PROCEDURE val_quantity_based(p_source VARCHAR2,
18 p_batch_Id NUMBER,
19 p_auction_header_id number);
20
21 PROCEDURE val_price_breaks(p_source VARCHAR2,
22 p_batch_Id NUMBER,
23 p_auction_header_id number);
24
25
26
27 --
28 PROCEDURE validate_creation (p_source VARCHAR2, p_batch_Id NUMBER,p_auction_header_id number) IS
29 l_user_id NUMBER;
30 l_login_id NUMBER;
31 l_exp_date DATE;
32 l_interface_type VARCHAR2(15);
33 l_module CONSTANT VARCHAR2(32) := 'VALIDATE_CREATION';
34 l_progress VARCHAR2(200);
35 p_request_id NUMBER;
36 l_price_Tiers_indicator pon_auction_headers_all.price_tiers_indicator%TYPE;
37 l_is_fed VARCHAR2(1);
38 l_doctype_id NUMBER;
39
40
41 BEGIN
42
43 l_user_id := fnd_global.user_id;
44 l_login_id := fnd_global.login_id;
45 l_exp_date := SYSDATE + 7;
46 l_interface_type := 'NEGSHIPMTUPLOAD';
47 p_request_id := 0;
48
49
50 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
51 print_debug_log(l_module,'Before insert all valiations of validate_creation for p_batch_id = '||p_batch_id);
52 END IF;
53
54 SELECT doctype_id INTO l_doctype_id FROM pon_auction_headers_all WHERE auction_Header_id = p_auction_header_id;
55
56 BEGIN
57 SELECT
58 pon_auc_doctype_rules.DEFAULT_VALUE INTO l_is_fed
59 FROM PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules,
60 PON_AUC_BIZRULES pon_auc_bizrules
61 WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
62 AND pon_auc_doctype_rules.DOCTYPE_ID = l_doctype_id
63 AND pon_auc_bizrules.NAME = 'FEDERAL_NEGOTIATION';
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 l_is_fed := 'N';
68 END;
69
70 SELECT price_tiers_indicator INTO l_price_tiers_indicator
71 FROM pon_auction_headers_all WHERE auction_header_id = p_auction_header_id;
72
73 IF(l_is_fed = 'Y') THEN
74 UPDATE pon_auc_price_breaks_interface pb_int
75 SET auction_line_number =
76 (SELECT line_number
77 FROM pon_auction_item_prices_all
78 WHERE LINE_NUM_DISPLAY = pb_int.DOCUMENT_DISP_LINE_NUMBER
79 AND auction_header_id = p_auction_header_id
80 )
81 WHERE batch_id = p_batch_id;
82
83 ELSE
84 UPDATE pon_auc_price_breaks_interface pb_int
85 SET auction_line_number =
86 (SELECT line_number
87 FROM pon_auction_item_prices_all
88 WHERE DOCUMENT_DISP_LINE_NUMBER = pb_int.DOCUMENT_DISP_LINE_NUMBER
89 AND auction_header_id = p_auction_header_id
90 )
91 WHERE batch_id = p_batch_id;
92 END IF;
93
94 -- Validate Quantity based price tiers
95
96 IF(l_price_tiers_indicator = 'QUANTITY_BASED') THEN
97 val_quantity_based(p_source, p_batch_Id,p_auction_header_id);
98
99 END IF;
100
101 -- Validate price breaks.
102
103 IF(l_price_tiers_indicator = 'PRICE_BREAKS') THEN
104
105 val_price_breaks(p_source , p_batch_Id ,p_auction_header_id );
106 END IF;
107
108 END validate_creation;
109 --
110
111 /*======================================================================
112 PROCEDURE: UPDATE_SHIPMENT_NUMBER PRIVATE
113 PARAMETERS:
114 COMMENT : This procedure is used to update the shipment number
115 ======================================================================*/
116
117
118
119 PROCEDURE update_shipment_number(p_batch_id NUMBER, p_auction_header_id NUMBER)
120 AS
121
122 CURSOR c_interface_lines
123 IS
124 SELECT DISTINCT auction_line_number
125 FROM pon_auc_price_breaks_interface
126 WHERE BATCH_ID = p_batch_Id
127 AND AUCTION_HEADER_ID = p_auction_header_id;
128
129 CURSOR c_interface_ship_count(l_line_number number)
130 IS
131 SELECT interface_line_id
132 FROM pon_auc_price_breaks_interface
133 WHERE BATCH_ID =p_batch_Id
134 AND AUCTION_HEADER_ID = p_auction_header_id
135 AND auction_line_number = l_line_number
136 AND Nvl(shipment_number,-1) NOT IN(SELECT shipment_number FROM pon_auction_shipments_all
137 WHERE auction_header_id = p_auction_header_id
138 AND line_number = l_line_number );
139
140
141 c_interface_lines_rec c_interface_lines%ROWTYPE;
142 c_interface_ship_count_rec c_interface_ship_count%ROWTYPE;
143
144 l_shipment_number NUMBER;
145
146 BEGIN
147
148 OPEN c_interface_lines;
149 LOOP
150 FETCH c_interface_lines INTO c_interface_lines_rec;
151 EXIT
152 WHEN c_interface_lines%NOTFOUND;
153 BEGIN
154
155 SELECT Nvl(max(shipment_number),-1) INTO l_shipment_number
156 FROM pon_auction_shipments_all
157 WHERE auction_header_id = p_auction_header_id
158 AND line_number = c_interface_lines_rec.auction_line_number;
159
160 OPEN c_interface_ship_count(c_interface_lines_rec.auction_line_number);
161 LOOP
162 FETCH c_interface_ship_count INTO c_interface_ship_count_rec;
163 EXIT
164 WHEN c_interface_ship_count%NOTFOUND;
165 BEGIN
166 l_shipment_number:=l_shipment_number+1;
167 UPDATE pon_auc_price_breaks_interface SET shipment_number = l_shipment_number
168 WHERE batch_id= p_batch_id
169 AND auction_header_id = p_auction_header_id
170 AND interface_line_id = c_interface_ship_count_rec.interface_line_id;
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 NULL;
175 END;
176 END LOOP;
177 CLOSE c_interface_ship_count;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 NULL;
182 END;
183
184 END LOOP;
185 CLOSE c_interface_lines;
186
187
188
189 END update_shipment_number;
190
191
192
193 PROCEDURE copy_shipments_from_int_to_txn(
194 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
195 p_auction_header_id IN NUMBER,
196 x_result OUT NOCOPY VARCHAR2, -- S: Success, E: failure
197 x_error_code OUT NOCOPY VARCHAR2,
198 x_error_message OUT NOCOPY VARCHAR2)
199 IS
200 l_module CONSTANT VARCHAR2(32) := 'COPY_SHIPMENTS_FROM_INT_TO_TXN';
201 l_progress varchar2(200);
202
203
204 BEGIN
205
206 x_result := 'S';
207
208 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
209 print_debug_log(l_module,'COPY_SHIPMENTS_FROM_INT_TO_TXN START p_batch_id = '||p_batch_id);
210 END IF;
211
212 update_shipment_number(p_batch_id, p_auction_header_id);
213
214 -- Update lines table with values in the interface table
215 MERGE INTO pon_auction_shipments_all sl
216 USING
217 (SELECT
218 pasi.auction_header_id,
219 pasi.auction_line_number,
220 pasi.document_disp_line_number,
221 pasi.batch_id,
222 pasi.interface_line_id,
223 pasi.shipment_type,
224 pasi.shipment_number,
225 pasi.ship_to_organization_id,
226 pasi.ship_to_location_id,
227 pasi.quantity,
228 pasi.price,
229 pasi.min_quantity,
230 pasi.max_quantity,
231 pasi.org_id,
232 pasi.effective_start_date,
233 pasi.effective_end_date,
234 paip.price_break_type,
235 pah.price_tiers_indicator
236 FROM pon_auc_price_breaks_interface pasi,
237 pon_auction_item_prices_all paip,
238 pon_auction_headers_all pah
239 WHERE pasi.batch_id = p_batch_id
240 AND pasi.auction_header_id = p_auction_header_id
241 AND pasi.auction_header_id = paip.auction_header_id
242 AND pasi.auction_line_number = paip.line_number
243 AND pah.auction_header_id = pasi.auction_header_id
244 AND Nvl(paip.clm_info_flag,'N') = 'N'
245
246 ) ship
247 ON (sl.shipment_number = ship.shipment_number
248 AND sl.line_number = ship.auction_line_number
249 AND sl.auction_header_id = ship.auction_header_id)
250 WHEN MATCHED THEN
251 UPDATE SET
252 sl.shipment_type = ship.shipment_type,
253 sl.ship_to_organization_id = decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.ship_to_organization_id,null),
254 sl.ship_to_location_id = decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.ship_to_location_id,null),
255 sl.quantity = decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.quantity,ship.min_quantity),
256 sl.PRICE = ship.price,
257 sl.max_quantity = decode(ship.price_tiers_indicator, 'QUANTITY_BASED',ship.max_quantity,null),
258 sl.org_id = ship.org_id,
259 sl.effective_start_date = decode(ship.price_break_type, 'NON-CUMULATIVE',ship.effective_start_date,null),
260 sl.effective_end_date = decode(ship.price_break_type, 'NON-CUMULATIVE',ship.effective_end_date,null),
261 sl.last_update_date = sysdate,
262 sl.last_updated_by = fnd_global.user_id,
263 sl.last_update_login = fnd_global.login_id
264 WHEN NOT MATCHED THEN
265 INSERT (
266 AUCTION_HEADER_ID,
267 LINE_NUMBER,
268 SHIPMENT_NUMBER,
269 shipment_type,
270 ship_to_organization_id,
271 ship_to_location_id,
272 quantity,
273 org_id,
274 PRICE, -- Auction Currency price
275 effective_start_date,
276 effective_end_date,
277 max_quantity,
278 has_price_differentials_flag,
279 CREATION_DATE,
280 CREATED_BY ,
281 LAST_UPDATE_DATE ,
282 LAST_UPDATED_BY ,
283 LAST_UPDATE_LOGIN
284 )
285 VALUES (
286 ship.auction_header_id,
287 ship.auction_line_number,
288 ship.shipment_number,
289 ship.shipment_type,
290 decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.ship_to_organization_id,null),
291 decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.ship_to_location_id,null),
292 decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.quantity,ship.min_quantity),
293 ship.org_id,
294 ship.price,
295 decode(ship.price_break_type, 'NON-CUMULATIVE',ship.effective_start_date,null),
296 decode(ship.price_break_type, 'NON-CUMULATIVE',ship.effective_end_date,null),
297 decode(ship.price_tiers_indicator, 'QUANTITY_BASED',ship.max_quantity,null),
298 'N',
299 SYSDATE ,
300 fnd_global.user_id ,
301 SYSDATE ,
302 fnd_global.user_id ,
303 fnd_global.login_id
304 ) ;
305
306 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
307 l_progress := 'Merge into pon_auction_shipments is successful for batch_id = '||p_batch_id;
308 END if;
309
310
311 -- Update lines table
312 UPDATE pon_auction_item_prices_all
313 SET has_shipments_flag= 'Y'
314 WHERE auction_header_id= p_auction_header_id
315 AND line_number IN(SELECT line_number FROM pon_auc_price_breaks_interface
316 WHERE batch_id = p_batch_id
317 AND auction_header_id = p_auction_header_id);
318
319
320 -- Clear the interface tables
321 delete from pon_auc_price_breaks_interface where batch_id = p_batch_id;
322
323
324 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
325 l_progress := 'delete from pon_auc_shipments_interface completed for p_batch_id = '||p_batch_id;
326 END if;
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 x_result := 'E';
331 x_error_code := SQLCODE;
332 x_error_message := SUBSTR(SQLERRM, 1, 100);
333 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
334 print_error_log(l_module, 'EXCEPTION - l_progress='||l_progress||' x_result=' || x_result || ' x_error_code=' || x_error_code || ' x_error_message=' || x_error_message || ' SQLERRM=' || SQLERRM);
335 END if;
336 END copy_shipments_from_int_to_txn;
337
338
339
340 /*======================================================================
341 PROCEDURE: VAL_PRICE_BREAKS PRIVATE
342 PARAMETERS:
343 COMMENT : This procedure is used to validate the price breaks
344 ======================================================================*/
345
346
347
348 PROCEDURE val_price_breaks(p_source VARCHAR2, p_batch_Id NUMBER,p_auction_header_id number) IS
349 l_user_id NUMBER;
350 l_login_id NUMBER;
351 l_exp_date DATE;
352 l_interface_type VARCHAR2(15);
353 l_module CONSTANT VARCHAR2(32) := 'VALIDATE_CREATION';
354 l_progress VARCHAR2(200);
355 p_request_id NUMBER;
356 l_price_Tiers_indicator pon_auction_headers_all.price_tiers_indicator%TYPE;
357
358
359 BEGIN
360
361 l_user_id := fnd_global.user_id;
362 l_login_id := fnd_global.login_id;
363 l_exp_date := SYSDATE + 7;
364 l_interface_type := 'NEGSHIPMTUPLOAD';
365 p_request_id := 0;
366
367
368 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
369 print_debug_log(l_module,'Validating price breaks for p_batch_id = '||p_batch_id);
370 END IF;
371
372 SELECT price_tiers_indicator INTO l_price_tiers_indicator
373 FROM pon_auction_headers_all WHERE auction_header_id = p_auction_header_id;
374
375 UPDATE pon_auc_price_breaks_interface pb_int
376 SET SHIP_TO_ORGANIZATION_ID =
377 (SELECT ORGANIZATION_ID
378 FROM org_organization_definitions
379 WHERE organization_code = pb_int.SHIP_TO_ORGANIZATION
380 )
381 WHERE batch_id = p_batch_id
382 AND SHIP_TO_ORGANIZATION IS NOT NULL;
383 /* bug#16839410 user will not be able to enter tab in the ship_to_location value so replacing the tab using fnd_global.local_chr */
384 UPDATE pon_auc_price_breaks_interface pb_int
385 SET ship_to_location_id =
386 (SELECT MAX(location_id)
387 FROM po_ship_to_loc_org_v po_v
388 WHERE replace(po_v.location_code,fnd_global.local_chr(9),'') = pb_int.ship_to_location
389 )
390 WHERE batch_id = p_batch_id
391 AND ship_to_location IS NOT null;
392
393
394 INSERT ALL
395 -- PRICE BREAK EFFECTIVE START DATE SHOULD BE BEFORE EFFECTIVE END DATE
396 WHEN ( sel_effective_start_date IS NOT NULL
397 AND sel_effective_end_date IS NOT NULL
398 AND sel_effective_end_date < sel_effective_start_date
399 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
400 INTO PON_INTERFACE_ERRORS
401 (
402 COLUMN_NAME ,
403 INTERFACE_TYPE ,
404 ERROR_MESSAGE_NAME,
405 REQUEST_ID ,
406 BATCH_ID ,
407 ENTITY_TYPE ,
408 AUCTION_HEADER_ID ,
409 LINE_NUMBER ,
410 SHIPMENT_NUMBER ,
411 EXPIRATION_DATE ,
412 TOKEN1_NAME ,
413 TOKEN1_VALUE ,
414 interface_line_id ,
415 CREATED_BY ,
416 CREATION_DATE ,
417 LAST_UPDATED_BY ,
418 LAST_UPDATE_DATE ,
419 LAST_UPDATE_LOGIN
420 )
421 VALUES
422 (
423 pon_auction_pkg.getMessage('PON_AUC_EFFEC_TO'),
424 l_interface_type , --INTERFACE_TYPE
425 'PON_AUCTS_EFFC_END_BEF_START', -- ERROR_MESSAGE_NAME
426 p_request_id , -- REQUEST_ID
427 p_batch_id , --BATCH_ID
428 g_auction_pbs_type , -- ENTITY_TYPE
429 p_auction_header_id , -- AUCTION_HEADER_ID
430 sel_line_number , -- LINE_NUMBER
431 sel_shipment_number , -- SHIPMENT_NUMBER
432 l_exp_date , -- EXPIRATION_DATE
433 'LINENUM' , -- TOKEN1_NAME
434 sel_document_disp_line_number , -- TOKEN1_VALUE
435 interface_line_number ,
436 l_user_id , -- CREATED_BY
437 sysdate , -- CREATION_DATE
438 l_user_id , -- LAST_UPDATED_BY
439 sysdate , -- LAST_UPDATE_DATE
440 l_login_id -- LAST_UPDATE_LOGIN
441 )
442 -- Validate the Line Number
443 WHEN ( sel_line_number IS NULL) THEN
444 INTO PON_INTERFACE_ERRORS
445 (
446 COLUMN_NAME ,
447 INTERFACE_TYPE ,
448 ERROR_MESSAGE_NAME,
449 REQUEST_ID ,
450 BATCH_ID ,
451 ENTITY_TYPE ,
452 AUCTION_HEADER_ID ,
453 LINE_NUMBER ,
454 SHIPMENT_NUMBER ,
455 EXPIRATION_DATE ,
456 TOKEN1_NAME ,
457 TOKEN1_VALUE ,
458 interface_line_id ,
459 CREATED_BY ,
460 CREATION_DATE ,
461 LAST_UPDATED_BY ,
462 LAST_UPDATE_DATE ,
463 LAST_UPDATE_LOGIN
464 )
465 VALUES
466 (
467 pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
468 l_interface_type , --INTERFACE_TYPE
469 'PON_INVALID_LINE_NUM', -- ERROR_MESSAGE_NAME
470 p_request_id , -- REQUEST_ID
471 p_batch_id , --BATCH_ID
472 g_auction_pbs_type , -- ENTITY_TYPE
473 p_auction_header_id , -- AUCTION_HEADER_ID
474 sel_line_number , -- LINE_NUMBER
475 sel_shipment_number , -- SHIPMENT_NUMBER
476 l_exp_date , -- EXPIRATION_DATE
477 null , -- TOKEN1_NAME
478 null , -- TOKEN1_VALUE
479 interface_line_number ,
480 l_user_id , -- CREATED_BY
481 sysdate , -- CREATION_DATE
482 l_user_id , -- LAST_UPDATED_BY
483 sysdate , -- LAST_UPDATE_DATE
484 l_login_id -- LAST_UPDATE_LOGIN
485 )
486 -- Validate Agreement Release Quantity for price breaks
487 /* WHEN ( Nvl(sel_agreement_rel_qty,'NONE') NOT IN (pon_auction_pkg.getmessage('PON_AUC_NON_CUMULATIVE'),pon_auction_pkg.getmessage('PON_AUC_CUMULATIVE'))
488 AND nvl(sel_clm_info_flag,'N') = 'N')
489 THEN
490 INTO PON_INTERFACE_ERRORS
491 (
492 COLUMN_NAME ,
493 INTERFACE_TYPE ,
494 ERROR_MESSAGE_NAME,
495 REQUEST_ID ,
496 BATCH_ID ,
497 ENTITY_TYPE ,
498 AUCTION_HEADER_ID ,
499 LINE_NUMBER ,
500 SHIPMENT_NUMBER ,
501 EXPIRATION_DATE ,
502 TOKEN1_NAME ,
503 TOKEN1_VALUE ,
504 interface_line_id ,
505 CREATED_BY ,
506 CREATION_DATE ,
507 LAST_UPDATED_BY ,
508 LAST_UPDATE_DATE ,
509 LAST_UPDATE_LOGIN
510 )
511 VALUES
512 (
513 pon_auction_pkg.getMessage('PON_AGREEMENT_RELEASE_QUANTITY'),
514 l_interface_type , --INTERFACE_TYPE
515 'PON_INVALID_AGR_REL_QTY', -- ERROR_MESSAGE_NAME
516 p_request_id , -- REQUEST_ID
517 p_batch_id , --BATCH_ID
518 g_auction_pbs_type , -- ENTITY_TYPE
519 p_auction_header_id , -- AUCTION_HEADER_ID
520 sel_line_number , -- LINE_NUMBER
521 sel_shipment_number , -- SHIPMENT_NUMBER
522 l_exp_date , -- EXPIRATION_DATE
523 null , -- TOKEN1_NAME
524 null , -- TOKEN1_VALUE
525 interface_line_number ,
526 l_user_id , -- CREATED_BY
527 sysdate , -- CREATION_DATE
528 l_user_id , -- LAST_UPDATED_BY
529 sysdate , -- LAST_UPDATE_DATE
530 l_login_id -- LAST_UPDATE_LOGIN
531 )*/
532 -- Validate Response Type for price breaks
533 WHEN Nvl(sel_price_break_type,'NONE') = 'NONE'
534 AND nvl(sel_clm_info_flag,'N') = 'N'
535 THEN
536 INTO PON_INTERFACE_ERRORS
537 (
538 COLUMN_NAME ,
539 INTERFACE_TYPE ,
540 ERROR_MESSAGE_NAME,
541 REQUEST_ID ,
542 BATCH_ID ,
543 ENTITY_TYPE ,
544 AUCTION_HEADER_ID ,
545 LINE_NUMBER ,
546 SHIPMENT_NUMBER ,
547 EXPIRATION_DATE ,
548 TOKEN1_NAME ,
549 TOKEN1_VALUE ,
550 interface_line_id ,
551 CREATED_BY ,
552 CREATION_DATE ,
553 LAST_UPDATED_BY ,
554 LAST_UPDATE_DATE ,
555 LAST_UPDATE_LOGIN
556 )
557 VALUES
558 (
559 pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
560 l_interface_type , --INTERFACE_TYPE
561 'PON_AUC_BAD_PBTYPE_SHIPS', -- ERROR_MESSAGE_NAME
562 p_request_id , -- REQUEST_ID
563 p_batch_id , --BATCH_ID
564 g_auction_pbs_type , -- ENTITY_TYPE
565 p_auction_header_id , -- AUCTION_HEADER_ID
566 sel_line_number , -- LINE_NUMBER
567 sel_shipment_number , -- SHIPMENT_NUMBER
568 l_exp_date , -- EXPIRATION_DATE
569 'LINENUMBER' , -- TOKEN1_NAME
570 sel_document_disp_line_number , -- TOKEN1_VALUE
571 interface_line_number ,
572 l_user_id , -- CREATED_BY
573 sysdate , -- CREATION_DATE
574 l_user_id , -- LAST_UPDATED_BY
575 sysdate , -- LAST_UPDATE_DATE
576 l_login_id -- LAST_UPDATE_LOGIN
577 )
578
579 -- SHIP TO LOCATON AND SHIP TO ORG SHOULD BE PROPER
580 -- THE SHIP_TO_LOCATION AND SHIP_TO_ORG IF BOTH ARE ENTERED THEN EITHER
581 -- 1. The Ship_to_location should belong to the Ship_to_organization
582 -- 2. The Ship_to_location should be a global location (inventory_organization_id is null)
583 WHEN (
584 nvl(sel_clm_info_flag,'N') = 'N'
585 AND sel_ship_to_organization_id IS NOT NULL
586 AND sel_ship_to_location_id IS NOT NULL
587 AND NOT EXISTS
588 (SELECT l.INVENTORY_ORGANIZATION_ID
589 FROM HR_LOCATIONS_ALL L
590 WHERE SYSDATE < NVL(L.INACTIVE_DATE, SYSDATE + 1)
591 AND NVL(L.SHIP_TO_SITE_FLAG,'N') = 'Y'
592 AND L.LOCATION_ID = sel_ship_to_location_id
593 AND NVL (L.INVENTORY_ORGANIZATION_ID, sel_ship_to_organization_id) = sel_ship_to_organization_id
594 ) ) THEN
595 INTO PON_INTERFACE_ERRORS
596 (
597 COLUMN_NAME ,
598 INTERFACE_TYPE ,
599 ERROR_MESSAGE_NAME,
600 REQUEST_ID ,
601 BATCH_ID ,
602 ENTITY_TYPE ,
603 AUCTION_HEADER_ID ,
604 LINE_NUMBER ,
605 SHIPMENT_NUMBER ,
606 EXPIRATION_DATE ,
607 TOKEN1_NAME ,
608 TOKEN1_VALUE ,
609 interface_line_id ,
610 CREATED_BY ,
611 CREATION_DATE ,
612 LAST_UPDATED_BY ,
613 LAST_UPDATE_DATE ,
614 LAST_UPDATE_LOGIN
615 )
616 VALUES
617 (
618 pon_auction_pkg.getMessage('PON_SHIP_TO_LOCATION'),
619 l_interface_type , --INTERFACE_TYPE
620 'PON_AUC_SHIP_TO_MATCHING_ERR', -- ERROR_MESSAGE_NAME
621 p_request_id , -- REQUEST_ID
622 p_batch_id , --BATCH_ID
623 g_auction_pbs_type , -- ENTITY_TYPE
624 p_auction_header_id , -- AUCTION_HEADER_ID
625 sel_line_number , -- LINE_NUMBER
626 sel_shipment_number , -- SHIPMENT_NUMBER
627 l_exp_date , -- EXPIRATION_DATE
628 'LINENUM' , -- TOKEN1_NAME
629 sel_document_disp_line_number , -- TOKEN1_VALUE
630 interface_line_number ,
631 l_user_id , -- CREATED_BY
632 sysdate , -- CREATION_DATE
633 l_user_id , -- LAST_UPDATED_BY
634 sysdate , -- LAST_UPDATE_DATE
635 l_login_id -- LAST_UPDATE_LOGIN
636 )
637 -- PRICE BREAK SHOULD NOT BE EMPTY
638 -- ONLY PRICE SHOULD NOT BE ENTERED
639 WHEN ( sel_ship_to_organization_id IS NULL
640 AND sel_ship_to_location_id IS NULL
641 AND sel_quantity IS NULL
642 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
643 INTO PON_INTERFACE_ERRORS
644 (
645 INTERFACE_TYPE ,
646 ERROR_MESSAGE_NAME,
647 REQUEST_ID ,
648 BATCH_ID ,
649 ENTITY_TYPE ,
650 AUCTION_HEADER_ID ,
651 LINE_NUMBER ,
652 SHIPMENT_NUMBER ,
653 EXPIRATION_DATE ,
654 TOKEN1_NAME ,
655 TOKEN1_VALUE ,
656 interface_line_id ,
657 CREATED_BY ,
658 CREATION_DATE ,
659 LAST_UPDATED_BY ,
660 LAST_UPDATE_DATE ,
661 LAST_UPDATE_LOGIN
662 )
663 VALUES
664 (
665 l_interface_type , --INTERFACE_TYPE
666 NVL2 (sel_price, 'PON_AUCTS_PB_PRICE_ONLY', 'PON_AUCTS_SHIPMENT_EMPTY'), -- ERROR_MESSAGE_NAME
667 p_request_id , -- REQUEST_ID
668 p_batch_id , --BATCH_ID
669 g_auction_pbs_type , -- ENTITY_TYPE
670 p_auction_header_id , -- AUCTION_HEADER_ID
671 sel_line_number , -- LINE_NUMBER
672 sel_shipment_number , -- SHIPMENT_NUMBER
673 l_exp_date , -- EXPIRATION_DATE
674 'LINENUM' , -- TOKEN1_NAME
675 sel_document_disp_line_number , -- TOKEN1_VALUE
676 interface_line_number ,
677 l_user_id , -- CREATED_BY
678 sysdate , -- CREATION_DATE
679 l_user_id , -- LAST_UPDATED_BY
680 sysdate , -- LAST_UPDATE_DATE
681 l_login_id -- LAST_UPDATE_LOGIN
682 )
683 -- quantity should not be empty or negative
684 WHEN ( sel_quantity IS NOT NULL
685 AND sel_quantity < 0
686 AND sel_quantity <> g_null_int
687 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
688 INTO PON_INTERFACE_ERRORS
689 (
690 COLUMN_NAME ,
691 INTERFACE_TYPE ,
692 ERROR_MESSAGE_NAME,
693 REQUEST_ID ,
694 BATCH_ID ,
695 ENTITY_TYPE ,
696 AUCTION_HEADER_ID ,
697 LINE_NUMBER ,
698 SHIPMENT_NUMBER ,
699 EXPIRATION_DATE ,
700 TOKEN1_NAME ,
701 TOKEN1_VALUE ,
702 interface_line_id ,
703 CREATED_BY ,
704 CREATION_DATE ,
705 LAST_UPDATED_BY ,
706 LAST_UPDATE_DATE ,
707 LAST_UPDATE_LOGIN
708 )
709 VALUES
710 (
711 pon_auction_pkg.getMessage('PON_AUCTS_QUANTITY'),
712 l_interface_type , --INTERFACE_TYPE
713 'PON_AUCTS_PB_QUANTITY_POSITIVE', -- ERROR_MESSAGE_NAME
714 p_request_id , -- REQUEST_ID
715 p_batch_id , --BATCH_ID
716 g_auction_pbs_type , -- ENTITY_TYPE
717 p_auction_header_id , -- AUCTION_HEADER_ID
718 sel_line_number , -- LINE_NUMBER
719 sel_shipment_number , -- SHIPMENT_NUMBER
720 l_exp_date , -- EXPIRATION_DATE
721 'LINENUM' , -- TOKEN1_NAME
722 sel_document_disp_line_number , -- TOKEN1_VALUE
723 interface_line_number ,
724 l_user_id , -- CREATED_BY
725 sysdate , -- CREATION_DATE
726 l_user_id , -- LAST_UPDATED_BY
727 sysdate , -- LAST_UPDATE_DATE
728 l_login_id -- LAST_UPDATE_LOGIN
729 )
730
731 -- the price break price should be positive
732 WHEN ( sel_price < 0 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
733 INTO PON_INTERFACE_ERRORS
734 (
735 COLUMN_NAME ,
736 INTERFACE_TYPE ,
737 ERROR_MESSAGE_NAME,
738 REQUEST_ID ,
739 BATCH_ID ,
740 ENTITY_TYPE ,
741 AUCTION_HEADER_ID ,
742 LINE_NUMBER ,
743 SHIPMENT_NUMBER ,
744 EXPIRATION_DATE ,
745 TOKEN1_NAME ,
746 TOKEN1_VALUE ,
747 interface_line_id ,
748 CREATED_BY ,
749 CREATION_DATE ,
750 LAST_UPDATED_BY ,
751 LAST_UPDATE_DATE ,
752 LAST_UPDATE_LOGIN
753 )
754 VALUES
755 (
756 pon_auction_pkg.getMessage('PON_AUCTS_TARGET_PRICE'),
757 l_interface_type , --INTERFACE_TYPE
758 'PON_AUCTS_PB_RPICE_POSITIVE', -- ERROR_MESSAGE_NAME
759 p_request_id , -- REQUEST_ID
760 p_batch_id , --BATCH_ID
761 g_auction_pbs_type , -- ENTITY_TYPE
762 p_auction_header_id , -- AUCTION_HEADER_ID
763 sel_line_number , -- LINE_NUMBER
764 sel_shipment_number , -- SHIPMENT_NUMBER
765 l_exp_date , -- EXPIRATION_DATE
766 'LINENUM' , -- TOKEN1_NAME
767 sel_document_disp_line_number, -- TOKEN1_VALUE
768 interface_line_number ,
769 l_user_id , -- CREATED_BY
770 sysdate , -- CREATION_DATE
771 l_user_id , -- LAST_UPDATED_BY
772 sysdate , -- LAST_UPDATE_DATE
773 l_login_id -- LAST_UPDATE_LOGIN
774 )
775 -- EFFECTIVE START DATE AFTER SYSDATE OR CLOSE DATE
776 WHEN (
777 nvl(sel_clm_info_flag,'N') = 'N'
778 AND sel_effective_start_date IS NOT NULL
779 AND sel_effective_start_date <= NVL (sel_close_bidding_date, SYSDATE) ) THEN
780 INTO PON_INTERFACE_ERRORS
781 (
782 COLUMN_NAME ,
783 INTERFACE_TYPE ,
784 ERROR_MESSAGE_NAME,
785 REQUEST_ID ,
786 BATCH_ID ,
787 ENTITY_TYPE ,
788 AUCTION_HEADER_ID ,
789 LINE_NUMBER ,
790 SHIPMENT_NUMBER ,
791 EXPIRATION_DATE ,
792 TOKEN1_NAME ,
793 TOKEN1_VALUE ,
794 interface_line_id ,
795 CREATED_BY ,
796 CREATION_DATE ,
797 LAST_UPDATED_BY ,
798 LAST_UPDATE_DATE ,
799 LAST_UPDATE_LOGIN
800 )
801 VALUES
802 (
803 pon_auction_pkg.getMessage('PON_AUC_EFFEC_FROM'),
804 l_interface_type , --INTERFACE_TYPE
805 NVL2 (sel_close_bidding_date, 'PON_AUC_EFFC_FROM_BEF_CLOSE', 'PON_AUC_EFFC_FROM_BEF_TODAY'), -- ERROR_MESSAGE_NAME
806 p_request_id , -- REQUEST_ID
807 p_batch_id , -- BATCH_ID
808 g_auction_pbs_type , -- ENTITY_TYPE
809 p_auction_header_id , -- AUCTION_HEADER_ID
810 sel_line_number , -- LINE_NUMBER
811 sel_shipment_number , -- SHIPMENT_NUMBER
812 l_exp_date , -- EXPIRATION_DATE
813 'LINENUM' , -- TOKEN1_NAME
814 sel_document_disp_line_number , -- TOKEN1_VALUE
815 interface_line_number ,
816 l_user_id , -- CREATED_BY
817 sysdate , -- CREATION_DATE
818 l_user_id , -- LAST_UPDATED_BY
819 sysdate , -- LAST_UPDATE_DATE
820 l_login_id -- LAST_UPDATE_LOGIN
821 )
822 -- EFFECTIVE END DATE AFTER SYSDATE OR CLOSE DATE
823 WHEN ( sel_effective_end_date IS NOT NULL
824 AND sel_effective_end_date <= NVL (sel_close_bidding_date, SYSDATE)
825 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
826 INTO PON_INTERFACE_ERRORS
827 (
828 COLUMN_NAME ,
829 INTERFACE_TYPE ,
830 ERROR_MESSAGE_NAME,
831 REQUEST_ID ,
832 BATCH_ID ,
833 ENTITY_TYPE ,
834 AUCTION_HEADER_ID ,
835 LINE_NUMBER ,
836 SHIPMENT_NUMBER ,
837 EXPIRATION_DATE ,
838 TOKEN1_NAME ,
839 TOKEN1_VALUE ,
840 interface_line_id ,
841 CREATED_BY ,
842 CREATION_DATE ,
843 LAST_UPDATED_BY ,
844 LAST_UPDATE_DATE ,
845 LAST_UPDATE_LOGIN
846 )
847 VALUES
848 (
849 pon_auction_pkg.getMessage('PON_AUC_EFFEC_TO'),
850 l_interface_type , --INTERFACE_TYPE
851 NVL2 (sel_close_bidding_date, 'PON_AUC_EFFC_TO_BEFORE_CLOSE', 'PON_AUC_EFFC_TO_BEFORE_TODAY'), -- ERROR_MESSAGE_NAME
852 p_request_id , -- REQUEST_ID
853 p_batch_id , --BATCH_ID
854 g_auction_pbs_type , -- ENTITY_TYPE
855 p_auction_header_id , -- AUCTION_HEADER_ID
856 sel_line_number , -- LINE_NUMBER
857 sel_shipment_number , -- SHIPMENT_NUMBER
858 l_exp_date , -- EXPIRATION_DATE
859 'LINENUM' , -- TOKEN1_NAME
860 sel_document_disp_line_number , -- TOKEN1_VALUE
861 interface_line_number ,
862 l_user_id , -- CREATED_BY
863 sysdate , -- CREATION_DATE
864 l_user_id , -- LAST_UPDATED_BY
865 sysdate , -- LAST_UPDATE_DATE
866 l_login_id -- LAST_UPDATE_LOGIN
867 )
868 -- Price Breaks should not be entered for Fixed price line types
869 WHEN ( sel_price_tiers_indicator = 'PRICE_BREAKS'
870 AND sel_order_type_lookup_code in ('FIXED PRICE','AMOUNT')
871 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
872 INTO PON_INTERFACE_ERRORS
873 (
874 COLUMN_NAME ,
875 INTERFACE_TYPE ,
876 ERROR_MESSAGE_NAME,
877 REQUEST_ID ,
878 BATCH_ID ,
879 ENTITY_TYPE ,
880 AUCTION_HEADER_ID ,
881 LINE_NUMBER ,
882 SHIPMENT_NUMBER ,
883 EXPIRATION_DATE ,
884 TOKEN1_NAME ,
885 TOKEN1_VALUE ,
886 interface_line_id ,
887 CREATED_BY ,
888 CREATION_DATE ,
889 LAST_UPDATED_BY ,
890 LAST_UPDATE_DATE ,
891 LAST_UPDATE_LOGIN
892 )
893 VALUES
894 (
895 pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
896 l_interface_type , --INTERFACE_TYPE
897 'PON_AUCTS_PB_FP_LINE_ERR', -- ERROR_MESSAGE_NAME
898 p_request_id , -- REQUEST_ID
899 p_batch_id , --BATCH_ID
900 g_auction_pbs_type , -- ENTITY_TYPE
901 p_auction_header_id , -- AUCTION_HEADER_ID
902 sel_line_number , -- LINE_NUMBER
903 sel_shipment_number , -- SHIPMENT_NUMBER
904 l_exp_date , -- EXPIRATION_DATE
905 'LINENUM' , -- TOKEN1_NAME
906 sel_document_disp_line_number , -- TOKEN1_VALUE
907 interface_line_number ,
908 l_user_id , -- CREATED_BY
909 sysdate , -- CREATION_DATE
910 l_user_id , -- LAST_UPDATED_BY
911 sysdate , -- LAST_UPDATE_DATE
912 l_login_id -- LAST_UPDATE_LOGIN
913 )
914
915
916 /* -- Temp based labor cannot have cumilative response
917 WHEN ((sel_PURCHASE_BASIS = 'TEMP LABOR' and sel_order_type_lookup_code = 'RATE') and
918 sel_response_type = pon_auction_pkg.getMessage('PON_AUC_CUMULATIVE')
919 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
920 INTO PON_INTERFACE_ERRORS
921 (
922 COLUMN_NAME ,
923 INTERFACE_TYPE ,
924 ERROR_MESSAGE_NAME,
925 REQUEST_ID ,
926 BATCH_ID ,
927 ENTITY_TYPE ,
928 AUCTION_HEADER_ID ,
929 LINE_NUMBER ,
930 SHIPMENT_NUMBER ,
931 EXPIRATION_DATE ,
932 TOKEN1_NAME ,
933 TOKEN1_VALUE ,
934 interface_line_id ,
935 CREATED_BY ,
936 CREATION_DATE ,
937 LAST_UPDATED_BY ,
938 LAST_UPDATE_DATE ,
939 LAST_UPDATE_LOGIN
940 )
941 VALUES
942 (
943 pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
944 l_interface_type , --INTERFACE_TYPE
945 'PON_AUCTS_PB_TBL_LINE_ERR', -- ERROR_MESSAGE_NAME
946 p_request_id , -- REQUEST_ID
947 p_batch_id , --BATCH_ID
948 g_auction_pbs_type , -- ENTITY_TYPE
949 p_auction_header_id , -- AUCTION_HEADER_ID
950 sel_line_number , -- LINE_NUMBER
951 sel_shipment_number , -- SHIPMENT_NUMBER
952 l_exp_date , -- EXPIRATION_DATE
953 'LINENUM' , -- TOKEN1_NAME
954 sel_document_disp_line_number , -- TOKEN1_VALUE
955 interface_line_number ,
956 l_user_id , -- CREATED_BY
957 sysdate , -- CREATION_DATE
958 l_user_id , -- LAST_UPDATED_BY
959 sysdate , -- LAST_UPDATE_DATE
960 l_login_id -- LAST_UPDATE_LOGIN
961 ) */
962
963 -- EFFECTIVE START DATE SHOULD BE AFTER PO START DATE
964
965 WHEN ( sel_po_start_date IS NOT NULL
966 AND sel_effective_start_date IS NOT NULL
967 AND sel_effective_start_date < sel_po_start_date
968 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
969 INTO PON_INTERFACE_ERRORS
970 (
971 COLUMN_NAME ,
972 INTERFACE_TYPE ,
973 ERROR_MESSAGE_NAME,
974 REQUEST_ID ,
975 batch_id ,
976 ENTITY_TYPE ,
977 AUCTION_HEADER_ID ,
978 LINE_NUMBER ,
979 SHIPMENT_NUMBER ,
980 EXPIRATION_DATE ,
981 TOKEN1_NAME ,
982 TOKEN1_VALUE ,
983 interface_line_id ,
984 CREATED_BY ,
985 CREATION_DATE ,
986 LAST_UPDATED_BY ,
987 LAST_UPDATE_DATE ,
988 LAST_UPDATE_LOGIN
989 )
990 VALUES
991 (
992 pon_auction_pkg.getMessage('PON_AUC_EFFEC_FROM'),
993 l_interface_type , --INTERFACE_TYPE
994 'PON_AUC_EFFC_FROM_BEF_NEG' , -- ERROR_MESSAGE_NAME
995 p_request_id , -- REQUEST_ID
996 p_batch_id , -- BATCH_ID
997 g_auction_pbs_type , -- ENTITY_TYPE
998 p_auction_header_id , -- AUCTION_HEADER_ID
999 sel_line_number , -- LINE_NUMBER
1000 sel_shipment_number , -- SHIPMENT_NUMBER
1001 l_exp_date , -- EXPIRATION_DATE
1002 'LINENUM' , -- TOKEN1_NAME
1003 sel_document_disp_line_number, -- TOKEN1_VALUE
1004 interface_line_number ,
1005 l_user_id , -- CREATED_BY
1006 sysdate , -- CREATION_DATE
1007 l_user_id , -- LAST_UPDATED_BY
1008 sysdate , -- LAST_UPDATE_DATE
1009 l_login_id -- LAST_UPDATE_LOGIN
1010 )
1011 -- EFFECTIVE END DATE SHOULD BE AFTER PO START DATE
1012 WHEN ( sel_po_start_date IS NOT NULL
1013 AND sel_effective_end_date IS NOT NULL
1014 AND sel_effective_end_date < sel_po_start_date
1015 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1016 INTO PON_INTERFACE_ERRORS
1017 (
1018 COLUMN_NAME ,
1019 INTERFACE_TYPE ,
1020 ERROR_MESSAGE_NAME,
1021 REQUEST_ID ,
1022 batch_id ,
1023 ENTITY_TYPE ,
1024 AUCTION_HEADER_ID ,
1025 LINE_NUMBER ,
1026 SHIPMENT_NUMBER ,
1027 EXPIRATION_DATE ,
1028 TOKEN1_NAME ,
1029 TOKEN1_VALUE ,
1030 interface_line_id ,
1031 CREATED_BY ,
1032 CREATION_DATE ,
1033 LAST_UPDATED_BY ,
1034 LAST_UPDATE_DATE ,
1035 LAST_UPDATE_LOGIN
1036 )
1037 VALUES
1038 (
1039
1040 pon_auction_pkg.getMessage('PON_AUC_EFFEC_TO'),
1041 l_interface_type , --INTERFACE_TYPE
1042 'PON_AUC_EFFC_TO_BEFORE_NEG' , -- ERROR_MESSAGE_NAME
1043 p_request_id , -- REQUEST_ID
1044 p_batch_id , -- BATCH_ID
1045 g_auction_pbs_type , -- ENTITY_TYPE
1046 p_auction_header_id , -- AUCTION_HEADER_ID
1047 sel_line_number , -- LINE_NUMBER
1048 sel_shipment_number , -- SHIPMENT_NUMBER
1049 l_exp_date , -- EXPIRATION_DATE
1050 'LINENUM' , -- TOKEN1_NAME
1051 sel_document_disp_line_number, -- TOKEN1_VALUE
1052 interface_line_number ,
1053 l_user_id , -- CREATED_BY
1054 sysdate , -- CREATION_DATE
1055 l_user_id , -- LAST_UPDATED_BY
1056 sysdate , -- LAST_UPDATE_DATE
1057 l_login_id -- LAST_UPDATE_LOGIN
1058 )
1059 -- the effective start date should be before po end date if both are entered
1060 WHEN ( sel_po_end_date IS NOT NULL
1061 AND sel_effective_start_date IS NOT NULL
1062 AND sel_effective_start_date > sel_po_end_date
1063 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1064 INTO PON_INTERFACE_ERRORS
1065 (
1066 COLUMN_NAME ,
1067 INTERFACE_TYPE ,
1068 ERROR_MESSAGE_NAME,
1069 REQUEST_ID ,
1070 batch_id ,
1071 ENTITY_TYPE ,
1072 AUCTION_HEADER_ID ,
1073 LINE_NUMBER ,
1074 SHIPMENT_NUMBER ,
1075 EXPIRATION_DATE ,
1076 TOKEN1_NAME ,
1077 TOKEN1_VALUE ,
1078 interface_line_id ,
1079 CREATED_BY ,
1080 CREATION_DATE ,
1081 LAST_UPDATED_BY ,
1082 LAST_UPDATE_DATE ,
1083 LAST_UPDATE_LOGIN
1084 )
1085 VALUES
1086 (
1087 pon_auction_pkg.getMessage('PON_AUC_EFFEC_FROM'),
1088 l_interface_type , --INTERFACE_TYPE
1089 'PON_AUC_EFFC_FROM_AFT_NEG' , -- ERROR_MESSAGE_NAME
1090 p_request_id , -- REQUEST_ID
1091 p_batch_id , -- BATCH_ID
1092 /*g_auction_pbs_type*/ null , -- ENTITY_TYPE
1093 p_auction_header_id , -- AUCTION_HEADER_ID
1094 sel_line_number , -- LINE_NUMBER
1095 sel_shipment_number , -- SHIPMENT_NUMBER
1096 l_exp_date , -- EXPIRATION_DATE
1097 'LINENUM' , -- TOKEN1_NAME
1098 sel_document_disp_line_number, -- TOKEN1_VALUE
1099 interface_line_number ,
1100 l_user_id , -- CREATED_BY
1101 sysdate , -- CREATION_DATE
1102 l_user_id , -- LAST_UPDATED_BY
1103 sysdate , -- LAST_UPDATE_DATE
1104 l_login_id -- LAST_UPDATE_LOGIN
1105 )
1106 -- effective end date should be before the po end date
1107 WHEN ( sel_po_end_date IS NOT NULL
1108 AND sel_effective_end_date IS NOT NULL
1109 AND sel_effective_end_date > sel_po_end_date
1110 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1111 INTO PON_INTERFACE_ERRORS
1112 (
1113 COLUMN_NAME ,
1114 INTERFACE_TYPE ,
1115 ERROR_MESSAGE_NAME,
1116 REQUEST_ID ,
1117 batch_id ,
1118 ENTITY_TYPE ,
1119 AUCTION_HEADER_ID ,
1120 LINE_NUMBER ,
1121 SHIPMENT_NUMBER ,
1122 EXPIRATION_DATE ,
1123 TOKEN1_NAME ,
1124 TOKEN1_VALUE ,
1125 interface_line_id ,
1126 CREATED_BY ,
1127 CREATION_DATE ,
1128 LAST_UPDATED_BY ,
1129 LAST_UPDATE_DATE ,
1130 LAST_UPDATE_LOGIN
1131 )
1132 VALUES
1133 (
1134 pon_auction_pkg.getMessage('PON_AUC_EFFEC_TO'),
1135 l_interface_type , --INTERFACE_TYPE
1136 'PON_AUC_EFFC_TO_AFT_NEG' , -- ERROR_MESSAGE_NAME
1137 p_request_id , -- REQUEST_ID
1138 p_batch_id , -- BATCH_ID
1139 g_auction_pbs_type , -- ENTITY_TYPE
1140 p_auction_header_id , -- AUCTION_HEADER_ID
1141 sel_line_number , -- LINE_NUMBER
1142 sel_shipment_number , -- SHIPMENT_NUMBER
1143 l_exp_date , -- EXPIRATION_DATE
1144 'LINENUM' , -- TOKEN1_NAME
1145 sel_document_disp_line_number, -- TOKEN1_VALUE
1146 interface_line_number ,
1147 l_user_id , -- CREATED_BY
1148 sysdate , -- CREATION_DATE
1149 l_user_id , -- LAST_UPDATED_BY
1150 sysdate , -- LAST_UPDATE_DATE
1151 l_login_id -- LAST_UPDATE_LOGIN
1152 )
1153 -- validating ship_to_organization
1154 WHEN ( sel_ship_to_organization IS NOT null
1155 and sel_ship_to_organization_id IS null
1156 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1157 INTO PON_INTERFACE_ERRORS
1158 (
1159 COLUMN_NAME ,
1160 INTERFACE_TYPE ,
1161 ERROR_MESSAGE_NAME,
1162 REQUEST_ID ,
1163 batch_id ,
1164 ENTITY_TYPE ,
1165 AUCTION_HEADER_ID ,
1166 LINE_NUMBER ,
1167 SHIPMENT_NUMBER ,
1168 EXPIRATION_DATE ,
1169 TOKEN1_NAME ,
1170 TOKEN1_VALUE ,
1171 TOKEN2_NAME ,
1172 TOKEN2_VALUE ,
1173 interface_line_id ,
1174 CREATED_BY ,
1175 CREATION_DATE ,
1176 LAST_UPDATED_BY ,
1177 LAST_UPDATE_DATE ,
1178 LAST_UPDATE_LOGIN
1179 )
1180 VALUES
1181 (
1182 pon_auction_pkg.getMessage('PON_SHIP_TO_ORGANIZATION'),
1183 l_interface_type , --INTERFACE_TYPE
1184 'PON_AUC_DATA_INVALID_SHIPO_REF' , -- ERROR_MESSAGE_NAME
1185 p_request_id , -- REQUEST_ID
1186 p_batch_id , -- BATCH_ID
1187 g_auction_pbs_type , -- ENTITY_TYPE
1188 p_auction_header_id , -- AUCTION_HEADER_ID
1189 sel_line_number , -- LINE_NUMBER
1190 sel_shipment_number , -- SHIPMENT_NUMBER
1191 l_exp_date , -- EXPIRATION_DATE
1192 'ITEMNUM' , -- TOKEN1_NAME
1193 sel_document_disp_line_number, -- TOKEN1_VALUE
1194 'SHIPNUM' , -- TOKEN2_NAME
1195 null, -- TOKEN2_VALUE
1196 interface_line_number ,
1197 l_user_id , -- CREATED_BY
1198 sysdate , -- CREATION_DATE
1199 l_user_id , -- LAST_UPDATED_BY
1200 sysdate , -- LAST_UPDATE_DATE
1201 l_login_id -- LAST_UPDATE_LOGIN
1202 )
1203 -- validating ship_to_location
1204
1205 WHEN ( sel_ship_to_location IS NOT null
1206 and sel_ship_to_location_id IS null
1207 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1208 INTO PON_INTERFACE_ERRORS
1209 (
1210 COLUMN_NAME ,
1211 INTERFACE_TYPE ,
1212 ERROR_MESSAGE_NAME,
1213 REQUEST_ID ,
1214 batch_id ,
1215 ENTITY_TYPE ,
1216 AUCTION_HEADER_ID ,
1217 LINE_NUMBER ,
1218 SHIPMENT_NUMBER ,
1219 EXPIRATION_DATE ,
1220 TOKEN1_NAME ,
1221 TOKEN1_VALUE ,
1222 TOKEN2_NAME ,
1223 TOKEN2_VALUE ,
1224 interface_line_id ,
1225 CREATED_BY ,
1226 CREATION_DATE ,
1227 LAST_UPDATED_BY ,
1228 LAST_UPDATE_DATE ,
1229 LAST_UPDATE_LOGIN
1230 )
1231 VALUES
1232 (
1233 pon_auction_pkg.getMessage('PON_SHIP_TO_LOCATION'),
1234 l_interface_type , --INTERFACE_TYPE
1235 'PON_AUC_DATA_INVALID_SHIPL_REF' , -- ERROR_MESSAGE_NAME
1236 p_request_id , -- REQUEST_ID
1237 p_batch_id , -- BATCH_ID
1238 g_auction_pbs_type , -- ENTITY_TYPE
1239 p_auction_header_id , -- AUCTION_HEADER_ID
1240 sel_line_number , -- LINE_NUMBER
1241 sel_shipment_number , -- SHIPMENT_NUMBER
1242 l_exp_date , -- EXPIRATION_DATE
1243 'ITEMNUM' , -- TOKEN1_NAME
1244 sel_document_disp_line_number, -- TOKEN1_VALUE
1245 'SHIPNUM' , -- TOKEN2_NAME
1246 null, -- TOKEN2_VALUE
1247 interface_line_number ,
1248 l_user_id , -- CREATED_BY
1249 sysdate , -- CREATION_DATE
1250 l_user_id , -- LAST_UPDATED_BY
1251 sysdate , -- LAST_UPDATE_DATE
1252 l_login_id -- LAST_UPDATE_LOGIN
1253 )
1254
1255 SELECT paip.line_number sel_line_number ,
1256 PAIP.document_disp_line_number sel_document_disp_line_number,
1257 papbi.shipment_number sel_shipment_number ,
1258 papbi.interface_line_id interface_line_number,
1259 papbi.PRICE sel_price ,
1260 papbi.min_quantity sel_min_quantity ,
1261 papbi.max_quantity sel_max_quantity ,
1262 papbi.quantity sel_quantity ,
1263 papbi.effective_end_date sel_effective_end_date ,
1264 papbi.effective_start_date sel_effective_start_date ,
1265 papbi.ship_to_location_id sel_ship_to_location_id ,
1266 papbi.ship_to_organization_id sel_ship_to_organization_id ,
1267 paha.po_start_date sel_po_start_date,
1268 paha.po_end_date sel_po_end_date,
1269 paha.price_tiers_indicator sel_price_tiers_indicator,
1270 PAIP.order_type_lookup_code sel_order_type_lookup_code,
1271 paip.PURCHASE_BASIS sel_PURCHASE_BASIS,
1272 paip.clm_info_flag sel_clm_info_flag,
1273 paip.price_break_type sel_price_break_type,
1274 paha.global_agreement_flag sel_global_agreement_flag,
1275 papbi.ship_to_organization sel_ship_to_organization,
1276 papbi.ship_to_location sel_ship_to_location,
1277 paha.close_bidding_date sel_close_bidding_date
1278 FROM PON_AUCTION_ITEM_PRICES_ALL PAIP,
1279 pon_auc_price_breaks_interface papbi,
1280 pon_auction_headers_all paha
1281 WHERE PAIP.AUCTION_HEADER_ID(+) = p_auction_header_id
1282 AND papbi.AUCTION_HEADER_ID = p_auction_header_id
1283 AND paha.AUCTION_HEADER_ID = p_auction_header_id
1284 AND PAIP.LINE_NUMBER(+) = papbi.auction_LINE_NUMBER
1285 AND papbi.batch_id = p_batch_id;
1286
1287 END val_price_breaks;
1288
1289 /*================================================================================
1290 PROCEDURE: VAL_QUANTITY_BASED PRIVATE
1291 PARAMETERS:
1292 COMMENT : This procedure is used to validate the quantity based price tiers.
1293 ==================================================================================*/
1294
1295
1296 PROCEDURE val_quantity_based (p_source VARCHAR2, p_batch_Id NUMBER,p_auction_header_id number) IS
1297 l_user_id NUMBER;
1298 l_login_id NUMBER;
1299 l_exp_date DATE;
1300 l_interface_type VARCHAR2(15);
1301 l_module CONSTANT VARCHAR2(32) := 'VALIDATE_CREATION';
1302 l_progress VARCHAR2(200);
1303 p_request_id NUMBER;
1304 l_price_Tiers_indicator pon_auction_headers_all.price_tiers_indicator%TYPE;
1305
1306
1307 BEGIN
1308
1309 l_user_id := fnd_global.user_id;
1310 l_login_id := fnd_global.login_id;
1311 l_exp_date := SYSDATE + 7;
1312 l_interface_type := 'NEGSHIPMTUPLOAD';
1313 p_request_id := 0;
1314
1315
1316 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1317 print_debug_log(l_module,'Validating quantity based price tiers for p_batch_id = '||p_batch_id);
1318 END IF;
1319
1320
1321 SELECT price_tiers_indicator INTO l_price_tiers_indicator
1322 FROM pon_auction_headers_all WHERE auction_header_id = p_auction_header_id;
1323
1324
1325
1326 INSERT ALL
1327 -- Validate the Line Number
1328 WHEN ( sel_line_number IS NULL) THEN
1329 INTO PON_INTERFACE_ERRORS
1330 (
1331 COLUMN_NAME ,
1332 INTERFACE_TYPE ,
1333 ERROR_MESSAGE_NAME,
1334 REQUEST_ID ,
1335 BATCH_ID ,
1336 ENTITY_TYPE ,
1337 AUCTION_HEADER_ID ,
1338 LINE_NUMBER ,
1339 SHIPMENT_NUMBER ,
1340 EXPIRATION_DATE ,
1341 TOKEN1_NAME ,
1342 TOKEN1_VALUE ,
1343 interface_line_id ,
1344 CREATED_BY ,
1345 CREATION_DATE ,
1346 LAST_UPDATED_BY ,
1347 LAST_UPDATE_DATE ,
1348 LAST_UPDATE_LOGIN
1349 )
1350 VALUES
1351 (
1352 pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
1353 l_interface_type , --INTERFACE_TYPE
1354 'PON_INVALID_LINE_NUM', -- ERROR_MESSAGE_NAME
1355 p_request_id , -- REQUEST_ID
1356 p_batch_id , --BATCH_ID
1357 g_auction_pbs_type , -- ENTITY_TYPE
1358 p_auction_header_id , -- AUCTION_HEADER_ID
1359 sel_line_number , -- LINE_NUMBER
1360 sel_shipment_number , -- SHIPMENT_NUMBER
1361 l_exp_date , -- EXPIRATION_DATE
1362 null , -- TOKEN1_NAME
1363 null , -- TOKEN1_VALUE
1364 interface_line_number ,
1365 l_user_id , -- CREATED_BY
1366 sysdate , -- CREATION_DATE
1367 l_user_id , -- LAST_UPDATED_BY
1368 sysdate , -- LAST_UPDATE_DATE
1369 l_login_id -- LAST_UPDATE_LOGIN
1370 )
1371 -- min quantity should not be empty or negative
1372 WHEN ( sel_min_quantity IS NOT NULL
1373 AND sel_min_quantity < 0
1374 AND sel_min_quantity <> g_null_int
1375 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1376 INTO PON_INTERFACE_ERRORS
1377 (
1378 COLUMN_NAME ,
1379 INTERFACE_TYPE ,
1380 ERROR_MESSAGE_NAME,
1381 REQUEST_ID ,
1382 BATCH_ID ,
1383 ENTITY_TYPE ,
1384 AUCTION_HEADER_ID ,
1385 LINE_NUMBER ,
1386 SHIPMENT_NUMBER ,
1387 EXPIRATION_DATE ,
1388 TOKEN1_NAME ,
1389 TOKEN1_VALUE ,
1390 interface_line_id ,
1391 CREATED_BY ,
1392 CREATION_DATE ,
1393 LAST_UPDATED_BY ,
1394 LAST_UPDATE_DATE ,
1395 LAST_UPDATE_LOGIN
1396 )
1397 VALUES
1398 (
1399 pon_auction_pkg.getMessage('PON_TIERS_MIN_QUANTITY'),
1400 l_interface_type , --INTERFACE_TYPE
1401 'PON_AUCTS_PB_QUANTITY_POSITIVE', -- ERROR_MESSAGE_NAME
1402 p_request_id , -- REQUEST_ID
1403 p_batch_id , --BATCH_ID
1404 g_auction_pbs_type , -- ENTITY_TYPE
1405 p_auction_header_id , -- AUCTION_HEADER_ID
1406 sel_line_number , -- LINE_NUMBER
1407 sel_shipment_number , -- SHIPMENT_NUMBER
1408 l_exp_date , -- EXPIRATION_DATE
1409 'LINENUM' , -- TOKEN1_NAME
1410 sel_document_disp_line_number , -- TOKEN1_VALUE
1411 interface_line_number ,
1412 l_user_id , -- CREATED_BY
1413 sysdate , -- CREATION_DATE
1414 l_user_id , -- LAST_UPDATED_BY
1415 sysdate , -- LAST_UPDATE_DATE
1416 l_login_id -- LAST_UPDATE_LOGIN
1417 )
1418 -- max quantity should not be empty or negative
1419 WHEN ( sel_max_quantity IS NOT NULL
1420 AND sel_max_quantity < 0
1421 AND sel_max_quantity <> g_null_int
1422 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1423 INTO PON_INTERFACE_ERRORS
1424 (
1425 COLUMN_NAME ,
1426 INTERFACE_TYPE ,
1427 ERROR_MESSAGE_NAME,
1428 REQUEST_ID ,
1429 BATCH_ID ,
1430 ENTITY_TYPE ,
1431 AUCTION_HEADER_ID ,
1432 LINE_NUMBER ,
1433 SHIPMENT_NUMBER ,
1434 EXPIRATION_DATE ,
1435 TOKEN1_NAME ,
1436 TOKEN1_VALUE ,
1437 interface_line_id ,
1438 CREATED_BY ,
1439 CREATION_DATE ,
1440 LAST_UPDATED_BY ,
1441 LAST_UPDATE_DATE ,
1442 LAST_UPDATE_LOGIN
1443 )
1444 VALUES
1445 (
1446 pon_auction_pkg.getMessage('PON_TIERS_MAX_QUANTITY'),
1447 l_interface_type , --INTERFACE_TYPE
1448 'PON_AUCTS_PB_QUANTITY_POSITIVE', -- ERROR_MESSAGE_NAME
1449 p_request_id , -- REQUEST_ID
1450 p_batch_id , --BATCH_ID
1451 g_auction_pbs_type , -- ENTITY_TYPE
1452 p_auction_header_id , -- AUCTION_HEADER_ID
1453 sel_line_number , -- LINE_NUMBER
1454 sel_shipment_number , -- SHIPMENT_NUMBER
1455 l_exp_date , -- EXPIRATION_DATE
1456 'LINENUM' , -- TOKEN1_NAME
1457 sel_document_disp_line_number , -- TOKEN1_VALUE
1458 interface_line_number ,
1459 l_user_id , -- CREATED_BY
1460 sysdate , -- CREATION_DATE
1461 l_user_id , -- LAST_UPDATED_BY
1462 sysdate , -- LAST_UPDATE_DATE
1463 l_login_id -- LAST_UPDATE_LOGIN
1464 )
1465
1466 -- max quantity should not be less than min qauntity
1467 WHEN ( sel_max_quantity IS NOT NULL
1468 AND sel_min_quantity IS NOT NULL
1469 AND sel_max_quantity < sel_min_quantity
1470 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1471 INTO PON_INTERFACE_ERRORS
1472 (
1473 COLUMN_NAME ,
1474 INTERFACE_TYPE ,
1475 ERROR_MESSAGE_NAME,
1476 REQUEST_ID ,
1477 BATCH_ID ,
1478 ENTITY_TYPE ,
1479 AUCTION_HEADER_ID ,
1480 LINE_NUMBER ,
1481 SHIPMENT_NUMBER ,
1482 EXPIRATION_DATE ,
1483 TOKEN1_NAME ,
1484 TOKEN1_VALUE ,
1485 interface_line_id ,
1486 CREATED_BY ,
1487 CREATION_DATE ,
1488 LAST_UPDATED_BY ,
1489 LAST_UPDATE_DATE ,
1490 LAST_UPDATE_LOGIN
1491 )
1492 VALUES
1493 (
1494 pon_auction_pkg.getMessage('PON_TIERS_MIN_QUANTITY'),
1495 l_interface_type , --INTERFACE_TYPE
1496 'PON_QT_MAX_MIN_QTY_ERR', -- ERROR_MESSAGE_NAME
1497 p_request_id , -- REQUEST_ID
1498 p_batch_id , --BATCH_ID
1499 g_auction_pbs_type , -- ENTITY_TYPE
1500 p_auction_header_id , -- AUCTION_HEADER_ID
1501 sel_line_number , -- LINE_NUMBER
1502 sel_shipment_number , -- SHIPMENT_NUMBER
1503 l_exp_date , -- EXPIRATION_DATE
1504 'LINENUM' , -- TOKEN1_NAME
1505 sel_document_disp_line_number , -- TOKEN1_VALUE
1506 interface_line_number ,
1507 l_user_id , -- CREATED_BY
1508 sysdate , -- CREATION_DATE
1509 l_user_id , -- LAST_UPDATED_BY
1510 sysdate , -- LAST_UPDATE_DATE
1511 l_login_id -- LAST_UPDATE_LOGIN
1512 )
1513
1514 -- price should be positive
1515 WHEN ( sel_price < 0
1516 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1517 INTO PON_INTERFACE_ERRORS
1518 (
1519 COLUMN_NAME ,
1520 INTERFACE_TYPE ,
1521 ERROR_MESSAGE_NAME,
1522 REQUEST_ID ,
1523 BATCH_ID ,
1524 ENTITY_TYPE ,
1525 AUCTION_HEADER_ID ,
1526 LINE_NUMBER ,
1527 SHIPMENT_NUMBER ,
1528 EXPIRATION_DATE ,
1529 TOKEN1_NAME ,
1530 TOKEN1_VALUE ,
1531 interface_line_id ,
1532 CREATED_BY ,
1533 CREATION_DATE ,
1534 LAST_UPDATED_BY ,
1535 LAST_UPDATE_DATE ,
1536 LAST_UPDATE_LOGIN
1537 )
1538 VALUES
1539 (
1540 pon_auction_pkg.getMessage('PON_AUCTS_TARGET_PRICE'),
1541 l_interface_type , --INTERFACE_TYPE
1542 'PON_AUCTS_PB_RPICE_POSITIVE', -- ERROR_MESSAGE_NAME
1543 p_request_id , -- REQUEST_ID
1544 p_batch_id , --BATCH_ID
1545 g_auction_pbs_type , -- ENTITY_TYPE
1546 p_auction_header_id , -- AUCTION_HEADER_ID
1547 sel_line_number , -- LINE_NUMBER
1548 sel_shipment_number , -- SHIPMENT_NUMBER
1549 l_exp_date , -- EXPIRATION_DATE
1550 'LINENUM' , -- TOKEN1_NAME
1551 sel_document_disp_line_number, -- TOKEN1_VALUE
1552 interface_line_number ,
1553 l_user_id , -- CREATED_BY
1554 sysdate , -- CREATION_DATE
1555 l_user_id , -- LAST_UPDATED_BY
1556 sysdate , -- LAST_UPDATE_DATE
1557 l_login_id -- LAST_UPDATE_LOGIN
1558 )
1559
1560 -- Quantity Based Price Tiers should be entered only for than rate based temp labor and goods
1561 WHEN (
1562 NOT ((sel_PURCHASE_BASIS = 'TEMP LABOR' and sel_order_type_lookup_code = 'RATE') OR
1563 sel_PURCHASE_BASIS = 'GOODS')
1564 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
1565 INTO PON_INTERFACE_ERRORS
1566 (
1567 COLUMN_NAME ,
1568 INTERFACE_TYPE ,
1569 ERROR_MESSAGE_NAME,
1570 REQUEST_ID ,
1571 BATCH_ID ,
1572 ENTITY_TYPE ,
1573 AUCTION_HEADER_ID ,
1574 LINE_NUMBER ,
1575 SHIPMENT_NUMBER ,
1576 EXPIRATION_DATE ,
1577 TOKEN1_NAME ,
1578 TOKEN1_VALUE ,
1579 interface_line_id ,
1580 CREATED_BY ,
1581 CREATION_DATE ,
1582 LAST_UPDATED_BY ,
1583 LAST_UPDATE_DATE ,
1584 LAST_UPDATE_LOGIN
1585 )
1586 VALUES
1587 (
1588 pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
1589 l_interface_type , --INTERFACE_TYPE
1590 'PON_AUCTS_QB_LINE_TYP_ERR', -- ERROR_MESSAGE_NAME
1591 p_request_id , -- REQUEST_ID
1592 p_batch_id , --BATCH_ID
1593 g_auction_pbs_type , -- ENTITY_TYPE
1594 p_auction_header_id , -- AUCTION_HEADER_ID
1595 sel_line_number , -- LINE_NUMBER
1596 sel_shipment_number , -- SHIPMENT_NUMBER
1597 l_exp_date , -- EXPIRATION_DATE
1598 'LINENUM' , -- TOKEN1_NAME
1599 sel_document_disp_line_number , -- TOKEN1_VALUE
1600 interface_line_number ,
1601 l_user_id , -- CREATED_BY
1602 sysdate , -- CREATION_DATE
1603 l_user_id , -- LAST_UPDATED_BY
1604 sysdate , -- LAST_UPDATE_DATE
1605 l_login_id -- LAST_UPDATE_LOGIN
1606 )
1607
1608 SELECT PAIP.LINE_NUMBER sel_line_number ,
1609 PAIP.DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
1610 papbi.shipment_number sel_shipment_number ,
1611 papbi.INTERFACE_LINE_id INTERFACE_LINE_NUMBER,
1612 papbi.PRICE sel_price ,
1613 papbi.min_quantity sel_min_quantity ,
1614 papbi.max_quantity sel_max_quantity ,
1615 papbi.QUANTITY sel_quantity ,
1616 papbi.EFFECTIVE_END_DATE sel_effective_end_date ,
1617 papbi.EFFECTIVE_START_DATE sel_effective_start_date ,
1618 papbi.SHIP_TO_LOCATION_ID sel_ship_to_location_id ,
1619 papbi.SHIP_TO_ORGANIZATION_ID sel_ship_to_organization_id ,
1620 paha.po_start_date sel_po_start_date,
1621 paha.po_end_date sel_po_end_date,
1622 paha.price_tiers_indicator sel_price_tiers_indicator,
1623 paip.order_type_lookup_code sel_order_type_lookup_code,
1624 paip.PURCHASE_BASIS sel_PURCHASE_BASIS,
1625 paip.clm_info_flag sel_clm_info_flag,
1626 paha.close_bidding_date sel_close_bidding_date
1627 FROM PON_AUCTION_ITEM_PRICES_ALL PAIP,
1628 pon_auc_price_breaks_interface papbi,
1629 pon_auction_headers_all paha
1630 WHERE PAIP.AUCTION_HEADER_ID(+) = p_auction_header_id
1631 AND papbi.AUCTION_HEADER_ID = p_auction_header_id
1632 AND paha.AUCTION_HEADER_ID = p_auction_header_id
1633 AND PAIP.LINE_NUMBER(+) = papbi.auction_LINE_NUMBER
1634 AND papbi.batch_id = p_batch_id;
1635
1636
1637
1638 -- The ranges of min-max quantities should not overlap across tiers for a given line in a negotiation
1639 -- When this validation is performed at shipments level multiple error message were thrown for one particular line
1640 -- To avoid the multiple error messages this validation has to be performed at line level rather than shipments level.
1641
1642 INSERT INTO PON_INTERFACE_ERRORS
1643 (
1644 COLUMN_NAME,INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
1645 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1646 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
1647 )
1648 SELECT
1649 distinct
1650 pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
1651 l_interface_type, --INTERFACE_TYPE
1652 'PON_AUC_OVERLAP_RANGES_QT', -- ERROR_MESSAGE_NAME
1653 p_request_id, -- REQUEST_ID
1654 p_batch_id, --BATCH_ID
1655 g_auction_pbs_type, -- ENTITY_TYPE
1656 p_auction_header_id, -- AUCTION_HEADER_ID
1657 paip.document_disp_line_number, -- LINE_NUMBER
1658 l_exp_date, -- EXPIRATION_DATE
1659 'LINENUM', -- TOKEN1_NAME
1660 paip.document_disp_line_number, -- TOKEN1_VALUE
1661 l_user_id, -- CREATED_BY
1662 sysdate, -- CREATION_DATE
1663 l_user_id, -- LAST_UPDATED_BY
1664 LAST_UPDATE_DATE,
1665 l_login_id -- LAST_UPDATE_LOGIN
1666 FROM pon_auc_price_breaks_interface paip
1667 WHERE paip.auction_header_id = p_auction_header_id
1668 AND paip.batch_id = p_batch_id
1669 AND paip.auction_line_number IN
1670 ( (
1671
1672 SELECT pasa1.auction_line_number
1673 FROM pon_auc_price_breaks_interface pasa1,
1674 pon_auc_price_breaks_interface pasa
1675 WHERE pasa1.auction_header_id = p_auction_header_id
1676 and pasa.auction_header_id = p_auction_header_id
1677 AND pasa.auction_line_number = pasa1.auction_line_number
1678 AND pasa1.batch_id = p_batch_id
1679 AND pasa.batch_id = p_batch_id
1680 AND pasa.INTERFACE_LINE_ID <> pasa1.INTERFACE_LINE_ID
1681 AND pasa1.min_quantity <= pasa.min_quantity
1682 and pasa.min_quantity <= pasa1.max_quantity)
1683
1684 UNION
1685
1686 (SELECT pasa1.auction_line_number
1687 FROM pon_auc_price_breaks_interface pasa1,
1688 pon_auction_shipments_all pasa
1689 WHERE pasa1.auction_header_id = p_auction_header_id
1690 and pasa.auction_header_id = p_auction_header_id
1691 AND pasa1.batch_id = p_batch_id
1692 AND pasa.line_number = pasa1.auction_line_number
1693 AND Nvl(pasa1.shipment_number,-999) <> pasa.shipment_number
1694 AND pasa.quantity <= pasa1.min_quantity
1695 and pasa1.min_quantity <= pasa.max_quantity ));
1696
1697
1698 END val_quantity_based;
1699
1700 /*======================================================================
1701 PROCEDURE: PRINT_ERROR_LOG PRIVATE
1702 PARAMETERS:
1703 COMMENT : This procedure is used to print unexpected exceptions or
1704 error messages into FND logs
1705 ======================================================================*/
1706
1707 PROCEDURE print_error_log(p_module IN VARCHAR2,
1708 p_message IN VARCHAR2)
1709 IS
1710 BEGIN
1711
1712 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1713 FND_LOG.string(log_level => FND_LOG.level_procedure,
1714 module => g_module_prefix || p_module,
1715 message => p_message);
1716 END if;
1717
1718 END;
1719
1720 /*======================================================================
1721 PROCEDURE: PRINT_DEBUG_LOG PRIVATE
1722 PARAMETERS:
1723 COMMENT : This procedure is used to print debug messages into
1724 FND logs
1725 ======================================================================*/
1726 PROCEDURE print_debug_log(p_module IN VARCHAR2,
1727 p_message IN VARCHAR2)
1728 IS
1729
1730 BEGIN
1731
1732 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1733 FND_LOG.string(log_level => FND_LOG.level_statement,
1734 module => g_module_prefix || p_module,
1735 message => p_message);
1736 END if;
1737 END;
1738 END pon_validate_shipments_int;