Untitled
unknown
plain_text
2 months ago
25 kB
4
Indexable
POBIERZ_POWIAZANIA_PODMIOTU = """
--sql
SELECT
(
SELECT
COUNT(*)
FROM
kk_podmiot_powiazania
WHERE
-- CZY_NAJNOWSZE_POZYSKANE = 1
id_podmiot_a = :id_podmiotu
or id_podmiot_b = :id_podmiotu
) AS liczba_powiazan,
(
SELECT
JSON_ARRAYAGG(JSON_OBJECT(
'id_podmiot' VALUE id_podmiot_b,
'dane_data_od' VALUE to_char(dane_data_od, 'YYYY-MM-DD'),
'dane_data_do' VALUE to_char(dane_data_do, 'YYYY-MM-DD'),
'rola' VALUE rola,
'dane_sformatowane' VALUE TO_CLOB(dane_sformatowane),
'typ' VALUE
CASE
WHEN czy_osoba_fizyczna = 1 THEN
'Osoba'
ELSE
'Podmiot'
END
) RETURNING CLOB)
FROM (
SELECT DISTINCT
id_podmiot_b,
dane_data_od,
dane_data_do,
rola,
CAST(dane_sformatowane as VARCHAR2(4000)) as dane_sformatowane,
czy_osoba_fizyczna
FROM kk_podmiot_powiazania pow
INNER JOIN {schema}.kk_podmiot pod ON pod.id_podmiot = pow.id_podmiot_b
WHERE
id_podmiot_a = :id_podmiotu
AND ( upper(klasa_roli) LIKE 'AKCJONARIUSZE%'
OR upper(klasa_roli) = 'KRS_SHAREHOLDER' )
)
) AS akcjonariusze,
(
SELECT
JSON_ARRAYAGG(JSON_OBJECT(
'id_podmiot' VALUE id_podmiot_b,
'dane_data_od' VALUE to_char(dane_data_od, 'YYYY-MM-DD'),
'dane_data_do' VALUE to_char(dane_data_do, 'YYYY-MM-DD'),
'rola' VALUE rola,
'dane_sformatowane' VALUE TO_CLOB(dane_sformatowane),
'typ' VALUE
CASE
WHEN czy_osoba_fizyczna = 1 THEN
'Osoba'
ELSE
'Podmiot'
END
) RETURNING CLOB)
FROM (
SELECT DISTINCT
id_podmiot_b,
dane_data_od,
dane_data_do,
rola,
CAST(dane_sformatowane as VARCHAR2(4000)) as dane_sformatowane,
czy_osoba_fizyczna
FROM kk_podmiot_powiazania pow
INNER JOIN {schema}.kk_podmiot pod ON pod.id_podmiot = pow.id_podmiot_b
WHERE
id_podmiot_a = :id_podmiotu
AND ( upper(klasa_roli) LIKE 'WSPÓLNICY%'
OR upper(klasa_roli) LIKE 'KOMITET%'
OR upper(klasa_roli) LIKE 'PARTNERZY%'
OR upper(klasa_roli) LIKE 'KOMPLEMENTARIUSZE%' )
)
) AS wspolnicy,
(
SELECT
JSON_ARRAYAGG(JSON_OBJECT(
'id_podmiot' VALUE id_podmiot_b,
'dane_data_od' VALUE to_char(dane_data_od, 'YYYY-MM-DD'),
'dane_data_do' VALUE to_char(dane_data_do, 'YYYY-MM-DD'),
'rola' VALUE rola,
'dane_sformatowane' VALUE TO_CLOB(dane_sformatowane),
'typ' VALUE
CASE
WHEN czy_osoba_fizyczna = 1 THEN
'Osoba'
ELSE
'Podmiot'
END
) RETURNING CLOB)
FROM
(
SELECT DISTINCT
id_podmiot_b,
dane_data_od,
dane_data_do,
rola,
CAST(dane_sformatowane as VARCHAR2(4000)) as dane_sformatowane,
czy_osoba_fizyczna
FROM kk_podmiot_powiazania pow
INNER JOIN {schema}.kk_podmiot pod ON pod.id_podmiot = pow.id_podmiot_b
WHERE
id_podmiot_a = :id_podmiotu
AND ( upper(klasa_roli) LIKE 'ZARZĄD%' )
)
) AS organ_uprawniony_do_reprezentowania_spolki,
(
SELECT
JSON_ARRAYAGG(JSON_OBJECT(
'id_podmiot' VALUE id_podmiot_b,
'dane_data_od' VALUE to_char(dane_data_od, 'YYYY-MM-DD'),
'dane_data_do' VALUE to_char(dane_data_do, 'YYYY-MM-DD'),
'rola' VALUE rola,
'dane_sformatowane' VALUE TO_CLOB(dane_sformatowane),
'typ' VALUE
CASE
WHEN czy_osoba_fizyczna = 1 THEN
'Osoba'
ELSE
'Podmiot'
END
) RETURNING CLOB)
FROM
(
SELECT DISTINCT
id_podmiot_b,
dane_data_od,
dane_data_do,
rola,
CAST(dane_sformatowane as VARCHAR2(4000)) as dane_sformatowane,
czy_osoba_fizyczna
FROM kk_podmiot_powiazania pow
INNER JOIN {schema}.kk_podmiot pod ON pod.id_podmiot = pow.id_podmiot_b
WHERE
id_podmiot_a = :id_podmiotu
AND ( upper(klasa_roli) LIKE 'RADA%' )
)
) AS organ_nadzoru,
(
SELECT
JSON_ARRAYAGG(JSON_OBJECT(
'id_podmiot' VALUE id_podmiot_b,
'dane_data_od' VALUE to_char(dane_data_od, 'YYYY-MM-DD'),
'dane_data_do' VALUE to_char(dane_data_do, 'YYYY-MM-DD'),
'rola' VALUE rola,
'dane_sformatowane' VALUE TO_CLOB(dane_sformatowane),
'typ' VALUE
CASE
WHEN czy_osoba_fizyczna = 1 THEN
'Osoba'
ELSE
'Podmiot'
END
) RETURNING CLOB)
FROM (
SELECT DISTINCT
id_podmiot_b,
dane_data_od,
dane_data_do,
rola,
CAST(dane_sformatowane as VARCHAR2(4000)) as dane_sformatowane,
czy_osoba_fizyczna
FROM kk_podmiot_powiazania pow
INNER JOIN {schema}.kk_podmiot pod ON pod.id_podmiot = pow.id_podmiot_b
WHERE
id_podmiot_a = :id_podmiotu
AND ( upper(klasa_roli) LIKE 'PROKUREN%' )
)
) AS prokurenci,
(
SELECT
JSON_ARRAYAGG(JSON_OBJECT(
'id_podmiot' VALUE id_podmiot_b,
'dane_data_od' VALUE to_char(dane_data_od, 'YYYY-MM-DD'),
'dane_data_do' VALUE to_char(dane_data_do, 'YYYY-MM-DD'),
'rola' VALUE rola,
'dane_sformatowane' VALUE TO_CLOB(dane_sformatowane),
'typ' VALUE
CASE
WHEN czy_osoba_fizyczna = 1 THEN
'Osoba'
ELSE
'Podmiot'
END
) RETURNING CLOB)
FROM (
SELECT DISTINCT
id_podmiot_b,
dane_data_od,
dane_data_do,
rola,
CAST(dane_sformatowane as VARCHAR2(4000)) as dane_sformatowane,
czy_osoba_fizyczna
FROM kk_podmiot_powiazania pow
INNER JOIN {schema}.kk_podmiot pod ON pod.id_podmiot = pow.id_podmiot_b
WHERE
id_podmiot_a = :id_podmiotu
AND ( upper(klasa_roli) = 'CRBR' )
AND ( upper(rola) = 'BENEFICJENT RZECZYWISTY' )
)
) AS beneficjenci_rzeczywisci,
(
SELECT
JSON_ARRAYAGG(JSON_OBJECT(
'id_podmiot' VALUE id_podmiot_b,
'dane_data_od' VALUE to_char(dane_data_od, 'YYYY-MM-DD'),
'dane_data_do' VALUE to_char(dane_data_do, 'YYYY-MM-DD'),
'rola' VALUE rola,
'dane_sformatowane' VALUE TO_CLOB(dane_sformatowane),
'typ' VALUE
CASE
WHEN czy_osoba_fizyczna = 1 THEN
'Osoba'
ELSE
'Podmiot'
END
) RETURNING CLOB)
FROM (
SELECT DISTINCT
id_podmiot_b,
dane_data_od,
dane_data_do,
rola,
CAST(dane_sformatowane as VARCHAR2(4000)) as dane_sformatowane,
czy_osoba_fizyczna
FROM kk_podmiot_powiazania pow
INNER JOIN {schema}.kk_podmiot pod ON pod.id_podmiot = pow.id_podmiot_b
WHERE
id_podmiot_a = :id_podmiotu
AND ( upper(klasa_roli) = 'CRBR' )
AND ( upper(rola) = 'REPREZENTANT' )
)
) AS reprezentanci,
(
SELECT
JSON_ARRAYAGG(JSON_OBJECT(
'id_podmiot' VALUE id_podmiot_b,
'dane_data_od' VALUE to_char(dane_data_od, 'YYYY-MM-DD'),
'dane_data_do' VALUE to_char(dane_data_do, 'YYYY-MM-DD'),
'rola' VALUE rola,
'dane_sformatowane' VALUE dane_sformatowane,
'typ' VALUE
CASE
WHEN czy_osoba_fizyczna = 1 THEN
'Osoba'
ELSE
'Podmiot'
END
RETURNING CLOB
) RETURNING CLOB)
FROM
kk_podmiot_powiazania pow
INNER JOIN {schema}.kk_podmiot pod ON pod.id_podmiot = pow.id_podmiot_b
WHERE
-- CZY_NAJNOWSZE_POZYSKANE = 1
id_podmiot_a = :id_podmiotu
AND ( upper(klasa_roli) = 'WŁAŚCICIEL' )
AND ( upper(rola) = 'WŁAŚCICIEL' )
) AS wlasciciel,
(
select
json_arrayagg ( json_object (
'id_podmiot' value id_podmiot_a,
'dane_data_od' value dane_data_od,
'dane_data_do' value dane_data_do,
'rola' value rola,
'dane_sformatowane' value to_clob(json_mergepatch(dane_sformatowane,(
select
json_object(
key 'nazwa' value nazwa
)
from
kk_podmiot
where
id_podmiot = id_podmiot_a))),
'typ' value typ
) returning clob )
from
(
select distinct
id_podmiot_a as id_podmiot_a,
to_char(dane_data_od, 'YYYY-MM-DD') as dane_data_od,
to_char(dane_data_do, 'YYYY-MM-DD') as dane_data_do,
case
when upper(rola) = 'WSPÓLNIK' then
'WSPÓLNIK'
when upper(rola) = 'ZAŁOŻYCIEL' then
'ZAŁOŻYCIEL'
when upper(rola) = 'PARTNER' then
'PARTNER'
when upper(rola) = 'KOMPLEMENTARIUSZ' then
'KOMPLEMENTARIUSZ'
end as rola,
TO_CHAR(dane_sformatowane) as dane_sformatowane,
case
when czy_osoba_fizyczna = 1 then
'Osoba'
else
'Podmiot'
end as typ
from
{schema}.kk_podmiot_powiazania pow
inner join {schema}.kk_podmiot pod on pod.id_podmiot = pow.id_podmiot_a
where
id_podmiot_b = :id_podmiotu
and ( upper(klasa_roli) like 'WSPÓLNICY%'
or upper(klasa_roli) like 'KOMITET%'
or upper(klasa_roli) like 'PARTNERZY%'
or upper(klasa_roli) like 'KOMPLEMENTARIUSZE%' )
)
) as udzialowcy,
(
select
json_arrayagg ( json_object (
'id_podmiot' value id_podmiot_a,
'dane_data_od' value dane_data_od,
'dane_data_do' value dane_data_do,
'rola' value rola,
'dane_sformatowane' value to_clob(json_mergepatch(dane_sformatowane,(
select
json_object(
key 'nazwa' value nazwa
)
from
kk_podmiot
where
id_podmiot = id_podmiot_a))),
'typ' value typ
) returning clob )
from
(
select distinct
id_podmiot_a as id_podmiot_a,
to_char(dane_data_od, 'YYYY-MM-DD') as dane_data_od,
to_char(dane_data_do, 'YYYY-MM-DD') as dane_data_do,
case
when upper(rola) = 'AKCJONARIUSZ' then
'AKCJONARIUSZ'
end as rola,
TO_CHAR(dane_sformatowane) as dane_sformatowane,
case
when czy_osoba_fizyczna = 1 then
'Osoba'
else
'Podmiot'
end as typ
from
{schema}.kk_podmiot_powiazania pow
inner join {schema}.kk_podmiot pod on pod.id_podmiot = pow.id_podmiot_a
where
id_podmiot_b = :id_podmiotu
and ( upper(klasa_roli) like 'AKCJONARIUSZE%' )
)
) as spolka_akcjonariusza,
(
select
json_arrayagg ( json_object (
'id_podmiot' value id_podmiot_a,
'dane_data_od' value dane_data_od,
'dane_data_do' value dane_data_do,
'rola' value rola,
'dane_sformatowane' value to_clob(json_mergepatch(dane_sformatowane,(
select
json_object(
key 'nazwa' value nazwa
)
from
kk_podmiot
where
id_podmiot = id_podmiot_a))),
'typ' value typ
) returning clob )
from
(
select distinct
id_podmiot_a as id_podmiot_a,
to_char(dane_data_od, 'YYYY-MM-DD') as dane_data_od,
to_char(dane_data_do, 'YYYY-MM-DD') as dane_data_do,
case
when upper(rola) = 'PROKURENT' then -- dac tu wiecej z klasa_roli?
'PROKURENT'
end as rola,
TO_CHAR(dane_sformatowane) as dane_sformatowane,
case
when czy_osoba_fizyczna = 1 then
'Osoba'
else
'Podmiot'
end as typ
from
{schema}.kk_podmiot_powiazania pow
inner join {schema}.kk_podmiot pod on pod.id_podmiot = pow.id_podmiot_a
where
id_podmiot_b = :id_podmiotu
and ( upper(klasa_roli) like 'PROKURENCI%')
)
) as spolka_prokurenta,
(
select
json_arrayagg ( json_object (
'id_podmiot' value id_podmiot_a,
'dane_data_od' value dane_data_od,
'dane_data_do' value dane_data_do,
'rola' value rola,
'dane_sformatowane' value to_clob(json_mergepatch(dane_sformatowane,(
select
json_object(
key 'nazwa' value nazwa
)
from
kk_podmiot
where
id_podmiot = id_podmiot_a))),
'typ' value typ
) returning clob )
from
(
select distinct
id_podmiot_a as id_podmiot_a,
to_char(dane_data_od, 'YYYY-MM-DD') as dane_data_od,
to_char(dane_data_do, 'YYYY-MM-DD') as dane_data_do,
case
when upper(rola) = 'WŁAŚCICIEL' then
'WŁAŚCICIEL'
end as rola,
TO_CHAR(dane_sformatowane) as dane_sformatowane,
case
when czy_osoba_fizyczna = 1 then
'Osoba'
else
'Podmiot'
end as typ
from
{schema}.kk_podmiot_powiazania pow
inner join {schema}.kk_podmiot pod on pod.id_podmiot = pow.id_podmiot_a
where
id_podmiot_b = :id_podmiotu
and ( upper(klasa_roli) like 'WŁAŚCICIEL%')
)
) as spolka_wlascicela,
(
select
json_arrayagg ( json_object (
'id_podmiot' value id_podmiot_a,
'dane_data_od' value dane_data_od,
'dane_data_do' value dane_data_do,
'rola' value rola,
'dane_sformatowane' value to_clob(json_mergepatch(dane_sformatowane,(
select
json_object(
key 'nazwa' value nazwa
)
from
kk_podmiot
where
id_podmiot = id_podmiot_a))),
'typ' value typ
) returning clob )
from
(
select distinct
id_podmiot_a as id_podmiot_a,
to_char(dane_data_od, 'YYYY-MM-DD') as dane_data_od,
to_char(dane_data_do, 'YYYY-MM-DD') as dane_data_do,
case
when upper(rola) = 'CZŁ. ORG. NADZORU' then
'CZŁ. ORG. NADZORU'
end as rola,
TO_CHAR(dane_sformatowane) as dane_sformatowane,
case
when czy_osoba_fizyczna = 1 then
'Osoba'
else
'Podmiot'
end as typ
from
{schema}.kk_podmiot_powiazania pow
inner join {schema}.kk_podmiot pod on pod.id_podmiot = pow.id_podmiot_a
where
id_podmiot_b = :id_podmiotu
and ( upper(klasa_roli) like 'RADA%')
)
) as spolka_organu_nadzoru,
(
select
json_arrayagg ( json_object (
'id_podmiot' value id_podmiot_a,
'dane_data_od' value dane_data_od,
'dane_data_do' value dane_data_do,
'rola' value rola,
'dane_sformatowane' value to_clob(json_mergepatch(dane_sformatowane,(
select
json_object(
key 'nazwa' value nazwa
)
from
kk_podmiot
where
id_podmiot = id_podmiot_a))),
'typ' value typ
) returning clob )
from
(
select distinct
id_podmiot_a as id_podmiot_a,
to_char(dane_data_od, 'YYYY-MM-DD') as dane_data_od,
to_char(dane_data_do, 'YYYY-MM-DD') as dane_data_do,
case
when upper(rola) = 'BENEFICJENT RZECZYWISTY' then
'BENEFICJENT RZECZYWISTY'
end as rola,
TO_CHAR(dane_sformatowane) as dane_sformatowane,
case
when czy_osoba_fizyczna = 1 then
'Osoba'
else
'Podmiot'
end as typ
from
{schema}.kk_podmiot_powiazania pow
inner join {schema}.kk_podmiot pod on pod.id_podmiot = pow.id_podmiot_a
where
id_podmiot_b = :id_podmiotu
and ( upper(klasa_roli) like 'CRBR%')
)
) as spolka_beneficjenta_rzeczywistego,
(
select
json_arrayagg ( json_object (
'id_podmiot' value id_podmiot_a,
'dane_data_od' value dane_data_od,
'dane_data_do' value dane_data_do,
'rola' value rola,
'dane_sformatowane' value to_clob(json_mergepatch(dane_sformatowane,(
select
json_object(
key 'nazwa' value nazwa
)
from
kk_podmiot
where
id_podmiot = id_podmiot_a))),
'typ' value typ
) returning clob )
from
(
select distinct
id_podmiot_a as id_podmiot_a,
to_char(dane_data_od, 'YYYY-MM-DD') as dane_data_od,
to_char(dane_data_do, 'YYYY-MM-DD') as dane_data_do,
case
when upper(rola) = 'REPREZENTANT' then
'REPREZENTANT'
end as rola,
TO_CHAR(dane_sformatowane) as dane_sformatowane,
case
when czy_osoba_fizyczna = 1 then
'Osoba'
else
'Podmiot'
end as typ
from
{schema}.kk_podmiot_powiazania pow
inner join {schema}.kk_podmiot pod on pod.id_podmiot = pow.id_podmiot_a
where
id_podmiot_b = :id_podmiotu
and ( upper(klasa_roli) like 'CRBR%')
)
) as spolka_reprezentanta
from
dual
--endsql
"""Editor is loading...
Leave a Comment