Last week I was talking to someone who asked suggestion to deal with the situation that I will explain here. I though the subject was interesting and would benefit others and decided to write this post. I hope you find it helpful.

This “solution” is only applicable to companies in Brasil and the company’s process has the following characteristics:

  • Company is using LE-TRA or TM
  • Company is issuing Shipment Cost Documents to pay freight
  • Company is not using SAP NFe 10.0 Inbound (or other NFe inbound tool)
  • Company is using MIRO to post CT-e (Freight Invoice / Freight Acknowledgement – In Portuguese: Conhecimento de Frete Eletronico)

We know that Carriers and LSPs must inform, in the CT-e that they issue, what is the source document that originated that “freight bill”, that means, what was the original Nota Fiscal that triggered the freight you are been charged for.

If you are using the Freight Settlement via Shipment Cost Document to trigger freight Purchase Orders, you may wonder how to better lookup for the right shipment cost document assigned to that freight based on the Nota Fiscal number informed in the CT-e instead referencing to other document numbers (that are not mandatory to be informed in the CTe, such as Shipment Number or Freight Purchase Order Number).

Here is an idea that you can use to make the Accounts Payable and Logistics Freight life’s easier… and by “idea” I mean, it is a starting point and may change slightly from company to company. It is your job to read, understand and adapt it!

There is an Enhancement Point you can add to the Include LMR1MI3W and limit it to company codes where LAND1 = BR only, since this functionality is intended only to improve the CT-e postings on MIRO, which is only required in Brazil.

The main idea is to have an extra button, in the example below “NF Freight PO Lookup”.

F01

Once this button is clicked it would ask the user to type the Nota Fiscal number shown in the CT-e (which is the Sales Nota Fiscal or other outgoing movement’s nota fiscal that originated that freight)

F02

All existing freight items / freight PO’s reference to the given Nota Fiscal would pop-up and the user can select the one that is related to that CT-e:

F03

F04

Here is where the Enhancement is implemented:

F05

In this example, to work the way it was shown above, it was added 4 methods to the implementation (which I will share the details of each of them more below).

  1. PO_FROMNOTAFISCAL is the method that will perform the lookup into the existing freight PO’s assigned to that freight.
  2. POPUP_NF will display the windows that request the user to type the nota fiscal number.
  3. BUILD_FIELDCAT_FOR_POPUP will display the results based on the Nota Fiscal number entered by the user.
  4. CHOOSE_PO will get the selected freight PO’s assigned to that nota fiscal and it will populate the invoice (MIRO), which can be done using a BDC call for example.

Remember, the logic to lookup for the data may change slightly from company to company, but this article should give enough ideas to get there.

F06

PO_FROM_NOTAFISCAL

METHOD PO_FROM_NOTAFISCAL.
DATA: LV_NOTA_FISCAL    TYPE J_1BNFNUM9,
LV_SERIES         TYPE J_1BNFDOC-SERIES,
LV_BILLING        TYPE VBELN,
LV_DELIVERY       TYPE VBELN,
LV_SHIPTO         TYPE KUNNR,
LV_SHIPMENT       TYPE TKNUM,
LV_SCD            TYPE VBELN,
LT_EBELN          TYPE STANDARD TABLE OF TY_EBELN,
LV_EBELN          TYPE TY_EBELN,
LV_REFKEY         TYPE J_1BREFKEY,
LV_REFTYP         TYPE J_1BREFTYP,
LV_LINES          TYPE I,
LT_WHERE          TYPE STRING_TABLE,
LT_SHIPMENT_RANGE TYPE RANGE OF TKNUM,
LT_SCD_RANGE      TYPE RANGE OF VBELN.

IF NOTA_FISCAL IS INITIAL.
POPUP_NF( CHANGING NOTA_FISCAL = LV_NOTA_FISCAL SERIES = LV_SERIES ).
ELSE.
LV_NOTA_FISCAL = NOTA_FISCAL.

CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_INPUT’
EXPORTING
INPUT  = LV_NOTA_FISCAL
IMPORTING
OUTPUT = LV_NOTA_FISCAL.
ENDIF.

CHECK LV_NOTA_FISCAL IS NOT INITIAL.

APPEND ‘B~NFENUM = @LV_NOTA_FISCAL’ TO LT_WHERE.
APPEND ‘AND B~BUKRS = @COMPANY_CODE’ TO LT_WHERE.

“Only use series if it is populated
IF LV_SERIES IS NOT INITIAL.
APPEND ‘AND B~SERIES = @LV_SERIES’ TO LT_WHERE.
ENDIF.

* get reference document for nota fiscal – should be a billing document
SELECT SINGLE REFKEY, REFTYP
FROM J_1BNFLIN AS A
INNER JOIN J_1BNFDOC AS B
ON A~DOCNUM = B~DOCNUM
INTO (@LV_REFKEY, @LV_REFTYP)
WHERE (LT_WHERE).

IF LV_REFTYP NE ‘BI’ OR LV_REFKEY IS INITIAL.
MESSAGE I000(YM) WITH TEXT-000.
EXIT.
ENDIF.

LV_BILLING = LV_REFKEY.
* find delivery from billing document
SELECT SINGLE VBELV
FROM VBFA
INTO @LV_DELIVERY
WHERE VBELN = @LV_BILLING
AND VBTYP_N = ‘M’                      “invoice
AND VBTYP_V = ‘J’.
IF LV_DELIVERY IS INITIAL.
MESSAGE I000(YM) WITH TEXT-001.
EXIT.
ENDIF.

* get shipto from delivery
CLEAR LV_SHIPTO.
SELECT SINGLE KUNNR
FROM LIKP
INTO @LV_SHIPTO
WHERE VBELN = @LV_DELIVERY.

* find shipments from delivery
SELECT ‘I’, ‘EQ’, VBELN
FROM VBFA
INTO TABLE @LT_SHIPMENT_RANGE
WHERE VBELV = @LV_DELIVERY
AND VBTYP_N = ‘8’
AND VBTYP_V = ‘J’.
IF LT_SHIPMENT_RANGE IS INITIAL.
MESSAGE I000(YM) WITH TEXT-001.
EXIT.
ENDIF.

*  find shipping cost documents
SELECT ‘I’, ‘EQ’, VBELN
FROM VTFA
INTO TABLE @LT_SCD_RANGE
WHERE VBELV IN @LT_SHIPMENT_RANGE
AND VBTYP_V = ‘8’
AND VBTYP_N = ‘a’.
IF LT_SCD_RANGE IS INITIAL.
MESSAGE I000(YM) WITH TEXT-001.
EXIT.
ENDIF.

* find po’s and related information
SELECT A~EBELN, B~LIFNR, C~NETWR, B~WAERS
INTO TABLE @LT_EBELN
FROM VFKP AS A
INNER JOIN EKKO AS B
ON A~EBELN = B~EBELN
INNER JOIN EKPO AS C
ON B~EBELN = C~EBELN
WHERE FKNUM IN @LT_SCD_RANGE
AND C~LOEKZ = ‘ ‘.

SORT LT_EBELN BY EBELN.

DELETE ADJACENT DUPLICATES FROM LT_EBELN.

LV_LINES = LINES( LT_EBELN ).

IF LV_LINES = 0.
MESSAGE I000(YM) WITH TEXT-002.
ELSEIF LV_LINES > 1.
CHOOSE_PO(
EXPORTING
SHIP_TO         = LV_SHIPTO
CHANGING
PURCHASE_ORDERS = LT_EBELN
PURCHASE_ORDER  = PURCHASE_ORDER
).
ELSE.
READ TABLE LT_EBELN INTO LV_EBELN INDEX 1.
PURCHASE_ORDER = LV_EBELN-EBELN.
ENDIF.
ENDMETHOD.

 

POPUP_NF

METHOD POPUP_NF.
DATA: LT_FIELDS TYPE STANDARD TABLE OF SVAL,
LS_FIELDS TYPE SVAL,
LV_RC     TYPE C.

“prompt for nota fiscal and series
APPEND VALUE #( TABNAME   = ‘J_1BNFDOC’ FIELDNAME = ‘NFENUM’ FIELD_OBL = ABAP_TRUE ) TO LT_FIELDS.
APPEND VALUE #( TABNAME   = ‘J_1BNFDOC’ FIELDNAME = ‘SERIES’ ) TO LT_FIELDS.

CALL FUNCTION ‘POPUP_GET_VALUES’
EXPORTING
NO_VALUE_CHECK = ‘X’
POPUP_TITLE    = TEXT-004
IMPORTING
RETURNCODE     = LV_RC
TABLES
FIELDS         = LT_FIELDS.

IF LV_RC = ‘A’.                  “cancelled
MESSAGE S000(YM) WITH TEXT-005.
EXIT.
ENDIF.

“Get Nota Fiscal
READ TABLE LT_FIELDS INTO LS_FIELDS INDEX 1.
NOTA_FISCAL = LS_FIELDS-VALUE.

“Get series
READ TABLE LT_FIELDS INTO LS_FIELDS INDEX 2.
SERIES = LS_FIELDS-VALUE.

CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_INPUT’
EXPORTING
INPUT  = NOTA_FISCAL
IMPORTING
OUTPUT = NOTA_FISCAL.
ENDMETHOD.

 

CHOOSE_PO

METHOD CHOOSE_PO.
DATA: LS_EBELN    TYPE TY_EBELN,
LS_SELFIELD TYPE SLIS_SELFIELD,
L_TITLE     TYPE STRING.

LOOP AT PURCHASE_ORDERS INTO LS_EBELN.
SELECT SINGLE A~NAME1 INTO LS_EBELN-NAME1
FROM ADRC AS A
JOIN LFA1 AS B
ON A~ADDRNUMBER = B~ADRNR
WHERE LIFNR = LS_EBELN-LIFNR.

IF SY-SUBRC = 0.
MODIFY PURCHASE_ORDERS FROM LS_EBELN.
ENDIF.

SELECT SINGLE A~NAME1 INTO LS_EBELN-SH_NAME
FROM ADRC AS A
JOIN KNA1 AS B
ON A~ADDRNUMBER = B~ADRNR
WHERE KUNNR = SHIP_TO.
IF SY-SUBRC = 0.
MODIFY PURCHASE_ORDERS FROM LS_EBELN.
ENDIF.
ENDLOOP.

BUILD_FIELDCAT_FOR_POPUP(
IMPORTING
FIELD_CATALOG = DATA(LT_FIELDCAT) ).

L_TITLE = TEXT-003.

CALL FUNCTION ‘REUSE_ALV_POPUP_TO_SELECT’
EXPORTING
I_TITLE               = L_TITLE
I_SCREEN_START_COLUMN = 1
I_SCREEN_START_LINE   = 1
I_SCREEN_END_COLUMN   = 100
I_SCREEN_END_LINE     = 10
I_ZEBRA               = ‘X’
I_TABNAME             = ‘LT_POPUP’
IT_FIELDCAT           = LT_FIELDCAT
IMPORTING
ES_SELFIELD           = LS_SELFIELD
TABLES
T_OUTTAB              = PURCHASE_ORDERS.
*       Read table with the unique index.
READ TABLE PURCHASE_ORDERS INTO LS_EBELN INDEX LS_SELFIELD-TABINDEX.
IF SY-SUBRC <> 0.
EXIT.
ENDIF.

PURCHASE_ORDER = LS_EBELN-EBELN.
ENDMETHOD.

 

BUILD_FIELDCAT_FOR_POPUP

METHOD BUILD_FIELDCAT_FOR_POPUP.
APPEND VALUE #( FIELDNAME = ‘EBELN’ COL_POS = ‘1’ SELTEXT_L = TEXT-006 SELTEXT_M = TEXT-006 SELTEXT_S = TEXT-006
REPTEXT_DDIC = TEXT-006 DATATYPE = ‘CHAR’ OUTPUTLEN = ’10’ ) TO FIELD_CATALOG.

APPEND VALUE #( FIELDNAME = ‘LIFNR’ COL_POS = ‘2’ SELTEXT_L = TEXT-007 SELTEXT_M = TEXT-007 SELTEXT_S = TEXT-007
REPTEXT_DDIC = TEXT-007 DATATYPE = ‘CHAR’ OUTPUTLEN = ’10’ ) TO FIELD_CATALOG.

APPEND VALUE #( FIELDNAME = ‘NAME1’ COL_POS = ‘3’ SELTEXT_L = TEXT-008 SELTEXT_M = TEXT-008 SELTEXT_S = TEXT-008
REPTEXT_DDIC = TEXT-008 DATATYPE = ‘CHAR’ OUTPUTLEN = ’40’ ) TO FIELD_CATALOG.

APPEND VALUE #( FIELDNAME = ‘SH_NAME’ COL_POS = ‘4’ SELTEXT_L = TEXT-009 SELTEXT_M = TEXT-009 SELTEXT_S = TEXT-009
REPTEXT_DDIC = TEXT-009 DATATYPE = ‘CHAR’ OUTPUTLEN = ’40’ ) TO FIELD_CATALOG.

APPEND VALUE #( FIELDNAME = ‘NETWR’ COL_POS = ‘5’ SELTEXT_L = TEXT-010 SELTEXT_M = TEXT-010 SELTEXT_S = TEXT-010
REPTEXT_DDIC = TEXT-010 DATATYPE = ‘CURR’ OUTPUTLEN = ’16’ ) TO FIELD_CATALOG.

APPEND VALUE #( FIELDNAME = ‘WAERS’ COL_POS = ‘6’ SELTEXT_L = ‘ ‘ SELTEXT_M = ‘ ‘ SELTEXT_S = ‘ ‘
REPTEXT_DDIC = ‘ ‘ DATATYPE = ‘CHAR’ OUTPUTLEN = ‘3’ ) TO FIELD_CATALOG.
ENDMETHOD.

 

Conclusion:

That’s it. That’s a simple way to allow the accounts payable team to use the information that is available in the CT-e to pull the right freight purchase order generated from a Shipment Cost Document without need to reach Logistics or Freight analysts to request the Shipment number, the freight PO and etc.

Business-Process-Outsourcing-is-the-life-raft_HD