Oracle Apps

Oracle Apps Blogs

Generate Positive Pay or ACH output in a Specified Directory

leave a comment »

Hi All.. Hope you are doing great. I would like to share about how we can generate the Positive Pay File / ACH Output in a particular directory in your server.

As always, this will be mostly screenshots so that it makes it easier for you to understand.

Navigate to Payables Manager > Setup > Payment > Payment Administrator

Click on the Payment Process Profiles Link. Check the below screenshot.

1

Query for your Payment Process Profile. In this blog, I have used US NACHA Generic Payment Process Profile. This is a standard payment process profile.

Search

Click on the Payment Process Profile and then navigate to “Payment Instruction Format” Tab. Here you will see the details that can be given for Payment File Information.
The payment file is the ACH Output.

Setup

 

Outbound Payment File Prefix           - If you want the ACH Output file to start with a particular name, this is the field that you should be using.

Outbound Payment File Extension – You can a file extension for the ACH Output file. For eg. “.txt”. However make sure that you dont include “.” in the field. The process while generating will take care of that.

Outbound Payment File Directory – You can specify the directory on the server where you want the ACH file to be generated.

ACH

 

Once you have entered all the details as show above, save the changes and then move to “Reporting” Tab. This is where you will be able to provide the details for the Positive Pay File generation.

See the below screenshot for the details. The details are self explanatory and similar to ACH File Generation.

PosPay

Hope this helps!!  Please provide your comments or ask any questions that you may have and I will try to answer them.!!

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

Follow

Get every new post delivered to your Inbox.

Join 683 other followers