query for service calls

[former_member218051]

Dear Experts,

We want to list out closed service calls whose invoice has not made.

For this, I used OSCL and SCL2 tables by linking on CALLID and using QTYTOINV = 0 as a flag. But, wrong result.

Please help me.

I also observed SCL4 table in which all the transactions against a particular service call are recorded. I tred to list the calls against which there is no object (13).

Help needed.

thanking You

Malhaar

[Former Member]

Dear Malhaar,

Have you tried:

SELECT Distinct T1.createDate, T1.callID, T1.custmrName,

T1.technician, T0.ItemCode,T0.ItemName,

T0.TransToTec, T0.QtyToInv, T1.status, t3.price,

T2.LastPurPrc,T2.LastPurPrc * T0.TransToTec AS 'Value'

FROM

SCL2 T0 INNER JOIN OSCL T1 ON T0.SrcvCallID = T1.callID

INNER JOIN OITM T2 ON T0.itemCode = T2.ItemCode

INNER JOIN SCL4 T4 ON T0.SrcvCallID = T1.callID AND T4.Object != '13'

LEFT JOIN INV1 t3 ON t3.itemcode = t2.itemcode AND T3.DocEntry = T4.DocAbs

WHERE T0.QtyToInv = 0 and T0.TransToTec > 0 and

T1.status = 1

ORDER BY T1.technician, T1.createDate

What is the result?

Thanks,

Gordon

[former_member218051]

Hi Gordon ,

I just changed T1.Status = -1 as i want to list the closed calls whose invoice is not there.

But , I'm not getting the right result.

It is giving me closed calls with invoices.

Thanking you

Malhaar

[former_member218051]

Hi Gordon,

How to list the service calls whose count (object) for object 13 is zero.

I guess that will solve this.

thanking You

Malhaar

[Former Member]

Try T1.status != 1

[former_member218051]

Hi Gordon,

I tried T1.Status != 1 but still it gives me service calls which are invoiced.

Thanks

Malhaar

[Former Member]

Hi Malhaar,

Judging from your original question that you only want to listing out service call which:

1. Status already closed

2. Invoice not yet created

Try this:

SELECT T0.callID,T0.CreateDate,T0.customer,T0.custmrName FROM OSCL T0
WHERE T0.status=-1 
AND T0.callID NOT IN(SELECT DISTINCT SrcvCallID FROM SCL4 WHERE Object=13)

Hope helps.

Best Regards,

Hendry Wijaya

[Former Member]

Try:

SELECT Distinct T1.createDate, T1.callID, T1.custmrName,

T1.technician, T0.ItemCode,T0.ItemName,

T0.TransToTec, T0.QtyToInv, T1.status,

T2.LastPurPrc,T2.LastPurPrc * T0.TransToTec AS 'Value'

FROM

SCL2 T0 INNER JOIN OSCL T1 ON T0.SrcvCallID = T1.callID

INNER JOIN OITM T2 ON T0.itemCode = T2.ItemCode

INNER JOIN SCL4 T4 ON T4.SrcvCallID = T1.callID AND T4.Object != '13'

WHERE T0.QtyToInv = 0 and T0.TransToTec > 0 and T1.status != 1

ORDER BY T1.technician, T1.createDate

[former_member218051]

Hi Henry and Gordon,

I tried all these queries.

Technically all these queries are right. Provided all the service related transactions have punched through

service module -

---

> Expenses Tab -

---

> New Documents and so on.

In this case only SAP maintains the qtytoinvoice flag in the SCL2 and object flag in the SCL4 table.

If user creates a stand alone A/R Invoice then System do not pass any reference in the SCL2 and SCL4 tables.

So, inspite of using these flags the query is not giving the desired results.

Thanking You

Malhaar

[Former Member]

For the standalone Invoices, they will be include in the query because there are no links in the table to be excluded. Query is not possible to identify them.

[Former Member]

Hi Malhaar,

Gordon is right. How can you match the standalone invoice with the service call if in SAP you're not even use a feature to relate those two things. There is a broken link here.

If you want to relate standalone invoice with service call, maybe you could consider using Activities in service call, and attached the document using Linked Document feature in activity.

Best Regards,

Hendry Wijaya

[former_member218051]

Hi Hendry and Gordon,

That's what i said .

Because users have punched the invoices outside of service module , the desired result is not possible.

Thanks for your precious time and guidance.

Thanking you

Malhaar