Query To Find The Outstanding Amount On A Customer Account

Skip to main content

Query to find the Outstanding Amount on a Customer Account

June 21, 2017

• Get link
• Facebook
• X
• Pinterest
• Email
• Other Apps

SELECT pty.party_number AS party_number, pty.party_name AS customer_name, acct.account_number AS account_number, acct.account_name AS account_name, SUM(ps.acctd_amount_due_remaining) AS amount_due FROM ar_payment_schedules_all ps, hz_party_sites site, hz_cust_acct_sites_all asite, hz_cust_accounts acct, hz_cust_site_uses_all uses, hz_parties pty, ra_customer_trx_all trx, hz_locations loc, ra_terms_lines_discounts dsc, ra_cust_trx_types_all rtype, ra_terms rt, ra_customer_trx_lines_all rl, fun_all_business_units_v org, fnd_vs_values_b val1, fnd_vs_values_tl val2 WHERE ps.acctd_amount_due_remaining <> 0 AND acct.cust_account_id = ps.customer_id AND acct.party_id = site.party_id AND asite.party_site_id = site.party_site_id AND acct.cust_account_id = asite.cust_account_id AND asite.cust_acct_site_id = uses.cust_acct_site_id AND uses.site_use_id = ps.customer_site_use_id AND pty.party_id = acct.party_id AND trx.customer_trx_id = ps.customer_trx_id AND loc.location_id = site.location_id AND dsc.term_id( + ) = ps.term_id AND ps.terms_sequence_number = dsc.sequence_num( + ) AND rtype.cust_trx_type_seq_id = trx.cust_trx_type_seq_id AND rt.term_id( + ) = ps.term_id AND rl.customer_trx_id = trx.customer_trx_id AND ps.org_id = org.bu_id AND val1.VALUE = site.attribute3 AND val1.value_id = val2.value_id AND val2.language = 'US' AND NOT EXISTS ( SELECT hca1.cust_account_id FROM ar_cash_receipts_all acra, hz_cust_accounts hca1, ar_receipt_methods arm WHERE 1 = 1 AND acct.cust_account_id = hca1.cust_account_id AND hca1.cust_account_id = acra.pay_from_customer AND arm.receipt_method_id = acra.receipt_method_id AND acra.receipt_date BETWEEN (sysdate - 30) AND sysdate ) GROUP BY pty.party_name, acct.account_number, acct.account_name, pty.party_number HAVING SUM(ps.acctd_amount_due_remaining) > 0 ORDER BY 5 DESC

• Get link
• Facebook
• X
• Pinterest
• Email
• Other Apps

Comments

Post a Comment

Popular Posts

Query to check whether a Supplier-Site-Bank can make an International Payment

SELECT    hp.party_name AS supplier_name,    ps.segment1 AS supplier_number,    pssam.vendor_site_code as site,    ieba.bank_account_num as bank_acc_no,    decode(LOWER(ieba.foreign_payment_use_flag), 'y', 'Allow International Payments', 'n', 'Don''t allow International Payments') AS international_payments,    CASE       WHEN          ipiua.end_date LIKE '4712-12-31%'       THEN          'Active'       ELSE          'Inactive'    END AS stat,    decode(LOWER(ipiua.primary_flag), 'y', 'Primary Bank Account', 'Non-Primary Bank Account') AS bank_type,    ieba.last_updated_by,    ieba.last_update_date || '  ' || 'UTC' AS last_update_date FROM    poz_suppliers ps,    hz_parties hp,    poz_supplier_sites_all_m pssam, ...

Query to obtain latest Note created for a Customer - Account

SELECT    hca.account_name,    round(hca.account_number) AS account_number,    initcap(substr(zn.created_by, 1, instr(zn.created_by, '.') - 1)) || ' ' || initcap(substr(zn.created_by, instr(zn.created_by, '.') + 1)) created_by,    to_clob(zn.note_txt) AS note_txt,    initcap(substr(zn.last_updated_by, 1, instr(zn.last_updated_by, '.') - 1)) || ' ' || initcap(substr(zn.last_updated_by, instr(zn.last_updated_by, '.') + 1)) last_updated_by,    to_char(zn.last_update_date, 'MM/DD/YY') AS created_on FROM    hz_cust_accounts hca,    hz_parties hp,    zmm_notes zn WHERE    hp.party_id = hca.party_id    AND hca.cust_account_id = zn.source_object_uid    AND zn.source_object_code = 'CUSTOMER_ACCOUNT'    AND zn.last_update_date =    (       SELECT          MAX(zn1.last_update_date)     ...