Re: ? Launch VB .EXE Inside SQR ?

[Arun Kumar <arunkumar13@YAHOO.COM>]

Bindu,
Make sure you have ODBC version of sqr to be able to
connect to two different databases at the same point.


--- Raj Amoli <rajkamoli@HOTMAIL.COM> wrote:
> Bindu:
> I did it for my last client. We are going live this
> week, so do not have
> time to make sample code but here is the relevent
> portion of code from sqr.
>
> ********************
>
!**************************************************************************
> ! PROCEDURE:  SELECT-PS_PHYSICAL_INV
> !   1. IT DELETES ALL RECORDS FROM INVBALANCES TABLE
> IN MAXIMO DATABASE.
> !   2. IT SELECTS RECORDS FROM PS_PHYSICAL_INV TABLE
> IN PEOPLESOFT DB.
>
!**************************************************************************
> BEGIN-PROCEDURE SELECT-PS_PHYSICAL_INV
> BEGIN-SQL       -DBDSN=MAXDEMO;UID=MAXIMO;PWD=PWD
>            ! Maximo Database
> DELETE FROM INVBALANCES WHERE 1 = 1
>
> END-SQL
>
> LET #COUNT = 0
>
> BEGIN-SELECT  -DBDSN=PSDEV7;UID=sa;PWD=PWD
>            ! PeopleSoft Database
> PHY.BUSINESS_UNIT               &BU
> PHY.INV_ITEM_ID                 &ITEM_ID
> PHY.STOR_LEVEL_1                &LEVEL_1
> PHY.STOR_LEVEL_2                &LEVEL_2
> PHY.STOR_LEVEL_3                &LEVEL_3
> PHY.STOR_LEVEL_4                &LEVEL_4
> PHY.QTY                                 &QTY
> PHY.DT_TIMESTAMP
>
>         MOVE &BU                                TO
> $BU
>         MOVE &ITEM_ID                   TO $ITEM_ID
>         MOVE &LEVEL_1                   TO $LEVEL_1
>         MOVE &LEVEL_2                   TO $LEVEL_2
>         MOVE &LEVEL_3                   TO $LEVEL_3
>         MOVE &LEVEL_4                   TO $LEVEL_4
>         MOVE &QTY                               TO
> #QTY
>
>         LET $ITEM_ID                    =
> UPPER($ITEM_ID)
>         LET $BU                                 =
> UPPER($BU)
>         LET $BINNUM                             =
> SUBSTR($LEVEL_1 || '  ',1,2) || SUBSTR($LEVEL_2 || '
> ',1,2)
>         LET $BINNUM                             =
> $BINNUM || SUBSTR($LEVEL_3 || '  ',1,2) ||
> SUBSTR($LEVEL_4
> || '  ',1,2)
>         LET $LOTNUM                             = ''
>         LET #CURBAL                             =
> #QTY
>         LET #PHYSCNT                    = 0
>         LET $PHYSCNTDATE                = ''
>         LET #LDKEY                              = 0
>         LET $IB1                                = ''
>         LET $IB2                                = ''
>         LET $IB3                                = ''
>         LET $IB4                                = ''
>         LET $IB5                                = 0
>         LET $RECONCILED                 = 'Y'
>         LET $SOURCESYSID                = ''
>         LET $OWNERSYSID                 = ''
>         LET $EXTERNALREFID              = ''
>
> #IFDEF DEBUGP
>         SHOW 'SELECTING VOUCHER ACCOUNTING LINE
> RECORDS'
>     SHOW $BU '/' $ITEM_ID '/' $LEVEL_1 '/' $LEVEL_2
> '/' $LEVEL_3 '/'
> $LEVEL_4 '/' #QTY
> #END-IF
>         DO DUPLICATEKEY-INVBALANCES
>         IF #FOUND = 0
>            DO INSERT-INVBALANCES
>         END-IF
>         LET #FOUND = 0
>
> FROM PS_PHYSICAL_INV PHY
> ORDER BY PHY.BUSINESS_UNIT,
> PHY.INV_ITEM_ID,PHY.STOR_LEVEL_1,PHY.STOR_LEVEL_2,
> PHY.STOR_LEVEL_3,PHY.STOR_LEVEL_4,PHY.DT_TIMESTAMP
> DESC,PHY.QTY DESC
>
>
> END-SELECT
>
> END-PROCEDURE PS_PHYSICAL_INV
>
>
!**************************************************************************
> ! PROCEDURE:  INSERT-INVBALANCES
> !
> !   THIS PROCEDURE INSERTS ROWS IN INVBALANCES TABLE
> IN MAXDEMO DATABASE.
>
!**************************************************************************
> BEGIN-PROCEDURE INSERT-INVBALANCES
>
> #IFDEF DEBUGP
>   SHOW 'IN INSERT-INVBALANCES ..'
> #END-IF
>
>
> BEGIN-SQL ON-ERROR=SQL-ERROR
> -DBDSN=MAXDEMO;UID=MAXIMO;PWD=PWD
> INSERT INTO
> INVBALANCES
> (
> ITEMNUM,
> LOCATION,
> BINNUM,
> LOTNUM,
> CURBAL,
> PHYSCNT,
> PHYSCNTDATE,
> LDKEY,
> IB1,
> IB2,
> IB3,
> IB4,
> IB5,
> RECONCILED,
> SOURCESYSID,
> OWNERSYSID,
> EXTERNALREFID
> )
> VALUES
> (
> $ITEM_ID,
> $BU,
> $BINNUM,
> $LOTNUM,
> #CURBAL,
> #PHYSCNT,
> $PHYSCNTDATE,
> #LDKEY,
> $IB1,
> $IB2,
> $IB3,
> $IB4,
> #IB5,
> $RECONCILED,
> $SOURCESYSID,
> $OWNERSYSID,
> $EXTERNALREFID
> )
>
> END-SQL
>
> END-PROCEDURE INSERT-INVBALANCES
>
>
>
> ********************
>
> I hope it helps.
>
>
> Raj Amoli
>
>
> >From: "Vallabhu, Bindu"
> <Bindu.Vallabhu@STAPLES.COM>
> >Reply-To: sqr-users@list.iex.net
> >To: SQR-USERS@list.iex.net
> >Subject: Re: ? Launch VB .EXE Inside SQR ?
> >Date: Tue, 28 Aug 2001 11:06:17 -0400
> >MIME-Version: 1.0
> >Received: from [192.156.196.15] by hotmail.com
> (3.2) with ESMTP id
> >MHotMailBD55045D00434004370FC09CC40FE9BA0; Tue, 28
> Aug 2001 08:31:47 -0700
> >Received: from list.iex.net (list.iex.net
> [192.156.196.15])by list.iex.net
> >(8.11.1/8.11.1) with ESMTP id f7SF7qT08399;Tue, 28
> Aug 2001 09:07:53 -0600
> >(MDT)
> >Received: from LIST.IEX.NET by LIST.IEX.NET
> (LISTSERV-TCP/IP release 1.8d)
> >with          spool id 692297 for
> SQR-USERS@LIST.IEX.NET; Tue, 28 Aug 2001
> >09:07:12          -0600
> >Received: from fraex10.staples.com ([66.54.210.62])
> by list.iex.net
> >  (8.11.1/8.11.1) with ESMTP id f7SEvBT08081 for
> ><sqr-users@list.iex.net>; Tue, 28 Aug 2001 08:57:11
> -0600 (MDT)
> >Received: by fraex10.staples.com with Internet Mail
> Service (5.5.2650.21)
> >id          <RYPB01K6>; Tue, 28 Aug 2001 11:02:47
> -0400
> >From owner-sqr-users@list.iex.net Tue, 28 Aug 2001
> 08:33:41 -0700
> >X-Mailer: Internet Mail Service (5.5.2650.21)
> >X-MIME-Autoconverted: from quoted-printable to 8bit
> by list.iex.net id
>
=== message truncated ===


__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/