Apps- Find Concurrent program and Request set Status

Find Concurrent program and Request set Status


SELECT   REQUEST_ID,
         DECODE(PHASE_CODE,'C','Completed'
                          ,'I','Inactive'
                          ,'P','Pending'
                          ,'R','Running'
                          ,PHASE_CODE) PHASE_CODE,
        DECODE(STATUS_CODE,'D','Cancelled'
                          ,'U','Disabled'
                          ,'E','Error'
                          ,'M','No Manager'
                          ,'R','Normal'
                          ,'I','Normal'
                          ,'C','Normal'
                          ,'H','On Hold'
                          ,'W','Paused'
                          ,'B','Resuming'
                          ,'P','Scheduled'
                          ,'Q','Standby'
                          ,'S','Suspended'
                          ,'X','Terminated'
                          ,'T','Terminating'
                          ,'A','Waiting'
                          ,'Z','Waiting'
                          ,'G','Warning'
                          ,STATUS_CODE) STATUS_CODE,
               RESPONSIBILITY_ID,
               ACTUAL_START_DATE,
              ACTUAL_COMPLETION_DATE,
              COMPLETION_TEXT,
              ARGUMENT_TEXT,
              PROGRAM_SHORT_NAME,
            REQUESTOR,
            PARENT_REQUEST_ID,
            RESPONSIBILITY_ID,
            COMPLETION_TEXT,
            PROGRAM,
            DESCRIPTION,
            USER_CONCURRENT_PROGRAM_NAME
  FROM APPS.FND_CONC_REQ_SUMMARY_V
 WHERE 1=1
--AND PROGRAM_SHORT_NAME = <PROGRAM SHORT NAME>
--AND REQUEST_ID=<REQUEST ID>
-- AND PARENT_REQUEST_ID= <REQUESTSET ID>
 ORDER BY REQUEST_ID DESC;

Apps-Find Responsibility name using concurrent program

Find Responsibility name using concurrent program

 SELECT  frt.responsibility_name

  ,frg.request_group_name

  ,frgu.request_unit_type

  ,frgu.request_unit_id

  ,fcpt.user_concurrent_program_name

   FROM  fnd_Responsibility fr

  ,fnd_responsibility_tl frt

  ,fnd_request_groups frg

  ,fnd_request_group_units frgu

  ,fnd_concurrent_programs_tl fcpt

  WHERE  frt.responsibility_id = fr.responsibility_id

    AND  frg.request_group_id = fr.request_group_id

    AND  frgu.request_group_id = frg.request_group_id

    AND  fcpt.concurrent_program_id = frgu.request_unit_id

    AND  frt.LANGUAGE = USERENV('LANG')

    AND  fcpt.LANGUAGE = USERENV('LANG')

    AND  fcpt.user_concurrent_program_name = :conc_prg_name

    ORDER BY 1,2,3,4;

Apps-Query to find Application Name and Short Name

Query to find Application Name and Short Name

SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
  FROM fnd_application     fa,
       fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
   AND fat.language      = USERENV('LANG')
   -- AND fat.application_name = 'Payables'  -- <change it>
 ORDER BY fat.application_name;

Apps-Submit a Concurrent Request from Backend

Submit a Concurrent Request from Backend


DECLARE
l_responsibility_id NUMBER;
l_application_id     NUMBER;
l_user_id           NUMBER;
l_request_id            NUMBER;
BEGIN
  --
  SELECT DISTINCT fr.responsibility_id,
    frx.application_id
     INTO l_responsibility_id,
    l_application_id
     FROM apps.fnd_responsibility frx,
    apps.fnd_responsibility_tl fr
    WHERE fr.responsibility_id = frx.responsibility_id
  AND LOWER (fr.responsibility_name) LIKE LOWER('Responsibility name');

   SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'USERNAME';

  --To set environment context.

  apps.fnd_global.apps_initialize (l_user_id,l_responsibility_id,l_application_id);

  --Submitting Concurrent Request

  l_request_id := fnd_request.submit_request (
                            application   => 'CUSTOM',
                            program       => 'Concurrent Program Name',
                            description   => 'Description',
                            start_time    => sysdate,
                            sub_request   => FALSE,
   argument1     => 'Argument1'
  );

  COMMIT;

  IF l_request_id = 0
  THEN
     dbms_output.put_line ('Concurrent request failed to submit');
  ELSE
     dbms_output.put_line('Successfully Submitted the Concurrent Request');
  END IF;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
END;

Apps- Copy Responsibilities of one user to another user

Copy Responsibilities of one user to another user 



DECLARE
  resp_count NUMBER := 0;

  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg
      WHERE 1                               = 1
    AND fu.user_name                        = 'MYUSER'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
    AND furga.end_date IS NULL;


BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
   
      fnd_user_pkg.addresp
                 (username            => 'HCM',
                  resp_app            => user_resp_details_rec.application_short_name,
                  resp_key            => user_resp_details_rec.responsibility_key,
                  security_group      => user_resp_details_rec.security_group_key,
                  description         => NULL,
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
   
      resp_count := resp_count + 1;
   
    EXCEPTION
    WHEN OTHERS THEN
   
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
   
    END;
  END LOOP;

  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );

  COMMIT;
END;

Apps- Query to find Active & In Active User's List in EBS

Query to find  Active & In Active User's List in EBS


SELECT 'Active', valid.*
  FROM fnd_user valid
 WHERE valid.user_id IN (SELECT user_id
                           FROM fnd_user
                          WHERE NVL (end_date, SYSDATE) >= SYSDATE)
UNION ALL
SELECT 'In Active', invalid.*
  FROM fnd_user invalid
 WHERE invalid.user_id IN (SELECT user_id
                             FROM fnd_user
                            WHERE NVL (end_date, SYSDATE) < SYSDATE)
ORDER BY 2;

Apps- Query to fond RDF Path

Query to fond RDF Path



SELECT
APPLICATION_NAME
,'$'||BASEPATH||'/'||'reports/US'  Reports_Path
,EXECUTION_FILE_NAME
FROM APPS.FND_EXECUTABLES_VL A,
APPS.FND_APPLICATION_VL B
WHERE EXECUTION_METHOD_CODE='P'
AND A.APPLICATION_ID=B.APPLICATION_ID
AND EXECUTION_FILE_NAME ='FILENAME' --Pass ur RDF file name

Apps- Query to create table data to csv

Query to create table data to csv

1) create or replace directory MYCSV as '/home/oracle/mycsv';
Note: /home/oracle/mycsv has to be physical location on disk.
2) grant read, write on directory MYCSV to scott;

CREATE OR REPLACE DIRECTORY MYCSV AS 'C:\Users\PG\Desktop';

grant read, write on directory MYCSV to scott;


DECLARE
    F UTL_FILE.FILE_TYPE;
    CURSOR C1 IS SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO;
    C1_R C1%ROWTYPE;
BEGIN
    F := UTL_FILE.FOPEN('MYCSV','EMP_DEPT.CSV','w',32767);
    FOR C1_R IN C1
    LOOP
        UTL_FILE.PUT(F,C1_R.EMPNO);
        UTL_FILE.PUT(F,','||C1_R.ENAME);
        UTL_FILE.PUT(F,','||C1_R.SAL);
        UTL_FILE.PUT(F,','||C1_R.DEPTNO);
        UTL_FILE.PUT(F,','||C1_R.DNAME);
        UTL_FILE.NEW_LINE(F);
    END LOOP;
    UTL_FILE.FCLOSE(F);
END;

Apps- Deleting Request Set using Query

Deleting Request Set using Query

SET SERVEROUTPUT ON


BEGIN
FND_SET.DELETE_SET(request_set => 'FNDRSSUB1238', application => 'CUSTOM');
DBMS_OUTPUT.PUT_LINE('Request set Successfully Deleted ');
COMMIT;
END;

Apps- Query to add sysadmin to user

Query to add sysadmin to user



BEGIN
fnd_user_pkg.addresp ('USERNAME','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 100);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ' Responsibility is not added due to ' || SQLCODE || SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;

Apps- Zipping file using Oracle Stored Procedure

Zipping file using Oracle Stored Procedure

create or replace PROCEDURE ZIP_PROC (myfilename IN VARCHAR2,L_PERSON_ID IN NUMBER) IS
  v_blob         BLOB;
  blob_length    INTEGER;
  out_file utl_file.file_type;
  v_buffer       RAW(32767);
  chunk_size     BINARY_INTEGER := 32767;
  blob_position  INTEGER := 1;

BEGIN

  -- Retrieve the BLOB for reading
  Select Image Into V_Blob From Per_Images
  Where Parent_Id =L_PERSON_ID;

  -- Retrieve the SIZE of the BLOB
  blob_length:=DBMS_LOB.GETLENGTH(v_blob);

  -- Open a handle to the location where you are going to write the BLOB to file
  -- NOTE: The 'wb' parameter means "write in byte mode" and is only availabe
  --       in the UTL_FILE package with Oracle 10g or later
  out_file := UTL_FILE.FOPEN ('INTF_DIR1', myfilename, 'wb', chunk_size);

  -- Write the BLOB to file in chunks
  WHILE blob_position <= blob_length LOOP
    IF blob_position + chunk_size - 1 > blob_length THEN
      chunk_size := blob_length - blob_position + 1;
    END IF;
    DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer);
    UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
    blob_position := blob_position + chunk_size;
  END LOOP;

  -- Close the file handle
  UTL_FILE.FCLOSE (out_file);
END;

Apps- Excel File as a Output using PL/SQL Procedure

Excel File as a Output using PLSQL Procedure

1)Package(multi_excel_pkg)
2)Executable (multi_excel_pkg.excel_proc)
3)Concurrent Program

Package:

create or replace package body multi_excel_pkg
is
procedure excel_proc(x_errbuff out varchar2,
                           x_retcode out number
                           )
                           is
cursor head_cur is
select   col1
,col2
,col3
,col4
from HEADER ;

cursor line_cur is
select   col1
,col2
,col3
from LINES ;

begin
fnd_file.put_line (fnd_file.output,'<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Administrator</Author>
  <LastAuthor>Administrator</LastAuthor>
  <Created>2017-03-20T08:34:02Z</Created>
  <Version>15.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>7650</WindowHeight>
  <WindowWidth>20400</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>0</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="HEAD">
  <Table ss:ExpandedColumnCount="10000" ss:ExpandedRowCount="10000" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Width="59.25"/>
   <Column ss:Width="66.75"/>
   <Column ss:Width="64.5"/>
   <Column ss:Width="33"/>
   <Column ss:Width="27.75"/>
   <Row>
    <Cell><Data ss:Type="String">col1</Data></Cell>
    <Cell><Data ss:Type="String">col2</Data></Cell>
    <Cell><Data ss:Type="String">col3</Data></Cell>
    <Cell><Data ss:Type="String">col4</Data></Cell>
    <Cell><Data ss:Type="String">col5</Data></Cell>
   </Row>');
   for i in head_cur
   loop
  fnd_file.put_line(fnd_file.output,'<Row>
    <Cell><Data ss:Type="String">'||i.col1||'</Data></Cell>
    <Cell><Data ss:Type="Number">'||i.col2||'</Data></Cell>
    <Cell><Data ss:Type="Number">'||i.col3||'</Data></Cell>
    <Cell><Data ss:Type="Number">'||i.col4||'</Data></Cell>
    <Cell><Data ss:Type="String">'||i.col5||'</Data></Cell>
   </Row>');
   end loop;
  fnd_file.put_line (fnd_file.output,'</Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="LINE">
  <Table ss:ExpandedColumnCount="10000" ss:ExpandedRowCount="10000" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Width="52.5"/>
   <Column ss:Width="65.25"/>
   <Column ss:Width="27"/>
   <Column ss:Width="45.75"/>
   <Row>
    <Cell><Data ss:Type="String">col1</Data></Cell>
<Cell><Data ss:Type="String">col2</Data></Cell>
    <Cell><Data ss:Type="String">col3</Data></Cell>
    <Cell><Data ss:Type="String">col4</Data></Cell>
   </Row>');
    for j in line_cur
   loop
   fnd_file.put_line (fnd_file.output,'<Row>
    <Cell><Data ss:Type="Number">'||j.col1||'</Data></Cell>
    <Cell><Data ss:Type="Number">'||j.col2||'</Data></Cell>
    <Cell><Data ss:Type="String">'||j.col3||'</Data></Cell>
    <Cell><Data ss:Type="Number">'||j.col4||'</Data></Cell>
   </Row>');
   end loop;
  fnd_file.put_line (fnd_file.output,'</Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>');
end;
end multi_excel_pkg;

Apps- Query to find Bin Directory

Query to find Bin Directory

SELECT variable_name, VALUE FROM apps.fnd_env_context;