Automate

Hey Expert, 

I'm struggling to pull the data I need from FBL1N. We are getting open items, but not ALL items. I cannot seem to find the proper trigger to get us everything. We are using a date range, certain doc. types and the vendor number cannot be blank. Also, all of the outputs are showing as positive dollar amounts, even if they should be showing as negative. 

I have included a copy of the query, template and an example export from SAP. All data is from the same date frame and SAP environment. 

Any thoughts/help greatly appreciated. 

------------------------------
Vanessa Kutasi
Foundation Admin./Application Analyst II
Idexx Operations, Inc.
USA
------------------------------

Hi Vanessa

Looks like your query is similar to mine, and it looks like the download from FBL1N is nearly identical to your query output.

BSIK is for open vendor items, so I just pull all doc types, and it ends up matching SAP's FBL1N.  But I am in a demo system, so there may be scenarios I'm not running into that you are.

SAP stores the values as positive, so you can use a formula to have it show up as negative, if that's what you want to show in your extract.

I've attached my queries, in case it's helpful.  One is high level, and one has more details on each line.   Again, I'm in a demo system, so I added search criteria to narrow down output a bit more, because we have tons of open invoices across many years.

Best Regards,

Sigrid

PS:  I sometimes run a SQL trace - tcode: ST05.  It also showed that it didn't filter BSIK by doctype, but again, I understand why you might want to.

------------------------------
Sigrid Kok
Precisely Software Inc.

Original Message:
Sent: 03-22-2023 15:50
From: Vanessa Kutasi
Subject: FBL1N Query

Hey Expert, 

I'm struggling to pull the data I need from FBL1N. We are getting open items, but not ALL items. I cannot seem to find the proper trigger to get us everything. We are using a date range, certain doc. types and the vendor number cannot be blank. Also, all of the outputs are showing as positive dollar amounts, even if they should be showing as negative. 

I have included a copy of the query, template and an example export from SAP. All data is from the same date frame and SAP environment. 

Any thoughts/help greatly appreciated. 

------------------------------
Vanessa Kutasi
Foundation Admin./Application Analyst II
Idexx Operations, Inc.
USA
------------------------------

Hi Sigrid

Thank you this was very helpful! After further comparison of the SAP & Winshuttle extracts, the values are all correct, they are just missing the - indicator. I pulled the debit/credit indicator into the query and found that it aligns with the -/+ values in SAP, so I am using that to transform the values appropriately. Everything is aligning now except the currency key. So I'll keep testing that piece. Otherwise, it looks like we are good. Thank you again!

Vanessa

Hi Vanessa

Looks like your query is similar to mine, and it looks like the download from FBL1N is nearly identical to your query output.

BSIK is for open vendor items, so I just pull all doc types, and it ends up matching SAP's FBL1N.  But I am in a demo system, so there may be scenarios I'm not running into that you are.

SAP stores the values as positive, so you can use a formula to have it show up as negative, if that's what you want to show in your extract.

I've attached my queries, in case it's helpful.  One is high level, and one has more details on each line.   Again, I'm in a demo system, so I added search criteria to narrow down output a bit more, because we have tons of open invoices across many years.

Best Regards,

Sigrid

PS:  I sometimes run a SQL trace - tcode: ST05.  It also showed that it didn't filter BSIK by doctype, but again, I understand why you might want to.

------------------------------
Sigrid Kok
Precisely Software Inc.

Original Message:
Sent: 03-22-2023 15:50
From: Vanessa Kutasi
Subject: FBL1N Query

Hey Expert, 

I'm struggling to pull the data I need from FBL1N. We are getting open items, but not ALL items. I cannot seem to find the proper trigger to get us everything. We are using a date range, certain doc. types and the vendor number cannot be blank. Also, all of the outputs are showing as positive dollar amounts, even if they should be showing as negative. 

I have included a copy of the query, template and an example export from SAP. All data is from the same date frame and SAP environment. 

Any thoughts/help greatly appreciated. 

------------------------------
Vanessa Kutasi
Foundation Admin./Application Analyst II
Idexx Operations, Inc.
USA
------------------------------

Great, Vanessa.

I think there is only one currency key in the BSIK table, but there are local and document values.

Glad it's working - or close to it!

Sigrid

Hi Sigrid

Thank you this was very helpful! After further comparison of the SAP & Winshuttle extracts, the values are all correct, they are just missing the - indicator. I pulled the debit/credit indicator into the query and found that it aligns with the -/+ values in SAP, so I am using that to transform the values appropriately. Everything is aligning now except the currency key. So I'll keep testing that piece. Otherwise, it looks like we are good. Thank you again!

Vanessa

Hi Vanessa

Looks like your query is similar to mine, and it looks like the download from FBL1N is nearly identical to your query output.

BSIK is for open vendor items, so I just pull all doc types, and it ends up matching SAP's FBL1N.  But I am in a demo system, so there may be scenarios I'm not running into that you are.

SAP stores the values as positive, so you can use a formula to have it show up as negative, if that's what you want to show in your extract.

I've attached my queries, in case it's helpful.  One is high level, and one has more details on each line.   Again, I'm in a demo system, so I added search criteria to narrow down output a bit more, because we have tons of open invoices across many years.

Best Regards,

Sigrid

PS:  I sometimes run a SQL trace - tcode: ST05.  It also showed that it didn't filter BSIK by doctype, but again, I understand why you might want to.

------------------------------
Sigrid Kok
Precisely Software Inc.

Original Message:
Sent: 03-22-2023 15:50
From: Vanessa Kutasi
Subject: FBL1N Query

Hey Expert, 

I'm struggling to pull the data I need from FBL1N. We are getting open items, but not ALL items. I cannot seem to find the proper trigger to get us everything. We are using a date range, certain doc. types and the vendor number cannot be blank. Also, all of the outputs are showing as positive dollar amounts, even if they should be showing as negative. 

I have included a copy of the query, template and an example export from SAP. All data is from the same date frame and SAP environment. 

Any thoughts/help greatly appreciated. 

------------------------------
Vanessa Kutasi
Foundation Admin./Application Analyst II
Idexx Operations, Inc.
USA
------------------------------