Marvin Grill wrote:
>=20
> When I select the Charges button under Batch it populates the =
CallHistory table but the Charges table nothing goes in. When checking =
with SQL Trace it appears the INSERT INTO Charges transactions are =
getting droped because of a foreign key constraint on the ItemID column. =
Is that normal? What I'm trying to do is test the billing of the usage =
charges as the Renewals just have the subscription charges showing up on =
the invoice.
What does your Charges table look like? ItemID isn't relevant to the =
Charges until you do invoicing/Renewal (when it actually associates a =
charge to an Invoice).=20
The following is a script created from the Emerald database with EM...
/****** Object: Table dbo.Charges Script Date: 2/24/97 7:13:06 PM =
******/
CREATE TABLE dbo.Charges (
ChargeID int IDENTITY (1, 1) NOT NULL ,
CustomerID int NOT NULL ,
Description varchar (50) NULL ,
Amount money NOT NULL ,
ItemID int NULL CONSTRAINT DF__Charges__ItemID__59704E9B DEFAULT =
(null),
LastModifyDate datetime NOT NULL CONSTRAINT =
DF__Charges__LastMod__5B58970D DEFAULT (getdate()),
LastModifyUser varchar (15) NOT NULL CONSTRAINT =
DF__Charges__LastMod__5C4CBB46 DEFAULT (suser_name(null)),
CONSTRAINT pk_ChargeID PRIMARY KEY CLUSTERED=20
(
ChargeID
),
CONSTRAINT FK__Charges__Custome__587C2A62 FOREIGN KEY=20
(
CustomerID
) REFERENCES dbo.MasterAccounts (
CustomerID
),
CONSTRAINT FK__Charges__ItemID__5A6472D4 FOREIGN KEY=20
(
ItemID
) REFERENCES dbo.InvoiceItems (
ItemID
)
)
GO
_________________________________________________________
I bolded the constraint in the above script. When I disabled this =
constraint the charges table was allowed to become populated when I ran =
the Charges from Emerald.
What was the SQL Statement you trapped?
=20
I included the trace above as a file instead of pasting it in here as =
the line widths are quite long. If you can't read this (text format), =
please let me know.
In the file it shows the INSERT into Charges statement. When I manually =
ran that statement from ISQL/W with the constraint on I got an error =
message about the constraint. Removing it allowed the Charges Button to =
work.
Also, you will notice a statement following that one that has the first =
part of it missing. It is as follows:
From Calls c, MasterAccounts ma, SubAccounts sa Where ma.CustomerID =3D =
sa.CustomerID And sa.Login =3D c.UserName And DateDiff(Month, =
CallDate, maExpireDate) > 0
I am assuming this is the statement that consolidates the calls as =
reported by the dialog box in Emerald after the Charges are run. But =
when I run Charges a second time it duplicates the records in the =
CallHistory table. I am guessing that this is temporarily disabled while =
in beta mode.
Regards,
Marvin Grill
------ =_NextPart_000_01BC2826.B18B5CA0
Content-Type: application/octet-stream; name="All.sql"
Content-Transfer-Encoding: base64
LS0gMy8zLzk3IDIyOjQwOjQ4LjAyNiBBY3RpdmUgY29ubmVjdGlvbnMgKElEPTQ1LCBTUElEPTE3
LCBVc2VyPXNhKEdSSUxMLlNLLkNBXEFETUlOSVNUUkFUT1IpLCBBcHA9J1NRTCBUcmFjZSBGaWx0
ZXInLCBIb3N0PSdNQVJWJyhmZmM0NzE1YikgKQ0KLS0gMy8zLzk3IDIyOjM5OjQzLjAxMCBBY3Rp
dmUgY29ubmVjdGlvbnMgKElEPTQ0LCBTUElEPTE0LCBVc2VyPXNhKEdSSUxMLlNLLkNBXEFETUlO
SVNUUkFUT1IpLCBBcHA9J1NRTCBUcmFjZScsIEhvc3Q9JycoZmZjNDcxNWIpICkNCi0tIDMvMS85
NyAxNTowMjoxOC43MDMgQWN0aXZlIGNvbm5lY3Rpb25zIChJRD03LCBTUElEPTExLCBVc2VyPXNh
KEdSSUxMLlNLLkNBXEFkbWluaXN0cmF0b3IpLCBBcHA9J1NRTEVYRUMgLSBUYXNrIFJlZnJlc2hl
cicsIEhvc3Q9J01FUkNVUlknKDNjKSApDQotLSAzLzMvOTcgMjI6NDE6MjAuNDQwIFNRTCAoSUQ9
NywgU1BJRD0xMSwgVXNlcj1zYShHUklMTC5TSy5DQVxBZG1pbmlzdHJhdG9yKSwgQXBwPSdTUUxF
WEVDIC0gVGFzayBSZWZyZXNoZXInLCBIb3N0PSdNRVJDVVJZJygzYykgKQ0KU2VsZWN0IEFjY291
bnRJRCwgRGF0ZURpZmYoTW9udGgsIENhbGxEYXRlLCBtYUV4cGlyZURhdGUpLCBBY2N0U2Vzc2lv
blRpbWUvNjArMSwgICAgIFN0YXJ0PURhdGVBZGQoU2Vjb25kLCAoMC1BY2N0U2Vzc2lvblRpbWUt
QWNjdERlbGF5VGltZSksIENhbGxEYXRlKSBGcm9tIENhbGxzIGMsIE1hc3RlckFjY291bnRzIG1h
LCBTdWJBY2NvdW50cyBzYSBXaGVyZSBtYS5DdXN0b21lcklEID0gc2EuQ3VzdG9tZXJJRCAgICBB
bmQgc2EuTG9naW4gPSBjLlVzZXJOYW1lICAgIEFuZCBBY2N0U3RhdHVzVHlwZSA9IDIgICAgQW5k
IERhdGVEaWZmKE1vbnRoLCBDYWxsRGF0ZSwgbWFFeHBpcmVEYXRlKSA+IDAgT3JkZXIgQnkgQWNj
b3VudElELCBEYXRlRGlmZihNb250aCwgQ2FsbERhdGUsIG1hRXhwaXJlRGF0ZSkgREVTQw0KZ28N
Ci0tIDMvMy85NyAyMjo0MDoxNy45NDAgQWN0aXZlIGNvbm5lY3Rpb25zIChJRD00NywgU1BJRD0x
NiwgVXNlcj1zYShHUklMTC5TSy5DQVxBRE1JTklTVFJBVE9SKSwgQXBwPSdFbWVyYWxkJywgSG9z
dD0nJyhmZmM0YWJkYikgKQ0KLS0gVGhlIHN0YXJ0IHRpbWUgb2YgdGhpcyBldmVudCBpcyB1bmtu
b3duLiAgU1FMIChJRD00NywgU1BJRD0xNiwgVXNlcj1zYShHUklMTC5TSy5DQVxBRE1JTklTVFJB
VE9SKSwgQXBwPSdFbWVyYWxkJywgSG9zdD0nJyhmZmM0YWJkYikgKQ0KQkVHSU4gVFJBTlNBQ1RJ
T04gQ2FsbEhpc3RvcnkNCmdvDQotLSAzLzMvOTcgMjI6NDE6NDYuNDMwIFNRTCAoSUQ9NDcsIFNQ
SUQ9MTYsIFVzZXI9c2EoR1JJTEwuU0suQ0FcQURNSU5JU1RSQVRPUiksIEFwcD0nRW1lcmFsZCcs
IEhvc3Q9JycoZmZjNGFiZGIpICkNClNlbGVjdCBzYS5DdXN0b21lcklELCBtYUV4cGlyZURhdGUs
IFN0YW5kYXJkSG91cnMsIFN0YW5kYXJkQ2hhcmdlLCBPdmVyQ2hhcmdlLCBMb2dpbiBGcm9tIE1h
c3RlckFjY291bnRzIG1hLCBTdWJBY2NvdW50cyBzYSwgQWNjb3VudFR5cGVzIGF0LCBSYXRlcyBy
IFdoZXJlIG1hLkN1c3RvbWVySUQ9c2EuQ3VzdG9tZXJJRCBBTkQgc2EuQWNjb3VudFR5cGU9YXQu
QWNjb3VudFR5cGUgQW5kIGF0LlJhdGVJRCA9IHIuUmF0ZUlEIGFuZCBzYS5BY2NvdW50SUQ9MTAN
CmdvDQotLSAzLzMvOTcgMjI6NDE6NDYuOTEwIFNRTCAoSUQ9NDcsIFNQSUQ9MTYsIFVzZXI9c2Eo
R1JJTEwuU0suQ0FcQURNSU5JU1RSQVRPUiksIEFwcD0nRW1lcmFsZCcsIEhvc3Q9JycoZmZjNGFi
ZGIpICkNCklOU0VSVCBJTlRPIENoYXJnZXMoQ3VzdG9tZXJJRCwgRGVzY3JpcHRpb24sIEFtb3Vu
dCwgSXRlbUlEKVZBTFVFUygxMywgJ3JjbGVhcndhIEZlYiA5NyB1c2FnZSBvZiAxMiBIb3Vycycs
IDE2LjUsMCApDQpnbw0KLS0gMy8zLzk3IDIyOjQxOjQ3LjY1MCBTUUwgKElEPTQ3LCBTUElEPTE2
LCBVc2VyPXNhKEdSSUxMLlNLLkNBXEFETUlOSVNUUkFUT1IpLCBBcHA9J0VtZXJhbGQnLCBIb3N0
PScnKGZmYzRhYmRiKSApDQpTZWxlY3QgTGFzdElkZW50aXR5PUBASURFTlRJVFkNCmdvDQotLSAz
LzMvOTcgMjI6NDE6NDguMTUwIFNRTCAoSUQ9NDcsIFNQSUQ9MTYsIFVzZXI9c2EoR1JJTEwuU0su
Q0FcQURNSU5JU1RSQVRPUiksIEFwcD0nRW1lcmFsZCcsIEhvc3Q9JycoZmZjNGFiZGIpICkNCklO
U0VSVCBJTlRPIENhbGxIaXN0b3J5KEFjY291bnRJRCwgU3RhcnREYXRlLCBNb250aHMsIE1pbnMs
IENhbGxzLCBDaGFyZ2VJRCkgVkFMVUVTKDEwICwnMS8yMi85NycsIDEsIDc2NywgMzUsIDM0MikN
CmdvDQotLSAzLzMvOTcgMjI6NDE6NDguNjIwIFNRTCAoSUQ9NDYsIFNQSUQ9MTUsIFVzZXI9c2Eo
R1JJTEwuU0suQ0FcQURNSU5JU1RSQVRPUiksIEFwcD0nRW1lcmFsZCcsIEhvc3Q9JycoZmZjNGFi
ZGIpICkNCiBGcm9tIENhbGxzIGMsIE1hc3RlckFjY291bnRzIG1hLCBTdWJBY2NvdW50cyBzYSBX
aGVyZSBtYS5DdXN0b21lcklEID0gc2EuQ3VzdG9tZXJJRCAgICBBbmQgc2EuTG9naW4gPSBjLlVz
ZXJOYW1lICAgIEFuZCBEYXRlRGlmZihNb250aCwgQ2FsbERhdGUsIG1hRXhwaXJlRGF0ZSkgPiAw
DQpnbw0KLS0gMy8zLzk3IDIyOjQxOjU4LjIyNiBTUUwgKElEPTQ3LCBTUElEPTE2LCBVc2VyPXNh
KEdSSUxMLlNLLkNBXEFETUlOSVNUUkFUT1IpLCBBcHA9J0VtZXJhbGQnLCBIb3N0PScnKGZmYzRh
YmRiKSApDQpDT01NSVQgVFJBTlNBQ1RJT04gQ2FsbEhpc3RvcnkNCmdvDQogDQotLSAzLzMvOTcg
MjI6NDI6MDQuNjczIEZpbHRlciBQYXVzZWQgKElEPTQ1LCBTUElEPTE3LCBVc2VyPXNhKEdSSUxM
LlNLLkNBXEFETUlOSVNUUkFUT1IpLCBBcHA9J1NRTCBUcmFjZSBGaWx0ZXInLCBIb3N0PSdNQVJW
JyhmZmM0NzE1YikgKQ0K
------ =_NextPart_000_01BC2826.B18B5CA0--