Step-1: Create Table & Insert data create table xxcus.xx_tmp_all(id number, name varchar2(100), org_id number); Replace XXCUS is with your custom schema name. Also make sure to have ORG_ID column and table name suffix as _ALL. Step-2: Create synonyms CREATE OR REPLACE SYNONYM apps.xx_tmp FOR xxcus.xx_tmp_all; Step-3: Create Policy Step-4: Query Data We can drop by […]
— Created by Prudhvi DECLARE p_event_source_info xla_events_pub_pkg.t_event_source_info; BEGIN p_event_source_info.source_application_id := NULL; p_event_source_info.application_id := 222; — AR p_event_source_info.legal_entity_id := 12340; — change it p_event_source_info.ledger_id := 1; — change it p_event_source_info.entity_type_code := ‘TRANSACTIONS’; p_event_source_info.transaction_number := ‘1234’; — change it p_event_source_info.source_id_int_1 := 1234; –Transaction ID; p_event_source_info.source_id_int_2 := NULL; p_event_source_info.source_id_int_3 := NULL; p_event_source_info.source_id_int_4 := NULL; p_event_source_info.source_id_char_1 := NULL; p_event_source_info.source_id_char_2 […]
— Creted by Prudhvi </code> DECLARE p_event_source_info xla_events_pub_pkg.t_event_source_info; BEGIN p_event_source_info.source_application_id := NULL; –101 GL — 602 XLE p_event_source_info.application_id := 222; — AR p_event_source_info.legal_entity_id := 1234; p_event_source_info.ledger_id := 1; p_event_source_info.entity_type_code := ‘TRANSACTIONS’; p_event_source_info.transaction_number := NULL; p_event_source_info.source_id_int_1 := 1234; — Customer Transaction ID; p_event_source_info.source_id_int_2 := NULL; p_event_source_info.source_id_int_3 := NULL; p_event_source_info.source_id_int_4 := NULL; p_event_source_info.source_id_char_1 := NULL; p_event_source_info.source_id_char_2 := […]
The below example illustrates how to write a line to a file using UTL_FILE. 1. Create Directory on your server (ex: Linux, Unix) 2. Give writable permissions to the directory created. If you register this code as a concurrent program then you need to make sure your applmgr user has write permissions to this directory. […]
The below script will lanch or kickoff the desired workflow from PL/SQL code:
Oracle workflow objects can be saved into database or into a file. So We can treat Oracle workflow objects as both file system objects and database objects. We save workflow object into file when working on local system either for development / enhancement or debugging. When you execute the workflow from server it will always […]
First of all why do we need to run a workflow from workflow administrator responsibility? Second, Who will run it? Third, When can we run it? Fourth, How can we run it? Finally, Is it recommended approach? 1) Normally we don’t run or kickoff workflow’s from workflow administrator responsibility. All seeded workflow’s are tied to […]
The below script can be used to auto approve notifications through database procedure. Write your business logic with in the below procedure ,register it as a concurrent program and schedule it as needed to run periodically. The same script can also be used in custom forms, custom or third party applications to respond to notifications from backend procedure.
GL Account Balance: The below SQL gets the beginning balance and ending balance of the specified GL Account for a giver ledger and period.
— Oracle Database version along with hostname and instance name — Database version along with operating system platform — Oracle Apps version — Database Version, PLSQL Version, TNS Version — Oracle workflow version — Oracle XML Publisher / BI Publisher Version
Script to get Trace details for a concurrent request along with the log file and output file location details
Concurrent Program session details along with SQL Query
Get the list of users who have access to a specific given responsibility
Get List of responsibilities assigned to a user
Get details of concurrent requests / programs that are executed by a specific user from a specific responsibility between certain dates.
Get details of concurrent requests which are running currently and also the script will provide details of who is running the request and from which responsibility
Script to fetch the following details 1. Concurrent Program Name 2. Concurrent Program Short Name 3. Concurrent Program Application Name 4. Executable Name 5. Executable Short Name 6. Executable Application Name 7. Execution Method 8. Execution file Name 9. Enable Trace flag
Use this query / script to find the link between a internal requisition and internal sales order. Please note that a internal sales order which is created from internal requisition may not always have sales order type as “INTERNAL” or “INTERCOMPANY”. Sales order type can also me “MIXED” or any other custom type so do […]
Query to retrieve Invoice List for a specific Customer
Order to Cash query / script
The below script will list all the OAF Personlizations in Oracle Applications:
The below Forms Personalization query will list all the forms in Oracle Applications that have been customized using Forms Personlization: –Get Customer actions for a specific rule Navigation: Help > Diagnostics > Custom Code > Personalize Read more forms personalization articles http://erpschools.com/articles/forms-personalization-tutorial http://erpschools.com/articles/change-displayed-data-in-lov-using-forms-personalization http://erpschools.com/articles/tools/forms/personalization/forms-personalization-call-plsql-procedure
This page will include the scripts 1. Find the responsibility name from which a concurrent program can be run 2. Find the responsibility name from which user has ran the concurrent program Find the Responsibility name from which a concurrent program can be run Find from which responsibility user has ran the concurrent program
This script will accept the menu name as parameter and will list all functions that can be accessed under that menu.
utl_smtp example: UTL_MAIL: This is the new package introduced in 10g. even though this uses the UTL_SMTP in the background the advantage with this script is we can change the mail server and ports in the configuration file with out changing the code.
This script can be used to read data from a flat file and insert into a table. UTL_FILE Read
This script can be used to get the below quantities. 1. On-hand Quantity 2. Available to Reserve 3. Quantity Reserved 4. Quantity Suggested 5. Available to Transact 6. Available to Reserve You can also get the On-hand quantities from the table mtl_onhand_quantities GET ON-HAND QUANTITIES API GET ON-HAND QUANTITIES FROM TABLE
The scripts in this article can be used to: 1) Register the executable and Program 2) Attach Concurrent program to a Request Group 3) Submit Concurrent program 1) Registering the Executable from back end Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too. Below is […]
Includes two scripts to fetch all the transactions information related with in a procure to pay cycle. Two scripts are provided to use one with receipts and other when receipts are not created. Few important fields that were included in the script are Requisition Number, Purchase Order Number, Invoice Number, Customer Number, Invoice Amount, GL […]
This script can be used to adjust the Revenue for a particular transaction. The amount can be adjusted from one transaction line to another line by specifying the actual amount to adjust or percentage. Run this procedure in loop to adjust many transactions.
The below script can be used to add Older revision numbers to an item. Why we need to use this script: In Oracle standard functionality revisions need to be added in sequence like A, B, C….. It doesn’t allow us to add B after adding A and C. In that case this script can be […]
This script can be used to extract order header details from backend.
Scripts to get the statistics based on order type: — Number of orders taken in each order type per month –Number of lines shipped in each order type per month
This script will display all the responsibilities for a given Username.
This script gets all the approvers and their details like approver name, position, approval group, amount limit, approval hierarchy path for a given position hierarchy. Inputs for this script are: business_group_id for the desired POSITION HIERARCHY pos_structure_version_id for the desired POSITION HIERARCHY Top position in the desired POSITION HIERARCHY SCRIPT TO GET POSITION_STRUCTURE_ID AND BUSINESS_GROUP_ID […]
This script below will get the list of all inactive users and end date all the responsibilities assigned to them.
1. Use this Script to get current sql statement that is running in the background for a given concurrent request. 2. Get all the sql statements that are being executed by a user. 3. Get all the blocking sessions