Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets

1-- Period status for Inventory
2  SELECT DISTINCT opu.name            AS operating_unit,
3                  per.organization_id AS inv_org_id,
4                  par.organization_code AS inv_org_code,
5                  org1.name           AS Organization_name,
6                  per.period_name,
7                  per.period_year,
8                  flv.meaning         AS status
9    FROM org_acct_periods           per,
10         fnd_lookup_values          flv,
11         mtl_parameters             par,
12         hr_all_organization_units  org1,
13         hr_all_organization_units_tl otl,
14         hr_organization_information org2,
15         hr_organization_information org3,
16         hr_operating_units         opu
17   WHERE     1 = 1
18         AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
19         AND flv.enabled_flag(+) = 'Y'
20         AND per.organization_id = par.organization_id
21         AND flv.lookup_code(+) =
22                DECODE (
23                   NVL (per.period_close_date, SYSDATE),
24                   per.period_close_date, DECODE (
25                                             per.open_flag,
26                                             'N', DECODE (summarized_flag,
27                                                          'N', 65,
28                                                          66),
29                                             'Y', 4,
30                                             'P', 2,
31                                             4),
32                   3)
33         AND flv.language = 'US'
34         AND UPPER (flv.meaning) != 'CLOSED'
35         AND per.organization_id = org1.organization_id
36         AND org1.organization_id = otl.organization_id
37         AND org1.organization_id = org2.organization_id
38         AND org1.organization_id = org3.organization_id
39         AND org2.org_information_context = 'Accounting Information'
40         AND org3.org_information_context = 'CLASS'
41         AND org3.org_information1 = 'INV'
42         AND org3.org_information2 = 'Y'
43         AND org2.org_information3 = opu.organization_id
44         AND PER.PERIOD_NAME IN ('JAN-20', 'FEB-20')
45ORDER BY opu.name, per.organization_id;

1--Period status for GL, PO, AP, AR, FA
2  SELECT DISTINCT (SELECT sob.NAME
3                     FROM gl_sets_of_books sob
4                    WHERE sob.set_of_books_id = a.set_of_books_id)
5                     "SOB Name",
6                  a.period_name "Period Name",
7                  a.period_num "Period Number",
8                  a.gl_status "GL",
9                  b.po_status "PO",
10                  c.ap_status "AP",
11                  d.ar_status "AR",
12                  e.fa_status "FA"
13    FROM (SELECT period_name,
14                 period_num,
15                 DECODE (closing_status,
16                         'O', 'Open',
17                         'C', 'Closed',
18                         'F', 'Future',
19                         'N', 'Never',
20                         closing_status)
21                    gl_status,
22                 set_of_books_id
23            FROM gl_period_statuses
24           WHERE     application_id = 101
25                 AND UPPER (period_name) IN ('JAN-20', 'FEB-20')
26                 AND 1 = 1) a,
27         (SELECT period_name,
28                 DECODE (closing_status,
29                         'O', 'Open',
30                         'C', 'Closed',
31                         'F', 'Future',
32                         'N', 'Never',
33                         closing_status)
34                    po_status,
35                 set_of_books_id
36            FROM gl_period_statuses
37           WHERE     application_id = 201
38                 AND UPPER (period_name) IN ('JAN-20', 'FEB-20')
39                 AND 1 = 1) b,
40         (SELECT period_name,
41                 DECODE (closing_status,
42                         'O', 'Open',
43                         'C', 'Closed',
44                         'F', 'Future',
45                         'N', 'Never',
46                         closing_status)
47                    ap_status,
48                 set_of_books_id
49            FROM gl_period_statuses
50           WHERE     application_id = 200
51                 AND UPPER (period_name) IN ('JAN-20', 'FEB-20')
52                 AND 1 = 1) c,
53         (SELECT period_name,
54                 DECODE (closing_status,
55                         'O', 'Open',
56                         'C', 'Closed',
57                         'F', 'Future',
58                         'N', 'Never',
59                         closing_status)
60                    ar_status,
61                 set_of_books_id
62            FROM gl_period_statuses
63           WHERE     application_id = 222
64                 AND UPPER (period_name) IN ('JAN-20', 'FEB-20')
65                 AND 1 = 1) d,
66         (SELECT fdp.period_name,
67                 DECODE (fdp.period_close_date, NULL, 'Open', 'Closed')
68                    fa_status,
69                 fbc.set_of_books_id
70            FROM fa_book_controls fbc, fa_deprn_periods fdp
71           WHERE     1 = 1
72                 AND fbc.book_type_code = fdp.book_type_code
73                 AND UPPER (period_name) IN ('JAN-20', 'FEB-20')) e,
74         gl_sets_of_books gsob
75   WHERE     a.period_name = b.period_name(+)
76         AND a.period_name = c.period_name(+)
77         AND a.period_name = d.period_name(+)
78         AND a.period_name = e.period_name(+)
79         AND gsob.set_of_books_id = a.set_of_books_id(+)
80         AND a.set_of_books_id = b.set_of_books_id(+)
81         AND a.set_of_books_id = c.set_of_books_id(+)
82         AND a.set_of_books_id = d.set_of_books_id(+)
83         AND a.set_of_books_id = e.set_of_books_id(+)
84ORDER BY 1, 2 DESC, 3;