gordan's blog

Hierarchicaly items

2010-11-06T14:45:00.000-07:00

Most useful thing is easy look at relater records. as Excell simple functionality for navigations, so I m lookin to solve problem for planning process in supply office departments with limits in materials, prices, items, money...Its DEMO for hierarchicaly views based on only ONE table, should be demonstrated on http://htmldb.oracle.com/pls/otn/f?p=28254:1

Tree hierarchical structures

Table plannavigacija have PK as ID column and NAME,CODE and IDID as FK with point to ID

code for select is

select * from plannavigacija where id in
with q as (
select level lvl,
id
from plannavigacija e
connect by prior id = idid
start with id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
)
select *
from (
-- employees above start with employee
select e.*
from plannavigacija e
connect by prior idid=id
start with id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
union
-- employees who are are peers of the start with employee
select e.*
from plannavigacija e
where level = (select lvl from q where id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA)))
connect by prior id = idid
start with id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
union
-- employees directly below start with employee
select e.*
from plannavigacija e
where idID = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
) sub
connect by prior id = idid
start with id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
Where 1 is one variable :P1_X as start tree or root

Ext JS oracle apex login form

2010-10-16T05:44:00.000-07:00

With Ext JS and oracle application expres (apex) 4 could be change reports/forms
initialy created by apex and integrated with Ext JS, this is one of example how can be completed login form with Ext JS window(). DEMO > http://htmldb.oracle.com/pls/otn/f?p=25605:101

Ext JS code in report region

Apex 4 based on Oracle xe 10g 10.2.0.1

2010-06-29T09:49:00.000-07:00

Files structures-I was trying to install and its pass ok, I was creating 2 folders, one corinscore(with files as from core) and coreinsbuilder(with files from builder), apexins.sql have PREFIX changed as path of Linux OS, and coreins.sql

Post installing steps

Apex admin as ADMIN/password

Completed install apex 4

Example Work Order

2009-11-15T13:25:00.001-08:00

This example present one part of ERP systems - main element of distribution production -work order wirh elements 1)operations and 2)materials
http://htmldb.oracle.com/pls/otn/f?p=27645:1:3186589601489819:::::

Input form for creating main part of work order

Work order with elements (materials, operations, adding parameters) master detail form

Apex 3.01 on Linux Ununtu 8.04 based on Application Express 10g

2009-08-24T14:53:00.000-07:00

Apex 3.01 can be install in Application Express 10g with this step:
1> need download zip Apex 3.01 and unzip it into /usr/lib/oracle/xe/apex folder
2> change apexins.sql UPGRADE to 2, PREFIX to /usr/lib/oracle/xe/apex/
and set path at the end of apexins.sql and save changes

from sql command line need type @ /usr/lib/oracle/xe/apex/apexins REPUBLICA SYSTEM SYSTEM TEMP /i/

Whith created Folder TEMP on /usr/lib/oracle/xe/ need copy pictures from apex/images folder in subfolder TEMP/i (I was creating and another folder /usr/lib/oracle/xe/TEMP/images with whole pictures

This figure explain how to call procedure for ADMIN user

Whith created ADMIN user needs insert password, with this username and password could be posible setting WORKSPACE and USER NAME and PASSWORD

finaly this step enter password for ADMIN user as confirmation

Import *.dmp into Oracle 10g XE Application Express on Linux Ubuntu 8.04

2009-04-22T13:39:00.001-07:00

Using OS Linux Ubuntu 8.04 with install Oracle 10g XE application express, database users can imports *.dmp files, setting environmetn on this way>
0) instal *.deb file Oracle app expres and create user (in this samples user is adempiere and password is adempiere)
1) on terminal type> sudo gedit /etc/environment
2) set this environment>
PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/lib/jvm:/home/nadrog:/home/nadrog/jdk1.6.0_11/bin:/home/nadrog/Adempiere/lib"
JAVA_HOME="/home/nadrog/jdk1.6.0_11"
JAVA="/home/nadrog/jdk1.6.0_11/bin/java"
CLASSPATH="/usr/lib/jvm:/home/nadrog"
JAVA_OPTS="-server -Xms128m -Xmx128m -Dprogram.name=run"
JRE_SRCROOT="/usr/lib/jvm/java-6-sun-1.6.0.07/bin/javaws"
ADEMPIERE_HOME="/home/nadrog/Adempiere"
JBOSS_HOME="/home/nadrog/Adempiere/jboss/lib"
COMPIERE_HOME="/home/nadrog/Compiere2"

3) restart PC and again on terminal type> sudo gedit /etc/environment
and add this environment
ORACLE_HOME="/usr/lib/oracle/xe/app/oracle/product/10.2.0/server"
ORACLE_SID="XE"
Restart PC and then type from terminal

nadrog@nadrog:~$ su oracle
Password:
To run a command as administrator (user "root"), use "sudo ".
See "man sudo_root" for details.

oracle@nadrog:/home/nadrog$ .
bash: .: filename argument required
.: usage: . filename [arguments]
oracle@nadrog:/home/nadrog$ cd Desktop
oracle@nadrog:/home/nadrog/Desktop$ cd..

oracle@nadrog:~/app/oracle$ /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/imp adempiere@adempiere

Figure 1> Import dmp files into Oracle Application Express
10g XE

Adempiere 3.52 Oracle Application Express10g

2009-04-17T11:53:00.000-07:00

Adempiere 3.52 is smart Enterprise Resource planning system, and offer good support for management and all enterprise activity.
Main page looking like above>
Figure 1> Main page Adempiere smart ERP system
Oracle Application Express 10g is database and have relation tables

Figure 2> AD_MENU table with attributes
Figure 3> Main Window and product setup
Figure 4> Main window and Financial setup
Figure 5> Wirh Oracle 10g need setup users, login as SYSTEM and PASSWORD from installations, and setup ADEMPIERE/ADEMPIERE
Figure 6> Setup ADEMPIERE/ADEMPIERE on Oracle Apex,
Figure 7 > Imports of Adempiere.dmp file into user Adempiere
This is installation Adempiere smart ERP on Windows platform

ORACLE APPLICATION EXPRESS 10g WITH COMPIERE ERP

2009-03-14T16:30:00.000-07:00

Compiere ERP systems was installing on Oracle Application Express 10 g.
Theres 760 tables with all columns need for implement ERP systems (from orders, invoices, products or services with bill of materials for each products/services, with unit prices for every part from BOM-s, security settings and roles for manu users, storages (FIFO, LIFO) and production management life cycle.
First need install Oracle Application Express 10g and need create user COMPIERE with COMPIERE password
next need start COMPIERE2.SH formsetup compiere on pc

Fig 1> Oracle Application Express 10g and table M_BOM with records about products/services. This examples show relations in relation model.

Products need insert into compiere products. In examples here product is Measuerment transformer for higth voltage UH 9-15. This product have bill of material with 1000 elements.
EPA 10022378722 Porcelain insulator,
EPA 20938977866 Lid Steel sheet
EPA 89809008088 connectors hv x 2
EPA 80980789890 connectors lv x 10
.......................................................................
.......................................................................
EPA 89779987887 dilatable diaphragm

Fig 2> Products have basics elements, all elements have unit measure,unit prices and technical dates. So all products and basics elements need insert into Oracles tables (over compiere web javas UI) This forms looking like that>

Fig 3> Input forms for products (before that need set Organisations name, type division etc)

INSTALLATIONS

Installation on Linux Ubuntu 8.04>
First step is downloads
Compiere 3.1 from sourceforge.net and Oracle Application Express 10g from www.oracle.com and JDK 1.6

Next need setting Environment as

nadrog@nadrog:~$ sudo gedit /etc/environment
and put this >

PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/lib/jvm:/home/nadrog:/home/nadrog/jdk1.6.0_11/bin:/home/nadrog/Adempiere/lib"
JAVA_HOME="/home/nadrog/jdk1.6.0_11"
JAVA="/home/nadrog/jdk1.6.0_11/bin/java"
CLASSPATH="/usr/lib/jvm:/home/nadrog"
JAVA_OPTS="-server -Xms128m -Xmx128m -Dprogram.name=run"
JRE_SRCROOT="/usr/lib/jvm/java-6-sun-1.6.0.07/bin/javaws"
ADEMPIERE_HOME="/home/nadrog/Adempiere"
JBOSS_HOME="/home/nadrog/Adempiere/jboss/lib"
COMPIERE_HOME="/home/nadrog/Compiere2"

Fig 4 > First step is to set ORACLE XE and JBOSS serves, user and password for compiere(and pass is compiere) and systems password form Oracle (during oracle 10 g installation step ). Compiere serves 1000 users by default, and ofer 100% security over internet. Oracle Application Express offer backup/restore/rman and have 4 gbytes spaces.

This ERP system is 100% free.

validations with Apex

2009-02-27T12:44:00.000-08:00

Validations with apex expres have step one-13 digits
next NUMERIC and last with condition above>

select :P4_JMBG from dual where
11-(
(
(7*(to_number(substr(:P2_JMBG,1,1))))+
(6*(to_number(substr(:P2_JMBG,2,1))))+
(5*(to_number(substr(:P2_JMBG,3,1))))+
(4*(to_number(substr(:P2_JMBG,4,1))))+
(3*(to_number(substr(:P2_JMBG,5,1))))+
(2*(to_number(substr(:P2_JMBG,6,1))))+
(7*(to_number(substr(:P2_JMBG,7,1))))+
(6*(to_number(substr(:P2_JMBG,8,1))))+
(5*(to_number(substr(:P2_JMBG,9,1))))+
(4*(to_number(substr(:P2_JMBG,10,1))))+
(3*(to_number(substr(:P2_JMBG,11,1))))+
(2*(to_number(substr(:P2_JMBG,12,1))))
)-
(FLOOR(((
(7*(to_number(substr(:P2_JMBG,1,1))))+
(6*(to_number(substr(:P2_JMBG,2,1))))+
(5*(to_number(substr(:P2_JMBG,3,1))))+
(4*(to_number(substr(:P2_JMBG,4,1))))+
(3*(to_number(substr(:P2_JMBG,5,1))))+
(2*(to_number(substr(:P2_JMBG,6,1))))+
(7*(to_number(substr(:P2_JMBG,7,1))))+
(6*(to_number(substr(:P2_JMBG,8,1))))+
(5*(to_number(substr(:P2_JMBG,9,1))))+
(4*(to_number(substr(:P2_JMBG,10,1))))+
(3*(to_number(substr(:P2_JMBG,11,1))))+
(2*(to_number(substr(:P2_JMBG,12,1))))
)
/11))*11))=to_number(substr(:P2_JMBG,13,1))
or
11-(
(
(7*(to_number(substr(:P2_JMBG,1,1))))+
(6*(to_number(substr(:P2_JMBG,2,1))))+
(5*(to_number(substr(:P2_JMBG,3,1))))+
(4*(to_number(substr(:P2_JMBG,4,1))))+
(3*(to_number(substr(:P2_JMBG,5,1))))+
(2*(to_number(substr(:P2_JMBG,6,1))))+
(7*(to_number(substr(:P2_JMBG,7,1))))+
(6*(to_number(substr(:P2_JMBG,8,1))))+
(5*(to_number(substr(:P2_JMBG,9,1))))+
(4*(to_number(substr(:P2_JMBG,10,1))))+
(3*(to_number(substr(:P2_JMBG,11,1))))+
(2*(to_number(substr(:P2_JMBG,12,1))))
)-
(FLOOR(((
(7*(to_number(substr(:P2_JMBG,1,1))))+
(6*(to_number(substr(:P2_JMBG,2,1))))+
(5*(to_number(substr(:P2_JMBG,3,1))))+
(4*(to_number(substr(:P2_JMBG,4,1))))+
(3*(to_number(substr(:P2_JMBG,5,1))))+
(2*(to_number(substr(:P2_JMBG,6,1))))+
(7*(to_number(substr(:P2_JMBG,7,1))))+
(6*(to_number(substr(:P2_JMBG,8,1))))+
(5*(to_number(substr(:P2_JMBG,9,1))))+
(4*(to_number(substr(:P2_JMBG,10,1))))+
(3*(to_number(substr(:P2_JMBG,11,1))))+
(2*(to_number(substr(:P2_JMBG,12,1))))
)
/11))*11))=11

references
http://en.wikipedia.org/wiki/Unique_Master_Citizen_Number
http://apex.oracle.com/pls/otn/f?p=39992:1

APEX on UBUNTU 8.04

2008-11-12T13:13:00.000-08:00

I was installing APEX on Ubuntu 8.04 installing oracle xe 10.0.2.0.1.deb files typing from command terminall
sudo dpkg -i oracle-xe_10.2.0.1-1.0_i386.deb
after installing packages need type
-e
'/etc/init.d/oracle-xe configure'
type 8080
1521 and SYSTEM or SYS and password
and
'/etc/init.d/oracle-xe start'

as root user to configure database

APEX 2.0 on UBUNTU 8.04 with database space of 4 GbytesLINUX

asset management

2008-10-31T10:43:00.000-07:00

Asset management have one main pages ASSET (with pk and fk referenced by asset char, asset state, asset history and more other assets)
Main pages are at>http://htmldb.oracle.com/pls/otn/f?p=38485:1:2547506111682738:::::

Fig 1: Main pages Asset Management. From left
side user can add new categories, states, conditions
performances and other attributess.

Apex and Ebs

2008-08-30T00:29:00.000-07:00

I was installing web applications using apex 2/3.0.
Apex 3.1.0 have 20 templates which can implement user interfaces (forms) with insert/update
dates into tables (using trigger sequences mechanisms). At that moment I m waiting Apex 3.2 which will completed so nearly

This is themes have apex 3.1 installation and theres basic for Web Applications. Using AJAX developer can modify forms.

duplicate code, street_name and street_number

2008-08-25T11:16:00.000-07:00

I was creating way how to solve problem with duplicate code, street_name and street_number.
so ther's duplicate code querys:

I have tableA(code, street_name,street_number, att1, att2) and theres duplicate code, street_name and street_numbers.
I was found duplicate code using:

select code from tableA where code in (select code from tableA group by code having count(*) >1)

but in meantime, code was changed and I have other code and old street_name and old street_number,
so tableA now have :
oldCODE,street_name, street_number and newCODE, street_name, street_number --- duplicate street_name and street_number!.
tableA have 7000 records.
Question is how found this duplicate street_name and street_number with this different code (old and new CODE)?

select STREET_NAME, STREET_NUMBER, max(ltrim(sys_connect_by_path(CODE,' - '),' -')) keep (dense_rank last order by length(sys_connect_by_path(CODE,' - ')))duplicate_code
from TABLEA

connect by prior CODE < CODE
and prior street_name =street_name
and prior street_number = street_number
group by STREET_NAME, STREET_NUMBER
having count(*)>1;

And ther's list of Street_Name, Street_Number, duplicate_CODE
100 of 15000 records!
So need update old code as 'NO ACTIVE'
or 'OLD VALUES'

HURIDOC apex information documentation system

2008-08-22T12:46:00.000-07:00

Its reprogramming existing application writen on ms access
with that web (client server) on:
http://htmldb.oracle.com/pls/otn/f?p=20604:4:3119063912909518:::::

When user login into this documentation system, can see options from main menu
Users can change, edit or only read informations according rolles

apex insert/update forms

2008-08-21T13:58:00.000-07:00

I was creating sample application with insert/update forms and small html (based on javascript and css support) pages on
http://htmldb.oracle.com/pls/otn/f?p=50223:1:2667842752465619:::::
This is basic of asset managemens web implementation technology's.