Skip to content

Instantly share code, notes, and snippets.

@ranafaraz

ranafaraz/01 - Electrical.sql

Created March 18, 2021 09:29
Show Gist options
  • Save ranafaraz/030f15ecd6f579cdd44588d9a0fec9c1 to your computer and use it in GitHub Desktop.
Save ranafaraz/030f15ecd6f579cdd44588d9a0fec9c1 to your computer and use it in GitHub Desktop.
Download ZIP
Queries relevant to Balance sheet of IUB
Raw
01 - Electrical.sql
-- Electrical Equipment / Office Equipment
SELECT
SUM(lad.netamttoinvoice) AS "2016-17",
-- 2017 - 2018 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2017-18"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '01'
AND
lad.gl_budget_id = 1000005),
-- 2017 - 2018 - Ended
-- 2018 - 2019 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2018-19"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '01'
AND
lad.gl_budget_id = 1000004),
-- 2018 - 2019 - Ended
-- 2019 - 2020 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2019-20"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '01'
AND
lad.gl_budget_id = 1000003),
-- 2019 - 2020 - Ended
-- 2020 - 2021 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2020-21"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '01'
AND
lad.gl_budget_id = 1000000)
-- 2020 - 2021 - Ended
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '01'
AND
lad.gl_budget_id = 1000006
Raw
02 - furniture.sql
-- Furniture & Fixture
SELECT
SUM(lad.netamttoinvoice) AS "2016-17",
-- 2017 - 2018 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2017-18"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '02'
AND
lad.gl_budget_id = 1000005),
-- 2017 - 2018 - Ended
-- 2018 - 2019 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2018-19"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '02'
AND
lad.gl_budget_id = 1000004),
-- 2018 - 2019 - Ended
-- 2019 - 2020 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2019-20"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '02'
AND
lad.gl_budget_id = 1000003),
-- 2019 - 2020 - Ended
-- 2020 - 2021 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2020-21"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '02'
AND
lad.gl_budget_id = 1000000)
-- 2020 - 2021 - Ended
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '02'
AND
lad.gl_budget_id = 1000006
Raw
03 - IT Equipment.sql
-- IT Equipment
SELECT
SUM(lad.netamttoinvoice) AS "2016-17",
-- 2017 - 2018 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2017-18"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '03'
AND
lad.gl_budget_id = 1000005),
-- 2017 - 2018 - Ended
-- 2018 - 2019 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2018-19"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '03'
AND
lad.gl_budget_id = 1000004),
-- 2018 - 2019 - Ended
-- 2019 - 2020 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2019-20"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '03'
AND
lad.gl_budget_id = 1000003),
-- 2019 - 2020 - Ended
-- 2020 - 2021 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2020-21"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '03'
AND
lad.gl_budget_id = 1000000)
-- 2020 - 2021 - Ended
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '03'
AND
lad.gl_budget_id = 1000006
Raw
04 - Tools & Machinery.sql
-- Tool and Machinery
SELECT
SUM(lad.netamttoinvoice) AS "2016-17",
-- 2017 - 2018 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2017-18"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '04'
AND
lad.gl_budget_id = 1000005),
-- 2017 - 2018 - Ended
-- 2018 - 2019 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2018-19"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '04'
AND
lad.gl_budget_id = 1000004),
-- 2018 - 2019 - Ended
-- 2019 - 2020 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2019-20"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '04'
AND
lad.gl_budget_id = 1000003),
-- 2019 - 2020 - Ended
-- 2020 - 2021 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2020-21"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '04'
AND
lad.gl_budget_id = 1000000)
-- 2020 - 2021 - Ended
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '04'
AND
lad.gl_budget_id = 1000006
Raw
05 - general.sql
-- General
SELECT
SUM(lad.netamttoinvoice) AS "2016-17",
-- 2017 - 2018 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2017-18"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '05'
AND
lad.gl_budget_id = 1000005),
-- 2017 - 2018 - Ended
-- 2018 - 2019 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2018-19"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '05'
AND
lad.gl_budget_id = 1000004),
-- 2018 - 2019 - Ended
-- 2019 - 2020 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2019-20"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '05'
AND
lad.gl_budget_id = 1000003),
-- 2019 - 2020 - Ended
-- 2020 - 2021 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2020-21"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '05'
AND
lad.gl_budget_id = 1000000)
-- 2020 - 2021 - Ended
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '05'
AND
lad.gl_budget_id = 1000006
Raw
06 - Transport Divison.sql
-- Transport Division
SELECT
SUM(lad.netamttoinvoice) AS "2016-17",
-- 2017 - 2018 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2017-18"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '06'
AND
lad.gl_budget_id = 1000005),
-- 2017 - 2018 - Ended
-- 2018 - 2019 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2018-19"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '06'
AND
lad.gl_budget_id = 1000004),
-- 2018 - 2019 - Ended
-- 2019 - 2020 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2019-20"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '06'
AND
lad.gl_budget_id = 1000003),
-- 2019 - 2020 - Ended
-- 2020 - 2021 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2020-21"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '06'
AND
lad.gl_budget_id = 1000000)
-- 2020 - 2021 - Ended
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '06'
AND
lad.gl_budget_id = 1000006
Raw
07 - Science Material.sql
-- Science Material
SELECT
SUM(lad.netamttoinvoice) AS "2016-17",
-- 2017 - 2018 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2017-18"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '07'
AND
lad.gl_budget_id = 1000005),
-- 2017 - 2018 - Ended
-- 2018 - 2019 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2018-19"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '07'
AND
lad.gl_budget_id = 1000004),
-- 2018 - 2019 - Ended
-- 2019 - 2020 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2019-20"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '07'
AND
lad.gl_budget_id = 1000003),
-- 2019 - 2020 - Ended
-- 2020 - 2021 - Started
(SELECT
SUM(lad.netamttoinvoice) AS "2020-21"
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '07'
AND
lad.gl_budget_id = 1000000)
-- 2020 - 2021 - Ended
FROM
adempiere.LegacyAssetData lad
LEFT JOIN adempiere.m_product p ON (( p.value = lad.value ))
LEFT JOIN adempiere.m_product_category pc ON (( pc.m_product_category_id = p.m_product_category_id ))
LEFT JOIN adempiere.m_product_category pcp ON (( pcp.m_product_category_id = pc.m_product_category_parent_id ))
WHERE
lad.ad_client_id = 1000000
AND
p.a_asset_group_id = 1000007
AND
pcp.value = '07'
AND
lad.gl_budget_id = 1000006
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment