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.MTH_UTIL_PKG dependencies on MTH_WORKDAY_SHIFTS_STG

Line 4132: -- mth_util_pkg.switch_column_default_value ( 'MTH_WORKDAY_SHIFTS_STG', v_processing_flag);

4128: BEGIN
4129: -- Initialize default parameters
4130: v_log_date := sysdate;
4131: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4132: -- mth_util_pkg.switch_column_default_value ( 'MTH_WORKDAY_SHIFTS_STG', v_processing_flag);
4133: -- SELECT Count(*) INTO v_reprocess_flag FROM MTH_WORKDAY_SHIFTS_ERR WHERE REPROCESS_READY_YN='Y';
4134:
4135: -- IF ( v_processing_flag=1 OR v_processing_flag=2 OR v_reprocess_flag>0 ) THEN
4136:

Line 4143: INSERT INTO MTH_WORKDAY_SHIFTS_STG

4139:
4140:
4141: SELECT MTH_UTIL_PKG.GET_PROFILE_VAL('MTH_SHIFT_SETUP' ) INTO v_profile FROM DUAL ;
4142:
4143: INSERT INTO MTH_WORKDAY_SHIFTS_STG
4144: (SHIFT_DATE,
4145: SOURCE_ORG_CODE,
4146: USER_ATTR1,
4147: USER_ATTR2,

Line 4188: mth_util_pkg.log_msg('Number of rows inserted in MTH_WORKDAY_SHIFTS_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4184: FROM MTH_WORKDAY_SHIFTS_ERR
4185: WHERE UPPER( REPROCESS_READY_YN ) = 'Y'
4186: )
4187: ;
4188: mth_util_pkg.log_msg('Number of rows inserted in MTH_WORKDAY_SHIFTS_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4189:
4190: --delete data from the output error table
4191: DELETE FROM MTH_WORKDAY_SHIFTS_ERR
4192: WHERE UPPER(REPROCESS_READY_YN) IN ('D','Y'); --Bug 14753663

Line 4195: mth_util_pkg.switch_column_default_value('MTH_WORKDAY_SHIFTS_STG',v_processing_flag);

4191: DELETE FROM MTH_WORKDAY_SHIFTS_ERR
4192: WHERE UPPER(REPROCESS_READY_YN) IN ('D','Y'); --Bug 14753663
4193: mth_util_pkg.log_msg('Number of rows deleted in MTH_WORKDAY_SHIFTS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4194: mth_util_pkg.REFRESH_ONE_MV ('MTH_SHIFT_REFERENCE_MV',null,null,'C');
4195: mth_util_pkg.switch_column_default_value('MTH_WORKDAY_SHIFTS_STG',v_processing_flag);
4196: IF ( v_processing_flag=1 OR v_processing_flag=2 ) THEN
4197:
4198: --Execute all validations on csv records
4199:

Line 4201: UPDATE mth_workday_shifts_stg stg

4197:
4198: --Execute all validations on csv records
4199:
4200: -- Validation for Duplicate record
4201: UPDATE mth_workday_shifts_stg stg
4202: SET stg.err_code = stg.err_code || 'DUP '
4203: WHERE EXISTS ( SELECT * FROM ( SELECT shift_workday_pk,Count(shift_workday_pk) cnt
4204: FROM mth_workday_shifts_stg
4205: GROUP BY shift_workday_pk) dup

Line 4204: FROM mth_workday_shifts_stg

4200: -- Validation for Duplicate record
4201: UPDATE mth_workday_shifts_stg stg
4202: SET stg.err_code = stg.err_code || 'DUP '
4203: WHERE EXISTS ( SELECT * FROM ( SELECT shift_workday_pk,Count(shift_workday_pk) cnt
4204: FROM mth_workday_shifts_stg
4205: GROUP BY shift_workday_pk) dup
4206: WHERE dup.cnt>1
4207: AND dup.shift_workday_pk = stg.shift_workday_pk
4208: AND stg.processing_flag = v_processing_flag );

Line 4209: mth_util_pkg.log_msg('Number of rows with DUP in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4205: GROUP BY shift_workday_pk) dup
4206: WHERE dup.cnt>1
4207: AND dup.shift_workday_pk = stg.shift_workday_pk
4208: AND stg.processing_flag = v_processing_flag );
4209: mth_util_pkg.log_msg('Number of rows with DUP in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4210:
4211: -- Validation for Null From Date
4212: UPDATE mth_workday_shifts_stg stg
4213: SET stg.err_code = stg.err_code || 'NFD '

Line 4212: UPDATE mth_workday_shifts_stg stg

4208: AND stg.processing_flag = v_processing_flag );
4209: mth_util_pkg.log_msg('Number of rows with DUP in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4210:
4211: -- Validation for Null From Date
4212: UPDATE mth_workday_shifts_stg stg
4213: SET stg.err_code = stg.err_code || 'NFD '
4214: WHERE stg.from_date IS NULL
4215: AND stg.processing_flag = v_processing_flag;
4216: mth_util_pkg.log_msg('Number of rows with NFD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 4216: mth_util_pkg.log_msg('Number of rows with NFD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4212: UPDATE mth_workday_shifts_stg stg
4213: SET stg.err_code = stg.err_code || 'NFD '
4214: WHERE stg.from_date IS NULL
4215: AND stg.processing_flag = v_processing_flag;
4216: mth_util_pkg.log_msg('Number of rows with NFD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4217:
4218:
4219: -- Validation for Null To Date
4220: UPDATE mth_workday_shifts_stg stg

Line 4220: UPDATE mth_workday_shifts_stg stg

4216: mth_util_pkg.log_msg('Number of rows with NFD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4217:
4218:
4219: -- Validation for Null To Date
4220: UPDATE mth_workday_shifts_stg stg
4221: SET stg.err_code = stg.err_code || 'NTD '
4222: WHERE stg.to_date IS NULL
4223: AND stg.processing_flag = v_processing_flag;
4224: mth_util_pkg.log_msg('Number of rows with NTD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 4224: mth_util_pkg.log_msg('Number of rows with NTD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4220: UPDATE mth_workday_shifts_stg stg
4221: SET stg.err_code = stg.err_code || 'NTD '
4222: WHERE stg.to_date IS NULL
4223: AND stg.processing_flag = v_processing_flag;
4224: mth_util_pkg.log_msg('Number of rows with NTD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4225:
4226: -- Validation for From Date = To date
4227: UPDATE mth_workday_shifts_stg stg
4228: SET stg.err_code = stg.err_code || 'SDT '

Line 4227: UPDATE mth_workday_shifts_stg stg

4223: AND stg.processing_flag = v_processing_flag;
4224: mth_util_pkg.log_msg('Number of rows with NTD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4225:
4226: -- Validation for From Date = To date
4227: UPDATE mth_workday_shifts_stg stg
4228: SET stg.err_code = stg.err_code || 'SDT '
4229: WHERE stg.from_date= stg.to_date
4230: AND stg.processing_flag = v_processing_flag;
4231: mth_util_pkg.log_msg('Number of rows with SDT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 4231: mth_util_pkg.log_msg('Number of rows with SDT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4227: UPDATE mth_workday_shifts_stg stg
4228: SET stg.err_code = stg.err_code || 'SDT '
4229: WHERE stg.from_date= stg.to_date
4230: AND stg.processing_flag = v_processing_flag;
4231: mth_util_pkg.log_msg('Number of rows with SDT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4232:
4233: -- Validation for From Date > To date
4234: UPDATE mth_workday_shifts_stg stg
4235: SET stg.err_code = stg.err_code || 'DTE '

Line 4234: UPDATE mth_workday_shifts_stg stg

4230: AND stg.processing_flag = v_processing_flag;
4231: mth_util_pkg.log_msg('Number of rows with SDT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4232:
4233: -- Validation for From Date > To date
4234: UPDATE mth_workday_shifts_stg stg
4235: SET stg.err_code = stg.err_code || 'DTE '
4236: WHERE Nvl(stg.from_date,TO_DATE ('01-01-1900', 'DD-MM-YYYY')) >Nvl ( stg.To_Date, TO_DATE ('01-01-2999', 'DD-MM-YYYY') )
4237: AND stg.processing_flag = v_processing_flag;
4238: mth_util_pkg.log_msg('Number of rows with DTE in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 4238: mth_util_pkg.log_msg('Number of rows with DTE in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4234: UPDATE mth_workday_shifts_stg stg
4235: SET stg.err_code = stg.err_code || 'DTE '
4236: WHERE Nvl(stg.from_date,TO_DATE ('01-01-1900', 'DD-MM-YYYY')) >Nvl ( stg.To_Date, TO_DATE ('01-01-2999', 'DD-MM-YYYY') )
4237: AND stg.processing_flag = v_processing_flag;
4238: mth_util_pkg.log_msg('Number of rows with DTE in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4239:
4240: -- Validation for shift_type
4241:
4242: UPDATE mth_workday_shifts_stg stg

Line 4242: UPDATE mth_workday_shifts_stg stg

4238: mth_util_pkg.log_msg('Number of rows with DTE in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4239:
4240: -- Validation for shift_type
4241:
4242: UPDATE mth_workday_shifts_stg stg
4243: SET stg.err_code = stg.err_code || 'SFT '
4244: WHERE stg.SHIFT_TYPE IS NOT NULL
4245: AND EXISTS (SELECT * FROM (SELECT stg.SHIFT_TYPE SHIFT_TYPE ,fl.meaning
4246: FROM mth_workday_shifts_stg stg,

Line 4246: FROM mth_workday_shifts_stg stg,

4242: UPDATE mth_workday_shifts_stg stg
4243: SET stg.err_code = stg.err_code || 'SFT '
4244: WHERE stg.SHIFT_TYPE IS NOT NULL
4245: AND EXISTS (SELECT * FROM (SELECT stg.SHIFT_TYPE SHIFT_TYPE ,fl.meaning
4246: FROM mth_workday_shifts_stg stg,
4247: ( SELECT meaning FROM fnd_lookup_values fl WHERE fl.LOOKUP_TYPE ='MTH_SHIFT_TYPE'
4248:
4249: AND fl.LANGUAGE = USERENV('LANG')
4250: AND fl.ENABLED_FLAG ='Y' ) fl

Line 4257: mth_util_pkg.log_msg('Number of rows with SFT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4253: ) fls
4254: WHERE fls.SHIFT_TYPE = stg.SHIFT_TYPE
4255: AND stg.processing_flag = v_processing_flag
4256: AND fls.meaning IS NULL);
4257: mth_util_pkg.log_msg('Number of rows with SFT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4258:
4259: -- Validation for graveyard shift
4260: UPDATE mth_workday_shifts_stg stg
4261: SET stg.err_code = stg.err_code || 'GRA '

Line 4260: UPDATE mth_workday_shifts_stg stg

4256: AND fls.meaning IS NULL);
4257: mth_util_pkg.log_msg('Number of rows with SFT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4258:
4259: -- Validation for graveyard shift
4260: UPDATE mth_workday_shifts_stg stg
4261: SET stg.err_code = stg.err_code || 'GRA '
4262: WHERE stg.graveyard_shift IS NOT NULL
4263: AND EXISTS (SELECT * FROM (SELECT fl.meaning, stg.graveyard_shift
4264: FROM mth_workday_shifts_stg stg,

Line 4264: FROM mth_workday_shifts_stg stg,

4260: UPDATE mth_workday_shifts_stg stg
4261: SET stg.err_code = stg.err_code || 'GRA '
4262: WHERE stg.graveyard_shift IS NOT NULL
4263: AND EXISTS (SELECT * FROM (SELECT fl.meaning, stg.graveyard_shift
4264: FROM mth_workday_shifts_stg stg,
4265: ( SELECT meaning FROM fnd_lookup_values fl WHERE fl.LOOKUP_TYPE ='MTH_GRAVEYARD_SHIFT_TYPE'
4266:
4267: AND fl.LANGUAGE = USERENV('LANG')
4268: AND fl.ENABLED_FLAG ='Y' )fl

Line 4275: mth_util_pkg.log_msg('Number of rows with GRA in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4271: )flg
4272: WHERE flg.graveyard_shift = stg.graveyard_shift
4273: AND stg.processing_flag = v_processing_flag
4274: AND flg.meaning IS NULL);
4275: mth_util_pkg.log_msg('Number of rows with GRA in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4276:
4277: -- Validation for incorrect plant
4278: UPDATE mth_workday_shifts_stg stg
4279: SET stg.err_code = stg.err_code || 'PLT '

Line 4278: UPDATE mth_workday_shifts_stg stg

4274: AND flg.meaning IS NULL);
4275: mth_util_pkg.log_msg('Number of rows with GRA in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4276:
4277: -- Validation for incorrect plant
4278: UPDATE mth_workday_shifts_stg stg
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,

Line 4280: WHERE EXISTS (SELECT * FROM ( SELECT PLANTS.PLANT_PK_KEY,STG.SHIFT_WORKDAY_PK FROM MTH_WORKDAY_SHIFTS_STG STG

4276:
4277: -- Validation for incorrect plant
4278: UPDATE mth_workday_shifts_stg stg
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

Line 4294: mth_util_pkg.log_msg('Number of rows with PLT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4290: )plants1
4291: WHERE stg.shift_workday_pk = plants1.shift_workday_pk
4292: AND stg.processing_flag = v_processing_flag
4293: AND plants1.plant_pk_key IS NULL);
4294: mth_util_pkg.log_msg('Number of rows with PLT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4295:
4296: --Validation for OVP
4297: UPDATE mth_workday_shifts_stg stag
4298: SET stag.err_code = stag.err_code || 'OVP '

Line 4297: UPDATE mth_workday_shifts_stg stag

4293: AND plants1.plant_pk_key IS NULL);
4294: mth_util_pkg.log_msg('Number of rows with PLT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4295:
4296: --Validation for OVP
4297: UPDATE mth_workday_shifts_stg stag
4298: SET stag.err_code = stag.err_code || 'OVP '
4299: WHERE EXISTS (SELECT *
4300: FROM
4301: (SELECT CASE

Line 4316: FROM mth_workday_shifts_stg stg ) ovp

4312: THEN 1 END overlap,
4313: stg.shift_workday_pk,
4314: stg.from_date,
4315: stg.to_date
4316: FROM mth_workday_shifts_stg stg ) ovp
4317: WHERE ovp.overlap = 1
4318: AND stag.shift_workday_pk = ovp.shift_workday_pk
4319: AND stag.from_date = ovp.from_date
4320: AND stag.To_Date = ovp.To_Date);

Line 4321: mth_util_pkg.log_msg('Number of rows with OVP in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4317: WHERE ovp.overlap = 1
4318: AND stag.shift_workday_pk = ovp.shift_workday_pk
4319: AND stag.from_date = ovp.from_date
4320: AND stag.To_Date = ovp.To_Date);
4321: mth_util_pkg.log_msg('Number of rows with OVP in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4322:
4323: --Validation for SAE
4324: UPDATE mth_workday_shifts_stg stg
4325: SET stg.err_code = stg.err_code || 'SAE '

Line 4324: UPDATE mth_workday_shifts_stg stg

4320: AND stag.To_Date = ovp.To_Date);
4321: mth_util_pkg.log_msg('Number of rows with OVP in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4322:
4323: --Validation for SAE
4324: UPDATE mth_workday_shifts_stg stg
4325: SET stg.err_code = stg.err_code || 'SAE '
4326: WHERE EXISTS (SELECT *
4327: FROM
4328: (SELECT stg.shift_workday_pk

Line 4329: FROM mth_workday_shifts_stg stg,

4325: SET stg.err_code = stg.err_code || 'SAE '
4326: WHERE EXISTS (SELECT *
4327: FROM
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

Line 4342: mth_util_pkg.log_msg('Number of rows with SAE in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4338: AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE (+)
4339: AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = MS.SYSTEM_PK (+)
4340: AND plants.PLANT_PK_KEY=MSV.PLANT_FK_KEY )sae
4341: WHERE stg.shift_workday_pk = sae.shift_workday_pk);
4342: mth_util_pkg.log_msg('Number of rows with SAE in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4343:
4344: --Validation for profile value
4345: UPDATE mth_workday_shifts_stg stg
4346: SET stg.err_code = stg.err_code || 'PRF '

Line 4345: UPDATE mth_workday_shifts_stg stg

4341: WHERE stg.shift_workday_pk = sae.shift_workday_pk);
4342: mth_util_pkg.log_msg('Number of rows with SAE in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4343:
4344: --Validation for profile value
4345: UPDATE mth_workday_shifts_stg stg
4346: SET stg.err_code = stg.err_code || 'PRF '
4347: WHERE v_profile<>'External Source';
4348: mth_util_pkg.log_msg('Number of rows with PRF in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4349:

Line 4348: mth_util_pkg.log_msg('Number of rows with PRF in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4344: --Validation for profile value
4345: UPDATE mth_workday_shifts_stg stg
4346: SET stg.err_code = stg.err_code || 'PRF '
4347: WHERE v_profile<>'External Source';
4348: mth_util_pkg.log_msg('Number of rows with PRF in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4349:
4350:
4351: --Insert records into mth_workday_shifts_err
4352:

Line 4397: FROM mth_workday_shifts_stg

4393: SHIFT_NAME,
4394: ERR_CODE,
4395: SHIFT_TYPE,
4396: GRAVEYARD_SHIFT
4397: FROM mth_workday_shifts_stg
4398: WHERE err_code IS NOT NULL
4399: );
4400: mth_util_pkg.log_msg('Number of rows inserted in MTH_WORKDAY_SHIFTS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4401:

Line 4408: mth_workday_shifts_stg stg,

4404: DELETE FROM mth_equipment_shifts_D
4405: WHERE shift_workday_fk_key IN (SELECT shift_workday_pk_key FROM
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)

Line 4431: mth_workday_shifts_stg stg,

4427: DELETE FROM mth_workday_shifts_D
4428: WHERE shift_workday_pk_key IN (SELECT shift_workday_pk_key FROM
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)

Line 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

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

Line 4586: MTH_WORKDAY_SHIFTS_STG STG where STG.processing_flag = v_processing_flag

4582: 'BOTH' ,
4583: NULL
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

Line 4603: mth_util_pkg.truncate_table_partition(' MTH_WORKDAY_SHIFTS_STG',v_processing_flag);

4599: );
4600:
4601: mth_util_pkg.log_msg('Number of catch all rows inserted in mth_workday_shifts_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4602:
4603: mth_util_pkg.truncate_table_partition(' MTH_WORKDAY_SHIFTS_STG',v_processing_flag);
4604:
4605: mth_util_pkg.log_msg('MTH_WORKDAY_SHIFTS_SF end', mth_util_pkg.G_DBG_PROC_FUN_END);
4606: mth_util_pkg.refresh_mv('MTH_SHIFT_GREGORIAN_DENORM_MV','f',null,null,null,null,null,null,null);
4607: COMMIT;

Line 4609: mth_util_pkg.log_msg('There are no records in MTH_WORKDAY_SHIFTS_STG for processing');

4605: mth_util_pkg.log_msg('MTH_WORKDAY_SHIFTS_SF end', mth_util_pkg.G_DBG_PROC_FUN_END);
4606: mth_util_pkg.refresh_mv('MTH_SHIFT_GREGORIAN_DENORM_MV','f',null,null,null,null,null,null,null);
4607: COMMIT;
4608: ELSE
4609: mth_util_pkg.log_msg('There are no records in MTH_WORKDAY_SHIFTS_STG for processing');
4610: END IF ;
4611:
4612:
4613: EXCEPTION