Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
oracle apps sql query for period status
period status backend query for Inventory, General Ledger, Purchasing, Payables, Receivables, Fixed Assets
Period status for Inventory
-- Period status for Inventory
SELECT DISTINCT opu.name AS operating_unit,
per.organization_id AS inv_org_id,
par.organization_code AS inv_org_code,
org1.name AS Organization_name,
per.period_name,
per.period_year,
flv.meaning AS status
FROM org_acct_periods per,
fnd_lookup_values flv,
mtl_parameters par,
hr_all_organization_units org1,
hr_all_organization_units_tl otl,
hr_organization_information org2,
hr_organization_information org3,
hr_operating_units opu
WHERE 1 = 1
AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
AND flv.enabled_flag(+) = 'Y'
AND per.organization_id = par.organization_id
AND flv.lookup_code(+) =
DECODE (
NVL (per.period_close_date, SYSDATE),
per.period_close_date, DECODE (
per.open_flag,
'N', DECODE (summarized_flag,
'N', 65,
66),
'Y', 4,
'P', 2,
4),
3)
AND flv.language = 'US'
AND UPPER (flv.meaning) != 'CLOSED'
AND per.organization_id = org1.organization_id
AND org1.organization_id = otl.organization_id
AND org1.organization_id = org2.organization_id
AND org1.organization_id = org3.organization_id
AND org2.org_information_context = 'Accounting Information'
AND org3.org_information_context = 'CLASS'
AND org3.org_information1 = 'INV'
AND org3.org_information2 = 'Y'
AND org2.org_information3 = opu.organization_id
AND PER.PERIOD_NAME IN ('JAN-20', 'FEB-20')
ORDER BY opu.name, per.organization_id;
Period status for GL, PO, AP, AR, FA
--Period status for GL, PO, AP, AR, FA
SELECT DISTINCT (SELECT sob.NAME
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id)
"SOB Name",
a.period_name "Period Name",
a.period_num "Period Number",
a.gl_status "GL",
b.po_status "PO",
c.ap_status "AP",
d.ar_status "AR",
e.fa_status "FA"
FROM (SELECT period_name,
period_num,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status)
gl_status,
set_of_books_id
FROM gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) IN ('JAN-20', 'FEB-20')
AND 1 = 1) a,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status)
po_status,
set_of_books_id
FROM gl_period_statuses
WHERE application_id = 201
AND UPPER (period_name) IN ('JAN-20', 'FEB-20')
AND 1 = 1) b,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status)
ap_status,
set_of_books_id
FROM gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) IN ('JAN-20', 'FEB-20')
AND 1 = 1) c,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status)
ar_status,
set_of_books_id
FROM gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) IN ('JAN-20', 'FEB-20')
AND 1 = 1) d,
(SELECT fdp.period_name,
DECODE (fdp.period_close_date, NULL, 'Open', 'Closed')
fa_status,
fbc.set_of_books_id
FROM fa_book_controls fbc, fa_deprn_periods fdp
WHERE 1 = 1
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (period_name) IN ('JAN-20', 'FEB-20')) e,
gl_sets_of_books gsob
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND gsob.set_of_books_id = a.set_of_books_id(+)
AND a.set_of_books_id = b.set_of_books_id(+)
AND a.set_of_books_id = c.set_of_books_id(+)
AND a.set_of_books_id = d.set_of_books_id(+)
AND a.set_of_books_id = e.set_of_books_id(+)
ORDER BY 1, 2 DESC, 3;
For more reusable queries, click https://tech7.in/category/technologyblog/oracle/
Related posts:- PTD and YTD balances query in Oracle R12
- Supervisor Hierarchy and Approval Limits in Oracle EBS R12
- Sales order line status query in Oracle apps
- Query to find all Expense report entry delegations
- Query to find vacation rules in Oracle R12
- Purchase Order Receipt details query in Oracle apps
- Oracle apps query to find approval limits
- View accounting query in oracle apps R12
- Query to get AR invoice SLA and GL details
- Accounting entries not transferred to the General Ledger
- Query to get price list details in Oracle EBS
- Query to find all APIs of Oracle Apps modules
- Oracle R12 shipping status query
- R12 AR cash receipt SLA and GL details query
- Oracle apps AR Applications and SLA details
- Oracle EBS query to get AP Invoice SLA Details
- Oracle EBS query to get AR Invoice SLA Details
- Query to get Organization Hierarchy in Oracle apps
- Oracle AR Remit to address query
- Oracle Apps iExpense credit card details query
- Query to get customer tax registration details in R12
- Query to get Sales order and lines details in Oracle Apps
- Purchase Requisition Vertex debug xml query in Oracle EBS
- Purchase Order Vertex debug xml query in Oracle EBS
- AP Invoice Vertex debug xml query in Oracle EBS
- AR Invoice Vertex debug xml query in Oracle EBS
- Query to find AP invoice workflow item key in oracle apps
- Query to find serial number material transactions details
- Query to get attribute values of Inventory Item Template
- Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
- Oracle apps EBS sql to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation
- Query to find Sales Orders that are shipped but not invoiced
- Query to get Customer Bank Account Details
- Query to get description of GL Code Combination
- Query to find Purchase Order and Invoice Details
- Query to find AP Invoices matched with receipts
- Query to find Supplier Bank Details
- Query to find Credit Memos on an AP invoice
- Query to find the AP invoices that are applied to multiple Purchase Orders
- AR Invoice grouping rule from RA_INTERFACE_LINES_ALL
- Query to find shipset and fulfilment set