[Home] [Help]
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:
149: update opi_dbi_run_log_curr -- state = prior populated and current truncated
150: set
151: stop_reason_code = 9999, -- flag to indicate prior tst table has been populated
165: BEGIN
166: /*
167: Differences between this procedure and collect_opm_LED_current:
168:
169: * target table is opi_dbi_opm_wip_TST_current (rather than ... LED_current)
170: * subr table is gl_subr_TST (rather than gl_subr_LED)
171: * ic_tran_pnd.gl_posted_ind = 0 (rather than 1)
172:
173: Noting these differences here makes it possible to propagate maintenance to the
174: led_current procedure by copying it and applying the differences. This is a
175: good practice, because the procedures as so similar.
176: */
177:
178: INSERT INTO opi_dbi_opm_wip_tst_current
179: ( orgn_code,
180: item_id,
181: gl_trans_date,
182: trans_qty,
289: SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
290: FROM opi_dbi_opm_wip_led_current
291: UNION ALL
292: SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
293: FROM opi_dbi_opm_wip_tst_current
294: )
295: GROUP BY orgn_code, item_id, gl_trans_date
296: HAVING SUM(trans_qty) <> 0 OR SUM(amount_base) <> 0
297: ) t
360: END IF;
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
401: -- Truncate work tables whose data is no longer needed
402: l_stmt_id := 90;
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:
491: SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
492: FROM opi_dbi_opm_wip_led_current
493: UNION ALL
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: )
577: -- Move tst_current data to tst_prior
578: capture_opm_tst_prior;
579:
580:
581: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_current');
582: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_led_current');
583:
584: -- Check if all the bounds have been properly set up
585: l_stmt_id := 20;
628: -- Truncate work tables whose data is no longer needed
629: l_stmt_id := 90;
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: