ERPstuff - Oracle EBS Community

Hi,

using fnd_global.userid in query gave me -1.

I need to know how to print the user id of the application user who requested of the customized report .

what do i need to add to the report?

my query is as follows and i have no customized PLSQL programs in the report:

SELECT
i.SEGMENT1 AS "ITEM ID"
,i.DESCRIPTION AS "ITEM DESCRIPTION"
,o.ORGANIZATION_CODE AS "ORGANIZATION"
,m.SUBINVENTORY_CODE AS "SUBINVENTORY"
,round(c.ITEM_COST,2) As ITEM_COST
,s.CURRENCY_CODE
,SUM(m.TRANSACTION_QUANTITY) AS "QUANTITY"
,round(((SUM(m.TRANSACTION_QUANTITY))*c.ITEM_COST),2) AS "Total COST"
FROM
mtl_material_transactions m
,MTL_SYSTEM_ITEMS_B i
,MTL_ITEM_LOCATIONS l
,org_organization_definitions o
,CST_ITEM_COST_TYPE_V c
,GL_SETS_OF_BOOKS s
WHERE
m.ORGANIZATION_ID=i.ORGANIZATION_ID
and s.SET_OF_BOOKS_ID=o.SET_OF_BOOKS_ID
AND m.INVENTORY_ITEM_ID=i.INVENTORY_ITEM_ID
AND o.ORGANIZATION_ID=m.ORGANIZATION_ID
AND m.ORGANIZATION_ID=l.ORGANIZATION_ID(+)
AND m.TRANSFER_LOCATOR_ID=l.INVENTORY_LOCATION_ID(+)
AND c.INVENTORY_ITEM_ID= i.INVENTORY_ITEM_ID
AND c.ITEM_COST > 0
and m.SUBINVENTORY_CODE in ('MWarehouse','MWAREHOUSE')
AND o.ORGANIZATION_ID = NVL(:P_ORG, o.ORGANIZATION_ID)
AND m.SUBINVENTORY_CODE = NVL(:P_SUBINV,m.SUBINVENTORY_CODE)
AND i.SEGMENT1=NVL(:P_ITEM,i.SEGMENT1)
AND TRUNC(m.TRANSACTION_DATE) BETWEEN NVL(:P_DATE_FROM, TRUNC(m.TRANSACTION_DATE ))
AND NVL(:P_DATE_TO, TRUNC(m.TRANSACTION_DATE ))
GROUP BY
i.SEGMENT1
,i.DESCRIPTION
,o.ORGANIZATION_CODE
,m.SUBINVENTORY_CODE
,c.ITEM_COST
,s.CURRENCY_CODE
having SUM(m.TRANSACTION_QUANTITY)>0
ORDER BY m.SUBINVENTORY_CODE;


I will appreciate any help

Hi,
You can pass the USERID or USERNAME from the application by a parameter.
Add a parameter , set default type as profile and default value as USERNAME or whatever profile value you need to pass.

Regards,
Ali Pirzada

thank you for your reply.

can you please provide me with more details. I will also appreciate an example

Hi Aimam,
just navigate to Application Developer --> Concurrent --> Define, query the report and go to Parameters. Add a new parameter, define its Seq No. and Name and in the value set you can select 20 characters, below that is Default Type and in that LOV select Profile and in Default Value write USERNAME, you can pass different profile values to the report.

Regards,
Ali Pirzada

thanks a lot