| Description |
This issue relates to i21 Help Desk ticket. Click the link below to go to Help Desk ticket.
[HDTN-234201 - DPR Company Titled Issue|https://helpdesk.irely.com/iRelyi21live//#/HD/Ticket/?ticket=HDTN-234201]
We created a new Inventory Transaction type for Transfer Storage process. In order to display the said transaction in the DPI we need some changes in uspRKGetCompanyTitled.
Please see the details below.
Starting Ln. 311
UNION
SELECT
strCommodityCode
, i.strItemNo
, Category.strCategoryCode
, dblTotal = dbo.fnCalculateQtyBetweenUOM(iuomStck.intItemUOMId, iuomTo.intItemUOMId, (ISNULL(ITS.dblQty ,0)))
, strLocationName
, intCommodityId = @intCommodityId
, intFromCommodityUnitMeasureId = @intCommodityUnitMeasureId
, strInventoryType = 'Company Titled' COLLATE Latin1_General_CI_AS
, ITS.dtmDate
, 'Transfer Storage'
, ITS.intTransactionId
, ITS.intTransactionDetailId
, TSR.intTransferStorageId
FROM tblICInventoryTransaction ITS
INNER JOIN tblGRTransferStorageReference TSR
ON TSR.intTransferStorageId = ITS.intTransactionId AND TSR.intTransferStorageSplitId = ITS.intTransactionDetailId
INNER JOIN tblGRTransferStorage GTS
ON TSR.intTransferStorageId = GTS.intTransferStorageId
INNER JOIN tblGRCustomerStorage FromStorage
ON FromStorage.intCustomerStorageId = TSR.intSourceCustomerStorageId
INNER JOIN tblGRStorageType FromType
ON FromType.intStorageScheduleTypeId = FromStorage.intStorageTypeId
INNER JOIN tblGRCustomerStorage ToStorage
ON ToStorage.intCustomerStorageId = TSR.intToCustomerStorageId
INNER JOIN tblGRStorageType ToType
ON ToType.intStorageScheduleTypeId = ToStorage.intStorageTypeId
JOIN tblICItemUOM IU
ON IU.intItemId = ToStorage.intItemId
AND IU.ysnStockUnit = 1
INNER JOIN tblICItemLocation IL
ON IL.intItemId = ToStorage.intItemId AND IL.intLocationId = ToStorage.intCompanyLocationId
INNER JOIN tblGRTransferStorage TS
ON TSR.intTransferStorageId = TS.intTransferStorageId
JOIN tblICItem i ON i.intItemId = ITS.intItemId
JOIN tblICCommodityUnitMeasure cuom ON i.intCommodityId = cuom.intCommodityId AND cuom.ysnStockUnit = 1
JOIN tblICItemUOM iuomStck ON ITS.intItemId = iuomStck.intItemId AND iuomStck.ysnStockUnit = 1
JOIN tblICItemUOM iuomTo ON ITS.intItemId = iuomTo.intItemId AND iuomTo.intUnitMeasureId = cuom.intUnitMeasureId
JOIN tblICCommodity c ON i.intCommodityId = c.intCommodityId
JOIN tblICCategory Category ON Category.intCategoryId = i.intCategoryId
JOIN tblSMCompanyLocation CL ON CL.intCompanyLocationId = GTS.intCompanyLocationId
WHERE (FromType.strStorageTypeDescription = 'DELAYED PRICING' AND ToType.strStorageTypeDescription = 'OPEN STORAGE') --OR (FromType.strStorageTypeDescription = 'DELAYED PRICING' AND ToType.strStorageTypeDescription = 'OPEN STORAGE')
AND ITS.intTransactionTypeId = 56
Starting Ln. 685
UNION ALL
SELECT
dtmDate
,dblUnpaidIncrease = CASE WHEN dblQtyShipped < 0 THEN 0 ELSE dblQtyShipped END
,dblUnpaidDecrease = CASE WHEN dblQtyShipped < 0 THEN ABS(dblQtyShipped) ELSE 0 END
,dblUnpaidBalance = 0
,dblPaidBalance = 0
,strTransactionId = strTransferStorageTicket
,intTransactionId
,'TS'
FROM (
SELECT DISTINCT dtmDate = CONVERT(DATETIME, CONVERT(VARCHAR(10),Inv.dtmDate, 110), 110)
,dblQtyShipped = Inv.dblTotal
,TS.strTransferStorageTicket
,Inv.intTransactionId
FROM @InventoryStock Inv
INNER JOIN tblGRTransferStorage TS ON Inv.intTransactionId = TS.intTransferStorageId
INNER JOIN tblGRTransferStorageSplit TSS ON Inv.intTransactionDetailId = TSS.intTransferStorageSplitId
) T
Please adjust the query if needed. I attached a sample working sp from my local dev environment for your reference.
Thanks
|