Period Table - org_acct_periods

最新推荐文章于 2025-11-10 22:56:20 发布

最新推荐文章于 2025-11-10 22:56:20 发布 · 450 阅读

文章标签：

本文介绍如何使用 Oracle 库存模块管理组织的财务期间，包括开放和关闭期间的过程及相关的数据表。提供了 SQL 查询示例来展示如何检索和更新期间状态，并讨论了与期间关闭相关的表。

ORG_ACCT_PERIODS holds the open and closed financial periods for organizations. When a period is opened, the period open form (INVTTGPO) pulls in the next available period from the GL table GL_PERIODS.

ORG_ACCT_PERIODS holds the relevant data, such as period start, end and close dates. Note that only postable periods will be brought over. You may create non-postable periods in Oracle General Ledger or in the GL forms provided with Oracle Inventory.

If data exists in the PERIOD_CLOSE_DATE column, then the period has been closed and no more transactions can be made against the closed period.

The combination of the PERIOD_CLOSE_DATE and OPEN_FLAG columns reflects the status of a period. When OPEN_FLAG = 'P', it means the period close is processing. When it equals 'N', the period close process is completed. When OPEN_FLAG is 'Y' and PERIOD_CLOSE_DATE is null, it means the period is open. Otherwise, it implies the period close process has failed.

Org_Acct_Periods Queries Sample:

SELECT * FROM org_acct_periods WHERE organization_id = 112 AND period_year IN (2010,2011);

SELECT * FROM org_acct_periods WHERE acct_period_id = 17040;

Mtl_Material_Transactions & Org_Acct_Periods Scripts Sample:

SELECT *
mmt.acct_period_id mmt_acct_period_id,
mmt.transaction_id mmt_transaction_id,
mmt.transaction_date mmt_transaction_date
FROM mtl_material_transactions mmt, org_acct_periods oap
WHERE mmt.costed_flag = 'E'
AND mmt.organization_id = oap.organization_id
AND oap.acct_period_id = mmt.acct_period_id
AND (mmt.transaction_date < oap.period_start_date
OR mmt.transaction_date > oap.schedule_close_date + .99999);

UPDATE MTL_MATERIAL_TRANSACTIONS
SET LAST_UPDATED_BY = -8468014,
ACCT_PERIOD_ID = 5008,
COSTED_FLAG = 'N',
ERROR_CODE = NULL
WHERE ORGANIZATION_ID = 87
AND COSTED_FLAG = 'E'
AND TRANSACTION_TYPE_ID = 10008
AND ACCT_PERIOD_ID = 4007
AND ERROR_CODE = 'CST_MATCH_DATE_PERIOD';

How to Re-Open a Closed Inventory Accounting Period (For Details,NEED Check Doc ID 472631.1)

-- A script to list all inventory periods for a specific organization
-- A script to reopen closed inventory accounting periods in 11.5.10 
-- The script will reopen all inventory periods for the specified 
-- Delete scripts to remove the rows created during the period close process to prevent duplicate rows
-- organization starting from the specified accounting period. 
-- The organization_id can be obtained from the MTL_PARAMETERS table. 
-- The acct_period_id can be obtained from the ORG_ACCT_PERIODS table.

SELECT acct_period_id period, open_flag, period_name name,  
      period_start_date, schedule_close_date, period_close_date  
      FROM  org_acct_periods  
      WHERE organization_id = &org_id  
      order by 1,2;

UPDATE org_acct_periods 
SET open_flag = 'Y', 
period_close_date = NULL, 
summarized_flag = 'N' 
WHERE organization_id = &&org_id 
AND acct_period_id >= &&acct_period_id; 

DELETE mtl_period_summary 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id; 

DELETE mtl_period_cg_summary 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id; 

DELETE mtl_per_close_dtls 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id; 

DELETE cst_period_close_summary 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id;

Period Related Tables

MTL_PERIOD_SUMMARY

MTL_PERIOD_SUMMARY records the inventory value for each subinventory in an organization at the end of a period. The table is populated when period close is performed.

Query Like:

SELECT inventory_value FROM mtl_period_summary WHERE organization_id = 207 AND secondary_inventory = 'FGI' AND acct_period_id = 109;

CST_PERIOD_CLOSE_SUMMARY

This table stores the result of the period close summarization process enhancement introduced in 11.5.10 (Patchset J). Previously, periods were summarized into MTL_PERIOD_SUMMARY or MTL_PERIOD_CG_SUMMARY. The old and new tables are consolidated under the views MTL_PERIOD_SUMMARY_V and MTL_PERIOD_CG_SUMMARY_V.

In this table, summarization results are stored at the item, cost group and subinventory level for each accounting period and organization combination. The two main results of the summarization are:
1) ACCOUNTED_VALUE - The sum of accounting distributions for the relevant period

2) ROLLBACK_VALUE - The product of the rolled back onhand quantity and cost.

MTL_PERIOD_CG_SUMMARY

The table contains summarized inventory valuation details for an accounting period, organization, inventory type and cost group

MTL_PER_CLOSE_DTLS

MTL_PER_CLOSE_DTLS stores period end quantities, costs, and values by subinventory, item, and cost group for an organization under Average Costing for Work in Process. The table also stores the period end value in intransit inventory for the organization. This table is populated by the period close program.
This table is a child table of MTL_PERIOD_SUMMARY.
The table records the inventory value for each inventory item by cost group in the organization at the end of a period.

The COST_GROUP_ID for the intransit inventory will be 1 and SECONDARY_INVENTORY will be NULL.