Search This Blog

Friday, May 15, 2009

Discoverer Query to Display Document Name, Folder, Business Unit etc.

Following query can be used to display Document Name, Document Key, Folder Name, Folder Key, Documents Sheets, Business Unit, Document Created By in discoverer.
Please note that the query is in crude format and not tested, any suggestion to improve this is appreciated.

 SELECT DISTINCT b.doc_developer_key Document_developer_key
, b.doc_name document_name
, a.qs_doc_details worksheet_name
, c.obj_name folder_name
, c.obj_developer_key folder_developer_key
, d.ba_name Business_unit
, fu.user_name created_by
FROM eul4_us.eul4_qpp_stats a
, eul4_us.eul4_documents b
, eul4_us.eul4_objs c
, eul4_us.eul4_bas d
, eul4_us.eul4_eul_users e
, eul4_us.eul4_ba_obj_links f
, fnd_user fu
WHERE a.qs_doc_name = b.doc_name
AND a.qs_doc_owner = e.eu_username
AND b.doc_eu_id = e.eu_id
AND c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
AND c.obj_id = f.bol_obj_id
AND d.ba_id = f.bol_ba_id
AND TO_CHAR (fu.user_id) = SUBSTR (b.doc_updated_by, 2)

Friday, May 8, 2009

Using 3 of 9 Barcodes in XML Publisher

I am using Code 39(3 of 9) Barcode font for my report. Every thing seems to be fine and barcode seems to be displayed correctly, but not recognized by the barcode reader/gun?
          For code 39 barcode fonts asterisk(*) is used as a start and end delimeter. So any text which is displayed as barcode should have * at the start and end.

E.g. 12345 should be displayed as *12345*

Thursday, May 7, 2009

How to query/find discoverer reports in Oracle using APPS Schema

The table to store discoverer is stored in the EUL Schema. For e.g. if the name of EUL is EUL4_US then a schema with that name will be created in oracle database. Run following query to know the name of table

 SELECT owner,table_name
FROM all_tables
WHERE owner = 'EUL4_US' AND table_name LIKE '%DOCUMENT%'


The above query in this case will list table_name EUL4_DOCUMENTS

All the discoverer report names are stored in this table, so
SELECT * FROM EUL4_US.EUL4_DOCUMENTS
should display name, owner etc of discoverer reports

Sunday, May 3, 2009

Preserve Timestamp, mode, owner while using copy command in Unix

In Unix, Sometimes when we need to take a backup of the file we generally add timestamp along with the file and create a backup, however the new file created has the creation date as system date.
This can be avoided using preserve option in unix.
For e.g. I will copy ARXSGPO.rdf from $AR_TOP/reports/US directory. The file signature looks like

-rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO.rdf


Now the following files are created using preserve and no preserve option
 cp ARXSGPO.rdf ARXSGPO_np.rdf   # without preserve option
cp -p ARXSGPO.rdf ARXSGPO_p.rdf # with preserve option

This is how the signature of file looks like
 -rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO.rdf
-rw-r--r-- 1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO_p.rdf
-rw-r--r-- 1 applmgr oracle 647168 May 3 23:43 ARXSGPO_np.rdf

It can be noted that ARXSGPO_np.rdf has timestamp as May 3, whereas file ARXSGPO_p.rdf created using preserve mode has timestamp as Apr 21. So using preserve option we can save the original information of the file from where the file is copied.

Copyright (c) All rights reserved. Presented by Suresh Vaishya