1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
|
from datetime import datetime, timedelta, date
from dateutil import parser
from dateutil import rrule
from dateutil.relativedelta import relativedelta
from openerp.osv import fields, osv
from openerp.service import web_services
from openerp.tools.translate import _
import pytz
import re
import time
import logging
from openerp import tools, SUPERUSER_ID
_logger = logging.getLogger(__name__)
class account_journal_backdated_transaction(osv.osv):
_name = 'account.journal.backdated.transaction'
#_inherit = 'procurement.order'
_description = 'Jurnal untuk transaksi backdated (Pembelian dan Retur)'
#_inherit = 'res.alarm'
__attribute__ = {}
_columns = {
'name' : fields.char('Reference'),
'backdated_picking_id': fields.many2one('stock.picking', 'Picking Reference', select=True),
}
_defaults = {}
def do_run_scheduler(self, cr, uid, automatic=False, use_new_cursor=False, \
context=None):
_logger.info("Start Scheduler Created Journal for Backdated Transaction")
cr.execute(" select b.product_id, c.price cogs, b.price_unit , date_trunc('day', a.date), " \
" date_trunc('day', a.create_date) create_date, " \
" d.valuation, b.location_dest_id, a.type, a.analytic_account_id, a.id, a.name " \
" from stock_picking a, " \
" stock_move b, " \
" product_historic_cost c," \
" product_product d" \
" where a.date < date_trunc('day', a.create_date)" \
" and a.state = 'done'" \
" and a.type in ('in','out')" \
" and b.picking_id = a.id" \
" and c.picking_id = a.id" \
" and c.product_id = b.product_id" \
" and d.id = b.product_id" \
" and NOT EXISTS (SELECT 1" \
" FROM account_journal_backdated_transaction d" \
" WHERE d.backdated_picking_id = a.id )" \
" order by a.date asc" )
datas = cr.fetchall()
#print 'datas : ', datas
tmp_picking_id = -9
tmp_name = ''
created = False
backdated_data = {}
for rows in datas:
_logger.info("Backdated Transaction, Picking_ID: %s, Refference : %s, Transaction Date : %s, Product_ID : %s, COGS : %s"
% (rows[9], rows[10], rows[0], rows[3], rows[1]))
backdated_trx = {
'product_id':rows[0],
'price_cogs':rows[1],
'price_unit':rows[2],
'transaction_date':rows[3],
'valuation':rows[5],
'picking_id':rows[9]
}
#print backdated_trx
#print 'ID : %s, created_uid : %s' %(rows[0], rows[1])
#print tmp_picking_id,' !=', rows[9],' and ',tmp_picking_id,' != ',-9
if tmp_picking_id != rows[9] and tmp_picking_id != -9:
#print 'lari kesini nih'
backdated_data = {'backdated_picking_id': tmp_picking_id,
'name' : tmp_name}
#print 'backdated_data : ', backdated_data
self.create(cr,uid,backdated_data, context=context)
created = True
else:
created = False
tmp_picking_id = rows[9]
tmp_name = rows[10]
self._backdated_transaction(cr, uid, backdated_trx, context=context)
if not created and tmp_picking_id != -9:
backdated_data = {'backdated_picking_id': tmp_picking_id,
'name' : tmp_name}
self.create(cr,uid,backdated_data, context=context)
#print '%s - Schedule Jobs use_new_cursor : ' % datetime.now().strftime('%Y-%m-%d %H:%M:%S'), use_new_cursor,', context : ', context, ', automatic : ', automatic, 'Database name : ', cr.dbname
_logger.info("End Scheduler Created Journal for Backdated Transaction")
return True
def _backdated_transaction(self, cr, uid, backdated_trx, context=None):
move_obj = self.pool.get('stock.move')
#print 'move_obj : ', move_obj
move_id = move_obj.search(cr, uid, [('date', '>', "%s" % backdated_trx['transaction_date']),
('product_id','=',backdated_trx['product_id']),
('picking_id','!=',backdated_trx['picking_id'])])
#print 'move_id : ', move_id
for move_trx in move_obj.browse(cr, uid, move_id):
#print 'move_trx.id : ',move_trx.id,', move_trx.picking_id : ', move_trx.picking_id,', move_trx.product_id.id : ', move_trx.product_id.id,", backdated_trx['product_id']", backdated_trx['product_id']
self._create_product_valuation_moves(cr, uid, move_trx, backdated_trx, context=None)
def _create_product_valuation_moves(self, cr, uid, move, backdated_trx, context=None):
"""
Generate the appropriate accounting moves if the product being moves is subject
to real_time valuation tracking, and the source or destination location is
a transit location or is outside of the company.
"""
#print 'move.product_id.valuation : ', move.product_id.valuation
narration = ''
if move.product_id.valuation == 'real_time': # FIXME: product valuation should perhaps be a property?
if context is None:
context = {}
src_company_ctx = dict(context,force_company=move.location_id.company_id.id)
dest_company_ctx = dict(context,force_company=move.location_dest_id.company_id.id)
account_moves = []
# Outgoing moves (or cross-company output part)
# start analytic account for journal, iqbal 23 Feb 2014
analytic_account_id_cr = False
analytic_account_id_dr = False
# end analytic account for journal, iqbal 23 Feb 2014
if move.location_id.company_id \
and (move.location_id.usage == 'internal' and move.location_dest_id.usage != 'internal'\
or move.location_id.company_id != move.location_dest_id.company_id):
journal_id, acc_src, acc_dest, acc_valuation = self._get_accounting_data_for_valuation(cr, uid, move, src_company_ctx)
reference_amount, reference_currency_id = self._get_reference_accounting_values_for_valuation(cr, uid, move, backdated_trx, src_company_ctx)
#print 'Kesini Scheduler move.location_id.usage : ', move.location_id.usage, ' move.location_dest_id.usage : ', move.location_dest_id.usage, \
# ' journal_id : ', journal_id,' acc_src : ', acc_src, ' acc_dest : ', acc_dest, ' acc_valuation : ', acc_valuation, \
# ' reference_amount : ', reference_amount, ' reference_currency_id : ', reference_currency_id, \
# ' analytic_account_id : ', move.picking_id.analytic_account_id.id, " move.picking_id.type : ", move.picking_id.type, \
# ' move.location_id.company_id : ', move.location_id.company_id
#returning goods to supplier
# add analytict account for journal, iqbal 20140223
if reference_amount != 0:
narration = 'Adjusment dari selisih COGS, sebesar %s ' % (reference_amount)
if move.location_dest_id.usage == 'supplier':
#print "Supplier "
account_moves += [(journal_id, self._create_account_move_line(cr, uid, move, acc_valuation, acc_src, reference_amount, reference_currency_id, analytic_account_id_cr, analytic_account_id_dr, context))]
else:
#print "Lainnya "
analytic_account_id_dr = move.picking_id.analytic_account_id.id
account_moves += [(journal_id, self._create_account_move_line(cr, uid, move, acc_valuation, acc_dest, reference_amount, reference_currency_id, analytic_account_id_cr, analytic_account_id_dr, context))]
# Incoming moves (or cross-company input part)
if move.location_dest_id.company_id \
and (move.location_id.usage != 'internal' and move.location_dest_id.usage == 'internal'\
or move.location_id.company_id != move.location_dest_id.company_id):
journal_id, acc_src, acc_dest, acc_valuation = self._get_accounting_data_for_valuation(cr, uid, move, dest_company_ctx)
reference_amount, reference_currency_id = self._get_reference_accounting_values_for_valuation(cr, uid, move, backdated_trx, src_company_ctx)
#goods return from customer
# add analytict account for journal, iqbal 20140223
#print 'Kesana Scheduler move.location_id.usage : ', move.location_id.usage, ' move.location_dest_id.usage : ', move.location_dest_id.usage, \
# ' journal_id : ', journal_id,' acc_src : ', acc_src, ' acc_dest : ', acc_dest, ' acc_valuation : ', acc_valuation, \
# ' reference_amount : ', reference_amount, ' reference_currency_id : ', reference_currency_id, \
# ' analytic_account_id : ', move.picking_id.analytic_account_id.id, " move.picking_id.type : ", move.picking_id.type, \
# ' move.location_dest_id.company_id : ', move.location_dest_id.company_id
if reference_amount != 0:
narration = 'Adjusment dari selisih COGS, sebesar %s ' % (reference_amount)
if move.location_id.usage == 'customer':
analytic_account_id_cr = move.picking_id.analytic_account_id.id
account_moves += [(journal_id, self._create_account_move_line(cr, uid, move, acc_dest, acc_valuation, reference_amount, reference_currency_id, analytic_account_id_cr, analytic_account_id_dr, context))]
else:
account_moves += [(journal_id, self._create_account_move_line(cr, uid, move, acc_src, acc_valuation, reference_amount, reference_currency_id, analytic_account_id_cr, analytic_account_id_dr, context))]
move_obj = self.pool.get('account.move')
for j_id, move_lines in account_moves:
#print 'move.date : ', move.date,', move.id : ', move.id
datamove = {
'journal_id': j_id,
'line_id': move_lines,
'ref': move.picking_id and move.picking_id.name,
'date': move.date,
'narration': narration
}
_logger.info("Backdated Transaction, j_id : %s , account_move: %s , account_move_lines : %s " % (j_id, datamove, move_lines))
move_obj.create(cr, uid,datamove, context=context)
def _create_account_move_line(self, cr, uid, move, src_account_id, dest_account_id, reference_amount, reference_currency_id, analytic_account_id_cr, analytic_account_id_dr, context=None):
"""
Generate the account.move.line values to post to track the stock valuation difference due to the
processing of the given stock move.
"""
# prepare default values considering that the destination accounts have the reference_currency_id as their main currency
partner_id = (move.picking_id.partner_id and self.pool.get('res.partner')._find_accounting_partner(move.picking_id.partner_id).id) or False
if reference_amount < 0 :
tmpsrc_account_id = src_account_id
src_account_id = dest_account_id
dest_account_id = src_account_id
reference_amount = reference_amount * -1
debit_line_vals = {
'name': move.name,
'product_id': move.product_id and move.product_id.id or False,
'quantity': move.product_qty,
'ref': move.picking_id and move.picking_id.name or False,
'date': move.date,
'partner_id': partner_id,
'debit': reference_amount,
'analytic_account_id' : analytic_account_id_cr,
'account_id': dest_account_id,
}
credit_line_vals = {
'name': move.name,
'product_id': move.product_id and move.product_id.id or False,
'quantity': move.product_qty,
'ref': move.picking_id and move.picking_id.name or False,
'date': move.date,
'partner_id': partner_id,
'credit': reference_amount,
'analytic_account_id' : analytic_account_id_cr,
'account_id': src_account_id,
}
# if we are posting to accounts in a different currency, provide correct values in both currencies correctly
# when compatible with the optional secondary currency on the account.
# Financial Accounts only accept amounts in secondary currencies if there's no secondary currency on the account
# or if it's the same as that of the secondary amount being posted.
account_obj = self.pool.get('account.account')
src_acct, dest_acct = account_obj.browse(cr, uid, [src_account_id, dest_account_id], context=context)
src_main_currency_id = src_acct.company_id.currency_id.id
dest_main_currency_id = dest_acct.company_id.currency_id.id
cur_obj = self.pool.get('res.currency')
if reference_currency_id != src_main_currency_id:
# fix credit line:
credit_line_vals['credit'] = cur_obj.compute(cr, uid, reference_currency_id, src_main_currency_id, reference_amount, context=context)
if (not src_acct.currency_id) or src_acct.currency_id.id == reference_currency_id:
credit_line_vals.update(currency_id=reference_currency_id, amount_currency=-reference_amount)
if reference_currency_id != dest_main_currency_id:
# fix debit line:
debit_line_vals['debit'] = cur_obj.compute(cr, uid, reference_currency_id, dest_main_currency_id, reference_amount, context=context)
if (not dest_acct.currency_id) or dest_acct.currency_id.id == reference_currency_id:
debit_line_vals.update(currency_id=reference_currency_id, amount_currency=reference_amount)
return [(0, 0, debit_line_vals), (0, 0, credit_line_vals)]
def _get_accounting_data_for_valuation(self, cr, uid, move, context=None):
"""
Return the accounts and journal to use to post Journal Entries for the real-time
valuation of the move.
:param context: context dictionary that can explicitly mention the company to consider via the 'force_company' key
:raise: osv.except_osv() is any mandatory account or journal is not defined.
"""
product_obj=self.pool.get('product.product')
accounts = product_obj.get_product_accounts(cr, uid, move.product_id.id, context)
if move.location_id.valuation_out_account_id:
acc_src = move.location_id.valuation_out_account_id.id
else:
acc_src = accounts['stock_account_input']
if move.location_dest_id.valuation_in_account_id:
acc_dest = move.location_dest_id.valuation_in_account_id.id
else:
acc_dest = accounts['stock_account_output']
acc_valuation = accounts.get('property_stock_valuation_account_id', False)
journal_id = accounts['stock_journal']
if acc_dest == acc_valuation:
raise osv.except_osv(_('Error!'), _('Cannot create Journal Entry, Output Account of this product and Valuation account on category of this product are same.'))
if acc_src == acc_valuation:
raise osv.except_osv(_('Error!'), _('Cannot create Journal Entry, Input Account of this product and Valuation account on category of this product are same.'))
if not acc_src:
raise osv.except_osv(_('Error!'), _('Please define stock input account for this product or its category: "%s" (id: %d)') % \
(move.product_id.name, move.product_id.id,))
if not acc_dest:
raise osv.except_osv(_('Error!'), _('Please define stock output account for this product or its category: "%s" (id: %d)') % \
(move.product_id.name, move.product_id.id,))
if not journal_id:
raise osv.except_osv(_('Error!'), _('Please define journal on the product category: "%s" (id: %d)') % \
(move.product_id.categ_id.name, move.product_id.categ_id.id,))
if not acc_valuation:
raise osv.except_osv(_('Error!'), _('Please define inventory valuation account on the product category: "%s" (id: %d)') % \
(move.product_id.categ_id.name, move.product_id.categ_id.id,))
return journal_id, acc_src, acc_dest, acc_valuation
def _get_reference_accounting_values_for_valuation(self, cr, uid, move, backdated_trx, context=None):
"""
Return the reference amount and reference currency representing the inventory valuation for this move.
These reference values should possibly be converted before being posted in Journals to adapt to the primary
and secondary currencies of the relevant accounts.
"""
product_uom_obj = self.pool.get('product.uom')
# by default the reference currency is that of the move's company
reference_currency_id = move.company_id.currency_id.id
default_uom = move.product_id.uom_id.id
qty = product_uom_obj._compute_qty(cr, uid, move.product_uom.id, move.product_qty, default_uom)
reference_amount = qty * (backdated_trx['price_cogs'] - move.price_unit)
reference_currency_id = move.price_currency_id.id or reference_currency_id
if reference_amount > 0 :
print backdated_trx['price_cogs'],' - ', move.price_unit
return reference_amount, reference_currency_id
account_journal_backdated_transaction()
|