Description
Submethod of PerformWorkItem that performans direct db-access
Parameters
| iiDaemonQueueID | input | integer | ID of the daemon queue record that indicates the work that needs to be done. |
| oiNbrOfDeletedQPostingLine | output | integer | |
| oiNbrOfDeletedQPostingSAF | output | integer | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/bhistorydaemonprocessor.p)
/* ================================================================================= */
/* This method should be called when the QPostingLine and QPostingSaf records must */
/* be processed. The QPostingLine- (and QPostingSaf-)records will be deleted and */
/* the PostingHist, PostingSafCombined and PostingSafHist will be updated */
/* Linked to this, also QFRXCube and QFRWCubeSaf records will be created */
/* ================================================================================= */
/* Program structure : */
/* PerformWorkItemDIRECT */
/* --> PerformWorkItemDIRECTCreatePostingSafCombined */
/* --> PerformWorkItemDIRECTUpdatePostingHist */
/* --> PerformWorkItemDIRECTUpdatePostingHistBalances */
/* --> PerformWorkItemDIRECTUpdateFcDaemonQueue */
/* */
/* - PerformWorkItemDIRECT: this method treats all QPostingLines one by one and is */
/* the main loop */
/* - PerformWorkItemDIRECTCreatePostingSafCombined: creates PostingSAFCombined and */
/* PostingSAFHist when it is a new combination */
/* - PerformWorkItemDIRECTUpdatePostingHist: updates or creates the PostingHist */
/* record of the given period */
/* - PerformWorkItemDIRECTUpdatePostingHistBalances: updates the balances of all */
/* PostingHist records which are > given period */
/* ================================================================================= */
/* Tables which are impacted in this flow */
/* - QPostingLine and QPostingSaf (delete) */
/* - PostingHist (create and/or update) */
/* - PostingSafCombined and PostingSafHist (create) */
/* - QFRWCube and QFRWCubeSaf */
/* - fcDaemonQueue (update) */
/* ================================================================================= */
/* High-Level Program Flow : */
/* for each QpostingLine : */
/* lock the record */
/* get the PostingSafCombined_ID */
/* create PostingSafCombined and PostingSafHist if needed */
/* update PostingHist and create QFRWCube */
/* delete QPostingLine */
/* Keep a trace in DaemonQueue */
/* end. */
/* ================================================================================= */
/* =============================== */
/* Local definitions outside of CB */
/* =============================== */
define variable vtTimeStarted as DATETIME-TZ no-undo.
define buffer bQPostingLine for QPostingLine.
/* =============================================================================================================================================== */
/* Start the persistenceLayer once so it can be used to get new db-sequences in all sub-methods as we will pass in the handle to these sub-methods */
/* =============================================================================================================================================== */
<M-61 run StartPersistence
(output vhLocalHandleToThePersistence (ohPersistence),
output viFcReturnSuper (oiReturnStatus)) in BHistoryDaemonProcessor>
if viFcReturnSuper <> 0
then assign oiReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then Return.
/* ====================================================================== */
/* Keep track of the current time - taken the time-zones etc into account */
/* This is needed for storing the avg-processing time */
/* ====================================================================== */
assign vtTimeStarted = now.
/* ====================================================================== */
/* Keep the ID of the last QPostingLine record to make sure we do not */
/* handle records that are created while we are processing others here */
/* ====================================================================== */
assign viLastQPostingLineID = 0.
find last QPostingLine
no-lock no-error.
if available QPostingLine
then assign viLastQPostingLineID = QPostingLine.QPostingLine_ID.
/* =================== */
/* Initialise counters */
/* =================== */
assign viProcessingCounterForReference = 0
oiNbrOfDeletedQPostingLine = 0
oiNbrOfDeletedQPostingSAF = 0.
/* ==================================================================================================== */
/* Process all the existing QPostingLine that were available to be processed when entering this method */
/* We are using 1 transaction block per Q-record */
/* We read bQpostingLine with no-lock, we read QPostingLine with exclusive-lock */
/* QPostingLine is availabale in the underlying code */
/* ==================================================================================================== */
for each bQPostingLine where
bQPostingLine.QPostingLine_ID <= viLastQPostingLineID
no-lock
TRANSACTION
on error undo, throw :
/* ========================================= */
/* Skip QPostingLine records that are locked */
/* ========================================= */
Find QPostingLine where
rowid(QPostingLine) = rowid (bQPostingLine)
exclusive-lock no-wait no-error.
if not available QPostingLine
then next.
/* ============================================================================ */
/* Be sure we don't have unknown values */
/* ============================================================================ */
if QPostingLine.Company_ID = ? then assign QPostingLine.Company_ID = 0.
if QPostingLine.GL_ID = ? then assign QPostingLine.GL_ID = 0.
if QPostingLine.Journal_ID = ? then assign QPostingLine.Journal_ID = 0.
if QPostingLine.Layer_ID = ? then assign QPostingLine.Layer_ID = 0.
if QPostingLine.Currency_ID = ? then assign QPostingLine.Currency_ID = 0.
if QPostingLine.Division_ID = ? then assign QPostingLine.Division_ID = 0.
if QPostingLine.CostCentre_ID = ? then assign QPostingLine.CostCentre_ID = 0.
if QPostingLine.Project_ID = ? then assign QPostingLine.Project_ID = 0.
if QPostingLine.IntercoBusinessRelation_ID = ? then assign QPostingLine.IntercoBusinessRelation_ID = 0.
/* ============================================================================================================================= */
/* Set viPostingSafCombinedID in case there are SAFs used and there is already a matching PostingSafCombined record */
/* If there is no such PostingSafCombined record yet then we create it and set viPostingSafCombinedID based upon this new record */
/* ============================================================================================================================= */
assign vlSafUsed = false
viPostingSafCombinedID = 0
viGLSafIDs = 0
viCostCentreSafIDs = 0
viProjectSafIDs = 0
viCostCentreSafStructureID = 0
viProjectSafStructureID = 0
viGLSafStructureID = 0.
for each QPostingSaf of QPostingLine
no-lock
on error undo, throw :
assign vlSafUsed = true.
if QPostingSaf.QPostingSafParentType = {&POSTINGSAFPARENTTYPE-GL} and QPostingSafInputSequence >= 1 and QPostingSafInputSequence <=5
then assign viGLSafIDs [QPostingSafInputSequence] = QPostingSaf.Saf_ID.
if QPostingSaf.QPostingSafParentType = {&POSTINGSAFPARENTTYPE-COSTCENTRE} and QPostingSafInputSequence >= 1 and QPostingSafInputSequence <=5
then assign viCostCentreSafIDs [QPostingSaf.QPostingSafInputSequence] = QPostingSaf.Saf_ID.
if QPostingSaf.QPostingSafParentType = {&POSTINGSAFPARENTTYPE-PROJECT} and QPostingSafInputSequence >= 1 and QPostingSafInputSequence <=5
then assign viProjectSafIDs [QPostingSaf.QPostingSafInputSequence] = QPostingSaf.Saf_ID.
if viCostCentreSafStructureID = 0 and
QPostingSaf.QPostingSafParentType = {&POSTINGSAFPARENTTYPE-COSTCENTRE}
then assign viCostCentreSafStructureID = QPostingSaf.SafStructure_ID.
if viProjectSafStructureID = 0 and
QPostingSaf.QPostingSafParentType = {&POSTINGSAFPARENTTYPE-PROJECT}
then assign viProjectSafStructureID = QPostingSaf.SafStructure_ID.
if viGLSafStructureID = 0 and
QPostingSaf.QPostingSafParentType = {&POSTINGSAFPARENTTYPE-GL}
then assign viGLSafStructureID = QPostingSaf.SafStructure_ID.
end. /* for each QPostingSaf */
if vlSafUsed = true
then do:
for each PostingSafCombined where
PostingSafCombined.Project1Saf_ID = viProjectSafIDs [1] and
PostingSafCombined.Project2Saf_ID = viProjectSafIDs [2] and
PostingSafCombined.Project3Saf_ID = viProjectSafIDs [3] and
PostingSafCombined.Project4Saf_ID = viProjectSafIDs [4] and
PostingSafCombined.Project5Saf_ID = viProjectSafIDs [5] and
PostingSafCombined.CostCentre1Saf_ID = viCostCentreSafIDs [1] and
PostingSafCombined.CostCentre2Saf_ID = viCostCentreSafIDs [2] and
PostingSafCombined.CostCentre3Saf_ID = viCostCentreSafIDs [3] and
PostingSafCombined.CostCentre4Saf_ID = viCostCentreSafIDs [4] and
PostingSafCombined.CostCentre5Saf_ID = viCostCentreSafIDs [5] and
PostingSafCombined.GL1Saf_ID = viGLSafIDs [1] and
PostingSafCombined.GL2Saf_ID = viGLSafIDs [2] and
PostingSafCombined.GL3Saf_ID = viGLSafIDs [3] and
PostingSafCombined.GL4Saf_ID = viGLSafIDs [4] and
PostingSafCombined.GL5Saf_ID = viGLSafIDs [5] and
PostingSafCombined.ProjectSafStructure_ID = viProjectSafStructureID and
PostingSafCombined.CostCentreSafStructure_ID = viCostCentreSafStructureID and
PostingSafCombined.GLSafStructure_ID = viGLSafStructureID
no-lock
on error undo, throw :
assign viPostingSafCombinedID = PostingSafCombined.PostingSafCombined_ID.
leave.
end. /* for each PostingSafCombined */
/* ===================================================================================== */
/* Create the new PostingSafCombined record and return viPostingSafCombinedID to be used */
/* ===================================================================================== */
if viPostingSafCombinedID = 0
then do :
<M-58 run PerformWorkItemDIRECTCreatePostingSafCombined
(input vhLocalHandleToThePersistence (ihLocalHandleToThePersistence),
input viGLSafIDs [1] (iiGL1SafID),
input viGLSafIDs [2] (iiGL2SafID),
input viGLSafIDs [3] (iiGL3SafID),
input viGLSafIDs [4] (iiGL4SafID),
input viGLSafIDs [5] (iiGL5SafID),
input viCostCentreSafIDs [1] (iiCostCentre1SafID),
input viCostCentreSafIDs [2] (iiCostCentre2SafID),
input viCostCentreSafIDs [3] (iiCostCentre3SafID),
input viCostCentreSafIDs [4] (iiCostCentre4SafID),
input viCostCentreSafIDs [5] (iiCostCentre5SafID),
input viProjectSafIDs [1] (iiProject1SafID),
input viProjectSafIDs [2] (iiProject2SafID),
input viProjectSafIDs [3] (iiProject3SafID),
input viProjectSafIDs [4] (iiProject4SafID),
input viProjectSafIDs [5] (iiProject5SafID),
input viGLSafStructureID (iiGLSafStructureID),
input viCostCentreSafStructureID (iiCostCentreSafStructureID),
input viProjectSafStructureID (iiProjectSafStructureID),
output viPostingSafCombinedID (oiNewPostingSafCombinedID),
output viDummy (oiNbrOfCreatedPostingSAFCombined),
output viDummy (oiNbrOfCreatedPostingSAFHist),
output viFcReturnSuper (oiReturnStatus)) in BHistoryDaemonProcessor>
if viFcReturnSuper <> 0
then assign oiReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then Return.
end. /* if viPostingSafCombinedID = 0 */
end. /* if vlSafUsed = true*/
/* ============================================================================== */
/* Run the Update to PostingHist, input is QPostingLine which is exclusive-locked */
/* ============================================================================== */
<M-40 run PerformWorkItemDIRECTUpdPHist
(input vhLocalHandleToThePersistence (ihLocalHandleToThePersistence),
input viPostingSafCombinedID (iiPostingSafCombinedID),
output viDummy (oiNbrOfCreatedPostingHist),
output viDummy (oiNbrOfUpdatedPostingHist),
output viDummy (oiNbrOfCreatedQFRWCube),
output viDummy (oiNbrOfCreatedQFRWCubeSaf),
output viFcReturnSuper (oiReturnStatus)) in BHistoryDaemonProcessor>
if viFcReturnSuper <> 0
then assign oiReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then Return.
/* =========================================================================================== */
/* Remove the QPostingLine record and its underlying SAFs as it has been processed now */
/* =========================================================================================== */
for each QPostingSAF of QPostingLine
exclusive-lock
on error undo, throw :
assign oiNbrOfDeletedQPostingSAF = oiNbrOfDeletedQPostingSAF + 1.
delete QPostingSAF.
end. /* for each QPostingSAF of QPostingLine */
assign oiNbrOfDeletedQPostingLine = oiNbrOfDeletedQPostingLine + 1
viProcessingCounterForReference = viProcessingCounterForReference + 1.
delete QPostingLine.
/* =============================================================================================== */
/* After 250 records, update the reference in fcDaemonQueue using viProcessingCounterForReference */
/* =============================================================================================== */
if viProcessingCounterForReference >= 250
then do :
<M-44 run PerformWorkItemDIRECTUpdFcDaemonQueue
(input vhLocalHandleToThePersistence (ihLocalHandleToThePersistence),
input iiDaemonQueueID (iiDaemonQueueID),
input if oiNbrOfDeletedQPostingLine = 0 then (now - vtTimeStarted) else (now - vtTimeStarted) / oiNbrOfDeletedQPostingLine (iiAvgProcesingTime),
input oiNbrOfDeletedQPostingLine (iiNbrQPostingLinesProcessed),
output viFcReturnSuper (oiReturnStatus)) in BHistoryDaemonProcessor>
assign viProcessingCounterForReference = 0.
if viFcReturnSuper <> 0
then assign oiReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then Return.
end. /* if viProcessingCounterForReference >= 250 */
end. /* for each QPostingLine */
/* ========================================================================================================= */
/* After processing everything, update the reference in fcDaemonQueue using viProcessingCounterForReference */
/* ========================================================================================================= */
do Transaction on error undo, throw:
<M-47 run PerformWorkItemDIRECTUpdFcDaemonQueue
(input vhLocalHandleToThePersistence (ihLocalHandleToThePersistence),
input iiDaemonQueueID (iiDaemonQueueID),
input if oiNbrOfDeletedQPostingLine = 0 then (now - vtTimeStarted) else (now - vtTimeStarted) / oiNbrOfDeletedQPostingLine (iiAvgProcesingTime),
input oiNbrOfDeletedQPostingLine (iiNbrQPostingLinesProcessed),
output viFcReturnSuper (oiReturnStatus)) in BHistoryDaemonProcessor>
assign viProcessingCounterForReference = 0.
if viFcReturnSuper <> 0
then assign oiReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then Return.
end. /* do Transaction */
/* ======================== */
/* Release the used buffers */
/* ======================== */
release bQPostingLine.
release QPostingLine.
release QPostingSAF.