Excel Basics

AR: Basiv(technical foundation)

This post is more on basic table structure of Oracle Account Receivable.

1.Table that holds AR Invoice data
The following tables can give most of the invoice information.

• RA_CUSTOMER_TRX_ALL stores invoice header information. RA_CUSTOMER_TRX_LINES_ALL stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines.
• The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts.
• RA_CUST_TRX_LINE_GL_DIST_ALL stores accounting distribution records for all transaction lines except bills receivable.The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line.
• RA_CUSTOMERS - Customer information
• RA_CUST_TRX_TYPES_ALL - Customer Transaction Type
• AR_PAYMENT_SCHEDULES_ALL
• RA_CUSTOMER_TRX_LINES_ALL - Transaction Line information
• MTL_SYSTEM_ITEMS - Base table for item

The ER Diagram for a customer Transaction can be easily understood as:

2.Customer Payment
These are the main tables which holds Customer Payment information

• AR_CASH_RECEIPTS_ALL stores one record for each receipt entry.
• AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step., Possible statuses are Approved, Cleared,Confirmed, Remitted, and Reversed.
• AR_MISC_CASH_DISTRIBUTIONS_ALL stores all accounting entries for miscellaneous cash applications.
• AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.
• AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
• Each row includes the amount applied, status, and accounting flexfield information.

The ER Diagram for a customer Payment can be easily understood as:

3. Accounting Link between the tables

• For Invoice:

ra_customers (This is for capturing customers information)
ra_customer_trx_all,
ra_cust_trx_types_all,
ar_payment_schedules_all,
ra_customer_trx_lines_all
and joins are customer_trx_id for ar_payment_schedules_all & ra_customer_trx_all & ra_customer_trx_lines_all .
ra_cust_trx_types_all.type IN ('INV')

• For the Receipts:

ar_receivable_applications_all,
ra_customer_trx_all,
ra_customer_trx_lines_all,
ar_cash_receipts_all
ar_receivable_applications_all.application_type = 'CASH',
ar_receivable_applications_all.cash_receipt_id = ar_cash_receipts_all.cash_receipt_id,
ar_receivable_applications_all.applied_customer_trx_id = ra_customer_trx_all.customer_trx_id

11i and R12 Client Info and Mo gloabal

begin
FND_CLIENT_INFO.SET_ORG_CONTEXT('440');
end;

begin
MO_GLOBAL.SET_POLICY_CONTEXT('S',101);
end;

FND LOAD Script

Concurrent program:

FNDLOAD apps/apps10 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct EMPDD.ldt PROGRAM APPLICATION_SHORT_NAME="AMW" CONCURRENT_PROGRAM_NAME="EMP_DD"

FNDLOAD apps/<<password>> O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct EMPDD.ldt

Data definition and template:

FNDLOAD apps/apps10 O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_MY_DATA_DEF_NAME.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME="AMW" DATA_SOURCE_CODE="EMP_DD"

FNDLOAD apps/<<password>> 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXEY_EMP_DATA_DEF.ldt

TCA

Overview:

Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. This document provides information about how to create a customer using TCA API. These APIs utilize the new TCA model, inserting directly to the HZ tables.

Architecture


Create Organization

DECLARE

p_organization_rec   hz_party_v2pub.organization_rec_type;

x_return_status      VARCHAR2 (2000);

x_msg_count          NUMBER;

x_msg_data           VARCHAR2 (2000);

x_party_id           NUMBER;

x_party_number       VARCHAR2 (2000);

x_profile_id         NUMBER;

BEGIN

p_organization_rec.organization_name := ’erpschools’;

p_organization_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_v2pub.create_organization (‘T’,

p_organization_rec,

x_return_status,

x_msg_count,

x_msg_data,

x_party_id,

x_party_number,

x_profile_id

);

DBMS_OUTPUT.put_line (‘party id ‘ || x_party_id);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_parties table and one record in hz_organization_profiles table. Similarly you can call hz_party_v2pub.create_person to create a record in the HZ_PARTIES and one record in HZ_PERSON_PROFILES tables.

Create a Location

DECLARE

p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;

x_location_id NUMBER;

x_return_status VARCHAR2(2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

BEGIN

p_location_rec.country := ’US’;

p_location_rec.address1 := ’2500 W Higgins Rd’;

p_location_rec.address2 := ’Suite 920′;

p_location_rec.city := ’Thumuluru’;

p_location_rec.postal_code := ’60118′;

p_location_rec.state := ’IL’;

p_location_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_location_v2pub.create_location(

‘T’,

p_location_rec,

x_location_id,

x_return_status,

x_msg_count,

x_msg_data);

dbms_output.put_line(‘location id ‘||x_location_id);

dbms_output.put_line(SubStr(‘x_return_status = ‘||x_return_status,1,255));

dbms_output.put_line(‘x_msg_count = ‘||TO_CHAR(x_msg_count));

dbms_output.put_line(SubStr(‘x_msg_data = ‘||x_msg_data,1,255));

IF x_msg_count >1 THEN

FOR I IN 1..x_msg_count

LOOP

dbms_output.put_line(I||’. ‘||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));

END LOOP;

END IF;

END

Note: The above API shall create an address record in hz_locations table.

Create a Party Site:

Use the organization_id and location_id created above and create a party site.

DECLARE

p_party_site_rec      hz_party_site_v2pub.party_site_rec_type;

x_party_site_id       NUMBER;

x_party_site_number   VARCHAR2 (2000);

x_return_status       VARCHAR2 (2000);

x_msg_count           NUMBER;

x_msg_data            VARCHAR2 (2000);

BEGIN

p_party_site_rec.party_id := 1272023;

p_party_site_rec.location_id := 359086;

p_party_site_rec.identifying_address_flag := ’Y';

p_party_site_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_site_v2pub.create_party_site (‘T’,

p_party_site_rec,

x_party_site_id,

x_party_site_number,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (‘party site id ‘ || x_party_site_id);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_party_sites table.

Create Party Site Use

Use the above party site created

DECLARE

p_party_site_use_rec   hz_party_site_v2pub.party_site_use_rec_type;

x_party_site_use_id    NUMBER;

x_return_status        VARCHAR2 (2000);

x_msg_count            NUMBER;

x_msg_data             VARCHAR2 (2000);

BEGIN

p_party_site_use_rec.site_use_type := ’SHIP_TO’;

p_party_site_use_rec.party_site_id := 349327;

p_party_site_use_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_site_v2pub.create_party_site_use (‘T’,

p_party_site_use_rec,

x_party_site_use_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create a Contact Point

DECLARE

p_contact_point_rec   hz_contact_point_v2pub.contact_point_rec_type;

p_edi_rec             hz_contact_point_v2pub.edi_rec_type;

p_email_rec           hz_contact_point_v2pub.email_rec_type;

p_phone_rec           hz_contact_point_v2pub.phone_rec_type;

p_telex_rec           hz_contact_point_v2pub.telex_rec_type;

p_web_rec             hz_contact_point_v2pub.web_rec_type;

x_return_status       VARCHAR2 (2000);

x_msg_count           NUMBER;

x_msg_data            VARCHAR2 (2000);

x_contact_point_id    NUMBER;

BEGIN

p_contact_point_rec.contact_point_type := ’PHONE’;

p_contact_point_rec.owner_table_name := ’HZ_PARTIES’;

p_contact_point_rec.owner_table_id := ’1272023′;

p_contact_point_rec.primary_flag := ’Y';

p_contact_point_rec.contact_point_purpose := ’BUSINESS’;

p_phone_rec.phone_area_code := ’650′;

p_phone_rec.phone_country_code := ’1′;

p_phone_rec.phone_number := ’506-7000′;

p_phone_rec.phone_line_type := ’GEN’;

p_contact_point_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_contact_point_v2pub.create_contact_point (‘T’,

p_contact_point_rec,

p_edi_rec,

p_email_rec,

p_phone_rec,

p_telex_rec,

p_web_rec,

x_contact_point_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create an Org Contact:

DECLARE

p_org_contact_rec   hz_party_contact_v2pub.org_contact_rec_type;

x_org_contact_id    NUMBER;

x_party_rel_id      NUMBER;

x_party_id          NUMBER;

x_party_number      VARCHAR2 (2000);

x_return_status     VARCHAR2 (2000);

x_msg_count         NUMBER;

x_msg_data          VARCHAR2 (2000);

BEGIN

p_org_contact_rec.department_code := ’ACCOUNTING’;

p_org_contact_rec.job_title := ’ACCOUNTS OFFICER’;

p_org_contact_rec.decision_maker_flag := ’Y';

p_org_contact_rec.job_title_code := ’APC’;

p_org_contact_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

p_org_contact_rec.party_rel_rec.subject_id := 16077;

p_org_contact_rec.party_rel_rec.subject_type := ’PERSON’;

p_org_contact_rec.party_rel_rec.subject_table_name := ’HZ_PARTIES’;

p_org_contact_rec.party_rel_rec.object_id := 1272023;

p_org_contact_rec.party_rel_rec.object_type := ’ORGANIZATION’;

p_org_contact_rec.party_rel_rec.object_table_name := ’HZ_PARTIES’;

p_org_contact_rec.party_rel_rec.relationship_code := ’CONTACT_OF’;

p_org_contact_rec.party_rel_rec.relationship_type := ’CONTACT’;

p_org_contact_rec.party_rel_rec.start_date := SYSDATE;

hz_party_contact_v2pub.create_org_contact (‘T’,

p_org_contact_rec,

x_org_contact_id,

x_party_rel_id,

x_party_id,

x_party_number,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_org_contacts table and one record in hz_relationships table. When a contact is created, a record in hz_parties table gets created with party_type as ‘PARTY_RELATIONSHIP’.

Create a Customer Account:

DECLARE

p_cust_account_rec       hz_cust_account_v2pub.cust_account_rec_type;

p_person_rec             hz_party_v2pub.person_rec_type;

p_customer_profile_rec   hz_customer_profile_v2pub.customer_profilerec_type;

x_cust_account_id        NUMBER;

x_account_number         VARCHAR2 (2000);

x_party_id               NUMBER;

x_party_number           VARCHAR2 (2000);

x_profile_id             NUMBER;

x_return_status          VARCHAR2 (2000);

x_msg_count              NUMBER;

x_msg_data               VARCHAR2 (2000);

BEGIN

p_cust_account_rec.account_name := ’John”s A/c’;

p_cust_account_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

p_person_rec.person_first_name := ’John’;

p_person_rec.person_last_name := ’Smith’;

hz_cust_account_v2pub.create_cust_account (‘T’,

p_cust_account_rec,

p_person_rec,

p_customer_profile_rec,

‘F’,

x_cust_account_id,

x_account_number,

x_party_id,

x_party_number,

x_profile_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note:

This routine is used to create a Customer Account. The API creates a record in the HZ_CUST_ACCOUNTS table for party type Person or Organization. Account can be created for an existing party by passing party_id of the party. Alternatively, this routine creates a new party and an account for the party.

Customer profile record in the HZ_CUSTOMER_PROFILES can also be created while calling this routine based on value passed in p_customer_profile_rec. The routine is overloaded for Person and Organization.

Create a Customer Account Site

Use an existing Party Site

DECLARE

p_cust_acct_site_rec   hz_cust_account_site_v2pub.cust_acct_site_rec_type;

x_return_status        VARCHAR2 (2000);

x_msg_count            NUMBER;

x_msg_data             VARCHAR2 (2000);

x_cust_acct_site_id    NUMBER;

BEGIN

p_cust_acct_site_rec.cust_account_id := 3472;

p_cust_acct_site_rec.party_site_id := 1024;

p_cust_acct_site_rec.LANGUAGE := ’US’;

p_cust_acct_site_rec.created_by_module := ’TCA-EXAMPLE’;

hz_cust_account_site_v2pub.create_cust_acct_site (‘T’,

p_cust_acct_site_rec,

x_cust_acct_site_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create Customer Account Site Use Code:

DECLARE

p_cust_site_use_rec      hz_cust_account_site_v2pub.cust_site_use_rec_type;

p_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;

x_site_use_id            NUMBER;

x_return_status          VARCHAR2 (2000);

x_msg_count              NUMBER;

x_msg_data               VARCHAR2 (2000);

BEGIN

p_cust_site_use_rec.cust_acct_site_id := 3580;

p_cust_site_use_rec.site_use_code := ’INV’;

p_cust_site_use_rec.LOCATION := ’TCA’;

p_cust_site_use_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_cust_account_site_v2pub.create_cust_site_use (‘T’,

p_cust_site_use_rec,

p_customer_profile_rec,

”,

”,

x_site_use_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

More Customer API’s:

Org Contact Role	Hz_party_contact_v2pub.Create_Org_Contact_Role
Relationships	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer Profile	HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile Amount	HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit Rating	HZ_PARTY_INFO_V2PUB.create_credit_rating
Sales Person	JTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps Territories	JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contacts	HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact Role	HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility

FA Transfer to GL

Fixed Asset Transfer to GL

Posted on December 1st, 2008 by Sanjit Anand | Print This Post | Email This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today

How Fixed Assets (FA) Passes Journals To General Ledger (GL)
Before 11.5.9 and Fixed Assets patchset M (11i.FA.M), Journals created by the Create Journals process in FA were entered directly to the journal tables. For some time it was possible to have different period names between the two ledgers and change them using the General Ledger Change Periods feature.
Financials Family pack G or higher all journals to be passed from FA through the gl_interface table using the standard import mechanism (GLLEZL). Initially the import had to be launched from GL separately.Check out old post for R12 SLA Changes.
How to generate FA journal entries and transfer to GL?
In FA module, the journals should be generated after asset addition, depreciation,revaluation, adjustment, transfer, and retirement. It is suggested that in end of each month, after run depreciation and close FA module, user should generate journal entries and transfer to GL module.
1. Login to system and switch to FA responsibility, select Other -> Request -> Run, select “Single Request”, click “OK” button.
2. In “Submit Request” form, input “Create Journal Entries” in name field.
3.Input or select the book and period that you want to generate journals entries
How and what FA Populates into GL
These are the columns which are used for capturing the information from FA to GL.
Convention :GL_INTERFACE table column (GL_LINES table COLUMN) :FA REFERENCE

• REFERENCE21(REFERENCE_1):TRANSACTION_HEADER_ID
• REFERENCE22(REFERENCE_2):ASSET_ID
• REFERENCE23(REFERENCE_3):DISTRIBUTION_ID
• REFERENCE24(REFERENCE_4):ADJUSTMENT_LINE_ID
• REFERENCE25(REFERENCE_5):BOOK_TYPE_CODE
• REFERENCE26(REFERENCE_6):PERIOD_COUNTER
• REFERENCE27(REFERENCE_7):FA_TRANSFER_TO_GL
• REFERENCE28(REFERENCE_8):ADJUSTMENT_TYPE / LOOKUP_CODE
• REFERENCE29(REFERENCE_9):CJE_ID

Journal categories used while FA to GL Transfer
These are the transaction type used for JE creation in GL.

• Addition
• Reinstatement
• Full Retirement
• Transfer
• Partial Retirement
• Adjustment
• Unit Adjustment
• Reclass
• Depreciation
• Revaluation

Query for Subledger Transfer to GL
If you want to get details of different journals transferred to GL, use this to get the result. You can also fine tune with period , currency or clearing company code or Journal Type.

Here is the query:

 
SELECT gjjlv.period_name period_name
, gjb.name batch_name
, gjjlv.header_name Journal_Entry
, gjjlv.je_source Source
, gjjlv.line_entered_dr Entered_Debit
, gjjlv.line_entered_cr Entered_credit
, gjjlv.line_accounted_dr Accounted_Debit
, gjjlv.line_accounted_cr Accounted_Credit
, gjjlv.currency_code Currency
, fasv.TRX_TYPE_NAME Trans_Type
, fasv.TRX_NUMBER_DISPLAYED Transaction_Number
, fasv.TRX_DATE Transaction_Date
, fasv.ASSET_NUMBER Reference
, glcc.CONCATENATED_SEGMENTS
,gjjlv.created_by
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, apps.fa_ael_gl_v fasv
, gl_je_headers gjh
, gl_je_batches gjb
, apps.gl_code_combinations_kfv glcc
WHERE gjh.period_name BETWEEN 'SEP-2008' AND 'OCT-2008'
AND glcc.code_combination_id = gje.code_combination_id
AND glcc.code_combination_id = fasv.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.je_header_id = fasv.je_header_id
AND glcc.segment1='22'
 

Verification Report
You can use these Verification Report for your FA and GL monthly Reconcilation.

1. Cost Detail Report :Use the Cost Detail and Cost Summary reports to reconcile your asset cost accounts to your general ledger to reconcile with Oracle General Ledger, compare the Cost Summary report with the Account Analysis Report.
2. Asset Retirements Report :Use this report to review the assets you retired for the Book and accounting Period range you choose. The report is sorted by balancing segment, asset type, asset
   account, cost center, and asset number. It prints totals for each cost center, account,asset type, and balancing segment.
3. Asset Reclassification Reconciliation Report
4. Asset Transfer Reconciliation Report :Use this report to review asset transfers for the Book and Period you choose. For each transaction Oracle Assets lists the expense account, balancing segment, cost center,and location of the asset before and after the transfer. Oracle Assets sorts the report by
   asset number.
5. Journal Entry Reserve Ledger Report : This report can be used to review how much depreciation Oracle Assets charged to a depreciation reserve account in an accounting period. The report is sorted by, and prints totals for each balancing segment, asset account, reserve account, and cost center.
6. CIP Capitalization Report
7. CIP Assets Report
8. Unposted Mass Additions Report
9. CIP Detail Report (If using adding asset through Project)
10. Asset Addition Report
11. Cost Adjustment Report

Important FAQ - that user may ask
1. Are journal entries transferred to the GL in summary or detail format?
Journal entries are summarized to code combination ids within journal categories.
2. Why do I have journal entries for zero dollar amounts (i.e., debit 0, credit 0)?
The expense segment is part of a single distribution row, which also contains the assets owner and location. Changes to any one of these items will result in the creation of a new distribution. If a change is made in the location or owner, journal entries are posted to reflect the new distribution, even though there has been no accounting impact (thus the zero dollar accounting entries).
3. How often can I create journal entries?
You can run the Create Journal Entries program once per accounting period after you run the depreciation program.
4. What happens if I forget to create journal entries for an accounting period? For example, I created journal entries for AUG-08 and OCT-08, but forgot to create journal entries for SEP-08).
Oracle Assets lets you create journal entries for accounting periods in any order as long as you have ran depreciation for the period and the period is open in your general ledger.
5. How do I reconcile Oracle Assets with my general ledger?
Oracle Assets has a set of reports you use to reconcile asset cost, depreciation expense, and depreciation reserve accounts to your general ledger.
Similar Post

• Fixed Assets:Journal Entries
• Fixed Asset & SLA

PRASANTH'S ORACLE APPLICATIONS BLOG: Interface Tables of GL, AP, AR,FA,INV,PO,BOMS,WIP,...

PRASANTH'S ORACLE APPLICATIONS BLOG: Interface Tables of GL, AP, AR,FA,INV,PO,BOMS,WIP,...: Interface Tables of GL, AP, AR,FA,INV,PO,BOMS,WIP,OM GL INTERFACE TABLES GL_BUDGET_INTERFACE GL_DAILY_RATES_INTERFACE GL_IEA_INTERFAC...