[Home] [Help]
206: AND claim_class = 'DEDUCTION'
207: AND settled_date >= ADD_MONTHS(sysdate,(-p_period))
208: AND cust_account_id IN (
209: select cust_account_id
210: FROM hz_cust_accounts
211: WHERE party_id in
212: ( SELECT child_id
213: from hz_hierarchy_nodes
214: where parent_object_type = 'ORGANIZATION'
428: largest_inv_cust_trx_id desc) rank_amount
429: FROM AR_TRX_SUMMARY
430: where cust_account_id in (
431: select cust_account_id
432: FROM hz_cust_accounts
433: WHERE party_id in
434: ( SELECT child_id
435: from hz_hierarchy_nodes
436: where parent_object_type = ''ORGANIZATION''
510: largest_inv_cust_trx_id desc) high_amount
511: FROM AR_TRX_SUMMARY
512: where cust_account_id in (
513: select cust_account_id
514: FROM hz_cust_accounts
515: WHERE party_id in
516: ( SELECT child_id
517: from hz_hierarchy_nodes
518: where parent_object_type = ''ORGANIZATION''
699: WHERE op_bal_high_watermark IS NOT NULL
700: AND as_of_date >= ADD_MONTHS(sysdate,(-p_period))
701: AND cust_account_id IN (
702: SELECT cust_account_id
703: FROM hz_cust_accounts
704: WHERE party_id in
705: ( SELECT child_id
706: FROM hz_hierarchy_nodes
707: WHERE parent_object_type = 'ORGANIZATION'
823: INTO l_last_payment_amount, l_last_payment_date,
824: l_last_payment_number, l_last_payment_currency
825: FROM AR_TRX_BAL_SUMMARY
826: WHERE cust_account_id in (select cust_account_id
827: FROM hz_cust_accounts
828: WHERE party_id in
829: ( SELECT child_id
830: from hz_hierarchy_nodes
831: where parent_object_type = 'ORGANIZATION'
856: and last_payment_date = ( select max(last_payment_date) from
857: ar_trx_bal_summary
858: where cust_account_id in
859: (select cust_account_id
860: FROM hz_cust_accounts
861: WHERE party_id in
862: ( SELECT child_id
863: from hz_hierarchy_nodes
864: where parent_object_type = 'ORGANIZATION'
1074: FROM ar_correspondences_all
1075: WHERE correspondence_type = 'DUNNING'
1076: AND customer_id in (
1077: select cust_account_id
1078: FROM hz_cust_accounts
1079: WHERE party_id in
1080: ( SELECT child_id
1081: from hz_hierarchy_nodes
1082: where parent_object_type = 'ORGANIZATION'
4786: SUM(TOTAL_DEPOSITS_VALUE)),2) total_deposits_value,
4787: SUM(TOTAL_deposits_COUNT) TOTAL_deposits_count
4788: FROM AR_TRX_SUMMARY
4789: WHERE CUST_ACCOUNT_ID in (select cust_account_id
4790: FROM hz_cust_accounts
4791: WHERE party_id in
4792: ( SELECT child_id
4793: from hz_hierarchy_nodes
4794: where parent_object_type = 'ORGANIZATION'
5188: SUM(nvl(op_invoices_value,0) - nvl(past_due_inv_value,0))) current_invoice_value,
5189: SUM(nvl(op_invoices_count,0) - nvl(past_due_inv_inst_count,0)) current_invoice_count
5190: FROM AR_TRX_BAL_SUMMARY
5191: WHERE cust_account_id in (select cust_account_id
5192: FROM hz_cust_accounts
5193: WHERE party_id in
5194: ( SELECT child_id
5195: from hz_hierarchy_nodes
5196: where parent_object_type = 'ORGANIZATION'
5442: nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0)
5443: - nvl(BEST_CURRENT_RECEIVABLES,0))*l_certified_dso_days)) delinquent_dso
5444: FROM ar_trx_bal_summary
5445: WHERE cust_account_id in (select cust_account_id
5446: FROM hz_cust_accounts
5447: WHERE party_id in
5448: ( SELECT child_id
5449: from hz_hierarchy_nodes
5450: where parent_object_type = 'ORGANIZATION'
5546: nvl(TOTAL_ADJUSTMENTS_VALUE,0)
5547: )))) delinquent_dso
5548: FROM ar_trx_summary
5549: WHERE cust_account_id in (select cust_account_id
5550: FROM hz_cust_accounts
5551: WHERE party_id in
5552: ( SELECT child_id
5553: from hz_hierarchy_nodes
5554: where parent_object_type = 'ORGANIZATION'