rz_print_all_po_v2.sql

-- adempiere.rz_print_all_po_v2 source

CREATE OR REPLACE VIEW adempiere.rz_print_all_po_v2
AS SELECT ord.c_order_id,
        CASE
            WHEN p.m_product_category_id = ANY (ARRAY[1000050::numeric, 1000049::numeric, 1000012::numeric]) THEN
            CASE
                WHEN p.kst_width_id <> 1000000::numeric THEN COALESCE(p.value::text || ' CM '::text, 'charge'::text)::character varying
                ELSE COALESCE(p.value, 'charge'::character varying)
            END
            ELSE COALESCE(p.value, 'charge'::character varying)
        END AS itemcode,
    COALESCE(p.name, c.name) AS name,
    p.description AS productdescription,
    u.name AS uom,
    ol.datepromised AS datepromised_l,
    ol.qtyentered,
    ol.qtyordered,
    ol.priceentered AS hargasatuan,
    ol.linenetamt AS total,
    ol.qtyentered * ol.priceentered AS su,
    otx.taxamt AS sumppn,
    tx.rate AS rateppn,
    cur.iso_code,
    ol.description,
    cur.c_currency_id,
    (( SELECT sum(pol2.qtyentered) AS sum
           FROM c_orderline pol2
          WHERE pol2.c_order_id = ol.c_order_id AND pol2.m_product_id = ol.m_product_id AND pol2.isfockst = 'N'::bpchar)) * ol.priceentered AS sump,
    ol.isfockst,
    ol.poreference,
    part.name AS customername,
    ord.kst_season,
    concat(loc.address1, loc.city) AS alamat,
    bloc.phone AS telp,
    bloc.fax,
    bloc.name AS cp,
    ord.documentno AS docno,
    ord.dateordered AS tanggal,
    ord.datepromised AS datepromised_h,
    concat(locc.address1, locc.city) AS alamatkirim,
    payterm.name AS paymentterm,
    reflst.name AS refname,
    ord.kst_sltnote AS note,
    ord.note AS onote,
    ord.docstatus,
    ord.kst_etddate AS dd,
    whs.value AS wh,
    ord.c_doctypetarget_id,
    string_agg(DISTINCT pso.upc::text, ';'::text ORDER BY (pso.upc::text)) AS so,
    COALESCE(( SELECT cr.dividerate
           FROM c_conversion_rate cr
          WHERE cr.c_conversiontype_id = ord.c_conversiontype_id AND cr.c_currency_id = ord.c_currency_id AND cr.c_currency_id_to = 100::numeric AND ord.dateordered <= cr.validto AND ord.dateordered >= cr.validfrom
          ORDER BY cr.validfrom DESC
         LIMIT 1), 0::numeric) AS rate,
    ord.dyed,
        CASE
            WHEN whs.value::text ~~ '%AOI 2%'::text THEN 'Jl. TUGU WIJAYA IV, KAW. INDUSTRI WIJAYAKUSUMA, TUGU, SEMARANG 50153 INDONESIA, PHONE +62-248666-4482, FAX. +62-248666-4483'::text
            WHEN whs.value::text ~~ '%AOI 1%'::text THEN 'KAWASAN BERIKAT RUKTI MUKTI BAWANA B-05, Jl. SEMARANG KENDAL KM.12, RANDU GARUT, TUGU, KOTA SEMARANG, JAWA TENGAH 50181, INDONESIA, PHONE +62 24 8664482'::text
            ELSE NULL::text
        END AS alamataoi,
    ord.c_bpartner_id,
    x.status,
    x.created_time,
    ol.c_orderline_id,
    xx.date_promised,
    xx.lock
   FROM c_orderline ol
     LEFT JOIN m_product p ON p.m_product_id = ol.m_product_id
     LEFT JOIN c_charge c ON c.c_charge_id = ol.c_charge_id
     LEFT JOIN c_order ord ON ord.c_order_id = ol.c_order_id
     LEFT JOIN c_currency cur ON ord.c_currency_id = cur.c_currency_id
     LEFT JOIN c_uom u ON u.c_uom_id = ol.c_uom_id
     LEFT JOIN c_ordertax otx ON otx.c_order_id = ord.c_order_id
     LEFT JOIN c_tax tx ON tx.c_tax_id = otx.c_tax_id
     LEFT JOIN kst_width width ON p.kst_width_id = width.kst_width_id
     LEFT JOIN kst_colordetails clr ON p.kst_colordetails_id = clr.kst_colordetails_id
     JOIN m_warehouse whs ON ord.m_warehouse_id = whs.m_warehouse_id
     JOIN c_bpartner part ON part.c_bpartner_id = ord.c_bpartner_id
     JOIN c_bpartner_location bloc ON bloc.c_bpartner_id = ord.c_bpartner_id
     JOIN c_location loc ON loc.c_location_id = bloc.c_location_id
     JOIN c_location locc ON locc.c_location_id = whs.c_location_id
     JOIN c_paymentterm payterm ON payterm.c_paymentterm_id = ord.c_paymentterm_id
     JOIN ad_ref_list reflst ON reflst.value::bpchar = ord.paymentrule
     JOIN ad_reference ref ON ref.ad_reference_id = reflst.ad_reference_id
     LEFT JOIN c_order so ON so.poreference::text = ol.poreference::text
     LEFT JOIN c_orderline sol ON sol.c_order_id = so.c_order_id
     LEFT JOIN m_product pso ON pso.m_product_id = sol.m_product_id
     LEFT JOIN dblink('dbname=import port=5432 host=192.168.51.52 user=postgres password=Becarefulwithme'::text, '
    select
    c_order_id,
    status,
    created_time
    from show_po_status
    where status is true'::text) x(c_order_id numeric, status text, created_time date) ON x.c_order_id = ord.c_order_id
     LEFT JOIN dblink('dbname=import port=5432 host=192.168.51.52 user=postgres password=Becarefulwithme'::text, '
    select
    a.c_orderline_id,
    b.date_promised,
    a.lock
    from m_date_promised a
    left join adt_max_date_promised b on b.c_orderline_id = a.c_orderline_id
    where a.lock != true
'::text) xx(c_orderline_id numeric, date_promised date, lock text) ON xx.c_orderline_id = ol.c_orderline_id
  WHERE ref.ad_reference_id = 195::numeric AND ol.qtydelivered < ol.qtyordered AND (ord.docstatus = ANY (ARRAY['CO'::bpchar, 'DR'::bpchar])) AND ord.issotrx = 'N'::bpchar AND x.status = 't'::text AND (ord.c_doctypetarget_id <> ALL (ARRAY[1000016::numeric, 1000065::numeric, 1000114::numeric, 1000047::numeric, 1000127::numeric, 1000131::numeric, 1000050::numeric, 1000113::numeric, 1000122::numeric, 1000103::numeric, 1000076::numeric, 1000146::numeric, 1000126::numeric])) AND ol.isclosed = 'N'::bpchar AND (ord.c_doctype_id <> ALL (ARRAY[1000016::numeric, 1000065::numeric, 1000114::numeric, 1000047::numeric, 1000127::numeric, 1000131::numeric, 1000050::numeric, 1000113::numeric, 1000122::numeric, 1000103::numeric, 1000076::numeric, 1000146::numeric, 1000126::numeric])) AND ol.isactive = 'Y'::bpchar
  GROUP BY ord.c_order_id, p.m_product_category_id, p.description, u.name, ol.datepromised, ol.qtyentered, ol.qtyordered, ol.priceentered, ol.linenetamt, otx.taxamt, tx.rate, cur.iso_code, ol.description, cur.c_currency_id, ol.isfockst, ol.poreference, p.kst_width_id, p.value, p.name, c.name, ol.c_order_id, ol.m_product_id, part.name, ord.kst_season, bloc.phone, bloc.fax, bloc.name, ord.documentno, ord.dateordered, ord.datepromised, payterm.name, reflst.name, ord.kst_sltnote, ord.note, ord.docstatus, loc.address1, locc.city, loc.city, locc.address1, whs.value, ord.c_doctypetarget_id, ord.c_bpartner_id, x.status, x.created_time, ol.c_orderline_id, xx.date_promised, xx.lock;