Sunday 2 November 2014

Diff Between InboundInterface and Conversion

Diff Between InboundInterface and Conversion:
===================== ================

Interface                                     Conversion
=========                            ==========

1)Interface will be used to upload the            1)Conversion is one-Time Data Transfer
  data multiple times.
  Regularley every week or month or day

2)Client Will be using Legacy system.     2)Client will use Only ORacle Applications.
  and also Oracle Applications                       once data is uploaed from legacy then legacy
                                 system will be closed.

3)We will not be knowing the Data file  3)here we will be knowing the data file volume
  volume.                                     exactley.
4)In Customization and as well as             4)Normally in the Implementation Porjects we
 implementation projectes we will be              will be using.
  developing Interface.

Monday 29 September 2014

Manual form development in Oracle apps using form builder

1)Open the TEMPLETE.fmb in form builder
2)Delete Canvas,Window,Datablock
3)Create New Canvas,Window attach Property classes
4)Create New Datablock select Manuval Development
5)Goto Properties of Datablcok
  1)SubClass Information     = Block
  2)Query Data SourceName    = TableName
  3)Query Data SourceColumns = Select Colunmns and Datatypes
6)Goto Canvas Define the Frame attach Properties
    1)SubClass Information     = FRAME_RECT
    2)Layout Datablock         = Select the Datablock Whatever we have created
7)Define Boiler Plate Text and Texst Items
  for Text Items attach folowing Property Classes
  1)SubClass Information     = TEXT_ITEM
  2)Coluimn Name     = Database Column Name
8)Save the form
9)Change the Property called first Navigation Datablock
10)Customize Pre-Form
    WHEN-NW-FORM-INSTANCE Triggers
    APP_CUSTOM package
11)Save the .fmb AND generate the .fmx
12)Register the Form in Application Developer

Implement Check Boxes:
========================
1)Define the check Box in the Canvas
2)Attach following Property Classes
    1)SubClass Information       = CheckBox
    2)Enter Value when Checked   = whatever value we want to insert into Column
    3)Enter Value when UnChecked = whatever value we want to insert into Column
    4)Initial Value              =Default Value
    5)Label                      =Whatever the Name we would like to display
    6)Column Name                = Database Column Name
Implement Radio Buttons:
========================
1)Define the Radio Buttons
2)Under Same Group
3)Attach following Properties
    1)SubClass Information       = Radio Button
    2)Label                      =Whatever the Name we would like to display
    3)Radio Button Value         =whatever value we want to insert into Column
4)Goto Object Navigator Select Radio Group Proiperties    
5)Attach following Properties
    1)SubClass Information       = Radio Group
    2)Initial Value              = Default Value
    3)Column Name                = Database Column Name
    

Custamization oracle apps forms using Code based method(ZOOM)


1)Open CUSTOM.pll go to the function called zoom_available
 and write the following code in the palce of "Real Code Starts Here"

    form_name  varchar2(30) := name_in('system.current_form');
    block_name varchar2(30) := name_in('system.cursor_block');
  begin
    if (form_name = 'POXRQERQ' and block_name = 'PO_REQ_HDR') then
      return TRUE;
    else
      return FALSE;
    end if;
2)Goto the Event Procedure and write the follwing code in the place of "Real Code Starts here"
   
form_name      varchar2(30) := name_in('system.current_form');
    block_name     varchar2(30) := name_in('system.cursor_block');
    begin
 
     if (event_name = 'ZOOM')  then
      if (form_name = 'POXRQERQ' and block_name = 'PO_REQ_HDR') then      
        fnd_function.execute(function_name =>   'PO_POXRQVRQ',
        open_flag     =>   'Y');
      end if;
     end if;
3)Save the CUSTOM.pll and compile this Execute the CUSTOM.pll
4)Copy both .pll and .plx into the Resource folder
(close the Application before copying into the Application).

Profiles in Oracle apps

Profile :
Profile is one of the changeable option it will change the way of application execution.

When User Log in to the application and select the the resp or Appl
system will automatically captures all the profile value as per the
profile values application will run.

Ex: If client have three Organizations 1)Hyd
      2)Ban
      3)Chn
If "hyd" users connect to the Application system will retrive the
data from database which is related to the Hyderabad branch.
If user is working for 'CHN' brnach then chennai branch setups or data
will be retrieved.

For every user we will assign the Profile value

Ex: Operation

Position - Profile Name
Profile Values
--------------
Manager
Supervisior
Clerk
Operator
Trainess

When we want assign any profile value we have four levels
we have to select any one of the level.


Profile Level Profile  Profile Value
------------- -------  --------------
User - OPERATIONS -  Print  - 10(This is for for Operations)
Responsibility  - 22Responsi -  Print  -  5(This is for 22resp users)
Application     - GL Applica -  Print  -  4(This is for GL App Users)
Site - ---        -  Print  -  2(This is for ALL Users)

Site : this is lowest level to assign the Profile values site values
are applicable for all the users.when we install Application by default
site level values will be assigned.

Application: These values are applicable for the users who are having
the access for the application. If user is eligible for both
application and site level values then application level value will
override the site level value.

Responsibility:We will select the responsibility name assign the value
which is applicalbe only for the users who are having the access for
specified responsibility.

Responsibility level value will override both application and site
level values.

User: This is highest level in the profile option.
we will select the user name and assign the profile value which is
applicable only for this user.
User level value will override all other profile level values.

Diff between Application and Responsibility:
============================================
Both are Group of Forms(Menu)
         Group of ConcurrentPrograms(Request Group)
         Group of Users (Data group)
But Application as per the Business functionality requirement
Responsibility will group as per the position requirement.

Some of the Imp Profile Names:

GL:Set Of Books
MO:Operating Unit
Hr:Business Groups
MFG_ORGANIZATION_ID
USER_ID
RESP_ID
USERNAME
RESP_NAME and so on..........


note: In the Projects most of the profile values will be assigned at the Responsibility
level.


Diff Between Application and Responsibility:
    ===========     ===============

Applciation is nothing Colletion of Forms,Reports and Program which are related for
specific business functionality.

Responsibility is nothing but Colletion of Forms,Reports and Program which are related for
specific Position in the Organization.

For Ex : We have to create One Responsibility For the Clerk. Which is accesable by all
         the Clerks.
     It Contains the Forms and Reports which are required for the Clerk.

We have to Create new Responsibility for the Manager,Which is accesable by all the
Managers.
It COntains the Forms and Reports which are required for the manager.

Where as Application includes all the Forms,Reports and Programs.If we assign the
application to the user he will access all the forms and Reports.
Intead of that we will create the responsibility and we will assign to the User.        
         
Common Profiles:
================

Gl:Set of Books: Which is Financial Profile option will be uset to assin SetofBooks
HR:Business Group : Which will used to assign the Business Group
MO:Operating Unit : To assign the Operating Unit (Branch) to the users.
MFG_ORGANIZATION_ID: Will Be used to assign the Manufacturing Organization ID.

USER_ID
USERNAME
RESP_NAME   and so on.........

We can find all the Profile details in Application Developer Responsibility.
We can assign the Profile values in System Administrator Responsibility.

Application Developer=>Profile =>Press CTRL+F11 we can find all the profiles.

System administrator=>profile=>System=> Select Profilename,Level =>Find button
then assign the Profile value.

Set Of Books :SOB is nothing but collection of Currency
       Calendar
       Chart of Accounts.
We will assign the SOB as a profile value to the user as per the Profile value system
will automatically change the application running.

Base on the SOB  name we can find the change in the currency and calendar and accounts

SELECT     NAME,
  CURRENCY_CODE,
  PERIOD_SET_NAME,
  CHART_OF_ACCOUNTS_ID
FROM GL_SETS_OF_BOOKS

SELECT * FROM GLFV_CHARTS_OF_ACCOUNTS  WHERE CHART_OF_ACCOUNTS_ID = 50713

22USER   GL:Set Of Books   Vision Operations (USA)   USD
23USER   GL:Set Of Books   Vision Korea     KRW
24USER   GL:Set Of Books   Vision Italy     ITL

Create Three users
Assign Profile values from System administrator (Profile=>System)
open the GL Form and verify the curency values  (GL=>Journal=>Enter=>new Journal)

Note: Most of the profile values will be assigned  at Responsibility Level.


Retrieve the Profile Value from Backend:(SQL,PL/SQL,Forms6i,Reports6i)
======================================

Fnd_Profile.Get('ProfileName',
local Variable);

local Variable:= Fnd_Profile.Value('Profile Name');

Both API's will be used to retrieve the Profile value frombackend

Get()   is Procedure
Value() is Function

Oracle Has provided both Procedure and Function becuase in some of the areas we can not
use procedure then we can use function.

For Ex: in SELECT clause we can not use procedure we have to go for using the Function.




1)We would like to display the Set of Books name
      User name
      Respname     in the first page of the report.

22USER
23USER
24USER

Ans)

1)Define the Local Variable
2)Goto before Report Trigger write the follwoing API
 

  :P_SOBNAME:= Fnd_Profile.value('GL_SET_OF_BKS_NAME');
  :USERNAME := Fnd_Profile.value('USERNAME');
  Fnd_Profile.Get('RESP_NAME',
    :RESPNAME);
3)Goto Layout model Header section and Display the Variable Name.
4)Submit from Diff Users and test the Output we can find the Difference.



2)Develop the PL/SQL Program for vendor Name updation. Vendor name should be updated
  if "OPERATIONS" user submit the Program for other users should not get update.

Parameters are    VendorID
 VendorName  

Create Or Replace Procedure ven_update(Errbuf  OUT varchar2,
         Retcode OUT varchar2,
     v_id   IN number,
     v_name  IN varchar2)  as
l_name   varchar2(100);
begin
l_name:=Fnd_Profile.value('USERNAME');
If l_name = 'OPERATIONS' then
UPDATE PO_VENDORS
SET VENDOR_NAME = v_name
WHERE VENDOR_ID =v_id;
commit;
Fnd_File.Put_line(Fnd_File.Output,'vendorname has updated succesfully');
Else
Fnd_File.Put_line(Fnd_File.Output,'Access Denied for updateion');
End If;
End;

Note: We can pass the profile value as default value by using Profile default type.
 Select Default type = profile
        Default Value= Profile Name
When we are passing Profile value  as default we are suppose to hide the Parameter
because profile is confidential Information we are not suppose to give permission for
modifications.

Procedure registration in Oracle apps

steps for procedure registration in Oracle apps........
-------------------------------------------------------------------------------------------

1)Develop the procedure and compile  at SQL prompt
2)Connect application select system administrator Responsbility create
  Executable by selecting the Execution Method as 'PL/SQL Stored Procedure'
3)Create Concurrent Program and attach Executable to the Program and add the
  Parameters and  incompatible programs.
4)Create Request group attach concurrent Progrtam
5)Attach Request group to the reponsibility  
6)Responsiboility  will be added to the user.
 User will submit the request from SRS Window.

Syntax:
Create Or Replace Procedure <ProcedureName> (Errbuf OUT varchar2,
    Retcode OUT varchar2,
    P1    IN NUMBER,
    P2    IN VARCHAR2,
    P3    IN DATE) AS
Local variable,Cursor,Collections Declare;
Begin

If statement
For Loop
Procedure Calling
DBMS_OUTPUT.Put_Line - This will not be used instead of this Fnd_File API will be used

FND_FILE.PUT_LINE(Fnd_File.Log   ,'Message'||Variable Name);
FND_FILE.PUT_LINE(Fnd_File.Output,'Message'||Variable Name);
Exception
When Other then
-Exception Statements;
End <Procedure Name>;

PL/SQL Procedure with Parameter:
================================
If we have any user defined Parameters then we have to register these parameters at the
time of Creating the COncurrent Program by selecting the Parameter button
enter the Seqno
 Parameter Name
 Value Set
Note : Token Field will be disabled.
Here First Parameter value will be passed to the first variable
Second Parameter will be passed to the second variable and so on........

When we are registering the report as C.P then only we required TOKEN field.
Because report builder bind variables may or may not be in the sequence that's why
we have to map with Token field.

Where as in Procedure variables position is fixed then TOKEN field will be disabled.

Sunday 28 September 2014

Report registration steps in Oracle apps

Report Registration Steps:
--------------------------------
1)Develop the report (.rdf)as per client Requirement by using reports 6i Builder

2)Move the report from Local mechine into Server
  CUS_TOP\11.5.0\Reports\US\ .rdf
   PO_TOP\11.5.0\Reports\US\ .rdf

3)Select System Administrator and create EXECUTABLE
1)Executable Name
2)ApplicationName
3)Execution Method
4)Report(.rdf) file Name

4)Create Concurrent Program and attach  1)EXECUTABLE
2)PARAMETERS
3)INCOMPATIBILITIES
5)Create Request Group and attach  Concurrent Program

6)Create Responsibility    1)Request Group
  2)Data    Group
  3)Menu
7)Create user, attach Responsibility to the user.

8)User will select the Responsibility and goto SRS(Standard Request Submission) Window
  submit the Request.


----------------------------------------------------------------------------------------------------------------

EXECUTABLE Window:
=======================
Executable      : We can enter any User Defined Name in this field
ShortName       : This is Primary key we have to enter Unique value
ApplicationName : Enter The Applications Name where the  (.rdf) file is located
Description      :It is Optional we can enter any valid Information
Execution Method :we have total 11 types of Execution methods select
   "Oracle Reports" for Report Development.
Execution File Name : Enter the (.rdf) File Name without extension Name.


If we know the Application Short Name we can find the Application name
by using following Navigation

Application =>Register=>Press F11=> Enter short Name => Press CTRL+F11

Concurrent Program Window:
=============================  
Program           : We can enter any User defined  Program Name which is meaningful
Enabled Check Box : Once the Concurrent Program is created and saved We can not delete
                    Instead of delete we can disable by unchecking this check box.
ShortName         : This is Primary key we have to enter Unique value
Application Name  : We can enter Any Valid Application Name
Description       : It is Optional we can enter any valid Information
EXECUTABLE        : Enter the "Executable Short Name" whatever we have created
Method  : System will automatically retrives the Execution MEthod.

Format            : we can select whatever the format we would like to print

Ex: HTML       (.html)Internet Explorer
pdf     (.pdf) Acrobat Reader
Postscript       (.ps)  GhostView
text   (.txt) Text Pad
PCL    HP Printer Language
XML   (.xml) XML Publisher

Save check Box  : System will save the Output and Log file details in the server
COLUMNS         : No of Columns to print per page in the Output file
ROWS            : No of Rows to print per page in the Output file

SELECT LOGFILE_NAME,
       OUTFILE_NAME
FROM   FND_CONCURRENT_REQUESTS
WHERE  REQUEST_ID =1424277

Request Group :
================
Request group is nothing but collection of Concurrent Programs and Reports we can group
All concurrent Programs.
Group :Enter Any User Defined Name (This is Case sensitive we have to maintain Capital and
           Small Letters)

Application Name :Enter Any Valid Application Name

Attach Concurrent Program by select type as Program.

Responsibility Window:
======================

Responsibility is nothing but Level of Authority which Combines Forms,Report and Application
User ID's.

Reponsibility Name : we can enter any meaningful Name
ApplicatioName     :Enter any Valid Application Name
ResponsibilityKey  :This is Primary enter the Unique Value

Data Group:
===========
data Group is nothing but Collection of Oracle Application UserID's.
it will be for Security purpose.(STANDARD will be used)
Application NAme :Enter any Valid Application Name

Request Group:
===============
Requset group is nothing but colletion of Concurrent Program
add the request group whatever we have created.
it will automatically retrieves the Application NAme.

MENU:
----
Menu : Menu is nothing but collection of Forms and Sub Menus
       select any one of Existing Menu.
 
Note : Once we create the Responsibility we can not delete instead of that we can disable
  by using Efective start Date and  End date.

Request set

Request Set:
============

Colection of Concurrent Programs which will be used to submit the Cnocurrent Programs
either sequentially or Paraalley multiple programs

It is also like Request group but in Requuest group we can submit only one program at
a time from SRS Window.
where as in Request set we can submit multiple programs at a time.

1)Select the Programs which we would like to group in the set
Ex:
Active Responsibilities
Active Users
Compile Reports
2)Open the Request Set form Select the button called Request set wizard and
  enter the concurrent Program list.
Concurrent =>Set
3)Open the Request group Form attach the Request set by selecting the Type = Set
  and attach the Request set.
4)Goto the SRS Window select the Option called Request set instead of Single Request.

Incompatibility:
================
Incompatibility is nothing but not compatible with the current concurrent program
For Ex
If we have three program A B C
If A program is running  in the server system should  not run the B and C programs
that time we wii define the Incompatibility
While createion of the A Program
Select the button called Incompatibility and enter the B and C Programs.

Run Alone Check box :if we would like to make the Program is not compatible with all
 other concurrent Program then we will check this check box.

Use in SRS Window: Be default this check box will be enabled we uncheck this we can
  find the Program at SRS window we have to submit from backend by using fnd_request
  API.

Copy To button: While customizing the Concurrent Programs(Reports,Package.....)
  we are suppose to create new concurrent Program with diff name then we will
  go for using the Copy To button.

note:  By default every concurrent Program will be executed in the CBO(Cost Based
    Optimizer) if we would like to execute in the RBO(Rule Bases Optimizer)mode then
    we will use the Session control buttion we will set the Rule Option.


:$FLEX$
:$PROFILES$

These two are Oracle apps reserved words will be used in the Value set creation

:$FLEX$: This will be used to Retrieve the Previous parameter value whatever we have
   selected.

Syntax : :$FLEX$.Previous Parameter VAlue set Name.


For Ex:  We have two Parameters


      Supplier Name      :  Table Value set
      Supplier Site Code :  Table VAlue set

based on the Suplier name we are suppose to get the Site codes in the Second parameter

SELECT VENDOR_NAME FROM PO_VENDORS  - First VAlue set

SELECT VENDOR_SITE_CODE FROM PO_vendor_sites_All   -Second Value Set
where  vendor_name = Whatever user has selected in the First PArameter
    (To get this value we will use :$FLEX$.Previous Value set name).

23SUPPLIER  - First Value set Name

23SITE
WHERE VENDOR_ID  IN(SELECT VENDOR_ID
                    FROM    PO_VENDORS
                    WHERE VENDOR_NAME = :$FLEX$.23SUPPLIER)

:$PROFILES$: This will be used to Get the Profile value in the Table Value set or
    from the front end.

To get Profile values from backend we are using Fnd_Profile.Value or Fnd_Profile.get()


Syntax : :$PROFILES$.Profile Name

SELECT SEGMENT1
FROM   PO_HEADERS_ALL

22USER  : 204
23USER  : 887
24USER  : 911

SELECT SEGMENT1
FROM PO_HEADER_ALL
WHERE ORG_ID = :$PROFILES$.ORG_ID

Ex: Display the PO's which are created by the current User

If 22user ope the LOV it has to display the PO's which are created by 22 user

SELECT SEGMENT1
FROM   PO_HEADERS_ALL
WHERE  CREATED_BY = :$PROFILES$.USER_ID