timallen
Well Known Member
We have created several update packages for PD7333. They asked me to go back and create one update package for PY7333 which would include all objects from the update packages for PD7333.
Although I created the package from OneWorld, I wrote some SQL statements to help me do this. We are using SQL Server, but similar SQL would work in Oracle. Here is how I did it (This is more complicated to explain than it is to do):
1) I created the new package for PY7333. When I got to the part where I had to select objects, I connected with iSQL:
c:> isql -S DATASERVER -U SYS7333 -P SYS7333 -d jde7333
2) This SQL finds all the objects which exist in the update packages for PD7333 (I named all of them like PD7333U001...) which don't exist in the new PD7333 update package named PD7333UALL:
select distinct pdobnm from f9631 where pdpkgname like 'PD7333U%'
and pdobnm not in (
select distinct pdobnm from f9631 where pdpkgname = 'PY7333UALL'
)
I then added each of these objects to the package. Important: I selected the option to include all versions (later I'll show how I got rid of versions I didn't want in the package). I was able to run this SQL repeatedly until I was done to be sure I didn't miss anything.
3) When I finished adding all the objects, I ran the following SQL to find objects which I didn't want in the package:
select distinct pdobnm + '|' + pdvers from f9631 where pdpkgname = 'PY7333UALL'
and pdobnm + '|' + pdvers not in (
select distinct pdobnm + '|' + pdvers from f9631 where pdpkgname like 'PD7333U%'
)
This SQL shows all the versions in the new package which are not in the old packages. From the list of objects in the package, I deleted these versions. Again, I ran this repeatedly as I progressed to make sure I didn't miss any.
4) When I had finished, I ran the following SQL to see what versions were in the old packages but not in the new one:
select distinct pdobnm + '|' + pdvers from f9631 where pdpkgname like 'PD7333U%'
and pdobnm + '|' + pdvers not in (
select distinct pdobnm + '|' + pdvers from f9631 where pdpkgname = 'PY7333UALL'
)
There were some versions in this situation, but they were all old versions with names like 'PRUEBA' (test) which had subsequently been erased-- apparently the F9631 keeps a record of what went into a package even after the object has been erased.
That's all. As I said, it looks very complicated, but it really was quite simple in the end. There's certainly a way to do this directly in the database, but I like doing things from the front end to make sure I don't mess something up out of ignorance.
Hope this is helpful.
Although I created the package from OneWorld, I wrote some SQL statements to help me do this. We are using SQL Server, but similar SQL would work in Oracle. Here is how I did it (This is more complicated to explain than it is to do):
1) I created the new package for PY7333. When I got to the part where I had to select objects, I connected with iSQL:
c:> isql -S DATASERVER -U SYS7333 -P SYS7333 -d jde7333
2) This SQL finds all the objects which exist in the update packages for PD7333 (I named all of them like PD7333U001...) which don't exist in the new PD7333 update package named PD7333UALL:
select distinct pdobnm from f9631 where pdpkgname like 'PD7333U%'
and pdobnm not in (
select distinct pdobnm from f9631 where pdpkgname = 'PY7333UALL'
)
I then added each of these objects to the package. Important: I selected the option to include all versions (later I'll show how I got rid of versions I didn't want in the package). I was able to run this SQL repeatedly until I was done to be sure I didn't miss anything.
3) When I finished adding all the objects, I ran the following SQL to find objects which I didn't want in the package:
select distinct pdobnm + '|' + pdvers from f9631 where pdpkgname = 'PY7333UALL'
and pdobnm + '|' + pdvers not in (
select distinct pdobnm + '|' + pdvers from f9631 where pdpkgname like 'PD7333U%'
)
This SQL shows all the versions in the new package which are not in the old packages. From the list of objects in the package, I deleted these versions. Again, I ran this repeatedly as I progressed to make sure I didn't miss any.
4) When I had finished, I ran the following SQL to see what versions were in the old packages but not in the new one:
select distinct pdobnm + '|' + pdvers from f9631 where pdpkgname like 'PD7333U%'
and pdobnm + '|' + pdvers not in (
select distinct pdobnm + '|' + pdvers from f9631 where pdpkgname = 'PY7333UALL'
)
There were some versions in this situation, but they were all old versions with names like 'PRUEBA' (test) which had subsequently been erased-- apparently the F9631 keeps a record of what went into a package even after the object has been erased.
That's all. As I said, it looks very complicated, but it really was quite simple in the end. There's certainly a way to do this directly in the database, but I like doing things from the front end to make sure I don't mess something up out of ignorance.
Hope this is helpful.
