[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.41 2007/09/14 18:15:12 sssahai 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
42 ---------------------------------------------------------------------------
43 --This procedure is called by the "Auto-Allocation of non-allocated items
44 --and Split requisition" activity node of the PONCOMPL (Sourcing Complete
45 --Auction) Workflow.
46 --It calls ALLOC_ALL_UNALLOC_ITEMS to allocate all unallocated items.
47 --It also populates PO's interface table with the appropriate award and
48 --req info and calls PO's Split Requisition API, which populates the same
49 --table with the new, split req ids
50 ----------------------------------------------------------------------------
51
52 procedure AUTO_ALLOC_AND_SPLIT_REQ(p_auction_header_id IN NUMBER, -- 1
53 p_user_name IN VARCHAR2, -- 2
54 p_user_id IN NUMBER, -- 3
55 p_formatted_name IN VARCHAR2, -- 4
56 p_auction_title IN VARCHAR2, -- 5
57 p_organization_name IN VARCHAR2,
58 p_resultout OUT NOCOPY VARCHAR2,
59 x_allocation_error OUT NOCOPY VARCHAR2,
60 x_line_number OUT NOCOPY NUMBER,
61 x_item_number OUT NOCOPY VARCHAR2,
62 x_item_description OUT NOCOPY VARCHAR2,
63 x_item_revision OUT NOCOPY VARCHAR2,
64 x_requisition_number OUT NOCOPY VARCHAR2,
65 x_job_name OUT NOCOPY VARCHAR2,
66 x_document_disp_line_number OUT NOCOPY VARCHAR2) IS
67
68 x_item VARCHAR2(50);
69 x_allocation_result VARCHAR2(10);
70 x_failure_status VARCHAR2(10);
71 x_alloc_failure_reason VARCHAR2(2000);
72 x_source_reqs_flag VARCHAR2(1);
73 x_contract_type VARCHAR2(10);
74 x_split_result VARCHAR2(10);
75 x_split_failure_reason VARCHAR2(2000);
76 x_split_failed_req_number NUMBER;
77 x_return_error_code VARCHAR2(10);
78
79 x_responsibility_id number := null;
80 x_application_id number := null;
81
82 x_language_code VARCHAR2(3);
83 x_last_update_date pon_auction_headers_all.last_update_date%TYPE;
84 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
85 x_origination_code pon_auction_headers_all.auction_origination_code%TYPE;
86 x_return_code VARCHAR2(10);
87
88 l_api_name VARCHAR2(30) := ' AUTO_ALLOC_AND_SPLIT_REQ';
89 l_debug_enabled VARCHAR2(1) := 'N';
90 l_exception_enabled VARCHAR2(1) := 'N';
91 l_progress NUMBER := 0;
92
93 BEGIN
94
95 /* perform initialization for FND logging */
96 if(g_fnd_debug = 'Y') then
97
98 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
99 l_debug_enabled := 'Y';
100 end if;
101
102 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
103 l_exception_enabled := 'Y';
104 end if;
105
106 end if;
107
108 if(l_debug_enabled = 'Y') then
109
110 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
111
112 log_message(x_progress);
113
114 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auction_header_id
115 || ' ' || p_user_name || ' ' || p_user_id || ' '
116 || p_formatted_name || ' ' || p_auction_title || ' '
117 || p_organization_name;
118 log_message(x_progress);
119
120 end if;
121
122 -- establish savepoint so that if an exception occurs during either
123 -- the auto-allocation or the splitting of requisition, no data inserted
124 -- when auto-allocating will get committed to db
125
126 --savepoint PON_BEFORE_AUTO_ALLOC;
127
128 -- basic initialization
129 x_failure_status := 'SUCCESS';
130 p_resultout := 'SUCCESS';
131
132
133 FND_PROFILE.GET('RESP_ID', x_responsibility_id);
134
135 FND_PROFILE.GET('RESP_APPL_ID', x_application_id);
136
137 fnd_global.APPS_INITIALIZE (p_user_id, x_responsibility_id, x_application_id);
138 --bug 5245568; need to call init to intialize MOAC
139 mo_global.init('PON');
140
141 if(l_debug_enabled = 'Y') then
142 x_progress := ++l_progress || l_api_name || ' : after retrieving resp_id and resp_appl_id';
143 log_message(x_progress);
144 end if;
145
146
147 -- set the session's language so that calls to getMessage would
148 -- return the correct message in user's language
149
150 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(p_user_name, x_language_code);
151
152 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
153
154 if(l_debug_enabled = 'Y') then
155 x_progress := ++l_progress || l_api_name || ' : after retrieving language code, etc';
156 log_message(x_progress);
157 end if;
158
159 -- Lock auction table to prevent concurrency errors
160
161 SELECT last_update_date
162 INTO x_last_update_date
163 FROM pon_auction_headers_all
164 WHERE auction_header_id = p_auction_header_id
165 FOR UPDATE;
166
167 -- Determine whether we are sourcing requisition lines against the
168 -- blanket agreements. In the case of a blanket agreement, if we are
169 -- not, do not automatically allocate or call po's split api
170
171 SELECT nvl(source_reqs_flag,'N'), contract_type, nvl(auction_origination_code, 'NONE')
172 into x_source_reqs_flag, x_contract_type, x_origination_code
173 FROM pon_auction_headers_all
174 where auction_header_id = p_auction_header_id;
175
176
177 if(l_debug_enabled = 'Y') then
178 x_progress := ++l_progress || l_api_name;
179 log_message(x_progress);
180 end if;
181
182 IF (x_origination_code = 'REQUISITION' AND
183 ((x_contract_type = 'BLANKET' AND x_source_reqs_flag = 'Y') OR
184 x_contract_type = 'STANDARD')) THEN
185
186 -- Call procedure to automatically allocate unallocated items
187 ALLOC_ALL_UNALLOC_ITEMS (p_auction_header_id,
188 x_allocation_result,
189 x_alloc_failure_reason,
190 x_line_number,
191 x_item_number,
192 x_item_description,
193 x_item_revision,
194 x_requisition_number,
195 x_job_name,
196 x_document_disp_line_number);
197
198 IF (x_allocation_result = 'FAILURE') THEN
199
200 if(l_debug_enabled = 'Y') then
201 x_progress := ++l_progress || l_api_name || ' failure after auto_alloc_and_split_req';
202 log_message(x_progress);
203 end if;
204
205 x_failure_status := 'FAILURE';
206 -- setting failure reason and item line on which allocation failure
207 -- occurred;
208 -- assigning error to x_allocation_error variable to be accessed after rollback to savepoint PON_BEFORE_AUTO_ALLOC
209 x_allocation_error := x_alloc_failure_reason;
210 ELSE
211
212 if(l_debug_enabled = 'Y') then
213 x_progress := ++l_progress || l_api_name || ' : now invoking split_req_lines for auction ' || p_auction_header_id;
214 log_message(x_progress);
215 end if;
216
217 -- Call procedure to split req lines and update
218 -- pon_award_allocations with split_req_id
219
220 SPLIT_REQ_LINES(p_auction_header_id,
221 x_split_result,
222 x_split_failure_reason,
223 x_line_number,
224 x_item_number,
225 x_item_description,
226 x_item_revision,
227 x_requisition_number,
228 x_job_name);
229
230 IF (x_split_result = 'FAILURE') THEN
231
232 if(l_debug_enabled = 'Y') then
233 x_progress := ++l_progress || l_api_name || ' : split_req_lines resulted in error for '
234 || p_auction_header_id;
235 log_message(x_progress);
236 end if;
237
238 x_failure_status := 'FAILURE';
239
240 -- setting failure reason and item line and req line on which
241 -- split failure occurred
242 -- assigning error to x_allocation_error variable to be accessed
243 -- after rollback to savepoint PON_BEFORE_AUTO_ALLOC
244 x_allocation_error := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SPLIT_ERROR') || ' - ' || x_split_failure_reason;
245
246 if(l_debug_enabled = 'Y') then
247 x_progress := ++l_progress || l_api_name || ' : alloc_error reported is '
248 || x_allocation_error;
249 log_message(x_progress);
250 end if;
251
252 END IF;
253 END IF;
254 END IF; -- end of automatic allocation and splitting
255
256 IF (x_failure_status = 'SUCCESS') THEN
257
258 if(l_debug_enabled = 'Y') then
259 x_progress := ++l_progress || l_api_name || ' : so far things are successful ' ;
260 log_message(x_progress);
261 end if;
262
263 IF (x_origination_code = 'REQUISITION') THEN
264 -- return req back to the pool for negotiation
265
266 if(l_debug_enabled = 'Y') then
267 x_progress := ++l_progress || l_api_name || ' : invoking cancel_negotiation_ref for auction ' || p_auction_header_id;
268 log_message(x_progress);
269 end if;
270
271 PON_AUCTION_PKG.cancel_negotiation_ref(p_auction_header_id, x_return_code);
272 IF (x_return_code = 'SUCCESS') THEN
273
274 if(l_debug_enabled = 'Y') then
275 x_progress := ++l_progress || l_api_name || ' : successful cancel_negotiation_ref for auction ' || p_auction_header_id;
276 log_message(x_progress);
277 end if;
278 p_resultout := 'SUCCESS';
279
280 ELSE
281 if(l_exception_enabled = 'Y') then
282 x_progress := ++l_progress || l_api_name || ' : failure cancel_negotiation_ref for auction ' || p_auction_header_id;
283 log_error(x_progress);
284 end if;
285
286 x_failure_status := 'FAILURE';
287 -- assigning error to x_allocation_error variable to be accessed
288 x_allocation_error := PON_AUCTION_PKG.getMessage('PON_AUC_WF_ALLOC_ERROR');
289
290 END IF;
291 END IF;
292 END IF;
293
294 IF (x_failure_status = 'FAILURE') THEN
295
296 if(l_exception_enabled = 'Y') then
297 x_progress := ++l_progress || l_api_name || ' : failure for auction ' || p_auction_header_id;
298 log_error(x_progress);
299 end if;
300
301
302 p_resultout := 'FAILURE';
303
304 -- call new procedure which sets attributes to generate failure e-mail
305 -- if fails, rollback to save point prior to auto allocation
306 -- double check the setting of alloc_error below
307
308
309 -- update outcome_status of auction
310 UPDATE PON_AUCTION_HEADERS_ALL
311 SET OUTCOME_STATUS = 'ALLOCATION_FAILED'
312 WHERE AUCTION_HEADER_ID = p_auction_header_id;
313
314 if(l_exception_enabled = 'Y') then
315 x_progress := ++l_progress || l_api_name || ' : update outcome_status for auction ' || p_auction_header_id;
316 log_error(x_progress);
317 end if;
318
319 END IF;
320
321 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
322
323 if(l_debug_enabled = 'Y') then
324 x_progress := ++l_progress || l_api_name || ' : END' ;
325 log_message(x_progress);
326 end if;
327
328 EXCEPTION
329 when others then
330
331 if(l_exception_enabled = 'Y') then
332 x_progress := ++l_progress || l_api_name || ' : exception for auction ' || p_auction_header_id;
333 log_error(x_progress);
334 end if;
335
336 p_resultout := 'FAILURE';
337
338 if(l_exception_enabled = 'Y') then
339 x_progress := ++l_progress || l_api_name || ' : set output to failure for auction ' || p_auction_header_id;
340 log_error(x_progress);
341 end if;
342
343 x_allocation_error := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || substrb(SQLERRM, 1, 500);
344
345 if(l_exception_enabled = 'Y') then
346 x_progress := ++l_progress || l_api_name || ' : for auction ' || p_auction_header_id || ' error:: ' || x_allocation_error;
347 log_error(x_progress);
348 end if;
349
350 -- update outcome_status of auction
351 UPDATE PON_AUCTION_HEADERS_ALL
352 SET OUTCOME_STATUS = 'ALLOCATION_FAILED'
353 WHERE AUCTION_HEADER_ID = p_auction_header_id;
354
355 if(l_exception_enabled = 'Y') then
356 x_progress := ++l_progress || l_api_name || ' : EXCEPTION END';
357 log_error(x_progress);
358 end if;
359
360 END AUTO_ALLOC_AND_SPLIT_REQ;
361
362 ----------------------------------------------------------------------
363 -- This procedure takes in an auction header id and calls
364 -- PON_AUCTION_CREATEPO_PKG.AUTO_REQ_ALLOCATION on all unallocated,
365 -- completed items that have awarded bids in this auction, and return a
366 -- 'success' or 'failure' as the allocation result, as well as the failure
367 -- reason if it failed.
368 -----------------------------------------------------------------------
369
370
371 procedure ALLOC_ALL_UNALLOC_ITEMS(p_auction_header_id IN NUMBER,
372 p_allocation_result OUT NOCOPY VARCHAR2,
373 p_failure_reason OUT NOCOPY VARCHAR2,
374 p_item_line_number OUT NOCOPY NUMBER,
375 p_item_number OUT NOCOPY VARCHAR2,
376 p_item_description OUT NOCOPY VARCHAR2,
377 p_item_revision OUT NOCOPY VARCHAR2,
378 p_requisition_number OUT NOCOPY VARCHAR2,
379 p_job_name OUT NOCOPY VARCHAR2,
380 p_document_disp_line_number OUT NOCOPY VARCHAR2) IS
381
382 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
383 x_result VARCHAR2(10);
384 x_error_message VARCHAR2(1000);
385
386 -- unallocatedItems are items that have awarded bids that have yet to be
387 -- allocated
388 CURSOR unallocatedItems IS
389 SELECT distinct itm.line_number
390 FROM pon_auction_item_prices_all itm,
391 po_req_lines_in_pool_src_v prlv
392 WHERE itm.auction_header_id = p_auction_header_id AND
393 nvl(itm.line_origination_code, 'NONE') = 'REQUISITION' AND
394 nvl(itm.allocation_status, 'NO') <> 'ALLOCATED' AND
395 nvl(itm.award_status, 'NO') = 'COMPLETED' AND
396 nvl(itm.awarded_quantity, -99) > 0 AND
397 itm.auction_header_id = prlv.auction_header_id AND
398 itm.line_number = prlv.auction_line_number;
399
400 -- The following cursor is to select line related info to be printed in the
401 -- Allocation Failure notification, if line fails to be auto-allocated.
402 CURSOR wf_item_cur (p_auction_header_id NUMBER, p_line_number NUMBER) IS
403 SELECT itm.document_disp_line_number, itm.item_number, itm.item_revision, itm.item_description,
404 itm.requisition_number, pjo.name
405 FROM pon_auction_item_prices_all itm,
406 per_jobs pjo
407 WHERE itm.auction_header_id = p_auction_header_id AND
408 itm.line_number = p_line_number AND
409 pjo.job_id (+) = itm.job_id;
410
411 l_api_name VARCHAR2(30) := ' ALLOC_ALL_UNALLOC_ITEMS ';
412 l_debug_enabled VARCHAR2(1) := 'N';
413 l_exception_enabled VARCHAR2(1) := 'N';
414 l_progress NUMBER := 0;
415
416 BEGIN
417
418 /* perform initialization for FND logging */
419 if(g_fnd_debug = 'Y') then
420
421 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
422 l_debug_enabled := 'Y';
423 end if;
424
425 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
426 l_exception_enabled := 'Y';
427 end if;
428
429 end if;
430
431 if(l_debug_enabled = 'Y') then
432 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
433 log_message(x_progress);
434 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auction_header_id;
435 log_message(x_progress);
436 end if;
437
438 OPEN unallocatedItems;
439 LOOP
440 FETCH unallocatedItems into p_item_line_number;
441 EXIT WHEN unallocatedItems%NOTFOUND;
442 -- call procedure to automatically allocate for a particular item
443
444 if(l_debug_enabled = 'Y') then
445 x_progress := ++l_progress || l_api_name || ' : invoke auto_req_allocation :';
446 log_message(x_progress);
447 end if;
448
449 AUTO_REQ_ALLOCATION(p_auction_header_id,
450 p_item_line_number,
451 x_result,
452 x_error_message);
453
454 IF (x_result = 'FAILURE') THEN
455
456 p_allocation_result := 'FAILURE';
457 p_failure_reason := x_error_message;
458
459 if(l_exception_enabled = 'Y') then
460 x_progress := ++l_progress || l_api_name || ' : failure after auto_req_allocation :' || x_error_message;
461 log_error(x_progress);
462 end if;
463
464 -- Fetch the item_number, item_revision, item_description,
465 -- requisition_number and job_name values for a given line_number.
466 OPEN wf_item_cur (p_auction_header_id, p_item_line_number);
467 FETCH wf_item_cur INTO p_document_disp_line_number,p_item_number, p_item_revision, p_item_description,
468 p_requisition_number, p_job_name;
469 CLOSE wf_item_cur;
470
471 RETURN;
472 ELSE
473 -- update allocation status of item to allocated if allocation succeeded
474
475 UPDATE pon_auction_item_prices_all
476 SET allocation_status = 'ALLOCATED'
477 WHERE auction_header_id = p_auction_header_id and
478 line_number = p_item_line_number;
479 END IF;
480 END LOOP;
481 p_allocation_result := 'SUCCESS';
482
483 if(l_debug_enabled = 'Y') then
484 x_progress := ++l_progress || l_api_name || ' : END :' || p_auction_header_id;
485 log_message(x_progress);
486 end if;
487
488
489
490 EXCEPTION
491 WHEN OTHERS THEN
492
493 p_allocation_result := 'FAILURE';
494
495 if(l_exception_enabled = 'Y') then
496 x_progress := ++l_progress || l_api_name || ' :EXCEPTION :' || p_auction_header_id;
497 log_error(x_progress);
498 end if;
499
500
501 IF p_item_line_number IS NULL THEN -- -- it means the exception was thrown before line information is selected
502 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');
503 ELSE
504 p_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500);
505 END IF;
506
507 if(l_exception_enabled = 'Y') then
508 x_progress := ++l_progress || l_api_name || ' :EXCEPTION :' || p_failure_reason;
509 log_error(x_progress);
510 end if;
511
512 RAISE;
513
514 END ALLOC_ALL_UNALLOC_ITEMS;
515
516
517
518 PROCEDURE SPLIT_REQ_LINES(p_auction_header_id IN NUMBER,
519 p_split_result OUT NOCOPY VARCHAR2,
520 p_split_failure_reason OUT NOCOPY VARCHAR2,
521 p_item_line_number OUT NOCOPY NUMBER,
522 p_item_number OUT NOCOPY VARCHAR2,
523 p_item_description OUT NOCOPY VARCHAR2,
524 p_item_revision OUT NOCOPY VARCHAR2,
525 p_requisition_number OUT NOCOPY VARCHAR2,
526 p_job_name OUT NOCOPY VARCHAR2) IS
527
528 l_split_error_code VARCHAR2(10);
529 l_orig_req_line NUMBER;
530 l_req_qty NUMBER;
531 l_num_messages NUMBER;
532 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
533 l_req_line_id_col dbms_sql.number_table;
534 lock_not_acquired EXCEPTION;
535 l_user_id NUMBER;
536
537 PRAGMA EXCEPTION_INIT(lock_not_acquired, -54);
538
539 -- cursor picks up all req lines allocated to a single supplier where mrp
540 -- rescheduling has rescheduled the req quantity to a value lower than
541 -- the alloc quantity
542 CURSOR reqRescheduledSingleSupplier IS
543 SELECT paa.orig_req_line_id, prlsv.requisition_quantity
544 FROM pon_award_allocations paa, po_req_lines_in_pool_src_v prlsv,
545 pon_auction_item_prices_all paip
546 WHERE paa.allocated_qty > prlsv.requisition_quantity AND
547 paa.auction_header_id = p_auction_header_id AND
548 nvl(paa.split_req_line_id, -999)= -999 AND
549 nvl(paa.allocated_qty,0) > 0 AND
550 prlsv.requisition_line_id = paa.orig_req_line_id AND
551 prlsv.requisition_header_id = paa.orig_req_header_id AND
552 paip.auction_header_id = paa.auction_header_id AND
553 paip.line_number = paa.bid_line_number AND
554 paip.order_type_lookup_code IN ('AMOUNT', 'QUANTITY')
555 GROUP BY paa.orig_req_line_id, prlsv.requisition_quantity
556 HAVING COUNT(distinct bid_number) = 1;
557
558 -- cursor picks up all req lines allocated to multiple suppliers in
559 -- which the req qty is lower than the allocated qty as a result
560 -- of mrp rescheduling
561 CURSOR reqRescheduledMultSupplier IS
562 SELECT paa.orig_req_line_id
563 FROM pon_award_allocations paa, po_req_lines_in_pool_src_v prlsv,
564 pon_auction_item_prices_all paip
565 WHERE paa.auction_header_id = p_auction_header_id AND
566 nvl(paa.split_req_line_id, -999)= -999 AND
567 nvl(paa.allocated_qty,0) > 0 AND
568 prlsv.requisition_line_id = paa.orig_req_line_id AND
569 prlsv.requisition_header_id = paa.orig_req_header_id AND
570 paip.auction_header_id = paa.auction_header_id AND
571 paip.line_number = paa.bid_line_number AND
572 paip.order_type_lookup_code IN ('AMOUNT', 'QUANTITY')
573 GROUP BY paa.orig_req_line_id
574 HAVING SUM (nvl(paa.allocated_qty,0)) > max(prlsv.requisition_quantity)
575 AND COUNT(distinct bid_number) > 1;
576
577 -- The following cursor is to select line related info to be printed in the
578 -- Allocation Failure notification, if line fails to be auto-allocated.
579 CURSOR wf_item_cur (p_auction_header_id NUMBER, p_orig_req_line_id NUMBER) IS
580 SELECT distinct paa.bid_line_number, itm.item_number, itm.item_revision, itm.item_description,
581 itm.requisition_number, pjo.name
582 FROM pon_auction_item_prices_all itm,
583 per_jobs pjo,
584 pon_award_allocations paa
585 WHERE paa.auction_header_id = itm.auction_header_id AND
586 paa.bid_line_number = itm.line_number AND
587 paa.orig_req_line_id = p_orig_req_line_id AND
588 itm.auction_header_id = p_auction_header_id AND
589 pjo.job_id (+) = itm.job_id;
590
591 l_api_name VARCHAR2(30) := ' SPLIT_REQ_LINES ';
592 l_debug_enabled VARCHAR2(1) := 'N';
593 l_exception_enabled VARCHAR2(1) := 'N';
594 l_progress NUMBER := 0;
595
596
597 BEGIN
598
599 /* perform initialization for FND logging */
600 if(g_fnd_debug = 'Y') then
601
602 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
603 l_debug_enabled := 'Y';
604 end if;
605
606 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
607 l_exception_enabled := 'Y';
608 end if;
609
610 end if;
611
612 if(l_debug_enabled = 'Y') then
613 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
614 log_message(x_progress);
615 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auction_header_id;
616 log_message(x_progress);
617 end if;
618
619
620 p_split_result := 'SUCCESS';
621
622
623 /*
624 rrkulkar-large-auction-support :- need to pass USER_ID
625
626 out-params-setitemattr
627
628 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
629 itemkey => itemkey,
630 aname => 'USER_ID');
631
632 */
633
634
635
636 -- get lock on all backing reqs for a negotiation. If it cannot
637 -- be locked, try 20 rtimes, then set result to failure and
638 -- set appropriate error message before returning and exiting procedure
639 FOR l_index IN 1..20 LOOP
640 BEGIN
641 SELECT requisition_line_id
642 BULK COLLECT INTO l_req_line_id_col
643 FROM po_requisition_lines_all
644 WHERE auction_header_id = p_auction_header_id
645 FOR UPDATE NOWAIT;
646 EXIT;
647 EXCEPTION
648 WHEN lock_not_acquired THEN
649 IF l_index >= 20 THEN
650 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');
651 p_split_result := 'FAILURE';
652
653 RETURN;
654 END IF;
655 END;
656 END LOOP;
657
658 IF (p_split_result = 'SUCCESS') THEN
659 -- if any req line allocated to multiple suppliers has been rescheduled
660 -- fail the process and include failure reason in e-mail
661 OPEN reqRescheduledMultSupplier;
662 LOOP
663 FETCH reqRescheduledMultSupplier
664 INTO l_orig_req_line;
665 EXIT WHEN reqRescheduledMultSupplier%NOTFOUND;
666
667 p_split_result := 'FAILURE';
668 --p_split_failed_req := l_orig_req_line;
669 p_split_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SPLIT_ERROR') || ' - ' || PON_AUCTION_PKG.getMessage('PON_AUC_WF_REQ_RESCHEDULED');
670
671 -- Fetch the line_number, item_number, item_revision, item_description,
672 -- requisition_number and job_name values for a given line_number.
673 OPEN wf_item_cur (p_auction_header_id, l_orig_req_line);
674 FETCH wf_item_cur INTO p_item_line_number, p_item_number, p_item_revision, p_item_description,
675 p_requisition_number, p_job_name;
676 CLOSE wf_item_cur;
677
678 END LOOP;
679 CLOSE reqRescheduledMultSupplier;
680 END IF;
681
682 IF (p_split_result = 'SUCCESS') THEN
683
684 if(l_debug_enabled = 'Y') then
685 x_progress := ++l_progress || l_api_name || ' : SUCCESS in split_result so far:';
686 log_message(x_progress);
687 end if;
688
689 -- if req line allocated to single supplier has been rescheduled
690 -- simply decrease allocated qty to new req qty
691
692 OPEN reqRescheduledSingleSupplier;
693 LOOP
694
695 if(l_debug_enabled = 'Y') then
696 x_progress := ++l_progress || l_api_name || ' : looping over reqRescheduledSingleSupplier:';
697 log_message(x_progress);
698 end if;
699
700 FETCH reqRescheduledSingleSupplier
701 INTO l_orig_req_line, l_req_qty;
702 EXIT WHEN reqRescheduledSingleSupplier%NOTFOUND;
703
704 UPDATE PON_AWARD_ALLOCATIONS
705 SET allocated_qty = l_req_qty,
706 last_update_date = sysdate,
707 last_updated_by = l_user_id
708 WHERE orig_req_line_id = l_orig_req_line;
709
710 END LOOP;
711 CLOSE reqRescheduledSingleSupplier;
712
713 -- Insert values into po's split temp global table
714 INSERT INTO po_req_split_lines_GT (
715 auction_header_id,
716 bid_number,
717 bid_line_number,
718 requisition_header_id,
719 requisition_line_id,
720 allocated_qty
721 )
722 SELECT paa.auction_header_id,
723 paa.bid_number,
724 paa.bid_line_number,
725 paa.orig_req_header_id,
726 paa.orig_req_line_id,
727 paa.allocated_qty
728 FROM pon_award_allocations paa
729 WHERE paa.auction_header_id = p_auction_header_id AND
730 nvl(paa.split_req_line_id, -999)= -999 AND
731 nvl(paa.allocated_qty,0) > 0;
732
733 -- DEBUG CODE
734 -- INSERT INTO po_req_split_lines_gt_debug (SELECT * FROM po_req_split_lines_gt WHERE auction_header_id = p_auction_header_id);
735
736 if(l_debug_enabled = 'Y') then
737 x_progress := ++l_progress || l_api_name || ' : invoke po_negotiations4_grp.split_requisitionlines :';
738 log_message(x_progress);
739 end if;
740
741 -- calling PO's split req api
742 -- passing in api_version, init_msg_list, commit_data,
743 -- auction_header_id
744 -- registering out result, error, num_msgs, error_msg, failed req
745 -- bug 3955102 - invoke API by names, not index
746 PO_NEGOTIATIONS4_GRP.Split_RequisitionLines(
747 P_API_VERSION => 1.0,
748 P_INIT_MSG_LIST => FND_API.G_FALSE,
749 P_COMMIT => FND_API.G_FALSE,
750 X_RETURN_STATUS => p_split_result,
751 X_MSG_COUNT => l_num_messages,
752 X_MSG_DATA => p_split_failure_reason,
753 P_AUCTION_HEADER_ID => p_auction_header_id);
754
755
756 if(l_debug_enabled = 'Y') then
757 x_progress := ++l_progress || l_api_name || ' : return from po_negotiations4_grp.split_requisitionlines :' || p_split_failure_reason;
758 log_message(x_progress);
759 end if;
760
761 END IF;
762
763 -- If successful, insert values back into sourcing's table
764 --
765 IF (p_split_result = FND_API.G_RET_STS_SUCCESS) THEN
766
767 if(l_debug_enabled = 'Y') then
768 x_progress := ++l_progress || l_api_name || ' : p_split_result is successful :';
769 log_message(x_progress);
770 end if;
771
772 UPDATE PON_AWARD_ALLOCATIONS PAA
773 SET split_req_line_id=
774 (select new_req_line_id
775 from po_req_split_lines_gt prlst
776 where prlst.requisition_line_id = PAA.orig_req_line_id
777 and prlst.auction_header_id = PAA.auction_header_id
778 and prlst.bid_number = PAA.bid_number
779 and prlst.bid_line_number = PAA.bid_line_number
780 and prlst.record_status in ('S', 'E', 'T')),
781 -- status in s and e means newly split lines and lines
782 -- with equal allocation
783 last_update_date = sysdate,
784 last_updated_by = l_user_id
785 WHERE PAA.auction_header_id = p_auction_header_id AND
786 nvl(paa.split_req_line_id, -999)= -999 AND
787 nvl(paa.allocated_qty,0) > 0;
788
789 if(l_debug_enabled = 'Y') then
790 x_progress := ++l_progress || l_api_name || ' : after updating pon_award_allocations with split_req_line_id :';
791 log_message(x_progress);
792 end if;
793
794
795 p_split_result := 'SUCCESS';
796
797 -- If unsuccessful, determine the item number on which the split
798 -- req failed based on the failed req number
799 ELSE
800
801 if(l_debug_enabled = 'Y') then
802 x_progress := ++l_progress || l_api_name || ' : not successful so far :' || p_split_result;
803 log_message(x_progress);
804 end if;
805
806 -- bug 3537686: if there is a message to be returned,
807 -- po populates the message w/ encoded instead of decoded message.
808 -- Here, we will retrieve the last error message to display to user.
809 -- in decoded format
810
811 IF (l_num_messages > 0) THEN
812 p_split_failure_reason := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
813 END IF;
814
815 -- if unexpected error, append the text 'Unexpected System Error' to
816 -- error message
817 IF (p_split_result = FND_API.G_RET_STS_UNEXP_ERROR) THEN
818 p_split_failure_reason := PON_AUCTION_PKG.getMessage('PON_UNEXPECTED_ERROR') || ': ' || p_split_failure_reason;
819 END IF;
820
821 p_split_result := 'FAILURE';
822
823 if(l_debug_enabled = 'Y') then
824 x_progress := ++l_progress || l_api_name || ' : failure :' || p_split_failure_reason ;
825 log_message(x_progress);
826 end if;
827
828 -- Fetch the line_number, item_number, item_revision, item_description,
829 -- requisition_number and job_name values for a given line_number.
830 OPEN wf_item_cur (p_auction_header_id, l_orig_req_line);
831 FETCH wf_item_cur INTO p_item_line_number, p_item_number, p_item_revision, p_item_description,
832 p_requisition_number, p_job_name;
833 CLOSE wf_item_cur;
834
835 END IF;
836
837 if(l_debug_enabled = 'Y') then
838 x_progress := ++l_progress || l_api_name || ' : END :';
839 log_message(x_progress);
840 end if;
841
842
843 EXCEPTION
844 WHEN OTHERS THEN
845 p_split_result := 'FAILURE';
846
847 if(l_exception_enabled = 'Y') then
848 x_progress := ++l_progress || l_api_name || ' : EXCEPTION :';
849 log_error(x_progress);
850 end if;
851
852
853 IF p_item_line_number IS NULL THEN -- -- it means the exception was thrown before line information is selected
854 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');
855 ELSE
856 p_split_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500);
857 END IF;
858
859 if(l_exception_enabled = 'Y') then
860 x_progress := ++l_progress || l_api_name || ' : EXCEPTION with reason :' || p_split_failure_reason;
861 log_error(x_progress);
862 end if;
863
864 RAISE;
865
866 END SPLIT_REQ_LINES;
867
868 -- This procedure allocates the award quantity across the backing requisition
869 -- distributions for a particular item. It does this by ordering the
870 -- requisition distributions and Awarded Suppliers in a predetermined way and
871 -- then fulfilling the requisition demand one by one with the supplier's award
872 -- quantity in a FIFO manner. The ordering is as follows. Requisitions are
873 -- ordered by need_by_date ascending, then creation_date ascending. Awarded
874 -- Suppliers are ordered by promise date ascending, awarded quantity
875 -- descending, bid price ascending, then bid number ascending for standard
876 -- purchase orders. In the case of blanket agreements, promise
877 -- date is implicitly excluded from the ordering, as it will be null.
878
879
880
881 PROCEDURE Auto_Req_Allocation(p_auctionID IN NUMBER,
882 p_line_number IN NUMBER,
883 p_result OUT NOCOPY VARCHAR2,
884 p_error_message OUT NOCOPY VARCHAR2) IS
885
886 l_qty_allocated NUMBER;
887 l_insert_cursor NUMBER;
888 l_insert_result NUMBER;
889 l_reqIdx NUMBER;
890 l_currentReqIdx NUMBER;
891 l_insert_index NUMBER;
892 l_count NUMBER;
893 l_bid_number_col dbms_sql.number_table;
894 l_award_col dbms_sql.number_table;
895 l_req_line_id_col dbms_sql.number_table;
896 l_req_header_id_col dbms_sql.number_table;
897 l_req_quantity_col dbms_sql.number_table;
898 l_req_new_quantity_col dbms_sql.number_table;
899 l_bid_number_insertcol dbms_sql.number_table;
900 l_bid_line_number_insertcol dbms_sql.number_table;
901 l_orig_req_line_insertcol dbms_sql.number_table;
902 l_orig_req_header_insertcol dbms_sql.number_table;
903 l_allocated_qty_insertcol dbms_sql.number_table;
904 l_auction_header_id_insertcol dbms_sql.number_table;
905 l_empty_table dbms_sql.number_table;
906 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
907 l_user_id NUMBER;
908 l_login_id NUMBER;
909 l_bid_price_col dbms_sql.number_table;
910 l_order_type_lookup_code pon_auction_item_prices_all.order_type_lookup_code%TYPE;
911 l_purchase_basis pon_auction_item_prices_all.purchase_basis%TYPE;
912 l_contract_type pon_auction_headers_all.contract_type%TYPE;
913
914
915 l_api_name VARCHAR2(30) := ' AUTO_REQ_ALLOCATION ';
916 l_debug_enabled VARCHAR2(1) := 'N';
917 l_exception_enabled VARCHAR2(1) := 'N';
918 l_progress NUMBER := 0;
919
920 BEGIN
921
922 /* perform initialization for FND logging */
923 if(g_fnd_debug = 'Y') then
924
925 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
926 l_debug_enabled := 'Y';
927 end if;
928
929 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
930 l_exception_enabled := 'Y';
931 end if;
932
933 end if;
934
935 if(l_debug_enabled = 'Y') then
936 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
937 log_message(x_progress);
938 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auctionID || ' ' || p_line_number;
939 log_message(x_progress);
940
941 end if;
942
943
944 /* empty tables to prevent data corruption*/
945 l_bid_number_col := l_empty_table;
946 l_award_col := l_empty_table;
947 l_req_line_id_col := l_empty_table;
948 l_req_header_id_col := l_empty_table;
949 l_req_quantity_col := l_empty_table;
950 l_req_new_quantity_col := l_empty_table;
951 l_bid_number_insertcol := l_empty_table;
952 l_bid_line_number_insertcol := l_empty_table;
953 l_orig_req_line_insertcol := l_empty_table;
954 l_orig_req_header_insertcol := l_empty_table;
955 l_allocated_qty_insertcol := l_empty_table;
956 l_auction_header_id_insertcol := l_empty_table;
957 l_bid_price_col := l_empty_table;
958
959 SELECT paip.order_type_lookup_code, paip.purchase_basis, pah.contract_type
960 INTO l_order_type_lookup_code, l_purchase_basis, l_contract_type
961 FROM pon_auction_item_prices_all paip, pon_auction_headers_all pah
962 WHERE paip.auction_header_id = pah.auction_header_id
963 AND paip.auction_header_id = p_auctionid
964 AND paip.line_number = p_line_number;
965
966 -- Bulk collect req info into collection table.
967
968 SELECT requisition_line_id, requisition_header_id, requisition_quantity
969 BULK COLLECT INTO l_req_line_id_col, l_req_header_id_col,
970 l_req_quantity_col
971 FROM po_req_lines_in_pool_src_v
972 WHERE auction_header_id = p_auctionID AND
973 auction_line_number = p_line_number AND
974 nvl(modified_by_agent_flag, 'N') <> 'Y'
975 ORDER BY need_by_date ASC, creation_date ASC;
976
977
978 -- bulk collect the supplier and award info. In the case of amount based
979 -- lines, the procedure will pick up bid_currency_unit_price.
980 -- Otherwise, the procedure will pick up the award_quantity.
981
982 SELECT decode(itm.order_type_lookup_code, 'AMOUNT',
983 bl.bid_currency_unit_price,
984 bl.award_quantity) quantity,
985 bh.bid_number,
986 bl.bid_currency_unit_price
987 BULK COLLECT INTO l_award_col, l_bid_number_col, l_bid_price_col
988 FROM pon_auction_item_prices_all itm,
989 pon_bid_item_prices bl,
990 pon_bid_headers bh,
991 pon_auction_headers_all pah
992 WHERE itm.auction_header_id = p_auctionID AND
993 itm.line_number = p_line_number AND
994 bl.line_number = itm.line_number AND
995 bl.auction_header_id = itm.auction_header_id AND
996 nvl(bl.award_status,'NO') = 'AWARDED' AND
997 bh.bid_number = bl.bid_number AND
998 bh.auction_header_id = itm.auction_header_id AND
999 nvl(bh.bid_status,'NONE') = 'ACTIVE'AND
1000 pah.auction_header_id = itm.auction_header_id
1001 ORDER BY bl.promised_date ASC, decode(pah.contract_type, 'BLANKET', 1, bl.award_quantity) DESC,
1002 bl.bid_currency_price ASC,
1003 bl.publish_date ASC;
1004
1005
1006
1007
1008 l_insert_index := 1;
1009
1010 -- implements actual allocation algorithm described above
1011
1012 FOR bidIdx IN 1..l_bid_number_col.COUNT LOOP
1013
1014 if(l_debug_enabled = 'Y') then
1015 x_progress := ++l_progress || l_api_name || 'bid award quantity: ' || l_award_col(bidIdx) || 'length of req array: '|| l_req_line_id_col.COUNT;
1016 log_message(x_progress);
1017 end if;
1018
1019 FOR l_reqIdx IN 1..l_req_line_id_col.COUNT LOOP
1020
1021 if(l_debug_enabled = 'Y') then
1022 x_progress := ++l_progress || l_api_name || 'req index: ' || l_reqIdx || ' req quantity: ' || l_req_quantity_col(l_reqIdx);
1023 log_message(x_progress);
1024 end if;
1025
1026
1027 -- IF ('BLANKET' = l_contract_type AND
1028 -- ('QUANTITY' = l_order_type_lookup_code OR
1029 -- 'AMOUNT' = l_order_type_lookup_code)) THEN
1030 -- IF (bidIdx = 1) THEN
1031 -- Full allocation goes to first bidder
1032 -- l_qty_allocated := l_req_quantity_col(l_reqIdx);
1033 -- ELSE
1034 -- l_qty_allocated := 0;
1035 -- END IF;
1036 -- ELS
1037
1038 IF ('RATE' = l_order_type_lookup_code OR
1039 'FIXED PRICE' = l_order_type_lookup_code) THEN
1040 IF (bidIdx = 1) THEN
1041 -- Allocation goes to first bidder
1042 l_qty_allocated := 1;
1043 ELSE
1044 l_qty_allocated := 0;
1045 END IF;
1046 ELSIF (l_req_quantity_col(l_reqIdx) = 0) THEN
1047 l_qty_allocated := 0;
1048 ELSIF (l_award_col(bidIdx) = 0) THEN
1049 l_qty_allocated := 0;
1050 -- If award quantity smaller than req quantity, alloc quantity
1051 -- will be the award quantity
1052 ELSIF (l_award_col(bidIdx) < l_req_quantity_col(l_reqIdx)) THEN
1053 l_qty_allocated := l_award_col(bidIdx);
1054 -- if award quantity equal to req quantity or if award quantity
1055 -- greater than req quantity
1056 ELSE
1057 l_qty_allocated := l_req_quantity_col(l_reqIdx);
1058 END IF;
1059
1060 x_progress := '25: Auto_Req_Allocation: ' || 'qty allocated: ' || l_qty_allocated;
1061 log_message(x_progress);
1062
1063 l_award_col(bidIdx) := l_award_col(bidIdx) - l_qty_allocated;
1064 l_req_quantity_col(l_reqIdx) := l_req_quantity_col(l_reqIdx) - l_qty_allocated;
1065
1066 -- insert into collection object for bulk insert later
1067 l_bid_number_insertcol(l_insert_index) := l_bid_number_col(bidIdx);
1068 l_bid_line_number_insertcol(l_insert_index) := p_line_number;
1069 l_orig_req_line_insertcol(l_insert_index) := l_req_line_id_col(l_reqIdx);
1070 l_orig_req_header_insertcol(l_insert_index) := l_req_header_id_col(l_reqIdx);
1071 l_allocated_qty_insertcol(l_insert_index) := l_qty_allocated;
1072
1073 x_progress := '30: Auto_Req_Allocation: ' || 'qty allocated: ' || l_allocated_qty_insertcol(l_insert_index) || ' index: ' || l_insert_index;
1074 log_message(x_progress);
1075
1076 l_auction_header_id_insertcol(l_insert_index) := p_auctionID;
1077 l_insert_index := l_insert_index + 1;
1078
1079 END LOOP;
1080 END LOOP;
1081
1082 x_progress := '33: Right before Bulk Insert';
1083 log_message(x_progress);
1084
1085
1086 l_user_id := fnd_global.user_id;
1087 l_login_id := fnd_global.login_id;
1088
1089 -- doing bulk insert
1090 FORALL l_count IN 1..l_bid_number_insertcol.COUNT
1091 INSERT INTO pon_award_allocations(bid_number, bid_line_number, orig_req_line_id, orig_req_header_id, allocated_qty, auction_header_id, created_by, last_update_date, last_updated_by, last_update_login, creation_date)
1092 VALUES(l_bid_number_insertcol(l_count),
1093 l_bid_line_number_insertcol(l_count),
1094 l_orig_req_line_insertcol(l_count),
1095 l_orig_req_header_insertcol(l_count),
1096 l_allocated_qty_insertcol(l_count),
1097 l_auction_header_id_insertcol(l_count),
1098 l_user_id,
1099 sysdate,
1100 l_user_id,
1101 l_login_id,
1102 sysdate);
1103 -- end of bulk insert
1104
1105 x_progress := '35: Auto_Req_Allocation: ' || 'qty allocated: ' || l_allocated_qty_insertcol(l_bid_number_insertcol.COUNT);
1106 log_message(x_progress);
1107
1108 p_result := 'SUCCESS';
1109
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 p_result := 'FAILURE';
1113 p_error_message := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500);
1114 log_message(x_progress);
1115 END Auto_Req_Allocation;
1116
1117
1118 -- This procedure is called by NegotiationDoc.startPOCreation. It kicks off
1119 -- the po creation workflow and sets up the wf attributes
1120 PROCEDURE START_PO_WORKFLOW(p_auction_header_id IN NUMBER, -- 1
1121 p_user_name IN VARCHAR2, -- 2
1122 p_user_id IN NUMBER, -- 3
1123 p_formatted_name IN VARCHAR2, -- 4
1124 p_auction_title IN VARCHAR2, -- 5
1125 p_organization_name IN VARCHAR2,
1126 p_email_type IN VARCHAR2,
1127 p_itemkey IN VARCHAR2,
1128 x_allocation_error OUT NOCOPY VARCHAR2,
1129 x_line_number OUT NOCOPY NUMBER,
1130 x_item_number OUT NOCOPY VARCHAR2,
1131 x_item_description OUT NOCOPY VARCHAR2,
1132 x_item_revision OUT NOCOPY VARCHAR2,
1133 x_requisition_number OUT NOCOPY VARCHAR2,
1134 x_job_name OUT NOCOPY VARCHAR2,
1135 x_document_disp_line_number OUT NOCOPY VARCHAR2) IS -- 6
1136
1137 x_itemkey wf_items.ITEM_KEY%TYPE;
1138 x_itemtype wf_items.ITEM_TYPE%TYPE;
1139
1140 x_progress VARCHAR2(4000);
1141 x_language_code VARCHAR2(3);
1142 x_msg_suffix VARCHAR2(3) := '';
1143 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
1144 x_doctype_id pon_auction_headers_all.doctype_id%TYPE;
1145 x_responsibility_id NUMBER;
1146 x_application_id NUMBER;
1147 x_doc_number_dsp VARCHAR2(60);
1148 x_contract_type pon_auction_headers_all.contract_type%TYPE;
1149 x_current_round NUMBER;
1150
1151 x_timezone VARCHAR2(80);
1152 x_newstarttime DATE;
1153 x_newendtime DATE;
1154 x_newpreviewtime DATE;
1155 x_oex_timezone VARCHAR2(80);
1156 x_timezone_disp VARCHAR2(240);
1157 p_open_bidding_date date;
1158 p_close_bidding_date date;
1159 p_trading_partner_contact_id number;
1160 x_award_summary_url_buyer VARCHAR2(2000);
1161 x_alloc_summary_url_buyer VARCHAR2(2000);
1162 x_alloc_byitem_url_buyer VARCHAR2(2000);
1163 x_po_summary_url_buyer VARCHAR2(2000);
1164 p_doctype_id PON_AUCTION_HEADERS_ALL.DOCTYPE_ID%TYPE;
1165 p_trading_partner_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
1166 p_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
1167 x_purchase_order VARCHAR2(30);
1168 x_purchase_orders VARCHAR2(30);
1169 p_preview_date DATE;
1170 x_requistion_based VARCHAR2(12);
1171 x_has_items PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
1172
1173
1174 l_api_name VARCHAR2(30) := ' START_PO_WORKFLOW ';
1175 l_debug_enabled VARCHAR2(1) := 'N';
1176 l_exception_enabled VARCHAR2(1) := 'N';
1177 l_progress NUMBER := 0;
1178
1179 BEGIN
1180
1181 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(p_user_name, x_language_code);
1182
1183 select open_bidding_date,
1184 close_bidding_date,
1185 trading_partner_contact_id,
1186 doctype_id,
1187 trading_partner_name,
1188 trading_partner_contact_name,
1189 has_items_flag
1190 into p_open_bidding_date,
1191 p_close_bidding_date,
1192 p_trading_partner_contact_id,
1193 p_doctype_id,
1194 p_trading_partner_name,
1195 p_trading_partner_contact_name,
1196 x_has_items
1197 from pon_auction_headers_all
1198 where auction_header_id = p_auction_header_id;
1199
1200 x_itemkey := p_itemkey;
1201 x_itemtype:= 'PONCOMPL';
1202
1203 x_progress := '10: START_PO_WORKFLOW: Called with following parameters: ' ||
1204 'ItemType = ' || x_itemType || ', ' ||
1205 'ItemKey = ' || x_itemKey || ', ' ||
1206 'auction_header_id = ' || p_auction_header_id || ', ' ||
1207 'user_name = ' || p_user_name || ', ' ||
1208 'user_id = ' || p_user_id || ', ' ||
1209 'formatted_name = ' || p_formatted_name || ', ' ||
1210 'auction_title = ' || p_auction_title || ', ' ||
1211 'organization_name = ' || p_organization_name;
1212
1213 log_message(x_itemtype || ' ' || x_progress);
1214
1215 wf_engine.CreateProcess(itemtype => x_itemtype,
1216 itemkey => x_itemkey,
1217 process => 'PO_CREATION_ENGINE');
1218
1219 x_progress := '20: START_PO_WORKFLOW: Just after CreateProcess';
1220
1221 log_message(x_itemtype || ' ' || x_progress);
1222
1223 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
1224
1225
1226
1227 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1228 itemkey => x_itemkey,
1229 aname => 'AUCTION_START_DATE',
1230 avalue => p_open_bidding_date);
1231
1232 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1233 itemkey => x_itemkey,
1234 aname => 'AUCTION_END_DATE',
1235 avalue => p_close_bidding_date);
1236
1237 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1238 itemkey => x_itemkey,
1239 aname => 'PREVIEW_DATE',
1240 avalue => p_preview_date);
1241
1242 -- new item attribute to hold the document type id. Item attribute value is going
1243 -- to be used as a parameter to Allocation by Item and Allocation Summary pages
1244 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1245 itemkey => x_itemkey,
1246 aname => 'DOCTYPE_ID',
1247 avalue => p_doctype_id);
1248
1249 --
1250 -- Get the exchange's time zone
1251 --
1252
1253 x_oex_timezone := pon_auction_pkg.Get_Oex_Time_Zone;
1254
1255 --
1256 -- Get the user's time zone
1257 --
1258 x_timezone := pon_auction_pkg.Get_Time_Zone(p_trading_partner_contact_id);
1259
1260 --
1261 -- Make sure that it is a valid time zone
1262 --
1263
1264 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(x_timezone) = 0) THEN
1265 x_timezone := x_oex_timezone;
1266 END IF;
1267
1268 --
1269 -- Convert the dates to the user's timezone.
1270 --
1271
1272 x_newstarttime := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(p_open_bidding_date,x_oex_timezone,x_timezone);
1273 x_newendtime := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(p_close_bidding_date,x_oex_timezone,x_timezone);
1274 x_newpreviewtime := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(p_preview_date,x_oex_timezone,x_timezone);
1275
1276 x_timezone_disp:= pon_auction_pkg.Get_TimeZone_Description(x_timezone, x_language_code);
1277
1278 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1279 itemkey => x_itemkey,
1280 aname => 'TP_TIME_ZONE',
1281 avalue => x_timezone_disp);
1282
1283 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1284 itemkey => x_itemkey,
1285 aname => 'AUCTION_START_DATE_TZ',
1286 avalue => x_newstarttime);
1287
1288 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1289 itemkey => x_itemkey,
1290 aname => 'AUCTION_END_DATE_TZ',
1291 avalue => x_newendtime);
1292
1293
1294 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1295 itemkey => x_itemkey,
1296 aname => 'AUCTION_PO_EMAIL_TYPE',
1297 avalue => p_email_type);
1298
1299 IF (p_preview_date IS NULL) THEN
1300 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1301 itemkey => x_itemkey,
1302 aname => 'PREVIEW_DATE_TZ',
1303 avalue => null);
1304
1305 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1306 itemkey => x_itemkey,
1307 aname => 'TP_TIME_ZONE1',
1308 avalue => null);
1309
1310 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1311 itemkey => x_itemkey,
1312 aname => 'PREVIEW_DATE_NOTSPECIFIED',
1313 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_PREVIEW_DATE_NOTSPEC',x_msg_suffix));
1314 ELSE
1315 wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1316 itemkey => x_itemkey,
1317 aname => 'PREVIEW_DATE_TZ',
1318 avalue => x_newpreviewtime);
1319
1320 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1321 itemkey => x_itemkey,
1322 aname => 'TP_TIME_ZONE1',
1323 avalue => x_timezone_disp);
1324
1325 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1326 itemkey => x_itemkey,
1327 aname => 'PREVIEW_DATE_NOTSPECIFIED',
1328 avalue => null);
1329 END IF;
1330
1331
1332 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1333 itemkey => x_itemkey,
1334 aname => 'AUCTION_ID',
1335 avalue => p_auction_header_id);
1336
1337
1338 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1339 itemkey => x_itemkey,
1340 aname => 'USER_NAME',
1341 avalue => p_user_name);
1342
1343 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1344 itemkey => x_itemkey,
1345 aname => 'USER_ID',
1346 avalue => p_user_id);
1347
1348 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1349 itemkey => x_itemkey,
1350 aname => 'CONTACT_NAME',
1351 avalue => p_formatted_name || ',');
1352
1353
1354 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1355 itemkey => x_itemkey,
1356 aname => 'AUCTION_TITLE',
1357 avalue => PON_AUCTION_PKG.replaceHtmlChars(p_auction_title));
1358
1359 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1360 itemkey => x_itemkey,
1361 aname => 'HAS_ITEMS_FLAG',
1362 avalue => x_has_items);
1363
1364 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1365 itemkey => x_itemkey,
1366 aname => 'ORGANIZATION_NAME',
1367 avalue => p_organization_name);
1368
1369 -- call to notification utility package to get the redirect page url that
1370 -- is responsible for getting the Award Summary url and forward to it.
1371 x_award_summary_url_buyer := pon_wf_utl_pkg.get_dest_page_url (
1372 p_dest_func => 'PON_AWARD_SUMM'
1373 ,p_notif_performer => 'BUYER');
1374
1375 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1376 itemkey => x_itemkey,
1377 aname => 'AWARD_SUMMARY_URL',
1378 avalue => x_award_summary_url_buyer);
1379
1380
1381 -- call to notification utility package to get the redirect page url that
1382 -- is responsible for getting the purchase order summary url and forward to it.
1383
1384 x_po_summary_url_buyer := pon_wf_utl_pkg.get_dest_page_url (
1385 p_dest_func => 'PON_PO_SUMMARY'
1386 ,p_notif_performer => 'BUYER');
1387
1388
1389 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1390 itemkey => x_itemkey,
1391 aname => 'PURCHASE_ORDER_SUMMARY_URL',
1392 avalue => x_po_summary_url_buyer);
1393
1394
1395 -- call to notification utility package to get the redirect page url that
1396 -- is responsible for getting the Allocate Summary url and forward to it.
1397 x_alloc_summary_url_buyer := pon_wf_utl_pkg.get_dest_page_url (
1398 p_dest_func => 'PONCPOSUM_ALLOCSUMMARY'
1399 ,p_notif_performer => 'BUYER');
1400
1401
1402 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1403 itemkey => x_itemkey,
1404 aname => 'ALLOCATE_SUMMARY_URL',
1405 avalue => x_alloc_summary_url_buyer);
1406
1407
1408 -- call to notification utility package to get the redirect page url that
1409 -- is responsible for getting the Allocate by Item url and forward to it.
1410
1411 x_alloc_byitem_url_buyer := pon_wf_utl_pkg.get_dest_page_url (
1412 p_dest_func => 'PONCPOABI_ALLOCATEBYITEM'
1413 ,p_notif_performer => 'BUYER');
1414
1415 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1416 itemkey => x_itemkey,
1417 aname => 'ALLOCATE_ITEM_URL',
1418 avalue => x_alloc_byitem_url_buyer);
1419
1420
1421 /* Setting Profile Attributes */
1422
1423 FND_PROFILE.GET('RESP_ID', x_responsibility_id);
1424
1425 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1426 itemkey => x_itemkey,
1427 aname => 'RESPONSIBILITY_ID',
1428 avalue => x_responsibility_id);
1429
1430 FND_PROFILE.GET('RESP_APPL_ID', x_application_id);
1431
1432 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
1433 itemkey => x_itemkey,
1434 aname => 'APPLICATION_ID',
1435 avalue => x_application_id);
1436
1437 x_progress := 'START_PO_WORKFLOW: profile values: ' ||
1438 'x_responsibility_id: ' || x_responsibility_id || ', ' ||
1439 'x_application_id: ' || x_application_id;
1440
1441 log_message(x_itemtype || ' ' ||x_progress);
1442
1443 /* Setting Message Attributes */
1444
1445 SELECT auh.document_number,
1446 dt.doctype_group_name, auh.contract_type,
1447 nvl(auh.wf_poncompl_current_round, 0), auh.doctype_id
1448 INTO x_doc_number_dsp, x_doctype_group_name, x_contract_type,
1449 x_current_round, x_doctype_id
1450 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
1451 WHERE auh.auction_header_id = p_auction_header_id and
1452 auh.doctype_id = dt.doctype_id;
1453
1454 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
1455
1456 IF (x_contract_type = 'STANDARD') THEN
1457 x_purchase_order := 'Standard Purchase Order';
1458 x_purchase_orders := 'Standard Purchase Orders';
1459 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1460 itemkey => x_itemkey,
1461 aname => 'PON_AUC_WF_PO_CREATE_SUBJ',
1462 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_PO_CREATE_SUBJ', x_msg_suffix, 'DOC_NUMBER', x_doc_number_dsp));
1463 ELSIF (x_contract_type = 'BLANKET') THEN
1464 x_purchase_order := 'Blanket Purchase Agreement';
1465 x_purchase_orders := 'Blanket Purchase Agreements';
1466 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1467 itemkey => x_itemkey,
1468 aname => 'PON_AUC_WF_PO_CREATE_SUBJ',
1469 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_BL_CREATE_SUBJ', x_msg_suffix, 'DOC_NUMBER', x_doc_number_dsp));
1470 ELSIF (x_contract_type = 'CONTRACT') THEN
1471 x_purchase_order := 'Contract Purchase Agreement';
1472 x_purchase_orders := 'Contract Purchase Agreements';
1473 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1474 itemkey => x_itemkey,
1475 aname => 'PON_AUC_WF_PO_CREATE_SUBJ',
1476 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_CPA_CREATE_SUBJ', x_msg_suffix, 'DOC_NUMBER', x_doc_number_dsp));
1477
1478 END IF;
1479
1480 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1481 itemkey => x_itemkey,
1482 aname => 'PURCHASE_ORDERS',
1483 avalue => x_purchase_order);
1484
1485 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1486 itemkey => x_itemkey,
1487 aname => 'PURCHASE_ORDER_TYPE',
1488 avalue => x_purchase_orders);
1489
1490 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1491 itemkey => x_itemkey,
1492 aname => 'WORKFLOW_ROUND_NUMBER',
1493 avalue => x_current_round);
1494
1495 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1496 itemkey => x_itemkey,
1497 aname => 'DOC_NUMBER',
1498 avalue => x_doc_number_dsp);
1499
1500 -- Setting workflow message header attributes
1501 pon_wf_utl_pkg.set_hdr_attributes (p_itemtype => x_itemtype
1502 ,p_itemkey => x_itemkey
1503 ,p_auction_tp_name => p_trading_partner_name
1504 ,p_auction_title => p_auction_title
1505 ,p_document_number => x_doc_number_dsp
1506 ,p_auction_tp_contact_name => p_trading_partner_contact_name);
1507
1508 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1509 itemkey => x_itemkey,
1510 aname => 'MSG_SUFFIX',
1511 avalue => x_msg_suffix);
1512
1513 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1514 itemkey => x_itemkey,
1515 aname => 'PON_AUC_WF_ORG',
1516 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_ORG'));
1517
1518 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1519 itemkey => x_itemkey,
1520 aname => 'PON_AUC_WF_SUCC_MESSAGE',
1521 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUCC_MESSAGE'));
1522
1523 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1524 itemkey => x_itemkey,
1525 aname => 'PON_AUC_WF_RESTART_WF_MSG',
1526 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_RESTART_WF_MSG', x_msg_suffix));
1527
1528 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1529 itemkey => x_itemkey,
1530 aname => 'PON_AUC_WF_RESTART_MSG',
1531 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_RESTART_MSG', x_msg_suffix));
1532
1533 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1534 itemkey => x_itemkey,
1535 aname => 'PON_AUC_WF_ALLOC_SUBJ',
1536 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_ALLOC_SUBJ', x_msg_suffix,
1537 'PURCHASE_ORDERS', x_purchase_order,
1538 'DOC_NUMBER', x_doc_number_dsp));
1539
1540 --check if the negotiation has requistion based line
1541 BEGIN
1542 SELECT 'REQUISITION'
1543 INTO x_requistion_based
1544 FROM DUAL
1545 WHERE EXISTS(
1546 SELECT '1'
1547 FROM pon_auction_item_prices_all
1548 WHERE auction_header_id = p_auction_header_id
1549 AND line_origination_code = 'REQUISITION'
1550 );
1551
1552 EXCEPTION
1553 WHEN NO_DATA_FOUND THEN
1554 x_requistion_based := 'NONE';
1555
1556 WHEN OTHERS THEN
1557 log_error(x_itemtype || ' ' || x_progress || 'in select exception' || SUBSTRB(SQLERRM, 1, 500));
1558 END;
1559
1560 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1561 itemkey => x_itemkey,
1562 aname => 'AUCTION_ORIGINATION_CODE',
1563 avalue => x_requistion_based);
1564
1565
1566 /*
1567 rrkulkar-large-auction-support
1568 */
1569
1570
1571
1572 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1573 itemkey => x_itemkey,
1574 aname => 'ALLOC_ERROR',
1575 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || substrb(SQLERRM, 1, 500));
1576
1577 IF x_line_number IS NULL THEN
1578
1579 -- it means the exception was thrown before line information is selected
1580 -- let buyer know no line information is available
1581
1582 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1583 itemkey => x_itemkey,
1584 aname => 'LINE_NUMBER',
1585 avalue => PON_AUCTION_PKG.getMessage('PON_LINE_INFO_NOT_AVAIL'));
1586 ELSE
1587 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1588 itemkey => x_itemkey,
1589 aname => 'LINE_NUMBER',
1590 avalue => to_char(x_line_number));
1591 END IF;
1592
1593 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1594 itemkey => x_itemkey,
1595 aname => 'ITEM_NUMBER',
1596 avalue => x_item_number);
1597
1598 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1599 itemkey => x_itemkey,
1600 aname => 'LINE_DESCRIPTION',
1601 avalue => x_item_description);
1602
1603 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1604 itemkey => x_itemkey,
1605 aname => 'REVISION_NUMBER',
1606 avalue => x_item_revision);
1607
1608 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1609 itemkey => x_itemkey,
1610 aname => 'REQ_NUMBERS',
1611 avalue => x_requisition_number);
1612
1613 -- setting workflow progress attribute to track the process and easy the debugging process
1614 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1615 itemkey => x_itemkey,
1616 aname => 'WORKFLOW_PROGRESS',
1617 avalue => x_progress);
1618
1619 -- Bug 4456420: Set initiator to current logged in user
1620 wf_engine.SetItemAttrText (itemtype => x_itemtype,
1621 itemkey => x_itemkey,
1622 aname => 'ORIGIN_USER_NAME',
1623 avalue => fnd_global.user_name);
1624
1625
1626 x_progress := '30: START_PO_WORKFLOW: Kicking off StartProcess';
1627 log_message(x_itemtype || ' ' ||x_progress);
1628
1629 -- Bug 4295915: Set the workflow owner
1630 wf_engine.SetItemOwner(itemtype => x_itemtype,
1631 itemkey => x_itemkey,
1632 owner => fnd_global.user_name);
1633
1634 wf_engine.StartProcess(itemtype => x_itemtype,
1635 itemkey => x_itemkey );
1636
1637
1638 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
1639
1640 END START_PO_WORKFLOW;
1641
1642 /*
1643 Creates the award purchase order structure in PDOI
1644 This procedure is invoked from PON_AUCTION_CREATE_PO_PKG.GENERATE_POS procedure
1645 which is invoked from our create po workflow (refer ponwfau7.wft)
1646
1647 This procedure inserts data from PON tables to PO interface tables
1648
1649 */
1650
1651 PROCEDURE CREATE_PO_STRUCTURE(p_auction_header_id IN NUMBER,
1652 p_bid_number IN NUMBER,
1653 p_user_id IN NUMBER,
1654 p_interface_header_id OUT NOCOPY NUMBER,
1655 p_pdoi_header OUT NOCOPY PDOIheader,
1656 p_error_code OUT NOCOPY VARCHAR2,
1657 p_error_message OUT NOCOPY VARCHAR2) IS
1658
1659
1660 x_user_id NUMBER;
1661 x_line_number NUMBER;
1662 x_award_quantity NUMBER;
1663 x_allocation_quantity NUMBER;
1664 x_requisition_line_id NUMBER;
1665 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1666 x_interface_header_id NUMBER;
1667 x_interface_line_id NUMBER;
1668 x_price NUMBER;
1669 x_quantity NUMBER;
1670 x_amount NUMBER;
1671 x_pdoi_header PDOIheader;
1672 x_pdoi_line PDOIline;
1673 x_hasBackingReqs pon_auction_headers_all.auction_origination_code%TYPE;
1674 x_source_reqs_flag VARCHAR2(1);
1675 x_sum_requisitions NUMBER;
1676 sum_of_alloc_quantities sumOfReqLineAllocQuantities;
1677
1678 l_rows_processed NUMBER;
1679 l_batch_end NUMBER;
1680 l_batch_start NUMBER;
1681 l_batch_size NUMBER;
1682 l_max_line_number NUMBER;
1683 l_commit_flag BOOLEAN;
1684
1685 /* Selects the data from Sourcing that will populate PO_HEADERS_INTERFACE */
1686
1687 CURSOR headerLevelInfo IS
1688 SELECT pah.auction_header_id,
1689 pah.document_number,
1690 pah.org_id,
1691 pah.contract_type,
1692 pah.language_code,
1693 pbh.po_start_date,
1694 pbh.po_end_date,
1695 pah.currency_code,
1696 pah.fob_code,
1697 pah.freight_terms_code,
1698 pah.carrier_code,
1699 pah.payment_terms_id,
1700 pah.ship_to_location_id,
1701 pah.bill_to_location_id,
1702 pah.auction_origination_code,
1703 pah.source_reqs_flag,
1704 pbh.bid_number,
1705 pbh.order_number,
1706 pbh.vendor_id,
1707 pbh.vendor_site_id,
1708 pbh.agent_id,
1709 pah.global_agreement_flag,
1710 round(pah.po_min_rel_amount* pbh.rate,fc.precision),
1711 pbh.po_agreed_amount,
1712 pbh.bid_currency_code,
1713 pah.rate_type,
1714 pah.rate_date,
1715 pbh.rate_dsp,
1716 pbh.create_sourcing_rules,
1717 pbh.update_sourcing_rules,
1718 pbh.release_method,
1719 pbh.initiate_approval,
1720 pbh.acceptance_required_flag,
1721 pah.po_style_id,
1722 pah.progress_payment_type,
1723 pah.supplier_enterable_pymt_flag
1724 FROM pon_auction_headers_all pah,
1725 pon_bid_headers pbh,
1726 fnd_currencies fc
1727 WHERE pah.auction_header_id = p_auction_header_id and
1728 pbh.auction_header_id = pah.auction_header_id and
1729 pbh.bid_number = p_bid_number and
1730 pbh.bid_currency_code = fc.currency_code;
1731
1732 /* Selects the data from Sourcing that will populate PO_LINES_INTERFACE */
1733
1734 /*
1735 rrkulkar-large-auction-support - this cursor will bring in all the lines in the middle-tier :
1736 hence, added the following condition in the where clause :-
1737
1738 paip.line_origination_code = 'REQUISITION';
1739
1740 We know that we will not have super-large negotiations with all lines having backing reqs. -
1741 hence, looping over all such lines is just fine.
1742
1743 In SPO outcome case, we will use bulk collect along with batching for super-large auctions for
1744 lines with no backing reqs.
1745
1746 */
1747 CURSOR reqlineLevelInfo IS
1748 SELECT paip.line_number,
1749 paip.line_type_id,
1750 paip.order_type_lookup_code,
1751 paip.line_origination_code,
1752 paip.item_id,
1753 paip.item_revision,
1754 paip.category_id,
1755 paip.item_description,
1756 mtluom.unit_of_measure,
1757 paip.ship_to_location_id,
1758 paip.need_by_start_date,
1759 pbip.award_quantity,
1760 nvl(pbip.po_bid_min_rel_amount, round(paip.po_min_rel_amount* pbh.rate,fc.precision)),
1761 paip.has_price_elements_flag,
1762 decode(paip.order_type_lookup_code, 'FIXED PRICE',
1763 round(pbip.bid_currency_unit_price, fc.precision),
1764 pbip.bid_currency_unit_price),
1765 pbip.promised_date,
1766 paip.job_id,
1767 round(paip.po_agreed_amount*pbh.rate, fc.precision),
1768 paip.purchase_basis
1769 , pbip.bid_curr_advance_amount
1770 , pbip.recoupment_rate_percent
1771 , pbip.progress_pymt_rate_percent
1772 , pbip.retainage_rate_percent
1773 , pbip.bid_curr_max_retainage_amt
1774 , decode(pbip.has_bid_payments_flag, 'Y', decode((select 1 from dual where exists
1775 ( select 1 from pon_bid_payments_shipments where
1776 auction_header_id = pbip.auction_header_id and
1777 bid_number= pbip.bid_number and bid_line_number=pbip.line_number
1778 and bid_currency_price <> 0
1779 )
1780 ),
1781 1,'Y','N')
1782 ,'N') has_bid_payments_flag
1783 , pbip.award_shipment_number
1784
1785 FROM
1786 pon_auction_item_prices_all paip,
1787 pon_bid_item_prices pbip,
1788 mtl_units_of_measure mtluom,
1789 pon_bid_headers pbh,
1790 fnd_currencies fc
1791 WHERE pbip.bid_number = p_bid_number and
1792 pbip.auction_header_id = p_auction_header_id and
1793 nvl(pbip.award_status, 'NO') = 'AWARDED' and
1794 paip.auction_header_id = pbip.auction_header_id and
1795 paip.line_number = pbip.line_number and
1796 paip.group_type NOT IN ('GROUP','LOT_LINE') and
1797 paip.uom_code = mtluom.uom_code (+) and
1798 pbh.bid_number = pbip.bid_number and
1799 fc.currency_code = pbh.bid_currency_code and
1800 paip.line_origination_code = 'REQUISITION';
1801
1802
1803 /* queries the allocation table to get req lines and the allocated quantity backing that particular bid and bid line.*/
1804 CURSOR reqBackingBidItem IS
1805 SELECT split_req_line_id, allocated_qty
1806 FROM pon_award_allocations
1807 WHERE auction_header_id = p_auction_header_id and
1808 bid_number = p_bid_number and
1809 bid_line_number = x_line_number and
1810 nvl(allocated_qty,0) <> 0 and
1811 nvl(split_req_line_id, -999) <> -999;
1812 /* returns the sum of allocated quantities to backing requisition lines for
1813 each negotiation line for a particular bid. The outer join is for picking
1814 up lines that have no allocations, but have backing requisitions to get a
1815 sum of 0 */
1816 CURSOR sumOfReqAllocQuantities is
1817 SELECT PAIP.line_number, nvl(sum(nvl(PAA.allocated_qty,0)), 0)
1818 FROM PON_AWARD_ALLOCATIONS PAA, PON_AUCTION_ITEM_PRICES_ALL PAIP
1819 WHERE PAIP.auction_header_id = p_auction_header_id
1820 AND PAIP.award_status = 'COMPLETED'
1821 AND nvl(PAIP.awarded_quantity,0) > 0
1822 AND PAA.auction_header_id(+) = PAIP.auction_header_id
1823 AND PAA.bid_line_number(+) = PAIP.line_number
1824 AND PAA.bid_number(+) = p_bid_number
1825 AND nvl(PAA.split_req_line_id(+), -999) <> -999
1826 GROUP BY PAIP.line_number;
1827 BEGIN
1828 x_progress := '10: CREATE_PO_STRUCTURE: ' || 'auction_header_id: ' || p_auction_header_id || ', ' || 'bid_number: ' || p_bid_number;
1829 log_message(x_progress);
1830
1831 SELECT po_headers_interface_s.nextval
1832 INTO x_interface_header_id
1833 FROM dual;
1834
1835 OPEN headerLevelInfo;
1836 FETCH headerLevelInfo
1837 INTO x_pdoi_header;
1838 CLOSE headerLevelInfo;
1839
1840 x_hasBackingReqs := x_pdoi_header.auction_origination_code;
1841
1842
1843 /*loops through sumOfReqAllocQuantities cursor and populates
1844 sum_of_alloc_quantities(line) array to hold the total number of req
1845 line quantities for that item line. */
1846
1847 OPEN sumOfReqAllocQuantities;
1848 LOOP
1849 FETCH sumOfReqAllocQuantities INTO x_line_number, x_sum_requisitions;
1850 EXIT WHEN sumOfReqAllocQuantities%NOTFOUND;
1851 sum_of_alloc_quantities(x_line_number) := x_sum_requisitions;
1852 END LOOP;
1853 CLOSE sumOfReqAllocQuantities;
1854
1855 /*
1856 Insert into PO_HEADERS_INTERFACE the purchase order header information based on the negotiation and the award bid.
1857 */
1858
1859 INSERT into PO_HEADERS_INTERFACE (
1860 interface_header_id,
1861 interface_source_code,
1862 batch_id,
1863 action,
1864 org_id,
1865 document_type_code,
1866 document_subtype,
1867 created_language,
1868 effective_date,
1869 expiration_date,
1870 document_num,
1871 group_code,
1872 vendor_id,
1873 vendor_site_id,
1874 agent_id,
1875 currency_code,
1876 rate_type_code,
1877 rate_date,
1878 rate,
1879 fob,
1880 freight_terms,
1881 freight_carrier,
1882 terms_id,
1883 ship_to_location_id,
1884 bill_to_location_id,
1885 consume_req_demand_flag,
1886 global_agreement_flag,
1887 min_release_amount,
1888 amount_agreed,
1889 acceptance_required_flag,
1890 style_id,
1891 created_by,
1892 creation_date,
1893 last_updated_by,
1894 last_update_date)
1895 values (
1896 x_interface_header_id, -- interface_header_id
1897 'SOURCING', -- interface_source_code
1898 x_interface_header_id, -- batch_id
1899 'NEW', -- action
1900 x_pdoi_header.org_id, -- org_id
1901 decode(x_pdoi_header.contract_type, 'BLANKET',
1902 'PA','CONTRACT','PA','PO'), -- document_type_code
1903 x_pdoi_header.contract_type, -- document_subtype
1904 x_pdoi_header.language_code, -- created_language
1905 x_pdoi_header.po_start_date, -- effective_date
1906 x_pdoi_header.po_end_date, -- expiration_date
1907 x_pdoi_header.order_number, -- document_num
1908 'DEFAULT', -- group_code
1909 x_pdoi_header.vendor_id, -- vendor_id
1910 x_pdoi_header.vendor_site_id, -- vendor_site_id
1911 x_pdoi_header.agent_id, -- agent_id
1912 x_pdoi_header.bid_currency_code, -- currency_code
1913 decode(x_pdoi_header.currency_code, x_pdoi_header.bid_currency_code, null, x_pdoi_header.rate_type), -- rate_type_code
1914 decode(x_pdoi_header.currency_code, x_pdoi_header.bid_currency_code, null, x_pdoi_header.rate_date), -- rate_date
1915 decode(x_pdoi_header.currency_code, x_pdoi_header.bid_currency_code, null, x_pdoi_header.rate_dsp), -- rate
1916 x_pdoi_header.fob_code, -- fob
1917 x_pdoi_header.freight_terms_code, -- freight_terms
1918 x_pdoi_header.carrier_code, -- freight_carrier,
1919 x_pdoi_header.payment_terms_id, -- terms_id
1920 x_pdoi_header.ship_to_location_id, -- ship_to_location_id
1921 x_pdoi_header.bill_to_location_id, -- bill_to_location_id
1922 x_pdoi_header.source_reqs_flag, -- consume req demandflag
1923 x_pdoi_header.global_agreement_flag, -- global_agreement_flag
1924 x_pdoi_header.po_min_rel_amount, -- min_release_amount
1925 x_pdoi_header.po_agreed_amount, -- amount_agreed
1926 x_pdoi_header.acceptance_required_flag, -- accept req flag
1927 x_pdoi_header.po_style_id, -- style_id
1928 p_user_id, -- created_by
1929 sysdate, -- creation_date
1930 p_user_id, -- last_update_by
1931 sysdate); -- last_update_date
1932
1933
1934 x_progress := '15: CREATE_PO_STRUCTURE: INSERTING the following data into po_headers_interface: ' ||
1935 'interface_header_id: ' || to_char(x_interface_header_id) || ', ' ||
1936 'interface_source_code: ' || 'SOURCING' || ', ' ||
1937 'batch_id: ' || to_char(x_interface_header_id) || ', ' ||
1938 'action: ' || 'NEW' || ', ' ||
1939 'org_id: ' || to_char(x_pdoi_header.org_id) || ', ' ;
1940
1941 IF (x_pdoi_header.contract_type = 'BLANKET') THEN
1942 x_progress := x_progress || 'document_type_code: ' || 'PA' || ', ';
1943 ELSE
1944 x_progress := x_progress || 'document_type_code: ' || 'PO' || ', ';
1945 END IF;
1946
1947 x_progress := x_progress ||
1948 'document_subtype: ' || x_pdoi_header.contract_type || ', ' ||
1949 'created_language: ' || x_pdoi_header.language_code || ', ' ||
1950 'effective_date: ' || to_char(x_pdoi_header.po_start_date) || ', ' ||
1951 'expiration_date: ' || to_char(x_pdoi_header.po_end_date) || ', ' ||
1952 'document_num: ' || x_pdoi_header.order_number || ', ' ||
1953 'group_code: ' || 'DEFAULT' || ', ' ||
1954 'vendor_id: ' || to_char(x_pdoi_header.vendor_id) || ', ' ||
1955 'vendor_site_id: ' || to_char(x_pdoi_header.vendor_site_id) || ', ' ||
1956 'agent_id: ' || to_char(x_pdoi_header.agent_id) || ', ' ||
1957 'currency_code: ' || x_pdoi_header.bid_currency_code || ', ' ||
1958 'rate_type_code: ' || x_pdoi_header.rate_type || ', ';
1959
1960 IF (x_pdoi_header.currency_code = x_pdoi_header.bid_currency_code) THEN
1961 x_progress := x_progress || 'rate_date: ' || 'null' || ', ' || 'rate: ' || null || ', ';
1962 ELSE
1963 x_progress := x_progress || 'rate_date: ' || x_pdoi_header.rate_date || ', ' || 'rate: ' || to_char(x_pdoi_header.rate_dsp) || ', ';
1964 END IF;
1965
1966 x_progress := x_progress ||
1967 'fob: ' || x_pdoi_header.fob_code || ', ' ||
1968 'freight_terms: ' || x_pdoi_header.freight_terms_code || ', ' ||
1969 'freight_carrier: ' || x_pdoi_header.carrier_code || ', ' ||
1970 'terms_id: ' || to_char(x_pdoi_header.payment_terms_id) || ', ' ||
1971 'ship_to_location_id: ' || to_char(x_pdoi_header.ship_to_location_id) || ', ' ||
1972 'bill_to_location_id: ' || to_char(x_pdoi_header.bill_to_location_id) || ', ' ||
1973 ' source_reqs_flag: ' || x_pdoi_header.source_reqs_flag || ', ' ||
1974 'amount_agreed: ' || to_char(x_pdoi_header.po_agreed_amount) || ', ' ||
1975 'created_by: ' || to_char(p_user_id) || ', ' ||
1976 'last_update_by: ' || to_char(p_user_id);
1977
1978 log_message(x_progress);
1979
1980 IF (x_pdoi_header.contract_type = 'STANDARD') THEN --{
1981
1982 OPEN reqlineLevelInfo;
1983
1984 LOOP --{ -- loop over reqlineLevelInfo
1985
1986 FETCH reqlineLevelInfo INTO x_pdoi_line;
1987 EXIT WHEN reqlineLevelInfo%NOTFOUND;
1988
1989 x_line_number := x_pdoi_line.line_number;
1990
1991 x_price := x_pdoi_line.bid_currency_unit_price;
1992
1993 -- Quantity Based Price Tiers changes
1994 IF x_pdoi_line.award_shipment_number IS NOT NULL THEN
1995
1996 SELECT BID_CURRENCY_UNIT_PRICE INTO x_price
1997 FROM PON_BID_SHIPMENTS
1998 WHERE LINE_NUMBER = x_pdoi_line.line_number
1999 AND AUCTION_HEADER_ID = x_pdoi_header.auction_header_id
2000 AND BID_NUMBER = p_bid_number
2001 AND SHIPMENT_NUMBER = x_pdoi_line.award_shipment_number;
2002
2003 END IF;
2004 IF (x_pdoi_line.order_type_lookup_code = 'FIXED PRICE') THEN
2005 x_amount := x_price;
2006 x_price := NULL;
2007 ELSE
2008 x_amount := NULL;
2009 x_price := x_price;
2010 END IF;
2011
2012 IF (x_pdoi_line.order_type_lookup_code = 'AMOUNT') THEN
2013 x_award_quantity := x_price;
2014 ELSE
2015 x_award_quantity := x_pdoi_line.award_quantity;
2016 END IF;
2017
2018 x_progress := '20: CREATE_PO_STRUCTURE:' || 'Processing bid number: ' || p_bid_number || ', '
2019 || 'line number: ' || x_line_number || ', '
2020 || 'award quantity: ' || x_award_quantity;
2021 log_message(x_progress);
2022
2023
2024 /* contract type is standard and this line comes from a
2025 backing requisition and the sum of the allocated quantity
2026 to the backing requisitions is greater than 0 */
2027
2028 /*
2029 rrkulkar-large-auction-support : since we don't expect too many lines
2030 with backing requisitions (i.e > 2500), we will not add batching over
2031 here
2032
2033 */
2034
2035 IF (x_pdoi_line.line_origination_code = 'REQUISITION' AND
2036 sum_of_alloc_quantities.EXISTS(x_line_number) AND
2037 sum_of_alloc_quantities(x_line_number) > 0) THEN --{
2038
2039 x_progress := '30: CREATE_PO_STRUCTURE: ' || 'Single Supplier is handling the demand';
2040
2041 log_message(x_progress);
2042
2043 OPEN reqBackingBidItem;
2044
2045 LOOP --{ -- loop over reqBackingBidItem
2046
2047 FETCH reqBackingBidItem INTO x_requisition_line_id,
2048 x_allocation_quantity;
2049 EXIT WHEN reqBackingBidItem%NOTFOUND;
2050 x_progress := '30: CREATE_PO_STRUCTURE: ' || 'Req Line: ' || x_requisition_line_id || ', ' || 'Alloc Quantity: ' || x_allocation_quantity;
2051
2052 log_message(x_progress);
2053
2054 /*
2055 Insert a row into PO_LINES_INTERFACE with the
2056 item information from the negotiation line, and
2057 the requisiton_line_id and quantity
2058 (price and quantity will be switched when
2059 negotiation line is amount-based-
2060 check pon_auction_item_prices_all.order_type_lookup_code)
2061 from the backing requisition.
2062 */
2063
2064
2065 INSERT into PO_LINES_INTERFACE (
2066 interface_header_id,
2067 interface_line_id,
2068 requisition_line_id,
2069 line_type_id,
2070 item_id,
2071 item_revision,
2072 category_id,
2073 item_description,
2074 unit_of_measure,
2075 quantity,
2076 unit_price,
2077 min_release_amount,
2078 ship_to_location_id,
2079 need_by_date,
2080 promised_date,
2081 last_updated_by,
2082 last_update_date,
2083 created_by,
2084 creation_date,
2085 auction_header_id,
2086 auction_display_number,
2087 auction_line_number,
2088 bid_number,
2089 bid_line_number,
2090 orig_from_req_flag,
2091 job_id,
2092 amount
2093 , advance_amount
2094 , recoupment_rate
2095 , progress_payment_rate
2096 , retainage_rate
2097 , max_retainage_amount
2098 , line_loc_populated_flag
2099
2100 )
2101
2102 values (
2103
2104 x_interface_header_id, -- interface_header_id
2105 po_lines_interface_s.nextval, -- interface_line_id
2106 x_requisition_line_id, -- requisition_line_id
2107 x_pdoi_line.line_type_id,
2108 -- line_type_id
2109 x_pdoi_line.item_id,
2110 -- item_id
2111 x_pdoi_line.item_revision,
2112 -- item_revision
2113 x_pdoi_line.category_id,
2114 -- category_id
2115 substrb(x_pdoi_line.item_description, 1, 240),
2116 -- item_description
2117 decode(x_pdoi_line.order_type_lookup_code, 'AMOUNT', null, x_pdoi_line.unit_of_measure),
2118 -- unit_of_measure
2119 decode(x_pdoi_line.order_type_lookup_code, 'RATE', NULL, 'FIXED PRICE', NULL, x_allocation_quantity), -- quantity
2120 decode(x_pdoi_line.order_type_lookup_code,'AMOUNT', 1, x_price), -- unit_price
2121 x_pdoi_line.po_min_rel_amount,
2122 -- min_release_amount
2123 x_pdoi_line.ship_to_location_id,
2124 -- ship_to_location_id
2125 x_pdoi_line.need_by_start_date,
2126 -- need_by_start_date
2127 x_pdoi_line.promised_date, -- promised_date
2128 p_user_id, -- last_update_by
2129 sysdate, -- last_update_date
2130 p_user_id, -- created_by
2131 sysdate, -- creation_date
2132 x_pdoi_header.auction_header_id, -- auction_header_id
2133 x_pdoi_header.document_number, -- document_number
2134 x_pdoi_line.line_number, -- auction_line_number,
2135 x_pdoi_header.bid_number, -- bid_number
2136 x_pdoi_line.line_number, -- bid_line_number
2137 'Y', -- orig_from_req_flag
2138 x_pdoi_line.job_id, -- job_id
2139 x_amount -- amount
2140 , decode(x_pdoi_line.bid_curr_advance_amount,0,null,x_pdoi_line.bid_curr_advance_amount)
2141 , x_pdoi_line.recoupment_rate_percent
2142 , x_pdoi_line.progress_pymt_rate_percent
2143 , x_pdoi_line.retainage_rate_percent
2144 , x_pdoi_line.Bid_curr_max_retainage_amt
2145 , x_pdoi_line.has_bid_payments_flag -- Line_loc_populated
2146
2147
2148 ) return interface_line_id into x_interface_line_id;
2149
2150 x_progress :=
2151 '35: CREATE_PO_STRUCTURE: INSERTING the following data into PO_LINES_INTERFACE: ' ||
2152 'interface_header_id: ' || to_char(x_interface_header_id) || ', ' ||
2153 'interface_line_id: ' || to_char(x_interface_line_id) || ', ' ||
2154 'requisition_line_id: ' || to_char(x_requisition_line_id) || ', ' ||
2155 'line_type_id: ' || to_char(x_pdoi_line.line_type_id) || ', ' ||
2156 'item_id: ' || to_char(x_pdoi_line.item_id) || ', ' ||
2157 'item_revision: ' || x_pdoi_line.item_revision || ', ' ||
2158 'category_id: ' || to_char(x_pdoi_line.category_id) || ', ' ||
2159 'item_description: ' || substrb(x_pdoi_line.item_description, 1, 240) || ', ';
2160
2161 IF (x_pdoi_line.order_type_lookup_code = 'AMOUNT') THEN
2162 x_progress := x_progress || 'unit_of_measure: ' || null || ', ' || 'quantity: ' || to_char(x_allocation_quantity) || ', ' ||
2163 'unit_price: ' || 1 || ', ';
2164 ELSE
2165 x_progress := x_progress || 'unit_of_measure: ' || x_pdoi_line.unit_of_measure || ' ' || 'quantity: ' || to_char(x_allocation_quantity) || ', ' ||
2166 'unit_price: ' || to_char(x_price) || ', ';
2167 END IF;
2168
2169 x_progress := x_progress ||
2170 'min_release_amount: ' || to_char(x_pdoi_line.po_min_rel_amount) || ', ' ||
2171 'ship_to_location_id: ' || to_char(x_pdoi_line.ship_to_location_id) ||', ' ||
2172 'need_by_start_date: ' || x_pdoi_line.need_by_start_date || ', ' ||
2173 'promised_date: ' || x_pdoi_line.promised_date || ', ' ||
2174 'last_update_by: ' || to_char(p_user_id) || ', ' ||
2175 'created_by: ' || to_char(p_user_id) || ', ' ||
2176 'auction_header_id: ' || to_char(x_pdoi_header.auction_header_id) || ', ' ||
2177 'document_number: ' || x_pdoi_header.document_number || ', ' ||
2178 'auction_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
2179 'bid_number: ' || to_char(x_pdoi_header.bid_number) || ', ' ||
2180 'bid_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
2181 'orig_from_req_flag: ' || 'Y';
2182
2183 log_message(x_progress);
2184
2185 END LOOP; --} -- stop loop over reqBackingBidItem
2186
2187 CLOSE reqBackingBidItem;
2188
2189 END IF; --} -- end-if to check for requisitions
2190
2191 /*
2192 rrkulkar-large-auction-support :-
2193
2194 Once we have inserted all the lines having allocated quantities
2195 with backing reqs, we need to take care of the following 3 more
2196 conditions :-
2197
2198 case-1. No backing requisition for current line OR
2199 case-2. Lines with backing requisitions have 0 allocation
2200 award quantities
2201 case-3. There is an excess award OR
2202
2203 In either of the aforementioned 3 cases, we need to
2204 insert an additional row into PO_LINES_INTERFACE with the
2205 item information from the negotiation line, a null
2206 requisition_line_id, a quantity for the excess award,
2207 and a value of 'N' in the orig_from_req_flag column.
2208
2209 */
2210
2211 /*
2212 rrkulkar-large-auction-support : In case of super-large auctions,
2213 this case will be satisfied more often than not. Here's what we can do :-
2214
2215 1. split this insert into 2 cases -
2216 1a. use cursor approach for lines with backing reqs. (case-2 and case-3 above)
2217 1b. use batching for lines with no backing reqs
2218 */
2219
2220 x_progress := '25: before execess award';
2221
2222 log_message(x_progress);
2223
2224 IF (
2225 /* case-2 :- zero allocated quantity*/
2226
2227 (x_pdoi_line.line_origination_code = 'REQUISITION' AND
2228 sum_of_alloc_quantities.EXISTS(x_line_number) AND
2229 sum_of_alloc_quantities(x_line_number) = 0) OR
2230
2231 /* case-3 :- excess allocated quantity*/
2232
2233 (x_pdoi_line.line_origination_code = 'REQUISITION' AND
2234 sum_of_alloc_quantities.EXISTS(x_line_number) AND
2235 x_award_quantity > sum_of_alloc_quantities(x_line_number))) THEN
2236
2237 --{ -- 2nd loop for SPO outcome
2238
2239 x_progress := '30: Excess award ' || 'Award Quantity: ' || x_award_quantity;
2240 log_message(x_progress);
2241
2242 IF (x_pdoi_line.order_type_lookup_code = 'RATE' OR
2243 x_pdoi_line.order_type_lookup_code = 'FIXED PRICE') THEN
2244 x_quantity := NULL;
2245 ELSIF (sum_of_alloc_quantities.EXISTS(x_line_number)) THEN
2246 x_quantity := x_award_quantity - sum_of_alloc_quantities(x_line_number);
2247 ELSE
2248 x_quantity := x_award_quantity;
2249 END IF;
2250 INSERT into PO_LINES_INTERFACE (
2251 interface_header_id,
2252 interface_line_id,
2253 requisition_line_id,
2254 line_type_id,
2255 item_id,
2256 item_revision,
2257 category_id,
2258 item_description,
2259 unit_of_measure,
2260 quantity,
2261 unit_price,
2262 min_release_amount,
2263 ship_to_location_id,
2264 need_by_date,
2265 promised_date,
2266 last_updated_by,
2267 last_update_date,
2268 created_by,
2269 creation_date,
2270 auction_header_id,
2271 auction_display_number,
2272 auction_line_number,
2273 bid_number,
2274 bid_line_number,
2275 orig_from_req_flag,
2276 job_id,
2277 amount
2278 , advance_amount
2279 , recoupment_rate
2280 , progress_payment_rate
2281 , retainage_rate
2282 , max_retainage_amount
2283 , Line_loc_populated_flag
2284
2285 )
2286
2287 values (
2288
2289 x_interface_header_id, -- interface_header_id
2290 po_lines_interface_s.nextval, -- interface_line_id
2291 NULL, -- requisition_line_id
2292 x_pdoi_line.line_type_id,
2293 -- line_type_id
2294 x_pdoi_line.item_id,
2295 -- item_id
2296 x_pdoi_line.item_revision,
2297 -- item_revision
2298 x_pdoi_line.category_id,
2299 -- category_id
2300 substrb(x_pdoi_line.item_description, 1, 240),
2301 -- item_description
2302 decode(x_pdoi_line.order_type_lookup_code, 'AMOUNT', null, x_pdoi_line.unit_of_measure), -- unit_of_measure
2303 x_quantity, -- quantity
2304 decode(x_pdoi_line.order_type_lookup_code,'AMOUNT', 1, x_price), -- unit_price
2305 x_pdoi_line.po_min_rel_amount, -- min_release_amount
2306 x_pdoi_line.ship_to_location_id,
2307 -- ship_to_location_id
2308 x_pdoi_line.need_by_start_date,
2309 -- need_by_start_date
2310 x_pdoi_line.promised_date,
2311 -- promised_date
2312 p_user_id, -- last_update_by
2313 sysdate, -- last_update_date
2314 p_user_id, -- created_by
2315 sysdate, -- creation_date
2316 x_pdoi_header.auction_header_id, -- auction_header_id
2317 x_pdoi_header.document_number, -- document_number
2318 x_pdoi_line.line_number, -- auction_line_number,
2319 x_pdoi_header.bid_number, -- bid_number
2320 x_pdoi_line.line_number, -- bid_line_number
2321 'N', -- orig_from_req_flag
2322 x_pdoi_line.job_id, -- job_id
2323 x_amount -- amount
2324 , decode(x_pdoi_line.bid_curr_advance_amount,0,null,x_pdoi_line.bid_curr_advance_amount)
2325 , x_pdoi_line.recoupment_rate_percent
2326 , x_pdoi_line.progress_pymt_rate_percent
2327 , x_pdoi_line.retainage_rate_percent
2328 , x_pdoi_line.Bid_curr_max_retainage_amt
2329 , x_pdoi_line.has_bid_payments_flag -- Line_loc_populated
2330
2331 ) return interface_line_id into x_interface_line_id;
2332
2333
2334 x_progress :=
2335 '35: CREATE_PO_STRUCTURE: INSERTING the following data into PO_LINES_INTERFACE: ' ||
2336 'interface_header_id: ' || to_char(x_interface_header_id) || ', ' ||
2337 'interface_line_id: ' || to_char(x_interface_line_id) || ', ' ||
2338 'requisition_line_id: ' || null || ', ' ||
2339 'line_type_id: ' || to_char(x_pdoi_line.line_type_id) || ', ' ||
2340 'item_id: ' || to_char(x_pdoi_line.item_id) || ', ' ||
2341 'item_revision: ' || x_pdoi_line.item_revision || ', ' ||
2342 'category_id: ' || to_char(x_pdoi_line.category_id) || ', ' ||
2343 'item_description: ' || substrb(x_pdoi_line.item_description, 1, 240) || ', ';
2344
2345 IF (x_pdoi_line.order_type_lookup_code = 'AMOUNT') THEN
2346 x_progress := x_progress || 'unit_of_measure: ' || null || ', ' ||
2347 'quantity: ' || x_quantity || ', ' ||
2348 'unit_price: ' || 1 || ',';
2349 ELSE
2350 x_progress := x_progress || 'unit_of_measure: ' || x_pdoi_line.unit_of_measure || ' ' ||
2351 'quantity: ' || x_quantity || ', ' ||
2352 'unit_price: ' || to_char(x_price) || ', ';
2353 END IF;
2354
2355 x_progress := x_progress ||
2356 'min_releaes_amount: ' || to_char(x_pdoi_line.po_min_rel_amount) || ', ' ||
2357 'ship_to_location_id: ' || to_char(x_pdoi_line.ship_to_location_id) ||', ' ||
2358 'need_by_start_date: ' || x_pdoi_line.need_by_start_date || ', ' ||
2359 'promised_date: ' || x_pdoi_line.promised_date || ', ' ||
2360 'last_update_by: ' || to_char(p_user_id) || ', ' ||
2361 'created_by: ' || to_char(p_user_id) || ', ' ||
2362 'auction_header_id: ' || to_char(x_pdoi_header.auction_header_id) || ', ' ||
2363 'document_number: ' || x_pdoi_header.document_number || ', ' ||
2364 'auction_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
2365 'bid_number: ' || to_char(x_pdoi_header.bid_number) || ', ' ||
2366 'bid_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
2367 'orig_from_req_flag: ' || 'N' || ', ';
2368
2369
2370 log_message(x_progress);
2371
2372
2373 END IF; --} -- End of excess award
2374
2375
2376 END LOOP; --} -- stop loop over reqlineLevelInfo
2377
2378 close reqlineLevelInfo;
2379
2380 /* rrkulkar-large-auction-support changes */
2381 --------------------------------------------------------------------------------------------------------------
2382 --BATCHING FOR OUTCOME = STANDARD PURCHASE ORDER : STARTS HERE
2383 --------------------------------------------------------------------------------------------------------------
2384
2385 --get the number of rows to be copied
2386 select nvl(max(line_number),0)
2387 into l_max_line_number
2388 from pon_bid_item_prices
2389 where bid_number = x_pdoi_header.bid_number;
2390
2391 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
2392 l_commit_flag := FALSE;
2393
2394 l_batch_start := 1;
2395
2396 IF (l_max_line_number <l_batch_size) THEN
2397 l_batch_end := l_max_line_number;
2398 ELSE
2399 l_batch_end := l_batch_size;
2400 END IF;
2401
2402 log_message('spo batching start: l_batch_size=' || l_batch_size || ' l_batch_start=' || l_batch_start || ' l_batch_end=' || l_batch_end);
2403
2404
2405 WHILE (l_batch_start <= l_max_line_number) LOOP --{ main-batching-loop--spo
2406
2407
2408 log_message('spo batching start: l_batch_size=' || l_batch_size || ' l_batch_start=' || l_batch_start || ' l_batch_end=' || l_batch_end);
2409
2410
2411 /* case-1: Lines with no backing reqs*/
2412
2413 /*
2414 rrkulkar-large-auction-support : study
2415
2416 need to verify the resolution of the following columns
2417 1. quantity
2418 2. unit_price
2419
2420 need to find out about x_interface_line_id :- is it used for debugging purposes only?
2421 */
2422
2423 INSERT into PO_LINES_INTERFACE (
2424 interface_header_id,
2425 interface_line_id,
2426 requisition_line_id,
2427 line_type_id,
2428 item_id,
2429 item_revision,
2430 category_id,
2431 item_description,
2432 unit_of_measure,
2433 quantity,
2434 unit_price,
2435 min_release_amount,
2436 ship_to_location_id,
2437 need_by_date,
2438 promised_date,
2439 last_updated_by,
2440 last_update_date,
2441 created_by,
2442 creation_date,
2443 auction_header_id,
2444 auction_display_number,
2445 auction_line_number,
2446 bid_number,
2447 bid_line_number,
2448 orig_from_req_flag,
2449 job_id,
2450 amount
2451 , advance_amount
2452 , recoupment_rate
2453 , progress_payment_rate
2454 , retainage_rate
2455 , max_retainage_amount
2456 , Line_loc_populated_flag
2457
2458 )
2459 SELECT
2460 x_interface_header_id, -- interface_header_id
2461 po_lines_interface_s.nextval, -- interface_line_id
2462 NULL, -- requisition_line_id
2463 paip.line_type_id, -- line_type_id
2464 paip.item_id, -- item_id
2465 paip.item_revision, -- item_revision
2466 paip.category_id, -- category_id
2467 substrb(paip.item_description, 1, 240), -- item_description
2468 decode(paip.order_type_lookup_code, 'AMOUNT', null, mtluom.unit_of_measure), -- unit_of_measure
2469 decode(paip.order_type_lookup_code, 'RATE', TO_NUMBER(null),
2470 'FIXED PRICE', TO_NUMBER(null),
2471 'AMOUNT', pbip.bid_currency_unit_price,
2472 pbip.award_quantity), -- QUANTITY
2473 decode(paip.order_type_lookup_code,'AMOUNT', 1,
2474 'FIXED PRICE', TO_NUMBER(NULL)
2475 ,nvl2( pbip.award_shipment_number,pbs.bid_currency_unit_price
2476 ,pbip.bid_currency_unit_price)), --unit_price
2477 nvl(pbip.po_bid_min_rel_amount, round(paip.po_min_rel_amount* pbh.rate,fc.precision)), -- min_release_amount
2478 paip.ship_to_location_id, -- ship_to_location_id
2479 paip.need_by_start_date, -- need_by_start_date
2480 pbip.promised_date, -- promised_date
2481 p_user_id, -- last_update_by
2482 sysdate, -- last_update_date
2483 p_user_id, -- created_by
2484 sysdate, -- creation_date
2485 x_pdoi_header.auction_header_id, -- auction_header_id
2486 x_pdoi_header.document_number, -- document_number
2487 paip.line_number, -- auction_line_number,
2488 pbip.bid_number, -- bid_number
2489 pbip.line_number, -- bid_line_number
2490 'N', -- orig_from_req_flag
2491 paip.job_id, -- job_id
2492 decode(paip.order_type_lookup_code,'FIXED PRICE', pbip.bid_currency_unit_price, TO_NUMBER(NULL)) -- amount
2493 , decode(pbip.bid_curr_advance_amount,0,null,pbip.bid_curr_advance_amount)
2494 , pbip.recoupment_rate_percent
2495 , pbip.progress_pymt_rate_percent
2496 , pbip.retainage_rate_percent
2497 , pbip.Bid_curr_max_retainage_amt
2498 , decode(pbip.has_bid_payments_flag, 'Y', decode((select 1 from dual where exists
2499 ( select 1 from pon_bid_payments_shipments where
2500 auction_header_id = pbip.auction_header_id and
2501 bid_number= pbip.bid_number and bid_line_number=pbip.line_number
2502 and bid_currency_price <> 0
2503 )
2504 ),
2505 1,'Y','N')
2506 ,'N') --Line_loc_populated
2507
2508 FROM
2509 pon_auction_item_prices_all paip,
2510 pon_bid_item_prices pbip,
2511 mtl_units_of_measure mtluom,
2512 pon_bid_headers pbh,
2513 fnd_currencies fc,
2514 pon_bid_shipments pbs
2515 WHERE
2516 pbip.bid_number = p_bid_number and
2517 pbip.auction_header_id = p_auction_header_id and
2518 nvl(pbip.award_status, 'NO') = 'AWARDED' and
2519 paip.auction_header_id = pbip.auction_header_id and
2520 paip.line_number = pbip.line_number and
2521 paip.group_type NOT IN ('GROUP','LOT_LINE') and
2522 paip.uom_code = mtluom.uom_code (+) and
2523 pbh.bid_number = pbip.bid_number and
2524 fc.currency_code = pbh.bid_currency_code and
2525 nvl(paip.line_origination_code, 'NO') <> 'REQUISITION' and
2526 pbip.line_number >= l_batch_start and
2527 pbip.line_number <= l_batch_end and
2528 pbs.bid_number(+) = pbip.bid_number and
2529 pbs.line_number(+) = pbip.line_number and
2530 pbs.shipment_number(+) = pbip.award_shipment_number;
2531
2532
2533 x_progress := '35.1: CREATE_PO_STRUCTURE: STANDARD CASE: END OF BULK INSERT';
2534
2535 log_message(x_progress);
2536
2537 x_progress := '35.1.1: CREATE_PAYMENTS: STANDARD CASE: CHECK IF COMPLEX WORK';
2538
2539 log_message(x_progress);
2540
2541 -- Insert all Payments for all lines in one go, if any and complex work
2542 IF (x_pdoi_header.progress_payment_type IN ('ACTUAL','FINANCE')) THEN
2543 x_progress := '35.1.5: CREATE_PAYMENTS: STANDARD CASE: IT IS COMPLEX WORK';
2544
2545 log_message(x_progress);
2546
2547 INSERT INTO po_line_locations_interface (
2548 interface_header_id,
2549 interface_line_id,
2550 interface_line_location_id,
2551 payment_type,
2552 shipment_num,
2553 ship_to_location_id,
2554 need_by_date,
2555 promised_date,
2556 quantity,
2557 unit_of_measure,
2558 price_override,
2559 amount,
2560 description,
2561 work_approver_id,
2562 project_id,
2563 task_id,
2564 award_id,
2565 expenditure_type,
2566 expenditure_organization_id,
2567 expenditure_item_date,
2568 auction_payment_id,
2569 bid_payment_id,
2570 last_update_date,
2571 last_updated_by,
2572 creation_date,
2573 created_by )
2574
2575 SELECT
2576 x_interface_header_id, -- interface_header_id
2577 pli.interface_line_id, -- interface_line_id
2578 po_line_locations_interface_s.NEXTVAL,
2579 -- interface_line_location_id
2580 bpys.payment_type_code, -- shipment_type
2581 bpys.payment_display_number, -- shipment_num
2582 nvl(apys.ship_to_location_id,
2583 paip. ship_to_location_id), -- ship_to_location_id
2584 decode(x_pdoi_header.supplier_enterable_pymt_flag,
2585 'Y', paip.need_by_date , apys.need_by_date), -- need_by_date
2586 bpys.promised_date, -- promised_date
2587 nvl(bpys.quantity, decode(paip.order_type_lookup_code,
2588 'QUANTITY',
2589 pli.quantity, null
2590 )
2591 ) , -- quantity. Populate this for RATE and Qty Milestone
2592
2593 nvl2(bpys.uom_code, mtluom.unit_of_measure,
2594 decode(paip.order_type_lookup_code, 'QUANTITY',
2595 (select unit_of_measure from
2596 mtl_units_of_measure where uom_code=
2597 paip.uom_code),
2598 null
2599 )
2600 ) , -- unit_of_measure.Populate this for RATE and Qty Milestone
2601
2602 nvl2(bpys.quantity, bpys.bid_currency_price,
2603 decode(paip.order_type_lookup_code,'QUANTITY',
2604 bpys.bid_currency_price, null)
2605 ), -- price_override. Populate this for RATE and Qty Milestone
2606
2607 nvl2(bpys.quantity, null,
2608 decode(paip.order_type_lookup_code, 'QUANTITY',
2609 null, bpys.bid_currency_price)
2610 ),-- amount.Populate this for LUMPSUM and Fixed Price Milestone
2611
2612 bpys.payment_description, -- item_description
2613 decode(x_pdoi_header.supplier_enterable_pymt_flag,
2614 'Y',paip.work_approver_user_id, apys.work_approver_user_id),
2615 -- Work_approver_user_id
2616
2617 decode(x_pdoi_header.supplier_enterable_pymt_flag,
2618 'Y', paip. project_id , apys.project_id), -- project_id
2619 decode(x_pdoi_header.supplier_enterable_pymt_flag,
2620 'Y', paip. project_task_id , apys.project_task_id),
2621 -- project_task_id
2622 decode(x_pdoi_header.supplier_enterable_pymt_flag,
2623 'Y', paip.project_award_id,apys.project_award_id),
2624 -- project_award_id
2625 decode(x_pdoi_header.supplier_enterable_pymt_flag,
2626 'Y', paip.project_expenditure_type,
2627 apys.project_expenditure_type),
2628 -- project_expenditure_type
2629 decode(x_pdoi_header.supplier_enterable_pymt_flag,
2630 'Y', paip. project_exp_organization_id,
2631 apys.project_exp_organization_id),
2632 -- project_exp_organization_id
2633 decode(x_pdoi_header.supplier_enterable_pymt_flag, 'Y',
2634 paip. project_expenditure_item_date,
2635 apys.project_expenditure_item_date),
2636 -- project_expenditure_date
2637 bpys.auction_payment_id , -- auction_payment_id
2638 bpys.bid_payment_id, -- bid_payment_id
2639 sysdate, -- last_update_date
2640 x_user_id, -- last_updated_by
2641 sysdate, -- creation_date
2642 x_user_id -- created_by
2643
2644 FROM pon_auction_item_prices_all paip,
2645 pon_bid_item_prices pbip,
2646 pon_bid_payments_shipments bpys,
2647 pon_auc_payments_shipments apys,
2648 po_lines_interface pli,
2649 mtl_units_of_measure mtluom
2650 WHERE pbip.bid_number = p_bid_number and
2651 pbip.auction_header_id = p_auction_header_id and
2652 nvl(pbip.award_status, 'NO') = 'AWARDED' and
2653 paip.auction_header_id = pbip.auction_header_id and
2654 paip.line_number = pbip.line_number and
2655 bpys.bid_number = pbip.bid_number and
2656 bpys.bid_line_number = pbip.line_number and
2657 pli.interface_header_id = x_interface_header_id and
2658 pli.auction_line_number = paip.line_number and
2659 pli.auction_header_id = paip.auction_header_id and
2660 bpys.auction_payment_id = apys.payment_id (+) and
2661 bpys.uom_code = mtluom.uom_code (+) and
2662 nvl(bpys.bid_currency_price,0) <> 0 and
2663 pbip.line_number >= l_batch_start and
2664 pbip.line_number <= l_batch_end;
2665
2666 x_progress := '35.1.10: CREATE_PAYMENTS: STANDARD CASE: END INSERTING PAYMENTS';
2667
2668 log_message(x_progress);
2669 END IF;-- if complex work
2670
2671
2672 x_progress := '35.2: CREATE_PO_STRUCTURE: STANDARD CASE: BATCH FROM '
2673 || l_batch_start ||' TO '||l_batch_end ||' (inclusive)';
2674 log_message(x_progress);
2675 l_batch_start := l_batch_end + 1;
2676
2677 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
2678 l_batch_end := l_max_line_number;
2679 l_commit_flag := FALSE;
2680 ELSE
2681 l_batch_end := l_batch_end + l_batch_size;
2682 l_commit_flag := TRUE;
2683 END IF;
2684
2685 /*
2686 Note from ATG-WF website :-
2687
2688 You CANNOT commit inside a PL/SQL procedure which is called by the workflow engine.
2689 If you issue a commit you are committing the workflow state as well as your application
2690 state. If you do commit and your pl/sql function fails subsequently the workflow engine
2691 will not be able to rollback to a consistent state.
2692 */
2693
2694 IF(l_commit_flag = TRUE) THEN
2695 COMMIT;
2696 x_progress := '35.3: CREATE_PO_STRUCTURE: STANDARD CASE: BATCH-COMMIT SUCCESSFUL ';
2697 log_message(x_progress);
2698 END IF;
2699
2700 END LOOP; --} --end-loop- batching-SPO
2701
2702 --------------------------------------------------------------------------------------------------------------
2703 --BATCHING FOR OUTCOME = STANDARD PURCHASE ORDER : ENDS HERE
2704 --------------------------------------------------------------------------------------------------------------
2705
2706 END IF; -- End of Standard
2707
2708
2709 /* Blanket Agreement case: will do bulk insert from one table to another */
2710
2711 IF (x_pdoi_header.contract_type = 'BLANKET') THEN --{ -- if outcome is BPA
2712
2713 /* rrkulkar-large-auction-support changes */
2714 --------------------------------------------------------------------------------------------------------------
2715 --BATCHING FOR OUTCOME = BLANKET PURCHASE AGREEMENT: STARTS HERE
2716 --------------------------------------------------------------------------------------------------------------
2717
2718 --get the number of rows to be copied
2719 select nvl(max(line_number),0)
2720 into l_max_line_number
2721 from pon_bid_item_prices
2722 where bid_number = x_pdoi_header.bid_number;
2723
2724 -- always reset -> although it is not possible that both bpa+spo cases are satisfied :)
2725 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
2726 l_commit_flag := FALSE;
2727 l_rows_processed := 0;
2728
2729 l_batch_start := 1;
2730
2731 IF (l_max_line_number <l_batch_size) THEN
2732 l_batch_end := l_max_line_number;
2733 ELSE
2734 l_batch_end := l_batch_size;
2735 END IF;
2736
2737 log_message('blanket batching start: l_batch_size=' || l_batch_size || ' l_batch_start=' || l_batch_start || ' l_batch_end=' || l_batch_end);
2738
2739 WHILE (l_batch_start <= l_max_line_number) LOOP --{ main-batching-loop--spo
2740
2741 log_message('blanket batching loop: l_batch_size=' || l_batch_size || ' l_batch_start=' || l_batch_start || ' l_batch_end=' || l_batch_end);
2742
2743
2744 INSERT into PO_LINES_INTERFACE (
2745 interface_header_id,
2746 interface_line_id,
2747 requisition_line_id,
2748 line_type_id,
2749 line_num,
2750 item_id,
2751 item_revision,
2752 category_id,
2753 ip_category_id,
2754 item_description,
2755 unit_of_measure,
2756 price_break_lookup_code,
2757 quantity,
2758 committed_amount,
2759 unit_price,
2760 min_release_amount,
2761 ship_to_location_id,
2762 need_by_date,
2763 promised_date,
2764 last_updated_by,
2765 last_update_date,
2766 created_by,
2767 creation_date,
2768 auction_header_id,
2769 auction_display_number,
2770 auction_line_number,
2771 bid_number,
2772 bid_line_number,
2773 orig_from_req_flag,
2774 job_id,
2775 amount
2776 )
2777 SELECT
2778 x_interface_header_id, -- interface_header_id
2779 po_lines_interface_s.nextval, -- interface_line_id
2780 NULL, -- requisition_line_id
2781 paip.line_type_id, -- line_type_id
2782 l_rows_processed + rownum, -- line num
2783 paip.item_id,
2784 -- item_id
2785 paip.item_revision,
2786 -- item_revision
2787 paip.category_id,
2788 -- category_id
2789 nvl(paip.ip_category_id, -2),
2790 -- ip category id
2791 substrb(paip.item_description, 1, 240),
2792 -- item_description
2793 decode(paip.order_type_lookup_code, 'AMOUNT', null, mtluom.unit_of_measure),
2794 -- unit_of_measure
2795 decode(pbip.price_break_type, 'NONE', null, 'NON-CUMULATIVE', 'NON CUMULATIVE', pbip.price_break_type),
2796 -- price_break_type
2797 decode(paip.order_type_lookup_code,
2798 'AMOUNT', NULL,
2799 'RATE', NULL,
2800 'FIXED PRICE', NULL,
2801 pbip.award_quantity), -- quantity
2802 decode(paip.order_type_lookup_code,
2803 'AMOUNT', pbip.bid_currency_unit_price,
2804 'RATE', round(paip.po_agreed_amount*pbh.rate, fc.precision),
2805 'FIXED PRICE', round(paip.po_agreed_amount*pbh.rate, fc.precision),
2806 null), -- committed_amount
2807 decode(paip.order_type_lookup_code,
2808 'AMOUNT', 1,
2809 'FIXED PRICE', null,
2810 nvl2( pbip.award_shipment_number,pbs.bid_currency_unit_price
2811 ,pbip.bid_currency_unit_price)), --unit_price
2812 nvl(pbip.po_bid_min_rel_amount, round(paip.po_min_rel_amount* pbh.rate, fc.precision)), -- min_release_amount
2813 paip.ship_to_location_id, -- ship_to_location_id
2814 paip.need_by_start_date, -- need_by_start_date
2815 pbip.promised_date, -- promised_date
2816 p_user_id, -- last_update_by
2817 sysdate, -- last_update_date
2818 p_user_id, -- created_by
2819 sysdate, -- creation_date
2820 x_pdoi_header.auction_header_id, -- auction_header_id
2821 x_pdoi_header.document_number, -- document_number
2822 paip.line_number, -- auction_line_number,
2823 x_pdoi_header.bid_number, -- bid_number
2824 paip.line_number, -- bid_line_number
2825 decode(paip.line_origination_code, 'REQUISITION', 'Y', 'N'), -- orig_from_req_flag
2826 paip.job_id, -- job_id
2827 decode(paip.order_type_lookup_code,
2828 'FIXED PRICE', round(pbip.bid_currency_unit_price, fc.precision),
2829 null) -- amount
2830 FROM pon_auction_item_prices_all paip,
2831 pon_bid_item_prices pbip,
2832 mtl_units_of_measure mtluom,
2833 pon_bid_headers pbh,
2834 fnd_currencies fc,
2835 pon_bid_shipments pbs
2836 WHERE pbip.bid_number = p_bid_number and
2837 pbip.auction_header_id = p_auction_header_id and
2838 nvl(pbip.award_status, 'NO') = 'AWARDED' and
2839 paip.auction_header_id = pbip.auction_header_id and
2840 paip.line_number = pbip.line_number and
2841 paip.group_type NOT IN ('GROUP','LOT_LINE') and
2842 paip.uom_code = mtluom.uom_code (+) and
2843 pbh.bid_number = pbip.bid_number and
2844 fc.currency_code = pbh.bid_currency_code and
2845 pbip.line_number >= l_batch_start and
2846 pbip.line_number <= l_batch_end and
2847 pbs.bid_number(+) = pbip.bid_number and
2848 pbs.line_number(+) = pbip.line_number and
2849 pbs.shipment_number(+) = pbip.award_shipment_number;
2850
2851 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2852
2853 log_message('Inserting iP Descriptors for lines: ' || l_batch_start || ' to ' || l_batch_end);
2854
2855
2856 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);
2857
2858 log_message('inserting blanket price break information');
2859
2860 -- Insert Price Break information
2861 INSERT INTO po_lines_interface (
2862 interface_header_id,
2863 interface_line_id,
2864 shipment_type,
2865 line_type_id,
2866 item_id,
2867 item_revision,
2868 quantity,
2869 price_break_lookup_code,
2870 unit_price,
2871 price_discount,
2872 ship_to_organization_id,
2873 ship_to_location_id,
2874 last_update_date,
2875 last_updated_by,
2876 creation_date,
2877 created_by,
2878 line_num,
2879 shipment_num,
2880 effective_date,
2881 expiration_date,
2882 auction_header_id,
2883 auction_line_number)
2884 SELECT
2885 x_interface_header_id, -- interface_header_id
2886 po_lines_interface_s.NEXTVAL, -- interface_line_id
2887 pbs.shipment_type, -- shipment_type
2888 paip.line_type_id, -- line_type_id
2889 paip.item_id, -- item_id
2890 paip.item_revision, -- item_revision
2891 pbs.quantity, -- quantity
2892 decode(pbip.price_break_type, 'NONE', null, 'NON-CUMULATIVE', 'NON CUMULATIVE', pbip.price_break_type),
2893 -- price_break_type
2894 pbs.bid_currency_unit_price, -- unit_price
2895 pbs.price_discount, -- price_discount
2896 pbs.ship_to_organization_id, -- ship_to_organization_id
2897 pbs.ship_to_location_id, -- ship_to_location_id
2898 sysdate, -- last_update_date
2899 p_user_id, -- last_updated_by
2900 sysdate, -- creation_date
2901 p_user_id, -- created_by
2902 pli.line_num, -- line num
2903 pbs.shipment_number, -- shipment_number
2904 pbs.effective_start_date, -- effective_date
2905 pbs.effective_end_date, -- expiration_date
2906 pbs.auction_header_id, -- auction_header_id
2907 pbs.auction_line_number -- auction_line_number
2908 FROM pon_auction_item_prices_all paip,
2909 pon_bid_item_prices pbip,
2910 pon_bid_shipments pbs,
2911 po_lines_interface pli
2912 WHERE pbip.bid_number = p_bid_number and
2913 pbip.auction_header_id = p_auction_header_id and
2914 nvl(pbip.award_status, 'NO') = 'AWARDED' and
2915 paip.auction_header_id = pbip.auction_header_id and
2916 paip.line_number = pbip.line_number and
2917 pbs.bid_number = p_bid_number and
2918 pli.interface_header_id = x_interface_header_id and
2919 pli.auction_line_number = paip.line_number and
2920 pli.auction_header_id = paip.auction_header_id and
2921 pbs.shipment_type = 'PRICE BREAK' and
2922 pbip.line_number = pbs.line_number and
2923 pbip.line_number >= l_batch_start and
2924 pbip.line_number <= l_batch_end ;
2925
2926
2927 -- Insert Line Price Differentials
2928 INSERT INTO po_price_diff_interface
2929 (price_diff_interface_id,
2930 price_differential_num,
2931 entity_type,
2932 interface_header_id,
2933 interface_line_id,
2934 price_type,
2935 enabled_flag,
2936 min_multiplier,
2937 max_multiplier,
2938 last_update_date,
2939 last_updated_by,
2940 creation_date,
2941 created_by,
2942 last_update_login)
2943 SELECT
2944 po_price_diff_interface_s.NEXTVAL, -- price_diff_interface_id
2945 ppd.price_differential_number, -- price_differential_num
2946 'BLANKET LINE', -- entity_type
2947 x_interface_header_id, -- interface_line_id
2948 pli.interface_line_id, -- interface_line_id
2949 ppd.price_type, -- price_type
2950 'Y', -- enabled_flag
2951 ppd.multiplier, -- min_multiplier
2952 pbpd.multiplier, -- max_multiplier
2953 sysdate, -- last_update_date
2954 p_user_id, -- last_updated_by
2955 sysdate, -- creation_date,
2956 p_user_id, -- created_by
2957 fnd_global.login_id -- last_update_login
2958 FROM pon_price_differentials ppd,
2959 pon_bid_item_prices pbip,
2960 pon_bid_price_differentials pbpd,
2961 pon_auction_headers_all pah,
2962 po_lines_interface pli
2963 WHERE pbip.bid_number = p_bid_number
2964 AND nvl(pbip.award_status, 'NO') = 'AWARDED'
2965 AND pbip.auction_header_id = ppd.auction_header_id
2966 AND pbip.line_number = ppd.line_number
2967 AND ppd.shipment_number = -1
2968 AND p_bid_number = pbpd.bid_number(+)
2969 AND ppd.line_number = pbpd.line_number(+)
2970 AND ppd.shipment_number = pbpd.shipment_number(+)
2971 AND ppd.price_differential_number = pbpd.price_differential_number(+)
2972 AND pah.auction_header_id = ppd.auction_header_id
2973 AND pli.interface_header_id = x_interface_header_id
2974 AND pli.auction_line_number = ppd.line_number
2975 AND pli.auction_header_id = ppd.auction_header_id
2976 AND pli.shipment_num IS NULL
2977 AND pbip.line_number >= l_batch_start
2978 AND pbip.line_number <= l_batch_end;
2979
2980 -- Insert Price Break Price Differentials
2981 INSERT INTO po_price_diff_interface
2982 (price_diff_interface_id,
2983 price_differential_num,
2984 entity_type,
2985 interface_header_id,
2986 interface_line_id,
2987 price_type,
2988 enabled_flag,
2989 min_multiplier,
2990 max_multiplier,
2991 last_update_date,
2992 last_updated_by,
2993 creation_date,
2994 created_by,
2995 last_update_login)
2996 SELECT
2997 po_price_diff_interface_s.NEXTVAL, -- price_diff_interface_id
2998 ppd.price_differential_number, -- price_differential_num
2999 'PRICE BREAK', -- entity_type
3000 x_interface_header_id, -- interface_header_id
3001 pli.interface_line_id, -- interface_line_id
3002 ppd.price_type, -- price_type
3003 'Y', -- enabled_flag
3004 ppd.multiplier, -- min_multiplier
3005 pbpd.multiplier, -- max_multiplier
3006 sysdate, -- last_update_date
3007 p_user_id, -- last_updated_by
3008 sysdate, -- creation_date,
3009 p_user_id, -- created_by
3010 fnd_global.login_id -- last_update_login
3011 FROM pon_price_differentials ppd,
3012 pon_bid_item_prices pbip,
3013 (select pbpd.bid_number, pbpd.line_number,
3014 pbpd.shipment_number, pbs.auction_shipment_number,
3015 pbpd.price_differential_number, pbpd.price_type,
3016 pbpd.multiplier, pbpd.auction_header_id
3017 from pon_bid_price_differentials pbpd, pon_bid_shipments pbs
3018 where pbs.bid_number = p_bid_number
3019 and pbs.line_number = pbpd.line_number
3020 and pbs.shipment_number = pbpd.shipment_number) pbpd,
3021 pon_bid_shipments pbs,
3022 pon_auction_headers_all pah,
3023 po_lines_interface pli
3024 WHERE pbip.bid_number = p_bid_number
3025 AND nvl(pbip.award_status, 'NO') = 'AWARDED'
3026 AND pbip.bid_number = pbs.bid_number
3027 AND pbip.line_number = pbs.line_number
3028 AND pbs.auction_header_id = ppd.auction_header_id
3029 AND pbs.line_number = ppd.line_number
3030 AND pbs.auction_shipment_number = ppd.shipment_number
3031 AND pah.auction_header_id = ppd.auction_header_id
3032 AND ppd.line_number = pbpd.line_number(+)
3033 AND ppd.shipment_number = pbpd.auction_shipment_number(+)
3034 AND ppd.price_differential_number = pbpd.price_differential_number(+)
3035 AND p_bid_number = pbpd.bid_number(+)
3036 AND pli.interface_header_id = x_interface_header_id
3037 AND pli.auction_line_number = pbs.line_number
3038 AND pli.auction_header_id = pbs.auction_header_id
3039 AND pli.shipment_num = pbs.shipment_number
3040 AND pbip.line_number >= l_batch_start
3041 AND pbip.line_number <= l_batch_end;
3042
3043 /*
3044 -- DEBUG CODE
3045 -- ALWAYS COMMENTED OUT
3046
3047 INSERT INTO po_lines_interface_debug
3048 (SELECT * FROM po_lines_interface WHERE interface_header_id = x_interface_header_id);
3049
3050 INSERT INTO po_price_diff_interface_debug
3051 (SELECT * FROM po_price_diff_interface WHERE interface_header_id = x_interface_header_id);
3052 */
3053
3054 x_progress := '38: CREATE_PO_STRUCTURE: BLANKET CASE: END OF BULK INSERT';
3055
3056 log_message(x_progress);
3057
3058 x_progress := '39: CREATE_PO_STRUCTURE: BLANKET CASE: BATCH FROM '
3059 || l_batch_start ||' TO '|| l_batch_end ||' (inclusive)';
3060
3061 log_message(x_progress);
3062
3063 l_batch_start := l_batch_end + 1;
3064
3065 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
3066 l_batch_end := l_max_line_number;
3067 l_commit_flag := FALSE;
3068 ELSE
3069 l_batch_end := l_batch_end + l_batch_size;
3070 l_commit_flag := TRUE;
3071 END IF;
3072
3073
3074 /*
3075 Note from ATG-WF website :-
3076
3077 You CANNOT commit inside a PL/SQL procedure which is called by the workflow engine.
3078 If you issue a commit you are committing the workflow state as well as your application
3079 state. If you do commit and your pl/sql function fails subsequently the workflow engine
3080 will not be able to rollback to a consistent state.
3081 */
3082
3083 IF(l_commit_flag = TRUE) THEN
3084 COMMIT;
3085 x_progress := '40: CREATE_PO_STRUCTURE: BLANKET CASE: BATCH-COMMIT SUCCESSFUL ';
3086 log_message(x_progress);
3087 END IF;
3088
3089
3090 END LOOP; --} -- end-main-batching-loop
3091 --------------------------------------------------------------------------------------------------------------
3092 --BATCHING FOR OUTCOME = BLANKET PURCHASE AGREEMENT: ENDS HERE
3093 --------------------------------------------------------------------------------------------------------------
3094
3095
3096 END IF; --} --if outcome is BPA
3097
3098 /* setting out parameters */
3099
3100 p_interface_header_id := x_interface_header_id;
3101 p_pdoi_header := x_pdoi_header;
3102
3103
3104 EXCEPTION
3105
3106 when others then
3107
3108 IF (headerLevelInfo%ISOPEN) THEN
3109 close headerLevelInfo;
3110 END IF;
3111
3112 IF (reqlineLevelInfo%ISOPEN) THEN
3113 close reqlineLevelInfo;
3114 END IF;
3115
3116 IF (reqBackingBidItem%ISOPEN) THEN
3117 close reqBackingBidItem;
3118 END IF;
3119
3120 IF (sumOfReqAllocQuantities%ISOPEN) THEN
3121 close sumOfReqAllocQuantities;
3122 END IF;
3123
3124 p_error_code := 'FAILURE';
3125 p_error_message := SUBSTRB(SQLERRM, 1, 500);
3126
3127 log_message('CREATE_PO_STRUCTURE : FATAL_ERROR : ' || p_error_code || ' ' || p_error_message);
3128
3129
3130 END CREATE_PO_STRUCTURE;
3131
3132
3133 PROCEDURE INSERT_IP_DESCRIPTORS(p_auction_header_id IN NUMBER,
3134 p_bid_number IN NUMBER,
3135 p_interface_header_id IN NUMBER,
3136 p_user_id IN NUMBER,
3137 p_login_id IN NUMBER,
3138 p_batch_start IN NUMBER,
3139 p_batch_end IN NUMBER) IS
3140
3141
3142 l_cursorName NUMBER;
3143 l_cursorResult NUMBER;
3144
3145 TYPE NUMBER_LIST is TABLE of NUMBER
3146 INDEX BY BINARY_INTEGER;
3147 TYPE VARCHAR_LIST is TABLE of VARCHAR2(32767)
3148 INDEX BY BINARY_INTEGER;
3149
3150
3151 -- holds the values to be inserted into the interface tables
3152 l_numValues NUMBER_LIST; -- holds descriptor values of number type
3153 l_txtValues VARCHAR_LIST; -- holds descriptor values of text type
3154 l_transTxtValues VARCHAR_LIST; -- holds descriptor values of translateable text type
3155
3156 -- empty tables for clearing/resetting above datastructures
3157 l_emptyNumValues NUMBER_LIST;
3158 l_emptyTxtValues VARCHAR_LIST;
3159 l_emptyTransTxtValues VARCHAR_LIST;
3160
3161 -- keeps track of the size of the tables
3162 l_numValuesCount NUMBER;
3163 l_txtValuesCount NUMBER;
3164 l_transTxtValuesCount NUMBER;
3165
3166 l_cur_interface_line_id NUMBER;
3167 l_cur_attr_values_id NUMBER;
3168 l_cur_attr_values_tlp_id NUMBER;
3169 l_cur_item_description pon_auction_item_prices_all.item_description%TYPE;
3170 l_cur_ip_category_id NUMBER;
3171 l_cur_item_id NUMBER;
3172 l_cur_org_id NUMBER;
3173 l_language_code pon_auction_headers_all.language_code%TYPE;
3174
3175 l_po_attr_values_stmt VARCHAR2(32767);
3176 l_po_attr_values_tlp_stmt VARCHAR2(32767);
3177
3178 l_po_attr_values_cols VARCHAR2(32767);
3179 l_po_attr_values_vals VARCHAR2(32767);
3180
3181 l_po_attr_values_tlp_cols VARCHAR2(32767);
3182 l_po_attr_values_tlp_vals VARCHAR2(32767);
3183
3184
3185 CURSOR descriptors IS
3186 SELECT pbip.line_number,
3187 pli.interface_line_id,
3188 paip.item_description,
3189 nvl(paip.ip_category_id, -2) ip_category_id,
3190 nvl(paip.item_id, -2) item_id,
3191 paip.org_id,
3192 decode(icx.type, 0, 'TXT', 1, 'NUM', 2, 'TRANS') datatype,
3193 icx.stored_in_table,
3194 icx.stored_in_column,
3195 pbav.value,
3196 paa.attribute_name
3197 FROM pon_bid_item_prices pbip,
3198 pon_auction_item_prices_all paip,
3199 po_lines_interface pli,
3200 pon_bid_attribute_values pbav,
3201 pon_auction_attributes paa,
3202 icx_cat_agreement_attrs_v icx
3203 WHERE pbip.auction_header_id = p_auction_header_id and
3204 pbip.bid_number = p_bid_number and
3205 nvl(pbip.award_status, 'NO') = 'AWARDED' and
3206 pbip.line_number >= p_batch_start and
3207 pbip.line_number <= p_batch_end and
3208 pbip.auction_header_id = paip.auction_header_id and
3209 pbip.line_number = paip.line_number and
3210 pli.interface_header_id = p_interface_header_id and
3211 pbip.auction_header_id = pli.auction_header_id and
3212 pbip.line_number = pli.auction_line_number and
3213 pbip.auction_header_id = pbav.auction_header_id (+) and
3214 pbip.bid_number = pbav.bid_number (+) and
3215 pbip.line_number = pbav.line_number (+) and
3216 pbav.auction_header_id = paa.auction_header_id (+) and
3217 pbav.line_number = paa.line_number (+) and
3218 pbav.sequence_number = paa.sequence_number (+) and
3219 paa.ip_category_id (+) is not null and
3220 paa.ip_category_id = icx.rt_category_id (+) and
3221 paa.ip_descriptor_id = icx.attribute_id (+) and
3222 icx.language (+) = userenv('LANG')
3223 ORDER BY interface_line_id asc, decode(datatype, 'NUM', 0, 'TXT', 1, 2) asc;
3224
3225 descriptor descriptors%ROWTYPE;
3226
3227 l_num_txt_offset NUMBER := 11;
3228 l_trans_txt_offset NUMBER := 13;
3229
3230 BEGIN
3231
3232 select language_code
3233 into l_language_code
3234 from pon_auction_headers_all
3235 where auction_header_id = p_auction_header_id;
3236
3237
3238 l_cursorName := DBMS_SQL.Open_Cursor;
3239 l_cur_interface_line_id := -9999;
3240
3241 OPEN descriptors;
3242 LOOP
3243
3244 FETCH descriptors INTO descriptor;
3245 IF (descriptors%NOTFOUND OR
3246 descriptor.interface_line_id <> l_cur_interface_line_id) THEN
3247
3248 -- process number and text descriptors
3249 IF (l_cur_interface_line_id <> -9999) THEN
3250
3251 l_po_attr_values_stmt :=
3252 'insert into po_attr_values_interface(' ||
3253 'interface_header_id, ' ||
3254 'interface_line_id, ' ||
3255 'interface_attr_values_id, ' ||
3256 'ip_category_id, ' ||
3257 'inventory_item_id, ' ||
3258 'org_id, ' ||
3259 'last_update_login, ' ||
3260 'last_updated_by, ' ||
3261 'last_update_date, ' ||
3262 'created_by, ' ||
3263 'creation_date' ||
3264 l_po_attr_values_cols ||
3265 ') values('||
3266 ':1, ' ||
3267 ':2, ' ||
3268 ':3, ' ||
3269 ':4, ' ||
3270 ':5, ' ||
3271 ':6, ' ||
3272 ':7, ' ||
3273 ':8, ' ||
3274 ':9, ' ||
3275 ':10, ' ||
3276 ':11' ||
3277 l_po_attr_values_vals ||
3278 ')';
3279
3280 log_message(l_po_attr_values_stmt);
3281
3282 DBMS_SQL.Parse(l_cursorName, l_po_attr_values_stmt, DBMS_SQL.NATIVE);
3283
3284 DBMS_SQL.Bind_Variable(l_cursorName, ':1', p_interface_header_id);
3285 DBMS_SQL.Bind_Variable(l_cursorName, ':2', l_cur_interface_line_id);
3286 DBMS_SQL.Bind_Variable(l_cursorName, ':3', l_cur_attr_values_id);
3287 DBMS_SQL.Bind_Variable(l_cursorName, ':4', l_cur_ip_category_id);
3288 DBMS_SQL.Bind_Variable(l_cursorName, ':5', l_cur_item_id);
3289 DBMS_SQL.Bind_Variable(l_cursorName, ':6', l_cur_org_id);
3290 DBMS_SQL.Bind_Variable(l_cursorName, ':7', p_login_id);
3291 DBMS_SQL.Bind_Variable(l_cursorName, ':8', p_user_id);
3292 DBMS_SQL.Bind_Variable(l_cursorName, ':9', sysdate);
3293 DBMS_SQL.Bind_Variable(l_cursorName, ':10', p_user_id);
3294 DBMS_SQL.Bind_Variable(l_cursorName, ':11', sysdate);
3295
3296 FOR i in 1 .. l_numValuesCount
3297 LOOP
3298 DBMS_SQL.Bind_Variable(l_cursorName, ':' || (i+l_num_txt_offset), l_numValues(i));
3299 END LOOP;
3300
3301 FOR i in 1 ..l_txtValuesCount
3302 LOOP
3303 DBMS_SQL.Bind_Variable(l_cursorName, ':' || (i+l_num_txt_offset+l_numValuesCount), l_txtValues(i));
3304 END LOOP;
3305
3306 l_cursorResult := DBMS_SQL.Execute(l_cursorName);
3307
3308 END IF;
3309
3310 -- process translateable text descriptors
3311 IF (l_cur_interface_line_id <> -9999) THEN
3312
3313 l_po_attr_values_tlp_stmt :=
3314 'insert into po_attr_values_tlp_interface(' ||
3315 'interface_header_id, ' ||
3316 'interface_line_id, ' ||
3317 'interface_attr_values_tlp_id, ' ||
3318 'ip_category_id, ' ||
3319 'inventory_item_id, ' ||
3320 'org_id, ' ||
3321 'language, ' ||
3322 'description, ' ||
3323 'last_update_login, ' ||
3324 'last_updated_by, ' ||
3325 'last_update_date, ' ||
3326 'created_by, ' ||
3327 'creation_date' ||
3328 l_po_attr_values_tlp_cols ||
3329 ') values('||
3330 ':1, ' ||
3331 ':2, ' ||
3332 ':3, ' ||
3333 ':4, ' ||
3334 ':5, ' ||
3335 ':6, ' ||
3336 ':7, ' ||
3337 ':8, ' ||
3338 ':9, ' ||
3339 ':10, ' ||
3340 ':11, ' ||
3341 ':12, ' ||
3342 ':13' ||
3343 l_po_attr_values_tlp_vals ||
3344 ')';
3345
3346 log_message(l_po_attr_values_tlp_stmt);
3347
3348 DBMS_SQL.Parse(l_cursorName, l_po_attr_values_tlp_stmt, DBMS_SQL.NATIVE);
3349
3350 DBMS_SQL.Bind_Variable(l_cursorName, ':1', p_interface_header_id);
3351 DBMS_SQL.Bind_Variable(l_cursorName, ':2', l_cur_interface_line_id);
3352 DBMS_SQL.Bind_Variable(l_cursorName, ':3', l_cur_attr_values_tlp_id);
3353 DBMS_SQL.Bind_Variable(l_cursorName, ':4', l_cur_ip_category_id);
3354 DBMS_SQL.Bind_Variable(l_cursorName, ':5', l_cur_item_id);
3355 DBMS_SQL.Bind_Variable(l_cursorName, ':6', l_cur_org_id);
3356 DBMS_SQL.Bind_Variable(l_cursorName, ':7', l_language_code);
3357 DBMS_SQL.Bind_Variable(l_cursorName, ':8', l_cur_item_description);
3358 DBMS_SQL.Bind_Variable(l_cursorName, ':9', p_login_id);
3359 DBMS_SQL.Bind_Variable(l_cursorName, ':10', p_user_id);
3360 DBMS_SQL.Bind_Variable(l_cursorName, ':11', sysdate);
3361 DBMS_SQL.Bind_Variable(l_cursorName, ':12', p_user_id);
3362 DBMS_SQL.Bind_Variable(l_cursorName, ':13', sysdate);
3363
3364 FOR i in 1 .. l_transTxtValuesCount
3365 LOOP
3366 DBMS_SQL.Bind_Variable(l_cursorName, ':' || (i+l_trans_txt_offset), l_transTxtValues(i));
3367 END LOOP;
3368
3369 l_cursorResult := DBMS_SQL.Execute(l_cursorName);
3370
3371 END IF;
3372
3373 EXIT WHEN descriptors%NOTFOUND;
3374
3375 -- initialize/reset variables on line change
3376
3377 l_cur_interface_line_id := descriptor.interface_line_id;
3378
3379 select po_attr_values_interface_s.nextval
3380 into l_cur_attr_values_id
3381 from dual;
3382
3383 select po_attr_values_tlp_interface_s.nextval
3384 into l_cur_attr_values_tlp_id
3385 from dual;
3386
3387 l_cur_item_description := descriptor.item_description;
3388 l_cur_ip_category_id := descriptor.ip_category_id;
3389 l_cur_item_id := descriptor.item_id;
3390 l_cur_org_id := descriptor.org_id;
3391
3392 l_po_attr_values_cols := '';
3393 l_po_attr_values_vals := '';
3394
3395 l_po_attr_values_tlp_cols := '';
3396 l_po_attr_values_tlp_vals := '';
3397
3398 l_numValues := l_emptyNumValues;
3399 l_txtValues := l_emptyTxtValues;
3400 l_transTxtValues := l_emptyTxtValues;
3401
3402 l_numValuesCount := 0;
3403 l_txtValuesCount := 0;
3404 l_transTxtValuesCount := 0;
3405
3406
3407 END IF;
3408
3409
3410 CASE descriptor.datatype
3411 WHEN 'NUM' THEN
3412 l_numValuesCount := l_numValuesCount + 1;
3413 l_numValues(l_numValuesCount) := to_number(descriptor.value);
3414 l_po_attr_values_cols := l_po_attr_values_cols || ', ' || descriptor.stored_in_column;
3415 l_po_attr_values_vals := l_po_attr_values_vals || ', ' || ':' || to_char(l_numValuesCount + l_num_txt_offset);
3416
3417 WHEN 'TXT' THEN
3418 l_txtValuesCount := l_txtValuesCount + 1;
3419 l_txtValues(l_txtValuesCount) := descriptor.value;
3420 l_po_attr_values_cols := l_po_attr_values_cols || ', ' || descriptor.stored_in_column;
3421 l_po_attr_values_vals := l_po_attr_values_vals || ', ' || ':' || to_char(l_txtValuesCount + l_num_txt_offset + l_numValuesCount);
3422
3423 WHEN 'TRANS' THEN
3424 l_transTxtValuesCount := l_transTxtValuesCount + 1;
3425 l_transTxtValues(l_transTxtValuesCount) := descriptor.value;
3426 l_po_attr_values_tlp_cols := l_po_attr_values_tlp_cols || ', ' || descriptor.stored_in_column;
3427 l_po_attr_values_tlp_vals := l_po_attr_values_tlp_vals || ', ' || ':' || to_char(l_transTxtValuesCount + l_trans_txt_offset);
3428 ELSE
3429 NULL;
3430 END CASE;
3431
3432
3433 END LOOP;
3434 CLOSE descriptors;
3435
3436 IF DBMS_SQL.IS_OPEN(l_cursorName) THEN
3437 DBMS_SQL.CLOSE_CURSOR(l_cursorName);
3438 END IF;
3439
3440
3441 END INSERT_IP_DESCRIPTORS;
3442
3443
3444 procedure GENERATE_POS(p_auction_header_id IN NUMBER, -- 1
3445 p_user_name IN VARCHAR2, -- 2
3446 p_user_id IN NUMBER, -- 3
3447 p_resultout OUT NOCOPY VARCHAR2) IS
3448
3449
3450
3451 --x_auction_header_id NUMBER;
3452 --x_user_name fnd_user.user_name%TYPE;
3453
3454 x_language_code VARCHAR2(4);
3455 x_round_number NUMBER;
3456 x_line_number NUMBER;
3457 x_bid_number NUMBER;
3458 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3459 x_po_header_id NUMBER;
3460 x_order_number pon_bid_headers.order_number%TYPE;
3461 x_interface_header_id NUMBER;
3462 x_last_update_date pon_auction_headers_all.last_update_date%TYPE;
3463 x_auction_start_date DATE;
3464 x_auction_end_date DATE;
3465 x_tp_time_zone VARCHAR2(80);
3466 x_tp_time_zone1 VARCHAR2(80);
3467 x_award_summary_url VARCHAR2(2000);
3468 x_alloc_summary_url VARCHAR2(2000);
3469 x_allocate_item_url VARCHAR2(2000);
3470 x_auction_org_name VARCHAR2(80);
3471
3472 x_doctype_id NUMBER;
3473 x_conterms_exist_flag pon_auction_headers_all.conterms_exist_flag%TYPE;
3474 x_contract_doc_name VARCHAR(20);
3475
3476 /* Error code can take on the following values:
3477
3478 1- Success
3479 2- Failure due to manual numbering (duplicates)
3480 3- System/Other Errors
3481 4- Auction Error (CREATE_PO_STRUCTURE throws an exception)
3482
3483 */
3484
3485 x_error_code NUMBER;
3486 x_return_status VARCHAR2(1);
3487 x_msg_count NUMBER;
3488 x_msg_data VARCHAR2(2000);
3489 x_failure_code varchar2(10);
3490 x_error_msg varchar2(1000);
3491 x_num_lines_processed NUMBER;
3492 x_pdoi_header PDOIheader;
3493 v_old_policy varchar2(1);
3494 v_old_org_id number;
3495 x_preview_date_notspec VARCHAR2(60);
3496 x_preview_date DATE;
3497
3498 /* returns all awarded bids where a PO was not created*/
3499
3500 /* rrkulkar-large-auction-support :
3501
3502 Changed this cursor to return active bids only,
3503
3504 Modified the old 'where' clause which was like this :-
3505
3506 .... and nvl(pbh.bid_status, 'NONE') not in ('ARCHIVED', 'DISQUALIFIED') and ...
3507 */
3508
3509 CURSOR awardedBids IS
3510 SELECT pbh.bid_number
3511 FROM pon_bid_headers pbh
3512 WHERE pbh.auction_header_id = p_auction_header_id and
3513 nvl(pbh.bid_status, 'NONE') = 'ACTIVE' and
3514 pbh.po_header_id is NULL and
3515 nvl(pbh.award_status, 'NO') IN ('AWARDED', 'PARTIAL')
3516 GROUP BY pbh.bid_number;
3517
3518 BEGIN
3519
3520 x_progress := '10: GENERATE_POS: Start of PO Creation Script';
3521
3522 log_message(x_progress);
3523
3524 -- initialize to success (3 possible values S=Success; F=Failure; W=Warning)
3525 p_resultout := 'S';
3526
3527 select open_bidding_date,
3528 close_bidding_date,
3529 view_by_date
3530 into x_auction_start_date,
3531 x_auction_end_date,
3532 x_preview_date
3533 from pon_auction_headers_all
3534 where auction_header_id = p_auction_header_id;
3535
3536 /* Lock auction table to prevent concurrency errors */
3537 /* added doctype_id, conterms_exist_flag for contract terms */
3538
3539 SELECT last_update_date, doctype_id, conterms_exist_flag
3540 INTO x_last_update_date, x_doctype_id, x_conterms_exist_flag
3541 FROM pon_auction_headers_all
3542 WHERE auction_header_id = p_auction_header_id
3543 FOR UPDATE;
3544
3545 x_contract_doc_name := PON_CONTERMS_UTL_PVT.get_response_doc_type(x_doctype_id);
3546
3547
3548 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(p_user_name, x_language_code);
3549
3550 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
3551 OPEN awardedBids;
3552 x_progress := '40: GENERATE_POS: Going through the awarded bids';
3553
3554 log_message(x_progress);
3555
3556 LOOP
3557 /* for each active bid where a PO was not created */
3558 FETCH awardedBids into x_bid_number;
3559 EXIT WHEN awardedBids%NOTFOUND;
3560 x_error_code := PO_SUCCESS;
3561 x_progress := '50: GENERATE_POS: Just Before CREATE_PO_STRUCTURE: ' ||
3562 'Bid Number: ' || x_bid_number;
3563
3564 log_message(x_progress);
3565
3566 /* Establish a savepoint */
3567
3568 --savepoint PON_CREATE_PO_DOCUMENTS;
3569
3570 /* This call will create the award purchase order structure
3571 in PDOI */
3572
3573 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'));
3574
3575 CREATE_PO_STRUCTURE(p_auction_header_id,
3576 x_bid_number,
3577 p_user_id,
3578 x_interface_header_id,
3579 x_pdoi_header,
3580 x_failure_code,
3581 x_error_msg);
3582
3583 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'));
3584
3585
3586 IF (x_failure_code = 'FAILURE') THEN
3587
3588 log_message( substrb(x_progress || SQLERRM, 1, 4000));
3589
3590 x_error_code := SOURCING_SYSTEM_ERROR;
3591
3592 END IF;
3593
3594
3595 x_order_number := x_pdoi_header.order_number;
3596
3597 /* call the PO's PL/SQL program to create the Purchase
3598 from the new rows in PDOI */
3599
3600 IF (x_error_code = PO_SUCCESS) THEN
3601
3602 x_progress := '60: GENERATE_POS: Just before creating document';
3603
3604 log_message(x_progress);
3605
3606 -- Get the current policy
3607 v_old_policy := mo_global.get_access_mode();
3608 v_old_org_id := mo_global.get_current_org_id();
3609
3610 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3611 fnd_log.string(
3612 fnd_log.level_statement,
3613 g_module || '.check_unique_order_number',
3614 'old_policy = ' || v_old_policy || ', old_org_id = ' || v_old_org_id);
3615 end if;
3616
3617 -- Set the connection's policy context
3618 mo_global.set_policy_context('S', x_pdoi_header.org_id);
3619
3620 --create BPA/CPA/SPO
3621 IF x_pdoi_header.contract_type = 'CONTRACT' THEN
3622 x_progress := '65: GENERATE_POS: Just before create_CPA interface id '||x_interface_header_id;
3623 log_message(x_progress);
3624 x_progress := '65.1: GENERATE_POS: Just before create_CPA auction header id '||p_auction_header_id;
3625 log_message(x_progress);
3626 x_progress := '65.2: GENERATE_POS: Just before create_CPA bid number '||x_bid_number;
3627 log_message(x_progress);
3628 x_progress := '65.3: GENERATE_POS: Just before create_CPA conterms flag '||x_conterms_exist_flag;
3629 log_message(x_progress);
3630 x_progress := '65.4: GENERATE_POS: Just before create_CPA conterms doc type '||x_contract_doc_name;
3631 log_message(x_progress);
3632
3633
3634 PO_SOURCING_GRP.CREATE_CPA(
3635 p_api_version => 1.0,
3636 p_init_msg_list => FND_API.G_TRUE,
3637 p_commit => FND_API.G_FALSE,
3638 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3639 x_msg_count => x_msg_count,
3640 x_msg_data => x_msg_data,
3641 x_return_status => x_return_status,
3642 p_interface_header_id => x_interface_header_id,
3643 p_auction_header_id => p_auction_header_id ,
3644 p_bid_number => x_bid_number ,
3645 p_sourcing_k_doc_type => x_contract_doc_name,
3646 p_conterms_exist_flag => x_conterms_exist_flag,
3647 p_document_creation_method => 'AWARD_SOURCING',
3648 x_document_id => x_po_header_id,
3649 x_document_number => x_order_number
3650 );
3651 x_progress := '66: GENERATE_POS: Just after create_cpa status:'||x_return_status;
3652
3653
3654 log_message(x_progress);
3655 x_progress := '66.1: GENERATE_POS: Just after create_CPA order number '||x_order_number;
3656 log_message(x_progress);
3657 x_progress := '66.2: GENERATE_POS: Just after create_CPA po header id '||x_po_header_id;
3658 log_message(x_progress);
3659
3660 IF (x_return_status = FND_API.g_ret_sts_success
3661 AND x_order_number is NOT NULL) THEN
3662 x_error_code := PO_SUCCESS;
3663 ELSIF (x_return_status = FND_API.g_ret_sts_error
3664 OR x_return_status = FND_API.g_ret_sts_unexp_error) THEN
3665 x_error_code := PO_PDOI_ERROR;
3666 IF x_msg_count = 1 THEN
3667 x_error_msg := x_msg_data;
3668 ELSIF (x_msg_count > 0) THEN
3669 x_error_msg := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
3670 END IF;-- msg_count
3671
3672 x_progress := substrb('67: GENERATE_POS: create cpa error:'||x_error_msg, 1,4000);
3673
3674 log_message(x_progress);
3675
3676
3677 END IF;-- return status
3678 -- delete the interface record irrespective of whether success or failure
3679
3680 x_progress := '68: GENERATE_POS: Before call to Delete interface header id '||x_interface_header_id;
3681
3682 log_message(x_progress);
3683
3684 PO_SOURCING_GRP.DELETE_INTERFACE_HEADER(
3685 p_api_version => 1.0,
3686 p_init_msg_list => FND_API.G_FALSE,
3687 p_commit => FND_API.G_FALSE,
3688 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3689 x_msg_count => x_msg_count,
3690 x_msg_data => x_msg_data,
3691 x_return_status => x_return_status,
3692 p_interface_header_id => x_interface_header_id
3693 );
3694 x_progress := '68.1: GENERATE_POS: Just after call to Delete interface header status:'||x_return_status;
3695
3696 log_message(x_progress);
3697
3698 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
3699 x_error_code := PO_DELETE_ERROR;
3700 IF x_msg_count = 1 THEN
3701 x_error_msg := x_msg_data;
3702 ELSIF (x_msg_count > 0) THEN
3703 x_error_msg := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
3704 END IF;-- msg_count
3705 x_progress := substrb('68.2: GENERATE_POS: delete interface header error:'||x_error_msg, 1,4000);
3706
3707 log_message(x_progress);
3708
3709 END IF;-- x_return_status
3710 ELSE -- else if contracttype is bpa or spo
3711
3712 x_progress := '69: GENERATE_POS: Just before create_documents with parameters : x_interface_header_id ='
3713 || x_interface_header_id || ' org_id =' || x_pdoi_header.org_id || ' x_po_header_id ='
3714 || x_po_header_id || ' x_num_lines_processed =' || x_num_lines_processed
3715 || ' x_contract_doc_name =' || x_contract_doc_name || ' x_conterms_exist_flag = '
3716 || x_conterms_exist_flag;
3717
3718 log_message(x_progress);
3719
3720
3721 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'));
3722
3723 po_interface_s.create_documents(P_API_VERSION => 1.0,
3724 X_RETURN_STATUS => x_return_status,
3725 X_MSG_COUNT => x_msg_count,
3726 X_MSG_DATA => x_msg_data,
3727 P_BATCH_ID => x_interface_header_id,
3728 P_REQ_OPERATING_UNIT_ID => x_pdoi_header.org_id,
3729 P_PURCH_OPERATING_UNIT_ID => x_pdoi_header.org_id,
3730 X_DOCUMENT_ID => x_po_header_id,
3731 X_NUMBER_LINES => x_num_lines_processed,
3732 X_DOCUMENT_NUMBER => x_order_number,
3733 P_SOURCING_K_DOC_TYPE => x_contract_doc_name,
3734 P_CONTERMS_EXIST_FLAG => x_conterms_exist_flag,
3735 P_DOCUMENT_CREATION_METHOD => 'AWARD_SOURCING');
3736
3737 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'));
3738
3739 --
3740 -- Derive x_error_code based on x_return_status
3741 --
3742 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);
3743
3744
3745
3746 IF (x_return_status = FND_API.g_ret_sts_success
3747 AND x_num_lines_processed >0 AND x_order_number is NOT NULL)
3748 THEN
3749 x_error_code := PO_SUCCESS;
3750 ELSIF (x_return_status = PO_INTERFACE_S.G_RET_STS_DUP_DOC_NUM) THEN
3751 x_error_code := DUPLICATE_PO_NUMBER;
3752 ELSE
3753 x_error_code := PO_SYSTEM_ERROR;
3754 END IF;
3755
3756
3757 log_message('after create_documents: x_error_code=' || x_error_code);
3758
3759 IF (x_error_code <> PO_SUCCESS) THEN
3760 null;
3761 --rollback to savepoint PON_CREATE_PO_DOCUMENTS;
3762 END IF;
3763
3764 END IF;--END if contractType is CONTRACT
3765
3766 -- Set the policy context back
3767 mo_global.set_policy_context(v_old_policy,v_old_org_id);
3768
3769 END IF;
3770
3771 IF (x_error_code = PO_SUCCESS) THEN
3772 x_pdoi_header.order_number := x_order_number;
3773 END IF;
3774
3775
3776 UPDATE pon_bid_headers
3777 SET po_header_id = decode(x_error_code, PO_SUCCESS, x_po_header_id, null),
3778 order_number = x_order_number,
3779 po_error_code = x_error_code,
3780 po_error_msg = x_error_msg,
3781 po_wf_creation_rnd = decode(x_error_code, PO_SUCCESS, x_round_number, po_wf_creation_rnd)
3782 where auction_header_id = p_auction_header_id and
3783 bid_number = x_bid_number;
3784
3785
3786 x_progress := '70: GENERATE_POS: After po creation: ' ||
3787 'Bid Number: ' || x_bid_number || ', ' ||
3788 'PO Header ID: ' || x_po_header_id || ', ' ||
3789 'PO Order Number: ' || x_order_number || ', ' ||
3790 'Error Code: ' || x_error_code || ', ' ||
3791 'Round Number: ' || x_round_number || ', '||
3792 'Return Status: '|| x_return_status || ', '||
3793 'Message Count: '|| x_msg_count || ', '||
3794 'Message Data: '|| x_msg_data;
3795
3796 log_message(x_progress);
3797
3798 IF (x_error_code = PO_SUCCESS AND x_pdoi_header.initiate_approval = 'Y') THEN
3799
3800 x_progress := '80: GENERATE_POS: Just before approval wf';
3801 log_message(x_progress);
3802
3803 /* kick off the PO approval worflow process */
3804
3805
3806 BEGIN
3807
3808 log_error('2.5 LAUNCH_PO_APPROVAL for auction ' || p_auction_header_id || ' and bid ' || x_bid_number
3809 || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
3810
3811 LAUNCH_PO_APPROVAL(x_po_header_id, x_pdoi_header, p_user_id);
3812
3813 log_error('2.6 Completed LAUNCH_PO_APPROVAL for auction ' || p_auction_header_id || ' and bid '
3814 || x_bid_number || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
3815
3816 EXCEPTION
3817
3818 when others then
3819
3820 log_error('2.61 EXCEPTION IN LAUNCH_PO_APPROVAL for auction ' || p_auction_header_id
3821 || ' with progress so far as ' || x_progress);
3822
3823 log_error('2.7 EXCEPTION IN LAUNCH_PO_APPROVAL for auction ' || p_auction_header_id
3824 || ' and bid ' || x_bid_number || ' at '
3825 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
3826
3827 p_resultout := 'W';
3828 END;
3829 END IF;
3830
3831 END LOOP;
3832
3833 CLOSE awardedBids;
3834
3835 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
3836
3837 EXCEPTION
3838
3839 when others then
3840
3841 p_resultout := 'F';
3842
3843 log_error(substrb(x_progress || SQLERRM, 1, 4000));
3844
3845 IF (awardedBids%ISOPEN) THEN
3846 close awardedBids;
3847 END IF;
3848
3849 raise;
3850
3851 END GENERATE_POS;
3852
3853
3854
3855 PROCEDURE LAUNCH_PO_APPROVAL (p_po_header_id IN NUMBER,
3856 p_pdoi_header IN PDOIheader,
3857 p_user_id IN NUMBER) IS
3858
3859 x_ItemType varchar2(20) := null;
3860 x_ItemKey varchar2(60) := null;
3861 x_workflow_process varchar2(40) := null;
3862 x_action_orig_from varchar2(30) := null;
3863 x_doc_id number := null;
3864
3865 x_responsibility_id number := null;
3866 x_application_id number := null;
3867 x_preparer_id number := null;
3868 x_doc_type varchar2(25) := null;
3869 x_doc_subtype varchar2(25) := null;
3870 x_seq_for_item_key varchar2(6) := null;
3871 x_doc_type_to_create varchar2(25);
3872 v_old_policy varchar2(1);
3873 v_old_org_id number;
3874 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3875 x_supplier_notif_method po_vendor_sites_all.supplier_notif_method%TYPE := null;
3876 x_print_flag varchar2(1) := 'N';
3877 x_fax_flag varchar2(1) := 'N';
3878 x_email_flag varchar2(1) := 'N';
3879 x_eMail_address po_vendor_sites_all.email_address%TYPE := null;
3880 x_fax_number varchar2(100) := null;
3881 x_po_api_return_status varchar2 (3) := null;
3882 x_msg_count number := NULL;
3883 x_msg_data varchar2(2000):= NULL;
3884 x_document_num po_headers.segment1%type := null;
3885
3886 BEGIN
3887 x_progress := '10: launch_po_approval: Start of Procedure';
3888
3889 log_message(x_progress);
3890
3891 FND_PROFILE.GET('RESP_ID', x_responsibility_id);
3892
3893 FND_PROFILE.GET('RESP_APPL_ID', x_application_id);
3894
3895 fnd_global.APPS_INITIALIZE (p_user_id, x_responsibility_id, x_application_id);
3896
3897 -- Get the current policy
3898 v_old_policy := mo_global.get_access_mode();
3899 v_old_org_id := mo_global.get_current_org_id();
3900
3901 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3902 fnd_log.string(
3903 fnd_log.level_statement,
3904 g_module || '.check_unique_order_number',
3905 'old_policy = ' || v_old_policy || ', old_org_id = ' || v_old_org_id);
3906 end if;
3907
3908 -- Set the connection's policy context
3909 mo_global.set_policy_context('S', p_pdoi_header.org_id);
3910
3911 x_doc_type_to_create := p_pdoi_header.contract_type;
3912
3913
3914 if (x_doc_type_to_create = 'BLANKET') then
3915 x_doc_type := 'PA';
3916 x_doc_subtype := 'BLANKET';
3917 elsif (x_doc_type_to_create = 'CONTRACT') then
3918 x_doc_type := 'PA';
3919 x_doc_subtype := 'CONTRACT';
3920 else
3921 /* STANDARD */
3922 x_doc_type := 'PO';
3923 x_doc_subtype := 'STANDARD';
3924 end if;
3925
3926 /* Need to get item_type and workflow process from po_document_types.
3927 * They may be different based on the doc/org.
3928 */
3929
3930 select wf_approval_itemtype,
3931 wf_approval_process
3932 into x_ItemType,
3933 x_workflow_process
3934 from po_document_types
3935 where document_type_code = x_doc_type
3936 and document_subtype = x_doc_subtype;
3937
3938 x_progress := '20: launch_po_approval: x_doc_type: ' || x_doc_type || ', ' ||
3939 'x_doc_subtype: ' || x_doc_subtype || ', ' ||
3940 'x_ItemType: ' || x_ItemType || ', ' ||
3941 'x_workflow_process: ' || x_workflow_process;
3942 log_message(x_progress);
3943
3944 /* Get the unique sequence to make sure item key will be unique */
3945
3946 SELECT to_char(PO_WF_ITEMKEY_S.NEXTVAL)
3947 INTO x_seq_for_item_key
3948 FROM dual;
3949
3950 SELECT employee_id
3951 INTO x_preparer_id
3952 FROM fnd_user
3953 WHERE user_id = p_user_id;
3954
3955 x_doc_id:= p_po_header_id;
3956
3957 x_ItemKey := to_char(x_doc_id) || '-' || x_seq_for_item_key;
3958
3959 x_progress := '25: Calling Get_Transmission_Defaults PO API:' ||
3960 'p_api_version: 1.0 , ' ||
3961 'p_int_msg_list: FND_API.G_FALSE, '||
3962 'p_doc_id: ' || x_doc_id || ', ' ||
3963 'p_doc_type: ' || x_doc_type || ', ' ||
3964 'p_doc_subtype: ' || x_doc_subtype || ', ' ||
3965 'p_preparer_id: ' || x_preparer_id;
3966
3967
3968 /* Get supplier's default transmission settings */
3969 PO_VENDOR_SITES_GRP.Get_Transmission_Defaults(
3970 p_api_version => 1.0,
3971 p_init_msg_list => FND_API.G_FALSE,
3972 p_document_id => p_po_header_id,
3973 p_document_type => x_doc_type,
3974 p_document_subtype => x_doc_subtype,
3975 p_preparer_id => x_preparer_id,
3976 x_default_method => x_supplier_notif_method,
3977 x_email_address => x_email_address,
3978 x_fax_number => x_fax_number,
3979 x_document_num => x_document_num,
3980 x_print_flag => x_print_flag,
3981 x_fax_flag => x_fax_flag,
3982 x_email_flag => x_email_flag,
3983 x_return_status => x_po_api_return_status,
3984 x_msg_count => x_msg_count,
3985 x_msg_data => x_msg_data);
3986
3987 if (x_po_api_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3988 x_progress := '27: Failure in : PO_VENDOR_SITES_GRP.Get_Transmission_Defaults : ' || 'x_po_api_return_status : '||x_po_api_return_status ||','||
3989 'x_msg_data : '|| x_msg_data;
3990
3991 log_message(x_progress);
3992
3993 else
3994
3995 x_progress := '30: launch_po_approval: Just before kicking off wf process ' ||
3996 'x_ItemType: ' || x_ItemType || ', ' ||
3997 'x_ItemKey: ' || x_ItemKey || ', ' ||
3998 'x_workflow_process: ' || x_workflow_process || ', ' ||
3999 'x_action_orig_from: ' || x_action_orig_from || ', ' ||
4000 'x_doc_id: ' || x_doc_id || ', ' ||
4001 'x_doc_num: ' || p_pdoi_header.order_number || ', ' ||
4002 'x_preparer_id: ' || x_preparer_id || ', ' ||
4003 'x_doc_type: ' || x_doc_type || ', ' ||
4004 'x_doc_subtype: ' || x_doc_subtype || ', ' ||
4005 'createsourcingrule: ' || p_pdoi_header.create_sourcing_rules || ', ' ||
4006 'releasegenmethod: ' || p_pdoi_header.release_method || ', ' ||
4007 'updatesourcingrule: ' || p_pdoi_header.update_sourcing_rules;
4008
4009
4010
4011
4012 log_message(x_progress);
4013
4014 po_reqapproval_init1.start_wf_process(
4015 x_ItemType,
4016 x_ItemKey,
4017 x_workflow_process,
4018 x_action_orig_from,
4019 x_doc_id,
4020 p_pdoi_header.order_number, -- x_doc_num
4021 x_preparer_id,
4022 x_doc_type,
4023 x_doc_subtype,
4024 null, -- x_submitter_action,
4025 null, -- x_forward_to_id,
4026 null, -- x_forward_from_id,
4027 null, -- x_def_approval_path_id,
4028 null, -- x_note,
4029 x_print_flag, -- x_printflag
4030 x_fax_flag, -- x_faxflag
4031 x_fax_number, -- x_faxnum
4032 x_email_flag, -- x_emailflag
4033 x_email_address, -- x_emailaddress
4034 p_pdoi_header.create_sourcing_rules,
4035 p_pdoi_header.release_method,
4036 p_pdoi_header.update_sourcing_rules
4037 );
4038
4039 -- Set the org context back
4040 mo_global.set_policy_context(v_old_policy, v_old_org_id);
4041
4042 end if;
4043
4044
4045 EXCEPTION
4046 when others then
4047
4048 log_error(substrb(x_progress || SQLERRM, 1, 4000));
4049
4050 raise;
4051
4052 END LAUNCH_PO_APPROVAL;
4053
4054 PROCEDURE CHECK_PO_STATUS(itemtype IN VARCHAR2,
4055 itemkey IN VARCHAR2,
4056 actid IN NUMBER,
4057 uncmode IN VARCHAR2,
4058 resultout OUT NOCOPY VARCHAR2) IS
4059
4060
4061 x_number_of_failed_pos NUMBER;
4062 x_auction_header_id NUMBER;
4063 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4064
4065 BEGIN
4066 IF (UNCMODE = 'CANCEL') THEN
4067 return;
4068 END IF;
4069
4070 x_progress := '10: CHECK_PO_STATUS: Start of po status check';
4071 log_message(x_progress);
4072
4073 x_number_of_failed_pos := 0;
4074
4075 x_auction_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
4076 itemkey => itemkey,
4077 aname => 'AUCTION_ID');
4078
4079
4080 SELECT count(pbh.bid_number)
4081 INTO x_number_of_failed_pos
4082 FROM pon_bid_headers pbh
4083 WHERE pbh.auction_header_id = x_auction_header_id and
4084 nvl(pbh.bid_status, 'NONE') not in ('ARCHIVED', 'DISQUALIFIED') and
4085 pbh.po_header_id is NULL and
4086 nvl(pbh.award_status, 'NO') in ('AWARDED', 'PARTIAL');
4087
4088 x_progress := '20: CHECK_PO_STATUS: Number of Failed POs: ' || x_number_of_failed_pos;
4089 log_message(x_progress);
4090
4091 IF (x_number_of_failed_pos > 0) THEN
4092 resultout := 'N';
4093 -- setting auction outcome status to outcome failed
4094 UPDATE PON_AUCTION_HEADERS_ALL
4095 SET OUTCOME_STATUS = 'OUTCOME_FAILED'
4096 WHERE AUCTION_HEADER_ID = x_auction_header_id;
4097 ELSE
4098 /* update auction outcome status to outcome_completed */
4099 UPDATE PON_AUCTION_HEADERS_ALL
4100 SET OUTCOME_STATUS = 'OUTCOME_COMPLETED'
4101 WHERE AUCTION_HEADER_ID = x_auction_header_id;
4102 resultout := 'Y';
4103
4104 END IF;
4105
4106 x_progress := '30: CHECK_PO_STATUS: resultout: ' || resultout;
4107 log_message(x_progress);
4108
4109
4110 EXCEPTION
4111
4112 when others then
4113 wf_core.context('PON_AUCTION_CREATE_PO_PKG','checkPOStatus', itemtype, itemkey, x_progress, SQLERRM);
4114 log_error(itemtype || ' ' || itemkey || ' ' || substrb(x_progress || SQLERRM, 1, 4000));
4115 raise;
4116
4117 END CHECK_PO_STATUS;
4118
4119
4120 /* document_id will have the form of auction_header_id:round_number:msg_suffix */
4121
4122 PROCEDURE GENERATE_PO_SUCCESS_EMAIL(document_id IN VARCHAR2,
4123 display_type IN VARCHAR2,
4124 document IN OUT NOCOPY VARCHAR2,
4125 document_type IN OUT NOCOPY VARCHAR2) IS
4126
4127 x_language_code VARCHAR2(4);
4128 x_index NUMBER;
4129 x_substr VARCHAR2(4000);
4130 x_auction_header_id NUMBER;
4131 x_round_number NUMBER;
4132 x_msg_suffix VARCHAR2(3) := '';
4133 x_user_name fnd_user.user_name%TYPE;
4134 x_bid_number pon_bid_headers.bid_number%TYPE;
4135 x_vendor_name po_vendors.vendor_name%TYPE;
4136 x_vendor_site_name po_vendor_sites_all.vendor_site_code%TYPE;
4137 x_agent_name per_all_people_f.full_name%TYPE;
4138 x_order_number pon_bid_headers.order_number%TYPE;
4139 po_status varchar2(4000);
4140 msgBid varchar2(2000);
4141 msgSupplier varchar2(2000);
4142 msgSupplierSite varchar2(2000);
4143 msgBuyer varchar2(2000);
4144 msgPO varchar2(2000);
4145 msgPOdetails varchar2(2000);
4146 msgNumCreated varchar2(2000);
4147 newline varchar2(256);
4148 beginBold VARCHAR2(10);
4149 endBold VARCHAR2(10);
4150 x_count NUMBER;
4151 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4152 itemkey pon_auction_headers_all.wf_poncompl_item_key%TYPE;
4153 x_purchase_orders VARCHAR2(30);
4154 x_contract_type pon_auction_headers_all.contract_type%TYPE;
4155
4156 /* Selects all relevant information for the first 10 successful POs of the current round*/
4157
4158 CURSOR successfulPOs is
4159
4160 SELECT *
4161 FROM (
4162 SELECT pbh.bid_number,
4163 pov.vendor_name,
4164 pbh.vendor_site_code,
4165 papf.full_name,
4166 pbh.order_number
4167 FROM pon_bid_headers pbh,
4168 po_vendors pov,
4169 per_all_people_f papf
4170 WHERE pbh.auction_header_id = x_auction_header_id
4171 AND pbh.bid_status = 'ACTIVE'
4172 AND pbh.po_header_id is NOT NULL
4173 AND pbh.po_wf_creation_rnd = x_round_number
4174 AND pbh.vendor_id = pov.vendor_id
4175 AND pbh.agent_id = papf.person_id
4176 AND papf.effective_start_date < sysdate
4177 AND papf.effective_end_date = (select max(papf2.effective_end_date)
4178 from per_all_people_f papf2
4179 where papf2.person_id = pbh.agent_id)
4180 GROUP BY
4181 pbh.bid_number,
4182 pov.vendor_name,
4183 pbh.vendor_site_code,
4184 papf.full_name,
4185 pbh.order_number
4186 )
4187 WHERE rownum <= 10;
4188
4189 BEGIN
4190
4191 x_progress := '10: GENERATE_PO_SUCCESS_EMAIL unique_key: ' || document_id;
4192 log_message('PONCOMPL' || ' ' || x_progress);
4193 x_index := instr(document_id, ':');
4194 x_auction_header_id := substr(document_id, 1, x_index-1);
4195 x_substr := substr(document_id, x_index+1);
4196 x_index := instr(x_substr, ':');
4197 x_round_number := substr(x_substr, 1, x_index-1);
4198 x_substr := substr(x_substr, x_index+1);
4199 x_index := instr(x_substr, ':');
4200 x_msg_suffix := substr(x_substr, 1, x_index-1);
4201 x_user_name := substr(x_substr, x_index+1);
4202
4203 SELECT wf_poncompl_item_key, contract_type
4204 INTO itemkey, x_contract_type
4205 FROM pon_auction_headers_all
4206 WHERE auction_header_id = x_auction_header_id;
4207
4208 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(x_user_name, x_language_code);
4209 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
4210 IF (x_contract_type = 'STANDARD') THEN
4211 x_purchase_orders := 'Standard Purchase Order';
4212 msgNumCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_SUCC_PO');
4213 msgPOdetails := PON_AUCTION_PKG.getMessage('PON_AUC_WF_PO_DETAILS');
4214 ELSIF (x_contract_type = 'BLANKET') THEN
4215 x_purchase_orders := 'Blanket Purchase Agreement';
4216 msgNumCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_SUCC_BL');
4217 msgPOdetails := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BL_DETAILS');
4218 ELSIF (x_contract_type = 'CONTRACT') THEN
4219 x_purchase_orders := 'Contract Purchase Agreement';
4220 msgNumCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_SUCC_CPA');
4221 msgPOdetails := PON_AUCTION_PKG.getMessage('PON_AUC_WF_CPA_DETAILS');
4222 END IF;
4223 msgBid := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BID', x_msg_suffix);
4224 msgSupplier := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUPPLIER');
4225 msgSupplierSite := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUPPLIER_SITE');
4226 msgBuyer := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BUYER');
4227 msgPO := PON_AUCTION_PKG.getMessage('PON_AUC_WF_PO', 'null', 'PURCHASE_ORDERS', x_purchase_orders);
4228 IF (display_type = 'text/plain') THEN
4229 document_type := 'text/plain';
4230 newline := fnd_global.newline;
4231 beginBold := '';
4232 endBold := '';
4233 ELSE
4234 document_type := 'text/html';
4235 newline := '<BR>';
4236 beginBold := '<b>';
4237 endBold := '</b>';
4238 END IF;
4239
4240 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;
4241 log_message('PONCOMPL' || ' ' || itemkey || x_progress);
4242 -- end if;
4243 OPEN successfulPOs;
4244 LOOP
4245 FETCH successfulPOs into x_bid_number, x_vendor_name,
4246 x_vendor_site_name, x_agent_name,
4247 x_order_number;
4248 EXIT WHEN successfulPOs%NOTFOUND;
4249 po_status := msgPOdetails || newline ||
4250 msgBid || ' ' || beginBold || x_bid_number || endBold || newline ||
4251 msgSupplier || ' ' || beginBold || x_vendor_name || endBold || newline ||
4252 msgSupplierSite || ' ' || beginBold || x_vendor_site_name || endBold || newline ||
4253 msgPO || ' ' || beginBold || x_order_number || endBold || newline ||
4254 msgBuyer || ' ' || beginBold || x_agent_name || endBold || newline;
4255
4256 x_progress := '30: GENERATE_PO_SUCCESS_EMAIL message: ' || po_status;
4257 log_message('PONCOMPL' || ' ' || itemkey || ' ' ||x_progress);
4258 document := document || po_status || newline;
4259 END LOOP;
4260 x_count := successfulPOs%ROWCOUNT;
4261 CLOSE successfulPOs;
4262 document := msgNumCreated || ' ' || beginBold || x_count || endBold || newline || newline|| document;
4263 x_progress := '40: GENERAGE_PO_SUCCESS_EMAIL final e-mail message: ' || document;
4264 log_message('PONCOMPL' || ' ' || itemkey || ' ' ||x_progress);
4265
4266 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
4267 EXCEPTION
4268
4269 when others then
4270 wf_core.context('PON_AUCTION_CREATE_PO_PKG','generatePOSuccessEmail', x_progress, SQLERRM);
4271 log_error('PONCOMPL' || ' ' || itemkey || ' ' || substrb(x_progress||SQLERRM, 1, 4000));
4272
4273 IF (successfulPOs%ISOPEN) THEN
4274 close successfulPOs;
4275 END IF;
4276
4277 raise;
4278
4279 END GENERATE_PO_SUCCESS_EMAIL;
4280
4281
4282 /* document_id will have the form of auction_header_id:msg_suffix */
4283
4284 PROCEDURE GENERATE_PO_FAILURE_EMAIL(document_id IN VARCHAR2,
4285 display_type IN VARCHAR2,
4286 document IN OUT NOCOPY VARCHAR2,
4287 document_type IN OUT NOCOPY VARCHAR2) IS
4288
4289
4290 x_language_code VARCHAR2(4);
4291 x_index NUMBER;
4292 x_substr VARCHAR2(4000);
4293 x_auction_header_id NUMBER;
4294 x_msg_suffix VARCHAR2(3) := '';
4295 x_user_name fnd_user.user_name%TYPE;
4296 x_bid_number NUMBER;
4297 x_vendor_name po_vendors.vendor_name%TYPE;
4298 x_vendor_site_name po_vendor_sites_all.vendor_site_code%TYPE;
4299 x_agent_name per_all_people_f.full_name%TYPE;
4300 x_order_number pon_bid_headers.order_number%TYPE;
4301 x_error_code pon_bid_headers.po_error_code%TYPE;
4302 po_status varchar2(4000);
4303 msgBid varchar2(2000);
4304 msgSupplier varchar2(2000);
4305 msgSupplierSite varchar2(2000);
4306 msgBuyer varchar2(2000);
4307 msgError varchar2(2000);
4308 msgNumNotCreated varchar2(2000);
4309 msgErrorCode varchar2(2000);
4310 msgPurchaseOrder varchar2(2000);
4311 msgDuplicatePONumber varchar2(2000);
4312 msgSystemError varchar2(2000);
4313 msgPO varchar2(2000);
4314 newline varchar2(256);
4315 beginBold VARCHAR2(10);
4316 endBold VARCHAR2(10);
4317 x_count NUMBER;
4318 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4319 itemkey pon_auction_headers_all.wf_poncompl_item_key%TYPE;
4320 x_purchase_orders VARCHAR2(30);
4321 x_contract_type pon_auction_headers_all.contract_type%TYPE;
4322 x_error_msg pon_bid_headers.po_error_msg%type;
4323
4324 CURSOR failedPOs is
4325 SELECT pbh.bid_number,
4326 pov.vendor_name,
4327 povsa.vendor_site_code,
4328 papf.full_name,
4329 pbh.order_number,
4330 pbh.po_error_code,
4331 pbh.po_error_msg
4332 FROM pon_bid_headers pbh,
4333 po_vendors pov,
4334 po_vendor_sites_all povsa,
4335 per_all_people_f papf
4336 WHERE pbh.auction_header_id = x_auction_header_id
4337 AND pbh.bid_status = 'ACTIVE'
4338 AND nvl(pbh.award_status, 'NO') in ('AWARDED', 'PARTIAL')
4339 AND pbh.po_header_id is NULL
4340 AND pbh.vendor_id = pov.vendor_id
4341 AND pbh.vendor_id = povsa.vendor_id
4342 AND pbh.agent_id = papf.person_id
4343 AND papf.effective_start_date < sysdate
4344 AND papf.effective_end_date = (select max(papf2.effective_end_date)
4345 from per_all_people_f papf2
4346 where papf2.person_id = pbh.agent_id)
4347 GROUP BY
4348 pbh.bid_number,
4349 pov.vendor_name,
4350 povsa.vendor_site_code,
4351 papf.full_name,
4352 pbh.order_number,
4353 pbh.po_error_code,
4354 pbh.po_error_msg;
4355
4356 BEGIN
4357 x_progress := '10: GENERATE_PO_FAILURE_EMAIL unique key: ' || document_id;
4358 log_message('PONCOMPL' || ' ' || x_progress);
4359 x_index := instr(document_id, ':');
4360 x_auction_header_id := substr(document_id, 1, x_index-1);
4361 x_substr := substr(document_id, x_index+1);
4362 x_index := instr(x_substr, ':');
4363 x_msg_suffix := substr(x_substr, 1, x_index-1);
4364 x_user_name := substr(x_substr, x_index+1);
4365
4366
4367 SELECT wf_poncompl_item_key, contract_type
4368 INTO itemkey, x_contract_type
4369 FROM pon_auction_headers_all
4370 WHERE auction_header_id = x_auction_header_id;
4371
4372 x_progress := '20: GENERATE_PO_FAILURE_EMAIL auction id: ' || x_auction_header_id || ', ' || 'message suffix: ' || x_msg_suffix || ', ' || 'user name: ' || x_user_name;
4373
4374 log_message('PONCOMPL' || ' ' || itemkey || ' ' || x_progress);
4375 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(x_user_name, x_language_code);
4376 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, x_language_code);
4377
4378
4379 IF (x_contract_type = 'STANDARD') THEN
4380 x_purchase_orders := 'Standard Purchase Order';
4381 msgNumNotCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_FAIL_PO');
4382 ELSIF (x_contract_type = 'BLANKET') THEN
4383 x_purchase_orders := 'Blanket Purchase Agreement';
4384 msgNumNotCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_FAIL_BL');
4385 ELSIF (x_contract_type = 'CONTRACT') THEN
4386 x_purchase_orders := 'Contract Purchase Agreement';
4387 msgNumNotCreated := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NUM_OF_FAIL_CPA');
4388 END IF;
4389
4390 msgBid := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BID', x_msg_suffix);
4391 msgSupplier := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUPPLIER');
4392 msgSupplierSite := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SUPPLIER_SITE');
4393 msgBuyer := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BUYER');
4394 msgPurchaseOrder := PON_AUCTION_PKG.getMessage('PON_AUCTS_PAY_PO');
4395 msgDuplicatePONumber := PON_AUCTION_PKG.getMessage('PON_AUC_WF_DUP_PO_NUM');
4396 msgSystemError := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR');
4397 msgPO := PON_AUCTION_PKG.getMessage('PON_AUC_WF_PO', 'null', 'PURCHASE_ORDERS', x_purchase_orders);
4398
4399 IF (display_type = 'text/plain') THEN
4400 document_type := 'text/plain';
4401 newline := fnd_global.newline;
4402 beginBold := '';
4403 endBold := '';
4404 ELSE
4405 document_type := 'text/html';
4406 newline := '<BR>';
4407 beginBold := '<b>';
4408 endBold := '</b>';
4409 END IF;
4410
4411 OPEN failedPOs;
4412 LOOP
4413 FETCH failedPOs into x_bid_number, x_vendor_name,
4414 x_vendor_site_name, x_agent_name,
4415 x_order_number, x_error_code ,x_error_msg;
4416 EXIT WHEN failedPOs%NOTFOUND;
4417
4418 IF (x_error_code = DUPLICATE_PO_NUMBER) THEN
4419 msgErrorCode := msgPurchaseOrder || ' ' || x_order_number || ': ' || msgDuplicatePONumber;
4420
4421 ELSIF (x_error_code = PO_SYSTEM_ERROR OR x_error_code = SOURCING_SYSTEM_ERROR) THEN
4422 msgErrorCode := msgSystemError;
4423 ELSIF (x_error_code = PO_PDOI_ERROR ) THEN
4424 msgErrorCode := msgSystemError||' :'||substrb(x_error_msg,1,1000);
4425
4426 END IF;
4427
4428 po_status := msgBid || ' ' || beginBold || x_bid_number || endBold || newline ||
4429 msgSupplier || ' ' || beginBold || x_vendor_name || endBold || newline ||
4430 msgSupplierSite || ' ' || beginBold || x_vendor_site_name || endBold || newline ||
4431 msgPO || ' ' || beginBold || 'Not Created' || endBold || newline ||
4432 msgBuyer || ' ' || beginBold || x_agent_name || endBold || newline ||
4433 msgError || ' ' || beginBold || msgErrorCode || endBold || newline;
4434
4435
4436 x_progress := '30: GENERATE_PO_FAILURE_EMAIL message: ' || po_status;
4437 log_message('PONCOMPL' || ' ' || itemkey || ' ' || x_progress);
4438 document := document || po_status || newline;
4439 END LOOP;
4440 x_count := failedPOs%ROWCOUNT;
4441 CLOSE failedPOs;
4442
4443 document := msgNumNotCreated || ' ' || beginBold || x_count || endBold || newline || newline|| document;
4444 x_progress := '40: GENERATE_PO_FAILURE_EMAIL final e-mail message: ' || document;
4445
4446 log_message('PONCOMPL' || ' ' || itemkey || ' ' || x_progress);
4447 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
4448
4449 EXCEPTION
4450
4451 when others then
4452 wf_core.context('PON_AUCTION_CREATE_PO_PKG','generatePOFailureEmail', x_progress, SQLERRM);
4453 log_error('PONCOMPL' || ' ' || itemkey || ' ' || substrb(x_progress || SQLERRM, 1, 4000));
4454
4455 IF (failedPOs%ISOPEN) THEN
4456 close failedPOs;
4457 END IF;
4458
4459 raise;
4460
4461 END GENERATE_PO_FAILURE_EMAIL;
4462
4463
4464
4465 procedure CHECK_PO_EMAIL_TYPE (itemtype IN VARCHAR2,
4466 itemkey IN VARCHAR2,
4467 actid IN NUMBER,
4468 uncmode IN VARCHAR2,
4469 resultout OUT NOCOPY VARCHAR2)
4470 IS
4471
4472 BEGIN
4473
4474 -- PON_AUC_PO_ALLOC_REQS_FAIL
4475 -- PON_AUC_PO_ALLOC_SPLIT_FAIL
4476 -- PON_AUC_PO_CREATE_PO_FAIL
4477 -- PON_AUC_PO_CREATE_PO_SUCCESS
4478
4479 -- should get it from a workflow item-attribute
4480
4481 resultout := wf_engine.GetItemAttrText (itemtype => itemtype,
4482 itemkey => itemkey,
4483 aname => 'AUCTION_PO_EMAIL_TYPE');
4484
4485
4486 END CHECK_PO_EMAIL_TYPE;
4487
4488
4489
4490 PROCEDURE START_PO_CREATION(EFFBUF OUT NOCOPY VARCHAR2,
4491 RETCODE OUT NOCOPY VARCHAR2,
4492 p_auction_header_id IN NUMBER, -- 1
4493 p_user_name IN VARCHAR2, -- 2
4494 p_user_id IN NUMBER, -- 3
4495 p_formatted_name IN VARCHAR2, -- 4
4496 p_auction_title IN VARCHAR2, -- 5
4497 p_organization_name IN VARCHAR2, -- 6
4498 p_resultout OUT NOCOPY VARCHAR2) IS -- 7
4499
4500
4501 x_itemkey wf_items.ITEM_KEY%TYPE;
4502 x_sequence NUMBER;
4503 x_current_round NUMBER;
4504 x_requistion_based VARCHAR2(12);
4505 x_has_items PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
4506 x_number_of_failed_pos NUMBER;
4507 x_email_type VARCHAR2(240);
4508 x_allocation_error VARCHAR2(2000);
4509 x_line_number NUMBER;
4510 x_item_number pon_auction_item_prices_all.ITEM_NUMBER%TYPE;
4511 x_item_description pon_auction_item_prices_all.ITEM_DESCRIPTION%TYPE;
4512 x_item_revision pon_auction_item_prices_all.ITEM_REVISION%TYPE;
4513 x_requisition_number PON_AUCTION_ITEM_PRICES_ALL.REQUISITION_NUMBER%TYPE;
4514 x_job_name PER_JOBS.NAME%TYPE;
4515 x_document_disp_line_number PON_AUCTION_ITEM_PRICES_ALL.DOCUMENT_DISP_LINE_NUMBER%TYPE;
4516 l_resultout VARCHAR2(10);
4517
4518 x_open_bidding_date date;
4519 x_close_bidding_date date;
4520 x_trading_partner_contact_id number;
4521 x_doctype_id PON_AUCTION_HEADERS_ALL.DOCTYPE_ID%TYPE;
4522 x_trading_partner_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
4523 x_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
4524
4525 l_workflow_failure VARCHAR2(1);
4526
4527 l_api_name VARCHAR2(30) := ' START_PO_CREATION ';
4528 l_debug_enabled VARCHAR2(1) := 'N';
4529 l_exception_enabled VARCHAR2(1) := 'N';
4530 l_progress NUMBER := 0;
4531
4532 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4533
4534 -- Business Events Project
4535 x_return_status VARCHAR2(20);
4536 x_msg_count NUMBER;
4537 x_msg_data VARCHAR2(2000);
4538
4539
4540 BEGIN
4541
4542 /* perform initialization for FND logging */
4543 if(g_fnd_debug = 'Y') then
4544
4545 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
4546 l_debug_enabled := 'Y';
4547 end if;
4548
4549 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
4550 l_exception_enabled := 'Y';
4551 end if;
4552
4553 end if;
4554
4555 if(l_debug_enabled = 'Y') then
4556
4557 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
4558
4559 log_message(x_progress);
4560
4561 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : ' || p_auction_header_id
4562 || ' ' || p_user_name || ' ' || p_user_id || ' '
4563 || p_formatted_name || ' ' || p_auction_title || ' '
4564 || p_organization_name;
4565 log_message(x_progress);
4566
4567 end if;
4568
4569
4570 log_message(++l_progress || l_api_name || '1. Start PO Creation for ' || p_auction_header_id || ' initiated by '
4571 || p_user_name || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4572
4573 select open_bidding_date,
4574 close_bidding_date,
4575 trading_partner_contact_id,
4576 doctype_id,
4577 trading_partner_name,
4578 trading_partner_contact_name,
4579 has_items_flag,
4580 nvl(wf_poncompl_current_round, 0)
4581 into x_open_bidding_date,
4582 x_close_bidding_date,
4583 x_trading_partner_contact_id,
4584 x_doctype_id,
4585 x_trading_partner_name,
4586 x_trading_partner_contact_name,
4587 x_has_items,
4588 x_current_round
4589 from pon_auction_headers_all
4590 where auction_header_id = p_auction_header_id;
4591
4592
4593 if(l_debug_enabled = 'Y') then
4594 x_progress := ++l_progress || l_api_name || ' : retrieved header information :';
4595 log_message(x_progress);
4596 end if;
4597
4598 --check if the negotiation has requistion based line
4599 BEGIN
4600 SELECT 'REQUISITION'
4601 INTO x_requistion_based
4602 FROM DUAL
4603 WHERE EXISTS(
4604 SELECT '1'
4605 FROM pon_auction_item_prices_all
4606 WHERE auction_header_id = p_auction_header_id
4607 AND line_origination_code = 'REQUISITION'
4608 );
4609
4610 if(l_debug_enabled = 'Y') then
4611 x_progress := ++l_progress || l_api_name || ' : checking whether req-based auction :' || x_requistion_based;
4612 log_message(x_progress);
4613 end if;
4614
4615 EXCEPTION
4616 WHEN NO_DATA_FOUND THEN
4617
4618 x_requistion_based := 'NONE';
4619
4620 if(l_exception_enabled = 'Y') then
4621 x_progress := ++l_progress || l_api_name || ' : exception while checking whether req-based auction :'
4622 || substrb(SQLERRM, 1, 500);
4623 log_error(x_progress);
4624 end if;
4625
4626
4627 END;
4628
4629 if(l_debug_enabled = 'Y') then
4630 x_progress := ++l_progress || l_api_name || ' : getting in to the main try-catch block :';
4631 log_message(x_progress);
4632 end if;
4633
4634 BEGIN -- main try-catch block --{
4635
4636 -- since we havent set wf_poncompl_current_round in pon_auction_headers_all
4637 -- as yet, x_current_round will be zero if there was no failure reported earlier
4638
4639 UPDATE pon_auction_headers_all set
4640 outcome_status = decode(x_current_round, 0, 'OUTCOME_INITIATED', 'OUTCOME_REINITIATED'),
4641 last_update_date = sysdate
4642 WHERE auction_header_id = p_auction_header_id;
4643
4644
4645 if(l_debug_enabled = 'Y') then
4646 x_progress := ++l_progress || l_api_name || ' : updated the outcome_status with round number :' || x_current_round;
4647 log_message(x_progress);
4648 end if;
4649
4650
4651 -- initialize to success
4652 l_resultout := 'SUCCESS';
4653
4654 /* Check whether the auction has any lines */
4655
4656 IF(NVL(X_HAS_ITEMS, 'N') = 'Y') THEN
4657
4658 if(l_debug_enabled = 'Y') then
4659 x_progress := ++l_progress || l_api_name || ' : from start_po_creation to x_has_items true '
4660 || ' to auto_alloc_and_split_req :' || x_current_round;
4661 log_message(x_progress);
4662 end if;
4663
4664 /* proceed with allocation if atleast one line has backing reqs */
4665
4666 IF(NVL(x_requistion_based , 'NONE') = 'REQUISITION') THEN
4667
4668 if(l_debug_enabled = 'Y') then
4669 x_progress := ++l_progress || l_api_name || ' : we have lines with backing reqs,'
4670 || ' hence invoke AUTO_ALLOC_AND_SPLIT_REQ.';
4671 log_message(x_progress);
4672 end if;
4673
4674
4675 log_message('21. from start_po_creation to x_requistion_based is true to auto_alloc_and_split_req');
4676
4677 AUTO_ALLOC_AND_SPLIT_REQ(
4678 p_auction_header_id, -- 1
4679 p_user_name ,
4680 p_user_id ,
4681 p_formatted_name ,
4682 p_auction_title ,
4683 p_organization_name ,
4684 l_resultout ,
4685 x_allocation_error ,
4686 x_line_number ,
4687 x_item_number ,
4688 x_item_description ,
4689 x_item_revision ,
4690 x_requisition_number ,
4691 x_job_name ,
4692 x_document_disp_line_number);
4693
4694 IF(l_resultout = 'FAILURE') THEN
4695
4696
4697 if(l_exception_enabled = 'Y') then
4698 x_progress := ++l_progress || l_api_name || ' : auto_alloc_and_split_req returned '
4699 || ' failure for auction ' || p_auction_header_id;
4700 log_error(x_progress);
4701 end if;
4702
4703 x_email_type := 'PON_AUC_PO_ALLOC_SPLIT_FAIL';
4704 END IF;
4705
4706 END IF;
4707
4708 END IF;
4709
4710 if(l_debug_enabled = 'Y') then
4711 x_progress := ++l_progress || l_api_name || ' : so far so good after req. based handling';
4712 log_message(x_progress);
4713 end if;
4714
4715 IF(l_resultout = 'SUCCESS') THEN --{
4716
4717 if(l_debug_enabled = 'Y') then
4718 x_progress := ++l_progress || l_api_name || ' : so far so good ready to invoke generate_pos';
4719 log_message(x_progress);
4720
4721 log_message(++l_progress || l_api_name || '2. Invoke GENERATE-POS for ' || p_auction_header_id
4722 || ' initiated by ' || p_user_name || ' at '
4723 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4724 end if;
4725
4726 GENERATE_POS(p_auction_header_id,
4727 p_user_name,
4728 p_user_id,
4729 l_resultout);
4730
4731
4732 if(l_resultout = 'W') then
4733 -- just keep track that po approval workflow caused an error
4734 -- proceed with normal operations
4735 l_workflow_failure := 'Y';
4736 end if;
4737
4738 if(l_debug_enabled = 'Y') then
4739 x_progress := ++l_progress || l_api_name || ' : control returned from generate_pos' || l_resultout;
4740 log_message(x_progress);
4741 end if;
4742
4743 x_number_of_failed_pos := 0;
4744
4745 SELECT count(pbh.bid_number)
4746 INTO x_number_of_failed_pos
4747 FROM pon_bid_headers pbh
4748 WHERE pbh.auction_header_id = p_auction_header_id and
4749 nvl(pbh.bid_status, 'NONE') = 'ACTIVE' and
4750 pbh.po_header_id is NULL and
4751 nvl(pbh.award_status, 'NO') in ('AWARDED', 'PARTIAL');
4752
4753 IF (x_number_of_failed_pos > 0) THEN
4754
4755 l_resultout := 'FAILURE';
4756
4757 if(l_exception_enabled = 'Y') then
4758 log_error(++l_progress || l_api_name || '2. GENERATE-POS failed as x_number_of_failed_pos is '
4759 || ' more than zero '
4760 || p_auction_header_id || ' initiated by ' || p_user_name || ' at '
4761 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4762 end if;
4763
4764 UPDATE PON_AUCTION_HEADERS_ALL
4765 SET OUTCOME_STATUS = 'OUTCOME_FAILED'
4766 WHERE AUCTION_HEADER_ID = p_auction_header_id;
4767
4768 IF(x_requistion_based = 'REQUISITION') THEN
4769 x_email_type := 'PON_AUC_PO_ALLOC_REQS_FAIL';
4770 ELSE
4771 x_email_type := 'PON_AUC_PO_CREATE_PO_FAIL';
4772 END IF;
4773
4774 ELSE
4775
4776
4777 if(l_debug_enabled = 'Y') then
4778 x_progress := ++l_progress || l_api_name || ' : generate_pos is successful';
4779 log_message(x_progress);
4780 end if;
4781
4782
4783 log_message('70. generate_pos successful');
4784 /* update auction outcome status to outcome_completed */
4785 UPDATE PON_AUCTION_HEADERS_ALL
4786 SET OUTCOME_STATUS = 'OUTCOME_COMPLETED'
4787 WHERE AUCTION_HEADER_ID = p_auction_header_id;
4788
4789 l_resultout := 'SUCCESS';
4790
4791 x_email_type := 'PON_AUC_PO_CREATE_PO_SUCCESS';
4792
4793 END IF;
4794
4795 END IF; --}
4796
4797 EXCEPTION --}
4798
4799 WHEN OTHERS THEN
4800
4801 if(l_exception_enabled = 'Y') then
4802 log_error(++l_progress || l_api_name || ' FATAL EXCEPTION in main try-catch block for generate pos '
4803 || substrb(SQLERRM, 1, 2500));
4804 end if;
4805
4806 rollback;
4807
4808 l_resultout := 'FAILURE';
4809
4810 UPDATE PON_AUCTION_HEADERS_ALL
4811 SET OUTCOME_STATUS = 'OUTCOME_FAILED'
4812 WHERE AUCTION_HEADER_ID = p_auction_header_id;
4813
4814 x_email_type := 'PON_AUC_PO_CREATE_PO_FAIL';
4815
4816 END;
4817
4818 if(l_debug_enabled = 'Y') then
4819 x_progress := ++l_progress || l_api_name || ' : invoke start_po_workflow for email ' || x_email_type;
4820 log_message(x_progress);
4821
4822 log_message(++l_progress || l_api_name || '3. Invoke START_PO_WORKFLOW for ' || p_auction_header_id
4823 || ' initiated by ' || p_user_name || ' for email ' || x_email_type || ' at '
4824 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss'));
4825 END IF;
4826
4827 /* if we have reached here, check the status so far and set the out parameter accordingly */
4828
4829 IF (l_resultout = 'SUCCESS') THEN
4830 p_resultout := 'S';
4831 ELSE
4832 p_resultout := 'F';
4833 END IF;
4834
4835 /* before we invoke the workflow, lets commit everything -
4836 if there was an exception, we have updated the status as well
4837 */
4838
4839 COMMIT;
4840
4841 BEGIN
4842
4843 -- Get next value in sequence for itemkey
4844
4845 SELECT pon_auction_wf_createpo_s.nextval
4846 INTO x_sequence
4847 FROM dual;
4848
4849 x_itemkey := (to_char(p_auction_header_id)||'-'||to_char(x_sequence));
4850
4851 -- update pon_auction_headers_all.wf_poncompl_current_round by incrementing by one
4852 -- update pon_bid_headers.po_wf_creation_rnd by incrementing by one
4853
4854 UPDATE pon_auction_headers_all set
4855 wf_poncompl_item_key = x_itemkey,
4856 wf_poncompl_current_round = x_current_round+1,
4857 last_update_date = sysdate
4858 WHERE auction_header_id = p_auction_header_id;
4859
4860 UPDATE pon_bid_headers set
4861 po_wf_creation_rnd = x_current_round+1
4862 WHERE auction_header_id = p_auction_header_id;
4863
4864 log_message(++l_progress || l_api_name || '. invoke start_po_workflow for itemkey=' || x_itemkey);
4865
4866 START_PO_WORKFLOW(p_auction_header_id ,
4867 p_user_name ,
4868 p_user_id ,
4869 p_formatted_name ,
4870 p_auction_title ,
4871 p_organization_name ,
4872 x_email_type ,
4873 x_itemkey ,
4874 x_allocation_error ,
4875 x_line_number ,
4876 x_item_number ,
4877 x_item_description ,
4878 x_item_revision ,
4879 x_requisition_number ,
4880 x_job_name ,
4881 x_document_disp_line_number );
4882
4883
4884 log_message(++l_progress || l_api_name || '3. AFTER START_PO_WORKFLOW for ' || p_auction_header_id
4885 || ' initiated by ' || p_user_name || ' for email ' || x_email_type || ' at '
4886 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss') || ' with p_resultout = '
4887 || p_resultout);
4888
4889
4890 EXCEPTION
4891 WHEN OTHERS THEN
4892
4893 if(l_exception_enabled = 'Y') then
4894 log_error(++l_progress || l_api_name || '3. EXCEPTION DURING START_PO_WORKFLOW for '
4895 || p_auction_header_id || ' initiated by ' || p_user_name || ' for email '
4896 || x_email_type || ' at ' || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss')
4897 || ' with p_resultout = ' || p_resultout || ' exception = '
4898 || substrb(SQLERRM, 1 , 500));
4899 end if;
4900
4901 /* our plan is to simply ignore this exception during create PO - we should not
4902 affect creation of a PO if we are not able to send an email - simply report it as a
4903 warning
4904 */
4905 l_workflow_failure := 'Y';
4906
4907 END;
4908
4909 -- Raise Business Event
4910 PON_BIZ_EVENTS_PVT.RAISE_PO_CREATION_INIT_EVENT (
4911 p_api_version => 1.0 ,
4912 p_init_msg_list => FND_API.G_FALSE,
4913 p_commit => FND_API.G_FALSE,
4914 p_auction_header_id => p_auction_header_id,
4915 p_user_name => p_user_name,
4916 p_requisition_based_flag => x_requistion_based,
4917 x_return_status => x_return_status,
4918 x_msg_count => x_msg_count,
4919 x_msg_data => x_msg_data);
4920
4921 -- finally, check whether our workflows behaved badly
4922 -- we will display a warning on the PO summary page
4923 -- perform this check only if all proceedings were successful
4924 if(l_workflow_failure = 'Y') then
4925 if(p_resultout = 'S') then
4926 p_resultout := 'W';
4927 end if;
4928 end if;
4929
4930 log_message(++l_progress || l_api_name || '4.Finished PO Creation for ' || p_auction_header_id
4931 || ' initiated by ' || p_user_name || ' for email ' || x_email_type || ' at '
4932 || to_char(sysdate, 'Dy DD-Mon-YYYY hh24:mi:ss')
4933 || ' with final p_resultout=' || p_resultout);
4934
4935 END START_PO_CREATION;
4936
4937 END PON_AUCTION_CREATE_PO_PKG;