Auto Accounting Rules - SQL statements in
Subject: Re: Oracle Projects - AA rules
Hi app. gurus,
We are using SQL statements in AA rules to determine
appropriate account. Oracle Projects manual suggests to
check for performance quality before proceeding.
Requirements have forced us to use SQL statements in AA rules.
We have tuned our sql statements in AA rules.
. Have you experienced any problems in using SQL statements
in AA rules ?
. What are the issues to be considered before using SQL statements
in AA rules ?
. From a performance perspective, what parameters need to
be cheked in order to use SQL statments in AA rules ?
We are on Oracle Applications 10.7 SC Prod 16.1, HP-UX.
Any help is really appreciated.
Thank you,
Rama
From: Jpkozicki@aol.com
Subject: Re: Oracle Projects - AA rules
Date: Tue, 6 Apr 1999 16:57:50 EDT
In response to your request regarding using SQL statements in AA
rules, I
have the following observations:
1.I have seen several clients use SQL statements in AA rules without
too many
problems.
2. The major design considerations involve trying to minimize the
number of
variables used and the number of tables used in individual queries.
These
factors determine performance.
3. Test each SQL statement in SQL*Plus prior to activating them in
AA. If
there is an error in a SQL statement, AA will fail dramatically and
will be
difficult to debug.
4. Those functions that use SQL statements will take a moderate
performance
hit. The more SQL statements you use and the complexity of the
statements
will affect the performance of individual functions. I would not be
able to
predict performance without a "stress test" of function.
Hope this helps.....
From: "Donna M. Armstrong" donnabsc@gte.net
Subject: Re: Oracle Projects - AA rules
Date: Tue, 06 Apr 1999 14:03:18 -0700
Hi Rama,
I have had to use SQL based rules alot. Never noticed any real
performance degredation due to them. My only recommendation would be
to
make sure you're using as few joins as possible and to make sure the
statement is tuned properly.
Not sure what you're asking in terms of the parameters part of
your
question. I did write a tip sheet that was published in the OAUG
Insight Newsletter a while back (Probably about a year ago). The best
tip from that one was that you don't have to use numeric parameter
indicators like they use in the examples in the manuals. Instead of
:1,
:2, you can use a more desciptive label :Exp_Item_Id, :Exp_Type which
makes the code much easier to understand. How it works is that the
first time it hits a ":" when parsing the statement it looks to see
what
the first parameter name is, etc. It is not matching on the number to
find which parameter to use.
Hope this helps!
Donna
Donna M. Armstrong Office: (310) 457-4200
Business Systems Consultant Voice Mail/Pager: (888) 886-1564
E-Mail: donnabsc@gte.net
Date: Tue, 06 Apr 1999 11:03:03 PDT
From: "rama gopal" ramagopalan_s@hotmail.com
subject: Oracle Projects - AA rules
. What are the issues to be considered before using SQL statements
in AA rules ?
From: "Haseeb, Mohamed" Mohamed.Haseeb@Jacobs.com
Subject: RE: Oracle Projects - AA rules
Date: Tue, 6 Apr 1999 14:36:41 -0700
One issue we had was a problem in the form where you enter the SQL.
The form
had a bug - which would truncate some parts of the SQL when you save
it -
Resulting in Autoaccounting errors. We have a open tar on this. Recent
update from support is (couple of weeks back) - they have fixed the
problem
but the patch is not available yet.
Regards... Haseeb
From: rama gopal ramagopalan_s@hotmail.com
Date: Sunday, April 11, 1999 9:34 PM
Subject: Fwd: Re. Re. Oracle Projects - AA Rules
Hi all,
Thank you very much for giving useful information.
(For convenience, i have combined all responses in this mail. )
These are the things we have done so far,
We have tested all the SQL statements in SQL*Plus before
implementing in AA rules. We have tuned all the SQL statements.
We have looked at parse time, executions time ....
We tried to run distribute usage costs process, this process gave
signal 11 error. (As you know signal 11 error could occur due
to memory problem). We have reduced processing batch size to
30, error is gone but it is taking lot of time to process.
We tried various batch sizes 50,100, 500 ... We were successful
only at 30. We defaulted all the segments to constants while
doing legacy-Projects data conversion. At that time we could
successfully process 2000 lines in one batch. After adding
SQL statements, we got signal 11 error. Batch size of 30
is not an acceptable solution due to constraint on processing
time. We have looked at SHMMAX(shared memory), MAXDSIZ(maximum
memory) parameters, looks like to we have to increase these
parameters but not sure, how to arrive at a number for this
process. Our SHMMAX and MAXDSIZ are over 500 MB.
One more process, Interface to GL process erred out, again it
is due to stand alone SQL statements in AA rules.
We could successfully execute the SQL statements from SQL*Plus.
Since a database function gives more flexibility that a
SQL statement, we have included these functions in a package and
ran the process again. This time we could successfully run the
process. We did not have improvement in processing time.
. Do we need increase shared memory or maximum memory,
if SQL statements are used in AA rules ? If so, how to
determine these for this process ( 5 sql statements,
50K byte size ...)
. What are your recommendations regarding use of stand alone
SQL statements vs using database package in AA rules?
Any help is really appreciated.
Thank you,
Rama
Date: Sun, 11 Apr 1999 22:56:05 -0400
From: "Richard" oraapps1@prodigy.net
Subject: Re: Re. Re. Oracle Projects - AA Rules
I had the same problem, and support basically told me that as this was a
customization they could not help. The manuals clearly state that SQL
statements should not be too complex, otherwise it won't work. I eventually
simplified my SQL statements, and now it all processes fine. Try replacing
some of your more complex statements with lookup tables (I know they are a
pain to maintain, but it might be your only solution). Otherwise, reduce
your batch size, and run the processes overnight (this may not work in the
long term, as your batch size of 30 may suddenly seem too big to Oracle,
depending on what else is running causing everything to fail!)
I also found that reducing the size of the rule names and descriptions
helped a bit, although this may have been a co-incidence.
Another method I have used (not recommended as Oracle says they do not
support direct table updates in extensions!) is to use an extension to run
the more complex sql statements and load the values into an attribute on the
expenditure items table. Then change your autoaccounting rules to read the
value of this attribute, which is a far less complex statement.
Another annoying thing I found was that once autoaccounting fails because
the SQL statement is too complex, it seems to store that result in memory
somewhere, and it will continue to fail even if you are only costing one
expenditure item. To re-test the SQL statement, you need to copy the rule
with a new name, and assign the new rule, then it will magically work again
for a while (even though the same SQL is being used!)
Your best bet is to go back to the design phase, and reduce the complexity
of your rules, and try to use as many constants as possible.
If anyone has any ideas on how you can increase the memory allocated to
autoaccounting, I'd be interested in hearing the solution. I've had the
problem occur with autoaccouting in AR before as well, and the solution was
to pin certain packages into memory on database startup. Perhaps this may be
the solution for this problem as well?
Let us know the outcome.
From: Jpkozicki@aol.com
Subject: Re: Oracle Projects - AA rules
Date: Tue, 6 Apr 1999 16:57:50 EDT
In response to your request regarding using SQL statements in AA
rules, I
have the following observations:
1.I have seen several clients use SQL statements in AA rules without
too many
problems.
2. The major design considerations involve trying to minimize the
number of
variables used and the number of tables used in individual queries.
These
factors determine performance.
3. Test each SQL statement in SQL*Plus prior to activating them in
AA. If
there is an error in a SQL statement, AA will fail dramatically and
will be
difficult to debug.
4. Those functions that use SQL statements will take a moderate
performance
hit. The more SQL statements you use and the complexity of the
statements
will affect the performance of individual functions. I would not be
able to
predict performance without a "stress test" of function.
Hope this helps.....
Creating Historical Data in Oracle Projects
From: Rangaraju_Krishna@tmac.com
Subject: Projects: Creating Historical Data in Oracle Projects
We have a requirement for creating historical data from 1994 - 1997
(Labor,
Expenses, Subcontractors, Overhead, G&A etc). We are in production from
Jan,
1998 (Release 10.7 SC). The issue is we have all the information on the
paper
that we want to create in the system. We are looking at creating ONE
entry
for
each revenue category (Labor -97, Overhead -97 ,Labor-96, Overhead-96
etc)
per
year. And we are planning to create "Events" for all these revenue
categories,
then recognize the revenue and invoice amounts.
We want to know if anyone has any other ideas or comments on this method
for
creating historical data. All we need is capturing the "Total" billed
amounts
for the year 1994-1997.
Thx
Krishna
Date: Mon, 10 May 1999 11:49:32 -0700
From: "Haseeb, Mohamed" Mohamed.Haseeb@Jacobs.com
Subject: RE: Projects: Creating Historical Data in Oracle Projects
We did a similar conversion. We brought in data for each of the projects
grouped by Revenue category.
Best way is to bring it thru events. We had the data in text file format.
Since you have it only in paper, create a spread sheet with the info and
load it into a staging table. The main advantage here is, event table needs
very limited information - Project, Task (optional), date, event type, Org
and Rev and billing $.
If you have any specific question on this conversion, feel free to write to
me.
Regards.. Haseeb
Date: Tue, 11 May 1999 09:26:01 -0700
From: Eric Tegenfeldt eric_tegenfeldt@rand.org
Subject: Re: PA: Creating Historical Data in Oracle Projects
Hello Krishna,
Haseeb gives you some good advice. Here are a few more details which
you
may or may not have considered for a conversion of historical revenue
based
on a conversion I worked on in the past:
1) We created a special Manual event type "Conversion" for the
historical
events.
2) If your agreements have "Hard Limit" turned on, you'll need to verify
that the funding is adequate to cover the historical revenue.
3) We ran the Streamline processes after generating revenue and invoices
in
order to post the data in PA but we then had to take extra steps to keep
the
historical revenue from doubling up in GL and AR. Since it is
historical, I'm
assuming the revenue you are converting is already in your General
Ledger and
has already been billed.
For GL, we just deleted the journals that were created in the
Streamline
process from PA and also the journals from AR.
For AR, I should state that we actually didn't run the "Streamline"
process
but rather did each step individually since we had to insert a couple
custom
steps into the process. Basically, we had to write a couple custom
scripts
that first copied all of our invoices from the RA_TRANSACTION_INTERFACE
table,
then converted them into credit memos and inserted them back into the
Interface table after AutoInvoice had completed successfully for the
original set of invoices. We then ran AutoInvoice for the credit memos
and
finally Tieback in PA for the original set of invoices.
4) And of course, last (and perhaps least) we had to set the calendar
back
while we did all of this processing so that the GL dates in PA would be
accurate. To be safe, we did the conversion activity over the weekend
in
order to keep the users out while the old periods were opened up.
Happy Converting!
Eric
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Eric B. Tegenfeldt Office: 310.393.0411 X7164
Oracle Projects Consultant Pager: 661.538.3896
Business Information Systems
RAND Corporation
Date: Wed, 12 May 1999 12:10:27 -0700
From: "Haseeb, Mohamed" Mohamed.Haseeb@Jacobs.com
Subject: RE: PA: Creating Historical Data in Oracle Projects
Just to add to what Eric listed:
1. We also created 14 different 'Conversion' events for various categories -
it helps in the long run to identify them seperately
2. To avoid it going to GL, we ran it upto the Tie-back process - then
deleted the journal without posting them.
3. For AR - we did not convert any invoices directly into AR. For the paid
invoices, we used Auto-lockbox to clear them out. For the Open invoices, we
brought them from PA to AR individually with the original dates. This way,
we had the same AR aging report. Also we were able to keep the AR Invoices
invoices tied up to Project for Project status inquiry.
MRC - Project Connect and AMG
From: oraapps-l@cpa.qc.ca [mailto:oraapps-l@cpa.qc.ca]On Behalf Of
Sylvain Fradette
Sent: Tuesday, May 11, 1999 11:12 AM
Subject: MRC - Project Connect and AMG
Does anyone know if you can use Oracle Project Connect and Activity
Management Gateway with a secondary reporting set of books under Oracle
Project.
Sylvain
Date: Wed, 12 May 1999 10:09:22 -0600
From: "Martha McKillip" mmckillip@projectp.com
Subject: RE: MRC - Project Connect and AMG
Hi:
Current design for MRC (Multiple Reporting Currencies) effects the
transaction tables rather than the project wbs, budget, and resource tables.
In an MRC environment, projects and tasks are created in the primary set of
books. When transactions are entered (through the forms, through import)
records are created for those transactions for the reporting set of book(s).
When processing such as the distribute processes, generate revenue/invoices,
etc. occurs, additional lines are created in the reporting set of books. In
short, MRC primarily effects transactions.
Project Connect (and AMG) are designed for creating and maintaining Project
WBS information, Budgets, and Resource Lists. Therefore, the information
from Projects Connect would be sent to the primary set of books. Projects
created in Project Connect should be visible in your reporting set of books.
In short, Project Connect (and AMG) do not effect transactions.
The place where Project Connect deals with transactional information is with
interfacing actuals from Oracle Projects to Project Connect. This will be
done from the primary set of books only. Currently, Project Connect pulls
actuals from the summarization tables, not the transactional tables. MRC at
this time, does not provide the summarization information in the reporting
set of books.
Hope this information helps.
Martha Anne McKillip
Project Partners, LLC.
http://www.projectp.com
mmckillip@projectp.com
Projects/AR Auto Invoicing
Date: Wed, 12 May 1999 17:33:40 -0500
From: "nkumar" nkumar@se-tech.com
Subject: Fw: Projects/AR Auto Invoicing
I have created an invoice in PA and generated draft invoice, approved
and released it. I have interfaced the invoice successfully to
Receivables
However when I run the Auto Invoice in AR I get an error with a message
" All enabled segments of Transaction Flexfield must have a value". I
have not made any changes to the Invoice Transaction and Line
Transaction Flexfield structures and have compiled them successfully. I
think Segment 8 (pre-seeded Line Transaction FF - Line Type) is not
getting populated
I would apprecciate any pointers on this
We are on Rel. 11.0.2 HP UX
Date: Thu, 13 May 1999 12:07:58 GMT
From: "Indrasen Kasireddi" indrasen98@hotmail.com
Subject: Re: Projects/AR Auto Invoicing
Why don't you take a look into interface attribute columns of
RA_INTERFACE_LINES and drive back to PA for further investigation for
missing data.
Hope this helps!!!
- Sen
PA-Commitment Status
Date: Thu, 13 May 1999 14:38:05 +0530
From: achattopadhyay@punjlloyd.com
Subject: PA-Commitment Status
Hi all,
We are interested to get the status of Commitment Amount for various
Projects from Oracle Projects(10.7), through the Project Status Inquiry
form. But the problem that we are facing with this new facility that the
system shows for all type of transactions in the form of Open Requisitions
or Purchase Orders or Invoices irrespective of the Approval Status of the
documents (PR/PO/Invoice).
If the said documents are for any reason Incomplete (i.e not even submitted
for approval), then the system should not take into account for such open
documents.
Is there any facility or parameter in the PSI form which will filter for
Approved documents only ? We would highly appreciate if anybody gives a
feedback on the same or even suggest some workaround so as to get the
commitment amount for "APPROVED" documents only.
Thanks and Regards,
Arup
Date: Thu, 13 May 1999 08:15:20 -0700
From: Eric Tegenfeldt eric_tegenfeldt@rand.org
Subject: Re: PA-Commitment Status
Good morning Arup,
Have you looked at the Project Status Inquiry client extension? I have
used this to generate a number of custom columns on the PSI form. I
know that it will create values at the Project, Task, and Resource
levels (although there is currently a bug in 10.7 affecting the Resource
level) but I'm not sure about Commitments. However, even if you are
unable to override the Commitment amounts, you could still add logic to
the extension to calculate commitments using whatever criteria you like
and send the result to Project and/or Task levels.
The name of the package for the client extension is PAXVPS2B.pls and you
can find it at $PA_TOP/admin/sql. When you update it, remember to save
the modified version in a different directory (preferably under your
custom directory).
E-mail me directly if you have more specific questions.
Best wishes,
Eric
Date: Thu, 13 May 1999 09:00:05 -0600
From: "Martha McKillip" mmckillip@projectp.com
Subject: RE: PA-Commitment Status
Hi:
Commitment values are based on a view (pa_commitment_txns_v) that Oracle
allows to be customized. The file that contains the view definition is
PACMTVW.sql. If you check on pages 11-420 to 11-422 in the 10.7 Projects
Users guide, you will find information on this view and customizing it.
By default the view includes all open requisitions irrespective of approval
status, all open purchase orders irrespective of approval status, and all
supplier invoices not yet interfaced.
Martha Anne McKillip
Project Partners, LLC.
http://www.projectp.com
mmckillip@projectp.com
Date: Thu, 13 May 1999 08:25:53 -0700
From: "Donna M. Armstrong" donnabsc@gte.net
Subject: Re: PA-Commitment Status
Hi Arup,
It sounds like you found a bug. If you can't wait for Oracle to
fix it there is documentation in the Technical Reference Manual on
customizing the commitment view. But Oracle's definition of a
commitment has always been Approved Requisitions/POs and Invoices that
are not yet transferred to PA.
Hope this helps,
Donna
Donna M. Armstrong Office: (310) 457-4200
Business Systems Consultant Voice Mail/Pager: (888) 886-1564
E-Mail: donnabsc@gte.net