Showing posts with label Oracle Apps. Show all posts
Showing posts with label Oracle Apps. Show all posts

Thursday, May 21, 2015

How To Disable/End Date/ Delete Responsibilities of a User from Backend/API


Below is the query.
Please not Responsibilities cannot be deleted in Oracle Apps from a User. It can only be Disabled or End Dated.


BEGIN
   FOR I
      IN (SELECT fu.user_id,
                 fu.user_name,
                 furg.RESPONSIBILITY_APPLICATION_ID,
                 frv.RESPONSIBILITY_NAME,
                 frv.RESPONSIBILITY_KEY,
                 fa.application_short_name,
                 frg.security_group_key
            FROM FND_USER fu,
                 FND_USER_RESP_GROUPS furg,
                 FND_RESPONSIBILITY_VL frv,
                 fnd_application fa,
                 fnd_security_groups frg
           WHERE     fu.user_id = furg.user_id
                 AND furg.responsibility_id = frv.responsibility_id
                 AND frv.application_id = fa.application_id
                 AND frv.data_group_id = frg.security_group_id
                 AND fu.user_name = 'ABC'
                 AND frv.RESPONSIBILITY_NAME IN ('Receivables Support'))
   LOOP
      BEGIN
         DBMS_OUTPUT.put_line (
               i.user_name
            || i.application_short_name
            || i.responsibility_key
            || i.security_group_key);
         fnd_user_pkg.delresp (username         => i.user_name,
                               resp_app         => i.application_short_name,
                               resp_key         => i.responsibility_key,
                               security_group   => i.security_group_key);
         DBMS_OUTPUT.put_line (
            i.RESPONSIBILITY_NAME || ' End Dated Successfully ');
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error encountered while deleting responsibilty from the user and the error is '
         || SQLERRM);
END;
/


Wednesday, May 13, 2015

FND DEBUG / FND LOG MESSAGES

What is FND Debug Log ?


  1. It helps you pinpoint the cause of error in standard Oracle Code, by making debug messages to appear in a centralized table named FND_LOG_MESSAGES
  2. You can design and build your custom extensions in a manner that can easily be debugged. This can be done by calling Oracle delivered API’s in your custom code. 

 A program written in any technology, either form, report, PL/SQL, java concurrent program, OAF has their debug message stored in FND_LOG_MESSAGES.

How does this work? 

How to use FND debug Log to pinpoint the error?

Step 1: Set up profiles for the User / Responsibility to be used to reproduce the issue

FND: Debug Log Enabled -> YES This turns the debugging feature on
FND: Debug Log Filename -> NULL Use when you want debug messages to get stored to a file FND: Debug Log Level -> STATEMENT
FND: Debug log module -> %
If the error pertains to a specific module use the application short name.
Ex: For receivable use FND: Debug log module -> ar%

Log into the Oracle application

Step 2: Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test. If you are tracking the debug messages for a concurrent request, note down the Concurrent Request id. Otherwise, note down current max value of log sequence retrieved as follows :

SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGE 

Step 3: Run the test case, avoiding extraneous steps to avoid stacking the table.

Step 4:

a) For a concurrent process:

  SELECT LOG.module, LOG.MESSAGE_TEXT MESSAGE
    FROM fnd_log_messages LOG, fnd_log_transaction_context con
   WHERE     request_id = <Conc Req ID>
         AND con.transaction_type = 'REQUEST'
         AND con.transaction_context_id = LOG.transaction_context_id
ORDER BY LOG.log_sequence;


b) For any other debugging:

SELECT module, MESSAGE_TEXT
  FROM fnd_log_messages
 WHERE log_sequence = (SELECT MAX (LOG_SEQUENCE) FROM FND_LOG_MESSAGE);


 Obtaining the log messages via Oracle Application:

1) Query Profile: %FND%DEBUG%



2) Set the FND: Debug Log Enabled: Yes



3) System Administrator > Help > Debug Logging > View


4) set the values and run any concurrent program
5) Then navigate to the above screen and download the complete fnd log messages report by exporting as a CSV.


Friday, April 3, 2015

How to send Concurrent Program output as an Email

There are two Methods to send Concurrent Program Output as Email

Method 1:

  • The printer styles have to be configured to execute a shell script instead of sending the output to a physical printer. 
  • The shell script then emails the concurrent program output to the intended recipient. 

Method 2: (Applicable R12.1.3 onwards)

With the R12.1.3 release, a hook to the BI Publisher Delivery Manager is available on the SRS window to send the output to IPP Printers, Email, Fax and FTP destinations.


Steps for Method 1
Step 1: Define/Query Printer Type: Define a new Printer Type or Query for an existing one. Responsibility: System Administrator Navigation: Install->Printer->Types

Step 2: Define Printer Style: Click on the ‘Style’ button in the Printer Types form (as seen in the previous screenshot). Create a new style and save it.

Step 3: Define Printer Driver: Click on ‘ Driver’ button in the Printer Types form. Specify Driver method as command. Specify the following in the arguments section: $XXCUST_TOP/bin/EMAIL.sh $PROFILES$.CONC_REQUEST_ID 30


Step 4: Mapping: Map the Printer Style and Printer Driver to the Printer Type.

Step 5: Install the Printer and map the Printer Type : Create a new printer and map the Printer Type created in the previous step.

Step 6: Attach the defined printer to the concurrent program: Navigation: Concurrent > Program > Define .Specify printer in Output section

Step 7: Running the Concurrent Program: Before clicking on the Submit button in the SRS window, specify non-zero value of no. of copies by clicking on the Options button.
This will invoke Printer.

Step 8: The sample shell script (email_conc_prog_output.sh) that is executed after the completion of the above steps is attached with the article.


Steps for Method 2

This feature is available in the new button ‘Delivery Opts’ as seen in the screenshot below.
On clicking the ‘Delivery Opts’ Button, a new form will pop up which will allow users to specific email addresses of the sender, recipient and carbon copy recipient (multiple emails can be specified separated by commas), subject of the email. This can be seen in the screenshot below.

The Email tab (as seen in the screenshot above) utilizes SMTP to communicate to a mail host which in turn sends the email to the recipients. The hostname and port of the mail host is configured via the profile values:
FND: SMTP Host and FND: SMTP Port


Wednesday, September 17, 2014

FNDLOAD Commands for Migration

The Generic Loader (FNDLOAD) is a concurrent program that can transfer Oracle Application entity data between database and text file.The loader reads a configuration file to determine which entity to access.

1 - Printer Styles
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

2 - Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND"
LOOKUP_TYPE="lookup name"

3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

Examples:
Note <274528.1> - Note How To Download Single Context Using FNDLOAD For Descriptive Flexfield.
Note <256573.1> - How To Download/Upload Descriptive Flexfield With Name $SRS$.

4 - Key Flexfield Structures
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

5 - Concurrent Programs
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"

6 - Value Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

7 - Value Sets with values
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

8 - Profile Options
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"

8 - Request Groups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"

10 - Request Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"

Examples:
Note <247126.1> - How To Download Request Set Definition From One Instance And Upload On Another Instance

11 - Responsibilities
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

12 - Menus
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/af
sload.lct file_name.ldt MENU MENU_NAME="menu_name"

13 - Request Group program name
FNDLOAD apps/xxx123 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_supplier_ConcProg_REQ.ldt REQUEST_GROUP REQUEST_GROUP_NAME='All Reports' APPLICATION_SHORT_NAME='SQLAP' REQUEST_GROUP_UNIT UNIT_APP='XXXXX' UNIT_TYPE='P' UNIT_NAME='XX_AP_supplier_conv_program'

Friday, September 5, 2014

Status Codes and Phase Codes of a Concurrent Request : Oracle EBS R12

Concurrent Request Status Codes : 

SELECT LOOKUP_CODE, MEANING
  FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
     AND LANGUAGE = 'US'
     AND ENABLED_FLAG = 'Y';

LOOKUP_CODEMEANING
RNormal
INormal
ZWaiting
DCancelled
UDisabled
EError
MNo Manager
CNormal
HOn Hold
WPaused
BResuming
PScheduled
QStandby
SSuspended
XTerminated
TTerminating
AWaiting
GWarning

Concurrent Request Phase Codes: 

SELECT LOOKUP_CODE, MEANING
  FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
    AND LANGUAGE = 'US'
    AND ENABLED_FLAG = 'Y';


LOOKUP_CODEMEANINGDESCRIPTION
CCompletedRequest has finished
IInactiveRequest is unable to run
PPendingRequest is waiting to be run
RRunningRequest is running

Sunday, June 15, 2014

Code for Generic SQL Loader program in Oracle Apps

Save the below code as .prog
Make sure to create a symbolic link from your script to $FND_TOP/bin/fndcpesr
Ex: ln -s $FND_TOP/bin/fndcpesr <<File_Name>>

Saturday, May 17, 2014

Request Set of a Concurrent Program

Provide Concurrent Program name to the following query.
It lists all the Request Sets which are created with the Concurrent Program given.

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

Programs in a Request Set

Provide the Request Set name to the following query.
It lists all concurrent programs of this Request Set.

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

Query to find ORG_ID attached to a responsibility

SELECT * --fpov.profile_option_value
             FROM applsys.fnd_profile_option_values fpov
                , applsys.fnd_profile_options fpo
                , applsys.fnd_profile_options_tl fpot
                , applsys.fnd_responsibility_tl fr
            WHERE 1 = 1
              AND fpo.profile_option_name = fpot.profile_option_name
              AND fpo.profile_option_id = fpov.profile_option_id
              AND fr.responsibility_id(+) = fpov.level_value
             -- AND fr.responsibility_id = :$PROFILES$.RESP_ID
              AND fpot.profile_option_name = 'ORG_ID'
              and fpov.profile_option_value='11'
              and fr.responsibility_name like '%Payables%Super%User%';

Examples of XMLCONCAT in PLSQL

SET serveroutput ON size 300000;
DECLARE
  LV_PAYMENT_TYPE_XML XMLTYPE;
  l_string VARCHAR2(1000);
BEGIN
  SELECT XMLCONCAT(xmlelement("C_EXCHANGE_RATE",'1.2'), xmlelement("C_EXCHANGE_RATE_TYPE",'user'))
  INTO LV_PAYMENT_TYPE_XML
  FROM DUAL;
  l_string:= LV_PAYMENT_TYPE_XML.getStringVal();
  DBMS_OUTPUT.PUT_LINE(l_string) ;
  SELECT XMLCONCAT(LV_PAYMENT_TYPE_XML,xmlelement("C_EXCHANGE",'1.2'))
  INTO LV_PAYMENT_TYPE_XML
  FROM DUAL;
  l_string:= LV_PAYMENT_TYPE_XML.getStringVal();
  DBMS_OUTPUT.PUT_LINE(l_string) ;
END;
    

Oracle Workflow Important Tables

Workflow Tables


--Two classification of tables
--Definition and Setup
--if we have item key it is instance specific

SELECT * FROM wf_item_types WHERE NAME = 'XXKSRBWF';
SELECT * FROM wf_items;  -- will have item type and item key (instance table)

select * from wf_item_attributes;
SELECT * FROM wf_item_attribute_values;

SELECT * FROM wf_Activities;
SELECT * FROM wf_notifications;  --message type is same as item type in this table

SELECT * FROM wf_Resources;
select * from wf_process_Activities; --process_item_type is same as item_type

Tables used in Defining Request Sets Oracle R12

REQUEST SETS:
FND_REQUEST_SETS
FND_REQUEST_SETS_TL


REQUEST SET – STAGES:
FND_REQUEST_SET_STAGES
FND_REQUEST_SET_STAGES_TL


REQUEST SET – STAGES-STAGE REQUESTS:
FND_REQUEST_SET_PROGRAMS


REQUEST SET – STAGES-STAGE REQUESTS-REQUEST PARAMETERS:
FND_DESCR_FLEX_COL_USAGE_TL
FND_DESCR_FLEX_COLUMN_USAGES

Query to find responsibilities attached to a user in Oracle R12

SELECT * FROM FND_USER WHERE user_name LIKE 'DAVIDEVANS';

SELECT *
  FROM FND_USER_RESP_GROUPS
 WHERE user_id=1180; --user_id is the link between the FND_USER and the FIND_USER_RESP_GROUPS

SELECT *
  FROM FND_RESPONSIBILITY_VL
 WHERE responsibility_id=50621; --responsibility_id is the link between the FND_RESPONSIBILITY_VL and FND_USER_RESP_GROUPS

-- The query which displays the user name and his responsibility_name will be

select fu.user_id,
       fu.user_name,
       furg.RESPONSIBILITY_APPLICATION_ID,
       frv.RESPONSIBILITY_NAME
  from FND_USER fu, FND_USER_RESP_GROUPS furg, FND_RESPONSIBILITY_VL frv
 where fu.user_id = furg.user_id
   and furg.responsibility_id = frv.responsibility_id
   and fu.user_name like 'DAVIDEVANS'

Concurrent Program to Executable Name Drill Down Oracle R12

SELECT fcpv.concurrent_program_name,
  fcpv.user_concurrent_program_name,
  fefv.executable_name,
  fefv.execution_file_name,
  fefv.execution_file_path
FROM fnd_concurrent_programs_vl fcpv,
  fnd_executables_form_v fefv
WHERE fcpv.executable_id = fefv.executable_id
AND fcpv.user_concurrent_program_name LIKE '%File%Watcher%'