Set override address workflow notification mailer from backend

Set override address workflow notification mailer from backend

In this post , We will discuss about Workflow Notification Mailer Override Email address in Oracle apps r12. We know that Workflow Notification Mailer send approval Email to approvers. But we cannot start Workflow Notification mailer in the Test Instance because if we will start the Workflow Mailer in Test then approvers start getting the approvers email from the test instance and it will create issues because , approver will assume this is the production approval email and it can effect business process. Usually we cannot start the Workflow notification mailer in test instance for this issue but some time , we need to perform testing with the Workflow Notification mailer by sending emails but we don't want the our approvers receives these approval test emails for this , oracle has provided override email address functionality in workflow notification mailer. In this what we can do , we can set the email address in this override email address field and after setting's this override email address then system start routing all emails from the system to that override email address or can send oracle workflow notifications to an email address. By this way , We can stop sending email to approvers and can route all the emails to specific email address for the testing.

Here below I will show steps to update override address from backend in workflow email notification oracle apps

Step1:- Oracle has provided script to update the Override email address in Workflow notification mailer.

Oracle has shared script ' afsvcpup.sql' to update the update override address from backend. You can find this script under FND_TOP/SQL directory.





Step2:- This script is recommend to execute in SQL Plus

Step3:- In SQL Plus execute the below script
@$FND_TOP/sql/afsvcpup.sql

Step4:- Once you will execute this script. It will ask the Component Id.

Enter Component Id: 10006 (for Workflow Notification Mailer)

 

Step5:- Once you will enter the Component Id: 10006 . It will ask the Comp Param Id to update : 10093 (for Override Email address)

Enter 10093 for Comp Param Id

 

Step6:- Once you will enter the Param Id then it will ask Email address to update in the Override Email address.

Enter email address in the Enter a value for the parameter.

Set override address workflow notification mailer from backend

  

 

 

 

How to set override address in workflow : Workflow Override Email Address in Oracle

How to set override address in workflow : Workflow Override Email Address in Oracle

In this post , We will discuss about Workflow Notification Mailer Override Email address in Oracle apps r12. We know that Workflow Notification Mailer send approval Email to approvers. But we cannot start Workflow Notification mailer in the Test Instance because if we will start the Workflow Mailer in Test then approvers start getting the approvers email from the test instance and it will create issues because , approver will assume this is the production approval email and it can effect business process. Usually we cannot start the Workflow notification mailer in test instance for this issue but some time , we need to perform testing with the Workflow Notification mailer by sending emails but we don't want the our approvers receives these approval test emails for this , oracle has provided override email address functionality in workflow notification mailer. In this what we can do , we can set the email address in this override email address field and after setting's this override email address then system start routing all emails from the system to that override email address or can send oracle workflow notifications to an email address. By this way , We can stop sending email to approvers and can route all the emails to specific email address for the testing.

 

Workflow email notification oracle apps

 

If oracle workflow override email address not working then you can verify the steps as below.

 

Here I will show you the steps to set override email address in workflow

 

Step1: - Go to Workflow Administrator Web Applications

 

 

Step2:- In the Workflow Notification Mailer. Click on the View Details Button as below.

 

 

Step3:- Click on the Button 'Set Override Address' as below and set the email address in this to route all the system workflow emails to this email

 

 

 

 

 

How to set override address in workflow : Workflow Override Email Address in Oracle

 

PO base tables in oracle apps r12 Complete Information's: Purchase Order Tables Complete Technical Flow

po base tables in oracle apps r12 Complete Information's

 

po_headers_all

 

This is the Purchase Order Headers Table in Oracle Apps. Here below you will find po_headers_all table columns details.

We can find the po number in po_headers_all in Segment1 Column of PO_HEADERS_ALL table.

 

 

This table have informations like PO Number, PO Type , Vendor Id ,Vendor Site Id,Currency ,PO Status.

 

 

PO_LINES_ALL

Here's Below we can find po_lines_all table description

 

This is the PO Lines Tables in Oracle Apps. In This Table We have ITem_Id , Qty , ITem Description ,UOM , UNIT_PRICE and many other important Columns.

 

 

PO_DISTRIBUTIONS_ALL

 

This Contains PO distributions Data. It handles the GL Account for the Line and Ship to locations too.

 

po_line_locations_all

This is the PO Lines Tables in Oracle Apps. In This Table We have ITem_Id , Qty , ITem Description ,UOM , UNIT_PRICE and many other important Columns. This table also provide information about total received qty against PO line and Total Invoiced Quantity too.

 

SQL Query To find the PO Lines from the Purchase Order

 

select A2.* from PO_HEADERS_ALL A1,PO_LINES_ALL A2

WHERE A1.PO_HEADER_ID=A2.PO_HEADER_ID

AND A1.SEGMENT1=:PO_NUMBER

AND A1.ORG_ID=:ORG_ID

 

SQL Query To find the PO Distributions Data from the Purchase Order

 

select A2.* from PO_HEADERS_ALL A1,PO_DISTRIBUTIONS_ALL A2

WHERE A1.PO_HEADER_ID=A2.PO_HEADER_ID

AND A1.SEGMENT1=:PO_NUMBER

AND A1.ORG_ID=:ORG_ID

 

SQL Query To find the PO Vendor Name from the Purchase Order

 

select A2.* from PO_HEADERS_ALL A1,AP_SUPPLIERS A2

WHERE A1.VENDOR_ID=A2.VENDOR_ID

AND A1.SEGMENT1=:PO_NUMBER

AND A1.ORG_ID=:ORG_ID

 

SQL Query To find the PO Vendor SITE from the Purchase Order

 

select A2.* from PO_HEADERS_ALL A1,AP_SUPPLIER_SITES_ALL A2

WHERE A1.VENDOR_SITE_ID=A2.VENDOR_SITE_ID

AND A1.SEGMENT1=:PO_NUMBER

AND A1.ORG_ID=:ORG_ID

 

 

SQL Query To find the PO Lines Inventory Items from the Purchase Order

 

select A2.* from PO_HEADERS_ALL A1,PO_LINES_ALL A2,MTL_SYSTEM_ITEMS_B A3 ,PO_DISTRIBUTIONS_ALL A4

WHERE A1.PO_HEADER_ID=A2.PO_HEADER_ID

AND A1.PO_HEADER_ID=A4.PO_HEADER_ID

AND A2.PO_LINE_ID=A4.PO_LINE_ID

AND A2.ITEM_ID=A3.INVENTORY_ITEM_ID

AND A3.ORGANIZATION_ID=A4.DESTINATION_ORGANIZATION_ID

AND A1.SEGMENT1=:PO_NUMBER

AND A1.ORG_ID=:ORG_ID

 

 

SQL Query To find the PO Buyers from the Purchase Order

 

 

SELECT A2.* FROM  PO_HEADERS_ALL A1,PER_ALL_PEOPLE_F A2

WHERE A1.AGMENT_ID=A2.PERSON_ID

AND A1.SEGMENT1=:PO_NUMBER

AND A1.ORG_ID=:ORG_ID

 

SQL Query To find the PO Receipts from the Purchase Order

 

po receipt tables in oracle apps r12

select a2.* from rcv_shipment_lines a1,rcv_shipment_headers a2 ,PO_HEADERS_ALL A3

where 1=1

and a1.SHIPMENT_HEADER_ID=a2.SHIPMENT_HEADER_ID

and a1.po_header_id=a3.po_header_id

and a3.SEGMENT1=:PO_NUMBER

 

SQL Query To find the PO Matching AP Invoice Numbers from the Purchase Order

 

select a3.* from ap_invoice_lines_all a1,ap_invoices_all a2,PO_HEADERS_ALL A3

where a1.po_header_id=a1.po_header_id

and a1.invoice_id=a2.invoice_id

and a3.SEGMENT1=:PO_NUMBER

 

 PO base tables in oracle apps r12 Complete Information's: Purchase Order Tables Complete Technical Flow

Workflow notification mailer not sending emails

Workflow notification mailer not sending emails in Oracle Apps

In this Post , We will discuss Workflow Notifications Mailer Issues. We often find issues in Oracle Apps where our Workflow notification mailer not sending emails. There are many Steps to be involved to do the Debugging of the Workflow notification mailer issues.

 

Step1:- First of all we need to confirm that our Notification Mailer and Listeners is working or Not. Some time Notification mailer got some issues and it goes down automatically. So this is also be one of the case of Workflow notification mailer not sending emails.

 

To Check Notification Mailer and Listeners are working or not We need to go to the

'Workflow Administrator Web Applications' responsibility.

 

 

 

 

 

Step2:- If our Notification Mailer is running fine , Then in Step 2 , we will check the WF_NOTIFICATIONS table to check the status of the Email for the notification.

 

 

All the oracle workflow notifications are stored in the WF_NOTIFICATIONS table.

Query the WF Notification table for the Specific Case (PO , PR, Expense Report #)

If workflow email notification not working in oracle then in step 2 we need to extract the data of the wf_notifications table for specific item_key.

 

I am sharing the query to check workflow mailer status as below.

 

Select * from WF_Notifications where notification_id = :notification_id and item_key = :item_key(po_header_id or requisition_header_id or Expense Report Number);

In this Table We have column 'Mail _Status' which determine the status of the email that whether email is send or not.

WF_NOTIFICATIONS Important Column Description

Mail_status:

• Sent: – Mails are successfully sent to the recipients.
• Error: – Mails are not delivered to the recipient due to invalid email address.
• Mail :- This Notification is Eligible for Sending Email but not sent yet. This status will be converted to Sent or Error.
• Null:- IF Notification Mail status Null then it means this notification is not eligible not sending emails. If its null then it could be an issue in the Setup side.

 

IF wf_notifications mail_status mail then this Notification is Eligible for Sending Email but not sent yet. This status will be converted to Sent or Error.

Status:

• Open: – Mails are sent to the recipient, but the user not read the email.
•  Closed: – Mail has been viewed by the recipient.
• Error: – Mail server is not able to deliver the message.
• Cancelled :- Workflow got cancelled
• Timeout :- Notification got timed out

 

Recipient_role should be approver name.

 

Step3:- If your Particular Notification Record Mail_Status showing 'Null' then it means there is some issue in the Setups.

 

If user is not receiving the Workflow Email's  , then we need to check the User Preferences settings for the user. This could be the reason of Mail_Status Column Showing null for the Notification for Particular Recipient

 

Go to Your Application Home Page , Then Go to the Top Right Side of the corner

Select the Preferences Option.

 

 

In the Preferences Under Notifications Select the Email Style ' HTML mail with attachments' 

 

 

This Information Stores in the WF_LOCAL_ROLES TABLE.

To Update Preferences From Backend.

update wf_local_roles set notification_preference = ‘MAILHTML’ where name = ‘USER_NAME'

Check if the Workflow notification has been sent or not

select mail_status, status from wf_notifications where notification_id= :notification_id

If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the email notification

If mail_status is SENT, its means workflow mailer has sent email

If mail_status is Null and status is OPEN, then no need to send email as notification preference of user is “Don’t send email”

Notification preference of user can be set by user by logging in application -> click on preference -> the notification preference

 

 

 

 

 

 

 

 

How to set profile options in oracle apps from backend . api to update profile option value

How to set profile options in oracle apps from backend . api to update profile option value

First of all We should have the name of the Profile option which we are going to change from database.

There are two name of the profile option in Oracle.

1.User Profile Option Name

2.Internal Profile Option Name

 

As an Example, We have profile  GL Ledger Name , and we want to set this from back end.

 

 

Sql Query to Extract the internal Name of the Profile Option

how to get the profile option value in oracle apps

 

SELECT profile_option_name

FROM fnd_profile_options_tl

WHERE user_profile_option_name = 'GL Ledger Name'

 

 

 

profile_option_name = GL_SET_OF_BKS_NAME

 

We have the Ledger Name ‘Vision Ledger’

Now We will set the Ledger in Oracle application from the Backend.

 

 

 

Profile_option_name = GL_SET_OF_BKS_NAME

 

We have the Ledger Name ‘Vision Ledger’

Now We will set the Ledger in Oracle application from the Backend.

 

This below  script helps to update or change the Profile options from the backend. We are using fnd_profile.save user level api to change or update the profiles in oracle application. We can also set fnd_profile.save responsibility level

Script to update profile values in oracle apps

Declare

Result Boolean;
BEGIN

   Result:= fnd_profile.SAVE (‘GL_SET_OF_BKS_NAME ‘, ‘Vision Ledger’, 'SITE');
   IF Result
   THEN
      DBMS_OUTPUT.put_line (‘Profile Updated’);
   ELSE
      DBMS_OUTPUT.put_line (‘Profile Not Updated’);
   END IF;
   COMMIT;
END;

 

 

 

Profile option query in oracle apps r12

Profile option query in oracle apps r12

This query helps to Extract the Profiles options and their values in Oracle apps R12. We will get all the details of the Profile options and their values with the Level of settings. In which level this profile has set.

 

 

query to find profile option values at user and responsibility level

SELECT po.profile_option_name "NAME",  

decode(to_char(pov.level_id),

'10001', 'SITE',

'10002', 'APP',

'10003', 'RESP',

'10005', 'SERVER',

'10006', 'ORG',

'10004', 'USER', '???') "LEV",

pov.profile_option_value

FROM   FND_PROFILE_OPTIONS po,

FND_PROFILE_OPTION_VALUES pov,

fnd_user usr,

fnd_application app,

fnd_responsibility rsp,

fnd_nodes svr,

hr_operating_units org

WHERE  1 =1

and    po.profile_option_name LIKE '&PROFILE%%'

AND    pov.application_id = po.application_id

AND    pov.profile_option_id = po.profile_option_id

AND    usr.user_id (+) = pov.level_value

AND    rsp.application_id (+) = pov.level_value_application_id

AND    rsp.responsibility_id (+) = pov.level_value

AND    app.application_id (+) = pov.level_value

AND    svr.node_id (+) = pov.level_value

AND    org.organization_id (+) = pov.level_value

ORDER BY "NAME", pov.level_id, "VALUE";

 

Query to find the responsibility attached to user in oracle apps

Query to find the responsibility attached to user in oracle apps.

 

This below query helps to extract the Oracle Applications User Responsibilities data. This query helps to find responsibility assigned to user in oracle apps r12. This query helps to find active users in oracle apps.

Query to find the responsibility attached to user in oracle apps

SELECT fu.user_name               ,

       frt.responsibility_name   ,

       furg.start_date           ,

       furg.end_date           ,      

       fr.responsibility_key      ,

       fa.application_short_name 

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa

 WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fa.application_id        =  fat.application_id

   AND fr.application_id        =  fat.application_id

   AND FU.END_DATE is null

   and furg.END_DATE is null

 ORDER BY fu.user_name,frt.responsibility_name;

 

Purchase Order Receiving Tables in Oracle apps. Receiving Tables Technical Flow

Purchase Order Receiving Tables in Oracle apps. Receiving Tables Technical Flow 

 

RCV_SHIPMENT_HEADERS

(important columns SHIPMENT_HEADER_ID,VENDOR_ID,RECEIPT_NUM,EMPLOYEE_ID,org_id)

 

This Table Store all information’s about Purchase Order Receiving Data. This Table Stores all the Receiving information of Purchase Orders.

HERE We get Receipt Number, Receipt Date , supplier , Supplier Site Information.

 

RCV_SHIPMENT_LINES

(this table describes the whole receiving like   line1 we receive that line2 we receive that by this table using po_header_id,po_line_id we join with ap_invoioce_distribution table)

 

(important columns are SHIPMENT_LINE_ID,SHIPMENT_HEADER_ID,LINE_NUM,QUANTITY_SHIPPED,QUANTITY_RECEIVED,ITEM_DESCRIPTION,ITEM_ID,SHIPMENT_LINE_STATUS_CODE(full or half rec),PO_HEADER_ID,PO_LINE_ID,TO_ORGANIZATION_ID)

 

THIS TABLE Stores information about Receipt Lines. It means which are the items we have received under the one Receipt. In Shipment Lines table

we get information about Inventory Item Id , Qty Received and Value too.

 

RCV_TRASNACTIONS

this table joins with rcv_shipment_headers,rcv_shipment_lines

 THIS SHOWS ALL THE STATUS OF ONE RECEVING OR RECEIPT NO  LIKE When they are RECEIVE ,When they are DELIVER,CORECT. In this INVENTORY_ITEM_ID  links with mtl_system_items_b invetory_item_id)

 

('RECEIVE','CORRECT','RETURN TO VENDOR','RETURN TO RECEIVING') These are the Transaction types common in this table

 

(transaction_id,SHIPMENT_HEADER_ID,UNIT_OF_MEASURE,QUANTITY,TRANSACTION_DATE,PO_HEADER_ID,PO_LINE_ID,PO_LINE_LOCATION_ID,PO_DISTRIBUTION_ID,DESTINATION_TYPE_CODE(EXP,RCV,INV)

 

This Table Store all receiving information again Single Receipt Number. When we receive the Item, Them It creates 1st record for Action Type '

RECEIVE’, when we deliver this received item to Inventory then it creates Transaction Type 'DELIVER'

Purchase Order Receiving Accounting Tables in Oracle Apps

 

RCV_RECEIVING_SUB_LEDGER

 

 (THIS TABLE SHOW THE ACCOUNTING OF EACH TRANSACTION ID LIKE WHEN ITEM REC THEN WHICH ACCOUNT CR, DR)

 (IMPORTANT COLOUMNS -  RCV_TRANSACTION_ID (IT LINKS RCV_TRANSACTION TABLE TO THIS TABLE), SET_OF_BOOKS_ID,CODE_COMBINATION_ID,ACCOUNTING_DATE,ACCOUNTED_DR,CR,PERIOD_NAME,RCV_SUB_LEDGER_ID)

 

BUT THE LIMITATION OF ABOVE TABLE IS THAT IT SHOWS THE ACCOUNTING OF THOSE TRANSACTION WHICH ARE EXPENSE IF THEY ARE INVENTORY THEN IT SHOWS THE ACCOUNTING OF RECEVING TRANSACTION NOT SHOWING THE TRANSACTION OF DELIVER TYPE TRANSACTION)

 

FOR SHOWING THE ACCOUNTING OF DELVER TRANSACTION IN INVENTORY WE SHOULD USE THIS BELOW TABLE

 

MTL_TRANSACTION_ACCOUNTS

 

(REFRENCE ACCOUNT,BASE_TRANSACTION_VALUE)

  THIS TABLE STORE THE INFORMATION OF ACCOUNTING OF INVENTORI ITEM DELIVER.

 

MTL_TRANSACTION_ACCOUNTS  WE REACH TO THIS TABLE BY MTL_MATERIAL_TRANSACTIONS(TRANSACTION_ID)  AND WE REACH TO THIS TABLE BY RCV_TRANSACTIONS(RCV_TRANSACTION_ID)

 

MTL_TRANSACTION_ACCOUNTS HAS COLOUMN REFRENCE ACCOUNT WHICH ARE ACCOUNTING CODE, BASE_TRANSACTION_VALUE  AMOUNT

 

MTL_MATERIAL_TRANSACTIONS

 

This Table Store all information about Inventory transactions. when any transaction happens, which affect the Inventory Stock then system creates

record for this transaction in this table. For Purchase Order Receipt When we receive the Item then it did not create any record in this table, but when we deliver the

receipt qty to inventory then it create record for this delivery transation in this table. We can join this record by 'transaction_id' with 'rcv_transaction_id' in rcv_transactions.

FOR EXAMPLE

 

WHEN WE do purchase order receiving then MATERIAL RECEIVING ACCOUNT DEBT AND ACCRUAL CREDIT 

 

WHEN WE do deliver then MATERIAL RECEIVING ACCOUNT CREDIT AND DESTINATION ACCOUNT to which account belong DEBIT 

 

AND When we create the invoice against the Purchase Order Receipt then it DEBITS THE ACRUAL ACCOUNT)

 

JAI_RCV_JOURNAL_ENTRIES(TRANSACTION_ID) THIS IS USED FOR SHOWING THE TAX ACCOUNTING OF ONE MRN NO