[set page_banner]Journal Entry[/set] [set fm_class]Ledger[/set] [set help_name]journal_entry[/set] [set page_title]Journal Entry[/set] [set icon_name]icon_stats.gif[/set] @_FM_STD_HEAD_@ [seti sqlSentence] BEGIN TRANSACTION; [perl tables="gl_chartofaccount"] my $trans; my $action = $Values->{"mv_action"}; my $status = 200 if $Values->{"approve"} eq 'yes'; my $updFunction = "gl_posting" if $status; $status = 100 if !$status; $updFunction = "gl_posting_prelim" if !$updFunction; my $batchseq = $Values->{"select_batch"} || "currval('gl_batchsequence')"; my $soa = $Values->{"soa"}; my ($totaldr, $totalcr) = (0,0); my $ref = $Scratch->{journals}; my ($ar, $nul, $names) = @$ref; # Calculating totals first for my $journal (@$ar) { my ($x, $journalnr, $jdate, $debit, $credit, $description, $acctinfo) = @$journal; $totaldr += $debit; $totalcr += $credit; }; $Scratch->{remark} = ""; if ($status == 200 && $totaldr != $totalcr && !$soa) { $status = 100; $updFunction = "gl_posting_prelim"; $Scratch->{remark} = "

[L]Batch not balanced. Still not accounted![/L]"; }; if ($action eq "Update") { $trans .= "DELETE FROM gl_journal WHERE batch_id = $batchseq;\n"; $trans .= "UPDATE gl_batch SET batchnr = [value batchnr], name = '[value batchname]',posteddate= '[value batchdate]', soa=$soa, totaldr = $totaldr, totalcr = $totalcr, status = $status WHERE batch_id = $batchseq;\n"; } elsif ($action eq "Delete") { $trans .= "DELETE FROM gl_batch WHERE batchnr = [value select_batch];"; return; } else { $trans .= "INSERT INTO gl_batch (system_id, type, batchnr, name, status, totaldr, totalcr, posteddate, updated) VALUES (10005, 40001, [value batchnr], '[value batchname]', $status, $totaldr, $totalcr,'[value batchdate]', 'now');\n"; } for my $journal (@$ar) { my ($x, $journalnr, $jdate, $debit, $credit, $description, $acctinfo) = @$journal; my ($acctnr,$pgrp,$cgrp) = split(/\//,$acctinfo); my ($coa_id, $cgrp_id, $vat_id, $vat_coa); my $db = $Sql{gl_chartofaccount} or return "NO chart of account?"; my $q = "SELECT coa_id, vat_id, vat_coa FROM gl_chartofaccount WHERE number = '$acctnr'"; my $sth = $db->prepare($q); $sth->execute or die("Can't execute $q\n"); ($coa_id, $vat_id, $vat_coa) = $sth->fetchrow(); # if ($pgrp) { # $q = "SELECT ac_id FROM pd_productgroup WHERE pgrp_id = $pgrp"; # $sth = $db->prepare($q); # $sth->execute or die("Can't execute $q\n"); # ($pgrp_id) = $sth->fetchrow(); # }; $pgrp ||= "NULL"; if ($cgrp) { $q = "SELECT ac_id FROM co_custgrp WHERE custgrp_nr = $cgrp"; $sth = $db->prepare($q); $sth->execute or die("Can't execute $q\n"); ($cgrp_id) = $sth->fetchrow(); }; $cgrp_id ||= "NULL"; # If final accounting of batch, check for vat code and chart of account if ($status == 200) { if ($soa && $soa ne "NULL") { # set-off account (switching debit and credit) $trans .= "SELECT $updFunction($soa, $pgrp, $cgrp_id, '$jdate', $credit, $debit, '$description', 40001, 1, $batchseq, $journalnr);\n"; }; if ($vat_id && $vat_coa) { # Vat information my ($vat_pct, $vat_debit, $vat_credit); $q = "SELECT percent FROM pd_vat WHERE vat_id = $vat_id"; $sth = $db->prepare($q); $sth->execute or die("Can't execute $q\n"); ($vat_pct) = $sth->fetchrow() / 100; $vat_debit = $debit / (1 + $vat_pct) * $vat_pct; $vat_credit = $credit / (1 + $vat_pct) * $vat_pct; $debit -= $vat_debit; $credit -= $vat_credit; $trans .= "SELECT $updFunction($vat_coa, $pgrp, $cgrp_id, '$jdate', $vat_debit, $vat_credit, '$description', 40001, 1, $batchseq, $journalnr);\n"; }; }; # Update accounting $trans .= "SELECT $updFunction($coa_id, $pgrp, $cgrp_id, '$jdate', $debit, $credit, '$description', 40001, 1, $batchseq, $journalnr);\n"; }; undef $Values->{select_batch}; $trans; [/perl] COMMIT; [/seti] [if value mv_action eq Update] [set returnpage]freemoney/batchList[/set] [set leadtext]The batch entry has been updated.[/set] [elsif value mv_action eq Delete] [set returnpage]freemoney/batchList[/set] [set leadtext]The batch entry has been deleted:[/set] [/elsif] [else] [set returnpage]freemoney/batchList[/set] [set leadtext]The batch entry has been added.[/set] [/else] [/if] [value name=mv_action set=""] [try][sql interpolate=1][scratch sqlSentence][/sql][/try]
[L][scratch leadtext][/L] [scratch remark]

[catch] [L]There was an error adding the new order. Please check your data.[/L]

[/catch] [L]BACK[/L] @_FM_STD_FOOTER_@