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_PRODUCT_ALLOCATIONS

Line 120: SELECT ozf_product_allocations_s.NEXTVAL

116: l_api_name CONSTANT VARCHAR2(30) := 'get_product_allocation_id';
117: l_full_api_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
118:
119: CURSOR product_seq_csr IS
120: SELECT ozf_product_allocations_s.NEXTVAL
121: FROM DUAL;
122:
123: CURSOR product_alloc_count_csr(p_product_alloc_id in number) IS
124: SELECT count(p.product_allocation_id)

Line 125: FROM ozf_product_allocations p

121: FROM DUAL;
122:
123: CURSOR product_alloc_count_csr(p_product_alloc_id in number) IS
124: SELECT count(p.product_allocation_id)
125: FROM ozf_product_allocations p
126: WHERE p.product_allocation_id = p_product_alloc_id;
127:
128: l_count number := -1;
129: l_product_alloc_id number := -1;

Line 364: FROM ozf_product_allocations p

360: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
361: AND DENORM.PARENT_ID = l_category_id
362: MINUS
363: SELECT p.item_id
364: FROM ozf_product_allocations p
365: WHERE p.fund_id = l_fund_id
366: AND p.item_type = 'PRICING_ATTRIBUTE1'
367: );
368:

Line 439: FROM ozf_product_allocations p

435: AND bsmv.time_id = l_time_id
436: AND NOT EXISTS
437: (
438: ( SELECT p.item_id
439: FROM ozf_product_allocations p
440: WHERE
441: p.fund_id = l_fund_id
442: AND p.item_type = 'PRICING_ATTRIBUTE1'
443: AND p.item_id = bsmv.inventory_item_id

Line 448: OZF_PRODUCT_ALLOCATIONS p

444: UNION ALL
445: SELECT MIC.INVENTORY_ITEM_ID
446: FROM MTL_ITEM_CATEGORIES MIC,
447: ENI_PROD_DENORM_HRCHY_V DENORM,
448: OZF_PRODUCT_ALLOCATIONS p
449: WHERE p.FUND_ID = l_fund_id
450: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
451: AND p.ITEM_ID = DENORM.PARENT_ID
452: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 697: FROM OZF_PRODUCT_ALLOCATIONS p

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
699: AND p.allocation_for_id = l_object_id
700: AND p.target =
701: (SELECT max(xz.target)

Line 702: FROM OZF_PRODUCT_ALLOCATIONS xz

698: WHERE p.allocation_for = l_object_type
699: AND p.allocation_for_id = l_object_id
700: AND p.target =
701: (SELECT max(xz.target)
702: FROM OZF_PRODUCT_ALLOCATIONS xz
703: WHERE xz.allocation_for = l_object_type
704: AND xz.allocation_for_id = l_object_id
705: )
706:

Line 713: FROM OZF_PRODUCT_ALLOCATIONS pz

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
715: AND pz.allocation_for_id = l_object_id
716: AND pz.target =
717: (SELECT max(xz.target)

Line 718: FROM OZF_PRODUCT_ALLOCATIONS xz

714: WHERE pz.allocation_for = l_object_type
715: AND pz.allocation_for_id = l_object_id
716: AND pz.target =
717: (SELECT max(xz.target)
718: FROM OZF_PRODUCT_ALLOCATIONS xz
719: WHERE xz.allocation_for = l_object_type
720: AND xz.allocation_for_id = l_object_id
721: )
722:

Line 739: UPDATE OZF_PRODUCT_ALLOCATIONS p

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

Line 767: FROM OZF_PRODUCT_ALLOCATIONS p

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'
769: AND p.allocation_for_id = l_account_allocation_id
770: AND p.item_id = -9999 )
771: );

Line 782: UPDATE OZF_PRODUCT_ALLOCATIONS p

778: END IF;
779: RAISE fnd_api.g_exc_unexpected_error;
780: END IF;
781:
782: UPDATE OZF_PRODUCT_ALLOCATIONS p
783: SET p.TARGET = p.TARGET + l_diff_target,
784: p.object_version_number = p.object_version_number + 1,
785: p.last_update_date = SYSDATE,
786: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 868: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

864:
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

Line 940: ozf_product_allocations p

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
944: AND t.allocation_for = 'PROD'

Line 952: ' ozf_product_allocations p'||

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'||
956: ' AND t.allocation_for = ''PROD'' '||

Line 970: ozf_product_allocations p

966: p.product_allocation_id,
967: p.item_id,
968: p.item_type
969: FROM
970: ozf_product_allocations p
971: WHERE
972: p.fund_id = l_fund_id;
973:
974: l_root_product_rec root_product_spread_csr%rowtype;

Line 1029: FROM ozf_product_allocations p

1025: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
1026: AND DENORM.PARENT_ID = l_category_id
1027: MINUS
1028: SELECT p.item_id
1029: FROM ozf_product_allocations p
1030: WHERE p.fund_id = l_fund_id
1031: AND p.item_type = 'PRICING_ATTRIBUTE1'
1032: );
1033:

Line 1051: FROM ozf_product_allocations p

1047: AND bsmv.time_id = l_time_id
1048: AND NOT EXISTS
1049: (
1050: ( SELECT p.item_id
1051: FROM ozf_product_allocations p
1052: WHERE
1053: p.fund_id = l_fund_id
1054: AND p.item_type = 'PRICING_ATTRIBUTE1'
1055: AND p.item_id = bsmv.inventory_item_id

Line 1060: OZF_PRODUCT_ALLOCATIONS p

1056: UNION ALL
1057: SELECT MIC.INVENTORY_ITEM_ID
1058: FROM MTL_ITEM_CATEGORIES MIC,
1059: ENI_PROD_DENORM_HRCHY_V DENORM,
1060: OZF_PRODUCT_ALLOCATIONS p
1061: WHERE p.FUND_ID = l_fund_id
1062: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
1063: AND p.ITEM_ID = DENORM.PARENT_ID
1064: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 1248: Ozf_Product_Allocations_Pkg.Insert_Row(

1244: p_prod_alloc_rec.target := 0;
1245: p_prod_alloc_rec.lysp_sales := 0;
1246:
1247:
1248: Ozf_Product_Allocations_Pkg.Insert_Row(
1249: px_product_allocation_id => l_product_allocation_id,
1250: p_allocation_for => p_prod_alloc_rec.allocation_for,
1251: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
1252: p_fund_id => p_prod_alloc_rec.fund_id,

Line 1381: UPDATE OZF_PRODUCT_ALLOCATIONS p

1377:
1378: END IF;
1379: END LOOP;
1380:
1381: UPDATE OZF_PRODUCT_ALLOCATIONS p
1382: SET p.lysp_sales = l_total_lysp_sales,
1383: p.target = ROUND( l_total_quota, 0),
1384: p.object_version_number = p.object_version_number + 1,
1385: p.last_update_date = SYSDATE,

Line 1402: FROM OZF_PRODUCT_ALLOCATIONS p

1398: BEGIN
1399:
1400:
1401: SELECT SUM(p.TARGET) INTO l_diff_target_1
1402: FROM OZF_PRODUCT_ALLOCATIONS p
1403: WHERE p.allocation_for = 'FACT'
1404: AND p.allocation_for_id = l_fact_id;
1405:
1406: l_diff_target := ROUND((NVL(l_fact_rec.recommend_total_amount, 0) - NVL(l_diff_target_1, 0)), 0);

Line 1424: FROM OZF_PRODUCT_ALLOCATIONS p

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'
1426: AND p.allocation_for_id = l_fact_id
1427: AND p.item_id = -9999 )
1428: );

Line 1441: UPDATE OZF_PRODUCT_ALLOCATIONS p

1437: RAISE fnd_api.g_exc_unexpected_error;
1438: END IF;
1439:
1440:
1441: UPDATE OZF_PRODUCT_ALLOCATIONS p
1442: SET p.TARGET = p.TARGET + l_diff_target,
1443: p.object_version_number = p.object_version_number + 1,
1444: p.last_update_date = SYSDATE,
1445: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 1579: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

1575:
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

Line 1651: ozf_product_allocations p

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
1655: AND t.allocation_for = 'PROD'

Line 1663: ' ozf_product_allocations p'||

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'||
1667: ' AND t.allocation_for = ''PROD'' '||

Line 1681: ozf_product_allocations p

1677: p.product_allocation_id,
1678: p.item_id,
1679: p.item_type
1680: FROM
1681: ozf_product_allocations p
1682: WHERE
1683: p.fund_id = l_fund_id;
1684:
1685: l_root_product_rec root_product_spread_csr%rowtype;

Line 1740: FROM ozf_product_allocations p

1736: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
1737: AND DENORM.PARENT_ID = l_category_id
1738: MINUS
1739: SELECT p.item_id
1740: FROM ozf_product_allocations p
1741: WHERE p.fund_id = l_fund_id
1742: AND p.item_type = 'PRICING_ATTRIBUTE1'
1743: );
1744:

Line 1762: FROM ozf_product_allocations p

1758: AND bsmv.time_id = l_time_id
1759: AND NOT EXISTS
1760: (
1761: ( SELECT p.item_id
1762: FROM ozf_product_allocations p
1763: WHERE
1764: p.fund_id = l_fund_id
1765: AND p.item_type = 'PRICING_ATTRIBUTE1'
1766: AND p.item_id = bsmv.inventory_item_id

Line 1771: OZF_PRODUCT_ALLOCATIONS p

1767: UNION ALL
1768: SELECT MIC.INVENTORY_ITEM_ID
1769: FROM MTL_ITEM_CATEGORIES MIC,
1770: ENI_PROD_DENORM_HRCHY_V DENORM,
1771: OZF_PRODUCT_ALLOCATIONS p
1772: WHERE p.FUND_ID = l_fund_id
1773: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
1774: AND p.ITEM_ID = DENORM.PARENT_ID
1775: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 1967: Ozf_Product_Allocations_Pkg.Insert_Row(

1963: p_prod_alloc_rec.target := 0;
1964: p_prod_alloc_rec.lysp_sales := 0;
1965:
1966:
1967: Ozf_Product_Allocations_Pkg.Insert_Row(
1968: px_product_allocation_id => l_product_allocation_id,
1969: p_allocation_for => p_prod_alloc_rec.allocation_for,
1970: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
1971: p_fund_id => p_prod_alloc_rec.fund_id,

Line 2104: UPDATE OZF_PRODUCT_ALLOCATIONS p

2100:
2101: END IF;
2102: END LOOP;
2103:
2104: UPDATE OZF_PRODUCT_ALLOCATIONS p
2105: SET p.lysp_sales = l_total_lysp_sales,
2106: p.object_version_number = p.object_version_number + 1,
2107: p.last_update_date = SYSDATE,
2108: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 2136: FROM OZF_PRODUCT_ALLOCATIONS p

2132: t.last_updated_by = FND_GLOBAL.USER_ID,
2133: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
2134: WHERE t.allocation_for = 'PROD'
2135: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
2136: FROM OZF_PRODUCT_ALLOCATIONS p
2137: WHERE p.allocation_for = 'FACT'
2138: AND p.allocation_for_id = l_fact_id );
2139:
2140: IF (SQL%NOTFOUND) THEN

Line 2149: UPDATE OZF_PRODUCT_ALLOCATIONS p

2145:
2146: RAISE fnd_api.g_exc_unexpected_error;
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),

Line 2181: UPDATE OZF_PRODUCT_ALLOCATIONS p

2177:
2178: l_denominator := l_period_tbl.COUNT;
2179:
2180: /*
2181: UPDATE OZF_PRODUCT_ALLOCATIONS p
2182: SET p.TARGET = ROUND(NVL(l_fact_rec.recommend_total_amount, 0),0),
2183: p.object_version_number = p.object_version_number + 1,
2184: p.last_update_date = SYSDATE,
2185: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 2208: FROM OZF_PRODUCT_ALLOCATIONS p

2204: t.last_updated_by = FND_GLOBAL.USER_ID,
2205: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
2206: WHERE t.allocation_for = 'PROD'
2207: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
2208: FROM OZF_PRODUCT_ALLOCATIONS p
2209: WHERE p.allocation_for = 'FACT'
2210: AND p.allocation_for_id = l_fact_id
2211: AND p.item_id = -9999 );
2212:

Line 2221: UPDATE OZF_PRODUCT_ALLOCATIONS p

2217: END IF;
2218: RAISE fnd_api.g_exc_unexpected_error;
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),

Line 2256: FROM OZF_PRODUCT_ALLOCATIONS p

2252:
2253: BEGIN
2254:
2255: SELECT SUM(p.TARGET) INTO l_diff_target_1
2256: FROM OZF_PRODUCT_ALLOCATIONS p
2257: WHERE p.allocation_for = 'FACT'
2258: AND p.allocation_for_id = l_fact_id;
2259:
2260: l_diff_target := (NVL(l_fact_rec.recommend_total_amount, 0) - NVL(l_diff_target_1, 0));

Line 2287: FROM OZF_PRODUCT_ALLOCATIONS p

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'
2289: AND p.allocation_for_id = l_fact_id
2290: AND p.item_id = -9999 )
2291: );

Line 2302: UPDATE OZF_PRODUCT_ALLOCATIONS p

2298: END IF;
2299: RAISE fnd_api.g_exc_unexpected_error;
2300: END IF;
2301:
2302: UPDATE OZF_PRODUCT_ALLOCATIONS p
2303: SET p.TARGET = p.TARGET + l_diff_target,
2304: p.object_version_number = p.object_version_number + 1,
2305: p.last_update_date = SYSDATE,
2306: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 2432: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

2428:
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

Line 2738: Ozf_Product_Allocations_Pkg.Insert_Row(

2734: p_prod_alloc_rec.target := 0;
2735: p_prod_alloc_rec.lysp_sales := 0;
2736:
2737:
2738: Ozf_Product_Allocations_Pkg.Insert_Row(
2739: px_product_allocation_id => l_product_allocation_id,
2740: p_allocation_for => p_prod_alloc_rec.allocation_for,
2741: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
2742: p_fund_id => p_prod_alloc_rec.fund_id,

Line 2842: UPDATE OZF_PRODUCT_ALLOCATIONS p

2838:
2839: END IF;
2840: END LOOP;
2841:
2842: UPDATE OZF_PRODUCT_ALLOCATIONS p
2843: SET p.lysp_sales = NVL(l_total_lysp_sales,0),
2844: p.object_version_number = p.object_version_number + 1,
2845: p.last_update_date = SYSDATE,
2846: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 2875: Ozf_Product_Allocations_Pkg.Insert_Row(

2871: p_prod_alloc_rec.target := 0;
2872: p_prod_alloc_rec.lysp_sales := 0;
2873:
2874:
2875: Ozf_Product_Allocations_Pkg.Insert_Row(
2876: px_product_allocation_id => l_product_allocation_id,
2877: p_allocation_for => p_prod_alloc_rec.allocation_for,
2878: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
2879: p_fund_id => p_prod_alloc_rec.fund_id,

Line 2979: UPDATE OZF_PRODUCT_ALLOCATIONS p

2975:
2976: END IF;
2977: END LOOP;
2978:
2979: UPDATE OZF_PRODUCT_ALLOCATIONS p
2980: SET p.lysp_sales = NVL(l_total_lysp_sales, 0),
2981: p.object_version_number = p.object_version_number + 1,
2982: p.last_update_date = SYSDATE,
2983: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 3008: Ozf_Product_Allocations_Pkg.Insert_Row(

3004: p_prod_alloc_rec.target := 0;
3005: p_prod_alloc_rec.lysp_sales := 0;
3006:
3007:
3008: Ozf_Product_Allocations_Pkg.Insert_Row(
3009: px_product_allocation_id => l_product_allocation_id,
3010: p_allocation_for => p_prod_alloc_rec.allocation_for,
3011: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
3012: p_fund_id => p_prod_alloc_rec.fund_id,

Line 3110: UPDATE OZF_PRODUCT_ALLOCATIONS p

3106:
3107: END IF;
3108: END LOOP;
3109:
3110: UPDATE OZF_PRODUCT_ALLOCATIONS p
3111: SET p.lysp_sales = NVL(l_total_lysp_sales, 0),
3112: p.object_version_number = p.object_version_number + 1,
3113: p.last_update_date = SYSDATE,
3114: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 3145: UPDATE OZF_PRODUCT_ALLOCATIONS p

3141:
3142: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor == '
3143: ||l_multiplying_factor||' ;');
3144: /*
3145: UPDATE OZF_PRODUCT_ALLOCATIONS p
3146: SET p.TARGET = ROUND( (NVL(p.LYSP_SALES, 0) * l_multiplying_factor), 0),
3147: p.object_version_number = p.object_version_number + 1,
3148: p.last_update_date = SYSDATE,
3149: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 3172: FROM OZF_PRODUCT_ALLOCATIONS p

3168: t.last_updated_by = FND_GLOBAL.USER_ID,
3169: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
3170: WHERE t.allocation_for = 'PROD'
3171: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
3172: FROM OZF_PRODUCT_ALLOCATIONS p
3173: WHERE p.allocation_for = 'FUND'
3174: AND p.allocation_for_id = l_fund_id );
3175:
3176: IF (SQL%NOTFOUND) THEN

Line 3186: UPDATE OZF_PRODUCT_ALLOCATIONS p

3182: RAISE fnd_api.g_exc_unexpected_error;
3183: END IF;
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),

Line 3219: UPDATE OZF_PRODUCT_ALLOCATIONS p

3215: IF l_multiplying_factor = 0 THEN
3216:
3217: l_denominator := l_period_tbl.COUNT;
3218: /*
3219: UPDATE OZF_PRODUCT_ALLOCATIONS p
3220: SET p.TARGET = ROUND( l_total_root_quota, 0),
3221: p.object_version_number = p.object_version_number + 1,
3222: p.last_update_date = SYSDATE,
3223: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 3246: FROM OZF_PRODUCT_ALLOCATIONS p

3242: t.last_updated_by = FND_GLOBAL.USER_ID,
3243: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
3244: WHERE t.allocation_for = 'PROD'
3245: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
3246: FROM OZF_PRODUCT_ALLOCATIONS p
3247: WHERE p.allocation_for = 'FUND'
3248: AND p.allocation_for_id = l_fund_id
3249: AND p.item_id = -9999 );
3250:

Line 3260: UPDATE OZF_PRODUCT_ALLOCATIONS p

3256: RAISE fnd_api.g_exc_unexpected_error;
3257: END IF;
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),

Line 3294: FROM OZF_PRODUCT_ALLOCATIONS p

3290:
3291: BEGIN
3292:
3293: SELECT p.TARGET INTO l_diff_target_1
3294: FROM OZF_PRODUCT_ALLOCATIONS p
3295: WHERE p.allocation_for = 'FUND'
3296: AND p.allocation_for_id = l_fund_id
3297: AND p.item_id = -9999;
3298:

Line 3304: FROM OZF_PRODUCT_ALLOCATIONS p

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'
3306: AND p.allocation_for_id = l_fund_id
3307: AND p.item_id = -9999 );
3308:

Line 3325: FROM OZF_PRODUCT_ALLOCATIONS p

3321: SELECT (a.target-b.target) INTO l_diff_target
3322: FROM
3323: (
3324: SELECT p.TARGET target
3325: FROM OZF_PRODUCT_ALLOCATIONS p
3326: WHERE p.allocation_for = 'FUND'
3327: AND p.allocation_for_id = l_fund_id
3328: AND p.item_id = -9999
3329: ) a,

Line 3335: FROM OZF_PRODUCT_ALLOCATIONS p

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'
3337: AND p.allocation_for_id = l_fund_id
3338: AND p.item_id = -9999 )
3339: ) b;

Line 3348: FROM OZF_PRODUCT_ALLOCATIONS p

3344: BEGIN
3345: SELECT
3346: (
3347: SELECT p.TARGET
3348: FROM OZF_PRODUCT_ALLOCATIONS p
3349: WHERE p.allocation_for = 'FUND'
3350: AND p.allocation_for_id = l_fund_id
3351: AND p.item_id = -9999
3352: )

Line 3359: FROM OZF_PRODUCT_ALLOCATIONS p

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'
3361: AND p.allocation_for_id = l_fund_id
3362: AND p.item_id = -9999 )
3363: ) diff_target INTO l_diff_target

Line 3384: FROM OZF_PRODUCT_ALLOCATIONS p

3380:
3381: BEGIN
3382:
3383: SELECT SUM(p.TARGET) INTO l_diff_target_1
3384: FROM OZF_PRODUCT_ALLOCATIONS p
3385: WHERE p.allocation_for = 'FUND'
3386: AND p.allocation_for_id = l_fund_id;
3387:
3388: l_diff_target := (NVL(l_total_root_quota, 0) - NVL(l_diff_target_1, 0));

Line 3427: FROM OZF_PRODUCT_ALLOCATIONS p

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'
3429: AND p.allocation_for_id = l_fund_id
3430: AND p.item_id = -9999 )
3431: );

Line 3594: UPDATE ozf_product_allocations p

3590:
3591: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Publishing Product Allocations Records'
3592: || 'FOR Fact_id = '|| l_fact_id || ' with NEW Fund_id = '||l_fund_id||' ; ');
3593:
3594: UPDATE ozf_product_allocations p
3595: SET p.fund_id = l_fund_id,
3596: p.object_version_number = p.object_version_number + 1,
3597: p.last_update_date = SYSDATE,
3598: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 3681: ozf_product_allocations p

3677: (l_fact_id number) IS
3678: SELECT DISTINCT
3679: p.product_allocation_id
3680: FROM
3681: ozf_product_allocations p
3682: WHERE
3683: p.allocation_for = 'FACT'
3684: AND p.allocation_for_id = l_fact_id;
3685:

Line 3725: DELETE ozf_product_allocations p

3721: WHERE t.allocation_for_id = fact_product_spread_rec.product_allocation_id
3722: AND t.allocation_for = 'PROD';
3723: END LOOP;
3724:
3725: DELETE ozf_product_allocations p
3726: WHERE p.allocation_for = 'FACT'
3727: AND p.allocation_for_id = l_fact_id;
3728:
3729:

Line 3854: ozf_product_allocations p

3850: (l_fund_id number) IS
3851: SELECT DISTINCT
3852: p.product_allocation_id
3853: FROM
3854: ozf_product_allocations p
3855: WHERE
3856: p.allocation_for = 'FUND'
3857: AND p.allocation_for_id = l_fund_id
3858: AND p.fund_id = l_fund_id;

Line 3938: DELETE ozf_product_allocations p

3934: WHERE t.allocation_for_id = fund_product_spread_rec.product_allocation_id
3935: AND t.allocation_for = 'PROD';
3936: END LOOP;
3937:
3938: DELETE ozf_product_allocations p
3939: WHERE p.allocation_for = 'FUND'
3940: AND p.allocation_for_id = l_fund_id
3941: AND p.fund_id = l_fund_id;
3942:

Line 4028: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

4024: l_lysp_time_id NUMBER;
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

Line 4101: FROM OZF_PRODUCT_ALLOCATIONS

4097: item_id,
4098: item_type,
4099: target,
4100: lysp_sales
4101: FROM OZF_PRODUCT_ALLOCATIONS
4102: WHERE allocation_for = 'FACT'
4103: AND allocation_for_id = l_fact_id
4104: AND item_id = -9999;
4105:

Line 4255: Ozf_Product_Allocations_Pkg.Insert_Row(

4251: p_prod_alloc_rec.target := 0;
4252: p_prod_alloc_rec.lysp_sales := 0;
4253:
4254:
4255: Ozf_Product_Allocations_Pkg.Insert_Row(
4256: px_product_allocation_id => l_product_allocation_id,
4257: p_allocation_for => p_prod_alloc_rec.allocation_for,
4258: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
4259: p_fund_id => p_prod_alloc_rec.fund_id,

Line 4365: UPDATE OZF_PRODUCT_ALLOCATIONS p

4361:
4362:
4363: END LOOP;
4364:
4365: UPDATE OZF_PRODUCT_ALLOCATIONS p
4366: SET p.lysp_sales = l_total_lysp_sales,
4367: p.object_version_number = p.object_version_number + 1,
4368: p.last_update_date = SYSDATE,
4369: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 4467: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

4463: l_lysp_time_id NUMBER;
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

Line 4541: FROM OZF_PRODUCT_ALLOCATIONS

4537: item_id,
4538: item_type,
4539: target,
4540: lysp_sales
4541: FROM OZF_PRODUCT_ALLOCATIONS
4542: WHERE allocation_for = 'FACT'
4543: AND allocation_for_id = l_fact_id
4544: AND item_id = l_item_id
4545: AND item_type = l_item_type;

Line 4710: UPDATE ozf_product_allocations p -- Update Others Quota for Q3-03 etc

4706: AND t.allocation_for = 'PROD';
4707:
4708: END LOOP;
4709:
4710: UPDATE ozf_product_allocations p -- Update Others Quota for Q3-03 etc
4711: SET p.target = p.target + NVL(l_prod_alloc_rec.target, 0),
4712: p.object_version_number = p.object_version_number + 1,
4713: p.last_update_date = SYSDATE,
4714: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 4727: DELETE ozf_product_allocations p

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
4728: WHERE p.product_allocation_id = l_prod_alloc_rec.product_allocation_id;
4729:
4730:
4731: <>

Line 4817: ozf_product_allocations p

4813: (l_acct_allocation_id number) IS
4814: SELECT DISTINCT
4815: p.product_allocation_id
4816: FROM
4817: ozf_product_allocations p
4818: WHERE
4819: p.allocation_for = 'CUST'
4820: AND p.allocation_for_id = l_acct_allocation_id;
4821:

Line 4868: DELETE ozf_product_allocations p

4864: WHERE t.allocation_for_id = product_rec.product_allocation_id
4865: AND t.allocation_for = 'PROD';
4866: END LOOP;
4867:
4868: DELETE ozf_product_allocations p
4869: WHERE p.allocation_for = 'CUST'
4870: AND p.allocation_for_id = account_rec.account_allocation_id;
4871:
4872: END LOOP;

Line 4989: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

4985: l_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
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:

Line 5143: ozf_product_allocations p

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
5147: AND t.allocation_for = 'PROD'

Line 5155: ' ozf_product_allocations p'||

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'||
5159: ' AND t.allocation_for = ''PROD'' '||

Line 5209: FROM ozf_product_allocations p

5205: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
5206: AND DENORM.PARENT_ID = l_category_id
5207: MINUS
5208: SELECT p.item_id
5209: FROM ozf_product_allocations p
5210: WHERE p.fund_id = l_fund_id
5211: AND p.item_type = 'PRICING_ATTRIBUTE1'
5212: );
5213:

Line 5233: FROM ozf_product_allocations p

5229: AND bsmv.time_id = l_time_id
5230: AND NOT EXISTS
5231: (
5232: ( SELECT p.item_id
5233: FROM ozf_product_allocations p
5234: WHERE
5235: p.fund_id = l_fund_id
5236: AND p.item_type = 'PRICING_ATTRIBUTE1'
5237: AND p.item_id = bsmv.inventory_item_id

Line 5242: OZF_PRODUCT_ALLOCATIONS p

5238: UNION ALL
5239: SELECT MIC.INVENTORY_ITEM_ID
5240: FROM MTL_ITEM_CATEGORIES MIC,
5241: ENI_PROD_DENORM_HRCHY_V DENORM,
5242: OZF_PRODUCT_ALLOCATIONS p
5243: WHERE p.FUND_ID = l_fund_id
5244: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
5245: AND p.ITEM_ID = DENORM.PARENT_ID
5246: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 5269: ozf_product_allocations p

5265: p.item_id,
5266: p.item_type,
5267: p.target
5268: FROM
5269: ozf_product_allocations p
5270: WHERE
5271: p.fund_id = l_fund_id;
5272:
5273: l_fund_product_rec fund_product_spread_csr%rowtype;

Line 5617: Ozf_Product_Allocations_Pkg.Insert_Row(

5613: p_prod_alloc_rec.lysp_sales := 0;
5614:
5615: l_product_allocation_id := get_product_allocation_id;
5616:
5617: Ozf_Product_Allocations_Pkg.Insert_Row(
5618: px_product_allocation_id => l_product_allocation_id,
5619: p_allocation_for => p_prod_alloc_rec.allocation_for,
5620: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
5621: p_fund_id => p_prod_alloc_rec.fund_id,

Line 5750: UPDATE OZF_PRODUCT_ALLOCATIONS p

5746:
5747: END IF;
5748: END LOOP account_product_time_loop;
5749:
5750: UPDATE OZF_PRODUCT_ALLOCATIONS p
5751: SET p.lysp_sales = l_total_product_sales,
5752: p.object_version_number = p.object_version_number + 1,
5753: p.last_update_date = SYSDATE,
5754: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 6100: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

6096: l_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
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:

Line 6255: ozf_product_allocations p

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
6259: AND t.allocation_for = 'PROD'

Line 6267: ' ozf_product_allocations p'||

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'||
6271: ' AND t.allocation_for = ''PROD'' '||

Line 6321: FROM ozf_product_allocations p

6317: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
6318: AND DENORM.PARENT_ID = l_category_id
6319: MINUS
6320: SELECT p.item_id
6321: FROM ozf_product_allocations p
6322: WHERE p.fund_id = l_fund_id
6323: AND p.item_type = 'PRICING_ATTRIBUTE1'
6324: );
6325:

Line 6345: FROM ozf_product_allocations p

6341: AND bsmv.time_id = l_time_id
6342: AND NOT EXISTS
6343: (
6344: ( SELECT p.item_id
6345: FROM ozf_product_allocations p
6346: WHERE
6347: p.fund_id = l_fund_id
6348: AND p.item_type = 'PRICING_ATTRIBUTE1'
6349: AND p.item_id = bsmv.inventory_item_id

Line 6354: OZF_PRODUCT_ALLOCATIONS p

6350: UNION ALL
6351: SELECT MIC.INVENTORY_ITEM_ID
6352: FROM MTL_ITEM_CATEGORIES MIC,
6353: ENI_PROD_DENORM_HRCHY_V DENORM,
6354: OZF_PRODUCT_ALLOCATIONS p
6355: WHERE p.FUND_ID = l_fund_id
6356: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
6357: AND p.ITEM_ID = DENORM.PARENT_ID
6358: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 6381: ozf_product_allocations p

6377: p.item_id,
6378: p.item_type,
6379: p.target
6380: FROM
6381: ozf_product_allocations p
6382: WHERE
6383: p.fund_id = l_fund_id;
6384:
6385: l_fund_product_rec fund_product_spread_csr%rowtype;

Line 6771: Ozf_Product_Allocations_Pkg.Insert_Row(

6767: p_prod_alloc_rec.lysp_sales := 0;
6768:
6769: l_product_allocation_id := get_product_allocation_id;
6770:
6771: Ozf_Product_Allocations_Pkg.Insert_Row(
6772: px_product_allocation_id => l_product_allocation_id,
6773: p_allocation_for => p_prod_alloc_rec.allocation_for,
6774: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
6775: p_fund_id => p_prod_alloc_rec.fund_id,

Line 6907: UPDATE OZF_PRODUCT_ALLOCATIONS p

6903:
6904: END IF;
6905: END LOOP account_product_time_loop;
6906:
6907: UPDATE OZF_PRODUCT_ALLOCATIONS p
6908: SET p.lysp_sales = l_total_product_sales,
6909: p.object_version_number = p.object_version_number + 1,
6910: p.last_update_date = SYSDATE,
6911: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 6942: FROM OZF_PRODUCT_ALLOCATIONS p

6938: t.last_updated_by = FND_GLOBAL.USER_ID,
6939: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
6940: WHERE t.allocation_for = 'PROD'
6941: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
6942: FROM OZF_PRODUCT_ALLOCATIONS p
6943: WHERE p.allocation_for = 'CUST'
6944: AND p.allocation_for_id = l_account_allocation_id);
6945:
6946: IF (SQL%NOTFOUND) THEN

Line 6955: UPDATE OZF_PRODUCT_ALLOCATIONS p

6951:
6952: RAISE fnd_api.g_exc_unexpected_error;
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),

Line 6987: UPDATE OZF_PRODUCT_ALLOCATIONS p

6983:
6984: l_p_denominator := l_period_tbl.COUNT;
6985:
6986: /*
6987: UPDATE OZF_PRODUCT_ALLOCATIONS p
6988: SET p.TARGET = ROUND(NVL(l_total_account_target, 0),0),
6989: p.object_version_number = p.object_version_number + 1,
6990: p.last_update_date = SYSDATE,
6991: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 7015: FROM OZF_PRODUCT_ALLOCATIONS p

7011: t.last_updated_by = FND_GLOBAL.USER_ID,
7012: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
7013: WHERE t.allocation_for = 'PROD'
7014: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
7015: FROM OZF_PRODUCT_ALLOCATIONS p
7016: WHERE p.allocation_for = 'CUST'
7017: AND p.allocation_for_id = l_account_allocation_id
7018: AND p.item_id = -9999 );
7019:

Line 7028: UPDATE OZF_PRODUCT_ALLOCATIONS p

7024: END IF;
7025: RAISE fnd_api.g_exc_unexpected_error;
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),

Line 7064: FROM OZF_PRODUCT_ALLOCATIONS p

7060: BEGIN
7061:
7062:
7063: SELECT SUM(p.TARGET) INTO l_diff_target_1
7064: FROM OZF_PRODUCT_ALLOCATIONS p
7065: WHERE p.allocation_for = 'CUST'
7066: AND p.allocation_for_id = l_account_allocation_id;
7067:
7068: l_diff_target := (NVL(l_total_account_target, 0) - NVL(l_diff_target_1, 0));

Line 7101: FROM OZF_PRODUCT_ALLOCATIONS p

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'
7103: AND p.allocation_for_id = l_account_allocation_id
7104: AND p.target =
7105: (SELECT max(xz.target)

Line 7106: FROM OZF_PRODUCT_ALLOCATIONS xz

7102: WHERE p.allocation_for = 'CUST'
7103: AND p.allocation_for_id = l_account_allocation_id
7104: AND p.target =
7105: (SELECT max(xz.target)
7106: FROM OZF_PRODUCT_ALLOCATIONS xz
7107: WHERE xz.allocation_for = 'CUST'
7108: AND xz.allocation_for_id = l_account_allocation_id
7109: )
7110:

Line 7117: FROM OZF_PRODUCT_ALLOCATIONS pz

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'
7119: AND pz.allocation_for_id = l_account_allocation_id
7120: AND pz.target =
7121: (SELECT max(xz.target)

Line 7122: FROM OZF_PRODUCT_ALLOCATIONS xz

7118: WHERE pz.allocation_for = 'CUST'
7119: AND pz.allocation_for_id = l_account_allocation_id
7120: AND pz.target =
7121: (SELECT max(xz.target)
7122: FROM OZF_PRODUCT_ALLOCATIONS xz
7123: WHERE xz.allocation_for = 'CUST'
7124: AND xz.allocation_for_id = l_account_allocation_id
7125: )
7126:

Line 7141: UPDATE OZF_PRODUCT_ALLOCATIONS p

7137: END IF;
7138: RAISE fnd_api.g_exc_unexpected_error;
7139: END IF;
7140:
7141: UPDATE OZF_PRODUCT_ALLOCATIONS p
7142: SET p.TARGET = p.TARGET + l_diff_target,
7143: p.object_version_number = p.object_version_number + 1,
7144: p.last_update_date = SYSDATE,
7145: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 7169: FROM OZF_PRODUCT_ALLOCATIONS p

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'
7171: AND p.allocation_for_id = l_account_allocation_id
7172: AND p.item_id = -9999 )
7173: );

Line 7184: UPDATE OZF_PRODUCT_ALLOCATIONS p

7180: END IF;
7181: RAISE fnd_api.g_exc_unexpected_error;
7182: END IF;
7183:
7184: UPDATE OZF_PRODUCT_ALLOCATIONS p
7185: SET p.TARGET = p.TARGET + l_diff_target,
7186: p.object_version_number = p.object_version_number + 1,
7187: p.last_update_date = SYSDATE,
7188: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 7577: FROM OZF_PRODUCT_ALLOCATIONS p

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'
7579: AND p.allocation_for_id = l_temp_account_allocation_id
7580: AND p.target =
7581: (SELECT max(xz.target)

Line 7582: FROM OZF_PRODUCT_ALLOCATIONS xz

7578: WHERE p.allocation_for = 'CUST'
7579: AND p.allocation_for_id = l_temp_account_allocation_id
7580: AND p.target =
7581: (SELECT max(xz.target)
7582: FROM OZF_PRODUCT_ALLOCATIONS xz
7583: WHERE xz.allocation_for = 'CUST'
7584: AND xz.allocation_for_id = l_temp_account_allocation_id
7585: )
7586:

Line 7593: FROM OZF_PRODUCT_ALLOCATIONS pz

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'
7595: AND pz.allocation_for_id = l_temp_account_allocation_id
7596: AND pz.target =
7597: (SELECT max(xz.target)

Line 7598: FROM OZF_PRODUCT_ALLOCATIONS xz

7594: WHERE pz.allocation_for = 'CUST'
7595: AND pz.allocation_for_id = l_temp_account_allocation_id
7596: AND pz.target =
7597: (SELECT max(xz.target)
7598: FROM OZF_PRODUCT_ALLOCATIONS xz
7599: WHERE xz.allocation_for = 'CUST'
7600: AND xz.allocation_for_id = l_temp_account_allocation_id
7601: )
7602:

Line 7617: UPDATE OZF_PRODUCT_ALLOCATIONS p

7613: END IF;
7614: RAISE fnd_api.g_exc_unexpected_error;
7615: END IF;
7616:
7617: UPDATE OZF_PRODUCT_ALLOCATIONS p
7618: SET p.TARGET = p.TARGET + l_diff_target,
7619: p.object_version_number = p.object_version_number + 1,
7620: p.last_update_date = SYSDATE,
7621: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 7779: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

7775: l_new_ozf_period_tbl OZF_PERIOD_TBL_TYPE;
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:

Line 7885: ozf_product_allocations p

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
7889: AND t.allocation_for = 'PROD'

Line 7897: ' ozf_product_allocations p'||

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'||
7901: ' AND t.allocation_for = ''PROD'' '||

Line 7947: FROM ozf_product_allocations p

7943: FROM mtl_item_categories mtl
7944: WHERE mtl.category_id = l_category_id
7945: MINUS
7946: SELECT p.item_id
7947: FROM ozf_product_allocations p
7948: WHERE p.fund_id = l_fund_id
7949: AND p.item_type = 'PRICING_ATTRIBUTE1'
7950: );
7951:

Line 7971: FROM ozf_product_allocations p

7967: AND bsmv.time_id = l_time_id
7968: AND NOT EXISTS
7969: (
7970: ( SELECT p.item_id
7971: FROM ozf_product_allocations p
7972: WHERE
7973: p.fund_id = l_fund_id
7974: AND p.item_type = 'PRICING_ATTRIBUTE1'
7975: AND p.item_id = bsmv.inventory_item_id

Line 7978: FROM ozf_product_allocations p,

7974: AND p.item_type = 'PRICING_ATTRIBUTE1'
7975: AND p.item_id = bsmv.inventory_item_id
7976: UNION ALL
7977: SELECT mtl.inventory_item_id
7978: FROM ozf_product_allocations p,
7979: mtl_item_categories mtl
7980: WHERE
7981: p.fund_id = l_fund_id
7982: AND p.item_type = 'PRICING_ATTRIBUTE2'

Line 8005: ozf_product_allocations p

8001: p.item_id,
8002: p.item_type,
8003: p.target
8004: FROM
8005: ozf_product_allocations p
8006: WHERE
8007: p.allocation_for = 'CUST'
8008: AND p.allocation_for_id = l_account_allocation_id;
8009:

Line 8032: ozf_product_allocations p

8028: SELECT
8029: p.product_allocation_id,
8030: p.target
8031: FROM
8032: ozf_product_allocations p
8033: WHERE
8034: p.fund_id = l_fund_id
8035: AND p.allocation_for = 'FACT'
8036: AND p.allocation_for_id = l_addon_fact_id

Line 8462: UPDATE OZF_PRODUCT_ALLOCATIONS p

8458:
8459: END IF;
8460: END LOOP account_product_time_loop;
8461:
8462: UPDATE OZF_PRODUCT_ALLOCATIONS p
8463: SET p.lysp_sales = p.lysp_sales + l_total_product_sales,
8464: p.target = p.target + l_total_product_target,
8465: p.object_version_number = p.object_version_number + 1,
8466: p.last_update_date = SYSDATE,

Line 8630: from ozf_product_allocations

8626: l_count number;
8627: CURSOR prod_alloc_count_csr (l_fund_id NUMBER)
8628: IS
8629: select count(product_allocation_id)
8630: from ozf_product_allocations
8631: where fund_id = l_fund_id;
8632:
8633: BEGIN
8634:

Line 9382: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

9378: l_period_tbl OZF_TIME_API_PVT.G_period_tbl_type;
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

Line 9451: FROM ozf_product_allocations p

9447: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
9448: AND DENORM.PARENT_ID = l_category_id
9449: MINUS
9450: SELECT p.item_id
9451: FROM ozf_product_allocations p
9452: WHERE p.fund_id = l_fund_id
9453: AND p.item_type = 'PRICING_ATTRIBUTE1'
9454: );
9455:

Line 9475: FROM ozf_product_allocations p

9471: AND bsmv.time_id = l_time_id
9472: AND NOT EXISTS
9473: (
9474: ( SELECT p.item_id
9475: FROM ozf_product_allocations p
9476: WHERE
9477: p.fund_id = l_fund_id
9478: AND p.item_type = 'PRICING_ATTRIBUTE1'
9479: AND p.item_id = bsmv.inventory_item_id

Line 9484: OZF_PRODUCT_ALLOCATIONS p

9480: UNION ALL
9481: SELECT MIC.INVENTORY_ITEM_ID
9482: FROM MTL_ITEM_CATEGORIES MIC,
9483: ENI_PROD_DENORM_HRCHY_V DENORM,
9484: OZF_PRODUCT_ALLOCATIONS p
9485: WHERE p.FUND_ID = l_fund_id
9486: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
9487: AND p.ITEM_ID = DENORM.PARENT_ID
9488: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 9511: ozf_product_allocations p

9507: p.item_id,
9508: p.item_type,
9509: p.target
9510: FROM
9511: ozf_product_allocations p
9512: WHERE
9513: p.fund_id = l_fund_id;
9514:
9515: l_fund_product_rec fund_product_spread_csr%rowtype;

Line 9529: ozf_product_allocations p

9525: CURSOR corr_prod_alloc_rec
9526: (l_acct_alloc_id number) IS
9527: SELECT *
9528: FROM
9529: ozf_product_allocations p
9530: WHERE
9531: p.allocation_for = 'CUST'
9532: AND p.allocation_for_id = l_acct_alloc_id; -- p_acct_alloc_rec.account_allocation_id;
9533:

Line 9945: Ozf_Product_Allocations_Pkg.Insert_Row(

9941: LOOP
9942:
9943: l_product_allocation_id := get_product_allocation_id;
9944:
9945: Ozf_Product_Allocations_Pkg.Insert_Row(
9946: px_product_allocation_id => l_product_allocation_id,
9947: p_allocation_for => p_prod_alloc_rec.allocation_for,
9948: p_allocation_for_id => l_account_allocation_id,
9949: p_fund_id => p_fund_id,

Line 10047: UPDATE OZF_PRODUCT_ALLOCATIONS p

10043:
10044: -- 11. Updating SHIPTOs Product Spread - Header Records ....
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),

Line 10082: Ozf_Product_Allocations_Pkg.Insert_Row(

10078: p_prod_alloc_rec.lysp_sales := 0;
10079:
10080: l_product_allocation_id := get_product_allocation_id;
10081:
10082: Ozf_Product_Allocations_Pkg.Insert_Row(
10083: px_product_allocation_id => l_product_allocation_id,
10084: p_allocation_for => p_prod_alloc_rec.allocation_for,
10085: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
10086: p_fund_id => p_fund_id,

Line 10210: UPDATE OZF_PRODUCT_ALLOCATIONS p

10206:
10207:
10208: -- 15. UPDATING SHIPTOs Product Spread - Header Record ....
10209:
10210: UPDATE OZF_PRODUCT_ALLOCATIONS p
10211: SET p.lysp_sales = l_total_product_sales,
10212: p.object_version_number = p.object_version_number + 1,
10213: p.last_update_date = SYSDATE,
10214: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 10466: FROM ozf_product_allocations pp

10462: tt.account_status = 'D'
10463: WHERE tt.allocation_for = 'PROD'
10464: AND tt.allocation_for_id IN (
10465: SELECT pp.product_allocation_id
10466: FROM ozf_product_allocations pp
10467: WHERE pp.allocation_for = 'CUST'
10468: AND pp.allocation_for_id = l_account_allocation_id
10469: )
10470: and EXISTS

Line 10491: UPDATE OZF_PRODUCT_ALLOCATIONS p

10487: BEGIN
10488: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 5. ADJUSTING ShipTos Product Spread Header Records....');
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),

Line 10503: FROM ozf_product_allocations pp

10499: p.last_updated_by = FND_GLOBAL.USER_ID,
10500: p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
10501: WHERE p.product_allocation_id IN (
10502: SELECT pp.product_allocation_id
10503: FROM ozf_product_allocations pp
10504: WHERE pp.allocation_for = 'CUST'
10505: AND pp.allocation_for_id = l_account_allocation_id
10506: );
10507: END;