===============================================
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
Read more: http://prasanthapps.blogspot.com/2011/06/few-interesting-questions-on-oracle-gl.html#ixzz1qnAWX7X0
=====================
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.
Read more: http://prasanthapps.blogspot.com/2011/06/faqs-in-account-payable.html#ixzz1qnB9YXNh
====================================
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);
Read more: http://prasanthapps.blogspot.com/2011/06/api-to-cancel-ap-invoice.html#ixzz1qnBKIUHp
============================
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.
Read more: http://prasanthapps.blogspot.com/search/label/HZ%28TCA%29%20tables%20in%20Oracle%20Receivables#ixzz1qnBXnEba
=================================================
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
Read more: http://prasanthapps.blogspot.com/2011/06/few-interesting-questions-on-oracle-gl.html#ixzz1qnC7QBgE
=====================
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
Read more: http://prasanthapps.blogspot.com/2011/06/few-interesting-questions-on-oracle-gl.html#ixzz1qnCCkcW7
======================================GL QUERY========
SELECT b.NAME je_batch_name,
b.description je_batch_description,
b.running_total_accounted_dr je_batch_total_dr,
b.running_total_accounted_cr je_batch_total_cr,
b.status je_batch_status,
b.default_effective_date je_batch_effective_date,
b.default_period_name je_batch_period_name,
b.creation_date je_batch_creation_date,
u.user_name je_batch_created_by,
h.je_category je_header_category,
h.je_source je_header_source,
h.period_name je_header_period_name,
h.NAME je_header_journal_name,
h.status je_header_journal_status,
h.creation_date je_header_created_date,
u1.user_name je_header_created_by,
h.description je_header_description,
h.running_total_accounted_dr je_header_total_acctd_dr,
h.running_total_accounted_cr je_header_total_acctd_cr,
l.je_line_num je_lines_line_number,
l.ledger_id je_lines_ledger_id,
glcc.concatenated_segments je_lines_ACCOUNT,
l.entered_dr je_lines_entered_dr,
l.entered_cr je_lines_entered_cr,
l.accounted_dr je_lines_accounted_dr,
l.accounted_cr je_lines_accounted_cr,
l.description je_lines_description,
glcc1.concatenated_segments xla_lines_account,
xlal.accounting_class_code xla_lines_acct_class_code,
xlal.accounted_dr xla_lines_accounted_dr,
xlal.accounted_cr xla_lines_accounted_cr,
xlal.description xla_lines_description,
xlal.accounting_date xla_lines_accounting_date,
xlate.entity_code xla_trx_entity_code,
xlate.source_id_int_1 xla_trx_source_id_int_1,
xlate.source_id_int_2 xla_trx_source_id_int_2,
xlate.source_id_int_3 xla_trx_source_id_int_3,
xlate.security_id_int_1 xla_trx_security_id_int_1,
xlate.security_id_int_2 xla_trx_security_id_int_2,
xlate.transaction_number xla_trx_transaction_number,
rcvt.transaction_type rcv_trx_transaction_type,
rcvt.transaction_date rcv_trx_transaction_date,
rcvt.quantity rcv_trx_quantity,
rcvt.shipment_header_id rcv_trx_shipment_header_id,
rcvt.shipment_line_id rcv_trx_shipment_line_id,
rcvt.destination_type_code rcv_trx_destination_type_code,
rcvt.po_header_id rcv_trx_po_header_id,
rcvt.po_line_id rcv_trx_po_line_id,
rcvt.po_line_location_id rcv_trx_po_line_location_id,
rcvt.po_distribution_id rcv_trx_po_distribution_id,
rcvt.vendor_id rcv_trx_vendor_id,
rcvt.vendor_site_id rcv_trx_vendor_site_id
FROM
gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
fnd_user u,
fnd_user u1,
gl_code_combinations_kfv glcc,
gl_code_combinations_kfv glcc1,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla.xla_transaction_entities xlate,
rcv_transactions rcvt
WHERE
b.created_by = u.user_id
AND h.created_by = u1.user_id
AND b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND l.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.application_id = xlah.application_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlal.code_combination_id = glcc1.code_combination_id
AND xlah.application_id = xlae.application_id
AND xlah.event_id = xlae.event_id
AND xlae.application_id = xlate.application_id
AND xlae.entity_id = xlate.entity_id
AND xlate.source_id_int_1 = rcvt.transaction_id
AND h.je_category = 'Receiving'
AND b.default_period_name = '01_APR-2009'
ORDER BY h.je_category;
======joins==
GL_JE_BATCHES (je_batch_id) => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id) => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id, je_line_num) => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id) => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id) => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id) => XLA_EVENTS (application_id, event_id)
XLA_EVENTS (application_id, entity_id) => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)
================
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)
SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number
SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;
Read more: http://prasanthapps.blogspot.com/search/label/Queries%20to%20connect%20RA_CUSTOMER_TRX_ALL%20and%20OE_ORDER_HEADERS_ALL%2FLINES_ALL#ixzz1rAThkB1H
=================
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)
SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number
SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;
Read more: http://prasanthapps.blogspot.com/search/label/Queries%20to%20connect%20RA_CUSTOMER_TRX_ALL%20and%20OE_ORDER_HEADERS_ALL%2FLINES_ALL#ixzz1rAThkB1H
====================
Query : Get Inventory Accounting Entries linked to GL
Query : Get Inventory Accounting Entries linked to GL
select mta.transaction_id,
mmt.organization_id,
msi.segment1,
mta.transaction_date,
mta.primary_quantity,
gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
gcc.segment4 || '.' || gcc.segment5 account,
DECODE(SIGN(mta.transaction_value),
1,
mta.transaction_value,
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
NULL)) Entered_Dr,
DECODE(SIGN(mta.transaction_value),
-1,
(-1 * mta.transaction_value),
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value))) Entered_Cr,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
0,
0,
NULL) Accounted_Dr,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value),
0,
0,
NULL) Accounted_Cr,
gh.currency_code,
mtt.transaction_type_name,
decode(mta.gl_batch_id, -1, 'N', 'Y') "Transfered_Flag",
mta.gl_batch_id,
gh.je_header_id
from inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_accounts mta,
gl.gl_code_combinations gcc,
gl.gl_je_batches gb,
gl.gl_je_headers gh,
gl.gl_je_lines gl,
gl.gl_import_references gr
where mmt.organization_id = msi.organization_id
and msi.inventory_item_id = mmt.inventory_item_id
and mmt.transaction_id = mta.transaction_id
and gcc.code_combination_id = mta.reference_account
and mtt.transaction_type_id = mmt.transaction_type_id
and gb.je_batch_id = gh.je_batch_id
and gh.je_header_id = gl.je_header_id
and gl.code_combination_id = mta.reference_account
and mta.gl_batch_id =
to_number(substr(gb.name, 1, instr(gb.name, ' ') - 1))
and gh.je_Category = 'MTL'
and gh.je_source = 'Inventory'
and gh.name = 'XXX' ---REPLACE XXX WITH NAME
and gl.je_line_num = gr.je_line_num
and gr.je_header_id = gl.je_header_id
and gr.je_line_num = gl.je_line_num
and mta.gl_batch_id = gr.reference_1
and gh.period_name = '&period_name' -- ENTER THE PERIOD
and upper(gb.name) like upper('%&gl_batch_name%')
order by 1
Read more: http://prasanthapps.blogspot.com/search/label/Query%20%3A%20Get%20Inventory%20Accounting%20Entries%20linked%20to%20GL#ixzz1rATz3ouX
=====================
Query : INVENTORY & ORDER MANAGEMENT
Query : INVENTORY & ORDER MANAGEMENT:
Just replace xxx in the query with your organization_id
SELECT
msi.segment1 as Item_Number
, msi.description as Item_Description
, SUM(ool.ordered_quantity) AS SO_quantity
, AVG(oh_qty) as on_hand
FROM
(SELECT
msi.inventory_item_id, msi.organization_id, SUM(ohd.primary_transaction_quantity) AS oh_qty
FROM
mtl_system_items_b msi
JOIN mtl_onhand_quantities_detail ohd ON
(msi.inventory_item_id = ohd.inventory_item_id AND msi.organization_id = ohd.organization_id)
WHERE ohd.subinventory_code = 'Warehouse' AND msi.organization_id = xxx GROUP BY msi.inventory_item_id, msi.organization_id) Q1
JOIN
oe_order_lines_all ool ON (q1.inventory_item_id = ool.inventory_item_id AND q1.organization_id = ool.ship_from_org_id)
JOIN oe_order_headers_all ooh ON (ool.header_id = ooh.header_id)
JOIN mtl_system_items_b msi ON (ool.flow_status_code = 'AWAITING_SHIPPING' AND ool.inventory_item_id = msi.inventory_item_id AND ool.ship_from_org_id = msi.organization_id)
GROUP BY msi.inventory_item_id, msi.segment1, msi.description
ORDER BY msi.segment1
Read more: http://prasanthapps.blogspot.com/search/label/Query%20%3A%20INVENTORY%20and%20ORDER%20MANAGEMENT#ixzz1rAU2dVFD
=======================
Query : Purchase Order and Requisition
Query : Purchase Order and Requisition
SELECT prh.segment1 req_number
,prh.authorization_status
,prl.line_num req_line_num
,prl.item_description req_item_description
,prl.unit_price req_unit_price
,prl.quantity req_quantity
,pd.req_header_reference_num
,pd.req_line_reference_num
,pl.line_num
,pl.item_description
,pl.quantity
,pl.amount
,ph.segment1 po_number
,prd.distribution_id
,pd.req_distribution_id
FROM po_requisition_headers_all prh
,po_requisition_lines_all prl
,po_req_distributions_all prd
,po_distributions_all pd
,po_line_locations_all pll
,po_lines_all pl
,po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
and prh.org_id = prl.org_id
and prl.requisition_line_id = prd.requisition_line_id
and prl.org_id = prd.org_id
and prd.distribution_id = pd.req_distribution_id(+)
and prd.org_id = pd.org_id(+)
and pd.line_location_id = pll.line_location_id(+)
and pd.org_id = pll.org_id(+)
and pll.po_line_id = pl.po_line_id(+)
and pll.org_id = pl.org_id(+)
and pl.po_header_id = ph.po_header_id(+)
and pl.org_id = ph.org_id(+);
Read more: http://prasanthapps.blogspot.com/search/label/Query%20%3A%20Purchase%20Order%20and%20Requisition#ixzz1rAU6NSc9
================
Query : Query to find receipts against a PO shipment line
Query : Query to find receipts against a PO shipment line
execute fnd_client_info.set_org_context('org_id');
SELECT
pol.po_header_id,
pol.po_line_id,
pll.line_location_id,
pll.quantity,
rsh. shipment_header_id,
rsh. receipt_source_code,
rsh. vendor_id,
rsh. vendor_site_id,
rsh. organization_id,
rsh. shipment_num,
rsh. receipt_num,
rsh. ship_to_location_id,
rsh. bill_of_lading,
rsl.shipment_line_id,
rsl.QUANTITY_SHIPPED,
rsl.QUANTITY_RECEIVED ,
rct.transaction_type,
rct.transaction_id,
decode(pol.order_type_lookup_code,'RATE',nvl(rct.amount,0),'FIXED PRICE',nvl(rct.amount,0),
nvl(rct.source_doc_quantity,0) ) transaction_qty
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines pol
, po_line_locations pll
where rct.po_line_location_id = 28302 --- check it out
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
and rct.shipment_line_id=rsl.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
order by rct.transaction_id
Read more: http://prasanthapps.blogspot.com/search/label/Query%20%3A%20Query%20to%20find%20receipts%20against%20a%20PO%20shipment%20line#ixzz1rAUABhqr
====================
Query : Sales Order Details After Pick Release
Query : Sales Order Details After Pick Release:
SELECT ooh.order_number,
ool.line_number,
wnd.delivery_id,
mtrh.request_number
FROM oe_order_headers ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
mtl_txn_request_lines mtrl,
mtl_txn_reuest_headers mtrh
WHERE ooh.order_number = --Order Number Input Parameter
AND ool.header_id = ooh.header_id
AND ool.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
Read more: http://prasanthapps.blogspot.com/search/label/Query%20%3A%20Sales%20Order%20Details%20After%20Pick%20Release#ixzz1rAUED5vW
=================================
Query : Sales Order Hold & Release
Query : Sales Order Hold & Release:
SELECT OHA.ORDER_NUMBER,
HS.HOLD_ENTITY_CODE CREDIT_CHECK_CRITERIA_CODE,
FLV.MEANING CREDIT_CHECK_CRITERIA,
HD.NAME HOLD_NAME,
HD.TYPE_CODE HOLD_TYPE,
HR.RELEASE_REASON_CODE,
FLV1.MEANING RELEASE_REASON,
HS.RELEASED_FLAG,
HS.HOLD_ENTITY_ID,
OH.HOLD_RELEASE_ID,
OH.HEADER_ID,
HS.HOLD_SOURCE_ID
FROM ONT.OE_HOLD_SOURCES_ALL HS,
ONT.OE_HOLD_DEFINITIONS HD,
ONT.OE_ORDER_HOLDS_ALL OH,
ONT.OE_ORDER_HEADERS_ALL OHA,
APPS.FND_LOOKUP_VALUES FLV,
ONT.OE_HOLD_RELEASES HR,
APPS.FND_LOOKUP_VALUES FLV1
WHERE HS.ORG_ID = '&ORG_ID' --- xxx
AND HS.HOLD_ID = HD.HOLD_ID
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND OH.HEADER_ID = OHA.HEADER_ID
AND FLV.LOOKUP_TYPE = 'HOLD_ENTITY_DESC'
AND FLV.VIEW_APPLICATION_ID = 660
AND FLV.LOOKUP_CODE = HS.HOLD_ENTITY_CODE
AND OH.HOLD_RELEASE_ID = HR.HOLD_RELEASE_ID
AND FLV1.LOOKUP_TYPE = 'RELEASE_REASON'
AND FLV1.VIEW_APPLICATION_ID = 660
AND FLV1.LOOKUP_CODE = HR.RELEASE_REASON_CODE
Read more: http://prasanthapps.blogspot.com/search/label/Query%20%3A%20Sales%20Order%20Hold%20and%20Release#ixzz1rAUHRlYu
========================
Queries to Drive price List /Qualifiers/Modifiers/Conext/Segments
Queries to Drive price List /Qualifiers/Modifiers/Conext/Segments
Below queries are for Oracle Applications Order Management and Advance Pricing Modules.
SELECT l.list_line_id,q.qualifier_grouping_no,
q.qualifier_id, q.qualifier_context, q.qualifier_attr_value,
q.comparison_operator_code,q.qualifier_precedence,q.qual_attr_value_from_number,
q.qualifier_attribute,q.end_date_active,l.end_date_active,h.end_date_active
FROM
qp_list_headers_all h,
qp_list_lines l,
qp_qualifiers q
where h.list_header_id = l.list_header_id
and h.list_header_id = q.list_header_id
and h.list_header_id = &list_id -- Price List Header ID or Modifier header ID
and NVL(h.end_date_active,sysdate) >= sysdate
and NVL(l.end_date_active,sysdate) >= sysdate
and NVL(q.end_date_active,sysdate) >= sysdate;
select q.qualifier_id,q.qualifier_context,q.qualifier_attribute,qualifier_attr_value ,
ct.prc_context_id, qs.segment_code
from qp_qualifiers q , qp_prc_contexts_b ct , qp_segments_b qs
where q.list_header_id = &ListHeaderID --PriceList Header ID
and ct.prc_context_type ='QUALIFIER'
and q.qualifier_context = ct.prc_context_code
and qs.prc_context_id = ct.prc_context_id
and qs.segment_mapping_column = q.qualifier_attribute
Read more: http://prasanthapps.blogspot.com/search/label/Queries%20to%20Drive%20price%20List%20%2FQualifiers%2FModifiers%2FConext%2FSegments#ixzz1rAULbOcL
==========================
Query to Join Delivery Details and MTL Material Table
Query to Join Delivery Details and MTL Material Table
select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id,
wdd.released_status, oe_interfaced_flag, inv_interfaced_flag,
wdd.creation_date, mtl.transaction_id, l.flow_status_code,
l.open_flag, l.cancelled_flag
from wsh_delivery_details wdd,
mtl_material_transactions mtl,
oe_order_lines_all l
where
l.line_id = wdd.source_line_id
AND wdd.delivery_Detail_id = mtl.picking_line_id
AND wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'Y'
and wdd.released_status = 'Y'
Also below is query to Join Order line with MTL_MATERIAL_TRANSACTIONS
select mtl.transaction_id, l.flow_status_code,
l.open_flag, l.cancelled_flag , l.shipped_quantity
from
mtl_material_transactions mtl,
oe_order_lines_all l
where
mtl.trx_source_line_id = l.line_id
Read more: http://prasanthapps.blogspot.com/search/label/Query%20to%20Join%20Delivery%20Details%20and%20MTL%20Material%20Table#ixzz1rAVS78Kh
====================
Query to List all the responsibilities attached to a User
Query to List all the responsibilities attached to a User
Based on a request from one of the reader here is the query which he was looking for.
He needed query that can list all the responsibilities attached to a user.
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')
Read more: http://prasanthapps.blogspot.com/search/label/Query%20to%20List%20all%20the%20responsibilities%20attached%20to%20a%20User#ixzz1rAVVHn6b
=============
Query to check all the Picked Delivery Deatils where Reservation not exists
Query to check all the Picked Delivery Deatils where Reservation not exists
Select ool.line_id,
wdd.delivery_detail_id,wdd.source_line_id,wdd.ship_from_location_id,wdd.organi
zation_id,wdd.src_requested_quantity,
wdd.shipped_quantity,wdd.move_order_line_id,wdd.customer_item_id,wdd.split_fro
m_delivery_detail_id,
wdd.source_header_number,wdd.source_line_number,wdd.picked_quantity
from wsh_delivery_details wdd , oe_order_lines_all ool
where wdd.source_line_id = ool.line_id
and ool.open_flag ='Y'
and wdd.source_code ='OE'
and Picked_quantity > 0
and released_status= 'Y'
and ool.header_id = &enter_Sales_Order_Header_id
and not exists
(
select 1 from mtl_reservations mr
where mr.demand_source_line_id = wdd.source_line_id
and mr.inventory_item_id = wdd.inventory_item_id)
Above sample script is with Order_Header_id as a Parameter. If you want to check for all the Orders , then remove the parameter Condition.
Read more: http://prasanthapps.blogspot.com/search/label/Query%20to%20check%20all%20the%20Picked%20Delivery%20Deatils%20where%20Reservation%20not%20exists#ixzz1rAVYFAOm
==============================
No comments:
Post a Comment