Request and Request Items from SAP

Maurie · ‎03-15-2017

We have a new requirement to bring in items from SAP orders (IT equipment only) to a Request and from that Request we will add the individual line items on the Purchase Order.

We are connecting through a JDBC scheduled import to a database view that brings in all the items (with PO Number)

i can create all the items in the RI table and all the individual Purchase Orders in the Request table, but connecting the two has been challenging.

If i do only the Request part then i dont get the identifiers of each individual line item which is part of the requirement. since i am capturing the PO Number on both forms, i thought i could build a business rule on the RI form to put the Request Number in the Case (or Request) field on the form and thus create the relationship between the two. however everytime i try to start the code, it appears that the two tables dont talk together until the record is actually connected which is what i am trying to do here.

Any suggestions would be very helpful.

Thanks

Michael Ritchie · ‎03-17-2017

OK no worries, actually thinking about it, I would suggest not using a transform script since its somewhere else you will need to remember to look. Instead click the Run script checkbox and paste in the code below. Please note my comments in the code to verify things.

var scReqID = "";

var scReq = new GlideRecord("sc_request");

scReq.addQuery("u_po_number", source.u_po_number);   //Verify the field name for PO Number in your import set table is called u_po_number, if not change it here.

scReq.query();

if (scReq.next()) {

       scReqID = scReq.sys_id;

} else {

       scReq.initialize();

       scReq.u_po_number = source.u_po_number; //Verify the field name for PO Number in your import set table is called u_po_number, if not change it here.   Also verify the PO number field in your sc_request table is called u_po_number if not change it here too.

     

       // Here you can set other attributes of the request like the requested_for, due_date, location, etc.

       //scReq.requested_for = "whatever";

       scReqID = scReq.insert();

}

target.parent = scReqID;

View solution in original post

Michael Ritchie · ‎03-15-2017

Can you describe a little more about the JDBC import and what its importing? is it one row of data that you are processing multiple times for the request and request items? Or multiple rows per PO?

Some initial thoughts...

You could create an onBefore script that does a lookup on the sc_request table looking for a request that matches the current row's PO. If it finds one then it sets the sc_req_item.request value to that SysID, if it doesn't then it will create one and set the sc_req_item.request field to that SysID.

Maurie · ‎03-15-2017

We are importing all line items individually with the po number that we coalesce against on the request table. The onbefore script sounds like a doable thing. I will try that and let you know.

Michael Ritchie · ‎03-16-2017

OK makes sense. I think the onBefore script should work for you just checking to see if it exists yet and if not create the request.

Maurie · ‎03-16-2017

ok heres my question on this. The sc_req_item table is where all the line items reside. so i may be looking at this backwards. i have been looking at creating the sc_request with a transform and then the sc_req_item with another transform. i only need to use the transform for the sc_req_item?

If i only create the sc_req_item with an onbefore script that looks for a matching on the PO Number on the sc_request table. if none is found then create one and put the sys_id on the sc_req_item.request field. if the sc_request already exists, is there an easy way to pull the sys_id from the identified sc_request to the sc_req_item.request field?