BI Data Extract - Revision history

Anw: Updated

2025-11-04T10:13:08Z

Updated

← Older revision		Revision as of 12:13, 4 November 2025
Line 23:		Line 23:
	\|TABLE_NAME		\|TABLE_NAME
	\|120		\|120
	\|The table being extracted. For more complex queries, ~~use~~ a view name instead. Alternatively, use a sub-query for the select e.g. <code>where oms_ref in (select oms_ref ...</code>~~. This must be unique~~.		\|The table being extracted. This must be unique. For more complex queries with multiple tables, consider using a view name instead.

			Where the extract requires another table linked to selected the data (for example, SCH_ORD_REFERENCE), these may be listed in the table names and linked in the here clause, as long as they do not exceed 120 characters. For example table name "<code>sch_ord_reference, sch_ord</code>" and where clause "<code> WHERE sch_ord_reference.oms_ref = sch_ord.oms_ref AND ...</code>". Ensure that the table names match EXACTLY on the detail record, and be wary that field names will then need to be explicitly identified by the table you want them from.

			Alternatively, use a sub-query for the select e.g. <code>where oms_ref in (select oms_ref ...</code>.
	\|-		\|-
	\|DELIMITER		\|DELIMITER

Anw: Minor

2025-11-04T10:05:29Z

Minor

← Older revision		Revision as of 12:05, 4 November 2025
Line 27:		Line 27:
	\|DELIMITER		\|DELIMITER
	\|40		\|40
	\|~~<nowiki>~~The delimiter character. ~~Use~~ '\|\|'®'\|\|' for a unique separator character. Not relevant if using tab delimiting.~~</nowiki>~~		\|The delimiter character. This must be enclosed in PL/SQL syntax to concatenate the values. For example, use <code><nowiki>'\|\|'®'\|\|'</nowiki></code> for a unique separator character. Not relevant if using tab delimiting, as the package will instead use CHR (9).
	\|-		\|-
	\|EXTRACT_OUTPUT_DIRECTORY		\|EXTRACT_OUTPUT_DIRECTORY
	\|200		\|200
	\|Usually /webint/dbname/export or /webint/dbname/MIS		\|Usually <code>/webint/dbname/export</code> or <code>/webint/dbname/MIS</code>
	\|-		\|-
	\|DATABASE_SOURCE		\|DATABASE_SOURCE
Line 54:		Line 54:
	\|}		\|}
	{{Note\|1=* For Where Clause:		{{Note\|1=* For Where Clause:
	** Standing data tables should be always be exported. So clause should be " WHERE 1=1"		** Standing data tables should be always be exported. So clause should be "<code> WHERE 1=1</code>"
	** Transactional data should export any data created or updated in the last calendar day or days (depending on customer requirements). So " WHERE ((created_date BETWEEN SYSDATE-4 AND SYSDATE) OR (updated_date BETWEEN SYSDATE-4 AND SYSDATE))". Date column names are dependent on the table (or tables) being selected.}}		** Transactional data should export any data created or updated in the last calendar day or days (depending on customer requirements). So "<code> WHERE ((created_date BETWEEN SYSDATE-4 AND SYSDATE) OR (updated_date BETWEEN SYSDATE-4 AND SYSDATE))</code>". Date column names are dependent on the table (or tables) being selected.}}


Line 74:		Line 74:
	\|DATA_TYPE_SIZE		\|DATA_TYPE_SIZE
	\|120		\|120
	\|the data type and length e.g. VARCHAR2(40), NUMBER(5), DATE		\|the data type and length e.g. <code>VARCHAR2(40), NUMBER(5), DATE</code>
	\|-		\|-
	\|COLUMN_POSITION		\|COLUMN_POSITION
Line 82:		Line 82:
	\|FORMATING_REQUIREMENT		\|FORMATING_REQUIREMENT
	\|240		\|240
	\|Usually used for dates e.g. TO_CHAR(START_TIME,'RRRR-MM-DD HH24:MI')		\|Usually used for dates e.g. <code>TO_CHAR(START_TIME,'RRRR-MM-DD HH24:MI')</code>
	\|}		\|}
	{{Note\|* Up to 50 columns only.		{{Note\|* Up to 50 columns only.
	* If more columns are required, define another extract for the additional columns.		* If more columns are required, define another extract for the additional columns.
	* Note that the table name must be unique, so if multiple extracts are required from the same table, this would need to be aliased e.g. "SCH_TRIP as st2". Note that this will need to be less than 120 characters in length. {{Warning}} This may not work.}}		* Note that the table name must be unique, so if multiple extracts are required from the same table, this would need to be aliased e.g. "<code>SCH_TRIP as st2</code>". Note that this will need to be less than 120 characters in length. {{Warning}} This may not work.}}


Line 165:		Line 165:
	First, create directory from PLSQL Developer, from Directories object/create new		First, create directory from PLSQL Developer, from Directories object/create new

	Usually /webint/dbname/export or /webint/dbname/MIS		Usually <code>/webint/dbname/export</code> or <code>/webint/dbname/MIS</code>

	Then grant permissions for MTS_USER to access (EDI user)		Then grant permissions for MTS_USER to access (EDI user)

Anw: Updated

2025-11-04T09:32:32Z

Updated

← Older revision		Revision as of 11:32, 4 November 2025
Line 23:		Line 23:
	\|TABLE_NAME		\|TABLE_NAME
	\|120		\|120
	\|The table being extracted. For more complex queries, use a view name instead. Alternatively, use a sub-query for the select e.g. <code>where oms_ref in (select oms_ref ...</code>		\|The table being extracted. For more complex queries, use a view name instead. Alternatively, use a sub-query for the select e.g. <code>where oms_ref in (select oms_ref ...</code>. This must be unique.
	\|-		\|-
	\|DELIMITER		\|DELIMITER
Line 85:		Line 85:
	\|}		\|}
	{{Note\|* Up to 50 columns only.		{{Note\|* Up to 50 columns only.
	* If more columns are required, define another extract for the additional columns.}}		* If more columns are required, define another extract for the additional columns.
			* Note that the table name must be unique, so if multiple extracts are required from the same table, this would need to be aliased e.g. "SCH_TRIP as st2". Note that this will need to be less than 120 characters in length. {{Warning}} This may not work.}}

Anw: Updated

2025-11-04T09:26:15Z

Updated

← Older revision		Revision as of 11:26, 4 November 2025
Line 23:		Line 23:
	\|TABLE_NAME		\|TABLE_NAME
	\|120		\|120
	\|The table being extracted. For more complex queries, use a view name instead.		\|The table being extracted. For more complex queries, use a view name instead. Alternatively, use a sub-query for the select e.g. <code>where oms_ref in (select oms_ref ...</code>
	\|-		\|-
	\|DELIMITER		\|DELIMITER
	\|40		\|40
	\|<nowiki>The delimiter character. Use '\|\|'®'\|\|' for tab ~~replacement~~</nowiki>		\|<nowiki>The delimiter character. Use '\|\|'®'\|\|' for a unique separator character. Not relevant if using tab delimiting.</nowiki>
	\|-		\|-
	\|EXTRACT_OUTPUT_DIRECTORY		\|EXTRACT_OUTPUT_DIRECTORY
Line 55:		Line 55:
	{{Note\|1=* For Where Clause:		{{Note\|1=* For Where Clause:
	** Standing data tables should be always be exported. So clause should be " WHERE 1=1"		** Standing data tables should be always be exported. So clause should be " WHERE 1=1"
	** Transactional data should export any data created or updated in the last calendar day or days (depending on customer requirements). So " WHERE ((created_date BETWEEN SYSDATE-1 AND SYSDATE-1) OR (updated_date BETWEEN SYSDATE-1 AND SYSDATE-1))". Date column names are dependent on the table (or tables) being selected.}}		** Transactional data should export any data created or updated in the last calendar day or days (depending on customer requirements). So " WHERE ((created_date BETWEEN SYSDATE-4 AND SYSDATE) OR (updated_date BETWEEN SYSDATE-4 AND SYSDATE))". Date column names are dependent on the table (or tables) being selected.}}


Line 84:		Line 84:
	\|Usually used for dates e.g. TO_CHAR(START_TIME,'RRRR-MM-DD HH24:MI')		\|Usually used for dates e.g. TO_CHAR(START_TIME,'RRRR-MM-DD HH24:MI')
	\|}		\|}
	{{Note\|* Up to 50 columns only.}}		{{Note\|* Up to 50 columns only.
			* If more columns are required, define another extract for the additional columns.}}

Anw: Added details of the MIS tables and columns definition.

2025-10-31T14:20:42Z

Added details of the MIS tables and columns definition.

@@ Line 2: / Line 2: @@
 This process has already been documented from a user perspective in [[ctms:BI_Data_Extract_Guide]]
 ==Configuration==
@@ Line 9: / Line 8: @@
 Only active files are generated.
-The columns (up to a maximum of 50) are defined in table mis_extract_detail
+The columns (up to a maximum of 50) are defined in table mis_extract_detail.
+{| class="wikitable"
-<pre>
+Sample SQL<pre>
 -- What denotes the files to be created
 select * from mis_extract_header
@@ Line 55: / Line 126: @@
 order by table_name, column_position
 </pre>
 == Parameters ==
@@ Line 61: / Line 131: @@
 System parameters control the extract.
-* MIS_CHAR_SET	WE8ISO8859P1
+* MIS_CHAR_SET - WE8ISO8859P1
-* MIS_DELETED_DAYS	30
+* MIS_DELETED_DAYS - 30
-* MIS_DELETE_REQUIRED	Y
+* MIS_DELETE_REQUIRED - Y
-* MIS_FTP_DESTINATION_DIRECTORY	- if a subdirectory is required, define it here.
+* MIS_FTP_DESTINATION_DIRECTORY - if a subdirectory is required, define it here.
-* MIS_FTP_DESTINATION_IP_ADDRESS	- an IP address or URLL for the FTP or SFTP
+* MIS_FTP_DESTINATION_IP_ADDRESS - an IP address or URLL for the FTP or SFTP
-* MIS_FTP_DESTINATION_PASSWORD	- the password for the FTP/SFTP server
+* MIS_FTP_DESTINATION_PASSWORD - the password for the FTP/SFTP server
-* MIS_FTP_DESTINATION_PORT	- the port, 22 for SFTP, 23 for FTP
+* MIS_FTP_DESTINATION_PORT - the port, 22 for SFTP, 23 for FTP
-* MIS_FTP_DESTINATION_USERNAME	- the username  for the FTP/SFTP server
+* MIS_FTP_DESTINATION_USERNAME - the username  for the FTP/SFTP server
-* MIS_FTP_PROTOCOL	- FTP or SFTP
+* MIS_FTP_PROTOCOL - FTP or SFTP
-* MIS_TAB_DELIMITER	 - Y if you want the files generated to be tab delimited.
+* MIS_TAB_DELIMITER - Y if you want the files generated to be tab delimited.
 If using SFTP, define also the level of logging:
-* SFTP_LOG_LEVEL	- 0 to 3.
+* SFTP_LOG_LEVEL - 0 to 3.
@@ Line 89: / Line 159: @@
 </pre>
 ==Directory access==
@@ Line 97: / Line 166: @@
 Then grant permissions for MTS_USER to access (EDI user)
@@ Line 115: / Line 185: @@
 </pre>
 == Packages ==
@@ Line 125: / Line 194: @@
 select SYSDATE FROM DUAL
 </pre>
 == Auditing and Troubleshooting ==
@@ Line 170: / Line 238: @@
 </pre>
-<noinclude>[[Category:CTMS]][[Category:Technical Guides]][[Category:Oracle]]</noinclude>
+<noinclude>

Anw: Initial Creation

2025-10-31T11:51:52Z

Initial Creation

New page

This page is intended to describe the configuration of the MIS/BI Extracts from a technical perspective.

This process has already been documented from a user perspective in [[ctms:BI_Data_Extract_Guide]]

==Configuration==

The files that will be created are defined in table mis_extract_header

Only active files are generated.

The columns (up to a maximum of 50) are defined in table mis_extract_detail

Sample SQL

<pre>
-- What denotes the files to be created
select * from mis_extract_header
WHERE active_flag = 'Y'
FOR UPDATE

select mih.extract_filename || '.' || mih.extract_filename_extension "File_Name"
, Table_Name
, Case WHERE_CLAUSE WHEN ' WHERE 1=1' THEN 'All Data' ELSE 'Daily Data' END "Contains"
from mis_extract_header mih
WHERE active_flag = 'Y'

select * from mis_extract_header
FOR UPDATE

select * from mis_extract_header
WHERE WHERE_CLAUSE NOT LIKE '%1=1%'
AND ACTIVE_FLAG = 'Y'

-- Set active the ones that you want
UPDATE mis_extract_header
SET active_flag = 'N'
WHERE extract_filename <> 'Z_RES_CARRIER_TYPE'

-- Set the output directory and database source
UPDATE mis_extract_header
SET EXTRACT_OUTPUT_DIRECTORY = '/webint/ststtst/export',
DATABASE_SOURCE = 'ststtst';

-- Set up the fields being exported
select * from mis_extract_detail
where table_name like 'SCH_ORD_ITEMS%'
order by table_name, column_position

select table_name, column_name, data_type_size, column_position
from mis_extract_detail
where table_name like 'SCH_ORD_ITEMS%'
order by table_name, column_position
</pre>

== Parameters ==

System parameters control the extract.

* MIS_CHAR_SET WE8ISO8859P1
* MIS_DELETED_DAYS 30
* MIS_DELETE_REQUIRED Y
* MIS_FTP_DESTINATION_DIRECTORY - if a subdirectory is required, define it here.
* MIS_FTP_DESTINATION_IP_ADDRESS - an IP address or URLL for the FTP or SFTP
* MIS_FTP_DESTINATION_PASSWORD - the password for the FTP/SFTP server
* MIS_FTP_DESTINATION_PORT - the port, 22 for SFTP, 23 for FTP
* MIS_FTP_DESTINATION_USERNAME - the username for the FTP/SFTP server
* MIS_FTP_PROTOCOL - FTP or SFTP
* MIS_TAB_DELIMITER - Y if you want the files generated to be tab delimited.

If using SFTP, define also the level of logging:
* SFTP_LOG_LEVEL - 0 to 3.

Sample SQL:
<pre>
-- Set up MIS parameters
SELECT * FROM ADM_SYSTEM_PARAM
WHERE PARAM_NAME LIKE 'MIS%'
FOR UPDATE

SELECT * FROM ADM_SYSTEM_PARAM
WHERE PARAM_NAME LIKE 'SFTP%'
FOR UPDATE

</pre>

==Directory access==
First, create directory from PLSQL Developer, from Directories object/create new

Usually /webint/dbname/export or /webint/dbname/MIS

Then grant permissions for MTS_USER to access (EDI user)

For FTP/SFTP destinations you will need to set up the fingerprint

Sample SQL:
<pre>
-- grant permission for MTS_USER to access (EDI user)
GRANT READ,WRITE ON DIRECTORY EXPORT TO MTS_USER;

BEGIN
dp_sftp.open_connection( i_host => 'the host', i_trust_server => true );
dp_sftp.close_connection;
END;

-- See the fingerprint
select * from sftp_known_hosts

</pre>

== Packages ==
The package to send MIS/BI data is DP_MIS

Sample SQL:
<pre>
select DP_MIS.GET_PACKAGE_VERSION from dual
select SYSDATE FROM DUAL
</pre>

== Auditing and Troubleshooting ==

Sample SQL:
<pre>
-- Audit for SFTP
select * from adm_log
--WHERE PROG_NAME IN ('DP_SFTP')
where date_created <= TO_DATE('2025-10-31 07:20:00', 'YYYY-MM-DD HH24:MI:SS')
AND PROG_NAME NOT IN ('TRM', 'PAR')
-- where err_type <> 'DEBUG' AND STMT < 2255004935 -- PROG_NAME IN ('DP_SFTP')
order by stmt desc
order by adm_log.date_created desc

-- Audit log from the MIS package
select * from Mis_extract_run_detail
order by last_ran desc

-- Checking job
Select * from DBA_JOBS
where UPPER(what) like '%DAILY_EXTRACTS%'

-- When did it last run?
-- Check the last run date/time on the jobs table to the sysdate
select sysdate from dual;

-- Run the job now
DECLARE
g_process_name edi_process_header.process_name%TYPE;
BEGIN
g_process_name := 'DAILY_EXTRACTS';
DP_REPORTS.P_RUN_PROCESS(g_process_name);
COMMIT;
END;
-- Or just find the job number, and then use PLSQL to run the job from the Jobs list to the left.

-- Jobs not running? Check the following:
select logins from v$instance; -- if not allowed, won't run jobs
select value from dba_scheduler_global_attribute where attribute_name='SCHEDULER_DISABLED' -- if TRUE won't run jobs
select value from v$parameter where name='job_queue_processes'; -- if 0 won't run jobs (most common
alter system set job_queue_processes=20; -- Fix to above issue
</pre>

<noinclude>[[Category:CTMS]][[Category:Technical Guides]][[Category:Oracle]]</noinclude>