Infolinks

Friday 11 May 2012

GL-QUE&ANS

===============================================

GENERAL LEDGER FAQS
==================================================

Few Interesting Questions on Oracle GL Journals Entry(GL FAQS)

Few Interesting Questions on Oracle GL Journals Entry


Is There a Report That Displays Information of One Specific Journal Entry Unposted/Posted?
No. General Ledger reports display information of journal batches posted or unposted. However, you can use the below sql query to find information of a particular journal entry.

select  b.je_batch_id batch_id ,
        h.je_header_id header_id ,
        l.je_line_num line ,
        l.code_combination_id ccid ,
        g.segment1 || '.' || g.segment2 || '.' || g.segment3 ||
        '.' || g.segment4 || '.' || g.segment5 || '.' || g.segment6 ||
        '.' || g.segment7 || '.' || g.segment8 || '.' || g.segment9 ||
        '.' || g.segment10 combination ,
        l.entered_dr entered_dr,
        l.entered_cr entered_cr,
        l.accounted_dr accounted_dr,
        l.accounted_cr accounted_cr,
        l.status
from    gl_je_lines l,
        gl_je_headers h,
        gl_je_batches b,
        gl_code_combinations g
where   b.je_batch_id = h.je_batch_id
        and h.je_header_id = &je_header_id
        and l.je_header_id = h.je_header_id
        and h.je_batch_id = b.je_batch_id
        and l.code_combination_id = g.code_combination_id
order by h.je_header_id, l.je_line_num;

Can a Posted General Ledger Journal Entry be deleted?
After a journal entry is posted, it cannot be deleted. Posted journal entries cannot be deleted because that would eliminate the audit trail. To nullify the accounting effect of the posted journal entry, you can reverse it.
When can not a journal batch be deleted or modified?
A journal batch cannot be deleted or modified under the following circumstances:
a. The source is frozen
b. Funds have been reserved for the batch
c. Funds are in the process of being reserved for the batch
d. The batch is in the process of being posted
e. The batch is posted
f. The batch is approved
g. The batch is in the process of being approved
A journal batch should not be updated if it comes from a sub-ledger.
Changing accounting information in a journal that originated in a sub-module will unsynchronize the accounting information between the ledger and the sub-ledger. Instead of changing the sub-ledger journal, define a new journal to adjust the accounting information if necessary.
A journal batch that has funds reversed cannot be updated because the funds would not be re-reserved appropriately.
Which report shows details of a posted journal batch?
Journals – General (180 Char) and Journals – General (132 Char)
Is possible to restrict users from entering negative amounts in journal lines?
Unfortunately, it is not possible to restrict users from entering negative amounts in journal entry lines.
How to set up journal approval in General Ledger?
This is set up using Oracle Workflow Builder. The basics steps to setup Journal Approval are as below
a) Enable Journal Approval at the Ledger level b) Setup Journal Sources for Journal Approval c) Configure the profile options that control how the approval list will be built d) Define Employees and Supervisors e) Define Approval limits for approvers f) Associate the employees to Oracle Apps users g) Optional Workflow Configuration
For more information refer metalink notes: ID 176459.1 & ID 278349.1
How do you attach an Excel spreadsheet to a journal entry in Oracle General Ledger?
  1. Query the Journal that needs the spreadsheet attachment.
  2. Click on the paperclip on the tool bar.
  3. Fill the following fields in the Attachment form.
     Category    -  Choose Journal from LOV
     Description -  optional
     Data Type   -  OLE Object from the LOV
  4. Right click on the large white portion of the Attachment form choose ‘Insert Object’ from the drop box.
  5. When the Insert Object Form appears check “create from file” and click on “Browse” to choose the file that should be attached from the directory structure.
  6. Save.
How do you easily copy a journal entry from one set of books to another?
There is no standard feature to copy journal entries between sets of books. However, there are some alternatives. Refer Metalink note: ID 204082.1
How to prevent user’s ability to reverse unposted journals?
For 11i, there is not a method to prevent users from reversing unposted journals. This is intended functionality to incorporate the maximum flexibility that users may require. However you can limit user access to journal reversals through user menus set up in Sys Admin responsibility.
This functionality changed in R12 – see Note 734848.1 In Release 12, a batch must be posted before it can be reversed.
How do you automatically generate a reversal journal entry for a journal category in the previous accounting period?
If you routinely generate and post large numbers of journal reversals as part of your month end closing and opening procedures, you can save time and reduce entry errors by using Automatic Journal Reversal to automatically generate and post your journal reversals.
First you define journal reversal criteria for journal categories. Journal reversal criteria let you specify the reversal method, period and date. You can also choose to enable automatic generation and posting of journals.
When you create a journal entry you want to automatically reverse, specify a journal category that has assigned reversal criteria. Your journal will be reversed based on the method, period and date criteria you defined for that journal category.
In Release 12, a reversal journal that is Unposted cannot be modified. Why?
This is the expected functionality in Release 12. However the profile GL: Edit Reverse Journals can be set to allow the modification. Refer metalink note: ID 567641.1
Reversing journal was deleted from the system, how can you still reverse the original journal?
General Ledger does not allow you to reverse a journal entry twice. . Refer metalink note: ID 145043.1 for details.
A journal entry with a source set up for automatic reversal is not reversed. Why?
General Ledger automatically submits the AutoReverse program when a period is opened if the profile option, GL: Launch AutoReverse After Open Period, is set to Yes. If a journal is created after the period has already been opened, then the AutoReverse program will need to be submitted manually.
A journal has been created and is unposted.  The following period has a reversing journal for the original journal and it is posted. Why it is so?
This is currently the functionality of the application to allow the reversing journal to be posted even if the original journal is not.
Few Concepts on General Ledger
What is General Ledger?
The Oracle General Ledger is the central repository of accounting information. The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help people inside and outside the organization make decisions.
 General Ledger Accounting Cycle:

   1. Open period
   2. Create/reverse journal entries
   3. Post
   4. Review
   5. Revalue
   6. Translate
   7. Consolidate
   8. Review/correct balances
   9. Run accounting reports
  10. Close accounting period

What are Set of Books?
A set of books determines the functional currency, account structure, and accounting calendar for each company or group of companies. It is replaced by the Ledger Sets in R12.
Set of Books consists of the following Three elements

    * Chart of Accounts: COA can be designed to match the Organizational Structure and dimensions of the business.
    * Currency:  GL enables to define one currency as Functional Currency and use other currencies for transactions.
    * Calendar: Calendar has to be defined to control the accounting year and its periods.

Types of Journal Entries:
Within Oracle General Ledger, you can work with the following types of journal entries:
Manual Journal Entries
The basic journal entry type is used for most accounting transactions. Examples include adjustments and reclassifications.
Reversing Journal Entries
Reversing journal entries are created by reversing an existing journal entry. You can reverse any journal entry and post it to the current or any future open accounting period.
Recurring Journal Entries
Recurring journal entries are defined once, then are repeated for each subsequent accounting period you generate. You can use recurring journal entries to define automatic consolidating and eliminating entries. Examples include intercompany debt, bad debt expense, and periodic accruals.
Mass Allocations
Mass Allocations are journal entries that utilize a single journal entry formula to allocate balances across a group of cost centers, departments, divisions or other segments. Examples include rent expense allocated by headcount or administrative costs allocated by machine labor hours.
Foreign Currency Concepts:
The three key foreign currency concepts in Oracle General Ledger are:
Conversion
Conversion refers to foreign currency transactions that are immediately converted at the time of entry to the functional currency of the set of books in which the transaction takes place.
Revaluation
Revaluation adjusts liability or asset accounts that may be materially understated or overstated at the end of a period due to a fluctuation in the exchange rate between the time the transaction was entered and the end of the period.
Translation
Translation refers to the act of restating an entire set of books or balances for a company from the functional currency to a foreign currency.
What are Financial Statement Generator Reports (FSG)?
Oracle General Ledger’s Financial Statement Generator (FSG) is a powerful and flexible tool you can use to build your own custom reports without programming. You can define custom financial reports, such as income statements and balance sheets, online with complete control over the rows, columns, and content of your report. You can control account assignments, headings, descriptions, format, and calculations in addition to the actual content. The reusable report components make building reports quick and easy. You can copy a report component from one report, make minor edits, then apply the report component to a new report without having to create a new report from scratch.
What is Applications Desktop Integrator(ADI)?
Applications Desktop Integrator combines the power of Oracle General Ledger journal entry, budgeting, and report creation, submission, publishing, and analysis within an Excel spreadsheet environment.
Journal Components:
Every journal entry in Oracle General Ledger has three components.

    * Every journal entry belongs to a batch. You create a batch of journal entries by entering a name, control total and description for the batch.
    * This step is optional. If you do not enter batch information, Oracle General Ledger automatically creates one batch for each journal entry, defaulting the name and the latest open period.
    * All journal entries in a batch share the same period.
    * Entering a batch control total and description are optional.
    * If you do not enter a batch name, you must recall the journal entry by date.
    * Batch information is stored in the GL_JE_BATCHES table.

Journal Header Information

    * The header information identifies common details for a single journal entry, such as name, effective date, source, category, currency, description, and control total.
    * Group related lines into journal entries
    * All lines in a journal entry must share the same currency and category.
    * If no journal entry-level information is entered, Oracle General Ledger assigns a default name, category, and the functional currency.
    * Header information is stored in the GL_JE_HEADERS table.

Journal Line Information

    * Journal lines specify the accounting information for the journal entry.
    * Total debits must equal total credits for a journal entry for all journal entries except budget journal entries and statistical journal entries.
    * Description for each line can be entered optionally.
    * Information for journal entry lines is stored in the GL_JE_LINES table.

Journal Posting Methods:
You have three methods to post journal batches.
Batch Posting: Navigate to the Post Journals window to post a group of journal batches.
(N) Journals > Post
Manual Posting: Select the More Actions button from either the Journals window or the Batch window to post a journal batch at the time of entry. This option is available only if the profile option Journals: Allow Posting During Journal Entry has been set to Yes.
When you post journals, Oracle General Ledger posts all journals in a batch. You cannot post individual journal entries in a batch.
(N) Journals > Enter (B) More Actions
Automatic Posting: Run the AutoPost program to post journal batches automatically based on a schedule you define.
(N) Setup > Journals > AutoPost

=====================

Query to find accounting flexfield structure


===================

Thursday, 9 June 2011
Query to find accounting flexfield structure

Query to find accounting flexfield structure

select sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name
, ifs.segment_name
, ifs.application_column_name column_name
, sav1.attribute_value BALANCING
, sav2.attribute_value COST_CENTER
, sav3.attribute_value NATURAL_ACCOUNT
, sav4.attribute_value INTERCOMPANY
, sav5.attribute_value SECONDARY_TRACKING
, sav6.attribute_value GLOBAL
, ffvs.flex_value_set_name
, ffvs.flex_value_set_id
from fnd_id_flex_structures fifs
, fnd_id_flex_structures_tl fifst
, fnd_segment_attribute_values sav1
, fnd_segment_attribute_values sav2
, fnd_segment_attribute_values sav3
, fnd_segment_attribute_values sav4
, fnd_segment_attribute_values sav5
, fnd_segment_attribute_values sav6
, fnd_id_flex_segments ifs
, fnd_flex_value_sets ffvs
, gl_ledgers sob
where 1=1
and fifs.id_flex_code = 'GL#'
and fifs.application_id = fifst.application_id
and fifs.id_flex_code = fifst.id_flex_code
and fifs.id_flex_num = fifst.id_flex_num
and fifs.application_id = ifs.application_id
and fifs.id_flex_code = ifs.id_flex_code
and fifs.id_flex_num = ifs.id_flex_num
and sav1.application_id = ifs.application_id
and sav1.id_flex_code = ifs.id_flex_code
and sav1.id_flex_num = ifs.id_flex_num
and sav1.application_column_name = ifs.application_column_name
and sav2.application_id = ifs.application_id
and sav2.id_flex_code = ifs.id_flex_code
and sav2.id_flex_num = ifs.id_flex_num
and sav2.application_column_name = ifs.application_column_name
and sav3.application_id = ifs.application_id
and sav3.id_flex_code = ifs.id_flex_code
and sav3.id_flex_num = ifs.id_flex_num
and sav3.application_column_name = ifs.application_column_name
and sav4.application_id = ifs.application_id
and sav4.id_flex_code = ifs.id_flex_code
and sav4.id_flex_num = ifs.id_flex_num
and sav4.application_column_name = ifs.application_column_name
and sav5.application_id = ifs.application_id
and sav5.id_flex_code = ifs.id_flex_code
and sav5.id_flex_num = ifs.id_flex_num
and sav5.application_column_name = ifs.application_column_name
and sav6.application_id = ifs.application_id
and sav6.id_flex_code = ifs.id_flex_code
and sav6.id_flex_num = ifs.id_flex_num
and sav6.application_column_name = ifs.application_column_name
and sav1.segment_attribute_type = 'GL_BALANCING'
and sav2.segment_attribute_type = 'FA_COST_CTR'
and sav3.segment_attribute_type = 'GL_ACCOUNT'
and sav4.segment_attribute_type = 'GL_INTERCOMPANY'
and sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
and sav6.segment_attribute_type = 'GL_GLOBAL'
and ifs.id_flex_num = sob.chart_of_accounts_id
and ifs.flex_value_set_id = ffvs.flex_value_set_id
and sob.ledger_id =
nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.ledger_id)
order by sob.name, sob.chart_of_accounts_id, ifs.application_column_name;



=====================
FAQs in Account Payable
=============
FAQs in Account Payable

How many types of Invoices we can create in Oracle Payables?
A. Standard
B. Debit Memo
C. Credit Memo
D. Pre-Payment
E. Expense Report
F. Withholding Tax Invoice
G. Miscellaneous Invoice
Describe the 4-Way Matching?
2 – Way Matching: The Invoice Amount Should match with PO, (Invoice with PO)
3 – Way Matching: The Receipts Should match with Invoice, (Invoice, PO with Receipts)
4 – Way Match: The Goods Should match with Inspection/Accepted (Invoice, PO, Inspections & Receipts)
2-Way (Invoice to Purchase Order)
Quantity billed vs. quantity ordered on shipment line
Invoice unit price vs. purchase order line unit price
3-Way (Invoice to Purchase Order and Receipt)
2-Way match criteria AND
Quantity billed vs. quantity received
Item = Receipt Required
4-Way (Invoice to Purchase Order and Receipt and Inspection)
3-Way match criteria AND
Quantity billed vs. quantity accepted
Note: Quantity accepted = (Quantity received – quantity rejected)
Item = Inspection Required
How many Holds we have?
System Holds: Tax, Quantity Match, Po amount with Invoice Amount
Manual Holds: Invoice Limit, Hold on Invoice
Can you Release Manual Holds? If Yes, How?
Yes. Holds – Release Holds
How many ways you can pay the Invoice Amount?
Apply in Full
Schedule Payments
Installments
What is the difference between Debit and Credit Memo?
Debit Memo will raise the Customer
Credit Memo will raise the Vendor
Can you create a Duplicate Vendor ?
No
Vendor Number Should be Generate Automatic or Manually?
Both, Manual And Automatic
How many key flexfields are there in Payables?
No key flexfields in PO,AP
What are Payment Terms?
Payables uses payment terms to automatically calculate due dates, discount dates, and discount amounts for each invoice you enter. Payment terms will default from the supplier site. If you need to change the payment terms and the terms you want to use are not on the list of values, you can define additional terms in the Payment Terms window.
What are the Payment Methods available?
• Check – You can pay with a manual payment, a Quick payment, or in a payment batch.
• Clearing – Used for recording invoice payments to internal suppliers.
• Electronic – You generate an electronic payment file that you deliver to your bank to create payments. Use Electronic if the invoice will be paid using EFT or EDI.
• Wire – Used to manually record a wire transfer of funds between your bank and your supplier’s bank.
What are the line types of an Invoice?
Item: Item lines capture the details of the goods and services billed on your invoice.
Freight: Freight lines capture the details of your freight charges.
Miscellaneous: Miscellaneous lines capture the details of other charges on your invoices such as installation or service.
Tax: Payables integrates with Oracle E-Business Tax to automatically determine and calculate the applicable tax lines for your invoices.
What are the Invoice Distribution Types?
Item
Freight
Miscellaneous
Tax
Withholding
Prepayment
What are Holds in AP?
Holds can be placed by the system and some holds can be placed manually. Holds prevent payment and, sometimes, accounting of an invoice. Payables applies holds to invoices that fail the Invoice Validation process. Release holds from invoices to allow payment and accounting entry creation for the invoices.
What are the Interface Tables in AP?
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
AP_INTERFACE_CONTROLS
————————————–
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIER_INT_REJECTIONS
What is the API to cancel single AP Invoice?
AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE
What is the API to find invoice status?
AP_INVOICES_PKG.GET_APPROVAL_STATUS
Give some of the Oracle Payables interface?
a) Credit Card Transaction Interface
b) Invoice Import Interface
c) Payables Open Interface
d) Purchase Order Matching
What are the key tables where supplier bank information are stored?
IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL
What is meant by with-holding tax invoice?
A separate tax invoice generated for withholding tax amount. Later this invoice will be knocked of when making the payment made to the authority.
What are all the Modules Interacting with AP?
Cash Management
Oracle iExpenses
General Ledger
Oracle Assets
Subledger Accounting (R12)
HRMS
Project Accounting
Purchasing/iprocurement
Global Accounting Engine (11i)
What are the mandatory setups in AP?
1- Financial Options
2- Define Suppliers
3- Define Payment Terms
4- Define Payment Methods
5- Define Banks and Banks Accounts And Banks Accounts Documents
6- Open AP Accounts Periods
What is pay date basis?
The Pay Date Basis for a supplier determines the pay date for a supplier’s invoices.
• Due
• Discount
What is the difference between quick payment and manual payment?
Quick Payment: It allows you to make a single payment against one or more invoices at a time to one supplier through payables.
Manual Payment: This is the process of entering the check details which has been paid manually in some emergency requirements into the payment form and selecting the invoices of the concerned supplier and check whether the total of the invoices and the paid amount at the header are same and save.
What is terms date basis?
Terms Date Basis is to calculate due date.
Due date is calculated 4way. Eg: payment term is 30days

    * Due date = Sysdate + 30days
    * Due date = Invoice date + 30days
    * Due date = Goods Receive Date + 30days
    * Due date = Invoice Received date + 30days

How you will transfer payables to general ledger?
When the payable accounting entries are created, then run the program called ‘Payables Transfer to GL’ Program, which sends the invoice entries and payable entries to GL interface. Then submit a request called Journal import to import journal entries to GL.
What are Aging Periods?
Aging periods are nothing but the periods that we setup to control and maintain the supplier outstanding bill towards the invoice. From this we can able to study the due date of the supplier form the generation of invoice.
What is a “Mixed” Invoice and how do I enter one?
Mixed Invoices are invoices or credit/debit memos for which you can perform both positive and negative matching to purchase orders and to other invoices.
For example, you can enter an invoice for -$100 with Invoice Type Mixed. You can match to an invoice for $-200, and match to a purchase order for $100.
To enter a Mixed invoice:
1. Enter the invoice or credit/debit memo in the Invoices Summary, and enter Mixed as the invoice Type. You can enter either a positive or negative invoice amount.
2. Match to purchase orders, and/or invoices.


====================================
API to cancel AP Invoice
=======================

API to cancel AP Invoice

AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE:
Is_Invoice_Cancellable is a Function in the AP_CANCEL_PKG package that checks that an Invoice is cancellable or not when an Invoice Cancellation process starts.
It follows the following steps and returns a Boolean value depending on the result.

    * If invoice contains distribution that does not have open GL period return FALSE.
    * If invoice has an effective payment, return FALSE.
    * If invoice is selected for payment, return FALSE.
    * If invoice is already cancelled, return FALSE.
    * If invoice is credited invoice, return FALSE.
    * If invoices have been applied against this invoice, return FALSE.
    * If invoice is matched to Finally Closed PO’s, return FALSE.
    * If project related invoices have pending adjustments, return FALSE.
    * If cancelling will cause qty_billed or amount_billed to less than 0, return FALSE.
    * If none of above, invoice is cancellable return True.

Here is a small procedure to check if an Invoice is cancellable or not.


create or replace procedure XX_INV_CANCELLABLE (p_inv_id IN NUMBER)
is
v_boolean               BOOLEAN;
v_error_code            VARCHAR2(100);
v_debug_info            VARCHAR2(1000);

begin
v_boolean :=AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE(
                P_invoice_id       => p_inv_id,
                P_error_code       => v_error_code,
                P_debug_info       => v_debug_info,
                P_calling_sequence => NULL);

IF v_boolean=TRUE
THEN
DBMS_OUTPUT.put_line ('Invoice '||p_inv_id|| ' is cancellable' );
ELSE
DBMS_OUTPUT.put_line ('Invoice '||p_inv_id|| ' is not cancellable :'|| v_error_code );

END IF;
End XX_INV_CANCELLABLE;

Execute XX_INV_CANCELLABLE(12960);

AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE:
AP_CANCEL_SINGLE_INVOICE is a Function in the AP_CANCEL_PKG package that cancels one invoice by executing the following sequence of steps, returning TRUE if successful and FALSE otherwise.
1] Check if the invoice is cancellable. if yes, proceed otherwise return false.
2] If invoice has tax withheld, undo withholding.
3] Clear out all payment schedules.
4] Cancel all the non-discard lines.
a. reverse matching
b. fetch the maximum distribution line number
c. Set encumbered flags to ‘N’
d. Accounting event generation
e. reverse the distributions
f. update Line level Cancelled information
5] Zero out the Invoice.
6] Run AutoApproval for this invoice.
7] Check posting holds remain on this cancelled invoice.
a. if NOT exist – complete the cancellation by updating header level information set return value to TRUE.
b. if exist – no update, set the return values to FALSE, NO DATA rollback.
8] Commit the Data.
9] Populate the out parameters.
Here is a small procedure to cancel a single invoice.

create or replace procedure XX_INV_CANCEL(
                            P_xx_invoice_id IN NUMBER,
                            P_xx_last_updated_by IN  NUMBER,
                            P_xx_last_update_login IN  NUMBER,
                            P_xx_accounting_date IN  DATE)
is

v_boolean               BOOLEAN;
v_message_name          VARCHAR2(1000);
v_invoice_amount        NUMBER;
v_base_amount           NUMBER;
v_temp_cancelled_amount NUMBER;
v_cancelled_by          VARCHAR2(1000);
v_cancelled_amount      NUMBER;
v_cancelled_date        DATE;
v_last_update_date      DATE;
v_orig_prepay_amt       NUMBER;
v_pay_cur_inv_amt       NUMBER;
v_token                 VARCHAR2(100);

begin
v_boolean := AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE
            (p_invoice_id                 => P_xx_invoice_id,
             p_last_updated_by            => P_xx_last_updated_by,
             p_last_update_login          => P_xx_last_update_login,
             p_accounting_date            => P_xx_accounting_date,
             p_message_name               => v_message_name,
             p_invoice_amount             => v_invoice_amount,
             p_base_amount                => v_base_amount,
             p_temp_cancelled_amount      => v_temp_cancelled_amount,
             p_cancelled_by               => v_cancelled_by,
             p_cancelled_amount           => v_cancelled_amount,
             p_cancelled_date             => v_cancelled_date,
             p_last_update_date           => v_last_update_date,
             p_original_prepayment_amount => v_orig_prepay_amt,
             p_pay_curr_invoice_amount    => v_pay_cur_inv_amt,
             P_Token                      => v_token,
             p_calling_sequence           => NULL
             );

IF v_boolean
THEN
DBMS_OUTPUT.put_line ('Successfully Cancelled the Invoice' );
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Failed to Cancel the Invoice' );
ROLLBACK;
END IF;

end XX_INV_CANCEL;

Execute XX_INV_CANCEL(120573,2325,-1,SYSDATE);


============================
HZ(TCA) tables in Oracle Receivables

==============================

HZ(TCA) tables in Oracle Receivables
This article describes few important HZ tables in AR and their relationships with each other.
HZ_PARTIES:
The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.
Few Important Columns are

    * PARTY_ID: Party identifier
    * PARTY_NUMBER: Unique identification number for this party
    * PARTY_NAME: Name of the party
    * PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.

HZ_PARTY_SITES:
The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.
Few Important Columns are

    * PARTY_SITE_ID: Party site identifier.
    * PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
    * LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
    * PARTY_SITE_NUMBER: Party site number.
    * PARTY_SITE_NAME: User-defined name for the site.
    * ADDRESSEE: Addressee information.

HZ_LOCATIONS:
The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.
Few Important Columns are

    * LOCATION_ID: Unique identifier for this location
    * COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
    * ADDRESS1: First line for address
    * ADDRESS2: Second line for address
    * ADDRESS3: Third line for address
    * ADDRESS4: Fourth line for address
    * CITY: City
    * POSTAL_CODE: Postal Code
    * STATE: State
    * ADDRESS_KEY: Derived key that facilitates fuzzy searches

HZ_CUST_ACCOUNTS:
The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.
Few Important Columns are

    * CUST_ACCOUNT_ID: Customer account identifier
    * PARTY_ID: A foreign key to the HZ_PARTY table.
    * ACCOUNT_NUMBER: Account Number
    * CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
    * CUSTOMER_CLASS_CODE: Customer class identifier

HZ_CUST_ACCT_SITES_ALL:
The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.
Few Important Columns are

    * CUST_ACCT_SITE_ID: Customer site identifier
    * CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
    * PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
    * BILL_TO_FLAG: Indicates if this is a Bill-To site.
    * SHIP_TO_FLAG: Indicates if this is a Ship-To site.
    * MARKET_FLAG: Indicates if this is a Marketing site.

HZ_CUST_SITE_USES_ALL:
The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.
Few Important Columns are

    * SITE_USE_ID: Site use identifier
    * CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
    * SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
    * PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.

HZ_CUSTOMER_PROFILES:
The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.
Few Important Columns are

    * CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
    * CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
    * STATUS: Indicates whether the profile is active or inactive

HZ_CUST_PROFILE_CLASSES:
The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.
HZ_PARTY_RELATIONSHIPS:
The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.


=================================================

Chart of Accounts Implementation in Oracle Apps R12


================================================

Chart of Accounts Implementation in Oracle Apps R12
Part of this Post contains the below,

   1. Definition of Chart of Accounts
   2. Overview
   3. Graphical Representation

Definition from Wikipedia:
Chart of accounts (COA) is a list of the accounts used by an organization. The list can be numerical, alphabetic, or alpha-numeric. The structure and headings of accounts should assist in consistent posting of transactions. Each nominal ledger account is unique to allow its ledger to be located. The list is typically arranged in the order of the customary appearance of accounts in the financial statements, profit and loss accounts followed by balance sheet accounts.

Overview:
This post will introduce the process flow for creating a chart of accounts. The chart of Accounts defines the accounting structure of the organization. This structure includes every aspects of the business like business units, accounts, products, services, geographical locations etc. Further COA also tells us about how the elements of the structure combined to form the account combination.

Uses:

   1. Accounting combinations defined in Chart of Accounts is used to various transactions happening in the organization.
   2. Helps in generating account balances.
   3. Helps in Reporting
   4. Helps in Analyzing financial information
   5. Many more …


Basic Steps Involved in Implementation:



Steps in Detail:

1. Value Set Definition:

The value set is the group of values that determine the attributes of the segment. The definition of value set decides whether the value entered for the corresponding segment is acceptable or not. We have to define the value sets for each segment we planned to have in Account combination.

Navigation: General Ledger Super User Responsibility
Setup à Financials à Flexifeilds à Validation à Sets


 2. Defining Accounting Flexifield Structure

Define an accounting flexfield structure using the Key Flexfield Segments form.

Caution 1:
Once we freeze our account structure in the Key Flexfield Segments window and begin using account numbers in data entry, we should not modify the flexfield definition. Changing the existing flexfield structure after flexfield data has been created can cause serious data inconsistencies. Modifying your existing structures may also adversely affect the behavior of cross–validation rules and shorthand aliases.

Caution 2:
Once you are done entering the segment information, click on flexfield qualifier and designate one of your segments as the natural account segment and another as the balancing segment. You can optionally designate a cost center segment and/or intercompany segment. This is the most important step.

Navigation: General Ledger Super User Responsibility
Setup à Financials à Flexfield à Keyà Segments



 3:  Entering Segment Values

We enter segment values which is valid for our application or organization. The valid value can be a phrase, word, abbreviation or numeric code. The valid value must conform to the criteria defined for the respective valid set.

Caution :
If you plan on defining summary accounts or reporting hierarchies, you must define parent values as well as child or detail values.
You can set up hierarchy structures for your segment values. Define parent values that include child values. You can view a segment value’s hierarchy structure as well as move the child ranges from one parent value to another.

Navigation: General Ledger Super User Responsibility
Setup à Financials à Flexfield à Keyà Values



4. Entering Account Combinations

This step is optional. Account combinations are part of Journal Transactions.
We can manually enter the new account combinations in a chart of accounts of a company using GL Accounts form. Anyhow, if we have checked the “Allow dynamic inserts” check box in segments for then we don’t need to worry about this step.

Navigation: General Ledger Super User Responsibility
Setup à Account à Combinations



5. Creation of Account Alias:
This step is again optional. For input or Retrieve data about a transaction in Oracle General Ledger requires the complete Account Combination. But generally the account combination is large and very difficult to remember. Hence we define a short name (Alias) for the Account combination which we use widely.

Detail Explanation of this step is available in another article. Please click the below link

For input or Retrieve data about a transaction in Oracle General Ledger requires the complete Account Combination. But generally the account combination is large and very difficult to remember. Hence we define a short name (Alias) for the Account combination which we use widely.

Let us see how we can create Account alias for the combination
“Vision Distribution.0110.000.100300.0000.00000.00000.0110” as “FuelAcc”

1. Navigation:

2. Shortand Alias Form




3. Click the Find icon in the toolbar to choose the Accounting Flexi field



This will automatically populate the Application, structure, flexifield title, descriptin fields as below.


 4. Next populate the Shorthand related specifications like below,


 5. Next populate the tab “Aliases, Description” as below,


 6. Next we need to populate the tab “Aliases,Effective” as below



7. Next Step is to save and Transaction. While saving we will be shown  a note window to recompile the flexifield using segments form. This is an important step to see our changes

 All the above information is stored in database table named FND_SHORTHAND_FLEX_ALIASES

6. Define Flexfield Security Rules

This step is to prevent group of users from accessing specific segment values while data entry and in report parameters. This maintains the integrity of accounting data. The flexfield security rule is effective only when assigned to an appropriate responsibility.
However to restrict all the users from accessing the particular segment value we need to disable them in segment s form.

Navigation: General Ledger Super User Responsibility
Setup à Financials à Flexfield à Keyà Security à Define



7. Define Cross Validation Rules

This step is required to maintain a consistent and valid set of account combination based on our business requirements. Cross validation rule prevent users from entering invalid account combinations. Cross validation rules validate only new account combinations hence it needs to be implemented before entering the chart of accounts.

Navigation: General Ledger Super User Responsibility
Setup à Financials à Flexfield à Keyà RuleS

============================================================
Few Interesting Questions on Oracle GL Journals Entry


=====================
Is There a Report That Displays Information of One Specific Journal Entry Unposted/Posted?
No. General Ledger reports display information of journal batches posted or unposted. However, you can use the below sql query to find information of a particular journal entry.

select  b.je_batch_id batch_id ,
        h.je_header_id header_id ,
        l.je_line_num line ,
        l.code_combination_id ccid ,
        g.segment1 || '.' || g.segment2 || '.' || g.segment3 ||
        '.' || g.segment4 || '.' || g.segment5 || '.' || g.segment6 ||
        '.' || g.segment7 || '.' || g.segment8 || '.' || g.segment9 ||
        '.' || g.segment10 combination ,
        l.entered_dr entered_dr,
        l.entered_cr entered_cr,
        l.accounted_dr accounted_dr,
        l.accounted_cr accounted_cr,
        l.status
from    gl_je_lines l,
        gl_je_headers h,
        gl_je_batches b,
        gl_code_combinations g
where   b.je_batch_id = h.je_batch_id
        and h.je_header_id = &je_header_id
        and l.je_header_id = h.je_header_id
        and h.je_batch_id = b.je_batch_id
        and l.code_combination_id = g.code_combination_id
order by h.je_header_id, l.je_line_num;

Can a Posted General Ledger Journal Entry be deleted?
After a journal entry is posted, it cannot be deleted. Posted journal entries cannot be deleted because that would eliminate the audit trail. To nullify the accounting effect of the posted journal entry, you can reverse it.
When can not a journal batch be deleted or modified?
A journal batch cannot be deleted or modified under the following circumstances:
a. The source is frozen
b. Funds have been reserved for the batch
c. Funds are in the process of being reserved for the batch
d. The batch is in the process of being posted
e. The batch is posted
f. The batch is approved
g. The batch is in the process of being approved
A journal batch should not be updated if it comes from a sub-ledger.
Changing accounting information in a journal that originated in a sub-module will unsynchronize the accounting information between the ledger and the sub-ledger. Instead of changing the sub-ledger journal, define a new journal to adjust the accounting information if necessary.
A journal batch that has funds reversed cannot be updated because the funds would not be re-reserved appropriately.
Which report shows details of a posted journal batch?
Journals – General (180 Char) and Journals – General (132 Char)
Is possible to restrict users from entering negative amounts in journal lines?
Unfortunately, it is not possible to restrict users from entering negative amounts in journal entry lines.
How to set up journal approval in General Ledger?
This is set up using Oracle Workflow Builder. The basics steps to setup Journal Approval are as below
a) Enable Journal Approval at the Ledger level b) Setup Journal Sources for Journal Approval c) Configure the profile options that control how the approval list will be built d) Define Employees and Supervisors e) Define Approval limits for approvers f) Associate the employees to Oracle Apps users g) Optional Workflow Configuration
For more information refer metalink notes: ID 176459.1 & ID 278349.1
How do you attach an Excel spreadsheet to a journal entry in Oracle General Ledger?
  1. Query the Journal that needs the spreadsheet attachment.
  2. Click on the paperclip on the tool bar.
  3. Fill the following fields in the Attachment form.
     Category    -  Choose Journal from LOV
     Description -  optional
     Data Type   -  OLE Object from the LOV
  4. Right click on the large white portion of the Attachment form choose ‘Insert Object’ from the drop box.
  5. When the Insert Object Form appears check “create from file” and click on “Browse” to choose the file that should be attached from the directory structure.
  6. Save.
How do you easily copy a journal entry from one set of books to another?
There is no standard feature to copy journal entries between sets of books. However, there are some alternatives. Refer Metalink note: ID 204082.1
How to prevent user’s ability to reverse unposted journals?
For 11i, there is not a method to prevent users from reversing unposted journals. This is intended functionality to incorporate the maximum flexibility that users may require. However you can limit user access to journal reversals through user menus set up in Sys Admin responsibility.
This functionality changed in R12 – see Note 734848.1 In Release 12, a batch must be posted before it can be reversed.
How do you automatically generate a reversal journal entry for a journal category in the previous accounting period?
If you routinely generate and post large numbers of journal reversals as part of your month end closing and opening procedures, you can save time and reduce entry errors by using Automatic Journal Reversal to automatically generate and post your journal reversals.
First you define journal reversal criteria for journal categories. Journal reversal criteria let you specify the reversal method, period and date. You can also choose to enable automatic generation and posting of journals.
When you create a journal entry you want to automatically reverse, specify a journal category that has assigned reversal criteria. Your journal will be reversed based on the method, period and date criteria you defined for that journal category.
In Release 12, a reversal journal that is Unposted cannot be modified. Why?
This is the expected functionality in Release 12. However the profile GL: Edit Reverse Journals can be set to allow the modification. Refer metalink note: ID 567641.1
Reversing journal was deleted from the system, how can you still reverse the original journal?
General Ledger does not allow you to reverse a journal entry twice. . Refer metalink note: ID 145043.1 for details.
A journal entry with a source set up for automatic reversal is not reversed. Why?
General Ledger automatically submits the AutoReverse program when a period is opened if the profile option, GL: Launch AutoReverse After Open Period, is set to Yes. If a journal is created after the period has already been opened, then the AutoReverse program will need to be submitted manually.
A journal has been created and is unposted.  The following period has a reversing journal for the original journal and it is posted. Why it is so?
This is currently the functionality of the application to allow the reversing journal to be posted even if the original journal is not.
Few Concepts on General Ledger
What is General Ledger?
The Oracle General Ledger is the central repository of accounting information. The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help people inside and outside the organization make decisions.
 General Ledger Accounting Cycle:

   1. Open period
   2. Create/reverse journal entries
   3. Post
   4. Review
   5. Revalue
   6. Translate
   7. Consolidate
   8. Review/correct balances
   9. Run accounting reports
  10. Close accounting period

What are Set of Books?
A set of books determines the functional currency, account structure, and accounting calendar for each company or group of companies. It is replaced by the Ledger Sets in R12.
Set of Books consists of the following Three elements

    * Chart of Accounts: COA can be designed to match the Organizational Structure and dimensions of the business.
    * Currency:  GL enables to define one currency as Functional Currency and use other currencies for transactions.
    * Calendar: Calendar has to be defined to control the accounting year and its periods.

Types of Journal Entries:
Within Oracle General Ledger, you can work with the following types of journal entries:
Manual Journal Entries
The basic journal entry type is used for most accounting transactions. Examples include adjustments and reclassifications.
Reversing Journal Entries
Reversing journal entries are created by reversing an existing journal entry. You can reverse any journal entry and post it to the current or any future open accounting period.
Recurring Journal Entries
Recurring journal entries are defined once, then are repeated for each subsequent accounting period you generate. You can use recurring journal entries to define automatic consolidating and eliminating entries. Examples include intercompany debt, bad debt expense, and periodic accruals.
Mass Allocations
Mass Allocations are journal entries that utilize a single journal entry formula to allocate balances across a group of cost centers, departments, divisions or other segments. Examples include rent expense allocated by headcount or administrative costs allocated by machine labor hours.
Foreign Currency Concepts:
The three key foreign currency concepts in Oracle General Ledger are:
Conversion
Conversion refers to foreign currency transactions that are immediately converted at the time of entry to the functional currency of the set of books in which the transaction takes place.
Revaluation
Revaluation adjusts liability or asset accounts that may be materially understated or overstated at the end of a period due to a fluctuation in the exchange rate between the time the transaction was entered and the end of the period.
Translation
Translation refers to the act of restating an entire set of books or balances for a company from the functional currency to a foreign currency.
What are Financial Statement Generator Reports (FSG)?
Oracle General Ledger’s Financial Statement Generator (FSG) is a powerful and flexible tool you can use to build your own custom reports without programming. You can define custom financial reports, such as income statements and balance sheets, online with complete control over the rows, columns, and content of your report. You can control account assignments, headings, descriptions, format, and calculations in addition to the actual content. The reusable report components make building reports quick and easy. You can copy a report component from one report, make minor edits, then apply the report component to a new report without having to create a new report from scratch.
What is Applications Desktop Integrator(ADI)?
Applications Desktop Integrator combines the power of Oracle General Ledger journal entry, budgeting, and report creation, submission, publishing, and analysis within an Excel spreadsheet environment.
Journal Components:
Every journal entry in Oracle General Ledger has three components.

    * Every journal entry belongs to a batch. You create a batch of journal entries by entering a name, control total and description for the batch.
    * This step is optional. If you do not enter batch information, Oracle General Ledger automatically creates one batch for each journal entry, defaulting the name and the latest open period.
    * All journal entries in a batch share the same period.
    * Entering a batch control total and description are optional.
    * If you do not enter a batch name, you must recall the journal entry by date.
    * Batch information is stored in the GL_JE_BATCHES table.

Journal Header Information

    * The header information identifies common details for a single journal entry, such as name, effective date, source, category, currency, description, and control total.
    * Group related lines into journal entries
    * All lines in a journal entry must share the same currency and category.
    * If no journal entry-level information is entered, Oracle General Ledger assigns a default name, category, and the functional currency.
    * Header information is stored in the GL_JE_HEADERS table.

Journal Line Information

    * Journal lines specify the accounting information for the journal entry.
    * Total debits must equal total credits for a journal entry for all journal entries except budget journal entries and statistical journal entries.
    * Description for each line can be entered optionally.
    * Information for journal entry lines is stored in the GL_JE_LINES table.

Journal Posting Methods:
You have three methods to post journal batches.
Batch Posting: Navigate to the Post Journals window to post a group of journal batches.
(N) Journals > Post
Manual Posting: Select the More Actions button from either the Journals window or the Batch window to post a journal batch at the time of entry. This option is available only if the profile option Journals: Allow Posting During Journal Entry has been set to Yes.
When you post journals, Oracle General Ledger posts all journals in a batch. You cannot post individual journal entries in a batch.
(N) Journals > Enter (B) More Actions
Automatic Posting: Run the AutoPost program to post journal batches automatically based on a schedule you define.
(N) Setup > Journals > AutoPost




======================================

No comments:

Post a Comment