--------------------------------------------------------------------------------xqp 20180209 begin --find related records about this delivery order, traverse --A shipping number may consist of multiple Kanbans, forCurrinch(Selectt.kanban_id, t.plant_id, t.item_id, t.station _id, T.kanban_group_qty, T.ref_kb_dis_kid, T.kanba N_qty fromFy_ps_report_data TwhereInStr (Concat (Concat (',', T.ref_kb_dis_kid),','), concat (Concat (',', To_char (P_kid)),','))> 0) LoopifCurr.ref_kb_dis_kid=To_char (P_kid) Then --when the Kanban's shipping order ID number is equal to P_kid, the state is changed directlyLN_MESSAGE_ID:=Fy_ps_pck.update_ps_report_data (p_kanban_id=curr.kanban_id, P_kanban_status= 'D'); IFln_message_id<>Hcm_public_pck.g_success Then --RETURN ln_message_id;Dbms_output.put_line ('-------Error1--------'); END IF; Else --when not equal, the kanban generates multiple shipping orders, taking the quantity of deliveries generated by this item begin SelectT.apply_dis_qty intoLn_ps_qty fromFy_ps_line TwhereT.kid=P_kid andt.item_id=curr.item_id andt.plant_id=curr.plant_id; Exception whenOthers ThenLn_ps_qty:= 0; End; LN_MESSAGE_ID:=Fy_ps_pck.update_ps_report_data (p_kanban_id=curr.kanban_id, P_kanban_qty=Curr.kanban_qty-Ln_ps_qty); IFln_message_id<>Hcm_public_pck.g_success Then --RETURN ln_message_id;Dbms_output.put_line ('----Error2--------'); END IF; End if; EndLoop; End;-------------------------------------------------------------------------------------
Oracle for loop, string concatenation, find