datasynth_output/formats/
oracle.rs

1//! Oracle EBS format export.
2//!
3//! Exports data in Oracle E-Business Suite compatible formats:
4//! - GL_JE_HEADERS (Journal Entry Headers)
5//! - GL_JE_LINES (Journal Entry Lines)
6//! - GL_JE_BATCHES (Journal Entry Batches)
7
8use chrono::{Datelike, NaiveDate, Utc};
9use rust_decimal::Decimal;
10use serde::{Deserialize, Serialize};
11use std::collections::HashMap;
12use std::fs::File;
13use std::io::{BufWriter, Write};
14use std::path::Path;
15
16use datasynth_core::error::SynthResult;
17use datasynth_core::models::JournalEntry;
18
19/// Oracle GL_JE_HEADERS record.
20#[derive(Debug, Clone, Serialize, Deserialize)]
21pub struct OracleJeHeader {
22    /// Unique header ID
23    pub je_header_id: u64,
24    /// Ledger ID
25    pub ledger_id: u64,
26    /// Batch ID
27    pub je_batch_id: u64,
28    /// Period name (e.g., "JAN-24")
29    pub period_name: String,
30    /// Journal entry name
31    pub name: String,
32    /// Journal category (e.g., "MANUAL", "ADJUSTMENT")
33    pub je_category: String,
34    /// Journal source (e.g., "MANUAL", "PAYABLES")
35    pub je_source: String,
36    /// Currency code
37    pub currency_code: String,
38    /// Actual flag (A=Actual, B=Budget, E=Encumbrance)
39    pub actual_flag: String,
40    /// Status (P=Posted, U=Unposted)
41    pub status: String,
42    /// Default effective date
43    pub default_effective_date: NaiveDate,
44    /// Description
45    pub description: Option<String>,
46    /// External reference
47    pub external_reference: Option<String>,
48    /// Parent header ID (for reversals)
49    pub parent_je_header_id: Option<u64>,
50    /// Reversal flag
51    pub accrual_rev_flag: Option<String>,
52    /// Running total (debits)
53    pub running_total_dr: Decimal,
54    /// Running total (credits)
55    pub running_total_cr: Decimal,
56    /// Running total accounted (debits)
57    pub running_total_accounted_dr: Decimal,
58    /// Running total accounted (credits)
59    pub running_total_accounted_cr: Decimal,
60    /// Creation date
61    pub creation_date: NaiveDate,
62    /// Created by user ID
63    pub created_by: u64,
64    /// Last update date
65    pub last_update_date: NaiveDate,
66    /// Last updated by user ID
67    pub last_updated_by: u64,
68}
69
70impl Default for OracleJeHeader {
71    fn default() -> Self {
72        let now = Utc::now().date_naive();
73        Self {
74            je_header_id: 0,
75            ledger_id: 1,
76            je_batch_id: 0,
77            period_name: String::new(),
78            name: String::new(),
79            je_category: "MANUAL".to_string(),
80            je_source: "MANUAL".to_string(),
81            currency_code: "USD".to_string(),
82            actual_flag: "A".to_string(),
83            status: "P".to_string(),
84            default_effective_date: now,
85            description: None,
86            external_reference: None,
87            parent_je_header_id: None,
88            accrual_rev_flag: None,
89            running_total_dr: Decimal::ZERO,
90            running_total_cr: Decimal::ZERO,
91            running_total_accounted_dr: Decimal::ZERO,
92            running_total_accounted_cr: Decimal::ZERO,
93            creation_date: now,
94            created_by: 0,
95            last_update_date: now,
96            last_updated_by: 0,
97        }
98    }
99}
100
101/// Oracle GL_JE_LINES record.
102#[derive(Debug, Clone, Serialize, Deserialize)]
103pub struct OracleJeLine {
104    /// Unique line ID
105    pub je_line_num: u32,
106    /// Header ID (foreign key)
107    pub je_header_id: u64,
108    /// Ledger ID
109    pub ledger_id: u64,
110    /// Code combination ID (account string)
111    pub code_combination_id: u64,
112    /// Period name
113    pub period_name: String,
114    /// Effective date
115    pub effective_date: NaiveDate,
116    /// Status (P=Posted, U=Unposted)
117    pub status: String,
118    /// Entered debit
119    pub entered_dr: Option<Decimal>,
120    /// Entered credit
121    pub entered_cr: Option<Decimal>,
122    /// Accounted debit (in functional currency)
123    pub accounted_dr: Option<Decimal>,
124    /// Accounted credit (in functional currency)
125    pub accounted_cr: Option<Decimal>,
126    /// Currency code
127    pub currency_code: String,
128    /// Currency conversion rate
129    pub currency_conversion_rate: Option<Decimal>,
130    /// Currency conversion type
131    pub currency_conversion_type: Option<String>,
132    /// Currency conversion date
133    pub currency_conversion_date: Option<NaiveDate>,
134    /// Description
135    pub description: Option<String>,
136    /// Reference columns
137    pub reference_1: Option<String>,
138    pub reference_2: Option<String>,
139    pub reference_3: Option<String>,
140    pub reference_4: Option<String>,
141    pub reference_5: Option<String>,
142    /// Statistical amount
143    pub stat_amount: Option<Decimal>,
144    /// Subledger document sequence ID
145    pub subledger_doc_sequence_id: Option<u64>,
146    /// Attribute columns (DFF)
147    pub attribute1: Option<String>,
148    pub attribute2: Option<String>,
149    pub attribute3: Option<String>,
150    /// Creation date
151    pub creation_date: NaiveDate,
152    /// Created by
153    pub created_by: u64,
154}
155
156impl Default for OracleJeLine {
157    fn default() -> Self {
158        let now = Utc::now().date_naive();
159        Self {
160            je_line_num: 0,
161            je_header_id: 0,
162            ledger_id: 1,
163            code_combination_id: 0,
164            period_name: String::new(),
165            effective_date: now,
166            status: "P".to_string(),
167            entered_dr: None,
168            entered_cr: None,
169            accounted_dr: None,
170            accounted_cr: None,
171            currency_code: "USD".to_string(),
172            currency_conversion_rate: None,
173            currency_conversion_type: None,
174            currency_conversion_date: None,
175            description: None,
176            reference_1: None,
177            reference_2: None,
178            reference_3: None,
179            reference_4: None,
180            reference_5: None,
181            stat_amount: None,
182            subledger_doc_sequence_id: None,
183            attribute1: None,
184            attribute2: None,
185            attribute3: None,
186            creation_date: now,
187            created_by: 0,
188        }
189    }
190}
191
192/// Oracle GL_JE_BATCHES record.
193#[derive(Debug, Clone, Serialize, Deserialize)]
194pub struct OracleJeBatch {
195    /// Batch ID
196    pub je_batch_id: u64,
197    /// Batch name
198    pub name: String,
199    /// Ledger ID
200    pub ledger_id: u64,
201    /// Status (P=Posted, U=Unposted)
202    pub status: String,
203    /// Actual flag
204    pub actual_flag: String,
205    /// Default period name
206    pub default_period_name: String,
207    /// Description
208    pub description: Option<String>,
209    /// Running total debits
210    pub running_total_dr: Decimal,
211    /// Running total credits
212    pub running_total_cr: Decimal,
213    /// Posted date
214    pub posted_date: Option<NaiveDate>,
215    /// Creation date
216    pub creation_date: NaiveDate,
217    /// Created by
218    pub created_by: u64,
219}
220
221impl Default for OracleJeBatch {
222    fn default() -> Self {
223        let now = Utc::now().date_naive();
224        Self {
225            je_batch_id: 0,
226            name: String::new(),
227            ledger_id: 1,
228            status: "P".to_string(),
229            actual_flag: "A".to_string(),
230            default_period_name: String::new(),
231            description: None,
232            running_total_dr: Decimal::ZERO,
233            running_total_cr: Decimal::ZERO,
234            posted_date: Some(now),
235            creation_date: now,
236            created_by: 0,
237        }
238    }
239}
240
241/// Configuration for Oracle export.
242#[derive(Debug, Clone)]
243pub struct OracleExportConfig {
244    /// Ledger ID
245    pub ledger_id: u64,
246    /// Set of Books ID (legacy)
247    pub set_of_books_id: u64,
248    /// Functional currency
249    pub functional_currency: String,
250    /// User ID for created_by/last_updated_by
251    pub user_id: u64,
252    /// Include batches table
253    pub include_batches: bool,
254    /// Account segment separator
255    pub segment_separator: String,
256    /// Number of segments in account combination
257    pub num_segments: usize,
258}
259
260impl Default for OracleExportConfig {
261    fn default() -> Self {
262        Self {
263            ledger_id: 1,
264            set_of_books_id: 1,
265            functional_currency: "USD".to_string(),
266            user_id: 1,
267            include_batches: true,
268            segment_separator: "-".to_string(),
269            num_segments: 6,
270        }
271    }
272}
273
274/// Oracle EBS format exporter.
275pub struct OracleExporter {
276    config: OracleExportConfig,
277    header_counter: u64,
278    line_counter: u64,
279    batch_counter: u64,
280    /// Maps company code + GL account to code_combination_id
281    ccid_map: HashMap<String, u64>,
282    next_ccid: u64,
283}
284
285impl OracleExporter {
286    /// Create a new Oracle exporter.
287    pub fn new(config: OracleExportConfig) -> Self {
288        Self {
289            config,
290            header_counter: 0,
291            line_counter: 0,
292            batch_counter: 0,
293            ccid_map: HashMap::new(),
294            next_ccid: 1000,
295        }
296    }
297
298    /// Get or create a code combination ID for an account.
299    fn get_ccid(&mut self, company_code: &str, gl_account: &str, cost_center: Option<&str>) -> u64 {
300        let key = format!(
301            "{}-{}-{}",
302            company_code,
303            gl_account,
304            cost_center.unwrap_or("0000")
305        );
306        if let Some(&ccid) = self.ccid_map.get(&key) {
307            return ccid;
308        }
309        let ccid = self.next_ccid;
310        self.next_ccid += 1;
311        self.ccid_map.insert(key, ccid);
312        ccid
313    }
314
315    /// Generate Oracle period name from date.
316    fn period_name(date: NaiveDate) -> String {
317        let month = match date.month() {
318            1 => "JAN",
319            2 => "FEB",
320            3 => "MAR",
321            4 => "APR",
322            5 => "MAY",
323            6 => "JUN",
324            7 => "JUL",
325            8 => "AUG",
326            9 => "SEP",
327            10 => "OCT",
328            11 => "NOV",
329            12 => "DEC",
330            _ => "JAN",
331        };
332        format!("{}-{}", month, date.format("%y"))
333    }
334
335    /// Map document type to Oracle category.
336    fn je_category(doc_type: &str) -> String {
337        match doc_type {
338            "SA" => "MANUAL".to_string(),
339            "RE" | "KR" => "PAYABLES".to_string(),
340            "RV" | "DR" => "RECEIVABLES".to_string(),
341            "KZ" => "PAYMENTS".to_string(),
342            "DZ" => "RECEIPTS".to_string(),
343            "AB" | "AA" => "ASSETS".to_string(),
344            _ => "OTHER".to_string(),
345        }
346    }
347
348    /// Map transaction source to Oracle source.
349    fn je_source(source: &str) -> String {
350        match source {
351            "Manual" | "ManualEntry" => "MANUAL".to_string(),
352            "Payables" | "VendorInvoice" => "PAYABLES".to_string(),
353            "Receivables" | "CustomerInvoice" => "RECEIVABLES".to_string(),
354            "Assets" | "Depreciation" => "ASSETS".to_string(),
355            "Inventory" => "INVENTORY".to_string(),
356            _ => "OTHER".to_string(),
357        }
358    }
359
360    /// Convert JournalEntry to Oracle header and lines.
361    pub fn convert(&mut self, je: &JournalEntry) -> (OracleJeHeader, Vec<OracleJeLine>) {
362        self.header_counter += 1;
363        let header_id = self.header_counter;
364        let batch_id = if self.config.include_batches {
365            self.batch_counter
366        } else {
367            0
368        };
369
370        let period_name = Self::period_name(je.header.posting_date);
371
372        // Calculate totals
373        let mut total_dr = Decimal::ZERO;
374        let mut total_cr = Decimal::ZERO;
375        for line in &je.lines {
376            total_dr += line.debit_amount;
377            total_cr += line.credit_amount;
378        }
379
380        let header = OracleJeHeader {
381            je_header_id: header_id,
382            ledger_id: self.config.ledger_id,
383            je_batch_id: batch_id,
384            period_name: period_name.clone(),
385            name: format!("JE-{}", je.header.document_id),
386            je_category: Self::je_category(&je.header.document_type),
387            je_source: Self::je_source(&format!("{:?}", je.header.source)),
388            currency_code: je.header.currency.clone(),
389            actual_flag: "A".to_string(),
390            status: "P".to_string(),
391            default_effective_date: je.header.posting_date,
392            description: je.header.header_text.clone(),
393            external_reference: je.header.reference.clone(),
394            parent_je_header_id: None,
395            accrual_rev_flag: None,
396            running_total_dr: total_dr,
397            running_total_cr: total_cr,
398            running_total_accounted_dr: total_dr * je.header.exchange_rate,
399            running_total_accounted_cr: total_cr * je.header.exchange_rate,
400            creation_date: je.header.created_at.date_naive(),
401            created_by: self.config.user_id,
402            last_update_date: je.header.created_at.date_naive(),
403            last_updated_by: self.config.user_id,
404        };
405
406        let mut lines = Vec::new();
407        for line in &je.lines {
408            self.line_counter += 1;
409            let ccid = self.get_ccid(
410                &je.header.company_code,
411                &line.gl_account,
412                line.cost_center.as_deref(),
413            );
414
415            let oracle_line = OracleJeLine {
416                je_line_num: line.line_number,
417                je_header_id: header_id,
418                ledger_id: self.config.ledger_id,
419                code_combination_id: ccid,
420                period_name: period_name.clone(),
421                effective_date: je.header.posting_date,
422                status: "P".to_string(),
423                entered_dr: if line.debit_amount > Decimal::ZERO {
424                    Some(line.debit_amount)
425                } else {
426                    None
427                },
428                entered_cr: if line.credit_amount > Decimal::ZERO {
429                    Some(line.credit_amount)
430                } else {
431                    None
432                },
433                accounted_dr: if line.debit_amount > Decimal::ZERO {
434                    Some(line.debit_amount * je.header.exchange_rate)
435                } else {
436                    None
437                },
438                accounted_cr: if line.credit_amount > Decimal::ZERO {
439                    Some(line.credit_amount * je.header.exchange_rate)
440                } else {
441                    None
442                },
443                currency_code: je.header.currency.clone(),
444                currency_conversion_rate: if je.header.exchange_rate != Decimal::ONE {
445                    Some(je.header.exchange_rate)
446                } else {
447                    None
448                },
449                currency_conversion_type: if je.header.exchange_rate != Decimal::ONE {
450                    Some("Corporate".to_string())
451                } else {
452                    None
453                },
454                currency_conversion_date: if je.header.exchange_rate != Decimal::ONE {
455                    Some(je.header.posting_date)
456                } else {
457                    None
458                },
459                description: line.line_text.clone(),
460                reference_1: Some(je.header.company_code.clone()),
461                reference_2: Some(line.gl_account.clone()),
462                reference_3: line.cost_center.clone(),
463                reference_4: line.profit_center.clone(),
464                reference_5: je.header.reference.clone(),
465                stat_amount: line.quantity,
466                subledger_doc_sequence_id: None,
467                attribute1: if je.header.is_fraud {
468                    Some("Y".to_string())
469                } else {
470                    None
471                },
472                attribute2: je.header.fraud_type.map(|ft| format!("{ft:?}")),
473                attribute3: je.header.business_process.map(|bp| format!("{bp:?}")),
474                creation_date: je.header.created_at.date_naive(),
475                created_by: self.config.user_id,
476            };
477            lines.push(oracle_line);
478        }
479
480        (header, lines)
481    }
482
483    /// Export journal entries to Oracle format files.
484    pub fn export_to_files(
485        &mut self,
486        entries: &[JournalEntry],
487        output_dir: &Path,
488    ) -> SynthResult<HashMap<String, String>> {
489        std::fs::create_dir_all(output_dir)?;
490
491        let mut output_files = HashMap::new();
492
493        // Export headers
494        let header_path = output_dir.join("gl_je_headers.csv");
495        let lines_path = output_dir.join("gl_je_lines.csv");
496
497        let header_file = File::create(&header_path)?;
498        let mut header_writer = BufWriter::with_capacity(256 * 1024, header_file);
499
500        let lines_file = File::create(&lines_path)?;
501        let mut lines_writer = BufWriter::with_capacity(256 * 1024, lines_file);
502
503        // Write header row
504        writeln!(
505            header_writer,
506            "JE_HEADER_ID,LEDGER_ID,JE_BATCH_ID,PERIOD_NAME,NAME,JE_CATEGORY,JE_SOURCE,\
507            CURRENCY_CODE,ACTUAL_FLAG,STATUS,DEFAULT_EFFECTIVE_DATE,DESCRIPTION,\
508            RUNNING_TOTAL_DR,RUNNING_TOTAL_CR,CREATION_DATE,CREATED_BY"
509        )?;
510
511        writeln!(
512            lines_writer,
513            "JE_LINE_NUM,JE_HEADER_ID,LEDGER_ID,CODE_COMBINATION_ID,PERIOD_NAME,EFFECTIVE_DATE,\
514            STATUS,ENTERED_DR,ENTERED_CR,ACCOUNTED_DR,ACCOUNTED_CR,CURRENCY_CODE,\
515            DESCRIPTION,REFERENCE_1,REFERENCE_2,REFERENCE_3,ATTRIBUTE1,ATTRIBUTE2,CREATION_DATE,CREATED_BY"
516        )?;
517
518        // Start a new batch for this export
519        if self.config.include_batches {
520            self.batch_counter += 1;
521        }
522
523        for je in entries {
524            let (header, lines) = self.convert(je);
525
526            writeln!(
527                header_writer,
528                "{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}",
529                header.je_header_id,
530                header.ledger_id,
531                header.je_batch_id,
532                header.period_name,
533                escape_csv_field(&header.name),
534                header.je_category,
535                header.je_source,
536                header.currency_code,
537                header.actual_flag,
538                header.status,
539                header.default_effective_date,
540                escape_csv_field(&header.description.unwrap_or_default()),
541                header.running_total_dr,
542                header.running_total_cr,
543                header.creation_date,
544                header.created_by,
545            )?;
546
547            for line in lines {
548                writeln!(
549                    lines_writer,
550                    "{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}",
551                    line.je_line_num,
552                    line.je_header_id,
553                    line.ledger_id,
554                    line.code_combination_id,
555                    line.period_name,
556                    line.effective_date,
557                    line.status,
558                    line.entered_dr.map(|d| d.to_string()).unwrap_or_default(),
559                    line.entered_cr.map(|d| d.to_string()).unwrap_or_default(),
560                    line.accounted_dr.map(|d| d.to_string()).unwrap_or_default(),
561                    line.accounted_cr.map(|d| d.to_string()).unwrap_or_default(),
562                    line.currency_code,
563                    escape_csv_field(&line.description.unwrap_or_default()),
564                    line.reference_1.as_deref().unwrap_or(""),
565                    line.reference_2.as_deref().unwrap_or(""),
566                    line.reference_3.as_deref().unwrap_or(""),
567                    line.attribute1.as_deref().unwrap_or(""),
568                    line.attribute2.as_deref().unwrap_or(""),
569                    line.creation_date,
570                    line.created_by,
571                )?;
572            }
573        }
574
575        header_writer.flush()?;
576        lines_writer.flush()?;
577
578        output_files.insert(
579            "gl_je_headers".to_string(),
580            header_path.to_string_lossy().to_string(),
581        );
582        output_files.insert(
583            "gl_je_lines".to_string(),
584            lines_path.to_string_lossy().to_string(),
585        );
586
587        // Export code combinations
588        let ccid_path = output_dir.join("gl_code_combinations.csv");
589        self.export_code_combinations(&ccid_path)?;
590        output_files.insert(
591            "gl_code_combinations".to_string(),
592            ccid_path.to_string_lossy().to_string(),
593        );
594
595        Ok(output_files)
596    }
597
598    /// Export code combinations mapping.
599    fn export_code_combinations(&self, filepath: &Path) -> SynthResult<()> {
600        let file = File::create(filepath)?;
601        let mut writer = BufWriter::with_capacity(256 * 1024, file);
602
603        writeln!(
604            writer,
605            "CODE_COMBINATION_ID,SEGMENT1,SEGMENT2,SEGMENT3,ENABLED_FLAG"
606        )?;
607
608        for (key, ccid) in &self.ccid_map {
609            let parts: Vec<&str> = key.split('-').collect();
610            let segment1 = parts.first().unwrap_or(&"");
611            let segment2 = parts.get(1).unwrap_or(&"");
612            let segment3 = parts.get(2).unwrap_or(&"0000");
613
614            writeln!(writer, "{ccid},{segment1},{segment2},{segment3},Y")?;
615        }
616
617        writer.flush()?;
618        Ok(())
619    }
620}
621
622/// Escape a field for CSV output.
623fn escape_csv_field(field: &str) -> String {
624    if field.contains(',') || field.contains('"') || field.contains('\n') {
625        format!("\"{}\"", field.replace('"', "\"\""))
626    } else {
627        field.to_string()
628    }
629}
630
631#[cfg(test)]
632#[allow(clippy::unwrap_used)]
633mod tests {
634    use super::*;
635    use crate::test_helpers::create_test_je;
636    use rust_decimal::Decimal;
637    use tempfile::TempDir;
638
639    #[test]
640    fn test_period_name_generation() {
641        let date = NaiveDate::from_ymd_opt(2024, 6, 15).unwrap();
642        assert_eq!(OracleExporter::period_name(date), "JUN-24");
643
644        let date = NaiveDate::from_ymd_opt(2024, 1, 1).unwrap();
645        assert_eq!(OracleExporter::period_name(date), "JAN-24");
646
647        let date = NaiveDate::from_ymd_opt(2024, 12, 31).unwrap();
648        assert_eq!(OracleExporter::period_name(date), "DEC-24");
649    }
650
651    #[test]
652    fn test_oracle_exporter_creates_files() {
653        let temp_dir = TempDir::new().unwrap();
654        let config = OracleExportConfig::default();
655        let mut exporter = OracleExporter::new(config);
656
657        let entries = vec![create_test_je()];
658        let result = exporter.export_to_files(&entries, temp_dir.path());
659
660        assert!(result.is_ok());
661        let files = result.unwrap();
662        assert!(files.contains_key("gl_je_headers"));
663        assert!(files.contains_key("gl_je_lines"));
664        assert!(files.contains_key("gl_code_combinations"));
665
666        assert!(temp_dir.path().join("gl_je_headers.csv").exists());
667        assert!(temp_dir.path().join("gl_je_lines.csv").exists());
668        assert!(temp_dir.path().join("gl_code_combinations.csv").exists());
669    }
670
671    #[test]
672    fn test_conversion_produces_balanced_totals() {
673        let config = OracleExportConfig::default();
674        let mut exporter = OracleExporter::new(config);
675        let je = create_test_je();
676
677        let (header, lines) = exporter.convert(&je);
678
679        assert_eq!(header.running_total_dr, header.running_total_cr);
680        assert_eq!(lines.len(), 2);
681
682        let line_total_dr: Decimal = lines.iter().filter_map(|l| l.entered_dr).sum();
683        let line_total_cr: Decimal = lines.iter().filter_map(|l| l.entered_cr).sum();
684        assert_eq!(line_total_dr, line_total_cr);
685    }
686}
datasynth_output/formats/oracle.rs

datasynth_output/formats/
oracle.rs
