Inventory Items update script

declare
 -- 426 is ABC HO CONSUMABLE warehouse, want to transfer XYZ, item 426 from org 0 to ABC HO Consumable
 v_org_id_to number := 0;   ---1265
 v_org_id_from number := 0;
 cursor c_item is
  select
   inventory_item_id, segment1, organization_id,
   item_type, revision_qty_control_code,
   must_use_approved_vendor_flag, inventory_planning_code,
   planner_code, planning_make_buy_code, fixed_days_supply,
   mrp_planning_code, planning_exception_set, shrinkage_rate,
   preprocessing_lead_time, fixed_lead_time, attribute2,
   attribute4, attribute1, postprocessing_lead_time, buyer_id,
   attribute15, description, 1 process_flag, 'UPDATE' transaction_type
  from mtl_system_items_b
  --where inventory_item_id = 275
  where organization_id = v_org_id_from and segment1 in (select item_code from mtl_items_sample);
 v_item c_item%ROWTYPE;
begin
 open c_item;
 loop
 fetch c_item into v_item;
 exit when c_item%NOTFOUND;
 -- insert into mtl_system_items_interface table
 insert into mtl_system_items_interface
 (
   inventory_item_id, segment1,
   item_type, revision_qty_control_code,
   must_use_approved_vendor_flag, inventory_planning_code,
   planner_code, planning_make_buy_code, fixed_days_supply,
   mrp_planning_code, planning_exception_set, shrinkage_rate,
   preprocessing_lead_time, fixed_lead_time, attribute2,
   attribute4, attribute1, postprocessing_lead_time, buyer_id,
   attribute15, description, process_flag,transaction_type, organization_id
 )
 values
 (
   v_item.inventory_item_id, v_item.segment1,
   v_item.item_type, v_item.revision_qty_control_code,
   v_item.must_use_approved_vendor_flag, v_item.inventory_planning_code,
   v_item.planner_code, v_item.planning_make_buy_code, v_item.fixed_days_supply,
   v_item.mrp_planning_code, v_item.planning_exception_set, v_item.shrinkage_rate,
   v_item.preprocessing_lead_time, v_item.fixed_lead_time, v_item.attribute2,
   v_item.attribute4, v_item.attribute1, v_item.postprocessing_lead_time, v_item.buyer_id,
   v_item.attribute15, v_item.description,v_item.process_flag,v_item.transaction_type,v_org_id_to
 );
 end loop;
 close c_item;
exception
 when others then
  dbms_output.put_line(sqlerrm);
end;


----------------------------------------------------------------------------------

select * from mtl_system_items_b where segment1 in
(select segment1 from mtl_system_items_interface)
and organization_id = 0

select * from mtl_system_items_interface --where segment1 = '002000100006'

--delete from mtl_system_items_interface

update mtl_system_items_interface
set set_process_id = 1

update mtl_system_items_interface
set process_flag = 1

------------------------------------

CREATE TABLE MTL_ITEMS_SAMPLE
(
  DESCRIPTION  VARCHAR2(200 BYTE),
  ITEM_CODE    VARCHAR2(100 BYTE)
)

-----------------------------------

CREATE UNIQUE INDEX MTL_ITEMS_SAMPLE_U01 ON APPS.MTL_ITEMS_SAMPLE
(ITEM_CODE)

-----------------------------------

Inventory Period Close Reconciliation Script

define org_id = &org_id
define cost_group_id = &default_cost_group_id
/*
Get org parameters */
SELECT organization_id org, primary_cost_method pcm,
       cost_organization_id cost_org, default_cost_group_id dcg,
       organization_code org_code, project_reference_enabled pre,
       wms_enabled_flag wms, NEGATIVE_INV_RECEIPT_CODE neg_inv,
       avg_rates_cost_type_id avg_rates
  FROM mtl_parameters
 WHERE organization_id = &org_id
/
/*
Get org_acct_periods data */
SELECT organization_id org_id, acct_period_id ,   period_name ,
       period_start_date ps_dte,   schedule_close_date sc_dte,
       period_close_date pc_dte,   open_flag,summarized_flag
  FROM org_acct_periods
 WHERE organization_id = &org_id
ORDER BY 2
/
/*
Get mtl_period_summary_data */
 SELECT acct_period_id  acct_id,
        cost_group_id cg_id, secondary_inventory sec_inv,
        inventory_value inv_value
  FROM mtl_period_summary
 WHERE organization_id = &org_id
   AND inventory_type = 1
ORDER BY 1
/
/*
Get mtl_perclose_dtls data */
 SELECT organization_id, acct_period_id  acct_id,
        cost_group_id cg_id,
        secondary_inventory subinv,
        sum(period_end_unit_cost* period_end_quantity) inv_value      
  FROM mtl_per_close_dtls
 WHERE organization_id = &org_id
 GROUP BY organization_id,acct_period_id , cost_group_id,
        secondary_inventory
order by 2
/
/*
Get cst_period_close_summary data
*/
select acct_period_id,
       organization_id,
       cost_group_id,
       subinventory_code,
       sum(ACCOUNTED_VALUE) acct_val,
       sum(ROLLBACK_VALUE) rollbk_val,
       sum(ROLLBACK_QUANTITY) rollbk_qty,
       sum(ROLLBACK_ONHAND_VALUE) rollbk_onh_val,
       sum(ROLLBACK_INTRANSIT_VALUE) rollbk_intr_val,
       sum(ACCOUNTED_ONHAND_VALUE) acc_onh_val,
       sum(ACCOUNTED_INTRANSIT_VALUE) acc_intr_val,
       sum(ONHAND_VALUE_DISCREPANCY) onh_val_disc,
       sum(INTRANSIT_VALUE_DISCREPANCY) intr_val_disc,
       sum(CUMULATIVE_ONHAND_MTA) cum_onh_mta,
       sum(CUMULATIVE_INTRANSIT_MTA) cum_intr_mta
  from cst_period_close_summary
  where organization_id = &org_id
 group by acct_period_id,
       organization_id,
       cost_group_id,
       subinventory_code
/
/*
get interorg parameters
*/
select FROM_ORGANIZATION_ID fmorg,
       TO_ORGANIZATION_ID torg,
       INTRANSIT_TYPE itype,
       FOB_POINT fob,
       ELEMENTAL_VISIBILITY_ENABLED elem_vis
 from mtl_interorg_parameters
where ( FROM_ORGANIZATION_ID = &org_id
        or TO_ORGANIZATION_ID = &org_id)
/
/*
 MMT MOQ MISMATCH
*/
SELECT TXN.inventory_item_id, TXN.mmt_qty,
        ONHAND.qty
  FROM
  (SELECT mmt.inventory_item_id, sum(mmt.primary_quantity) mmt_qty
  FROM mtl_material_transactions mmt,
        mtl_system_items msi,
        mtl_secondary_inventories mse
  WHERE mmt.organization_id = &org_id
    AND msi.organization_id = &org_id
    AND msi.inventory_item_id = mmt.inventory_item_id
    AND msi.inventory_asset_flag = 'Y'
    AND mse.secondary_inventory_name=mmt.subinventory_code
    AND mse.asset_inventory=1
    AND mse.organization_id=&org_id
    AND mmt.transaction_action_id not in
        (5,6,24,30,50,51,52,55,26,7,11,17,10,9,13,14)
  GROUP BY mmt.inventory_item_id) TXN,
  (SELECT moq.inventory_item_id, sum(moq.transaction_quantity) qty
  FROM  mtl_onhand_quantities moq,
        mtl_system_items msi,
        mtl_secondary_inventories mse
   WHERE moq.organization_id = &org_id
    AND msi.organization_id = &org_id
    AND msi.inventory_item_id = moq.inventory_item_id
    AND msi.inventory_asset_flag = 'Y'
    AND mse.secondary_inventory_name=moq.subinventory_code
    AND mse.asset_inventory=1
    AND mse.organization_id=&org_id
  GROUP BY moq.inventory_item_id) ONHAND
  WHERE TXN.inventory_item_id = ONHAND.inventory_item_id (+)
    AND TXN.mmt_qty <> NVL(ONHAND.qty,0)
/
/*
Item having MMT CQL mismatch for a particular cost group
*/
select CQL.inventory_item_id,CQL.cost_group_id,CQL.lqty,TXN.qty,intransit.intransit_qty,
       CQL.item_cost lcost
 from
(SELECT inventory_item_id,cost_group_id,item_cost,
              NVL(layer_quantity,0) lqty      
          FROM cst_quantity_layers
        WHERE organization_id = &org_id
and cost_group_id = &cost_group_id
        ) CQL,
(SELECT mmt.inventory_item_id item,sum(mmt.primary_quantity) qty
 FROM mtl_material_transactions mmt,
        mtl_system_items msi,
        mtl_secondary_inventories mse
  WHERE mmt.organization_id = &org_id
    AND msi.organization_id = &org_id
    AND msi.inventory_item_id = mmt.inventory_item_id
    AND msi.inventory_asset_flag = 'Y'
    AND mse.secondary_inventory_name=mmt.subinventory_code
    AND mse.asset_inventory=1
    AND mse.organization_id=&org_id
    AND nvl(mmt.cost_group_id,&cost_group_id) = &cost_group_id
    AND mmt.costed_flag is null
   AND mmt.transaction_action_id not in
        (5,6,24,30,50,51,52,55,26,7,11,17,10,9,13,14)
    group by  mmt.inventory_item_id) txn,
( select nvl(sum(decode(to_organization_id,
                           &org_id,to_org_primary_quantity,
                           quantity)),0) intransit_qty ,ms.item_id item_id
       from mtl_supply ms,
            rcv_shipment_headers rsh
      where ms.intransit_owning_org_id = &org_id
        and ms.supply_type_code in ('SHIPMENT', 'RECEIVING')
        and rsh.shipment_header_id = ms.shipment_header_id          
        and (ms.to_organization_id  = &org_id
              or ms.from_organization_id = &org_id)
        and exists
            ( select 'x'
                from mtl_material_transactions mmt
               where mmt.inventory_item_id = ms.item_id
                 and mmt.shipment_number = rsh.shipment_num
                 and mmt.costed_flag is null
                 and (
                        mmt.organization_id=ms.from_organization_id and
                        mmt.transfer_organization_id=
                                           ms.to_organization_id and
                        mmt.transfer_organization_id=
                                           ms.intransit_owning_org_id
                        and nvl(mmt.transfer_cost_group_id,&cost_group_id) = &cost_group_id  
                     ) /* intsit ship, fob: ship */
                     or
                     (
                        mmt.organization_id=ms.from_organization_id and
                        mmt.transfer_organization_id=
                                           ms.to_organization_id   and
                        mmt.organization_id=
                                           ms.intransit_owning_org_id
                        and nvl(mmt.cost_group_id,&cost_group_id) = &cost_group_id  
                     ) /* intsit ship, fob: rcpt */
)
group by ms.item_id ) intransit
 where CQL.inventory_item_id = TXN.item
   and TXN.item = intransit.item_id (+)
   and CQL.lqty <>(TXN.qty + nvl(intransit.intransit_qty,0))
/
/*
 Total MTA value for asset items
  This is the value which should match with the period close value summary
  report run for current open period
*/
select sum(mta.base_transaction_value) mta_value
           from mtl_transaction_accounts mta,
                mtl_system_items msi
           where mta.accounting_line_type in (1,14)
             and mta.organization_id = &org_id
             and mta.organization_id = msi.organization_id
             and mta.inventory_item_id = msi.inventory_item_id
             and nvl(msi.inventory_asset_flag,'N') = 'Y'
/
/*
 Total inventory value for asset items
 This value should match with the inventory_value + Intransit value of
 All Inventory Value report - Average Costing run for all cost groups
 and should also match with the MTA value got in previous select
*/
select sum( cql.layer_quantity * nvl(cql.item_cost,0)) INV_VALUE
       from mtl_system_items msi,
          cst_quantity_layers cql
       where cql.organization_id = msi.organization_id
         and cql.inventory_item_id = msi.inventory_item_id
         and nvl(msi.inventory_asset_flag,'N') = 'Y'
         and cql.organization_id=&org_id
/
/*
The Following would give the value in MTA for a particular cost
group entered by user
*/
 select sum(mta.base_transaction_value) mta_value
           from mtl_transaction_accounts mta,
                mtl_system_items msi
           where mta.accounting_line_type in (1,14)
             and mta.organization_id = &org_id
             and mta.organization_id = msi.organization_id
             and mta.inventory_item_id = msi.inventory_item_id
             and nvl(msi.inventory_asset_flag,'N') = 'Y'
    and exists ( select 'X'
                  from mtl_cst_actual_cost_details mcacd,
       cst_quantity_layers cql
   where mcacd.organization_id=&org_id
     and mcacd.layer_id=cql.layer_id
     and cql.cost_group_id = &cost_group_id
     and cql.organization_id=mcacd.organization_id
     and mcacd.transaction_id = mta.transaction_id
                )
           AND not exists ( select 'x'
                         from mtl_material_transactions mmt,
     mtl_parameters mp
where mmt.transaction_id = mta.transaction_id
 and mmt.transaction_source_type_id = 5
 and nvl(mmt.transfer_cost_group_id,
         mp.default_cost_group_id)= &cost_group_id
 AND mmt.organization_id = mp.organization_id
 AND mp.organization_id = mta.organization_id
                      )
/
/*
 CQL Value for a particular cost group which customer has given
 as input.It should match with the above select.
*/
select sum( cql.layer_quantity * nvl(cql.item_cost,0)) INV_VALUE_CG
       from mtl_system_items msi,
          cst_quantity_layers cql
       where cql.organization_id = msi.organization_id
         and cql.inventory_item_id = msi.inventory_item_id
         and nvl(msi.inventory_asset_flag,'N') = 'Y'
         and cql.organization_id=&org_id
and cql.cost_group_id = &cost_group_id
/
/*
 Total MTA value in 1,14 as per acct_periods
*/
 select oap.acct_period_id,
        mta.accounting_line_type,
sum(mta.base_transaction_value)
  from mtl_transaction_accounts mta,
       org_acct_periods oap
 where mta.organization_id = oap.organization_id
   and mta.accounting_line_type in (1,14)
   and mta.transaction_date between oap.period_start_date
                                and oap.schedule_close_date+0.99999
   and oap.organization_id = &org_id
   and mta.organization_id = &org_id
 group by oap.acct_period_id,
        mta.accounting_line_type
/
/*
 Total MTA value in 1,14 as per acct_periods for the cost group
 given by the ct
*/
 select oap.acct_period_id,
        mta.accounting_line_type,
sum(mta.base_transaction_value)
  from mtl_transaction_accounts mta,
       org_acct_periods oap
 where mta.organization_id = oap.organization_id
   and mta.accounting_line_type in (1,14)
   and mta.transaction_date between oap.period_start_date
                                and oap.schedule_close_date+0.99999
   and oap.organization_id = &org_id
   and mta.organization_id = &org_id
   and exists ( select 'X'
                  from mtl_cst_actual_cost_details mcacd,
       cst_quantity_layers cql
   where mcacd.organization_id=&org_id
     and mcacd.layer_id=cql.layer_id
     and cql.cost_group_id = &cost_group_id
     and cql.organization_id=mcacd.organization_id
     and mcacd.transaction_id = mta.transaction_id
                )
   AND not exists ( select 'x'
                         from mtl_material_transactions mmt,
     mtl_parameters mp
where mmt.transaction_id = mta.transaction_id
 and mmt.transaction_source_type_id = 5
 and nvl(mmt.transfer_cost_group_id,
         mp.default_cost_group_id)= &cost_group_id
 AND mmt.organization_id = mp.organization_id
 AND mp.organization_id = mta.organization_id
                      )
 group by oap.acct_period_id,
        mta.accounting_line_type
/
/*
Following would give details of backdated transactions.
*/
SELECT organization_id, trunc(transaction_date,'MONTH') txn_month,
       trunc(creation_date,'MONTH') creation_Month, count(*)
  FROM mtl_material_transactions
 WHERE organization_id = &org_id
   AND trunc(creation_date) > trunc(transaction_date)
 GROUP BY organization_id, trunc(transaction_date,'MONTH'),
          trunc(creation_date,'MONTH')        
/
/*
Following gives the data for cost_groups_id in MMT
*/
select count(*),mmt.cost_group_id
 from mtl_material_transactions mmt
  where mmt.organization_id=&org_id
   group by mmt.cost_group_id
/
/*
 The items which have different MTA Value than CQl
  This would only have correct meaning if the
  customer has not done any transaction_purge.
*/
select MTA.mta_item, MTA.mta_value,CQL.inv_value
 from
(select sum(mta.base_transaction_value) mta_value,
       mta.inventory_item_id mta_item
  from mtl_transaction_accounts mta,
       mtl_system_items msi
   where mta.accounting_line_type in (1,14)
     and mta.organization_id = &org_id
     and mta.organization_id = msi.organization_id
     and mta.inventory_item_id = msi.inventory_item_id
     and nvl(msi.inventory_asset_flag,'N') = 'Y'
     and exists ( select 'X'
                    from mtl_cst_actual_cost_details mcacd,
                cst_quantity_layers cql
    where mcacd.organization_id=&org_id
      and mcacd.layer_id=cql.layer_id
      and cql.cost_group_id = &cost_group_id
      and cql.organization_id=mcacd.organization_id
      and mcacd.transaction_id = mta.transaction_id
        )
     AND not exists ( select 'x'
                         from mtl_material_transactions mmt,
     mtl_parameters mp
where mmt.transaction_id = mta.transaction_id
 and mmt.transaction_source_type_id = 5
 and nvl(mmt.transfer_cost_group_id,
         mp.default_cost_group_id)= &cost_group_id
 AND mmt.organization_id = mp.organization_id
 AND mp.organization_id = mta.organization_id
                      )
      group by mta.inventory_item_id
  ) MTA,
 ( select sum( cql.layer_quantity * nvl(cql.item_cost,0)) INV_VALUE,
          cql.inventory_item_id cql_item
       from mtl_system_items msi,
          cst_quantity_layers cql
       where cql.organization_id = msi.organization_id
         and cql.inventory_item_id = msi.inventory_item_id
         and nvl(msi.inventory_asset_flag,'N') = 'Y'
         and cql.organization_id=&org_id
and cql.cost_group_id = &cost_group_id
group by cql.inventory_item_id
  ) CQL
  where CQL.cql_item = MTA.mta_item
    and abs(round(CQL.inv_value,2) - round(MTA.mta_value,2)) > 10
/
/*
 Check to see whether we have items in different cost group
 than default cost group having quantity <>0. This would
 have meaning only in organizations which are not project
 reference enabled or wms enabled.
*/
select cql.inventory_item_id item,
       cql.cost_group_id cg,
       mp.default_cost_group_id def_cg,
       cql.layer_quantity layer_qty,
       cql.item_cost item_cost
  from cst_quantity_layers cql,
       mtl_parameters mp
   where mp.organization_id =cql.organization_id
     and mp.organization_id = &org_id
     and cql.cost_group_id <> mp.default_cost_group_id
     and cql.layer_quantity <>0
/
/*
 Check to see whether there are any transactions which are costed
 but donot have MTA.
*/
select mmt.inventory_item_id item,
       mmt.transaction_type_id ttype,
       mmt.transaction_action_id act,
       mmt.transaction_source_type_id src,
       mmt.primary_quantity pq,
       mmt.cost_group_id cg,
       mmt.actual_cost ac,
       mmt.transaction_cost tc,
       mmt.prior_cost pc,
       mmt.new_cost nc,
       mmt.quantity_adjusted qadj,
       mmt.percentage_change pcg,
       msi.inventory_asset_flag iaf,
       mse.asset_inventory AI
 from mtl_material_transactions mmt,
      mtl_system_items msi,
      mtl_secondary_inventories mse
  where mmt.organization_id =&org_id
    and msi.inventory_item_id = mmt.inventory_item_id
    and msi.organization_id = &org_id
    and mse.secondary_inventory_name = mmt.subinventory_code
    and mse.organization_id = &org_id
    and mmt.costed_flag is null
    and not ( mmt.primary_quantity >0
              and mmt.transaction_action_id in (2,5,55,28)
    )
    AND mmt.transaction_action_id not in
        ( 5,24,30,40,41,42,43,50,51,52)
    and not exists ( select 'x'
                       from mtl_transaction_accounts mta
        where mta.transaction_id = mmt.transaction_id
   )
/
/*
 Null transfer_transaction_id problem */
SELECT transaction_id txn, transaction_action_id action,
       transaction_source_type_id srct,
       primary_quantity p_qty
  FROM mtl_material_transactions
 WHERE organization_id = &org_id
   AND transaction_action_id IN (2,3,28)
   AND transfer_transaction_id is NULL
/  
/*
 Check to see if we have acct_period_id as -1 in MMT
*/
SELECT organization_id org_id, acct_period_id, count(*)
  FROM mtl_material_transactions
 WHERE organization_id = &org_id
GROUP BY organization_id, acct_period_id
/
/*
 The following will group the transactions in null cost
 group id if any are there
*/
select count(*),
       mmt.transaction_type_id ttype,
       mmt.transaction_action_id act,
       mmt.transaction_source_type_id src
 from mtl_material_transactions mmt
  where mmt.organization_id=&org_id
   and  mmt.cost_group_id is null
   group by mmt.transaction_type_id ,
       mmt.transaction_action_id ,
       mmt.transaction_source_type_id
/
/*
purged??!!
*/
select PURGE_ID pg_id,
       PURGE_DATE pg_dte,
       PURGE_NAME Pname,
       ORGANIZATION_ID org
 from mtl_purge_header
/
spool off

General Ledger postings rollback experience

Some years ago, my team and I encountered a strange error on the Oracle Applications, the situation was strange because we never envisaged that we will find ourselves in such unexplainable situation, but here we are, faced with it.

What is this strange situation? Trial balance was showing an imbalance position and the cause of the imbalance could not be explained or traced. Three weeks of round round the clock analysis of transactions coming from all sources failed to give us a clue or hint for the imbalance. 

From my years of experience with Oracle Applications, it is a well known fact that imbalance transactions coming from the subledgers or legacy applications cannot be posted in the General Ledger, rather, it would hang on the interface table. Unfortunately, an imbalance journal was able to creep into the General Ledger undetected and was posted. 

Permit me to say that this particular client had four (4) other legacy applications communicating and interfacing with Oracle EBS applications. These legacy applications drop financial data on the interface staging tables on a daily basis. 

Having exhausted all available functional options available to us, we turned our attention to the DBA guy for help. His involvement yielded a positive result, in the sense that the GL_BALANCES tables that was spooled out indicated that there was an imbalance. 

The problem now was how to identify the journal or journal batch(es) that caused the imbalance. From which of the applications? What period? etc. A lot of questions but no answer. Lest I forget, a severity one SR (tar) was raised with Oracle Support on this issue when it was initially observed, and for upward of three weeks, there was no solution coming from them. Nevertheless, Oracle Support gave us maximum support during this period.

With a glimpse of hope received from the DBA guy, we asked him, 

Is it possible to rollback all the postings that have been made to General Ledger from inception? 

What are the likely effects or drawback from taking this action? 

Does Oracle supports transaction rollback? 

Has it ever been done before and what was the result and where? 

He was asked to go do his research and get back to us with his findings the following day. We raised rollback service request question with Oracle Support. Oracle replied that they can't give us an affirmative answer as to whether General Ledger rollback was possible or not, but should it be possible, they would never advise us doing that. 

We carried out our individual research on the General Ledger rollback possibility. We reached out to some more experienced DBAs on this issue, asked them whether they have carried out such an assignment before and what was the outcome. Interestingly, 98% of the experienced DBA said, they haven't carried out such assignment task before. And would align with Oracle Support not to undertake such a risk venture for it may be costly.

So armed with all our findings, we met, evaluated all the finds, and asked ourselves some serious questions. Please note, the client was seriously concerned and had given us a "no going back date" to resolve the issue. 

With that ultimatum from the client and no solutions coming forth from Oracle Support, we concluded that - 

1. Oracle support did not say it is possible or not. They said, even if it is possible, it won't support such.

2. None of the DBAs we asked had ever carried out such a task. It means they have never encountered such situation.

3. Our DBA colleague was mandated to craft or write out the script to achieve the rollback.

4. The client DBA team were mandated to clone the production instance unto the DEV and Training Instances.

5. We adjourned and agreed to meet the following day. 

We reconvened as planned. The DBA said, he has the script but has not tested it. He projected the script and we reviewed and fine tuned together. The next thing was to test the script on the training instance. 

The script was applied on the instance database, and like magic, it worked. The script created and backed up the following GL tables - 

GL_BALANCES

GL_JE_BATCHES

GL_JE_HEADERS

GL_JE_LINES

GL_PERIOD_STATUSES

GL_SETS_OF_BOOKS

GL_TRANSLATION_TRACKING

GL_TRANSLATION_STATUSES

GL_MC_BOOK_ASSIGNMENTS

GL_DAILY_BALANCES

GL_CODE_COMBINATIONS  

And carried out other processes as specified in it, which includes deleting all the transactions posted in the GL_Balances table.

After carrying out the cleanup process on the GL_Balances table leaving no single data, we returned GL_JE_BATCHES data, GL_JE_HEADERS data and GL_JE_LINES data from the backup, back to the their various base tables and reviewed each batch alongside its header and line transactions. In the process of our reviews, we discovered the Journal Batch that caused the imbalance. This particular batch, its headers and lines were isolated, others were reviewed and posted back and the trial balance report program was ran and the trial balance report balanced.

The offensive journal batch that caused the imbalance was from one of the legacy system; the batch was reviewed, corrected and reposted. The trial balance was rerun after the correction, and it balanced.

The same process was carried out on the development instance and the result was the same. Having satisfied ourselves that our experiment accomplished its targeted result, we elected to move the solution to production instance. However, before doing that, we asked for the clone of the production of the previous day.

On the day the solution was to be migrated to the production instance which was on a weekend; we decided to run the solution on two instances concurrently. First on the clone instance, once it achieved the desired result, we replicate on the production and so on.

Three weeks of agony was resolved in five hours and everyone was happy, going home to have a good cool drink and night rest. 

Kudus to my DBA guy Ashok and support from Venkat. Not forgetting my brother from a different mother Adams Jubrin, a jolly nice fellow and great consultant to work with anytime, anyday and anywhere.

For those facing similar challenge, send me a mail, ready to share the script with you.

How To Create New SLA Event Class And Journal Category in Subledger Accounting?

In Release 12, with the introduction of SLA (Subledger Accounting), the system uses only the seeded journal categories. Custom journal categories used in previous releases can no longer be linked in the Book Controls form.

The Journal Categories tab is missing. And, in the Accounting Event Class Options form
Setup > Financials >Subledger Accounting > Accounting Methods Builder > Events,
the seeded event class Adjustments cannot be edited nor can a new event class be inserted for Assets.

The only possible option is to update the seeded Adjustment journal category
GL: Setup > Journal > Categories
and change the category for category key Adjustment to FA Adjustment. Note, however, that this is the seeded journal category which means that it can be updated/changed by a patch or upgrade

Welcome

Welcome to Oracle EBusiness Applications Solution Center