Personal Folder

Get first day of current year:
SELECT     *
FROM         tbl
WHERE     (tbl.datefield = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))

Get last day of current year:
SELECT     *
FROM         tbl
WHERE     DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0)))

Get first day of current month:
SELECT     *
FROM         tbl
WHERE     select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Get first day of last month:
SELECT     *
FROM         tbl
WHERE     DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)

Get month part of a datetime field.
DATEPART(MONTH, A.AEDAT)

Get year part of a datetime field.
DATEPART(YEAR, A.AEDAT)

Cast field to char.
CAST(aField AS char(4))

Set 2 digit literal with trailing 0.
RIGHT('0' + RTRIM(MONTH(A.AEDAT)), 2)

Get Date Part Only of Datetime Field
CONVERT(varchar(8), GETDATE(), 112)

Get Time Part Only of Datetime Field
CONVERT(varchar(8), GETDATE(), 108)

Get Yesterday Date

DATEADD(DAY,DATEDIFF(DAY,'20000102',GETDATE()),'20000101')

And many else (from shanecooper.net) 
Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
Sunday of the Current Week
set DATEFIRST 1
select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())
First Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
First Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
Midnight for the Current Day
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
Last Day of Prior Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))
Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))
Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))
Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))
First Monday of the Month
select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()) ), 0)    

Many customized SAP reports run slowly in SAP environment. So we make a bapi to download a mature data and store it on our sql server. The program run as service on the server and run in daily basis. We also profide small application so user can update the data on request.

To install service manually

1. Go to directory in which installutill located (for me, in WINDOWS\Microsoft.NET\Framework\v2.0.50727).
2. Run InstallUtil.exe with your exe as parameter. Enter code below:
   

   installutil abc.exe

To uninstall service manually

• 
  Run InstallUtil.exe with your exe as a parameter. Enter code below:
  
  
  

  installutil /u abc.exe

Error in spool C call: spool overflow
t.code SPAD
menu path: Administration > Clean Spool

Im developing .net application in my local computer. Because we’ll develop it in team, I have to save it in share forlder so any else developer can access. Then come this error when I run the application.

Message=”Request for the permission of type ‘System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed.”

Its because .net dont trust developer location. Run caspol.exe (in directory C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727) to add developer trust site.
D:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>caspol.exe -pp off -m -ag 1.2 –url file:w:\* FullTrust

     Im on the way creating waste selling on intranet. User will input material that will be sold, do some level approval and create sales order in SAP. Im using BAPI_SALESORDER_CREATEFROMDAT2 to create sales order. Test using the function is ok (sales document number diplayed). But when I try to display the SO via VA03, error ‘SD document xxx is not in the database or has been archived’ displayed.
Function test displaying sales order successfully created.


To handle this, we  need to commit the transaction by running BAPI_TRANSACTION_COMMIT.

Sequence test on creating sales order and commit transaction:
On function module SE37, click menu Function Module > Test > Test sequence, input BAPI_SALESORDER_CREATEFROMDAT2 and BAPI_TRANSACTION_COMMIT, then run.

Example of abap program testing the function:

*&---------------------------------------------------------------------*
*& Report  ZCREATESALESDOC                                             *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*&                                                                     *
*&---------------------------------------------------------------------*
REPORT  ZCREATESALESDOC                         .
DATA: order_header_in LIKE standard table of bapisdhd1 with header line.
DATA: order_items_in TYPE STANDARD TABLE OF bapisditm WITH HEADER LINE.
DATA: order_schedules_in
TYPE STANDARD TABLE OF bapischdl WITH HEADER LINE.
DATA: order_partners TYPE STANDARD TABLE OF bapiparnr WITH HEADER LINE.
DATA: return TYPE STANDARD TABLE OF bapiret2 WITH HEADER LINE.
data: itemx like bapisditmx occurs 10 with header line.
order_header_in-doc_type = 'ZORL'.
order_header_in-sales_org = 'ID10'.
order_header_in-distr_chan = 'DC'.
order_header_in-division = '00'.
order_header_in-purch_date = sy-datum.
order_header_in-purch_no_c = 'BAPITEST'.
APPEND order_header_in.
order_partners-partn_role = 'AG'.
order_partners-partn_numb = '0000100080'.
APPEND order_partners.
order_partners-partn_role = 'WE'.
order_partners-partn_numb = '0000100082'.
APPEND order_partners.
*req deliv date, pricing date
*payment terms, incoterms
*add next line with more than one item
order_items_in-itm_number = '000010'.
order_items_in-material = 'LL0209AA'.
order_items_in-target_qty = '1000'.
order_items_in-target_qu = 'KG'.
order_items_in-sales_unit = 'KG'.
APPEND order_items_in.
order_schedules_in-itm_number = '000010'.
order_schedules_in-req_qty = '1000'.
APPEND order_schedules_in.
*Add following lines with more than one line
itemx-itm_number = '000010'.
itemx-updateflag = 'I'.
itemx-material = 'X'.
itemx-target_qty = 'X'.
append itemx.
*stop add more lines
REFRESH return.
*BREAK-POINT.
CALL FUNCTION 'SD_SALESDOCUMENT_CREATE'
EXPORTING
  sales_header_in = order_header_in
TABLES
  return = return
  sales_items_in = order_items_in
  sales_items_inx = itemx
  sales_partners = order_partners
  sales_schedules_in = order_schedules_in.
COMMIT WORK.
LOOP AT return.
WRITE: / return-message.
ENDLOOP.

     Something interesting found in this non valuated material. I’ve GR non val material 1700000015 for 15 EA. But in my customized report ZMMF001, the total stock is not updated. But in SAP standard report MB52, the quantity is updated. In report ZMMF001 I use account MBEW-LBKUM as a reference. While on MB52 it use MARD-LABST as reference. This show that non valuated material is not using this field.


MM03 > Accounting1, field Total Stock (MBEW-LBKUM)

MM03 > Plant stock, field Unsres.use stock (MARD-LABST)

One of procurement audit finding is, PO release has to be reset whether PO value increase or decrease. Old fashion way, PO release only reset if total value is increase only. Testing and googling shows that there is no way PO release reset if total value is decrease. So we choose freeze PO on its fully release state.
SPRO > IMG > Materials Mangement > Purchasing > Purchase Order > Release Procedure for Purchase Orders > Define Release Procedure for Purchase Orders > Release Indicator

But then problem occur. If user want to edit the PO, it has to be un-released first. And still PO cant be un-released if PO has been issued (error is ‘Document has already been outputted… ’). We still waiting the solution from SAP. Or we need to create new different procurement procedure for this. Or, another solution is using user-exit.