SQL query to find Open/Close Periods in Oracle Apps R12

Query to find the Gl Set of Books.

To find SET_OF_BOOKS_ID:

SELECT * FROM gl_sets_of_books

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 = '&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?

  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_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;

Oracle R12- Bank Setup : Changes from 11i to R12

In R12 , bank setup has been moved from AP CE module, yes you should use a Cash Management responsibility with all grants and permissions to create and maintain bank accounts. The grants must be done by Sysadmins in User management responsibility.

The new tables are now under Cash Management product as follows:

CE_BANK_ACCOUNTS: contains Legal Entity Level bank account information. Each bank account must be affiliated with one bank branch.

CE_BANK_ACCT_USES_ALL: stores Operating Unit level bank account use information

CE_PAYMENT_DOCUMENTS: Payment Documents to be used for Printed type Payments

CE_BANK_BRANCHES_V: view: Bank/Branches Info
CE_BANK_ACCT_USES_OU_V: view: Internal Bank Account Uses Info

The following tables were obsoleted:

AP_BANK_BRANCHES
AP_BANK_ACCOUNTS_ALL
AP_BANK_ACCOUNTS_USES_ALL

In R12, Internal bank accounts are now created in Cash Management (Setup -> Banks

In R12, Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms.  In the Payables Manager responsibility:

1. Navigate to Suppliers -> Entry.
2. Query or create your supplier.
3. Click on Banking Details and then choose Create.

After you have created the bank account, you can assign the bank account to the supplier site.

Where is Supplier Bank Information Stored in Release 12?

In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i.

The supplier bank account information is in the table: IBY_EXT_BANK_ACCOUNTS, the bank and bank branches information is in the table: HZ_PARTIES.

The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES

The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. This table also contains data retrieved from Dun & Bradstreet using either the Dun & Bradstreet online or batch download methods. Historical data for the organization can also be stored in this table. Each time organization information is changed, the effective end date column for the original record is updated and a new record that contains the updated information is created.

The column name is BANK_OR_BRANCH_NUMBER VARCHAR2 (30) Stores bank number for banks and branch number for bank branches, primarily ABA number for US bank branches.