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!

Invoices with bank accounts set up without international payment flag set to Y

Discussion in 'Oracle Financials' started by dude_me5, Apr 18, 2012.

  1. dude_me5

    dude_me5 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Need alert for invoices with bank accounts where bank account is not set up with this flag.

    I tried the following query :

    SELECT TO_CHAR
    (
    SYSDATE
    , 'DD-MON-YYYY'
    )
    , ai.invoice_num
    , acct.bank_account_num bank_account_num
    , asup.vendor_name
    , apsa.vendor_site_code
    , DECODE
    (
    UPPER (acct.bank_account_type)
    , 'CHECKING', 'C'
    , 'SAVINGS', 'S'
    , 'C'
    )
    bank_account_type
    , NVL (acct.bank_account_name_alt, acct.bank_account_name)
    bank_account_name
    , NVL (acct.attribute1, 'N') account_token_flag
    -- , brpr.bank_or_branch_number bank_num
    , ai.org_id
    , acct.FOREIGN_PAYMENT_USE_FLAG
    /*INTO
    &V_DATE
    ,&invoice_num
    ,&bank_account_num
    ,&vendor_name
    ,&vendor_site_code
    ,&bank_account_type
    ,&bank_account_name
    ,&account_token_flag
    ,&bank_branch_num
    ,&org_id*/
    FROM ap_invoices_all ai
    , ap_payment_schedules_all aps
    , ap_suppliers asup
    , ap_supplier_sites_all apsa
    , iby_external_payees_all payee
    , iby_ext_bank_accounts acct
    -- , hz_organization_profiles brpr
    ,hz_code_assignments hca
    WHERE ai.cancelled_date IS NULL
    AND ai.payment_status_flag = 'N'
    AND aps.invoice_id = ai.invoice_id
    and ai.vendor_id = asup.vendor_id
    AND ai.vendor_site_id = apsa.vendor_site_id
    AND payee.supplier_site_id = apsa.vendor_site_id
    AND aps.external_bank_account_id = acct.ext_bank_account_id
    -- AND acct.branch_id = brpr.party_id(+)
    -- AND brpr.effective_end_date IS NULL
    -- AND acct.branch_id IS NULL
    AND hca.owner_table_id =acct.branch_id
    AND NVL(ai.payment_method_code, ai.Payment_method_lookup_code) = 'EFT'
    AND acct.FOREIGN_PAYMENT_USE_FLAG<>'Y';

    Can anyone please let me know if it is correct or do I have to add anymore joins. :)
     
  2. dude_me5

    dude_me5 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    And I am in R12 :)
     
  3. dude_me5

    dude_me5 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    @Everybody

    Please check this :(