topic ABAP Extractor in Application Development and Automation Discussions

ABAP Extractor

Former Member — Fri, 25 Mar 2005 15:22:59 GMT

I was hoping someone could help me out. I need to create an ABAP extractor that pulls data from twelve R3 tables and loads it into a single table for transfer to BW. The data is Production Order data and it needs to be reported at the OPERATION level. I have created a simple ABAP "report" that collects the information but I am not sure how to convert it to a extraction program. I know that a select statement that does an "Inner Join" on these tables is likely necessary, and I understand how to join most of the tables in this statement, but I do not know how to join all of them (or if that would be the best way to do it). I have included the ABAP report code below so you can see the combination of information I need. Any help in formulating the code to properly join the tables and create an internal table would be GREATLY appreciated. Thank you. Dave Burrows

REPORT ZBW_PO_DRILL_DOWN NO STANDARD PAGE HEADING LINE-SIZE 650.

TABLES:

AFKO, "Order header data PP orders

AFVC, "Operation within an order

JEST, "Object status

TJ02T, "System status texts

AFPO, "Order item

AFRU, "Order completion confirmations

CRHD, "Work Center Header

AUAK, "Document Header for Settlement

MAKT, "Material Descriptions

MARC, "Plant data for material

T024D, "MRP Controllers

AUFK. "Order Master Data

selection screen

SELECTION-SCREEN BEGIN OF BLOCK b00 WITH FRAME TITLE text-b00.

SELECTION-SCREEN BEGIN OF BLOCK b02 WITH FRAME TITLE text-b01.

PARAMETERS: mrpcntlr LIKE MARC-DISPO OBLIGATORY DEFAULT '001'.

SELECTION-SCREEN END OF BLOCK b02.

SELECTION-SCREEN END OF BLOCK b00.

DATA: W_STATUS(15),

OP_STATUS(15),

JOB_STATUS(15),

W_OBJNR(14),

W_PRODQTY LIKE AFPO-WEMNG,

W_REMAINING_QTY,

W_CALC_PROD_TM LIKE MARC-DZEIT.

FIELD-GROUPS: HEADER.

SELECT * FROM AFKO WHERE

DISPO = MRPCNTLR

ORDER BY AUFNR.

CONCATENATE 'OR' AFKO-AUFNR INTO W_OBJNR.

W_OBJNR = AFKO-AUFNR.

SELECT SINGLE * FROM T024D WHERE DISPO = AFKO-DISPO.

SELECT SINGLE * FROM AUFK WHERE AUFNR = AFKO-AUFNR.

SELECT SINGLE * FROM AUAK WHERE OBJNR = W_OBJNR.

SELECT SINGLE * FROM AFPO WHERE AUFNR = AFKO-AUFNR.

IF AFPO-PSMNG >= AFPO-WEMNG.

W_REMAINING_QTY = AFPO-PSMNG - AFPO-WEMNG.

ELSE.

W_REMAINING_QTY = 0.

ENDIF.

SELECT SINGLE * FROM MARC

WHERE MATNR = AFPO-MATNR

AND WERKS = AFPO-DWERK.

IF MARC-DZEIT <= 0.

W_CALC_PROD_TM = MARC-RUEZT + MARC-TRANZ + MARC-BEARZ.

ELSE.

W_CALC_PROD_TM = MARC-DZEIT.

ENDIF.

SELECT SINGLE * FROM MAKT WHERE MATNR = AFPO-MATNR.

SELECT * FROM AFVC WHERE AUFPL = AFKO-AUFPL.

SELECT * FROM JEST WHERE

OBJNR = AFVC-OBJNR AND

INACT = SPACE.

ENDSELECT.

SELECT * FROM TJ02T WHERE ISTAT = JEST-STAT

AND SPRAS = 'E'.

ENDSELECT.

CLEAR W_PRODQTY.

SELECT * FROM AFRU WHERE RUECK = AFVC-RUECK AND

STOKZ NE 'X' AND

STZHL EQ '0'.

W_PRODQTY = W_PRODQTY + AFRU-LMNGA.

ENDSELECT.

SELECT SINGLE * FROM CRHD WHERE OBJID = AFVC-ARBID.

WRITE: /002 AFPO-DWERK, "Plant

008 AFPO-PROJN, "Program

028 AFPO-DAUAT, "Order Type

038 AFKO-DISPO, "MRP Controller

050 AFKO-AUFNR, "PO No.

060 AFPO-MATNR, "Material No.

080 MAKT-MAKTX, "Material Desc.

125 AFKO-GSTRP, "Order Start Date

137 AFKO-GLTRP, "Order End Date

150 JOB_STATUS, "Order Status Code

166 CRHD-ARBPL, "Work Center

175 AFVC-VORNR, "Operation

183(10) W_STATUS, "Work Status (not used)

195(12) W_PRODQTY, "Confirm Qty

208(12) AFKO-GAMNG, "Order Qty

222(12) AFPO-WEMNG, "Received Qty

237 JEST-STAT, "Order Status

251 TJ02T-TXT04, "Order Status Code Text

259 MARC-DZEIT, "Production Time

269 MARC-RUEZT, "Setup Time

279 MARC-TRANZ, "Interoperation Time

289 MARC-BEARZ, "Processing Time

299 W_CALC_PROD_TM, "Calculated Production Time

319 AFRU-ISDD, "Execution Date

335 AFRU-BUDAT, "Confirmation Date

350 W_REMAINING_QTY, "Remaining Qty

358 AFKO-FTRMS, "Release Date

370 AFKO-GSTRI, "Actual Start Date

383 AFKO-GETRI, "Confirmed Order Finish Date

395 AFKO-GLTRI, "Actual Finish Date

410 MARC-BESKZ, "Procurement Type

420 T024D-DSNAM, "Controller Name

440 AUFK-AEDAT, "Change Date

452 AFKO-FTRMI, "Actual Release Date

470 MARC-BASMG, "Base Qty

490 AFVC-PLNKN, "Total number of operations

500 AFRU-AUFPL, "Routing No.

512 AFVC-RUECK, "Confirmation No.

525 AFRU-RUECK, "Confirmation No.

537 AFRU-AUERU, "Final Confirmation Indicator

540 AFPO-PSMNG, "Order Qty

560 AFVC-OBJNR. "Object No.

ENDSELECT.

TOP-OF-PAGE.

ULINE.

WRITE: /002 'Plant',

008 'Program',

028 'Order_Typ',

038 'MRP_Cntlr',

050 ' PO_No',

060 'Material',

080 'Material_Desc',

125 'Order_Start',

137 'Order_End',

150 'Status',

166 'Work_Ctr',

175 'Op',

182 'Op_Status',

199 ' Confirm_Qty',

211 ' Order_Qty',

227 'Recd_Qty',

237 'Order_Stat',

249 'Stat_Cde',

259 'Prod_time',

269 'Setup_tm',

279 'Intop_tm',

289 'Proc_tm',

299 'Calc_tm',

319 'Exe_Dt',

335 'Cnf_Dt',

350 'Rem_Qty',

358 'Rel_Dt',

370 'Act_Strt_Dt',

383 'Cnf_Fin_Dt',

395 'Act_Fin_Dt',

407 'Proc_typ',

420 'Cntlr_Name',

440 'Chng_Dt',

452 'Act_Rel_Dt',

480 'Base_Qty',

490 'No_Ops',

500 'Routing_No',

512 'Cnf_No1',

525 'Cnf_No2',

537 'Cnf_Ind',

547 'Order_Qty2',

564 'Object_No'.

WRITE: /001 ' AFPO', "Plant

008 ' AFPO', "Program

028 ' AFPO', "Order Type

038 ' AFKO', "MRP Controller

050 ' AFKO', "PO No.

060 ' AFPO', "Material No.

080 ' MAKT', "Material Desc.

125 ' AFKO', "Order Start Date

137 ' AFKO', "Order End Date

150 ' ', "Order Status Code

166 ' CRHD', "Work Center

175 ' AFVC', "Operation No.

182(10) ' ', "Work Status (not used)

201(12) ' ', "Confirm Qty

214(12) ' AFKO', "Order Qty

226(12) ' AFPO', "Received Qty

237 ' JEST', "Order Status

251 ' TJ02T', "Order Status Code Text

259 ' MARC', "Production Time

269 ' MARC', "Setup Time

279 ' MARC', "Interoperation Time

289 ' MARC', "Processing Time

299 ' ', "Calculated Production Time

319 ' AFRU', "Execution Date

335 ' AFRU', "Confirmation Date

350 ' ', "Remaining Qty

358 ' AFKO', "Release Date

370 ' AFKO', "Actual Start Date

383 ' AFKO', "Confirmed Order Finish Date

395 ' AFKO', "Actual Finish Date

408 ' MARC', "Procurement Type

420 ' T024D', "Controller Name

440 ' AUFK', "Change Date

452 ' AFKO', "Actual Release Date

480 ' MARC', "Base Qty

490 ' AFVC', "Total number of operations

500 ' AFRU', "Routing No.

512 ' AFVC', "Confirmation No.

525 ' AFRU', "Confirmation No.

537 ' AFRU', "Final Confirmation Indicator

547 ' AFPO', "Order Qty

564 ' AFVC'. "Object No.

WRITE: /001 '(DWERK)', "Plant

008 '(PROJN)', "Program

028 '(DAUAT)', "Order Type

038 '(DISPO)', "MRP Controller

050 '(AUFNR)', "PO No.

060 '(MATNR)', "Material No.

080 '(MAKTX)', "Material Desc.

125 '(GSTRP)', "Order Start Date

137 '(GLTRP)', "Order End Date

150 'VAR', "Order Status Code

166 '(ARBPL)', "Work Center

175 '(VORNR)', "Operation No.

182(10) 'VAR', "Work Status (not used)

201(12) 'VAR', "Confirm Qty

214(12) '(GAMNG)', "Order Qty

226(12) '(WEMNG)', "Received Qty

237 '(STAT)', "Order Status

251 '(TXT04)', "Order Status Code Text

259 '(DZEIT)', "Production Time

269 '(RUEZT)', "Setup Time

279 '(TRANZ)', "Interoperation Time

289 '(BEARZ)', "Processing Time

299 'VAR', "Calculated Production Time

319 '(ISDD)', "Execution Date

335 '(BUDAT)', "Confirmation Date

350 'VAR', "Remaining Qty

358 '(FTRMS)', "Release Date

370 '(GSTRI)', "Actual Start Date

383 '(GETRI)', "Confirmed Order Finish Date

395 '(GLTRI)', "Actual Finish Date

408 '(BESKZ)', "Procurement Type

420 '(DSNAM)', "Controller Name

440 '(AEDAT)', "Change Date

452 '(FTRMI)', "Actual Release Date

480 '(BASMG)', "Base Qty

490 '(PLNKN)', "Total number of operations

500 '(AUFPL)', "Routing No.

512 '(RUECK1)', "Confirmation No.

525 '(RUECK2)', "Confirmation No.

537 '(AUERU)', "Final Confirmation Indicator

547 '(PSMNG)', "Order Qty

564 '(OBJNR)'. "Object No.

ULINE.

SKIP.

INITIALIZATION.

Re: ABAP Extractor

Former Member — Mon, 28 Mar 2005 00:44:02 GMT

hi david,

i think you should have asked this question in the BW forums - good thing i frequent both.

well, unless i understood you wrong, you need to collate data from different tables into just one, and extract from BW via this table. i'm assuming from your code, that you aren't exactly having a problem collating all this data together. it's just the extraction part your confused with.

well, just join everything together into a database table - that's what we could use for extraction. you may want to put update time/date too if you want delta technology to work. afterwards you should use tcode RSO2 and extract the data via a table (which you define inside).

i'd also suggest that you try to see if any of the business content extractors can provide you the data without you having to stage it prior to loading. you can do this via tcode SBIW (under install BC datasources or something like that).

hope this helps! and don't forget to give points. 😃

ryan.

Re: ABAP Extractor

Former Member — Mon, 28 Mar 2005 15:14:19 GMT

Ryan, Thank you for your response. Actually my problem really is how to get the data together into a single table. The code I originally listed doesn't do any "proper" joins and isn't suitable as-is for a Function Module. And as you said it would be good to add a date field to enable deltas. Below is my stab at writing the code to combine the data into a single table. I have commented out the lines that do not work. I am not sure how to combine this data in the same way as my original code but with proper joins. Any help would be appreciated.

REPORT ZBW_PO_DETAIL line-size 400 line-count 65.

TABLES: afru, crhd, afvc, afpo, aufk, afko, mara, marc, zafru.

TYPES: BEGIN OF GT_PODETAIL,

BUDAT LIKE AFRU-BUDAT, "Confirmation Date

ISDD LIKE AFRU-ISDD, "Execution Date

AUFPL LIKE AFRU-AUFPL, "Routing No.

RUECK2 LIKE AFRU-RUECK, "Confirmation No.

AUERU LIKE AFRU-AUERU, "Final Confirmation Indicator

ARBPL LIKE CRHD-ARBPL, "Work Center

PLNKN LIKE AFVC-PLNKN, "Total number of operations

RUECK1 LIKE AFVC-RUECK, "Confirmation No.

OBJNR LIKE AFVC-OBJNR, "Object No.

VORNR LIKE AFVC-VORNR, "Operation

AUFNR1 LIKE AFPO-AUFNR, "order #

POSNR LIKE AFPO-POSNR, "order item number

DAUAT LIKE AFPO-DAUAT, "order type

MATNR LIKE AFPO-MATNR, "material number of order

DWERK LIKE AFPO-DWERK, "plant

PSMNG LIKE AFPO-PSMNG, "order item quantity

WEMNG LIKE AFPO-WEMNG, "goods rec'd qty for order item

AMEIN LIKE AFPO-AMEIN, "UOM for in-house production

PROJN LIKE AFPO-PROJN, "Program

AEDAT LIKE AUFK-AEDAT, "Change Date

FTRMS LIKE AFKO-FTRMS, "scheduled release date

GLTRS LIKE AFKO-GLTRS, "scheduled finish date

GSTRS LIKE AFKO-GSTRS, "scheduled start date

GSTRI LIKE AFKO-GSTRI, "actual start date

GETRI LIKE AFKO-GETRI, "confirmed order finish date

GLTRI LIKE AFKO-GLTRI, "actual finish date

FTRMI LIKE AFKO-FTRMI, "actual release date

DISPO1 LIKE AFKO-DISPO, "MRP Controller

AUFNR2 LIKE AFKO-AUFNR, "PO No.

GSTRP LIKE AFKO-GSTRP, "Order Start Date

GLTRP LIKE AFKO-GLTRP, "Order End Date

GAMNG LIKE AFKO-GAMNG, "Order Qty

MFRPN LIKE MARA-MFRPN, "manufacturer part number

BESKZ LIKE MARC-BESKZ, "procurement type

DISPO2 LIKE MARC-DISPO, "MRP controller

DZEIT LIKE MARC-DZEIT, "Production Time

RUEZT LIKE MARC-RUEZT, "Setup Time

TRANZ LIKE MARC-TRANZ, "Interoperation Time

BEARZ LIKE MARC-BEARZ, "Processing Time

BASMG LIKE MARC-BASMG, "Base Qty

ZSERNR LIKE ZAFRU-SERNR, "Serial No.

END OF GT_PODETAIL.

*used to build PO Detail internal table

DATA: GT_PODETL TYPE STANDARD TABLE OF GT_PODETAIL WITH HEADER LINE.

DATA GVD_INITDATE LIKE SY-DATUM.

DATA: GVD_DATE LIKE AUFK-AEDAT, "change date

GVT_TIME LIKE AUFK-AEZEIT. "change time

*clear GT_PODETL.

select A_{BUDAT A}ISDD A_{AUFPL A}RUECK A~AUERU

B~ARBPL

C_{PLNKN C}RUECK C_{OBJNR C}VORNR

D_{AUFNR D}POSNR D_{DAUAT D}MATNR D_{DWERK D}PSMNG D~WEMNG

D_{AMEIN D}PROJN

E~AEDAT

F_{FTRMS F}GLTRS F_{GSTRS F}GSTRI F_{GETRI F}GLTRI F~FTRMI

F_{DISPO F}AUFNR F_{GSTRP F}GLTRP F~GAMNG

G~MFRPN
H_{BESKZ H}DISPO H_{DZEIT H}RUEZT H_{TRANZ H}BEARZ H~BASMG
I~MAKTX

Z~SERNR

into corresponding fields of table GT_PODETL

from AFRU AS A

inner join CRHD AS B on A_{ARBID = B}OBJID

inner join AFVC AS C on A_{AUFPL = C}AUFPL

and A_{APLZL = C}APLZL

inner join AFPO AS D on A_{AUFNR = D}AUFNR

inner join AUFK AS E on A_{AUFNR = E}AUFNR

inner join AFKO AS F on A_{AUFNR = F}AUFNR

inner join MARA AS G on A_{MATNR = G}MATNR
inner join MARC AS H on A_{MATNR = H}MATNR
and A_{DWERK = H}WERKS
inner join MAKT AS I on A_{MATNR = I}MATNR

left outer join ZAFRU AS Z on A_{RUECK = Z}RUECK

and A_{RMZHL = Z}RMZHL

where ( F~FTRMI ne GVD_INITDATE

and D~XLOEK ne 1

and ( D_{DAUAT eq 'ZP01' or D}DAUAT EQ 'ZP02' or

D_{DAUAT eq 'ZCNR' or D}DAUAT EQ 'ZCNV' ) )

and ( E~AEDAT lt GVD_DATE

or ( E_{AEDAT eq GVD_DATE and E}AEZEIT le GVT_TIME ) ).

refresh non-depot data in db table

delete from ZBW_PO_DETAIL.

insert ZBW_PO_DETAIL from table GT_PODETL.

commit work.

Re: ABAP Extractor

Former Member — Mon, 28 Mar 2005 17:49:36 GMT

Hi David,

I think you don't need to be so obsessive about inner joins. You have a nice report with you that shows your data as report output. All you need to do is -

1. remove top-of-page section completely

2. Instead of 'write' statements use 'move' to copy the content into internal table work area.

3. Append data into internal table.

4. Once all the data is collected in internal table, use this internal table to insert data into your 'single table'.

Alternatively you can insert data into your 'single table' directly in step 3 and forget about step 4.

Hope this helps.

Regards,

Sanjeev

Re: ABAP Extractor

Former Member — Mon, 28 Mar 2005 17:50:45 GMT

I believe I answered the question myself. I have not run this to validate it but it at least compiles. The problem was the joins. I will give it a shot and see if it works. Thank you all for your help!

select A_{BUDAT A}ISDD A_{AUFPL A}RUECK A~AUERU

B~ARBPL

C_{PLNKN C}RUECK C_{OBJNR C}VORNR

D_{AUFNR D}POSNR D_{DAUAT D}MATNR D_{DWERK D}PSMNG D~WEMNG

D_{AMEIN D}PROJN

E~AEDAT

F_{FTRMS F}GLTRS F_{GSTRS F}GSTRI F_{GETRI F}GLTRI F~FTRMI

F_{DISPO F}AUFNR F_{GSTRP F}GLTRP F~GAMNG

H~MFRPN

I_{BESKZ I}DISPO I_{DZEIT I}RUEZT I_{TRANZ I}BEARZ I~BASMG

J~MAKTX

Z~SERNR

into corresponding fields of table GT_PODETL

from AFRU AS A

inner join CRHD AS B on A_{ARBID = B}OBJID

inner join AFVC AS C on A_{AUFPL = C}AUFPL

and A_{APLZL = C}APLZL

inner join AFPO AS D on A_{AUFNR = D}AUFNR

inner join AUFK AS E on A_{AUFNR = E}AUFNR

inner join AFKO AS F on A_{AUFNR = F}AUFNR

inner join AFPO AS G on A_{AUFNR = G}AUFNR

inner join MARA AS H on G_{MATNR = H}MATNR

inner join MARC AS I on H_{MATNR = I}MATNR

inner join MAKT AS J on I_{MATNR = J}MATNR

left outer join ZAFRU AS Z on A_{RUECK = Z}RUECK

and A_{RMZHL = Z}RMZHL

where ( F~FTRMI ne GVD_INITDATE

and D~XLOEK ne 1

and ( D_{DAUAT eq 'ZP01' or D}DAUAT EQ 'ZP02' or

D_{DAUAT eq 'ZCNR' or D}DAUAT EQ 'ZCNV' ) )

and ( E~AEDAT lt GVD_DATE

or ( E_{AEDAT eq GVD_DATE and E}AEZEIT le GVT_TIME ) ).

Re: ABAP Extractor

Former Member — Wed, 01 Jun 2005 13:39:57 GMT

Can you please tell me what an ABAP extractor is? & Also what i sthe difference between an abap extractor and a generic extractor? Thank you