Query to get the Suppier Open balances upto particular date.

SELECT  ORG_ID,VENDOR_SITE_CODE SUPPLIER_SITE,SEGMENT1 Supplier_number,case when sign(sum(balance))=-1 then -1*sum(balance) else 0 end CR
                                ,case when sign(sum(balance))=1 then sum(balance) else 0 end DR from
                               (
SELECT VENDOR_NAME,vendor_id,SEGMENT1,
              vendor_site_code,
               invoice_currency_code,
               org_id,
               accts ,
               jai_ap_rpt_apcr_pkg.compute_credit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2021, vendor_site_code,org_id,invoice_currency_code,accts) CREDIT,
             jai_ap_rpt_apcr_pkg.compute_debit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2021, vendor_site_code,org_id,invoice_currency_code,accts) debit,
              jai_ap_rpt_apcr_pkg.compute_credit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2021, vendor_site_code,org_id,invoice_currency_code,accts) - jai_ap_rpt_apcr_pkg.compute_debit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2021, vendor_site_code,org_id,invoice_currency_code,accts) balance
               FROM (                
             
 SELECT      VENDOR_NAME,api.vendor_id,POV.SEGMENT1,
               povs.vendor_site_code,
               api.invoice_currency_code,
               api.org_id,
               api.accts_pay_code_combination_id accts ,
               api.gl_date
FROM   ap_invoices_all api,
              po_vendors pov,
              ap_supplier_sites_all  povs
WHERE        api.vendor_id = pov.vendor_id
AND       pov.vendor_id = povs.vendor_id
AND       api.vendor_site_id = povs.vendor_site_id
--AND jai_ap_rpt_apcr_pkg.compute_credit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2022, vendor_site_code,org_id,invoice_currency_code,accts) - jai_ap_rpt_apcr_pkg.compute_debit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2022, vendor_site_code,83,invoice_currency_code,accts) <> 0
AND        api.vendor_id = NVL(:p_vendor_id, api.vendor_id)
AND      pov.segment1  = NVL(:P_vendor_no,pov.segment1)
AND      (
                (:p_vendor_type_lookup_code is null)
                OR
                ( (:p_vendor_type_lookup_code is not null) and (pov.vendor_type_lookup_code = :p_vendor_type_lookup_code) )
               )
AND (api.gl_date)          <= nvl(:p_as_of_date,api.gl_date)
AND     (api.org_id = :p_org_id )
AND api.accts_pay_code_combination_id = nvl(:p_accts_id,api.accts_pay_code_combination_id)
GROUP BY api.vendor_id,
POV.SEGMENT1 ,
VENDOR_NAME,
               povs.vendor_site_code,
               api.invoice_currency_code,
               api.org_id,
               api.accts_pay_code_combination_id)      
 )
               WHERE  1= 1
               --and  BALANCE<>0
               GROUP BY ORG_ID,VENDOR_SITE_CODE,SEGMENT1
               order by SEGMENT1;
               

Query to get the 11i Bank Accounts Setup Data

SELECT ABAA.SET_OF_BOOKS_ID,ABAA.ORG_ID,ABAA.CURRENCY_CODE,ABAA.MULTI_CURRENCY_FLAG,abaa.bank_account_id,abAa.bank_accounT_NAME,ABAA.BANK_ACCOUNT_NUM,
ABAA.CONTACT_FIRST_NAME AS BANKACC_CNCT_FIRST,
ABAA.CONTACT_MIDDLE_NAME  AS BANKACCT_CNCT_MID,ABAA.CONTACT_LAST_NAME AS BANKACC_CNCT_LAST,
ABAA.CONTACT_PREFIX,ABAA.CONTACT_TITLE,ABAA.INACTIVE_DATE,
ABAA.DESCRIPTION AS BANKACC_DESCRIPTION,ABAA.BANK_BRANCH_ID,ABAA.BANK_ACCOUNT_TYPE,ABAA.VALIDATION_NUMBER,ABAA.ACCOUNT_TYPE,ABAA.POOLED_FLAG,ABAA.ZERO_AMOUNTS_ALLOWED,
ABAA.RECEIPT_MULTI_CURRENCY_FLAG,ABAA.ALLOW_MULTI_ASSIGNMENTS_FLAG,ABAA.IBAN_NUMBER,
ABAA.ASSET_CODE_COMBINATION_ID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.ASSET_CODE_COMBINATION_ID) as ASSET_CODE_COMBINATION,
ABAA.GAIN_CODE_COMBINATION_ID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.GAIN_CODE_COMBINATION_ID) as GAIN_CODE_COMBINATION,
ABAA.LOSS_CODE_COMBINATION_ID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.LOSS_CODE_COMBINATION_ID) as LOSS_CODE_COMBINATION,
ABAA.CASH_CLEARING_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.CASH_CLEARING_CCID) as CASH_CLEARING,
ABAA.BANK_CHARGES_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.BANK_CHARGES_CCID) as BANK_CHARGES,
ABAA.BANK_ERRORS_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.BANK_ERRORS_CCID) as BANK_ERRORS,
ABAA.EARNED_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.EARNED_CCID) as EARNED,
ABAA.UNEARNED_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.UNEARNED_CCID) as UNEARNED,
ABAA.ON_ACCOUNT_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.ON_ACCOUNT_CCID) as ON_ACCOUNT,
ABAA.UNAPPLIED_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.UNAPPLIED_CCID) as UNAPPLIED,
ABAA.UNIDENTIFIED_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.UNIDENTIFIED_CCID) as UNIDENTIFIED,
ABAA.FACTOR_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.FACTOR_CCID) as FACTOR,
ABAA.RECEIPT_CLEARING_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.RECEIPT_CLEARING_CCID) as RECEIPT_CLEARING,
ABAA.REMITTANCE_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.REMITTANCE_CCID) as REMITTANCE,
ABAA.EDISC_RECEIVABLES_TRX_ID,
( select name from AR_RECEIVABLES_TRX_ALL where receivables_trx_id =  ABAA.EDISC_RECEIVABLES_TRX_ID) as EDISC_RECEIVABLES_TRX,
ABAA.UNEDISC_RECEIVABLES_TRX_ID,
( select name from AR_RECEIVABLES_TRX_ALL where receivables_trx_id =  ABAA.UNEDISC_RECEIVABLES_TRX_ID) as UNEDISC_RECEIVABLES_TRX,
ABAA.FUTURE_DATED_PAYMENT_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.FUTURE_DATED_PAYMENT_CCID) as FUTURE_DATED_PAYMENT,
abb.bank_name,abb.bank_name_alt,abb.bank_branch_name,abb.bank_branch_name_alt,abb.description as bank_description,
abb.address_line1,abb.address_line2,abb.city,abb.state,abb.zip,abb.country,abb.institution_type
--acsa.name as check_name,acsa.banK_account_id,acsa.disbursement_type_lookup_code,acsa.check_format_id,acsa.last_document_num,acsa.last_available_document_num,acsa.num_setup_checks,
--acsa.inactive_date,--acsa,org_id,
--acsa.bank_charges_ccid,acsa.bank_errors_ccid,acsa.clearing_days,acsa.future_dated_payments_flag,acsa.cash_clearing_ccid--,acsa,transmission_code
FROM ap_bank_accounts_all abaa, ap_bank_branches abb
--,ap_check_stocks_all acsa,
  --ap_check_formats acf
 WHERE abaa.bank_branch_id = abb.bank_branch_id
-- AND abaa.BANK_ACCOUNT_ID=acsa.BANK_ACCOUNT_ID
 --AND acsa.CHECK_FORMAT_ID=acf.CHECK_FORMAT_ID
 --AND acsa.INACTIVE_DATE is null
   AND abb.end_date IS NULL
   AND abaa.account_type = 'INTERNAL'

Query to get the Customer Profile Class setup data in 11i

select  a.customer_profile_class_id,a.PROFILE_CLASS_NAME,a.PROFILE_CLASS_DESCRIPTION,a.status,a.collector_name,a.STANDARD_TERMS_NAME Payment_terms,
a.override_terms,a.PAYMENT_GRACE_DAYS,a.GROUPING_RULE_NAME,a.DUNNING_LETTERS,a.DUNNING_LETTER_SET_NAME,a.TOLERANCE,a.CREDIT_CHECKING,
b.CURRENCY_CODE,b.OVERALL_CREDIT_LIMIT Credit_limit,b.TRX_CREDIT_LIMIT Order_limit
from AR_CUSTOMER_PROFILE_CLASSES_V a,
AR_CUST_PROF_CLASS_AMOUNTS b where a.customer_profile_class_id=b.customer_profile_class_id

Query to get the 11i Asset categories setup information with GL Code Combinations,Salvage value and depreciation rate

SELECT fcb.book_type_code, fc.segment1, fc.segment2, FC.DESCRIPTION,FC.ENABLED_FLAG,fc.category_type,
       fc.owned_leased,
       (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.ASSET_CLEARING_ACCOUNT_CCID) asset_clearing_cost,
        (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.ASSET_COST_ACCOUNT_CCID ) asset_cost,
         DEPRN_RESERVE_ACCT,
          fcb.DEPRN_EXPENSE_ACCT,
          (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.RESERVE_ACCOUNT_CCID) ACCUM_DEPRN,
  fcb.BONUS_DEPRN_EXPENSE_ACCT,
          BONUS_DEPRN_RESERVE_ACCT ,
          (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.BONUS_RESERVE_ACCT_CCID) BONUS_RESERVE_ACCT,
          fcb.CIP_COST_ACCT  ,
(select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.WIP_COST_ACCOUNT_CCID) CIP_cost,
          fcb.CIP_CLEARING_ACCT ,
(select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.WIP_CLEARING_ACCOUNT_CCID) CIP_CLEARING,
  fcbd.DEPRN_METHOD,
          fcbd.BASIC_RATE ,
          fcbd.ADJUSTED_RATE ,
 FCBD.LIFE_IN_MONTHS ,
 FCBD.PRORATE_CONVENTION_CODE,
 FCBD.RETIREMENT_PRORATE_CONVENTION,
 FCBD.PERCENT_SALVAGE_VALUE
  FROM fa_category_books fcb, fa_categories fc,fa_category_book_defaults fcbd
 WHERE  fcb.category_id = fc.category_id
 AND fcb.CATEGORY_ID=fcbd.CATEGORY_ID
 AND fcb.BOOK_TYPE_CODE=fcbd.BOOK_TYPE_CODE
--- AND Fcb.book_type_code =
  --AND fc.CATEGORY_ID=1

Accounts Receivables Period Close Check list

Following steps need to follow before the Receivable period closure

 

Check Details
Product	Period Close Phase		Description		Standard Oracle/ Custom process		Action Item Details
AR	1: Evaluate and Prepare		Apply Recommended Patches		Oracle
AR	1: Evaluate and Prepare		Apply Recommended Patches		Oracle
AR	1: Evaluate and Prepare		Ensure Data Consistency		Oracle
AR	1: Evaluate and Prepare		Ensure Data Consistency		Oracle
AR	2: Process Transactions		Complete Invoicing, Credits and Adjustments		Oracle
AR	2: Process Transactions		Complete Receipts and Reversals		Oracle
AR	2: Process Transactions		Complete Invoice and Customer Import		Oracle
AR	2: Process Transactions		Complete Lock Box Processing		Oracle
AR	2: Process Transactions		Run the revenue recognition program (Optional; required if OM & Shipping is used)		Oracle
AR	2: Process Transactions		Print Invoices		Oracle
AR	3: Create Accounting		Run Create Accounting		Oracle
AR	3: Create Accounting		Review the Transfer Journal Entries to GL		Oracle
AR	4: Reconcile		Reconcile Transaction Activity for the Period		Oracle		Run the Transaction Register for the same accounting period date range
AR	4: Reconcile		Reconcile Transaction Activity for the Period		Oracle		Run the Sales Journal by Customer Report and the Sales Journal by GL Account Report for the same accounting period date range
AR	4: Reconcile		Reconcile Transaction Activity for the Period		Oracle		Run the Journal Entries Report for the same accounting period date range
AR	4: Reconcile		Reconcile Transaction Activity for the Period		Oracle		Run the AR to GL Reconciliation Report for the same accounting period date range
AR	4: Reconcile		Reconcile Outstanding Customer Balances		Oracle		Run Beginning Balance - Aging reports
AR	4: Reconcile		Reconcile Outstanding Customer Balances		Oracle		Run Transactions - Transaction Register
AR	4: Reconcile		Reconcile Outstanding Customer Balances		Oracle		Run Adjustments - Adjustment Register
AR	4: Reconcile		Reconcile Outstanding Customer Balances		Oracle		Run Exceptions- Invoice Exceptions Report
AR	4: Reconcile		Reconcile Outstanding Customer Balances		Oracle		Run Applied Receipts - Applied Receipts Register (Identify payments received from customers)
AR	4: Reconcile		Reconcile Outstanding Customer Balances		Oracle		Run Unapplied Receipts - Unapplied and Unresolved Receipts Register (identify payments received from customers)
AR	4: Reconcile		Reconcile Outstanding Customer Balances		Oracle		Run Ending Balance - Aging report (as of the last day of the accounting period)
AR	4: Reconcile		Reconcile Receipts		Oracle		Run Receipts Journal Report
AR	4: Reconcile		Reconcile Receipts		Oracle		Run Receipt Register
AR	4: Reconcile		Reconcile Receipts to Bank Statement Activity for the Period		Oracle		Re0run the Create Accounting process for any miscellaneous accounting entries generated from the bank reconciliation, for transfer to the General Ledger
AR	4: Reconcile		Reconcile Posted Journal Entries		Oracle		Reconcile the Journal Import by manually reviewing the transactions in the Subledger Accounting Program Report, and comparing them with the Journal Entries Report output.
AR	5: Close Period		Close the Current Oracle Receivables Period		Oracle		Close the current period in Oracle Receivables using the Open/Close Accounting Periods window.
AR	5: Close Period		Review the Subledger Period Close Exceptions Report		Oracle
AR	5: Close Period		Third Party Balances Report		Oracle
AR	5: Close Period		Review the Unposted Items Report		Oracle
AR	5: Close Period		Review Account Analysis Report		Oracle
AR	5: Close Period		Open the Next Oracle Receivables Period		Oracle
AR	5: Close Period		Run Reports for Tax Reporting Purposes (Optional)		Oracle

 

Accounts Payable Period Close Checklist

Following steps need to follow before the payable period closure

Check Details     Product Period Close Phase Description Action Item   Pre-requisite Standard Oracle/ Custom process AP 1: Evaluate and Prepare Apply Recommended Patches EBS: R12.1 Oracle Financials Recommended Patches     Oracle               AP 1: Evaluate and Prepare Apply Recommended Patches R12 Troubleshooting Period Close in Payables     Oracle               AP 1: Evaluate and Prepare Ensure Data Consistency R12: Experience Smoother Accounting and Period Close By Proactively Eliminating Data Integrity Issues      Oracle               AP 1: Evaluate and Prepare Ensure Data Consistency R12: Master GDF Diagnostic to Validate Data Related to Invoices, Payments, Accounting and Suppliers      Oracle               AP 1: Evaluate and Prepare Ensure Data Consistency How to Register the Master GDF Diagnostic as a Concurrent Program      Oracle               AP 1: Evaluate and Prepare Ensure Data Consistency What is a Generic Datafix Patch (GDF) and what GDFs are available for Payables?      Oracle               AP 2: Process Transactions Complete Invoices and Credits       Oracle               AP 2: Process Transactions Complete Invoice Import        Oracle               AP 2: Process Transactions Import from external system or Internet Expenses       Oracle               AP 2: Process Transactions Run Invoice Validation        Oracle               AP 2: Process Transactions Review the Invoice on Hold Report        Oracle               AP 2: Process Transactions Complete Payments        Oracle               AP 2: Process Transactions Run a Payment Process Request at the month end (Optional)       Oracle               AP 2: Process Transactions Confirm all Payment Instructions       Oracle               AP 2: Process Transactions Reconcile Payments to Bank Statement Activity for the Period         Oracle               AP 2: Process Transactions Run the Payments Register (Optional)       Oracle               AP 2: Process Transactions Submit the Update Matured Bills Payables Status Process       Oracle               AP 3: Create Accounting Run Create Accounting       Oracle               AP 3: Create Accounting Review the Transfer Journal Entries to GL       Oracle               AP 3: Create Accounting Run and Review the Unaccounted Transactions Report       Oracle               AP 3: Create Accounting Transfer All accounted Invoices and Payments to the General Ledger       Oracle               AP 3: Create Accounting Review the Transfer Journal Entries to GL Report generated from step 4       Oracle               AP 3: Create Accounting Run the Posted Invoices Register       Oracle               AP 3: Create Accounting Run the Posted Payments Register       Oracle               AP 4: Reconcile Run the Accounts Payable Trial Balance Run the Payables Posted Invoice Register for this month   Keep the Last Months Accounts Payable Trial Balance handy as you would need this for Trial balance. Oracle               AP 4: Reconcile Run the Accounts Payable Trial Balance Run the Payables Posted Payment Register for this month     Oracle               AP 5: Close Period Close the Current Oracle Payables Period       Oracle               AP 5: Close Period Submit the Unaccounted Transactions Sweep Program     Before running the sweep program, you will need to open the next accounting period Oracle               AP 5: Close Period Accrue Uninvoiced Receipts       Oracle               AP 5: Close Period Run Mass Additions Transfer to Oracle Assets       Oracle               AP 5: Close Period Open the Next Oracle Payables Period Open the next accounting period by using the Control Payables Periods window to set the Period Status to Open.     Oracle