一个BOM展尾阶的例子

#program: bom_extract.4gl

 

DATABASE db
DEFINE  g_key   CHAR(20),
        g_cnt   SMALLINT,
        g_ac    ARRAY[99] OF SMALLINT
MAIN
    WHENEVER ERROR CONTINUE
    FOR g_cnt = 1 TO 99
        LET g_ac[g_cnt] = 0
    END FOR
    DELETE FROM bom_table WHERE bom01=g_key
    CALL bom_ext(0,1,g_key)
END MAIN
FUNCTION bom_ext(p_level,p_qpa,p_bomb01)
DEFINE  p_level  SMALLINT,
        p_bomb01 CHAR(20),
        p_qpa    DEC(15,12),
        l_qpa    DEC(15,12),
        l_ac,l_i SMALLINT,
        l_sql    CHAR(200),
        l_ima08  CHAR(1),
        sr  ARRAY[100] OF RECORD
            bomb01  CHAR(20),
            bomb03  CHAR(20),
            bomb04  DATE,
            bomb05  DATE,
            bomb06  DEC(9,5),
            bomb16  CHAR(1)
            END RECORD

    IF p_level >= 99 THEN
        INSERT INTO bom_table
        VALUES

(g_key,p_level,99,p_bomb01,'18991231','18991231',0,0,'N/A','E',TODAY,'bom_ext')
        RETURN
    END IF
    IF g_cnt >= 199 THEN
        INSERT INTO bom_table
        VALUES

(g_key,p_level,199,p_bomb01,'18991231','18991231',0,0,'N/A','E',TODAY,'bom_ext')
        RETURN
    END IF
    LET g_cnt = g_cnt + 1
    LET p_level = p_level + 1
    LET l_sql = "SELECT bomb01,bomb03,bomb04,bomb05,bomb06,bomb16",
                "  FROM bomb_table,boma_table",
                " WHERE bomb01=boma01 AND bomb01='",p_bomb01,"' ",
                " ORDER by bomb02"
    PREPARE bom_pre FROM l_sql
    DECLARE bom_cur CURSOR WITH HOLD FOR bom_pre
    LET l_ac = 1
    FOREACH bom_cur INTO sr[l_ac].*
        IF  STATUS THEN
            RETURN
        END IF
        LET l_ac = l_ac + 1
        IF  l_ac > 100 THEN
            EXIT FOREACH
        END IF
    END FOREACH

    FOR l_i = 1 TO l_ac-1
        LET l_qpa = p_qpa*sr[l_i].bomb06
        SELECT boma01 FROM boma_table WHERE boma01=sr[l_i].bomb03
        IF  STATUS=100 THEN
            LET g_ac[p_level] = g_ac[p_level] + 1
            SELECT ima08 INTO l_ima08 FROM ima_file WHERE ima01=sr[l_i].bomb03
            IF l_ima08<>'P' THEN  CONTINUE FOR  END IF
            INSERT INTO bom_table
            VALUES(g_key,p_level,g_ac[p_level],sr[l_i].bomb03,sr[l_i].bomb04,
                   sr[l_i].bomb05,l_qpa,sr[l_i].bomb16,sr

[l_i].bomb01,'0',TODAY,'bom_ext')
        ELSE
            CALL bom_ext(p_level,l_qpa,sr[l_i].bomb03)
        END IF
    END FOR
END FUNCTION

table: bom_table
desc : BOM Extract(End node)
create table bom_table
(
bom01    char(20) not null,  {root matno}
bom02    smallint not null,  {level}
bom03    smallint not null,  {no}
bom04    char(20) not null,  {end matno(from bomb03)}
bom05    date     not null,  {star date(from bomb04)}
bom06    date     not null,  {stop date(from bomb05)}
bom07    dec(15,10) not null,{QPA}
bom08    char(1)  not null,  {SU(from bomb16)}
bom09    char(20) not null,  {father matno(from bomb01)}
bom10    char(1)  not null,  {flag  0:OK  E:Error}
bomdate  date     not null,  {create date}
bomcom   char(10) not null   {create come}
);
create unique index bom_01 on bom_table(bom01,bom02,bom03,bom09);
create        index bom_02 on bom_table(bom04);
create        index bom_03 on bom_table(bom05);
create        index bom_04 on bom_table(bomdate);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24955184/viewspace-773358/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24955184/viewspace-773358/