HOW TO SHRINK A TABLE IN ORACLE

--------------- How to create table to another table reference

create table test121 as select * from fnd_user;


select count(1) from test121;

select * from test121;
------------------Insert data to anoter table reference:


insert into test121  select * from fnd_user;

------------------Check the table size

select segment_name,bytes/1024/1024 as mb from user_segments where segment_name='TEST121';


delete from test121;


alter table test121 enable row movement;


 alter table test121 shrink space;


select segment_name,bytes/1024/1024 as mb from user_segments


------ take table backup

create table test_bak as select * from test1

"Internal Requisition Order Type" Lov Does Not Have All Order Types

ISSUE:  

Transaction Types have been defined  in Order Management and are not found in the Order type list of values on the Purchasing Options Internal Requisition tab.  The list of values for Order type is missing some values.

REASON:

Irregular population of the po_required_flag and agreement_required_flag in oe_transaction_types (
TRANSACTION TYPES).When the form is saved without checking off the boxes.. the associated column value was saving as NULL instead of N. The Order type LOV to fetch the transaction type does not have an nvl condition.

Per bug 5904753 IRREGULAR POPULATION OF ORDER TYPE LOV IN PURCHASING OPTIONS

SOLUTION TRICKS: 

1. states profile option OE: Set of Books should be set
before the order types are defined in Order Management.
Set the profile option OE: Set of Books and set the profile value as necessary. Save the changes. 

2. Query each Transaction Type to be used for Internal Requisitions toggle the check box value for both PO_REQUIRED and AGREEMENT_REQUIRED  ( check and save, uncheck and save) . The intent is to set the value to N for both PO_REQUIRED and AGREEMENT_REQUIRED the box is unchecked and saved as the last action for this step. 

3. Retest the issue

AUTOINVOICE DATE

For invoices without Rules:
AutoInvoice first uses the GL date in the interface table (RA_INTERFACE_LINES_ALL), if one exists.
If one does not exist then it is derived as follows:
If the Derive Date box is checked for your batch source (Menu: Setup>Transactions>Sources, query your batch source, alternate region Accounting), AutoInvoice first uses the ship date in the interface table. If the ship date does not exist, AutoInvoice uses the sales order date. If the sales order date does not exist, AutoInvoice uses the date entered in the Submit Request window when AutoInvoice was run.

If the Derive Date box is not checked for your batch source, AutoInvoice uses the date entered in the Submit Request window when AutoInvoice was run.

For Invoices with Rules:
AutoInvoice first uses the GL date in the interface table  (RA_INTERFACE_LINES_ALL), if one exists.

• If the Invoicing Rule is 'Bill In Advance', AutoInvoice uses the Rule Start Date for the GL date. 
• If the Invoicing Rule is 'Bill in Arrears' and the invoice line has an accounting rule of type 'Accounting, Fixed Duration' and a period of 'Specific Date', AutoInvoice computes an end date using the earliest  accounting rule date. 
• For all other Accounting rules, AutoInvoice computes an ending date for each invoice line, and then takes the earliest date of these lines and uses it as the GL date of the invoice. 

If your invoice does not use a fixed rule accounting duration and the rule start date is not provided in the interface table, GL date is derived as follows:
If the Derive Date box is checked in batch source options, AutoInvoice first uses the ship date. If the ship date does not exist, AutoInvoice uses the sales order date. If the sales order date does not exist, AutoInvoice uses the date entered in the Submit Request window.
If the Derive Date box is not checked, AutoInvoice uses the date entered in the Submit Request window.

How to Audit User Activity in Oracle Database

Step 1.

SQL> alter system set audit_trail=db scope=spfile;
System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

Step 2. stop Database

Step 3. start database.

Step 4 check parameter
SQL> show parameter audit_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string          /d011/db11i/db/oaproddb/10.2.0/rdbms/audit
audit_sys_operations            boolean     TRUE
audit_syslog_level                string
audit_trail                            string           DB

Step 5. Log in as sysdba.

 SQL> audit all by apps by access;
     
OR
        Give the audit permission for delete and drop objects.

        SQL> audit DELETE ANY TABLE,DROP ANY INDEX,DROP ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,ALTER ANY PROCEDURE,ALTER ANY INDEX by apps
by access whenever successful;

Step 7. Check the operation (log in as sysdba);

 SQL> SELECT username, extended_timestamp, owner,obj_name ,action_name FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate -1 ORDER BY timestamp;

 SQL> SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate -1 ORDER BY timestamp;

 SQL> SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate - 1

SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' ORDER BY timestamp;

Step 8. Check how many no of record in audit tables;
select count(1) from sys.aud$;

        

PL/SQL code sending email with single text attachment.

Step:-1
create a sequence,

create sequence file_id start with 1 increment by 1 nomaxvalue;



create a clob table,

create table file_test
(file_id number,
txt clob);

Step:-2

Create a procedure for populating the clob table with text file that needs to sent,

create or replace PROCEDURE load_file_swarup ( pfname VARCHAR2) IS
l_size number;
l_file_ptr bfile;
l_blob clob;
begin
l_file_ptr:= bfilename('KROSS_TEST', pfname);
dbms_lob.fileopen(l_file_ptr);
l_size:= dbms_lob.getlength(l_file_ptr);
insert into file_test ( file_id, txt )
values ( file_id.nextval, empty_clob() )
returning txt into l_blob;
dbms_lob.loadfromfile(l_blob, l_file_ptr, l_size);
commit;
dbms_lob.close(l_file_ptr);
end; 

Step:-3,

execute the procedure to populate the clob table,

begin
load_file_swarup('myfile.txt');
end;

Step:-4,

Create the procedure for sending the emaill with attachment,

CREATE OR REPLACE PROCEDURE send_mail (p_to          IN VARCHAR2,
                                       p_from        IN VARCHAR2,
                                       p_subject     IN VARCHAR2,
                                       p_text_msg    IN VARCHAR2 DEFAULT NULL,
                                       p_attach_name IN VARCHAR2 DEFAULT NULL,
                                       p_attach_mime IN VARCHAR2 DEFAULT NULL,
                                       p_attach_clob IN CLOB DEFAULT NULL,
                                       p_smtp_host   IN VARCHAR2,
                                       p_smtp_port   IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
  l_step        PLS_INTEGER  := 24573;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);
 
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
 
  IF p_text_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_text_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  IF p_attach_name IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) LOOP
      UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));
    END LOOP;

    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;
 
  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;

Step:- 5,

Execute the above mentioned procedure to send the mail finaly with one text attachment,

DECLARE
  l_clob CLOB ;
BEGIN
select a.TXT  into l_clob
from file_test a;
  send_mail(p_to          => 'swarup.ghosh@xyz.com',
            p_from        => 'faheiz.alam@xyz.com',
            p_subject     => 'Test Message',
            p_text_msg    => 'This is a test message.',
            p_attach_name => 'myfile.txt',
            p_attach_mime => 'text/plain',
            p_attach_clob => l_clob,
            p_smtp_host   => 'webmail.conacent.com');
END;

Code for excute a data loader from oracle forms.

In "When Button Pressed " Trigger of a button in the form, write the below mentioned code,

DECLARE

   usid            VARCHAR2 (10)   := 'apps';

   pwd             VARCHAR2 (10)   := 'wipl10';

   db              VARCHAR2 (10)   := 'dev';

   msqlldr         VARCHAR2 (300);

   ctrl_filename   VARCHAR2 (300)

               := '/c03/DEV/apps/apps_st/appl/payapps/12.0.0/bin/my_test.ctl';

   data_filename   VARCHAR2 (300)

               := '/c03/DEV/apps/apps_st/appl/payapps/12.0.0/bin/my_data.csv';

   fname           VARCHAR2 (1000);

BEGIN

   msqlldr :=

         'sqlldr'

      || ' userid='

      || usid

      || '/'

      || pwd

      || '@'

      || db

      || ' control='

      || ctrl_filename

      || ' data='

      || data_filename;

   HOST (msqlldr, no_screen);

END;

Usid :- is the user id of the database.

pwd:- is the password of the database

db:- the database sid.

ctrl_filename:- is the control file location along with the control file name in the server

data_filename:- is the control file location along with the control file name in the server

Code for Calling a XML report from Oracle Forms.

DECLARE
   lc_boolean        BOOLEAN;
   ln_request_id     NUMBER;
   lc_boolean1       BOOLEAN;
   lc_boolean2       BOOLEAN;
BEGIN

      -- Initialize Apps 
      fnd_global.apps_initialize (>USER_ID<
                                 ,>RESP_ID<
                                 ,>RESP_APPL_ID<
                                 );
  
  lc_boolean2 :=
               fnd_request.add_layout (
                            template_appl_name   => 'Template Application',
                            template_code        => 'Template Code',
                            template_language    => 'en', --Use language from template definition
                            template_territory   => 'US', --Use territory from template definition
                            output_format        => 'PDF/EXCEL' --Use output format from template definition
                                    );
   ln_request_id :=
      fnd_request.submit_request ('FND',                -- application
                                  'COCN_PGM_SHORT_NAME',-- program short name
                                  '',                  
                                  '',                  
                                  FALSE,               
                                  CHR (0)              
                                 );
   COMMIT;

   IF ln_request_id = 0
   THEN
      dbms.output.put_line ('Concurrent request failed to submit');
   END IF;
END;

JSP Page Personalization in ORACLE EBS

REQUIREMENT: How to show Item Number at PO Requisition Approval Notification

Set the following profile options at user level

1. Disable Self-Service Personal = No

2. Personalize Self-Service Defn = Yes

3. FND: Personalization Region Link Enabled = Yes

Click on Personalize "Requisition Lines"

How to find the text attachemnts attached to a Requisition Header

     /* Step:-1 -> Find the requisition */
     
      select b.REQUISITION_HEADER_ID from po_requisition_headers_all b where   trunc(b.CREATION_DATE)='25-MAR-2013'
     
      /*Step:- 2 - >   Find The document id (Here PK1_VALUE is the requisition_header_id) */
     
      select a.PK1_VALUE ,a.DOCUMENT_ID ,a.* from apps.FND_ATTACHED_DOCUMENTS a where a.PK1_VALUE =95355(requisition_header_id)
     
      /* Step :- 3 ->  Find the media_id */
     
      select a.MEDIA_ID ,a.* from fnd_documents a where a.DOCUMENT_ID=160936
     
      /*Step:- 4 -> Find the  Input Text in the requisition */
     
      select a.SHORT_TEXT,a.MEDIA_ID from fnd_documents_short_text a where a.MEDIA_ID=2;

How to attach multiple templates (.rtf) to a single Report

1.      Create your multiple RTFs.

2.     Now go to XML Publisher Administrator and create a Data Definition.

3. We need to create multiple templates against the same Data Definition.

       Similarly, you need to create multiple template against the same Data Definition ( e.g.           KCT000RCONA  here).

4.     Now run the Report from SRS window.

Submit the request and you will get output according to your selected template.