[Home] [Help]
55: --the rows in the mth_run_log will be at organization granularity
56: CURSOR c_ebs_orgs IS
57: SELECT ebs_organization_id,organization_code,
58: source,plant_pk,system_fk_key,from_date
59: FROM mth_plants_d, mth_systems_setup,mth_organizations_l
60: WHERE system_pk_key = system_fk_key
61: AND system_pk = p_db_global_name
62: AND NVL(to_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)--pick active plants only
63: AND plant_fk_key=plant_pk_key;
261:
262: CURSOR c_ebs_orgs
263: IS
264: SELECT ebs_organization_id,system_fk_key
265: FROM mth_plants_d, mth_systems_setup,mth_organizations_l
266: WHERE system_pk_key = system_fk_key
267: AND system_pk = p_db_global_name
268: AND NVL(to_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)--pick active plants only
269: AND plant_fk_key=plant_pk_key;
1784: --the rows in the mth_run_log will be at organization granularity
1785: CURSOR c_ebs_orgs IS
1786: SELECT ebs_organization_id,organization_code,
1787: source,plant_pk,system_fk_key,from_date
1788: FROM mth_plants_d, mth_systems_setup,mth_organizations_l
1789: WHERE system_pk_key = system_fk_key
1790: AND system_pk = p_db_global_name
1791: AND NVL(to_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)--pick active plants only
1792: AND plant_fk_key=plant_pk_key;
3074:
3075:
3076:
3077: select plant_pk into l_plant_pk from mth_plants_d where plant_pk_key=l_plant_fk_key;
3078: --SELECT organization_code INTO l_organization_code FROM mth_organizations_l WHERE plant_fk_key =
3079: --l_plant_fk_key;
3080: N := l_end_date-l_start_date;
3081:
3082: for i IN 1.. N+1
4279: SET stg.err_code = stg.err_code || 'PLT '
4280: WHERE EXISTS (SELECT * FROM ( SELECT PLANTS.PLANT_PK_KEY,STG.SHIFT_WORKDAY_PK FROM MTH_WORKDAY_SHIFTS_STG STG
4281:
4282: , ( SELECT MOL.ORGANIZATION_CODE,MP.PLANT_PK_KEY,MS.SYSTEM_PK FROM MTH_PLANTS_D MP,
4283: MTH_ORGANIZATIONS_L MOL,
4284: MTH_SYSTEMS_SETUP MS
4285: WHERE NVL(MP.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
4286: AND MP.PLANT_PK_KEY = MOL.PLANT_FK_KEY
4287: AND MOL.SYSTEM_FK_KEY = MS.SYSTEM_PK_KEY )PLANTS
4328: (SELECT stg.shift_workday_pk
4329: FROM mth_workday_shifts_stg stg,
4330: MTH_SHIFT_REFERENCE_MV MSV,
4331: mth_plants_d plants,
4332: mth_organizations_l mol,
4333: mth_systems_setup ms
4334: WHERE NVL(MSV.SHIFT_DATE,stg.shift_date-1) >= stg.shift_date
4335: AND NVL(plants.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
4336: AND plants.PLANT_PK_KEY = MOL.PLANT_FK_KEY
4406: mth_workday_shifts_D wsd,
4407: mth_shift_reference_MV mv,
4408: mth_workday_shifts_stg stg,
4409: mth_plants_d plants ,
4410: MTH_ORGANIZATIONS_L MOL,
4411: MTH_SYSTEMS_SETUP MS
4412: WHERE wsd.SHIFT_DATE > Nvl( mv.SHIFT_DATE ,wsd.shift_date-1)
4413: And (TRUNC( wsd.SHIFT_DATE ) = TRUNC ( stg.SHIFT_DATE ) OR
4414: TRUNC( wsd.FROM_DATE ) = TRUNC ( stg.SHIFT_DATE ) )
4429: mth_workday_shifts_D wsd,
4430: mth_shift_reference_MV mv,
4431: mth_workday_shifts_stg stg,
4432: mth_plants_d plants ,
4433: MTH_ORGANIZATIONS_L MOL,
4434: MTH_SYSTEMS_SETUP MS
4435: WHERE wsd.SHIFT_DATE > Nvl( mv.SHIFT_DATE,wsd.shift_date-1)
4436: And (TRUNC( wsd.SHIFT_DATE ) = TRUNC ( stg.SHIFT_DATE ) OR
4437: TRUNC( wsd.FROM_DATE ) = TRUNC ( stg.SHIFT_DATE ) )
4503: STG.SOURCE_ORG_CODE,
4504: Decode(STG.SHIFT_TYPE,NULL,'PROD-SHIFT',FL2.LOOKUP_CODE),
4505: DECODE(STG.GRAVEYARD_SHIFT,NULL,NULL,FL1.LOOKUP_CODE)
4506: FROM
4507: MTH_WORKDAY_SHIFTS_STG STG ,MTH_SYSTEMS_SETUP SP,MTH_PLANTS_D PD,MTH_ORGANIZATIONS_L MOL,FND_LOOKUP_VALUES FL1,FND_LOOKUP_VALUES FL2
4508: WHERE NVL ( STG.SYSTEM_FK , MTH_UTIL_PKG.MTH_UA_GET_VAL ) = SP.SYSTEM_PK
4509: AND NVL(PD.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
4510: AND PD.PLANT_PK_KEY = MOL.PLANT_FK_KEY
4511: AND MOL.SYSTEM_FK_KEY = SP.SYSTEM_PK_KEY
4584: FROM
4585: ( SELECT DISTINCT SHIFT_DATE,SOURCE_ORG_CODE,system_fk from
4586: MTH_WORKDAY_SHIFTS_STG STG where STG.processing_flag = v_processing_flag
4587: AND STG.ERR_CODE IS NULL
4588: ) STG ,MTH_SYSTEMS_SETUP SP,MTH_PLANTS_D PD,MTH_ORGANIZATIONS_L MOL,(SELECT DISTINCT DAY FROM(SELECT DAY FROM MTH_445_PERIOD_CALENDAR
4589: UNION
4590: SELECT DAY FROM MTH_GREGORIAN_CALENDAR
4591: UNION
4592: SELECT REPORT_DATE DAY FROM MTH_DAY_D )) CATCH_ALL