[Home] [Help]
50: --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
51: -- ' AND status <> ''CURRENT'' ';
52: ' AND status NOT IN (''CURRENT'', ''CLOSE'') ';
53: --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
54: l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt, hz_cust_accounts acc ' ||
55: ' WHERE bnkrpt.party_id = acc.party_id AND acc.cust_account_id = :cust_account_id';
56: l_object_id := p_cust_account_id;
57: ELSIF p_site_use_id > 0 THEN
58: l_del_clause := 'SELECT COUNT(1) FROM iex_delinquencies WHERE customer_site_use_id = :site_use_id ' ||
59: --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
60: -- ' AND status <> ''CURRENT'' ';
61: ' AND status NOT IN (''CURRENT'', ''CLOSE'') ';
62: --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
63: l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt,hz_cust_site_uses site_use, hz_cust_acct_sites acct_site, hz_cust_accounts acc ' ||
64: ' WHERE bnkrpt.party_id = acc.party_id ' ||
65: ' AND acct_site.cust_account_id = acc.cust_account_id ' ||
66: ' AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
67: ' AND site_use.site_use_id = :site_use_id';
129: IF p_party_id > 0 THEN
130: l_pro_clause := 'SELECT COUNT(1) ' ||
131: 'FROM (' ||
132: ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
133: ' FROM iex_promise_details pro, hz_cust_accounts ca, iex_delinquencies_all del ' ||
134: ' WHERE ca.cust_account_id = pro.cust_account_id ' ||
135: ' AND pro.state = ''BROKEN_PROMISE'' ' ||
136: ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
137: ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
147: ' AND ca.party_id = :party_id ' ||
148: ' AND del.org_id = :org_id ' ||
149: ' UNION ALL ' ||
150: ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
151: ' FROM iex_promise_details pro, hz_cust_accounts ca ' ||
152: ' WHERE ca.cust_account_id = pro.cust_account_id ' ||
153: ' AND pro.state = ''BROKEN_PROMISE'' ' ||
154: ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
155: ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
191: --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
192: ' AND pro.cust_account_id = :cust_account_id ' ||
193: ' AND pro.delinquency_id is null ' ||
194: ')';
195: l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt, hz_cust_accounts acc ' ||
196: ' WHERE bnkrpt.party_id = acc.party_id AND acc.cust_account_id = :cust_account_id';
197: l_object_id := p_cust_account_id;
198: ELSIF p_site_use_id > 0 THEN
199: l_pro_clause := 'SELECT COUNT(1) ' ||
224: ' AND pro.contract_id = okch.id ' ||
225: ' AND okch.bill_to_site_use_id = :site_use_id ' ||
226: --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise
227: ')';
228: l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt,hz_cust_site_uses site_use, hz_cust_acct_sites acct_site, hz_cust_accounts acc ' ||
229: ' WHERE bnkrpt.party_id = acc.party_id ' ||
230: ' AND acct_site.cust_account_id = acc.cust_account_id ' ||
231: ' AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
232: ' AND site_use.site_use_id = :site_use_id';
333: 'AND str.status_code in (''OPEN'', ''ONHOLD'') ' ||
334: 'AND wtm.status_code in (''OPEN'', ''ONHOLD'') ' ||
335: 'AND str.jtf_object_type = ''IEX_ACCOUNT'' ' ||
336: 'AND str.jtf_object_id = :cust_account_id ';
337: l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt, hz_cust_accounts acc ' ||
338: ' WHERE bnkrpt.party_id = acc.party_id AND acc.cust_account_id = :cust_account_id';
339: l_object_id := p_cust_account_id;
340: ELSIF p_site_use_id > 0 THEN
341: l_str_clause := 'SELECT COUNT(1) FROM iex_strategy_work_items wtm, iex_strategies str ' ||
344: 'AND str.status_code in (''OPEN'', ''ONHOLD'') ' ||
345: 'AND wtm.status_code in (''OPEN'', ''ONHOLD'') ' ||
346: 'AND str.jtf_object_type = ''IEX_BILLTO'' ' ||
347: 'AND str.jtf_object_id = :site_use_id ';
348: l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt,hz_cust_site_uses site_use, hz_cust_acct_sites acct_site, hz_cust_accounts acc ' ||
349: ' WHERE bnkrpt.party_id = acc.party_id ' ||
350: ' AND acct_site.cust_account_id = acc.cust_account_id ' ||
351: ' AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
352: ' AND site_use.site_use_id = :site_use_id';
439: IF p_party_id > 0 THEN
440: --Start bug 7684877 gnramasa 5th Jan 08
441: /*
442: l_amt_clause := ' SELECT o_summ.last_payment_amount, o_summ.currency ' ||
443: ' FROM ar_trx_bal_summary o_summ, hz_cust_accounts acc' ||
444: ' WHERE o_summ.cust_account_id = acc.cust_account_id' ||
445: ' AND acc.party_id = :party_id ' ||
446: ' AND last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
447: ' FROM ar_trx_bal_summary ' ||
455: ' last_payment_date, ' ||
456: ' MAX(last_payment_date) over(PARTITION BY cust_account_id) max_pay ' ||
457: ' FROM ar_trx_bal_summary o_summ) ' ||
458: ' o_summ, ' ||
459: ' hz_cust_accounts acc ' ||
460: ' WHERE o_summ.cust_account_id = acc.cust_account_id ' ||
461: ' AND acc.party_id = :party_id ' ||
462: ' AND o_summ.max_pay = o_summ.last_payment_date ';
463: --End bug 7684877 gnramasa 5th Jan 08
507: IF p_party_id > 0 THEN
508: --Start bug 7684877 gnramasa 5th Jan 08
509: /*
510: l_no_clause := ' SELECT o_summ.last_payment_number ' ||
511: ' FROM ar_trx_bal_summary o_summ, hz_cust_accounts acc' ||
512: ' WHERE o_summ.cust_account_id = acc.cust_account_id' ||
513: ' AND acc.party_id = :party_id ' ||
514: ' AND last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
515: ' FROM ar_trx_bal_summary ' ||
522: ' last_payment_date, ' ||
523: ' MAX(last_payment_date) over(PARTITION BY cust_account_id) max_pay ' ||
524: ' FROM ar_trx_bal_summary o_summ) ' ||
525: ' o_summ, ' ||
526: ' hz_cust_accounts acc ' ||
527: ' WHERE o_summ.cust_account_id = acc.cust_account_id ' ||
528: ' AND acc.party_id = :party_id ' ||
529: ' AND o_summ.max_pay = o_summ.last_payment_date ';
530: --End bug 7684877 gnramasa 5th Jan 08
596: IS
597: CURSOR c_party_amt IS
598: --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
599: -- SELECT SUM(amount_due_remaining)
600: -- FROM iex_promise_details pro, hz_cust_accounts ca, iex_delinquencies del
601: -- WHERE pro.cust_account_id = ca.cust_account_id
602: -- AND ca.party_id = p_party_id
603: -- AND pro.status in ('COLLECTABLE', 'PENDING')
604: -- AND pro.state = 'BROKEN_PROMISE'
618: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
619: AND del.org_id = p_org_id
620: UNION ALL
621: SELECT amount_due_remaining
622: FROM iex_promise_details pro, okc_k_headers_b okch, hz_cust_accounts ca
623: WHERE ca.cust_account_id = pro.cust_account_id
624: AND ca.party_id = p_party_id
625: AND pro.status in ('COLLECTABLE', 'PENDING')
626: AND pro.state = 'BROKEN_PROMISE'
705: IS
706: CURSOR c_party_amt IS
707: --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
708: -- SELECT SUM(promise_amount)
709: -- FROM iex_promise_details pro, hz_cust_accounts ca, iex_delinquencies del
710: -- WHERE pro.cust_account_id = ca.cust_account_id
711: -- AND ca.party_id = p_party_id
712: -- AND pro.status in ('COLLECTABLE', 'PENDING')
713: -- AND pro.state = 'BROKEN_PROMISE'
729: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
730: AND del.org_id = p_org_id
731: UNION ALL
732: SELECT promise_amount
733: FROM iex_promise_details pro, okc_k_headers_b okch, hz_cust_accounts ca
734: WHERE ca.party_id = p_party_id
735: AND pro.cust_account_id = ca.cust_account_id
736: AND pro.status in ('COLLECTABLE', 'PENDING')
737: AND pro.state = 'BROKEN_PROMISE'