Do you want to meet your fellow Oracle Apps Consultants ?? then join us @ Facebook
APIs Trade Mangement

API to add a Claim Line to already existing Claim

SET serveroutput ON;   DECLARE l_return_status VARCHAR2 (1); l_msg_count NUMBER; l_msg_data VARCHAR2 (20000); l_resp_name VARCHAR2 (100) := 'Oracle Trade Management User'; l_user_name VARCHAR2 (100) := 'SYSADMIN'; l_claim_number CONSTANT VARCHAR2 (25) := 'CLA10107'; l_appl_id NUMBER; l_resp_id NUMBER; l_user_id NUMBER; l_org_id NUMBER; l_line_counter NUMBER := 1; l_claim_id NUMBER; l_api_version CONSTANT NUMBER := 1.0; l_error_index
PL/SQL Workflows

PL/SQL Script to Inactive and Delete Adhoc Role

DECLARE lc_role_name := 'XX_TEST_ROLE'; BEGIN wf_directory.setadhocroleexpiration(lc_role_name, sysdate -1 ); COMMIT; wf_directory.setadhocrolestatus( lc_role_name, 'INACTIVE'); COMMIT; wf_directory.deleterole(lc_role_name , 'WF_LOCAL_ROLES' , 0); COMMIT; END; / PL/SQL Script to Create Adhoc Role PL/SQL Script to Remove and Add Users to Adhoc Role
PL/SQL Workflows

Adhoc Roles in Oracle Workflow

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
PL/SQL Workflows

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

DECLARE ln_role_users_count NUMBER; lc_role_name apps.wf_roles.NAME%TYPE := 'XX_TEST_ROLE'; BEGIN ------------------------------ --Checking if Adhoc Role Exits ------------------------------ SELECT COUNT (1) 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 -------------------------------- apps.wf_directory.removeusersfromadhocrole (role_name => lc_role_name);   dbms_output.put_line("Users removed from Role!"); END IF; -------------------------------- --Adding
PL/SQL Workflows

PL/SQL Script to Create Adhoc Role

DECLARE 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'; BEGIN ------------------------------ --Checking if Adhoc Role Exits ------------------------------ SELECT COUNT (1) INTO ln_role_count FROM apps.wf_roles WHERE NAME = lc_role_name;   IF ln_role_count = 0 THEN --------------------- --Creating Adhoc Role --------------------- apps.wf_directory.createadhocrole (role_name => lc_role_name, role_display_name => lc_role_display_name, notification_preference =>

What is a Role in Oracle Workflow

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

Query to list all Documents and their information under a Payment Process Request

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

Query to list Supplier Bank, Bank Branch, Bank Account information

/********************************************************************** *PURPOSE: Query to list Supplier Bank, Bank Branch, Bank Account information *AUTHOR: Shailender Thallam ***********************************************************************/ SELECT owners.account_owner_party_id, asp.segment1 vendor_num, asp.vendor_name, (SELECT NAME FROM apps.hr_operating_units hou WHERE 1 = 1 AND hou.organization_id = asa.org_id ) ou_name, asa.vendor_site_code, ieb.country_code, cbbv.bank_name, cbbv.bank_number, cbbv.bank_branch_name, cbbv.branch_number, cbbv.bank_branch_type, cbbv.eft_swift_code, ieb.bank_account_num, ieb.currency_code, ieb.iban, ieb.foreign_payment_use_flag, ieb.bank_account_name_alt FROM apps.iby_pmt_instr_uses_all instrument,

Query to list Bank, Bank Branch, Bank Account information Operating Unit wise

/********************************************************************** *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_id, cba.bank_account_name_alt, cba.bank_account_num , cba.multi_currency_allowed_flag , cba.zero_amount_allowed , cba.account_classification , bb.bank_name , cba.bank_id, bb.bank_number, bb.bank_branch_type , bb.bank_branch_name , cba.bank_branch_id , bb.bank_branch_number , bb.eft_swift_code , bb.description BANK_DESCRIPTION, cba.currency_code , bb.address_line1,

How to set ORG_ID for MOAC Enabled Concurrent Programs while Submitting from Backend

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'   S-Single M-Multiple null-Empty To submit MOAC enabled concurrent program, org_id has to be set first
Extensions Workflows

Attaching Concurrent Program Output as Workflow Email Notification Attachment

This article explains how to attach a concurrent program output as a Workflow email notification. Prerequisites 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
Payables Payments

XML Data for Positive Pay

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.