You can use the UTL_MAILpackage to send email from pl/sql procedure.
utl_mail.send( sender => ‘firstname.lastname@example.org’,
recipients => ‘email@example.com,firstname.lastname@example.org’,
subject => ‘Test Mail from my Instance’,
message => ‘Test message’ );
As you can see the package can be used to send mails to multiple recipients as well.
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”.
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;
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
WHERE name = ‘VISION OPERATIONS SET’ ;
INSERT INTO gl_interface_control (je_source_name,interface_run_id,status,set_of_books_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
DBMS_OUTPUT.PUT_LINE(‘GL Import Submitted. Request Id : ‘||l_conc_id);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error while submitting the GL Import Program.’);
DBMS_OUTPUT.PUT_LINE(‘Error : ‘||SQLCODE||‘-’||SUBSTR(SQLERRM,1,200));
Hope this helps!! Will be back with another topic soon!!
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
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.
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:
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:
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:
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:
- Check the “Freeze Flexfield Definition” check box
- Click the “Compile” button on the form
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.
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!!
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.
WHERE payment_instruction_id = :l_payment_inst_id;
You can open the output in TOAD / SQL Developer.
For Web ADI documents to open you can follow the below steps:
1. Open Excel and go to File > Options
You will see a new window as shown below. Select “Trust Center” on the left navigation pane and then click on “Trust Center Settings”:
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.
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:
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”:
Scroll down and then “Enable” the “Allow status bar updates via script” option.
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.
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:
Once you click on that, you might have to login again to view the changes.
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
Please see the below screenshots for reference:
Login to Receivables Manager Responsibility:
Click on AutoInvoice function under Interfaces Sub Menu.
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:
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:
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)