Showing posts with label VAT. Show all posts
Showing posts with label VAT. Show all posts

VAT Register Query

SELECT 'INPUT_R' tax_register_source, aia.legal_entity_id,
          aia.set_of_books_id, aia.org_id company_code, hou.NAME ou_name,
          aia.doc_sequence_value transaction_number,
          aia.SOURCE transaction_source, aia.invoice_id,
          aia.invoice_num invoice_number,
          aia.invoice_type_lookup_code transaction_type,
          UPPER (aia.SOURCE) transaction_classification,     -- Same as source
                                                        aia.gl_date,
          aia.invoice_date, aia.vendor_id, asa.vendor_name supplier_name,
          assa.vat_registration_num registration_num, assa.city LOCATION,
          ap_invoices_pkg.get_approval_status
                                (aia.invoice_id,
                                 aia.invoice_amount,
                                 aia.payment_status_flag,
                                 aia.invoice_type_lookup_code
                                ) approval_status,
          NVL (aida_item.amount, 0) transaction_line_amount,
          aida_item.invoice_line_number transaction_line_number,
          aida_item.description transaction_description,
          item_ccid.concatenated_segments asset_expense_account,
          rec_ccid.concatenated_segments tax_distribution_account,
          NVL (aida_item.amount, 0) invoice_ledger_currency_amount,
          NVL (aida_item.base_amount, 0) invoice_foreign_curr_amount,
          aia.invoice_currency_code currency_code,
          (SELECT (CASE
                      WHEN aia.invoice_date < '1-JAN-2018'
                         THEN 'OSC-2017'
                      ELSE tax_rate_code
                   END
                  )
             FROM zx_rec_nrec_dist
            WHERE rec_nrec_tax_dist_id = aida_rec.detail_tax_dist_id)
                                                                     tax_code,
          (SELECT (CASE
                      WHEN aia.invoice_date < '1-JAN-2018'
                         THEN 0
                      ELSE tax_rate
                   END
                  )
             FROM gl_tax_codes_v
            WHERE tax_code_id = aida_rec.tax_code_id
              AND org_id = aida_rec.org_id) tax_rate,
          NVL (aida_rec.amount, 0) input_tax_amount,
          NVL (aida_rec.base_amount, 0) input_tax_funct_curr_amount,
          NULL output_tax_amount, NULL output_tax_func_curr_amount,
          payments.check_date payment_date, payments.payment_voucher,
          aia.amount_paid,
                          ---------Added -----------
                          aia.batch_id, aia.invoice_type_lookup_code,
          assa.vendor_site_code, (SELECT batch_name
                                    FROM ap_batches_all
                                   WHERE batch_id = aia.batch_id) batch_name,
          (SELECT segment1
             FROM po_headers_all
            WHERE po_header_id = aia.po_header_id
              AND org_id = aia.org_id) po_num,
          (SELECT NAME
             FROM pa_projects_all
            WHERE project_id = aida_item.task_id) project_name,
          (SELECT task_name
             FROM pa_tasks
            WHERE task_id = aida_item.task_id) task_name,
          (SELECT NAME
             FROM ap_terms
            WHERE term_id = aia.terms_id) terms,
          NVL (aia.exchange_rate, 1) exchange_rate,
          (CASE
              WHEN ap_invoices_pkg.get_approval_status
                                                 (aia.invoice_id,
                                                  aia.invoice_amount,
                                                  aia.payment_status_flag,
                                                  aia.invoice_type_lookup_code
                                                 ) != 'CANCELLED'
                 THEN aia.invoice_amount
              ELSE aida_item.amount
           END
          ) invoice_amount,
          DECODE (aia.invoice_type_lookup_code,
                  'PREPAYMENT', aia.invoice_amount,
                  0
                 ) prepayment_amt,
          aida_item.expenditure_type, UPPER (aia.SOURCE) transaction_class,
          aia.pay_group_lookup_code, aia.SOURCE inv_source,
          aia.invoice_type_lookup_code invoice_type,
          NVL
             (ap_invoice_lines_utility_pkg.get_approval_status
                                                (aia.invoice_id,
                                                 aida_item.invoice_line_number
                                                ),
              'NEVER APPROVED'
             ) validation_status,
          (SELECT aca.payment_method_code
             FROM ap_checks_all aca,
                  ap_invoice_payments_all aip
            WHERE aca.check_id = aip.check_id
              AND aca.org_id = aip.org_id
              AND NVL (aip.reversal_flag, 'N') = 'N'
              AND aip.org_id = aia.org_id
              AND aip.invoice_id = aia.invoice_id
              AND ROWNUM = 1) payment_method,
          aida_item.accounting_date, aida_item.line_type_lookup_code
---------------------------------
   FROM   ap_invoices_all aia,
          hr_operating_units hou,
          ap_suppliers asa,
          ap_supplier_sites_all assa,
          (SELECT set_of_books_id, dist_code_combination_id, org_id,
                  detail_tax_dist_id, invoice_id, amount, base_amount,
                  start_date, end_date, aida_item.description,
                  invoice_distribution_id, invoice_line_number, task_id,
                  accounting_date, expenditure_type, distribution_line_number,
                  line_type_lookup_code                               -- Added
             FROM ap_invoice_distributions_all aida_item, gl_periods
            WHERE 1 = 1
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
              AND aida_item.accounting_date BETWEEN start_date AND end_date
              AND aida_item.line_type_lookup_code IN
                     ('ITEM', 'ITEM', 'PREPAY', 'AWT', 'IPV', 'MISCELLANEOUS',
                      'ERV')) aida_item,
          (SELECT set_of_books_id, dist_code_combination_id, org_id,
                  detail_tax_dist_id, invoice_id, accounting_date, amount,
                  base_amount, start_date, end_date, aida.description,
                  charge_applicable_to_dist_id, tax_code_id
             FROM ap_invoice_distributions_all aida, gl_periods
            WHERE 1 = 1
              AND aida.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
              AND aida.line_type_lookup_code = 'REC_TAX') aida_rec,
         
          /* Payments table in outer query begins */
          (SELECT aip.org_id, aip.invoice_id, aca.payment_method_code,
                  aca.check_date, aca.doc_sequence_value payment_voucher,
                  aca.amount paid_amount
             FROM ap_checks_all aca, ap_invoice_payments_all aip
            WHERE aca.check_id = aip.check_id
              AND aca.org_id = aip.org_id
              AND NVL (aip.reversal_flag, 'N') = 'N'
              AND aip.ROWID IN (
                     SELECT MIN (aip_in.ROWID)
                       FROM ap_checks_all aca_in,
                            ap_invoice_payments_all aip_in
                      WHERE aca_in.check_id = aip_in.check_id
                        AND aca_in.org_id = aip_in.org_id
                        AND NVL (aip_in.reversal_flag, 'N') = 'N'
                        AND aip.invoice_id = aip_in.invoice_id)) payments,
          gl_code_combinations_kfv item_ccid,
          gl_code_combinations_kfv rec_ccid
    WHERE 1 = 1
      --and aida_item.invoice_distribution_id  = zl_standard_rec.charge_applicable_to_dist_id (+)
      AND aida_item.invoice_distribution_id = aida_rec.charge_applicable_to_dist_id(+)
      AND aida_item.invoice_id = aia.invoice_id
      --AND aida_item.tax_code_id  = aida_rec.tax_code_id(+)
      --AND aida_rec.invoice_id  = aia.invoice_id
      AND aia.vendor_id = asa.vendor_id
      AND aia.org_id = assa.org_id
      AND aia.vendor_id = assa.vendor_id
      AND aia.vendor_site_id = assa.vendor_site_id
      AND hou.organization_id = aia.org_id
      --and aia.invoice_id = aila.invoice_id
      --and aila.invoice_id = aida_item.invoice_id
      AND aia.org_id = payments.org_id(+)
      AND aia.invoice_id = payments.invoice_id(+)
      AND aida_item.dist_code_combination_id = item_ccid.code_combination_id(+)
      AND aida_rec.dist_code_combination_id = rec_ccid.code_combination_id
      AND 'Y' = ap_invoices_pkg.get_posting_status (aia.invoice_id)
      AND
      --(  
        aia.gl_date BETWEEN aida_rec.start_date AND aida_rec.end_date
           AND aia.gl_date BETWEEN aida_item.start_date AND aida_item.end_date
        --  )
   ---and aida_rec.dist_code_combination_id = 700591 ,aia.invoice_id=1842014
   UNION ALL                             -------------------------------------
   SELECT 'INPUT_N' tax_register_source, aia.legal_entity_id,
          aia.set_of_books_id, aia.org_id company_code, hou.NAME ou_name,
          aia.doc_sequence_value transaction_number,
          aia.SOURCE transaction_source, aia.invoice_id,
          aia.invoice_num invoice_number,
          aia.invoice_type_lookup_code transaction_type,
          UPPER (aia.SOURCE) transaction_classification,     -- Same as source
                                                        aia.gl_date,
          aia.invoice_date, aia.vendor_id, asa.vendor_name supplier_name,
          assa.vat_registration_num registration_num, assa.city LOCATION,
          ap_invoices_pkg.get_approval_status
                                (aia.invoice_id,
                                 aia.invoice_amount,
                                 aia.payment_status_flag,
                                 aia.invoice_type_lookup_code
                                ) approval_status,
          NVL (aida_item.amount, 0) transaction_line_amount,
          aida_item.invoice_line_number transaction_line_number,
          aida_item.description transaction_description,
          item_ccid.concatenated_segments asset_expense_account,
          nrec_ccid.concatenated_segments tax_distribution_account,
          NVL (aida_item.amount, 0) invoice_ledger_currency_amount,
          NVL (aida_item.base_amount, 0) invoice_foreign_curr_amount,
          aia.invoice_currency_code currency_code,
          (SELECT (CASE
                      WHEN aia.invoice_date < '1-JAN-2018'
                         THEN 'OSC-2017'
                      ELSE tax_rate_code
                   END
                  )                                            --tax_rate_code
             FROM zx_rec_nrec_dist
            WHERE rec_nrec_tax_dist_id = aida_rec.detail_tax_dist_id)
                                                                     tax_code,
          (SELECT (CASE
                      WHEN aia.invoice_date < '1-JAN-2018'
                         THEN 0
                      ELSE tax_rate
                   END
                  )
             FROM gl_tax_codes_v
            WHERE tax_code_id = aida_rec.tax_code_id
              AND org_id = aida_rec.org_id) tax_rate,
          NVL (aida_rec.amount, 0) input_tax_amount,
          NVL (aida_rec.base_amount, 0) input_tax_funct_curr_amount,
          NULL output_tax_amount, NULL output_tax_func_curr_amount,
          payments.check_date payment_date, payments.payment_voucher,
          aia.amount_paid,
                          ---------Added -----------
                          aia.batch_id, aia.invoice_type_lookup_code,
          assa.vendor_site_code, (SELECT batch_name
                                    FROM ap_batches_all
                                   WHERE batch_id = aia.batch_id) batch_name,
          (SELECT segment1
             FROM po_headers_all
            WHERE po_header_id = aia.po_header_id
              AND org_id = aia.org_id) po_num,
          (SELECT NAME
             FROM pa_projects_all
            WHERE project_id = aida_item.task_id) project_name,
          (SELECT task_name
             FROM pa_tasks
            WHERE task_id = aida_item.task_id) task_name,
          (SELECT NAME
             FROM ap_terms
            WHERE term_id = aia.terms_id) terms,
          NVL (aia.exchange_rate, 1) exchange_rate,
          (CASE
              WHEN ap_invoices_pkg.get_approval_status
                                                 (aia.invoice_id,
                                                  aia.invoice_amount,
                                                  aia.payment_status_flag,
                                                  aia.invoice_type_lookup_code
                                                 ) != 'CANCELLED'
                 THEN aia.invoice_amount
              ELSE aida_item.amount
           END
          ) invoice_amount,
          DECODE (aia.invoice_type_lookup_code,
                  'PREPAYMENT', aia.invoice_amount,
                  0
                 ) prepayment_amt,
          aida_item.expenditure_type, UPPER (aia.SOURCE) transaction_class,
          aia.pay_group_lookup_code, aia.SOURCE inv_source,
          aia.invoice_type_lookup_code invoice_type,
          NVL
             (ap_invoice_lines_utility_pkg.get_approval_status
                                                (aia.invoice_id,
                                                 aida_item.invoice_line_number
                                                ),
              'NEVER APPROVED'
             ) validation_status,
          (SELECT aca.payment_method_code
             FROM ap_checks_all aca,
                  ap_invoice_payments_all aip
            WHERE aca.check_id = aip.check_id
              AND aca.org_id = aip.org_id
              AND NVL (aip.reversal_flag, 'N') = 'N'
              AND aip.org_id = aia.org_id
              AND aip.invoice_id = aia.invoice_id
              AND ROWNUM = 1) payment_method,
          aida_item.accounting_date, aida_item.line_type_lookup_code
---------------------------------
   FROM   ap_invoices_all aia,
          hr_operating_units hou,
          ap_suppliers asa,
          ap_supplier_sites_all assa,
          (SELECT set_of_books_id, dist_code_combination_id, org_id,
                  invoice_id, amount, base_amount, aida_item.description,
                  invoice_distribution_id, start_date, end_date,
                  invoice_line_number, task_id, accounting_date,
                  expenditure_type, distribution_line_number,
                  line_type_lookup_code                               -- Added
             FROM ap_invoice_distributions_all aida_item, gl_periods
            WHERE 1 = 1
              AND aida_item.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
              AND aida_item.line_type_lookup_code IN
                     ('ITEM', 'ITEM', 'PREPAY', 'AWT', 'IPV', 'MISCELLANEOUS',
                      'ERV')) aida_item,
          (SELECT set_of_books_id, dist_code_combination_id, org_id,
                  detail_tax_dist_id, invoice_id, accounting_date, amount,
                  base_amount, start_date, end_date, aida.description,
                  charge_applicable_to_dist_id, tax_code_id
             FROM ap_invoice_distributions_all aida, gl_periods
            WHERE 1 = 1
              AND aida.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
              AND aida.line_type_lookup_code = 'NONREC_TAX') aida_rec,
         
          /* Payments table in outer query begins */
          (SELECT aip.org_id, aip.invoice_id, aca.payment_method_code,
                  aca.check_date, aca.doc_sequence_value payment_voucher,
                  aca.amount paid_amount
             FROM ap_checks_all aca, ap_invoice_payments_all aip
            WHERE aca.check_id = aip.check_id
              AND aca.org_id = aip.org_id
              AND NVL (aip.reversal_flag, 'N') = 'N'
              AND aip.ROWID IN (
                     SELECT MIN (aip_in.ROWID)
                       FROM ap_checks_all aca_in,
                            ap_invoice_payments_all aip_in
                      WHERE aca_in.check_id = aip_in.check_id
                        AND aca_in.org_id = aip_in.org_id
                        AND NVL (aip_in.reversal_flag, 'N') = 'N'
                        AND aip.invoice_id = aip_in.invoice_id)) payments,
          gl_code_combinations_kfv item_ccid,
          gl_code_combinations_kfv nrec_ccid
    WHERE 1 = 1
      AND aida_item.invoice_distribution_id = aida_rec.charge_applicable_to_dist_id(+)
      AND aida_item.invoice_id = aia.invoice_id
      --AND aida_item.tax_code_id  = aida_rec.tax_code_id(+)
      --AND aida_rec.invoice_id  = aia.invoice_id
      AND aia.vendor_id = asa.vendor_id
      AND aia.org_id = assa.org_id
      AND aia.vendor_id = assa.vendor_id
      AND aia.vendor_site_id = assa.vendor_site_id
      AND hou.organization_id = aia.org_id
      AND aia.org_id = payments.org_id(+)
      AND aia.invoice_id = payments.invoice_id(+)
      --and aida_rec.dist_code_combination_id = 700591
      AND aida_item.dist_code_combination_id = item_ccid.code_combination_id(+)
      AND aida_rec.dist_code_combination_id = nrec_ccid.code_combination_id
      AND 'Y' = ap_invoices_pkg.get_posting_status (aia.invoice_id)
      AND
      --(
        aia.gl_date BETWEEN aida_rec.start_date AND aida_rec.end_date
           AND aia.gl_date BETWEEN aida_item.start_date AND aida_item.end_date
          --)
   UNION ALL                             -------------------------------------
   SELECT 'INPUT_T' tax_register_source, aia.legal_entity_id,
          aia.set_of_books_id, aia.org_id company_code, hou.NAME ou_name,
          aia.doc_sequence_value transaction_number,
          aia.SOURCE transaction_source, aia.invoice_id,
          aia.invoice_num invoice_number,
          aia.invoice_type_lookup_code transaction_type,
          UPPER (aia.SOURCE) transaction_classification,     -- Same as source
                                                        aia.gl_date,
          aia.invoice_date, aia.vendor_id, asa.vendor_name supplier_name,
          assa.vat_registration_num registration_num, assa.city LOCATION,
          ap_invoices_pkg.get_approval_status
                                (aia.invoice_id,
                                 aia.invoice_amount,
                                 aia.payment_status_flag,
                                 aia.invoice_type_lookup_code
                                ) approval_status,
          NVL (aida_item.amount, 0) transaction_line_amount,
          aida_item.invoice_line_number transaction_line_number,
          aida_item.description transaction_description,
          item_ccid.concatenated_segments asset_expense_account,
          nrec_ccid.concatenated_segments tax_distribution_account,
          NVL (aida_item.amount, 0) invoice_ledger_currency_amount,
          NVL (aida_item.base_amount, 0) invoice_foreign_curr_amount,
          aia.invoice_currency_code currency_code,
          (SELECT (CASE
                      WHEN aia.invoice_date < '1-JAN-2018'
                         THEN 'OSC-2017'
                      ELSE tax_rate_code
                   END
                  )                                            --tax_rate_code
             FROM zx_rec_nrec_dist
            WHERE rec_nrec_tax_dist_id = aida_rec.detail_tax_dist_id)
                                                                     tax_code,
          (SELECT (CASE
                      WHEN aia.invoice_date < '1-JAN-2018'
                         THEN 0
                      ELSE tax_rate
                   END
                  )
             FROM gl_tax_codes_v
            WHERE tax_code_id = aida_rec.tax_code_id
              AND org_id = aida_rec.org_id) tax_rate,
          NVL (aida_rec.amount, 0) input_tax_amount,
          NVL (aida_rec.base_amount, 0) input_tax_funct_curr_amount,
          NULL output_tax_amount, NULL output_tax_func_curr_amount,
          payments.check_date payment_date, payments.payment_voucher,
          aia.amount_paid,
                          ---------Added -----------
                          aia.batch_id, aia.invoice_type_lookup_code,
          assa.vendor_site_code, (SELECT batch_name
                                    FROM ap_batches_all
                                   WHERE batch_id = aia.batch_id) batch_name,
          (SELECT segment1
             FROM po_headers_all
            WHERE po_header_id = aia.po_header_id
              AND org_id = aia.org_id) po_num,
          (SELECT NAME
             FROM pa_projects_all
            WHERE project_id = aida_item.task_id) project_name,
          (SELECT task_name
             FROM pa_tasks
            WHERE task_id = aida_item.task_id) task_name,
          (SELECT NAME
             FROM ap_terms
            WHERE term_id = aia.terms_id) terms,
          NVL (aia.exchange_rate, 1) exchange_rate,
          (CASE
              WHEN ap_invoices_pkg.get_approval_status
                                                 (aia.invoice_id,
                                                  aia.invoice_amount,
                                                  aia.payment_status_flag,
                                                  aia.invoice_type_lookup_code
                                                 ) != 'CANCELLED'
                 THEN aia.invoice_amount
              ELSE aida_item.amount
           END
          ) invoice_amount,
          DECODE (aia.invoice_type_lookup_code,
                  'PREPAYMENT', aia.invoice_amount,
                  0
                 ) prepayment_amt,
          aida_item.expenditure_type, UPPER (aia.SOURCE) transaction_class,
          aia.pay_group_lookup_code, aia.SOURCE inv_source,
          aia.invoice_type_lookup_code invoice_type,
          NVL
             (ap_invoice_lines_utility_pkg.get_approval_status
                                                (aia.invoice_id,
                                                 aida_item.invoice_line_number
                                                ),
              'NEVER APPROVED'
             ) validation_status,
          (SELECT aca.payment_method_code
             FROM ap_checks_all aca,
                  ap_invoice_payments_all aip
            WHERE aca.check_id = aip.check_id
              AND aca.org_id = aip.org_id
              AND NVL (aip.reversal_flag, 'N') = 'N'
              AND aip.org_id = aia.org_id
              AND aip.invoice_id = aia.invoice_id
              AND ROWNUM = 1) payment_method,
          aida_item.accounting_date, aida_item.line_type_lookup_code
---------------------------------
   FROM   ap_invoices_all aia,
          hr_operating_units hou,
          ap_suppliers asa,
          ap_supplier_sites_all assa,
          (SELECT set_of_books_id, dist_code_combination_id, org_id,
                  invoice_id, amount, base_amount, aida_item.description,
                  invoice_distribution_id, start_date, end_date,
                  invoice_line_number, task_id, accounting_date,
                  expenditure_type, distribution_line_number,
                  line_type_lookup_code                               -- Added
             FROM ap_invoice_distributions_all aida_item, gl_periods
            WHERE 1 = 1
              AND aida_item.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
              AND aida_item.line_type_lookup_code IN
                     ('ITEM', 'ITEM', 'PREPAY', 'AWT', 'IPV', 'MISCELLANEOUS',
                      'ERV')) aida_item,
          (SELECT set_of_books_id, dist_code_combination_id, org_id,
                  detail_tax_dist_id, invoice_id, accounting_date, amount,
                  base_amount, start_date, end_date, aida.description,
                  charge_applicable_to_dist_id, tax_code_id
             FROM ap_invoice_distributions_all aida, gl_periods
            WHERE 1 = 1
              AND aida.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
              AND aida.line_type_lookup_code IN ('TIPV', 'TERV', 'TRV')) aida_rec,
         
          /* Payments table in outer query begins */
          (SELECT aip.org_id, aip.invoice_id, aca.payment_method_code,
                  aca.check_date, aca.doc_sequence_value payment_voucher,
                  aca.amount paid_amount
             FROM ap_checks_all aca, ap_invoice_payments_all aip
            WHERE aca.check_id = aip.check_id
              AND aca.org_id = aip.org_id
              AND NVL (aip.reversal_flag, 'N') = 'N'
              AND aip.ROWID IN (
                     SELECT MIN (aip_in.ROWID)
                       FROM ap_checks_all aca_in,
                            ap_invoice_payments_all aip_in
                      WHERE aca_in.check_id = aip_in.check_id
                        AND aca_in.org_id = aip_in.org_id
                        AND NVL (aip_in.reversal_flag, 'N') = 'N'
                        AND aip.invoice_id = aip_in.invoice_id)) payments,
          gl_code_combinations_kfv item_ccid,
          gl_code_combinations_kfv nrec_ccid
    WHERE 1 = 1
      AND aida_item.invoice_distribution_id = aida_rec.charge_applicable_to_dist_id(+)
      AND aida_item.invoice_id = aia.invoice_id
      --AND aida_item.tax_code_id  = aida_rec.tax_code_id(+)
      --AND aida_rec.invoice_id  = aia.invoice_id
      AND aia.vendor_id = asa.vendor_id
      AND aia.org_id = assa.org_id
      AND aia.vendor_id = assa.vendor_id
      AND aia.vendor_site_id = assa.vendor_site_id
      AND hou.organization_id = aia.org_id
      AND aia.org_id = payments.org_id(+)
      AND aia.invoice_id = payments.invoice_id(+)
      --and aida_rec.dist_code_combination_id = 700591
      AND aida_item.dist_code_combination_id = item_ccid.code_combination_id(+)
      AND aida_rec.dist_code_combination_id = nrec_ccid.code_combination_id
      AND 'Y' = ap_invoices_pkg.get_posting_status (aia.invoice_id)
      AND
      --(
        aia.gl_date BETWEEN aida_rec.start_date AND aida_rec.end_date
           AND aia.gl_date BETWEEN aida_item.start_date AND aida_item.end_date
          --)