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