

SELECT

    *
FROM
    gltrans
LEFT JOIN accnt AS join_accnt ON gltrans_accnt_id = accnt_id

WHERE
    gltrans_accnt_id = (    SELECT
                                costcat_asset_accnt_id 
                            FROM
                                costcat
                            LIMIT 1
                        )

    <? if exists("_as_of") ?>
        AND gltrans_date = <? value("_as_of") ?>
    <? endif ?>   
    AND
    gltrans_deleted = FALSE


ORDER BY gltrans_docnumber ASC;


-- TEST fetch all the order which with the difference shipdate!

-- SELECT  
--     DISTINCT(regexp_replace(join_invhist.invhist_ordnumber, '[-]+.*', '')) as order_number,
--     join_invhist.invhist_transdate::date AS transdate,
--     join_shiphead.shiphead_shipdate AS shipdate,
--     SUM((join_invhist.invhist_unitcost - join_invfifo.invfifo_landedunitcost) * invdetail_qty) AS add_value
--     
-- FROM 
--     invdetail 
-- 
-- LEFT JOIN invhist AS join_invhist ON invdetail.invdetail_invhist_id = join_invhist.invhist_id
-- LEFT JOIN invfifo AS join_invfifo ON invdetail.invdetail_id = join_invfifo.invfifo_invdetail_id
-- LEFT JOIN cohead AS join_cohead ON join_cohead.cohead_number = regexp_replace(join_invhist.invhist_ordnumber, '[-]+.*', '')
-- LEFT JOIN shiphead AS join_shiphead ON join_shiphead.shiphead_order_id = join_cohead.cohead_id
-- 
-- WHERE
--     join_shiphead.shiphead_shipdate != join_invhist.invhist_transdate::date
--     AND
--     join_invhist.invhist_transtype != 'RL'
--     
--     AND join_invhist.invhist_transdate::date  BETWEEN '2012-05-01' AND NOW()::date
--     AND (strpos(join_invhist.invhist_ordnumber,'CM')) = 0
--     AND (strpos(join_invhist.invhist_ordnumber,'XF')) = 0 
-- GROUP BY order_number,join_shiphead.shiphead_shipdate,join_invhist.invhist_transdate::date
-- ORDER BY join_invhist.invhist_transdate::date ASC;

-- 
-- SELECT  
--     
--     join_invhist.invhist_transdate::date AS transdate,
--     join_shiphead.shiphead_shipdate AS shipdate
--     
-- FROM 
--     invdetail 
-- 
-- LEFT JOIN invhist AS join_invhist ON invdetail.invdetail_invhist_id = join_invhist.invhist_id
-- LEFT JOIN cohead AS join_cohead ON join_cohead.cohead_number = regexp_replace(join_invhist.invhist_ordnumber, '[-]+.*', '')
-- LEFT JOIN shiphead AS join_shiphead ON join_shiphead.shiphead_order_id = join_cohead.cohead_id
-- 
-- WHERE
--     join_invhist.invhist_transtype != 'RL'
--     AND
--     join_cohead.cohead_number = 'US10068';