Oracle Apps

Oracle Apps Blogs

UTL_MAIL to Send Email form PL SQL

leave a comment »

You can use the UTL_MAILpackage to send email from pl/sql procedure.

begin

utl_mail.send( sender        => ‘oracle@mydomain.com’,

recipients                               => ‘user1@mydomain.com,user2@mydomain.com’,

subject                                    => ‘Test Mail from my Instance’,

message                                 => ‘Test message’ );

end;

As you can see the package can be used to send mails to multiple recipients as well.

Written by sarunraj

04/02/2014 at 4:40 PM

Submit GL Journal Import Programmatically

leave a comment »

Hi All.. This post describes how to call the GL Journal Import program from your pl/sql procedure. We will be looking at importing AR (Receivable) Journal Entries.

Pre Requisite step is to insert the journal records to be imported into GL_INTERFACE Table. The status of the records in the GL_INTERFACE table for new records should be “NEW”.

DECLARE

l_conc_id                NUMBER;
l_int_run_id           NUMBER;
l_access_set_id    NUMBER;
l_org_id                   NUMBER := 81;
l_sob_id                   NUMBER := 101;
l_user_id                 NUMBER := FND_GLOBAL.USER_ID;
l_resp_id                 NUMBER := FND_GLOBAL.RESP_ID;
l_resp_app_id       NUMBER := FND_GLOBAL.RESP_APPL_ID;

BEGIN

fnd_global.apps_initialize ( user_id             => l_user_id              –User Id
,resp_id             => l_resp_id              –Responsibility Id
,resp_appl_id => l_resp_app_id); –Responsibility Application Id

mo_global.set_policy_context(‘S’,l_org_id);

SELECT   gl_journal_import_s.NEXTVAL
INTO    l_int_run_id
FROM   dual;

SELECT   access_set_id
INTO    l_access_set_id
FROM   gl_access_sets
WHERE   name = ‘VISION OPERATIONS SET’ ;

INSERT INTO gl_interface_control (je_source_name,interface_run_id,status,set_of_books_id)
VALUES (‘Receivables’,l_int_run_id,‘S’,l_sob_id);

l_conc_id := fnd_request.submit_request(application     => ‘SQLGL’
,program          => ‘GLLEZL’
,description    => NULL
,start_time      => SYSDATE
,sub_request  => FALSE
,argument1     => l_int_run_id        –interface run id
,argument2     => l_access_set_id –data access set_id
,argument3     => ‘N’                              –post to suspense
,argument4     => NULL                      –from date
,argument5     => NULL                      –to date
,argument6     => ‘N’                             –summary mode
,argument7     => ‘N’                             –import DFF
,argument8     => ‘Y’                             –backward mode
);

COMMIT;

DBMS_OUTPUT.PUT_LINE(‘GL Import Submitted. Request Id : ‘||l_conc_id);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(‘Error while submitting the GL Import Program.’);
DBMS_OUTPUT.PUT_LINE(‘Error : ‘||SQLCODE||‘-’||SUBSTR(SQLERRM,1,200));

END;

Hope this helps!! Will be back with another topic soon!!

Written by sarunraj

04/01/2014 at 11:55 PM

Make City, State, Postal Code mandatory for Supplier / Supplier Site Address

leave a comment »

Hello Everyone. Wanted to share another article you might be interested in.

Requirement is to make the City, State and Post Code fields on the address (Supplier / Supplier Site) as mandatory. As you know, out of the box, these fields are non-mandatory. How do we achieve this? Well, personalization is not the way to get this done.

1. Get the values of the below Profile Options to decide the geographic location / Country being used in the instance

  • ICX: Territory
  • HZ: Reference Territory

Navigation :- System Administrator > Profile > System

Profile_Options

In my instance both the profile options have a value “United States”. The next step is to identify the address style being used by the Country.

2. Find the address style being used by the country / geographic location (You got the country / geographic location value in Step 1)

Navigate to “Receivables Manager > Setup > Countries”.  Query for “United States” and note the Address Style being used. Check the below image for reference.

Country_AddressStyle

Now comes the interesting steps.

3. Query the Address DFF

Navigation :- Receivables Manager > Setup > Financials > Flexfields > Descriptive > Segments

Query for Application – “Receivables” and Title – “Address”.
Check for the address style being used in our system. In my instance it is “POSTAL_ADDR_US” (you got this from Step 2). Check for the value in the “Code” field the DFF Form. Check the below image for reference:

DFF_Form1

Uncheck the “Freeze Flexfield Definition” check box and click on “Segments” button. The new form will show the segments along with the Table Column Name and the Value Set being used for each of the Segments. You can also see the “State”, “City” and “Postal Code” segments. Check the below image for reference:

DFF_Form2

Now let us make the City field as mandatory.

4. Make the segment mandatory

Click on “City” segment and then Click on “Open”. This will open a new form shown as below. You need to “check” the Required checkbox in this form.
Voila and we are done!! Check the below image for reference:

DFF_Form3

Save and repeat the steps for “State” and “Postal Code” Segments.

5. Compile DFF Definition

Once you have made the changes to the segments, navigate to the DFF Form and perform the below steps:

  1. Check the “Freeze Flexfield Definition” check box
  2. Click the “Compile” button on the form

DFF_Compile

6. Log out and Bounce the web servers

Make sure the Compile Flexfield Definition completed successfully. Log out of the instance. It does not harm to get the web servers bounced. Although for me it worked with the bouncing the web servers.

7. Login and be amazed at your own work!!

Navigate to  “Payables Manager > Suppliers > Entry”

Query for a supplier and then navigate to “Address Book”. Click “Update” on the address. You will see that the “City”, “State” and “Postal Code” fields are now mandatory. Check the below image for reference.

Supp_Address

There you go!! The fields are mandatory. Time to get a coffee or a beer or whatever drink you like!!

Enjoy your time!! Till next Time!!

XML Output for AP Check Payments in R12

leave a comment »

How to get the XML output for the check payment (Format Payment Instruction) program in R12? I have asked this many times to myself.

Usually we look at the log file and use the XML tags from the log file. When the volume of payments made are high, the XML tags are truncated in the log file. So you wont be able to get the entire XML tags. So how do we get it??

IBY_TRXN_DOCUMENTS Table stores the XML generated during the Format Payment Instruction program. The following SQL Statement can be used to get the XML output.

SELECT DOCUMENT
FROM IBY_TRXN_DOCUMENTS
WHERE payment_instruction_id = :l_payment_inst_id;

You can open the output in TOAD / SQL Developer.

 

Written by sarunraj

11/13/2013 at 11:44 PM

Excel and IE Setups for Oracle Web ADI

leave a comment »

For Web ADI documents to open you can follow the below steps:

1. Open Excel and go to File > Options

Navigation_1

You will see a new window as shown below. Select “Trust Center” on the left navigation pane and then click on “Trust Center Settings”:

Navigation_2

Select “MAcro Settings” on the left navigation pane. Under Macro Settings, select “Enable all macros” . Under “Developer Macro Settings”, select “Trust access to the VBA project object model”.  See the image below for more details.

Navigation_3

Next step is to navigate to the “Protected View” option on the left navigation pane. once you are there, uncheck all the protected view options as show below:

Navigation_4

Click “Ok” and close Excel.

Open IE and navigage to IE > Internet Options and select “Security Tab”. Select zone as “Internet” and click on “Custom Level”:

IE_1

Scroll down and then “Enable” the “Allow status bar updates via script” option.

IE_2

Close and Re-Open IE. Go ahead and try to open Web ADI Documents and it should be working fine.

Comments are welcome!! if there are other ways to accomplish this, please share your thoughts on that as well.

 

Written by sarunraj

11/04/2013 at 1:35 PM

Posted in Oracle Apps

Tagged with , ,

Issues with IE 10 and Oracle EBS Login

leave a comment »

If you are using IE 10, then you might have issues when you login to EBS.

The list of responsibilities will be shown as “undefined”. This is because of the incompatibilities with IE 10 version.

You can get this resolved by clicking the “Compatibility View” option on IE. Please see the image below:

Compatibility

Once you click on that, you might have to login again to view the changes.

 

Written by sarunraj

10/28/2013 at 2:48 PM

APP-FND-1564: ORACLE error 24347 in FDFGVD

leave a comment »

After upgrading to 12.1.3, I have been getting the below error while submitting the Auto Invoice Master Program from AR Responsibility.

APP-FND-1564: ORACLE error 24347 in FDFGVD
Cause: FDFGVD failed due to ORA-24347: Warning of a NULL column in an aggregate function.

SELECT BATCH_SOURCE_ID,NAME VALUE, DESCRIPTION DESCRIPTION, NVL(‘N’, ‘N’),
NVL(TO_NUMBER(NULL), -1), NULL, NVL(‘Y’, ‘Y’), NVL(TO_CHAR(TO_DATE(NULL),
‘J’), 0), NVL(TO_CHAR(TO_DATE(NULL), ‘J’), 0) FROM AR_AI_BATCH_SOURCE_V WHERE
( GET_ALL = DECODE(:b1, -99, ‘Y’,’N’) AND ORG_ID = DECODE(:b2, -99,
ORG_ID, :b3) ) AND BATCH_SOURCE_ID = :X and was executed from the file
&ERRFILE.

Please see the below screenshots for reference:

Login to Receivables Manager Responsibility:

Receivables Manager > Interfaces > Auto Invoice

Click on AutoInvoice function under Interfaces Sub Menu.

Submit Request

Click “Ok”. The SRS window will be opening. Enter all the mandatory parameters. The organization parameter value defaults to “All”.

Once you have entered the mandatory parameters, click on “Submit”. You will receive the below error:

5

Work Around:- Select a different value for Organization parameter. You can select the operating unit value (US Operating Unit) and then submit the program.

See the screenshot below:

6

References:

The following note from Oracle Support will be helpful.

AutoInvoice Error: Batch Source List of Values Raises Error: APP-FND-1564: ORACLE error 24347 in FDFGVD (Doc ID 1406558.1)

Written by sarunraj

10/09/2013 at 6:21 PM

Follow

Get every new post delivered to your Inbox.

Join 679 other followers