有谁知道如何将 CREATE VIEW SQL 语句推送到 Acumatica 定制项目中?
我知道如何在 SQL Management Studio 中执行此操作,但通过自定义项目执行此操作对于 Acumatica SaaS 客户非常有用。
您应该执行以下步骤:
-
在 Management Studio 中创建 SQL 视图(出于演示目的,让我坚持使用简单的 PositivePay 视图):
CREATE VIEW [dbo].[PositivePay] AS
SELECT
APPayment.RefNbr,
APPayment.ExtRefNbr,
APRegister.DocDate,
APRegister.OrigDocAmt,
CashAccount.ExtRefNbr BankAccountID
FROM APPayment
JOIN APRegister
ON APRegister.CompanyID = APPayment.CompanyID
AND APRegister.RefNbr = APPayment.RefNbr
JOIN CashAccount
ON APPayment.CashAccountID = CashAccount.AccountID
AND APPayment.CompanyID = CashAccount.CompanyID
WHERE APPayment.CompanyID = 2 AND APPayment.DocType= 'CHK'
GO
Open your customization project and click on Code, then generate new DAC based on your SQL view as shown in the screenshot below:
-
为新 DAC 定义关键字段并保存更改。对于 PositivePay 我们设置IsKey to true对于 RefNbr 字段顶部的 PXDBString 属性:
[PXDBString(15, IsUnicode = true, InputMask = "", IsKey = true)]
[PXUIField(DisplayName = "Ref Nbr")]
public string RefNbr { get; set; }
-
点击数据库脚本,在中选择您的 SQL 视图名称数据库对象名称并将 SQL 脚本放入自定义脚本控制遵循以下模式:
IF EXISTS
(
SELECT * FROM sys.views
WHERE name = 'PositivePay' AND schema_id = SCHEMA_ID('dbo')
)
DROP VIEW [dbo].[PositivePay]
GO
CREATE VIEW [dbo].[PositivePay] AS
SELECT
APPayment.RefNbr,
APPayment.ExtRefNbr,
APRegister.DocDate,
APRegister.OrigDocAmt,
CashAccount.ExtRefNbr BankAccountID
FROM APPayment
JOIN APRegister
ON APRegister.CompanyID = APPayment.CompanyID
AND APRegister.RefNbr = APPayment.RefNbr
JOIN CashAccount
ON APPayment.CashAccountID = CashAccount.AccountID
AND APPayment.CompanyID = CashAccount.CompanyID
WHERE APPayment.CompanyID = 2 AND APPayment.DocType= 'CHK'
GO
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)