Post a Comment On: Programming Solution

/* program take input from two excel sheets and show the same item with same rate and their   corresponding value in same row ,*/
/* if not found same item with same rate , then fetch the first record for the item from second sheet and show this record with not matched record */
/* item found in one sheet but not in another sheet will show with the zero value for the sheet for which it is not found */


DEFINE TEMP-TABLE ttinv_st NO-UNDO
    FIELD st_part AS CHARACTER 
    FIELD st_qty AS DECIMAL  FORMAT  "->>>,999,999.999"
    FIELD st_rate AS DECIMAL  FORMAT  "->>>,999,999.999".
DEFINE TEMP-TABLE ttinv_st2 NO-UNDO
    FIELD st2_part AS CHARACTER 
    FIELD st2_qty AS DECIMAL  FORMAT  "->>>,999,999.999"
    FIELD st2_rate AS DECIMAL  FORMAT  "->>>,999,999.999".

DEFINE TEMP-TABLE ttinv_cs NO-UNDO
    FIELD cs_part AS CHARACTER 
    FIELD cs_qty AS DECIMAL  FORMAT  "->>>,999,999.999"
    FIELD cs_rate AS DECIMAL  FORMAT  "->>>,999,999.999".
   
DEFINE TEMP-TABLE ff_temp NO-UNDO
    FIELD ff_part AS CHARACTER 
    FIELD ff_qty AS DECIMAL  FORMAT  "->>>,999,999.999"
    FIELD ff_rate AS DECIMAL  FORMAT  "->>>,999,999.999"
    FIELD kk_part AS CHARACTER 
    FIELD kk_qty AS DECIMAL  FORMAT  "->>>,999,999.999"
    FIELD kk_rate AS DECIMAL  FORMAT  "->>>,999,999.999".
EMPTY TEMP-TABLE ttinv_cs.
EMPTY TEMP-TABLE ttinv_st.
EMPTY TEMP-TABLE ff_temp.
INPUT  FROM "C:\Documents and Settings\sachingaur\Desktop\again_sheet\sachinstand.csv".
REPEAT:
   CREATE ttinv_st2.
   IMPORT DELIMITER "," ttinv_st2.
END.
INPUT CLOSE.
/*FIND FIRST ttinv_st NO-LOCK NO-ERROR.
IF AVAILABLE ttinv_st THEN
    MESSAGE "ttinv_st record available"
        VIEW-AS ALERT-BOX INFO BUTTONS OK.*/
INPUT  FROM "C:\Documents and Settings\sachingaur\Desktop\again_sheet\gaur.csv".   /* sheet which have single record for item */
REPEAT:
    CREATE ttinv_cs.
    IMPORT DELIMITER "," ttinv_cs.
END.
INPUT CLOSE.
FOR EACH ttinv_st2 NO-LOCK:
    ASSIGN st2_part = TRIM(st2_part).
END.
FOR EACH ttinv_cs NO-LOCK:
    ASSIGN cs_part = TRIM(cs_part).
END.
FOR EACH ttinv_st2 BREAK BY st2_part:
    FIND FIRST ttinv_st WHERE st_part = st2_part AND st_rate = st2_rate NO-LOCK NO-ERROR.
    IF AVAIL ttinv_st THEN
    DO:
        st_qty = st_qty + st2_qty.
    END.
    ELSE
    DO:
        CREATE ttinv_st.
        ASSIGN
            st_part = st2_part
            st_qty  = st2_qty
            st_rate = st2_rate.
    END.
END.
FOR EACH ttinv_cs WHERE cs_part <> '' BREAK BY cs_part:
    FIND FIRST ttinv_st WHERE st_part = cs_part AND st_rate = cs_rate NO-LOCK NO-ERROR.
    IF AVAIL ttinv_st  THEN
    DO:
        CREATE ff_temp.
        ASSIGN
            ff_part = st_part
            ff_qty  = st_qty
            ff_rate = st_rate
            kk_part = cs_part
            kk_qty  = cs_qty
            kk_rate = cs_rate.
    END.
    ELSE
    DO:
        FIND FIRST ttinv_st WHERE st_part = cs_part NO-LOCK NO-ERROR.
        IF AVAIL ttinv_st THEN
        DO:
            CREATE ff_temp.
            ASSIGN
                ff_part = st_part
                ff_qty  = st_qty
                ff_rate = st_rate
                kk_part = cs_part
                kk_qty  = cs_qty
                kk_rate = cs_rate.
        END.
        ELSE
        DO:
            CREATE ff_temp.
            ASSIGN
                ff_part = "'" + cs_part
                ff_qty  = 0
                ff_rate = 0
                kk_part = cs_part
                kk_qty  = cs_qty
                kk_rate = cs_rate.
        END. /* else of avail ttinv_st 2 */
    END. /* ELSE OF IF AVAIL tinv_st */
END.  /* for each ttinv_cs */
FOR EACH ttinv_st WHERE st_part <> '' BREAK BY st_part:
    FIND FIRST ff_temp WHERE ff_part = st_part AND ff_qty = st_qty AND ff_rate = st_rate NO-LOCK NO-ERROR.
    IF AVAIL ff_temp THEN.
    ELSE
    DO:
         CREATE ff_temp.
         ASSIGN
            ff_part = st_part
            ff_qty  = st_qty
            ff_rate = st_rate
            kk_part = "'" + st_part
            kk_qty  = 0
            kk_rate = 0.
    END.
END.
RUN printoutput.
PROCEDURE printoutput:
    OUTPUT TO "C:\Documents and Settings\sachingaur\Desktop\again_sheet\check_rate_new12.xls".
    PUT UNFORMATTED
        "St_item"         "~011"
        "St_qty"         "~011"
        "St_rate"         "~011"
        "cs_item"         "~011"
        "cs_qty"         "~011"
        "cs_rate"         "~011"
        SKIP.
    FOR EACH ff_temp BREAK BY ff_part:
        PUT UNFORMATTED
           ff_part       "~011"
           ff_qty        "~011"
           ff_rate       "~011"
           kk_part       "~011"
           kk_qty        "~011"
           kk_rate       "~011"
        SKIP.
    END.
    OUTPUT CLOSE.   
END PROCEDURE.