HELP ON QUERY GENERATION (STOCK TRANSFER)

[Former Member]

Dear SAP Community,

I would like to seek help on creating query report.  I need to generate report on STOCK TRANSFER per BP, get the items, posting date, invoiced items per whse location/BP Name, invoice posting dates, and the remaining balance per location/BP.

This is a scenario, we have consignment deals and instead of SO-DO-SI we do Stock Transfer to CONSIGNMENT warehouse location we created.  Now, we need to generate a report on:

1. Units that are consigned per BP

2. Aged of consignment

3. Invoiced items per BP

4. Remaining consigned units per BP

Hoping for your assistance.

[Johan_H]

Hi Joan,

Can you please describe in a little more detail what exactly you do in B1 for this scenario ?

For example, you create a Stock Transfer, and then you separately create an invoice ?

Regards,

Johan

[Former Member]

Hi Johan,

We call this as CONSIGNMENT transactions/orders.  This is what we do

I. Order Processing:

a. Inventory Transfer Request

      - Under particular BP Name

      - Choose Consignment Warehouse Location

b. Inventory accounting department to approve the ITR

c. Logistics department to add the ITR to Inventory Transfer

     - upload IMEI

Few days after order processing.

II. Processing of Sold Units

a. Process SO draft

     - Choose Item

     - Choose Consignment Warehouse Location

b. Accounting Department to approve SO draft

c. Logistics department to add the SO draft to SO approved

   - upload IMEI of sold units

   - create DR

d. Accounting Department to create Invoice

Since the reporting of sold units from consignment took days and they only report partially to invoice what were actually sold.  We need to create a report to monitor the following:

1. Units that are consigned per BP

2. Aged of consignment

3. Invoiced items per BP

4. Remaining consigned units per BP

Hoping for assistance on this.

[Johan_H]

Hi Joan,

One more question, does a BP always receive goods from the same warehouse ?

In other words, does each consignment warehouse have its own BPs ?

Or can a BP order their goods from different consignment warehouses ?

Regards,

Johan

[Former Member]

Hi Johan,

All consignments are in one same warehouse.  Below is the inventory of the Consignment location, and these are accumulation of various BP's and different dates of transactions. 

[Johan_H]

Two more question (these are really the last ones 😞

1.

Joan Ang wrote:
...
2. Aged of consignment
...

I assume that this is supposed to be the difference (in days) between the DocDate of Inventory Transfer and the DocDate of Delivery Note (to BP) ?

Theoretically, a BP could purchase the same item many times. The first time, the goods are in consignment for 3 days, the second time for 14 days, the third time for 1 day, the fourth time for 7 days, etc. etc.

How do you need this information to be presented in the report?

• A line per item per consignment-delivery. I.e. as many lines per item as there are Inventory Transfers x Delivery Notes.
• One line per item, where this number is the average age of consignment of all transactions.

2.

I assume that you receive a preliminary order from your customer, which determines which items and quantities are transferred to the consignment warehouse, and later you receive an order confirmation from the customer that determines which items and quantities are actually delivered ?

What happens to items and quantities that were initially transferred to the consignment warehouse, but that were not ordered (confirmed) by the customer ?

What I mean is, can each Warehouse Transfer (line) be connected to a Sales Order 1:1, or can a Warehouse Transfer (line) be connected to many different Sales Orders (and even BPs) ?

Regards,

Johan

[Former Member]

Hi Johan,

To answer you, 

1. yes difference in transfer doc date and delivery note doc date.

We prefer to have the first one:

• A line per item per consignment-delivery. I.e. as many lines per item as there are Inventory Transfers x Delivery Notes.

2. For the unsold/undelivered items, either we do the following options:

* Pull-out the items and return to warehouse (both physically and in the system) by the following process:

    - compare the unsold items in SAP vs actual units returned

    - scan the units and transfer from Consignment location to Good To Sell location (these units will now be offered to other BP either by 1. Outright purchase or again 2. Consignment, but depends on the management agreement)

[Johan_H]

Hi Joan,

This is a pretty complicated scenario from a query perspective, and as I do not have relevant data in my database, I cannot actually test it, but here is a first attempt:

SELECT inh.CardCode

      ,inh.CardName

      ,i.ItemCode

      ,i.ItemName

      ,ith.DocNum AS 'IT DocNum'

      ,ith.DocDate

      ,SUM(ISNULL(itr.Quantity, 0)) AS ConsignedQuantity

      ,inh.DocNum AS 'IN DocNum'

      ,inh.DocDate

      ,SUM(ISNULL(inr.Quantity, 0)) AS InvoicedQuantity

      ,SUM(ISNULL(itr.Quantity, 0)) - SUM(ISNULL(inr.Quantity, 0)) AS RemainsInConsigment

      ,DATEDIFF(DAY, ith.DocDate, inh.DocDate) AS DaysInConsigment

FROM OITM i

     LEFT OUTER JOIN INV1 inr ON i.ItemCode = inr.ItemCode

     LEFT OUTER JOIN OINV inh ON inr.DocEntry = inh.DocEntry

     LEFT OUTER JOIN WTR1 itr ON i.ItemCode = itr.ItemCode AND inr.WhsCode = itr.WhsCode

     INNER JOIN OWTR ith ON itr.DocEntry = ith.DocEntry

                             AND inh.CardCode = ith.CardCode

                             AND inh.DocDate > ith.DocDate

WHERE inh.CardCode = [%0]

GROUP BY inh.CardCode

      ,inh.CardName

      ,i.ItemCode

      ,i.ItemName

      ,ith.DocNum

      ,ith.DocDate

      ,inh.DocNum

      ,inh.DocDate

HAVING SUM(ISNULL(itr.Quantity, 0)) > 0

Please give this a try, and let me know.

Regards,

Johan

[Former Member]

Hi Johan,

Thank you.  I will work with this today.  I will keep you posted.