Generate DDL Script (A Faster way)

Generate DDL Script (A Faster way)


SELECT dbms_metadata.get_ddl(Object_type,object_name,OWNER)
FROM all_objects
WHERE object_name LIKE 'XX%'
AND Object_type IN ( 'TABLE' ,'PACKAGE' , 'PROCEDURE' ,'FUNCTION' ,'SEQUENCE' ,'DIRECTORY' ,'TRIGGER' )
Order by Object_type

Query to Get All Concurrent Program & Executable Name, Description and More

Concurrent Program & Executable Name, Description


SELECT fcp.user_concurrent_program_name ,
  fcp.description Description,
  fef.executable_name exe_Name,
  fef.description Exe_Description,
  fef.execution_file_name execution_file_name,
  DECODE (fe.execution_method_code , 'A', 'Spawned' , 'B', 'Request Set Stage Function' , 'E', 'Perl Concurrent Program' , 'H', 'Host' , 'I', 'PL/SQL Stored Procedure' , 'J', 'Java Stored Procedure' , 'K', 'Java Concurrent Program' , 'L', 'SQL*Loader' , 'M', 'Multi Language Function' , 'P', 'Oracle Reports' , 'Q', 'SQL*Plus' , 'S', 'Immediate' , 'Other')execution_method
FROM fnd_executables_form_v fef,
  fnd_concurrent_programs_vl fcp ,
  fnd_executables fe
WHERE fcp.APPLICATION_ID=fef.APPLICATION_ID
AND fef.EXECUTABLE_ID   =fcp.EXECUTABLE_ID
AND fef.EXECUTABLE_ID   =fe.EXECUTABLE_ID
--AND fef.executable_name='XX%'---Your Executable Name
--AND fcp.user_concurrent_program_name LIKE 'XX%' -- Your Concurrent Program name

BI/ XML Publisher Excel Template

Oracle Link.
https://docs.oracle.com/cd/E21764_01/bi.1111/e13881/T527073T571887.htm

Print rdf Parameters directly in rtf template

Define User Parameter in rfd report

Open rtf file

Add following Line

<?param@begin:P_PERIOD?>

Period: <?$P_PERIOD?>

Tuning SQL using DBMS_SQLTUNE

DECLARE
  SQL_STRING  VARCHAR2(15000);
  TASK_NUMBER VARCHAR2(200);
BEGIN
  SQL_STRING  := '<<write your sql query>>';
  TASK_NUMBER := dbms_sqltune.create_tuning_task(sql_text => SQL_STRING, task_name=>'TASK_NUMBER1');
END;

EXEC dbms_sqltune.execute_tuning_task('TASK_NUMBER1');

SELECT DBMS_SQLTUNE.report_tuning_task('TASK_NUMBER1') FROM dual; 

... and follow the recommendation

Oracle BI/XML Publisher : format numbers as text so that leading zero don't disappear or excel will not convert large numbers in scientific format

To preserve leading zero in a number string, modify rtf file replace field code with following code.

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?NUMBER_FIELD?>
</fo:bidi-override>

Update Supplier Site Information using API

CREATE OR REPLACE PROCEDURE xx_update_supplier_site AS

  lr_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

  p_vendor_site_id   NUMBER;

  x_msg_data         VARCHAR2(1000);

  x_msg_count        NUMBER;

  p_count            NUMBER;

  x_return_status    VARCHAR2(10);

  lc_msg_data        VARCHAR2(4000);

-- Modify cursor as per your requirement to select Vendor and site

  CURSOR lcu_head IS

    SELECT aps.vendor_id,

           apss.vendor_site_id,

           apss.org_id           

      FROM ap_suppliers aps, ap_supplier_sites_all apss

     WHERE 1 = 1      

       AND aps.vendor_id = apss.vendor_id     

       --AND aps.vendor_id = 10284 

       ;

BEGIN

  fnd_global.apps_initialize(1110, 50747, 200);

  mo_global.init('SQLAP');

  fnd_client_info.set_org_context(83);

  FOR i IN lcu_head LOOP

   

      p_vendor_site_id := NULL;

      x_msg_data       := NULL;

      x_msg_count      := NULL;

      p_count          := NULL;

      x_return_status  := NULL;

      lc_msg_data      := NULL;

      -- Assign Vendor Site Details      

      lr_vendor_site_rec.last_update_date := SYSDATE;

      lr_vendor_site_rec.last_updated_by  := 1110;

      lr_vendor_site_rec.vendor_id        := i.vendor_id;

      lr_vendor_site_rec.org_id           := i.org_id;   

      

      --lr_vendor_site_rec.pay_group_lookup_code := 'PAY GROUP';

     

   

      ap_vendor_pub_pkg.update_vendor_site(p_api_version      => 1.0

                                           x_return_status    => x_return_status,

                                           x_msg_count        => x_msg_count,

                                           x_msg_data         => x_msg_data,

                                           p_vendor_site_rec  => lr_vendor_site_rec,

                                           p_vendor_site_id   => i.vendor_site_id

                                           );

   

      Commit;

      IF x_return_status = 'S' THEN

        dbms_output.put_line('Success ' || x_return_status);

      ELSE

        dbms_output.put_line('Failed ' || x_return_status);

        IF (x_msg_count = 1) THEN

          dbms_output.put_line('x_msg_data ' || x_msg_data);

          lc_msg_data := x_msg_data;

        ELSIF (x_msg_count > 1) THEN

          LOOP

            p_count    := p_count + 1;

            x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);

            IF (x_msg_data IS NULL) THEN

              EXIT;

            END IF;

            dbms_output.put_line('Message' || p_count || ' ---' || x_msg_data);

            lc_msg_data := lc_msg_data || ' ' || x_msg_data;

          END LOOP;

        END IF;

     

      END IF;

    

 

  END LOOP;

END;