Atlanta OAUG

Tips and Techniques

www.atloaug.org

This newsletter is in a different format from previous editions. I have been saving a long article submitted last fall for just an occasion when my busy schedule prevents me from creating a newsletter on the regular production schedule. Thanks to Graham Richmond of KPMG for expanding a tip first discussed in September 1998. This topic should be especially valuable to users trying to automate their R10 installations.

How to set up a scheduled concurrent program with an incrementing date parameter

In the Sept 1998 newsletter a question was asked on how to run the Payables Transfer to General Ledger when it requires the current date as one of the parameters. There are a couple of ways to do this.

If you are on Release 11, then it’s a breeze. Release 11 has several cool enhancements to Request Submission. One of them is the Schedule option ‘Increment date parameters each run’. This enables you to schedule the Payables Transfer to run at a specific time each day and have the Post Through Date parameter increment to the current date. The catch here is that if you want the Batch Name to include the date, you have to customize the standard report parameter; this is not recommended. An alternative is to set up a Request Set to run the program and define SQL statements for the Post Through Date and Batch Name parameters. See the R10 solution below.

If you are not Release 11, you have two options: Define a Request Set to run the Payables Transfer with customized parameters; or Define a Periodic Alert to call the Payables Transfer and feed the required arguments.

Requests Sets are more reliable and less complicated, but both options are covered below. The main advantage of using a periodic alert to run a concurrent program is that you have more submission options.

While the examples given below are for the Payables Transfer to General Ledger, the methodology can be used to run any scheduled concurrent program that requires incrementing date parameters. Another common usage is for the AutoInvoice Master Program in Receivables, where the Default Date parameter needs to increment. In this case, however, AutoInvoice is usually run daily in the early morning to invoice all sales orders ship-confirmed up until midnight; so the parameter has to be set to yesterday (SYSDATE – 1).

Both options should be subject to appropriate Q&A before either is implemented in a production environment.

Prior to either of the methods below, run the concurrent program manually so that you can see the parameter values that are generated. In our example of the Payables Transfer to General Ledger, set the Batch Name to ‘AP <current date>’ (AP 15-SEP-98 for example) and the Post Through Date to the current date. Make sure all other parameters are correct for the way you want the program to run every day: Journal Entry Category = All; Submit Journal Import = Yes; and the Audit/No Audit and Create Summary Journals parameters correct for your business process. Make a screen print of the Parameters window.

In System Administrator responsibility: Navigate to: Concurrent: Set

You can define the Request Set in Payables (Other: Requests: Set), but it then belongs to the user and cannot be changed by anyone else. It is better for the System Administrator to define it in AOL.

Define the Request Set as follows:

• Name: Payables Transfer to GL - Daily
• Application: Oracle Payables
• Description: Runs Payables Transfer to GL Every Day
• Owner: Leave blank
• Execution: Sequential
• Run Options: Abort on Error
• Requests:
• Seq: 10
• Name: Payables Transfer to General Ledger
• Print Options:
• Copies: 1 (if you want hardcopies of Execution Reports)
• Save: Yes
• Style: accept default Landscape
• Printer: Select printer if you want it hard coded, otherwise it will read user profile option.

Select Parameters button. Note: Several of the parameters for this program contain default values based upon bind variables such as :$PROFILE$, :$FLEX$. You must replace most of the SQL Statement values with the Constant values that were generated when the program was run manually (refer to screen print made previously). Note that the parameters illustrated below may be different from those for your site; it depends on how you have setup the Payables Options for Accounting.

Below is an example of possible changes to the Request Parameters:

• 20 Set of Books Name Leave this parameter as is
• 60 Batch Name SQL Statement select ‘AP’||’ ‘||SYSDATE from DUAL
• 70 Post Through Date SQL Statement select SYSDATE from DUAL
• 80 Journal Entry Category Constant All
• 90 Submit Journal Import Constant Y (Yes in R11)
• 160 Primary SOB --- Expense Constant Audit
• 170 Liability Constant No Audit
• 180 Cash Constant Audit
• 190 Discount Taken Constant Audit
• 200 Gain or Loss Constant Audit
• 210 Cash Clearing Constant Audit
• 215 Rounding Constant Audit (R11 only)
• 220 Create Summary Journals Constant Y (Yes in R11)
• 230 Secondary SOB } Assuming no Secondary SOB,
• to } delete the Type and Value for
• 290 Create Summary Journals } these and leave blank.

Note: It is a good idea to uncheck the Modify box for all parameters, so that they cannot be changed. Save your work.

The Batch Name parameter will result in an imported payables batch with a unique batch name of ‘AP <Current Date> <Request ID> : <Group ID> A.

Assign the Set to the appropriate Payables Request Group (Security: Responsibility: Request).

Now you need to submit the ‘Payables Transfer to GL – Daily’ request set and define the submission options.

In Payables responsibility: Navigate to: Other: Requests: Run

Note: Steps below are to R10; R11 is changed with new Schedule window and added functionality. In R11, you do not need the check the ‘Increment date parameters each run’ box, because we are already doing that by deriving sysdate.

In Submit Request screen:

• Type: Set
• Name: Payables Transfer to GL - Daily
• Parameters: Confirm all defaults are correct
• Run Options:
• Resubmit: Interval 1 Days
• From: Start
• To Start: Required start date and time

Submit report.

Confirm that the initial request and first reschedule run properly with correct date value.

In Alert Manager responsibility: Navigate to: Alert: Define

Define Alert as follows:

Select Verify button to validate the SQL statement.

Select Actions button and define Action as follows:

Select Action Details button and define Action Details as follows:

Arguments: This is the important part! You need to replicate the arguments generated when the program was run manually, but change the hard coded date. Go to Help> View My Requests (assuming you are SC or NCA) and select the Details button for the Payable Transfer program run manually. Highlight all values in the Parameters field and copy to your clipboard (Ctrl-C). Return to the Alert Details screen and paste into the Arguments field (Ctrl-V). The pasted parameters should look something like this:

A, R2I CLIENT, 1, Primary, Accrual, AP 15-SEP-98, 15-SEP-98, All, Y, Audit, No Audit, No Audit, Audit, Audit, Audit, Audit, Detail, Summary, Detail, Detail, Detail, Detail, Detail, Y, , , , , , , , , 58

Edit the arguments as follows:

• Enclose any parameters which contain spaces in double quotes. All null parameters must be replaced with two double quotes.
• Remove the comma separator between each parameter. The parameters should then be separated by a single space (hence the need for double quotes around parameters which contain a space).
• Replace the hardcoded date with &CURRENT_DATE

The arguments should would now look like this:

A "R2I CLIENT" 1 Primary Accrual "AP &CURRENT_DATE" &CURRENT_DATE All Y Audit "No Audit" "No Audit" Audit Audit Audit Audit Detail Summary Detail Detail Detail Detail Detail Y "" "" "" "" "" "" "" "" "" 58

This will generate an imported payables batch with a unique batch name of ‘AP <Current Date> <Request ID> : <Group ID> A.

Select Action Sets button and define Action Set as follows:

That’s it! Note that the System Administrator must set up the Periodic Alert Checker for the alert to run.

We need your contributions to keep the content of this newsletter fresh and topical. Please, send me an email to jcrum@sprynet.com or FAX to BOSS Corporation at 770-622-5400. Thanks!

Since this newsletter is a cooperative and joint effort, no one has tried to own (or be responsible for) the material contained in these newsletters. Therefore, the reader is granted a right to copy and distribute these materials for educational use. We ask only that you attribute the Atlanta OAUG as the source. Use these materials at your own risk, and ATLOAUG or the authors make no representation about the suitability of this material at your site.

This newsletter is delivered via the atloaug list server.

To SUBSCRIBE to the list, register as a member at www.atloaug.org. Or,

1) Send an email to majordomo@atloaug.org

2) Leave the subject line blank

3) The text of the message should be SUBSCRIBE atl-apps-l [your email address]

4) Please send inquires to Atl-Apps-L-Owner@atloaug.org

To UNSUBSCRIBE:

1) Send an email to majordomo@atloaug.org

2) Leave the subject line blank

3) The text of the message should be UNSUBSCRIBE atl-apps-l [your email address]

Location: Marriott Marquis, hosted by KPMG

See www.atloaug.org for presentations

I. Presentation: "General Ledger Top 10 Enhancements Brad Eldredge, AnswerThink

II. Presentation: "Oracle Workflow" Kristen McGregor and Lisa Maxwell, Oracle Corp

III. Presentation: "Converting Data From Legacy Systems & Migrating Databases" Chuck Fazio, Smart DB

IV. Presentation: "Data Warehousing" Harley Eisenberg, Tactics

IV. General Business - Brenda Carlton, KPMG

Future Meetings

Jan 15 Deloitte-Touche - by Marc Kilburn, Reservations: makilburn@dttus.com

1999 Meetings: Jan 15, Mar 19, May 21, Jun 18, Aug 20, Oct 15, Nov 19

Volunteers needed to speak in '99.

Send an email to bcarlton@sprynet.com.

Liaison Contact Person needed at each ATL-OAUG member company One liaison is needed from each company to forward Atl-OAUG notifications and newsletters to the other Oracle users/consultants within your company.

SEOUC - Southeast Oracle Users Conference - Feb 18-19. The 1999 Southeast Oracle Users Conference will be Feb 18-19 in Charlotte, North Carolina at the Airport Sheraton. In 1998 there were 250 attendees with three speaker tracks. In 1999 the target is 400 with 5 tracks. There are three tracks for tools and two tracks for applications (~50 presentations). Cost: $125 ($225 after Jan. 9th) Register at www.seouc.org

Spring OAUG Conference: Make your reservations now for the OAUG conference in San Diego April 18-22. A conference confirmation number is required in order to book a hotel at the OAUG discounted rate. See www.oaug.org for details. Submit your application to present a paper in San Diego via www.oaug.org.

Thanks to KPMG for providing us with the conference space and refreshments.

Special thanks to Caribiner International for providing the audiovisual equipment. Caribiner provides audiovisual equipment to companies and conferences throughout the U.S. They have agreed to provide the equipment for Atlanta OAUG on an "as needed" basis.

Final Tip from Jim Crum:

If you are planning to attend the San Diego OAUG conference, the last day to make reservations at discount rates is February 17. Call meeting expectations at 404-240-0999.