eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ENG_CHANGE_LIFECYCLE_UTIL dependencies on ENG_LIFECYCLE_STATUSES

Line 358: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

354:
355: --l_curr_status_code NUMBER;
356:
357: l_last_status_code NUMBER;
358: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
359:
360: l_last_imp_flag VARCHAR2(1) := 'N';
361:
362:

Line 417: FROM eng_lifecycle_statuses

413: -- Get the sequence_number of the last phase
414: -- Note that only phase of IMPLEMENT type can be the last phase
415: SELECT max(sequence_number)
416: INTO l_max_phase_sn
417: FROM eng_lifecycle_statuses
418: WHERE entity_name = G_ENG_CHANGE
419: AND entity_id1 = p_change_id
420: AND active_flag = 'Y';
421:

Line 425: FROM eng_lifecycle_statuses

421:
422: -- Get the sequence number of the last phase
423: SELECT status_code
424: INTO l_last_status_code
425: FROM eng_lifecycle_statuses
426: WHERE entity_name = G_ENG_CHANGE
427: AND entity_id1 = p_change_id
428: AND active_flag = 'Y'
429: AND sequence_number = l_max_phase_sn;

Line 465: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

461: )
462: IS
463: l_cm_type_code eng_engineering_changes.CHANGE_MGMT_TYPE_CODE%TYPE;
464: l_base_cm_type_code eng_change_order_types.BASE_CHANGE_MGMT_TYPE_CODE%TYPE;
465: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
466: l_start_date eng_lifecycle_statuses.start_date%TYPE;
467: BEGIN
468: -- Standard Start of API savepoint
469: --SAVEPOINT Is_CO_Last_Imp_Phase_Used;

Line 466: l_start_date eng_lifecycle_statuses.start_date%TYPE;

462: IS
463: l_cm_type_code eng_engineering_changes.CHANGE_MGMT_TYPE_CODE%TYPE;
464: l_base_cm_type_code eng_change_order_types.BASE_CHANGE_MGMT_TYPE_CODE%TYPE;
465: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
466: l_start_date eng_lifecycle_statuses.start_date%TYPE;
467: BEGIN
468: -- Standard Start of API savepoint
469: --SAVEPOINT Is_CO_Last_Imp_Phase_Used;
470:

Line 501: FROM eng_lifecycle_statuses

497: -- Get the sequence_number of the last phase
498: -- Note that only phase of IMPLEMENT type can be the last phase
499: SELECT max(sequence_number)
500: INTO l_max_phase_sn
501: FROM eng_lifecycle_statuses
502: WHERE entity_name = G_ENG_CHANGE
503: AND entity_id1 = p_change_id
504: AND active_flag = 'Y';
505:

Line 509: FROM eng_lifecycle_statuses

505:
506: -- Get the start_date of the last phase (to see if it's been used)
507: SELECT status_code, start_date
508: INTO x_last_status_code, l_start_date
509: FROM eng_lifecycle_statuses
510: WHERE entity_name = G_ENG_CHANGE
511: AND entity_id1 = p_change_id
512: AND active_flag = 'Y'
513: AND sequence_number = l_max_phase_sn

Line 607: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

603: l_fnd_login_id NUMBER := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
604:
605: l_updated BOOLEAN := FALSE ;
606:
607: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
608: l_max_appr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
609:
610: l_status_type eng_engineering_changes.status_type%TYPE;
611:

Line 608: l_max_appr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

604:
605: l_updated BOOLEAN := FALSE ;
606:
607: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
608: l_max_appr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
609:
610: l_status_type eng_engineering_changes.status_type%TYPE;
611:
612: l_wf_item_key wf_item_activity_statuses.item_key%TYPE := NULL;

Line 726: FROM eng_lifecycle_statuses

722:
723: -- Get the current phase's sequence number
724: SELECT sequence_number
725: INTO l_phase_sn
726: FROM eng_lifecycle_statuses
727: WHERE entity_name = G_ENG_CHANGE
728: AND entity_id1 = p_change_id
729: AND status_code = p_status_code
730: AND active_flag = 'Y'

Line 736: FROM eng_lifecycle_statuses lcs,

732:
733: -- Get the sequence number of the last phase of type APPROVAL
734: SELECT max(lcs.sequence_number)
735: INTO l_max_appr_phase_sn
736: FROM eng_lifecycle_statuses lcs,
737: eng_change_statuses chs
738: WHERE lcs.entity_name = G_ENG_CHANGE
739: AND lcs.entity_id1 = p_change_id
740: AND lcs.active_flag = 'Y'

Line 1287: eng_lifecycle_statuses line_start_after,

1283: , line.change_line_id
1284: FROM eng_change_routes route,
1285: eng_change_statuses s,
1286: eng_change_lines line,
1287: eng_lifecycle_statuses line_start_after,
1288: eng_lifecycle_statuses new_status,
1289: eng_lifecycle_statuses cur_status
1290: WHERE route.status_code = Eng_Workflow_Util.G_RT_NOT_STARTED
1291: AND route.route_id = line.route_id

Line 1288: eng_lifecycle_statuses new_status,

1284: FROM eng_change_routes route,
1285: eng_change_statuses s,
1286: eng_change_lines line,
1287: eng_lifecycle_statuses line_start_after,
1288: eng_lifecycle_statuses new_status,
1289: eng_lifecycle_statuses cur_status
1290: WHERE route.status_code = Eng_Workflow_Util.G_RT_NOT_STARTED
1291: AND route.route_id = line.route_id
1292: AND s.status_type <> G_ENG_COMPLETED

Line 1289: eng_lifecycle_statuses cur_status

1285: eng_change_statuses s,
1286: eng_change_lines line,
1287: eng_lifecycle_statuses line_start_after,
1288: eng_lifecycle_statuses new_status,
1289: eng_lifecycle_statuses cur_status
1290: WHERE route.status_code = Eng_Workflow_Util.G_RT_NOT_STARTED
1291: AND route.route_id = line.route_id
1292: AND s.status_type <> G_ENG_COMPLETED
1293: AND s.status_type <> G_ENG_IMPLEMENTED

Line 1540: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;

1536: l_fnd_login_id NUMBER := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
1537:
1538: l_action_id eng_change_actions.action_id%TYPE;
1539:
1540: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
1541: l_wf_route_temp_id eng_lifecycle_statuses.change_wf_route_template_id%TYPE;
1542: l_wf_item_key wf_item_activity_statuses.item_key%TYPE := NULL;
1543:
1544: l_chg_notice eng_engineering_changes.change_notice%TYPE;

Line 1541: l_wf_route_temp_id eng_lifecycle_statuses.change_wf_route_template_id%TYPE;

1537:
1538: l_action_id eng_change_actions.action_id%TYPE;
1539:
1540: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
1541: l_wf_route_temp_id eng_lifecycle_statuses.change_wf_route_template_id%TYPE;
1542: l_wf_item_key wf_item_activity_statuses.item_key%TYPE := NULL;
1543:
1544: l_chg_notice eng_engineering_changes.change_notice%TYPE;
1545: l_org_id eng_engineering_changes.organization_id%TYPE;

Line 1548: l_min_appr_sn eng_lifecycle_statuses.sequence_number%TYPE;

1544: l_chg_notice eng_engineering_changes.change_notice%TYPE;
1545: l_org_id eng_engineering_changes.organization_id%TYPE;
1546: l_request_id NUMBER;
1547:
1548: l_min_appr_sn eng_lifecycle_statuses.sequence_number%TYPE;
1549: l_doc_lc_object_flag BOOLEAN := FALSE ;
1550:
1551:
1552: BEGIN

Line 1749: FROM eng_lifecycle_statuses

1745: END IF;
1746: -- Check if automatic wf start is needed
1747: SELECT change_wf_route_id, change_wf_route_template_id
1748: INTO l_wf_route_id, l_wf_route_temp_id
1749: FROM eng_lifecycle_statuses
1750: WHERE entity_name = G_ENG_CHANGE
1751: AND entity_id1 = p_change_id
1752: AND status_code = p_status_code
1753: AND active_flag = 'Y'

Line 1963: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

1959: l_msg_data VARCHAR2(2000);
1960:
1961: l_curr_status_code eng_engineering_changes.status_code%TYPE;
1962:
1963: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1964: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1965: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;
1966: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1967:

Line 1964: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

1960:
1961: l_curr_status_code eng_engineering_changes.status_code%TYPE;
1962:
1963: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1964: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1965: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;
1966: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1967:
1968: l_new_status_type eng_change_statuses.status_type%TYPE;

Line 1965: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;

1961: l_curr_status_code eng_engineering_changes.status_code%TYPE;
1962:
1963: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1964: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1965: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;
1966: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1967:
1968: l_new_status_type eng_change_statuses.status_type%TYPE;
1969: l_ri_status_type eng_change_statuses.status_type%TYPE;

Line 1966: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

1962:
1963: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1964: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1965: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;
1966: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
1967:
1968: l_new_status_type eng_change_statuses.status_type%TYPE;
1969: l_ri_status_type eng_change_statuses.status_type%TYPE;
1970:

Line 2003: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

1999:
2000:
2001: l_ri_status_code eng_change_lines.status_code%TYPE;
2002: -- revItem's status_code's sequence_number
2003: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
2004:
2005: l_last_imp_flag VARCHAR2(1) := 'N';
2006: l_cm_type_code eng_engineering_changes.CHANGE_MGMT_TYPE_CODE%TYPE;
2007: l_base_cm_type_code eng_change_order_types.BASE_CHANGE_MGMT_TYPE_CODE%TYPE;

Line 2063: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;

2059: l_request_id NUMBER := 0;
2060:
2061: l_action_id eng_change_actions.action_id%TYPE;
2062:
2063: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;
2064: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
2065: l_new_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
2066:
2067: l_found_rev_item VARCHAR2(1) := 'N';

Line 2064: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;

2060:
2061: l_action_id eng_change_actions.action_id%TYPE;
2062:
2063: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;
2064: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
2065: l_new_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
2066:
2067: l_found_rev_item VARCHAR2(1) := 'N';
2068:

Line 2065: l_new_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;

2061: l_action_id eng_change_actions.action_id%TYPE;
2062:
2063: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;
2064: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
2065: l_new_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
2066:
2067: l_found_rev_item VARCHAR2(1) := 'N';
2068:
2069: l_skip_wf VARCHAR2(1) := 'N';

Line 2165: UPDATE eng_lifecycle_statuses

2161: Write_Debug('After: calling Refresh_WF_Route procedure: ' || l_return_status) ;
2162: END IF;
2163:
2164: -- Update the remaining columns of the phase row
2165: UPDATE eng_lifecycle_statuses
2166: SET start_date = null,
2167: completion_date = null,
2168: last_update_date = sysdate,
2169: last_updated_by = l_fnd_user_id,

Line 2183: FROM eng_lifecycle_statuses

2179:
2180: -- Get the sequence number for the current phase of the change header
2181: SELECT sequence_number, status_code
2182: INTO l_curr_phase_sn, l_curr_status_code
2183: FROM eng_lifecycle_statuses
2184: WHERE entity_name = G_ENG_CHANGE
2185: AND entity_id1 = p_change_id
2186: AND status_code = ( SELECT status_code
2187: FROM eng_engineering_changes

Line 2195: FROM eng_lifecycle_statuses

2191:
2192: -- Get the sequence number for the new phase of the change header
2193: SELECT sequence_number
2194: INTO l_new_phase_sn
2195: FROM eng_lifecycle_statuses
2196: WHERE entity_name = G_ENG_CHANGE
2197: AND entity_id1 = p_change_id
2198: AND status_code = p_status_code
2199: AND active_flag = 'Y'

Line 2212: FROM eng_lifecycle_statuses

2208:
2209: -- Get the max sequence number in the lifecycle
2210: SELECT max(sequence_number)
2211: INTO l_max_sn
2212: FROM eng_lifecycle_statuses
2213: WHERE entity_name = G_ENG_CHANGE
2214: AND entity_id1 = p_change_id
2215: AND active_flag = 'Y';
2216:

Line 2279: FROM eng_lifecycle_statuses

2275: --
2276: -- Get the sequence_number for line/task's complete_before_status_code
2277: SELECT sequence_number
2278: INTO l_phase_sn
2279: FROM eng_lifecycle_statuses
2280: WHERE entity_name = G_ENG_CHANGE
2281: AND entity_id1 = p_change_id
2282: AND status_code = l_status_code
2283: AND active_flag = 'Y'

Line 2466: UPDATE eng_lifecycle_statuses

2462: -- start_date of implement phase blank in case of CO promotion to
2463: -- implement phase
2464: /*
2465: -- Update start_date of the implement phase
2466: UPDATE eng_lifecycle_statuses
2467: SET start_date = sysdate,
2468: completion_date = null,
2469: last_update_date = sysdate,
2470: last_updated_by = l_fnd_user_id,

Line 2482: FROM eng_lifecycle_statuses

2478:
2479: -- If the CO's last implement type phase is already used, refresh its workflow id and status
2480: SELECT workflow_status, change_wf_route_id
2481: INTO l_wf_status, l_wf_route_id
2482: FROM eng_lifecycle_statuses
2483: WHERE entity_name = G_ENG_CHANGE
2484: AND entity_id1 = p_change_id
2485: AND status_code = p_status_code
2486: AND active_flag = 'Y'

Line 2502: UPDATE eng_lifecycle_statuses

2498: P_API_CALLER => p_api_caller
2499: );
2500:
2501: -- refresh imp phase row
2502: UPDATE eng_lifecycle_statuses
2503: SET change_wf_route_id = l_new_route_id,
2504: workflow_status = Eng_Workflow_Util.G_RT_NOT_STARTED,
2505: last_update_date = sysdate,
2506: last_updated_by = l_fnd_user_id,

Line 2550: UPDATE eng_lifecycle_statuses

2546:
2547: ELSE
2548: -- Normal phase promotion
2549: -- Complete the current phase
2550: UPDATE eng_lifecycle_statuses
2551: SET completion_date = sysdate,
2552: last_update_date = sysdate,
2553: last_updated_by = l_fnd_user_id,
2554: last_update_login = l_fnd_login_id

Line 2560: Write_Debug('After updating eng_lifecycle_statuses.completion_date.');

2556: AND entity_id1 = p_change_id
2557: AND status_code = l_curr_status_code
2558: AND active_flag = 'Y';
2559: IF g_debug_flag THEN
2560: Write_Debug('After updating eng_lifecycle_statuses.completion_date.');
2561: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
2562: END IF;
2563: -- Sanity check, only one record can qualify the condition
2564: IF SQL%ROWCOUNT <> 1 THEN

Line 2622: FROM eng_lifecycle_statuses

2618: -- Note: moved this query inside this IF block because cancel
2619: -- status is not in the regular lifecycle phase definitions
2620: SELECT sequence_number
2621: INTO l_ri_phase_sn
2622: FROM eng_lifecycle_statuses
2623: WHERE entity_name = G_ENG_CHANGE
2624: AND entity_id1 = p_change_id
2625: AND status_code = l_ri_status_code
2626: AND active_flag = 'Y'

Line 2655: UPDATE eng_lifecycle_statuses

2651: Write_Debug('Done: Updating header and revised items to the new phase');
2652: END IF;
2653:
2654: -- Update the new phase's start_date
2655: UPDATE eng_lifecycle_statuses
2656: SET start_date = sysdate,
2657: last_update_date = sysdate,
2658: last_updated_by = l_fnd_user_id,
2659: last_update_login = l_fnd_login_id

Line 2665: Write_Debug('After updating eng_lifecycle_statuses.start_date.');

2661: AND entity_id1 = p_change_id
2662: AND status_code = p_status_code
2663: AND active_flag = 'Y';
2664: IF g_debug_flag THEN
2665: Write_Debug('After updating eng_lifecycle_statuses.start_date.');
2666: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
2667: END IF;
2668:
2669: END IF;

Line 3061: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

3057: l_return_status VARCHAR2(1);
3058: l_msg_count NUMBER;
3059: l_msg_data VARCHAR2(2000);
3060:
3061: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3062: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3063:
3064: l_new_status_type eng_change_statuses.status_type%TYPE;
3065:

Line 3062: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

3058: l_msg_count NUMBER;
3059: l_msg_data VARCHAR2(2000);
3060:
3061: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3062: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3063:
3064: l_new_status_type eng_change_statuses.status_type%TYPE;
3065:
3066: l_change_mgmt_type_code eng_engineering_changes.change_mgmt_type_code%TYPE;

Line 3071: FROM eng_lifecycle_statuses

3067:
3068: -- Cursor to get all lifecycle phases between (inclusive) [demotionStatus, currentStatus]
3069: CURSOR c_lcStatuses IS
3070: SELECT *
3071: FROM eng_lifecycle_statuses
3072: WHERE entity_name = G_ENG_CHANGE
3073: AND entity_id1 = p_change_id
3074: AND active_flag = 'Y'
3075: AND sequence_number >= l_new_phase_sn

Line 3085: FROM eng_lifecycle_statuses

3081:
3082: -- Cursor to get all lifecycle phases between (inclusive) [demotionStatus, maxStatus]
3083: CURSOR c_lcStatusesToMax IS
3084: SELECT *
3085: FROM eng_lifecycle_statuses
3086: WHERE entity_name = G_ENG_CHANGE
3087: AND entity_id1 = p_change_id
3088: AND active_flag = 'Y'
3089: AND sequence_number >= l_new_phase_sn

Line 3091: l_lcStatuses_row eng_lifecycle_statuses%ROWTYPE;

3087: AND entity_id1 = p_change_id
3088: AND active_flag = 'Y'
3089: AND sequence_number >= l_new_phase_sn
3090: FOR UPDATE;
3091: l_lcStatuses_row eng_lifecycle_statuses%ROWTYPE;
3092: l_old_iter_num eng_lifecycle_statuses.iteration_number%TYPE := -1;
3093: l_old_row_id NUMBER;
3094: l_new_row_id NUMBER;
3095: l_new_route_id NUMBER;

Line 3092: l_old_iter_num eng_lifecycle_statuses.iteration_number%TYPE := -1;

3088: AND active_flag = 'Y'
3089: AND sequence_number >= l_new_phase_sn
3090: FOR UPDATE;
3091: l_lcStatuses_row eng_lifecycle_statuses%ROWTYPE;
3092: l_old_iter_num eng_lifecycle_statuses.iteration_number%TYPE := -1;
3093: l_old_row_id NUMBER;
3094: l_new_row_id NUMBER;
3095: l_new_route_id NUMBER;
3096:

Line 3111: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

3107: WHERE change_id = p_change_id
3108: FOR UPDATE;
3109: l_ri_status_code eng_change_lines.status_code%TYPE;
3110: -- revItem's status_code's sequence_number
3111: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3112: l_ri_status_type eng_change_statuses.status_type%TYPE;
3113:
3114:
3115: l_base_cm_type_code VARCHAR2(30) ;

Line 3197: FROM eng_lifecycle_statuses

3193:
3194: -- Get the sequence number for the current phase of the change header
3195: SELECT sequence_number
3196: INTO l_curr_phase_sn
3197: FROM eng_lifecycle_statuses
3198: WHERE entity_name = G_ENG_CHANGE
3199: AND entity_id1 = p_change_id
3200: AND active_flag = 'Y'
3201: AND status_code = ( SELECT status_code

Line 3209: FROM eng_lifecycle_statuses

3205:
3206: -- Get the sequence number for the new phase of the change header
3207: SELECT sequence_number
3208: INTO l_new_phase_sn
3209: FROM eng_lifecycle_statuses
3210: WHERE entity_name = G_ENG_CHANGE
3211: AND entity_id1 = p_change_id
3212: AND status_code = p_status_code
3213: AND active_flag = 'Y'

Line 3228: UPDATE eng_lifecycle_statuses

3224: RAISE FND_API.G_EXC_ERROR;
3225: END IF;
3226:
3227: -- Finish the current phase before demotion
3228: UPDATE eng_lifecycle_statuses
3229: SET completion_date = sysdate,
3230: last_update_date = sysdate,
3231: last_updated_by = l_fnd_user_id,
3232: last_update_login = l_fnd_login_id

Line 3242: Write_Debug('After updating eng_lifecycle_statuses.completion_date.');

3238: WHERE change_id = p_change_id)
3239: AND sequence_number = l_curr_phase_sn
3240: AND rownum = 1;
3241: IF g_debug_flag THEN
3242: Write_Debug('After updating eng_lifecycle_statuses.completion_date.');
3243: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
3244: END IF;
3245:
3246:

Line 3290: UPDATE eng_lifecycle_statuses

3286: l_has_approval_phase := 'T';
3287: END IF;
3288:
3289: -- Obsolete (but not delete) the old phase row
3290: UPDATE eng_lifecycle_statuses
3291: SET active_flag = 'N',
3292: last_update_date = sysdate,
3293: last_updated_by = l_fnd_user_id,
3294: last_update_login = l_fnd_login_id

Line 3299: SELECT eng_lifecycle_statuses_s.nextval

3295: WHERE CURRENT OF c_lcStatuses;
3296:
3297: -- First get the new unique index id value for the new row (also save the old id)
3298: l_old_row_id := l_lcStatuses_row.change_lifecycle_status_id;
3299: SELECT eng_lifecycle_statuses_s.nextval
3300: INTO l_new_row_id
3301: FROM DUAL;
3302:
3303: -- Secondly get the new wf_route_id if needed

Line 3360: INSERT INTO eng_lifecycle_statuses

3356: END IF;
3357:
3358: -- Insert the new phase row
3359: /* Only Oracle 9.2+ supports this directly row insertion feature
3360: INSERT INTO eng_lifecycle_statuses
3361: VALUES l_lcStatuses_row;
3362: */
3363: INSERT INTO eng_lifecycle_statuses
3364: ( CHANGE_LIFECYCLE_STATUS_ID,

Line 3363: INSERT INTO eng_lifecycle_statuses

3359: /* Only Oracle 9.2+ supports this directly row insertion feature
3360: INSERT INTO eng_lifecycle_statuses
3361: VALUES l_lcStatuses_row;
3362: */
3363: INSERT INTO eng_lifecycle_statuses
3364: ( CHANGE_LIFECYCLE_STATUS_ID,
3365: ENTITY_NAME,
3366: ENTITY_ID1,
3367: ENTITY_ID2,

Line 3435: UPDATE eng_lifecycle_statuses

3431: END IF;
3432: l_old_iter_num := l_lcStatuses_row.iteration_number;
3433:
3434: -- Increment sequence number of the active phases
3435: UPDATE eng_lifecycle_statuses
3436: SET iteration_number = l_old_iter_num + 1,
3437: last_update_date = sysdate,
3438: last_updated_by = l_fnd_user_id,
3439: last_update_login = l_fnd_login_id

Line 3541: FROM eng_lifecycle_statuses

3537: -- Note: moved this query inside this IF block because cancel
3538: -- status is not in the regular lifecycle phase definitions
3539: SELECT sequence_number
3540: INTO l_ri_phase_sn
3541: FROM eng_lifecycle_statuses
3542: WHERE entity_name = G_ENG_CHANGE
3543: AND entity_id1 = p_change_id
3544: AND status_code = l_ri_status_code
3545: AND active_flag = 'Y'

Line 3785: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

3781: l_return_status VARCHAR2(1);
3782: l_msg_count NUMBER;
3783: l_msg_data VARCHAR2(2000);
3784:
3785: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3786: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3787: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;
3788: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3789:

Line 3786: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

3782: l_msg_count NUMBER;
3783: l_msg_data VARCHAR2(2000);
3784:
3785: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3786: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3787: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;
3788: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3789:
3790: l_last_imp_flag VARCHAR2(1) := 'N';

Line 3787: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;

3783: l_msg_data VARCHAR2(2000);
3784:
3785: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3786: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3787: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;
3788: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3789:
3790: l_last_imp_flag VARCHAR2(1) := 'N';
3791:

Line 3788: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

3784:
3785: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3786: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3787: l_max_sn eng_lifecycle_statuses.sequence_number%TYPE;
3788: l_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
3789:
3790: l_last_imp_flag VARCHAR2(1) := 'N';
3791:
3792: l_new_status_type eng_change_statuses.status_type%TYPE;

Line 3854: FROM eng_lifecycle_statuses

3850:
3851: -- Get the sequence number for the current phase of the revised item
3852: SELECT sequence_number, status_code
3853: INTO l_ri_phase_sn, l_ri_status_code
3854: FROM eng_lifecycle_statuses
3855: WHERE entity_name = G_ENG_CHANGE
3856: AND entity_id1 = p_change_id
3857: AND status_code = ( SELECT status_code
3858: FROM eng_revised_items

Line 3866: FROM eng_lifecycle_statuses

3862:
3863: -- Get the sequence number for the new phase of the revised item
3864: SELECT sequence_number
3865: INTO l_new_phase_sn
3866: FROM eng_lifecycle_statuses
3867: WHERE entity_name = G_ENG_CHANGE
3868: AND entity_id1 = p_change_id
3869: AND status_code = p_status_code
3870: AND active_flag = 'Y'

Line 3883: FROM eng_lifecycle_statuses

3879:
3880: -- Get the max sequence number in the lifecycle
3881: SELECT max(sequence_number)
3882: INTO l_max_sn
3883: FROM eng_lifecycle_statuses
3884: WHERE entity_name = G_ENG_CHANGE
3885: AND entity_id1 = p_change_id
3886: AND active_flag = 'Y';
3887:

Line 4163: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

4159: l_return_status VARCHAR2(1);
4160: l_msg_count NUMBER;
4161: l_msg_data VARCHAR2(2000);
4162:
4163: l_new_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4164:
4165: l_new_status_type eng_change_statuses.status_type%TYPE;
4166:
4167: l_ri_status_code eng_change_lines.status_code%TYPE;

Line 4169: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

4165: l_new_status_type eng_change_statuses.status_type%TYPE;
4166:
4167: l_ri_status_code eng_change_lines.status_code%TYPE;
4168: -- revItem's status_code's sequence_number
4169: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4170: l_ri_status_type eng_change_statuses.status_type%TYPE;
4171:
4172: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4173:

Line 4172: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

4168: -- revItem's status_code's sequence_number
4169: l_ri_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4170: l_ri_status_type eng_change_statuses.status_type%TYPE;
4171:
4172: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4173:
4174: BEGIN
4175: -- Standard Start of API savepoint
4176: SAVEPOINT Demote_Revised_Item;

Line 4227: FROM eng_lifecycle_statuses

4223: -- Real code starts here -----------------------------------------------
4224: -- Get the sequence number for the current phase of the change header
4225: SELECT sequence_number, status_code
4226: INTO l_ri_phase_sn, l_ri_status_code
4227: FROM eng_lifecycle_statuses
4228: WHERE entity_name = G_ENG_CHANGE
4229: AND entity_id1 = p_change_id
4230: AND active_flag = 'Y'
4231: AND status_code = ( SELECT status_code

Line 4239: FROM eng_lifecycle_statuses

4235:
4236: -- Get the sequence number for the new phase of the change header
4237: SELECT sequence_number
4238: INTO l_new_phase_sn
4239: FROM eng_lifecycle_statuses
4240: WHERE entity_name = G_ENG_CHANGE
4241: AND entity_id1 = p_change_id
4242: AND status_code = p_status_code
4243: AND active_flag = 'Y'

Line 4310: FROM eng_lifecycle_statuses

4306:
4307: -- Get the sequence number for the current phase of the change header
4308: SELECT sequence_number
4309: INTO l_curr_phase_sn
4310: FROM eng_lifecycle_statuses
4311: WHERE entity_name = G_ENG_CHANGE
4312: AND entity_id1 = p_change_id
4313: AND active_flag = 'Y'
4314: AND status_code = ( SELECT status_code

Line 4475: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;

4471: l_msg_data VARCHAR2(2000);
4472:
4473: l_curr_status_code eng_engineering_changes.status_code%TYPE;
4474: l_curr_status_type eng_engineering_changes.status_type%TYPE;
4475: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
4476: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;
4477: BEGIN
4478: -- Standard Start of API savepoint
4479: SAVEPOINT Change_Phase;

Line 4476: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;

4472:
4473: l_curr_status_code eng_engineering_changes.status_code%TYPE;
4474: l_curr_status_type eng_engineering_changes.status_type%TYPE;
4475: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
4476: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;
4477: BEGIN
4478: -- Standard Start of API savepoint
4479: SAVEPOINT Change_Phase;
4480: -- Standard call to check for call compatibility

Line 4530: FROM eng_lifecycle_statuses

4526:
4527: -- Get the workflow route id and status for the current phase
4528: SELECT change_wf_route_id, workflow_status
4529: INTO l_wf_route_id, l_wf_status
4530: FROM eng_lifecycle_statuses
4531: WHERE entity_name = G_ENG_CHANGE
4532: AND entity_id1 = p_change_id
4533: AND status_code = l_curr_status_code
4534: AND active_flag = 'Y'

Line 4863: l_min_appr_sn eng_lifecycle_statuses.sequence_number%TYPE;

4859: l_next_status_code NUMBER;
4860: l_last_status_code NUMBER;
4861:
4862: l_status_type NUMBER; -- status_type for p_status_code as incoming parameter
4863: l_min_appr_sn eng_lifecycle_statuses.sequence_number%TYPE;
4864:
4865: l_last_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
4866:
4867: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

Line 4865: l_last_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;

4861:
4862: l_status_type NUMBER; -- status_type for p_status_code as incoming parameter
4863: l_min_appr_sn eng_lifecycle_statuses.sequence_number%TYPE;
4864:
4865: l_last_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
4866:
4867: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4868: l_max_appr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4869: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

Line 4867: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

4863: l_min_appr_sn eng_lifecycle_statuses.sequence_number%TYPE;
4864:
4865: l_last_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
4866:
4867: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4868: l_max_appr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4869: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4870:
4871: l_nir_update_flag VARCHAR2(1) := 'F';

Line 4868: l_max_appr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

4864:
4865: l_last_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
4866:
4867: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4868: l_max_appr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4869: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4870:
4871: l_nir_update_flag VARCHAR2(1) := 'F';
4872:

Line 4869: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

4865: l_last_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
4866:
4867: l_curr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4868: l_max_appr_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4869: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
4870:
4871: l_nir_update_flag VARCHAR2(1) := 'F';
4872:
4873: CURSOR c_lines IS

Line 4888: l_auto_demote_status eng_lifecycle_statuses.status_code%TYPE;

4884:
4885: l_imp_eco_flag VARCHAR2(1) := 'N';
4886:
4887: l_is_co_last_phase VARCHAR2(1);
4888: l_auto_demote_status eng_lifecycle_statuses.status_code%TYPE;
4889:
4890: l_flag_imp_failed VARCHAR2(1) := 'N';
4891:
4892: BEGIN

Line 4991: FROM eng_lifecycle_statuses

4987:
4988: -- Get the current phase's sequence number
4989: SELECT sequence_number
4990: INTO l_curr_phase_sn
4991: FROM eng_lifecycle_statuses
4992: WHERE entity_name = G_ENG_CHANGE
4993: AND entity_id1 = p_change_id
4994: AND status_code = l_curr_status_code
4995: AND active_flag = 'Y'

Line 5002: FROM eng_lifecycle_statuses

4998: -- Get the sequence_number of the last phase
4999: -- Note that only phase of IMPLEMENT type can be the last phase
5000: SELECT max(sequence_number)
5001: INTO l_max_phase_sn
5002: FROM eng_lifecycle_statuses
5003: WHERE entity_name = G_ENG_CHANGE
5004: AND entity_id1 = p_change_id
5005: AND active_flag = 'Y';
5006:

Line 5010: FROM eng_lifecycle_statuses

5006:
5007: -- Get the sequence number of the last phase
5008: SELECT status_code, change_wf_route_id
5009: INTO l_last_status_code, l_last_wf_route_id
5010: FROM eng_lifecycle_statuses
5011: WHERE entity_name = G_ENG_CHANGE
5012: AND entity_id1 = p_change_id
5013: AND active_flag = 'Y'
5014: AND sequence_number = l_max_phase_sn;

Line 5031: UPDATE eng_lifecycle_statuses

5027: Write_Debug('In block: case (special) last implement phase of ECO: workflow completion.');
5028: END IF;
5029:
5030: -- Update the current phase's workflow status
5031: UPDATE eng_lifecycle_statuses
5032: SET workflow_status = p_route_status,
5033: completion_date = sysdate, -- newly added for 3479509 fix (launch wf after implementation)
5034: last_update_date = sysdate,
5035: last_updated_by = l_fnd_user_id,

Line 5043: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');

5039: AND status_code = l_next_status_code
5040: AND active_flag = 'Y'
5041: AND rownum = 1;
5042: IF g_debug_flag THEN
5043: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');
5044: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
5045: END IF;
5046:
5047: -- Case: (special 2) last implement phase of ECO direct promotion w/o wf

Line 5276: UPDATE eng_lifecycle_statuses

5272: Write_Debug('Case 1: no workflow or workflow is approved');
5273: END IF;
5274:
5275: -- Update the current phase's workflow status
5276: UPDATE eng_lifecycle_statuses
5277: SET workflow_status = p_route_status,
5278: last_update_date = sysdate,
5279: last_updated_by = l_fnd_user_id,
5280: last_update_login = l_fnd_login_id

Line 5287: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');

5283: AND status_code = l_curr_status_code
5284: AND active_flag = 'Y'
5285: AND rownum = 1;
5286: IF g_debug_flag THEN
5287: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');
5288: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
5289: END IF;
5290:
5291: -- IF the current phase is the last one, also update its completion date

Line 5295: UPDATE eng_lifecycle_statuses

5291: -- IF the current phase is the last one, also update its completion date
5292: -- Otherwise leave it for promote_header and demote_header procedures
5293: -- to set the completion_date
5294: IF (l_curr_phase_sn = l_max_phase_sn) THEN
5295: UPDATE eng_lifecycle_statuses
5296: SET completion_date = sysdate,
5297: last_update_date = sysdate,
5298: last_updated_by = l_fnd_user_id,
5299: last_update_login = l_fnd_login_id

Line 5306: Write_Debug('After updating eng_lifecycle_statuses.completion_date.');

5302: AND status_code = l_curr_status_code
5303: AND active_flag = 'Y'
5304: AND rownum = 1;
5305: IF g_debug_flag THEN
5306: Write_Debug('After updating eng_lifecycle_statuses.completion_date.');
5307: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
5308: END IF;
5309: END IF;
5310:

Line 5360: FROM eng_lifecycle_statuses lcs,

5356:
5357: -- Get the sequence number of the last phase of type REVIEW/APPROVAL
5358: SELECT max(lcs.sequence_number)
5359: INTO l_max_appr_phase_sn
5360: FROM eng_lifecycle_statuses lcs,
5361: eng_change_statuses chs
5362: WHERE lcs.entity_name = G_ENG_CHANGE
5363: AND lcs.entity_id1 = p_change_id
5364: AND lcs.active_flag = 'Y'

Line 5560: FROM eng_lifecycle_statuses

5556: Write_Debug('promote_status_code is NULL');
5557: END IF;
5558: SELECT auto_promote_status
5559: INTO l_next_status_code
5560: FROM eng_lifecycle_statuses
5561: WHERE entity_name = G_ENG_CHANGE
5562: AND entity_id1 = p_change_id
5563: AND status_code = l_curr_status_code
5564: AND active_flag = 'Y'

Line 5635: UPDATE eng_lifecycle_statuses

5631: Write_Debug('Case 2: workflow is rejected');
5632: END IF;
5633:
5634: -- Update lifecycle table
5635: UPDATE eng_lifecycle_statuses
5636: SET workflow_status = p_route_status,
5637: last_update_date = sysdate,
5638: last_updated_by = l_fnd_user_id,
5639: last_update_login = l_fnd_login_id

Line 5646: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');

5642: AND status_code = l_curr_status_code
5643: AND active_flag = 'Y'
5644: AND rownum = 1;
5645: IF g_debug_flag THEN
5646: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');
5647: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
5648: END IF;
5649:
5650: IF g_debug_flag THEN

Line 5657: FROM eng_lifecycle_statuses

5653:
5654: -- Get the auto demotion phase for the current phase of the change header
5655: SELECT auto_demote_status
5656: INTO l_next_status_code
5657: FROM eng_lifecycle_statuses
5658: WHERE entity_name = G_ENG_CHANGE
5659: AND entity_id1 = p_change_id
5660: AND status_code = l_curr_status_code
5661: AND active_flag = 'Y'

Line 5798: UPDATE eng_lifecycle_statuses

5794: Write_Debug('Case 3: workflow ends with unexpected status');
5795: END IF;
5796:
5797: -- Update lifecycle table
5798: UPDATE eng_lifecycle_statuses
5799: SET workflow_status = p_route_status,
5800: last_update_date = sysdate,
5801: last_updated_by = l_fnd_user_id,
5802: last_update_login = l_fnd_login_id

Line 5809: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');

5805: AND status_code = l_curr_status_code
5806: AND active_flag = 'Y'
5807: AND rownum = 1;
5808: IF g_debug_flag THEN
5809: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');
5810: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
5811: END IF;
5812:
5813:

Line 5826: Write_Debug('After updating eng_lifecycle_statuses.promote_status_code.');

5822: last_updated_by = l_fnd_user_id,
5823: last_update_login = l_fnd_login_id
5824: WHERE change_id = p_change_id;
5825: IF g_debug_flag THEN
5826: Write_Debug('After updating eng_lifecycle_statuses.promote_status_code.');
5827: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
5828: END IF;
5829:
5830: IF g_debug_flag THEN

Line 5883: UPDATE eng_lifecycle_statuses

5879: FND_MSG_PUB.Add;
5880: RAISE FND_API.G_EXC_ERROR;
5881: END IF;
5882: -- update phase workflow status
5883: UPDATE eng_lifecycle_statuses
5884: SET workflow_status = p_route_status,
5885: last_update_date = sysdate,
5886: last_updated_by = l_fnd_user_id,
5887: last_update_login = l_fnd_login_id

Line 5893: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');

5889: AND entity_id1 = p_change_id
5890: AND status_code = p_status_code
5891: AND active_flag = 'Y';
5892: IF g_debug_flag THEN
5893: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');
5894: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
5895: END IF;
5896:
5897: -- update header approval status to "submitted" if the p_status_code passed

Line 6279: FROM eng_lifecycle_statuses

6275: l_fnd_login_id NUMBER := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
6276:
6277: CURSOR c_currPhase IS
6278: SELECT *
6279: FROM eng_lifecycle_statuses
6280: WHERE entity_name = G_ENG_CHANGE
6281: AND entity_id1 = p_change_id
6282: AND status_code = p_status_code
6283: AND active_flag = 'Y'

Line 6286: l_phase_row eng_lifecycle_statuses%ROWTYPE;

6282: AND status_code = p_status_code
6283: AND active_flag = 'Y'
6284: FOR UPDATE;
6285: l_row_counter NUMBER := 0;
6286: l_phase_row eng_lifecycle_statuses%ROWTYPE;
6287: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
6288: l_wf_route_id_new eng_lifecycle_statuses.change_wf_route_id%TYPE;
6289: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;
6290:

Line 6287: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;

6283: AND active_flag = 'Y'
6284: FOR UPDATE;
6285: l_row_counter NUMBER := 0;
6286: l_phase_row eng_lifecycle_statuses%ROWTYPE;
6287: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
6288: l_wf_route_id_new eng_lifecycle_statuses.change_wf_route_id%TYPE;
6289: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;
6290:
6291: BEGIN

Line 6288: l_wf_route_id_new eng_lifecycle_statuses.change_wf_route_id%TYPE;

6284: FOR UPDATE;
6285: l_row_counter NUMBER := 0;
6286: l_phase_row eng_lifecycle_statuses%ROWTYPE;
6287: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
6288: l_wf_route_id_new eng_lifecycle_statuses.change_wf_route_id%TYPE;
6289: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;
6290:
6291: BEGIN
6292: -- Standard Start of API savepoint

Line 6289: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;

6285: l_row_counter NUMBER := 0;
6286: l_phase_row eng_lifecycle_statuses%ROWTYPE;
6287: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
6288: l_wf_route_id_new eng_lifecycle_statuses.change_wf_route_id%TYPE;
6289: l_wf_status eng_lifecycle_statuses.workflow_status%TYPE;
6290:
6291: BEGIN
6292: -- Standard Start of API savepoint
6293: SAVEPOINT Refresh_WF_Route;

Line 6383: UPDATE eng_lifecycle_statuses

6379: P_USER_ID => l_fnd_user_id,
6380: P_API_CALLER => p_api_caller
6381: );
6382: -- Replace the old id with the new id
6383: UPDATE eng_lifecycle_statuses
6384: SET change_wf_route_id = l_wf_route_id_new,
6385: workflow_status = ENG_WORKFLOW_UTIL.G_RT_NOT_STARTED,
6386: last_update_date = sysdate,
6387: last_updated_by = l_fnd_user_id,

Line 6400: UPDATE eng_lifecycle_statuses

6396: ELSE
6397: IF g_debug_flag THEN
6398: Write_Debug('Branch: p_route_id is not null') ;
6399: END IF;
6400: UPDATE eng_lifecycle_statuses
6401: SET change_wf_route_id = p_wf_route_id,
6402: workflow_status = ENG_WORKFLOW_UTIL.G_RT_NOT_STARTED,
6403: last_update_date = sysdate,
6404: last_updated_by = l_fnd_user_id,

Line 6523: l_initial_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

6519: l_cm_type_code eng_engineering_changes.change_mgmt_type_code%TYPE;
6520: l_base_cm_type_code eng_change_order_types.BASE_CHANGE_MGMT_TYPE_CODE%TYPE;
6521: l_bug_number NUMBER := 0;
6522:
6523: l_initial_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
6524:
6525: l_status_code eng_lifecycle_statuses.status_code%TYPE;
6526: l_status_type eng_engineering_changes.status_type%TYPE;
6527: l_sequence_number eng_lifecycle_statuses.sequence_number%TYPE;

Line 6525: l_status_code eng_lifecycle_statuses.status_code%TYPE;

6521: l_bug_number NUMBER := 0;
6522:
6523: l_initial_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
6524:
6525: l_status_code eng_lifecycle_statuses.status_code%TYPE;
6526: l_status_type eng_engineering_changes.status_type%TYPE;
6527: l_sequence_number eng_lifecycle_statuses.sequence_number%TYPE;
6528: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
6529:

Line 6527: l_sequence_number eng_lifecycle_statuses.sequence_number%TYPE;

6523: l_initial_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
6524:
6525: l_status_code eng_lifecycle_statuses.status_code%TYPE;
6526: l_status_type eng_engineering_changes.status_type%TYPE;
6527: l_sequence_number eng_lifecycle_statuses.sequence_number%TYPE;
6528: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
6529:
6530: l_pls_block VARCHAR2(5000);
6531:

Line 6528: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;

6524:
6525: l_status_code eng_lifecycle_statuses.status_code%TYPE;
6526: l_status_type eng_engineering_changes.status_type%TYPE;
6527: l_sequence_number eng_lifecycle_statuses.sequence_number%TYPE;
6528: l_wf_route_id eng_lifecycle_statuses.change_wf_route_id%TYPE;
6529:
6530: l_pls_block VARCHAR2(5000);
6531:
6532: -- for auto-propagation if it's defined for first phase

Line 6618: FROM eng_lifecycle_statuses

6614: -- Get the first phase from the change header's lifecycle definition
6615: -- In R12, added p_init_status_code condition here
6616: SELECT min(sequence_number)
6617: INTO l_initial_phase_sn
6618: FROM eng_lifecycle_statuses
6619: WHERE entity_name = G_ENG_CHANGE
6620: AND entity_id1 = p_change_id
6621: AND active_flag = 'Y'
6622: AND (status_code = p_init_status_code OR p_init_status_code IS NULL) ;

Line 6627: FROM eng_lifecycle_statuses lcs,

6623:
6624:
6625: SELECT lcs.status_code, ecs.status_type
6626: INTO l_status_code, l_status_type
6627: FROM eng_lifecycle_statuses lcs,
6628: eng_change_statuses ecs
6629: WHERE lcs.entity_name = G_ENG_CHANGE
6630: AND lcs.entity_id1 = p_change_id
6631: AND lcs.active_flag = 'Y'

Line 6666: FROM eng_lifecycle_statuses

6662:
6663: -- Get the sequence number for the current phase
6664: SELECT sequence_number, change_wf_route_id
6665: INTO l_sequence_number, l_wf_route_id
6666: FROM eng_lifecycle_statuses
6667: WHERE entity_name = G_ENG_CHANGE
6668: AND entity_id1 = p_change_id
6669: AND active_flag = 'Y'
6670: AND status_code = l_status_code;

Line 6696: UPDATE eng_lifecycle_statuses

6692: END IF;
6693: END IF;
6694:
6695: -- Initialize workflow_status for all phases that have workflows
6696: UPDATE eng_lifecycle_statuses
6697: SET workflow_status = Eng_Workflow_Util.G_RT_NOT_STARTED,
6698: last_update_date = sysdate,
6699: last_updated_by = l_fnd_user_id,
6700: last_update_login = l_fnd_login_id

Line 6706: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');

6702: AND entity_id1 = p_change_id
6703: AND active_flag = 'Y'
6704: AND change_wf_route_id IS NOT NULL;
6705: IF g_debug_flag THEN
6706: Write_Debug('After updating eng_lifecycle_statuses.workflow_status.');
6707: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
6708: END IF;
6709:
6710: IF g_debug_flag THEN

Line 6715: UPDATE eng_lifecycle_statuses

6711: Write_Debug('After: Initialize workflow_status for all phases that have workflows');
6712: END IF;
6713:
6714: -- Update current phase row
6715: UPDATE eng_lifecycle_statuses
6716: SET start_date = sysdate,
6717: last_update_date = sysdate,
6718: last_updated_by = l_fnd_user_id,
6719: last_update_login = l_fnd_login_id

Line 6726: Write_Debug('After updating eng_lifecycle_statuses.start_date.');

6722: AND status_code = l_status_code
6723: AND active_flag = 'Y';
6724:
6725: IF g_debug_flag THEN
6726: Write_Debug('After updating eng_lifecycle_statuses.start_date.');
6727: Write_Debug(' Row count = ' || SQL%ROWCOUNT);
6728: END IF;
6729:
6730: IF g_debug_flag THEN

Line 7269: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;

7265:
7266: l_next_status_code NUMBER;
7267: l_reset_status_code NUMBER;
7268: l_last_status_code NUMBER;
7269: l_max_phase_sn eng_lifecycle_statuses.sequence_number%TYPE;
7270: l_curr_status_type eng_engineering_changes.status_type%TYPE;
7271:
7272: -- l_last_imp_flag VARCHAR2(1) := 'N';
7273:

Line 7393: -- FROM eng_lifecycle_statuses

7389: --
7390: -- Get the current phase's sequence number
7391: -- SELECT sequence_number
7392: -- INTO l_curr_phase_sn
7393: -- FROM eng_lifecycle_statuses
7394: -- WHERE entity_name = G_ENG_CHANGE
7395: -- AND entity_id1 = p_change_id
7396: -- AND status_code = l_reset_status_code
7397: -- AND active_flag = 'Y'

Line 7717: FROM eng_lifecycle_statuses

7713: , AUTO_DEMOTE_STATUS
7714: , CHANGE_EDITABLE_FLAG
7715: , CHANGE_WF_ROUTE_ID
7716: , ENTITY_ID1 CHANGE_TYPE_ID
7717: FROM eng_lifecycle_statuses
7718: WHERE entity_name = 'ENG_CHANGE_TYPE'
7719: AND entity_id1 = c_change_type_id
7720: ORDER BY SEQUENCE_NUMBER ASC ;
7721:

Line 7787: INSERT INTO ENG_LIFECYCLE_STATUSES

7783: END IF ;
7784:
7785: -- Insert the statuses data if it does not exists in change objects that have not gone past the phase
7786: -- and the phase does not exist in lifecycle of the change objects
7787: INSERT INTO ENG_LIFECYCLE_STATUSES
7788: ( CHANGE_LIFECYCLE_STATUS_ID
7789: , ENTITY_NAME
7790: , ENTITY_ID1
7791: , ENTITY_ID2

Line 7814: ENG_LIFECYCLE_STATUSES_S.NEXTVAL

7810: , ACTIVE_FLAG
7811: , CHANGE_WF_ROUTE_TEMPLATE_ID
7812: )
7813: SELECT
7814: ENG_LIFECYCLE_STATUSES_S.NEXTVAL
7815: , G_ENG_CHANGE
7816: , ch.CHANGE_ID
7817: , NULL -- ENTITY_ID2
7818: , NULL -- ENTITY_ID3

Line 7839: , ENG_LIFECYCLE_STATUSES cur_phase

7835: , cur_phase.ITERATION_NUMBER
7836: , 'S'
7837: , change_type.CHANGE_WF_ROUTE_ID
7838: FROM ENG_ENGINEERING_CHANGES ch
7839: , ENG_LIFECYCLE_STATUSES cur_phase
7840: , ENG_LIFECYCLE_STATUSES change_type
7841: WHERE change_type.entity_id1 = p_change_type_id
7842: AND change_type.entity_name = 'ENG_CHANGE_TYPE'
7843: AND ch.CHANGE_ORDER_TYPE_ID = change_type.entity_id1

Line 7840: , ENG_LIFECYCLE_STATUSES change_type

7836: , 'S'
7837: , change_type.CHANGE_WF_ROUTE_ID
7838: FROM ENG_ENGINEERING_CHANGES ch
7839: , ENG_LIFECYCLE_STATUSES cur_phase
7840: , ENG_LIFECYCLE_STATUSES change_type
7841: WHERE change_type.entity_id1 = p_change_type_id
7842: AND change_type.entity_name = 'ENG_CHANGE_TYPE'
7843: AND ch.CHANGE_ORDER_TYPE_ID = change_type.entity_id1
7844: AND ch.STATUS_TYPE NOT IN (0, 5, 6, 11) -- exclude draft, cancel, implemented, completed

Line 7852: FROM ENG_LIFECYCLE_STATUSES change_lc_phase

7848: AND cur_phase.active_flag = 'Y'
7849: AND cur_phase.status_code = ch.STATUS_CODE
7850: AND cur_phase.SEQUENCE_NUMBER < change_type.SEQUENCE_NUMBER
7851: AND NOT EXISTS ( SELECT 'exists'
7852: FROM ENG_LIFECYCLE_STATUSES change_lc_phase
7853: WHERE change_lc_phase.entity_name = G_ENG_CHANGE
7854: AND change_lc_phase.entity_id1 = ch.CHANGE_ID
7855: AND change_lc_phase.active_flag = 'Y'
7856: AND change_lc_phase.status_code = change_type.STATUS_CODE

Line 7873: INSERT INTO ENG_LIFECYCLE_STATUSES

7869: Write_Debug(' Add LC Phase for Draft Change Objects. . .') ;
7870: END IF ;
7871:
7872: -- Add LC Phase for Draft Change Objects
7873: INSERT INTO ENG_LIFECYCLE_STATUSES
7874: ( CHANGE_LIFECYCLE_STATUS_ID
7875: , ENTITY_NAME
7876: , ENTITY_ID1
7877: , ENTITY_ID2

Line 7900: ENG_LIFECYCLE_STATUSES_S.NEXTVAL

7896: , ACTIVE_FLAG
7897: , CHANGE_WF_ROUTE_TEMPLATE_ID
7898: )
7899: SELECT
7900: ENG_LIFECYCLE_STATUSES_S.NEXTVAL
7901: , G_ENG_CHANGE
7902: , ch.CHANGE_ID
7903: , NULL -- ENTITY_ID2
7904: , NULL -- ENTITY_ID3

Line 7925: , ENG_LIFECYCLE_STATUSES change_type

7921: , 0 -- ITERATION_NUMBER
7922: , 'S' -- ACTIVE_FLAG
7923: , change_type.CHANGE_WF_ROUTE_ID
7924: FROM ENG_ENGINEERING_CHANGES ch
7925: , ENG_LIFECYCLE_STATUSES change_type
7926: WHERE change_type.entity_id1 = p_change_type_id
7927: AND change_type.entity_name = 'ENG_CHANGE_TYPE'
7928: AND ch.CHANGE_ORDER_TYPE_ID = change_type.entity_id1
7929: AND ch.STATUS_CODE = 0 -- DRAFT

Line 7931: FROM ENG_LIFECYCLE_STATUSES change_lc_phase

7927: AND change_type.entity_name = 'ENG_CHANGE_TYPE'
7928: AND ch.CHANGE_ORDER_TYPE_ID = change_type.entity_id1
7929: AND ch.STATUS_CODE = 0 -- DRAFT
7930: AND NOT EXISTS ( SELECT 'exists'
7931: FROM ENG_LIFECYCLE_STATUSES change_lc_phase
7932: WHERE change_lc_phase.entity_name = G_ENG_CHANGE
7933: AND change_lc_phase.entity_id1 = ch.CHANGE_ID
7934: AND change_lc_phase.active_flag = 'Y'
7935: AND change_lc_phase.status_code = change_type.STATUS_CODE

Line 7974: , ENG_LIFECYCLE_STATUSES lc_phase_setup

7970: , SYSDATE
7971: , l_fnd_user_id
7972: , l_fnd_login_id
7973: FROM ENG_STATUS_PROPERTIES phase_prop_setup
7974: , ENG_LIFECYCLE_STATUSES lc_phase_setup
7975: , ENG_LIFECYCLE_STATUSES lc_phase
7976: , ENG_ENGINEERING_CHANGES ch
7977: WHERE lc_phase_setup.entity_id1 = p_change_type_id
7978: AND lc_phase_setup.entity_name = 'ENG_CHANGE_TYPE'

Line 7975: , ENG_LIFECYCLE_STATUSES lc_phase

7971: , l_fnd_user_id
7972: , l_fnd_login_id
7973: FROM ENG_STATUS_PROPERTIES phase_prop_setup
7974: , ENG_LIFECYCLE_STATUSES lc_phase_setup
7975: , ENG_LIFECYCLE_STATUSES lc_phase
7976: , ENG_ENGINEERING_CHANGES ch
7977: WHERE lc_phase_setup.entity_id1 = p_change_type_id
7978: AND lc_phase_setup.entity_name = 'ENG_CHANGE_TYPE'
7979: AND phase_prop_setup.CHANGE_LIFECYCLE_STATUS_ID = lc_phase_setup.CHANGE_LIFECYCLE_STATUS_ID

Line 8015: UPDATE ENG_LIFECYCLE_STATUSES

8011: Write_Debug(' Removing a Phase. . .') ;
8012: END IF ;
8013:
8014:
8015: UPDATE ENG_LIFECYCLE_STATUSES
8016: SET ACTIVE_FLAG = 'D'
8017: WHERE CHANGE_LIFECYCLE_STATUS_ID IN (
8018: SELECT change_phase.CHANGE_LIFECYCLE_STATUS_ID
8019: FROM ENG_ENGINEERING_CHANGES ch

Line 8020: , ENG_LIFECYCLE_STATUSES cur_phase

8016: SET ACTIVE_FLAG = 'D'
8017: WHERE CHANGE_LIFECYCLE_STATUS_ID IN (
8018: SELECT change_phase.CHANGE_LIFECYCLE_STATUS_ID
8019: FROM ENG_ENGINEERING_CHANGES ch
8020: , ENG_LIFECYCLE_STATUSES cur_phase
8021: , ENG_LIFECYCLE_STATUSES change_phase
8022: WHERE ch.CHANGE_ORDER_TYPE_ID = p_change_type_id
8023: AND ch.STATUS_TYPE NOT IN (5, 6, 11) -- exclude cancel, implemented, completed
8024: AND cur_phase.entity_name = G_ENG_CHANGE

Line 8021: , ENG_LIFECYCLE_STATUSES change_phase

8017: WHERE CHANGE_LIFECYCLE_STATUS_ID IN (
8018: SELECT change_phase.CHANGE_LIFECYCLE_STATUS_ID
8019: FROM ENG_ENGINEERING_CHANGES ch
8020: , ENG_LIFECYCLE_STATUSES cur_phase
8021: , ENG_LIFECYCLE_STATUSES change_phase
8022: WHERE ch.CHANGE_ORDER_TYPE_ID = p_change_type_id
8023: AND ch.STATUS_TYPE NOT IN (5, 6, 11) -- exclude cancel, implemented, completed
8024: AND cur_phase.entity_name = G_ENG_CHANGE
8025: AND cur_phase.entity_id1 = ch.CHANGE_ID

Line 8033: FROM eng_lifecycle_statuses lc_phase_setup

8029: AND change_phase.entity_name = 'ENG_CHANGE'
8030: AND change_phase.entity_id1 = ch.CHANGE_ID
8031: AND change_phase.active_flag = 'Y'
8032: AND NOT EXISTS ( SELECT 'exists'
8033: FROM eng_lifecycle_statuses lc_phase_setup
8034: WHERE lc_phase_setup.entity_name = 'ENG_CHANGE_TYPE'
8035: AND lc_phase_setup.entity_id1 = ch.CHANGE_ORDER_TYPE_ID
8036: AND lc_phase_setup.SEQUENCE_NUMBER = change_phase.SEQUENCE_NUMBER
8037: AND lc_phase_setup.STATUS_CODE = change_phase.STATUS_CODE

Line 8042: , ENG_LIFECYCLE_STATUSES change_phase

8038: )
8039: UNION ALL
8040: SELECT change_phase.CHANGE_LIFECYCLE_STATUS_ID
8041: FROM ENG_ENGINEERING_CHANGES ch
8042: , ENG_LIFECYCLE_STATUSES change_phase
8043: WHERE ch.CHANGE_ORDER_TYPE_ID = p_change_type_id
8044: AND ch.STATUS_CODE = 0 -- DRAFT
8045: AND ch.STATUS_TYPE NOT IN (5, 6, 11) -- exclude cancel, implemented, completed
8046: AND change_phase.entity_name = G_ENG_CHANGE

Line 8050: FROM eng_lifecycle_statuses lc_phase_setup

8046: AND change_phase.entity_name = G_ENG_CHANGE
8047: AND change_phase.entity_id1 = ch.CHANGE_ID
8048: AND change_phase.active_flag = 'Y'
8049: AND NOT EXISTS ( SELECT 'exists'
8050: FROM eng_lifecycle_statuses lc_phase_setup
8051: WHERE lc_phase_setup.entity_name = 'ENG_CHANGE_TYPE'
8052: AND lc_phase_setup.entity_id1 = ch.CHANGE_ORDER_TYPE_ID
8053: AND lc_phase_setup.SEQUENCE_NUMBER = change_phase.SEQUENCE_NUMBER
8054: AND lc_phase_setup.STATUS_CODE = change_phase.STATUS_CODE

Line 8073: UPDATE ENG_LIFECYCLE_STATUSES lc_phase

8069: END IF ;
8070:
8071: -- 3. Mass Update Change LC Phases marked as 'S' in the above process of syncronization of newly added phase.
8072: -- to make them Activie
8073: UPDATE ENG_LIFECYCLE_STATUSES lc_phase
8074: SET lc_phase.ACTIVE_FLAG = 'Y'
8075: WHERE lc_phase.CHANGE_LIFECYCLE_STATUS_ID IN (
8076: SELECT added_change_phase.CHANGE_LIFECYCLE_STATUS_ID
8077: FROM ENG_ENGINEERING_CHANGES ch

Line 8078: , ENG_LIFECYCLE_STATUSES added_change_phase

8074: SET lc_phase.ACTIVE_FLAG = 'Y'
8075: WHERE lc_phase.CHANGE_LIFECYCLE_STATUS_ID IN (
8076: SELECT added_change_phase.CHANGE_LIFECYCLE_STATUS_ID
8077: FROM ENG_ENGINEERING_CHANGES ch
8078: , ENG_LIFECYCLE_STATUSES added_change_phase
8079: WHERE ch.CHANGE_ORDER_TYPE_ID = p_change_type_id
8080: AND ch.STATUS_TYPE NOT IN (5, 6, 11) -- exclude cancel, implemented, completed
8081: AND added_change_phase.entity_name = G_ENG_CHANGE
8082: AND added_change_phase.entity_id1 = ch.CHANGE_ID