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_ALLOCATION_ENGINE_PVT dependencies on OZF_TIME_ALLOCATIONS

Line 62: SELECT ozf_time_allocations_s.NEXTVAL

58: l_api_name CONSTANT VARCHAR2(30) := 'get_time_allocation_id';
59: l_full_api_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
60:
61: CURSOR time_seq_csr IS
62: SELECT ozf_time_allocations_s.NEXTVAL
63: FROM DUAL;
64:
65: CURSOR time_alloc_count_csr(p_time_alloc_id in number) IS
66: SELECT count(t.time_allocation_id)

Line 67: FROM ozf_time_allocations t

63: FROM DUAL;
64:
65: CURSOR time_alloc_count_csr(p_time_alloc_id in number) IS
66: SELECT count(t.time_allocation_id)
67: FROM ozf_time_allocations t
68: WHERE t.time_allocation_id = p_time_alloc_id;
69:
70: l_count number := -1;
71: l_time_alloc_id number := -1;

Line 687: UPDATE OZF_TIME_ALLOCATIONS t

683:
684:
685: l_temp_product_allocation_id := 0;
686:
687: UPDATE OZF_TIME_ALLOCATIONS t
688: SET t.TARGET = t.TARGET + l_diff_target,
689: t.object_version_number = t.object_version_number + 1,
690: t.last_update_date = SYSDATE,
691: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 694: FROM OZF_TIME_ALLOCATIONS x

690: t.last_update_date = SYSDATE,
691: t.last_updated_by = FND_GLOBAL.USER_ID,
692: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
693: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id)
694: FROM OZF_TIME_ALLOCATIONS x
695: WHERE x.allocation_for = 'PROD'
696: AND x.allocation_for_id IN ( SELECT max(p.product_allocation_id)
697: FROM OZF_PRODUCT_ALLOCATIONS p
698: WHERE p.allocation_for = l_object_type

Line 710: FROM OZF_TIME_ALLOCATIONS zx

706:
707: )
708: AND x.target =
709: (SELECT max(zx.target)
710: FROM OZF_TIME_ALLOCATIONS zx
711: WHERE zx.allocation_for = 'PROD'
712: AND zx.allocation_for_id IN (SELECT max(pz.product_allocation_id)
713: FROM OZF_PRODUCT_ALLOCATIONS pz
714: WHERE pz.allocation_for = l_object_type

Line 758: UPDATE OZF_TIME_ALLOCATIONS t

754: RAISE fnd_api.g_exc_unexpected_error;
755: END IF;
756:
757: /*---------------------------------------------------------------------------------------------------
758: UPDATE OZF_TIME_ALLOCATIONS t
759: SET t.TARGET = t.TARGET + l_diff_target,
760: t.object_version_number = t.object_version_number + 1,
761: t.last_update_date = SYSDATE,
762: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 764: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x

760: t.object_version_number = t.object_version_number + 1,
761: t.last_update_date = SYSDATE,
762: t.last_updated_by = FND_GLOBAL.USER_ID,
763: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
764: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
765: WHERE x.allocation_for = 'PROD'
766: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
767: FROM OZF_PRODUCT_ALLOCATIONS p
768: WHERE p.allocation_for = 'CUST'

Line 869: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

865: l_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
866: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
867:
868: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
869: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
870:
871:
872: CURSOR fact_csr
873: (l_fact_id number) IS

Line 939: ozf_time_allocations t,

935: (l_fund_id NUMBER,
936: l_in_clause VARCHAR2) IS
937: SELECT SUM(t.target)
938: FROM
939: ozf_time_allocations t,
940: ozf_product_allocations p
941: WHERE
942: p.fund_id = l_fund_id
943: AND t.allocation_for_id = p.product_allocation_id

Line 951: ' ozf_time_allocations t,'||

947:
948: l_get_total_target_sql VARCHAR2(30000) :=
949: ' SELECT SUM(t.target) '||
950: ' FROM '||
951: ' ozf_time_allocations t,'||
952: ' ozf_product_allocations p'||
953: ' WHERE'||
954: ' p.fund_id = :l_fund_id'||
955: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 981: ozf_time_allocations t

977: (l_product_allocation_id number,
978: l_time_id number) IS
979: SELECT t.target
980: FROM
981: ozf_time_allocations t
982: WHERE
983: t.allocation_for_id = l_product_allocation_id
984: AND t.allocation_for = 'PROD'
985: AND t.time_id = l_time_id;

Line 1344: Ozf_Time_Allocations_Pkg.Insert_Row(

1340: p_time_alloc_rec.target := NVL(l_time_quota, 0);
1341: p_time_alloc_rec.lysp_sales := NVL(l_lysp_sales, 0);
1342:
1343:
1344: Ozf_Time_Allocations_Pkg.Insert_Row(
1345: px_time_allocation_id => l_time_allocation_id,
1346: p_allocation_for => p_time_alloc_rec.allocation_for,
1347: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
1348: p_time_id => p_time_alloc_rec.time_id,

Line 1415: UPDATE OZF_TIME_ALLOCATIONS t

1411: END;
1412:
1413: IF l_diff_target <> 0 THEN
1414:
1415: UPDATE OZF_TIME_ALLOCATIONS t
1416: SET t.TARGET = t.TARGET + l_diff_target,
1417: t.object_version_number = t.object_version_number + 1,
1418: t.last_update_date = SYSDATE,
1419: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 1421: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x

1417: t.object_version_number = t.object_version_number + 1,
1418: t.last_update_date = SYSDATE,
1419: t.last_updated_by = FND_GLOBAL.USER_ID,
1420: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
1421: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
1422: WHERE x.allocation_for = 'PROD'
1423: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
1424: FROM OZF_PRODUCT_ALLOCATIONS p
1425: WHERE p.allocation_for = 'FACT'

Line 1580: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

1576: l_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
1577: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
1578:
1579: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
1580: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
1581:
1582:
1583: CURSOR fact_csr
1584: (l_fact_id number) IS

Line 1650: ozf_time_allocations t,

1646: (l_fund_id NUMBER,
1647: l_in_clause VARCHAR2) IS
1648: SELECT SUM(t.target)
1649: FROM
1650: ozf_time_allocations t,
1651: ozf_product_allocations p
1652: WHERE
1653: p.fund_id = l_fund_id
1654: AND t.allocation_for_id = p.product_allocation_id

Line 1662: ' ozf_time_allocations t,'||

1658:
1659: l_get_total_target_sql VARCHAR2(30000) :=
1660: ' SELECT SUM(t.target) '||
1661: ' FROM '||
1662: ' ozf_time_allocations t,'||
1663: ' ozf_product_allocations p'||
1664: ' WHERE'||
1665: ' p.fund_id = :l_fund_id'||
1666: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 1692: ozf_time_allocations t

1688: (l_product_allocation_id number,
1689: l_time_id number) IS
1690: SELECT t.target
1691: FROM
1692: ozf_time_allocations t
1693: WHERE
1694: t.allocation_for_id = l_product_allocation_id
1695: AND t.allocation_for = 'PROD'
1696: AND t.time_id = l_time_id;

Line 2067: Ozf_Time_Allocations_Pkg.Insert_Row(

2063: p_time_alloc_rec.target := 0; --------NVL(l_time_quota, 0);
2064: p_time_alloc_rec.lysp_sales := NVL(l_lysp_sales, 0);
2065:
2066:
2067: Ozf_Time_Allocations_Pkg.Insert_Row(
2068: px_time_allocation_id => l_time_allocation_id,
2069: p_allocation_for => p_time_alloc_rec.allocation_for,
2070: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
2071: p_time_id => p_time_alloc_rec.time_id,

Line 2128: UPDATE OZF_TIME_ALLOCATIONS t

2124: END IF;
2125:
2126: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor is '|| l_multiplying_factor);
2127:
2128: UPDATE OZF_TIME_ALLOCATIONS t
2129: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_multiplying_factor), 0),
2130: t.object_version_number = t.object_version_number + 1,
2131: t.last_update_date = SYSDATE,
2132: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 2151: FROM OZF_TIME_ALLOCATIONS ti

2147: END IF;
2148:
2149: UPDATE OZF_PRODUCT_ALLOCATIONS p
2150: SET p.TARGET = (SELECT SUM(ti.TARGET)
2151: FROM OZF_TIME_ALLOCATIONS ti
2152: WHERE ti.ALLOCATION_FOR = 'PROD'
2153: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
2154: p.object_version_number = p.object_version_number + 1,
2155: p.last_update_date = SYSDATE,

Line 2200: UPDATE OZF_TIME_ALLOCATIONS t

2196:
2197: RAISE fnd_api.g_exc_unexpected_error;
2198: END IF;
2199: */
2200: UPDATE OZF_TIME_ALLOCATIONS t
2201: SET t.TARGET = ROUND((NVL(l_fact_rec.recommend_total_amount, 0) / l_denominator), 0),
2202: t.object_version_number = t.object_version_number + 1,
2203: t.last_update_date = SYSDATE,
2204: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 2223: FROM OZF_TIME_ALLOCATIONS ti

2219: END IF;
2220:
2221: UPDATE OZF_PRODUCT_ALLOCATIONS p
2222: SET p.TARGET = (SELECT SUM(ti.TARGET)
2223: FROM OZF_TIME_ALLOCATIONS ti
2224: WHERE ti.ALLOCATION_FOR = 'PROD'
2225: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
2226: p.object_version_number = p.object_version_number + 1,
2227: p.last_update_date = SYSDATE,

Line 2278: UPDATE OZF_TIME_ALLOCATIONS t

2274:
2275: fix_product_rounding_err('FACT', l_fact_id, l_diff_target);
2276:
2277: /*
2278: UPDATE OZF_TIME_ALLOCATIONS t
2279: SET t.TARGET = t.TARGET + l_diff_target,
2280: t.object_version_number = t.object_version_number + 1,
2281: t.last_update_date = SYSDATE,
2282: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 2284: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x

2280: t.object_version_number = t.object_version_number + 1,
2281: t.last_update_date = SYSDATE,
2282: t.last_updated_by = FND_GLOBAL.USER_ID,
2283: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
2284: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
2285: WHERE x.allocation_for = 'PROD'
2286: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
2287: FROM OZF_PRODUCT_ALLOCATIONS p
2288: WHERE p.allocation_for = 'FACT'

Line 2433: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

2429: l_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
2430: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
2431:
2432: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
2433: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
2434:
2435:
2436: CURSOR fund_csr (l_fund_id NUMBER) IS
2437: SELECT

Line 2805: Ozf_Time_Allocations_Pkg.Insert_Row(

2801: p_time_alloc_rec.target := 0;
2802: p_time_alloc_rec.lysp_sales := l_lysp_sales;
2803:
2804:
2805: Ozf_Time_Allocations_Pkg.Insert_Row(
2806: px_time_allocation_id => l_time_allocation_id,
2807: p_allocation_for => p_time_alloc_rec.allocation_for,
2808: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
2809: p_time_id => p_time_alloc_rec.time_id,

Line 2942: Ozf_Time_Allocations_Pkg.Insert_Row(

2938: p_time_alloc_rec.target := 0;
2939: p_time_alloc_rec.lysp_sales := l_lysp_sales;
2940:
2941:
2942: Ozf_Time_Allocations_Pkg.Insert_Row(
2943: px_time_allocation_id => l_time_allocation_id,
2944: p_allocation_for => p_time_alloc_rec.allocation_for,
2945: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
2946: p_time_id => p_time_alloc_rec.time_id,

Line 3073: Ozf_Time_Allocations_Pkg.Insert_Row(

3069: p_time_alloc_rec.target := 0;
3070: p_time_alloc_rec.lysp_sales := l_lysp_sales;
3071:
3072:
3073: Ozf_Time_Allocations_Pkg.Insert_Row(
3074: px_time_allocation_id => l_time_allocation_id,
3075: p_allocation_for => p_time_alloc_rec.allocation_for,
3076: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
3077: p_time_id => p_time_alloc_rec.time_id,

Line 3164: UPDATE OZF_TIME_ALLOCATIONS t

3160: RAISE fnd_api.g_exc_unexpected_error;
3161: END IF;
3162: */
3163:
3164: UPDATE OZF_TIME_ALLOCATIONS t
3165: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_multiplying_factor), 0),
3166: t.object_version_number = t.object_version_number + 1,
3167: t.last_update_date = SYSDATE,
3168: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 3188: FROM OZF_TIME_ALLOCATIONS ti

3184:
3185:
3186: UPDATE OZF_PRODUCT_ALLOCATIONS p
3187: SET p.TARGET = (SELECT SUM(ti.TARGET)
3188: FROM OZF_TIME_ALLOCATIONS ti
3189: WHERE ti.ALLOCATION_FOR = 'PROD'
3190: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
3191: p.object_version_number = p.object_version_number + 1,
3192: p.last_update_date = SYSDATE,

Line 3238: UPDATE OZF_TIME_ALLOCATIONS t

3234:
3235: RAISE fnd_api.g_exc_unexpected_error;
3236: END IF;
3237: */
3238: UPDATE OZF_TIME_ALLOCATIONS t
3239: SET t.TARGET = ROUND((l_total_root_quota / l_denominator), 0),
3240: t.object_version_number = t.object_version_number + 1,
3241: t.last_update_date = SYSDATE,
3242: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 3262: FROM OZF_TIME_ALLOCATIONS ti

3258:
3259:
3260: UPDATE OZF_PRODUCT_ALLOCATIONS p
3261: SET p.TARGET = (SELECT SUM(ti.TARGET)
3262: FROM OZF_TIME_ALLOCATIONS ti
3263: WHERE ti.ALLOCATION_FOR = 'PROD'
3264: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
3265: p.object_version_number = p.object_version_number + 1,
3266: p.last_update_date = SYSDATE,

Line 3301: FROM OZF_TIME_ALLOCATIONS t

3297: AND p.item_id = -9999;
3298:
3299:
3300: SELECT SUM(t.TARGET) INTO l_diff_target_2
3301: FROM OZF_TIME_ALLOCATIONS t
3302: WHERE t.allocation_for = 'PROD'
3303: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
3304: FROM OZF_PRODUCT_ALLOCATIONS p
3305: WHERE p.allocation_for = 'FUND'

Line 3332: FROM OZF_TIME_ALLOCATIONS t

3328: AND p.item_id = -9999
3329: ) a,
3330: (
3331: SELECT SUM(t.TARGET) target
3332: FROM OZF_TIME_ALLOCATIONS t
3333: WHERE t.allocation_for = 'PROD'
3334: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
3335: FROM OZF_PRODUCT_ALLOCATIONS p
3336: WHERE p.allocation_for = 'FUND'

Line 3356: FROM OZF_TIME_ALLOCATIONS t

3352: )
3353: -
3354: (
3355: SELECT SUM(t.TARGET)
3356: FROM OZF_TIME_ALLOCATIONS t
3357: WHERE t.allocation_for = 'PROD'
3358: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
3359: FROM OZF_PRODUCT_ALLOCATIONS p
3360: WHERE p.allocation_for = 'FUND'

Line 3418: UPDATE OZF_TIME_ALLOCATIONS t

3414: l_diff_target := 0;
3415: l_diff_target := MOD(l_total_root_quota,l_denominator);
3416:
3417: IF l_diff_target <> 0 THEN
3418: UPDATE OZF_TIME_ALLOCATIONS t
3419: SET t.TARGET = t.TARGET + l_diff_target,
3420: t.object_version_number = t.object_version_number + 1,
3421: t.last_update_date = SYSDATE,
3422: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 3424: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x

3420: t.object_version_number = t.object_version_number + 1,
3421: t.last_update_date = SYSDATE,
3422: t.last_updated_by = FND_GLOBAL.USER_ID,
3423: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
3424: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
3425: WHERE x.allocation_for = 'PROD'
3426: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
3427: FROM OZF_PRODUCT_ALLOCATIONS p
3428: WHERE p.allocation_for = 'FUND'

Line 3720: DELETE ozf_time_allocations t

3716: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
3717:
3718: FOR fact_product_spread_rec IN fact_product_spread_csr(l_fact_id)
3719: LOOP
3720: DELETE ozf_time_allocations t
3721: WHERE t.allocation_for_id = fact_product_spread_rec.product_allocation_id
3722: AND t.allocation_for = 'PROD';
3723: END LOOP;
3724:

Line 3933: DELETE ozf_time_allocations t

3929: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3930:
3931: FOR fund_product_spread_rec IN fund_product_spread_csr(l_fund_id)
3932: LOOP
3933: DELETE ozf_time_allocations t
3934: WHERE t.allocation_for_id = fund_product_spread_rec.product_allocation_id
3935: AND t.allocation_for = 'PROD';
3936: END LOOP;
3937:

Line 4029: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

4025: l_object_version_number NUMBER := 1;
4026: l_org_id NUMBER; -- := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)); --Bugfix 7540057
4027:
4028: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
4029: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
4030:
4031:
4032: CURSOR fund_csr(l_fund_id NUMBER) IS
4033: SELECT

Line 4117: FROM OZF_TIME_ALLOCATIONS

4113: time_id,
4114: period_type_id,
4115: target,
4116: lysp_sales
4117: FROM OZF_TIME_ALLOCATIONS
4118: WHERE allocation_for = 'PROD'
4119: AND allocation_for_id = l_prod_alloc_id;
4120:
4121: l_time_alloc_rec time_alloc_csr%ROWTYPE;

Line 4329: Ozf_Time_Allocations_Pkg.Insert_Row(

4325: p_time_alloc_rec.target := 0;
4326: p_time_alloc_rec.lysp_sales := l_lysp_sales;
4327:
4328:
4329: Ozf_Time_Allocations_Pkg.Insert_Row(
4330: px_time_allocation_id => l_time_allocation_id,
4331: p_allocation_for => p_time_alloc_rec.allocation_for,
4332: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
4333: p_time_id => p_time_alloc_rec.time_id,

Line 4468: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

4464: l_object_version_number NUMBER := 1;
4465: l_org_id NUMBER; -- := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)); --Bugfix 7540057
4466:
4467: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
4468: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
4469:
4470: CURSOR fund_csr(l_fund_id NUMBER) IS
4471: SELECT
4472: owner,

Line 4559: FROM OZF_TIME_ALLOCATIONS

4555: time_id,
4556: period_type_id,
4557: target,
4558: lysp_sales
4559: FROM OZF_TIME_ALLOCATIONS
4560: WHERE allocation_for = 'PROD'
4561: AND allocation_for_id = l_prod_alloc_id;
4562:
4563: l_time_alloc_rec time_alloc_csr%ROWTYPE;

Line 4697: UPDATE ozf_time_allocations t -- Update Others Quota for Jul03, Aug03, Sep03 etc

4693:
4694: FOR time_alloc_rec IN time_alloc_csr(l_prod_alloc_rec.product_allocation_id)
4695: LOOP
4696:
4697: UPDATE ozf_time_allocations t -- Update Others Quota for Jul03, Aug03, Sep03 etc
4698: SET t.target = t.target + NVL(time_alloc_rec.target, 0),
4699: t.object_version_number = t.object_version_number + 1,
4700: t.last_update_date = SYSDATE,
4701: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 4723: DELETE ozf_time_allocations t

4719: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Updating OTHERS Product and Time Allocations Records'
4720: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4721:
4722:
4723: DELETE ozf_time_allocations t
4724: WHERE t.allocation_for_id = l_prod_alloc_rec.product_allocation_id
4725: AND t.allocation_for = 'PROD';
4726:
4727: DELETE ozf_product_allocations p

Line 4857: DELETE ozf_time_allocations t

4853: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
4854:
4855: FOR account_rec IN fund_account_spread_csr(l_fund_id)
4856: LOOP
4857: DELETE ozf_time_allocations t
4858: WHERE t.allocation_for_id = account_rec.account_allocation_id
4859: AND t.allocation_for = 'CUST';
4860:
4861: FOR product_rec IN acct_product_spread_csr(account_rec.account_allocation_id)

Line 4863: DELETE ozf_time_allocations t

4859: AND t.allocation_for = 'CUST';
4860:
4861: FOR product_rec IN acct_product_spread_csr(account_rec.account_allocation_id)
4862: LOOP
4863: DELETE ozf_time_allocations t
4864: WHERE t.allocation_for_id = product_rec.product_allocation_id
4865: AND t.allocation_for = 'PROD';
4866: END LOOP;
4867:

Line 4990: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

4986: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
4987:
4988: p_acct_alloc_rec ozf_account_allocations%ROWTYPE;
4989: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
4990: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
4991:
4992:
4993:
4994: CURSOR fund_csr (l_fund_id NUMBER)

Line 5142: ozf_time_allocations t,

5138: (l_fund_id NUMBER,
5139: l_in_clause VARCHAR2) IS
5140: SELECT SUM(t.target)
5141: FROM
5142: ozf_time_allocations t,
5143: ozf_product_allocations p
5144: WHERE
5145: p.fund_id = l_fund_id
5146: AND t.allocation_for_id = p.product_allocation_id

Line 5154: ' ozf_time_allocations t,'||

5150:
5151: l_get_total_target_sql VARCHAR2(30000) :=
5152: ' SELECT SUM(t.target) '||
5153: ' FROM '||
5154: ' ozf_time_allocations t,'||
5155: ' ozf_product_allocations p'||
5156: ' WHERE'||
5157: ' p.fund_id = :l_fund_id'||
5158: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 5280: ozf_time_allocations t

5276: (l_product_allocation_id number,
5277: l_time_id number) IS
5278: SELECT t.target
5279: FROM
5280: ozf_time_allocations t
5281: WHERE
5282: t.allocation_for_id = l_product_allocation_id
5283: AND t.allocation_for = 'PROD'
5284: AND t.time_id = l_time_id;

Line 5547: Ozf_Time_Allocations_Pkg.Insert_Row(

5543: p_time_alloc_rec.lysp_sales := l_account_sales;
5544:
5545: l_time_allocation_id := get_time_allocation_id;
5546:
5547: Ozf_Time_Allocations_Pkg.Insert_Row(
5548: px_time_allocation_id => l_time_allocation_id,
5549: p_allocation_for => p_time_alloc_rec.allocation_for,
5550: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
5551: p_time_id => p_time_alloc_rec.time_id,

Line 5713: Ozf_Time_Allocations_Pkg.Insert_Row(

5709: p_time_alloc_rec.lysp_sales := l_product_sales;
5710:
5711: l_time_allocation_id := get_time_allocation_id;
5712:
5713: Ozf_Time_Allocations_Pkg.Insert_Row(
5714: px_time_allocation_id => l_time_allocation_id,
5715: p_allocation_for => p_time_alloc_rec.allocation_for,
5716: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
5717: p_time_id => p_time_alloc_rec.time_id,

Line 5865: Ozf_Time_Allocations_Pkg.Insert_Row(

5861: p_time_alloc_rec.lysp_sales := 0;
5862:
5863: l_time_allocation_id := get_time_allocation_id;
5864:
5865: Ozf_Time_Allocations_Pkg.Insert_Row(
5866: px_time_allocation_id => l_time_allocation_id,
5867: p_allocation_for => p_time_alloc_rec.allocation_for,
5868: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
5869: p_time_id => p_time_alloc_rec.time_id,

Line 5917: FROM OZF_TIME_ALLOCATIONS t

5913: AND a.allocation_for_id = l_fund_id
5914: AND a.parent_party_id = -9999;
5915:
5916: SELECT SUM(t.TARGET) INTO l_diff_target_2
5917: FROM OZF_TIME_ALLOCATIONS t
5918: WHERE t.allocation_for = 'CUST'
5919: AND t.allocation_for_id IN ( SELECT a.account_allocation_id
5920: FROM OZF_ACCOUNT_ALLOCATIONS a
5921: WHERE a.allocation_for = 'FUND'

Line 5935: UPDATE OZF_TIME_ALLOCATIONS t

5931:
5932:
5933: IF l_diff_target <> 0 THEN
5934:
5935: UPDATE OZF_TIME_ALLOCATIONS t
5936: SET t.TARGET = t.TARGET + l_diff_target,
5937: t.object_version_number = t.object_version_number + 1,
5938: t.last_update_date = SYSDATE,
5939: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 5941: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x

5937: t.object_version_number = t.object_version_number + 1,
5938: t.last_update_date = SYSDATE,
5939: t.last_updated_by = FND_GLOBAL.USER_ID,
5940: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
5941: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
5942: WHERE x.allocation_for = 'CUST'
5943: AND x.allocation_for_id IN ( SELECT a.account_allocation_id
5944: FROM OZF_ACCOUNT_ALLOCATIONS a
5945: WHERE a.allocation_for = 'FUND'

Line 6101: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

6097: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
6098:
6099: p_acct_alloc_rec ozf_account_allocations%ROWTYPE;
6100: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
6101: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
6102:
6103:
6104:
6105: CURSOR fund_csr (l_fund_id NUMBER)

Line 6254: ozf_time_allocations t,

6250: (l_fund_id NUMBER,
6251: l_in_clause VARCHAR2) IS
6252: SELECT SUM(t.target)
6253: FROM
6254: ozf_time_allocations t,
6255: ozf_product_allocations p
6256: WHERE
6257: p.fund_id = l_fund_id
6258: AND t.allocation_for_id = p.product_allocation_id

Line 6266: ' ozf_time_allocations t,'||

6262:
6263: l_get_total_target_sql VARCHAR2(30000) :=
6264: ' SELECT SUM(t.target) '||
6265: ' FROM '||
6266: ' ozf_time_allocations t,'||
6267: ' ozf_product_allocations p'||
6268: ' WHERE'||
6269: ' p.fund_id = :l_fund_id'||
6270: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 6392: ozf_time_allocations t

6388: (l_product_allocation_id number,
6389: l_time_id number) IS
6390: SELECT t.target
6391: FROM
6392: ozf_time_allocations t
6393: WHERE
6394: t.allocation_for_id = l_product_allocation_id
6395: AND t.allocation_for = 'PROD'
6396: AND t.time_id = l_time_id;

Line 6659: Ozf_Time_Allocations_Pkg.Insert_Row(

6655: p_time_alloc_rec.lysp_sales := l_account_sales;
6656:
6657: l_time_allocation_id := get_time_allocation_id;
6658:
6659: Ozf_Time_Allocations_Pkg.Insert_Row(
6660: px_time_allocation_id => l_time_allocation_id,
6661: p_allocation_for => p_time_alloc_rec.allocation_for,
6662: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
6663: p_time_id => p_time_alloc_rec.time_id,

Line 6703: FROM OZF_TIME_ALLOCATIONS ti

6699:
6700: BEGIN
6701:
6702: SELECT SUM(ti.TARGET) into l_total_account_target
6703: FROM OZF_TIME_ALLOCATIONS ti
6704: WHERE ti.ALLOCATION_FOR = 'CUST'
6705: AND ti.ALLOCATION_FOR_ID = l_account_allocation_id;
6706:
6707: UPDATE OZF_ACCOUNT_ALLOCATIONS a

Line 6870: Ozf_Time_Allocations_Pkg.Insert_Row(

6866: p_time_alloc_rec.lysp_sales := l_product_sales;
6867:
6868: l_time_allocation_id := get_time_allocation_id;
6869:
6870: Ozf_Time_Allocations_Pkg.Insert_Row(
6871: px_time_allocation_id => l_time_allocation_id,
6872: p_allocation_for => p_time_alloc_rec.allocation_for,
6873: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
6874: p_time_id => p_time_alloc_rec.time_id,

Line 6934: UPDATE OZF_TIME_ALLOCATIONS t

6930: -- OZF_UTILITY_PVT.debug_message('1. mkothari --'||' -- l_prod_mltply_factor==> '||l_prod_mltply_factor || ' ; ');
6931: -- OZF_UTILITY_PVT.debug_message('2. mkothari --'||' l_total_account_target===> '||l_total_account_target);
6932: -- OZF_UTILITY_PVT.debug_message('3. mkothari --'||' l_p_grand_total_lysp_sales='||l_p_grand_total_lysp_sales);
6933:
6934: UPDATE OZF_TIME_ALLOCATIONS t
6935: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_prod_mltply_factor), 0),
6936: t.object_version_number = t.object_version_number + 1,
6937: t.last_update_date = SYSDATE,
6938: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 6957: FROM OZF_TIME_ALLOCATIONS ti

6953: END IF;
6954:
6955: UPDATE OZF_PRODUCT_ALLOCATIONS p
6956: SET p.TARGET = (SELECT SUM(ti.TARGET)
6957: FROM OZF_TIME_ALLOCATIONS ti
6958: WHERE ti.ALLOCATION_FOR = 'PROD'
6959: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
6960: p.object_version_number = p.object_version_number + 1,
6961: p.last_update_date = SYSDATE,

Line 7007: UPDATE OZF_TIME_ALLOCATIONS t

7003: RAISE fnd_api.g_exc_unexpected_error;
7004: END IF;
7005: */
7006:
7007: UPDATE OZF_TIME_ALLOCATIONS t
7008: SET t.TARGET = ROUND((NVL(l_total_account_target, 0) / l_p_denominator), 0),
7009: t.object_version_number = t.object_version_number + 1,
7010: t.last_update_date = SYSDATE,
7011: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7030: FROM OZF_TIME_ALLOCATIONS ti

7026: END IF;
7027:
7028: UPDATE OZF_PRODUCT_ALLOCATIONS p
7029: SET p.TARGET = (SELECT SUM(ti.TARGET)
7030: FROM OZF_TIME_ALLOCATIONS ti
7031: WHERE ti.ALLOCATION_FOR = 'PROD'
7032: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
7033: p.object_version_number = p.object_version_number + 1,
7034: p.last_update_date = SYSDATE,

Line 7091: UPDATE OZF_TIME_ALLOCATIONS t

7087:
7088: /*
7089: l_temp_product_allocation_id := 0;
7090:
7091: UPDATE OZF_TIME_ALLOCATIONS t
7092: SET t.TARGET = t.TARGET + l_diff_target,
7093: t.object_version_number = t.object_version_number + 1,
7094: t.last_update_date = SYSDATE,
7095: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7098: FROM OZF_TIME_ALLOCATIONS x

7094: t.last_update_date = SYSDATE,
7095: t.last_updated_by = FND_GLOBAL.USER_ID,
7096: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7097: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id)
7098: FROM OZF_TIME_ALLOCATIONS x
7099: WHERE x.allocation_for = 'PROD'
7100: AND x.allocation_for_id IN ( SELECT max(p.product_allocation_id)
7101: FROM OZF_PRODUCT_ALLOCATIONS p
7102: WHERE p.allocation_for = 'CUST'

Line 7114: FROM OZF_TIME_ALLOCATIONS zx

7110:
7111: )
7112: AND x.target =
7113: (SELECT max(zx.target)
7114: FROM OZF_TIME_ALLOCATIONS zx
7115: WHERE zx.allocation_for = 'PROD'
7116: AND zx.allocation_for_id IN (SELECT max(pz.product_allocation_id)
7117: FROM OZF_PRODUCT_ALLOCATIONS pz
7118: WHERE pz.allocation_for = 'CUST'

Line 7160: UPDATE OZF_TIME_ALLOCATIONS t

7156: */
7157:
7158:
7159: /*---------------------------------------------------------------------------------------------------
7160: UPDATE OZF_TIME_ALLOCATIONS t
7161: SET t.TARGET = t.TARGET + l_diff_target,
7162: t.object_version_number = t.object_version_number + 1,
7163: t.last_update_date = SYSDATE,
7164: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7166: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x

7162: t.object_version_number = t.object_version_number + 1,
7163: t.last_update_date = SYSDATE,
7164: t.last_updated_by = FND_GLOBAL.USER_ID,
7165: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7166: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
7167: WHERE x.allocation_for = 'PROD'
7168: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
7169: FROM OZF_PRODUCT_ALLOCATIONS p
7170: WHERE p.allocation_for = 'CUST'

Line 7292: Ozf_Time_Allocations_Pkg.Insert_Row(

7288: p_time_alloc_rec.lysp_sales := 0;
7289:
7290: l_time_allocation_id := get_time_allocation_id;
7291:
7292: Ozf_Time_Allocations_Pkg.Insert_Row(
7293: px_time_allocation_id => l_time_allocation_id,
7294: p_allocation_for => p_time_alloc_rec.allocation_for,
7295: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
7296: p_time_id => p_time_alloc_rec.time_id,

Line 7344: FROM OZF_TIME_ALLOCATIONS t

7340: AND a.allocation_for_id = l_fund_id
7341: AND a.parent_party_id = -9999;
7342:
7343: SELECT SUM(t.TARGET) INTO l_diff_target_2
7344: FROM OZF_TIME_ALLOCATIONS t
7345: WHERE t.allocation_for = 'CUST'
7346: AND t.allocation_for_id IN ( SELECT a.account_allocation_id
7347: FROM OZF_ACCOUNT_ALLOCATIONS a
7348: WHERE a.allocation_for = 'FUND'

Line 7370: UPDATE OZF_TIME_ALLOCATIONS t

7366: ELSE
7367: l_diff_target := FLOOR(l_diff_target); -- (So, +1.5 will become +1)
7368: END IF;
7369:
7370: UPDATE OZF_TIME_ALLOCATIONS t
7371: SET t.TARGET = t.TARGET + l_diff_target,
7372: t.object_version_number = t.object_version_number + 1,
7373: t.last_update_date = SYSDATE,
7374: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7376: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x

7372: t.object_version_number = t.object_version_number + 1,
7373: t.last_update_date = SYSDATE,
7374: t.last_updated_by = FND_GLOBAL.USER_ID,
7375: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7376: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
7377: WHERE x.allocation_for = 'CUST'
7378: AND x.allocation_for_id IN ( SELECT a.account_allocation_id
7379: FROM OZF_ACCOUNT_ALLOCATIONS a
7380: WHERE a.allocation_for = 'FUND'

Line 7432: UPDATE OZF_TIME_ALLOCATIONS t

7428:
7429: /*
7430: IF l_diff_target > 0 THEN
7431:
7432: UPDATE OZF_TIME_ALLOCATIONS t
7433: SET t.TARGET = t.TARGET + l_diff_target,
7434: t.object_version_number = t.object_version_number + 1,
7435: t.last_update_date = SYSDATE,
7436: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7438: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x

7434: t.object_version_number = t.object_version_number + 1,
7435: t.last_update_date = SYSDATE,
7436: t.last_updated_by = FND_GLOBAL.USER_ID,
7437: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7438: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
7439: WHERE x.allocation_for = 'CUST'
7440: AND x.allocation_for_id IN ( SELECT a.account_allocation_id
7441: FROM OZF_ACCOUNT_ALLOCATIONS a
7442: WHERE a.allocation_for = 'FUND'

Line 7490: UPDATE OZF_TIME_ALLOCATIONS t

7486: -- OZF_UTILITY_PVT.debug_message('222. mkothari-- ALL TARGETS l_diff_target => '||l_diff_target || ' ; ');
7487:
7488: l_temp_account_allocation_id := 0;
7489:
7490: UPDATE OZF_TIME_ALLOCATIONS t
7491: SET t.TARGET = t.TARGET + l_diff_target,
7492: t.object_version_number = t.object_version_number + 1,
7493: t.last_update_date = SYSDATE,
7494: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7497: FROM OZF_TIME_ALLOCATIONS x

7493: t.last_update_date = SYSDATE,
7494: t.last_updated_by = FND_GLOBAL.USER_ID,
7495: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7496: WHERE t.time_allocation_id = (SELECT MAX(x.time_allocation_id)
7497: FROM OZF_TIME_ALLOCATIONS x
7498: WHERE x.allocation_for = 'CUST'
7499: AND x.allocation_for_id IN ( SELECT MAX(a.account_allocation_id)
7500: FROM OZF_ACCOUNT_ALLOCATIONS a
7501: WHERE a.allocation_for = 'FUND'

Line 7509: FROM OZF_TIME_ALLOCATIONS xyz2

7505: WHERE xyz.allocation_for = 'FUND'
7506: AND xyz.allocation_for_id = l_fund_id)
7507: )
7508: AND x.target = (SELECT MAX(xyz2.target)
7509: FROM OZF_TIME_ALLOCATIONS xyz2
7510: WHERE xyz2.allocation_for = 'CUST'
7511: AND xyz2.allocation_for_id IN
7512: ( SELECT MAX(ax.account_allocation_id)
7513: FROM OZF_ACCOUNT_ALLOCATIONS ax

Line 7567: UPDATE OZF_TIME_ALLOCATIONS t

7563:
7564: /*
7565: l_temp_product_allocation_id := 0;
7566:
7567: UPDATE OZF_TIME_ALLOCATIONS t
7568: SET t.TARGET = t.TARGET + l_diff_target,
7569: t.object_version_number = t.object_version_number + 1,
7570: t.last_update_date = SYSDATE,
7571: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7574: FROM OZF_TIME_ALLOCATIONS x

7570: t.last_update_date = SYSDATE,
7571: t.last_updated_by = FND_GLOBAL.USER_ID,
7572: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7573: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id)
7574: FROM OZF_TIME_ALLOCATIONS x
7575: WHERE x.allocation_for = 'PROD'
7576: AND x.allocation_for_id IN ( SELECT max(p.product_allocation_id)
7577: FROM OZF_PRODUCT_ALLOCATIONS p
7578: WHERE p.allocation_for = 'CUST'

Line 7590: FROM OZF_TIME_ALLOCATIONS zx

7586:
7587: )
7588: AND x.target =
7589: (SELECT max(zx.target)
7590: FROM OZF_TIME_ALLOCATIONS zx
7591: WHERE zx.allocation_for = 'PROD'
7592: AND zx.allocation_for_id IN (SELECT max(pz.product_allocation_id)
7593: FROM OZF_PRODUCT_ALLOCATIONS pz
7594: WHERE pz.allocation_for = 'CUST'

Line 7780: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

7776: l_old_ozf_period_tbl OZF_PERIOD_TBL_TYPE;
7777:
7778: p_acct_alloc_rec ozf_account_allocations%ROWTYPE;
7779: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
7780: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
7781:
7782:
7783:
7784: CURSOR fund_csr (l_fund_id NUMBER)

Line 7884: ozf_time_allocations t,

7880: (l_fund_id NUMBER,
7881: l_in_clause VARCHAR2) IS
7882: SELECT SUM(t.target)
7883: FROM
7884: ozf_time_allocations t,
7885: ozf_product_allocations p
7886: WHERE
7887: p.fund_id = l_fund_id
7888: AND t.allocation_for_id = p.product_allocation_id

Line 7896: ' ozf_time_allocations t,'||

7892:
7893: l_get_total_target_sql VARCHAR2(30000) :=
7894: ' SELECT SUM(t.target) '||
7895: ' FROM '||
7896: ' ozf_time_allocations t,'||
7897: ' ozf_product_allocations p'||
7898: ' WHERE'||
7899: ' p.fund_id = :l_fund_id'||
7900: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 8017: ozf_time_allocations t

8013: (l_product_allocation_id number,
8014: l_time_id number) IS
8015: SELECT t.target
8016: FROM
8017: ozf_time_allocations t
8018: WHERE
8019: t.allocation_for_id = l_product_allocation_id
8020: AND t.allocation_for = 'PROD'
8021: AND t.time_id = l_time_id;

Line 8047: ozf_time_allocations t

8043: (l_product_allocation_id number,
8044: l_time_id number) IS
8045: SELECT t.target
8046: FROM
8047: ozf_time_allocations t
8048: WHERE
8049: t.allocation_for_id = l_product_allocation_id
8050: AND t.allocation_for = 'PROD'
8051: AND t.time_id = l_time_id;

Line 8057: ozf_time_allocations t,

8053: CURSOR get_old_period_csr
8054: (l_fund_id NUMBER) IS
8055: SELECT DISTINCT t.time_id
8056: FROM
8057: ozf_time_allocations t,
8058: ozf_account_allocations a
8059: WHERE
8060: a.allocation_for = 'FUND'
8061: AND a.allocation_for_id = l_fund_id

Line 8297: Ozf_Time_Allocations_Pkg.Insert_Row(

8293: p_time_alloc_rec.lysp_sales := NVL(l_account_sales, 0);
8294:
8295: l_time_allocation_id := get_time_allocation_id;
8296:
8297: Ozf_Time_Allocations_Pkg.Insert_Row(
8298: px_time_allocation_id => l_time_allocation_id,
8299: p_allocation_for => p_time_alloc_rec.allocation_for,
8300: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
8301: p_time_id => p_time_alloc_rec.time_id,

Line 8425: Ozf_Time_Allocations_Pkg.Insert_Row(

8421: p_time_alloc_rec.lysp_sales := NVL(l_product_sales, 0);
8422:
8423: l_time_allocation_id := get_time_allocation_id;
8424:
8425: Ozf_Time_Allocations_Pkg.Insert_Row(
8426: px_time_allocation_id => l_time_allocation_id,
8427: p_allocation_for => p_time_alloc_rec.allocation_for,
8428: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
8429: p_time_id => p_time_alloc_rec.time_id,

Line 8517: Ozf_Time_Allocations_Pkg.Insert_Row(

8513: p_time_alloc_rec.lysp_sales := 0;
8514:
8515: l_time_allocation_id := get_time_allocation_id;
8516:
8517: Ozf_Time_Allocations_Pkg.Insert_Row(
8518: px_time_allocation_id => l_time_allocation_id,
8519: p_allocation_for => p_time_alloc_rec.allocation_for,
8520: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
8521: p_time_id => p_time_alloc_rec.time_id,

Line 9230: OZF_TIME_ALLOCATIONS

9226: ELSE
9227: SELECT
9228: target into l_target
9229: FROM
9230: OZF_TIME_ALLOCATIONS
9231: WHERE
9232: allocation_for = p_allocation_for
9233: AND allocation_for_id = p_allocation_for_id
9234: AND time_id = p_time_id;

Line 9274: OZF_TIME_ALLOCATIONS

9270: ELSE
9271: SELECT
9272: time_allocation_id into l_time_allocation_id
9273: FROM
9274: OZF_TIME_ALLOCATIONS
9275: WHERE
9276: allocation_for = p_allocation_for
9277: AND allocation_for_id = p_allocation_for_id
9278: AND time_id = p_time_id;

Line 9317: OZF_TIME_ALLOCATIONS

9313: ELSE
9314: SELECT
9315: lysp_sales into l_lysp_sales
9316: FROM
9317: OZF_TIME_ALLOCATIONS
9318: WHERE
9319: allocation_for = p_allocation_for
9320: AND allocation_for_id = p_allocation_for_id
9321: AND time_id = p_time_id;

Line 9383: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

9379: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
9380:
9381: p_acct_alloc_rec ozf_account_allocations%ROWTYPE;
9382: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
9383: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
9384:
9385:
9386: CURSOR fund_csr
9387: IS

Line 9520: FROM ozf_time_allocations tt

9516:
9517:
9518: CURSOR corr_time_alloc_rec (l_alloc_for_id NUMBER) IS
9519: SELECT *
9520: FROM ozf_time_allocations tt
9521: WHERE tt.allocation_for = 'CUST'
9522: AND tt.allocation_for_id = l_alloc_for_id; -- p_acct_alloc_rec.account_allocation_id;
9523:
9524:

Line 9537: FROM ozf_time_allocations tt

9533:
9534:
9535: CURSOR corr_prod_time_alloc_rec (l_alloc_for_id NUMBER) IS
9536: SELECT *
9537: FROM ozf_time_allocations tt
9538: WHERE tt.allocation_for = 'PROD'
9539: AND tt.allocation_for_id = l_alloc_for_id; -- p_prod_alloc_rec.product_allocation_id;
9540:
9541:

Line 9764: Ozf_Time_Allocations_Pkg.Insert_Row(

9760:
9761: FOR p_time_alloc_rec IN corr_time_alloc_rec (p_acct_alloc_rec.account_allocation_id)
9762: LOOP
9763: l_time_allocation_id := get_time_allocation_id;
9764: Ozf_Time_Allocations_Pkg.Insert_Row(
9765: px_time_allocation_id => l_time_allocation_id,
9766: p_allocation_for => p_time_alloc_rec.allocation_for,
9767: p_allocation_for_id => l_account_allocation_id,
9768: p_time_id => p_time_alloc_rec.time_id,

Line 9806: update ozf_time_allocations tt

9802:
9803: -- CURRENT AND FUTURE TARGETS ARE carried with the account (already done above)
9804:
9805: -- NOW, MAKE PAST TARGETS ZERO
9806: update ozf_time_allocations tt
9807: set tt.target = 0
9808: WHERE tt.allocation_for = 'CUST'
9809: AND tt.allocation_for_id = l_account_allocation_id
9810: and EXISTS

Line 9828: update ozf_time_allocations tta

9824: -- 4. UPDATING UNALLOCATED TIME RECORDS - CURR and FUTURE periods....
9825:
9826: -- PAST UNALLOCATED remains unchanged
9827: -- Adjust CURRRENT AND FUTURE Targets for UNALLOCATED row
9828: update ozf_time_allocations tta
9829: set tta.target = tta.target - (
9830: SELECT ttb.target
9831: FROM ozf_time_allocations ttb
9832: WHERE ttb.allocation_for = 'CUST'

Line 9831: FROM ozf_time_allocations ttb

9827: -- Adjust CURRRENT AND FUTURE Targets for UNALLOCATED row
9828: update ozf_time_allocations tta
9829: set tta.target = tta.target - (
9830: SELECT ttb.target
9831: FROM ozf_time_allocations ttb
9832: WHERE ttb.allocation_for = 'CUST'
9833: AND ttb.allocation_for_id = l_account_allocation_id
9834: AND ttb.time_id = tta.time_id
9835: )

Line 9858: update ozf_time_allocations tta, ozf_time_allocations ttb

9854: and tta.period_type_id = 64
9855: );
9856:
9857: /*
9858: update ozf_time_allocations tta, ozf_time_allocations ttb
9859: set tta.target = tta.target - ttb.target
9860: where ttb.allocation_for = 'CUST'
9861: AND ttb.allocation_for_id = l_account_allocation_id
9862: AND tta.time_id = ttb.time_id

Line 9890: update ozf_time_allocations tt

9886: -- When corr quota is not found i.e. this is brand new account
9887:
9888: -- 5. UPDATING NEW SHIPTO ACCOUNT TIME RECORDS - All periods....
9889:
9890: update ozf_time_allocations tt
9891: set tt.target = 0,
9892: tt.lysp_sales = (
9893: SELECT SUM(bsmv.sales)
9894: FROM ozf_order_sales_v bsmv

Line 9914: FROM OZF_TIME_ALLOCATIONS ti

9910:
9911: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
9912: SET (aa.TARGET, aa.LYSP_SALES) = (
9913: SELECT SUM(ti.TARGET), SUM(ti.lysp_sales)
9914: FROM OZF_TIME_ALLOCATIONS ti
9915: WHERE ti.ALLOCATION_FOR = 'CUST'
9916: AND ti.ALLOCATION_FOR_ID = aa.account_allocation_id
9917: ),
9918: aa.object_version_number = aa.object_version_number + 1,

Line 9985: Ozf_Time_Allocations_Pkg.Insert_Row(

9981: -- 9. CREATING SHIPTOs Product Spread - TIME Records ....
9982: FOR p_time_alloc_rec IN corr_prod_time_alloc_rec (p_prod_alloc_rec.product_allocation_id)
9983: LOOP
9984: l_time_allocation_id := get_time_allocation_id;
9985: Ozf_Time_Allocations_Pkg.Insert_Row(
9986: px_time_allocation_id => l_time_allocation_id,
9987: p_allocation_for => p_time_alloc_rec.allocation_for,
9988: p_allocation_for_id => l_product_allocation_id,
9989: p_time_id => p_time_alloc_rec.time_id,

Line 10024: update ozf_time_allocations tt

10020: -- 10. Updating SHIPTOs Product Spread - TIME Records ....PAST PERIODS....
10021:
10022: -- CURRENT AND FUTURE TARGETS ARE carried with the product (above)
10023: -- MAKE PAST TARGETS ZERO
10024: update ozf_time_allocations tt
10025: set tt.target = 0
10026: WHERE tt.allocation_for = 'PROD'
10027: AND tt.allocation_for_id = l_product_allocation_id
10028: and EXISTS

Line 10049: FROM OZF_TIME_ALLOCATIONS ti

10045:
10046: -- Rollup the target numbers to the product record
10047: UPDATE OZF_PRODUCT_ALLOCATIONS p
10048: SET p.TARGET = (SELECT SUM(ti.TARGET)
10049: FROM OZF_TIME_ALLOCATIONS ti
10050: WHERE ti.ALLOCATION_FOR = 'PROD'
10051: AND ti.ALLOCATION_FOR_ID = p.product_allocation_id),
10052: p.object_version_number = p.object_version_number + 1,
10053: p.last_update_date = SYSDATE,

Line 10170: Ozf_Time_Allocations_Pkg.Insert_Row(

10166: p_time_alloc_rec.lysp_sales := l_product_sales;
10167:
10168: l_time_allocation_id := get_time_allocation_id;
10169:
10170: Ozf_Time_Allocations_Pkg.Insert_Row(
10171: px_time_allocation_id => l_time_allocation_id,
10172: p_allocation_for => p_time_alloc_rec.allocation_for,
10173: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
10174: p_time_id => p_time_alloc_rec.time_id,

Line 10361: update ozf_time_allocations tta

10357: BEGIN
10358: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 1. ADJUSTING UNALLOCATED Time records....');
10359:
10360: -- Increase the Unallocated for Current and Future Periods
10361: update ozf_time_allocations tta
10362: set tta.target = tta.target + (
10363: SELECT ttb.target
10364: FROM ozf_time_allocations ttb
10365: WHERE ttb.allocation_for = 'CUST'

Line 10364: FROM ozf_time_allocations ttb

10360: -- Increase the Unallocated for Current and Future Periods
10361: update ozf_time_allocations tta
10362: set tta.target = tta.target + (
10363: SELECT ttb.target
10364: FROM ozf_time_allocations ttb
10365: WHERE ttb.allocation_for = 'CUST'
10366: AND ttb.allocation_for_id = l_account_allocation_id
10367: AND ttb.time_id = tta.time_id
10368: )

Line 10395: FROM OZF_TIME_ALLOCATIONS ti

10391: -- Rollup the total targets to the unallocated records
10392: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
10393: SET (aa.TARGET, aa.LYSP_SALES) = (
10394: SELECT SUM(ti.TARGET), SUM(ti.lysp_sales)
10395: FROM OZF_TIME_ALLOCATIONS ti
10396: WHERE ti.ALLOCATION_FOR = 'CUST'
10397: AND ti.ALLOCATION_FOR_ID = aa.account_allocation_id
10398: ),
10399: aa.object_version_number = aa.object_version_number + 1,

Line 10413: update ozf_time_allocations ttb

10409: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 2. ADJUSTING SHIP TO Time records....');
10410:
10411: -- Past Targets stay with the shipTo
10412: -- Make ShipTos Current and Future Periods Targets ZERO
10413: update ozf_time_allocations ttb
10414: set ttb.target = 0,
10415: ttb.account_status = 'D'
10416: WHERE ttb.allocation_for = 'CUST'
10417: AND ttb.allocation_for_id = l_account_allocation_id

Line 10441: FROM OZF_TIME_ALLOCATIONS ti

10437: -- Rollup the total targets to the shipTo record
10438: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
10439: SET (aa.TARGET, aa.LYSP_SALES) = (
10440: SELECT SUM(ti.TARGET), SUM(ti.lysp_sales)
10441: FROM OZF_TIME_ALLOCATIONS ti
10442: WHERE ti.ALLOCATION_FOR = 'CUST'
10443: AND ti.ALLOCATION_FOR_ID = aa.account_allocation_id
10444: ),
10445: aa.account_status = 'D',

Line 10460: update ozf_time_allocations tt

10456: BEGIN
10457: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 3. ADJUSTING ShipTos Product Spread Time Records....');
10458:
10459: -- CURRENT AND FUTURE TARGETS of this Shiptos products are made ZERO
10460: update ozf_time_allocations tt
10461: set tt.target = 0,
10462: tt.account_status = 'D'
10463: WHERE tt.allocation_for = 'PROD'
10464: AND tt.allocation_for_id IN (

Line 10493: FROM OZF_TIME_ALLOCATIONS ti

10489:
10490: -- Rollup the target numbers to the product record
10491: UPDATE OZF_PRODUCT_ALLOCATIONS p
10492: SET p.TARGET = (SELECT SUM(ti.TARGET)
10493: FROM OZF_TIME_ALLOCATIONS ti
10494: WHERE ti.ALLOCATION_FOR = 'PROD'
10495: AND ti.ALLOCATION_FOR_ID = p.product_allocation_id),
10496: p.account_status = 'D',
10497: p.object_version_number = p.object_version_number + 1,