Query for AP withholding Tax details

Query for AP withholding Tax details

AP_AWT_TAX_RATES

SELECT DISTINCT
  awt.*
FROM
  ap_tax_codes_all atc,
  ap_awt_tax_rates_all awt
WHERE
  awt.tax_name  = atc.name
AND atc.tax_id IN
  (
    SELECT DISTINCT
      aid.tax_code_id tax_code_id
    FROM
      ap_invoice_distributions_all aid,
      ap_tax_codes_all atc
    WHERE
      aid.tax_code_id    = atc.tax_id
    AND aid.tax_code_id IS NOT NULL
    AND atc.tax_type     = 'AWT'
    AND aid.invoice_id   = :P_INVOICE_ID
  )
UNION
SELECT DISTINCT
  awt.*
FROM
  ap_awt_tax_rates_all awt
WHERE
  awt.tax_rate_id IN
  (
    SELECT DISTINCT
      aid.awt_tax_rate_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.awt_tax_rate_id IS NOT NULL
    AND aid.invoice_id     = :P_INVOICE_ID
  );


AP_AWT_GROUPS

SELECT DISTINCT
  awt.*
FROM
  ap_awt_groups awt
WHERE
  awt.group_id IN
  (
    SELECT DISTINCT
      aid.awt_group_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.awt_group_id IS NOT NULL
    AND aid.invoice_id  = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      aid.pay_awt_group_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.pay_awt_group_id IS NOT NULL
    AND aid.invoice_id      = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      ai.awt_group_id
    FROM
      ap_invoices_all ai
    WHERE
      ai.awt_group_id IS NOT NULL
    AND ai.invoice_id  = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      ai.pay_awt_group_id
    FROM
      ap_invoices_all ai
    WHERE
      ai.pay_awt_group_id IS NOT NULL
    AND ai.invoice_id      = :P_INVOICE_ID
  );


AP_AWT_GROUP_TAXES_ALL

SELECT DISTINCT
  awt.*
FROM
  AP_AWT_GROUP_TAXES_ALL awt
WHERE
  awt.group_id IN
  (
    SELECT DISTINCT
      aid.awt_group_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.awt_group_id IS NOT NULL
    AND aid.invoice_id  = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      aid.pay_awt_group_id
    FROM
      ap_invoice_distributions_all aid
    WHERE
      aid.pay_awt_group_id IS NOT NULL
    AND aid.invoice_id      = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      ai.awt_group_id
    FROM
      ap_invoices_all ai
    WHERE
      ai.awt_group_id IS NOT NULL
    AND ai.invoice_id  = :P_INVOICE_ID
    UNION
    SELECT DISTINCT
      ai.pay_awt_group_id
    FROM
      ap_invoices_all ai
    WHERE
      ai.pay_awt_group_id IS NOT NULL
    AND ai.invoice_id      = :P_INVOICE_ID
  );


AP_AWT_TEMP_DISTRIBUTIONS_ALL


SELECT
  *
FROM
  ap_awt_temp_distributions_all
WHERE
  invoice_id = :P_INVOICE_ID;