#__AW_Repository_Version '12.1.0.0000';
#__AW_Product_Version '12.1.1.1';
#__AW_ATL_Locale 'eng_gb.utf-8';
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RSavedAfterCheckOut' , "ActaValue_1" = 'YES' , "ActaName_2" = 'RDate_modified' , "ActaValue_2" = 'Mon May 03 11:46:26 2010' , "ActaName_3" = 'RDate_created' , "ActaValue_3" = 'Tue Nov 24 12:54:04 2009' )
CREATE DATABASE DATASTORE DS_ORA_IPW::'46b52a3f-27f3-47c3-8318-7828a343ccb4' SET ("DB2_version" = 'DB2 UDB 6.1',
"DBLiveLoad" = 'no',
"DB_connection_retry_count" = '3',
"DB_retry_interval" = '10000',
"Oracle_default_number_precision" = '24',
"Oracle_default_number_scale" = '11',
"abap_execution_mode" = 'generate_and_execute',
"abap_job_class" = 'C',
"abap_upload_application" = 'S',
"abap_upload_package" = '$TMP',
"abap_upload_status" = 'T',
"application_type" = 'Custom',
"attunity_workspace" = 'Navigator',
"case_sensitive" = 'no',
"cdc_enabled" = 'no',
"data_federator_version" = 'Data Federator 11.x',
"database_type" = 'Oracle',
"datastore_repotype" = 'local',
"db_date_format" = 'yyyy-mm-dd',
"db_datetime_format" = 'yyyy-mm-dd hh:mi:ss',
"db_decimal_separator" = '.',
"db_driver_supports_array_fetch" = 'odbc_automatic',
"db_driver_supports_param_sql" = 'odbc_automatic',
"db_time_format" = 'hh:mi:ss',
"ds_configuration_enabled" = 'yes',
"ds_configurations" = '<?xml version="1.0" encoding="WINDOWS-1252" ?>
<DSConfigurations>
<DSConfiguration default="true" name="Configuration1">
<Oracle_default_number_precision>24</Oracle_default_number_precision>
<Oracle_default_number_scale>11</Oracle_default_number_scale>
<case_sensitive>no</case_sensitive>
<database_type>Oracle</database_type>
<loader_xact_size>1000</loader_xact_size>
<locale_codepage>&lt;default&gt;</locale_codepage>
<locale_language>&lt;default&gt;</locale_language>
<locale_territory>&lt;default&gt;</locale_territory>
<oracle_host_string>XWSX0402_IPWDV01A</oracle_host_string>
<oracle_version>Oracle 10g</oracle_version>
<password>;39AC0102834307B3813FC019CC29420AB532DBDAF9CD6A1A70F43ED41468A7D142192E0413FB36EB75CB3D9521730015</password>
<server_codepage>&lt;default&gt;</server_codepage>
<user>etl_ads_user</user>
</DSConfiguration>
</DSConfigurations>
',
"enable_data_transfer" = 'yes',
"ftp_passwd" = ';645AEF89ABFB2753C4433574FB2A3F532BDB6E657E8F579C4DF7F91260CE49CB',
"import_unknown_type" = 'no',
"informix_version" = 'Informix IDS 7.3',
"js_db_on_same_machine" = 'no',
"loader_file_location" = 'shared_directory',
"loader_xact_size" = '1000',
"locale_codepage" = '<default>',
"locale_language" = '<default>',
"locale_territory" = '<default>',
"mssql_windows_authentication" = 'no',
"mysql_version" = 'MySQL 5.0',
"name" = 'DS_ORA_IPW',
"neoview_datasource" = 'Admin_Load_DataSource',
"neoview_version" = 'HP Neoview 2.3',
"netezza_version" = 'Netezza NPS 2.5',
"odbc_autocommit_support" = 'odbc_automatic',
"odbc_convert_func_support" = 'odbc_automatic',
"odbc_func_absolute" = 'odbc_automatic',
"odbc_func_avg" = 'odbc_automatic',
"odbc_func_ceiling" = 'odbc_automatic',
"odbc_func_count" = 'odbc_automatic',
"odbc_func_count_distinct" = 'odbc_automatic',
"odbc_func_dayofmonth" = 'odbc_automatic',
"odbc_func_dayofyear" = 'odbc_automatic',
"odbc_func_floor" = 'odbc_automatic',
"odbc_func_lcase" = 'odbc_automatic',
"odbc_func_length" = 'odbc_automatic',
"odbc_func_ln" = 'odbc_automatic',
"odbc_func_log" = 'odbc_automatic',
"odbc_func_ltrim_blanks" = 'odbc_automatic',
"odbc_func_max" = 'odbc_automatic',
"odbc_func_min" = 'odbc_automatic',
"odbc_func_mod" = 'odbc_automatic',
"odbc_func_month" = 'odbc_automatic',
"odbc_func_power" = 'odbc_automatic',
"odbc_func_quarter" = 'odbc_automatic',
"odbc_func_round" = 'odbc_automatic',
"odbc_func_rtrim_blanks" = 'odbc_automatic',
"odbc_func_soundex" = 'odbc_automatic',
"odbc_func_sqrt" = 'odbc_automatic',
"odbc_func_substring" = 'odbc_automatic',
"odbc_func_sum" = 'odbc_automatic',
"odbc_func_sysdate" = 'odbc_automatic',
"odbc_func_systime" = 'odbc_automatic',
"odbc_func_truncate" = 'odbc_automatic',
"odbc_func_ucase" = 'odbc_automatic',
"odbc_func_week" = 'odbc_automatic',
"odbc_func_year" = 'odbc_automatic',
"odbc_ifthenelse_func_support" = 'no',
"odbc_ms_access" = 'no',
"odbc_nvarchar_type_name" = '<unknown>',
"odbc_nvl_func_support" = 'odbc_automatic',
"odbc_outer_join_support" = 'odbc_automatic',
"oracle_host_string" = 'XWSX0402_IPWDV01A',
"oracle_version" = 'Oracle 10g',
"password" = ';39AC0102834307B3813FC019CC29420AB532DBDAF9CD6A1A70F43ED41468A7D142192E0413FB36EB75CB3D9521730015',
"rem_repo_passwd" = 'no',
"runtime_in_utf16" = '1',
"sap_language" = 'E - English',
"sap_system_number" = '00',
"secure_repo" = 'no',
"server_codepage" = '<default>',
"server_sorts_in_binary" = 'yes',
"sql_server_version" = 'Microsoft SQL Server 7.0',
"sybase_iq_version" = 'Sybase IQ 12.5',
"sybase_version" = 'Sybase ASE 11.x',
"teradata_version" = 'Teradata 2.5',
"transaction_isolation_level" = 'Database default',
"transfer_password" = ';645AEF89ABFB2753C4433574FB2A3F532BDB6E657E8F579C4DF7F91260CE49CB',
"unknown_type_size" = '255',
"use_named_pipe" = 'no',
"user" = 'etl_ads_user',
"xml_recursion_level" = '0');

 AlGUIComment (ActaName_1 = 'RSavedAfterCheckOut',ActaValue_1 = 'NO' , 
ActaName_2 = 'RLoader_Is_Template_Table',ActaValue_2 = 'no' , 
ActaName_3 = 'RDate_created',ActaValue_3 = 'Thu Apr 15 12:55:21 2010' , 
ActaName_4 = 'RDate_last_loaded',ActaValue_4 = 'Wed Apr 28 00:43:40 2010' , 
ActaName_5 = 'RDate_modified',ActaValue_5 = 'Mon May 03 09:29:28 2010' , 
ActaName_6 = 'REstimated_Row_Count',ActaValue_6 = '50000' , 
ActaName_7 = 'RTotal_Number_Of_Rows_Processed',ActaValue_7 = '0' , 
ActaName_8 = 'RNumber_Of_Rows_Rejected',ActaValue_8 = '0' , 
ActaName_9 = 'RNumber_Of_Inserts',ActaValue_9 = '0' , 
ActaName_10 = 'RNumber_Of_Updates',ActaValue_10 = '0' , 
ActaName_11 = 'RNumber_Of_Deletes',ActaValue_11 = '0' , 
ActaName_12 = 'RElapsed_Time_For_Load',ActaValue_12 = '0 Seconds' , 
ActaName_13 = 'RTable_Type',ActaValue_13 = 'TABLE' , 
ActaName_14 = 'RDescription',ActaValue_14 = '' )
CREATE TABLE DS_ORA_IPW.STG_OBJECTS.ERROR_LOG(FILE_ID DECIMAL(24, 11)  NOT NULL , 
TAB_ID VARCHAR(5)  NOT NULL , 
RUN_ID DECIMAL(24, 11)  NOT NULL , 
COLUMN_ID DECIMAL(24, 11)  NOT NULL , 
RECORD_NUMBER DECIMAL(24, 11)  NULL , 
ERROR_DESC VARCHAR(4000)  NULL , 
ERROR_INSERT_DATE DATETIME  NULL , 
ERROR_RECORD VARCHAR(4000)  NULL ) 
SET("db_alias_name" = 'STG_OBJECTS')
;

 AlGUIComment (ActaName_1 = 'RTotal_Number_Of_Rows_Processed',ActaValue_1 = '1' , 
ActaName_2 = 'RNumber_Of_Rows_Rejected',ActaValue_2 = '0' , 
ActaName_3 = 'RNumber_Of_Inserts',ActaValue_3 = '0' , 
ActaName_4 = 'RNumber_Of_Updates',ActaValue_4 = '1' , 
ActaName_5 = 'RNumber_Of_Deletes',ActaValue_5 = '0' , 
ActaName_6 = 'RElapsed_Time_For_Load',ActaValue_6 = '0 Seconds' , 
ActaName_7 = 'RLoader_Is_Template_Table',ActaValue_7 = 'no' , 
ActaName_8 = 'RTable_Type',ActaValue_8 = 'TABLE' , 
ActaName_9 = 'RDescription',ActaValue_9 = '' , 
ActaName_10 = 'RDate_last_loaded',ActaValue_10 = 'Wed Apr 28 00:43:40 2010' , 
ActaName_11 = 'RDate_modified',ActaValue_11 = 'Mon May 03 19:26:09 2010' , 
ActaName_12 = 'REstimated_Row_Count',ActaValue_12 = '50000' , 
ActaName_13 = 'RSavedAfterCheckOut',ActaValue_13 = 'NO' , 
ActaName_14 = 'RDate_created',ActaValue_14 = 'Thu Apr 15 09:59:53 2010' )
CREATE TABLE DS_ORA_IPW.STG_OBJECTS.ETL_METRICS(RUN_ID INT  KEY  NOT NULL , 
TARGET_TAB_NAME VARCHAR(30)  KEY  NOT NULL , 
JOB_START_TIME DATETIME  NULL , 
JOB_END_TIME DATETIME  NULL , 
INSERT_RECORD_COUNT DECIMAL(24, 11)  NULL , 
UPDATE_RECORD_COUNT DECIMAL(24, 11)  NULL , 
DELETE_RECORD_COUNT DECIMAL(24, 11)  NULL , 
REJECT_RECORD_COUNT DECIMAL(24, 11)  NULL , 
ETL_JOB_STATUS VARCHAR(30)  NULL , 
ETL_STATUS_MESSAGE VARCHAR(250)  NULL ) 
PRIMARY KEY (RUN_ID, TARGET_TAB_NAME) 
CREATE_INDEX ETL_METRICS_PK ON (RUN_ID, TARGET_TAB_NAME) PRIMARY 
SET("db_alias_name" = 'STG_OBJECTS')
;

 AlGUIComment (ActaName_1 = 'RTotal_Number_Of_Rows_Processed',ActaValue_1 = '3' , 
ActaName_2 = 'RNumber_Of_Rows_Rejected',ActaValue_2 = '0' , 
ActaName_3 = 'RNumber_Of_Inserts',ActaValue_3 = '0' , 
ActaName_4 = 'RNumber_Of_Updates',ActaValue_4 = '0' , 
ActaName_5 = 'RNumber_Of_Deletes',ActaValue_5 = '3' , 
ActaName_6 = 'RElapsed_Time_For_Load',ActaValue_6 = '0 Seconds' , 
ActaName_7 = 'RTable_Type',ActaValue_7 = 'TABLE' , 
ActaName_8 = 'RDescription',ActaValue_8 = '' , 
ActaName_9 = 'REstimated_Row_Count',ActaValue_9 = '50000' , 
ActaName_10 = 'RSavedAfterCheckOut',ActaValue_10 = 'NO' , 
ActaName_11 = 'RLoader_Is_Template_Table',ActaValue_11 = 'no' , 
ActaName_12 = 'RDate_created',ActaValue_12 = 'Wed Mar 03 07:14:57 2010' , 
ActaName_13 = 'RDate_last_loaded',ActaValue_13 = 'Mon May  3 13:24:39 2010' , 
ActaName_14 = 'RDate_modified',ActaValue_14 = 'Mon May 03 09:29:27 2010' )
CREATE TABLE DS_ORA_IPW.PST_OBJECTS.PS_AI_SP_HISTORY(SPO_IDENTIFIER DECIMAL(10, 0)  KEY  NOT NULL , 
START_DATE DATETIME  KEY  NOT NULL , 
CNF_REFERENCE_NUM INT  NOT NULL , 
CNF_SHIPPER_REF VARCHAR(30)  NULL , 
DM_AQ_TOTAL DECIMAL(12, 0)  NULL , 
DM_SOQ INT  NULL , 
END_DATE DATETIME  NULL , 
EUC_NUMBER INT  NULL , 
EXZ_IDENTIFIER VARCHAR(3)  NULL , 
LDZ_IDENTIFIER VARCHAR(4)  NULL , 
NDM_AQ_TOTAL DECIMAL(12, 0)  NULL , 
NDM_SOQ INT  NULL , 
NUM_DATALOGGERS INT  NULL , 
SPO_BOTTOM_STP_SOQ DECIMAL(24, 11)  NULL , 
SPT_CODE VARCHAR(4)  NULL , 
WHOLLY_DM_IND VARCHAR(1)  NULL , 
MRF_CODE VARCHAR(1)  NULL , 
NUM_INTRPTBL_DAYS INT  NULL , 
ORG_ID DECIMAL(10, 0)  NULL , 
DS_LOAD_DATE DATETIME  NULL ) 
PRIMARY KEY (SPO_IDENTIFIER, START_DATE) 
CREATE_INDEX AIH001 ON (SPO_IDENTIFIER, START_DATE) PRIMARY 
SET("db_alias_name" = 'PST_OBJECTS')
;

 AlGUIComment (ActaName_1 = 'RTotal_Number_Of_Rows_Processed',ActaValue_1 = '4' , 
ActaName_2 = 'RNumber_Of_Rows_Rejected',ActaValue_2 = '0' , 
ActaName_3 = 'RNumber_Of_Inserts',ActaValue_3 = '4' , 
ActaName_4 = 'RNumber_Of_Updates',ActaValue_4 = '0' , 
ActaName_5 = 'RNumber_Of_Deletes',ActaValue_5 = '0' , 
ActaName_6 = 'RElapsed_Time_For_Load',ActaValue_6 = '0 Seconds' , 
ActaName_7 = 'RTable_Type',ActaValue_7 = 'TABLE' , 
ActaName_8 = 'RDescription',ActaValue_8 = '' , 
ActaName_9 = 'REstimated_Row_Count',ActaValue_9 = '50000' , 
ActaName_10 = 'RSavedAfterCheckOut',ActaValue_10 = 'NO' , 
ActaName_11 = 'RLoader_Is_Template_Table',ActaValue_11 = 'no' , 
ActaName_12 = 'RDate_created',ActaValue_12 = 'Fri Mar 12 12:24:33 2010' , 
ActaName_13 = 'RDate_last_loaded',ActaValue_13 = 'Mon May  3 13:24:34 2010' , 
ActaName_14 = 'RDate_modified',ActaValue_14 = 'Mon May 03 09:29:26 2010' )
CREATE TABLE DS_ORA_IPW.STG_OBJECTS.SS_AI_SP_HISTORY(TRANS_ID DECIMAL(24, 11)  NULL , 
TRANS_TYPE VARCHAR(1)  NULL , 
TRANS_DATE DATETIME  NULL , 
SPO_IDENTIFIER DECIMAL(10, 0)  NOT NULL , 
START_DATE DATETIME  NOT NULL , 
CNF_REFERENCE_NUM INT  NOT NULL , 
CNF_EFFECTIVE_DATE DATETIME  NULL , 
CNF_END_DATE DATETIME  NULL , 
ORG_ID DECIMAL(10, 0)  NULL , 
DM_AQ_TOTAL DECIMAL(12, 0)  NULL , 
NDM_AQ_TOTAL DECIMAL(12, 0)  NULL , 
SPT_CODE VARCHAR(4)  NULL , 
EUC_NUMBER INT  NULL , 
EUC_EFFECTIVE_DATE DATETIME  NULL , 
EXZ_IDENTIFIER VARCHAR(3)  NULL , 
LDZ_IDENTIFIER VARCHAR(4)  NULL , 
CNF_SHIPPER_REF VARCHAR(30)  NULL , 
DM_SOQ INT  NULL , 
NDM_SOQ INT  NULL , 
MRF_CODE VARCHAR(1)  NULL , 
TRANSCO_METER_READ VARCHAR(1)  NULL , 
WHOLLY_DM_IND VARCHAR(1)  NULL , 
NUM_DATALOGGERS INT  NULL , 
END_DATE DATETIME  NULL , 
VOLUNTARY_INCREASE VARCHAR(1)  NULL , 
NUM_INTRPTBL_DAYS INT  NULL , 
COMP_EFFECTIVE_DATE VARCHAR(255)  NULL  SET("Native Type" = 'RAW'), 
CMPTTN_EFCTV_DATE DATETIME  NULL , 
SPO_BOTTOM_STP_SOQ DECIMAL(24, 11)  NULL , 
DS_LOAD_DATE DATETIME  NULL , 
DS_RUN_ID INT  NULL , 
DS_STATUS_FLAG VARCHAR(1)  NULL ) 
SET("db_alias_name" = 'STG_OBJECTS')
;
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RSavedAfterCheckOut' , "ActaValue_1" = 'NO' , "ActaName_2" = 'RDate_created' , "ActaValue_2" = 'Fri Nov 27 06:45:22 2009' , "ActaName_3" = 'RDate_modified' , "ActaValue_3" = 'Mon May 03 09:29:29 2010' )
CREATE FILE DATASTORE FF_ITR2_Error_File_part_1::'5974e659-3255-47b7-88e5-f3eeab0f32a5'(FileName VARCHAR(43) , RowNumber INT , ErrorDescription VARCHAR(2000) , ColumnID INT ) SET ("abap_file_format" = 'no',
"begining_of_file_string" = 'A00',
"blank_pad" = 'leading',
"cache" = 'yes',
"column_delimiter" = '/207',
"column_width" = '1',
"column_width1" = '43',
"column_width2" = '2',
"column_width3" = '2000',
"column_width4" = '1',
"date_format" = 'dd/mm/yyyyhh24:mi:ss',
"datetime_format" = 'yyyy.mm.dd hh24:mi:ss',
"end_of_file_string" = 'Z00',
"file_format" = 'ascii',
"file_location" = 'job_server',
"file_name" = '$P_Error_File1',
"file_type" = 'delimited_file',
"ignore_row_markers" = 'A00;Z99',
"locale_codepage" = '<default>',
"locale_language" = '<default>',
"locale_territory" = '<default>',
"name" = 'FF_ITR2_Error_File_part_1',
"reader_capture_data_conversion_errors" = 'no',
"reader_capture_row_format_errors" = 'no',
"reader_log_data_conversion_warnings" = 'no',
"reader_log_row_format_warnings" = 'no',
"reader_maximum_warnings_to_log" = '-99',
"reader_write_error_rows_to_file" = 'no',
"root_dir" = '$G_ERROR_LOG_DIR',
"row_delimiter" = '\\n',
"skip_row_header" = 'no',
"table_weight" = '0',
"time_format" = 'hh24:mi:ss',
"transfer_custom" = 'no',
"use_root_dir" = 'no',
"write_bom" = 'no',
"write_row_header" = 'no' );ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RSavedAfterCheckOut' , "ActaValue_1" = 'NO' , "ActaName_2" = 'RDate_created' , "ActaValue_2" = 'Fri Nov 27 06:45:21 2009' , "ActaName_3" = 'RDate_modified' , "ActaValue_3" = 'Mon May 03 09:29:28 2010' )
CREATE FILE DATASTORE FF_ITR2_Error_File_Part_2::'bec8a853-db09-4c72-b0a2-b2527119da27'(ErrorRecord VARCHAR(4000) ) SET ("abap_file_format" = 'no',
"begining_of_file_string" = 'A00',
"blank_pad" = 'leading',
"cache" = 'yes',
"column_delimiter" = '{none}',
"column_width" = '1',
"column_width1" = '4000',
"date_format" = 'dd/mm/yyyyhh24:mi:ss',
"datetime_format" = 'yyyy.mm.dd hh24:mi:ss',
"end_of_file_string" = 'Z99',
"file_format" = 'ascii',
"file_location" = 'job_server',
"file_name" = '$P_Error_File2',
"file_type" = 'delimited_file',
"ignore_row_markers" = 'A00;Z99',
"locale_codepage" = '<default>',
"locale_language" = '<default>',
"locale_territory" = '<default>',
"name" = 'FF_ITR2_Error_File_Part_2',
"reader_capture_data_conversion_errors" = 'no',
"reader_capture_row_format_errors" = 'yes',
"reader_log_data_conversion_warnings" = 'yes',
"reader_log_row_format_warnings" = 'yes',
"reader_maximum_warnings_to_log" = '-99',
"reader_write_error_rows_to_file" = 'no',
"root_dir" = '$G_ERROR_LOG_DIR',
"row_delimiter" = '\\n',
"skip_row_header" = 'no',
"table_weight" = '0',
"time_format" = 'hh24:mi:ss',
"transfer_custom" = 'no',
"use_root_dir" = 'no',
"write_bom" = 'no',
"write_row_header" = 'no' );ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RSavedAfterCheckOut' , "ActaValue_1" = 'NO' , "ActaName_2" = 'RDate_created' , "ActaValue_2" = 'Fri Mar 12 10:18:10 2010' , "ActaName_3" = 'RDate_modified' , "ActaValue_3" = 'Mon May 03 09:29:27 2010' )
CREATE FILE DATASTORE FF_SS_AI_SP_HISTORY::'eb77df57-8c79-449c-a509-f206d1504d49'(TRANS_ID DECIMAL(24, 11) , TRANS_TYPE VARCHAR(1) , TRANS_DATE DATETIME , SPO_IDENTIFIER DECIMAL(10, 0) , START_DATE DATETIME , CNF_REFERENCE_NUM DECIMAL(9, 0) , CNF_EFFECTIVE_DATE DATETIME , CNF_END_DATE DATETIME , ORG_ID DECIMAL(10, 0) , DM_AQ_TOTAL DECIMAL(12, 0) , NDM_AQ_TOTAL DECIMAL(12, 0) , SPT_CODE VARCHAR(4) , EUC_NUMBER DECIMAL(4, 0) , EUC_EFFECTIVE_DATE DATETIME , EXZ_IDENTIFIER VARCHAR(3) , LDZ_IDENTIFIER VARCHAR(4) , CNF_SHIPPER_REF VARCHAR(30) , DM_SOQ DECIMAL(8, 0) , NDM_SOQ DECIMAL(8, 0) , MRF_CODE VARCHAR(1) , TRANSCO_METER_READ VARCHAR(1) , WHOLLY_DM_IND VARCHAR(1) , NUM_DATALOGGERS DECIMAL(3, 0) , END_DATE DATETIME , VOLUNTARY_INCREASE VARCHAR(1) , NUM_INTRPTBL_DAYS INT , COMP_EFFECTIVE_DATE VARCHAR(255) , CMPTTN_EFCTV_DATE DATETIME , SPO_BOTTOM_STP_SOQ DECIMAL(24, 11) ) SET ("abap_file_format" = 'no',
"begining_of_file_string" = 'A00',
"blank_pad" = 'leading',
"cache" = 'yes',
"column_delimiter" = '/207',
"column_width" = '1',
"column_width1" = '48',
"column_width10" = '3',
"column_width11" = '3',
"column_width12" = '4',
"column_width13" = '4',
"column_width14" = '2',
"column_width15" = '3',
"column_width16" = '4',
"column_width17" = '30',
"column_width18" = '1',
"column_width19" = '1',
"column_width2" = '1',
"column_width20" = '1',
"column_width21" = '1',
"column_width22" = '1',
"column_width23" = '1',
"column_width24" = '1',
"column_width25" = '1',
"column_width26" = '1',
"column_width27" = '255',
"column_width28" = '1',
"column_width29" = '1',
"column_width3" = '24',
"column_width4" = '10',
"column_width5" = '2',
"column_width6" = '1',
"column_width7" = '24',
"column_width8" = '8',
"column_width9" = '8',
"date_format" = 'dd/mm/yyyy',
"datetime_format" = 'dd/mm/yyyyhh24:mi:ss',
"end_of_file_string" = 'Z99',
"file_format" = 'ascii',
"file_location" = 'job_server',
"file_name" = '$P_Input_File_Name',
"file_type" = 'delimited_file',
"ignore_row_markers" = 'A00;Z99',
"locale_codepage" = '<default>',
"locale_language" = '<default>',
"locale_territory" = '<default>',
"name" = 'FF_SS_AI_SP_HISTORY',
"reader_capture_data_conversion_errors" = 'yes',
"reader_capture_row_format_errors" = 'yes',
"reader_error_file_name" = '$G_Error_Filename',
"reader_error_file_root_dir" = '$G_Error_Log_Dir',
"reader_log_data_conversion_warnings" = 'yes',
"reader_log_row_format_warnings" = 'yes',
"reader_maximum_warnings_to_log" = '-99',
"reader_write_error_rows_to_file" = 'yes',
"root_dir" = '$G_Inbox_Dir',
"row_delimiter" = '\\n',
"skip_row_header" = 'no',
"table_weight" = '0',
"time_format" = 'hh24:mi:ss',
"transfer_custom" = 'no',
"use_root_dir" = 'no',
"write_bom" = 'no',
"write_row_header" = 'no' );ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = 'print(\'HPOV alerter..!\');
print(\'Alert message : \'||$P_Message);
print(\'Job Name : \'||$P_Job_Name);
print(\'Priority : \'||$P_Priority);

#	Define the application for HPOV log with respect to the environment the app is running
$L_Conn = word(replace_substr(datastore_field_value(\'DS_ORA_IPW\',\'oracle_host_string\'),\'_\',\' \'), 2);

if($L_Conn = \'IPWDV01A\')
	$L_Env = \'dev51etl\';
else if($L_Conn = \'IPWDV02A\')
	$L_Env = \'dev52etl\';
else if($L_Conn = \'IPWFX01A\')
	$L_Env = \'fofetl\';
else if($L_Conn = \'IPWPR01A\')
	$L_Env = \'prodetl\';

if(nvl($G_HPOV_Flag, \'N\') = \'Y\')
begin
print(exec(\'ksh\', \'-c "/app/global/bin/hpoview_alert.ksh {$P_Message} \\\'etl_bods_[$L_Env]\\\' {$P_Job_Name} {$P_Priority}\', 8));
end

return 0;', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Alert_HPOV  ($P_Message varchar(1000) IN, $P_Job_Name varchar(100) IN, $P_Priority varchar(1) IN ) RETURNS int
DECLARE
     $L_Env varchar(50);
     $L_Conn varchar(50);

BEGIN
print('HPOV alerter..!');
print('Alert message : '||$P_Message);
print('Job Name : '||$P_Job_Name);
print('Priority : '||$P_Priority);

#	Define the application for HPOV log with respect to the environment the app is running
$L_Conn = word(replace_substr(datastore_field_value('DS_ORA_IPW','oracle_host_string'),'_',' '), 2);

if($L_Conn = 'IPWDV01A')
	$L_Env = 'dev51etl';
else if($L_Conn = 'IPWDV02A')
	$L_Env = 'dev52etl';
else if($L_Conn = 'IPWFX01A')
	$L_Env = 'fofetl';
else if($L_Conn = 'IPWPR01A')
	$L_Env = 'prodetl';

if(nvl($G_HPOV_Flag, 'N') = 'Y')
begin
print(exec('ksh', '-c "/app/global/bin/hpoview_alert.ksh {$P_Message} \'etl_bods_[$L_Env]\' {$P_Job_Name} {$P_Priority}', 8));
end

return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing


$L_Pst_Pk_List = null;
FN_ITR2_Stg_Get_Pst_Pk($P_Pst_Table_Name,$L_Pst_Pk_List);

SQL(\'DS_ORA_IPW\',\'UPDATE [$P_Stg_Table_Name] SET DS_STATUS_FLAG=\\\'D\\\' WHERE (DS_RUN_ID,TRANS_ID) IN (SELECT DS_RUN_ID,TRANS_ID FROM [$P_Stg_Table_Name] \'||
					\'WHERE ds_run_id = [$P_Run_Id] AND TRANS_TYPE=\\\'I\\\' AND DS_STATUS_FLAG=\\\'W\\\' AND ([$L_Pst_Pk_List]) IN \'||
					\'(SELECT [$L_Pst_Pk_List] FROM [$P_Pst_Table_Name]))\');

SQL(\'DS_ORA_IPW\',\'UPDATE [$P_Stg_Table_Name] \'||
\'SET ds_status_flag = \\\'W\\\' \'||
\'WHERE(ds_run_id,   trans_id) IN \'||
  \'(SELECT ds_run_id, \'||
    \' trans_id \'||
   \'FROM [$P_Stg_Table_Name] \'||
  \' WHERE trans_id IN \'||
   \' (SELECT a.trans_id \'||
   \'  FROM [$P_Stg_Table_Name] a \'||
   \'  WHERE ([$L_Pst_Pk_List]) IN \'||
    \'  (SELECT [$L_Pst_Pk_List] \'||
    \'   FROM [$P_Stg_Table_Name] b \'||
    \'   WHERE a.trans_id > b.trans_id \'||
     \'  AND a.trans_type = \\\'I\\\' \'||
     \'  AND b.trans_type = \\\'D\\\' \'||
\'and a.ds_run_id=[$P_Run_Id] \'||
\'and b.ds_run_id=[$P_Run_Id] \'||
     \'  AND a.ds_status_flag= \\\'D\\\' \'||
	      \'  AND b.ds_status_flag= \\\'W\\\') \'||
   \' ) \'||
  \')\');

#PRINT(\'##########	EXISTING RECORDS HAVE COME AS INSERTS	##########\');


SQL(\'DS_ORA_IPW\',\'UPDATE [$P_Stg_Table_Name] SET DS_STATUS_FLAG=\\\'N\\\' WHERE (DS_RUN_ID,TRANS_ID) IN (SELECT DS_RUN_ID,TRANS_ID FROM [$P_Stg_Table_Name] \'||
					\'WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\\\'W\\\' AND ([$L_Pst_Pk_List]) IN \'||
					\'(SELECT [$L_Pst_Pk_List] FROM (select [$L_Pst_Pk_List] FROM [$P_Stg_Table_Name] \'||
					\'WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\\\'W\\\' AND trans_type in (\\\'U\\\',\\\'D\\\') MINUS \'||
					\'select [$L_Pst_Pk_List] FROM [$P_Stg_Table_Name] WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\\\'W\\\' \'||
					\' AND trans_type =\\\'I\\\') WHERE ([$L_Pst_Pk_List]) NOT IN \'||
					\'(SELECT [$L_Pst_Pk_List] FROM [$P_Pst_Table_Name] )))\');

#PRINT(\'##########	MISSING UPDATES AND DELETES RECORD TRAPPING COMPLETED	##########\');

SQL(\'DS_ORA_IPW\',\'UPDATE [$P_Stg_Table_Name] SET DS_STATUS_FLAG=\\\'T\\\' WHERE (DS_RUN_ID,TRANS_ID) IN (SELECT DS_RUN_ID,TRANS_ID FROM [$P_Stg_Table_Name] \'||
					\'WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\\\'W\\\' AND ([$L_Pst_Pk_List]) IN \'||
					\'(SELECT [$L_Pst_Pk_List] FROM (select count(*),[$L_Pst_Pk_List] from \'||
					\'[$P_Pst_Table_Name] where ([$L_Pst_Pk_List]) IN \'||
					\'(select [$L_Pst_Pk_List] FROM [$P_Stg_Table_Name] \'||
					\'WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\\\'W\\\' AND trans_type in (\\\'U\\\',\\\'D\\\') MINUS \'||
					\'select [$L_Pst_Pk_List] FROM [$P_Stg_Table_Name] WHERE ds_run_id = [$P_Run_Id] \'||
					\' AND DS_STATUS_FLAG=\\\'W\\\' AND trans_type =\\\'I\\\') GROUP BY [$L_Pst_Pk_List] HAVING COUNT(*) > 1)))\');

#PRINT(\'##########	MULTIPLE RECORD UPDATES TRAPPING COMPLETED	##########\');

return 0;', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Capture_To_Suspense  ($P_Pst_Table_Name varchar(50) IN, $P_Stg_Table_Name varchar(50) IN, $P_Run_Id int IN ) RETURNS int
DECLARE
     $L_Pst_Pk_List varchar(2000);

BEGIN
#last updated on 24-dec-2009 for integration testing


$L_Pst_Pk_List = null;
FN_ITR2_Stg_Get_Pst_Pk($P_Pst_Table_Name,$L_Pst_Pk_List);

SQL('DS_ORA_IPW','UPDATE [$P_Stg_Table_Name] SET DS_STATUS_FLAG=\'D\' WHERE (DS_RUN_ID,TRANS_ID) IN (SELECT DS_RUN_ID,TRANS_ID FROM [$P_Stg_Table_Name] '||
					'WHERE ds_run_id = [$P_Run_Id] AND TRANS_TYPE=\'I\' AND DS_STATUS_FLAG=\'W\' AND ([$L_Pst_Pk_List]) IN '||
					'(SELECT [$L_Pst_Pk_List] FROM [$P_Pst_Table_Name]))');

SQL('DS_ORA_IPW','UPDATE [$P_Stg_Table_Name] '||
'SET ds_status_flag = \'W\' '||
'WHERE(ds_run_id,   trans_id) IN '||
  '(SELECT ds_run_id, '||
    ' trans_id '||
   'FROM [$P_Stg_Table_Name] '||
  ' WHERE trans_id IN '||
   ' (SELECT a.trans_id '||
   '  FROM [$P_Stg_Table_Name] a '||
   '  WHERE ([$L_Pst_Pk_List]) IN '||
    '  (SELECT [$L_Pst_Pk_List] '||
    '   FROM [$P_Stg_Table_Name] b '||
    '   WHERE a.trans_id > b.trans_id '||
     '  AND a.trans_type = \'I\' '||
     '  AND b.trans_type = \'D\' '||
'and a.ds_run_id=[$P_Run_Id] '||
'and b.ds_run_id=[$P_Run_Id] '||
     '  AND a.ds_status_flag= \'D\' '||
	      '  AND b.ds_status_flag= \'W\') '||
   ' ) '||
  ')');

#PRINT('##########	EXISTING RECORDS HAVE COME AS INSERTS	##########');


SQL('DS_ORA_IPW','UPDATE [$P_Stg_Table_Name] SET DS_STATUS_FLAG=\'N\' WHERE (DS_RUN_ID,TRANS_ID) IN (SELECT DS_RUN_ID,TRANS_ID FROM [$P_Stg_Table_Name] '||
					'WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\'W\' AND ([$L_Pst_Pk_List]) IN '||
					'(SELECT [$L_Pst_Pk_List] FROM (select [$L_Pst_Pk_List] FROM [$P_Stg_Table_Name] '||
					'WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\'W\' AND trans_type in (\'U\',\'D\') MINUS '||
					'select [$L_Pst_Pk_List] FROM [$P_Stg_Table_Name] WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\'W\' '||
					' AND trans_type =\'I\') WHERE ([$L_Pst_Pk_List]) NOT IN '||
					'(SELECT [$L_Pst_Pk_List] FROM [$P_Pst_Table_Name] )))');

#PRINT('##########	MISSING UPDATES AND DELETES RECORD TRAPPING COMPLETED	##########');

SQL('DS_ORA_IPW','UPDATE [$P_Stg_Table_Name] SET DS_STATUS_FLAG=\'T\' WHERE (DS_RUN_ID,TRANS_ID) IN (SELECT DS_RUN_ID,TRANS_ID FROM [$P_Stg_Table_Name] '||
					'WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\'W\' AND ([$L_Pst_Pk_List]) IN '||
					'(SELECT [$L_Pst_Pk_List] FROM (select count(*),[$L_Pst_Pk_List] from '||
					'[$P_Pst_Table_Name] where ([$L_Pst_Pk_List]) IN '||
					'(select [$L_Pst_Pk_List] FROM [$P_Stg_Table_Name] '||
					'WHERE ds_run_id = [$P_Run_Id] AND DS_STATUS_FLAG=\'W\' AND trans_type in (\'U\',\'D\') MINUS '||
					'select [$L_Pst_Pk_List] FROM [$P_Stg_Table_Name] WHERE ds_run_id = [$P_Run_Id] '||
					' AND DS_STATUS_FLAG=\'W\' AND trans_type =\'I\') GROUP BY [$L_Pst_Pk_List] HAVING COUNT(*) > 1)))');

#PRINT('##########	MULTIPLE RECORD UPDATES TRAPPING COMPLETED	##########');

return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#-------------------------------------------------------------------------#
#																		  #
#            Job related updates on staging tables and job-control table  #
#			 on job completion											  #
#-------------------------------------------------------------------------#

#last updated on 24-dec-2009 for integration testing

FN_ITR2_Stg_Update_ETL_Count( );
$L_Total_Insert_Count = (sql(\'DS_ORA_IPW\', \'select INSERT_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name}\'));
$L_Total_Update_Count = (sql(\'DS_ORA_IPW\', \'select UPDATE_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name}\'));
$L_Total_Delete_Count = (sql(\'DS_ORA_IPW\', \'select DELETE_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name}\'));

#	Moving token file based on token_flag [which is set to 1 if error is 
#	encountered while processing file(s)] to in_archive or in_error directory
if($G_Token_Flag = 1)
begin	
	$L_Dest_Dir = $G_Error_Dir;
	$L_Status = \'Error\';
	$L_Status_Message = \'Error encountered while processing file(s)\';
		
	#Token file variable check for null
	if($G_Token_File is not null)
	begin	
		print(\'Moving Token File to in_error directory, Status (0 - Success): \'||exec(\'ksh\',\'-c "mv [$G_Inbox_Dir][$G_Token_File] [$L_Dest_Dir][$G_Token_File]"\', 8));
	end
end
else
begin
	$L_Dest_Dir = $G_Archive_Dir;
	$L_Status = \'Completed\';
	if($L_Total_Insert_Count = 0 AND $L_Total_Update_Count = 0 AND $L_Total_Delete_Count = 0)
	begin
		$L_Status_Message = \'Processed with zero records\';
	end
	else 
	begin
	IF(NVL($G_File_Type,\'CPT\') =\'CPT\')
		$L_Status_Message = \'All Changes captured to the staging table\';
	else
		$L_Status_Message = \'All records loaded to staging table\';
	end

	#	update details in ETL_Metrics table
	#	print(\'Updating details in ETL_Metrics table : job_end_time, etl_job_status, etl_status_message\');
	sql(\'DS_ORA_IPW\', \'update etl_metrics set etl_job_status = {$L_Status}, etl_status_message = {$L_Status_Message}, job_end_time = to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\') where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name} \');

$L_Total_Insert_Count = (sql(\'DS_ORA_IPW\', \'select INSERT_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name}\'));
$L_Total_Update_Count = (sql(\'DS_ORA_IPW\', \'select UPDATE_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name}\'));
$L_Total_Delete_Count = (sql(\'DS_ORA_IPW\', \'select DELETE_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name}\'));
$L_Total_Error_Count = (sql(\'DS_ORA_IPW\', \'select REJECT_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name}\'));

if($L_Total_Insert_Count = 0 AND $L_Total_Update_Count = 0 AND $L_Total_Delete_Count = 0)

		$L_Status_Message = \'Processed with zero records\';

	else 
	begin
	if($L_Total_Error_Count != 0)
	$L_Status_Message = \'Error records encountered \';
	else
	$L_Status_Message = \'All records loaded to PST table\';
	end

sql(\'DS_ORA_IPW\', \'update etl_metrics set etl_job_status = {$L_Status}, etl_status_message = {$L_Status_Message}, job_end_time = to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\') where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name} \');

	#Token file variable check for null
	if($G_Token_File is not null)
	begin	
	print(\'Moving Token File to in_archive directory, Status (0 - Success):  \'||exec(\'ksh\',\'-c "mv [$G_Inbox_Dir][$G_Token_File] [$L_Dest_Dir][$G_Token_File]"\', 8));
	end
end
	
	
	#	update details in DS_JOB_RUN table
	#	print(\'Updating details in DS_JOB_RUN table : job_status, end_date\');
	sql(\'DS_ORA_IPW\', \'update DS_JOB_RUN set job_status = {$L_Status}, end_date = to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\') where run_id = [$G_Run_Id]\');


return 0;', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_End_of_Job_Process  ( ) RETURNS int
DECLARE
     $L_Status varchar(20);
     $L_Status_Message varchar(100);
     $L_Dest_Dir varchar(100);
     $L_Total_Insert_Count int;
     $L_Total_Update_Count int;
     $L_Total_Delete_Count int;
     $L_Total_Error_Count int;

BEGIN
#-------------------------------------------------------------------------#
#																		  #
#            Job related updates on staging tables and job-control table  #
#			 on job completion											  #
#-------------------------------------------------------------------------#

#last updated on 24-dec-2009 for integration testing

FN_ITR2_Stg_Update_ETL_Count( );
$L_Total_Insert_Count = (sql('DS_ORA_IPW', 'select INSERT_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name}'));
$L_Total_Update_Count = (sql('DS_ORA_IPW', 'select UPDATE_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name}'));
$L_Total_Delete_Count = (sql('DS_ORA_IPW', 'select DELETE_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name}'));

#	Moving token file based on token_flag [which is set to 1 if error is 
#	encountered while processing file(s)] to in_archive or in_error directory
if($G_Token_Flag = 1)
begin	
	$L_Dest_Dir = $G_Error_Dir;
	$L_Status = 'Error';
	$L_Status_Message = 'Error encountered while processing file(s)';
		
	#Token file variable check for null
	if($G_Token_File is not null)
	begin	
		print('Moving Token File to in_error directory, Status (0 - Success): '||exec('ksh','-c "mv [$G_Inbox_Dir][$G_Token_File] [$L_Dest_Dir][$G_Token_File]"', 8));
	end
end
else
begin
	$L_Dest_Dir = $G_Archive_Dir;
	$L_Status = 'Completed';
	if($L_Total_Insert_Count = 0 AND $L_Total_Update_Count = 0 AND $L_Total_Delete_Count = 0)
	begin
		$L_Status_Message = 'Processed with zero records';
	end
	else 
	begin
	IF(NVL($G_File_Type,'CPT') ='CPT')
		$L_Status_Message = 'All Changes captured to the staging table';
	else
		$L_Status_Message = 'All records loaded to staging table';
	end

	#	update details in ETL_Metrics table
	#	print('Updating details in ETL_Metrics table : job_end_time, etl_job_status, etl_status_message');
	sql('DS_ORA_IPW', 'update etl_metrics set etl_job_status = {$L_Status}, etl_status_message = {$L_Status_Message}, job_end_time = to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\') where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name} ');

$L_Total_Insert_Count = (sql('DS_ORA_IPW', 'select INSERT_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name}'));
$L_Total_Update_Count = (sql('DS_ORA_IPW', 'select UPDATE_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name}'));
$L_Total_Delete_Count = (sql('DS_ORA_IPW', 'select DELETE_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name}'));
$L_Total_Error_Count = (sql('DS_ORA_IPW', 'select REJECT_RECORD_COUNT from etl_metrics where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name}'));

if($L_Total_Insert_Count = 0 AND $L_Total_Update_Count = 0 AND $L_Total_Delete_Count = 0)

		$L_Status_Message = 'Processed with zero records';

	else 
	begin
	if($L_Total_Error_Count != 0)
	$L_Status_Message = 'Error records encountered ';
	else
	$L_Status_Message = 'All records loaded to PST table';
	end

sql('DS_ORA_IPW', 'update etl_metrics set etl_job_status = {$L_Status}, etl_status_message = {$L_Status_Message}, job_end_time = to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\') where run_id = [$G_Run_Id] AND target_tab_name = {$G_Pst_Table_Name} ');

	#Token file variable check for null
	if($G_Token_File is not null)
	begin	
	print('Moving Token File to in_archive directory, Status (0 - Success):  '||exec('ksh','-c "mv [$G_Inbox_Dir][$G_Token_File] [$L_Dest_Dir][$G_Token_File]"', 8));
	end
end
	
	
	#	update details in DS_JOB_RUN table
	#	print('Updating details in DS_JOB_RUN table : job_status, end_date');
	sql('DS_ORA_IPW', 'update DS_JOB_RUN set job_status = {$L_Status}, end_date = to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\') where run_id = [$G_Run_Id]');


return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#	Read file from the inbox directory
#	print(\'Reading Files from inbox......\');
# 	$G_File_Count is null when the run is not an adhoc RUN. It picks up the count of files in the INBOX
#	for that particular tab_id. If it is mentioned, 
#	then the count shall be initialised to the $P_List_Size

#last updated on 24-dec-2009 for integration testing

IF(NVL($G_File_Type,\'CPT\')=\'OAQ\')

BEGIN

	IF ($G_Adhoc_File_Name IS NULL)
	BEGIN
	$P_File_List = SQL(\'DS_ORA_IPW\',\'select file_name from file_detail where (run_id,file_id) in (select run_id,max(file_id) from file_detail where run_id = (select max(run_id) from file_detail where run_id in (select run_id from DS_JOB_RUN WHERE JOB_ID = {$G_Job_Id} and job_status=\\\'Completed\\\')) and Adhoc_Flag=\\\'N\\\' AND FILE_STATUS_CODE IN (\\\'2\\\',\\\'4\\\') group by run_id)\');
	if($P_File_List is null)
	begin
	print(\'FIRST RUN OF THE JOB. NO ENTRY IN FILE_DETAIL FOUND. TAKING IT FROM THE UNIX DIRECTORY\');	
	$P_File_List = exec(\'ksh\',\'-c "ls [$G_Inbox_Dir][$G_Table_Id].PN* |head -1\',2);
	$P_File_List = substr($P_File_List,length($G_Inbox_Dir)+1,18);
	end
	print(\'Last Processed File Name :\'||$P_File_List);
	END
	ELSE
	$P_File_List = $G_Adhoc_File_Name;

	IF ($G_File_Count is null)
	$P_List_Size = exec(\'ksh\',\'-c "ls [$G_Inbox_Dir][$G_Table_Id].PN* | grep -v TOK | wc -l"\',2);
	else 
	$P_List_Size = $G_File_Count;
print(\'Count of files :\'||$P_List_Size);
END

ELSE
begin
$P_File_List = exec(\'ksh\',\'-c "ls [$G_Inbox_Dir][$G_Table_Id].PN* | grep -v TOK"\',2);
$P_File_List = word($P_File_List , 1);
$P_File_List = substr($P_File_List,length($G_Inbox_Dir)+1, 18);
$P_List_Size = 1;

end

$L_Actual_File_Count = exec(\'ksh\',\'-c "ls [$G_Inbox_Dir][$G_Table_Id].PN* | grep -v TOK | wc -l"\',2);
if (nvl($L_Actual_File_Count,0) = 0) 
begin

#	Exit out to scheduler with error code 20
	raise_exception_ext(\'20 - No file(s) found in inbox directory\', 20);
end
if (nvl($L_Actual_File_Count,0) >1 and NVL($G_File_Type,\'CPT\')!=\'OAQ\') 
begin
PRINT(\'ONLY ONE FILE PER LOAD IS EXPECTED. FOUND MORE FILES IN THE INBOX\');
print(\'Count of files :\'||$L_Actual_File_Count);	
end

return 0;', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Get_Data_Files  ($P_File_List varchar(1000) IN OUT, $P_List_Size int IN OUT ) RETURNS int
DECLARE
     $L_Actual_File_Count int;

BEGIN
#	Read file from the inbox directory
#	print('Reading Files from inbox......');
# 	$G_File_Count is null when the run is not an adhoc RUN. It picks up the count of files in the INBOX
#	for that particular tab_id. If it is mentioned, 
#	then the count shall be initialised to the $P_List_Size

#last updated on 24-dec-2009 for integration testing

IF(NVL($G_File_Type,'CPT')='OAQ')

BEGIN

	IF ($G_Adhoc_File_Name IS NULL)
	BEGIN
	$P_File_List = SQL('DS_ORA_IPW','select file_name from file_detail where (run_id,file_id) in (select run_id,max(file_id) from file_detail where run_id = (select max(run_id) from file_detail where run_id in (select run_id from DS_JOB_RUN WHERE JOB_ID = {$G_Job_Id} and job_status=\'Completed\')) and Adhoc_Flag=\'N\' AND FILE_STATUS_CODE IN (\'2\',\'4\') group by run_id)');
	if($P_File_List is null)
	begin
	print('FIRST RUN OF THE JOB. NO ENTRY IN FILE_DETAIL FOUND. TAKING IT FROM THE UNIX DIRECTORY');	
	$P_File_List = exec('ksh','-c "ls [$G_Inbox_Dir][$G_Table_Id].PN* |head -1',2);
	$P_File_List = substr($P_File_List,length($G_Inbox_Dir)+1,18);
	end
	print('Last Processed File Name :'||$P_File_List);
	END
	ELSE
	$P_File_List = $G_Adhoc_File_Name;

	IF ($G_File_Count is null)
	$P_List_Size = exec('ksh','-c "ls [$G_Inbox_Dir][$G_Table_Id].PN* | grep -v TOK | wc -l"',2);
	else 
	$P_List_Size = $G_File_Count;
print('Count of files :'||$P_List_Size);
END

ELSE
begin
$P_File_List = exec('ksh','-c "ls [$G_Inbox_Dir][$G_Table_Id].PN* | grep -v TOK"',2);
$P_File_List = word($P_File_List , 1);
$P_File_List = substr($P_File_List,length($G_Inbox_Dir)+1, 18);
$P_List_Size = 1;

end

$L_Actual_File_Count = exec('ksh','-c "ls [$G_Inbox_Dir][$G_Table_Id].PN* | grep -v TOK | wc -l"',2);
if (nvl($L_Actual_File_Count,0) = 0) 
begin

#	Exit out to scheduler with error code 20
	raise_exception_ext('20 - No file(s) found in inbox directory', 20);
end
if (nvl($L_Actual_File_Count,0) >1 and NVL($G_File_Type,'CPT')!='OAQ') 
begin
PRINT('ONLY ONE FILE PER LOAD IS EXPECTED. FOUND MORE FILES IN THE INBOX');
print('Count of files :'||$L_Actual_File_Count);	
end

return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing


$L_Error_Count = exec(\'ksh\',\'-c "cat [$G_Error_Log_Dir][$G_Error_Filename] | wc -l"\', 2) - 2;
if($L_Error_Count < 0 ) $L_Error_Count = 0;

return ($L_Error_Count);', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Get_Error_File_Count  ( ) RETURNS int
DECLARE
     $L_Error_Count int;

BEGIN
#last updated on 24-dec-2009 for integration testing


$L_Error_Count = exec('ksh','-c "cat [$G_Error_Log_Dir][$G_Error_Filename] | wc -l"', 2) - 2;
if($L_Error_Count < 0 ) $L_Error_Count = 0;

return ($L_Error_Count);
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing

#Update the unprocessed records to processed.
if(nvl($G_File_Type,\'CPT\') = \'OAQ\')
SQL(\'DS_ORA_IPW\',\'UPDATE [$G_Stg_Table_Name] set DS_status_flag=\\\'S\\\' where ds_run_id = [$G_Run_Id] and DS_Status_Flag = \\\'W\\\'\');

#	Compress data file
print(\'Compressing Data File......\');

$L_Return = exec(\'ksh\',\'-c "compress -f [$G_Inbox_Dir][$P_Get_File_Name]"\', 8);
$L_Exec_Status = substr($L_Return, 1, 7);

if($P_Record_Count < 0) $P_Record_Count = 0;

if($P_Error_Count < 0) $P_Error_Count = 0;

if($P_Error_Flag = 1)
begin
	if($P_Record_Count <= $P_Error_Count )
	begin
		$L_File_Status = \'5\'; #	Error
	end
	else 
	begin
		$L_File_Status = \'3\'; #	Processed with Error
	end
		$L_Dest_Dir = $G_Error_Dir;
end
else
begin
	if($P_Record_Count = 0 )
	begin
		$L_File_Status = \'4\'; #	Processed file and arrived with Zero Records
	end
	else 
	begin
		$L_File_Status = \'2\'; #	Loaded
	end
		$L_Dest_Dir = $G_Archive_Dir;

end


if(nvl($L_Exec_Status, 1) = 0)
begin
print(\'Compression Successful..!\');
$L_Return = exec(\'ksh\',\'-c "mv [$G_Inbox_Dir][$P_Get_File_Name].Z [$L_Dest_Dir][$P_Get_File_Name].Z"\',8);
$L_Exec_Status = substr($L_Return, 1, 7);
end
else 
begin
print(\'Compression Failed..!, Status : \'||$L_Return);
$L_Return = exec(\'ksh\',\'-c "mv [$G_Inbox_Dir][$P_Get_File_Name] [$L_Dest_Dir][$P_Get_File_Name]"\',8);
$L_Exec_Status = substr($L_Return, 1, 7);
end

#	Move data file 
print(\'Moving Data File......\');

if(nvl($L_Exec_Status, 1) = 0)
begin
print(\'File successfully moved..!\');
end
else 
begin
print(\'Unable to move file..!, Status : \'||$L_Return);
raise_exception_ext(\'21 : Unable to move data file\', 21);
end


#	Update details in File_Detail table : updates on file_status_code = 2, record_count
print(\'Updating details in File_Detail table : file_status_code, record_count\');
sql(\'DS_ORA_IPW\', \'update file_detail set file_status_code = [$L_File_Status], record_count = [$P_Record_Count] where run_id = [$G_Run_Id] AND file_id = [$P_File_Id]\');
# find the count of the files to be processed


# Count Incremented to check the next file
$P_File_Count = $P_File_Count + 1;
$P_File_Id = $P_File_Id + 1;


return 0;', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Get_Next_File  ($P_File_List varchar(1000) IN OUT, $P_List_Size int IN OUT, $P_File_Count int IN OUT, $P_Get_File_Name varchar(100) IN, $P_Record_Count int IN, $P_Error_Count int IN, $P_File_Id int IN OUT, $P_Error_Flag varchar(1) IN ) RETURNS int
DECLARE
     $L_Dest_Dir varchar(100);
     $L_File_Status varchar(1);
     $L_Return varchar(1000);
     $L_Exec_Status int;
     $L_File_Count int;

BEGIN
#last updated on 24-dec-2009 for integration testing

#Update the unprocessed records to processed.
if(nvl($G_File_Type,'CPT') = 'OAQ')
SQL('DS_ORA_IPW','UPDATE [$G_Stg_Table_Name] set DS_status_flag=\'S\' where ds_run_id = [$G_Run_Id] and DS_Status_Flag = \'W\'');

#	Compress data file
print('Compressing Data File......');

$L_Return = exec('ksh','-c "compress -f [$G_Inbox_Dir][$P_Get_File_Name]"', 8);
$L_Exec_Status = substr($L_Return, 1, 7);

if($P_Record_Count < 0) $P_Record_Count = 0;

if($P_Error_Count < 0) $P_Error_Count = 0;

if($P_Error_Flag = 1)
begin
	if($P_Record_Count <= $P_Error_Count )
	begin
		$L_File_Status = '5'; #	Error
	end
	else 
	begin
		$L_File_Status = '3'; #	Processed with Error
	end
		$L_Dest_Dir = $G_Error_Dir;
end
else
begin
	if($P_Record_Count = 0 )
	begin
		$L_File_Status = '4'; #	Processed file and arrived with Zero Records
	end
	else 
	begin
		$L_File_Status = '2'; #	Loaded
	end
		$L_Dest_Dir = $G_Archive_Dir;

end


if(nvl($L_Exec_Status, 1) = 0)
begin
print('Compression Successful..!');
$L_Return = exec('ksh','-c "mv [$G_Inbox_Dir][$P_Get_File_Name].Z [$L_Dest_Dir][$P_Get_File_Name].Z"',8);
$L_Exec_Status = substr($L_Return, 1, 7);
end
else 
begin
print('Compression Failed..!, Status : '||$L_Return);
$L_Return = exec('ksh','-c "mv [$G_Inbox_Dir][$P_Get_File_Name] [$L_Dest_Dir][$P_Get_File_Name]"',8);
$L_Exec_Status = substr($L_Return, 1, 7);
end

#	Move data file 
print('Moving Data File......');

if(nvl($L_Exec_Status, 1) = 0)
begin
print('File successfully moved..!');
end
else 
begin
print('Unable to move file..!, Status : '||$L_Return);
raise_exception_ext('21 : Unable to move data file', 21);
end


#	Update details in File_Detail table : updates on file_status_code = 2, record_count
print('Updating details in File_Detail table : file_status_code, record_count');
sql('DS_ORA_IPW', 'update file_detail set file_status_code = [$L_File_Status], record_count = [$P_Record_Count] where run_id = [$G_Run_Id] AND file_id = [$P_File_Id]');
# find the count of the files to be processed


# Count Incremented to check the next file
$P_File_Count = $P_File_Count + 1;
$P_File_Id = $P_File_Id + 1;


return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing

$L_Constraint_Name = SQL(\'DS_ORA_IPW\',\'SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS \'||
								\'WHERE table_name={$P_Pst_Table_Name} and CONSTRAINT_type=\\\'P\\\'\');

print($L_Constraint_Name);
$L_Pk_Count = SQL(\'DS_ORA_IPW\',\'select count(*) from all_cons_columns where \'||
								\'CONSTRAINT_NAME = {$L_Constraint_Name} and TABLE_NAME = {$P_Pst_Table_Name} \');

print($L_Pk_Count);
while ( $L_Pk_Count >= 0 )
	begin
		
		$P_Pst_Pk_List = SQL(\'DS_ORA_IPW\',\'select COLUMN_NAME from all_cons_columns where \'||
								\'CONSTRAINT_NAME = {$L_Constraint_Name} and TABLE_NAME = {$P_Pst_Table_Name}\'||
								\'and position = [$L_Pk_Count]\') ||\' \'|| $P_Pst_Pk_List;
		$L_Pk_Count = $L_Pk_Count - 1;
	end

$P_Pst_Pk_List = replace_substr($P_Pst_Pk_List ,\' \',\',\');

$P_Pst_Pk_List = substr($P_Pst_Pk_List , 2, length($P_Pst_Pk_List ) - 2);

print(\'PK\\\'s are : \'||$P_Pst_Pk_List );

return 0;', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Get_Pst_Pk  ($P_Pst_Table_Name varchar(50) IN, $P_Pst_Pk_List varchar(2000) IN OUT ) RETURNS int
DECLARE
     $L_Pk_Count int;
     $L_Constraint_Name varchar(100);
     $L_Pst_Pk_List varchar(1000);

BEGIN
#last updated on 24-dec-2009 for integration testing

$L_Constraint_Name = SQL('DS_ORA_IPW','SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS '||
								'WHERE table_name={$P_Pst_Table_Name} and CONSTRAINT_type=\'P\'');

print($L_Constraint_Name);
$L_Pk_Count = SQL('DS_ORA_IPW','select count(*) from all_cons_columns where '||
								'CONSTRAINT_NAME = {$L_Constraint_Name} and TABLE_NAME = {$P_Pst_Table_Name} ');

print($L_Pk_Count);
while ( $L_Pk_Count >= 0 )
	begin
		
		$P_Pst_Pk_List = SQL('DS_ORA_IPW','select COLUMN_NAME from all_cons_columns where '||
								'CONSTRAINT_NAME = {$L_Constraint_Name} and TABLE_NAME = {$P_Pst_Table_Name}'||
								'and position = [$L_Pk_Count]') ||' '|| $P_Pst_Pk_List;
		$L_Pk_Count = $L_Pk_Count - 1;
	end

$P_Pst_Pk_List = replace_substr($P_Pst_Pk_List ,' ',',');

$P_Pst_Pk_List = substr($P_Pst_Pk_List , 2, length($P_Pst_Pk_List ) - 2);

print('PK\'s are : '||$P_Pst_Pk_List );

return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing

#print(\'Reading Token File from inbox......\');
wait_for_file(\'[$P_Inbox_Dir][$P_Tab_Id]*.TOK\', 0, 0, 1000, $L_File, $L_Size, \' \');
$L_File = substr(word($L_File , $L_Size),length($P_Inbox_Dir)+1, 18);
return($L_File);', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Get_Token_File  ($P_Inbox_Dir varchar(100) IN, $P_Tab_Id varchar(10) IN ) RETURNS varchar(100)
DECLARE
     $L_Size int;
     $L_File varchar(100);

BEGIN
#last updated on 24-dec-2009 for integration testing

#print('Reading Token File from inbox......');
wait_for_file('[$P_Inbox_Dir][$P_Tab_Id]*.TOK', 0, 0, 1000, $L_File, $L_Size, ' ');
$L_File = substr(word($L_File , $L_Size),length($P_Inbox_Dir)+1, 18);
return($L_File);
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing


#	Get the oracle sysdate if the $G_Sysdate is not initialised
if($G_Sysdate is null)
$G_Sysdate = SQL(\'DS_ORA_IPW\',\'SELECT to_char(ipwdate,\\\'yyyy.mm.dd hh24:mi:ss\\\') from dual\');

#	Get the Job_Id
$G_Job_Id = (sql(\'DS_ORA_IPW\', \'select job_id from DS_JOB where job_name = {$G_JOB_NAME}\'));

if (nvl($G_Job_Id, \'none\') = \'none\') 
begin
$G_Job_Id = \'job_\'||sql(\'DS_ORA_IPW\',\'SELECT DS_JOB_ID_SEQ.NEXTVAL FROM DUAL\');
print(\'Job ID : \'||$G_Job_Id);
print(\'For the current job, inserting a record in DS_JOB table\');
sql(\'DS_ORA_IPW\', \'insert into DS_JOB (job_id, job_name) values (\\\'\'||$G_Job_Id||\'\\\', {$G_JOB_NAME})\');
end

#	Generate the new Run_ID
$G_Run_Id = sql(\'DS_ORA_IPW\',\'SELECT DS_RUN_ID_SEQ.NEXTVAL FROM DUAL\');
print(\'Run ID : \'||$G_Run_Id);

#	Add new record in DS_JOB_RUN table : job_status = Started
print(\'Inserting a record in DS_JOB_RUN table\');
sql(\'DS_ORA_IPW\', \'insert into DS_JOB_RUN (job_id, start_date, end_date, job_status, run_id) values ({$G_Job_Id}, to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\'), null, \\\'Started\\\', [$G_Run_Id])\');

#	Add new record in ETL_Metrics table : job_status = Started
print(\'Inserting record in ETL_Metrics table\');
sql(\'DS_ORA_IPW\', \'insert into etl_metrics (run_id, TARGET_TAB_NAME, job_start_time, job_end_time, insert_record_count, update_record_count, delete_record_count, reject_record_count, etl_job_status, etl_status_message) values ([$G_Run_Id], {$G_Stg_Table_Name}, to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\'), null, 0, 0, 0, 0, \\\'Started\\\', null)\');
if(NVL($G_File_Type,\'CPT\')=\'OAQ\')
sql(\'DS_ORA_IPW\', \'insert into etl_metrics (run_id, TARGET_TAB_NAME, job_start_time, job_end_time, insert_record_count, update_record_count, delete_record_count, reject_record_count, etl_job_status, etl_status_message) values ([$G_Run_Id], {$G_Pst_Table_Name}, to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\'), null, 0, 0, 0, 0, \\\'Started\\\', null)\');
#	Check Previous Job Run status
$L_Prev_Run_Status = sql(\'DS_ORA_IPW\', \'select job_status from DS_JOB_RUN where run_id = (select max(run_id) from DS_JOB_RUN where job_id = {$G_Job_Id} AND run_id != [$G_Run_Id])\');
Print(\'Previous Job run Status: \'||$L_Prev_Run_Status);

$L_Prev_Run_Status = upper($L_Prev_Run_Status);

if ($L_Prev_Run_Status is null) 
Print(\'First Run of the Job\');
else if(($L_Prev_Run_Status is not null) AND ($L_Prev_Run_Status = \'STARTED\' OR $L_Prev_Run_Status = \'INPROGRESS\' OR $L_Prev_Run_Status = \'FAILED\' OR $L_Prev_Run_Status = \'ERROR\'))
begin
	print(\'Updating details in DS_JOB_RUN table : end_date, job_status\');
	print(\'Updating details in ETL_Metrics table : job_end_time, ETL_JOB_STATUS\');
	FN_ITR2_Stg_Update_ETLMetrics(\'Execution error : 23 - Previous execution was not completed\');
	raise_exception_ext(\'23 - Previous execution was not completed\', 23);
end

#Update record in DS_JOB_RUN table : job_status = Inprogress
print(\'Updating details in DS_JOB_RUN table : job_status\');
sql(\'DS_ORA_IPW\', \'update DS_JOB_RUN set job_status = \\\'Inprogress\\\' where run_id = [$G_Run_Id] \');

###----Update record in ETL_Metrics table : job_status = Inprogress
print(\'Updating details in ETL_Metrics table : ETL_JOB_STATUS\');
sql(\'DS_ORA_IPW\', \'update etl_metrics set ETL_JOB_STATUS = \\\'Inprogress\\\' where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name} \');

Return 0;', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Initial_Checks  ( ) RETURNS int
DECLARE
     $L_Prev_Run_Status varchar(100);

BEGIN
#last updated on 24-dec-2009 for integration testing


#	Get the oracle sysdate if the $G_Sysdate is not initialised
if($G_Sysdate is null)
$G_Sysdate = SQL('DS_ORA_IPW','SELECT to_char(ipwdate,\'yyyy.mm.dd hh24:mi:ss\') from dual');

#	Get the Job_Id
$G_Job_Id = (sql('DS_ORA_IPW', 'select job_id from DS_JOB where job_name = {$G_JOB_NAME}'));

if (nvl($G_Job_Id, 'none') = 'none') 
begin
$G_Job_Id = 'job_'||sql('DS_ORA_IPW','SELECT DS_JOB_ID_SEQ.NEXTVAL FROM DUAL');
print('Job ID : '||$G_Job_Id);
print('For the current job, inserting a record in DS_JOB table');
sql('DS_ORA_IPW', 'insert into DS_JOB (job_id, job_name) values (\''||$G_Job_Id||'\', {$G_JOB_NAME})');
end

#	Generate the new Run_ID
$G_Run_Id = sql('DS_ORA_IPW','SELECT DS_RUN_ID_SEQ.NEXTVAL FROM DUAL');
print('Run ID : '||$G_Run_Id);

#	Add new record in DS_JOB_RUN table : job_status = Started
print('Inserting a record in DS_JOB_RUN table');
sql('DS_ORA_IPW', 'insert into DS_JOB_RUN (job_id, start_date, end_date, job_status, run_id) values ({$G_Job_Id}, to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\'), null, \'Started\', [$G_Run_Id])');

#	Add new record in ETL_Metrics table : job_status = Started
print('Inserting record in ETL_Metrics table');
sql('DS_ORA_IPW', 'insert into etl_metrics (run_id, TARGET_TAB_NAME, job_start_time, job_end_time, insert_record_count, update_record_count, delete_record_count, reject_record_count, etl_job_status, etl_status_message) values ([$G_Run_Id], {$G_Stg_Table_Name}, to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\'), null, 0, 0, 0, 0, \'Started\', null)');
if(NVL($G_File_Type,'CPT')='OAQ')
sql('DS_ORA_IPW', 'insert into etl_metrics (run_id, TARGET_TAB_NAME, job_start_time, job_end_time, insert_record_count, update_record_count, delete_record_count, reject_record_count, etl_job_status, etl_status_message) values ([$G_Run_Id], {$G_Pst_Table_Name}, to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\'), null, 0, 0, 0, 0, \'Started\', null)');
#	Check Previous Job Run status
$L_Prev_Run_Status = sql('DS_ORA_IPW', 'select job_status from DS_JOB_RUN where run_id = (select max(run_id) from DS_JOB_RUN where job_id = {$G_Job_Id} AND run_id != [$G_Run_Id])');
Print('Previous Job run Status: '||$L_Prev_Run_Status);

$L_Prev_Run_Status = upper($L_Prev_Run_Status);

if ($L_Prev_Run_Status is null) 
Print('First Run of the Job');
else if(($L_Prev_Run_Status is not null) AND ($L_Prev_Run_Status = 'STARTED' OR $L_Prev_Run_Status = 'INPROGRESS' OR $L_Prev_Run_Status = 'FAILED' OR $L_Prev_Run_Status = 'ERROR'))
begin
	print('Updating details in DS_JOB_RUN table : end_date, job_status');
	print('Updating details in ETL_Metrics table : job_end_time, ETL_JOB_STATUS');
	FN_ITR2_Stg_Update_ETLMetrics('Execution error : 23 - Previous execution was not completed');
	raise_exception_ext('23 - Previous execution was not completed', 23);
end

#Update record in DS_JOB_RUN table : job_status = Inprogress
print('Updating details in DS_JOB_RUN table : job_status');
sql('DS_ORA_IPW', 'update DS_JOB_RUN set job_status = \'Inprogress\' where run_id = [$G_Run_Id] ');

###----Update record in ETL_Metrics table : job_status = Inprogress
print('Updating details in ETL_Metrics table : ETL_JOB_STATUS');
sql('DS_ORA_IPW', 'update etl_metrics set ETL_JOB_STATUS = \'Inprogress\' where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name} ');

Return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#	Directory Path is set using the environment variables set in BODS ETL server profile file.
#	Environment variables are set in the following format 
#	$HOME_ETLDIRECTORY_<SID>	Eg : $HOME_ETLDIRECTORY_IPWPR01A
#
#	Dependencies : 1. Connection name should be of the format <host name>_<SID>
#				   2. If staging datastore name is changed from DS_ORA_IPW, it should be reflected 
#					  in the below code also
#				   3. The entry for environment variable should be made in .profile file
#					  of the user that runs the BODS engine
#					  Eg: For production, the user will be \'prodbods\'

#last updated on 24-dec-2009 for integration testing


	$G_Dir_Path 	= get_env(\'HOME_ETLDIRECTORY_\'||upper(word(replace_substr(datastore_field_value(\'DS_ORA_IPW\',\'oracle_host_string\'),\'_\',\' \'), 2)));

#	Intialization of File Directories
	$G_Inbox_Dir 	= $G_Dir_Path||$G_Source_Name||\'/inbox/\';
	$G_Archive_Dir 	= $G_Dir_Path||$G_Source_Name||\'/in_archive/\';
	$G_Error_Dir 	= $G_Dir_Path||$G_Source_Name||\'/in_error/\';
	$G_Error_Log_Dir= $G_Dir_Path||$G_Source_Name||\'/in_error_log/\';

#	Get job name
	$G_Job_Name = job_name();

#	Set token filename
	$G_Token_File = FN_ITR2_Stg_Get_Token_File($G_Inbox_Dir, $G_Table_Id);

#	Checking if token file is present in inobx directory or not
	if($G_Token_File is null)
	begin
		print(\'Token File is not present in inbox..!\');
	end
	else if($G_Token_File is not null)
	begin
		print(\'Token File : \'||$G_Token_File);
	end

return 0;
', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Initialisation  ( ) RETURNS int
BEGIN
#	Directory Path is set using the environment variables set in BODS ETL server profile file.
#	Environment variables are set in the following format 
#	$HOME_ETLDIRECTORY_<SID>	Eg : $HOME_ETLDIRECTORY_IPWPR01A
#
#	Dependencies : 1. Connection name should be of the format <host name>_<SID>
#				   2. If staging datastore name is changed from DS_ORA_IPW, it should be reflected 
#					  in the below code also
#				   3. The entry for environment variable should be made in .profile file
#					  of the user that runs the BODS engine
#					  Eg: For production, the user will be 'prodbods'

#last updated on 24-dec-2009 for integration testing


	$G_Dir_Path 	= get_env('HOME_ETLDIRECTORY_'||upper(word(replace_substr(datastore_field_value('DS_ORA_IPW','oracle_host_string'),'_',' '), 2)));

#	Intialization of File Directories
	$G_Inbox_Dir 	= $G_Dir_Path||$G_Source_Name||'/inbox/';
	$G_Archive_Dir 	= $G_Dir_Path||$G_Source_Name||'/in_archive/';
	$G_Error_Dir 	= $G_Dir_Path||$G_Source_Name||'/in_error/';
	$G_Error_Log_Dir= $G_Dir_Path||$G_Source_Name||'/in_error_log/';

#	Get job name
	$G_Job_Name = job_name();

#	Set token filename
	$G_Token_File = FN_ITR2_Stg_Get_Token_File($G_Inbox_Dir, $G_Table_Id);

#	Checking if token file is present in inobx directory or not
	if($G_Token_File is null)
	begin
		print('Token File is not present in inbox..!');
	end
	else if($G_Token_File is not null)
	begin
		print('Token File : '||$G_Token_File);
	end

return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing
print(\'Removing split error files (rm cmd)\');
print(\'Split file-1 : \'||exec(\'ksh\',\'-c "\\rm -f [$G_Error_Log_Dir][$P_Error_File1]"\'));
print(\'Split file-2 : \'||exec(\'ksh\',\'-c "\\rm -f [$G_Error_Log_Dir][$P_Error_File2]"\'));

return 0;', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Remove_Split_Error_Files  ($P_Error_File1 varchar(100) IN, $P_Error_File2 varchar(100) IN ) RETURNS int
BEGIN
#last updated on 24-dec-2009 for integration testing
print('Removing split error files (rm cmd)');
print('Split file-1 : '||exec('ksh','-c "\rm -f [$G_Error_Log_Dir][$P_Error_File1]"'));
print('Split file-2 : '||exec('ksh','-c "\rm -f [$G_Error_Log_Dir][$P_Error_File2]"'));

return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing
if (nvl($G_File_Type,\'CPT\') = \'OAQ\')
begin
#To identify if the entry is available in the file_detail table
$L_Prev_File_Name = SQL(\'DS_ORA_IPW\',\'select file_name from file_detail where (run_id,file_id) in (select run_id,max(file_id) from file_detail where run_id = (select max(run_id) from file_detail where run_id in (select run_id from DS_JOB_RUN WHERE JOB_ID = {$G_Job_Id} and job_status=\\\'Completed\\\') and Adhoc_Flag=\\\'N\\\' AND FILE_STATUS_CODE IN (\\\'2\\\',\\\'4\\\'))group by run_id)\');

# 	To get the file sequence from the last processed file.
$L_File_Sequence = substr($P_Get_File_Name,9,6);

# 	incrementing the file sequence if it is a BAU run
if(($G_Adhoc_File_Name is null and $L_Prev_File_Name is not null) or $P_File_Id >1)
$L_File_Sequence = $L_File_Sequence + 1; 

#	Getting the file extension
$L_File_Extension = substr($P_Get_File_Name,16,3);

#	Creating the new file name
$P_Get_File_Name = $G_Table_Id||\'.PN\'||lpad($L_File_Sequence,6,0)||\'.\'||$L_File_Extension;

end 

$G_Error_Filename = \'err_\'||to_char($G_Sysdate,\'DD-MM-YYYY\')||\'_\'||$P_Get_File_Name||\'.txt\';


#	Add details in File_Detail table 
#print(\'Adding a record for the file \'||$L_Get_File_Name||\' in File_Detail table\');
IF ($G_Adhoc_File_Name is null)
sql(\'DS_ORA_IPW\', \'insert into file_detail (file_id, run_id, file_name, record_count, file_arrival_date, file_status_code,Adhoc_Flag) values ({$P_File_Id}, [$G_Run_Id],{$P_Get_File_Name}, 0, to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\'), \\\'1\\\',\\\'N\\\')\');
else
sql(\'DS_ORA_IPW\', \'insert into file_detail (file_id, run_id, file_name, record_count, file_arrival_date, file_status_code,Adhoc_Flag) values ({$P_File_Id}, [$G_Run_Id],{$P_Get_File_Name}, 0, to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\'), \\\'1\\\',\\\'Y\\\')\');

print(\'Current file \'||$P_Get_File_Name||\' being processed with File ID : \'||$P_File_Id);

#	Find the record count from footer and add it up for total record count

if (nvl($G_File_Type,\'CPT\') = \'MAN\')
$P_Record_Count = (exec(\'ksh\',\'-c "cat [$G_Inbox_Dir][$P_Get_File_Name] | wc -l"\', 2));
else 
$P_Record_Count = (exec(\'ksh\',\'-c "cat [$G_Inbox_Dir][$P_Get_File_Name] | wc -l"\', 2)) - 2;

return 0;
', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Set_File_Name  ($P_Get_File_Name varchar(100) IN OUT, $P_File_Id int IN OUT, $P_Record_Count int IN OUT ) RETURNS int
DECLARE
     $L_File_Sequence int;
     $L_File_Extension varchar(4);
     $L_Prev_File_Name varchar(50);

BEGIN
#last updated on 24-dec-2009 for integration testing
if (nvl($G_File_Type,'CPT') = 'OAQ')
begin
#To identify if the entry is available in the file_detail table
$L_Prev_File_Name = SQL('DS_ORA_IPW','select file_name from file_detail where (run_id,file_id) in (select run_id,max(file_id) from file_detail where run_id = (select max(run_id) from file_detail where run_id in (select run_id from DS_JOB_RUN WHERE JOB_ID = {$G_Job_Id} and job_status=\'Completed\') and Adhoc_Flag=\'N\' AND FILE_STATUS_CODE IN (\'2\',\'4\'))group by run_id)');

# 	To get the file sequence from the last processed file.
$L_File_Sequence = substr($P_Get_File_Name,9,6);

# 	incrementing the file sequence if it is a BAU run
if(($G_Adhoc_File_Name is null and $L_Prev_File_Name is not null) or $P_File_Id >1)
$L_File_Sequence = $L_File_Sequence + 1; 

#	Getting the file extension
$L_File_Extension = substr($P_Get_File_Name,16,3);

#	Creating the new file name
$P_Get_File_Name = $G_Table_Id||'.PN'||lpad($L_File_Sequence,6,0)||'.'||$L_File_Extension;

end 

$G_Error_Filename = 'err_'||to_char($G_Sysdate,'DD-MM-YYYY')||'_'||$P_Get_File_Name||'.txt';


#	Add details in File_Detail table 
#print('Adding a record for the file '||$L_Get_File_Name||' in File_Detail table');
IF ($G_Adhoc_File_Name is null)
sql('DS_ORA_IPW', 'insert into file_detail (file_id, run_id, file_name, record_count, file_arrival_date, file_status_code,Adhoc_Flag) values ({$P_File_Id}, [$G_Run_Id],{$P_Get_File_Name}, 0, to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\'), \'1\',\'N\')');
else
sql('DS_ORA_IPW', 'insert into file_detail (file_id, run_id, file_name, record_count, file_arrival_date, file_status_code,Adhoc_Flag) values ({$P_File_Id}, [$G_Run_Id],{$P_Get_File_Name}, 0, to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\'), \'1\',\'Y\')');

print('Current file '||$P_Get_File_Name||' being processed with File ID : '||$P_File_Id);

#	Find the record count from footer and add it up for total record count

if (nvl($G_File_Type,'CPT') = 'MAN')
$P_Record_Count = (exec('ksh','-c "cat [$G_Inbox_Dir][$P_Get_File_Name] | wc -l"', 2));
else 
$P_Record_Count = (exec('ksh','-c "cat [$G_Inbox_Dir][$P_Get_File_Name] | wc -l"', 2)) - 2;

return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing
If(nvl($G_File_Type,\'CPT\') = \'MAN\')
SQL(\'DS_ORA_IPW\',\'UPDATE ETL_METRICS SET INSERT_RECORD_COUNT = (SELECT COUNT(*) FROM [$G_Stg_Table_Name] WHERE DS_RUN_ID = [$G_Run_Id]) where run_id = [$G_Run_Id] AND Target_Tab_Name = {$G_Stg_Table_Name}\');

If(nvl($G_File_Type,\'CPT\') = \'OAQ\')
BEGIN 

$L_Insert_Count = SQL(\'DS_ORA_IPW\',\'SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = \'||
						\'[$G_Run_Id] and DS_STATUS_FLAG=\\\'S\\\' AND TRANS_TYPE=\\\'I\\\'\');

$L_Update_Count = SQL(\'DS_ORA_IPW\',\'SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = \'||
						\'[$G_Run_Id] and DS_STATUS_FLAG=\\\'S\\\' AND TRANS_TYPE=\\\'U\\\'\');

$L_Delete_Count = SQL(\'DS_ORA_IPW\',\'SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = \'||
						\'[$G_Run_Id] and DS_STATUS_FLAG=\\\'S\\\' AND TRANS_TYPE=\\\'D\\\'\');

$L_Error_Count = SQL(\'DS_ORA_IPW\',\'SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = \'||
						\'[$G_Run_Id] and DS_STATUS_FLAG IN (\\\'D\\\',\\\'N\\\',\\\'T\\\')\');

if($L_Error_Count!=0)
print(\'#####	ERROR RECORDS DETECTED. PLEASE REFER THE ETL_METRICS TABLE FOR MORE DETAILS		####\');
# UPDATING ETL_METRICS 

SQL(\'DS_ORA_IPW\',\'UPDATE ETL_METRICS SET INSERT_RECORD_COUNT = (SELECT COUNT(*) FROM [$G_Stg_Table_Name] WHERE DS_RUN_ID = [$G_Run_Id]) where run_id = [$G_Run_Id] AND Target_Tab_Name = {$G_Stg_Table_Name}\');

SQL(\'DS_ORA_IPW\',\'UPDATE ETL_METRICS SET INSERT_RECORD_COUNT = [$L_Insert_Count],UPDATE_RECORD_COUNT = [$L_Update_Count],\'||
				\' DELETE_RECORD_COUNT = [$L_Delete_Count],REJECT_RECORD_COUNT = [$L_Error_Count] WHERE RUN_ID = [$G_Run_Id] \'||
				\' and Target_Tab_Name = {$G_Pst_Table_Name}\');
end

If(nvl($G_File_Type,\'CPT\') = \'CPT\')
begin
$L_Insert_Count = SQL(\'DS_ORA_IPW\',\'SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = \'||
						\'[$G_Run_Id] and TRANS_TYPE=\\\'I\\\'\');

$L_Update_Count = SQL(\'DS_ORA_IPW\',\'SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = \'||
						\'[$G_Run_Id] AND TRANS_TYPE=\\\'U\\\'\');

$L_Delete_Count = SQL(\'DS_ORA_IPW\',\'SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = \'||
						\'[$G_Run_Id] AND TRANS_TYPE=\\\'D\\\'\');

SQL(\'DS_ORA_IPW\',\'UPDATE ETL_METRICS SET INSERT_RECORD_COUNT = [$L_Insert_Count],UPDATE_RECORD_COUNT = [$L_Update_Count],\'||
				\' DELETE_RECORD_COUNT = [$L_Delete_Count] WHERE RUN_ID = [$G_Run_Id] \'||
				\' and Target_Tab_Name = {$G_Stg_Table_Name}\');

end
return 0;
', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Update_ETL_Count  ( ) RETURNS int
DECLARE
     $L_Insert_Count int;
     $L_Update_Count int;
     $L_Delete_Count int;
     $L_Error_Count int;

BEGIN
#last updated on 24-dec-2009 for integration testing
If(nvl($G_File_Type,'CPT') = 'MAN')
SQL('DS_ORA_IPW','UPDATE ETL_METRICS SET INSERT_RECORD_COUNT = (SELECT COUNT(*) FROM [$G_Stg_Table_Name] WHERE DS_RUN_ID = [$G_Run_Id]) where run_id = [$G_Run_Id] AND Target_Tab_Name = {$G_Stg_Table_Name}');

If(nvl($G_File_Type,'CPT') = 'OAQ')
BEGIN 

$L_Insert_Count = SQL('DS_ORA_IPW','SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = '||
						'[$G_Run_Id] and DS_STATUS_FLAG=\'S\' AND TRANS_TYPE=\'I\'');

$L_Update_Count = SQL('DS_ORA_IPW','SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = '||
						'[$G_Run_Id] and DS_STATUS_FLAG=\'S\' AND TRANS_TYPE=\'U\'');

$L_Delete_Count = SQL('DS_ORA_IPW','SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = '||
						'[$G_Run_Id] and DS_STATUS_FLAG=\'S\' AND TRANS_TYPE=\'D\'');

$L_Error_Count = SQL('DS_ORA_IPW','SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = '||
						'[$G_Run_Id] and DS_STATUS_FLAG IN (\'D\',\'N\',\'T\')');

if($L_Error_Count!=0)
print('#####	ERROR RECORDS DETECTED. PLEASE REFER THE ETL_METRICS TABLE FOR MORE DETAILS		####');
# UPDATING ETL_METRICS 

SQL('DS_ORA_IPW','UPDATE ETL_METRICS SET INSERT_RECORD_COUNT = (SELECT COUNT(*) FROM [$G_Stg_Table_Name] WHERE DS_RUN_ID = [$G_Run_Id]) where run_id = [$G_Run_Id] AND Target_Tab_Name = {$G_Stg_Table_Name}');

SQL('DS_ORA_IPW','UPDATE ETL_METRICS SET INSERT_RECORD_COUNT = [$L_Insert_Count],UPDATE_RECORD_COUNT = [$L_Update_Count],'||
				' DELETE_RECORD_COUNT = [$L_Delete_Count],REJECT_RECORD_COUNT = [$L_Error_Count] WHERE RUN_ID = [$G_Run_Id] '||
				' and Target_Tab_Name = {$G_Pst_Table_Name}');
end

If(nvl($G_File_Type,'CPT') = 'CPT')
begin
$L_Insert_Count = SQL('DS_ORA_IPW','SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = '||
						'[$G_Run_Id] and TRANS_TYPE=\'I\'');

$L_Update_Count = SQL('DS_ORA_IPW','SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = '||
						'[$G_Run_Id] AND TRANS_TYPE=\'U\'');

$L_Delete_Count = SQL('DS_ORA_IPW','SELECT COUNT(*) FROM [$G_Stg_Table_Name] where ds_run_id = '||
						'[$G_Run_Id] AND TRANS_TYPE=\'D\'');

SQL('DS_ORA_IPW','UPDATE ETL_METRICS SET INSERT_RECORD_COUNT = [$L_Insert_Count],UPDATE_RECORD_COUNT = [$L_Update_Count],'||
				' DELETE_RECORD_COUNT = [$L_Delete_Count] WHERE RUN_ID = [$G_Run_Id] '||
				' and Target_Tab_Name = {$G_Stg_Table_Name}');

end
return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RActa_user_func_category' , "ActaValue_1" = 'User_Script_Function' , ui_script_text = '#last updated on 24-dec-2009 for integration testing	
	# Staging table load failure
	sql(\'DS_ORA_IPW\', \'update etl_metrics set etl_job_status = \\\'Failed\\\', etl_status_message = {$P_Error_Message}, job_end_time = to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\') where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name}\');

	
	# PST table load failure
	sql(\'DS_ORA_IPW\', \'update etl_metrics set etl_job_status = \\\'Failed\\\', etl_status_message = {$P_Error_Message}, job_end_time = to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\') where run_id = [$G_Run_Id] AND target_tab_name = {$L_Pst_Tab_Name}\');
	
	
	FN_ITR2_Stg_Update_ETL_Count( );

	sql(\'DS_ORA_IPW\', \'update ds_job_run set job_status = \\\'Failed\\\', end_date = to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\') where run_id = [$G_Run_Id]\');

return 0;', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
 CREATE FUNCTION FN_ITR2_Stg_Update_ETLMetrics  ($P_Error_Message varchar(250) IN ) RETURNS int
DECLARE
     $L_Pst_Tab_Name varchar(100);

BEGIN
#last updated on 24-dec-2009 for integration testing	
	# Staging table load failure
	sql('DS_ORA_IPW', 'update etl_metrics set etl_job_status = \'Failed\', etl_status_message = {$P_Error_Message}, job_end_time = to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\') where run_id = [$G_Run_Id] AND target_tab_name = {$G_Stg_Table_Name}');

	
	# PST table load failure
	sql('DS_ORA_IPW', 'update etl_metrics set etl_job_status = \'Failed\', etl_status_message = {$P_Error_Message}, job_end_time = to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\') where run_id = [$G_Run_Id] AND target_tab_name = {$L_Pst_Tab_Name}');
	
	
	FN_ITR2_Stg_Update_ETL_Count( );

	sql('DS_ORA_IPW', 'update ds_job_run set job_status = \'Failed\', end_date = to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\') where run_id = [$G_Run_Id]');

return 0;
 

END
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RDescription' , "ActaValue_1" = 'BODS Standard job populating data from flat file to corresponding Staging or Persistant table.

Version 2.0 Apr 2010' , "ActaName_2" = 'RDate_modified' , "ActaValue_2" = 'Mon May 03 09:29:25 2010' , "ActaName_3" = 'RSavedAfterCheckOut' , "ActaValue_3" = 'NO' , "ActaName_4" = 'RDate_created' , "ActaValue_4" = 'Fri Mar 12 10:18:07 2010',ANNOTATION_0='ALGUICOMMENT( x = \'44\', y = \'-5\',ObjectDesc=\'File_Landing - to - Staging
--------------------------------------
WF_Fil_To_Stg_Initialise: Contains the script to initialise the variables and control logic implementation.
WF_123: This workflow reads the data files and processes them. Error log files processing is also the part of this workflow. It also loads the data into the PST table.


\',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-44</X><Y>5</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>1581</CX><CY>367</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
',ANNOTATION_1='ALGUICOMMENT( x = \'267\', y = \'-1125\',ObjectDesc=\'Global Variable initialisation and initial checks related to staging control tables are performed in this workflow.\',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-267</X><Y>1125</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>535</CX><CY>256</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
',ANNOTATION_2='ALGUICOMMENT( x = \'744\', y = \'-576\',ObjectDesc=\'Staging workflow : Contains data flow for staging data load, error file processing, updation of staging control tables.\',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-744</X><Y>576</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>450</CX><CY>272</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
',ANNOTATION_3='ALGUICOMMENT( x = \'1184\', y = \'-1116\',ObjectDesc=\'Record counts in ETL_Metrics table, moving of token file and setting job statuses is carried out by SC_End_Of_Job_Process script. \',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-1184</X><Y>1116</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>547</CX><CY>262</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
',ANNOTATION_4='ALGUICOMMENT( x = \'31\', y = \'-584\',ObjectDesc=\'To keep things simple for now we will simply catch all errors.\',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-31</X><Y>584</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>576</CX><CY>200</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
',ANNOTATION_5='ALGUICOMMENT( x = \'1488\', y = \'-2171\',ObjectDesc=\'This Try/Catch series will catch all errors, set an error status against the Job run and exit.\',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-1488</X><Y>2171</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>862</CX><CY>200</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
' )
CREATE SESSION JOB_123::'141e3ada-830e-41f6-ac5a-7239ebc55bea'( )
DECLARE
     GLOBAL $G_Run_Id int;
     GLOBAL $G_Job_Id varchar(50);
     GLOBAL $G_Job_Name varchar(100);
     GLOBAL $G_Dir_Path varchar(250);
     GLOBAL $G_Inbox_Dir varchar(300);
     GLOBAL $G_Archive_Dir varchar(300);
     GLOBAL $G_Error_Dir varchar(300);
     GLOBAL $G_Table_Id varchar(5);
     GLOBAL $G_Error_Filename varchar(100);
     GLOBAL $G_Token_File varchar(100);
     GLOBAL $G_Error_Log_Dir varchar(300);
     GLOBAL $G_Token_Flag int;
     GLOBAL $G_Source_Name varchar(20);
     GLOBAL $G_Stg_Table_Name varchar(50);
     GLOBAL $G_HPOV_Flag varchar(1);
     GLOBAL $G_Sysdate datetime;
     GLOBAL $G_Adhoc_File_Name varchar(50);
     GLOBAL $G_File_Count int;
     GLOBAL $G_File_Type varchar(4);
     GLOBAL $G_Pst_Table_Name varchar(50);
     GLOBAL $G_Delete_Flag varchar(1);
BEGIN
ALGUICOMMENT( x = '54', y = '-386' , ui_display_name = 'Try_All',ObjectDesc='To keep things simple for now we will simply catch all errors.',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>216</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-190</Y></LOCATION><SIZE><CX>200</CX><CY>351</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>' )
TRY
BEGIN
ALGUICOMMENT( x = '383', y = '-928',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>585</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CALL PLAN WF_ITR2_Fil_To_Stg_Initialise::'4bfb03fc-476d-4eea-86c8-e0f276e2b6a2'();
ALGUICOMMENT( x = '819', y = '-390',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>216</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CALL PLAN WF_123::'97574228-86b2-4ffc-b52d-6fb3ef6cdfcd'();
ALGUICOMMENT( x = '1348', y = '-868' , ui_display_name = 'SC_End_Of_Job_Process' , ui_script_text = '# Call FN_End_of_Job_Process() function to perform job control table updation
FN_ITR2_Stg_End_of_Job_Process();
',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>507</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
# Call FN_End_of_Job_Process() function to perform job control table updation
FN_ITR2_Stg_End_of_Job_Process();

 

END
END
ALGUICOMMENT( x = '1816', y = '-62' , ui_display_name = 'Catch_Database_Access_Errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>619</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-190</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CATCH (1002)
BEGIN
ALGUICOMMENT( x = '707', y = '-243' , ui_display_name = 'SC_Database_Access_Errors' , ui_script_text = 'print(\'Fatal Database Access Error has occured..!\');
print(\'Error number : \'||error_number());
print(\'Error message : \'||error_message());
print(\'Error context : \'||error_context());

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV(\'Database Access Error : \'||error_message( ), job_name(), \'3\');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics(\'Database Access Error\');

# Exit out to scheduler with error code 11
raise_exception_ext(\'11 : Database Access Error\', 11);',ObjectDesc='This script will catch any specific database access errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>128</X><Y>0</Y></LOCATION><SIZE><CX>450</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-221</Y></LOCATION><SIZE><CX>594</CX><CY>151</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Fatal Database Access Error has occured..!');
print('Error number : '||error_number());
print('Error message : '||error_message());
print('Error context : '||error_context());

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV('Database Access Error : '||error_message( ), job_name(), '3');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics('Database Access Error');

# Exit out to scheduler with error code 11
raise_exception_ext('11 : Database Access Error', 11);
 

END
END
ALGUICOMMENT( x = '1804', y = '-300' , ui_display_name = 'Catch_Database_Connection_Errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>691</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-187</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CATCH (1003)
BEGIN
ALGUICOMMENT( x = '903', y = '-256' , ui_display_name = 'SC_Database_Connection_Errors' , ui_script_text = 'print(\'Fatal Database Connection Error has occured..!\');
print(\'Error number : \'||error_number());
print(\'Error message : \'||error_message());
print(\'Error context : \'||error_context());

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV(\'Database Connection Error : \'||error_message( ), job_name(), \'3\');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics(\'Database Connection Error\');

# Exit out to scheduler with error code 12
raise_exception_ext(\'12 : Database Connection Error\', 12);',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>463</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-187</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Fatal Database Connection Error has occured..!');
print('Error number : '||error_number());
print('Error message : '||error_message());
print('Error context : '||error_context());

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV('Database Connection Error : '||error_message( ), job_name(), '3');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics('Database Connection Error');

# Exit out to scheduler with error code 12
raise_exception_ext('12 : Database Connection Error', 12);
 

END
END
ALGUICOMMENT( x = '1804', y = '-525' , ui_display_name = 'Catch_File_Access_Errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>513</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-190</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CATCH (1005)
BEGIN
ALGUICOMMENT( x = '881', y = '-250' , ui_display_name = 'SC_File_Access_Errors' , ui_script_text = 'print(\'Fatal File access error has occured..!\');
print(\'Error number : \'||error_number( ));
print(\'Error message : \'||error_message( ));
print(\'Error context : \'||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV(\'File access error : \'||error_message( ), job_name(), \'3\');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics(\'File access error\');

# Exit out to scheduler with error code 14
raise_exception_ext(\'14 : File access error\', 14);',ObjectDesc='This script will catch any specific file access errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>47</X><Y>0</Y></LOCATION><SIZE><CX>425</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-212</Y></LOCATION><SIZE><CX>356</CX><CY>207</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Fatal File access error has occured..!');
print('Error number : '||error_number( ));
print('Error message : '||error_message( ));
print('Error context : '||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV('File access error : '||error_message( ), job_name(), '3');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics('File access error');

# Exit out to scheduler with error code 14
raise_exception_ext('14 : File access error', 14);
 

END
END
ALGUICOMMENT( x = '1829', y = '-760' , ui_display_name = 'Catch_Flat_File_Processing_Errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>669</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CATCH (1004)
BEGIN
ALGUICOMMENT( x = '794', y = '-224' , ui_display_name = 'SC_Flat_File_Processing_Errors' , ui_script_text = 'print(\'Fatal error has occured during flat file processing..!\');
print(\'Error number : \'||error_number( ));
print(\'Error message : \'||error_message( ));
print(\'Error context : \'||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV(\'Flat file processing error : \'||error_message( ), job_name(), \'3\');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics(\'Flat file processing error\');

# Exit out to scheduler with error code 13
raise_exception_ext(\'13 : Flat file processing error\', 13);',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>403</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Fatal error has occured during flat file processing..!');
print('Error number : '||error_number( ));
print('Error message : '||error_message( ));
print('Error context : '||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV('Flat file processing error : '||error_message( ), job_name(), '3');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics('Flat file processing error');

# Exit out to scheduler with error code 13
raise_exception_ext('13 : Flat file processing error', 13);
 

END
END
ALGUICOMMENT( x = '1803', y = '-981' , ui_display_name = 'Catch_Excel_File_Processing_Errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>697</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-187</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CATCH (1012)
BEGIN
ALGUICOMMENT( x = '697', y = '-294' , ui_display_name = 'SC_Excel_File_Processing_Error' , ui_script_text = 'print(\'Fatal excel file processing error has occured..!\');
print(\'Error number : \'||error_number( ));
print(\'Error message : \'||error_message( ));
print(\'Error context : \'||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV(\'Excel file processing error : \'||error_message( ), job_name(), \'3\');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics(\'Excel file processing error\');

# Exit out to scheduler with error code 18
raise_exception_ext(\'18 : Excel file processing error\', 18);',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>413</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-187</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Fatal excel file processing error has occured..!');
print('Error number : '||error_number( ));
print('Error message : '||error_message( ));
print('Error context : '||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV('Excel file processing error : '||error_message( ), job_name(), '3');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics('Excel file processing error');

# Exit out to scheduler with error code 18
raise_exception_ext('18 : Excel file processing error', 18);
 

END
END
ALGUICOMMENT( x = '1797', y = '-1204' , ui_display_name = 'Catch_Repository_Access_Errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>635</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-190</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CATCH (1006)
BEGIN
ALGUICOMMENT( x = '743', y = '-149' , ui_display_name = 'SC_Repository_Access_Errors' , ui_script_text = 'print(\'Fatal Repository access error has occured..!\');
print(\'Error number : \'||error_number( ));
print(\'Error message : \'||error_message( ));
print(\'Error context : \'||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV(\'Repository access error : \'||error_message( ), job_name(), \'3\');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics(\'Repository access error\');

# Exit out to scheduler with error code 15
raise_exception_ext(\'15 : Repository access error\', 15);

',ObjectDesc='This script will catch any specific repository access errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>38</X><Y>0</Y></LOCATION><SIZE><CX>422</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-206</Y></LOCATION><SIZE><CX>366</CX><CY>238</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Fatal Repository access error has occured..!');
print('Error number : '||error_number( ));
print('Error message : '||error_message( ));
print('Error context : '||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV('Repository access error : '||error_message( ), job_name(), '3');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics('Repository access error');

# Exit out to scheduler with error code 15
raise_exception_ext('15 : Repository access error', 15);


 

END
END
ALGUICOMMENT( x = '1795', y = '-1456' , ui_display_name = 'Catch_System_Resource_Exception_Errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>810</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-190</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CATCH (1008)
BEGIN
ALGUICOMMENT( x = '831', y = '-307' , ui_display_name = 'SC_System_Resource_Exception_Errors' , ui_script_text = 'print(\'Fatal System Resource Exception error has occured..!\');
print(\'Error number : \'||error_number( ));
print(\'Error message : \'||error_message( ));
print(\'Error context : \'||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV(\'System Resource Exception error : \'||error_message( ), job_name(), \'3\');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics(\'System Resource Exception error\');

# Exit out to scheduler with error code 16
raise_exception_ext(\'16 : System Resource Exception error\', 16);',ObjectDesc='This script will catch any specific system exception errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>63</X><Y>0</Y></LOCATION><SIZE><CX>453</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-215</Y></LOCATION><SIZE><CX>410</CX><CY>200</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Fatal System Resource Exception error has occured..!');
print('Error number : '||error_number( ));
print('Error message : '||error_message( ));
print('Error context : '||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV('System Resource Exception error : '||error_message( ), job_name(), '3');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics('System Resource Exception error');

# Exit out to scheduler with error code 16
raise_exception_ext('16 : System Resource Exception error', 16);
 

END
END
ALGUICOMMENT( x = '1795', y = '-1721' , ui_display_name = 'Catch_Execution_Errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>469</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-190</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CATCH (1001)
BEGIN
ALGUICOMMENT( x = '936', y = '-185' , ui_display_name = 'SC_Execution_Errors' , ui_script_text = 'print(\'Fatal execution error has occured..!\');
print(\'Error number : \'||error_number( ));
print(\'Error message : \'||error_message( ));
print(\'Error context : \'||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV(\'Execution error : \'||error_message( ), job_name(), \'3\');

# Update Job Control and Audit tables with failed status
# Filtering off Previous execution exception
if(substr(error_message(),1,2) != 23)
	FN_ITR2_Stg_Update_ETLMetrics(\'Execution error : \'||error_message());

raise_exception_ext(\'17 : Execution error\', 17);

',ObjectDesc='This script will catch any specific engine abort errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>65</X><Y>0</Y></LOCATION><SIZE><CX>416</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-221</Y></LOCATION><SIZE><CX>450</CX><CY>200</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Fatal execution error has occured..!');
print('Error number : '||error_number( ));
print('Error message : '||error_message( ));
print('Error context : '||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV('Execution error : '||error_message( ), job_name(), '3');

# Update Job Control and Audit tables with failed status
# Filtering off Previous execution exception
if(substr(error_message(),1,2) != 23)
	FN_ITR2_Stg_Update_ETLMetrics('Execution error : '||error_message());

raise_exception_ext('17 : Execution error', 17);


 

END
END
ALGUICOMMENT( x = '1666', y = '-1984' , ui_display_name = 'Catch_All_Exceptions',ObjectDesc='This Try/Catch series will catch all errors, set an error status against the Job run and exit.',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>138</X><Y>0</Y></LOCATION><SIZE><CX>425</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-214</Y></LOCATION><SIZE><CX>570</CX><CY>182</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>' )
CATCH (ALL)
BEGIN
ALGUICOMMENT( x = '681', y = '-160' , ui_display_name = 'SC_Catch_All_Exceptions' , ui_script_text = 'print(\'Fatal exception error has occured..!\');
print(\'Error number : \'||error_number( ));
print(\'Error message : \'||error_message( ));
print(\'Error context : \'||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV(\'Other Exception error : \'||error_message( ), job_name(), \'3\');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics(\'Exception error\');

# Exit out to scheduler with error code 19
raise_exception_ext(\'19 : Other Exception...!\', 19);

',ObjectDesc='This script will catch all exception errors',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>19</X><Y>0</Y></LOCATION><SIZE><CX>438</CX><CY>-176</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-218</Y></LOCATION><SIZE><CX>325</CX><CY>210</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Fatal exception error has occured..!');
print('Error number : '||error_number( ));
print('Error message : '||error_message( ));
print('Error context : '||error_context( ));

#	Alert HPOV : FN_Alert_HPOV(<alert message>, <job_name>, <priority>)
	FN_ITR2_Alert_HPOV('Other Exception error : '||error_message( ), job_name(), '3');

# Update Job Control and Audit tables with failed status
FN_ITR2_Stg_Update_ETLMetrics('Exception error');

# Exit out to scheduler with error code 19
raise_exception_ext('19 : Other Exception...!', 19);


 

END
END
END_TRY
END

SET("database_type" = 'Microsoft_SQL_Server',
"job_GV_$G_Adhoc_File_Name" = 'null',
"job_GV_$G_Delete_Flag" = '\'Y\'',
"job_GV_$G_File_Count" = 'null',
"job_GV_$G_File_Type" = '\'OAQ\'',
"job_GV_$G_HPOV_Flag" = '\'Y\'',
"job_GV_$G_Pst_Table_Name" = '\'123\'',
"job_GV_$G_Source_Name" = '\'sam\'',
"job_GV_$G_Stg_Table_Name" = '\'123\'',
"job_GV_$G_Sysdate" = 'null',
"job_GV_$G_Table_Id" = '\'123\'',
"job_checkpoint_enabled" = 'no',
"job_collect_statistics" = 'yes',
"job_collect_statistics_monitor" = 'yes',
"job_enable_assemblers" = 'yes',
"job_enable_audit" = 'yes',
"job_enable_dataquality" = 'yes',
"job_export_repo" = 'no',
"job_guid" = '141e3ada-830e-41f6-ac5a-7239ebc55bea',
"job_isrecoverable" = 'no',
"job_mode" = 'Multi-Process',
"job_monitor_sample_rate" = '50000',
"job_name" = 'JOB_123',
"job_print_version" = 'no',
"job_testmode_enabled" = 'no',
"job_trace_abapquery" = 'no',
"job_trace_all" = 'no',
"job_trace_ascomm" = 'no',
"job_trace_assemblers" = 'no',
"job_trace_audit" = 'no',
"job_trace_dataflow" = 'yes',
"job_trace_idoc_file" = 'no',
"job_trace_memory_loader" = 'no',
"job_trace_memory_reader" = 'no',
"job_trace_optimized_dataflow" = 'no',
"job_trace_parallel_execution" = 'no',
"job_trace_rfc_function" = 'no',
"job_trace_row" = 'no',
"job_trace_script" = 'no',
"job_trace_session" = 'yes',
"job_trace_sql_only" = 'no',
"job_trace_sqlfunctions" = 'no',
"job_trace_sqlloaders" = 'no',
"job_trace_sqlreaders" = 'no',
"job_trace_sqltransforms" = 'no',
"job_trace_stored_procedure" = 'no',
"job_trace_table" = 'no',
"job_trace_table_reader" = 'no',
"job_trace_transform" = 'no',
"job_trace_userfunction" = 'no',
"job_trace_usertransform" = 'no',
"job_trace_workflow" = 'yes',
"job_type" = 'batch',
"job_use_statistics" = 'no',
"locale_codepage" = 'ms1252',
"locale_language" = 'eng',
"locale_territory" = 'us',
"oracle_host_string" = 'S401DYDEV49',
"password" = ';4276A51FFAC9466709A57595CEF28A9DE6F26933EC498CCC2666D11A531D09D0',
"sql_server_database" = 'DI_REPO',
"sql_server_dataserver" = 'S401DYDEV49',
"user" = 'bo_admin')
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RDescription' , "ActaValue_1" = 'Initialize the job variables, setup the job control tables, check the datastore configurations and output the job start information to the joblog.' , "ActaName_2" = 'RDate_modified' , "ActaValue_2" = 'Mon May 03 09:29:25 2010' , "ActaName_3" = 'RSavedAfterCheckOut' , "ActaValue_3" = 'NO' , "ActaName_4" = 'RDate_created' , "ActaValue_4" = 'Fri Nov 27 06:45:16 2009',ANNOTATION_0='ALGUICOMMENT( x = \'187\', y = \'-547\',ObjectDesc=\'Global variable are declared and  directory paths are set here.\',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-187</X><Y>547</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>556</CX><CY>200</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
',ANNOTATION_1='ALGUICOMMENT( x = \'994\', y = \'-503\',ObjectDesc=\'Run_id generation, previous job status check and job status updation are performed in this script.\',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-994</X><Y>503</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>659</CX><CY>200</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
' )
CREATE PLAN WF_ITR2_Fil_To_Stg_Initialise::'4bfb03fc-476d-4eea-86c8-e0f276e2b6a2'( )
DECLARE
     $L_Prev_Run_Status varchar(20);
     $L_Table_Name varchar(50);
BEGIN
ALGUICOMMENT( x = '338', y = '-296' , ui_display_name = 'SC_Initialisation' , ui_script_text = '#---------------------------------------------------------------------#
#																	  #
#            Landing to Staging Initialization script        		  #
#---------------------------------------------------------------------#
#---------------------------------------------------------------------#
#
#	$G_Dir_Path			:	Specifies Directory Path
#	$G_Inbox_Dir		:	Will point to the source specific Inbox directory
#	$G_Archive_Dir		:	Specifies the Archive directory
#	$G_Error_Dir		:	Points to the Error directory
#	$G_Job_Name			:	Stores Job Name
#	$G_Job_Id			:	Stores Job id
#	$G_Table_Id			:	Stores TABLE_ID
#	$G_Table_Name		:	Stores Target table name
#
#---------------------------------------------------------------------#
#			Landing Area path and directories
#---------------------------------------------------------------------#
#		
#	/var/$env/						Parent directory
#	/var/$env/$source/inbox/		Interface file inbox directory
#	/var/$env/$source/in_archive	Interface Source Archive Directory(.atl scripts for file landing to staging)
#	/var/$env/$source/in_error		Interface Error/Reject Directory
#	/var/$env/$source/in_error_log	Interface error log Directory
#	/var/$env/$source/log			Common Log Directory
#	$G_Source_Name = \'sam\',\'inv\',\'conquest\',\'unique_sites\', \'gemini\' and \'manual_entry\'
#
#---------------------------------------------------------------------#

#   Print job statistics over Reading of file
#	print(\'__________________Initialisation Process__________________\');
#	print(\'____________________________________________________\');
	
# Call FN_Initialisation() function to do intialisation processes
	print(\'TabID : \'||$G_Table_Id);
	FN_ITR2_Stg_Initialisation();',ObjectDesc='Setup initial variables such as run date, environment.',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>335</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>44</X><Y>-201</Y></LOCATION><SIZE><CX>256</CX><CY>439</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
#---------------------------------------------------------------------#
#																	  #
#            Landing to Staging Initialization script        		  #
#---------------------------------------------------------------------#
#---------------------------------------------------------------------#
#
#	$G_Dir_Path			:	Specifies Directory Path
#	$G_Inbox_Dir		:	Will point to the source specific Inbox directory
#	$G_Archive_Dir		:	Specifies the Archive directory
#	$G_Error_Dir		:	Points to the Error directory
#	$G_Job_Name			:	Stores Job Name
#	$G_Job_Id			:	Stores Job id
#	$G_Table_Id			:	Stores TABLE_ID
#	$G_Table_Name		:	Stores Target table name
#
#---------------------------------------------------------------------#
#			Landing Area path and directories
#---------------------------------------------------------------------#
#		
#	/var/$env/						Parent directory
#	/var/$env/$source/inbox/		Interface file inbox directory
#	/var/$env/$source/in_archive	Interface Source Archive Directory(.atl scripts for file landing to staging)
#	/var/$env/$source/in_error		Interface Error/Reject Directory
#	/var/$env/$source/in_error_log	Interface error log Directory
#	/var/$env/$source/log			Common Log Directory
#	$G_Source_Name = 'sam','inv','conquest','unique_sites', 'gemini' and 'manual_entry'
#
#---------------------------------------------------------------------#

#   Print job statistics over Reading of file
#	print('__________________Initialisation Process__________________');
#	print('____________________________________________________');
	
# Call FN_Initialisation() function to do intialisation processes
	print('TabID : '||$G_Table_Id);
	FN_ITR2_Stg_Initialisation();
 

END
ALGUICOMMENT( x = '1106', y = '-295' , ui_display_name = 'SC_Initial_Checks' , ui_script_text = '#---------------------------------------------------------------------#
#																	  #
#            			Initial Checks							      #
#---------------------------------------------------------------------#

#print(\'_____________________Initial Checks_____________________\');
#print(\'____________________________________________________\');


# Call FN_Initial_Checks() function to do initial control table checks, creation of run_id, job_id
FN_ITR2_Stg_Initial_Checks();
',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>372</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
#---------------------------------------------------------------------#
#																	  #
#            			Initial Checks							      #
#---------------------------------------------------------------------#

#print('_____________________Initial Checks_____________________');
#print('____________________________________________________');


# Call FN_Initial_Checks() function to do initial control table checks, creation of run_id, job_id
FN_ITR2_Stg_Initial_Checks();

 

END
END

SET(unit_of_recovery = 'no',
run_once = 'no')
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RDate_modified' , "ActaValue_1" = 'Mon May 03 13:13:27 2010' , "ActaName_2" = 'RSavedAfterCheckOut' , "ActaValue_2" = 'NO' , "ActaName_3" = 'RDate_created' , "ActaValue_3" = 'Fri Mar 12 10:18:09 2010',ANNOTATION_0='ALGUICOMMENT( x = \'85\', y = \'-457\',ObjectDesc=\'The data file(s) are retrieved from the inbox directory.\',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-85</X><Y>457</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>659</CX><CY>240</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
',ANNOTATION_1='ALGUICOMMENT( x = \'896\', y = \'-448\',ObjectDesc=\'Sequential file read and processing.\',UI_DATA_XML=\'<UIDATA><MAINICON><LOCATION><X>-896</X><Y>448</Y></LOCATION><SIZE><CX>0</CX><CY>0</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>617</CX><CY>233</CY></SIZE><VISIBLE>1</VISIBLE></DESCRIPTION></UIDATA>\' )
' )
CREATE PLAN WF_123::'97574228-86b2-4ffc-b52d-6fb3ef6cdfcd'( )
DECLARE
     $L_File_Count int;
     $L_File_List varchar(1000);
     $L_List_Size int;
     $L_File_Name varchar(100);
     $L_File_Id int;
     $L_Error_File_Count int;
     $L_Record_Count int;
     $L_Delimiter varchar(1);
     $L_Error_File1 varchar(100);
     $L_Error_File2 varchar(100);
     $L_Get_File_Name varchar(100);
     $L_Error_Flag varchar(1);
     $L_File_Sequence int;
     $L_File_Extension varchar(4);
     $L_Delete_Count int;
BEGIN
ALGUICOMMENT( x = '283', y = '-255' , ui_display_name = 'SC_Retrieve_Data_Files' , ui_script_text = '#---------------------------------------------------------------------#
#																	  #
#            Retrieve landing area files for processing 			  #
#			 and generate file_id       		  					  #
#---------------------------------------------------------------------#
#---------------------------------------------------------------------#

#print(\'___________________File Processing_____________________\');
#print(\'____________________________________________________\');

FN_ITR2_Stg_Get_Data_Files($L_File_List, $L_List_Size);
$L_Get_File_Name  = $L_File_List;

#	Set variables for file count, file_id generation, total insert and error counts
$L_File_Count = 1;
$G_Token_Flag = 0;
#	The File that is read first will hold the $L_File_Count value
$L_File_Id = $L_File_Count;',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>478</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
#---------------------------------------------------------------------#
#																	  #
#            Retrieve landing area files for processing 			  #
#			 and generate file_id       		  					  #
#---------------------------------------------------------------------#
#---------------------------------------------------------------------#

#print('___________________File Processing_____________________');
#print('____________________________________________________');

FN_ITR2_Stg_Get_Data_Files($L_File_List, $L_List_Size);
$L_Get_File_Name  = $L_File_List;

#	Set variables for file count, file_id generation, total insert and error counts
$L_File_Count = 1;
$G_Token_Flag = 0;
#	The File that is read first will hold the $L_File_Count value
$L_File_Id = $L_File_Count;
 

END
ALGUICOMMENT( x = '1017', y = '-253' , ui_display_name = 'File_Read_WL',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>310</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
WHILE (($L_File_Count <= $L_List_Size))
BEGIN
ALGUICOMMENT( x = '113', y = '-28' , ui_display_name = 'SC_Set_File_Name' , ui_script_text = '#---------------------------------------------------------------------#
#																	  #
#            Retrieve file names and perform inserts to 			  #
#			 file_detail table										  #
#---------------------------------------------------------------------#
#---------------------------------------------------------------------#
#
# The variable $L_File_List will contain the filename along with file path 
# A substr() is performed on $L_File_List to obtain the filename

FN_ITR2_Stg_Set_File_Name($L_Get_File_Name,$L_File_Id,$L_Record_Count);
$L_Error_Flag = 0;

',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>397</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-187</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
#---------------------------------------------------------------------#
#																	  #
#            Retrieve file names and perform inserts to 			  #
#			 file_detail table										  #
#---------------------------------------------------------------------#
#---------------------------------------------------------------------#
#
# The variable $L_File_List will contain the filename along with file path 
# A substr() is performed on $L_File_List to obtain the filename

FN_ITR2_Stg_Set_File_Name($L_Get_File_Name,$L_File_Id,$L_Record_Count);
$L_Error_Flag = 0;


 

END
ALGUICOMMENT( x = '200', y = '-353',ParmDefaulted_01=FALSE,UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>216</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CALL DATAFLOW DF_123::'b37bc29e-1bb9-4e40-b8e4-06b13771b00f'( &ERROR('') );
ALGUICOMMENT( x = '109', y = '-679' , ui_display_name = 'SC_Suspense_Capture' , ui_script_text = '# 	THIS FUNCTION IS USED TO CAPTURE THE INSERTS THAT ARE ALREADY PRESENT IN THE PST 
#	TO AVOID UNIQUE KEY CONSTRAINT. IT ALSO CAPTURES THOSE UPDATES AND DELETES THAT DO NOT HAVE A PARENT
#	ISNERT AND THOSE UPDATES THAT WOULD HAVE MORE THAN ONE UPDATE IN THE PST TABLE

FN_ITR2_Stg_Capture_To_Suspense($G_Pst_Table_Name,$G_Stg_Table_Name,$G_Run_id);


$L_Delete_Count = sql(\'DS_ORA_IPW\',\'SELECT COUNT(*) FROM [$G_Stg_Table_Name] where (DS_STATUS_FLAG=\\\'W\\\' OR DS_STATUS_FLAG=\\\'N\\\') and trans_type=\\\'D\\\' AND ROWNUM<2 AND DS_RUN_ID = [$G_Run_Id] and start_date < to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\')-1\');

IF($L_Delete_Count = 1)
begin
	print(\'DELETES HAVE BEEN DETECTED IN THE STAGING TABLE\');
	sql(\'DS_ORA_IPW\',\'UPDATE [$G_Stg_Table_Name] SET DS_STATUS_FLAG = \\\'E\\\' WHERE (DS_STATUS_FLAG=\\\'W\\\' OR DS_STATUS_FLAG=\\\'N\\\') AND trans_type=\\\'D\\\' AND DS_RUN_ID = [$G_Run_Id] and start_date < to_date({$G_Sysdate},\\\'yyyy.mm.dd hh24:mi:ss\\\')-1\');
end



 
',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>466</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
# 	THIS FUNCTION IS USED TO CAPTURE THE INSERTS THAT ARE ALREADY PRESENT IN THE PST 
#	TO AVOID UNIQUE KEY CONSTRAINT. IT ALSO CAPTURES THOSE UPDATES AND DELETES THAT DO NOT HAVE A PARENT
#	ISNERT AND THOSE UPDATES THAT WOULD HAVE MORE THAN ONE UPDATE IN THE PST TABLE

FN_ITR2_Stg_Capture_To_Suspense($G_Pst_Table_Name,$G_Stg_Table_Name,$G_Run_id);


$L_Delete_Count = sql('DS_ORA_IPW','SELECT COUNT(*) FROM [$G_Stg_Table_Name] where (DS_STATUS_FLAG=\'W\' OR DS_STATUS_FLAG=\'N\') and trans_type=\'D\' AND ROWNUM<2 AND DS_RUN_ID = [$G_Run_Id] and start_date < to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\')-1');

IF($L_Delete_Count = 1)
begin
	print('DELETES HAVE BEEN DETECTED IN THE STAGING TABLE');
	sql('DS_ORA_IPW','UPDATE [$G_Stg_Table_Name] SET DS_STATUS_FLAG = \'E\' WHERE (DS_STATUS_FLAG=\'W\' OR DS_STATUS_FLAG=\'N\') AND trans_type=\'D\' AND DS_RUN_ID = [$G_Run_Id] and start_date < to_date({$G_Sysdate},\'yyyy.mm.dd hh24:mi:ss\')-1');
end



 

END
ALGUICOMMENT( x = '172', y = '-1137',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>235</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CALL DATAFLOW DF_123_1::'b2b45d0a-35fd-410a-bcd7-92c42c39d6c7'();
ALGUICOMMENT( x = '93', y = '-1474' , ui_display_name = 'SC_Check_error_file' , ui_script_text = 'print(\'Error file generated :\'||$G_Error_Log_Dir||$G_Error_Filename);

# Call FN_Get_Error_File_Count() function to retrieve error file count
$L_Error_File_Count = FN_ITR2_Stg_Get_Error_File_Count();
print(\'Error count :\'||$L_Error_File_Count);
',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>413</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('Error file generated :'||$G_Error_Log_Dir||$G_Error_Filename);

# Call FN_Get_Error_File_Count() function to retrieve error file count
$L_Error_File_Count = FN_ITR2_Stg_Get_Error_File_Count();
print('Error count :'||$L_Error_File_Count);

 

END
ALGUICOMMENT( x = '585', y = '-1474' , ui_display_name = 'Process_Errors_CW',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>410</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' ,  If_condition = '$L_Error_File_Count> 0'  )
IF ($L_Error_File_Count> 0)
BEGIN
ALGUICOMMENT( x = '254', y = '-287' , ui_display_name = 'SC_Split_Error_File' , ui_script_text = '#	Set Delimiter character
$L_Delimiter = chr(207);

$L_Error_File1 = \'part1_\'||$G_Error_Filename;
$L_Error_File2 = \'part2_\'||$G_Error_Filename;

#	print(\'Splitting error file (cut cmd)\');
print(\'Execution status for split file1 (0 - Success) : \'||exec(\'ksh\',\'-c "cut -f1-4 -d\'||$L_Delimiter||\' [$G_Error_Log_Dir][$G_Error_Filename] > [$G_Error_Log_Dir][$L_Error_File1]"\', 8));
print(\'Execution status for split file2 (0 - Success) : \'||exec(\'ksh\',\'-c "cut -f5- -d\'||$L_Delimiter||\' [$G_Error_Log_Dir][$G_Error_Filename] > [$G_Error_Log_Dir][$L_Error_File2]"\', 8));

#	Add up error count to get total error count(from all the files processed) for ETL_METRICS table
$L_Error_Flag = 1;
$G_Token_Flag = 1;

#	update details in File_Detail table
#	print(\'Updating details in File_detail table : file_status_code\');
sql(\'DS_ORA_IPW\', \'update file_detail set file_status_code = \\\'3\\\' where run_id = [$G_Run_Id] AND file_id = [$L_File_Id]\');',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>394</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-191</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
#	Set Delimiter character
$L_Delimiter = chr(207);

$L_Error_File1 = 'part1_'||$G_Error_Filename;
$L_Error_File2 = 'part2_'||$G_Error_Filename;

#	print('Splitting error file (cut cmd)');
print('Execution status for split file1 (0 - Success) : '||exec('ksh','-c "cut -f1-4 -d'||$L_Delimiter||' [$G_Error_Log_Dir][$G_Error_Filename] > [$G_Error_Log_Dir][$L_Error_File1]"', 8));
print('Execution status for split file2 (0 - Success) : '||exec('ksh','-c "cut -f5- -d'||$L_Delimiter||' [$G_Error_Log_Dir][$G_Error_Filename] > [$G_Error_Log_Dir][$L_Error_File2]"', 8));

#	Add up error count to get total error count(from all the files processed) for ETL_METRICS table
$L_Error_Flag = 1;
$G_Token_Flag = 1;

#	update details in File_Detail table
#	print('Updating details in File_detail table : file_status_code');
sql('DS_ORA_IPW', 'update file_detail set file_status_code = \'3\' where run_id = [$G_Run_Id] AND file_id = [$L_File_Id]');
 

END
ALGUICOMMENT( x = '669', y = '-369',ParmDefaulted_01=FALSE,ParmDefaulted_02=FALSE,ParmDefaulted_03=FALSE,UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>544</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-187</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )
CALL DATAFLOW DF_ITR2_Stg_Staging_Error::'2dc24b5e-9a0c-417d-a3da-884cde5af7bd'($L_File_Id, $L_Error_File1, $L_Error_File2);
ALGUICOMMENT( x = '1066', y = '-475' , ui_display_name = 'SC_Remove_Split_File' , ui_script_text = '# Call FN_Remove_Split_Error_Files() function to remove the split error files
FN_ITR2_Stg_Remove_Split_Error_Files($L_Error_File1, $L_Error_File2);',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>453</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-187</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
# Call FN_Remove_Split_Error_Files() function to remove the split error files
FN_ITR2_Stg_Remove_Split_Error_Files($L_Error_File1, $L_Error_File2);
 

END
END
ELSE
BEGIN
ALGUICOMMENT( x = '675', y = '-151' , ui_display_name = 'SC_Print_No_Error' , ui_script_text = 'print(\'No error encountered in file \'||$L_Get_File_Name||\'......\');',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>385</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-187</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
print('No error encountered in file '||$L_Get_File_Name||'......');
 

END
END
ALGUICOMMENT( x = '1026', y = '-1472' , ui_display_name = 'SC_Get_Next_File' , ui_script_text = '#---------------------------------------------------------------------#
#																	  #
#            Retrieve next file for processing 						  #
#			 move processed file to archive							  #
#			 update details of processed file to file_detail table	  #
#---------------------------------------------------------------------#

# Call FN_Get_Next_File() function to retrieve the next file to be processed
FN_ITR2_Stg_Get_Next_File($L_File_List, $L_List_Size, $L_File_Count, $L_Get_File_Name, $L_Record_Count, $L_Error_File_Count, $L_File_Id, $L_Error_Flag);

',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>369</CX><CY>-182</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-187</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>' )

BEGIN_SCRIPT
#---------------------------------------------------------------------#
#																	  #
#            Retrieve next file for processing 						  #
#			 move processed file to archive							  #
#			 update details of processed file to file_detail table	  #
#---------------------------------------------------------------------#

# Call FN_Get_Next_File() function to retrieve the next file to be processed
FN_ITR2_Stg_Get_Next_File($L_File_List, $L_List_Size, $L_File_Count, $L_Get_File_Name, $L_Record_Count, $L_Error_File_Count, $L_File_Id, $L_Error_Flag);


 

END
END
END

SET(unit_of_recovery = 'no',
run_once = 'no')
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RSavedAfterCheckOut' , "ActaValue_1" = 'NO' , "ActaName_2" = 'Rcolumn_mapping_calculated' , "ActaValue_2" = 'yes' , "ActaName_3" = 'RDate_modified' , "ActaValue_3" = 'Mon May 03 09:29:28 2010' , "ActaName_4" = 'RDate_created' , "ActaValue_4" = 'Fri Nov 27 06:45:20 2009' )
CREATE DATAFLOW DF_ITR2_Stg_Staging_Error::'2dc24b5e-9a0c-417d-a3da-884cde5af7bd' ($P_File_ID int IN, $P_Error_File1 varchar(100) IN, $P_Error_File2 varchar(100) IN )

BEGIN 
ALGUICOMMENT( x = '366', y = '-137' , ui_acta_from_schema_0 = 'FF_ITR2_Error_File_part_1'  , ui_display_name = 'Error_Detail_Q' ,UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>310</CX><CY>-166</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '49', InputSchema_WidthProp = '49', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'b9170f1c-7881-425b-8071-5f6e99e364b6' CREATE VIEW Error_Detail_Q ( FileName varchar(43) SET("ui_mapping_text"='FF_ITR2_Error_File_part_1.FileName
 
') ,
 Row_Id int SET("ui_mapping_text"='gen_row_num( )
 
') ,
 RowNumber int SET("ui_mapping_text"='FF_ITR2_Error_File_part_1.RowNumber
 
') ,
 ErrorDescription varchar(2000) SET("ui_mapping_text"='FF_ITR2_Error_File_part_1.ErrorDescription
 
') ,
 ColumnId int SET("ui_mapping_text"='FF_ITR2_Error_File_part_1.ColumnID
 
')  ) 
AS SELECT  FF_ITR2_Error_File_part_1.FileName
 
 ,
 gen_row_num( )
 
 ,
 FF_ITR2_Error_File_part_1.RowNumber
 
 ,
 FF_ITR2_Error_File_part_1.ErrorDescription
 
 ,
 FF_ITR2_Error_File_part_1.ColumnID
 
 
	FROM FF_ITR2_Error_File_part_1 SET (
"distinct_run_as_separate_process" = 'no',
"group_by_run_as_separate_process" = 'no',
"join_run_as_separate_process" = 'no',
"order_by_run_as_separate_process" = 'no',
"run_as_separate_process" = 'no');
ALGUICOMMENT( x = '393', y = '-554' , ui_acta_from_schema_0 = 'FF_ITR2_Error_File_Part_2'  , ui_display_name = 'Error_Record_Q' ,UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>335</CX><CY>-166</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '49', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'889527fb-ac63-4238-a2f4-77c3a5dec9ea' CREATE VIEW Error_Record_Q ( ErrorRecord varchar(4000) SET("ui_mapping_text"='FF_ITR2_Error_File_Part_2.ErrorRecord
 
') ,
 Row_Id int SET("ui_mapping_text"='gen_row_num( )
 
')  ) 
AS SELECT  FF_ITR2_Error_File_Part_2.ErrorRecord
 
 ,
 gen_row_num( )
 
 
	FROM FF_ITR2_Error_File_Part_2 SET (
"distinct_run_as_separate_process" = 'no',
"group_by_run_as_separate_process" = 'no',
"join_run_as_separate_process" = 'no',
"order_by_run_as_separate_process" = 'no',
"run_as_separate_process" = 'no');
ALGUICOMMENT( x = '782', y = '-179' , ui_acta_from_schema_0 = 'Error_Record_Q'  , ui_acta_from_schema_1 = 'Error_Detail_Q'  , ui_display_name = 'Insert_To_Error_Log_Q'  , ui_where_text = '(Error_Record_Q.Row_Id = Error_Detail_Q.Row_Id)' ,UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>460</CX><CY>-166</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '57', InputSchema_WidthProp = '46', Output_Width_Name = '264', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '120', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'3dca9855-b45d-4918-bdbc-fc7a88c4dc4c' CREATE VIEW Insert_To_Error_Log_Q ( FILE_ID int SET("ui_mapping_text"='$P_File_ID') ,
 ERROR_RECORD varchar(4000) SET("ui_mapping_text"='Error_Record_Q.ErrorRecord') ,
 COLUMN_ID int SET("ui_mapping_text"='Error_Detail_Q.ColumnId') ,
 TAB_ID varchar(5) SET("ui_mapping_text"='$G_TABLE_ID') ,
 RUN_ID int SET("ui_mapping_text"='$G_RUN_ID') ,
 RECORD_NUMBER int SET("ui_mapping_text"='Error_Detail_Q.RowNumber') ,
 ERROR_INSERT_DATE datetime SET("ui_mapping_text"='$G_Sysdate') ,
 ERROR_DESC varchar(2000) SET("ui_mapping_text"='Error_Detail_Q.ErrorDescription')  ) 
AS SELECT  $P_File_ID
 
 ,
 Error_Record_Q.ErrorRecord
 
 ,
 Error_Detail_Q.ColumnId
 
 ,
 $G_TABLE_ID
 
 ,
 $G_RUN_ID
 
 ,
 Error_Detail_Q.RowNumber
 
 ,
 $G_Sysdate
 
 ,
 Error_Detail_Q.ErrorDescription
 
 
	FROM Error_Detail_Q, Error_Record_Q
	WHERE (Error_Record_Q.Row_Id = Error_Detail_Q.Row_Id)
 
 SET (
"distinct_run_as_separate_process" = 'no',
"group_by_run_as_separate_process" = 'no',
"join_run_as_separate_process" = 'no',
"order_by_run_as_separate_process" = 'no',
"run_as_separate_process" = 'no');
ALGUICOMMENT( x = '1038', y = '-437' , ui_acta_from_schema_0 = 'Insert_To_Error_Log_Q'  , ui_display_name = 'Error_Count_Q' ,UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>313</CX><CY>-166</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'c18038fc-78a3-42c5-80ec-c607386ce7a5' CREATE VIEW Error_Count_Q ( error_count int SET("ui_mapping_text"='count(Insert_To_Error_Log_Q.RUN_ID)')  ) 
AS SELECT  count(Insert_To_Error_Log_Q.RUN_ID)
 
 
	FROM Insert_To_Error_Log_Q SET (
"distinct_run_as_separate_process" = 'no',
"group_by_run_as_separate_process" = 'no',
"join_run_as_separate_process" = 'no',
"order_by_run_as_separate_process" = 'no',
"run_as_separate_process" = 'no');
ALGUICOMMENT( x = '1385', y = '-616' , ui_acta_from_schema_0 = 'ETL_METRICS'  , ui_acta_from_schema_1 = 'Error_Count_Q'  , ui_display_name = 'Update_ETLMetrics_Q'  , ui_where_text = 'ETL_METRICS.RUN_ID = $G_Run_Id' ,UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>441</CX><CY>-166</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'68a4eb56-a043-49f0-b1c3-ed4ab8dcb720' CREATE VIEW Update_ETLMetrics_Q ( RUN_ID int KEY  SET("ui_mapping_text"='ETL_METRICS.RUN_ID
 
') ,
 TARGET_TAB_NAME varchar(30) KEY  SET("ui_mapping_text"='ETL_METRICS.TARGET_TAB_NAME
 
') ,
 REJECT_RECORD_COUNT decimal(24,11) SET("ui_mapping_text"='Error_Count_Q.error_count + ETL_METRICS.REJECT_RECORD_COUNT
 
')  ) 
AS SELECT  ETL_METRICS.RUN_ID
 
 ,
 ETL_METRICS.TARGET_TAB_NAME
 
 ,
 Error_Count_Q.error_count + ETL_METRICS.REJECT_RECORD_COUNT
 
 
	FROM Error_Count_Q, ETL_METRICS
	WHERE ETL_METRICS.RUN_ID = $G_Run_Id
 
 SET (
"distinct_run_as_separate_process" = 'no',
"group_by_run_as_separate_process" = 'no',
"join_run_as_separate_process" = 'no',
"order_by_run_as_separate_process" = 'no',
"run_as_separate_process" = 'no');
ALGUICOMMENT( x = '1400', y = '-993' , ui_display_name = 'Update_ETLMetrics_MO',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>469</CX><CY>-166</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = '' )
GUID::'2079c2bb-e8c5-4a13-a8ac-20b1f030c465' CALL TRANSFORM Map_Operation ( ) 
INPUT ( Update_ETLMetrics_Q )
OUTPUT ( Update_ETLMetrics_MO_1 ( RUN_ID int KEY ,
 TARGET_TAB_NAME varchar(30) KEY ,
 REJECT_RECORD_COUNT decimal(24,11) )  )
SET ("delete" = 'discard',
"insert" = 'discard',
"normal" = 'update',
"update" = 'discard');
ALGUICOMMENT( x = '975', y = '-781',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>813</CX><CY>-185</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'b457b2c0-eb9a-4579-a259-0432f0fa95ad' READ TABLE DS_ORA_IPW.STG_OBJECTS.ETL_METRICS  OUTPUT ( ETL_METRICS ) SET (
"array_fetch_size" = '1000',
"cache" = 'yes',
"connection_port" = 'no',
"enable_partitioning" = 'no',
"name" = 'DS_ORA_IPW',
"package_size" = '0',
"reader_is_DB2CDC_table" = 'no',
"reader_overflow_file" = 'overflow_file',
"reader_template_table" = 'no',
"reader_use_overflow_file" = 'no',
"table_weight" = '0');
ALGUICOMMENT( x = '1953', y = '-988',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>857</CX><CY>-185</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'16b63d18-1ef0-4e30-88ac-2a2899c8983a' LOAD TABLE DS_ORA_IPW.STG_OBJECTS.ETL_METRICS  INPUT ( Update_ETLMetrics_MO_1 ) SET (
"LDRLiveLoad" = 'no',
"Table_Type" = 'TABLE',
"connection_port" = 'no',
"ldr_configuration_enabled" = 'yes',
"ldr_configurations" = '<?xml version="1.0" encoding="UTF-8"?>
<LDRConfigurations>
<LDRConfiguration database_type="Oracle" database_version="Oracle 10g">
<bulk_col_delimiter>/127</bulk_col_delimiter>
<bulk_ldr_cleanup>yes</bulk_ldr_cleanup>
<bulk_ldr_control_file_name></bulk_ldr_control_file_name>
<bulk_ldr_direct></bulk_ldr_direct>
<bulk_ldr_direct_api></bulk_ldr_direct_api>
<bulk_ldr_generate_files_only></bulk_ldr_generate_files_only>
<bulk_ldr_max_bind_array>100000</bulk_ldr_max_bind_array>
<bulk_ldr_max_discards>10</bulk_ldr_max_discards>
<bulk_ldr_mode>append</bulk_ldr_mode>
<bulk_ldr_recoverable></bulk_ldr_recoverable>
<bulk_ldr_rows_per_commit>1000</bulk_ldr_rows_per_commit>
<bulk_ldr_trailing_nullcols></bulk_ldr_trailing_nullcols>
<bulk_ldr_use_control_file></bulk_ldr_use_control_file>
<column_delimiter>,</column_delimiter>
<enable_partitioning>no</enable_partitioning>
<ignore_column_case>yes</ignore_column_case>
<ignore_columns_null>no</ignore_columns_null>
<ignore_columns_value></ignore_columns_value>
<loader_auto_correct>no</loader_auto_correct>
<loader_bulk_load></loader_bulk_load>
<loader_compare_column>compare_by_name</loader_compare_column>
<loader_delete_map></loader_delete_map>
<loader_drop_and_create_table>yes</loader_drop_and_create_table>
<loader_insert_map></loader_insert_map>
<loader_insert_select_commit_size>yes</loader_insert_select_commit_size>
<loader_load_choice>append</loader_load_choice>
<loader_num_parallel_loaders>1</loader_num_parallel_loaders>
<loader_overflow_file>overflow_file</loader_overflow_file>
<loader_post_load1></loader_post_load1>
<loader_pre_load1></loader_pre_load1>
<loader_quote_names>0</loader_quote_names>
<loader_transactional>no</loader_transactional>
<loader_transactional_order>0</loader_transactional_order>
<loader_update_key_columns>no</loader_update_key_columns>
<loader_update_map></loader_update_map>
<loader_use_user_defined_keys>no</loader_use_user_defined_keys>
<loader_xact_size>1000</loader_xact_size>
<oracle_auto_correct_using_merge>yes</oracle_auto_correct_using_merge>
<oracle_bulk_ldr_all_method>None</oracle_bulk_ldr_all_method>
<oracle_bulk_ldr_file_method>None</oracle_bulk_ldr_file_method>
<oracle_bulk_ldr_method>None</oracle_bulk_ldr_method>
<oracle_bulk_ldr_method_api>API</oracle_bulk_ldr_method_api>
<oracle_bulk_ldr_method_file>File</oracle_bulk_ldr_method_file>
<oracle_bulk_ldr_method_none>None</oracle_bulk_ldr_method_none>
<oracle_bulk_ldr_mode_api>append</oracle_bulk_ldr_mode_api>
<oracle_sqlldr_version></oracle_sqlldr_version>
<overflow_file_format>write_data</overflow_file_format>
<text_delimiter></text_delimiter>
<use_overflow_file>no</use_overflow_file>
<use_unicode_varchar>no</use_unicode_varchar>
<sql_text></sql_text>
</LDRConfiguration>
</LDRConfigurations>',
"loader_template_table" = 'no',
"name" = 'DS_ORA_IPW',
"use_unicode_varchar" = 'no');
ALGUICOMMENT( x = '1215', y = '-176',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>838</CX><CY>-185</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = '' )
GUID::'7dc18ad5-fef2-451e-af06-9927575a071b' LOAD TABLE DS_ORA_IPW.STG_OBJECTS.ERROR_LOG  INPUT ( Insert_To_Error_Log_Q ) SET (
"LDRLiveLoad" = 'no',
"Table_Type" = 'TABLE',
"connection_port" = 'no',
"ldr_configuration_enabled" = 'yes',
"ldr_configurations" = '<?xml version="1.0" encoding="UTF-8"?>
<LDRConfigurations>
<LDRConfiguration database_type="Oracle" database_version="Oracle 10g">
<bulk_col_delimiter>/127</bulk_col_delimiter>
<bulk_ldr_cleanup>yes</bulk_ldr_cleanup>
<bulk_ldr_control_file_name></bulk_ldr_control_file_name>
<bulk_ldr_direct></bulk_ldr_direct>
<bulk_ldr_direct_api></bulk_ldr_direct_api>
<bulk_ldr_generate_files_only></bulk_ldr_generate_files_only>
<bulk_ldr_max_bind_array>100000</bulk_ldr_max_bind_array>
<bulk_ldr_max_discards>10</bulk_ldr_max_discards>
<bulk_ldr_mode>append</bulk_ldr_mode>
<bulk_ldr_recoverable></bulk_ldr_recoverable>
<bulk_ldr_rows_per_commit>1000</bulk_ldr_rows_per_commit>
<bulk_ldr_trailing_nullcols></bulk_ldr_trailing_nullcols>
<bulk_ldr_use_control_file></bulk_ldr_use_control_file>
<column_delimiter>,</column_delimiter>
<enable_partitioning>no</enable_partitioning>
<ignore_column_case>yes</ignore_column_case>
<ignore_columns_null>no</ignore_columns_null>
<ignore_columns_value></ignore_columns_value>
<loader_auto_correct>no</loader_auto_correct>
<loader_bulk_load></loader_bulk_load>
<loader_compare_column>compare_by_name</loader_compare_column>
<loader_delete_map></loader_delete_map>
<loader_drop_and_create_table>yes</loader_drop_and_create_table>
<loader_insert_map></loader_insert_map>
<loader_insert_select_commit_size>yes</loader_insert_select_commit_size>
<loader_load_choice>append</loader_load_choice>
<loader_num_parallel_loaders>1</loader_num_parallel_loaders>
<loader_overflow_file>overflow_file</loader_overflow_file>
<loader_post_load1></loader_post_load1>
<loader_pre_load1></loader_pre_load1>
<loader_quote_names>0</loader_quote_names>
<loader_transactional>no</loader_transactional>
<loader_transactional_order>0</loader_transactional_order>
<loader_update_key_columns>no</loader_update_key_columns>
<loader_update_map></loader_update_map>
<loader_use_user_defined_keys>no</loader_use_user_defined_keys>
<loader_xact_size>1000</loader_xact_size>
<oracle_auto_correct_using_merge>yes</oracle_auto_correct_using_merge>
<oracle_bulk_ldr_all_method>None</oracle_bulk_ldr_all_method>
<oracle_bulk_ldr_file_method>None</oracle_bulk_ldr_file_method>
<oracle_bulk_ldr_method>None</oracle_bulk_ldr_method>
<oracle_bulk_ldr_method_api>API</oracle_bulk_ldr_method_api>
<oracle_bulk_ldr_method_file>File</oracle_bulk_ldr_method_file>
<oracle_bulk_ldr_method_none>None</oracle_bulk_ldr_method_none>
<oracle_bulk_ldr_mode_api>append</oracle_bulk_ldr_mode_api>
<oracle_sqlldr_version></oracle_sqlldr_version>
<overflow_file_format>write_data</overflow_file_format>
<text_delimiter></text_delimiter>
<use_overflow_file>no</use_overflow_file>
<use_unicode_varchar>no</use_unicode_varchar>
<sql_text></sql_text>
</LDRConfiguration>
</LDRConfigurations>',
"loader_template_table" = 'no',
"name" = 'DS_ORA_IPW',
"use_unicode_varchar" = 'no');
ALGUICOMMENT( x = '34', y = '-406',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>791</CX><CY>-185</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Description', Input_2nd_Column_Name = 'Type', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = '' )
GUID::'99a08868-a8d4-4432-ad32-0b5ba6849c35' READ FILE FF_ITR2_Error_File_Part_2."$P_Error_File2"  OUTPUT ( FF_ITR2_Error_File_Part_2 ) SET (
"adaptable" = 'no',
"cache" = 'yes',
"connection_port" = 'no',
"file_location" = 'job_server',
"ignore_row_markers" = 'A00;Z99',
"name" = 'FF_ITR2_Error_File_Part_2',
"reader_capture_data_conversion_errors" = 'no',
"reader_capture_row_format_errors" = 'yes',
"reader_filename_col" = 'DI_FILENAME',
"reader_filename_col_size" = '100',
"reader_filename_only" = 'yes',
"reader_include_filename" = 'no',
"reader_log_data_conversion_warnings" = 'yes',
"reader_log_row_format_warnings" = 'yes',
"reader_maximum_warnings_to_log" = '-99',
"reader_write_error_rows_to_file" = 'no',
"root_dir" = '$G_ERROR_LOG_DIR',
"table_weight" = '0',
"transfer_custom" = 'no');
ALGUICOMMENT( x = '28', y = '-28',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>788</CX><CY>-185</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Description', Input_2nd_Column_Name = 'Type', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = '' )
GUID::'8b5f9e1b-07c4-4bff-a631-e304949f1871' READ FILE FF_ITR2_Error_File_part_1."$P_Error_File1"  OUTPUT ( FF_ITR2_Error_File_part_1 ) SET (
"adaptable" = 'no',
"cache" = 'yes',
"connection_port" = 'no',
"file_location" = 'job_server',
"ignore_row_markers" = 'A00;Z99',
"name" = 'FF_ITR2_Error_File_part_1',
"reader_capture_data_conversion_errors" = 'no',
"reader_capture_row_format_errors" = 'no',
"reader_filename_col" = 'DI_FILENAME',
"reader_filename_col_size" = '100',
"reader_filename_only" = 'no',
"reader_include_filename" = 'no',
"reader_log_data_conversion_warnings" = 'no',
"reader_log_row_format_warnings" = 'no',
"reader_maximum_warnings_to_log" = '-99',
"reader_write_error_rows_to_file" = 'no',
"root_dir" = '$G_ERROR_LOG_DIR',
"table_weight" = '0',
"transfer_custom" = 'no');
END

SET("Audit" = '<?xml version="1.0" encoding="UTF-8"?>
<Audit>
<AuditObjects>
</AuditObjects>
<AuditRules>
</AuditRules>
<Notifications>
<RaiseException Enabled="true" >
</RaiseException>
<Mail Enabled="false" ></Mail>
<Script Enabled="false" ></Script>
</Notifications>
</Audit>
',
"Cache_type" = 'pageable_cache',
"Parallelism_degree" = '0',
"allows_both_input_and_output" = 'yes',
"run_once" = 'no',
"use_dataflow_links" = 'no',
"use_datastore_links" = 'yes',
"validation_xform_exists" = 'no',
"validation_xform_stats" = 'no')
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'RSavedAfterCheckOut' , "ActaValue_1" = 'NO' , "ActaName_2" = 'Rcolumn_mapping_calculated' , "ActaValue_2" = 'yes' , "ActaName_3" = 'RDate_modified' , "ActaValue_3" = 'Mon May 03 09:29:27 2010' , "ActaName_4" = 'RDate_created' , "ActaValue_4" = 'Fri Mar 12 10:18:11 2010' )
CREATE DATAFLOW DF_123_1::'b2b45d0a-35fd-410a-bcd7-92c42c39d6c7'

BEGIN 
ALGUICOMMENT( x = '835', y = '-405',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>422</CX><CY>-166</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-171</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = '' )
GUID::'3c7da2de-8784-4594-a3a0-2bf33016fc7f' CALL TRANSFORM Map_CDC_Operation ( ) 
INPUT ( LOAD_Q )
OUTPUT ( Map_CDC_Operation ( SPO_IDENTIFIER decimal(10,0),
 START_DATE datetime,
 CNF_REFERENCE_NUM int,
 DM_AQ_TOTAL decimal(12,0),
 DM_SOQ int,
 NDM_AQ_TOTAL decimal(12,0),
 SPT_CODE varchar(4),
 EUC_NUMBER int,
 EXZ_IDENTIFIER varchar(3),
 LDZ_IDENTIFIER varchar(4),
 NDM_SOQ int,
 WHOLLY_DM_IND varchar(1),
 NUM_DATALOGGERS int,
 END_DATE datetime,
 SPO_BOTTOM_STP_SOQ decimal(24,11),
 MRF_CODE varchar(1),
 NUM_INTRPTBL_DAYS int,
 ORG_ID decimal(10,0),
 DS_LOAD_DATE datetime )  )
SET ("cdc_source_is_sorted" = 'yes',
"row_op_col_name" = 'TRANS_TYPE',
"sequence_col_name" = 'TRANS_ID');
ALGUICOMMENT( x = '349', y = '-402' , ui_acta_from_schema_0 = 'SS_AI_SP_HISTORY'  , ui_display_name = 'LOAD_Q'  , ui_where_text = 'SS_AI_SP_HISTORY.DS_RUN_ID = $G_Run_Id
and SS_AI_SP_HISTORY.DS_STATUS_FLAG = \'W\'' ,UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>238</CX><CY>-166</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-171</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '64', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'cc276ed3-79bb-483f-968d-7a80e7e08f38' CREATE VIEW LOAD_Q ( TRANS_ID int SET("ui_mapping_text"='SS_AI_SP_HISTORY.TRANS_ID
 
') ,
 TRANS_TYPE varchar(1) SET("ui_mapping_text"='SS_AI_SP_HISTORY.TRANS_TYPE
 
') ,
 SPO_IDENTIFIER decimal(10,0) SET("ui_mapping_text"='SS_AI_SP_HISTORY.SPO_IDENTIFIER') ,
 START_DATE datetime SET("ui_mapping_text"='SS_AI_SP_HISTORY.START_DATE') ,
 CNF_REFERENCE_NUM int SET("ui_mapping_text"='SS_AI_SP_HISTORY.CNF_REFERENCE_NUM') ,
 DM_AQ_TOTAL decimal(12,0) SET("ui_mapping_text"='SS_AI_SP_HISTORY.DM_AQ_TOTAL') ,
 DM_SOQ int SET("ui_mapping_text"='SS_AI_SP_HISTORY.DM_SOQ') ,
 NDM_AQ_TOTAL decimal(12,0) SET("ui_mapping_text"='SS_AI_SP_HISTORY.NDM_AQ_TOTAL') ,
 SPT_CODE varchar(4) SET("ui_mapping_text"='SS_AI_SP_HISTORY.SPT_CODE') ,
 EUC_NUMBER int SET("ui_mapping_text"='SS_AI_SP_HISTORY.EUC_NUMBER') ,
 EXZ_IDENTIFIER varchar(3) SET("ui_mapping_text"='SS_AI_SP_HISTORY.EXZ_IDENTIFIER') ,
 LDZ_IDENTIFIER varchar(4) SET("ui_mapping_text"='SS_AI_SP_HISTORY.LDZ_IDENTIFIER') ,
 NDM_SOQ int SET("ui_mapping_text"='SS_AI_SP_HISTORY.NDM_SOQ') ,
 WHOLLY_DM_IND varchar(1) SET("ui_mapping_text"='SS_AI_SP_HISTORY.WHOLLY_DM_IND') ,
 NUM_DATALOGGERS int SET("ui_mapping_text"='SS_AI_SP_HISTORY.NUM_DATALOGGERS') ,
 END_DATE datetime SET("ui_mapping_text"='SS_AI_SP_HISTORY.END_DATE') ,
 SPO_BOTTOM_STP_SOQ decimal(24,11) SET("ui_mapping_text"='SS_AI_SP_HISTORY.SPO_BOTTOM_STP_SOQ') ,
 MRF_CODE varchar(1) SET("ui_mapping_text"='SS_AI_SP_HISTORY.MRF_CODE') ,
 NUM_INTRPTBL_DAYS int SET("ui_mapping_text"='SS_AI_SP_HISTORY.NUM_INTRPTBL_DAYS') ,
 ORG_ID decimal(10,0) SET("ui_mapping_text"='SS_AI_SP_HISTORY.ORG_ID') ,
 DS_LOAD_DATE datetime SET("ui_mapping_text"='SS_AI_SP_HISTORY.DS_LOAD_DATE
 
')  ) 
AS SELECT  SS_AI_SP_HISTORY.TRANS_ID
 
 ,
 SS_AI_SP_HISTORY.TRANS_TYPE
 
 ,
 SS_AI_SP_HISTORY.SPO_IDENTIFIER
 
 ,
 SS_AI_SP_HISTORY.START_DATE
 
 ,
 SS_AI_SP_HISTORY.CNF_REFERENCE_NUM
 
 ,
 SS_AI_SP_HISTORY.DM_AQ_TOTAL
 
 ,
 SS_AI_SP_HISTORY.DM_SOQ
 
 ,
 SS_AI_SP_HISTORY.NDM_AQ_TOTAL
 
 ,
 SS_AI_SP_HISTORY.SPT_CODE
 
 ,
 SS_AI_SP_HISTORY.EUC_NUMBER
 
 ,
 SS_AI_SP_HISTORY.EXZ_IDENTIFIER
 
 ,
 SS_AI_SP_HISTORY.LDZ_IDENTIFIER
 
 ,
 SS_AI_SP_HISTORY.NDM_SOQ
 
 ,
 SS_AI_SP_HISTORY.WHOLLY_DM_IND
 
 ,
 SS_AI_SP_HISTORY.NUM_DATALOGGERS
 
 ,
 SS_AI_SP_HISTORY.END_DATE
 
 ,
 SS_AI_SP_HISTORY.SPO_BOTTOM_STP_SOQ
 
 ,
 SS_AI_SP_HISTORY.MRF_CODE
 
 ,
 SS_AI_SP_HISTORY.NUM_INTRPTBL_DAYS
 
 ,
 SS_AI_SP_HISTORY.ORG_ID
 
 ,
 SS_AI_SP_HISTORY.DS_LOAD_DATE
 
 
	FROM SS_AI_SP_HISTORY
	WHERE SS_AI_SP_HISTORY.DS_RUN_ID = $G_Run_Id
and SS_AI_SP_HISTORY.DS_STATUS_FLAG = 'W'
 
 ORDER BY  SS_AI_SP_HISTORY.TRANS_ID asc SET (
"distinct_run_as_separate_process" = 'no',
"group_by_run_as_separate_process" = 'no',
"join_run_as_separate_process" = 'no',
"order_by_run_as_separate_process" = 'no',
"run_as_separate_process" = 'no');
ALGUICOMMENT( x = '19', y = '-113' , ui_display_name = '123',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>194</CX><CY>-185</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '55', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Description', Input_2nd_Column_Name = 'Type', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = '' )
GUID::'4bf1d072-c2ab-4f96-9c3c-d1ce124f8bef' READ TABLE DS_ORA_IPW.STG_OBJECTS.SS_AI_SP_HISTORY  OUTPUT ( SS_AI_SP_HISTORY ) SET (
"array_fetch_size" = '1000',
"cache" = 'yes',
"connection_port" = 'no',
"enable_partitioning" = 'no',
"name" = 'DS_ORA_IPW',
"package_size" = '0',
"reader_is_DB2CDC_table" = 'no',
"reader_overflow_file" = 'overflow_file',
"reader_template_table" = 'no',
"reader_use_overflow_file" = 'no',
"table_weight" = '0');
ALGUICOMMENT( x = '1505', y = '-406' , ui_display_name = '123_1',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>255</CX><CY>-185</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '55', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = '' )
GUID::'0d6ef2a0-f612-4cdb-8142-86e70edbb493' LOAD TABLE DS_ORA_IPW.PST_OBJECTS.PS_AI_SP_HISTORY  INPUT ( Map_CDC_Operation ) SET (
"LDRLiveLoad" = 'no',
"Table_Type" = 'TABLE',
"connection_port" = 'no',
"ldr_configuration_enabled" = 'yes',
"ldr_configurations" = '<?xml version="1.0" encoding="UTF-8"?>
<LDRConfigurations>
<LDRConfiguration database_type="Oracle" database_version="Oracle 10g">
<bulk_col_delimiter>/127</bulk_col_delimiter>
<bulk_ldr_cleanup>yes</bulk_ldr_cleanup>
<bulk_ldr_control_file_name></bulk_ldr_control_file_name>
<bulk_ldr_direct></bulk_ldr_direct>
<bulk_ldr_direct_api>no</bulk_ldr_direct_api>
<bulk_ldr_generate_files_only></bulk_ldr_generate_files_only>
<bulk_ldr_max_bind_array>100000</bulk_ldr_max_bind_array>
<bulk_ldr_max_discards>10</bulk_ldr_max_discards>
<bulk_ldr_mode>append</bulk_ldr_mode>
<bulk_ldr_recoverable></bulk_ldr_recoverable>
<bulk_ldr_rows_per_commit>1000</bulk_ldr_rows_per_commit>
<bulk_ldr_trailing_nullcols></bulk_ldr_trailing_nullcols>
<bulk_ldr_use_control_file></bulk_ldr_use_control_file>
<column_delimiter>,</column_delimiter>
<enable_partitioning>no</enable_partitioning>
<ignore_column_case>yes</ignore_column_case>
<ignore_columns_null>no</ignore_columns_null>
<ignore_columns_value></ignore_columns_value>
<loader_auto_correct>no</loader_auto_correct>
<loader_bulk_load></loader_bulk_load>
<loader_compare_column>compare_by_name</loader_compare_column>
<loader_delete_map></loader_delete_map>
<loader_drop_and_create_table>no</loader_drop_and_create_table>
<loader_insert_map></loader_insert_map>
<loader_insert_select_commit_size>yes</loader_insert_select_commit_size>
<loader_load_choice>append</loader_load_choice>
<loader_num_parallel_loaders>1</loader_num_parallel_loaders>
<loader_overflow_file>overflow_file</loader_overflow_file>
<loader_post_load1></loader_post_load1>
<loader_pre_load1></loader_pre_load1>
<loader_quote_names>0</loader_quote_names>
<loader_transactional>yes</loader_transactional>
<loader_transactional_order>0</loader_transactional_order>
<loader_update_key_columns>no</loader_update_key_columns>
<loader_update_map></loader_update_map>
<loader_use_user_defined_keys>no</loader_use_user_defined_keys>
<loader_xact_size>1000</loader_xact_size>
<oracle_auto_correct_using_merge>yes</oracle_auto_correct_using_merge>
<oracle_bulk_ldr_all_method>None</oracle_bulk_ldr_all_method>
<oracle_bulk_ldr_file_method>None</oracle_bulk_ldr_file_method>
<oracle_bulk_ldr_method>None</oracle_bulk_ldr_method>
<oracle_bulk_ldr_method_api>API</oracle_bulk_ldr_method_api>
<oracle_bulk_ldr_method_file>File</oracle_bulk_ldr_method_file>
<oracle_bulk_ldr_method_none>None</oracle_bulk_ldr_method_none>
<oracle_bulk_ldr_mode_api>append</oracle_bulk_ldr_mode_api>
<oracle_sqlldr_version></oracle_sqlldr_version>
<overflow_file_format>write_data</overflow_file_format>
<text_delimiter></text_delimiter>
<use_overflow_file>no</use_overflow_file>
<use_unicode_varchar>no</use_unicode_varchar>
<sql_text></sql_text>
</LDRConfiguration>
</LDRConfigurations>',
"loader_template_table" = 'no',
"name" = 'DS_ORA_IPW',
"use_unicode_varchar" = 'no');
END

SET("Cache_type" = 'pageable_cache',
"Parallelism_degree" = '0',
"allows_both_input_and_output" = 'yes',
"run_once" = 'no',
"use_dataflow_links" = 'no',
"use_datastore_links" = 'yes',
"validation_xform_exists" = 'no',
"validation_xform_stats" = 'no')
ALGUICOMMENT( x = '-1', y = '-1' , "ActaName_1" = 'Rcolumn_mapping_calculated' , "ActaValue_1" = 'yes' , "ActaName_2" = 'RDate_created' , "ActaValue_2" = 'Fri Mar 12 10:18:09 2010' , "ActaName_3" = 'RSavedAfterCheckOut' , "ActaValue_3" = 'NO' , "ActaName_4" = 'RDate_modified' , "ActaValue_4" = 'Mon May 03 09:29:26 2010' )
CREATE DATAFLOW DF_123::'b37bc29e-1bb9-4e40-b8e4-06b13771b00f' ($P_Input_File_Name varchar(50) IN )

BEGIN 
ALGUICOMMENT( x = '478', y = '-569' , ui_acta_from_schema_0 = 'FF_SS_AI_SP_HISTORY'  , ui_display_name = 'Load_Q' ,UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>238</CX><CY>-166</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-171</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '55', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'191a2294-24a1-4a63-853d-531b2197f12f' CREATE VIEW Load_Q ( TRANS_ID decimal(24,11) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.TRANS_ID
 
') ,
 TRANS_TYPE varchar(1) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.TRANS_TYPE
 
') ,
 TRANS_DATE datetime SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.TRANS_DATE
 
') ,
 SPO_IDENTIFIER decimal(10,0) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.SPO_IDENTIFIER
 
') ,
 START_DATE datetime SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.START_DATE
 
') ,
 CNF_REFERENCE_NUM decimal(9,0) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.CNF_REFERENCE_NUM
 
') ,
 CNF_EFFECTIVE_DATE datetime SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.CNF_EFFECTIVE_DATE
 
') ,
 CNF_END_DATE datetime SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.CNF_END_DATE
 
') ,
 ORG_ID decimal(10,0) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.ORG_ID
 
') ,
 DM_AQ_TOTAL decimal(12,0) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.DM_AQ_TOTAL
 
') ,
 NDM_AQ_TOTAL decimal(12,0) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.NDM_AQ_TOTAL
 
') ,
 SPT_CODE varchar(4) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.SPT_CODE
 
') ,
 EUC_NUMBER decimal(4,0) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.EUC_NUMBER
 
') ,
 EUC_EFFECTIVE_DATE datetime SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.EUC_EFFECTIVE_DATE
 
') ,
 EXZ_IDENTIFIER varchar(3) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.EXZ_IDENTIFIER
 
') ,
 LDZ_IDENTIFIER varchar(4) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.LDZ_IDENTIFIER
 
') ,
 CNF_SHIPPER_REF varchar(30) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.CNF_SHIPPER_REF
 
') ,
 DM_SOQ decimal(8,0) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.DM_SOQ
 
') ,
 NDM_SOQ decimal(8,0) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.NDM_SOQ
 
') ,
 MRF_CODE varchar(1) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.MRF_CODE
 
') ,
 TRANSCO_METER_READ varchar(1) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.TRANSCO_METER_READ
 
') ,
 WHOLLY_DM_IND varchar(1) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.WHOLLY_DM_IND
 
') ,
 NUM_DATALOGGERS decimal(3,0) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.NUM_DATALOGGERS
 
') ,
 END_DATE datetime SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.END_DATE
 
') ,
 VOLUNTARY_INCREASE varchar(1) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.VOLUNTARY_INCREASE
 
') ,
 NUM_INTRPTBL_DAYS int SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.NUM_INTRPTBL_DAYS
 
') ,
 CMPTTN_EFCTV_DATE datetime SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.CMPTTN_EFCTV_DATE
 
') ,
 SPO_BOTTOM_STP_SOQ decimal(24,11) SET("ui_mapping_text"='FF_SS_AI_SP_HISTORY.SPO_BOTTOM_STP_SOQ
 
') ,
 DS_LOAD_DATE datetime SET("ui_mapping_text"='$G_Sysdate
 
') ,
 DS_RUN_ID int SET("ui_mapping_text"='$G_Run_Id 
 
') ,
 DS_STATUS_FLAG varchar(1) SET("ui_mapping_text"='\'W\'
 
')  ) 
AS SELECT  FF_SS_AI_SP_HISTORY.TRANS_ID
 
 ,
 FF_SS_AI_SP_HISTORY.TRANS_TYPE
 
 ,
 FF_SS_AI_SP_HISTORY.TRANS_DATE
 
 ,
 FF_SS_AI_SP_HISTORY.SPO_IDENTIFIER
 
 ,
 FF_SS_AI_SP_HISTORY.START_DATE
 
 ,
 FF_SS_AI_SP_HISTORY.CNF_REFERENCE_NUM
 
 ,
 FF_SS_AI_SP_HISTORY.CNF_EFFECTIVE_DATE
 
 ,
 FF_SS_AI_SP_HISTORY.CNF_END_DATE
 
 ,
 FF_SS_AI_SP_HISTORY.ORG_ID
 
 ,
 FF_SS_AI_SP_HISTORY.DM_AQ_TOTAL
 
 ,
 FF_SS_AI_SP_HISTORY.NDM_AQ_TOTAL
 
 ,
 FF_SS_AI_SP_HISTORY.SPT_CODE
 
 ,
 FF_SS_AI_SP_HISTORY.EUC_NUMBER
 
 ,
 FF_SS_AI_SP_HISTORY.EUC_EFFECTIVE_DATE
 
 ,
 FF_SS_AI_SP_HISTORY.EXZ_IDENTIFIER
 
 ,
 FF_SS_AI_SP_HISTORY.LDZ_IDENTIFIER
 
 ,
 FF_SS_AI_SP_HISTORY.CNF_SHIPPER_REF
 
 ,
 FF_SS_AI_SP_HISTORY.DM_SOQ
 
 ,
 FF_SS_AI_SP_HISTORY.NDM_SOQ
 
 ,
 FF_SS_AI_SP_HISTORY.MRF_CODE
 
 ,
 FF_SS_AI_SP_HISTORY.TRANSCO_METER_READ
 
 ,
 FF_SS_AI_SP_HISTORY.WHOLLY_DM_IND
 
 ,
 FF_SS_AI_SP_HISTORY.NUM_DATALOGGERS
 
 ,
 FF_SS_AI_SP_HISTORY.END_DATE
 
 ,
 FF_SS_AI_SP_HISTORY.VOLUNTARY_INCREASE
 
 ,
 FF_SS_AI_SP_HISTORY.NUM_INTRPTBL_DAYS
 
 ,
 FF_SS_AI_SP_HISTORY.CMPTTN_EFCTV_DATE
 
 ,
 FF_SS_AI_SP_HISTORY.SPO_BOTTOM_STP_SOQ
 
 ,
 $G_Sysdate
 
 ,
 $G_Run_Id
 
 ,
 'W'
 
 
	FROM FF_SS_AI_SP_HISTORY SET (
"distinct_run_as_separate_process" = 'no',
"group_by_run_as_separate_process" = 'no',
"join_run_as_separate_process" = 'no',
"order_by_run_as_separate_process" = 'no',
"run_as_separate_process" = 'no');
ALGUICOMMENT( x = '1016', y = '-569' , ui_display_name = '123',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>255</CX><CY>-185</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '99', InputSchema_WidthProp = '39', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = '' )
GUID::'7ffc57ba-8a39-4938-aa0a-c06ee48b9bb7' LOAD TABLE DS_ORA_IPW.STG_OBJECTS.SS_AI_SP_HISTORY  INPUT ( Load_Q ) SET (
"LDRLiveLoad" = 'no',
"Table_Type" = 'TABLE',
"connection_port" = 'no',
"ldr_configuration_enabled" = 'yes',
"ldr_configurations" = '<?xml version="1.0" encoding="UTF-8"?>
<LDRConfigurations>
<LDRConfiguration database_type="Oracle" database_version="Oracle 10g">
<bulk_col_delimiter>/127</bulk_col_delimiter>
<bulk_ldr_cleanup>yes</bulk_ldr_cleanup>
<bulk_ldr_control_file_name></bulk_ldr_control_file_name>
<bulk_ldr_direct></bulk_ldr_direct>
<bulk_ldr_direct_api>no</bulk_ldr_direct_api>
<bulk_ldr_generate_files_only></bulk_ldr_generate_files_only>
<bulk_ldr_max_bind_array>100000</bulk_ldr_max_bind_array>
<bulk_ldr_max_discards>10</bulk_ldr_max_discards>
<bulk_ldr_mode>append</bulk_ldr_mode>
<bulk_ldr_recoverable></bulk_ldr_recoverable>
<bulk_ldr_rows_per_commit>1000</bulk_ldr_rows_per_commit>
<bulk_ldr_trailing_nullcols></bulk_ldr_trailing_nullcols>
<bulk_ldr_use_control_file></bulk_ldr_use_control_file>
<column_delimiter>,</column_delimiter>
<enable_partitioning>no</enable_partitioning>
<ignore_column_case>yes</ignore_column_case>
<ignore_columns_null>no</ignore_columns_null>
<ignore_columns_value></ignore_columns_value>
<loader_auto_correct>no</loader_auto_correct>
<loader_bulk_load></loader_bulk_load>
<loader_compare_column>compare_by_name</loader_compare_column>
<loader_delete_map></loader_delete_map>
<loader_drop_and_create_table>no</loader_drop_and_create_table>
<loader_insert_map></loader_insert_map>
<loader_insert_select_commit_size>yes</loader_insert_select_commit_size>
<loader_load_choice>append</loader_load_choice>
<loader_num_parallel_loaders>1</loader_num_parallel_loaders>
<loader_overflow_file>overflow_file</loader_overflow_file>
<loader_post_load1></loader_post_load1>
<loader_pre_load1></loader_pre_load1>
<loader_quote_names>0</loader_quote_names>
<loader_transactional>yes</loader_transactional>
<loader_transactional_order>0</loader_transactional_order>
<loader_update_key_columns>no</loader_update_key_columns>
<loader_update_map></loader_update_map>
<loader_use_user_defined_keys>no</loader_use_user_defined_keys>
<loader_xact_size>1000</loader_xact_size>
<oracle_auto_correct_using_merge>yes</oracle_auto_correct_using_merge>
<oracle_bulk_ldr_all_method>None</oracle_bulk_ldr_all_method>
<oracle_bulk_ldr_file_method>None</oracle_bulk_ldr_file_method>
<oracle_bulk_ldr_method>None</oracle_bulk_ldr_method>
<oracle_bulk_ldr_method_api>API</oracle_bulk_ldr_method_api>
<oracle_bulk_ldr_method_file>File</oracle_bulk_ldr_method_file>
<oracle_bulk_ldr_method_none>None</oracle_bulk_ldr_method_none>
<oracle_bulk_ldr_mode_api>append</oracle_bulk_ldr_mode_api>
<oracle_sqlldr_version></oracle_sqlldr_version>
<overflow_file_format>write_data</overflow_file_format>
<text_delimiter></text_delimiter>
<use_overflow_file>no</use_overflow_file>
<use_unicode_varchar>no</use_unicode_varchar>
<sql_text></sql_text>
</LDRConfiguration>
</LDRConfigurations>',
"loader_template_table" = 'no',
"name" = 'DS_ORA_IPW',
"use_unicode_varchar" = 'no');
ALGUICOMMENT( x = '56', y = '-197' , ui_display_name = 'File_123',UI_DATA_XML='<UIDATA><MAINICON><LOCATION><X>0</X><Y>0</Y></LOCATION><SIZE><CX>194</CX><CY>-185</CY></SIZE></MAINICON><DESCRIPTION><LOCATION><X>0</X><Y>-175</Y></LOCATION><SIZE><CX>200</CX><CY>200</CY></SIZE><VISIBLE>0</VISIBLE></DESCRIPTION></UIDATA>', UpperContainer_HeightProp = '85', InputSchema_WidthProp = '50', Input_Width_Description = '130', Output_Width_Decsription = '130', Input_Width_Type = '85', Output_Width_Type = '80', Output_Width_Mapping = '85', Input_Column_Width_3 = '100', Output_Column_Width_4 = '100', Input_Column_Width_4 = '100', Output_Column_Width_5 = '100', Input_1st_Column_Name = 'Type', Input_2nd_Column_Name = 'Description', Input_Column_Name_3 = 'Content_Type', Input_Column_Name_4 = 'Business_Name', Output_1st_Column_Name = 'Type', Output_2nd_Column_Name = 'Mapping', Output_3rd_Column_Name = 'Description', Output_Column_Name_4 = 'Content_Type', Output_Column_Name_5 = 'Business_Name' )
GUID::'cfc727b5-a3a6-46c2-a32a-34706d8a148b' READ FILE FF_SS_AI_SP_HISTORY."$P_Input_File_Name"  OUTPUT ( FF_SS_AI_SP_HISTORY ) SET (
"adaptable" = 'no',
"cache" = 'yes',
"connection_port" = 'no',
"file_location" = 'job_server',
"ignore_row_markers" = 'A00;Z99',
"name" = 'FF_SS_AI_SP_HISTORY',
"reader_capture_data_conversion_errors" = 'yes',
"reader_capture_row_format_errors" = 'yes',
"reader_error_file_name" = '$G_Error_Filename',
"reader_error_file_root_dir" = '$G_Error_Log_Dir',
"reader_filename_col" = 'DI_FILENAME',
"reader_filename_col_size" = '100',
"reader_filename_only" = 'no',
"reader_include_filename" = 'no',
"reader_log_data_conversion_warnings" = 'yes',
"reader_log_row_format_warnings" = 'yes',
"reader_maximum_warnings_to_log" = '-99',
"reader_write_error_rows_to_file" = 'yes',
"root_dir" = '$G_Inbox_Dir',
"table_weight" = '0',
"transfer_custom" = 'no');
END

SET("Cache_type" = 'pageable_cache',
"Parallelism_degree" = '0',
"allows_both_input_and_output" = 'yes',
"run_once" = 'no',
"use_dataflow_links" = 'no',
"use_datastore_links" = 'yes',
"validation_xform_exists" = 'no',
"validation_xform_stats" = 'no')