groovy jdbc stored procedure return resultset

import groovy.sql.Sql
import java.sql.*
import java.io.*
import oracle.jdbc.driver.*
import org.compiere.util.Env
import org.compiere.util.DB
int client=Env.getContextAsInt(A_Ctx, "AD_Client_ID")
int org=Env.getContextAsInt(A_Ctx, "AD_Org_ID")
int uid=Env.getContextAsInt(A_Ctx, "#AD_User_ID")
def now= System.currentTimeMillis()
//  sql = Sql.newInstance( 'jdbc:jtds:sqlserver://serverName/dbName-CLASS;domain=domainName', 'username',  'password', 'net.sourceforge.jtds.jdbc.Driver' )
sql = Sql.newInstance("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","adempiere", "adempiere","oracle.jdbc.driver.OracleDriver")
// sql.eachRow( 'SELECT Value, Name FROM AD_Rule' ) { println "$it.Value -- ${it.Name} --" }
// row = sql.firstRow('SELECT Value, Name FROM AD_Rule')
// println "Row: Value= ${row.Value} and Name = ${row.Name}"
ptable= sql.dataSet("AD_Rule_T")
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = null;
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","adempiere", "adempiere");
CallableStatement call = conn.prepareCall ("{ ? = call 讀取規則設定.AD_Rule_Listing (?)}");
call.registerOutParameter (1, OracleTypes.CURSOR);
call.setString (2, "%");
call.execute ();
ResultSet rset = (ResultSet)call.getObject (1);
while (rset.next ()){
vname=rset.getString ("Name");
vvalue=rset.getString ("Value");
sql.query("SELECT nextidBYNAMEfunc('AD_Rule', 'N') as id , Generate_UUID() as uu FROM DUAL ")
{ResultSet rsuu -> if (rsuu.next()) { id = rsuu.getInt('id'); uu = rsuu.getString('uu'); }}
ptable.add(ad_client_id:client, ad_org_id:org,  isactive: 'Y', createdby:uid,  updatedby:uid, AD_Rule_ID:id,  AD_Rule_UU:uu, Value:vvalue, Name:vname)
}
rset.close();
call.close();
conn.close();
return "成功..."

Oracle ASCP Demand Supply 的轉移到 iDempiere 實作

Oracle ASCP Demand Supply 的轉移到 iDempiere 實作
第一輪   主料  取用
第二輪   有組件限制替代料  取用
第三輪   無組件限制替代料  取用


是 每一輪是一個 Cursor  !!!
是 總共只有一輪只有一個 Cursor  !!!
 ==>依據可供料日期順序取用 !!!!

這才對 !!!!!!!!!!!!!!!!!!


CURSOR C_Supply_2 (x_Organization_ID NUMBER, x_Assembly_Item_ID NUMBER, x_Inventory_Item_ID   NUMBER)IS
                  
   SELECT a.LOS_MRP_SUPPLY_SEQ,a.COMPILE_DESIGNATOR,a.SOURCE_TYPE,a.ORGANIZATION_ID,a.INVENTORY_ITEM_ID,a.ITEM_NAME,a.ITEM_TYPE,
       -- ITEM_PACKAGE,ITEM_PACKAGE_DESC,ITEM_CLASS_DESC,
          SUPPLY_DATE,QTY,
          NVL((SELECT SUM(NVL(b.Allocated_Qty,0)) FROM LOS_MRP_ALLOCATED_1 b WHERE a.LOS_MRP_SUPPLY_SEQ=b.LOS_MRP_SUPPLY_SEQ),0) AS AllocatedQty1,
          NVL((SELECT SUM(NVL(b.Allocated_Qty,0)) FROM LOS_MRP_ALLOCATED_3 b WHERE a.LOS_MRP_SUPPLY_SEQ=b.LOS_MRP_SUPPLY_SEQ),0) AS AllocatedQty3
      FROM LOS_MRP_SUPPLY a
INNER JOIN MRP_Designators p  ON p.Compile_Designator = a.Compile_Designator AND p.Attribute1 = 'Y'
    WHERE       (a.Organization_ID =x_Organization_ID AND a.Inventory_Item_ID  =x_Inventory_Item_ID)
      OR EXISTS (SELECT 1  --Organization_ID,Assembly_Item_ID,Component_Item_ID,S_Component_Item_ID
                            --bh.Organization_ID,,bh.Assembly_Item_ID
                            --bc.Component_Item_ID
                            --bc1.Component_Item_ID   AS s_Component_Item_ID
                   FROM BOM_Components_B bc   --主料
             INNER JOIN BOM_Components_B bc1  ON bc.Bill_Sequence_ID = bc1.Bill_Sequence_ID  --替代料
                                             AND bc.Item_Num = bc1.Item_Num - MOD(bc1.Item_Num,10) AND MOD(bc1.Item_Num,10) > 0
             INNER JOIN BOM_Structures_B bh   ON bc.Bill_Sequence_ID = bh.Common_Bill_Sequence_ID
                                             AND bh.Alternate_BOM_Designator IS NULL 
                  WHERE 1=1
                    AND bc.DISABLE_DATE  IS NULL
                    AND bc1.DISABLE_DATE IS NULL
                    AND bh.Organization_ID    = x_Organization_ID
                    AND bh.Assembly_Item_ID   = x_Assembly_Item_ID     --組件
                    AND bc.Component_Item_ID  = x_Inventory_Item_ID
                    AND bc1.Component_Item_ID = a.Inventory_Item_ID)   --用料
      OR EXISTS (SELECT 1 
                   FROM Mtl_Related_Items r
                  WHERE r.Organization_ID   = x_Organization_ID
                    AND r.Related_Item_ID   = x_Inventory_Item_ID
                    AND r.Inventory_Item_ID = a.Inventory_Item_ID)
               ORDER BY a.Supply_Date;

iDempiere JAVA8 setup.bat

@Title ... Setup iDempiere Server
@Echo off

keytool -genkey -keyalg rsa -alias idempiere -dname "CN=acer7i, OU=Albert, O=AdempiereUser, L=MyTown, C=TW" -keypass myPassword -validity 999 -keystore C:\Users\Albert\buckminster.output\org.adempiere.server_3.0.0-eclipse.feature\idempiere.win32.win32.x86_64\idempiere-server\jettyhome\etc\keystore -storepass myPassword

@REM Setup idempiere.properties and idempiereEnv.properties
idempiere --launcher.ini setup.ini -application org.adempiere.install.application -consoleLog

@Echo ErrorLevel = %ERRORLEVEL%
@IF NOT ERRORLEVEL = 1 GOTO NEXT
@Echo ***************************************
@Echo Check the error message above.
@Echo ***************************************
@Pause
@Exit

:NEXT
@REM Setup Jetty
@Echo ... Setup Jetty
idempiere --launcher.ini setup.ini -application org.eclipse.ant.core.antRunner -buildfile build.xml

@Echo ...
@Echo For problems, check log file in base directory
@Rem Wait 10 second
@PING 1.1.1.1 -n 1 -w 10000 > NUL

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 v$locked_object

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 v$locked_object

SELECT vs.USERNAME, lo.OBJECT_ID, sob.NAEM, lo.SESSION_ID, vs.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.PROCESS
FROM V$LOCKED_OBJECT lo
INNER JOIN V$SESSION vs ON lo.SESSION_ID = vs.SID
INNER JOIN SYS.OBJ$ sob ON sob.obj# = lo.OBJECT_ID;

-- iDempiere 去抓取  Oracle EBS 送出解開被鎖死的程序 Interrupt the process deadlock,

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

SELECT c.owner, c.object_name, c.object_type,  fu.user_name  locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time,
vs.module,  vs.machine, vs.osuser,vlocked.oracle_username,
vs.SID, vp.pid, vp.spid AS os_process,
vs.serial#, vs.status, vs.saddr, vs.audsid, vs.process
           FROM v$locked_object vlocked
     INNER JOIN dba_objects c ON vlocked.object_id = c.object_id
     INNER JOIN v$session  vs ON vs.SID = vlocked.session_id
     INNER JOIN v$process  vp ON vs.paddr = vp.addr
LEFT OUTER JOIN fnd_logins fl ON vp.spid = fl.process_spid  AND vp.pid = fl.pid
LEFT OUTER JOIN fnd_user   fu ON fl.user_id = fu.user_id
WHERE 1=1
  AND NVL (vs.status, 'XX')<> 'KILLED';

-- iDempiere 去抓取 Oracle EBS 送出解開被鎖死的程序 Interrupt the process deadlock,

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 Find a deadlock process

SELECT vs.USERNAME, lo.OBJECT_ID, sob.NAEM, lo.SESSION_ID, vs.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.PROCESS
FROM V$LOCKED_OBJECT lo
INNER JOIN V$SESSION vs ON lo.SESSION_ID = vs.SID
INNER JOIN SYS.OBJ$ sob ON sob.obj# = lo.OBJECT_ID;

-- iDempiere 去抓取  Oracle EBS 送出解開被鎖死的程序 Interrupt the process deadlock,

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 Find a deadlock process

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 Find a deadlock process

SELECT vs.USERNAME, lo.OBJECT_ID, sob.NAEM, lo.SESSION_ID, vs.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.PROCESS
FROM V$LOCKED_OBJECT lo
INNER JOIN V$SESSION vs ON lo.SESSION_ID = vs.SID
INNER JOIN SYS.OBJ$ sob ON sob.obj# = lo.OBJECT_ID;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AP_CAL_WEEK_START_DATES_V

DROP VIEW APPS.MRP_AP_CAL_WEEK_START_DATES_V;

/* Formatted on 2015/2/1 下午 05:04:47 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AP_CAL_WEEK_START_DATES_V
(
   CALENDAR_CODE,
   EXCEPTION_SET_ID,
   WEEK_START_DATE,
   NEXT_DATE,
   PRIOR_DATE,
   SEQ_NUM,
   RN3,
   RN2,
   RN1
)
AS
   SELECT x.CALENDAR_CODE,
          x.EXCEPTION_SET_ID,
          x.WEEK_START_DATE,
          x.NEXT_DATE,
          x.PRIOR_DATE,
          x.SEQ_NUM,
          0 RN3,
          0 RN2,
          0 RN1
     FROM BOM_CAL_WEEK_START_DATES x,
          (SELECT DISTINCT Calendar_Code, Calendar_Exception_Set_ID
             FROM MTL_PARAMETERS mtlp
           UNION
           SELECT DISTINCT
                  Delivery_Calendar Calendar_Code,
                  -1 Calendar_Exception_Set_ID
             FROM PO_ASL_ATTRIBUTES) y
    WHERE     x.Calendar_Code = y.Calendar_Code
          AND x.Exception_Set_ID = y.Calendar_Exception_Set_ID;