|
(0177030)
|
|
Sandrahuguet
|
2025-03-20 14:44
(edited on: 2025-03-25 09:45) |
|
Test Plan:
In F&B Client
-Go to Costing Rule and activate backdated transactions (Fix backdated transactions button) Use Today's date as the input value in the popup.
-Go to product tab and create a new product add a price in be soft drinker price list and in general sales price list. Set price as 10.
- Add a costing record with date as today cost = 10.
-Create a physical inventory for this product qty 100 and date today
-Create a goods shipment for 10 units date as today and product created in previous step
-Create a goods receipt date as today and product with 50 units created in previous step
-Create a goods receipt date as tomorrow and product with 75 units created in previous step
-Create a goods shipment date as tomorrow and product with 15 units created in previous step (in the database update the movementdate in m_transaction with any hour,min,sec) for example: 2025-03-25 16:43:22
-Create a goods receipt date as tomorrow and product with 25 units created in previous step
-Create a goods receipt date as day after tomorrow and product with 35 units created in previous step
-Launch the costing background
Use the product id in the below query
select t.movementtype, t.movementqty, t.trxprocessdate, t.movementdate, tcost.cost
, sum(t.movementqty) over (order by trunc(t.movementdate), trxprocessdate asc, rl.seqno, movementqty desc, t.m_transaction_id asc) as realstock
, sum(t.movementqty) over (order by trxprocessdate asc, rl.seqno, movementqty desc, t.m_transaction_id asc) as realstock2
, sum(tcost.cost) OVER (order by trunc(t.movementdate), trxprocessdate asc, rl.seqno, movementqty desc, t.m_transaction_id asc) as realcumcost
, ROUND(CASE WHEN sum(t.movementqty) OVER (order by trunc(t.movementdate), trxprocessdate asc, rl.seqno, movementqty desc, t.m_transaction_id asc) = 0 THEN 0 ELSE sum(tcost.cost) OVER (order by trunc(t.movementdate), trxprocessdate asc, rl.seqno, movementqty desc, t.m_transaction_id asc)/sum(t.movementqty) OVER (order by trunc(t.movementdate), trxprocessdate asc, rl.seqno, movementqty desc, t.m_transaction_id asc) END, 6) AS realavg
, ' ' as blank
, c.datefrom, c.dateto, c.ismanual, c.qty, c.price, c.cost, c.cumstock, c.cumcost, c.originalcost
, sum(t.movementqty) OVER (order by trunc(t.movementdate), trxprocessdate asc, rl.seqno, movementqty desc, t.m_transaction_id asc) - c.cumstock as stockdiff
, t.m_transaction_id
from m_transaction t
left join m_costing c on t.m_transaction_id = c.m_transaction_id
left join (select sum(tc.cost)*sign(tct.movementqty) as cost, tc.m_transaction_id
from m_transaction_cost tc
join m_transaction tct on tc.m_transaction_id = tct.m_transaction_id
where tct.m_product_id = '689293E13F6B4DBDB835AB3506CC6876'
-- and tct.m_locator_id in ('BCDE2DA62BD048869B1899590E8E0484', '8D1543D981594A56891E4BC21472A88F')
group by tc.m_transaction_id, tct.movementqty) as tcost on tcost.m_transaction_id = t.m_transaction_id
join ad_ref_list rl on t.movementtype = rl.value and rl.ad_reference_id = '189'
where t.m_product_id = '689293E13F6B4DBDB835AB3506CC6876'
-- and t.m_locator_id in ('BCDE2DA62BD048869B1899590E8E0484', '8D1543D981594A56891E4BC21472A88F')
group by t.m_transaction_id, tcost.cost, c.m_costing_id, rl.seqno
order by trunc(t.movementdate), trxprocessdate asc, rl.seqno, movementqty desc, t.m_transaction_id asc;
- Check that cumulative stock is correct i.e 260 and cumulative cost is 2600.
|
|