APPS.IEX_COLL_IND dependencies on GL_CURRENCY_API

Line 1476: --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,

1472: BEGIN
1473: l_conversion_type := NVL(FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), 'Corporate');
1474: IF p_party_id IS NOT NULL THEN
1475: -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1476: --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1477: SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1478: -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1479: -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1480: sysdate, l_conversion_type, prof_amt.overall_credit_limit)))

Line 1477: SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,

1473: l_conversion_type := NVL(FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), 'Corporate');
1474: IF p_party_id IS NOT NULL THEN
1475: -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1476: --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1477: SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1478: -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1479: -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1480: sysdate, l_conversion_type, prof_amt.overall_credit_limit)))
1481: INTO l_credit_limit

Line 1496: --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,

1492: AND prof_amt.site_use_id IS NULL;
1493:
1494: ELSIF p_cust_account_id IS NOT NULL THEN
1495: -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1496: --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1497: SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1498: -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1499: -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1500: sysdate, l_conversion_type, prof_amt.overall_credit_limit)))

Line 1497: SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,

1493:
1494: ELSIF p_cust_account_id IS NOT NULL THEN
1495: -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1496: --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1497: SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1498: -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1499: -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1500: sysdate, l_conversion_type, prof_amt.overall_credit_limit)))
1501: INTO l_credit_limit

Line 1512: --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,

1508: AND prof_amt.cust_account_id = p_cust_account_id
1509: AND prof_amt.site_use_id IS NULL;
1510: ELSIF p_customer_site_use_id IS NOT NULL THEN
1511: -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1512: --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1513: SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1514: -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1515: -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1516: sysdate, l_conversion_type, prof_amt.overall_credit_limit)))

Line 1513: SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,

1509: AND prof_amt.site_use_id IS NULL;
1510: ELSIF p_customer_site_use_id IS NOT NULL THEN
1511: -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1512: --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1513: SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1514: -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1515: -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1516: sysdate, l_conversion_type, prof_amt.overall_credit_limit)))
1517: INTO l_credit_limit

Line 1552: SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,

1548: l_conversion_type VARCHAR(30);
1549: BEGIN
1550: l_conversion_type := NVL(FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), 'Corporate');
1551: IF p_party_id IS NOT NULL THEN
1552: SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
1553: -- sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
1554: sysdate, l_conversion_type , trx_summ.op_bal_high_watermark))
1555: INTO l_high_credit_ytd
1556: FROM ar_trx_summary trx_summ,ar_system_parameters asp,--Added for Bug 9404646 09-Mar-2010 barathsr

Line 1563: SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,

1559: WHERE trx_summ.org_id=asp.org_id--Added for Bug 9404646 09-Mar-2010 barathsr
1560: AND trx_summ.cust_account_id = ca.cust_account_id
1561: AND ca.party_id = p_party_id;
1562: ELSIF p_cust_account_id IS NOT NULL THEN
1563: SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
1564: -- sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
1565: sysdate, l_conversion_type , trx_summ.op_bal_high_watermark))
1566: INTO l_high_credit_ytd
1567: FROM ar_trx_summary trx_summ,ar_system_parameters asp--Added for Bug 9404646 09-Mar-2010 barathsr

Line 1572: SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,

1568: -- ar_cmgt_setup_options cm_opt
1569: WHERE trx_summ.org_id=asp.org_id--Added for Bug 9404646 09-Mar-2010 barathsr
1570: AND trx_summ.cust_account_id = p_cust_account_id;
1571: ELSIF p_customer_site_use_id IS NOT NULL THEN
1572: SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
1573: -- sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
1574: sysdate, l_conversion_type , trx_summ.op_bal_high_watermark))
1575: INTO l_high_credit_ytd
1576: FROM ar_trx_summary trx_summ,ar_system_parameters asp--Added for Bug 9404646 09-Mar-2010 barathsr

Line 1603: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1599:
1600: begin
1601:
1602: IF p_party_id IS NOT NULL THEN
1603: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1604: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1605: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1606: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1607: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1605: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1601:
1602: IF p_party_id IS NOT NULL THEN
1603: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1604: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1605: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1606: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1607: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1608: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1609: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1607: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1603: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1604: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1605: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1606: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1607: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1608: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1609: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1610: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
1611: INTO l_amount_due_org

Line 1609: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1605: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1606: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1607: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1608: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1609: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1610: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
1611: INTO l_amount_due_org
1612: from gl_sets_of_books a,
1613: ar_system_parameters b,

Line 1622: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1618: AND c.cust_account_id = d.cust_account_id
1619: AND d.party_id = p_party_id
1620: and as_of_date BETWEEN p_start_date AND p_end_date;
1621: ELSE IF p_cust_account_id IS NOT NULL THEN
1622: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1623: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1624: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1625: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1626: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1624: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1620: and as_of_date BETWEEN p_start_date AND p_end_date;
1621: ELSE IF p_cust_account_id IS NOT NULL THEN
1622: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1623: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1624: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1625: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1626: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1627: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1628: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1626: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1622: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1623: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1624: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1625: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1626: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1627: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1628: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1629: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
1630: INTO l_amount_due_org

Line 1628: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1624: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1625: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1626: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1627: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1628: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1629: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
1630: INTO l_amount_due_org
1631: from gl_sets_of_books a,
1632: ar_system_parameters b,

Line 1639: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1635: and b.org_id = c.org_id
1636: and c.cust_account_id = p_cust_account_id
1637: and as_of_date BETWEEN p_start_date AND p_end_date;
1638: ELSE IF p_customer_site_use_id IS NOT NULL THEN
1639: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1640: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1641: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1642: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1643: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1641: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1637: and as_of_date BETWEEN p_start_date AND p_end_date;
1638: ELSE IF p_customer_site_use_id IS NOT NULL THEN
1639: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1640: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1641: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1642: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1643: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1644: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1645: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1643: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1639: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1640: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1641: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1642: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1643: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1644: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1645: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1646: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
1647: INTO l_amount_due_org

Line 1645: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1641: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1642: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1643: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1644: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1645: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1646: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
1647: INTO l_amount_due_org
1648: from gl_sets_of_books a,
1649: ar_system_parameters b,

Line 1676: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1672: l_num_val NUMBER;
1673: l_char_val VARCHAR2(1000);
1674: begin
1675: IF p_party_id IS NOT NULL THEN
1676: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1677: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
1678: INTO l_adj_total
1679: from gl_sets_of_books a,
1680: ar_system_parameters b,

Line 1689: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1685: AND c.cust_account_id = d.cust_account_id
1686: AND d.party_id = p_party_id
1687: and as_of_date BETWEEN p_start_date AND p_end_date;
1688: ELSE IF p_cust_account_id IS NOT NULL THEN
1689: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1690: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
1691: INTO l_adj_total
1692: from gl_sets_of_books a,
1693: ar_system_parameters b,

Line 1700: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1696: and b.org_id = c.org_id
1697: and c.cust_account_id = p_cust_account_id
1698: and as_of_date BETWEEN p_start_date AND p_end_date;
1699: ELSE IF p_customer_site_use_id IS NOT NULL THEN
1700: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1701: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
1702: INTO l_adj_total
1703: from gl_sets_of_books a,
1704: ar_system_parameters b,

Line 1730: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1726:
1727: begin
1728:
1729: IF p_party_id IS NOT NULL THEN
1730: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1731: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1732: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1733: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1734: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1732: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1728:
1729: IF p_party_id IS NOT NULL THEN
1730: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1731: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1732: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1733: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1734: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1735: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1736: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1734: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1730: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1731: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1732: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1733: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1734: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1735: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1736: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1737: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1738: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1736: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1732: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1733: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1734: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1735: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1736: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1737: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1738: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1739: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1740: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1738: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1734: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1735: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1736: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1737: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1738: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1739: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1740: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1741: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1742: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1740: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1736: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1737: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1738: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1739: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1740: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1741: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1742: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1743: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
1744: INTO l_amount_due_rem

Line 1742: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1738: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1739: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1740: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1741: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1742: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1743: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
1744: INTO l_amount_due_rem
1745: from gl_sets_of_books a,
1746: ar_system_parameters b,

Line 1755: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1751: AND c.cust_account_id = d.cust_account_id
1752: AND d.party_id = p_party_id
1753: and as_of_date BETWEEN p_start_date AND p_end_date;
1754: ELSE IF p_cust_account_id IS NOT NULL THEN
1755: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1756: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1757: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1758: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1759: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1757: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1753: and as_of_date BETWEEN p_start_date AND p_end_date;
1754: ELSE IF p_cust_account_id IS NOT NULL THEN
1755: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1756: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1757: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1758: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1759: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1760: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1761: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1759: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1755: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1756: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1757: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1758: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1759: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1760: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1761: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1762: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1763: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1761: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1757: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1758: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1759: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1760: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1761: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1762: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1763: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1764: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1765: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1763: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1759: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1760: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1761: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1762: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1763: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1764: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1765: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1766: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1767: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1765: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1761: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1762: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1763: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1764: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1765: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1766: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1767: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1768: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
1769: INTO l_amount_due_rem

Line 1767: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1763: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1764: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1765: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1766: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1767: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1768: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
1769: INTO l_amount_due_rem
1770: from gl_sets_of_books a,
1771: ar_system_parameters b,

Line 1778: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1774: and b.org_id = c.org_id
1775: and c.cust_account_id = p_cust_account_id
1776: and as_of_date BETWEEN p_start_date AND p_end_date;
1777: ELSE IF p_customer_site_use_id IS NOT NULL THEN
1778: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1779: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1780: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1781: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1782: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1780: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1776: and as_of_date BETWEEN p_start_date AND p_end_date;
1777: ELSE IF p_customer_site_use_id IS NOT NULL THEN
1778: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1779: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1780: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1781: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1782: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1783: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1784: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1782: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1778: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1779: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1780: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1781: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1782: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1783: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1784: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1785: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1786: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1784: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1780: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1781: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1782: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1783: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1784: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1785: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1786: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1787: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1788: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1786: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1782: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1783: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1784: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1785: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1786: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1787: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1788: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1789: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1790: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1788: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1784: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1785: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1786: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1787: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1788: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1789: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1790: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1791: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
1792: INTO l_amount_due_rem

Line 1790: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1786: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1787: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1788: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1789: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1790: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1791: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
1792: INTO l_amount_due_rem
1793: from gl_sets_of_books a,
1794: ar_system_parameters b,

Line 1821: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1817: l_num_val NUMBER;
1818: BEGIN
1819:
1820: IF p_party_id IS NOT NULL THEN
1821: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1822: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1823: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1824: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1825: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1823: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1819:
1820: IF p_party_id IS NOT NULL THEN
1821: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1822: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1823: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1824: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1825: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1826: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1827: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1825: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1821: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1822: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1823: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1824: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1825: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1826: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1827: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1828: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1829: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1827: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1823: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1824: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1825: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1826: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1827: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1828: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1829: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1830: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1831: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1829: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1825: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1826: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1827: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1828: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1829: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1830: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1831: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1832: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
1833: INTO l_tot_rec

Line 1831: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1827: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1828: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1829: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1830: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1831: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1832: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
1833: INTO l_tot_rec
1834: from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a, hz_cust_accounts d
1835: where b.set_of_books_id = a.set_of_books_id

Line 1840: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1836: AND c.cust_account_id = d.cust_account_id
1837: AND d.party_id = p_party_id
1838: and c.org_id = b.org_id;
1839:
1840: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1841: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1842: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1843: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1844: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1842: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1838: and c.org_id = b.org_id;
1839:
1840: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1841: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1842: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1843: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1844: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1845: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1846: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1844: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1840: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1841: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1842: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1843: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1844: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1845: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1846: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1847: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1848: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1846: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1842: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1843: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1844: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1845: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1846: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1847: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1848: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1849: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1850: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1848: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1844: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1845: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1846: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1847: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1848: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1849: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1850: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1851: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1852: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1850: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1846: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1847: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1848: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1849: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1850: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1851: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1852: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1853: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1854: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1852: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1848: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1849: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1850: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1851: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1852: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1853: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1854: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1855: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1856: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1854: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1850: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1851: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1852: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1853: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1854: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1855: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1856: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1857: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
1858: INTO l_tot_sales_in_year

Line 1856: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1852: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1853: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1854: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1855: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1856: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1857: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
1858: INTO l_tot_sales_in_year
1859: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b, hz_cust_accounts d
1860: WHERE c.org_id = b.org_id

Line 1867: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1863: AND b.set_of_books_id = a.set_of_books_id
1864: and as_of_date between TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
1865:
1866: ELSE IF p_cust_account_id IS NOT NULL THEN
1867: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1868: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1869: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1870: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1871: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1869: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1865:
1866: ELSE IF p_cust_account_id IS NOT NULL THEN
1867: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1868: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1869: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1870: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1871: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1872: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1873: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1871: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1867: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1868: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1869: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1870: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1871: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1872: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1873: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1874: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1875: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1873: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1869: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1870: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1871: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1872: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1873: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1874: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1875: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1876: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1877: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1875: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1871: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1872: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1873: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1874: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1875: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1876: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1877: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1878: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
1879: INTO l_tot_rec

Line 1877: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1873: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1874: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1875: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1876: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1877: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1878: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
1879: INTO l_tot_rec
1880: from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
1881: where b.set_of_books_id = a.set_of_books_id

Line 1885: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1881: where b.set_of_books_id = a.set_of_books_id
1882: AND c.cust_account_id = p_cust_account_id
1883: and c.org_id = b.org_id;
1884:
1885: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1886: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1887: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1888: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1889: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1887: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1883: and c.org_id = b.org_id;
1884:
1885: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1886: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1887: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1888: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1889: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1890: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1891: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1889: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1885: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1886: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1887: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1888: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1889: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1890: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1891: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1892: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1893: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1891: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1887: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1888: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1889: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1890: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1891: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1892: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1893: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1894: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1895: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1893: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1889: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1890: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1891: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1892: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1893: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1894: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1895: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1896: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1897: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1895: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1891: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1892: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1893: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1894: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1895: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1896: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1897: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1898: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1899: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1897: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1893: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1894: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1895: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1896: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1897: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1898: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1899: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1900: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1901: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1899: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1895: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1896: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1897: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1898: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1899: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1900: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1901: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1902: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
1903: INTO l_tot_sales_in_year

Line 1901: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1897: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1898: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1899: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1900: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1901: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1902: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
1903: INTO l_tot_sales_in_year
1904: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
1905: WHERE c.org_id = b.org_id

Line 1910: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1906: AND c.cust_account_id = p_cust_account_id
1907: AND b.set_of_books_id = a.set_of_books_id
1908: and as_of_date between TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
1909: ELSE IF p_customer_site_use_id IS NOT NULL THEN
1910: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1911: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1912: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1913: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1914: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1912: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1908: and as_of_date between TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
1909: ELSE IF p_customer_site_use_id IS NOT NULL THEN
1910: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1911: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1912: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1913: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1914: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1915: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1916: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1914: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1910: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1911: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1912: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1913: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1914: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1915: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1916: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1917: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1918: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1916: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1912: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1913: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1914: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1915: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1916: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1917: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1918: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1919: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1920: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1918: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1914: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1915: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1916: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1917: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1918: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1919: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1920: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1921: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
1922: INTO l_tot_rec

Line 1920: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1916: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1917: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1918: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1919: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1920: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1921: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
1922: INTO l_tot_rec
1923: from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
1924: where b.set_of_books_id = a.set_of_books_id

Line 1928: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1924: where b.set_of_books_id = a.set_of_books_id
1925: AND c.site_use_id = p_customer_site_use_id
1926: and c.org_id = b.org_id;
1927:
1928: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1929: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1930: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1931: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1932: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1930: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1926: and c.org_id = b.org_id;
1927:
1928: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1929: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1930: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1931: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1932: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1933: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1934: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1932: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1928: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1929: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1930: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1931: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1932: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1933: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1934: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1935: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1936: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1934: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1930: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1931: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1932: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1933: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1934: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1935: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1936: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1937: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1938: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1936: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1932: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1933: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1934: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1935: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1936: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1937: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1938: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1939: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1940: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1938: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1934: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1935: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1936: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1937: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1938: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1939: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1940: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1941: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1942: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1940: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1936: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1937: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1938: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1939: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1940: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1941: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1942: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1943: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1944: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 1942: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1938: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1939: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1940: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1941: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1942: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1943: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1944: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1945: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
1946: INTO l_tot_sales_in_year

Line 1944: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

1940: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1941: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1942: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1943: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1944: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1945: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
1946: INTO l_tot_sales_in_year
1947: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
1948: WHERE c.org_id = b.org_id

Line 2025: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2021:
2022: /*Denominator Total Sales month wise data */
2023:
2024: cursor c_month_sales_party(p_party_id number) is
2025: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2026: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2027: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2028: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2029: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2027: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2023:
2024: cursor c_month_sales_party(p_party_id number) is
2025: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2026: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2027: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2028: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2029: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2030: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2031: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2029: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2025: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2026: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2027: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2028: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2029: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2030: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2031: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2032: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2033: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2031: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2027: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2028: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2029: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2030: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2031: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2032: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2033: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2034: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
2035: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b , hz_cust_accounts d

Line 2033: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2029: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2030: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2031: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2032: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2033: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2034: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
2035: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b , hz_cust_accounts d
2036: WHERE c.cust_account_id = d.cust_account_id
2037: AND d.party_id = p_party_id

Line 2044: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2040: AND c.as_of_date between TRUNC(sysdate-365) AND TRUNC(sysdate)
2041: group by to_char(c.as_of_date,'MM');
2042:
2043: cursor c_month_sales_acct(p_cust_account_id number) is
2044: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2045: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2046: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2047: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2048: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2046: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2042:
2043: cursor c_month_sales_acct(p_cust_account_id number) is
2044: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2045: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2046: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2047: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2048: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2049: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2050: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2048: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2044: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2045: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2046: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2047: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2048: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2049: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2050: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2051: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2052: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2050: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2046: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2047: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2048: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2049: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2050: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2051: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2052: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2053: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
2054: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b

Line 2052: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2048: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2049: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2050: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2051: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2052: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2053: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
2054: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
2055: WHERE c.cust_account_id = p_cust_account_id
2056: and c.org_id = b.org_id

Line 2062: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2058: AND c.as_of_date between TRUNC(sysdate-365) AND TRUNC(sysdate)
2059: group by to_char(c.as_of_date,'MM');
2060:
2061: cursor c_month_sales_site(p_customer_site_use_id number) is
2062: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2063: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2064: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2065: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2066: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2064: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2060:
2061: cursor c_month_sales_site(p_customer_site_use_id number) is
2062: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2063: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2064: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2065: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2066: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2067: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2068: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2066: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2062: select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2063: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2064: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2065: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2066: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2067: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2068: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2069: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2070: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2068: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2064: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2065: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2066: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2067: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2068: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2069: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2070: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2071: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
2072: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b

Line 2070: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2066: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2067: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2068: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2069: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2070: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2071: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
2072: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
2073: WHERE c.site_use_id = p_customer_site_use_id
2074: and c.org_id = b.org_id

Line 2217: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2213: BEGIN
2214: --fnd_file.put_line(FND_FILE.LOG,'get_conv_dso_ocm: Start');
2215:
2216: IF p_party_id IS NOT NULL THEN
2217: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2218: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2219: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2220: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2221: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2219: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2215:
2216: IF p_party_id IS NOT NULL THEN
2217: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2218: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2219: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2220: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2221: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2222: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2223: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2221: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2217: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2218: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2219: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2220: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2221: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2222: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2223: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2224: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2225: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2223: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2219: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2220: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2221: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2222: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2223: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2224: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2225: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2226: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2227: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2225: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2221: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2222: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2223: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2224: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2225: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2226: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2227: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2228: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
2229: INTO l_op_bal

Line 2227: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2223: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2224: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2225: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2226: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2227: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2228: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
2229: INTO l_op_bal
2230: from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a,hz_cust_accounts d
2231: where c.cust_account_id = d.cust_account_id

Line 2236: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2232: AND d.party_id = p_party_id
2233: AND b.set_of_books_id = a.set_of_books_id
2234: and c.org_id = b.org_id;
2235: ELSE IF p_cust_account_id IS NOT NULL THEN
2236: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2237: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2238: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2239: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2240: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2238: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2234: and c.org_id = b.org_id;
2235: ELSE IF p_cust_account_id IS NOT NULL THEN
2236: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2237: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2238: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2239: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2240: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2241: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2242: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2240: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2236: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2237: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2238: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2239: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2240: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2241: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2242: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2243: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2244: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2242: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2238: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2239: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2240: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2241: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2242: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2243: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2244: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2245: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2246: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2244: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2240: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2241: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2242: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2243: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2244: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2245: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2246: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2247: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
2248: INTO l_op_bal

Line 2246: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2242: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2243: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2244: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2245: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2246: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2247: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
2248: INTO l_op_bal
2249: from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
2250: where c.cust_account_id= p_cust_account_id

Line 2254: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2250: where c.cust_account_id= p_cust_account_id
2251: AND b.set_of_books_id = a.set_of_books_id
2252: and c.org_id = b.org_id;
2253: ELSE IF p_customer_site_use_id IS NOT NULL THEN
2254: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2255: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2256: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2257: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2258: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2256: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2252: and c.org_id = b.org_id;
2253: ELSE IF p_customer_site_use_id IS NOT NULL THEN
2254: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2255: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2256: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2257: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2258: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2259: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2260: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2258: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2254: select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2255: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2256: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2257: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2258: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2259: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2260: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2261: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2262: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2260: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2256: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2257: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2258: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2259: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2260: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2261: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2262: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2263: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2264: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2262: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2258: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2259: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2260: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2261: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2262: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2263: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2264: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2265: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
2266: INTO l_op_bal

Line 2264: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2260: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2261: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2262: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2263: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2264: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2265: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
2266: INTO l_op_bal
2267: from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
2268: where c.site_use_id = p_customer_site_use_id

Line 2278: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2274:
2275: fnd_file.put_line(FND_FILE.LOG, 'GET_CONV_DSO_OCM :l_op_bal '|| l_op_bal);
2276:
2277: IF p_party_id IS NOT NULL THEN
2278: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2279: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2280: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2281: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2282: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2280: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2276:
2277: IF p_party_id IS NOT NULL THEN
2278: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2279: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2280: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2281: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2282: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2283: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2284: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2282: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2278: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2279: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2280: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2281: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2282: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2283: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2284: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2285: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2286: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2284: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2280: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2281: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2282: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2283: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2284: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2285: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2286: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2287: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2288: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2286: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2282: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2283: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2284: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2285: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2286: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2287: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2288: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2289: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
2290: INTO l_tot_bal

Line 2288: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2284: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2285: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2286: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2287: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2288: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2289: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
2290: INTO l_tot_bal
2291: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b, hz_cust_accounts d
2292: WHERE c.cust_account_id = d.cust_account_id

Line 2298: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2294: and c.org_id = b.org_id
2295: AND b.set_of_books_id = a.set_of_books_id
2296: and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);
2297: ELSE IF p_cust_account_id IS NOT NULL THEN
2298: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2299: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2300: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2301: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2302: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2300: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2296: and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);
2297: ELSE IF p_cust_account_id IS NOT NULL THEN
2298: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2299: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2300: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2301: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2302: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2303: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2304: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2302: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2298: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2299: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2300: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2301: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2302: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2303: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2304: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2305: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2306: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2304: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2300: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2301: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2302: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2303: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2304: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2305: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2306: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2307: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2308: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2306: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2302: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2303: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2304: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2305: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2306: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2307: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2308: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2309: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
2310: INTO l_tot_bal

Line 2308: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2304: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2305: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2306: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2307: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2308: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2309: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
2310: INTO l_tot_bal
2311: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
2312: WHERE c.cust_account_id = p_cust_account_id

Line 2317: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2313: and c.org_id = b.org_id
2314: AND b.set_of_books_id = a.set_of_books_id
2315: and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);
2316: ELSE IF p_customer_site_use_id IS NOT NULL THEN
2317: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2318: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2319: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2320: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2321: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2319: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2315: and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);
2316: ELSE IF p_customer_site_use_id IS NOT NULL THEN
2317: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2318: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2319: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2320: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2321: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2322: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2323: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2321: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2317: SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2318: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2319: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2320: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2321: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2322: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2323: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2324: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2325: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2323: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2319: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2320: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2321: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2322: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2323: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2324: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2325: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2326: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2327: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

Line 2325: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2321: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2322: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2323: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2324: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2325: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2326: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2327: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2328: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
2329: INTO l_tot_bal

Line 2327: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2323: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2324: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2325: nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2326: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2327: NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2328: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
2329: INTO l_tot_bal
2330: FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
2331: WHERE c.site_use_id = p_customer_site_use_id

Line 2445: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2441: l_num_val NUMBER;
2442: l_char_val VARCHAR2(1000);
2443: begin
2444: IF p_party_id IS NOT NULL THEN
2445: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2446: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_count),0))
2447: INTO l_nsf_count
2448: from gl_sets_of_books a,
2449: ar_system_parameters b,

Line 2458: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2454: AND c.cust_account_id = d.cust_account_id
2455: AND d.party_id = p_party_id
2456: and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2457: ELSE IF p_cust_account_id IS NOT NULL THEN
2458: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2459: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_count),0))
2460: INTO l_nsf_count
2461: from gl_sets_of_books a,
2462: ar_system_parameters b,

Line 2469: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2465: and b.org_id = c.org_id
2466: and c.cust_account_id = p_cust_account_id
2467: and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2468: ELSE IF p_customer_site_use_id IS NOT NULL THEN
2469: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2470: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_count),0))
2471: INTO l_nsf_count
2472: from gl_sets_of_books a,
2473: ar_system_parameters b,

Line 2499: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2495: l_num_val NUMBER;
2496: l_char_val VARCHAR2(1000);
2497: begin
2498: IF p_party_id IS NOT NULL THEN
2499: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2500: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
2501: INTO l_nsf_amount
2502: from gl_sets_of_books a,
2503: ar_system_parameters b,

Line 2512: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2508: AND c.cust_account_id = d.cust_account_id
2509: AND d.party_id = p_party_id
2510: and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2511: ELSE IF p_cust_account_id IS NOT NULL THEN
2512: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2513: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
2514: INTO l_nsf_amount
2515: from gl_sets_of_books a,
2516: ar_system_parameters b,

Line 2523: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,

2519: and b.org_id = c.org_id
2520: and c.cust_account_id = p_cust_account_id
2521: and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2522: ELSE IF p_customer_site_use_id IS NOT NULL THEN
2523: select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2524: fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
2525: INTO l_nsf_amount
2526: from gl_sets_of_books a,
2527: ar_system_parameters b,

Line 2555: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /

2551: l_char_val VARCHAR2(1000);
2552:
2553: BEGIN
2554: IF p_party_id IS NOT NULL THEN
2555: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2556: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2557: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2558: INTO l_wt_days_paid_late
2559: FROM gl_sets_of_books a,

Line 2556: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,

2552:
2553: BEGIN
2554: IF p_party_id IS NOT NULL THEN
2555: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2556: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2557: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2558: INTO l_wt_days_paid_late
2559: FROM gl_sets_of_books a,
2560: ar_system_parameters b ,

Line 2557: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)

2553: BEGIN
2554: IF p_party_id IS NOT NULL THEN
2555: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2556: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2557: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2558: INTO l_wt_days_paid_late
2559: FROM gl_sets_of_books a,
2560: ar_system_parameters b ,
2561: ar_trx_summary c ,

Line 2569: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /

2565: AND c.cust_account_id = d.cust_account_id
2566: AND d.party_id = p_party_id
2567: AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2568: ELSE IF p_cust_account_id IS NOT NULL THEN
2569: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2570: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2571: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2572: INTO l_wt_days_paid_late
2573: FROM gl_sets_of_books a,

Line 2570: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,

2566: AND d.party_id = p_party_id
2567: AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2568: ELSE IF p_cust_account_id IS NOT NULL THEN
2569: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2570: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2571: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2572: INTO l_wt_days_paid_late
2573: FROM gl_sets_of_books a,
2574: ar_system_parameters b ,

Line 2571: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)

2567: AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2568: ELSE IF p_cust_account_id IS NOT NULL THEN
2569: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2570: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2571: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2572: INTO l_wt_days_paid_late
2573: FROM gl_sets_of_books a,
2574: ar_system_parameters b ,
2575: ar_trx_summary c

Line 2582: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /

2578: AND b.org_id = c.org_id
2579: AND c.cust_account_id = p_cust_account_id
2580: AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2581: ELSE IF p_customer_site_use_id IS NOT NULL THEN
2582: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2583: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2584: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2585: INTO l_wt_days_paid_late
2586: FROM gl_sets_of_books a,

Line 2583: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,

2579: AND c.cust_account_id = p_cust_account_id
2580: AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2581: ELSE IF p_customer_site_use_id IS NOT NULL THEN
2582: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2583: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2584: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2585: INTO l_wt_days_paid_late
2586: FROM gl_sets_of_books a,
2587: ar_system_parameters b ,

Line 2584: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)

2580: AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2581: ELSE IF p_customer_site_use_id IS NOT NULL THEN
2582: SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2583: DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2584: SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2585: INTO l_wt_days_paid_late
2586: FROM gl_sets_of_books a,
2587: ar_system_parameters b ,
2588: ar_trx_summary c