[Home] [Help]
26: G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
27: ------------------------------------
28: -- Common constants and variables --
29: ------------------------------------
30: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
31: l_log_statement NUMBER := fnd_log.level_statement;
32: l_log_procedure NUMBER := fnd_log.level_procedure;
33: l_log_error NUMBER := fnd_log.level_error;
34: l_log_unexpected NUMBER := fnd_log.level_unexpected;
27: ------------------------------------
28: -- Common constants and variables --
29: ------------------------------------
30: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
31: l_log_statement NUMBER := fnd_log.level_statement;
32: l_log_procedure NUMBER := fnd_log.level_procedure;
33: l_log_error NUMBER := fnd_log.level_error;
34: l_log_unexpected NUMBER := fnd_log.level_unexpected;
35: ---------------------------------------------------------------------
28: -- Common constants and variables --
29: ------------------------------------
30: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
31: l_log_statement NUMBER := fnd_log.level_statement;
32: l_log_procedure NUMBER := fnd_log.level_procedure;
33: l_log_error NUMBER := fnd_log.level_error;
34: l_log_unexpected NUMBER := fnd_log.level_unexpected;
35: ---------------------------------------------------------------------
36: -- Define Record Types for record structures needed by the APIs --
29: ------------------------------------
30: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
31: l_log_statement NUMBER := fnd_log.level_statement;
32: l_log_procedure NUMBER := fnd_log.level_procedure;
33: l_log_error NUMBER := fnd_log.level_error;
34: l_log_unexpected NUMBER := fnd_log.level_unexpected;
35: ---------------------------------------------------------------------
36: -- Define Record Types for record structures needed by the APIs --
37: ---------------------------------------------------------------------
30: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
31: l_log_statement NUMBER := fnd_log.level_statement;
32: l_log_procedure NUMBER := fnd_log.level_procedure;
33: l_log_error NUMBER := fnd_log.level_error;
34: l_log_unexpected NUMBER := fnd_log.level_unexpected;
35: ---------------------------------------------------------------------
36: -- Define Record Types for record structures needed by the APIs --
37: ---------------------------------------------------------------------
38: TYPE Stage_Record IS RECORD ( INDEX_ID INTEGER, STAGE_ID NUMBER, DURATION NUMBER, PLANNED_START_DATE DATE, CALCULATED_START_TIME DATE,DEPARTMENT_ID NUMBER); -- PRAKKUM :: 28/03/2011 :: VWPE :: ER 12424063 :: Record to store stage details
1150: x_return_status => l_return_status,
1151: x_msg_count => l_msg_count,
1152: x_msg_data => l_msg_data);
1153:
1154: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1155: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_SUCC_STG_START_DATES - l_return_status : '||l_return_status);
1156: END IF;
1157:
1158: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1151: x_msg_count => l_msg_count,
1152: x_msg_data => l_msg_data);
1153:
1154: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1155: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_SUCC_STG_START_DATES - l_return_status : '||l_return_status);
1156: END IF;
1157:
1158: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1159: x_msg_count := l_msg_count;
1176: x_return_status => l_return_status,
1177: x_msg_count => l_msg_count,
1178: x_msg_data => l_msg_data);
1179:
1180: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1181: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DEPENDENCY_RULES - l_return_status : '||l_return_status||' :p_x_stages_ovn_tbl cnt is '||p_x_stages_ovn_tbl.count);
1182: END IF;
1183:
1184: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1177: x_msg_count => l_msg_count,
1178: x_msg_data => l_msg_data);
1179:
1180: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1181: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DEPENDENCY_RULES - l_return_status : '||l_return_status||' :p_x_stages_ovn_tbl cnt is '||p_x_stages_ovn_tbl.count);
1182: END IF;
1183:
1184: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1185: x_msg_count := l_msg_count;
1429: --------------------- Initialize -----------------------
1430: SAVEPOINT Delete_Stages;
1431:
1432: IF (l_log_procedure >= l_log_current_level) THEN
1433: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','At the start of the PLSQL procedure');
1434: END IF;
1435:
1436: -- Initialize message list if p_init_msg_list is set to TRUE.
1437: IF Fnd_Api.to_boolean(p_init_msg_list)
1485: end if;
1486:
1487: --Set duration to zero, calling UPDATE_AS_STAGE_RULE_DEL next to this statement will not consider duration into account
1488: IF (l_log_statement >= l_log_current_level) THEN
1489: fnd_log.string(l_log_statement,L_DEBUG,p_x_stages_tbl(i).STAGE_ID||' stage id updated');
1490: END IF;
1491: update AHL_VWP_STAGES_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
1492: DURATION = 0,
1493: LAST_UPDATE_DATE = SYSDATE,
1536: FETCH c_stage_tasks_count INTO l_stage_tasks_count;
1537: CLOSE c_stage_tasks_count;
1538:
1539: IF (l_log_statement >= l_log_current_level) THEN
1540: fnd_log.string(l_log_statement,L_DEBUG,'l_stage_tasks_count :'||l_stage_tasks_count);
1541: END IF;
1542:
1543: IF l_stage_tasks_count>0 THEN
1544: Fnd_Message.SET_NAME('AHL','AHL_VWP_STG_NO_DEL_HAV_TASKS');
1555: DELETE FROM AHL_VISIT_TASKS_TL WHERE VISIT_TASK_ID = l_stage_task_dets.VISIT_TASK_ID;
1556: DELETE FROM AHL_VISIT_TASKS_B WHERE VISIT_TASK_ID = l_stage_task_dets.VISIT_TASK_ID;
1557: END IF;*/
1558: IF (l_log_statement >= l_log_current_level) THEN
1559: fnd_log.string(l_log_statement,L_DEBUG,'l_stage_task_dets.VISIT_TASK_ID :'||l_stage_task_dets.VISIT_TASK_ID);
1560: END IF;
1561: IF l_stage_task_dets.VISIT_TASK_ID IS NOT NULL THEN
1562:
1563: IF (l_log_statement >= l_log_current_level) THEN
1560: END IF;
1561: IF l_stage_task_dets.VISIT_TASK_ID IS NOT NULL THEN
1562:
1563: IF (l_log_statement >= l_log_current_level) THEN
1564: fnd_log.string(l_log_statement,L_DEBUG,'Before call to Delete_Task');
1565: END IF;
1566:
1567: AHL_VWP_TASKS_PVT.Delete_Task (
1568: p_api_version => p_api_version,
1574: x_msg_count => l_msg_count,
1575: x_msg_data => l_msg_data );
1576:
1577: IF (l_log_statement >= l_log_current_level) THEN
1578: fnd_log.string(l_log_statement,L_DEBUG,'After call to Delete_Task: l_return_status:'||l_return_status||' l_msg_count:'||l_msg_count);
1579: END IF;
1580:
1581: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1582: x_msg_count := l_msg_count;
1596: Ahl_VWP_stages_Pkg.DELETE_ROW ( X_STAGE_ID => p_x_stages_tbl(i).STAGE_ID);
1597:
1598: -- Remove all stage assciations on tasks
1599: IF (l_log_statement >= l_log_current_level) THEN
1600: fnd_log.string(l_log_statement,L_DEBUG,p_x_stages_tbl(i).STAGE_ID||' stage id updated');
1601: END IF;
1602: update AHL_VISIT_TASKS_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
1603: STAGE_ID = null,
1604: LAST_UPDATE_DATE = SYSDATE,
1622: COMMIT;
1623: END IF;
1624:
1625: IF (l_log_procedure >= l_log_current_level) THEN
1626: fnd_log.string(l_log_procedure,L_DEBUG||'.end','At the end of the PLSQL procedure');
1627: END IF;
1628:
1629: EXCEPTION
1630: WHEN Fnd_Api.g_exc_error THEN
1894: close c_stage_data;
1895:
1896:
1897: IF (l_log_statement >= l_log_current_level) THEN
1898: fnd_log.string(l_log_statement,
1899: L_FULL_NAME,
1900: ' l_stage_rec.OBJECT_VERSION_NUMBER: '||l_stage_rec.OBJECT_VERSION_NUMBER||
1901: ' p_stages_rec.OBJECT_VERSION_NUMBER: '||p_stages_rec.OBJECT_VERSION_NUMBER);
1902: END IF;
2163: OPEN c_get_task_stage_id(parent_task_rec.parent_task_id);
2164: FETCH c_get_task_stage_id INTO l_task_stage_id;
2165: CLOSE c_get_task_stage_id;
2166: IF (l_log_statement >= l_log_current_level) THEN
2167: fnd_log.string(l_log_statement,
2168: L_DEBUG_KEY,
2169: 'In the for loop of c_get_parent_tasks and l_task_stage_id is: '||l_task_stage_id);
2170: END IF;
2171: -- For the passed stage id, loop through all of its successive children
2170: END IF;
2171: -- For the passed stage id, loop through all of its successive children
2172: FOR child_stage_rec IN c_get_child_stages(l_stage_id) LOOP
2173: IF (l_log_statement >= l_log_current_level) THEN
2174: fnd_log.string(l_log_statement,
2175: L_DEBUG_KEY,
2176: 'In the for loop of c_get_child_stages and child_stage_rec.object_id is: '||child_stage_rec.object_id);
2177: END IF;
2178: -- If any of the successive parents of passed task id falls in any of the successive children
2193: OPEN c_get_task_stage_id(child_task_rec.visit_task_id);
2194: FETCH c_get_task_stage_id INTO l_task_stage_id;
2195: CLOSE c_get_task_stage_id;
2196: IF (l_log_statement >= l_log_current_level) THEN
2197: fnd_log.string(l_log_statement,
2198: L_DEBUG_KEY,
2199: 'In the for loop of c_get_child_tasks and l_task_stage_id is: '||l_task_stage_id);
2200: END IF;
2201:
2201:
2202: -- For the passed stage id, loop through all of its successive parents
2203: FOR parent_stage_rec IN c_get_parent_stages(l_stage_id) LOOP
2204: IF (l_log_statement >= l_log_current_level) THEN
2205: fnd_log.string(l_log_statement,
2206: L_DEBUG_KEY,
2207: 'In the for loop of c_get_parent_stages and parent_stage_rec.subject_id is: '||parent_stage_rec.subject_id);
2208: END IF;
2209: -- If any of the successive children of passed task id falls in any of the successive parent
2220: END LOOP;
2221:
2222: -- SET OUT PARAM
2223: IF (l_log_statement >= l_log_current_level) THEN
2224: fnd_log.string(l_log_statement,
2225: L_DEBUG_KEY,
2226: 'Before setting stage id as out param: '||l_stage_id);
2227: END IF;
2228: x_stage_id := l_stage_id;
2635:
2636: --PRAKKUM :: VWPE :: ER 12424063 :: 15-APR-2011 :: to find duplicate rules :: START
2637: l_duplicated_count :=0; --Initialization
2638: IF (l_log_statement >= l_log_current_level) THEN
2639: fnd_log.string(l_log_statement,L_DEBUG_KEY,':Sub and Obj Id is : '||p_stage_rule_rec.SUBJECT_STAGE_ID||' and ' || p_stage_rule_rec.OBJECT_STAGE_ID||' and l_duplicated_count is :' ||l_duplicated_count );
2640: END IF;
2641:
2642: OPEN c_get_stage_is_dup (p_stage_rule_rec.SUBJECT_STAGE_ID,p_stage_rule_rec.OBJECT_STAGE_ID);
2643: FETCH c_get_stage_is_dup INTO l_duplicated_count;
2643: FETCH c_get_stage_is_dup INTO l_duplicated_count;
2644: CLOSE c_get_stage_is_dup;
2645:
2646: IF (l_log_statement >= l_log_current_level) THEN
2647: fnd_log.string(l_log_statement,L_DEBUG_KEY,': l_duplicated_count is :' ||l_duplicated_count );
2648: END IF;
2649:
2650: IF l_duplicated_count IS NOT NULL AND l_duplicated_count > 1 THEN
2651: FND_MESSAGE.SET_NAME('AHL','AHL_VWP_STG_RULE_DUP'); --a stage rule already exists between these stages
2664: l_invalid_rules_count := l_invalid_rules_count+1;
2665: END LOOP;
2666:
2667: IF (l_log_statement >= l_log_current_level) THEN
2668: fnd_log.string(l_log_statement,L_DEBUG_KEY,': l_invalid_rules_count is :' ||l_invalid_rules_count );
2669: END IF;
2670:
2671: IF l_invalid_rules_count>0 THEN
2672: RAISE FND_API.G_EXC_ERROR;
2678: FETCH c_stage_number INTO l_parent_stage_num;
2679: CLOSE c_stage_number;
2680:
2681: IF (l_log_statement >= l_log_current_level)THEN
2682: fnd_log.string (l_log_statement,L_DEBUG_KEY, 'Before Calling NON_CYCLIC_STAGE_REL');
2683: END IF;
2684:
2685: NON_CYCLIC_STAGE_REL
2686: (
2691: P_PARENT_STAGE_NUMBER =>l_parent_stage_num
2692: );
2693:
2694: IF (l_log_statement >= l_log_current_level)THEN
2695: fnd_log.string (l_log_statement,L_DEBUG_KEY, 'After Calling NON_CYCLIC_STAGE_REL &'||' Return status is: '||l_return_Status);
2696: END IF;
2697:
2698: IF l_return_Status <>'S' THEN
2699: IF l_return_Status = FND_API.G_RET_STS_ERROR THEN
2708: FETCH c_prev_stage_num into l_prev_stage_num;
2709: CLOSE c_prev_stage_num;
2710:
2711: IF (l_log_statement >= l_log_current_level) THEN
2712: fnd_log.string(l_log_statement,L_DEBUG_KEY,'tch Parent stage number is: '||l_parent_stage_num||
2713: 'Previous stage number is: '||l_prev_stage_num);
2714: END IF;
2715:
2716:
2788: OPEN c_stage_number (p_stage_rule_rec.OBJECT_STAGE_ID);
2789: FETCH c_stage_number INTO l_child_stage_num;
2790: CLOSE c_stage_number;
2791: IF (l_log_statement >= l_log_current_level) THEN
2792: fnd_log.string(l_log_statement,L_DEBUG_KEY,'Parent stage number is: '||l_parent_stage_num||
2793: 'Previous stage number is: '||l_prev_stage_num);
2794: END IF;
2795:
2796: -- For the given stage rule, loop through all the successive parent stages
2917: --------------------- Initialize -----------------------
2918: SAVEPOINT Process_StageTyp_Assoc;
2919:
2920: IF (l_log_procedure >= l_log_current_level) THEN
2921: fnd_log.string(l_log_procedure,L_DEBUG_KEY||'.begin','At the start of the PLSQL procedure');
2922: END IF;
2923:
2924: -- Initialize message list if p_init_msg_list is set to TRUE.
2925: IF Fnd_Api.to_boolean(p_init_msg_list)
2954: END IF;
2955:
2956: -------------------------- Insert/Delete --------------------------
2957: IF (l_log_procedure >= l_log_current_level) THEN
2958: fnd_log.string(l_log_procedure,L_DEBUG_KEY,'start of the DML operation');
2959: END IF;
2960:
2961: -- Loop through all the stage-stage type associations that are passed
2962: FOR i IN p_stage_type_assoc_tbl.FIRST..p_stage_type_assoc_tbl.LAST
2963: loop
2964:
2965: IF (p_stage_type_assoc_tbl(i).dml_operation = 'C') THEN
2966: IF (l_log_statement >= l_log_current_level) THEN
2967: fnd_log.string(l_log_statement,L_DEBUG_KEY,'DML operation for the record is Create');
2968: END IF;
2969: --We need to create an association between the passed stage type and stage id
2970: -- Invoke the table handler to create a record in "AHL_VISIT_STAGE_TYP_ASOC"
2971:
2996: X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
2997: X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
2998:
2999: IF (l_log_statement >= l_log_current_level) THEN
3000: fnd_log.string(l_log_statement,L_DEBUG_KEY,'After calling insert row. Created an association for stage - '||p_stage_type_assoc_tbl(i).STAGE_ID||' and stage type - '||p_stage_type_assoc_tbl(i).STAGE_TYPE_CODE);
3001: END IF;
3002:
3003: --We need to update all the tasks (that are in PLANNING) of this visit which have stage type that matches with the passed stage type. So loop through all such tasks
3004: -- Set the stage_id with the passed stage_id for all such tasks.
3003: --We need to update all the tasks (that are in PLANNING) of this visit which have stage type that matches with the passed stage type. So loop through all such tasks
3004: -- Set the stage_id with the passed stage_id for all such tasks.
3005: FOR l_tasks_rec IN c_visit_tasks(l_visit_id, p_stage_type_assoc_tbl(i).stage_type_code) LOOP
3006: IF (l_log_statement >= l_log_current_level) THEN
3007: fnd_log.string(l_log_statement,
3008: L_DEBUG_KEY,
3009: ' Before Calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES ');
3010: END IF;
3011:
3019: X_MSG_COUNT => l_msg_count,
3020: X_MSG_DATA => l_msg_data );
3021:
3022: IF (l_log_statement >= l_log_current_level) THEN
3023: fnd_log.string(l_log_statement,
3024: L_DEBUG_KEY,
3025: ' After Calling VALIDATE_STAGE_UPDATES and return status is: '||l_return_status);
3026: END IF;
3027:
3045:
3046: IF (p_stage_type_assoc_tbl(i).dml_operation = 'D') THEN
3047:
3048: IF (l_log_statement >= l_log_current_level) THEN
3049: fnd_log.string(l_log_statement,L_DEBUG_KEY,'DML operation for the record is Delete. Deleting a record with assoc id : '||p_stage_type_assoc_tbl(i).STAGE_TYPE_ASSOC_ID);
3050: END IF;
3051:
3052: --We need to update all the tasks (that are in PLANNING) of this visit which have stage type that matches with the passed stage type.
3053: --Nullify the stage Ids for all such tasks
3196: --------------------- Initialize -----------------------
3197: SAVEPOINT Assoc_VisitType_to_Visit;
3198:
3199: IF (l_log_procedure >= l_log_current_level) THEN
3200: fnd_log.string(l_log_procedure,L_DEBUG_KEY||'.begin','At the start of the PLSQL procedure');
3201: END IF;
3202:
3203: -- Initialize message list if p_init_msg_list is set to TRUE.
3204: IF Fnd_Api.to_boolean(p_init_msg_list)
3217: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3218: END IF;
3219:
3220: IF (l_log_statement >= l_log_current_level) THEN
3221: fnd_log.string(l_log_statement,
3222: L_DEBUG_KEY,
3223: 'Before inserting stages for visit_type_code '||p_visit_type_code||'Visit ID =' || P_VISIT_ID);
3224: END IF;
3225:
3245: FETCH c_stage_num INTO l_prev_stage_num;
3246: CLOSE c_stage_num;
3247:
3248: IF (l_log_statement >= l_log_current_level) THEN
3249: fnd_log.string(l_log_statement,
3250: L_DEBUG_KEY,
3251: 'Before calling table handler for inserting a record. stage ID is '||l_stage_id|| ' previous stage number is: '||l_prev_stage_num);
3252: END IF;
3253:
3287: X_PREV_STAGE_NUM => l_prev_stage_num,
3288: X_EARLIEST_START_DATE => NULL );
3289:
3290: IF (l_log_statement >= l_log_current_level) THEN
3291: fnd_log.string(l_log_statement,
3292: L_DEBUG_KEY,
3293: 'After calling table handler for inserting a record and before calling AHL_VWP_TASKS_PVT.Create_Stage_Tasks');
3294: END IF;
3295:
3307: x_msg_data => l_msg_data
3308: );
3309:
3310: IF (l_log_statement >= l_log_current_level) THEN
3311: fnd_log.string(l_log_statement,
3312: L_DEBUG_KEY,
3313: 'After calling AHL_VWP_TASKS_PVT.Create_Stage_Tasks and the return status is: '||l_return_status);
3314: END IF;
3315:
3323: END IF;
3324: END IF;
3325:
3326: IF (l_log_statement >= l_log_current_level) THEN
3327: fnd_log.string(l_log_statement,
3328: L_DEBUG_KEY,
3329: 'Before inserting stage - stage type association for stage num '||l_stages_rec.stage_number);
3330: END IF;
3331:
3357: --We need to update all the tasks (that are in PLANNING) of this visit which have stage type that matches with the passed stage type.
3358: -- Set the stage_id with the passed stage_id for all such tasks
3359: FOR l_tasks_rec IN c_visit_tasks(P_VISIT_ID, l_stagetypes_rec.stage_type_code) LOOP
3360: IF (l_log_statement >= l_log_current_level) THEN
3361: fnd_log.string(l_log_statement,
3362: L_DEBUG_KEY,
3363: 'Before Calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES ');
3364: END IF;
3365: AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
3372: X_MSG_COUNT => l_msg_count,
3373: X_MSG_DATA => l_msg_data );
3374:
3375: IF (l_log_statement >= l_log_current_level) THEN
3376: fnd_log.string(l_log_statement,
3377: L_DEBUG_KEY,
3378: ' After calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES and return status: '||l_return_status);
3379: END IF;
3380:
3401: -- Loop through all the stages associated to the passed visit type code
3402: FOR l_stages_rec IN get_visit_type_stages(p_visit_type_code, P_VISIT_ID)
3403: LOOP
3404: IF (l_log_statement >= l_log_current_level) THEN
3405: fnd_log.string(l_log_statement,
3406: L_DEBUG_KEY,
3407: 'Before calling inserting stage rules visit_type_stage_id= '||l_stages_rec.visit_type_stage_id||', visit_id'||p_visit_id);
3408: END IF;
3409:
3410: -- Fetch all the stage rules of a stage and loop through all the rules.
3411: FOR l_stage_rules_rec IN get_stage_rules(l_stages_rec.visit_type_stage_id, p_visit_id)
3412: LOOP
3413: IF (l_log_statement >= l_log_current_level) THEN
3414: fnd_log.string(l_log_statement,
3415: L_DEBUG_KEY,
3416: 'Before inserting stage rules l_stage_rules_rec.object_id and l_stage_rules_rec.subject_id= '||l_stage_rules_rec.object_id||' '||l_stage_rules_rec.subject_id);
3417: END IF;
3418:
3444: Fnd_Global.LOGIN_ID
3445: );
3446:
3447: IF (l_log_statement >= l_log_current_level) THEN
3448: fnd_log.string(l_log_statement,
3449: L_DEBUG_KEY,
3450: 'After inserting stage rules and before claling validate stage rules');
3451: END IF;
3452:
3456: x_msg_count => l_msg_count,
3457: x_msg_data => l_msg_data
3458: );
3459:
3460: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3461: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'After calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_RULES - l_return_status : '||l_return_status);
3462: END IF;
3463:
3464: l_msg_count := Fnd_Msg_Pub.count_msg;
3457: x_msg_data => l_msg_data
3458: );
3459:
3460: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3461: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'After calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_RULES - l_return_status : '||l_return_status);
3462: END IF;
3463:
3464: l_msg_count := Fnd_Msg_Pub.count_msg;
3465: IF l_msg_count > 0 OR NVL(l_return_status,'X') <> FND_API.G_RET_STS_SUCCESS THEN
3476:
3477: END LOOP; --Loop through all the stages associated to the passed visit type code
3478:
3479: IF (l_log_statement >= l_log_current_level) THEN
3480: fnd_log.string(l_log_statement,
3481: L_DEBUG_KEY,
3482: 'Before calling UPDATE_STAGES_HIERARICHY and message count is :'||Fnd_Msg_Pub.count_msg);
3483: END IF;
3484:
3495: x_msg_data => l_msg_data
3496: );
3497:
3498: IF (l_log_statement >= l_log_current_level) THEN
3499: fnd_log.string(l_log_statement,
3500: L_DEBUG_KEY,
3501: 'After calling UPDATE_STAGES_HIERARICHY and return status is:'||l_return_status);
3502: END IF;
3503:
3516: COMMIT WORK;
3517: END IF;
3518:
3519: IF (l_log_procedure >= l_log_current_level)THEN
3520: fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure');
3521: END IF;
3522:
3523: EXCEPTION
3524: WHEN Fnd_Api.g_exc_error THEN
3579:
3580: BEGIN
3581:
3582: IF (l_log_procedure >= l_log_current_level) THEN
3583: fnd_log.string(l_log_procedure,
3584: L_DEBUG_KEY ||'.begin',
3585: 'At the start of PL SQL function. Visit Id = ' || p_visit_id);
3586: END IF;
3587:
3590: FETCH c_stage_number INTO x_Visit_Stage_Number;
3591: CLOSE c_stage_number;
3592:
3593: IF (l_log_procedure >= l_log_current_level) THEN
3594: fnd_log.string(l_log_procedure,
3595: L_DEBUG_KEY ||'.end',
3596: 'At the end of PL SQL function. Visit Stage Number = ' || x_Visit_Stage_Number);
3597: END IF;
3598: RETURN x_Visit_Stage_Number;
3633:
3634: BEGIN
3635:
3636: IF (l_log_procedure >= l_log_current_level) THEN
3637: fnd_log.string(l_log_procedure,
3638: L_DEBUG_KEY ||'.begin',
3639: 'At the start of PL SQL function. Visit Id = ' || p_visit_id);
3640: END IF;
3641:
3659:
3660: END LOOP;
3661:
3662: IF (l_log_statement >= l_log_current_level) THEN
3663: fnd_log.string(l_log_statement,
3664: L_DEBUG_KEY ,'l_changesExist : '||l_changesExist);
3665: END IF;
3666:
3667: IF l_changesExist ='Y' THEN
3668: l_parallel_stage_ids := Get_Stage_Parallel_Stages(l_parallel_stage_ids , p_visit_id );
3669: END IF;
3670:
3671: IF (l_log_procedure >= l_log_current_level) THEN
3672: fnd_log.string(l_log_procedure,
3673: L_DEBUG_KEY ||'.end',
3674: 'At the end of PL SQL function. p_visit_id = ' || p_visit_id);
3675: END IF;
3676:
3714:
3715: SAVEPOINT UPDATE_STAGES_HIERARICHY;
3716:
3717: IF (l_log_procedure >= l_log_current_level) THEN
3718: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
3719: END IF;
3720:
3721: -- Initialize API return status to success
3722: -- Initialize message list if p_init_msg_list is set to TRUE.
3747:
3748: --------------------------End of API Body---------------------------------------
3749:
3750: IF (l_log_statement >= l_log_current_level) THEN
3751: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.PROCESS_STAGE_DATES - l_return_status : '||l_return_status);
3752: END IF;
3753:
3754: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3755: x_msg_count := l_msg_count;
3773: COMMIT;
3774: END IF;
3775:
3776: IF (l_log_procedure >= l_log_current_level) THEN
3777: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
3778: END IF;
3779:
3780: EXCEPTION
3781: WHEN Fnd_Api.G_EXC_ERROR THEN
3873:
3874: SAVEPOINT UPDATE_AS_STAGE_RULE_DEL;
3875:
3876: IF (l_log_procedure >= l_log_current_level) THEN
3877: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
3878: END IF;
3879:
3880: -- Initialize API return status to success
3881: -- Initialize message list if p_init_msg_list is set to TRUE.
3902: LAST_UPDATED_BY = Fnd_Global.USER_ID,
3903: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
3904: where STAGE_ID = p_object_id;
3905: IF (l_log_statement >= l_log_current_level) THEN
3906: fnd_log.string(l_log_statement,L_DEBUG,p_object_id||' stage id updated');
3907: END IF;
3908:
3909: END IF;
3910: IF l_visit_start_date is NOT NULL THEN -- PRAKKUM :: 02/06/2011 :: VWPE :: Fixed start date null issue on draft status visits
3934: WHILE l_update_stage_id IS NOT NULL
3935: LOOP
3936:
3937: IF (l_log_statement >= l_log_current_level) THEN
3938: fnd_log.string(l_log_statement,L_DEBUG,'Before planned end date calc ');
3939: END IF;
3940:
3941: --Get previous stage planned end date
3942: SELECT max(PLANNED_END_DATE) into l_prev_stage_planned_end_date from AHL_VWP_STAGES_B,AHL_STAGE_LINKS
3942: SELECT max(PLANNED_END_DATE) into l_prev_stage_planned_end_date from AHL_VWP_STAGES_B,AHL_STAGE_LINKS
3943: WHERE subject_ID = STAGE_ID AND RELATION_TYPE='BEFORE' AND object_ID=l_update_stage_id AND VISIT_ID=p_visit_id;
3944:
3945: IF (l_log_statement >= l_log_current_level) THEN
3946: fnd_log.string(l_log_statement,L_DEBUG,'After planned end date calc ');
3947: END IF;
3948:
3949: IF l_prev_stage_planned_end_date is NULL THEN
3950: l_prev_stage_planned_end_date := l_visit_start_date;
3950: l_prev_stage_planned_end_date := l_visit_start_date;
3951: END IF;
3952:
3953: IF (l_log_statement >= l_log_current_level) THEN
3954: fnd_log.string(l_log_statement,L_DEBUG,'Update stage start date is '||l_prev_stage_planned_end_date||' for stage '||l_update_stage_id);
3955: END IF;
3956:
3957: -- update stage start date to previous stage planned end date
3958: update AHL_VWP_STAGES_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
3961: LAST_UPDATED_BY = Fnd_Global.USER_ID,
3962: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
3963: where STAGE_ID = l_update_stage_id;
3964: IF (l_log_statement >= l_log_current_level) THEN
3965: fnd_log.string(l_log_statement,L_DEBUG,l_update_stage_id||' stage id updated');
3966: END IF;
3967:
3968: l_update_stage_id := parallel_stage_ids.NEXT(l_update_stage_id);
3969:
3973:
3974: --If deletion of relation type is before then find and keep all parallel stages of object stage.
3975:
3976: IF (l_log_statement >= l_log_current_level) THEN
3977: fnd_log.string(l_log_statement,L_DEBUG,' Visit Id : '||p_visit_id);
3978: END IF;
3979:
3980: --Call PROCESS_STAGE_DATES which will update all successor stages in hierarichy to correct stage start dates.
3981: PROCESS_STAGE_DATES (
3991:
3992:
3993: --------------------------End of API Body---------------------------------------
3994: IF (l_log_statement >= l_log_current_level) THEN
3995: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.PROCESS_STAGE_DATES - l_return_status : '||l_return_status);
3996: END IF;
3997:
3998: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3999: x_msg_count := l_msg_count;
4017: COMMIT;
4018: END IF;
4019:
4020: IF (l_log_procedure >= l_log_current_level) THEN
4021: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
4022: END IF;
4023:
4024: EXCEPTION
4025: WHEN Fnd_Api.G_EXC_ERROR THEN
4144:
4145: SAVEPOINT UPDATE_STAGE_DEPENDENCY_RULES;
4146:
4147: IF (l_log_procedure >= l_log_current_level) THEN
4148: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
4149: END IF;
4150:
4151: --Initialize API return status to success
4152: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4171: x_msg_count => l_msg_count,
4172: x_msg_data => l_msg_data);
4173:
4174: IF (l_log_statement >= l_log_current_level) THEN
4175: fnd_log.string(l_log_statement,L_DEBUG,'Stage rule : subject id:'||l_Stage_link_dets.subject_id||' object id:'||l_Stage_link_dets.object_id);
4176: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.PROCESS_STAGE_DATES - l_return_status : '||l_return_status);
4177: END IF;
4178:
4179: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4172: x_msg_data => l_msg_data);
4173:
4174: IF (l_log_statement >= l_log_current_level) THEN
4175: fnd_log.string(l_log_statement,L_DEBUG,'Stage rule : subject id:'||l_Stage_link_dets.subject_id||' object id:'||l_Stage_link_dets.object_id);
4176: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.PROCESS_STAGE_DATES - l_return_status : '||l_return_status);
4177: END IF;
4178:
4179: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4180: x_msg_count := l_msg_count;
4206: LAST_UPDATED_BY = Fnd_Global.USER_ID,
4207: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
4208: WHERE STAGE_ID = p_stage_id;
4209: IF (l_log_statement >= l_log_current_level) THEN
4210: fnd_log.string(l_log_statement,L_DEBUG,p_stage_id||' stage id updated');
4211: END IF;
4212:
4213: END IF;
4214: CLOSE c_get_stage_start_date_dets;
4216: END IF;
4217:
4218: -- PRAKKUM :: 03/06/2011 :: VWPE :: ER 12424063 :: Fixed issue in getting parallel stage :: START
4219:
4220: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4221: fnd_log.string(fnd_log.level_statement,L_DEBUG,' p_stage_id: '||p_stage_id);
4222: END IF;
4223: parallel_stage_ids(p_stage_id) := true;
4224:
4217:
4218: -- PRAKKUM :: 03/06/2011 :: VWPE :: ER 12424063 :: Fixed issue in getting parallel stage :: START
4219:
4220: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4221: fnd_log.string(fnd_log.level_statement,L_DEBUG,' p_stage_id: '||p_stage_id);
4222: END IF;
4223: parallel_stage_ids(p_stage_id) := true;
4224:
4225:
4229: l_process_stage_id := parallel_stage_ids.FIRST;
4230: WHILE l_process_stage_id IS NOT NULL
4231: LOOP
4232:
4233: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4234: fnd_log.string(fnd_log.level_statement,L_DEBUG,' l_process_stage_id: '||l_process_stage_id);
4235: END IF;
4236:
4237: OPEN c_get_user_upd_details (l_process_stage_id);
4230: WHILE l_process_stage_id IS NOT NULL
4231: LOOP
4232:
4233: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4234: fnd_log.string(fnd_log.level_statement,L_DEBUG,' l_process_stage_id: '||l_process_stage_id);
4235: END IF;
4236:
4237: OPEN c_get_user_upd_details (l_process_stage_id);
4238: FETCH c_get_user_upd_details INTO l_usr_upd_dets;
4240: IF c_get_user_upd_details%FOUND AND l_usr_upd_dets.IS_USER_UPDATED = 'Y' THEN
4241: CLOSE c_get_user_upd_details;
4242:
4243: IF (l_log_statement >= l_log_current_level) THEN
4244: fnd_log.string(l_log_statement,L_DEBUG,'IS_USER_UPDATED '||l_usr_upd_dets.IS_USER_UPDATED);
4245: END IF;
4246:
4247: UPDATE_SUCC_STG_START_DATES(
4248: p_stage_id => l_usr_upd_dets.SUB_STAGE_ID,
4253: x_return_status => l_return_status,
4254: x_msg_count => l_msg_count,
4255: x_msg_data => l_msg_data);
4256:
4257: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4258: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_SUCC_STG_START_DATES - l_return_status : '||l_return_status);
4259: END IF;
4260:
4261: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4254: x_msg_count => l_msg_count,
4255: x_msg_data => l_msg_data);
4256:
4257: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4258: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_SUCC_STG_START_DATES - l_return_status : '||l_return_status);
4259: END IF;
4260:
4261: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4262: x_msg_count := l_msg_count;
4286: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4287: END IF;
4288:
4289: IF (l_log_procedure >= l_log_current_level) THEN
4290: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
4291: END IF;
4292:
4293: EXCEPTION
4294: WHEN Fnd_Api.G_EXC_ERROR THEN
4394:
4395: SAVEPOINT UPDATE_STG_RUL_HIERARCHY;
4396:
4397: IF (l_log_procedure >= l_log_current_level) THEN
4398: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
4399: END IF;
4400:
4401: -- Initialize API return status to success
4402: -- Initialize message list if p_init_msg_list is set to TRUE.
4438: l_is_stages_parallel := true;
4439: END IF;
4440:
4441: IF (l_log_statement >= l_log_current_level) THEN
4442: fnd_log.string(l_log_statement,L_DEBUG,' For Stage ' || l_update_stage_id ||' Prev Stage Number is ' || l_prev_stage_id);
4443: END IF;
4444:
4445: --Update previous stage number and planned start date to previous stage end date or visit start date
4446: IF NOT l_is_stages_parallel THEN
4455: RAISE Fnd_Api.G_EXC_ERROR;
4456: END IF;
4457:
4458: IF (l_log_statement >= l_log_current_level) THEN
4459: fnd_log.string(l_log_statement,L_DEBUG,' Previous stage num is '||l_stg_stage_number);
4460: fnd_log.string(l_log_statement,L_DEBUG,' Previous stage end date is '||l_prev_stage_planned_end_date);
4461: END IF;
4462:
4463: OPEN c_get_prev_stage_dets(l_update_stage_id);
4456: END IF;
4457:
4458: IF (l_log_statement >= l_log_current_level) THEN
4459: fnd_log.string(l_log_statement,L_DEBUG,' Previous stage num is '||l_stg_stage_number);
4460: fnd_log.string(l_log_statement,L_DEBUG,' Previous stage end date is '||l_prev_stage_planned_end_date);
4461: END IF;
4462:
4463: OPEN c_get_prev_stage_dets(l_update_stage_id);
4464: FETCH c_get_prev_stage_dets INTO l_prev_stg_num;
4464: FETCH c_get_prev_stage_dets INTO l_prev_stg_num;
4465: CLOSE c_get_prev_stage_dets;
4466:
4467: IF (l_log_statement >= l_log_current_level) THEN
4468: fnd_log.string(l_log_statement,L_DEBUG,' l_prev_stg_num is '||l_prev_stg_num);
4469: END IF;
4470:
4471: --IF l_prev_stg_num is NULL THEN
4472:
4477: LAST_UPDATED_BY = Fnd_Global.USER_ID,
4478: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
4479: where STAGE_ID = l_update_stage_id;
4480: IF (l_log_statement >= l_log_current_level) THEN
4481: fnd_log.string(l_log_statement,L_DEBUG,l_update_stage_id||' stage id updated');
4482: END IF;*/
4483:
4484: IF l_prev_stg_num is NOT NULL THEN -- Two predessors validation
4485: IF l_prev_stg_num <> l_stg_stage_number THEN
4508: x_msg_data => l_msg_data
4509: );
4510:
4511: IF (l_log_statement >= l_log_current_level) THEN
4512: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_RULES - l_return_status : '||l_return_status);
4513: END IF;
4514:
4515: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4516: x_msg_count := l_msg_count;
4536:
4537:
4538: --------------------------End of API Body---------------------------------------
4539: IF (l_log_statement >= l_log_current_level) THEN
4540: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.PROCESS_STAGE_DATES - l_return_status : '||l_return_status);
4541: END IF;
4542:
4543: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4544: x_msg_count := l_msg_count;
4562: x_return_status => l_return_status,
4563: x_msg_count => l_msg_count,
4564: x_msg_data => l_msg_data);
4565:
4566: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4567: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_SUCC_STG_START_DATES - l_return_status : '||l_return_status);
4568: END IF;
4569:
4570: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4563: x_msg_count => l_msg_count,
4564: x_msg_data => l_msg_data);
4565:
4566: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4567: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_SUCC_STG_START_DATES - l_return_status : '||l_return_status);
4568: END IF;
4569:
4570: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4571: x_msg_count := l_msg_count;
4591: COMMIT;
4592: END IF;
4593:
4594: IF (l_log_procedure >= l_log_current_level) THEN
4595: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
4596: END IF;
4597:
4598: EXCEPTION
4599: WHEN Fnd_Api.G_EXC_ERROR THEN
4727:
4728: BEGIN
4729:
4730: IF (l_log_procedure >= l_log_current_level) THEN
4731: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
4732: END IF;
4733:
4734: --Initialize API return status to success
4735: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4742: l_dept_id := l_visit_limited_dets.DEPARTMENT_ID;
4743: l_visit_start_date := l_visit_limited_dets.START_DATE_TIME;
4744:
4745: IF (l_log_statement >= l_log_current_level) THEN
4746: fnd_log.string(l_log_statement,L_DEBUG,' Visit Id : '||p_visit_id);
4747: fnd_log.string(l_log_statement,L_DEBUG,' Dept Id : '||l_dept_id);
4748: END IF;
4749:
4750: IF l_visit_limited_dets.status_code='DRAFT' AND (l_dept_id IS NULL OR l_visit_start_date IS NULL OR (l_visit_limited_dets.visit_type_code IS NULL AND l_visit_limited_dets.IS_OLD_VISIT <> 'Y')) THEN
4743: l_visit_start_date := l_visit_limited_dets.START_DATE_TIME;
4744:
4745: IF (l_log_statement >= l_log_current_level) THEN
4746: fnd_log.string(l_log_statement,L_DEBUG,' Visit Id : '||p_visit_id);
4747: fnd_log.string(l_log_statement,L_DEBUG,' Dept Id : '||l_dept_id);
4748: END IF;
4749:
4750: IF l_visit_limited_dets.status_code='DRAFT' AND (l_dept_id IS NULL OR l_visit_start_date IS NULL OR (l_visit_limited_dets.visit_type_code IS NULL AND l_visit_limited_dets.IS_OLD_VISIT <> 'Y')) THEN
4751:
4762: ELSE
4763:
4764: IF l_dept_id IS NULL THEN
4765: IF (l_log_statement >= l_log_current_level) THEN
4766: fnd_log.string(l_log_statement,L_DEBUG,' No Dept Id' );
4767: END IF;
4768: Fnd_Message.SET_NAME('AHL','AHL_VWP_VST_NO_DEP');
4769: Fnd_Msg_Pub.ADD;
4770: RAISE Fnd_Api.G_EXC_ERROR;
4771: END IF;
4772:
4773: IF l_visit_start_date IS NULL THEN
4774: IF (l_log_statement >= l_log_current_level) THEN
4775: fnd_log.string(l_log_statement,L_DEBUG,' No visit start date' );
4776: END IF;
4777: Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_ST_DATE_NULL');
4778: Fnd_Msg_Pub.ADD;
4779: RAISE Fnd_Api.G_EXC_ERROR;
4780: END IF;
4781:
4782: IF ( l_visit_limited_dets.visit_type_code IS NULL AND l_visit_limited_dets.IS_OLD_VISIT <> 'Y' ) THEN
4783: IF (l_log_statement >= l_log_current_level) THEN
4784: fnd_log.string(l_log_statement,L_DEBUG,' No visit type code' );
4785: END IF;
4786: Fnd_Message.SET_NAME('AHL','AHL_VWP_VST_TYPE_MAND');
4787: Fnd_Msg_Pub.ADD;
4788: RAISE Fnd_Api.G_EXC_ERROR;
4794: FETCH c_stage_rules_count INTO l_relations_count;
4795: CLOSE c_stage_rules_count;
4796:
4797: IF (l_log_statement >= l_log_current_level) THEN
4798: fnd_log.string(l_log_statement,L_DEBUG,' Stage rules count is '||l_relations_count);
4799: END IF;
4800:
4801: IF l_relations_count IS NULL OR l_relations_count=0 THEN
4802: --Update stages start date to null and return , since there is no rules defined between any of stages
4815:
4816: -- PRAKKUM :: 06-APR-2011 :: Bug 13711800 :: START
4817:
4818: IF (l_log_statement >= l_log_current_level) THEN
4819: fnd_log.string(l_log_statement,L_DEBUG,'Before calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_DATES ');
4820: END IF;
4821:
4822: VALIDATE_STAGE_DATES( p_visit_id => p_visit_id,
4823: x_return_status => l_return_status,
4824: x_msg_count => l_msg_count,
4825: x_msg_data => l_msg_data);
4826:
4827: IF (l_log_statement >= l_log_current_level) THEN
4828: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_DATES - l_return_status : '||l_return_status);
4829: END IF;
4830:
4831: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4832: x_msg_count := l_msg_count;
4842: return;--No relations to do process
4843: END IF;
4844:
4845: IF (l_log_statement >= l_log_current_level) THEN
4846: fnd_log.string(l_log_statement,L_DEBUG,' Before fetch stage links');
4847: END IF;
4848:
4849: SELECT distinct STG_LK.subject_ID, STG_LK.object_ID, STG_LK.RELATION_TYPE
4850: BULK COLLECT INTO l_stages_links
4853: WHERE ( STG.STAGE_ID = STG_LK.subject_ID OR STG.STAGE_ID = STG_LK.object_ID )
4854: AND STG.VISIT_ID = p_visit_id;
4855:
4856: IF (l_log_statement >= l_log_current_level) THEN
4857: fnd_log.string(l_log_statement,L_DEBUG,' Stage links fetched');
4858: END IF;
4859:
4860: FOR l_stage_dets IN c_stage_dets(p_visit_id)
4861: LOOP
4862: l_stages_dets(l_stage_dets.STAGE_ID) := l_stage_dets; -- Indexed By stage id
4863: END LOOP;
4864:
4865: IF (l_log_statement >= l_log_current_level) THEN
4866: fnd_log.string(l_log_statement,L_DEBUG,' Stage details fetched');
4867: END IF;
4868:
4869: l_relations_count := l_stages_links.count;
4870: FOR I in 1 .. l_relations_count
4873:
4874: IF 'PARALLEL' = l_current_rel_rec.RELATION_TYPE THEN
4875:
4876: IF (l_log_statement >= l_log_current_level) THEN
4877: fnd_log.string(l_log_statement,L_DEBUG,' Relation'||l_current_rel_rec.subject_ID||':'||l_current_rel_rec.object_ID||' is PARALLEL');
4878: END IF;
4879:
4880: l_stage_relations_lookup(l_current_rel_rec.subject_ID||':'||l_current_rel_rec.object_ID) := 'PARALLEL'; -- PARALLEL
4881: l_stage_relations_lookup(l_current_rel_rec.object_ID||':'||l_current_rel_rec.subject_ID) := 'PARALLEL'; -- PARALLEL
4882:
4883: ELSIF 'BEFORE' = l_current_rel_rec.RELATION_TYPE THEN
4884:
4885: IF (l_log_statement >= l_log_current_level) THEN
4886: fnd_log.string(l_log_statement,L_DEBUG,' Relation'||l_current_rel_rec.subject_ID||':'||l_current_rel_rec.object_ID||' is BEFORE');
4887: END IF;
4888:
4889: l_stage_relations_lookup(l_current_rel_rec.subject_ID||':'||l_current_rel_rec.object_ID) := 'BEFORE'; -- BEFORE
4890:
4891: END IF;
4892: END LOOP;
4893:
4894: IF (l_log_statement >= l_log_current_level) THEN
4895: fnd_log.string(l_log_statement,L_DEBUG,' Stage relations populated as a lookup');
4896: fnd_log.string(l_log_statement,L_DEBUG,' Before call to process stage rules');
4897: END IF;
4898:
4899: CALC_STAGE_START_DURATIONS (
4892: END LOOP;
4893:
4894: IF (l_log_statement >= l_log_current_level) THEN
4895: fnd_log.string(l_log_statement,L_DEBUG,' Stage relations populated as a lookup');
4896: fnd_log.string(l_log_statement,L_DEBUG,' Before call to process stage rules');
4897: END IF;
4898:
4899: CALC_STAGE_START_DURATIONS (
4900: p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
4904: x_msg_count => l_msg_count,
4905: x_msg_data => l_msg_data);
4906:
4907: IF (l_log_statement >= l_log_current_level) THEN
4908: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.CALC_STAGE_START_DURATIONS - l_return_status : '||l_return_status);
4909: END IF;
4910:
4911: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4912: x_msg_count := l_msg_count;
4918: END IF;
4919: END IF;
4920:
4921: IF (l_log_statement >= l_log_current_level) THEN
4922: fnd_log.string(l_log_statement,L_DEBUG,' After call to process stage rules');
4923: END IF;
4924:
4925: IF p_subject_id is NULL AND p_object_id is NULL AND p_relation_type is NULL THEN
4926:
4924:
4925: IF p_subject_id is NULL AND p_object_id is NULL AND p_relation_type is NULL THEN
4926:
4927: IF (l_log_statement >= l_log_current_level) THEN
4928: fnd_log.string(l_log_statement,L_DEBUG,' Set valid stages list');
4929: END IF;
4930:
4931: l_valid_stages := l_stages_dets; -- All stage are valid for updation
4932:
4930:
4931: l_valid_stages := l_stages_dets; -- All stage are valid for updation
4932:
4933: IF (l_log_statement >= l_log_current_level) THEN
4934: fnd_log.string(l_log_statement,L_DEBUG,' Before call to update stage dates');
4935: END IF;
4936:
4937: UPDATE_STAGE_DATES (
4938: p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
4945: x_msg_count => l_msg_count,
4946: x_msg_data => l_msg_data);
4947:
4948: IF (l_log_statement >= l_log_current_level) THEN
4949: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DATES - l_return_status : '||l_return_status);
4950: END IF;
4951:
4952: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4953: x_msg_count := l_msg_count;
4959: END IF;
4960: END IF;
4961:
4962: IF (l_log_statement >= l_log_current_level) THEN
4963: fnd_log.string(l_log_statement,L_DEBUG,' After call to update stage dates');
4964: END IF;
4965:
4966: ELSIF p_subject_id is NOT NULL AND p_object_id is NOT NULL AND p_relation_type is NOT NULL THEN
4967:
4967:
4968: IF 'PARALLEL' = p_relation_type AND UPPER(p_operation_flag) = 'D' THEN -- On deletion of parallel rule
4969:
4970: IF (l_log_statement >= l_log_current_level) THEN
4971: fnd_log.string(l_log_statement,L_DEBUG,' Before call to get valid stages list');
4972: END IF;
4973:
4974: GET_VALID_STAGES_LIST ( p_stage_id => p_subject_id,
4975: p_all_stage_details => l_stages_dets,
4979: x_msg_count => l_msg_count,
4980: x_msg_data => l_msg_data);
4981:
4982: IF (l_log_statement >= l_log_current_level) THEN
4983: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.GET_VALID_STAGES_LIST - l_return_status : '||l_return_status);
4984: END IF;
4985:
4986: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4987: x_msg_count := l_msg_count;
4993: END IF;
4994: END IF;
4995:
4996: IF (l_log_statement >= l_log_current_level) THEN
4997: fnd_log.string(l_log_statement,L_DEBUG,' After call to get valid stages list');
4998: fnd_log.string(l_log_statement,L_DEBUG,' Before call to update stage dates');
4999: END IF;
5000:
5001: UPDATE_STAGE_DATES ( p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
4994: END IF;
4995:
4996: IF (l_log_statement >= l_log_current_level) THEN
4997: fnd_log.string(l_log_statement,L_DEBUG,' After call to get valid stages list');
4998: fnd_log.string(l_log_statement,L_DEBUG,' Before call to update stage dates');
4999: END IF;
5000:
5001: UPDATE_STAGE_DATES ( p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
5002: p_base_stage_id => p_subject_id,
5008: x_msg_count => l_msg_count,
5009: x_msg_data => l_msg_data);
5010:
5011: IF (l_log_statement >= l_log_current_level) THEN
5012: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DATES - l_return_status : '||l_return_status);
5013: fnd_log.string(l_log_statement,L_DEBUG,'After call to update stage dates');
5014: END IF;
5015:
5016: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5009: x_msg_data => l_msg_data);
5010:
5011: IF (l_log_statement >= l_log_current_level) THEN
5012: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DATES - l_return_status : '||l_return_status);
5013: fnd_log.string(l_log_statement,L_DEBUG,'After call to update stage dates');
5014: END IF;
5015:
5016: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5017: x_msg_count := l_msg_count;
5025:
5026: l_valid_stages := l_empty_details;
5027:
5028: IF (l_log_statement >= l_log_current_level) THEN
5029: fnd_log.string(l_log_statement,L_DEBUG,'Before call to get valid stages list');
5030: END IF;
5031:
5032: GET_VALID_STAGES_LIST ( p_stage_id => p_object_id,
5033: p_all_stage_details => l_stages_dets,
5037: x_msg_count => l_msg_count,
5038: x_msg_data => l_msg_data);
5039:
5040: IF (l_log_statement >= l_log_current_level) THEN
5041: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.GET_VALID_STAGES_LIST - l_return_status : '||l_return_status);
5042: END IF;
5043:
5044: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5045: x_msg_count := l_msg_count;
5051: END IF;
5052: END IF;
5053:
5054: IF (l_log_statement >= l_log_current_level) THEN
5055: fnd_log.string(l_log_statement,L_DEBUG,'After call to get valid stages list');
5056: fnd_log.string(l_log_statement,L_DEBUG,'Before call to update stage dates');
5057: END IF;
5058:
5059: UPDATE_STAGE_DATES ( p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
5052: END IF;
5053:
5054: IF (l_log_statement >= l_log_current_level) THEN
5055: fnd_log.string(l_log_statement,L_DEBUG,'After call to get valid stages list');
5056: fnd_log.string(l_log_statement,L_DEBUG,'Before call to update stage dates');
5057: END IF;
5058:
5059: UPDATE_STAGE_DATES ( p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
5060: p_base_stage_id => p_object_id,
5065: x_return_status => l_return_status,
5066: x_msg_count => l_msg_count,
5067: x_msg_data => l_msg_data);
5068: IF (l_log_statement >= l_log_current_level) THEN
5069: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DATES - l_return_status : '||l_return_status);
5070: END IF;
5071:
5072: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5073: x_msg_count := l_msg_count;
5079: END IF;
5080: END IF;
5081:
5082: IF (l_log_statement >= l_log_current_level) THEN
5083: fnd_log.string(l_log_statement,L_DEBUG,'After call to update stage dates');
5084: END IF;
5085:
5086: ELSE
5087:
5085:
5086: ELSE
5087:
5088: IF (l_log_statement >= l_log_current_level) THEN
5089: fnd_log.string(l_log_statement,L_DEBUG,'Before call to get valid stages list');
5090: END IF;
5091: GET_VALID_STAGES_LIST ( p_stage_id => p_object_id,
5092: p_all_stage_details => l_stages_dets,
5093: p_stage_relations => l_stage_relations_lookup,
5095: x_return_status => l_return_status,
5096: x_msg_count => l_msg_count,
5097: x_msg_data => l_msg_data);
5098: IF (l_log_statement >= l_log_current_level) THEN
5099: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.GET_VALID_STAGES_LIST - l_return_status : '||l_return_status);
5100: END IF;
5101:
5102: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5103: x_msg_count := l_msg_count;
5109: END IF;
5110: END IF;
5111:
5112: IF (l_log_statement >= l_log_current_level) THEN
5113: fnd_log.string(l_log_statement,L_DEBUG,'After call to get valid stages list');
5114: fnd_log.string(l_log_statement,L_DEBUG,'Before call to update stage dates');
5115: END IF;
5116:
5117: UPDATE_STAGE_DATES ( p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
5110: END IF;
5111:
5112: IF (l_log_statement >= l_log_current_level) THEN
5113: fnd_log.string(l_log_statement,L_DEBUG,'After call to get valid stages list');
5114: fnd_log.string(l_log_statement,L_DEBUG,'Before call to update stage dates');
5115: END IF;
5116:
5117: UPDATE_STAGE_DATES ( p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
5118: p_base_stage_id => p_object_id,
5123: x_return_status => l_return_status,
5124: x_msg_count => l_msg_count,
5125: x_msg_data => l_msg_data);
5126: IF (l_log_statement >= l_log_current_level) THEN
5127: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DATES - l_return_status : '||l_return_status);
5128: END IF;
5129:
5130: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5131: x_msg_count := l_msg_count;
5137: END IF;
5138: END IF;
5139:
5140: IF (l_log_statement >= l_log_current_level) THEN
5141: fnd_log.string(l_log_statement,L_DEBUG,'After call to update stage dates');
5142: END IF;
5143:
5144: END IF;
5145:
5145:
5146: ELSE
5147:
5148: IF (l_log_statement >= l_log_current_level) THEN
5149: fnd_log.string(l_log_statement,L_DEBUG,'Invalid stage rule');
5150: END IF;
5151:
5152: Fnd_Message.SET_NAME('AHL','AHL_VWP_MAND_STG_RULE');
5153: Fnd_Msg_Pub.ADD;
5157:
5158: -- PRAKKUM :: 13-FEB-2011 :: Bug 13711800 :: START
5159:
5160: IF (l_log_statement >= l_log_current_level) THEN
5161: fnd_log.string(l_log_statement,L_DEBUG,'Before calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_DATES ');
5162: END IF;
5163:
5164: VALIDATE_STAGE_DATES( p_visit_id => p_visit_id,
5165: x_return_status => l_return_status,
5166: x_msg_count => l_msg_count,
5167: x_msg_data => l_msg_data);
5168:
5169: IF (l_log_statement >= l_log_current_level) THEN
5170: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_DATES - l_return_status : '||l_return_status);
5171: END IF;
5172:
5173: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5174: x_msg_count := l_msg_count;
5193: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5194: END IF;
5195:
5196: IF (l_log_procedure >= l_log_current_level) THEN
5197: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
5198: END IF;
5199:
5200: EXCEPTION
5201: WHEN Fnd_Api.G_EXC_ERROR THEN
5262:
5263: BEGIN
5264:
5265: IF (l_log_procedure >= l_log_current_level) THEN
5266: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
5267: END IF;
5268:
5269: --Initialize API return status to success
5270: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5273: l_dirtyFlag := false;
5274: l_relations_count := p_relations.COUNT;
5275:
5276: IF (l_log_statement >= l_log_current_level) THEN
5277: fnd_log.string(l_log_statement,L_DEBUG,'l_relations_count ' ||l_relations_count );
5278: END IF;
5279:
5280: FOR I in 1 .. l_relations_count
5281: LOOP
5322:
5323: IF l_dirtyFlag THEN
5324:
5325: IF (l_log_statement >= l_log_current_level) THEN
5326: fnd_log.string(l_log_statement,L_DEBUG,'Before Recursive call to CALC_STAGE_START_DURATIONS');
5327: END IF;
5328:
5329: CALC_STAGE_START_DURATIONS (
5330: p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
5334: x_msg_count => l_msg_count,
5335: x_msg_data => l_msg_data);
5336:
5337: IF (l_log_statement >= l_log_current_level) THEN
5338: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.CALC_STAGE_START_DURATIONS - l_return_status : '||l_return_status);
5339: END IF;
5340:
5341: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5342: x_msg_count := l_msg_count;
5348: END IF;
5349: END IF;
5350:
5351: IF (l_log_statement >= l_log_current_level) THEN
5352: fnd_log.string(l_log_statement,L_DEBUG,'After Recursive call to CALC_STAGE_START_DURATIONS');
5353: END IF;
5354:
5355: END IF;
5356:
5362: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5363: END IF;
5364:
5365: IF (l_log_procedure >= l_log_current_level) THEN
5366: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
5367: END IF;
5368:
5369: EXCEPTION
5370: WHEN Fnd_Api.G_EXC_ERROR THEN
5426:
5427: BEGIN
5428:
5429: IF (l_log_procedure >= l_log_current_level) THEN
5430: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
5431: END IF;
5432:
5433: --Initialize API return status to success
5434: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5434: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5435: l_return_status := x_return_status;
5436:
5437: IF (l_log_statement >= l_log_current_level) THEN
5438: fnd_log.string(l_log_statement,L_DEBUG,'Stage count '||p_all_stage_details.count);
5439: fnd_log.string(l_log_statement,L_DEBUG,'Valid Stages count before '||p_x_valid_stages.count);
5440: fnd_log.string(l_log_statement,L_DEBUG,'Stage id '||p_stage_id);
5441: END IF;
5442:
5435: l_return_status := x_return_status;
5436:
5437: IF (l_log_statement >= l_log_current_level) THEN
5438: fnd_log.string(l_log_statement,L_DEBUG,'Stage count '||p_all_stage_details.count);
5439: fnd_log.string(l_log_statement,L_DEBUG,'Valid Stages count before '||p_x_valid_stages.count);
5440: fnd_log.string(l_log_statement,L_DEBUG,'Stage id '||p_stage_id);
5441: END IF;
5442:
5443: p_x_valid_stages(p_stage_id) := p_all_stage_details(p_stage_id);
5436:
5437: IF (l_log_statement >= l_log_current_level) THEN
5438: fnd_log.string(l_log_statement,L_DEBUG,'Stage count '||p_all_stage_details.count);
5439: fnd_log.string(l_log_statement,L_DEBUG,'Valid Stages count before '||p_x_valid_stages.count);
5440: fnd_log.string(l_log_statement,L_DEBUG,'Stage id '||p_stage_id);
5441: END IF;
5442:
5443: p_x_valid_stages(p_stage_id) := p_all_stage_details(p_stage_id);
5444:
5442:
5443: p_x_valid_stages(p_stage_id) := p_all_stage_details(p_stage_id);
5444:
5445: IF (l_log_statement >= l_log_current_level) THEN
5446: fnd_log.string(l_log_statement,L_DEBUG,'Valid Stages count after '||p_x_valid_stages.count);
5447: END IF;
5448:
5449: l_obj_stage_id := p_all_stage_details.FIRST;
5450: WHILE l_obj_stage_id IS NOT NULL
5450: WHILE l_obj_stage_id IS NOT NULL
5451: LOOP
5452:
5453: IF (l_log_statement >= l_log_current_level) THEN
5454: fnd_log.string(l_log_statement,L_DEBUG,'In iteration - stage id '||l_obj_stage_id);
5455: END IF;
5456:
5457: IF (NOT p_x_valid_stages.EXISTS(l_obj_stage_id)) THEN
5458:
5457: IF (NOT p_x_valid_stages.EXISTS(l_obj_stage_id)) THEN
5458:
5459: l_is_valid_stage := false;
5460: IF (l_log_statement >= l_log_current_level) THEN
5461: fnd_log.string(l_log_statement,L_DEBUG,'Processing relation '||p_stage_id||':'||l_obj_stage_id);
5462: END IF;
5463:
5464: IF p_stage_relations.EXISTS(p_stage_id||':'||l_obj_stage_id) THEN
5465: IF (l_log_statement >= l_log_current_level) THEN
5462: END IF;
5463:
5464: IF p_stage_relations.EXISTS(p_stage_id||':'||l_obj_stage_id) THEN
5465: IF (l_log_statement >= l_log_current_level) THEN
5466: fnd_log.string(l_log_statement,L_DEBUG,'Relation Exisits');
5467: END IF;
5468: IF p_stage_relations(p_stage_id||':'||l_obj_stage_id)='BEFORE' THEN -- BEFORE RELATION
5469: l_is_valid_stage := true;
5470: ELSIF p_stage_relations(p_stage_id||':'||l_obj_stage_id)='PARALLEL' THEN -- PARALLEL RELATION
5473: END IF;
5474:
5475: IF l_is_valid_stage THEN
5476: IF (l_log_statement >= l_log_current_level) THEN
5477: fnd_log.string(l_log_statement,L_DEBUG,'Before Recursive call to GET_VALID_STAGES_LIST');
5478: END IF;
5479: -- Make call only if stage is not processed
5480: GET_VALID_STAGES_LIST ( p_stage_id => l_obj_stage_id,
5481: p_all_stage_details => p_all_stage_details,
5484: x_return_status => l_return_status,
5485: x_msg_count => l_msg_count,
5486: x_msg_data => l_msg_data);
5487: IF (l_log_statement >= l_log_current_level) THEN
5488: fnd_log.string(l_log_statement,L_DEBUG,'After Recursive call to GET_VALID_STAGES_LIST');
5489: END IF;
5490:
5491: IF (l_log_statement >= l_log_current_level) THEN
5492: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.GET_VALID_STAGES_LIST - l_return_status : '||l_return_status);
5488: fnd_log.string(l_log_statement,L_DEBUG,'After Recursive call to GET_VALID_STAGES_LIST');
5489: END IF;
5490:
5491: IF (l_log_statement >= l_log_current_level) THEN
5492: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.GET_VALID_STAGES_LIST - l_return_status : '||l_return_status);
5493: END IF;
5494:
5495: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5496: x_msg_count := l_msg_count;
5517: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5518: END IF;
5519:
5520: IF (l_log_procedure >= l_log_current_level) THEN
5521: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
5522: END IF;
5523:
5524: EXCEPTION
5525: WHEN Fnd_Api.G_EXC_ERROR THEN
5585:
5586: BEGIN
5587:
5588: IF (l_log_procedure >= l_log_current_level) THEN
5589: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
5590: END IF;
5591:
5592: --Initialize API return status to success
5593: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5593: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5594: l_return_status := x_return_status;
5595:
5596: IF (l_log_statement >= l_log_current_level) THEN
5597: fnd_log.string(l_log_statement,L_DEBUG,'p_base_stage_id '||p_base_stage_id);
5598: END IF;
5599:
5600: -- p_base_stage_id is null if this is called to update entire visit stage start dates
5601: IF p_base_stage_id = Fnd_Api.g_miss_num OR p_base_stage_id IS NULL THEN
5604: l_base_start_time := p_valid_stages(p_base_stage_id).CALCULATED_START_TIME;
5605: END IF;
5606:
5607: IF (l_log_statement >= l_log_current_level) THEN
5608: fnd_log.string(l_log_statement,L_DEBUG,'Base start time '||l_base_start_time);
5609: END IF;
5610:
5611: l_stage_id := p_valid_stages.FIRST;
5612: WHILE l_stage_id IS NOT NULL
5618: -- PRAKKUM :: 23-FEB-2011 :: Bug 13711800 :: Fix for Operational Visits
5619: l_stage_end_date := Compute_Stage_End_Date(p_visit_id, null, l_current_stage_rec.CALCULATED_START_TIME, p_dept_id, l_current_stage_rec.DURATION);
5620:
5621: IF (l_log_statement >= l_log_current_level) THEN
5622: fnd_log.string(l_log_statement,L_DEBUG,'SD '||l_stage_start_date||' ED '||l_stage_end_date ||' for stage id '||l_stage_id);
5623: END IF;
5624:
5625: -- Stage dates are updated
5626: UPDATE AHL_VWP_STAGES_B SET object_version_number=object_version_number+1,
5630: LAST_UPDATED_BY = Fnd_Global.USER_ID,
5631: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
5632: WHERE STAGE_ID = l_stage_id;
5633: IF (l_log_statement >= l_log_current_level) THEN
5634: fnd_log.string(l_log_statement,L_DEBUG,l_stage_id||' stage id updated');
5635: END IF;
5636:
5637: UPDATE_STAGES_OVN_COUNT(
5638: p_stage_id => p_base_stage_id,
5641: x_return_status => l_return_status,
5642: x_msg_count => l_msg_count,
5643: x_msg_data => l_msg_data);
5644:
5645: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5646: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGES_OVN_COUNT - l_return_status : '||l_return_status);
5647: END IF;
5648:
5649: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5642: x_msg_count => l_msg_count,
5643: x_msg_data => l_msg_data);
5644:
5645: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5646: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGES_OVN_COUNT - l_return_status : '||l_return_status);
5647: END IF;
5648:
5649: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5650: x_msg_count := l_msg_count;
5667: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5668: END IF;
5669:
5670: IF (l_log_procedure >= l_log_current_level) THEN
5671: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
5672: END IF;
5673:
5674: EXCEPTION
5675: WHEN Fnd_Api.G_EXC_ERROR THEN
5769:
5770: BEGIN
5771:
5772: IF (l_log_procedure >= l_log_current_level) THEN
5773: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
5774: END IF;
5775:
5776: --Initialize API return status to success
5777: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5796: IF length(parallelStagesInStr)>1 THEN
5797:
5798: parallelStagesInStr := SUBSTR(parallelStagesInStr, 2);
5799: IF (l_log_statement >= l_log_current_level) THEN
5800: fnd_log.string(l_log_statement,L_DEBUG,'parallelStagesInStr is :' ||parallelStagesInStr );
5801: END IF;
5802:
5803: -- Get max planned end date from all parallel stage before stages
5804: l_query_str :=' select MAX(stg.PLANNED_END_DATE) from ahl_stage_links, ahl_vwp_stages_b stg '||
5805: ' where stg.STAGE_ID = subject_id AND relation_type = ''BEFORE'' '||
5806: ' and object_id in (' || parallelStagesInStr ||')';
5807:
5808: IF (l_log_statement >= l_log_current_level) THEN
5809: fnd_log.string(l_log_statement,L_DEBUG,'l_query_str is :' ||l_query_str);
5810: END IF;
5811:
5812: EXECUTE IMMEDIATE l_query_str into l_max_planned_end_date ;
5813:
5811:
5812: EXECUTE IMMEDIATE l_query_str into l_max_planned_end_date ;
5813:
5814: IF (l_log_statement >= l_log_current_level) THEN
5815: fnd_log.string(l_log_statement,L_DEBUG,'l_max_planned_end_date is :' ||l_max_planned_end_date );
5816: END IF;
5817:
5818: -- PRAKKUM :: 09/05/2012 :: Bug 13965577
5819: -- Get max earliest start date from all parallel stages
5821: ' MAX(stg.EARLIEST_START_DATE) MAX_EARLIEST_START_DATE from ahl_vwp_stages_b stg '||
5822: ' where stg.STAGE_ID in (' || parallelStagesInStr ||')';
5823:
5824: IF (l_log_statement >= l_log_current_level) THEN
5825: fnd_log.string(l_log_statement,L_DEBUG,'l_query_str is :' ||l_query_str);
5826: END IF;
5827:
5828: EXECUTE IMMEDIATE l_query_str into l_max_earliest_start_date ;
5829:
5827:
5828: EXECUTE IMMEDIATE l_query_str into l_max_earliest_start_date ;
5829:
5830: IF (l_log_statement >= l_log_current_level) THEN
5831: fnd_log.string(l_log_statement,L_DEBUG,'l_max_planned_start_date is :' ||l_max_earliest_start_date );
5832: END IF;
5833:
5834: -- Get max planned start date from all parallel stages considering if earliest start date exists
5835: l_query_str :=' select case when '||
5837: ' else MAX(stg.PLANNED_START_DATE) end MIN_PLANNED_START_DATE from ahl_vwp_stages_b stg '||
5838: ' where stg.STAGE_ID in (' || parallelStagesInStr ||')';
5839:
5840: IF (l_log_statement >= l_log_current_level) THEN
5841: fnd_log.string(l_log_statement,L_DEBUG,'l_query_str is :' ||l_query_str);
5842: END IF;
5843:
5844: EXECUTE IMMEDIATE l_query_str into l_max_planned_start_date ;
5845:
5843:
5844: EXECUTE IMMEDIATE l_query_str into l_max_planned_start_date ;
5845:
5846: IF (l_log_statement >= l_log_current_level) THEN
5847: fnd_log.string(l_log_statement,L_DEBUG,'l_max_planned_start_date is :' ||l_max_planned_start_date );
5848: END IF;
5849:
5850: -- PRAKKUM :: 09/05/2012 :: Bug 13965577
5851: -- Get updated planned end date of a stage is greater than max planned start date that calc before then update all parallel stage start date to new date
5852: IF l_max_planned_end_date IS NOT NULL AND p_planned_end_date <> NVL(l_max_earliest_start_date,(p_planned_end_date-1)) THEN
5853:
5854:
5855: IF (l_log_statement >= l_log_current_level) THEN
5856: fnd_log.string(l_log_statement,L_DEBUG,'l_max_planned_end_date is :' ||TO_CHAR(l_max_planned_end_date,'DD-MON-YYYY HH24:MI:SS') );
5857: END IF;
5858: -- PRAKKUM :: 09/05/2012 :: Bug 13965577 :: START
5859: --Find latest date among planned start date of llel stages and updated date and planned end date of llel stage before stages
5860: IF l_max_earliest_start_date >= l_max_planned_end_date THEN
5872: END IF;
5873: -- PRAKKUM :: 09/05/2012 :: Bug 13965577 :: END
5874:
5875: IF (l_log_statement >= l_log_current_level) THEN
5876: fnd_log.string(l_log_statement,L_DEBUG,'l_planned_end_date is :' ||TO_CHAR(l_planned_end_date,'DD-MON-YYYY HH24:MI:SS') );
5877: END IF;
5878:
5879:
5880: l_query_str :=' UPDATE AHL_VWP_STAGES_B set object_version_number=object_version_number+1, '||
5884: ' LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID '||
5885: ' WHERE STAGE_ID IN ('||parallelStagesInStr||') '||
5886: ' AND PLANNED_START_DATE = :p_max_planned_start_date';
5887: IF (l_log_statement >= l_log_current_level) THEN
5888: fnd_log.string(l_log_statement,L_DEBUG,parallelStagesInStr||' stage id updated');
5889: END IF;
5890:
5891: IF (l_log_statement >= l_log_current_level) THEN
5892: fnd_log.string(l_log_statement,L_DEBUG,'l_query_str is :' ||l_query_str);
5888: fnd_log.string(l_log_statement,L_DEBUG,parallelStagesInStr||' stage id updated');
5889: END IF;
5890:
5891: IF (l_log_statement >= l_log_current_level) THEN
5892: fnd_log.string(l_log_statement,L_DEBUG,'l_query_str is :' ||l_query_str);
5893: END IF;
5894: EXECUTE IMMEDIATE l_query_str USING l_planned_end_date,l_max_planned_start_date;
5895:
5896: -- PRAKKUM :: 08-JUL-2011 :: VWPE 12730539 :: start
5905: x_return_status => l_return_status,
5906: x_msg_count => l_msg_count,
5907: x_msg_data => l_msg_data);
5908:
5909: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5910: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGES_OVN_COUNT - l_return_status : '||l_return_status);
5911: END IF;
5912:
5913: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5906: x_msg_count => l_msg_count,
5907: x_msg_data => l_msg_data);
5908:
5909: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5910: fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGES_OVN_COUNT - l_return_status : '||l_return_status);
5911: END IF;
5912:
5913: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5914: x_msg_count := l_msg_count;
5932: FOR invalid_stage_numbers IN c_get_invalid_stage_numbers (p_visit_id)
5933: LOOP
5934:
5935: IF (l_log_statement >= l_log_current_level) THEN
5936: fnd_log.string(l_log_statement,L_DEBUG,'Invalid Stage Num is :' ||invalid_stage_numbers.STAGE_NUM );
5937: END IF;
5938:
5939: IF p_caller_id = 'R' THEN -- If validation failed during stage rule addition
5940: Fnd_Message.SET_NAME('AHL','AHL_VWP_VST_R_STAGE_NUMS');
5967:
5968: -- PRAKKUM :: 13-FEB-2011 :: Bug 13711800 :: START
5969:
5970: IF (l_log_statement >= l_log_current_level) THEN
5971: fnd_log.string(l_log_statement,L_DEBUG,'Before calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_DATES ');
5972: END IF;
5973:
5974: VALIDATE_STAGE_DATES( p_visit_id => p_visit_id,
5975: x_return_status => l_return_status,
5976: x_msg_count => l_msg_count,
5977: x_msg_data => l_msg_data);
5978:
5979: IF (l_log_statement >= l_log_current_level) THEN
5980: fnd_log.string(l_log_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_DATES - l_return_status : '||l_return_status);
5981: END IF;
5982:
5983: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5984: x_msg_count := l_msg_count;
5999: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
6000: END IF;
6001:
6002: IF (l_log_procedure >= l_log_current_level) THEN
6003: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
6004: END IF;
6005:
6006: EXCEPTION
6007: WHEN Fnd_Api.G_EXC_ERROR THEN
6062:
6063: BEGIN
6064:
6065: IF (l_log_procedure >= l_log_current_level) THEN
6066: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
6067: END IF;
6068:
6069: --Initialize API return status to success
6070: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
6070: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
6071: l_return_status := x_return_status;
6072:
6073: /*IF p_stage_id = p_updated_stage_id THEN
6074: fnd_log.string(l_log_procedure,L_DEBUG||'.end','Return');
6075: RETURN; -- NO need to update
6076: END IF;*/
6077:
6078: l_updated_version_no := 'N';
6078: l_updated_version_no := 'N';
6079: l_table_size :=p_x_stages_ovn_tbl.count;
6080:
6081: IF (l_log_statement >= l_log_current_level) THEN
6082: fnd_log.string(l_log_statement,L_DEBUG,' p_stage_id '||p_stage_id||' p_updated_stage_id '||p_updated_stage_id);
6083: fnd_log.string(l_log_statement,L_DEBUG,' p_x_stages_ovn_tbl count is '||p_x_stages_ovn_tbl.count||' : l_table_size is '||l_table_size);
6084: END IF;
6085:
6086: IF p_x_stages_ovn_tbl.count > 0 THEN
6079: l_table_size :=p_x_stages_ovn_tbl.count;
6080:
6081: IF (l_log_statement >= l_log_current_level) THEN
6082: fnd_log.string(l_log_statement,L_DEBUG,' p_stage_id '||p_stage_id||' p_updated_stage_id '||p_updated_stage_id);
6083: fnd_log.string(l_log_statement,L_DEBUG,' p_x_stages_ovn_tbl count is '||p_x_stages_ovn_tbl.count||' : l_table_size is '||l_table_size);
6084: END IF;
6085:
6086: IF p_x_stages_ovn_tbl.count > 0 THEN
6087: FOR i IN p_x_stages_ovn_tbl.FIRST..p_x_stages_ovn_tbl.LAST
6087: FOR i IN p_x_stages_ovn_tbl.FIRST..p_x_stages_ovn_tbl.LAST
6088: loop
6089:
6090: IF (l_log_statement >= l_log_current_level) THEN
6091: fnd_log.string(l_log_statement,L_DEBUG,' p_x_stages_ovn_tbl(i).STAGE_ID '||p_x_stages_ovn_tbl(i).STAGE_ID
6092: ||' p_x_stages_ovn_tbl(i).UPDATED_OBJ_VERSION_NO_COUNT:'||p_x_stages_ovn_tbl(i).UPDATED_OBJ_VERSION_NO_COUNT);
6093: END IF;
6094: IF p_x_stages_ovn_tbl(i).STAGE_ID = p_updated_stage_id THEN
6095: p_x_stages_ovn_tbl(i).UPDATED_OBJ_VERSION_NO_COUNT := p_x_stages_ovn_tbl(i).UPDATED_OBJ_VERSION_NO_COUNT + 1;
6101: END LOOP;
6102: END IF;
6103:
6104: IF (l_log_statement >= l_log_current_level) THEN
6105: fnd_log.string(l_log_statement,L_DEBUG,' l_table_size '||l_table_size||' : l_updated_version_no '||l_updated_version_no);
6106: END IF;
6107:
6108: l_table_size := l_table_size+1;
6109:
6112: p_x_stages_ovn_tbl(l_table_size).UPDATED_OBJ_VERSION_NO_COUNT := 1; -- Initialized
6113: END IF;
6114:
6115: IF (l_log_statement >= l_log_current_level) THEN
6116: fnd_log.string(l_log_statement,L_DEBUG,' p_x_stages_ovn_tbl count is '||p_x_stages_ovn_tbl.count);
6117: END IF;
6118:
6119: --Standard check to count messages
6120: l_msg_count := Fnd_Msg_Pub.count_msg;
6124: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
6125: END IF;
6126:
6127: IF (l_log_procedure >= l_log_current_level) THEN
6128: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
6129: END IF;
6130:
6131: EXCEPTION
6132: WHEN Fnd_Api.G_EXC_ERROR THEN
6196:
6197: BEGIN
6198:
6199: IF (l_log_procedure >= l_log_current_level) THEN
6200: fnd_log.string(l_log_procedure,L_DEBUG||'.begin','Start');
6201: END IF;
6202:
6203: --Initialize API return status to success
6204: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
6204: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
6205: l_return_status := x_return_status;
6206:
6207: IF (l_log_statement >= l_log_current_level) THEN
6208: fnd_log.string(l_log_statement,L_DEBUG,' p_visit_id '||p_visit_id);
6209: END IF;
6210:
6211: -- Cursor to find visit start time
6212: FOR l_stages_rec IN get_invalid_stages(p_visit_id)
6212: FOR l_stages_rec IN get_invalid_stages(p_visit_id)
6213: LOOP
6214:
6215: IF (l_log_statement >= l_log_current_level) THEN
6216: fnd_log.string(l_log_statement,L_DEBUG,' Stage Number --@>'|| l_stages_rec.stage_num);
6217: fnd_log.string(l_log_statement,L_DEBUG,' Stage Planned End Date --@>'|| TO_CHAR(l_stages_rec.planned_end_date,'DD-MON-YYYY HH24:MI:SS'));
6218: fnd_log.string(l_log_statement,L_DEBUG,' Visit Planned End Date --@>'|| TO_CHAR(l_stages_rec.close_date_time,'DD-MON-YYYY HH24:MI:SS'));
6219: END IF;
6220:
6213: LOOP
6214:
6215: IF (l_log_statement >= l_log_current_level) THEN
6216: fnd_log.string(l_log_statement,L_DEBUG,' Stage Number --@>'|| l_stages_rec.stage_num);
6217: fnd_log.string(l_log_statement,L_DEBUG,' Stage Planned End Date --@>'|| TO_CHAR(l_stages_rec.planned_end_date,'DD-MON-YYYY HH24:MI:SS'));
6218: fnd_log.string(l_log_statement,L_DEBUG,' Visit Planned End Date --@>'|| TO_CHAR(l_stages_rec.close_date_time,'DD-MON-YYYY HH24:MI:SS'));
6219: END IF;
6220:
6221: Fnd_Message.SET_NAME('AHL','AHL_VWP_INV_STAGE_DATE');
6214:
6215: IF (l_log_statement >= l_log_current_level) THEN
6216: fnd_log.string(l_log_statement,L_DEBUG,' Stage Number --@>'|| l_stages_rec.stage_num);
6217: fnd_log.string(l_log_statement,L_DEBUG,' Stage Planned End Date --@>'|| TO_CHAR(l_stages_rec.planned_end_date,'DD-MON-YYYY HH24:MI:SS'));
6218: fnd_log.string(l_log_statement,L_DEBUG,' Visit Planned End Date --@>'|| TO_CHAR(l_stages_rec.close_date_time,'DD-MON-YYYY HH24:MI:SS'));
6219: END IF;
6220:
6221: Fnd_Message.SET_NAME('AHL','AHL_VWP_INV_STAGE_DATE');
6222: Fnd_Message.SET_TOKEN('STAGE_NUM', l_stages_rec.stage_num);
6233: RAISE Fnd_Api.G_EXC_ERROR;
6234: END IF;
6235:
6236: IF (l_log_statement >= l_log_current_level) THEN
6237: fnd_log.string(l_log_statement,L_DEBUG,' l_isValidationFailed --@>'|| l_isValidationFailed);
6238: END IF;
6239:
6240: --Standard check to count messages
6241: l_msg_count := Fnd_Msg_Pub.count_msg;
6245: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
6246: END IF;
6247:
6248: IF (l_log_procedure >= l_log_current_level) THEN
6249: fnd_log.string(l_log_procedure,L_DEBUG||'.end','End');
6250: END IF;
6251:
6252: EXCEPTION
6253: WHEN Fnd_Api.G_EXC_ERROR THEN
6318: --
6319: BEGIN
6320:
6321: IF (l_log_procedure >= l_log_current_level) THEN
6322: fnd_log.string(l_log_procedure,
6323: L_DEBUG_KEY ||'.begin',
6324: 'At the start of PL SQL function. Visit Id = '||p_visit_id||
6325: ', Start Date = ' || p_start_date ||
6326: ', Department Id = ' || p_dept_id || ', Duration = ' || p_duration);
6348: IF l_is_operational_visit THEN-- Operational Visit
6349:
6350: l_end_date := p_start_date + (p_duration/24);
6351:
6352: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6353: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Operational Visit - l_end_date : '||TO_CHAR(l_end_date,'DD-MON-YYYY HH24:MI:SS'));
6354: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'l_visit_dets_rec.UNIT_SCHEDULE_ID : '||l_visit_dets_rec.UNIT_SCHEDULE_ID);
6355: END IF;
6356:
6349:
6350: l_end_date := p_start_date + (p_duration/24);
6351:
6352: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6353: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Operational Visit - l_end_date : '||TO_CHAR(l_end_date,'DD-MON-YYYY HH24:MI:SS'));
6354: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'l_visit_dets_rec.UNIT_SCHEDULE_ID : '||l_visit_dets_rec.UNIT_SCHEDULE_ID);
6355: END IF;
6356:
6357: ELSE -- Normal Visit
6350: l_end_date := p_start_date + (p_duration/24);
6351:
6352: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6353: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Operational Visit - l_end_date : '||TO_CHAR(l_end_date,'DD-MON-YYYY HH24:MI:SS'));
6354: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'l_visit_dets_rec.UNIT_SCHEDULE_ID : '||l_visit_dets_rec.UNIT_SCHEDULE_ID);
6355: END IF;
6356:
6357: ELSE -- Normal Visit
6358:
6357: ELSE -- Normal Visit
6358:
6359: l_end_date := AHL_VWP_TIMES_PVT.compute_date(p_start_date, p_dept_id, p_duration );
6360:
6361: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6362: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Normal Visit - l_end_date : '||l_end_date);
6363: END IF;
6364:
6365: END IF;
6358:
6359: l_end_date := AHL_VWP_TIMES_PVT.compute_date(p_start_date, p_dept_id, p_duration );
6360:
6361: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6362: fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Normal Visit - l_end_date : '||l_end_date);
6363: END IF;
6364:
6365: END IF;
6366:
6365: END IF;
6366:
6367: --RETURN the derived end date.
6368: IF (l_log_procedure >= l_log_current_level) THEN
6369: fnd_log.string(l_log_procedure,
6370: L_DEBUG_KEY ||'.end',
6371: 'At the end of PL SQL function. End Date = ' || TO_CHAR(l_end_date,'DD-MON-YYYY HH24:MI:SS'));
6372: END IF;
6373: RETURN l_end_date;