Vault General

Reply
New Member
goepfert
Posts: 1
Registered: ‎05-28-2009
Message 1 of 2 (120 Views)

onewaysync of data from PPS to PSP - best practice?

120 Views, 1 Replies
05-28-2009 01:40 AM
Hi.

In advance:
From other postings I already learned, that ODBC is not recommended and not supported, and that there is a API, that should be used, as it's not changing, and by using it PSP takes care of a lot of depenencies it has to maintain internally.

What we need:
Our PPS is Baeurer Industry, Version 6. Several Data from the PPS system have to be imported to PSP, so that PPS. The main junction is the "part number", that will, and that *MUST* be equal to the CAD document number.

What is the best practice to *IMPORT* the required information from the PPS system into the PSP database?
- DB trigger, to "tell" PSP to import specified data that have changed?
- batch job to do a SQL query to the PPS system, then use PSP API to push data down to PSP?
- any other, much better way, I can't think of right now, as I have no PSP skills so far at all?


What we've got so far:
A Autodesk Partner in our region introduced the PSP server here in fall 2009. They decided to use a Excel Export from Baeurer Industry to import the Data to PSP. IMHO this is a quite, - well - ehm - strange way to do so, as Excel is limited to 64k rows, and we already have 180000 parts with own CAD documents in several revisions in B2...

This Import to PSP failed / was canceled so far at least 5 times.

In PSP there are several custom fields inserted into table "Parts". Those are the destination fields to take the
resulting export Excel Files that are the output of this SQL statement:


------------------------------------------------------------------------
select g000.identnr,g000.ben,g040.werkstoff,g040.zeichnr,g040.zf,g040.klasse,g000.me,g020.konto,g020.dispn,
g043.bs,g040.ts,g000.ch,g000.datneu,g000.userneu,g000.dataen,g000.useraen
from g040,g000,g020,g030,g043

where substr(g000.identnr,1,1) in ("1","2","3")
and (g000.dataen > "2009-04-19 00:00:00 "
or g000.datneu > "2009-04-19 00:00:00 ")

and g000.identnr = g020.identnr and g000.fi_nr = g020.fi_nr
and g000.identnr = g043.identnr and g000.fi_nr = g043.fi_nr
and g000.identnr = g030.identnr and g000.fi_nr = g030.fi_nr
and g000.identnr = g040.identnr and g000.fi_nr = g040.fi_nr

order by g000.identnr
------------------------------------------------------------------------

Currently there is no need to sync data FROM PSP TO PPS, but this might be a future challange.


Any suggestions appreciated,

TIA, Rudi.
*Thomas Schaetzle
Message 2 of 2 (120 Views)

Re: onewaysync of data from PPS to PSP - best practice?

06-09-2009 08:22 AM in reply to: goepfert
goepfert <> wrote in news:6190828@discussion.autodesk.com:

> Hi.
>
> In advance:
> From other postings I already learned, that ODBC is not recommended and
> not supported, and that there is a API, that should be used, as it's not
> changing, and by using it PSP takes care of a lot of depenencies it has
> to maintain internally.
>
> What we need:
> Our PPS is Baeurer Industry, Version 6. Several Data from the PPS system
> have to be imported to PSP, so that PPS. The main junction is the "part
> number", that will, and that *MUST* be equal to the CAD document number.
>
> What is the best practice to *IMPORT* the required information from the
> PPS system into the PSP database?
> - DB trigger, to "tell" PSP to import specified data that have changed?
> - batch job to do a SQL query to the PPS system, then use PSP API to
> push data down to PSP? - any other, much better way, I can't think of
> right now, as I have no PSP skills so far at all?
>
>
> What we've got so far:
> A Autodesk Partner in our region introduced the PSP server here in fall
> 2009. They decided to use a Excel Export from Baeurer Industry to import
> the Data to PSP. IMHO this is a quite, - well - ehm - strange way to do
> so, as Excel is limited to 64k rows, and we already have 180000 parts
> with own CAD documents in several revisions in B2...
>
> This Import to PSP failed / was canceled so far at least 5 times.
>
> In PSP there are several custom fields inserted into table "Parts".
> Those are the destination fields to take the resulting export Excel
> Files that are the output of this SQL statement:
>
>
> ------------------------------------------------------------------------
> select
> g000.identnr,g000.ben,g040.werkstoff,g040.zeichnr,g040.zf,g040.klasse,g00
> 0.me,g020.konto,g020.dispn,
> g043.bs,g040.ts,g000.ch,g000.datneu,g000.userneu,g000.dataen,g000.useraen
> from g040,g000,g020,g030,g043
>
> where substr(g000.identnr,1,1) in ("1","2","3")
> and (g000.dataen > "2009-04-19 00:00:00 "
> or g000.datneu > "2009-04-19 00:00:00 ")
>
> and g000.identnr = g020.identnr and g000.fi_nr = g020.fi_nr
> and g000.identnr = g043.identnr and g000.fi_nr = g043.fi_nr
> and g000.identnr = g030.identnr and g000.fi_nr = g030.fi_nr
> and g000.identnr = g040.identnr and g000.fi_nr = g040.fi_nr
>
> order by g000.identnr
> ------------------------------------------------------------------------
>
> Currently there is no need to sync data FROM PSP TO PPS, but this might
> be a future challange.
>
>
> Any suggestions appreciated,
>
> TIA, Rudi.
>

Hi Rudi,

i think, the major question is how the data import to PSP can be triggered
inside the PPS system.
And there's also the question of how the item world in PPS and the
documents in PSP can be mapped. As far as i understood you don't use the
items in PSP, right? I understood that you map the PSP documents to the PPS
items?

With PSP there are several possible ways to put data into the database:

- Use the mechanisms inside PSP to view foreign database tables.
The PPS data can be viewed directly inside PSP.
There is the possibility of adding custom commands to PSP
to transfer the data.
But this solution isn't good for automatic stuff.

- PSP has a central COM Component which can be used by custom programs.
So there is the possibility to program a custom client
which changes the data of PSP.
This solution would be overkill for that requirements, i think.

- If you don't want to create data in PSP but just change some data
the idea of using triggers inside the PPS database and fire UPDATEs
into the PSP database is a valid solution.
This would do an automatic sync of data based on the triggers
that are implemented in the PPS database.
Creation of elements without the core COM components of PSP is
critical because of all the implicit logic that is done inside PSP.
Especially with documents.

Hope that helps

Thomas

You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

Announcements
New button for Platinum & Gold Subscription customers
Gold & Platinum Subscription customers can alert Autodesk if they require assistance with their forum post

Manufacturing Community
The Manufacturing Community provides additional access to tips, tutorials, blogs and networking with peers.

Need installation help?

Start with some of our most frequented solutions to get help installing your software.

Ask the Community