CONTENT
01. OVERVIEW
In some specific scenarios, the fields FILORIG must be completed. These fields must be blank as the record is older than the creation of this column.
This rdmake records the field E5_FILORIG according to the bill origin branch (E1_FILORIG or E2_FILORIG), filtering the transactions whose record has the E5_TIPODOC equal to 'VL', 'VM','BA','CP','LJ','V2' or E5_TIPODOC = 'ES'.
Only the records in which this field E5_FILORIG is affected are not saved.
If the posting payment has records in the tables FK1, FK2 e FK5, the fields FK1_FILORI, FK2_FILORI and FK5_FILORI will also have the records adjusted.
02. EXAMPLE OF USE
The Rdmake was developed through what was specified in the “Overview”, and the adjustment is available only for this scenario. If you find other scenarios, the user function is available in this document for customization.
IMPORTANT!
How to use:
We suggest to execute in the staging environment first, and then after validation, in the production environment.
1 - Backup of the database;
2 - Copy the rdmake example below and compile it in the environment to be processed;
3 - In the initial program, in the SmartClient, enter "U_FIXE5FORIG" then click "OK";
4 - Click "Finalize";
5 - In the message to confirm the routine, click "Yes";
6 - After execution, the message PROCESSING FINALIZED is displayed. Click OK;
7 - If you click "No", the routine is not processed and the message PROCESSING CANCELED displayed;
Only re records with E5_TIPODOC equal to 'VL', 'VM','BA','CP','LJ','V2'' or E5_TIPODOC = 'ES’ will be updated.
For the records with E5_MOVFKS = 'S', the fields FK1_FILORI, FK2_FILORI and FK5_FILORI are completed with the content of the fields E1_FILORIG/E2_FILORIG.
RDKAME ##INCLUDE "PROTHEUS.CH"
#INCLUDE "APWIZARD.CH"
Static __oQrySE5 := NIL
//-------------------------------------------------------------------------------
/*/{Protheus.doc} FIXE5FORIG
FIX to save the E5_FILORIG, FK1_FILORI, FK2_FILORI and FK5_FILORI,
based on the content of the fields E1_FILORIG/E2_FILORIG of the bill.
@return nil
@author totvs
@since 10/12/2020
@version P12
/*/
//-------------------------------------------------------------------------------
User Function FIXE5FORIG()
Private oWizard
Private lOk := .T.
oWizard := APWizard():New( "Wizard to adjust base." , “Attention!” ,;
"",;
"This wizard aims at adjusting FILORIG of the table SE5 (not completed) ";
+CHR(10)+CHR(13)+"with the content of the field E1_FILORIG/E2_FILORIG.";
+CHR(10)+CHR(13)+"- You can only run this adjustment in exclusive mode!";
+CHR(10)+CHR(13)+"- Execute the backup of database before updating.";
+CHR(10)+CHR(13)+"- First run the updating in the staging base.",;
{|| .T.}, {|| Process({|lEnd| lOk := FinExecFix(),Iif(lOk,MSGINFO("PROCESSAMENTO FINALIZADO !!","PROCESSO"),MSGINFO("PROCESSAMENTO CANCELADO !!","PROCESSO")) }),.T.},,,,,)
ACTIVATE WIZARD oWizard CENTERED WHEN {||.T.}
Return
//-------------------------------------------------------------------------------
/*/{Protheus.doc} FinExecFix
It saves the field E5_FILORIG in blank for the records downloaded.
@return lRet, Logic indicated in case query was properly executed
@author totvs
@since 10/12/2020
@version P12
/*/
//-------------------------------------------------------------------------------
Static Function FinExecFix()
Local aArea As Array
Local cEmpUlt As Character
Local lRet As Logical
Local aRetSM0
Local nInc As Numeric
aArea:= GetArea()
lRet := .F.
If MsgYesNo("Database is edited after this confirmation! Do you really want to update it?")
lRet := .T.
cEmpUlt := ""
OpenSM0()
SM0->(dbGotop())
aRetSM0 := FWLoadSM0()
For nInc := 1 To Len( aRetSM0 )
If aRetSM0[nInc][1] <> cEmpUlt
RpcSetType(3)
RpcSetEnv( aRetSM0[nInc][1], aRetSM0[nInc][2] )
FnE5Filori()
RpcClearEnv()
If __oQrySE5 <> NIL
__oQrySE5:Destroy()
EndIf
__oQrySE5 := NIL
Endif
cEmpUlt := aRetSM0[nInc][1]
Next
Endif
RestArea(aArea)
Return(lRet)
//-------------------------------------------------------------------------------
// FK5BUSCA
//Function to find FK5 from transaction SE5
//-------------------------------------------------------------------------
Static Function FK5BUSCA(cChave, lEstorno)
Local uRet := ""
Local cQuery := ""
Default cChave := ''
Default lEstorno := .F.
cQuery := "SELECT FK5.FK5_IDMOV URET"
cQuery += "FROM "+RetSqlName("SE5")+" SE5 "
cQuery += "INNER JOIN "+RetSqlName("FKA")+" FKA1 ON FKA1.FKA_FILIAL = SE5.E5_FILIAL AND FKA1.FKA_IDORIG = SE5.E5_IDORIG "
cQuery += " LEFT JOIN "+RetSqlName("FKA")+" FKA2 ON FKA2.FKA_FILIAL = FKA1.FKA_FILIAL AND FKA2.FKA_IDPROC = FKA1.FKA_IDPROC "
cQuery += "INNER JOIN "+RetSqlName("FK5")+" FK5 ON FK5.FK5_FILIAL = FKA2.FKA_FILIAL AND FKA2.FKA_TABORI = 'FK5' AND FK5.FK5_IDMOV = FKA2.FKA_IDORIG "
If lEstorno
cQuery += "AND FK5.FK5_TPDOC = 'ES' "
Else
cQuery += "AND FK5.FK5_TPDOC <> 'ES' "
EndIf
cQuery += "WHERE "
cQuery += "SE5.E5_FILIAL || SE5.E5_PREFIXO || SE5.E5_NUMERO || SE5.E5_PARCELA || SE5.E5_TIPO || SE5.E5_CLIFOR || SE5.E5_LOJA || SE5.E5_SEQ = '" + cChave + "'"
If lEstorno
cQuery += "AND SE5.E5_TIPODOC = 'ES' "
Else
cQuery += "AND FK5.FK5_TPDOC <> 'ES' "
EndIf
cQuery += "AND SE5.D_E_L_E_T_ = ' '"
cQuery := ChangeQuery(cQuery)
uRet := MpSysExecScalar(cQuery,"URET")
Return uRet
//-------------------------------------------------------------------------------
//FnE5Filori
//It saves the field E5_FILORIG in blank for the records downloaded.
//-------------------------------------------------------------------------------
Static Function FnE5Filori() As Logical
Local cQry1 As Character
Local cAlias1 As Character
Local cIdMovFK5 As Character
Local lRet As Logical
Local lEstorno As Logical
Local nTamSE5 As Numeric
cQry1 := ""
cAlias1 := ""
cIdMovFK5 := ""
lRet := .F.
lEstorno := .F.
nTamSE5 := Len(Alltrim(xFilial("SE5")))
//Query para buscar registros inconsistentes na tabela SE5
If __oQrySE5 == Nil
cQry1 := "SELECT SE5.R_E_C_N_O_ RECSE5,E5_MOVFKS MOVFKS, E2_FILIAL FILIAL, E2_FILORIG FILORIG, SE2.R_E_C_N_O_ RECSE "
cQry1 += "FROM " + RetSqlName("SE5") + " SE5 "
cQry1 += "INNER JOIN " + RetSqlName("SE2") + " SE2 "
cQry1 += "ON "
If nTamSE5 > 0
cQry1 += "SUBSTRING(SE5.E5_FILIAL,1," + Str(nTamSE5) + ") = SUBSTRING(SE2.E2_FILORIG,1," + Str(nTamSE5) + ") AND "
EndIf
cQry1 += "E5_PREFIXO = E2_PREFIXO "
cQry1 += "AND E5_NUMERO = E2_NUM "
cQry1 += "AND E5_PARCELA = E2_PARCELA "
cQry1 += "AND E5_TIPO = E2_TIPO "
cQry1 += "AND E5_CLIFOR = E2_FORNECE "
cQry1 += "AND E5_LOJA = E2_LOJA "
cQry1 += "AND E5_NATUREZ = E2_NATUREZ "
cQry1 += "AND SE2.D_E_L_E_T_ = ' ' "
cQry1 += "AND SE2.E2_FILORIG <> ' ' "
cQry1 += "WHERE E5_FILORIG = ' ' AND SE5.D_E_L_E_T_ = ' ' "
cQry1 += "AND ((E5_RECPAG = 'P' AND E5_TIPODOC IN ('VL','VM','BA','CP','LJ','V2')) OR (E5_RECPAG = 'R' AND E5_TIPODOC = 'ES')) "
cQry1 += "UNION "
cQry1 += "SELECT SE5.R_E_C_N_O_ RECSE5,E5_MOVFKS MOVFKS, E1_FILIAL FILIAL, E1_FILORIG FILORIG, SE1.R_E_C_N_O_ RECSE "
cQry1 += "FROM " + RetSqlName("SE5") + " SE5 "
cQry1 += "INNER JOIN " + RetSqlName("SE1") + " SE1 "
cQry1 += "ON "
If nTamSE5 > 0
cQry1 += "SUBSTRING(SE5.E5_FILIAL,1," + Str(nTamSE5) + ") = SUBSTRING(SE1.E1_FILORIG,1," + Str(nTamSE5) + ") AND "
EndIf
cQry1 += "E5_PREFIXO = E1_PREFIXO "
cQry1 += "AND E5_NUMERO = E1_NUM "
cQry1 += "AND E5_PARCELA = E1_PARCELA "
cQry1 += "AND E5_TIPO = E1_TIPO "
cQry1 += "AND E5_CLIFOR = E1_CLIENTE "
cQry1 += "AND E5_LOJA = E1_LOJA "
cQry1 += "AND E5_NATUREZ = E1_NATUREZ "
cQry1 += "AND SE1.D_E_L_E_T_ = ' ' "
cQry1 += "AND SE1.E1_FILORIG <> ' ' "
cQry1 += "WHERE E5_FILORIG = ' ' AND SE5.D_E_L_E_T_ = ' ' "
cQry1 += "AND ((E5_RECPAG = 'R' AND E5_TIPODOC IN ('VL','VM','BA','CP','LJ','V2')) OR (E5_RECPAG = 'P' AND E5_TIPODOC = 'ES')) "
cQry1 := ChangeQuery(cQry1)
__oQrySE5 := FWPreparedStatement():New(cQry1)
EndIf
cQry1 := __oQrySE5:GetFixQuery()
cAlias1 := MpSysOpenQuery(cQry1)
DbGotop()
While (cAlias1)->(!Eof())
SE5->(dbGoto( (cAlias1)->RECSE5 ))
Reclock("SE5",.F.)
SE5->E5_FILORIG := (cAlias1)->FILORIG
MsUnlock()
If (cAlias1)->MOVFKS == "S"
If SE5->E5_TABORI =="FK2"
FK2->(dbSetOrder(1))
If FK2->(dbSeek(SE5->E5_FILIAL+SE5->E5_IDORIG))
If Empty(FK2->FK2_FILORI)
Reclock("FK2",.F.)
FK2->FK2_FILORI := (cAlias1)->FILORIG
MsUnlock()
EndIf
EndIf
lEstorno := ( SE5->E5_RECPAG == "R" .And. SE5->E5_TIPODOC = 'ES' )
ElseIf SE5->E5_TABORI =="FK1"
FK1->(dbSetOrder(1))
If FK1->(dbSeek(SE5->E5_FILIAL+SE5->E5_IDORIG))
If Empty(FK1->FK1_FILORI)
Reclock("FK1",.F.)
FK1->FK1_FILORI := (cAlias1)->FILORIG
MsUnlock()
EndIf
EndIf
lEstorno := ( SE5->E5_RECPAG == "P" .And. SE5->E5_TIPODOC = 'ES' )
EndIf
cIdMovFK5 := FK5BUSCA(SE5->(E5_FILIAL+E5_PREFIXO+E5_NUMERO+E5_PARCELA+E5_TIPO+E5_CLIFOR+E5_LOJA+E5_SEQ),lEstorno)
FK5->(dbSetOrder(1))
If FK5->(dbSeek(SE5->E5_FILIAL+cIdMovFK5))
If Empty(FK5->FK5_FILORI)
Reclock("FK5",.F.)
FK5->FK5_FILORI := (cAlias1)->FILORIG
MsUnlock()
EndIf
EndIf
EndIf
(cAlias1)->(DbSkip())
EndDo
If Select(cAlias1) > 0
(cAlias1)->(DBCloseArea())
lRet := .T.
EndIf
Return(lRet)
|
|---|
03. TABLES
This Rdmake adjusts the tables SE5, FK1, FK2 and FK5 in the scenarios described in the topic "Example of use".
Visão Geral
Import HTML Content
Conteúdo das Ferramentas
Tarefas