eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OE_CREDIT_EXPOSURE_PVT dependencies on OE_CREDIT_SUMMARIES

Line 141: FROM OE_credit_summaries

137: l_return VARCHAR2(1) ;
138:
139: CURSOR chk_inv_past_due_cust(p_curr_code IN VARCHAR2 ) IS
140: SELECT 'Y'
141: FROM OE_credit_summaries
142: WHERE cust_account_id = p_customer_id
143: AND currency_code = p_curr_code
144: AND bucket <= l_jdate
145: AND bucket_duration = 1

Line 153: FROM OE_credit_summaries

149: AND balance_type = 20 ;
150:
151: CURSOR chk_inv_past_due_cust_gl(p_curr_code IN VARCHAR2 ) IS
152: SELECT 'Y'
153: FROM OE_credit_summaries
154: WHERE cust_account_id = p_customer_id
155: AND currency_code = p_curr_code
156: AND bucket <= l_jdate
157: AND bucket_duration = 1

Line 163: FROM OE_credit_summaries

159:
160:
161: CURSOR chk_inv_past_due_site(p_curr_code IN VARCHAR2 ) IS
162: SELECT 'Y'
163: FROM OE_credit_summaries
164: WHERE site_use_id = p_site_use_id
165: AND currency_code = p_curr_code
166: AND bucket <= l_jdate
167: AND bucket_duration = 1

Line 172: FROM OE_credit_summaries oes

168: AND balance_type = 20 ;
169:
170: CURSOR chk_inv_past_due_party(p_curr_code IN VARCHAR2 ) IS
171: SELECT 'Y'
172: FROM OE_credit_summaries oes
173: , hz_hierarchy_nodes hn
174: WHERE hn.parent_id = p_party_id
175: AND hn.parent_object_type = 'ORGANIZATION'
176: and hn.parent_table_name = 'HZ_PARTIES'

Line 191: FROM OE_credit_summaries

187:
188: --Bug 4991241
189: CURSOR chk_inv_past_due_single_party(p_curr_code IN VARCHAR2 ) IS
190: SELECT 'Y'
191: FROM OE_credit_summaries
192: WHERE party_id = p_party_id
193: AND currency_code = p_curr_code
194: AND bucket <= l_jdate
195: AND bucket_duration = 1

Line 200: FROM OE_credit_summaries

196: AND balance_type = 20 ;
197:
198: CURSOR chk_inv_past_due_cust_all IS
199: SELECT 'Y'
200: FROM OE_credit_summaries
201: WHERE cust_account_id = p_customer_id
202: AND bucket <= l_jdate
203: AND bucket_duration = 1
204: AND ((org_id = p_org_id)

Line 211: FROM OE_credit_summaries

207: AND balance_type = 20 ;
208:
209: CURSOR chk_inv_past_due_cust_all_gl IS
210: SELECT 'Y'
211: FROM OE_credit_summaries
212: WHERE cust_account_id = p_customer_id
213: AND bucket <= l_jdate
214: AND bucket_duration = 1
215: AND balance_type = 20 ;

Line 220: FROM OE_credit_summaries

216:
217:
218: CURSOR chk_inv_past_due_site_all IS
219: SELECT 'Y'
220: FROM OE_credit_summaries
221: WHERE site_use_id = p_site_use_id
222: AND bucket <= l_jdate
223: AND bucket_duration = 1
224: AND balance_type = 20 ;

Line 228: FROM OE_credit_summaries oes

224: AND balance_type = 20 ;
225:
226: CURSOR chk_inv_past_due_party_all IS
227: SELECT 'Y'
228: FROM OE_credit_summaries oes
229: , hz_hierarchy_nodes hn
230: WHERE hn.parent_id = p_party_id
231: AND hn.parent_object_type = 'ORGANIZATION'
232: and hn.parent_table_name = 'HZ_PARTIES'

Line 246: FROM OE_credit_summaries

242:
243: --Bug 4991241
244: CURSOR chk_inv_past_party_single_all IS
245: SELECT 'Y'
246: FROM OE_credit_summaries
247: WHERE party_id = p_party_id
248: AND bucket <= l_jdate
249: AND bucket_duration = 1
250: AND balance_type = 20 ;

Line 1514: -- OE_CREDIT_SUMMARIES table for the balance types

1510: END convert_currency_amount;
1511:
1512: ----------------------------------------------------------
1513: ---- COMMENTS: Fucntion to select the overall exposure from the
1514: -- OE_CREDIT_SUMMARIES table for the balance types
1515: -- This function will be called from the main
1516: -- GET_EXPOSURE procedure both for AR and OM exposure
1517: -- as the exposure balance is governed by the
1518: -- balance types used

Line 1556: FROM oe_credit_summaries

1552: l_br_bucket_length NUMBER;
1553:
1554: CURSOR site_balance_csr IS
1555: SELECT SUM( balance )
1556: FROM oe_credit_summaries
1557: WHERE balance_type IN
1558: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1559: b10, b11, b12, b13, b14, b15, b16, b17, b18)
1560: AND site_use_id = l_site_use_id

Line 1570: FROM oe_credit_summaries

1566: ---added for the Returns project
1567: ---the same as above including Returns
1568: CURSOR site_balance_ret_csr IS
1569: SELECT NVL(SUM( balance ),0)
1570: FROM oe_credit_summaries
1571: WHERE balance_type IN
1572: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1573: b10, b11, b12, b13, b14, b15, b16, b17, b18, b23, b24,
1574: b25, b26, b27, b28, b29, b30, b31, b32, b33, b34, b35, b36)

Line 1583: FROM oe_credit_summaries

1579:
1580:
1581: CURSOR site_balance_stub_csr IS
1582: SELECT SUM( balance )
1583: FROM oe_credit_summaries
1584: WHERE balance_type IN
1585: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1586: b10, b11, b12, b13, b14, b15, b16, b17, b18)
1587: AND site_use_id = l_site_use_id

Line 1597: FROM oe_credit_summaries

1593: ---added for the Returns project
1594: ---the same as above including Returns
1595: CURSOR site_balance_stub_ret_csr IS
1596: SELECT SUM( balance )
1597: FROM oe_credit_summaries
1598: WHERE balance_type IN
1599: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1600: b10, b11, b12, b13, b14, b15, b16, b17, b18, b23, b24,
1601: b25, b26, b27, b28, b29, b30, b31, b32, b33, b34, b35, b36)

Line 1610: FROM oe_credit_summaries

1606:
1607:
1608: CURSOR site_br_stub_csr IS
1609: SELECT SUM( balance )
1610: FROM oe_credit_summaries
1611: WHERE balance_type IN
1612: (21,22)
1613: AND site_use_id = l_br_site_use_id
1614: AND currency_code = l_currency_code

Line 1622: FROM oe_credit_summaries

1618:
1619:
1620: CURSOR customer_balance_csr IS
1621: SELECT SUM( balance )
1622: FROM oe_credit_summaries
1623: WHERE balance_type IN
1624: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1625: b10, b11, b12, b13, b14, b15, b16, b17, b18, b21,b22)
1626: AND cust_account_id = p_customer_id

Line 1639: FROM oe_credit_summaries

1635: ---added for the Returns project
1636: ---the same as above including Returns
1637: CURSOR customer_balance_ret_csr IS
1638: SELECT SUM( balance )
1639: FROM oe_credit_summaries
1640: WHERE balance_type IN
1641: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1642: b10, b11, b12, b13, b14, b15, b16, b17, b18, b21,b22, b23, b24,
1643: b25, b26, b27, b28, b29, b30, b31, b32, b33, b34, b35, b36)

Line 1655: FROM oe_credit_summaries

1651:
1652:
1653: CURSOR customer_balance_stub_csr IS
1654: SELECT SUM( balance )
1655: FROM oe_credit_summaries
1656: WHERE balance_type IN
1657: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1658: b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
1659: AND cust_account_id = p_customer_id

Line 1672: FROM oe_credit_summaries

1668: ---added for the Returns project
1669: ---the same as above including Returns
1670: CURSOR customer_balance_stub_ret_csr IS
1671: SELECT SUM( balance )
1672: FROM oe_credit_summaries
1673: WHERE balance_type IN
1674: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1675: b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22, b23, b24,
1676: b25, b26, b27, b28, b29, b30, b31, b32, b33, b34, b35, b36)

Line 1689: FROM oe_credit_summaries

1685:
1686:
1687: CURSOR customer_br_stub_csr IS
1688: SELECT SUM( balance )
1689: FROM oe_credit_summaries
1690: WHERE balance_type IN
1691: (21,22)
1692: AND cust_account_id = p_customer_id
1693: AND ((org_id = p_org_id)

Line 1704: FROM oe_credit_summaries

1700:
1701:
1702: CURSOR cust_balance_csr_global IS
1703: SELECT SUM( balance )
1704: FROM oe_credit_summaries
1705: WHERE balance_type IN
1706: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1707: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
1708: AND cust_account_id = p_customer_id

Line 1719: FROM oe_credit_summaries

1715: ---the same as above including Returns
1716:
1717: CURSOR cust_balance_ret_csr_global IS
1718: SELECT SUM( balance )
1719: FROM oe_credit_summaries
1720: WHERE balance_type IN
1721: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1722: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
1723: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 1733: FROM oe_credit_summaries

1729: ;
1730:
1731: CURSOR cust_balance_stub_csr_global IS
1732: SELECT SUM( balance )
1733: FROM oe_credit_summaries
1734: WHERE balance_type IN
1735: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1736: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
1737: AND cust_account_id = p_customer_id

Line 1747: FROM oe_credit_summaries

1743: ---added for the Returns project
1744: ---the same as above including Returns
1745: CURSOR cust_bal_stub_ret_csr_global IS
1746: SELECT SUM( balance )
1747: FROM oe_credit_summaries
1748: WHERE balance_type IN
1749: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1750: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
1751: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 1761: FROM oe_credit_summaries

1757: ;
1758:
1759: CURSOR cust_br_stub_csr_global IS
1760: SELECT SUM( balance )
1761: FROM oe_credit_summaries
1762: WHERE balance_type IN
1763: (21,22)
1764: AND cust_account_id = p_customer_id
1765: AND currency_code = l_currency_code

Line 1775: FROM oe_credit_summaries

1771: --------------------------------------------------------------
1772:
1773: CURSOR party_balance_csr_global IS
1774: SELECT SUM( balance )
1775: FROM oe_credit_summaries
1776: WHERE balance_type IN
1777: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1778: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
1779: AND party_id = p_party_id

Line 1790: FROM oe_credit_summaries

1786: ---the same as above including Returns
1787:
1788: CURSOR party_balance_ret_csr_global IS
1789: SELECT SUM( balance )
1790: FROM oe_credit_summaries
1791: WHERE balance_type IN
1792: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1793: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
1794: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 1805: FROM oe_credit_summaries

1801:
1802:
1803: CURSOR party_balance_stub_csr_global IS
1804: SELECT SUM( balance )
1805: FROM oe_credit_summaries
1806: WHERE balance_type IN
1807: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1808: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
1809: AND party_id = p_party_id

Line 1819: FROM oe_credit_summaries

1815: ---added for the Returns project
1816: ---the same as above including Returns
1817: CURSOR party_bal_stub_ret_csr_global IS
1818: SELECT SUM( balance )
1819: FROM oe_credit_summaries
1820: WHERE balance_type IN
1821: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1822: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
1823: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 1833: FROM oe_credit_summaries

1829: ;
1830:
1831: CURSOR party_br_stub_csr_global IS
1832: SELECT SUM( balance )
1833: FROM oe_credit_summaries
1834: WHERE balance_type IN
1835: (21,22)
1836: AND party_id = p_party_id
1837: AND currency_code = l_currency_code

Line 1846: FROM oe_credit_summaries oes

1842:
1843: -------------------------------------------------------------
1844: CURSOR party_h_balance_csr_global IS
1845: SELECT SUM( oes.balance )
1846: FROM oe_credit_summaries oes
1847: , hz_hierarchy_nodes hn
1848: WHERE oes.balance_type IN
1849: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1850: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)

Line 1870: FROM oe_credit_summaries oes

1866: ---added for the Returns project
1867: ---the same as above including Returns
1868: CURSOR party_h_bal_ret_csr_global IS
1869: SELECT SUM( oes.balance )
1870: FROM oe_credit_summaries oes
1871: , hz_hierarchy_nodes hn
1872: WHERE oes.balance_type IN
1873: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1874: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,

Line 1894: FROM oe_credit_summaries oes

1890:
1891:
1892: CURSOR party_h_bal_stub_csr_gl IS
1893: SELECT SUM( oes.balance )
1894: FROM oe_credit_summaries oes
1895: , hz_hierarchy_nodes hn
1896: WHERE oes.balance_type IN
1897: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1898: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)

Line 1917: FROM oe_credit_summaries oes

1913: ---added for the Returns project
1914: ---the same as above including Returns
1915: CURSOR party_h_bal_ret_stub_csr_gl IS
1916: SELECT SUM( oes.balance )
1917: FROM oe_credit_summaries oes
1918: , hz_hierarchy_nodes hn
1919: WHERE oes.balance_type IN
1920: (b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
1921: b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,

Line 1941: FROM oe_credit_summaries oes

1937:
1938:
1939: CURSOR party_br_h_stub_csr_gl IS
1940: SELECT SUM( oes.balance )
1941: FROM oe_credit_summaries oes
1942: , hz_hierarchy_nodes hn
1943: WHERE oes.balance_type IN
1944: (21,22)
1945: AND hn.parent_id = p_party_id

Line 1965: FROM oe_credit_summaries

1961:
1962: CURSOR site_balance_all_curr_csr IS
1963: SELECT SUM( balance )
1964: , currency_code
1965: FROM oe_credit_summaries
1966: WHERE balance_type IN
1967: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1968: b10, b11, b12, b13, b14, b15, b16, b17, b18)
1969: AND site_use_id = l_site_use_id

Line 1981: FROM oe_credit_summaries

1977:
1978: CURSOR site_bal_all_curr_ret_csr IS
1979: SELECT SUM( balance )
1980: , currency_code
1981: FROM oe_credit_summaries
1982: WHERE balance_type IN
1983: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
1984: b10, b11, b12, b13, b14, b15, b16, b17, b18,
1985: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 1997: FROM oe_credit_summaries

1993:
1994: CURSOR site_balance_all_curr_stub_csr IS
1995: SELECT SUM( balance )
1996: , currency_code
1997: FROM oe_credit_summaries
1998: WHERE balance_type IN
1999: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2000: b10, b11, b12, b13, b14, b15, b16, b17, b18)
2001: AND site_use_id = l_site_use_id

Line 2012: FROM oe_credit_summaries

2008: ---the same as above including Returns
2009: CURSOR site_bal_all_curr_stub_ret_csr IS
2010: SELECT SUM( balance )
2011: , currency_code
2012: FROM oe_credit_summaries
2013: WHERE balance_type IN
2014: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2015: b10, b11, b12, b13, b14, b15, b16, b17, b18,
2016: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 2027: FROM oe_credit_summaries

2023:
2024: CURSOR site_br_all_curr_stub_csr IS
2025: SELECT SUM( balance )
2026: , currency_code
2027: FROM oe_credit_summaries
2028: WHERE balance_type IN
2029: (21,22)
2030: AND site_use_id = l_br_site_use_id
2031: AND bucket < l_br_bucket

Line 2040: FROM oe_credit_summaries

2036:
2037: CURSOR customer_balance_all_curr_csr IS
2038: SELECT SUM( balance )
2039: , currency_code
2040: FROM oe_credit_summaries
2041: WHERE balance_type IN
2042: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2043: b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22 )
2044: AND cust_account_id = p_customer_id

Line 2058: FROM oe_credit_summaries

2054: ---the same as above including Returns
2055: CURSOR customer_bal_all_curr_ret_csr IS
2056: SELECT SUM( balance )
2057: , currency_code
2058: FROM oe_credit_summaries
2059: WHERE balance_type IN
2060: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2061: b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
2062: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 2076: FROM oe_credit_summaries

2072:
2073: CURSOR customer_all_curr_stub_csr IS
2074: SELECT SUM( balance )
2075: , currency_code
2076: FROM oe_credit_summaries
2077: WHERE balance_type IN
2078: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2079: b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
2080: AND cust_account_id = p_customer_id

Line 2094: FROM oe_credit_summaries

2090: ---the same as above including Returns
2091: CURSOR customer_all_curr_stub_ret_csr IS
2092: SELECT SUM( balance )
2093: , currency_code
2094: FROM oe_credit_summaries
2095: WHERE balance_type IN
2096: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2097: b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
2098: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 2112: FROM oe_credit_summaries

2108:
2109: CURSOR customer_br_all_curr_stub_csr IS
2110: SELECT SUM( balance )
2111: , currency_code
2112: FROM oe_credit_summaries
2113: WHERE balance_type IN
2114: (21,22)
2115: AND cust_account_id = p_customer_id
2116: AND ((org_id = p_org_id)

Line 2128: FROM oe_credit_summaries

2124:
2125: CURSOR cust_all_curr_csr_global IS
2126: SELECT SUM( balance )
2127: , currency_code
2128: FROM oe_credit_summaries
2129: WHERE balance_type IN
2130: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2131: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
2132: AND cust_account_id = p_customer_id

Line 2143: FROM oe_credit_summaries

2139: ---the same as above including Returns
2140: CURSOR cust_all_curr_ret_csr_global IS
2141: SELECT SUM( balance )
2142: , currency_code
2143: FROM oe_credit_summaries
2144: WHERE balance_type IN
2145: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2146: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
2147: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 2159: FROM oe_credit_summaries

2155:
2156: CURSOR cust_all_curr_stub_csr_global IS
2157: SELECT SUM( balance )
2158: , currency_code
2159: FROM oe_credit_summaries
2160: WHERE balance_type IN
2161: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2162: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
2163: AND cust_account_id = p_customer_id

Line 2174: FROM oe_credit_summaries

2170: ---the same as above including Returns
2171: CURSOR cust_all_curr_stub_ret_csr_gl IS
2172: SELECT SUM( balance )
2173: , currency_code
2174: FROM oe_credit_summaries
2175: WHERE balance_type IN
2176: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2177: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
2178: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 2189: FROM oe_credit_summaries

2185:
2186: CURSOR cust_br_all_curr_stub_csr_gl IS
2187: SELECT SUM( balance )
2188: , currency_code
2189: FROM oe_credit_summaries
2190: WHERE balance_type IN
2191: (21,22)
2192: AND cust_account_id = p_customer_id
2193: AND bucket < l_br_bucket

Line 2204: FROM oe_credit_summaries

2200:
2201: CURSOR party_all_curr_csr_global IS
2202: SELECT SUM( balance )
2203: , currency_code
2204: FROM oe_credit_summaries
2205: WHERE balance_type IN
2206: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2207: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
2208: AND party_id = p_party_id

Line 2219: FROM oe_credit_summaries

2215: ---the same as above including Returns
2216: CURSOR party_all_curr_ret_csr_gl IS
2217: SELECT SUM( balance )
2218: , currency_code
2219: FROM oe_credit_summaries
2220: WHERE balance_type IN
2221: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2222: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
2223: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 2234: FROM oe_credit_summaries

2230:
2231: CURSOR party_all_curr_stub_csr_global IS
2232: SELECT SUM( balance )
2233: , currency_code
2234: FROM oe_credit_summaries
2235: WHERE balance_type IN
2236: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2237: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
2238: AND party_id = p_party_id

Line 2249: FROM oe_credit_summaries

2245: ---the same as above including Returns
2246: CURSOR party_all_curr_stub_ret_csr_gl IS
2247: SELECT SUM( balance )
2248: , currency_code
2249: FROM oe_credit_summaries
2250: WHERE balance_type IN
2251: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2252: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
2253: b23, b24, b25, b26, b27, b28, b29, b30, b31,

Line 2264: FROM oe_credit_summaries

2260:
2261: CURSOR party_br_all_curr_stub_csr_gl IS
2262: SELECT SUM( balance )
2263: , currency_code
2264: FROM oe_credit_summaries
2265: WHERE balance_type IN
2266: (21,22)
2267: AND party_id = p_party_id
2268: AND bucket < l_br_bucket

Line 2279: FROM oe_credit_summaries oes

2275:
2276: CURSOR party_h_all_curr_csr_global IS
2277: SELECT SUM( oes.balance )
2278: , oes.currency_code
2279: FROM oe_credit_summaries oes
2280: , hz_hierarchy_nodes hn
2281: WHERE oes.balance_type IN
2282: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2283: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)

Line 2303: FROM oe_credit_summaries oes

2299: ---the same as above including Returns
2300: CURSOR party_h_all_curr_ret_csr_gl IS
2301: SELECT SUM( oes.balance )
2302: , oes.currency_code
2303: FROM oe_credit_summaries oes
2304: , hz_hierarchy_nodes hn
2305: WHERE oes.balance_type IN
2306: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2307: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,

Line 2327: FROM oe_credit_summaries oes

2323:
2324: CURSOR party_h_all_curr_stub_csr_gl IS
2325: SELECT SUM( oes.balance )
2326: , oes.currency_code
2327: FROM oe_credit_summaries oes
2328: , hz_hierarchy_nodes hn
2329: WHERE oes.balance_type IN
2330: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2331: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)

Line 2351: FROM oe_credit_summaries oes

2347: ---the same as above including Returns
2348: CURSOR p_h_all_curr_stub_ret_csr_gl IS
2349: SELECT SUM( oes.balance )
2350: , oes.currency_code
2351: FROM oe_credit_summaries oes
2352: , hz_hierarchy_nodes hn
2353: WHERE oes.balance_type IN
2354: (b1, b2, b3, b4, b5, b6, b7, b8, b9,
2355: b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,

Line 2375: FROM oe_credit_summaries oes

2371:
2372: CURSOR party_br_h_all_curr_stub_gl IS
2373: SELECT SUM( oes.balance )
2374: , oes.currency_code
2375: FROM oe_credit_summaries oes
2376: , hz_hierarchy_nodes hn
2377: WHERE oes.balance_type IN
2378: (21,22)
2379: AND hn.parent_id = p_party_id

Line 4908: -- which will repopulate oe_credit_summaries table with summarized

4904: -- p_lock_tables 'Y' or 'N' for all transaction tables
4905: ---
4906: -- COMMENT : This is the concurrent program body for
4907: -- Initialize Credit Summaries Table
4908: -- which will repopulate oe_credit_summaries table with summarized
4909: -- credit exposure information.
4910: -- The p_lock_tables flag specifies if
4911: -- the oe_order_lines_all, oe_order_headers_all,
4912: -- oe_cash_adjustments, ar_payment_schedules_all,

Line 5059: DELETE FROM oe_credit_summaries

5055: oe_debug_pub.add( 'Value of the profile OM: Preserve External Credit Balances is :' || NVL(FND_PROFILE.VALUE('ONT_PRESERVE_EXT_CR_BAL'),'Y'),2);
5056: END IF;
5057:
5058: IF NVL(FND_PROFILE.VALUE('ONT_PRESERVE_EXT_CR_BAL'),'Y') = 'Y' THEN
5059: DELETE FROM oe_credit_summaries
5060: WHERE balance_type <> 18;
5061: ELSE
5062: l_ret := FND_INSTALLATION.get_app_info('ONT',l_status,l_industry,l_ont_schema); --This API is used to get schema name
5063: l_sql_stmnt := 'truncate table '||l_ont_schema ||'.oe_credit_summaries';

Line 5063: l_sql_stmnt := 'truncate table '||l_ont_schema ||'.oe_credit_summaries';

5059: DELETE FROM oe_credit_summaries
5060: WHERE balance_type <> 18;
5061: ELSE
5062: l_ret := FND_INSTALLATION.get_app_info('ONT',l_status,l_industry,l_ont_schema); --This API is used to get schema name
5063: l_sql_stmnt := 'truncate table '||l_ont_schema ||'.oe_credit_summaries';
5064: EXECUTE IMMEDIATE l_sql_stmnt;
5065: END IF;
5066:
5067: /* comment out the following code for bug 4219133

Line 5069: DELETE FROM oe_credit_summaries

5065: END IF;
5066:
5067: /* comment out the following code for bug 4219133
5068: -- Do not delete external exposure data
5069: DELETE FROM oe_credit_summaries
5070: WHERE balance_type <> 18;
5071: */
5072:
5073:

Line 5267: -- Insert Tax Summaries into oe_credit_summaries

5263: -- check rule and other parameters.
5264:
5265:
5266: --------------------------------------------------------
5267: -- Insert Tax Summaries into oe_credit_summaries
5268: -- for uninvoiced orders and tax balance types at
5269: -- header bill-to-site level
5270:
5271:

Line 5278: INSERT INTO oe_credit_summaries

5274: oe_debug_pub.add( 'Start Inserting into summary tables ',1);
5275: END IF;
5276:
5277: -- balance type 1
5278: INSERT INTO oe_credit_summaries
5279: ( balance
5280: , balance_type
5281: , site_use_id
5282: , cust_account_id

Line 5350: INSERT INTO OE_CREDIT_SUMMARIES

5346: END IF;
5347:
5348:
5349: -- balance type 3
5350: INSERT INTO OE_CREDIT_SUMMARIES
5351: ( balance
5352: , balance_type
5353: , site_use_id
5354: , cust_account_id

Line 5420: INSERT INTO oe_credit_summaries

5416: --- For uninvoiced orders and tax returns
5417: --- balance types at header bill-to-site level
5418: ----------------------------------------------
5419: -- balance type 23
5420: INSERT INTO oe_credit_summaries
5421: ( balance
5422: , balance_type
5423: , site_use_id
5424: , cust_account_id

Line 5487: INSERT INTO OE_CREDIT_SUMMARIES

5483: END IF;
5484:
5485: -- balance type 25
5486:
5487: INSERT INTO OE_CREDIT_SUMMARIES
5488: ( balance
5489: , balance_type
5490: , site_use_id
5491: , cust_account_id

Line 5554: INSERT INTO OE_CREDIT_SUMMARIES

5550: -- h.invoice_to_org_id is replaced with l.invoice_to_org_id
5551:
5552: -- balance type 2
5553:
5554: INSERT INTO OE_CREDIT_SUMMARIES
5555: ( balance
5556: , balance_type
5557: , site_use_id
5558: , cust_account_id

Line 5626: INSERT INTO OE_CREDIT_SUMMARIES

5622: END IF;
5623:
5624: -- balance type 4
5625:
5626: INSERT INTO OE_CREDIT_SUMMARIES
5627: ( balance
5628: , balance_type
5629: , site_use_id
5630: , cust_account_id

Line 5699: INSERT INTO OE_CREDIT_SUMMARIES

5695: -- h.invoice_to_org_id is replaced with l.invoice_to_org_id
5696:
5697: -- balance type 24
5698:
5699: INSERT INTO OE_CREDIT_SUMMARIES
5700: ( balance
5701: , balance_type
5702: , site_use_id
5703: , cust_account_id

Line 5767: INSERT INTO OE_CREDIT_SUMMARIES

5763: END IF;
5764:
5765: -- balance type 26
5766:
5767: INSERT INTO OE_CREDIT_SUMMARIES
5768: ( balance
5769: , balance_type
5770: , site_use_id
5771: , cust_account_id

Line 5834: INSERT INTO OE_CREDIT_SUMMARIES

5830: -- Line Freight
5831: --------------------------
5832:
5833: -- balance type 6
5834: INSERT INTO OE_CREDIT_SUMMARIES
5835: ( balance
5836: , balance_type
5837: , site_use_id
5838: , cust_account_id

Line 5917: INSERT INTO OE_CREDIT_SUMMARIES

5913: -- Line Freight
5914: --------------------------
5915:
5916: -- balance type 28
5917: INSERT INTO OE_CREDIT_SUMMARIES
5918: ( balance
5919: , balance_type
5920: , site_use_id
5921: , cust_account_id

Line 5998: INSERT INTO OE_CREDIT_SUMMARIES

5994: --------------------------
5995:
5996: -- balance type 5
5997:
5998: INSERT INTO OE_CREDIT_SUMMARIES
5999: ( balance
6000: , balance_type
6001: , site_use_id
6002: , cust_account_id

Line 6081: INSERT INTO OE_CREDIT_SUMMARIES

6077: --------------------------------
6078:
6079: -- balance type 27
6080:
6081: INSERT INTO OE_CREDIT_SUMMARIES
6082: ( balance
6083: , balance_type
6084: , site_use_id
6085: , cust_account_id

Line 6164: INSERT INTO OE_CREDIT_SUMMARIES

6160: -- this same balance type summary is added
6161:
6162: -- balance type 7
6163:
6164: INSERT INTO OE_CREDIT_SUMMARIES
6165: ( balance
6166: , balance_type
6167: , site_use_id
6168: , cust_account_id

Line 6329: INSERT INTO OE_CREDIT_SUMMARIES

6325: -- this same balance type summary is added
6326:
6327: -- balance type 29
6328:
6329: INSERT INTO OE_CREDIT_SUMMARIES
6330: ( balance
6331: , balance_type
6332: , site_use_id
6333: , cust_account_id

Line 6495: INSERT INTO OE_CREDIT_SUMMARIES

6491: IF l_order_hold_bal = 'Y' THEN
6492:
6493: -- balance type 10
6494:
6495: INSERT INTO OE_CREDIT_SUMMARIES
6496: ( balance
6497: , balance_type
6498: , site_use_id
6499: , cust_account_id

Line 6579: INSERT INTO OE_CREDIT_SUMMARIES

6575: END IF;
6576:
6577: -- balance type 13
6578:
6579: INSERT INTO OE_CREDIT_SUMMARIES
6580: ( balance
6581: , balance_type
6582: , site_use_id
6583: , cust_account_id

Line 6666: INSERT INTO OE_CREDIT_SUMMARIES

6662: -- check rule
6663:
6664: -- balance type 30
6665:
6666: INSERT INTO OE_CREDIT_SUMMARIES
6667: ( balance
6668: , balance_type
6669: , site_use_id
6670: , cust_account_id

Line 6772: INSERT INTO OE_CREDIT_SUMMARIES

6768: END IF;
6769:
6770: -- balance type 32
6771:
6772: INSERT INTO OE_CREDIT_SUMMARIES
6773: ( balance
6774: , balance_type
6775: , site_use_id
6776: , cust_account_id

Line 6850: INSERT INTO OE_CREDIT_SUMMARIES

6846: -- h.invoice_to_org_id is replaced with l.invoice_to_org_id
6847:
6848: -- balance type 11
6849:
6850: INSERT INTO OE_CREDIT_SUMMARIES
6851: ( balance
6852: , balance_type
6853: , site_use_id
6854: , cust_account_id

Line 6931: INSERT INTO OE_CREDIT_SUMMARIES

6927: END IF;
6928:
6929: -- balance type 14
6930:
6931: INSERT INTO OE_CREDIT_SUMMARIES
6932: ( balance
6933: , balance_type
6934: , site_use_id
6935: , cust_account_id

Line 7015: INSERT INTO OE_CREDIT_SUMMARIES

7011: -- h.invoice_to_org_id is replaced with l.invoice_to_org_id
7012:
7013: -- balance type 31
7014:
7015: INSERT INTO OE_CREDIT_SUMMARIES
7016: ( balance
7017: , balance_type
7018: , site_use_id
7019: , cust_account_id

Line 7091: INSERT INTO OE_CREDIT_SUMMARIES

7087: END IF;
7088:
7089: -- balance type 33
7090:
7091: INSERT INTO OE_CREDIT_SUMMARIES
7092: ( balance
7093: , balance_type
7094: , site_use_id
7095: , cust_account_id

Line 7167: INSERT INTO OE_CREDIT_SUMMARIES

7163: --------------------------
7164:
7165: -- balance type 16
7166:
7167: INSERT INTO OE_CREDIT_SUMMARIES
7168: ( balance
7169: , balance_type
7170: , site_use_id
7171: , cust_account_id

Line 7259: INSERT INTO OE_CREDIT_SUMMARIES

7255: ---------------------------------
7256:
7257: -- balance type 35
7258:
7259: INSERT INTO OE_CREDIT_SUMMARIES
7260: ( balance
7261: , balance_type
7262: , site_use_id
7263: , cust_account_id

Line 7346: INSERT INTO OE_CREDIT_SUMMARIES

7342: --------------------------
7343:
7344: -- balance type 15
7345:
7346: INSERT INTO OE_CREDIT_SUMMARIES
7347: ( balance
7348: , balance_type
7349: , site_use_id
7350: , cust_account_id

Line 7438: INSERT INTO OE_CREDIT_SUMMARIES

7434: --------------------------------------
7435:
7436: -- balance type 34
7437:
7438: INSERT INTO OE_CREDIT_SUMMARIES
7439: ( balance
7440: , balance_type
7441: , site_use_id
7442: , cust_account_id

Line 7525: INSERT INTO OE_CREDIT_SUMMARIES

7521: --------------------------
7522:
7523: -- balance type 17
7524:
7525: INSERT INTO OE_CREDIT_SUMMARIES
7526: ( balance
7527: , balance_type
7528: , site_use_id
7529: , cust_account_id

Line 7626: INSERT INTO OE_CREDIT_SUMMARIES

7622: --------------------------------------
7623:
7624: -- balance type 36
7625:
7626: INSERT INTO OE_CREDIT_SUMMARIES
7627: ( balance
7628: , balance_type
7629: , site_use_id
7630: , cust_account_id

Line 7729: INSERT INTO OE_CREDIT_SUMMARIES

7725: -- by AR
7726:
7727: -- balance type 8
7728:
7729: INSERT INTO OE_CREDIT_SUMMARIES
7730: ( balance
7731: , balance_type
7732: , site_use_id
7733: , cust_account_id

Line 7802: INSERT INTO OE_CREDIT_SUMMARIES

7798: -----------------------------------------
7799:
7800: -- balance type 9
7801:
7802: INSERT INTO OE_CREDIT_SUMMARIES
7803: ( balance
7804: , balance_type
7805: , site_use_id
7806: , cust_account_id

Line 7896: INSERT INTO OE_CREDIT_SUMMARIES

7892: ---------------------- BR ------------------
7893:
7894: -- balance type 21
7895:
7896: INSERT INTO OE_CREDIT_SUMMARIES
7897: ( balance
7898: , balance_type
7899: , site_use_id
7900: , cust_account_id

Line 7971: INSERT INTO OE_CREDIT_SUMMARIES

7967: -----------------------------------------
7968:
7969: -- balance type 22
7970:
7971: INSERT INTO OE_CREDIT_SUMMARIES
7972: ( balance
7973: , balance_type
7974: , site_use_id
7975: , cust_account_id

Line 8069: INSERT INTO OE_CREDIT_SUMMARIES

8065: ----------------------- past due inv -------------
8066:
8067: -- balance type 20
8068:
8069: INSERT INTO OE_CREDIT_SUMMARIES
8070: ( balance
8071: , balance_type
8072: , site_use_id
8073: , cust_account_id

Line 8148: oe_credit_summaries

8144: l_bucket_length := POWER( 2, l_level );
8145:
8146: -- Commented for thr bug 7688615 to improve the performance.
8147: /*UPDATE
8148: oe_credit_summaries
8149: SET
8150: bucket_duration = l_bucket_length
8151: WHERE balance_type NOT IN (18,23,24,25,26,27,28,29,30,31,32,33,34,35,36); ---change for Returns
8152:

Line 8156: oe_credit_summaries

8152:
8153: */
8154: IF NVL(FND_PROFILE.VALUE('ONT_PRESERVE_EXT_CR_BAL'),'Y') = 'N' THEN
8155: UPDATE
8156: oe_credit_summaries
8157: SET
8158: bucket_duration = l_bucket_length
8159: WHERE balance_type =18;
8160: END IF;

Line 8175: INSERT /+ parallel / INTO OE_CREDIT_SUMMARIES

8171: WHILE l_level <= G_MAX_BUCKET_LEVEL LOOP
8172:
8173: -- hints are introduced as part of the performance bug 7688615
8174:
8175: INSERT /*+ parallel */ INTO OE_CREDIT_SUMMARIES
8176: ( balance
8177: , balance_type
8178: , site_use_id
8179: , cust_account_id

Line 8196: SELECT /+ parallel(OE_CREDIT_SUMMARIES) /

8192: , program_update_date
8193: , request_id
8194: )
8195:
8196: SELECT /*+ parallel(OE_CREDIT_SUMMARIES) */
8197: SUM( balance )
8198: , balance_type
8199: , site_use_id
8200: , cust_account_id

Line 8217: oe_credit_summaries

8213: , SYSDATE
8214: , l_request_id
8215:
8216: FROM
8217: oe_credit_summaries
8218: WHERE
8219: bucket_duration = l_bucket_length / 2
8220: AND balance_type NOT IN (18,23,24,25,26,27,28,29,30,31,32,33,34,35,36) ---change for Returns
8221: GROUP BY

Line 8475: -- Goal: read-consistency of oe_credit_summaries during exposure

8471:
8472:
8473: -----------
8474: -- Locks needed for consistency
8475: -- Goal: read-consistency of oe_credit_summaries during exposure
8476: -- Means: SHARE lock or read-only transaction.
8477: -- SHARE: Need to verify that update overrides share.
8478: -- read-only: May slow down query due to reconstruction using
8479: -- rollback segment w.r.t SCN