How to call Concurrent Program through OAF Personalization

Requirement: From OAF page i have to call a custom procedure for some activity.


Solution:
Create a button through OAF personalization like below. Concurrent program name is XXPRCTEST. I register this under Application Developer.

ID: TestBtn
Prompt: Submit Report
OA.jsp?akRegionCode=FNDCPPROGRAMPAGE&akRegionApplicationId=0&programApplName=FND&programRegion=Hide&scheduleRegion=Hide¬ifyRegion=Hide&printRegion=Hide&&programName=XXPRCTEST
Target Frame: _blank

This will open a new submit window.

Thanks & Regards,
Anto Joe Natesh I

FND Message

Calling messages: Following standard procedure can be called to show predefined oracle applications message through forms or concurrent programs.

FND_MESSAGE.CLEAR – Clears the message stack of all the messages.

FND_MESSAGE.DEBUG – Immediately displays the string passed to it as input parameter.

FND_MESSAGE.ERASE – Clears the oracle Forms status line.

FND_MESSAGE.ERROR - Displays an error message in an Oracle Forms modal window or a concurrent program log file. (Example: “Invalid value entered.”). It takes its message from the stack, displays the message, and then clears all the messages from the message stack.

FND_MESSAGE.GET - Retrieves a translated and token–substituted message from the message stack and then clears that message from the message stack. This could be used for getting a translated message for a forms built–in or other function. Assumes you have already called FND_MESSAGE.SET_NAME and, if necessary,
FND_MESSAGE.SET_TOKEN. It returns up to 2000 bytes of message.

FND_MESSAGE.HINT - Displays a message in the Oracle Forms status line. It takes its message from the stack, displays the message, and then clears that message from the message stack. The user may still need to acknowledge the message if another message immediately comes onto the message line.

FND_MESSAGE.QUESTION - Displays a message and up to three buttons in an Oracle Forms modal window. (Example: “Please choose one of the following actions: ”) It takes its message from the stack, and clears that message. After the user selects a button, It returns the number of the button selected.
For each button, you must define or use an existing message in Message Dictionary (under the Oracle Application Object Library application) that contains the text of the button. This routine looks for your button name message in the Oracle Application Object Library messages, so when you define your message, you must associate it with Oracle Application Object Library (the “FND” application) instead of your application.

FND_MESSAGE.RETRIEVE - Retrieves a message from the database server, translates and substitutes tokens, and sets the message on the message stack.

FND_MESSAGE.SET_NAME - Retrieves your message from Message Dictionary and sets it on the message stack. You call it once for each message you use in your client–side PL/SQL procedure. You must call this procedure before you call FND_MESSAGE.SET_TOKEN.

FND_MESSAGE.SET_STRING - Takes an input string and sets it directly on the message stack. The string does not need to be defined in the Messages window. These strings may be hard coded into the form and are not translated like messages defined in Message Dictionary.

FND_MESSAGE.SET_TOKEN - Substitutes a message token with a value you specify. You call FND_MESSAGE.SET_TOKEN once for each token/value pair in a message. The optional translate parameter can be set to TRUE to indicate that the value should be translated before substitution. (The value should be translated if it is, itself, a Message Dictionary message name.)

FND_MESSAGE.SHOW - Displays an informational message in an Oracle Forms modal window or a concurrent program log file. (Example: “To complete this function, please enter the following... ”). It takes its message from the stack, displays the message, and then clears only that message from the message stack.

FND_MESSAGE.WARN - Displays a warning message in an Oracle Forms modal window and allows the user to either accept or cancel the current operation. (Example: “Do you wish to proceed with the current operation?”) FND_MESSAGE.WARN returns TRUE if the user accepts the message (that is, clicks OK), or FALSE if the user cancels. It takes its message from the stack, displays the message, and clears that message from the message stack.
Most frequently used procedures are FND_MESSAGE.SET_NAME and FND_MESSAGE.SET_TOKEN

Example
/*Here FND_MESSAGE.SET_NAME is used to retrieve the message and put it on the message stack. Message name is ‘WANT TO CONTINUE’.*/

FND_MESSAGE.SET_NAME (’FND’, ‘WANT TO CONTINUE);

/*Next FND_MESSAGE.SET_TOKEN is used to replace the token ‘PROCEDURE’ with text ‘Compiling this flexfield’.

FND_MESSAGE.SET_TOKEN (’PROCEDURE’, ’Compiling this flexfield’);

IF FND_MESSAGE.WARN THEN
/* User want to continue */

ELSE
/* User want to cancel*/

END;

Thanks & Regards,
Anto Joe Natesh

Add Responsibility using Sqlplus

BEGIN
FND_USER_RESP_GROUPS_API.LOAD_ROW
(X_USER_NAME => 'ANATESH',
X_RESP_KEY => 'APPLICATION_DEVELOPER',
X_APP_SHORT_NAME => 'FND',
X_SECURITY_GROUP => 'STANDARD',
X_OWNER => 'SYSADMIN',
X_START_DATE => Trunc(sysdate),
X_END_DATE => NULL,
X_DESCRIPTION => NULL,
X_LAST_UPDATE_DATE => Trunc(sysdate));
COMMIT;
END;

Thanks & Regards,
Anto Joe Natesh

Resolving ORACLE ERROR:ORA-28000: the account is locked

From your command prompt, type
sqlplus "/ as sysdba"

Once logged in as SYSDBA, you need to unlock the scott account
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;

Sales Order Line Status Flow and Meaning

Below are some of the different statuses of Sales Order Line with brief explanation

OM = Order Management Sales order form
SE = Shipping Transactions or execution form

1) Entered (OM): Order is saved but not booked

2) Booked (OM): Order is Booked.

3) Awaiting Shipping (OM): Order is booked but lines are not yet picked.
Navigating to Shipping Execution, the delivery line status flow is:

4) Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used

5) Released to Warehouse (SE): Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.

6) Ready to Release (SE): Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.

7) Backordered(SE): The status of Backorderd is assigned to a line under the following circumstances.

·         The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.

·         At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.

·         The user manually Backorders the entire delivery.

8) Shipped (SE): The delivery line is shipped confirmed.

9) Confirmed (SE): The delivery line is shipped or backordered and the trip stops are open.

10) Picked (OM): Pick release is complete, both allocations and pick confirm

11) Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred

12) Interfaced (SE): The delivery line is shipped and Inventory interface concurrent process is complete.

13) Awaiting Fulfillment (OM): When fulfillment set is used, Not all shippable lines in a fulfillment set or a
configuration are fulfilled

14) Fulfilled (OM): All lines in a fulfillment set are fulfilled.

15) Interfaced to Receivables (OM): The order is linked with Receivables and the invoice is created.

16) Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

17) Closed (OM): Closed indicates that the line is closed.

18) Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.

Thanks & Regards,

Anto Joe Natesh

Insert BLOB image file in oracle database table

Here we will discuss how to insert BLOB file in the database. For this we will create a table and then a procedure that will be used to insert records in the table.

Use following script to create an employee table

CREATE TABLE SV_EMP_PHOTO
(
  ID          NUMBER(3) NOT NULL,
  PHOTO_NAME  VARCHAR2(40),
  PHOTO_RAW   BLOB,
  EMP_NAME    VARCHAR2(80)
)


Create a directory where the photos will be stored. I am creating a directory in UNIX as our database is created in UNIX.

Create directory SV_PHOTO_DIR as '/u002/app/applmgr/empphoto'


Script to create a procedure SV_LOAD_IMAGE that will insert records in the table.


CREATE OR REPLACE PROCEDURE sv_load_image (
   p_id                NUMBER
 , p_emp_name     IN   VARCHAR2
 , p_photo_name   IN   VARCHAR2
)
IS
   l_source   BFILE;
   l_dest     BLOB;
   l_length   BINARY_INTEGER;
BEGIN
   l_source := BFILENAME ('SV_PHOTO_DIR', p_photo_name);

   INSERT INTO sv_emp_photo
               (ID
              , photo_name
              , emp_name
              , photo_raw
               )
   VALUES      (p_id
              , p_photo_name
              , p_emp_name
              , EMPTY_BLOB ()
               )
   RETURNING   photo_raw
   INTO        l_dest;

   -- lock record
   SELECT     photo_raw
   INTO       l_dest
   FROM       sv_emp_photo
   WHERE      ID = p_id AND photo_name = p_photo_name
   FOR UPDATE;

   -- open the file
   DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
   -- get length
   l_length := DBMS_LOB.getlength (l_source);
   -- read the file and store in the destination
   DBMS_LOB.loadfromfile (l_dest, l_source, l_length);

   -- update the blob field with destination
   UPDATE sv_emp_photo
   SET photo_raw = l_dest
   WHERE  ID = p_id AND photo_name = p_photo_name;

   -- close file
   DBMS_LOB.fileclose (l_source);
END --sv_load_image;
/


I have copied few .jpg images in /u002/app/applmgr/empphoto in UNIX.
Execute the procedure as follows to create record in database


exec sv_load_image(1,'Pavki','one.jpg')
exec sv_load_image(2,'Suresh','two.jpg')
exec sv_load_image(3,'Rachna','three.jpg')

List of Tables for Forms Personalization

Insert BLOB image file in oracle database table
Below is the list of tables that are populated when any Forms Personalization is done

FND_FORM_CUSTOM_RULES
FND_FORM_CUSTOM_SCOPES
FND_FORM_CUSTOM_ACTIONS
FND_FORM_CUSTOM_PARAMS
FND_FORM_CUSTOM_PROP_VALUES
FND_FORM_CUSTOM_PROP_LIST

You can FNDLOAD utility to move one instance to another.

Or export and import this records from one instance to other. But make sure the sequences are altered :)