Home » Applications » Oracle Fusion Apps & E-Business Suite » need help with the sql query
| need help with the sql query [message #151640] |
Thu, 15 December 2005 23:55 |
amankhan
Messages: 69
Registered: December 2005
Location: Texas
|
Member |
|
|
Hi Guys,
My query looks like this.
=============================================================
SELECT prha.creation_date "Requisition Creation Date"
, prha.segment1 "Requistion Number"
, prla.LINE_NUM "Requisition Line Number"
, gcc.SEGMENT1 ||'.'||gcc.segment2||'.'||gcc.segment3 "Requisition FQA"
, gcc1.segment3 "Approver Department"
, he.FULL_NAME "Approver Name"
, prla.ITEM_DESCRIPTION "Requisition Item Description"
, he1.FULL_NAME "Requisition Requestor"
, he2.FULL_NAME "Requisition Preparer"
, gcc.segment1 "Requisition Company"
, mtc.DESCRIPTION "Requisition Category"
, (prla.QUANTITY * prla.UNIT_PRICE) "Requisition Total Amount"
, prha.AUTHORIZATION_STATUS "Requisition Status"
, pha.segment1 "PO Number"
, pha.authorization_status "PO Status"
, pda.req_distribution_id " PO Req Dist ID"
FROM po_requisition_lines_all prla
, po_requisition_headers_all prha
, po_distributions_all pda
, po_headers_all
, gl_code_combinations gcc
, gl_code_combinations gcc1
, po_req_distributions_all prda
, po_approval_list_lines pall
, po_approval_list_headers palh
, mtl_categories mtc
, per_people_f he
, per_people_f he1
, per_people_f he2
, PER_ASSIGNMENTS_V7 pav
WHERE prla.REQUISITION_HEADER_ID = prha.REQUISITION_HEADER_ID
AND prla.REQUISITION_LINE_ID = prda.REQUISITION_LINE_ID
AND prda.distribution_id = pda.req_distribution_id
AND pda.po_header_id = pha.po_header_id
AND prla.CATEGORY_ID = mtc.CATEGORY_ID
AND prla.TO_PERSON_ID = he1.PERSON_ID
AND prha.PREPARER_ID = he2.PERSON_ID
AND prha.REQUISITION_HEADER_ID = palh.document_id
AND gcc.CODE_COMBINATION_ID = prda.CODE_COMBINATION_ID
AND he.PERSON_ID = pall.APPROVER_ID
AND pall.approval_list_header_id = palh.approval_list_header_id
AND he.PERSON_ID = pav.PERSON_ID
AND pav.DEFAULT_CODE_COMB_ID = gcc1.CODE_COMBINATION_ID
AND pall.STATUS = 'APPROVE'
=======================================================
In the above query, po_distributions_all.req_distribution_id , this req_distribution_id is a foriegn key dependent on po_req_distributions_all.distribution_id (distribution_id which is a primary key). But this PO_DISTRIBUTIONS_ALL table is populated after a procedure is implemented. Before that, this table PO_DISTRIBUTIONS_ALL for that row will have nothing. In that case, before the autocreate procedure is executed, I want to display the column PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID as null in the output.
Is WHERE PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID is sufficient or anything else to be added in the WHERE clause. coz my doubt is if PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID is not populated for some lines, then for that line this where condition fails. In this case, I want to display the column value as NULL.
Help needed and Appreciated.
My output should look like this:
=======================================================================================================
Requisition Creation Date Requistion Number Requisition Line Number Requisition FQA Approver Department Approver Name Requisition Item Description Requestor
12-05-2005 2345 1 2435.345.345 ABC Dept1 DEF ITEM DESC DEF
12-06-2005 2341 1 2345.324.345 DEF Dept1 DEF ITEM DESC1 ABC
Requisition Preparer Requisition Company Requisition Category Requisition Total Amount Requisition Status PO Number PO Status PO Req Dist ID
DEF Dept1 Item Software 2345.00 APPR 23423 APPR 234325
DEF Dept2 Comp Hard 3211.00 APPR NULL NULL NULL
"NULL" means it shouldnt display anything over there.
===================================================================================================
Thanks
|
|
|
|
Goto Forum:
Current Time: Thu May 09 15:06:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
