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    #[allow(dead_code)] // Reserved for future batch export feature
280    batch_counter: u64,
281    /// Maps company code + GL account to code_combination_id
282    ccid_map: HashMap<String, u64>,
283    next_ccid: u64,
284}
285
286impl OracleExporter {
287    /// Create a new Oracle exporter.
288    pub fn new(config: OracleExportConfig) -> Self {
289        Self {
290            config,
291            header_counter: 0,
292            line_counter: 0,
293            batch_counter: 0,
294            ccid_map: HashMap::new(),
295            next_ccid: 1000,
296        }
297    }
298
299    /// Get or create a code combination ID for an account.
300    fn get_ccid(&mut self, company_code: &str, gl_account: &str, cost_center: Option<&str>) -> u64 {
301        let key = format!(
302            "{}-{}-{}",
303            company_code,
304            gl_account,
305            cost_center.unwrap_or("0000")
306        );
307        if let Some(&ccid) = self.ccid_map.get(&key) {
308            return ccid;
309        }
310        let ccid = self.next_ccid;
311        self.next_ccid += 1;
312        self.ccid_map.insert(key, ccid);
313        ccid
314    }
315
316    /// Generate Oracle period name from date.
317    fn period_name(date: NaiveDate) -> String {
318        let month = match date.month() {
319            1 => "JAN",
320            2 => "FEB",
321            3 => "MAR",
322            4 => "APR",
323            5 => "MAY",
324            6 => "JUN",
325            7 => "JUL",
326            8 => "AUG",
327            9 => "SEP",
328            10 => "OCT",
329            11 => "NOV",
330            12 => "DEC",
331            _ => "JAN",
332        };
333        format!("{}-{}", month, date.format("%y"))
334    }
335
336    /// Map document type to Oracle category.
337    fn je_category(doc_type: &str) -> String {
338        match doc_type {
339            "SA" => "MANUAL".to_string(),
340            "RE" | "KR" => "PAYABLES".to_string(),
341            "RV" | "DR" => "RECEIVABLES".to_string(),
342            "KZ" => "PAYMENTS".to_string(),
343            "DZ" => "RECEIPTS".to_string(),
344            "AB" | "AA" => "ASSETS".to_string(),
345            _ => "OTHER".to_string(),
346        }
347    }
348
349    /// Map transaction source to Oracle source.
350    fn je_source(source: &str) -> String {
351        match source {
352            "Manual" | "ManualEntry" => "MANUAL".to_string(),
353            "Payables" | "VendorInvoice" => "PAYABLES".to_string(),
354            "Receivables" | "CustomerInvoice" => "RECEIVABLES".to_string(),
355            "Assets" | "Depreciation" => "ASSETS".to_string(),
356            "Inventory" => "INVENTORY".to_string(),
357            _ => "OTHER".to_string(),
358        }
359    }
360
361    /// Convert JournalEntry to Oracle header and lines.
362    pub fn convert(&mut self, je: &JournalEntry) -> (OracleJeHeader, Vec<OracleJeLine>) {
363        self.header_counter += 1;
364        let header_id = self.header_counter;
365
366        let period_name = Self::period_name(je.header.posting_date);
367
368        // Calculate totals
369        let mut total_dr = Decimal::ZERO;
370        let mut total_cr = Decimal::ZERO;
371        for line in &je.lines {
372            total_dr += line.debit_amount;
373            total_cr += line.credit_amount;
374        }
375
376        let header = OracleJeHeader {
377            je_header_id: header_id,
378            ledger_id: self.config.ledger_id,
379            je_batch_id: 0, // Set later if batching
380            period_name: period_name.clone(),
381            name: format!("JE-{}", je.header.document_id),
382            je_category: Self::je_category(&je.header.document_type),
383            je_source: Self::je_source(&format!("{:?}", je.header.source)),
384            currency_code: je.header.currency.clone(),
385            actual_flag: "A".to_string(),
386            status: "P".to_string(),
387            default_effective_date: je.header.posting_date,
388            description: je.header.header_text.clone(),
389            external_reference: je.header.reference.clone(),
390            parent_je_header_id: None,
391            accrual_rev_flag: None,
392            running_total_dr: total_dr,
393            running_total_cr: total_cr,
394            running_total_accounted_dr: total_dr * je.header.exchange_rate,
395            running_total_accounted_cr: total_cr * je.header.exchange_rate,
396            creation_date: je.header.created_at.date_naive(),
397            created_by: self.config.user_id,
398            last_update_date: je.header.created_at.date_naive(),
399            last_updated_by: self.config.user_id,
400        };
401
402        let mut lines = Vec::new();
403        for line in &je.lines {
404            self.line_counter += 1;
405            let ccid = self.get_ccid(
406                &je.header.company_code,
407                &line.gl_account,
408                line.cost_center.as_deref(),
409            );
410
411            let oracle_line = OracleJeLine {
412                je_line_num: line.line_number,
413                je_header_id: header_id,
414                ledger_id: self.config.ledger_id,
415                code_combination_id: ccid,
416                period_name: period_name.clone(),
417                effective_date: je.header.posting_date,
418                status: "P".to_string(),
419                entered_dr: if line.debit_amount > Decimal::ZERO {
420                    Some(line.debit_amount)
421                } else {
422                    None
423                },
424                entered_cr: if line.credit_amount > Decimal::ZERO {
425                    Some(line.credit_amount)
426                } else {
427                    None
428                },
429                accounted_dr: if line.debit_amount > Decimal::ZERO {
430                    Some(line.debit_amount * je.header.exchange_rate)
431                } else {
432                    None
433                },
434                accounted_cr: if line.credit_amount > Decimal::ZERO {
435                    Some(line.credit_amount * je.header.exchange_rate)
436                } else {
437                    None
438                },
439                currency_code: je.header.currency.clone(),
440                currency_conversion_rate: if je.header.exchange_rate != Decimal::ONE {
441                    Some(je.header.exchange_rate)
442                } else {
443                    None
444                },
445                currency_conversion_type: if je.header.exchange_rate != Decimal::ONE {
446                    Some("Corporate".to_string())
447                } else {
448                    None
449                },
450                currency_conversion_date: if je.header.exchange_rate != Decimal::ONE {
451                    Some(je.header.posting_date)
452                } else {
453                    None
454                },
455                description: line.line_text.clone(),
456                reference_1: Some(je.header.company_code.clone()),
457                reference_2: Some(line.gl_account.clone()),
458                reference_3: line.cost_center.clone(),
459                reference_4: line.profit_center.clone(),
460                reference_5: je.header.reference.clone(),
461                stat_amount: line.quantity,
462                subledger_doc_sequence_id: None,
463                attribute1: if je.header.is_fraud {
464                    Some("Y".to_string())
465                } else {
466                    None
467                },
468                attribute2: je.header.fraud_type.map(|ft| format!("{:?}", ft)),
469                attribute3: je.header.business_process.map(|bp| format!("{:?}", bp)),
470                creation_date: je.header.created_at.date_naive(),
471                created_by: self.config.user_id,
472            };
473            lines.push(oracle_line);
474        }
475
476        (header, lines)
477    }
478
479    /// Export journal entries to Oracle format files.
480    pub fn export_to_files(
481        &mut self,
482        entries: &[JournalEntry],
483        output_dir: &Path,
484    ) -> SynthResult<HashMap<String, String>> {
485        std::fs::create_dir_all(output_dir)?;
486
487        let mut output_files = HashMap::new();
488
489        // Export headers
490        let header_path = output_dir.join("gl_je_headers.csv");
491        let lines_path = output_dir.join("gl_je_lines.csv");
492
493        let header_file = File::create(&header_path)?;
494        let mut header_writer = BufWriter::new(header_file);
495
496        let lines_file = File::create(&lines_path)?;
497        let mut lines_writer = BufWriter::new(lines_file);
498
499        // Write header row
500        writeln!(
501            header_writer,
502            "JE_HEADER_ID,LEDGER_ID,JE_BATCH_ID,PERIOD_NAME,NAME,JE_CATEGORY,JE_SOURCE,\
503            CURRENCY_CODE,ACTUAL_FLAG,STATUS,DEFAULT_EFFECTIVE_DATE,DESCRIPTION,\
504            RUNNING_TOTAL_DR,RUNNING_TOTAL_CR,CREATION_DATE,CREATED_BY"
505        )?;
506
507        writeln!(
508            lines_writer,
509            "JE_LINE_NUM,JE_HEADER_ID,LEDGER_ID,CODE_COMBINATION_ID,PERIOD_NAME,EFFECTIVE_DATE,\
510            STATUS,ENTERED_DR,ENTERED_CR,ACCOUNTED_DR,ACCOUNTED_CR,CURRENCY_CODE,\
511            DESCRIPTION,REFERENCE_1,REFERENCE_2,REFERENCE_3,ATTRIBUTE1,ATTRIBUTE2,CREATION_DATE,CREATED_BY"
512        )?;
513
514        for je in entries {
515            let (header, lines) = self.convert(je);
516
517            writeln!(
518                header_writer,
519                "{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}",
520                header.je_header_id,
521                header.ledger_id,
522                header.je_batch_id,
523                header.period_name,
524                escape_csv_field(&header.name),
525                header.je_category,
526                header.je_source,
527                header.currency_code,
528                header.actual_flag,
529                header.status,
530                header.default_effective_date,
531                escape_csv_field(&header.description.unwrap_or_default()),
532                header.running_total_dr,
533                header.running_total_cr,
534                header.creation_date,
535                header.created_by,
536            )?;
537
538            for line in lines {
539                writeln!(
540                    lines_writer,
541                    "{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}",
542                    line.je_line_num,
543                    line.je_header_id,
544                    line.ledger_id,
545                    line.code_combination_id,
546                    line.period_name,
547                    line.effective_date,
548                    line.status,
549                    line.entered_dr.map(|d| d.to_string()).unwrap_or_default(),
550                    line.entered_cr.map(|d| d.to_string()).unwrap_or_default(),
551                    line.accounted_dr.map(|d| d.to_string()).unwrap_or_default(),
552                    line.accounted_cr.map(|d| d.to_string()).unwrap_or_default(),
553                    line.currency_code,
554                    escape_csv_field(&line.description.unwrap_or_default()),
555                    line.reference_1.as_deref().unwrap_or(""),
556                    line.reference_2.as_deref().unwrap_or(""),
557                    line.reference_3.as_deref().unwrap_or(""),
558                    line.attribute1.as_deref().unwrap_or(""),
559                    line.attribute2.as_deref().unwrap_or(""),
560                    line.creation_date,
561                    line.created_by,
562                )?;
563            }
564        }
565
566        header_writer.flush()?;
567        lines_writer.flush()?;
568
569        output_files.insert(
570            "gl_je_headers".to_string(),
571            header_path.to_string_lossy().to_string(),
572        );
573        output_files.insert(
574            "gl_je_lines".to_string(),
575            lines_path.to_string_lossy().to_string(),
576        );
577
578        // Export code combinations
579        let ccid_path = output_dir.join("gl_code_combinations.csv");
580        self.export_code_combinations(&ccid_path)?;
581        output_files.insert(
582            "gl_code_combinations".to_string(),
583            ccid_path.to_string_lossy().to_string(),
584        );
585
586        Ok(output_files)
587    }
588
589    /// Export code combinations mapping.
590    fn export_code_combinations(&self, filepath: &Path) -> SynthResult<()> {
591        let file = File::create(filepath)?;
592        let mut writer = BufWriter::new(file);
593
594        writeln!(
595            writer,
596            "CODE_COMBINATION_ID,SEGMENT1,SEGMENT2,SEGMENT3,ENABLED_FLAG"
597        )?;
598
599        for (key, ccid) in &self.ccid_map {
600            let parts: Vec<&str> = key.split('-').collect();
601            let segment1 = parts.first().unwrap_or(&"");
602            let segment2 = parts.get(1).unwrap_or(&"");
603            let segment3 = parts.get(2).unwrap_or(&"0000");
604
605            writeln!(writer, "{},{},{},{},Y", ccid, segment1, segment2, segment3)?;
606        }
607
608        writer.flush()?;
609        Ok(())
610    }
611}
612
613/// Escape a field for CSV output.
614fn escape_csv_field(field: &str) -> String {
615    if field.contains(',') || field.contains('"') || field.contains('\n') {
616        format!("\"{}\"", field.replace('"', "\"\""))
617    } else {
618        field.to_string()
619    }
620}
621
622#[cfg(test)]
623mod tests {
624    use super::*;
625    use chrono::NaiveDate;
626    use datasynth_core::models::{JournalEntryHeader, JournalEntryLine};
627    use rust_decimal::Decimal;
628    use tempfile::TempDir;
629
630    fn create_test_je() -> JournalEntry {
631        let header = JournalEntryHeader::new(
632            "1000".to_string(),
633            NaiveDate::from_ymd_opt(2024, 6, 15).unwrap(),
634        );
635        let mut je = JournalEntry::new(header);
636
637        je.add_line(JournalEntryLine::debit(
638            je.header.document_id,
639            1,
640            "100000".to_string(),
641            Decimal::from(5000),
642        ));
643        je.add_line(JournalEntryLine::credit(
644            je.header.document_id,
645            2,
646            "200000".to_string(),
647            Decimal::from(5000),
648        ));
649
650        je
651    }
652
653    #[test]
654    fn test_period_name_generation() {
655        let date = NaiveDate::from_ymd_opt(2024, 6, 15).unwrap();
656        assert_eq!(OracleExporter::period_name(date), "JUN-24");
657
658        let date = NaiveDate::from_ymd_opt(2024, 1, 1).unwrap();
659        assert_eq!(OracleExporter::period_name(date), "JAN-24");
660
661        let date = NaiveDate::from_ymd_opt(2024, 12, 31).unwrap();
662        assert_eq!(OracleExporter::period_name(date), "DEC-24");
663    }
664
665    #[test]
666    fn test_oracle_exporter_creates_files() {
667        let temp_dir = TempDir::new().unwrap();
668        let config = OracleExportConfig::default();
669        let mut exporter = OracleExporter::new(config);
670
671        let entries = vec![create_test_je()];
672        let result = exporter.export_to_files(&entries, temp_dir.path());
673
674        assert!(result.is_ok());
675        let files = result.unwrap();
676        assert!(files.contains_key("gl_je_headers"));
677        assert!(files.contains_key("gl_je_lines"));
678        assert!(files.contains_key("gl_code_combinations"));
679
680        assert!(temp_dir.path().join("gl_je_headers.csv").exists());
681        assert!(temp_dir.path().join("gl_je_lines.csv").exists());
682        assert!(temp_dir.path().join("gl_code_combinations.csv").exists());
683    }
684
685    #[test]
686    fn test_conversion_produces_balanced_totals() {
687        let config = OracleExportConfig::default();
688        let mut exporter = OracleExporter::new(config);
689        let je = create_test_je();
690
691        let (header, lines) = exporter.convert(&je);
692
693        assert_eq!(header.running_total_dr, header.running_total_cr);
694        assert_eq!(lines.len(), 2);
695
696        let line_total_dr: Decimal = lines.iter().filter_map(|l| l.entered_dr).sum();
697        let line_total_cr: Decimal = lines.iter().filter_map(|l| l.entered_cr).sum();
698        assert_eq!(line_total_dr, line_total_cr);
699    }
700}
datasynth_output/formats/oracle.rs

datasynth_output/formats/
oracle.rs
