Due to the nature of our business we end up with a lot of stranded open
drop ship orders in our ERP system. There is no process provided in the
base software to delete more than one order at a time. And in this case
it is deleting the order and closing the corresponding PO. We can
access the applications underlying DB via ODBC connection (Progress is
the DB). I put together this process to delete all open orders based on
customer number and cancel date. I will preface this that no matter
what we use Access / Crystal Reports / this code, everything runs really
slow through this connection and bogs down the server.
I would appreciate any comments on how I am using my CFC. I would
really like to hear from anyone or anyone who knows about Progress DB or
its programming language 4GL now called ABL.
I would probably be better off if I could set this up as a stored
procedure and just call it.
Thanks,
Mike Sumner
Here is the action page to handle the orders selected. The prior page
allows you to view the line detail and check mark that order for
deletion. I submit the form to this page.
Page Name: d_member_open_orders_action.cfm
Date: 005-02-07
Author: Mike Sumner
--->
Find out how many orders to loop through
<cfset i = ArrayLen(orders.FaxInvc)>
<html>
<body>
<!--- Set podet.LineItemSts to C --->
<cfdump var="#i#">
<cfdump var="#FORM#">
With this I can delete only open orders and not the POs if true
<cfif FORM.order eq false >
<!------<cfif reset EQ "">--->
<cfloop from="1" to="#i#" index="i" >
<cfif orders.FaxInvc[i] eq true >
<!--- Close the PO Header and PO Detail files - pass the PO Number
--->
<!--- Set podet.LineItemSts to C --->
<cfinvoke component="terms.CFC.delete"
method="closePOdet" >
<cfinvokeargument
name="OurPurchOrdNum" value="#orders.OurPurchOrdNum[i]#">
</cfinvoke>
<!--- Set pohed.POSts to C --->
<cfinvoke component="terms.CFC.delete"
method="closePOhed">
<cfinvokeargument
name="OurPurchOrdNum" value="#orders.OurPurchOrdNum[i]#">
</cfinvoke>
<!--- Delete ordet record --->
<cfinvoke component="terms.CFC.delete"
method="deleteORDet">
<cfinvokeargument
name="OurPurchOrdNum" value="#orders.OurPurchOrdNum[i]#">
<cfinvokeargument
name="OrdNum" value="#orders.OrdNum[i]#">
<cfinvokeargument
name="OrdGenNum" value="#orders.OrdGenNum[i]#">
</cfinvoke>
<!--- Delete ordhed record --->
<cfinvoke component="terms.CFC.delete"
method="deleteORHed">
<cfinvokeargument
name="OrdNum" value="#orders.OrdNum[i]#">
<cfinvokeargument
name="OrdGenNum" value="#orders.OrdGenNum[i]#">
</cfinvoke>
<!--- Delete the OHHE1 record --->
<cfinvoke component="terms.CFC.delete"
method="deleteOHHE1">
<cfinvokeargument
name="OrdNum" value="#orders.OrdNum[i]#">
<cfinvokeargument
name="OrdGenNum" value="#orders.OrdGenNum[i]#">
<cfinvokeargument
name="CustNum" value="#orders.CustNum[i]#">
</cfinvoke>
</cfif>
</cfloop>
<cfelse >
<cfloop from="1" to="#i#" index="i" >
<cfif orders.FaxInvc[i] eq true >
<!---
Delete ordet record --->
<cfinvoke component="terms.CFC.delete"
method="deleteORDet">
<cfinvokeargument
name="OurPurchOrdNum" value="#orders.OurPurchOrdNum[i]#">
<cfinvokeargument
name="OrdNum" value="#orders.OrdNum[i]#">
<cfinvokeargument
name="OrdGenNum" value="#orders.OrdGenNum[i]#">
</cfinvoke>
<!--- Delete ordhed record --->
<cfinvoke component="terms.CFC.delete"
method="deleteORHed">
<cfinvokeargument
name="OrdNum" value="#orders.OrdNum[i]#">
<cfinvokeargument
name="OrdGenNum" value="#orders.OrdGenNum[i]#">
</cfinvoke>
<!--- Delete the OHHE1 record --->
<cfinvoke component="terms.CFC.delete"
method="deleteOHHE1">
<cfinvokeargument
name="OrdNum" value="#orders.OrdNum[i]#">
<cfinvokeargument
name="OrdGenNum" value="#orders.OrdGenNum[i]#">
<cfinvokeargument
name="CustNum" value="#orders.CustNum[i]#">
</cfinvoke>
</cfif>
</cfloop>
</cfif>
<cflocation url="index.cfm">
HERE IS part of my CFC
<cfcomponent>
<cffunction name="OpenOrders" access="public"
returntype="query" output="true" >
<cfargument name="CustNum" type="numeric"
required="yes" default="2">
<cfargument name="CncDate" type="string"
default="2007/02/02">
<cfset var getOpenOrders = "" >
<cfquery name="getOpenOrders" datasource="commerce">
SELECT
PUB.orhed.CustNum,
PUB.orhed.EntDate,
PUB.orhed.CncDate,
PUB.orhed.OrdNum,
PUB.orhed.OrdGenNum,
PUB.orhed.TotOrdVal,
PUB.orhed.FaxInvc,
PUB.ordet.ItemNum,
PUB.ordet.OurPurchOrdNum
FROM
PUB.orhed
INNER JOIN
PUB.ordet ON PUB.orhed.CustNum = PUB.ordet.CustNum AND
PUB.orhed.OrdNum = PUB.ordet.OrdNum AND PUB.orhed.OrdGenNum =
PUB.ordet.OrdGenNum
WHERE PUB.orhed.CustNum = <cfqueryparam value="#Arguments.CustNum#"
cfsqltype="cf_sql_numeric">
AND
Pub.orhed.CncDate < '#ARGUMENTS.CncDate#'
</cfquery>
<cfreturn getOpenOrders>
</cffunction>
<!--- Set PODet to C --->
<cffunction name="closePOdet" output="true" access="public" >
<cfargument name="OurPurchOrdNum" type="string">
<cfquery name="cPOdet" datasource="terms" >
UPDATE PUB.podet
SET LineItemSts = 'C'
WHERE PurchOrdNum = '#ARGUMENTS.OurPurchOrdNum#'
</cfquery>
<cfreturn>
</cffunction>
<!--- Set POHed to C --->
<cffunction name="closePOhed" output="true" access="public" >
<cfargument name="OurPurchOrdNum" type="string">
<cfquery name="cPOhed" datasource="terms" >
UPDATE PUB.pohed
SET POSts = 'C'
WHERE PurchOrdNum = '#ARGUMENTS.OurPurchOrdNum#'
</cfquery>
<cfreturn>
</cffunction>
<!--- Delete ORdet --->
<cffunction name="deleteORDet" output="true" access="public" >
<cfargument name="OurPurchOrdNum" type="string">
<cfargument name="OrdNum" type="string">
<cfargument name="OrdGenNum" type="numeric">
<cfquery name="cPOhed" datasource="terms" >
Delete
From
Pub.ORdet
WHERE OrdNum = '#ARGUMENTS.OrdNum#'
AND
OrdGenNum = #ARGUMENTS.OrdGenNum#
AND
OurPurchOrdNum = '#ARGUMENTS.OurPurchOrdNum#'
</cfquery>
<cfreturn>
</cffunction>
Michael Sumner
Nations Best Sports
817-788-0034 ext 244
817-788-8542 fax
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/
www.teksystems.com/
