[Home] [Help]
3389: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3390: lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
3391: lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
3392: ELSE
3393: lv_tbl:= 'MSC_RESOURCE_REQUIREMENTS';
3394: lv_supplies_tbl:= 'MSC_SUPPLIES';
3395: END IF;
3396:
3397: /** PREPLACE CHANGE START **/
3533: ||' CREATION_DATE,'
3534: ||' CREATED_BY)'
3535: ||'VALUES'
3536: ||'( -1,'
3537: ||' MSC_RESOURCE_REQUIREMENTS_S.NEXTVAL,'
3538: ||' :DEPARTMENT_ID,'
3539: ||' :RESOURCE_ID,'
3540: ||' :ORGANIZATION_ID,'
3541: ||' :ASSEMBLY_ITEM_ID,'
3659: ||' CREATION_DATE,'
3660: ||' CREATED_BY)'
3661: ||' SELECT'
3662: ||' -1,'
3663: ||' MSC_RESOURCE_REQUIREMENTS_S.NEXTVAL,'
3664: ||' msrr.DEPARTMENT_ID,'
3665: ||' msrr.RESOURCE_ID,'
3666: ||' msrr.ORGANIZATION_ID,'
3667: ||' NVL(miil.INVENTORY_ITEM_ID,ms.inventory_item_id),'
3743:
3744: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3745: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3746: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3747: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3748: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3749:
3750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3751: RAISE;
3753: ELSE
3754: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3755: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3756: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3757: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3758: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3759:
3760: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3761:
3779: -- BUG 7521174
3780: -- Delete the past resource requirements as it is not required
3781: -- by GOP based on ODS data.
3782:
3783: DELETE FROM MSC_RESOURCE_REQUIREMENTS
3784: WHERE PLAN_ID= -1
3785: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3786: AND WIP_ENTITY_ID= c_rec.WIP_ENTITY_ID
3787: AND OPERATION_SEQ_NUM= NVL( c_rec.OPERATION_SEQ_NUM, OPERATION_SEQ_NUM)
3787: AND OPERATION_SEQ_NUM= NVL( c_rec.OPERATION_SEQ_NUM, OPERATION_SEQ_NUM)
3788: AND ORIG_RESOURCE_SEQ_NUM= NVL(c_rec.ORIG_RESOURCE_SEQ_NUM, ORIG_RESOURCE_SEQ_NUM);
3789:
3790: /*
3791: UPDATE MSC_RESOURCE_REQUIREMENTS
3792: SET RESOURCE_HOURS= 0,
3793: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3794: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3795: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3873: END IF;
3874:
3875: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
3876:
3877: UPDATE MSC_RESOURCE_REQUIREMENTS
3878: SET
3879: DEPARTMENT_ID= lv_DEPARTMENT_ID,
3880: RESOURCE_ID= lv_RESOURCE_ID,
3881: ASSEMBLY_ITEM_ID = lv_ASSEMBLY_ITEM_ID,
4015:
4016: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4017: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4018: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
4019: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
4020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4021:
4022: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4023: RAISE;
4028:
4029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4030: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4031: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
4032: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
4033: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4034:
4035: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4036: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
4072: lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
4073:
4074: lv_sql_stmt:=
4075: 'INSERT INTO '||lv_tbl
4076: ||' SELECT * from MSC_RESOURCE_REQUIREMENTS'
4077: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4078: ||' AND plan_id = -1 '
4079: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4080:
4101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'alter temp table for res_req not handled here');
4102: ELSE
4103: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4104: lv_retcode,
4105: 'MSC_RESOURCE_REQUIREMENTS',
4106: MSC_CL_COLLECTION.v_instance_code,
4107: MSC_UTIL.G_WARNING
4108: );
4109: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4135: -- Procedur Name : Load_ODS_RES_REQ
4136: -- Description:
4137: -- New procedure introduced for USAF project (CMRO integration).
4138: -- Now we are populating the cmro forecast resource requirements into
4139: -- msc_resource_requirements. This is controlled by a new collection
4140: -- parameter.
4141: --=============================================================================
4142:
4143: PROCEDURE LOAD_ODS_RES_REQ
4155: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) AND
4156: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO))) THEN
4157: -- We do not need to do anything as, we are collecting both WIP and CMRO
4158: -- forecasts and the are there in the staging table.
4159: -- Or, we need not change any data in msc_resource_requirements.
4160: RETURN;
4161: END IF;
4162:
4163: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
4164: lv_temp_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
4165:
4166: lv_sql_stmt :=
4167: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_res_req_tbl
4168: ||' SELECT * FROM '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_RESOURCE_REQUIREMENTS '
4169: ||' WHERE sr_instance_id =' ||MSC_CL_COLLECTION.v_instance_id
4170: ||' AND plan_id = -1 and (';
4171:
4172: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4227: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4228: MSC_UTIL.G_ALL_ORGANIZATIONS)) THEN
4229: -- We do not need to do anything as, we are collecting both WIP and CMRO
4230: -- forecasts and the are there in the staging table.
4231: -- Or, we need not change any data in msc_resource_requirements.
4232: RETURN;
4233: END IF;
4234:
4235: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
4236: lv_temp_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
4237:
4238: lv_copySQL_fixedpart :=
4239: 'INSERT INTO '||lv_temp_res_req_tbl
4240: ||' SELECT * FROM MSC_RESOURCE_REQUIREMENTS '
4241: ||' WHERE sr_instance_id =' ||MSC_CL_COLLECTION.v_instance_id
4242: ||' AND plan_id = -1 ';
4243:
4244: