APPS.ZX_TCM_GET_EXEMPT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 19

     select p.start_date, p.end_date
     from gl_period_statuses p, gl_sets_of_books g
    where p.application_id = 222
      and p.set_of_books_id = p_ledger_id
      and trunc(p_tax_date) between p.start_date and p.end_date
      and g.set_of_books_id = p.set_of_books_id
      and g.accounted_period_type = p.period_type;

Line: 86

    SELECT tax_exemption_id, exemption_type_code, rate_modifier, apply_to_lower_levels_flag,
           decode(product_id, null, decode(site_use_id, null, decode(cust_account_id, null,4,3),2), 1) select_order1,
            decode(exemption_status_code,'PRIMARY',1,'MANUAL',2,'UNAPPROVED',3) select_order2,
            tax_rate_code, tax_jurisdiction_id, tax_status_code, tax, exempt_reason_code, exempt_certificate_number
    FROM zx_exemptions
    WHERE party_tax_profile_id = p_ptp_id
    AND ((p_exempt_control_flag = 'S' and exemption_status_code = 'PRIMARY' )
             OR ( p_exempt_control_flag = 'E'
                  AND exemption_status_code IN ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' )
                  AND exempt_reason_code = p_reason_code
                  AND ( (rtrim(ltrim(exempt_certificate_number)) = p_exempt_certificate_number)
                      or (exempt_certificate_number IS NULL AND
                          p_exempt_certificate_number IS NULL))   ))
    AND duplicate_exemption = 0
    AND tax_regime_code = p_tax_regime_code
    AND (cust_account_id is null or cust_account_id = p_cust_account_id)
    AND (site_use_id is null or site_use_id = p_site_use_id)
    AND (tax is null or tax = p_tax)
    AND (tax_status_code is null or tax_status_code = p_tax_status_code)
    AND (tax_rate_code is null or tax_rate_code = p_tax_rate_code)
    AND (tax_jurisdiction_id is null or tax_jurisdiction_id = p_tax_jurisdiction_id)
    AND (product_id is null or product_id = p_inventory_item_id)
    AND effective_from <= p_tax_date
    AND (effective_to >= p_tax_date or effective_to is null)
order by select_order2,
         select_order1,
         tax_rate_code NULLS LAST, tax_jurisdiction_id NULLS LAST, tax_status_code NULLS LAST, tax NULLS LAST;

Line: 131

    SELECT tax_exemption_id, exemption_type_code, rate_modifier, apply_to_lower_levels_flag,
           decode(product_id, null, decode(site_use_id, null, decode(cust_account_id, null,4,3),2), 1) select_order1,
            decode(exemption_status_code,'PRIMARY',1,'MANUAL',2,'UNAPPROVED',3) select_order2,
            tax_rate_code, tax_jurisdiction_id, tax_status_code, tax, exempt_reason_code, exempt_certificate_number
    FROM zx_exemptions
     WHERE party_tax_profile_id = p_ptp_id
     AND  exemption_status_code = 'PRIMARY'
     AND duplicate_exemption = 0
     AND tax_regime_code = p_tax_regime_code
     AND (cust_account_id is null or cust_account_id = p_cust_account_id)
     AND (site_use_id is null or site_use_id = p_site_use_id)
     AND (tax is null or tax = p_tax)
     AND (tax_status_code is null or tax_status_code = p_tax_status_code)
     AND (tax_rate_code is null or tax_rate_code = p_tax_rate_code)
     AND (tax_jurisdiction_id is null or tax_jurisdiction_id = p_tax_jurisdiction_id)
     AND (product_id is null or product_id = p_inventory_item_id)
     AND effective_from <= p_tax_date
     AND (effective_to >= p_tax_date or effective_to is null)
     AND content_owner_id = nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)
     order by select_order1, tax_rate_code NULLS LAST, tax_jurisdiction_id NULLS LAST, tax_status_code NULLS LAST, tax NULLS LAST;

Line: 166

    SELECT tax_exemption_id, exemption_type_code, rate_modifier, apply_to_lower_levels_flag,
           decode(product_id, null, decode(site_use_id, null, decode(cust_account_id, null,4,3),2), 1) select_order1,
            decode(exemption_status_code,'PRIMARY',1,'MANUAL',2,'UNAPPROVED',3) select_order2,
            tax_rate_code, tax_jurisdiction_id, tax_status_code, tax, exempt_reason_code, exempt_certificate_number
    FROM zx_exemptions
     WHERE party_tax_profile_id = p_ptp_id
     AND  exempt_certificate_number IS NULL
     AND duplicate_exemption = 0
     AND tax_regime_code = p_tax_regime_code
     AND (cust_account_id is null or cust_account_id = p_cust_account_id)
     AND (site_use_id is null or site_use_id = p_site_use_id)
     AND (tax is null or tax = p_tax)
     AND (tax_status_code is null or tax_status_code = p_tax_status_code)
     AND (tax_rate_code is null or tax_rate_code = p_tax_rate_code)
     AND (tax_jurisdiction_id is null or tax_jurisdiction_id = p_tax_jurisdiction_id)
     AND (product_id is null or product_id = p_inventory_item_id)
     AND effective_from <= p_tax_date
     AND (effective_to >= p_tax_date or effective_to is null)
     AND content_owner_id = nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)
 order by select_order2,
          select_order1,
          tax_rate_code NULLS LAST, tax_jurisdiction_id NULLS LAST,
         tax_status_code NULLS LAST, tax NULLS LAST;

Line: 204

    SELECT tax_exemption_id, exemption_type_code, rate_modifier, apply_to_lower_levels_flag,
           decode(product_id, null, decode(site_use_id, null, decode(cust_account_id, null,4,3),2), 1) select_order1,
            decode(exemption_status_code,'PRIMARY',1,'MANUAL',2,'UNAPPROVED',3) select_order2,
            tax_rate_code, tax_jurisdiction_id, tax_status_code, tax, exempt_reason_code, exempt_certificate_number
    FROM zx_exemptions
     WHERE party_tax_profile_id = p_ptp_id
     AND exemption_status_code IN ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' )
     AND exempt_reason_code = p_reason_code
     AND rtrim(ltrim(exempt_certificate_number)) = p_exempt_certificate_number
     AND duplicate_exemption = 0
     AND tax_regime_code = p_tax_regime_code
     AND (cust_account_id is null or cust_account_id = p_cust_account_id)
     AND (site_use_id is null or site_use_id = p_site_use_id)
     AND (tax is null or tax = p_tax)
     AND (tax_status_code is null or tax_status_code = p_tax_status_code)
     AND (tax_rate_code is null or tax_rate_code = p_tax_rate_code)
     AND (tax_jurisdiction_id is null or tax_jurisdiction_id =  p_tax_jurisdiction_id)
     AND (product_id is null or product_id = p_inventory_item_id)
     AND effective_from <= p_tax_date
     AND (effective_to >= p_tax_date or effective_to is null)
     AND content_owner_id = nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)
 order by select_order2,
          select_order1,
          tax_rate_code NULLS LAST, tax_jurisdiction_id NULLS LAST,
          tax_status_code NULLS LAST, tax NULLS LAST;

Line: 291

           SELECT meaning
           INTO x_exemption_rec.exempt_reason
           from FND_LOOKUPS
           where  lookup_type = 'ZX_EXEMPTION_REASON_CODE'
           and    lookup_code = x_exemption_rec.exempt_reason_code;

Line: 352

           SELECT meaning
           INTO x_exemption_rec.exempt_reason
           from FND_LOOKUPS
           where  lookup_type = 'ZX_EXEMPTION_REASON_CODE'
           and    lookup_code = x_exemption_rec.exempt_reason_code;

Line: 412

           SELECT meaning
           INTO x_exemption_rec.exempt_reason
           from FND_LOOKUPS
           where  lookup_type = 'ZX_EXEMPTION_REASON_CODE'
           and    lookup_code = x_exemption_rec.exempt_reason_code;

Line: 467

    SELECT tax_exmpt_cr_method_code, tax_exmpt_source_tax
    INTO   l_tax_exmpt_cr_method_code, l_tax_exmpt_source_tax
    FROM   zx_sco_taxes
    WHERE  tax_regime_code = p_tax_regime_code
    AND    tax = p_tax;

Line: 483

            SELECT TAX_EXMPT_SRC_JURISDICT_ID
            INTO l_source_tax_jurisdiction_id
            FROM zx_jurisdictions_b
            WHERE tax_jurisdiction_id = p_tax_jurisdiction_id
            AND effective_from <= p_tax_date
            AND (effective_to >= p_tax_date or effective_to is null);

Line: 684

      SELECT tax_jurisdiction_id, precedence_level
      BULK COLLECT INTO l_tax_jurisdiction_id_tbl, l_precedence_level_tbl
      FROM zx_jurisdictions_gt
      ORDER BY precedence_level;

Line: 869

       SELECT set_of_books_id
       INTO l_ledger_id
       FROM ar_system_parameters_all
       WHERE org_id = zx_global_structures_pkg.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(1);

Line: 874

       SELECT set_of_books_id
       INTO l_ledger_id
       FROM ar_system_parameters_all
       WHERE org_id = p_event_class_rec.internal_organization_id;

Line: 895

     select 'Y' into l_exists
     from
     zx_exemptions
     where nvl(tax_rate_code,'X') = nvl(p_tax_rate_code,'X') and
     effective_from = nvl(l_start_date, trunc(sysdate))
     and nvl(exempt_certificate_number,'X') = nvl(p_exempt_certificate_number,'X')
     and exempt_reason_code = p_reason_code
     and party_tax_profile_id = p_bill_to_party_ptp_id
     and tax_regime_code = p_tax_regime_code
     and content_owner_id =
       nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)
     and nvl(tax_status_code,'X') = nvl(p_tax_status_code,'X')
      and nvl(tax,'X') = nvl(p_tax,'X')
     and nvl(tax_jurisdiction_id,-999) = nvl(l_tax_jurisdiction_id,-999)
     and exemption_status_code = 'UNAPPROVED';

Line: 929

  SELECT zx_exemptions_s.nextval
  INTO x_exemption_rec.exemption_id
  FROM dual;

Line: 953

  INSERT INTO ZX_EXEMPTIONS(
    TAX_EXEMPTION_ID,
    EXEMPTION_TYPE_CODE,
    EXEMPTION_STATUS_CODE,
    TAX_REGIME_CODE,
    TAX,
    TAX_STATUS_CODE,
    tax_rate_code,
    CONTENT_OWNER_ID,
    EXEMPT_CERTIFICATE_NUMBER,
    EXEMPT_REASON_CODE,
    EFFECTIVE_FROM,
    PARTY_TAX_PROFILE_ID,
    RATE_MODIFIER,
    APPLY_TO_LOWER_LEVELS_FLAG,
    TAX_JURISDICTION_ID,
    cust_account_id,
    site_use_id,
    RECORD_TYPE_CODE,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    REQUEST_ID,
    PROGRAM_APPLICATION_ID,
    PROGRAM_ID,
    --PROGRAM_LOGIN_ID,
    OBJECT_VERSION_NUMBER,
    duplicate_exemption)
  VALUES (
    x_exemption_rec.exemption_id,
    'DISCOUNT',
    'UNAPPROVED',
    p_TAX_REGIME_CODE,
    p_TAX,
    p_TAX_STATUS_CODE,
    p_tax_rate_code,
    nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID),
    p_exempt_certificate_number,
    p_REASON_CODE,
    nvl(l_start_date, trunc(sysdate)),
    p_bill_to_party_ptp_id,
    100,
    'Y',
    l_tax_jurisdiction_id,
    null,
    null,
    'USER_DEFINED',
    fnd_global.user_id,
    sysdate,
    fnd_global.user_id,
    sysdate,
    fnd_global.conc_login_id,
    null, -- request id
    null, -- PROGRAM_APPLICATION_ID,
    null, -- PROGRAM_ID,
    --PROGRAM_LOGIN_ID,
    1,
    0);