Request and Request Items from SAP

[Maurie]

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]

I like to describe the Request (sc_request) as a container or shopping cart.   Not a lot really happens at that level, but it provides the ability to link a bunch of items together.

So I would suggest you just do a 1 to 1 mapping of your PO Lines to Request Items, remove the transform map for sc_request.   Then as the request items are created, check to see if there is a parent request with your PO already and if not create it, if so use it.  

[Maurie]

ok i havent done this by myself yet and this is my first try at a transform script.

can you please take a look -- i know i am missing something since it doesnt add anything in the case field

//This will verify that there is a request with the PO Number and if not insert a record with that po number.  

//Either way, the sys_id of the sc_request record with the same po number will be inserted to the case field.

{var source = new GlideRecord ("sc_req_item");

var target = new GlideRecord("sc_request");

var reqpo = target.u_po_number;

var caseno = sc_req_item;

      fieldMap.reqpo("map.u_po_number", "u_po_number");

  fieldMap.request("map.caseno", "sys_id");

      fieldMap.query();

}

[Michael Ritchie · Accepted solution]

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;