cancel
Showing results for 
Search instead for 
Did you mean: 

Delta indicator for BSAK table

former_member198401
Active Contributor
0 Kudos

Hi

Could you please tell me how do we achieve the following.

We are pulling tables from SAP ECC and then do some analytics on the extracted data. For pulling data from SAP tables like BSEG and BKPF we are using delta columns from these tables E.g. BKPF.CPUDT

We have an issue with using CPUDT as delta indicator for BSAK.

E.g. The creation date of  document no 5100027711  is 2016.02.02; whereas the clearing Entry Date (AUGCP) and clearing date are both 2016.05.03.

The delta will never be triggered since the clearing is done at a later stage than document creation.

BSAK.CPUDT >= $StartDate and BSAK.CPUDT <= $EndDate

$StartDate and $EndDate are a window of 3 days.

Can anyone suggest what should be the delta logic in this case where Clearing Date doesnt fall within the CPUDT

OR

Do we need to set up Delta logic on BSEG.AUGCP. Do you recommend to do a delta on BSEG table as it is huge and will impact performance.

Regards

Arun Sasi

Accepted Solutions (0)

Answers (1)

Answers (1)

Gustavo_Vazquez
Active Contributor
0 Kudos

Hi Arun,

BSEG-AUGCP should be similar to your BSAK-CPUDT in your payment document ( BSAK-AUGBL = BSAK-BELNR )

Kind Regards

former_member198401
Active Contributor
0 Kudos

Thanks Gustavo for your prompt reply!! Truly appreciate your help!!

As I am not familiar with SAP, can you provide me an example. Also could you please help me in finding the delta logic with the input criteria on the SAP tables

Regards

Arun Sasi

iklovski
Active Contributor
0 Kudos

Hi Gustavo,

I don't think so... BSEG-AUGCP will be equal to BSAK-AUGDT. BSAK-CPUDT will not be the date, when document actually entered to BSAK table from BSIK, or was cleared in other words, but still it will be the date of creation of original document. E.g. if document was created on 15.05.15 and cleared on 02.06.16, BSAK-CPUDT will be 15.05.15 and BSAK-AUGDT=BSEG-AUGCP=02.06.16.

Regards,


Eli

Gustavo_Vazquez
Active Contributor
0 Kudos

Hi Eli,

I was talking about payment document. (BSAK-BELNR = BSAK-AUGBL )

If I'm on the invoice record in BSAK (BSAK-BELNR <> BSAK-AUGBL )  I don't think I can get the day the payment document was created. F-53 or F110 clearing date could be different from CPUDT in payment document

Kind Regards

former_member198401
Active Contributor
0 Kudos

Hi Eli,

In this case my Delta logic will fail as BSAK.CPUDT(Creation) is always less than BSAK.AUGDT(Clearing Date)

Regards

Arun Sasi

iklovski
Active Contributor
0 Kudos

Exactly

former_member198401
Active Contributor
0 Kudos

What could be the possible workaround to achieve this. There are lot of documents in the SAP system where Clearing date is greater than the Document creation date.

Regards

Arun Sasi

iklovski
Active Contributor
0 Kudos

This means that you have to work not only on CPUDT, but also on AUGDT. Delta on CPUDT can still be short... As for AUGDT, you should create a longer gap, because clearing date could be mentioned in the past. Alternatively, you have to make a check on AUGBL in BSAK, checking its CPUDT in BKPF.

former_member198401
Active Contributor
0 Kudos

Hi Eli,

Hope you are doing good!!

I pulled some sample data from our SAP ECC system and loaded into our Oracle staging database tables

Below is the query which I wrote to pull records where Clearing Date is greater than Posting Date(CPUDT). I hope you understand what I have done to achieve the requirement

Join BSAK and BKPF tables

select  B.BUKRS, B.AUGDT, B.CPUDT, B.AUGBL, B.GJAHR, B.BELNR

from STG_BSAK_2016 B  INNER JOIN STG_BKPF_2016 C ON B.AUGBL= C.BELNR AND B.CPUDT = C.CPUDT

where B.AUGBL = B.BELNR

AND B.CPUDT <= B.AUGDT/** Condition to check if Posting Date is less than Clearing Date

and B.CPUDT >= '01-SEP.2016' and B.CPUDT <= '30-SEP_2016'

UNION ALL/** I am combining both data sets

select B.BUKRS, B.AUGDT, B.CPUDT, B.AUGBL, B.GJAHR, B.BELNR

from STG_BSAK_2016 B  INNER JOIN STG_BKPF_2016 C ON B.AUGBL= C.BELNR AND B.CPUDT = C.CPUDT

where B.AUGBL=B.BELNR

AND B.CPUDT >= B.AUGDT/**Condition to check if Posting Date is greater than Clearing Date

and B.CPUDT >= '01-SEP.2016' and B.CPUDT <= '30-SEP_2016'

Regards

Arun Sasi