Dependent Parameters in Oracle Applications Requirement: Say there is a concurrent program that lets you retrieve employee details based on employee name or employee number. The concurrent program has 3 parameters: 1.Search Criteria is a value set containing “Employee Name” and “Employee Number” as values 2.Employee Name 3.Employee Number When the user wants to search Read More..
The below script will lanch or kickoff the desired workflow from PL/SQL code: declare v_itemtype VARCHAR2(50); v_itemkey VARCHAR2(50); v_process VARCHAR2(50); v_userkey VARCHAR2(50); begin v_itemtype := ‘DEMOIT’; v_itemkey := ’1233′; v_userkey := ’1233′; v_process := ‘DEMOPROCESS’; WF_ENGINE.Threshold := -1; WF_ENGINE.CREATEPROCESS(v_itemtype, v_itemkey, v_process); wf_engine.setitemuserkey(v_itemtype, v_itemkey, v_userkey ); wf_engine.setitemowner (v_itemtype, v_itemkey,’SYSADMIN’); WF_ENGINE.STARTPROCESS(v_itemtype, v_itemkey); commit; exception when others Read More..
The below script will list all the OAF Personlizations in Oracle Applications: SELECT PATH.PATH_DOCID PERZ_DOC_ID, jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) PERZ_DOC_PATH FROM JDR_PATHS PATH WHERE PATH.PATH_DOCID IN (SELECT DISTINCT COMP_DOCID FROM JDR_COMPONENTS WHERE COMP_SEQ = 0 AND COMP_ELEMENT = ‘customization’ AND COMP_ID IS NULL) ORDER BY PERZ_DOC_PATH;
The below script list all the forms in Oracle Applications that have been customized using Forms Personlization: select distinct a.form_name , a.enabled, c.USER_FORM_NAME, d.APPLICATION_NAME from FND_FORM_CUSTOM_RULES a, FND_FORM b, FND_FORM_TL c, fnd_application_tl d where enabled = ‘Y’ and a.form_name = b.form_name and b.form_id = c.form_id and b.application_id = d.application_id order by application_name
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 select distinct a.application_id, a.concurrent_program_id, a.user_concurrent_program_name, a.description, Read More..
This script will accept the menu name as parameter and will list all functions that can be accessed under that menu. SELECT DISTINCT fmep.menu_id, DECODE(fmep.function_id,NULL, DECODE(fmec.function_id, NULL,DECODE(fmec1.function_id,NULL, ‘No Func’,fmec1.function_id ), fmec.function_id), fmep.function_id) funcID, fff.user_function_name, fff.description FROM fnd_form_functions_tl fff, fnd_menu_entries fmec1, fnd_menu_entries fmec, fnd_menu_entries fmep Read More..
1. UTL_SMTP CREATE OR REPLACE PROCEDURE erpschools_send_email IS mailhost varchar2 (30) := ‘smtp.erpschools.com’; crlf varchar2(2)Â := CHR (13) || CHR (10); v_email varchar2(16000); message_1 LONG; mail_conn utl_smtp.connection; BEGIN mail_conn := utl_smtp.open_connection (mailhost, 25); utl_smtp.helo (mail_conn, mailhost); utl_smtp.mail (mail_conn, ‘sender@erpschools.com’); utl_smtp.rcpt (mail_conn, ‘receiver@erpschools.com’); message_1 Read More..
This script can be used to read data from a flat file and insert into a table. UTL_FILE Read CREATE OR REPLACE PROCEDURE load_data(errbuff varchar2,errcode number) AS v_line VARCHAR2(2000); — Data line read from input file v_file UTL_FILE.FILE_TYPE; — Data file handle v_dir VARCHAR2(250); — Directory containing the data file Read More..
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 DECLARE x_return_status VARCHAR2 (50); x_msg_count VARCHAR2 (50); Read More..
Use this script to create a procedure in Database and call the procedure by passing the delivery number as a parameter to ship confirm it. You can set the options for 1. Backordering unspecified quantities 2. Closing the delivery automatically by submitting the Trip stop program after ship confirm is successfull SHIP CONFIRMATION THROUGH API Read More..
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 Read More..
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 Read More..
This articles contains all the table information related to Oracle Workflows and queries joining these tables. WORKFLOW TABLES SELECT * FROM WF_USER_ROLE_ASSIGNMENTS SELECT * FROM WF_USER_ROLES SELECT * FROM WF_ROLES SELECT * FROM WF_ITEMS SELECT * FROM WF_ITEM_ATTRIBUTES SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES SELECT * FROM WF_ITEM_ATTRIBUTES_TL SELECT * FROM WF_ACTIVITIES SELECT * FROM WF_ACTIVITIES_TL SELECT Read More..
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. CREATE OR REPLACE PROCEDURE adjustment_api IS p_revenue_adj_rec ar_revenue_adjustment_pvt.rev_adj_rec_type; x_return_status Read More..
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 Read More..
This script can be used to extract order header details from backend. SELECT ra.customer_number, hl.address1, hl.address2, hl.city, hl.state, hl.postal_code, hl.country, hl.province, hl.county FROM oe_order_headers_all ooh, hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_party_sites hps, hz_locations hl, ra_customers ra WHERE 1 = 1 AND ooh.invoice_to_org_id = hcsu.site_use_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.party_site_id = hps.party_site_id AND hps.location_id = hl.location_id Read More..
Scripts to get the statistics based on order type: – Number of orders taken in each order type per month select b.name,b.description,to_char(ordered_date,’Mon’) Month, count(0) NoOfOrders from oe_order_headers_all a, oe_transaction_types_tl b where 1=1 and a.order_type_id = b.transaction_type_id and ordered_Date between ’01-SEP-2009′ and ’01-OCT-2009′ and org_id = 1 group by b.name, b.description,to_char(ordered_date,’Mon’) order by to_char(ordered_date,’Mon’) –Number of Read More..
This script will display all the responsibilities for a given Username. SELECT fu.user_name, frt.responsibility_name, furgd.start_date, furgd.end_date FROM fnd_user fu, fnd_user_resp_groups_direct furgd, fnd_responsibility_tl frt WHERE fu.user_id = furgd.user_id AND furgd.responsibility_id = frt.responsibility_id AND UPPER (fu.user_name) = UPPER (’ERPSCHOOLS’)
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 FOR Read More..
This script below will get the list of all inactive users and end date all the responsibilities assigned to them. Declare –cursor to get all inactive users CURSOR cur_inactive_user IS select fu.user_id, fd.responsibility_id, fd.responsibility_application_id, fd.security_group_id, fd.start_date, fd.end_date from fnd_user fu, fnd_user_resp_groups_direct fd Read More..
