[Home] [Help]
63: l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
64: l_return_status VARCHAR2(1);
65: BEGIN
66:
67: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Quering Work Order'); END IF;
68:
69: EAM_WO_UTILITY_PVT.Query_Row
70: ( p_wip_entity_id => p_eam_wo_rec.wip_entity_id
71: , p_organization_id => p_eam_wo_rec.organization_id
72: , x_eam_wo_rec => x_old_eam_wo_rec
73: , x_Return_status => l_return_status
74: );
75:
76: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Query Row Returned with : ' || l_return_status); END IF;
77:
78: IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND AND
79: p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
80: THEN
74: );
75:
76: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Query Row Returned with : ' || l_return_status); END IF;
77:
78: IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND AND
79: p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
80: THEN
81: l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
82: l_token_tbl(1).token_value := p_eam_wo_rec.wip_entity_name;
75:
76: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Query Row Returned with : ' || l_return_status); END IF;
77:
78: IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND AND
79: p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
80: THEN
81: l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
82: l_token_tbl(1).token_value := p_eam_wo_rec.wip_entity_name;
83:
91: l_mesg_token_tbl := l_out_mesg_token_tbl;
92:
93: l_return_status := FND_API.G_RET_STS_ERROR;
94:
95: ELSIF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND AND
96: p_eam_wo_rec.transaction_type IN
97: (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
98: THEN
99: l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
93: l_return_status := FND_API.G_RET_STS_ERROR;
94:
95: ELSIF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND AND
96: p_eam_wo_rec.transaction_type IN
97: (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
98: THEN
99: l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
100: l_token_tbl(1).token_value := p_eam_wo_rec.wip_entity_name;
101:
122: l_mesg_token_tbl := l_out_mesg_token_tbl;
123: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124:
125: ELSE /* Assign the relevant transaction type for SYNC operations */
126: IF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_SYNC THEN
127: IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND THEN
128: x_old_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
129: ELSE
130: x_old_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
123: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124:
125: ELSE /* Assign the relevant transaction type for SYNC operations */
126: IF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_SYNC THEN
127: IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND THEN
128: x_old_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
129: ELSE
130: x_old_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
131: END IF;
124:
125: ELSE /* Assign the relevant transaction type for SYNC operations */
126: IF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_SYNC THEN
127: IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND THEN
128: x_old_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
129: ELSE
130: x_old_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
131: END IF;
132: END IF;
126: IF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_SYNC THEN
127: IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND THEN
128: x_old_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
129: ELSE
130: x_old_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
131: END IF;
132: END IF;
133: l_return_status := FND_API.G_RET_STS_SUCCESS;
134:
167: BEGIN
168:
169: x_return_status := FND_API.G_RET_STS_SUCCESS;
170:
171: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Within WO Check Attributes b4 Defaulting . . . '); END IF;
172:
173:
174: -- organization_id
175:
172:
173:
174: -- organization_id
175:
176: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating organization_id . . . '); END IF;
177:
178: declare
179: l_disable_date date;
180: begin
218:
219:
220: -- organization_id (EAM enabled)
221:
222: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating organization_id (EAM enabled) . . . '); END IF;
223:
224: begin
225:
226: select 1
254: end;
255:
256: -- maintenance_object_source
257:
258: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating maintenance_object_source . . . '); END IF;
259:
260: begin
261:
262: if p_eam_wo_rec.maintenance_object_source is null or
311: end;
312:
313: -- maintenance_object_type
314:
315: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating maintenance_object_type . . . '); END IF;
316:
317: begin
318:
319: if p_eam_wo_rec.maintenance_object_type is null or
369: end;
370:
371: -- maintenance_object_id
372:
373: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating maintenance_object_id . . . '); END IF;
374:
375: begin
376:
377: if p_eam_wo_rec.maintenance_object_id is null or
383: IF p_eam_wo_rec.maintenance_object_type = 3 then
384: --CMRO does not use the EAM family of maintenance orgs concept.
385: IF p_eam_wo_rec.maintenance_object_source = 1 THEN
386:
387: IF p_eam_wo_rec.transaction_type <> EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
388: select maintenance_object_id into l_old_eam_wo_moid
389: from wip_discrete_jobs
390: where wip_entity_id = p_eam_wo_rec.wip_entity_id
391: and organization_id = p_eam_wo_rec.organization_id
390: where wip_entity_id = p_eam_wo_rec.wip_entity_id
391: and organization_id = p_eam_wo_rec.organization_id
392: and rownum = 1;
393: END IF;
394: IF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
395: OR (p_eam_wo_rec.transaction_type <> EAM_PROCESS_WO_PVT.G_OPR_CREATE
396: AND l_old_eam_wo_moid <> p_eam_wo_rec.maintenance_object_id) THEN
397: -- Code for create validation
398: select 1 into g_dummy
391: and organization_id = p_eam_wo_rec.organization_id
392: and rownum = 1;
393: END IF;
394: IF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
395: OR (p_eam_wo_rec.transaction_type <> EAM_PROCESS_WO_PVT.G_OPR_CREATE
396: AND l_old_eam_wo_moid <> p_eam_wo_rec.maintenance_object_id) THEN
397: -- Code for create validation
398: select 1 into g_dummy
399: from csi_item_instances cii, mtl_parameters mp
461: end;
462:
463: -- rebuild_item_id
464:
465: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating rebuild_item_id . . . '); END IF;
466:
467: begin
468:
469: -- for CMRO work orders, the rebuild_item_id cannot be null.
498: end;
499:
500: -- rebuild_item_id and asset_group_id
501:
502: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating rebuild_item_id and asset_group_id. . . '); END IF;
503:
504: begin
505:
506: -- rebuild_item_id and asset_group_id cannot both be null.
535:
536:
537: -- wip_entity_id
538:
539: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating wip_entity_id . . . '); END IF;
540:
541: begin
542:
543: if p_eam_wo_rec.wip_entity_id is not null and
626: BEGIN
627: l_mo_err_flag:= '';
628: x_return_status := FND_API.G_RET_STS_SUCCESS;
629:
630: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Within WO Check Attributes . . . '); END IF;
631:
632:
633: -- organization_id
634:
631:
632:
633: -- organization_id
634:
635: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating organization_id . . . '); END IF;
636:
637: declare
638: l_disable_date date;
639: begin
700:
701:
702: -- organization_id (EAM enabled)
703:
704: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating organization_id (EAM enabled) . . . '); END IF;
705:
706: begin
707:
708: select 1
737:
738:
739: -- maintenance_object_type
740:
741: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating maintenance_object_type . . . '); END IF;
742:
743: begin
744:
745: if (p_eam_wo_rec.maintenance_object_type is not null) then
776:
777:
778: -- object_source_id
779:
780: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating maintenance_object_id . . . '); END IF;
781:
782: begin
783:
784: if (p_eam_wo_rec.maintenance_object_type is not null) then
784: if (p_eam_wo_rec.maintenance_object_type is not null) then
785:
786: if (p_eam_wo_rec.maintenance_object_type = 3 and p_eam_wo_rec.maintenance_object_source = 1) then
787:
788: IF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
789: OR (p_eam_wo_rec.transaction_type <> EAM_PROCESS_WO_PVT.G_OPR_CREATE
790: AND p_eam_wo_rec.maintenance_object_id <> p_old_eam_wo_rec.maintenance_object_id) THEN
791: -- Code for creation
792: select 1 into g_dummy
785:
786: if (p_eam_wo_rec.maintenance_object_type = 3 and p_eam_wo_rec.maintenance_object_source = 1) then
787:
788: IF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
789: OR (p_eam_wo_rec.transaction_type <> EAM_PROCESS_WO_PVT.G_OPR_CREATE
790: AND p_eam_wo_rec.maintenance_object_id <> p_old_eam_wo_rec.maintenance_object_id) THEN
791: -- Code for creation
792: select 1 into g_dummy
793: from csi_item_instances cii, mtl_parameters mp
843:
844:
845:
846: -- maintenance object
847: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating maintenance object . . . '); END IF;
848:
849: begin
850:
851: if (p_eam_wo_rec.asset_group_id is not null) and (p_eam_wo_rec.rebuild_item_id is not null) then
877:
878: end;
879:
880: -- asset_group_id
881: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating asset_group_id . . . '); END IF;
882:
883: begin
884:
885: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE and p_eam_wo_rec.asset_group_id is not null) then
881: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating asset_group_id . . . '); END IF;
882:
883: begin
884:
885: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE and p_eam_wo_rec.asset_group_id is not null) then
886:
887: if(p_eam_wo_rec.asset_number is null)
888: then
889: raise fnd_api.g_exc_unexpected_error;
925:
926: end;
927:
928: -- asset_number
929: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating asset_number . . . '); END IF;
930:
931: begin
932:
933: if (p_eam_wo_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE,EAM_PROCESS_WO_PVT.G_OPR_UPDATE) and p_eam_wo_rec.asset_number is not null) then
929: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating asset_number . . . '); END IF;
930:
931: begin
932:
933: if (p_eam_wo_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE,EAM_PROCESS_WO_PVT.G_OPR_UPDATE) and p_eam_wo_rec.asset_number is not null) then
934:
935: /* In update mode do not check the current maintenance organization*/
936: select 1
937: into g_dummy
969:
970:
971:
972: -- eam_linear_location_id
973: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating eam_linear_location_id . . . '); END IF;
974:
975: begin
976:
977: if (p_eam_wo_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE,
973: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating eam_linear_location_id . . . '); END IF;
974:
975: begin
976:
977: if (p_eam_wo_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE,
978: EAM_PROCESS_WO_PVT.G_OPR_UPDATE)
979: and p_eam_wo_rec.eam_linear_location_id is not null) then
980:
981: select 1
974:
975: begin
976:
977: if (p_eam_wo_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE,
978: EAM_PROCESS_WO_PVT.G_OPR_UPDATE)
979: and p_eam_wo_rec.eam_linear_location_id is not null) then
980:
981: select 1
982: into g_dummy
1009: end;
1010:
1011:
1012: -- rebuild_item_id
1013: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating rebuild_item_id . . . '); END IF;
1014:
1015: begin
1016:
1017: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE and p_eam_wo_rec.rebuild_item_id is not null) then
1013: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating rebuild_item_id . . . '); END IF;
1014:
1015: begin
1016:
1017: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE and p_eam_wo_rec.rebuild_item_id is not null) then
1018: select 1
1019: into g_dummy
1020: from mtl_system_items msi, mtl_parameters mp
1021: where msi.inventory_item_id = p_eam_wo_rec.rebuild_item_id
1048:
1049: end;
1050:
1051: -- rebuild_serial_number
1052: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating rebuild_serial_number . . . '); END IF;
1053:
1054: begin
1055: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE) then
1056:
1051: -- rebuild_serial_number
1052: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating rebuild_serial_number . . . '); END IF;
1053:
1054: begin
1055: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE) then
1056:
1057: if(p_eam_wo_rec.rebuild_item_id is null and
1058: p_eam_wo_rec.rebuild_serial_number is not null) then
1059: raise fnd_api.g_exc_unexpected_error;
1085: /* Validation added so that rebuild serial number is not
1086: updateable if the WO is in status Released */
1087: /***** Enahancement No. : 2943473 ******/
1088:
1089: IF ( p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
1090: and p_eam_wo_rec.rebuild_item_id is not null
1091: and p_eam_wo_rec.rebuild_serial_number is not null
1092: and nvl(p_old_eam_wo_rec.rebuild_serial_number,'null_old_serial_number') <>
1093: nvl(p_eam_wo_rec.rebuild_serial_number,'null_old_serial_number') )
1140: -- When a work order created on non instantiated rebuild serial number is released ,its must have an parent work order
1141: -- 3659469
1142:
1143: -- Serial rebuild number is required while releasing the work order if Rebuild number is serial controlled
1144: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating if rebuild_serial_number is mandatory . . . '); END IF;
1145: BEGIN
1146: IF p_eam_wo_rec.status_type = 3
1147: and p_eam_wo_rec.rebuild_item_id IS NOT NULL
1148: and p_eam_wo_rec.rebuild_serial_number IS NULL
1184:
1185: end ;
1186:
1187: -- parent_wip_entity_id
1188: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating parent_wip_entity_id . . . '); END IF;
1189:
1190: begin
1191: /* Following validation ensures that only WO of parents of serialized
1192: rebuildables can become parent work orders of rebuild WO and any
1199:
1200: IF ( p_eam_wo_rec.maintenance_object_type = 3 and p_eam_wo_rec.maintenance_object_source = 1)
1201: THEN
1202:
1203: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating parent_wip_entity_id . rebuild status=' || l_rebuild_status); END IF;
1204:
1205: SELECT 1
1206: INTO g_dummy
1207: FROM mtl_object_genealogy mog, mtl_serial_numbers msn, wip_discrete_jobs wdj,
1269: /* While updating parent_wip_entity_id check for any transactions posted on the work order */
1270:
1271: begin
1272:
1273: IF ( p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
1274: AND p_old_eam_wo_rec.parent_wip_entity_id <> p_eam_wo_rec.parent_wip_entity_id )
1275: THEN
1276: EAM_WORKORDER_UTIL_PKG.CK_MATERIAL_ALLOC_ON_HOLD(X_Org_Id => p_eam_wo_rec.organization_id,
1277: X_Wip_Id => p_eam_wo_rec.wip_entity_id,
1311:
1312:
1313:
1314: -- job_name
1315: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating wip_entity_name . . . '); END IF;
1316:
1317: declare
1318: l_count NUMBER;
1319: begin
1317: declare
1318: l_count NUMBER;
1319: begin
1320:
1321: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE) then
1322:
1323: select count(*)
1324: into l_count
1325: from wip_entities
1356: end;
1357:
1358:
1359: -- job_id
1360: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating wip_entity_id . . . '); END IF;
1361:
1362: declare
1363: l_count NUMBER;
1364: begin
1362: declare
1363: l_count NUMBER;
1364: begin
1365:
1366: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE) then
1367:
1368: select count(*)
1369: into l_count
1370: from wip_entities
1399: end;
1400:
1401:
1402: -- firm_planned_flag
1403: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating firm_planned_flag . . . '); END IF;
1404:
1405: begin
1406:
1407: if p_eam_wo_rec.firm_planned_flag not in (wip_constants.yes, wip_constants.no) then
1435:
1436:
1437:
1438: -- issue_zero_cost_flag
1439: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating wip_entity_name . . . '); END IF;
1440:
1441: declare
1442: l_count NUMBER;
1443: begin
1473:
1474:
1475:
1476: -- schedule_group_id
1477: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating schedule_group_id . . . '); END IF;
1478: begin
1479:
1480: if p_eam_wo_rec.schedule_group_id is not null then
1481:
1508: return;
1509:
1510: end;
1511:
1512: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('p_eam_wo_rec.status_type ' || p_eam_wo_rec.status_type); END IF;
1513: --attribute change not allowed for comp_no_chrg,closed,pending-close,failed-close and cancelled
1514: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating attribute change for complete_no_chrg,cancelled,closed,pending-cl,failed-close statuses');END IF;
1515: --start of fix for 3389850
1516:
1510: end;
1511:
1512: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('p_eam_wo_rec.status_type ' || p_eam_wo_rec.status_type); END IF;
1513: --attribute change not allowed for comp_no_chrg,closed,pending-close,failed-close and cancelled
1514: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating attribute change for complete_no_chrg,cancelled,closed,pending-cl,failed-close statuses');END IF;
1515: --start of fix for 3389850
1516:
1517: begin
1518: IF ( p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE )
1514: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating attribute change for complete_no_chrg,cancelled,closed,pending-cl,failed-close statuses');END IF;
1515: --start of fix for 3389850
1516:
1517: begin
1518: IF ( p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE )
1519: AND (p_eam_wo_rec.status_type IN (5,7,12,14,15)) --if status is complete-no-chrg,cancelled,closed,pending-close and failed-close
1520: AND (p_eam_wo_rec.status_type=p_old_eam_wo_rec.status_type) --status is same
1521: AND ( p_eam_wo_rec.description<>p_old_eam_wo_rec.description
1522: OR p_eam_wo_rec.asset_number<>p_old_eam_wo_rec.asset_number
1618:
1619: --end of fix for 3389850
1620:
1621: -- status_type
1622: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating status_type . . . '); END IF;
1623:
1624: declare
1625:
1626: l_count number;
1631: ch_rel_par_canc exception;
1632:
1633: begin
1634:
1635: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE and
1636: p_eam_wo_rec.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.hold, wip_constants.draft)) then
1637:
1638: raise fnd_api.g_exc_unexpected_error;
1639:
1636: p_eam_wo_rec.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.hold, wip_constants.draft)) then
1637:
1638: raise fnd_api.g_exc_unexpected_error;
1639:
1640: elsif (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE) then
1641: if(p_eam_wo_rec.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,wip_constants.comp_nochrg, wip_constants.closed,
1642: wip_constants.hold, wip_constants.cancelled, wip_constants.pend_sched, wip_constants.draft)
1643: OR ( p_eam_wo_rec.status_type in (wip_constants.draft) and p_old_eam_wo_rec.status_type not in (wip_constants.draft))
1644: /* Added the check so that status will not be updated to status Draft */
1683: end;
1684:
1685:
1686: -- job_quantity
1687: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating job_quantity . . . '); END IF;
1688:
1689: begin
1690:
1691: if(p_eam_wo_rec.job_quantity <> 1) then
1716: end;
1717:
1718:
1719: -- primary_item_id (asset activity)
1720: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating asset_activity_id . . . '); END IF;
1721:
1722: declare
1723: l_eam_op_tbl EAM_PROCESS_WO_PUB.EAM_OP_TBL_TYPE;
1724: l_mat_req_exists number;
1742: where wip_entity_id = p_eam_wo_rec.wip_entity_id
1743: and organization_id = p_eam_wo_rec.organization_id;
1744:
1745: if nvl(p_eam_wo_rec.asset_activity_id,-99999) <> nvl(p_old_eam_wo_rec.asset_activity_id,-99999)
1746: and p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
1747: and (EAM_OP_UTILITY_PVT.NUM_OF_ROW(
1748: p_eam_op_tbl => l_eam_op_tbl
1749: ,p_wip_entity_id => p_eam_wo_rec.wip_entity_id
1750: ,p_organization_id => p_eam_wo_rec.organization_id) = false
1811:
1812:
1813: -- asset activity association
1814:
1815: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating asset_activity_association . . . '); END IF;
1816:
1817: declare
1818: l_start_date DATE;
1819: l_end_date DATE;
1902: end;
1903:
1904:
1905: -- wip_supply_type
1906: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating wip_supply_type . . . '); END IF;
1907:
1908: begin
1909:
1910: if(p_eam_wo_rec.wip_supply_type is not null and p_eam_wo_rec.wip_supply_type not in (wip_constants.push, wip_constants.bulk, wip_constants.based_on_bom)) then
1938: end;
1939:
1940:
1941: -- alternate_routing_designator
1942: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating alternate_routing_designator . . . '); END IF;
1943:
1944: begin
1945:
1946: if p_eam_wo_rec.alternate_routing_designator is not null then
1978:
1979: end;
1980:
1981: -- alternate_bom_designator
1982: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating alternate_bom_designator . . . '); END IF;
1983:
1984: begin
1985:
1986: if p_eam_wo_rec.alternate_bom_designator is not null then
2018:
2019: end;
2020:
2021: -- project_id
2022: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating project_id . . . '); END IF;
2023:
2024: declare
2025:
2026: l_min_date DATE;
2081: end;
2082:
2083:
2084: -- task_id
2085: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating task_id . . . '); END IF;
2086:
2087: declare
2088:
2089: l_min_date DATE;
2143:
2144:
2145: -- schedule_dates 0
2146:
2147: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating PM Suggested dates . . . '); END IF;
2148:
2149: begin
2150:
2151: if p_eam_wo_rec.pm_suggested_start_date is not null and
2180:
2181:
2182: -- schedule_dates 1
2183:
2184: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating due_date, requested_start_date . . . '); END IF;
2185:
2186: begin
2187:
2188: if p_eam_wo_rec.due_date is not null and
2217:
2218:
2219: -- schedule_dates 2
2220:
2221: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating due_date, requested_start_date . . . '); END IF;
2222:
2223: begin
2224:
2225: if p_eam_wo_rec.due_date is null and
2253: end;
2254:
2255:
2256: -- schedule_dates 3
2257: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating schedule_dates . . . '); END IF;
2258:
2259: declare
2260: l_rtg_count NUMBER;
2261: l_date_count NUMBER := 0;
2268: if p_eam_wo_rec.due_date is not null then
2269: l_date_count := l_date_count + 1;
2270: end if;
2271:
2272: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE and l_date_count = 0) then
2273: --all job creations must have at least one date
2274: raise fnd_api.g_exc_unexpected_error;
2275: end if;
2276:
2342: --end of fix for 3396136
2343:
2344:
2345: -- end_item_unit_number
2346: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating end_item_unit_number . . . '); END IF;
2347:
2348: declare
2349: is_unit_effective_item boolean;
2350: l_bom_item_id NUMBER;
2350: l_bom_item_id NUMBER;
2351: begin
2352:
2353: -- Unit number is required for unit effective assemblies.
2354: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE) then
2355:
2356: if(is_unit_effective_item and p_eam_wo_rec.end_item_unit_number is null) then
2357: fnd_message.set_name('PJM', 'UEFF-UNIT NUMBER REQUIRED');
2358: end if;
2400: end;
2401:
2402:
2403: -- class_code
2404: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating class_code . . . '); END IF;
2405:
2406: declare
2407: l_disable_date date;
2408: WO_CANT_CH_WIPACCT exception;
2482: end;
2483:
2484:
2485: -- bom_revision
2486: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating bom_revision . . . '); END IF;
2487:
2488: declare
2489: l_start_date DATE;
2490: begin
2522: end;
2523:
2524:
2525: -- routing_revision
2526: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating routing_revision . . . '); END IF;
2527:
2528: declare
2529: l_start_date DATE;
2530: l_count NUMBER;
2573: end;
2574:
2575:
2576: -- manual_rebuild_flag
2577: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating manual_rebuild_flag . . . '); END IF;
2578:
2579: begin
2580: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE) then
2581: if(p_eam_wo_rec.manual_rebuild_flag is not null and
2576: -- manual_rebuild_flag
2577: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating manual_rebuild_flag . . . '); END IF;
2578:
2579: begin
2580: if (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE) then
2581: if(p_eam_wo_rec.manual_rebuild_flag is not null and
2582: (p_eam_wo_rec.rebuild_item_id is null or
2583: p_eam_wo_rec.manual_rebuild_flag not in ('Y', 'N'))) then
2584: raise fnd_api.g_exc_unexpected_error;
2613:
2614: end;
2615:
2616: -- owning_department
2617: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating owing_department_id . . . '); END IF;
2618:
2619: declare
2620: l_job_date DATE;
2621: l_disable_date DATE;
2663:
2664: end;
2665:
2666: -- notification_required
2667: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating notification_required . . . '); END IF;
2668:
2669: begin
2670: if (p_eam_wo_rec.notification_required is not null and
2671: p_eam_wo_rec.notification_required not in ('Y', 'N')) then
2695:
2696: end;
2697:
2698: -- shutdown_type
2699: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating shutdown_type . . . '); END IF;
2700:
2701: begin
2702: if p_eam_wo_rec.shutdown_type is not null then
2703: select 1
2731:
2732: end;
2733:
2734: -- tagout_required
2735: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating tagout_required . . . '); END IF;
2736:
2737: begin
2738: if (p_eam_wo_rec.tagout_required is not null and
2739: p_eam_wo_rec.tagout_required not in ('Y', 'N')) then
2763:
2764: end;
2765:
2766: -- plan_maintenance
2767: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating plan_maintenance . . . '); END IF;
2768:
2769: begin
2770: if p_eam_wo_rec.plan_maintenance is not null and
2771: p_eam_wo_rec.plan_maintenance not in ('Y', 'N') then
2795:
2796: end;
2797:
2798: -- work_order_type
2799: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating work_order_type . . . '); END IF;
2800:
2801: begin
2802: if p_eam_wo_rec.work_order_type is not null then
2803: select 1
2831:
2832: end;
2833:
2834: -- activity_type
2835: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating activity_type . . . '); END IF;
2836:
2837: begin
2838: if p_eam_wo_rec.activity_type is not null then
2839: select 1
2867:
2868: end;
2869:
2870: -- activity_cause
2871: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating activity_cause . . . '); END IF;
2872:
2873: begin
2874: if p_eam_wo_rec.activity_cause is not null then
2875: select 1
2905:
2906:
2907:
2908: -- activity_source
2909: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating activity_source . . . '); END IF;
2910:
2911: begin
2912: if p_eam_wo_rec.activity_source is not null then
2913: select 1
2942: end;
2943:
2944:
2945: -- date_released
2946: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating accounting_period . . . '); END IF;
2947:
2948: --bug#4425025 - need to validate period only when releasing the first time.
2949: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Old Date_released is : '||nvl(to_char(p_old_eam_wo_rec.date_released),'NULL')); END IF;
2950:
2945: -- date_released
2946: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating accounting_period . . . '); END IF;
2947:
2948: --bug#4425025 - need to validate period only when releasing the first time.
2949: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Old Date_released is : '||nvl(to_char(p_old_eam_wo_rec.date_released),'NULL')); END IF;
2950:
2951: declare
2952: l_date_released_calc DATE;
2953: l_min_open_period_date DATE;
3012:
3013:
3014: -- maintenance_object_source
3015:
3016: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating maintenance_object_source . . . '); END IF;
3017:
3018: begin
3019:
3020: if (p_eam_wo_rec.maintenance_object_source is not null) then
3051:
3052:
3053: -- user_id
3054:
3055: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating user_id . . . '); END IF;
3056:
3057: begin
3058:
3059: if (p_eam_wo_rec.user_id is not null) then
3089:
3090:
3091: -- responsibility_id
3092:
3093: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating responsibility_id . . . '); END IF;
3094:
3095: begin
3096:
3097: if (p_eam_wo_rec.responsibility_id is not null) then
3126: end;
3127:
3128: -- user_defined_status_id
3129:
3130: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating user_defined_status_id . . . '); END IF;
3131:
3132: begin
3133:
3134: IF (p_eam_wo_rec.user_defined_status_id IS NOT NULL) AND
3166: end;
3167:
3168: -- material_issue_by_mo
3169:
3170: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating material_issue_by_mo flag . . . '); END IF;
3171:
3172: begin
3173:
3174: if (p_eam_wo_rec.material_issue_by_mo is null) then
3178: end if;
3179:
3180:
3181:
3182: if p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
3183: and p_eam_wo_rec.status_type not in (WIP_CONSTANTS.DRAFT,WIP_CONSTANTS.UNRELEASED) /* Bug no 3349197 */
3184: and not(p_eam_wo_rec.status_type=WIP_CONSTANTS.RELEASED and (p_old_eam_wo_rec.status_type IN (WIP_CONSTANTS.DRAFT,WIP_CONSTANTS.UNRELEASED,WIP_CONSTANTS.CANCELLED))) /*Bug No 3476156*/
3185: and p_eam_wo_rec.material_issue_by_mo <> p_old_eam_wo_rec.material_issue_by_mo then
3186:
3183: and p_eam_wo_rec.status_type not in (WIP_CONSTANTS.DRAFT,WIP_CONSTANTS.UNRELEASED) /* Bug no 3349197 */
3184: and not(p_eam_wo_rec.status_type=WIP_CONSTANTS.RELEASED and (p_old_eam_wo_rec.status_type IN (WIP_CONSTANTS.DRAFT,WIP_CONSTANTS.UNRELEASED,WIP_CONSTANTS.CANCELLED))) /*Bug No 3476156*/
3185: and p_eam_wo_rec.material_issue_by_mo <> p_old_eam_wo_rec.material_issue_by_mo then
3186:
3187: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN
3188: EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating material_issue_by_mo flag . .wip_entity_id' || p_eam_wo_rec.wip_entity_id);
3189: EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating material_issue_by_mo flag . .org_id' || p_eam_wo_rec.organization_id);
3190: END IF;
3191:
3251:
3252: end;
3253:
3254: -- Bug # 4709084 : FAILURE ANALYSIS : Check for failure_code_required..
3255: IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN
3256: EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating failure_code_required . . . ');
3257: END IF;
3258:
3259: if (p_eam_wo_rec.failure_code_required is not null and p_eam_wo_rec.failure_code_required not in ('Y', 'N')) then