| icDomainCode | input | character | Code of the domain used for for filtering of Supplier Invoices and Receiver Matchings |
| icEdiBatchNumber | input | character | Batch number used for filtering of the Supplier invoice and Receiver matching details to be quyeried. This batch number have to be assigned upfront for required records. |
| tApiCInvoiceEDI | output | temp-table | Temporary table with details about required Supplier Invoice. Temporary table structure: tcCInvoiceReferenceInt Internal reference of supplier invoice (Year/Journal/Voucher) tcNormalPaymentConditionCode Code of payment condition for normal payment tdCInvoiceBalanceTC Balance of supplier Invoice in transactional currency ttCInvoiceDiscountDueDate Due date for application of discount on supplier Invoice ttCInvoiceIsHoldAmount Does invoice contain any hold amount which cannot be payed ttCInvoiceLastPayedDate Date of last payment of supplier invoice tdCInvoicePaySelAmountTC Total amount in foreign currency included on payments tdCInvoicePaySelDiscAmountTC Todal discount amount of supplier invoices included on payments tdCInvoiceNonDiscAmountTC Supplier invoice amount which cannot be discounted tdCInvocieHodlAmountTC Supplier invoice amount which cannot be payed tcCInvoiceCurrencyCode Currency code of invoice tcCInvoiceShipTo Ship to address tcCInvoiceReference External reference of supplier invoices as provided by supplier tlCInvoiceIsStatusFinal Is this invoices created in final status. If not no any financial update is done tlCInvoiceReceived Has been all items of supplier invoice recieved to stock? tdCInvoicePrepayAmountTC Prepayed amount ttCInvoiceTaxPointDate Tax point date tcCInvoiceTaxEnv Tax environment tlCInvoiceIsTaxatable Is this invoices taxatable? tcCInvoiceTaxUsage Tax usage tdCInvoiceBalanceLC Balance of supplier invoice in local currency CInvoicePaySelAmountLC Total amount in local currency included on payments CInvoicePaySelDiscAmountLC Total discount amount of supplier invices in local currency in payments CInvoiceNonDiscAmtLC Supplier invoice amount which cannot be discounted CInvoiceHoldAmountLC Supplier invoice amount which cannot be payed CInvoicePostingDate Posting date of supplier invoices CInvoiceDate Invoice date (date invoices was issued by supplier) CInvoiceBankNumber Bank number where invoice amount should be payed to CInvoiceDueDate Due date of payment of supplier invoice RemittanceAddressName Remmitance supplier address CInvoiceID ID of supplier invoice CompanyId ID of company PurchaseOrderNumber Purchase order which should be payed by the invoices ReceiverNumber Receiver number which should be payed by the invoice |
| tApiMatchingLnEDI | output | temp-table | Matching details DomainCode Code of the domain where receipt occured CInvoice_ID ID of supplier invoices (can be used as relation field between records in tApiCInvoiceEDI and tAPIMatchingLnEDI tables) PvoId ID of pending voucher master PvodLineId ID of pending voucher detail records APMatchingLnId ID of matching line MatchQty Matched quantity in purchase UoM (taken from purchase order) APMatchingLnTaxBaseLC Total base amount used for calculation of taxes APMatchingLnTaxBaseTC Total base amount used for calculation of taxes APMatchingLnTaxLC Total tax amount APMatchingLnTaxTC Total tax amount APMatchingLnTotalLC Total matching amount including taxes APMatchingLnTotalTC Total matching amount including taxes |
| oiReturnStatus | output | integer | Return status of the method. |
/* =================================================================================================== */
/* Method : ApiGetCInvoiceAPMatchingDetails */
/* Desc : Get all matching lines and details for purchase order */
/* --------------------------------------------------------------------------------------------------- */
/* Params: (I) DomainCode Domain code */
/* (I) EDIBatchNumber Batch number to be reported */
/* (O) ApiCInvoiceEDI Temporary table with details needed for EDI export */
/* (O) ApiMatchingLnEDI Temporary table with matching details needed for EDI export */
/* =================================================================================================== */
assign oiReturnStatus = -98
viLocalReturn = 0.
/* =================================================================================================== */
/* Default output values */
/* =================================================================================================== */
empty temp-table tApiCInvoiceEDI.
empty temp-table tApiMatchingLnEDI.
/* =================================================================================================== */
/* Normalize input parameters */
/* =================================================================================================== */
if icDomainCode = '':U then icDomainCode = ?.
if icEdiBatchNumber = '':U then icEdiBatchNumber = ?.
/* =================================================================================================== */
/* Validate input parameters */
/* =================================================================================================== */
if icDomainCode = ? or
icEdiBatchNumber = ?
then do:
assign vcMessage = #T-1'You have not entered all the mandatory values.':255(63250)t-1#
vcContext = 'icDomainCode=&1|icEdiBatchNumber=&2':U
vcContext = replace(vcContext, '|':U, chr(2))
vcContext = substitute(vcContext, icDomainCode, icEdiBatchNumber)
vcMessage = vcMessage + " ":U + vcContext.
<M-2 run SetMessage
(input vcMessage (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'S':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QadFin-8557':U (icFcMsgNumber),
input '':U (icFcExplanation),
input '':U (icFcIdentification),
input vcContext (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BCInvoiceAPMatching>
assign oiReturnStatus = -1.
return.
end.
/* =================================================================================================== */
/* Start queries */
/* =================================================================================================== */
<Q-9 run PaySelLineByParentId (Start) in BPaymentSelection >
<Q-11 run APMatchingByCInvoiceWithLines (Start) in BAPMatching >
<Q-16 run CInvoiceBankByCInvoiceID (Start) in BCInvoice >
<Q-14 run CInvoiceVatByCInvoiceId (Start) in BCInvoice >
<Q-22 run CPayByCInvoice (Start) in BCPay >
/* =================================================================================================== */
/* Get all supplier invoices included in this export */
/* =================================================================================================== */
<Q-3 run VoucherInvoiceHistoryForERSExp (all) (Read) (NoCache)
(input icDomainCode, (DomainCode)
input icEdiBatchNumber, (EDIBatchNumber)
input ?, (CompanyId)
input {&ADDRESSTYPECODESYSTEM-REMITTANCE}, (AddressTypeRemmitance)
output dataset tqVoucherInvoiceHistoryForERSExp) in BMfgPurchaseVoucherHist >
/* =================================================================================================== */
/* For every invoice we have to calculate some extra data */
/* =================================================================================================== */
for each tqVoucherInvoiceHistoryForERSExp
break by tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID:
if first-of(tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID)
then do:
/* =========================================================================================== */
/* Create record in outgoing temporary table */
/* =========================================================================================== */
create tApiCInvoiceEDI.
assign tApiCInvoiceEDI.tcCInvoiceReferenceInt = substitute("&1/&2/&3":U,
tqVoucherInvoiceHistoryForERSExp.tiPeriodYear,
tqVoucherInvoiceHistoryForERSExp.tcJournalCode,
string(tqVoucherInvoiceHistoryForERSExp.tiCInvoiceVoucher, "999999999":U))
tApiCInvoiceEDI.tcNormalPaymentConditionCode = tqVoucherInvoiceHistoryForERSExp.tcPaymentConditionCode
tApiCInvoiceEDI.tdCInvoiceBalanceTC = tqVoucherInvoiceHistoryForERSExp.tdCInvoiceBalanceDebitTC
- tqVoucherInvoiceHistoryForERSExp.tdCInvoiceBalanceCreditTC
tApiCInvoiceEDI.ttCInvoiceDiscountDueDate = tqVoucherInvoiceHistoryForERSExp.ttCInvoiceDiscountDueDate
tApiCInvoiceEDI.tlCInvoiceIsHoldAmount = (tqVoucherInvoiceHistoryForERSExp.tdCInvoiceHoldAmountTC <> 0)
tApiCInvoiceEDI.tdCInvoiceNonDiscAmtTC = tqVoucherInvoiceHistoryForERSExp.tdCInvoiceNonDiscAmtTC
tApiCInvoiceEDI.tdCInvoiceHoldAmountTC = tqVoucherInvoiceHistoryForERSExp.tdCInvoiceHoldAmountTC
tApiCInvoiceEDI.tcCInvoiceCurrencyCode = tqVoucherInvoiceHistoryForERSExp.tcCurrencyCode
tApiCInvoiceEDI.tcCInvoiceShipTo = tqVoucherInvoiceHistoryForERSExp.tcAddressName
tApiCInvoiceEDI.tcCInvoiceReference = tqVoucherInvoiceHistoryForERSExp.tcCInvoiceReference
tApiCInvoiceEDI.tlCInvoiceIsStatusFinal = (tqVoucherInvoiceHistoryForERSExp.tlCInvoiceIsInitialStatus <> true)
tApiCInvoiceEDI.tlCInvoiceReceived = (tqVoucherInvoiceHistoryForERSExp.tcReasonAllocationStatus = {&ALLOCSTATUS-ALLOC})
tApiCInvoiceEDI.ttCInvoiceTaxPointDate = tqVoucherInvoiceHistoryForERSExp.ttCInvoiceTaxPointDate
tApiCInvoiceEDI.tdCInvoiceBalanceLC = tqVoucherInvoiceHistoryForERSExp.tdCInvoiceBalanceDebitLC
- tqVoucherInvoiceHistoryForERSExp.tdCInvoiceBalanceCreditLC
tApiCInvoiceEDI.tdCInvoiceNonDiscAmtLC = tqVoucherInvoiceHistoryForERSExp.tdCInvoiceNonDiscAmtLC
tApiCInvoiceEDI.tdCInvoiceHoldAmountLC = <M-5 RoundAmount
(input tApiCInvoiceEDI.tdCInvoiceHoldAmountTC * tqVoucherInvoiceHistoryForERSExp.tdCInvoiceExchangeRate * tqVoucherInvoiceHistoryForERSExp.tdCInvoiceRateScale (idUnroundedAmount),
input ? (iiCurrencyID),
input tqVoucherInvoiceHistoryForERSExp.tcCurrencyCode (icCurrencyCode)) in BCInvoiceAPMatching>
tApiCInvoiceEDI.ttCInvoicePostingDate = tqVoucherInvoiceHistoryForERSExp.ttCInvoicePostingDate
tApiCInvoiceEDI.ttCInvoiceDate = tqVoucherInvoiceHistoryForERSExp.ttCInvoiceDate
tApiCInvoiceEDI.ttCInvoiceDueDate = tqVoucherInvoiceHistoryForERSExp.ttCInvoiceDueDate
tApiCInvoiceEDI.tcRemittanceAddressName = tqVoucherInvoiceHistoryForERSExp.tcRemitToAddressName
tApiCInvoiceEDI.tiCInvoiceID = tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID
tApiCInvoiceEDI.tiCompanyID = tqVoucherInvoiceHistoryForERSExp.tiCompany_ID.
/* =========================================================================================== */
/* Get all payment selections of this supplier invoice in confirmed status */
/* =========================================================================================== */
<Q-6 run PaySelLineByParentId (all) (Read) (NoCache)
(input ?, (CompanyId)
input tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID, (ParentObjectID)
input ?, (ParentObjectType)
input {&PAYMENTSELECTIONSTATUS-REGISTERED}, (PaySelStatus)
output dataset tqPaySelLineByParentId) in BPaymentSelection >
assign vdPaySelAmountTC = 0
vdPaySelDiscountTC = 0.
for each tqPaySelLineByParentId where
tqPaySelLineByParentId.tiPaySelLineParentObject_ID = tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID and
tqPaySelLineByParentId.tcPaySelStatus = {&PAYMENTSELECTIONSTATUS-REGISTERED}:
assign vdPaySelAmountTC = vdPaySelAmountTC + tqPaySelLineByParentId.tdPaySelLineAmountTC
vdPaySelDiscountTC = vdPaySelDiscountTC + tqPaySelLineByParentId.tdPaySelLineDiscountTC.
end.
assign tApiCInvoiceEDI.tdCInvoicePaySelAmountTC = vdPaySelAmountTC
tApiCInvoiceEDI.tdCInvoicePaySelDiscAmountTC = vdPaySelDiscountTC
tApiCInvoiceEDI.tdCInvoicePaySelAmountLC = <M-7 RoundAmount
(input vdPaySelAmountTC * tqVoucherInvoiceHistoryForERSExp.tdCInvoiceExchangeRate * tqVoucherInvoiceHistoryForERSExp.tdCInvoiceRateScale (idUnroundedAmount),
input ? (iiCurrencyID),
input tqVoucherInvoiceHistoryForERSExp.tcCurrencyCode (icCurrencyCode)) in BCInvoiceAPMatching>
tApiCInvoiceEDI.tdCInvoicePaySelDiscAmountLC = <M-8 RoundAmount
(input vdPaySelDiscountTC * tqVoucherInvoiceHistoryForERSExp.tdCInvoiceExchangeRate * tqVoucherInvoiceHistoryForERSExp.tdCInvoiceRateScale (idUnroundedAmount),
input ? (iiCurrencyID),
input tqVoucherInvoiceHistoryForERSExp.tcCurrencyCode (icCurrencyCode)) in BCInvoiceAPMatching>.
/* =========================================================================================== */
/* Get purchase order and receiver number - in case of ERS invoices and matchings, PO nbr and */
/* Receiver number are the same. So we can take it from first matching line */
/* =========================================================================================== */
<Q-10 run APMatchingByCInvoiceWithLines (all) (Read) (NoCache)
(input ?, (CompanyId)
input tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID, (CInvoiceID)
input {&APMATCHINGSTATUS-FINISHED}, (APMatchingStatus)
output dataset tqAPMatchingByCInvoiceWithLines) in BAPMatching >
find first tqAPMatchingByCInvoiceWithLines where
tqAPMatchingByCInvoiceWithLines.tiCInvoice_ID = tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID and
tqAPMatchingByCInvoiceWithLines.tcAPMatchingStatus = {&APMATCHINGSTATUS-FINISHED}
no-error.
if available tqAPMatchingByCInvoiceWithLines
then assign tApiCInvoiceEDI.tcPurchaseOrderNumber = tqAPMatchingByCInvoiceWithLines.tcAPMatchingLnPvodOrder
tApiCInvoiceEDI.tcReceiverNumber = tqAPMatchingByCInvoiceWithLines.tcAPMatchingLnPvodIntRef.
/* =========================================================================================== */
/* Get Bank number of this inovice - because invoice is created from ERS, there should be only */
/* bank number */
/* =========================================================================================== */
<Q-15 run CInvoiceBankByCInvoiceID (all) (Read) (NoCache)
(input tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID, (CInvoiceId)
output dataset tqCInvoiceBankByCInvoiceID) in BCInvoice >
find first tqCInvoiceBankByCInvoiceID where
tqCInvoiceBankByCInvoiceID.tiCInvoice_ID = tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID
no-error.
if available tqCInvoiceBankByCInvoiceID
then assign tApiCInvoiceEDI.tcCInvoiceBankNumber = tqCInvoiceBankByCInvoiceID.tcBankNumber.
/* =========================================================================================== */
/* Get tax details of supplier invoice (take only first line) */
/* =========================================================================================== */
<Q-13 run CInvoiceVatByCInvoiceId (all) (Read) (NoCache)
(input tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID, (CInvoiceId)
output dataset tqCInvoiceVatByCInvoiceId) in BCInvoice >
find first tqCInvoiceVatByCInvoiceId where
tqCInvoiceVatByCInvoiceId.tiCInvoice_ID = tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID
no-error.
if available tqCInvoiceVatByCInvoiceId
then assign tApiCInvoiceEDI.tcCInvoiceTaxEnv = tqCInvoiceVatByCInvoiceId.tcTxenvTaxEnv
tApiCInvoiceEDI.tcCInvoiceTaxClass = tqCInvoiceVatByCInvoiceId.tcTxclTaxCls
tApiCInvoiceEDI.tcCInvoiceTaxUsage = tqCInvoiceVatByCInvoiceId.tcTxuTaxUsage.
/* =========================================================================================== */
/* Check whether there has been any payment to this invoice */
/* =========================================================================================== */
<Q-21 run CPayByCInvoice (all) (Read) (NoCache)
(input tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID, (CInvoiceId)
input ?, (CompanyId)
output dataset tqCPayByCInvoice) in BCPay >
for first tqCPayByCInvoice where
tqCPayByCInvoice.tiCInvoice = tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID
by tqCPayByCInvoice.ttPostingDate DESC:
assign tApiCInvoiceEDI.ttCInvoiceLastPaidDate = tqCPayByCInvoice.ttPostingDate.
end.
end. /* if first-of(tqVoucherInvoiceHistoryForERSExp.tiCInvoice_ID) */
end. /* foreach tqVoucherInvoiceHistoryForERSExp */
/* =================================================================================================== */
/* Get all matching detailsincluded in this export */
/* =================================================================================================== */
<Q-24 run VoucherInvoiceHistoryForERSDet (all) (Read) (NoCache)
(input icDomainCode, (DomainCode)
input icEdiBatchNumber, (EDIBatchNumber)
input ?, (CompanyId)
output dataset tqVoucherInvoiceHistoryForERSDet) in BMfgPurchaseVoucherHist >
for each tqVoucherInvoiceHistoryForERSDet
break by tqVoucherInvoiceHistoryForERSDet.tiAPMatchingLn_ID:
if first-of(tqVoucherInvoiceHistoryForERSDet.tiAPMatchingLn_ID)
then do:
create tApiMatchingLnEDI.
assign tApiMatchingLnEDI.tcDomainCode = tqVoucherInvoiceHistoryForERSDet.tcPvoDomain
tApiMatchingLnEDI.tdAPMatchingLnTaxBaseLC = tqVoucherInvoiceHistoryForERSDet.tdAPMatchingLnMatchAmtLC
tApiMatchingLnEDI.tdAPMatchingLnTaxBaseTC = tqVoucherInvoiceHistoryForERSDet.tdAPMatchingLnMatchAmtTC
tApiMatchingLnEDI.tdAPMatchingLnTaxLC = 0
tApiMatchingLnEDI.tdAPMatchingLnTaxTC = 0
tApiMatchingLnEDI.tdAPMatchingLnTotalLC = 0
tApiMatchingLnEDI.tdAPMatchingLnTotalTC = 0
tApiMatchingLnEDI.tdMatchQty = tqVoucherInvoiceHistoryForERSDet.tdAPMatchingLnMatchQty
tApiMatchingLnEDI.tiAPMatchingLnId = tqVoucherInvoiceHistoryForERSDet.tiAPMatchingLn_ID
tApiMatchingLnEDI.tiCInvoiceID = tqVoucherInvoiceHistoryForERSDet.tiCInvoice_ID
tApiMatchingLnEDI.tiPvodLineId = tqVoucherInvoiceHistoryForERSDet.tiPvodLineID
tApiMatchingLnEDI.tiPvoId = tqVoucherInvoiceHistoryForERSDet.tiPvoID.
end.
/* cumulate taxes */
assign tApiMatchingLnEDI.tdAPMatchingLnTaxLC = tApiMatchingLnEDI.tdAPMatchingLnTaxLC
+ (if tqVoucherInvoiceHistoryForERSDet.tdAPMatchingLnTaxTaxLC = ?
then 0
else tqVoucherInvoiceHistoryForERSDet.tdAPMatchingLnTaxTaxLC)
tApiMatchingLnEDI.tdAPMatchingLnTaxTC = tApiMatchingLnEDI.tdAPMatchingLnTaxTC
+ (if tqVoucherInvoiceHistoryForERSDet.tdAPMatchingLnTaxTaxTC = ?
then 0
else tqVoucherInvoiceHistoryForERSDet.tdAPMatchingLnTaxTaxTC).
if last-of(tqVoucherInvoiceHistoryForERSDet.tiAPMatchingLn_ID)
then assign tApiMatchingLnEDI.tdAPMatchingLnTotalLC = tApiMatchingLnEDI.tdAPMatchingLnTaxBaseLC
+ tApiMatchingLnEDI.tdAPMatchingLnTaxLC.
tApiMatchingLnEDI.tdAPMatchingLnTotalTC = tApiMatchingLnEDI.tdAPMatchingLnTaxBaseTC
+ tApiMatchingLnEDI.tdAPMatchingLnTaxTC.
end. /* for each tqVoucherInvoiceHistoryForERSDet: */
/* =================================================================================================== */
/* Stop queries */
/* =================================================================================================== */
<Q-17 run PaySelLineByParentId (Stop) in BPaymentSelection >
<Q-18 run APMatchingByCInvoiceWithLines (Stop) in BAPMatching >
<Q-19 run CInvoiceBankByCInvoiceID (Stop) in BCInvoice >
<Q-20 run CInvoiceVatByCInvoiceId (Stop) in BCInvoice >
<Q-23 run CPayByCInvoice (Stop) in BCPay >
/* =================================================================================================== */
/* Return */
/* =================================================================================================== */
assign oiReturnStatus = viLocalReturn.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 = "BCInvoiceAPMatching".
create ttContext.
assign ttContext.propertyName = "methodName"
ttContext.propertyValue = "ApiGetCInvoiceAPMatchingDetails".
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/bcinvoiceapmatching.apigetcinvoiceapmatchingdetails.i.xsd", ?).
vhParameter = vhInputDS:get-buffer-handle("tParameterI").
vhParameter:buffer-create().
assign vhParameter::icDomainCode = <parameter value>
vhParameter::icEdiBatchNumber = <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.