Start Routine - Delete Data w/ No Matching Record In Target

[former_member212897]

I'm loading a DSO with data from 2 other DSO's and need to figure out how to limit the data load from the 2nd DSO to just the records that are in the Target DSO.

Looking at the image below when I load data from the first DSO I'm moving 4K records to my Target DSO. I also have a filter on the DTP to only load data that has a GL Account Between a certian range.

Now I need to load data from the 2nd DSO but I only want to load the data that has a matching record in the target DSO. In my case the 2nd DSO contains 28K records but I only need from 4K of those records. Whats happening is that the 2nd DSO is loading all 28K records so I end up with data I don't need. The issue with my 2nd load is that it does not contain the GL Account number so I'm unable to filter this DSO's DTP to only select data with a GL Account Range.

This image is an image that shows teh keys of the Target DSO. DSO 1 and DSO 2 both contain these fields and i'm mapping to them.

So back to my origianl question, how am I able to restrict the data from the 2nd DSO to just the 4K reocrds that are currently existing? Maybe a start routine is not the best way to go about this.

Ideas? Suggestions?

Thanks

[ccc_ccc]

Hi Larry,

Please confirm below things.

1. DSO 1 and Target DSO has G/L No hence your able to restrict at DTP Level ?
2. DSO 2 and Target DSO doe sot have G/L no , still you want restrict 4k out of 28k, so my question is at what infoobject level your planning to restrict data from DSO 2 since ?

Please let me know this .

Thank you,

Nanda

[former_member212897]

Nanda, you are correct, DSO 1 has a GL Account and DSO does not. DSO 1 is the GL Line item DSO and DSO 2 is the Tax table for Accounting Documents which dosen't contain the GL Account. However, the 2 DSO's do have account document number, company code, document type and period in common.

Basically, I'm trying to create a new DSO that will only hold that data for a specific set of GL Accounts (DSO 1). DSO 2 contains the tax information along with some customer information that I need to update the existing records with. However, the way its set up i'm loading everything from the 2nd DSO since I dont' have a way to restrict the data.

[ccc_ccc]

Hi Larry,

Do like this.

In target DSO DTP(DSO2-->Target DSO), for "Account document number" write routine as like below.

1. Read account document number from DSO2 for G/L 400000 - 499999 (which already you know from DSO1) for company code, account doc type, fisvarnt, and fiscper
2. By implementing in DTP  with above logic you can get only account numbers which are related G/L account 400000 - 499999 in such way you may restrict , first check manually and if you feel comfortable then do code in DTP.

Thank you,

Nanda

[former_member217320]

Hi Larry,

when loading DSO 2 to Target DSO - either in Start or Expert Routine - perform a look-up on the DSO 1 to determine if the record in DSO 2 is applicable and filter / delete accordingly.

This is a common practice to harmonize data.

Let me know if this is helpful.

Regards,

Amber

[former_member212897]

Both of these appear to do exaclty the same thing but one is in the DTP where as the other is in the start routine. Of the two (and being pretty green and not knowing better) I think I prefer the start routine since I tend to over look the logic in the DTP. Regardless I believe they pretty much follow the same logic and i need a little help with the syntax.

DSO 2 Start Routine:

Select Distinct Accounting document number from the target dso. This will give me a list of valid records (accounting documents) that I need to load from the second dso.

Now I need to delete the records from the source package where the accounting document number is not in the list above. Im not sure if you can do a delete like this but in SQL I would do something like the following (Granted this is not valid syntax but hopefully you get the idea).

DELETE SOURCE_PACKAGE WHERE AC_DOC_NR Not In GT_VALID_RECORDS

So I have the following code in my start routine but this does not validate and not even sure you can do this. When I check the syntax it says that GT_VALID_RECS does not have the structure of a seclection table.

So Im not sure if I can do a deletion this way or will I have to create a loop and loop over each record and delete the records 1 by 1?

Thanks

[ccc_ccc]

Hi Larry,

Since in initial question you said Start routine is not preferable hence I suggested routine in DTP.

Anyway here is the idea to delete records from source package

1. Create ACDCNR as RANGE Table let called R_ACDCNR
2. Loop at GT_VALID_RECORDS and update R_ACDCNR-LOW = GT_VALID_RECORDS-AC_DC_NR.
3. Delete adjustment duplicates from R_ACDCNR.
4. DELETE SOURCE_PACKAGE WHERE AC_DC_NR NOT IN R_ACDCNR-LOW.

Please check this.

Thank you,

Nanda

[sander_vanwilligen]

Hi Larry,

I fear with both approaches that it can lead to technical problems. SQL select statements cannot deal with thousands of distinct values. Also FOR ALL ENTRIES without a distinct list of values can lead to memory problems.

Furthermore, your DELETE statement needs a selection table (i.e. like a range table).

Since the target DSO is rather small, I suggest to load the entire table into an internal table. Alternatively, you can filter on Company Code, Fiscal Year/Period and Fiscal Year Variant (i.e. leave out Accounting Document Number). But then you have to do a preliminary step to prepare an FAE (For All Entries) table to be used in the SELECT.

Then you can LOOP over the source package and check if the Accounting Document exists in the target DSO. If not, delete.

Coding could look as follows:

DATA:

  gt_fae TYPE _ty_T_SC_1.

REFRESH gt_fae.

gt_fae = source_package[].

SORT gt_fae BY comp_code fiscper fiscvarnt.

DELETE ADJACENT DUPLICATES FROM gt_fae COMPARING comp_code fiscper fiscvarnt.

SELECT DISTINCT ac_doc_no comp_code fiscper fiscvarnt

                            FROM /bic/azfi_o0100

                            INTO CORRESPONDING FIELDS OF TABLE gt_valid_records

                            FOR ALL ENTRIES IN gt_fae

                            WHERE comp_code = gt_fae-comp_code AND

                                         fiscper        = gt_fae-fiscper AND

                                         fiscvarnt      = gt_fae-fiscvarnt.

LOOP AT source_package ASSIGNING <source_fields>.

  READ TABLE gt_valid_records TRANSPORTING NO FIELDS

                       WITH KEY ac_doc_no  = <source_fields>-ac_doc_no

                                        comp_code = <source_fields>-comp_code

                                        fiscper        = <source_fields>-fiscper

                                        fiscvarnt      = <source_fields>-fiscvarnt.

  IF sy-subrc <> 0.

    DELETE <source_fields>.

  ENDIF.

ENDLOOP.

Best regards,

Sander