Query To Get PO Details With TAX Amount & Item Amount Details In EBS R12

 Query to Get PO detail with his tax amount and item amount details

Note:- Only Purchase Requisitions will be converted to the Purchase orders.

select pha.SEGMENT1 po_num , pha.AUTHORIZATION_STATUS po_status , pha.CLOSED_CODE close_status , trunc(pha.CREATION_DATE) po_date , pla.LINE_NUM po_line_number , gcc.SEGMENT2 cost_center , gl_flexfields_pkg.get_description_sql (0986, 2, gcc.segment2 ) cost_center_desc, pla.UNIT_PRICE , pla.QUANTITY , (pla.UNIT_PRICE*pla.QUANTITY) Base_amount , ( select nvl(sum(jpt.TAX_AMOUNT),0) from jai_po_taxes jpt, jai_cmn_taxes_all jcta where jpt.PO_HEADER_ID =pla.PO_HEADER_ID and jpt.PO_LINE_ID =pla.PO_LINE_ID and jcta.TAX_ID =jpt.TAX_ID and jcta.INCLUSIVE_TAX_FLAG='N' ) tax_amt, ((pla.UNIT_PRICE*pla.QUANTITY) + ( select nvl(sum(jpt.TAX_AMOUNT),0) from jai_po_taxes jpt, jai_cmn_taxes_all jcta where jpt.PO_HEADER_ID =pla.PO_HEADER_ID and jpt.PO_LINE_ID =pla.PO_LINE_ID and jcta.TAX_ID =jpt.TAX_ID and jcta.INCLUSIVE_TAX_FLAG='N' ) ) total_amt from po_headers_all pha , po_lines_all pla , po_distributions_all pda , po_line_locations_all plla , gl_code_combinations gcc where pha.PO_HEADER_ID =pla.PO_HEADER_ID and pla.PO_LINE_ID =pda.PO_LINE_ID and pda.PO_HEADER_ID =pha.PO_HEADER_ID and pda.LINE_LOCATION_ID =plla.LINE_LOCATION_ID and pda.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID and pha.SEGMENT1 = :P_PO_NUM order by 1,5