PO_Loc_Tax_Final

/* Formatted on 11-08-2011 01:29:10 (QP5 v5.115.810.9015) */
Select * from XX_APIL_POLINETAX
DROP TABLE XX_APIL_POLINETAX;
CREATE TABLE XX_APIL_POLINETAX
AS
     SELECT   hou.name Operating_unit,
              po.po_header_id,
              po.PO_LINE_ID,
              (SELECT   vendor_name
                 FROM   po_vendors
                WHERE   vendor_id = poh.vendor_id)
                 PO_Vendor_Name,
              TO_NUMBER (poh.segment1) PO_NO,
              pol.LINE_NUm PO_Line_No,
              tax.tax_id,
              tax.TAX_NAME,
              po.TAX_TYPE,
              tax.TAX_RATE,
              po.TAX_LINE_NO,
              po.PRECEDENCE_1,
              po.PRECEDENCE_2,
              po.PRECEDENCE_3,
              po.PRECEDENCE_4,
              po.PRECEDENCE_5,
              po.MODVAT_FLAG,
              po.TAX_AMOUNT,
              pov.vendor_name
       FROM   JA_IN_PO_LINE_LOCATION_TAXES po,
              JA_IN_TAX_CODES tax,
              po_vendors pov,
              po_headers_all poh,
              hr_operating_units hou,
              po_lines_all pol
      WHERE       po.TAX_ID = tax.TAX_ID
              AND pov.vendor_id = po.vendor_id
              AND po.po_header_id = poh.po_header_id
              AND poh.po_header_id = pol.po_header_id
              AND pol.po_line_id = po.po_line_id
              AND TRUNC (poh.creation_date) BETWEEN '01-APR-2011'
                                                AND  '30-JUN-2011'
              AND hou.ORGANIZATION_ID = poh.org_id
              AND hou.set_of_books_id = 5
   ORDER BY   po.po_header_id,
              po.PO_LINE_ID,
              TO_NUMBER (poh.segment1),
              pol.LINE_NUm,
              po.TAX_LINE_NO



       NEW
=====================
SELECT  DISTINCT hou.name Operating_unit,
              po.po_header_id,
              po.PO_LINE_ID,
              (SELECT   vendor_name
                 FROM   po_vendors
                WHERE   vendor_id = poh.vendor_id)
                 PO_Vendor_Name,
              TO_NUMBER (poh.segment1) PO_NO,
               mis.SEGMENT1 ITEM_NAME,
               mis.DESCRIPTION ITEM_DESCRIPTION,
              pol.LINE_NUm PO_Line_No,
              tax.tax_id,
              tax.TAX_NAME,
              po.TAX_TYPE,
              tax.TAX_RATE,
              po.TAX_LINE_NO,
              po.PRECEDENCE_1,
              po.PRECEDENCE_2,
              po.PRECEDENCE_3,
              po.PRECEDENCE_4,
              po.PRECEDENCE_5,
              po.MODVAT_FLAG,
              po.TAX_AMOUNT,
              pov.vendor_name
       FROM   JA_IN_PO_LINE_LOCATION_TAXES po,
              JA_IN_TAX_CODES tax,
              po_vendors pov,
              po_headers_all poh,
              hr_operating_units hou,
              po_lines_all pol,
              mtl_system_items_b MIS
      WHERE       po.TAX_ID = tax.TAX_ID
              AND pov.vendor_id = po.vendor_id
              AND po.po_header_id = poh.po_header_id
              AND poh.po_header_id = pol.po_header_id
              AND pol.po_line_id = po.po_line_id
--              AND TRUNC (poh.creation_date) BETWEEN '01-APR-2011'
--                                                AND  '30-JUN-2011'
              AND hou.ORGANIZATION_ID = poh.org_id
              AND MIS.INVENTORY_ITEM_ID = POL.ITEM_ID
              AND POH.SEGMENT1 IN ('47402336')
              AND hou.set_of_books_id = 5
   ORDER BY   po.po_header_id,
              po.PO_LINE_ID,
              TO_NUMBER (poh.segment1),
              pol.LINE_NUm,
              po.TAX_LINE_NO