[Home] [Help]
1: Package Body CSP_PLAN_DETAILS_PKG AS
2: /*$Header: csptpldb.pls 120.59 2008/06/03 00:01:33 hhaugeru noship $*/
3: l_organization_id number := 0;
4: l_inventory_item_id number := 0;
5: l_forecast_rule_id number := 0;
88: nvl(cpl.newbuy_lead_time,0) newbuy_lead_time,
89: nvl(cpl.repair_lead_time,0) repair_lead_time,
90: nvl(cpl.excess_lead_time,0) excess_lead_time,
91: nvl(cic.item_cost,0) item_cost
92: from csp_plan_details cpd,
93: csp_plan_leadtimes cpl,
94: cst_item_costs cic,
95: mtl_parameters mp,
96: csp_usage_headers cuh
148: sum(cpd.quantity * cic.item_cost),2)),15,' ') ||
149: lpad(to_char(sum(cpd.quantity)),15,' ') ||
150: lpad(to_char(round(avg(
151: nvl(cuh.tracking_signal,0)),2)),11,' ') statistics
152: from csp_plan_details cpd,
153: cst_item_costs cic,
154: mtl_parameters mp,
155: csp_usage_headers cuh
156: where cic.organization_id = cpd.organization_id
432: , x_msg_data => l_msg_data);
433:
434:
435: if l_return_status = FND_API.G_RET_STS_SUCCESS then
436: update csp_plan_details
437: set plan_detail_type = decode(cr.plan_detail_type,'4110','8630',
438: '4210','8620',
439: '4310','8610'),
440: parent_type = l_parent_type
553: sysdate,
554: fnd_global.user_id,
555: sysdate,
556: fnd_global.login_id
557: from csp_plan_Details cpd
558: where plan_detail_type = '1'
559: and organization_id = l_organization_id
560: and inventory_item_id = nvl(l_inventory_item_id, inventory_item_id)
561: group by organization_id, inventory_item_id;
680: floor(cpl.NewBuy_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date - trunc(sysdate)- cpl.newbuy_lead_time), -1, cpd.quantity, 0), 0)))* ((cpl.NewBuy_lead_time - (cfrb.period_size *
681: floor(cpl.NewBuy_Lead_Time/cfrb.period_size)))/cfrb.period_size),4))/ cpl.newbuy_lead_time) * 7 NewBuy_AWR,
682: cpd.inventory_item_id,
683: cpd.organization_id
684: from csp_plan_details cpd,
685: csp_plan_leadtimes cpl,
686: csp_planning_parameters cpp,
687: csp_forecast_rules_b cfrb
688: where cpd.organization_id = p_organization_id
718:
719: procedure return_history is
720:
721: begin
722: insert into csp_plan_details(
723: plan_detail_type,
724: parent_type,
725: inventory_item_id,
726: related_item_id,
745: sysdate,
746: fnd_global.user_id,
747: sysdate,
748: fnd_global.login_id
749: from csp_plan_details cpd,
750: csp_planning_parameters cpp,
751: csp_forecast_rules_b cfrb,
752: mtl_material_transactions mmt,
753: csp_usg_transaction_types cutt
766: cpd.organization_id,
767: cpd.source_organization_id,
768: trunc(trunc(sysdate) - round((trunc(sysdate) - trunc(mmt.transaction_date))/cfrb.period_size)*cfrb.period_size);
769:
770: insert into csp_plan_details(
771: plan_detail_type,
772: parent_type,
773: source_number,
774: source_organization_id,
797: sysdate,
798: fnd_global.login_id,
799: max(a.history_periods),
800: max(a.period_size)
801: from csp_plan_details cpd,
802: (select round(max(cfrb.history_periods*cfrb.period_size)/max(cfrb.period_size)+0.499999) history_periods,max(cfrb.period_size) period_size,cpd.organization_id,cpd.inventory_item_id
803: from csp_forecast_rules_b cfrb,
804: csp_planning_parameters cpp,
805: csp_plan_details cpd
801: from csp_plan_details cpd,
802: (select round(max(cfrb.history_periods*cfrb.period_size)/max(cfrb.period_size)+0.499999) history_periods,max(cfrb.period_size) period_size,cpd.organization_id,cpd.inventory_item_id
803: from csp_forecast_rules_b cfrb,
804: csp_planning_parameters cpp,
805: csp_plan_details cpd
806: where cfrb.forecast_rule_id = cpp.forecast_rule_id
807: and cpd.plan_detail_type in ('6100','6200')
808: and cpd.source_organization_id = cpp.organization_id
809: group by cpd.organization_id, cpd.inventory_item_id) a
814: and a.organization_id = cpd.organization_id
815: group by cpd.plan_date,cpd.inventory_item_id,cpd.organization_id,cpd.source_organization_id;
816:
817: -- Delete 6100 if no 6200 exists, to allow for better display of defective returns
818: delete from csp_plan_details cpd
819: where cpd.plan_detail_type = '6100'
820: and cpd.organization_id = l_organization_id
821: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
822: and not exists
820: and cpd.organization_id = l_organization_id
821: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
822: and not exists
823: (select 'x'
824: from csp_plan_details
825: where plan_detail_type = '6200'
826: and inventory_item_id = cpd.inventory_item_id
827: and organization_id = cpd.organization_id);
828: end return_history;
844: quantity,
845: alpha,
846: beta,
847: nvl(cpl.repair_lead_time,0) repair_lead_time
848: from csp_plan_details cpd,
849: csp_plan_leadtimes cpl,
850: csp_forecast_rules_b cfrb
851: where cpd.organization_id = l_organization_id
852: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
859: plan_date;
860:
861: begin
862:
863: insert into csp_plan_details(
864: plan_detail_type,
865: parent_type,
866: source_number,
867: source_organization_id,
886: sysdate,
887: fnd_global.user_id,
888: sysdate,
889: fnd_global.login_id
890: from csp_plan_details cpd,
891: csp_plan_leadtimes cpl,
892: (select round(sum(cpd2.quantity* l_period_size /cfrb.period_size/cfrb.history_periods)) quantity,
893: cpd2.organization_id organization_id,
894: cpd2.inventory_item_id inventory_item_id
891: csp_plan_leadtimes cpl,
892: (select round(sum(cpd2.quantity* l_period_size /cfrb.period_size/cfrb.history_periods)) quantity,
893: cpd2.organization_id organization_id,
894: cpd2.inventory_item_id inventory_item_id
895: from csp_plan_details cpd2,
896: csp_forecast_rules_b cfrb,
897: csp_planning_parameters cpp
898: where cpd2.plan_detail_type = '6000'
899: and cpp.organization_id = cpd2.source_organization_id
908: and cpl.organization_id = cpd.organization_id
909: and cpl.inventory_item_id = cpd.inventory_item_id
910: and rownum <= l_forecast_periods;
911:
912: insert into csp_plan_details(
913: plan_detail_type,
914: parent_type,
915: source_number,
916: source_organization_id,
935: sysdate,
936: fnd_global.user_id,
937: sysdate,
938: fnd_global.login_id
939: from csp_plan_details cpd,
940: csp_plan_leadtimes cpl,
941: (select round(sum(quantity*decode(round((trunc(sysdate)-trunc(plan_date))/l_forecast_period_size),
942: 1,weighted_avg_period1,
943: 2,weighted_avg_period2,
952: 11,weighted_avg_period11,
953: 12,weighted_avg_period12))*l_period_size/l_forecast_period_size) quantity,
954: cpd2.organization_id organization_id,
955: cpd2.inventory_item_id inventory_item_id
956: from csp_plan_details cpd2,
957: csp_forecast_rules_b cfrb,
958: csp_planning_parameters cpp
959: where cpd2.plan_detail_type = '6000'
960: and cpp.organization_id = cpd2.source_organization_id
972:
973: if l_forecast_method = -3 then
974: for cr in c_items loop
975: if nvl(l_item,cr.inventory_item_id) <> cr.inventory_item_id then
976: insert into csp_plan_details(
977: plan_detail_type,
978: parent_type,
979: source_number,
980: source_organization_id,
999: sysdate,
1000: fnd_global.user_id,
1001: sysdate,
1002: fnd_global.login_id
1003: from csp_plan_details
1004: where organization_id = l_organization_id
1005: and rownum <= l_forecast_periods;
1006: l_start := 1;
1007: l_forecast := 0;
1018: end if;
1019: l_start := l_period + 1;
1020: end loop;
1021: end loop;
1022: insert into csp_plan_details(
1023: plan_detail_type,
1024: parent_type,
1025: source_number,
1026: source_organization_id,
1045: sysdate,
1046: fnd_global.user_id,
1047: sysdate,
1048: fnd_global.login_id
1049: from csp_plan_details
1050: where organization_id = l_organization_id
1051: and rownum <= l_forecast_periods;
1052: elsif l_forecast_method = -4 then
1053: for cr in c_items loop
1051: and rownum <= l_forecast_periods;
1052: elsif l_forecast_method = -4 then
1053: for cr in c_items loop
1054: if nvl(l_item,cr.inventory_item_id) <> cr.inventory_item_id then
1055: insert into csp_plan_details(
1056: plan_detail_type,
1057: parent_type,
1058: source_number,
1059: source_organization_id,
1078: sysdate,
1079: fnd_global.user_id,
1080: sysdate,
1081: fnd_global.login_id
1082: from csp_plan_details
1083: where organization_id = l_organization_id
1084: and rownum <= l_forecast_periods;
1085: l_start := 1;
1086: l_forecast := 0;
1107: end if;
1108: l_start := l_period + 1;
1109: end loop;
1110: end loop;
1111: insert into csp_plan_details(
1112: plan_detail_type,
1113: parent_type,
1114: source_number,
1115: source_organization_id,
1134: sysdate,
1135: fnd_global.user_id,
1136: sysdate,
1137: fnd_global.login_id
1138: from csp_plan_details
1139: where organization_id = l_organization_id
1140: and rownum <= l_forecast_periods;
1141:
1142: end if;
1146: procedure forecast is
1147: begin
1148: for l_counter in 1..l_forecast_periods loop
1149: -- Usage Forecast
1150: insert into csp_plan_details(
1151: plan_detail_type,
1152: parent_type,
1153: source_number,
1154: source_organization_id,
1192: fnd_global.user_id,
1193: sysdate,
1194: fnd_global.login_id;
1195: -- Manual Forecast
1196: insert into csp_plan_details(
1197: plan_detail_type,
1198: parent_type,
1199: source_number,
1200: source_organization_id,
1236: sysdate,
1237: fnd_global.login_id;
1238:
1239: -- Population Forecast
1240: insert into csp_plan_details(
1241: plan_detail_type,
1242: parent_type,
1243: source_number,
1244: source_organization_id,
1282: commit;
1283: end loop;
1284:
1285: -- Warehouse Planned Orders
1286: insert into csp_plan_details(
1287: plan_detail_type,
1288: parent_type,
1289: source_number,
1290: source_organization_id,
1309: max(sysdate),
1310: max(fnd_global.user_id),
1311: max(sysdate),
1312: max(fnd_global.login_id)
1313: from csp_plan_details cpd
1314: where cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1315: and cpd.source_organization_id = l_organization_id
1316: and cpd.plan_detail_type = '4310'
1317: group by cpd.organization_id,cpd.inventory_item_id,cpd.source_organization_id,cpd.plan_date;--heh
1315: and cpd.source_organization_id = l_organization_id
1316: and cpd.plan_detail_type = '4310'
1317: group by cpd.organization_id,cpd.inventory_item_id,cpd.source_organization_id,cpd.plan_date;--heh
1318: commit;
1319: insert into csp_plan_details(
1320: plan_detail_type,
1321: parent_type,
1322: source_number,
1323: source_organization_id,
1342: max(sysdate),
1343: max(fnd_global.user_id),
1344: max(sysdate),
1345: max(fnd_global.login_id)
1346: from csp_plan_details cpd
1347: where cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1348: and cpd.organization_id = l_organization_id
1349: and cpd.plan_detail_type = '1610'
1350: group by
1357:
1358:
1359: procedure orders is
1360: begin
1361: insert into csp_plan_details(
1362: plan_detail_type,
1363: parent_type,
1364: source_number,
1365: source_organization_id,
1404: end orders;
1405:
1406: procedure supply is
1407: begin
1408: insert into csp_plan_details(
1409: plan_detail_type,
1410: parent_type,
1411: source_number,
1412: source_organization_id,
1468: trunc(nvl(expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
1469: ms.item_id,
1470: ms.to_organization_id;
1471:
1472: insert into csp_plan_details(
1473: plan_detail_type,
1474: parent_type,
1475: source_number,
1476: source_organization_id,
1533: trunc(nvl(ms.expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
1534: ms.item_id,
1535: ms.to_organization_id;
1536:
1537: insert into csp_plan_details(
1538: plan_detail_type,
1539: parent_type,
1540: source_number,
1541: source_organization_id,
1566: where pria.destination_organization_id = l_organization_id
1567: and pria.item_id = nvl(l_inventory_item_id,pria.item_id)
1568: and crph.requisition_line_id(+) = pria.requisition_line_id;
1569: /* Not needed
1570: insert into csp_plan_details(
1571: plan_detail_type,
1572: parent_type,
1573: source_number,
1574: source_organization_id,
1602:
1603: procedure total_requirement is
1604: begin
1605:
1606: insert into csp_plan_details(
1607: plan_detail_type,
1608: parent_type,
1609: source_number,
1610: source_organization_id,
1629: min(creation_date),
1630: min(last_updated_by),
1631: min(last_update_date),
1632: min(last_update_login)
1633: from csp_plan_details cpd
1634: where parent_type = '1000'
1635: and plan_detail_type in ('1100','1200','1300','1500','1600')
1636: and organization_id = l_organization_id
1637: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1635: and plan_detail_type in ('1100','1200','1300','1500','1600')
1636: and organization_id = l_organization_id
1637: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1638: and not exists (select 'x'
1639: from csp_plan_details
1640: where organization_id = cpd.organization_id
1641: and inventory_item_id = cpd.inventory_item_id
1642: and plan_date = cpd.plan_date
1643: and plan_detail_type = '1400')
1642: and plan_date = cpd.plan_date
1643: and plan_detail_type = '1400')
1644: group by parent_type,'1',null,null,plan_date,inventory_item_id,organization_id;
1645:
1646: insert into csp_plan_details(
1647: plan_detail_type,
1648: parent_type,
1649: source_number,
1650: source_organization_id,
1669: min(creation_date),
1670: min(last_updated_by),
1671: min(last_update_date),
1672: min(last_update_login)
1673: from csp_plan_details cpd
1674: where parent_type = '1000'
1675: and plan_detail_type in ('1400','1500','1600')
1676: and organization_id = l_organization_id
1677: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1675: and plan_detail_type in ('1400','1500','1600')
1676: and organization_id = l_organization_id
1677: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1678: and not exists (select 'x'
1679: from csp_plan_details
1680: where organization_id = cpd.organization_id
1681: and inventory_item_id = cpd.inventory_item_id
1682: and plan_date = cpd.plan_date
1683: and plan_detail_type in ('1000'))
1688:
1689: procedure total_on_order is
1690: begin
1691: --Total On Order
1692: insert into csp_plan_details(
1693: plan_detail_type,
1694: parent_type,
1695: source_number,
1696: source_organization_id,
1715: min(creation_date),
1716: min(last_updated_by),
1717: min(last_update_date),
1718: min(last_update_login)
1719: from csp_plan_details
1720: where parent_type in ('2100','2200','2300')
1721: and organization_id = l_organization_id
1722: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1723: group by parent_type,plan_date,--trunc(greatest(trunc(sysdate) + floor(((plan_date - trunc(sysdate))/l_period_size))*l_period_size,trunc(sysdate) - l_period_size)),
1722: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1723: group by parent_type,plan_date,--trunc(greatest(trunc(sysdate) + floor(((plan_date - trunc(sysdate))/l_period_size))*l_period_size,trunc(sysdate) - l_period_size)),
1724: inventory_item_id,organization_id;
1725:
1726: insert into csp_plan_details(
1727: plan_detail_type,
1728: parent_type,
1729: source_number,
1730: source_organization_id,
1749: min(creation_date),
1750: min(last_updated_by),
1751: min(last_update_date),
1752: min(last_update_login)
1753: from csp_plan_details
1754: where plan_detail_type in ('2100','2200','2300')
1755: and organization_id = l_organization_id
1756: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1757: group by organization_id,inventory_item_id,plan_date;
1761: procedure unfilled_requirement(p_source_type varchar2) is
1762: i number := 0;
1763: begin
1764: for i in 0..l_forecast_periods loop
1765: insert into csp_plan_details(
1766: plan_detail_type,
1767: parent_type,
1768: source_number,
1769: source_organization_id,
1796: sysdate,
1797: fnd_global.user_id,
1798: sysdate,
1799: fnd_global.login_id
1800: from csp_plan_details cpd,
1801: csp_plan_reorders cpr
1802: where cpd.plan_detail_type in ('1','1000','2000','4110','4210','4310','4220')
1803: and cpr.organization_id (+) = cpd.organization_id
1804: and cpr.inventory_item_id (+) = cpd.inventory_item_id
1819: group by cpd.organization_id,cpd.inventory_item_id;
1820: */
1821: end loop;
1822: -- delete unfilled requirement for parts that are superseded
1823: delete from csp_plan_details cpd
1824: where (organization_id,inventory_item_id) in
1825: (select csi.organization_id,csi.inventory_item_id
1826: from csp_supersede_items csi
1827: where csi.inventory_item_id = cpd.inventory_item_id
1835: end unfilled_requirement;
1836:
1837: procedure newbuy_excess_onorder is
1838: begin
1839: insert into csp_plan_details(
1840: plan_detail_type,
1841: parent_type,
1842: source_number,
1843: source_organization_id,
1869: sysdate,
1870: fnd_global.user_id,
1871: sysdate,
1872: fnd_global.login_id
1873: from csp_plan_details cpd,
1874: csp_plan_reorders cpr,
1875: csp_item_pl_params cipp
1876: where cpd.organization_id = l_organization_id
1877: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1894: fnd_global.user_id,
1895: sysdate,
1896: fnd_global.login_id;
1897: -- Delete new buy excess on orders that have a value less than minimum value
1898: delete from csp_plan_details
1899: where (plan_detail_type,organization_id,inventory_item_id) in
1900: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
1901: from csp_plan_details cpd,
1902: mtl_parameters mp,
1897: -- Delete new buy excess on orders that have a value less than minimum value
1898: delete from csp_plan_details
1899: where (plan_detail_type,organization_id,inventory_item_id) in
1900: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
1901: from csp_plan_details cpd,
1902: mtl_parameters mp,
1903: cst_item_costs cict
1904: where cpd.plan_detail_type = '8110'
1905: and mp.organization_id = cict.organization_id
1912: end;
1913:
1914: procedure unutilized_excess is
1915: begin
1916: insert into csp_plan_details(
1917: plan_detail_type,
1918: parent_type,
1919: source_number,
1920: source_organization_id,
1940: sysdate,
1941: fnd_global.user_id,
1942: sysdate,
1943: fnd_global.login_id
1944: from csp_plan_details cpd,
1945: csp_plan_reorders cpr
1946: where cpd.organization_id = l_organization_id
1947: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1948: and cpd.plan_detail_type in ('2300','8110','4110','9001')
1962: fnd_global.user_id,
1963: sysdate,
1964: fnd_global.login_id;
1965: /*
1966: insert into csp_plan_details(
1967: plan_detail_type,
1968: parent_type,
1969: source_number,
1970: source_organization_id,
1990: sysdate,
1991: fnd_global.user_id,
1992: sysdate,
1993: fnd_global.login_id
1994: from csp_plan_details cpd,
1995: csp_plan_reorders cpr
1996: where cpd.organization_id = l_organization_id
1997: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1998: and cpd.plan_detail_type in ('2100','2200','8120','8210','9001')
2012: fnd_global.user_id,
2013: sysdate,
2014: fnd_global.login_id;
2015: */
2016: insert into csp_plan_details(
2017: plan_detail_type,
2018: parent_type,
2019: source_number,
2020: source_organization_id,
2040: sysdate,
2041: fnd_global.user_id,
2042: sysdate,
2043: fnd_global.login_id
2044: from csp_plan_details cpd,
2045: csp_plan_reorders cpr
2046: where cpd.organization_id = l_organization_id
2047: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2048: and cpd.plan_detail_type in ('4100','2200','8120','8210','9001')
2062: fnd_global.user_id,
2063: sysdate,
2064: fnd_global.login_id;
2065:
2066: delete from csp_plan_details
2067: where (plan_detail_type,organization_id,inventory_item_id) in
2068: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2069: from csp_plan_details cpd,
2070: mtl_parameters mp,
2065:
2066: delete from csp_plan_details
2067: where (plan_detail_type,organization_id,inventory_item_id) in
2068: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2069: from csp_plan_details cpd,
2070: mtl_parameters mp,
2071: cst_item_costs cict
2072: where cpd.plan_detail_type in ('8210','8220')
2073: and mp.organization_id = cict.organization_id
2080: end;
2081:
2082: procedure unutilized_repair is
2083: begin
2084: insert into csp_plan_details(
2085: plan_detail_type,
2086: parent_type,
2087: source_number,
2088: source_organization_id,
2108: sysdate,
2109: fnd_global.user_id,
2110: sysdate,
2111: fnd_global.login_id
2112: from csp_plan_details cpd,
2113: csp_plan_reorders cpr
2114: where cpd.organization_id = l_organization_id
2115: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2116: and cpd.plan_detail_type in ('4200','2300','8110','8210','9002','9003')
2130: fnd_global.user_id,
2131: sysdate,
2132: fnd_global.login_id;
2133:
2134: delete from csp_plan_details
2135: where (plan_detail_type,organization_id,inventory_item_id) in
2136: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2137: from csp_plan_details cpd,
2138: mtl_parameters mp,
2133:
2134: delete from csp_plan_details
2135: where (plan_detail_type,organization_id,inventory_item_id) in
2136: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2137: from csp_plan_details cpd,
2138: mtl_parameters mp,
2139: cst_item_costs cict
2140: where cpd.plan_detail_type = '8310'
2141: and mp.organization_id = cict.organization_id
2148: end;
2149:
2150: procedure repair_excess_onorder is
2151: begin
2152: insert into csp_plan_details(
2153: plan_detail_type,
2154: parent_type,
2155: source_number,
2156: source_organization_id,
2183: sysdate,
2184: fnd_global.user_id,
2185: sysdate,
2186: fnd_global.login_id
2187: from csp_plan_details cpd,
2188: csp_plan_reorders cpr,
2189: csp_item_pl_params cipp
2190: where cpd.organization_id = l_organization_id
2191: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2209: sysdate,
2210: fnd_global.login_id;
2211:
2212: -- Delete repair excess on orders that have a value less than minimum value
2213: delete from csp_plan_details
2214: where (plan_detail_type,organization_id,inventory_item_id) in
2215: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2216: from csp_plan_details cpd,
2217: mtl_parameters mp,
2212: -- Delete repair excess on orders that have a value less than minimum value
2213: delete from csp_plan_details
2214: where (plan_detail_type,organization_id,inventory_item_id) in
2215: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2216: from csp_plan_details cpd,
2217: mtl_parameters mp,
2218: cst_item_costs cict
2219: where cpd.plan_detail_type = '8120'
2220: and mp.organization_id = cict.organization_id
2227: end;
2228:
2229: procedure reschedule_in is
2230: begin
2231: insert into csp_plan_details(
2232: plan_detail_type,
2233: parent_type,
2234: inventory_item_id,
2235: organization_id,
2257: sysdate,
2258: fnd_global.user_id,
2259: sysdate,
2260: fnd_global.login_id
2261: from csp_plan_details cpd,
2262: csp_plan_reorders cpr
2263: where cpd.organization_id = l_organization_id
2264: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2265: and cpd.plan_detail_type = l_onhand_type_in
2275: and trunc(sysdate+l_end_day_in)
2276: and cpr.organization_id = cpd.organization_id
2277: and cpr.inventory_item_id = cpd.inventory_item_id
2278: and exists (select 'x'
2279: from csp_plan_details
2280: where organization_id = cpd.organization_id
2281: and inventory_item_id = cpd.inventory_item_id
2282: and plan_detail_type = '2000'
2283: and quantity > 0
2283: and quantity > 0
2284: and plan_date >= cpd.plan_date)
2285: and plan_date >= trunc(sysdate);
2286:
2287: delete from csp_plan_details
2288: where (plan_detail_type,organization_id,inventory_item_id) in
2289: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2290: from csp_plan_details cpd
2291: where cpd.plan_detail_type = '8410'
2286:
2287: delete from csp_plan_details
2288: where (plan_detail_type,organization_id,inventory_item_id) in
2289: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2290: from csp_plan_details cpd
2291: where cpd.plan_detail_type = '8410'
2292: and cpd.organization_id = l_organization_id
2293: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2294: group by cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2293: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2294: group by cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2295: having count(*) < l_periods_in);
2296: /*
2297: insert into csp_plan_details(
2298: plan_detail_type,
2299: parent_type,
2300: inventory_item_id,
2301: organization_id,
2312: sysdate,
2313: fnd_global.user_id,
2314: sysdate,
2315: fnd_global.login_id
2316: from csp_plan_details cpd,
2317: csp_plan_reorders cpr
2318: where cpd.organization_id = l_organization_id
2319: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2320: and cpd.plan_detail_type = l_onhand_type_in
2335: end reschedule_in;
2336:
2337: procedure reschedule_out is
2338: begin
2339: insert into csp_plan_details(
2340: plan_detail_type,
2341: parent_type,
2342: inventory_item_id,
2343: organization_id,
2360: sysdate,
2361: fnd_global.user_id,
2362: sysdate,
2363: fnd_global.login_id
2364: from csp_plan_details cpd,
2365: csp_plan_reorders cpr,
2366: cst_item_costs cic,
2367: mtl_parameters mp
2368: where cpd.organization_id = l_organization_id
2381: and cic.item_cost * (cpd.quantity - greatest(cpr.repair_rop + cpr.repair_edq * l_edq_multiple_out,
2382: cpr.excess_rop + cpr.excess_edq * l_edq_multiple_out,
2383: cpr.newbuy_rop + cpr.newbuy_edq * l_edq_multiple_out)) > l_onhand_value_out;
2384:
2385: delete from csp_plan_details
2386: where (plan_detail_type,organization_id,inventory_item_id) in
2387: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2388: from csp_plan_details cpd
2389: where cpd.plan_detail_type = '8420'
2384:
2385: delete from csp_plan_details
2386: where (plan_detail_type,organization_id,inventory_item_id) in
2387: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2388: from csp_plan_details cpd
2389: where cpd.plan_detail_type = '8420'
2390: and cpd.organization_id = l_organization_id
2391: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2392: group by cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2394: end reschedule_out;
2395:
2396: procedure excess_excess_onorder is
2397: begin
2398: insert into csp_plan_details(
2399: plan_detail_type,
2400: parent_type,
2401: source_number,
2402: source_organization_id,
2430: sysdate,
2431: fnd_global.user_id,
2432: sysdate,
2433: fnd_global.login_id
2434: from csp_plan_details cpd,
2435: csp_plan_reorders cpr
2436: where cpd.organization_id = l_organization_id
2437: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2438: and cpd.quantity > 0
2453: sysdate,
2454: fnd_global.login_id;
2455:
2456: -- Delete excess excess on orders that have a value less than minimum value
2457: delete from csp_plan_details
2458: where (plan_detail_type,organization_id,inventory_item_id) in
2459: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2460: from csp_plan_details cpd,
2461: mtl_parameters mp,
2456: -- Delete excess excess on orders that have a value less than minimum value
2457: delete from csp_plan_details
2458: where (plan_detail_type,organization_id,inventory_item_id) in
2459: (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2460: from csp_plan_details cpd,
2461: mtl_parameters mp,
2462: cst_item_costs cict
2463: where cpd.plan_detail_type = '8130'
2464: and mp.organization_id = cict.organization_id
2471: end;
2472:
2473: procedure excess is
2474: begin
2475: insert into csp_plan_details(
2476: plan_detail_type,
2477: parent_type,
2478: source_number,
2479: source_organization_id,
2512: sysdate,
2513: fnd_global.login_id
2514: from MRP_ITEM_SOURCING_LEVELS_V mislv,
2515: csp_plan_reorders cpr,
2516: csp_plan_details cpd
2517: where mislv.organization_id = l_organization_id
2518: and mislv.assignment_set_id = l_usable_assignment_set_id
2519: and mislv.inventory_item_id = cpd.inventory_item_id
2520: and mislv.sourcing_level not in (2,9)
2540: end;
2541:
2542: procedure repair is
2543: begin
2544: insert into csp_plan_details(
2545: plan_detail_type,
2546: parent_type,
2547: source_number,
2548: source_organization_id,
2572: fnd_global.user_id,
2573: sysdate,
2574: fnd_global.login_id
2575: from MRP_ITEM_SOURCING_LEVELS_V mislv,
2576: csp_plan_details cpd
2577: where mislv.organization_id = cpd.organization_id
2578: and mislv.assignment_set_id = l_defective_assignment_set_id
2579: and mislv.inventory_item_id = cpd.inventory_item_id
2580: and mislv.sourcing_level not in (2,9)
2582: and cpd.organization_id = l_organization_id
2583: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2584: and cpd.plan_detail_type = '1';
2585:
2586: insert into csp_plan_details(
2587: plan_detail_type,
2588: parent_type,
2589: source_number,
2590: source_organization_id,
2616: fnd_global.user_id,
2617: sysdate,
2618: fnd_global.login_id
2619: from MRP_ITEM_SOURCING_LEVELS_V mislv,
2620: csp_plan_details cpd,
2621: mtl_related_items mri,
2622: mtl_parameters mp
2623: where mislv.organization_id = cpd.organization_id
2624: and mislv.assignment_set_id = l_defective_assignment_set_id
2633: and mri.relationship_type_id = 18
2634: and mri.related_item_id = cpd.inventory_item_id;
2635: /*
2636: -- Delete repair information for down level parts
2637: delete from csp_plan_details cpd
2638: where cpd.plan_detail_type = '9002'
2639: and cpd.organization_id = l_organization_id
2640: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2641: and cpd.inventory_item_id in
2639: and cpd.organization_id = l_organization_id
2640: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2641: and cpd.inventory_item_id in
2642: (select cpd2.related_item_id
2643: from csp_plan_details cpd2
2644: where cpd2.plan_detail_type = '9003'
2645: and cpd2.organization_id = l_organization_id
2646: and cpd2.related_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id));
2647: */
2665:
2666: cursor c_unfilled_items is
2667: select distinct cpd.inventory_item_id,
2668: cpd.organization_id
2669: from csp_plan_details cpd
2670: where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
2671: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2672: and cpd.plan_detail_type = '9004'
2673: and cpd.quantity > 0;
2687: 'NEWBUY',cpr.newbuy_edq,
2688: 'REPAIR_FORECAST',cpr.repair_edq) edq_quantity,
2689: trunc(cpd.plan_date) plan_date,
2690: cpr.newbuy_safety_stock - cpr.repair_safety_stock
2691: from csp_plan_details cpd,
2692: csp_plan_reorders cpr
2693: where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
2694: and cpd.inventory_item_id = p_inventory_item_id
2695: and cpr.organization_id(+) = cpd.organization_id
2704: cursor c_excess(p_inventory_item_id number) is
2705: select cpd.source_organization_id,
2706: sum(decode(cpd.plan_detail_type,'4110',nvl(cpd.quantity,0)*-1,
2707: nvl(cpd.quantity,0)))
2708: from csp_plan_details cpd
2709: where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
2710: and cpd.inventory_item_id = p_inventory_item_id
2711: and cpd.plan_detail_type in ('4110','9001')
2712: group by cpd.source_organization_id,
2717: select cpd.source_organization_id,
2718: sum(decode(cpd.plan_detail_type,'4210',nvl(cpd.quantity,0)*-1,
2719: nvl(cpd.available_quantity,0))),
2720: cpd.related_item_id
2721: from csp_plan_details cpd
2722: where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
2723: and cpd.inventory_item_id = p_inventory_item_id
2724: and cpd.plan_detail_type in ('4210','9002','9003')
2725: group by cpd.source_organization_id,
2732: sum(nvl(decode(cpd.plan_detail_type,'4220',cpd.quantity*-1,cpd.quantity),0)),
2733: cpr.repair_edq,
2734: null
2735: from csp_plan_reorders cpr,
2736: csp_plan_details cpd
2737: where cpr.organization_id = l_organization_id
2738: and cpr.inventory_item_id = p_inventory_item_id
2739: and cpd.organization_id = cpr.organization_id
2740: and cpd.inventory_item_id = p_inventory_item_id
2849: ceil(l_unfilled_quantity/nvl(l_edq_quantity,1))*nvl(l_edq_quantity,1));
2850: end if;
2851: end if;
2852: if l_order_quantity > 0 then
2853: insert into csp_plan_details(
2854: plan_detail_id,
2855: plan_detail_type,
2856: parent_type,
2857: source_number,
2865: creation_date,
2866: last_updated_by,
2867: last_update_date,
2868: last_update_login)
2869: select csp_plan_details_s1.nextval,
2870: decode(l_source_type,'EXCESS','4110',
2871: 'REPAIR','4210',
2872: 'NEWBUY','4310',
2873: 'REPAIR_FORECAST','4220'),
2889: sysdate,
2890: fnd_global.user_id,
2891: sysdate,
2892: fnd_global.login_id
2893: from csp_plan_details cpd
2894: where cpd.organization_id = l_organization_id
2895: and cpd.inventory_item_id = cr.inventory_item_id
2896: and cpd.plan_detail_type = '1000'
2897: and cpd.plan_date = l_plan_date;
2895: and cpd.inventory_item_id = cr.inventory_item_id
2896: and cpd.plan_detail_type = '1000'
2897: and cpd.plan_date = l_plan_date;
2898:
2899: update csp_plan_details
2900: set quantity = quantity - l_order_quantity
2901: where organization_id = l_organization_id
2902: and inventory_item_id = cr.inventory_item_id
2903: and plan_date >= l_plan_date
2903: and plan_date >= l_plan_date
2904: and plan_detail_type = '9004';
2905: end if;
2906: if p_source_type = 'REPAIR_FORECAST' then
2907: update csp_plan_details
2908: set quantity = 0
2909: where organization_id = l_organization_id
2910: and inventory_item_id = cr.inventory_item_id
2911: and plan_date = l_plan_date
2917: close c_unfilled_requirements;
2918: end if;
2919: end loop;
2920:
2921: delete from csp_plan_details
2922: where organization_id = l_organization_id
2923: and plan_detail_type = '9004';
2924: end planned_orders;
2925:
2925:
2926: procedure total_planned_orders is
2927: begin
2928:
2929: insert into csp_plan_details(
2930: plan_detail_type,
2931: parent_type,
2932: source_number,
2933: source_organization_id,
2952: sysdate,
2953: fnd_global.user_id,
2954: sysdate,
2955: fnd_global.login_id
2956: from csp_plan_details cpd
2957: where cpd.plan_detail_type in ('4110','4210','4310')
2958: and cpd.organization_id = l_organization_id
2959: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2960: group by cpd.organization_id,cpd.inventory_item_id,trunc(plan_date),--hehtrunc(trunc(sysdate) + (trunc((plan_date - trunc(sysdate))/l_period_size))*l_period_size)
2959: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2960: group by cpd.organization_id,cpd.inventory_item_id,trunc(plan_date),--hehtrunc(trunc(sysdate) + (trunc((plan_date - trunc(sysdate))/l_period_size))*l_period_size)
2961: cpd.plan_detail_type;
2962:
2963: insert into csp_plan_details(
2964: plan_detail_type,
2965: parent_type,
2966: source_number,
2967: source_organization_id,
2986: sysdate,
2987: fnd_global.user_id,
2988: sysdate,
2989: fnd_global.login_id
2990: from csp_plan_details cpd
2991: where cpd.plan_detail_type in ('4100','4200','4300')
2992: and cpd.organization_id = l_organization_id
2993: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2994: group by cpd.organization_id,cpd.inventory_item_id,cpd.plan_detail_type,cpd.plan_date;
2997: procedure projected_onhand_1 is
2998: i number;
2999: begin
3000: for i in 0..l_forecast_periods loop
3001: insert into csp_plan_details(
3002: plan_detail_type,
3003: parent_type,
3004: source_number,
3005: source_organization_id,
3024: sysdate,
3025: fnd_global.user_id,
3026: sysdate,
3027: fnd_global.login_id
3028: from csp_plan_details cpd,csp_plan_details cpd2
3029: where cpd.plan_detail_type in ('1000','2000')
3030: and cpd2.plan_detail_type = '1'
3031: and cpd.organization_id = cpd2.organization_id
3032: and cpd.inventory_item_id = cpd2.inventory_item_id
3039: end projected_onhand_1;
3040:
3041: procedure projected_onhand_2 is
3042: begin
3043: insert into csp_plan_details(
3044: plan_detail_type,
3045: parent_type,
3046: source_number,
3047: source_organization_id,
3067: sysdate,
3068: fnd_global.user_id,
3069: sysdate,
3070: fnd_global.login_id
3071: from csp_plan_details cpd,
3072: csp_plan_details cpd2
3073: where cpd.plan_detail_type = '3000'
3074: and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
3075: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3068: fnd_global.user_id,
3069: sysdate,
3070: fnd_global.login_id
3071: from csp_plan_details cpd,
3072: csp_plan_details cpd2
3073: where cpd.plan_detail_type = '3000'
3074: and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
3075: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3076: and cpd2.organization_id = cpd.organization_id
3078: and cpd2.plan_date <= cpd.plan_date+6
3079: and cpd2.plan_detail_type in ('4000','4220')
3080: group by cpd.organization_id,cpd.inventory_item_id,cpd.plan_date;
3081:
3082: insert into csp_plan_details(
3083: plan_detail_type,
3084: parent_type,
3085: source_number,
3086: source_organization_id,
3105: sysdate,
3106: fnd_global.user_id,
3107: sysdate,
3108: fnd_global.login_id
3109: from csp_plan_details cpd
3110: where cpd.plan_detail_type = '3000'
3111: and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
3112: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3113: and cpd.plan_date not in
3111: and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
3112: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3113: and cpd.plan_date not in
3114: (select cpd2.plan_date
3115: from csp_plan_details cpd2
3116: where cpd2.organization_id = cpd.organization_id
3117: and cpd2.inventory_item_id = cpd.inventory_item_id
3118: and cpd2.plan_detail_type = '5000');
3119:
3120: end projected_onhand_2;
3121:
3122: procedure onhand is
3123: begin
3124: insert into csp_plan_details(
3125: plan_detail_type,
3126: parent_type,
3127: source_number,
3128: source_organization_id,
3171: (select l_inventory_item_id
3172: from dual
3173: union
3174: select distinct cpd2.inventory_item_id
3175: from csp_plan_details cpd2
3176: where cpd2.plan_detail_type in ('1000','2000')
3177: and cpd2.organization_id = l_organization_id
3178: and cpd2.inventory_item_id = nvl(l_inventory_item_id,cpd2.inventory_item_id));
3179: end onhand;
3180:
3181: procedure review_superseded_parts is
3182: begin
3183:
3184: insert into csp_plan_details(
3185: plan_detail_type,
3186: parent_type,
3187: source_number,
3188: source_organization_id,
3207: min(cpd.creation_date),
3208: min(cpd.last_updated_by),
3209: min(cpd.last_update_date),
3210: min(cpd.last_update_login)
3211: from csp_plan_details cpd,
3212: csp_supersede_items csi
3213: where cpd.plan_detail_type in ('1200','1300','1400','2000')
3214: and cpd.organization_id = l_organization_id
3215: and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3219: and csi.inventory_item_id <> csi.item_supplied
3220: group by cpd.organization_id,cpd.inventory_item_id;
3221:
3222: -- Delete recommendations for superseeded parts
3223: delete from csp_plan_details cpd
3224: where (organization_id,inventory_item_id) in
3225: (select csi.organization_id,csi.inventory_item_id
3226: from csp_supersede_items csi
3227: where csi.inventory_item_id = cpd.inventory_item_id
3302: period_size,
3303: forecast_periods,
3304: p_history_type,
3305: l_history_date
3306: from csp_plan_details
3307: where organization_id = p_organization_id
3308: and inventory_item_id = nvl(p_inventory_item_id,inventory_item_id);
3309: insert into csp_pl_param_histories(
3310: organization_id,
3366: sysdate,
3367: fnd_global.user_id,
3368: sysdate,
3369: fnd_global.login_id
3370: from csp_plan_details cpd,
3371: csp_planning_parameters cpp,
3372: csp_plan_reorders cpr,
3373: csp_plan_leadtimes cpl,
3374: csp_item_pl_params cipp
3396: p_inventory_item_id number,
3397: p_history_date date) is
3398: begin
3399:
3400: delete from csp_plan_details
3401: where organization_id = p_organization_id
3402: and inventory_item_id = p_inventory_item_id;
3403:
3404: insert into csp_plan_details(
3400: delete from csp_plan_details
3401: where organization_id = p_organization_id
3402: and inventory_item_id = p_inventory_item_id;
3403:
3404: insert into csp_plan_details(
3405: plan_detail_type,
3406: organization_id,
3407: inventory_item_id,
3408: parent_type,
3468: close c_forecast_rules;
3469: l_organization_id := p_organization_id;
3470: l_inventory_item_id := p_inventory_item_id;
3471: begin
3472: delete from csp_plan_details
3473: where organization_id = p_organization_id
3474: and inventory_item_id = p_inventory_item_id
3475: and plan_detail_type in ('1','9001','9002','9003');
3476: exception
3532: where reschedule_rule_id = l_reschedule_rule_id;
3533:
3534: cursor c_order_automation(p_organization_id number) is
3535: select inventory_item_id
3536: from csp_plan_details
3537: where organization_id = p_organization_id
3538: and plan_detail_type in ('8610','8620','8630');
3539:
3540: begin
3550: purge_saved_plans(p_days => p_purge_saved_plans);
3551: end if;
3552:
3553: if l_inventory_item_id is null then
3554: delete from csp_plan_details
3555: where organization_id = nvl(l_organization_id,organization_id)
3556: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id);
3557: commit;
3558: else
3555: where organization_id = nvl(l_organization_id,organization_id)
3556: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id);
3557: commit;
3558: else
3559: delete from csp_plan_details
3560: where organization_id = nvl(l_organization_id,organization_id)
3561: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
3562: and plan_detail_type not in ('8610','8620','8630');
3563: commit;
3658: reschedule_in;commit;
3659: reschedule_out;commit;
3660: review_superseded_parts;commit;
3661: -- Clean up exceptions
3662: delete from csp_plan_details
3663: where plan_detail_type in ('8110','8120','8130','8210',
3664: '8220','8310','8410','8420')
3665: and organization_id = l_organization_id
3666: and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)