[Home] [Help]
38:
39: --------------------------------------------------------------------
40: -- START: Defining local functions and procedures SIGNATURES --
41: --------------------------------------------------------------------
42: -- To find out the Visit_Id for the AHL_Visits_B and TL tables
43: FUNCTION Get_Visit_Id
44: RETURN NUMBER;
45:
46: -- To find out the Visit_Number for the AHL_Visits_B table
42: -- To find out the Visit_Id for the AHL_Visits_B and TL tables
43: FUNCTION Get_Visit_Id
44: RETURN NUMBER;
45:
46: -- To find out the Visit_Number for the AHL_Visits_B table
47: FUNCTION Get_Visit_Number
48: RETURN NUMBER;
49:
50: -- To find out the Visit_Task_Number for the AHL_Visit_Tasks_B table
541: FUNCTION Get_Visit_Id RETURN NUMBER IS
542:
543: -- To find the next id value from visit sequence
544: CURSOR c_seq IS
545: SELECT Ahl_Visits_B_S.NEXTVAL
546: FROM dual;
547:
548: -- To find whether id already exists
549: CURSOR c_id_exists (x_id IN NUMBER) IS
595:
596: -- To find maximum visit number among all visits
597: CURSOR c_visit_number IS
598: SELECT MAX(visit_number)
599: FROM Ahl_Visits_B;
600:
601: L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Number';
602: L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
603: L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
720: l_item_key VARCHAR2(100);
721:
722: -- To find out visit status
723: CURSOR c_visit_status (x_visit_id IN NUMBER) IS
724: SELECT STATUS_CODE FROM AHL_VISITS_B
725: WHERE VISIT_ID = x_visit_id;
726:
727: -- Cursor to get planned UEs for the given visit
728: CURSOR get_visit_planned_ues (c_visit_id NUMBER) IS
1236: -- Removed project template from the SELECT, FROM, and WHERE clauses of this cursor
1237: --PA.NAME PROJECT_TEMPLATE_NAME,
1238: AVTS.TOP_PROJECT_TASK_ID, --PRAKKUM ::FP PIE :: 13-OCT-2010
1239: AVTS.INV_LOCATOR_ID -- Added by sowsubra
1240: FROM AHL_VISITS_B AVTS,AHL_VISITS_TL AVTSTL,
1241: AHL_SIMULATION_PLANS_B ASPV,AHL_SIMULATION_PLANS_TL ASPVTL,
1242: CSI_ITEM_INSTANCES CSIS, HR_ALL_ORGANIZATION_UNITS_TL HROU,
1243: BOM_DEPARTMENTS BDPT, MTL_SYSTEM_ITEMS_B_KFV MTSB,
1244: FND_LOOKUP_VALUES FLVT, FND_LOOKUP_VALUES FLVT1,
1688:
1689: -- Define local Cursors
1690: -- To find the next id value from visit sequence
1691: CURSOR c_seq IS
1692: SELECT Ahl_Visits_B_S.NEXTVAL
1693: FROM dual;
1694:
1695: -- To find whether id already exists
1696: CURSOR c_id_exists (x_id IN NUMBER) IS
1698: WHERE Visit_id = x_id;
1699:
1700: -- To find the maximum visit number among all visits
1701: CURSOR c_visit_number IS
1702: SELECT MAX(visit_number) FROM Ahl_Visits_B;
1703:
1704: -- Fix for ADS bug# 4357001.
1705: -- Changed cursor to not use ahl_mtl_items_ou_v and replaced with mtl_system_items_kfv
1706: -- and added OU stripping so that Master Org does not belong to the current user's OU.
2585: -- TCHIMIRA :: Changes for Component Maintenance Planning :: 10-JAN-2012 :: START
2586: -- if the visit is component type then do not flag the visit as past dated irrespective of the start date
2587: IF (l_is_comp_visit = 'Y') THEN
2588: --Update the visit table and set past dated visit flag to 'N'
2589: UPDATE AHL_VISITS_B
2590: set PAST_DATED_VISIT_FLAG = 'N',
2591: OBJECT_VERSION_NUMBER = object_version_number + 1,
2592: LAST_UPDATE_DATE = SYSDATE,
2593: LAST_UPDATED_BY = Fnd_Global.USER_ID,
2706: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2707: fnd_log.string(fnd_log.level_statement,L_DEBUG,'l_date_time - '||l_date_time);
2708: END IF;
2709:
2710: UPDATE ahl_visits_b
2711: SET close_date_time = l_date_time
2712: WHERE visit_id = l_Visit_rec.visit_id ;
2713: END IF;
2714:
3496: --salogan::Modified the cursor to handle both request id from P2P and Cost Estimation CP ER 9299910::01-JUN-2009::BEGIN
3497: --Cursor to fetch phase
3498: CURSOR c_conc_req_phase(c_id IN NUMBER) IS
3499: SELECT 'X'
3500: FROM FND_CONCURRENT_REQUESTS FCR, AHL_VISITS_B AVB
3501: WHERE FCR.REQUEST_ID IN ( AVB.CST_EST_REQUEST_ID, AVB.REQUEST_ID )
3502: AND FCR.PHASE_CODE IN ('P', 'R')
3503: AND AVB.VISIT_ID = c_id;
3504: l_dummy_val c_conc_req_phase%ROWTYPE;
3519:
3520: --Fetch to find whether task updation is going to happend or not
3521: CURSOR get_visit_data_csr(p_visit_id IN NUMBER) IS
3522: SELECT v.department_id , v.start_date_time
3523: FROM AHL_VISITS_B v, AHL_DEPARTMENT_SHIFTS shifts
3524: WHERE v.department_id = shifts.department_id
3525: AND v.visit_id = p_visit_id;
3526:
3527: l_visit_dept_id NUMBER := NULL;
4831: CLOSE c_job;
4832:
4833: IF l_count <> 0 THEN
4834: -- To update visit's prices
4835: UPDATE AHL_VISITS_B
4836: SET ACTUAL_PRICE = NULL,
4837: ESTIMATED_PRICE = NULL,
4838: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
4839: WHERE VISIT_ID = l_Visit_rec.visit_id;
5606: --Delete the visit
5607: AHL_VISITS_PKG.Delete_Row( x_visit_id => p_visit_id);
5608: ELSE
5609: -- Soft Delete
5610: UPDATE AHL_VISITS_B
5611: SET STATUS_CODE = 'DELETED',
5612: SIMULATION_PLAN_ID = NULL,
5613: OBJECT_VERSION_NUMBER =OBJECT_VERSION_NUMBER + 1,
5614: --TCHIMIRA::FP BUG 12668460 for BUG 12607498 ::14-JUN-2011::UPDATE WHO COLUMNS
5700: AVB.visit_number,
5701: AVB.start_date_time,
5702: AVB.close_date_time
5703:
5704: FROM AHL_VISITS_B AVB,
5705: AHL_VISIT_TYPES_B AVTB
5706:
5707: WHERE AVB.organization_id = c_org_id
5708: AND AVB.visit_type_code = AVTB.visit_type_code
6648: fnd_log.string(fnd_log.level_statement,L_DEBUG,'Updating Visit Status to CLOSED..');
6649: END IF;
6650:
6651: -- To update visit to CLOSED status
6652: UPDATE AHL_VISITS_B
6653: SET STATUS_CODE = 'CLOSED',
6654: SIMULATION_PLAN_ID = NULL,
6655: OBJECT_VERSION_NUMBER = c_visit_rec.object_version_number + 1,
6656: --TCHIMIRA::FP BUG 12668460 for BUG 12607498 ::14-JUN-2011::UPDATE WHO COLUMNS
6849: -- To check if any other active visits have this SR associated
6850: CURSOR c_check_SR (x_visit_id IN NUMBER, x_sr_id IN NUMBER) IS
6851: SELECT 'X' FROM DUAL
6852: WHERE exists ( select a.visit_id
6853: from ahl_visits_b a, ahl_visit_tasks_b b
6854: where a.visit_id <> x_visit_id
6855: and a.visit_id = b.visit_id
6856: and b.visit_id <> x_visit_id
6857: AND NVL(a.STATUS_CODE,'X') not in ('DELETED','CLOSED')
7086: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
7087: fnd_log.string(fnd_log.level_statement,L_DEBUG,'Updating the visit status to CANCELLED..');
7088: END IF;
7089: -- update visit status
7090: UPDATE AHL_VISITS_B
7091: SET STATUS_CODE = 'CANCELLED',
7092: OBJECT_VERSION_NUMBER = c_visit_rec.object_version_number + 1,
7093: --TCHIMIRA::FP BUG 12668460 for BUG 12607498 ::14-JUN-2011::UPDATE WHO COLUMNS
7094: LAST_UPDATE_DATE = SYSDATE,
7660:
7661: -- Initialize return status to success initially
7662: x_return_status:=FND_API.G_RET_STS_SUCCESS;
7663:
7664: UPDATE AHL_VISITS_B
7665: SET UNIT_SCHEDULE_ID = NULL
7666: WHERE UNIT_SCHEDULE_ID = p_unit_schedule_id ;
7667:
7668: EXCEPTION
7903: RETURN VARCHAR2
7904: IS
7905: -- To find out the maximum task number value in the visit
7906: CURSOR c_is_old_visit IS
7907: select sum(1) from AHL_VISITS_B VST,
7908: ahl_vwp_stages_b STG
7909: WHERE VST.visit_id = STG.VISIT_ID AND VST.VISIT_ID = p_visit_id
7910: AND nvl2(STG.stage_status_code,'Y','N') = 'N';
7911: