[Home] [Help]
766: -- bug 4143943
767: CURSOR get_wo_status(c_workorder_id NUMBER)
768: IS
769: SELECT STATUS_CODE
770: FROM AHL_WORKORDERS
771: WHERE workorder_id = c_workorder_id;
772:
773: -- Balaji added for Release NR error
774: CURSOR get_wo_sch_sec(c_wip_entity_id IN NUMBER)
787: SELECT
788: TO_CHAR(actual_start_date, 'ss') actual_start_sec,
789: TO_CHAR(actual_end_date, 'ss') actual_end_sec
790: FROM
791: AHL_WORKORDERS
792: WHERE
793: WORKORDER_ID = c_workorder_id;
794:
795: -- FP bug# 7631453
1017: AWOS.ACTUAL_END_DATE,
1018: WIP.SCHEDULED_START_DATE,
1019: WIP.SCHEDULED_COMPLETION_DATE,
1020: WIP.OWNING_DEPARTMENT
1021: FROM AHL_WORKORDERS AWOS,
1022: WIP_DISCRETE_JOBS WIP
1023: WHERE AWOS.WIP_ENTITY_ID = WIP.WIP_ENTITY_ID
1024: AND AWOS.workorder_id = c_workorder_id;
1025:
1134: IS
1135: SELECT WDJ.scheduled_start_date,
1136: WDJ.scheduled_completion_date
1137: FROM WIP_DISCRETE_JOBS WDJ,
1138: AHL_WORKORDERS WO
1139: WHERE WDJ.wip_entity_id = WO.wip_entity_id
1140: AND WO.visit_task_id IS NULL
1141: AND WO.master_workorder_flag = 'Y'
1142: AND WO.visit_id = c_visit_id;
1145: CURSOR get_wo_status(c_workorder_id VARCHAR2)
1146: IS
1147: SELECT AWOS.status_code,
1148: FNDL.meaning
1149: FROM AHL_WORKORDERS AWOS,
1150: FND_LOOKUP_VALUES_VL FNDL
1151: WHERE AWOS.WORKORDER_ID = c_workorder_id
1152: AND FNDL.lookup_type = 'AHL_JOB_STATUS'
1153: AND FNDL.lookup_code(+) = AWOS.status_code;
1530: p_prd_workorder_rec.Visit_task_id<>FND_API.G_MISS_NUM ) THEN
1531:
1532: SELECT COUNT(*)
1533: INTO l_dummy_ctr
1534: FROM AHL_WORKORDERS
1535: WHERE visit_task_id=NVL(p_prd_workorder_rec.visit_task_id,0)
1536: AND LTRIM(RTRIM(status_code)) NOT IN ( G_JOB_STATUS_CANCELLED, G_JOB_STATUS_DELETED );
1537:
1538: IF l_dummy_ctr >0 THEN
1949: SELECT
1950: nvl(vtsk.quantity, 1)
1951: FROM
1952: ahl_visit_tasks_b vtsk,
1953: ahl_workorders awo
1954: WHERE
1955: vtsk.visit_task_id = awo.visit_task_id AND
1956: awo.workorder_id = p_workorder_id;
1957:
2163: SELECT
2164: nvl(vtsk.quantity, 1)
2165: FROM
2166: ahl_visit_tasks_b vtsk,
2167: ahl_workorders awo
2168: WHERE
2169: vtsk.visit_task_id = awo.visit_task_id AND
2170: awo.workorder_id = p_workorder_id;
2171:
2448: AWO.operation_sequence_num,
2449: WIPO.first_unit_start_date scheduled_start_date,
2450: WIPO.last_unit_completion_date scheduled_end_date
2451:
2452: FROM AHL_WORKORDERS AHLW,
2453: WIP_OPERATIONS WIPO,
2454: AHL_WORKORDER_OPERATIONS AWO
2455:
2456: WHERE AHLW.workorder_id = c_workorder_id
2719: -- Cursor to check whether the workorder being processed is in allowable status
2720: CURSOR check_wo_status_cur (c_workorder_id IN NUMBER)
2721: IS
2722: SELECT 1
2723: FROM AHL_WORKORDERS
2724: WHERE workorder_id = c_workorder_id
2725: AND status_code = G_JOB_STATUS_DRAFT;
2726: --
2727:
3340: l_sr_summary VARCHAR2(240);
3341: l_sr_type VARCHAR2(90);
3342: BEGIN
3343:
3344: SELECT AHL_WORKORDERS_S.NEXTVAL
3345: INTO p_x_prd_workorder_rec.WORKORDER_ID
3346: FROM DUAL;
3347:
3348: IF p_x_prd_workorder_rec.MASTER_WORKORDER_FLAG = 'Y' AND p_x_prd_workorder_rec.VISIT_TASK_ID IS NULL THEN
3828: --Cursor to get instance routing attached to workorder instance.
3829: CURSOR Get_Wo_Inst_Rtng(p_workorder_id IN NUMBER) IS
3830: SELECT RTNG.INST_ROUTING_CODE INSTANCE_RTNG
3831: FROM
3832: AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK, AHL_VISIT_INST_ROUTINGS RTNG
3833: WHERE
3834: WO.WORKORDER_ID = p_workorder_id
3835: AND WO.VISIT_TASK_ID = TSK.VISIT_TASK_ID
3836: AND TSK.INSTANCE_ID IS NOT NULL
3899: p_x_prd_workorder_rec =>p_x_prd_workorder_rec
3900: );
3901:
3902: IF ( G_DEBUG = 'Y' ) THEN
3903: AHL_DEBUG_PUB.debug( l_api_name || ' - Before Insert into AHL_WORKORDERS' );
3904: END IF;
3905:
3906: -- Begin OGMA Issue # 105 - Balaji
3907: -- update VWP planned task quantity current instance quantity
3930: END IF;
3931: -- End OGMA Issue # 105 - Balaji
3932: --sukhwsin: VCP Integration Requirements - code changes - starts
3933: --Check if task contains any item group based requirement. If yes then populate ITEM_ALTERNATES_EXIST column
3934: --in ahl_workorders table to 'Y'
3935: l_alt_mat_req_count := 0;
3936: l_alt_item_exist := NULL;
3937: OPEN Get_Alt_Mat_Requirment_Count(p_x_prd_workorder_rec.VISIT_TASK_ID);
3938: FETCH Get_Alt_Mat_Requirment_Count INTO l_alt_mat_req_count;
3948: x_uc_header_id => l_uc_header_id,
3949: x_nha_uc_header_id => l_nha_uc_header_id,
3950: x_maintenance_type_code => l_maintenance_type_code
3951: );
3952: INSERT INTO AHL_WORKORDERS
3953: (
3954: WORKORDER_ID,
3955: OBJECT_VERSION_NUMBER,
3956: LAST_UPDATE_DATE,
4298: RAISE FND_API.G_EXC_ERROR;
4299: ELSE
4300:
4301: IF ( G_DEBUG = 'Y' ) THEN
4302: AHL_DEBUG_PUB.debug( l_api_name || ' - Before Update AHL_WORKORDERS with wip_entity_id' );
4303: END IF;
4304:
4305: UPDATE AHL_WORKORDERS
4306: SET wip_entity_id = p_x_prd_workorder_rec.wip_entity_id
4301: IF ( G_DEBUG = 'Y' ) THEN
4302: AHL_DEBUG_PUB.debug( l_api_name || ' - Before Update AHL_WORKORDERS with wip_entity_id' );
4303: END IF;
4304:
4305: UPDATE AHL_WORKORDERS
4306: SET wip_entity_id = p_x_prd_workorder_rec.wip_entity_id
4307: WHERE workorder_id = p_x_prd_workorder_rec.workorder_id;
4308: END IF;
4309:
4416: WIPJ.scheduled_start_date,
4417: WIPJ.scheduled_completion_date scheduled_end_date,
4418: WO.actual_start_date,
4419: WO.actual_end_date
4420: FROM AHL_WORKORDERS WO,
4421: WIP_SCHED_RELATIONSHIPS WOR,
4422: WIP_DISCRETE_JOBS wipj
4423: WHERE
4424: WIPJ.wip_entity_id = WO.wip_entity_id
4438: WO.actual_start_date actual_start_date,
4439: WO.actual_end_date actual_end_date,
4440: WO.status_code status_code
4441: FROM WIP_DISCRETE_JOBS WDJ,
4442: AHL_WORKORDERS WO
4443: WHERE WDJ.wip_entity_id = WO.wip_entity_id
4444: AND WO.status_code <> G_JOB_STATUS_DELETED
4445: AND WO.wip_entity_id in
4446: (
4459: IS
4460: SELECT AWOS.status_code,
4461: AWOS.workorder_name,
4462: FNDL.meaning
4463: FROM AHL_WORKORDERS AWOS,
4464: FND_LOOKUP_VALUES_VL FNDL
4465: WHERE AWOS.WORKORDER_ID = c_workorder_id
4466: AND FNDL.lookup_type = 'AHL_JOB_STATUS'
4467: AND FNDL.lookup_code(+) = AWOS.status_code;
4485:
4486: -- Fix for bug# 5347560.
4487: CURSOR chk_inst_in_job (p_workorder_id IN NUMBER) IS
4488: SELECT 'x'
4489: FROM CSI_ITEM_INSTANCES CII, AHL_WORKORDERS AWO
4490: WHERE CII.WIP_JOB_ID = AWO.WIP_ENTITY_ID
4491: AND AWO.workorder_id = p_workorder_id
4492: AND ACTIVE_START_DATE <= SYSDATE
4493: AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE))
4502: IS
4503: SELECT
4504: visit_task_id
4505: FROM
4506: AHL_WORKORDERS
4507: WHERE
4508: workorder_id = p_wo_id;
4509:
4510: -- Bug # 8433227 (FP for Bug # 7453393) -- start
4516: IS
4517: SELECT WDJ.scheduled_start_date,
4518: WDJ.scheduled_completion_date
4519: FROM WIP_DISCRETE_JOBS WDJ,
4520: AHL_WORKORDERS WO
4521: WHERE WDJ.wip_entity_id = WO.wip_entity_id
4522: AND WO.visit_task_id IS NULL
4523: AND WO.master_workorder_flag = 'Y'
4524: AND WO.visit_id = c_visit_id;
4539: IS
4540: SELECT
4541: WO.master_workorder_flag
4542: FROM
4543: AHL_WORKORDERS WO,
4544: WIP_DISCRETE_JOBS WDJ
4545: WHERE WDJ.wip_entity_id = WO.wip_entity_id
4546: AND WDJ.wip_entity_id = p_wip_entity_id;
4547:
4580:
4581: l_employee_id NUMBER;
4582: l_operation_seq_num NUMBER;
4583: l_resource_seq_num NUMBER;
4584: l_workorder_name ahl_workorders.workorder_name%TYPE;
4585:
4586: L_X_PRD_WORKORDER_REC AHL_PRD_WORKORDER_PVT.PRD_WORKORDER_REC;
4587: L_OPERATION_TBL AHL_PRD_OPERATIONS_PVT.PRD_OPERATION_TBL;
4588: L_MATERIAL_REQ_TBL AHL_PP_MATERIALS_PVT.REQ_MATERIAL_TBL_TYPE;
4635: IS
4636: SELECT
4637: wo.status_code, wdj.firm_planned_flag
4638: FROM
4639: ahl_workorders wo, wip_discrete_jobs wdj
4640: WHERE
4641: wo.wip_entity_id = wdj.wip_entity_id
4642: and wo.workorder_id = p_workorder_id;
4643:
4675: SELECT
4676: WDJ.firm_planned_flag
4677: FROM
4678: WIP_DISCRETE_JOBS WDJ,
4679: AHL_WORKORDERS AWO
4680: WHERE
4681: AWO.wip_entity_id = WDJ.wip_entity_id AND
4682: AWO.workorder_id = c_workorder_id;
4683:
4704:
4705: -- JKJain, NR Analysis and Forecasting
4706: CURSOR get_uc_fleet_mntnc_csr(p_wo_id NUMBER) IS
4707: SELECT uc_header_id ,nha_uc_header_id,fleet_header_id,maintenance_type_code
4708: from AHL_WORKORDERS
4709: where workorder_id = p_wo_id;
4710: l_fleet_header_id NUMBER := NULL;
4711: l_maintenance_type_code VARCHAR2(30) := NULL;
4712: l_uc_header_id NUMBER := NULL;
4738: WDJ.FIRM_PLANNED_FLAG,
4739: WDJ.SCHEDULED_START_DATE,
4740: WDJ.SCHEDULED_COMPLETION_DATE
4741: FROM AHL_MR_HEADERS_B AMH,
4742: AHL_WORKORDERS AW,
4743: AHL_VISITS_B AV,
4744: AHL_VISIT_TASKS_B AVT,
4745: wip_discrete_jobs wdj
4746: WHERE AW.VISIT_TASK_ID = AVT.VISIT_TASK_ID
4757: IS
4758: SELECT ASM.AOG_FLAG
4759: FROM AHL_SCHEDULE_MATERIALS ASM,
4760: AHL_VISIT_TASKS_B AVT,
4761: AHL_WORKORDERS AW
4762: WHERE ASM.VISIT_TASK_ID = AVT.VISIT_TASK_ID
4763: AND AW.VISIT_TASK_ID = AVT.VISIT_TASK_ID
4764: AND ASM.AOG_FLAG = 'Y'
4765: AND AW.WORKORDER_ID = P_WORKORDER_ID;
4768: CURSOR GET_SRC_RES_ID_CSR(P_WORKORDER_ID NUMBER)
4769: IS
4770: SELECT mtl.reservation_id RESERVATION_ID
4771: FROM mtl_reservations mtl ,
4772: ahl_workorders awo
4773: WHERE mtl.supply_source_header_id = awo.wip_entity_id
4774: AND awo.workorder_id = P_WORKORDER_ID;
4775: -- ARUNJK included cursor for cancel reservation for WO-WO supply WO Cancel Marshalling2 Ends
4776:
5032: p_x_prd_workorder_rec.HOLD_REASON_CODE := NULL;
5033: END IF;
5034:
5035: IF ( G_DEBUG = 'Y' ) THEN
5036: AHL_DEBUG_PUB.debug( l_api_name || ' - Before Updating AHL_WORKORDERS' );
5037: END IF;
5038:
5039: -- Begin OGMA Issue # 105 - Balaji
5040: -- update VWP planned task quantity current instance quantity
5139:
5140: -- end check if any material requirement for WO is AOG, then WO cant me modified to non AOG
5141:
5142:
5143: UPDATE AHL_WORKORDERS SET
5144: OBJECT_VERSION_NUMBER =p_x_prd_workorder_rec.OBJECT_VERSION_NUMBER + 1,
5145: LAST_UPDATE_DATE =NVL(p_x_prd_workorder_rec.LAST_UPDATE_DATE,SYSDATE),
5146: LAST_UPDATED_BY =NVL(p_x_prd_workorder_rec.LAST_UPDATED_BY,FND_GLOBAL.user_id),
5147: LAST_UPDATE_LOGIN =NVL(p_x_prd_workorder_rec.LAST_UPDATE_LOGIN,FND_GLOBAL.user_id),
6637: FOR i IN p_x_prd_workorder_tbl.FIRST..p_x_prd_workorder_tbl.LAST LOOP
6638:
6639: IF ( p_x_prd_workorder_tbl(i).dml_operation = 'C' ) THEN
6640: IF ( G_DEBUG = 'Y' ) THEN
6641: AHL_DEBUG_PUB.debug( l_api_name || 'Updating AHL_WORKORDERS with wip_entity_id for Workorder ' || i );
6642: END IF;
6643:
6644: UPDATE AHL_WORKORDERS
6645: SET wip_entity_id = l_eam_wo_tbl(i).wip_entity_id,
6640: IF ( G_DEBUG = 'Y' ) THEN
6641: AHL_DEBUG_PUB.debug( l_api_name || 'Updating AHL_WORKORDERS with wip_entity_id for Workorder ' || i );
6642: END IF;
6643:
6644: UPDATE AHL_WORKORDERS
6645: SET wip_entity_id = l_eam_wo_tbl(i).wip_entity_id,
6646: object_version_number = p_x_prd_workorder_tbl(i).object_version_number + 1,
6647: last_update_date = SYSDATE,
6648: last_updated_by = Fnd_Global.USER_ID,
6690: END IF;
6691:
6692: ELSIF ( p_x_prd_workorder_tbl(i).dml_operation = 'U' ) THEN
6693: IF ( G_DEBUG = 'Y' ) THEN
6694: AHL_DEBUG_PUB.debug( l_api_name || 'Updating AHL_WORKORDERS with status code for Workorder ' || i );
6695: END IF;
6696: --
6697: --sukhwsin::VEE - enhancement for insertion of wo status change - starts
6698: ADD_WO_OPER_TURNOVER_NOTES
6707: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6708: RAISE FND_API.G_EXC_ERROR;
6709: END IF;
6710: --sukhwsin::VEE - enhancement for insertion of wo status change - ends
6711: UPDATE AHL_WORKORDERS
6712: SET status_code = l_eam_wo_tbl(i).status_type,
6713: object_version_number = p_x_prd_workorder_tbl(i).object_version_number + 1,
6714: last_update_date = SYSDATE,
6715: last_updated_by = Fnd_Global.USER_ID,
6873: AVB.department_id,
6874: BOMD.description department_name
6875:
6876: FROM AHL_VISIT_TASKS_B AVTB,
6877: AHL_WORKORDERS AHLW,
6878: AHL_VISITS_B AVB,
6879: BOM_DEPARTMENTS BOMD
6880:
6881: WHERE ( AVTB.stage_id = c_stage_id OR
6883: AND AVTB.task_type_code = 'STAGE'
6884: AND AVTB.visit_task_id = AHLW.visit_task_id
6885: AND AVTB.visit_id = AVB.visit_id
6886: AND AVB.department_id = BOMD.department_id;
6887: -- clauses added in order to use index AHL_WORKORDERS_N3 (visit_task_id, master_workorder_flag, status_code)
6888: -- MANESING::Bug 14137483, commented out the following 2 clauses so query can return the result even when Stage Workorder is in Deleted status
6889: -- AND AHLW.master_workorder_flag = 'N'
6890: -- AND AHLW.status_code = G_JOB_STATUS_DRAFT;
6891: --
7418: object_version_number,
7419: status_code,
7420: wip_entity_id,
7421: workorder_name
7422: FROM AHL_WORKORDERS
7423: WHERE visit_id = c_visit_id
7424: AND status_code <> G_JOB_STATUS_DELETED
7425: AND visit_task_id IS NULL;
7426: -- To get a workorder from the wp_entity_id
7431: WO.wip_entity_id wip_entity_id,
7432: WO.status_code status_code,
7433: WO.master_workorder_flag,
7434: WO.workorder_name
7435: FROM AHL_WORKORDERS WO
7436: WHERE wip_entity_id = c_wip_entity_id
7437: AND STATUS_CODE <> G_JOB_STATUS_DELETED;
7438:
7439: -- To get the Child Workorders
7470: WO.status_code status_code,
7471: WO.wip_entity_id wip_entity_id,
7472: WO.workorder_name workorder_name,
7473: WO.master_workorder_flag master_workorder_flag
7474: FROM AHL_WORKORDERS WO,
7475: AHL_VISIT_TASKS_B VT
7476: WHERE WO.status_code <> G_JOB_STATUS_DELETED
7477: AND WO.visit_task_id = VT.visit_task_id
7478: AND VT.task_type_code IN ( 'SUMMARY', 'UNASSOCIATED' )
7484: object_version_number,
7485: status_code,
7486: wip_entity_id,
7487: workorder_name
7488: FROM AHL_WORKORDERS
7489: WHERE workorder_id =p_workorder_id;
7490:
7491: BEGIN
7492: SAVEPOINT release_visit_jobs_PVT;
7939:
7940: CURSOR get_visit_child_wos( c_visit_id NUMBER ) IS
7941: SELECT workorder_name,
7942: wip_entity_id
7943: FROM AHL_WORKORDERS
7944: WHERE visit_id = c_visit_id
7945: AND status_code NOT IN ( G_JOB_STATUS_COMPLETE,
7946: G_JOB_STATUS_COMPLETE_NC,
7947: G_JOB_STATUS_CANCELLED,
7949: G_JOB_STATUS_DELETED );
7950:
7951: CURSOR get_visit_dependencies( c_visit_id NUMBER, c_wip_entity_id NUMBER ) IS
7952: SELECT WO.workorder_name workorder_name
7953: FROM AHL_WORKORDERS WO,
7954: WIP_SCHED_RELATIONSHIPS REL
7955: WHERE WO.wip_entity_id = REL.parent_object_id
7956: AND WO.visit_id <> c_visit_id
7957: AND WO.status_code NOT IN ( G_JOB_STATUS_COMPLETE,
7966:
7967: CURSOR get_ue_mwo( c_unit_effectivity_id NUMBER ) IS
7968: SELECT WO.workorder_name workorder_name,
7969: WO.wip_entity_id wip_entity_id
7970: FROM AHL_WORKORDERS WO,
7971: AHL_VISIT_TASKS_B VT
7972: WHERE WO.visit_task_id = VT.visit_task_id
7973: AND WO.status_code NOT IN ( G_JOB_STATUS_COMPLETE,
7974: G_JOB_STATUS_COMPLETE_NC,
7981: -- Fix for connect by issue
7982: CURSOR get_child_wos( c_wip_entity_id NUMBER ) IS
7983: SELECT WO.wip_entity_id wip_entity_id,
7984: WO.workorder_name workorder_name
7985: FROM AHL_WORKORDERS WO
7986: WHERE WO.status_code NOT IN ( G_JOB_STATUS_COMPLETE,
7987: G_JOB_STATUS_COMPLETE_NC,
7988: G_JOB_STATUS_CANCELLED,
7989: G_JOB_STATUS_CLOSED,
8009:
8010: CURSOR get_wo_dependencies( c_wip_entity_id NUMBER ) IS
8011: SELECT WO.workorder_name workorder_name,
8012: WO.wip_entity_id wip_entity_id
8013: FROM AHL_WORKORDERS WO,
8014: WIP_SCHED_RELATIONSHIPS REL
8015: WHERE WO.wip_entity_id = REL.parent_object_id
8016: AND WO.status_code NOT IN ( G_JOB_STATUS_COMPLETE,
8017: G_JOB_STATUS_COMPLETE_NC,
8025:
8026: CURSOR get_wo( c_workorder_id NUMBER ) IS
8027: SELECT workorder_name,
8028: wip_entity_id
8029: FROM AHL_WORKORDERS
8030: WHERE workorder_id =p_workorder_id
8031: AND status_code NOT IN ( G_JOB_STATUS_COMPLETE,
8032: G_JOB_STATUS_COMPLETE_NC,
8033: G_JOB_STATUS_CANCELLED,
8257:
8258: -- Get Child WOs which are not cancelled
8259: CURSOR get_child_wos( c_wip_entity_id NUMBER ) IS
8260: SELECT WO.workorder_id workorder_id
8261: FROM AHL_WORKORDERS WO,
8262: WIP_SCHED_RELATIONSHIPS REL
8263: WHERE WO.status_code NOT IN ( G_JOB_STATUS_CANCELLED,
8264: G_JOB_STATUS_DELETED )
8265: AND WO.wip_entity_id = REL.child_object_id
8371: awo.status_code,
8372: awo.wip_entity_id,
8373: awo.workorder_name,
8374: vst.visit_number
8375: FROM AHL_WORKORDERS AWO, AHL_VISITS_B VST
8376: WHERE awo.visit_id = c_visit_id
8377: AND awo.visit_id = vst.visit_id
8378: AND awo.status_code NOT IN (G_JOB_STATUS_DELETED,G_JOB_STATUS_CANCELLED)
8379: AND awo.visit_task_id IS NULL;
8385: WO.status_code status_code,
8386: WO.wip_entity_id wip_entity_id,
8387: WO.workorder_name workorder_name,
8388: WO.master_workorder_flag master_workorder_flag
8389: FROM AHL_WORKORDERS WO,
8390: AHL_VISIT_TASKS_B VT
8391: WHERE WO.status_code <> G_JOB_STATUS_DELETED
8392: AND WO.visit_task_id = VT.visit_task_id
8393: AND VT.task_type_code IN ( 'SUMMARY', 'UNASSOCIATED' )
8396: -- bug 4094884
8397: -- To get the UE Workorder
8398: CURSOR get_ue_wo( c_unit_effectivity_id NUMBER ) IS
8399: SELECT WO.wip_entity_id wip_entity_id
8400: FROM AHL_WORKORDERS WO,
8401: AHL_VISIT_TASKS_B VT
8402: WHERE WO.status_code <> G_JOB_STATUS_DELETED
8403: AND WO.visit_task_id = VT.visit_task_id
8404: AND VT.task_type_code NOT IN ('SUMMARY')
8411: object_version_number,
8412: status_code,
8413: wip_entity_id,
8414: workorder_name
8415: FROM AHL_WORKORDERS
8416: WHERE workorder_id =p_workorder_id;
8417:
8418: -- To get the Child Workorders of a Master Workorder
8419: -- Fix for connect by issue
8425: WO.status_code status_code,
8426: WO.wip_entity_id wip_entity_id,
8427: WO.master_workorder_flag,
8428: WO.workorder_name
8429: FROM AHL_WORKORDERS WO
8430: WHERE WO.wip_entity_id = c_wip_entity_id;
8431: /* bug 5104519 - end */
8432: -- To get the Child Workorders of a Master Workorder
8433: CURSOR get_child_wos( c_wip_entity_id NUMBER ) IS
8494: SELECT UE.unit_effectivity_id unit_effectivity_id,
8495: UE.object_version_number object_version_number
8496: FROM AHL_UNIT_EFFECTIVITIES_B UE,
8497: AHL_VISIT_TASKS_B VT,
8498: AHL_WORKORDERS WO
8499: WHERE UE.unit_effectivity_id = VT.unit_effectivity_id
8500: AND UE.manually_planned_flag = 'Y'
8501: -- Check added by balaji by balaji for bug # 4095002
8502: -- As per the update in the bug, for Manually planned UEs of type SR
8529: -- that are not master workorders
8530: CURSOR get_visit_wos(c_visit_id NUMBER)
8531: IS
8532: SELECT wip_entity_id
8533: FROM AHL_WORKORDERS
8534: WHERE visit_id = c_visit_id
8535: AND master_workorder_flag <> 'Y';
8536:
8537: -- to see if a workorder is a top level workorder
8551: IS
8552: SELECT REL.sched_relationship_id,
8553: REL.parent_object_id,
8554: REL.child_object_id
8555: FROM --AHL_WORKORDERS WO,
8556: WIP_SCHED_RELATIONSHIPS REL
8557: WHERE --WO.wip_entity_id = REL.child_object_id
8558: REL.parent_object_type_id = 1
8559: AND REL.child_object_type_id = 1
8619: -- cursor to check if all workorders in a visit are cancelled.
8620: CURSOR chk_cmplt_wo_exists(c_wip_entity_id NUMBER )
8621: IS
8622: SELECT 'x'
8623: FROM AHL_WORKORDERS AWO, WIP_DISCRETE_JOBS WDJ
8624: WHERE awo.wip_entity_id = wdj.wip_entity_id
8625: AND wdj.date_completed IS NOT NULL
8626: --AND master_workorder_flag = 'N'
8627: --AND status_code NOT IN ('7', '22', '12')
9925: WIP.WIP_ENTITY_ID,
9926: AWO.STATUS_CODE,
9927: WIP.SCHEDULED_START_DATE,
9928: WIP.SCHEDULED_COMPLETION_DATE
9929: FROM AHL_WORKORDERS AWO,
9930: WIP_DISCRETE_JOBS WIP
9931: WHERE AWO.VISIT_ID=c_visit_id
9932: AND AWO.VISIT_TASK_ID IS NULL
9933: AND AWO.MASTER_WORKORDER_FLAG = 'Y'
9955: CWO.status_code status_code,
9956: WIP.SCHEDULED_START_DATE,
9957: WIP.SCHEDULED_COMPLETION_DATE SCHEDULED_END_DATE,
9958: level
9959: FROM AHL_WORKORDERS CWO,
9960: WIP_SCHED_RELATIONSHIPS REL,
9961: WIP_DISCRETE_JOBS WIP
9962: WHERE CWO.wip_entity_id = REL.parent_object_id
9963: AND CWO.wip_entity_id = WIP.WIP_ENTITY_ID
9966: AND CWO.master_workorder_flag = 'Y'
9967: AND REL.parent_object_type_id = 1
9968: AND REL.child_object_type_id = 1
9969: START WITH REL.child_object_id IN
9970: (select wos.wip_entity_id from ahl_workorders wos, WIP_DISCRETE_JOBS WIPS
9971: where wos.wip_entity_id = wips.wip_entity_id
9972: and wips.date_completed IS NOT NULL
9973: and exists (Select 'X' from WIP_DISCRETE_JOBS WIPSP,WIP_SCHED_RELATIONSHIPS RELP
9974: where RELP.child_object_id = wips.wip_entity_id
9991: FROM
9992: (select cwo.workorder_id, cwo.visit_id,
9993: cwo.object_version_number,
9994: cwo.wip_entity_id , cwo.status_code status_code
9995: from ahl_workorders cwo
9996: WHERE cwo.visit_id = c_visit_id
9997: and cwo.status_code NOT IN ('22', '7', '4', '5', '12')
9998: AND cwo.master_workorder_flag = 'Y') cwo,
9999: wip_sched_relationships rel,
10005: AND rel.child_object_type_id = 1
10006: START WITH rel.child_object_id IN (
10007: SELECT distinct wos.wip_entity_id
10008: FROM wip_discrete_jobs wips, wip_discrete_jobs wipsp,
10009: wip_sched_relationships relp, ahl_workorders wos
10010: WHERE wos.visit_id = c_visit_id
10011: AND wos.wip_entity_id = wips.wip_entity_id
10012: AND wips.date_completed IS NOT NULL
10013: AND relp.child_object_id = wips.wip_entity_id
10031: CWO.wip_entity_id wip_entity_id,
10032: CWO.status_code status_code,
10033: WIP.SCHEDULED_START_DATE,
10034: WIP.SCHEDULED_COMPLETION_DATE
10035: FROM AHL_WORKORDERS CWO,
10036: WIP_SCHED_RELATIONSHIPS REL,
10037: WIP_DISCRETE_JOBS WIP
10038: WHERE CWO.wip_entity_id = REL.parent_object_id
10039: AND CWO.wip_entity_id = WIP.WIP_ENTITY_ID
10042: AND CWO.master_workorder_flag = 'Y'
10043: AND REL.parent_object_type_id = 1
10044: AND REL.child_object_type_id = 1
10045: START WITH REL.child_object_id IN
10046: (select wos.wip_entity_id from ahl_workorders wos, WIP_DISCRETE_JOBS WIPS
10047: where wos.wip_entity_id = wips.wip_entity_id
10048: and wips.date_completed IS NOT NULL
10049: and wos.visit_id = c_visit_id)
10050: AND REL.relationship_type = 1
10056: CURSOR get_minmax_comp_schedule_dates(c_visit_id IN NUMBER)
10057: IS
10058: SELECT min(scheduled_start_date) max_start_date,
10059: max(scheduled_completion_date) min_end_date
10060: FROM wip_discrete_jobs wips, ahl_workorders wos
10061: WHERE wips.wip_entity_id = wos.wip_entity_id
10062: and wips.date_completed IS NOT NULL
10063: and wos.visit_id = c_visit_id
10064: group by wos.visit_id;
10806: DECODE(WO.MASTER_WORKORDER_FLAG,'Y',to_number(NULL),VST.PROJECT_ID) PROJECT_ID,
10807: DECODE(WO.MASTER_WORKORDER_FLAG,'Y',to_number(NULL),VTS.PROJECT_TASK_ID) PROJECT_TASK_ID,
10808: WDJ.PRIORITY PRIORITY
10809: FROM
10810: AHL_WORKORDERS WO,
10811: AHL_VISITS_B VST,
10812: AHL_VISIT_TASKS_B VTS,
10813: WIP_DISCRETE_JOBS WDJ,
10814: CSI_ITEM_INSTANCES CSI
10882: IS
10883: SELECT
10884: AWO.wip_entity_id
10885: FROM
10886: ahl_workorders AWO
10887: WHERE
10888: awo.workorder_id = c_workorder_id;
10889:
10890: CURSOR get_parent_workorder( c_child_wip_entity_id NUMBER )
10895: WO.visit_task_id,
10896: WO.STATUS_CODE,
10897: WDJ.SCHEDULED_START_DATE SCHEDULED_START_DATE,
10898: WDJ.scheduled_completion_date scheduled_end_date
10899: FROM AHL_WORKORDERS WO,WIP_DISCRETE_JOBS WDJ,
10900: WIP_SCHED_RELATIONSHIPS WOR
10901: WHERE WO.WIP_ENTITY_ID = WOR.PARENT_OBJECT_ID
10902: AND WDJ.wip_entity_id = WO.wip_entity_id
10903: AND WO.master_workorder_flag = 'Y'
10918: WO.actual_start_date actual_start_date,
10919: WO.actual_end_date actual_end_date,
10920: WO.status_code status_code
10921: FROM WIP_DISCRETE_JOBS WDJ,
10922: AHL_WORKORDERS WO
10923: WHERE WDJ.wip_entity_id = WO.wip_entity_id
10924: AND WO.status_code <> '22'
10925: AND WO.wip_entity_id in
10926: (
11108: --added status '17' for USAF VWPE Enh. Visit can now contain a mix of draft and released/unreleased work orders.
11109: CURSOR wo_status_csr( c_visit_id NUMBER ) IS
11110: select 'x'
11111: from dual
11112: where exists (select 'x' from ahl_workorders
11113: where visit_id = c_visit_id
11114: and status_code NOT IN ('1','3','7', '17')
11115: );
11116:
11118: CURSOR op_complete_csr( c_visit_id NUMBER ) IS
11119: select 'x'
11120: from dual
11121: where exists (select 'x'
11122: from ahl_workorders awo, ahl_workorder_operations awop
11123: where awo.workorder_id = awop.workorder_id
11124: and awo.visit_id = c_visit_id
11125: and awop.status_code = '1'
11126: );
11129: CURSOR part_chg_txn_csr( c_visit_id NUMBER ) IS
11130: select 'x'
11131: from dual
11132: where exists (select 'x'
11133: from ahl_workorders awo, ahl_workorder_operations awop, ahl_part_changes apc
11134: where awo.workorder_id = awop.workorder_id
11135: and awop.workorder_operation_id = apc.workorder_operation_id
11136: and awo.visit_id = c_visit_id
11137: );
11141: CURSOR tracked_material_csr( c_visit_id NUMBER ) IS
11142: select 'x'
11143: from dual
11144: where exists (select 'x'
11145: from ahl_workorders awo, csi_item_instances cii
11146: where awo.wip_entity_id = cii.wip_job_id
11147: and cii.location_type_code = 'WIP'
11148: and cii.ACTIVE_START_DATE <= SYSDATE
11149: and ((cii.ACTIVE_END_DATE IS NULL) OR (cii.ACTIVE_END_DATE >= SYSDATE))
11155: CURSOR resource_txn_csr( c_visit_id NUMBER ) IS
11156: select 'x'
11157: from dual
11158: where exists (select 'x'
11159: from ahl_workorders awo, wip_transactions wipt
11160: where awo.wip_entity_id = wipt.wip_entity_id
11161: and awo.visit_id = c_visit_id
11162: UNION ALL
11163: select 'x'
11160: where awo.wip_entity_id = wipt.wip_entity_id
11161: and awo.visit_id = c_visit_id
11162: UNION ALL
11163: select 'x'
11164: from ahl_workorders awos, wip_COST_TXN_INTERFACE wict
11165: where awos.wip_entity_id = wict.wip_entity_id
11166: and awos.visit_id = c_visit_id
11167: and process_status IN (1,3)
11168: );
11174: WHERE EXISTS
11175: ( SELECT 'X'
11176: FROM csi_transactions cst ,
11177: csi_txn_types cstrntyp,
11178: ahl_workorders awo
11179: WHERE cstrntyp.source_transaction_type = 'ITEM_SERIAL_CHANGE'
11180: AND cst.transaction_type_id = cstrntyp.transaction_type_id
11181: AND cst.source_line_ref = 'AHL_PRD_WO'
11182: AND cst.source_line_ref_id = awo.workorder_id
11186: --allow cancelation if material have not been returned.
11187: -- check issue txns with return txns.
11188: CURSOR wo_material_csr(c_visit_id NUMBER ) IS
11189: SELECT INVENTORY_ITEM_ID, REVISION, LOT_NUMBER, SERIAL_NUMBER, sum(quantity) quantity
11190: FROM ahl_workorder_mtl_txns amt, ahl_workorder_operations awo, ahl_workorders aw
11191: WHERE aw.visit_id = c_visit_id
11192: and aw.workorder_id = awo.workorder_id
11193: and awo.workorder_operation_id = amt.workorder_operation_id
11194: and TRANSACTION_TYPE_ID = 35
11194: and TRANSACTION_TYPE_ID = 35
11195: group by INVENTORY_ITEM_ID, REVISION, LOT_NUMBER, SERIAL_NUMBER
11196: MINUS
11197: SELECT INVENTORY_ITEM_ID, REVISION, LOT_NUMBER, SERIAL_NUMBER, sum(quantity) quantity
11198: FROM ahl_workorder_mtl_txns amt, ahl_workorder_operations awo, ahl_workorders aw
11199: WHERE aw.visit_id = c_visit_id
11200: and aw.workorder_id = awo.workorder_id
11201: and awo.workorder_operation_id = amt.workorder_operation_id
11202: and TRANSACTION_TYPE_ID = 43
11334: ORGANIZATION_ID,
11335: CLASS_CODE,
11336: DATE_CLOSED,
11337: DATE_COMPLETED
11338: FROM AHL_WORKORDERS_V
11339: WHERE WORKORDER_ID = c_workorder_id
11340: FOR UPDATE OF JOB_STATUS_CODE;
11341:
11342: CURSOR get_closed_mwo_csr(c_wip_entity_id IN NUMBER)IS
11347: WDJ.CLASS_CODE class_code,
11348: WDJ.DATE_CLOSED,
11349: --sukhwsin::VEE Changes- included workorder_id
11350: WO.workorder_id
11351: FROM AHL_WORKORDERS WO,
11352: WIP_SCHED_RELATIONSHIPS WOR,
11353: WIP_DISCRETE_JOBS WDJ
11354: WHERE WO.wip_entity_id = WOR.parent_object_id
11355: AND WDJ.wip_entity_id = WO.wip_entity_id
11516: RAISE FND_API.G_EXC_ERROR;
11517: END IF;
11518: --sukhwsin::VEE - enhancement for insertion of wo status change - ends
11519:
11520: UPDATE ahl_workorders
11521: SET status_code = G_JOB_STATUS_COMPLETE,
11522: object_version_number = mwo_rec.ovn+1
11523: WHERE wip_entity_id = mwo_rec.wip_entity_id;
11524:
11551: RAISE FND_API.G_EXC_ERROR;
11552: END IF;
11553: --sukhwsin::VEE - enhancement for insertion of wo status change - ends
11554:
11555: UPDATE ahl_workorders
11556: SET status_code = G_JOB_STATUS_COMPLETE,
11557: object_version_number = l_obj_ver_no+1
11558: WHERE wip_entity_id = l_wip_entity_id;
11559:
11629: IS
11630: SELECT WIP.WIP_ENTITY_ID,
11631: WIP.SCHEDULED_START_DATE,
11632: WIP.SCHEDULED_COMPLETION_DATE SCHEDULED_END_DATE
11633: FROM AHL_WORKORDERS AWO,
11634: WIP_DISCRETE_JOBS WIP
11635: WHERE AWO.VISIT_ID = p_visit_id
11636: AND AWO.VISIT_TASK_ID IS NULL
11637: AND AWO.MASTER_WORKORDER_FLAG = 'Y'
11649: CWO.visit_task_id,
11650: WIP.SCHEDULED_START_DATE,
11651: WIP.SCHEDULED_COMPLETION_DATE SCHEDULED_END_DATE,
11652: level
11653: FROM AHL_WORKORDERS CWO,
11654: WIP_SCHED_RELATIONSHIPS REL,
11655: WIP_DISCRETE_JOBS WIP
11656: WHERE CWO.wip_entity_id = REL.parent_object_id
11657: AND CWO.wip_entity_id = WIP.WIP_ENTITY_ID
11658: AND CWO.master_workorder_flag = 'Y'
11659: AND REL.parent_object_type_id = 1
11660: AND REL.child_object_type_id = 1
11661: START WITH REL.child_object_id IN (select wos.wip_entity_id
11662: from ahl_workorders wos
11663: where wos.workorder_id = p_workorder_id)
11664: AND REL.relationship_type = 1
11665: CONNECT BY REL.child_object_id = PRIOR REL.parent_object_id
11666: AND REL.relationship_type = 1
11775: -- STHILAK :: FP:Bug 9186962
11776: -- Fetch visit task id also
11777: awo.visit_task_id
11778: FROM
11779: ahl_workorders awo,
11780: ahl_visits_b vst
11781: WHERE
11782: awo.visit_id = vst.visit_id
11783: AND vst.status_code not in ('CANCELLED', 'DELETED')
12156: CURSOR Get_Wo_Cur_Values(c_workorder_id NUMBER) IS
12157: --sukhwsin::Bug 12915297. Removed Hold Reason.
12158: --select wo.status_code, wo.hold_reason_code, vst.organization_id
12159: select wo.status_code, vst.organization_id
12160: from ahl_workorders wo, ahl_visits_b vst, ahl_visit_tasks_b vts
12161: where
12162: wo.visit_task_id = vts.visit_task_id
12163: and vts.visit_id = vst.visit_id
12164: and wo.workorder_id = c_workorder_id;
12164: and wo.workorder_id = c_workorder_id;
12165: --Cursor to get Operation organization_id, seq_num and status_code
12166: CURSOR Get_Op_Details(c_workorder_op_id NUMBER) IS
12167: select vst.organization_id, oper.operation_sequence_num, oper.status_code, wo.workorder_id
12168: from ahl_workorders wo, ahl_visits_b vst, ahl_visit_tasks_b vts,ahl_workorder_operations oper
12169: where
12170: wo.visit_task_id = vts.visit_task_id
12171: and vts.visit_id = vst.visit_id
12172: and oper.workorder_id = wo.workorder_id
12405: IS
12406:
12407: CURSOR get_wip_date_released( c_wo_id NUMBER ) IS
12408: SELECT WIP.DATE_RELEASED
12409: FROM AHL_WORKORDERS WO,
12410: WIP_DISCRETE_JOBS WIP
12411: WHERE WO.workorder_id = c_wo_id
12412: AND WO.wip_entity_id = WIP.wip_entity_id;
12413:
12636: WO.ACTUAL_END_DATE ACTUAL_END_DATE,
12637: WDJ.COMPLETION_SUBINVENTORY COMPLETION_SUBINVENTORY,
12638: WDJ.COMPLETION_LOCATOR_ID COMPLETION_LOCATOR_ID,
12639: WDJ.PRIORITY PRIORITY
12640: FROM AHL_WORKORDERS WO,
12641: WIP_DISCRETE_JOBS WDJ
12642: WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
12643: AND WO.STATUS_CODE NOT IN (4, 5, 7, 12, 15, 17, 22)
12644: AND WO.WORKORDER_ID = P_WORKORDER_ID;
12684: END IF;
12685: RETURN;
12686: END IF;
12687:
12688: l_sql_string := 'SELECT AW.WORKORDER_ID, AW.WORKORDER_NAME, AW.STATUS_CODE, FLV.MEANING WO_STATUS FROM AHL_WORKORDERS AW, FND_LOOKUP_VALUES_VL FLV WHERE AW.STATUS_CODE = FLV.LOOKUP_CODE AND FLV.LOOKUP_TYPE = ''AHL_JOB_STATUS'' AND WORKORDER_ID IN (0';
12689:
12690: FOR i IN p_workorder_id_tbl.first .. p_workorder_id_tbl.last
12691: LOOP
12692: l_sql_string := l_sql_string || ',' || p_workorder_id_tbl(i).WORKORDER_ID;
12726: IF (G_LEVEL_STATEMENT >= G_CURRENT_LOG_LEVEL)THEN
12727: FND_LOG.STRING( G_LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, 'All workorder have proper status, lets update');
12728: END IF;
12729:
12730: -- Go for bulk update of AHL_WORKORDERS table
12731: FORALL I IN P_WORKORDER_ID_TBL.first .. P_WORKORDER_ID_TBL.last
12732: UPDATE AHL_WORKORDERS
12733: SET AOG_FLAG = 'Y',
12734: OBJECT_VERSION_NUMBER = P_WORKORDER_ID_TBL(I).OBJ_VER_NUM + 1,
12728: END IF;
12729:
12730: -- Go for bulk update of AHL_WORKORDERS table
12731: FORALL I IN P_WORKORDER_ID_TBL.first .. P_WORKORDER_ID_TBL.last
12732: UPDATE AHL_WORKORDERS
12733: SET AOG_FLAG = 'Y',
12734: OBJECT_VERSION_NUMBER = P_WORKORDER_ID_TBL(I).OBJ_VER_NUM + 1,
12735: LAST_UPDATE_DATE = SYSDATE,
12736: LAST_UPDATED_BY = FND_GLOBAL.USER_ID,