joehodge
asked on
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/Base DataEstPro cedure.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> </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> </td>--->
<td NOWRAP>#WeeklyOrdersReturn .REGION#</ td>
<td NOWRAP>#WeeklyOrdersReturn .END_MARKE T#</td>
<td NOWRAP>#WeeklyOrdersReturn .END_MARKE T_DESC#</t d>
<td NOWRAP>#WeeklyOrdersReturn .TRADE_CHA NNEL#</td>
<td NOWRAP>#WeeklyOrdersReturn .SOLD_TO_P ARTY#</td>
<td NOWRAP>#WeeklyOrdersReturn .PLANT#</t d>
<td NOWRAP>#WeeklyOrdersReturn .POINT_OF_ MF#</td>
<td NOWRAP>#WeeklyOrdersReturn .BRAND_FAM ILY#</td>
<td NOWRAP>#WeeklyOrdersReturn .BRAND_VAR IANT#</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_F ACTORY_COD E#</td>
<td valign="top"><INPUT TYPE="text" NAME="p_order_number" SIZE="7" MAXLENGTH="8" VALUE="#WeeklyOrdersReturn .ORDER_NUM BER#" class="TableText" readonly></td>
<td valign="top"><INPUT TYPE="text" NAME="p_line_number" SIZE="3" MAXLENGTH="3" VALUE="#WeeklyOrdersReturn .LINE_NUMB ER#" class="TableText" readonly></td>
<td NOWRAP>#dateformat(WeeklyO rdersRetur n.ORIGINAL _ORD_REQUE ST_DATE,'d d/mm/yyyy' )#</td>
<td valign="top"><INPUT TYPE="text" NAME="p_estimated_date" SIZE="10" VALUE="#dateformat(WeeklyO rdersRetur n.ESTIMATE D_PRODUCTI ON_DATE,'d d/mm/yyyy' )#" onchange="return isDate(p_estimated_date, 'Estimated Production Date')" class="TableText" ></td>
<td NOWRAP>#dateformat(WeeklyO rdersRetur n.ESTIMATE D_DATE_OF_ SAILING,'d d/mm/yyyy' )#</td>
<td NOWRAP>#dateformat(WeeklyO rdersRetur n.ACTUAL_D ATE_OF_SAI LING,'dd/m m/yyyy')#< /td>
<td><input type="submit" name="butSubmit" value="Save" id="ButtonEmphasized" nowrap class="buttonTextEmphasize d" 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_Process EstDates"
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_dat e"
>
<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/BaseDataEstD ate.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
I have the following form situated in a page called BaseDataEstDate.cfm
--------------------------
<form action="../components/Base
<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> </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> </td>--->
<td NOWRAP>#WeeklyOrdersReturn
<td NOWRAP>#WeeklyOrdersReturn
<td NOWRAP>#WeeklyOrdersReturn
<td NOWRAP>#WeeklyOrdersReturn
<td NOWRAP>#WeeklyOrdersReturn
<td NOWRAP>#WeeklyOrdersReturn
<td NOWRAP>#WeeklyOrdersReturn
<td NOWRAP>#WeeklyOrdersReturn
<td NOWRAP>#WeeklyOrdersReturn
<td valign="top"><INPUT TYPE="text" NAME="p_material" SIZE="10" MAXLENGTH="8" VALUE="#WeeklyOrdersReturn
<td NOWRAP>#WeeklyOrdersReturn
<td valign="top"><INPUT TYPE="text" NAME="p_order_number" SIZE="7" MAXLENGTH="8" VALUE="#WeeklyOrdersReturn
<td valign="top"><INPUT TYPE="text" NAME="p_line_number" SIZE="3" MAXLENGTH="3" VALUE="#WeeklyOrdersReturn
<td NOWRAP>#dateformat(WeeklyO
<td valign="top"><INPUT TYPE="text" NAME="p_estimated_date" SIZE="10" VALUE="#dateformat(WeeklyO
<td NOWRAP>#dateformat(WeeklyO
<td NOWRAP>#dateformat(WeeklyO
<td><input type="submit" name="butSubmit" value="Save" id="ButtonEmphasized" nowrap class="buttonTextEmphasize
</tr>
</cfoutput>
</cfloop>
</form>
--------------------------
this then calls the following coldfusion page called BaseDataEstProcedure.cfm
--------------------------
<cfstoredproc datasource="MANIF"
procedure="Pkg_DP_Process.
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_dat
>
<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/BaseDataEstD
--------------------------
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER