03-25-2005 3:22 PM
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.
03-28-2005 1:44 AM
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.
03-28-2005 1:44 AM
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.
03-28-2005 4:14 PM
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 ABUDAT AISDD AAUFPL ARUECK A~AUERU
B~ARBPL
CPLNKN CRUECK COBJNR CVORNR
DAUFNR DPOSNR DDAUAT DMATNR DDWERK DPSMNG D~WEMNG
DAMEIN DPROJN
E~AEDAT
FFTRMS FGLTRS FGSTRS FGSTRI FGETRI FGLTRI F~FTRMI
FDISPO FAUFNR FGSTRP FGLTRP F~GAMNG
G~MFRPN
HBESKZ HDISPO HDZEIT HRUEZT HTRANZ HBEARZ H~BASMG
I~MAKTX
Z~SERNR
into corresponding fields of table GT_PODETL
from AFRU AS A
inner join CRHD AS B on AARBID = BOBJID
inner join AFVC AS C on AAUFPL = CAUFPL
and AAPLZL = CAPLZL
inner join AFPO AS D on AAUFNR = DAUFNR
inner join AUFK AS E on AAUFNR = EAUFNR
inner join AFKO AS F on AAUFNR = FAUFNR
inner join MARA AS G on AMATNR = GMATNR
inner join MARC AS H on AMATNR = HMATNR
and ADWERK = HWERKS
inner join MAKT AS I on AMATNR = IMATNR
left outer join ZAFRU AS Z on ARUECK = ZRUECK
and ARMZHL = ZRMZHL
where ( F~FTRMI ne GVD_INITDATE
and D~XLOEK ne 1
and ( DDAUAT eq 'ZP01' or DDAUAT EQ 'ZP02' or
DDAUAT eq 'ZCNR' or DDAUAT EQ 'ZCNV' ) )
and ( E~AEDAT lt GVD_DATE
or ( EAEDAT eq GVD_DATE and EAEZEIT 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.
03-28-2005 6:49 PM
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
03-28-2005 6:50 PM
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 ABUDAT AISDD AAUFPL ARUECK A~AUERU
B~ARBPL
CPLNKN CRUECK COBJNR CVORNR
DAUFNR DPOSNR DDAUAT DMATNR DDWERK DPSMNG D~WEMNG
DAMEIN DPROJN
E~AEDAT
FFTRMS FGLTRS FGSTRS FGSTRI FGETRI FGLTRI F~FTRMI
FDISPO FAUFNR FGSTRP FGLTRP F~GAMNG
H~MFRPN
IBESKZ IDISPO IDZEIT IRUEZT ITRANZ IBEARZ I~BASMG
J~MAKTX
Z~SERNR
into corresponding fields of table GT_PODETL
from AFRU AS A
inner join CRHD AS B on AARBID = BOBJID
inner join AFVC AS C on AAUFPL = CAUFPL
and AAPLZL = CAPLZL
inner join AFPO AS D on AAUFNR = DAUFNR
inner join AUFK AS E on AAUFNR = EAUFNR
inner join AFKO AS F on AAUFNR = FAUFNR
inner join AFPO AS G on AAUFNR = GAUFNR
inner join MARA AS H on GMATNR = HMATNR
inner join MARC AS I on HMATNR = IMATNR
inner join MAKT AS J on IMATNR = JMATNR
left outer join ZAFRU AS Z on ARUECK = ZRUECK
and ARMZHL = ZRMZHL
where ( F~FTRMI ne GVD_INITDATE
and D~XLOEK ne 1
and ( DDAUAT eq 'ZP01' or DDAUAT EQ 'ZP02' or
DDAUAT eq 'ZCNR' or DDAUAT EQ 'ZCNV' ) )
and ( E~AEDAT lt GVD_DATE
or ( EAEDAT eq GVD_DATE and EAEZEIT le GVT_TIME ) ).
06-01-2005 2:39 PM
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