, , , ,

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
, , , ,

New Terminologies in R12

Some of the new terminologies used in Oracle Applications Release 12 are:

11i Vs. 12

Sets of Books = Ledgers
Global Accounting Engine = Subledger Accounting
AX Posting Sets of Books = Secondary Ledgers
Global Intercompany System = Advanced Global Intercompany System
GL Intercompany Accounts = Intracompany Balancing
Translated Currency = Balance level RCs
Multiple Reporting Currencies = Reporting Currencies
MRC Primary Set of Books = Primary Ledger
MRC Reporting Set of Books = Reporting Currency
Thin MRC Reporting Book = Journal level RC
Full MRC Reporting Book = Subledger level RC
Translated Balance = Balance Level RC

Hope it will be helpful. ………..

/by
, , , ,

Difference Between Lexical and Bind Variable

Bind references are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY, and START WITH clauses of queries. Binds may not be referenced in the FROM clause.
An example is:
SELECT Col1,Col2
FROM XX_table
WHERE Col1 = :P_col1

Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY , ORDER BY , HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value.
An example is:
SELECT Col1,Col2
FROM &ATABLE

/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