[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_RCV_TXN_F_C
Source
1 PACKAGE BODY POA_DBI_RCV_TXN_F_C AS
2 /* $Header: poadbirtxfrefb.pls 120.0 2005/06/01 14:29:56 appldev noship $ */
3 g_init boolean := false;
4
5 /* PUBLIC PROCEDURE */
6 PROCEDURE initial_load (errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY NUMBER)
8 IS
9 l_poa_schema VARCHAR2(30);
10 l_status VARCHAR2(30);
11 l_industry VARCHAR2(30);
12
13 l_stmt VARCHAR2(4000);
14 BEGIN
15 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
16 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_RTX_F';
17 EXECUTE IMMEDIATE l_stmt;
18
19 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_RTX_INC';
20 EXECUTE IMMEDIATE l_stmt;
21
22 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_RTX_RATES';
23 EXECUTE IMMEDIATE l_stmt;
24
25 -- l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_ITEMS';
26 -- EXECUTE IMMEDIATE l_stmt;
27
28 g_init := true;
29 populate_rcv_txn_facts (errbuf, retcode);
30 END IF;
31
32 EXCEPTION
33 WHEN OTHERS THEN
34 Errbuf:= Sqlerrm;
35 Retcode:=sqlcode;
36
37 ROLLBACK;
38 POA_LOG.debug_line('initial_load' || Sqlerrm || sqlcode || sysdate);
39 RAISE_APPLICATION_ERROR(-20000,'Stack Dump Follows =>', true);
40
41 END initial_load;
42
43
44
45 /* PUBLIC PROCEDURE */
46 PROCEDURE populate_rcv_txn_facts (errbuf OUT NOCOPY VARCHAR2,
47 retcode OUT NOCOPY NUMBER)
48 IS
49 l_no_batch NUMBER;
50 l_go_ahead BOOLEAN := false;
51 l_count NUMBER := 0;
52
53 l_poa_schema VARCHAR2(30);
54 l_status VARCHAR2(30);
55 l_industry VARCHAR2(30);
56
57 l_stmt varchar2(4000);
58 l_start_date VARCHAR2(22);
59 l_end_date varchar2(22);
60 l_glob_date VARCHAR2(22);
61 /*
62 fnd_date.initialize('YYYY/MM/DD', 'YYYY/MM/DD HH24:MI:SS');
63 l_from_date := fnd_date.displayDT_to_date(p_from_date);
64 l_to_date := fnd_date.displayDT_to_date(p_to_date);
65 */
66 l_ret number;
67 l_batch_size NUMBER;
68 l_start_time DATE;
69 l_login number;
70 l_user number;
71 l_dop NUMBER := 1;
72 d_start_date DATE;
73 d_end_date DATE;
74 d_glob_date DATE;
75 l_rate_type VARCHAR2(30);
76 l_srate_type varchar2(30);
77 l_sec_cur_yn number;
78 l_global_cur_code gl_sets_of_books.currency_code%type;
79 l_sglobal_cur_code gl_sets_of_books.currency_code%type;
80 BEGIN
81 Errbuf :=NULL;
82 Retcode:=0;
83 l_global_cur_code := bis_common_parameters.get_currency_code;
84 l_sglobal_cur_code := bis_common_parameters.get_secondary_currency_code;
85 l_srate_type := bis_common_parameters.get_secondary_rate_type;
86 l_batch_size := bis_common_parameters.get_batch_size(10);
87 l_rate_type := bis_common_parameters.get_rate_type;
88 if(poa_currency_pkg.display_secondary_currency_yn)
89 then
90 l_sec_cur_yn := 1;
91 else
92 l_sec_cur_yn := 0;
93 end if;
94
95 DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'DBI RTX COLLECT', action_name => 'start');
96 l_dop := bis_common_parameters.get_degree_of_parallelism;
97 -- default DOP to profile in EDW_PARALLEL_SRC if 2nd param is not passed
98 l_go_ahead := bis_collection_utilities.setup('POARCVTXN');
99 if (g_init) then
100 execute immediate 'alter session set hash_area_size=104857600';
101 execute immediate 'alter session set sort_area_size=104857600';
102 -- execute immediate 'alter session disable parallel dml' ;
103 end if;
104 IF (NOT l_go_ahead) THEN
105 errbuf := fnd_message.get;
106 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
107 END IF;
108 bis_collection_utilities.g_debug := FALSE;
109
110 -- --------------------------------------------
111 -- Taking care of cases where the input from/to
112 -- date is NULL.
113 -- --------------------------------------------
114
115 IF(g_init) THEN
116 l_start_date := To_char(bis_common_parameters.get_global_start_date
117 , 'YYYY/MM/DD HH24:MI:SS');
118 d_start_date := bis_common_parameters.get_global_start_date;
119 ELSE
120 l_start_date := '''' || To_char(fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('POARCVTXN')) - 0.004,'YYYY/MM/DD HH24:MI:SS') || '''';
121 /* note that if there is not a success record in the log, we should get global start date as l_start_date */
122 d_start_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('POARCVTXN')) - 0.004;
123 END IF;
124
125 l_end_date := '''' || To_char(Sysdate, 'YYYY/MM/DD HH24:MI:SS') || '''';
126 d_end_date := Sysdate;
127
128
129
130 bis_collection_utilities.log( 'The collection range is from '||
131 l_start_date ||' to '|| l_end_date, 0);
132
133
134 IF (l_batch_size IS NULL) THEN
135 l_batch_size := 10000;
136 END if;
137
138 bis_collection_utilities.log('Truncate INC table: '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
139 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
140 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_RTX_INC';
141 EXECUTE IMMEDIATE l_stmt;
142
143 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_RTX_RATES';
144 EXECUTE IMMEDIATE l_stmt;
145 END IF;
146
147 DBMS_APPLICATION_INFO.SET_ACTION('inc');
148 bis_collection_utilities.log('Populate INC table '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
149 l_glob_date := '''' || To_char(bis_common_parameters.get_global_start_date, 'YYYY/MM/DD HH24:MI:SS') || '''';
150 d_glob_date := bis_common_parameters.get_global_start_date;
151
152 if (g_init) then
153 insert /*+ append parallel(poa_dbi_rtx_inc) */ into
154 poa_dbi_rtx_inc
155 (
156 primary_key,
157 global_cur_conv_rate,
158 batch_id,
159 txn_cur_code,
160 func_cur_code,
161 rate_date,
162 source_document_code
163 )
164 select /*+ parallel(rcv) parallel(poh) */
165 rcv.transaction_id primary_key,
166 null global_cur_conv_rate,
167 1 batch_id,
168 poh.currency_code txn_cur_code,
169 poa_gl.currency_code func_cur_code,
170 trunc(nvl(rcv.currency_conversion_date, rcv.transaction_date)) rate_date,
171 rcv.source_document_code source_document_code
172 from rcv_transactions rcv,
173 po_headers_all poh,
174 financials_system_params_all fsp,
175 gl_sets_of_books poa_gl
176 where ( rcv.last_update_date between d_start_date and d_end_date or
177 poh.last_update_date between d_start_date and d_end_date
178 )
179 and rcv.po_header_id = poh.po_header_id (+)
180 and poh.org_id = fsp.org_id (+)
181 and fsp.set_of_books_id = poa_gl.set_of_books_id (+)
182 --and rcv.transaction_type in ('RECEIVE','MATCH','CORRECT', 'REJECT', 'ACCEPT', 'RETURN TO VENDOR', 'DELIVER', 'TRANSFER')
183 and rcv.creation_date >= d_glob_date;
184
185 ELSE -- not initial load
186
187 insert /*+ append */ into
188 poa_dbi_rtx_inc
189 (
190 primary_key,
191 global_cur_conv_rate,
192 batch_id,
193 txn_cur_code,
194 func_cur_code,
195 rate_date,
196 source_document_code
197 )
198 select /*+ cardinality(rcv, 1)*/
199 rcv.transaction_id primary_key,
200 null global_cur_conv_rate,
201 ceil(rownum/l_batch_size) batch_id,
202 poh.currency_code txn_cur_code,
203 poa_gl.currency_code func_cur_code,
204 trunc(nvl(rcv.currency_conversion_date, rcv.transaction_date)) rate_date,
205 rcv.source_document_code source_document_code
206 from rcv_transactions rcv,
207 po_headers_all poh,
208 financials_system_params_all fsp,
209 gl_sets_of_books poa_gl
210 where rcv.last_update_date between d_start_date and d_end_date
211 and rcv.po_header_id = poh.po_header_id (+)
212 and poh.org_id = fsp.org_id (+)
213 and fsp.set_of_books_id = poa_gl.set_of_books_id (+)
214 and rcv.creation_date >= d_glob_date
215 -- and rcv.transaction_type in ('RECEIVE','MATCH','CORRECT','REJECT','ACCEPT','RETURN TO VENDOR', 'DELIVER', 'TRANSFER')
216 UNION
217 select /*+ cardinality(poh, 1)*/
218 rcv.transaction_id primary_key,
219 null global_cur_conv_rate,
220 ceil(rownum/l_batch_size) batch_id,
221 poh.currency_code txn_cur_code,
222 poa_gl.currency_code func_cur_code,
223 trunc(nvl(rcv.currency_conversion_date, rcv.transaction_date)) rate_date,
224 rcv.source_document_code source_document_code
225 from rcv_transactions rcv,
226 po_headers_all poh,
227 financials_system_params_all fsp,
228 gl_sets_of_books poa_gl
229 where
230 poh.last_update_date between d_start_date and d_end_date
231 and rcv.po_header_id = poh.po_header_id (+)
232 and poh.org_id = fsp.org_id (+)
233 and fsp.set_of_books_id = poa_gl.set_of_books_id (+)
234 and rcv.creation_date >= d_glob_date
235 ;
236 END IF;
237
238 COMMIT;
239
240 DBMS_APPLICATION_INFO.SET_ACTION('stats incremental');
241
242 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
243 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_poa_schema,
244 TABNAME => 'POA_DBI_RTX_INC') ;
245 END IF;
246
247 insert /*+ APPEND */ into
248 poa_dbi_rtx_rates
249 (
250 txn_cur_code,
251 func_cur_code,
252 rate_date,
253 global_cur_conv_rate,
254 sglobal_cur_conv_rate
255 )
256 select
257 txn_cur_code,
258 func_cur_code,
259 rate_date,
260 poa_currency_pkg.get_dbi_global_rate(
261 l_rate_type,
262 func_cur_code,
263 rate_date,
264 txn_cur_code
265 ) global_cur_conv_rate,
266 ( case when l_sec_cur_yn = 0
267 then null
268 else poa_currency_pkg.get_dbi_sglobal_rate(
269 l_srate_type,
270 func_cur_code,
271 rate_date,
272 txn_cur_code
273 )
274 end
275 ) sglobal_cur_conv_rate
276 from
277 ( select distinct
278 txn_cur_code,
279 func_cur_code,
280 rate_date
281 from poa_dbi_rtx_inc
282 where source_document_code = 'PO'
283 and txn_cur_code is not null -- added this for UNORDERED txns that have a source doc code of PO but don't have any PO reference on them
284 order by func_cur_code,rate_date
285 );
286
287 commit;
288 DBMS_APPLICATION_INFO.SET_ACTION('stats rates');
289
290 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
291 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_poa_schema,
292 TABNAME => 'POA_DBI_RTX_RATES') ;
293 END IF;
294
295 bis_collection_utilities.log('Populate base table: '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
296
297 select max(batch_id), COUNT(1) into l_no_batch, l_count from poa_dbi_rtx_inc;
298 bis_collection_utilities.log('Identified '|| l_count ||' changed records. Batch size='|| l_batch_size || '. # of Batches=' || l_no_batch
299 || '. Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
300
301 /* missing currency handling */
302
303 IF (poa_currency_pkg.g_missing_cur) THEN
304 poa_currency_pkg.g_missing_cur := false;
305 errbuf := 'There are missing currencies\n';
306 RAISE_APPLICATION_ERROR (-20000, 'Error in INC table collection: ' || errbuf);
307 END IF;
308
309 l_start_time := sysdate;
310 l_login := fnd_global.login_id;
311 l_user := fnd_global.user_id;
312 DBMS_APPLICATION_INFO.SET_ACTION('collect');
313
314 if (l_no_batch is NOT NULL) then
315 IF (g_init) THEN
316 INSERT /*+ APPEND PARALLEL(poa_dbi_rtx_f) */ INTO poa_dbi_rtx_f
317 ( transaction_id,
318 transaction_type,
319 parent_transaction_type,
320 grp_txn_date,
321 receive_txn_date,
322 supplier_id,
323 supplier_site_id,
324 creation_operating_unit_id,
325 receiving_org_id,
326 reason_id,
327 transaction_date,
328 rcv_creation_date,
329 quantity,
330 func_cur_code,
331 global_cur_conv_rate,
332 line_location_id,
333 shipment_header_id,
334 shipment_line_id,
335 asn_type,
336 receipt_num,
337 created_by,
338 last_update_login,
339 creation_date,
340 last_updated_by,
341 last_update_date,
342 sglobal_cur_conv_rate,
343 source_doc_quantity,
344 receipt_exists,
345 currency_conversion_rate,
346 currency_conversion_date,
347 source_document_code,
348 shipping_control,
349 oe_order_line_id,
350 requisition_line_id,
351 routing_header_id,
352 inventory_item_id,
353 primary_quantity,
354 primary_uom_code,
355 wms_enabled_flag,
356 wms_grp_txn_date,
357 dropship_type_code,
358 inv_transaction_id
359 )
360 (
361 select /*+ PARALLEL(val) PARALLEL(par) PARALLEL(inc) PARALLEL(f) PARALLEL(poh) PARALLEL(rsh) PARALLEL(rsl) parallel(item) parallel(poa_gl) no_merge */
362 val.transaction_id,
363 val.transaction_type,
364 par.transaction_type parent_transaction_type,
365 (CASE WHEN (val.transaction_type<>'CORRECT') THEN val.transaction_date
366 WHEN (val.transaction_type='CORRECT' AND par.transaction_type='MATCH') THEN get_date(par.parent_transaction_id)
367 ELSE par.transaction_date END) grp_txn_date,
368 (CASE WHEN (val.transaction_type='RECEIVE') THEN val.transaction_date
369 WHEN (Nvl(par.parent_transaction_id,0) <=0 ) THEN par.transaction_date
370 else get_top_date(val.transaction_id)
371 END) receive_txn_date,
372 poh.vendor_id supplier_id,
373 poh.vendor_site_id supplier_site_id,
374 poh.org_id creation_operating_unit_id,
375 val.organization_id receiving_org_id,
376 (CASE WHEN val.transaction_type = 'CORRECT' THEN par.reason_id ELSE val.reason_id END) reason_id,
377 val.transaction_date transaction_date,
378 val.creation_date rcv_creation_date,
379 val.quantity quantity,
380 poa_gl.currency_code func_cur_code,
381 rat.global_cur_conv_rate,
382 val.po_line_location_id line_location_id,
383 val.shipment_header_id,
384 val.shipment_line_id,
385 rsh.asn_type,
386 rsh.receipt_num,
387 l_user created_by,
388 l_login last_update_login,
389 l_start_time creation_date,
390 l_user last_updated_by,
391 l_start_time last_update_date,
392 rat.sglobal_cur_conv_rate,
393 val.source_doc_quantity,
394 (CASE WHEN val.transaction_type IN ('MATCH', 'RECEIVE') OR val.transaction_type = 'CORRECT' AND par.transaction_type IN ('MATCH', 'RECEIVE')
395 THEN 'Y' ELSE 'N' END ) receipt_exists,
396 val.currency_conversion_rate,
397 val.currency_conversion_date,
398 val.source_document_code,
399 poh.shipping_control shipping_control,
400 val.oe_order_line_id,
401 val.requisition_line_id,
402 val.routing_header_id routing_header_id,
403 rsl.item_id inventory_item_id,
404 Decode(rsl.item_id, NULL, NULL, val.quantity * inv_convert.inv_um_convert(rsl.item_id, 5, 1, null, null, val.unit_of_measure, item.primary_unit_of_measure)) primary_quantity,
405 item.primary_uom_code primary_uom_code,
406 param.wms_enabled_flag wms_enabled_flag,
407 trunc(CASE WHEN (val.transaction_type<>'CORRECT') THEN val.transaction_date
408 ELSE par.transaction_date END) wms_grp_txn_date,
409 val.dropship_type_code,
410 val.inv_transaction_id
411 from rcv_transactions val,
412 rcv_transactions par,
413 poa_dbi_rtx_inc inc,
414 PO_HEADERS_ALL POH,
415 RCV_SHIPMENT_HEADERS RSH,
416 RCV_SHIPMENT_LINES rsl,
417 gl_sets_of_books poa_gl,
418 financials_system_params_all fsp,
419 mtl_system_items item,
420 mtl_parameters param,
421 poa_dbi_rtx_rates rat
422 where
423 -- val.transaction_type IN ('RECEIVE','MATCH','CORRECT','REJECT','ACCEPT','RETURN TO VENDOR')
424 -- AND val.source_document_code = 'PO' AND
425 inc.primary_key = val.transaction_id
426 and val.parent_transaction_id = par.transaction_id(+)
427 AND val.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
428 AND val.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
429 AND val.PO_HEADER_ID = POH.PO_HEADER_ID (+)
430 and inc.txn_cur_code = rat.txn_cur_code (+)
431 and inc.func_cur_code = rat.func_cur_code (+)
432 and inc.rate_date = rat.rate_date (+)
433 AND poh.org_id = fsp.org_id (+)
434 AND fsp.set_of_books_id = poa_gl.set_of_books_id (+)
435 AND rsl.item_id = item.inventory_item_id (+)
436 AND val.organization_id = nvl(item.organization_id,val.organization_id)
437 AND val.organization_id = param.organization_id
438 );
439
440 COMMIT;
441 else
442
443 FOR v_batch_no IN 1..l_no_batch LOOP
444 bis_collection_utilities.log('batch no='||v_batch_no || ' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 1);
445 merge INTO poa_dbi_rtx_f T
446 using (
447 select /*+ cardinality(inc,1) */
448 val.transaction_id,
449 val.transaction_type,
450 par.transaction_type parent_transaction_type,
451 (CASE WHEN (val.transaction_type<>'CORRECT') THEN val.transaction_date
452 WHEN (val.transaction_type='CORRECT' AND par.transaction_type='MATCH') THEN get_date(par.parent_transaction_id)
453 ELSE par.transaction_date END) grp_txn_date,
454 (CASE WHEN (val.transaction_type='RECEIVE') THEN val.transaction_date
455 WHEN (Nvl(par.parent_transaction_id,0) <=0 ) THEN par.transaction_date
456 ELSE get_top_date(val.transaction_id)
457 END) receive_txn_date,
458 poh.vendor_id supplier_id,
459 poh.vendor_site_id supplier_site_id,
460 poh.org_id creation_operating_unit_id,
461 val.organization_id receiving_org_id,
462 (CASE WHEN val.transaction_type = 'CORRECT' THEN par.reason_id ELSE val.reason_id END) reason_id,
463 val.transaction_date transaction_date,
464 val.creation_date rcv_creation_date,
465 val.quantity quantity,
466 poa_gl.currency_code func_cur_code,
467 rat.global_cur_conv_rate,
468 val.po_line_location_id line_location_id,
469 val.shipment_header_id,
470 val.shipment_line_id,
471 rsh.asn_type,
472 rsh.receipt_num,
473 l_user created_by,
474 l_login last_update_login,
475 l_start_time creation_date,
476 l_user last_updated_by,
477 l_start_time last_update_date,
478 rat.sglobal_cur_conv_rate,
479 val.source_doc_quantity,
480 (CASE WHEN val.transaction_type IN ('MATCH', 'RECEIVE') OR val.transaction_type = 'CORRECT' AND par.transaction_type IN ('MATCH', 'RECEIVE')
481 THEN 'Y' ELSE 'N' END ) receipt_exists,
482 val.currency_conversion_rate,
483 val.currency_conversion_date,
484 val.source_document_code,
485 poh.shipping_control shipping_control,
486 val.oe_order_line_id,
487 val.requisition_line_id,
488 val.routing_header_id,
489 rsl.item_id inventory_item_id,
490 Decode(rsl.item_id, NULL, NULL, val.quantity * inv_convert.inv_um_convert(rsl.item_id, 5, 1, null, null, val.unit_of_measure, item.primary_unit_of_measure)) primary_quantity,
491 item.primary_uom_code primary_uom_code,
492 param.wms_enabled_flag wms_enabled_flag,
493 trunc(CASE WHEN (val.transaction_type<>'CORRECT') THEN val.transaction_date
494 ELSE par.transaction_date END) wms_grp_txn_date,
495 val.dropship_type_code,
496 val.inv_transaction_id
497 from rcv_transactions val,
498 rcv_transactions par,
499 poa_dbi_rtx_inc inc,
500 PO_HEADERS_ALL POH,
501 RCV_SHIPMENT_HEADERS RSH,
502 RCV_SHIPMENT_LINES rsl,
503 gl_sets_of_books poa_gl,
504 financials_system_params_all fsp,
505 mtl_system_items item,
506 mtl_parameters param,
507 poa_dbi_rtx_rates rat
508 where
509 inc.primary_key = val.transaction_id
510 and val.parent_transaction_id = par.transaction_id(+)
511 AND val.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
512 AND val.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
513 AND val.PO_HEADER_ID = POH.PO_HEADER_ID (+)
514 and inc.txn_cur_code = rat.txn_cur_code (+)
515 and inc.func_cur_code = rat.func_cur_code (+)
516 and inc.rate_date = rat.rate_date (+)
517 AND poh.org_id = fsp.org_id (+)
518 AND fsp.set_of_books_id = poa_gl.set_of_books_id (+)
519 AND rsl.item_id = item.inventory_item_id (+)
520 AND val.organization_id = nvl(item.organization_id,val.organization_id)
521 AND val.organization_id = param.organization_id
522 AND inc.batch_id = v_batch_no
523 ) S
524 ON (T.transaction_id = S.transaction_id)
525 WHEN matched THEN UPDATE SET
526 t.supplier_id = s.supplier_id,
527 t.supplier_site_id = s.supplier_site_id,
528 t.global_cur_conv_rate = s.global_cur_conv_rate,
529 t.last_update_login = s.last_update_login,
530 t.last_updated_by = s.last_updated_by,
531 t.last_update_date = s.last_update_date,
532 t.sglobal_cur_conv_rate = s.sglobal_cur_conv_rate,
533 t.source_doc_quantity = s.source_doc_quantity,
534 t.receipt_exists = s.receipt_exists,
535 t.currency_conversion_rate = s.currency_conversion_rate,
536 t.currency_conversion_date = s.currency_conversion_date,
537 t.source_document_code = s.source_document_code,
538 t.shipping_control = s.shipping_control,
539 t.quantity = s.quantity,
540 t.oe_order_line_id = s.oe_order_line_id,
541 t.requisition_line_id = s.requisition_line_id,
542 t.routing_header_id = s.routing_header_id,
543 t.inventory_item_id = s.inventory_item_id,
544 t.primary_quantity = s.primary_quantity,
545 t.primary_uom_code = s.primary_uom_code,
546 t.wms_enabled_flag = s.wms_enabled_flag,
547 t.wms_grp_txn_date = s.wms_grp_txn_date,
548 t.dropship_type_code = s.dropship_type_code,
549 t.inv_transaction_id = s.inv_transaction_id
550
551
552 WHEN NOT matched THEN INSERT (
553 t.transaction_id,
554 t.transaction_type,
555 t.parent_transaction_type,
556 t.grp_txn_date,
557 t.receive_txn_date,
558 t.supplier_id,
559 t.supplier_site_id,
560 t.creation_operating_unit_id,
561 t.receiving_org_id,
562 t.reason_id,
563 t.transaction_date,
564 t.rcv_creation_date,
565 t.quantity,
566 t.func_cur_code,
567 t.global_cur_conv_rate,
568 t.line_location_id,
569 t.shipment_header_id,
570 t.shipment_line_id,
571 t.asn_type,
572 t.receipt_num,
573 t.created_by,
574 t.last_update_login,
575 t.creation_date,
576 t.last_updated_by,
577 t.last_update_date,
578 t.sglobal_cur_conv_rate,
579 t.source_doc_quantity,
580 t.receipt_exists,
581 t.currency_conversion_rate,
582 t.currency_conversion_date,
583 t.source_document_code,
584 t.shipping_control,
585 t.oe_order_line_id,
586 t.requisition_line_id,
587 t.routing_header_id,
588 t.inventory_item_id,
589 t.primary_quantity,
590 t.primary_uom_code,
591 t.wms_enabled_flag,
592 t.wms_grp_txn_date,
593 t.dropship_type_code,
594 t.inv_transaction_id
595
596 ) VALUES (
597 s.transaction_id,
598 s.transaction_type,
599 s.parent_transaction_type,
600 s.grp_txn_date,
601 s.receive_txn_date,
602 s.supplier_id,
603 s.supplier_site_id,
604 s.creation_operating_unit_id,
605 s.receiving_org_id,
606 s.reason_id,
607 s.transaction_date,
608 s.rcv_creation_date,
609 s.quantity,
610 s.func_cur_code,
611 s.global_cur_conv_rate,
612 s.line_location_id,
613 s.shipment_header_id,
614 s.shipment_line_id,
615 s.asn_type,
616 s.receipt_num,
617 s.created_by,
618 s.last_update_login,
619 s.creation_date,
620 s.last_updated_by,
621 s.last_update_date,
622 s.sglobal_cur_conv_rate,
623 s.source_doc_quantity,
624 s.receipt_exists,
625 s.currency_conversion_rate,
626 s.currency_conversion_date,
627 s.source_document_code,
628 s.shipping_control,
629 s.oe_order_line_id,
630 s.requisition_line_id,
631 s.routing_header_id,
632 s.inventory_item_id,
633 s.primary_quantity,
634 s.primary_uom_code,
635 s.wms_enabled_flag,
636 s.wms_grp_txn_date,
637 s.dropship_type_code,
638 s.inv_transaction_id
639 );
640
641 COMMIT;
642
643 DBMS_APPLICATION_INFO.SET_ACTION('batch ' || v_batch_no || ' done');
644 END LOOP;
645 END IF; -- init load
646 END IF; -- no data
647
648 bis_collection_utilities.log('Collection complete '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
649 bis_collection_utilities.wrapup(TRUE, l_count, 'POA DBI RCV TXN COLLECTION SUCEEDED', To_date(l_start_date, '''YYYY/MM/DD HH24:MI:SS'''), To_date(l_end_date, '''YYYY/MM/DD HH24:MI:SS'''));
650 g_init := false;
651 DBMS_APPLICATION_INFO.set_module(NULL, NULL);
652 EXCEPTION
653 WHEN OTHERS THEN
654 DBMS_APPLICATION_INFO.SET_ACTION('error');
655 errbuf:=sqlerrm;
656 retcode:=sqlcode;
657 bis_collection_utilities.log('Collection failed with '||errbuf||':'||retcode||' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
658 -- dbms_output.put_line(l_start_date || l_end_date);
659 bis_collection_utilities.wrapup(FALSE, l_count, errbuf||':'||retcode,
660 To_date(l_start_date, '''YYYY/MM/DD HH24:MI:SS'''), To_date(l_end_date, '''YYYY/MM/DD HH24:MI:SS'''));
661
662 ROLLBACK;
663 RAISE_APPLICATION_ERROR(-20000,'Stack Dump Follows =>', true);
664 END populate_rcv_txn_facts;
665
666 FUNCTION get_date (txn_id NUMBER) RETURN DATE IS
667 ret DATE;
668 begin
669 SELECT rcv.transaction_date
670 INTO ret
671 from rcv_transactions rcv
672 where rcv.transaction_id = txn_id;
673 RETURN ret;
674 END GET_DATE;
675
676 FUNCTION get_top_date (txn_id NUMBER) RETURN DATE IS
677 ret date;
678 begin
679 SELECT rcv.transaction_date
680 INTO ret
681 from rcv_transactions rcv
682 where rcv.parent_transaction_id <= 0
683 start with rcv.transaction_id = txn_id
684 connect by prior rcv.parent_transaction_id = rcv.transaction_id;
685 RETURN ret;
686 END GET_TOP_DATE;
687
688
689
690 END POA_DBI_RCV_TXN_F_C;