Thursday 19 September 2019

Script for getting Sales Tax Transactions

SELECT DISTINCT aia.invoice_date transaction_date,
                aia.invoice_num transaction_number, hou.NAME company_name,               
                zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
                (SELECT meaning
                   FROM fnd_lookup_values_vl
                  WHERE lookup_code = UPPER (zl.event_class_code)
                    AND lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
                    AND enabled_flag = 'Y') transaction_class,               
                aia.gl_date accounting_date,
                psv.vendor_name third_party, pss.vendor_site_code site,
                zl.tax_amt tax_amount, zl.trx_currency_code entered_curr,
                zl.taxable_amt_funcl_curr tax_base_amount,
                NVL (zl.tax_currency_code, zl.trx_currency_code) functional_currency,
                tax_amt_funcl_curr tax_amount_func,
                xle.accounting_status, xle.tgl GL_Transfer_Status
           FROM ap_invoices_all aia,
                ap_invoice_lines_all ail,
                poz_suppliers_v psv,
                poz_supplier_sites_v pss,
                zx_lines zl,
                xle_entity_profiles hou,
                (SELECT CASE
                           WHEN xae.gl_transfer_date IS NULL
                              THEN 'Not Transferred'
                           ELSE 'Transferred'
                        END tgl,
                        xte.source_id_int_1,
                        CASE
                           WHEN xae.accounting_date IS NULL
                              THEN 'Draft'
                           ELSE 'Accounted'
                        END accounting_status
                   FROM xla_transaction_entities xte, xla_ae_headers xae
                  WHERE xte.entity_id = xae.entity_id) xle
          WHERE aia.invoice_id = ail.invoice_id
            AND aia.vendor_id = psv.vendor_id
            AND aia.vendor_site_id = pss.vendor_site_id
            AND ail.invoice_id = zl.trx_id(+)
            AND ail.line_number = zl.trx_line_number(+)
            AND hou.legal_entity_id(+) = aia.legal_entity_id           
            AND zl.cancel_flag <> 'Y'
            AND aia.cancelled_date IS NULL
UNION
SELECT DISTINCT rct.trx_date transaction_date,
                rct.trx_number transaction_number, hou.NAME company_name,               
                zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
                (SELECT meaning
                   FROM fnd_lookup_values_vl
                  WHERE lookup_code = zl.event_class_code
                    AND lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
                    AND enabled_flag = 'Y') transaction_class,               
                gl_date accounting_date,
                hca.account_name third_party,
                NVL (hps.party_site_name, hps.party_site_number) site,
                zl.tax_amt tax_amount, zl.trx_currency_code entered_curr,
                zl.taxable_amt_funcl_curr tax_base_amount,
                NVL (zl.tax_currency_code, zl.trx_currency_code ) functional_currency,
                tax_amt_funcl_curr tax_amount_func,
                xle.accounting_status, xle.tgl GL_Transfer_Status
           FROM ra_customer_trx_all rct,
                ra_customer_trx_lines_all rctl,
                zx_lines zl,
                xle_entity_profiles hou,
                hz_cust_site_uses_all hcsua,
                hz_cust_acct_sites_all hcasa,
                hz_cust_accounts hca,
                hz_party_sites hps,
                (SELECT DISTINCT customer_trx_line_id, gl_date,
                                 customer_trx_id
                            FROM ra_cust_trx_line_gl_dist_all) dist_tab,
                (SELECT CASE
                           WHEN xae.gl_transfer_date IS NULL
                              THEN 'Not Transferred'
                           ELSE 'Transferred'
                        END tgl,
                        xte.source_id_int_1,
                        CASE
                           WHEN xae.accounting_date IS NULL
                              THEN 'Draft'
                           ELSE 'Accounted'
                        END accounting_status
                   FROM xla_transaction_entities xte, xla_ae_headers xae
                  WHERE xte.entity_id = xae.entity_id) xle
          WHERE rct.customer_trx_id = rctl.customer_trx_id
            AND rct.customer_trx_id = zl.trx_id(+)
            AND rctl.line_number = zl.trx_line_number(+)
            AND hou.legal_entity_id(+) = rct.legal_entity_id
            AND zl.entity_code = 'TRANSACTIONS'
            AND zl.cancel_flag <> 'Y'
            AND rctl.line_type = 'LINE'
            AND hcsua.site_use_id = rct.bill_to_site_use_id
            AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
            AND hcasa.cust_account_id = hca.cust_account_id
            AND hps.party_site_id = hcasa.party_site_id
            AND dist_tab.customer_trx_id = rct.customer_trx_id
            AND dist_tab.customer_trx_line_id = rctl.customer_trx_line_id
            

2 comments: