Monday 31 October 2011

PO_RECEIPTS_TAX_QRY

/* Formatted on 16-08-2011 19:50:37 (QP5 v5.115.810.9015) */
  SELECT   DISTINCT
           hou.name Operating_unit,
           TO_NUMBER (poh.segment1) PO_NO,
           TO_NUMBER (rsh.RECEIPT_NUM) Receipt_No,
           pla.LINE_NUM,
           pov1.vendor_name Fright_Vendor,
           site.VENDOR_SITE_CODE,
           (SELECT   ood.organization_code
              FROM   org_organization_definitions ood
             WHERE   ood.organization_id = rsh.organization_id
                     AND ood.OPERATING_UNIT = hou.organization_id)
              Organization_Code,
           (SELECT   ood.organization_name
              FROM   org_organization_definitions ood
             WHERE   ood.organization_id = rsh.organization_id
                     AND ood.OPERATING_UNIT = hou.organization_id)
              Organization_Name,
           rsl.TO_SUBINVENTORY Sub_Inventory,
           jir.TAX_LINE_NO,
           jir.TAX_NAME,
           jir.TAX_RATE,
           jir.TAX_TYPE,
           jir.PRECEDENCE_1,
           jir.PRECEDENCE_2,
           jir.PRECEDENCE_3,
           jir.PRECEDENCE_4,
           jir.PRECEDENCE_5,
           jir.CURRENCY,
           jir.MODVAT_FLAG,
           jir.THIRD_PARTY_FLAG,
           jir.TAX_AMOUNT
    FROM   JA_IN_RECEIPT_TAX_LINES jir,
           rcv_shipment_lines rsl,
           po_vendors pov,
           po_vendors pov1,
           po_vendor_sites_all site,
           po_headers_all poh,
           po_lines_all pla,
           rcv_shipment_headers rsh,
           hr_operating_units hou                                          --,
   --org_organization_definitions ood
   WHERE       jir.SHIPMENT_LINE_ID = jir.SHIPMENT_LINE_ID
           AND jir.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID
           AND pov.vendor_id = jir.vendor_id
           AND site.vendor_site_id(+) = jir.vendor_site_id
           AND poh.po_header_id = rsl.PO_HEADER_ID
           AND poh.po_header_id = pla.po_header_id
           AND rsl.po_line_id = pla.po_line_id
           AND jir.vendor_id = pov1.vendor_id
           AND rsh.SHIPMENT_HEADER_ID = jir.SHIPMENT_HEADER_ID
           --AND jir.SHIPMENT_LINE_ID = 1454967
           AND hou.organization_id(+) = poh.org_id
           AND TRUNC (jir.CREATION_DATE) BETWEEN '01-APR-2011' --AND  '05-APR-2011'
                                             AND  '31-jul-2011'
           AND hou.set_of_books_id = 5
--and ood.set_of_books_id=5
ORDER BY   TO_NUMBER (poh.segment1),
           TO_NUMBER (rsh.RECEIPT_NUM),
           pla.LINE_NUM,
           jir.TAX_LINE_NO

No comments:

Post a Comment