Inserting records from foreign table or text file

markj

Member
I am in the process of creating a UBE that will import a text file into a OneWorld table. It seems to function correctly, however, I now want to add additional records at the time the UBE is run, based on certain values of fields in the records being fetched and inserted into the OneWorld table. IE...The records being imported into this table are journal entries...when a particular entry is read, I want to be able to insert two more records to create two additional journal entries. Does this need to be done to the text file before importing or can it be done with Event Rules? Any help would be greatly appreciated.


B733.2 SP10 AS/400 SQL 7.0

Mark P. Johnson
JDE Administrator
Support Analyst
Community Bio-Resources, Inc.
[email protected]
 
Mark,

You can go to Advanced ER option of Table conversion and add the code to
insert a record based on your criteria. It shouldn't give any problems.

Pradeep.


>From: markj <[email protected]>
>Reply-To: [email protected]
>To: [email protected]
>Subject: Inserting records from foreign table or text file ~~0:5478
>Date: Mon, 12 Feb 2001 13:15:56 -0800 (PST)
>
>I am in the process of creating a UBE that will import a text file into a
>OneWorld table. It seems to function correctly, however, I now want to add
>additional records at the time the UBE is run, based on certain values of
>fields in the records being fetched and inserted into the OneWorld table.
>IE...The records being imported into this table are journal entries...when
>a particular entry is read, I want to be able to insert two more records to
>create two additional journal entries. Does this need to be done to the
>text file before importing or can it be done with Event Rules? Any help
>would be greatly appreciated.
>
>
>B733.2 SP10 AS/400 SQL 7.0
>
>Mark P. Johnson
>JDE Administrator
>Support Analyst
>Community Bio-Resources, Inc.
>[email protected]
>
>
>
>--------------------------
>Visit the forum to view this thread at:
>http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Number=5478
>*************************************************************
>This is the JDEList One World / XE Developers Mailing List.
>Archives and information on how to SUBSCRIBE, and
>UNSUBSCRIBE can be found at http://www.JDELIST.com
>*************************************************************
>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
 
markj,

this is very easy to do with "Table I/O" Event Rules. In my opinion
inserting records this way is much better than using either Table
Conversion's "Insert Row" or a UBE's Database Output as you can easily check
the success/failure of the row insertion.

The advantage to adding the records beforehand is that it is easy to display
each record in the report. I'm not sure how you do this otherwise.
Suggestions anyone?

Chris

>From: markj <[email protected]>
>Reply-To: [email protected]
>To: [email protected]
>Subject: Inserting records from foreign table or text file ~~0:5478
>Date: Mon, 12 Feb 2001 13:15:56 -0800 (PST)
>
>I am in the process of creating a UBE that will import a text file into a
>OneWorld table. It seems to function correctly, however, I now want to add
>additional records at the time the UBE is run, based on certain values of
>fields in the records being fetched and inserted into the OneWorld table.
>IE...The records being imported into this table are journal entries...when
>a particular entry is read, I want to be able to insert two more records to
>create two additional journal entries. Does this need to be done to the
>text file before importing or can it be done with Event Rules? Any help
>would be greatly appreciated.
>
>
>B733.2 SP10 AS/400 SQL 7.0
>
>Mark P. Johnson
>JDE Administrator
>Support Analyst
>Community Bio-Resources, Inc.
>[email protected]
>
>
>
>--------------------------
>Visit the forum to view this thread at:
>http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Number=5478
>*************************************************************
>This is the JDEList One World / XE Developers Mailing List.
>Archives and information on how to SUBSCRIBE, and
>UNSUBSCRIBE can be found at http://www.JDELIST.com
>*************************************************************
>

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
 
H Mark,

You can do that in Event Rules. If you use a text file, the event to select
is "Format is Fetched". There, you will see the mapping of the existing
fields (OC ANI = IC ANI) and you can add your logic.
To add records, use the Table IO button.

Regards

Philippe
----- Original Message -----
From: "markj" <[email protected]>
To: <[email protected]>
Sent: Monday, February 12, 2001 1:15 PM
Subject: Inserting records from foreign table or text file ~~0:5478


> I am in the process of creating a UBE that will import a text file into a
OneWorld table. It seems to function correctly, however, I now want to add
additional records at the time the UBE is run, based on certain values of
fields in the records being fetched and inserted into the OneWorld table.
IE...The records being imported into this table are journal entries...when a
particular entry is read, I want to be able to insert two more records to
create two additional journal entries. Does this need to be done to the
text file before importing or can it be done with Event Rules? Any help
would be greatly appreciated.
>
>
> B733.2 SP10 AS/400 SQL 7.0
>
> Mark P. Johnson
> JDE Administrator
> Support Analyst
> Community Bio-Resources, Inc.
> [email protected]
>
>
>
> --------------------------
> Visit the forum to view this thread at:
>
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Numbe
r=5478
> *************************************************************
> This is the JDEList One World / XE Developers Mailing List.
> Archives and information on how to SUBSCRIBE, and
> UNSUBSCRIBE can be found at http://www.JDELIST.com
> *************************************************************



One World B733 Oracle 8.1 RS6000
 
Hi Mark,
Starting from B733 you can use any Table I/O for any table in the ERs of Table Conversion. Under B7321 the Table I/O possibilities are very limited in TC UBE.

Your issue had been discussed more times and detailed in the past on the Forum. If you want to get some more idea then you can read this threads, e.g.:
Re: Table Conversion..!! One World / XE Developers Zoltan_Gyimesi 12/21/00 10:48 AM

You can find these threads and related issues searching for:
* Table Conversion
* Clearing Work Files
on the OneWorld XE Developers Forum.

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Thank you all for your responses. Another thing I failed to mention is that the input files and output files have different fields. Does this cause a problem? Can you add values to the additional fields in the output table...if so, do you do this through variables? I would like a little more detail if anyone could please give me an example of ER code to handle inserting additional records at the time the input records are read as well as inserting the values in the additional fields. I am very new to the development side of OneWorld and would appreciate some 'hand held' guidance if anyone could find the time to help. Again, thank you!

Mark P. Johnson
JDE Administrator
Support Analyst
Community Bio-Resources, Inc.
[email protected]
 
Hi Mark,
You are welcome among OneWorld developers.
Please, answer us that do you use Table Conversion UBE or the Database Output section feature in a "normal" UBE? Thanks.
This information is necessary to give you more help in your issue.
Good luck,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
This is a multi-part message in MIME format.

------=_NextPart_000_003A_01C09663.30E64560
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hi Mark,

What we usually do here is:
- Disable the "Issue a Write this event" on the mapping grid (bottom left of
the form, it is disabled by default)
- Use a Table IO (Insert) in the output table at the end of the "Format
Fetched" event (if the input is a text file) and map the fields manually. If
we need to, we can handle variables in the Event Rule prior to the Insert.
(Currency conversion, for example, if handle in ER and the converted amounts
stored in variables)

I attached some ER code to this so you can have a look (I don't pretend that
it's a reference or anything. Let me know if you have better solutions...)

Regards

Philippe

----- Original Message -----
From: "markj" <[email protected]>
To: <[email protected]>
Sent: Tuesday, February 13, 2001 7:54 AM
Subject: Re: Inserting records from foreign table or text file


> Thank you all for your responses. Another thing I failed to mention is
that the input files and output files have different fields. Does this cause
a problem? Can you add values to the additional fields in the output
table...if so, do you do this through variables? I would like a little more
detail if anyone could please give me an example of ER code to handle
inserting additional records at the time the input records are read as well
as inserting the values in the additional fields. I am very new to the
development side of OneWorld and would appreciate some 'hand held' guidance
if anyone could find the time to help. Again, thank you!
>
> Mark P. Johnson
> JDE Administrator
> Support Analyst
> Community Bio-Resources, Inc.
> [email protected]
>
>
>
>
>
>
>
>
> --------------------------
> Visit the forum to view this thread at:
>
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Numbe
r=5506
> *************************************************************
> This is the JDEList One World / XE Developers Mailing List.
> Archives and information on how to SUBSCRIBE, and
> UNSUBSCRIBE can be found at http://www.JDELIST.com
> *************************************************************

------=_NextPart_000_003A_01C09663.30E64560
Content-Type: text/plain;
name="TableConversionER.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="TableConversionER.txt"

Listing of ER for Table Conversion: Upload F0401Z1

EVENT: Format Fetched
-----------------------------------------------------------------------
evt_58ERR_ErrorStatus
evt_58ERNO_ErrorNumber
evt_58CRCM_CurrConversionMethd
evt_58CRR_CurrencyConverRate
evt_58CRR1_TriangulationRate1
evt_58CRR2_TriangulationRate2
evt_58CRCD_CurrencyCodeFrom
evt_58CRDC_CurrencyCodeTo
evt_58FDAA_ForeignOpenOrderYTD
evt_58FDAA_ForeignAmount1
evt_58FDAA_ForeignABAmount
evt_58FDAA_ForeignVouchPriorYr
evt_58FDAA_ForeignVouchYTD
evt_58FDAA_ForeignMaxOrder
evt_58FDAA_ForeignMinOrder
evt_58DESC_ErrorDescription
// Philippe CAENARO 22/12/00
// *********************************
//
VA evt_58ERR_ErrorStatus =3D "0"
//
OC Hold Payment Code (F0401Z1) =3D [IC HDPY (TextLine1)]
OC Multiple Payments (F0401Z1) =3D [IC SCK (TextLine1)]
OC Payment Instrument (F0401Z1) =3D [IC PYIN (TextLine1)]
OC Price Pick List (Y/N) (F0401Z1) =3D [IC PLST (TextLine1)]
OC Pre-Note Code (F0401Z1) =3D [IC AB1 (TextLine1)]
OC Ledger Inquiry Sequence (F0401Z1) =3D [IC SQNL (TextLine1)]
OC Billing Address Type (F0401Z1) =3D [IC BADT (TextLine1)]
OC Batch Processing Mode (F0401Z1) =3D [IC EDPM (TextLine1)]
OC Customer Type Identifier (F0401Z1) =3D [IC EDCI (TextLine1)]
OC Item Type Identifier (F0401Z1) =3D [IC EDII (TextLine1)]
OC Delivery Note (F0401Z1) =3D [IC EDF1 (TextLine1)]
OC Item Restrictions (F0401Z1) =3D [IC EDF2 (TextLine1)]
OC Supplier/Parent Rebate level (F0401Z1) =3D [IC VI01 (TextLine1)]
OC Supplier Rebate Code (F0401Z1) =3D [IC VI02 (TextLine1)]
OC Special Instruction 03 (F0401Z1) =3D [IC VI03 (TextLine1)]
OC Special Instruction 04 (F0401Z1) =3D [IC VI04 (TextLine1)]
OC Special Instruction 05 (F0401Z1) =3D [IC VI05 (TextLine1)]
OC Minimum Check Amount Code (F0401Z1) =3D [IC MNSC (TextLine1)]
OC Address Type - User Code 5 (F0401Z1) =3D [IC ATO (TextLine1)]
OC Revenue Netted (Y/N) (F0401Z1) =3D [IC RVNT (TextLine1)]
OC Send Method (F0401Z1) =3D [IC CRMD (TextLine1)]
OC Code - Evaluated Receipt Settlement (F0401Z1) =3D [IC AVCH =
(TextLine1)]
OC Price and Adjustment Schedule (F0401Z1) =3D IC ASN (TextLine1)
OC EDI - Batch Number (F0401Z1) =3D IC EDBT (TextLine1)
OC EDI - Transaction Number (F0401Z1) =3D IC EDTN (TextLine1)
OC Transaction Action (F0401Z1) =3D IC TNAC (TextLine1)
OC G/L Class (F0401Z1) =3D IC APC (TextLine1)
OC Business Unit - A/P Default (F0401Z1) =3D IC MCUP (TextLine1)
OC Object - Accounts Payable Default (F0401Z1) =3D IC OBAP (TextLine1)
OC Subsidiary - Accounts Payable Default (F0401Z1) =3D IC AIDP =
(TextLine1)
OC Document Company (A/P Model Document) (F0401Z1) =3D IC KCOP =
(TextLine1)
OC Document - A/P Default for Model JE (F0401Z1) =3D IC DCAP (TextLine1)
OC Document Type - A/P Default for Model JE (F0401Z1) =3D IC DTAP =
(TextLine1)
OC Currency Code - Accounts Payable (F0401Z1) =3D IC CRRP (TextLine1)
OC Tax Rate/Area 2 (F0401Z1) =3D IC TXA2 (TextLine1)
OC Tax Expl Code 2 (F0401Z1) =3D IC EXR2 (TextLine1)
OC Tax Rate/Area 3 - Withholding (F0401Z1) =3D IC TXA3 (TextLine1)
OC Tax Expl Code 3 - Withholding (F0401Z1) =3D IC EXR3 (TextLine1)
OC Tax Authority for A/P Withholding (F0401Z1) =3D IC TAWH (TextLine1)
OC Withholding Percent (F0401Z1) =3D IC PCWH (TextLine1)
OC Payment Terms - A/P (F0401Z1) =3D IC TRAP (TextLine1)
OC Address Number - Approver (F0401Z1) =3D IC SNTO (TextLine1)
OC Float Days for Payments (F0401Z1) =3D IC FLD (TextLine1)
OC Currency Code - A/B Amounts (F0401Z1) =3D IC CRCA (TextLine1)
OC Amount - Vouchered YTD (F0401Z1) =3D IC AYPD (TextLine1)
OC Amount - Vouchered PYE (F0401Z1) =3D IC APPD (TextLine1)
OC Amount - Address Book (F0401Z1) =3D IC ABAM (TextLine1)
OC Amount - Address Book (F0401Z1) =3D IC ABA1 (TextLine1)
OC Amount - Open Order (F0401Z1) =3D IC APRC (TextLine1)
OC Minimum Order Value (F0401Z1) =3D IC MINO (TextLine1)
OC Maximum Order Value (F0401Z1) =3D IC MAXO (TextLine1)
OC Related - Address Number (F0401Z1) =3D IC AN8R (TextLine1)
OC Customer Price Group (F0401Z1) =3D IC CPGP (TextLine1)
OC Order Template (F0401Z1) =3D IC ORTP (TextLine1)
OC Print Message (F0401Z1) =3D IC INMG (TextLine1)
OC Hold Orders Code (F0401Z1) =3D IC HOLD (TextLine1)
OC Route Code (F0401Z1) =3D IC ROUT (TextLine1)
OC Stop Code (F0401Z1) =3D IC STOP (TextLine1)
OC Zone Number (F0401Z1) =3D IC ZON (TextLine1)
OC Carrier Number (F0401Z1) =3D IC ANCR (TextLine1)
OC Carrier Number (F0401Z1) =3D IC CARS (TextLine1)
OC Delivery Instructions Line 1 (F0401Z1) =3D IC DEL1 (TextLine1)
OC Delivery Instructions Line 2 (F0401Z1) =3D IC DEL2 (TextLine1)
OC Transit Days (F0401Z1) =3D IC LTDT (TextLine1)
OC Freight Handling Code (F0401Z1) =3D IC FRTH (TextLine1)
OC Invoice Copies (F0401Z1) =3D IC INVC (TextLine1)
OC Unit of Measure - Weight Display (F0401Z1) =3D IC WUMD (TextLine1)
OC Unit of Measure - Volume Display (F0401Z1) =3D IC VUMD (TextLine1)
OC Purchasing Category Code 5 (F0401Z1) =3D IC PRP5 (TextLine1)
OC Quantity Decimals (F0401Z1) =3D IC EDQD (TextLine1)
OC Amount Decimals (F0401Z1) =3D IC EDAD (TextLine1)
//
// Retrieve the AN8 from the X-ref
// **********************************
F580101.Fetch Single
If SV File_IO_Status is not equal to CO SUCCESS
VA evt_58ERR_ErrorStatus =3D "1"
VA evt_58ERNO_ErrorNumber =3D "00"
VA evt_58DESC_ErrorDescription =3D "Address X-ref not found"
F5801ERR.Insert
Else
// Check that the Supplier does not exist (for Action Code =3D 'A')
// *****************************************************************
If IC TNAC (TextLine1) is equal to "A"
F0401.Fetch Single
If SV File_IO_Status is equal to CO SUCCESS
VA evt_58ERR_ErrorStatus =3D "1"
VA evt_58ERNO_ErrorNumber =3D "6A"
VA evt_58DESC_ErrorDescription =3D "Supplier Already exists"
F5801ERR.Insert
End If
End If
End If
//
// Retrieve the Approver Number (SNTO) from the AB X-REF
// ************************************************************
If OC Address Number - Approver (F0401Z1) is not equal to <Blank>
F580101.Fetch Single
If SV File_IO_Status is not equal to CO SUCCESS
VA evt_58ERR_ErrorStatus =3D "1"
VA evt_58ERNO_ErrorNumber =3D "06"
VA evt_58DESC_ErrorDescription =3D "SNTO Address X-ref not found"
F5801ERR.Insert
End If
End If
//
// Retrieve the Carrier Number (CARS) from the AB X-REF
// ************************************************************
If OC Carrier Number (F0401Z1) is not equal to <Blank>
F580101.Fetch Single
If SV File_IO_Status is not equal to CO SUCCESS
VA evt_58ERR_ErrorStatus =3D "1"
VA evt_58ERNO_ErrorNumber =3D "07"
VA evt_58DESC_ErrorDescription =3D "CARS Address X-ref not found"
F5801ERR.Insert
End If
End If
//
//
// Check the Company number against the Processing Option
// *****************************************************************
If IC CPY (TextLine1) is not equal to RI szCompany
VA evt_58ERR_ErrorStatus =3D "1"
VA evt_58ERNO_ErrorNumber =3D "01"
VA evt_58DESC_ErrorDescription =3D "Cpy# does not match Processing =
Option"
F5801ERR.Insert
End If
//
// Check the Batch Number against the Processing Option
// *************************************************************
If IC EDBT (TextLine1) is not equal to RI szEdiBatchNumber
VA evt_58ERR_ErrorStatus =3D "1"
VA evt_58ERNO_ErrorNumber =3D "02"
VA evt_58DESC_ErrorDescription =3D "Batch# does not match Processing =
Option"
F5801ERR.Insert
End If
//
// Convert Currency Amounts to EURO
// Amounts fields: Vouchered YTD, Vouchered Prior Year, Amount Address =
Book,
// Amount1, Open Orders, Min Order Value, Max Order Value
// =
*************************************************************************=
****
//
VA evt_58CRDC_CurrencyCodeTo =3D RI szCurrencyCodeFrom
VA evt_58CRCD_CurrencyCodeFrom =3D OC Currency Code - A/B Amounts =
(F0401Z1)
If VA evt_58CRCD_CurrencyCodeFrom is not equal to VA =
evt_58CRDC_CurrencyCodeTo
And VA evt_58CRDC_CurrencyCodeTo is not equal to <Blank>
F0015 Get Exchange Rate
If SV Error_Status is equal to CO SUCCESS
Currency Conversion, Calculate
Currency Conversion, Calculate
Currency Conversion, Calculate
Currency Conversion, Calculate
Currency Conversion, Calculate
Currency Conversion, Calculate
Currency Conversion, Calculate
Else
VA evt_58ERR_ErrorStatus =3D "1"
VA evt_58ERNO_ErrorNumber =3D "05"
VA evt_58DESC_ErrorDescription =3D "Exchange Rate not found"
F5801ERR.Insert
End If
Else
VA evt_58FDAA_ForeignOpenOrderYTD =3D OC Amount - Open Order =
(F0401Z1)
VA evt_58FDAA_ForeignAmount1 =3D OC Amount - Address Book (F0401Z1)
VA evt_58FDAA_ForeignABAmount =3D OC Amount - Address Book (F0401Z1)
VA evt_58FDAA_ForeignVouchPriorYr =3D OC Amount - Vouchered PYE =
(F0401Z1)
VA evt_58FDAA_ForeignVouchYTD =3D OC Amount - Vouchered YTD (F0401Z1)
VA evt_58FDAA_ForeignMaxOrder =3D OC Maximum Order Value (F0401Z1)
VA evt_58FDAA_ForeignMinOrder =3D OC Minimum Order Value (F0401Z1)
VA evt_58CRDC_CurrencyCodeTo =3D OC Currency Code - A/B Amounts =
(F0401Z1)
End If
//
// If no error and Mode is 'Final'
// ********************************
If VA evt_58ERR_ErrorStatus is equal to <Zero>
And RI cProofOrFinalMode is equal to "1"
F0401Z1.Insert
End If

------=_NextPart_000_003A_01C09663.30E64560--



One World B733 Oracle 8.1 RS6000
 
Back
Top