Smile with Smileys - Decorate your Cubicle

All of us want some ideas to make our work place lively. Here is a simple and great idea which brings smile on your face every time you look at it. Smile with Smileys :)

How to make it?

1. Search your favorite smileys on Google

2. Copy and paste them in a document

3. Print the smileys

4. Cut the extra paper and give them a proper look

5. Take pencils or sticks and attach the smileys to them using tape. 

(If you want to increase the height of pencils, roll the paper and attach to pencil with cello tape)

6. Place the smileys attached to pencils in a nice vase or mug.

Oracle Cash Management Setups

Oracle Cash Management module helps you to streamline the reconciliation process and manage the cash cycle effectively. 

Below are the basic functionalities and the setups to start with Cash Management module.

Step 1. Create Cash Management Responsibility
Responsibility: System Administrator
Navigation: Security > Responsibility > Define         

  Exclude Menus/Functions which are not required.

Step 2: Define Profile Option Values for Cash Management responsibility
Navigation: System Administrator > Profile > System 

1. MO: Operating Unit
2. MO: Security Profile
3. GL Ledger Name
4. GL: Data Access Set
5. SLA: Additional Data Access Set

Step 3: Assign Cash Management Responsibility to Users
Security > User > Define

Query the user and assign Cash Management responsibility created in Step 1.

Step 4: Assign Legal Entity to the Cash Management Responsibility
Assigning the Legal Entity to Cash Management responsibility gives the access to legal entity for cash management setups.

This is very important setup and with out this setup, you can not access the legal entity details to setup CM system parameters. 

Responsibility: User Management
Navigation: Roles & Role Inheritance

Search the page with below values:

Type: Roles & Responsibilities

Category: Miscellaneous

Name: Cash Management Super User (The CM responsibility Name) 

Application: Cash Management

Click on Update icon against Cash Management Super User responsibility.

Click on Security Wizards.

Click on Run Wizard icon for CE UMX Security wizard. Add the Legal Entity ‘XX India Private Limited’ and select Use, Bank Account Grants Maintenance and Bank Account Transfers check boxes.

Click on Apply to save the changes.

Step 5: Define Cash Management System Parameters

Setup the System Parameters to configure your Cash Management system according to your business requirements. 

Navigation: Cash Management Superuser > Setup > System > System Parameters

Legal Entity: The Legal Entity name.
Ledger: The ledger name for Cash Management accounting transactions. If there are multiple ledgers assigned to your Legal Entity, System Parameters should be setup for each ledger.
Begin Date: Cash Management shows payment, receipts, journals, and open interface transactions details from the Begin Date.
Show Cleared Transactions: If you check this check box, cleared transactions will be available for reconciliation along with uncleared transactions.
Add Lines to Automatic Statements: If you check this check box, users can add lines to existing automatically loaded bank statements.

Archive/Purge: Use the Archive and Purge check boxes to allow Autoreconciliation program to automatically purge/archive the data from Bank Statement Interface tables once the information has been successfully transferred to the bank statement tables.

Exchange Rate Type: Cash Flows and Bank Account Transfer transactions use the exchange rate specified here.
Bank Statement Cash flow Rate Date: The cash flow program uses the date specified here to get the exchange rate details.
Bank Transfer Rate Date: The Bank Account Transfers use the date specified here to get the exchange rate details.


Bank Account Transfers Authorization: Specify whether the authorization is required to settle bank account transfers.

Step 6: Define Bank Transaction Codes
Define the bank transaction codes that your bank uses to identify different types of transactions on its statements. Bank Statement Loading will fail if the bank transaction codes are not defined in the system which are there in the bank statement files.

Cash Management Superuser > Setup > Banks > Bank Transaction Codes

Step 7: Define Bank Statement Mappings
Bank Statement Loader program uses this Bank Statement mappings template to map the Bank statement fields. For Example, the first lines in below image means, the 5th position on second records represents the Statement number.

Cash Management Superuser > Setup > Banks > Bank Statement Mappings

Step 8: Define Cash Flow Mappings
Cash Management module provide the feature of Cashflows to generate the cash flow transactions and generate the Journal entries in the system. 

This feature is mainly used when there is no payment/receipt/journal to reconcile against a bank statement line. Instead of creating a manual journal entry, you can use this feature to create cashflow transactions you can reconcile with bank statement lines and subsequently the journal entries get created.

Cash Management Superuser > Setup > Banks > Bank Statement Cashflow Mappings

As a prerequisite, there should be transaction codes exist in the system, for which the GL accounts are assigned in cash flow mappings. The journal entries will get created by using this account.

Once the transaction codes and cashflow mappings present in the system, users need to run Bank Statement Cashflow creation program to generate the cash flows for a particular bank account for which the statement lines exist with Cash Flow transaction codes.

Step 9: Setup Autoreconciliation Tolerances

Cash Management Superuser > Setup > Bank Accounts

Query Bank Account name and select Update Account option. Navigate to Account Controls.

 Under Auto Reconciliation Tolerances > Payments section, Setup Tolerance amount, Tolerance Percentage, Foreign Tolerance Differences and Tolerance Differences.

Step 10: Setup Cash Clearing Account
Cash Management Superuser > Setup > Bank Accounts

Query Bank Account name and select Update Account option. Navigate to Account Access > Options.

Step 11: Setup Entered Currency Balancing Account

The Entered Currency Balancing Account is used by Subledger Accounting to balance foreign currency subledger journals by the entered currency and balancing segment value. Without this account defined, system can not derive the CCID for foreign currency transactions.

Navigation: General Ledger Super User > Setup > Financials >Accounting Setup Manager > Accounting Setups

Query the Ledger. Click on Update Accounting options.

Click on Update Ledger Options. Under Subledger Accounting section, add Entered Currency Balancing Account.

Find AP invoice Data in AP, XLA and GL tables by using Invoice_ID

AP:

Find AP Invoice Summary Data:

SELECT ai.invoice_id,
  SUBSTR(ai.invoice_num,1,25) invoice_num,
  SUBSTR(aps.vendor_name,1,25) vendor_name,
  ai.invoice_date,
  ai.invoice_amount,
  ai.base_amount,
  SUBSTR(ai.invoice_type_lookup_code,1,15) invoice_type_lookup_code,
  SUBSTR(ai.invoice_currency_code,1,3) invoice_currency_code,
  SUBSTR(ai.payment_currency_code,1,3) payment_currency_code,
  ai.legal_entity_id,
  ai.org_id
FROM AP_INVOICES_ALL ai,
  AP_SUPPLIERS aps,
  AP_SUPPLIER_SITES_ALL avs
WHERE ai.invoice_id   = '&Invoice_ID'
AND ai.vendor_id      = aps.vendor_id(+)
and AI.VENDOR_SITE_ID = AVS.VENDOR_SITE_ID(+)

ORDER BY ai.invoice_id ASC;

Find AP Invoice Lines Summary:

SELECT LINE_NUMBER,
  LINE_TYPE_LOOKUP_CODE,
  LINE_SOURCE,
  ACCOUNTING_DATE,
  PERIOD_NAME,
  AMOUNT,
  SUMMARY_TAX_LINE_ID,
  DEFERRED_ACCTG_FLAG,
  ORG_ID
FROM AP_INVOICE_LINES_ALL
where INVOICE_ID = '&Invoice_ID'
ORDER BY LINE_NUMBER ASC;

Find AP Invoice Distributions Summary:

SELECT INVOICE_ID,
  INVOICE_LINE_NUMBER,
  SUBSTR(DISTRIBUTION_LINE_NUMBER,1,8) DISTRIBUTION_LINE_NUMBER,
  SUBSTR(LINE_TYPE_LOOKUP_CODE,1,9) LINE_TYPE_LOOKUP_CODE,
  ACCOUNTING_DATE,
  PERIOD_NAME,
  AMOUNT,
  BASE_AMOUNT,
  POSTED_FLAG,
  MATCH_STATUS_FLAG,
  ENCUMBERED_FLAG,
  HISTORICAL_FLAG,
  SUBSTR(DIST_CODE_COMBINATION_ID,1,15) DIST_CODE_COMBINATION_ID,
  SUBSTR(ACCOUNTING_EVENT_ID,1,15) ACCOUNTING_EVENT_ID,
  SUBSTR(BC_EVENT_ID,1,15) BC_EVENT_ID,
  SUBSTR(INVOICE_DISTRIBUTION_ID,1,15) INVOICE_DISTRIBUTION_ID,
  SUBSTR(PARENT_REVERSAL_ID,1,15) PARENT_REVERSAL_ID,
  SUBSTR(PO_DISTRIBUTION_ID,1,15) PO_DISTRIBUTION_ID,
  SUMMARY_TAX_LINE_ID,
  DETAIL_TAX_DIST_ID,
  ORG_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = '&Invoice_ID'
order by INVOICE_LINE_NUMBER,
  DISTRIBUTION_LINE_NUMBER ASC;

XLA

Find AP Invoice data in XLA_EVENTS table:

SELECT DISTINCT XE.*
FROM AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XE.APPLICATION_ID           =200
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
AND XTE.ENTITY_ID               =XE.ENTITY_ID
order by XE.ENTITY_ID ,
  XE.EVENT_NUMBER;

Find AP Invoice data in XLA_AE_HEADERS table:

SELECT DISTINCT XEH.*
FROM XLA_AE_HEADERS XEH ,
  AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XEH.APPLICATION_ID          =200
AND XE.APPLICATION_ID           =200
AND XE.ENTITY_ID                =XTE.ENTITY_ID
AND XE.EVENT_ID                 =XEH.EVENT_ID
AND XTE.ENTITY_ID               =XEH.ENTITY_ID
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
order by XEH.EVENT_ID ,
  XEH.AE_HEADER_ID ASC;

Find AP Invoice data in XLA_AE_LINES table:

 select distinct XEL.*
  FROM XLA_AE_LINES XEL ,
  XLA_AE_HEADERS XEH ,
  AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XEL.APPLICATION_ID          =200
AND XEH.APPLICATION_ID          =200
AND XE.APPLICATION_ID           =200
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XE.ENTITY_ID                =XTE.ENTITY_ID
AND XE.EVENT_ID                 =XEH.EVENT_ID
AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
AND XTE.ENTITY_ID               =XEH.ENTITY_ID
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
order by XEL.AE_HEADER_ID ,
  XEL.AE_LINE_NUM ASC;

General Ledger:

Find AP Invoice Data in GL_JE_BATCHES table:

SELECT DISTINCT GJB.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_BATCHES GJB,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
AND GJB.JE_BATCH_ID      = GIR.JE_BATCH_ID;

Find AP Invoice data in GL_JE_HEDAERS Table:

SELECT DISTINCT GJH.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
and AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
AND GJH.JE_HEADER_ID     = GIR.JE_HEADER_ID;

Find AP Invoice Data in GL_JE_LINES Table:

SELECT DISTINCT GLL.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_LINES GLL,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
and GLL.JE_HEADER_ID     = GIR.JE_HEADER_ID
AND GLL.JE_LINE_NUM      = GIR.JE_LINE_NUM;

Find AP Invoice Data in GL_IMPORT_REFERENCES Table:

SELECT DISTINCT GIR.*
FROM GL_IMPORT_REFERENCES GIR,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
and AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID;

Find The Account Code Combinations used for a specific AP Invoice:

SELECT DISTINCT GCC.*
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID IN
  ( SELECT DISTINCT XEL.CODE_COMBINATION_ID
  FROM XLA_AE_LINES XEL ,
    XLA_AE_HEADERS XEH ,
    AP_INVOICES_ALL AI ,
    XLA.XLA_TRANSACTION_ENTITIES XTE
  WHERE XTE.APPLICATION_ID        =200
  AND XEH.APPLICATION_ID          =200
  AND XEL.APPLICATION_ID          =200
  AND AI.INVOICE_ID               ='1317327'
  AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
  AND XTE.ENTITY_CODE             ='AP_INVOICES'
  AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
  AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
  AND XTE.ENTITY_ID               =XEH.ENTITY_ID
  UNION ALL
  SELECT DISTINCT XEL.CODE_COMBINATION_ID
  FROM XLA_AE_LINES XEL ,
    XLA_AE_HEADERS XEH ,
    AP_INVOICE_PAYMENTS_ALL AIP ,
    XLA.XLA_TRANSACTION_ENTITIES XTE
  WHERE XTE.APPLICATION_ID        =200
  AND XEL.APPLICATION_ID          =200
  AND XEH.APPLICATION_ID          =200
  AND AIP.INVOICE_ID              ='1317327'
  AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
  AND XTE.ENTITY_CODE             ='AP_PAYMENTS'
  AND XTE.LEDGER_ID               =AIP.SET_OF_BOOKS_ID
  AND NVL(XTE.SOURCE_ID_INT_1,-99)=AIP.CHECK_ID
  AND XTE.ENTITY_ID               =XEH.ENTITY_ID
  UNION ALL
  SELECT DISTINCT PO.CODE_COMBINATION_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID ,
    PO_DISTRIBUTIONS_ALL PO
  WHERE AID.INVOICE_ID        ='1317327'
  AND AID.PO_DISTRIBUTION_ID IS NOT NULL
  and PO.PO_DISTRIBUTION_ID   =AID.PO_DISTRIBUTION_ID
  );

List of Tables In Oracle Financials for India Payables

Following is the list of Tables used to get all the information about India Localization Payables Transactions.

1. AP_INVOICES_ALL
AP Invoice Details for the given Invoice

2. AP_INVOICE_LINES_ALL
AP Invoice Lines Details for Passed AP invoice

3. AP_INVOICE_DISTRIBUTIONS_ALL
AP Invoice Distributions Details for Passed invoice

4. JAI_AP_INVOICE_LINES
India Local AP invoice lines with tax details

5. JAI_AP_TDS_INVOICES
- Details of Supplier Credit memo and Standard Invoice on Tax Authority generated on Validation of Invoice
- Details of amount of prepayment applied and amount reversed through RTN generated.
- Details of Source Attribute used for tax type identification TDS/WCT: TDS-Attribute1,WCT-Attribute 2,ESI-Attribute 3

6. JAI_AP_TDS_PAYMENTS
Invoice will get updated to this table only if Challan Details are filled for the Invoice

7. JAI_AP_TDS_INV_PAYMENTS
Invoice will get updated to this table only if India Process TDS Payments got run

8. JAI_AP_TDS_F16_DTLS_ALL
Invoice will get updated to this table only if India Generate TDS concurrent got run

9. JAI_AP_TDS_YEARS
Setups pertaining to tds year definition

10. JAI_RGM_DEFINITIONS
Shows details of various regimes Registrations.(vat,service tax, tds,tcs,excise)

11. JAI_RGM_REGISTRATIONS
Shows details of tan and pan Table shows details of various regimes.(vat,service tax, tds,tcs,excise)

12. JAI_RGM_PARTIES
Shows details various regimes.(vat,service tax, tds,tcs,excise) Assigned at Organization (header level details)

13. JAI_CMN_VENDOR_SITES
Supplier addtional information details

14. JAI_AP_TDS_VENDOR_HDRS
Supplier addtional information TDS details

15. JAI_CMN_TAXES_ALL
JAI_cmn_taxes_all for TDS Tax attached in the Inv Dist Tax definition details, for the tax id attached at invoice distribution

16. JAI_AP_TDS_THHOLD_HDRS
TDS Thresholds Setup

17. JAI_AP_TDS_THHOLD_TYPES
TDS Thresholds Types Setup

18. JAI_AP_TDS_THHOLD_SLABS
TDS Thresholds Slabs Setup

19. JAI_AP_TDS_THHOLD_TAXES
TDS Thresholds Taxes Setup

20. JAI_AP_TDS_THHOLD_XCEPS
TDS Thresholds Exception Setup

21. JAI_AP_TDS_THHOLD_GRPS
TDS Thresholds Groups Information

22. JAI_AP_TDS_THHOLD_TRXS
TDS Thresholds Transactions

23. JAI_AP_TDS_INV_TAXES
TDS Thresholds Transactions Taxes

24. JAI_AP_TDS_PREPAYMENTS
Table we can get Prepayment application details (details of SI and Prepayemnt tax id should exist for both and section code should be same)

25. JAI_AP_TDS_INV_CANCELS
TDS Cancellations

26. JAI_AP_TDS_THGRP_AUDITS
TDS Threshold Audits,(details of transaction group id, in this we can get details of threshold calculation)

27. JAI_RGM_TRX_REFS
This table will be populated only when the invoice is accounted and the concurrent program India Service Tax Processing is run

28. JAI_RGM_TRX_RECORDS
This table will be populated when the invoice is paid and the concurrent program India Service Tax Processing is run

29. JAI_AP_MATCH_ERS_T
Stores Invoice details of ERS invoices, so that taxes can be carried from Purchasing to Payables. This table is used for bulk propagation of taxes by concurrent India - to insert taxes by pay on receipt.

30. JAI_RCV_LINES
Get RCV lines of the receipt from the ap invoice

31. JAI_RCV_LINE_TAXES
This Table holds the Information of the Receipt Taxes.

32. JAI_RCV_TRANSACTIONS
Gets IL transactionslines for the receipt

33. JAI_PO_LINE_LOCATIONS
Po details FROM AP DISTRIBUTIONS

34. JAI_PO_TAXES
Gets PO taxes Details for AP Invoice

35. JAI_CMN_JOURNAL_ENTRIES
Stores Accounting Entries Details

36. JAI_CMN_DOCUMENT_TAXES
This table stores the taxes for various India Localization transactions

37. JAI_AP_MATCH_INV_TAXES
This table stores the details of all the TDS taxes that are applicable on an invoice distribution line. This includes, TDS, WCT and ESSI taxes, attached to the 3 segments of the invoice distribution DFF