Description
This method is used to get a quarter summary Export Vat List by input parameters.
Parameters
| icVatNumberIdentity | input | character | Vat Number Identity |
| icVatNumberDeclaration | input | character | Vat Number Declaration |
| icTransactionEndingPoint | input | character | Transaction Ending Point. |
| icTransactionStartingPoint | input | character | Transaction Starting Point |
| itFromTaxPointDate | input | date | From Tax Point Date. |
| itToTaxPointDate | input | date | To Tax Point Date. |
| icVatTransactionType | input | character | Vat Transaction Type |
| icVatPeriodMark | input | character | Vat Period Mark. |
| iiFromVatPeriod | input | integer | From Vat Period. |
| iiToVatPeriod | input | integer | To Vat Period. |
| icVatCode | input | character | Vat Code |
| iiFromVatYear | input | integer | From Vat Year. |
| iiToVatYear | input | integer | To Vat Year. |
| icConvertToCurrency | input | character | Convert To Currency Code. |
| iiExportPeriod | input | integer | Export Period. |
| iiExportYear | input | integer | Export Year. |
| tqVatExportListing | output | temp-table | Temp table of VatExportListing. |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
unused
program code (program6/bvatexportlisting.p)
empty temp-table tqExportVATQuarterSum.
empty temp-table tqVatExportListing.
/* chceck if transaction filter has been entered */
if icVatTransactionType = ? or icVatTransactionType = "":U
then do:
<M-2 run SetMessage
(input #T-23'You must choose a transaction type.':100(3846)t-23# (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-1864':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BVATExportListing>
assign oiReturnStatus = -1.
return.
end.
if iiExportPeriod <> ?
and iiExportYear <> ?
then assign vcPeriodYearForExportFile = string(iiExportPeriod, "9":U) + string(iiExportYear, "9999":U).
else do:
<M-19 run SetMessage
(input #T-24'You must enter the GL calendar year and GL period for the export.':100(3847)T-24# (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-1895':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BVATExportListing>
assign oiReturnStatus = -1.
return.
end.
/* ================*/
/* Get currency ID */
/* ================*/
if icConvertToCurrency = "":U or
icConvertToCurrency = ?
then assign vlUseLC = true.
else do:
assign vlUseLC = false.
<Q-3 run CurrencyPrim (all) (Read) (NoCache)
(input icConvertToCurrency, (CurrencyCode)
input ?, (Currency_ID)
output dataset tqCurrencyPrim) in BCurrency >
find first tqCurrencyPrim no-error.
if not available tqCurrencyPrim
then do:
<M-4 run SetMessage
(input #T-25'The specified currency code $1 is not defined in the system or is invalid.':100(3844)t-25# (icMessage),
input icConvertToCurrency (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-1865':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BVATExportListing>
assign oiReturnStatus = -1.
return.
end.
assign viExportCurrency_ID = tqCurrencyPrim.tiCurrency_ID.
<M-18 run GetReportExchangeRate
(input icConvertToCurrency (icConvertToCurrency),
input viExportCurrency_ID (iiExportCurrency_ID),
output vdExchangeRateValue (odExchangeRate),
output vdExchangeRateScale (odExchangeRateScale),
output viFcReturnSuper (oiReturnStatus)) in BVATExportListing>
if viFcReturnSuper <> 0
then do:
assign oiReturnStatus = -1.
return.
end.
end.
/* =================================== */
/* Precache tables for later filtering */
/* =================================== */
/* Get condition */
assign vlAreCDFiltersOn = false.
<Q-20 run CountryByCode (all) (Read) (NoCache)
(input ?, (CountryCode)
output dataset tqCountryByCode) in BCountry >
<Q-16 run DebtorPrim (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input ?, (DebtorCode)
input ?, (Debtor_ID)
output dataset tqDebtorPrim) in BDebtor >
<Q-17 run CreditorPrim (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input ?, (CreditorId)
input ?, (CreditorCode)
output dataset tqCreditorPrim) in BCreditor >
if (icTransactionEndingPoint <> ? and icTransactionEndingPoint <> "":U) or
(icTransactionStartingPoint <> ? and icTransactionStartingPoint <> "":U and icTransactionStartingPoint <> "":U)
then do:
assign vlAreCDFiltersOn = true.
if icTransactionEndingPoint <> ? and icTransactionEndingPoint <> "":U
then do:
<Q-7 run CountryByCode (all) (Read) (NoCache)
(input icTransactionEndingPoint, (CountryCode)
output dataset tqCountryByCode) in BCountry >
find first tqCountryByCode no-error.
if available tqCountryByCode
then assign viTEP_ID = tqCountryByCode.tiCountry_ID.
else do:
<M-21 run SetMessage
(input #T-26'You must choose a valid transaction ending point.':100(3848)t-26# (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-2797':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BVATExportListing>
end.
end.
if icTransactionStartingPoint <> ? and icTransactionStartingPoint <> "":U
then do:
<Q-8 run CountryByCode (all) (Read) (NoCache)
(input icTransactionStartingPoint, (CountryCode)
output dataset tqCountryByCode) in BCountry >
find first tqCountryByCode no-error.
if available tqCountryByCode
then assign viTSP_ID = tqCountryByCode.tiCountry_ID.
else do:
<M-22 run SetMessage
(input #T-27'You must choose a valid transaction starting point.':100(3849)t-27# (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-2798':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BVATExportListing>
end.
end.
end.
<Q-28 run VatGroupBoxLinkByBoxGroup (all) (Read) (NoCache)
(input ?, (VATBoxCode)
input ?, (VATGroupCode)
output dataset tqVatGroupBoxLinkByBoxGroup) in BVATGroup >
/* this sets default value for transaction type filter if not defined so
only IC transactions are displayed */
if icVatTransactionType = ?
then assign icVatTransactionType = {&VATTRANSACTIONTYPE-ACQUISITION} + ",":U + {&VATTRANSACTIONTYPE-SALES}.
/* =========================== */
/* Call query using parameters */
/* =========================== */
<Q-10 run PostingVatByPeriod (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input ?, (DeliveryType)
input ?, (FromAccPeriod)
input ?, (ToAccPeriod)
input ?, (FromPostingDate)
input ?, (ToPostingDate)
input itFromTaxPointDate, (FromTaxpointDate)
input itToTaxPointDate, (ToTaxpointDate)
input ?, (GLTypeCode)
input ?, (JournalCode)
input ?, (JournalTypeCode)
input icVatCode, (VatCode)
input ?, (VatInOut)
input icVatPeriodMark, (VatPeriodMark)
input icVatTransactionType, (VatTransactionType)
input ?, (FromVoucher)
input ?, (ToVoucher)
output dataset tqPostingVatByPeriod) in BPosting >
output close.
assign vlSkipPosting = false.
for each tqPostingVatByPeriod
break by tqPostingVatByPeriod.tiPostingLine_ID :
/* message tqPostingVatByPeriod.tiPosting_ID tqPostingVatByPeriod.tiPostingLine_ID '~n'
tqPostingVatByPeriod.tcCVatNumberIdentity tqPostingVatByPeriod.tcDVatNumberIdentity
view-as alert-box.*/
if not first-of (tqPostingVatByPeriod.tiPostingLine_ID) then next.
/*
if /*icVatGroupCode <> ?
or*/ icVatBoxCode <> ?
then do:
if tqPostingVatByPeriod.tcJournalTypeCode = {&JOURNALTYPE-CREDITORCREDITNOTE} or
tqPostingVatByPeriod.tcJournalTypeCode = {&JOURNALTYPE-DEBTORCREDITNOTE}
then if not can-find(first tqVatGroupBoxLinkByBoxGroup where tqVatGroupBoxLinkByBoxGroup.tiVatGroup_ID = tqPostingVatByPeriod.tiCreditNoteVatGroup_ID)
then next.
else if not can-find(first tqVatGroupBoxLinkByBoxGroup where tqVatGroupBoxLinkByBoxGroup.tiVatGroup_ID = tqPostingVatByPeriod.tiInvoiceVatGroup_ID)
then next.
end.
*/
assign vlSkipPosting = true.
/* ============================================= */
/* check for CC, CI, DI, DC within filters given */
/* ============================================= */
vlAreCDFiltersOn = true.
if vlAreCDFiltersOn
then do:
if tqPostingVatByPeriod.tcJournalTypeCode = {&JOURNALTYPE-CREDITORCREDITNOTE} or
tqPostingVatByPeriod.tcJournalTypeCode = {&JOURNALTYPE-CREDITORINVOICE}
then do:
/*message '3.' icTransactionStartingPoint viTSP_ID view-as alert-box. */
if icTransactionStartingPoint <> ? and icTransactionStartingPoint <> "":U
then if tqPostingVatByPeriod.tiCTransActionStartCountry_ID <> viTSP_ID then next.
/*message '4.' icTransactionEndingPoint viTEP_ID view-as alert-box. */
if icTransactionEndingPoint <> ? and icTransactionEndingPoint <> "":U
then if tqPostingVatByPeriod.tiCTransActionEndCountry_ID <> viTEP_ID then next.
/*message 'OK1' view-as alert-box.*/
end.
else if tqPostingVatByPeriod.tcJournalTypeCode = {&JOURNALTYPE-DEBTORCREDITNOTE} or
tqPostingVatByPeriod.tcJournalTypeCode = {&JOURNALTYPE-DEBTORINVOICE}
then do:
if icTransactionStartingPoint <> ? and icTransactionStartingPoint <> "":U
then if tqPostingVatByPeriod.tiDTransActionStartCountry_ID <> viTSP_ID then next.
if icTransactionEndingPoint <> ? and icTransactionEndingPoint <> "":U
then if tqPostingVatByPeriod.tiDTransActionEndCountry_ID <> viTEP_ID then next.
end.
else next. /* this skips all JE and BE */
end.
create tqExportVATQuarterSum.
if tqPostingVatByPeriod.tcJournalTypeCode = {&JOURNALTYPE-CREDITORCREDITNOTE} or
tqPostingVatByPeriod.tcJournalTypeCode = {&JOURNALTYPE-CREDITORINVOICE}
then do:
/* assign tqExportVATQuarterSum.tcCinvoiceReference = tqPostingVatByPeriod.tcCInvoiceReference*/
/*tqExportVATQuarterSum.tiInvoiceVoucher = tqPostingVatByPeriod.tiCInvoiceVoucher. */
find first tqCreditorPrim where tqCreditorPrim.tiCreditor_ID = tqPostingVatByPeriod.tiCreditor_ID no-lock no-error.
if avail tqCreditorPrim
then assign tqExportVATQuarterSum.tiBusinessRelationIDEQS = tqCreditorPrim.tiBusinessRelation_ID
tqExportVATQuarterSum.tcBusinessRelationNameEQS = tqCreditorPrim.tcBusinessRelationName1.
if tqPostingVatByPeriod.tcVatTransactionType = {&VATTRANSACTIONTYPE-SUPPLY}
then do:
find first tqCountryByCode where tqCountryByCode.tiCountry_ID = tqPostingVatByPeriod.tiCTransActionEndCountry_ID no-error.
if available tqCountryByCode
then assign tqExportVATQuarterSum.tcCountryCode = tqCountryByCode.tcCountryCode.
end.
else do:
find first tqCountryByCode where tqCountryByCode.tiCountry_ID = tqPostingVatByPeriod.tiCTransActionStartCountry_ID no-error.
if available tqCountryByCode
then assign tqExportVATQuarterSum.tcCountryCode = tqCountryByCode.tcCountryCode.
end.
end.
else do:
find first tqDebtorPrim where tqDebtorPrim.tiDebtor_ID = tqPostingVatByPeriod.tiDebtor_ID no-lock no-error.
if avail tqDebtorPrim
then assign tqExportVATQuarterSum.tiBusinessRelationIDEQS = tqDebtorPrim.tiBusinessRelation_ID
tqExportVATQuarterSum.tcBusinessRelationNameEQS = tqDebtorPrim.tcBusinessRelationName1.
if tqPostingVatByPeriod.tcVatTransactionType = {&VATTRANSACTIONTYPE-SUPPLY}
then do:
find first tqCountryByCode where tqCountryByCode.tiCountry_ID = tqPostingVatByPeriod.tiDTransactionEndCountry_ID no-error.
if available tqCountryByCode
then assign tqExportVATQuarterSum.tcCountryCode = tqCountryByCode.tcCountryCode.
end.
else do:
find first tqCountryByCode where tqCountryByCode.tiCountry_ID = tqPostingVatByPeriod.tiDTransactionStartCountry_ID no-error.
if available tqCountryByCode
then assign tqExportVATQuarterSum.tcCountryCode = tqCountryByCode.tcCountryCode.
end.
end.
assign tqExportVATQuarterSum.tcJournalCode = tqPostingVatByPeriod.tcJournalCode
tqExportVATQuarterSum.tiAccPeriodEQS = tqPostingVatByPeriod.tiVatPeriodPeriod
tqExportVATQuarterSum.tiAccYearEQS = tqPostingVatByPeriod.tiVatPeriodYear.
if vlUseLC
then assign tqExportVATQuarterSum.tdBaseRC = tqPostingVatByPeriod.tdPostingVatBaseDebitLC - tqPostingVatByPeriod.tdPostingVatBaseCreditLC
tqExportVATQuarterSum.tdVatRC = tqPostingVatByPeriod.tdPostingLineDebitLC - tqPostingVatByPeriod.tdPostingLineCreditLC.
else do:
if viExportCurrency_ID = tqPostingVatByPeriod.tiCurrency_ID
then assign tqExportVATQuarterSum.tdBaseRC = tqPostingVatByPeriod.tdPostingVatBaseDebitTC - tqPostingVatByPeriod.tdPostingVatBaseCreditTC
tqExportVATQuarterSum.tdVatRC = tqPostingVatByPeriod.tdPostingLineDebitTC - tqPostingVatByPeriod.tdPostingLineCreditTC.
else assign tqExportVATQuarterSum.tdBaseRC = (vdExchangeRateValue * tqPostingVatByPeriod.tdPostingVatBaseDebitLC) * vdExchangeRateScale - (vdExchangeRateValue * tqPostingVatByPeriod.tdPostingVatBaseCreditLC) * vdExchangeRateScale
tqExportVATQuarterSum.tdVatRC = (vdExchangeRateValue * tqPostingVatByPeriod.tdPostingLineDebitLC) * vdExchangeRateScale - (vdExchangeRateValue * tqPostingVatByPeriod.tdPostingLineCreditTC) * vdExchangeRateScale.
end.
end.
/* formatting */
<M-11 run ExportFormatting723 (input tqExportVATQuarterSum (t_sqExportVATQuarterSum),
input vcPeriodYearForExportFile (icPeriodYearForExportFile),
output tqVatExportListing (tqVatExportListing),
output viFcReturnSuper (oiReturnStatus)) in BVATExportListing>
Sample code: how to call this method through RPCRequestService (QXtend Inbound)
define temp-table ttContext no-undo
field propertyQualifier as character
field propertyName as character
field propertyValue as character
index entityContext is primary unique
propertyQualifier
propertyName
index propertyQualifier
propertyQualifier.
define dataset dsContext for ttContext.
define variable vhContextDS as handle no-undo.
define variable vhExceptionDS as handle no-undo.
define variable vhServer as handle no-undo.
define variable vhInputDS as handle no-undo.
define variable vhInputOutputDS as handle no-undo.
define variable vhOutputDS as handle no-undo.
define variable vhParameter as handle no-undo.
/* Create context */
create ttContext.
assign ttContext.propertyName = "programName"
ttContext.propertyValue = "BVATExportListing".
create ttContext.
assign ttContext.propertyName = "methodName"
ttContext.propertyValue = "ApiExportQuarterSum".
create ttContext.
assign ttContext.propertyName = "applicationId"
ttContext.propertyValue = "fin".
create ttContext.
assign ttContext.propertyName = "entity"
ttContext.propertyValue = "1000".
create ttContext.
assign ttContext.propertyName = "userName"
ttContext.propertyValue = "mfg".
create ttContext.
assign ttContext.propertyName = "password"
ttContext.propertyValue = "".
/* Create input dataset */
create dataset vhInputDS.
vhInputDS:read-xmlschema("file", "xml/bvatexportlisting.apiexportquartersum.i.xsd", ?).
vhParameter = vhInputDS:get-buffer-handle("tParameterI").
vhParameter:buffer-create().
assign vhParameter::icVatNumberIdentity = <parameter value>
vhParameter::icVatNumberDeclaration = <parameter value>
vhParameter::icTransactionEndingPoint = <parameter value>
vhParameter::icTransactionStartingPoint = <parameter value>
vhParameter::itFromTaxPointDate = <parameter value>
vhParameter::itToTaxPointDate = <parameter value>
vhParameter::icVatTransactionType = <parameter value>
vhParameter::icVatPeriodMark = <parameter value>
vhParameter::iiFromVatPeriod = <parameter value>
vhParameter::iiToVatPeriod = <parameter value>
vhParameter::icVatCode = <parameter value>
vhParameter::iiFromVatYear = <parameter value>
vhParameter::iiToVatYear = <parameter value>
vhParameter::icConvertToCurrency = <parameter value>
vhParameter::iiExportPeriod = <parameter value>
vhParameter::iiExportYear = <parameter value>.
/* Connect the AppServer */
create server vhServer.
vhServer:connect("-URL <appserver-url>").
if not vhServer:connected()
then do:
message "Could not connect AppServer" view-as alert-box error title "Error".
return.
end.
/* Run */
assign vhContextDS = dataset dsContext:handle.
run program/rpcrequestservice.p on vhServer
(input-output dataset-handle vhContextDS by-reference,
output dataset-handle vhExceptionDS,
input dataset-handle vhInputDS by-reference,
input-output dataset-handle vhInputOutputDS by-reference,
output dataset-handle vhOutputDS).
/* Handle output however you want, in this example, we dump it to xml */
if valid-handle(vhExceptionDS)
then vhExceptionDS:write-xml("file", "Exceptions.xml", true).
if valid-handle(vhOutputDS)
then vhOutputDS:write-xml("file", "Output.xml", true).
/* Cleanup */
vhServer:disconnect().
assign vhServer = ?.
if valid-handle(vhInputDS)
then delete object vhInputDS.
if valid-handle(vhOutputDS)
then delete object vhOutputDS.
if valid-handle(vhExceptionDS)
then delete object vhExceptionDS.