[Home] [Help]
PACKAGE BODY: APPS.PON_AUCTION_CREATE_PO_PKG
Source
1 PACKAGE BODY pon_auction_create_po_pkg as
2 /* $Header: PONCRPOB.pls 120.72.12020000.4 2013/02/09 10:33:03 hvutukur ship $ */
3
4 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 g_module CONSTANT VARCHAR2(50) := 'pon.plsql.pon_auction_create_po_pkg';
6
7 ---------Variables---------------------
8 PO_SUCCESS NUMBER := 1;
9 DUPLICATE_PO_NUMBER NUMBER := 2;
10 PO_SYSTEM_ERROR NUMBER := 3;
11 SOURCING_SYSTEM_ERROR NUMBER := 4;
12 PO_PDOI_ERROR NUMBER := 5;
13 PO_DELETE_ERROR NUMBER :=6;
14
15 -------------------------------------------------------------------------------
16 -------------------------- PACKAGE BODY --------------------------------------
17 -------------------------------------------------------------------------------
18
19 PROCEDURE log_message(p_message IN VARCHAR2) IS
20 BEGIN
21 IF (g_fnd_debug = 'Y') THEN
22 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
23 FND_LOG.string(log_level => FND_LOG.level_statement,
24 module => g_module,
25 message => substr(p_message, 0, 4000));
26 END IF;
27 END IF;
28 END;
29
30 PROCEDURE log_error(p_message IN VARCHAR2) IS
31 BEGIN
32 IF (g_fnd_debug = 'Y') THEN
33 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
34 FND_LOG.string(log_level => FND_LOG.level_unexpected,
35 module => g_module,
36 message => substr(p_message, 0, 4000));
37 END IF;
38 END IF;
39 END;
40
41 PROCEDURE MAINTAIN_CLM_RELATIONS(x_interface_id IN NUMBER);
42
43
44 ---------------------------------------------------------------------------
45 --This procedure is called by the "Auto-Allocation of non-allocated items
46 --and Split requisition" activity node of the PONCOMPL (Sourcing Complete
47 --Auction) Workflow.
48 --It calls ALLOC_ALL_UNALLOC_ITEMS to allocate all unallocated items.
49 --It also populates PO's interface table with the appropriate award and
50 --req info and calls PO's Split Requisition API, which populates the same
51 --table with the new, split req ids
52 ----------------------------------------------------------------------------
53
54 procedure AUTO_ALLOC_AND_SPLIT_REQ(p_auction_header_id IN NUMBER, -- 1
55 p_user_name IN VARCHAR2, -- 2
56 p_user_id IN NUMBER, -- 3
57 p_formatted_name IN VARCHAR2, -- 4
58 p_auction_title IN VARCHAR2, -- 5
59 p_organization_name IN VARCHAR2,
60 p_resultout OUT NOCOPY VARCHAR2,
61 x_allocation_error OUT NOCOPY VARCHAR2,
62 x_line_number OUT NOCOPY NUMBER,
63 x_item_number OUT NOCOPY VARCHAR2,
64 x_item_description OUT NOCOPY VARCHAR2,
65 x_item_revision OUT NOCOPY VARCHAR2,
66 x_requisition_number OUT NOCOPY VARCHAR2,
67 x_job_name OUT NOCOPY VARCHAR2,
68 x_document_disp_line_number OUT NOCOPY VARCHAR2) IS
69
70 x_item VARCHAR2(50);
71 x_allocation_result VARCHAR2(10);
72 x_failure_status VARCHAR2(10);
73 x_alloc_failure_reason VARCHAR2(2000);
74 x_source_reqs_flag VARCHAR2(1);
75 x_contract_type VARCHAR2(10);
76 x_split_result VARCHAR2(10);
77 x_split_failure_reason VARCHAR2(2000);
78 x_split_failed_req_number NUMBER;
79 x_return_error_code VARCHAR2(10);
80
81 x_responsibility_id number := null;
82 x_application_id number := null;
83
84 x_language_code VARCHAR2(3);
85 x_last_update_date pon_auction_headers_all.last_update_date%TYPE;
86 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
87 x_origination_code pon_auction_headers_all.auction_origination_code%TYPE;
88 x_return_code VARCHAR2(10);
89
90 l_api_name VARCHAR2(30) := ' AUTO_ALLOC_AND_SPLIT_REQ';
91 l_debug_enabled VARCHAR2(1) := 'N';
92 l_exception_enabled VARCHAR2(1) := 'N';
93 l_progress NUMBER := 0;
94
95 --Staggered Awards project
96 l_allow_stag_awards VARCHAR2(1);
97 l_has_unawarded_lines VARCHAR2(1);
98 l_unawarded_line_count NUMBER;
99
100 BEGIN
101
102 /* perform initialization for FND logging */
103 if(g_fnd_debug = 'Y') then
104
105 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
106 l_debug_enabled := 'Y';
107 end if;
108
109 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
110 l_exception_enabled := 'Y';
111 end if;
112
113 end if;
114
115 if(l_debug_enabled = 'Y') then
116
117 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
118
119 log_message(x_progress);
120
121 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auction_header_id
122 || ' ' || p_user_name || ' ' || p_user_id || ' '
123 || p_formatted_name || ' ' || p_auction_title || ' '
124 || p_organization_name;
125 log_message(x_progress);
126
127 end if;
128
129 -- establish savepoint so that if an exception occurs during either
130 -- the auto-allocation or the splitting of requisition, no data inserted
131 -- when auto-allocating will get committed to db
132
133 --savepoint PON_BEFORE_AUTO_ALLOC;
134
135 -- basic initialization
136 x_failure_status := 'SUCCESS';
137 p_resultout := 'SUCCESS';
138
139
140 FND_PROFILE.GET('RESP_ID', x_responsibility_id);
141
142 FND_PROFILE.GET('RESP_APPL_ID', x_application_id);
143
144 fnd_global.APPS_INITIALIZE (p_user_id, x_responsibility_id, x_application_id);
145 --bug 5245568; need to call init to intialize MOAC
146 mo_global.init('PON');
147
148 if(l_debug_enabled = 'Y') then
149 x_progress := ++l_progress || l_api_name || ' : after retrieving resp_id and resp_appl_id';
150 log_message(x_progress);
151 end if;
152
153
154 -- set the session's language so that calls to getMessage would
155 -- return the correct message in user's language
156
157 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(p_user_name, x_language_code);
158
159 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
160
161 if(l_debug_enabled = 'Y') then
162 x_progress := ++l_progress || l_api_name || ' : after retrieving language code, etc';
163 log_message(x_progress);
164 end if;
165
166 -- Lock auction table to prevent concurrency errors
167
168 SELECT last_update_date
169 INTO x_last_update_date
170 FROM pon_auction_headers_all
171 WHERE auction_header_id = p_auction_header_id
172 FOR UPDATE;
173
174 -- Determine whether we are sourcing requisition lines against the
175 -- blanket agreements. In the case of a blanket agreement, if we are
176 -- not, do not automatically allocate or call po's split api
177
178 SELECT nvl(source_reqs_flag,'N'), contract_type, nvl(auction_origination_code, 'NONE')
179 into x_source_reqs_flag, x_contract_type, x_origination_code
180 FROM pon_auction_headers_all
181 where auction_header_id = p_auction_header_id;
182
183
184 if(l_debug_enabled = 'Y') then
185 x_progress := ++l_progress || l_api_name;
186 log_message(x_progress);
187 log_message(l_api_name || ' x_source_reqs_flag is ' || x_source_reqs_flag);
188 log_message(l_api_name || 'x_contract_type is ' || x_contract_type);
189 log_message(l_api_name || 'x_origination_code is ' || x_origination_code);
190 end if;
191
192 IF (x_origination_code = 'REQUISITION' AND
193 ((x_contract_type = 'BLANKET' AND x_source_reqs_flag = 'Y') OR
194 x_contract_type = 'STANDARD')) THEN
195
196 -- Call procedure to automatically allocate unallocated items
197 ALLOC_ALL_UNALLOC_ITEMS (p_auction_header_id,
198 x_allocation_result,
199 x_alloc_failure_reason,
200 x_line_number,
201 x_item_number,
202 x_item_description,
203 x_item_revision,
204 x_requisition_number,
205 x_job_name,
206 x_document_disp_line_number);
207
208 IF (x_allocation_result = 'FAILURE') THEN
209
210 if(l_debug_enabled = 'Y') then
211 x_progress := ++l_progress || l_api_name || ' failure after auto_alloc_and_split_req';
212 log_message(x_progress);
213 end if;
214
215 x_failure_status := 'FAILURE';
216 -- setting failure reason and item line on which allocation failure
217 -- occurred;
218 -- assigning error to x_allocation_error variable to be accessed after rollback to savepoint PON_BEFORE_AUTO_ALLOC
219 x_allocation_error := x_alloc_failure_reason;
220 ELSE
221
222 if(l_debug_enabled = 'Y') then
223 x_progress := ++l_progress || l_api_name || ' : now invoking split_req_lines for auction ' || p_auction_header_id;
224 log_message(x_progress);
225 end if;
226
227 -- Call procedure to split req lines and update
228 -- pon_award_allocations with split_req_id
229
230 SPLIT_REQ_LINES(p_auction_header_id,
231 x_split_result,
232 x_split_failure_reason,
233 x_line_number,
234 x_item_number,
235 x_item_description,
236 x_item_revision,
237 x_requisition_number,
238 x_job_name);
239
240 IF (x_split_result = 'FAILURE') THEN
241
242 if(l_debug_enabled = 'Y') then
243 x_progress := ++l_progress || l_api_name || ' : split_req_lines resulted in error for '
244 || p_auction_header_id;
245 log_message(x_progress);
246 end if;
247
248 x_failure_status := 'FAILURE';
249
250 -- setting failure reason and item line and req line on which
251 -- split failure occurred
252 -- assigning error to x_allocation_error variable to be accessed
253 -- after rollback to savepoint PON_BEFORE_AUTO_ALLOC
254 x_allocation_error := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SPLIT_ERROR') || ' - ' || x_split_failure_reason;
255
256 if(l_debug_enabled = 'Y') then
257 x_progress := ++l_progress || l_api_name || ' : alloc_error reported is '
258 || x_allocation_error;
259 log_message(x_progress);
260 end if;
261
262 END IF;
263 END IF;
264 END IF; -- end of automatic allocation and splitting
265
266 IF (x_failure_status = 'SUCCESS') THEN
267
268 if(l_debug_enabled = 'Y') then
269 x_progress := ++l_progress || l_api_name || ' : so far things are successful ' ;
270 log_message(x_progress);
271 end if;
272
273 IF (x_origination_code = 'REQUISITION') THEN
274 -- return req back to the pool for negotiation
275
276 if(l_debug_enabled = 'Y') then
277 x_progress := ++l_progress || l_api_name || ' : invoking cancel_negotiation_ref for auction ' || p_auction_header_id;
278 log_message(x_progress);
279 end if;
280
281 --Staggered Awarding project
282 select nvl(allow_staggered_awards, 'N') into l_allow_stag_awards from pon_auction_headers_all where auction_header_id = p_auction_header_id;
283
284 select count(DISTINCT line_number) into l_unawarded_line_count from pon_auction_item_prices_all where auction_header_id = p_auction_header_id and nvl(award_status,'NO') = 'NO';
285
286 if (l_unawarded_line_count > 0) then
287 l_has_unawarded_lines := 'Y';
288 else
289 l_has_unawarded_lines := 'N';
290 end if;
291
292 x_return_code := 'SUCCESS';
293 --Donot release the requisition lines for the remaining unawarded lines in case of Staggered Awarding
294 IF(l_allow_stag_awards <> 'Y' or l_has_unawarded_lines = 'N') THEN
295 PON_AUCTION_PKG.cancel_negotiation_ref(p_auction_header_id, x_return_code);
296 END IF;
297
298 IF (x_return_code = 'SUCCESS') THEN
299
300 if(l_debug_enabled = 'Y') then
301 x_progress := ++l_progress || l_api_name || ' : successful cancel_negotiation_ref for auction ' || p_auction_header_id;
302 log_message(x_progress);
303 end if;
304 p_resultout := 'SUCCESS';
305
306 ELSE
307 if(l_exception_enabled = 'Y') then
308 x_progress := ++l_progress || l_api_name || ' : failure cancel_negotiation_ref for auction ' || p_auction_header_id;
309 log_error(x_progress);
310 end if;
311
312 x_failure_status := 'FAILURE';
313 -- assigning error to x_allocation_error variable to be accessed
314 x_allocation_error := PON_AUCTION_PKG.getMessage('PON_AUC_WF_ALLOC_ERROR');
315
316 END IF;
317 END IF;
318 END IF;
319
320 IF (x_failure_status = 'FAILURE') THEN
321
322 if(l_exception_enabled = 'Y') then
323 x_progress := ++l_progress || l_api_name || ' : failure for auction ' || p_auction_header_id;
324 log_error(x_progress);
325 end if;
326
327
328 p_resultout := 'FAILURE';
329
330 -- call new procedure which sets attributes to generate failure e-mail
331 -- if fails, rollback to save point prior to auto allocation
332 -- double check the setting of alloc_error below
333
334
335 -- update outcome_status of auction
336 UPDATE PON_AUCTION_HEADERS_ALL
337 SET OUTCOME_STATUS = 'ALLOCATION_FAILED'
338 WHERE AUCTION_HEADER_ID = p_auction_header_id;
339
340 if(l_exception_enabled = 'Y') then
341 x_progress := ++l_progress || l_api_name || ' : update outcome_status for auction ' || p_auction_header_id;
342 log_error(x_progress);
343 end if;
344
345 END IF;
346
347 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
348
349 if(l_debug_enabled = 'Y') then
350 x_progress := ++l_progress || l_api_name || ' : END' ;
351 log_message(x_progress);
352 end if;
353
354 EXCEPTION
355 when others then
356
357 if(l_exception_enabled = 'Y') then
358 x_progress := ++l_progress || l_api_name || ' : exception for auction ' || p_auction_header_id;
359 log_error(x_progress);
360 end if;
361
362 p_resultout := 'FAILURE';
363
364 if(l_exception_enabled = 'Y') then
365 x_progress := ++l_progress || l_api_name || ' : set output to failure for auction ' || p_auction_header_id;
366 log_error(x_progress);
367 end if;
368
369 x_allocation_error := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || substrb(SQLERRM, 1, 500);
370
371 if(l_exception_enabled = 'Y') then
372 x_progress := ++l_progress || l_api_name || ' : for auction ' || p_auction_header_id || ' error:: ' || x_allocation_error;
373 log_error(x_progress);
374 end if;
375
376 -- update outcome_status of auction
377 UPDATE PON_AUCTION_HEADERS_ALL
378 SET OUTCOME_STATUS = 'ALLOCATION_FAILED'
379 WHERE AUCTION_HEADER_ID = p_auction_header_id;
380
381 if(l_exception_enabled = 'Y') then
382 x_progress := ++l_progress || l_api_name || ' : EXCEPTION END';
383 log_error(x_progress);
384 end if;
385
386 END AUTO_ALLOC_AND_SPLIT_REQ;
387
388 ----------------------------------------------------------------------
389 -- This procedure takes in an auction header id and calls
390 -- PON_AUCTION_CREATEPO_PKG.AUTO_REQ_ALLOCATION on all unallocated,
391 -- completed items that have awarded bids in this auction, and return a
392 -- 'success' or 'failure' as the allocation result, as well as the failure
393 -- reason if it failed.
394 -----------------------------------------------------------------------
395
396
397 procedure ALLOC_ALL_UNALLOC_ITEMS(p_auction_header_id IN NUMBER,
398 p_allocation_result OUT NOCOPY VARCHAR2,
399 p_failure_reason OUT NOCOPY VARCHAR2,
400 p_item_line_number OUT NOCOPY NUMBER,
401 p_item_number OUT NOCOPY VARCHAR2,
402 p_item_description OUT NOCOPY VARCHAR2,
403 p_item_revision OUT NOCOPY VARCHAR2,
404 p_requisition_number OUT NOCOPY VARCHAR2,
405 p_job_name OUT NOCOPY VARCHAR2,
406 p_document_disp_line_number OUT NOCOPY VARCHAR2) IS
407
408 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
409 x_result VARCHAR2(10);
410 x_error_message VARCHAR2(1000);
411
412 -- unallocatedItems are items that have awarded bids that have yet to be
413 -- allocated
414 CURSOR unallocatedItems IS
415 SELECT distinct itm.line_number
416 FROM pon_auction_item_prices_all itm,
417 po_req_lines_in_pool_src_v prlv
418 WHERE itm.auction_header_id = p_auction_header_id AND
419 nvl(itm.line_origination_code, 'NONE') = 'REQUISITION' AND
420 nvl(itm.allocation_status, 'NO') <> 'ALLOCATED' AND
421 nvl(itm.award_status, 'NO') = 'COMPLETED' AND
422 (Nvl(itm.clm_info_flag,'N') = 'Y' OR nvl(itm.awarded_quantity, -99) > 0) AND
423 itm.auction_header_id = prlv.auction_header_id AND
424 itm.line_number = prlv.auction_line_number;
425
426 -- The following cursor is to select line related info to be printed in the
427 -- Allocation Failure notification, if line fails to be auto-allocated.
428 CURSOR wf_item_cur (p_auction_header_id NUMBER, p_line_number NUMBER) IS
429 SELECT itm.document_disp_line_number, itm.item_number, itm.item_revision, itm.item_description,
430 itm.requisition_number, pjo.name
431 FROM pon_auction_item_prices_all itm,
432 per_jobs pjo
433 WHERE itm.auction_header_id = p_auction_header_id AND
434 itm.line_number = p_line_number AND
435 pjo.job_id (+) = itm.job_id;
436
437 l_api_name VARCHAR2(30) := ' ALLOC_ALL_UNALLOC_ITEMS ';
438 l_debug_enabled VARCHAR2(1) := 'N';
439 l_exception_enabled VARCHAR2(1) := 'N';
440 l_progress NUMBER := 0;
441
442 BEGIN
443
444 /* perform initialization for FND logging */
445 if(g_fnd_debug = 'Y') then
446
447 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
448 l_debug_enabled := 'Y';
449 end if;
450
451 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
452 l_exception_enabled := 'Y';
453 end if;
454
455 end if;
456
457 if(l_debug_enabled = 'Y') then
458 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
459 log_message(x_progress);
460 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auction_header_id;
461 log_message(x_progress);
462 end if;
463
464 OPEN unallocatedItems;
465 LOOP
466 FETCH unallocatedItems into p_item_line_number;
467 EXIT WHEN unallocatedItems%NOTFOUND;
468 -- call procedure to automatically allocate for a particular item
469
470 if(l_debug_enabled = 'Y') then
471 x_progress := ++l_progress || l_api_name || ' : invoke auto_req_allocation :';
472 log_message(x_progress);
473 end if;
474
475 AUTO_REQ_ALLOCATION(p_auction_header_id,
476 p_item_line_number,
477 x_result,
478 x_error_message);
479
480 IF (x_result = 'FAILURE') THEN
481
482 p_allocation_result := 'FAILURE';
483 p_failure_reason := x_error_message;
484
485 if(l_exception_enabled = 'Y') then
486 x_progress := ++l_progress || l_api_name || ' : failure after auto_req_allocation :' || x_error_message;
487 log_error(x_progress);
488 end if;
489
490 -- Fetch the item_number, item_revision, item_description,
491 -- requisition_number and job_name values for a given line_number.
492 OPEN wf_item_cur (p_auction_header_id, p_item_line_number);
493 FETCH wf_item_cur INTO p_document_disp_line_number,p_item_number, p_item_revision, p_item_description,
494 p_requisition_number, p_job_name;
495 CLOSE wf_item_cur;
496
497 RETURN;
498 ELSE
499 -- update allocation status of item to allocated if allocation succeeded
500 log_message( l_api_name || 'update allocation status of item to allocated. Line num - ' || p_item_line_number);
501 UPDATE pon_auction_item_prices_all
502 SET allocation_status = 'ALLOCATED'
503 WHERE auction_header_id = p_auction_header_id and
504 line_number = p_item_line_number;
505 END IF;
506 END LOOP;
507 p_allocation_result := 'SUCCESS';
508
509 if(l_debug_enabled = 'Y') then
510 x_progress := ++l_progress || l_api_name || ' : END :' || p_auction_header_id;
511 log_message(x_progress);
512 end if;
513
514
515
516 EXCEPTION
517 WHEN OTHERS THEN
518
519 p_allocation_result := 'FAILURE';
520
521 if(l_exception_enabled = 'Y') then
522 x_progress := ++l_progress || l_api_name || ' :EXCEPTION :' || p_auction_header_id;
523 log_error(x_progress);
524 end if;
525
526
527 IF p_item_line_number IS NULL THEN -- -- it means the exception was thrown before line information is selected
528 p_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500) || PON_AUCTION_PKG.getMessage('PON_LINE_INFO_NOT_AVAIL');
529 ELSE
530 p_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500);
531 END IF;
532
533 if(l_exception_enabled = 'Y') then
534 x_progress := ++l_progress || l_api_name || ' :EXCEPTION :' || p_failure_reason;
535 log_error(x_progress);
536 end if;
537
538 RAISE;
539
540 END ALLOC_ALL_UNALLOC_ITEMS;
541
542
543
544 PROCEDURE SPLIT_REQ_LINES(p_auction_header_id IN NUMBER,
545 p_split_result OUT NOCOPY VARCHAR2,
546 p_split_failure_reason OUT NOCOPY VARCHAR2,
547 p_item_line_number OUT NOCOPY NUMBER,
548 p_item_number OUT NOCOPY VARCHAR2,
549 p_item_description OUT NOCOPY VARCHAR2,
550 p_item_revision OUT NOCOPY VARCHAR2,
551 p_requisition_number OUT NOCOPY VARCHAR2,
552 p_job_name OUT NOCOPY VARCHAR2) IS
553
554 l_split_error_code VARCHAR2(10);
555 l_orig_req_line NUMBER;
556 l_req_qty NUMBER;
557 l_num_messages NUMBER;
558 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
559 l_req_line_id_col dbms_sql.number_table;
560 lock_not_acquired EXCEPTION;
561 l_user_id NUMBER;
562
563 PRAGMA EXCEPTION_INIT(lock_not_acquired, -54);
564
565 -- cursor picks up all req lines allocated to a single supplier where mrp
566 -- rescheduling has rescheduled the req quantity to a value lower than
567 -- the alloc quantity
568 CURSOR reqRescheduledSingleSupplier IS
569 SELECT paa.orig_req_line_id, prlsv.requisition_quantity
570 FROM pon_award_allocations paa, po_req_lines_in_pool_src_v prlsv,
571 pon_auction_item_prices_all paip
572 WHERE paa.allocated_qty > prlsv.requisition_quantity AND
573 paa.auction_header_id = p_auction_header_id AND
574 nvl(paa.split_req_line_id, -999)= -999 AND
575 nvl(paa.allocated_qty,0) > 0 AND
576 prlsv.requisition_line_id = paa.orig_req_line_id AND
577 prlsv.requisition_header_id = paa.orig_req_header_id AND
578 paip.auction_header_id = paa.auction_header_id AND
579 paip.line_number = paa.bid_line_number AND
580 paip.order_type_lookup_code IN ('AMOUNT', 'QUANTITY')
581 GROUP BY paa.orig_req_line_id, prlsv.requisition_quantity
582 HAVING COUNT(distinct bid_number) = 1;
583
584 -- cursor picks up all req lines allocated to multiple suppliers in
585 -- which the req qty is lower than the allocated qty as a result
586 -- of mrp rescheduling
587 CURSOR reqRescheduledMultSupplier IS
588 SELECT paa.orig_req_line_id
589 FROM pon_award_allocations paa, po_req_lines_in_pool_src_v prlsv,
590 pon_auction_item_prices_all paip
591 WHERE paa.auction_header_id = p_auction_header_id AND
592 nvl(paa.split_req_line_id, -999)= -999 AND
593 nvl(paa.allocated_qty,0) > 0 AND
594 prlsv.requisition_line_id = paa.orig_req_line_id AND
595 prlsv.requisition_header_id = paa.orig_req_header_id AND
596 paip.auction_header_id = paa.auction_header_id AND
597 paip.line_number = paa.bid_line_number AND
598 paip.order_type_lookup_code IN ('AMOUNT', 'QUANTITY')
599 GROUP BY paa.orig_req_line_id
600 HAVING SUM (nvl(paa.allocated_qty,0)) > max(prlsv.requisition_quantity) -- Clin Slin- Will work for info lines. SELECT Max(NULL) FROM dual gives null and comparision of null will gives false
601 AND COUNT(distinct bid_number) > 1;
602
603 -- The following cursor is to select line related info to be printed in the
604 -- Allocation Failure notification, if line fails to be auto-allocated.
605 CURSOR wf_item_cur (p_auction_header_id NUMBER, p_orig_req_line_id NUMBER) IS
606 SELECT distinct paa.bid_line_number, itm.item_number, itm.item_revision, itm.item_description,
607 itm.requisition_number, pjo.name
608 FROM pon_auction_item_prices_all itm,
609 per_jobs pjo,
610 pon_award_allocations paa
611 WHERE paa.auction_header_id = itm.auction_header_id AND
612 paa.bid_line_number = itm.line_number AND
613 paa.orig_req_line_id = p_orig_req_line_id AND
614 itm.auction_header_id = p_auction_header_id AND
615 pjo.job_id (+) = itm.job_id;
616
617 l_api_name VARCHAR2(30) := ' SPLIT_REQ_LINES ';
618 l_debug_enabled VARCHAR2(1) := 'N';
619 l_exception_enabled VARCHAR2(1) := 'N';
620 l_progress NUMBER := 0;
621
622 l_is_neg_federal NUMBER := 0; --13584934 : To hold is federal value
623
624
625 BEGIN
626
627 /* perform initialization for FND logging */
628 if(g_fnd_debug = 'Y') then
629
630 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
631 l_debug_enabled := 'Y';
632 end if;
633
634 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
635 l_exception_enabled := 'Y';
636 end if;
637
638 end if;
639
640 if(l_debug_enabled = 'Y') then
641 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
642 log_message(x_progress);
643 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auction_header_id;
644 log_message(x_progress);
645 end if;
646
647
648 p_split_result := 'SUCCESS';
649
650 /* Bug : 13584934 : Donot call split api in case of clm. */
651 l_is_neg_federal := PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id);
652
653
654 /*
655 rrkulkar-large-auction-support :- need to pass USER_ID
656
657 out-params-setitemattr
658
659 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
660 itemkey => itemkey,
661 aname => 'USER_ID');
662
663 */
664
665
666
667 -- get lock on all backing reqs for a negotiation. If it cannot
668 -- be locked, try 20 rtimes, then set result to failure and
669 -- set appropriate error message before returning and exiting procedure
670 FOR l_index IN 1..20 LOOP
671 BEGIN
672 SELECT requisition_line_id
673 BULK COLLECT INTO l_req_line_id_col
674 FROM po_requisition_lines_all
675 WHERE auction_header_id = p_auction_header_id
676 FOR UPDATE NOWAIT;
677 EXIT;
678 EXCEPTION
679 WHEN lock_not_acquired THEN
680 IF l_index >= 20 THEN
681 p_split_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SPLIT_ERROR') || ' - ' || PON_AUCTION_PKG.getMessage('PON_AUC_CANNOT_GET_LOCK') || PON_AUCTION_PKG.getMessage('PON_LINE_INFO_NOT_AVAIL');
682 p_split_result := 'FAILURE';
683
684 RETURN;
685 END IF;
686 END;
687 END LOOP;
688
689 IF (p_split_result = 'SUCCESS') THEN
690 -- if any req line allocated to multiple suppliers has been rescheduled
691 -- fail the process and include failure reason in e-mail
692 OPEN reqRescheduledMultSupplier;
693 LOOP
694 FETCH reqRescheduledMultSupplier
695 INTO l_orig_req_line;
696 EXIT WHEN reqRescheduledMultSupplier%NOTFOUND;
697
698 p_split_result := 'FAILURE';
699 --p_split_failed_req := l_orig_req_line;
700 p_split_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SPLIT_ERROR') || ' - ' || PON_AUCTION_PKG.getMessage('PON_AUC_WF_REQ_RESCHEDULED');
701
702 -- Fetch the line_number, item_number, item_revision, item_description,
703 -- requisition_number and job_name values for a given line_number.
704 OPEN wf_item_cur (p_auction_header_id, l_orig_req_line);
705 FETCH wf_item_cur INTO p_item_line_number, p_item_number, p_item_revision, p_item_description,
706 p_requisition_number, p_job_name;
707 CLOSE wf_item_cur;
708
709 END LOOP;
710 CLOSE reqRescheduledMultSupplier;
711 END IF;
712
713 IF (p_split_result = 'SUCCESS') THEN
714
715 if(l_debug_enabled = 'Y') then
716 x_progress := ++l_progress || l_api_name || ' : SUCCESS in split_result so far:';
717 log_message(x_progress);
718 end if;
719
720 -- if req line allocated to single supplier has been rescheduled
721 -- simply decrease allocated qty to new req qty
722
723 OPEN reqRescheduledSingleSupplier;
724 LOOP
725
726 if(l_debug_enabled = 'Y') then
727 x_progress := ++l_progress || l_api_name || ' : looping over reqRescheduledSingleSupplier:';
728 log_message(x_progress);
729 end if;
730
731 FETCH reqRescheduledSingleSupplier
732 INTO l_orig_req_line, l_req_qty;
733 EXIT WHEN reqRescheduledSingleSupplier%NOTFOUND;
734
735 UPDATE PON_AWARD_ALLOCATIONS
736 SET allocated_qty = l_req_qty,
737 last_update_date = sysdate,
738 last_updated_by = l_user_id
739 WHERE orig_req_line_id = l_orig_req_line;
740
741 END LOOP;
742 CLOSE reqRescheduledSingleSupplier;
743
744 /* Bug : 13584934 : For clm documents, we are not supporting split. There
745 * can be cases where same req line is consumed by multiple bid lines. So
746 * for clm always populate -1 as bid number and bid_line_number.
747 */
748 IF l_is_neg_federal = 1 THEN
749 UPDATE po_requisition_lines_all prl
750 set bid_number = -1, bid_line_number = -1
751 where prl.requisition_line_id in
752 (select distinct orig_req_line_id
753 from pon_award_allocations alloc
754 where alloc.auction_header_id = p_auction_header_id);
755
756 if(l_debug_enabled = 'Y') then
757 x_progress := ++l_progress || l_api_name || ' : Federal document : Updated po_requisition_lines_all with -1 :';
758 log_message(x_progress);
759 END IF;
760 p_split_result := FND_API.G_RET_STS_SUCCESS;
761 ELSE
762
763 -- Insert values into po's split temp global table
764 INSERT INTO po_req_split_lines_GT (
765 auction_header_id,
766 bid_number,
767 bid_line_number,
768 requisition_header_id,
769 requisition_line_id,
770 allocated_qty
771 )
772 SELECT paa.auction_header_id,
773 paa.bid_number,
774 paa.bid_line_number,
775 paa.orig_req_header_id,
776 paa.orig_req_line_id,
777 paa.allocated_qty
778 FROM pon_award_allocations paa
779 WHERE paa.auction_header_id = p_auction_header_id AND
780 nvl(paa.split_req_line_id, -999)= -999 AND
781 nvl(paa.allocated_qty,0) > 0;
782
783 -- DEBUG CODE
784 -- INSERT INTO po_req_split_lines_gt_debug (SELECT * FROM po_req_split_lines_gt WHERE auction_header_id = p_auction_header_id);
785
786 if(l_debug_enabled = 'Y') then
787 x_progress := ++l_progress || l_api_name || ' : invoke po_negotiations4_grp.split_requisitionlines :';
788 log_message(x_progress);
789 end if;
790
791 -- calling PO's split req api
792 -- passing in api_version, init_msg_list, commit_data,
793 -- auction_header_id
794 -- registering out result, error, num_msgs, error_msg, failed req
795 -- bug 3955102 - invoke API by names, not index
796 PO_NEGOTIATIONS4_GRP.Split_RequisitionLines(
797 P_API_VERSION => 1.0,
798 P_INIT_MSG_LIST => FND_API.G_FALSE,
799 P_COMMIT => FND_API.G_FALSE,
800 X_RETURN_STATUS => p_split_result,
801 X_MSG_COUNT => l_num_messages,
802 X_MSG_DATA => p_split_failure_reason,
803 P_AUCTION_HEADER_ID => p_auction_header_id);
804
805
806 if(l_debug_enabled = 'Y') then
807 x_progress := ++l_progress || l_api_name || ' : return from po_negotiations4_grp.split_requisitionlines :' || p_split_failure_reason;
808 log_message(x_progress);
809 end if;
810
811 END IF; --End if is_neg_federal
812
813 END IF; -- End if p_split_result = 'SUCCESS'
814
815 -- If successful, insert values back into sourcing's table
816 --
817 IF (p_split_result = FND_API.G_RET_STS_SUCCESS) THEN
818
819 if(l_debug_enabled = 'Y') then
820 x_progress := ++l_progress || l_api_name || ' : p_split_result is successful :';
821 log_message(x_progress);
822 end if;
823
824 UPDATE PON_AWARD_ALLOCATIONS PAA
825 SET split_req_line_id=
826 (select new_req_line_id
827 from po_req_split_lines_gt prlst
828 where prlst.requisition_line_id = PAA.orig_req_line_id
829 and prlst.auction_header_id = PAA.auction_header_id
830 and prlst.bid_number = PAA.bid_number
831 and prlst.bid_line_number = PAA.bid_line_number
832 and prlst.record_status in ('S', 'E', 'T')),
833 -- status in s and e means newly split lines and lines
834 -- with equal allocation
835 last_update_date = sysdate,
836 last_updated_by = l_user_id
837 WHERE PAA.auction_header_id = p_auction_header_id AND
838 nvl(paa.split_req_line_id, -999)= -999 AND
839 nvl(paa.allocated_qty,0) > 0;
840
841 if(l_debug_enabled = 'Y') then
842 x_progress := ++l_progress || l_api_name || ' : after updating pon_award_allocations with split_req_line_id :';
843 log_message(x_progress);
844 end if;
845
846
847 p_split_result := 'SUCCESS';
848
849 -- If unsuccessful, determine the item number on which the split
850 -- req failed based on the failed req number
851 ELSE
852
853 if(l_debug_enabled = 'Y') then
854 x_progress := ++l_progress || l_api_name || ' : not successful so far :' || p_split_result;
855 log_message(x_progress);
856 end if;
857
858 -- bug 3537686: if there is a message to be returned,
859 -- po populates the message w/ encoded instead of decoded message.
860 -- Here, we will retrieve the last error message to display to user.
861 -- in decoded format
862
863 IF (l_num_messages > 0) THEN
864 p_split_failure_reason := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
865 END IF;
866
867 -- if unexpected error, append the text 'Unexpected System Error' to
868 -- error message
869 IF (p_split_result = FND_API.G_RET_STS_UNEXP_ERROR) THEN
870 p_split_failure_reason := PON_AUCTION_PKG.getMessage('PON_UNEXPECTED_ERROR') || ': ' || p_split_failure_reason;
871 END IF;
872
873 p_split_result := 'FAILURE';
874
875 if(l_debug_enabled = 'Y') then
876 x_progress := ++l_progress || l_api_name || ' : failure :' || p_split_failure_reason ;
877 log_message(x_progress);
878 end if;
879
880 -- Fetch the line_number, item_number, item_revision, item_description,
881 -- requisition_number and job_name values for a given line_number.
882 OPEN wf_item_cur (p_auction_header_id, l_orig_req_line);
883 FETCH wf_item_cur INTO p_item_line_number, p_item_number, p_item_revision, p_item_description,
884 p_requisition_number, p_job_name;
885 CLOSE wf_item_cur;
886
887 END IF;
888
889 if(l_debug_enabled = 'Y') then
890 x_progress := ++l_progress || l_api_name || ' : END :';
891 log_message(x_progress);
892 end if;
893
894
895 EXCEPTION
896 WHEN OTHERS THEN
897 p_split_result := 'FAILURE';
898
899 if(l_exception_enabled = 'Y') then
900 x_progress := ++l_progress || l_api_name || ' : EXCEPTION :';
901 log_error(x_progress);
902 end if;
903
904
905 IF p_item_line_number IS NULL THEN -- -- it means the exception was thrown before line information is selected
906 p_split_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500) || PON_AUCTION_PKG.getMessage('PON_LINE_INFO_NOT_AVAIL');
907 ELSE
908 p_split_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500);
909 END IF;
910
911 if(l_exception_enabled = 'Y') then
912 x_progress := ++l_progress || l_api_name || ' : EXCEPTION with reason :' || p_split_failure_reason;
913 log_error(x_progress);
914 end if;
915
916 RAISE;
917
918 END SPLIT_REQ_LINES;
919
920 -- This procedure allocates the award quantity across the backing requisition
921 -- distributions for a particular item. It does this by ordering the
922 -- requisition distributions and Awarded Suppliers in a predetermined way and
923 -- then fulfilling the requisition demand one by one with the supplier's award
924 -- quantity in a FIFO manner. The ordering is as follows. Requisitions are
925 -- ordered by need_by_date ascending, then creation_date ascending. Awarded
926 -- Suppliers are ordered by promise date ascending, awarded quantity
927 -- descending, bid price ascending, then bid number ascending for standard
928 -- purchase orders. In the case of blanket agreements, promise
929 -- date is implicitly excluded from the ordering, as it will be null.
930
931
932
933 PROCEDURE Auto_Req_Allocation(p_auctionID IN NUMBER,
934 p_line_number IN NUMBER,
935 p_result OUT NOCOPY VARCHAR2,
936 p_error_message OUT NOCOPY VARCHAR2) IS
937
938 l_qty_allocated NUMBER;
939 l_insert_cursor NUMBER;
940 l_insert_result NUMBER;
941 l_reqIdx NUMBER;
942 l_currentReqIdx NUMBER;
943 l_insert_index NUMBER;
944 l_count NUMBER;
945 l_bid_number_col dbms_sql.number_table;
946 l_award_col dbms_sql.number_table;
947 l_req_line_id_col dbms_sql.number_table;
948 l_req_header_id_col dbms_sql.number_table;
949 l_req_quantity_col dbms_sql.number_table;
950 l_req_new_quantity_col dbms_sql.number_table;
951 l_bid_number_insertcol dbms_sql.number_table;
952 l_bid_line_number_insertcol dbms_sql.number_table;
953 l_orig_req_line_insertcol dbms_sql.number_table;
954 l_orig_req_header_insertcol dbms_sql.number_table;
955 l_allocated_qty_insertcol dbms_sql.number_table;
956 l_auction_header_id_insertcol dbms_sql.number_table;
957 -- Clin Slin Changes
958 --l_split_req_line_id_insertcol dbms_sql.number_table;
959 l_clm_info_flag dbms_sql.number_table;
960
961 l_empty_table dbms_sql.number_table;
962 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
963 l_user_id NUMBER;
964 l_login_id NUMBER;
965 l_bid_price_col dbms_sql.number_table;
966 l_order_type_lookup_code pon_auction_item_prices_all.order_type_lookup_code%TYPE;
967 l_purchase_basis pon_auction_item_prices_all.purchase_basis%TYPE;
968 l_contract_type pon_auction_headers_all.contract_type%TYPE;
969
970
971 l_api_name VARCHAR2(30) := ' AUTO_REQ_ALLOCATION ';
972 l_debug_enabled VARCHAR2(1) := 'N';
973 l_exception_enabled VARCHAR2(1) := 'N';
974 l_progress NUMBER := 0;
975
976 BEGIN
977
978 /* perform initialization for FND logging */
979 if(g_fnd_debug = 'Y') then
980
981 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
982 l_debug_enabled := 'Y';
983 end if;
984
985 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
986 l_exception_enabled := 'Y';
987 end if;
988
989 end if;
990
991 if(l_debug_enabled = 'Y') then
992 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
993 log_message(x_progress);
994 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auctionID || ' ' || p_line_number;
995 log_message(x_progress);
996
997 end if;
998
999
1000 /* empty tables to prevent data corruption*/
1001 l_bid_number_col := l_empty_table;
1002 l_award_col := l_empty_table;
1003 l_req_line_id_col := l_empty_table;
1004 l_req_header_id_col := l_empty_table;
1005 l_req_quantity_col := l_empty_table;
1006 l_req_new_quantity_col := l_empty_table;
1007 l_bid_number_insertcol := l_empty_table;
1008 l_bid_line_number_insertcol := l_empty_table;
1009 l_orig_req_line_insertcol := l_empty_table;
1010 l_orig_req_header_insertcol := l_empty_table;
1011 l_allocated_qty_insertcol := l_empty_table;
1012 l_auction_header_id_insertcol := l_empty_table;
1013 l_bid_price_col := l_empty_table;
1014 -- l_split_req_line_id_insertcol := l_empty_table;
1015 l_clm_info_flag := l_empty_table;
1016
1017 SELECT paip.order_type_lookup_code, paip.purchase_basis, pah.contract_type
1018 INTO l_order_type_lookup_code, l_purchase_basis, l_contract_type
1019 FROM pon_auction_item_prices_all paip, pon_auction_headers_all pah
1020 WHERE paip.auction_header_id = pah.auction_header_id
1021 AND paip.auction_header_id = p_auctionid
1022 AND paip.line_number = p_line_number;
1023
1024 -- Bulk collect req info into collection table.
1025
1026 SELECT requisition_line_id, requisition_header_id, requisition_quantity
1027 BULK COLLECT INTO l_req_line_id_col, l_req_header_id_col,
1028 l_req_quantity_col
1029 FROM po_req_lines_in_pool_src_v
1030 WHERE auction_header_id = p_auctionID AND
1031 auction_line_number = p_line_number AND
1032 nvl(modified_by_agent_flag, 'N') <> 'Y'
1033 ORDER BY need_by_date ASC, creation_date ASC;
1034
1035
1036 -- bulk collect the supplier and award info. In the case of amount based
1037 -- lines, the procedure will pick up bid_currency_unit_price.
1038 -- Otherwise, the procedure will pick up the award_quantity.
1039
1040 SELECT decode(itm.order_type_lookup_code, 'AMOUNT',
1041 bl.bid_currency_unit_price,
1042 bl.award_quantity) quantity,
1043 bh.bid_number,
1044 bl.bid_currency_unit_price,
1045 Decode(Nvl(itm.clm_info_flag,'N'), 'Y', 1, 0) AS clm_info_indicator
1046 BULK COLLECT INTO l_award_col, l_bid_number_col, l_bid_price_col , l_clm_info_flag
1047 FROM pon_auction_item_prices_all itm,
1048 pon_bid_item_prices bl,
1049 pon_bid_headers bh,
1050 pon_auction_headers_all pah
1051 WHERE itm.auction_header_id = p_auctionID AND
1052 itm.line_number = p_line_number AND
1053 bl.line_number = itm.line_number AND
1054 bl.auction_header_id = itm.auction_header_id AND
1055 nvl(bl.award_status,'NO') = 'AWARDED' AND
1056 bh.bid_number = bl.bid_number AND
1057 bh.auction_header_id = itm.auction_header_id AND
1058 nvl(bh.bid_status,'NONE') = 'ACTIVE'AND
1059 pah.auction_header_id = itm.auction_header_id
1060 ORDER BY bl.promised_date ASC, decode(pah.contract_type, 'BLANKET', 1, bl.award_quantity) DESC,
1061 bl.bid_currency_price ASC,
1062 bl.publish_date ASC;
1063
1064
1065
1066
1067 l_insert_index := 1;
1068
1069 -- implements actual allocation algorithm described above
1070
1071 FOR bidIdx IN 1..l_bid_number_col.COUNT LOOP
1072
1073 if(l_debug_enabled = 'Y') then
1074 x_progress := ++l_progress || l_api_name || 'bid award quantity: ' || l_award_col(bidIdx) || 'length of req array: '|| l_req_line_id_col.COUNT;
1075 log_message(x_progress);
1076 end if;
1077
1078 FOR l_reqIdx IN 1..l_req_line_id_col.COUNT LOOP
1079
1080 if(l_debug_enabled = 'Y') then
1081 x_progress := ++l_progress || l_api_name || 'req index: ' || l_reqIdx || ' req quantity: ' || l_req_quantity_col(l_reqIdx);
1082 log_message(x_progress);
1083 end if;
1084
1085
1086 -- IF ('BLANKET' = l_contract_type AND
1087 -- ('QUANTITY' = l_order_type_lookup_code OR
1088 -- 'AMOUNT' = l_order_type_lookup_code)) THEN
1089 -- IF (bidIdx = 1) THEN
1090 -- Full allocation goes to first bidder
1091 -- l_qty_allocated := l_req_quantity_col(l_reqIdx);
1092 -- ELSE
1093 -- l_qty_allocated := 0;
1094 -- END IF;
1095 -- ELS
1096
1097 IF ('RATE' = l_order_type_lookup_code OR
1098 'FIXED PRICE' = l_order_type_lookup_code) THEN
1099 IF (bidIdx = 1) THEN
1100 -- Allocation goes to first bidder
1101 l_qty_allocated := 1;
1102 ELSE
1103 l_qty_allocated := 0;
1104 END IF;
1105 ELSIF (l_req_quantity_col(l_reqIdx) = 0) THEN
1106 l_qty_allocated := 0;
1107 ELSIF (l_award_col(bidIdx) = 0) THEN
1108 l_qty_allocated := 0;
1109 -- If award quantity smaller than req quantity, alloc quantity
1110 -- will be the award quantity
1111 ELSIF (l_award_col(bidIdx) < l_req_quantity_col(l_reqIdx)) THEN
1112 l_qty_allocated := l_award_col(bidIdx);
1113 -- if award quantity equal to req quantity or if award quantity
1114 -- greater than req quantity
1115 ELSE
1116 l_qty_allocated := l_req_quantity_col(l_reqIdx);
1117 END IF;
1118
1119 x_progress := '25: Auto_Req_Allocation: ' || 'qty allocated: ' || l_qty_allocated;
1120 log_message(x_progress);
1121
1122 l_award_col(bidIdx) := l_award_col(bidIdx) - l_qty_allocated;
1123 l_req_quantity_col(l_reqIdx) := l_req_quantity_col(l_reqIdx) - l_qty_allocated;
1124
1125 -- insert into collection object for bulk insert later
1126 l_bid_number_insertcol(l_insert_index) := l_bid_number_col(bidIdx);
1127 l_bid_line_number_insertcol(l_insert_index) := p_line_number;
1128 l_orig_req_line_insertcol(l_insert_index) := l_req_line_id_col(l_reqIdx);
1129 l_orig_req_header_insertcol(l_insert_index) := l_req_header_id_col(l_reqIdx);
1130 l_allocated_qty_insertcol(l_insert_index) := l_qty_allocated;
1131 l_clm_info_flag(l_insert_index) := l_clm_info_flag(1);
1132
1133
1134 x_progress := '30: Auto_Req_Allocation: ' || 'qty allocated: ' || l_allocated_qty_insertcol(l_insert_index) || ' index: ' || l_insert_index;
1135 log_message(x_progress);
1136 x_progress := '31: Auto_Req_Allocation: ' || 'bid number: ' || l_bid_number_insertcol(l_insert_index) || ' bid line number : ' || l_bid_line_number_insertcol(l_insert_index)
1137 || 'req line id: ' || l_orig_req_line_insertcol(l_insert_index) || 'clm_info ' || l_clm_info_flag(l_insert_index);
1138 log_message(x_progress);
1139
1140
1141 l_auction_header_id_insertcol(l_insert_index) := p_auctionID;
1142 l_insert_index := l_insert_index + 1;
1143
1144 -- Clin Slin Changes :
1145 /* IF l_clm_info_flag(l_reqIdx) = 1 THEN
1146 l_split_req_line_id_insertcol(l_insert_index) := l_req_line_id_col(l_reqIdx);
1147 ELSE
1148 l_split_req_line_id_insertcol(l_insert_index) := null;
1149 END IF;
1150 */
1151
1152 END LOOP;
1153 END LOOP;
1154
1155 x_progress := '33: Right before Bulk Insert';
1156 log_message(x_progress);
1157
1158
1159 l_user_id := fnd_global.user_id;
1160 l_login_id := fnd_global.login_id;
1161
1162 -- doing bulk insert
1163 FORALL l_count IN 1..l_bid_number_insertcol.COUNT
1164 INSERT INTO pon_award_allocations(bid_number, bid_line_number, orig_req_line_id, orig_req_header_id, allocated_qty, split_req_line_id, auction_header_id, created_by, last_update_date, last_updated_by, last_update_login, creation_date)
1165 VALUES(l_bid_number_insertcol(l_count),
1166 l_bid_line_number_insertcol(l_count),
1167 l_orig_req_line_insertcol(l_count),
1168 l_orig_req_header_insertcol(l_count),
1169 l_allocated_qty_insertcol(l_count),
1170 Decode(l_clm_info_flag(l_count), 1 , l_orig_req_line_insertcol(l_count), NULL),
1171 --l_split_req_line_id_insertcol(l_count),
1172 l_auction_header_id_insertcol(l_count),
1173 l_user_id,
1174 sysdate,
1175 l_user_id,
1176 l_login_id,
1177 sysdate);
1178 -- end of bulk insert
1179
1180 x_progress := '35: Auto_Req_Allocation: ' || 'qty allocated: ' || l_allocated_qty_insertcol(l_bid_number_insertcol.COUNT);
1181 log_message(x_progress);
1182
1183 p_result := 'SUCCESS';
1184
1185 EXCEPTION
1186 WHEN OTHERS THEN
1187 p_result := 'FAILURE';
1188 p_error_message := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500);
1189 log_message(x_progress);
1190 END Auto_Req_Allocation;
1191
1192
1193 -- This procedure is called by NegotiationDoc.startPOCreation. It kicks off
1194 -- the po creation workflow and sets up the wf attributes
1195 PROCEDURE START_PO_WORKFLOW(p_auction_header_id IN NUMBER, -- 1
1196 p_user_name IN VARCHAR2, -- 2
1197 p_user_id IN NUMBER, -- 3
1198 p_formatted_name IN VARCHAR2, -- 4
1199 p_auction_title IN VARCHAR2, -- 5
1200 p_organization_name IN VARCHAR2,
1201 p_email_type IN VARCHAR2,
1202 p_itemkey IN VARCHAR2,
1203 x_allocation_error OUT NOCOPY VARCHAR2,
1204 x_line_number OUT NOCOPY NUMBER,
1205 x_item_number OUT NOCOPY VARCHAR2,
1206 x_item_description OUT NOCOPY VARCHAR2,
1207 x_item_revision OUT NOCOPY VARCHAR2,
1208 x_requisition_number OUT NOCOPY VARCHAR2,
1209 x_job_name OUT NOCOPY VARCHAR2,
1210 x_document_disp_line_number OUT NOCOPY VARCHAR2) IS -- 6
1211
1212 x_itemkey wf_items.ITEM_KEY%TYPE;
1213 x_itemtype wf_items.ITEM_TYPE%TYPE;
1214
1215 x_progress VARCHAR2(4000);
1216 x_language_code VARCHAR2(3);
1217 x_msg_suffix VARCHAR2(3) := '';
1218 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
1219 x_doctype_id pon_auction_headers_all.doctype_id%TYPE;
1220 x_responsibility_id NUMBER;
1221 x_application_id NUMBER;
1222 x_doc_number_dsp VARCHAR2(60);
1223 x_contract_type pon_auction_headers_all.contract_type%TYPE;
1224 x_current_round NUMBER;
1225
1226 x_timezone VARCHAR2(80);
1227 x_newstarttime DATE;
1228 x_newendtime DATE;
1229 x_newpreviewtime DATE;
1230 x_oex_timezone VARCHAR2(80);
1231 x_timezone_disp VARCHAR2(240);
1232 p_open_bidding_date date;
1233 p_close_bidding_date date;
1234 p_trading_partner_contact_id number;
1235 x_award_summary_url_buyer VARCHAR2(2000);
1236 x_alloc_summary_url_buyer VARCHAR2(2000);
1237 x_alloc_byitem_url_buyer VARCHAR2(2000);
1238 x_po_summary_url_buyer VARCHAR2(2000);
1239 p_doctype_id PON_AUCTION_HEADERS_ALL.DOCTYPE_ID%TYPE;
1240 p_trading_partner_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
1241 p_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
1242 x_purchase_order VARCHAR2(30);
1243 x_purchase_orders VARCHAR2(30);
1244 p_preview_date DATE;
1245 x_requistion_based VARCHAR2(12);
1246 x_has_items PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
1247
1248
1249 l_api_name VARCHAR2(30) := ' START_PO_WORKFLOW ';
1250 l_debug_enabled VARCHAR2(1) := 'N';
1251 l_exception_enabled VARCHAR2(1) := 'N';
1252 l_progress NUMBER := 0;
1253
1254 BEGIN
1255
1256 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(p_user_name, x_language_code);
1257
1258 select open_bidding_date,
1259 close_bidding_date,
1260 trading_partner_contact_id,
1261 doctype_id,
1262 trading_partner_name,
1263 trading_partner_contact_name,
1264 has_items_flag
1265 into p_open_bidding_date,
1266 p_close_bidding_date,
1267 p_trading_partner_contact_id,
1268 p_doctype_id,
1269 p_trading_partner_name,
1270 p_trading_partner_contact_name,
1271 x_has_items
1272 from pon_auction_headers_all
1273 where auction_header_id = p_auction_header_id;
1274
1275 x_itemkey := p_itemkey;
1276 x_itemtype:= 'PONCOMPL';
1277
1278 x_progress := '10: START_PO_WORKFLOW: Called with following parameters: ' ||
1279 'ItemType = ' || x_itemType || ', ' ||
1280 'ItemKey = ' || x_itemKey || ', ' ||
1281 'auction_header_id = ' || p_auction_header_id || ', ' ||
1282 'user_name = ' || p_user_name || ', ' ||
1283 'user_id = ' || p_user_id || ', ' ||
1284 'formatted_name = ' || p_formatted_name || ', ' ||
1285 'auction_title = ' || p_auction_title || ', ' ||
1286 'organization_name = ' || p_organization_name;
1287
1288 log_message(x_itemtype || ' ' || x_progress);
1289
1290 wf_engine.CreateProcess(itemtype => x_itemtype,
1291 itemkey => x_itemkey,
1292 process => 'PO_CREATION_ENGINE');
1293
1294 x_progress := '20: START_PO_WORKFLOW: Just after CreateProcess';
1295
1296 log_message(x_itemtype || ' ' || x_progress);
1297
1298 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
1299
1300
1301
1302 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1303 itemkey => x_itemkey,
1304 aname => 'AUCTION_START_DATE',
1305 avalue => p_open_bidding_date);
1306
1307 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1308 itemkey => x_itemkey,
1309 aname => 'AUCTION_END_DATE',
1310 avalue => p_close_bidding_date);
1311
1312 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1313 itemkey => x_itemkey,
1314 aname => 'PREVIEW_DATE',
1315 avalue => p_preview_date);
1316
1317 -- new item attribute to hold the document type id. Item attribute value is going
1318 -- to be used as a parameter to Allocation by Item and Allocation Summary pages
1319 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1320 itemkey => x_itemkey,
1321 aname => 'DOCTYPE_ID',
1322 avalue => p_doctype_id);
1323
1324 --
1325 -- Get the exchange's time zone
1326 --
1327
1328 x_oex_timezone := pon_auction_pkg.Get_Oex_Time_Zone;
1329
1330 --
1331 -- Get the user's time zone
1332 --
1333 x_timezone := pon_auction_pkg.Get_Time_Zone(p_trading_partner_contact_id);
1334
1335 --
1336 -- Make sure that it is a valid time zone
1337 --
1338
1339 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(x_timezone) = 0) THEN
1340 x_timezone := x_oex_timezone;
1341 END IF;
1342
1343 --
1344 -- Convert the dates to the user's timezone.
1345 --
1346
1347 x_newstarttime := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(p_open_bidding_date,x_oex_timezone,x_timezone);
1348 x_newendtime := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(p_close_bidding_date,x_oex_timezone,x_timezone);
1349 x_newpreviewtime := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(p_preview_date,x_oex_timezone,x_timezone);
1350
1351 x_timezone_disp:= pon_auction_pkg.Get_TimeZone_Description(x_timezone, x_language_code);
1352
1353 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1354 itemkey => x_itemkey,
1355 aname => 'TP_TIME_ZONE',
1356 avalue => x_timezone_disp);
1357
1358 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1359 itemkey => x_itemkey,
1360 aname => 'AUCTION_START_DATE_TZ',
1361 avalue => x_newstarttime);
1362
1363 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1364 itemkey => x_itemkey,
1365 aname => 'AUCTION_END_DATE_TZ',
1366 avalue => x_newendtime);
1367
1368
1369 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1370 itemkey => x_itemkey,
1371 aname => 'AUCTION_PO_EMAIL_TYPE',
1372 avalue => p_email_type);
1373
1374 IF (p_preview_date IS NULL) THEN
1375 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1376 itemkey => x_itemkey,
1377 aname => 'PREVIEW_DATE_TZ',
1378 avalue => null);
1379
1380 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1381 itemkey => x_itemkey,
1382 aname => 'TP_TIME_ZONE1',
1383 avalue => null);
1384
1385 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1386 itemkey => x_itemkey,
1387 aname => 'PREVIEW_DATE_NOTSPECIFIED',
1388 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_PREVIEW_DATE_NOTSPEC',x_msg_suffix));
1389 ELSE
1390 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1391 itemkey => x_itemkey,
1392 aname => 'PREVIEW_DATE_TZ',
1393 avalue => x_newpreviewtime);
1394
1395 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1396 itemkey => x_itemkey,
1397 aname => 'TP_TIME_ZONE1',
1398 avalue => x_timezone_disp);
1399
1400 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1401 itemkey => x_itemkey,
1402 aname => 'PREVIEW_DATE_NOTSPECIFIED',
1403 avalue => null);
1404 END IF;
1405
1406
1407 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1408 itemkey => x_itemkey,
1409 aname => 'AUCTION_ID',
1410 avalue => p_auction_header_id);
1411
1412
1413 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1414 itemkey => x_itemkey,
1415 aname => 'USER_NAME',
1416 avalue => p_user_name);
1417
1418 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1419 itemkey => x_itemkey,
1420 aname => 'USER_ID',
1421 avalue => p_user_id);
1422
1423 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1424 itemkey => x_itemkey,
1425 aname => 'CONTACT_NAME',
1426 avalue => p_formatted_name || ',');
1427
1428
1429 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1430 itemkey => x_itemkey,
1431 aname => 'AUCTION_TITLE',
1432 avalue => PON_AUCTION_PKG.replaceHtmlChars(p_auction_title));
1433
1434 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1435 itemkey => x_itemkey,
1436 aname => 'HAS_ITEMS_FLAG',
1437 avalue => x_has_items);
1438
1439 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1440 itemkey => x_itemkey,
1441 aname => 'ORGANIZATION_NAME',
1442 avalue => p_organization_name);
1443
1444 -- call to notification utility package to get the redirect page url that
1445 -- is responsible for getting the Award Summary url and forward to it.
1446 x_award_summary_url_buyer := pon_wf_utl_pkg.get_dest_page_url (
1447 p_dest_func => 'PON_AWARD_SUMM'
1448 ,p_notif_performer => 'BUYER');
1449
1450 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1451 itemkey => x_itemkey,
1452 aname => 'AWARD_SUMMARY_URL',
1453 avalue => x_award_summary_url_buyer);
1454
1455
1456 -- call to notification utility package to get the redirect page url that
1457 -- is responsible for getting the purchase order summary url and forward to it.
1458
1459 x_po_summary_url_buyer := pon_wf_utl_pkg.get_dest_page_url (
1460 p_dest_func => 'PON_PO_SUMMARY'
1461 ,p_notif_performer => 'BUYER');
1462
1463
1464 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1465 itemkey => x_itemkey,
1466 aname => 'PURCHASE_ORDER_SUMMARY_URL',
1467 avalue => x_po_summary_url_buyer);
1468
1469
1470 -- call to notification utility package to get the redirect page url that
1471 -- is responsible for getting the Allocate Summary url and forward to it.
1472 x_alloc_summary_url_buyer := pon_wf_utl_pkg.get_dest_page_url (
1473 p_dest_func => 'PONCPOSUM_ALLOCSUMMARY'
1474 ,p_notif_performer => 'BUYER');
1475
1476
1477 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1478 itemkey => x_itemkey,
1479 aname => 'ALLOCATE_SUMMARY_URL',
1480 avalue => x_alloc_summary_url_buyer);
1481
1482
1483 -- call to notification utility package to get the redirect page url that
1484 -- is responsible for getting the Allocate by Item url and forward to it.
1485
1486 x_alloc_byitem_url_buyer := pon_wf_utl_pkg.get_dest_page_url (
1487 p_dest_func => 'PONCPOABI_ALLOCATEBYITEM'
1488 ,p_notif_performer => 'BUYER');
1489
1490 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1491 itemkey => x_itemkey,
1492 aname => 'ALLOCATE_ITEM_URL',
1493 avalue => x_alloc_byitem_url_buyer);
1494
1495
1496 /* Setting Profile Attributes */
1497
1498 FND_PROFILE.GET('RESP_ID', x_responsibility_id);
1499
1500 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1501 itemkey => x_itemkey,
1502 aname => 'RESPONSIBILITY_ID',
1503 avalue => x_responsibility_id);
1504
1505 FND_PROFILE.GET('RESP_APPL_ID', x_application_id);
1506
1507 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1508 itemkey => x_itemkey,
1509 aname => 'APPLICATION_ID',
1510 avalue => x_application_id);
1511
1512 x_progress := 'START_PO_WORKFLOW: profile values: ' ||
1513 'x_responsibility_id: ' || x_responsibility_id || ', ' ||
1514 'x_application_id: ' || x_application_id;
1515
1516 log_message(x_itemtype || ' ' ||x_progress);
1517
1518 /* Setting Message Attributes */
1519
1520 SELECT auh.document_number,
1521 dt.doctype_group_name, auh.contract_type,
1522 nvl(auh.wf_poncompl_current_round, 0), auh.doctype_id
1523 INTO x_doc_number_dsp, x_doctype_group_name, x_contract_type,
1524 x_current_round, x_doctype_id
1525 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
1526 WHERE auh.auction_header_id = p_auction_header_id and
1527 auh.doctype_id = dt.doctype_id;
1528
1529 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
1530
1531 IF (x_contract_type = 'STANDARD') THEN
1532 -- x_purchase_order := 'Standard Purchase Order';
1533 -- x_purchase_orders := 'Standard Purchase Orders';
1534 x_purchase_order := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_SPOT');
1535 x_purchase_orders := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_SPOS');
1536 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1537 itemkey => x_itemkey,
1538 aname => 'PON_AUC_WF_PO_CREATE_SUBJ',
1539 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_PO_CREATE_SUBJ', x_msg_suffix, 'DOC_NUMBER', x_doc_number_dsp));
1540 ELSIF (x_contract_type = 'BLANKET') THEN
1541 -- x_purchase_order := 'Blanket Purchase Agreement';
1542 -- x_purchase_orders := 'Blanket Purchase Agreements';
1543 x_purchase_order := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_LONG');
1544 x_purchase_orders := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_BLKTS');
1545 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1546 itemkey => x_itemkey,
1547 aname => 'PON_AUC_WF_PO_CREATE_SUBJ',
1548 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_BL_CREATE_SUBJ', x_msg_suffix, 'DOC_NUMBER', x_doc_number_dsp));
1549 ELSIF (x_contract_type = 'CONTRACT') THEN
1550 -- x_purchase_order := 'Contract Purchase Agreement';
1551 -- x_purchase_orders := 'Contract Purchase Agreements';
1552 x_purchase_order := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_CNTR');
1553 x_purchase_orders := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_CNTRS');
1554 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1555 itemkey => x_itemkey,
1556 aname => 'PON_AUC_WF_PO_CREATE_SUBJ',
1557 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_CPA_CREATE_SUBJ', x_msg_suffix, 'DOC_NUMBER', x_doc_number_dsp));
1558
1559 END IF;
1560
1561 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1562 itemkey => x_itemkey,
1563 aname => 'PURCHASE_ORDERS',
1564 avalue => x_purchase_order);
1565
1566 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1567 itemkey => x_itemkey,
1568 aname => 'PURCHASE_ORDER_TYPE',
1569 avalue => x_purchase_orders);
1570
1571 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1572 itemkey => x_itemkey,
1573 aname => 'WORKFLOW_ROUND_NUMBER',
1574 avalue => x_current_round);
1575
1576 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1577 itemkey => x_itemkey,
1578 aname => 'DOC_NUMBER',
1579 avalue => x_doc_number_dsp);
1580
1581 -- Setting workflow message header attributes
1582 pon_wf_utl_pkg.set_hdr_attributes (p_itemtype => x_itemtype
1583 ,p_itemkey => x_itemkey
1584 ,p_auction_tp_name => p_trading_partner_name
1585 ,p_auction_title => p_auction_title
1586 ,p_document_number => x_doc_number_dsp
1587 ,p_auction_tp_contact_name => p_trading_partner_contact_name);
1588
1589 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1590 itemkey => x_itemkey,
1591 aname => 'MSG_SUFFIX',
1592 avalue => x_msg_suffix);
1593
1594 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1595 itemkey => x_itemkey,
1596 aname => 'PON_AUC_WF_ORG',
1597 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_ORG'));
1598
1599 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1600 itemkey => x_itemkey,
1601 aname => 'PON_AUC_WF_SUCC_MESSAGE',
1602 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUCC_MESSAGE'));
1603
1604 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1605 itemkey => x_itemkey,
1606 aname => 'PON_AUC_WF_RESTART_WF_MSG',
1607 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_RESTART_WF_MSG', x_msg_suffix));
1608
1609 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1610 itemkey => x_itemkey,
1611 aname => 'PON_AUC_WF_RESTART_MSG',
1612 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_RESTART_MSG', x_msg_suffix));
1613
1614 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1615 itemkey => x_itemkey,
1616 aname => 'PON_AUC_WF_ALLOC_SUBJ',
1617 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_ALLOC_SUBJ', x_msg_suffix,
1618 'PURCHASE_ORDERS', x_purchase_order,
1619 'DOC_NUMBER', x_doc_number_dsp));
1620
1621 --check if the negotiation has requistion based line
1622 BEGIN
1623 SELECT 'REQUISITION'
1624 INTO x_requistion_based
1625 FROM DUAL
1626 WHERE EXISTS(
1627 SELECT '1'
1628 FROM pon_auction_item_prices_all
1629 WHERE auction_header_id = p_auction_header_id
1630 AND line_origination_code = 'REQUISITION'
1631 );
1632
1633 EXCEPTION
1634 WHEN NO_DATA_FOUND THEN
1635 x_requistion_based := 'NONE';
1636
1637 WHEN OTHERS THEN
1638 log_error(x_itemtype || ' ' || x_progress || 'in select exception' || SUBSTRB(SQLERRM, 1, 500));
1639 END;
1640
1641 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1642 itemkey => x_itemkey,
1643 aname => 'AUCTION_ORIGINATION_CODE',
1644 avalue => x_requistion_based);
1645
1646
1647 /*
1648 rrkulkar-large-auction-support
1649 */
1650
1651
1652
1653 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1654 itemkey => x_itemkey,
1655 aname => 'ALLOC_ERROR',
1656 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || substrb(SQLERRM, 1, 500));
1657
1658 IF x_line_number IS NULL THEN
1659
1660 -- it means the exception was thrown before line information is selected
1661 -- let buyer know no line information is available
1662
1663 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1664 itemkey => x_itemkey,
1665 aname => 'LINE_NUMBER',
1666 avalue => PON_AUCTION_PKG.getMessage('PON_LINE_INFO_NOT_AVAIL'));
1667 ELSE
1668 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1669 itemkey => x_itemkey,
1670 aname => 'LINE_NUMBER',
1671 avalue => to_char(x_line_number));
1672 END IF;
1673
1674 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1675 itemkey => x_itemkey,
1676 aname => 'ITEM_NUMBER',
1677 avalue => x_item_number);
1678
1679 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1680 itemkey => x_itemkey,
1681 aname => 'LINE_DESCRIPTION',
1682 avalue => x_item_description);
1683
1684 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1685 itemkey => x_itemkey,
1686 aname => 'REVISION_NUMBER',
1687 avalue => x_item_revision);
1688
1689 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1690 itemkey => x_itemkey,
1691 aname => 'REQ_NUMBERS',
1692 avalue => x_requisition_number);
1693
1694 -- setting workflow progress attribute to track the process and easy the debugging process
1695 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1696 itemkey => x_itemkey,
1697 aname => 'WORKFLOW_PROGRESS',
1698 avalue => x_progress);
1699
1700 -- Bug 4456420: Set initiator to current logged in user
1701 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1702 itemkey => x_itemkey,
1703 aname => 'ORIGIN_USER_NAME',
1704 avalue => fnd_global.user_name);
1705
1706
1707 x_progress := '30: START_PO_WORKFLOW: Kicking off StartProcess';
1708 log_message(x_itemtype || ' ' ||x_progress);
1709
1710 -- Bug 4295915: Set the workflow owner
1711 wf_engine.SetItemOwner(itemtype => x_itemtype,
1712 itemkey => x_itemkey,
1713 owner => fnd_global.user_name);
1714
1715 wf_engine.StartProcess(itemtype => x_itemtype,
1716 itemkey => x_itemkey );
1717
1718
1719 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
1720
1721 END START_PO_WORKFLOW;
1722
1723 /*
1724 Creates the award purchase order structure in PDOI
1725 This procedure is invoked from PON_AUCTION_CREATE_PO_PKG.GENERATE_POS procedure
1726 which is invoked from our create po workflow (refer ponwfau7.wft)
1727
1728 This procedure inserts data from PON tables to PO interface tables
1729
1730 */
1731
1732 PROCEDURE CREATE_PO_STRUCTURE(p_auction_header_id IN NUMBER,
1733 p_bid_number IN NUMBER,
1734 p_user_id IN NUMBER,
1735 p_interface_header_id OUT NOCOPY NUMBER,
1736 p_pdoi_header OUT NOCOPY PDOIheader,
1737 p_error_code OUT NOCOPY VARCHAR2,
1738 p_error_message OUT NOCOPY VARCHAR2) IS
1739
1740
1741 x_user_id NUMBER;
1742 x_line_number NUMBER;
1743 x_award_quantity NUMBER;
1744 x_allocation_quantity NUMBER;
1745 x_requisition_line_id NUMBER;
1746 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1747 x_interface_header_id NUMBER;
1748 x_interface_line_id NUMBER;
1749 x_price NUMBER;
1750 x_quantity NUMBER;
1751 x_amount NUMBER;
1752 x_pdoi_header PDOIheader;
1753 x_pdoi_line PDOIline;
1754 x_hasBackingReqs pon_auction_headers_all.auction_origination_code%TYPE;
1755 x_source_reqs_flag VARCHAR2(1);
1756 x_sum_requisitions NUMBER;
1757 sum_of_alloc_quantities sumOfReqLineAllocQuantities;
1758
1759 l_rows_processed NUMBER;
1760 l_batch_end NUMBER;
1761 l_batch_start NUMBER;
1762 l_batch_size NUMBER;
1763 l_max_line_number NUMBER;
1764 l_commit_flag BOOLEAN;
1765
1766 -- clm clin/slin change
1767 l_is_fed VARCHAR2(1);
1768 l_doctype_id NUMBER;
1769
1770 /* Bug 9883780 - Start
1771 Added the following variable for Renumbering during auto create from Sol to Award*/
1772
1773 clin_num_tbl po_tbl_varchar100 := po_tbl_varchar100();
1774 len NUMBER;
1775 next_clin_num VARCHAR2(10);
1776 x_result VARCHAR2(10);
1777
1778 /* The following log variables are added for logging purpose. */
1779
1780 log_interface_header_id NUMBER;
1781 log_interface_line_id NUMBER;
1782 log_auction_header_id NUMBER;
1783 log_auction_line_number NUMBER;
1784 log_group_line_id NUMBER;
1785 log_requisition_line_id NUMBER;
1786 log_clm_base_line_num NUMBER;
1787 log_line_num_display VARCHAR2(100);
1788
1789 CURSOR update_clin_num_cursor(l_interface_header_id NUMBER,
1790 l_auction_header_id NUMBER ) IS
1791 SELECT interface_line_id,
1792 group_line_id,
1793 clm_info_flag,
1794 interface_header_id
1795 FROM po_lines_interface
1796 WHERE group_Line_id IS NULL
1797 AND INTERFACE_HEADER_ID = l_interface_header_id
1798 AND auction_header_id = l_auction_header_id
1799 AND clm_exhibit_name IS NULL --ELINs project
1800 ORDER BY interface_line_id;
1801
1802
1803 CURSOR update_slin_num_cursor(l_interface_header_id NUMBER,
1804 l_auction_header_id NUMBER ) IS
1805 SELECT interface_line_id,
1806 group_line_id,
1807 line_num_display,
1808 interface_header_id
1809 FROM po_lines_interface
1810 WHERE group_Line_id IS NULL
1811 AND INTERFACE_HEADER_ID = l_interface_header_id
1812 AND auction_header_id = l_auction_header_id
1813 ORDER BY interface_line_id;
1814
1815 CURSOR log_cursor (l_interface_header_id NUMBER) IS
1816 SELECT interface_header_id,
1817 interface_line_id,
1818 auction_header_id,
1819 auction_line_number,
1820 group_line_id,
1821 requisition_line_id,
1822 clm_base_line_num,
1823 line_num_display
1824 FROM PO_LINES_INTERFACE
1825 WHERE INTERFACE_HEADER_ID = l_interface_header_id
1826 ORDER BY interface_line_id;
1827
1828 /* Bug 9883780 - End */
1829
1830 /* Selects the data from Sourcing that will populate PO_HEADERS_INTERFACE */
1831
1832 /* Bug 9891996 - Added External IDV field to headerLevelInfo cursor */
1833
1834 CURSOR headerLevelInfo IS
1835 SELECT pah.auction_header_id,
1836 pah.document_number,
1837 pah.org_id,
1838 pah.contract_type,
1839 pah.language_code,
1840 pbh.po_start_date,
1841 pbh.po_end_date,
1842 pah.currency_code,
1843 pah.fob_code,
1844 pah.freight_terms_code,
1845 pah.carrier_code,
1846 pah.payment_terms_id,
1847 pah.ship_to_location_id,
1848 pah.bill_to_location_id,
1849 pah.auction_origination_code,
1850 pah.source_reqs_flag,
1851 pbh.bid_number,
1852 pbh.order_number,
1853 pbh.vendor_id,
1854 pbh.vendor_site_id,
1855 PON_AUCTION_CREATE_PO_PKG.get_vendor_contact_id(pbh.trading_partner_contact_id,pbh.vendor_site_id,pbh.vendor_id) vendor_contact_id,
1856 pbh.agent_id,
1857 pah.global_agreement_flag,
1858 round(pah.po_min_rel_amount* pbh.rate,fc.precision),
1859 pbh.po_agreed_amount,
1860 pbh.bid_currency_code,
1861 pah.rate_type,
1862 pah.rate_date,
1863 pbh.rate_dsp,
1864 pbh.create_sourcing_rules,
1865 pbh.update_sourcing_rules,
1866 pbh.release_method,
1867 pbh.initiate_approval,
1868 pbh.acceptance_required_flag,
1869 pah.po_style_id,
1870 pah.progress_payment_type,
1871 pah.supplier_enterable_pymt_flag,
1872 pbh.idv_header_id, -- CLM Order off IDV Project
1873 pbh.external_idv,
1874 pah.umbrella_program_id,
1875 pah.fair_opp_notice_flag
1876 FROM pon_auction_headers_all pah,
1877 pon_bid_headers pbh,
1878 fnd_currencies fc
1879 WHERE pah.auction_header_id = p_auction_header_id and
1880 pbh.auction_header_id = pah.auction_header_id and
1881 pbh.bid_number = p_bid_number and
1882 pbh.bid_currency_code = fc.currency_code;
1883
1884 /* Selects the data from Sourcing that will populate PO_LINES_INTERFACE */
1885
1886 /*
1887 rrkulkar-large-auction-support - this cursor will bring in all the lines in the middle-tier :
1888 hence, added the following condition in the where clause :-
1889
1890 paip.line_origination_code = 'REQUISITION';
1891
1892 We know that we will not have super-large negotiations with all lines having backing reqs. -
1893 hence, looping over all such lines is just fine.
1894
1895 In SPO outcome case, we will use bulk collect along with batching for super-large auctions for
1896 lines with no backing reqs.
1897
1898 */
1899 CURSOR reqlineLevelInfo(l_is_fed varchar2) IS
1900 SELECT paip.line_number,
1901 paip.line_type_id,
1902 paip.order_type_lookup_code,
1903 paip.line_origination_code,
1904 paip.item_id,
1905 paip.item_revision,
1906 paip.category_id,
1907 paip.item_description,
1908 mtluom.unit_of_measure,
1909 paip.ship_to_location_id,
1910 paip.clm_need_by_date,
1911 /*Clm Bug : 10212430 : For clm,copy POP dates from offer. */
1912 Decode(Nvl(l_is_fed,'N'),'Y',pbip.PROMISE_POP_START_DATE,paip.need_by_start_date),
1913 Decode(Nvl(l_is_fed,'N'),'Y',pbip.PROMISE_POP_END_DATE,paip.need_by_date),
1914 pbip.award_quantity,
1915 nvl(pbip.po_bid_min_rel_amount, round(paip.po_min_rel_amount* pbh.rate,fc.precision)),
1916 paip.has_price_elements_flag,
1917 decode(paip.order_type_lookup_code, 'FIXED PRICE',
1918 round(pbip.bid_currency_unit_price, fc.precision),
1919 pbip.bid_currency_unit_price),
1920 pbip.promised_date,
1921 paip.job_id,
1922 round(paip.po_agreed_amount*pbh.rate, fc.precision),
1923 paip.purchase_basis
1924 , pbip.bid_curr_advance_amount
1925 , pbip.recoupment_rate_percent
1926 , pbip.progress_pymt_rate_percent
1927 , pbip.retainage_rate_percent
1928 , pbip.bid_curr_max_retainage_amt
1929 , decode(pbip.has_bid_payments_flag, 'Y', decode((select 1 from dual where exists
1930 ( select 1 from pon_bid_payments_shipments where
1931 auction_header_id = pbip.auction_header_id and
1932 bid_number= pbip.bid_number and bid_line_number=pbip.line_number
1933 and bid_currency_price <> 0
1934 )
1935 ),
1936 1,'Y','N')
1937 ,'N') has_bid_payments_flag
1938 , pbip.award_shipment_number
1939
1940 -- Clin Slin Changes
1941 , paip.line_num_display
1942 , paip.group_line_id
1943 , paip.clm_info_flag
1944 , paip.clm_option_indicator
1945 , paip.clm_option_num
1946 , paip.clm_option_from_date
1947 , paip.clm_option_to_date
1948 , paip.clm_funded_flag
1949 , paip.clm_base_line_num
1950
1951 -- Complex Pricing Changes
1952 , paip.CLM_CONTRACT_TYPE
1953 , paip.CLM_COST_CONSTRAINT
1954 , paip.CLM_IDC_TYPE
1955 , pbip.idv_line_id -- CLM Order Off IDV Project.
1956 , paip.exhibit_number --ELINs project
1957 -- Event Based Delivery Project
1958 ,paip.CLM_DELIVERY_EVENT_CODE
1959 ,paip.CLM_DELIVERY_PERIOD
1960 ,paip.CLM_DELIVERY_PERIOD_UOM
1961 ,pbip.CLM_PROMISE_PERIOD
1962 ,pbip.CLM_PROMISE_PERIOD_UOM
1963 ,pbip.CLM_PROMISE_POP_DURATION
1964 ,pbip.CLM_PROMISE_POP_DURATION_UOM
1965 FROM
1966 pon_auction_item_prices_all paip,
1967 pon_bid_item_prices pbip,
1968 mtl_units_of_measure mtluom,
1969 pon_bid_headers pbh,
1970 fnd_currencies fc
1971 WHERE pbip.bid_number = p_bid_number and
1972 pbip.auction_header_id = p_auction_header_id and
1973 nvl(pbip.award_status, 'NO') = 'AWARDED' and
1974 paip.auction_header_id = pbip.auction_header_id and
1975 paip.line_number = pbip.line_number and
1976 paip.group_type NOT IN ('GROUP','LOT_LINE') and
1977 paip.uom_code = mtluom.uom_code (+) and
1978 pbh.bid_number = pbip.bid_number and
1979 fc.currency_code = pbh.bid_currency_code and
1980 paip.line_origination_code = 'REQUISITION';
1981
1982
1983 /* queries the allocation table to get req lines and the allocated quantity backing that particular bid and bid line.*/
1984 /* Clin Slin Changes -- Info Clin lines will be there in the pon_award_allocations, but they will never cause a split on
1985 the backing req line. */
1986 /* Bug : 13584934 : For clm, we donot split req lines. So same req line can be backing multiple award lines.
1987 * Use orig_req_line_id column.
1988 */
1989 CURSOR reqBackingBidItem IS
1990 SELECT Decode(Nvl(l_is_fed,'N'),'Y', paa.orig_req_line_id, paa.split_req_line_id), paa.allocated_qty
1991 FROM pon_award_allocations paa, pon_auction_item_prices_all paip
1992 WHERE paa.auction_header_id = p_auction_header_id and
1993 paa.bid_number = p_bid_number AND
1994 paa.auction_header_id = paip.auction_header_id and
1995 paa.bid_line_number = paip.line_number AND
1996 bid_line_number = x_line_number and
1997 ((Nvl(paip.clm_info_flag,'N') = 'Y') OR (Nvl(paip.clm_info_flag,'N') = 'N' AND nvl(paa.allocated_qty,0) <> 0)) and
1998 ((Nvl(paip.clm_info_flag,'N') = 'Y') OR
1999 (Nvl(paip.clm_info_flag,'N') = 'N' AND nvl(paa.split_req_line_id, -999) <> -999) OR
2000 (Nvl(l_is_fed,'N') = 'Y' AND nvl(paa.orig_req_line_id, -999) <> -999));
2001
2002 /* returns the sum of allocated quantities to backing requisition lines for
2003 each negotiation line for a particular bid. The outer join is for picking
2004 up lines that have no allocations, but have backing requisitions to get a
2005 sum of 0 */
2006
2007 /* Clin Slin changes : We need to get the info lines selected in this cursor, since
2008 later in the code we use the results of this cursor
2009 to identify the lines which have to go into the interface tables.
2010
2011 The condition used is
2012 sum_of_alloc_quantities.EXISTS(x_line_number) AND
2013 sum_of_alloc_quantities(x_line_number) > 0
2014
2015 */
2016 CURSOR sumOfReqAllocQuantities is
2017 SELECT PAIP.line_number, nvl(sum(nvl(PAA.allocated_qty,0)), 0)
2018 FROM PON_AWARD_ALLOCATIONS PAA, PON_AUCTION_ITEM_PRICES_ALL PAIP
2019 WHERE PAIP.auction_header_id = p_auction_header_id
2020 AND PAIP.award_status = 'COMPLETED'
2021 AND ((Nvl(clm_info_flag,'N')='Y') OR (Nvl(clm_info_flag,'N') = 'N' and nvl(PAIP.awarded_quantity,0) > 0))
2022 AND PAA.auction_header_id(+) = PAIP.auction_header_id
2023 AND PAA.bid_line_number(+) = PAIP.line_number
2024 AND PAA.bid_number(+) = p_bid_number
2025 AND nvl(PAA.split_req_line_id(+), -999) <> -999
2026 GROUP BY PAIP.line_number;
2027
2028 /* Bug : 13584934 : For clm, we donot split req lines. So same req line can be backing multiple award lines.
2029 * To find the sum of allocation quantities, use orig_req_line_id column.
2030 */
2031 CURSOR clm_sumOfReqAllocQuantities is
2032 SELECT PAIP.line_number, nvl(sum(nvl(PAA.allocated_qty,0)), 0)
2033 FROM PON_AWARD_ALLOCATIONS PAA, PON_AUCTION_ITEM_PRICES_ALL PAIP
2034 WHERE PAIP.auction_header_id = p_auction_header_id
2035 AND PAIP.award_status = 'COMPLETED'
2036 AND ((Nvl(clm_info_flag,'N')='Y') OR (Nvl(clm_info_flag,'N') = 'N' and nvl(PAIP.awarded_quantity,0) > 0))
2037 AND PAA.auction_header_id(+) = PAIP.auction_header_id
2038 AND PAA.bid_line_number(+) = PAIP.line_number
2039 AND PAA.bid_number(+) = p_bid_number
2040 AND nvl(PAA.orig_req_line_id(+), -999) <> -999
2041 GROUP BY PAIP.line_number;
2042
2043 --Unsolicited Lines Project
2044 l_allow_unsol_lines pon_auction_headers_all.ALLOW_UNSOL_OFFER_LINES%TYPE;
2045
2046 --Bug : 14134092
2047 l_is_line_type_enabled VARCHAR2(1);
2048
2049 BEGIN
2050 x_progress := '10: CREATE_PO_STRUCTURE: ' || 'auction_header_id: ' || p_auction_header_id || ', ' || 'bid_number: ' || p_bid_number;
2051 log_message(x_progress);
2052
2053 --clm clin/slin change
2054 BEGIN
2055
2056 --Unsolicited Lines Project : Get column ALLOW_UNSOL_OFFER_LINES
2057 SELECT doctype_id, Nvl(ALLOW_UNSOL_OFFER_LINES,'N')
2058 INTO l_doctype_id,l_allow_unsol_lines
2059 FROM pon_auction_headers_all
2060 WHERE auction_Header_id = p_auction_header_id;
2061
2062 SELECT
2063 pon_auc_doctype_rules.DEFAULT_VALUE INTO l_is_fed
2064 FROM PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
2065 , PON_AUC_BIZRULES pon_auc_bizrules
2066 WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
2067 AND pon_auc_doctype_rules.DOCTYPE_ID = l_doctype_id
2068 AND pon_auc_bizrules.NAME = 'FEDERAL_NEGOTIATION';
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 l_is_fed := 'N';
2072 END;
2073
2074
2075 SELECT po_headers_interface_s.nextval
2076 INTO x_interface_header_id
2077 FROM dual;
2078
2079 OPEN headerLevelInfo;
2080 FETCH headerLevelInfo
2081 INTO x_pdoi_header;
2082 CLOSE headerLevelInfo;
2083
2084 x_hasBackingReqs := x_pdoi_header.auction_origination_code;
2085
2086
2087 /*loops through sumOfReqAllocQuantities cursor and populates
2088 sum_of_alloc_quantities(line) array to hold the total number of req
2089 line quantities for that item line. */
2090 /* Clin Slin Changes : The below cursor find the sum of allocated quantities across the lines
2091 Since the allocated quantity of the Info lines are null, and will cause no
2092 effect on the quantity. so we do not need to modify anything here.
2093
2094 */
2095 /* Bug : 13584934 : For clm, use cursor clm_sumOfReqAllocQuantities */
2096 IF Nvl(l_is_fed,'N') = 'Y' THEN
2097 OPEN clm_sumOfReqAllocQuantities;
2098 LOOP
2099 FETCH clm_sumOfReqAllocQuantities INTO x_line_number, x_sum_requisitions;
2100 EXIT WHEN clm_sumOfReqAllocQuantities%NOTFOUND;
2101 sum_of_alloc_quantities(x_line_number) := x_sum_requisitions;
2102 END LOOP;
2103 CLOSE clm_sumOfReqAllocQuantities;
2104 ELSE
2105 OPEN sumOfReqAllocQuantities;
2106 LOOP
2107 FETCH sumOfReqAllocQuantities INTO x_line_number, x_sum_requisitions;
2108 EXIT WHEN sumOfReqAllocQuantities%NOTFOUND;
2109 sum_of_alloc_quantities(x_line_number) := x_sum_requisitions;
2110 END LOOP;
2111 CLOSE sumOfReqAllocQuantities;
2112 END IF;
2113
2114 /*
2115 Insert into PO_HEADERS_INTERFACE the purchase order header information based on the negotiation and the award bid.
2116 */
2117
2118 /* Bug 9891996 - Populate External IDV also in PO_HEADERS_INTERFACE table */
2119
2120 INSERT into PO_HEADERS_INTERFACE (
2121 interface_header_id,
2122 interface_source_code,
2123 batch_id,
2124 action,
2125 org_id,
2126 document_type_code,
2127 document_subtype,
2128 created_language,
2129 effective_date,
2130 expiration_date,
2131 document_num,
2132 group_code,
2133 vendor_id,
2134 vendor_site_id,
2135 vendor_contact_id,
2136 agent_id,
2137 currency_code,
2138 rate_type_code,
2139 rate_date,
2140 rate,
2141 fob,
2142 freight_terms,
2143 freight_carrier,
2144 terms_id,
2145 ship_to_location_id,
2146 bill_to_location_id,
2147 consume_req_demand_flag,
2148 global_agreement_flag,
2149 min_release_amount,
2150 amount_agreed,
2151 acceptance_required_flag,
2152 style_id,
2153 created_by,
2154 creation_date,
2155 last_updated_by,
2156 last_update_date,
2157 clm_external_idv,
2158 umbrella_program_id,
2159 fon_ref_id)
2160 values (
2161 x_interface_header_id, -- interface_header_id
2162 'SOURCING', -- interface_source_code
2163 x_interface_header_id, -- batch_id
2164 'NEW', -- action
2165 x_pdoi_header.org_id, -- org_id
2166 decode(x_pdoi_header.contract_type, 'BLANKET',
2167 'PA','CONTRACT','PA','PO'), -- document_type_code
2168 x_pdoi_header.contract_type, -- document_subtype
2169 x_pdoi_header.language_code, -- created_language
2170 x_pdoi_header.po_start_date, -- effective_date
2171 x_pdoi_header.po_end_date, -- expiration_date
2172 NULL, --x_pdoi_header.order_number, -- document_num
2173 'DEFAULT', -- group_code
2174 x_pdoi_header.vendor_id, -- vendor_id
2175 x_pdoi_header.vendor_site_id, -- vendor_site_id
2176 x_pdoi_header.vendor_contact_id, -- vendor_contact_id
2177 x_pdoi_header.agent_id, -- agent_id
2178 x_pdoi_header.bid_currency_code, -- currency_code
2179 decode(x_pdoi_header.currency_code, x_pdoi_header.bid_currency_code, null, x_pdoi_header.rate_type), -- rate_type_code
2180 decode(x_pdoi_header.currency_code, x_pdoi_header.bid_currency_code, null, x_pdoi_header.rate_date), -- rate_date
2181 decode(x_pdoi_header.currency_code, x_pdoi_header.bid_currency_code, null, x_pdoi_header.rate_dsp), -- rate
2182 x_pdoi_header.fob_code, -- fob
2183 x_pdoi_header.freight_terms_code, -- freight_terms
2184 x_pdoi_header.carrier_code, -- freight_carrier,
2185 x_pdoi_header.payment_terms_id, -- terms_id
2186 x_pdoi_header.ship_to_location_id, -- ship_to_location_id
2187 x_pdoi_header.bill_to_location_id, -- bill_to_location_id
2188 x_pdoi_header.source_reqs_flag, -- consume req demandflag
2189 x_pdoi_header.global_agreement_flag, -- global_agreement_flag
2190 x_pdoi_header.po_min_rel_amount, -- min_release_amount
2191 x_pdoi_header.po_agreed_amount, -- amount_agreed
2192 x_pdoi_header.acceptance_required_flag, -- accept req flag
2193 x_pdoi_header.po_style_id, -- style_id
2194 p_user_id, -- created_by
2195 sysdate, -- creation_date
2196 p_user_id, -- last_update_by
2197 SYSDATE,
2198 x_pdoi_header.clm_external_idv,
2199 decode(x_pdoi_header.contract_type, 'STANDARD',NULL,x_pdoi_header.umbrella_program_id),
2200 decode(Nvl(x_pdoi_header.fair_opp_notice_flag,'N'), 'Y',x_pdoi_header.auction_header_id,NULL)); -- last_update_date
2201
2202
2203 x_progress := '15: CREATE_PO_STRUCTURE: INSERTING the following data into po_headers_interface: ' ||
2204 'interface_header_id: ' || to_char(x_interface_header_id) || ', ' ||
2205 'interface_source_code: ' || 'SOURCING' || ', ' ||
2206 'batch_id: ' || to_char(x_interface_header_id) || ', ' ||
2207 'action: ' || 'NEW' || ', ' ||
2208 'org_id: ' || to_char(x_pdoi_header.org_id) || ', ' ;
2209
2210 IF (x_pdoi_header.contract_type = 'BLANKET') THEN
2211 x_progress := x_progress || 'document_type_code: ' || 'PA' || ', ';
2212 ELSE
2213 x_progress := x_progress || 'document_type_code: ' || 'PO' || ', ';
2214 END IF;
2215
2216 x_progress := x_progress ||
2217 'document_subtype: ' || x_pdoi_header.contract_type || ', ' ||
2218 'created_language: ' || x_pdoi_header.language_code || ', ' ||
2219 'effective_date: ' || to_char(x_pdoi_header.po_start_date) || ', ' ||
2220 'expiration_date: ' || to_char(x_pdoi_header.po_end_date) || ', ' ||
2221 'document_num: ' || x_pdoi_header.order_number || ', ' ||
2222 'group_code: ' || 'DEFAULT' || ', ' ||
2223 'vendor_id: ' || to_char(x_pdoi_header.vendor_id) || ', ' ||
2224 'vendor_site_id: ' || to_char(x_pdoi_header.vendor_site_id) || ', ' ||
2225 'vendor_contact_id: ' || to_char(x_pdoi_header.vendor_contact_id) || ', ' ||
2226 'agent_id: ' || to_char(x_pdoi_header.agent_id) || ', ' ||
2227 'currency_code: ' || x_pdoi_header.bid_currency_code || ', ' ||
2228 'rate_type_code: ' || x_pdoi_header.rate_type || ', ';
2229
2230 IF (x_pdoi_header.currency_code = x_pdoi_header.bid_currency_code) THEN
2231 x_progress := x_progress || 'rate_date: ' || 'null' || ', ' || 'rate: ' || null || ', ';
2232 ELSE
2233 x_progress := x_progress || 'rate_date: ' || x_pdoi_header.rate_date || ', ' || 'rate: ' || to_char(x_pdoi_header.rate_dsp) || ', ';
2234 END IF;
2235
2236 x_progress := x_progress ||
2237 'fob: ' || x_pdoi_header.fob_code || ', ' ||
2238 'freight_terms: ' || x_pdoi_header.freight_terms_code || ', ' ||
2239 'freight_carrier: ' || x_pdoi_header.carrier_code || ', ' ||
2240 'terms_id: ' || to_char(x_pdoi_header.payment_terms_id) || ', ' ||
2241 'ship_to_location_id: ' || to_char(x_pdoi_header.ship_to_location_id) || ', ' ||
2242 'bill_to_location_id: ' || to_char(x_pdoi_header.bill_to_location_id) || ', ' ||
2243 ' source_reqs_flag: ' || x_pdoi_header.source_reqs_flag || ', ' ||
2244 'amount_agreed: ' || to_char(x_pdoi_header.po_agreed_amount) || ', ' ||
2245 'created_by: ' || to_char(p_user_id) || ', ' ||
2246 'last_update_by: ' || to_char(p_user_id) || ', ' ||
2247 'clm_external_idv: ' || x_pdoi_header.clm_external_idv;
2248
2249 log_message(x_progress);
2250
2251 --Bug : 14134092 : If line type structure changes are enabled, then fund allocation will be done for all lines.
2252 l_is_line_type_enabled := PON_CLM_UTIL_PKG.get_line_structure_enabled(p_auction_header_id => p_auction_header_id);
2253
2254 IF (x_pdoi_header.contract_type = 'STANDARD') THEN --{
2255
2256 OPEN reqlineLevelInfo(l_is_fed);
2257
2258 LOOP --{ -- loop over reqlineLevelInfo
2259
2260 FETCH reqlineLevelInfo INTO x_pdoi_line;
2261 EXIT WHEN reqlineLevelInfo%NOTFOUND;
2262
2263 x_line_number := x_pdoi_line.line_number;
2264
2265 x_price := x_pdoi_line.bid_currency_unit_price;
2266
2267 -- Quantity Based Price Tiers changes
2268 IF x_pdoi_line.award_shipment_number IS NOT NULL THEN
2269
2270 SELECT BID_CURRENCY_UNIT_PRICE INTO x_price
2271 FROM PON_BID_SHIPMENTS
2272 WHERE LINE_NUMBER = x_pdoi_line.line_number
2273 AND AUCTION_HEADER_ID = x_pdoi_header.auction_header_id
2274 AND BID_NUMBER = p_bid_number
2275 AND SHIPMENT_NUMBER = x_pdoi_line.award_shipment_number;
2276
2277 END IF;
2278 IF (x_pdoi_line.order_type_lookup_code = 'FIXED PRICE') THEN
2279 x_amount := x_price;
2280 x_price := NULL;
2281 ELSE
2282 x_amount := NULL;
2283 x_price := x_price;
2284 END IF;
2285
2286 IF (x_pdoi_line.order_type_lookup_code = 'AMOUNT') THEN
2287 x_award_quantity := x_price;
2288 ELSE
2289 x_award_quantity := x_pdoi_line.award_quantity;
2290 END IF;
2291
2292 x_progress := '20: CREATE_PO_STRUCTURE:' || 'Processing bid number: ' || p_bid_number || ', '
2293 || 'line number: ' || x_line_number || ', '
2294 || 'award quantity: ' || x_award_quantity;
2295 log_message(x_progress);
2296
2297
2298 /* contract type is standard and this line comes from a
2299 backing requisition and the sum of the allocated quantity
2300 to the backing requisitions is greater than 0 */
2301
2302 /*
2303 rrkulkar-large-auction-support : since we don't expect too many lines
2304 with backing requisitions (i.e > 2500), we will not add batching over
2305 here
2306
2307 */
2308 --Bug : 14134092 : If line type structure changes are enabled, then fund allocation will be done for all lines.
2309 IF (l_is_line_type_enabled = 'N' AND
2310 x_pdoi_line.line_origination_code = 'REQUISITION' AND
2311 sum_of_alloc_quantities.EXISTS(x_line_number) AND
2312 ( Nvl(x_pdoi_line.clm_info_flag,'N') = 'Y' OR (Nvl(x_pdoi_line.clm_info_flag,'N') = 'N' AND sum_of_alloc_quantities(x_line_number) > 0) )
2313 ) THEN --{
2314
2315 x_progress := '30: CREATE_PO_STRUCTURE: ' || 'Single Supplier is handling the demand';
2316
2317 log_message(x_progress);
2318
2319 OPEN reqBackingBidItem;
2320
2321 LOOP --{ -- loop over reqBackingBidItem
2322
2323 FETCH reqBackingBidItem INTO x_requisition_line_id,
2324 x_allocation_quantity;
2325 EXIT WHEN reqBackingBidItem%NOTFOUND;
2326 x_progress := '30: CREATE_PO_STRUCTURE: ' || 'Req Line: ' || x_requisition_line_id || ', ' || 'Alloc Quantity: ' || x_allocation_quantity;
2327
2328 log_message(x_progress);
2329
2330 /*
2331 Insert a row into PO_LINES_INTERFACE with the
2332 item information from the negotiation line, and
2333 the requisiton_line_id and quantity
2334 (price and quantity will be switched when
2335 negotiation line is amount-based-
2336 check pon_auction_item_prices_all.order_type_lookup_code)
2337 from the backing requisition.
2338 */
2339
2340
2341 INSERT into PO_LINES_INTERFACE (
2342 interface_header_id,
2343 interface_line_id,
2344 requisition_line_id,
2345 line_type_id,
2346 item_id,
2347 item_revision,
2348 category_id,
2349 item_description,
2350 unit_of_measure,
2351 quantity,
2352 unit_price,
2353 min_release_amount,
2354 ship_to_location_id,
2355 need_by_date,
2356 clm_period_perf_start_date,
2357 clm_period_perf_end_date,
2358 promised_date,
2359 last_updated_by,
2360 last_update_date,
2361 created_by,
2362 creation_date,
2363 auction_header_id,
2364 auction_display_number,
2365 auction_line_number,
2366 bid_number,
2367 bid_line_number,
2368 orig_from_req_flag,
2369 job_id,
2370 amount
2371 , advance_amount
2372 , recoupment_rate
2373 , progress_payment_rate
2374 , retainage_rate
2375 , max_retainage_amount
2376 , line_loc_populated_flag,
2377
2378 -- Clin Slin Changes
2379 line_num_display,
2380 group_line_id,
2381 clm_info_flag,
2382 clm_option_indicator,
2383 clm_option_num,
2384 clm_option_from_date,
2385 clm_option_to_date,
2386 clm_funded_flag,
2387 clm_base_line_num,
2388
2389 -- Complex Pricing Changes
2390 CONTRACT_TYPE,
2391 COST_CONSTRAINT,
2392 CLM_IDC_TYPE,
2393 -- CLM Order Off IDV Project
2394 from_header_id,
2395 from_line_id,
2396 clm_exhibit_name, --ELINs project
2397 -- Event Based Delivery Project
2398 CLM_DELIVERY_EVENT_CODE,
2399 CLM_DELIVERY_PERIOD,
2400 CLM_DELIVERY_PERIOD_UOM,
2401 CLM_PROMISE_PERIOD,
2402 CLM_PROMISE_PERIOD_UOM,
2403 CLM_POP_DURATION,
2404 CLM_POP_DURATION_UOM
2405 )
2406
2407 values (
2408
2409 x_interface_header_id, -- interface_header_id
2410 po_lines_interface_s.nextval, -- interface_line_id
2411 x_requisition_line_id, -- requisition_line_id
2412 x_pdoi_line.line_type_id,
2413 -- line_type_id
2414 x_pdoi_line.item_id,
2415 -- item_id
2416 x_pdoi_line.item_revision,
2417 -- item_revision
2418 x_pdoi_line.category_id,
2419 -- category_id
2420 substrb(x_pdoi_line.item_description, 1, 240),
2421 -- item_description
2422 decode(x_pdoi_line.order_type_lookup_code, 'AMOUNT', null, x_pdoi_line.unit_of_measure),
2423 -- unit_of_measure
2424 decode(x_pdoi_line.order_type_lookup_code, 'RATE', NULL, 'FIXED PRICE', NULL, x_allocation_quantity), -- quantity
2425 decode(x_pdoi_line.order_type_lookup_code,'AMOUNT', 1, x_price), -- unit_price
2426 x_pdoi_line.po_min_rel_amount,
2427 -- min_release_amount
2428 x_pdoi_line.ship_to_location_id,
2429 -- ship_to_location_id
2430 /*Bug : 10212430 : For clm : need_by_date <-> clm_need_by_date and Promise Start Date <-> need_by_start_date */
2431 Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.need_by_date,x_pdoi_line.pop_start_date),
2432 -- need_by_date
2433 Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.pop_start_date,null),
2434 -- period_of_performance_start_date
2435 Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.pop_end_date,null),
2436 -- period_of_performance_end_date
2437 x_pdoi_line.promised_date, -- promised_date
2438 p_user_id, -- last_update_by
2439 sysdate, -- last_update_date
2440 p_user_id, -- created_by
2441 sysdate, -- creation_date
2442 x_pdoi_header.auction_header_id, -- auction_header_id
2443 x_pdoi_header.document_number, -- document_number
2444 x_pdoi_line.line_number, -- auction_line_number,
2445 x_pdoi_header.bid_number, -- bid_number
2446 x_pdoi_line.line_number, -- bid_line_number
2447 'Y', -- orig_from_req_flag
2448 x_pdoi_line.job_id, -- job_id
2449 x_amount -- amount
2450 , decode(x_pdoi_line.bid_curr_advance_amount,0,null,x_pdoi_line.bid_curr_advance_amount)
2451 , x_pdoi_line.recoupment_rate_percent
2452 , x_pdoi_line.progress_pymt_rate_percent
2453 , x_pdoi_line.retainage_rate_percent
2454 , x_pdoi_line.Bid_curr_max_retainage_amt
2455 , x_pdoi_line.has_bid_payments_flag -- Line_loc_populated
2456
2457 --Clin Slin Changes
2458 ,x_pdoi_line.line_num_display
2459 ,x_pdoi_line.group_line_id
2460 ,x_pdoi_line.clm_info_flag
2461 ,x_pdoi_line.clm_option_indicator
2462 ,x_pdoi_line.clm_option_num
2463 ,x_pdoi_line.clm_option_from_date
2464 ,x_pdoi_line.clm_option_to_date
2465 ,x_pdoi_line.clm_funded_flag
2466 ,x_pdoi_line.clm_base_line_num
2467 ,x_pdoi_line.CONTRACT_TYPE
2468 ,x_pdoi_line.COST_CONSTRAINT
2469 ,x_pdoi_line.CLM_IDC_TYPE
2470 -- CLM Order Off IDV Project
2471 ,x_pdoi_header.idv_header_id
2472 ,x_pdoi_line.idv_line_id
2473 ,x_pdoi_line.exhibit_number -- ELINs project
2474 -- Event Based Delivery Project
2475 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_EVENT_CODE,null)
2476 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_PERIOD,null)
2477 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_PERIOD_UOM,null)
2478 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_PERIOD,null)
2479 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_PERIOD_UOM,null)
2480 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_POP_DURATION,null)
2481 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_POP_DURATION_UOM,null)
2482
2483 ) return interface_line_id into x_interface_line_id;
2484
2485 x_progress :=
2486 '35: CREATE_PO_STRUCTURE: INSERTING the following data into PO_LINES_INTERFACE: ' ||
2487 'interface_header_id: ' || to_char(x_interface_header_id) || ', ' ||
2488 'interface_line_id: ' || to_char(x_interface_line_id) || ', ' ||
2489 'requisition_line_id: ' || to_char(x_requisition_line_id) || ', ' ||
2490 'line_type_id: ' || to_char(x_pdoi_line.line_type_id) || ', ' ||
2491 'item_id: ' || to_char(x_pdoi_line.item_id) || ', ' ||
2492 'item_revision: ' || x_pdoi_line.item_revision || ', ' ||
2493 'category_id: ' || to_char(x_pdoi_line.category_id) || ', ' ||
2494 'item_description: ' || substrb(x_pdoi_line.item_description, 1, 240) || ', ';
2495
2496 IF (x_pdoi_line.order_type_lookup_code = 'AMOUNT') THEN
2497 x_progress := x_progress || 'unit_of_measure: ' || null || ', ' || 'quantity: ' || to_char(x_allocation_quantity) || ', ' ||
2498 'unit_price: ' || 1 || ', ';
2499 ELSE
2500 x_progress := x_progress || 'unit_of_measure: ' || x_pdoi_line.unit_of_measure || ' ' || 'quantity: ' || to_char(x_allocation_quantity) || ', ' ||
2501 'unit_price: ' || to_char(x_price) || ', ';
2502 END IF;
2503
2504 x_progress := x_progress ||
2505 'min_release_amount: ' || to_char(x_pdoi_line.po_min_rel_amount) || ', ' ||
2506 'ship_to_location_id: ' || to_char(x_pdoi_line.ship_to_location_id) ||', ' ||
2507 'need_by_date: ' || x_pdoi_line.need_by_date || ', ' ||
2508 'pop_start_date: ' || x_pdoi_line.pop_start_date || ', ' ||
2509 'pop_end_date: ' || x_pdoi_line.pop_end_date || ', ' ||
2510 'promised_date: ' || x_pdoi_line.promised_date || ', ' ||
2511 'last_update_by: ' || to_char(p_user_id) || ', ' ||
2512 'created_by: ' || to_char(p_user_id) || ', ' ||
2513 'auction_header_id: ' || to_char(x_pdoi_header.auction_header_id) || ', ' ||
2514 'document_number: ' || x_pdoi_header.document_number || ', ' ||
2515 'auction_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
2516 'bid_number: ' || to_char(x_pdoi_header.bid_number) || ', ' ||
2517 'bid_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
2518 'orig_from_req_flag: ' || 'Y';
2519
2520 x_progress := x_progress ||
2521 'line_num_display:' || x_pdoi_line.line_num_display || ', ' ||
2522 'group_line_id:' || To_Char(x_pdoi_line.group_line_id) || ', ' ||
2523 'clm_info_flag:' || x_pdoi_line.clm_info_flag || ', ' ||
2524 'clm_option_indicator:' || x_pdoi_line.clm_option_indicator || ', ' ||
2525 'clm_option_num:' || To_Char(x_pdoi_line.clm_option_num) || ', ' ||
2526 'clm_option_from_date:' || x_pdoi_line.clm_option_from_date || ', ' ||
2527 'clm_option_to_date:' || x_pdoi_line.clm_option_to_date || ', ' ||
2528 'clm_base_line_num:' || To_Char(x_pdoi_line.clm_base_line_num) || ', ' ||
2529 'CONTRACT_TYPE:' || x_pdoi_line.CONTRACT_TYPE || ', ' ||
2530 'COST_CONSTRAINT:' || x_pdoi_line.COST_CONSTRAINT || ', ' ||
2531 'CLM_IDC_TYPE:' || x_pdoi_line.CLM_IDC_TYPE || ', ' ||
2532 'clm_funded_flag:' || x_pdoi_line.clm_funded_flag || ', ' ||
2533 'exhibit_number:' || x_pdoi_line.exhibit_number || ', ';
2534
2535
2536 log_message(x_progress);
2537
2538
2539
2540
2541
2542
2543
2544 END LOOP; --} -- stop loop over reqBackingBidItem
2545
2546 CLOSE reqBackingBidItem;
2547
2548 END IF; --} -- end-if to check for requisitions
2549
2550
2551
2552 /*
2553 rrkulkar-large-auction-support :-
2554
2555 Once we have inserted all the lines having allocated quantities
2556 with backing reqs, we need to take care of the following 3 more
2557 conditions :-
2558
2559 case-1. No backing requisition for current line OR
2560 case-2. Lines with backing requisitions have 0 allocation
2561 award quantities
2562 case-3. There is an excess award OR
2563
2564 In either of the aforementioned 3 cases, we need to
2565 insert an additional row into PO_LINES_INTERFACE with the
2566 item information from the negotiation line, a null
2567 requisition_line_id, a quantity for the excess award,
2568 and a value of 'N' in the orig_from_req_flag column.
2569
2570 */
2571
2572 /*
2573 rrkulkar-large-auction-support : In case of super-large auctions,
2574 this case will be satisfied more often than not. Here's what we can do :-
2575
2576 1. split this insert into 2 cases -
2577 1a. use cursor approach for lines with backing reqs. (case-2 and case-3 above)
2578 1b. use batching for lines with no backing reqs
2579 */
2580
2581 x_progress := '25: before execess award';
2582
2583 log_message(x_progress);
2584
2585 --Bug : 14134092 : If line type structure changes are enabled, then fund allocation will be done for all lines.
2586 IF (l_is_line_type_enabled = 'N' AND
2587 /* case-2 :- zero allocated quantity*/
2588
2589 ((x_pdoi_line.line_origination_code = 'REQUISITION' AND
2590 sum_of_alloc_quantities.EXISTS(x_line_number) AND
2591 sum_of_alloc_quantities(x_line_number) = 0 AND
2592 Nvl(x_pdoi_line.clm_info_flag,'N') = 'N') OR
2593
2594 /* case-3 :- excess allocated quantity*/
2595
2596 (x_pdoi_line.line_origination_code = 'REQUISITION' AND
2597 sum_of_alloc_quantities.EXISTS(x_line_number) AND
2598 x_award_quantity > sum_of_alloc_quantities(x_line_number)))) THEN
2599
2600 --{ -- 2nd loop for SPO outcome
2601
2602 x_progress := '30: Excess award ' || 'Award Quantity: ' || x_award_quantity;
2603 log_message(x_progress);
2604
2605 IF (x_pdoi_line.order_type_lookup_code = 'RATE' OR
2606 x_pdoi_line.order_type_lookup_code = 'FIXED PRICE') THEN
2607 x_quantity := NULL;
2608 ELSIF (sum_of_alloc_quantities.EXISTS(x_line_number)) THEN
2609 x_quantity := x_award_quantity - sum_of_alloc_quantities(x_line_number);
2610 ELSE
2611 x_quantity := x_award_quantity;
2612 END IF;
2613 INSERT into PO_LINES_INTERFACE (
2614 interface_header_id,
2615 interface_line_id,
2616 requisition_line_id,
2617 line_type_id,
2618 item_id,
2619 item_revision,
2620 category_id,
2621 item_description,
2622 unit_of_measure,
2623 quantity,
2624 unit_price,
2625 min_release_amount,
2626 ship_to_location_id,
2627 need_by_date,
2628 clm_period_perf_start_date,
2629 clm_period_perf_end_date,
2630 promised_date,
2631 last_updated_by,
2632 last_update_date,
2633 created_by,
2634 creation_date,
2635 auction_header_id,
2636 auction_display_number,
2637 auction_line_number,
2638 bid_number,
2639 bid_line_number,
2640 orig_from_req_flag,
2641 job_id,
2642 amount
2643 , advance_amount
2644 , recoupment_rate
2645 , progress_payment_rate
2646 , retainage_rate
2647 , max_retainage_amount
2648 , Line_loc_populated_flag,
2649
2650 -- Clin Slin Changes
2651 line_num_display,
2652 group_line_id,
2653 clm_info_flag,
2654 clm_option_indicator,
2655 clm_option_num,
2656 clm_option_from_date,
2657 clm_option_to_date,
2658 clm_funded_flag,
2659 clm_base_line_num,
2660
2661 -- Complex Pricing Changes
2662 CONTRACT_TYPE,
2663 COST_CONSTRAINT,
2664 CLM_IDC_TYPE,
2665 --CLM Order off idv project
2666 from_header_id,
2667 from_line_id,
2668 clm_exhibit_name, --ELINs project
2669 -- Event Based Delivery Project
2670 CLM_DELIVERY_EVENT_CODE,
2671 CLM_DELIVERY_PERIOD,
2672 CLM_DELIVERY_PERIOD_UOM,
2673 CLM_PROMISE_PERIOD,
2674 CLM_PROMISE_PERIOD_UOM,
2675 CLM_POP_DURATION,
2676 CLM_POP_DURATION_UOM
2677 )
2678
2679 values (
2680
2681 x_interface_header_id, -- interface_header_id
2682 po_lines_interface_s.nextval, -- interface_line_id
2683 NULL, -- requisition_line_id
2684 x_pdoi_line.line_type_id,
2685 -- line_type_id
2686 x_pdoi_line.item_id,
2687 -- item_id
2688 x_pdoi_line.item_revision,
2689 -- item_revision
2690 x_pdoi_line.category_id,
2691 -- category_id
2692 substrb(x_pdoi_line.item_description, 1, 240),
2693 -- item_description
2694 decode(x_pdoi_line.order_type_lookup_code, 'AMOUNT', null, x_pdoi_line.unit_of_measure), -- unit_of_measure
2695 x_quantity, -- quantity
2696 decode(x_pdoi_line.order_type_lookup_code,'AMOUNT', 1, x_price), -- unit_price
2697 x_pdoi_line.po_min_rel_amount, -- min_release_amount
2698 x_pdoi_line.ship_to_location_id,
2699 -- ship_to_location_id
2700 /*Bug : 10212430 : For clm : need_by_date <-> clm_need_by_date and Promise Start Date <-> need_by_start_date */
2701 Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.need_by_date,x_pdoi_line.pop_start_date),
2702 -- need_by_date
2703 Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.pop_start_date,null),
2704 -- period_of_performance_start_date
2705 Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.pop_end_date,null),
2706 -- period_of_performance_end_date
2707 x_pdoi_line.promised_date,
2708 -- promised_date
2709 p_user_id, -- last_update_by
2710 sysdate, -- last_update_date
2711 p_user_id, -- created_by
2712 sysdate, -- creation_date
2713 x_pdoi_header.auction_header_id, -- auction_header_id
2714 x_pdoi_header.document_number, -- document_number
2715 x_pdoi_line.line_number, -- auction_line_number,
2716 x_pdoi_header.bid_number, -- bid_number
2717 x_pdoi_line.line_number, -- bid_line_number
2718 'N', -- orig_from_req_flag
2719 x_pdoi_line.job_id, -- job_id
2720 x_amount -- amount
2721 , decode(x_pdoi_line.bid_curr_advance_amount,0,null,x_pdoi_line.bid_curr_advance_amount)
2722 , x_pdoi_line.recoupment_rate_percent
2723 , x_pdoi_line.progress_pymt_rate_percent
2724 , x_pdoi_line.retainage_rate_percent
2725 , x_pdoi_line.Bid_curr_max_retainage_amt
2726 , x_pdoi_line.has_bid_payments_flag -- Line_loc_populated
2727 --Clin Slin Changes
2728 ,x_pdoi_line.line_num_display
2729 ,x_pdoi_line.group_line_id
2730 ,x_pdoi_line.clm_info_flag
2731 ,x_pdoi_line.clm_option_indicator
2732 ,x_pdoi_line.clm_option_num
2733 ,x_pdoi_line.clm_option_from_date
2734 ,x_pdoi_line.clm_option_to_date
2735 ,x_pdoi_line.clm_funded_flag
2736 ,x_pdoi_line.clm_base_line_num
2737 ,x_pdoi_line.CONTRACT_TYPE
2738 ,x_pdoi_line.COST_CONSTRAINT
2739 ,x_pdoi_line.CLM_IDC_TYPE
2740 --CLM Order off idv project
2741 ,x_pdoi_header.idv_header_id
2742 ,x_pdoi_line.idv_line_id
2743 ,x_pdoi_line.exhibit_number --ELINs project
2744 -- Event Based Delivery Project
2745 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_EVENT_CODE,null)
2746 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_PERIOD,null)
2747 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_PERIOD_UOM,null)
2748 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_PERIOD,null)
2749 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_PERIOD_UOM,null)
2750 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_POP_DURATION,null)
2751 ,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_POP_DURATION_UOM,null)
2752
2753 ) return interface_line_id into x_interface_line_id;
2754
2755
2756 x_progress :=
2757 '35: CREATE_PO_STRUCTURE: INSERTING the following data into PO_LINES_INTERFACE: ' ||
2758 'interface_header_id: ' || to_char(x_interface_header_id) || ', ' ||
2759 'interface_line_id: ' || to_char(x_interface_line_id) || ', ' ||
2760 'requisition_line_id: ' || null || ', ' ||
2761 'line_type_id: ' || to_char(x_pdoi_line.line_type_id) || ', ' ||
2762 'item_id: ' || to_char(x_pdoi_line.item_id) || ', ' ||
2763 'item_revision: ' || x_pdoi_line.item_revision || ', ' ||
2764 'category_id: ' || to_char(x_pdoi_line.category_id) || ', ' ||
2765 'item_description: ' || substrb(x_pdoi_line.item_description, 1, 240) || ', ';
2766
2767 IF (x_pdoi_line.order_type_lookup_code = 'AMOUNT') THEN
2768 x_progress := x_progress || 'unit_of_measure: ' || null || ', ' ||
2769 'quantity: ' || x_quantity || ', ' ||
2770 'unit_price: ' || 1 || ',';
2771 ELSE
2772 x_progress := x_progress || 'unit_of_measure: ' || x_pdoi_line.unit_of_measure || ' ' ||
2773 'quantity: ' || x_quantity || ', ' ||
2774 'unit_price: ' || to_char(x_price) || ', ';
2775 END IF;
2776
2777 x_progress := x_progress ||
2778 'min_releaes_amount: ' || to_char(x_pdoi_line.po_min_rel_amount) || ', ' ||
2779 'ship_to_location_id: ' || to_char(x_pdoi_line.ship_to_location_id) ||', ' ||
2780 'need_by_date: ' || x_pdoi_line.need_by_date || ', ' ||
2781 'pop_start_date: ' || x_pdoi_line.pop_start_date || ', ' ||
2782 'pop_end_date: ' || x_pdoi_line.pop_end_date || ', ' ||
2783 'promised_date: ' || x_pdoi_line.promised_date || ', ' ||
2784 'last_update_by: ' || to_char(p_user_id) || ', ' ||
2785 'created_by: ' || to_char(p_user_id) || ', ' ||
2786 'auction_header_id: ' || to_char(x_pdoi_header.auction_header_id) || ', ' ||
2787 'document_number: ' || x_pdoi_header.document_number || ', ' ||
2788 'auction_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
2789 'bid_number: ' || to_char(x_pdoi_header.bid_number) || ', ' ||
2790 'bid_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
2791 'orig_from_req_flag: ' || 'N' || ', ';
2792
2793 x_progress := x_progress ||
2794 'line_num_display:' || x_pdoi_line.line_num_display || ', ' ||
2795 'group_line_id:' || To_Char(x_pdoi_line.group_line_id) || ', ' ||
2796 'clm_info_flag:' || x_pdoi_line.clm_info_flag || ', ' ||
2797 'clm_option_indicator:' || x_pdoi_line.clm_option_indicator || ', ' ||
2798 'clm_option_num:' || To_Char(x_pdoi_line.clm_option_num) || ', ' ||
2799 'clm_option_from_date:' || x_pdoi_line.clm_option_from_date || ', ' ||
2800 'clm_option_to_date:' || x_pdoi_line.clm_option_to_date || ', ' ||
2801 'clm_base_line_num:' || To_Char(x_pdoi_line.clm_base_line_num) || ', ' ||
2802 'CONTRACT_TYPE:' || x_pdoi_line.CONTRACT_TYPE || ', ' ||
2803 'COST_CONSTRAINT:' || x_pdoi_line.COST_CONSTRAINT || ', ' ||
2804 'CLM_IDC_TYPE:' || x_pdoi_line.CLM_IDC_TYPE || ', ' ||
2805 'clm_funded_flag:' || x_pdoi_line.clm_funded_flag || ', ' ||
2806 'exhibit_number:' || x_pdoi_line.exhibit_number || ', '; --ELINs project
2807
2808 log_message(x_progress);
2809
2810
2811 END IF; --} -- End of excess award
2812
2813
2814
2815 -- clm clin/slin changes
2816 -- end of clm clin/slin changes
2817
2818
2819
2820 END LOOP; --} -- stop loop over reqlineLevelInfo
2821
2822 close reqlineLevelInfo;
2823
2824
2825 /* rrkulkar-large-auction-support changes */
2826 --------------------------------------------------------------------------------------------------------------
2827 --BATCHING FOR OUTCOME = STANDARD PURCHASE ORDER : STARTS HERE
2828 --------------------------------------------------------------------------------------------------------------
2829
2830 --get the number of rows to be copied
2831 select nvl(max(line_number),0)
2832 into l_max_line_number
2833 from pon_bid_item_prices
2834 where bid_number = x_pdoi_header.bid_number
2835 AND auction_line_number <> -1; --Unsolicited Lines Project
2836
2837 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
2838 l_commit_flag := FALSE;
2839
2840 l_batch_start := 1;
2841
2842 IF (l_max_line_number <l_batch_size) THEN
2843 l_batch_end := l_max_line_number;
2844 ELSE
2845 l_batch_end := l_batch_size;
2846 END IF;
2847
2848 log_message('spo batching start: l_batch_size=' || l_batch_size || ' l_batch_start=' || l_batch_start || ' l_batch_end=' || l_batch_end);
2849
2850
2851 WHILE (l_batch_start <= l_max_line_number) LOOP --{ main-batching-loop--spo
2852
2853
2854 log_message('spo batching start: l_batch_size=' || l_batch_size || ' l_batch_start=' || l_batch_start || ' l_batch_end=' || l_batch_end);
2855
2856
2857 /* case-1: Lines with no backing reqs*/
2858
2859 /*
2860 rrkulkar-large-auction-support : study
2861
2862 need to verify the resolution of the following columns
2863 1. quantity
2864 2. unit_price
2865
2866 need to find out about x_interface_line_id :- is it used for debugging purposes only?
2867 */
2868
2869 INSERT into PO_LINES_INTERFACE (
2870 interface_header_id,
2871 interface_line_id,
2872 requisition_line_id,
2873 line_type_id,
2874 item_id,
2875 item_revision,
2876 category_id,
2877 item_description,
2878 unit_of_measure,
2879 quantity,
2880 unit_price,
2881 min_release_amount,
2882 ship_to_location_id,
2883 need_by_date,
2884 clm_period_perf_start_date,
2885 clm_period_perf_end_date,
2886 promised_date,
2887 last_updated_by,
2888 last_update_date,
2889 created_by,
2890 creation_date,
2891 auction_header_id,
2892 auction_display_number,
2893 auction_line_number,
2894 bid_number,
2895 bid_line_number,
2896 orig_from_req_flag,
2897 job_id,
2898 amount
2899 , advance_amount
2900 , recoupment_rate
2901 , progress_payment_rate
2902 , retainage_rate
2903 , max_retainage_amount
2904 , Line_loc_populated_flag,
2905 -- Clin Slin Changes
2906 line_num_display,
2907 group_line_id,
2908 clm_info_flag,
2909 clm_option_indicator,
2910 clm_option_num,
2911 clm_option_from_date,
2912 clm_option_to_date,
2913 clm_funded_flag,
2914 clm_base_line_num,
2915 -- Complex Pricing Changes
2916 CONTRACT_TYPE,
2917 COST_CONSTRAINT,
2918 CLM_IDC_TYPE,
2919 -- CLM Order Off IDV Project
2920 from_header_id,
2921 from_line_id,
2922 clm_exhibit_name, --ELINs project
2923 -- Event Based Delivery Project
2924 CLM_DELIVERY_EVENT_CODE,
2925 CLM_DELIVERY_PERIOD,
2926 CLM_DELIVERY_PERIOD_UOM,
2927 CLM_PROMISE_PERIOD,
2928 CLM_PROMISE_PERIOD_UOM,
2929 CLM_POP_DURATION,
2930 CLM_POP_DURATION_UOM
2931 )
2932 SELECT
2933 x_interface_header_id, -- interface_header_id
2934 po_lines_interface_s.nextval, -- interface_line_id
2935 NULL, -- requisition_line_id
2936 paip.line_type_id, -- line_type_id
2937 paip.item_id, -- item_id
2938 paip.item_revision, -- item_revision
2939 paip.category_id, -- category_id
2940 substrb(paip.item_description, 1, 240), -- item_description
2941 decode(paip.order_type_lookup_code, 'AMOUNT', null, mtluom.unit_of_measure), -- unit_of_measure
2942 decode(paip.order_type_lookup_code, 'RATE', TO_NUMBER(null),
2943 'FIXED PRICE', TO_NUMBER(null),
2944 'AMOUNT', pbip.bid_currency_unit_price,
2945 pbip.award_quantity), -- QUANTITY
2946 decode(paip.order_type_lookup_code,'AMOUNT', 1,
2947 'FIXED PRICE', TO_NUMBER(NULL)
2948 ,nvl2( pbip.award_shipment_number,pbs.bid_currency_unit_price
2949 ,pbip.bid_currency_unit_price)), --unit_price
2950 nvl(pbip.po_bid_min_rel_amount, round(paip.po_min_rel_amount* pbh.rate,fc.precision)), -- min_release_amount
2951 paip.ship_to_location_id, -- ship_to_location_id
2952 /*Clm Bug : 10212430 : For clm,copy POP dates from offer. */
2953 Decode(Nvl(l_is_fed,'N'),'Y',paip.clm_need_by_date,paip.need_by_start_date), -- need_by_date
2954 Decode(Nvl(l_is_fed,'N'),'Y',pbip.promise_pop_start_date,null), -- period_of_performance_start_date
2955 Decode(Nvl(l_is_fed,'N'),'Y',pbip.promise_pop_end_date,null), -- period_of_performance_end_date
2956 pbip.promised_date, -- promised_date
2957 p_user_id, -- last_update_by
2958 sysdate, -- last_update_date
2959 p_user_id, -- created_by
2960 sysdate, -- creation_date
2961 x_pdoi_header.auction_header_id, -- auction_header_id
2962 x_pdoi_header.document_number, -- document_number
2963 paip.line_number, -- auction_line_number,
2964 pbip.bid_number, -- bid_number
2965 pbip.line_number, -- bid_line_number
2966 Decode('Y', (SELECT is_linked_pr_line_yn FROM pon_award_allocations paa
2967 WHERE paa.bid_number = pbip.bid_number AND paa.bid_line_number = pbip.line_number AND paa.is_linked_pr_line_yn = 'Y' AND ROWNUM <2),
2968 'S', 'N'), -- orig_from_req_flag
2969 paip.job_id, -- job_id
2970 decode(paip.order_type_lookup_code,'FIXED PRICE', pbip.bid_currency_unit_price, TO_NUMBER(NULL)) -- amount
2971 , decode(pbip.bid_curr_advance_amount,0,null,pbip.bid_curr_advance_amount)
2972 , pbip.recoupment_rate_percent
2973 , pbip.progress_pymt_rate_percent
2974 , pbip.retainage_rate_percent
2975 , pbip.Bid_curr_max_retainage_amt
2976 , decode(pbip.has_bid_payments_flag, 'Y', decode((select 1 from dual where exists
2977 ( select 1 from pon_bid_payments_shipments where
2978 auction_header_id = pbip.auction_header_id and
2979 bid_number= pbip.bid_number and bid_line_number=pbip.line_number
2980 and bid_currency_price <> 0
2981 )
2982 ),
2983 1,'Y','N')
2984 ,'N') --Line_loc_populated
2985 , paip.line_num_display
2986 , paip.group_line_id
2987 , paip.clm_info_flag
2988 , paip.clm_option_indicator
2989 , paip.clm_option_num
2990 , paip.clm_option_from_date
2991 , paip.clm_option_to_date
2992 , paip.clm_funded_flag
2993 , paip.clm_base_line_num
2994 , paip.CLM_CONTRACT_TYPE
2995 , paip.CLM_COST_CONSTRAINT
2996 , paip.CLM_IDC_TYPE
2997 --CLM Order Off IDV Project
2998 , pbh.idv_header_id
2999 , pbip.idv_line_id
3000 , paip.exhibit_number --ELINs project
3001 -- Event Based Delivery Project
3002 ,Decode(Nvl(l_is_fed,'N'),'Y',paip.CLM_DELIVERY_EVENT_CODE,null)
3003 ,Decode(Nvl(l_is_fed,'N'),'Y',paip.CLM_DELIVERY_PERIOD,null)
3004 ,Decode(Nvl(l_is_fed,'N'),'Y',paip.CLM_DELIVERY_PERIOD_UOM,null)
3005 ,Decode(Nvl(l_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD,null)
3006 ,Decode(Nvl(l_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD_UOM,null)
3007 ,Decode(Nvl(l_is_fed,'N'),'Y',pbip.CLM_PROMISE_POP_DURATION,null)
3008 ,Decode(Nvl(l_is_fed,'N'),'Y',pbip.CLM_PROMISE_POP_DURATION_UOM,null)
3009
3010 FROM
3011 pon_auction_item_prices_all paip,
3012 pon_bid_item_prices pbip,
3013 mtl_units_of_measure mtluom,
3014 pon_bid_headers pbh,
3015 fnd_currencies fc,
3016 pon_bid_shipments pbs
3017 WHERE
3018 pbip.bid_number = p_bid_number and
3019 pbip.auction_header_id = p_auction_header_id and
3020 nvl(pbip.award_status, 'NO') = 'AWARDED' and
3021 Nvl(pbip.order_number, -1) = -1 and --Staggered Awards project
3022 paip.auction_header_id = pbip.auction_header_id and
3023 paip.line_number = pbip.line_number and
3024 paip.group_type NOT IN ('GROUP','LOT_LINE') and
3025 paip.uom_code = mtluom.uom_code (+) and
3026 pbh.bid_number = pbip.bid_number and
3027 fc.currency_code = pbh.bid_currency_code and
3028 (nvl(paip.line_origination_code, 'NO') <> 'REQUISITION'
3029 OR l_is_line_type_enabled = 'Y') and --Bug : 14134092 : If line type structure changes are enabled, insert autocreated lines as normal lines.
3030 pbip.line_number >= l_batch_start and
3031 pbip.line_number <= l_batch_end and
3032 pbs.bid_number(+) = pbip.bid_number and
3033 pbs.line_number(+) = pbip.line_number and
3034 pbs.shipment_number(+) = pbip.award_shipment_number;
3035
3036
3037
3038
3039
3040 -- clm clin/slin changes
3041
3042 -- end of clm clin/slin changes
3043
3044
3045
3046 x_progress := '35.1: CREATE_PO_STRUCTURE: STANDARD CASE: END OF BULK INSERT';
3047
3048 log_message(x_progress);
3049
3050 x_progress := '35.1.1: CREATE_PAYMENTS: STANDARD CASE: CHECK IF COMPLEX WORK';
3051
3052 log_message(x_progress);
3053
3054 -- Insert all Payments for all lines in one go, if any and complex work
3055 IF (x_pdoi_header.progress_payment_type IN ('ACTUAL','FINANCE')) THEN
3056 x_progress := '35.1.5: CREATE_PAYMENTS: STANDARD CASE: IT IS COMPLEX WORK';
3057
3058 log_message(x_progress);
3059
3060 INSERT INTO po_line_locations_interface (
3061 interface_header_id,
3062 interface_line_id,
3063 interface_line_location_id,
3064 payment_type,
3065 shipment_num,
3066 ship_to_location_id,
3067 need_by_date,
3068 promised_date,
3069 quantity,
3070 unit_of_measure,
3071 price_override,
3072 amount,
3073 description,
3074 work_approver_id,
3075 project_id,
3076 task_id,
3077 award_id,
3078 expenditure_type,
3079 expenditure_organization_id,
3080 expenditure_item_date,
3081 auction_payment_id,
3082 bid_payment_id,
3083 last_update_date,
3084 last_updated_by,
3085 creation_date,
3086 created_by )
3087
3088 SELECT
3089 x_interface_header_id, -- interface_header_id
3090 pli.interface_line_id, -- interface_line_id
3091 po_line_locations_interface_s.NEXTVAL,
3092 -- interface_line_location_id
3093 bpys.payment_type_code, -- shipment_type
3094 bpys.payment_display_number, -- shipment_num
3095 nvl(apys.ship_to_location_id,
3096 paip. ship_to_location_id), -- ship_to_location_id
3097 decode(x_pdoi_header.supplier_enterable_pymt_flag,
3098 'Y', paip.need_by_date , apys.need_by_date), -- need_by_date
3099 bpys.promised_date, -- promised_date
3100 nvl(bpys.quantity, decode(paip.order_type_lookup_code,
3101 'QUANTITY',
3102 pli.quantity, null
3103 )
3104 ) , -- quantity. Populate this for RATE and Qty Milestone
3105
3106 nvl2(bpys.uom_code, mtluom.unit_of_measure,
3107 decode(paip.order_type_lookup_code, 'QUANTITY',
3108 (select unit_of_measure from
3109 mtl_units_of_measure where uom_code=
3110 paip.uom_code),
3111 null
3112 )
3113 ) , -- unit_of_measure.Populate this for RATE and Qty Milestone
3114
3115 nvl2(bpys.quantity, bpys.bid_currency_price,
3116 decode(paip.order_type_lookup_code,'QUANTITY',
3117 bpys.bid_currency_price, null)
3118 ), -- price_override. Populate this for RATE and Qty Milestone
3119
3120 nvl2(bpys.quantity, null,
3121 decode(paip.order_type_lookup_code, 'QUANTITY',
3122 null, bpys.bid_currency_price)
3123 ),-- amount.Populate this for LUMPSUM and Fixed Price Milestone
3124
3125 bpys.payment_description, -- item_description
3126 decode(x_pdoi_header.supplier_enterable_pymt_flag,
3127 'Y',paip.work_approver_user_id, apys.work_approver_user_id),
3128 -- Work_approver_user_id
3129
3130 decode(x_pdoi_header.supplier_enterable_pymt_flag,
3131 'Y', paip. project_id , apys.project_id), -- project_id
3132 decode(x_pdoi_header.supplier_enterable_pymt_flag,
3133 'Y', paip. project_task_id , apys.project_task_id),
3134 -- project_task_id
3135 decode(x_pdoi_header.supplier_enterable_pymt_flag,
3136 'Y', paip.project_award_id,apys.project_award_id),
3137 -- project_award_id
3138 decode(x_pdoi_header.supplier_enterable_pymt_flag,
3139 'Y', paip.project_expenditure_type,
3140 apys.project_expenditure_type),
3141 -- project_expenditure_type
3142 decode(x_pdoi_header.supplier_enterable_pymt_flag,
3143 'Y', paip. project_exp_organization_id,
3144 apys.project_exp_organization_id),
3145 -- project_exp_organization_id
3146 decode(x_pdoi_header.supplier_enterable_pymt_flag, 'Y',
3147 paip. project_expenditure_item_date,
3148 apys.project_expenditure_item_date),
3149 -- project_expenditure_date
3150 bpys.auction_payment_id , -- auction_payment_id
3151 bpys.bid_payment_id, -- bid_payment_id
3152 sysdate, -- last_update_date
3153 x_user_id, -- last_updated_by
3154 sysdate, -- creation_date
3155 x_user_id -- created_by
3156
3157 FROM pon_auction_item_prices_all paip,
3158 pon_bid_item_prices pbip,
3159 pon_bid_payments_shipments bpys,
3160 pon_auc_payments_shipments apys,
3161 po_lines_interface pli,
3162 mtl_units_of_measure mtluom
3163 WHERE pbip.bid_number = p_bid_number and
3164 pbip.auction_header_id = p_auction_header_id and
3165 nvl(pbip.award_status, 'NO') = 'AWARDED' and
3166 paip.auction_header_id = pbip.auction_header_id and
3167 paip.line_number = pbip.line_number and
3168 bpys.bid_number = pbip.bid_number and
3169 bpys.bid_line_number = pbip.line_number and
3170 pli.interface_header_id = x_interface_header_id and
3171 pli.auction_line_number = paip.line_number and
3172 pli.auction_header_id = paip.auction_header_id and
3173 bpys.auction_payment_id = apys.payment_id (+) and
3174 bpys.uom_code = mtluom.uom_code (+) and
3175 nvl(bpys.bid_currency_price,0) <> 0 and
3176 pbip.line_number >= l_batch_start and
3177 pbip.line_number <= l_batch_end;
3178
3179 x_progress := '35.1.10: CREATE_PAYMENTS: STANDARD CASE: END INSERTING PAYMENTS';
3180
3181 log_message(x_progress);
3182 END IF;-- if complex work
3183
3184
3185 x_progress := '35.2: CREATE_PO_STRUCTURE: STANDARD CASE: BATCH FROM '
3186 || l_batch_start ||' TO '||l_batch_end ||' (inclusive)';
3187 log_message(x_progress);
3188 l_batch_start := l_batch_end + 1;
3189
3190 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
3191 l_batch_end := l_max_line_number;
3192 l_commit_flag := FALSE;
3193 ELSE
3194 l_batch_end := l_batch_end + l_batch_size;
3195 l_commit_flag := TRUE;
3196 END IF;
3197
3198 /*
3199 Note from ATG-WF website :-
3200
3201 You CANNOT commit inside a PL/SQL procedure which is called by the workflow engine.
3202 If you issue a commit you are committing the workflow state as well as your application
3203 state. If you do commit and your pl/sql function fails subsequently the workflow engine
3204 will not be able to rollback to a consistent state.
3205 */
3206
3207 IF(l_commit_flag = TRUE) THEN
3208 COMMIT;
3209 x_progress := '35.3: CREATE_PO_STRUCTURE: STANDARD CASE: BATCH-COMMIT SUCCESSFUL ';
3210 log_message(x_progress);
3211 END IF;
3212
3213 END LOOP; --} --end-loop- batching-SPO
3214
3215 /* Unsolicited Lines Project : If unsolicited lines are enabled,
3216 insert into po_lines_interface.
3217 */
3218 IF l_allow_unsol_lines = 'Y' THEN
3219 log_message('SPO : l_allow_unsol_lines '||l_allow_unsol_lines);
3220 log_message('SPO : Before inserting unsolicited lines. ');
3221 PON_UNSOL_CREATE_PO_PKG.insert_unsol_lines_spo(p_interface_header_id => x_interface_header_id,
3222 p_auction_header_id => p_auction_header_id,
3223 p_bid_number => p_bid_number,
3224 p_document_number => x_pdoi_header.document_number,
3225 p_is_fed => Nvl(l_is_fed,'N'),
3226 p_user_id => p_user_id);
3227 log_message('SPO : After inserting unsolicited lines succesfully. ');
3228 END IF;
3229
3230
3231 --------------------------------------------------------------------------------------------------------------
3232 --BATCHING FOR OUTCOME = STANDARD PURCHASE ORDER : ENDS HERE
3233 --------------------------------------------------------------------------------------------------------------
3234
3235 END IF; -- End of Standard
3236
3237
3238 /* Blanket Agreement case: will do bulk insert from one table to another */
3239
3240 IF (x_pdoi_header.contract_type = 'BLANKET') THEN --{ -- if outcome is BPA
3241
3242 /* rrkulkar-large-auction-support changes */
3243 --------------------------------------------------------------------------------------------------------------
3244 --BATCHING FOR OUTCOME = BLANKET PURCHASE AGREEMENT: STARTS HERE
3245 --------------------------------------------------------------------------------------------------------------
3246
3247 --get the number of rows to be copied
3248 select nvl(max(line_number),0)
3249 into l_max_line_number
3250 from pon_bid_item_prices
3251 where bid_number = x_pdoi_header.bid_number;
3252
3253 -- always reset -> although it is not possible that both bpa+spo cases are satisfied :)
3254 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3255 l_commit_flag := FALSE;
3256 l_rows_processed := 0;
3257
3258 l_batch_start := 1;
3259
3260 IF (l_max_line_number <l_batch_size) THEN
3261 l_batch_end := l_max_line_number;
3262 ELSE
3263 l_batch_end := l_batch_size;
3264 END IF;
3265
3266 log_message('blanket batching start: l_batch_size=' || l_batch_size || ' l_batch_start=' || l_batch_start || ' l_batch_end=' || l_batch_end);
3267
3268 WHILE (l_batch_start <= l_max_line_number) LOOP --{ main-batching-loop--spo
3269
3270 log_message('blanket batching loop: l_batch_size=' || l_batch_size || ' l_batch_start=' || l_batch_start || ' l_batch_end=' || l_batch_end);
3271
3272 /* Bug 9915090 - In Case of IDVs quantity should be populated with extended price / offer price. */
3273
3274 INSERT into PO_LINES_INTERFACE (
3275 interface_header_id,
3276 interface_line_id,
3277 requisition_line_id,
3278 line_type_id,
3279 line_num,
3280 item_id,
3281 item_revision,
3282 category_id,
3283 ip_category_id,
3284 item_description,
3285 unit_of_measure,
3286 price_break_lookup_code,
3287 quantity,
3288 committed_amount,
3289 unit_price,
3290 min_release_amount,
3291 ship_to_location_id,
3292 need_by_date,
3293 clm_period_perf_start_date,
3294 clm_period_perf_end_date,
3295 promised_date,
3296 last_updated_by,
3297 last_update_date,
3298 created_by,
3299 creation_date,
3300 auction_header_id,
3301 auction_display_number,
3302 auction_line_number,
3303 bid_number,
3304 bid_line_number,
3305 orig_from_req_flag,
3306 job_id,
3307 amount,
3308
3309 -- Clin Slin Changes
3310 line_num_display,
3311 group_line_id,
3312 clm_info_flag,
3313 clm_option_indicator,
3314 clm_option_num,
3315 clm_option_from_date,
3316 clm_option_to_date,
3317 clm_funded_flag,
3318 clm_base_line_num,
3319
3320 -- Complex Pricing Changes
3321 CONTRACT_TYPE,
3322 COST_CONSTRAINT,
3323 CLM_IDC_TYPE,
3324 --CLM Order Off IDV Project
3325 from_header_id,
3326 from_line_id,
3327 clm_exhibit_name --ELINs project
3328
3329 )
3330 SELECT
3331 x_interface_header_id, -- interface_header_id
3332 po_lines_interface_s.nextval, -- interface_line_id
3333 NULL, -- requisition_line_id
3334 paip.line_type_id, -- line_type_id
3335 l_rows_processed + rownum, -- line num
3336 paip.item_id,
3337 -- item_id
3338 paip.item_revision,
3339 -- item_revision
3340 paip.category_id,
3341 -- category_id
3342 nvl(paip.ip_category_id, -2),
3343 -- ip category id
3344 substrb(paip.item_description, 1, 240),
3345 -- item_description
3346 decode(paip.order_type_lookup_code, 'AMOUNT', null, mtluom.unit_of_measure),
3347 -- unit_of_measure
3348 decode(pbip.price_break_type, 'NONE', null, 'NON-CUMULATIVE', 'NON CUMULATIVE', pbip.price_break_type),
3349 -- price_break_type
3350 decode(paip.order_type_lookup_code,
3351 'AMOUNT', Decode(Nvl(l_is_fed,'N'),'Y',pbip.bid_currency_unit_price,NULL),
3352 'RATE', NULL,
3353 'FIXED PRICE', NULL,
3354 pbip.award_quantity), -- quantity
3355 decode(paip.order_type_lookup_code,
3356 'AMOUNT', pbip.bid_currency_unit_price,
3357 'RATE', round(paip.po_agreed_amount*pbh.rate, fc.precision),
3358 'FIXED PRICE', round(paip.po_agreed_amount*pbh.rate, fc.precision),
3359 null), -- committed_amount
3360 decode(paip.order_type_lookup_code,
3361 'AMOUNT', 1,
3362 'FIXED PRICE', null,
3363 nvl2( pbip.award_shipment_number,pbs.bid_currency_unit_price
3364 ,pbip.bid_currency_unit_price)), --unit_price
3365 nvl(pbip.po_bid_min_rel_amount, round(paip.po_min_rel_amount* pbh.rate, fc.precision)), -- min_release_amount
3366 paip.ship_to_location_id, -- ship_to_location_id
3367 Decode(Nvl(l_is_fed,'N'),'Y',paip.clm_need_by_date,paip.need_by_start_date), -- need_by_date
3368 Decode(Nvl(l_is_fed,'N'),'Y',paip.need_by_start_date,null), -- period_of_performance_start_date
3369 Decode(Nvl(l_is_fed,'N'),'Y',paip.need_by_date,null), -- period_of_performance_end_date
3370 pbip.promised_date, -- promised_date
3371 p_user_id, -- last_update_by
3372 sysdate, -- last_update_date
3373 p_user_id, -- created_by
3374 sysdate, -- creation_date
3375 x_pdoi_header.auction_header_id, -- auction_header_id
3376 x_pdoi_header.document_number, -- document_number
3377 paip.line_number, -- auction_line_number,
3378 x_pdoi_header.bid_number, -- bid_number
3379 paip.line_number, -- bid_line_number
3380 decode(paip.line_origination_code, 'REQUISITION', 'Y', 'N'), -- orig_from_req_flag
3381 paip.job_id, -- job_id
3382 decode(paip.order_type_lookup_code,
3383 'FIXED PRICE', round(pbip.bid_currency_unit_price, fc.precision),
3384 null) -- amount
3385
3386 -- Clin Slin Changes
3387 , paip.line_num_display
3388 , paip.group_line_id
3389 , paip.clm_info_flag
3390 , paip.clm_option_indicator
3391 , paip.clm_option_num
3392 , paip.clm_option_from_date
3393 , paip.clm_option_to_date
3394 , paip.clm_funded_flag
3395 , paip.clm_base_line_num
3396
3397 -- Complex Pricing Changes
3398 , paip.CLM_CONTRACT_TYPE
3399 , paip.CLM_COST_CONSTRAINT
3400 , paip.CLM_IDC_TYPE
3401 --CLM Order Off IDV Project
3402 , pbh.idv_header_id
3403 , pbip.idv_line_id
3404 , paip.exhibit_number --ELINs project
3405 FROM pon_auction_item_prices_all paip,
3406 pon_bid_item_prices pbip,
3407 mtl_units_of_measure mtluom,
3408 pon_bid_headers pbh,
3409 fnd_currencies fc,
3410 pon_bid_shipments pbs
3411 WHERE pbip.bid_number = p_bid_number and
3412 pbip.auction_header_id = p_auction_header_id and
3413 nvl(pbip.award_status, 'NO') = 'AWARDED' and
3414 paip.auction_header_id = pbip.auction_header_id and
3415 paip.line_number = pbip.line_number and
3416 paip.group_type NOT IN ('GROUP','LOT_LINE') and
3417 paip.uom_code = mtluom.uom_code (+) and
3418 pbh.bid_number = pbip.bid_number and
3419 fc.currency_code = pbh.bid_currency_code and
3420 pbip.line_number >= l_batch_start and
3421 pbip.line_number <= l_batch_end and
3422 pbs.bid_number(+) = pbip.bid_number and
3423 pbs.line_number(+) = pbip.line_number and
3424 pbs.shipment_number(+) = pbip.award_shipment_number;
3425
3426 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
3427
3428
3429 -- clm clin/slin changes
3430 IF(Nvl(l_is_fed,'N') = 'Y') THEN
3431
3432 log_message('blanket inserting info lines: l_rows_processed' || l_rows_processed );
3433
3434
3435
3436 UPDATE po_lines_interface PLI1
3437 SET PLI1.group_line_id = (select PLI2.interface_line_id from po_lines_interface PLI2
3438 where PLI2.interface_header_id = x_interface_header_id
3439 and PLI2.auction_header_id = p_auction_header_id
3440 and PLI2.auction_line_number = PLI1.group_line_id
3441 AND PLI2.group_line_id IS NULL
3442 AND ROWNUM < 2)
3443 where PLI1.group_line_id is not null
3444 and PLI1.interface_header_id = x_interface_header_id
3445 and PLI1.auction_header_id = p_auction_header_id;
3446
3447 UPDATE po_lines_interface PLI1
3448 SET PLI1.clm_base_line_num = (select PLI2.interface_line_id from po_lines_interface PLI2
3449 where PLI2.interface_header_id = x_interface_header_id
3450 and PLI2.auction_header_id = p_auction_header_id
3451 and PLI2.auction_line_number = PLI1.clm_base_line_num
3452 AND PLI2.clm_base_line_num IS NULL
3453 AND ROWNUM < 2)
3454 where PLI1.clm_base_line_num is not null
3455 and PLI1.interface_header_id = x_interface_header_id
3456 and PLI1.auction_header_id = p_auction_header_id;
3457
3458
3459
3460 END IF; -- if l_is_fed = 'Y'
3461 -- end of clm clin/slin changes
3462
3463
3464
3465 log_message('Inserting iP Descriptors for lines: ' || l_batch_start || ' to ' || l_batch_end);
3466
3467
3468 INSERT_IP_DESCRIPTORS(p_auction_header_id, p_bid_number, x_interface_header_id, p_user_id, fnd_global.login_id, l_batch_start, l_batch_end);
3469
3470 log_message('inserting blanket price break information');
3471
3472 -- Insert Price Break information
3473 INSERT INTO po_lines_interface (
3474 interface_header_id,
3475 interface_line_id,
3476 shipment_type,
3477 line_type_id,
3478 item_id,
3479 item_revision,
3480 quantity,
3481 price_break_lookup_code,
3482 unit_price,
3483 price_discount,
3484 ship_to_organization_id,
3485 ship_to_location_id,
3486 last_update_date,
3487 last_updated_by,
3488 creation_date,
3489 created_by,
3490 line_num,
3491 shipment_num,
3492 effective_date,
3493 expiration_date,
3494 auction_header_id,
3495 auction_line_number,
3496 -- Clin Slin Changes
3497 line_num_display,
3498 group_line_id,
3499 clm_info_flag,
3500 clm_option_indicator,
3501 clm_option_num,
3502 clm_option_from_date,
3503 clm_option_to_date,
3504 clm_funded_flag,
3505 clm_base_line_num,
3506 -- Complex Pricing Changes
3507 CONTRACT_TYPE,
3508 COST_CONSTRAINT,
3509 CLM_IDC_TYPE
3510
3511 )
3512 SELECT
3513 x_interface_header_id, -- interface_header_id
3514 po_lines_interface_s.NEXTVAL, -- interface_line_id
3515 pbs.shipment_type, -- shipment_type
3516 paip.line_type_id, -- line_type_id
3517 paip.item_id, -- item_id
3518 paip.item_revision, -- item_revision
3519 pbs.quantity, -- quantity
3520 decode(pbip.price_break_type, 'NONE', null, 'NON-CUMULATIVE', 'NON CUMULATIVE', pbip.price_break_type),
3521 -- price_break_type
3522 pbs.bid_currency_unit_price, -- unit_price
3523 pbs.price_discount, -- price_discount
3524 pbs.ship_to_organization_id, -- ship_to_organization_id
3525 pbs.ship_to_location_id, -- ship_to_location_id
3526 sysdate, -- last_update_date
3527 p_user_id, -- last_updated_by
3528 sysdate, -- creation_date
3529 p_user_id, -- created_by
3530 pli.line_num, -- line num
3531 pbs.shipment_number, -- shipment_number
3532 pbs.effective_start_date, -- effective_date
3533 pbs.effective_end_date, -- expiration_date
3534 pbs.auction_header_id, -- auction_header_id
3535 pbs.auction_line_number, -- auction_line_number
3536 -- Clin Slin Changes
3537 paip.line_num_display
3538 , paip.group_line_id
3539 , paip.clm_info_flag
3540 , paip.clm_option_indicator
3541 , paip.clm_option_num
3542 , paip.clm_option_from_date
3543 , paip.clm_option_to_date
3544 , paip.clm_funded_flag
3545 , paip.clm_base_line_num
3546 -- Complex Pricing Changes
3547 , paip.CLM_CONTRACT_TYPE
3548 , paip.CLM_COST_CONSTRAINT
3549 , paip.CLM_IDC_TYPE
3550
3551 FROM pon_auction_item_prices_all paip,
3552 pon_bid_item_prices pbip,
3553 pon_bid_shipments pbs,
3554 po_lines_interface pli
3555 WHERE pbip.bid_number = p_bid_number and
3556 pbip.auction_header_id = p_auction_header_id and
3557 nvl(pbip.award_status, 'NO') = 'AWARDED' and
3558 paip.auction_header_id = pbip.auction_header_id and
3559 paip.line_number = pbip.line_number and
3560 pbs.bid_number = p_bid_number and
3561 pli.interface_header_id = x_interface_header_id and
3562 pli.auction_line_number = paip.line_number and
3563 pli.auction_header_id = paip.auction_header_id and
3564 pbs.shipment_type = 'PRICE BREAK' and
3565 pbip.line_number = pbs.line_number and
3566 pbip.line_number >= l_batch_start and
3567 pbip.line_number <= l_batch_end ;
3568
3569
3570 -- Insert Line Price Differentials
3571 INSERT INTO po_price_diff_interface
3572 (price_diff_interface_id,
3573 price_differential_num,
3574 entity_type,
3575 interface_header_id,
3576 interface_line_id,
3577 price_type,
3578 enabled_flag,
3579 min_multiplier,
3580 max_multiplier,
3581 last_update_date,
3582 last_updated_by,
3583 creation_date,
3584 created_by,
3585 last_update_login)
3586 SELECT
3587 po_price_diff_interface_s.NEXTVAL, -- price_diff_interface_id
3588 ppd.price_differential_number, -- price_differential_num
3589 'BLANKET LINE', -- entity_type
3590 x_interface_header_id, -- interface_line_id
3591 pli.interface_line_id, -- interface_line_id
3592 ppd.price_type, -- price_type
3593 'Y', -- enabled_flag
3594 ppd.multiplier, -- min_multiplier
3595 pbpd.multiplier, -- max_multiplier
3596 sysdate, -- last_update_date
3597 p_user_id, -- last_updated_by
3598 sysdate, -- creation_date,
3599 p_user_id, -- created_by
3600 fnd_global.login_id -- last_update_login
3601 FROM pon_price_differentials ppd,
3602 pon_bid_item_prices pbip,
3603 pon_bid_price_differentials pbpd,
3604 pon_auction_headers_all pah,
3605 po_lines_interface pli
3606 WHERE pbip.bid_number = p_bid_number
3607 AND nvl(pbip.award_status, 'NO') = 'AWARDED'
3608 AND pbip.auction_header_id = ppd.auction_header_id
3609 AND pbip.line_number = ppd.line_number
3610 AND ppd.shipment_number = -1
3611 AND p_bid_number = pbpd.bid_number(+)
3612 AND ppd.line_number = pbpd.line_number(+)
3613 AND ppd.shipment_number = pbpd.shipment_number(+)
3614 AND ppd.price_differential_number = pbpd.price_differential_number(+)
3615 AND pah.auction_header_id = ppd.auction_header_id
3616 AND pli.interface_header_id = x_interface_header_id
3617 AND pli.auction_line_number = ppd.line_number
3618 AND pli.auction_header_id = ppd.auction_header_id
3619 AND pli.shipment_num IS NULL
3620 AND pbip.line_number >= l_batch_start
3621 AND pbip.line_number <= l_batch_end;
3622
3623 -- Insert Price Break Price Differentials
3624 INSERT INTO po_price_diff_interface
3625 (price_diff_interface_id,
3626 price_differential_num,
3627 entity_type,
3628 interface_header_id,
3629 interface_line_id,
3630 price_type,
3631 enabled_flag,
3632 min_multiplier,
3633 max_multiplier,
3634 last_update_date,
3635 last_updated_by,
3636 creation_date,
3637 created_by,
3638 last_update_login)
3639 SELECT
3640 po_price_diff_interface_s.NEXTVAL, -- price_diff_interface_id
3641 ppd.price_differential_number, -- price_differential_num
3642 'PRICE BREAK', -- entity_type
3643 x_interface_header_id, -- interface_header_id
3644 pli.interface_line_id, -- interface_line_id
3645 ppd.price_type, -- price_type
3646 'Y', -- enabled_flag
3647 ppd.multiplier, -- min_multiplier
3648 pbpd.multiplier, -- max_multiplier
3649 sysdate, -- last_update_date
3650 p_user_id, -- last_updated_by
3651 sysdate, -- creation_date,
3652 p_user_id, -- created_by
3653 fnd_global.login_id -- last_update_login
3654 FROM pon_price_differentials ppd,
3655 pon_bid_item_prices pbip,
3656 (select pbpd.bid_number, pbpd.line_number,
3657 pbpd.shipment_number, pbs.auction_shipment_number,
3658 pbpd.price_differential_number, pbpd.price_type,
3659 pbpd.multiplier, pbpd.auction_header_id
3660 from pon_bid_price_differentials pbpd, pon_bid_shipments pbs
3661 where pbs.bid_number = p_bid_number
3662 and pbs.line_number = pbpd.line_number
3663 and pbs.shipment_number = pbpd.shipment_number) pbpd,
3664 pon_bid_shipments pbs,
3665 pon_auction_headers_all pah,
3666 po_lines_interface pli
3667 WHERE pbip.bid_number = p_bid_number
3668 AND nvl(pbip.award_status, 'NO') = 'AWARDED'
3669 AND pbip.bid_number = pbs.bid_number
3670 AND pbip.line_number = pbs.line_number
3671 AND pbs.auction_header_id = ppd.auction_header_id
3672 AND pbs.line_number = ppd.line_number
3673 AND pbs.auction_shipment_number = ppd.shipment_number
3674 AND pah.auction_header_id = ppd.auction_header_id
3675 AND ppd.line_number = pbpd.line_number(+)
3676 AND ppd.shipment_number = pbpd.auction_shipment_number(+)
3677 AND ppd.price_differential_number = pbpd.price_differential_number(+)
3678 AND p_bid_number = pbpd.bid_number(+)
3679 AND pli.interface_header_id = x_interface_header_id
3680 AND pli.auction_line_number = pbs.line_number
3681 AND pli.auction_header_id = pbs.auction_header_id
3682 AND pli.shipment_num = pbs.shipment_number
3683 AND pbip.line_number >= l_batch_start
3684 AND pbip.line_number <= l_batch_end;
3685
3686 /*
3687 -- DEBUG CODE
3688 -- ALWAYS COMMENTED OUT
3689
3690 INSERT INTO po_lines_interface_debug
3691 (SELECT * FROM po_lines_interface WHERE interface_header_id = x_interface_header_id);
3692
3693 INSERT INTO po_price_diff_interface_debug
3694 (SELECT * FROM po_price_diff_interface WHERE interface_header_id = x_interface_header_id);
3695 */
3696
3697 x_progress := '38: CREATE_PO_STRUCTURE: BLANKET CASE: END OF BULK INSERT';
3698
3699 log_message(x_progress);
3700
3701 x_progress := '39: CREATE_PO_STRUCTURE: BLANKET CASE: BATCH FROM '
3702 || l_batch_start ||' TO '|| l_batch_end ||' (inclusive)';
3703
3704 log_message(x_progress);
3705
3706 l_batch_start := l_batch_end + 1;
3707
3708 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
3709 l_batch_end := l_max_line_number;
3710 l_commit_flag := FALSE;
3711 ELSE
3712 l_batch_end := l_batch_end + l_batch_size;
3713 l_commit_flag := TRUE;
3714 END IF;
3715
3716
3717 /*
3718 Note from ATG-WF website :-
3719
3720 You CANNOT commit inside a PL/SQL procedure which is called by the workflow engine.
3721 If you issue a commit you are committing the workflow state as well as your application
3722 state. If you do commit and your pl/sql function fails subsequently the workflow engine
3723 will not be able to rollback to a consistent state.
3724 */
3725
3726 IF(l_commit_flag = TRUE) THEN
3727 COMMIT;
3728 x_progress := '40: CREATE_PO_STRUCTURE: BLANKET CASE: BATCH-COMMIT SUCCESSFUL ';
3729 log_message(x_progress);
3730 END IF;
3731
3732
3733 END LOOP; --} -- end-main-batching-loop
3734 --------------------------------------------------------------------------------------------------------------
3735 --BATCHING FOR OUTCOME = BLANKET PURCHASE AGREEMENT: ENDS HERE
3736 --------------------------------------------------------------------------------------------------------------
3737 /* Unsolicited Lines Project : If unsolicited lines are enabled,
3738 insert into po_lines_interface.
3739 */
3740 log_message('BPA : l_allow_unsol_lines '||l_allow_unsol_lines);
3741 IF l_allow_unsol_lines = 'Y' THEN
3742 log_message('BPA : Before inserting unsolicited lines. ');
3743 PON_UNSOL_CREATE_PO_PKG.insert_unsol_lines_bpa(p_interface_header_id => x_interface_header_id,
3744 p_auction_header_id => p_auction_header_id,
3745 p_bid_number => p_bid_number,
3746 p_document_number => x_pdoi_header.document_number,
3747 p_is_fed => Nvl(l_is_fed,'N'),
3748 p_user_id => p_user_id,
3749 x_rows_processed => l_rows_processed);
3750 log_message('BPA : After inserting unsolicited lines succesfully, l_rows_processed '||l_rows_processed);
3751 END IF;
3752
3753 END IF; --} --if outcome is BPA
3754
3755
3756 /* Bug 9883780 - Start */
3757
3758 IF (x_pdoi_header.contract_type = 'STANDARD') THEN --{
3759
3760 /* The following code is added only for logging purpose. */
3761 IF (g_fnd_debug = 'Y') THEN
3762 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3763 log_message('x_interface_header_id1 : ' ||x_interface_header_id);
3764 log_message('*********************************************************************************');
3765 log_message('Interface Header Id - Interface Line Id - Auction Header Id - Auction Line Number'||
3766 'Group Line Id - Requisition Line Id - Clm Base Line Num - Lne Number');
3767 OPEN log_cursor(x_interface_header_id);
3768 LOOP
3769 FETCH log_cursor into log_interface_header_id,
3770 log_interface_line_id,
3771 log_auction_header_id,
3772 log_auction_line_number,
3773 log_group_line_id,
3774 log_requisition_line_id,
3775 log_clm_base_line_num,
3776 log_line_num_display;
3777 EXIT WHEN log_cursor%NOTFOUND;
3778 log_message(log_interface_header_id||' - '||log_interface_line_id||' - '||log_auction_header_id||' - '||
3779 log_auction_line_number||' - '||log_group_line_id||' - '||log_requisition_line_id||' - '||
3780 log_clm_base_line_num||' - '||log_line_num_display);
3781 END LOOP;
3782 CLOSE log_cursor;
3783 log_message('*********************************************************************************');
3784 END IF;
3785 END IF;
3786
3787 /* The above code is added only for logging purpose. */
3788
3789 IF(Nvl(l_is_fed,'N') = 'Y') THEN
3790
3791
3792 /* Update all the slin numbers's group line id with interface line id of the Corresponding CLIN to maintain
3793 CLIN - SLIN relationships .
3794 The below update query updates the mappings only for records, which were backed up by requisitions or
3795 those records, which were splitted because of excess awarding or those records which were added in the
3796 solicitation in relation with Requisition. */
3797
3798 UPDATE po_lines_interface PLI1
3799 SET PLI1.group_line_id = Nvl( (select PLI2.interface_line_id
3800 from po_lines_interface PLI2
3801 where PLI2.interface_header_id = x_interface_header_id
3802 and PLI2.auction_header_id = p_auction_header_id
3803 and PLI2.auction_line_number = PLI1.group_line_id
3804 AND PLI2.group_line_id IS NULL
3805 AND PLI2.requisition_line_id IS NOT NULL
3806 AND ROWNUM < 2), PLI1.group_line_id)
3807 where PLI1.group_line_id is not null
3808 and PLI1.interface_header_id = x_interface_header_id
3809 and PLI1.auction_header_id = p_auction_header_id;
3810
3811
3812 /* Update all the slin numbers's group line id with interface line id of the Corresponding CLIN to maintain
3813 CLIN - SLIN relationships .
3814 The below update query updates the mappings only for records, which were added in the solicitions
3815 and not updated in the above query*/
3816
3817 UPDATE po_lines_interface PLI1
3818 SET PLI1.group_line_id = (select PLI2.interface_line_id from po_lines_interface PLI2
3819 where PLI2.interface_header_id = x_interface_header_id
3820 and PLI2.auction_header_id = p_auction_header_id
3821 and PLI2.auction_line_number = PLI1.group_line_id
3822 AND PLI2.group_line_id IS NULL
3823 AND ROWNUM < 2)
3824 where PLI1.group_line_id is not null
3825 and PLI1.interface_header_id = x_interface_header_id
3826 and PLI1.auction_header_id = p_auction_header_id
3827 and PLI1.group_line_id <> Nvl ( (SELECT PLI3.interface_line_id
3828 FROM po_lines_interface PLI3
3829 WHERE PLI3.interface_header_id = x_interface_header_id
3830 and PLI3.auction_header_id = p_auction_header_id
3831 AND PLI3.interface_line_id = PLI1.group_line_id), -99999 );
3832
3833 /* Update all the slin numbers's clm base line num id with interface line id of the Corresponding
3834 Base CLIN/SLIN to maintain BASE - OPTION relationships.
3835 The below update query updates the mappings only for records, which were backed up by requisitions or
3836 those records, which were splitted because of excess awarding or those records which were added in the
3837 solicitation in relation with Requisition. */
3838
3839 UPDATE po_lines_interface PLI1
3840 SET PLI1.clm_base_line_num = Nvl( (select PLI2.interface_line_id
3841 from po_lines_interface PLI2
3842 where PLI2.interface_header_id = x_interface_header_id
3843 and PLI2.auction_header_id = p_auction_header_id
3844 and PLI2.auction_line_number = PLI1.clm_base_line_num
3845 AND PLI2.clm_base_line_num IS NULL
3846 AND PLI2.requisition_line_id IS NOT NULL
3847 AND ROWNUM < 2), PLI1.clm_base_line_num)
3848 where PLI1.clm_base_line_num is not null
3849 and PLI1.interface_header_id = x_interface_header_id
3850 and PLI1.auction_header_id = p_auction_header_id;
3851
3852 /* Update all the slin numbers's clm base line num id with interface line id of the Corresponding
3853 Base CLIN/SLIN to maintain BASE - OPTION relationships.
3854 The below update query updates the mappings only for records, which were added in the solicitions
3855 and not updated in the above query*/
3856
3857 UPDATE po_lines_interface PLI1
3858 SET PLI1.clm_base_line_num = (select PLI2.interface_line_id from po_lines_interface PLI2
3859 where PLI2.interface_header_id = x_interface_header_id
3860 and PLI2.auction_header_id = p_auction_header_id
3861 and PLI2.auction_line_number = PLI1.clm_base_line_num
3862 AND PLI2.clm_base_line_num IS NULL
3863 AND ROWNUM < 2)
3864 where PLI1.clm_base_line_num is not null
3865 and PLI1.interface_header_id = x_interface_header_id
3866 and PLI1.auction_header_id = p_auction_header_id
3867 and PLI1.clm_base_line_num <> Nvl ( (SELECT PLI3.interface_line_id
3868 FROM po_lines_interface PLI3
3869 WHERE PLI3.interface_header_id = x_interface_header_id
3870 and PLI3.auction_header_id = p_auction_header_id
3871 AND PLI3.interface_line_id = PLI1.clm_base_line_num), -99999 );
3872
3873 END IF; -- if l_is_fed = 'Y'
3874
3875 /* The following code is added only for logging purpose. */
3876 IF (g_fnd_debug = 'Y') THEN
3877 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3878 log_message('*********************************************************************************');
3879 log_message('Interface Header Id - Interface Line Id - Auction Header Id - Auction Line Number'||
3880 'Group Line Id - Requisition Line Id - Clm Base Line Num - Lne Number');
3881 OPEN log_cursor(x_interface_header_id);
3882 LOOP
3883 FETCH log_cursor into log_interface_header_id,
3884 log_interface_line_id,
3885 log_auction_header_id,
3886 log_auction_line_number,
3887 log_group_line_id,
3888 log_requisition_line_id,
3889 log_clm_base_line_num,
3890 log_line_num_display;
3891 EXIT WHEN log_cursor%NOTFOUND;
3892 log_message(log_interface_header_id||' - '||log_interface_line_id||' - '||log_auction_header_id||' - '||
3893 log_auction_line_number||' - '||log_group_line_id||' - '||log_requisition_line_id||' - '||
3894 log_clm_base_line_num||' - '||log_line_num_display);
3895 END LOOP;
3896 CLOSE log_cursor;
3897 log_message('*********************************************************************************');
3898 END IF;
3899 END IF;
3900
3901 /* The above code is added only for logging purpose. */
3902
3903 /* In case CLM generate CLIN numbers starting from 0001 */
3904
3905 IF(Nvl(l_is_fed,'N') = 'Y') THEN
3906 BEGIN
3907 x_progress := '45: CREATE_PO_STRUCTURE: REGENERATE CLIN NUMBER LOGIC STARTED ';
3908 log_message(x_progress);
3909
3910 FOR upd_row IN update_clin_num_cursor(x_interface_header_id,p_auction_header_id) LOOP
3911 next_clin_num := pon_clo_renumber_pkg.next_clin_num(clin_num_tbl);
3912
3913 log_message('next_clin_num : '||next_clin_num);
3914
3915 UPDATE po_lines_interface
3916 SET line_num_display = next_clin_num
3917 WHERE interface_header_id = upd_row.interface_header_id
3918 AND interface_line_id = upd_row.interface_line_id;
3919
3920 clin_num_tbl.extend();
3921 len := clin_num_tbl.Count ;
3922 clin_num_tbl(len) := next_clin_num;
3923
3924 --pon_clo_renumber_pkg.GenerateSlinStructure('PON',upd_row.interface_header_id,next_clin_num,x_result);
3925 END LOOP;
3926
3927 x_progress := '50: CREATE_PO_STRUCTURE: REGENERATE CLIN NUMBER LOGIC SUCCESSFUL ';
3928 log_message(x_progress);
3929 COMMIT;
3930 EXCEPTION WHEN OTHERS THEN
3931 p_error_code := 'FAILURE';
3932 p_error_message := SUBSTRB(SQLERRM, 1, 500);
3933 log_message('CREATE_PO_STRUCTURE : REGENERATE CLIN NUMBER LOGIC FAILURE : ' || p_error_code || ' ' || p_error_message);
3934 END;
3935
3936
3937 /* In case CLM generate SLIN numbers for each of the CLINs created above based on the mappings. */
3938
3939 BEGIN
3940 x_progress := '55: CREATE_PO_STRUCTURE: REGENERATE SLIN NUMBER LOGIC STARTED ';
3941 log_message(x_progress);
3942
3943 FOR upd_row IN update_slin_num_cursor(x_interface_header_id,p_auction_header_id) LOOP
3944 log_message('interface_header_id : '||upd_row.interface_header_id);
3945 log_message('line_num_display : '||upd_row.line_num_display);
3946 pon_clo_renumber_pkg.GenerateSlinStructure('PON',upd_row.interface_header_id,upd_row.line_num_display,x_result);
3947 END LOOP;
3948
3949 x_progress := '60: CREATE_PO_STRUCTURE: REGENERATE SLIN NUMBER LOGIC SUCCESSFUL ';
3950 log_message(x_progress);
3951 COMMIT;
3952 EXCEPTION WHEN OTHERS THEN
3953 p_error_code := 'FAILURE';
3954 p_error_message := SUBSTRB(SQLERRM, 1, 500);
3955 log_message('CREATE_PO_STRUCTURE : REGENERATE SLIN NUMBER LOGIC FAILURE : ' || p_error_code || ' ' || p_error_message);
3956 END;
3957 END IF;
3958
3959 /* The following code is added only for logging purpose. */
3960 IF (g_fnd_debug = 'Y') THEN
3961 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3962 log_message('*********************************************************************************');
3963 log_message('Interface Header Id - Interface Line Id - Auction Header Id - Auction Line Number'||
3964 'Group Line Id - Requisition Line Id - Clm Base Line Num - Lne Number');
3965 OPEN log_cursor(x_interface_header_id);
3966 LOOP
3967 FETCH log_cursor into log_interface_header_id,
3968 log_interface_line_id,
3969 log_auction_header_id,
3970 log_auction_line_number,
3971 log_group_line_id,
3972 log_requisition_line_id,
3973 log_clm_base_line_num,
3974 log_line_num_display;
3975 EXIT WHEN log_cursor%NOTFOUND;
3976 log_message(log_interface_header_id||' - '||log_interface_line_id||' - '||log_auction_header_id||' - '||
3977 log_auction_line_number||' - '||log_group_line_id||' - '||log_requisition_line_id||' - '||
3978 log_clm_base_line_num||' - '||log_line_num_display);
3979 END LOOP;
3980 CLOSE log_cursor;
3981 log_message('*********************************************************************************');
3982 END IF;
3983 END IF;
3984
3985 /* The above code is added only for logging purpose. */
3986
3987 END IF;
3988 /* Bug 9883780 - End */
3989
3990 /* setting out parameters */
3991
3992 p_interface_header_id := x_interface_header_id;
3993 p_pdoi_header := x_pdoi_header;
3994
3995
3996 EXCEPTION
3997
3998 when others then
3999
4000 IF (headerLevelInfo%ISOPEN) THEN
4001 close headerLevelInfo;
4002 END IF;
4003
4004 IF (reqlineLevelInfo%ISOPEN) THEN
4005 close reqlineLevelInfo;
4006 END IF;
4007
4008 IF (reqBackingBidItem%ISOPEN) THEN
4009 close reqBackingBidItem;
4010 END IF;
4011
4012 IF (sumOfReqAllocQuantities%ISOPEN) THEN
4013 close sumOfReqAllocQuantities;
4014 END IF;
4015
4016 p_error_code := 'FAILURE';
4017 p_error_message := SUBSTRB(SQLERRM, 1, 500);
4018
4019 log_message('CREATE_PO_STRUCTURE : FATAL_ERROR : ' || p_error_code || ' ' || p_error_message);
4020
4021
4022 END CREATE_PO_STRUCTURE;
4023
4024
4025 PROCEDURE INSERT_IP_DESCRIPTORS(p_auction_header_id IN NUMBER,
4026 p_bid_number IN NUMBER,
4027 p_interface_header_id IN NUMBER,
4028 p_user_id IN NUMBER,
4029 p_login_id IN NUMBER,
4030 p_batch_start IN NUMBER,
4031 p_batch_end IN NUMBER) IS
4032
4033
4034 l_cursorName NUMBER;
4035 l_cursorResult NUMBER;
4036
4037 TYPE NUMBER_LIST is TABLE of NUMBER
4038 INDEX BY BINARY_INTEGER;
4039 TYPE VARCHAR_LIST is TABLE of VARCHAR2(32767)
4040 INDEX BY BINARY_INTEGER;
4041
4042
4043 -- holds the values to be inserted into the interface tables
4044 l_numValues NUMBER_LIST; -- holds descriptor values of number type
4045 l_txtValues VARCHAR_LIST; -- holds descriptor values of text type
4046 l_transTxtValues VARCHAR_LIST; -- holds descriptor values of translateable text type
4047
4048 -- empty tables for clearing/resetting above datastructures
4049 l_emptyNumValues NUMBER_LIST;
4050 l_emptyTxtValues VARCHAR_LIST;
4051 l_emptyTransTxtValues VARCHAR_LIST;
4052
4053 -- keeps track of the size of the tables
4054 l_numValuesCount NUMBER;
4055 l_txtValuesCount NUMBER;
4056 l_transTxtValuesCount NUMBER;
4057
4058 l_cur_interface_line_id NUMBER;
4059 l_cur_attr_values_id NUMBER;
4060 l_cur_attr_values_tlp_id NUMBER;
4061 l_cur_item_description pon_auction_item_prices_all.item_description%TYPE;
4062 l_cur_ip_category_id NUMBER;
4063 l_cur_item_id NUMBER;
4064 l_cur_org_id NUMBER;
4065 l_language_code pon_auction_headers_all.language_code%TYPE;
4066
4067 l_po_attr_values_stmt VARCHAR2(32767);
4068 l_po_attr_values_tlp_stmt VARCHAR2(32767);
4069
4070 l_po_attr_values_cols VARCHAR2(32767);
4071 l_po_attr_values_vals VARCHAR2(32767);
4072
4073 l_po_attr_values_tlp_cols VARCHAR2(32767);
4074 l_po_attr_values_tlp_vals VARCHAR2(32767);
4075
4076
4077 CURSOR descriptors IS
4078 SELECT pbip.line_number,
4079 pli.interface_line_id,
4080 paip.item_description,
4081 nvl(paip.ip_category_id, -2) ip_category_id,
4082 nvl(paip.item_id, -2) item_id,
4083 paip.org_id,
4084 decode(icx.type, 0, 'TXT', 1, 'NUM', 2, 'TRANS') datatype,
4085 icx.stored_in_table,
4086 icx.stored_in_column,
4087 pbav.value,
4088 paa.attribute_name
4089 FROM pon_bid_item_prices pbip,
4090 pon_auction_item_prices_all paip,
4091 po_lines_interface pli,
4092 pon_bid_attribute_values pbav,
4093 pon_auction_attributes paa,
4094 icx_cat_agreement_attrs_v icx
4095 WHERE pbip.auction_header_id = p_auction_header_id and
4096 pbip.bid_number = p_bid_number and
4097 nvl(pbip.award_status, 'NO') = 'AWARDED' and
4098 pbip.line_number >= p_batch_start and
4099 pbip.line_number <= p_batch_end and
4100 pbip.auction_header_id = paip.auction_header_id and
4101 pbip.line_number = paip.line_number and
4102 pli.interface_header_id = p_interface_header_id and
4103 pbip.auction_header_id = pli.auction_header_id and
4104 pbip.line_number = pli.auction_line_number and
4105 pbip.auction_header_id = pbav.auction_header_id (+) and
4106 pbip.bid_number = pbav.bid_number (+) and
4107 pbip.line_number = pbav.line_number (+) and
4108 pbav.auction_header_id = paa.auction_header_id (+) and
4109 pbav.line_number = paa.line_number (+) and
4110 pbav.sequence_number = paa.sequence_number (+) and
4111 paa.ip_category_id (+) is not null and
4112 paa.ip_category_id = icx.rt_category_id (+) and
4113 paa.ip_descriptor_id = icx.attribute_id (+) and
4114 icx.language (+) = userenv('LANG')
4115 ORDER BY interface_line_id asc, decode(datatype, 'NUM', 0, 'TXT', 1, 2) asc;
4116
4117 descriptor descriptors%ROWTYPE;
4118
4119 l_num_txt_offset NUMBER := 11;
4120 l_trans_txt_offset NUMBER := 13;
4121
4122 BEGIN
4123
4124 select language_code
4125 into l_language_code
4126 from pon_auction_headers_all
4127 where auction_header_id = p_auction_header_id;
4128
4129
4130 l_cursorName := DBMS_SQL.Open_Cursor;
4131 l_cur_interface_line_id := -9999;
4132
4133 OPEN descriptors;
4134 LOOP
4135
4136 FETCH descriptors INTO descriptor;
4137 IF (descriptors%NOTFOUND OR
4138 descriptor.interface_line_id <> l_cur_interface_line_id) THEN
4139
4140 -- process number and text descriptors
4141 IF (l_cur_interface_line_id <> -9999) THEN
4142
4143 l_po_attr_values_stmt :=
4144 'insert into po_attr_values_interface(' ||
4145 'interface_header_id, ' ||
4146 'interface_line_id, ' ||
4147 'interface_attr_values_id, ' ||
4148 'ip_category_id, ' ||
4149 'inventory_item_id, ' ||
4150 'org_id, ' ||
4151 'last_update_login, ' ||
4152 'last_updated_by, ' ||
4153 'last_update_date, ' ||
4154 'created_by, ' ||
4155 'creation_date' ||
4156 l_po_attr_values_cols ||
4157 ') values('||
4158 ':1, ' ||
4159 ':2, ' ||
4160 ':3, ' ||
4161 ':4, ' ||
4162 ':5, ' ||
4163 ':6, ' ||
4164 ':7, ' ||
4165 ':8, ' ||
4166 ':9, ' ||
4167 ':10, ' ||
4168 ':11' ||
4169 l_po_attr_values_vals ||
4170 ')';
4171
4172 log_message(l_po_attr_values_stmt);
4173
4174 DBMS_SQL.Parse(l_cursorName, l_po_attr_values_stmt, DBMS_SQL.NATIVE);
4175
4176 DBMS_SQL.Bind_Variable(l_cursorName, ':1', p_interface_header_id);
4177 DBMS_SQL.Bind_Variable(l_cursorName, ':2', l_cur_interface_line_id);
4178 DBMS_SQL.Bind_Variable(l_cursorName, ':3', l_cur_attr_values_id);
4179 DBMS_SQL.Bind_Variable(l_cursorName, ':4', l_cur_ip_category_id);
4180 DBMS_SQL.Bind_Variable(l_cursorName, ':5', l_cur_item_id);
4181 DBMS_SQL.Bind_Variable(l_cursorName, ':6', l_cur_org_id);
4182 DBMS_SQL.Bind_Variable(l_cursorName, ':7', p_login_id);
4183 DBMS_SQL.Bind_Variable(l_cursorName, ':8', p_user_id);
4184 DBMS_SQL.Bind_Variable(l_cursorName, ':9', sysdate);
4185 DBMS_SQL.Bind_Variable(l_cursorName, ':10', p_user_id);
4186 DBMS_SQL.Bind_Variable(l_cursorName, ':11', sysdate);
4187
4188 FOR i in 1 .. l_numValuesCount
4189 LOOP
4190 DBMS_SQL.Bind_Variable(l_cursorName, ':' || (i+l_num_txt_offset), l_numValues(i));
4191 END LOOP;
4192
4193 FOR i in 1 ..l_txtValuesCount
4194 LOOP
4195 DBMS_SQL.Bind_Variable(l_cursorName, ':' || (i+l_num_txt_offset+l_numValuesCount), l_txtValues(i));
4196 END LOOP;
4197
4198 l_cursorResult := DBMS_SQL.Execute(l_cursorName);
4199
4200 END IF;
4201
4202 -- process translateable text descriptors
4203 IF (l_cur_interface_line_id <> -9999) THEN
4204
4205 l_po_attr_values_tlp_stmt :=
4206 'insert into po_attr_values_tlp_interface(' ||
4207 'interface_header_id, ' ||
4208 'interface_line_id, ' ||
4209 'interface_attr_values_tlp_id, ' ||
4210 'ip_category_id, ' ||
4211 'inventory_item_id, ' ||
4212 'org_id, ' ||
4213 'language, ' ||
4214 'description, ' ||
4215 'long_description, ' ||
4216 'last_update_login, ' ||
4217 'last_updated_by, ' ||
4218 'last_update_date, ' ||
4219 'created_by, ' ||
4220 'creation_date' ||
4221 l_po_attr_values_tlp_cols ||
4222 ') values('||
4223 ':1, ' ||
4224 ':2, ' ||
4225 ':3, ' ||
4226 ':4, ' ||
4227 ':5, ' ||
4228 ':6, ' ||
4229 ':7, ' ||
4230 ':8, ' ||
4231 ':9, ' ||
4232 ':10, ' ||
4233 ':11, ' ||
4234 ':12, ' ||
4235 ':13, ' ||
4236 ':14' ||
4237 l_po_attr_values_tlp_vals ||
4238 ')';
4239
4240 log_message(l_po_attr_values_tlp_stmt);
4241
4242 DBMS_SQL.Parse(l_cursorName, l_po_attr_values_tlp_stmt, DBMS_SQL.NATIVE);
4243
4244 DBMS_SQL.Bind_Variable(l_cursorName, ':1', p_interface_header_id);
4245 DBMS_SQL.Bind_Variable(l_cursorName, ':2', l_cur_interface_line_id);
4246 DBMS_SQL.Bind_Variable(l_cursorName, ':3', l_cur_attr_values_tlp_id);
4247 DBMS_SQL.Bind_Variable(l_cursorName, ':4', l_cur_ip_category_id);
4248 DBMS_SQL.Bind_Variable(l_cursorName, ':5', l_cur_item_id);
4249 DBMS_SQL.Bind_Variable(l_cursorName, ':6', l_cur_org_id);
4250 DBMS_SQL.Bind_Variable(l_cursorName, ':7', l_language_code);
4251 DBMS_SQL.Bind_Variable(l_cursorName, ':8', SubStrB(l_cur_item_description,1,240));
4252 DBMS_SQL.Bind_Variable(l_cursorName, ':9', SubStrB(l_cur_item_description,1,2000));
4253 DBMS_SQL.Bind_Variable(l_cursorName, ':10', p_login_id);
4254 DBMS_SQL.Bind_Variable(l_cursorName, ':11', p_user_id);
4255 DBMS_SQL.Bind_Variable(l_cursorName, ':12', sysdate);
4256 DBMS_SQL.Bind_Variable(l_cursorName, ':13', p_user_id);
4257 DBMS_SQL.Bind_Variable(l_cursorName, ':14', sysdate);
4258
4259 FOR i in 1 .. l_transTxtValuesCount
4260 LOOP
4261 DBMS_SQL.Bind_Variable(l_cursorName, ':' || (i+l_trans_txt_offset), l_transTxtValues(i));
4262 END LOOP;
4263
4264 l_cursorResult := DBMS_SQL.Execute(l_cursorName);
4265
4266 END IF;
4267
4268 EXIT WHEN descriptors%NOTFOUND;
4269
4270 -- initialize/reset variables on line change
4271
4272 l_cur_interface_line_id := descriptor.interface_line_id;
4273
4274 select po_attr_values_interface_s.nextval
4275 into l_cur_attr_values_id
4276 from dual;
4277
4278 select po_attr_values_tlp_interface_s.nextval
4279 into l_cur_attr_values_tlp_id
4280 from dual;
4281
4282 l_cur_item_description := descriptor.item_description;
4283 l_cur_ip_category_id := descriptor.ip_category_id;
4284 l_cur_item_id := descriptor.item_id;
4285 l_cur_org_id := descriptor.org_id;
4286
4287 l_po_attr_values_cols := '';
4288 l_po_attr_values_vals := '';
4289
4290 l_po_attr_values_tlp_cols := '';
4291 l_po_attr_values_tlp_vals := '';
4292
4293 l_numValues := l_emptyNumValues;
4294 l_txtValues := l_emptyTxtValues;
4295 l_transTxtValues := l_emptyTxtValues;
4296
4297 l_numValuesCount := 0;
4298 l_txtValuesCount := 0;
4299 l_transTxtValuesCount := 0;
4300
4301
4302 END IF;
4303
4304
4305 CASE descriptor.datatype
4306 WHEN 'NUM' THEN
4307 l_numValuesCount := l_numValuesCount + 1;
4308 l_numValues(l_numValuesCount) := to_number(descriptor.value);
4309 l_po_attr_values_cols := l_po_attr_values_cols || ', ' || descriptor.stored_in_column;
4310 l_po_attr_values_vals := l_po_attr_values_vals || ', ' || ':' || to_char(l_numValuesCount + l_num_txt_offset);
4311
4312 WHEN 'TXT' THEN
4313 l_txtValuesCount := l_txtValuesCount + 1;
4314 l_txtValues(l_txtValuesCount) := descriptor.value;
4315 l_po_attr_values_cols := l_po_attr_values_cols || ', ' || descriptor.stored_in_column;
4316 l_po_attr_values_vals := l_po_attr_values_vals || ', ' || ':' || to_char(l_txtValuesCount + l_num_txt_offset + l_numValuesCount);
4317
4318 WHEN 'TRANS' THEN
4319 l_transTxtValuesCount := l_transTxtValuesCount + 1;
4320 l_transTxtValues(l_transTxtValuesCount) := descriptor.value;
4321 l_po_attr_values_tlp_cols := l_po_attr_values_tlp_cols || ', ' || descriptor.stored_in_column;
4322 l_po_attr_values_tlp_vals := l_po_attr_values_tlp_vals || ', ' || ':' || to_char(l_transTxtValuesCount + l_trans_txt_offset);
4323 ELSE
4324 NULL;
4325 END CASE;
4326
4327
4328 END LOOP;
4329 CLOSE descriptors;
4330
4331 IF DBMS_SQL.IS_OPEN(l_cursorName) THEN
4332 DBMS_SQL.CLOSE_CURSOR(l_cursorName);
4333 END IF;
4334
4335
4336 END INSERT_IP_DESCRIPTORS;
4337
4338
4339 procedure GENERATE_POS(p_auction_header_id IN NUMBER, -- 1
4340 p_user_name IN VARCHAR2, -- 2
4341 p_user_id IN NUMBER, -- 3
4342 p_resultout OUT NOCOPY VARCHAR2) IS
4343
4344
4345
4346 --x_auction_header_id NUMBER;
4347 --x_user_name fnd_user.user_name%TYPE;
4348
4349 x_language_code VARCHAR2(4);
4350 x_round_number NUMBER;
4351 x_line_number NUMBER;
4352 x_bid_number NUMBER;
4353 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4354 x_po_header_id NUMBER;
4355 x_order_number pon_bid_headers.order_number%TYPE;
4356 x_interface_header_id NUMBER;
4357 x_last_update_date pon_auction_headers_all.last_update_date%TYPE;
4358 x_auction_start_date DATE;
4359 x_auction_end_date DATE;
4360 x_tp_time_zone VARCHAR2(80);
4361 x_tp_time_zone1 VARCHAR2(80);
4362 x_award_summary_url VARCHAR2(2000);
4363 x_alloc_summary_url VARCHAR2(2000);
4364 x_allocate_item_url VARCHAR2(2000);
4365 x_auction_org_name VARCHAR2(80);
4366
4367 x_doctype_id NUMBER;
4368 x_conterms_exist_flag pon_auction_headers_all.conterms_exist_flag%TYPE;
4369 x_contract_doc_name VARCHAR(30);
4370
4371 /* Error code can take on the following values:
4372
4373 1- Success
4374 2- Failure due to manual numbering (duplicates)
4375 3- System/Other Errors
4376 4- Auction Error (CREATE_PO_STRUCTURE throws an exception)
4377
4378 */
4379
4380 x_error_code NUMBER;
4381 x_return_status VARCHAR2(1);
4382 x_msg_count NUMBER;
4383 x_msg_data VARCHAR2(2000);
4384 x_failure_code varchar2(10);
4385 x_error_msg varchar2(1000);
4386 x_num_lines_processed NUMBER;
4387 x_pdoi_header PDOIheader;
4388 v_old_policy varchar2(1);
4389 v_old_org_id number;
4390 x_preview_date_notspec VARCHAR2(60);
4391 x_preview_date DATE;
4392
4393 /* returns all awarded bids where a PO was not created*/
4394
4395 /* rrkulkar-large-auction-support :
4396
4397 Changed this cursor to return active bids only,
4398
4399 Modified the old 'where' clause which was like this :-
4400
4401 .... and nvl(pbh.bid_status, 'NONE') not in ('ARCHIVED', 'DISQUALIFIED') and ...
4402 */
4403
4404 CURSOR awardedBids IS
4405 SELECT pbh.bid_number
4406 FROM pon_bid_headers pbh
4407 WHERE pbh.auction_header_id = p_auction_header_id and
4408 nvl(pbh.bid_status, 'NONE') = 'ACTIVE' and
4409 --pbh.po_header_id is NULL and
4410 nvl(pbh.award_status, 'NO') IN ('AWARDED', 'PARTIAL') and
4411 exists (select 1 from pon_bid_item_prices pbip
4412 where pbip.bid_number = pbh.bid_number
4413 and nvl(pbip.award_status,'NO') = 'AWARDED'
4414 and pbip.order_number is null)
4415 GROUP BY pbh.bid_number;
4416
4417 BEGIN
4418
4419 x_progress := '10: GENERATE_POS: Start of PO Creation Script';
4420
4421 log_message(x_progress);
4422
4423 -- initialize to success (3 possible values S=Success; F=Failure; W=Warning)
4424 p_resultout := 'S';
4425
4426 select open_bidding_date,
4427 close_bidding_date,
4428 view_by_date
4429 into x_auction_start_date,
4430 x_auction_end_date,
4431 x_preview_date
4432 from pon_auction_headers_all
4433 where auction_header_id = p_auction_header_id;
4434
4435 /* Lock auction table to prevent concurrency errors */
4436 /* added doctype_id, conterms_exist_flag for contract terms */
4437
4438 SELECT last_update_date, doctype_id, conterms_exist_flag
4439 INTO x_last_update_date, x_doctype_id, x_conterms_exist_flag
4440 FROM pon_auction_headers_all
4441 WHERE auction_header_id = p_auction_header_id
4442 FOR UPDATE;
4443
4444 x_contract_doc_name := PON_CONTERMS_UTL_PVT.get_response_doc_type(x_doctype_id);
4445
4446
4447 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(p_user_name, x_language_code);
4448
4449 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
4450 OPEN awardedBids;
4451 x_progress := '40: GENERATE_POS: Going through the awarded bids';
4452
4453 log_message(x_progress);
4454
4455 LOOP
4456 /* for each active bid where a PO was not created */
4457 FETCH awardedBids into x_bid_number;
4458 EXIT WHEN awardedBids%NOTFOUND;
4459 x_error_code := PO_SUCCESS;
4460 x_progress := '50: GENERATE_POS: Just Before CREATE_PO_STRUCTURE: ' ||
4461 'Bid Number: ' || x_bid_number;
4462
4463 log_message(x_progress);
4464
4465 /* Establish a savepoint */
4466
4467 --savepoint PON_CREATE_PO_DOCUMENTS;
4468
4469 /* This call will create the award purchase order structure
4470 in PDOI */
4471
4472 log_message('2.1 Invoke CREATE_PO_STRUCTURE for auction ' || p_auction_header_id || ' and bid ' || x_bid_number || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4473
4474 CREATE_PO_STRUCTURE(p_auction_header_id,
4475 x_bid_number,
4476 p_user_id,
4477 x_interface_header_id,
4478 x_pdoi_header,
4479 x_failure_code,
4480 x_error_msg);
4481
4482 log_message('2.2. CREATE_PO_STRUCTURE completed for auction ' || p_auction_header_id || ' and bid ' || x_bid_number || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4483
4484 log_message('2.2.1 MAINTAIN_CLM_RELATIONS for auction ' || p_auction_header_id || ' and bid ' || x_bid_number || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4485
4486 MAINTAIN_CLM_RELATIONS(x_interface_header_id);
4487 log_message('2.2.1 MAINTAIN_CLM_RELATIONS completed for auction ' || p_auction_header_id || ' and bid ' || x_bid_number || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4488
4489
4490 IF (x_failure_code = 'FAILURE') THEN
4491
4492 log_message( substrb(x_progress || SQLERRM, 1, 4000));
4493
4494 x_error_code := SOURCING_SYSTEM_ERROR;
4495
4496 END IF;
4497
4498
4499 x_order_number := x_pdoi_header.order_number;
4500
4501 /* call the PO's PL/SQL program to create the Purchase
4502 from the new rows in PDOI */
4503
4504 IF (x_error_code = PO_SUCCESS) THEN
4505
4506 x_progress := '60: GENERATE_POS: Just before creating document';
4507
4508 log_message(x_progress);
4509
4510 -- Get the current policy
4511 v_old_policy := mo_global.get_access_mode();
4512 v_old_org_id := mo_global.get_current_org_id();
4513
4514 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
4515 fnd_log.string(
4516 fnd_log.level_statement,
4517 g_module || '.check_unique_order_number',
4518 'old_policy = ' || v_old_policy || ', old_org_id = ' || v_old_org_id);
4519 end if;
4520
4521 -- Set the connection's policy context
4522 mo_global.set_policy_context('S', x_pdoi_header.org_id);
4523
4524 --create BPA/CPA/SPO
4525 IF x_pdoi_header.contract_type = 'CONTRACT' THEN
4526 x_progress := '65: GENERATE_POS: Just before create_CPA interface id '||x_interface_header_id;
4527 log_message(x_progress);
4528 x_progress := '65.1: GENERATE_POS: Just before create_CPA auction header id '||p_auction_header_id;
4529 log_message(x_progress);
4530 x_progress := '65.2: GENERATE_POS: Just before create_CPA bid number '||x_bid_number;
4531 log_message(x_progress);
4532 x_progress := '65.3: GENERATE_POS: Just before create_CPA conterms flag '||x_conterms_exist_flag;
4533 log_message(x_progress);
4534 x_progress := '65.4: GENERATE_POS: Just before create_CPA conterms doc type '||x_contract_doc_name;
4535 log_message(x_progress);
4536
4537
4538 PO_SOURCING_GRP.CREATE_CPA(
4539 p_api_version => 1.0,
4540 p_init_msg_list => FND_API.G_TRUE,
4541 p_commit => FND_API.G_FALSE,
4542 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4543 x_msg_count => x_msg_count,
4544 x_msg_data => x_msg_data,
4545 x_return_status => x_return_status,
4546 p_interface_header_id => x_interface_header_id,
4547 p_auction_header_id => p_auction_header_id ,
4548 p_bid_number => x_bid_number ,
4549 p_sourcing_k_doc_type => x_contract_doc_name,
4550 p_conterms_exist_flag => x_conterms_exist_flag,
4551 p_document_creation_method => 'AWARD_SOURCING',
4552 x_document_id => x_po_header_id,
4553 x_document_number => x_order_number
4554 );
4555 x_progress := '66: GENERATE_POS: Just after create_cpa status:'||x_return_status;
4556
4557
4558 log_message(x_progress);
4559 x_progress := '66.1: GENERATE_POS: Just after create_CPA order number '||x_order_number;
4560 log_message(x_progress);
4561 x_progress := '66.2: GENERATE_POS: Just after create_CPA po header id '||x_po_header_id;
4562 log_message(x_progress);
4563
4564 IF (x_return_status = FND_API.g_ret_sts_success
4565 AND x_order_number is NOT NULL) THEN
4566 x_error_code := PO_SUCCESS;
4567 -- In case of federal : Populate clm_document_number column to order_number
4568 IF PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(x_doctype_id) = 1 THEN
4569 select clm_document_number into x_order_number
4570 from po_headers_all
4571 where
4572 po_header_id = x_po_header_id;
4573 END IF;
4574 ELSIF (x_return_status = FND_API.g_ret_sts_error
4575 OR x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4576 x_error_code := PO_PDOI_ERROR;
4577 IF x_msg_count = 1 THEN
4578 x_error_msg := x_msg_data;
4579 ELSIF (x_msg_count > 0) THEN
4580 x_error_msg := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
4581 END IF;-- msg_count
4582
4583 x_progress := substrb('67: GENERATE_POS: create cpa error:'||x_error_msg, 1,4000);
4584
4585 log_message(x_progress);
4586
4587
4588 END IF;-- return status
4589 -- delete the interface record irrespective of whether success or failure
4590
4591 x_progress := '68: GENERATE_POS: Before call to Delete interface header id '||x_interface_header_id;
4592
4593 log_message(x_progress);
4594
4595 /* PO_SOURCING_GRP.DELETE_INTERFACE_HEADER(
4596 p_api_version => 1.0,
4597 p_init_msg_list => FND_API.G_FALSE,
4598 p_commit => FND_API.G_FALSE,
4599 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4600 x_msg_count => x_msg_count,
4601 x_msg_data => x_msg_data,
4602 x_return_status => x_return_status,
4603 p_interface_header_id => x_interface_header_id
4604 );
4605 */
4606 x_progress := '68.1: GENERATE_POS: Just after call to Delete interface header status:'||x_return_status;
4607
4608 log_message(x_progress);
4609
4610 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
4611 x_error_code := PO_DELETE_ERROR;
4612 IF x_msg_count = 1 THEN
4613 x_error_msg := x_msg_data;
4614 ELSIF (x_msg_count > 0) THEN
4615 x_error_msg := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
4616 END IF;-- msg_count
4617 x_progress := substrb('68.2: GENERATE_POS: delete interface header error:'||x_error_msg, 1,4000);
4618
4619 log_message(x_progress);
4620
4621 END IF;-- x_return_status
4622 ELSE -- else if contracttype is bpa or spo
4623
4624 x_progress := '69: GENERATE_POS: Just before create_documents with parameters : x_interface_header_id ='
4625 || x_interface_header_id || ' org_id =' || x_pdoi_header.org_id || ' x_po_header_id ='
4626 || x_po_header_id || ' x_num_lines_processed =' || x_num_lines_processed
4627 || ' x_contract_doc_name =' || x_contract_doc_name || ' x_conterms_exist_flag = '
4628 || x_conterms_exist_flag;
4629
4630 log_message(x_progress);
4631
4632
4633 log_message('2.3 Invoke PO_INTERFACE_S.CREATE_DOCUMENTS for auction ' || p_auction_header_id || ' and bid ' || x_bid_number || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4634
4635 po_interface_s.create_documents(P_API_VERSION => 1.0,
4636 X_RETURN_STATUS => x_return_status,
4637 X_MSG_COUNT => x_msg_count,
4638 X_MSG_DATA => x_msg_data,
4639 P_BATCH_ID => x_interface_header_id,
4640 P_REQ_OPERATING_UNIT_ID => x_pdoi_header.org_id,
4641 P_PURCH_OPERATING_UNIT_ID => x_pdoi_header.org_id,
4642 X_DOCUMENT_ID => x_po_header_id,
4643 X_NUMBER_LINES => x_num_lines_processed,
4644 X_DOCUMENT_NUMBER => x_order_number,
4645 P_SOURCING_K_DOC_TYPE => x_contract_doc_name,
4646 P_CONTERMS_EXIST_FLAG => x_conterms_exist_flag,
4647 P_DOCUMENT_CREATION_METHOD => 'AWARD_SOURCING');
4648
4649 log_message('2.4. Completed PO_INTERFACE_S.CREATE_DOCUMENTS for auction ' || p_auction_header_id || ' and bid ' || x_bid_number || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4650
4651 --
4652 -- Derive x_error_code based on x_return_status
4653 --
4654 log_message('70.GENERATE_POS: Just after create_documents: x_return_status=' || x_return_status || ' x_num_lines_processed=' || x_num_lines_processed || ' x_order_number=' || x_order_number);
4655
4656
4657
4658 IF (x_return_status = FND_API.g_ret_sts_success
4659 AND x_num_lines_processed >0 AND x_order_number is NOT NULL)
4660 THEN
4661 x_error_code := PO_SUCCESS;
4662 -- In case of federal : Populate clm_document_number column to order_number
4663 IF PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(x_doctype_id) = 1 THEN
4664 select clm_document_number into x_order_number
4665 from po_headers_all
4666 where
4667 po_header_id = x_po_header_id;
4668 END IF;
4669 ELSIF (x_return_status = PO_INTERFACE_S.G_RET_STS_DUP_DOC_NUM) THEN
4670 x_error_code := DUPLICATE_PO_NUMBER;
4671 ELSE
4672 x_error_code := PO_SYSTEM_ERROR;
4673 END IF;
4674
4675
4676 log_message('after create_documents: x_error_code=' || x_error_code);
4677
4678 IF (x_error_code <> PO_SUCCESS) THEN
4679 null;
4680 --rollback to savepoint PON_CREATE_PO_DOCUMENTS;
4681 END IF;
4682
4683 END IF;--END if contractType is CONTRACT
4684
4685 -- Set the policy context back
4686 mo_global.set_policy_context(v_old_policy,v_old_org_id);
4687
4688 END IF;
4689
4690 IF (x_error_code = PO_SUCCESS) THEN
4691 x_pdoi_header.order_number := x_order_number;
4692 END IF;
4693
4694
4695 UPDATE pon_bid_headers
4696 SET po_header_id = decode(x_error_code, PO_SUCCESS, x_po_header_id, null),
4697 order_number = x_order_number,
4698 po_error_code = x_error_code,
4699 po_error_msg = x_error_msg,
4700 po_wf_creation_rnd = decode(x_error_code, PO_SUCCESS, x_round_number, po_wf_creation_rnd)
4701 where auction_header_id = p_auction_header_id and
4702 bid_number = x_bid_number;
4703
4704 --Added for Staggered Awards, update order_number for each bid line
4705 UPDATE pon_bid_item_prices
4706 SET po_header_id = decode(x_error_code, PO_SUCCESS, x_po_header_id, null),
4707 order_number = x_order_number,
4708 po_error_code = x_error_code,
4709 po_error_msg = x_error_msg,
4710 po_wf_creation_rnd = decode(x_error_code, PO_SUCCESS, x_round_number, po_wf_creation_rnd),
4711 initiate_approval = x_pdoi_header.initiate_approval
4712 WHERE auction_header_id = p_auction_header_id and
4713 bid_number = x_bid_number AND
4714 Nvl(order_number, '-1') = -1 AND
4715 award_status = 'AWARDED';
4716
4717
4718 x_progress := '70: GENERATE_POS: After po creation: ' ||
4719 'Bid Number: ' || x_bid_number || ', ' ||
4720 'PO Header ID: ' || x_po_header_id || ', ' ||
4721 'PO Order Number: ' || x_order_number || ', ' ||
4722 'Error Code: ' || x_error_code || ', ' ||
4723 'Round Number: ' || x_round_number || ', '||
4724 'Return Status: '|| x_return_status || ', '||
4725 'Message Count: '|| x_msg_count || ', '||
4726 'Message Data: '|| x_msg_data;
4727
4728 log_message(x_progress);
4729 --ORCA Integration
4730 IF PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(x_doctype_id) = 1 THEN
4731 INSERT
4732 INTO pon_orca_cert_details (
4733 ORCA_CERT_DETAILS_ID,
4734 VENDOR_ID,
4735 VENDOR_SITE_ID,
4736 DUNS_NUMBER,
4737 RESPONSE_CODE,
4738 RESPONSE_MESSAGE,
4739 TIME_RECEIVED,
4740 REG_STATUS_CODE,
4741 VALID_FROM,
4742 VALID_TO,
4743 XML_ORCA_RECORD,
4744 DOCUMENT_TYPE,
4745 DOCUMENT_NUMBER,
4746 CREATED_BY,
4747 ATTACH_PRI_KEY,
4748 CREATION_DATE,
4749 LAST_UPDATED_BY,
4750 LAST_UPDATE_DATE,
4751 LAST_UPDATE_LOGIN
4752 )
4753 SELECT PON_ORCA_CERT_DETAIL_S.NEXTVAL,
4754 pocd.vendor_id,
4755 pocd.vendor_site_id,
4756 pocd.duns_number,
4757 pocd.response_code,
4758 pocd.response_message,
4759 pocd.time_received,
4760 pocd.reg_status_code,
4761 pocd.valid_from,
4762 pocd.valid_to,
4763 pocd.xml_orca_record,
4764 'AWARD' document_type,
4765 TO_CHAR(x_po_header_id) document_number,
4766 pocd.created_by,
4767 pocd.attach_pri_key,
4768 sysdate,
4769 fnd_global.user_id,
4770 sysdate,
4771 fnd_global.login_id
4772 FROM pon_orca_cert_details pocd, po_headers_all pha, pon_auction_headers_all paha
4773 WHERE pocd.document_number = paha.document_number
4774 AND paha.auction_header_id = p_auction_header_id
4775 AND pha.po_header_id = x_po_header_id
4776 AND pha.vendor_id = pocd.vendor_id
4777 AND pha.vendor_site_id = pocd.vendor_site_id;
4778 END IF;
4779 --End of ORCA Integration
4780 IF (x_error_code = PO_SUCCESS AND x_pdoi_header.initiate_approval = 'Y') THEN
4781
4782 x_progress := '80: GENERATE_POS: Just before approval wf';
4783 log_message(x_progress);
4784
4785 /* kick off the PO approval worflow process */
4786
4787
4788 BEGIN
4789
4790 log_error('2.5 LAUNCH_PO_APPROVAL for auction ' || p_auction_header_id || ' and bid ' || x_bid_number
4791 || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4792
4793 LAUNCH_PO_APPROVAL(x_po_header_id, x_pdoi_header, p_user_id);
4794
4795 log_error('2.6 Completed LAUNCH_PO_APPROVAL for auction ' || p_auction_header_id || ' and bid '
4796 || x_bid_number || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4797
4798 EXCEPTION
4799
4800 when others then
4801
4802 log_error('2.61 EXCEPTION IN LAUNCH_PO_APPROVAL for auction ' || p_auction_header_id
4803 || ' with progress so far as ' || x_progress);
4804
4805 log_error('2.7 EXCEPTION IN LAUNCH_PO_APPROVAL for auction ' || p_auction_header_id
4806 || ' and bid ' || x_bid_number || ' at '
4807 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4808
4809 p_resultout := 'W';
4810 END;
4811 END IF;
4812
4813 END LOOP;
4814
4815 CLOSE awardedBids;
4816
4817 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
4818
4819 EXCEPTION
4820
4821 when others then
4822
4823 p_resultout := 'F';
4824
4825 log_error(substrb(x_progress || SQLERRM, 1, 4000));
4826
4827 IF (awardedBids%ISOPEN) THEN
4828 close awardedBids;
4829 END IF;
4830
4831 raise;
4832
4833 END GENERATE_POS;
4834
4835
4836
4837 PROCEDURE LAUNCH_PO_APPROVAL (p_po_header_id IN NUMBER,
4838 p_pdoi_header IN PDOIheader,
4839 p_user_id IN NUMBER) IS
4840
4841 x_ItemType varchar2(20) := null;
4842 x_ItemKey varchar2(60) := null;
4843 x_workflow_process varchar2(40) := null;
4844 x_action_orig_from varchar2(30) := null;
4845 x_doc_id number := null;
4846
4847 x_responsibility_id number := null;
4848 x_application_id number := null;
4849 x_preparer_id number := null;
4850 x_doc_type varchar2(25) := null;
4851 x_doc_subtype varchar2(25) := null;
4852 x_seq_for_item_key varchar2(6) := null;
4853 x_doc_type_to_create varchar2(25);
4854 v_old_policy varchar2(1);
4855 v_old_org_id number;
4856 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4857 x_supplier_notif_method po_vendor_sites_all.supplier_notif_method%TYPE := null;
4858 x_print_flag varchar2(1) := 'N';
4859 x_fax_flag varchar2(1) := 'N';
4860 x_email_flag varchar2(1) := 'N';
4861 x_eMail_address po_vendor_sites_all.email_address%TYPE := null;
4862 x_fax_number varchar2(100) := null;
4863 x_po_api_return_status varchar2 (3) := null;
4864 x_msg_count number := NULL;
4865 x_msg_data varchar2(2000):= NULL;
4866 x_document_num po_headers.segment1%type := null;
4867
4868 BEGIN
4869 x_progress := '10: launch_po_approval: Start of Procedure';
4870
4871 log_message(x_progress);
4872
4873 FND_PROFILE.GET('RESP_ID', x_responsibility_id);
4874
4875 FND_PROFILE.GET('RESP_APPL_ID', x_application_id);
4876
4877 fnd_global.APPS_INITIALIZE (p_user_id, x_responsibility_id, x_application_id);
4878
4879 -- Get the current policy
4880 v_old_policy := mo_global.get_access_mode();
4881 v_old_org_id := mo_global.get_current_org_id();
4882
4883 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
4884 fnd_log.string(
4885 fnd_log.level_statement,
4886 g_module || '.check_unique_order_number',
4887 'old_policy = ' || v_old_policy || ', old_org_id = ' || v_old_org_id);
4888 end if;
4889
4890 -- Set the connection's policy context
4891 mo_global.set_policy_context('S', p_pdoi_header.org_id);
4892
4893 x_doc_type_to_create := p_pdoi_header.contract_type;
4894
4895
4896 if (x_doc_type_to_create = 'BLANKET') then
4897 x_doc_type := 'PA';
4898 x_doc_subtype := 'BLANKET';
4899 elsif (x_doc_type_to_create = 'CONTRACT') then
4900 x_doc_type := 'PA';
4901 x_doc_subtype := 'CONTRACT';
4902 else
4903 /* STANDARD */
4904 x_doc_type := 'PO';
4905 x_doc_subtype := 'STANDARD';
4906 end if;
4907
4908 /* Need to get item_type and workflow process from po_document_types.
4909 * They may be different based on the doc/org.
4910 */
4911
4912 select wf_approval_itemtype,
4913 wf_approval_process
4914 into x_ItemType,
4915 x_workflow_process
4916 from po_document_types
4917 where document_type_code = x_doc_type
4918 and document_subtype = x_doc_subtype;
4919
4920 x_progress := '20: launch_po_approval: x_doc_type: ' || x_doc_type || ', ' ||
4921 'x_doc_subtype: ' || x_doc_subtype || ', ' ||
4922 'x_ItemType: ' || x_ItemType || ', ' ||
4923 'x_workflow_process: ' || x_workflow_process;
4924 log_message(x_progress);
4925
4926 /* Get the unique sequence to make sure item key will be unique */
4927
4928 SELECT to_char(PO_WF_ITEMKEY_S.NEXTVAL)
4929 INTO x_seq_for_item_key
4930 FROM dual;
4931
4932 SELECT employee_id
4933 INTO x_preparer_id
4934 FROM fnd_user
4935 WHERE user_id = p_user_id;
4936
4937 x_doc_id:= p_po_header_id;
4938
4939 x_ItemKey := to_char(x_doc_id) || '-' || x_seq_for_item_key;
4940
4941 x_progress := '25: Calling Get_Transmission_Defaults PO API:' ||
4942 'p_api_version: 1.0 , ' ||
4943 'p_int_msg_list: FND_API.G_FALSE, '||
4944 'p_doc_id: ' || x_doc_id || ', ' ||
4945 'p_doc_type: ' || x_doc_type || ', ' ||
4946 'p_doc_subtype: ' || x_doc_subtype || ', ' ||
4947 'p_preparer_id: ' || x_preparer_id;
4948
4949
4950 /* Get supplier's default transmission settings */
4951 PO_VENDOR_SITES_GRP.Get_Transmission_Defaults(
4952 p_api_version => 1.0,
4953 p_init_msg_list => FND_API.G_FALSE,
4954 p_document_id => p_po_header_id,
4955 p_document_type => x_doc_type,
4956 p_document_subtype => x_doc_subtype,
4957 p_preparer_id => x_preparer_id,
4958 x_default_method => x_supplier_notif_method,
4959 x_email_address => x_email_address,
4960 x_fax_number => x_fax_number,
4961 x_document_num => x_document_num,
4962 x_print_flag => x_print_flag,
4963 x_fax_flag => x_fax_flag,
4964 x_email_flag => x_email_flag,
4965 x_return_status => x_po_api_return_status,
4966 x_msg_count => x_msg_count,
4967 x_msg_data => x_msg_data);
4968
4969 if (x_po_api_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4970 x_progress := '27: Failure in : PO_VENDOR_SITES_GRP.Get_Transmission_Defaults : ' || 'x_po_api_return_status : '||x_po_api_return_status ||','||
4971 'x_msg_data : '|| x_msg_data;
4972
4973 log_message(x_progress);
4974
4975 else
4976
4977 x_progress := '30: launch_po_approval: Just before kicking off wf process ' ||
4978 'x_ItemType: ' || x_ItemType || ', ' ||
4979 'x_ItemKey: ' || x_ItemKey || ', ' ||
4980 'x_workflow_process: ' || x_workflow_process || ', ' ||
4981 'x_action_orig_from: ' || x_action_orig_from || ', ' ||
4982 'x_doc_id: ' || x_doc_id || ', ' ||
4983 'x_doc_num: ' || p_pdoi_header.order_number || ', ' ||
4984 'x_preparer_id: ' || x_preparer_id || ', ' ||
4985 'x_doc_type: ' || x_doc_type || ', ' ||
4986 'x_doc_subtype: ' || x_doc_subtype || ', ' ||
4987 'createsourcingrule: ' || p_pdoi_header.create_sourcing_rules || ', ' ||
4988 'releasegenmethod: ' || p_pdoi_header.release_method || ', ' ||
4989 'updatesourcingrule: ' || p_pdoi_header.update_sourcing_rules;
4990
4991
4992
4993
4994 log_message(x_progress);
4995
4996 po_reqapproval_init1.start_wf_process(
4997 x_ItemType,
4998 x_ItemKey,
4999 x_workflow_process,
5000 x_action_orig_from,
5001 x_doc_id,
5002 p_pdoi_header.order_number, -- x_doc_num
5003 x_preparer_id,
5004 x_doc_type,
5005 x_doc_subtype,
5006 null, -- x_submitter_action,
5007 null, -- x_forward_to_id,
5008 null, -- x_forward_from_id,
5009 null, -- x_def_approval_path_id,
5010 null, -- x_note,
5011 x_print_flag, -- x_printflag
5012 x_fax_flag, -- x_faxflag
5013 x_fax_number, -- x_faxnum
5014 x_email_flag, -- x_emailflag
5015 x_email_address, -- x_emailaddress
5016 p_pdoi_header.create_sourcing_rules,
5017 p_pdoi_header.release_method,
5018 p_pdoi_header.update_sourcing_rules
5019 );
5020
5021 -- Set the org context back
5022 mo_global.set_policy_context(v_old_policy, v_old_org_id);
5023
5024 end if;
5025
5026
5027 EXCEPTION
5028 when others then
5029
5030 log_error(substrb(x_progress || SQLERRM, 1, 4000));
5031
5032 raise;
5033
5034 END LAUNCH_PO_APPROVAL;
5035
5036 PROCEDURE CHECK_PO_STATUS(itemtype IN VARCHAR2,
5037 itemkey IN VARCHAR2,
5038 actid IN NUMBER,
5039 uncmode IN VARCHAR2,
5040 resultout OUT NOCOPY VARCHAR2) IS
5041
5042
5043 x_number_of_failed_pos NUMBER;
5044 x_auction_header_id NUMBER;
5045 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5046
5047 BEGIN
5048 IF (UNCMODE = 'CANCEL') THEN
5049 return;
5050 END IF;
5051
5052 x_progress := '10: CHECK_PO_STATUS: Start of po status check';
5053 log_message(x_progress);
5054
5055 x_number_of_failed_pos := 0;
5056
5057 x_auction_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
5058 itemkey => itemkey,
5059 aname => 'AUCTION_ID');
5060
5061
5062 SELECT count(pbh.bid_number)
5063 INTO x_number_of_failed_pos
5064 FROM pon_bid_headers pbh
5065 WHERE pbh.auction_header_id = x_auction_header_id and
5066 nvl(pbh.bid_status, 'NONE') not in ('ARCHIVED', 'DISQUALIFIED') and
5067 pbh.po_header_id is NULL and
5068 nvl(pbh.award_status, 'NO') in ('AWARDED', 'PARTIAL');
5069
5070 x_progress := '20: CHECK_PO_STATUS: Number of Failed POs: ' || x_number_of_failed_pos;
5071 log_message(x_progress);
5072
5073 IF (x_number_of_failed_pos > 0) THEN
5074 resultout := 'N';
5075 -- setting auction outcome status to outcome failed
5076 UPDATE PON_AUCTION_HEADERS_ALL
5077 SET OUTCOME_STATUS = 'OUTCOME_FAILED'
5078 WHERE AUCTION_HEADER_ID = x_auction_header_id;
5079 ELSE
5080 /* update auction outcome status to outcome_completed */
5081 UPDATE PON_AUCTION_HEADERS_ALL
5082 SET OUTCOME_STATUS = 'OUTCOME_COMPLETED'
5083 WHERE AUCTION_HEADER_ID = x_auction_header_id;
5084 resultout := 'Y';
5085
5086 END IF;
5087
5088 x_progress := '30: CHECK_PO_STATUS: resultout: ' || resultout;
5089 log_message(x_progress);
5090
5091
5092 EXCEPTION
5093
5094 when others then
5095 wf_core.context('PON_AUCTION_CREATE_PO_PKG','checkPOStatus', itemtype, itemkey, x_progress, SQLERRM);
5096 log_error(itemtype || ' ' || itemkey || ' ' || substrb(x_progress || SQLERRM, 1, 4000));
5097 raise;
5098
5099 END CHECK_PO_STATUS;
5100
5101
5102 /* document_id will have the form of auction_header_id:round_number:msg_suffix */
5103
5104 PROCEDURE GENERATE_PO_SUCCESS_EMAIL(document_id IN VARCHAR2,
5105 display_type IN VARCHAR2,
5106 document IN OUT NOCOPY VARCHAR2,
5107 document_type IN OUT NOCOPY VARCHAR2) IS
5108
5109 x_language_code VARCHAR2(4);
5110 x_index NUMBER;
5111 x_substr VARCHAR2(4000);
5112 x_auction_header_id NUMBER;
5113 x_round_number NUMBER;
5114 x_msg_suffix VARCHAR2(3) := '';
5115 x_user_name fnd_user.user_name%TYPE;
5116 x_bid_number pon_bid_headers.bid_number%TYPE;
5117 x_vendor_name po_vendors.vendor_name%TYPE;
5118 x_vendor_site_name po_vendor_sites_all.vendor_site_code%TYPE;
5119 x_agent_name per_all_people_f.full_name%TYPE;
5120 x_order_number pon_bid_headers.order_number%TYPE;
5121 po_status varchar2(4000);
5122 msgBid varchar2(2000);
5123 msgSupplier varchar2(2000);
5124 msgSupplierSite varchar2(2000);
5125 msgBuyer varchar2(2000);
5126 msgPO varchar2(2000);
5127 msgPOdetails varchar2(2000);
5128 msgNumCreated varchar2(2000);
5129 newline varchar2(256);
5130 beginBold VARCHAR2(10);
5131 endBold VARCHAR2(10);
5132 x_count NUMBER;
5133 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5134 itemkey pon_auction_headers_all.wf_poncompl_item_key%TYPE;
5135 x_purchase_orders VARCHAR2(30);
5136 x_contract_type pon_auction_headers_all.contract_type%TYPE;
5137
5138 /* Selects all relevant information for the first 10 successful POs of the current round*/
5139
5140 CURSOR successfulPOs is
5141
5142 SELECT *
5143 FROM (
5144 SELECT pbh.bid_number,
5145 pov.vendor_name,
5146 pbh.vendor_site_code,
5147 papf.full_name,
5148 pbh.order_number
5149 FROM pon_bid_headers pbh,
5150 po_vendors pov,
5151 per_all_people_f papf
5152 WHERE pbh.auction_header_id = x_auction_header_id
5153 AND pbh.bid_status = 'ACTIVE'
5154 AND pbh.po_header_id is NOT NULL
5155 AND pbh.po_wf_creation_rnd = x_round_number
5156 AND pbh.vendor_id = pov.vendor_id
5157 AND pbh.agent_id = papf.person_id
5158 AND papf.effective_start_date < sysdate
5159 AND papf.effective_end_date = (select max(papf2.effective_end_date)
5160 from per_all_people_f papf2
5161 where papf2.person_id = pbh.agent_id)
5162 GROUP BY
5163 pbh.bid_number,
5164 pov.vendor_name,
5165 pbh.vendor_site_code,
5166 papf.full_name,
5167 pbh.order_number
5168 )
5169 WHERE rownum <= 10;
5170
5171 BEGIN
5172
5173 x_progress := '10: GENERATE_PO_SUCCESS_EMAIL unique_key: ' || document_id;
5174 log_message('PONCOMPL' || ' ' || x_progress);
5175 x_index := instr(document_id, ':');
5176 x_auction_header_id := substr(document_id, 1, x_index-1);
5177 x_substr := substr(document_id, x_index+1);
5178 x_index := instr(x_substr, ':');
5179 x_round_number := substr(x_substr, 1, x_index-1);
5180 x_substr := substr(x_substr, x_index+1);
5181 x_index := instr(x_substr, ':');
5182 x_msg_suffix := substr(x_substr, 1, x_index-1);
5183 x_user_name := substr(x_substr, x_index+1);
5184
5185 SELECT wf_poncompl_item_key, contract_type
5186 INTO itemkey, x_contract_type
5187 FROM pon_auction_headers_all
5188 WHERE auction_header_id = x_auction_header_id;
5189
5190 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(x_user_name, x_language_code);
5191 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
5192 IF (x_contract_type = 'STANDARD') THEN
5193 -- x_purchase_orders := 'Standard Purchase Order';
5194 x_purchase_orders := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_SPOT');
5195 msgNumCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_SUCC_PO');
5196 msgPOdetails := PON_AUCTION_PKG.getMessage('PON_AUC_WF_PO_DETAILS');
5197 ELSIF (x_contract_type = 'BLANKET') THEN
5198 -- x_purchase_orders := 'Blanket Purchase Agreement';
5199 x_purchase_orders := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_LONG');
5200 msgNumCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_SUCC_BL');
5201 msgPOdetails := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BL_DETAILS');
5202 ELSIF (x_contract_type = 'CONTRACT') THEN
5203 -- x_purchase_orders := 'Contract Purchase Agreement';
5204 x_purchase_orders := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_CNTR');
5205 msgNumCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_SUCC_CPA');
5206 msgPOdetails := PON_AUCTION_PKG.getMessage('PON_AUC_WF_CPA_DETAILS');
5207 END IF;
5208 msgBid := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BID', x_msg_suffix);
5209 msgSupplier := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUPPLIER');
5210 msgSupplierSite := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUPPLIER_SITE');
5211 msgBuyer := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BUYER');
5212 msgPO := PON_AUCTION_PKG.getMessage('PON_AUC_WF_PO', 'null', 'PURCHASE_ORDERS', x_purchase_orders);
5213 IF (display_type = 'text/plain') THEN
5214 document_type := 'text/plain';
5215 newline := fnd_global.newline;
5216 beginBold := '';
5217 endBold := '';
5218 ELSE
5219 document_type := 'text/html';
5220 newline := '<BR>';
5221 beginBold := '<b>';
5222 endBold := '</b>';
5223 END IF;
5224
5225 x_progress := '20: GENERATE_PO_SUCCESS_EMAIL auction id: ' || x_auction_header_id || ', ' || 'round number: ' || x_round_number || ', ' || 'message suffix: ' || x_msg_suffix || ', ' || 'user name: ' || x_user_name;
5226 log_message('PONCOMPL' || ' ' || itemkey || x_progress);
5227 -- end if;
5228 OPEN successfulPOs;
5229 LOOP
5230 FETCH successfulPOs into x_bid_number, x_vendor_name,
5231 x_vendor_site_name, x_agent_name,
5232 x_order_number;
5233 EXIT WHEN successfulPOs%NOTFOUND;
5234 po_status := msgPOdetails || newline ||
5235 msgBid || ' ' || beginBold || x_bid_number || endBold || newline ||
5236 msgSupplier || ' ' || beginBold || x_vendor_name || endBold || newline ||
5237 msgSupplierSite || ' ' || beginBold || x_vendor_site_name || endBold || newline ||
5238 msgPO || ' ' || beginBold || x_order_number || endBold || newline ||
5239 msgBuyer || ' ' || beginBold || x_agent_name || endBold || newline;
5240
5241 x_progress := '30: GENERATE_PO_SUCCESS_EMAIL message: ' || po_status;
5242 log_message('PONCOMPL' || ' ' || itemkey || ' ' ||x_progress);
5243 document := document || po_status || newline;
5244 END LOOP;
5245 x_count := successfulPOs%ROWCOUNT;
5246 CLOSE successfulPOs;
5247 document := msgNumCreated || ' ' || beginBold || x_count || endBold || newline || newline|| document;
5248 x_progress := '40: GENERAGE_PO_SUCCESS_EMAIL final e-mail message: ' || document;
5249 log_message('PONCOMPL' || ' ' || itemkey || ' ' ||x_progress);
5250
5251 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
5252 EXCEPTION
5253
5254 when others then
5255 wf_core.context('PON_AUCTION_CREATE_PO_PKG','generatePOSuccessEmail', x_progress, SQLERRM);
5256 log_error('PONCOMPL' || ' ' || itemkey || ' ' || substrb(x_progress||SQLERRM, 1, 4000));
5257
5258 IF (successfulPOs%ISOPEN) THEN
5259 close successfulPOs;
5260 END IF;
5261
5262 raise;
5263
5264 END GENERATE_PO_SUCCESS_EMAIL;
5265
5266
5267 /* document_id will have the form of auction_header_id:msg_suffix */
5268
5269 PROCEDURE GENERATE_PO_FAILURE_EMAIL(document_id IN VARCHAR2,
5270 display_type IN VARCHAR2,
5271 document IN OUT NOCOPY VARCHAR2,
5272 document_type IN OUT NOCOPY VARCHAR2) IS
5273
5274
5275 x_language_code VARCHAR2(4);
5276 x_index NUMBER;
5277 x_substr VARCHAR2(4000);
5278 x_auction_header_id NUMBER;
5279 x_msg_suffix VARCHAR2(3) := '';
5280 x_user_name fnd_user.user_name%TYPE;
5281 x_bid_number NUMBER;
5282 x_vendor_name po_vendors.vendor_name%TYPE;
5283 x_vendor_site_name po_vendor_sites_all.vendor_site_code%TYPE;
5284 x_agent_name per_all_people_f.full_name%TYPE;
5285 x_order_number pon_bid_headers.order_number%TYPE;
5286 x_error_code pon_bid_headers.po_error_code%TYPE;
5287 po_status varchar2(4000);
5288 msgBid varchar2(2000);
5289 msgSupplier varchar2(2000);
5290 msgSupplierSite varchar2(2000);
5291 msgBuyer varchar2(2000);
5292 msgError varchar2(2000);
5293 msgNumNotCreated varchar2(2000);
5294 msgErrorCode varchar2(2000);
5295 msgPurchaseOrder varchar2(2000);
5296 msgDuplicatePONumber varchar2(2000);
5297 msgSystemError varchar2(2000);
5298 msgPO varchar2(2000);
5299 newline varchar2(256);
5300 beginBold VARCHAR2(10);
5301 endBold VARCHAR2(10);
5302 x_count NUMBER;
5303 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5304 itemkey pon_auction_headers_all.wf_poncompl_item_key%TYPE;
5305 x_purchase_orders VARCHAR2(30);
5306 x_contract_type pon_auction_headers_all.contract_type%TYPE;
5307 x_error_msg pon_bid_headers.po_error_msg%type;
5308
5309 CURSOR failedPOs is
5310 SELECT pbh.bid_number,
5311 pov.vendor_name,
5312 povsa.vendor_site_code,
5313 papf.full_name,
5314 pbh.order_number,
5315 pbh.po_error_code,
5316 pbh.po_error_msg
5317 FROM pon_bid_headers pbh,
5318 po_vendors pov,
5319 po_vendor_sites_all povsa,
5320 per_all_people_f papf
5321 WHERE pbh.auction_header_id = x_auction_header_id
5322 AND pbh.bid_status = 'ACTIVE'
5323 AND nvl(pbh.award_status, 'NO') in ('AWARDED', 'PARTIAL')
5324 AND pbh.po_header_id is NULL
5325 AND pbh.vendor_id = pov.vendor_id
5326 AND pbh.vendor_id = povsa.vendor_id
5327 AND pbh.agent_id = papf.person_id
5328 AND papf.effective_start_date < sysdate
5329 AND papf.effective_end_date = (select max(papf2.effective_end_date)
5330 from per_all_people_f papf2
5331 where papf2.person_id = pbh.agent_id)
5332 GROUP BY
5333 pbh.bid_number,
5334 pov.vendor_name,
5335 povsa.vendor_site_code,
5336 papf.full_name,
5337 pbh.order_number,
5338 pbh.po_error_code,
5339 pbh.po_error_msg;
5340
5341 BEGIN
5342 x_progress := '10: GENERATE_PO_FAILURE_EMAIL unique key: ' || document_id;
5343 log_message('PONCOMPL' || ' ' || x_progress);
5344 x_index := instr(document_id, ':');
5345 x_auction_header_id := substr(document_id, 1, x_index-1);
5346 x_substr := substr(document_id, x_index+1);
5347 x_index := instr(x_substr, ':');
5348 x_msg_suffix := substr(x_substr, 1, x_index-1);
5349 x_user_name := substr(x_substr, x_index+1);
5350
5351
5352 SELECT wf_poncompl_item_key, contract_type
5353 INTO itemkey, x_contract_type
5354 FROM pon_auction_headers_all
5355 WHERE auction_header_id = x_auction_header_id;
5356
5357 x_progress := '20: GENERATE_PO_FAILURE_EMAIL auction id: ' || x_auction_header_id || ', ' || 'message suffix: ' || x_msg_suffix || ', ' || 'user name: ' || x_user_name;
5358
5359 log_message('PONCOMPL' || ' ' || itemkey || ' ' || x_progress);
5360 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(x_user_name, x_language_code);
5361 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
5362
5363
5364 IF (x_contract_type = 'STANDARD') THEN
5365 -- x_purchase_orders := 'Standard Purchase Order';
5366 x_purchase_orders := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_SPOT');
5367 msgNumNotCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_FAIL_PO');
5368 ELSIF (x_contract_type = 'BLANKET') THEN
5369 -- x_purchase_orders := 'Blanket Purchase Agreement';
5370 msgNumNotCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_FAIL_BL');
5371 x_purchase_orders := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_LONG');
5372 ELSIF (x_contract_type = 'CONTRACT') THEN
5373 -- x_purchase_orders := 'Contract Purchase Agreement';
5374 x_purchase_orders := PON_AUCTION_PKG.getMessage('PON_MI_AUCOUTCM_CNTR');
5375 msgNumNotCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_FAIL_CPA');
5376 END IF;
5377
5378 msgBid := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BID', x_msg_suffix);
5379 msgSupplier := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUPPLIER');
5380 msgSupplierSite := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUPPLIER_SITE');
5381 msgBuyer := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BUYER');
5382 msgPurchaseOrder := PON_AUCTION_PKG.getMessage('PON_AUCTS_PAY_PO');
5383 msgDuplicatePONumber := PON_AUCTION_PKG.getMessage('PON_AUC_WF_DUP_PO_NUM');
5384 msgSystemError := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR');
5385 msgPO := PON_AUCTION_PKG.getMessage('PON_AUC_WF_PO', 'null', 'PURCHASE_ORDERS', x_purchase_orders);
5386
5387 IF (display_type = 'text/plain') THEN
5388 document_type := 'text/plain';
5389 newline := fnd_global.newline;
5390 beginBold := '';
5391 endBold := '';
5392 ELSE
5393 document_type := 'text/html';
5394 newline := '<BR>';
5395 beginBold := '<b>';
5396 endBold := '</b>';
5397 END IF;
5398
5399 OPEN failedPOs;
5400 LOOP
5401 FETCH failedPOs into x_bid_number, x_vendor_name,
5402 x_vendor_site_name, x_agent_name,
5403 x_order_number, x_error_code ,x_error_msg;
5404 EXIT WHEN failedPOs%NOTFOUND;
5405
5406 IF (x_error_code = DUPLICATE_PO_NUMBER) THEN
5407 msgErrorCode := msgPurchaseOrder || ' ' || x_order_number || ': ' || msgDuplicatePONumber;
5408
5409 ELSIF (x_error_code = PO_SYSTEM_ERROR OR x_error_code = SOURCING_SYSTEM_ERROR) THEN
5410 msgErrorCode := msgSystemError;
5411 ELSIF (x_error_code = PO_PDOI_ERROR ) THEN
5412 msgErrorCode := msgSystemError||' :'||substrb(x_error_msg,1,1000);
5413
5414 END IF;
5415
5416 po_status := msgBid || ' ' || beginBold || x_bid_number || endBold || newline ||
5417 msgSupplier || ' ' || beginBold || x_vendor_name || endBold || newline ||
5418 msgSupplierSite || ' ' || beginBold || x_vendor_site_name || endBold || newline ||
5419 msgPO || ' ' || beginBold || 'Not Created' || endBold || newline ||
5420 msgBuyer || ' ' || beginBold || x_agent_name || endBold || newline ||
5421 msgError || ' ' || beginBold || msgErrorCode || endBold || newline;
5422
5423
5424 x_progress := '30: GENERATE_PO_FAILURE_EMAIL message: ' || po_status;
5425 log_message('PONCOMPL' || ' ' || itemkey || ' ' || x_progress);
5426 document := document || po_status || newline;
5427 END LOOP;
5428 x_count := failedPOs%ROWCOUNT;
5429 CLOSE failedPOs;
5430
5431 document := msgNumNotCreated || ' ' || beginBold || x_count || endBold || newline || newline|| document;
5432 x_progress := '40: GENERATE_PO_FAILURE_EMAIL final e-mail message: ' || document;
5433
5434 log_message('PONCOMPL' || ' ' || itemkey || ' ' || x_progress);
5435 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
5436
5437 EXCEPTION
5438
5439 when others then
5440 wf_core.context('PON_AUCTION_CREATE_PO_PKG','generatePOFailureEmail', x_progress, SQLERRM);
5441 log_error('PONCOMPL' || ' ' || itemkey || ' ' || substrb(x_progress || SQLERRM, 1, 4000));
5442
5443 IF (failedPOs%ISOPEN) THEN
5444 close failedPOs;
5445 END IF;
5446
5447 raise;
5448
5449 END GENERATE_PO_FAILURE_EMAIL;
5450
5451
5452
5453 procedure CHECK_PO_EMAIL_TYPE (itemtype IN VARCHAR2,
5454 itemkey IN VARCHAR2,
5455 actid IN NUMBER,
5456 uncmode IN VARCHAR2,
5457 resultout OUT NOCOPY VARCHAR2)
5458 IS
5459
5460 BEGIN
5461
5462 -- PON_AUC_PO_ALLOC_REQS_FAIL
5463 -- PON_AUC_PO_ALLOC_SPLIT_FAIL
5464 -- PON_AUC_PO_CREATE_PO_FAIL
5465 -- PON_AUC_PO_CREATE_PO_SUCCESS
5466
5467 -- should get it from a workflow item-attribute
5468
5469 resultout := wf_engine.GetItemAttrText (itemtype => itemtype,
5470 itemkey => itemkey,
5471 aname => 'AUCTION_PO_EMAIL_TYPE');
5472
5473
5474 END CHECK_PO_EMAIL_TYPE;
5475
5476
5477
5478 PROCEDURE START_PO_CREATION(EFFBUF OUT NOCOPY VARCHAR2,
5479 RETCODE OUT NOCOPY VARCHAR2,
5480 p_auction_header_id IN NUMBER, -- 1
5481 p_user_name IN VARCHAR2, -- 2
5482 p_user_id IN NUMBER, -- 3
5483 p_formatted_name IN VARCHAR2, -- 4
5484 p_auction_title IN VARCHAR2, -- 5
5485 p_organization_name IN VARCHAR2, -- 6
5486 p_resultout OUT NOCOPY VARCHAR2) IS -- 7
5487
5488
5489 x_itemkey wf_items.ITEM_KEY%TYPE;
5490 x_sequence NUMBER;
5491 x_current_round NUMBER;
5492 x_requistion_based VARCHAR2(12);
5493 x_has_items PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
5494 x_number_of_failed_pos NUMBER;
5495 x_email_type VARCHAR2(240);
5496 x_allocation_error VARCHAR2(2000);
5497 x_line_number NUMBER;
5498 x_item_number pon_auction_item_prices_all.ITEM_NUMBER%TYPE;
5499 x_item_description pon_auction_item_prices_all.ITEM_DESCRIPTION%TYPE;
5500 x_item_revision pon_auction_item_prices_all.ITEM_REVISION%TYPE;
5501 x_requisition_number PON_AUCTION_ITEM_PRICES_ALL.REQUISITION_NUMBER%TYPE;
5502 x_job_name PER_JOBS.NAME%TYPE;
5503 x_document_disp_line_number PON_AUCTION_ITEM_PRICES_ALL.DOCUMENT_DISP_LINE_NUMBER%TYPE;
5504 l_resultout VARCHAR2(10);
5505
5506 x_open_bidding_date date;
5507 x_close_bidding_date date;
5508 x_trading_partner_contact_id number;
5509 x_doctype_id PON_AUCTION_HEADERS_ALL.DOCTYPE_ID%TYPE;
5510 x_trading_partner_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
5511 x_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
5512
5513 l_workflow_failure VARCHAR2(1);
5514
5515 l_api_name VARCHAR2(30) := ' START_PO_CREATION ';
5516 l_debug_enabled VARCHAR2(1) := 'N';
5517 l_exception_enabled VARCHAR2(1) := 'N';
5518 l_progress NUMBER := 0;
5519
5520 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5521
5522 -- Business Events Project
5523 x_return_status VARCHAR2(20);
5524 x_msg_count NUMBER;
5525 x_msg_data VARCHAR2(2000);
5526
5527 l_linked_pr_line_exists VARCHAR2(1);
5528 l_error_code VARCHAR2(1000);
5529
5530 --Bug : 14134092
5531 l_is_line_type_enabled VARCHAR2(1);
5532
5533 BEGIN
5534
5535 /* perform initialization for FND logging */
5536 if(g_fnd_debug = 'Y') then
5537
5538 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
5539 l_debug_enabled := 'Y';
5540 end if;
5541
5542 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
5543 l_exception_enabled := 'Y';
5544 end if;
5545
5546 end if;
5547
5548 if(l_debug_enabled = 'Y') then
5549
5550 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
5551
5552 log_message(x_progress);
5553
5554 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auction_header_id
5555 || ' ' || p_user_name || ' ' || p_user_id || ' '
5556 || p_formatted_name || ' ' || p_auction_title || ' '
5557 || p_organization_name;
5558 log_message(x_progress);
5559
5560 end if;
5561
5562
5563 log_message(++l_progress || l_api_name || '1. Start PO Creation for ' || p_auction_header_id || ' initiated by '
5564 || p_user_name || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
5565
5566 select open_bidding_date,
5567 close_bidding_date,
5568 trading_partner_contact_id,
5569 doctype_id,
5570 trading_partner_name,
5571 trading_partner_contact_name,
5572 has_items_flag,
5573 nvl(wf_poncompl_current_round, 0)
5574 into x_open_bidding_date,
5575 x_close_bidding_date,
5576 x_trading_partner_contact_id,
5577 x_doctype_id,
5578 x_trading_partner_name,
5579 x_trading_partner_contact_name,
5580 x_has_items,
5581 x_current_round
5582 from pon_auction_headers_all
5583 where auction_header_id = p_auction_header_id;
5584
5585
5586 if(l_debug_enabled = 'Y') then
5587 x_progress := ++l_progress || l_api_name || ' : retrieved header information :';
5588 log_message(x_progress);
5589 end if;
5590
5591 --check if the negotiation has requistion based line
5592 BEGIN
5593 SELECT 'REQUISITION'
5594 INTO x_requistion_based
5595 FROM DUAL
5596 WHERE EXISTS(
5597 SELECT '1'
5598 FROM pon_auction_item_prices_all
5599 WHERE auction_header_id = p_auction_header_id
5600 AND line_origination_code = 'REQUISITION'
5601 );
5602
5603 if(l_debug_enabled = 'Y') then
5604 x_progress := ++l_progress || l_api_name || ' : checking whether req-based auction :' || x_requistion_based;
5605 log_message(x_progress);
5606 end if;
5607
5608 EXCEPTION
5609 WHEN NO_DATA_FOUND THEN
5610
5611 x_requistion_based := 'NONE';
5612
5613 if(l_exception_enabled = 'Y') then
5614 x_progress := ++l_progress || l_api_name || ' : exception while checking whether req-based auction :'
5615 || substrb(SQLERRM, 1, 500);
5616 log_error(x_progress);
5617 end if;
5618
5619
5620 END;
5621
5622 if(l_debug_enabled = 'Y') then
5623 x_progress := ++l_progress || l_api_name || ' : getting in to the main try-catch block :';
5624 log_message(x_progress);
5625 end if;
5626
5627 BEGIN -- main try-catch block --{
5628
5629 -- since we havent set wf_poncompl_current_round in pon_auction_headers_all
5630 -- as yet, x_current_round will be zero if there was no failure reported earlier
5631
5632 UPDATE pon_auction_headers_all set
5633 outcome_status = decode(x_current_round, 0, 'OUTCOME_INITIATED', 'OUTCOME_REINITIATED'),
5634 last_update_date = sysdate
5635 WHERE auction_header_id = p_auction_header_id;
5636
5637
5638 if(l_debug_enabled = 'Y') then
5639 x_progress := ++l_progress || l_api_name || ' : updated the outcome_status with round number :' || x_current_round;
5640 log_message(x_progress);
5641 end if;
5642
5643 --Bug : 14134092
5644 l_is_line_type_enabled := PON_CLM_UTIL_PKG.get_line_structure_enabled(p_auction_header_id => p_auction_header_id);
5645
5646 -- initialize to success
5647 l_resultout := 'SUCCESS';
5648
5649 /* Check whether the auction has any lines */
5650 --Bug : 14134092 : If line type is enabled, no need to allocate and split req's.
5651 IF(NVL(X_HAS_ITEMS, 'N') = 'Y' AND l_is_line_type_enabled = 'N') THEN
5652
5653 if(l_debug_enabled = 'Y') then
5654 x_progress := ++l_progress || l_api_name || ' : from start_po_creation to x_has_items true '
5655 || ' to auto_alloc_and_split_req :' || x_current_round;
5656 log_message(x_progress);
5657 end if;
5658
5659 /* proceed with allocation if atleast one line has backing reqs */
5660
5661 IF(NVL(x_requistion_based , 'NONE') = 'REQUISITION') THEN
5662
5663 if(l_debug_enabled = 'Y') then
5664 x_progress := ++l_progress || l_api_name || ' : we have lines with backing reqs,'
5665 || ' hence invoke AUTO_ALLOC_AND_SPLIT_REQ.';
5666 log_message(x_progress);
5667 end if;
5668
5669
5670 log_message('21. from start_po_creation to x_requistion_based is true to auto_alloc_and_split_req');
5671
5672 AUTO_ALLOC_AND_SPLIT_REQ(
5673 p_auction_header_id, -- 1
5674 p_user_name ,
5675 p_user_id ,
5676 p_formatted_name ,
5677 p_auction_title ,
5678 p_organization_name ,
5679 l_resultout ,
5680 x_allocation_error ,
5681 x_line_number ,
5682 x_item_number ,
5683 x_item_description ,
5684 x_item_revision ,
5685 x_requisition_number ,
5686 x_job_name ,
5687 x_document_disp_line_number);
5688
5689 IF(l_resultout = 'FAILURE') THEN
5690
5691
5692 if(l_exception_enabled = 'Y') then
5693 x_progress := ++l_progress || l_api_name || ' : auto_alloc_and_split_req returned '
5694 || ' failure for auction ' || p_auction_header_id;
5695 log_error(x_progress);
5696 end if;
5697
5698 x_email_type := 'PON_AUC_PO_ALLOC_SPLIT_FAIL';
5699 END IF;
5700
5701 END IF;
5702
5703 END IF;
5704
5705 if(l_debug_enabled = 'Y') then
5706 x_progress := ++l_progress || l_api_name || ' : so far so good after req. based handling';
5707 log_message(x_progress);
5708 end if;
5709
5710 /*Line Type and Structure Changes project*/
5711 IF(l_resultout = 'SUCCESS') THEN
5712
5713 if(l_debug_enabled = 'Y') then
5714 x_progress := ++l_progress || l_api_name || ' : call API TO populate fund allocations to PON_AWARD_ALLOCATIONS table';
5715 log_message(x_progress);
5716 end if;
5717
5718 BEGIN
5719 --API call TO populate fund allocations to PON_AWARD_ALLOCATIONS table
5720 SELECT 'Y' INTO l_linked_pr_line_exists
5721 FROM pon_bid_backing_requisitions
5722 WHERE auction_header_id = p_auction_header_id
5723 AND ROWNUM <2;
5724
5725 EXCEPTION
5726 WHEN No_Data_Found
5727 THEN
5728 l_linked_pr_line_exists := 'N';
5729 END;
5730
5731 --Get_line_structure enabled doesnt have function check.
5732 IF(l_linked_pr_line_exists = 'Y' AND l_is_line_type_enabled = 'Y') THEN --Bug : 14134092
5733 create_pon_back_req_alloc(p_auction_header_id, l_resultout);
5734
5735 IF(l_resultout = 'SUCCESS') THEN --{
5736
5737 IF(l_debug_enabled = 'Y') then
5738 x_progress := ++l_progress || l_api_name || ' create_pon_back_req_alloc completed successfully';
5739 log_message(x_progress);
5740 END IF;
5741
5742 po_negotiations_sv1.update_sol_ref_delete_all(p_auction_header_id => p_auction_header_id,
5743
5744 p_delete_pbr_yn => 'N',
5745 x_return_status => x_return_status,
5746 x_error_msg => x_msg_data,
5747 x_error_code => l_error_code);
5748 END IF;
5749 END IF;
5750 END IF;
5751
5752 IF(l_resultout = 'SUCCESS') THEN --{
5753
5754 if(l_debug_enabled = 'Y') then
5755 x_progress := ++l_progress || l_api_name || ' : so far so good ready to invoke generate_pos';
5756 log_message(x_progress);
5757
5758 log_message(++l_progress || l_api_name || '2. Invoke GENERATE-POS for ' || p_auction_header_id
5759 || ' initiated by ' || p_user_name || ' at '
5760 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
5761 end if;
5762
5763 GENERATE_POS(p_auction_header_id,
5764 p_user_name,
5765 p_user_id,
5766 l_resultout);
5767
5768
5769 if(l_resultout = 'W') then
5770 -- just keep track that po approval workflow caused an error
5771 -- proceed with normal operations
5772 l_workflow_failure := 'Y';
5773 end if;
5774
5775 if(l_debug_enabled = 'Y') then
5776 x_progress := ++l_progress || l_api_name || ' : control returned from generate_pos' || l_resultout;
5777 log_message(x_progress);
5778 end if;
5779
5780 x_number_of_failed_pos := 0;
5781
5782 SELECT count(pbh.bid_number)
5783 INTO x_number_of_failed_pos
5784 FROM pon_bid_headers pbh
5785 WHERE pbh.auction_header_id = p_auction_header_id and
5786 nvl(pbh.bid_status, 'NONE') = 'ACTIVE' and
5787 pbh.po_header_id is NULL and
5788 nvl(pbh.award_status, 'NO') in ('AWARDED', 'PARTIAL');
5789
5790 IF (x_number_of_failed_pos > 0) THEN
5791
5792 l_resultout := 'FAILURE';
5793
5794 if(l_exception_enabled = 'Y') then
5795 log_error(++l_progress || l_api_name || '2. GENERATE-POS failed as x_number_of_failed_pos is '
5796 || ' more than zero '
5797 || p_auction_header_id || ' initiated by ' || p_user_name || ' at '
5798 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
5799 end if;
5800
5801 UPDATE PON_AUCTION_HEADERS_ALL
5802 SET OUTCOME_STATUS = 'OUTCOME_FAILED'
5803 WHERE AUCTION_HEADER_ID = p_auction_header_id;
5804
5805 IF(x_requistion_based = 'REQUISITION') THEN
5806 x_email_type := 'PON_AUC_PO_ALLOC_REQS_FAIL';
5807 ELSE
5808 x_email_type := 'PON_AUC_PO_CREATE_PO_FAIL';
5809 END IF;
5810
5811 ELSE
5812
5813
5814 if(l_debug_enabled = 'Y') then
5815 x_progress := ++l_progress || l_api_name || ' : generate_pos is successful';
5816 log_message(x_progress);
5817 end if;
5818
5819
5820 log_message('70. generate_pos successful');
5821 /* update auction outcome status to outcome_completed */
5822 UPDATE PON_AUCTION_HEADERS_ALL
5823 SET OUTCOME_STATUS = 'OUTCOME_COMPLETED'
5824 WHERE AUCTION_HEADER_ID = p_auction_header_id;
5825
5826 l_resultout := 'SUCCESS';
5827
5828 x_email_type := 'PON_AUC_PO_CREATE_PO_SUCCESS';
5829
5830 END IF;
5831
5832 END IF; --}
5833
5834 EXCEPTION --}
5835
5836 WHEN OTHERS THEN
5837
5838 if(l_exception_enabled = 'Y') then
5839 log_error(++l_progress || l_api_name || ' FATAL EXCEPTION in main try-catch block for generate pos '
5840 || substrb(SQLERRM, 1, 2500));
5841 end if;
5842
5843 rollback;
5844
5845 l_resultout := 'FAILURE';
5846
5847 UPDATE PON_AUCTION_HEADERS_ALL
5848 SET OUTCOME_STATUS = 'OUTCOME_FAILED'
5849 WHERE AUCTION_HEADER_ID = p_auction_header_id;
5850
5851 x_email_type := 'PON_AUC_PO_CREATE_PO_FAIL';
5852
5853 END;
5854
5855 if(l_debug_enabled = 'Y') then
5856 x_progress := ++l_progress || l_api_name || ' : invoke start_po_workflow for email ' || x_email_type;
5857 log_message(x_progress);
5858
5859 log_message(++l_progress || l_api_name || '3. Invoke START_PO_WORKFLOW for ' || p_auction_header_id
5860 || ' initiated by ' || p_user_name || ' for email ' || x_email_type || ' at '
5861 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
5862 END IF;
5863
5864 /* if we have reached here, check the status so far and set the out parameter accordingly */
5865
5866 IF (l_resultout = 'SUCCESS') THEN
5867 p_resultout := 'S';
5868 ELSE
5869 p_resultout := 'F';
5870 END IF;
5871
5872 /* before we invoke the workflow, lets commit everything -
5873 if there was an exception, we have updated the status as well
5874 */
5875
5876 COMMIT;
5877
5878 BEGIN
5879
5880 -- Get next value in sequence for itemkey
5881
5882 SELECT pon_auction_wf_createpo_s.nextval
5883 INTO x_sequence
5884 FROM dual;
5885
5886 x_itemkey := (to_char(p_auction_header_id)||'-'||to_char(x_sequence));
5887
5888 -- update pon_auction_headers_all.wf_poncompl_current_round by incrementing by one
5889 -- update pon_bid_headers.po_wf_creation_rnd by incrementing by one
5890
5891 UPDATE pon_auction_headers_all set
5892 wf_poncompl_item_key = x_itemkey,
5893 wf_poncompl_current_round = x_current_round+1,
5894 last_update_date = sysdate
5895 WHERE auction_header_id = p_auction_header_id;
5896
5897 UPDATE pon_bid_headers set
5898 po_wf_creation_rnd = x_current_round+1
5899 WHERE auction_header_id = p_auction_header_id;
5900
5901 log_message(++l_progress || l_api_name || '. invoke start_po_workflow for itemkey=' || x_itemkey);
5902
5903 START_PO_WORKFLOW(p_auction_header_id ,
5904 p_user_name ,
5905 p_user_id ,
5906 p_formatted_name ,
5907 p_auction_title ,
5908 p_organization_name ,
5909 x_email_type ,
5910 x_itemkey ,
5911 x_allocation_error ,
5912 x_line_number ,
5913 x_item_number ,
5914 x_item_description ,
5915 x_item_revision ,
5916 x_requisition_number ,
5917 x_job_name ,
5918 x_document_disp_line_number );
5919
5920
5921 log_message(++l_progress || l_api_name || '3. AFTER START_PO_WORKFLOW for ' || p_auction_header_id
5922 || ' initiated by ' || p_user_name || ' for email ' || x_email_type || ' at '
5923 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss') || ' with p_resultout = '
5924 || p_resultout);
5925
5926
5927 EXCEPTION
5928 WHEN OTHERS THEN
5929
5930 if(l_exception_enabled = 'Y') then
5931 log_error(++l_progress || l_api_name || '3. EXCEPTION DURING START_PO_WORKFLOW for '
5932 || p_auction_header_id || ' initiated by ' || p_user_name || ' for email '
5933 || x_email_type || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss')
5934 || ' with p_resultout = ' || p_resultout || ' exception = '
5935 || substrb(SQLERRM, 1 , 500));
5936 end if;
5937
5938 /* our plan is to simply ignore this exception during create PO - we should not
5939 affect creation of a PO if we are not able to send an email - simply report it as a
5940 warning
5941 */
5942 l_workflow_failure := 'Y';
5943
5944 END;
5945
5946 -- Raise Business Event
5947 PON_BIZ_EVENTS_PVT.RAISE_PO_CREATION_INIT_EVENT (
5948 p_api_version => 1.0 ,
5949 p_init_msg_list => FND_API.G_FALSE,
5950 p_commit => FND_API.G_FALSE,
5951 p_auction_header_id => p_auction_header_id,
5952 p_user_name => p_user_name,
5953 p_requisition_based_flag => x_requistion_based,
5954 x_return_status => x_return_status,
5955 x_msg_count => x_msg_count,
5956 x_msg_data => x_msg_data);
5957
5958 -- finally, check whether our workflows behaved badly
5959 -- we will display a warning on the PO summary page
5960 -- perform this check only if all proceedings were successful
5961 if(l_workflow_failure = 'Y') then
5962 if(p_resultout = 'S') then
5963 p_resultout := 'W';
5964 end if;
5965 end if;
5966
5967 log_message(++l_progress || l_api_name || '4.Finished PO Creation for ' || p_auction_header_id
5968 || ' initiated by ' || p_user_name || ' for email ' || x_email_type || ' at '
5969 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss')
5970 || ' with final p_resultout=' || p_resultout);
5971
5972 END START_PO_CREATION;
5973
5974 PROCEDURE MAINTAIN_CLM_RELATIONS(x_interface_id IN NUMBER)
5975 IS
5976
5977 CURSOR po_lines IS
5978 SELECT interface_line_id , auction_line_number
5979 FROM po_lines_interface
5980 WHERE interface_header_id = x_interface_id;
5981
5982 BEGIN
5983
5984 FOR oneline IN po_lines
5985 LOOP
5986 UPDATE po_lines_interface
5987 SET group_line_id = oneline.interface_line_id
5988 WHERE interface_header_id = x_interface_id
5989 AND group_line_id = oneline.auction_line_number;
5990
5991 UPDATE po_lines_interface
5992 SET clm_base_line_num = oneline.interface_line_id
5993 WHERE interface_header_id = x_interface_id
5994 AND clm_base_line_num = oneline.auction_line_number;
5995
5996 END LOOP;
5997
5998 EXCEPTION
5999 WHEN NO_DATA_FOUND THEN
6000 NULL;
6001 END MAINTAIN_CLM_RELATIONS;
6002
6003 /*
6004 API to update PON_AWARD_ALLOCATION table - allocated funds.
6005 This is to figure out how much of funds are available for each bid line from the req Line
6006 Later this will be used by the po_auto_line_process_pvt.create_pon_back_req_dist to apportion
6007 the funds based on the allocated_funds in the ratio of distributions for each bid line.
6008
6009 The formula here is funds_remaining on req line(R1) * Bid_line_amount(B1) /(Sum of all bid Line amounts where this req line is linked(B1+B2+B3)
6010
6011 allocated_funds := R1*B1/(B1+B2+B3) */
6012
6013 PROCEDURE create_pon_back_req_alloc(p_auction_header_id IN NUMBER, x_resultout in out nocopy varchar2)
6014 IS
6015
6016 l_user_id NUMBER;
6017 l_login_id NUMBER;
6018 l_progress NUMBER;
6019 l_api_name VARCHAR2(150) := 'create_pon_back_req_alloc';
6020 l_debug_enabled VARCHAR2(1);
6021 l_exception_enabled VARCHAR2(1);
6022
6023 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
6024 x_result VARCHAR2(10);
6025 x_error_message VARCHAR2(1000);
6026 l_count NUMBER;
6027
6028 BEGIN
6029
6030 if(g_fnd_debug = 'Y') then
6031
6032 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
6033 l_debug_enabled := 'Y';
6034 end if;
6035
6036 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
6037 l_exception_enabled := 'Y';
6038 end if;
6039
6040 end if;
6041
6042 if(l_debug_enabled = 'Y') then
6043 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
6044 log_message(x_progress);
6045 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auction_header_id;
6046 log_message(x_progress);
6047 end if;
6048
6049 l_user_id := fnd_global.user_id;
6050 l_login_id := fnd_global.login_id;
6051
6052 if(l_debug_enabled = 'Y') then
6053 x_progress := ++l_progress || l_api_name || ' : l_user_id : ' || l_user_id;
6054 log_message(x_progress);
6055 x_progress := ++l_progress || l_api_name || ' : l_login_id : ' || l_login_id;
6056 log_message(x_progress);
6057 end if;
6058
6059 DELETE FROM pon_award_allocations WHERE bid_number IN (
6060 SELECT bid_number FROM pon_bid_headers
6061 WHERE auction_header_id = p_auction_header_id
6062 AND po_header_id IS NULL)
6063 AND IS_LINKED_PR_LINE_YN = 'Y';
6064
6065 if(l_debug_enabled = 'Y') then
6066 x_progress := ++l_progress || l_api_name || ' : after delete : ' || l_user_id;
6067 log_message(x_progress);
6068 end if;
6069
6070 /*Inline View rec_bid_funds: This will give a table of all requisition_line_id's used in this solicitation
6071 and the total amount on bids to which this req line is associated to
6072 i.e B1+B2+B3 if r1 is associated to B1, B2 and B3
6073
6074 Inline View pbrv: This will give a list of all the
6075 1. Sol lines PR Ref
6076 2. Unsol Lines Pr Ref
6077 3. Unsol lines Linked to Sol lines - Linked PR Ref */
6078
6079 INSERT INTO pon_award_allocations(bid_number,
6080 bid_line_number,
6081 orig_req_line_id,
6082 orig_req_header_id,
6083 auction_header_id,
6084 allocated_funds,
6085 is_linked_pr_line_yn,
6086 created_by,
6087 last_update_date,
6088 last_updated_by,
6089 last_update_login,
6090 creation_date
6091 )
6092
6093 ( SELECT pbip.bid_number,
6094 pbip.line_number,
6095 pbrv.requisition_line_id,
6096 pbrv.requisition_header_id,
6097 pbip.auction_header_id,
6098 (pbip.award_quantity * pbip.award_price * prl.funds_remaining )/
6099 (rec_bid_funds.tot_bid_line_amount),
6100 'Y',
6101 l_user_id,
6102 SYSDATE,
6103 l_user_id,
6104 l_login_id,
6105 SYSDATE
6106
6107 FROM pon_bid_item_prices pbip,
6108 pon_bid_headers pbh,
6109 po_clmreq_lines_v prl,
6110 (SELECT pbip1.auction_header_id, pbip1.auction_line_number,pbip1.bid_number, pbip1.line_number AS bid_line_number,
6111 pbr.requisition_line_id, pbr.requisition_header_id
6112 FROM pon_bid_item_references pir, pon_bid_backing_requisitions pbr, pon_bid_item_prices pbip1
6113 WHERE pir.auction_header_id = p_auction_header_id
6114 AND pir.auction_header_id = pbr.auction_header_id
6115 AND pir.auction_line_number = pbr.auction_line_number
6116 AND pir.bid_number = pbip1.bid_number
6117 AND pir.line_number = pbip1.line_number
6118 AND pir.link_done_by = 'BOTH'
6119 union
6120 SELECT pbr.auction_header_id, pbr.auction_line_number, pbr.bid_number, pbr.bid_line_number,
6121 pbr.requisition_line_id , pbr.requisition_header_id
6122 FROM pon_bid_backing_requisitions pbr
6123 WHERE pbr.auction_header_id = p_auction_header_id) pbrv,
6124
6125 (SELECT pbrv1.requisition_line_id, sum (pbip.award_quantity * pbip.award_price) AS tot_bid_line_amount
6126 FROM pon_bid_item_prices pbip,
6127 (SELECT pbip1.auction_header_id, pbip1.auction_line_number,pbip1.bid_number, pbip1.line_number AS bid_line_number,
6128 pbr.requisition_line_id, pbr.requisition_header_id
6129 FROM pon_bid_item_references pir, pon_bid_backing_requisitions pbr, pon_bid_item_prices pbip1
6130 WHERE pir.auction_header_id = p_auction_header_id
6131
6132 AND pir.auction_header_id = pbr.auction_header_id
6133 AND pir.auction_line_number = pbr.auction_line_number
6134 AND pir.bid_number = pbip1.bid_number
6135 AND pir.line_number = pbip1.line_number
6136 AND pir.link_done_by = 'BOTH'
6137 union
6138 SELECT pbr.auction_header_id, pbr.auction_line_number, pbr.bid_number, pbr.bid_line_number,
6139 pbr.requisition_line_id , pbr.requisition_header_id
6140 FROM pon_bid_backing_requisitions pbr
6141 WHERE pbr.auction_header_id = p_auction_header_id ) pbrv1
6142 WHERE pbip.auction_header_id = p_auction_header_id
6143 AND pbip.auction_header_id = pbrv1.auction_header_id
6144 AND pbip.auction_line_number = pbrv1.auction_line_number
6145 AND Decode(pbrv1.bid_number, -1, pbip.bid_number, pbrv1.bid_number) = pbip.bid_number
6146 AND Decode(pbrv1.bid_line_number, -1, pbip.line_number, pbrv1.bid_line_number) = pbip.line_number
6147 AND pbip.award_status = 'AWARDED'
6148 GROUP BY (pbrv1.auction_header_id,pbrv1.requisition_line_id )) rec_bid_funds
6149
6150 WHERE pbh.auction_header_id = p_auction_header_id
6151 AND pbh.bid_number = pbip.bid_number
6152 AND pbh.po_header_id IS NULL
6153 AND pbip.award_status = 'AWARDED'
6154 AND Nvl(pbip.clm_info_flag, 'N') <> 'Y'
6155 AND Nvl(pbip.clm_option_indicator, 'B') <> 'O'
6156 AND pbip.auction_header_id = pbrv.auction_header_id
6157 AND pbip.auction_line_number = pbrv.auction_line_number
6158 AND Decode(pbrv.bid_number, -1,-1 , pbip.bid_number) = pbrv.bid_number
6159 AND Decode(pbrv.bid_line_number, -1,-1 , pbip.line_number) = pbrv.bid_line_number
6160 AND prl.requisition_line_id = pbrv.requisition_line_id
6161 AND pbrv.requisition_line_id = rec_bid_funds.requisition_line_id
6162 AND prl.funds_remaining > 0
6163 --Bug : 14134092 :For unsol lines, skip fund allocations from inventory destination req lines.
6164 AND (pbip.auction_line_number <> -1 OR (pbip.auction_line_number = -1 AND prl.destination_type_code = 'EXPENSE')));
6165 --Bug : 14134092 : When line type is enabled, fund allocation is done for autocreated lines also.
6166 /* AND NOT EXISTS (SELECT 1
6167 FROM pon_auction_item_prices_all paip
6168 WHERE paip.auction_header_id = pbrv.auction_header_id
6169 AND paip.line_number = pbrv.auction_line_number
6170 AND paip.line_origination_code = 'REQUISITION'));*/
6171
6172 l_count := SQL%ROWCOUNT;
6173 if(l_debug_enabled = 'Y') then
6174 x_progress := ++l_progress || l_api_name || ' : after insert : ' || l_count;
6175 log_message(x_progress);
6176 end if;
6177
6178 IF(l_count > 0) THEN
6179 UPDATE pon_award_allocations paa
6180 SET allocated_funds = (SELECT CASE
6181 WHEN (tot_amt_per_bid.allocated_funds > (pbip.award_quantity * pbip.award_price) )
6182 --equate allocated funds to bid_line_amount
6183 THEN ((paa.allocated_funds * (pbip.award_quantity * pbip.award_price ))/tot_amt_per_bid.allocated_funds )
6184 ELSE paa.allocated_funds
6185 END
6186 FROM pon_bid_item_prices pbip,
6187 (SELECT paa1.bid_number, paa1.bid_line_number, Sum(paa1.allocated_funds) AS allocated_funds
6188 FROM pon_award_allocations paa1
6189 GROUP BY (paa1.bid_number, paa1.bid_line_number)) tot_amt_per_bid
6190 WHERE paa.auction_header_id = p_auction_header_id
6191 AND pbip.bid_number = paa.bid_number
6192 AND pbip.line_number = paa.bid_line_number
6193 AND tot_amt_per_bid.bid_number = paa.bid_number
6194 AND tot_amt_per_bid.bid_line_number = paa.bid_line_number) ;
6195
6196 l_count := SQL%ROWCOUNT;
6197
6198 if(l_debug_enabled = 'Y') then
6199 x_progress := ++l_progress || l_api_name || ' : after insert : ' || l_count;
6200 log_message(x_progress);
6201 end if;
6202 END IF;
6203
6204 if(l_debug_enabled = 'Y') then
6205 x_progress := ++l_progress || l_api_name || ' : END :';
6206 log_message(x_progress);
6207 end if;
6208 x_resultout := 'SUCCESS';
6209
6210 EXCEPTION
6211 when others then
6212
6213 if(l_exception_enabled = 'Y') then
6214 x_progress := ++l_progress || l_api_name || ' : exception for auction ' || p_auction_header_id;
6215 log_error(x_progress);
6216 x_progress := ++l_progress || l_api_name || ' : EXCEPTION END';
6217 log_error(x_progress);
6218 end if;
6219 x_resultout := 'FAILURE';
6220
6221 END create_pon_back_req_alloc;
6222
6223 FUNCTION get_vendor_contact_id(
6224 p_trading_partner_contact_id IN NUMBER,
6225 p_vendor_site_id IN NUMBER,
6226 p_vendor_id IN NUMBER)
6227 RETURN NUMBER
6228 IS
6229 l_vendor_contact_id NUMBER := NULL;
6230 BEGIN
6231 SELECT
6232 vendor_contact_id into l_vendor_contact_id
6233 FROM po_vendor_contacts
6234 WHERE per_party_id = p_trading_partner_contact_id
6235 AND vendor_site_id = p_vendor_site_id
6236 AND vendor_id = p_vendor_id
6237 AND INACTIVE_DATE > sysdate;
6238 log_message('PON_AUCTION_CREATE_PO_PKG.get_vendor_contact_id--> l_vendor_contact_id: ' || l_vendor_contact_id);
6239 RETURN l_vendor_contact_id;
6240
6241 EXCEPTION
6242 WHEN OTHERS THEN
6243 log_error('PON_AUCTION_CREATE_PO_PKG.get_vendor_contact_id' || substrb(SQLERRM, 1, 4000));
6244 RETURN NULL;
6245
6246 END get_vendor_contact_id;
6247
6248 END PON_AUCTION_CREATE_PO_PKG;
6249
6250