Like
Share
Report One of the most important steps performed during Inventory Control Reconciliation is reconciling the purchases receipt details [ IV10201 ] against purchase receipt work [ IV10200 ]. This is practically performed by linking all the "details layers" to the associated "work layer".
Dynamics GP joins both tables through a receipt sequence number that is unique on an item-location base.
Receipt sequence number is unique on an item-location level.
-----------------------------------------------------------------------------------------------------------------
The following shows an example of reconciliation process effects;
-----------------------------------------------------------------------------------------------------------------
SQL Script - Corrupted Cost Layers
The following script retrieves the corrupted purchase receipt details against purchase receipt work layers.
SELECT A.[ITEMNMBR],
A.[TRXLOCTN],
A.[RCTSEQNM],
A.[QTYRECVD_200],
A.[QTYSOLD_200],
A.[RCPTSOLD_Index],
B.[ITEMNMBR],
B.[TRXLOCTN],
B.[SRCRCTSEQNM],
B.[QTYSOLD_201],
A.[QTYSOLD_200] - B.[QTYSOLD_201] AS VARIANCE
FROM
(
SELECT [ITEMNMBR],
[TRXLOCTN],
[RCTSEQNM],
SUM([QTYRECVD]) AS QTYRECVD_200,
SUM([QTYSOLD]) AS QTYSOLD_200 ,
RCPTSOLD_Index = CASE
WHEN SUM([QTYRECVD])-SUM([QTYSOLD]) = 0
THEN 1
ELSE 0
END
FROM [IV10200]
GROUP BY [ITEMNMBR], [TRXLOCTN], [RCTSEQNM]
) AS A
FULL OUTER JOIN
(
SELECT [ITEMNMBR],
[TRXLOCTN],
[SRCRCTSEQNM],
SUM([QTYSOLD]) AS QTYSOLD_201
FROM [IV10201]
GROUP BY [ITEMNMBR], [TRXLOCTN], [SRCRCTSEQNM]
) AS B
ON A.[ITEMNMBR] = B.[ITEMNMBR] AND
A.[TRXLOCTN] = B.[TRXLOCTN] AND
A.[RCTSEQNM] = B.[SRCRCTSEQNM]
WHERE
ISNULL(A.[QTYSOLD_200],0) - ISNULL(B.[QTYSOLD_201],0)
<> 0
Best Regards,
Mahmoud M. AlSaadi
Dynamics GP joins both tables through a receipt sequence number that is unique on an item-location base.
- Purchase Receipt Work | IV10200 - [RCTSEQNM]
- Purchase Receipt Details | IV10201 - [SRCRCTSEQNM]
Receipt sequence number is unique on an item-location level.
-----------------------------------------------------------------------------------------------------------------
The following shows an example of reconciliation process effects;
Regular Case | Due to posting interruption, a record is missing from Purchase Receipt Details
Before Reconciliation
· IV10200 |Inventory Purchase Receipts Work
| Document Type | Document Number | Item Number | TRXLOCTN | Date Received | QTY Received | QTY Sold | Unit Cost | Adjust Cost |
| Transfer | Transfer1111 | 100XLG | Main | 2011-03-10 | 12 | 12 | 60 | 60 |
· IV10201 |Inventory Purchase Receipts Work Detail
| | Item Number | TRXLOCTN | Doc Date | Document No. | QTY SOLD | UNITCOST | ||
| | 100XLG | Main | 2011-03-10 | INVOICE12311 | 1 | 60 | ||
| | 100XLG | Main | 2011-03-13 | INVOICE11111 | 6 | 60 | ||
| | 100XLG | Main | 2011-03-16 | INVOICE22222 | 2 | 60 | ||
| Grand Total | | | | | 9 | |
After Reconciliation
· The following Recon is added into purchase receipt details in order to match the quantities between the inventory tables
| | Item Number | TRXLOCTN | Doc Date | Document No. | QTY SOLD | UNITCOST |
| | 100XLG | Main | 2011-03-10 | RECON | 3 | 60 |
Once the inventory reconciliation process is finished, the report will show the quantity adjustments made above on an item level as follows;
Reconcile Report
Item Number: 100XLG
Item Description: Green Phone
Transaction Location: Main
Added Quantity Sold detail record quantity of 3 for receipt Transfer1111
SQL Script - Corrupted Cost Layers
The following script retrieves the corrupted purchase receipt details against purchase receipt work layers.
SELECT A.[ITEMNMBR],
A.[TRXLOCTN],
A.[RCTSEQNM],
A.[QTYRECVD_200],
A.[QTYSOLD_200],
A.[RCPTSOLD_Index],
B.[ITEMNMBR],
B.[TRXLOCTN],
B.[SRCRCTSEQNM],
B.[QTYSOLD_201],
A.[QTYSOLD_200] - B.[QTYSOLD_201] AS VARIANCE
FROM
(
SELECT [ITEMNMBR],
[TRXLOCTN],
[RCTSEQNM],
SUM([QTYRECVD]) AS QTYRECVD_200,
SUM([QTYSOLD]) AS QTYSOLD_200 ,
RCPTSOLD_Index = CASE
WHEN SUM([QTYRECVD])-SUM([QTYSOLD]) = 0
THEN 1
ELSE 0
END
FROM [IV10200]
GROUP BY [ITEMNMBR], [TRXLOCTN], [RCTSEQNM]
) AS A
FULL OUTER JOIN
(
SELECT [ITEMNMBR],
[TRXLOCTN],
[SRCRCTSEQNM],
SUM([QTYSOLD]) AS QTYSOLD_201
FROM [IV10201]
GROUP BY [ITEMNMBR], [TRXLOCTN], [SRCRCTSEQNM]
) AS B
ON A.[ITEMNMBR] = B.[ITEMNMBR] AND
A.[TRXLOCTN] = B.[TRXLOCTN] AND
A.[RCTSEQNM] = B.[SRCRCTSEQNM]
WHERE
ISNULL(A.[QTYSOLD_200],0) - ISNULL(B.[QTYSOLD_201],0)
<> 0
Best Regards,
Mahmoud M. AlSaadi
*This post is locked for comments