[Home] [Help]
160: END IF;
161:
162: l_stmt_num := 40;
163: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
164: l_debug_msg := 'OPI_DBI_ONHAND_STG table truncated.';
165: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
166: END IF;
167:
168: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
209: -- are bucketed on GSD
210: /* specifications of the API needs to be changed to be called only once for all orgs in incremental load */
211: l_stmt_num :=10;
212: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
213: l_debug_msg := 'Inserting data Into OPI_DBI_ONHAND_STG ';
214: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
215: END IF;
216:
217: INSERT /*+ append */ INTO OPI_DBI_ONHAND_STG
213: l_debug_msg := 'Inserting data Into OPI_DBI_ONHAND_STG ';
214: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
215: END IF;
216:
217: INSERT /*+ append */ INTO OPI_DBI_ONHAND_STG
218: (organization_id,
219: subinventory_code,
220: inventory_item_id,
221: transaction_date,
313: AND mta1.transaction_date(+) = mmt1.transaction_date
314: AND mta1.subinventory_code(+) = mmt1.subinventory_code
315: AND (mmt1.onhand_qty <> 0 or mta1.base_transaction_value <> 0);
316:
317: l_debug_msg := 'Inserted into staging table OPI_DBI_ONHAND_STG - ' || SQL%ROWCOUNT || ' rows. ';
318: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
319:
320: commit;
321:
320: commit;
321:
322: l_stmt_num :=20;
323: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
324: l_debug_msg := 'Inserting data Into OPI_DBI_ONHAND_STG ';
325: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
326: END IF;
327:
328: -- get the process organizations data by joining to GTV
331: -- it is assumed that inner query on gtv is resolved first and then mmt is joined
332: -- using unique index on U1 on MMT. this needs to be tested in performance testing.
333: -- commit should be performed by wrapper as if the program errors out in between incremental load its not
334: -- re-runnable
335: INSERT /*+ append */ INTO OPI_DBI_ONHAND_STG
336: (organization_id,
337: subinventory_code,
338: inventory_item_id,
339: transaction_date,
429: mmt.inventory_item_id,
430: -- backdated transactions prior to GSD are bucketed on GSD in incremental load
431: decode(sign(trunc(mmt.transaction_date) - g_global_start_date), -1,g_global_start_date, trunc(mmt.transaction_date));
432:
433: l_debug_msg := 'Inserted into staging table OPI_DBI_ONHAND_STG - ' || SQL%ROWCOUNT || ' rows. ';
434: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
435:
436: commit;
437:
1283: onhand_value_b_draft onhand_value_b_draft,
1284: 0 intransit_value_b_draft,
1285: 0 wip_value_b_draft,
1286: source
1287: FROM OPI_DBI_ONHAND_STG
1288: UNION ALL
1289: select
1290: organization_id,
1291: NULL,