rma_reportt1t2v10fabric

 SELECT po.created AS issue_date,
    ''::text AS order_number,
    to_char((po.updated - '07:00:00'::interval), 'yyyy-MM-dd HH24:mi'::text) AS latest_update,
    'Apparel One Indonesia'::text AS buyer,
        CASE
            WHEN ((wh.name)::text ~~ '%AOI 1%'::text) THEN 'Jl. Raya Semarang-Kendal Km 12 Kawasan Rukti Mukti Bawana Blok B5'::text
            WHEN ((wh.name)::text ~~ '%AOI 2%'::text) THEN 'Tugu Wijaya IV Kawasan Industri Wijaya Kusuma Randu Garut'::text
            ELSE ''::text
        END AS t1_address1buy,
    'Kel. Randu Garut'::text AS t1_address2buy,
    'Kec. Tugu'::text AS t1_address3buy,
    loc.address4 AS t1_address4buy,
    'Semarang'::text AS t1_addresscitybuy,
    'Jawa Tengah'::text AS t1_addressstatebuy,
        CASE
            WHEN ((wh.name)::text ~~ '%AOI 1%'::text) THEN '50156'::text
            WHEN ((wh.name)::text ~~ '%AOI 2%'::text) THEN '50153'::text
            ELSE ''::text
        END AS t1_addresspostalcodebuy,
    'Indonesia'::text AS t1_addresscountrybuy,
    po.ad_client_id,
    ad.description AS contact_personbuyer,
    '+62-24843-10051'::text AS contact_nobuyer,
    ad.email AS emailbuyer,
        CASE
            WHEN ((wh.name)::text ~~ '%AOI 1%'::text) THEN 'PT. APPAREL ONE INDONESIA 1'::text
            WHEN ((wh.name)::text ~~ '%AOI 2%'::text) THEN 'PT. APPAREL ONE INDONESIA 2'::text
            ELSE ''::text
        END AS ship_to,
        CASE
            WHEN ((wh.name)::text ~~ '%AOI 1%'::text) THEN 'Jl. Raya Semarang-Kendal Km 12 Kawasan Rukti Mukti Bawana Blok B5 Kel. Randu Garut, Tugu'::text
            WHEN ((wh.name)::text ~~ '%AOI 2%'::text) THEN 'Tugu Wijaya IV Kawasan Industri Wijaya Kusuma Randu Garut, Tugu'::text
            ELSE ''::text
        END AS t1_address1,
        CASE
            WHEN ((wh.name)::text ~~ '%AOI 1%'::text) THEN 'SEMARANG'::text
            WHEN ((wh.name)::text ~~ '%AOI 2%'::text) THEN 'SEMARANG'::text
            ELSE ''::text
        END AS t1_addresscity,
        CASE
            WHEN ((wh.name)::text ~~ '%AOI 1%'::text) THEN 'JAWA TENGAH'::text
            WHEN ((wh.name)::text ~~ '%AOI 2%'::text) THEN 'JAWA TENGAH'::text
            ELSE ''::text
        END AS t1_addressprovince,
        CASE
            WHEN ((wh.name)::text ~~ '%AOI 1%'::text) THEN '50156'::text
            WHEN ((wh.name)::text ~~ '%AOI 2%'::text) THEN '50153'::text
            ELSE ''::text
        END AS t1_addresspostal,
        CASE
            WHEN ((wh.name)::text ~~ '%AOI 1%'::text) THEN 'INDONESIA'::text
            WHEN ((wh.name)::text ~~ '%AOI 2%'::text) THEN 'INDONESIA'::text
            ELSE ''::text
        END AS t1_addresscountry,
    'ATV009'::character varying(40) AS t1_6digit_factorycode,
    pol.c_bpartner_id,
    bp1.name AS seller,
    bp1.name AS actual_manufacturer,
    bp1.value AS t2_6digit_factorycode,
    to_char(COALESCE(( SELECT max(so_1.kst_lcdate) AS max
           FROM c_order so_1
          WHERE (((so_1.poreference)::text = (prl.poreference)::text) AND (so_1.c_doctype_id = (1000051)::numeric))), po.created), 'yyyy-MM-dd'::text) AS po_download_date,
    bp.c_currency_id,
    cur.iso_code AS currency,
    bp.po_paymentterm_id,
    pay.name AS payment_terms,
    po.kst_shippingmethod AS ship_mode,
    co1.name AS country_origin,
        CASE
            WHEN ((po.version)::integer = 0) THEN 'No Change'::text
            WHEN (( SELECT count(1) AS count
               FROM ad_changelog
              WHERE ((ad_changelog.ad_table_id = (260)::numeric) AND (ad_changelog.record_id = pol.c_orderline_id) AND (ad_changelog.ad_column_id = (2208)::numeric) AND ((ad_changelog.newvalue)::boolean = false) AND (ad_changelog.created > po.date_from_version) AND (po.created <= po.date_to_version))) > 0) THEN 'Line Cancellation'::text
            WHEN (( SELECT count(1) AS count
               FROM ad_changelog
              WHERE ((ad_changelog.ad_table_id = (260)::numeric) AND (ad_changelog.record_id = pol.c_orderline_id) AND (ad_changelog.ad_column_id = (2205)::numeric) AND (ad_changelog.created > po.date_from_version) AND (po.created <= po.date_to_version))) > 0) THEN 'Add New Line'::text
            WHEN (( SELECT count(1) AS count
               FROM ad_changelog
              WHERE ((ad_changelog.ad_table_id = (260)::numeric) AND (ad_changelog.record_id = pol.c_orderline_id) AND (po.created > po.date_from_version) AND (po.created <= po.date_to_version))) = 0) THEN 'No Change'::text
            ELSE 'Line Amendment'::text
        END AS line_status,
    po.m_warehouse_id,
    pol.line,
    pol.m_product_id,
    mp.upc AS ref,
    mp.description AS material_name,
    mp.kst_width_id,
    ( SELECT wid1.value
           FROM (m_product mp1
             LEFT JOIN kst_width wid1 ON ((mp1.kst_width_id = wid1.kst_width_id)))
          WHERE ((mp1.m_product_category_id = (1000003)::numeric) AND (mp1.m_product_id = pol.m_product_id) AND ((wid1.value)::text !~~ '%X%'::text))) AS width,
    mp.kst_sourcesize_id,
    ( SELECT wid2.value
           FROM (m_product mp2
             LEFT JOIN kst_width wid2 ON ((mp2.kst_width_id = wid2.kst_width_id)))
          WHERE ((mp2.m_product_category_id <> (1000003)::numeric) AND (mp2.m_product_id = pol.m_product_id))) AS size,
        CASE
            WHEN ((col.value)::text ~~ '%DTM%'::text) THEN (col.value)::text
            ELSE (((col.name)::text || ' '::text) || (col.value)::text)
        END AS material_colour,
        CASE
            WHEN (mp.m_product_category_id = ANY (ARRAY[(1000050)::numeric, (1000052)::numeric, (1000064)::numeric, (1000070)::numeric, (1000027)::numeric])) THEN pol.priceentered
            ELSE pol.priceactual
        END AS unit_price,
        CASE
            WHEN (mp.m_product_category_id = ANY (ARRAY[(1000050)::numeric, (1000052)::numeric, (1000064)::numeric, (1000070)::numeric, (1000027)::numeric])) THEN pol.qtyentered
            ELSE pol.qtyordered
        END AS order_quantity,
    mp.c_uom_id,
        CASE
            WHEN (mp.m_product_category_id = ANY (ARRAY[(1000050)::numeric, (1000052)::numeric, (1000064)::numeric, (1000070)::numeric, (1000027)::numeric])) THEN uom2.uomsymbol
            ELSE uom.uomsymbol
        END AS uom,
    to_char(so.kst_mr_date, 'yyyy-MM-dd'::text) AS buyer_requestdate,
    po.kst_deliverydate AS delivery_date,
    pol.qtydelivered AS delivery_quantity,
    so.kst_season AS season,
    ''::text AS adidas_ordernumber,
    ''::text AS adidas_articlenumber,
    po.ad_org_id,
    po.documentno,
    po.version,
    po.purpose,
    ( SELECT inco.code
           FROM t1t2_incoterm inco
          WHERE ((po.incoterm)::text = (inco.name)::text)) AS incoterm,
    bpf.name AS forwarder,
        CASE
            WHEN (po.c_doctypetarget_id = (1000049)::numeric) THEN 'APP_Bulk_Speed_NOOS_P1'::text
            WHEN (po.c_doctypetarget_id = (1000112)::numeric) THEN 'APP_Bulk_Inline_MTF_P2'::text
            WHEN (po.c_doctypetarget_id = (1000103)::numeric) THEN 'APP_Sample_P1'::text
            WHEN (po.c_doctypetarget_id = (1000050)::numeric) THEN 'APP_Sample_P1'::text
            WHEN (po.c_doctypetarget_id = ANY (ARRAY[(1000087)::numeric, (1000128)::numeric, ('10001321000067'::bigint)::numeric, (1000129)::numeric, (1000135)::numeric])) THEN 'APP_Bulk_Inline_P2'::text
            ELSE ''::text
        END AS ordertype,
    bp1.name2,
    (po.version)::text AS versi,
    'adidas'::text AS t1_customer,
    (wid.value)::text AS width1,
    ' '::text AS weight1,
        CASE
            WHEN ((mp.m_product_category_id = ANY (ARRAY[(1000012)::numeric, (1000048)::numeric])) OR ((mp.value)::text ~~ '%tiecord%'::text)) THEN wid.value
            ELSE ' '::character varying
        END AS length,
        CASE
            WHEN (mp.m_product_category_id = (1000048)::numeric) THEN wid.value
            ELSE ' '::character varying
        END AS height,
        CASE
            WHEN ((mp.m_product_category_id = (1000003)::numeric) AND ((mp.description)::text ~~ '%CM%'::text)) THEN 'CM'::text
            WHEN ((mp.m_product_category_id = (1000003)::numeric) AND ((mp.description)::text !~~ '%CM%'::text)) THEN 'INCH'::text
            ELSE ' '::text
        END AS uom_width,
    ' '::text AS uom_weight,
        CASE
            WHEN ((mp.m_product_category_id = (1000048)::numeric) OR ((mp.value)::text ~~ '%tiecord%'::text)) THEN uom.uomsymbol
            WHEN (mp.m_product_category_id = (1000012)::numeric) THEN 'CM'::character varying
            ELSE ' '::character varying
        END AS uom_length,
        CASE
            WHEN (mp.m_product_category_id = (1000048)::numeric) THEN uom.uomsymbol
            ELSE ' '::character varying
        END AS uom_height,
    string_agg(DISTINCT "left"((so.kst_joborder)::text, (strpos((so.kst_joborder)::text, '::'::text) - 1)), ':'::text) AS model_name,
    COALESCE(
        CASE
            WHEN ((mp.m_product_category_id = (1000012)::numeric) AND ((mp.value)::text ~~ '%RT%'::text)) THEN 'RT'::text
            WHEN ((mp.m_product_category_id = (1000012)::numeric) AND ((mp.value)::text ~~ '%LT%'::text)) THEN 'LT'::text
            WHEN ((mp.m_product_category_id = (1000003)::numeric) AND ((wid.value)::text ~~ '%X%'::text)) THEN (wid.value)::text
            ELSE ''::text
        END, (pol.additional1)::text) AS additional1,
    mp.value,
    loc1.address1 AS t2_address_line_1,
    loc1.address2 AS t2_address_line_2,
    loc1.address3 AS t2_address_line_3,
    loc1.address4 AS t2_address_line_4,
    loc1.city AS t2_address_city,
    loc1.postal AS t2_address_postal,
    co1.name AS t2_address_country,
    po.dyed AS color_matching,
    bp.c_paymentterm_id,
    to_char(po.created, 'yyyy-MM-dd'::text) AS issue_date2,
    po.incoterm AS incoterm2,
        CASE
            WHEN ((po.kst_shippingmethod)::text = 'AC'::text) THEN 'Air-Collect'::text
            WHEN ((po.kst_shippingmethod)::text = 'AP'::text) THEN 'Air-Prepaid'::text
            WHEN ((po.kst_shippingmethod)::text = 'AT'::text) THEN 'Air-Truck Prepaid'::text
            WHEN ((po.kst_shippingmethod)::text = 'CC'::text) THEN 'Courrier-Collect'::text
            WHEN ((po.kst_shippingmethod)::text = 'CP'::text) THEN 'Courrier-Prepaid'::text
            WHEN ((po.kst_shippingmethod)::text = 'SA'::text) THEN 'Sea Air-Prepaid'::text
            WHEN ((po.kst_shippingmethod)::text = 'SP'::text) THEN 'Sea-Prepaid'::text
            WHEN ((po.kst_shippingmethod)::text = 'SC'::text) THEN 'Sea-Collect'::text
            WHEN ((po.kst_shippingmethod)::text = 'T'::text) THEN 'Truck'::text
            ELSE ''::text
        END AS ship_mode2,
    to_char(pol.datepromised, 'yyyy-MM-dd'::text) AS buyer_requestdate2,
    po.c_order_id,
    uom2.uomsymbol AS uom2,
    ( SELECT wid2.value
           FROM (m_product mp2
             LEFT JOIN kst_width wid2 ON ((mp2.kst_width_id = wid2.kst_width_id)))
          WHERE ((mp2.m_product_category_id <> ALL (ARRAY[(1000003)::numeric, (1000012)::numeric, (1000050)::numeric, (1000052)::numeric])) AND (mp2.m_product_id = pol.m_product_id))) AS size2,
    to_char(so.kst_statisticaldate, 'yyyy-MM-dd'::text) AS adidas_crd,
    to_char(so.datepromised, 'yyyy-MM-dd'::text) AS plan_date,
    mp.m_product_category_id,
    pol.c_orderline_id
   FROM (((((((((((((((((((((((((m_requisitionline prl
     LEFT JOIN m_requisition pr ON ((prl.m_requisition_id = pr.m_requisition_id)))
     LEFT JOIN kst_orderdetail odt ON ((prl.m_requisitionline_id = odt.m_requisitionline_id)))
     LEFT JOIN c_orderline pol ON ((odt.c_orderline_id = pol.c_orderline_id)))
     LEFT JOIN c_order po ON ((pol.c_order_id = po.c_order_id)))
     LEFT JOIN c_bpartner bp1 ON ((pol.c_bpartner_id = bp1.c_bpartner_id)))
     LEFT JOIN c_bpartner_location bpl1 ON ((po.c_bpartner_location_id = bpl1.c_bpartner_location_id)))
     LEFT JOIN c_location loc1 ON ((bpl1.c_location_id = loc1.c_location_id)))
     LEFT JOIN c_country co1 ON ((loc1.c_country_id = co1.c_country_id)))
     LEFT JOIN c_bpartner bpf ON ((po.kst_bpartner_forwarder_id = bpf.c_bpartner_id)))
     LEFT JOIN m_warehouse wh ON ((po.m_warehouse_id = wh.m_warehouse_id)))
     LEFT JOIN m_product mp ON ((pol.m_product_id = mp.m_product_id)))
     LEFT JOIN kst_colordetails col ON ((mp.kst_colordetails_id = col.kst_colordetails_id)))
     LEFT JOIN c_uom uom ON ((mp.c_uom_id = uom.c_uom_id)))
     LEFT JOIN c_uom uom2 ON ((pol.c_uom_id = uom2.c_uom_id)))
     LEFT JOIN kst_width wid ON ((mp.kst_width_id = wid.kst_width_id)))
     LEFT JOIN kst_sourcesize si ON ((mp.kst_sourcesize_id = si.kst_sourcesize_id)))
     LEFT JOIN c_order so ON ((((prl.poreference)::text = (so.poreference)::text) AND (so.issotrx = 'Y'::bpchar) AND (so.c_doctype_id <> (1000082)::numeric))))
     LEFT JOIN c_bpartner bp ON ((po.c_bpartner_id = bp.c_bpartner_id)))
     LEFT JOIN c_paymentterm pay ON ((bp.po_paymentterm_id = pay.c_paymentterm_id)))
     LEFT JOIN c_currency cur ON ((bp.c_currency_id = cur.c_currency_id)))
     LEFT JOIN ad_user ad ON ((ad.ad_user_id = po.createdby)))
     LEFT JOIN c_bpartner_location bpl ON ((po.c_bpartner_location_id = bpl.c_bpartner_location_id)))
     LEFT JOIN c_location loc ON ((bpl.c_location_id = loc.c_location_id)))
     LEFT JOIN c_region reg ON ((loc.c_region_id = reg.c_region_id)))
     LEFT JOIN c_country co ON ((loc.c_country_id = co.c_country_id)))
  GROUP BY po.documentno, po.created, po.updated, wh.name, loc.address4, bp.ad_client_id, ad.description, ad.email, pol.c_bpartner_id, bp1.name, bp1.value, bp.c_currency_id, cur.iso_code, bp.po_paymentterm_id, pay.name, po.kst_shippingmethod, co1.name, po.version, pol.c_orderline_id, po.date_from_version, po.date_to_version, po.m_warehouse_id, mp.upc, mp.description, mp.kst_width_id, mp.kst_sourcesize_id, col.value, col.name, mp.c_uom_id, uom.uomsymbol, po.kst_deliverydate, so.kst_season, bp.ad_org_id, po.purpose, po.incoterm, po.kst_bpartner_forwarder_id, po.ordertype, bp1.name2, po.ad_client_id, prl.poreference, so.kst_mr_date, po.ad_org_id, bpf.name, po.c_doctype_id, wid.value, mp.m_product_category_id, mp.value, loc1.address1, loc1.address2, loc1.address3, loc1.address4, loc1.city, loc1.postal, po.dyed, bp.c_paymentterm_id, po.c_order_id,
        CASE
            WHEN ((wh.name)::text ~~ '%AOI 1%'::text) THEN 'Jl. Raya Semarang-Kendal Km 12 Kawasan Rukti Mukti Bawana Blok B5'::text
            WHEN ((wh.name)::text ~~ '%AOI 2%'::text) THEN 'Tugu Wijaya IV Kawasan Industri Wijaya Kusuma Randu Garut'::text
            ELSE ''::text
        END, so.kst_joborder, uom2.uomsymbol, so.kst_statisticaldate, so.datepromised;