, , , ,

Query to print the calender for the year

SELECT LPAD (MONTH, 20 – (20 – LENGTH (MONTH)) / 2) MONTH, “Sun”, “Mon”,
“Tue”, “Wed”, “Thu”, “Fri”, “Sat”
FROM (SELECT TO_CHAR (dt, ‘fmMonthfm YYYY’) MONTH,
TO_CHAR (dt + 1, ‘iw’) week,
MAX (DECODE (TO_CHAR (dt, ‘d’),
‘1’, LPAD (TO_CHAR (dt, ‘fmdd’), 2)
)
) “Sun”,
MAX (DECODE (TO_CHAR (dt, ‘d’),
‘2’, LPAD (TO_CHAR (dt, ‘fmdd’), 2)
)
) “Mon”,
MAX (DECODE (TO_CHAR (dt, ‘d’),
‘3’, LPAD (TO_CHAR (dt, ‘fmdd’), 2)
)
) “Tue”,
MAX (DECODE (TO_CHAR (dt, ‘d’),
‘4’, LPAD (TO_CHAR (dt, ‘fmdd’), 2)
)
) “Wed”,
MAX (DECODE (TO_CHAR (dt, ‘d’),
‘5’, LPAD (TO_CHAR (dt, ‘fmdd’), 2)
)
) “Thu”,
MAX (DECODE (TO_CHAR (dt, ‘d’),
‘6’, LPAD (TO_CHAR (dt, ‘fmdd’), 2)
)
) “Fri”,
MAX (DECODE (TO_CHAR (dt, ‘d’),
‘7’, LPAD (TO_CHAR (dt, ‘fmdd’), 2)
)
) “Sat”
FROM (SELECT TRUNC (SYSDATE, ‘y’) – 1 + ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, ‘y’), 12) – TRUNC (SYSDATE, ‘y’)) GROUP BY TO_CHAR (dt, ‘fmMonthfm YYYY’), TO_CHAR (dt + 1, ‘iw’))
ORDER BY TO_DATE (MONTH, ‘Month YYYY’), TO_NUMBER (week)
/by
, , , ,

Link Purchase Order and Requisition

You have a purchase order and you have a requisition, but wait how do you know how this purchase order is linked with requisition. Here is the query thats answers this. Use this query find linked Purchase order and Requisition.

This could be really a helpful one.

SELECT prh.segment1 req_number
      ,prh.authorization_status
      ,prl.line_num req_line_num
      ,prl.item_description req_item_description
      ,prl.unit_price req_unit_price
      ,prl.quantity req_quantity
      ,pd.req_header_reference_num
      ,pd.req_line_reference_num
      ,pl.line_num
      ,pl.item_description
      ,pl.quantity
      ,pl.amount
      ,ph.segment1 po_number
      ,prd.distribution_id
      ,pd.req_distribution_id
  FROM po_requisition_headers_all prh
      ,po_requisition_lines_all   prl
      ,po_req_distributions_all   prd
      ,po_distributions_all       pd
      ,po_line_locations_all      pll
      ,po_lines_all           pl
      ,po_headers_all             ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   and prh.org_id = prl.org_id
   and prl.requisition_line_id = prd.requisition_line_id
   and prl.org_id = prd.org_id
   and prd.distribution_id = pd.req_distribution_id(+)
   and prd.org_id = pd.org_id(+)
   and pd.line_location_id = pll.line_location_id(+)
   and pd.org_id = pll.org_id(+)
   and pll.po_line_id = pl.po_line_id(+)
   and pll.org_id = pl.org_id(+)
   and pl.po_header_id = ph.po_header_id(+)
   and pl.org_id = ph.org_id(+)

/by
, , , , ,

Query to List all the responsibilities attached to a User

Based on a request from one of the reader here is the query which he was looking for.

He needed query that can list all the responsibilities attached to a user.

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv(‘LANG’)

/by
, , , , , ,

Common Reporting Tools in Oracle

Oracle Reports: Fixed format reports delivered with the 11i release were built on this tool. This is the most used tool for reporting on Oracle Applications. Most of reports customizations are built with this tool. Once customized the output of the report can be in Excel (Not group By Report), word, Acrobat documents or text format.

Oracle Discoverer: is an intuitive tool for creating reports and performing on-line analysis. Discoverer uses the EUL (End User Layer), a meta data definition, which hides the complexity of the database from the end user and provides easy to use wizards for creating reports to suit individual needs. The flexibility of this tool allows the user to create cross tab reports that perform like pivot tables in Excel.

Oracle XML Publisher: is a new Oracle tool for reporting. It enables users to utilize a familiar desktop tool, like MS Word or MS Excel, to create and maintain their own report. At runtime, XML Publisher merges the custom templates with the concurrent request extracts data to generate output in RTF, PDF, HTML and EXCEL.

RXi Report: (Variable reports) – variable format reports delivered with the E-Business 11i. With this tool a user has the ability to print the same report with multiple layouts. The user can also choose which columns he requires on a particular report. This tool is most used on Oracle Financials Applications.

FSG Reports (Financial Statement Generator): is a powerful report building tool for Oracle General Ledger. Some of benefits of using this tool are that a user can generate financial reports, and schedule reports to run automatically. The only drawback of this tool is that it is only available for the general ledger responsibility and can be used to see only financial account balances.

Business Intelligence System (BI): is a set of tools to provide high level information for the managers (decision makers) to run their business such as the profitability of a particular business unit. The information this tool provides helps managers to take the right decision with the daily data that is uploaded on their systems

/by
, , , , , ,

Create Trace File for Concurrent Program

If your program is taking time to complete, then the best way to know what is causing the problem is by creating a trace file.

Navigation:
System Administrator(R) –> Concurrent –> Program –> Define

Query for the concurrent program and check enable trace button.

Now when the concurrent program is executed the trace file is created in the udump directory. The path of udump directory can be found by executing following query.
select * from v$parameter
where name like ‘%user_dump_dest%’


The trace file can be converted to a readable format by running a tkprof command over the trace file.
Syntax:

tkprof [trace_file_name] [new_file_name]

Understanding TKPROF
The TKPROF program can be used to format the contents of the trace file and convert it into a readable output file.
TKPROF can also be used to generate Explain Plan for the queries.
I will create a seperate post to discuss various options available with TKPROF.
/by