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"