GST Queries - PO

GST Related Transaction Queries :-

Purchase Order

Query to fetch the record of PO 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(pha.CREATION_DATE,'DD-MON-YYYY') Po_date,

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

           pha.segment1 TransactionNo,

           (select sum(l.UNIT_PRICE*l.TRX_LINE_QUANTITY) from

             jai_tax_det_factors l

           where l.trx_id=pha.po_header_id

          and  l.ENTITY_CODE = 'PURCHASE_ORDER') grossamount,

           (select sum(l.UNIT_PRICE*l.TRX_LINE_QUANTITY) from

             jai_tax_det_factors l

           where l.trx_id=pha.po_header_id

          and  l.ENTITY_CODE = 'PURCHASE_ORDER')

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

                FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

               WHERE   pha.po_header_id = jtl1.TRX_ID

                       AND jtl1.tax_type_id = jtt1.tax_type_id(+))

              netamount,

           pha.COMMENTS narration,

           ppx.full_name SalesExec,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       pha.po_header_id = jtl1.TRX_ID

             and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%CGST%')

              CGST_AMT,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       pha.po_header_id = jtl1.TRX_ID

             and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%SGST%')

              SGST_AMT,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       pha.po_header_id = jtl1.TRX_ID

             and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%IGST%')

              IGST_AMT,

           pvs.STATE,

           pla.LINE_NUM,

           msi.segment1,

           pla.quantity,

           pla.unit_price Rate,

           (pla.quantity * pla.unit_price) line_GrossAmount,

           (pla.quantity * pla.unit_price) TaxableValue,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       pha.po_header_id = jtl1.TRX_ID

                     AND pla.po_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     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       pha.po_header_id = jtl1.TRX_ID

                     AND pla.po_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     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       pha.po_header_id = jtl1.TRX_ID

                     AND pla.po_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     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       pha.po_header_id = jtl1.TRX_ID

                     AND pla.po_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     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       pha.po_header_id = jtl1.TRX_ID

                     AND pla.po_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     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       pha.po_header_id = jtl1.TRX_ID

                     AND pla.po_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     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       pha.po_header_id = jtl1.TRX_ID

                     AND pla.po_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     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       pha.po_header_id = jtl1.TRX_ID

                     AND pla.po_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     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       pha.po_header_id = jtl1.TRX_ID

                     AND pla.po_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'PURCHASE_ORDER'

                     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   po_headers_all pha,

           po_lines_all pla,

           po_line_locations_all plla,

           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 Po line*/

           jai_tax_det_factors det_fct,

           jai_tax_categories def_tax_cat,

           jai_tax_categories tax_cat,

           ja_lookups jl

   /*Table of india localization Po line Taxes*/

   --           jai_tax_lines_all jtl,

   --           jai_tax_rates jtr,

   --           jai_tax_types jtt

   where       pha.po_header_id = pla.po_header_id

           and pha.po_header_id=plla.po_header_id

           and pla.po_line_id=plla.po_line_id

           and pha.agent_id = ppx.person_id

           and pha.vendor_id = pv.vendor_id

           and pv.vendor_id = pvs.vendor_id

           and pha.vendor_site_id = pvs.vendor_site_id

           /*table of india localization po line*/

           and pha.po_header_id = det_fct.trx_id

           and pla.po_line_id = det_fct.trx_line_id

           and det_fct.entity_code = 'PURCHASE_ORDER'

           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'

           /*table of india localization po line taxes*/

           --           and pha.po_header_id = jtl.trx_id

           --           and pla.po_line_id = jtl.trx_line_id

           --           and jtl.entity_code   ='purchase_order'

           --           and jtl.tax_rate_id = jtr.tax_rate_id(+)

           --           and jtr.tax_type_id = jtt.tax_type_id(+)

         --  and pha.segment1 = '2200000178833'

group by   pha.creation_date,

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

           pha.segment1,

           pla.unit_price * pla.quantity,

           pha.comments,

           ppx.full_name,

           pha.po_header_id,

           pla.po_line_id,

           pvs.state,

           pla.line_num,

           msi.segment1,

           pla.quantity,

           pla.unit_price,

           ood.organization_code,

         ood.organization_name,

         ood.organization_id