Hi All,
I need to find the opening balance of an item according to the date parameter entered by the user.
Can any one provide me with the query or the table name from where i can get that.
Regards,
Ali Pirzada
Item Opening Balance
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
SELECT (mmt.inventory_item_id),
msi.attribute14, msi.description item_description,
DECODE (mtln.lot_number,
NULL, SUM (mmt.transaction_quantity),
SUM (mtln.transaction_quantity)
) net_qty,
mtln.lot_number,
mil.segment1
|| '-'
|| mil.segment11
|| '-'
|| mil.segment2
|| '-'
|| mil.segment3
|| '-'
|| mil.segment4
|| '-'
|| mil.segment5
|| '-'
|| mil.segment6
|| '-'
|| mil.segment7
|| '-'
|| mil.segment8
|| '-'
|| mil.segment9
|| '-'
|| mil.segment10 loc_seg,
secondary_inventory_name, ood.organization_name
FROM mtl_material_transactions mmt,
org_organization_definitions ood,
mtl_transaction_lot_numbers mtln,
mtl_system_items msi,
mtl_item_locations mil,
mtl_secondary_inventories mseci
WHERE mmt.organization_id = ood.organization_id
AND mmt.transaction_id = mtln.transaction_id(+)
AND msi.inventory_item_id = mmt.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.locator_id = mil.inventory_location_id(+)
AND mmt.organization_id = mil.organization_id(+)
AND mmt.subinventory_code = mseci.secondary_inventory_name
AND mmt.organization_id = mseci.organization_id
and mmt.ORGANIZATION_ID = 116
AND TRUNC (mmt.transaction_date) <= '31-JUL-2007'
--and mmt.INVENTORY_ITEM_ID= 2984
GROUP BY (mmt.inventory_item_id),
ood.organization_name,
mtln.lot_number,
msi.description,
mil.segment1
|| '-'
|| mil.segment11
|| '-'
|| mil.segment2
|| '-'
|| mil.segment3
|| '-'
|| mil.segment4
|| '-'
|| mil.segment5
|| '-'
|| mil.segment6
|| '-'
|| mil.segment7
|| '-'
|| mil.segment8
|| '-'
|| mil.segment9
|| '-'
|| mil.segment10,
secondary_inventory_name,
msi.attribute14
HAVING DECODE (mtln.lot_number,
NULL, SUM (mmt.transaction_quantity),
SUM (mtln.transaction_quantity)
) != 0
ORDER BY ood.organization_name, (mmt.inventory_item_id)
msi.attribute14, msi.description item_description,
DECODE (mtln.lot_number,
NULL, SUM (mmt.transaction_quantity),
SUM (mtln.transaction_quantity)
) net_qty,
mtln.lot_number,
mil.segment1
|| '-'
|| mil.segment11
|| '-'
|| mil.segment2
|| '-'
|| mil.segment3
|| '-'
|| mil.segment4
|| '-'
|| mil.segment5
|| '-'
|| mil.segment6
|| '-'
|| mil.segment7
|| '-'
|| mil.segment8
|| '-'
|| mil.segment9
|| '-'
|| mil.segment10 loc_seg,
secondary_inventory_name, ood.organization_name
FROM mtl_material_transactions mmt,
org_organization_definitions ood,
mtl_transaction_lot_numbers mtln,
mtl_system_items msi,
mtl_item_locations mil,
mtl_secondary_inventories mseci
WHERE mmt.organization_id = ood.organization_id
AND mmt.transaction_id = mtln.transaction_id(+)
AND msi.inventory_item_id = mmt.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.locator_id = mil.inventory_location_id(+)
AND mmt.organization_id = mil.organization_id(+)
AND mmt.subinventory_code = mseci.secondary_inventory_name
AND mmt.organization_id = mseci.organization_id
and mmt.ORGANIZATION_ID = 116
AND TRUNC (mmt.transaction_date) <= '31-JUL-2007'
--and mmt.INVENTORY_ITEM_ID= 2984
GROUP BY (mmt.inventory_item_id),
ood.organization_name,
mtln.lot_number,
msi.description,
mil.segment1
|| '-'
|| mil.segment11
|| '-'
|| mil.segment2
|| '-'
|| mil.segment3
|| '-'
|| mil.segment4
|| '-'
|| mil.segment5
|| '-'
|| mil.segment6
|| '-'
|| mil.segment7
|| '-'
|| mil.segment8
|| '-'
|| mil.segment9
|| '-'
|| mil.segment10,
secondary_inventory_name,
msi.attribute14
HAVING DECODE (mtln.lot_number,
NULL, SUM (mmt.transaction_quantity),
SUM (mtln.transaction_quantity)
) != 0
ORDER BY ood.organization_name, (mmt.inventory_item_id)
Who is online
Users browsing this forum: No registered users and 0 guests