begin
--Intrastat pro elektronické hláseni do 2008
/* Faktury vydane */
for
select
'A' as Status,
IE.Code as extratype,
A.IntrastatAmount as IntrastatAmount,
A.LocalIntrastatAmount as LocalIntrastatAmount,
A.StatisticAmount as StatisticAmount,
P.Currency_ID as Currency_ID,
CT.Code as CountryCode,
IR.Code as IntrastatRegionCode,
IC.Code as CommodityCode,
IDT.Code as DeliveryTermCode,
ITT.Code as TransportationTypeCode,
IT.Code as TransactionTypeCode,
IC.UnitCode as IntrastatUnitCode,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) as IntrastatQuantity,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) * CAST(S.IntrastatWeight AS DOUBLE PRECISION) as Weight,
S.IntrastatWeightUnit as WeightUnit,
OCT.Code as OriginCountryCode,
OCT.ID as IntrastatOriginCountry_ID,
IC.WeightIsOptional as WeightIsOptional,
IC.Description as CommodityDescription ,
IC.ConstantWeight as ConstantWeight,
CT.ID as Country_ID,
IR.ID as IntrastatRegion_ID,
IC.ID as Commodity_ID,
IDT.ID as DeliveryTerm_ID,
ITT.ID as TransportationType_ID,
IT.ID as TransactionType_ID,
IE.ID as Extra_ID,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
F.TAXIdentNumber as TAXIdentNumber
from
IssuedInvoices2 A
join IssuedInvoices P on P.ID=A.Parent_ID
left join StoreCards S on S.ID=A.StoreCard_ID
join Countries CT on CT.ID=P.Country_ID
left join Countries OCT on OCT.ID=S.Country_ID
left join IntrastatRegions IR on IR.ID=A.IntrastatRegion_ID
left join IntrastatCommodities IC on IC.ID=S.IntrastatCommodity_ID
left join IntrastatExtraTypes IE on IE.ID=S.IntrastatExtraType_ID
left join IntrastatDeliveryTerms IDT on IDT.ID=P.IntrastatDeliveryTerm_ID
left join IntrastatTransportationTypes ITT on ITT.ID=P.IntrastatTransportationType_ID
left join IntrastatTransactionTypes IT on IT.ID=P.IntrastatTransactionType_ID
left join Firms F on F.ID=P.Firm_ID
where
A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8)
AND (P.VATDate$Date >= :avatdatefrom and P.VATDate$Date < :avatdateto) and P.VATDocument = 'A'
AND ((:afirmselid = ''
OR (P.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (P.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
AND ((:acountryselid = ''
OR (P.Country_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :acountryselid))
))
AND ((:acurrencyselid = ''
OR (P.currency_id IN (SELECT obj_id FROM seldat WHERE sel_id = :acurrencyselid))
))
AND ((:aStoreSelID = ''
OR (A.Store_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreSelID))
))
AND ((:aStoreCardselid = ''
OR (A.StoreCard_id IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreCardselid))
))
AND ((:aCreatedBySelID = ''
OR (P.CreatedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCreatedBySelID))
))
AND ((:aCorrectedBySelID = ''
OR (P.CorrectedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCorrectedBySelID))
))
into
:status, :extratype, :intrastatamount, :localintrastatamount, :statisticamount, :currency_id,
:countrycode, :intrastatregioncode, :commoditycode, :deliverytermcode, :transportationtypecode, :transactiontypecode, :intrastatunitcode,
:IntrastatQuantity, :Weight, :weightunit, :origincountrycode, :intrastatorigincountry_id, :weightisoptional, :commoditydescription, :constantweight,
:country_id, :intrastatregion_id, :commodity_id, :deliveryterm_id, :transportationtype_id, :transactiontype_id, :extra_id,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id, :taxidentnumber
do begin
EXECUTE PROCEDURE checkdivisionandbuswithnull
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds,
:abusorderswithnull, :abustransactionswithnull, :abusprojectswithnull
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN
BEGIN
suspend;
end
end
--**************************************************
/* Pokladni prijmy */
for
select
'A' as Status,
IE.Code as extratype,
A.IntrastatAmount as IntrastatAmount,
A.LocalIntrastatAmount as LocalIntrastatAmount,
A.StatisticAmount as StatisticAmount,
P.Currency_ID as Currency_ID,
CT.Code as CountryCode,
IR.Code as IntrastatRegionCode,
IC.Code as CommodityCode,
IDT.Code as DeliveryTermCode,
ITT.Code as TransportationTypeCode,
IT.Code as TransactionTypeCode,
IC.UnitCode as IntrastatUnitCode,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) as IntrastatQuantity,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) * CAST(S.IntrastatWeight AS DOUBLE PRECISION) as Weight,
S.IntrastatWeightUnit as WeightUnit,
OCT.Code as OriginCountryCode,
OCT.ID as IntrastatOriginCountry_ID,
IC.WeightIsOptional as WeightIsOptional,
IC.Description as CommodityDescription ,
IC.ConstantWeight as ConstantWeight,
CT.ID as Country_ID,
IR.ID as IntrastatRegion_ID,
IC.ID as Commodity_ID,
IDT.ID as DeliveryTerm_ID,
ITT.ID as TransportationType_ID,
IT.ID as TransactionType_ID,
IE.ID as Extra_ID,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
F.TAXIdentNumber as TAXIdentNumber
from
CashReceived2 A
join CashReceived P on P.ID=A.Parent_ID
left join StoreCards S on S.ID=A.StoreCard_ID
join Countries CT on CT.ID=P.Country_ID
left join Countries OCT on OCT.ID=S.Country_ID
left join IntrastatRegions IR on IR.ID=A.IntrastatRegion_ID
left join IntrastatCommodities IC on IC.ID=S.IntrastatCommodity_ID
left join IntrastatExtraTypes IE on IE.ID=S.IntrastatExtraType_ID
left join IntrastatDeliveryTerms IDT on IDT.ID=P.IntrastatDeliveryTerm_ID
left join IntrastatTransportationTypes ITT on ITT.ID=P.IntrastatTransportationType_ID
left join IntrastatTransactionTypes IT on IT.ID=P.IntrastatTransactionType_ID
left join Firms F on F.ID=P.Firm_ID
where
A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8)
AND (P.VATDate$Date >= :avatdatefrom and P.VATDate$Date < :avatdateto) and P.VATDocument = 'A'
AND ((:afirmselid = ''
OR (P.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (P.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
AND ((:acountryselid = ''
OR (P.Country_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :acountryselid))
))
AND ((:acurrencyselid = ''
OR (P.currency_id IN (SELECT obj_id FROM seldat WHERE sel_id = :acurrencyselid))
))
AND ((:aStoreSelID = ''
OR (A.Store_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreSelID))
))
AND ((:aStoreCardselid = ''
OR (A.StoreCard_id IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreCardselid))
))
AND ((:aCreatedBySelID = ''
OR (P.CreatedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCreatedBySelID))
))
AND ((:aCorrectedBySelID = ''
OR (P.CorrectedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCorrectedBySelID))
))
into
:status, :extratype, :intrastatamount, :localintrastatamount, :statisticamount, :currency_id,
:countrycode, :intrastatregioncode, :commoditycode, :deliverytermcode, :transportationtypecode, :transactiontypecode, :intrastatunitcode,
:IntrastatQuantity, :Weight, :weightunit, :origincountrycode, :intrastatorigincountry_id, :weightisoptional, :commoditydescription, :constantweight,
:country_id, :intrastatregion_id, :commodity_id, :deliveryterm_id, :transportationtype_id, :transactiontype_id, :extra_id,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id, :taxidentnumber
do begin
EXECUTE PROCEDURE checkdivisionandbuswithnull
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds,
:abusorderswithnull, :abustransactionswithnull, :abusprojectswithnull
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN
BEGIN
suspend;
end
end
--**************************************************
/* Dobropisy faktur vydanych */
for
select
'N' as Status,
IE.Code as extratype,
A.IntrastatAmount as IntrastatAmount,
A.LocalIntrastatAmount as LocalIntrastatAmount,
A.StatisticAmount as StatisticAmount,
P.Currency_ID as Currency_ID,
CT.Code as CountryCode,
IR.Code as IntrastatRegionCode,
IC.Code as CommodityCode,
IDT.Code as DeliveryTermCode,
ITT.Code as TransportationTypeCode,
IT.Code as TransactionTypeCode,
IC.UnitCode as IntrastatUnitCode,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) as IntrastatQuantity,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) * CAST(S.IntrastatWeight AS DOUBLE PRECISION) as Weight,
S.IntrastatWeightUnit as WeightUnit,
OCT.Code as OriginCountryCode,
OCT.ID as IntrastatOriginCountry_ID,
IC.WeightIsOptional as WeightIsOptional,
IC.Description as CommodityDescription ,
IC.ConstantWeight as ConstantWeight,
CT.ID as Country_ID,
IR.ID as IntrastatRegion_ID,
IC.ID as Commodity_ID,
IDT.ID as DeliveryTerm_ID,
ITT.ID as TransportationType_ID,
IT.ID as TransactionType_ID,
IE.ID as Extra_ID,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
F.TAXIdentNumber as TAXIdentNumber
from
IssuedCreditNotes2 A
join IssuedCreditNotes P on P.ID=A.Parent_ID
left join StoreCards S on S.ID=A.StoreCard_ID
join Countries CT on CT.ID=P.Country_ID
left join Countries OCT on OCT.ID=S.Country_ID
left join IntrastatRegions IR on IR.ID=A.IntrastatRegion_ID
left join IntrastatCommodities IC on IC.ID=S.IntrastatCommodity_ID
left join IntrastatExtraTypes IE on IE.ID=S.IntrastatExtraType_ID
left join IntrastatDeliveryTerms IDT on IDT.ID=P.IntrastatDeliveryTerm_ID
left join IntrastatTransportationTypes ITT on ITT.ID=P.IntrastatTransportationType_ID
left join IntrastatTransactionTypes IT on IT.ID=P.IntrastatTransactionType_ID
left join Firms F on F.ID=P.Firm_ID
where
A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8)
AND (P.VATDate$Date >= :avatdatefrom and P.VATDate$Date < :avatdateto) and P.VATDocument = 'A'
AND ((:afirmselid = ''
OR (P.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (P.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
AND ((:acountryselid = ''
OR (P.Country_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :acountryselid))
))
AND ((:acurrencyselid = ''
OR (P.currency_id IN (SELECT obj_id FROM seldat WHERE sel_id = :acurrencyselid))
))
AND ((:aStoreSelID = ''
OR (A.Store_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreSelID))
))
AND ((:aStoreCardselid = ''
OR (A.StoreCard_id IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreCardselid))
))
AND ((:aCreatedBySelID = ''
OR (P.CreatedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCreatedBySelID))
))
AND ((:aCorrectedBySelID = ''
OR (P.CorrectedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCorrectedBySelID))
))
into
:status, :extratype, :intrastatamount, :localintrastatamount, :statisticamount, :currency_id,
:countrycode, :intrastatregioncode, :commoditycode, :deliverytermcode, :transportationtypecode, :transactiontypecode, :intrastatunitcode,
:IntrastatQuantity, :Weight, :weightunit, :origincountrycode, :intrastatorigincountry_id, :weightisoptional, :commoditydescription, :constantweight,
:country_id, :intrastatregion_id, :commodity_id, :deliveryterm_id, :transportationtype_id, :transactiontype_id, :extra_id,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id, :taxidentnumber
do begin
EXECUTE PROCEDURE checkdivisionandbuswithnull
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds,
:abusorderswithnull, :abustransactionswithnull, :abusprojectswithnull
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN
BEGIN
suspend;
end
end
--**************************************************
/* Vraceni pokladnich prijmu */
for
select
'N' as Status,
IE.Code as extratype,
A.IntrastatAmount as IntrastatAmount,
A.LocalIntrastatAmount as LocalIntrastatAmount,
A.StatisticAmount as StatisticAmount,
P.Currency_ID as Currency_ID,
CT.Code as CountryCode,
IR.Code as IntrastatRegionCode,
IC.Code as CommodityCode,
IDT.Code as DeliveryTermCode,
ITT.Code as TransportationTypeCode,
IT.Code as TransactionTypeCode,
IC.UnitCode as IntrastatUnitCode,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) as IntrastatQuantity,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) * CAST(S.IntrastatWeight AS DOUBLE PRECISION) as Weight,
S.IntrastatWeightUnit as WeightUnit,
OCT.Code as OriginCountryCode,
OCT.ID as IntrastatOriginCountry_ID,
IC.WeightIsOptional as WeightIsOptional,
IC.Description as CommodityDescription ,
IC.ConstantWeight as ConstantWeight,
CT.ID as Country_ID,
IR.ID as IntrastatRegion_ID,
IC.ID as Commodity_ID,
IDT.ID as DeliveryTerm_ID,
ITT.ID as TransportationType_ID,
IT.ID as TransactionType_ID,
IE.ID as Extra_ID,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
F.TAXIdentNumber as TAXIdentNumber
from
RefundedCashReceived2 A
join RefundedCashReceived P on P.ID=A.Parent_ID
left join StoreCards S on S.ID=A.StoreCard_ID
join Countries CT on CT.ID=P.Country_ID
left join Countries OCT on OCT.ID=S.Country_ID
left join IntrastatRegions IR on IR.ID=A.IntrastatRegion_ID
left join IntrastatCommodities IC on IC.ID=S.IntrastatCommodity_ID
left join IntrastatExtraTypes IE on IE.ID=S.IntrastatExtraType_ID
left join IntrastatDeliveryTerms IDT on IDT.ID=P.IntrastatDeliveryTerm_ID
left join IntrastatTransportationTypes ITT on ITT.ID=P.IntrastatTransportationType_ID
left join IntrastatTransactionTypes IT on IT.ID=P.IntrastatTransactionType_ID
left join Firms F on F.ID=P.Firm_ID
where
A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8)
AND (P.VATDate$Date >= :avatdatefrom and P.VATDate$Date < :avatdateto) and P.VATDocument = 'A'
AND ((:afirmselid = ''
OR (P.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (P.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
AND ((:acountryselid = ''
OR (P.Country_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :acountryselid))
))
AND ((:acurrencyselid = ''
OR (P.currency_id IN (SELECT obj_id FROM seldat WHERE sel_id = :acurrencyselid))
))
AND ((:aStoreSelID = ''
OR (A.Store_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreSelID))
))
AND ((:aStoreCardselid = ''
OR (A.StoreCard_id IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreCardselid))
))
AND ((:aCreatedBySelID = ''
OR (P.CreatedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCreatedBySelID))
))
AND ((:aCorrectedBySelID = ''
OR (P.CorrectedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCorrectedBySelID))
))
into
:status, :extratype, :intrastatamount, :localintrastatamount, :statisticamount, :currency_id,
:countrycode, :intrastatregioncode, :commoditycode, :deliverytermcode, :transportationtypecode, :transactiontypecode, :intrastatunitcode,
:IntrastatQuantity, :Weight, :weightunit, :origincountrycode, :intrastatorigincountry_id, :weightisoptional, :commoditydescription, :constantweight,
:country_id, :intrastatregion_id, :commodity_id, :deliveryterm_id, :transportationtype_id, :transactiontype_id, :extra_id,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id, :taxidentnumber
do begin
EXECUTE PROCEDURE checkdivisionandbuswithnull
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds,
:abusorderswithnull, :abustransactionswithnull, :abusprojectswithnull
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN
BEGIN
suspend;
end
end
--**************************************************
/* Store documents typ 20 */
for
select
'N' as Status,
IE.Code as extratype,
A.IntrastatAmount as IntrastatAmount,
A.LocalIntrastatAmount as LocalIntrastatAmount,
A.StatisticAmount as StatisticAmount,
P.Currency_ID as Currency_ID,
CT.Code as CountryCode,
IR.Code as IntrastatRegionCode,
IC.Code as CommodityCode,
IDT.Code as DeliveryTermCode,
ITT.Code as TransportationTypeCode,
IT.Code as TransactionTypeCode,
IC.UnitCode as IntrastatUnitCode,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) as IntrastatQuantity,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) * CAST(S.IntrastatWeight AS DOUBLE PRECISION) as Weight,
S.IntrastatWeightUnit as WeightUnit,
OCT.Code as OriginCountryCode,
OCT.ID as IntrastatOriginCountry_ID,
IC.WeightIsOptional as WeightIsOptional,
IC.Description as CommodityDescription ,
IC.ConstantWeight as ConstantWeight,
CT.ID as Country_ID,
IR.ID as IntrastatRegion_ID,
IC.ID as Commodity_ID,
IDT.ID as DeliveryTerm_ID,
ITT.ID as TransportationType_ID,
IT.ID as TransactionType_ID,
IE.ID as Extra_ID,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
F.TAXIdentNumber as TAXIdentNumber
from
StoreDocuments2 A
join PMStates PMS on A.PMState_ID = PMS.ID
join StoreDocuments P on P.ID=A.Parent_ID
left join StoreCards S on S.ID=A.StoreCard_ID
left join Countries OCT on OCT.ID=A.OriginCountry_ID
join Countries CT on CT.ID=P.Country_ID
left join IntrastatRegions IR on IR.ID=A.IntrastatRegion_ID
left join IntrastatCommodities IC on IC.ID=S.IntrastatCommodity_ID
left join IntrastatExtraTypes IE on IE.ID=S.IntrastatExtraType_ID
left join IntrastatDeliveryTerms IDT on IDT.ID=P.IntrastatDeliveryTerm_ID
left join IntrastatTransportationTypes ITT on ITT.ID=P.IntrastatTransportationType_ID
left join IntrastatTransactionTypes IT on IT.ID=P.IntrastatTransactionType_ID
left join Firms F on F.ID=P.Firm_ID
where
PMS.SystemState = 3 and
A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6) and P.DocumentType='20'
AND (P.IntrastatDate$Date >= :avatdatefrom and P.IntrastatDate$DATE < :avatdateto) --and P.VATDocument = 'A'
AND ((:afirmselid = ''
OR (P.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (P.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
AND ((:acountryselid = ''
OR (P.Country_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :acountryselid))
))
AND ((:acurrencyselid = ''
OR (P.currency_id IN (SELECT obj_id FROM seldat WHERE sel_id = :acurrencyselid))
))
AND ((:aStoreSelID = ''
OR (A.Store_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreSelID))
))
AND ((:aStoreCardselid = ''
OR (A.StoreCard_id IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreCardselid))
))
AND ((:aCreatedBySelID = ''
OR (P.CreatedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCreatedBySelID))
))
AND ((:aCorrectedBySelID = ''
OR (P.CorrectedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCorrectedBySelID))
))
into
:status, :extratype, :intrastatamount, :localintrastatamount, :statisticamount, :currency_id,
:countrycode, :intrastatregioncode, :commoditycode, :deliverytermcode, :transportationtypecode, :transactiontypecode, :intrastatunitcode,
:IntrastatQuantity, :Weight, :weightunit, :origincountrycode, :intrastatorigincountry_id, :weightisoptional, :commoditydescription, :constantweight,
:country_id, :intrastatregion_id, :commodity_id, :deliveryterm_id, :transportationtype_id, :transactiontype_id, :extra_id,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id, :taxidentnumber
do begin
EXECUTE PROCEDURE checkdivisionandbuswithnull
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds,
:abusorderswithnull, :abustransactionswithnull, :abusprojectswithnull
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN
BEGIN
suspend;
end
end
--**************************************************
/* Store documents typ 30 */
for
select
'A' as Status,
IE.Code as extratype,
A.IntrastatAmount as IntrastatAmount,
A.LocalIntrastatAmount as LocalIntrastatAmount,
A.StatisticAmount as StatisticAmount,
A.IntrastatCurrency_ID as Currency_ID,
CT.Code as CountryCode,
IR.Code as IntrastatRegionCode,
IC.Code as CommodityCode,
IDT.Code as DeliveryTermCode,
ITT.Code as TransportationTypeCode,
IT.Code as TransactionTypeCode,
IC.UnitCode as IntrastatUnitCode,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) as IntrastatQuantity,
CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) * CAST(S.IntrastatWeight AS DOUBLE PRECISION) as Weight,
S.IntrastatWeightUnit as WeightUnit,
OCT.Code as OriginCountryCode,
OCT.ID as IntrastatOriginCountry_ID,
IC.WeightIsOptional as WeightIsOptional,
IC.Description as CommodityDescription ,
IC.ConstantWeight as ConstantWeight,
CT.ID as Country_ID,
IR.ID as IntrastatRegion_ID,
IC.ID as Commodity_ID,
IDT.ID as DeliveryTerm_ID,
ITT.ID as TransportationType_ID,
IT.ID as TransactionType_ID,
IE.ID as Extra_ID,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
F.TAXIdentNumber as TAXIdentNumber
from
StoreDocuments2 A
join PMStates PMS on A.PMState_ID = PMS.ID
join StoreDocuments P on P.ID=A.Parent_ID
left join StoreCards S on S.ID=A.StoreCard_ID
left join Countries OCT on OCT.ID=A.OriginCountry_ID
join Countries CT on CT.ID=P.Country_ID
left join IntrastatRegions IR on IR.ID=A.IntrastatRegion_ID
left join IntrastatCommodities IC on IC.ID=S.IntrastatCommodity_ID
left join IntrastatExtraTypes IE on IE.ID=S.IntrastatExtraType_ID
left join IntrastatDeliveryTerms IDT on IDT.ID=P.IntrastatDeliveryTerm_ID
left join IntrastatTransportationTypes ITT on ITT.ID=P.IntrastatTransportationType_ID
left join IntrastatTransactionTypes IT on IT.ID=P.IntrastatTransactionType_ID
left join Firms F on F.ID=P.Firm_ID
where
PMS.SystemState = 3 and
A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6) and P.DocumentType='30'
AND (P.IntrastatDate$Date >= :avatdatefrom and P.IntrastatDate$DATE < :avatdateto) --and P.VATDocument = 'A'
AND ((:afirmselid = ''
OR (P.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (P.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
AND ((:acountryselid = ''
OR (P.Country_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :acountryselid))
))
AND ((:acurrencyselid = ''
OR (P.currency_id IN (SELECT obj_id FROM seldat WHERE sel_id = :acurrencyselid))
))
AND ((:aStoreSelID = ''
OR (A.Store_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreSelID))
))
AND ((:aStoreCardselid = ''
OR (A.StoreCard_id IN (SELECT obj_id FROM seldat WHERE sel_id = :aStoreCardselid))
))
AND ((:aCreatedBySelID = ''
OR (P.CreatedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCreatedBySelID))
))
AND ((:aCorrectedBySelID = ''
OR (P.CorrectedBy_ID IN (SELECT obj_id FROM seldat WHERE sel_id = :aCorrectedBySelID))
))
into
:status, :extratype, :intrastatamount, :localintrastatamount, :statisticamount, :currency_id,
:countrycode, :intrastatregioncode, :commoditycode, :deliverytermcode, :transportationtypecode, :transactiontypecode, :intrastatunitcode,
:IntrastatQuantity, :Weight, :weightunit, :origincountrycode, :intrastatorigincountry_id, :weightisoptional, :commoditydescription, :constantweight,
:country_id, :intrastatregion_id, :commodity_id, :deliveryterm_id, :transportationtype_id, :transactiontype_id, :extra_id,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id, :taxidentnumber
do begin
EXECUTE PROCEDURE checkdivisionandbuswithnull
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds,
:abusorderswithnull, :abustransactionswithnull, :abusprojectswithnull
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN
BEGIN
suspend;
end
end
end