GST Queries - GRN

GST Related Transaction Queries: -

Good Receipts

Query to fetch the record of Receipt data with header and line GST details: -

This query use to fetch consolidated record of base tables with GST details which is handled in sub query.

  SELECT   TO_CHAR (rch.creation_DATE, 'DD-MON-YYYY') receipt_date,

           pv.segment1 || '-' || pvs.vendor_site_code Seller,

           rch.receipt_num TransactionNo,

           (SELECT   SUM (l.UNIT_PRICE * l.TRX_LINE_QUANTITY)

              FROM   jai_tax_det_factors l

             WHERE       l.trx_id = rch.shipment_header_id

                     AND l.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND l.TRX_TYPE = 'RECEIVE')

              grossamount,

           (SELECT   SUM (l.UNIT_PRICE * l.TRX_LINE_QUANTITY)

              FROM   jai_tax_det_factors l

             WHERE       l.trx_id = rch.shipment_header_id

                     AND l.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND l.TRX_TYPE = 'RECEIVE')

           + (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

                FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

               WHERE       rch.shipment_header_id = jtl1.TRX_ID

                       AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                       AND jtl1.TRX_TYPE = 'RECEIVE')

              netamount,

           ppx.full_name SalesExec,

           (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%CGST%')

           + (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

                FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

               WHERE       rch.shipment_header_id = jtl1.TRX_ID

                       AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                       AND jtl1.TRX_TYPE = 'RECEIVE'

                       AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                       AND jtt1.TAX_TYPE_NAME LIKE '%SGST%')

           + (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

                FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

               WHERE       rch.shipment_header_id = jtl1.TRX_ID

                       AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                       AND jtl1.TRX_TYPE = 'RECEIVE'

                       AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                       AND jtt1.TAX_TYPE_NAME LIKE '%IGST%')

              GST_AMT,

           (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%CGST%')

              CGST_AMT,

           (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%SGST%')

              SGST_AMT,

           (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%IGST%')

              IGST_AMT,

           pvs.STATE,

           rcl.LINE_NUM,

           msi.segment1,

           rcl.QUANTITY_RECEIVED,

           rt.po_unit_price Rate,

           (rcl.quantity_received * rt.po_unit_price) line_GrossAmount,

           (rcl.quantity_received * rt.po_unit_price) TaxableValue,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%CGST%')

              LINE_CGST_AMT,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%SGST%')

              line_SGST_AMT,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%IGST%')

              line_IGST_AMT,

           (SELECT   MAX (TAX_RATE_PERCENTAGE)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%CGST%')

              LINE_CGST_RATE,

           (SELECT   MAX (TAX_RATE_PERCENTAGE)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%SGST%')

              line_SGST_RATE,

           (SELECT   MAX (TAX_RATE_PERCENTAGE)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%IGST%')

              line_IGST_RATE,

                (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE      rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE 'FREIGHT - LOCAL') FREIGHT_LOCAL,

                          (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE     rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE 'INSURANCE') INSURANCE,

                        (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE     rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE 'P&F, FREIGHT & INSURANCE') PF_FREIGHT_INSURANCE,

                 (SELECT   jprl.REGISTRATION_NUMBER

            FROM   jai_party_regs jpr, jai_party_reg_lines jprl

           WHERE       jprl.PARTY_REG_ID = jpr.PARTY_REG_ID

                   AND jpr.party_id = ood.organization_id

                   AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE

                   AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE

                   AND REGISTRATION_TYPE_CODE IN ('GTIN', 'GSTIN')

                   AND jpr.party_type_code IN ('IO'))

            "GSTN NUMBER of FIRST PARTY",

         ood.organization_code,

         ood.organization_name

    FROM   rcv_shipment_headers rch,

           rcv_shipment_lines rcl,

           rcv_transactions rt,

           per_people_x ppx,

           ap_suppliers pv,

           ap_supplier_sites_all pvs,

           mtl_system_items_b msi,

           org_organization_definitions ood,

           /*Table of india localization Receipt line*/

           jai_tax_det_factors det_fct,

           --           jai_tax_categories def_tax_cat,

           --           jai_tax_categories tax_cat,

           ja_lookups jl

   WHERE       rch.shipment_header_id = rcl.shipment_header_id

           AND rch.shipment_header_id = rt.shipment_header_id

           AND rcl.shipment_line_id = rt.shipment_line_id

           AND rt.transaction_type = 'RECEIVE'

           AND rch.employee_id = ppx.person_id

           AND rch.vendor_id = pv.vendor_id

           AND pv.vendor_id = pvs.vendor_id

           AND rch.vendor_site_id = pvs.vendor_site_id

           /*table of india localization po line*/

           AND rch.shipment_header_id = det_fct.trx_id

           AND rcl.shipment_line_id = det_fct.trx_line_id

           AND det_fct.entity_code = 'RCV_TRANSACTION'

           AND TRX_TYPE = 'RECEIVE'

           --           and det_fct.default_tax_category_id = def_tax_cat.tax_category_id(+)

           --           and det_fct.override_tax_category_id = tax_cat.tax_category_id(+)

           AND det_fct.organization_id = msi.organization_id

           AND det_fct.item_id = msi.inventory_item_id

           and det_fct.organization_id = ood.organization_id

           AND det_fct.intended_use = jl.lookup_code(+)

           AND jl.lookup_type(+) = 'JAI_INTENDED_USE'

GROUP BY   rch.creation_date,

           pv.segment1 || '-' || pvs.vendor_site_code,

           rch.receipt_num,

          rcl.quantity_received,

          rt.po_unit_price,

           rch.shipment_header_id,

           rcl.shipment_line_id,

           pvs.state,

           msi.segment1,

           rcl.LINE_NUM,ppx.full_name,

              ood.organization_code,

         ood.organization_name,

         ood.organization_id