Some Background info. We are using Infor XA as our ERP and I am trying to write an RPGLE program as a user action to save Purchase order information to an XML on the IFS. This will then get picked up by the forms package. There is a table with the PO header information, and one with the lines.
The issue is the code will not return any results when the program runs, even though there is proper Order number in the where clause.
If I take that statement out of the program, and run it directly, it returns all the header information. When I step through it and debug it, none of the variables are filled. I declared all the variable types based on the database fields.
exec sql
select p.ordno, // Order Number
p.revnb, // Revesion Number
p.revdt, // Revision Date
p.actdt, // PO Completion Date
s.viads, // Ship Via Description
t.trmds, // Terms Description
p.reprt, // Reprint code
p.UUSAPM, // User switch field to send PO as email.
b.buynm, // Buyer Name
b.buyph, // Buyer Phone
b.eadr, // Buyer Email
v.vn35, // Vendor Name
v.v135, // Vender Address 1
v.v235, // Vendor Address 2
v.vcity, // Vendor City
v.vstac, // Vendor State Code
v.vzipc, // Vendor Zip
v.vcntr, // Vendor Country,
v.vetel, // Vendor Tel
v.eadr, // Vendor Email Address
d.sn35, // Ship To Name
d.s135, // Ship To Address 1
d.s235, // Ship To Address 2
d.s335, // Ship To Address 3
d.scity, // Ship To City
d.sstac, // Ship To State Code
d.shpzp, // Ship to Zip
d.scntr, // Ship to Country
d.stele, // Ship to Telephone
d.eadr, // Ship to Email Address
c.sn35, // bill To Name
c.s135, // Bill To Address 1
d.s235, // Bill To Address 2
c.s335, // Bill To Address 3
c.scity, // Bill To City
c.sstac, // Bill To State Code
c.shpzp, // Bill To Zip
c.scntr, // Bill To Country
c.stele, // Bill To Telehpone
c.eadr // Bill to Email
into :Order, // Purchase Order Number
:OrdRev, // Revision Number
:OrdRevDt, // Revision Date
:Pocmpdt, // PO Complete Date
:Shpvids, // Ship Via Descirption
:Terms, // Terms Description
:rpprt, // Reprint Code
:EmlPO, // Send PO as Email
:Buynm, // Buyer FileName
:BuyTel, // Buyer Telephone
:Buyeadr, // Buyer Email
:VName, // Vendor Name
:Vadd1, // Vendor Address 1
:Vadd2, // Vendor Address 2
:Vadd3, // Vendor Address 3
:Vcity, // Vendor City
:Vstac, // Vendor State Code
:Vzipc, // Vendor Zip
:Vcntr, // Vendor Country
:Vtel, // Vendor Telphone
:Veadr, // Vendor Email
:Shptnm, // Ship To Name
:Shptst1, // Ship to Address 1
:Shptst2, // Ship to Address 2
:Shptst3, // Ship to Address 3
:Shptcty, // Ship to City
:Shptstc, // Ship to State
:ShptZip, // Ship to Zip
:Shptcnt, // Ship to Country
:cnteml, // Contact Email
:Bname, // Bill To FileName
:BAdd1, // Bill to Address 1
:Badd2, // Bill to Address 2
:Badd3, // Bill to Address 3
:Bcity, // Bill to City
:Bstc, // Bill to State Code
:Bzip, // Bill to zip
:Bcntr, // Bill to Country
:BTel, // Bill to Telephone
:Beadr // Bill to Email
from amflibn.pomast as p
join amflibn.vennam as v
on p.vndnr = v.vndnr
join amflibn.buyerf as b
on p.buyno = b.buyno
left outer join amflibn.termsf as t
on p.trmcd = t.trmcd
left outer join amflibn.shipvf as s
on p.viacd = s.viacd
join amflibn.shpmst as d
on p.shpid = d.shpid
and p.house = d.house
join amflibn.shpmst as c
on p.bilid = c.shpid
and p.house = c.house
where p.ORDNO = 'P974625';
XMLdata = '<?xml version="1.0" encoding="UTF-8"?>'
+ '<PurchaseOrder>'
+ '<Header>'
+ '<OrderNumber>' + %trim(Order) +'</OrderNumber>'
+ '<OrderRev>' + %char(OrdRev) +'</OrderRev>'
+ '<OrderRevDate>' + %char(OrdRevDt) + '</OrderRevDate>'
+ '<ShipVia>' + %trim(Shpvids) + '</ShipVia>'
+ '<Terms>' + %trim(Terms) + '</Terms>'
+ '<ReprintCode>' + %trim(rpprt) + '</ReprintCode>'
+ '<EmailPO>' + %trim(EmlPO) + '</EmailPO>'
+ '<BuyerName>' +%trim(Buynm) + '</BuyerName>'
+ '<BuyerTel>' + %trim(BuyTel) + '</BuyerTel>'
+ '<BuyerEadr>' + %trim(Buyeadr) + '</BuyerEadr>'
+ '<VendorName>' + %trim(VName) + '</VendorName>'
+ '<VendorAddress1>' + %trim(Vadd1) + '</VendorAddress1>'
+ '<VendorAddress2>' + %trim(Vadd2) + '</VendorAddress2>'
+ '<VendorAddress3>' + %trim(Vadd3) + '</VendorAddress3>'
+ '<VendorCity>' + %trim(Bcity) + '</VendorCity>'
+ '<VendorState>' + %trim(Bstc) + '</VendorState>'
+ '<VendorZip>' + %trim(Bzip) + '</VendorZip>'
+ '<VendorCountry>' + %trim(Vcntr) + '</VendorCountry>'
+ '<VendorTel>' + %trim(Vtel) + '</VendorTel>'
+ '<VendorEmail>' + %trim(Veadr) +'</VendorEmail>'
+ '<ShipToName>' + %trim(Shptnm) + '</ShipToName>'
+ '<ShipToStreet1>' + %trim(BAdd1) + '</ShipToStreet1>'
+ '<ShipToStreet2>' + %trim(BAdd2) + '</ShipToStreet2>'
+ '<ShipToStreet3>' + %trim(BAdd3) + '</ShipToStreet3>'
+ '<ShipToCity>' + %trim(Shptcty) + '</ShipToCity>'
+ '<ShipToState>' + %trim(Shptstc) + '</ShipToState>'
+ '<ShipToZip>' + %trim(Bzip) + '</ShipToZip>'
+ '<ShipToCountry>' + %trim(Shptcnt) + '</ShipToCountry>'
+ '<ContactName>' + %trim(Shptcnt) + '<ContactName>'
+ '<ContactEmail>' + %trim(cnteml) + '<ContactEmail>'
+ '<BillToName>' + %trim(Bname) + '</BillToName>'
+ '<BillToStreet1>' + %trim(Shptst1) + '</BillToStreet1>'
+ '<BillToStreet2>' + %trim(Shptst2) + '</BillToStreet2>'
+ '<BillToStreet3>' + %trim(Shptst3) + '</BillToStreet3>'
+ '<BillToCity>' + %trim(Shptcty) + '</BillToCity>'
+ '<BillToState>' + %trim(Shptstc) + '</BillToState>'
+ '<BillToZip>' + %trim(ShptZip) + '</BillToZip>'
+ '<BillToCountry>' + %trim(Bcntr) + '</BillToCountry>'
+ '<ContactName>' + %trim(BTel) + '<ContactName>'
+ '<ContactEmail>' + %trim(Beadr) + '<ContactEmail>'
+ '</Header>'
+ '<Detail>';
I am not sure how to to do any further debugging. Any help would be great. Still new to the RGPLE language. If you know of a better way of getting this info from the DB, and in an XML file, I am all ears.
So after a lot of digging around. The issue was that I had an outer join which will cause null values in the returned results. RPGLE can't deal with null fields well and requires extra care. But if you wrap the columns that may have null values are returned in a coalesce, it solve the issue.
Also make sure that you are looking at the SQL error codes.