erpSchools

Author: Prudhvi

  • Inventory Miscellaneous Transactions

    As the name implies “Inventory Miscellaneous Transactions” are used for Miscellaneous reasons. Primarily we create Miscellaneous Receipts and Miscellaneous Issues to adjust the inventory on-hand quantity when actual quantity doesn’t match with the quantity represented by ERP system, Oracle Apps. Business Scenario – 1: Actual quantity available in warehouse is more than on-hand quantity in…

  • AP: Payment Manager Process in R12

    Payment Batch lets you create payments for multiple invoices that meet the criteria you specify in a single shot. In R12, it lets you select invoices with different Operating units, Currencies in a single batch. Note: The process of creating payment batches and processing payments in R12 has changed completely when compared to 11i. Navigation:…

  • API: ARP_NOTES_PKG

      API: ARP_NOTES_PKG.insert_p to create new AR Notes DECLARE v_notes_rec ar_notes%rowtype; begin –Set the org_id for apps 11i –fnd_client_info.set_org_context(‘204’); — set org_id for apps R12 mo_global.init(‘AR’); mo_global.set_policy_context(‘S’,’204′); — to initialize the variables v_notes_rec.note_type := ‘MAINTAIN’; v_notes_rec.customer_trx_id := 759812; v_notes_rec.text := ‘AR Notes API Demo form erpSchools.com’; v_notes_rec.customer_call_topic_id := NULL; v_notes_rec.call_action_id := NULL; v_notes_rec.customer_call_id := NULL;…

  • Value Set: Security Type

      Enabling security on value sets helps us to restrict values on concurrent program parameters. Business Scenario: Let’s say that we would like define Apple products with their codes in a independent value set which will be attached to a concurrent program as a parameter. 100: iPod Product Family             (Note: This is not a product…

  • Value Sets: Independent, Dependent, Table

    To demonstrate different types (Independent, Dependent and Table) of value set let’s first define a concurrent program. 1. Define Executable. Navigation: Application Developer > Concurrent > Executable Enter Information as below Note: You don’t really need to have PLSQL package defined for this demo. Save it. Navigation: Application Developer > Concurrent > Program Enter Information…

  • Trace details for a concurrent request along with the log and output file location details

    Script to get Trace details for a concurrent request along with the log file and output file location details SELECT ‘Request id: ‘ || request_id, ‘Trace id: ‘ || oracle_process_id, ‘Trace Flag: ‘ || req.enable_trace, ‘Trace Name: ‘ || dest.VALUE || ‘/’ || LOWER (dbnm.VALUE) || ‘_ora_’ || oracle_process_id || ‘.trc’, ‘Prog. Name: ‘ ||…

  • Concurrent Program session details along with SQL Query

    Concurrent Program session details along with SQL Query SELECT sqlt.sql_text, sess.sid, sess.serial#, fcr.requested_start_date FROM fnd_concurrent_requests fcr , V$SQLTEXT sqlt , v$session sess WHERE fcr.request_id = :pRequestID AND fcr.phase_code = ‘R’ AND fcr.status_code = ‘R’ AND fcr.oracle_session_id = sess.audsid (+) AND sqlt.address = sess.sql_address ORDER BY sqlt.piece;

  • List Concurrent Programs using a specific valueset in its parameter list

    SELECT fcp.user_concurrent_program_name, fat.application_name, fdfc.column_seq_num Seq, fdfc.form_left_prompt “Parameter Name”, fdfc.enabled_flag Active FROM FND_DESCR_FLEX_COL_USAGE_VL fdfc , fnd_flex_value_sets ffvs , fnd_concurrent_programs_vl fcp , fnd_application_tl fat WHERE 1 =1 AND ffvs.flex_value_set_id = fdfc.flex_value_set_id and fdfc.descriptive_flexfield_name = ‘$SRS$.’||fcp.concurrent_program_name and fcp.application_id = fat.application_id and ffvs.flex_value_set_name = :p_valuesetname — Enter value set name –and fcp.user_concurrent_program_name = :p_conc_program_name — use this condition to…

  • List of users having access to specific responsibility

    Get the list of users who have access to a specific given responsibility SELECT fu.user_name, frt.responsibility_name, TO_CHAR(furg.start_date,’DD-MON-YYYY’) start_date, furg.end_date FROM fnd_user fu , fnd_user_resp_groups_direct furg , fnd_responsibility_vl frt WHERE fu.user_id = furg.user_id AND frt.responsibility_id = furg.responsibility_id AND frt.application_id = furg.responsibility_application_id AND NVL(furg.end_date,sysdate+1) > sysdate AND NVL(frt.end_date,sysdate +1) > sysdate AND NVL(fu.end_date,sysdate +1) > sysdate AND…

  • Get List of responsibilities assigned to a user

    Get List of responsibilities assigned to a user select fu.user_name, frt.responsibility_name, furg.start_date, furg.end_date from fnd_user fu , fnd_user_resp_groups_direct furg , fnd_responsibility_vl frt where fu.user_id = furg.user_id and frt.responsibility_id = furg.responsibility_id and frt.application_id = furg.responsibility_application_id and nvl(furg.end_date,sysdate+1) > sysdate and nvl(frt.end_date,sysdate +1) > sysdate and fu.user_name = :p_user_name –and fu.user_name = :p_userid; ;

  • Concurrent requests / programs executed by a specific user

    Get details of concurrent requests / programs that are executed by a specific user from a specific responsibility between certain dates. SELECT fcp.user_concurrent_program_name, fcr.request_id, fcr.request_date, fu.user_name Requested_By , fr.responsibility_name FROM fnd_concurrent_requests fcr , fnd_concurrent_programs_tl fcp , fnd_user fu , fnd_responsibility_tl fr WHERE 1 =1 AND fcr.concurrent_program_id = fcp.concurrent_program_id AND fcr.requested_by = fu.user_id AND fcr.responsibility_id =…

  • Currently running concurrent program details along with user and responsibility

    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 SELECT fcp.user_concurrent_program_name, fcr.request_id, fcr.request_date, fu.user_name Requested_By , fr.responsibility_name, TO_CHAR(fcr.actual_start_date,’DD-MON-YYYY HH24:MI:SS’) actual_start_date FROM fnd_concurrent_requests fcr , fnd_concurrent_programs_tl fcp , fnd_user fu , fnd_responsibility_tl fr WHERE 1 =1 AND fcr.phase_code =’R’…

  • Concurrent Program Details: Name, Short name, Executable, Execution method, Trace

    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 SELECT fcpt.user_concurrent_program_name , fcp.concurrent_program_name short_name , fat.application_name program_application_name , fet.executable_name , fat1.application_name executable_application_name…

  • SOA: Create DB Connection with JDBC and JNDI

    SOA Applications communicate with Database using DB Adapter. When SOA composites are deployed in weblogic server, they require Outbound connection pool to communicate with Database. A JDBC Data Source with Database connection details should be associated to connection pool. JNDI Name used for creating connection pool should match the value entered for database connection when…

  • USER Management | Role Categories | Roles | Indirect Responsibilities

    User Mangement Application helps system administrators to assign or un-assign a responsibility for multiple users at a time. Yes, We don’t have to manage individual user accounts for assigning the responsibilities if you are using User Management Application. In Oracle Applications, Traditional method of assigning a responsibility to a user is from System Administrator >…

  • Internal Requsition and Internal Sales Order Link

    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…

  • Common Issues accessing Oracle EBS (Apps) server Java applets

    Is your Oracle Apps Applet not loading properly? Try the below options. 1. Pop-Up blocker: Ensure that you disable pop-up blocker or add your hostname or URL in exceptions list. Firefox: IE 2. Majority of the anti virus softwares like Norton, McAfee, AVG block Java coming from Oracle Apps Servers. So Disable your anti virus…

  • Procure to Pay Cycle Video

    P2p process with R12 screen shots: procure-to-pay-oracle-r12 Article with 11i screen shots: Procure to Pay Cycle 11i Query: Procure to Pay Cycle Query

  • Oracle Apps – Application Short Names

    APPLICATION SHORT NAME APPLICATION NAME FND Application Object Library SYSADMIN System Administration AU Application Utilities AD Applications DBA SQLGL General Ledger OFA Assets ALR Alert RG Application Report Generator CS Service EC e-Commerce Gateway SQLAP Payables PO Purchasing AR Receivables QA Quality CE Cash Management PA Projects AS Sales Foundation CN Incentive Compensation OE Order…

  • Invoice List for a Customer query

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

  • Order to Cash query

    Order to Cash query / script SELECT hp.party_name customer ,hca.account_number ,hp.party_number ,ooh.order_number ,ooh.flow_status_code Order_status ,rcta.trx_date Invoice_date ,rcta.trx_number Invoice_number ,rcta.status_trx Invoice_Status ,rcta.invoice_currency_code Invoice_currency ,rctla.line_number ,msi.segment1 Item_number ,rctla.description ,rctla.extended_amount line_amt ,arp.amount_due_original ,arp.amount_due_remaining ,rcta.org_id ,rcta.customer_trx_id FROM RA_CUSTOMER_TRX_ALL RCTA, RA_CUSTOMER_TRX_LINES_ALL RCTLA, RA_CUST_TRX_TYPES_ALL rctt, AR_PAYMENT_SCHEDULES_ALL arp, HZ_CUST_ACCOUNTS_ALL HCA, HZ_PARTIES HP, MTL_SYSTEM_ITEMS_B msi, OE_ORDER_LINES_ALL ool, OE_ORDER_HEADERS_ALL ooh WHERE rcta.customer_trx_id = rctla.customer_trx_id…

  • Back to Back Order Process

    Steps involved involved in back to back order process in oracle apps Enter Sales Order Book Sales Order Progress Sales Order to create Supply Order Requisition Import Create Purchase / Inventory Receipt Pick Release Sales Order Ship Confirm   Navigation: Order Management Super User >> Orders, Returns >> Order Organizer   Enter Customer name and…

  • Order to Cash cycle Video

    Note: Firefox is partially supported to view this video. You can view the video in IE, Chrome, Safari.

  • Assemble to Order Cycle Video

  • Oracle Apps Images

        [fbphotos id=307672549329650]

  • Dependent Parameters in Concurrent Program using Special Value Set

    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…

  • Launch Workflow from PL/SQL

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

  • OAF Personlization related Script

    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;

  • Forms Personalization query

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

  • Concurrent Requests and Programs Related Scripts

    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…

  • Menu and Functions Related Scripts

    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…

  • Send e mail through PL SQL code (UTL_SMTP, UTL_MAIL)

    utl_smtp example: 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 := ‘From: Mr Sender < sender@erpschools.com >‘ || crlf || ‘Subject: Testing…

  • UTL_FILE Read Example

    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…

  • Get On Hand Quantities through API

    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…

  • Ship Confirm through API

    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…

  • Concurrent Program Registration Scripts

    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…

  • Procure to Pay Cycle Query

    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…

  • Workflow Tables and Queries

    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…

  • Revenue Adjustment Script

    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…

  • Add Older revision numbers to an item

    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…