[Home] [Help]
158: /* ---PM reviewer--- end---- */
159: generate_next_work_order,
160: planner_maintenance -- WO defaults ER
161: into pm_header_row
162: from eam_pm_schedulings
163: where pm_schedule_id=p_pm_schedule_id;
164: exception
165: when no_data_found then
166: FND_MESSAGE.SET_NAME ('EAM', 'EAM_MT_SUPPLY_PARAMS');
167: FND_MSG_PUB.Add;
168: RAISE FND_API.G_EXC_ERROR;
169: end;
170:
171: select eam_pm_schedulings_s.nextval into l_pm_schedule_id from dual;
172:
173: -- If the source pm is not a template, raise error
174: if (not (pm_header_row.tmpl_flag = 'Y')) then
175: FND_MESSAGE.SET_NAME('EAM', 'EAM_PM_INST_NOT_TMPL');
207:
208:
209: if (l_validated) then
210:
211: insert into eam_pm_schedulings (
212: PM_SCHEDULE_ID,
213: ACTIVITY_ASSOCIATION_ID,
214: NON_SCHEDULED_FLAG,
215: FROM_EFFECTIVE_DATE,
459: (select initial_reading from CSI_COUNTERS_B where counter_id = a_pm_rule.meter_id
460: union
461: select initial_reading from CSI_COUNTER_TEMPLATE_B where counter_id = a_pm_rule.meter_id
462: );
463: select EAM_LAST_CYCLIC_ACT into l_last_act from eam_pm_schedulings
464: where pm_schedule_id = l_pm_schedule_id;
465:
466: -- for bug 7230256
467: l_count := 0;
583: -- of l_activity_assoc_id, that have auto_instantiation_flag as "Y"
584:
585: cursor pm_template_csr_old IS
586: select pm_schedule_id
587: from eam_pm_schedulings eps, mtl_eam_asset_activities meaa, eam_pm_set_names epsn
588: where eps.activity_association_id=meaa.source_tmpl_id
589: and meaa.activity_association_id=l_activity_assoc_id
590: and eps.tmpl_flag='Y'
591: and eps.auto_instantiation_flag='Y'
593: and (epsn.end_date is null or (epsn.end_date > sysdate and epsn.end_date > nvl(eps.to_effective_date, sysdate)));
594:
595: cursor pm_template_csr IS
596: select distinct eps.pm_schedule_id
597: from eam_pm_schedulings eps, eam_pm_activities epa,mtl_eam_asset_activities meaa, eam_pm_set_names epsn
598: where eps.pm_schedule_id=epa.pm_schedule_id
599: and epa.activity_association_id=meaa.source_tmpl_id
600: and meaa.activity_association_id=l_activity_assoc_id
601: and eps.tmpl_flag='Y'
852: FND_MSG_PUB.Add;
853: RAISE FND_API.G_EXC_ERROR;
854: END IF;
855:
856: select eam_pm_schedulings_s.nextval into l_pm_schedule_id from dual;
857:
858:
859: insert into eam_pm_schedulings (
860: PM_SCHEDULE_ID,
855:
856: select eam_pm_schedulings_s.nextval into l_pm_schedule_id from dual;
857:
858:
859: insert into eam_pm_schedulings (
860: PM_SCHEDULE_ID,
861: ACTIVITY_ASSOCIATION_ID,
862: NON_SCHEDULED_FLAG,
863: FROM_EFFECTIVE_DATE,
1354: l_act_names
1355: );
1356: if (l_validated and l_validated_act) then
1357:
1358: update eam_pm_schedulings set
1359: ACTIVITY_ASSOCIATION_ID=p_pm_schedule_rec.ACTIVITY_ASSOCIATION_ID,
1360: NON_SCHEDULED_FLAG=p_pm_schedule_rec.NON_SCHEDULED_FLAG,
1361: FROM_EFFECTIVE_DATE=p_pm_schedule_rec.FROM_EFFECTIVE_DATE,
1362: TO_EFFECTIVE_DATE=p_pm_schedule_rec.TO_EFFECTIVE_DATE,
1736: -- header validation: PM Name must be unique
1737: /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
1738: BEGIN
1739: SELECT pm_schedule_id into l_pm_schedule_id
1740: FROM eam_pm_schedulings
1741: WHERE name=p_pm_schedule_rec.name
1742: AND tmpl_flag=p_pm_schedule_rec.tmpl_flag;
1743:
1744: if (p_pm_schedule_rec.pm_schedule_id is null) then
2619: /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
2620: BEGIN
2621:
2622: select eps.pm_schedule_id into l_pm_schedule_id from
2623: eam_pm_schedulings eps,eam_pm_activities epa
2624: where eps.pm_schedule_id=epa.pm_schedule_id
2625: and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
2626: and eps.set_name_id=p_pm_schedule_rec.set_name_id
2627: and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
2654: /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
2655: BEGIN
2656:
2657: select eps.pm_schedule_id into l_pm_schedule_id from
2658: eam_pm_schedulings eps,eam_pm_activities epa
2659: where eps.pm_schedule_id=epa.pm_schedule_id
2660: and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
2661: and eps.default_implement = 'Y'
2662: and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
2748: /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
2749: BEGIN
2750:
2751: select eps.pm_schedule_id into l_pm_schedule_id from
2752: eam_pm_schedulings eps,eam_pm_activities epa
2753: where eps.pm_schedule_id=epa.pm_schedule_id
2754: and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
2755: and eps.set_name_id=p_pm_schedule_rec.set_name_id
2756: and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
2783: /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
2784: BEGIN
2785:
2786: select eps.pm_schedule_id into l_pm_schedule_id from
2787: eam_pm_schedulings eps,eam_pm_activities epa
2788: where eps.pm_schedule_id=epa.pm_schedule_id
2789: and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
2790: and eps.default_implement = 'Y'
2791: and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
3015:
3016: --getting the scheduling option for the pm schedule
3017:
3018: select rescheduling_point,current_seq into l_rescheduling_point,l_current_seq
3019: from eam_pm_schedulings
3020: where pm_schedule_id = p_pm_schedule_id;
3021:
3022: --mandatory checking for rescheduling and current interval values
3023: if (l_rescheduling_point is null) then
3040:
3041: if (l_rescheduling_point in (1,5,6)) then
3042:
3043: select max(meaa.last_service_start_date) into l_last_service_date
3044: from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3045: where epa.activity_association_id=meaa.activity_association_id and
3046: epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
3047: ((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
3048: (epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0))
3051:
3052: elsif (l_rescheduling_point = 2) then
3053:
3054: select max(meaa.last_service_end_date) into l_last_service_date
3055: from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3056: where epa.activity_association_id=meaa.activity_association_id and
3057: epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
3058: ((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
3059: (epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0))
3062:
3063: elsif (l_rescheduling_point = 3) then
3064:
3065: select max(meaa.last_scheduled_start_date) into l_last_service_date
3066: from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3067: where epa.activity_association_id=meaa.activity_association_id and
3068: epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
3069: ((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
3070: (epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0))
3073:
3074: elsif (l_rescheduling_point = 4) then
3075:
3076: select max(meaa.last_scheduled_end_date) into l_last_service_date
3077: from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3078: where epa.activity_association_id=meaa.activity_association_id and
3079: epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
3080: ((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
3081: (epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0))
3089:
3090: if (l_rescheduling_point in (1,5,6)) then
3091:
3092: select max(meaa.last_service_start_date) into l_last_service_date
3093: from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3094: where epa.activity_association_id=meaa.activity_association_id and
3095: epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id
3096: and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate; /*bug 10381070 */
3097:
3097:
3098: elsif (l_rescheduling_point = 2) then
3099:
3100: select max(meaa.last_service_end_date) into l_last_service_date
3101: from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3102: where epa.activity_association_id=meaa.activity_association_id and
3103: epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id
3104: and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate; /*bug 10381070 */
3105:
3105:
3106: elsif (l_rescheduling_point = 3) then
3107:
3108: select max(meaa.last_scheduled_start_date) into l_last_service_date
3109: from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3110: where epa.activity_association_id=meaa.activity_association_id and
3111: epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id
3112: and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate; /*bug 10381070 */
3113:
3113:
3114: elsif (l_rescheduling_point = 4) then
3115:
3116: select max(meaa.last_scheduled_end_date) into l_last_service_date
3117: from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3118: where epa.activity_association_id=meaa.activity_association_id and
3119: epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id
3120: and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate; /*bug 10381070 */
3121:
3217:
3218: -- updating the eam_last_cyclic_act field
3219: -- modified for 9754424.
3220: if (l_act_assoc_id is not null) or (l_act_assoc_id <>FND_API.G_MISS_NUM) THEN
3221: update eam_pm_schedulings set EAM_LAST_CYCLIC_ACT = l_act_assoc_id,
3222: last_update_date=sysdate,
3223: last_updated_by=fnd_global.user_id,
3224: last_update_login=fnd_global.login_id
3225: where pm_schedule_id = p_pm_schedule_id;