Oracle E-Business Suite

Hello Zulqarnain,
The slowness you are facing is because of the vast amount of data inside the mtl_material_transactions table. In such cases, there is one check you can do before getting into the solution to avoid scanning all the data inside the table above.
1- Check the statistics whether it’s up to date or not. To check this, run the query below:

SELECT NUM_ROWS, LAST_ANALYZED
FROM DBA_TABLES
WHERE TABLE_NAME = 'MTL_MATERIAL_TRANSACTIONS';

compare the total number of rows on the table with the number returned from this query and check the last analyzed date.

Suppose you found the number of rows so little than the current one or last analyzed date is old; ask the DBA to submit a concurrent program called “Gather Table Statistics” to update the statistics. Then you can try your query again.

2- The second action is a workaround to avoid accessing all the data on the table MTL_MATERIAL_TRANSACTIONS by converting the column of the last price to be a subquery within your main query or to creating a function to return the last price for each inventory item within an organization. As you said, you are selecting only the price for each item.

I hope this answer helped you or gave you an insight.
Hassan

Hello Waqas,

You can use the following query to get inventory periods with it’s status for one or more inventory organization also there is a condition hashed for period name if you want to query about particular period. you just need to un hash the condition.

SELECT OOD.ORGANIZATION_CODE
,OAP.PERIOD_NAME
,OAP.OPEN_FLAG
,X.MEANING
FROM ORG_ACCT_PERIODS OAP, ORG_ORGANIZATION_DEFINITIONS OOD, MFG_LOOKUPS X
WHERE OAP.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND OOD.ORGANIZATION_CODE = ('&ORG_CODE')
--AND UPPER (OAP.PERIOD_NAME) = UPPER ('&PERIOD_NAME')
AND X.LOOKUP_TYPE(+) = 'MTL_ACCT_PERIOD_STATUS'
AND X.ENABLED_FLAG(+) = 'Y'
AND X.LOOKUP_CODE(+) =
DECODE (NVL (OAP.PERIOD_CLOSE_DATE, SYSDATE), OAP.PERIOD_CLOSE_DATE, DECODE (OAP.OPEN_FLAG, 'N', DECODE (OAP.SUMMARIZED_FLAG, 'N', 65, 66), 'Y', 4, 'P', 2, 4), 3)
ORDER BY OOD.ORGANIZATION_CODE;