[Home] [Help]
742: -- if still null, default from job if load type is reschedule job
743: update wip_job_schedule_interface wjsi
744: set wjsi.schedule_group_id =
745: (select wdj.schedule_group_id
746: from wip_discrete_jobs wdj
747: where wdj.wip_entity_id = wjsi.wip_entity_id
748: and wdj.organization_id = wjsi.organization_id),
749: wjsi.last_update_date = sysdate
750: where wjsi.group_id = p_groupID
1056: wjsi.firm_planned_flag = WIP_CONSTANTS.YES)
1057: or (wjsi.firm_planned_flag = WIP_CONSTANTS.YES and
1058: wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1059: WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1060: from wip_discrete_jobs wdj
1061: where wdj.wip_entity_id = wjsi.wip_entity_id
1062: and wdj.organization_id = wjsi.organization_id))
1063: or (wjsi.firm_planned_flag not in (WIP_CONSTANTS.YES, WIP_CONSTANTS.NO)))
1064: returning wjsi.interface_id bulk collect into l_interfaceTbl;
1134: end if;
1135:
1136: update wip_job_schedule_interface wjsi
1137: set primary_item_id = (select wdj.primary_item_id
1138: from wip_discrete_jobs wdj
1139: where wdj.organization_id = wjsi.organization_id
1140: and wdj.wip_entity_id = wjsi.wip_entity_id)
1141: where wjsi.group_id = p_groupID
1142: and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1319: and wjsi.status_type is null;
1320:
1321: update wip_job_schedule_interface wjsi
1322: set wjsi.status_type = (select wdj.status_type
1323: from wip_discrete_jobs wdj
1324: where wdj.wip_entity_id = wjsi.wip_entity_id
1325: and wdj.organization_id = wjsi.organization_id),
1326: wjsi.last_update_date = sysdate
1327: where wjsi.group_id = p_groupID
1377: or (wjsi.load_type = wip_constants.create_ns_job and
1378: wjsi.routing_reference_id is not null) )
1379: and wjsi.routing_reference_segments is not null
1380: and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
1381: from wip_discrete_jobs wdj
1382: where wdj.wip_entity_id = wjsi.wip_entity_id
1383: and wdj.organization_id = wjsi.organization_id)
1384: returning wjsi.interface_id bulk collect into l_interfaceTbl;
1385:
1399: wip_constants.create_sched,
1400: wip_constants.resched_job)
1401: and wjsi.routing_reference_id is not null
1402: and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
1403: from wip_discrete_jobs wdj
1404: where wdj.wip_entity_id = wjsi.wip_entity_id
1405: and wdj.organization_id = wjsi.organization_id)
1406: returning wjsi.interface_id bulk collect into l_interfaceTbl;
1407:
1421: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1422: and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1423: OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
1424: and WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1425: from wip_discrete_jobs wdj
1426: where wdj.wip_entity_id = wjsi.wip_entity_id
1427: and wdj.organization_id = wjsi.organization_id)))
1428: and wjsi.routing_reference_segments is not null
1429: and wjsi.routing_reference_id is null
1441: end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1442: /* Modified for bug 5479283. When Null is passed for routing reference, old value is maintained. */
1443: update wip_job_schedule_interface wjsi
1444: set routing_reference_id = decode(wjsi.routing_reference_segments,null,(select wdj.routing_reference_id
1445: from wip_discrete_jobs wdj
1446: where wdj.wip_entity_id = wjsi.wip_entity_id
1447: and wdj.organization_id = wjsi.organization_id),
1448: (select inventory_item_id
1449: from mtl_system_items_kfv msik
1455: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1456: and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1457: OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1458: WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1459: from wip_discrete_jobs wdj
1460: where wdj.wip_entity_id = wjsi.wip_entity_id
1461: and wdj.organization_id = wjsi.organization_id)))
1462: --and wjsi.routing_reference_segments is not null
1463: and wjsi.routing_reference_id is null ;
1472: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1473: and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1474: OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1475: WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1476: from wip_discrete_jobs wdj
1477: where wdj.wip_entity_id = wjsi.wip_entity_id
1478: and wdj.organization_id = wjsi.organization_id)))
1479: and wjsi.routing_reference_id is not null
1480: and not exists (select 1
1497: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1498: and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1499: OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1500: WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1501: from wip_discrete_jobs wdj
1502: where wdj.wip_entity_id = wjsi.wip_entity_id
1503: and wdj.organization_id = wjsi.organization_id)))
1504: and wjsi.routing_reference_id is not null
1505: and ('Y' <> (select msi.build_in_wip_flag
1546: and bor.organization_id = wjsi.organization_id
1547: and bor.assembly_item_id = decode(wjsi.load_type,
1548: wip_constants.create_ns_job, wjsi.routing_reference_id,
1549: wip_constants.resched_job, decode((select wdj.job_type
1550: from wip_discrete_jobs wdj
1551: where wdj.organization_id = wjsi.organization_id
1552: and wdj.wip_entity_id = wjsi.wip_entity_id),
1553: 1, wjsi.primary_item_id, wjsi.routing_reference_id),
1554: wjsi.primary_item_id)
1569: /*Modified for bug 5479283. When Null is passed for alt routing designator, old value is maintained.
1570: When g_miss_char is passed for alt routing designator, updated to primary routing/bom.*/
1571: update wip_job_schedule_interface wjsi
1572: set wjsi.alternate_routing_designator = decode(wjsi.alternate_routing_designator,fnd_api.g_miss_char,null,null,(select wdj.alternate_routing_designator
1573: from wip_discrete_jobs wdj
1574: where wdj.organization_id = wjsi.organization_id
1575: and wdj.wip_entity_id = wjsi.wip_entity_id),wjsi.alternate_routing_designator),
1576: wjsi.last_update_date = sysdate
1577: where wjsi.group_id = p_groupID
1593: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1594: and wjsi.load_type = wip_constants.resched_job
1595: and nvl(wjsi.alternate_routing_designator, '@@-@@@') <>
1596: nvl( (select wdj.alternate_routing_designator
1597: from wip_discrete_jobs wdj
1598: where wdj.organization_id = wjsi.organization_id
1599: and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1600: and (select status_type
1601: from wip_discrete_jobs wdj
1597: from wip_discrete_jobs wdj
1598: where wdj.organization_id = wjsi.organization_id
1599: and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1600: and (select status_type
1601: from wip_discrete_jobs wdj
1602: where wdj.organization_id = wjsi.organization_id
1603: and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
1604: returning wjsi.interface_id bulk collect into l_interfaceTbl;
1605:
1631:
1632: /* Fix for bug 5020741. Do not default routing revision/revision date while updating jobs.
1633: update wip_job_schedule_interface wjsi
1634: set wjsi.routing_revision = (select wdj.routing_revision
1635: from wip_discrete_jobs wdj
1636: where wdj.organization_id = wjsi.organization_id
1637: and wdj.wip_entity_id = wjsi.wip_entity_id),
1638: wjsi.last_update_date = sysdate
1639: where wjsi.group_id = p_groupID
1651: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1652: and wjsi.load_type = wip_constants.resched_job
1653: and nvl(wjsi.routing_revision, '@@-@@@') <>
1654: nvl( (select wdj.routing_revision
1655: from wip_discrete_jobs wdj
1656: where wdj.organization_id = wjsi.organization_id
1657: and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1658: and (select status_type
1659: from wip_discrete_jobs wdj
1655: from wip_discrete_jobs wdj
1656: where wdj.organization_id = wjsi.organization_id
1657: and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1658: and (select status_type
1659: from wip_discrete_jobs wdj
1660: where wdj.organization_id = wjsi.organization_id
1661: and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
1662: returning wjsi.interface_id bulk collect into l_interfaceTbl;
1663:
1667: end if;
1668:
1669: update wip_job_schedule_interface wjsi
1670: set wjsi.routing_revision_date = (select wdj.routing_revision_date
1671: from wip_discrete_jobs wdj
1672: where wdj.organization_id = wjsi.organization_id
1673: and wdj.wip_entity_id = wjsi.wip_entity_id),
1674: wjsi.last_update_date = sysdate
1675: where wjsi.group_id = p_groupID
1687: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1688: and wjsi.load_type = wip_constants.resched_job
1689: and nvl(wjsi.routing_revision_date, fnd_api.g_miss_date) <>
1690: nvl((select wdj.routing_revision_date
1691: from wip_discrete_jobs wdj
1692: where wdj.organization_id = wjsi.organization_id
1693: and wdj.wip_entity_id = wjsi.wip_entity_id), fnd_api.g_miss_date)
1694: and (select wdj.status_type
1695: from wip_discrete_jobs wdj
1691: from wip_discrete_jobs wdj
1692: where wdj.organization_id = wjsi.organization_id
1693: and wdj.wip_entity_id = wjsi.wip_entity_id), fnd_api.g_miss_date)
1694: and (select wdj.status_type
1695: from wip_discrete_jobs wdj
1696: where wdj.organization_id = wjsi.organization_id
1697: and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
1698: returning wjsi.interface_id bulk collect into l_interfaceTbl;
1699:
1718: or (wjsi.load_type = wip_constants.create_ns_job and
1719: wjsi.bom_reference_id is not null) )
1720: and wjsi.bom_reference_segments is not null
1721: and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
1722: from wip_discrete_jobs wdj
1723: where wdj.wip_entity_id = wjsi.wip_entity_id
1724: and wdj.organization_id = wjsi.organization_id)
1725: returning wjsi.interface_id bulk collect into l_interfaceTbl;
1726:
1740: wip_constants.create_sched,
1741: wip_constants.resched_job)
1742: and wjsi.bom_reference_id is not null
1743: and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
1744: from wip_discrete_jobs wdj
1745: where wdj.wip_entity_id = wjsi.wip_entity_id
1746: and wdj.organization_id = wjsi.organization_id)
1747: returning wjsi.interface_id bulk collect into l_interfaceTbl;
1748:
1763: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1764: and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1765: OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1766: WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1767: from wip_discrete_jobs wdj
1768: where wdj.wip_entity_id = wjsi.wip_entity_id
1769: and wdj.organization_id = wjsi.organization_id)))
1770: and wjsi.bom_reference_segments is not null
1771: and wjsi.bom_reference_id is null
1783: end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1784: /*Modified for bug 5479283. When Null is passed for bom reference, old value is maintained. */
1785: update wip_job_schedule_interface wjsi
1786: set bom_reference_id = decode(wjsi.bom_reference_segments,null,(select wdj.bom_reference_id
1787: from wip_discrete_jobs wdj
1788: where wdj.wip_entity_id = wjsi.wip_entity_id
1789: and wdj.organization_id = wjsi.organization_id),
1790: (select inventory_item_id
1791: from mtl_system_items_kfv msik
1797: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1798: and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1799: OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1800: WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1801: from wip_discrete_jobs wdj
1802: where wdj.wip_entity_id = wjsi.wip_entity_id
1803: and wdj.organization_id = wjsi.organization_id)))
1804: --and wjsi.bom_reference_segments is not null
1805: and (wjsi.bom_reference_id is null or wjsi.bom_reference_id=fnd_api.g_miss_num);
1814: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1815: and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1816: OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1817: WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1818: from wip_discrete_jobs wdj
1819: where wdj.wip_entity_id = wjsi.wip_entity_id
1820: and wdj.organization_id = wjsi.organization_id)))
1821: and wjsi.bom_reference_id is not null
1822: and not exists (select 1
1839: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1840: and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1841: OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1842: WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1843: from wip_discrete_jobs wdj
1844: where wdj.wip_entity_id = wjsi.wip_entity_id
1845: and wdj.organization_id = wjsi.organization_id)))
1846: and wjsi.bom_reference_id is not null
1847: and ('Y' <> (select msi.build_in_wip_flag
1886: and bba.organization_id = wjsi.organization_id
1887: and bba.assembly_item_id = decode(wjsi.load_type,
1888: wip_constants.create_ns_job, wjsi.bom_reference_id,
1889: wip_constants.resched_job, decode((select wdj.job_type
1890: from wip_discrete_jobs wdj
1891: where wdj.organization_id = wjsi.organization_id
1892: and wdj.wip_entity_id = wjsi.wip_entity_id),
1893: 1, wjsi.primary_item_id, wjsi.bom_reference_id),
1894: wjsi.primary_item_id)
1911:
1912: update wip_job_schedule_interface wjsi
1913: set wjsi.alternate_bom_designator = decode(wjsi.alternate_bom_designator,fnd_api.g_miss_char,null,null,
1914: (select wdj.alternate_bom_designator
1915: from wip_discrete_jobs wdj
1916: where wdj.organization_id = wjsi.organization_id
1917: and wdj.wip_entity_id = wjsi.wip_entity_id),wjsi.alternate_bom_designator),
1918: wjsi.last_update_date = sysdate
1919: where wjsi.group_id = p_groupID
1933: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1934: and wjsi.load_type = wip_constants.resched_job
1935: and nvl(wjsi.alternate_bom_designator, '@@-@@@') <>
1936: nvl( (select wdj.alternate_bom_designator
1937: from wip_discrete_jobs wdj
1938: where wdj.organization_id = wjsi.organization_id
1939: and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1940: and (select status_type
1941: from wip_discrete_jobs wdj
1937: from wip_discrete_jobs wdj
1938: where wdj.organization_id = wjsi.organization_id
1939: and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1940: and (select status_type
1941: from wip_discrete_jobs wdj
1942: where wdj.organization_id = wjsi.organization_id
1943: and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
1944: returning wjsi.interface_id bulk collect into l_interfaceTbl;
1945:
1971:
1972: /* Fix for bug 5020741. Do not default bom revision/revision date while updating jobs.
1973: update wip_job_schedule_interface wjsi
1974: set wjsi.bom_revision = (select wdj.bom_revision
1975: from wip_discrete_jobs wdj
1976: where wdj.organization_id = wjsi.organization_id
1977: and wdj.wip_entity_id = wjsi.wip_entity_id),
1978: wjsi.last_update_date = sysdate
1979: where wjsi.group_id = p_groupID
1991: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1992: and wjsi.load_type = wip_constants.resched_job
1993: and nvl(wjsi.bom_revision, '@@-@@@') <>
1994: nvl( (select wdj.bom_revision
1995: from wip_discrete_jobs wdj
1996: where wdj.organization_id = wjsi.organization_id
1997: and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1998: and (select status_type
1999: from wip_discrete_jobs wdj
1995: from wip_discrete_jobs wdj
1996: where wdj.organization_id = wjsi.organization_id
1997: and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1998: and (select status_type
1999: from wip_discrete_jobs wdj
2000: where wdj.organization_id = wjsi.organization_id
2001: and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
2002: returning wjsi.interface_id bulk collect into l_interfaceTbl;
2003:
2007: end if;
2008:
2009: update wip_job_schedule_interface wjsi
2010: set wjsi.bom_revision_date = (select wdj.bom_revision_date
2011: from wip_discrete_jobs wdj
2012: where wdj.organization_id = wjsi.organization_id
2013: and wdj.wip_entity_id = wjsi.wip_entity_id),
2014: wjsi.last_update_date = sysdate
2015: where wjsi.group_id = p_groupID
2027: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2028: and wjsi.load_type = wip_constants.resched_job
2029: and nvl(wjsi.bom_revision_date, fnd_api.g_miss_date) <>
2030: nvl( (select wdj.bom_revision_date
2031: from wip_discrete_jobs wdj
2032: where wdj.organization_id = wjsi.organization_id
2033: and wdj.wip_entity_id = wjsi.wip_entity_id), fnd_api.g_miss_date)
2034: and (select status_type
2035: from wip_discrete_jobs wdj
2031: from wip_discrete_jobs wdj
2032: where wdj.organization_id = wjsi.organization_id
2033: and wdj.wip_entity_id = wjsi.wip_entity_id), fnd_api.g_miss_date)
2034: and (select status_type
2035: from wip_discrete_jobs wdj
2036: where wdj.organization_id = wjsi.organization_id
2037: and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
2038: returning wjsi.interface_id bulk collect into l_interfaceTbl;
2039:
2124: and wjsi.start_quantity is not null
2125: and ( wjsi.load_type = wip_constants.create_sched
2126: or (wjsi.load_type = wip_constants.resched_job and
2127: (select wdj.status_type
2128: from wip_discrete_jobs wdj
2129: where wdj.organization_id = wjsi.organization_id
2130: and wdj.wip_entity_id = wjsi.wip_entity_id) not in
2131: (wip_constants.unreleased,
2132: wip_constants.released,
2154: wjsi.start_quantity = 0)
2155: or (wjsi.load_type = wip_constants.resched_job and
2156: wjsi.start_quantity = 0 and
2157: wip_constants.standard = (select wdj.job_type
2158: from wip_discrete_jobs wdj
2159: where wdj.organization_id = wjsi.organization_id
2160: and wdj.wip_entity_id = wjsi.wip_entity_id)))
2161: returning wjsi.interface_id bulk collect into l_interfaceTbl;
2162:
2173: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2174: and wjsi.load_type = wip_constants.resched_job
2175: and wjsi.start_quantity is not null
2176: and ( wjsi.start_quantity < (select wdj.quantity_completed
2177: from wip_discrete_jobs wdj
2178: where wdj.organization_id = wjsi.organization_id
2179: and wdj.wip_entity_id = wjsi.wip_entity_id)
2180: or (0 < (select count(*)
2181: from wip_reservations_v wr
2224: update wip_job_schedule_interface wjsi
2225: set wjsi.net_quantity = (select decode(wdj.net_quantity,
2226: wdj.start_quantity, wjsi.start_quantity,
2227: least(wdj.net_quantity, nvl(wjsi.start_quantity, wdj.net_quantity)))
2228: from wip_discrete_jobs wdj
2229: where wdj.wip_entity_id = wjsi.wip_entity_id
2230: and wdj.organization_id = wjsi.organization_id),
2231: wjsi.last_update_date = sysdate
2232: where wjsi.group_id = p_groupID
2259: or ( wjsi.net_quantity <> 0
2260: and ((wjsi.load_type = wip_constants.create_ns_job and wjsi.primary_item_id is null) or
2261: (wjsi.load_type = wip_constants.resched_job and
2262: wip_constants.nonstandard = (select wdj.job_type
2263: from wip_discrete_jobs wdj
2264: where wdj.organization_id = wjsi.organization_id
2265: and wdj.wip_entity_id = wjsi.wip_entity_id) and
2266: (select wdj.primary_item_id
2267: from wip_discrete_jobs wdj
2263: from wip_discrete_jobs wdj
2264: where wdj.organization_id = wjsi.organization_id
2265: and wdj.wip_entity_id = wjsi.wip_entity_id) and
2266: (select wdj.primary_item_id
2267: from wip_discrete_jobs wdj
2268: where wdj.organization_id = wjsi.organization_id
2269: and wdj.wip_entity_id = wjsi.wip_entity_id) is null))))
2270: returning wjsi.interface_id bulk collect into l_interfaceTbl;
2271:
2393: and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2394: and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2395: and wjsi.status_type = wip_constants.released
2396: and exists (select 1
2397: from wip_discrete_jobs
2398: where wip_entity_id = wjsi.wip_entity_id
2399: and organization_Id = wjsi.organization_id
2400: and (status_type IS NULL OR
2401: status_type NOT IN (wip_constants.released,wip_constants.comp_chrg,
2818: and ((wjsi.load_type = wip_constants.create_ns_job and
2819: wjsi.routing_reference_id is null) or
2820: (wjsi.load_type = wip_constants.resched_job and
2821: (select wdj.job_type
2822: from wip_discrete_jobs wdj
2823: where wdj.organization_id = wjsi.organization_id
2824: and wdj.wip_entity_id = wjsi.wip_entity_id) = wip_constants.nonstandard and
2825: (select wdj.routing_reference_id
2826: from wip_discrete_jobs wdj
2822: from wip_discrete_jobs wdj
2823: where wdj.organization_id = wjsi.organization_id
2824: and wdj.wip_entity_id = wjsi.wip_entity_id) = wip_constants.nonstandard and
2825: (select wdj.routing_reference_id
2826: from wip_discrete_jobs wdj
2827: where wdj.organization_id = wjsi.organization_id
2828: and wdj.wip_entity_id = wjsi.wip_entity_id) is null)))
2829: or (wjsi.scheduling_method = wip_constants.ml_manual
2830: and (wjsi.first_unit_start_date is null or
2930: (wjsi.first_unit_start_date is null or wjsi.last_unit_completion_date is null))
2931: or (wjsi.load_type = wip_constants.resched_job and
2932: --when rescheduling a ns job and providing one date, it must have a routing
2933: wip_constants.nonstandard = (select wdj.job_type
2934: from wip_discrete_jobs wdj
2935: where wdj.organization_id = wjsi.organization_id
2936: and wdj.wip_entity_id = wjsi.wip_entity_id) and
2937: /* (select wdj.routing_reference_id
2938: from wip_discrete_jobs wdj
2934: from wip_discrete_jobs wdj
2935: where wdj.organization_id = wjsi.organization_id
2936: and wdj.wip_entity_id = wjsi.wip_entity_id) and
2937: /* (select wdj.routing_reference_id
2938: from wip_discrete_jobs wdj
2939: where wdj.organization_id = wjsi.organization_id
2940: and wdj.wip_entity_id = wjsi.wip_entity_id) */ wjsi.routing_reference_id is null and
2941: (select count(*)
2942: from wip_operations
3276: wdj_row.overcompletion_tolerance_value,
3277: wdj_row.completion_subinventory,
3278: wdj_row.completion_locator_id,
3279: wdj_row.build_sequence
3280: from wip_discrete_jobs wdj,
3281: wip_entities we
3282: where wdj.wip_entity_id = wjsi_row.wip_entity_id
3283: and we.wip_entity_id = wdj.wip_entity_id;
3284:
3501: if ( wjsi_row.load_type = wip_constants.resched_job ) then
3502: select pa.task_id
3503: into wjsi_row.task_id
3504: from pa_tasks_expend_v pa,
3505: wip_discrete_jobs wdj
3506: where wdj.wip_entity_id = wjsi_row.wip_entity_id
3507: and pa.project_id = nvl(wjsi_row.project_id, wdj.project_id)
3508: and pa.task_number = wjsi_row.task_number;
3509: else
3614: end if ;
3615: elsif ( wjsi_row.load_type = wip_constants.resched_job ) then
3616: select wdj.class_code
3617: into wjsi_row.class_code
3618: from wip_discrete_jobs wdj
3619: where wdj.wip_entity_id = wjsi_row.wip_entity_id
3620: and wdj.organization_id = wjsi_row.organization_id;
3621: end if;
3622: end if;
3683: wjsi_row.bom_revision_date IS NULL THEN
3684: BEGIN
3685: select bom_revision_date
3686: into wjsi_row.bom_revision_date
3687: from wip_discrete_jobs
3688: where wip_entity_id = wjsi_row.wip_entity_id;
3689: EXCEPTION
3690: WHEN OTHERS THEN
3691: wjsi_row.bom_revision_date := NULL;
3746: wjsi_row.routing_revision_date IS NULL THEN
3747: BEGIN
3748: select routing_revision_date
3749: into wjsi_row.routing_revision_date
3750: from wip_discrete_jobs
3751: where wip_entity_id = wjsi_row.wip_entity_id;
3752: EXCEPTION
3753: WHEN OTHERS THEN
3754: wjsi_row.routing_revision_date := NULL;
3859: */
3860: begin
3861: select status_type
3862: into l_old_status
3863: from wip_discrete_jobs
3864: where wip_entity_id = wjsi_row.wip_entity_id ;
3865: end ;
3866:
3867: if ( wjsi_row.status_type in (wip_constants.cancelled,
4053: if ( wjsi_row.end_item_unit_number is null) then
4054: begin
4055: select wdj.end_item_unit_number
4056: into wjsi_row.end_item_unit_number
4057: from wip_discrete_jobs wdj
4058: where wdj.wip_entity_id = wjsi_row.wip_entity_id;
4059: exception
4060: when others then
4061: fnd_message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID');
4069: wjsi_row.end_item_unit_number is not null ) then
4070: -- bug#2719927, bom revision code/reexplosion is based on bom_reference_id
4071: select primary_item_id
4072: into wjsi_row.bom_reference_id
4073: from wip_discrete_jobs
4074: where wip_entity_id = wjsi_row.wip_entity_id;
4075: end if;
4076:
4077: if ( wjsi_row.status_type <> wip_constants.unreleased and
4079: fnd_message.set_name('WIP', 'END_ITEM_UNIT_NUMBER');
4080: setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationWarning);
4081: -- Added by Renga Kannan
4082: -- Fixed for bug 5332672
4083: -- Added code to get the end unit item number from wip_discrete_jobs and populate
4084: -- to interface table
4085:
4086: begin
4087: select end_item_unit_number into wjsi_row.end_item_unit_number
4084: -- to interface table
4085:
4086: begin
4087: select end_item_unit_number into wjsi_row.end_item_unit_number
4088: from wip_discrete_jobs
4089: where wip_entity_id =
4090: (select wip_entity_id
4091: from wip_job_schedule_interface
4092: where rowid = p_rowid
4388: end if;
4389:
4390: if ( l_loadType in (wip_constants.create_job, wip_constants.create_ns_job) ) then
4391: if ( p_rtgVal is not null ) then
4392: update wip_discrete_jobs
4393: set serialization_start_op = p_rtgVal
4394: where wip_entity_id = l_wipID
4395: and exists (select 1
4396: from mtl_system_items
4398: and organization_id = l_orgID
4399: and serial_number_control_code = wip_constants.full_sn);
4400:
4401: elsif ( l_default = wip_constants.yes ) then
4402: update wip_discrete_jobs
4403: set serialization_start_op = (select nvl(min(operation_seq_num), 1)
4404: from wip_operations
4405: where wip_entity_id = l_wipID)
4406: where wip_entity_id = l_wipID
4447:
4448: if ( l_serialOp is null ) then
4449: if ( wjsi_row.load_type = wip_constants.resched_job ) then
4450: --in this case, we may need to clear the serialization op if the routing was re-exploded
4451: update wip_discrete_jobs wdj
4452: set serialization_start_op = null
4453: where wip_entity_id = l_wipEntityID
4454: and serialization_start_op <> 1
4455: and not exists(select 1
4463:
4464: --job must have an assembly, and the assembly must be serial controlled (predefined).
4465: select 1
4466: into l_dummy
4467: from wip_discrete_jobs wdj,
4468: mtl_system_items msi
4469: where wdj.primary_item_id = msi.inventory_item_id
4470: and wdj.organization_id = msi.organization_id
4471: and wdj.wip_entity_id = l_wipEntityID
4497: -- reschedule request. This is to guarantee no txns have taken place.
4498: if ( l_loadType = wip_constants.resched_job ) then
4499: select 1
4500: into l_dummy
4501: from wip_discrete_jobs
4502: where wip_entity_id = l_wipEntityID
4503: and status_type = wip_constants.unreleased;
4504: end if;
4505: exception
4656:
4657: begin
4658: select wdj.scheduled_start_date
4659: into wjsi_row.first_unit_start_date
4660: from wip_discrete_jobs wdj
4661: where wdj.wip_entity_id = wjsi_row.wip_entity_id
4662: and wdj.organization_id = wjsi_row.organization_id
4663: and exists ( select operation_seq_num
4664: from wip_operations wo