Hvilke CCL'er og Exports har en oplæggelse "ramt"?

Problem

Omend det er muligt at se hvilke Exports og CCL'er der er dannet ud fra import-oplæggelsen direkte fra GUI'en, så kan man ikke se hvilke vareposter, de er endt på.

Det er et problem for brugeren hvis denne ønsker at lave en manuel rettelsesangivelse hos SKAT.

Løsning (i det mindste midlertidig)

Eksempel.

imports-id:

select *
from lts.imports i
where IMPORT_REFERENCE='2017122703324';

/* EXPORTS */

select ili.line_number imp_line_number, e.export_number, e.export_reference, eil.line_number exp_line_number, sum(oi.quantity)
from lts.import_line_items ili
join lts.stock_items si on si.IMPORT_LINE_ITEMS_ID = ili.IMPORT_LINE_ITEMS_ID
join lts.outbound_items oi on oi.STOCK_ITEMS_ID = si.STOCK_ITEMS_ID
join lts.outbound_pool op on op.OUTBOUND_POOL_ID = oi.OUTBOUND_POOL_ID
join lts.export_line_items eil on eil.EXPORT_LINE_ITEMS_ID = op.EXPORT_LINE_ITEMS_ID
join lts.exports e on e.exports_id = eil.EXPORTS_ID
where ili.imports_id=20641
and oi.deleted = 'N'
and op.deleted = 'N'
and oi.ccl_items_id is null
group by ili.line_number, e.export_number, e.export_reference, eil.line_number
order by 1,3,4;

-- det bemærkes her, at varer der er både er kommet på en export og en ccl er frasorteret i ovenstående. Det sker hvis en bruger har trykket på T2 knappen.

/* IMPORTS */

select ili.line_number imp_line_number, i2.import_number ccl_import_number, i2.import_reference ccl_reference,ili2.line_number ccl_line_number, sum(oi.quantity)
from lts.import_line_items ili
join lts.stock_items si on si.IMPORT_LINE_ITEMS_ID = ili.IMPORT_LINE_ITEMS_ID
join lts.outbound_items oi on oi.STOCK_ITEMS_ID = si.STOCK_ITEMS_ID
join lts.ccl_items ci on ci.CCL_ITEMS_ID = oi.CCL_ITEMS_ID
join lts.import_line_items ili2 on ili2.import_line_items_id = ci.IMPORT_LINE_ITEMS_ID
join lts.imports i2 on i2.imports_id = ili2.imports_id
where ili.imports_id=20641
and oi.deleted = 'N'
group by ili.line_number , i2.import_number , i2.import_reference ,ili2.line_number
order by 1,3,4;

Extra

Hvis man også ønsker at se statistisk værdi på CCL'erne:

select ili.line_number imp_line_number,
i2.import_number ccl_import_number,
i2.import_reference ccl_reference,
ili2.line_number ccl_line_number,
sum(oi.quantity) quantity_from_import,
ili2.quantity ccl_quantity,
(select round(sum(amount),2) ccl_statistical_value from stat_value_components svc where svc.import_line_items_id=ili2.import_line_items_id) ccl_stat_value
from lts.import_line_items ili
join lts.stock_items si on si.IMPORT_LINE_ITEMS_ID = ili.IMPORT_LINE_ITEMS_ID
join lts.outbound_items oi on oi.STOCK_ITEMS_ID = si.STOCK_ITEMS_ID
join lts.ccl_items ci on ci.CCL_ITEMS_ID = oi.CCL_ITEMS_ID
left join lts.import_line_items ili2 on ili2.import_line_items_id = ci.IMPORT_LINE_ITEMS_ID
left join lts.imports i2 on i2.imports_id = ili2.imports_id
where ili.imports_id=20641
and oi.deleted = 'N'
group by ili.line_number , i2.import_number , i2.import_reference ,ili2.line_number, ili2.import_line_items_id, ili2.quantity
order by 1,3,4;

Har du flere spørgsmål? Send en anmodning

0 Kommentarer

Log ind for at kommentere.
Drevet af Zendesk