Do you want to meet your fellow Oracle Apps Consultants ?? then join us @ Facebook
Oracle SQL
Oracle Apps Basics

APIs, Trade Mangement »

[ 24 Aug 2016 | No Comment | 23 views]

SET serveroutput ON;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (20000);
l_resp_name …

PL/SQL, Workflows »

[ 24 Jun 2016 | No Comment | 62 views]

lc_role_name := ‘XX_TEST_ROLE’;
wf_directory.setadhocroleexpiration(lc_role_name, sysdate -1 );
wf_directory.setadhocrolestatus( lc_role_name, ‘INACTIVE’);
wf_directory.deleterole(lc_role_name , ‘WF_LOCAL_ROLES’ , 0);

PL/SQL Script to Create Adhoc Role
PL/SQL Script to Remove and Add Users to Adhoc Role

PL/SQL, Workflows »

[ 24 Jun 2016 | No Comment | 63 views]

Before reading this article, read about Roles in Workflow if you are not aware of what a role is about.
A role can be created from application from “User Management” responsibility. Adhoc role is something which can be created dynamically using PL/SQL on run time based on requirement.
Not only Adhoc role can be created through PL/SQL, we can also add …

PL/SQL, Workflows »

[ 24 Jun 2016 | No Comment | 74 views]

ln_role_users_count NUMBER;
lc_role_name apps.wf_roles.NAME%TYPE := ‘XX_TEST_ROLE’;
–Checking if Adhoc Role Exits
INTO ln_role_users_count
FROM apps.wf_user_roles
WHERE NAME = lc_role_name;
IF ln_role_users_count > 0 THEN
–Removing users from Adhoc Role

PL/SQL, Workflows »

[ 24 Jun 2016 | No Comment | 46 views]

ln_role_count NUMBER;
lc_role_name apps.wf_roles.NAME%TYPE := ‘XX_TEST_ROLE’;
lc_role_display_name apps.wf_roles.description%TYPE := ‘XX Test Role’;
–Checking if Adhoc Role Exits
INTO ln_role_count
FROM apps.wf_roles

Workflows »

[ 24 Jun 2016 | No Comment | 84 views]

A Role in Oracle EBS is a a categorization of set of users who share same responsibilities. A role will have one or more users under it.
When a notification in Oracle Workflow needs to be sent to multiple users, a role is created with set of users and notifications is sent to the role.
Roles are of two types, Static and …

Payments »

[ 23 Jun 2016 | No Comment | 81 views]

This query list all payment documents under a payment batch with detailed invoice and Supplier/Employee information.

*PURPOSE: Query to list all Documents and their information under a Payment Process Request
*AUTHOR: Shailender Thallam
SELECT ip.payment_process_request_name,
invh.invoice_id ,
invh.vendor_id ,
invh.invoice_num ,
invh.invoice_amount ,
invh.amount_paid ,
idpa.payment_date ,
idpa.document_type ,
idpa.payment_amount ,

Payments »

[ 23 Jun 2016 | No Comment | 75 views]

*PURPOSE: Query to list Supplier Bank, Bank Branch, Bank Account information
*AUTHOR: Shailender Thallam
SELECT owners.account_owner_party_id,
asp.segment1 vendor_num,
FROM apps.hr_operating_units hou
WHERE 1 = 1
AND hou.organization_id = asa.org_id
) ou_name,

Payments »

[ 23 Jun 2016 | No Comment | 70 views]

*PURPOSE: Query to list all Bank, Bank Branch, Bank Account information
* Operating Unit wise
*AUTHOR: Shailender Thallam
SELECT cba.bank_account_name ,
cba.bank_account_num ,
cba.multi_currency_allowed_flag ,
cba.zero_amount_allowed ,
cba.account_classification ,
bb.bank_name ,
bb.bank_branch_type ,
bb.bank_branch_name ,

Uncategorized »

[ 20 Jun 2016 | No Comment | 106 views]

Deloitte is Hiring BSc (Computers / IT) or BCA. students with 0-1 years experience
Location: Hyderabad and Bangalore
Skill Required: Experience in codes using languages such as Java, C, C++, C#, etc
Education and Work experience:

BSc (Computers / IT) or BCA.
0-1 year related technical experiences (includes certification/ internship projects)
Proficient in business communication skills at entry level (spoken and written)
Strong analytical and logical …


[ 23 May 2016 | No Comment | 199 views]

A field “Operating Unit Mode” is present in the Concurrent Program definition when viewed through OA Framework pages.
Query to check this option from back-end:

SELECT concurrent_program_name, multi_org_category
FROM fnd_concurrent_programs
WHERE concurrent_program_name = ‘<CP_short_name>’; –Ex: ‘APXINRIR’

To submit MOAC enabled concurrent program, org_id has to be set first and then FND_REQUEST.SUBMIT_REQUEST API has to be used. To set org_id use the API FND_REQUEST.SET_ORG_ID



[ 20 May 2016 | No Comment | 2,317 views]

Oracle is coming up with some exciting features on “Concurrent Request” form in upcoming EBS 12.2.6 release.
As per a discussion on MOS, An Oracle user Attiq Ahmed has suggested Oracle to implement some features on “Concurrent Request” form and looks like Oracle has considered these ideas and implemented them in their next release
Below are the features which may …

Extensions, Workflows »

[ 17 May 2016 | No Comment | 913 views]

This article explains how to attach a concurrent program output as a Workflow email notification.

DBA Directory should be created with Concurrent Program output path
A new document category should be created under “Application Developer –> Attachments –> Document Categories”

Steps to Implement

Call API “FND_DOCUMENTS_PKG.INSERT_ROW” to detail the information of document to be created
Insert a BLOB type of record into “FND_LOBS” table
Use “DBMS_LOB” …

Payables, Payments »

[ 8 May 2016 | No Comment | 220 views]

Unlike check printing, Positive Pay XML data is NOT present in table IBY_TRXN_DOCUMENTS.
To get XML data for Positive Pay, we need to attach ‘Extract Identity‘ XMLP Template to Payment Format and run PPR process. Output of ‘Positive Pay File‘ concurrent request will be the XML data of that particular PPR run. Using this XML data, we can find the data …

Payables, Payments »

[ 7 May 2016 | No Comment | 195 views]

Generally after running Payment Process Request, we can get the XML data in the log file of ‘Format Payment Instructions with Text Output’ using which we can develop/debug the rtf/eText template using XML Publisher.
Not only in the log file but also the XML data is stored in IBY_TRXN_DOCUMENTS table.
Below is the SQL Statement can be used to get the XML …