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 101: SELECT ozf_product_allocations_s.NEXTVAL

97: l_api_name CONSTANT VARCHAR2(30) := 'get_product_allocation_id';
98: l_full_api_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
99:
100: CURSOR product_seq_csr IS
101: SELECT ozf_product_allocations_s.NEXTVAL
102: FROM DUAL;
103:
104: CURSOR product_alloc_count_csr(p_product_alloc_id in number) IS
105: SELECT count(p.product_allocation_id)

Line 106: FROM ozf_product_allocations p

102: FROM DUAL;
103:
104: CURSOR product_alloc_count_csr(p_product_alloc_id in number) IS
105: SELECT count(p.product_allocation_id)
106: FROM ozf_product_allocations p
107: WHERE p.product_allocation_id = p_product_alloc_id;
108:
109: l_count number := -1;
110: l_product_alloc_id number := -1;

Line 345: FROM ozf_product_allocations p

341: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
342: AND DENORM.PARENT_ID = l_category_id
343: MINUS
344: SELECT p.item_id
345: FROM ozf_product_allocations p
346: WHERE p.fund_id = l_fund_id
347: AND p.item_type = 'PRICING_ATTRIBUTE1'
348: );
349:

Line 420: FROM ozf_product_allocations p

416: AND bsmv.time_id = l_time_id
417: AND NOT EXISTS
418: (
419: ( SELECT p.item_id
420: FROM ozf_product_allocations p
421: WHERE
422: p.fund_id = l_fund_id
423: AND p.item_type = 'PRICING_ATTRIBUTE1'
424: AND p.item_id = bsmv.inventory_item_id

Line 429: OZF_PRODUCT_ALLOCATIONS p

425: UNION ALL
426: SELECT MIC.INVENTORY_ITEM_ID
427: FROM MTL_ITEM_CATEGORIES MIC,
428: ENI_PROD_DENORM_HRCHY_V DENORM,
429: OZF_PRODUCT_ALLOCATIONS p
430: WHERE p.FUND_ID = l_fund_id
431: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
432: AND p.ITEM_ID = DENORM.PARENT_ID
433: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 678: FROM OZF_PRODUCT_ALLOCATIONS p

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
680: AND p.allocation_for_id = l_object_id
681: AND p.target =
682: (SELECT max(xz.target)

Line 683: FROM OZF_PRODUCT_ALLOCATIONS xz

679: WHERE p.allocation_for = l_object_type
680: AND p.allocation_for_id = l_object_id
681: AND p.target =
682: (SELECT max(xz.target)
683: FROM OZF_PRODUCT_ALLOCATIONS xz
684: WHERE xz.allocation_for = l_object_type
685: AND xz.allocation_for_id = l_object_id
686: )
687:

Line 694: FROM OZF_PRODUCT_ALLOCATIONS pz

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
696: AND pz.allocation_for_id = l_object_id
697: AND pz.target =
698: (SELECT max(xz.target)

Line 699: FROM OZF_PRODUCT_ALLOCATIONS xz

695: WHERE pz.allocation_for = l_object_type
696: AND pz.allocation_for_id = l_object_id
697: AND pz.target =
698: (SELECT max(xz.target)
699: FROM OZF_PRODUCT_ALLOCATIONS xz
700: WHERE xz.allocation_for = l_object_type
701: AND xz.allocation_for_id = l_object_id
702: )
703:

Line 720: UPDATE OZF_PRODUCT_ALLOCATIONS p

716: END IF;
717: RAISE fnd_api.g_exc_unexpected_error;
718: END IF;
719:
720: UPDATE OZF_PRODUCT_ALLOCATIONS p
721: SET p.TARGET = p.TARGET + l_diff_target,
722: p.object_version_number = p.object_version_number + 1,
723: p.last_update_date = SYSDATE,
724: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 748: FROM OZF_PRODUCT_ALLOCATIONS p

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'
750: AND p.allocation_for_id = l_account_allocation_id
751: AND p.item_id = -9999 )
752: );

Line 763: UPDATE OZF_PRODUCT_ALLOCATIONS p

759: END IF;
760: RAISE fnd_api.g_exc_unexpected_error;
761: END IF;
762:
763: UPDATE OZF_PRODUCT_ALLOCATIONS p
764: SET p.TARGET = p.TARGET + l_diff_target,
765: p.object_version_number = p.object_version_number + 1,
766: p.last_update_date = SYSDATE,
767: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 849: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

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

Line 921: ozf_product_allocations p

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

Line 933: ' ozf_product_allocations p'||

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

Line 951: ozf_product_allocations p

947: p.product_allocation_id,
948: p.item_id,
949: p.item_type
950: FROM
951: ozf_product_allocations p
952: WHERE
953: p.fund_id = l_fund_id;
954:
955: l_root_product_rec root_product_spread_csr%rowtype;

Line 1010: FROM ozf_product_allocations p

1006: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
1007: AND DENORM.PARENT_ID = l_category_id
1008: MINUS
1009: SELECT p.item_id
1010: FROM ozf_product_allocations p
1011: WHERE p.fund_id = l_fund_id
1012: AND p.item_type = 'PRICING_ATTRIBUTE1'
1013: );
1014:

Line 1032: FROM ozf_product_allocations p

1028: AND bsmv.time_id = l_time_id
1029: AND NOT EXISTS
1030: (
1031: ( SELECT p.item_id
1032: FROM ozf_product_allocations p
1033: WHERE
1034: p.fund_id = l_fund_id
1035: AND p.item_type = 'PRICING_ATTRIBUTE1'
1036: AND p.item_id = bsmv.inventory_item_id

Line 1041: OZF_PRODUCT_ALLOCATIONS p

1037: UNION ALL
1038: SELECT MIC.INVENTORY_ITEM_ID
1039: FROM MTL_ITEM_CATEGORIES MIC,
1040: ENI_PROD_DENORM_HRCHY_V DENORM,
1041: OZF_PRODUCT_ALLOCATIONS p
1042: WHERE p.FUND_ID = l_fund_id
1043: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
1044: AND p.ITEM_ID = DENORM.PARENT_ID
1045: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 1227: Ozf_Product_Allocations_Pkg.Insert_Row(

1223: p_prod_alloc_rec.target := 0;
1224: p_prod_alloc_rec.lysp_sales := 0;
1225:
1226:
1227: Ozf_Product_Allocations_Pkg.Insert_Row(
1228: px_product_allocation_id => l_product_allocation_id,
1229: p_allocation_for => p_prod_alloc_rec.allocation_for,
1230: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
1231: p_fund_id => p_prod_alloc_rec.fund_id,

Line 1360: UPDATE OZF_PRODUCT_ALLOCATIONS p

1356:
1357: END IF;
1358: END LOOP;
1359:
1360: UPDATE OZF_PRODUCT_ALLOCATIONS p
1361: SET p.lysp_sales = l_total_lysp_sales,
1362: p.target = ROUND( l_total_quota, 0),
1363: p.object_version_number = p.object_version_number + 1,
1364: p.last_update_date = SYSDATE,

Line 1381: FROM OZF_PRODUCT_ALLOCATIONS p

1377: BEGIN
1378:
1379:
1380: SELECT SUM(p.TARGET) INTO l_diff_target_1
1381: FROM OZF_PRODUCT_ALLOCATIONS p
1382: WHERE p.allocation_for = 'FACT'
1383: AND p.allocation_for_id = l_fact_id;
1384:
1385: l_diff_target := ROUND((NVL(l_fact_rec.recommend_total_amount, 0) - NVL(l_diff_target_1, 0)), 0);

Line 1403: FROM OZF_PRODUCT_ALLOCATIONS p

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'
1405: AND p.allocation_for_id = l_fact_id
1406: AND p.item_id = -9999 )
1407: );

Line 1420: UPDATE OZF_PRODUCT_ALLOCATIONS p

1416: RAISE fnd_api.g_exc_unexpected_error;
1417: END IF;
1418:
1419:
1420: UPDATE OZF_PRODUCT_ALLOCATIONS p
1421: SET p.TARGET = p.TARGET + l_diff_target,
1422: p.object_version_number = p.object_version_number + 1,
1423: p.last_update_date = SYSDATE,
1424: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 1558: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

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

Line 1630: ozf_product_allocations p

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

Line 1642: ' ozf_product_allocations p'||

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

Line 1660: ozf_product_allocations p

1656: p.product_allocation_id,
1657: p.item_id,
1658: p.item_type
1659: FROM
1660: ozf_product_allocations p
1661: WHERE
1662: p.fund_id = l_fund_id;
1663:
1664: l_root_product_rec root_product_spread_csr%rowtype;

Line 1719: FROM ozf_product_allocations p

1715: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
1716: AND DENORM.PARENT_ID = l_category_id
1717: MINUS
1718: SELECT p.item_id
1719: FROM ozf_product_allocations p
1720: WHERE p.fund_id = l_fund_id
1721: AND p.item_type = 'PRICING_ATTRIBUTE1'
1722: );
1723:

Line 1741: FROM ozf_product_allocations p

1737: AND bsmv.time_id = l_time_id
1738: AND NOT EXISTS
1739: (
1740: ( SELECT p.item_id
1741: FROM ozf_product_allocations p
1742: WHERE
1743: p.fund_id = l_fund_id
1744: AND p.item_type = 'PRICING_ATTRIBUTE1'
1745: AND p.item_id = bsmv.inventory_item_id

Line 1750: OZF_PRODUCT_ALLOCATIONS p

1746: UNION ALL
1747: SELECT MIC.INVENTORY_ITEM_ID
1748: FROM MTL_ITEM_CATEGORIES MIC,
1749: ENI_PROD_DENORM_HRCHY_V DENORM,
1750: OZF_PRODUCT_ALLOCATIONS p
1751: WHERE p.FUND_ID = l_fund_id
1752: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
1753: AND p.ITEM_ID = DENORM.PARENT_ID
1754: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 1944: Ozf_Product_Allocations_Pkg.Insert_Row(

1940: p_prod_alloc_rec.target := 0;
1941: p_prod_alloc_rec.lysp_sales := 0;
1942:
1943:
1944: Ozf_Product_Allocations_Pkg.Insert_Row(
1945: px_product_allocation_id => l_product_allocation_id,
1946: p_allocation_for => p_prod_alloc_rec.allocation_for,
1947: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
1948: p_fund_id => p_prod_alloc_rec.fund_id,

Line 2081: UPDATE OZF_PRODUCT_ALLOCATIONS p

2077:
2078: END IF;
2079: END LOOP;
2080:
2081: UPDATE OZF_PRODUCT_ALLOCATIONS p
2082: SET p.lysp_sales = l_total_lysp_sales,
2083: p.object_version_number = p.object_version_number + 1,
2084: p.last_update_date = SYSDATE,
2085: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 2113: FROM OZF_PRODUCT_ALLOCATIONS p

2109: t.last_updated_by = FND_GLOBAL.USER_ID,
2110: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
2111: WHERE t.allocation_for = 'PROD'
2112: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
2113: FROM OZF_PRODUCT_ALLOCATIONS p
2114: WHERE p.allocation_for = 'FACT'
2115: AND p.allocation_for_id = l_fact_id );
2116:
2117: IF (SQL%NOTFOUND) THEN

Line 2126: UPDATE OZF_PRODUCT_ALLOCATIONS p

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

Line 2158: UPDATE OZF_PRODUCT_ALLOCATIONS p

2154:
2155: l_denominator := l_period_tbl.COUNT;
2156:
2157: /*
2158: UPDATE OZF_PRODUCT_ALLOCATIONS p
2159: SET p.TARGET = ROUND(NVL(l_fact_rec.recommend_total_amount, 0),0),
2160: p.object_version_number = p.object_version_number + 1,
2161: p.last_update_date = SYSDATE,
2162: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 2185: FROM OZF_PRODUCT_ALLOCATIONS p

2181: t.last_updated_by = FND_GLOBAL.USER_ID,
2182: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
2183: WHERE t.allocation_for = 'PROD'
2184: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
2185: FROM OZF_PRODUCT_ALLOCATIONS p
2186: WHERE p.allocation_for = 'FACT'
2187: AND p.allocation_for_id = l_fact_id
2188: AND p.item_id = -9999 );
2189:

Line 2198: UPDATE OZF_PRODUCT_ALLOCATIONS p

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

Line 2233: FROM OZF_PRODUCT_ALLOCATIONS p

2229:
2230: BEGIN
2231:
2232: SELECT SUM(p.TARGET) INTO l_diff_target_1
2233: FROM OZF_PRODUCT_ALLOCATIONS p
2234: WHERE p.allocation_for = 'FACT'
2235: AND p.allocation_for_id = l_fact_id;
2236:
2237: l_diff_target := (NVL(l_fact_rec.recommend_total_amount, 0) - NVL(l_diff_target_1, 0));

Line 2264: FROM OZF_PRODUCT_ALLOCATIONS p

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'
2266: AND p.allocation_for_id = l_fact_id
2267: AND p.item_id = -9999 )
2268: );

Line 2279: UPDATE OZF_PRODUCT_ALLOCATIONS p

2275: END IF;
2276: RAISE fnd_api.g_exc_unexpected_error;
2277: END IF;
2278:
2279: UPDATE OZF_PRODUCT_ALLOCATIONS p
2280: SET p.TARGET = p.TARGET + l_diff_target,
2281: p.object_version_number = p.object_version_number + 1,
2282: p.last_update_date = SYSDATE,
2283: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 2409: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

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

Line 2713: Ozf_Product_Allocations_Pkg.Insert_Row(

2709: p_prod_alloc_rec.target := 0;
2710: p_prod_alloc_rec.lysp_sales := 0;
2711:
2712:
2713: Ozf_Product_Allocations_Pkg.Insert_Row(
2714: px_product_allocation_id => l_product_allocation_id,
2715: p_allocation_for => p_prod_alloc_rec.allocation_for,
2716: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
2717: p_fund_id => p_prod_alloc_rec.fund_id,

Line 2817: UPDATE OZF_PRODUCT_ALLOCATIONS p

2813:
2814: END IF;
2815: END LOOP;
2816:
2817: UPDATE OZF_PRODUCT_ALLOCATIONS p
2818: SET p.lysp_sales = NVL(l_total_lysp_sales,0),
2819: p.object_version_number = p.object_version_number + 1,
2820: p.last_update_date = SYSDATE,
2821: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 2850: Ozf_Product_Allocations_Pkg.Insert_Row(

2846: p_prod_alloc_rec.target := 0;
2847: p_prod_alloc_rec.lysp_sales := 0;
2848:
2849:
2850: Ozf_Product_Allocations_Pkg.Insert_Row(
2851: px_product_allocation_id => l_product_allocation_id,
2852: p_allocation_for => p_prod_alloc_rec.allocation_for,
2853: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
2854: p_fund_id => p_prod_alloc_rec.fund_id,

Line 2954: UPDATE OZF_PRODUCT_ALLOCATIONS p

2950:
2951: END IF;
2952: END LOOP;
2953:
2954: UPDATE OZF_PRODUCT_ALLOCATIONS p
2955: SET p.lysp_sales = NVL(l_total_lysp_sales, 0),
2956: p.object_version_number = p.object_version_number + 1,
2957: p.last_update_date = SYSDATE,
2958: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 2983: Ozf_Product_Allocations_Pkg.Insert_Row(

2979: p_prod_alloc_rec.target := 0;
2980: p_prod_alloc_rec.lysp_sales := 0;
2981:
2982:
2983: Ozf_Product_Allocations_Pkg.Insert_Row(
2984: px_product_allocation_id => l_product_allocation_id,
2985: p_allocation_for => p_prod_alloc_rec.allocation_for,
2986: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
2987: p_fund_id => p_prod_alloc_rec.fund_id,

Line 3085: UPDATE OZF_PRODUCT_ALLOCATIONS p

3081:
3082: END IF;
3083: END LOOP;
3084:
3085: UPDATE OZF_PRODUCT_ALLOCATIONS p
3086: SET p.lysp_sales = NVL(l_total_lysp_sales, 0),
3087: p.object_version_number = p.object_version_number + 1,
3088: p.last_update_date = SYSDATE,
3089: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 3120: UPDATE OZF_PRODUCT_ALLOCATIONS p

3116:
3117: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor == '
3118: ||l_multiplying_factor||' ;');
3119: /*
3120: UPDATE OZF_PRODUCT_ALLOCATIONS p
3121: SET p.TARGET = ROUND( (NVL(p.LYSP_SALES, 0) * l_multiplying_factor), 0),
3122: p.object_version_number = p.object_version_number + 1,
3123: p.last_update_date = SYSDATE,
3124: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 3147: FROM OZF_PRODUCT_ALLOCATIONS p

3143: t.last_updated_by = FND_GLOBAL.USER_ID,
3144: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
3145: WHERE t.allocation_for = 'PROD'
3146: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
3147: FROM OZF_PRODUCT_ALLOCATIONS p
3148: WHERE p.allocation_for = 'FUND'
3149: AND p.allocation_for_id = l_fund_id );
3150:
3151: IF (SQL%NOTFOUND) THEN

Line 3161: UPDATE OZF_PRODUCT_ALLOCATIONS p

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

Line 3194: UPDATE OZF_PRODUCT_ALLOCATIONS p

3190: IF l_multiplying_factor = 0 THEN
3191:
3192: l_denominator := l_period_tbl.COUNT;
3193: /*
3194: UPDATE OZF_PRODUCT_ALLOCATIONS p
3195: SET p.TARGET = ROUND( l_total_root_quota, 0),
3196: p.object_version_number = p.object_version_number + 1,
3197: p.last_update_date = SYSDATE,
3198: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 3221: FROM OZF_PRODUCT_ALLOCATIONS p

3217: t.last_updated_by = FND_GLOBAL.USER_ID,
3218: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
3219: WHERE t.allocation_for = 'PROD'
3220: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
3221: FROM OZF_PRODUCT_ALLOCATIONS p
3222: WHERE p.allocation_for = 'FUND'
3223: AND p.allocation_for_id = l_fund_id
3224: AND p.item_id = -9999 );
3225:

Line 3235: UPDATE OZF_PRODUCT_ALLOCATIONS p

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

Line 3269: FROM OZF_PRODUCT_ALLOCATIONS p

3265:
3266: BEGIN
3267:
3268: SELECT p.TARGET INTO l_diff_target_1
3269: FROM OZF_PRODUCT_ALLOCATIONS p
3270: WHERE p.allocation_for = 'FUND'
3271: AND p.allocation_for_id = l_fund_id
3272: AND p.item_id = -9999;
3273:

Line 3279: FROM OZF_PRODUCT_ALLOCATIONS p

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'
3281: AND p.allocation_for_id = l_fund_id
3282: AND p.item_id = -9999 );
3283:

Line 3300: FROM OZF_PRODUCT_ALLOCATIONS p

3296: SELECT (a.target-b.target) INTO l_diff_target
3297: FROM
3298: (
3299: SELECT p.TARGET target
3300: FROM OZF_PRODUCT_ALLOCATIONS p
3301: WHERE p.allocation_for = 'FUND'
3302: AND p.allocation_for_id = l_fund_id
3303: AND p.item_id = -9999
3304: ) a,

Line 3310: FROM OZF_PRODUCT_ALLOCATIONS p

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'
3312: AND p.allocation_for_id = l_fund_id
3313: AND p.item_id = -9999 )
3314: ) b;

Line 3323: FROM OZF_PRODUCT_ALLOCATIONS p

3319: BEGIN
3320: SELECT
3321: (
3322: SELECT p.TARGET
3323: FROM OZF_PRODUCT_ALLOCATIONS p
3324: WHERE p.allocation_for = 'FUND'
3325: AND p.allocation_for_id = l_fund_id
3326: AND p.item_id = -9999
3327: )

Line 3334: FROM OZF_PRODUCT_ALLOCATIONS p

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'
3336: AND p.allocation_for_id = l_fund_id
3337: AND p.item_id = -9999 )
3338: ) diff_target INTO l_diff_target

Line 3359: FROM OZF_PRODUCT_ALLOCATIONS p

3355:
3356: BEGIN
3357:
3358: SELECT SUM(p.TARGET) INTO l_diff_target_1
3359: FROM OZF_PRODUCT_ALLOCATIONS p
3360: WHERE p.allocation_for = 'FUND'
3361: AND p.allocation_for_id = l_fund_id;
3362:
3363: l_diff_target := (NVL(l_total_root_quota, 0) - NVL(l_diff_target_1, 0));

Line 3402: FROM OZF_PRODUCT_ALLOCATIONS p

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'
3404: AND p.allocation_for_id = l_fund_id
3405: AND p.item_id = -9999 )
3406: );

Line 3569: UPDATE ozf_product_allocations p

3565:
3566: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Publishing Product Allocations Records'
3567: || 'FOR Fact_id = '|| l_fact_id || ' with NEW Fund_id = '||l_fund_id||' ; ');
3568:
3569: UPDATE ozf_product_allocations p
3570: SET p.fund_id = l_fund_id,
3571: p.object_version_number = p.object_version_number + 1,
3572: p.last_update_date = SYSDATE,
3573: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 3656: ozf_product_allocations p

3652: (l_fact_id number) IS
3653: SELECT DISTINCT
3654: p.product_allocation_id
3655: FROM
3656: ozf_product_allocations p
3657: WHERE
3658: p.allocation_for = 'FACT'
3659: AND p.allocation_for_id = l_fact_id;
3660:

Line 3700: DELETE ozf_product_allocations p

3696: WHERE t.allocation_for_id = fact_product_spread_rec.product_allocation_id
3697: AND t.allocation_for = 'PROD';
3698: END LOOP;
3699:
3700: DELETE ozf_product_allocations p
3701: WHERE p.allocation_for = 'FACT'
3702: AND p.allocation_for_id = l_fact_id;
3703:
3704:

Line 3829: ozf_product_allocations p

3825: (l_fund_id number) IS
3826: SELECT DISTINCT
3827: p.product_allocation_id
3828: FROM
3829: ozf_product_allocations p
3830: WHERE
3831: p.allocation_for = 'FUND'
3832: AND p.allocation_for_id = l_fund_id
3833: AND p.fund_id = l_fund_id;

Line 3913: DELETE ozf_product_allocations p

3909: WHERE t.allocation_for_id = fund_product_spread_rec.product_allocation_id
3910: AND t.allocation_for = 'PROD';
3911: END LOOP;
3912:
3913: DELETE ozf_product_allocations p
3914: WHERE p.allocation_for = 'FUND'
3915: AND p.allocation_for_id = l_fund_id
3916: AND p.fund_id = l_fund_id;
3917:

Line 4003: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

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

Line 4076: FROM OZF_PRODUCT_ALLOCATIONS

4072: item_id,
4073: item_type,
4074: target,
4075: lysp_sales
4076: FROM OZF_PRODUCT_ALLOCATIONS
4077: WHERE allocation_for = 'FACT'
4078: AND allocation_for_id = l_fact_id
4079: AND item_id = -9999;
4080:

Line 4228: Ozf_Product_Allocations_Pkg.Insert_Row(

4224: p_prod_alloc_rec.target := 0;
4225: p_prod_alloc_rec.lysp_sales := 0;
4226:
4227:
4228: Ozf_Product_Allocations_Pkg.Insert_Row(
4229: px_product_allocation_id => l_product_allocation_id,
4230: p_allocation_for => p_prod_alloc_rec.allocation_for,
4231: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
4232: p_fund_id => p_prod_alloc_rec.fund_id,

Line 4338: UPDATE OZF_PRODUCT_ALLOCATIONS p

4334:
4335:
4336: END LOOP;
4337:
4338: UPDATE OZF_PRODUCT_ALLOCATIONS p
4339: SET p.lysp_sales = l_total_lysp_sales,
4340: p.object_version_number = p.object_version_number + 1,
4341: p.last_update_date = SYSDATE,
4342: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 4440: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

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

Line 4514: FROM OZF_PRODUCT_ALLOCATIONS

4510: item_id,
4511: item_type,
4512: target,
4513: lysp_sales
4514: FROM OZF_PRODUCT_ALLOCATIONS
4515: WHERE allocation_for = 'FACT'
4516: AND allocation_for_id = l_fact_id
4517: AND item_id = l_item_id
4518: AND item_type = l_item_type;

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

4679: AND t.allocation_for = 'PROD';
4680:
4681: END LOOP;
4682:
4683: UPDATE ozf_product_allocations p -- Update Others Quota for Q3-03 etc
4684: SET p.target = p.target + NVL(l_prod_alloc_rec.target, 0),
4685: p.object_version_number = p.object_version_number + 1,
4686: p.last_update_date = SYSDATE,
4687: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 4700: DELETE ozf_product_allocations p

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
4701: WHERE p.product_allocation_id = l_prod_alloc_rec.product_allocation_id;
4702:
4703:
4704: <>

Line 4790: ozf_product_allocations p

4786: (l_acct_allocation_id number) IS
4787: SELECT DISTINCT
4788: p.product_allocation_id
4789: FROM
4790: ozf_product_allocations p
4791: WHERE
4792: p.allocation_for = 'CUST'
4793: AND p.allocation_for_id = l_acct_allocation_id;
4794:

Line 4841: DELETE ozf_product_allocations p

4837: WHERE t.allocation_for_id = product_rec.product_allocation_id
4838: AND t.allocation_for = 'PROD';
4839: END LOOP;
4840:
4841: DELETE ozf_product_allocations p
4842: WHERE p.allocation_for = 'CUST'
4843: AND p.allocation_for_id = account_rec.account_allocation_id;
4844:
4845: END LOOP;

Line 4962: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

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

Line 5116: ozf_product_allocations p

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

Line 5128: ' ozf_product_allocations p'||

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

Line 5182: FROM ozf_product_allocations p

5178: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
5179: AND DENORM.PARENT_ID = l_category_id
5180: MINUS
5181: SELECT p.item_id
5182: FROM ozf_product_allocations p
5183: WHERE p.fund_id = l_fund_id
5184: AND p.item_type = 'PRICING_ATTRIBUTE1'
5185: );
5186:

Line 5206: FROM ozf_product_allocations p

5202: AND bsmv.time_id = l_time_id
5203: AND NOT EXISTS
5204: (
5205: ( SELECT p.item_id
5206: FROM ozf_product_allocations p
5207: WHERE
5208: p.fund_id = l_fund_id
5209: AND p.item_type = 'PRICING_ATTRIBUTE1'
5210: AND p.item_id = bsmv.inventory_item_id

Line 5215: OZF_PRODUCT_ALLOCATIONS p

5211: UNION ALL
5212: SELECT MIC.INVENTORY_ITEM_ID
5213: FROM MTL_ITEM_CATEGORIES MIC,
5214: ENI_PROD_DENORM_HRCHY_V DENORM,
5215: OZF_PRODUCT_ALLOCATIONS p
5216: WHERE p.FUND_ID = l_fund_id
5217: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
5218: AND p.ITEM_ID = DENORM.PARENT_ID
5219: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 5242: ozf_product_allocations p

5238: p.item_id,
5239: p.item_type,
5240: p.target
5241: FROM
5242: ozf_product_allocations p
5243: WHERE
5244: p.fund_id = l_fund_id;
5245:
5246: l_fund_product_rec fund_product_spread_csr%rowtype;

Line 5588: Ozf_Product_Allocations_Pkg.Insert_Row(

5584: p_prod_alloc_rec.lysp_sales := 0;
5585:
5586: l_product_allocation_id := get_product_allocation_id;
5587:
5588: Ozf_Product_Allocations_Pkg.Insert_Row(
5589: px_product_allocation_id => l_product_allocation_id,
5590: p_allocation_for => p_prod_alloc_rec.allocation_for,
5591: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
5592: p_fund_id => p_prod_alloc_rec.fund_id,

Line 5721: UPDATE OZF_PRODUCT_ALLOCATIONS p

5717:
5718: END IF;
5719: END LOOP account_product_time_loop;
5720:
5721: UPDATE OZF_PRODUCT_ALLOCATIONS p
5722: SET p.lysp_sales = l_total_product_sales,
5723: p.object_version_number = p.object_version_number + 1,
5724: p.last_update_date = SYSDATE,
5725: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 6071: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

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

Line 6226: ozf_product_allocations p

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

Line 6238: ' ozf_product_allocations p'||

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

Line 6292: FROM ozf_product_allocations p

6288: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
6289: AND DENORM.PARENT_ID = l_category_id
6290: MINUS
6291: SELECT p.item_id
6292: FROM ozf_product_allocations p
6293: WHERE p.fund_id = l_fund_id
6294: AND p.item_type = 'PRICING_ATTRIBUTE1'
6295: );
6296:

Line 6316: FROM ozf_product_allocations p

6312: AND bsmv.time_id = l_time_id
6313: AND NOT EXISTS
6314: (
6315: ( SELECT p.item_id
6316: FROM ozf_product_allocations p
6317: WHERE
6318: p.fund_id = l_fund_id
6319: AND p.item_type = 'PRICING_ATTRIBUTE1'
6320: AND p.item_id = bsmv.inventory_item_id

Line 6325: OZF_PRODUCT_ALLOCATIONS p

6321: UNION ALL
6322: SELECT MIC.INVENTORY_ITEM_ID
6323: FROM MTL_ITEM_CATEGORIES MIC,
6324: ENI_PROD_DENORM_HRCHY_V DENORM,
6325: OZF_PRODUCT_ALLOCATIONS p
6326: WHERE p.FUND_ID = l_fund_id
6327: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
6328: AND p.ITEM_ID = DENORM.PARENT_ID
6329: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 6352: ozf_product_allocations p

6348: p.item_id,
6349: p.item_type,
6350: p.target
6351: FROM
6352: ozf_product_allocations p
6353: WHERE
6354: p.fund_id = l_fund_id;
6355:
6356: l_fund_product_rec fund_product_spread_csr%rowtype;

Line 6740: Ozf_Product_Allocations_Pkg.Insert_Row(

6736: p_prod_alloc_rec.lysp_sales := 0;
6737:
6738: l_product_allocation_id := get_product_allocation_id;
6739:
6740: Ozf_Product_Allocations_Pkg.Insert_Row(
6741: px_product_allocation_id => l_product_allocation_id,
6742: p_allocation_for => p_prod_alloc_rec.allocation_for,
6743: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
6744: p_fund_id => p_prod_alloc_rec.fund_id,

Line 6876: UPDATE OZF_PRODUCT_ALLOCATIONS p

6872:
6873: END IF;
6874: END LOOP account_product_time_loop;
6875:
6876: UPDATE OZF_PRODUCT_ALLOCATIONS p
6877: SET p.lysp_sales = l_total_product_sales,
6878: p.object_version_number = p.object_version_number + 1,
6879: p.last_update_date = SYSDATE,
6880: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 6911: FROM OZF_PRODUCT_ALLOCATIONS p

6907: t.last_updated_by = FND_GLOBAL.USER_ID,
6908: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
6909: WHERE t.allocation_for = 'PROD'
6910: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
6911: FROM OZF_PRODUCT_ALLOCATIONS p
6912: WHERE p.allocation_for = 'CUST'
6913: AND p.allocation_for_id = l_account_allocation_id);
6914:
6915: IF (SQL%NOTFOUND) THEN

Line 6924: UPDATE OZF_PRODUCT_ALLOCATIONS p

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

Line 6956: UPDATE OZF_PRODUCT_ALLOCATIONS p

6952:
6953: l_p_denominator := l_period_tbl.COUNT;
6954:
6955: /*
6956: UPDATE OZF_PRODUCT_ALLOCATIONS p
6957: SET p.TARGET = ROUND(NVL(l_total_account_target, 0),0),
6958: p.object_version_number = p.object_version_number + 1,
6959: p.last_update_date = SYSDATE,
6960: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 6984: FROM OZF_PRODUCT_ALLOCATIONS p

6980: t.last_updated_by = FND_GLOBAL.USER_ID,
6981: t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
6982: WHERE t.allocation_for = 'PROD'
6983: AND t.allocation_for_id IN ( SELECT p.product_allocation_id
6984: FROM OZF_PRODUCT_ALLOCATIONS p
6985: WHERE p.allocation_for = 'CUST'
6986: AND p.allocation_for_id = l_account_allocation_id
6987: AND p.item_id = -9999 );
6988:

Line 6997: UPDATE OZF_PRODUCT_ALLOCATIONS p

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

Line 7033: FROM OZF_PRODUCT_ALLOCATIONS p

7029: BEGIN
7030:
7031:
7032: SELECT SUM(p.TARGET) INTO l_diff_target_1
7033: FROM OZF_PRODUCT_ALLOCATIONS p
7034: WHERE p.allocation_for = 'CUST'
7035: AND p.allocation_for_id = l_account_allocation_id;
7036:
7037: l_diff_target := (NVL(l_total_account_target, 0) - NVL(l_diff_target_1, 0));

Line 7070: FROM OZF_PRODUCT_ALLOCATIONS p

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'
7072: AND p.allocation_for_id = l_account_allocation_id
7073: AND p.target =
7074: (SELECT max(xz.target)

Line 7075: FROM OZF_PRODUCT_ALLOCATIONS xz

7071: WHERE p.allocation_for = 'CUST'
7072: AND p.allocation_for_id = l_account_allocation_id
7073: AND p.target =
7074: (SELECT max(xz.target)
7075: FROM OZF_PRODUCT_ALLOCATIONS xz
7076: WHERE xz.allocation_for = 'CUST'
7077: AND xz.allocation_for_id = l_account_allocation_id
7078: )
7079:

Line 7086: FROM OZF_PRODUCT_ALLOCATIONS pz

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'
7088: AND pz.allocation_for_id = l_account_allocation_id
7089: AND pz.target =
7090: (SELECT max(xz.target)

Line 7091: FROM OZF_PRODUCT_ALLOCATIONS xz

7087: WHERE pz.allocation_for = 'CUST'
7088: AND pz.allocation_for_id = l_account_allocation_id
7089: AND pz.target =
7090: (SELECT max(xz.target)
7091: FROM OZF_PRODUCT_ALLOCATIONS xz
7092: WHERE xz.allocation_for = 'CUST'
7093: AND xz.allocation_for_id = l_account_allocation_id
7094: )
7095:

Line 7110: UPDATE OZF_PRODUCT_ALLOCATIONS p

7106: END IF;
7107: RAISE fnd_api.g_exc_unexpected_error;
7108: END IF;
7109:
7110: UPDATE OZF_PRODUCT_ALLOCATIONS p
7111: SET p.TARGET = p.TARGET + l_diff_target,
7112: p.object_version_number = p.object_version_number + 1,
7113: p.last_update_date = SYSDATE,
7114: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 7138: FROM OZF_PRODUCT_ALLOCATIONS p

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'
7140: AND p.allocation_for_id = l_account_allocation_id
7141: AND p.item_id = -9999 )
7142: );

Line 7153: UPDATE OZF_PRODUCT_ALLOCATIONS p

7149: END IF;
7150: RAISE fnd_api.g_exc_unexpected_error;
7151: END IF;
7152:
7153: UPDATE OZF_PRODUCT_ALLOCATIONS p
7154: SET p.TARGET = p.TARGET + l_diff_target,
7155: p.object_version_number = p.object_version_number + 1,
7156: p.last_update_date = SYSDATE,
7157: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 7546: FROM OZF_PRODUCT_ALLOCATIONS p

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'
7548: AND p.allocation_for_id = l_temp_account_allocation_id
7549: AND p.target =
7550: (SELECT max(xz.target)

Line 7551: FROM OZF_PRODUCT_ALLOCATIONS xz

7547: WHERE p.allocation_for = 'CUST'
7548: AND p.allocation_for_id = l_temp_account_allocation_id
7549: AND p.target =
7550: (SELECT max(xz.target)
7551: FROM OZF_PRODUCT_ALLOCATIONS xz
7552: WHERE xz.allocation_for = 'CUST'
7553: AND xz.allocation_for_id = l_temp_account_allocation_id
7554: )
7555:

Line 7562: FROM OZF_PRODUCT_ALLOCATIONS pz

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'
7564: AND pz.allocation_for_id = l_temp_account_allocation_id
7565: AND pz.target =
7566: (SELECT max(xz.target)

Line 7567: FROM OZF_PRODUCT_ALLOCATIONS xz

7563: WHERE pz.allocation_for = 'CUST'
7564: AND pz.allocation_for_id = l_temp_account_allocation_id
7565: AND pz.target =
7566: (SELECT max(xz.target)
7567: FROM OZF_PRODUCT_ALLOCATIONS xz
7568: WHERE xz.allocation_for = 'CUST'
7569: AND xz.allocation_for_id = l_temp_account_allocation_id
7570: )
7571:

Line 7586: UPDATE OZF_PRODUCT_ALLOCATIONS p

7582: END IF;
7583: RAISE fnd_api.g_exc_unexpected_error;
7584: END IF;
7585:
7586: UPDATE OZF_PRODUCT_ALLOCATIONS p
7587: SET p.TARGET = p.TARGET + l_diff_target,
7588: p.object_version_number = p.object_version_number + 1,
7589: p.last_update_date = SYSDATE,
7590: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 7748: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

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

Line 7854: ozf_product_allocations p

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

Line 7866: ' ozf_product_allocations p'||

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

Line 7916: FROM ozf_product_allocations p

7912: FROM mtl_item_categories mtl
7913: WHERE mtl.category_id = l_category_id
7914: MINUS
7915: SELECT p.item_id
7916: FROM ozf_product_allocations p
7917: WHERE p.fund_id = l_fund_id
7918: AND p.item_type = 'PRICING_ATTRIBUTE1'
7919: );
7920:

Line 7940: FROM ozf_product_allocations p

7936: AND bsmv.time_id = l_time_id
7937: AND NOT EXISTS
7938: (
7939: ( SELECT p.item_id
7940: FROM ozf_product_allocations p
7941: WHERE
7942: p.fund_id = l_fund_id
7943: AND p.item_type = 'PRICING_ATTRIBUTE1'
7944: AND p.item_id = bsmv.inventory_item_id

Line 7947: FROM ozf_product_allocations p,

7943: AND p.item_type = 'PRICING_ATTRIBUTE1'
7944: AND p.item_id = bsmv.inventory_item_id
7945: UNION ALL
7946: SELECT mtl.inventory_item_id
7947: FROM ozf_product_allocations p,
7948: mtl_item_categories mtl
7949: WHERE
7950: p.fund_id = l_fund_id
7951: AND p.item_type = 'PRICING_ATTRIBUTE2'

Line 7974: ozf_product_allocations p

7970: p.item_id,
7971: p.item_type,
7972: p.target
7973: FROM
7974: ozf_product_allocations p
7975: WHERE
7976: p.allocation_for = 'CUST'
7977: AND p.allocation_for_id = l_account_allocation_id;
7978:

Line 8001: ozf_product_allocations p

7997: SELECT
7998: p.product_allocation_id,
7999: p.target
8000: FROM
8001: ozf_product_allocations p
8002: WHERE
8003: p.fund_id = l_fund_id
8004: AND p.allocation_for = 'FACT'
8005: AND p.allocation_for_id = l_addon_fact_id

Line 8429: UPDATE OZF_PRODUCT_ALLOCATIONS p

8425:
8426: END IF;
8427: END LOOP account_product_time_loop;
8428:
8429: UPDATE OZF_PRODUCT_ALLOCATIONS p
8430: SET p.lysp_sales = p.lysp_sales + l_total_product_sales,
8431: p.target = p.target + l_total_product_target,
8432: p.object_version_number = p.object_version_number + 1,
8433: p.last_update_date = SYSDATE,

Line 8597: from ozf_product_allocations

8593: l_count number;
8594: CURSOR prod_alloc_count_csr (l_fund_id NUMBER)
8595: IS
8596: select count(product_allocation_id)
8597: from ozf_product_allocations
8598: where fund_id = l_fund_id;
8599:
8600: BEGIN
8601:

Line 9349: p_prod_alloc_rec ozf_product_allocations%ROWTYPE;

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

Line 9418: FROM ozf_product_allocations p

9414: AND MIC.CATEGORY_ID = DENORM.CHILD_ID
9415: AND DENORM.PARENT_ID = l_category_id
9416: MINUS
9417: SELECT p.item_id
9418: FROM ozf_product_allocations p
9419: WHERE p.fund_id = l_fund_id
9420: AND p.item_type = 'PRICING_ATTRIBUTE1'
9421: );
9422:

Line 9442: FROM ozf_product_allocations p

9438: AND bsmv.time_id = l_time_id
9439: AND NOT EXISTS
9440: (
9441: ( SELECT p.item_id
9442: FROM ozf_product_allocations p
9443: WHERE
9444: p.fund_id = l_fund_id
9445: AND p.item_type = 'PRICING_ATTRIBUTE1'
9446: AND p.item_id = bsmv.inventory_item_id

Line 9451: OZF_PRODUCT_ALLOCATIONS p

9447: UNION ALL
9448: SELECT MIC.INVENTORY_ITEM_ID
9449: FROM MTL_ITEM_CATEGORIES MIC,
9450: ENI_PROD_DENORM_HRCHY_V DENORM,
9451: OZF_PRODUCT_ALLOCATIONS p
9452: WHERE p.FUND_ID = l_fund_id
9453: AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
9454: AND p.ITEM_ID = DENORM.PARENT_ID
9455: AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID

Line 9478: ozf_product_allocations p

9474: p.item_id,
9475: p.item_type,
9476: p.target
9477: FROM
9478: ozf_product_allocations p
9479: WHERE
9480: p.fund_id = l_fund_id;
9481:
9482: l_fund_product_rec fund_product_spread_csr%rowtype;

Line 9496: ozf_product_allocations p

9492: CURSOR corr_prod_alloc_rec
9493: (l_acct_alloc_id number) IS
9494: SELECT *
9495: FROM
9496: ozf_product_allocations p
9497: WHERE
9498: p.allocation_for = 'CUST'
9499: AND p.allocation_for_id = l_acct_alloc_id; -- p_acct_alloc_rec.account_allocation_id;
9500:

Line 9910: Ozf_Product_Allocations_Pkg.Insert_Row(

9906: LOOP
9907:
9908: l_product_allocation_id := get_product_allocation_id;
9909:
9910: Ozf_Product_Allocations_Pkg.Insert_Row(
9911: px_product_allocation_id => l_product_allocation_id,
9912: p_allocation_for => p_prod_alloc_rec.allocation_for,
9913: p_allocation_for_id => l_account_allocation_id,
9914: p_fund_id => p_fund_id,

Line 10012: UPDATE OZF_PRODUCT_ALLOCATIONS p

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

Line 10047: Ozf_Product_Allocations_Pkg.Insert_Row(

10043: p_prod_alloc_rec.lysp_sales := 0;
10044:
10045: l_product_allocation_id := get_product_allocation_id;
10046:
10047: Ozf_Product_Allocations_Pkg.Insert_Row(
10048: px_product_allocation_id => l_product_allocation_id,
10049: p_allocation_for => p_prod_alloc_rec.allocation_for,
10050: p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
10051: p_fund_id => p_fund_id,

Line 10175: UPDATE OZF_PRODUCT_ALLOCATIONS p

10171:
10172:
10173: -- 15. UPDATING SHIPTOs Product Spread - Header Record ....
10174:
10175: UPDATE OZF_PRODUCT_ALLOCATIONS p
10176: SET p.lysp_sales = l_total_product_sales,
10177: p.object_version_number = p.object_version_number + 1,
10178: p.last_update_date = SYSDATE,
10179: p.last_updated_by = FND_GLOBAL.USER_ID,

Line 10431: FROM ozf_product_allocations pp

10427: tt.account_status = 'D'
10428: WHERE tt.allocation_for = 'PROD'
10429: AND tt.allocation_for_id IN (
10430: SELECT pp.product_allocation_id
10431: FROM ozf_product_allocations pp
10432: WHERE pp.allocation_for = 'CUST'
10433: AND pp.allocation_for_id = l_account_allocation_id
10434: )
10435: and EXISTS

Line 10456: UPDATE OZF_PRODUCT_ALLOCATIONS p

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

Line 10468: FROM ozf_product_allocations pp

10464: p.last_updated_by = FND_GLOBAL.USER_ID,
10465: p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
10466: WHERE p.product_allocation_id IN (
10467: SELECT pp.product_allocation_id
10468: FROM ozf_product_allocations pp
10469: WHERE pp.allocation_for = 'CUST'
10470: AND pp.allocation_for_id = l_account_allocation_id
10471: );
10472: END;