By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE "XXSAMPLE2"
(
"JG_INFO_V1" VARCHAR2(240 BYTE),
"JG_INFO_V14" VARCHAR2(150 BYTE),
"JG_INFO_V16" VARCHAR2(150 BYTE),
"JG_INFO_V21" VARCHAR2(150 BYTE),
"JG_INFO_V32" VARCHAR2(1996 BYTE),
"JG_INFO_N3" NUMBER,
"JG_INFO_N4" NUMBER,
"JG_INFO_N11" NUMBER,
"JG_INFO_N15" NUMBER,
"JG_INFO_N30" NUMBER
);
begin
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',3225.79,225.81,842,58.94,'VAT',859634,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',421.42,50.57,110,13.2,'KOREKTA',859635,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',490.38,49.04,128,12.8,'KOREKTA',859635,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',122.6,6.13,32,1.6,'VAT',860621,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',3225.79,225.81,842,58.94,'VAT',860621,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',-100,-10,-100,-10,'KOREKTA',859638,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',-87,-10.44,-87,-10.44,'KOREKTA',859638,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',40,4.4,40,4.4,'KOREKTA',859638,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',-3225.79,-225.81,-842,-58.94,'VAT',860622,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',-122.6,-6.13,-32,-1.6,'VAT',860622,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',367.79,40.46,96,10.56,'KOREKTA',860622,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',3225.79,322.58,842,84.2,'KOREKTA',860622,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',30.06,3.01,30.06,3.01,'KOREKTA',863622,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',74,8.88,74,8.88,'KOREKTA',863622,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',30.06,3.01,30.06,3.01,'KOREKTA',863625,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',74,8.14,74,8.14,'KOREKTA',863625,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',115.16,11.53,30.06,3.01,'KOREKTA',863626,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',283.5,34.02,74,8.88,'KOREKTA',863626,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',-200,-14,-200,-14,'VAT',860619,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',-123,-8.61,-123,-8.61,'VAT',860619,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',-72,-3.6,-72,-3.6,'VAT',860619,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',48,4.8,48,4.8,'KOREKTA',860619,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',200,24,200,24,'KOREKTA',860619,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',369,40.59,369,40.59,'KOREKTA',860619,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('H','Krakowska',null,null,null,null,18,'AV',null,null);
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',100,11,100,11,'KOREKTA',859619,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',50,5.5,50,5.5,'KOREKTA',859624,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',144,17.28,144,17.28,'KOREKTA',859624,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',-360.12,-36.01,-94,-9.4,'KOREKTA',859639,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',-122.6,-13.49,-32,-3.52,'KOREKTA',859639,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',114.93,13.79,30,3.6,'KOREKTA',859639,'EUR');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',100,7,100,7,'VAT',859618,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',72,3.6,72,3.6,'VAT',859621,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',123,8.61,123,8.61,'VAT',859621,'PLN');
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',200,14,200,14,'VAT',859621,'PLN');
1 rows affected
Select invoice_id
, Invoice_Type
, BOX
, functional_box
, ROUND(sum(ENTERED_AMT), 2) ENTERED_AMT_TOT
, ROUND(sum(FUNC_AMT),2) FUNC_AMT_TOT
from (
Select jg_info_v14 box -- taxable_box
, Case When jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' Then jg_info_v14||'W' else null end functional_box
, jg_info_n3 func_amt -- taxable_amt
, jg_info_n15 entered_amt -- entered_taxable_amount
, jg_info_v21 Invoice_Type
, jg_info_n11 invoice_id
, jg_info_v32 invoice_currency_code
From XXSAMPLE2
WHERE jg_info_v1 IN ('AR', 'AP')
and jg_info_v14 <> 'tns:'
union all
Select jg_info_v16 box -- tax_box
, Case When jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' Then jg_info_v16||'W' else null end functional_box
, jg_info_n4 func_amt -- tax_amt
, jg_info_n30 entered_amt -- entered_tax_amount
, jg_info_v21 Invoice_Type
, jg_info_n11 invoice_id
, jg_info_v32 invoice_currency_code
From XXSAMPLE2
WHERE jg_info_v1 IN ('AR', 'AP')
and jg_info_v16 <> 'tns:'
) tax_info
WHERE invoice_id = 859639
group by
tax_info.invoice_id
, tax_info.Invoice_Type
, tax_info.BOX
, tax_info.functional_box
;
| INVOICE_ID | INVOICE_TYPE | BOX | FUNCTIONAL_BOX | ENTERED_AMT_TOT | FUNC_AMT_TOT |
|---|---|---|---|---|---|
| 859639 | KOREKTA | tns:P_14_5 | tns:P_14_5W | -3.52 | -13.49 |
| 859639 | KOREKTA | tns:P_13_5 | null | -32 | -122.6 |
| 859639 | KOREKTA | tns:P_14_3 | tns:P_14_3W | 3.6 | 13.79 |
| 859639 | KOREKTA | tns:P_13_3 | null | 30 | 114.93 |
| 859639 | KOREKTA | tns:P_13_4 | null | -94 | -360.12 |
select jg_info_n11 invoice_id
, jg_info_v21 invoice_type
, jg_info_v32 currency_code
, case when jg_info_v14 = 'tns:' then null
else jg_info_v14
end as v14_box
, case when jg_info_v14 = 'tns:' then null
else jg_info_n15
end as v14_amount
, case when jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_v14||'W'
end as v14_func_box
, case when jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_n3
end as v14_func_amount
, case when jg_info_v16 = 'tns:' then null
else jg_info_v16
end as v16_box
, case when jg_info_v16 = 'tns:' then null
else jg_info_n30
end as v16_amount
, case when jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_v16||'W'
end as v16_func_box
, case when jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_n4
end as v16_func_amount
from XXSAMPLE2
where jg_info_v1 IN ('AR', 'AP')
and jg_info_n11 = 859639;
| INVOICE_ID | INVOICE_TYPE | CURRENCY_CODE | V14_BOX | V14_AMOUNT | V14_FUNC_BOX | V14_FUNC_AMOUNT | V16_BOX | V16_AMOUNT | V16_FUNC_BOX | V16_FUNC_AMOUNT |
|---|---|---|---|---|---|---|---|---|---|---|
| 859639 | KOREKTA | EUR | tns:P_13_4 | -94 | null | null | null | null | null | null |
| 859639 | KOREKTA | EUR | tns:P_13_5 | -32 | null | null | tns:P_14_5 | -3.52 | tns:P_14_5W | -13.49 |
| 859639 | KOREKTA | EUR | tns:P_13_3 | 30 | null | null | tns:P_14_3 | 3.6 | tns:P_14_3W | 13.79 |
select invoice_id, invoice_type, box, amount
from (
select jg_info_n11 invoice_id
, jg_info_v21 invoice_type
, jg_info_v32 currency_code
, case when jg_info_v14 = 'tns:' then null
else jg_info_v14
end as v14_box
, case when jg_info_v14 = 'tns:' then null
else jg_info_n15
end as v14_amount
, case when jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_v14||'W'
end as v14_func_box
, case when jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_n3
end as v14_func_amount
, case when jg_info_v16 = 'tns:' then null
else jg_info_v16
end as v16_box
, case when jg_info_v16 = 'tns:' then null
else jg_info_n30
end as v16_amount
, case when jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_v16||'W'
end as v16_func_box
, case when jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_n4
end as v16_func_amount
from XXSAMPLE2
where jg_info_v1 IN ('AR', 'AP')
and jg_info_n11 = 859639
)
unpivot (
(box, amount)
for x in (
(v14_box, v14_amount) as 1,
(v14_func_box, v14_func_amount) as 2,
(v16_box, v16_amount) as 3,
(v16_func_box, v16_func_amount) as 4
| INVOICE_ID | INVOICE_TYPE | BOX | AMOUNT |
|---|---|---|---|
| 859639 | KOREKTA | tns:P_13_4 | -94 |
| 859639 | KOREKTA | tns:P_13_5 | -32 |
| 859639 | KOREKTA | tns:P_14_5 | -3.52 |
| 859639 | KOREKTA | tns:P_14_5W | -13.49 |
| 859639 | KOREKTA | tns:P_13_3 | 30 |
| 859639 | KOREKTA | tns:P_14_3 | 3.6 |
| 859639 | KOREKTA | tns:P_14_3W | 13.79 |