Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Could anyone provide the SAP 'BSAD' table incremental load logic?
Implemented the below logic but with one issue. if the Document is moved from BSAD to BSID (Open items), it is still there in BSAD.
BSAD:
LOAD
*
, BUKRS & GJAHR & BELNR & BUZEI as Documentitem;
SELECT * FROM BSAD WHERE AUGBL IN
( SELECT BELNR FROM BSAD WHERE CPUDT >= '$(vLastChangeDate_num)' );
concatenate (BSAD)
LOAD *
from BSAD.qvd (qvd)
where not exists(Documentitem);
The primary key isn't that complex if you take into account that each of those tables has about 180 columns.
You are right. I did the same. Pulled the key fields from SAP ( as mentioned by pcammaert )
and did the inner join with existing QVD. The problem here is , It took's around 2 hours (Only pulling the data).
We can't do this on daily basis.
As i don't understand the complete business, not able to find the proper logic to do this.
I found the below discussion is SAP forum with same issue. But, It seems to be no proper answer.
How to identify reverse or reset document nos from FI tables?
Dear Settu
I know its a bit old thread but I am facing kind of similar problem.
I am creating AR Ageing report in SAP and providing a variable for date on front end, so that user can see back dated Ageing report. I am not able to write down the logic to remove those invoices which has been cleared in the due period.
Can you give some suggestions if you have faced such kind of issue.
Best regards
Ahmed
Hi
The way I found to solved it, since there is not timestamp into these tables is shown as follow:
BSIDTEMP - This table contains the accumulated data up to the last extraction.
BSIDINCR - Contains the information for new records and possible duplicate records based on the time extraction
BSIDDELE - Copy of BSIDINCR with two Fields only, Join from fields (GJAHR & BELNR & BUZEI & ZUONR)as BSIDkey and 'DELETE' as FlagToDelete
BSADTEMP - This table contains the accumulated data up to the last extraction.
BSADINCR - Contains the information for new records and possible duplicate records based on the time extraction
BSADDELE - Copy of BSADINCR with two Fields only, Join from fields (GJAHR & BELNR & BUZEI & ZUONR)as BSIDkey and 'DELETE' as FlagToDelete
BSADvsBSID - Used to identified cleared documents to be removed from BSID
BSIDvsBSAD - Used to identified re-open documents to removed from BSAD
BSID
1 Create the incremental using field CPUDT (BSIDINCR)
2 Create BSIDDELE (QVD) based on the table BSIDINCR records
3 Using applymap to identify records in BSIDTEMP from BSIDDELE and mark them to be deleted
4 Load BSIDTEMP with records <> 'DELETE'
5 Concatenate records from BSIDINCR into BSIDTEMP.
Example with data | |||||
BSIDTEMP | |||||
Fiscal Year | Document Number | Line item | Assignment | Entered on | |
GJAHR | BELNR | BUZEI | ZUONR | CPUDT | |
2020 | 5000000063 | 1 | 0973216 | 7/18/2019 | |
2020 | 5000000064 | 2 | 0947241 | 7/18/2019 | |
2020 | 5000000065 | 1 | 0974159 | 7/18/2019 | |
2020 | 100000447 | 2 | 0974259 | 7/18/2019 | |
2020 | 100100480 | 3 | 0974357 | 7/19/2019 | |
2020 | 1400000310 | 4 | 0974453 | 7/19/2019 | |
BSIDINCR | |||||
Fiscal Year | Document Number | Line item | Assignment | Entered on | |
GJAHR | BELNR | BUZEI | ZUONR | CPUDT | |
2020 | 100100480 | 3 | 0974357 | 7/19/2019 | |
2020 | 1400000310 | 4 | 0974453 | 7/19/2019 | |
2020 | 5000000066 | 1 | 0974459 | 7/19/2019 | |
2020 | 5000000067 | 3 | 0974460 | 7/19/2019 | |
2020 | 5000000068 | 2 | 0974461 | 7/19/2019 | |
BSIDDELE | |||||
BSIDKey | FlagToDelete | ||||
202010010048030974357 | DELETE | ||||
2020140000031040974453 | DELETE | ||||
2020500000006610974459 | DELETE | ||||
2020500000006730974460 | DELETE | ||||
2020500000006820974461 | DELETE | ||||
APPLYMAP FROM BSIDDELE INTO BSIDTEMP | |||||
BSIDTEMP | |||||
Fiscal Year | Document Number | Line item | Assignment | Entered on | FlagToDelete |
GJAHR | BELNR | BUZEI | ZUONR | CPUDT | |
2020 | 5000000063 | 1 | 0973216 | 7/18/2019 | |
2020 | 5000000064 | 2 | 0947241 | 7/18/2019 | |
2020 | 5000000065 | 1 | 0974159 | 7/18/2019 | |
2020 | 100000447 | 2 | 0974259 | 7/18/2019 | |
2020 | 100100480 | 3 | 0974357 | 7/19/2019 | DELETE |
2020 | 1400000310 | 4 | 0974453 | 7/19/2019 | DELETE |
LOAD GJAHR BELNR BUZEI ZUONR CPUDT from BSIDTEMP Where FlagToDelete <> 'DELETE' | |||||
Fiscal Year | Document Number | Line item | Assignment | Entered on | |
GJAHR | BELNR | BUZEI | ZUONR | CPUDT | |
2020 | 5000000063 | 1 | 0973216 | 7/18/2019 | |
2020 | 5000000064 | 2 | 0947241 | 7/18/2019 | |
2020 | 5000000065 | 1 | 0974159 | 7/18/2019 | |
2020 | 100000447 | 2 | 0974259 | 7/18/2019 | |
CONCATENATE BSIDTEMP AND BSIDINCR | |||||
Fiscal Year | Document Number | Line item | Assignment | Entered on | |
GJAHR | BELNR | BUZEI | ZUONR | CPUDT | |
2020 | 5000000063 | 1 | 0973216 | 7/18/2019 | |
2020 | 5000000064 | 2 | 0947241 | 7/18/2019 | |
2020 | 5000000065 | 1 | 0974159 | 7/18/2019 | |
2020 | 100000447 | 2 | 0974259 | 7/18/2019 | |
2020 | 100100480 | 3 | 0974357 | 7/19/2019 | |
2020 | 1400000310 | 4 | 0974453 | 7/19/2019 | |
2020 | 5000000066 | 1 | 0974459 | 7/19/2019 | |
2020 | 5000000067 | 3 | 0974460 | 7/19/2019 | |
2020 | 5000000068 | 2 | 0974461 | 7/19/2019 |
For BSAD you have to follow the same process; the diference is
BSADINCR - Contains the information for new cleared records and possible duplicate records based on the time extraction. In my previous example July, 19 2019.
1 Create the incremental using fields CPUDT AND BUDAT (BSADINCR)
Once you have your tables BSIDTEMP and BSADTEMP with the incremental records, you have to identify those records that exists in both tables.
1 Create a QVD with BSADTEMP information, this new QVD will be called BSADvsBSID (Using previous Join from fields (GJAHR & BELNR & BUZEI & ZUONR)as BSADkey and 'DELETE' as FlagToDelete)
2 Applymap BSADvsBSID into BSIDTEMP to identify those records that were already cleared
3 Load BSIDTEMP <> from 'DELETE'; at this point we have the Open Documents
4 Create a QVD with BSIDTEMP information, this new QVD will be called BSIDvsBSAD (Using previous Join from fields (GJAHR & BELNR & BUZEI & ZUONR)as BSIDkey and 'DELETE' as FlagToDelete)
5 Applymap BSIDvsBSAD into BSADTEMP to identify those records that were re-open.
6 Load BSADTEMP <> from 'DELETE'; at this point we have the real cleared documents.
Regards,
Edgar.
I know this thread is pretty old, but I have a question and want to see if anyone can answer it. I have pulled BSID/BSAD and I need to identify only the transactions from BSID that are unapplied. Any ideas? My users want to see anything that as unapplied cash. Thanks
Hi
There is a field in BSID (REBZT - Follow-On Document T) that might help you; if the field is blank there is not any process about the document; but if the field has a value it means that the document has a partial payment.
Regards,
Edgar.