1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Equivalent SQL Query from R11 to R12

Discussion in 'General' started by xhyrruz, Jun 23, 2010.

  1. xhyrruz

    xhyrruz Guest

    Hi Everyone,

    Anyone help me to convert the query below from R11 to R12. Some tables in R11 were changed in R12.

    SQL query 1:
    Code (SQL):

    SELECT a.check_id, a.bank_account_id,UPPER(nvl(a.attribute3,a.vendor_name)) cv_pay_to,
     to_char(a.creation_date,'FMMONTH DD, YYYY') cv_date,
    a.doc_sequence_value cv_number,
    '*'||ltrim(to_char(a.amount,'999,999,990.90')) ||'*' cv_net_amount,
    '**'||apps.kcc_amt_to_words(a.amount,a.currency_code) ||'**' cv_pesos,
    a.current_bank_account_name cv_bank_name,
    a.check_number cv_check_number,
    CASE WHEN a.future_pay_due_date IS NULL THEN
         a.check_date
         ELSE
         a.future_pay_due_date
    END cv_check_date,
    b.invoice_num, b.invoice_date, b.description invoice_desc
    FROM apps.ap_checks_v a, apps.ap_invoice_payments_v b
    WHERE a.bank_account_id = :p_bank_acctno
    AND a.status_lookup_code <> 'VOIDED'
    AND a.check_number BETWEEN :p_chk_number AND  :p_to_chk_number
    AND a.check_id=b.check_id(+)
     
    SQL query 2:
    Code (SQL):

    SELECT check_id, account, description, SUM(debit) debit, SUM(credit) credit
    FROM
    (SELECT a.check_id,
           c.segment2||'-'||c.segment3 account,
           d.description,
           a.amount debit,
           NULL credit
    FROM apps.ap_invoice_payments_v a, apps.ap_invoices_v b, apps.gl_code_combinations c, apps.fnd_flex_values_vl d
    WHERE b.invoice_id = a.invoice_id
    AND c.code_combination_id = b.accts_pay_code_combination_id
    AND d.flex_value = c.segment2
    AND d.flex_value_set_id = 1007699
    UNION ALL
    SELECT a.check_id,
           d.segment2||'-'||d.segment3 account,
           e.description,
           NULL debit,
           b.amount_withheld credit
    FROM apps.ap_invoice_payments_v a, apps.ap_invoices_v b, apps.ap_tax_codes c, apps.gl_code_combinations d, apps.fnd_flex_values_vl e
    WHERE a.invoice_id = b.invoice_id
    AND c.name = b.awt_group_name
    AND d.code_combination_id = c.tax_code_combination_id
    AND e.flex_value = d.segment2
    AND e.flex_value_set_id = 1007699
    UNION ALL
    SELECT a.check_id,
           d.segment2||'-'||d.segment3 account,
           e.description,
           NULL debit, c.amount credit  
    FROM apps.ap_checks_v a, apps.ap_bank_accounts b, apps.ap_invoice_payments c, apps.gl_code_combinations d, apps.fnd_flex_values_vl e
    WHERE b.bank_account_id = a.bank_account_id
    AND c.check_id = a.check_id
    AND d.code_combination_id = b.cash_clearing_ccid
    AND e.flex_value = d.segment2
    AND e.flex_value_set_id = 1007699)
    GROUP BY check_id, description, account
    ORDER BY debit, account, description
     
    Any help appreciated.

    Thanks.
    Cyrus