[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_NEG_F_C
Source
1 PACKAGE BODY POA_DBI_NEG_F_C AS
2 /* $Header: poadbinegfrefb.pls 120.11.12000000.2 2007/02/27 14:41:15 sriswami ship $ */
3 g_init boolean := false;
4
5 /* PUBLIC PROCEDURE */
6 PROCEDURE initial_load (
7 errbuf OUT NOCOPY VARCHAR2,
8 retcode OUT NOCOPY NUMBER
9 )
10 IS
11 l_poa_schema VARCHAR2(30);
12 l_status VARCHAR2(30);
13 l_industry VARCHAR2(30);
14 l_stmt VARCHAR2(4000);
15 BEGIN
16 IF (fnd_installation.get_app_info('POA', l_status, l_industry, l_poa_schema)) THEN
17 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_NEG_F';
18 EXECUTE immediate l_stmt;
19
20 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_NEG_INC';
21 EXECUTE immediate l_stmt;
22
23 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_NEG_RATES';
24 EXECUTE immediate l_stmt;
25
26 g_init := TRUE;
27 populate_neg_facts (errbuf, retcode);
28 END IF;
29
30 EXCEPTION
31 WHEN others THEN
32 errbuf:= sqlerrm;
33 retcode:=sqlcode;
34 ROLLBACK;
35 poa_log.debug_line('Initial_load' || sqlerrm || sqlcode || sysdate);
36 raise_application_error(-20000,'Stack Dump Follows =>', true);
37 END initial_load;
38
39 /* PUBLIC PROCEDURE */
40 PROCEDURE populate_neg_facts(
41 errbuf OUT NOCOPY VARCHAR2,
42 retcode OUT NOCOPY NUMBER
43 )
44 IS
45 l_no_batch NUMBER;
46 l_go_ahead boolean := FALSE;
47 l_count NUMBER := 0;
48 l_poa_schema VARCHAR2(30);
49 l_status VARCHAR2(30);
50 l_industry VARCHAR2(30);
51 l_stmt VARCHAR2(4000);
52 l_start_date VARCHAR2(22);
53 l_end_date VARCHAR2(22);
54 l_glob_date VARCHAR2(22);
55 l_ret NUMBER;
56 l_batch_size NUMBER;
57 l_start_time DATE;
58 l_login NUMBER;
59 l_user NUMBER;
60 l_dop NUMBER := 1;
61 d_start_date DATE;
62 d_end_date DATE;
63 d_glob_date DATE;
64 l_rate_type VARCHAR2(30);
65 l_srate_type VARCHAR2(30);
66 l_sec_cur_yn NUMBER;
67 l_global_cur_code gl_sets_of_books.currency_code%type;
68 l_sglobal_cur_code gl_sets_of_books.currency_code%type;
69 BEGIN
70 errbuf :=NULL;
71 retcode:=0;
72 l_batch_size := bis_common_parameters.get_batch_size(10);
73 l_rate_type := bis_common_parameters.get_rate_type;
74 l_global_cur_code := bis_common_parameters.get_currency_code;
75 l_sglobal_cur_code := bis_common_parameters.get_secondary_currency_code;
76 l_srate_type := bis_common_parameters.get_secondary_rate_type;
77 if(poa_currency_pkg.display_secondary_currency_yn)
78 then
79 l_sec_cur_yn := 1;
80 else
81 l_sec_cur_yn := 0;
82 end if;
83
84 dbms_application_info.set_module(module_name => 'DBI NEGOTIATION COLLECT', action_name => 'start');
85 l_dop := bis_common_parameters.get_degree_of_parallelism;
86 -- default DOP to profile in EDW_PARALLEL_SRC if 2nd param is not passed
87 l_go_ahead := bis_collection_utilities.setup('POADBINEGF');
88
89 IF (g_init)
90 then
91 execute immediate 'alter session set hash_area_size=104857600';
92 execute immediate 'alter session set sort_area_size=104857600';
93 END IF;
94
95 IF (NOT l_go_ahead) THEN
96 errbuf := fnd_message.get;
97 raise_application_error (-20000, 'Error in SETUP: ' || errbuf);
98 END IF;
99 bis_collection_utilities.g_debug := false;
100
101 -- --------------------------------------------
102 -- Taking care of cases where the input from/to
103 -- date is NULL.
104 -- --------------------------------------------
105
106 IF (g_init) THEN
107 l_start_date := To_char(bis_common_parameters.get_global_start_date,'YYYY/MM/DD HH24:MI:SS');
108 d_start_date := bis_common_parameters.get_global_start_date;
109 ELSE
110 l_start_date := '''' || to_char(fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('POADBINEGF'))-0.004,'YYYY/MM/DD HH24:MI:SS') || '''';
111 /* if there is not a success record in the bis refresh log, then we have to get the global start date as l_start_date*/
112 d_start_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('POADBINEGF'))-0.004;
113 END IF;
114
115
116 l_end_date := '''' || To_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') || '''';
117 d_end_date := SYSDATE;
118
119
120 bis_collection_utilities.log( 'The collection range is from '||
121 l_start_date ||' to '|| l_end_date, 0);
122
123
124 IF (l_batch_size is null) THEN
125 l_batch_size := 10000;
126 END IF;
127
128 bis_collection_utilities.log('Truncate Currency Conversion table: '|| 'Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
129 IF (fnd_installation.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
130 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_NEG_INC';
131 EXECUTE IMMEDIATE l_stmt;
132 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_NEG_RATES';
133 EXECUTE IMMEDIATE l_stmt;
134 END IF;
135
136 dbms_application_info.set_action('inc');
137 bis_collection_utilities.log('Populate Currency Conversion table '|| 'Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
138 l_glob_date := '''' || To_char(bis_common_parameters.get_global_start_date, 'YYYY/MM/DD HH24:MI:SS') || '''';
139 d_glob_date := bis_common_parameters.get_global_start_date;
140
141 IF (g_init) THEN
142 INSERT /*+ append parallel(poa_dbi_neg_inc) */ INTO poa_dbi_neg_inc
143 (
144 primary_key,
145 batch_id,
146 txn_cur_code,
147 func_cur_code,
148 rate_date
149 )
150 (
151 SELECT /*+ PARALLEL(ponh) PARALLEL(pfsp) PARALLEL(pgl)
152 USE_HASH(ponh) USE_HASH(pfsp) */
153 ponh.auction_header_id,
154 1 batch_id,
155 ponh.currency_code txn_cur_code,
156 pgl.currency_code func_cur_code,
157 nvl(trunc(ponh.rate_date),trunc(ponh.creation_date)) rate_date
158 FROM
159 pon_auction_headers_all ponh,
160 financials_system_params_all pfsp,
161 gl_sets_of_books pgl,
162 pon_auc_doctypes doctype
163 WHERE
164 ponh.auction_status = 'AUCTION_CLOSED'
165 and ponh.auction_type = 'REVERSE' /* Only Reverse (Buyer) auctions considered */
166 and (ponh.award_complete_date is not null /* Only Award Complete Negotiations are considered */
167 OR (doctype.internal_name = 'REQUEST_FOR_INFORMATION' AND ponh.award_status='QUALIFIED')) /* Only Completed RFIs will be included */
168 and ponh.org_id = pfsp.org_id
169 and pfsp.set_of_books_id = pgl.set_of_books_id
170 and ponh.doctype_id = doctype.doctype_id
171 and doctype.transaction_type = 'REVERSE' /* Redundant Filter condition as PONH is taken care */
172 and ponh.creation_date > d_glob_date
173 and ponh.last_update_date between d_start_date and d_end_date
174 );
175 ELSE
176 INSERT /*+ append */ INTO poa_dbi_neg_inc
177 (
178 primary_key,
179 batch_id,
180 txn_cur_code,
181 func_cur_code,
182 rate_date
183 )
184 (
185 (
186 SELECT
187 ponh.auction_header_id,
188 1 batch_id,
189 ponh.currency_code txn_cur_code,
190 pgl.currency_code func_cur_code,
191 nvl(trunc(ponh.rate_date),trunc(ponh.creation_date)) rate_date
192 FROM
193 pon_auction_headers_all ponh,
194 financials_system_params_all pfsp,
195 gl_sets_of_books pgl,
196 pon_auc_doctypes doctype
197 WHERE
198 nvl(ponh.auction_status,'DRAFT') <> 'DRAFT'
199 and ponh.auction_type = 'REVERSE' /* Forward Auctions not considered */
200 and (ponh.award_complete_date is not null /* Only Award Complete Negotiations are considered */
201 OR (doctype.internal_name = 'REQUEST_FOR_INFORMATION' AND ponh.award_status='QUALIFIED')) /* Only Completed RFIs will be included */
202 and ponh.org_id = pfsp.org_id
203 and pfsp.set_of_books_id = pgl.set_of_books_id
204 and ponh.doctype_id = doctype.doctype_id
205 and doctype.transaction_type = 'REVERSE'
206 and ponh.creation_date > d_glob_date
207 and ponh.last_update_date between d_start_date and d_end_date
208 )
209 UNION
210 (
211 SELECT /*+ cardinality(ponbh,1) */
212 distinct
213 ponh.auction_header_id,
214 1 batch_id,
215 ponh.currency_code txn_cur_code,
216 pgl.currency_code func_cur_code,
217 nvl(trunc(ponh.rate_date),trunc(ponh.creation_date)) rate_date
218 FROM
219 pon_auction_headers_all ponh,
220 pon_bid_headers ponbh,
221 financials_system_params_all pfsp,
222 gl_sets_of_books pgl,
223 pon_auc_doctypes doctype
224 WHERE
225 nvl(ponh.auction_status,'DRAFT') <> 'DRAFT'
226 and ponh.auction_type = 'REVERSE' /* Forward Auctions not considered */
227 and ponh.award_complete_date is not null /* Only Award Complete Negotiations are considered */
228 and ponh.auction_header_id = ponbh.auction_header_id
229 and ponh.org_id = pfsp.org_id
230 and pfsp.set_of_books_id = pgl.set_of_books_id
231 and ponh.doctype_id = doctype.doctype_id
232 and doctype.transaction_type = 'REVERSE'
233 and ponh.creation_date > d_glob_date
234 and ponbh.last_update_date between d_start_date and d_end_date
235 )
236 );
237 END IF;
238 COMMIT;
239 dbms_application_info.set_action('stats incremental');
240
241 IF (fnd_installation.get_app_info('POA', l_status, l_industry, l_poa_schema)) THEN
242 fnd_stats.gather_table_stats(ownname => l_poa_schema, tabname => 'POA_DBI_NEG_INC') ;
243 END IF;
244
245 INSERT /*+ APPEND */ INTO poa_dbi_neg_rates
246 (
247 txn_cur_code,
248 func_cur_code,
249 rate_date,
250 global_cur_conv_rate,
251 sglobal_cur_conv_rate
252 )
253 SELECT
254 txn_cur_code,
255 func_cur_code,
256 rate_date,
257 poa_currency_pkg.get_dbi_global_rate(
258 l_rate_type,
259 func_cur_code,
260 rate_date,
261 txn_cur_code
262 ) global_cur_conv_rate,
263 ( case when l_sec_cur_yn = 0 then null
264 else
265 poa_currency_pkg.get_dbi_sglobal_rate (
266 l_srate_type,
267 func_cur_code,
268 rate_date,
269 txn_cur_code
270 )
271 end
272 ) sglobal_cur_conv_rate
273 FROM
274 (
275 select distinct
276 txn_cur_code,
277 func_cur_code,
278 rate_date
279 from
280 poa_dbi_neg_inc
281 order by func_cur_code, rate_date
282 );
283
284 COMMIT;
285
286 dbms_application_info.set_action('stats rates');
287
288 IF (fnd_installation.get_app_info('POA', l_status, l_industry, l_poa_schema)) THEN
289 fnd_stats.gather_table_stats(ownname => l_poa_schema,
290 tabname => 'POA_DBI_NEG_RATES') ;
291 END IF;
292
293 bis_collection_utilities.log('Populate base table: '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
294 select max(batch_id), COUNT(1) into l_no_batch, l_count from poa_dbi_neg_inc;
295 bis_collection_utilities.log('Identified '|| l_count ||' changed records. Batch size='|| l_batch_size || '. # of Batches=' || l_no_batch
296 || '. Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
297
298
299 /* missing currency handling */
300
301 IF (poa_currency_pkg.g_missing_cur) THEN
302 poa_currency_pkg.g_missing_cur := false;
303 errbuf := 'There are missing currencies\n';
304 raise_application_error (-20000, 'Error in INC table collection: ' || errbuf);
305 END IF;
306
307 l_start_time := sysdate; -- should be the end date of the collection??
308 l_login := fnd_global.login_id;
309 l_user := fnd_global.user_id;
310 dbms_application_info.set_action('collect');
311 IF (l_no_batch is NOT NULL) then
312 IF (g_init) THEN
313 bis_collection_utilities.log('Initial Load - using one batch approach, populate base fact. '|| 'Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
314 INSERT /*+ append parallel(t) */ INTO poa_dbi_neg_f t (
315 t.auction_header_id , /* Auction Header ID */
316 t.auction_line_number, /* Auction Line Number */
317 t.bid_number, /* Awarded Bid Number */
318 t.bid_line_number, /* Awarded Bid Line Number */
319 t.doctype_id, /* Is it an Auction/RFQ/Offer */
320 t.auction_round_number, /* Auction Round Number */
321 t.prev_round_auction_header_id, /* Previous Round Auction Header ID */
322 t.auction_creation_date, /* Auction Creation Date */
323 t.publish_date, /* Published Date */
324 t.open_bidding_date, /* Opened for Bidding Date */
325 t.close_bidding_date, /* Closed for Bidding Date */
326 t.prev_round_close_date, /* Previous Round Close Bidding Date */
327 t.next_round_creation_date, /* Next Round Creation Date */
328 t.award_date, /* Award Date */
329 t.award_complete_date, /* Award Complete Date */
330 t.rfi_complete_date, /* RFI Complete Date */
331 t.org_id, /* OU ID */
332 t.negotiation_creator_id, /* Negotiation Creator ID */
333 t.category_id, /* Category ID */
334 t.po_item_id, /* PO Item ID */
335 t.supplier_id, /* Winning Supplier ID */
336 t.supplier_site_id, /* Winning Supplier Site ID */
337 t.quantity, /* Requested Quantity */
338 t.award_qty, /* Awarded Quantity */
339 t.award_price, /* Awarded Price */
340 t.current_price, /* Current Price of that Vendor */
341 t.award_amount_t, /* Award Amount in transactional currency */
342 t.award_amount_b, /* Award Amount in functional currency */
343 t.award_amount_g, /* Award Amount in global currency */
344 t.award_amount_sg, /* Award Amount in secondary global currency */
345 t.current_amount_t, /* Current Amount in transactional currency */
346 t.current_amount_b, /* Current Amount in functional currency */
347 t.current_amount_g, /* Current Amount in global currency */
348 t.current_amount_sg, /* Current Amount in secondary global currency */
349 t.line_type_id, /* Line Type of the Sourcing Line */
350 t.order_type_lookup_code, /* Value basis of the Sourcing Line */
351 t.auction_status, /* Auction Status */
352 t.award_status, /* Award Status */
353 t.allocation_status, /* Allocation Status */
354 t.received_bid_count, /* No. of Bids Received for this Sourcing Line */
355 t.supplier_invite_date, /* Date on which the Supplier was invited */
356 t.contract_type, /* Outcome Document STANDARD/BLANKET */
357 t.po_header_id, /* PO Header ID of the Outcome Document */
358 -- t.requisition_header_id, /* Backing Requisition Header ID */
359 -- t.requisition_line_id, /* Backing Requisition Line ID */
360 t.func_cur_code, /* Functional Currency Code */
361 t.func_cur_conv_rate, /* Functional Currency Conversion Rate */
362 t.global_cur_conv_rate, /* Global Currency Conversion Rate */
363 t.sglobal_cur_conv_rate, /* Secondary Global Currency Conversion Rate */
364 t.base_uom, /* Base UOM */
365 t.transaction_uom, /* Transaction UOM */
366 t.base_uom_conv_rate, /* Base UOM conversion rate */
367 t.created_by, /* WHO Column */
368 t.last_update_login, /* WHO Column */
369 t.creation_date, /* WHO Column */
370 t.last_updated_by, /* WHO Column */
371 t.last_update_date /* WHO Column */
372 )
373 SELECT
374 s.auction_header_id,
375 s.auction_line_number,
376 s.bid_number,
377 s.bid_line_number,
378 s.doctype_id,
379 s.auction_round_number,
380 s.prev_round_auction_header_id,
381 s.current_round_creation_date creation_date,
382 s.publish_date,
383 s.open_bidding_date,
384 s.close_bidding_date,
385 s.prev_round_close_date,
386 s.next_round_creation_date,
387 s.award_date,
388 s.award_complete_date,
389 s.rfi_complete_date,
390 s.org_id,
391 s.negotiation_creator_id,
392 s.category_id,
393 s.po_item_id,
394 s.supplier_id,
395 s.supplier_site_id,
396 decode(s.order_type_lookup_code,'QUANTITY',s.quantity * s.base_uom_conv_rate, to_number(null)),
397 decode(s.order_type_lookup_code,'QUANTITY',s.award_qty * s.base_uom_conv_rate, to_number(null)),
398 (s.award_price / s.base_uom_conv_rate),
399 (s.current_price / s.base_uom_conv_rate),
400 decode(s.award_status, 'COMPLETED', decode(s.order_type_lookup_code, 'QUANTITY',s.award_price * s.award_qty, s.award_price), null),
401 decode(s.award_status,'COMPLETED' , decode(s.order_type_lookup_code, 'QUANTITY',s.award_price * s.award_qty * s.func_cur_conv_rate, s.award_price * s.func_cur_conv_rate)
402 ,null),
403 decode(s.award_status,'COMPLETED', decode(s.order_type_lookup_code, 'QUANTITY',
404 decode(s.global_cur_conv_rate, 0, s.award_price * s.award_qty, s.award_price * s.award_qty * s.func_cur_conv_rate * s.global_cur_conv_rate),
405 decode(s.global_cur_conv_rate, 0, s.award_price, s.award_price * s.func_cur_conv_rate * s.global_cur_conv_rate)), null),
406 decode(s.award_status,'COMPLETED', decode(s.order_type_lookup_code, 'QUANTITY',
407 decode(s.sglobal_cur_conv_rate, 0, s.award_price * s.award_qty, s.award_price * s.award_qty * s.func_cur_conv_rate * s.sglobal_cur_conv_rate),
408 decode(s.sglobal_cur_conv_rate, 0, s.award_price, s.award_price * s.func_cur_conv_rate * s.sglobal_cur_conv_rate)), null),
409 decode(s.order_type_lookup_code, 'QUANTITY', s.current_price * s.award_qty, s.current_price),
410 decode(s.order_type_lookup_code, 'QUANTITY', s.current_price * s.award_qty * s.func_cur_conv_rate, s.current_price * s.func_cur_conv_rate),
411 decode(s.order_type_lookup_code, 'QUANTITY',
412 decode(s.global_cur_conv_rate, 0, s.current_price * s.award_qty, s.current_price * s.award_qty * s.func_cur_conv_rate * s.global_cur_conv_rate),
413 decode(s.global_cur_conv_rate, 0, s.current_price, s.current_price * s.func_cur_conv_rate * s.global_cur_conv_rate)),
414 decode(s.order_type_lookup_code, 'QUANTITY',
415 decode(s.sglobal_cur_conv_rate, 0, s.current_price * s.award_qty, s.current_price * s.award_qty * s.func_cur_conv_rate * s.sglobal_cur_conv_rate),
416 decode(s.sglobal_cur_conv_rate, 0, s.current_price, s.current_price * s.func_cur_conv_rate * s.sglobal_cur_conv_rate)),
417 s.line_type_id,
418 s.order_type_lookup_code,
419 s.auction_status,
420 s.award_status,
421 s.allocation_status,
422 s.received_bid_count,
423 s.supplier_invite_date,
424 s.contract_type,
425 s.po_header_id,
426 -- s.requisition_header_id,
427 -- s.requisition_line_id,
428 s.func_cur_code,
429 s.func_cur_conv_rate,
430 s.global_cur_conv_rate,
431 s.sglobal_cur_conv_rate,
432 decode(s.order_type_lookup_code,'QUANTITY', s.base_uom, null),
433 s.transaction_uom,
434 s.base_uom_conv_rate,
435 l_user,
436 l_login,
437 l_start_time,
438 l_user,
439 l_start_time
440 FROM
441 (
442 SELECT /*+ PARALLEL(inc) PARALLEL(ponh) USE_HASH(inc) USE_HASH(ponh) */
443 ponh_multi.auction_header_id,
444 ponip.line_number auction_line_number,
445 ponbh.bid_number bid_number,
446 ponbip.line_number bid_line_number,
447 ponh_multi.doctype_id,
448 ponh_multi_orig.creation_date current_round_creation_date,
449 ponh_prev.close_bidding_date prev_round_close_date,
450 ponh_next.creation_date next_round_creation_date,
451 nvl(ponh_multi.auction_round_number,1) auction_round_number,
452 ponh_prev.auction_header_id prev_round_auction_header_id,
453 ponh_multi_orig.publish_date,
454 ponh_multi_orig.open_bidding_date,
455 ponh_multi.close_bidding_date,
456 ponh_multi.award_date,
457 decode(doctype.internal_name, 'REQUEST_FOR_INFORMATION', to_date(null), ponh.award_complete_date) award_complete_date,
458 ponh.org_id,
459 hz.person_identifier negotiation_creator_id,
460 ponip.category_id,
461 poa_dbi_items_pkg.getitemkey(ponip.item_id, ppar.master_organization_id, ponip.category_id, NULL, NULL, ponip.item_description) po_item_id,
462 decode(ponh_multi.award_status,'QUALIFIED', -99, 'NO', -99, ponbh.vendor_id) supplier_id,
463 decode(ponh_multi.award_status,'QUALIFIED', -99, 'NO', -99, ponbh.vendor_site_id) supplier_site_id,
464 ponip.order_type_lookup_code,
465 decode(ponh_multi.award_status,'NO',null, 'QUALIFIED', null, decode(ponh.contract_type,'CONTRACT', to_number(null), ponip.quantity)) quantity,
466 decode(ponh_multi.award_status,'NO',null, 'QUALIFIED', null, decode(ponh.contract_type, 'CONTRACT', to_number(null), ponbip.award_quantity)) award_qty,
467 decode(ponh_multi.award_status,'NO',null, 'QUALIFIED', null, decode(ponh.contract_type, 'CONTRACT', to_number(null), ponip.current_price)) current_price,
468 decode(ponh_multi.award_status,'NO',null, 'QUALIFIED', null, decode(ponbip.award_status,'AWARDED',ponbip.award_price,null)) award_price,
469 ponip.line_type_id,
470 ponh.auction_status,
471 nvl(ponh_multi.award_status,ponbip.award_status) award_status,
472 decode(ponh_multi.award_status,'NO',NULL,ponip.allocation_status) allocation_status,
473 decode(ponh_multi.award_status,'NO',NULL,ponip.number_of_bids) received_bid_count,
474 decode(ponh_multi.award_status,'NO', to_date(null), nvl(ponbp.creation_date,ponh_multi_orig.publish_date)) supplier_invite_date,
475 ponh.contract_type,
476 ponbh.po_header_id,
477 -- ponreq.requisition_header_id, /* Placeholder */
478 -- ponreq.requisition_line_id, /* Placeholder */
479 decode(ponip.item_id, null, uom.unit_of_measure, pitem.primary_unit_of_measure) base_uom,
480 uom.unit_of_measure transaction_uom,
481 decode(
482 ponip.item_id,
483 null, 1,
484 decode(uom.unit_of_measure,
485 pitem.primary_unit_of_measure, 1,
486 poa_dbi_uom_pkg.convert_to_item_base_uom(
487 ponip.item_id,
488 ppar.master_organization_id,
489 uom.unit_of_measure,
490 pitem.primary_uom_code
491 )
492 )
493 ) base_uom_conv_rate,
494 rat.func_cur_code func_cur_code,
495 nvl(ponh.rate,1) func_cur_conv_rate,
496 rat.global_cur_conv_rate,
497 rat.sglobal_cur_conv_rate,
498 decode(doctype.internal_name, 'REQUEST_FOR_INFORMATION', nvl(ponh.award_complete_date, ponh.last_update_date), null) rfi_complete_date
499 FROM
500 poa_dbi_neg_inc inc,
501 poa_dbi_neg_rates rat,
502 pon_auction_headers_all ponh,
503 pon_auction_item_prices_all ponip,
504 pon_bid_headers ponbh,
505 pon_bid_item_prices ponbip,
506 pon_bidding_parties ponbp,
507 -- pon_backing_requisitions ponreq,
508 financials_system_params_all pfsp,
509 mtl_parameters ppar,
510 mtl_system_items pitem,
511 gl_sets_of_books pgl,
512 hz_parties hz,
513 mtl_units_of_measure uom,
514 pon_auc_doctypes doctype,
515 pon_auction_headers_all ponh_multi,
516 pon_auction_headers_all ponh_multi_orig,
517 pon_auction_headers_all ponh_prev,
518 pon_auction_headers_all ponh_next
519 WHERE
520 inc.primary_key = ponh.auction_header_id
521 and (ponh.award_complete_date is not null /* Only Published Negotiations are considered */
522 OR (doctype.internal_name = 'REQUEST_FOR_INFORMATION' AND ponh.award_status='QUALIFIED')) /* Only Completed RFIs will be included */
523 and ponh.auction_header_id = ponip.auction_header_id
524 and decode(ponh.award_status, 'QUALIFIED', null, ponh.auction_header_id) = ponbh.auction_header_id(+) /* Include only the Auction Record of RFI and not the Responses */
525 and ponbh.auction_header_id = ponbip.auction_header_id(+) /* For Bidded Transactions Only */
526 and ponbh.bid_number = ponbip.bid_number(+)
527 and nvl(ponbip.line_number,ponip.line_number) = ponip.line_number /* Filter to give unique record */
528 and ponbh.auction_header_id = ponbp.auction_header_id(+)
529 and ponbh.trading_partner_id = ponbp.trading_partner_id(+)
530 and ponbh.vendor_site_id = ponbp.vendor_site_id(+)
531 and nvl(ponbh.bid_status,'ACTIVE') = 'ACTIVE' /* If a Supplier changes bids, they store ARCHIVED. Ignore them. */
532 and nvl(ponbip.award_status,'-999') <> 'REJECTED' /* Cannot be NULL or REJECTED */
533 -- and ponip.auction_header_id = ponreq.auction_header_id(+) /* If Backing Requisition is available */
534 -- and ponip.line_number = ponreq.line_number(+) /* If Backing Requisition is available */
535 and ponh_multi.doctype_id = doctype.doctype_id /* Join to get document type, particulary for RFI */
536 and doctype.transaction_type = 'REVERSE' /* Redundant Filter condition as PONH is taken care */
537 and ponh.org_id = pfsp.org_id
538 and pfsp.set_of_books_id = pgl.set_of_books_id
539 and pfsp.inventory_organization_id = ppar.organization_id
540 and ponip.uom_code = uom.uom_code(+)
541 and ponip.item_id = pitem.inventory_item_id(+)
542 and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
543 and inc.txn_cur_code = rat.txn_cur_code
544 and inc.func_cur_code = rat.func_cur_code
545 and inc.rate_date = rat.rate_date
546 and ponh.trading_partner_contact_id = hz.party_id
547 and ponh_multi.auction_header_id_orig_round = ponh.auction_header_id_orig_round
548 and ponh_multi_orig.auction_header_id = ponh_multi.auction_header_id_orig_amend
549 and nvl(ponh_multi.auction_header_id_prev_round, ponh_multi.auction_header_id) = ponh_prev.auction_header_id
550 and ponh_multi.auction_header_id = ponh_next.auction_header_id_prev_round(+)
551 and nvl(ponh_next.auction_status,'AUCTION_CLOSED')='AUCTION_CLOSED'
552 and ponh_multi.auction_status = 'AUCTION_CLOSED' /* Check that it cannot be ACTIVE */
553 and ponip.group_type IN ('LINE', 'LOT', 'GROUP_LINE') /* Do not involve Lot Lines and Group */
554 and ponh.creation_date > d_glob_date
555 )s;
556 COMMIT;
557 ELSE
558 -- Incremental load (process in batches)
559 bis_collection_utilities.log('incremental collection');
560 FOR v_batch_no IN 1..l_no_batch LOOP
561 bis_collection_utilities.log('batch no='||v_batch_no || ' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 1);
562
563 MERGE INTO poa_dbi_neg_f t using
564 ( SELECT /*+ cardinality(inc,1) */
565 ponh_multi.auction_header_id,
566 ponip.line_number auction_line_number,
567 ponbh.bid_number bid_number,
568 ponbip.line_number bid_line_number,
569 ponh_multi.doctype_id,
570 ponh_multi_orig.creation_date current_round_creation_date,
571 ponh_prev.close_bidding_date prev_round_close_date,
572 ponh_next.creation_date next_round_creation_date,
573 nvl(ponh_multi.auction_round_number,1) auction_round_number,
574 ponh_prev.auction_header_id prev_round_auction_header_id,
575 ponh_multi_orig.publish_date,
576 ponh_multi_orig.open_bidding_date,
577 ponh_multi.close_bidding_date,
578 ponh_multi.award_date,
579 decode(doctype.internal_name, 'REQUEST_FOR_INFORMATION', to_date(null), ponh.award_complete_date) award_complete_date,
580 ponh.org_id,
581 hz.person_identifier negotiation_creator_id,
582 ponip.category_id,
583 poa_dbi_items_pkg.getitemkey(ponip.item_id, ppar.master_organization_id, ponip.category_id, NULL, NULL, ponip.item_description) po_item_id,
584 decode(ponh_multi.award_status,'QUALIFIED', -99, 'NO', -99, ponbh.vendor_id) supplier_id,
585 decode(ponh_multi.award_status,'QUALIFIED', -99, 'NO', -99, ponbh.vendor_site_id) supplier_site_id,
586 ponip.order_type_lookup_code,
587 decode(ponh_multi.award_status,'NO',null, 'QUALIFIED', null, decode(ponh.contract_type,'CONTRACT', to_number(null), ponip.quantity)) quantity,
588 decode(ponh_multi.award_status,'NO',null, 'QUALIFIED', null, decode(ponh.contract_type, 'CONTRACT', to_number(null), ponbip.award_quantity)) award_qty,
589 decode(ponh_multi.award_status,'NO',null, 'QUALIFIED', null, decode(ponh.contract_type, 'CONTRACT', to_number(null), ponip.current_price)) current_price,
590 decode(ponh_multi.award_status,'NO',null, 'QUALIFIED', null, decode(ponbip.award_status,'AWARDED',ponbip.award_price,null)) award_price,
591 ponip.line_type_id,
592 ponh.auction_status,
593 --decode(ponbh.bid_status,'ARCHIVED','DELETE',nvl(ponbip.award_status,ponh.award_status)) award_status,
594 nvl(ponh_multi.award_status,ponbip.award_status) award_status,
595 decode(ponh_multi.award_status,'NO',null,ponip.allocation_status) allocation_status,
596 decode(ponh_multi.award_status,'NO',null,ponip.number_of_bids) received_bid_count,
597 decode(ponh_multi.award_status,'NO', to_date(null), nvl(ponbp.creation_date, ponh_multi_orig.publish_date)) supplier_invite_date,
598 ponh.contract_type,
599 ponbh.po_header_id,
600 -- ponreq.requisition_header_id,
601 -- ponreq.requisition_line_id,
602 decode(ponip.item_id, null, uom.unit_of_measure, pitem.primary_unit_of_measure) base_uom,
603 uom.unit_of_measure transaction_uom,
604 decode(
605 ponip.item_id,
606 null, 1,
607 decode(uom.unit_of_measure,
608 pitem.primary_unit_of_measure, 1,
609 poa_dbi_uom_pkg.convert_to_item_base_uom(
610 ponip.item_id,
611 ppar.master_organization_id,
612 uom.unit_of_measure,
613 pitem.primary_uom_code
614 )
615 )
616 ) base_uom_conv_rate,
617 rat.func_cur_code func_cur_code,
618 nvl(ponh.rate,1) func_cur_conv_rate,
619 rat.global_cur_conv_rate,
620 rat.sglobal_cur_conv_rate,
621 nvl(ponbip.award_status, ponh.award_status) bid_award_status, /* RFI has only ponh information */
622 ponbh.bid_status bid_status,
623 decode(doctype.internal_name, 'REQUEST_FOR_INFORMATION', nvl(ponh.award_complete_date, ponh.last_update_date), null) rfi_complete_date
624 FROM
625 poa_dbi_neg_inc inc,
626 poa_dbi_neg_rates rat,
627 pon_auction_headers_all ponh,
628 pon_auction_item_prices_all ponip,
629 pon_bid_headers ponbh,
630 pon_bid_item_prices ponbip,
631 pon_bidding_parties ponbp,
632 -- pon_backing_requisitions ponreq,
633 financials_system_params_all pfsp,
634 mtl_parameters ppar,
635 mtl_system_items pitem,
636 gl_sets_of_books pgl,
637 hz_parties hz,
638 mtl_units_of_measure uom,
639 pon_auc_doctypes doctype,
640 pon_auction_headers_all ponh_multi,
641 pon_auction_headers_all ponh_multi_orig,
642 pon_auction_headers_all ponh_prev,
643 pon_auction_headers_all ponh_next
644 WHERE
645 inc.primary_key = ponh.auction_header_id
646 and (ponh.award_complete_date is not null /* Only Published Negotiations are considered */
647 OR (doctype.internal_name = 'REQUEST_FOR_INFORMATION' AND ponh.award_status='QUALIFIED')) /* Only Completed RFIs will be included */
648 and ponh.auction_header_id = ponip.auction_header_id
649 and decode(ponh.award_status, 'QUALIFIED', null, ponh.auction_header_id) = ponbh.auction_header_id(+) /* Include only the Auction Record of RFI and not the Responses */
650 and ponbh.auction_header_id = ponbip.auction_header_id(+) /* For Bidded Transactions Only */
651 and ponbh.bid_number = ponbip.bid_number(+)
652 and nvl(ponbip.line_number,ponip.line_number) = ponip.line_number /* Filter to give unique record */
653 and ponbh.auction_header_id = ponbp.auction_header_id(+) /* Join to Bidding Parties to get Supplier Invite Date */
654 and ponbh.trading_partner_id = ponbp.trading_partner_id(+)
655 and ponbh.vendor_site_id = ponbp.vendor_site_id(+)
656 and nvl(ponbh.bid_status,'ACTIVE') = 'ACTIVE' /* If a Supplier changes bids, they store ARCHIVED. Ignore them. */
657 and nvl(ponbip.award_status,'-999') <> 'REJECTED' /* Cannot be NULL or REJECTED */
658 -- and ponip.auction_header_id = ponreq.auction_header_id(+) /* If Backing Requisition is available */
659 -- and ponip.line_number = ponreq.line_number(+) /* If Backing Requisition is available */
660 and ponh_multi.doctype_id = doctype.doctype_id /* Join to get document type, particulary for RFI */
661 and doctype.transaction_type = 'REVERSE'
662 and ponh.org_id = pfsp.org_id
663 and pfsp.set_of_books_id = pgl.set_of_books_id
664 and pfsp.inventory_organization_id = ppar.organization_id
665 and ponip.uom_code = uom.uom_code(+)
666 and ponip.item_id = pitem.inventory_item_id(+)
667 and inc.txn_cur_code = rat.txn_cur_code
668 and inc.func_cur_code = rat.func_cur_code
669 and inc.rate_date = rat.rate_date
670 and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
671 and ponh.trading_partner_contact_id = hz.party_id
672 and ponh_multi.auction_header_id_orig_round = ponh.auction_header_id_orig_round
673 and ponh_multi_orig.auction_header_id = ponh_multi.auction_header_id_orig_amend
674 and nvl(ponh_multi.auction_header_id_prev_round, ponh_multi.auction_header_id) = ponh_prev.auction_header_id
675 and ponh_multi.auction_header_id = ponh_next.auction_header_id_prev_round(+)
676 and nvl(ponh_next.auction_status,'AUCTION_CLOSED')='AUCTION_CLOSED'
677 and ponh_multi.auction_status = 'AUCTION_CLOSED'
678 and ponip.group_type IN ('LINE', 'LOT', 'GROUP_LINE') /* Do not involve Lot Lines and Group */
679 and ponh.creation_date > d_glob_date
680 ) s
681 ON ( t.auction_header_id=s.auction_header_id
682 and t.auction_line_number=s.auction_line_number
683 and nvl(t.bid_number,-99) = nvl(s.bid_number,-99) /* RFI has NULL in this column */
684 and nvl(t.bid_line_number,-99) = nvl(s.bid_line_number,-99) /* RFI has NULL in this column */
685 ) /* These 4 would give unique records */
686 WHEN MATCHED THEN UPDATE SET
687 t.doctype_id = s.doctype_id,
688 t.auction_round_number = s.auction_round_number,
689 t.prev_round_auction_header_id = s.prev_round_auction_header_id,
690 t.auction_creation_date = s.current_round_creation_date,
691 t.publish_date = s.publish_date,
692 t.open_bidding_date = s.open_bidding_date,
693 t.close_bidding_date = s.close_bidding_date,
694 t.prev_round_close_date = s.prev_round_close_date,
695 t.next_round_creation_date = s.next_round_creation_date,
696 t.award_date = s.award_date,
697 t.award_complete_date = s.award_complete_date,
698 t.rfi_complete_date = s.rfi_complete_date,
699 t.org_id = s.org_id,
700 t.negotiation_creator_id = s.negotiation_creator_id,
701 t.category_id = s.category_id,
702 t.po_item_id = s.po_item_id,
703 t.supplier_id = s.supplier_id,
704 t.supplier_site_id = s.supplier_site_id,
705 t.quantity =
706 (case
707 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
708 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
709 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
710 decode(s.order_type_lookup_code,'QUANTITY',s.quantity * s.base_uom_conv_rate, to_number(null))
711 else
712 null
713 end
714 ),
715 t.award_qty =
716 (case
717 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
718 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
719 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
720 decode(s.order_type_lookup_code,'QUANTITY',s.award_qty * s.base_uom_conv_rate, to_number(null))
721 else
722 null
723 end
724 ),
725 t.award_price =
726 (case
727 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
728 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
729 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
730 s.award_price / s.base_uom_conv_rate
731 else
732 null
733 end
734 ),
735 t.current_price =
736 (case
737 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
738 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
739 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
740 s.current_price / s.base_uom_conv_rate
741 else
742 null
743 end
744 ),
745 t.award_amount_t =
746 (case
747 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
748 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
749 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
750 decode(s.award_status,
751 'COMPLETED',decode(s.order_type_lookup_code, 'QUANTITY', s.award_price * s.award_qty, s.award_price),
752 null
753 )
754 else
755 null
756 end
757 ),
758 t.award_amount_b =
759 (case
760 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
761 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
762 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
763 decode(s.award_status,
764 'COMPLETED', decode(s.order_type_lookup_code, 'QUANTITY',s.award_price * s.award_qty * s.func_cur_conv_rate, s.award_price * s.func_cur_conv_rate),
765 null
766 )
767 else
768 null
769 end
770 ),
771 t.award_amount_g =
772 (case
773 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
774 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
775 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
776 decode(s.award_status,
777 'COMPLETED',
778 decode(s.order_type_lookup_code, 'QUANTITY',
779 decode(s.global_cur_conv_rate, 0, s.award_price * s.award_qty, s.award_price * s.award_qty * s.func_cur_conv_rate * s.global_cur_conv_rate),
780 decode(s.global_cur_conv_rate, 0, s.award_price, s.award_price * s.func_cur_conv_rate * s.global_cur_conv_rate)),
781 null
782 )
783 else
784 null
785 end
786 ),
787 t.award_amount_sg =
788 (case
789 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
790 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
791 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
792 decode(s.award_status,
793 'COMPLETED',
794 decode(s.order_type_lookup_code, 'QUANTITY',
795 decode(s.sglobal_cur_conv_rate, 0,s.award_price * s.award_qty, s.award_price * s.award_qty * s.func_cur_conv_rate * s.sglobal_cur_conv_rate)),
796 null
797 )
798 else
799 null
800 end
801 ),
802 t.current_amount_t =
803 (case
804 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
805 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
806 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
807 decode(s.order_type_lookup_code,'QUANTITY',s.current_price * s.award_qty, s.current_price)
808 else
809 null
810 end
811 ),
812 t.current_amount_b =
813 (case
814 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
815 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
816 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
817 decode(s.order_type_lookup_code, 'QUANTITY',s.current_price * s.award_qty * s.func_cur_conv_rate, s.current_price * s.func_cur_conv_rate)
818 else
819 null
820 end
821 ),
822 t.current_amount_g =
823 (case
824 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
825 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
826 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
827 decode(s.order_type_lookup_code, 'QUANTITY',
828 decode(s.global_cur_conv_rate, 0, s.current_price * s.award_qty, s.current_price * s.award_qty * s.func_cur_conv_rate * s.global_cur_conv_rate),
829 decode(s.global_cur_conv_rate,0, s.current_price, s.current_price * s.func_cur_conv_rate * s.global_cur_conv_rate))
830 else
831 null
832 end
833 ),
834 t.current_amount_sg =
835 (case
836 when nvl(s.auction_status,'-999') IN ('ACTIVE','AUCTION_CLOSED') /* Check if COMMIT_ACTIVE needs to be included here */
837 and nvl(s.bid_status,'ACTIVE') = 'ACTIVE'
838 and nvl(s.bid_award_status,'-999') <> 'REJECTED' THEN
839 decode(s.order_type_lookup_code, 'QUANTITY',
840 decode(s.sglobal_cur_conv_rate, 0, s.current_price * s.award_qty, s.current_price * s.award_qty * s.func_cur_conv_rate * s.sglobal_cur_conv_rate),
841 decode(s.sglobal_cur_conv_rate, 0, s.current_price , s.current_price * s.func_cur_conv_rate * s.sglobal_cur_conv_rate))
842 else
843 null
844 end
845 ),
846 t.line_type_id = s.line_type_id,
847 t.order_type_lookup_code = s.order_type_lookup_code,
848 t.auction_status = s.auction_status,
849 t.award_status = s.award_status,
850 t.allocation_status = s.allocation_status,
851 t.supplier_invite_date = s.supplier_invite_date,
852 t.contract_type = s.contract_type,
853 t.po_header_id = s.po_header_id,
854 -- t.requisition_header_id = s.requisition_header_id,
855 -- t.requisition_line_id = s.requisition_line_id,
856 t.func_cur_code = s.func_cur_code,
857 t.func_cur_conv_rate = s.func_cur_conv_rate,
858 t.global_cur_conv_rate = s.global_cur_conv_rate,
859 t.sglobal_cur_conv_rate = s.sglobal_cur_conv_rate,
860 t.base_uom = s.base_uom,
861 t.transaction_uom = s.transaction_uom,
862 t.base_uom_conv_rate = s.base_uom_conv_rate,
863 t.last_update_login = l_login,
864 t.last_updated_by = l_user,
865 t.last_update_date = l_start_time
866 WHEN NOT MATCHED THEN INSERT
867 (
868 t.auction_header_id , /* Auction Header ID */
869 t.auction_line_number, /* Auction Line Number */
870 t.bid_number, /* Awarded Bid Number */
871 t.bid_line_number, /* Awarded Bid Line Number */
872 t.doctype_id, /* Is it an Auction/RFQ/Offer */
873 t.auction_round_number, /* Auction Round Number */
874 t.prev_round_auction_header_id, /* Previous Round Auction Header ID */
875 t.auction_creation_date, /* Auction Creation Date */
876 t.publish_date, /* Published Date */
877 t.open_bidding_date, /* Opened for Bidding Date */
878 t.close_bidding_date, /* Closed for Bidding Date */
879 t.prev_round_close_date, /* Previous Round Close Bidding Date */
880 t.next_round_creation_date, /* Next Round Creation Date */
881 t.award_date, /* Award Date */
882 t.award_complete_date, /* Award Complete Date */
883 t.rfi_complete_date, /* RFI Complete Date */
884 t.org_id, /* OU ID */
885 t.negotiation_creator_id, /* Negotiation Creator ID */
886 t.category_id, /* Category ID */
887 t.po_item_id, /* PO Item ID */
888 t.supplier_id, /* Winning Supplier ID */
889 t.supplier_site_id, /* Winning Supplier Site ID */
890 t.quantity, /* Requested Quantity */
891 t.award_qty, /* Awarded Quantity */
892 t.award_price, /* Awarded Price */
893 t.current_price, /* Current Price */
894 t.award_amount_t, /* Awarded Amount in transactional currency */
895 t.award_amount_b, /* Awarded Amount in functional currency */
896 t.award_amount_g, /* Awarded Amount in global currency */
897 t.award_amount_sg, /* Awarded Amount in secondary global currency */
898 t.current_amount_t, /* Current Amount in transactional currency */
899 t.current_amount_b, /* Current Amount in functional currency */
900 t.current_amount_g, /* Current Amount in global currency */
901 t.current_amount_sg, /* Current Amount in secondary global currency */
902 t.line_type_id, /* Line Type of the Sourcing Line */
903 t.order_type_lookup_code, /* Value basis of the Sourcing Line */
904 t.auction_status, /* Auction Status */
905 t.award_status, /* Award Status */
906 t.allocation_status, /* Allocation Status */
907 t.received_bid_count, /* No. of Bids Received for this Sourcing Line */
908 t.supplier_invite_date, /* Date on which Supplier was Invited */
909 t.contract_type, /* Outcome Document STANDARD/BLANKET */
910 t.po_header_id, /* PO Header ID of the Outcome Document */
911 -- t.requisition_header_id, /* Backing Requisition Header ID */
912 -- t.requisition_line_id, /* Backing Requisition Line ID */
913 t.func_cur_code, /* Functional Currency Code */
914 t.func_cur_conv_rate, /* Functional Currency Conversion Rate */
915 t.global_cur_conv_rate, /* Global Currency Conversion Rate */
916 t.sglobal_cur_conv_rate, /* Secondary Global Currency Conversion Rate */
917 t.base_uom, /* Base UOM */
918 t.transaction_uom, /* Transaction UOM */
919 t.base_uom_conv_rate, /* Base UOM conversion rate */
920 t.created_by, /* WHO Column */
921 t.last_update_login, /* WHO Column */
922 t.creation_date, /* WHO Column */
923 t.last_updated_by, /* WHO Column */
924 t.last_update_date /* WHO Column */
925 ) VALUES
926 (
927 s.auction_header_id,
928 s.auction_line_number,
929 s.bid_number,
930 s.bid_line_number,
931 s.doctype_id,
932 s.auction_round_number,
933 s.prev_round_auction_header_id,
934 s.current_round_creation_date,
935 s.publish_date,
936 s.open_bidding_date,
937 s.close_bidding_date,
938 s.prev_round_close_date,
939 s.next_round_creation_date,
940 s.award_date,
941 s.award_complete_date,
942 s.rfi_complete_date,
943 s.org_id,
944 s.negotiation_creator_id,
945 s.category_id,
946 s.po_item_id,
947 s.supplier_id,
948 s.supplier_site_id,
949 decode(s.order_type_lookup_code,'QUANTITY',s.quantity * s.base_uom_conv_rate, to_number(null)),
950 decode(s.order_type_lookup_code,'QUANTITY',s.award_qty * s.base_uom_conv_rate, to_number(null)),
951 s.award_price / s.base_uom_conv_rate,
952 s.current_price / s.base_uom_conv_rate,
953 decode(s.award_status,
954 'COMPLETED', decode(s.order_type_lookup_code, 'QUANTITY',s.award_price * s.award_qty, s.award_price),
955 'QUALIFIED',0,
956 null
957 ),
958 decode(s.award_status,
959 'COMPLETED', decode(s.order_type_lookup_code, 'QUANTITY',s.award_price * s.award_qty * s.func_cur_conv_rate, s.award_price * s.func_cur_conv_rate),
960 'QUALIFIED',0,
961 null
962 ),
963 decode(s.award_status,
964 'COMPLETED',
965 decode(s.order_type_lookup_code, 'QUANTITY',
966 decode(s.global_cur_conv_rate, 0, s.award_price * s.award_qty, s.award_price * s.award_qty * s.func_cur_conv_rate * s.global_cur_conv_rate),
967 decode(s.global_cur_conv_rate, 0, s.award_price, s.award_price * s.func_cur_conv_rate * s.global_cur_conv_rate)),
968 'QUALIFIED',0,
969 null
970 ),
971 decode(s.award_status,
972 'COMPLETED' ,
973 decode(s.order_type_lookup_code, 'QUANTITY',
974 decode(s.sglobal_cur_conv_rate, 0, s.award_price * s.award_qty, s.award_price * s.award_qty * s.func_cur_conv_rate * s.sglobal_cur_conv_rate),
975 decode(s.sglobal_cur_conv_rate, 0, s.award_price, s.award_price * s.func_cur_conv_rate * s.sglobal_cur_conv_rate)),
976 'QUALIFIED',0,
977 null
978 ),
979 decode(s.order_type_lookup_code, 'QUANTITY', s.current_price * s.award_qty, s.current_price),
980 decode(s.order_type_lookup_code, 'QUANTITY', s.current_price * s.award_qty * s.func_cur_conv_rate, s.current_price * s.func_cur_conv_rate),
981 decode(s.order_type_lookup_code, 'QUANTITY',
982 decode(s.global_cur_conv_rate, 0, s.current_price * s.award_qty, s.current_price * s.award_qty * s.func_cur_conv_rate * s.global_cur_conv_rate),
983 decode(s.global_cur_conv_rate, 0, s.current_price, s.current_price * s.func_cur_conv_rate * s.global_cur_conv_rate)),
984 decode(s.order_type_lookup_code, 'QUANTITY',
985 decode(s.sglobal_cur_conv_rate, 0, s.current_price * s.award_qty, s.current_price * s.award_qty * s.func_cur_conv_rate * s.sglobal_cur_conv_rate),
986 decode(s.sglobal_cur_conv_rate, 0, s.current_price, s.current_price * s.func_cur_conv_rate * s.sglobal_cur_conv_rate)),
987 s.line_type_id,
988 s.order_type_lookup_code,
989 s.auction_status,
990 s.award_status,
991 s.allocation_status,
992 s.received_bid_count,
993 s.supplier_invite_date,
994 s.contract_type,
995 s.po_header_id,
996 -- s.requisition_header_id,
997 -- s.requisition_line_id,
998 s.func_cur_code,
999 s.func_cur_conv_rate,
1000 s.global_cur_conv_rate,
1001 s.sglobal_cur_conv_rate,
1002 decode(s.order_type_lookup_code,'QUANTITY', s.base_uom, null),
1003 s.transaction_uom,
1004 s.base_uom_conv_rate,
1005 l_user,
1006 l_login,
1007 l_start_time,
1008 l_user,
1009 l_start_time
1010 );
1011 COMMIT;
1012 DBMS_APPLICATION_INFO.SET_ACTION('batch ' || v_batch_no || ' done');
1013 END LOOP;
1014 END IF;
1015 END IF;
1016 bis_collection_utilities.log('Collection complete '|| 'Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1017 bis_collection_utilities.wrapup(TRUE, l_count, 'POA_DBI_NEG_F COLLECTION SUCEEDED', to_date(l_start_date, '''YYYY/MM/DD HH24:MI:SS'''), To_date(l_end_date, '''YYYY/MM/DD HH24:MI:SS'''));
1018 g_init := false;
1019 dbms_application_info.set_module(null, null);
1020 EXCEPTION
1021 WHEN others THEN
1022 dbms_application_info.set_action('error');
1023 errbuf:=sqlerrm;
1024 retcode:=sqlcode;
1025 bis_collection_utilities.log('Collection failed with '||errbuf||':'||retcode||' Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1026 bis_collection_utilities.wrapup(FALSE, l_count, errbuf||':'||retcode, to_date(l_start_date, '''YYYY/MM/DD HH24:MI:SS'''), to_date(l_end_date, '''YYYY/MM/DD HH24:MI:SS'''));
1027 RAISE;
1028 END populate_neg_facts;
1029
1030 END POA_DBI_NEG_F_C;