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.

[Home] [Help]

APPS.OZF_ALLOCATION_ENGINE_PVT dependencies on OZF_TIME_ALLOCATIONS

Line 43: SELECT ozf_time_allocations_s.NEXTVAL

39: l_api_name CONSTANT VARCHAR2(30) := 'get_time_allocation_id';
40: l_full_api_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
41:
42: CURSOR time_seq_csr IS
43: SELECT ozf_time_allocations_s.NEXTVAL
44: FROM DUAL;
45:
46: CURSOR time_alloc_count_csr(p_time_alloc_id in number) IS
47: SELECT count(t.time_allocation_id)

Line 48: FROM ozf_time_allocations t

44: FROM DUAL;
45:
46: CURSOR time_alloc_count_csr(p_time_alloc_id in number) IS
47: SELECT count(t.time_allocation_id)
48: FROM ozf_time_allocations t
49: WHERE t.time_allocation_id = p_time_alloc_id;
50:
51: l_count number := -1;
52: l_time_alloc_id number := -1;

Line 668: UPDATE OZF_TIME_ALLOCATIONS t

664:
665:
666: l_temp_product_allocation_id := 0;
667:
668: UPDATE OZF_TIME_ALLOCATIONS t
669: SET t.TARGET = t.TARGET + l_diff_target,
670: t.object_version_number = t.object_version_number + 1,
671: t.last_update_date = SYSDATE,
672: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 675: FROM OZF_TIME_ALLOCATIONS x

671: t.last_update_date = SYSDATE,
672: t.last_updated_by = FND_GLOBAL.USER_ID,
673: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
674: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id)
675: FROM OZF_TIME_ALLOCATIONS x
676: WHERE x.allocation_for = 'PROD'
677: AND x.allocation_for_id IN ( SELECT max(p.product_allocation_id)
678: FROM OZF_PRODUCT_ALLOCATIONS p
679: WHERE p.allocation_for = l_object_type

Line 691: FROM OZF_TIME_ALLOCATIONS zx

687:
688: )
689: AND x.target =
690: (SELECT max(zx.target)
691: FROM OZF_TIME_ALLOCATIONS zx
692: WHERE zx.allocation_for = 'PROD'
693: AND zx.allocation_for_id IN (SELECT max(pz.product_allocation_id)
694: FROM OZF_PRODUCT_ALLOCATIONS pz
695: WHERE pz.allocation_for = l_object_type

Line 739: UPDATE OZF_TIME_ALLOCATIONS t

735: RAISE fnd_api.g_exc_unexpected_error;
736: END IF;
737:
738: /*---------------------------------------------------------------------------------------------------
739: UPDATE OZF_TIME_ALLOCATIONS t
740: SET t.TARGET = t.TARGET + l_diff_target,
741: t.object_version_number = t.object_version_number + 1,
742: t.last_update_date = SYSDATE,
743: t.last_updated_by = FND_GLOBAL.USER_ID,

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

741: t.object_version_number = t.object_version_number + 1,
742: t.last_update_date = SYSDATE,
743: t.last_updated_by = FND_GLOBAL.USER_ID,
744: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
745: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
746: WHERE x.allocation_for = 'PROD'
747: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
748: FROM OZF_PRODUCT_ALLOCATIONS p
749: WHERE p.allocation_for = 'CUST'

Line 850: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

846: l_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
847: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
848:
849: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
850: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
851:
852:
853: CURSOR fact_csr
854: (l_fact_id number) IS

Line 920: ozf_time_allocations t,

916: (l_fund_id NUMBER,
917: l_in_clause VARCHAR2) IS
918: SELECT SUM(t.target)
919: FROM
920: ozf_time_allocations t,
921: ozf_product_allocations p
922: WHERE
923: p.fund_id = l_fund_id
924: AND t.allocation_for_id = p.product_allocation_id

Line 932: ' ozf_time_allocations t,'||

928:
929: l_get_total_target_sql VARCHAR2(30000) :=
930: ' SELECT SUM(t.target) '||
931: ' FROM '||
932: ' ozf_time_allocations t,'||
933: ' ozf_product_allocations p'||
934: ' WHERE'||
935: ' p.fund_id = :l_fund_id'||
936: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 962: ozf_time_allocations t

958: (l_product_allocation_id number,
959: l_time_id number) IS
960: SELECT t.target
961: FROM
962: ozf_time_allocations t
963: WHERE
964: t.allocation_for_id = l_product_allocation_id
965: AND t.allocation_for = 'PROD'
966: AND t.time_id = l_time_id;

Line 1323: Ozf_Time_Allocations_Pkg.Insert_Row(

1319: p_time_alloc_rec.target := NVL(l_time_quota, 0);
1320: p_time_alloc_rec.lysp_sales := NVL(l_lysp_sales, 0);
1321:
1322:
1323: Ozf_Time_Allocations_Pkg.Insert_Row(
1324: px_time_allocation_id => l_time_allocation_id,
1325: p_allocation_for => p_time_alloc_rec.allocation_for,
1326: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
1327: p_time_id => p_time_alloc_rec.time_id,

Line 1394: UPDATE OZF_TIME_ALLOCATIONS t

1390: END;
1391:
1392: IF l_diff_target <> 0 THEN
1393:
1394: UPDATE OZF_TIME_ALLOCATIONS t
1395: SET t.TARGET = t.TARGET + l_diff_target,
1396: t.object_version_number = t.object_version_number + 1,
1397: t.last_update_date = SYSDATE,
1398: t.last_updated_by = FND_GLOBAL.USER_ID,

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

1396: t.object_version_number = t.object_version_number + 1,
1397: t.last_update_date = SYSDATE,
1398: t.last_updated_by = FND_GLOBAL.USER_ID,
1399: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
1400: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
1401: WHERE x.allocation_for = 'PROD'
1402: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
1403: FROM OZF_PRODUCT_ALLOCATIONS p
1404: WHERE p.allocation_for = 'FACT'

Line 1559: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

1555: l_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
1556: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
1557:
1558: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
1559: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
1560:
1561:
1562: CURSOR fact_csr
1563: (l_fact_id number) IS

Line 1629: ozf_time_allocations t,

1625: (l_fund_id NUMBER,
1626: l_in_clause VARCHAR2) IS
1627: SELECT SUM(t.target)
1628: FROM
1629: ozf_time_allocations t,
1630: ozf_product_allocations p
1631: WHERE
1632: p.fund_id = l_fund_id
1633: AND t.allocation_for_id = p.product_allocation_id

Line 1641: ' ozf_time_allocations t,'||

1637:
1638: l_get_total_target_sql VARCHAR2(30000) :=
1639: ' SELECT SUM(t.target) '||
1640: ' FROM '||
1641: ' ozf_time_allocations t,'||
1642: ' ozf_product_allocations p'||
1643: ' WHERE'||
1644: ' p.fund_id = :l_fund_id'||
1645: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 1671: ozf_time_allocations t

1667: (l_product_allocation_id number,
1668: l_time_id number) IS
1669: SELECT t.target
1670: FROM
1671: ozf_time_allocations t
1672: WHERE
1673: t.allocation_for_id = l_product_allocation_id
1674: AND t.allocation_for = 'PROD'
1675: AND t.time_id = l_time_id;

Line 2044: Ozf_Time_Allocations_Pkg.Insert_Row(

2040: p_time_alloc_rec.target := 0; --------NVL(l_time_quota, 0);
2041: p_time_alloc_rec.lysp_sales := NVL(l_lysp_sales, 0);
2042:
2043:
2044: Ozf_Time_Allocations_Pkg.Insert_Row(
2045: px_time_allocation_id => l_time_allocation_id,
2046: p_allocation_for => p_time_alloc_rec.allocation_for,
2047: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
2048: p_time_id => p_time_alloc_rec.time_id,

Line 2105: UPDATE OZF_TIME_ALLOCATIONS t

2101: END IF;
2102:
2103: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor is '|| l_multiplying_factor);
2104:
2105: UPDATE OZF_TIME_ALLOCATIONS t
2106: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_multiplying_factor), 0),
2107: t.object_version_number = t.object_version_number + 1,
2108: t.last_update_date = SYSDATE,
2109: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 2128: FROM OZF_TIME_ALLOCATIONS ti

2124: END IF;
2125:
2126: UPDATE OZF_PRODUCT_ALLOCATIONS p
2127: SET p.TARGET = (SELECT SUM(ti.TARGET)
2128: FROM OZF_TIME_ALLOCATIONS ti
2129: WHERE ti.ALLOCATION_FOR = 'PROD'
2130: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
2131: p.object_version_number = p.object_version_number + 1,
2132: p.last_update_date = SYSDATE,

Line 2177: UPDATE OZF_TIME_ALLOCATIONS t

2173:
2174: RAISE fnd_api.g_exc_unexpected_error;
2175: END IF;
2176: */
2177: UPDATE OZF_TIME_ALLOCATIONS t
2178: SET t.TARGET = ROUND((NVL(l_fact_rec.recommend_total_amount, 0) / l_denominator), 0),
2179: t.object_version_number = t.object_version_number + 1,
2180: t.last_update_date = SYSDATE,
2181: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 2200: FROM OZF_TIME_ALLOCATIONS ti

2196: END IF;
2197:
2198: UPDATE OZF_PRODUCT_ALLOCATIONS p
2199: SET p.TARGET = (SELECT SUM(ti.TARGET)
2200: FROM OZF_TIME_ALLOCATIONS ti
2201: WHERE ti.ALLOCATION_FOR = 'PROD'
2202: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
2203: p.object_version_number = p.object_version_number + 1,
2204: p.last_update_date = SYSDATE,

Line 2255: UPDATE OZF_TIME_ALLOCATIONS t

2251:
2252: fix_product_rounding_err('FACT', l_fact_id, l_diff_target);
2253:
2254: /*
2255: UPDATE OZF_TIME_ALLOCATIONS t
2256: SET t.TARGET = t.TARGET + l_diff_target,
2257: t.object_version_number = t.object_version_number + 1,
2258: t.last_update_date = SYSDATE,
2259: t.last_updated_by = FND_GLOBAL.USER_ID,

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

2257: t.object_version_number = t.object_version_number + 1,
2258: t.last_update_date = SYSDATE,
2259: t.last_updated_by = FND_GLOBAL.USER_ID,
2260: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
2261: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
2262: WHERE x.allocation_for = 'PROD'
2263: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
2264: FROM OZF_PRODUCT_ALLOCATIONS p
2265: WHERE p.allocation_for = 'FACT'

Line 2410: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

2406: l_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
2407: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
2408:
2409: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
2410: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
2411:
2412:
2413: CURSOR fund_csr (l_fund_id NUMBER) IS
2414: SELECT

Line 2780: Ozf_Time_Allocations_Pkg.Insert_Row(

2776: p_time_alloc_rec.target := 0;
2777: p_time_alloc_rec.lysp_sales := l_lysp_sales;
2778:
2779:
2780: Ozf_Time_Allocations_Pkg.Insert_Row(
2781: px_time_allocation_id => l_time_allocation_id,
2782: p_allocation_for => p_time_alloc_rec.allocation_for,
2783: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
2784: p_time_id => p_time_alloc_rec.time_id,

Line 2917: Ozf_Time_Allocations_Pkg.Insert_Row(

2913: p_time_alloc_rec.target := 0;
2914: p_time_alloc_rec.lysp_sales := l_lysp_sales;
2915:
2916:
2917: Ozf_Time_Allocations_Pkg.Insert_Row(
2918: px_time_allocation_id => l_time_allocation_id,
2919: p_allocation_for => p_time_alloc_rec.allocation_for,
2920: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
2921: p_time_id => p_time_alloc_rec.time_id,

Line 3048: Ozf_Time_Allocations_Pkg.Insert_Row(

3044: p_time_alloc_rec.target := 0;
3045: p_time_alloc_rec.lysp_sales := l_lysp_sales;
3046:
3047:
3048: Ozf_Time_Allocations_Pkg.Insert_Row(
3049: px_time_allocation_id => l_time_allocation_id,
3050: p_allocation_for => p_time_alloc_rec.allocation_for,
3051: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
3052: p_time_id => p_time_alloc_rec.time_id,

Line 3139: UPDATE OZF_TIME_ALLOCATIONS t

3135: RAISE fnd_api.g_exc_unexpected_error;
3136: END IF;
3137: */
3138:
3139: UPDATE OZF_TIME_ALLOCATIONS t
3140: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_multiplying_factor), 0),
3141: t.object_version_number = t.object_version_number + 1,
3142: t.last_update_date = SYSDATE,
3143: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 3163: FROM OZF_TIME_ALLOCATIONS ti

3159:
3160:
3161: UPDATE OZF_PRODUCT_ALLOCATIONS p
3162: SET p.TARGET = (SELECT SUM(ti.TARGET)
3163: FROM OZF_TIME_ALLOCATIONS ti
3164: WHERE ti.ALLOCATION_FOR = 'PROD'
3165: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
3166: p.object_version_number = p.object_version_number + 1,
3167: p.last_update_date = SYSDATE,

Line 3213: UPDATE OZF_TIME_ALLOCATIONS t

3209:
3210: RAISE fnd_api.g_exc_unexpected_error;
3211: END IF;
3212: */
3213: UPDATE OZF_TIME_ALLOCATIONS t
3214: SET t.TARGET = ROUND((l_total_root_quota / l_denominator), 0),
3215: t.object_version_number = t.object_version_number + 1,
3216: t.last_update_date = SYSDATE,
3217: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 3237: FROM OZF_TIME_ALLOCATIONS ti

3233:
3234:
3235: UPDATE OZF_PRODUCT_ALLOCATIONS p
3236: SET p.TARGET = (SELECT SUM(ti.TARGET)
3237: FROM OZF_TIME_ALLOCATIONS ti
3238: WHERE ti.ALLOCATION_FOR = 'PROD'
3239: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
3240: p.object_version_number = p.object_version_number + 1,
3241: p.last_update_date = SYSDATE,

Line 3276: FROM OZF_TIME_ALLOCATIONS t

3272: AND p.item_id = -9999;
3273:
3274:
3275: SELECT SUM(t.TARGET) INTO l_diff_target_2
3276: FROM OZF_TIME_ALLOCATIONS t
3277: WHERE t.allocation_for = 'PROD'
3278: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
3279: FROM OZF_PRODUCT_ALLOCATIONS p
3280: WHERE p.allocation_for = 'FUND'

Line 3307: FROM OZF_TIME_ALLOCATIONS t

3303: AND p.item_id = -9999
3304: ) a,
3305: (
3306: SELECT SUM(t.TARGET) target
3307: FROM OZF_TIME_ALLOCATIONS t
3308: WHERE t.allocation_for = 'PROD'
3309: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
3310: FROM OZF_PRODUCT_ALLOCATIONS p
3311: WHERE p.allocation_for = 'FUND'

Line 3331: FROM OZF_TIME_ALLOCATIONS t

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

Line 3393: UPDATE OZF_TIME_ALLOCATIONS t

3389: l_diff_target := 0;
3390: l_diff_target := MOD(l_total_root_quota,l_denominator);
3391:
3392: IF l_diff_target <> 0 THEN
3393: UPDATE OZF_TIME_ALLOCATIONS t
3394: SET t.TARGET = t.TARGET + l_diff_target,
3395: t.object_version_number = t.object_version_number + 1,
3396: t.last_update_date = SYSDATE,
3397: t.last_updated_by = FND_GLOBAL.USER_ID,

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

3395: t.object_version_number = t.object_version_number + 1,
3396: t.last_update_date = SYSDATE,
3397: t.last_updated_by = FND_GLOBAL.USER_ID,
3398: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
3399: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
3400: WHERE x.allocation_for = 'PROD'
3401: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
3402: FROM OZF_PRODUCT_ALLOCATIONS p
3403: WHERE p.allocation_for = 'FUND'

Line 3695: DELETE ozf_time_allocations t

3691: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
3692:
3693: FOR fact_product_spread_rec IN fact_product_spread_csr(l_fact_id)
3694: LOOP
3695: DELETE ozf_time_allocations t
3696: WHERE t.allocation_for_id = fact_product_spread_rec.product_allocation_id
3697: AND t.allocation_for = 'PROD';
3698: END LOOP;
3699:

Line 3908: DELETE ozf_time_allocations t

3904: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3905:
3906: FOR fund_product_spread_rec IN fund_product_spread_csr(l_fund_id)
3907: LOOP
3908: DELETE ozf_time_allocations t
3909: WHERE t.allocation_for_id = fund_product_spread_rec.product_allocation_id
3910: AND t.allocation_for = 'PROD';
3911: END LOOP;
3912:

Line 4004: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

4000: l_object_version_number NUMBER := 1;
4001: l_org_id NUMBER := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10));
4002:
4003: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
4004: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
4005:
4006:
4007: CURSOR fund_csr(l_fund_id NUMBER) IS
4008: SELECT

Line 4092: FROM OZF_TIME_ALLOCATIONS

4088: time_id,
4089: period_type_id,
4090: target,
4091: lysp_sales
4092: FROM OZF_TIME_ALLOCATIONS
4093: WHERE allocation_for = 'PROD'
4094: AND allocation_for_id = l_prod_alloc_id;
4095:
4096: l_time_alloc_rec time_alloc_csr%ROWTYPE;

Line 4302: Ozf_Time_Allocations_Pkg.Insert_Row(

4298: p_time_alloc_rec.target := 0;
4299: p_time_alloc_rec.lysp_sales := l_lysp_sales;
4300:
4301:
4302: Ozf_Time_Allocations_Pkg.Insert_Row(
4303: px_time_allocation_id => l_time_allocation_id,
4304: p_allocation_for => p_time_alloc_rec.allocation_for,
4305: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
4306: p_time_id => p_time_alloc_rec.time_id,

Line 4441: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

4437: l_object_version_number NUMBER := 1;
4438: l_org_id NUMBER := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10));
4439:
4440: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
4441: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
4442:
4443: CURSOR fund_csr(l_fund_id NUMBER) IS
4444: SELECT
4445: owner,

Line 4532: FROM OZF_TIME_ALLOCATIONS

4528: time_id,
4529: period_type_id,
4530: target,
4531: lysp_sales
4532: FROM OZF_TIME_ALLOCATIONS
4533: WHERE allocation_for = 'PROD'
4534: AND allocation_for_id = l_prod_alloc_id;
4535:
4536: l_time_alloc_rec time_alloc_csr%ROWTYPE;

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

4666:
4667: FOR time_alloc_rec IN time_alloc_csr(l_prod_alloc_rec.product_allocation_id)
4668: LOOP
4669:
4670: UPDATE ozf_time_allocations t -- Update Others Quota for Jul03, Aug03, Sep03 etc
4671: SET t.target = t.target + NVL(time_alloc_rec.target, 0),
4672: t.object_version_number = t.object_version_number + 1,
4673: t.last_update_date = SYSDATE,
4674: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 4696: DELETE ozf_time_allocations t

4692: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Updating OTHERS Product and Time Allocations Records'
4693: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4694:
4695:
4696: DELETE ozf_time_allocations t
4697: WHERE t.allocation_for_id = l_prod_alloc_rec.product_allocation_id
4698: AND t.allocation_for = 'PROD';
4699:
4700: DELETE ozf_product_allocations p

Line 4830: DELETE ozf_time_allocations t

4826: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
4827:
4828: FOR account_rec IN fund_account_spread_csr(l_fund_id)
4829: LOOP
4830: DELETE ozf_time_allocations t
4831: WHERE t.allocation_for_id = account_rec.account_allocation_id
4832: AND t.allocation_for = 'CUST';
4833:
4834: FOR product_rec IN acct_product_spread_csr(account_rec.account_allocation_id)

Line 4836: DELETE ozf_time_allocations t

4832: AND t.allocation_for = 'CUST';
4833:
4834: FOR product_rec IN acct_product_spread_csr(account_rec.account_allocation_id)
4835: LOOP
4836: DELETE ozf_time_allocations t
4837: WHERE t.allocation_for_id = product_rec.product_allocation_id
4838: AND t.allocation_for = 'PROD';
4839: END LOOP;
4840:

Line 4963: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

4959: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
4960:
4961: p_acct_alloc_rec ozf_account_allocations%ROWTYPE;
4962: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
4963: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
4964:
4965:
4966:
4967: CURSOR fund_csr (l_fund_id NUMBER)

Line 5115: ozf_time_allocations t,

5111: (l_fund_id NUMBER,
5112: l_in_clause VARCHAR2) IS
5113: SELECT SUM(t.target)
5114: FROM
5115: ozf_time_allocations t,
5116: ozf_product_allocations p
5117: WHERE
5118: p.fund_id = l_fund_id
5119: AND t.allocation_for_id = p.product_allocation_id

Line 5127: ' ozf_time_allocations t,'||

5123:
5124: l_get_total_target_sql VARCHAR2(30000) :=
5125: ' SELECT SUM(t.target) '||
5126: ' FROM '||
5127: ' ozf_time_allocations t,'||
5128: ' ozf_product_allocations p'||
5129: ' WHERE'||
5130: ' p.fund_id = :l_fund_id'||
5131: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 5253: ozf_time_allocations t

5249: (l_product_allocation_id number,
5250: l_time_id number) IS
5251: SELECT t.target
5252: FROM
5253: ozf_time_allocations t
5254: WHERE
5255: t.allocation_for_id = l_product_allocation_id
5256: AND t.allocation_for = 'PROD'
5257: AND t.time_id = l_time_id;

Line 5518: Ozf_Time_Allocations_Pkg.Insert_Row(

5514: p_time_alloc_rec.lysp_sales := l_account_sales;
5515:
5516: l_time_allocation_id := get_time_allocation_id;
5517:
5518: Ozf_Time_Allocations_Pkg.Insert_Row(
5519: px_time_allocation_id => l_time_allocation_id,
5520: p_allocation_for => p_time_alloc_rec.allocation_for,
5521: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
5522: p_time_id => p_time_alloc_rec.time_id,

Line 5684: Ozf_Time_Allocations_Pkg.Insert_Row(

5680: p_time_alloc_rec.lysp_sales := l_product_sales;
5681:
5682: l_time_allocation_id := get_time_allocation_id;
5683:
5684: Ozf_Time_Allocations_Pkg.Insert_Row(
5685: px_time_allocation_id => l_time_allocation_id,
5686: p_allocation_for => p_time_alloc_rec.allocation_for,
5687: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
5688: p_time_id => p_time_alloc_rec.time_id,

Line 5836: Ozf_Time_Allocations_Pkg.Insert_Row(

5832: p_time_alloc_rec.lysp_sales := 0;
5833:
5834: l_time_allocation_id := get_time_allocation_id;
5835:
5836: Ozf_Time_Allocations_Pkg.Insert_Row(
5837: px_time_allocation_id => l_time_allocation_id,
5838: p_allocation_for => p_time_alloc_rec.allocation_for,
5839: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
5840: p_time_id => p_time_alloc_rec.time_id,

Line 5888: FROM OZF_TIME_ALLOCATIONS t

5884: AND a.allocation_for_id = l_fund_id
5885: AND a.parent_party_id = -9999;
5886:
5887: SELECT SUM(t.TARGET) INTO l_diff_target_2
5888: FROM OZF_TIME_ALLOCATIONS t
5889: WHERE t.allocation_for = 'CUST'
5890: AND t.allocation_for_id IN ( SELECT a.account_allocation_id
5891: FROM OZF_ACCOUNT_ALLOCATIONS a
5892: WHERE a.allocation_for = 'FUND'

Line 5906: UPDATE OZF_TIME_ALLOCATIONS t

5902:
5903:
5904: IF l_diff_target <> 0 THEN
5905:
5906: UPDATE OZF_TIME_ALLOCATIONS t
5907: SET t.TARGET = t.TARGET + l_diff_target,
5908: t.object_version_number = t.object_version_number + 1,
5909: t.last_update_date = SYSDATE,
5910: t.last_updated_by = FND_GLOBAL.USER_ID,

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

5908: t.object_version_number = t.object_version_number + 1,
5909: t.last_update_date = SYSDATE,
5910: t.last_updated_by = FND_GLOBAL.USER_ID,
5911: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
5912: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
5913: WHERE x.allocation_for = 'CUST'
5914: AND x.allocation_for_id IN ( SELECT a.account_allocation_id
5915: FROM OZF_ACCOUNT_ALLOCATIONS a
5916: WHERE a.allocation_for = 'FUND'

Line 6072: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

6068: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
6069:
6070: p_acct_alloc_rec ozf_account_allocations%ROWTYPE;
6071: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
6072: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
6073:
6074:
6075:
6076: CURSOR fund_csr (l_fund_id NUMBER)

Line 6225: ozf_time_allocations t,

6221: (l_fund_id NUMBER,
6222: l_in_clause VARCHAR2) IS
6223: SELECT SUM(t.target)
6224: FROM
6225: ozf_time_allocations t,
6226: ozf_product_allocations p
6227: WHERE
6228: p.fund_id = l_fund_id
6229: AND t.allocation_for_id = p.product_allocation_id

Line 6237: ' ozf_time_allocations t,'||

6233:
6234: l_get_total_target_sql VARCHAR2(30000) :=
6235: ' SELECT SUM(t.target) '||
6236: ' FROM '||
6237: ' ozf_time_allocations t,'||
6238: ' ozf_product_allocations p'||
6239: ' WHERE'||
6240: ' p.fund_id = :l_fund_id'||
6241: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 6363: ozf_time_allocations t

6359: (l_product_allocation_id number,
6360: l_time_id number) IS
6361: SELECT t.target
6362: FROM
6363: ozf_time_allocations t
6364: WHERE
6365: t.allocation_for_id = l_product_allocation_id
6366: AND t.allocation_for = 'PROD'
6367: AND t.time_id = l_time_id;

Line 6628: Ozf_Time_Allocations_Pkg.Insert_Row(

6624: p_time_alloc_rec.lysp_sales := l_account_sales;
6625:
6626: l_time_allocation_id := get_time_allocation_id;
6627:
6628: Ozf_Time_Allocations_Pkg.Insert_Row(
6629: px_time_allocation_id => l_time_allocation_id,
6630: p_allocation_for => p_time_alloc_rec.allocation_for,
6631: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
6632: p_time_id => p_time_alloc_rec.time_id,

Line 6672: FROM OZF_TIME_ALLOCATIONS ti

6668:
6669: BEGIN
6670:
6671: SELECT SUM(ti.TARGET) into l_total_account_target
6672: FROM OZF_TIME_ALLOCATIONS ti
6673: WHERE ti.ALLOCATION_FOR = 'CUST'
6674: AND ti.ALLOCATION_FOR_ID = l_account_allocation_id;
6675:
6676: UPDATE OZF_ACCOUNT_ALLOCATIONS a

Line 6839: Ozf_Time_Allocations_Pkg.Insert_Row(

6835: p_time_alloc_rec.lysp_sales := l_product_sales;
6836:
6837: l_time_allocation_id := get_time_allocation_id;
6838:
6839: Ozf_Time_Allocations_Pkg.Insert_Row(
6840: px_time_allocation_id => l_time_allocation_id,
6841: p_allocation_for => p_time_alloc_rec.allocation_for,
6842: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
6843: p_time_id => p_time_alloc_rec.time_id,

Line 6903: UPDATE OZF_TIME_ALLOCATIONS t

6899: -- OZF_UTILITY_PVT.debug_message('1. mkothari --'||' -- l_prod_mltply_factor==> '||l_prod_mltply_factor || ' ; ');
6900: -- OZF_UTILITY_PVT.debug_message('2. mkothari --'||' l_total_account_target===> '||l_total_account_target);
6901: -- OZF_UTILITY_PVT.debug_message('3. mkothari --'||' l_p_grand_total_lysp_sales='||l_p_grand_total_lysp_sales);
6902:
6903: UPDATE OZF_TIME_ALLOCATIONS t
6904: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_prod_mltply_factor), 0),
6905: t.object_version_number = t.object_version_number + 1,
6906: t.last_update_date = SYSDATE,
6907: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 6926: FROM OZF_TIME_ALLOCATIONS ti

6922: END IF;
6923:
6924: UPDATE OZF_PRODUCT_ALLOCATIONS p
6925: SET p.TARGET = (SELECT SUM(ti.TARGET)
6926: FROM OZF_TIME_ALLOCATIONS ti
6927: WHERE ti.ALLOCATION_FOR = 'PROD'
6928: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
6929: p.object_version_number = p.object_version_number + 1,
6930: p.last_update_date = SYSDATE,

Line 6976: UPDATE OZF_TIME_ALLOCATIONS t

6972: RAISE fnd_api.g_exc_unexpected_error;
6973: END IF;
6974: */
6975:
6976: UPDATE OZF_TIME_ALLOCATIONS t
6977: SET t.TARGET = ROUND((NVL(l_total_account_target, 0) / l_p_denominator), 0),
6978: t.object_version_number = t.object_version_number + 1,
6979: t.last_update_date = SYSDATE,
6980: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 6999: FROM OZF_TIME_ALLOCATIONS ti

6995: END IF;
6996:
6997: UPDATE OZF_PRODUCT_ALLOCATIONS p
6998: SET p.TARGET = (SELECT SUM(ti.TARGET)
6999: FROM OZF_TIME_ALLOCATIONS ti
7000: WHERE ti.ALLOCATION_FOR = 'PROD'
7001: AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
7002: p.object_version_number = p.object_version_number + 1,
7003: p.last_update_date = SYSDATE,

Line 7060: UPDATE OZF_TIME_ALLOCATIONS t

7056:
7057: /*
7058: l_temp_product_allocation_id := 0;
7059:
7060: UPDATE OZF_TIME_ALLOCATIONS t
7061: SET t.TARGET = t.TARGET + l_diff_target,
7062: t.object_version_number = t.object_version_number + 1,
7063: t.last_update_date = SYSDATE,
7064: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7067: FROM OZF_TIME_ALLOCATIONS x

7063: t.last_update_date = SYSDATE,
7064: t.last_updated_by = FND_GLOBAL.USER_ID,
7065: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7066: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id)
7067: FROM OZF_TIME_ALLOCATIONS x
7068: WHERE x.allocation_for = 'PROD'
7069: AND x.allocation_for_id IN ( SELECT max(p.product_allocation_id)
7070: FROM OZF_PRODUCT_ALLOCATIONS p
7071: WHERE p.allocation_for = 'CUST'

Line 7083: FROM OZF_TIME_ALLOCATIONS zx

7079:
7080: )
7081: AND x.target =
7082: (SELECT max(zx.target)
7083: FROM OZF_TIME_ALLOCATIONS zx
7084: WHERE zx.allocation_for = 'PROD'
7085: AND zx.allocation_for_id IN (SELECT max(pz.product_allocation_id)
7086: FROM OZF_PRODUCT_ALLOCATIONS pz
7087: WHERE pz.allocation_for = 'CUST'

Line 7129: UPDATE OZF_TIME_ALLOCATIONS t

7125: */
7126:
7127:
7128: /*---------------------------------------------------------------------------------------------------
7129: UPDATE OZF_TIME_ALLOCATIONS t
7130: SET t.TARGET = t.TARGET + l_diff_target,
7131: t.object_version_number = t.object_version_number + 1,
7132: t.last_update_date = SYSDATE,
7133: t.last_updated_by = FND_GLOBAL.USER_ID,

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

7131: t.object_version_number = t.object_version_number + 1,
7132: t.last_update_date = SYSDATE,
7133: t.last_updated_by = FND_GLOBAL.USER_ID,
7134: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7135: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
7136: WHERE x.allocation_for = 'PROD'
7137: AND x.allocation_for_id IN ( SELECT p.product_allocation_id
7138: FROM OZF_PRODUCT_ALLOCATIONS p
7139: WHERE p.allocation_for = 'CUST'

Line 7261: Ozf_Time_Allocations_Pkg.Insert_Row(

7257: p_time_alloc_rec.lysp_sales := 0;
7258:
7259: l_time_allocation_id := get_time_allocation_id;
7260:
7261: Ozf_Time_Allocations_Pkg.Insert_Row(
7262: px_time_allocation_id => l_time_allocation_id,
7263: p_allocation_for => p_time_alloc_rec.allocation_for,
7264: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
7265: p_time_id => p_time_alloc_rec.time_id,

Line 7313: FROM OZF_TIME_ALLOCATIONS t

7309: AND a.allocation_for_id = l_fund_id
7310: AND a.parent_party_id = -9999;
7311:
7312: SELECT SUM(t.TARGET) INTO l_diff_target_2
7313: FROM OZF_TIME_ALLOCATIONS t
7314: WHERE t.allocation_for = 'CUST'
7315: AND t.allocation_for_id IN ( SELECT a.account_allocation_id
7316: FROM OZF_ACCOUNT_ALLOCATIONS a
7317: WHERE a.allocation_for = 'FUND'

Line 7339: UPDATE OZF_TIME_ALLOCATIONS t

7335: ELSE
7336: l_diff_target := FLOOR(l_diff_target); -- (So, +1.5 will become +1)
7337: END IF;
7338:
7339: UPDATE OZF_TIME_ALLOCATIONS t
7340: SET t.TARGET = t.TARGET + l_diff_target,
7341: t.object_version_number = t.object_version_number + 1,
7342: t.last_update_date = SYSDATE,
7343: t.last_updated_by = FND_GLOBAL.USER_ID,

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

7341: t.object_version_number = t.object_version_number + 1,
7342: t.last_update_date = SYSDATE,
7343: t.last_updated_by = FND_GLOBAL.USER_ID,
7344: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7345: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
7346: WHERE x.allocation_for = 'CUST'
7347: AND x.allocation_for_id IN ( SELECT a.account_allocation_id
7348: FROM OZF_ACCOUNT_ALLOCATIONS a
7349: WHERE a.allocation_for = 'FUND'

Line 7401: UPDATE OZF_TIME_ALLOCATIONS t

7397:
7398: /*
7399: IF l_diff_target > 0 THEN
7400:
7401: UPDATE OZF_TIME_ALLOCATIONS t
7402: SET t.TARGET = t.TARGET + l_diff_target,
7403: t.object_version_number = t.object_version_number + 1,
7404: t.last_update_date = SYSDATE,
7405: t.last_updated_by = FND_GLOBAL.USER_ID,

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

7403: t.object_version_number = t.object_version_number + 1,
7404: t.last_update_date = SYSDATE,
7405: t.last_updated_by = FND_GLOBAL.USER_ID,
7406: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7407: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
7408: WHERE x.allocation_for = 'CUST'
7409: AND x.allocation_for_id IN ( SELECT a.account_allocation_id
7410: FROM OZF_ACCOUNT_ALLOCATIONS a
7411: WHERE a.allocation_for = 'FUND'

Line 7459: UPDATE OZF_TIME_ALLOCATIONS t

7455: -- OZF_UTILITY_PVT.debug_message('222. mkothari-- ALL TARGETS l_diff_target => '||l_diff_target || ' ; ');
7456:
7457: l_temp_account_allocation_id := 0;
7458:
7459: UPDATE OZF_TIME_ALLOCATIONS t
7460: SET t.TARGET = t.TARGET + l_diff_target,
7461: t.object_version_number = t.object_version_number + 1,
7462: t.last_update_date = SYSDATE,
7463: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7466: FROM OZF_TIME_ALLOCATIONS x

7462: t.last_update_date = SYSDATE,
7463: t.last_updated_by = FND_GLOBAL.USER_ID,
7464: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7465: WHERE t.time_allocation_id = (SELECT MAX(x.time_allocation_id)
7466: FROM OZF_TIME_ALLOCATIONS x
7467: WHERE x.allocation_for = 'CUST'
7468: AND x.allocation_for_id IN ( SELECT MAX(a.account_allocation_id)
7469: FROM OZF_ACCOUNT_ALLOCATIONS a
7470: WHERE a.allocation_for = 'FUND'

Line 7478: FROM OZF_TIME_ALLOCATIONS xyz2

7474: WHERE xyz.allocation_for = 'FUND'
7475: AND xyz.allocation_for_id = l_fund_id)
7476: )
7477: AND x.target = (SELECT MAX(xyz2.target)
7478: FROM OZF_TIME_ALLOCATIONS xyz2
7479: WHERE xyz2.allocation_for = 'CUST'
7480: AND xyz2.allocation_for_id IN
7481: ( SELECT MAX(ax.account_allocation_id)
7482: FROM OZF_ACCOUNT_ALLOCATIONS ax

Line 7536: UPDATE OZF_TIME_ALLOCATIONS t

7532:
7533: /*
7534: l_temp_product_allocation_id := 0;
7535:
7536: UPDATE OZF_TIME_ALLOCATIONS t
7537: SET t.TARGET = t.TARGET + l_diff_target,
7538: t.object_version_number = t.object_version_number + 1,
7539: t.last_update_date = SYSDATE,
7540: t.last_updated_by = FND_GLOBAL.USER_ID,

Line 7543: FROM OZF_TIME_ALLOCATIONS x

7539: t.last_update_date = SYSDATE,
7540: t.last_updated_by = FND_GLOBAL.USER_ID,
7541: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7542: WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id)
7543: FROM OZF_TIME_ALLOCATIONS x
7544: WHERE x.allocation_for = 'PROD'
7545: AND x.allocation_for_id IN ( SELECT max(p.product_allocation_id)
7546: FROM OZF_PRODUCT_ALLOCATIONS p
7547: WHERE p.allocation_for = 'CUST'

Line 7559: FROM OZF_TIME_ALLOCATIONS zx

7555:
7556: )
7557: AND x.target =
7558: (SELECT max(zx.target)
7559: FROM OZF_TIME_ALLOCATIONS zx
7560: WHERE zx.allocation_for = 'PROD'
7561: AND zx.allocation_for_id IN (SELECT max(pz.product_allocation_id)
7562: FROM OZF_PRODUCT_ALLOCATIONS pz
7563: WHERE pz.allocation_for = 'CUST'

Line 7749: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

7745: l_old_ozf_period_tbl OZF_PERIOD_TBL_TYPE;
7746:
7747: p_acct_alloc_rec ozf_account_allocations%ROWTYPE;
7748: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
7749: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
7750:
7751:
7752:
7753: CURSOR fund_csr (l_fund_id NUMBER)

Line 7853: ozf_time_allocations t,

7849: (l_fund_id NUMBER,
7850: l_in_clause VARCHAR2) IS
7851: SELECT SUM(t.target)
7852: FROM
7853: ozf_time_allocations t,
7854: ozf_product_allocations p
7855: WHERE
7856: p.fund_id = l_fund_id
7857: AND t.allocation_for_id = p.product_allocation_id

Line 7865: ' ozf_time_allocations t,'||

7861:
7862: l_get_total_target_sql VARCHAR2(30000) :=
7863: ' SELECT SUM(t.target) '||
7864: ' FROM '||
7865: ' ozf_time_allocations t,'||
7866: ' ozf_product_allocations p'||
7867: ' WHERE'||
7868: ' p.fund_id = :l_fund_id'||
7869: ' AND t.allocation_for_id = p.product_allocation_id'||

Line 7986: ozf_time_allocations t

7982: (l_product_allocation_id number,
7983: l_time_id number) IS
7984: SELECT t.target
7985: FROM
7986: ozf_time_allocations t
7987: WHERE
7988: t.allocation_for_id = l_product_allocation_id
7989: AND t.allocation_for = 'PROD'
7990: AND t.time_id = l_time_id;

Line 8016: ozf_time_allocations t

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

Line 8026: ozf_time_allocations t,

8022: CURSOR get_old_period_csr
8023: (l_fund_id NUMBER) IS
8024: SELECT DISTINCT t.time_id
8025: FROM
8026: ozf_time_allocations t,
8027: ozf_account_allocations a
8028: WHERE
8029: a.allocation_for = 'FUND'
8030: AND a.allocation_for_id = l_fund_id

Line 8264: Ozf_Time_Allocations_Pkg.Insert_Row(

8260: p_time_alloc_rec.lysp_sales := NVL(l_account_sales, 0);
8261:
8262: l_time_allocation_id := get_time_allocation_id;
8263:
8264: Ozf_Time_Allocations_Pkg.Insert_Row(
8265: px_time_allocation_id => l_time_allocation_id,
8266: p_allocation_for => p_time_alloc_rec.allocation_for,
8267: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
8268: p_time_id => p_time_alloc_rec.time_id,

Line 8392: Ozf_Time_Allocations_Pkg.Insert_Row(

8388: p_time_alloc_rec.lysp_sales := NVL(l_product_sales, 0);
8389:
8390: l_time_allocation_id := get_time_allocation_id;
8391:
8392: Ozf_Time_Allocations_Pkg.Insert_Row(
8393: px_time_allocation_id => l_time_allocation_id,
8394: p_allocation_for => p_time_alloc_rec.allocation_for,
8395: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
8396: p_time_id => p_time_alloc_rec.time_id,

Line 8484: Ozf_Time_Allocations_Pkg.Insert_Row(

8480: p_time_alloc_rec.lysp_sales := 0;
8481:
8482: l_time_allocation_id := get_time_allocation_id;
8483:
8484: Ozf_Time_Allocations_Pkg.Insert_Row(
8485: px_time_allocation_id => l_time_allocation_id,
8486: p_allocation_for => p_time_alloc_rec.allocation_for,
8487: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
8488: p_time_id => p_time_alloc_rec.time_id,

Line 9197: OZF_TIME_ALLOCATIONS

9193: ELSE
9194: SELECT
9195: target into l_target
9196: FROM
9197: OZF_TIME_ALLOCATIONS
9198: WHERE
9199: allocation_for = p_allocation_for
9200: AND allocation_for_id = p_allocation_for_id
9201: AND time_id = p_time_id;

Line 9241: OZF_TIME_ALLOCATIONS

9237: ELSE
9238: SELECT
9239: time_allocation_id into l_time_allocation_id
9240: FROM
9241: OZF_TIME_ALLOCATIONS
9242: WHERE
9243: allocation_for = p_allocation_for
9244: AND allocation_for_id = p_allocation_for_id
9245: AND time_id = p_time_id;

Line 9284: OZF_TIME_ALLOCATIONS

9280: ELSE
9281: SELECT
9282: lysp_sales into l_lysp_sales
9283: FROM
9284: OZF_TIME_ALLOCATIONS
9285: WHERE
9286: allocation_for = p_allocation_for
9287: AND allocation_for_id = p_allocation_for_id
9288: AND time_id = p_time_id;

Line 9350: p_time_alloc_rec ozf_time_allocations%ROWTYPE;

9346: l_lysp_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
9347:
9348: p_acct_alloc_rec ozf_account_allocations%ROWTYPE;
9349: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;
9350: p_time_alloc_rec ozf_time_allocations%ROWTYPE;
9351:
9352:
9353: CURSOR fund_csr
9354: IS

Line 9487: FROM ozf_time_allocations tt

9483:
9484:
9485: CURSOR corr_time_alloc_rec (l_alloc_for_id NUMBER) IS
9486: SELECT *
9487: FROM ozf_time_allocations tt
9488: WHERE tt.allocation_for = 'CUST'
9489: AND tt.allocation_for_id = l_alloc_for_id; -- p_acct_alloc_rec.account_allocation_id;
9490:
9491:

Line 9504: FROM ozf_time_allocations tt

9500:
9501:
9502: CURSOR corr_prod_time_alloc_rec (l_alloc_for_id NUMBER) IS
9503: SELECT *
9504: FROM ozf_time_allocations tt
9505: WHERE tt.allocation_for = 'PROD'
9506: AND tt.allocation_for_id = l_alloc_for_id; -- p_prod_alloc_rec.product_allocation_id;
9507:
9508:

Line 9729: Ozf_Time_Allocations_Pkg.Insert_Row(

9725:
9726: FOR p_time_alloc_rec IN corr_time_alloc_rec (p_acct_alloc_rec.account_allocation_id)
9727: LOOP
9728: l_time_allocation_id := get_time_allocation_id;
9729: Ozf_Time_Allocations_Pkg.Insert_Row(
9730: px_time_allocation_id => l_time_allocation_id,
9731: p_allocation_for => p_time_alloc_rec.allocation_for,
9732: p_allocation_for_id => l_account_allocation_id,
9733: p_time_id => p_time_alloc_rec.time_id,

Line 9771: update ozf_time_allocations tt

9767:
9768: -- CURRENT AND FUTURE TARGETS ARE carried with the account (already done above)
9769:
9770: -- NOW, MAKE PAST TARGETS ZERO
9771: update ozf_time_allocations tt
9772: set tt.target = 0
9773: WHERE tt.allocation_for = 'CUST'
9774: AND tt.allocation_for_id = l_account_allocation_id
9775: and EXISTS

Line 9793: update ozf_time_allocations tta

9789: -- 4. UPDATING UNALLOCATED TIME RECORDS - CURR and FUTURE periods....
9790:
9791: -- PAST UNALLOCATED remains unchanged
9792: -- Adjust CURRRENT AND FUTURE Targets for UNALLOCATED row
9793: update ozf_time_allocations tta
9794: set tta.target = tta.target - (
9795: SELECT ttb.target
9796: FROM ozf_time_allocations ttb
9797: WHERE ttb.allocation_for = 'CUST'

Line 9796: FROM ozf_time_allocations ttb

9792: -- Adjust CURRRENT AND FUTURE Targets for UNALLOCATED row
9793: update ozf_time_allocations tta
9794: set tta.target = tta.target - (
9795: SELECT ttb.target
9796: FROM ozf_time_allocations ttb
9797: WHERE ttb.allocation_for = 'CUST'
9798: AND ttb.allocation_for_id = l_account_allocation_id
9799: AND ttb.time_id = tta.time_id
9800: )

Line 9823: update ozf_time_allocations tta, ozf_time_allocations ttb

9819: and tta.period_type_id = 64
9820: );
9821:
9822: /*
9823: update ozf_time_allocations tta, ozf_time_allocations ttb
9824: set tta.target = tta.target - ttb.target
9825: where ttb.allocation_for = 'CUST'
9826: AND ttb.allocation_for_id = l_account_allocation_id
9827: AND tta.time_id = ttb.time_id

Line 9855: update ozf_time_allocations tt

9851: -- When corr quota is not found i.e. this is brand new account
9852:
9853: -- 5. UPDATING NEW SHIPTO ACCOUNT TIME RECORDS - All periods....
9854:
9855: update ozf_time_allocations tt
9856: set tt.target = 0,
9857: tt.lysp_sales = (
9858: SELECT SUM(bsmv.sales)
9859: FROM ozf_order_sales_v bsmv

Line 9879: FROM OZF_TIME_ALLOCATIONS ti

9875:
9876: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
9877: SET (aa.TARGET, aa.LYSP_SALES) = (
9878: SELECT SUM(ti.TARGET), SUM(ti.lysp_sales)
9879: FROM OZF_TIME_ALLOCATIONS ti
9880: WHERE ti.ALLOCATION_FOR = 'CUST'
9881: AND ti.ALLOCATION_FOR_ID = aa.account_allocation_id
9882: ),
9883: aa.object_version_number = aa.object_version_number + 1,

Line 9950: Ozf_Time_Allocations_Pkg.Insert_Row(

9946: -- 9. CREATING SHIPTOs Product Spread - TIME Records ....
9947: FOR p_time_alloc_rec IN corr_prod_time_alloc_rec (p_prod_alloc_rec.product_allocation_id)
9948: LOOP
9949: l_time_allocation_id := get_time_allocation_id;
9950: Ozf_Time_Allocations_Pkg.Insert_Row(
9951: px_time_allocation_id => l_time_allocation_id,
9952: p_allocation_for => p_time_alloc_rec.allocation_for,
9953: p_allocation_for_id => l_product_allocation_id,
9954: p_time_id => p_time_alloc_rec.time_id,

Line 9989: update ozf_time_allocations tt

9985: -- 10. Updating SHIPTOs Product Spread - TIME Records ....PAST PERIODS....
9986:
9987: -- CURRENT AND FUTURE TARGETS ARE carried with the product (above)
9988: -- MAKE PAST TARGETS ZERO
9989: update ozf_time_allocations tt
9990: set tt.target = 0
9991: WHERE tt.allocation_for = 'PROD'
9992: AND tt.allocation_for_id = l_product_allocation_id
9993: and EXISTS

Line 10014: FROM OZF_TIME_ALLOCATIONS ti

10010:
10011: -- Rollup the target numbers to the product record
10012: UPDATE OZF_PRODUCT_ALLOCATIONS p
10013: SET p.TARGET = (SELECT SUM(ti.TARGET)
10014: FROM OZF_TIME_ALLOCATIONS ti
10015: WHERE ti.ALLOCATION_FOR = 'PROD'
10016: AND ti.ALLOCATION_FOR_ID = p.product_allocation_id),
10017: p.object_version_number = p.object_version_number + 1,
10018: p.last_update_date = SYSDATE,

Line 10135: Ozf_Time_Allocations_Pkg.Insert_Row(

10131: p_time_alloc_rec.lysp_sales := l_product_sales;
10132:
10133: l_time_allocation_id := get_time_allocation_id;
10134:
10135: Ozf_Time_Allocations_Pkg.Insert_Row(
10136: px_time_allocation_id => l_time_allocation_id,
10137: p_allocation_for => p_time_alloc_rec.allocation_for,
10138: p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
10139: p_time_id => p_time_alloc_rec.time_id,

Line 10326: update ozf_time_allocations tta

10322: BEGIN
10323: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 1. ADJUSTING UNALLOCATED Time records....');
10324:
10325: -- Increase the Unallocated for Current and Future Periods
10326: update ozf_time_allocations tta
10327: set tta.target = tta.target + (
10328: SELECT ttb.target
10329: FROM ozf_time_allocations ttb
10330: WHERE ttb.allocation_for = 'CUST'

Line 10329: FROM ozf_time_allocations ttb

10325: -- Increase the Unallocated for Current and Future Periods
10326: update ozf_time_allocations tta
10327: set tta.target = tta.target + (
10328: SELECT ttb.target
10329: FROM ozf_time_allocations ttb
10330: WHERE ttb.allocation_for = 'CUST'
10331: AND ttb.allocation_for_id = l_account_allocation_id
10332: AND ttb.time_id = tta.time_id
10333: )

Line 10360: FROM OZF_TIME_ALLOCATIONS ti

10356: -- Rollup the total targets to the unallocated records
10357: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
10358: SET (aa.TARGET, aa.LYSP_SALES) = (
10359: SELECT SUM(ti.TARGET), SUM(ti.lysp_sales)
10360: FROM OZF_TIME_ALLOCATIONS ti
10361: WHERE ti.ALLOCATION_FOR = 'CUST'
10362: AND ti.ALLOCATION_FOR_ID = aa.account_allocation_id
10363: ),
10364: aa.object_version_number = aa.object_version_number + 1,

Line 10378: update ozf_time_allocations ttb

10374: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 2. ADJUSTING SHIP TO Time records....');
10375:
10376: -- Past Targets stay with the shipTo
10377: -- Make ShipTos Current and Future Periods Targets ZERO
10378: update ozf_time_allocations ttb
10379: set ttb.target = 0,
10380: ttb.account_status = 'D'
10381: WHERE ttb.allocation_for = 'CUST'
10382: AND ttb.allocation_for_id = l_account_allocation_id

Line 10406: FROM OZF_TIME_ALLOCATIONS ti

10402: -- Rollup the total targets to the shipTo record
10403: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
10404: SET (aa.TARGET, aa.LYSP_SALES) = (
10405: SELECT SUM(ti.TARGET), SUM(ti.lysp_sales)
10406: FROM OZF_TIME_ALLOCATIONS ti
10407: WHERE ti.ALLOCATION_FOR = 'CUST'
10408: AND ti.ALLOCATION_FOR_ID = aa.account_allocation_id
10409: ),
10410: aa.account_status = 'D',

Line 10425: update ozf_time_allocations tt

10421: BEGIN
10422: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 3. ADJUSTING ShipTos Product Spread Time Records....');
10423:
10424: -- CURRENT AND FUTURE TARGETS of this Shiptos products are made ZERO
10425: update ozf_time_allocations tt
10426: set tt.target = 0,
10427: tt.account_status = 'D'
10428: WHERE tt.allocation_for = 'PROD'
10429: AND tt.allocation_for_id IN (

Line 10458: FROM OZF_TIME_ALLOCATIONS ti

10454:
10455: -- Rollup the target numbers to the product record
10456: UPDATE OZF_PRODUCT_ALLOCATIONS p
10457: SET p.TARGET = (SELECT SUM(ti.TARGET)
10458: FROM OZF_TIME_ALLOCATIONS ti
10459: WHERE ti.ALLOCATION_FOR = 'PROD'
10460: AND ti.ALLOCATION_FOR_ID = p.product_allocation_id),
10461: p.account_status = 'D',
10462: p.object_version_number = p.object_version_number + 1,