I'm just running the process manually now, not as part of a chore. The Prolog tab is correctly clearing the period, and the parameter used in the SQL is getting set to the correct value. If I run the SQL in an interactive client, I'm getting rows returned. I've added ASCIIOutput to every tab, and I'm only getting files for the Prolog and Epilog tabs-- nothing from Data nor Metadata.
Anyone see what I'm missing?
Thanks,
Matt
Data Source Tab / Query:
Code: Select all
SELECT SET_OF_BOOKS,PERIOD_NAME,DEPTCODE,SPENDINGACCOUNTCODE,PROJECT_CODE,AMOUNT,CURRENCY_CODE
FROM appsr.xxdsc_TM1_IS_v
WHERE (PERIOD_NAME = '?pFirstOpenPeriodSQL?' ) AND
(TRANSLATED_FLAG IS NULL OR TRANSLATED_FLAG <> 'R') AND
CURRENCY_CODE <> 'STAT';
Parameter: pFirstOpenPeriodSQL
Type: String
Default Value: ADJ-08
Prompt Question: (blank)
Advanced Tab / Prolog
Code: Select all
#####
# set some globals for diagnostics
#####
vOutputDir = CellGetS('sys_Global_Parameters', 'Export_File_Path', 'Value');
vOutputFile = vOutputDir | 'Oracle_GL Cube - Reload Recent P&L Data DIAGNOSTIC.csv';
#####
# calculate periods to clear and reload
#####
vFirstOpenPeriodYear = ATTRS( 'Scenario' , 'Current Forecast' , 'Forecast Start Period' );
vFirstOpenPeriod = SUBST ( vFirstOpenPeriodYear , 1 , 3 );
vFirstOpenYear = SUBST ( vFirstOpenPeriodYear , 5 , 4 );
pFirstOpenPeriodSQL = vFirstOpenPeriod | '-' | SUBST ( vFirstOpenYear , 3 , 2 );
ASCIIOUTPUT ( vOutputFile , pFirstOpenPeriodSQL );
#####
# clear first period
#####
IF ( SubsetExists ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' ) = 1 );
SubsetDestroy ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' );
ENDIF;
SubsetCreate ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' );
SubsetElementInsert ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' , vFirstOpenPeriod , 1 );
IF ( SubsetExists ( 'Year' , 'tmp_Clear for Oracle Refresh' ) = 1 );
SubsetDestroy ( 'Year' , 'tmp_Clear for Oracle Refresh' );
ENDIF;
SubsetCreate ( 'Year' , 'tmp_Clear for Oracle Refresh' );
SubsetElementInsert ( 'Year' , 'tmp_Clear for Oracle Refresh' , vFirstOpenYear , 1 );
IF ( ViewExists ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' ) = 1 );
ViewDestroy ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' );
ENDIF;
ViewCreate ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' );
ViewExtractSkipCalcsSet ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' , 1 );
ViewExtractSkipRuleValuesSet ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' , 1 );
ViewExtractSkipZeroesSet ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' , 1 );
ViewSubsetAssign( ' Oracle_GL', 'tmp_Clear for Oracle Refresh' , 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' );
ViewSubsetAssign( ' Oracle_GL', 'tmp_Clear for Oracle Refresh' , 'Year' , 'tmp_Clear for Oracle Refresh' );
ViewZeroOut( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' );
ViewDestroy ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' );
SubsetDestroy ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' );
SubsetDestroy ( 'Year' , 'tmp_Clear for Oracle Refresh' );
code to create new elements if encountered
Advanced Tab: Data
code to load data, worked fine in non-dynamic SQL process
Advanced Tab: Epilog
(blank)