Skip to Content
0
Former Member
Mar 25, 2005 at 03:22 PM

ABAP Extractor

770 Views

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.

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.