Oracle Apps Technical Blog

Multi Org in Release 12

2007-01-23T23:36:00.000Z

You must have heard that multi org access control has been introduced by Oracle in R12.

This is an excellent feature from Oracle.

Rather than have one responsibility-one operating unit.
Now you will have one responsibility-multiple operating units.

I suggest you read the below article on Multi Org Access Control in Oracle R12 [release 12]

http://oracle.anilpassi.com/multi-org-r12.html

OA Framework Tutorials

2007-01-23T23:32:00.000Z

Please find a list of articles on OA Framework Tutorials
These cover extension of OA Framework.
This covers, Extension of View Objects.
Also, Extension of Application Module, EO

Extension of Application Module in OA Framework - Part 1

Extension of Application Module in OA Framework - Part 2

How to use XMLImporter in OA Framework

When to bounce and How to Bounce Apache Server when working on OA Framework

Java in Oracle Apps
How to write Java Concurrent Programs in Oracle Apps?

New Articles added on HRMS and Payroll

2006-12-11T13:11:00.000Z

New articles have been added on HRMS and payroll

Visit http://oracle.anilpassi.com

OA Framework Extensions for View Objects

2006-12-09T11:43:00.000Z

Please find an article that explains how to modify LOV in Oracle's OA Framework without customization

Link to Article for Extending OA Framework View Object

Best Practices to design Interfaces in Apps

2006-12-05T00:07:00.000Z

This article discusses various approaches on how to design Oracle Apps Interfaces.

I have considered Point to Point interface, alongwith a centralised interface approach.

ORACLE APPS INTERFACES BEST PRACTICES

How to Setup Oracle HRMS Steps

2006-12-05T00:05:00.000Z

Please find an article, which is not BR100.

However, in this article, I am listing all the practical steps that one undergoes to implement Oracle HRMS.

LINK TO ORACLE HRMS SETUPS ARTICLE

FND_LOG_MESSAGES FND Debug in Apps

2006-12-05T00:02:00.000Z

Do you wonder, how Oracle Apps Debugs its applications?

Do you want to know how to debug forms, reports, OA Framework, Java, Workflows all using single methodology?

To find answer to your questions, please click on Link To FND Debug Logging Article

iProcurement - Step by Step Implementation

2006-11-26T12:11:00.000Z

Please find an article that explains step by step implementation of Oracle iProcurement.

It covers:-
1. Purchasing setup
2. Setup required on Oracle Exchange server
3. Setup required for Punchout in Catalog Administrator in iProcurement.

Link for article on Oracle iProcurement Setup

FND_LOG_MESSAGES Debugging Oracle Apps EBS

2006-11-21T01:01:00.000Z

Please find the article for Debugging in Oracle Applications.

FND_LOG_MESSAGES [FND Logging] article link

Oracle Fusion - Tools for Development in Fusion

2006-11-21T00:58:00.000Z

The future of current conventional Oracle Apps skill sets does not appear to be great in Fusion.

Please find the comparison of Toolsets in Oracle Fusion in comparison to current Toolsets.

Oracle Fusion Development Article Here

Make a field case insensitive using Forms Personalization

2006-11-16T14:10:00.000Z

In response to query on Article , how to make Field Case Insensitive for Query.
This can be done via forms personalization.

Interview Questions for Oracle iProcurement

2006-11-07T23:30:00.000Z

A new article has been added for Oracle iProcurement Interview questions.

It can be accessed from Link iProc Interview Questions .

New article on FNDLOAD for Web ADI

2006-11-07T22:45:00.000Z

Please find a new article that explains how to use FNDLOAD to move Web ADI setups from one environment to another.

Link for FNDLOAD of Web ADI

Update FND_USER and assign new Responsibility to users via scripts

2006-11-01T00:10:00.000Z

This article explains the below:-

1. Usage of API to update FND_USER record
2. Usage of API to assign responsibilities to a given FND_USER record

The APIs covered in this article are fnd_user_pkg & fnd_user_resp_groups_api

Article can be accessed from this link

Thanks,
Anil Passi

Override Email Address for Workflow Notifications

2006-10-31T00:18:00.000Z

The steps that required to override Workflow Notification Email Address are listed in article below.

This is of help when testing Oracle Workflows based applications, so that you can set yourself up to receive all email notifications on Oracle Apps Development or Test environment.

These steps were tried and tested on 11.5.10 environment.

Link for Article

My Oracle Apps Blogs :- Clarification

2006-10-07T03:24:00.000Z

Dear all,

Thanks for visiting this blog, without your feedbacks I will have no motivation to work upon the blog.

Appstechnical happens to be my first blog on Oracle Applications. During the past couple of months, I have received varying types of queries. Some people expect basic information, whereas others request for Advanced information. And some readers simply want to know whats happening in the Oracle Apps market place.

Hence to keep things simple, I will be running four blogs, each with a distinct purpose.

http://appstechnical.blogspot.com
This blog is for those who already know Oracle Applications ( Financials or HRMS suite)

http://oraclethoughts.blogspot.com
In this blog I will publish what is happening in the Oracle Apps world from a consultant point of view.

http://scripts4oracle.blogspot.com
The idea behind this blog is to keep the articles simple without dumping too much source code into an article. Hence, to support any article, the source code link will be provided via scripts4oracle blog.

http://getappstraining.blogspot.com
Last but not the least. I am amazed by the number of requests that I received whereby peoplel wish to learn Oracle Financials right from Scratch. This blog will be my prime focus during the coming months. Unlike appstechnical, this blog will be for starters of Oracle Applications.

Thanks,
Anil Passi

2006-10-05T23:23:00.000Z

Thoughts about TCA in R12 and Fusion

Yes, Oracle TCA is an excellent design. However it is not without any flaws. During my work experience with differnet clients, I have noted a few that I wish to share.

Here is the link for Oracle TCA vision in R12 & Fusion

Oracle Workflows Training Lesson Three

2006-09-30T23:22:00.000Z

Welcome to the lesson 3 of the Oracle Workflows Training.

One of the common business requests for Oracle Workflows has been to track the progress of Workflow at its various stages.
If your business user desires to track the progress of a Workflow, they can do so by viewing the workflow process diagram. However such approach has limitations related to attribute value security of the workflow. Also, that approach is not user friendly, as the workflow may contain various technical but important steps that can confuse end user.

Here is the deal, we will write a common workflow function, and re-use that workflow function at various stages of the workflow. The workflow function will be attached to a pl/sql procedure that will insert record into a progress table. User can then trace the progress either via a screen on that table or by some report.

The source code of Oracle Workflow Training Lesson 2 is being carried forward here.
Please click here if you wish to step back for Training Lesson 2 for Oracle Workflow.

This requirement will be accomplished in following steps:-
1. Create a table to track the progress.
2. Create a Workflow Function to pass parameters to indicate progress.
Note: For each parameter for Workflow function, we will create one function attribute.
3. Write the pl/sql function to insert record into progress table.

Below are steps for Oracle Workflow Training Lesson 3
Step 1. Open process “Main training process”, and create a new WF Function by right clicking the space between “Set Role” & “SET_VALIDATE_FLAG”

Step 2. Now create the function named LOG_STATUS_OF_ACTION. Assign it a function xxxx_po_wf_training_pkg.log_status_of_action

Make this function join to “Set Role” and “SET_VALIDATE_FLAG”

Step 3. In the Oracle Workflow Designer Navigator, as in picture below, right click on Function “Log Action Status for Progress”and select new attribute(as created in Step 2 above).

Step 4. Create attribute “ACTION_STAGE” ( Progress Action stage )

Step 5. Create attribute “ACTION_NAME” (Progress Action to indicate status)

Step 6. Drag the Attribute PO_ID from “XX Training Workflow” and drop that into function “Log Action Status for Progress”
As a result of above three steps, you will see something similar to below.
Screenshot after having created 3 Function Attributes
Screenshot of Oracle Workflow after having created 3 Function Attributes

Step 7. Now, navigate back to process “Main Training Process”. Double click on WF Function “Log Action Status for Progress”. Click on Tab “Node Attribute”
Select drop down list in section Attribute Name, select “Progress Action Stage”.
Effectively, here we will pass the parameters to the WF Function.
Attribute: Progress Action Stage
Type: Constant
Value: Validation
Click on Apply

Step 8.
Repeat the step 7 above for second attribute
Attribute: Progress Action to indicate status
Type: Constant
Value: Before Validation
Click On Apply

Step 9. Do nothing really, leave the PO_ID to its default value. Remember we had dragged this from WF level Attribute. Hence the function attribute will inherit value for PO_IF from Workflow Level.

Step 10. Right click between SET_VALIDATE_FLAG and “PO Notif Info”, to create a new function, but instead of creating new Function, simply pick the function created in “Step 2”. i.e. from drop down list for INTERNAL, select “LOG_STATUS_OF_ACTION”

Step 11. You will see the WF Diagram as below

Step 12.
Now repeat steps 7, 8 & 9.
However, the difference being that instead of Step 8, this time our values will be
Attribute: Progress Action to indicate status
Type: Constant
Value: After Validation
Image

Step 13. Create the procedure below in package “xxxx_po_wf_training_pkg”

PROCEDURE log_status_of_action(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
n_po_id INTEGER;
v_action_name VARCHAR2(200);
v_action_stage VARCHAR2(200);
BEGIN
IF funcmode = 'RUN'
THEN
--get the PO id, as it is after the hyphen of itemkey in this case
n_po_id := get_request_id_from_item_key(itemkey);

v_action_stage := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_STAGE');

v_action_name := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_NAME');
INSERT INTO xx_po_wf_progress
(po_id
,p_wf_action_stage
,p_wf_action_name)
VALUES
(n_po_id
,v_action_stage
,v_action_name);
END IF;
RESULT := 'COMPLETE:Y';
END log_status_of_action;

Note that the above pl/sql procedure simply reads the value from parameters passed under Node Attributes. Hence there is no hardcoding in the pl/sql.
By doing the above, you will be able to track the progress of each transaction in a very simple way.

Now for testing….
Step 14.
Save the wft file in database( Source code at end of this article)

Step 15. Initiate workflow as below….
DECLARE
n_new_po_id INTEGER;
BEGIN
SELECT oe_order_headers_s.NEXTVAL INTO n_new_po_id FROM dual;
INSERT INTO xxxx_po_headers
(po_id
,vendor_id
,suggested_vendor_id
,po_description
,po_status
,comments_from_approver
,send_email_to)
VALUES
(n_new_po_id --po_id
,10000 --vendor_id
,NULL --suggested_vendor_id
,'This is PO Training Description' --po_description
,'INITIAL' --po_status
,NULL --comments_from_approver
,' ANILPASSI@GMAIL.comThis email address is being protected from spam bots, you need Javascript enabled to view it ' --send_email_to
);
xxxx_po_wf_training_pkg.start_training_wf(p_po_id => n_new_po_id);
COMMIT;
END;

You will then see two records created as below.

The workflow progress will be visible as below

The source code for Training Lesson 3 is available here .

2006-09-26T23:57:00.000Z

Training Lesson 2 For Oracle Workflows

Lesson 2 of Oracle workflows training is now available. Thanks to all those who responded with their feedback for Workflow Training Lesson 1 (Audio Visual)

In this lesson we are going to learn:-
1. How to create new lookups in workflow.
2. How to provide a drop down list in notification to user. User will be select the response(approve or reject) from drop down list.
3. How to make a user enter rejection reason in free text response field within notification.
4. Validate that the value of rejection reason is not null, if user selects rejected. Effectivley this will train you on how to do validations on notification responses using pl/sql.

To access the second Lesson of Oracle Workflows Training please visit Oracle Workflow Training Lesson 2
Please feel free to either leave a comment or shoot an email to me. Kindly note that this lesson is in draft version, hence I am happy to add further explanations if need be.

Upcoming future lessons:

Workflow training Lesson 3.
How to make a generic step in oracle workflow, by passing parameters. This can be useful when you wish to display progress in some screen. This also happens to be a widely used concept in account genrators.

Workflow training lesson 4.
Raise event to execute an Oracle Workflow Process.

Workflow training lesson 5.
How to generate notifications from servlet styled pl/sql

If you would like to learn some other aspect of Oracle Workflow in Apps, please feel free to contact me.

2006-09-20T06:08:00.000Z

Oracle GL Interface API General Ledger

Please find an API for creating Journals in Oracle General Ledger ( GL ) .
I had developed this API in year 2002, and I have used this for three of my clients.

The idea behind this API is that you do not need to program for:-
1. Inserts to GL Interface ( GL_INTERFACE )
2. The validations required for Oracle GL Import
3. Submission of GL Import

Features of this Oracle GL Interface API are:-
1. It will ensure that the credit equals debit
2. It will submit GL Import when parameter p_submit_gl_interface is Y

For the source code for these API's click on the links below:-
GL Import API Package Header
GL Import API Package Body

Usage of this API
DECLARE
BEGIN
xx_import_xfer_gl_iface_api.transfer_to_gl
(p_sob_id => --Set of Books Id
,p_batch_header_id => --header id of your feed
,p_trx_header_id => --sub header id of your feed
--or leave this to be the same as above
,p_trx_line_id => --line id of your feed
,p_date => --effective date of the journal
,p_currency_code => --currency code of the journal
,p_amount => --journal line amount
,p_dr_account => --GL CCID of account to be credited
,p_cr_account => --GL CCID of account to be debited
,p_journal_name => --Journal Name to be created
,p_header_description => --Journal Description to be created
,p_line_description => --Journal Line descriptionn
,p_success_flag => b_success_flag--boolean returned to show if errored
,p_first_record_in_batch => v_first_record_in_batch --pass Y for First Record in batch
,p_submit_gl_interface => v_is_last_trx_line --Pass Y submit for last line of batch
--when Y it will submit the GL Import
,p_gl_user_je_source_name => --user_je_source_name
,p_gl_user_je_category_name => --user_je_category_name
,p_trx_number => --trx_number
);
END;

For any suggestions or questions, please post a comment

2006-09-17T17:41:00.000Z

Account Generator Workflow Example

This article illustrates the steps one needs to implement to build an Account Generator Oracle Workflow for eBusiness Suite. For this exercise I am using iProcurement & core Purchasing Account generation workflows. However, the concepts remain the same for all the account generator workflows, regardless of the module.

Assume that we have two Accounting Flexfield segments in Oracle General Ledger:-
1. Company Account
2. Cost Centre

Important: In real life, you will have more than mere two segments in the GL Accounting Flexfield. These pseudo Two GL Segments are for demo purposes only.
Assuming that you have implemented Oracle Projects Accounting, the Requisition or a Purchase Order can either be Project based or can be Non Project based. For this exercise, for simplicity, lets restrict ourselves to Project Based Requisition.

Lets assume that the business requirement for Project Based Requisitions is:-

Company Segment for Projects based Requisition
Company Account Segment will be Defaulted to a constant value of GO4GOLD

Cost Centre Segment for Projects based Requisition
This will be defaulted from the Attribute1 against Primary Organization for Project Task .

OK, here we go with all the steps that are required to build such an Account Generator Workflow.

Step 1
Open the account generator Workflow

Step 2.
Assign default value to Company segment.

Double click to open the process “Build Expense Project Charge Account”
After the Start Icon, Right Click to create a new Function.
As in screenshot below, in “Item Type” field, select “Standard Flexfield Workflow” from the drop down list. In the Display Name select “Assign Value to Segment” and click on OK.

By doing the above step, we have just created a workflow Function that is capable of assigning a desired value to GL Flexfield Segment. Now we need to configure this Function further, so that it knows the Segment upon which it operates, and also the default value that is assigned to the segment.
As per our business requirement, we need to default a constant value of GO4GOLD for our Segment1 ( Company Segment ) . For this, click on the function created above, and click on the Node Attributes.

Do the steps, as in above screenshot.

Step 3 ( Part a).
Default the cost centre from Attribute1 against Primary Organization for Project Task. In the Part-a of this step, we will assign the Cost Centre to an Attribute Named “TEMP_CC_VALUE”.

For this, we can define a new function named “Assign Cost Centre to Attribute”, by right clicking on the process.

This function calls PL/SQL XX_account_generator_pkg.set_cost_centre_attribute

PROCEDURE set_cost_centre_attribute
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT OUT VARCHAR2
) AS
CURSOR c_get_cc(p_org_id IN NUMBER) IS
SELECT attribute1 org_cc
FROM hr_all_organization_units hrou
WHERE hrou.organization_id = p_org_id;
rec_get_cc c_get_cc%ROWTYPE;
BEGIN
IF NOT (funcmode = 'RUN')
THEN
RETURN;
END IF;
BEGIN
--First nullify the value, so that accidentaly it does not get used in the next step.
wf_engine.setitemattrtext(itemtype, itemkey, 'TEMP_CC_VALUE', '');
OPEN c_get_cc(wf_engine.getactivityattrnumber(itemtype
,itemkey
,actid
,'EXPENDITURE_ORGANIZATION_ID'));
FETCH c_get_cc
INTO rec_get_cc;
IF c_get_cc%NOTFOUND
THEN
RESULT := 'COMPLETE:FAILURE';
ELSE
wf_engine.setitemattrtext(itemtype
,itemkey
,'TEMP_CC_VALUE'
,rec_get_cc.org_cc);
RESULT := 'COMPLETE:SUCCESS';
END IF;
CLOSE c_get_cc;
RETURN;
END;
EXCEPTION
WHEN OTHERS THEN
-- Record error using generic error message routine for debugging and
-- raise it
wf_core.CONTEXT(pkg_name => 'ic_account_generator_pkg'
,proc_name => 'GMS_GET_CC'
,arg5 => NULL);
RAISE;
END set_cost_centre_attribute;

In the above function, we are fetching the Attribute1 against the Organization Record. The organization_id is being passed as a parameter to the Newly defined Function( as in screenshot below).

Step 3 ( Part b).
In this part of the step, we will assign the value in Attribute “TEMP_CC_VALUE” to the Segment2 ( i.e. the Cost Centre). This is done as per the screenshot below.

Step 4. Check to see if the Code Combination is Complete

Right click to create a function. From Item Type dropdown list, select “Standard Flexfield Workflow”. In the Display Name dropdown list, select “Is Code Combination Complete?”

Next, click on Node Attributes, and assign a value of TRUE to “Check only required Segments”.

Step 5.
By implementing the above steps, we have been able to complete the Account Generation of our two-segment Flexfield using Oracle Workflow Account Generator.

The last and final step will already be there within the Pre-Installed Account Generation. This workflow step validates the Code Combination Id, for the Oracle’s Security Rules that are defined in General Ledger, against users responsibility. For reference purposes this is pasted below.

A final note, for debugging the Account Generator workflow. Account Generator workflows are designed to run efficiently and quickly, hence they always in synchronous mode. This means that no inserts are made into the Oracle Workflow tables when generating the accounting.
Hence by default it is not possible to debug the Account Generator workflow using Status Monitor screen in Oracle Workflow.
However, Oracle delivers a profile option named “Account Generator:Run in Debug Mode”. Setting this profile to Yes, will make Account Genration processes insert records in workflow tables. Hence by setting this profile to Yes, you will be able to debug your account generation workflow. Further debugging of FND_FLEX API’s is made possible by enabling the FND Logging.

2006-09-16T19:21:00.000Z

Apps Technical Interview Questions

Please note that most of the Oracle apps interview questions listed here are technical in nature. These interview questions span various Oracle Apps modules and also FND & OA Framework. For Oracle HRMS and Payroll interview questions please visit HRMS Payroll Interview Questions .

Question: Does Oracle 10g support rule based optimization?
Answer: The official stance is that RBO is no longer supported by 10g.

Question: Does oracle support partitioning of tables in Oracle Apps?
Answer: Yes, Oracle does support partitioning of tables in Oracle Applications. There are several implementations that partition on GL_BALANCES. However your client must buy licenses to if they desire to partition tables. To avoid the cost of licensing you may suggest the clients may decide to permanently close their older GL Periods, such that historical records can be archived.
Note: Before running the archival process the second time, you must clear down the archive table GL_ARCHIVE_BALANCES (don’t forget to export archive data to a tape).

Question: What will be your partitioning strategy on GL_BALANCES? Your views please?
Answer: This really depends upon how many periods are regularly reported upon, how many periods are left open etc. You can then decide to partition on period_name, or period ranges, or on the status of the GL Period.

Question: Does Oracle support running of gather stats on SYS schema in Oracle Apps?
Answer: If your Oracle Applications instance is on 10g, then you can decide to run stats for SYS schema. This can be done by exec dbms_stats.delete_schema_stats('SYS');
Alternately using command dbms_stats.gather_schema_stats('SYS',cascade=>TRUE,degree=>20);
I will prefer the former with default values.
If you wish to delete the stats for SYS use exec dbms_stats.gather_schema_stats('SYS');
You can schedule a dbms_job for running stats for SYS schema.

Question: Can you use concurrent program "Gather Schema Statistics" to gather stats on sys schema in oracle apps?
Answer: No, "Gather Schema Statistics" has no parameters for SYS schema. Please use dbms_job.

Question: Which table is used to provide drill down from Oracle GL into sub-ledger?
Answer: GL_IMPORT_REFERENCES

Question: What is the significance of profile option “Node Trust Level” in Oracle Apps.
Answer: If this profile option is set to a value of external against a server, then it signifies that the specific mid-tier is External i.e. it will be exposed to the www. In other words this server is not within the firewall of your client. The idea behind this profile option is to flag such middle-tier so that special restrictions can be applied against its security, which means a very restricted set of responsibilities will be available from such Middle-Tier.

Question: What is the significance of profile option “Responsibility Trust Level”.
Answer: In order to make a responsibility accessible from an external web tier, you must set profile option “Responsibility Trust Level” at responsibility level to “External”. Only those responsibilities that have this profile option against them will be accessible from External Middle tiers.

Question: What else can you suggest to restrict the access to screens from external web tiers?
Answer: You may use URL filtering within Apache.

Question: What is the role of Document Manager in Oracle Purchasing?
Answer: POXCON is an immediate concurrent program. It receives pipe signal from the application when a request is made for approval/reservations/receipts.

Question: How to debug a document manager in Oracle Apps?
Answer: Document manger runs within the concurrent manager in Oracle Applications. When an application uses a Document Manager, it sends a pipe signal which is picked up by the document manager.
There are two mechanisms by which to trace the document manager
1. Set the debugging on by using profile option
STEP 1. Set profile option "Concurrent:Debug Flags" to TCTM1
This profile should only generate debugs when set at Site level(I think, as I have only tried site), because Document Manager runs in a different session.
STEP 2. Bounce the Document Managers
STEP 3. Retry the Workflow to generate debugs.
STEP 4. Reset profile option "Concurrent:Debug Flags" to blank
STEP 5. have a look at debug information in table fnd_concurrent_debug_info

2. Enable tracing for the document managers
This can be done by setting profile option “Initialization SQL Statement – Custom” against your username before reproducing the issue. The value of this profile will be set so as to enable trace using event 10046, level 12.

Question: You have written a Java Concurrent Program in Oracle Apps. You want to modify the CLASSPATH such that new class CLASSPATH is effective just for this program.
Answer: In the options field of the concurrent program you can enter something similar to below.
-cp <your custom lib pathused by Java Conc Prog> :/home/xxvisiondev/XXDEVDB/comn/java/appsborg.zip:/home/xxvisiondev/XXDEVDB/comn/java

Question: In self service, you wish to change the background color and the foreground text of the OA Framework screens to meet your corporate standards. How will you do it?
Answer: You will need to do the below steps
a….Go to Mid Tier, and open $OA_HTML/cabo/styles/custom.xss
b…Enter below text( change colours as needed)

<style name="DarkBackground">
<property name="background-color">#000066</property>
</style>
<style name="TextForeground">
<property name="color">#0000FF</property>
</style>
c… cd $OA_HTML/cabo/styles/cache
d…Take a backup of all the css files.
e…Delete all the files of following pattern oracle-desktop*.css
The idea here is to delete the cache. Next time when you logon to Oracle Apps Self Service, the Framework will rebuild the css file if found missing for your browser.

Question: In OA Framework Self-Service screen, you wish to disable a tab. How will you do it?
Answer: Generally speaking, the tabs on a OA Framework page are nothing but the SubMenus. By entering menu exclusion against the responsibility, you can remove the tab from self service page.

Question: How will you open a bc4j package in jdeveloper?
Answer: Oracle ships a file named server.xml with each bc4j package. You will need to ftp that file alongside other bc4j objects(VO’s, EO’s, AM, Classes etc).
Opening the server.xml will load the complete package starting from AM(application module). This is a mandatory step when building Extensions to framework.

Question: Can you extend and substitue a root AM ( Application Module) in OA Framework using JDeveloper.
Answer: You can extend the AM in jDeveloper, but it doesn’t work( at least it didn’t work in 11.5.9). I am hopeful that Oracle will deliver a solution to this in the future.

Question: In a workflow notification, you have a free text response field where the user enters the Vendor Number for the new vendor. You want to validate the value entered in the notification response field upon the submission of a response. How will you do it?
Answer: You will need to attach a post notification function to the Workflow Notification.
The PL/SQL code will look similar to below:-
The below code will display an error in the notification when user attempts to create a Duplicate Vendor Number.
PROCEDURE validate_response_from_notif
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
) IS
l_nid NUMBER;
l_activity_result_code VARCHAR2(200);
v_newly_entered_vendor_num VARCHAR2(50);
CURSOR c_get_response_for_new_vendor IS
SELECT wl.lookup_code
FROM wf_notification_attributes wna
,wf_notifications wn
,wf_message_attributes_vl wma
,wf_lookups wl
WHERE wna.notification_id = l_nid
AND wna.notification_id = wn.notification_id
AND wn.message_name = wma.message_name
AND wn.message_type = wma.message_type
AND wna.NAME = wma.NAME
AND wma.SUBTYPE = 'RESPOND'
AND wma.format = wl.lookup_type
AND wna.text_value = wl.lookup_code
AND wma.TYPE = 'LOOKUP'
AND decode(wma.NAME, 'RESULT', 'RESULT', 'NORESULT') = 'RESULT';
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
OPEN c_get_response_for_new_vendor;
FETCH c_get_response_for_new_vendor
INTO l_activity_result_code;
CLOSE c_get_response_for_new_vendor;
v_newly_entered_vendor_num := wf_notification.getattrtext(l_nid,'NEWLY_ENTERED_VENDOR_NUM_4_PO');
IF l_activity_result_code = 'NEW_VENDOR'
AND does_vendor_exist(p_vendor => v_newly_entered_vendor_num)
THEN
RESULT := 'ERROR: VendorNumber you entered already exists';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END validate_response_from_notif;

Question: How to make concurrent program end with warning?
Answer: If the concurrent program is of type PL/SQL, you can assign a value of 1 to the “retcode” OUT Parameter.
For a Java Concurrent program, use the code similar to below
ReqCompletion lRC;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
lRC.setCompletion(ReqCompletion.WARNING, "WARNING");

Question: How do you link a Host type concurrent program to Concurrent Manager?
Answer: Assuming your executable script is LOADPO.prog, then use the commands below
cd $XXPO_TOP/bin
ln -s $FND_TOP/bin/fndcpesr $XXPO_TOP/bin/LOADPO

Question: How do you know if a specific Oracle patch has been applied in apps to your environment.
Answer: Use table ad_bugs, in which column bug_number is the patch number.
SELECT bug_number
,to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS') dated
FROM apps.ad_bugs
WHERE bug_number = TRIM('&bug_number') ;

Question: How do you send a particular Oracle Apps Workflow Activity/Function within a workflow process into background mode.
Answer: If cost of the workflow activity is greater than 50, then the workflow activity will be processed in background mode only, and it won’t be processed in online mode.

Question: What are the various ways to kick-off a workflow
Answer: You can eiter use wf_engine.start_process or you can attach a runnable process such ghat it subscribes to a workflow event.

Question: When starting (kicking off) an oracle workflow process, how do you ensure that it happens in a background mode?
Answer:
--a)if initiating the process using start_process, do the below
wf_engine.threshold := -1;
wf_engine.createprocess(l_itemtype,l_itemkey);
wf_engine.startprocess(l_itemtype, l_itemkey)
--B) When initiating the workflow process through an event subscription, set the Execution Condition Phase to be equal to or above 100 for it to be executed by background process.

Question :On 10g, how will you use awr?
Answer: By running below scripts. These are both the same scripts, but with differing parameters.
$ORACLE_HOME/rdbms/admin/awrrpt.sql
$ORACLE_HOME/rdbms/admin/awrrpti.sql

Question : How will you configure Apache to run in Debug mode, specifically usefull when debugging iProcurement ( prior to 11.5.10).
Answer: After 11.5.10, FND Logging can be used for debugging Oracle iProcurement.
Prior to 11.5.10
----STEPS IN A NUTSHELL-----
cd $ORACLE_HOME/../iAS/Apache
vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/ssp_init.txt
DebugOutput=/home//ora9/iAS/Apache/Apache/logs/debug.log
DebugLevel=5
DebugSwitch=ON

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.conf
ApJServLogLevel debug

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.properties
log=true

Question: How will you add a new column to a List Of Values ( LOV ) in Oracle Applications Framework? Can this be done without customization?
Answer: Yes, this can be done without customization, i.e. by using OA Framework Extension coupled with Personalization. Implement the following Steps :-
a) Extend the VO ( View Object ), to implement the new SQL required to support the LOV.
b) Substitute the base VO, by using jpximport [ similar to as explained in Link ]
c) Personalize the LOV Region, by clicking on Add New Item. While adding the new Item, you will cross reference the newly added column to VO.

Question: Can you do fnd_request.submit_request from SQL Plus in Oracle?
Answer: You will need to initialize the global variables first using fnd_global.initialize
DECLARE
v_session_id INTEGER := userenv('sessionid') ;
BEGIN
fnd_global.initialize
(
SESSION_ID => v_session_id
,USER_ID => "your user id from fnd_user.user_id"
,RESP_ID => "You may use Examine from the screen PROFILE/RESP_ID"
,RESP_APPL_ID => "You may use Examine from the screen PROFILE/RESP_APPL_ID"
,SECURITY_GROUP_ID => 0
,SITE_ID => NULL
,LOGIN_ID => 3115003--Any number here
,CONC_LOGIN_ID => NULL
,PROG_APPL_ID => NULL
,CONC_PROGRAM_ID => NULL
,CONC_REQUEST_ID => NULL
,CONC_PRIORITY_REQUEST => NULL
) ;
commit ;
END ;
/

Optionally you may use fnd_global.apps_initialize, which internally calls fnd_global.initialize
fnd_global.apps_initialize(user_id => :user_id,
resp_id => :resp_id,
resp_appl_id => :resp_appl_id,
security_group_id => :security_group_id,
server_id => :server_id);

By doing the above, your global variables upon which Concurrent Managers depend upon will be populated. This will be equivalent to logging into Oracle Apps and submitting the concurrent request from a responsibility.

Question: You are told that the certain steps in the Oracle Apps Form/Screen are running slow, and you are asked to tune it. How do you go about it.
Answer: First thing to do is to enable trace. Preferably, enable the trace with Bind Variables. This can be done by selecting menu Help/Diagnostics/Trace/”Trace With Binds and Wait”
Internally Oracle Forms issues a statement similar to below:-
alter session set events='10046 trace name context forever, level 12' ;

This will enable the trace with Bind Variable values being shown in the trace file.
The screen in Oracle Apps will also provide the name of the trace file which is located in directly identified by
select value from v$parameter where name like '%us%r%dump%'
Doing a tkprof with explain plan option, reviewing plans and stats in trace file can help identify the slow performing SQL.

Question: What is the difference between running Gather Stats and “Program – Optimizer[RGOPTM]” in Oracle General Ledger?
Answer: “Gather Stats” will simply gather the stats against existing tables, indexes etc. However Gather Stats does not create any new indexes. But “Program – Optimizer[RGOPTM]” can create indexes on GL_CODE_COMBINATIONS, provided accounting segment has the indexed flag enabled,

Question: You have written a piece of code in POR_CUSTOM_PKG for Oracle iProcurement, but its not taking any effect? What may be the reason?
Answer: Depending upon which procedure in POR_CUSTOM_PKG has been programmed, one or more of the below profile options must be set to Yes
POR: Enable Req Header Customization
POR: Enable Requisition Line Customization
POR: Enable Req Distribution Customization

Question: What is the key benefit of punching out to suppliers catalogs rather than loading their catalogs locally in Oracle iProcurement?
Answer: Punchout has several advantages like, Catalogs don’t need to be loaded locally saves space on your system. You can get up-to-date list of catalogs by punching out and also you get the benefit of up-to-date pricing information on vendor items.

Question: Does oracle have a test environment on exchange?
Answer: http://testexchange.oracle.com

Question: Does Oracle Grants use its own schema or does it uses Oracle Project Accounting schema?
Answer: Although Oracle Grants has its own schema i.e. GMS, it reuses many of the tables with in Oracle Projects Schema like PA_PROJECTS_ALL, PA_EXPENDITURE_ITEMS_ALL, PA_EXPENDITURE_TYPES etc.

Question: How to make an Oracle Report Type concurrent program produce an excel friendly output?
Answer: Comma can be concatenated between the column values, however a better option is to create tab delimited file, as it takes care of commas within the string.
For this, use SQL similar to below in the report
select 'a' || chr(9) || 'b' from dual;

Question: What are the settings needed for printing bitmap reports?
Answer: Get your DBA to configure two files i.e. uiprint.txt & default.ppd
For details, refer to Metalink Note 189708.1

Question: For a PL/SQL based concurrent program do you have to issue a commit at the end?
Answer: The concurrent program runs within its own new session. In APPS, the default database setting enforces a commit at the end of each session. Hence no explicit COMMIT is required.

Question: What is the best way to add debugging to the code in apps?
Answer: Use fnd_log.string , i.e. FND Logging. Behind the scenes Oracles FND Logging uses autonomous transaction to insert records in a table named fnd_log_messages.
For example
DECLARE
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => 'xxxx ' || 'pkg/procedurename '
,message => 'your debug message here');
END ;

Three profile options effecting FND Logging are
FND: Debug Log Mode FND: Debug Log Enabled FND: Debug Log Module

Question: If you wish to trigger of an update or insert in bespoke table or take some action in response to a TCA record being created or modified, how would you do it? Will you write a database triggers on TCA Tables?
Answer: There are various pre-defined Events that are invoked from the Oracle TCA API’s.
TCA was Oracle’s first initiative towards a fully API based approach, which means the screen and the processes all use the same set of APIs for doing same task.
In order to take an action when these events occur, you can subscribe a custom PL/SQL procedure or a Custom Workflow to these events. Some of the important TCA events are listed below:-
oracle.apps.ar.hz.ContactPoint.update oracle.apps.ar.hz.CustAccount.create oracle.apps.ar.hz.CustAccount.update oracle.apps.ar.hz.CustAcctSite.create oracle.apps.ar.hz.CustAcctSite.update oracle.apps.ar.hz.CustAcctSiteUse.create oracle.apps.ar.hz.CustAcctSiteUse.update oracle.apps.ar.hz.Location.create oracle.apps.ar.hz.Location.update oracle.apps.ar.hz.Organization.create oracle.apps.ar.hz.Organization.update oracle.apps.ar.hz.PartySite.create oracle.apps.ar.hz.PartySite.update oracle.apps.ar.hz.PartySiteUse.create oracle.apps.ar.hz.PartySiteUse.update oracle.apps.ar.hz.Person.create

Question: In Oracle OA Framework, is the MDS page/document definition stored in database or in the file system?
Answer: The MDS document details are loaded into database, in the following sets of tables.
JDR_ATTRIBUTES
JDR_ATTRIBUTES_TRANS
JDR_COMPONENTS
JDR_PATHS
The Document is loaded via XMLImporter, as detailed in link

Question: In a Oracle Report data group, you have a “data link” between two queries. How do you ensure that the data link is made Outer Joined?
Answer: The data link is an Outer Join by default.

Question: How does substitution work in OA Framework?
What are the benefits of using Substitution in OA Framework?
Answer: Based on the user that has logged into OA Framework, MDS defines the context of the logged in user. Based upon this logged in context, all applicable personalization are applied by MDS. Given that substitutions are loaded as site level personalizations, MDS applies the
substituted BC4J objects along with the personalizations. The above listed steps occur as soon as Root Application module has been loaded.
The benefit of using Substitution is to extend the OA Framework without customization of the underlying code. This is of great help during Upgrades. Entity Objects and Validation Objects can be substituted. I think Root AM’s can’t be substituted given that substitution kicks off after Root AM gets loaded.

Question: In OA Framework, once your application has been extended by substitutions, is it possible to revert back to remove those substitutions?
Answer: Yes, by setting profile option “Disable Self-Service Personal%” to Yes, keeping in mind that all your personalizations will get disabled by this profile option.
This profile is also very useful when debugging your OA Framework based application in the event of some error. By disabling the personalization via profile, you can isolate the error, i.e. is being caused by your extension/substitution code or by Oracle’s standard functionality.

Question: How can you import invoices into Oracle Receivables?
Answer: You can either use AutoInvoice by populating tables RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL & RA_INTERFACE_SALESCREDITS_ALL.
Alternately you may decide to use API ar_invoice_api_pub.create_single_invoice
for Receivables Invoice Import.

Question: How do you setup a context sensitive flexfield
Answer: Note: I will publish a white paper to sho step by step approach.
But for the purpose of your interview, a brief explanation is…a)Create a reference field, b) Use that reference field in “Context Field” section of DFF Segment screen c) For each possible value of the context field, you will need to create one record in section “Context Field Value” ( beneath the global data elements).

Question: Does Oracle iProcurement use same tables as Oracle Purchasing?
Answer: Yes, iProcurement uses the same set of requisition tables as are used by Core Purchasing.

Question: What is the name of the schema for tables in tca
Answer: AR (at least till 11.5.10, not sure about 11.5.10).

Question: Are suppliers a part of TCA?
Answer: Unfortunately not yet. However, Release 12 will be merging Suppliers into TCA.

Question: What is the link between order management and purchasing
Answer: Internal Requisitions get translated into Internal Sales Orders.

Question: How would you know if the purchase order XML has been transmitted to vendor, looking at the tables.
Answer: The XML delivery status can be found from a table named ecx_oxta_logmsg. Use the query below
SELECT edoc.document_number
,decode(eol.result_code, 1000, 'Success', 'Failure') AS status
,eol.result_text
FROM ecx_oxta_logmsg eol
,ecx_doclogs edoc
,ecx_outbound_logs eog
WHERE edoc.msgid = eol.sender_message_id
AND eog.out_msgid = edoc.msgid
ORDER BY edoc.document_number

Question: You have done forms personalization, now how will you move it from one environment to another?
Answer: Use FNDLOAD. For examples visit the link FNDLOAD

Question: What are the key benefits of forms personalization over custom.pll?
Answer:
-->Multiple users can develop forms personalization at any given point in time.
-->It is fairly easy to enable and disable forms personalizations.
-->A programmer is not required to do simple things such as hide/disable fields or buttons.
-->Provides more visibility on customizations to the screen.

Question: Tell me some limitations of forms personalization when compared to CUSTOM.pll?
Answer:
-->Can't create record group queries, hence can’t implement LOV Query changes.
-->Can't make things interactive, i.e. can’t have a message box that gives multiple choices for example Proceed or Stop etc.

Question: Give me one example where apps uses partitioning?
Answer: WF_LOCAL_ROLES

Question: Give me one example of securing attributes in iProcurement.
Answer: You can define Realm to bundle suppliers into a Category. Such realm can then be assigned to the User using Define User Screen. Security Attribute ICX_POR_REALM_ID can be used. By doing so, the user will only be made visible those Punchout suppliers that belong to the realm against their securing attributes.

Question: Can you send blob attachments via workflow notifications?
Answer: Yes, you can send BLOB Attachments.
For details on how to do this, refer to link BLOBS In Workflow Notifications

2006-09-12T00:46:00.000Z

Training Lesson 1 for Oracle Workflows

Please find an Audio Visual Oracle Workflow Training lesson, which will be the pre-requisite for the next stream of lessons on Oracle Workflows Training.

Oracle Workflow is a copyright product from Oracle Corp. This training session is for educational purposes only.

Following steps will be covered in the first lesson of Oracle Workflow Training:-
1. How to design & create a Workflow using Oracle Workflow Builder
2. How to start the Oracle Workflow process from pl/sql
3. How to integrate Oracle Workflow with pl/sql for validation and DML etc.
4. How to build Oracle Workflow Notifications
5. How to attach roles/people to notifications in Oracle Workflow

For this Oracle Workflow Training session, lets assume that the requirement is to initiate a workflow, validate it within Oracle Workflow & PL/SQL and then to send a notification.

Please click here for the Audio-Visual Lesson 1 in Oracle Workflows Training
Oracle Workflows Training Lesson 1

2006-09-07T00:31:00.000Z

Forms Personalization in Oracle Apps 11.5.10

>
My first article in the series of Forms Personalization is in response to a question raised in Oracle Forum under Oracle Human Resources (HRMS ). As per the Oracle forum request, If the Person Type is Employee, their clients wants Person Title field to become Mandatory ( lets assume it is the title field for now). When the Person Type field changes to a value that is anything but Employee, the person title field should then toggle back to become optional.

Please note that when Person type Employee is selected, value in field
PERSON.D_PTU_USER_PERSON_TYPE is assigned a value of “Employee”

Now the requirement is that for “Employee” field PERSON.D_TITLE must be made mandatory.

There are two possible ways the Person Type can change.
Either by picking a dropdown list of Action (e.g. Create Employee) or by directly picking up a value from LOV on field “Person Type for Action”. Whenever the person type changes, WHEN-NEW-ITEM-INSTANCE is fired for one for the below fields(depending upon how its changed). Hence forms personalization must check conditions for below three fields
PERWSHRG.PERSON.PTU_ACTION_TYPE
PERWSHRG.PERSON.D_PTU_USER_PERSON_TYPE
PERWSHRG.PERSON.SHOW_NUMBER

The demo below contains conditional check on “WHEN-NEW-ITEM-INSTANCE” of PERSON.PTU_ACTION_TYPE

When implementing this, you will have to replicate the steps in the demo for WNII on both PERWSHRG.PERSON.D_PTU_USER_PERSON_TYPE & PERWSHRG.PERSON.SHOW_NUMBER

I have tested the steps below myself, and they appear to work.

STEP 1.
Create Personalization as below( to make Title field mandatory)
Sequence: 50
Description: Make Person Title Mandatory when Person Type is Employee.
Trigger Event: WHEN-NEW-ITEM-INSTANCE
Trigger Object: PERSON.PTU_ACTION_TYPE
Condition: ${item.person.d_ptu_user_person_type.value} = 'Employee'

Action Sequence: 10
Action Type: Property
Action Object Type: Item
Action Target Object: PERSON.D_TITLE
Action Property Name: REQUIRED
Action Value: TRUE

STEP 2
Create another Personalization as below ( to make Title field Optional)
Sequence: 51
Description: Make Person Title Mandatory when Person Type is Employee.
Trigger Event: WHEN-NEW-ITEM-INSTANCE
Trigger Object: PERSON.PTU_ACTION_TYPE
Condition: NVL(${item.person.d_ptu_user_person_type.value},'xxyyzz') != 'Employee'

Action Sequence: 10
Action Type: Property
Action Object Type: Item
Action Target Object: PERSON.D_TITLE
Action Property Name: REQUIRED
Action Value: FALSE

2006-09-06T00:58:00.000Z

ReBuild Account in Oracle iProcurement

Note: If using Firefox, for this article use the link iProc Rebuild Account

Let us assume you have a requirement to make the Account Generator in Oracle iProcurement (11.5.10) rebuild accounts whenever there is a change in Requisition Line Descriptive Flex Fields Attribute5 or Attribute6 or both.

Until 11.5.9, you will be implementing this by placing some code in por_custom_pkg.set_rebuild_dist_value
The code will look similar to below( although might require slight tweak as per your requirements):-

BEGIN

x_return_code := 0;

x_error_msg   := '
';

/* apassi Jan 2004. If the DFF value has been changed then we must

rebuild the Code Combination taking into consideration all the segments */

END IF;

IF nvl(line_att5, 'XXX')
!= nvl(attribute8, 'XXX')

THEN

    returnvalue := 'Y';

END IF;

IF nvl(line_att6, 'XXX')
!= nvl(attribute9, 'XXX')

THEN

    returnvalue := 'Y';

END IF;

END;

It looks quite simple, ain’t it? Indeed it is quite simple, all you require is the above few lines of code in por_custom_pkg, following which changes to your Line Descriptive Flex 5 & 6 will re-initiate Proc Account Generator workflow.

Well, welcome 11.5.10 !!
The steps in 11.5.10 will be as below( although each step is visually explained in this article with further breakdowns)
a. Extend the Entity Object that contains these attributes.
b. The Extended EO will introduce business attribute properties for these DFF Attributes. This will tell the Oracle iProc engine to regenerate the accounts.
c. Substitute the standard EO by Extended EO
d. Bounce the entire middle tier, try your luck by merely bouncing the Apache.

OK, here with go into the details of each step. These steps are documented in Oracle iProc Manual too, but in a language that can only be understood by Experienced Oracle guys. Hence this article is an effort from me to explain the steps in naïve manner.

Step 1.
FTP and bring all the iProc BC4J classes and XML files onto your PC. Ensure that the location of those packages is included in the CLASSPATH that is defined against your JDeveloper Project. You will need to import server.xml from respective package to open up the BC4J in jDeveloper.

Step 2
Extend the Entity Object PoRequisitionLineEO to create a new entity object named xxxxPoRequisitionLineEO.
Select the BC4J Package that contains the EO that you need to extend. In my case I will select oracle.apps.icx.por.schema.server and right click to see the option for “New Entity Object” as below

Now define a new Entity Object xxxxPoRequisitionLineEO. Move the cursor to Extends Entity and click on button Browse to select PoRequisitionLineEO

Now click on Next, until you see the New from Table button as below. Click on that “New from Table” button.
As in the picture below, select Attribute5 and Attribute6 ( or the appropriate attributes in your case).

Now, your EO will look somewhat similar to below.

Please note that the schema object is PO_REQUISITION_LINES_ALL, simply because in this example, we wish to rebuild the iProcurement Account whenever a change is made to certain attributes in Requisition Line DFF.

Step 3
One of the most important steps is to define these attributes as Business Attributes, to help re-initiate account generator workflow.

Select on Tab “Attribute Properties”

As in above picture, Name=AccountLineBizAttrSet
Value=Y ……click on Add

Step 4
Once having created the Extended Entity Object, next we need to create a Substitution such that our extended EO xxxxPoRequisitionLineEO will replace the standard EO PoRequisitionLineEO

For doing so, right click on the jpx file within which you had opened iProc’s BC4J packages(this was done by importing server.xml initially). Once you right click jpx file, you will see an option “Edit Business Components Project”.

Following this, select “Substitution” option as in the list below

Now, finally you can implement substitution. In the left hand pane select PoRequisitionLineEO under the package oracle.apps.icx.por.schema.server. In the right hand side pane, select xxxxPoRequisitionLineEO from your appropriate package.

Now, this substitution will create a .jpx file for you. In my case, I will name it xxicIcxPorAccReBuild.jpx

This file will look like below

######## Begin xxicIcxPorAccReBuild.jpx #######

<?xml version="1.0" encoding='windows-1252'?>

<!DOCTYPE JboProject SYSTEM "jbo_03_01.dtd">

<JboProject

Name="xxxxIcxPorAccReBuild"

SeparateXMLFiles="true"

PackageName="" >

<DesignTime>

<Attr Name="_version" Value="9.0.3.13.75" />

<Attr Name="_jprName" Value="xxxxIcxPorAccReBuild.jpr" />

<Attr Name="_ejbPackage" Value="false" />

</DesignTime>

<Substitutes>

<Substitute OldName ="oracle.apps.icx.por.schema.server.PoRequisitionLineEO" NewName ="oracle.apps.icx.por.schema.server.xxxxPoRequisitionLineEO" />

</Substitutes>

</JboProject>

######## End xxicIcxPorAccReBuild.jpx #######

Now, to move this .jpx file from one environment to another( to move the substitution definition)
you can either use personalization or you may use the script below

######################Script to load substitution definition to new environment######################

##By Anil Passi

##Please note that this can very well be done by simply exporting and importing the personalizations
too.

##This is so, because the Substitution is stored as a personalization in Oracle.

##Anyhow, because I like to experiment, i prefered using JPXImporter, and it worked

MACHINE_NAME=0

PORT_NUMBER=0

export MACHINE_NAME

export PORT_NUMBER

getPortMachine()

{

MACHINE_NAME=`sqlplus -s apps/$APPS_PWD <<EOF

set pages 0

set lines 1023

select fnd_profile.value('CSF_EMAP_DS_HOST') from dual ;

exit

EOF`

PORT_NUMBER=`sqlplus -s apps/$APPS_PWD <<EOF

set pages 0

set feed off

select fnd_profile.value('CSF_MAP_DB_PORT') from dual ;

exit

EOF`

}

getPortMachine

echo "**********************************"

echo $MACHINE_NAME is Machine Name

echo "**********************************"

sleep 5

echo $PORT_NUMBER is port number

echo "**********************************"

sleep 5

"Echo load substitution"

adjava oracle.jrad.tools.xml.importer.JPXImporter $XXPO_TOP/bin/xxxxIcxPorAccReBuild.jpx -username
apps -password $APPS_PWD -dbconnection "(description=(address_list=(address=(community=tcp.world)(protocol=tcp)(host=$MACHINE_NAME)(port=$PORT_NUMBER)))(connect_data=(sid=$TWO_TASK)))"

######################END OF Script to load substitution ######################

Step 5.

Hang on, not there as yet. You need to deploy the new EO and its implementation on the Unix
server under appropriate directory structure.

This will involve three files, i.e. xxxxPoRequisitionLineEO.xml, xxxxPoRequisitionLineEOImpl.java & xxxxPoRequisitionLineEOImpl.class

Please note that it is not mandatory to deploy the .java file, but I recommend this to be a
good practice.

xxxxPoRequisitionLineEO.xml will contain the EO definition and this file is generated by jDeveloper
to hold meta info of extended EO.

xxxxPoRequisitionLineEOImpl will generate by your favourite jDeveloper too.

If you feel confident, the entire Rebuilding functionality can be implemented without touching
jDeveloper, but for that you will be directly modifying the files that I have pasted below.
In my personal opinion use jDeveloper.

######### Beginning of xxxxPoRequisitionLineEOImpl.java#########

package oracle.apps.icx.por.schema.server;

import oracle.jbo.server.EntityDefImpl;

import oracle.jbo.server.AttributeDefImpl;

import oracle.jbo.Key;

// ---------------------------------------------------------------

// --- Anil Passi iProc Rebuild account.

// ---------------------------------------------------------------

public class xxxxPoRequisitionLineEOImpl extends PoRequisitionLineEOImpl

{

protected static final int MAXATTRCONST = EntityDefImpl.getMaxAttrConst("oracle.apps.icx.por.schema.server.PoRequisitionLineEO");

private static oracle.apps.fnd.framework.server.OAEntityDefImpl mDefinitionObject;

/**

*

* This is the default constructor (do not remove)

*/

public xxxxPoRequisitionLineEOImpl()

{

}

/**

*

* Retrieves the definition object for this instance class.

*/

public static synchronized EntityDefImpl getDefinitionObject()

{

if (mDefinitionObject == null)

{

mDefinitionObject = (oracle.apps.fnd.framework.server.OAEntityDefImpl)EntityDefImpl.findDefObject("oracle.apps.icx.por.schema.server.xxxxPoRequisitionLineEO");

}

return mDefinitionObject;

}

// Generated method. Do not modify.

protected Object getAttrInvokeAccessor(int index, AttributeDefImpl attrDef) throws Exception

{

if (index == ATTRIBUTE5)

{

return getAttribute5();

}

if (index == ATTRIBUTE6)

{

return getAttribute6();

}

return super.getAttrInvokeAccessor(index, attrDef);

}

// Generated method. Do not modify.

protected void setAttrInvokeAccessor(int index, Object value, AttributeDefImpl attrDef) throws
Exception

{

if (index == ATTRIBUTE5)

{

setAttribute5((String)value);

return;

}

if (index == ATTRIBUTE6)

{

setAttribute6((String)value);

return;

}

super.setAttrInvokeAccessor(index, value, attrDef);

return;

}

/**

*

* Gets the attribute value for Attribute5, using the alias name Attribute5

*/

public String getAttribute5()

{

return super.getAttribute5();

}

/**

*

* Sets <code>value</code> as the attribute value for Attribute5

*/

public void setAttribute5(String value)

{

super.setAttribute5(value);

}

/**

*

* Gets the attribute value for Attribute6, using the alias name Attribute6

*/

public String getAttribute6()

{

return super.getAttribute6();

}

/**

*

* Sets <code>value</code> as the attribute value for Attribute6

*/

public void setAttribute6(String value)

{

super.setAttribute6(value);

}

/**

*

* Creates a Key object based on given key constituents

*/

public static Key createPrimaryKey(oracle.jbo.domain.Number requisitionLineId)

{

return new Key(new Object[] {requisitionLineId});

}

}

######### end of xxxxPoRequisitionLineEOImpl.java#########

#########Begin New EO Definition within xml########

<?xml version="1.0" encoding='windows-1252'?>

<!DOCTYPE Entity SYSTEM "jbo_03_01.dtd">

<Entity

Name="xxxxPoRequisitionLineEO"

Extends="oracle.apps.icx.por.schema.server.PoRequisitionLineEO"

DBObjectType="table"

DBObjectName="PO_REQUISITION_LINES_ALL"

AliasName="xxxxPoRequisitionLineEO"

BindingStyle="Oracle"

UseGlueCode="false"

CodeGenFlag="4"

RowClass="oracle.apps.icx.por.schema.server.xxxxPoRequisitionLineEOImpl"

DefClass="oracle.apps.fnd.framework.server.OAEntityDefImpl"

CollClass="oracle.apps.fnd.framework.server.OAEntityCache" >

<DesignTime>

<Attr Name="_isCodegen" Value="true" />

<Attr Name="_version" Value="9.0.3.13.75" />

<AttrArray Name="_publishEvents">

</AttrArray>

</DesignTime>

<Attribute

Name="Attribute5"

Precision="255"

Type="java.lang.String"

ColumnName="ATTRIBUTE5"

ColumnType="VARCHAR2"

SQLType="VARCHAR"

TableName="PO_REQUISITION_LINES_ALL" >

<Properties>

<Property Name ="AccountLineBizAttrSet" Value ="Y" />

</Properties>

<DesignTime>

<Attr Name="_OverrideAttr" Value="true" />

</DesignTime>

</Attribute>

<Attribute

Name="Attribute6"

Precision="255"

Type="java.lang.String"

ColumnName="ATTRIBUTE6"

ColumnType="VARCHAR2"

SQLType="VARCHAR"

TableName="PO_REQUISITION_LINES_ALL" >

<Properties>

<Property Name ="AccountLineBizAttrSet" Value ="Y" />

</Properties>

<DesignTime>

<Attr Name="_OverrideAttr" Value="true" />

</DesignTime>

</Attribute>

</Entity>

#########End New EO Definition within xml########

Last Step

#######Finish Recompilation of deployment#######

Recompile the .java file on the server as below. I do this to ensure that everything will work
in production without any grief.

#Note that i should have actually created a new directory structure to keep

#this extended file. But if i were to redo this, i would create a new directory structure

#Neverthless, this is still safe to Recompile your deployment on Server itself.

echo deploy xxxxPoRequisitionLineEOImpl.class

cp $XXPO_TOP/bin/xxxxPoRequisitionLineEOImpl.class $JAVA_TOP/oracle/apps/icx/por/schema/server/xxxxPoRequisitionLineEOImpl.class

echo deploy xxxxPoRequisitionLineEOImpl.java

cp $XXPO_TOP/bin/xxxxPoRequisitionLineEOImpl.java $JAVA_TOP/oracle/apps/icx/por/schema/server/xxxxPoRequisitionLineEOImpl.java

echo deploy xxxxPoRequisitionLineEO.xml

cp $XXPO_TOP/bin/xxxxPoRequisitionLineEO.xml $JAVA_TOP/oracle/apps/icx/por/schema/server/xxxxPoRequisitionLineEO.xml

cd $JAVA_TOP/oracle/apps/icx/por/schema/server

echo "Now compiling....."

javac xxxxPoRequisitionLineEOImpl.java

#######Finish Recompilation of deployment#######

Your comments are welcome, if you feel I should elaborate any section further. I believe almost
every second client on 11.5.10 iProcurement will have to implement this functionality.

Thanks,

Anil Passi

2006-09-02T12:06:00.000Z

Oracle AR Receipt API

Back in year 2002, when working on 11.5.8 for one of my previous clients, I was tasked to build a lockbox interface. The requirement was quite simple, receipts from a third party system were being dumped into a couple of tables. Those receipts were to be validated and transferred into Oracle Receivables. For this simple task, I found Lockboxes to be an overkill. Doing a bit of research on Metalink I found that AR now has a receipt creation API. Please find the steps below for implementing a Receipt creation API in Oracle AR.

Step 1. Validate the following:-
a. Ensure that exchange Rate exists in the system if the receipt being interfaced is a foreign currency receipt.
b. Validate that the receipt belongs to an Open or Future enterable period in GL.
This check can be done by using below function within a package
FUNCTION validate_gl_accounting_date (
p_accounting_date IN DATE
,p_sob_id IN NUMBER
)
RETURN BOOLEAN
IS
v_count NUMBER := 0;
BEGIN
SELECT COUNT ( * )
INTO v_count
FROM gl_period_statuses gps
WHERE gps.application_id = g_gl_application_id
AND gps.set_of_books_id = p_sob_id
AND gps.closing_status IN ( 'O', 'F' )
AND p_accounting_date BETWEEN NVL ( gps.start_date, p_accounting_date )
AND NVL ( gps.end_date, p_accounting_date );

IF v_count > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END validate_gl_accounting_date ;

Step 2.
After successful validation, call the AR Receipt API ar_receipt_api_pub.create_cash. If the receipt is a foreign currency receipt, then parameters p_exchange_rate_type & p_exchange_rate_date must be supplied with a value. You may pass a value of fnd_api.g_false to parameter p_commit , to facilitate rollback if an errored is encountered in any other part of the functionality. If OUT parameter x_return_status is returned with a value of E, it implies error.
IF p_foriegn_currency THEN
ar_receipt_api_pub.create_cash (
p_api_version => g_api_version
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_currency_code => p_rhp_receipt.originating_currency
,p_amount => p_rhp_receipt_routing.routed_amount
,p_receipt_number => g_process_name || '-' ||
p_rhp_receipt.receipt_id ||
'-' ||
p_rhp_receipt_routing.receipt_routing_id
,p_receipt_date => p_rhp_receipt.receipt_date
,p_cr_id => v_cr_id
,p_receipt_method_name => g_receipt_method_name
,p_customer_number => p_rhp_receipt_routing.ar_customer_number
,p_comments => p_rhp_receipt.originating_customer
,p_customer_receipt_reference => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
,p_remittance_bank_account_id => p_rhp_receipt.bank_account_id
,p_exchange_rate_type => xxdhi_util_pkg.g_conversion_type_code
,p_exchange_rate_date => p_rhp_receipt.receipt_date
);
ELSE
ar_receipt_api_pub.create_cash (
p_api_version => g_api_version
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_currency_code => p_rhp_receipt.originating_currency
,p_amount => p_rhp_receipt_routing.routed_amount
,p_receipt_number => g_process_name || '-' ||
p_rhp_receipt.receipt_id ||
'-' ||
p_rhp_receipt_routing.receipt_routing_id
,p_receipt_date => p_rhp_receipt.receipt_date
,p_cr_id => v_cr_id
,p_receipt_method_name => g_receipt_method_name
,p_customer_number => p_rhp_receipt_routing.ar_customer_number
,p_comments => p_rhp_receipt.originating_customer
,p_customer_receipt_reference => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
,p_remittance_bank_account_id => p_rhp_receipt.bank_account_id
);
END IF ;

Step 3. If the requirement is To reverse an existing receipt, then use API ar_receipt_api_pub.REVERSE. Once again, prefer setting the validation level to fnd_api.g_valid_level_full

Following validation must be done at the time of reversal. This is required because Oracle wouldn't let you Reverse an applied receipt straightaway.
FUNCTION validate_reversal_flag_valid(p_cash_receipt_id OUT INTEGER)
RETURN BOOLEAN IS
CURSOR c_check IS
SELECT cash_receipt_id
,status
FROM ar_cash_receipts
WHERE receipt_number =
g_process_name || '-' || p_rhp_receipt.receipt_id || '-' ||
p_rhp_receipt_routing.receipt_routing_id;
p_check c_check%ROWTYPE;
no_existing_receipt EXCEPTION;
receipt_already_applied EXCEPTION;
receipt_already_reversed EXCEPTION;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
CLOSE c_check;

IF p_check.cash_receipt_id IS NULL
THEN
RAISE no_existing_receipt;
END IF;

IF p_check.status = 'APP'
THEN
RAISE receipt_already_applied;
END IF;

IF p_check.status = 'REV'
THEN
RAISE receipt_already_reversed;
END IF;

p_cash_receipt_id := p_check.cash_receipt_id;
RETURN TRUE;

EXCEPTION
WHEN no_existing_receipt THEN
fnd_message.set_name('AR', 'AR_RAPI_CASH_RCPT_ID_INVALID');
RETURN FALSE;
WHEN receipt_already_applied THEN
fnd_message.set_name('AR', 'GENERIC_MESSAGE');
fnd_message.set_token('GENERIC_TEXT'
,'This receipt has been applied to transaction in Delphi Oracle Receivables.' ||
chr(10) ||
'Please unapply the receipt before reversing');
RETURN FALSE;
WHEN receipt_already_reversed THEN
fnd_message.set_name('AR', 'GENERIC_MESSAGE');
fnd_message.set_token('GENERIC_TEXT'
,'This receipt has already been reversed in Delphi Oracle Receivables.');
RETURN FALSE;
END validate_reversal_flag_valid;

Please note that for any given receipt, the Step 2 & Step 3 is mutually exclusive.

To refer to the source code of the package body, please click on AR Receipts API.pdf

2006-09-02T00:25:00.000Z

Oracle Read Only APPS Database Schema

In this article I have detailed how to create a read only schema for APPS in Oracle eBusiness Suite, without the overhead of creating thousands of synonyms. Whilst in the past I have known clients to implement this using synonyms. However the approach discussed below is designed without the need of having to create synonyms.

Step 1
Create the read-only schema, in this case lets call it APPS_QUERY.

Step 2.
Surely, the schema created in above Step 1 will be given read only grants to objects in apps. There will be cases where the grant command might fail. To monitor such failures create a table as below
conn xx_g4g/&2 ;
--For APPS_QUERY. This table will capture the exceptions during Grants
PROMPT create table XX_GRANTS_FAIL_APPS_QUERY
create table XX_GRANTS_FAIL_APPS_QUERY (
object_name VARCHAR2(100)
,sqlerrm varchar2(2000)
,creation_date DATE
);

grant all on XX_GRANTS_FAIL_APPS_QUERY to apps with grant option;

grant select on XX_GRANTS_FAIL_APPS_QUERY to apps_query ;

Step 3
In this step we grant select on all the existing views and synonyms in apps schema to apps_query.

conn apps/&1 ;

PROMPT This can take upto 15-30 minutes
PROMPT Granting SELECT on All synonyms and views to apps_query
DECLARE
--One off script to execute grants to apps_query
v_error VARCHAR2(2000);
BEGIN

FOR p_rec IN (SELECT *
FROM all_objects
WHERE owner = 'APPS'
AND object_type IN ('SYNONYM', 'VIEW')
AND object_name NOT LIKE '%_S')
LOOP
BEGIN
EXECUTE IMMEDIATE 'grant select on ' || p_rec.object_name ||
' to apps_query';
EXCEPTION
WHEN OTHERS THEN
v_error := substr(SQLERRM, 1, 2000);
INSERT INTO bes.XX_GRANTS_FAIL_apps_query
(object_name
,SQLERRM
,creation_date
)
VALUES
(p_rec.object_name
,v_error
,sysdate
);
END;
END LOOP;
COMMIT;
END;
/

Step 4
Write a after logon trigger on apps_query schema. The main purpose of this trigger is to alter the session to apps schema, such that the CurrentSchema will be set to apps for the session(whilst retaining apps_query restrictions).In doing so your logon will retain the permissions of apps_query schema(read_only). Howerver it will be able to reference the apps objects with exactly the same name as does a direct connection to apps schema.

conn apps/&1 ;
PROMPT CREATE OR REPLACE TRIGGER xx_apps_query_logon_trg
CREATE OR REPLACE TRIGGER xx_apps_query_logon_trg
--16Jun2006 By Anil Passi
--Trigger to toggle schema to apps, but yet retaining apps_query resitrictions
--Also sets the org_id
AFTER logon ON apps_query.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE
'declare begin ' ||
'dbms_application_info.set_client_info ( 101 ); end;';
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
END;
/

Step 5
Create a Trigger on the apps schema to issue select only grants for all new views and synonyms. Please note that I am excluding grants for sequences. SELECT grants for views and synonyms will be provided to apps_query as and when such objects are created in APPS. Please note that, all the APPS objects (views and synonyms) that existed in APPS schema prior to the implementation of this design, would have been granted read-only access to apps_query in "Step 2".

conn apps/&1 ;
PROMPT CREATE OR REPLACE TRIGGER xx_grant_apps_query
CREATE OR REPLACE TRIGGER xx_grant_apps_query
--16Jun2006 By Anil Passi
--
AFTER CREATE ON APPS.SCHEMA
DECLARE
l_str VARCHAR2(255);
l_job NUMBER;
BEGIN
IF (ora_dict_obj_type IN ('SYNONYM', 'VIEW'))
AND (ora_dict_obj_name NOT LIKE '%_S')
THEN
l_str := 'execute immediate "grant select on ' || ora_dict_obj_name ||
' to apps_query";';
dbms_job.submit(l_job, REPLACE(l_str, '"', ''''));
END IF;
END;
/

OK, now for sme notes:-
Note1
You need to ensure that the schema created in Step 1 has very limited permissions. Most importantly it must not be given grant for “EXECUTE/CREATE ANY PROCEDURE”. You will need to agree with your DBAs upfront for the permissions,

Note 2
Only views and synonyms will be granted access. Objects in your xx_g4g(bespoke) schema should have their synonyms in apps already in place.

Note 3
If your site has multi org enabled, you will then have to set the org I'd after loggiong on to apps query schema. In case you have only one single ORG_ID, then would have been set as in Step 4 above.

Note 4
ALTER SESSION SET CURRENT_SCHEMA =APPS
This facilitates users to run their queries as if they were connected to apps schema. However, their previliges will be restricted to those of apps_query

Note 5
It is assumed that ALTER SESSION privilege will exist for APPS_QUERY schema.

Thanks,
Anil Passi

2006-09-01T00:39:00.000Z

Blob as Oracle Workflow Attachment

Please find a 4 step solution to send Binary Document Attachments with Oracle Workflows Notifications. Using this technique you can generate email notifications with attachments for MsWord, or PDF or any other binary files.

Oracle has made sending binary documents as attachments with notifications a piece of cake. In the example below, I am assuming only one attachment per notification. However, you can create as many Attachment attributes you wish, and also can programmatically control how many attachments are sent per transactions. Anyway, below are four simple steps for sending Workflow Notifications with Attachments.

Step 1
As in the picture below, workflow Message “XX_G4G Notification With Attachment” is defined. This message will be referenced by the Notification that sends either MsWord or PDF or any other binary attachment as email.

Step 2.
Define an attribute to the message defined in Step 1. This is shown in the picture below. Following must be noted:-
Type: Document
Source: Send
Frame Target: New Window
Attach Content: Yes.

If you had dragged the Attribute from Workflow level into this message, then default sub-section will be populated automatically.

Step 3.
After executing the wf_engine.createprocess and before executing wf_engine.startprocess, we need to set a value for the attribute defined in Step 2. This can be done by calling wf_engine.setitemattrdocument as below
wf_engine.setitemattrdocument
(itemtype=>'XG4GWFIT'
, itemkey=>'XG4GWFIT' || l_item_key
, aname=>'XX_G4G_ATTACHMENT1'
, documentid =>'PLSQLBLOB:xx_g4g_package.xx_notif_attach_procedure/' || to_char(l_file_id));

Please note the manner in which parameter documented is assigned. The syntax is PLSQLBLOB:./ . In my example, I am capturing the file_id from fnd_lobs. In your case this ID will be derived depending upon whether you are looking for PO Attachment or say an attachment to Oracle Sales Order or a Resume in iRecruitment or some course notes in oracle Learning Management.

Step 4.
Handle the execution of the procedure, in this case xx_notif_attach_procedure

PROCEDURE xx_notif_attach_procedure
(
document_id IN VARCHAR2
,display_type IN VARCHAR2
,document IN OUT BLOB
,document_type IN OUT VARCHAR2
) IS
lob_id NUMBER;
bdoc BLOB;
content_type VARCHAR2(100);
filename VARCHAR2(300);
BEGIN
set_debug_context('xx_notif_attach_procedure');
lob_id := to_number(document_id);

-- Obtain the BLOB version of the document
SELECT file_name
,file_content_type
,file_data
INTO filename
,content_type
,bdoc
FROM fnd_lobs
WHERE file_id = lob_id;
document_type := content_type || ';name=' || filename;
dbms_lob.copy(document, bdoc, dbms_lob.getlength(bdoc));
EXCEPTION
WHEN OTHERS THEN
debug('ERROR ^^^^0018 ' || SQLERRM);
wf_core.CONTEXT('xx_g4g_package'
,'xx_notif_attach_procedure'
,document_id
,display_type);
RAISE;
END xx_notif_attach_procedure;

Please note the following:-
Note 1. In step 3, the value of Unique File id that is passed in after / gets translated into document_id in step 4. This translation occurs within the workflow engine, when it splits the pl/sql name from parameter.
Note 2. Proecdure xx_notif_attach_procedure must be defined in the package specification too.
Note 3. This procedure has an out parameter “document IN OUT BLOB”
Note 4. The value from the Oracle Blob column is fetched into blob variable bdoc from fnd_lobs.
Note 5. Next you can use dbms_lob.copy to copy the value from blob variable into out parameter for notification.

When I implemented this the very first time, I could hardly believe it to be as simple as this, to implement BLOB attachments in Oracle Workflow Notifications.

Thanks,
Anil Passi

2006-08-29T11:43:00.000Z

Create FND_USER with System Administrator

If you have the Apps Password, its quite easy to create a FND_USER for yourself by using the API. I find this script very useful when development environment gets cloned from Production(that is when i do not have FND_USER in Production).

Please note that:-
1. You will be allocated System Administrator by this script. Hence you can assign whatever responsibilities that you desire latter, after logging in.
2. The password will be set to oracle
3. You need apps password to run this script. Alternately you need execute permission on fnd_user_pkg from the user where this script will be run. If using some other user, please use apps.fnd_user_pkg.createuser
4. You need a COMMIT. I have not included the commit within this script.
5. When running this script, you will be prompted to enter a user name.

DECLARE
--By: Anil Passi
--When Jun-2001
v_session_id INTEGER := userenv('sessionid');
v_user_name VARCHAR2(30) := upper('&Enter_User_Name');
BEGIN
--Note, can be executed only when you have apps password.
-- Call the procedure to Creaet FND User
fnd_user_pkg.createuser(x_user_name => v_user_name
,x_owner => ''
,x_unencrypted_password => 'oracle'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => 'appstechnical.blogspot.com'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_employee_id => 30 /*Change this id by running below SQL*/
/*
SELECT person_id
,full_name
FROM per_all_people_f
WHERE upper(full_name) LIKE '%' || upper('full_name') || '%'
GROUP BY person_id
,full_name
*/
,x_email_address => 'appstechnical.blogspot@gmail.com'
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);
END;
/

Thanks,
Anil Passi

2006-08-28T09:54:00.000Z

XMLImporter in Oracle Apps Framework

Please find a sample script for XMLImporter. This can be used to load new XML Documents that you will be creating (using jDeveloper ) while building new screens using OAF ( Oracle Applications Framework ).

This script is quite generic and can be used with little modifications to move XML Document in OAF from one environment to another.

Eventually, the XML data will be loaded into MDS Repository within tables JDR_ATTRIBUTES, JDR_COMPONENTS, JDR_PATHS & JDR_ATTRIBUTES_TRANS(if applicable).
In order to Load the XML Document, you will require the Machine Name where the Oracle DB is hosted.

This script will require the DB port number and the machine name where XML Document is being loaded. For this I have written a simple Unix Function, given that the XMLImporter will be run from Unix in this example. This function was written to make this script machine independent. Surely there are other ways to fetch these values.
However, I will be using below profile options :-
Profile option "CSF: eLocation Map host name" to get the host name of Oracle Database.
Profile option "CSF: Database port to receive data" to get the port number.
Please ensure that these two profile options are set before invoking XMLImporter. On 11.5.7 onwards, you will certainly have values in these profiles.

getPortMachine() being a Unix function, connects to the database and populates two Unix Environment variables.

For simplicity, I have taken an example of a Standard XML Document from iProcurement.
This will illustrate the full/relative path one needs to use in XMLImporter script
The document in the example is located at $ICX_TOP/mds/por/req/webui/CheckoutSummaryPG.xml

#############################
Script begins below
#############################

MACHINE_NAME=0
PORT_NUMBER=0

export MACHINE_NAME
export PORT_NUMBER

getPortMachine()
{
MACHINE_NAME=`sqlplus -s apps/$APPS_PWD <
set pages 0
set lines 1023
select fnd_profile.value('CSF_EMAP_DS_HOST') from dual ;
exit
EOF`

PORT_NUMBER=`sqlplus -s apps/$APPS_PWD <
set pages 0
set feed off
select fnd_profile.value('CSF_MAP_DB_PORT') from dual ;
exit
EOF`
}

getPortMachine

echo "**********************************"
echo $MACHINE_NAME is Machine Name
echo "**********************************"
sleep 5
echo $PORT_NUMBER is port number
echo "**********************************"
sleep 5

"Echo import CheckoutSummaryPG.xml"
adjava oracle.jrad.tools.xml.importer.XMLImporter $ICX_TOP/mds/por/req/webui/CheckoutSummaryPG.xml -rootdir $ICPO_TOP/bin -username apps -password $APPS_PWD -dbconnection "(description=(address_list=(address=(community=tcp.world)(protocol=tcp)(host=$MACHINE_NAME)(port=$PORT_NUMBER)))(connect_data=(sid=$TWO_TASK)))" -rootPackage /oracle/apps/icx/por/req/webui

##Please take care of the wrapping in this blog page.
For wider viewing area, visit http://oracle.anilpassi.com

Thanks,
Anil Passi

2006-08-27T21:59:00.000Z

Oracle FNDLOAD Script Examples .

In this article I wish to give real working examples of Oracle's FNDLOAD utility.
Besides that, I have included some notes at the bottom of this article.

I have used FNDLOAD successfully in past for several different entities/data types within Oracle 11i for almost all my previous clients, ever since this utility became available.
Some of the examples in this FNDLOAD article include:-
FNDLOAD to transfer Request Groups
FNDLOAD for moving Concurrent Programs
FNDLOAD to download and upload Forms Personalizations ( or Personalisations depending on where you are located )

Use FNDLOAD for transferring value set definitions.
-->Please note that when transferring Key Flex Fields and Descriptive flex fields the respective value sets against each segment will be extracted and loaded automatically.

Also, FNDLOAD can be used to migrate Key FlexFields, Descriptive Flexfields, Responsibilities and almost every other FND entity.

Please note that the text written down here could get wrapped in the browser.
Hence you may have to use \ to continue the single line command on Unix, in case you find the lines wrapping
In my case I am ensuring that $CLIENT_APPS_PWD has the apps password before running the scripts

------------------------------------------------------------------------------------------

##To FNDLOAD Request groups
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_MY_REPORT_GROUP_NAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX_MY_REPORT_GROUP_NAME" APPLICATION_SHORT_NAME="XXGMS"
##Note that
##---------
## <> will be your Application Shortname where request group is registered
## XX_MY_REPORT_GROUP_NAME
Will be the name of your request group
## ##To upload this Request Group in other environment after having transferred the ldt file

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct

------------------------------------------------------------------------------------------

##To FNDLOAD Concurrent Programs
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt PROGRAM APPLICATION_SHORT_NAME="XXGMS" CONCURRENT_PROGRAM_NAME="XX_CUSTOM_ORACLE_INTERFACE_PROG"
##Note that
##---------
## XXGMS will be your custom GMS Application Shortname where concurrent program is registered
## XX_CUSTOM_ORACLE_INTERFACE_PROG
Will be the name of your request group
## XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt is the file where concurrent program definition will be extracted
## ##To upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt

------------------------------------------------------------------------------------------

##To FNDLOAD Oracle Descriptive Flexfields
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_HEADERS'
##Note that
##---------
## PO is the Application Shortname against which descriptive flexfield against PO Headers is registered
## PO_REQUISITION_HEADERS
is the name of Descriptive Flexfield against PO Requisition Headers
## Use the SQL below to find the name of DFF, rather than logging into the screen (ooops via jinitiator)
########----->SELECT
########----->application_id, DESCRIPTIVE_FLEXFIELD_NAME, application_table_name
########----->FROM
########-----> fnd_descriptive_flexs_vl
########----->WHERE
########-----> APPLICATION_TABLE_NAME like '%' || upper('&tab_name') || '%'
########----->ORDER BY APPLICATION_TABLE_NAME
########----->/
## To upload into another environment
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt

## OK another example for DFF against FND_LOOKUPS
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FND_COMMON_LOOKUPS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=FND DESCRIPTIVE_FLEXFIELD_NAME='FND_COMMON_LOOKUPS'
## OK another example for DFF against Project Accounting Expenditure Types
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PA_EXPENDITURE_TYPES_DESC_FLEX_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PA DESCRIPTIVE_FLEXFIELD_NAME='PA_EXPENDITURE_TYPES_DESC_FLEX'

------------------------------------------------------------------------------------------

##To FNDLOAD Oracle Menus
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt MENU MENU_NAME="ICX_POR_SSP_HOME"
##Note that
##---------
## Oracle Menus are not attached to applications. Hence no need to include application short name
## ICX_POR_SSP_HOME is the menu name. This can be validated via below SQL
## select user_menu_name from fnd_menus_vl where menu_name = 'ICX_POR_SSP_HOME' ;
## Also note that we do not pass in the User_menu_name in this example
## OK, now to upload this file
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt

----------------------------------------------------------------------------------------------------------------------------

## Well, now for FND Messages to download a single message
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='ICX' MESSAGE_NAME=XX_ICX_POR_LIFECYCLE_PAY_TIP

##or you may as well download all the messages within an application
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ALL_GMS_MESSAGES_00.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXGMS'

## now to upload using FNDLOAD
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt

----------------------------------------------------------------------------------------------------------------------------

## Now it's the turn of Lookup values. Again, its not a rocket science
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='XXGMS' LOOKUP_TYPE="XX_TRX_BATCH_STATUS"
## Note that
## XX_TRX_BATCH_STATUS is the name of FND Lookup Type in this example
## This will download all the lookup codes within the defined lookup
## To upload
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt

----------------------------------------------------------------------------------------------------------------------------

## Now lets have a look at the profile option using oracle's FNDLOAD
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt PROFILE PROFILE_NAME="POR_ENABLE_REQ_HEADER_CUST" APPLICATION_SHORT_NAME="ICX"
## Note that
## POR_ENABLE_REQ_HEADER_CUST is the short name of profile option
## We aren't passing the user profile option name in this case. Validate using ...
########----->select application_id, PROFILE_OPTION_NAME || '==>' || profile_option_id || '==>' ||
########----->USER_PROFILE_OPTION_NAME
########----->from FND_PROFILE_OPTIONS_VL
########----->where PROFILE_OPTION_NAME like '%' || upper('&profile_option_name') || '%'
########----->order by PROFILE_OPTION_NAME
########----->/
## Now to upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt

----------------------------------------------------------------------------------------------------------------------------

## Now for the request sets that contain the stages and links for underlying concurrent programs
## For this you will be firstly required to download the request set definition.
## Next you will be required to download the Sets Linkage definition
## Well, lets be clear here, the above sequence is more important while uploading
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt REQ_SET REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"
## Note that FNDRSSUB4610101 can be found by doing an examine on the
########----->select request_set_name from fnd_request_sets_vl
########----->where user_request_set_name = 'User visible name for the request set here'
## Now for uploading the request set, execute the below commands
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt

----------------------------------------------------------------------------------------------------------------------------

## Now for the responsibility
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt FND_RESPONSIBILITY RESP_KEY="XX_PERSON_RESPY"
## note that XX_PERSON_RESPY is the responsibility key
## Now to upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt

----------------------------------------------------------------------------------------------------------------------------
## OK, now for the forms personalizations
## For the forms personalizations, I have given three examples as below.
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt FND_FORM_CUSTOM_RULES function_name="PERWSHRG-404"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt FND_FORM_CUSTOM_RULES function_name="HZ_ARXCUDCI_STD"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt FND_FORM_CUSTOM_RULES function_name="AP_APXVDMVD"
## Note that the function name above is the function short name as seen in the Function Definition Screen
## Now to upload the forms personalizations that are defined against these forms functions....
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt

----------------------------------------------------------------------------------------------------------------------------

Notes:-
1. Give special attention when downloading Menus or Responsibilities.
In case your client has several developers modifying Responsibilities and Menus, then be ultra carefull. Not being carefull will mean that untested Forms and Functions will become available in your clients Production environment besides your tested forms, functions and menus.

2. Be very careful when downloading flexfields that reference value sets with independent values for GL Segment Codes.
By doing so, you will download and extract all the test data in GL Codes that might not be applicable for production.

3. There are several variations possible for FNDLOAD, for example you can restrict the download and uploads to specific segments within Descriptive Flex Fields.
Please amend the above examples as desired for applying appropriate filterations.

4. The list of examples by no mean cover all possible FNDLOAD entities.

5. FNDLOAD is very reliable and stable, if used properly. This happens to by one of my favourite Oracle utilities.

4. Last but not the least, please test your FNDLOAD properly, so as to ensure that you do not get any unexpected data. In past I have noticed undesired results when the Lookup gets modified manually directly on production, and then the FNDLOAD is run for similar changes.

If possible, try to follow a good practice of modifying FNDLOADable data only by FNDLOAD on production environment.

2006-08-26T19:31:00.000Z

Encumbrance Migration to Oracle Grants for Open Purchase Orders

Firstly, let me tell you that Oracle Applications Encumbrance functionality is very rich, it works well and is also very stable. But it did take Oracle some time to stabilise & deliver feature rich encumbrance functionality.In oracle applications, my exposure to encumbrances is related to those in Oracle Purchasing / iProcurement, Oracle Payables, Labor Distributions. This article is mostly a technical explanation of how encumbrances can be migrated from open Purchase Orders into Oracle Grants / Oracle Projects. My client was already live with Oracle Purchasing & iProcurement when this migration requirement came through. Prior to migration, the encumbrances were pushed into GL via the Oracle Purchasing module.
When implementing Oracle Grants/ Oracle Projects, some of my clients GL Codes were mapped to Grants Awards, Projects, Tasks & Expenditure type. Before migration, the Encumbrances for such GL codes wouldn't be visible from Grants in either the Award status (ASI) or the Project Status (PSI) screen. The challenge was not only to make those encumbrances visible from GMS [Grants Management System], but also to have those liquidated/reversed when purchase orders were matched to Oracle Payables Invoices. Not only that, upon matching invoices in Payables, award status screen must then show those earlier encumbrances as actuals. There are now three subsections below, and you may wish to read those that interest you.
Section 1. Challenges
Section 2. Steps undertaken
Section 3. Notes

Overall this task was of medium complexity, but challenges were :-
1. Ensuring that all Open Purchase Orders regardless of their Approval/Reservation status were successfully migrated to POETA.
2. To ensure that after switching the relevant Purchase Orders to POETA, those purchase Orders must be Re-Encumbered Successfully. See the note at very end to find how this was ensured.
3. To ensure that all the PO's that were approved prior to migration were Re-Approved successfully without invoking the Approval Workflow and also without having to update any Oracle table.
4. Use Oracle API's at as many possible places. No direct updates to any of the Oracle tables were to be done. Considering there aren't many public API's to help achieve these tasks, I had to do plenty digging to find the APIs that forms and workflows etc called. Those internal API's were re-used, hence avoiding any direct table updates.
5. Some of the PO_DISTRBUTION_LINES records were partially matched. Hence such Distributions had to be split into two.

Following steps were undertaken

All the below steps were accomplished by writing various procedures and functions within a PL/SQL Package
Step 1.
Update the logged in User Id that executes migration program to be that of the Employee that can approve any Purchase Order. See Note 4 below.
Step 2.
Run Payables Accounting Process and Program Create Journal in GL
Doing so will ensure that PO Distribution table is in synch with the latest set of matched invoices.
Step 3.
Load the data into a table say xx_eligible_po_dist_4_gms_enc
Create a local procedure for doing so.

INSERT INTO xx_eligible_po_dist_4_gms_enc
(po_header_id
,po_line_id
,line_location_id
,po_distribution_id
,code_combination_id
,po_status
,old_quantity_ordered
,old_quantity_cancelled
,old_quantity_billed
,old_quantity_delivered
,fnd_request_id)
SELECT pod.po_header_id
,pod.po_line_id
,pod.line_location_id
,pod.po_distribution_id
,pod.code_combination_id
,po_headers_sv3.get_po_status(phea.po_header_id)
,pod.quantity_ordered
,pod.quantity_cancelled
,pod.quantity_billed
,pod.quantity_delivered
,g_conc_request_id
FROM po_line_locations pll
,po_distributions pod
,po_headers phea
,po_lines pol
,po_lookup_codes plc
,po_line_types plt
,xx_enc_eligible_projects eep
,gl_code_combinations glcc
WHERE pod.line_location_id = pll.line_location_id AND
pol.po_line_id = pod.po_line_id AND
pol.line_type_id = plt.line_type_id AND
nvl(pll.closed_code
,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED') AND
nvl(pol.closed_code
,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED') AND
pll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED') AND
nvl(pll.closed_code
,'OPEN') = plc.lookup_code AND
plc.lookup_type = 'DOCUMENT STATE' AND
nvl(pll.cancel_flag
,'N') = 'N' AND nvl(pol.cancel_flag
,'N') = 'N' AND
glcc.code_combination_id = pod.code_combination_id AND
(glcc.segment3 LIKE 'P%' OR glcc.segment3 LIKE 'R%') AND
phea.po_header_id = pol.po_header_id AND
pod.quantity_ordered - pod.quantity_cancelled -
pod.quantity_billed > 0 AND
phea.authorization_status IN
('APPROVED', 'IN PROCESS', 'INCOMPLETE', 'REQUIRES REAPPROVAL') AND
eep.project_code = glcc.segment3 AND status <> 'CLOSED';
Next Update the above entries in the table with POETA
PROCEDURE load_poeta_info_for_tab IS
l_poeta_rec r_poeta_rec_type;
BEGIN
FOR p_rec IN (SELECT tt.code_combination_id
FROM xx_eligible_po_dist_4_gms_enc tt
WHERE tt.fnd_request_id = g_conc_request_id
GROUP BY tt.code_combination_id)
LOOP
l_poeta_rec := get_poeta_rec_for_ccid(p_gl_code_combination_id => p_rec.code_combination_id);
UPDATE xx_eligible_po_dist_4_gms_enc tab_poeta
SET tab_poeta.project_id = l_poeta_rec.project_id
,tab_poeta.expenditure_org_id = l_poeta_rec.expenditure_org_id
,tab_poeta.expenditure_type = l_poeta_rec.expenditure_type
,tab_poeta.task_id = l_poeta_rec.task_id
,tab_poeta.award_id = l_poeta_rec.award_id
,tab_poeta.expenditure_item_date = l_poeta_rec.expenditure_item_date
WHERE tab_poeta.code_combination_id = p_rec.code_combination_id;
END LOOP;
END load_poeta_info_for_tab;

Step 4. Validate the POETA information loaded into above table by calling api's
gms_transactions_pub.validate_transaction(p_project_id => p_rec.project_id
,p_task_id => p_rec.task_id
,p_award_id => p_rec.award_id
,p_expenditure_type => p_rec.expenditure_type
,p_expenditure_item_date => p_rec.expenditure_item_date
,p_calling_module => 'POXPOEPO'
,p_outcome => v_gms_outcome);
pa_transactions_pub.validate_transaction(p_rec.project_id
,p_rec.task_id
,p_rec.expenditure_item_date
,p_rec.expenditure_type
,NULL
,141 --
,NULL
,'GBP' --x_denom_currency_code
,'GBP' --x_acct_currency_code
,p_rec.denom_raw_cost --denom_raw_cost
,p_rec.denom_raw_cost --x_acct_raw_cost
,NULL --x_acct_rate_type
,NULL --x_acct_rate_date
,NULL --x_acct_exchange_rate
,NULL --transfer_ei
,p_rec.expenditure_org_id --x_expenditure_org_id
,NULL --nl_resource_org_id
,NULL --transaction_source
,'POXPOEPO' --x_form_name
,p_rec.vendor_id
,1054 -- x_last_updated_by
,NULL --x_attribute_category
,NULL --x_attribute1
,NULL --x_attribute2
,NULL --x_attribute3
,NULL --x_attribute4
,NULL --x_attribute5
,NULL --x_attribute6
,NULL --x_attribute7
,NULL --x_attribute8
,NULL --x_attribute9
,NULL --x_attribute10
,NULL --x_attribute11
,NULL --x_attribute12
,NULL --x_attribute13
,NULL --x_attribute14
,NULL --x_attribute15
,x_msg_application
,x_msg_type
,x_msg_token1
,x_msg_token2
,x_msg_token3
,x_msg_count
,x_msg_data
,x_billable_flag);

The POETA records that fail validation must be flagged and reported, so that they are'nt processed for migration.
Step 5.
Now we are ready to roll. Finally Loop through the eligible PO Lines and move them to POETA.
Please find the psuedo code below
LOOP FOR ALL ELIGIBLE PO LINES
debug_log('Processing p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id || ' status=>' || p_get_pos.status);
BEGIN
IF does_invvalid_account_exist(p_po_header_id => p_get_pos.po_header_id)
THEN
RAISE gl_code_inactive;
END IF;

IF does_invalid_budget_acct_exist(p_po_header_id => p_get_pos.po_header_id)
THEN
RAISE gl_code_inactive;
END IF;

IF check_invalid_locations_exists(p_po_header_id => p_get_pos.po_header_id)
THEN
RAISE invalid_location;
END IF;
--Now here we would only have the validated PO's.
--The POETA that was'nt validated were rejected during the
--validate_gms and validate_pa stages
b_originally_reserved_flag := is_orig_po_reserved(p_get_pos.status);
b_originally_approved_flag := is_orig_po_approved(p_get_pos.status);

IF b_originally_reserved_flag AND
non_reserved_line_exists(p_po_header_id => p_get_pos.po_header_id)
THEN
RAISE unreserved_line_in_reserved_po;
END IF;

IF b_originally_reserved_flag AND
does_del_more_than_order_exist(p_po_header_id => p_get_pos.po_header_id)
THEN
RAISE delivered_more_than_ordered;
END IF ;
debug_log('Remove the association to Requisition.');
--the reason we do below, is to esure that REQ is not debited in gl_bc when Unreserve is done.
--just updating attribute columns, hence no violation of Oracle support here
UPDATE po_distributions_all
SET attribute13 = req_header_reference_num || ':' ||
req_line_reference_num || ':' ||
req_distribution_id
,req_header_reference_num = NULL
,req_line_reference_num = NULL
,req_distribution_id = NULL
,attribute12 = to_char(gl_encumbered_date
,'DD-MON-YYYY')
,gl_encumbered_date = trunc(SYSDATE)
WHERE po_header_id = p_get_pos.po_header_id;

IF b_originally_reserved_flag
THEN
IF NOT unreserve_po(p_po_header_id => p_get_pos.po_header_id
,p_agent_id => p_get_pos.agent_id)
THEN
RAISE can_not_unreserve;
END IF;
debug_log('Successfully unreserved p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
END IF;

/* Here comes the main Logic for splitting the distribution lines*/
split_po_distribution_lines(p_po_header_id => p_get_pos.po_header_id);

IF b_originally_reserved_flag
THEN
IF NOT reserve_po(p_po_header_id => p_get_pos.po_header_id
,p_agent_id => p_get_pos.agent_id)
THEN
RAISE can_not_reserve;
END IF;
debug_log('Successfully re-reserved p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
END IF;
IF b_originally_approved_flag
THEN
IF NOT approve_po(p_po_header_id => p_get_pos.po_header_id
,p_agent_id => p_get_pos.agent_id)
THEN
RAISE can_not_approve;
END IF;
debug_log('Successfully re-approved p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
END IF;
EXCEPTION
WHEN can_not_unreserve THEN
debug_log('Exception in unreserving p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
WHEN can_not_reserve THEN
debug_log('Exception in reserving p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
WHEN can_not_approve THEN
debug_log('Exception in approving p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
WHEN gl_code_inactive THEN
debug_log('GL Code is Inactive p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
WHEN unreserved_line_in_reserved_po THEN
debug_log('Unreserved Line Exists in Reserved PO. Skipping p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
WHEN invalid_location THEN
debug_log('Invalid Location for Ship-to or Bill-to. Skipping p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
WHEN delivered_more_than_ordered THEN
debug_log('Delivered Qty more than ordered qty. Skipping p_get_pos.po_header_id=>' ||
p_get_pos.po_header_id);
END;
UPDATE xx_eligible_po_dist_4_gms_enc
SET processed_flag = 'Y'
,po_status_after_process_is_run = po_headers_sv3.get_po_status(p_get_pos.po_header_id)
,status_updated_by_request_id = fnd_global.conc_request_id
WHERE po_header_id = p_get_pos.po_header_id;
END LOOP;

Usefull API code packages for various activities in this context are
FUNCTION reserve_po(p_po_header_id IN INTEGER
,p_agent_id IN INTEGER) RETURN BOOLEAN IS
l_dm_call_rec po_doc_manager_pub.dm_call_rec_type;
x_progress VARCHAR2(200);
l_warning_msg VARCHAR2(200);
x_mesg VARCHAR2(200);
l_attr_exist NUMBER := 0;
v_session_id INTEGER := userenv('sessionid');
BEGIN
l_dm_call_rec.action := 'RESERVE_DOCUMENT';
l_dm_call_rec.document_type := 'PO';
l_dm_call_rec.document_subtype := 'STANDARD';
l_dm_call_rec.document_id := p_po_header_id;
l_dm_call_rec.line_id := NULL;
l_dm_call_rec.shipment_id := NULL;
l_dm_call_rec.distribution_id := NULL;
l_dm_call_rec.employee_id := p_agent_id;
l_dm_call_rec.new_document_status := NULL;
l_dm_call_rec.offline_code := NULL;
l_dm_call_rec.note := NULL;
l_dm_call_rec.approval_path_id := NULL;
l_dm_call_rec.forward_to_id := p_agent_id;
l_dm_call_rec.action_date := NULL;
l_dm_call_rec.override_funds := 'N';
-- Below are the output parameters
l_dm_call_rec.info_request := NULL;
l_dm_call_rec.document_status := NULL;
l_dm_call_rec.online_report_id := NULL;
l_dm_call_rec.return_code := NULL;
l_dm_call_rec.error_msg := NULL;
/* This is the variable that contains the return value from the
** call to the DOC MANAGER:
** SUCCESS =0, TIMEOUT=1, NO MANAGER=2, OTHER=3
*/
l_dm_call_rec.return_value := NULL;

/* Call the API that calls the Document manager */
debug_log('Before Calling doc_manager to RESERVE');
COMMIT;
po_doc_manager_pub.call_doc_manager(l_dm_call_rec);
COMMIT;
dbms_lock.sleep(.2);
COMMIT;
debug_log('After Calling doc_manager to RESERVE');
debug_log('RESERVE p_po_header_id=>' || p_po_header_id ||
' L_DM_CALL_REC.Return_Value=>' ||
l_dm_call_rec.return_value);
IF l_dm_call_rec.return_value = 0
THEN
RETURN TRUE;
END IF;
--capture the fact that Doc Mgr could re-reserve the PO
log_error(p_po_header_id => p_po_header_id
,p_po_distribution_id => NULL
,p_gl_code_combination_id => NULL
,p_field_name => 'CAN NOT RE-RESERVE'
,p_error_text => l_dm_call_rec.error_msg);
RETURN FALSE;
--if doc mgr returns status 0, then return true
--in all other cases, return FALSE

END reserve_po;

FUNCTION approve_po(p_po_header_id IN INTEGER
,p_agent_id IN INTEGER) RETURN BOOLEAN IS
l_dm_call_rec po_doc_manager_pub.dm_call_rec_type;
x_progress VARCHAR2(200);
l_warning_msg VARCHAR2(200);
x_mesg VARCHAR2(200);
l_attr_exist NUMBER := 0;
v_session_id INTEGER := userenv('sessionid');
BEGIN
l_dm_call_rec.action := 'APPROVE_DOCUMENT';
l_dm_call_rec.action_date := SYSDATE;
l_dm_call_rec.document_type := 'PO';
l_dm_call_rec.document_subtype := 'STANDARD';
l_dm_call_rec.document_id := p_po_header_id;
l_dm_call_rec.line_id := NULL;
l_dm_call_rec.shipment_id := NULL;
l_dm_call_rec.distribution_id := NULL;
l_dm_call_rec.employee_id := g_buyer_employee_id;
l_dm_call_rec.new_document_status := 'APPROVED';
l_dm_call_rec.offline_code := NULL;
l_dm_call_rec.note := NULL;
l_dm_call_rec.approval_path_id := NULL;
l_dm_call_rec.forward_to_id := g_buyer_employee_id;
/* L_DM_CALL_REC.Action_date := NULL;*/
l_dm_call_rec.override_funds := 'N';
-- Below are the output parameters
l_dm_call_rec.info_request := NULL;
l_dm_call_rec.document_status := NULL;
l_dm_call_rec.online_report_id := NULL;
l_dm_call_rec.return_code := NULL;
l_dm_call_rec.error_msg := NULL;
/* This is the variable that contains the return value from the
** call to the DOC MANAGER:
** SUCCESS =0, TIMEOUT=1, NO MANAGER=2, OTHER=3
*/
l_dm_call_rec.return_value := NULL;
/* Call the API that calls the Document manager */
COMMIT;
po_doc_manager_pub.call_doc_manager(l_dm_call_rec);
COMMIT;
dbms_lock.sleep(.2);
COMMIT;
debug_log('APPROVE p_po_header_id=>' || p_po_header_id ||
' L_DM_CALL_REC.Return_Value=>' ||
l_dm_call_rec.return_value);
--if doc mgr returns status 0, then return true
--in all other cases, return FALSE
IF l_dm_call_rec.return_value = 0
THEN
RETURN TRUE;
END IF;
log_error(p_po_header_id => p_po_header_id
,p_po_distribution_id => NULL
,p_gl_code_combination_id => NULL
,p_field_name => 'CAN NOT APPROVE'
,p_error_text => l_dm_call_rec.error_msg);
RETURN FALSE;
END approve_po;

FUNCTION unreserve_po(p_po_header_id IN INTEGER
,p_agent_id IN INTEGER) RETURN BOOLEAN IS
l_dm_call_rec po_doc_manager_pub.dm_call_rec_type;
x_progress VARCHAR2(200);
l_warning_msg VARCHAR2(200);
x_mesg VARCHAR2(200);
l_attr_exist NUMBER := 0;
v_session_id INTEGER := userenv('sessionid');
BEGIN
l_dm_call_rec.action := 'UNRESERVE_DOCUMENT';
l_dm_call_rec.action_date := SYSDATE;
l_dm_call_rec.document_type := 'PO';
l_dm_call_rec.document_subtype := 'STANDARD';
l_dm_call_rec.document_id := p_po_header_id;
l_dm_call_rec.line_id := NULL;
l_dm_call_rec.shipment_id := NULL;
l_dm_call_rec.distribution_id := NULL;
l_dm_call_rec.employee_id := p_agent_id;
l_dm_call_rec.new_document_status := NULL;
l_dm_call_rec.offline_code := NULL;
l_dm_call_rec.note := NULL;
l_dm_call_rec.approval_path_id := NULL;
l_dm_call_rec.forward_to_id := p_agent_id;
/* L_DM_CALL_REC.Action_date := NULL;*/
l_dm_call_rec.override_funds := 'N';
-- Below are the output parameters
l_dm_call_rec.info_request := NULL;
l_dm_call_rec.document_status := NULL;
l_dm_call_rec.online_report_id := NULL;
l_dm_call_rec.return_code := NULL;
l_dm_call_rec.error_msg := NULL;
/* This is the variable that contains the return value from the
** call to the DOC MANAGER:
** SUCCESS =0, TIMEOUT=1, NO MANAGER=2, OTHER=3
*/
l_dm_call_rec.return_value := NULL;

/* Call the API that calls the Document manager */
COMMIT;
po_doc_manager_pub.call_doc_manager(l_dm_call_rec);
COMMIT;
dbms_lock.sleep(.2);
COMMIT;

debug_log('UNRESERVE p_po_header_id=>' || p_po_header_id ||
' L_DM_CALL_REC.Return_Value=>' ||
l_dm_call_rec.return_value);
--if doc mgr returns status 0, then return true
--in all other cases, return FALSE
IF l_dm_call_rec.return_value = 0
THEN
RETURN TRUE;
END IF;
--capture the fact that Doc Mgr could not un-reserve the PO
log_error(p_po_header_id => p_po_header_id
,p_po_distribution_id => NULL
,p_gl_code_combination_id => NULL
,p_field_name => 'CAN NOT UNRESERVE'
,p_error_text => l_dm_call_rec.error_msg);
RETURN FALSE;
END unreserve_po;

Step 6.
Run exceptions. I had written half a dozen SQLs for reconcilliation etc.

Step 7.
After reconcilliation, delete the Journals from GL_INTERFACE.
But before deletion please complete your reconcilliation.
You may decide not to delete those journals, but Oracle support gave a nod to this.

Important notes for migration encumbrances to poeta

Note 1.
Internal to Oracle Encumbrances, two key tables are GMS_BC_PACKETS and GL_BC_PACKETS. Do not modify the data within these tables, treat these two tables as sacred tables. These tables are where the encumbrances are queued, which eventually hits the GL in GL_BALANCES. However, feel free to use gl_bc_packets to debug the progression of encumbrances. I learnt quite a lot about encumbrances by merely studying the type of record that gets inserted into packet tables at various stages of PO/Req Entry, Approval, Reservation, Cancellation & Matching stages. The reference1 column in gl_bc_packets will tell you whether the Transaction is PO or REQ. There reference2 columns stores the respective header id, and reference3 stored the po_dist_id. Rest of the reference columns may be used, but I never bothered much as Ref1,2,3 sufficed for my requirements. Also, I must mention that GL_BC_PACKETS is for queuing encumbrances into GL. Once those encumbrances have been transferred and Posted, the respective records in GL_BC_PACKETS get deleted by running Oracle's concurrent process.

Note 2.
For some open PO's, you will be required for UnReserve, UnApprove, Modify PO with POETA and then Re-Reserve and Re-Approve the PO. For Re-Approval, make sure that your data migration Concurrent Process runs via an FND_USER that is attached to an Employee that has the highest approval limit across all cost centres for your client.

Note 3.
Given that during migration to POETA, we re-reserve the Purchase Orders, it is necessary that all the Awards against which encumbrances are being migrated have Advisory Funds Check Level. You may as well set the Advisory level to None for those Awards. This must be taken care of when migrating the Awards into Oracle Grants Accounting.

Note 4.
My client did not use internal Purchase Orders or Requisitions, hence I did not deal with those transactions. However the underlying principles must remain the same.

Note 5.
This migration task worked upon the Open Purchase Orders that had following possible statuses. I defined a global variable for each such status
g_status_approved VARCHAR2(50) := 'Approved';
g_status_approved_reserved VARCHAR2(50) := 'Approved, Reserved';
g_status_in_process VARCHAR2(50) := 'In Process';
g_status_in_process_reserved VARCHAR2(50) := 'In Process, Reserved';
g_status_incomplete VARCHAR2(50) := 'Incomplete';
g_status_incomplete_reserved VARCHAR2(50) := 'Incomplete, Reserved';
g_status_requires_reapproval VARCHAR2(50) := 'Requires Reapproval';
g_status_requires_re_reserved VARCHAR2(50) := 'Requires Reapproval, Reserved';

Note 6.
The Award_id that you see in po_distributions_all is not the gms_awards.award_id. For the PO_DISTRIBUTION Award_id you need to create adsl. Used Oracle API for doing so.

Note 7.
We used document manager API for Re-Approval of Purchase Order, Reservation of Purchase Order and also for Unreserving the Purchase Order.
Given that the document manager api sends a pipe signal that is read by doc mgr it happens in a different session. Not only that, being run by a conc mgr, the doc mgr has limited slots hence give a sleep command before and after each document manager call. Reason we need to do this is to ensure that your loop execution is not faster than the load which Document Manager can undertake.

Note 8.
My client was based in UK, and they had only one single SOB. Hence i felt no risk in hardcoding the Currency Code to GBP.

2006-08-25T22:44:00.000Z

Oracle iProcurement Demo

After much popular demand, for the learners of Oracle iProcurement, I am hereby presenting an audio visual presentation of Oracle iProcurement.

IMPORTANT: This demo[at bottom of this article] is purely intended for Educational and Training purposes.

My love affair with Oracle iProcurement started in the era of AK Developer. Even though my entry to iProc world was late, Oracle's iProc product has moved on further at a fantastic pace.
Thankfully now all of iProcurement is on OAF ( Oracle Applications Framework ). Yes this also means partial page rendering.

But this transition from AK Developer to JRAD Oracle Framework did not happen overnight.
I remember the day when Oracle made a limited release of Framework in iProcurement. One of the main pages moved to framework was the iProcurement homepage.Despite some minor connection leaks(oh yes Connections not being released), the move of iProc Front page to framework was very welcome.

For my client, this meant we got rid of all the AK Developer customizations and replaced those with Personalizations. Ah!! reminding of Personalizations, "Add New Item" of type RAW never worked right until 11.5.10 FWK CU2.

Anyway, coming back to iProcurement, the demo covers the following:-
1. A simplistic end to end process of iProcurement.
2. A brief overview of Oracle iProcurements integration with Workflowfor Accounts Generation
3. A presentation of what can be achieved by personalizations ( a lot!! )
4. How to design account generation for both Projects and Non Projects based Account codes.

Well one thing that I will not be touching base here is Loading/Catalog Management.
I have'nt seen the need for Catalog management at the previous two iProcurement implementations that I have worked upon.

Yes, Punchout Implementation is an interesting topic. Given the time, I will publish a similar demo for step-by-step setups to implement punchouts in Oracle iProcurement. The link below may take 1 to 5 minutes to load...please keep patience...
Please click here to view Oracle iProcurement Demo with Account Generation Implementation
If using IE-7, please Control + Click on above link

Thanks,
Anil Passi

2006-08-25T16:12:00.000Z

Oracle HRMS Payroll Interview Questions

Qns 1. Can you call pl/sql package functions from within a Fast Formula?
Ans 1. Yes you can
-->How do we do this?
-->There is a Define Function screen in HR. In this screen you will register the pl/sql as “External Function”.

-->If we want to pass a parameter PAYROLL_ID to this external pl/sql Function, how do we do it ?
-->The “Define Function” screen has a button named “Context Usage”. This button opens up a window in which you can register the parameters.

Qns 2: P35 report is run after the “End Of Year (EOY)” process.
Do you know which tables does EOY process populate.
P35 and P60 reports uses these tables which get populated by EOY
Ans2: FF_ARCHIVE_ITEMS
FF_ARCHIVE_ITEM_CONTEXTS

Qns 3: How do you Debug a Fast Formula?
Ans 3: You can create a message variable in Fast Formula.
This message variable must also be registered as a Formula Result( In “Formula Result Rule” window).

Qns 4: What are the various levels where you can Set up Payroll Costing Accounts?
Which levels take the highest precedence? **Assume Labor Distributions is not implemented**
Ans4;
Element Entry => Highest
Assignment => Second Highest
Organization => Third Highest
Element Link => Fourth Highest
Payroll => Fifth Highest

Qns5: Technically speaking, how do you know from tables that an Employee has been terminated?
Ans 5:
(a) The Person_Type_ID in PER_PERSON_TYPE_USAGES_F will correspond to System Person Type EX_EMP
(b) A record gets created in table PER_PERIODS_OF_SERVICE with Actual_Termination_date being not null

Qns6: How can you make Employee Number generation Automated based on business rule etc.
Ans6: Use Fast Formula.

Qns7: What is the difference between per_people_f and per_all_people_f
Ans7: PER_PEOPLE_F is a secured view on top of PER_ALL_PEOPLE_F. The secure view uses an API hr_security.show_person. This API internally checks for an entry in table PER_PERSON_LIST for the logged in person's security profile. A concurrent program named "Security List Maintainence program" will insert records in PER_PERSON_LIST

Oracle HRMS Self Service Interview Some Simple Questions
Qns 1 :
If you do personalization at both FUNCTION level and Responsibility level, which personalization has higher precedence?
Ans 1:
Responsibility level. Responsibility level will override FUNCTION level personalizations.

Qns 2:
Say you have done a lot of Personalizations to Self Service Screens.
But all these Personalizations were done in DEVELOPMENT environment.
How will you migrate these personalizations to PRODUCTION environment?
Ans2:
Before 11.5.7…Use AKLoad
This is a java program given by Oracle. This is the only way you can script it.
In DEV you will do è akdload DOWNLOAD ( & other parameters)
In PRD you will do è akdload UPLOAD ( & other parameters)
On or after 11.5.10 Use responsibility “Functional Administrator”, then click
on Personalization Tab, and then click on Import/Export.
Next select the documents to be exported.
Go to the unix box and tar the personalizations into a .tar file.
On the Production environment unzip this tar file in location as entered in profile “FND: Personalization Document Root Path”.
Setting up of this profile is mandatory or else Oracle Framework would’nt know where to search for the files to be imported.

2006-08-25T14:55:00.000Z

Oracle Payroll Accounting & Costing Basic Fundamentals

Golden Rule of accounting from ORACLE PAYROLL

Dr :- Actual Account

Cr :- Balancing Account

When LD(Labour Distributions) comes into play, “Actual Account in Payroll” is
replaced by “Clearing Account in Payroll”.

Based on above golden rule, for Earning Elements, following account
entry takes place .

The payroll run result value is +ve for “XX_Custom Basic Salary”

Step	Credit	Debit	Element	How to recognise
Post from Payroll	Balancing Account		XX_Custom Basic Salary	Pickup Balancing entries for LD elements when in Credit
Post from Payroll		~~Clearing Account~~	XX_Custom Basic Salary	Ignore debit entries for LD elements
Post from LD	~~Clearing Account~~		XX_Custom Basic Salary
Post from LD		Actual Account	XX_Custom Basic Salary

For Rebate element, following accounting takes place

( golden rule is violated, as the original entries need to be either fully
or partially reversed). The payroll run result value is –ve for Rebate Element

Step	Credit	Debit	Element
Post from Payroll		Balancing Account	NI Employers Rebate
Post from Payroll	~~Clearing Account~~		XX_Custom Basic Salary
Post from LD		~~Clearing Account~~	XX_Custom Basic Salary
Post from LD	Actual Account		XX_Custom Basic Salary

Conclusions( Not taking LD into account. These rules apply to costing entries in Payroll.) :-

1. The golden rule applies only when run result values are Positive.

a. If run result value is positive, then

v Credit Balancing Account

v Debit Actual Account

2. The golden rule DOES NOT apply when run result values are Negative.

a. If run result value is negative, then

v Credit Actual Account

v Debit Balancing Account

3. The distributed element has a negative run result value then

a. Debit entry is made against Actual Element ( i.e. “NI Employers Rebate” )

b. Multiple Credit entries are made against elements in Distribution Set
( i.e. XXX_BASIC_SALARY, XXX_LONDON_ALLOWANCE etc)

NI Employers Rebate	2.25	Dr	Balancing Account
Basic Salary	2.15	Cr	Actual Account
London Allowance	0.1	Cr	Actual Account

4. The distributed element has a positive run result value then

a. Credit entry is made against Actual Element ( i.e. “NI Employer” )

b. Multiple Debit entries are made against elements in Distribution Set
( i.e. XXX_BASIC_SALARY, XXX_LONDON_ALLOWANCE etc)

NI Employer	399.99	C	Balancing Account
Basic Salary	382.57	D	Actual Account
London Allowance	17.42	D	Actual Account

2006-08-24T12:50:00.000Z

Restart / Bounce Apache in Oracle Apps 11i

Please find the two commands that I use for bouncing the Apache $COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start

Of course this needs to be done in Middle Tier of Oracle Applications.
In case you have modified any java or class file in OAF ( Oracle Applications Framework ), then Apache bounce becomes mandatory for those changes to take effect.

In case you modify and load the XML Document in Oracle Framework, then it is noticed, for those XML changes to take effect, complete bounce of Middle Tier is required in Oracle Apps.

If your client is still stuck with AK Developer, then Apache bounce will be required after akload has been executed.

2006-08-21T21:26:00.000Z

Oracle Workflow Timeouts design with Bank Holidays and Weekends

Very often our client demand that they want to set reminder or timeout for Oracle Workflow notifications after number of working days. Well surely, they always wish to exclude not only the weekends but also the bank holidays.

Having implemented an approach for various Oracle based Workflows, I am now in a position to share a very simple methodology which uses recursion in a pl/sql function.

A site level profile option is assigned to global variable g_number_of_wait_days to capture the offset days. This happens during the package initialization of the pl/sql code.

For example, let’s assume that we need to escalate/timeout a workflow notification after 3 Working Days. In this case g_number_of_wait_days will be assigned a value of 3 via a profile option.

=>Ensure that you have a table say xxx_holiday_list, in which you capture bank holidays. This needs once per year maintenance only

=>In the Oracle workflow, create a activity names “Set Wait Days for Timeout” that executes a procedure named set_wait_days_for_timeout within a pl/sql package.

FUNCTION is_this_date_bank_holiday(p_date IN DATE) RETURN BOOLEAN IS
CURSOR c_get IS
SELECT 'x'
FROM xxx_holiday_list ibh
WHERE ibh.bank_holiday_date = p_date;

p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;

IF c_get%FOUND
THEN
CLOSE c_get;
RETURN TRUE;
END IF;

CLOSE c_get;
RETURN FALSE;
END is_this_date_bank_holiday;

FUNCTION get_next_working_date(p_estimated_next_working_date IN DATE)
RETURN DATE IS
BEGIN
IF is_this_date_bank_holiday(trunc(p_estimated_next_working_date))
THEN
RETURN get_next_working_date(p_estimated_next_working_date + 1);
END IF;

IF to_char(p_estimated_next_working_date, 'DY') = 'SAT'
THEN
RETURN get_next_working_date(p_estimated_next_working_date + 2);
END IF;

IF to_char(p_estimated_next_working_date, 'DY') = 'SUN'
THEN
RETURN get_next_working_date(p_estimated_next_working_date + 1);
END IF;
RETURN p_estimated_next_working_date;
END get_next_working_date;

FUNCTION get_next_working_date(
p_estimated_next_working_date IN DATE
,p_offset_days INTEGER )
RETURN DATE
IS
v_next_working_date DATE;
BEGIN
IF p_offset_days < 2
THEN
RETURN get_next_working_date( p_estimated_next_working_date + 1 );
END IF;

v_next_working_date := get_next_working_date( p_estimated_next_working_date + 1 );
RETURN get_next_working_date( v_next_working_date, p_offset_days - 1 );
END get_next_working_date;

FUNCTION get_timeout_minutes RETURN NUMBER IS
BEGIN
RETURN(get_next_working_date(SYSDATE
,g_number_of_wait_days) -
SYSDATE) * 24 * 60;
END get_timeout_minutes;

PROCEDURE set_wait_days_for_timeout
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
) IS
n_timeout_in_minutes NUMBER := get_timeout_minutes;
BEGIN
IF (funcmode != 'RUN')
THEN
RETURN;
END IF;
wf_engine.setitemattrnumber(itemtype => itemtype
,itemkey => itemkey
,aname => 'WAIT_DAYS_INCLUSIVE_WEEEKEND_H'
,avalue => n_timeout_in_minutes);

RESULT := 'COMPLETE:Y';
END set_wait_days_for_timeout;

Rather than spoon feeding you to explain the code, let me simply list the broader logic

End result of the above procedures and functions is to assign a value in minutes to an attribute workflow “WAIT_DAYS_INCLUSIVE_WEEEKEND_H”

Oracle workflow calls a procedure that in-turn calls a function names get_timeout_minutes.

Above function then makes a call to get_next_working_date. On a closer look you will notice that the function get_next_working_date is overloaded and gets passed in either a Date or Date and an offset. It recursively keeps skipping the days until it has gone past all the bank holidays and also all the weekends that might be encountered plus the offset days as defined in a site level profile option.

Finally, the approval notification is assigned the Workflow Attributes that dictates the timeout in minutes.

My 2 cents, to my knowledge I am not yet aware of an out of the box solution from Oracle. Until Oracle Workflow delivers one out of the box, please feel free to use this methodology.

Thanks,
Anil Passi

2006-08-21T20:31:00.000Z

My Introduction

Hello All,

I am a Oracle Apps Technie, with some functional background.

On this blog I intend to post all that I know and all that I will learn in the due course.

Comments are most welcome, both positive and negative.

Thanks
Anil Passi