URLs for APPS

OA Framework

===============
http://oracle.anilpassi.com/oa-framework.html
http://oracle.anilpassi.com/xmlimporter-in-oracle-applications-framework.html
http://apps2fusion.com/at/61-kv/317-oa-framework-page-without-login-guest-no-security
http://www.orafaq.com/wiki/JDeveloper
http://oracle.anilpassi.com/jdr-utils.html
http://oracle.anilpassi.com/oa-framework-tutorials-training.html
http://www.dulcian.com/papers/MAOP-AOTC/2002/Don'tBeAMuggle.htm
http://www.dulcian.com/papers/OracleOpenWorld/2002/What%20You%20Need%20to%20Know%20Before%20Building%20Applications%20with%20JDeveloper%209i.htm
http://apps2fusion.com/apps/oa-framework/14-fwk/151-oa-framework-screen-extension-by-embedding-a-custom-page
http://oracle.anilpassi.com/oa-framework-tutorial-01-2.html
http://oracle.anilpassi.com/oa-framework-table-based-screen-2.html
http://www.dbforums.com/oracle/1630066-jdeveloper-resolving-errors-encountered.html
http://appstechnical.blogspot.com/2007/01/oa-framework-tutorials.html
http://oraclearea51.com/oracle-technical-articles/oa-framework/oa-framework-beginners-guide/322-exporting-oa-page-definitions.html
http://oraclearea51.com/oracle-technical-articles/oa-framework/oa-framework-beginners-guide.html
http://www.oracle.com/technology/products/jdev/tips/muench/designpatterns/index.html
http://www.oracle.com/technology/pub/articles/vohra-jdev-xmlpub.html
http://mukx.blogspot.com/2010/01/upload-file-to-application-server-using.html
http://blog.cholaconsulting.com/search/label/OA%20Framework
http://sabersurge.com/oracleappsdev/index.php?option=com_content&view=article&id=54%3Afile-upload-to-database-server-oa-framework&catid=34%3Aoa-framework&Itemid=1
http://www.tier1inc.com/blog_comments.php?pid=12-----Comparing OA Framework with Forms 6i
http://oracle-applications-rama.blogspot.com/2009/01/how-to-search-apps-documents-in-google.html


Oracle Forms Web
Upload, edit and download files from/to the database with the
Webutil library
================================
http://sheikyerbouti.developpez.com/webutil-docs/Webutil_store_edit_docs.htm


Check Java Version
===============
http://java.com/en/download/installed.jsp?jre_version=1.6.0_07&vendor=Sun+Microsystems+Inc.&os=Windows+2000&os_version=5.0

Linux Commands
===============
http://www.ss64.com/bash/
http://teachmeoracle.com/unixa.html
http://www.nixblog.org/post/2008/03/14/UNIX-ID-ORACLE-SESSION
http://www.unix.com/shell-programming-scripting/84635-unix-script-detect-new-file-entry-directory.html

Register Shell Scripts As Concurrent Program
===================================
http://www.notesbit.com/index.php/scripts-oracle/oracle-applications-steps-to-register-shell-script-as-a-concurrent-program/

UTL_FILE_DIR
======================
http://oracleappstechnology.blogspot.com/2008/03/minimize-usage-of-utlfiledir.html

Oracle Applications
===============
http://becomeappsdba.blogspot.com/
http://www.ddbcinc.com/askDDBC/
http://beginapps.blogspot.com/2007_09_01_archive.html
http://knoworacle.wordpress.com/tag/apps-table/
http://appsdba4u.blogspot.com/2007/08/oracle-apps-dba-interview-questions.html
http://cnubandi.blogspot.com/
http://idbasolutions.com/category/papers/3480000-115102/3480000-final-run/
http://becomeappsdba.blogspot.com/2006/08/startup-shutdown-apps-services.html
http://oracleappss.blogspot.com/2008/07/supplier-additional-information.html
http://erp-consultancy.blogspot.com/2008/03/tds-flow-in-accounts-payable-oracle.html
http://etrm.oracle.com/license/MustLogin.html
http://oracle-magic.blogspot.com/2007/06/concurrent-request-and-its-database-sid.html
http://oracle-applications-rama.blogspot.com/
http://www.oracleappshub.com/11i/purchase-order-approval-hierarchy/
http://confluentminds.com/Trainings/SCM/Topic1.1_Ch1_Part4.html
http://forums.oracle.com/forums/thread.jspa?threadID=457983
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/alr/multins.htm
http://oracle.ittoolbox.com/groups/technical-functional/oracle-apps-l/
http://www.aboutoracleapps.com/2007/07/oracle-purchasing-po-faq.html
http://forums.oracle.com/forums/thread.jspa?threadID=664806&tstart=0
http://oracle.anilpassi.com/technical-interview-questions-in-oracle-apps.html
http://www.oracleappshub.com/accounts-receivable/ar-back-to-basictechnical-foundation/
http://www.oracleappshub.com/aol/setting-default-to-excel-for-exported-file-from-file-export/
http://asoracle.blogspot.com/2007/11/key-tables-financials.html
http://oracle.ittoolbox.com/groups/technical-functional/oracle-apps-l/switch-responsibility-icon-on-toolbar-283079
http://oracle.anilpassi.com/forms-personalizations.html
http://www.erpschools.com/Oracle_Apps_Form_Customization.asp
http://www.erpschools.com/
http://realworldoracleapps.blogspot.com/search/label/Welcome%20Note
http://oracleappsrealworld.blogspot.com/
http://mastanreddy.blogspot.com/
http://www.scribd.com/doc/3256741/Oracle-Applications-Developers-Guide
http://garethroberts.blogspot.com/2007/10/excel-file-output-from-oracle.html
http://garethroberts.blogspot.com/2008/01/changing-default-layout-format-from-pdf.html
http://erpcrmapps.blogspot.com/2008/01/using-keyboard-shortcuts.html
http://www.hrmsaces.co.uk/ubbthreads.php/forums/8/1
http://bbs.erp100.com/archiver/tid-36506.html
http://edelivery.oracle.com/EPD/WelcomePage/get_form?ARU_LANG=US
http://oraclepitstop.wordpress.com/2007/04/18/versions-of-components-in-oracle-apps/
http://www.aboutoracleapps.com/2007/08/oracle-general-ledger.html
http://oracle.anilpassi.com/oracle-payroll-tables.html
http://confluentminds.com/Trainings/SCM/Topic2.3_Ch2_Part2.html
http://aksenthil.blogspot.com/
http://knoworacle.wordpress.com/category/oracle-applications-technical/oracle-fixed-assets-technical/
https://metalink.oracle.com/
http://solutionbeacon.blogspot.com/2007/07/simple-tutorial-for-publishing-fsg.html
http://sbllc3.solutionbeacon.net/pls/a159vis2/fndgfm/fnd_help.get/US@PSA_US/fnd/@e_cp
http://apps2fusion.com/apps/oracle-hrms/oracle-hr/hrms
http://oracle.anilpassi.com/creating-customer-address-in-tca-step-by-step.html
http://www.aboutoracleapps.com/2007/07/oracle-apps-manufacturingaolforms.html
http://forums.oracle.com/forums/thread.jspa?threadID=590547
(All In one Blog) http://www.sap-img.com/oracle-database/oracle-application-hints-and-tips.htm
http://www.dba-oracle.com/art_dbazine_conc_mgr.htm
http://dineshnair.wordpress.com/
http://oracle.anilpassi.com/basic-concepts-list-of-useful-oracle-apps-articles-2.html
http://chandramatta.blogspot.com/search/label/Concurrent%20Programs------Very Useful Blog for all Topics

Interfaces
===========
(AR Customer Interface Info.)
http://sbllc3.solutionbeacon.net/pls/a159vis2/fndgfm/fnd_help.get/US@PSA_US/ar/@n_tbl_val@PSA_US
http://download-west.oracle.com/docs/cd/A60725_05/html/comnls/us/ar/cusimprt.htm#n_cust_import
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/ar/autoin05.htm#n_autoval
http://www.erpschools.com/apps/oracle-applications/Articles/General/Interfaces-and-Conversions-in-Oracle-Applications/index.aspx
(Payables)
http://sbllc3.solutionbeacon.net/pls/a159vis2/fndgfm/fnd_help.get/US@PSA_US/AP/@r_openaud@PSA_US
http://irep11i10.oracle.com/OA_HTML/OA.jsp?page=/oracle/apps/fnd/rep/webui/OpenInterfaceDetailsPG&CategoryValue=F&_ti=199580375&retainAM=Y&addBreadCrumb=Y&oapc=7&oas=biKg9_cvMvUQM4gNQIA6ug..
( Customer Interface Error Code Meaning )
http://sbllc3.solutionbeacon.net/pls/a159vis2/fndgfm/fnd_help.get/US@PSA_US/ar/@custerrs@PSA_US



FND LOAD
===========
http://oracle.anilpassi.com/fndload-for-oracle-web-adi.html

R12 Vision Instance Login
=======================
http://vis1200.solutionbeacon.net/OA_HTML/RF.jsp?function_id=1032924&resp_id=-1&resp_appl_id=-1&security_group_id=0&lang_code=US¶ms=gEzpj7eR1-rfQLgP8Ol8DQ3u3xBOeCmcdxx.JgrY94g&oas=-Q6TtBxoQEySwZJoZFr0Fw..

General
==============
http://www.httpsurf.com/
http://www.webindia123.com/history/index.html
http://download.oracle.com/docs/cd/A57673_01/DOC/api/doc/PC_22/ch02.htm
http://www.wapuser.co.cc/2009/12/how-to-hack-others-yahoo-password.html
http://www.osun.org/
http://oracle-applications-rama.blogspot.com/2009/01/how-to-search-apps-documents-in-google.html

Oracle Apps Data Structure
=======================
http://www.scribd.com/doc/404946/Oracle-Apps-Data-Structure

Oracle Apps Scripts
=================
http://www.erpschools.com/Oracle_Apps_Scripts.asp
http://www.erpschools.com/
http://scripts4oracle.blogspot.com/

Metalink Notes
===============
http://teachmeoracle.com/metalink.html
http://www.oracle.com/technology/tech/globalization/index.html

Orcale DB Concepts
===============
http://www.adp-gmbh.ch/ora/concepts/

Autoconfig
===============
http://becomeappsdba.blogspot.com/2006/10/autoconfig-in-apps-template-files.html
http://onlineappsdba.com/index.php/2008/01/28/autoconfig-in-oracle-apps-11i-r12-12i/

TroubleShooting Concurrent Managers
================
http://becomeappsdba.blogspot.com/2006/10/troubleshooting-concurrent-managers.html

NLS Language Parameters
===================
http://it.toolbox.com/wiki/index.php/ORA-12700#NLS_Parameters
http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm#_Toc110410543

Trace Concurrent Request and Generate TKPROF
=======================
http://knoworacle.wordpress.com/2008/06/27/tkprof-trace-a-program/

How to find Processes running in Sunsolaris Operating System
=================================================
http://www.unix.com/sun-solaris/25208-how-find-number-processes.html

Character Set
===================
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch11charsetmig.htm
http://download.oracle.com/docs/cd/B10500_01/server.920/a96529/ch2.htm#745
http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/applocaledata.htm#i634428
http://www.oracledba.co.uk/tips/character_set.htm

ASCII Characters
===================
http://www.alcyone.com/max/reference/compsci/ascii.html
http://www.oracle.com/technology/obe/obe9ir2/obe-nls/localbld/localbld.htm

Oracle SMS Getway Guide
=====================
http://www3.ozekisms.com/index.php?ow_page_number=166

Hindustan Times Corporate News
======================
http://www.hindustantimes.com/ListingPage/ListingPage.aspx?Category=Chunk-HT-UI-BusinessSectionPage-Corporate&SectionName=BusinessSectionPage&BC=CorporateNews

MS Word Mail Merge Tutorial
=======================
http://personal-computer-tutor.com/mailmerge.htm
http://www.frontpage2002.com/frontpage_2003_tutorial_guide.htm

How to start perticular Concurrent Manager
============================
http://teachmeoracle.com/forum/viewtopic.php?t=4320

Company Profile & Overview
==========================
http://info.shine.com/company/Infosys-Technologies-Ltd/102.aspx

Letter Generation using WEB-ADI
=============================
http://apps2fusion.com/at/38-ss/351-generate-recruitment-letters-web-adi

Oracle HRMS Fast Farmula Tutorial
=============================
http://www.aboutoracleapps.com/2008/11/oracle-hrms-fast-formula-tutorial.html
http://oracle.anilpassi.com/index.php?Itemid=4&id=117&option=com_content&show=1&task=view
http://www.erpschools.com/Apps/oracle-applications/articles/hrms/fast-formulas-in-hrms/index.aspx
http://www.aboutoracleapps.com/2009/01/how-to-generate-develop-or-edit.html

Oracle Workflow
========
http://oracleappstechnology.blogspot.com/2008/02/workflow-mails-not-moving-after-fresh.html
http://forums.oracle.com/forums/thread.jspa?messageID=2327979
http://apps2fusion.com/at/gt/tc/328-workflow-mailer-debugging-script-for-debugging-emails-issues
http://onlineappsdba.com/index.php/2008/07/16/oracle-workflow-notification-mailer-outbound-processing/
http://oracleebusinesssuite.wordpress.com/2008/10/18/debugging-the-approval-workflow-for-purchase-order-or-purchase-requisition/
http://oracle.anilpassi.com/workflows-business-events-training-lesson-4.html
http://oracleappstechnology.blogspot.com/2008/05/disable-retention-on-workflow-queues.html
http://www.freelists.org/post/ora-apps-dba/EXPIRED-messages-in-the-WF-NOTIFICATION-OUT-queue-THE-SOLUTION
https://csslxa03.hkcss.org.hk:16298/OA_HTML/oam/helpdoc/oam_wfoam_notificationmailerg.htm
http://arunrathod.blogspot.com/2008/08/workflow-wfload-through-unix.html
http://oracle.anilpassi.com/apps-technology/2.html
http://www.erpschools.com/Oracle_Applications_Workflow_Launching.asp
http://www.erpschools.com/Oracle_Applications_Workflow_Tutorial.asp
http://oracleebusinesssuite.wordpress.com/2008/10/18/debugging-the-approval-workflow-for-purchase-order-or-purchase-requisition/

AME
======
http://greenoracleapps.blogspot.com/

Make Packages Valid
====================
http://idbasolutions.com/3480000-115102-first-run/

DB Upgrade Context File Creation
=========================
http://forums.oracle.com/forums/thread.jspa?threadID=672921&tstart=0&messageID=2612978

Oracle PL/SQL SMS
===========================
http://forums.oracle.com/forums/thread.jspa?messageID=1827704
http://www.dbasupport.com/forums/archive/index.php/t-24763.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839412906735
http://www.erpschools.com/Apps/oracle-applications/Interview-Questions/Tools/PL-SQL/default.aspx

Oracle Apps DBA
============================
http://www.teachmeoracle.com/imptables.html
http://www.dba-oracle.com/art_tr_dyn_sga.htm
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/alr/multins.htm
http://blogs.oracle.com/gverma/2007/11/accrue_economically_dont_recal.html
http://becomeappsdba.blogspot.com/2007/01/change-oracle-apps-password-fndcpass.html
http://oracleappstechnology.blogspot.com/2007/07/query-to-find-out-your-atgpfh-rollup.html
http://onlineappsdba.blogspot.com/2008/06/personalizing-login-page.html
http://www.oracleutilities.com/
http://www.shutdownabort.com/
http://www.dbatoolz.com/sql-scripts
http://krackoracle.blogspot.com/2009_02_01_archive.html

Insert BOB Object (Image) in Database and Display In Report 6i
===============================================
http://sureshvaishya.blogspot.com/2009/08/insert-blob-image-file-into-database.html
http://oracleappsexperience.blogspot.com/

Tunning Oracle Reports
==========================
http://download.oracle.com/docs/cd/B10464_05/bi.904/b13673/pbr_tune.htm#i1007365

FSG Reports
=======================
http://www.oracleappshub.com/finance/fsg-what-is-it/

XML / BI Publisher
======================
http://www.oracleappshub.com/xml-publisher/migration-of-xmlpub-templates-and-data-definitions-across-instances/#more-1084
http://blogs.oracle.com/xmlpublisher/2007/05/howto_java_concurrent_programs.html
http://blogs.oracle.com/xmlpublisher/templates/templates_rtf/
http://asun.ifmo.ru/docs/XMLP/help/en_US/htmfiles/B25951_01/T421739T421827.htm

XML Publisher Concurrent Program - XMLP

In this article I have listed a step by step demo of XML Publisher as a Concurrent Program in Oracle Apps. 

I think it is very important that you get a feel of XML Publisher, as this will become the reporting tool in Fusion [assuming XMLP can become as powerful as Oracle Report in the given time].

Before I explain the steps in details with screenshots, let me brief them:-
a.    Create a report and register it as Concurrent Program of type XML
b.    Build a Data Definition & XML Template using XML Publisher.
c.    Create a relation between XML Template & Concurrent Program.

Sounds simple, it is indeed, but devil lies in detail. Hence read the below carefully.
I have provided the source code with installation instructions for you to try this. Please note to try this you need 11.5.10 environment.

Now lets get into the details….Step by Step…..
1. Create a table xx_emp_demo and insert two records into it.
create table xx_managers ( manager_no integer, manager_name varchar2(200) ) ;
insert into xx_managers ( manager_no, manager_name ) values ( 1000, 'Anil Passi') ;
insert into xx_managers ( manager_no, manager_name ) values ( 1001, 'Martin') ;
commit;

2. Next you need a concurrent program that spit out an XML file to the output of the concurrent program. Such concurrent program can be of type SQL or PL/SQL or Oracle Report or any other supportable type, provided it can produce a XML output.
Hence in this case, we will develop a Report that has all the columns from table xx_managers
Create a plain default Oracle Report using wizard in Oracle Reports 6i.
This can be done using wizard with SQL “select * from xx_managers”


Above picture shows the report output in Reports 6i preview

Do not change anything in DESFORMAT, as Concurrent Manager will take care of passing XML to this. Hence XML Publisher will be fed an XML output by the concurrent manager itself.

3. Add three user parameters P_CONC_REQUEST_ID,  P_RESPONSIBILITY_ID, P_USER_ID

These parameters will make it possible to run a report as concurrent program.
Save the above RDF as XX_MANAGERS.rdf

4. FTP XX_MANAGERS.rdf  to $AR_TOP/reports/US
Silly it is, but for demo I will use standard Application [forgive me, but I am making it easier for you to run the source code].

5. Create Concurrent program executable for XMLP report, as if it is a normal report



6. Create a concurrent program for the executable. Note that I have kept the shortname and the program name both as XX_MANAGERS.
IMPORTANT:- Note that format is XML



7. Register this program with Receivables request group.
DECLARE
BEGIN
FND_PROGRAM.add_to_group
    (
     PROGRAM_SHORT_NAME  =>'XX_MANAGERS'
    ,PROGRAM_APPLICATION =>'AR'
    ,REQUEST_GROUP       => 'Receivables All'
    ,GROUP_APPLICATION   =>'AR'
    ) ;  
  commit;
exception
    when others then
           dbms_output.put_line('Object already exists');
END ;
/

8. Run the report and you will see an xml output appearing. Save the output as XX_MANAGERS_241106.xml on your computer. You will need to use menu /Tool/Copy to open this XML output in browser, and then save it as XX_MANAGERS_241106.xml on your PC.



NOW THE TIME TO DO XML PUBLISHER BIT…

9. Install this XML Publisher Desktop software on your pc, ensuring that you have MS-Word installed(for this demo)
The XML Builder Desktop Patch for Word is Patch Number: 4561835
This software can be downloaded from
http://updates.oracle.com/download/4561835.html

This is a tool given by Oracle[Desktop XML Publisher] that will read any XML file, will create the fields automatically.


10. Open MS Word after installing as per step 9, and you will now see something similar to below in Microsoft Word

Now in MS Word, click on “Load XML Data” and select file XX_MANAGERS_241106.xml that was created in Step 8.







11. Now, click on Insert/Table Form
And then drag and drop “List G Manager No” within the template section and click on OK.



Effectively by doing these steps, you have just created a Layout for the report in XML Publisher. The layout will look like this [as below].



Save this MSWord file as MANAGER_XML_TEMPLATE.rtf

By clicking on Preview, you will see the output as below


12. Now, lets do the real bit in attaching the Concurrent Program definition to XML
Publisher. Logon to responsibility “XML Publisher Administrator”.


Click on “Data Definition” menu as above…so that we can create the XMLs source data Definition. Effectively we are saying that output of the Concurrent program becomes the data definition for XMLP.

Lets define the value in below fields
Name : XX_MANAGERS
Application : Receivables
Code : XX_MANAGERS  [Note: This is the short name of the Concurrent program]
Start Date : Leave it to default Sysdate
Click on “Apply”



13. Now lets define a template as below for XML Publisher.

For doing so, click on Templates Tab. We need to create a Template for the data-definition of previous step.



Name : XX_MANAGERS  [this is the name of the template]
Code : XX_MANAGERS  [short name of conc program]
Application : Receivables
Data Definition : XX_MANAGERS [the one that we created in prior step 12]
Start Date: : Leave this default.
Type : RTF
Click on browse and select the RTF file[MANAGER_XML_TEMPLATE.rtf] that you had saved in Step 11.
xmlp_A_14



Click on Apply, and you will get the below record.



14. Navigate to “Receivables Manager” and submit report XX_MANAGERS.
Click on Output button to see the report as pasted below.

Forms Personalization

In this article, I would like to explain different possibilities of Forms Personalizations. Surely before explaining what can be done, first we will touch base upon what exactly it is.

Why was forms personalization introduced by Oracle in 11.5.10?
1. CUSTOM.pll is a programmatic methodology for extending Oracle Forms, even for trivial tasks.
2. Multiple developers working on same CUSTOM.pll could cause version related headaches

Does Forms Personalization replace CUSTOM.pll?
Not really, however it does provide an alternative to most common activities for which earlier one would use CUSTOM.pll
Forms personalization is driven by the same set of events as that for CUSTOM.pll.
Keep in mind that Oracle Forms Libraries first invoke the Forms Personalization, and then the CUSTOM.pll too for the same set of events.

How do I enable Forms Personalizations?
Enable the personalizations using the Help/Diagnostics menu.
What are the various components of Forms Personalizations?Different components of Oracle Forms Personalizations are listed below. You begin with recognizing which Triggers/Events that you wish to trap and under what conditions. The condition can be entered using the syntax as defined in the picture below.
What does the Action Tab do?Here you will either change form property, or display messages or enable menu etc.
Can I modify SQL Query behind the LOV using Forms Personalizations?Of course you can. In the below example I am restricting the LOV to less than 3records by introducing where rownum <3
Can I call a PL/SQL Stored procedure passing it form field variables?Yes, using the syntax below.
Note: You can also invoke FORMS_DDL built-in to perform DDL.
Can I call BuiltIn's? Indeed, as shown below, you can pick from all the form built-in's as below.



What is forms Personalization?

It is new features where some of the customization can be handled easy by simply setting the rules from the front end.It is available from 11.5.10.2

Why do we need forms Personalization?

For example in scenarios like

1.Change the prompts

2.Display some custom message when certain conditions are met

3.Enable and disabling fields/buttons based on conditions

4.For Enabling special menu.(like opening another reference form from the current form)

IF both Form personalization and custom pll are done for a form which one will take effect first?

Form personalization first and then only custom pll

How to start doing it?

Open the form where you want to personalize .

Menu Navigation: Help ->Diagnostics->Custom Code -> Personalize

This should open the personalization Form.

There are two profile options which will decide whether you able to see or not

Profiles:

Utilities: Diagnostics = Yes/No
Hide Diagnostics = Yes/No

There is new option available for level to define whether the rule need to be applied at form level or function level.Because a form can be attached to multiple functions.

Insert Get Expression & Insert item value button help you in building the conditional statement , very help for non technical people.

The major thing to observe are

1.SEQ: The sequence in which the rules are executed

2.Description:Description of the Rule

3.Level -- whether it is at form level or Function level..

4.Condition –what is the event and the object where the below condition need to

be checked

5.Action-- what actions it should perform when the condition is met

6.Context -- same a profile option levels at what level the rule need to be applied

Under actions

SEQ: it gives the list of actions that need to be performed when the condition is met

Type:1.Property – used for changing the properties of field

Message –used to display message when the condition is met

Built in --used to calling some standard form and AOL functionalities 

Menu -- used in case of enabling special menu’s

General & Important Observations:

All actions and rules can be enabled and disabled(using checkbox) or deleted using delete option on the tool bar

Always validate the rule after you create using validate button

Save the record before you close this personalization form

To move form personalization from one instance to other there is FNDLOAD utility available .

Check the site for FNDLOAD examples…

STANDARD FORMS CUSTOMIZATION(PART1--CUSTOM PLL )

There are different ways to handle Forms Level Customizations.
1.Using Custom.PLL
2.Using Forms Personalizations
3.Copy the Standard form Object and Change the Code

First lets see Custom.pll how to use it?


What is Custom.PLL??
The CUSTOM.pll library is a standard Oracle Forms PL/SQL library that is supplied by Oracle with the Oracle
Applications. This is Oracle’s built-in feature that allows the customer to enhance the standard functionality of the
Applications by implementing site-specific business rules. Every Oracle Forms -based eBusiness screen, and any
custom form developed using the Oracle Application development standards, will access the CUSTOM library.
This makes an ideal point of creating business rules that effect the entire organization.

Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.

How to add code to this ?
open this pll using the Form builder.make changes to the program units

How to compile this PLL ?
Once you make changes you need to compile the pll.use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special

What are Different Triggers that is supported?

WHEN-NEW-FORM-INSTANCE – initially entering a form

WHEN-NEW-BLOCK-INSTANCE – entering a zone (or block) within a form

WHEN-NEW-ITEM-INSTANCE – moving into a new field within the form

WHEN-NEW-RECORD-INSTANCE - creating a new record

WHEN-FORM-NAVIGATE – navigating thru a form using the mouse

WHEN-VALIDATE-RECORD – saving (committing) the information to the database

EXPORT – triggered by using the Export feature Some events are field specific

ZOOM – Pre -11 feature for moving to another form and querying up specific records

Some events are form specific

SPECIALn - (where n is a number between 1 and 45) used to generate entries in the ‘Special’ menu of the
tool bar and the code is triggered by selecting a menu choices from the ‘Special’ option on the toolbar

KEY-Fn – (where n is a number between 1 and 8) triggered by pressing the corresponding function key

Some events are application specific:
Application Object Library

WHEN-LOGIN-CHANGED – when a user logs on as a different user
WHEN-RESPONSIBILITY-CHANGED – when a user changes responsibilities
WHEN-PASSWORD-CHANGED – when a user changes their password

How to make changes get affected?
Once you make the changes compile the pll and generate the PLX
Since the CUSTOM library is loaded once for a given session, a user must log out of the
application and sign-on again before any changes will become apparent.



Examples--Metalink:

1. Sample code to make all the responsibilities read only for a specific user.
BEGIN

IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
IF FND_PROFILE.VALUE('USER_NAME')='' THEN

BEGIN
COPY('Entering app_form.query_only_mode.','global.frd_debug');
COPY('YES', 'PARAMETER.QUERY_ONLY');
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
formname := NAME_IN('system.current_form');
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);

WHILE (blockname is not null) LOOP

IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN

SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);

END IF;

blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);

END LOOP;


END query_only_mode;


END;

2.How does one restrict or reduce the LOV?
"The customer LOV can be overriden using the when-new-item-instance or when-new-form-instance event at the form level through CUSTOM.pll."
You will need to write custom code using that specific event in the custom.pll

Some sample codeif (event_name = 'WHEN-NEW-FORM-INSTANCE' and form_name = 'form name')then
r:=find_group('group name');
if not id_null(r) then
delete_group('group name');
end if;
v:='select colum1,column2
from table';

r:=create_group_from_query('group name',v);

set_lov_property('lov NAME',group_name,r);--lov

See that the column names should be same as the old query so that the mappings still holds good


3.How to make the attachment function in specific responsibilities to act as read-only mode so that users who log into these specific responsibilities can only view attachments, while for the rest of the responsibilities allow users to add, update and delete attachments?


// Source File Name: custom.pll
// Source File path: $AU_TOP/resource

form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'FNDATTCH') then
if (FND_GLOBAL.RESP_NAME Like '') then -- 
Set_item_Property( SEQ_NUM, ENABLED,PROPERTY_FALSE);
Set_item_Property( CATEGORY_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DOCUMENT_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DATATYPE_NAME, ENABLED,PROPERTY_FALSE);
Set_item_Property( FILE_NAME_DISPLAY, ENABLED,PROPERTY_FALSE);
end if;
end if;
end if;

4. How to make the customisation CustomPO Number not less than PO 4 digits in sales order form?

procedure event(event_name varchar2) is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
item_name varchar2(30) := name_in('system.cursor_item');

Begin
if (form_name = 'OEXOEORD'and block_name = 'ORDER') then
if LENGTH(name_in('ORDER.CUST_PO_NUMBER')) > 3 then
fnd_message.set_name('FND','Cust PO Number should be less than 4 digits');
fnd_message.Error;
RAISE FORM_TRIGGER_FAILURE;
End if;
End if;
End Event;

Oracle Inventory Tables

MTL_PARAMETERS:

It maintains a set of default options like general ledger accounts; locator, lot, and serial controls; inter–organization options; costing method; etc. for each organization defined in Oracle Inventory.

Each organization’s item master organization (MASTER_ORGANIZATION_ID) and costing organization (COST_ORGANIZATION_ID) are maintained here. You specify which manufacturing calendar the organization will use (CALENDAR_CODE), as well as the set of exceptions to this calendar (CALENDAR_EXCEPTION_SET_ID).

NEGATIVE_INV_RECEIPT_CODE indicates whether this organization will allow negative balances in inventory. It is used in the transaction system to ensure, for example, that you do not over–issue a particular item (if you have indicated that negative balances are not allowed). For lot number and serial number auto–generation, you can specify whether you want the numbers generated from defaults defined at the organization level or the item level (LOT_NUMBER_GENERATION,

SERIAL_NUMBER_GENERATION). You can also specify uniqueness constraints for lot numbers (LOT_NUMBER_UNIQUENESS) and serial numbers (SERIAL_NUMBER_TYPE). Among the accounting defaults that can be defined here are valuation accounts, receiving accounts, profit and loss accounts, and inter– organization transfer accounts.

MTL_SYSTEM_ITEMS:

This is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. You can specify item–related information in fields such as: Bill of Material, Costing, Purchasing, Receiving, Inventory, Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing. You can set up the item with multiple segments, since it is implemented as a flex field. Use the standard ’System Items’ flex field that is shipped with the product to configure your item flex field. The flex field code is MSTK.

The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Each item is initially defined in an item master organization. The user then assigns the item to other Organizations that need to recognize this item; a row is inserted for each new organization the item is assigned to. Many columns such as MTL_TRANSACTIONS_ENABLED_FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in then MTL_ITEM_ATTRIBUTES table.

Two units of measure columns are stored in MTL_SYSTEM_ITEMS table. PRIMARY_UOM_CODE is the 3–character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25–character unit that is used throughout Oracle Purchasing. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only.

MTL_ITEM_STATUS

MTL_ITEM_STATUS is the definition table for material status codes. Status code is a required item attribute. It indicates the status of an item, i.e., Active, Pending, Obsolete. The status optionally controls several ’function controlling’ item attributes (status attributes) and can be used to disable the item from being used by the various application products.

MTL_UNITS_OF_MEASURE_TL

MTL_UNITS_OF_MEASURE_TL is the definition table for both the 25–character and the 3–character units of measure. The base_uom_flag indicates if the unit of measure is the primary unit of measure for the uom_class. Oracle Inventory uses this table to keep track of the units of measure used to transact an item. This table is also used by Oracle Purchasing, Oracle Order Management and Oracle Shipping Execution.

MTL_ITEM_LOCATIONS

MTL_ITEM_LOCATIONS is the definition table for stock locators. The associated attributes describe which sub inventory this locator belongs to, what the locator physical capacity is, etc. The locator is a key flex field. The Flex field Code is MTLL.

Entity 2

MTL_ITEM_CATEGORIES

This table stores the item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items may be assigned to multiple categories and category sets but may be assigned to only one category in a given category set. This table may be populated through the Master Items and Organization items windows. It can also be populated by performing item assignments when a category set is defined. It is also populated when an item is transferred from engineering to manufacturing.

MTL_CATEGORIES

This is the code combinations table for item categories. Items are grouped into categories within the context of a category set to provide flexible grouping schemes. The item category is a key flexfield with a flex code of MCAT. Item categories now support multilingual category description. MLS is implemented with a pair of tables: MTL_CATEGORIES_B and MTL_CATEGORIES_TL.

MTL_CATEGORY_SETS

MTL_CATEGORY_SETS contains the entity definition for category sets. A category set is a categorization scheme for a group of items. Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes. An item may be assigned to only one category within a category set, however, STRUCTURE_ID identifies the flexfield structure associated with the category set. Only categories with the same flexfield structure may be grouped into a category set.

CONTROL_LEVEL defines whether the category set is controlled at the item or the item/organization level. When an item is assigned to an item level category set within the item master organization, the category set assignment is propagated to all other organizations to which the item is assigned. VALIDATE_FLAG defines whether a list of valid categories is used to validate category usage within the set. Validated category sets will not allow item assignment to the category set in categories that are not in a predefined list of valid categories. Category Sets now support multilingual category set name and description. MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL.

Entity 3

MTL_DEMAND

This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows, Open Demand Rows, and Reservation Rows.

Summary Demand is direct demand for an item within an organization on a particular date that originated from a particular source. For hard reservations there are several columns which further define what the reservation is for, and where it is being placed. Currently, four sources of demand are supported, Sales Order, Account, Account Alias, and User Defined transaction sources. Five different types of demand, denoted by DEMAND_TYPE column, are used. These five types are Model, Option Class, Option Item, Configuration Item and Derived. Derived demand rows are inserted by BOM Demand exploder when demanded item has ATP table components. Each Summary Demand row may be associated with one or more Reservation rows. Reservation may be placed against a particular inventory control (that is, specific sub inventory, locator, revision and lot) against any sources (that is, Account Number, Account Alias, Sales Order or even User–Defined sources). Each Summary Demand row may be associated with one or more detailed rows. The detailed rows consist of reservations and open demand. A reservation row represents a firm promise of a supply source. Currently, two types of reservation are supported, reservations to on–hand, and reservations to WIP jobs. Each summary demand row may be associated with one and only one open demand row. Open Demand rows represent the un–reserved portion of the Summary Demand.

MTL_SECONDARY_INVENTORIES

MTL_SECONDARY_INVENTORIES is the definition table for the sub inventory. A sub inventory is a section of inventory, i.e., raw material, finished goods, etc. Sub inventories are assigned to items (in a many to one relationship), indicating a list of valid places where this item will physically exist in inventory. Other attributes include general ledger accounts, demand picking order, locator type, availability type, and reservable type. You can also specify whether this sub inventory is an expense or asset sub inventory (ASSET_INVENTORY) or whether quantities are tracked (QUANTITY_TRACKED).

MTL_ONHAND_QUANTITIES

MTL_ONHAND_QUANTITIES stores quantity on hand information by control level and location. MTL_ONHAND_QUANTITIES is maintained as a stack of receipt records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria. Note that any transactions which are committed to the table MTL_MATERIAL_TRANSACTIONS_TEMP are considered to be played out as far as quantity on hand is concerned in Inventory transaction forms. All our Inquiry forms and ABC compile are only based on MTL_ONHAND_QUANTITIES.MTL_ONHAND_QUANTITIES has two columns, CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_IDs to join to MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID the transactions that created the row and the transaction that last updated a row.

Entity 4

MTL_TRANSACTION_TYPES

MTL_TRANSACTION_TYPES contains seeded transaction types and the user defined ones. USER_DEFINED_FLAG will distinguish the two. The table also stores the TRANSACTION_ACTION_ID and TRANSACTION_SOURCE_TYPE_ID that is associated with each transaction type.

MTL_MATERIAL_TRANSACTIONS

MTL_MATERIAL_TRANSACTIONS stores a record of every material transaction or cost update performed in Inventory.

Records are inserted into this table either through the transaction processor or by the standard cost update program. The columns

TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID and TRANSACTION_SOURCE_NAME describe what the transaction is and against what entity it was performed. All accounting journal entries for a given transaction are stored in MTL_TRANSACTION_ACCOUNTS, joined by the column TRANSACTION_ID. If the item is under lot control then the lot records are stored in

MTL_TRANSACTION_LOT_NUMBERS, joined by the column TRANSACTION_ID. If the item is under serial control then the serial records are stored in MTL_UNIT_TRANSACTIONS, joined by the column TRANSACTION_ID. The Item revision and locator control are stored in the columns REVISION and LOCATOR_ID respectively. Transfer transactions are represented as two single records in the table. They are related through the column TRANSFER_TRANSACTION_ID, which contains the TRANSACTION_ID of the other transaction in the transfer. The index MTL_MATERIAL_TRANSACTIONS_UPGD is used only during install and upgrade, and will be dropped during the course thereof, but is included here for completeness.

Oracle Purchasing Tables

PO_REQUISITION_HEADERS_ALL REQUISITION_HEADER_ID,

PREPARER_ID,

SEGMENT1,

SUMMARY_FLAG,

ENABLED_FLAG

Stores information about requisition headers. You need one row for each requisition header you create. Each row contains the requisition number, preparer, status, and description.SEGMENT1 is the number you use to identify the requisition in forms and reports (unique).

PO_REQUISITION_LINES_ALL

REQUISITION_LINE_ID,

REQUISITION_HEADER_ID,

LINE_NUM,

LINE_TYPE_ID,

CATEGORY_ID,

ITEM_DESCRIPTION,

UNIT_MEAS_LOOKUP_CODE,

UNIT_PRICE,

QUANTITY,

DELIVER_TO_LOCATION_ID,

TO_PERSON_ID,

SOURCE_TYPE_CODE

Stores information about requisition lines, Line number, item number, item category, item description, need–by date, deliver–to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line.

LINE_LOCATION_ID - purchase order shipment line on which you placed the requisition. it is null if you have not placed the requisition line on a purchase order.

BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement or catalog quotation line information for the requisition line.

PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you exploded or multistoried this requisition line.

PO_HEADERS_ALL PO_HEADER_ID,
AGENT_ID,

TYPE_LOOKUP_CODE,

SEGMENT1,

SUMMARY_FLAG,

ENABLED_FLAG

Information for your purchasing documents.There is six types of documents that use PO_HEADERS_ALL

RFQs,

Quotations,

Standard purchase orders,

planned purchase orders,

Blanket purchase orders,

Contracts, can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using PO_HEADER_ID.BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders.

If we use copy document Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID.

PO_LINES_ALL PO_LINE_ID,

PO_HEADER_ID,

LINE_TYPE_ID,

LINE_NUM

Stores current information about each purchase order line. CONTRACT_NUM reference a contract purchase order from a standard purchase order line.

PO_VENDORS VENDOR_ID,

VENDOR_NAME,

SEGMENT1,

SUMMARY_FLAG,

ENABLED_FLAG

Information about your suppliers. Purchasing, receiving, payment, accounting, tax, classification, and general information.

PO_VENDOR_SITES_ALL VENDOR_SITE_ID,

VENDOR_ID,

VENDOR_SITE_CODE

Information about your supplier sites. a row for each supplier site you define. Each row includes the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this information to store supplier address information.

PO_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID,

PO_HEADER_ID,

PO_LINE_ID,

LINE_LOCATION_ID,

SET_OF_BOOKS_ID,

CODE_COMBINATION_ID,

QUANTITY_ORDERED,

DISTRIBUTION_NUM

Contains accounting distribution information for purchase order shipment line. You need one row for each distribution line you attach to a purchase order shipment.

There are four types of documents using distributions in Oracle Purchasing:

Standard Purchase Orders,

Planned Purchase Orders,

Planned Purchase Order Releases,

Blanket Purchase Order Releases

Includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution.

PO_RELEASES_ALL PO_RELEASE_ID,

PO_HEADER_ID,

RELEASE_NUM,

AGENT_ID,

RELEASE_DATE

Contains information about blanket and planned purchase order releases. You need one row for each release you issue for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment

PO_VENDOR_CONTACTS VENDOR_CONTACT_ID,

VENDOR_SITE_ID

Stores information about contacts for a supplier site. You need one row for each supplier contact you define. Each row includes the contact name and site.

PO_ACTION_HISTORY OBJECT_ID,

OBJECT_TYPE_CODE,

OBJECT_SUB_TYPE_CODE,

SEQUENCE_NUM

Information about the approval and control history of your purchasing documents. There is one record in this table for each approval or control action an employee takes on a purchase order, purchase agreement, release, or requisition.

Stores object_id -- Document header identifier,

OBJECT_TYPE_CODE --- Document type,

OBJECT_SUB_TYPE_CODE --Document subtype

SEQUENCE_NUM --Sequence of the approval or control action for a document

PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_ID,

REQUISITION_LINE_ID,

SET_OF_BOOKS_ID,

CODE_COMBINATION_ID,

REQ_LINE_QUANTITY,

DISTRIBUTION_NUM

Stores information about the accounting distributions associated with each requisition line.

PO_LINE_LOCATIONS_ALL LINE_LOCATION_ID,

LAST_UPDATE_DATE, 

LAST_UPDATED_BY,

PO_HEADER_ID,

PO_LINE_ID, 

SHIPMENT_TYPE

Contains information about purchase order shipment schedules and blanket agreement price breaks. You need one row for each schedule or price break you attach to a document line. There are seven types of documents that use shipment schedules:

RFQs, Quotations, Standard purchase orders, planned purchase orders, planned purchase order releases, Blanket purchase orders, Blanket purchase order releases each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders, and price break information for blanket purchase orders, quotations and RFQs.

ORA-04091: Table is Mutating

Many a times you must have encountered the issue ORA-04091: table SYSTEM.TEST1 is mutating, trigger/function may not see it.

This issue basically persists in PLSQL triggers. At one time I was also hit by the same issue. One of my friend “Nikhil Tungare – PLSQL Guru” helped me to get rid of this issue.
Here is how the error can be reproducible.
SQL> create table test1
2  (col1 varchar2(10),
3  col2 number);
Table created.
SQL> create table test1_audit
2  (col1 number,
3  time date);     
Table created.
SQL> create or replace trigger mutat_trig
2  after insert on test1
3  for each row
4  declare
5  id number;
6  begin
7  select col2 into id from test1
8  where col2 = :NEW.col2;
9  insert into test1_audit values (id, sysdate);
10  end;
11  /
Trigger created.
SQL> insert into test1 values(‘test’,1);
insert into test1 values(‘test’,1)
*
ERROR at line 1:
ORA-04091: table SYSTEM.TEST1 is mutating, trigger/function may not see it
ORA-06512: at “SYSTEM.MUTAT_TRIG”, line 4
ORA-04088: error during execution of trigger ‘SYSTEM.MUTAT_TRIG’
The reason for this error is because, you have a table and you are inserting a row. Now as soon as you insert a row, a trigger is fired which will select the inserted data. This causes a problem because the data is inconsistent, it is not yet commited. Oracle engine allows only commited data to be queried.
This problem comes with row level trigger only, because row level trigger will gets fired immidiately as soon as you insert a row in a table. Statement level trigger will get fired after every statement.
To get rid of this problem and still use row level trigger, we have the solution as given below.
We need to create following triggers to avoide this issue.
1. After RowLevel
2. After Statement Level
Also we need to declare a global variable to store the value of ID that we will select. This global variable should be declared in package, so that we can access it when ever required.
Create a package, which will hold global variable
SQL> CREATE OR REPLACE PACKAGE test_package AS
2  id test1.col2%TYPE;
3  END;
4  /
Package created.
1) Creating After Row level trigger to populate the global variable with required value
SQL> create or replace trigger mutat_trig
2  after insert on test1
3  for each row
4  begin
5    test_package.id := :new.col2;
6  end;
7  /
Trigger created.
2) Create a After Statement level trigger to insert into test1_audit table.
SQL> create or replace trigger insert_audit
2  after insert on test1
3  begin
4  insert into test1_audit values (test_package.id, sysdate);
5  end;
6  /
Trigger created.
Trying to insert value now.
SQL> insert into test1 values(‘test’,1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1_audit;
      COL1 TIME
———- ———
1 30-AUG-07
So always avoid selecting the value from the table, which is not yet committed. Thanks for Nikhil for helping me figure out this very closely.

Script for Creating sales person(CRM)

DECLARE

CURSOR get_credit_id(v_name VARCHAR2)
IS
select sales_credit_type_id
FROM oe_sales_credit_types
WHERE name = v_name;

l_Credit_id NUMBER;
l_salesrep_id NUMBER;
l_msg_count NUMBER;
l_msg_Data varchar2(2000);
l_return_status varchar2(1);

l_msg_index_out  NUMBER;

BEGIN

OPEN get_credit_id('Quota Sales Credit');  ----Pass the Sales Quota Type
FETCH get_Credit_id INTO l_credit_id;
CLOSE get_Credit_id;

dbms_application_info.set_client_info('240'); ---Set the ORG if being run from SQLPLUS

jtf_rs_salesreps_pub.CREATE_SALESREP(
 P_API_VERSION                  => 1.0,
 P_INIT_MSG_LIST                => 'T',
 P_COMMIT                       => 'T',
 P_RESOURCE_ID                  => 100000020,          ----Get the resource id from JTF_RS_RESOURCE_EXTNS
 P_SALES_CREDIT_TYPE_ID         => l_credit_id,
 P_NAME                         => 'Kanti Jinger',  ----Name of the resource
 P_STATUS                       => NULL,
 P_START_DATE_ACTIVE            => sysdate,
 P_END_DATE_ACTIVE              => NULL,
 P_GL_ID_REV                    => NULL,
 P_GL_ID_FREIGHT                => NULL,
 P_GL_ID_REC                    => NULL,
 P_SET_OF_BOOKS_ID              => 1001,          ------Replace with your set of Books ID
 P_SALESREP_NUMBER              => 'ABCD00991',
 P_EMAIL_ADDRESS                => ----Replace with Email ID of the user
 P_WH_UPDATE_DATE               => sysdate,
 P_SALES_TAX_GEOCODE            => NULL,
 P_SALES_TAX_INSIDE_CITY_LIMITS => NULL,
 X_RETURN_STATUS                => l_return_status,
 X_MSG_COUNT                    => l_msg_count,
 X_MSG_DATA                     => l_msg_data,
 X_SALESREP_ID                  => l_salesrep_id);

dbms_output.put_line('return status is ' || l_return_Status);

 FND_MSG_PUB.GET(p_msg_index     => 1,
                 p_encoded       => 'F',          
                 p_data          => l_msg_data,
                 p_msg_index_out => l_msg_index_out);  
                         
 DBMS_OUTPUT.put_line('API Error Message : '||l_msg_data);

dbms_output.put_line('msg data is  ' || l_msg_data);
dbms_output.put_line('Sales Rep id is ' || l_salesrep_id);

END;