aku dah buat datawindow di crosstab dengan sql spt ini:
SELECT distinct STMAT.TAPE_TYPE, STMAT.TAPE_TYPE_NAME,
T.TAPET_DURATION,
NVL(F_BLKMAT_AVAIL_TODATE( to_date( :as_date1, 'yyyymmdd'),
STMAT.TAPE_TYPE), 0) AS TOTTPSTK,
NVL(F_BLKMAT_QTY_IN( to_date( :as_date1, 'yyyymmdd'), to_date( :as_date2,
'yyyymmdd'), STMAT.TAPE_TYPE ), 0) AS TPRTND,
DTL.BORROWER_DEPT,
DTL.QTYOUT,
NVL( F_BLKMAT_OUT( to_date( :as_date1, 'yyyymmdd'), to_date( :as_date2,
'yyyymmdd'), STMAT.TAPE_TYPE ), 0 ) AS TPOUT
FROM STOCK_BLANK_MATERIAL STMAT,
( SELECT TAPE_TYPE, BORROWER_DEPT, COUNT(TAPE_CODE) AS QTYOUT FROM
BLANK_MAT_MOVEMENT_HDR
WHERE TXN_DATE between to_date( :as_date1, 'yyyymmdd') and to_date(
:as_date2, 'yyyymmdd') AND
BORROWER_DEPT IS NOT NULL AND
((RETURNDATE IS NULL AND
TAPE_STATUS = 'O' ) or
(TAPE_STATUS = 'I' and RETURNDATE IS not NULL ))
GROUP BY TAPE_TYPE, BORROWER_DEPT
ORDER BY TAPE_TYPE, BORROWER_DEPT ) DTL, blank_tape_types t
WHERE STMAT.TAPE_TYPE = DTL.TAPE_TYPE (+)
and STMAT.TAPE_TYPE = t.TAPET_CODE (+)
AND STMAT.TAPE_TYPE NOT IN
('','A1','A2','A3','A4','A5','A6','B1','B3','B4','B5','B6','BBBB')
ORDER BY STMAT.TAPE_TYPE
dan hasilnya adalha layout yg pertama
no | format tape | durasi | begining stock | total recd | total out | final
> stock | department
> | IT Acounting HRD
>
> ------------------------------------------------------------------------------------------------------------|--------------------------------------
> 1 CDR 10 100 0 0 0 0 0 0
> 2 15 150 5 1 4 1 0 0
> 3 CDRW 5 50 3 2 1 1 0 1
> 4 DIGITAL 195 100 7 5 2 0 3 2
sekarang gimana caranya supaya saya bisa mindahin kolom final stock ke kolom
terkahir setelah department seperti dibawah ini
no | format tape | durasi | begining stock | total recd | total out | |
> department final stock
> IT Acounting HRD
>
> --------------------------------------------------------------------------------------------------------------------------------------------------
> 1 CDR 10 100 0 0 0 0 0 0
> 2 15 150 5 1 1 0 0 4
> 3 CDRW 5 50 3 2 1 0 1 1
> 4 DIGITAL 195 100 7 5 0 3 2 2
tabel yang digunakan
STOCK_BLANK_MATERIAL :
TAPE_TYPE VARCHAR2(6),
> TAPEF_CODE VARCHAR2(10),
> ARR_DATE DATE,
> QUANTITY NUMBER(7),
> CODE1 NUMBER(7),
> CODE2 NUMBER(7),
> UPDATE_USER VARCHAR2(10) NOT NULL,
> UPDATE_DATE DATE NOT NULL,
> ROW_ID VARCHAR2(18) NOT NULL,
> REMARKS VARCHAR2(254),
> PO_NUM VARCHAR2(40),
> TEMP_ARR_DATE VARCHAR2(20),
> TAPE_TYPE_NAME VARCHAR2(20),
> STATUS_DONE VARCHAR2(2),
> COST_PER_TAPE NUMBER(15,2),
> TAPET_DURATION VARCHAR2(8),
> NUM1 NUMBER,
> UPLD_SEQ NUMBER
dan
TABLE BLANK_MAT_MOVEMENT_HDR
TXN_DATE DATE DEFAULT NULL,
TAPE_TYPE VARCHAR2(6),
TAPEF_CODE VARCHAR2(10),
PROGRAM_NAME VARCHAR2(100),
BORROWER_NAME VARCHAR2(50),
BUDGET_ID VARCHAR2(18),
QUANTITY NUMBER,
BORROWER_DEPT VARCHAR2(25),
BORROWER_DURATION VARCHAR2(8),
REMARKS VARCHAR2(100),
UPDATE_USER VARCHAR2(30) NOT NULL,
UPDATE_DATE DATE DEFAULT SYSDATE NOT NULL,
ROW_ID VARCHAR2(18) NOT NULL,
TAPE_CODE VARCHAR2(18),
TAPE_CATEGORY CHAR(1),
RETURNDATE DATE,
ROW_ID_STOCK VARCHAR2(18),
TAPE_STATUS CHAR(1),
PRG_CODE VARCHAR2(6),
ROW_ID_DTL VARCHAR2(18),
TO_CLIENT CHAR(1) DEFAULT 0,
ARR_DATE DATE,
CHECKIN_REMARKS VARCHAR2(254),
CHECK_IN_REMARKS VARCHAR2(254),
OLD_COSTC_CODE VARCHAR2(50),
ACTION_MODE VARCHAR2(1)
thanks Before,
>
> Iwan Setiawan
> e-mail : [EMAIL PROTECTED]
> Yahoo mesengger : setiawanthea
>
>
--
Iwan Setiawan
e-mail : [EMAIL PROTECTED]
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~-->
Most low income households are not online. Help bridge the digital divide today!
http://us.click.yahoo.com/cd_AJB/QnQLAA/TtwFAA/NhFolB/TM
--------------------------------------------------------------------~->
-----------------------------------------------
INDOPB TURUT BERDUKA CITA UNTUK ACEH & SUMUT
-----------------------------------------------
IndoPB - Indonesia PB User Group
To Post a message, send it to: [EMAIL PROTECTED]
To Unsubscribe,send a blank message to: [EMAIL PROTECTED]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indopb/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
