topic Re: Map the database table data to Purchase Order JSON format in Application Development and Automation Discussions

Map the database table data to Purchase Order JSON format

former_member139597 — Tue, 09 May 2023 04:53:40 GMT

Hi,

I have the below JSON format which needs to passed to Purchase Order API:

{

"CompanyCode": " ",

"PurchaseOrderType": " ",

"Supplier": " ",

"Language": " ",

"PurchasingOrganization": " ",

"PurchasingGroup": " ",

"DocumentCurrency": " ",

"to_PurchaseOrderItem": {

"results": [

{

"PurchaseOrderItem": " ",

"Plant": " ",

"OrderQuantity": " ",

"PurchaseOrderQuantityUnit": " ",

"OrderPriceUnit": " ",

"NetPriceAmount": " ",

"NetPriceQuantity": " ",

"Material": " ",

"ManufacturerMaterial": " ",

"ProductType": " "

}

]

}

I have a log table in SAP ECC system with the following fileds :

REGION NAME

SUPPLIERCONTACTID

LINE NUMBER

REGION NAME

ORDERQUANTITY

UNIT PRICE

And, I need to map the region name to Company code,

suppliercontactid to supplier

line number to purchaseorderitem

region name to plant

orderquantity to OrderQuantity

UnitPrice to NetPriceAmount

to the above json from the database table.

Could you please any one let me know how can I achieve this in ABAP?

Re: Map the database table data to Purchase Order JSON format

RaymondGiuseppi — Tue, 09 May 2023 05:14:07 GMT

Perform first some search on methods such as /ui2/cl_json=>serialize. There are already many threads, tutorials and blogs in the forum.

Re: Map the database table data to Purchase Order JSON format

former_member139597 — Tue, 09 May 2023 05:49:39 GMT

Hi Raymond, I have posted my question after going through many blogs. I didn't see the relavent blog to my question. My mapping was not working and not converted to the expected JSON format. Hence reaching out.

Kind regards

Ramesh

Re: Map the database table data to Purchase Order JSON format

Sandra_Rossi — Tue, 09 May 2023 07:02:53 GMT

Please use the COMMENT button for comments, asking for complements, adding details, replying to a comment or a proposed solution or to the OP question, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area.

If you want to target someone, if this person has posted an Answer, use the button COMMENT, if this person is the Original Poster of the question he/she will be automatically informed, otherwise copy/paste their hyperlinked name (e.g. yourself: rmasetty80) so that the person receives a warning (NB: @ doesn't work/but typing this character will suggest hyperlinked names).

Re: Map the database table data to Purchase Order JSON format

Sandra_Rossi — Tue, 09 May 2023 07:04:24 GMT

Also, post your code concerning /ui2/cl_json=>serialize so that you can understand what you did wrong...

NB: you can edit your question via Actions > Edit.

Re: Map the database table data to Purchase Order JSON format

former_member139597 — Tue, 09 May 2023 11:23:57 GMT

Hi, Here is the code : TYPES: BEGIN OF ty_purchase_order_item_results,

PurchaseOrderItem TYPE string,
Plant TYPE string,
OrderQuantity TYPE string,
PurchaseOrderQuantityUnit TYPE string,
OrderPriceUnit TYPE string,
NetPriceAmount TYPE string,
NetPriceQuantity TYPE string,
Material TYPE string,
ManufacturerMaterial TYPE string,
ProductType TYPE string,
END OF ty_purchase_order_item_results.

TYPES: BEGIN OF ty_purchase_order_item,
RESULTS TYPE ty_purchase_order_item_results,
END OF ty_purchase_order_item.

TYPES: BEGIN OF ty_purchase_order,
CompanyCode TYPE string,
PurchaseOrderType TYPE string,
Supplier TYPE string,
Language TYPE string,
PurchasingOrganization TYPE string,
PurchasingGroup TYPE string,
DocumentCurrency TYPE string,
to_PurchaseOrderItem TYPE ty_purchase_order_item,
END OF ty_purchase_order.

DATA :
lt_purchase_order_item_results type standard table of ty_purchase_order_item_results,
ls_purchase_order_item_results type ty_purchase_order_item_results,

lt_purchase_order_item type STANDARD TABLE OF ty_purchase_order_item,
ls_purchase_order_item type ty_purchase_order_item,

lt_purchase_order type STANDARD TABLE OF ty_purchase_order,
ls_purchase_order type ty_purchase_order.

* Declare internal table to hold ZEC_MEX_PO_LOG data
DATA: lt_zec_mex_po_log TYPE STANDARD TABLE OF zec_mex_po_log.

SELECT REGIONNAME
SUPPLIERCONTACTID
LINENUMBER
ORDEREDQUANTITY
UNITPRICE
INTO CORRESPONDING FIELDS OF TABLE lt_zec_mex_po_log
FROM zec_mex_po_log
where purchaseordernumber = 'G9623'.

Loop at lt_zec_mex_po_log into data(ls_zec_mex_po_log).
ls_purchase_order-CompanyCode = ls_zec_mex_po_log-regionname.
ls_purchase_order-PurchaseOrderType = 'ZMX'.
ls_purchase_order-Supplier = ls_zec_mex_po_log-SUPPLIERCONTACTID.
ls_purchase_order-Language = 'EN'.
ls_purchase_order-PurchasingOrganization = 'PO02'.
ls_purchase_order-PurchasingGroup = 'PGP'.
ls_purchase_order-DocumentCurrency = 'AUD'.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-PurchaseOrderItem = ls_zec_mex_po_log-LINENUMBER.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-Plant = ls_zec_mex_po_log-regionname.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-OrderQuantity = ls_zec_mex_po_log-ORDEREDQUANTITY.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-PurchaseOrderQuantityUnit = 'EA'.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-OrderPriceUnit = 'EA'.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-NetPriceAmount = ls_zec_mex_po_log-UNITPRICE.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-NetPriceQuantity = '1'.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-Material = 'TG11'.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-ManufacturerMaterial = 'TG11'.
ls_purchase_order-to_PurchaseOrderItem-RESULTS-ProductType = '1'.
Append ls_purchase_order to lt_purchase_order.
endloop.

*DATA(lv_json) = /ui2/cl_json=>serialize( data = lt_purchase_order ).

*WRITE : LV_JSON.

DATA(lv_json) = /ui2/cl_json=>serialize(
data = lt_purchase_order
compress = abap_true
pretty_name = /ui2/cl_json=>pretty_mode-camel_case ).

" Display JSON in ABAP
CALL TRANSFORMATION sjson2html SOURCE XML lv_json
RESULT XML DATA(lvc_html).
cl_abap_browser=>show_html(
title = 'Sample JSON'
html_string = cl_abap_codepage=>convert_from( lvc_html ) ).

DATA(lo_reader) = cl_sxml_string_reader=>create( cl_abap_codepage=>convert_to( lv_json ) ).
TRY.
lo_reader->next_node( ).
lo_reader->skip_node( ).
cl_demo_output=>display( 'JSON is valid' ).
CATCH cx_sxml_parse_error INTO DATA(lx_parse_error).
cl_demo_output=>display( lx_parse_error->get_text( ) ).
ENDTRY.

This program is producing the below JSON format :

[
  {
    "companycode": "ECG",
    "purchaseordertype": "ZMX",
    "supplier": "348",
    "language": "EN",
    "purchasingorganization": "PO02",
    "purchasinggroup": "PGP",
    "documentcurrency": "AUD",
    "toPurchaseorderitem": {
      "results": {
        "purchaseorderitem": "1",
        "plant": "ECG",
        "orderquantity": "1.0000000",
        "purchaseorderquantityunit": "EA",
        "orderpriceunit": "EA",
        "netpriceamount": "15000.0000",
        "netpricequantity": "1",
        "material": "TG11",
        "manufacturermaterial": "TG11",
        "producttype": "1"
      }
    }
  }

]

But the expected format :

{

"CompanyCode": " ",

"PurchaseOrderType": " ",

"Supplier": " ",

"Language": " ",

"PurchasingOrganization": " ",

"PurchasingGroup": " ",

"DocumentCurrency": " ",

"to_PurchaseOrderItem": {

"results": [

{

"PurchaseOrderItem": " ",

"Plant": " ",

"OrderQuantity": " ",

"PurchaseOrderQuantityUnit": " ",

"OrderPriceUnit": " ",

"NetPriceAmount": " ",

"NetPriceQuantity": " ",

"Material": " ",

"ManufacturerMaterial": " ",

"ProductType": " "

}

]

}

The system produced JSON format is different from the expected format. First difference : starting and ending with square brackets ([ ]). This is to be removed. As per the expectation, Results are expecting in array format. Could any one please help me on this?

Thanks in advance.

Re: Map the database table data to Purchase Order JSON format

Sandra_Rossi — Tue, 09 May 2023 11:54:01 GMT

Please use the COMMENT button for comments, asking for complements, adding details, replying to a comment or a proposed solution or to the OP question, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area.

To edit your question, use Actions > Edit.

Thank you.

Re: Map the database table data to Purchase Order JSON format

former_member139597 — Tue, 09 May 2023 23:11:46 GMT

Hi, Here is the code : TYPES: BEGIN OF ty_purchase_order_item_results,

This program is producing the below JSON format :

[
  {
    "companycode": "ECG",
    "purchaseordertype": "ZMX",
    "supplier": "348",
    "language": "EN",
    "purchasingorganization": "PO02",
    "purchasinggroup": "PGP",
    "documentcurrency": "AUD",
    "toPurchaseorderitem": {
      "results": {
        "purchaseorderitem": "1",
        "plant": "ECG",
        "orderquantity": "1.0000000",
        "purchaseorderquantityunit": "EA",
        "orderpriceunit": "EA",
        "netpriceamount": "15000.0000",
        "netpricequantity": "1",
        "material": "TG11",
        "manufacturermaterial": "TG11",
        "producttype": "1"
      }
    }
  }

]

But the expected format :

{

"CompanyCode": " ",

"PurchaseOrderType": " ",

"Supplier": " ",

"Language": " ",

"PurchasingOrganization": " ",

"PurchasingGroup": " ",

"DocumentCurrency": " ",

"to_PurchaseOrderItem": {

"results": [

{

"PurchaseOrderItem": " ",

"Plant": " ",

"OrderQuantity": " ",

"PurchaseOrderQuantityUnit": " ",

"OrderPriceUnit": " ",

"NetPriceAmount": " ",

"NetPriceQuantity": " ",

"Material": " ",

"ManufacturerMaterial": " ",

"ProductType": " "

}

]

}

Thanks in advance.

Re: Map the database table data to Purchase Order JSON format

kmoore007 — Mon, 17 Feb 2025 18:55:59 GMT

Did you find out how to remove the square brackets?