project BLF > class BNumber > method ApiSetFreeNumbers

Description

API method to define new sequences.
(When not defined, sequences start counting from 1 by default.)


Parameters


tNumberFreeinputtemp-table
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


QadFinancials
method BGLReport.GLSequenceRenumber


program code (program3/bnumber.p)

<M-22 run ClearData  (output viFcReturnSuper (oiReturnStatus)) in BNumber>
if viFcReturnSuper <> 0
then oiReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then return.

/* Validate input temp-table */
if not can-find (first tNumberFree)
then return.

<M-41 run ApiSetFreeNumbersValidate  (output viFcReturnSuper (oiReturnStatus)) in BNumber>
if viFcReturnSuper <> 0
then oiReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then return.

/* Add newly created Numbers to the instance */
for each tNumberFree where
         tNumberFree.tcNumbrStatus  = {&NUMBERSTATUS-FREE} and
         tNumberFree.tcUpdateStatus = "N" on error undo, throw:
    /* Verify that this key does not already exist in database */
    <Q-2 assign vlFcQueryRecordsAvailable = NumbrByYearTypeStatus (NoCache)
       (input viCompanyID, (CompanyId)
        input {&NUMBERSTATUS-FREE}, (NumbrStatus)
        input tNumberFree.tiNumbrYear, (NumbrYear)
        input tNumberFree.tcNumbrType, (NumbrType)) in BNumber >

    if vlFcQueryRecordsAvailable <> false
    then do:
        assign vcNumbrMsg     = trim(subst(#T-32'The Numbering record with Year &1 and Type &2 already exists.':200(495)t-32#, string(tNumberFree.tiNumbrYear), trim(tNumberFree.tcNumbrType)))
               oiReturnStatus = -1.

        <M-3 run SetMessage
           (input  trim(vcNumbrMsg) (icMessage), 
            input  '' (icArguments), 
            input  '' (icFieldName), 
            input  '' (icFieldValue), 
            input  'E' (icType), 
            input  3 (iiSeverity), 
            input  '' (icRowid), 
            input  'BLF-113':U (icFcMsgNumber), 
            input  '' (icFcExplanation), 
            input  '' (icFcIdentification), 
            input  '' (icFcContext), 
            output viFcReturnSuper (oiReturnStatus)) in BNumber>

        return.
    end.

    /* Verify that this key has not been added more than once in the UI */
    if can-find(first bNumberFree where
                      bNumberFree.tiNumbrYear =  tNumberFree.tiNumbrYear and
                      bNumberFree.tcNumbrType =  tNumberFree.tcNumbrType and
                      rowid(bNumberFree)     <> rowid(tNumberFree))
    then do:
        assign vcNumbrMsg     = trim(subst(#T-33'A number type with year &1 and type &2 was added twice. None was saved.':200(496)T-33#, string(tNumberFree.tiNumbrYear), trim(tNumberFree.tcNumbrType)))
               oiReturnStatus = -1.

        <M-4 run SetMessage
           (input  trim(vcNumbrMsg) (icMessage), 
            input  '' (icArguments), 
            input  '' (icFieldName), 
            input  '' (icFieldValue), 
            input  'E' (icType), 
            input  3 (iiSeverity), 
            input  '' (icRowid), 
            input  'BLF-114':U (icFcMsgNumber), 
            input  '' (icFcExplanation), 
            input  '' (icFcIdentification), 
            input  '' (icFcContext), 
            output viFcReturnSuper (oiReturnStatus)) in BNumber>

        return.
    end.

    <M-5 run AddDetailLine
       (input  'Numbr' (icTable), 
        input  ? (icParentRowid), 
        output viFcReturnSuper (oiReturnStatus)) in BNumber>
    if viFcReturnSuper <> 0
    then oiReturnStatus = viFcReturnSuper.
    if viFcReturnSuper < 0
    then return.

    assign tNumbr.Company_ID    = viCompanyId
           tNumbr.NumbrYear     = tNumberFree.tiNumbrYear
           tNumbr.NumbrType     = tNumberFree.tcNumbrType
           tNumbr.NumbrStatus   = {&NUMBERSTATUS-FREE}
           tNumbr.Numbr         = tNumberFree.tiNumbr
           tNumbr.NumbrIsActive = true.
end.

/* ================================================================================================ */
/* Placing the call to ValidateBC here in the code causes only newly added numbers to be validated. */
/* Validating changed numbers would always fail, since Numbr.Numbr is part of the primary key.      */
/* The Foundation Classes ancestor code considers this to be illegal and thus needs to be bypassed. */
/* The Numbr table contains no Numbr_ID field, necessitating this kind of composite primary key.    */
/* ================================================================================================ */
<M-23 run ValidateBC (output viFcReturnSuper (oiReturnStatus)) in BNumber>
if viFcReturnSuper <> 0
then oiReturnStatus = viFcReturnSuper.

/* Load changed Numbers into the instance */
assign vcChangedRowIDs  = ""
       vcChangedNumbers = ""
       vcReleasedRowIDs = "".

/* Make a list of RowIDs so DataLoad only needs to be called once. */
for each tNumberFree where
         tNumberFree.tcNumbrStatus  = {&NUMBERSTATUS-FREE} and
         tNumberFree.tcUpdateStatus = "C" on error undo, throw:
    <Q-6 run NumbrByYearTypeStatus (all) (Read) (NoCache)
       (input viCompanyId, (CompanyId)
        input {&NUMBERSTATUS-FREE}, (NumbrStatus)
        input tNumberFree.tiNumbrYear, (NumbrYear)
        input tNumberFree.tcNumbrType, (NumbrType)
        output dataset tqNumbrByYearTypeStatus) in BNumber >

    find first tqNumbrByYearTypeStatus where
               tqNumbrByYearTypeStatus.tiCompany_ID  = viCompanyId             and
               tqNumbrByYearTypeStatus.tiNumbrYear   = tNumberFree.tiNumbrYear and
               tqNumbrByYearTypeStatus.tcNumbrType   = tNumberFree.tcNumbrType and
               tqNumbrByYearTypeStatus.tcNumbrStatus = {&NUMBERSTATUS-FREE}
               no-error.

    if not available tqNumbrByYearTypeStatus
    then do:
        assign vcNumbrMsg     = trim(subst(#T-34'The modified number with year &1 and type &2 could not be found.':200(497)T-34#, string(tNumberFree.tiNumbrYear), trim(tNumberFree.tcNumbrType)))
               oiReturnStatus = -1.

        <M-7 run SetMessage
           (input  trim(vcNumbrMsg) (icMessage), 
            input  '' (icArguments), 
            input  '' (icFieldName), 
            input  '' (icFieldValue), 
            input  'E' (icType), 
            input  3 (iiSeverity), 
            input  '' (icRowid), 
            input  'BLF-115':U (icFcMsgNumber), 
            input  '' (icFcExplanation), 
            input  '' (icFcIdentification), 
            input  '' (icFcContext), 
            output viFcReturnSuper (oiReturnStatus)) in BNumber>

        return.
    end.

    assign vcTempRowID = tqNumbrByYearTypeStatus.tc_rowid
           viOldNumber = tqNumbrByYearTypeStatus.tiNumbr.

    /* If the user changed the number-value for the FREE-status record,  */
    /* the new value must not be less than the old value.                */
    /* If the value is less than the old value, there should be a record */
    /* with status RELEASED and that number AND all numbers in between   */
    /* should also be available as RELEASED                              */
    if tNumberFree.tiNumbr < tqNumbrByYearTypeStatus.tiNumbr
    then do:
        <Q-45 run NumbrByYearTypeStatus (all) (Read) (NoCache)
           (input viCompanyId, (CompanyId)
            input {&NUMBERSTATUS-RELEASED}, (NumbrStatus)
            input tNumberFree.tiNumbrYear, (NumbrYear)
            input tNumberFree.tcNumbrType, (NumbrType)
            output dataset tqNumbrByYearTypeStatus) in BNumber >

        find first tqNumbrByYearTypeStatus where
                   tqNumbrByYearTypeStatus.tiCompany_ID  = viCompanyId              and
                   tqNumbrByYearTypeStatus.tiNumbrYear   = tNumberFree.tiNumbrYear  and
                   tqNumbrByYearTypeStatus.tcNumbrType   = tNumberFree.tcNumbrType  and
                   tqNumbrByYearTypeStatus.tcNumbrStatus = {&NUMBERSTATUS-RELEASED} and
                   tqNumbrByYearTypeStatus.tiNumbr       = tNumberFree.tiNumbr
                   no-error.

        if not available tqNumbrByYearTypeStatus
        then do:
            assign vcNumbrMsg     = trim(subst(#T-43'The new value (&1) should either be bigger than the old value (&2) or,':255(73468360)T-43#, string(tNumberFree.tiNumbr), string(viOldNumber))) + chr(10) +
                                    trim(subst(#T-44'the new value (&1) should exist with status &2.':255(413960739)T-44#, string(tNumberFree.tiNumbr), {&NUMBERSTATUS-RELEASED-TR}))
                   oiReturnStatus = -1.

            <M-21 run SetMessage
               (input  trim(vcNumbrMsg) (icMessage), 
                input  '' (icArguments), 
                input  '' (icFieldName), 
                input  '' (icFieldValue), 
                input  'E' (icType), 
                input  3 (iiSeverity), 
                input  '' (icRowid), 
                input  'BLF-119':U (icFcMsgNumber), 
                input  '' (icFcExplanation), 
                input  '' (icFcIdentification), 
                input  '' (icFcContext), 
                output viFcReturnSuper (oiReturnStatus)) in BNumber>

            return.
        end.

        assign viLastNumber = 0
               vlOK         = true.

        for each tqNumbrByYearTypeStatus where
                 tqNumbrByYearTypeStatus.tiCompany_ID  = viCompanyId              and
                 tqNumbrByYearTypeStatus.tiNumbrYear   = tNumberFree.tiNumbrYear  and
                 tqNumbrByYearTypeStatus.tcNumbrType   = tNumberFree.tcNumbrType  and
                 tqNumbrByYearTypeStatus.tcNumbrStatus = {&NUMBERSTATUS-RELEASED} and
                 tqNumbrByYearTypeStatus.tiNumbr      >= tNumberFree.tiNumbr      and
                 tqNumbrByYearTypeStatus.tiNumbr      <= viOldNumber
                 by tqNumbrByYearTypeStatus.tiNumbr
                 on error undo, throw:
            if viLastNumber = 0
            then assign viLastNumber = tqNumbrByYearTypeStatus.tiNumbr.
            else
            if tqNumbrByYearTypeStatus.tiNumbr > viLastNumber + 1
            then do:
                assign vlOK = false.
                leave.
            end.

            assign viLastNumber = tqNumbrByYearTypeStatus.tiNumbr.
        end.

        if not vlOK
        then do:
            assign vcNumbrMsg     = trim(subst(#T-47'Unable to set new number to &1 for year &2 and type &3 because number &4 is not available with status &5.':255(73459412)T-47#,
                                               string(tNumberFree.tiNumbr),
                                               string(tNumberFree.tiNumbrYear),
                                               tNumberFree.tcNumbrType,
                                               string(viLastNumber + 1),
                                               {&NUMBERSTATUS-RELEASED-TR}))
                   oiReturnStatus = -1.

            <M-46 run SetMessage
               (input  trim(vcNumbrMsg) (icMessage), 
                input  '' (icArguments), 
                input  '' (icFieldName), 
                input  '' (icFieldValue), 
                input  'E' (icType), 
                input  3 (iiSeverity), 
                input  '' (icRowid), 
                input  'BLF-441':U:U (icFcMsgNumber), 
                input  '' (icFcExplanation), 
                input  '' (icFcIdentification), 
                input  '' (icFcContext), 
                output viFcReturnSuper (oiReturnStatus)) in BNumber>

            return.
        end.
    end.

    /* Verify that no Numbr records of the same Year and Type exist with */
    /* status = 'CLAIMED' or 'DRAFT'.                                    */
    /* If such records do exist, no update is done for this Year/Type.   */
    <Q-18 assign vlFcQueryRecordsAvailable = NumbrByYearTypeStatus (NoCache)
       (input viCompanyId, (CompanyId)
        input {&NUMBERSTATUS-CLAIMED} + ',' + {&NUMBERSTATUS-DRAFT}, (NumbrStatus)
        input tNumberFree.tiNumbrYear, (NumbrYear)
        input tNumberFree.tcNumbrType, (NumbrType)) in BNumber >

    if vlFcQueryRecordsAvailable <> false
    then do:
        assign vcNumbrMsg     = trim(subst(#T-36'Records with status Claimed or Draft exist for year &1 and type &2. No update was done.':200(499)T-36#, string(tNumberFree.tiNumbrYear), trim(tNumberFree.tcNumbrType)))
               oiReturnStatus = -1.

        <M-19 run SetMessage
           (input  trim(vcNumbrMsg) (icMessage), 
            input  '' (icArguments), 
            input  '' (icFieldName), 
            input  '' (icFieldValue), 
            input  'E' (icType), 
            input  3 (iiSeverity), 
            input  '' (icRowid), 
            input  'BLF-118':U (icFcMsgNumber), 
            input  '' (icFcExplanation), 
            input  '' (icFcIdentification), 
            input  '' (icFcContext), 
            output viFcReturnSuper (oiReturnStatus)) in BNumber>

        return.
    end.

    if vcChangedNumbers <> ""
    then assign vcChangedNumbers = vcChangedNumbers + ","
                vcChangedRowIDs  = vcChangedRowIDs  + ",".

    assign vcChangedRowIDs  = vcChangedRowIDs  + vcTempRowID
           vcChangedNumbers = vcChangedNumbers + string(tNumberFree.tiNumbr).
end.

/* Load list of changed numbers into instance and save new values */
if vcChangedRowIDs <> ""
then do:
    <M-10 run DataLoad
       (input  vcChangedRowIDs (icRowids), 
        input  '' (icPkeys), 
        input  '' (icObjectIds), 
        input  '' (icFreeform), 
        input  true (ilKeepPrevious), 
        output viFcReturnSuper (oiReturnStatus)) in BNumber>
    if viFcReturnSuper <> 0
    then oiReturnStatus = viFcReturnSuper.
    if viFcReturnSuper < 0
    then return.

    do viIDCounter = num-entries(vcChangedRowIDs) to 1 by -1:
        find tNumbr where
             tNumbr.tc_Rowid = entry(viIDCounter, vcChangedRowIDs)
             no-error.

        if not available tNumbr
        then do:
            assign vcNumbrMsg     = trim(#T-37'The updated numbering record could not be found.':200(500)T-37#)
                   oiReturnStatus = -1.

            <M-11 run SetMessage
               (input  trim(vcNumbrMsg) (icMessage), 
                input  '' (icArguments), 
                input  '' (icFieldName), 
                input  '' (icFieldValue), 
                input  'E' (icType), 
                input  3 (iiSeverity), 
                input  entry(viIDCounter, vcChangedRowIDs) (icRowid), 
                input  'BLF-116':U (icFcMsgNumber), 
                input  '' (icFcExplanation), 
                input  '' (icFcIdentification), 
                input  '' (icFcContext), 
                output viFcReturnSuper (oiReturnStatus)) in BNumber>

            return.
        end.

        /* Save date and time in UTC */
        session:timezone = 0.

        assign tNumbr.tc_Status        = "C"
               tNumbr.Numbr            = int(entry(viIDCounter, vcChangedNumbers))
               tNumbr.LastModifiedDate = today
               tNumbr.LastModifiedTime = time
               tNumbr.LastModifiedUser = vcUserLogin.

        session:timezone = viTimeOffset.
        
        /* Make a list of RowIDs to be deleted so DataLoad only needs to be called once */
        <Q-12 run NumbrByYearTypeStatus (all) (Read) (NoCache)
           (input viCompanyId, (CompanyId)
            input {&NUMBERSTATUS-RELEASED}, (NumbrStatus)
            input tNumbr.NumbrYear, (NumbrYear)
            input tNumbr.NumbrType, (NumbrType)
            output dataset tqNumbrByYearTypeStatus) in BNumber >

        for each tqNumbrByYearTypeStatus where
                 tqNumbrByYearTypeStatus.tiCompany_ID  = viCompanyId              and
                 tqNumbrByYearTypeStatus.tcNumbrStatus = {&NUMBERSTATUS-RELEASED} and
                 tqNumbrByYearTypeStatus.tiNumbrYear   = tNumbr.NumbrYear         and
                 tqNumbrByYearTypeStatus.tcNumbrType   = tNumbr.NumbrType
                 on error undo, throw:
            if vcReleasedRowIDs <> ""
            then assign vcReleasedRowIDs = vcReleasedRowIDs + ",".

            assign vcReleasedRowIDs = vcReleasedRowIDs + tqNumbrByYearTypeStatus.tc_rowid.
        end.

        empty temp-table tqNumbrByYearTypeStatus.
    end.
end.

/* Delete status-RELEASED records for updated Numbers */
if vcReleasedRowIDs <> ""
then do:
    <M-13 run DataLoad
       (input  vcReleasedRowIDs (icRowids), 
        input  '' (icPkeys), 
        input  '' (icObjectIds), 
        input  '' (icFreeform), 
        input  true (ilKeepPrevious), 
        output viFcReturnSuper (oiReturnStatus)) in BNumber>
    if viFcReturnSuper <> 0
    then oiReturnStatus = viFcReturnSuper.
    if viFcReturnSuper < 0
    then return.

    for each tNumbr where
             tNumbr.tc_Status = "":
        if can-do(vcReleasedRowIDs, tNumbr.tc_Rowid)
        then assign tNumbr.tc_Status = "D".
    end.
end.

/*======================================================================================================*/
/* Validation + AdditionalUpdates + DataSave                                                            */
/*======================================================================================================*/

/*======================================================================================================*/
/* We need to bypass ValidateBC, because changes to a column belonging to the primary key are           */
/* considered illegal by the Foundation Classes.                                                        */
/* ValidateBC is called only for newly added records (see above).                                       */
/*======================================================================================================*/
assign vlFcDataValidated = yes.

if oiReturnStatus < 0 then return.

<M-17 run DataSave  (output viFcReturnSuper (oiReturnStatus)) in BNumber>
if viFcReturnSuper <> 0
then oiReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then return.


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 = "BNumber".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "ApiSetFreeNumbers".
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/bnumber.apisetfreenumbers.i.xsd", ?).
vhParameter = vhInputDS:get-buffer-handle("tNumberFree").
vhParameter:buffer-create().
assign vhParameter::<field-name-1> = <field-value-1>
       vhParameter::<field-name-2> = <field-value-2>
       ...

/* 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.