You’re offline. This is a read only version of the page.
290
I'v this regular table that I wanted to have static data from other table(s). I'm using run batch framework classes to insert this data. Below are my code/insert method. When I run this... it doesn't insert any data. Please advise on what I might be missing here in terms of coding. Thanks in advance!
Share
Report//bulk insertion into mgbShipmentVIEWTable
static void InsertMGBShipmentViewTable2(Args args)
{
//declare variables
MGBShipmentTableViewCreate mgbShipmentTableViewCreate;
SalesLine salesLine;
SalesTable salesTable;
InventTable inventTable;
MGBShipmentVIEWTable mgbShipmentVIEWTable;
CustTable custTable;
DirPartyTable dirPartyTable;
MGB_InventTableProductFamilySubFamReUse productFamilyView, productFamilyView2, productFamilyView3;
InventDim inventDim;
LogisticsPostalAddress logisticsPostalAddress;
HcmWorker hcmWorkerProjectManager;
DirPersonName dirPersonNameProjectManager;
HcmWorker hcmWorkerRegionalManager;
DirPersonName dirPersonNameRegionalManager;
SqlDictionary sqlDictionary;
CustInvoiceJour custInvoiceJour;
PurchTable purchTable;
mgbFirmMaster mgbFirmMaster;
mgbFirmContactMaster mgbFirmContactMaster, mgbContactFirmMaster2;
DimensionAttributeLevelValueView dimensionAttributeLevelValueView;
DimensionFinancialTag dimensionFinancialTag;
IntrastatItemCode intrastatItemCode;
delete_from mgbShipmentVIEWTable;
insert_recordset mgbShipmentVIEWTable
(
CustAccount,
mgbOrderCreationDate,
PURCHORDERFORMNUM,
SalesId,
ShippingDateConfirmed,
ShippingDateRequested,
SalesQty,
SalesStatus,
LineAmount,
SalesPrice,
ItemId,
mgbLineNum,
mgbProjectedShipDate,
ShipCarrierAccount,
ShipCarrierAccountCode,
DeliveryName,
mgbLegFinishId,
mgbPolyId,
mgbAcrylic,
mgbArmBoltCapId,
mgbCasterId,
mgbContrastA,
mgbContrastB,
mgbContrastCover,
mgbContrastCushion,
mgbContrastPillow,
mgbContrastWelts,
mgbFerrules,
mgbFringe,
mgbGrommetId,
mgbHinge,
mgbKickplate,
mgbNails,
mgbLegFinishId,
mgbOutsideContrast,
mgbMetalBaseFinishId,
mgbLinkedProductionOrder,
DeliveryPostalAddress,
ReqGroupId,
IntraCode,
ConfigId,
City,
State,
Zipcode,
CountryRegionId,
mgbInstallDate,
mgbProjectName,
mgbProjectManager,
mgbSalesAssociate,
mgbRetailStore,
mgbRegionalAccountManager,
mgbDesignFirmId,
mgbDesignFirmContactId,
SegmentId,
SubsegmentId,
Name,
mgbProjectManager,
mgbFirmId,
mgbFirmDescription,
mgbFirmContactId,
ProductFamily,
SubProductFamily,
InvoiceAccount,
InvoiceDate,
Qty
)
select
CustAccount,
mgbOrderCreationDate,
PURCHORDERFORMNUM,
SalesId,
ShippingDateConfirmed,
ShippingDateRequested,
SalesQty,
SalesStatus,
LineAmount,
SalesPrice,
ItemId,
mgbLineNum,
mgbProjectedShipDate,
ShipCarrierAccount,
ShipCarrierAccountCode,
DeliveryName,
mgbLegFinishId,
mgbPolyId,
mgbAcrylic,
mgbArmBoltCapId,
mgbCasterId,
mgbContrastA,
mgbContrastB,
mgbContrastCover,
mgbContrastCushion,
mgbContrastPillow,
mgbContrastWelts,
mgbFerrules,
mgbFringe,
mgbGrommetId,
mgbHinge,
mgbKickplate,
mgbNails,
mgbLegFinishId,
mgbOutsideContrast,
mgbMetalBaseFinishId,
mgbLinkedProductionOrder,
DeliveryPostalAddress
from SalesLine
join salesLine where salesLine.SalesId == salesTable.SalesId
outer join firstonly ReqGroupId from inventTable where inventTable.ItemId == salesLine.ItemId
outer join firstOnly IntraCode from inventTable where inventTable.IntraCode == intrastatItemCode.ItemCodeId
outer join firstonly ConfigId from inventDim where inventDim.inventDimId == salesLine.InventDimId
outer join firstonly City, State, ZipCode, CountryRegionId from logisticsPostaladdress where logisticsPostaladdress.RecId == salesLine.DeliveryPostalAddress
//outer join firstonly itemId from productFamilyView where productFamilyView.ItemId == salesLine.ItemId
//outer join firstOnly purchTable where purchTable.purchId == salesLine.InventRefid
join mgbInstallDate, mgbProjectName, mgbProjectManager, mgbSalesAssociate, mgbRetailStore, mgbRegionalAccountManager, mgbDesignFirmId, mgbDesignFirmContactId from salesTable where salesTable.SalesId == salesLine.SalesId
join firstonly SegmentId, SubsegmentId from custTable where custTable.AccountNum == salesTable.CustAccount
outer join firstOnly Name from dirPartyTable where dirPartyTable.RecId == custTable.Party
outer join firstOnly person from hcmWorkerProjectManager where hcmWorkerProjectManager.RecId == salesTable.mgbProjectManager
outer join firstonly dirPersonNameProjectManager where dirPersonNameProjectManager.Person == hcmWorkerProjectManager.Person
//outer join firstOnly person from hcmWorkerRegionalManager where hcmWorkerRegionalManager.RecId == salesTable.mgbProjectManager
outer join firstonly dirPersonNameRegionalManager where dirPersonNameRegionalManager.Person == hcmWorkerRegionalManager.Person
outer join firstOnly mgbFirmId, mgbFirmDescription from mgbFirmMaster where mgbFirmMaster.mgbFirmId == salesTable.mgbPurchaseFirmId
outer join firstOnly mgbFirmContactId from mgbFirmContactMaster where mgbFirmContactMaster.mgbFirmContactId == salesTable.mgbPurchaseFirmContactId
outer join firstOnly mgbFirmContactMaster where mgbFirmContactMaster.mgbFirmContactId == salesTable.mgbDesignFirmContactId
outer join firstOnly mgbContactFirmMaster2 where mgbContactFirmMaster2.mgbFirmId == salesTable.mgbDesignFirmId
join ProductFamily, SubProductFamily from productFamilyView where productFamilyView.ProductFamily == mgbShipmentVIEWTable.ProductFamily
join firstOnly productFamilyView where productFamilyView.ProductFamilyDescription == mgbShipmentVIEWTable.ProductFamilyDesc
//join firstOnly productFamilyView2 where productFamilyView2.SubProductFamily == mgbShipmentVIEWTable.SubProductFamily
join firstOnly productFamilyView3 where productFamilyView3.SubProductFamilyDescription == mgbShipmentVIEWTable.SubProductFamilyDesc
join InvoiceAccount, InvoiceDate, Qty from CustInvoiceJour where custInvoiceJour.SalesId == salesTable.SalesId;
}
*This post is locked for comments
Hi all....
Instead of doing an outer join for all. I had to change them joins to update_recordset and it inserted data into the table. Thanks for all the help.
Hi Sukrut,
Data is returning when I run it using SQL query but I've turned this SQL query into X++ code and this is where I'm having issues with data not inserting to the table in AOT. I think there might be some issues on the joins that I've in X++.
Hi,
I corrected your query by commenting the mgb* prefix objects and I am able to get the data. Please use the following query in your insert statement(by adjusting the fieldlist) and let me know if anything is missing.
All responses (
Answers (
select
CustAccount,
mgbOrderCreationDate,
PURCHORDERFORMNUM,
SalesId,
ShippingDateConfirmed,
ShippingDateRequested,
SalesQty,
SalesStatus,
LineAmount,
SalesPrice,
ItemId,
mgbLineNum,
mgbProjectedShipDate,
ShipCarrierAccount,
ShipCarrierAccountCode,
DeliveryName,
mgbLegFinishId,
mgbPolyId,
mgbAcrylic,
mgbArmBoltCapId,
mgbCasterId,
mgbContrastA,
mgbContrastB,
mgbContrastCover,
mgbContrastCushion,
mgbContrastPillow,
mgbContrastWelts,
mgbFerrules,
mgbFringe,
mgbGrommetId,
mgbHinge,
mgbKickplate,
mgbNails,
mgbLegFinishId,
mgbOutsideContrast,
mgbMetalBaseFinishId,
mgbLinkedProductionOrder,
DeliveryPostalAddress
from SalesLine
join
mgbInstallDate, mgbProjectName, mgbProjectManager, mgbSalesAssociate, mgbRetailStore, mgbRegionalAccountManager, mgbDesignFirmId, mgbDesignFirmContactId from
salesTable where salesLine.SalesId == salesTable.SalesId // Corrected here
outer join firstonly IntraCode, ReqGroupId from inventTable where inventTable.ItemId == salesLine.ItemId // Corrected here
// outer join firstOnly IntraCode from intrastatItemCode where inventTable.IntraCode == intrastatItemCode.ItemCodeId // Removed this
outer join firstonly ConfigId from inventDim where inventDim.inventDimId == salesLine.InventDimId
outer join firstonly City, State, ZipCode, CountryRegionId from logisticsPostaladdress where logisticsPostaladdress.RecId == salesLine.DeliveryPostalAddress
//outer join firstonly itemId, ProductFamily, SubProductFamily from productFamilyView where productFamilyView.ItemId == salesLine.ItemId
outer join firstOnly purchTable where purchTable.purchId == salesLine.InventRefid
//join mgbInstallDate, mgbProjectName, mgbProjectManager, mgbSalesAssociate, mgbRetailStore, mgbRegionalAccountManager, mgbDesignFirmId, mgbDesignFirmContactId from salesTable where salesTable.SalesId == salesLine.SalesId // Removed this
join firstonly SegmentId, SubsegmentId from custTable where custTable.AccountNum == salesTable.CustAccount
outer join firstOnly Name from dirPartyTable where dirPartyTable.RecId == custTable.Party
outer join firstOnly person from hcmWorkerProjectManager where hcmWorkerProjectManager.RecId == salesTable.mgbProjectManager
outer join firstonly dirPersonNameProjectManager where dirPersonNameProjectManager.Person == hcmWorkerProjectManager.Person
outer join firstOnly person from hcmWorkerRegionalManager where hcmWorkerRegionalManager.RecId == salesTable.mgbProjectManager
outer join firstonly dirPersonNameRegionalManager where dirPersonNameRegionalManager.Person == hcmWorkerRegionalManager.Person
outer join firstOnly mgbFirmId, mgbFirmDescription from mgbFirmMaster where mgbFirmMaster.mgbFirmId == salesTable.mgbPurchaseFirmId
outer join firstOnly mgbFirmContactId from mgbFirmContactMaster where mgbFirmContactMaster.mgbFirmContactId == salesTable.mgbPurchaseFirmContactId
outer join firstOnly mgbFirmContactMaster where mgbFirmContactMaster.mgbFirmContactId == salesTable.mgbDesignFirmContactId
outer join firstOnly mgbContactFirmMaster2 where mgbContactFirmMaster2.mgbFirmId == salesTable.mgbDesignFirmId
join mgbShipmentVIEWTable where productFamilyView.ProductFamily == mgbShipmentVIEWTable.ProductFamily // Corrected here
&& productFamilyView.ProductFamilyDescription == mgbShipmentVIEWTable.ProductFamilyDesc
// join firstOnly productFamilyView where productFamilyView.ProductFamilyDescription == mgbShipmentVIEWTable.ProductFamilyDesc // Removed this
join firstOnly productFamilyView2 where productFamilyView2.SubProductFamily == mgbShipmentVIEWTable.SubProductFamily
join firstOnly productFamilyView3 where productFamilyView3.SubProductFamilyDescription == mgbShipmentVIEWTable.SubProductFamilyDesc
join InvoiceAccount, InvoiceDate, Qty from CustInvoiceJour where custInvoiceJour.SalesId == salesTable.SalesId;
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156