Home » Developer & Programmer » Reports & Discoverer » help needed with SQL query...discoverer report
| help needed with SQL query...discoverer report [message #153772] |
Wed, 04 January 2006 01:20  |
amankhan
Messages: 69
Registered: December 2005
Location: Texas
|
Member |
|
|
Hi Guys,
here is my query. Need some help. This query shows information about the requisitions created. Now my question is " I want to restrict this query so that a oracle applications user who logs in can only see his/her requisitions created and not others."
SELECT prh.creation_date req_creation_date,
prh.segment1 req_number,
prl.line_num req_line_num,
gl.SEGMENT3 FQA_Department,
per_gl.segment3 approver_department,
per.full_name approve_by,
prl.item_description,
hp.full_name REQUESTOR,
lp.full_name PREPARER_NAME,
gl.SEGMENT1 company,
cat.segment1 category,
(l.QUANTITY*l.UNIT_PRICE) requisition_amount,
prh.AUTHORIZATION_STATUS,
poh.segment1 po_number,
pv.vendor_name vendor,
poh.AUTHORIZATION_STATUS PO_STATUS
FROM apps.PO_REQUISITION_HEADERS_ALL prh,
apps.PO_REQUISITION_LINES_ALL prl,
apps.PO_REQ_DISTRIBUTIONS_ALL prd,
apps.per_all_people_f hp ,
apps.per_all_people_f lp ,
apps.PO_ACTION_HISTORY app,
apps.PER_PEOPLE_V7 per,
apps.GL_CODE_COMBINATIONS per_gl,
apps.PER_ASSIGNMENTS_V7 dep,
apps.po_distributions_all pod,
apps.po_lines_all pol,
apps.po_headers_all poh,
apps.po_vendors pv,
apps.GL_CODE_COMBINATIONS GL,
apps.MTL_CATEGORIES CAT
WHERE prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID
AND prl.REQUISITION_LINE_ID = prd.REQUISITION_LINE_ID
and prh.preparer_id = hp.person_id
and prl.to_person_id = lp.person_id
and h.REQUISITION_HEADER_ID = app.object_id (+)
and app.action_code = 'APPROVE'
and app.employee_id = per.person_id(+)
and per.person_id = dep.person_id(+)
and dep.DEFAULT_CODE_COMB_ID = per_gl.code_combination_id(+)
and prd.distribution_id = pod.req_distribution_id (+)
and pod.po_line_id = pol.po_line_id (+)
and pod.PO_HEADER_ID = poh.PO_HEADER_ID (+)
and poh.vendor_id = pv.vendor_id (+)
and prd.code_combination_id = gl.code_combination_id
and prl.category_id = cat.category_id
order by h.creation_date desc
can i include the table "fnd_user" which has a column employee_id which i can join with per_all_people_f, a table which has column employee_id in it.
pa.employee_id = fnd_user.employee_id
or should i use fnd_global.employee_id to join with the per_all_people_f employee_id.
Also, one more thing is " the user who is logged in is an employee which has his/her details in per_all_people_f. In my query, i used them twice to display "requestor name" and "preparer name". Now my question is " the condition is if he/she who is logged in , if present in "requestor name" column or "preparer name" column, then those records only i should display.
Help Appreciated.
Thanks
|
|
|
|
| Re: help needed with SQL query...discoverer report [message #155619 is a reply to message #153772] |
Tue, 17 January 2006 12:14  |
amankhan
Messages: 69
Registered: December 2005
Location: Texas
|
Member |
|
|
| amankhan wrote on Tue, 03 January 2006 23:20 |
Hi Guys,
here is my query. Need some help. This query shows information about the requisitions created. Now my question is " I want to restrict this query so that a oracle applications user who logs in can only see his/her requisitions created and not others."
SELECT prh.creation_date req_creation_date,
prh.segment1 req_number,
prl.line_num req_line_num,
gl.SEGMENT3 FQA_Department,
per_gl.segment3 approver_department,
per.full_name approve_by,
prl.item_description,
hp.full_name REQUESTOR,
lp.full_name PREPARER_NAME,
gl.SEGMENT1 company,
cat.segment1 category,
(l.QUANTITY*l.UNIT_PRICE) requisition_amount,
prh.AUTHORIZATION_STATUS,
poh.segment1 po_number,
pv.vendor_name vendor,
poh.AUTHORIZATION_STATUS PO_STATUS
FROM apps.PO_REQUISITION_HEADERS_ALL prh,
apps.PO_REQUISITION_LINES_ALL prl,
apps.PO_REQ_DISTRIBUTIONS_ALL prd,
apps.per_all_people_f hp ,
apps.per_all_people_f lp ,
apps.PO_ACTION_HISTORY app,
apps.PER_PEOPLE_V7 per,
apps.GL_CODE_COMBINATIONS per_gl,
apps.PER_ASSIGNMENTS_V7 dep,
apps.po_distributions_all pod,
apps.po_lines_all pol,
apps.po_headers_all poh,
apps.po_vendors pv,
apps.GL_CODE_COMBINATIONS GL,
apps.MTL_CATEGORIES CAT
WHERE prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID
AND prl.REQUISITION_LINE_ID = prd.REQUISITION_LINE_ID
and prh.preparer_id = hp.person_id
and prl.to_person_id = lp.person_id
and h.REQUISITION_HEADER_ID = app.object_id (+)
and app.action_code = 'APPROVE'
and app.employee_id = per.person_id(+)
and per.person_id = dep.person_id(+)
and dep.DEFAULT_CODE_COMB_ID = per_gl.code_combination_id(+)
and prd.distribution_id = pod.req_distribution_id (+)
and pod.po_line_id = pol.po_line_id (+)
and pod.PO_HEADER_ID = poh.PO_HEADER_ID (+)
and poh.vendor_id = pv.vendor_id (+)
and prd.code_combination_id = gl.code_combination_id
and prl.category_id = cat.category_id
order by h.creation_date desc
can i include the table "fnd_user" which has a column employee_id which i can join with per_all_people_f, a table which has column employee_id in it.
pa.employee_id = fnd_user.employee_id
or should i use fnd_global.employee_id to join with the per_all_people_f employee_id.
Also, one more thing is " the user who is logged in is an employee which has his/her details in per_all_people_f. In my query, i used them twice to display "requestor name" and "preparer name". Now my question is " the condition is if he/she who is logged in , if present in "requestor name" column or "preparer name" column, then those records only i should display.
Help Appreciated.
Thanks
|
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 09:59:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
