Material Quantity Issued doesn't = Quantity Required after PO is received, Why?

Hi!

I don't think there's a problem with the on hand qty being 24.

I think what we should understand is,

A- why 5 units were automatically issued or

B- why 20 units were not automatically issued. 

According to your description it seems that, B is the problem for you. Maybe due to some reason some units are not automatically issued even though they're reserved. Or could be a bug. So can you think of any reason to support B? Also mention your app version. 

Thanks.

smelanson wrote:

I am working in Work Order and have noticed that if I create a Purchase Requisition for an Inventory item the quantity issued after the po is received isn’t the quantity required. 

 

Eg. 

Add Part number to Requisition tab of Work Order.  Quantity required = 25, quantity on hand = 4/ 

Material line is created for 25.

On Requisitions tab, RMB and select ‘Requisition to Order”

PO is created, approved and released

Register arrival = 25

A few minutes later, on the Material tab, the Quantity Issued changes to 5 even thought the Quantity on Hand = 24. 

There are 20 reserved, but I will have to manually issue the 20 in order for my Material Req line to show Quantity Issued = 25. 

 

 

Do you have a configuration (sounds like a background job/scheduled migration job as there is a delay) or a modification to automatically issue the parts? 

Hi,  

We are on Apps 10 and I have only been working with IFS for 6 months so I don’t have a lot of experience. 

Yes, I saw there is a background job running “ Auto Issue Material Requisitions for POxxxx….” and an Event Action ‘Auto_Issue_Mat_Req_Line”.

Both are questions that I’m not sure about:

A- why only 5 units were automatically issued 

AND

B- why 20 units were not automatically issued. 

If I received all 25, which would have meant that 29 were on hand, why didn’t all 25 get issued automatically?

Thanks

Shelley

Hi,

You had 4 on hand. Received 25, and 5 has been issued from it. So that, on hand would be 24. So yes the question is, why rest of the 20 parts were not issued. 

Have you noticed any of the related jobs stopped with any Warning or Error in Background job while this is been noticed?

Does this happen each time when you receive such a PO?

Thanks.  

Whoever created that event and background job would likely need to review the code to answer that. Or if it’s not sensitive, you can share the code here for review.

Hi, 

Here is the Event action that gets called.  

DECLARE
   attr_          VARCHAR2(32000);
   sql_msg_       VARCHAR2(32000);
   stmt_          VARCHAR2(32000);
   po_rec_        Purchase_Order_Line_Part_API.Public_Rec := Purchase_Order_Line_Part_Api.Get('&NEW:SOURCE_REF1', '&NEW:SOURCE_REF2', '&NEW:SOURCE_REF3');
BEGIN

      IF NOT ((po_rec_.requisition_no IS NOT NULL) AND (po_rec_.req_line IS NOT NULL) AND (po_rec_.req_release IS NOT NULL) AND (po_rec_.demand_code = 'WO') AND (po_rec_.rowtype LIKE 'PurchaseOrderLinePart%')) THEN
         RETURN;
      END IF;

      stmt_ :='DECLARE
                  po_rec_           Purchase_Order_Line_Part_API.Public_Rec := Purchase_Order_Line_Part_Api.Get(''&NEW:SOURCE_REF1'', ''&NEW:SOURCE_REF2'', ''&NEW:SOURCE_REF3'');
              act_qty_issue_    NUMBER;
      
               CURSOR get_demand_req_lines IS
                 

            BEGIN   
               IF ((po_rec_.requisition_no IS NOT NULL) AND (po_rec_.req_line IS NOT NULL) AND (po_rec_.req_release IS NOT NULL) AND (po_rec_.demand_code = ''WO'') AND (po_rec_.rowtype LIKE ''PurchaseOrderLinePart%'')) THEN
                 FOR rec_ IN get_demand_req_lines LOOP
                   act_qty_issue_ := 0;
                   Maint_Material_Req_Line_API.Make_Auto_Issue_Detail(act_qty_issue_, 
                                                         rec_.maint_material_order_no, 
                                                         rec_.line_item_no, 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         NULL, 
                                                         NULL, 
                                                         rec_.plan_line_no, 
                                                         NULL, 
                                                         '''', 
                                                         NULL);
                 END LOOP;
               END IF;
            END;';

      sql_msg_ := Message_SYS.Construct('RELEASE');
      Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);

      Client_SYS.Clear_Attr(attr_);
      Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
      Client_SYS.Add_To_Attr('MSG_', '', attr_);

      Transaction_SYS.Deferred_Call('Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_,
                                    Language_SYS.Translate_Constant('Event', 'AUTO_ISSUE_MAT_REQ: Auto issue material requisition for purchase order :P1 - :P2 - :P3', p1_ => '&NEW:SOURCE_REF1', p2_ => '&NEW:SOURCE_REF2', p3_ => '&NEW:SOURCE_REF3'));
 
END; 
 

Hi, 

I sent the Event Action, but it didn’t post.  Is there a better way to send this type of file?

thanks

Shelley

You should be able to attach the file. Have you tried that?

Ok see attached document.  

I’m including a print screen also so you can see the condition. 

smelanson wrote:

Ok see attached document.  

I’m including a print screen also so you can see the condition. 

 

Weirdly part of the cursor had disappeared in your export. 

It’s a bit tricky to investigate this without having access to your data an system. I’m assuming you did only one receipt, and the full quantity went straight into inventory (nothing to inspect). 

I’m not entirely sure what goes wrong, and I haven’t had time to dig into it too deep. I tried changing the code slightly which seems to work, however, I have not tested it extensively and I recommend that you talk to whoever wrote the original event action before deploying it. Please also note that I have not used the maint_material_req_line_cfv as I did not have that in my environment.

And please, test this in a test environment :)

DECLARE
   attr_    VARCHAR2(32000);
   sql_msg_ VARCHAR2(32000);
   stmt_    VARCHAR2(32000);
   po_rec_  Purchase_Order_Line_Part_API.Public_Rec := Purchase_Order_Line_Part_API.Get('&NEW:SOURCE_REF1', '&NEW:SOURCE_REF2', '&NEW:SOURCE_REF3');

BEGIN

   IF NOT ((po_rec_.requisition_no IS NOT NULL) AND (po_rec_.req_line IS NOT NULL) AND (po_rec_.req_release IS NOT NULL) AND (po_rec_.demand_code = 'WO') AND (po_rec_.rowtype LIKE 'PurchaseOrderLinePart%')) THEN
      RETURN;
   END IF;

   stmt_ := 'DECLARE
                  po_rec_           Purchase_Order_Line_Part_API.Public_Rec := Purchase_Order_Line_Part_Api.Get(''&NEW:SOURCE_REF1'', ''&NEW:SOURCE_REF2'', ''&NEW:SOURCE_REF3'');
              act_qty_issue_    NUMBER;

               CURSOR get_demand_req_lines IS
                SELECT maint_material_order_no,
                       line_no,
                       qty_assigned,
                       line_item_no,
                       plan_line_no
                FROM   maint_material_req_line
                WHERE  requisition_no = po_rec_.requisition_no
                AND    line_no = po_rec_.req_line
                AND    release_no = po_rec_.req_release
                AND    qty_assigned > 0
                AND    is_closed = 0;

            BEGIN   
               IF ((po_rec_.requisition_no IS NOT NULL) AND (po_rec_.req_line IS NOT NULL) AND (po_rec_.req_release IS NOT NULL) AND (po_rec_.demand_code = ''WO'') AND (po_rec_.rowtype LIKE ''PurchaseOrderLinePart%'')) THEN
                 FOR rec_ IN get_demand_req_lines LOOP
                   act_qty_issue_ := 0;

               Maint_Material_Req_Line_API.Make_Auto_Issue_Detail(act_qty_issue_, 
                                                         rec_.maint_material_order_no, 
                                                         rec_.line_item_no, 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                           NULL, 
                                                         --Added qty to issue
                                                         rec_.qty_assigned, 
                                                         NULL);
                 END LOOP;
               END IF;
            END;';

   sql_msg_ := Message_SYS.Construct('RELEASE');
   Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);

   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
   Client_SYS.Add_To_Attr('MSG_', '', attr_);

   Transaction_SYS.Deferred_Call('Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_, Language_SYS.Translate_Constant('Event', 'AUTO_ISSUE_MAT_REQ: Auto issue material requisition for purchase order :P1 - :P2 - :P3', p1_ => '&NEW:SOURCE_REF1', p2_ => '&NEW:SOURCE_REF2', p3_ => '&NEW:SOURCE_REF3'));

END;

Good morning, 

Yes, it was weird that the cursor didn’t paste into my file.  Here’s the cursor and it is a bit different than what you sent.

-- CURSOR get_demand_req_lines IS
               -- SELECT maint_material_order_no,
                --     line_no,
                --     qty_assigned,
                --     line_item_no,
                --     plan_line_no
              --  FROM   maint_material_req_line
              --  WHERE  requisition_no = po_rec_.requisition_no
              --  AND    line_no = po_rec_.req_line
              --  AND    release_no = po_rec_.req_release
              --  AND    qty_assigned > 0
              --  AND    is_closed = 0;

Also, I see the change you made and think that the number being issues is the material order line number, so I’m thinking whoever created this event action made a mistake and selected the wrong field. I spoke to my colleague and she didn’t create it and if it isn’t out of the box, then thinks it might have been  done by a consultant.

I am not a developer so I’m wondering why you have the 6 double “, and then only 1 NULL, where the text below has 2 NULL, fields and then the plan_line_no, which is probably causing the issue.  

I just want to understand is the placement of the qty_assigned, that you sent important and need to be after he first NULL,

Not sure if I’m making sense, if you’d like to have a Teams call, we can.  

                                                         rec_.maint_material_order_no, 
                                                         rec_.line_item_no, 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         '''', 
                                                         NULL, 
                                                         NULL,   
                                                         rec_.plan_line_no,   
                                                         NULL, 
                                                         '''', 
                                                         NULL);

Hi, 

I see the change you made and think that the quantities that have been issued are the same as the material order line no so I think that whomever created the event action must have used the wrong field.

I’m not a developer so I want to understand what you did.  Is the placement of the rec_.qty_assigned important.  You added it after the NULL, but in the event action that we have the rec_.plan_line_no, is after a second NULL,.  Is the number of fields between the comma’s important?  Maybe if you have time, we could have a Teams call?

I realized there is another error we’ve been seeing occasionally with regards to the AutoIssue event action but I can’t figure out exactly what the issue is.  

Occasionally we get background job errors with the following error message:

“The quantity issued may not be greater than the required quantity.

ORA-20110:MaintMaterialReqLine.ISSUEDMORETHANPLANED”  

I understand that this happens because the line in the script is pointing to the wrong field and actually tries to issue rec_.plan_line_no.  

What I’m having trouble understanding is why it doesn’t happen every time.

I just duplicated 4 material work order lines.  All 4 lines were inventory items.  2 of the 4 had a quantity of 1 ea on hand and the other two items had 0 on hand.  

I created a purchase requisition for all 4 lines and then rolled the requisition into purchase orders.

I approved and released the purchase orders and then did the ‘register arrival’ to receive the quantities. Based on the error mentioned above, I expected only 1 of the 4 lines to auto issue because the quantity required was higher than the Material Line Item No.  

Qty required 125, line number was 123.

However the auto issue of the 125 qty and  the 1st line, of the background jobs errored with the error message :

The quantity issued may not be greater than the required quantity.                    ORA-20110: MaintMaterialReqLine.ISSUEDMORETHANPLANED

Both of these lines are the ones that had a qty of 1 on hand already.  The two lines that had 0 on hand did auto issue.  

Can someone please help me figure out why?

Thanks