Home » Applications » Oracle Fusion Apps & E-Business Suite » How to find a Requisition Number from the given PO Number
| How to find a Requisition Number from the given PO Number [message #139950] |
Fri, 30 September 2005 02:53  |
justchakri
Messages: 27
Registered: September 2005
Location: Bangalore
|
Junior Member |
|
|
Hi ALL,
i want the query for finding the details of PR# (Requisition Number ) based on a particular PO Number and Cost Center.
I had written the query like this for getting the other details like vendor name , vendor code, receipt number etc...
i want to get the Requisition Number details for the PO which i am passing..
QUERY:
SELECT DISTINCT pha.segment1 "PO#"
, pha.po_header_id
, DECODE ( pha.type_lookup_code
, 'BLANKET', 'PA'
, 'CONTRACT', 'PA'
, 'STANDARD', 'PO'
, 'PLANNED', 'PO'
, pha.type_lookup_code
) "po type"
, pla.item_description "item descirption"
, pla.unit_price "Unit Price"
, pla.quantity "qty"
, ( pla.unit_price
* pla.quantity ) "PO Amount Value "
, pov.vendor_name "Vendor Name"
, pov.segment1 "Vendor Number"
, povs.vendor_site_code "Vendor Site CODE"
, rsh.receipt_num "GRN#"
FROM po_headers_all pha
, po_lines_all pla
, po_distributions_all pda
, gl_code_combinations gcc
, po_vendors pov
, po_vendor_sites_all povs
, rcv_shipment_lines rsl
, rcv_shipment_headers rsh
WHERE pha.po_header_id = pla.po_header_id
AND pla.org_id = pha.org_id
AND pha.vendor_id = pov.vendor_id
AND pha.vendor_site_id = povs.vendor_site_id
AND rsl.po_header_id = pha.po_header_id
AND rsl.po_line_id = pla.po_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND pha.org_id = 10
AND pda.po_line_id = pla.po_line_id
AND pda.org_id = pha.org_id
AND pda.code_combination_id = gcc.code_combination_id
AND gcc.segment3 = '001'
AND TRUNC ( pha.creation_date ) >=
TRUNC ( TO_DATE ( '01/01/2005', 'DD/MM/YYYY' ))
i know that i can get the Requisition Number from po_requisition_lines_all , po_requisition_headers_all (segment3 in this table). But iam not getting the correct joins to these tables, can any body plz help me.....
Thanks in Advance.... 
Bye,
Chakri
|
|
|
|
| Re: How to find a Requisition Number from the given PO Number [message #146481 is a reply to message #139950] |
Thu, 10 November 2005 15:54  |
AshokS
Messages: 4
Registered: November 2005
|
Junior Member |
|
|
Hi,
I have added the conditions. Add the requisition fields which you want to display.
SELECT DISTINCT pha.segment1 "PO#"
, pha.po_header_id
, DECODE ( pha.type_lookup_code
, 'BLANKET', 'PA'
, 'CONTRACT', 'PA'
, 'STANDARD', 'PO'
, 'PLANNED', 'PO'
, pha.type_lookup_code
) "po type"
, pla.item_description "item descirption"
, pla.unit_price "Unit Price"
, pla.quantity "qty"
, ( pla.unit_price
* pla.quantity ) "PO Amount Value "
, pov.vendor_name "Vendor Name"
, pov.segment1 "Vendor Number"
, povs.vendor_site_code "Vendor Site CODE"
, rsh.receipt_num "GRN#"
FROM po_headers_all pha
, po_lines_all pla
, po_distributions_all pda
, gl_code_combinations gcc
, po_vendors pov
, po_vendor_sites_all povs
, rcv_shipment_lines rsl
, rcv_shipment_headers rsh
,po_requisition_headers_all prh
,po_req_distributions_all prd
,po_requisition_lines_all porl
WHERE pha.po_header_id = pla.po_header_id
AND pla.org_id = pha.org_id
AND pha.vendor_id = pov.vendor_id
AND pha.vendor_site_id = povs.vendor_site_id
AND rsl.po_header_id = pha.po_header_id
AND rsl.po_line_id = pla.po_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND pha.org_id = 10
AND pda.po_line_id = pla.po_line_id
AND pda.org_id = pha.org_id
AND pda.code_combination_id = gcc.code_combination_id
AND gcc.segment3 = '001'
AND TRUNC ( pha.creation_date ) >=
TRUNC ( TO_DATE ( '01/01/2005', 'DD/MM/YYYY' ))
and prh.requisition_header_id(+)=porl.requisition_header_id
and porl.requisition_line_id(+)=prd.requisition_line_id
and prd.distribution_id(+)=pda.req_distribution_id
Regards
Ashok
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 11 02:37:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
