GL Account Balance: The below SQL gets the beginning balance and ending balance of the specified GL Account for a giver ledger and period. SELECT gcc.concatenated_segments Code_combination, SUM(NVL(gb.begin_balance_dr,0)-NVL(gb.begin_balance_cr,0)) beginning_bal, SUM(NVL(gb.begin_balance_dr,0)-NVL(gb.begin_balance_cr,0) + (NVL(gb.period_net_Dr,0) – NVL(gb.period_net_cr,0))) end_bal FROM gl_balances gb, gl_code_combinations_kfv gcc WHERE gb.code_combination_id = gcc.code_combination_id AND gcc.CONCATENATED_SEGMENTS = ’01-000-1110-0000-000′ — Enter GL Account AND gb.ledger_id =…

Query to retrieve Invoice List for a specific Customer SELECT hp.party_name customer ,hca.account_number ,rcta.trx_date ,rcta.trx_number Invoice_number ,rcta.status_trx ,rcta.invoice_currency_code Invoice_currency ,rctla.line_number ,msi.segment1 Item_number ,rctla.description ,rctla.extended_amount line_amt ,rcta.org_id ,rctt.cust_trx_type_id FROM RA_CUSTOMER_TRX_ALL RCTA, RA_CUSTOMER_TRX_LINES_ALL RCTLA, RA_CUST_TRX_TYPES_ALL rctt, HZ_CUST_ACCOUNTS_ALL HCA, HZ_PARTIES HP, MTL_SYSTEM_ITEMS_B msi WHERE rcta.customer_trx_id = rctla.customer_trx_id AND msi.inventory_item_id = rctla.inventory_item_id AND msi.organization_id = 116 AND rcta.cust_trx_type_id =…

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 Forms Personalization query will list all the forms in Oracle Applications that have been customized using Forms Personlization: select distinct ffcr.form_name , ffcr.enabled, fft.USER_FORM_NAME, fat.APPLICATION_NAME , ffcr.description, ffcr.id RULE_ID from FND_FORM_CUSTOM_RULES ffcr, FND_FORM ff, FND_FORM_TL fft, fnd_application_tl fat where enabled = ‘Y’ and ffcr.form_name = ff.form_name and ff.form_id = fft.form_id and ff.application_id =…

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, b.request_group_id, request_group_name, e.responsibility_name from fnd_concurrent_programs_tl…

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 WHERE fmep.menu_id = (SELECT menu_id FROM fnd_menus WHERE menu_name=’INV_NAVIGATE’Â –Change the menu…

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 v_filename VARCHAR2(50); — Data filename…

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); x_msg_data VARCHAR2…

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. CREATE OR REPLACE PROCEDURE adjustment_api IS p_revenue_adj_rec ar_revenue_adjustment_pvt.rev_adj_rec_type; x_return_status VARCHAR2 (2000); x_msg_count…

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. 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…

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…

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…

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 where fu.user_id = fd.user_id and (fu.end_date <= sysdate or fu.end_date is NOT NULL)…

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 — GET THE CURRENT SQL STATEMENT RUNNING FOR A CONCURRENT REQUEST SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER,…