Project Migration Extract Queries

OPEN PROJECTS

SELECT PROJECT_TYPE,SEGMENT1, NAME,PROJECT_STATUS_CODE,START_DATE,COMPLETION_DATE,BASELINE_FUNDING_FLAG,CARRYING_OUT_ORGANIZATION_ID,PROJECT_LEVEL_FUNDING_FLAG
  FROM PA_PROJECTS_ALL WHERE ORG_ID = '1062' AND TEMPLATE_FLAG = 'N'
EVENTS

SELECTA.SEGMENT1,B.COMPLETION_DATE,B.EVENT_TYPE,B.DESCRIPTION,B.BILL_AMOUNT,B.BILL_HOLD_FLAG,B.BILL_TRANS_REV_AMOUNT,
  B.PROJECT_CURRENCY_CODE,A.ORG_ID,C.NAME
  FROM PA_EVENTS B,PA_PROJECTS_ALL A,HR_ORGANIZATION_UNITS C
  WHERE A.PROJECT_ID = B.PROJECT_ID AND B.ORGANIZATION_ID = C.ORGANIZATION_ID AND ORG_ID = '1062'

REVENUE EVENTS

SELECT A.SEGMENT1,B.COMPLETION_DATE,B.EVENT_TYPE,B.DESCRIPTION,B.BILL_TRANS_REV_AMOUNT,
  B.PROJECT_CURRENCY_CODE,A.ORG_ID,C.NAME
  FROM PA_EVENTS B,PA_PROJECTS_ALL A,HR_ORGANIZATION_UNITS C
  WHERE A.PROJECT_ID = B.PROJECT_ID AND B.ORGANIZATION_ID = C.ORGANIZATION_ID AND ORG_ID = '1062' AND EVENT_TYPE LIKE 'REVENUE%' 
BILLING EVENTS

 SELECT A.SEGMENT1,B.COMPLETION_DATE,B.EVENT_TYPE,B.DESCRIPTION,B.BILL_AMOUNT,B.BILL_HOLD_FLAG,
  B.PROJECT_CURRENCY_CODE,A.ORG_ID,C.NAME
  FROM PA_EVENTS B,PA_PROJECTS_ALL A,HR_ORGANIZATION_UNITS C
  WHERE A.PROJECT_ID = B.PROJECT_ID AND B.ORGANIZATION_ID = C.ORGANIZATION_ID AND ORG_ID = '1062' AND EVENT_TYPE LIKE 'INVOICE%'

PROJECT WIP

SELECT A.SEGMENT1,B.EXPENDITURE_ITEM_DATE, B.EXPENDITURE_TYPE,B.BURDEN_COST,B.RAW_COST,B.TRANSACTION_SOURCE
  FROM PA_PROJECTS_ALL A, PA_EXPENDITURE_ITEMS_ALL B
  WHERE A.PROJECT_ID = B.PROJECT_ID AND A.ORG_ID = '1062' AND TRANSACTION_SOURCE = 'XX FLS EXCEL BASED MIGRATION C'

 PROJECT FUNDING 

SELECT PPA.SEGMENT1,ACV.CUSTOMER_NAME,PAA.AGREEMENT_NUM,PPF.FUNDING_CURRENCY_CODE,PPF.ALLOCATED_AMOUNT,PPF.PROJFUNC_CURRENCY_CODE,PPF.PROJFUNC_ALLOCATED_AMOUNT,PPF.BUDGET_TYPE_CODE
  FROM PA_PROJECTS_ALL PPA, PA_PROJECT_FUNDINGS PPF,PA_AGREEMENTS_ALL PAA,AR_CUSTOMERS_V ACV
  WHERE PPA.PROJECT_ID = PPF.PROJECT_ID AND PPA.ORG_ID = '1062' AND PAA.AGREEMENT_ID = PPF.AGREEMENT_ID
  AND PAA.CUSTOMER_ID = ACV.CUSTOMER_ID


CREDIT MEMO CREATION FOR PROJECTS MIGRATED INVOICES

1.        TAKE RA INTERFACE TABLE BACKUP
    CREATE TABLE XXS_DC_RA_INTF_LNS_V01
  AS
  SELECT INF_LNS.*
  FROM   APPS.RA_INTERFACE_LINES_ALL  INF_LNS
  WHERE  1=1
  AND    INF_LNS.BATCH_SOURCE_NAME = 'FNW_PROJECTS_INVOICES'
    2.        CREATE UPLOAD DATA AND SENT FOR LOADING 

SELECT
    'OPNAR'                          RECORD_TYPE,
    'FNW'                            CONV_CODE,
    'FNW'                            ORGANIZATION_CODE,
    ROWNUM                           RECORD_NUMBER,
    'S'                              TRANS_STATUS,
    'LINE'                           LINE_TYPE,
    DESCRIPTION                      DESCRIPTION,
    NULL                             PAYMENT_TERMS,
    RA.TAX_CODE,
    ARC.ORIG_SYSTEM_REFERENCE        CUSTOMER_NUMBER,
     'FNW'                            ORIG_SYS_REF,
       (    SELECT MAX(HCSUA.ORIG_SYSTEM_REFERENCE)
    FROM   APPS.HZ_CUST_SITE_USES_ALL HCSUA
    WHERE  HCSUA.CUST_ACCT_SITE_ID = RA.ORIG_SYSTEM_BILL_ADDRESS_ID
    )                                ORIG_SYSTEM_SHIP_ADDRESS_REF,
    (    SELECT MAX(HCSUA.ORIG_SYSTEM_REFERENCE)
    FROM   APPS.HZ_CUST_SITE_USES_ALL HCSUA
    WHERE  HCSUA.CUST_ACCT_SITE_ID = RA.ORIG_SYSTEM_BILL_ADDRESS_ID
    )                                ORIG_SYSTEM_BILL_ADDRESS_REF,
    RA.TRX_NUMBER                    INVOICE_NUMBER,
    RA.TRX_DATE                      INVOICE_DATE,
    RA.AMOUNT*(-1)                   AMOUNT,
    RA.AMOUNT*(-1)                   AMT_COUNTRY_CURRENCY,
    RA.CURRENCY_CODE,
    'FNW_IMPORTED_INV'               TRANSACTION_SOURCE,
    RA.GL_DATE,
    'FNW PROJECTS CR MEMO'           TXN_TYPE_NAME,
    RA.INTERFACE_LINE_ATTRIBUTE1        PROJECT_NUMBER,
    '00'                             TASK_NUMBER,
    NULL                             SHIP_DATE_ACTUAL
    FROM
    XXS_DC_RA_INTF_LNS_V01 RA
    , AR_CUSTOMERS_V ARC
    WHERE 1=1
    AND RA.BATCH_SOURCE_NAME = 'FNW_PROJECTS_INVOICES'
    AND RA.ORIG_SYSTEM_BILL_CUSTOMER_ID = ARC.CUSTOMER_ID

Troubleshooting Fund Check/Budgetary Control Guide

Troubleshooting Fund Check/Budgetary Control Guide -Part 1

Troubleshooting Details A) Common Setup

Profile Options

PA: Enable Budgetary Controls

PA: Allow Override of Budget Accounts

PA: Days to Maintain BC Packets

PSA: Budgetary Control Report Template (R12)

i)Bottom-Up Budgeting Setup/Processing Steps

Perform the following tasks to implement Bottom-Up Budgeting:

1. Define the Project Budget Account Workflow process

2. Define the Allow Override of Budget Accounts profile option

3. Define the Enable Budgetary Controls profile option

4. Define the GL budget

5. Define Integration for the Project Type/Project

6. Define the Project Budget

7. Generate accounting information for each budget line

8. Optionally, override the generated account for specific budget lines

9. Baseline the Project Budget

10. Run the Program: Create Journals process

11. Post the GL journal batches

ii)Bottom-Up Budgeting Integration Definition

Field Descriptions:

Allow Override at Project Level - Default or Dictate settings for projects using this project type.

Budget Type - Select from the defined project budget types. The list of values will not include project budget types that have baselined budget versions.

Balance Type - For Bottom-Up Budgeting the Balance Type must be Budget.

Non-Project Budget - Select a defined General Ledger budget. The list of values will only include GL budgets with a status of ‘Open’ or ‘Current’.

Remaining Fields - All remaining fields should be null

iii)Bottom-Up Budgeting Troubleshooting Tips

1. Account Generation Fails

Verify the Budget Account Workflow definition.

Is Dynamic Insertion enabled in General Ledger.

2. Budget Baseline Process Fails

Will the resulting budget journals reduce GL available funds to less than zero.

Does the budget contain a new or changed budget amount for a closed GL Period.

j)Top-Down Budgeting Setup

Perform the following tasks to implement Top-Down Budgeting:

-Define the Project Budget Account Workflow process

-Define the Allow Override of Budget Accounts profile option

-Define the Enable Budgetary Controls profile option

-Define the PA: Days to Maintain BC Packets profile option

-Define the PA encumbrance type

-Define the GL/CC budget

-Setup encumbrance in GL, PO, AP

-Define Integration for the Project Type/Project

-Define the Project Budget

-Generate accounting information for each budget line

-Optionally, override generated accounts

-Baseline the Project Budget

jj)Top-Down Budgeting Restrictions

-Supported for Oracle General Ledger and Oracle Contract Commitment funding only

-The budget entry method must be time phased by GL Period.

-Project budget amounts in open GL Periods.

-A budget line must exist for each budget period

-Cross Charge transactions are not supported

jjj)Top-down Budgeting Troubleshooting Tips

1.Transaction Fails Funds Check

Does a Baselined Budget exist.

Do budget lines exist for all project levels that have a control level other than ‘None’.

Is the transaction amount greater than available funds.

Was the PRC: Update Project Summary Amounts After Resource List Change ran after a new resource member was added.

2.Account Generation Fails

Verify the Budget Account Workflow definition.

Is Dynamic Insertion enabled in General Ledger.

3. Budget Baseline Process Fails

Will the resulting budget journals reduce GL available funds to less than zero.

Does the budget contain a new or changed budget amount for a closed GL Period.

Is the Budget amount greater than current actual plus commitment balances.

k)Non-Integrated Budgets Setup

-Enable BC in GL and encumbrance accounting in Oracle Payables or/and Oracle Purchasing

-Define the PA: Enable Budgetary Controls profile option

-Define the PA: Days to Maintain BC Packets profile option

-Enable Budgetary Controls for Project Type/Project

-Define Control Level Defaults and Time Intervals

-Create and Baseline a Cost Budget

-Update the Control Levels for your Baselined Budget

    Resource List

    Project/Task

    Resource Groups/Resources

kk)Non-Integrated Budgets Troubleshooting Tips

1. Transaction Fails Funds Check

Baselined Budget must exist.

Budget Line must exist if control level is not ‘None’.

Available funds must exceed transaction amount.

PRC: Update Project Summary Amounts After Resource List Change must be ran when a new resource member is added.

2. Budget Baseline Process Fails

Budget amount must be greater than current actual plus commitment balances.

3. The following processes fail to interface transactions:

Interface Usage and Miscellaneous Costs to GL

Interface Total Burdened Costs to GL

The Maintain Budgetary Control Balances process must be ran.

4. Data corruption in funds-related tables and cause failures during funds validation processing

Either BC in GL or encumbrance accounting in AP or PO have been changed ( disabled)

l)Troubleshooting Baseline/Transactions Failures

-Use the Transactions Funds Check Results form to review funds check failures

-Use the Transactions Funds Check Results form to review funds check failures that occur during transaction processing.

-Review Funds Check Result Messages and identify the corrective action see Note 445119.1 and/or Oracle Project Management User Guide pag 6-111

-Always check PA_BC_PACKETS table,where funds check errors are present

B)General Troubleshooting

1.See Note 296559.1 -Common Tracing Techniques within the Oracle

2. Note 301372.1 - How to Generate a Raw Trace File Including Binds and Waits for a Concurrent Program for 11.5.10

3. To debug workflow issues verify/check

-Note 224308.1 -Account Generator & Workflow Frequently Asked Questions

-Note.116375.1-Workflow How to Get Information Required to Run wfstatus/wfstat.sql

-Note 187071.1- bde_wf_item.sql - Runtime Data of a Single Workflow Item

C)Troubleshooting Budget Workflow

1.PABDACWF ORA-20002: 3136: Item 'PABDACWF/#SYNCH' Cannot be Accessed

See Note 744359.1 for solution

2. ORA-20002: 3135: 'Wf_Engine.Notification' is not permitted in synchronous processes

See Note 334232.1 for solution

3. Unable to baseline a budget through workflow budget remains in 'In progress" status

See Note 552483.1 for solution

4. Modifying Existing Budget Gets Rejected in Workflow With ORA-1422

See Note 396515.1 for solution

5. PAWFBUI - PAXBUEBU: Error ORA-6502 In Workflow When Budget Is Baselined

See Note 336055.1 for solution

6. Workflow background process for PAWFBUI has performance issues

Apply Bi -monthly rollup Patch 7364558 or consolidated

Patch 7584055  This patch fixes critical fund check issues

D)Troubleshooting Budget/Fund check 

1.For code issues/fixes see Note 745300.1 -Fund Check and Budgetary Control Troubleshooting Guide part 2

2.Check latest fund check consolidated Patch 7584055 applicable on top of 11i.PJ_PF.M

3.For Budget/Fund check datafixes see unpublished Note 820963.1-Troubleshooting Guide for Funds Check/Budgetary Controls Data Issues

4. Note.732309.1-R12.0.6+ Oracle Projects Funds Check Data Collection Transaction Data Test:

5. Note.423519.1-11i Oracle Project Costing Funds Check Data Collection Data Collection Test:  

Item Cross References in Oracle Apps R12

Item Cross References in Oracle Apps R12

   

This post talks about the Front end navigations related to Item Cross References.

1. How to create a cross reference type?
2. Which form is used to assign the Item cross reference type and Items?
3. How to define cross reference name for an item?
4. Is there a standard report to show the list of cross references?

1.     How to create a cross reference type?

Cross Reference : This form can be used to capture the reference information pertaining to an item, like drawing number, vendor part number, etc.

This is available at Inventory >> Items >> Cross References

                      

In this form, user can define the list of cross references applicable for the Items. This list will be available at the Item master.

                     

2.      Which form is used to assign the Item cross reference type and Items?

New Cross reference label & description can be added in this form.

            

3.      How to define cross reference name for an item?

After defining a cross reference, the value can be assigned to items using ASSIGN button. Select the item from the LOV and applicable value for the cross reference can be defined at VALUE column. Org assigning feature also avialble in this form.

                            

After defining the Cross reference labels at the Cross Reference form, values for the labels can be assigned from Item Master form as well.

                       

 Query the Item record >> Tools menu >> Cross Reference >> Select the Cross Reference Type  >> Assign Values for the Cross Reference Labels >> Save

 4.      Is there a standard report to show the list of cross references?

There is report at Inventory to view the Cross Reference Listing by items.

Name of the Report : Item Cross-References Listing

Thats all folks!!!!  please provide your comments to improve our team and readers knowledge....

Oracle Apps Credit Card: Process in iExpense and Payables

Oracle Apps Credit Card: Process in iExpense and Payables

Tables
ap_credit_card_trxns_all
ap_card_programs_all
ap_cards_all
AP_EXPENSE_FEED_LINES
AP_EXPENSE_FEED_DISTS


Credit Card setups:
1. Credit Card Code Sets window: create credit card code sets.

2. Credit Card Programs window: define your credit card program, including the card issuer, card type, and credit card code set.

3. Credit Card GL Sets window: define GL account sets

4. Credit Card Profiles window: define credit card profiles that you assign to credit cards. Attributes of a credit card profile include credit card program, GL account set, default GL account, exception clearing account, employee verification options, and manager approval options.

5. Credit Cards window: assign a card to a card holder and assign a credit card profile to the card


Steps to process credit card transactions:
Procure to Pay process:
You can streamline your procure-to-pay process by implementing a procurement card program in which your employees purchase items directly from suppliers using a credit card. The credit card issuer then sends transaction files directly to you (the employer). You can import credit card transaction files from your card issuer directly into Payables. Then, you can automatically generate transaction accounting distributions and create invoices to pay the card issuer.

1. You can create a SQL*Loader program that uses a flat file containing the credit card transaction details you want to transfer into the AP_EXPENSE_FEED_LINES table

2. Credit Card Transaction Validation and Exception Report:
   Use this program to validate the credit card transactions you imported into AP_EXPENSE_FEED_LINES.  This program identifies   exceptions such as undefined credit card numbers, invalid transaction or posted currency codes, and invalid credit card codes.

3. Employee verification. This initiates the Credit Card Transaction Employee Workflow, and it executes as you have defined it. If verification is required, an employee can verify transactions directly from a workflow notification.

4. Manager approval or notification. This inititates the Credit Card Transaction Manager Workflow, and it executes as you have defined it. If approval is required from the manager, a manager can approve an employee's credit card transactions directly from a workflow notification.

5. Credit Card Invoice Interface Summary: This program creates invoices for your credit card issuers in the Payables Open Interface tables. This program selects all records for a given date range in AP_EXPENSE_FEED_DISTS with a status of at least Validated.

6. Use Payables Open Interface Import Program to create the invoices for data in Payables Open Interface tables.

iExpense Credit card transactions process:
1. Load Employee credit card expense data into ap_credit_card_trxns_all.

2. Run the "Credit Card Transactions Validation Program" to validated the credit card transactions data.

3. Once data is validated the lines become available in iExpense for employees to select and submit the Expense reports.

Credit Card Setup

Credit Card Setup

How to setup Credit Cards Process in Oracle, below note id's will give detailed explanation


Credit Card Payments, Refunds and Chargebacks: Overview and Setup for Oracle Receivables Release 12 [ID 1357967.1]

Oracle Payments Minimum/Dummy Setup For Credit Card/Purchase Card Funds Capture Processing [ID 553614.1]

P-CARD (Procurement Card) SETUP AND TRANSACTION FLOW STEPS

P-CARD (Procurement Card) SETUP AND TRANSACTION FLOW STEPS

P-CARD SETUP AND FLOW STEPS

PREREQUISITE  : 1)I-Procurement setup should exist in the Instance

                                    I-Procurement Set-Up Docs

STEP 1: Create a SQL*Loader program that uses the flat file provided by the card issuer containing         the credit card transaction details you want to transfer into the  AP_EXPENSE_FEED_LINES_ALL table.

STEP 2: Define the card issuer as a supplier.

              Navigation: Oracle Payable Super User--> Suppliers--> Entry.

STEP 3: Create Credit Card Code

              Navigation: Oracle Payable Super User--> Setup--> Credit Cards--> Procurement Card-->   Code Sets .

STEP 4: Define Credit Card Programs

               Navigation: Oracle Payable Super User--> Setup--> Credit Cards--> Card Programs

STEP 5: Define the GL Account Sets

              Navigation: Oracle Payable Super User--> Setup--> Credit Cards-->Procurement Card-->       GL Account Sets.

STEP 6: Define the Credit Card Profile

              Navigation: Oracle Payable Super User--> Setup--> Credit Cards-->Procurement Card--> Profiles.

STEP 7: Select the Procurement Card check box to indicate that the supplier site is P-Card enabled.

STEP 8: Set the Profile Options: 

PO: Use P-Cards in Purchasing

STEP 9: After that upload the data through the SQL Loader Program, then run the                                      "Procurement Card Transaction Validation Program" Request.

 Navigation: Payable Super User--> View--> Request

STEP 10: Run the other Requests:

Navigation: Payable Super User--> View--> Request "Create Procurement Card Issuer Invoice"

  "Payables Open Interface Import with source" 

Source Parameter as  "PCARD"

Multi Period Accounting (MPA) 3 – SLA MPA Feature

From Oracle R12.0, subledger accounting (SLA) is introduced to Oracle E-Business Suite, which is a event-based accounting generation engine. From R12.1, MPA feature is introduced to SLA architecture. It enables users to create accounting for a single accounting event for more than one GL period.  Taking an example for prepaid expense, when AP invoice validated, an accounting event “Invoice Validation” will be created, by create accounting, the multiple period accounting journals will created for this event. As one SLA accounting journal can have only one GL date, so the relationship between event and SLA journal is 1:m.

The multiple period accounting process is depicted as below diagram.

1. Setup

1. Define Accounting Derivation Rule (ADR): This is used to get the journal entry account for recogonition entries. (Dr. Expense, Cr. Prepaid expense)
2. Define Journal Entry Type (JLT):
• Accural journal entry type
• Recogonition journal entry type
3. From journal entry type perspective, the total accounting entries should be like this
   Dr. Accrual journal entry type  (first period)
        Cr. Liability  (first period)
   Dr. Recogonition journal entry type  (first period)
        Cr. Accrual journal entry type (first period)
   Dr. Recogonition journal entry type (second period)
        Cr. Accrual journal entry type (second period)
   …….
4. Define Journal Line Definition (JLD) and assign Accrual journal entry type to JLD and set up accrual jlt’s ADR;
5. Attach Recogonition journal entry type to Accrual journal entry type and set up multi period accounting options and recogonition JLT’s ADR;
6. Define Application Accounting Definition(AAD) and Subledger Accounting Method (SLAM)
7. Assign SLAM to ledger

Create an AP invoice. In AP invoice line assign multiple accounting options.

The three options in red circle will be mapping to accounting attribute in SLA to create multiple accounitng. The two options in blue circle doesn’t make sense currently.

NAME	DATA TYPE	JOURNAL ENTRY LEVEL	ACCOUNTING METHOD BUILDER COMPONENTS	ACCOUNTING ATTRIBUTE ASSIGNMENT RULES	OPTIONAL OR MANDATORY	VALIDATION RULES
Multiperiod Option	Alphanumeric	Line	Event class	Should be assigned if another multiperiod accounting attribute is assigned	Optional	Should be Y for yes or N for no
Multiperiod Start Date	Date	Line	Event class	Should be assigned if another multiperiod accounting attribute is assigned	Mandatory if multiperiod option is populated
Multiperiod End Date	Date	Line	Event class	Should be assigned if another multiperiod accounting attribute is assigned	Mandatory if multiperiod period type is not populated

After invoice created and validated, the AP invoice distribution has still only two lines. The recogonition accounting entries will not be inserted to AP invoice.

After AP invoice validation, the AP invoice validation accounting event will be generated.

After AP invoice accounting, the event’s status becomes “Final Accounted”, even though not all of the journal entries under the event are finally accounted.

After AP invoice accounting, the multiple journal entries are generated for multiple periods.

For opened AP period, the status is Final;
For unopened AP period, the status is Incompleted;
Note that it depends on AP period status.
If the accounting calendar has not been defined for the period, the journal entry will not be generated, the remaining balance will be recogonized in the last defined period.
Another very important point is the relationship between the first accrual journal entry and repeated recogonition journal entries. In xla_ae_headers, accrual journal entry is the parent journal entry of recogonition journal entries.
Please also understand that all of journal entries are generated by “Create Accounting”. It is very important for us to extent the MPA functionality.

When next AP period is opened, the user can run the concurrent program “Complete Multi Period Journal Entries” to make the journal entry status from “Incompleted” to “Final”. Please note that it also depends on AP period status.
The program has outout to tell you how many and what MPA journal entries are handled.
Below is the a section of complete MPA journal entry program code. It shows how MPA journal entries are controled. (Package Name: XLA_MULTIPERIOD_ACCOUNTING_PKG)
After the concurrent program is run, the journal entry will be finally processed and can be transferred to GL.