[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_REQ_F_C
Source
1 PACKAGE BODY POA_DBI_REQ_F_C AS
2 /* $Header: poadbireqfrefb.pls 120.10 2006/07/24 11:34:11 sdiwakar noship $ */
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_REQ_F';
18 EXECUTE immediate l_stmt;
19
20 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_REQ_INC';
21 EXECUTE immediate l_stmt;
22
23 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_REQ_RATES';
24 EXECUTE immediate l_stmt;
25
26 g_init := TRUE;
27 populate_req_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_req_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 l_num_corrupt_rows number;
70 l_ret_variable boolean;
71 cursor corrupt_rows is
72 select
73 rpad(hr.name,30) name,
74 rpad(rhr.segment1,11) segment1,
75 rpad(rln.line_num,5) line_num,
76 pod.req_distribution_id,
77 rln.requisition_line_id
78 from
79 poa_dbi_req_inc inc,
80 po_requisition_headers_all rhr,
81 po_requisition_lines_all rln,
82 po_req_distributions_all rdn,
83 po_distributions_all pod,
84 po_headers_all poh,
85 po_doc_style_headers style,
86 hr_all_organization_units_tl hr
87 where inc.primary_key = rln.requisition_line_id
88 and rln.requisition_line_id = rdn.requisition_line_id
89 and rdn.distribution_id = pod.req_distribution_id
90 and pod.po_header_id = poh.po_header_id
91 and poh.style_id = style.style_id
92 and nvl(style.progress_payment_flag,'N') = 'N'
93 and rhr.requisition_header_id = rln.requisition_header_id
94 and rhr.org_id = hr.organization_id
95 and hr.language = userenv('LANG')
96 group by
97 pod.req_distribution_id,
98 rln.requisition_line_id,
99 hr.name,
100 rhr.segment1,
101 rln.line_num,
102 pod.req_distribution_id
103 having count(*) > 1
104 order by 1,2,3;
105 l_corrupt_record corrupt_rows%rowtype;
106 type corrupt_rec_table_type is table of l_corrupt_record%type;
107 corrupt_rec_table corrupt_rec_table_type;
108 BEGIN
109 errbuf :=NULL;
110 retcode:=0;
111 l_num_corrupt_rows := 0;
112 l_batch_size := bis_common_parameters.get_batch_size(10);
113 l_rate_type := bis_common_parameters.get_rate_type;
114 l_global_cur_code := bis_common_parameters.get_currency_code;
115 l_sglobal_cur_code := bis_common_parameters.get_secondary_currency_code;
116 l_srate_type := bis_common_parameters.get_secondary_rate_type;
117 if(poa_currency_pkg.display_secondary_currency_yn)
118 then
119 l_sec_cur_yn := 1;
120 else
121 l_sec_cur_yn := 0;
122 end if;
123
124 dbms_application_info.set_module(module_name => 'DBI REQ COLLECT', action_name => 'start');
125 l_dop := bis_common_parameters.get_degree_of_parallelism;
126 -- default DOP to profile in EDW_PARALLEL_SRC if 2nd param is not passed
127 l_go_ahead := bis_collection_utilities.setup('POAREQLN');
128
129 IF (g_init)
130 then
131 execute immediate 'alter session set hash_area_size=104857600';
132 execute immediate 'alter session set sort_area_size=104857600';
133 END IF;
134
135 IF (NOT l_go_ahead) THEN
136 errbuf := fnd_message.get;
137 raise_application_error (-20000, 'Error in SETUP: ' || errbuf);
138 END IF;
139 bis_collection_utilities.g_debug := false;
140
141 -- --------------------------------------------
142 -- Taking care of cases where the input from/to
143 -- date is NULL.
144 -- --------------------------------------------
145
146 IF (g_init) THEN
147 l_start_date := To_char(bis_common_parameters.get_global_start_date,'YYYY/MM/DD HH24:MI:SS');
148 d_start_date := bis_common_parameters.get_global_start_date;
149 ELSE
150 l_start_date := '''' || to_char(fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('POAREQLN'))-0.004,'YYYY/MM/DD HH24:MI:SS') || '''';
151 /* 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*/
152 d_start_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('POAREQLN'))-0.004;
153 END IF;
154
155
156 l_end_date := '''' || To_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') || '''';
157 d_end_date := SYSDATE;
158
159
160 bis_collection_utilities.log( 'The collection range is from '||
161 l_start_date ||' to '|| l_end_date, 0);
162
163
164 IF (l_batch_size is null) THEN
165 l_batch_size := 10000;
166 END IF;
167
168 bis_collection_utilities.log('Truncate Currency Conversion table: '|| 'Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
169 IF (fnd_installation.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
170 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_REQ_INC';
171 EXECUTE IMMEDIATE l_stmt;
172
173 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_REQ_RATES';
174 EXECUTE IMMEDIATE l_stmt;
175 END IF;
176
177 dbms_application_info.set_action('inc');
178 bis_collection_utilities.log('Populate Currency Conversion table '|| 'Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
179 l_glob_date := '''' || To_char(bis_common_parameters.get_global_start_date, 'YYYY/MM/DD HH24:MI:SS') || '''';
180 d_glob_date := bis_common_parameters.get_global_start_date;
181
182
183 IF (g_init) THEN
184 INSERT /*+ append parallel(poa_dbi_req_inc) */ INTO poa_dbi_req_inc
185 (
186 primary_key,
187 line_location_id,
188 batch_id,
189 txn_cur_code,
190 func_cur_code,
191 rate_date
192 )
193 ( SELECT /*+ PARALLEL(rhr) PARALLEL(rln) PARALLEL(poh) PARALLEL(pol)
194 PARALLEL(pll) PARALLEL(por) PARALLEL(pfsp) PARALLEL(rfsp) PARALLEL(pgl)
195 PARALLEL(rgl) USE_HASH(rhr) USE_HASH(rln) USE_HASH(poh) USE_HASH(pol)
196 USE_HASH(pll) USE_HASH(por) USE_HASH(pfsp) USE_HASH(rfsp) USE_HASH(pgl)
197 USE_HASH(rgl) */
198 rln.requisition_line_id primary_key,
199 pll.line_location_id,
200 1 batch_id,
201 decode(pll.line_location_id, null, nvl(rln.currency_code, rgl.currency_code),poh.currency_code) txn_cur_code,
202 decode(pll.line_location_id, null, rgl.currency_code, pgl.currency_code) func_cur_code,
203 decode(pll.line_location_id, null, nvl(trunc(rln.rate_date),trunc(rln.creation_date)), nvl(trunc(poh.rate_date), trunc(pll.creation_date))) rate_date
204 FROM
205 po_requisition_headers_all rhr,
206 po_requisition_lines_all rln,
207 po_req_distributions_all rdn,
208 po_headers_all poh,
209 po_lines_all pol,
210 po_line_locations_all pll,
211 po_distributions_all pod,
212 po_releases_all por,
213 financials_system_params_all pfsp,
214 financials_system_params_all rfsp,
215 gl_sets_of_books pgl,
216 gl_sets_of_books rgl
217 WHERE
218 rhr.authorization_status = 'APPROVED'
219 and rln.source_type_code = 'VENDOR'
220 and rln.requisition_header_id = rhr.requisition_header_id
221 and rln.creation_date >= d_glob_date
222 and nvl(rln.modified_by_agent_flag,'N') <> 'Y'
223 and nvl(rln.cancel_flag,'N')='N'
224 and nvl(rhr.contractor_status,'NOT_APPLICABLE') <> 'PENDING'
225 and nvl(rln.closed_code,'-999') <> 'FINALLY CLOSED'
226 and rln.org_id = rfsp.org_id
227 and rfsp.set_of_books_id = rgl.set_of_books_id
228 and rdn.requisition_line_id = rln.requisition_line_id
229 and rdn.distribution_id = pod.req_distribution_id (+)
230 and pod.line_location_id = pll.line_location_id (+)
231 and nvl(pll.shipment_type,'-99') <> 'PREPAYMENT'
232 and pll.po_release_id = por.po_release_id (+)
233 and pll.po_line_id = pol.po_line_id (+)
234 and pol.po_header_id = poh.po_header_id (+)
235 and pll.org_id = pfsp.org_id (+)
236 and pfsp.set_of_books_id = pgl.set_of_books_id (+)
237 and (rhr.last_update_date between d_start_date and d_end_date or
238 rln.last_update_date between d_start_date and d_end_date or
239 pll.last_update_date between d_start_date and d_end_date or
240 poh.last_update_date between d_start_date and d_end_date or
241 pol.last_update_date between d_start_date and d_end_date or
242 por.last_update_date between d_start_date and d_end_date )
243 group by
244 rln.requisition_line_id,
245 pll.line_location_id,
246 rln.currency_code,
247 rgl.currency_code,
248 poh.currency_code,
249 pgl.currency_code,
250 trunc(rln.rate_date),
251 trunc(rln.creation_date),
252 trunc(poh.rate_date),
253 trunc(pll.creation_date)
254 );
255
256 /* Have to do a commit here as check for corrupt rows need to be done.
257 */
258
259 commit;
260
261 /* After collection of poa_dbi_req_inc, run a check for corrupt data.
262 ** If corrupt data is found, print details in request output and
263 ** set the request to complete with warning.
264 */
265
266
267 open corrupt_rows;
268 fetch corrupt_rows bulk collect into corrupt_rec_table;
269 close corrupt_rows;
270 for i in 1..corrupt_rec_table.count loop
271 l_num_corrupt_rows := l_num_corrupt_rows + 1;
272 if (l_num_corrupt_rows = 1) then
273 fnd_file.put_line(fnd_file.output,'Corrupt Data Report');
274 fnd_file.put_line(fnd_file.output,'===================');
275 fnd_file.put_line(fnd_file.output,'Operating Unit Requisition Line');
276 fnd_file.put_line(fnd_file.output,'------------------------------ ----------- -----');
277 end if;
278 fnd_file.put_line(fnd_file.output,corrupt_rec_table(i).name||' '||corrupt_rec_table(i).segment1||' '||corrupt_rec_table(i).line_num);
279 end loop;
280 if (l_num_corrupt_rows > 0) then
281 bis_collection_utilities.log('-------------------------------------------------------------------------------------', 0);
282 bis_collection_utilities.log('This request has encountered corrupt data in PO tables.', 0);
283 bis_collection_utilities.log('There are one or more requisition distributions which are referred to by', 0);
284 bis_collection_utilities.log('multiple non-complex-work purchase order distributions. Please see the output of', 0);
285 bis_collection_utilities.log('this request for the list of such requisition lines.', 0);
286 bis_collection_utilities.log(' ', 0);
287 bis_collection_utilities.log('These requisition lines have not been collected into the fact and consequently', 0);
288 bis_collection_utilities.log('are not displayed in DBI reports', 0);
289 bis_collection_utilities.log(' ', 0);
290 bis_collection_utilities.log('If you need to collect and report on these records in DBI please fix this bad data',0);
291 bis_collection_utilities.log('and re-run the DBI initial load request set. If you do not need this data collected',0);
292 bis_collection_utilities.log('and reported in DBI you can ignore this warning. If you need help fixing this data',0);
293 bis_collection_utilities.log('please contact Oracle Support.',0);
294 bis_collection_utilities.log('-------------------------------------------------------------------------------------', 0);
295
296 for i in 1..corrupt_rec_table.count loop
297 delete from poa_dbi_req_inc where primary_key = corrupt_rec_table(i).requisition_line_id;
298 end loop;
299 commit;
300 l_ret_variable := fnd_concurrent.set_interim_status(
301 status => 'WARNING',
302 message => 'Bad data found in PO tables.'
303 );
304 end if;
305 ELSE -- not initial load
306 INSERT /*+ APPEND */ INTO poa_dbi_req_inc
307 (
308 primary_key,
309 line_location_id,
310 batch_id,
311 txn_cur_code,
312 func_cur_code,
313 rate_date
314 )
315 select primary_key,
316 line_location_id,
317 batch_id,
318 txn_cur_code,
319 func_cur_code,
320 rate_date
321 from
322 (
323 (
324 (
325 SELECT /*+ cardinality(rhr, 1)*/
326 rln.requisition_line_id primary_key,
327 pll.line_location_id,
328 ceil(rownum/l_batch_size) batch_id,
329 decode(pll.line_location_id, null, nvl(rln.currency_code, rgl.currency_code),poh.currency_code) txn_cur_code,
330 decode(pll.line_location_id, null, rgl.currency_code, pgl.currency_code) func_cur_code,
331 decode(pll.line_location_id, null, nvl(trunc(rln.rate_date),trunc(rln.creation_date)), nvl(trunc(poh.rate_date), trunc(pll.creation_date))) rate_date
332 FROM
333 po_requisition_headers_all rhr,
334 po_requisition_lines_all rln,
335 po_headers_all poh,
336 po_line_locations_all pll,
337 financials_system_params_all pfsp,
338 financials_system_params_all rfsp,
339 gl_sets_of_books pgl,
340 gl_sets_of_books rgl,
341 po_req_distributions_all rdn,
342 po_distributions_all pod
343 WHERE rhr.authorization_status in ('APPROVED','CANCELLED','REJECTED','RETURNED')
344 and rln.source_type_code = 'VENDOR'
345 and rln.requisition_header_id = rhr.requisition_header_id
346 and nvl(rhr.contractor_status,'NOT_APPLICABLE') <> 'PENDING'
347 and pll.po_header_id = poh.po_header_id (+)
348 and rln.org_id = rfsp.org_id
349 and rfsp.set_of_books_id = rgl.set_of_books_id
350 and pll.org_id = pfsp.org_id (+)
351 and pfsp.set_of_books_id = pgl.set_of_books_id (+)
352 and rln.creation_date >= d_glob_date
353 and rdn.requisition_line_id = rln.requisition_line_id
354 and rdn.distribution_id = pod.req_distribution_id(+)
355 and pod.line_location_id = pll.line_location_id(+)
356 and nvl(pll.shipment_type,'-99') <> 'PREPAYMENT'
357 and rhr.last_update_date between d_start_date and d_end_date
358 )
359 UNION
360 (
361 SELECT /*+ cardinality(rln, 1)*/
362 rln.requisition_line_id primary_key,
363 pll.line_location_id,
364 ceil(rownum/l_batch_size) batch_id,
365 decode(pll.line_location_id, null, nvl(rln.currency_code, rgl.currency_code),poh.currency_code) txn_cur_code,
366 decode(pll.line_location_id, null, rgl.currency_code, pgl.currency_code) func_cur_code,
367 decode(pll.line_location_id, null, nvl(trunc(rln.rate_date),trunc(rln.creation_date)), nvl(trunc(poh.rate_date), trunc(pll.creation_date))) rate_date
368 FROM
369 po_requisition_headers_all rhr,
370 po_requisition_lines_all rln,
371 po_headers_all poh,
372 po_line_locations_all pll,
373 financials_system_params_all pfsp,
374 financials_system_params_all rfsp,
375 gl_sets_of_books pgl,
376 gl_sets_of_books rgl,
377 po_req_distributions_all rdn,
378 po_distributions_all pod
379 WHERE rhr.authorization_status in ('APPROVED','CANCELLED','REJECTED','RETURNED')
380 and rln.source_type_code = 'VENDOR'
381 and rln.requisition_header_id = rhr.requisition_header_id
382 and nvl(rhr.contractor_status,'NOT_APPLICABLE') <> 'PENDING'
383 and pll.po_header_id = poh.po_header_id (+)
384 and rln.org_id = rfsp.org_id
385 and rfsp.set_of_books_id = rgl.set_of_books_id
386 and pll.org_id = pfsp.org_id (+)
387 and pfsp.set_of_books_id = pgl.set_of_books_id (+)
388 and rln.creation_date >= d_glob_date
389 and rdn.requisition_line_id = rln.requisition_line_id
390 and rdn.distribution_id = pod.req_distribution_id(+)
391 and pod.line_location_id = pll.line_location_id(+)
392 and nvl(pll.shipment_type,'-99') <> 'PREPAYMENT'
393 and rln.last_update_date between d_start_date and d_end_date
394 )
395 UNION
396 (
397 SELECT /*+ cardinality(pll, 1)*/
398 rln.requisition_line_id primary_key,
399 pll.line_location_id,
400 ceil(rownum/l_batch_size) batch_id,
401 decode(pll.line_location_id, null, nvl(rln.currency_code, rgl.currency_code),poh.currency_code) txn_cur_code,
402 decode(pll.line_location_id, null, rgl.currency_code, pgl.currency_code) func_cur_code,
403 decode(pll.line_location_id, null, nvl(trunc(rln.rate_date),trunc(rln.creation_date)), nvl(trunc(poh.rate_date), trunc(pll.creation_date))) rate_date
404 FROM
405 po_requisition_headers_all rhr,
406 po_requisition_lines_all rln,
407 po_headers_all poh,
408 po_line_locations_all pll,
409 financials_system_params_all pfsp,
410 financials_system_params_all rfsp,
411 gl_sets_of_books pgl,
412 gl_sets_of_books rgl,
413 po_req_distributions_all rdn,
414 po_distributions_all pod
415 WHERE rhr.authorization_status in ('APPROVED','CANCELLED','REJECTED','RETURNED')
416 and rln.source_type_code = 'VENDOR'
417 and rln.requisition_header_id = rhr.requisition_header_id
418 and nvl(rhr.contractor_status,'NOT_APPLICABLE') <> 'PENDING'
419 and pll.po_header_id = poh.po_header_id
420 and rln.org_id = rfsp.org_id
421 and rfsp.set_of_books_id = rgl.set_of_books_id
422 and pll.org_id = pfsp.org_id
423 and pfsp.set_of_books_id = pgl.set_of_books_id
424 and rln.creation_date >= d_glob_date
425 and rdn.requisition_line_id = rln.requisition_line_id
426 and rdn.distribution_id = pod.req_distribution_id
427 and pod.line_location_id = pll.line_location_id
428 and nvl(pll.shipment_type,'-99') <> 'PREPAYMENT'
429 and pll.last_update_date between d_start_date and d_end_date
430 )
431 UNION
432 (
433 SELECT /*+ cardinality(poh, 1)*/
434 rln.requisition_line_id primary_key,
435 pll.line_location_id,
436 ceil(rownum/l_batch_size) batch_id,
437 decode(pll.line_location_id, null, nvl(rln.currency_code, rgl.currency_code),poh.currency_code) txn_cur_code,
438 decode(pll.line_location_id, null, rgl.currency_code, pgl.currency_code) func_cur_code,
439 decode(pll.line_location_id, null, nvl(trunc(rln.rate_date),trunc(rln.creation_date)), nvl(trunc(poh.rate_date), trunc(pll.creation_date))) rate_date
440 FROM
441 po_requisition_headers_all rhr,
442 po_requisition_lines_all rln,
443 po_headers_all poh,
444 po_line_locations_all pll,
445 financials_system_params_all pfsp,
446 financials_system_params_all rfsp,
447 gl_sets_of_books pgl,
448 gl_sets_of_books rgl,
449 po_req_distributions_all rdn,
450 po_distributions_all pod
451 WHERE rhr.authorization_status in ('APPROVED','CANCELLED','REJECTED','RETURNED')
452 and rln.source_type_code = 'VENDOR'
453 and rln.requisition_header_id = rhr.requisition_header_id
454 and nvl(rhr.contractor_status,'NOT_APPLICABLE') <> 'PENDING'
455 and pll.po_header_id = poh.po_header_id
456 and rln.org_id = rfsp.org_id
457 and rfsp.set_of_books_id = rgl.set_of_books_id
458 and pll.org_id = pfsp.org_id
459 and pfsp.set_of_books_id = pgl.set_of_books_id
460 and rln.creation_date >= d_glob_date
461 and rdn.requisition_line_id = rln.requisition_line_id
462 and rdn.distribution_id = pod.req_distribution_id
463 and pod.line_location_id = pll.line_location_id
464 and nvl(pll.shipment_type,'-99') <> 'PREPAYMENT'
465 and poh.last_update_date between d_start_date and d_end_date
466 )
467 UNION
468 (
469 SELECT /*+ cardinality(pol, 1)*/
470 rln.requisition_line_id primary_key,
471 pll.line_location_id,
472 ceil(rownum/l_batch_size) batch_id,
473 decode(pll.line_location_id, null, nvl(rln.currency_code, rgl.currency_code),poh.currency_code) txn_cur_code,
474 decode(pll.line_location_id, null, rgl.currency_code, pgl.currency_code) func_cur_code,
475 decode(pll.line_location_id, null, nvl(trunc(rln.rate_date),trunc(rln.creation_date)), nvl(trunc(poh.rate_date), trunc(pll.creation_date))) rate_date
476 FROM
477 po_requisition_headers_all rhr,
478 po_requisition_lines_all rln,
479 po_headers_all poh,
480 po_lines_all pol,
481 po_line_locations_all pll,
482 financials_system_params_all pfsp,
483 financials_system_params_all rfsp,
484 gl_sets_of_books pgl,
485 gl_sets_of_books rgl,
486 po_req_distributions_all rdn,
487 po_distributions_all pod
488 WHERE rhr.authorization_status in ('APPROVED','CANCELLED','REJECTED','RETURNED')
489 and rln.source_type_code = 'VENDOR'
490 and rln.requisition_header_id = rhr.requisition_header_id
491 and nvl(rhr.contractor_status,'NOT_APPLICABLE') <> 'PENDING'
492 and pll.po_line_id = pol.po_line_id
493 and pll.po_header_id = poh.po_header_id
494 and rln.org_id = rfsp.org_id
495 and rfsp.set_of_books_id = rgl.set_of_books_id
496 and pll.org_id = pfsp.org_id
497 and pfsp.set_of_books_id = pgl.set_of_books_id
498 and rln.creation_date >= d_glob_date
499 and rdn.requisition_line_id = rln.requisition_line_id
500 and rdn.distribution_id = pod.req_distribution_id
501 and pod.line_location_id = pll.line_location_id
502 and nvl(pll.shipment_type,'-99') <> 'PREPAYMENT'
503 and pol.last_update_date between d_start_date and d_end_date
504 )
505 UNION
506 (
507 SELECT /*+ cardinality(por, 1)*/
508 rln.requisition_line_id primary_key,
509 pll.line_location_id,
510 ceil(rownum/l_batch_size) batch_id,
511 decode(pll.line_location_id, null, nvl(rln.currency_code, rgl.currency_code),poh.currency_code) txn_cur_code,
512 decode(pll.line_location_id, null, rgl.currency_code, pgl.currency_code) func_cur_code,
513 decode(pll.line_location_id, null, nvl(trunc(rln.rate_date),trunc(rln.creation_date)), nvl(trunc(poh.rate_date), trunc(pll.creation_date))) rate_date
514 FROM
515 po_requisition_headers_all rhr,
516 po_requisition_lines_all rln,
517 po_headers_all poh,
518 po_line_locations_all pll,
519 po_releases_all por,
520 financials_system_params_all pfsp,
521 financials_system_params_all rfsp,
522 gl_sets_of_books pgl,
523 gl_sets_of_books rgl,
524 po_req_distributions_all rdn,
525 po_distributions_all pod
526 WHERE rhr.authorization_status in ('APPROVED','CANCELLED','REJECTED','RETURNED')
527 and rln.source_type_code = 'VENDOR'
528 and rln.requisition_header_id = rhr.requisition_header_id
529 and nvl(rhr.contractor_status,'NOT_APPLICABLE') <> 'PENDING'
530 and pll.po_release_id = por.po_release_id
531 and pll.po_header_id = poh.po_header_id
532 and rln.org_id = rfsp.org_id
533 and rfsp.set_of_books_id = rgl.set_of_books_id
534 and pll.org_id = pfsp.org_id
535 and pfsp.set_of_books_id = pgl.set_of_books_id
536 and rln.creation_date >= d_glob_date
537 and rdn.requisition_line_id = rln.requisition_line_id
538 and rdn.distribution_id = pod.req_distribution_id
539 and pod.line_location_id = pll.line_location_id
540 and nvl(pll.shipment_type,'-99') <> 'PREPAYMENT'
541 and por.last_update_date between d_start_date and d_end_date
542 )
543 )
544 UNION ALL
545 (
546 (
547 SELECT /*+ cardinality(rhr, 1)*/
548 rln.requisition_line_id primary_key,
549 null line_location_id,
550 ceil(rownum/l_batch_size) batch_id,
551 nvl(rln.currency_code,rgl.currency_code) txn_cur_code,
552 rgl.currency_code func_cur_code ,
553 nvl(trunc(rln.rate_date),trunc(rln.creation_date)) rate_date
554 FROM po_requisition_headers_all rhr,
555 po_requisition_lines_all rln,
556 financials_system_params_all rfsp,
557 gl_sets_of_books rgl
558 WHERE rhr.authorization_status='INCOMPLETE'
559 and rln.source_type_code = 'VENDOR'
560 and rln.requisition_header_id = rhr.requisition_header_id
561 and rhr.approved_date is not null
562 and nvl(rhr.contractor_status,'NOT_APPLICABLE')<>'PENDING'
563 and rln.org_id = rfsp.org_id
564 and rfsp.set_of_books_id = rgl.set_of_books_id
565 and rln.creation_date >= d_glob_date
566 and rhr.last_update_date between d_start_date and d_end_date
567 )
568 UNION
569 (
570 SELECT /*+ cardinality(rln, 1)*/
571 rln.requisition_line_id primary_key,
572 null line_location_id,
573 ceil(rownum/l_batch_size) batch_id,
574 nvl(rln.currency_code,rgl.currency_code) txn_cur_code,
575 rgl.currency_code func_cur_code,
576 nvl(trunc(rln.rate_date),trunc(rln.creation_date)) rate_date
577 FROM po_requisition_headers_all rhr,
578 po_requisition_lines_all rln,
579 financials_system_params_all rfsp,
580 gl_sets_of_books rgl
581 WHERE rhr.authorization_status='INCOMPLETE'
582 and rln.source_type_code = 'VENDOR'
583 and rln.requisition_header_id = rhr.requisition_header_id
584 and rhr.approved_date is not null
585 and nvl(rhr.contractor_status,'NOT_APPLICABLE')<>'PENDING'
586 and rln.org_id = rfsp.org_id
587 and rfsp.set_of_books_id = rgl.set_of_books_id
588 and rln.creation_date >= d_glob_date
589 and rln.last_update_date between d_start_date and d_end_date
590 )
591 )
592 )
593 group by
594 primary_key,
595 line_location_id,
596 batch_id,
597 txn_cur_code,
598 func_cur_code,
599 rate_date;
600 END IF;
601
602 COMMIT;
603 dbms_application_info.set_action('stats incremental');
604
605 IF (fnd_installation.get_app_info('POA', l_status, l_industry, l_poa_schema)) THEN
606 fnd_stats.gather_table_stats(ownname => l_poa_schema, tabname => 'POA_DBI_REQ_INC') ;
607 END IF;
608
609 INSERT /*+ APPEND */ INTO poa_dbi_req_rates
610 (
611 txn_cur_code,
612 func_cur_code,
613 rate_date,
614 global_cur_conv_rate,
615 sglobal_cur_conv_rate
616 )
617 SELECT
618 txn_cur_code,
619 func_cur_code,
620 rate_date,
621 poa_currency_pkg.get_dbi_global_rate(
622 l_rate_type,
623 func_cur_code,
624 rate_date,
625 txn_cur_code
626 ) global_cur_conv_rate,
627 ( case when l_sec_cur_yn = 0 then null
628 else
629 poa_currency_pkg.get_dbi_sglobal_rate (
630 l_srate_type,
631 func_cur_code,
632 rate_date,
633 txn_cur_code
634 )
635 end
636 ) sglobal_cur_conv_rate
637 FROM
638 (
639 select distinct
640 txn_cur_code,
641 func_cur_code,
642 rate_date
643 from
644 poa_dbi_req_inc
645 order by func_cur_code, rate_date
646 );
647
648
649 COMMIT;
650 dbms_application_info.set_action('stats rates');
651
652 IF (fnd_installation.get_app_info('POA', l_status, l_industry, l_poa_schema)) THEN
653 fnd_stats.gather_table_stats(ownname => l_poa_schema,
654 tabname => 'POA_DBI_REQ_RATES') ;
655 END IF;
656
657 bis_collection_utilities.log('Populate base table: '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
658 select max(batch_id), COUNT(1) into l_no_batch, l_count from poa_dbi_req_inc;
659 bis_collection_utilities.log('Identified '|| l_count ||' changed records. Batch size='|| l_batch_size || '. # of Batches=' || l_no_batch
660 || '. Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
661
662
663 /* missing currency handling */
664
665 IF (poa_currency_pkg.g_missing_cur) THEN
666 poa_currency_pkg.g_missing_cur := false;
667 errbuf := 'There are missing currencies\n';
668 raise_application_error (-20000, 'Error in INC table collection: ' || errbuf);
669 END IF;
670
671 l_start_time := sysdate; -- should be the end date of the collection??
672 l_login := fnd_global.login_id;
673 l_user := fnd_global.user_id;
674 dbms_application_info.set_action('collect');
675 if (l_no_batch is NOT NULL) then
676 IF (g_init) THEN
677 bis_collection_utilities.log('Initial Load - using one batch approach, populate base fact. '|| 'Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
678 INSERT /*+ append parallel(t) */ INTO poa_dbi_req_f t (
679 t.req_line_id ,
680 t.req_header_id ,
681 t.po_line_location_id ,
682 t.req_creation_ou_id,
683 t.req_creation_date ,
684 t.req_approved_date ,
685 t.po_creation_ou_id,
686 t.po_creation_date ,
687 t.po_submit_date ,
688 t.po_approved_date,
689 t.req_fulfilled_date,
690 t.expected_date,
691 t.supplier_id ,
692 t.supplier_site_id,
693 t.category_id,
694 t.po_item_id,
695 t.buyer_id,
696 t.org_id,
697 t.ship_to_org_id,
698 t.requester_id,
699 t.line_type_id,
700 t.preparer_id,
701 t.unit_price,
702 t.line_quantity,
703 t.line_amount_t,
704 t.line_amount_b,
705 t.line_amount_g,
706 t.line_amount_sg,
707 t.emergency_flag,
708 t.urgent_flag,
709 t.sourcing_flag,
710 t.include_in_ufr,
711 t.unproc_ped_flag,
712 t.po_revisions,
713 t.po_creation_method,
714 t.func_cur_code,
715 t.func_cur_conv_rate,
716 t.global_cur_conv_rate,
717 t.sglobal_cur_conv_rate,
718 t.base_uom,
719 t.transaction_uom,
720 t.base_uom_conv_rate,
721 t.created_by,
722 t.last_update_login,
723 t.creation_date,
724 t.last_updated_by,
725 t.last_update_date
726 )
727 SELECT
728 req_line_id,
729 req_header_id,
730 min(po_line_location_id) po_line_location_id,
731 req_creation_ou_id,
732 req_creation_date,
733 req_approved_date,
734 po_creation_ou_id,
735 min(po_creation_date),
736 max(po_submit_date),
737 decode(min(po_approved_flag),'Y',max(po_approved_date),to_date(null)) po_approved_date,
738 max(req_fulfilled_date),
739 min(expected_date),
740 supplier_id,
741 supplier_site_id,
742 category_id,
743 po_item_id,
744 buyer_id,
745 org_id,
746 ship_to_org_id,
747 requester_id,
748 line_type_id,
749 preparer_id,
750 sum(unit_price),
751 sum(line_quantity),
752 sum(line_amount_t),
753 sum(line_amount_b),
754 sum(line_amount_g),
755 sum(line_amount_sg),
756 emergency_flag,
757 urgent_flag,
758 sourcing_flag,
759 include_in_ufr,
760 max(unproc_ped_flag),
761 po_revisions,
762 po_creation_method,
763 func_cur_code,
764 func_cur_conv_rate,
765 global_cur_conv_rate,
766 sglobal_cur_conv_rate,
767 base_uom,
768 transaction_uom,
769 base_uom_conv_rate,
770 l_user,
771 l_login,
772 l_start_time,
773 l_user,
774 l_start_time
775 FROM
776 ( SELECT
777 s.req_line_id ,
778 s.req_header_id ,
779 s.po_line_location_id,
780 s.req_creation_ou_id,
781 s.req_creation_date,
782 s.req_approved_date,
783 s.po_creation_ou_id,
784 s.po_creation_date,
785 s.po_submit_date,
786 s.po_approved_date,
787 s.po_approved_flag,
788 decode(s.matching_basis, 'AMOUNT', to_date(null), s.req_fulfilled_date) req_fulfilled_date,
789 decode(s.matching_basis, 'AMOUNT', to_date(null), nvl(s.po_promised_date, nvl(s.po_need_by_date, s.req_need_by_date))) expected_date,
790 nvl(s.supplier_id,-1) supplier_id,
791 nvl(s.supplier_site_id,-1) supplier_site_id,
792 s.category_id,
793 s.po_item_id,
794 nvl(s.buyer_id,-1) buyer_id,
795 nvl(s.po_creation_ou_id,req_creation_ou_id) org_id,
796 s.ship_to_org_id,
797 s.requester_id,
798 s.line_type_id,
799 s.preparer_id,
800 (s.unit_price / s.base_uom_conv_rate) unit_price,
801 decode(s.order_type_lookup_code,'QUANTITY', s.line_quantity * s.base_uom_conv_rate, to_number(null)) line_quantity,
802 decode(s.matching_basis, 'AMOUNT', s.line_amount_t, s.unit_price * s.line_quantity) line_amount_t,
803 decode(s.matching_basis, 'AMOUNT', s.line_amount_t * s.func_cur_conv_rate, s.unit_price * s.line_quantity * s.func_cur_conv_rate) line_amount_b,
804 decode(
805 s.matching_basis, 'AMOUNT',
806 decode(s.global_cur_conv_rate, 0, s.line_amount_t, s.line_amount_t * s.func_cur_conv_rate * s.global_cur_conv_rate),
807 decode(s.global_cur_conv_rate, 0, s.unit_price * s.line_quantity, s.unit_price * s.line_quantity * s.func_cur_conv_rate * s.global_cur_conv_rate)
808 ) line_amount_g,
809 decode(
810 s.matching_basis, 'AMOUNT',
811 decode(s.sglobal_cur_conv_rate, 0, s.line_amount_t, s.line_amount_t * s.func_cur_conv_rate * s.sglobal_cur_conv_rate),
812 decode(s.sglobal_cur_conv_rate, 0, s.unit_price * s.line_quantity, s.unit_price * s.line_quantity * s.func_cur_conv_rate * s.sglobal_cur_conv_rate)
813 ) line_amount_sg,
814 s.emergency_flag,
815 s.urgent_flag,
816 s.sourcing_flag,
817 s.include_in_ufr,
818 s.po_revisions,
819 s.po_creation_method,
820 s.func_cur_code,
821 s.func_cur_conv_rate,
822 s.global_cur_conv_rate,
823 s.sglobal_cur_conv_rate,
824 decode(s.order_type_lookup_code,'QUANTITY', s.base_uom, null) base_uom,
825 s.transaction_uom,
826 s.base_uom_conv_rate,
827 ( case when (s.po_approved_date is null and
828 decode(s.matching_basis, 'AMOUNT', to_date(null),
829 nvl(s.po_promised_date, nvl(s.po_need_by_date,
830 s.req_need_by_date))) < l_start_time) then 'Y'
831 else 'N' end
832 ) unproc_ped_flag
833 FROM
834 ( SELECT /*+ PARALLEL(inc) PARALLEL(rln) PARALLEL(rhr) PARALLEL(poh)
835 PARALLEL(pol) PARALLEL(pll) PARALLEL(por) PARALLEL(pitem) PARALLEL(ritem)
836 PARALLEL(pod) PARALLEL(rdn) PARALLEL(rat) PARALLEL(pfsp) PARALLEL(rfsp)
837 PARALLEL(pgl) PARALLEL(rgl) USE_HASH(inc) USE_HASH(rln) USE_HASH(rhr)
838 USE_HASH(poh) USE_HASH(pol) USE_HASH(pll) USE_HASH(por) USE_HASH(pitem)
839 USE_HASH(ritem) USE_HASH(pod) USE_HASH(rdn) USE_HASH(rat) USE_HASH(pfsp)
840 USE_HASH(fsp) USE_HASH(pgl) USE_HASH(rgl)*/
841 rln.requisition_line_id req_line_id,
842 rhr.requisition_header_id req_header_id,
843 pll.line_location_id po_line_location_id,
844 rhr.org_id req_creation_ou_id,
845 rln.creation_date req_creation_date,
846 nvl(rhr.approved_date,rhr.creation_date) req_approved_date,
847 rln.need_by_date req_need_by_date,
848 decode(pll.po_release_id,null,poh.org_id,por.org_id) po_creation_ou_id,
849 pll.creation_date po_creation_date,
850 decode(pll.po_release_id,null,poh.submit_date,por.submit_date) po_submit_date,
851 decode(pll.approved_flag, 'Y', pll.approved_date, null) po_approved_date,
852 nvl(pll.approved_flag,'N') po_approved_flag,
853 ( case
854 when nvl(pll.consigned_flag,'N')='Y' or nvl(pll.vmi_flag,'N')='Y' then null
855 when nvl(style.progress_payment_flag,'N') = 'Y' then null
856 when nvl(pll.approved_flag,'N')='Y' then
857 case when nvl(pll.receipt_required_flag, 'N') = 'N'
858 and nvl(pll.inspection_required_flag, 'N') = 'N'
859 then least(nvl(pll.shipment_closed_date,pll.closed_for_invoice_date), pll.closed_for_invoice_date)
860 else least(nvl(pll.shipment_closed_date,pll.closed_for_receiving_date),pll.closed_for_receiving_date)
861 end
862 else
863 null
864 end
865 ) req_fulfilled_date,
866 pll.need_by_date po_need_by_date,
867 pll.promised_date po_promised_date,
868 nvl(poh.vendor_id, rln.vendor_id) supplier_id,
869 nvl(poh.vendor_site_id, rln.vendor_site_id) supplier_site_id,
870 nvl(pol.category_id, rln.category_id) category_id,
871 decode(
872 pll.line_location_id, null,
873 poa_dbi_items_pkg.getitemkey(rln.item_id, rpar.master_organization_id, rln.category_id, rln.suggested_vendor_product_code, rln.vendor_id, rln.item_description),
874 poa_dbi_items_pkg.getitemkey(pol.item_id, ppar.master_organization_id, pol.category_id, pol.vendor_product_num, poh.vendor_id, pol.item_description)
875 ) po_item_id,
876 nvl(decode(pll.po_release_id,null,poh.agent_id,por.agent_id),rln.suggested_buyer_id) buyer_id,
877 nvl(pll.ship_to_organization_id, rln.destination_organization_id) ship_to_org_id,
878 rln.to_person_id requester_id, --get the requester from the requisition itself since it can be changed on the PO distn
879 rln.line_type_id,
880 rhr.preparer_id,
881 nvl(pll.price_override, nvl(rln.currency_unit_price,rln.unit_price)) unit_price, -- in transactional currency
882 sum( case when pll.line_location_id is null then rdn.req_line_quantity
883 when pll.line_location_id is not null then
884 decode(pll.matching_basis,'QUANTITY',pod.quantity_ordered - nvl(pod.quantity_cancelled,0),0)
885 else null
886 end
887 ) line_quantity,
888 sum( case when pll.line_location_id is null then nvl(rdn.req_line_currency_amount,rdn.req_line_amount)
889 when pll.line_location_id is not null
890 then decode(pll.matching_basis,'AMOUNT',pod.amount_ordered - nvl(pod.amount_cancelled,0),0) -- Confirm if this amount is in transactional currency
891 else null
892 end
893 ) line_amount_t,
894 decode(rhr.emergency_po_num, null, 'N', 'Y') emergency_flag,
895 rln.urgent_flag,
896 ( case when nvl(rln.line_location_id,-999)=-999
897 and nvl(rln.cancel_flag,'N')='N'
898 and nvl(rhr.authorization_status,'-999')='APPROVED'
899 and (rln.at_sourcing_flag='Y' or
900 (rln.reqs_in_pool_flag='Y'
901 and nvl(rln.on_rfq_flag,'N')='Y'
902 and nvl(rln.auction_header_id,-999)=-999))
903 then 'Y'
904 when nvl(rln.line_location_id,-999)=-999
905 and nvl(rln.cancel_flag,'N')='N'
906 and nvl(rhr.authorization_status,'-999')='APPROVED'
907 and rln.reqs_in_pool_flag='Y'
908 then 'N'
909 else ''
910 end
911 ) sourcing_flag,
912 ( case when decode(pll.line_location_id,null,rln.matching_basis,pll.matching_basis)='AMOUNT' then 'N'
913 when (nvl(style.progress_payment_flag,'N') = 'Y') then 'N'
914 when nvl(pll.consigned_flag,'N')='Y' or nvl(pll.vmi_flag,'N')='Y' then 'N'
915 else 'Y'
916 end
917 ) include_in_ufr,
918 decode(pll.line_location_id,null,rln.matching_basis,pll.matching_basis) matching_basis,
919 decode(pll.line_location_id,null,rln.order_type_lookup_code,pll.value_basis) order_type_lookup_code,
920 decode(pll.po_release_id,null,poh.revision_num,por.revision_num) po_revisions,
921 ( case when decode(pll.po_release_id,null,poh.document_creation_method,por.document_creation_method) in ('ENTER_PO', 'ENTER_RELEASE', 'COPY_DOCUMENT', 'AUTOCREATE')
922 then 'M' else 'A' end) po_creation_method,
923 rat.func_cur_code func_cur_code,
924 decode(pll.line_location_id,null,nvl(rln.rate,1),nvl(poh.rate,1)) func_cur_conv_rate,
925 rat.global_cur_conv_rate,
926 rat.sglobal_cur_conv_rate,
927 decode(
928 pll.line_location_id, null,
929 decode(rln.item_id, null, rln.unit_meas_lookup_code, ritem.primary_unit_of_measure),
930 decode(pol.item_id, null, pol.unit_meas_lookup_code, pitem.primary_unit_of_measure)
931 ) base_uom,
932 decode(
933 pll.line_location_id, null,
934 rln.unit_meas_lookup_code,
935 pol.unit_meas_lookup_code
936 ) transaction_uom,
937 decode(
938 pll.line_location_id,
939 null, decode(
940 rln.item_id,
941 null, 1,
942 decode(rln.unit_meas_lookup_code,
943 ritem.primary_unit_of_measure, 1,
944 poa_dbi_uom_pkg.convert_to_item_base_uom(
945 rln.item_id,
946 rpar.master_organization_id,
947 rln.unit_meas_lookup_code,
948 ritem.primary_uom_code
949 )
950 )
951 ),
952 decode(
953 pol.item_id,
954 null, 1,
955 decode(
956 pol.unit_meas_lookup_code,
957 pitem.primary_unit_of_measure, 1,
958 poa_dbi_uom_pkg.convert_to_item_base_uom(
959 pol.item_id,
960 ppar.master_organization_id,
961 pol.unit_meas_lookup_code,
962 pitem.primary_uom_code
963 )
964 )
965 )
966 ) base_uom_conv_rate
967 FROM
968 poa_dbi_req_inc inc,
969 poa_dbi_req_rates rat,
970 po_requisition_lines_all rln,
971 po_req_distributions_all rdn,
972 po_headers_all poh,
973 po_lines_all pol,
974 po_releases_all por,
975 financials_system_params_all pfsp,
976 mtl_parameters ppar,
977 mtl_system_items pitem,
978 financials_system_params_all rfsp,
979 mtl_parameters rpar,
980 mtl_system_items ritem,
981 gl_sets_of_books pgl,
982 gl_sets_of_books rgl,
983 po_requisition_headers_all rhr,
984 po_line_locations_all pll,
985 po_distributions_all pod,
986 po_doc_style_headers style
987 WHERE
988 inc.primary_key = rln.requisition_line_id
989 and (inc.line_location_id is null or inc.line_location_id = pll.line_location_id)
990 and rln.requisition_header_id = rhr.requisition_header_id
991 and rln.requisition_line_id = rdn.requisition_line_id
992 and nvl(rln.cancel_flag,'N')='N'
993 and rdn.distribution_id = pod.req_distribution_id (+)
994 and pll.po_line_id = pol.po_line_id (+)
995 and pll.po_release_id = por.po_release_id (+)
996 and pol.po_header_id = poh.po_header_id (+)
997 and pod.line_location_id = pll.line_location_id(+)
998 and poh.style_id = style.style_id(+)
999 and poh.org_id = pfsp.org_id (+)
1000 and pfsp.set_of_books_id = pgl.set_of_books_id (+)
1001 and pfsp.inventory_organization_id = ppar.organization_id (+)
1002 and rhr.org_id = rfsp.org_id
1003 and rfsp.inventory_organization_id = rpar.organization_id
1004 and rfsp.set_of_books_id = rgl.set_of_books_id
1005 and rln.item_id = ritem.inventory_item_id (+)
1006 and rpar.master_organization_id = nvl(ritem.organization_id, rpar.master_organization_id)
1007 and pol.item_id = pitem.inventory_item_id (+)
1008 and inc.txn_cur_code = rat.txn_cur_code
1009 and inc.func_cur_code = rat.func_cur_code
1010 and inc.rate_date = rat.rate_date
1011 and nvl(ppar.master_organization_id, -999) = nvl(pitem.organization_id, nvl(ppar.master_organization_id, -999))
1012 and rhr.authorization_status = 'APPROVED'
1013 and rln.source_type_code = 'VENDOR'
1014 and nvl(rln.modified_by_agent_flag,'N') <> 'Y'
1015 and nvl(rhr.contractor_status,'NOT_APPLICABLE') <> 'PENDING'
1016 and rln.creation_date > d_glob_date
1017 group by
1018 pitem.primary_unit_of_measure,
1019 pitem.primary_uom_code,
1020 pll.amount,
1021 pll.amount_cancelled,
1022 pll.approved_date,
1023 pll.approved_flag,
1024 pll.closed_for_invoice_date,
1025 pll.closed_for_receiving_date,
1026 pll.consigned_flag,
1027 pll.creation_date,
1028 pll.inspection_required_flag,
1029 pll.line_location_id,
1030 pll.matching_basis,
1031 pll.need_by_date,
1032 pll.payment_type,
1033 pll.po_release_id,
1034 pll.price_override,
1035 pll.promised_date,
1036 pll.receipt_required_flag,
1037 pll.ship_to_organization_id,
1038 pll.shipment_closed_date,
1039 pll.value_basis,
1040 pll.vmi_flag,
1041 poh.agent_id,
1042 poh.document_creation_method,
1043 poh.org_id,
1044 poh.rate,
1045 poh.revision_num,
1046 poh.submit_date,
1047 poh.vendor_id,
1048 poh.vendor_site_id,
1049 pol.category_id,
1050 pol.item_description,
1051 pol.item_id,
1052 pol.matching_basis,
1053 pol.order_type_lookup_code,
1054 pol.unit_meas_lookup_code,
1055 pol.vendor_product_num,
1056 por.agent_id,
1057 por.document_creation_method,
1058 por.org_id,
1059 por.revision_num,
1060 por.submit_date,
1061 ppar.master_organization_id,
1062 rat.func_cur_code,
1063 rat.global_cur_conv_rate,
1064 rat.sglobal_cur_conv_rate,
1065 rhr.approved_date,
1066 rhr.authorization_status,
1067 rhr.creation_date,
1068 rhr.emergency_po_num,
1069 rhr.org_id,
1070 rhr.preparer_id,
1071 rhr.requisition_header_id,
1072 ritem.primary_unit_of_measure,
1073 ritem.primary_uom_code,
1074 rln.at_sourcing_flag,
1075 rln.auction_header_id,
1076 rln.cancel_flag,
1077 rln.category_id,
1078 rln.creation_date,
1079 rln.currency_unit_price,
1080 rln.destination_organization_id,
1081 rln.item_description,
1082 rln.item_id,
1083 rln.line_location_id,
1084 rln.line_type_id,
1085 rln.matching_basis,
1086 rln.need_by_date,
1087 rln.on_rfq_flag,
1088 rln.order_type_lookup_code,
1089 rln.rate,
1090 rln.reqs_in_pool_flag,
1091 rln.requisition_line_id,
1092 rln.suggested_buyer_id,
1093 rln.suggested_vendor_product_code,
1094 rln.to_person_id,
1095 rln.unit_meas_lookup_code,
1096 rln.unit_price,
1097 rln.urgent_flag,
1098 rln.vendor_id,
1099 rln.vendor_site_id,
1100 rpar.master_organization_id,
1101 style.progress_payment_flag
1102 ) s
1103 )
1104 group by
1105 req_line_id,
1106 req_header_id,
1107 req_creation_ou_id,
1108 req_creation_date,
1109 req_approved_date,
1110 po_creation_ou_id,
1111 supplier_id,
1112 supplier_site_id,
1113 category_id,
1114 po_item_id,
1115 buyer_id,
1116 org_id,
1117 ship_to_org_id,
1118 requester_id,
1119 line_type_id,
1120 preparer_id,
1121 emergency_flag,
1122 urgent_flag,
1123 sourcing_flag,
1124 include_in_ufr,
1125 po_revisions,
1126 po_creation_method,
1127 func_cur_code,
1128 func_cur_conv_rate,
1129 global_cur_conv_rate,
1130 sglobal_cur_conv_rate,
1131 base_uom,
1132 transaction_uom,
1133 base_uom_conv_rate;
1134
1135 COMMIT;
1136
1137 ELSE
1138 -- Incremental load (process in batches)
1139 bis_collection_utilities.log('incremental collection');
1140 FOR v_batch_no IN 1..l_no_batch LOOP
1141 bis_collection_utilities.log('batch no='||v_batch_no || ' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 1);
1142 MERGE INTO poa_dbi_req_f t using
1143 (
1144 SELECT
1145 req_line_id,
1146 req_header_id,
1147 min(po_line_location_id) po_line_location_id,
1148 req_creation_ou_id,
1149 req_creation_date,
1150 req_approved_date,
1151 po_creation_ou_id,
1152 min(po_creation_date) po_creation_date,
1153 max(po_submit_date) po_submit_date,
1154 decode(min(po_approved_flag),'Y',max(po_approved_date),to_date(null)) po_approved_date,
1155 max(req_fulfilled_date) req_fulfilled_date,
1156 min(expected_date) expected_date,
1157 supplier_id,
1158 supplier_site_id,
1159 category_id,
1160 po_item_id,
1161 buyer_id,
1162 org_id,
1163 ship_to_org_id,
1164 requester_id,
1165 line_type_id,
1166 preparer_id,
1167 sum(unit_price) unit_price,
1168 sum(line_quantity) line_quantity,
1169 sum(line_amount_t) line_amount_t,
1170 sum(line_amount_b) line_amount_b,
1171 sum(line_amount_g) line_amount_g,
1172 sum(line_amount_sg) line_amount_sg,
1173 emergency_flag,
1174 urgent_flag,
1175 sourcing_flag,
1176 include_in_ufr,
1177 max(unproc_ped_flag) unproc_ped_flag,
1178 po_revisions,
1179 po_creation_method,
1180 func_cur_code,
1181 func_cur_conv_rate,
1182 global_cur_conv_rate,
1183 sglobal_cur_conv_rate,
1184 base_uom,
1185 transaction_uom,
1186 base_uom_conv_rate
1187 from
1188 ( select
1189 req_line_id,
1190 req_header_id,
1191 po_line_location_id,
1192 req_creation_ou_id,
1193 req_creation_date,
1194 req_approved_date,
1195 po_creation_ou_id,
1196 po_creation_date,
1197 po_submit_date,
1198 po_approved_date,
1199 po_approved_flag,
1200 decode(matching_basis, 'AMOUNT', to_date(null), req_fulfilled_date) req_fulfilled_date,
1201 decode(matching_basis, 'AMOUNT', to_date(null), nvl(po_promised_date, nvl(po_need_by_date, req_need_by_date))) expected_date,
1202 nvl(supplier_id,-1) supplier_id,
1203 nvl(supplier_site_id,-1) supplier_site_id,
1204 category_id,
1205 po_item_id,
1206 nvl(buyer_id,-1) buyer_id,
1207 nvl(po_creation_ou_id,req_creation_ou_id) org_id,
1208 ship_to_org_id,
1209 requester_id,
1210 line_type_id,
1211 preparer_id,
1212 (unit_price / base_uom_conv_rate) unit_price,
1213 decode(order_type_lookup_code,'QUANTITY', line_quantity * base_uom_conv_rate, to_number(null)) line_quantity,
1214 decode(matching_basis, 'AMOUNT', line_amount_t, unit_price * line_quantity) line_amount_t,
1215 decode(matching_basis, 'AMOUNT', line_amount_t * func_cur_conv_rate, unit_price * line_quantity * func_cur_conv_rate) line_amount_b,
1216 decode(
1217 matching_basis, 'AMOUNT',
1218 decode(global_cur_conv_rate, 0, line_amount_t, line_amount_t * func_cur_conv_rate * global_cur_conv_rate),
1219 decode(global_cur_conv_rate, 0, unit_price * line_quantity, unit_price * line_quantity * func_cur_conv_rate * global_cur_conv_rate)
1220 ) line_amount_g,
1221 decode(
1222 matching_basis, 'AMOUNT',
1223 decode(sglobal_cur_conv_rate, 0, line_amount_t, line_amount_t * func_cur_conv_rate * sglobal_cur_conv_rate),
1224 decode(sglobal_cur_conv_rate, 0, unit_price * line_quantity, unit_price * line_quantity * func_cur_conv_rate * sglobal_cur_conv_rate)
1225 ) line_amount_sg,
1226 emergency_flag,
1227 urgent_flag,
1228 sourcing_flag,
1229 include_in_ufr,
1230 po_revisions,
1231 po_creation_method,
1232 func_cur_code,
1233 func_cur_conv_rate,
1234 global_cur_conv_rate,
1235 sglobal_cur_conv_rate,
1236 decode(order_type_lookup_code,'QUANTITY', base_uom, null) base_uom,
1237 transaction_uom,
1238 base_uom_conv_rate,
1239 ( case when (po_approved_date is null and
1240 decode(matching_basis, 'AMOUNT', to_date(null),
1241 nvl(po_promised_date, nvl(po_need_by_date,
1242 req_need_by_date))) < l_start_time) then 'Y'
1243 else 'N' end
1244 ) unproc_ped_flag
1245 from
1246 ( SELECT /*+ cardinality(inc,1) */
1247 rln.requisition_line_id req_line_id,
1248 rhr.requisition_header_id req_header_id,
1249 pll.line_location_id po_line_location_id,
1250 rhr.org_id req_creation_ou_id,
1251 rln.creation_date req_creation_date,
1252 (case when nvl(rhr.authorization_status,'-999')='APPROVED' and nvl(rln.cancel_flag,'N')='N'
1253 and nvl(rln.modified_by_agent_flag,'N')='N' and nvl(rln.closed_code,'-999') <> 'FINALLY CLOSED'
1254 then nvl(rhr.approved_date,rhr.creation_date)
1255 else
1256 null
1257 end) req_approved_date,
1258 rln.need_by_date req_need_by_date,
1259 decode(pll.po_release_id,null,poh.org_id,por.org_id) po_creation_ou_id,
1260 pll.creation_date po_creation_date,
1261 decode(pll.po_release_id,null,poh.submit_date,por.submit_date) po_submit_date,
1262 (case when nvl(rhr.authorization_status,'-999')='APPROVED' and nvl(rln.cancel_flag,'N')='N'
1263 and nvl(rln.modified_by_agent_flag,'N')='N' and nvl(rln.closed_code,'-999') <> 'FINALLY CLOSED'
1264 and nvl(pll.approved_flag,'N')='Y' then pll.approved_date
1265 else
1266 null
1267 end) po_approved_date,
1268 nvl(pll.approved_flag,'N') po_approved_flag,
1269 ( case
1270 when nvl(rhr.authorization_status,'-999')='APPROVED' and nvl(rln.cancel_flag,'N')='N'
1271 and nvl(rln.modified_by_agent_flag,'N')='N' and nvl(rln.closed_code,'-999') <> 'FINALLY CLOSED'
1272 then
1273 case
1274 when nvl(pll.consigned_flag,'N')='Y' or nvl(pll.vmi_flag,'N')='Y' then null
1275 when nvl(style.progress_payment_flag,'N') = 'Y' then null
1276 when nvl(pll.approved_flag,'N')='Y'
1277 then
1278 case when nvl(pll.receipt_required_flag,'N') = 'N' and nvl(pll.inspection_required_flag, 'N') = 'N'
1279 then least(nvl(pll.shipment_closed_date,pll.closed_for_invoice_date),pll.closed_for_invoice_date)
1280 else least(nvl(pll.shipment_closed_date,pll.closed_for_receiving_date),pll.closed_for_receiving_date)
1281 end
1282 else
1283 null
1284 end
1285 else
1286 null
1287 end
1288 ) req_fulfilled_date,
1289 pll.need_by_date po_need_by_date,
1290 pll.promised_date po_promised_date,
1291 nvl(poh.vendor_id, rln.vendor_id) supplier_id,
1292 nvl(poh.vendor_site_id, rln.vendor_site_id) supplier_site_id,
1293 nvl(pol.category_id, rln.category_id) category_id,
1294 decode(
1295 pll.line_location_id,
1296 null, poa_dbi_items_pkg.getitemkey(
1297 rln.item_id,
1298 rpar.master_organization_id,
1299 rln.category_id,
1300 rln.suggested_vendor_product_code,
1301 rln.vendor_id,
1302 rln.item_description
1303 ),
1304 poa_dbi_items_pkg.getitemkey(
1305 pol.item_id,
1306 ppar.master_organization_id,
1307 pol.category_id,
1308 pol.vendor_product_num,
1309 poh.vendor_id,
1310 pol.item_description
1311 )
1312 ) po_item_id,
1313 nvl(decode(pll.po_release_id,null,poh.agent_id,por.agent_id),rln.suggested_buyer_id) buyer_id,
1314 nvl(pll.ship_to_organization_id, rln.destination_organization_id) ship_to_org_id,
1315 rln.to_person_id requester_id, --get the requester from the requisition itself
1316 rln.line_type_id,
1317 rhr.preparer_id,
1318 nvl(pll.price_override, nvl(rln.currency_unit_price,rln.unit_price)) unit_price, -- in transactional currency
1319 sum( case
1320 when nvl(rhr.authorization_status,'-999')='APPROVED'
1321 and nvl(rln.cancel_flag,'N')='N' and nvl(rln.closed_code,'-999') <> 'FINALLY CLOSED'
1322 and nvl(rln.modified_by_agent_flag,'N')='N' then
1323 case when pll.line_location_id is null then rdn.req_line_quantity
1324 when pll.line_location_id is not null then (pod.quantity_ordered-nvl(pod.quantity_cancelled,0))
1325 else
1326 null
1327 end
1328 else
1329 null
1330 end
1331 ) line_quantity,
1332 sum( case
1333 when nvl(rhr.authorization_status,'-999')='APPROVED'
1334 and nvl(rln.cancel_flag,'N')='N' and nvl(rln.closed_code,'-999') <> 'FINALLY CLOSED'
1335 and nvl(rln.modified_by_agent_flag,'N')='N' then
1336 case when pll.line_location_id is null then nvl(rdn.req_line_currency_amount, rdn.req_line_amount)
1337 when pll.line_location_id is not null then (pod.amount_ordered-nvl(pod.amount_cancelled,0))
1338 else
1339 null
1340 end
1341 else
1342 null
1343 end
1344 ) line_amount_t,
1345 decode(rhr.emergency_po_num, null, 'N', 'Y') emergency_flag,
1346 rln.urgent_flag,
1347 ( case when nvl(rln.line_location_id,-999)=-999
1348 and nvl(rln.cancel_flag,'N')='N'
1349 and nvl(rhr.authorization_status,'-999')='APPROVED'
1350 and (rln.at_sourcing_flag='Y' or
1351 (rln.reqs_in_pool_flag='Y'
1352 and nvl(rln.on_rfq_flag,'N')='Y'
1353 and nvl(rln.auction_header_id,-999)=-999))
1354 then 'Y'
1355 when nvl(rln.line_location_id,-999)=-999
1356 and nvl(rln.cancel_flag,'N')='N'
1357 and nvl(rhr.authorization_status,'-999')='APPROVED'
1358 and rln.reqs_in_pool_flag='Y'
1359 then 'N'
1360 else ''
1361 end
1362 ) sourcing_flag,
1363 ( case when nvl(rhr.authorization_status,'-999') = 'APPROVED' and nvl(rln.closed_code,'-999') <> 'FINALLY CLOSED'
1364 then
1365 case when nvl(rln.cancel_flag,'N')='Y' then 'N'
1366 when decode(pll.line_location_id,null,rln.matching_basis,pll.matching_basis)='AMOUNT' then 'N'
1367 when nvl(pll.consigned_flag,'N')='Y' or nvl(pll.vmi_flag,'N')='Y' then 'N'
1368 when (nvl(style.progress_payment_flag,'N') = 'Y') then 'N'
1369 else 'Y'
1370 end
1371 when nvl(rhr.authorization_status,'-999') = 'CANCELLED'
1372 then 'A'
1373 else 'N'
1374 end
1375 ) include_in_ufr,
1376 decode(pll.line_location_id,null,rln.matching_basis,pll.matching_basis) matching_basis,
1377 decode(pll.line_location_id,null,rln.order_type_lookup_code,pll.value_basis) order_type_lookup_code,
1378 decode(pll.po_release_id,null,poh.revision_num,por.revision_num) po_revisions,
1379 ( case when decode(pll.po_release_id,null,poh.document_creation_method,por.document_creation_method) in ('ENTER_PO', 'ENTER_RELEASE', 'COPY_DOCUMENT', 'AUTOCREATE')
1380 then 'M' else 'A' end) po_creation_method,
1381 rat.func_cur_code func_cur_code,
1382 decode(pll.line_location_id,null,nvl(rln.rate,1),nvl(poh.rate,1)) func_cur_conv_rate,
1383 rat.global_cur_conv_rate,
1384 rat.sglobal_cur_conv_rate,
1385 decode(
1386 pll.line_location_id,
1387 null, decode(rln.item_id, null, rln.unit_meas_lookup_code,ritem.primary_unit_of_measure),
1388 decode(pol.item_id, null, pol.unit_meas_lookup_code,pitem.primary_unit_of_measure)
1389 ) base_uom,
1390 decode( pll.line_location_id, null, rln.unit_meas_lookup_code, pol.unit_meas_lookup_code) transaction_uom,
1391 decode(
1392 pll.line_location_id,
1393 null, decode(
1394 rln.item_id,
1395 null, 1,
1396 decode(
1397 rln.unit_meas_lookup_code,
1398 ritem.primary_unit_of_measure, 1,
1399 poa_dbi_uom_pkg.convert_to_item_base_uom(
1400 rln.item_id,
1401 rpar.master_organization_id,
1402 rln.unit_meas_lookup_code,
1403 ritem.primary_uom_code
1404 )
1405 )
1406 ),
1407 decode(
1408 pol.item_id,
1409 null, 1,
1410 decode(
1411 pol.unit_meas_lookup_code,
1412 pitem.primary_unit_of_measure, 1,
1413 poa_dbi_uom_pkg.convert_to_item_base_uom(
1414 pol.item_id,
1415 ppar.master_organization_id,
1416 pol.unit_meas_lookup_code,
1417 pitem.primary_uom_code
1418 )
1419 )
1420 )
1421 ) base_uom_conv_rate
1422 FROM
1423 poa_dbi_req_inc inc,
1424 poa_dbi_req_rates rat,
1425 po_requisition_lines_all rln,
1426 po_req_distributions_all rdn,
1427 po_headers_all poh,
1428 po_lines_all pol,
1429 po_releases_all por,
1430 financials_system_params_all pfsp,
1431 mtl_parameters ppar,
1432 mtl_system_items pitem,
1433 financials_system_params_all rfsp,
1434 mtl_parameters rpar,
1435 mtl_system_items ritem,
1436 gl_sets_of_books pgl,
1437 gl_sets_of_books rgl,
1438 po_requisition_headers_all rhr,
1439 po_line_locations_all pll,
1440 po_distributions_all pod,
1441 po_doc_style_headers style
1442 WHERE
1443 inc.primary_key = rln.requisition_line_id
1444 and (inc.line_location_id is null or inc.line_location_id = pll.line_location_id)
1445 and inc.batch_id = v_batch_no
1446 and rln.requisition_header_id = rhr.requisition_header_id
1447 and rln.requisition_line_id = rdn.requisition_line_id
1448 and rdn.distribution_id = pod.req_distribution_id (+)
1449 and pll.po_line_id = pol.po_line_id (+)
1450 and pod.line_location_id = pll.line_location_id(+)
1451 and pll.po_release_id = por.po_release_id (+)
1452 and pol.po_header_id = poh.po_header_id (+)
1453 and poh.org_id = pfsp.org_id (+)
1454 and pfsp.inventory_organization_id = ppar.organization_id (+)
1455 and pfsp.set_of_books_id = pgl.set_of_books_id(+)
1456 and rhr.org_id = rfsp.org_id
1457 and rfsp.inventory_organization_id = rpar.organization_id
1458 and rfsp.set_of_books_id = rgl.set_of_books_id
1459 and rln.item_id = ritem.inventory_item_id (+)
1460 and rpar.master_organization_id = nvl(ritem.organization_id, rpar.master_organization_id)
1461 and pol.item_id = pitem.inventory_item_id (+)
1462 and nvl(ppar.master_organization_id, -999) = nvl(pitem.organization_id, nvl(ppar.master_organization_id, -999))
1463 and inc.txn_cur_code = rat.txn_cur_code
1464 and inc.func_cur_code = rat.func_cur_code
1465 and inc.rate_date = rat.rate_date
1466 and rhr.authorization_status in ('APPROVED','CANCELLED','REJECTED','RETURNED','INCOMPLETE')
1467 and rln.source_type_code = 'VENDOR'
1468 and nvl(rhr.contractor_status,'NOT_APPLICABLE') <> 'PENDING'
1469 and rln.creation_date > d_glob_date
1470 and poh.style_id = style.style_id(+)
1471 group by
1472 pitem.primary_unit_of_measure,
1473 pitem.primary_uom_code,
1474 pll.amount,
1475 pll.amount_cancelled,
1476 pll.approved_date,
1477 pll.approved_flag,
1478 pll.closed_for_invoice_date,
1479 pll.closed_for_receiving_date,
1480 pll.consigned_flag,
1481 pll.creation_date,
1482 pll.inspection_required_flag,
1483 pll.line_location_id,
1484 pll.matching_basis,
1485 pll.need_by_date,
1486 pll.payment_type,
1487 pll.po_release_id,
1488 pll.price_override,
1489 pll.promised_date,
1490 pll.receipt_required_flag,
1491 pll.ship_to_organization_id,
1492 pll.shipment_closed_date,
1493 pll.value_basis,
1494 pll.vmi_flag,
1495 poh.agent_id,
1496 poh.document_creation_method,
1497 poh.org_id,
1498 poh.rate,
1499 poh.revision_num,
1500 poh.submit_date,
1501 poh.vendor_id,
1502 poh.vendor_site_id,
1503 pol.category_id,
1504 pol.item_description,
1505 pol.item_id,
1506 pol.matching_basis,
1507 pol.order_type_lookup_code,
1508 pol.unit_meas_lookup_code,
1509 pol.vendor_product_num,
1510 por.agent_id,
1511 por.document_creation_method,
1512 por.org_id,
1513 por.revision_num,
1514 por.submit_date,
1515 ppar.master_organization_id,
1516 rat.func_cur_code,
1517 rat.global_cur_conv_rate,
1518 rat.sglobal_cur_conv_rate,
1519 rhr.approved_date,
1520 rhr.authorization_status,
1521 rhr.creation_date,
1522 rhr.emergency_po_num,
1523 rhr.org_id,
1524 rhr.preparer_id,
1525 rhr.requisition_header_id,
1526 ritem.primary_unit_of_measure,
1527 ritem.primary_uom_code,
1528 rln.at_sourcing_flag,
1529 rln.auction_header_id,
1530 rln.cancel_flag,
1531 rln.category_id,
1532 rln.closed_code,
1533 rln.creation_date,
1534 rln.currency_unit_price,
1535 rln.destination_organization_id,
1536 rln.item_description,
1537 rln.item_id,
1538 rln.line_location_id,
1539 rln.line_type_id,
1540 rln.matching_basis,
1541 rln.modified_by_agent_flag,
1542 rln.need_by_date,
1543 rln.on_rfq_flag,
1544 rln.order_type_lookup_code,
1545 rln.rate,
1546 rln.reqs_in_pool_flag,
1547 rln.requisition_line_id,
1548 rln.suggested_buyer_id,
1549 rln.suggested_vendor_product_code,
1550 rln.to_person_id,
1551 rln.unit_meas_lookup_code,
1552 rln.unit_price,
1553 rln.urgent_flag,
1554 rln.vendor_id,
1555 rln.vendor_site_id,
1556 rpar.master_organization_id,
1557 style.progress_payment_flag
1558 )
1559 )
1560 group by
1561 req_line_id,
1562 req_header_id,
1563 req_creation_ou_id,
1564 req_creation_date,
1565 req_approved_date,
1566 po_creation_ou_id,
1567 supplier_id,
1568 supplier_site_id,
1569 category_id,
1570 po_item_id,
1571 buyer_id,
1572 org_id,
1573 ship_to_org_id,
1574 requester_id,
1575 line_type_id,
1576 preparer_id,
1577 emergency_flag,
1578 urgent_flag,
1579 sourcing_flag,
1580 include_in_ufr,
1581 po_revisions,
1582 po_creation_method,
1583 func_cur_code,
1584 func_cur_conv_rate,
1585 global_cur_conv_rate,
1586 sglobal_cur_conv_rate,
1587 base_uom,
1588 transaction_uom,
1589 base_uom_conv_rate
1590 ) s
1591 ON (t.req_line_id = s.req_line_id)
1592 WHEN MATCHED THEN UPDATE SET
1593 t.po_line_location_id = s.po_line_location_id,
1594 t.req_approved_date = s.req_approved_date,
1595 t.po_creation_ou_id = s.po_creation_ou_id,
1596 t.po_creation_date = s.po_creation_date,
1597 t.po_submit_date = s.po_submit_date,
1598 t.po_approved_date = s.po_approved_date,
1599 t.req_fulfilled_date = s.req_fulfilled_date,
1600 t.expected_date = s.expected_date,
1601 t.supplier_id = s.supplier_id,
1602 t.supplier_site_id = s.supplier_site_id,
1603 t.category_id = s.category_id,
1604 t.po_item_id = s.po_item_id,
1605 t.buyer_id = s.buyer_id,
1606 t.org_id = s.org_id,
1607 t.ship_to_org_id = s.ship_to_org_id,
1608 t.requester_id = s.requester_id,
1609 t.line_type_id = s.line_type_id,
1610 t.preparer_id = s.preparer_id,
1611 t.unit_price = s.unit_price,
1612 t.line_quantity = s.line_quantity,
1613 t.line_amount_t = s.line_amount_t,
1614 t.line_amount_b = s.line_amount_b,
1615 t.line_amount_g = s.line_amount_g,
1616 t.line_amount_sg = s.line_amount_sg,
1617 t.emergency_flag = s.emergency_flag,
1618 t.urgent_flag = s.urgent_flag,
1619 t.sourcing_flag = s.sourcing_flag,
1620 t.include_in_ufr = s.include_in_ufr,
1621 t.po_revisions = s.po_revisions,
1622 t.po_creation_method = s.po_creation_method,
1623 t.func_cur_code = s.func_cur_code,
1624 t.func_cur_conv_rate = s.func_cur_conv_rate,
1625 t.global_cur_conv_rate = s.global_cur_conv_rate,
1626 t.sglobal_cur_conv_rate = s.sglobal_cur_conv_rate,
1627 t.base_uom = s.base_uom,
1628 t.transaction_uom = s.transaction_uom,
1629 t.base_uom_conv_rate = s.base_uom_conv_rate,
1630 t.last_update_login = l_login,
1631 t.last_updated_by = l_user,
1632 t.last_update_date = l_start_time,
1633 t.unproc_ped_flag = s.unproc_ped_flag
1634 WHEN NOT MATCHED THEN INSERT
1635 (
1636 t.req_line_id ,
1637 t.req_header_id ,
1638 t.po_line_location_id ,
1639 t.req_creation_ou_id,
1640 t.req_creation_date ,
1641 t.req_approved_date ,
1642 t.po_creation_ou_id,
1643 t.po_creation_date ,
1644 t.po_submit_date ,
1645 t.po_approved_date,
1646 t.req_fulfilled_date,
1647 t.expected_date,
1648 t.supplier_id ,
1649 t.supplier_site_id,
1650 t.category_id,
1651 t.po_item_id,
1652 t.buyer_id,
1653 t.org_id,
1654 t.ship_to_org_id,
1655 t.requester_id,
1656 t.line_type_id,
1657 t.preparer_id,
1658 t.unit_price,
1659 t.line_quantity,
1660 t.line_amount_t,
1661 t.line_amount_b,
1662 t.line_amount_g,
1663 t.line_amount_sg,
1664 t.emergency_flag,
1665 t.urgent_flag,
1666 t.sourcing_flag,
1667 t.include_in_ufr,
1668 t.po_revisions,
1669 t.po_creation_method,
1670 t.func_cur_code,
1671 t.func_cur_conv_rate,
1672 t.global_cur_conv_rate,
1673 t.sglobal_cur_conv_rate,
1674 t.base_uom,
1675 t.transaction_uom,
1676 t.base_uom_conv_rate,
1677 t.created_by,
1678 t.last_update_login,
1679 t.creation_date,
1680 t.last_updated_by,
1681 t.last_update_date,
1682 t.unproc_ped_flag
1683 ) VALUES
1684 (
1685 s.req_line_id ,
1686 s.req_header_id ,
1687 s.po_line_location_id ,
1688 s.req_creation_ou_id,
1689 s.req_creation_date ,
1690 s.req_approved_date ,
1691 s.po_creation_ou_id,
1692 s.po_creation_date ,
1693 s.po_submit_date ,
1694 s.po_approved_date,
1695 s.req_fulfilled_date,
1696 s.expected_date,
1697 s.supplier_id ,
1698 s.supplier_site_id,
1699 s.category_id,
1700 s.po_item_id,
1701 s.buyer_id,
1702 s.org_id,
1703 s.ship_to_org_id,
1704 s.requester_id,
1705 s.line_type_id,
1706 s.preparer_id,
1707 s.unit_price,
1708 s.line_quantity,
1709 s.line_amount_t,
1710 s.line_amount_b,
1711 s.line_amount_g,
1712 s.line_amount_sg,
1713 s.emergency_flag,
1714 s.urgent_flag,
1715 s.sourcing_flag,
1716 s.include_in_ufr,
1717 s.po_revisions,
1718 s.po_creation_method,
1719 s.func_cur_code,
1720 s.func_cur_conv_rate,
1721 s.global_cur_conv_rate,
1722 s.sglobal_cur_conv_rate,
1723 s.base_uom,
1724 s.transaction_uom,
1725 s.base_uom_conv_rate,
1726 l_user,
1727 l_login,
1728 l_start_time,
1729 l_user,
1730 l_start_time,
1731 s.unproc_ped_flag
1732 );
1733 COMMIT;
1734
1735 DBMS_APPLICATION_INFO.SET_ACTION('batch ' || v_batch_no || ' done');
1736 END LOOP;
1737 END IF;
1738 END IF;
1739 bis_collection_utilities.log('Collection complete '|| 'Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1740 bis_collection_utilities.wrapup(TRUE, l_count, 'POA DBI REQ COLLECTION SUCEEDED', to_date(l_start_date, '''YYYY/MM/DD HH24:MI:SS'''), To_date(l_end_date, '''YYYY/MM/DD HH24:MI:SS'''));
1741 g_init := false;
1742 dbms_application_info.set_module(null, null);
1743 if ( l_num_corrupt_rows > 0 ) then
1744 fnd_concurrent.af_commit;
1745 l_ret_variable := fnd_concurrent.set_completion_status(
1746 status => 'WARNING',
1747 message => 'Bad data found in PO tables.'
1748 );
1749 end if;
1750 EXCEPTION
1751 WHEN others THEN
1752 dbms_application_info.set_action('error');
1753 errbuf:=sqlerrm;
1754 retcode:=sqlcode;
1755 bis_collection_utilities.log('Collection failed with '||errbuf||':'||retcode||' Sysdate=' ||to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1756 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'''));
1757 RAISE;
1758 END populate_req_facts;
1759
1760 END POA_DBI_REQ_F_C;