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.OZF_CUST_FACTS_PVT dependencies on OZF_FUNDS_ALL_B

Line 23: FROM ozf_funds_all_b a

19: b.bill_to_site_use_id bill_to_site_use_id,
20: c.item_id inventory_item_id ,
21: c.item_id item_id ,
22: c.item_type item_type
23: FROM ozf_funds_all_b a
24: ,ozf_account_allocations b
25: ,ozf_product_allocations c
26: WHERE a.fund_type = 'QUOTA'
27: --AND p_report_date BETWEEN a.start_date_active

Line 37: FROM ozf_funds_all_b bb

33: AND c.allocation_for = 'CUST'
34: AND c.allocation_for_id = b.account_allocation_id
35: AND a.parent_fund_id IS NOT NULL
36: AND NOT EXISTS ( SELECT 1
37: FROM ozf_funds_all_b bb
38: WHERE bb.parent_fund_id = a.fund_id );
39:
40: l_api_version CONSTANT NUMBER := 1.0;
41: l_api_name CONSTANT VARCHAR2(30) := 'refresh_accts_and_products';

Line 115: FROM ozf_funds_all_b a

111: prod.item_id PRODUCT_ATTR_VALUE
112: FROM ozf_account_allocations acct,
113: ozf_product_allocations prod,
114: (SELECT DISTINCT a.owner
115: FROM ozf_funds_all_b a
116: WHERE a.fund_type = 'QUOTA'
117: AND a.status_code <> 'CANCELLED') fund
118: WHERE prod.allocation_for = 'CUST'
119: AND prod.allocation_for_id = acct.account_allocation_id

Line 124: FROM ozf_funds_all_b aa

120: AND acct.allocation_for = 'FUND'
121: AND NVL(acct.account_status, 'X') <> 'D'
122: AND acct.allocation_for_id in
123: (SELECT aa.fund_id
124: FROM ozf_funds_all_b aa
125: WHERE aa.owner = fund.owner
126: AND aa.fund_type = 'QUOTA'
127: AND aa.status_code <> 'CANCELLED'
128: AND NOT EXISTS ( SELECT 1

Line 129: FROM ozf_funds_all_b bb

125: WHERE aa.owner = fund.owner
126: AND aa.fund_type = 'QUOTA'
127: AND aa.status_code <> 'CANCELLED'
128: AND NOT EXISTS ( SELECT 1
129: FROM ozf_funds_all_b bb
130: WHERE bb.parent_fund_id = aa.fund_id )
131: UNION ALL
132: SELECT aa.fund_id
133: FROM ozf_funds_all_b aa

Line 133: FROM ozf_funds_all_b aa

129: FROM ozf_funds_all_b bb
130: WHERE bb.parent_fund_id = aa.fund_id )
131: UNION ALL
132: SELECT aa.fund_id
133: FROM ozf_funds_all_b aa
134: WHERE aa.fund_type = 'QUOTA'
135: AND aa.status_code <> 'CANCELLED'
136: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
137: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id

Line 138: FROM ozf_funds_all_b bb

134: WHERE aa.fund_type = 'QUOTA'
135: AND aa.status_code <> 'CANCELLED'
136: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
137: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
138: FROM ozf_funds_all_b bb
139: WHERE bb.owner= fund.owner
140: AND bb.fund_type = 'QUOTA'
141: AND bb.status_code <> 'CANCELLED')
142: )

Line 891: ,ozf_funds_all_b d1

887: CURSOR target_csr IS
888: SELECT NVL(SUM(NVL(c1.target,0)),0)
889: FROM ozf_account_allocations b1
890: ,ozf_time_allocations c1
891: ,ozf_funds_all_b d1
892: WHERE b1.allocation_for = 'FUND'
893: AND b1.allocation_for_id = d1.fund_id
894: AND b1.site_use_id = p_site_use_id
895: AND b1.bill_to_site_use_id = p_bill_to_site_use_id

Line 905: FROM ozf_funds_all_b dd1

901: AND d1.status_code <> 'CANCELLED'
902: AND d1.parent_fund_id IS NOT NULL
903: -- AND p_report_date BETWEEN d1.start_date_active AND d1.end_date_active
904: AND NOT EXISTS ( SELECT 1
905: FROM ozf_funds_all_b dd1
906: WHERE dd1.parent_fund_id = d1.fund_id);
907:
908: l_target NUMBER := 0;
909:

Line 977: ,ozf_funds_all_b quota

973: )
974: FROM ozf_account_allocations cust
975: ,ozf_product_allocations prod
976: ,ozf_time_allocations time
977: ,ozf_funds_all_b quota
978: WHERE
979: -- Customer Filter
980: -- cust.site_use_code = 'SHIP_TO'
981: cust.allocation_for = 'FUND'

Line 1006: FROM ozf_funds_all_b dd

1002: AND quota.parent_fund_id IS NOT NULL
1003: -- Pick only quotas for leaf nodes.
1004: -- This filter is not required since quotas are always generated for leaf nodes
1005: AND NOT EXISTS ( SELECT 1
1006: FROM ozf_funds_all_b dd
1007: WHERE dd.parent_fund_id = quota.fund_id );
1008:
1009: CURSOR period_time_id_csr IS
1010: SELECT ent_period_id,

Line 1422: FROM ozf_funds_all_b a

1418:
1419: -- Cursor to get all resources
1420: CURSOR active_resources_csr IS
1421: SELECT DISTINCT a.owner
1422: FROM ozf_funds_all_b a
1423: WHERE a.fund_type = 'QUOTA'
1424: AND a.status_code <> 'CANCELLED' ;
1425:
1426:

Line 1445: FROM ozf_funds_all_b aa

1441: -- R12: Do not consider UnAllocated Rows
1442: AND a.parent_party_id <> -9999
1443: AND a.allocation_for_id IN ( -- Get leaf node quotas for this resource owns
1444: SELECT aa.fund_id
1445: FROM ozf_funds_all_b aa
1446: WHERE aa.owner = p_resource_id
1447: AND aa.fund_type = 'QUOTA'
1448: AND aa.status_code <> 'CANCELLED'
1449: AND NOT EXISTS ( SELECT 1

Line 1450: FROM ozf_funds_all_b bb

1446: WHERE aa.owner = p_resource_id
1447: AND aa.fund_type = 'QUOTA'
1448: AND aa.status_code <> 'CANCELLED'
1449: AND NOT EXISTS ( SELECT 1
1450: FROM ozf_funds_all_b bb
1451: WHERE bb.parent_fund_id = aa.fund_id )
1452: --
1453: UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
1454: --

Line 1456: FROM ozf_funds_all_b aa

1452: --
1453: UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
1454: --
1455: SELECT aa.fund_id
1456: FROM ozf_funds_all_b aa
1457: WHERE aa.fund_type = 'QUOTA'
1458: AND aa.status_code <> 'CANCELLED'
1459: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
1460: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id

Line 1461: FROM ozf_funds_all_b bb

1457: WHERE aa.fund_type = 'QUOTA'
1458: AND aa.status_code <> 'CANCELLED'
1459: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
1460: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
1461: FROM ozf_funds_all_b bb
1462: WHERE bb.owner = p_resource_id
1463: AND bb.fund_type = 'QUOTA'
1464: AND bb.status_code <> 'CANCELLED' )
1465: );

Line 1512: FROM ozf_funds_all_b aa

1508: -- R12: Do not consider UnAllocated Rows
1509: AND site.parent_party_id <> -9999
1510: AND site.allocation_for_id in (
1511: SELECT aa.fund_id
1512: FROM ozf_funds_all_b aa
1513: WHERE aa.owner = p_resource_id
1514: AND aa.fund_type = 'QUOTA'
1515: AND aa.status_code <> 'CANCELLED'
1516: AND NOT EXISTS ( SELECT 1

Line 1517: FROM ozf_funds_all_b bb

1513: WHERE aa.owner = p_resource_id
1514: AND aa.fund_type = 'QUOTA'
1515: AND aa.status_code <> 'CANCELLED'
1516: AND NOT EXISTS ( SELECT 1
1517: FROM ozf_funds_all_b bb
1518: WHERE bb.parent_fund_id = aa.fund_id )
1519: --
1520: UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
1521: --

Line 1523: FROM ozf_funds_all_b aa

1519: --
1520: UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
1521: --
1522: SELECT aa.fund_id
1523: FROM ozf_funds_all_b aa
1524: WHERE aa.fund_type = 'QUOTA'
1525: AND aa.status_code <> 'CANCELLED'
1526: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
1527: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id

Line 1528: FROM ozf_funds_all_b bb

1524: WHERE aa.fund_type = 'QUOTA'
1525: AND aa.status_code <> 'CANCELLED'
1526: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
1527: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
1528: FROM ozf_funds_all_b bb
1529: WHERE bb.owner = p_resource_id
1530: AND bb.fund_type = 'QUOTA'
1531: AND bb.status_code <> 'CANCELLED' )
1532: ) ;

Line 1751: FROM ozf_funds_all_b a

1747:
1748:
1749: CURSOR leaf_level_quotas_csr IS
1750: SELECT a.fund_id
1751: FROM ozf_funds_all_b a
1752: WHERE a.status_code = 'ACTIVE'
1753: AND a.fund_type = 'QUOTA'
1754: -- and a.fund_id in ( 10746 , 10745)
1755: AND NOT EXISTS ( SELECT 1

Line 1756: FROM ozf_funds_all_b b

1752: WHERE a.status_code = 'ACTIVE'
1753: AND a.fund_type = 'QUOTA'
1754: -- and a.fund_id in ( 10746 , 10745)
1755: AND NOT EXISTS ( SELECT 1
1756: FROM ozf_funds_all_b b
1757: WHERE b.parent_fund_id = a.fund_id);
1758:
1759: CURSOR quota_acct_allocs_csr (p_fund_id NUMBER) IS
1760: SELECT account_allocation_id,

Line 2000: UPDATE ozf_funds_all_b

1996:
1997:
1998: END LOOP; -- End of quota_acct_allocs_csr
1999: --
2000: UPDATE ozf_funds_all_b
2001: SET utilized_amt = l_quota_sales
2002: WHERE fund_id = quota.fund_id;
2003: --
2004: -- dbms_output.put_line('l_quota_sales '|| l_quota_sales);

Line 2056: FROM ozf_funds_all_b a

2052: CURSOR leaf_node_quotas_csr IS
2053: SELECT a.fund_id,
2054: start_date_active,
2055: end_date_active
2056: FROM ozf_funds_all_b a
2057: WHERE fund_type = 'QUOTA'
2058: AND a.status_code = 'ACTIVE'
2059: AND NOT EXISTS ( SELECT 1
2060: FROM ozf_funds_all_b b

Line 2060: FROM ozf_funds_all_b b

2056: FROM ozf_funds_all_b a
2057: WHERE fund_type = 'QUOTA'
2058: AND a.status_code = 'ACTIVE'
2059: AND NOT EXISTS ( SELECT 1
2060: FROM ozf_funds_all_b b
2061: WHERE b.parent_fund_id = a.fund_id );
2062:
2063: CURSOR parent_quotas_csr IS
2064: SELECT a.fund_id,

Line 2067: FROM ozf_funds_all_b a

2063: CURSOR parent_quotas_csr IS
2064: SELECT a.fund_id,
2065: start_date_active,
2066: end_date_active
2067: FROM ozf_funds_all_b a
2068: WHERE fund_type = 'QUOTA'
2069: AND status_code = 'ACTIVE'
2070: AND EXISTS ( SELECT 1
2071: FROM ozf_funds_all_b b

Line 2071: FROM ozf_funds_all_b b

2067: FROM ozf_funds_all_b a
2068: WHERE fund_type = 'QUOTA'
2069: AND status_code = 'ACTIVE'
2070: AND EXISTS ( SELECT 1
2071: FROM ozf_funds_all_b b
2072: WHERE b.parent_fund_id = a.fund_id );
2073:
2074: CURSOR all_leaf_node_quotas_csr( p_fund_id NUMBER ) IS
2075: SELECT a.fund_id,

Line 2077: FROM ozf_funds_all_b a

2073:
2074: CURSOR all_leaf_node_quotas_csr( p_fund_id NUMBER ) IS
2075: SELECT a.fund_id,
2076: NVL(a.utilized_amt,0) utilized_amt
2077: FROM ozf_funds_all_b a
2078: WHERE NOT EXISTS ( SELECT 'x'
2079: FROM ozf_funds_all_b b
2080: WHERE b.parent_fund_id = a.fund_id
2081: AND b.status_code = 'ACTIVE' )

Line 2079: FROM ozf_funds_all_b b

2075: SELECT a.fund_id,
2076: NVL(a.utilized_amt,0) utilized_amt
2077: FROM ozf_funds_all_b a
2078: WHERE NOT EXISTS ( SELECT 'x'
2079: FROM ozf_funds_all_b b
2080: WHERE b.parent_fund_id = a.fund_id
2081: AND b.status_code = 'ACTIVE' )
2082: AND a.status_code = 'ACTIVE'
2083: CONNECT BY PRIOR a.fund_id = a.parent_fund_id

Line 2140: UPDATE ozf_funds_all_b

2136: CLOSE xtd_sales_csr;
2137:
2138: IF (l_xtd_end_date > l_xtd_start_date )
2139: THEN
2140: UPDATE ozf_funds_all_b
2141: SET utilized_amt = (l_xtd_end_date - l_xtd_start_date )
2142: WHERE fund_id = quota.fund_id ;
2143: END IF;
2144:

Line 2159: UPDATE ozf_funds_all_b

2155: LOOP
2156: l_parent_quota_sales := l_parent_quota_sales + i.utilized_amt ;
2157: END LOOP;
2158:
2159: UPDATE ozf_funds_all_b
2160: SET utilized_amt = l_parent_quota_sales
2161: WHERE fund_id = quota.fund_id;
2162:
2163: END LOOP; -- End of parent_quotas_csr

Line 2322: FROM ozf_funds_all_b a

2318: FROM ozf_cust_daily_facts c,
2319: hz_cust_accounts x,
2320: ozf_dashb_daily_kpi kpi,
2321: (SELECT DISTINCT a.owner
2322: FROM ozf_funds_all_b a
2323: WHERE a.fund_type = 'QUOTA'
2324: AND a.status_code <> 'CANCELLED') fund
2325: WHERE c.cust_account_id = x.cust_account_id
2326: AND c.product_attribute <> 'OTHERS'

Line 2342: FROM ozf_funds_all_b aa

2338: AND prod.allocation_for_id = acct.account_allocation_id
2339: AND acct.allocation_for = 'FUND'
2340: AND acct.allocation_for_id in
2341: (SELECT aa.fund_id
2342: FROM ozf_funds_all_b aa
2343: WHERE aa.owner = kpi.resource_id
2344: AND aa.fund_type = 'QUOTA'
2345: AND aa.status_code <> 'CANCELLED'
2346: AND NOT EXISTS ( SELECT 1

Line 2347: FROM ozf_funds_all_b bb

2343: WHERE aa.owner = kpi.resource_id
2344: AND aa.fund_type = 'QUOTA'
2345: AND aa.status_code <> 'CANCELLED'
2346: AND NOT EXISTS ( SELECT 1
2347: FROM ozf_funds_all_b bb
2348: WHERE bb.parent_fund_id = aa.fund_id )
2349: UNION ALL
2350: SELECT aa.fund_id
2351: FROM ozf_funds_all_b aa

Line 2351: FROM ozf_funds_all_b aa

2347: FROM ozf_funds_all_b bb
2348: WHERE bb.parent_fund_id = aa.fund_id )
2349: UNION ALL
2350: SELECT aa.fund_id
2351: FROM ozf_funds_all_b aa
2352: WHERE aa.fund_type = 'QUOTA'
2353: AND aa.status_code <> 'CANCELLED'
2354: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2355: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id

Line 2356: FROM ozf_funds_all_b bb

2352: WHERE aa.fund_type = 'QUOTA'
2353: AND aa.status_code <> 'CANCELLED'
2354: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2355: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
2356: FROM ozf_funds_all_b bb
2357: WHERE bb.owner = kpi.resource_id
2358: AND bb.fund_type = 'QUOTA'
2359: AND bb.status_code <> 'CANCELLED')
2360: ))

Line 2423: FROM ozf_funds_all_b a

2419: FROM ozf_cust_daily_facts c,
2420: hz_cust_accounts x,
2421: ozf_dashb_daily_kpi kpi,
2422: (SELECT DISTINCT a.owner
2423: FROM ozf_funds_all_b a
2424: WHERE a.fund_type = 'QUOTA'
2425: AND a.status_code <> 'CANCELLED') fund
2426: WHERE c.cust_account_id = x.cust_account_id
2427: AND c.product_attribute <> 'OTHERS'

Line 2443: FROM ozf_funds_all_b aa

2439: AND prod.allocation_for_id = acct.account_allocation_id
2440: AND acct.allocation_for = 'FUND'
2441: AND acct.allocation_for_id in
2442: (SELECT aa.fund_id
2443: FROM ozf_funds_all_b aa
2444: WHERE aa.owner = kpi.resource_id
2445: AND aa.fund_type = 'QUOTA'
2446: AND aa.status_code <> 'CANCELLED'
2447: AND NOT EXISTS ( SELECT 1

Line 2448: FROM ozf_funds_all_b bb

2444: WHERE aa.owner = kpi.resource_id
2445: AND aa.fund_type = 'QUOTA'
2446: AND aa.status_code <> 'CANCELLED'
2447: AND NOT EXISTS ( SELECT 1
2448: FROM ozf_funds_all_b bb
2449: WHERE bb.parent_fund_id = aa.fund_id )
2450: UNION ALL
2451: SELECT aa.fund_id
2452: FROM ozf_funds_all_b aa

Line 2452: FROM ozf_funds_all_b aa

2448: FROM ozf_funds_all_b bb
2449: WHERE bb.parent_fund_id = aa.fund_id )
2450: UNION ALL
2451: SELECT aa.fund_id
2452: FROM ozf_funds_all_b aa
2453: WHERE aa.fund_type = 'QUOTA'
2454: AND aa.status_code <> 'CANCELLED'
2455: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2456: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id

Line 2457: FROM ozf_funds_all_b bb

2453: WHERE aa.fund_type = 'QUOTA'
2454: AND aa.status_code <> 'CANCELLED'
2455: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2456: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
2457: FROM ozf_funds_all_b bb
2458: WHERE bb.owner = kpi.resource_id
2459: AND bb.fund_type = 'QUOTA'
2460: AND bb.status_code <> 'CANCELLED')
2461: ))

Line 2524: FROM ozf_funds_all_b a

2520: FROM ozf_cust_daily_facts c,
2521: hz_cust_accounts x,
2522: ozf_dashb_daily_kpi kpi,
2523: (SELECT DISTINCT a.owner
2524: FROM ozf_funds_all_b a
2525: WHERE a.fund_type = 'QUOTA'
2526: AND a.status_code <> 'CANCELLED') fund
2527: WHERE c.cust_account_id = x.cust_account_id
2528: AND c.product_attribute <> 'OTHERS'

Line 2544: FROM ozf_funds_all_b aa

2540: AND prod.allocation_for_id = acct.account_allocation_id
2541: AND acct.allocation_for = 'FUND'
2542: AND acct.allocation_for_id in
2543: (SELECT aa.fund_id
2544: FROM ozf_funds_all_b aa
2545: WHERE aa.owner = kpi.resource_id
2546: AND aa.fund_type = 'QUOTA'
2547: AND aa.status_code <> 'CANCELLED'
2548: AND NOT EXISTS ( SELECT 1

Line 2549: FROM ozf_funds_all_b bb

2545: WHERE aa.owner = kpi.resource_id
2546: AND aa.fund_type = 'QUOTA'
2547: AND aa.status_code <> 'CANCELLED'
2548: AND NOT EXISTS ( SELECT 1
2549: FROM ozf_funds_all_b bb
2550: WHERE bb.parent_fund_id = aa.fund_id )
2551: UNION ALL
2552: SELECT aa.fund_id
2553: FROM ozf_funds_all_b aa

Line 2553: FROM ozf_funds_all_b aa

2549: FROM ozf_funds_all_b bb
2550: WHERE bb.parent_fund_id = aa.fund_id )
2551: UNION ALL
2552: SELECT aa.fund_id
2553: FROM ozf_funds_all_b aa
2554: WHERE aa.fund_type = 'QUOTA'
2555: AND aa.status_code <> 'CANCELLED'
2556: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2557: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id

Line 2558: FROM ozf_funds_all_b bb

2554: WHERE aa.fund_type = 'QUOTA'
2555: AND aa.status_code <> 'CANCELLED'
2556: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2557: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
2558: FROM ozf_funds_all_b bb
2559: WHERE bb.owner = kpi.resource_id
2560: AND bb.fund_type = 'QUOTA'
2561: AND bb.status_code <> 'CANCELLED')
2562: ))

Line 2625: FROM ozf_funds_all_b a

2621: -1 LAST_UPDATE_LOGIN
2622: FROM ozf_cust_daily_facts c ,
2623: ozf_dashb_daily_kpi kpi,
2624: (SELECT DISTINCT a.owner
2625: FROM ozf_funds_all_b a
2626: WHERE a.fund_type = 'QUOTA'
2627: AND a.status_code <> 'CANCELLED') fund
2628: WHERE
2629: kpi.sequence_number = 1

Line 2644: FROM ozf_funds_all_b aa

2640: AND prod.allocation_for_id = acct.account_allocation_id
2641: AND acct.allocation_for = 'FUND'
2642: AND acct.allocation_for_id in
2643: (SELECT aa.fund_id
2644: FROM ozf_funds_all_b aa
2645: WHERE aa.owner = kpi.resource_id
2646: AND aa.fund_type = 'QUOTA'
2647: AND aa.status_code <> 'CANCELLED'
2648: AND NOT EXISTS ( SELECT 1

Line 2649: FROM ozf_funds_all_b bb

2645: WHERE aa.owner = kpi.resource_id
2646: AND aa.fund_type = 'QUOTA'
2647: AND aa.status_code <> 'CANCELLED'
2648: AND NOT EXISTS ( SELECT 1
2649: FROM ozf_funds_all_b bb
2650: WHERE bb.parent_fund_id = aa.fund_id )
2651: UNION ALL
2652: SELECT aa.fund_id
2653: FROM ozf_funds_all_b aa

Line 2653: FROM ozf_funds_all_b aa

2649: FROM ozf_funds_all_b bb
2650: WHERE bb.parent_fund_id = aa.fund_id )
2651: UNION ALL
2652: SELECT aa.fund_id
2653: FROM ozf_funds_all_b aa
2654: WHERE aa.fund_type = 'QUOTA'
2655: AND aa.status_code <> 'CANCELLED'
2656: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2657: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id

Line 2658: FROM ozf_funds_all_b bb

2654: WHERE aa.fund_type = 'QUOTA'
2655: AND aa.status_code <> 'CANCELLED'
2656: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2657: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
2658: FROM ozf_funds_all_b bb
2659: WHERE bb.owner = kpi.resource_id
2660: AND bb.fund_type = 'QUOTA'
2661: AND bb.status_code <> 'CANCELLED')
2662: ))

Line 3839: FROM ozf_funds_all_b

3835: ozf_time_allocations b
3836: WHERE a.allocation_for = 'FUND'
3837: AND a.allocation_for_id IN (
3838: SELECT fund_id
3839: FROM ozf_funds_all_b
3840: WHERE parent_fund_id IS NOT NULL
3841: AND start_date_active >= p_start_date
3842: AND end_date_active <= p_end_date )
3843: AND b.allocation_for = 'CUST'

Line 3967: FROM ozf_funds_all_b aa

3963: -- R12: Do not consider UnAllocated Rows
3964: AND acct.parent_party_id <> -9999
3965: AND acct.allocation_for_id IN (
3966: SELECT aa.fund_id
3967: FROM ozf_funds_all_b aa
3968: WHERE aa.owner = p_resource_id
3969: AND aa.fund_type = 'QUOTA'
3970: AND aa.status_code <> 'CANCELLED'
3971: AND NOT EXISTS ( SELECT 1

Line 3972: FROM ozf_funds_all_b bb

3968: WHERE aa.owner = p_resource_id
3969: AND aa.fund_type = 'QUOTA'
3970: AND aa.status_code <> 'CANCELLED'
3971: AND NOT EXISTS ( SELECT 1
3972: FROM ozf_funds_all_b bb
3973: WHERE bb.parent_fund_id = aa.fund_id )
3974: --
3975: UNION ALL-- Get all leaf node quotas in the hierarchy of this resource
3976: --

Line 3978: FROM ozf_funds_all_b aa

3974: --
3975: UNION ALL-- Get all leaf node quotas in the hierarchy of this resource
3976: --
3977: SELECT aa.fund_id
3978: FROM ozf_funds_all_b aa
3979: WHERE aa.fund_type = 'QUOTA'
3980: AND aa.status_code <> 'CANCELLED'
3981: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
3982: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id

Line 3983: FROM ozf_funds_all_b bb

3979: WHERE aa.fund_type = 'QUOTA'
3980: AND aa.status_code <> 'CANCELLED'
3981: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
3982: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
3983: FROM ozf_funds_all_b bb
3984: WHERE bb.owner = p_resource_id
3985: AND bb.fund_type = 'QUOTA'
3986: AND bb.status_code <> 'CANCELLED' )
3987: );

Line 4005: FROM ozf_funds_all_b aa

4001: -- R12: Do not consider UnAllocated Rows
4002: AND a.parent_party_id <> -9999
4003: AND a.allocation_for_id IN ( -- Get leaf node quotas for this resource owns
4004: SELECT aa.fund_id
4005: FROM ozf_funds_all_b aa
4006: WHERE aa.owner = p_resource_id
4007: AND aa.fund_type = 'QUOTA'
4008: AND aa.status_code <> 'CANCELLED'
4009: AND NOT EXISTS ( SELECT 1

Line 4010: FROM ozf_funds_all_b bb

4006: WHERE aa.owner = p_resource_id
4007: AND aa.fund_type = 'QUOTA'
4008: AND aa.status_code <> 'CANCELLED'
4009: AND NOT EXISTS ( SELECT 1
4010: FROM ozf_funds_all_b bb
4011: WHERE bb.parent_fund_id = aa.fund_id )
4012: --
4013: UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
4014: --

Line 4016: FROM ozf_funds_all_b aa

4012: --
4013: UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
4014: --
4015: SELECT aa.fund_id
4016: FROM ozf_funds_all_b aa
4017: WHERE aa.fund_type = 'QUOTA'
4018: AND aa.status_code <> 'CANCELLED'
4019: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
4020: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id

Line 4021: FROM ozf_funds_all_b bb

4017: WHERE aa.fund_type = 'QUOTA'
4018: AND aa.status_code <> 'CANCELLED'
4019: CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
4020: START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
4021: FROM ozf_funds_all_b bb
4022: WHERE bb.owner = p_resource_id
4023: AND bb.fund_type = 'QUOTA'
4024: AND bb.status_code <> 'CANCELLED' )
4025: );