[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(last_push_date)
into l_last_push_date
from opi_ids_push_date_log pdl
where pdl.organization_id = i_org_id;
select trunc (min(transaction_date)) --Dinkar 10/11/02
into l_inv_from_date
from mtl_material_transactions
where organization_id = i_org_id
and costed_flag is null;
select trunc (min(wt.transaction_date)) --Dinkar 10/11/02
into l_wip_from_date
from wip_transactions wt
where organization_id = i_org_id;
select pdl.last_push_inv_txn_date, -- push date log dates are trunc'ed
pdl.last_push_inv_txn_id,
pdl.last_push_wip_txn_date,
pdl.last_push_wip_txn_id
into
l_prev_push_inv_txn_date,
l_prev_push_inv_txn_id,
l_prev_push_wip_txn_date,
l_prev_push_wip_txn_id
from opi_ids_push_date_log pdl
where pdl.organization_id = i_org_id
and pdl.last_push_date = l_last_push_date;
select min(trx_date)
into l_first_push_date
from opi_ids_push_log
where organization_id = i_org_id;
select trunc (schedule_close_date), open_flag
into l_from_date_per_close, l_per_open_flag
from org_acct_periods
where organization_id = i_org_id
and period_start_date <= trunc (l_from_date)
and schedule_close_date >= trunc (l_from_date);
/* select min(mmt.transaction_id)
into l_push_inv_start_txn_id
from mtl_material_transactions mmt
where trunc (mmt.transaction_date) >= l_push_inv_start_date
and mmt.organization_id = i_org_id
and mmt.costed_flag is null;
select min(mmt.transaction_id)
into l_push_inv_start_txn_id
from mtl_material_transactions mmt
where mmt.transaction_id > l_prev_push_inv_txn_id
and mmt.organization_id = i_org_id
and mmt.costed_flag is null
AND MMT.organization_id = NVL(MMT.owning_organization_id, MMT.organization_id)
AND NVL(MMT.OWNING_TP_TYPE,2) = 2;
select trunc (min(mmt.transaction_date))
into l_inv_txn_date
from mtl_material_transactions mmt
where mmt.transaction_id >= l_push_inv_start_txn_id
and mmt.organization_id = i_org_id
and mmt.costed_flag is null;
select nvl(min(transaction_id),0)
into l_inv_txn_id
from mtl_material_transactions mmt
where mmt.organization_id = i_org_id
AND mmt.transaction_date >= trunc (l_push_inv_start_date) -- rjin 10/31/02
and mmt.transaction_date <= trunc (l_push_inv_end_date) + 0.99999
and mmt.costed_flag is not null;
select trunc (mmt.transaction_date)
into l_inv_txn_date
from mtl_material_transactions mmt
where mmt.transaction_id = l_inv_txn_id;
/* Select the min and max transaction id from between the period start
date and the collection end date. This is different from the
previous approach where we get the date from between collection start
and end date. The reason is to avoid occluding collection backdated
transactions e.g in the following scenario:
Suppose we backdate a trx. on 18th Jan to the 1st Jan with id 100.
Then collect 1st Jan to 15th Jan. The last pushed trx id is 100, not
the max (transaction_id) of 15th Jan.
Then collect 15th Jan to 17th Jan. The last pushed trx id is now
max (transaction_id) of 17th Jan which is less than 100.
Now collect 18th to 25th.
Since the trx id of 100 belonging to a transaction dated on
1st Jan is greater than the last pushed transaction id,
the collection of 18th to 25th will go back to the first.
This can be avoided if we collect the max trx id from the start of
period instead of the start of the collection period.
However, there might be backdated transactions to within the
period collected that might be past the collection period. In the
example here, there could be a backdated transaction for the 2nd Jan,
created after 25th Jan. If the transaction has already been entered,
then it will be collected with the rest of the data from 1st Jan, and
so is not a problem. If not, then the transaction has not been made
yet and will have a transaction id greater than the last pushed
transaction id when it is made.
*/
BEGIN
select period_start_date
into l_look_for_txn_id
from org_acct_periods
where organization_id = i_org_id
and period_start_date <= trunc (l_push_inv_start_date)
and schedule_close_date >= trunc (l_push_inv_start_date);
select nvl(min(transaction_id),0),
nvl(max(transaction_id),0)
into l_push_inv_start_txn_id,
l_push_inv_end_txn_id
from mtl_material_transactions mmt
where mmt.organization_id = i_org_id
and mmt.transaction_date between l_look_for_txn_id
and trunc (l_push_inv_end_date) + 0.99999
and mmt.costed_flag is null;
* THIS IS NEEDED BECAUSE WE DELETE EVERYTHING FROM THE PUSH LOG STARTING
* AT THE INV START DATE.
* THE ONE CASE DROPPED IS THAT OF A WIP TRANSACTION THAT IS BACKDATED TO
* BEFORE THE INV START DATE.
* WE FEEL THAT THIS WOULD BE A VERY RARE CASE, AND NEGLIGIBLE IN
* COMPARISON TO THE ONHAND INVENTORY AND CAN THEREFORE BE IGNORED.
* digupta - 03/17/02
**********************************************************************/
l_stmt_num := 140;
select trunc (min(wt.transaction_date))
into l_wip_txn_date
from wip_transactions wt
where wt.organization_id = i_org_id
and wt.transaction_date >= l_from_date;
select max(wt.transaction_id)
into l_wip_txn_id
from wip_transactions wt
where wt.organization_id = i_org_id
and wt.transaction_date BETWEEN trunc(l_wip_txn_date) and
trunc (l_wip_txn_date) + 0.99999;
Select min(wt.transaction_id)
into l_push_wip_start_txn_id
from wip_transactions wt
where wt.organization_id = i_org_id
and wt.transaction_date >= l_push_wip_start_date;
select trunc (min(wt.transaction_date)) -- Dinkar 10/11/02
into l_wip_txn_date
from wip_transactions wt
where wt.organization_id = i_org_id
and wt.transaction_id >= l_push_wip_start_txn_id;
select period_start_date
into l_look_for_txn_id
from org_acct_periods
where organization_id = i_org_id
and period_start_date <= trunc (l_push_inv_start_date)
and schedule_close_date >= trunc (l_push_inv_start_date);
select min(wt.transaction_id), max(wt.transaction_id)
into l_push_wip_start_txn_id,
l_push_wip_end_txn_id
from wip_transactions wt
where wt.organization_id = i_org_id
and wt.transaction_date between l_look_for_txn_id
and trunc (l_push_wip_end_date) + 0.99999;
select trunc (oap.period_start_date), --Dinkar 10/11/02
trunc (oap.schedule_close_date), --Dinkar 10/11/02
trunc (oap.period_close_date) --Dinkar 10/11/02
into l_per_start_date,
l_sched_close_date,
l_per_close_date
from org_acct_periods oap
where organization_id = i_org_id
and oap.period_start_date <= l_from_date
and oap.schedule_close_date >= l_from_date;