Showing posts with label GL. Show all posts

GL :Fetch Batch Name and perioddname from invoice

SELECT gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, TRUNC(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
WHERE glcc.code_combination_id = gje.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gje.je_header_id
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = rcta.trx_number
AND rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND rcta.org_id = rctype.org_id
AND ra.customer_id = rcta.bill_to_customer_id
and rcta.trx_number ='1301117'

GL : 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;

FND : Fetch Parameters and Value Sets associated with a Concurrent Program

The following query will fetch the Parameter List and associated Value Sets of a Concurrent Program.

SELECT
        fcpl.user_concurrent_program_name "Concurrent Program Name",
        fcp.concurrent_program_name "Short Name",
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        fdfcuv.enabled_flag " Enabled Flag",
        fdfcuv.required_flag "Required Flag",
        fdfcuv.display_flag "Display Flag",
        fdfcuv.flex_value_set_id "Value Set Id",
        ffvs.flex_value_set_name "Value Set Name",
        flv.meaning "Default Type",
        fdfcuv.DEFAULT_VALUE "Default Value"
FROM
        fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpl,
        fnd_descr_flex_col_usage_vl fdfcuv,
        fnd_flex_value_sets ffvs,
        fnd_lookup_values flv
WHERE
        fcp.concurrent_program_id = fcpl.concurrent_program_id
        AND    fcpl.user_concurrent_program_name = :conc_prg_name
        AND    fdfcuv.descriptive_flexfield_name = '$SRS$.'
                 || fcp.concurrent_program_name
        AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
        AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
        AND    flv.lookup_code(+) = fdfcuv.default_type
        AND    fcpl.LANGUAGE = USERENV ('LANG')
        AND    flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY fdfcuv.column_seq_num;

GL : Ledger Sets

Ledgers sets allow you to group multiple ledgers together to achieve processing efficiencies.

For example, you can open or close periods for multiple ledgers simultaneously, translate balances for all ledgers in a ledger set, run recurring journals that update balances for multiple ledgers, or run consolidated financial reports that summarize balances across multiple ledgers in a ledger set.

All ledgers in a ledger set must share the same chart of accounts and accounting calendar/period type combination.
They do not have to share the same currency.

This allows you to group the primary or secondary ledgers with their associated reporting currencies to reduce maintenance efforts and streamline processing.

Note: Both the source ledger and its reporting currency (Journal and Subledger level) must have the same open periods to prevent problems during posting in General Ledger.

you can use a ledger set to combine the source ledger with its reporting currencies (journal and subledger levels) to open and close periods across all ledgers simultaneously

Some of the General Ledger features that benefit from the use of ledger sets are as follows:

Translation and Revaluation: Translate balances and run revaluation across multiple ledgers in a ledger set.
Open and Close Periods: Open and close periods for multiple ledgers within a ledger set from a single operation.
Reporting: Submit standard reports and Financial Statement Generator (FSG) reports across multiple ledgers in a ledger set.
The added benefit of using ledger sets in FSG reports is to aggregate data and create summarized balances across multiple ledgers in a ledger set.
Inquiry: Perform online inquiry on account balances or journals across multiple ledgers in a ledger set from a single view; drill down to the journal details and subledger transaction for each ledger.

Create a Legal Entity

The legal entity is an important part of defining the Multi Org Structure. It is a part of the organization which is a registered legal unit to which laws and regulations of a particular territory or geographic area is applicable.

Legal Entity represents a legal company for which Fiscal and Tax reports are prepared. A Legal Entity can Model to an Enterprise, a Designated Legal unit which is recognized by legal authorities in a country.

Legal entity is a business entity that is known to exist to the outside world. Internal organizations or divisions are not legal entities themselves, but are just part of it. In the Multi Organization structure various legal entities could share the same ledger.

Both legal entities and operating units are associated with the ledger and the relationship between the legal entity and operating unit is derived based on the ledger.

A legal Entity can be associated to Single Ledger, Multiple Legal Entities can be Associated to a Ledger.

The Legal Entity Configurator allows you to define legal entities and establishments in the Oracle system to achieve legal compliance for business activities handled by the Oracle E-Business Suite.

Create legal entities with minimum required information using the Create Legal Entity page.
If you create a legal entity from an existing organization, the country, organization name, and organization number are defaulted from the organization. In this case, the organization number is not updatable even if the HZ: Generate Party Number profile option is set to No.

When you create a legal entity, the main establishment is created automatically.

The legal entity territory, name, address, effective date, and other information are defaulted to the main establishment. The establishment inherits the registration number of the legal entity only if the establishment does not have it own number.

Setup >> Financials >> Accounting Setup Manager >> Accounting Setup

Enter the required fields (See example below)

Territory: The territory where the legal entity is registered. This list displays territories at the country level and shows only territories for which the identifying jurisdiction has been defined. Therefore, the territory determines the identifying jurisdiction to which the legal entity needs to register. The territory also determines the context for the information that needs to be displayed in the General Information region. For Canada the BIN number is one such field

Organization Number: The organization number is a number used to identify organizations. This field is displayed only when the HZ: Generate Party Number profile option is set to No. In this case, the main establishment organization number is built as a concatenation of the legal entity organization number and ETB (establishment). Otherwise it is not displayed and is generated automatically. For example, if the legal entity organization number entered is 12536, the establishment's organization number will be 12536ETB.

Legal Entity Identifier: The identification number used to uniquely identify the legal entity. It is displayed only when the LE: Generate Legal Entity Identifier profile option set to No and you must enter it manually. If this option is set to Yes, the legal entity identifier is generated automatically based on the International Organization for Standardization (ISO) code of the country of registration, plus the registration number of the identifying jurisdiction, which qualifies an entity to be a legal entity in that particular territory.

Registration Number: The identifying jurisdiction determines the prompt for the registration number (in the U.S., the EIN/TIN). The Establishment registration prompt is displayed if it is defined for the identifying jurisdiction. In this case the main establishment is created with this registration number. The registration
number must be unique within a jurisdiction.

Legal Address: The address a legal entity uses to register with a legal authority. A legal entity may use different addresses for different authorities and hence, may have more than one registered address. This legal address must be located within the territory entered.

Place Of Registration: Optionally enter the place of the legal entity registration.

Inception Date Optionally enter the date of legal entity registration (creation). It can be on or before the system date and on or after legal entity's inception date but must not be a future date.

Once the above information is entered, select "Save and add details"

CM: Fetch Invoices related Reconciliation Per Currency

SELECT DISTINCT cel.*

FROM CE_STATEMENT_LINES cel,

CE_STATEMENT_HEADERS ceh,

CE_STATEMENT_RECONCILS_ALL csr,

AP_INVOICE_PAYMENTS_ALL aip,

AP_CHECKS_ALL ac

WHERE cel.statement_header_id = ceh.statement_header_id

AND ac.check_id = aip.check_id

AND aip.invoice_id = '166014'

AND aip.check_id = ac.check_id

AND ac.bank_account_id = ceh.bank_account_id

AND TO_CHAR(ac.check_number) = cel.bank_trx_number

AND csr.statement_line_id = cel.statement_line_id;

Simultaneously Opening and Closing Period for Multiple Ledgers

When using the Open and Close period form within General Ledger you are only able to control a single period at a time.

You can however, select which ledger you wish to control a period for without the need to change responsibility.

You now also have the ability to simultaneously open and close multiple accounting periods.

Simply by creating a ledger set you no longer have to open and close periods for each ledger separately by switching responsibilities.

A ledger set can contain any number of ledgers as long as each of the selected ledgers share the same chart of account and calendar. By virtue of sharing the same calendar, the ledgers will also share the same period type.

You are now able to run any of the following open and close period programs from the concurrent manager to open and close periods simultaneously.

Open Period - Used to open periods up to and including the specified target period for one or more ledgers
Periods – Close Period - Used to close a single period for one or more ledgers. Only open periods can be closed. A warning message will appear in the close period program if unposted journals exist.
Periods – Gapless Close Period - Used to close a consecutive range of open periods for one or more ledgers. This program closes the specified period and all prior periods that are still open.
Periods – Permanently Close Period - Used to permanently close one or more closed periods for one or more ledgers. To permanently close a range of closed periods, the specified period and any prior periods must have a status of Closed or Permanently Closed.

If you permanently close a period, all prior periods will also be permanently closed which means that they can never be re-opened.

By controlling multiple GL periods processing efficiencies can easily be gained.

if you use the concurrent programs above, a business can also make use of the various concurrent program options available such as scheduling and building request sets.

Another advantage of this new functionality is the fact that you can now keep periods across multiple ledgers in sync, which is particularly useful for shared service centre operations.

GL :General Ledger Standard Reports

1.   Account Analysis Report - Line Item
2.   Account Analysis Report with Payables Detail
3.   Foreign Account Analysis Report - Line Item
4.   Foreign Account Analysis Report with Payables Detail
5.   Account Analysis Report with Subledger Detail
6.   Budget Heirarchy Listing
7.   Budget Journal by Flexfield
8.   Budget Organization Listing
9.   Budget Organization Range Listing
10.   Summary/Detail Budget Report
11.   Unbudgeted Master/Detail Accounts
12.   Account Hierarchy Report
13.   Chart of Accounts Listing
14.   Detail Rollups by Cost Center
15.   Rollup Range Listing
16.   Segment Values Listing
17.   Suspense Accounts Listing
18.   Consolidation Exceptions Report - Disabled Parents
19.   Consolidation Exception Report - Unmapped Subsidiary
20.   Consolidated Journals Report
21.   Consolidated Rules Report
22.   Daily Conversion Rates Listing
23.   Historical Rates Listing
24.   Period Rates Listing
25.   Column Set Detail Listing
26.   Column Set Summary Listing
27.   Content Set Detail Listing
28.   Content Set Summary Listing
29.   Report Detail Listing
30.   Report Set Detail Listing
31.   Report Set Summary Listing
32.   Report Summary Listing
33.   Row Order Detail Listing
34.   Row Set Detail Listing
35.   Row Set Summary Listing
36.   Where Used Report
37.   Foreign Currency General Ledger
38.   Journal Import
39.   Dual Currency Journals
40.   Foreign Unposted Journals
41.   Posted Journals
42.   Unposted Journals
43.   Error Journals
44.   Journal Batch Summary
45.   Journal Entry Report
46.   Journal Line Report
47.   Journals by Document Number
48.   Budget Trial Balance
49.   Detail Trial Balance
50.   Encumbrance Trial Balance
51.   Expanded Trial Balance
52.   Foreign Currency Detail Trial Balance
53.   Foreign Currency Summary 1 Trial Balance
54.   Summary 1 Trial Balance
55.   Summary 2 Trial Balance
56.   Translation Trial Balance
57.   MassAllocation Formula Listing
58.   Recurring Formula Listing
59.   Unit of Measure Report
60.   Value Added Tax Report
61.   Archive and Purge Audit Report
62.   Assign Budget Account Ranges Execution Report
63.   AutoPost Execution Report
64.   Budget Assignments AutoCopy Execution Report
65.   Budget Spreadsheet Upload Execution Report
66.   Carry Forward Execution Report
67.   Create Journal Entries Execution Report
68.   Delete Journal Import Data Execution Report
69.   Dual Currency Program Execution Report
70.   Historical Rates Execution Report
71.   Journal Import Execution Report
72.   Maintain Batch Period Execution Report
73.   MassAllocation/MassBudgeting Validation Report
74.   MassAllocations Execution Report
75.   MassApproval Journal Execution Report
76.   Posting Execution Report
77.   Revaluation Execution Report
78.   Weighted Average Rate Program Execution Report

EBS Queryboard

GL :Fetch Batch Name and perioddname from invoice

GL : Query to find accounting flexfield structure

FND : Fetch Parameters and Value Sets associated with a Concurrent Program

GL : Ledger Sets

Create a Legal Entity

AP : Create a Link with AP - SLA -GL

CM: Fetch Invoices related Reconciliation Per Currency

Simultaneously Opening and Closing Period for Multiple Ledgers

GL :General Ledger Standard Reports

AR/GL : AR to GL link in R12

Popular Posts

Recent Posts

Categories

Unordered List

Text Widget

Blog Archive