Automate

Good morning,
We have a requirement each month to send to specific departs a list of open purchase orders to determine if the items have been received or not received. The file would need to include the purchase order data identify the items where the items have not been received or partially received and then pull in the requisition number and the person who created the requisition. Let's say that a PO had 10 line items. Eight of those line items have been received so the report would need to pick up the 2 that have not yet been received. Our current process is to run multiple reports and export to excel then use excel to join them together. Any assistance with this is greatly appreciated.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------

Hi Frank,
Take a look at the attached.  It's EKPO (line items), EKET (schedule lines) and EBAN with a left outer join, because in our system, we almost never have a PR prior to a PO created (it's a demo system).
Depending on what you need, you might also include EKBE (PO history), but it's not in this example.
HTH,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA
------------------------------

Original Message:
Sent: 08-11-2021 10:52
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Good morning,
We have a requirement each month to send to specific departs a list of open purchase orders to determine if the items have been received or not received. The file would need to include the purchase order data identify the items where the items have not been received or partially received and then pull in the requisition number and the person who created the requisition. Let's say that a PO had 10 line items. Eight of those line items have been received so the report would need to pick up the 2 that have not yet been received. Our current process is to run multiple reports and export to excel then use excel to join them together. Any assistance with this is greatly appreciated.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------

Thanks Sigrid,
The query works. I now have to figure out why it is returning one row on the PO multiple times.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------

Original Message:
Sent: 08-11-2021 11:10
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Hi Frank,
Take a look at the attached.  It's EKPO (line items), EKET (schedule lines) and EBAN with a left outer join, because in our system, we almost never have a PR prior to a PO created (it's a demo system).
Depending on what you need, you might also include EKBE (PO history), but it's not in this example.
HTH,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 10:52
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Good morning,
We have a requirement each month to send to specific departs a list of open purchase orders to determine if the items have been received or not received. The file would need to include the purchase order data identify the items where the items have not been received or partially received and then pull in the requisition number and the person who created the requisition. Let's say that a PO had 10 line items. Eight of those line items have been received so the report would need to pick up the 2 that have not yet been received. Our current process is to run multiple reports and export to excel then use excel to join them together. Any assistance with this is greatly appreciated.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------

Please look at the schedule lines, Frank.  It's likely the delivery is split, thus one row for each.


FYI
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA
------------------------------

Original Message:
Sent: 08-11-2021 15:21
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Thanks Sigrid,
The query works. I now have to figure out why it is returning one row on the PO multiple times.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752

Original Message:
Sent: 08-11-2021 11:10
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Hi Frank,
Take a look at the attached.  It's EKPO (line items), EKET (schedule lines) and EBAN with a left outer join, because in our system, we almost never have a PR prior to a PO created (it's a demo system).
Depending on what you need, you might also include EKBE (PO history), but it's not in this example.
HTH,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 10:52
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Good morning,
We have a requirement each month to send to specific departs a list of open purchase orders to determine if the items have been received or not received. The file would need to include the purchase order data identify the items where the items have not been received or partially received and then pull in the requisition number and the person who created the requisition. Let's say that a PO had 10 line items. Eight of those line items have been received so the report would need to pick up the 2 that have not yet been received. Our current process is to run multiple reports and export to excel then use excel to join them together. Any assistance with this is greatly appreciated.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------

Thanks Sigrid,
Looking further into the requirements, there are a couple of criteria that needs to be met:

• Has the amount ordered = to the amount received (ordered and received equals); and
• Has the amount that has been received been invoiced;
• Not sure if this impacts the payment, but has the invoice been paid.

If all the above, then provide that list. We would then run a transaction to flag them as complete

The reason i was getting the duplicates is that i linked to the requisition table but the ekpo table already had that information.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------

Original Message:
Sent: 08-11-2021 15:28
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Please look at the schedule lines, Frank.  It's likely the delivery is split, thus one row for each.


FYI
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 15:21
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Thanks Sigrid,
The query works. I now have to figure out why it is returning one row on the PO multiple times.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752

Original Message:
Sent: 08-11-2021 11:10
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Hi Frank,
Take a look at the attached.  It's EKPO (line items), EKET (schedule lines) and EBAN with a left outer join, because in our system, we almost never have a PR prior to a PO created (it's a demo system).
Depending on what you need, you might also include EKBE (PO history), but it's not in this example.
HTH,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 10:52
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Good morning,
We have a requirement each month to send to specific departs a list of open purchase orders to determine if the items have been received or not received. The file would need to include the purchase order data identify the items where the items have not been received or partially received and then pull in the requisition number and the person who created the requisition. Let's say that a PO had 10 line items. Eight of those line items have been received so the report would need to pick up the 2 that have not yet been received. Our current process is to run multiple reports and export to excel then use excel to join them together. Any assistance with this is greatly appreciated.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------

That's good news, Frank. 

It's much easier to remove than find & add. :)

Cheers,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA
------------------------------

Original Message:
Sent: 08-11-2021 17:12
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Thanks Sigrid,
Looking further into the requirements, there are a couple of criteria that needs to be met:

• Has the amount ordered = to the amount received (ordered and received equals); and
• Has the amount that has been received been invoiced;
• Not sure if this impacts the payment, but has the invoice been paid.

If all the above, then provide that list. We would then run a transaction to flag them as complete

The reason i was getting the duplicates is that i linked to the requisition table but the ekpo table already had that information.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752

Original Message:
Sent: 08-11-2021 15:28
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Please look at the schedule lines, Frank.  It's likely the delivery is split, thus one row for each.


FYI
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 15:21
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Thanks Sigrid,
The query works. I now have to figure out why it is returning one row on the PO multiple times.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752

Original Message:
Sent: 08-11-2021 11:10
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Hi Frank,
Take a look at the attached.  It's EKPO (line items), EKET (schedule lines) and EBAN with a left outer join, because in our system, we almost never have a PR prior to a PO created (it's a demo system).
Depending on what you need, you might also include EKBE (PO history), but it's not in this example.
HTH,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 10:52
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Good morning,
We have a requirement each month to send to specific departs a list of open purchase orders to determine if the items have been received or not received. The file would need to include the purchase order data identify the items where the items have not been received or partially received and then pull in the requisition number and the person who created the requisition. Let's say that a PO had 10 line items. Eight of those line items have been received so the report would need to pick up the 2 that have not yet been received. Our current process is to run multiple reports and export to excel then use excel to join them together. Any assistance with this is greatly appreciated.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------

Original Message:
Sent: 8/11/2021 8:19:00 PM
From: Sigrid Kok
Subject: RE: Purchase orders - items not yet delivered

That's good news, Frank. 

It's much easier to remove than find & add. :)

Cheers,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA
------------------------------

Original Message:
Sent: 08-11-2021 17:12
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Thanks Sigrid,
Looking further into the requirements, there are a couple of criteria that needs to be met:

• Has the amount ordered = to the amount received (ordered and received equals); and
• Has the amount that has been received been invoiced;
• Not sure if this impacts the payment, but has the invoice been paid.

If all the above, then provide that list. We would then run a transaction to flag them as complete

The reason i was getting the duplicates is that i linked to the requisition table but the ekpo table already had that information.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752

Original Message:
Sent: 08-11-2021 15:28
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Please look at the schedule lines, Frank.  It's likely the delivery is split, thus one row for each.


FYI
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 15:21
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Thanks Sigrid,
The query works. I now have to figure out why it is returning one row on the PO multiple times.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752

Original Message:
Sent: 08-11-2021 11:10
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Hi Frank,
Take a look at the attached.  It's EKPO (line items), EKET (schedule lines) and EBAN with a left outer join, because in our system, we almost never have a PR prior to a PO created (it's a demo system).
Depending on what you need, you might also include EKBE (PO history), but it's not in this example.
HTH,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 10:52
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Good morning,
We have a requirement each month to send to specific departs a list of open purchase orders to determine if the items have been received or not received. The file would need to include the purchase order data identify the items where the items have not been received or partially received and then pull in the requisition number and the person who created the requisition. Let's say that a PO had 10 line items. Eight of those line items have been received so the report would need to pick up the 2 that have not yet been received. Our current process is to run multiple reports and export to excel then use excel to join them together. Any assistance with this is greatly appreciated.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------

Hi Frank,

I'm not sure what you're missing.  You have the PO tables and the PO history table in my first reply.  They should have the counts you listed.  You may need to roll them up in a pivot table, but they're there.

Since you state you still don't have what you need and your questions are more about SAP tables , I suggest you search on this site: https://answers.sap.com/index.html  for what you need.  It's where I go for info when I'm stuck.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA
------------------------------

Original Message:
Sent: 08-12-2021 10:07
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Good morning,

We have made some steps but getting the required data is a challenge.

Thanks,

Frank

 

Original Message:
Sent: 8/11/2021 8:19:00 PM
From: Sigrid Kok
Subject: RE: Purchase orders - items not yet delivered

That's good news, Frank.

It's much easier to remove than find & add. :)

Cheers,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 17:12
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Thanks Sigrid,
Looking further into the requirements, there are a couple of criteria that needs to be met:

• Has the amount ordered = to the amount received (ordered and received equals); and
• Has the amount that has been received been invoiced;
• Not sure if this impacts the payment, but has the invoice been paid.

If all the above, then provide that list. We would then run a transaction to flag them as complete

The reason i was getting the duplicates is that i linked to the requisition table but the ekpo table already had that information.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752

Original Message:
Sent: 08-11-2021 15:28
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Please look at the schedule lines, Frank.  It's likely the delivery is split, thus one row for each.


FYI
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 15:21
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Thanks Sigrid,
The query works. I now have to figure out why it is returning one row on the PO multiple times.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752

Original Message:
Sent: 08-11-2021 11:10
From: Sigrid Kok
Subject: Purchase orders - items not yet delivered

Hi Frank,
Take a look at the attached.  It's EKPO (line items), EKET (schedule lines) and EBAN with a left outer join, because in our system, we almost never have a PR prior to a PO created (it's a demo system).
Depending on what you need, you might also include EKBE (PO history), but it's not in this example.
HTH,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 08-11-2021 10:52
From: Frank Simms
Subject: Purchase orders - items not yet delivered

Good morning,
We have a requirement each month to send to specific departs a list of open purchase orders to determine if the items have been received or not received. The file would need to include the purchase order data identify the items where the items have not been received or partially received and then pull in the requisition number and the person who created the requisition. Let's say that a PO had 10 line items. Eight of those line items have been received so the report would need to pick up the 2 that have not yet been received. Our current process is to run multiple reports and export to excel then use excel to join them together. Any assistance with this is greatly appreciated.
thanks,
Frank

------------------------------
Frank Simms | Controller
Equinox Gold Corp | 9289193752
------------------------------