Update Multi Record Forms: Passing variable of type table into a PLSQL procedure?

Hi,

I have the following form situated in a page called BaseDataEstDate.cfm

---------------------------------------------------------------------------------------------------

<form action="../components/BaseDataEstProcedure.cfm" method="POST" name="FormAllItems" onClick="changeto(event, 'lightgreen')" >
                                    
                                    
                  <table   border="0" cellspacing="1" cellpadding="0" >
                        <tr>
                              <td class="TableTitle" >OTIF Base Data</td>
                        </tr>
                  </table>

                  <table  class="TableBorder"  width="1500"  border="0" cellspacing="1" cellpadding="0">
                        <tr class="TableColumnHeader">
                                     <!---<td NOWRAP align="center" >Hide</td>--->
                                    <td NOWRAP align="center" >Region</td>
                                    <td NOWRAP align="center" >EM</td>
                                    <td NOWRAP align="center" >EM Desc</td>
                                    <td NOWRAP align="center" >TC</td>
                                    <td NOWRAP>Sold-To</td>
                                    <td NOWRAP>Plant</td>
                                    <td NOWRAP>POM</td>
                                    <td NOWRAP>Brand Family</td>
                                    <td NOWRAP>Brand Variant</td>
                                    <td NOWRAP>Material</td>
                                    <td NOWRAP>Primary Factory Code</td>
                                    <td NOWRAP>Order</td>
                                    <td NOWRAP>Line</td>
                                    <td NOWRAP>Orig. Ord Request</td>
                                    <td NOWRAP>Est. Date of Prod.</td>
                                    <td NOWRAP>Est. date of Sailing</td>
                                    <td NOWRAP>Act. Date of Sailing</td>
                                    <td>&nbsp;</td>
                        </tr>
                  <cfset lv_Num =0>
                  <cfloop query="WeeklyOrdersReturn">
                  <cfoutput>
                  
                                <cfset lv_Num = lv_Num +1>
                                 <cfif  (lv_Num MOD 2) eq 0>
                                      <cfset lv_row = "TableRow0">
                              <cfelse>
                                      <cfset lv_row = "TableRow1">
                                </cfif>
                  
                              <cfset lv_NumVal = lv_Num MOD 2>



                        <tr class="#lv_row#">
                              <!---<td>&nbsp;</td>--->
                                    <td NOWRAP>#WeeklyOrdersReturn.REGION#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.END_MARKET#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.END_MARKET_DESC#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.TRADE_CHANNEL#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.SOLD_TO_PARTY#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.PLANT#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.POINT_OF_MF#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.BRAND_FAMILY#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.BRAND_VARIANT#</td>
                                    <td valign="top"><INPUT TYPE="text" NAME="p_material"             SIZE="10"       MAXLENGTH="8" VALUE="#WeeklyOrdersReturn.MATERIAL#" class="TableText"  readonly></td>
                                    <td NOWRAP>#WeeklyOrdersReturn.PRIMARY_FACTORY_CODE#</td>
                                    <td valign="top"><INPUT TYPE="text" NAME="p_order_number"       SIZE="7"       MAXLENGTH="8" VALUE="#WeeklyOrdersReturn.ORDER_NUMBER#" class="TableText"  readonly></td>
                                    <td valign="top"><INPUT TYPE="text" NAME="p_line_number"       SIZE="3"       MAXLENGTH="3" VALUE="#WeeklyOrdersReturn.LINE_NUMBER#"  class="TableText" readonly></td>                                    
                                    <td NOWRAP>#dateformat(WeeklyOrdersReturn.ORIGINAL_ORD_REQUEST_DATE,'dd/mm/yyyy')#</td>
                                    <td valign="top"><INPUT TYPE="text" NAME="p_estimated_date" SIZE="10"  VALUE="#dateformat(WeeklyOrdersReturn.ESTIMATED_PRODUCTION_DATE,'dd/mm/yyyy')#" onchange="return isDate(p_estimated_date, 'Estimated Production Date')"  class="TableText" ></td>                                                                                                                                                            
                                    <td NOWRAP>#dateformat(WeeklyOrdersReturn.ESTIMATED_DATE_OF_SAILING,'dd/mm/yyyy')#</td>
                                    <td NOWRAP>#dateformat(WeeklyOrdersReturn.ACTUAL_DATE_OF_SAILING,'dd/mm/yyyy')#</td>
                                                                                                                        
                                    <td><input type="submit" name="butSubmit" value="Save" id="ButtonEmphasized" nowrap class="buttonTextEmphasized" tabindex="0"></td>
                                          
                                                            </tr>
                              
                </cfoutput>
                  </cfloop>
</form>

---------------------------------------------------------------------------------------------------

this then calls the following coldfusion page called BaseDataEstProcedure.cfm



---------------------------------------------------------------------------------------------------
<cfstoredproc datasource="MANIF"
                              procedure="Pkg_DP_Process.Pr_ProcessEstDates"
                              debug>
                                                 
                  <cfprocparam type="In"
                        cfsqltype=cf_sql_varchar
                        value="#p_order_number#"
                        dbvarname="p_order_number"
                        >
                        
                  <cfprocparam type="In"
                        cfsqltype=cf_sql_varchar
                        value="#p_line_number#"
                        dbvarname="p_line_number"
                        >

                  <cfprocparam type="In"
                        cfsqltype=cf_sql_date
                        value="#p_estimated_date#"
                        dbvarname="p_estimated_date"
                        >
                  
                  <cfprocparam type="In"
                        cfsqltype=cf_sql_varchar
                        value="#p_material#"
                        dbvarname="p_material"
                        >

                  <cfprocparam type="In"
                        cfsqltype=cf_sql_varchar
                        value="NULL"
                        dbvarname="butSubmit"
                        >
                                          
            </cfstoredproc>
            
      <cflocation url="../pages/BaseDataEstDate.cfm">
            

---------------------------------------------------------------------------------------------------

the plsql package is as follows:
spec:

   TYPE order_number_ids        IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE line_number_ids     IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE reason_code_ids     IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE reason_text_ids    IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE material_ids        IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE estimated_date_ids      IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;



PROCEDURE Pr_ProcessEstDates                     (p_order_number order_number_ids,
                                                   p_line_number  line_number_ids,
                                                     p_reason_code      reason_code_ids,
                                                     p_reason_text reason_text_ids,
                                                         p_material material_ids,
                                                       p_estimated_date estimated_date_ids,
                                                       p_FormNum  NUMBER DEFAULT NULL,
                                                       butSubmit  VARCHAR2 DEFAULT NULL
                                                       )
IS


BEGIN
      
      IF butSubmit IS NOT NULL THEN
      
         FOR i IN p_order_number.FIRST .. p_order_number.LAST LOOP
             
               UPDATE OTIF_DP
               SET  ESTIMATED_PRODUCTION_DATE  =   p_estimated_date(i)
               WHERE p_order_number(i) = ORDER_NUMBER
               AND   p_line_number(i) = LINE_NUMBER;
      
         END LOOP;
        
         COMMIT;
        
            
      END IF;
      

EXCEPTION
WHEN OTHERS THEN
null;
END;

----------------------------------------------------------------------------------------------------------------------------

As you can see, the BaseDataEstProcedure.cfm was originally set up to update one form/record at a time, I now want to update multiple rows with 1 save.

My question is:

Is this the best way to go about this? If it is, do I just change the
joehodgeAsked:
Who is Participating?
 
dgrafxCommented:
yes
0
 
joehodgeAuthor Commented:
cfsqltype to the coldfusion equivalent of table?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.