ERP Flashing back

CREATE TABLE trserp (
    codice INTEGER NOT NULL,
    codop CHARACTER VARYING(51),
    codubi CHARACTER VARYING(51),
    codoper CHARACTER VARYING(51),
    datatrs CHARACTER VARYING(9),
    oratrs CHARACTER VARYING(9),
    tmtrs INTEGER,
    filename CHARACTER VARYING(101)
);
COMMENT ON COLUMN trserp.datatrs IS 'Data di trasferimento';
COMMENT ON COLUMN trserp.oratrs IS 'Ora di trasferimento';
COMMENT ON COLUMN trserp.tmtrs IS 'Tm di trasferimento';
COMMENT ON COLUMN trserp.filename IS 'Nome del file generato';
ALTER TABLE ONLY trserp
    ADD CONSTRAINT trserp_pkey PRIMARY KEY (codice);

<?xml version="1.0"?>
<dsOEE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Activity>
    <Company>000</Company>
    <WorkOrderNumber>1220433</WorkOrderNumber>
    <IDAct>1683438</IDAct>
    <ActPos>50</ActPos>
    <Qty>444</Qty>
    <QtyScrap>1</QtyScrap>
    <ScrapRemark>Ausschuss</ScrapRemark>
    <Resource>821102</Resource>
    <Finished>no</Finished>
    <TimeTE>43294</TimeTE>
    <TimeTR>3514</TimeTR>
    <TimeUnit>0</TimeUnit>
  </Activity>
</dsOEE>

Il dato relativo ad ActPos va ricavato dal file di Input vedi sotto:



Dal file di Input bisogna alimentare la relazione "activity" dichiarata sotto

CREATE TABLE activity (
    codice INTEGER NOT NULL,
    codop CHARACTER VARYING(51),
    codubi CHARACTER VARYING(51),
    idact CHARACTER VARYING(51),
    actpos CHARACTER VARYING(51)
);
COMMENT ON COLUMN activity.codop IS 'Codice ordine di produzione (ordprod.codop)';
COMMENT ON COLUMN activity.codubi IS 'Codice ubicazione (ubicaz.codice)';
ALTER TABLE ONLY activity
    ADD CONSTRAINT activity_codop_codubi_key UNIQUE (codop, codubi);
ALTER TABLE ONLY activity
    ADD CONSTRAINT activity_pkey PRIMARY KEY (codice);
ALTER TABLE ONLY activity 
    ADD CONSTRAINT activity_codop_fkey FOREIGN KEY (codop) REFERENCES ordprod(codop) ON DELETE CASCADE;
ALTER TABLE ONLY activity 
    ADD CONSTRAINT activity_codubi_fkey FOREIGN KEY (codubi) REFERENCES ubicaz(codice) ON DELETE CASCADE;

Aktivität (Workorder Position/machine):

SELECT identif FROM ubicaz WHERE codice='821102A';

Menge Gut (number of good parts):

SELECT SUM(movimag.qta) AS npp FROM movimag INNER JOIN (lottim INNER JOIN (prodord INNER JOIN produbi ON prodord.codprod=produbi.codice) ON prodord.codop=lottim.procord) ON movimag.codlot=lottim.codice WHERE produbi.codubi='821102A' AND prodord.codop='1220433' AND (produbi.tmfi>'1493902281'OR produbi.tmfi IS NULL) AND movimag.codcau='PREL' AND movimag.codubi='821102A';

Menge Ausschuss (number of scrap):

SELECT SUM(movimag.qta) AS nps FROM movimag INNER JOIN (lottim INNER JOIN (prodord INNER JOIN produbi ON prodord.codprod=produbi.codice) ON prodord.codop=lottim.procord) ON movimag.codlot=lottim.codice WHERE produbi.codubi='821102A' AND prodord.codop='1220433' AND (produbi.tmfi>'1493902281'OR produbi.tmfi IS NULL) AND movimag.codcau='SCAPR' AND movimag.codubi='821102A';

Buchungshinweis (additional information about scrap):

SELECT causanc.codice, movimag.qta FROM causanc INNER JOIN (movimag INNER JOIN (lottim INNER JOIN (prodord INNER JOIN produbi ON prodord.codprod=produbi.codice) ON prodord.codop=lottim.procord) ON movimag.codlot=lottim.codice) ON causanc.codice=movimag.caudef WHERE produbi.codubi='821102A' AND prodord.codop='1220433' AND (produbi.tmfi>'1493902281'OR produbi.tmfi IS NULL) AND movimag.codcau='SCAPR' AND movimag.codubi='821102A';

Abgeschlossen (AkvP fertig) (status of work order partly oder finished):

SELECT SUM(movimag.qta) AS STATUS FROM movimag INNER JOIN lottim ON movimag.codlot=lottim.codice WHERE movimag.codcau='PREL' AND lottim.procord='1220433';

Mitarbeiter Nr. (ident. nr. of worker):

SELECT (CASE WHEN ndip IS NULL THEN '1' ELSE ndip END) AS nrworkers FROM ordprod WHERE codop='1220433';

Zeit te (working time machine/100st) ???:

SELECT SUM(tmdiff) FROM movimag WHERE codcau='INFM' AND tm>'1493902281' AND tm<'1493976666' AND codubi='821102A';

Zeit tr (setup time) ???:

SELECT SUM(tmdiff) FROM movimag WHERE codcau='FIFM' AND caufm='setup' AND tm>'1493902281' AND tm<'1493976666' AND codubi='821102A';

The RFC function module name is /FOX/PP_ANDON_CONF

• Parameters:
  • budate = posting date
  • store = default 0
  • EXNAM = defautl space
  • iv_conftime is a structure - order number and yield quantity and unit of measure should be in, the times can be empty.

• iv_rueck contains the confirmation numbers.

The export parameter subrc = S - in case it worked or E it failed.

Here the different views on the FM you can call. all is on QE1 our test system need to be called via logon groups as for the other one before.