Saturday 5 November 2011

R12 PO Receipt Tax Querie

/* Formatted on 1/8/2012 1:54:48 PM (QP5 v5.115.810.9015) */
SELECT   DISTINCT
         hou.name Operating_unit,
         TO_NUMBER (poh.segment1) PO_NO,
         (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   JAI_RCV_LINE_TAXES 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
 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 rsh.RECEIPT_NUM = '50126500022'

No comments:

Post a Comment