21 rules for faster SQL queries

Pro Tips listed in the below web page gives good insights and changes the view of looking at code while building a optimized applications on rdbms databases.

Follow these tried-and-true techniques to improve the speed and scalability of your relational database

Infoworld.com - 21 rules for faster SQL queries

[Issue solved] Dell Inspiron 15 5559 laptop webcam is poor

With factory settings, integrated webcam of this Dell laptop shows the video/photo quality so poor when you check by opening camera app in windows 10. And see no options to adjust it. I searched in control panel and could not find anything.

I tried doing this through skype. Follow the below steps to resolve this issue.

Go to Skype and login with your account. (Install Skype if you have not done it already).

Go to Tools -> options -> video settings. Here you see a button "webcam settings", click on it

Increase the exposure, or play with other adjustment bars there and see the output. Save the settings.

This will fix the problem, open the camera app again and see the difference.

Leave a comment if any problems.

Thanks.

Microsoft Outlook "Not implemented" error

I recently faced an error "Not implemented" when clicked on Send/Receive button. I was forced to use web version of outlook to access my emails. After searching online and trying different options suggested by community. I decided to try below steps and hoila, it worked. So thought it could be of use for some one like me.

Go to control panel -> programs and features (in windows 10) or add or remove programs in lower versions.

select office 2013 (in my case) and click on change button on the top.

Select Repair and proceed. This will repair whole installation, it will ask for reboot. Go for it.

After reboot, outlook will work. If it doesn't work, delete outlook profile and recreate again.

I noticed this issue has occurred with recent major updates downloaded and installed last night. This has done something with my outlook to fail.

Hope this solves your problem.

Amount Released calculation in the PO Headers/Summary Screen

 Steps followed to identify the calculation

 

Enable the log profiles at user level.

FND: Debug Log Enabled: yes

FND: Debug Log Level: Statement

 

Run the below query and take the maximum seq number from the debug log table. This table contains huge data and we only want the logs created after this seq number.

select max(log_sequence) from fnd_log_messages

Assume, seq number is 1001

navigate to the Purchase order screen, query for the purchase order document, then take the seq number again.

select max(log_sequence) from fnd_log_messages

Assume, seq number is 1010

Now query the log between these two numbers

select * from fnd_log_messages where log_sequence between 1001 and  1010

 

Concentrate on columns module, message_text to analyze log data.

The API that does amount_released calculation is PO_CORE_S.get_gc_amount_released

 

Send PO communication PDFs manually through Pl/Sql API

Query to find file attachment id

select * from FND_LOBS fl,FND_DOCUMENTS fd,FND_ATTACHED_DOCUMENTS fad
where fl.file_id = fd.media_id
and fd.document_id= fad.document_id
and fad.pk1_value=<po header id>
and fad.pk2_value=<rev num>


Identify what POs are failed in workflow notifications table , for whom you should send pdfs manually as a workaround, change the parameters as required.

SELECT *
from wf_notifications
where Mail_Status = 'MAIL'
and message_name ='EMAIL_PO_PDF'
and status= 'OPEN'
and sent_Date > (Sysdate - 2)
and recipient_role not like 'PO_OUTPUT%'
order by Notification_id;

Use the sample below stub to retrieve blob object and save as file in any oracle directory

declare
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;
v_file_name varchar2(240);

BEGIN

SELECT FILE_DATA,file_name INTO v_blob,v_file_name FROM fnd_lobs WHERE FILE_ID = 12345;
blob_length:=DBMS_LOB.GETLENGTH(v_blob);

out_file := UTL_FILE.FOPEN ('oracle directory name', v_file_name, 'wb', chunk_size);

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;

UTL_FILE.FCLOSE (out_file);
END;
/

Then use an utl_smtp api to send the file attachment to respective supplier email, email address you get in wf_notifications table. Query the recipient role with wf_roles to derive email address.

DECLARE
   v_From       VARCHAR2(80) := 'ab.cs@nergy.com';
   v_Recipient  VARCHAR2(80) := 'ni.ka@ergy.com';
   v_Subject    VARCHAR2(80) := 'test subject';
   v_Mail_Host  VARCHAR2(30) := 'host name';        -- take from v$instance
   v_Mail_Conn  utl_smtp.Connection;
   crlf         VARCHAR2(2)  := chr(13)||chr(10);
BEGIN
  v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

  utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

  utl_smtp.Mail(v_Mail_Conn, v_From);

  utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

  utl_smtp.Data(v_Mail_Conn,
    'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
    'From: '   || v_From || crlf ||
    'Subject: '|| v_Subject || crlf ||
    'To: '     || v_Recipient || crlf ||

    'MIME-Version: 1.0'|| crlf ||    -- Use MIME mail standard
    'Content-Type: multipart/mixed;'|| crlf ||
    ' boundary="-----SECBOUND"'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/plain;'|| crlf ||
    'Content-Transfer_Encoding: 7bit'|| crlf ||
    crlf ||
    'some message text'|| crlf ||    -- Message body
    'more message text'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/pdf;'|| crlf ||
    ' name="abc.pdf"'|| crlf ||
    'Content-Transfer_Encoding: 8bit'|| crlf ||
    'Content-Disposition: attachment;'|| crlf ||
    ' filename="/ass/ddd/fff/asdadS.pdf"'|| crlf ||
    crlf ||
    'pdf,file,attachement'|| crlf ||    -- Content of attachment
    crlf ||

    '-------SECBOUND--'            -- End MIME mail
  );

  utl_smtp.Quit(v_mail_conn);
EXCEPTION
  WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
    raise_application_error(-20000, 'Unable to send mail', TRUE);
   
END;
/


 

Excel template XML Publisher report – data exceeding more than 65k rows

There was a requirement from client to build a report using xml publisher 5.6.3 for getting Purchase order commitments. The intention is to use excel template to achieve the table formatting such as auto expansion of height and width for a cel, and adding headers and company logos are achieved in excel template using Alt-text option. During the time when report was built and tested the total row count in the outcome of the report was less than 50,000 records, we never had a thought of going with csv file because excel is feasible with presenting data in required fashion and total row count was within the limits of older version of MS excel (.xls template excel 2003). XML Publisher 5.6.3 which is a embedded version in e-business suite R12.1.3 only supports excel 2003 version templates to run reports. After an year, transaction data surged in the system only to affect the report to end in warning status when ran and output is not viewable as .xls file only allows 65k rows in a sheet.

As an interim solution to provide report to user, I had to run the script manually in toad and extract to excel (latest version). An oracle SR was raised for the issue, they only said they stopped to support for this version of bi publisher, and no patches are available for this issue. Although rebuilding the report with csv would fix the issue but user will lose the presentable way of viewing data, i then built a XSL template to overcome this product limitation. With XSL template, we will be able to show all the data with perfect formatting standards as user needs, this solution comes with an additional step to perform to get the concurrent request output file.

When the report is submitted, user needs to give the contacts of recipients who want to receive output file. After the request is completed normal it sends an notification to user with a link to open output file, they then have to right click on the link and click save as, it saves the file to their local machine which then has to be opened with Microsoft excel. This is required because when user clicks on View Output button in the SRS window, it opens an xsl gobbledegook in an internet explorer. This way it doesn’t matter what the record count is, they will get it as they need.

There are options to convert your excel template to xsl template online.

Thanks,

Nithin

XML Publisher v 5.6.3 issue with excel based template bursting

Introduction to Problem Statements:

Bursting with excel template is not allowed in xml publisher 5.6.3.

Proposed Solution:

This can be overcome by going little deep in to the standard tables.
When building the xml publisher report with excel template. You need to be cautious while writing bursting control file, particularly in the syntax where system understands from where the template needs to be picked up.
An issue was identified with bursting when used excel based template (.xls) in xml publisher (bi publisher) 5.6.3 version.

 Error shown in log is

--Exception ZIP file must have at least one entry java.util.zip.ZipException: ZIP file must have at least one entry at java.util.zip.ZipOutputStream.finish(ZipOutputStream.java:304) at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.zipOutputFiles(JCP4XDOBurstingEngine.java:534) at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:292) at oracle.apps.fnd.cp.request.Run.main(Run.java:157)

After troubleshooting, it is identified that the issue is in the below statement.

<xapi:template type="excel" location="blob://xdo_lobs.file_data/VARCHAR2:LOB_TYPE=TEMPLATE_SOURCE/VARCHAR2:APPLICATION_SHORT_NAME=ENEAP/VARCHAR2:LOB_CODE=ENEAPDRINV/VARCHAR2:LANGUAGE=en/VARCHAR2:TERRITORY=US"/>

This statement informs system to check in the XDO_TEMPLATES_TL table with template type "TEMPLATE_SOURCE". We only find rtf templates with this template type, so we get the above error, all excel based templates are stored with template type "TEMPLATE".
You can change the LOB_TYPE = TEMPLATE instead of TEMPLATE_SOURCE in the above template tag, else use the below statement which works perfectly fine.
<xapi:template type="excel" location="xdo://ENEAP.ENEAPDRINV.en.US/?getSource=true"/>

useful query for this situation

SELECT    lt.application_short_name

       || '.'

       || lt.template_code

       || '.'

       || lt.default_language

       || '.'

       || lt.default_territory

          AS burst_template_xdo_location

  FROM apps.xdo_templates_b lt

 WHERE lt.template_code = 'ENEQMSQLTYFNDASSTYPE' -- where 'ENEQMSQLTYFNDASSTYPE' is a data definition code.

References:

• Bursting with excel template is given as a limitation in Metalink note (1351502.1).

Thanks,
Nithin