138: and source = 2;
139:
140: if NVL(l_prior_populated, 0) <> 9999 --if state = successful
141: then
142: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_prior');
143:
144: INSERT INTO opi_dbi_opm_wip_tst_prior
145: (orgn_code, item_id, gl_trans_date, trans_qty, amount_base)
146: SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
140: if NVL(l_prior_populated, 0) <> 9999 --if state = successful
141: then
142: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_prior');
143:
144: INSERT INTO opi_dbi_opm_wip_tst_prior
145: (orgn_code, item_id, gl_trans_date, trans_qty, amount_base)
146: SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
147: FROM opi_dbi_opm_wip_tst_current;
148:
361:
362: -- Clear OPM-specific work tables
363: l_stmt_id := 10;
364: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_current');
365: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_prior');
366: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_led_current');
367:
368: -- Check if all the bounds have been properly set up
369: l_stmt_id := 20;
403:
404: -- Following line moved to capture_opm_tst_prior by CDALY 11/13/03
405: -- EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_current');
406:
407: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_prior');
408: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_led_current');
409:
410: -- all done, so return successfully.
411: l_stmt_id := 100;
494: SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
495: FROM opi_dbi_opm_wip_tst_current
496: UNION ALL
497: SELECT orgn_code, item_id, gl_trans_date, -trans_qty, -amount_base
498: FROM opi_dbi_opm_wip_tst_prior
499: )
500: GROUP BY orgn_code, item_id, gl_trans_date
501: HAVING SUM(trans_qty) <> 0 OR SUM(amount_base) <> 0
502: ) t
630:
631: -- Following line moved to capture_opm_tst_prior by CDALY 11/13/03
632: -- EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_current');
633:
634: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_prior');
635: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_led_current');
636:
637: -- all done, so return successfully.
638: l_stmt_id := 100;