[Home] [Help]
132: -- Initialize API return status to SUCCESS
133: x_return_status := FND_API.G_RET_STS_SUCCESS;
134:
135:
136: -- Standard call to get message count and if count is 1, get message info.
137: FND_MSG_PUB.Count_And_Get
138: ( p_count => x_msg_count,
139: p_data => x_msg_data
140: );
272: from csp_planning_parameters cpp
273: Where cpp.level_id like p_Level_Id || '%' And cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY'));
274: End If;
275: commit;
276: -- Standard Start of API savepoint
277: SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
278:
279: if p_api_version_number <> 2.0 then
280: -- Standard call to check for call compatibility.
276: -- Standard Start of API savepoint
277: SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
278:
279: if p_api_version_number <> 2.0 then
280: -- Standard call to check for call compatibility.
281: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
282: p_api_version_number,
283: l_api_name,
284: G_PKG_NAME) THEN
403: RAW_AWU,
404: AWU,
405: ITEM_COST,
406: LEAD_TIME,
407: STANDARD_DEVIATION,
408: PROCESS_STATUS,
409: EXTERNAL_DATA,
410: CREATION_DATE,
411: CREATED_BY,
460: RAW_AWU,
461: AWU,
462: ITEM_COST,
463: LEAD_TIME,
464: STANDARD_DEVIATION,
465: PROCESS_STATUS,
466: EXTERNAL_DATA,
467: CREATION_DATE,
468: CREATED_BY,
516: RAW_AWU,
517: AWU,
518: ITEM_COST,
519: LEAD_TIME,
520: STANDARD_DEVIATION,
521: PROCESS_STATUS,
522: EXTERNAL_DATA,
523: PLANNING_PARAMETERS_ID,
524: CREATION_DATE,
553: decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
554: decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
555: nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0), nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)),
556: nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))),
557: sum(nvl(cuh.standard_deviation,0)),
558: 'O',
559: 'N',
560: cpp.planning_parameters_id,
561: sysdate,
648: sq.MAX_QUANTITY
649: FROM (Select cuh.Inventory_Item_Id,
650: cuh.Organization_Id,
651: cuh.Secondary_Inventory,
652: ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time + DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0)) Min_quantity,
653: ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
654: DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
655: DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
656: DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
650: cuh.Organization_Id,
651: cuh.Secondary_Inventory,
652: ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time + DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0)) Min_quantity,
653: ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
654: DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
655: DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
656: DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
657: from CSP_USAGE_HEADERS cuh,
658: CSP_PLANNING_PARAMETERS cpp,
686: sq.MAX_QUANTITY
687: FROM (Select cuh.inventory_item_id,
688: cuh.organization_id,
689: ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
690: DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0)) min_quantity,
691: ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
692: DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
693: DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
694: DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
688: cuh.organization_id,
689: ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
690: DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0)) min_quantity,
691: ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
692: DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
693: DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
694: DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
695: from CSP_USAGE_HEADERS cuh,
696: CSP_PLANNING_PARAMETERS cpp,
837: --
838: -- End of API body
839: --
840:
841: -- Standard check for p_commit
842: IF FND_API.to_Boolean( l_commit )
843: THEN
844: COMMIT WORK;
845: END IF;
846:
847: EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW CSP_USAGE_ORG_MV COMPILE';
848: EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW CSP_USAGE_REG_MV COMPILE';
849:
850: -- Standard call to get message count and if count is 1, get message info.
851: FND_MSG_PUB.Count_And_Get
852: ( p_count => x_msg_count,
853: p_data => x_msg_data
854: );
930: l_status varchar2(1);
931: l_industry varchar2(1);
932: l_oracle_schema varchar2(30);
933: BEGIN
934: -- Standard Start of API savepoint
935: SAVEPOINT PURGE_DATA_PVT;
936:
937: -- Standard call to check for call compatibility.
938: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
933: BEGIN
934: -- Standard Start of API savepoint
935: SAVEPOINT PURGE_DATA_PVT;
936:
937: -- Standard call to check for call compatibility.
938: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
939: p_api_version_number,
940: l_api_name,
941: G_PKG_NAME)
980: --
981: -- End of API body
982: --
983:
984: -- Standard check for p_commit
985: IF FND_API.to_Boolean( p_commit )
986: THEN
987: COMMIT WORK;
988: END IF;
988: END IF;
989:
990:
991:
992: -- Standard call to get message count and if count is 1, get message info.
993: FND_MSG_PUB.Count_And_Get
994: ( p_count => x_msg_count,
995: p_data => x_msg_data
996: );
1064: l_oracle_schema varchar2(30);
1065: BEGIN
1066: -- Alter session
1067: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
1068: -- Standard call to check for call compatibility.
1069: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1070: p_api_version_number,
1071: l_api_name,
1072: G_PKG_NAME)
1109: If G_LAST_RUN_DATE < trunc(sysdate) - 1 Then
1110: l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
1111: -- Delete from CSP_SUPPLY_CHAIN
1112: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
1113: -- Standard Start of API savepoint
1114: SAVEPOINT CREATE_USAGE_PVT;
1115: -- Build Supply Chain
1116: Create_Supply_Chain (
1117: P_Api_Version_Number => 1.0,
1160:
1161: --
1162: -- End of API body
1163: --
1164: -- Standard call to get message count and if count is 1, get message info.
1165: FND_MSG_PUB.Count_And_Get
1166: ( p_count => x_msg_count,
1167: p_data => x_msg_data
1168: );
1399: l_Usage_Qty_Sum NUMBER := 0;
1400: l_weeks NUMBER := 0;
1401: l_Awu NUMBER := 0;
1402: l_Variance NUMBER := 0;
1403: l_Standard_Deviation NUMBER := 0;
1404:
1405: l_Usage_Id Number;
1406: l_index Number := 0;
1407: l_count Number := 0;
1410: l_Msg_Count NUMBER;
1411: l_Msg_Data Varchar2(2000);
1412:
1413: BEGIN
1414: -- Standard Start of API savepoint
1415: SAVEPOINT CALCULATE_FORECAST_PVT;
1416:
1417: -- Standard call to check for call compatibility.
1418: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1413: BEGIN
1414: -- Standard Start of API savepoint
1415: SAVEPOINT CALCULATE_FORECAST_PVT;
1416:
1417: -- Standard call to check for call compatibility.
1418: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1419: p_api_version_number,
1420: l_api_name,
1421: G_PKG_NAME)
1538: l_Usage_Data_Count := l_Usage_Qty_Tbl.COUNT;
1539: l_weeks := (l_Prev_Rec.Period_Size * l_Prev_Rec.Forecast_Periods)/7;
1540: l_Awu := ROUND((l_Forecast_Quantity/l_weeks),4);
1541:
1542: -- Calculate Standard Deviation
1543: l_Variance := 0;
1544: If l_Usage_Data_Count > 1 Then
1545: l_Variance :=
1546: (l_Usage_Qty_Sum - ((l_Usage_Quantity * l_Usage_Quantity) /l_Usage_Data_Count))/(l_Usage_Data_Count - 1);
1544: If l_Usage_Data_Count > 1 Then
1545: l_Variance :=
1546: (l_Usage_Qty_Sum - ((l_Usage_Quantity * l_Usage_Quantity) /l_Usage_Data_Count))/(l_Usage_Data_Count - 1);
1547: End If;
1548: l_Standard_Deviation := ROUND(SQRT(NVL(l_Variance,0)),4);
1549: INSERT INTO CSP_USAGE_HEADERS
1550: (USAGE_HEADER_ID,
1551: INVENTORY_ITEM_ID,
1552: ORGANIZATION_ID,
1553: SECONDARY_INVENTORY,
1554: HEADER_DATA_TYPE,
1555: RAW_AWU,
1556: AWU,
1557: STANDARD_DEVIATION,
1558: LEAD_TIME,
1559: PROCESS_STATUS,
1560: EXTERNAL_DATA,
1561: COMMENTS,
1571: l_prev_rec.Subinventory_code,
1572: l_prev_rec.History_Data_Type,
1573: NULL,
1574: l_Awu,
1575: l_Standard_Deviation,
1576: NULL,
1577: 'O',
1578: 'N',
1579: NULL,
1592: l_Usage_Qty_Sum := 0;
1593: l_weeks := 0;
1594: l_Awu := 0;
1595: l_Variance := 0;
1596: l_Standard_Deviation := 0;
1597: l_Usage_Qty_Tbl.Delete;
1598: l_Weighted_Avg_Tbl.Delete;
1599: END IF;
1600: l_Usage_Qty_Tbl(l_count) := l_Usage_Details_Rec.Quantity;
1612: --
1613: -- End of API body
1614: --
1615:
1616: -- Standard check for p_commit
1617: IF FND_API.to_Boolean( p_commit )
1618: THEN
1619: COMMIT WORK;
1620: END IF;
1619: COMMIT WORK;
1620: END IF;
1621:
1622:
1623: -- Standard call to get message count and if count is 1, get message info.
1624: FND_MSG_PUB.Count_And_Get
1625: ( p_count => x_msg_count,
1626: p_data => x_msg_data
1627: );
1686: l_Msg_Count NUMBER;
1687: l_Msg_Data Varchar2(2000);
1688:
1689: BEGIN
1690: -- Standard Start of API savepoint
1691: SAVEPOINT CALCULATE_PNORM_PVT;
1692:
1693: -- Standard call to check for call compatibility.
1694: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1689: BEGIN
1690: -- Standard Start of API savepoint
1691: SAVEPOINT CALCULATE_PNORM_PVT;
1692:
1693: -- Standard call to check for call compatibility.
1694: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1695: p_api_version_number,
1696: l_api_name,
1697: G_PKG_NAME)
1744: and csi.organization_id (+) = cppf.organization_id
1745: and csi.sub_inventory_code (+) = cppf.secondary_inventory;
1746:
1747:
1748: -- Standard check for p_commit
1749: IF FND_API.to_Boolean( p_commit )
1750: THEN
1751: COMMIT WORK;
1752: END IF;
1751: COMMIT WORK;
1752: END IF;
1753:
1754:
1755: -- Standard call to get message count and if count is 1, get message info.
1756: FND_MSG_PUB.Count_And_Get
1757: ( p_count => x_msg_count,
1758: p_data => x_msg_data
1759: );
1818: l_Msg_Count NUMBER;
1819: l_Msg_Data Varchar2(2000);
1820:
1821: BEGIN
1822: -- Standard Start of API savepoint
1823: SAVEPOINT CALCULATE_NPROD_PVT;
1824:
1825: -- Standard call to check for call compatibility.
1826: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1821: BEGIN
1822: -- Standard Start of API savepoint
1823: SAVEPOINT CALCULATE_NPROD_PVT;
1824:
1825: -- Standard call to check for call compatibility.
1826: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1827: p_api_version_number,
1828: l_api_name,
1829: G_PKG_NAME)
1875: and csi.inventory_item_id(+) = cnpp.inventory_item_id
1876: and csi.organization_id (+) = cnpp.organization_id
1877: and csi.sub_inventory_code (+) = cnpp.secondary_inventory;
1878:
1879: -- Standard check for p_commit
1880: IF FND_API.to_Boolean( p_commit )
1881: THEN
1882: COMMIT WORK;
1883: END IF;
1882: COMMIT WORK;
1883: END IF;
1884:
1885:
1886: -- Standard call to get message count and if count is 1, get message info.
1887: FND_MSG_PUB.Count_And_Get
1888: ( p_count => x_msg_count,
1889: p_data => x_msg_data
1890: );
1949: l_Msg_Count NUMBER;
1950: l_Msg_Data Varchar2(2000);
1951:
1952: BEGIN
1953: -- Standard Start of API savepoint
1954: SAVEPOINT CALCULATE_TNORM_PVT;
1955:
1956: -- Standard call to check for call compatibility.
1957: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1952: BEGIN
1953: -- Standard Start of API savepoint
1954: SAVEPOINT CALCULATE_TNORM_PVT;
1955:
1956: -- Standard call to check for call compatibility.
1957: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1958: p_api_version_number,
1959: l_api_name,
1960: G_PKG_NAME)
2006: and csi.sub_inventory_code(+) = curos.secondary_inventory
2007: group by nvl(csi.item_supplied,curos.inventory_item_id),
2008: curos.organization_id,curos.secondary_inventory,curos.awu;
2009:
2010: -- Standard check for p_commit
2011: IF FND_API.to_Boolean( p_commit )
2012: THEN
2013: COMMIT WORK;
2014: END IF;
2013: COMMIT WORK;
2014: END IF;
2015:
2016:
2017: -- Standard call to get message count and if count is 1, get message info.
2018: FND_MSG_PUB.Count_And_Get
2019: ( p_count => x_msg_count,
2020: p_data => x_msg_data
2021: );
2099: l_industry varchar2(1);
2100: l_oracle_schema varchar2(30);
2101:
2102: BEGIN
2103: -- Standard Start of API savepoint
2104: SAVEPOINT CREATE_SUPPLY_CHAIN_PVT;
2105:
2106: -- Standard call to check for call compatibility.
2107: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2102: BEGIN
2103: -- Standard Start of API savepoint
2104: SAVEPOINT CREATE_SUPPLY_CHAIN_PVT;
2105:
2106: -- Standard call to check for call compatibility.
2107: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2108: p_api_version_number,
2109: l_api_name,
2110: G_PKG_NAME)
2248: --
2249: -- End of API body
2250: --
2251:
2252: -- Standard check for p_commit
2253: IF FND_API.to_Boolean( p_commit )
2254: THEN
2255: COMMIT WORK;
2256: END IF;
2255: COMMIT WORK;
2256: END IF;
2257:
2258:
2259: -- Standard call to get message count and if count is 1, get message info.
2260: FND_MSG_PUB.Count_And_Get
2261: ( p_count => x_msg_count,
2262: p_data => x_msg_data
2263: );
2334: g_default_flag number := 1;
2335: l_period_size number := 0;
2336:
2337: BEGIN
2338: -- Standard Start of API savepoint
2339: SAVEPOINT CREATE_SUPPLY_CHAIN_PVT;
2340:
2341: -- Standard call to check for call compatibility.
2342: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2337: BEGIN
2338: -- Standard Start of API savepoint
2339: SAVEPOINT CREATE_SUPPLY_CHAIN_PVT;
2340:
2341: -- Standard call to check for call compatibility.
2342: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2343: p_api_version_number,
2344: l_api_name,
2345: G_PKG_NAME)
2504: --
2505: -- End of API body
2506: --
2507:
2508: -- Standard check for p_commit
2509: IF FND_API.to_Boolean( p_commit )
2510: THEN
2511: COMMIT WORK;
2512: END IF;
2511: COMMIT WORK;
2512: END IF;
2513:
2514:
2515: -- Standard call to get message count and if count is 1, get message info.
2516: FND_MSG_PUB.Count_And_Get
2517: ( p_count => x_msg_count,
2518: p_data => x_msg_data
2519: );
2592: l_Msg_Count NUMBER;
2593: l_Msg_Data Varchar2(2000);
2594:
2595: BEGIN
2596: -- Standard Start of API savepoint
2597: SAVEPOINT CREATE_USAGE_HIST_PVT;
2598:
2599:
2600: -- Initialize API return status to SUCCESS
2705: --
2706: -- End of API body
2707: --
2708:
2709: -- Standard check for p_commit
2710: IF FND_API.to_Boolean( p_commit )
2711: THEN
2712: COMMIT WORK;
2713: END IF;
2712: COMMIT WORK;
2713: END IF;
2714:
2715:
2716: -- Standard call to get message count and if count is 1, get message info.
2717: FND_MSG_PUB.Count_And_Get
2718: ( p_count => x_msg_count,
2719: p_data => x_msg_data
2720: );
3005: X_Msg_Count NUMBER;
3006: X_Msg_Data VARCHAR2(2000);
3007:
3008: BEGIN
3009: -- Standard Start of API savepoint
3010: SAVEPOINT CREATE_ROLLUP_PVT;
3011:
3012:
3013: --
3021: --
3022: -- End of API body
3023: --
3024:
3025: -- Standard check for p_commit
3026: IF FND_API.to_Boolean( l_commit )
3027: THEN
3028: COMMIT WORK;
3029: END IF;
3027: THEN
3028: COMMIT WORK;
3029: END IF;
3030:
3031: -- Standard call to get message count and if count is 1, get message info.
3032: FND_MSG_PUB.Count_And_Get
3033: ( p_count => x_msg_count,
3034: p_data => x_msg_data
3035: );
3089: X_Msg_Count OUT NOCOPY NUMBER,
3090: X_Msg_Data OUT NOCOPY VARCHAR2
3091: ) IS
3092: cursor c_usage_headers(p_item_id number,p_org_id number) is
3093: select nvl(cuh.standard_deviation,0) standard_deviation,
3094: cuh.awu ,
3095: cuh.item_cost,
3096: cuh.lead_time,
3097: cpp.edq_factor,
3123: l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3124:
3125:
3126: BEGIN
3127: -- Standard Start of API savepoint
3128: SAVEPOINT CALCULATE_NEEDBY_PVT;
3129:
3130: -- Standard call to check for call compatibility.
3131: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3126: BEGIN
3127: -- Standard Start of API savepoint
3128: SAVEPOINT CALCULATE_NEEDBY_PVT;
3129:
3130: -- Standard call to check for call compatibility.
3131: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3132: p_api_version_number,
3133: l_api_name,
3134: G_PKG_NAME)
3160: p_service_level => l_usage_headers_rec.service_level,
3161: p_item_cost => l_usage_headers_rec.item_cost,
3162: p_awu => l_usage_headers_rec.awu,
3163: p_lead_time => l_usage_headers_rec.lead_time,
3164: p_standard_deviation => l_usage_headers_rec.standard_deviation,
3165: p_safety_stock_flag => 'Y',
3166: p_asl_flag => NULL);
3167: If nvl(l_usage_headers_rec.awu,0) > 0 Then
3168: X_needby_date := trunc(sysdate) +
3176: --
3177: -- End of API body
3178: --
3179:
3180: -- Standard check for p_commit
3181: IF FND_API.to_Boolean( l_commit )
3182: THEN
3183: COMMIT WORK;
3184: END IF;
3182: THEN
3183: COMMIT WORK;
3184: END IF;
3185:
3186: -- Standard call to get message count and if count is 1, get message info.
3187: FND_MSG_PUB.Count_And_Get
3188: ( p_count => x_msg_count,
3189: p_data => x_msg_data
3190: );