Logic of split planned order

The splitting of planned order is executed by following logic:

Validate the availability of the columns in the TABLE_NAME as follows:

1.0 Validate existence of below columns for specified for TABLE_NAME
              plan_category
              scp_order_number
              scp_order_sequence_number
              planned_order_qty
              scp_order_status
              planned_need_time

  1.1 when plan_category ="Production",
    1.1a. Use ITEM_COLUMN_NAME and LOCATION_COLUMN_NAME columns from TABLE_NAME and join with SC_V_EXT_ITEM_SOURCING
     by using item_id = ITEM_COLUMN_NAME, location_id = LOCATION_COLUMN_NAME and planned_need_time >= effective_from_date and <= effective_to_date and Select xs_sourcing_max_order_qty exists else select sourcing_max_order_qty exists as maximum order quantity else move to step 1.1b
    1.1b. Use ITEM_COLUMN_NAME and LOCATION_COLUMN_NAME columns from TABLE_NAME and join with SC_V_EXT_ITEM_LOCATION
     by using item_id = ITEM_COLUMN_NAME and location_id = LOCATION_COLUMN_NAME and Select xs_inv_max_order_qty exists else select inv_max_order_qty exists as maximum order quantity else consider maximum order quantity as null.

   1.2 when plan_category ="Transfer",
      1.2a. If SOURCE_LOCATION_COLUMN_NAME is not specified move to step 1.2b else Use 
       SOURCE_LOCATION_COLUMN_NAME, ITEM_COLUMN_NAME and LOCATION_COLUMN_NAME columns from TABLE_NAME and join with SC_V_EXT_ITEM_SOURCING
       by using item_id = ITEM_COLUMN_NAME , location_id = LOCATION_COLUMN_NAME,
       source_location_id = SOURCE_LOCATION_COLUMN_NAME and planned_need_time >= effective_from_date and <= effective_to_date and Select xs_sourcing_max_order_qty exists else select sourcing_max_order_qty exists as maximum order quantity else move to step 1.2b
     1.2b. Use ITEM_COLUMN_NAME and LOCATION_COLUMN_NAME columns from TABLE_NAME and join with SC_V_EXT_ITEM_LOCATION by using item_id = ITEM_COLUMN_NAME and location_id = LOCATION_COLUMN_NAME and Select xs_inv_max_order_qty exists else select inv_max_order_qty exists as maximum order quantity else consider maximum order quantity as null.

   1.3 when plan_category ="Purchase",
      1.3a. If SOURCE_SUPPLIER_COLUMN_NAME is not specified move to step 1.3b else Use
      SOURCE_SUPPLIER_COLUMN_NAME, ITEM_COLUMN_NAME and LOCATION_COLUMN_NAME columns from TABLE_NAME and join with SC_V_EXT_ITEM_SOURCING by using item_id = ITEM_COLUMN_NAME,
      location_id = LOCATION_COLUMN_NAME, source_supplier_id = SOURCE_SUPPLIER_COLUMN_NAME and planned_need_time >= effective_from_date and <= effective_to_date and Select xs_sourcing_max_order_qty exists else select sourcing_max_order_qty exists as maximum order quantity else move to step 1.3b
      1.3b. Use ITEM_COLUMN_NAME and LOCATION_COLUMN_NAME columns from TABLE_NAME and join with SC_V_EXT_ITEM_LOCATION by using item_id = ITEM_COLUMN_NAME and location_id = LOCATION_COLUMN_NAME and Select xs_inv_max_order_qty exists else select inv_max_order_qty exists as maximum order quantity else consider maximum order quantity as null.

If the maximum order quantity is null or planned_order_qty is less than or equal to the specified maximum order quantity, the current planned order is not split. The split order process proceeds with the next planned order. If planned_order_qty is greater than the selected maximum order quantity, the planned order is split as follows:

2.1. Update planned_order_qty with maximum order quantity for current planned order  
   2.2. Insert new order record in the table by selecting data as below and repeat this step till planned_ordered_qty is less then or equal to maximum order quantity or when number of order splits are equal to SCP_MAX_SPLIT_NUMBER_PER_ORDER.
   If any plan order is split more then SCP_MAX_SPLIT_NUMBER_PER_ORDER, then throw error stating below error message 
    Number of allowed splits per order (1000) is exceeded for order  (plan_value_id), correct master data for Item (item_id) @ Location (location_id). Max number can also be increased using configuration parameter SCP_MAX_SPLIT_NUMBER_PER_ORDER. Rolling back transaction

         - starting from 0001 increment by 1 for each split value as scp_order_sequence_number
         - CONCAT(scp_order_number, "_", scp_order_sequence_number) as scp_order_number
         - Remaining planned_order_qty (planned_order_qty - already split order quantity ) as 
              planned_order_qty
         - copy all other columns as it is except audit columns like created_by, created_time 
         - All Audit columns should be updated with program name and execution time respectively