Query to get the list of all profiles defined at different levels

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Query to get the List of all profiles defined at different levels
-- Release Date: 28.July.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select fpo.profile_option_name Profile_name
, fpot.user_profile_option_name User_Profile_Name
, fpot.description Description
, fpo.start_date_active Start_date
, fpo.end_date_active End_Date
, fpo.creation_date Creation_Date
, fu.user_name created_by
, fpov.LAST_UPDATE_DATE Updated_Date
, (select user_name from fnd_user fu1
where fpov.LAST_UPDATEd_by = fu1.user_id) Updated_by
, decode(fpov.level_id ,10001, 'Site',10002, 'Application',10003, 'Responsibility',10004, 'User') Profile_Level
, fpov.profile_option_value Profile_option_value
from FND_PROFILE_OPTIONS_vl fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
where fpot.profile_option_name = fpo.profile_option_name
and fpo.application_id = fpov.application_id
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
order by fpov.level_id;

Query to get the account details for a particular payable bank

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Query to get the account details for a particular payable bank
-- Release Date: 28.July.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select abb.bank_name,
aba.BANK_ACCOUNT_NAME,
aba.BANK_ACCOUNT_NUM,
aba.DESCRIPTION,
aba.CURRENCY_CODE curr_code
from AP_BANK_branches abb,
AP_BANK_ACCOUNTS_ALL aba
where abb.BANK_BRANCH_ID = aba.BANK_BRANCH_ID
and abb.bank_name = :Bank_name

Oracle Discoverer Handbook

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Discoverer Administrator / Desktop Handbook.
-- Release Date: 03.June.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

Oracle Discoverer Handbook gives a complete hands on experience in Oracle Discoverer Administrator and Discoverer Desktop.

Get the book download from this link Oracle Discoverer Handbook.pdf covers all areas in discoverer desktop and discoverer administrator.

Write to us to get more updates on oracle technologies.


-- ----------------------------------------------------------------------------------

Inventory Stock details of an item

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Inventory Stock details of an item by Subinventory / Oranizations
-- Date      : 01.June.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

SELECT
msi.SEGMENT1 Item_name,
ood.ORGANIZATION_NAME org_name,
msinv.SECONDARY_INVENTORY_NAME subinv_name,
moq.TRANSACTION_QUANTITY
from
mtl_system_items_b msi,
mtl_onhand_quantities moq,
mtl_secondary_inventories msinv,
org_organization_definitions ood
where msi.INVENTORY_ITEM_ID = moq.INVENTORY_ITEM_ID
and msinv.ORGANIZATION_ID = moq.ORGANIZATION_ID
and msi.ORGANIZATION_ID = moq.ORGANIZATION_ID
and msinv.SECONDARY_INVENTORY_NAME = moq.SUBINVENTORY_CODE
and ood.ORGANIZATION_ID = moq.ORGANIZATION_ID
and msi.segment1 = :item_name;

-- --------------------------------------------------------

AR Invoice Interface rejected transactions

-- ---------------------------------------------------------------------------
-- Purpose : AR Invoice Interface rejected Records for Analysis
-- Date      : 31.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------


SELECT ril.INTERFACE_LINE_ID,
(select customer_name from ra_customers
where customer_id = ril.ORIG_SYSTEM_BILL_CUSTOMER_ID
or customer_number = ril.orig_system_bill_customer_ref) Customer_Name,
ril.trx_number,
ril.trx_date,
ril.gl_date,
ril.INTERFACE_LINE_CONTEXT,
ril.INTERFACE_LINE_ATTRIBUTE1,
ril.BATCH_SOURCE_NAME,
ril.LINE_TYPE,
ril.INTERFACE_STATUS Status ,
rie.INVALID_VALUE,
rie.MESSAGE_TEXT
from ra_interface_lines_all ril,
ra_interface_errors_all rie
where rie.INTERFACE_LINE_ID = ril.INTERFACE_LINE_ID
and ril.BATCH_SOURCE_NAME = :batch_source_name;

-- ---------------------------------------------------------------------------

PID and SID details of a Concurrent Request

-- ----------------------------------------------------------------------------------------------------------------
-- Purpose : Getting the Process ID, session and terminal details for the Concurrent request.
-- Date      : 30.May.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------------------

SELECT req.request_id ,
prog.USER_CONCURRENT_PROGRAM_NAME Conc_prg_Name,
fu.USER_NAME requested_by,
fr.RESPONSIBILITY_KEY responsibility,
ses.LOGON_TIME,
ses.sid,
ses.serial#,
proc.SPID,
proc.pid,
ses.USERNAME "User",
ses.STATUS,
ses.sql_hash_value sql_hash,
proc.USERNAME osuser,
ses.MACHINE,
nvl(ses.TERMINAL,'Not Available') Terminal,
ses.MODULE,
ses.ACTION,
ses.MODULE_HASH,
nvl(ses.PROGRAM,'Not Available') Program
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_responsibility fr,fnd_user fu,
fnd_executables execname
where
1=1
and req.request_id = :request_id
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and req.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID
and fu.USER_ID = req.REQUESTED_BY
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

-- --------------------------------------------------------------------------------

Purchase order and Receipt Information

-- ---------------------------------------------------------------------------------
-- Purpose : Get the details of the PO, Receipt and line status information
-- Date      : 29.May.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------

SELECT ph.segment1 po_num,
pl.LINE_NUM,
msi.segment1 item_name,
pv.VENDOR_NAME,
pll.QUANTITY ord_qty,
pll.QUANTITY_RECEIVED qty_rcvd,
pll.QUANTITY_BILLED Invcd_qty,
decode((select count(distinct rsh.RECEIPT_NUM) from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID),1,
(select distinct rsh.RECEIPT_NUM from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID)
,'Received in More than one Receipt') Receipt_num,
pll.PO_LINE_ID,
ph.CLOSED_CODE order_status,
pl.CLOSED_CODE line_status
from po_headers_all ph
,po_lines_all pl
,po_line_locations_all pll
,po_vendors pv
,mtl_system_items_b msi
where 1=1
and ph.type_lookup_code = 'STANDARD'
and pl.PO_HEADER_ID = ph.PO_HEADER_ID
and pll.PO_HEADER_ID = ph.PO_HEADER_ID
and pl.PO_LINE_ID = pll.PO_LINE_ID
and msi.ORGANIZATION_ID = pll.SHIP_TO_ORGANIZATION_ID
and ph.VENDOR_ID = pv.VENDOR_ID
and msi.INVENTORY_ITEM_ID = pl.ITEM_ID
and ph.SEGMENT1 = :po_num;

-- ---------------------------------------------------------------------------

AP Invoice Interface rejected Records

-- ---------------------------------------------------------------------------
-- Purpose : AP Invoice Interface rejected Records for Analysis
-- Date      : 28.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------


SELECT pv.vendor_name,
aii.INVOICE_ID,
aii.SOURCE,
aii.invoice_num,
(select decode(count(*),0,'Invoice N/A','Invoice Available') from ap_invoices_all
where org_id = aii.ORG_ID
and invoice_num = aii.INVOICE_NUM) Inv_in_Base,
aii.CREATION_DATE,
aii.invoice_date,
aii.GL_DATE,
ail.LINE_NUMBER,
aii.REQUEST_ID,
aii.invoice_currency_code,
nvl(airh.REJECT_LOOKUP_CODE,airl.REJECT_LOOKUP_CODE) Rejection_Reason,
(select decode(count(INACTIVE_DATE),0,'Site is Active','Site is Inactive') from po_vendor_sites_All
where vendor_site_id = aii.VENDOR_SITE_ID) Site_STatus
from ap_invoice_lines_interface ail,
AP_INTERFACE_REJECTIONS airh,
AP_INTERFACE_REJECTIONS airl,
ap_invoices_interface aii,
po_vendors pv
where aii.INVOICE_ID = ail.INVOICE_ID
and airh.PARENT_ID = aii.INVOICE_ID
and ail.INVOICE_LINE_ID = airl.PARENT_ID
and aii.VENDOR_ID = pv.VENDOR_ID
order by 2,6;

-- ---------------------------------------------------------------------------