Query to find the Gl Set of Books.
To find SET_OF_BOOKS_ID:
SELECT * FROM gl_sets_of_books
Inventory
, 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 = '&Period_Name'
and opu.set_of_books_id = '&SOB'
ORDER BY opu.name
, per.organization_id;
To Check whether Periods of AP/AR/GL/FA/PO is closed?
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_Num"
, a.gl_status "GL_Status"
, b.po_status "PO_Status"
, c.ap_status "AP_Status"
, d.ar_status "AR_Status"
, e.fa_status "FA_Status"
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) = UPPER ('&period_name')
AND set_of_books_id = '&sob') 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) = UPPER ('&period_name')
AND set_of_books_id = '&sob') 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) = UPPER ('&period_name')
AND set_of_books_id = '&sob') 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) = UPPER ('&period_name')
AND set_of_books_id = '&sob') 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 fbc.set_of_books_id = '&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
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 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;
Only inventory status is not work, any suggestion?
ReplyDeleteon where clause change
DeleteAND PER.PERIOD_NAME = '&Period_Name'
into
AND PER.PERIOD_NAME = UPPER ('&period_name')
hopa that's help
This comment has been removed by the author.
ReplyDeleteit does not give the future period status for the Inventory
ReplyDeletehsn code master table refference please
ReplyDeleteI need API to open a GL period
ReplyDelete