Frequently used PO Queries

PO QUERIES

 Technical Queries related to Oracle Purchasing
] TO LIST OUT ALL CANCEL REQUISITIONS:->> list My cancel Requistion
select prh.REQUISITION_HEADER_ID, prh.PREPARER_ID , prh.SEGMENT1 "REQ NUM", trunc(prh.CREATION_DATE), prh.DESCRIPTION, prh.NOTE_TO_AUTHORIZERfrom apps.Po_Requisition_headers_all prh, apps.po_action_history pah where Action_code='CANCEL' and pah.object_type_code='REQUISITION'
and pah.object_id=prh.REQUISITION_HEADER_ID

2] TO LIST ALL INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER
>> Select RQH.SEGMENT1 REQ_NUM,RQL.LINE_NUM,RQL.REQUISITION_HEADER_ID ,RQL.REQUISITION_LINE_ID,RQL.ITEM_ID ,RQL.UNIT_MEAS_LOOKUP_CODE ,RQL.UNIT_PRICE ,RQL.QUANTITY ,RQL.QUANTITY_CANCELLED,RQL.QUANTITY_DELIVERED ,RQL.CANCEL_FLAG ,RQL.SOURCE_TYPE_CODE ,RQL.SOURCE_ORGANIZATION_ID ,RQL.DESTINATION_ORGANIZATION_ID,RQH.TRANSFERRED_TO_OE_FLAGfromPO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQHwhereRQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_IDand RQL.SOURCE_TYPE_CODE = 'INVENTORY'and RQL.SOURCE_ORGANIZATION_ID is not nulland not exists (select 'existing internal order'from OE_ORDER_LINES_ALL LINwhere LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_IDand LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

3] Display what requisition and PO are linked(Relation with Requisition and PO )>>
select r.segment1 "Req Num", p.segment1 "PO Num"from po_headers_all p, po_distributions_all d,po_req_distributions_all rd, po_requisition_lines_all rl,po_requisition_headers_all r where p.po_header_id = d.po_header_id and d.req_distribution_id = rd.distribution_id and rd.requisition_line_id = rl.requisition_line_id and rl.requisition_header_id = r.requisition_header_id

4] List all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO. (Purchase Requisition without a Purchase Order)>>
select prh.segment1 "PR NUM", trunc(prh.creation_date) "CREATED ON", trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #", msi.segment1 "Item Num", prl.item_description "Description", prl.quantity "Qty", trunc(prl.need_by_date) "Required By", ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER" from po.po_requisition_headers_all prh, po.po_requisition_lines_all prl, apps.per_people_f ppf1, (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, po.po_req_distributions_all prd, inv.mtl_system_items_b msi, po.po_line_locations_all pll, po.po_lines_all pl, po.po_headers_all ph WHERE prh.requisition_header_id = prl.requisition_header_id and prl.requisition_line_id = prd.requisition_line_id and ppf1.person_id = prh.preparer_id and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and ppf2.agent_id(+) = msi.buyer_id and msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id and pll.line_location_id(+) = prl.line_location_id and pll.po_header_id = ph.po_header_id(+) AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PRH.AUTHORIZATION_STATUS = 'APPROVED' AND PLL.LINE_LOCATION_ID IS NULL AND PRL.CLOSED_CODE IS NULL AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
ORDER BY 1,2

5] list all information form PR to PO …as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.>> LIST AND ALL DATA ENTRY FROM PR TILL PO
select distinct u.description "Requestor", porh.segment1 as "Req Number", trunc(porh.Creation_Date) "Created On", pord.LAST_UPDATED_BY, porh.Authorization_Status "Status", porh.Description "Description", poh.segment1 "PO Number", trunc(poh.Creation_date) "PO Creation Date", poh.AUTHORIZATION_STATUS "PO Status", trunc(poh.Approved_Date) "Approved Date"from apps.po_headers_all poh, apps.po_distributions_all pod, apps.po_req_distributions_all pord, apps.po_requisition_lines_all porl, apps.po_requisition_headers_all porh, apps.fnd_user u where porh.requisition_header_id = porl.requisition_header_id and porl.requisition_line_id = pord.requisition_line_id and pord.distribution_id = pod.req_distribution_id(+) and pod.po_header_id = poh.po_header_id(+) and porh.created_by = u.user_id
order by 2

6] Identifying all PO’s which does not have any PR’s>>LIST ALL PURCHASE REQUISITION WITHOUT A PURCHASE ORDER THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO. select prh.segment1 "PR NUM", trunc(prh.creation_date) "CREATED ON", trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #", msi.segment1 "Item Num", prl.item_description "Description", prl.quantity "Qty", trunc(prl.need_by_date) "Required By", ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER" from po.po_requisition_headers_all prh, po.po_requisition_lines_all prl, apps.per_people_f ppf1, (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, po.po_req_distributions_all prd, inv.mtl_system_items_b msi, po.po_line_locations_all pll, po.po_lines_all pl, po.po_headers_all ph WHERE prh.requisition_header_id = prl.requisition_header_id and prl.requisition_line_id = prd.requisition_line_id and ppf1.person_id = prh.preparer_id and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and ppf2.agent_id(+) = msi.buyer_id and msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id and pll.line_location_id(+) = prl.line_location_id and pll.po_header_id = ph.po_header_id(+) AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PRH.AUTHORIZATION_STATUS = 'APPROVED' AND PLL.LINE_LOCATION_ID IS NULL AND PRL.CLOSED_CODE IS NULL AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y' ORDER BY 1,2

7] Relation between Requisition and PO tables>>Here is link:
PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_IDPO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_IDPO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1
What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.
--You need to find table which hold PO Approval path…
These two table keeps the data:
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES

8] List all the PO’s with there approval ,invoice and Payment Details>>LIST AND PO WITH THERE APPROVAL , INVOICE AND PAYMENT DETAILSselect a.org_id "ORG ID", E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE", f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY", to_char(trunc(d.CREATION_DATE)) "PO Date", d.segment1 "PO NUM",d.type_lookup_code "PO Type", c.quantity_ordered "QTY ORDERED", c.quantity_cancelled "QTY CANCELLED", g.item_id "ITEM ID" , g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE", (NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount", (select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where ph.po_header_ID = d.po_header_id)"PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT", to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE", a.invoice_num "INVOICE NUMBER", (select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?", a.amount_paid,h.amount, h.check_id, h.invoice_payment_id "Payment Id", i.check_number "Cheque Number", to_char(trunc(i.check_DATE)) "PAYMENT DATE" FROM AP.AP_INVOICES_ALL A, AP.AP_INVOICE_DISTRIBUTIONS_ALL B, PO.PO_DISTRIBUTIONS_ALL C, PO.PO_HEADERS_ALL D, PO.PO_VENDORS E, PO.PO_VENDOR_SITES_ALL F, PO.PO_LINES_ALL G, AP.AP_INVOICE_PAYMENTS_ALL H, AP.AP_CHECKS_ALL I where a.invoice_id = b.invoice_id and b.po_distribution_id = c. po_distribution_id (+) and c.po_header_id = d.po_header_id (+) and e.vendor_id (+) = d.VENDOR_ID and f.vendor_site_id (+) = d.vendor_site_id and d.po_header_id = g.po_header_id and c.po_line_id = g.po_line_id and a.invoice_id = h.invoice_id and h.check_id = i.check_id and f.vendor_site_id = i.vendor_site_id and c.PO_HEADER_ID is not null and a.payment_status_flag = 'Y'
and d.type_lookup_code != 'BLANKET'

10] To know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.
The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.
These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) isPurchasing.
Budgetary Records from PO (These include reservations, reversals and cancellations):
REFERENCE_1- Source (PO or REQ)
REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id orpo_requisition_headers_all.requisition_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id orpo_req_distributions_all.distribution_id)
REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 orpo_requisition_headers_all.segment1)
REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)
Accrual Records from PO:
REFERENCE_1- Source (PO)
REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)
Take a note for Period end accruals, the REFERENCE_5 column is not used.

11] List all open PO'S>> select h.segment1 "PO NUM", h.authorization_status "STATUS", l.line_num "SEQ NUM", ll.line_location_id, d.po_distribution_id , h.type_lookup_code "TYPE" from po.po_headers_all h, po.po_lines_all l, po.po_line_locations_all ll, po.po_distributions_all d where h.po_header_id = l.po_header_id and ll.po_line_id = l.po_Line_id and ll.line_location_id = d.line_location_id and h.closed_date is null
and h.type_lookup_code not in ('QUOTATION')

12] There are different authorization_status can a requisition have.Approved
Cancelled
In Process
Incomplete
Pre-Approved
Rejected
and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it’s closed_code becomes ‘FINALLY CLOSED’.

13] A standard Quotations one that you can tie back to a PO.Navigate to RFQ -> Auto create -> enter a PO and reference it back.14] To debug for a PO , where should I start.Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.
Stage 1: PO Creation :
PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =;
select * from po_headers_all where po_header_id =;
po_lines_all
select * from po_lines_all where po_header_id =;
po_line_locations_all
select * from po_line_locations_all where po_header_id =;
po_distributions_all
select * from po_distributions_all where po_header_id =;
po_releases_all
SELECT * FROM po_releases_all WHERE po_header_id =;
Stage 2: Once PO is received data is moved to respective receving tables and inventory tables
RCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers where shipment_header_id in(select shipment_header_id from rcv_shipment_lineswhere po_header_id =);
RCV_SHIPMENT_LINES
select * from rcv_shipment_lines where po_header_id =;
RCV_TRANSACTIONS
select * from rcv_transactions where po_header_id =;
RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN(select transaction_id from rcv_transactionswhere po_header_id =);
RCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);
RCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_detailswhere rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);
MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =;
MTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =);
Stage 3: Invoicing details
AP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =);
AP_INVOICES_ALL
select * from ap_invoices_all where invoice_id in(select invoice_id from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =));
Stage 4 : Many Time there is tie up with Project related PO
PA_EXPENDITURE_ITEMS_ALL
select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in( select to_char(transaction_id) from mtl_material_transactionswhere transaction_source_id = );
Stage 5 : General Ledger
Prompt 17. GL_BC_PACKETS ..This is for encumbrances
SELECT * FROM gl_bc_packets WHERE reference2 IN (’‘);
GL_INTERFACE
SELECT *FROM GL_INTERFACE GLIWHERE user_je_source_name =’Purchasing’AND gl_sl_link_table =’RSL’AND reference21=’PO’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLI.reference22 =RRSL.reference2AND GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactionswhere po_header_id ));
GL_IMPORT_REFERENCES
SELECT *FROM gl_import_references GLIRWHERE reference_1=’PO’AND gl_sl_link_table =’RSL’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions where po_header_id =))

Attach image logo in XML Puslisher Report with BLOB

There was a requirement to build a report having photograph on it.Instead of storing image in OA media and reading the image is there any other way we can achieve the requirement.

It is a report used by HR department to keep along with the personal/academic/experience records for each employee of the organization. This report was named as ‘Employee Photograph report’.

This requirement was actually to find out the way of converting BLOB (photographs are stored as BLOB in HR table) to CLOB and use that in a XML PUBLISHER report template.


This is how it was achieved step by step:
1. Write a BLOB TO CLOB converter db function as below and compile in the database.
CREATE OR REPLACE FUNCTION APPS getbase64( p_source BLOB )
RETURN CLOB
IS
v_result CLOB;
BEGIN
--dbms_lob.freetemporary(v_result);
DBMS_LOB.createtemporary(lob_loc = > v_result, CACHE =>False, dur => 0);
Wf_Mail_Util.EncodeBLOB ( p_source, v_result);
RETURN ( v_result );
END getbase64;

2.Build the report ( it can be a rdf report or XMl data template) and call the above BLOB to CLOB converter function from the report query as shown below:
In this case employee photographs were stored in PER_IMAGES table in IMAGE column.
SAMPLE REPORT QUERY
——————————–
SELECT pa.person_id
,pa.employee_number
,pa.full_name
,getbase64(PerImageEO.IMAGE) IMAGE1
FROM per_all_people_f pa
,PER_IMAGES PerImageEO
WHERE PerImageEO.PARENT_ID=pa.employee_number


For the front end we can prepare the template( .rtf file ) and incorporate the photograph tag as well and below is the illustration for the same:

THE TEMPLATE

Now the IMAGE field show above is the place where the Photo of the employee should come:
Below is the code need to put in the tag of the image field shown above:
<?if:IMAGE1!=''?>
<fo:<span class=”hiddenSpellError”>instream-foreign-object</span>content-type=”image/jpg”>IMAGE1</fo:instream-foreign-object>
<?end if?>
The ‘ IF’ shown above is to handle the employee records without photograph in the table. Otherwise the report will end up with error if there is an employee without photo in the database.
Place the rdf and rtf and register as usual like any other XML publisher report.
IMAGE

Comparing Two Oracle Forms

Comparing Two Oracle Forms

1> Convert the fmb in txt and compare using any file comparing tool.
File -> Administration -> Object List Report  (It will create a txt file in the same directory)
Then Compare using any text comparing tool.
       OR
2> Another solution is to convert FMB(binary file) to FMT(Text file).
Open your forms, then choose menu File:Administration:Convert:Binary_to_text
This optin generate a Source Code in text format, do it with your files, then compare the files with a command like "diff" on linux, that list all differences line by line if exists

Oracle Business Events and step by step process to subscribe to a Business Event

The concept of Business Events in the context of EBS plays a critcal role in enabling event-driven integration with other systems outside the application.

In addition to that, the Business Events in E-Business Suite in particular allow for an exceptionally effective way of decoupling the standard product functionality, available out of the box, from client customizations that seek to adapt the standard product to meet customer-specific business needs. In other words, Oracle Applications developers should consider using Business Events whenever possible when configuring and customizing the standard products.

 What are Business Events

“A business event is an occurrence in an internet or intranet application or program that might be significant to other objects in a system or to external agents.”

For example, the creation of a purchase order is an example of a business event in a purchasing application

 Oracle Business Events =>Architecture

The Oracle Workflow Business Event System is an application service that leverages the Oracle Advanced Queuing (AQ) infrastructure to communicate business events between systems.

The Business Event System consists of the Event Manager and workflow process event activities

• Is available with both standalone and E-Business Suite Workflow
• Provides event driven processing
• Allows Application modules and external systems to raise events
• Facilitates Oracle Application modules and external system to subscribe to these events
• Subscriptions can be synchronous or asynchronous

o you know,

11i10 E-Business Suite is preconfigured with 915 Business Events

Each Business Event represents a ready to use Integration or extension point

915 Outbound Integration/extension points

915 Inbound Integration/extension points

Integration points centered around the major E-Business Suite flows like p2p, o2c etc

dgreybarrowComponent Architecture

Typically Business events Component can be best understood as:

Transactional Diagram of business Events can be best understood as:

Below is Architectural Diagram for Outbound Business Events , typical flow consist of

• Creates deferred subscription to the selected
• Deferred subscription transfers the event to the customer queue (WF_BPEL_Q)
• Unique consumer is created automatically

“A business event is an occurrence in an internet or intranet application or program that might be significant to other objects in a system or to external agents.”

For example, the creation of a purchase order is an example of a business event in a purchasing application

Event Manager for Oracle Applications

The Oracle Workflow Event Manager lets you register interesting business events that may occur in your applications, the systems among which events will be communicated, named communication agents within those systems, and subscriptions indicating that an event is significant to a particular system. The Event Manager also performs subscribtion processing when events occur.

 Subscriptions for Business Events

• Events that trigger custom code
• Events that send information to Workflow
• Events that send information to other queues or systems

 where you can Uses of Business Events

• System integration messaging hubs
• Distributed applications messaging
• Message-based system integration
• Business-event based workflow processes
• Non-invasive customization of packaged applications

 PLSQL vs Java Business Event System

Oracle Workflow provides Business Event System implementation within the database (PLSQL) and in the middle tier (Java).

The implementation is exactly the same in terms of the event subscription processing in both these layers but the only difference is how the Developer wants to leverage Business Event System's capabilities for event processing requirements.

With the availability of Business Event System implementation in PLSQL and Java, different subscription processing scenarios can be achieved.

 How to Proceed if Business events are required to use

• Design your Business Event/s
• Define your event

 Setting Up the Business Event System [Adopted workflow user documentation]

To set up the Business Event System and enable message propagation, perform the following steps:

1. If you want to communicate business events between the local system and external systems, create database links to those external systems.
2. If you want to use custom queues for propagating events, set up your queues.
3. Check the Business Event System setup parameters.
4. Schedule listeners for local inbound agents.
5. Schedule propagation for local outbound agents.
6. If you are using the version of Oracle Workflow embedded in Oracle Applications, synchronize event and subscription license statuses with product license statuses.
7. Ensure that the WF_CONTROL queue is periodically cleaned up to remove inactive subscribers.

---------------------------------------------------------------------------------------

Business Event

Business Event is an occurrence of a business activity which has some significance. For example, the activity of creating a purchase order (PO) is a business event, like wise approving PO, receiving goods against a PO, matching a PO receipt with invoice is a Business Event.

Subscription

Subscription is an activity to be performed on occurrence of a Business Event.
Eg:- If you would like to send an email notification to some set of users when a PO receipt is created then you can subscribe to PO Receipt related Business Event and trigger a workflow notification from the subscription to send email.

How is Business Event Raised?

Usually seeded Business Events are raise by workflows or Forms through a PL/SQL code.

Oracle not only provides an option to create custom subscriptions to seeded Business Event s but also provides a flexibility to create a complete custom Business Event.

Workflow Engine vs Business Event System

Oracle Workflow has two major execution engines.

• Workflow Engine
• Workflow Business Event System

Here is a simple comparison of what they process and their associated background components.

Workflow Engine	Workflow Business Event System
Executes workflow processes created using Windows based Workflow Builder client	Executes subscriptions to business events registered using Event Manager in Workflow Administrator Web Applications Responsibility
Entry point foreground APIs are WF_ENGINE.CreateProcess and WF_ENGINE.StartProcess	Entry point foreground API is WF_EVENT.Raise
Execution deferred to background by enqueuing message to AQ WF_DEFERRED_QUEUE_M	Execution deferred to background by enqueuing message to AQ WF_DEFERRED
Entry point background API is WF_ENGINE.Background	Entry point background API is WF_EVENT.Listen
AQ Payload is SYSTEM.WF_PAYLOAD_T	AQ Payload is WF_EVENT_T
Background processing is done by Concurrent Program - FNDWFBG (Workflow Background Engine)	Background processing is done by GSC Component - Workflow Deferred Agent Listener
Background Engine is submitted as recurring concurrent request from SRS form or Workflow Manager in OAM	Agent Listener is a service component managed through Workflow Manager in OAM

• When troubleshooting issues with Business Event System, users verify that the Workflow Background Engine is running.
• When troubleshooting deferred workflow processes, users verify that the Workflow Deferred Agent Listener is running.

Steps to Subscribe to a Business Event

In this article I will show you how to subscribe to PO Receipt standard business event (oracle.apps.po.rcv.rcvtxn). Our subscription would just insert the seeded business event details into a custom temporary table.

Navigate to “Workflow Administrator Web (New)” responsibility –> Administrator Workflow –> Business Events

Search for a business event oracle.apps.po.rcv.rcvtxn

Click on Subscription icon

and then click on Create Subscription button

In Create Subscription page enter the following details and save the page:
– System: < choose your system name from LOV >
– Phase: 101 (enter some number greater than 100)
– Status: Enabled
– Rule Data: Message
– Action Type: Custom
– On Error: Stop and Rollback
– PL/SQL Rule Function: xx_be_test_pkg.xx_insert (we will create this package and procedure in next step)
– Priority: Normal
– Owner Name: (enter your custom application short name)
– Owner Tag: (enter your custom application short name)

Execution Condition: PHASE

If you want to execute the business event subscription on sync with workflow activity then you need to select phase below 100 OR else enter phase more than 100 if you want to execute business event subscription after completion of workflow business activity i.e, asynchronously.

Package Specification

-- CREATE OR REPLACE PACKAGE xx_be_test_pkg AS FUNCTION xx_insert (p_subscription_guid IN RAW, p_event IN OUT wf_event_t) RETURN VARCHAR2; END xx_be_test_pkg; / --

Package Body

CREATE OR REPLACE PACKAGE BODY xx_be_test_pkg AS FUNCTION xx_insert (p_subscription_guid IN RAW, p_event IN OUT wf_event_t) RETURN VARCHAR2 IS -- l_param_list wf_parameter_list_t; l_param_name VARCHAR2 (240); l_param_value VARCHAR2 (2000); l_event_name VARCHAR2 (2000); l_event_key VARCHAR2 (2000); l_event_data VARCHAR2 (4000); -- BEGIN -- l_param_list := p_event.getparameterlist; l_event_name := p_event.geteventname (); l_event_key := p_event.geteventkey (); l_event_data := p_event.geteventdata ();   -- INSERT INTO xx_be_debug_log_tmp (text ) VALUES ('EVENT NAME: ' || l_event_name ); -- -- INSERT INTO xx_be_debug_log_tmp (text ) VALUES ('EVENT KEY: ' || l_event_key ); -- -- INSERT INTO xx_be_debug_log_tmp (text ) VALUES ('EVENT DATA: ' || l_event_data );   IF l_param_list IS NOT NULL THEN FOR i IN l_param_list.FIRST .. l_param_list.LAST LOOP -- l_param_name := l_param_list (i).getname; l_param_value := l_param_list (i).getvalue;   -- INSERT INTO xx_be_debug_log_tmp (text ) VALUES (l_param_name || ': ' || l_param_value );   COMMIT; -- END LOOP; END IF;   COMMIT; RETURN 'SUCCESS'; -- -- EXCEPTION WHEN OTHERS THEN -- --Provide context information that helps locate the source of an error. -- wf_core.CONTEXT (pkg_name => 'XX_BE_TEST_PKG', proc_name => 'XX_INSERT', arg1 => p_event.geteventname (), arg2 => p_event.geteventkey (), arg3 => p_subscription_guid ); -- --Retrieves error information from the error stack and sets it into the event message. -- wf_event.seterrorinfo (p_event => p_event, p_type => 'ERROR'); -- RETURN 'ERROR'; -- END xx_insert; END xx_be_test_pkg;

Compile the above package and Restart Workflow Agent Listener service as shown in the below screen shot

More about the package

When the Event Manager calls the rule function, it passes two parameters to the function and expects a return code when the function completes. The parameters are defined here:

p_subscription_ guid – The globally unique identifier for the subscription.
p_event – The event message with which we can access Event Key, Event Name, Event Data and Parameters. Every seeded business event has some parameters which we can access through p_event parameter.

The function must return one of the three status codes: SUCCESS or WARNING or ERROR.

Create PO Receipt Transaction to raise Business Event

Create a PO Receipt transaction so that the business event oracle.apps.po.rcv.rcvtxn will get raised the custom subscription we created will get executed.

After creating PO Receipt transaction wait for a minute and query for the custom table which we have mentioned in the package.

SELECT * FROM xx_be_debug_log_tmp;

Event Key

A string that uniquely identifies an instance of an event. Together, the event name, event key, and event data fully communicate what occurred in the event.

Event Data

A set of additional details describing an event. The event data can be structured as an XML document. Together, the event name, event key, and event data fully communicate what occurred in the event.

Event Message

A standard Workflow structure for communicating business events, defined by the datatype WF_EVENT_T. The event message contains the event data as well as several header properties, including the event name, event key, addressing attributes, and error information.

Event Activity

A business event modeled as an activity so that it can be included in a workflow process.

To know if the business event is fired or not query for WF_DEFERRED queue table if the subscription is using PL/SQL procedure or else use WF_JAVA_DEFERRED queue table if the subscription is using Java procedure.

SELECT substr(wfd.corrid,1,40) corrid, decode(wfd.state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained', 3, '3 = Exception', to_char(substr(wfd.state,1,12))) State, COUNT(*) COUNT FROM applsys.wf_deferred wfd GROUP BY wfd.corrid, wfd.state;

Status	Meaning
Ready	Activity is ready to be processed
Delayed	Activity will be processed later
Retained	Activity was already processed
Exception	Activity had an error

Note: Make sure Workflow Background Process is running in the background.

In this way we can use business events in many ways to solve business requirements.