Problema / Problem:
Hay ocasiones que necesitamos cambiar a ciertos proveedores o clientes de perfil de cuentas, esto lo podemos hacer desde la modificación de Cliente 27.1.1.2 o de Proveedor 28.1.1.2. Pero que pasa si tiene documentos abiertos? Para ello es necesario correr un programa que actualice la cuenta.
In some cases we need to change the profile accounts to our suppliers or customer, for that we need to update in 27.1.1.2 or 28.1.1.2 and run the next utility.
In some cases we need to change the profile accounts to our suppliers or customer, for that we need to update in 27.1.1.2 or 28.1.1.2 and run the next utility.
Versión / Version:
QAD EE 2013
Solución / Solve
El siguiente programa recibe un archivo csv de entrada con 3 columnas el código del cliente o proveedor, la cuenta GL anterior ,la nueva cuenta GL a utilizar, el programa lo que hace es buscar que documentos estan abiertos y cambia el ID de la cuenta vieja por el ID de la cuenta nueva en la tabla PostingLine.
This program read a CSV file with 3 columns: The supplier or customer code, the old GL account, the new account.
Then look for open documents and update the PostingLine.GL_ID with the new GL account.
DEF VAR i AS CHARACTER NO-UNDO.
DEF VAR lvc-infile AS CHARACTER NO-UNDO INITIAL "intercompany.csv"
FORMAT "X(30)".
DEF VAR lvc-file AS CHARACTER NO-UNDO INITIAL "/intercompany.log"
FORMAT "X(30)".
DEF VAR lvc-update AS LOGICAL NO-UNDO INITIAL FALSE.
DEF VAR v-totrec AS INTEGER NO-UNDO INITIAL 0.
DEF VAR new_accunt AS INTEGER NO-UNDO INITIAL 0.
DEF TEMP-TABLE ttData NO-UNDO
FIELD tt_supp AS CHARACTER
FIELD tt_oldact AS CHARACTER
FIELD tt_newacta AS CHARACTER .
DEF STREAM s-output.
FORM
lvc-infile COLON 18 LABEL "INPUT FILE"
lvc-file COLON 18 LABEL "OUTPUT FILE"
lvc-update COLON 18 LABEL "Update"
WITH FRAME a SIDE-LABELS WIDTH 80.
REPEAT:
UPDATE lvc-infile
lvc-file
lvc-update
WITH FRAME a.
{us/wb/wbrp06.i
&command = update
&fields = " lvc-infile lvc-file lvc-update"
&frm = "a"}
/* Output Destination Selection */
{us/gp/gpselout.i
&printType = "printer"
&printWidth = 180
&pagedFlag = " "
&stream = " "
&appendToFile = " "
&streamedOutputToTerminal = " "
&withBatchOption = "no"
&displayStatementType = 1
&withCancelMessage = "yes"
&pageBottomMargin = 6
&withEmail = "yes"
&withWinprint = "yes"
&defineVariables = "yes"}
{us/bbi/mfphead.i}
v-totrec = 0.
EMPTY temp-table ttData.
INPUT FROM VALUE(lvc-infile).
REPEAT:
CREATE ttData.
IMPORT DELIMITER "," ttData .
v-totrec = v-totrec + 1.
END. /* REPEAT */
INPUT CLOSE.
OUTPUT STREAM s-output TO VALUE(lvc-file) PAGE-SIZE 0.
FOR EACH ttData WHERE tt_supp <> "" NO-LOCK:
/*get gl_Id for new account*/
FOR EACH gl where glcode = ttData.tt_newacta NO-LOCK:
new_accunt = 0.
PUT STREAM s-output "New Account " + STRING(Gl.gl_id) FORMAT "X(20)"
"|"
tt_supp
"|".
ASSIGN new_accunt = Gl_id.
END.
/*Suppliers*/
FOR EACH creditor WHERE creditorcode = ttData.tt_supp NO-LOCK,
EACH cinvoice OF creditor WHERE cinvoiceisopen = TRUE NO-LOCK,
EACH cinvoiceposting OF cinvoice NO-LOCK,
EACH posting OF cinvoiceposting NO-LOCK,
EACH postingline OF posting EXCLUSIVE-LOCK,
EACH gl OF postingline where glcode = tt_oldact NO-LOCK:
IF lvc-update = TRUE THEN
ASSIGN postingline.gl = new_accunt
postingline.CrossCompany_ID = 0.
PUT STREAM s-output
tt_oldact "|"
new_accunt "|"
postingline.gl "|"
cinvoicereference
SKIP.
END.
FOR EACH creditor WHERE creditorcode = ttData.tt_supp NO-LOCK,
EACH BusinessRelation of Creditor EXCLUSIVE-LOCK:
ASSIGN BusinessRelationICCode = "".
PUT STREAM s-output BusinessRelationICCode SKIP.
END.
/*Customer*/
FOR EACH debtor WHERE debtorcode = ttData.tt_supp NO-LOCK,
EACH dinvoice OF debtor WHERE dinvoiceisopen = TRUE NO-LOCK,
EACH dinvoiceposting OF dinvoice NO-LOCK,
EACH posting OF dinvoiceposting NO-LOCK,
EACH postingline OF posting EXCLUSIVE-LOCK,
EACH gl OF postingline where glcode = tt_oldact NO-LOCK:
IF lvc-update = TRUE THEN
ASSIGN postingline.gl = new_accunt
CrossCompany_ID = 0.
PUT STREAM s-output
tt_oldact "|"
new_accunt "|"
postingline.gl "|"
DInvoiceDescription
SKIP.
END.
FOR EACH debtor WHERE debtorcode = ttData.tt_supp NO-LOCK,
EACH BusinessRelation of Debtor EXCLUSIVE-LOCK:
ASSIGN BusinessRelationICCode = "".
PUT STREAM s-output BusinessRelationICCode
SKIP.
END.
END.
OUTPUT STREAM s-output CLOSE.
HIDE MESSAGE NO-PAUSE.
{us/mf/mfrtrail.i} /*Generate Report Trailer */
END.
{us/wb/wbrp04.i &frame-spec = a}
This program read a CSV file with 3 columns: The supplier or customer code, the old GL account, the new account.
Then look for open documents and update the PostingLine.GL_ID with the new GL account.
DEF VAR i AS CHARACTER NO-UNDO.
DEF VAR lvc-infile AS CHARACTER NO-UNDO INITIAL "intercompany.csv"
FORMAT "X(30)".
DEF VAR lvc-file AS CHARACTER NO-UNDO INITIAL "/intercompany.log"
FORMAT "X(30)".
DEF VAR lvc-update AS LOGICAL NO-UNDO INITIAL FALSE.
DEF VAR v-totrec AS INTEGER NO-UNDO INITIAL 0.
DEF VAR new_accunt AS INTEGER NO-UNDO INITIAL 0.
DEF TEMP-TABLE ttData NO-UNDO
FIELD tt_supp AS CHARACTER
FIELD tt_oldact AS CHARACTER
FIELD tt_newacta AS CHARACTER .
DEF STREAM s-output.
FORM
lvc-infile COLON 18 LABEL "INPUT FILE"
lvc-file COLON 18 LABEL "OUTPUT FILE"
lvc-update COLON 18 LABEL "Update"
WITH FRAME a SIDE-LABELS WIDTH 80.
REPEAT:
UPDATE lvc-infile
lvc-file
lvc-update
WITH FRAME a.
{us/wb/wbrp06.i
&command = update
&fields = " lvc-infile lvc-file lvc-update"
&frm = "a"}
/* Output Destination Selection */
{us/gp/gpselout.i
&printType = "printer"
&printWidth = 180
&pagedFlag = " "
&stream = " "
&appendToFile = " "
&streamedOutputToTerminal = " "
&withBatchOption = "no"
&displayStatementType = 1
&withCancelMessage = "yes"
&pageBottomMargin = 6
&withEmail = "yes"
&withWinprint = "yes"
&defineVariables = "yes"}
{us/bbi/mfphead.i}
v-totrec = 0.
EMPTY temp-table ttData.
INPUT FROM VALUE(lvc-infile).
REPEAT:
CREATE ttData.
IMPORT DELIMITER "," ttData .
v-totrec = v-totrec + 1.
END. /* REPEAT */
INPUT CLOSE.
OUTPUT STREAM s-output TO VALUE(lvc-file) PAGE-SIZE 0.
FOR EACH ttData WHERE tt_supp <> "" NO-LOCK:
/*get gl_Id for new account*/
FOR EACH gl where glcode = ttData.tt_newacta NO-LOCK:
new_accunt = 0.
PUT STREAM s-output "New Account " + STRING(Gl.gl_id) FORMAT "X(20)"
"|"
tt_supp
"|".
ASSIGN new_accunt = Gl_id.
END.
/*Suppliers*/
FOR EACH creditor WHERE creditorcode = ttData.tt_supp NO-LOCK,
EACH cinvoice OF creditor WHERE cinvoiceisopen = TRUE NO-LOCK,
EACH cinvoiceposting OF cinvoice NO-LOCK,
EACH posting OF cinvoiceposting NO-LOCK,
EACH postingline OF posting EXCLUSIVE-LOCK,
EACH gl OF postingline where glcode = tt_oldact NO-LOCK:
IF lvc-update = TRUE THEN
ASSIGN postingline.gl = new_accunt
postingline.CrossCompany_ID = 0.
PUT STREAM s-output
tt_oldact "|"
new_accunt "|"
postingline.gl "|"
cinvoicereference
SKIP.
END.
FOR EACH creditor WHERE creditorcode = ttData.tt_supp NO-LOCK,
EACH BusinessRelation of Creditor EXCLUSIVE-LOCK:
ASSIGN BusinessRelationICCode = "".
PUT STREAM s-output BusinessRelationICCode SKIP.
END.
/*Customer*/
FOR EACH debtor WHERE debtorcode = ttData.tt_supp NO-LOCK,
EACH dinvoice OF debtor WHERE dinvoiceisopen = TRUE NO-LOCK,
EACH dinvoiceposting OF dinvoice NO-LOCK,
EACH posting OF dinvoiceposting NO-LOCK,
EACH postingline OF posting EXCLUSIVE-LOCK,
EACH gl OF postingline where glcode = tt_oldact NO-LOCK:
IF lvc-update = TRUE THEN
ASSIGN postingline.gl = new_accunt
CrossCompany_ID = 0.
PUT STREAM s-output
tt_oldact "|"
new_accunt "|"
postingline.gl "|"
DInvoiceDescription
SKIP.
END.
FOR EACH debtor WHERE debtorcode = ttData.tt_supp NO-LOCK,
EACH BusinessRelation of Debtor EXCLUSIVE-LOCK:
ASSIGN BusinessRelationICCode = "".
PUT STREAM s-output BusinessRelationICCode
SKIP.
END.
END.
OUTPUT STREAM s-output CLOSE.
HIDE MESSAGE NO-PAUSE.
{us/mf/mfrtrail.i} /*Generate Report Trailer */
END.
{us/wb/wbrp04.i &frame-spec = a}
Comments
Post a Comment