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.MSD_SPF_COLLECT_HISTORY_DATA dependencies on MSD_SPF_ST_SPARE_USAGE

Line 385: x_sql := x_sql || ' (select distinct dm_org_code from MSD_SPF_ST_SPARE_USAGE)';

381: ' where teo.organization in ';
382:
383: --bug#10253936 - in case of usage flat file load include only those orgs from data collected in the filter
384: if p_ff_load = G_YES then
385: x_sql := x_sql || ' (select distinct dm_org_code from MSD_SPF_ST_SPARE_USAGE)';
386: else
387: x_sql := x_sql || ' (select distinct organization_code from MSD_DEM_APP_INSTANCE_ORGS)';
388: end if;
389:

Line 878: /* Pre-Process data in msd_spf_st_spare_usage */

874: msd_dem_common_utilities.log_message ('Truncate sales staging table...');
875: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_schema || '.T_SRC_SALES_TMPL';
876: END IF;
877:
878: /* Pre-Process data in msd_spf_st_spare_usage */
879: BEGIN
880: var_request_id := null;
881: var_request_id := fnd_request.submit_request ('MSD', 'MSDSPFUPP', NULL, NULL, FALSE, p_sr_instance_id);
882: EXCEPTION

Line 2495: var_table_name := var_msd_schema_name || '.MSD_SPF_ST_SPARE_USAGE';

2491: msd_dem_common_utilities.log_message ('');
2492: msd_dem_common_utilities.log_message ('');
2493: msd_dem_common_utilities.log_message('Truncating MSD Staging Tables...');
2494:
2495: var_table_name := var_msd_schema_name || '.MSD_SPF_ST_SPARE_USAGE';
2496: msd_dem_common_utilities.log_debug('Truncating table ' || var_table_name);
2497: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || var_table_name;
2498:
2499: var_table_name := var_msd_schema_name || '.MSD_SPF_ITEM_SUPERSESSION';

Line 3428: ' select max(usage_date) max_date from MSD_SPF_ST_SPARE_USAGE '||

3424: case when (p_install_base = G_YES and x_to_date is not null)
3425: then ' and sdate <= to_date(''' || to_char(x_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'')'
3426: else '' end ||
3427: ' union ' ||
3428: ' select max(usage_date) max_date from MSD_SPF_ST_SPARE_USAGE '||
3429: ' union ' ||
3430: ' select to_date(' || g_schema || '.get_max_date, param.pval) max_date from ' ||
3431: g_schema || '.db_params param where param.pname = ''nls_date_format'' )';
3432: msd_dem_common_utilities.log_debug(x_sql);

Line 4096: FROM msd_spf_st_spare_usage

4092:
4093: CURSOR c_get_bad_rows
4094: IS
4095: SELECT ebs_base_model_code, dm_item_code, dm_org_code, usage_date, error_text
4096: FROM msd_spf_st_spare_usage
4097: WHERE process_flag = -1
4098: AND rownum < 10000;
4099:
4100: BEGIN

Line 4132: SELECT count(1) INTO var_total_rows FROM msd_spf_st_spare_usage;

4128: END IF;
4129:
4130: /* Get Total Rows in the table */
4131: msd_dem_common_utilities.log_debug('Total Count Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4132: SELECT count(1) INTO var_total_rows FROM msd_spf_st_spare_usage;
4133: msd_dem_common_utilities.log_debug('Total Count Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4134: IF (var_total_rows = 0)
4135: THEN
4136: retcode := -1;

Line 4137: errbuf := 'No rows found in msd_spf_st_spare_usage';

4133: msd_dem_common_utilities.log_debug('Total Count Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4134: IF (var_total_rows = 0)
4135: THEN
4136: retcode := -1;
4137: errbuf := 'No rows found in msd_spf_st_spare_usage';
4138: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_usage_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4139: msd_dem_common_utilities.log_message (errbuf);
4140: RETURN;
4141: ELSE

Line 4142: msd_dem_common_utilities.log_message('Total rows in msd_spf_st_spare_usage - ' || to_char(var_total_rows));

4138: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_usage_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4139: msd_dem_common_utilities.log_message (errbuf);
4140: RETURN;
4141: ELSE
4142: msd_dem_common_utilities.log_message('Total rows in msd_spf_st_spare_usage - ' || to_char(var_total_rows));
4143: msd_dem_common_utilities.log_message ('');
4144: msd_dem_common_utilities.log_message ('');
4145: END IF;
4146:

Line 4153: UPDATE msd_spf_st_spare_usage su

4149: var_row_count := 0;
4150:
4151: msd_dem_common_utilities.log_message ('Update Source Organization Id...');
4152: msd_dem_common_utilities.log_debug('Update DM_ORG_SR_PK Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4153: UPDATE msd_spf_st_spare_usage su
4154: SET dm_org_sr_pk = NVL((SELECT sr_tp_id FROM msc_trading_partners mtp
4155: WHERE mtp.sr_instance_id = p_sr_instance_id
4156: AND mtp.partner_type = 3
4157: AND mtp.organization_code = su.dm_org_code), su.dm_org_sr_pk);

Line 4173: UPDATE msd_spf_st_spare_usage su

4169: var_row_count := 0;
4170:
4171: msd_dem_common_utilities.log_message ('Update Source Inventory Item Id for Service Parts...');
4172: msd_dem_common_utilities.log_debug('Update DM_ITEM_SR_PK Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4173: UPDATE msd_spf_st_spare_usage su
4174: SET dm_item_sr_pk = NVL((SELECT sr_inventory_item_id FROM msc_system_items msi
4175: WHERE msi.plan_id = -1
4176: AND msi.sr_instance_id = p_sr_instance_id
4177: AND msi.organization_id = su.dm_org_sr_pk

Line 4195: UPDATE msd_spf_st_spare_usage su

4191: var_row_count := 0;
4192:
4193: msd_dem_common_utilities.log_message ('Update Source Inventory Item Id for Finished Goods...');
4194: msd_dem_common_utilities.log_debug('Update EBS_BASE_MODEL_SR_PK Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4195: UPDATE msd_spf_st_spare_usage su
4196: SET ebs_base_model_sr_pk = NVL((SELECT sr_inventory_item_id FROM msc_system_items msi
4197: WHERE msi.plan_id = -1
4198: AND msi.sr_instance_id = p_sr_instance_id
4199: AND msi.organization_id = var_spf_master_org -- Bug#12675064

Line 4217: UPDATE msd_spf_st_spare_usage

4213: var_bad_rows := 0;
4214:
4215: msd_dem_common_utilities.log_message ('Checking for invalid rows ...');
4216: msd_dem_common_utilities.log_debug('Update Bad Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4217: UPDATE msd_spf_st_spare_usage
4218: SET process_flag = -1,
4219: error_text = 'Unable to find key(s) for - '
4220: || decode (ebs_base_model_sr_pk, -23453, ' EBS_BASE_MODEL_CODE = ' || ebs_base_model_code, '')
4221: || decode (dm_item_sr_pk, -23453, ' DM_ITEM_CODE = ' || dm_item_code, '')

Line 4238: UPDATE msd_spf_st_spare_usage su

4234: raise_application_error(-20001, 'Profile ''MSC: Category Set for Serviceable Items'' not set. Cannot proceed with validation of spares.');
4235: end if;
4236:
4237: msd_dem_common_utilities.log_debug('Update Bad Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4238: UPDATE msd_spf_st_spare_usage su
4239: SET process_flag = -1,
4240: error_text = 'Spare not assigned to Spare Cat Set ' || x_category_set_id || ', for - '
4241: || ' DM_ITEM_CODE = ' || dm_item_code || ''
4242: WHERE (process_flag is null or process_flag <> -1)

Line 4292: DELETE FROM msd_spf_st_spare_usage WHERE process_flag = -1;

4288: msd_dem_common_utilities.log_message ('Deleting Invalid Rows...');
4289: var_row_count := 0;
4290:
4291: msd_dem_common_utilities.log_debug('Delete Bad Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4292: DELETE FROM msd_spf_st_spare_usage WHERE process_flag = -1;
4293: var_row_count := SQL%ROWCOUNT;
4294: msd_dem_common_utilities.log_debug('Delete Bad Rows Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4295:
4296: msd_dem_common_utilities.log_message('Rows Deleted - ' || to_char(var_row_count));

Line 4340: UPDATE msd_spf_st_spare_usage

4336: msd_dem_common_utilities.log_message('---------------------------Aggregating Data to Base Time Level - Begin------------------------------');
4337: var_row_count := null;
4338:
4339: msd_dem_common_utilities.log_debug('Update Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4340: UPDATE msd_spf_st_spare_usage
4341: SET process_flag = 0;
4342: msd_dem_common_utilities.log_debug('Update Rows Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4343: COMMIT;
4344:

Line 4346: INSERT /+ APPEND / INTO MSD_SPF_ST_SPARE_USAGE NOLOGGING

4342: msd_dem_common_utilities.log_debug('Update Rows Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4343: COMMIT;
4344:
4345: msd_dem_common_utilities.log_debug('Aggregate and Insert Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4346: INSERT /*+ APPEND */ INTO MSD_SPF_ST_SPARE_USAGE NOLOGGING
4347: (USAGE_HIST_TYPE, EBS_BASE_MODEL_CODE, DM_ITEM_CODE, DM_ORG_CODE, USAGE_DATE, QUANTITY, -- Bug#12675064
4348: EBS_BASE_MODEL_SR_PK, DM_ITEM_SR_PK, DM_ORG_SR_PK,
4349: LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY, CREATED_BY)
4350: SELECT su.usage_hist_type, su.ebs_base_model_code, su.dm_item_code, su.dm_org_code, mdd.datet, sum(su.quantity), -- Bug#12675064

Line 4354: msd_spf_st_spare_usage su,

4350: SELECT su.usage_hist_type, su.ebs_base_model_code, su.dm_item_code, su.dm_org_code, mdd.datet, sum(su.quantity), -- Bug#12675064
4351: su.ebs_base_model_sr_pk, su.dm_item_sr_pk, su.dm_org_sr_pk,
4352: FND_GLOBAL.LOGIN_ID, systimestamp, systimestamp, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID
4353: FROM
4354: msd_spf_st_spare_usage su,
4355: msd_dem_dates mdd
4356: WHERE
4357: su.usage_date BETWEEN mdd.start_date AND mdd.end_date
4358: GROUP BY su.usage_hist_type, su.ebs_base_model_sr_pk, su.dm_item_sr_pk, su.dm_org_sr_pk, mdd.datet, -- Bug#12675064

Line 4369: DELETE FROM msd_spf_st_spare_usage

4365:
4366: msd_dem_common_utilities.log_debug('Daily Rows Delete Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4367: var_row_count := null;
4368:
4369: DELETE FROM msd_spf_st_spare_usage
4370: WHERE process_flag =0;
4371: var_row_count := SQL%ROWCOUNT;
4372: msd_dem_common_utilities.log_debug('Daily Rows Delete Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4373: msd_dem_common_utilities.log_message('Rows (at day level) Deleted - ' || to_char(var_row_count));