Oracle Projects Data Model

This topic covers the Oracle Projects Data model.

Projects are created from the Projects Templates / other Projects .

Base Table:  PA_IMPLEMENTATIONS_ALL

This table contains a row for each projects implementation, i.e one per operating unit. This table contains the setup information specific to the operating unit. 

The table data corresponds to the front end: Projects implementation super user resp -> Setup->System->Implementation options.

Base Table:  PA_PROJECTS_ALL

Important Columns:

Project ID: uniquely identifies a project

Name: Name of the Project

Segment1: Project Number

This project number can be automatic/ Manual depending upon the System Implementation Option Setups, i.e., if the setting is automatic, there is no need of giving a project number when creating a project. If it is manual, then a project number should be provided while creating the project.

Carrying_out_organization_id : This is the project owning organization.

Pm_product_code: This identifies the source of the project, generally used whenever a project is created from a third party product.

Project_Status_code: Indicates the project status whether active, approved, closed, rejected etc.

Start_Date :  Transaction start date of the Project.

Completion_Date: Transaction End Date of the project.

(There are different dates for a project each having its own significance, we will see those in a different topic.)

 Tasks:

Base Table:  PA_TASKS

Important Columns:

Task_id :  uniquely identifies a task.

Project_id : – From the pa_projects_all table .

Carrying_out_organization_id : This is the task owning organization.

(Task owning organization can be different from Project owning organization).

Start_date : Transaction Start Date of the Task

Completion_Date:  Transaction End Date of the Task

 Wbs_level :Indicates the level of the Task in the WBS hierarchy.

(WBS – Work Break down Structure indicates the structure of the Project)

Parent_Task_id :  uniquely identifies the Parent Task

Top_Task_id  : uniquely identifies the  Top Task.

Pm_product_code : Indicates the source of the task(used in conversion projects).

Pm_task_reference: uniquely identifies the corresponding task in the source system (used in conversion projects).

Base Table:  PA_AGREEMENTS_ALL

This is the table which stores the Agreement information.

Agreement_id : uniquely identifies the agreement.

Customer_id : Agreement’s customer id.

Agreement_num : Agreement Number

Expiration_Date : Expiration Date of the Agreement

Revenue_Limit_Flag: Flag which indicates whether the revenue can exceed the allocated funding amount.

Invoice_Limit_Flag: Flag which indicates whether invoice can exceed the allocated funding amount.

Amount: Agreement Amount.

Base Table: PA_PROJECT_FUNDINGS

Project_Funding_id :  uniquely identifies the Funding

Project_id : id of Project to which the funding is allocated

Task_id : id of Top Task to which the funding is allocated

Budget_type_code : Status of the budget whether baselined or not.

Allocated_amount: the amount of funding allocated to the project or top task.

To be continued…

R12 Audit Trail AP_SUPPLIERS AND vendor_site_code of AP_SUPPLIER_SITES_ALL.

Seeded Functionality (R12) - Demo

Consider the following scenario: We have a requirement where we need to capture the changes in vendor_name of AP_SUPPLIERS AND vendor_site_code of AP_SUPPLIER_SITES_ALL. Now let us see the detailed Stepwise approach to achieve this in R12.

1. Enable Audit Trail Profile Set the system profile AuditTrail:Activate to Yes at the site level. Navigation: System Administrator Responsibility-->System--->Profile

2. Enable Audit for Schemas We need to enable the audit for the schemas which are the owners of the tables, on which we are doing audit. We need to include APPS schema in every case. In this case we need to enable audit for AP (as it is the owner of the tables AP_SUPPLIERS and AP_SUPPLERS_SITES_ALL). Navigation: System Administrator Responsibility--->Security--->AuditTrail--->Install

3. Audit Group Creation Navigation: System Administrator Responsibility--->Security--->AuditTrail--->Groups. Here we need to create the Audit Group under Payables Application. Create an audit group with a proper naming convention, and select the group state as Enabled. Now include the table names AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL.

4. Specify the Columns to be Audited By default, Oracle has specified few columns under few tables that are Audit enabled. Check whether columns which we want to audit exists under these particular tables. If not include the columns. In this case (R12) Oracle has included few columns under AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL. We don’t have the vendor_name under AP_SUPPLIERS. So include it. Navigation: System Administrator Responsibility--->Security--->AuditTrail--->Tables

5. Run the concurrent request Now in order to create the Audit tables and corresponding views for the base tables that we want to audit, we need to run a concurrent request from System Administrator responsibility.

After the successful completion of the request, the audit tables and views will be created in the database. This will create following Audit tables: AP_SUPPLIERS_A for AP_SUPPLIERS AP_SUPPLIER_SITES_ALL_A for AP_SUPPLIER_SITES_ALL Also it will create following main views based on base table and Audit table. AP_SUPPLIERS_AC1 for AP_SUPPLIERS AP_SUPPLIER_SITES_ALL_AC1 for AP_SUPPLIER_SITES_ALL This will create Audit views on individual columns too. These will be in the form of TABLE_NAME_AV% Query it with following command for the audit views of the table AP_SUPPLIERS SELECT * FROM all_objects WHERE object_name LIKE 'AP_SUPPLIERS_AV%'

We have 25 audit views created, because Oracle has included few columns in the setup. When we ran the audit trial update program it creates views for those columns too. Now we need to check the view name for vendor_name column. In this case we have the view AP_SUPPLIERS_AV25 created for vendor_name. Verify it using the query SELECT * FROM ap_suppliers_av25

Similarly, Query for the audit views of the table AP_SUPPLIER_SITES_ALL We are nothing to do with these audit tables and columns. Oracle has used these tables and views and developed a report to report the changed data in these columns.

6. Run the Audit Report Now run the report Audit report from System Administrator responsibility. To do so we need to follow few steps as mentioned below. Create template SUPPLIERS TEMPLATE to include the audit group (the one we created to include the columns while doing the audit setup) i.e. AUDIT SUPPLIERS under Functional group. Navigate--->Audit Trial--->Audit Trial--->Reporting--->Audit Industry Template

Then run the report by navigating Navigate--->Audit Trial--->Audit Trial--->Reporting--->Audit Report

And pass the parameters for the remaining as per the requirement. Let us run the report for AUDIT SUPPLIERS Group.

Here we are passing null for all fields So it tries to report the data which is Transacted by All users, of all transactions, the data which has been changed before or equal to system time.

Select the VENDOR_SITE_CODE under the table name AP_SUPPLIER_SITES_ALL and VENDOR_NAME under the table AP_SUPPLIERS. Run the report.

Output:

Metalink ID's

How do you audit an Oracle Applications' user? [ID 395849.1] Auditing: How Do I Audit Responsibilities and Data? [ID 436316.1] How To Audit Data Changes In Tables Using Triggers [ID 1025832.6] Reference Documentation to Set Up of Audit Trail in Oracle HRMS [ID 111786.1] Understanding Data Auditing in Oracle Applications Tables using Audit Trail (AuditTrail) [ID 69660.1] Which HRMS Tables Need To Be Audited for SOX compliance? [ID 737201.1] Is There a Performance Issue when Enabling the Audit Trail for HRMS Tables? [ID 334379.1] Is There Any Way to Enable Auditing for All Tables in 11i ? [ID 471474.1]

How to Rebuild the data for TB in R12

Problem: How to Rebuild the data for TB in R12

Solution:

Rebuild the data for TB 

1. Navigate: Payables Responsibility > Setup> Accounting Setups > Subledger Accounting Setups > Open Account Balances Listing Definitions 
2. Query up your Report Definition: select Defined By = Accounting Flexfield or Segment (whichever applies in your case), click“Go”
3. Click on Update icon in the "Actions" column – the definition's update page opens.
4. "Touch" the definition by simply clicking "Apply" button (without making any change). 
This should start the “Open Account Balances Data Manager” concurrent processes and a (at least one) worker process “TB Worker…”.Wait for them to complete and verify if the TB data was rebuilt:

select xtb.definition_code
, xeh.ledger_id
, decode( xeh.upg_batch_id, null, 'No', 'Yes' ) upgraded_from_11i
, min( xtb.creation_date ) min_creation_date
, count( * ) events_count
from xla_trial_balances xtb
, xla_ae_headers xeh
where xtb.ae_header_id = xeh.ae_header_id(+)
/* and xtb.definition_code = '&TB_definition_code' */
group by
xtb.definition_code
, xeh.ledger_id
, decode( xeh.upg_batch_id, null, 'No', 'Yes' )
order by
xtb.definition_code, xeh.ledger_id;

Confirm if the TB report still empty .

References:

NOTE.553484.1 Troubleshooting Accounts Payable Trial Balance

Oracle Sub-ledger Accounting : a Technical Overview

https://www.slideshare.net/drdavidtaylor/oracle-subledger-accounting-a-technical-overview

http://oracletechi.blogspot.com/2013/11/r12-sla-tables-connection-to-ap-ar.html

http://www.oracleappshub.com/release12/r12-sla-analyzing-subledger-accounting/


https://oracleappsebsyashrajvarsity.blogspot.com/2015/12/oracle-ebs-financials-tables-link.html

https://resource.neocortex.com/object/view/2368

http://www.oracleappshub.com/oracle-purchasing/po-tips-and-useful-query/

R12- AR Tables and Changes

Here goes...
AR R12 Tables
New Tables
Description of Change
AR_REC_TRX_LE_DETAILS_ALL
Added detail level table under AR_RECEIVABLES_TRX_ALL to record the relationship between a receivables activity and the sets of asset and liability tax codes by legal entity.
AR_DEFERRAL_REASONS
Revenue Deferral Reasons
AR_DEFERRED_LINES_ALL
Revenue Deferral Reasons
AR_RDR_PARAMETERS_GT
Revenue Deferral Reasons
AR_REVIEWED_LINES_GT
Revenue Deferral Reasons
Changed Tables
Table Name
Description of Change
RA_CUSTOMER_TRX_LINES_ALL
Added numerous columns to support E-Business Tax and line-level Ship To, Added Payment extension identifier.
AR_MEMO_LINES
Added tax_product_category to support E-Business Tax uptake.
AR_TRX_LINES_GT
Added numerous columns to support E-Business Tax uptake, Added parent_line_id and deferral_exclusion_flag.
AR_TRX_LINES_TMP_GT
Added numerous columns to support E-Business Tax uptake, Added parent_line_id and deferral_exclusion_flag.
AR_ARCHIVE_DETAIL
Added numerous columns to support E-Business Tax and line-level Ship To,
RA_BATCH_SOURCES_ALL
Obsoleted invalid_tax_rate_rule column.
HZ_CUST_SITE_USES
Migrated tax_code, tax_header_level_flag, tax_reference, and tax_classification to ZX_PARTY_TAX_PROFILE and ZX_REGISTRATIONS tables.
AR_CASH_RECEIPTS_ALL
Added Payment extension identifier and obsoleted, approval_code,
address_verification_code, payment_server_order_num, Renamed column REMITTANCE_BANK_ACCOUNT_ID to REMIT_BANK_ACCT_USE_ID, Added legal_entity_id column.
AR_RECEIPT_METHODS
Added Payment Channel Code and obsoleted, payment_type_code, merchant_ref, merchant_id, auto_print_program_id
RA_CUSTOMER_TRX_ALL
Added Payment extension identifier and Payment Attributes,
obsoleted payment_server_order_num, approval_code, address_verification_code, Renamed column REMITTANCE_BANK_ACCOUNT_ID to REMIT_BANK_ACCT_USE_ID, Added legal_entity_id column.
RA_INTERFACE_LINES_ALL
Added Payment extension identifier and Payment Attributes, and obsoleted payment_server_order_num, approval_code, address_verification_code, Added legal_entity_id column, Added parent_line_id and deferral_exclusion_flag.
AR_AUTOREC_EXCEPTIONS
Renamed column REMIT_BANK_ACCT_ID to REMIT_BANK_ACCT_USE_ID
AR_BATCHES_ALL
Renamed column REMITTANCE_BANK_ACCOUNT_ID to REMIT_BANK_ACCT_USE_ID
AR_BATCH_SOURCES_ALL
Renamed column DEFAULT_REMIT_BANK_ACCOUNT_ID to REMIT_BANK_ACCT_USE_ID
AR_INTERIM_CASH_RECEIPTS_ALL
Renamed column REMITTANCE_BANK_ACCOUNT_ID to REMIT_BANK_ACCT_USE_ID
AR_RECEIPT_METHOD_ACCOUNTS_ALL
Renamed column BANK_ACCOUNT_ID to REMIT_BANK_ACCT_USE_ID
AR_TRX_HEADER_GT
Added legal_entity_id column
AR_TRX_HEADER_TMP_GT
Added legal_entity_id column
AR_LINE_CONTS_ALL
Added contingency_id as a foreign key to AR_DEFERRAL_REASONS
AR_TRX_CONTINGENCIES_GT
Added contingency_id, expiration_event_date and completed_flag.
Obsolete Tables
Table Name
AR_TA_ASGN_TO_INT_ALL
AR_TA_CASHAPP_OB_ALL
AR_TA_CR_AGEN_INF_ALL
AR_TA_CUST_HIER_CHILD
AR_TA_CUST_HIER_PARNT
AR_TA_CUST_INT_ALL
AR_TA_CUST_TRXSTR_ALL
AR_TA_DEDN_ASSGNS_ALL
AR_TA_DEDN_INT_ALL
AR_TA_DEDN_RELATE_ALL
AR_TA_LEGACY_CUST_INT
AR_TA_LEGACY_STAT_INT
AR_TA_PAYMNT_HIST_ALL
AR_TA_PROF_ASSGNT_ALL
AR_TA_PROMOTIONS_ALL
AR_TA_PROMOTIONS_INT
AR_TA_PROM_CUST_ALL
AR_TA_PROM_CUST_MAP
AR_TA_PROM_LINES_INT
AR_TA_PROM_PAYMNT_ALL
AR_TA_PROM_PAYMNT_TYP
AR_TA_PROM_PRODS_ALL
AR_TA_PROM_STATUS_MAP
AR_TA_RECON_INFO_ALL
AR_TA_RECV_TRXSET_ALL
AR_TA_REMIT_HIST_ALL
AR_TA_REMIT_PROF_ALL
AR_TA_RISK_COMENT_ALL
AR_TA_RULES_ALL
AR_TA_RULE_OUTPUT
AR_TA_RULE_SETS_ALL
AR_TA_RULE_SET_LN_ALL
AR_TA_TRX_SETS_ALL
AR_TA_TRX_SET_LN_ALL
AR_TA_TRX_TO_CREATE
AR_TA_TRX_TYP_INT_ALL
AR_TA_VERSIONS
New Views
View Name
Description of Change
AR_MEMO_LINES_VL
Added tax_product_category.
AR_SHIP_ADDRESS_V
Removed hz_loc_assignments.
RA_CUSTOMER_TRX_LINES_V
Added numerous columns
AR_DOCS_RECEIVABLES_V
AR_FUNDS_CAPTURE_ORDERS_V
ARBR_DOCS_RECEIVABLES_V
ARBR_DOCUMENT_LINES_V
ARBR_FUNDS_CAPTURE_ORDERS_V
AR_CBA_BANK_BRANCH_CONTACTS_V
AR_CBA_BANK_ACCOUNT_CONTACTS_V
AR_CBA_EFT_USER_NUMBER_V
AR_LINE_DEFERRAL_REASONS
Revenue Deferral Reasons
AR_LINE_CONTINGENCIES_V
Revenue Deferral Reasons
AR_RDR_ITEMS_V
Reasons, Validation Set for RDR setup
Changed Views
View Name
Description of Change
AR_AUTOMATIC_RECEIPTS_FORMAT_V
Added Payment extension identifier & payment channel code to view. Also modified the view to accommodate the data model changes related to payment uptake project.
AR_BOE_AUTO_RECEIPTS_V
Added Payment extension identifier to view
AR_BOE_REMIT_RECEIPTS_CBUNR_V
Added Payment extension identifier & payment channel code to view. Also modified the view to accommodate the data model changes related to payment uptake project.
AR_BOE_REMIT_RECEIPTS_NONCBR_V
Added Payment extension identifier & payment channel code to view. Also modified the view to accommodate the data model changes related to payment uptake project.
AR_BOE_REMIT_RECEIPTS_V
Modified the view to accommodate the data model changes related to payment uptake project
AR_BR_ASSIGNMENTS_V
Modified the view to accommodate the data model changes related to payment uptake project
AR_BR_MAIN_REMIT_BATCH_V
Modified the view to accommodate the data model changes related to payment uptake project
AR_CASH_RECEIPTS_V
Added Payment extension identifier and payment channel identifier to view
AR_CUSTOMER_BILLS_TRX_V
Added Payment extension identifier and payment channel identifier to view
AR_REMITTED_RECEIPTS_FORMAT_V
Added Payment extension identifier & payment channel code to view. Also modified the view to accommodate the data model changes related to payment uptake project.
RA_CUSTOMER_TRX_CM_V
Modified the view to accommodate the data model changes related to payment uptake project
Added Payment extension identifier and the payment channel identifier to view
RA_CUSTOMER_TRX_PART_CB_V
Modified the view to accommodate the data model changes related to payment uptake project
Added Payment extension identifier and the payment channel identifier to view
RA_CUSTOMER_TRX_PARTIAL_V
Modified the view to accommodate the data model changes related to payment uptake project
Added Payment extension identifier and the payment channel identifier to view
AR_AEL_GL_REC_V
Made changes to support the new Internal Bank Account data model.
AR_AEL_SL_REC_V
Made changes to support the new Internal Bank Account data model.
AR_CUSTOMER_BILLS_TRX_V
Made changes to support the new Internal Bank Account data model.
AR_BR_MAIN_REMIT_BATCH_V
Made changes to support the new Internal Bank Account data model.
AR_BATCHES_V
Made changes to support the new Internal Bank Account data model.
AR_BATCH_REMIT_V
Made changes to support the new Internal Bank Account data model.
AR_BOE_REMIT_RECEIPTS_V
Made changes to support the new Internal Bank Account data model.
AR_CASH_RECEIPTS_V
Made changes to support the new Internal Bank Account data model.
AR_INTERIM_CR_LINES_V
Made changes to support the new Internal Bank Account data model.
AR_CASH_RECEIPTS_V
Added legal_entity_id column
AR_CUSTOMER_BILLS_TRX_V
Added legal_entity_id column
AR_PAYMENT_SCHEDULES_PMT_V
Added legal_entity_id column
AR_PAYMENT_SCHEDULES_TRX2_V
Added legal_entity_id column
RA_CUSTOMER_TRX_CR_TRX_V
Added legal_entity_id column
RA_CUSTOMER_TRX_PARTIAL_V
Added legal_entity_id column
RA_CUSTOMER_TRX_PART_CB_V
Added legal_entity_id column
AR_PAYMENT_SCHEDULES_V
Added legal_entity_id column
RA_CUST_TRX_LINE_GL_DIST_V
Added column user_generated_flag

AP Trail Balances SQL Query for R12

SELECT  aia.invoice_num, aia.description inv_description,aia.attribute2 "Dept", aps.segment1 vendor#,
       aps.vendor_name, aia.invoice_currency_code, aia.invoice_amount,tb.diff "Amount Remain",
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment5
       || '.'
       || gcc.segment6
       || '.'
       || gcc.segment7 ACCOUNT,
       aia.invoice_type_lookup_code,
       aia.invoice_date, aia.attribute5 status,
       ap.NAME terms
  FROM xla.xla_transaction_entities xte,
       (SELECT   tb.code_combination_id,
                 NVL (tb.applied_to_entity_id, tb.source_entity_id) entity_id,
                 SUM (NVL (tb.acctd_rounded_cr, 0)),
                 SUM (NVL (tb.acctd_rounded_dr, 0)),
                   SUM (NVL (tb.acctd_rounded_cr, 0))
                 - SUM (NVL (tb.acctd_rounded_dr, 0)) diff,
                 party_id
            FROM xla_trial_balances tb
           WHERE tb.definition_code =:definition_code  -- Ex :  'AP_200_1001'
               and trunc(tb.gl_date) <=TO_DATE ('1-NOV-2011')
                       GROUP BY tb.code_combination_id,
                 NVL (tb.applied_to_entity_id, tb.source_entity_id),
                 tb.party_id
          HAVING SUM (NVL (tb.acctd_rounded_cr, 0)) <>
                                            SUM (NVL (tb.acctd_rounded_dr, 0))) tb,
       ap_invoices_all aia,
       ap_suppliers aps,
       gl_code_combinations gcc,
       ap_terms ap
 WHERE tb.entity_id = xte.entity_id
   AND xte.application_id =:application_id   --Ex :  200
   AND xte.source_id_int_1 = aia.invoice_id
   AND aia.vendor_id = aps.vendor_id
   AND tb.code_combination_id = gcc.code_combination_id
   AND ap.term_id = aia.terms_id

AR: Transaction Write-Offs

What do I mean when I say Transaction Write-Offs?

I mean to say that a customer’s transaction is outstanding for a longest period and you have no clue as to if the customer is going to pay or not, you cannot trace the customer and now you want to write-off that transaction.

Yep it’s called Bad Debts.

How to enter bad debts in Oracle Receivables? That’s your question.

Here’s my answer and Oracle Receivable’s standard functionality:

There is no such term in Receivables as Bad Debts. Receivables suggests that you “adjust” the particular transaction. Yes! Adjustments is type of write off that will cater your Bad Debt scenario.

These are setup and transactions steps for creating and entering Bad Debt Adjustments

Setup Steps:

1. Create a Receivables Activity with Adjustment type. I think adjustment is called a receivable activity because it write offs the outstanding balance on customer.
2. Assign Approval Limit of user to write off amounts. Obviously you cannot let anyone just write off the outstanding balance so you have to define the user’s limit.

Transaction Steps:

1. Query the transaction to write off
2. Adjust the transaction

Here are the details for Setups:

1.Creating Receivables Activity:

Navigation: AR>Setup>Receipt>Receivables Activity

1. Select the Operating Unit for which the activity is being defined.
2. Enter the Activity Name like Bad Debts Adjustments or Uncollectible Adjustemtns
3. Enter the Description (Optional)
4. Select the Type as Adjustments. Since it is defaulted for a new setup so move on.
5. Let the GL Account Source as Activity GL Account
6. Select Tax Rate Code Source as None.
7. Enter the Expense Account in Activity GL Account.
8. Save the Setup

2.Assign the Approval Limits:

Navigation: AR>Setup>Transaction>Approval Limits

1. Query the User Name
2. Select the Document Type as Adjustment
3. Enter the Currency, in case multiple currencies are used
4. Enter the From Amount. This value should be in negative, as the adjustment amount will be -ve of the invoice amount.
5. Enter the To Amount. Do I really need to tell that it should be greater then From Amount?
6. Leave Primary Check box uncheck as it does not relate to adjustment
7. More Tabbed region also does not relate to adjustments.
8. Save the Approval Limit

With this your Bad Debts or Transaction Write-Off is ready to work :)

The question remains about how to use this adjustment?

Hmm… Ok here goes the transaction steps :)

As I said, Query the transaction you want to write off. The Transaction should be Complete. Now do the following

1. Go to Action Menu and select Adjust
2. Select the Activity Name you’ve created for Bad Debt or Write-Off
3. If the whole transaction amount needs to write-off then select Type as Invoice. If partial amount needs to be write-off then select Line.
4. If you select Type as Line then you have to enter the negative amount to be write-off otherwise for Invoice Type the whole outstanding amount comes in automatically.
5. You have to create the accounting of adjustment separately or run the Create Accounting Report

When you save the transaction the outstanding amount is write off and following Account Entry is created for Adjustment

Activity GL Account / Adjustment               DR

Receivables                                            CR