# Prog. Version..: '5.00.06-08.07.09(00005)' #
# Pattern name...: cbmr901.4gl
# Descriptions...: ?????極?株??€€??蝝啗」??
# Modify ........:
DATABASE ds
GLOBALS "../../../tiptop/config/top.global"
DEFINE tm RECORD
wc LIKE type_file.chr1000, # Where condition
cb_plant LIKE type_file.chr10, #??銝剖? ??? [TEXTINPUT] DS01,DS02...
cb_year LIKE type_file.num5, #撟游漲 銝??詨? [COMBOBOX] 2008 - 2014
cb_month LIKE type_file.num5 #?遢 銝??詨? [COMBOBOX] 1-12
END RECORD
DEFINE l_flag LIKE type_file.num5 #SMALLINT,
DEFINE g_level_end ARRAY[20] OF LIKE type_file.num5 #SMALLINT
DEFINE g_argv1 LIKE type_file.chr1
DEFINE g_i LIKE type_file.num5 #SMALLINT #count/index for any purpose
DEFINE l_table STRING
DEFINE g_str STRING
DEFINE g_sql STRING
MAIN
OPTIONS
FORM LINE FIRST + 2,
MESSAGE LINE LAST,
PROMPT LINE LAST,
INPUT NO WRAP
DEFER INTERRUPT # Supress DEL key function
LET g_argv1 = ARG_VAL(1)
LET g_towhom = ARG_VAL(2)
LET g_rlang = ARG_VAL(3)
LET g_bgjob = ARG_VAL(4)
LET g_prtway = ARG_VAL(5)
LET g_copies = ARG_VAL(6)
LET tm.wc = ARG_VAL(7)
LET tm.cb_plant = ARG_VAL(8)
LET tm.cb_year = ARG_VAL(9)
LET tm.cb_month = ARG_VAL(10)
LET g_prog ="cbmr901"
IF (NOT cl_user()) THEN
EXIT PROGRAM
END IF
WHENEVER ERROR CALL cl_err_msg_log
IF (NOT cl_setup("CBM")) THEN
EXIT PROGRAM
END IF
## BEGIN: ??Crystal Reports 銝脰畾?- <<<< ?Ｙ?Temp Table >>>> 2010/12/27 Jeffrey Hu *** ##
LET g_sql = "x1.type_file.chr10,",
"x2.type_file.chr18,",
"x3.type_file.chr10,",
"x4.type_file.chr50,",
"x5.type_file.chr50,",
"x6.type_file.chr50,",
"x7.type_file.chr1000,",
"x8.type_file.chr1000,",
"x9.type_file.num20,",
"x10.type_file.chr50"
## END: ??Crystal Reports 銝脰畾?- <<<< ?Ｙ?Temp Table >>>> 2010/12/27 Jeffrey Hu *** ##
LET l_table = cl_prt_temptable('cbmr901',g_sql) CLIPPED
IF l_table = -1 THEN EXIT PROGRAM END IF
LET g_sql = "INSERT INTO ",g_cr_db_str CLIPPED,l_table CLIPPED," VALUES(?,?,?,?,?, ?,?,?,?,? ) "
PREPARE insert_prep FROM g_sql
IF STATUS THEN
CALL cl_err('insert_prep:',status,1) EXIT PROGRAM
END IF
IF cl_null(g_bgjob) OR g_bgjob = 'N' # If background job sw is off
THEN CALL r002_tm(0,0) # Input print condition
ELSE CALL cbmr901() # Read data and create out-file
END IF
END MAIN
FUNCTION r002_tm(p_row,p_col)
DEFINE p_row,p_col LIKE type_file.num5, #SMALLINT,
l_flag LIKE type_file.num5, #SMALLINT,
l_one LIKE type_file.chr1, #CHAR(01)
l_cmd LIKE type_file.chr1000 #CHAR(1000)
IF p_row = 0 THEN LET p_row = 4 LET p_col =14 END IF
#UI
IF g_gui_type MATCHES "[13]" AND fgl_getenv('GUI_VER') = '6' THEN
LET p_row = 7 LET p_col = 20
ELSE
LET p_row = 4 LET p_col = 14
END IF
OPEN WINDOW r002_w AT p_row,p_col
WITH FORM "cbm/42f/cbmr901"
ATTRIBUTE (STYLE = g_win_style CLIPPED)
CALL cl_ui_init()
CALL cl_opmsg('p')
INITIALIZE tm.* TO NULL # Default condition
#### BEGIN: INPUT 霈 FOR SQL 璇辣撘?
LET tm.cb_plant = 'DS01'
LET tm.cb_year = YEAR(TODAY)
LET tm.cb_month = MONTH(TODAY)
#### END: INPUT 霈 FOR SQL 璇辣撘?
LET g_pdate = g_today
LET g_rlang = g_lang
LET g_bgjob = 'N'
LET g_copies = '1'
WHILE TRUE
INPUT BY NAME tm.cb_plant,tm.cb_year,tm.cb_month
WITHOUT DEFAULTS
AFTER FIELD cb_plant
IF cl_null(tm.cb_plant) THEN
NEXT FIELD cb_year
END IF
AFTER FIELD cb_year
IF cl_null(tm.cb_year) THEN
NEXT FIELD cb_month
END IF
ON ACTION CONTROLZ
CALL cl_show_req_fields()
ON ACTION CONTROLG
CALL cl_cmdask()
ON IDLE g_idle_seconds
CALL cl_on_idle()
CONTINUE INPUT
ON ACTION about
CALL cl_about()
ON ACTION help
CALL cl_show_help()
ON ACTION exit
LET INT_FLAG = 1
EXIT INPUT
END INPUT
IF g_action_choice = "locale" THEN
LET g_action_choice = ""
CALL cl_dynamic_locale()
CONTINUE WHILE
END IF
IF INT_FLAG THEN
LET INT_FLAG = 0
CLOSE WINDOW r002_w
EXIT PROGRAM
END IF
IF g_bgjob = 'Y' THEN
SELECT zz08 INTO l_cmd FROM zz_file #get exec cmd (fglgo xxxx)
WHERE zz01='cbmr901'
IF SQLCA.sqlcode OR cl_null(l_cmd) THEN
CALL cl_err('cbmr901','9031',1)
ELSE
LET tm.wc =cl_replace_str(tm.wc, "'", "\"")
LET l_cmd = l_cmd CLIPPED, #(at time fglgo xxxx p1 p2 p3)
" '",g_pdate CLIPPED,"'",
" '",g_towhom CLIPPED,"'",
" '",g_lang CLIPPED,"'",
" '",g_bgjob CLIPPED,"'",
" '",g_prtway CLIPPED,"'",
" '",g_copies CLIPPED,"'",
" '",tm.wc CLIPPED,"'",
" '",tm.cb_plant CLIPPED,"'",
" '",tm.cb_year CLIPPED,"'",
" '",tm.cb_month CLIPPED,"'"
CALL cl_cmdat('cbmr901',g_time,l_cmd) # Execute cmd at later time
END IF
CLOSE WINDOW r002_w
EXIT PROGRAM
END IF
CALL cl_wait()
CALL cbmr901()
ERROR ""
END WHILE
CLOSE WINDOW r002_w
END FUNCTION
FUNCTION cbmr901()
DEFINE l_sql STRING
DEFINE l_prog LIKE type_file.chr10
DEFINE sr RECORD
x1 LIKE type_file.chr10,
x2 LIKE type_file.chr18,
x3 LIKE type_file.chr10,
x4 LIKE type_file.chr50,
x5 LIKE type_file.chr50,
x6 LIKE type_file.chr50,
x7 LIKE type_file.chr1000,
x8 LIKE type_file.chr1000,
x9 LIKE type_file.num20,
x10 LIKE type_file.chr50
END RECORD
CALL cl_del_data(l_table)
LET l_prog = g_prog
## BEGIN: SQL COMMAND WRITE HERE
LET l_sql = " select '",tm.cb_plant CLIPPED,"' X1,tlf905 X2,to_char(tlf06,'yyyy/mm/dd') X3,to_char(tlf906) X4,tlf62 X5,tlf01 X6,ima02 X7,ima021 X8,(tlf10*tlf907*(-1)) X9, USERNAME X10 ",
" from ",
tm.cb_plant CLIPPED,".ima_file,",
tm.cb_plant CLIPPED,".tlf_file,",
tm.cb_plant CLIPPED,".sfp_file,",
" DS.STAFF_file ",
" where ima01 = tlf01 and tlf13 in ('asfi512','asfi527') and tlf907 <> 0 ",
" AND year(tlf06) = '",tm.cb_year CLIPPED,"' AND month(tlf06) = '",tm.cb_month CLIPPED,"' ",
" and tlf905 = sfp01 and sfpuser = USERID(+) order by 2,3 "
## END: SQL COMMAND WRITE HERE
PREPARE r002_prepare1 FROM l_sql
IF SQLCA.sqlcode != 0 THEN
CALL cl_err('prepare:',SQLCA.sqlcode,1) EXIT PROGRAM
END IF
DECLARE r002_curs1 CURSOR FOR r002_prepare1
INITIALIZE sr.* TO NULL
#BEGIN: 頛詨鞈?
FOREACH r002_curs1 INTO sr.*
IF SQLCA.sqlcode != 0 THEN
CALL cl_err('foreach:',SQLCA.sqlcode,1)
EXIT FOREACH
END IF
EXECUTE insert_prep USING sr.x1,sr.x2,sr.x3,sr.x4,sr.x5,
sr.x6,sr.x7,sr.x8,sr.x9,sr.x10
END FOREACH
#END: 頛詨鞈?
LET g_prog = 'cbmr901'
LET g_str= tm.wc,";",l_prog
LET l_sql = "SELECT * FROM ", g_cr_db_str CLIPPED, l_table CLIPPED
CALL cl_prt_cs3('cbmr901','cbmr901',l_sql,g_str)
LET g_prog = l_prog
END FUNCTION
全站熱搜
留言列表
站台介紹 (1)