Showing posts with label Technical Architecture Content. Show all posts
Showing posts with label Technical Architecture Content. Show all posts

Friday, January 30, 2015

How to Define Segment Values in Accounting Flex field in Release 12.2

How to Define Segment Values in Accounting Flex field in Release 12.2 

Not able to Search Value sets in R12.2

In Release 12.2 Value set Insert/Update depends on Security access. To Insert/Update the Accounting Flex filed segment values for a specific user, User need to follow below steps
By Default in R12.2 a user will not be able to see any values in the value set / Key Flexfield values Form and will receive an error
FRM-40212: Invalid value for field FLEX_VALUE_SET-NAME




To Insert/Update the Accounting Flex filed segment values for a specific user, User need to be provided with appropriate Grants.
Please follow below steps toad Grants for User to Insert and update Value Sets.

Responsibility : Functional Administrator
Go to Navigation : Security à Grants à  Create Grant

1.Name            :Give Name as Required
2.Grantee Type :Select as Specific User
3.Grantee         :Select the Required User Name from List of Values
4.Object           :Select Object as ''Flexfield Value Set Security Object(1)'' from List of Values
5.Set               :Select the Set as ''Flexfield Value Set Security Insert/Update Set'' from List of values .
6. Click on ''Apply'' Button


Add the Object Data Context : All Rows



Select the Set as ''Flexfield Value Set Security Insert/Update Set'' from List of values






After creating the grants from functional administrator, we will be able to perform the following in Value set and Key Flex field Values form:

i) Access the values in Name field LOV, when we Find Values By Value Set.
ii) Access the values in Application,Title,Structure & Segment field LOV's, when we Find Values By Key 
Flexfield
iii) Access the values in Application,Title,Structure & Segment field LOV's, when we Find Values By 
Descriptive Flexfield
iv) Access the values in Application, Name & Parameter LOV's, when we Find Values By Concurrent Program.


Saturday, May 4, 2013

General Ledger - Sub ledger - Project Accounting Drill Down

General Ledger - Sub ledger - Project Accounting Drill Down

Well This Is Definitely one of the Complicated One!

Not very Often though but you have a Project which involves Project accounting, and you are working on Reports / or Customization which need an integration form Oracle Projects, General Ledger and Sub-ledger Accounting, you need a very good understanding of how various Database tables of these Different Modules Link with each other.

B Below is a query which can be leveraged to Drill Down or Drill up for the data between

GENERAL LEDGER
SUB-LEDGER ACCOUNTING
PROJECT ACCOUNTING


select
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAL.ACCOUNTING_CLASS_CODE,PA.PROJECT_ID,PA.SEGMENT1,PA.NAME,PA.PROJECT_TYPE,PT.TASK_ID,PT.TASK_NUMBER,PT.TASK_NAME,PCD.EXPENDITURE_ITEM_ID,
PE.EXPENDITURE_ITEM_DATE ,
PE.TRANSACTION_SOURCE,
PE.EXPENDITURE_ITEM_DATE ,PE.EXPENDITURE_ITEM_DATE
,888
,PCD.*
--,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*,8,PCD.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL,
APPS.PA_COST_DISTRIBUTION_LINES_ALL PCD,
APPS.PA_PROJECTS_ALL PA,
APPS.PA_TASKS PT,
APPS.PA_EXPENDITURE_ITEMS_ALL PE,
APPS.PA_TRANSACTION_SOURCES PTS
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PCD.EXPENDITURE_ITEM_ID
AND PCD.EXPENDITURE_ITEM_ID = PE.EXPENDITURE_ITEM_ID
AND PE.TRANSACTION_SOURCE = PTS.TRANSACTION_SOURCE
--AND PE.TRANSACTION_SOURCE LIKE 'PA%'
AND PCD.PROJECT_ID = PA.PROJECT_ID
AND PCD.TASK_ID = PT.TASK_ID
AND PA.PROJECT_ID = PT.PROJECT_ID
AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-25)
--AND UPPER(GJH.JE_CATEGORY) LIKE UPPER('PROJECT%')
AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Project Accounting%')

Thursday, April 4, 2013

General Ledger - Accounts Payables Drill Down - Data Base

General Ledger - Accounts Payables Drill Down - Data Base Query


As Part of Regular GL , Accounts Payables Designing and Troubleshooting, It is required to have a good Understanding of the Links between Various General Ledger and Accounts Payables Table involving Sub-ledger accounting tables, Below is a select statement which provides you the link between GENERAL LEDGER, ACCOUNTS PAYABLES AND  SUB-LEDGER ACCOUNTING Data base Tables

select --distinct aia.invoice_num
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAH.GL_TRANSFER_STATUS_CODE,XDL.ROUNDING_CLASS_CODE,XDL.APPLIED_TO_ENTITY_CODE
,AIA.INVOICE_ID,AIA.INVOICE_NUM,AIA.INVOICE_AMOUNT,AIL.LINE_NUMBER,AIL.LINE_TYPE_LOOKUP_CODE,AIL.AMOUNT,GL.CURRENCY_CODE "MRC CURRENCY",GLL.ACCOUNTED_DR,GLL.ACCOUNTED_CR
--,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AID,
APPS.AP_INVOICE_LINES_ALL AIL,
APPS.AP_INVOICES_ALL AIA
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND AID.INVOICE_ID = AIA.INVOICE_ID
AND AIL.INVOICE_ID = AIA.INVOICE_ID
AND AIL.INVOICE_ID = AID.INVOICE_ID
AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-25)
AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Payable%')
AND AIA.INVOICE_NUM LIKE 'EXP360319%'
AND GL.LEDGER_CATEGORY_CODE NOT IN ('ALC','SECONDARY')
order by aia.invoice_num;

Monday, March 4, 2013

General Ledger - Sub-ledger Accounting Dril Down


As Part of regular Designing and Trouble shooting of General Ledger and Subledger Accounting Analysis it is required to have knowledge of Technical Drill Down and Link between GL and Subledger Tables.

Please see the Below mentioned select Statement to Identify relation between various tables for DrillDown from GL To Subledger and Vice Verse.



select
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAL.ACCOUNTING_CLASS_CODE,xal.*
,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*--,8,PCD.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
--AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PCD.EXPENDITURE_ITEM_ID
--AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-10)
--AND GCC.SEGMENT6 != '000'
--AND UPPER(GJH.JE_CATEGORY) LIKE UPPER('PROJECT%')
--AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Project Accounting%')
and gjh.je_header_id = <je header ID>
and gll.je_line_num =<JE LINE NUMBER>


Thursday, August 23, 2012

Application Implementation methodology (AIM) Template list

Application Implementation methodology (AIM) Template list

Application Implementation Method is a proven approach for all the activities required to implement oracle applications. there are eleven processes of implementation.

1. Business Process Architecture [BP] – This phase outlines:

    *
      Existing Business Practices
    *
      Catalog change practices
    *
      Leading practices
    *
      Future practices

BP.010 Define Business and Process StrategyBP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision

BP.070 Develop High-Level Process Design

BP.080 Develop Future Process Model
BP.090 Document Business Procedure



2. Business Requirement Definition [RD] - This phase explains about the initial baseline questionnaire and gathering of requirements.

RD.010 Identify Current Financial and Operating Structure RD.020 Conduct Current Business Baseline RD.030 Establish Process and Mapping Summary RD.040 Gather Business Volumes and Metrics RD.050 Gather Business Requirements RD.060 Determine Audit and Control Requirements RD.070 Identify Business Availability Requirements RD.080 Identify Reporting and Information Access Requirements

3. Business Requirement Mapping [BR] – In this phase the requirements of business are matched with the standard functionality of the oracle applications.
BR.010 Analyze High-Level GapsBR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model

BR.070 Create Reporting Fit Analysis

BR.080 Test Business Solutions

BR.090 Confirm Integrated Business Solutions

BR.100 Define Applications Setup
BR.110 Define security Profiles



4. Application and Technical Architecture [TA] – This outlines the infrastructure requirements to implement oracle applications.

TA.010 Define Architecture Requirements and StrategyTA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy

TA.070 Revise Conceptual Architecture

TA.080 Define Application Security Architecture

TA.090 Define Application and Database Server Architecture

TA.100 Define and Propose Architecture Subsystems

TA.110 Define System Capacity Plan

TA.120 Define Platform and Network Architecture

TA.130 Define Application Deployment Plan

TA.140 Assess Performance Risks
TA.150 Define System Management Procedures



5. Build and Module Design [MD] – This phase emphasizes the development of new functionality (customization) required by the client. It mainly details how to design the required forms, database and reports.
MD.010 Define Application Extension StrategyMD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions

MD.070 Create Application extensions technical design

MD.080 Review functional and Technical designs

MD.090 Prepare Development environment

MD.100 Create Database extensions

MD.110 Create Application extension modules
MD.120 Create Installation routines



6. Data Conversion [CV] – Data Conversion is the process of converting or transferring the data from legacy system to oracle applications. Ex. Transferring customer records from the legacy to the Customer Master.
CV.010 Define data conversion requirements and strategyCV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs

CV.070 Prepare conversion test plans

CV.080 Develop conversion programs

CV.090 Perform conversion unit tests

CV.100 Perform conversion business objects

CV.110 Perform conversion validation tests

CV.120 Install conversion programs
CV.130 Convert and verify data



7. Documentation [DO] – Documentation prepared per module that includes user guides and implementation manuals.
DO.010 Define documentation requirements and strategyDO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual

DO.070 Publish user guide

DO.080 Publish technical reference manual
DO.090 Publish system management guide



8. Business System Testing [TE] – A process of validating the setup’s and functionality by QA(functional consultant) to certify status.
TE.010 Define testing requirements and strategyTE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments

TE.070 Perform unit test

TE.080 Perform link test

TE.090 perform installation test

TE.100 Prepare key users for testing

TE.110 Perform system test

TE.120 Perform systems integration test
TE.130 Perform Acceptance test



9. Performance Testing [PT] - Performance testing is the evaluation of transactions saving time, transaction retrieval times, workflow background process, database performance, etc
PT.010 – Define Performance Testing StrategyPT.020 – Identify Performance Test Scenarios
PT.030 – Identify Performance Test Transaction
PT.040 – Create Performance Test Scripts
PT.050 – Design Performance Test Transaction Programs
PT.060 – Design Performance Test Data

PT.070 – Design Test Database Load Programs

PT.080 – Create Performance Test TransactionPrograms

PT.090 – Create Test Database Load Programs

PT.100 – Construct Performance Test Database

PT.110 – Prepare Performance Test Environment
PT.120 – Execute Performance Test



10. Adoption and Learning [AP] – This phase explains the removal of the legacy system and oracle application roll out enterprise wide.
AP.010 – Define Executive Project StrategyAP.020 – Conduct Initial Project Team Orientation
AP.030 – Develop Project Team Learning Plan
AP.040 – Prepare Project Team Learning Environment
AP.050 – Conduct Project Team Learning Events
AP.060 – Develop Business Unit Managers’Readiness Plan

AP.070 – Develop Project Readiness Roadmap

AP.080 – Develop and Execute CommunicationCampaign

AP.090 – Develop Managers’ Readiness Plan

AP.100 – Identify Business Process Impact onOrganization

AP.110 – Align Human Performance SupportSystems

AP.120 – Align Information Technology Groups

AP.130 – Conduct User Learning Needs Analysis

AP.140 – Develop User Learning Plan

AP.150 – Develop User Learningware

AP.160 – Prepare User Learning Environment

AP.170 – Conduct User Learning Events
AP.180 – Conduct Effectiveness Assessment




11. Production Migration [PM] – The process of “decommissioning” of legacy system and the usage(adoption) of oracle application system.

PM.010 – Define Transition Strategy

PM.020 – Design Production Support Infrastructure

PM.030 – Develop Transition and Contingency Plan

PM.040 – Prepare Production Environment

PM.050 – Set Up Applications

PM.060 – Implement Production Support Infrastructure

PM.070 – Verify Production Readiness

PM.080 – Begin Production

PM.090 – Measure System Performance

PM.100 – Maintain System

PM.110 – Refine Production System

PM.120 – Decommission Former Systems

PM.130 – Propose Future Business Direction


PM.140 – Propose Future Technical Direction

Monday, July 23, 2012

Application Implementation Methodology

Application Implementation Methodology::


AIM consists of a project management methodology together with the underlying documentation templates that support the tasks you perform within this methodology. This combination of a methodology together with documentation templates makes AIM a powerful tool for assisting implementation participants in running and managing projects successfully.

There are 6 phases in AIM


  1. Definition ::
    1. Establish the business objectives and related Requirements,
    2. Define the Project Plan

  1. Operations Analysis :
    1. Analyze the operations and identify the fit between Organizational requirements and Standard Applications Functionality

  1. Design :
    1. Develop detailed design for the optimal Solutions to meet the future business requirements

  1. Build : (Implementation)
    1. Physical Software system Build and testing

  1. Transition:
    1. Deploying the Finished solution in to Organization

  1. Production Go live

** Each Phase has Number of tasks to be followed.


** There are more than 150 templates, which can be used through out the AIM Process.

There are two types of AIM Documentation template:
-        Project management related (Project plan, Change Orders, Quality control document. Etc.)

-        Oracle Applications Implementation related Docs (RD50, BR100, MD200 etc.)

Saturday, June 23, 2012

Security Profiles in Oracle Application R12

Security Profiles

Oracle Applications 11i had a limitation where, the Business Personnel’s responsible for a particular business function who had access to multiple organizations had to navigate to individual Organization specific responsibilities for taking action on the transactions. For example: a buyer who can create Purchase orders for multiple organizations will have to Navigate to each operating Unit specific responsibility to create or manage purchasing Documents.

R12 comes as a blessing for such user of Oracle applications where Multi Organization Access control features enabled such users to take action on documents and transactions without navigating between various organizations specific responsibilities. This feature can be implemented using Security Profiles in R12.

Security profiles is a feature in R12 which can be used for grouping the organizations logically such that a set of users can access a Set of Organizations using the responsibility which are assigned with particular Security profile.

There are two types of Security Profiles.

Security Profile: If it is required to group multiple operating units for the same business group.
Global Security Profile: If it is required to group multiple operating units for the different business group.
After the security profiles are defined it is required to run the Security List Maintenance program, before assigning the Security profile to the Responsibilities





A Security profile can be assigned to a responsibility by using the MO: Security Profile option.





If you set the MO: Security Profile option, you can also set the MO: Default Operating Unit profile option to specify a default operating unit.
It is important to note that if the MO: Security Profile option is not set for the responsibility it will behave same as R11i.

The MO: Operating Unit profile option still exists in R12 but it should only be used if Multi organization access control is not desired.

HR: Security Profile

Oracle Applications also has HR: Security Profile feature. This profile option is used for defining association between a Business group and the Organizations hierarchy. HR: Security Profile options needs to be setup before defining any new organizations or organization hierarchies under a Business group.




Wednesday, February 1, 2012

Designing Payables Interface

Designing Payables Interface

Designing a Payables interface is a very important task of any Oracle Applications Payables Implementation. Hence it is always required to create a payables Interface.

Payables Interface has significantly changed from 11i to R12.
Even though the R12 has Three levels of details on a payables Invoice which are

Invoice Header
Invoice Line
Invoice Distribution.

The Payables Interface Still continue to have two level of details, which are

Invoice Header
Invoice Line.

Below is a Insert script which can be leveraged for Inserting records in Payables Interface Header Table. AP_INVOICES_INTERFACE

Payables Invoice Header Interface Insert

INSERT
INTO apps.ap_invoices_interface
  (
    invoice_id,
    invoice_type_lookup_code,
    invoice_date,
    invoice_num,
    vendor_id,
    vendor_site_id,
    invoice_amount,
    source,
    org_id,
    INVOICE_CURRENCY_CODE,
    EXCHANGE_RATE_TYPE,
    --TERMS_NAME,
    DESCRIPTION,
    GLOBAL_ATTRIBUTE18,
    GLOBAL_ATTRIBUTE19,
    GLOBAL_ATTRIBUTE20,
    --GROUP_ID,
    --PAY_GROUP_LOOKUP_CODE,
    GL_DATE
  )

  VALUES

  (
    AP.AP_INVOICES_INTERFACE_S.NEXTVAL
   ,'STANDARD'
   ,SYSDATE --<invoice date>
   ,'KTICAP081801'
   ,24699    --<vendor_id validated against po_vendors.vendor_id>,
   ,35433     --<vendor_site_id validated against po_vendor_sites.vendor_site_id>,
   ,111     --<invoice_amount>
   ,<SOURCE>
   ,<ORG_ID>
   ,'USD'
   ,'Corporate'
   ,'KTEST for ICO'
   ,'Services In Country'
   ,'01-AUG-2014'
   ,'18-AUG-2014'
   ,trunc(Sysdate)
  );

 
Below is a Insert script which can be leveraged for Inserting records in Payables Interface Header Table. AP_INVOICE_LINES_INTERFACE


Payables Invoice Lines Interface Insert

INSERT
INTO APPS.ap_invoice_lines_interface
  (
    invoice_id
    ,invoice_line_id
    ,line_number
    ,line_type_lookup_code
    ,amount
    ,accounting_date
    ,DESCRIPTION
   --,PO_HEADER_ID
   --,PO_DISTRIBUTION_ID
   --,SHIP_TO_LOCATION_CODE
    ,DIST_CODE_COMBINATION_ID
    ,ATTRIBUTE_CATEGORY
    ,ATTRIBUTE1
    ,ATTRIBUTE2
    ,ATTRIBUTE15
  --  ,GLOBAL_ATTRIBUTE_CATEGORY
    ,GLOBAL_ATTRIBUTE18
    ,GLOBAL_ATTRIBUTE19
    ,GLOBAL_ATTRIBUTE20
    --,PROJECT_ID
    --,TASK_ID
    --,EXPENDITURE_TYPE
    --,EXPENDITURE_ITEM_DATE
    --,EXPENDITURE_ORGANIZATION_ID
    ,ORG_ID
   
    ,CONTROL_AMOUNT
    ,TAX_CLASSIFICATION_CODE
  )

  VALUES
  (
    AP.AP_INVOICES_INTERFACE_S.CURRVAL
   ,AP.AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
   ,1       
   ,'ITEM'   --<ITEM>/<FREIGHT>/<TAX>
   ,100     
   ,SYSDATE
   ,'KT ICO INVOICE DIST DESCRIPTION'
   ,'2906440' --'<Derive from Code Combination>'
   ,<ATTRIBUTE CONTEXT>
   ,'12-AUG-2014'
   ,'BIL'
   ,'987654321'
   --,'JE.IL.APXINWKB.GOOD_SERVICES'
   ,'Services In Country'
   ,'01-AUG-2014'
   ,'18-AUG-2014'
   ,<ORG_ID>
   ,'11'
,<TAX CODE>

  );

Sunday, January 1, 2012

Designing Accounts Receivables Interface

Designing Accounts Receivables Interface

Designing a Receivables interface is a very important task of any Oracle Applications Receivables Implementation. Hence it is always required to create a Receivables Interface.
In Oracle Application R12 the Receivables Invoice Interface has two level of details, which are

AR Invoice transaction Lines

Transaction lines can be used for populating the Majority of details in the AR invoice interfaces. And hence this is the most important table to insert the data in.

AR invoice Transaction Distribution

Though the Distributions are used for deriving the accounting of an AR transaction, and this can directly be done using the AR Auto accounting rules, data can be inserted in this table for Recevables and Revenue Accounting if Accounting required on lines is more complex than Auto Accounting rules and cannot be managed there.

Below is a Insert script which can be leveraged for Inserting records in AR Invoice transaction Lines

RA_INTERFACE_LINES_ALL

AR Invoice transaction Lines Interface Insert


INSERT INTO Apps.ra_interface_lines_all
(
INTERFACE_LINE_ID,
INTERFACE_LINE_CONTEXT,
--INTERFACE_LINE_ATTRIBUTE1,
--INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3,
BATCH_SOURCE_NAME,
SET_OF_BOOKS_ID,
LINE_TYPE,
DESCRIPTION,
CURRENCY_CODE,
AMOUNT,
CUST_TRX_TYPE_NAME,
CUST_TRX_TYPE_ID,
--TERM_NAME,
TERM_ID,
ORIG_SYSTEM_BILL_CUSTOMER_ID,
ORIG_SYSTEM_BILL_ADDRESS_ID,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE,
TRX_DATE,
TRX_NUMBER,
-- LINE_NUMBER,
UOM_CODE,
UOM_NAME,
ORG_ID
)

VALUES
(
AR.RA_CUSTOMER_TRX_LINES_S.NEXTVAL,
<INTERFACE ATTRIBUTE CONTEXT 9IF ANY),
'GL|98765432|8',
<AR INVOICE SOURCE>,
1002,
'LINE',
'KTEST FOR NBL IC AR INV 090208- with DIST',
'USD',
111,
<AR TRANSSACTION TYPE>,
11920,
--NA
4,
<CUSTOMER ID>,
<CUTOMER SITE ID>,
'User',
TRUNC(SYSDATE),
1,
TRUNC(SYSDATE),
<AR INVOICE NUMBER>,
--NA
'EA',
'EA',
<ORG_ID>
);

Below is a Insert script which can be leveraged for Inserting records in AR Invoice transaction Distribution

RA_INTERFACE_DISTRIBUTIONS_ALL

AR Invoice transaction Distribution Interface Insert

INSERT INTO Apps.RA_INTERFACE_DISTRIBUTIONS_ALL
(
INTERFACE_DISTRIBUTION_ID,
INTERFACE_LINE_ID,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE3,
ACCOUNT_CLASS
,AMOUNT
,PERCENT
,CODE_COMBINATION_ID
,SEGMENT1
,SEGMENT2
,SEGMENT3
,SEGMENT4
,SEGMENT5
,SEGMENT6
,SEGMENT7
,ORG_ID
)
VALUES
(
AR.RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL,
AR.RA_CUSTOMER_TRX_LINES_S.CURRVAL,
<ATTRIBUTE CONTEXT if required>,
'GL|98765432|8',
'REV' (DEFINE THE DISTRIBUTION TYPE “REV” FOR Revenue and “REC” for receivables)
,111
,100
,20798 <code combination id derived on the basis of Code> this is over written if you pop[ulate the actual segment values.>
,'<SEGMENT1>'
, <SEGMENT2>
, <SEGMENT3>
, <SEGMENT4>
, <SEGMENT5>
, <SEGMENT6>
, <SEGMENT7>
,ORG_ID
);


Monday, May 30, 2011

GL Balances Database Queries


GL Balances Database Queries


Troubleshooting and Understanding GL balances is a important part of any Oracle application Implementation, Support or Design Project.

In Oracle Application R12 The GL Balances are stored in the database table GL_BALANCES
Below are few queries which we can leverage to identify, Actual and Budget Balances.


Actual Balances


SELECT gl.name,cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE,
nvl(sum(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),0) "Actual Balance"
FROM apps.gl_balances bal,
apps.gl_code_combinations_KFV cc,
apps.gl_ledgers gl
WHERE cc.code_combination_id = bal.code_combination_id
and bal.ledger_id = gl.ledger_id
--AND bal.Ledger_id = <'your SOB id'>
AND bal.period_name LIKE 'JAN%2015'
AND bal.actual_flag = 'A'
and gl.ledger_category_code = 'PRIMARY'
--AND bal.currency_code = <'you SOB currency code'>
--and cc.segment1 = '101'
and cc.segment4 = '113113'
--and cc.concatenated_segments in ('202.0000.000000000.251001.0000.000.000000','202.0000.000000000.299000.0000.101.000000')
GROUP by cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE
order by gl.name,cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE;


Actual And Budget Balances


SELECT cc.concatenated_segments,cc.segment2,
nvl(sum(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),0) "Actual Balance",
nvl(sum(balb.begin_balance_dr + balb.period_net_dr - balb.begin_balance_cr - balb.period_net_cr),0) "Budget Balance"
FROM apps.gl_balances bal,
apps.gl_balances balb,
apps.gl_code_combinations_KFV cc,

WHERE cc.code_combination_id = bal.code_combination_id
AND balb.code_combination_id = cc.code_combination_id
AND
--AND bal.Ledger_id = <'your SOB id'>
--AND balb.Ledger_id = <'your SOB id'>
--AND bal.period_name = <'your actual period name'>
--AND balb.period_name = <'your budget period_name'>
AND bal.actual_flag = 'A'
AND balb.actual_flag = 'B'
--AND bal.currency_code = <'you SOB currency code'>
--AND balb.currency_code = <'your SOB currency code'>
and cc.segment1 = '101'
and cc.segment4 = '251011'
GROUP by cc.concatenated_segments,cc.segment2;

Friday, April 22, 2011

Important Accounts Receivables Database Tables

Important Accounts Receivables Tables

In an Accounts Receivables implementation, Support or trouble Shooting it is very important to have information about relevant Database tables, These details come very handy while any analysis of an issue or even technical architecture / solution designing, sharing some of the AR database Tables for reference

AR invoice Transactions

RA_CUSTOMER_TRX_ALL Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL Distribution for Transaction Lines

Transaction Interface Tables

RA_INTERFACE_LINES_ALL Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL Transaction Distribution information
RA_INTERFACE_ERRORS_ALL Transaction errors table

Receipt Interface Tables

AR_PAYMENTS_INTERFACE_ALL Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL Lockbox transfers the receipts that pass validation to the interim tables


Receipts tables
AR_CASH_RECEIPTS_ALL Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL This table stores all of the activity that is contained for the life cycle of a receipt.

• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.


Customer Tables

HZ_PARTIES A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.


Accounts Receivables Setup tables

RA_CUST_TRX_TYPES_ALL This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications