Receipt Query for GST

 Below are some useful queries for finding the receipts with tax information for GST Changes

 Query to find all the receipts lines with tax amount = 0

SELECT jtlv.operating_unit, jtlv.party_name, jtlv.party_number,

       jtlv.tax_currency_conversion_rate, rsh.receipt_num,

       msib.segment1 item_code, rsl.item_description, jtc.tax_category_name,

       jtlv.tax_rate_name, jtlv.tax_type_name, jtlv.tax_rate_code,

       jtlv.organization_name, jtlv.trx_date, rsl.unit_of_measure,

       jtlv.unit_price, jtlv.line_amt, jtlv.tax_rate_percentage,

       exclusive_tax_amt_curr, jtlv.trx_line_quantity, jtlv.trx_currency_code,

       tax_regime_code, jtlv.recoverable_flag

  FROM jai_tax_lines_v jtlv,

       rcv_shipment_headers rsh,

       rcv_shipment_lines rsl,

       jai_tax_det_factors jdfi,

       jai_tax_categories jtc,

       mtl_system_items_b msib,

       po_lines_all pla

 WHERE jtlv.entity_code = 'RCV_TRANSACTION'

   AND jtlv.org_id NOT IN (85, 86) -- Operating Unit ID

   AND jtlv.trx_id = rsh.shipment_header_id                  

   AND jtlv.ref_doc_line_id = rsl.po_line_id

   AND jtlv.ref_doc_trx_id = rsl.po_header_id

   AND rsh.shipment_header_id = rsl.shipment_header_id

   AND jtlv.trx_line_id = rsl.shipment_line_id

   AND rsl.item_id = jtlv.item_id

   AND jtlv.det_factor_id = jdfi.det_factor_id

   AND jtlv.trx_type = 'RECEIVE'

   AND jtlv.item_id = msib.inventory_item_id

   AND jtlv.organization_id = msib.organization_id

   AND NVL (jdfi.override_tax_category_id, default_tax_category_id) =

                                                           jtc.tax_category_id

   AND jtlv.ref_doc_trx_id = pla.po_header_id

   AND jtlv.ref_doc_line_id = pla.po_line_id

   AND exclusive_tax_amt_curr = 0 --Tax Amount is zero

   AND jtlv.item_id = jdfi.item_id

   

  Query to find the receipts for which no tax is applied --

SELECT aps.VENDOR_NAME, aps.SEGMENT1 as vendor_num, 

       receipt_num, rsh.creation_date, rsl.to_organization_id,

       ood.organization_code, rsl.item_description, rsl.item_id,

       rsl.to_subinventory, micv.segment1, micv.category_concat_segs

  FROM rcv_shipment_headers rsh,

       rcv_shipment_lines rsl,

       rcv_transactions  rt,

       org_organization_definitions ood,

       mtl_item_categories_v micv,

       ap_suppliers aps

 WHERE rsh.creation_date BETWEEN '01-Jul-2017' AND '31-Jul-2017' --Receipt Creation Date 

   AND rsh.shipment_header_id = rsl.shipment_header_id

   AND rsh.receipt_num IS NOT NULL

   AND rsl.to_organization_id NOT IN (93, 100) -- Organization ID

   AND rsl.to_organization_id = ood.organization_id

   AND rsl.item_id = micv.inventory_item_id

   AND rsl.to_organization_id = micv.organization_id

   and rsh.vendor_id = aps.vendor_id

   and rsh.SHIPMENT_HEADER_ID = rt.SHIPMENT_HEADER_ID

   and rsl.SHIPMENT_LINE_ID = rt.SHIPMENT_LINE_ID

   and rt.TRANSACTION_TYPE ='RECEIVE'

   AND NOT EXISTS (

          SELECT 1

            FROM jai_tax_lines_v jtlv

           WHERE jtlv.entity_code = 'RCV_TRANSACTION'

             AND jtlv.org_id NOT IN (85, 86) --Operating Unit ID

             AND rsh.shipment_header_id = jtlv.trx_id

             AND jtlv.trx_line_id = rsl.shipment_line_id

             AND jtlv.trx_type = 'RECEIVE')