Oracle Fusion General Ledger Tables Setup Query

Query to identify companies assigned to legal entities and ledgers

Query lists all the BSV’s assigned to legal entity and/or directly to the ledger.
 It also lists any BSV’s created but not assigned to check if we missed anything. 
To use it for another customer, change the name of the Value Set for the primary balancing segment of the chart of accounts.

**********************************************************
select bsv.ledger_name "Ledger Name"
, nvl(bsv.legal_entity_name,'DIRECTLY ASSIGNED TO LEDGER') "Legal Entity"
, bsv.segment_value "Company"
, fvl.description "Description"
from gl_ledger_le_bsv_specific_v bsv
, fnd_flex_values_vl fvl
, fnd_flex_value_sets fvs
where fvl.flex_value = bsv.segment_value
and fvl.flex_value_set_id = fvs.flex_value_set_id
and fvs.flex_value_set_name = '3SM Technologies'
UNION
select 'ZZ UNASSIGNED' "Ledger Name"
, 'ZZ NOT APPLICABLE' "Legal Entity"
, ffv.flex_value "Company"
, fvl.description "Description"
from fnd_flex_values ffv
, fnd_flex_values_tl fvl
where ffv.flex_value_id = fvl.flex_value_id
and fvl.language = 'US'
and ffv.summary_flag = 'N'
and ffv.value_category = '3SM Technologies'
and ffv.flex_value NOT IN
(select segment_value
from gl_ledger_norm_seg_vals);

SELECT GLSV.SEGMENT_VALUE,
       GLL.LEDGER_ID,
       GLL.NAME LEDGER_NAME,
       GLL.DESCRIPTION LEDGER_DESCRIPTION,
       GLL.CURRENCY_CODE LEDGER_CURRENCY_CODE,
       GLSV.LEGAL_ENTITY_ID,
       (SELECT NAME
          FROM XLE_ENTITY_PROFILES
         WHERE LEGAL_ENTITY_ID = GLSV.LEGAL_ENTITY_ID) LEGAL_ENTITY_NAME,
         (SELECT BU_ID
           FROM FUN_ALL_BUSINESS_UNITS_V HOUF
          WHERE LEGAL_ENTITY_ID = GLSV.LEGAL_ENTITY_ID) BUSINESS_UNIT_ID,
       (SELECT bu_name
           FROM FUN_ALL_BUSINESS_UNITS_V HOUF
          WHERE LEGAL_ENTITY_ID = GLSV.LEGAL_ENTITY_ID) BUSINESS_UNIT_NAME,
          GLL.CHART_OF_ACCOUNTS_ID
  FROM GL_LEDGERS GLL,
       GL_LEDGER_SEGMENT_VALUES GLSV
WHERE GLL.LEDGER_ID = GLSV.LEDGER_ID
   AND NVL(GLL.CURRENCY_CODE,'X') <> 'X'
   AND GLL.NAME LIKE '%PRM%'
ORDER BY GLSV.SEGMENT_VALUE

Fusion Cloud Accounts Payables (AP) tables and queries

--Internal Bank Accounts
SELECT * FROM CE_BANK_ACCOUNTS;

--Payment Documents to be used for Printed type Payments
SELECT * FROM CE_PAYMENT_DOCUMENTS;
SELECT * FROM AP_CHECK_FORMATS;

-- Payable Formats
SELECT * FROM IBY_FORMATS_B;

--Payable Profiles
SELECT * FROM IBY_SYS_PMT_PROFILES_B
SELECT * FROM IBY_EXTRACTS_TL;

-- Payment Methods
SELECT * FROM IBY_PAYMENT_METHODS_B;

-- stores the criteria that a payment batch uses to select invoices for payment
SELECT * FROM AP_INV_SELECTION_CRITERIA_ALL;

/*AP_SELECTED_INVOICES_ALL is a temporary table that stores
information about invoices selected for payment in a
payment batch. Your Oracle Payables application
inserts into this table after you initiate a payment batch.
There will be one row for each invoice that Payables selects for
payment in the current payment batch. When you build payments
in a payment batch, your Oracle Payables
application uses information in this table to create
rows in AP_SELECTED_INVOICE_CHECKS.*/
SELECT * FROM AP_SELECTED_INVOICES_ALL
SELECT * FROM AP_SELECTED_INVOICE_CHECKS_ALL
SELECT * FROM AP_UNSELECTED_INVOICES_ALL

-- This table containes locked invoices details to prevent other check runs from selecting the same invoices
SELECT * FROM AP_PAYMENT_SCHEDULES_ALL

--This table contains transaction details, document details, payer, payee, etc
SELECT * FROM IBY_DOCS_PAYABLE_ALL;
SELECT * FROM IBY_PAYMENTS_ALL;
SELECT * FROM AP_INVOICE_PAYMENTS_ALL;

-- XML documents associated with a particular transaction generated by FORMAT PAYMENT INSTRUCTION Program
SELECT * FROM IBY_TRXN_DOCUMENTS;

-- Stores PPR instructions
SELECT * FROM IBY_PAYMENT_INSTRUCTIONS_ALL

--Payables Payments Data
SELECT * FROM AP_CHECKS_ALL;

AR Refund Process on Fusion Cloud

Refund Process for AR

a. Run 'Create Automatic Receipts' - this program will create the receipts for the transactions.
b. Run ' Create Receipt Remittance Batch' - this program will create the remittance batch to be interfaced to bank.
c. Run 'Create Settlment Batch' - this program will interface the file to bank based d. Reconcile the Bank statement lines with the receipts.
e. Next day negative bank statement lines are received
f. Query the receipts and 1st Unapply the transaction g. Create a Refund on the receipt - this will automatically create an AP Invoice h. Make payment for the AP invoice i. Reconcile the negative bank statement line with the AP Payment line.

The above steps ensures that all the bank statement lines are reconciled with correct accounting.