Daily SSRS subscription failure alerts

Daily SSRS subscription failure alerts

SELECT  c.name  AS Category,
                a.name  AS JobName,
Report = (SELECT cat.name AS Report
FROM   reportserver..[catalog] cat WITH (nolock)
JOIN reportserver..subscriptions sub WITH (nolock)
ON cat.itemid = sub.report_oid
JOIN reportserver..reportschedule rs WITH (nolock)
ON cat.itemid = rs.reportid
AND rs.subscriptionid = sub.subscriptionid
WHERE  Ltrim(Rtrim(rs.scheduleid)) = Ltrim(Rtrim(a.name))),
Count(step_name) AS StepName,
Sum(CASE
WHEN run_status = 1 THEN 0
ELSE 1
END)         AS FailedTotal
FROM   msdb.dbo.sysjobs_view a WITH (nolock)
INNER JOIN msdb.dbo.sysjobhistory b WITH (nolock)
ON ( a.job_id = b.job_id
AND a.enabled = 1 )
INNER JOIN msdb.dbo.syscategories c WITH (nolock)
ON ( a.category_id = c.category_id )
WHERE  CONVERT(DATETIME, Cast(run_date AS CHAR(8)), 102) = Cast(
CONVERT(CHAR(12), Getdate()  1, 102) AS DATETIME)
GROUP  BY c.name,
a.name
HAVING Sum(CASE
WHEN run_status = 1 THEN 0
ELSE 1
END) > 0

Cheers!

Sanjay

 

How to order the top horizontal dynamic menus based on product levels.

How to order the top horizontal / Vertical dynamic menus.

  1. Log on to e Catalog and connect to publisher if replication is enabled.

    esource

    esource

  2. Open the e Catalog by selecting appropriate ibe file.

    eSource-1

    eSource-1

  3. Enter to edit mode.

    Edit mode

    Edit mode

  4. Select any item row and a product level  1 column, click the ellipses button to open the levels tree.

    elipses

    click the elipses

  5. Category level window opens up

    Levels

    Levels

  6. Ordering Level 1 is achieved by controlling the number of spaces before the category description.
  7. For ordering level 2, adopt the same procedure above, except click the ellipses button at the product level 2 column.

    Level 2

    Level 2

  8. The more spaces the item will be last member of the sort order.

 

Cheers!

Sanjay

GP Constants – General Ledger

GP Constants - General Ledger

FieldConstantValue
ACCTTYPE1Posting Account
ACCTTYPE2Unit Account
ACCTTYPE3Posting Allocation Account
ACCTTYPE4Unit Allocation Account
PSTNGTYP0In Active
PSTNGTYP1Active
TPCLBLNC0Debit
TPCLBLNC1Credit
FXDORVAR1Fixed allocation
FXDORVAR2Variable Allocation
TRXTYPE0Standard
TRXTYPE1Reversing
SERIES1All
SERIES2Financial
SERIES3Sales
SERIES4Purchasing
SERIES5Inventory
SERIES6Payroll - US
SERIES7Project
SERIES103rd Party
DTA_Series2Financial
DTA_Series3Sales
DTA_Series4Purchasing
DTA_Series10Invoicing
DTA_Series11Sales Order Processing
DTA_Series12Purchase Order Processing
BACHFREQ1Single Use
BACHFREQ2Weekly
BACHFREQ3Biweekly
BACHFREQ4Semi monthly
BACHFREQ5Monthly
BACHFREQ6Bi Monthly
BACHFREQ7Quartely
BACHFREQ8Miscellaneous
PSTGSTUS1Work
PSTGSTUS2Open
PSTGSTUS3History
PSTGSTUS4Not in GL
ACCTENTR0Account entry no allowed
ACCTENTR1Account entry allowed

GP Constants

GP Constants - Sales Order Processing

FieldConstantValue
SOPTYPE1Quote
SOPTYPE2Order
SOPTYPE3Invoice
SOPTYPE4Return
SOPTYPE5Back Order
SOPTYPE6Fulfillment Order
PYMTTYPE1Cash Deposit
PYMTTYPE2Check Deposit
PYMTTYPE3Credit Card Deposit
PYMTTYPE4Cash Payment
PYMTTYPE5Check Payment
PYMTTYPE6Credit Card Payment
DISTTYPE1SALES
DISTTYPE2RECV
DISTTYPE3CASH
DISTTYPE4TAKEN
DISTTYPE5AVAIL
DISTTYPE6TRADE
DISTTYPE7FREIGHT
DISTTYPE8MISC
DISTTYPE9TAXES
DISTTYPE10MARK
DISTTYPE11COMMEXP
DISTTYPE12COMMPAY
DISTTYPE13OTHER
DISTTYPE14COGS
DISTTYPE15INV
DISTTYPE16RETURN
DISTTYPE17IN USE
DISTTYPE18IN SERVICE
DISTTYPE19DAMAGED
DISTTYPE20UNIT
DISTTYPE21DEPOSITS
DISTTYPE22ROUND
DISTTYPE23REBATE
PURCHSTAT1None
PURCHSTAT2Needs Purchase
PURCHSTAT3Purchased
PURCHSTAT4Partially Received
PURCHSTAT5Fully received
QTYTYPE1On Hand
QTYTYPE2Returned
QTYTYPE3In Use
QTYTYPE4In Service
QTYTYPE5Damaged
PROSPECT0Existing Customer
PROSPECT1Prospect
COMAPPTO0Sales
COMAPPTO1Invoice Total
VOIDSTTS0Not Voided
VOIDSTTS1Voided
DROPSHIP0Not Drop Ship
DROPSHIP1Drop Ship
NONINVEN0Inventoried item
NONINVEN1Not Inventoried
SOPSTATUS1New
SOPSTATUS2Ready to Print Pick Ticket
SOPSTATUS3Unconfirmed Pick
SOPSTATUS4Ready to Print Packing Slip
SOPSTATUS5Unfonfirmed Pack
SOPSTATUS6Shipped
SOPSTATUS7Ready to Post
SOPSTATUS8In Process
SOPSTATUS9Complete
DELETE10Not deleted
DELETE11Hold removed
PSTGSTUS0Not Posted
PSTGSTUS2Posted
PSTGSTUS3Error
PSTGSTUS508Transferring to History

Another user is rolling up costs. Try again later.

Another user is rolling up costs. Try again later.

When user attempts to perform the standard cost roll up from Dynamics GP Manufacturing module, the alert message appears and users are not allowed to conduct the roll up.

Another user is rolling up costs

Another user is rolling up costs

The resolution would be to identify the user who is holding the lock on this process to release the lock by closing the standard cost roll up window, but who is the user ?  The following script should list the user using the standard cost roll down in GP

SELECT userid
FROM   pdt.dbo.iciv0400 WITH (nolock)

Cheers!

Sanjay

 

 

 

 

 

 

 

 

 

GP Tables

Manufacturing

TABLEDESCRIPTIONGROUPRESOURCE ID
BM010115Bill Of Material Line FileBOM22007
BM010300Bill Of Materials Log FileBOM22012
BM010200BOM <--> Routing LinkBOM22015
BM010415BOM RevisionBOM22214
BMS10000BOM SecurityBOM22660
BC010000Buyer CodesInventory22065
CT40401IC COST Item Class SetupInventory22912
CT00102IC COST Item MasterInventory22913
CT00001IC COST Standard Company DefaultsInventory22917
CT00002IC COST Standard Item Class DefaultsInventory22915
CT00003IC COST Standard Item DefaultsInventory22916
IVR10300Inventory Item ABC SettingsInventory22614
IVR10015Item Engineering FileInventory22004
IVR40400Manufacturing Item Class SetupInventory22803
ICGL0102Manufacturing Series GL LineInventory22906
IVIC0200Manufacturing Series Inventory HeaderInventory22895
IVIC0101Manufacturing Series Inventory LineInventory22897
IVR40500Manufacturing Series Item Class Fulfill SetupInventory22871
IC10200Manufacturing Series Purchase ReceiptsInventory22000
WO010701Manufacture Order Closing FileMOP22046
WO010615Manufacture Order Finished Serial AssignMOP22045
WOA01060Manufacture Order Finished Serial Assign ArchiveMOP22175
WO010213Manufacture Order HistoryMOP22117
WO010302Manufacture Order Lot IssueMOP22115
WO010400Manufacture Order Lot/Serial IssueMOP22113
WO010032Manufacture Order MasterMOP22044
WOUF0132Manufacture Order Reporting OptionsMOP22238
WO010515Manufacture Order Serial IssueMOP22114
WOPS0100Manufacture Order Setup FileMOP22123
WO010116MOP Historical VarianceMOP22135
PK010033Picklist FileMOP22109
PK020031Picklist File for a single Finished GoodMOP22389
MOP10213Manufacture Order ActivityMOP22329
MOP1600Manufacture Order Complete Close WorkMOP22364
MOP10223Manufacture Order LockingMOP22319
MOP1020Manufacture Order Lot IssueMOP22330
MOP1030Manufacture Order Lot IssueMOP22349
MOP1027Manufacture Order Lot IssueMOP22398
MOP1090Manufacture Order Lot Issue HISTMOP22420
MOP1025Manufacture Order Pending BinMOP22403
MOP1095Manufacture Order Pending Bin HISTMOP22419
MOP1026Manufacture Order Pending Bin TrxMOP22402
MOPV1100Manufacturing Order List ViewMOP22504
MF010130MPS LineMPS22187
MF010330MPS Line HeaderMPS22687
MF010030MPS MasterMPS22169
MF010230MPS ScheduleMPS22688
MF010430MPS Transaction LogMPS22690
MP010000Manufacture Order LockingMRP22509
MP030000MRP Exclusion ListMRP22610
MPS10000MRP SecurityMRP22206
MPPS0130MRP Setup FileMRP22149
MP040000MRP StatusesMRP22241
RT010500Routing Cycle TimeRouting22723

GP Tables

Payables Management

TableDescriptionResource ID
PM00100Class Master File49
PM00101Vendor Class Accounts638
PM00200Vendor Master File48
PM00201Vendor Master Summary182
PM00202Vendor Master Period Summary225
PM00203Vendor Accounts639
PM00204Purchasing 1099 Period Detail1245
PM00300Address Master50
PM00400Key Master File181
PM10000Transaction WORK File223
PM10100Distribution WORK OPEN202
PM10200Apply To WORK OPEN File191
PM10201Payment Apply To Work File211
PM10300Payment WORK212
PM10400Manual Payment WORK File209
PM10500Tax Work File220
PM10600Distribution Void WORK Temporary File201
PM10801Payment Stub Duplicate406
PM10900Void Payment WORK Temporary File227
PM10901Void Transaction WORK Temporary File229
PM10902Tax Void Work Temporary File219
PM20000Transaction OPEN File192
PM20100Apply To OPEN OPEN Temporary File195
PM20200Distribution OPEN OPEN Temporary File196
PM20400Scheduled Payments Header979
PM20401Scheduled Payment LINE1033
PM30200Paid Transaction History File210
PM30300Apply To History File194
PM30401Scheduled Payments Header History984
PM30600Distribution History File199
PM30700Tax History File218
PM30800Tax Invoices854
PM40100Setup File183
PM40101Period Setup File176
PM40102Payables Document Types203
PM40103Payables Distribution Type SETP457
PM401041099 Setup File1214
PM50100History Removal Tempoary File787
PM70500Purchasing Report Options177
PM80100Reprint Transaction Batch Headers214
PM80200Void Reprint Void Payment WORK134
PM80300Reprint Void Transaction WORK132
PM80500Reprint Posting Journal Apply To File213
PM80600Reprint Distribution215
PM80700Reprint Tax216
PM80800Reprint Transaction Check Register File217
PM80900Mass Voucher Apply Temp File619
PM80905Payables Apply Document Browse108
PM80950Remittance Temp1343
PM80960Exception List Temp1342
PM81000Modified Vendor Class Accounts646
PMV0100Scheduled Payment Header View985

GP Tables

Receivable Management

TABLEDESCRIPTIONRESOURCE ID
RM00101Customer Master141
RM00102Customer Master Address167
RM00103Customer Master Summary161
RM00104Customer Period Summary166
RM00105National Accounts Master654
RM00106Statements E-mail Addresses928
RM00201Class Master165
RM00301Salesperson Master155
RM00302Salesperson Master Summary156
RM00303Sales Territory Master153
RM00305Sales Territory Master Summary154
RM00401Key147
RM00500Extended Pricing Price Sheet Links1056
RM00700Report Options179
RM10101Distribution Work146
RM10201Cash Receipts Work151
RM10301Sales Work148
RM10501Commission Work152
RM10504Unpaid Removed Commissions360
RM10601Tax Work157
RM10901Aging Reconcile Control278
RM20101Open143
RM20101Open Debit Copy752
RM20102Realtime Posting348
RM20201Apply Open150
RM20400Scheduled Payment Header1035
RM20401Scheduled Payment Line1038
RM30101History344
RM30201Apply History337
RM30202Apply Reprint Journal357
RM30301Receivables GL Distribution History342
RM30401Scheduled Payment Header History1036
RM30501Commission History340
RM30502Batch History163
RM30601Tax History352
RM30701Reprint Statements Header923
RM30702Reprint Statements Transactions926
RM40101Module Setup185
RM40102Reprint Statements Identifier924
RM40201Period Setup145
RM40401Document Type Setup144
RM40501Statements Options351
RM40601Statement Messages Setup350
RM50100History Removal Tempoary826
RM50101Statements Emailed Header Temp929
RM50102Statements Emailed RecipientsTemp930
RM50103Email Statements Header Temporary1086
RM50104Email Statements Transactions Temporary1087
RMV0100Scheduled Payment Header View1037

GP Tables

Inventory

TABLEDESCRIPTIONRESOURCE ID
IV00101Item Master364
IV00102Item Quantity Master372
IV00103Item Vendor Master367
IV00104Item Kit Master366
IV00105Item Currency Master777
IV00106Item Purchasing776
IV00107Item Price List Options833
IV00108Item Price List832
IV00109Item Serial Number Mask831
IV00111Site Defaults935
IV00112Item Site Bin Master981
IV00115Multiple Manufacture Items Master1134
IV00117Item Site Bin Priorities1082
IV00118Item Cost Change History1183
IV00200Item Serial Number Master383
IV00300Item Lot Number Master377
IV00301Item Lot Attribute Master376
IV10000Inventory Transaction Work394
IV10001Inventory Transaction Amounts Work395
IV10002Inventory Serial and Lot Number Work396
IV10003Inventory Transaction Bin Quantities Work983
IV10004Inventory Bin Quantity Transfer1027
IV10200Inventory Purchase Receipts Work378
IV10201Inventory Purchase Receipts Detail1165
IV10300Stock Count861
IV10301Stock Count Line862
IV10302Stock Count Serial Lot863
IV10303Stock Count U of M864
IV30100Inventory Transaction Batch History390
IV30101Inventory Sales Summary History385
IV30102Inventory Sales Summary Period History386
IV30200Inventory Transaction History391
IV30300Inventory Transaction Amounts History392
IV30301Inventory Transaction Detail History128
IV30302Inventory Transaction Bin Quantities History982
IV30400Item Serial and Lot Number History393
IV30500Inventory Distribution History363
IV30600Item Lot Attribute History463
IV30700Stock Count History866
IV30701Stock Count Line History867
IV30702Stock Count Serial Lot History868

GP Tables

General Ledger

TABLEDESCRIPTIONRESOURCE ID
GL00100Breakdown Account Master10014
GL00100Account Master3
GL00100Distribution Account Master10013
GL00102Account Category Master7
GL00103Fixed Allocation Master6
GL00104Variable Allocation Master5
GL00105Account Index Master531
GL00200Budget Master18
GL00201Budget Summary Master19
GL10000Transaction Work11
GL10001Transaction Amounts Work13
GL10002Transaction Clearing Amounts Work12
GL10100Quick Journal Work15
GL10101Quick Journal Amounts Work16
GL10110Account Current Summary Master4
GL10111Account Summary History42
GL10300General Ledger Tax Work936
GL11000GL Distribution Register Temporary102
GL12000Budget Transaction Work1344
GL12001Budget Transaction Amounts Work1346
GL20000Year-to-Date Transaction Open46
GL30000Account Transaction History45
GL32000Budget Transaction History1345