How to Reverse the distribution Lines when entered wrongly

Reverse the distribution Lines when entered wrongly

Responsibility: GL Super User
Nav: Setup > Financials > Flexfields > Key > Security > Assign
Pull up COMPANY rule
Delete row for INR STAT AP Manager

Query to find on which Patch level we are for particular module

Query to find on which Patch level we are for particular module

select * from fnd_application
where APPLICATION_SHORT_NAME='PA'


select * from FND_PRODUCT_INSTALLATIONS
where APPLICATION_ID=275

Can cross check the PATCH_LEVEL column.

To Track Requisition Number from PO Number

If we have PO Number then use below query to get the Related Requisition Number

SELECT segment1
FROM po_requisition_headers_all
WHERE requisition_header_id IN
(SELECT requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id IN
(SELECT requisition_line_id
FROM po_req_distributions_all
WHERE distribution_id IN
(SELECT req_distribution_id
FROM po_distributions_all
WHERE po_header_id IN
(SELECT po_header_id
FROM po_headers_all
WHERE segment1 = Give Po Number)
)
)
)
;

How to block Auto Approval of PurchaseRequisition created by preparer, and it should route to his manager for approval,though preparer has approval

How to block Auto Approval of PurchaseRequisition created by preparer, and it should route to his manager for approval,though preparer has approval Authority

Navigation to change the setup as below

PO Super User--->Setup--->Purchasing--->Document Types (Select Purchase Requisition)

Now uncheck the Attribute (Owner Can Approve). Save the work and close the form.

Once the above setup is done, no preparer can approve the requisitions he created rather it will route to his manager, though he has Approval Authority.

steps are involved while making payments, checkrun and generation of payment notifications

The following steps are involved while making payments, checkrun and generation of payment notifications:

1. Create Invoices
Navigation: AP Super User -->Invoices-->Entry-->Invoice Batches
Validate the above Invoices Through 'Actions' / Release Holds if any exist.

2. Create Payment Batch
Navigation: AP Super User -->Payments-->Entry-->Payment Batches
Actions --> Select Invoices
Actions --> Build Payments
Once the above steps are done Requery the Payment Batch and Click 'Payment' Button to Build Payments

3. Requery Payment Batch in 'Payment Batch Window' and perform below steps one by one after completion of Concurrent requests generated by the same
Actions --> Build Payments
Actions --> Format Payments
Actions --> Confirm Payment Batch

The Payment Notifications are generated in the 'Actions --> Confirm Payment Batch' step, when the Payment Batch is confirmed.

A trigger is executed when 'AP_INV_SELECTION_CRITERIA_ALL' table is updated with STATUS = 'CONFIRMED' and if the PAYMENT_METHOD_LOOKUP_CODE = 'EFT'.

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

select * from V$SESSION
where OSUSER like 'c_sgoud' -- User name of the terminal ( may be your windows login name )

Alter system kill session '146,46619'


select sid, serial# from v$session where username = 'USER';

alter system kill session 'SID,SERIAL#';

Oracle Table to Find Approval Limits for any person

Oracle Table to Find Approval Limits for any person

ap_Web_signing_limits_all

query with person_id

Time Card Creation

Time Card Creation

Patcard Creation steps for i2 technologies client

As i2 do not have OTL implemented so all the time cards created will be saved in pa tables only.

Go to responsibility @webtimecards--- Create New Timecard
Select project and other details and enter the time details for the week and submit the time cards , you will get the Time card number once you submitted.

The above information is saves in Pa_expenditures_all table and the lines details are saved in the internediate interface tables once you go to the
PA Super User ---Expenditures---Transaction Import ---Import Transactions
here select 'PRC: Transaction Import' select Transaction source as "Oracle Self Service Time" and Btach Name we need to take from pa_expenditures_all table USER_BATCH_NAME and submit the program.

Once Submitted the records will be inserted into select * from pa_expenditure_items_all table

3 C's.(Set of books)

Set of books determines the functional Currency, Chart of account structure, and
accounting Calendar for each company or group of companies, which are known as the 3 C's.

Select data with a hierarchical (parent/child) relationship.

Select data with a hierarchical (parent/child) relationship.

Syntax:

SELECT...
[START WITH initial_condition]
CONNECT BY [nocycle] PRIOR recurse_condition
[ORDER SIBLINGS BY order_by_clause]

Key:

START WITH : The row(s) to be used as the root of the hierarchy

CONNECT BY : Condition that identifies the relationship between
parent and child rows of the hierarchy

NOCYCLE : Do not circle around loops (where the current row has
a child which is also its ancestor.)

ORDER SIBLINGS BY : Preserve ordering of the hierarchical query
then apply the order_by_clause to the sibling rows
Example:

create table test_connect_by (
Level1 varchar2(100),
Item varchar2(100)
);

Sample Data

LEVEL1 ITEM

0 123
1 345
2 678
1 987
0 666

Inserted above values in the table

insert into test_connect_by values(0,666);

select * from test_connect_by


LEVEL1 ITEM

0 123
1 345
2 678
1 987
0 666


select * from test_connect_by
connect by prior item=level1


LEVEL1 ITEM

0 123
0 666
1 345
1 987
2 678

=====================================================

Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities

Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities in oracle

SELECT fu.user_id, fu.user_name, fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'SAIRAM_GOUD' --- for all user or for perticular user
-- AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name

Script To find Oracle API's for any module

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'PA_%API%'
order by
a.owner, a.name;

Project Expense Report Account Generator workflow process

If using a custom Project Expense Report Account Generator workflow process:

To implement the solution, please execute the following steps:

1. Set the profile option 'Account Generator:Run in Debug Mode' = 'Yes'. This will persist the
workflow data so that the custom Account Generator workflow can be debugged.

2. Bounce the Apache server.

3. Reproduce the error. The custom Project Expense Report Account Generator workflow process can now be viewed through the workflow monitor.

4. Resolve the issue with the custom Project Expense Report Account Generator

5. After the issue has been resolved, the employees whose expense reports have failed with this
error should withdraw and resubmit their expense reports.

Expense report workflow errors ( WFENG_NOTIFICATION_PERFORMER )

Expense report workflow errors ( WFENG_NOTIFICATION_PERFORMER )

Failed Activity Inform Individual of Expense Allocations Failure
Activity Type Notice
Error Name WFENG_NOTIFICATION_PERFORMER
Error Message 3120: Activity 'APEXP/301116' has no performer.
Error Stack Wf_Engine_Util.Notification_Send(APEXP, 550307, 301116, APEXP:OIE_AP_EXP_ALLOCATIONS_FAILURE) Wf_Engine_Util.Notification(APEXP, 550307, 301116, RUN)


For the above error we need to cross check the setup for that employee who has filed the expense report

select * from gl_sets_of_books
where CHART_OF_ACCOUNTS_ID=50184

select * from per_employees_x
where EMPLOYEE_ID=72546

select * from GL_CODE_COMBINATIONS_KFV
code_combination_id=298127

and from the above cross check concatnated segments all are defined correctly or not

and you can go to HR super user and find the employee and click on Assignment buttion and from their select purchase order information where you can find the segmnents change accordigly and now file new expense report which will be in success.

Oracle Workflow tables:

Oracle Workflow tables:

WF_ACTIVITIES table stores the definition of an activity. Activities
can be processes, notifications, functions or folders. A process activity
is a modelled workflow process, which can be included as an activity in
other processes to represent a subprocess. A notification activity
sends a message to a performer. A functions activity performs an
automated function that is written as a PL/SQL stored procedure.
A folder activity is not part of a process, it provides a means of grouping activities.

WF_ITEMS is the runtime table for workflow processes. Each row
defines one work item within the system.

WF_ITEM_ATTRIBUTES table stores definitions of attributes
associated with a process. Each row includes the sequence in which
the attribute is used as well as the format of the attribute data.

WF_NOTIFICATIONS holds the runtime information about a specific
instance of a sent message. A new row is created in the table each time
a message is sent.

AP Standard Expense Report Process (Expense report workflow error)

AP Standard Expense Report Process

Request Approval from APPROVER Error

Failed Activity Request Approval from APPROVER
Activity Type Notice
Error Name WFENG_NOTIFICATION_PERFORMER
Error Message 3120: Activity 'APEXP/301347' has no performer.
Error Stack Wf_Engine_Util.Notification_Send(APEXP, 549406, 301347, APEXP:OIE_REQ_EXPENSE_REPORT_APPRVL) Wf_Engine_Util.Notification(APEXP, 549406, 301347, RUN)

Resolution

For the errors with 'no performer' for 'Request Approval from APPROVER' Activity we should not restart the process, first we need to update the attribute values and then we can restart the process.

Once we open the workflow notification click on 'Activities History' and below we will have "Update Attributes" Button click on that and we will have fields with

Approver ID
Approver Name
Approver Display Name

Update above fields with proper values and Click on Apply Button , it will prompt with message
"Confirmation The workflow-level attribute values have been updated."

Now go to "Monitor Activities History" window and cilck the " Request Approval from APPROVER" Button and Re-start the workflow now the workfow will route to correct person and will be in Active state.

setting the Password for PDF File sent through XML Publisher

setting the Password for PDF File sent through XML Publisher

Open the rtf for which you want to set password and do the following things

1) Open the .rtf
2) Go to File - > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true

Note : property name should always start with xdo- .

Generating Excel Outputs from existing standard Oracle Reports 6i Output

Generating Excel Outputs from existing standard Oracle Reports 6i Output

Most end-users use Excel as a very generic tool, because of their familiarity and the control in analysing data using the power of MS Excel.

There is a trick to capture Oracle Reports output (text output) into a excel sheet.

Basic Steps :
1) Set the output format for the report to XML
2) Run the report to generate output.
3) Save the output file locally as a XML file.
4) Open the file using MS Excel.
5) To make it more beautiful, you may use a MS Excel Template.

The Excel Templates can be used to do complex data analysis and formatting. Displayed example below is a simple excel template, to make the point.

Points be to Noted while doing AR Conversion

Conversion (Data Migration) of Invoices in Receivables

Whenever we are going in for implementation of Receivables module, we have to consider the necessity of bringing in customer open balances from the old system to Oracle Receivables.

Some of the key questions that needs to be addressed before we take up a conversion activity. This is just a sample list and not an exhaustive one:

1. What are the different types of invoices in existing system Provide invoice samples? (invoices, credit/ debit memos, commitments, chargebacks)
2. Do we need to migrate only open invoices?
3. Do we migrate closed invoices also, if yes, then for what time period?
4. Please explain the invoice numbering mechanism? Is it automatic?
5. What are the interfaces from/to your existing receivables system?
6. Will the old system still be in place for querying and reporting purpose?

One can adopt one of the following three strategies for conversion:

1. Consolidate all the open balances customer-wise and create a single open invoice for each customer in the new Oracle system. The advantage of this system is that it is quite easy and not data intensive and makes good business sense in case of small businesses with very few customers. The major demerit of this approach is that later on one cannot track the individual invoices which the customer had sent and can become an audit issue also. In case of dispute over payment, this invoice will remain open till the dispute is resolved. Also, aging of invoices and dunning history will be lost.

2. Bring in all the open and partially paid invoices, credit/debit memos into the new system. Migrate all the unapplied and partially applied receipts to the new system. The advantage of this process of conversion is that you can track all open invoices individually and apply the correct receipt to correct invoice. Also, the conversion effort will be moderately low compared to case if you migrate all open and closed invoices. The disadvantage of this approach is that you cannot have a track of closed invoices in the new system. Also, it would be tough to handle scenarios where there is a dispute regarding incorrect receipt application, etc. This is the most common approach taken for receivables invoice, credit/debit memo and receipt migration.

3. Migrate all open and closed invoices to the new system. Reapply the migrated receipts to invoices in the new system. This approach makes sense if your receivables data is quite small else the effort involved in migrating all closed invoices and credit memos to the new system does not make much business sense.

The next question that arises is how we should migrate the invoices, credit/debit memos and receipts to the new system. Oracle provides standard interfaces to load the same. We can also use tools like Dataloader or manually key in the data into Oracle.

In this article i will talk of invoice, credit/debit memo conversion only. Prior to invoice migration, customer migration should be over apart from other pre-requisites. Following is the list of pre-requisites that should be completed prior to invoice, credit/debit memo conversion:

•Set-up of Customer Payment Terms should be complete
•Set-up of Currencies should be complete (this is necessary in case you have foreign currency invoices also)
•Set-up of Transaction Types should be complete
•Set-up of Accounting Rules should be complete
•Set-up of Tax rates and Tax codes should be complete
•Set up for sales representative should be complete
•Set up for debtor area should be complete
•Set up for income category should be complete
•Automatic customer invoice numbering should be set to 'No'
•Customer and Customer address should be migrated in the system
•Disable the Invoice interface purge program so that the data successfully imported should not get purged in the interface table.
•Set up for invoice batch source name should be complete

In the next step extract Invoice data from the legacy files and using SQL loader populate the interface tables RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL. Submit the Auto Invoice open interface program. Data from the two interface tables will be uploaded to the following base tables using the Invoice open interface program:

•RA_CUSTOMER_TRX_ALL
•RA_CUSTOMER_TRX_LINES_ALL
•RA_CUST_TRX_LINE_GL_DIST_ALL
•RA_BATCHES_ALL
•RA_CUST_TRX_LINE_SALESREPS_ALL
•AR_RECEIVABLE_APPLICATIONS_ALL
•AR_PAYMENT_SCHEDULES_ALL
•RA_INTERFACE_ERRORS_ALL

Ensure that the Purge Interface check box is not checked when you submit the Autoinvoice program. In the Autoinvoice errors form you can see the error corresponding to failed records. Correct the errors in the interface table and rerun the Autoinvoice program. Submit the Autoinvoice Purge Program separately. Only records that have been successfully processed by Autoinvoice are purged.

Using autoinvoice you can migrate invoices, credit/debit memos and on-account credits into Oracle. However, you have to set grouping rules (Navigation > Setup > Transactions > Autoinvoice > Grouping Rule) to group lines to create one transaction and ordering rules (Navigation > Setup > Transactions > Autoinvoice > Line Ordering Rules) to determine the order of the transaction lines on a particular invoice.

Troubleshooting the Projects to Receivables Interface

Troubleshooting the Projects to Receivables Interface

This feature is for anyone using Oracle Projects and Receivables 11.5.10 and beyond. There were several new features introduced to Projects 10.5.10 (Family Pack M), which now require additional configurations for the Receivables interface to work.Here are few quick tipts consolidated from the Metalink notes and user guides to remember what to do

Open Periods

Make sure to open respective accounting periods in Receivables. As Receivables Manager, navigate to Control > Accounting > Open/Close Periods.

Retention Error

You may not be using retention or multi-currency in Project Billing, but you still receive the “Transaction Code: TRA OU Level Retention Accounting flag N… Some transactions are disabled. Please Check auto-accounting setup” error when running the PRC: Interface Invoices to Receivables program (PATTAR).

To resolve the error you need to enable the AutoAccounting Assignments for Unbilled Retention Account and Rounding Account.

As Projects Billing Super User:

Navigate to Setup > AutoAccounting > Assign
Query up function ‘Revenue and Invoice Accounts’
Define Segment Rule Pairings for the Unbilled Retention Account
Define Segment Rule Pairings for the Rounding Account
Make sure to enable the Function Transactions!
Again, you need to do this even if you do not intend to enable retention and multi-currency billing in your Implementation Options.

Sales Credit Type Rejection

You might get a ‘No sales credit type at Implementation or Projec Level’ rejection when running the PRC: Interface Invoices to Receivables program (PATTAR).

In order to resolve this error as Projects Billing Super User:

Navigate to Implementation Options
Select ‘Exchange Rate Type’ in the Currency tab
Select ‘Sales Credit Type’ in the Billing tab
Transaction Source and Transaction Type Errors

The seeded Project Transaction Source and Project Transaction Types might be incomplete. Make sure to review and update these for your setting you defined in the Implementation Options > Billing tab. The source It is going to be ‘PROJECTS INVOICES’ (if new 11i or R12 implementation, or ‘PA INVOICES’ if upgraded from 10.7) and Transaction Types are going to be ‘Projects Invoice’ and ‘Projects Credit Memo’ (11i and beyond) or ‘PA Invoice’ and ‘PA Credit Memo’ (10.7) respectively.

As Receivables Manager:

Navigate to Transaction Sources: Setup > Transactions > Sources
Query up your Transactions Source you defined in the Implementation Options > Billing tab
Make sure the Reference Field Default Value field = ‘interface_header_attribute1′
Make sure the Standard Transaction Type = ‘Projects Invoice’
Make sure the Credit Meno Batch Source = ‘PROJECTS INVOICES’
Also navigate to Transaction Types: Setup > Transactions > Types
Query up ‘Projects Invoice’ transaction type
Make sure the Credit memo Type = ‘Projects Credit Memo’