, , ,

How to Get GL Code Combination Description

Script 1:(Works in Both 11i & R12)

SELECT gcc.segment1
||’.’
||gcc.segment2
||’.’
||gcc.segment3 ACCOUNT,
A1.DESCRIPTION
||’-‘
||A2.DESCRIPTION
||’-‘
|| A3.DESCRIPTION DECS
FROM fnd_flex_values_vl A1,
fnd_flex_values_vl A2,
fnd_flex_values_vl A3,
gl_code_combinations gcc
WHERE a1.flex_value       =gcc.segment1
AND a1.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id       = 101
AND id_flex_code           = ‘GL#’
AND enabled_flag           = ‘Y’
AND application_column_name=’SEGMENT1′
)
AND a2.flex_value         =gcc.segment2
AND a2.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id       = 101
AND id_flex_code           = ‘GL#’
AND enabled_flag           = ‘Y’
AND application_column_name=’SEGMENT2′
)
AND a3.flex_value         =gcc.segment3
AND a3.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id       = 101
AND id_flex_code           = ‘GL#’
AND enabled_flag           = ‘Y’
AND application_column_name=’SEGMENT3′

);

Script 2: ( Applicable in R12)

SELECT gcc.CONCATENATED_SEGMENTS,
gl_flexfields_pkg.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description

FROM gl_code_combinations_kfv gcc;

/by
, , ,

R12 – GL-Receiving India Drill Down Query

Here is the query for Link between General Ledger and Receiving India data’s.

SELECT msib.segment1 item,
msib.description,
rsh.receipt_num,
mp.organization_code,
gjl.accounted_dr,
gjl.accounted_cr,
gjh.je_source,
gjh.je_category
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations_kfv gcc,
apps.gl_import_references gir,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.gl_je_batches gjb,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_header_id   = gir.je_header_id
AND gir.je_header_id   = gjl.je_header_id
AND gir.je_line_num    = gjl.je_line_num
AND gjb.je_batch_id    = gir.je_batch_id
AND GJH.JE_SOURCE      = ‘Purchasing India’
AND gjh.period_name    =’Nov-14′
AND gjl.reference_2 = ‘India Localization Entry’
AND gjl.code_combination_id = gcc.code_combination_id
AND rt.organization_id      = mp.organization_id
AND rt.transaction_id       = gjl.reference_5
AND rt.shipment_header_id   = rsh.shipment_header_id
AND rsl.shipment_header_id  = rsh.shipment_header_id
AND rsl.shipment_line_id    = rt.shipment_line_id
AND msib.inventory_item_id  = rsl.item_id
AND msib.organization_id    = rt.organization_id
AND gjl.period_name        IN
(SELECT DISTINCT period_name
FROM APPS.GL_PERIODS
WHERE START_DATE BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD
);

Note: This one was not linked with India Localization SLA Tables, its a direct link between GL and Localization Info’s

/by
, , , , , , , , , , , , ,

R12 – Period End Closing Checklist in Oracle Apps

Product family
Product Code
Step No
Step name






1
Order Management (OM):




Order Management
ONT
1.1
Complete the sales orders
Order Management
ONT
1.2
Import Sales Order
Order Management
ONT
1.3
Pre-Billing Acceptance
Shipping
WSH
1.4
Complete the shipping transactions
Order Management
ONT
1.5
Prepare the link with AR




Order Management
ONT
1.5.1
Check run of Workflow background engine
Receivables
AR
1.5.2
Run the Invoice Interface/Autoinvoice
Receivables
AR
1.5.3
Verify the Autoinvoice Exceptions






2
Receivables (AR):




Receivables
AR
2.1
Complete all Receivables transactions for the period being closed
Subledger Accounting
SLA
2.2
Create accounting
Subledger Accounting
SLA
2.3
Transfer to GL
Receivables
AR
2.4
Manage the Receivables Period






3
Purchasing (PO):




Purchasing
PO
3.1
Complete all transactions for Oracle Purchasing
Subledger Accounting
SLA
3.2
Create accounting for Payables
Subledger Accounting
SLA
3.3
Transfer to GL
Purchasing/Payables
PO/AP
3.4
Purchasing Period management




Payables
AP
3.4.1
Close the Payables period
Purchasing
PO
3.4.2
Run the Receipt Accruals – Period-End Report
Purchasing
PO
3.4.3
Manage the PO Periods
Payables
AP
3.4.4
Open next AP Period






4
Transactions control:






4.1
Enter all transactions in eBS:




Inventory
INV
4.1.1
Enter the pending cycle counts
Inventory
INV
4.1.2
Check run of the Material Transaction Manager
Inventory
INV
4.1.3
Check run of the Move Transaction Manager 
Inventory
INV
4.1.4
Check run of the Lot Move Transaction Manager    
Inventory
INV
4.1.5
Check run of the Cost manager (mandatory)




Inventory
INV
4.2
Check the pending or in error transactions
Inventory
INV
4.3
Unprocessed Material (resolution required)
Inventory
INV
4.4
Uncosted material transactions (resolution required)
Costing
CST
4.5
Pending LCM Interface Transactions (resolution required)
Work In Process
WIP
4.6
Pending WIP Costing transactions (resolution required)
Shop Floor Management
WSM
4.7
Pending WSM interface (resolution required)






4.7.1
Run the Import WIP Lot Transactions


4.7.2
Run the (WSM) Pending Transactions Report




Shipping
WSH
4.8
Unprocessed Shipping Transactions (by default, resolution required)




Shipping
WSH
4.8.1
Search delivery line with the status Shipped
Shipping
WSH
4.8.2
Run the Interface Trip Stop SRS




Inventory
INV
4.9
Pending material transactions for this period (resolution recommended)
Work In process
WIP
4.10
Pending Shop Floor Move (resolution recommended)
Purchasing
PO
4.11
Pending Receiving Transactions (resolution recommended)
Project Manufacturing
PJM
4.12
Project Manudacturing – Cost Collector




Project Manufacturing
PJM
4.12.1
Run the Cost Collection Manager
Project Manufacturing
PJM
4.12.2
Import the transactions in Projects






5
Cost Management:




Costing
CST
5.1
Cost Of Goods Sold




Costing
CST
5.1.1
Record the Order Management Transactions
Costing
CST
5.1.2
Collect the Revenue Recognition information
Costing
CST
5.1.3
Generate the COGS Recognition events




Costing
CST
5.2
Create accounting
Costing
CST
5.3
Transfer to GL






6
 Inventory Open / Close period:




Inventory
INV
6.1
Open the next period
Inventory
INV
6.2
Close the period
Inventory
INV
6.3
Open or Close several periods in the same time




Inventory
INV
6.3.1
Hierarchy of Organizations
Costing
CST
6.3.2
Open several periods
Costing
CST
6.3.3
Close several periods
/by
, ,

R12 – Mass Allocation in GL

The basic concept of Mass Allocation is dividing a cost on some factors.

If we take a simple example then consider 3 departments X, Y and Z using a single landline telephone. Each department has 2, 3 and 6 employees respectively. The bill for month of June-09 of landlines comes out to be Rs.1700. If you have to calculate the telephone usage for department Y. How will you do that? Is it Rs.1700? No. Is it 1700/3 (total divided by total number of employees in dept-Y)? No, because there were 8 more person using the same landline connection. The answer is that you will allocate the total cost into each department based on the number of employee it has. So in this case the allocation will be 1700*3/11 and you’ll get Rs.463.

Here is the department wise total of the telephone bill:
Department X: Rs.309.09 (2 employees usage of 1700)
Department Y: Rs.463.63 (3 employees usage of 1700)
Department Z: Rs.927.27 (6 employees usage of 1700)
This is the formula for MassAllocation A*B/C where,
A = Total Cost
B = Factor (Number of Employee of particular department)
C = Total Factor (Total Employees)
So the Allocation formula for Department Y will be
A = 1700 (Total Landline Bill for a particular month)
B = 3 (Total Number of Employees in Department Y)
C = 11 (Total Number of Employee in all three departments)
In Oracle General Ledger this facility is given to divide or allocate your expenses or revenue income on your selected distributing criteria which can be your number of departments, branches, head count, covered area, etc. In my above example I used Head Count as the basis of allocation.

Steps for generating a Mass Allocation Journal:
  1. Pass a Standard Journal – This will identify your “A”
  2. Pass a STAT Journal – This will identify your “B” and “C”
  3. Define Mass Allocation Formula Batch and Journal.
  4. Validate the Formula.
  5. Generate the formula for specific accounting period.
Let’s see how we can carry out the process of Mass Allocation in General Ledger.
First let me take a scenario. Consider an organization with 4 divisions or departments:
1. Enterprise Resource Planning (ERP)
2. Software Development (SD)
3. Software Support (SS)
4. Network Infrastructure (NI)
The COA Structure of this organization is Company-Branch-Department-Product-Account
The segment values of Department or the hierarchy of Department segment is
0000 – Common or No Department
1200- Information Technology (Parent) (Child Ranges: 1201 – 1299)
1201-ERP
1202-Software Development
1203-Software Support
1204-Network Infrastructure
Let’s allocate the telephone bill expense of Rs. 18950 for the month of June incurred at Karachi branch on the number of employee each department has. The allocation basis in this example is Head Count per Department.
The account code for Karachi branch is 101 and the natural Account for PTCL expense is 50201 and each department has 9, 11, 5, and 3 employees respectively. That is ERP has a head count of 9, SD has 11, SS has 5 and NI has a head count of 3.

Now the Mass Allocation procedure steps starts.

STEP1: We will create a total cost or “A” of the formula. Pass a Standard JV in the period of JUNE with the following Lines
Line1: 1-101-0000-00-50201 18950(DR)
Line2: 1-101-0000-00-10122 18950(CR)
Line 1 Account Description: XYZ-Karachi-NoDeparment-NoProduct-PTCL Expense
Line 2 Account Description: XYZ-Karachi-NoDepartment-NoProduct-Bank
This journal entry is equivalent to paying your PTCL telephone bill. Ideally this expense entry should be coming from Oracle Payables. We are manually entering this actual journal so that we can created a Cost Pool “A” having an amount of Rs.18950.

STEP2: Now we will create the “B” and “C” or Usage Factor and “Total Usage”. Pass a STAT JV. STAT is short for Statisticaland it can be used by changing the currency from PKR to STAT. The STAT journal doesn’t need to be balanced. But they do affect the account balances if we inquire on the currency type of TOTAL but let’s not get there, it is a different topic. Simply pass a STAT JV to create “B” and “C”. Remember the Period of the JV should be JUNE as the Standard JV.
The account code combination for the STAT journals in this scenario will be
Line1: 1-101-1201-00-50201 9(DR)
Account Description: XYZ-Karachi-ERP-NoProduct-PTCL Expense
Line2: 1-101-1202-00-50201 11(DR)
Line3: 1-101-1203-00-50201 5(DR)
Line4: 1-101-1204-00-50201 3(DR)

By passing or posting this STAT journal we are creating a basis for expense allocation. The line 1 tells that the XYZorganization has 9 employees at Karachi branch in ERP department incurring PTCL Expense. We can enable UOM on STAT journal by enabling the profile option JOURNAL:MIX STATISTICAL AND MONETARY to YES. Similarly so on and so forth. Now where are “B” and “C” in this journal? You can see 4 lines with changing Department codes, these four lines individually represent Usage Factor “B” which is 9, 11, 5 & 3 and collectively they represent Total Usage “C” which is equal to 9+11+5+3=28.

Now moving on with STEP3

Create a MassAllocation Batch and then a Journal. Name it Karachi PTCL Expense Formula.
When you open the formula entry form you will find the three constant of the Mass Allocation formula A, B, C and two other fields T and O. “T” stands for Target Account and “O” stands for Offset Account. I will explain these Accounts later. Let’s continue with the formula.
Now give the account of the “A” which is 1-101-0000-00-50201 having the value of Rs.18950. On the account entry form you will find that the system prompts or asks for Ledger, it is an optional field. This option of ledger set is used when we are allocating cost from multiple ledgers. And there is another LOV having the value as
C: Constant – The segment is constant and doesn’t need any Loop or Sum. And the balance should be picked against “A” as a constant
L: Looping – The segment needs to loop from first value to last value provided in STAT JV.
S: Summing – The segment needs to sum the value in provided in STAT JV.
Generally the account code in “A” doesn’t not need any kind of looping or summing. So every segment should be given the value of C. The value this account has for the particular period should be picked as a constant. Keep the currency as Entered.
Now move on to enter the code for “B”. The account code for Usage Factor in our example will be
1-101-1200-00-50201. Note that I have given the department code as 1200 which is parent of the departments we selected for allocation basis. Give every segment a Constant C but the segment of Department will be having the value as Looping L. Why? Because we need to pick the individual values of 9, 11, 5 and 3.

REMEMBER: looping is only done on Parent Value of the Segment. In this example 1200 is the Parent department which has the child departments 1201, 1202, 1203 and 1204.
The system will automatically pick the allocation basis by matching the natural account and the looping segment.

REMEMBER: The currency for “B” and “C” should be STAT.
Now give the account code for the Total Usage “C”. The account code will remain the same as “B” with 1200 as the department code. The only difference this time is that instead of Looping we will give the Department segment the value of Summing S. so that we can have the sum of head count which is 28.
It’s time to give the “T” account. No, it’s not the T Account as we see in Ledger. It is the Target Account of the cost pool or these are the Debit Accounts which should hold the allocated expense. In our example these account are the accounts we gave in “B”. Yes the account code combination 1-101-1200-00-50201 with 1200 as Looping. IN FACT, usually the accounts given in “B” are repeated in “T” and account given in “A” is repeated in “O”
Let’s proceed further by entering the “O” or the Offset account. This account is same as the account we gave in “A”. This is the credit account. The account code combination given here will 1-101-0000-00-50201 with every segment as Constant.
With this step we have completed our allocation formula. The final Journal generated with this formula should be
Line 1
1-101-1201-00-50201
6091.071
Line2
1-101-1202-00-50201
7444.643
Line3
1-101-1203-00-50201
3383.929
Line4
1-101-1204-00-50201
2030.357
Line5
1-101-0000-00-50201
18950

If you enable the Full Cost Pool Allocation option then the system will post the rounding difference to the account with highest value. In this case the all the rounding will be given to line2 account. The first four accounts are the accounts we mentioned in Target field and the last account is the one we mentioned in Offset field. The accounting done here is that the PTCL Expense posted on a Common department was credited and distributed to four other departments on the basis we defined in STAT journal in Step 2.

If the concurrent request ends with an error then check the Output and Log file for error details.
/by
, , ,

Basics of Accounting

This is how the accounting works. Everything pertains to what an Organization owns, have and what it has to give. There is always a balance to what it owns and what it has to give.
This “balance” is converted into an equation, also called the Accounting Equation, which is:
ASSETS = LIABILITIES + OWNER’S EQUITY
though I’ve understood it this way:
OWNER’S EQUITY =  ASSETS – LIABILITIES
Let’s take a simple example to justify the above equation, say you have Rs.1,000 but you know that you have to pay a loan of Rs.400 that you borrowed from your friend.
So according to the equation Rs.1000 is your Asset, Rs.400 loan is your Liability and Rs.600 is the Equity that you own.
Every organization which is registered with Government is obliged to disclose the above mentioned balance in a document called Balance Sheet.
That’s all for the accounting equation.
Moving on after the accounting equation,
There is a
  • Debit (Always on the Left Side, written as “DR” for shorthand) and
  • Credit (Always on the Right Side, written as “CR” for shorthand)
  • Debit Side should always be equal to Credit Side or
  • Left Side should always be equal to Right Side or
  • DR = CR
With the Debit and Credit comes in the
  • Increase in balance or
  • Decrease in balance
There are 5 natures of account. Every account can have any one nature and that’s why we can also call it natural account. These natures are:
  1. Assets
  2. Liabilities
  3. Revenue
  4. Expenses
  5. Owner’s Equity
ASSET: Literally asset is any thing which is valuable to a person, organization or any entity. For example we say that “his quick learning ability is an asset to him” or “Her writing ability is her asset”. Why do we say that? Because quick learning skill or writing ability adds value to a person. A writer sells his writing skills to earn money, similarly in terms of business anything which is valuable to a business is the asset.
Say your organization is a pharmaceutical and manufactures Medicines, then all the chemicals used to manufacture medicine is your asset or in other words the Raw Material is your asset. The cash your organization own is an asset because it can be used to buy items or pay your employee who in turn are used to run your business. There are different types of assets, the broader categories of asset are Current Asset and Fixed, but let’s not discuss it here. For now it is enough to know that asset is anything which is valuable to your organization.
Asset INCREASES when it is Debited and DECREASES when Credited.
Any organization which is registered with the government and exists as Legal Entity is obligated to disclose its Assets on the balance sheet to the government and its Creditors. You might ask Who are creditors and Why is it that an organization is obligated to disclose asset to them? With Creditor comes in the liability.
LIABILITY: Comes from the word “Liable”. Literal meaning of Liable is “to be obligated” , “to be responsible” or “Legally responsible”. In terms of accounting you become liable, responsible to pay when you buy or purchase any thing from another entity. You are liable to compensate whatever you’ve bought. Generally an organization records its liability and pays it afterward. Again, there are different types of liabilities like Short Term Liability and Long Term Liability.
Liability INCREASES when it is Credited and DECREASES when Debited.
OWNER’S EQUITY: This is the share of owner in the business.
Equity INCREASES when it is Credited and DECREASES when Debited.
REVENUE: By definition it is the total gain before inducting any expense. It is mostly associated with the Asset. When any organization sell goods or renders its services, it records an increase in Asset and with this increase comes the gain it has made from selling the goods or services. This gain is called Revenue or Income.
Revenue INCREASES when it is Credited and DECREASES when Debited.
Revenue are not displayed in Balance Sheet. They are reflected in Owner’s Equity.
EXPENSE: By definition any payment made is an expense. How payments are made? Either by Cash or Credit which eventually means Cash. So redefining Expense “The outflow of cash to any person or organization for its supplied Goods or rendered Services”. We incur expenses daily, for example, taxi fare is an expense, dine-out payments are expenses. Expenses are associated with Liability. Whenever an organization books a liability, it is mostly against some expense. There are different type of expense
Expense INCREASES when it is Debited and DECREASES when Credited.
Following table shows the Tabular form of the effect
Nature DEBIT CREDIT
Asset Increase (+) Decrease (-)
Liability Decrease (-) Increase (+)
Equity Decrease (-) Increase (+)
Revenue Decrease (-) Increase (+)
Expense Increase (+) Decrease (-)
In Oracle General Ledger, when we attach the “Natural Account” Flexfield Qualifier to a segment. System attaches the 5 nature on the Value form. When we add the Natural Account Value, we have to define the nature of the account as well.
When we define the natures of the account, the accounting rules of Debit and Credit works accordingly. Like in Payables, the line item is Debit side, so if you’ll give an expense or asset account, it will increase and vice versa.
It is necessary to understand the application accounting behavior in order to properly suggest and implement the accounting solution in an organization.
/by