DROP VIEW c_dunning_header_vt;

CREATE OR REPLACE VIEW c_dunning_header_vt
AS
  SELECT dr.ad_client_id,
         dr.ad_org_id,
         dr.isactive,
         dr.created,
         dr.createdby,
         dr.updated,
         dr.updatedby,
         dlt.ad_language,
         dr.c_dunningrun_id,
         dre.c_dunningrunentry_id,
         dr.dunningdate,
         dlt.printname,
         dlt.note                         AS documentnote,
         dre.c_bpartner_id,
         bp.value                         AS bpvalue,
         bp.taxid                         AS bptaxid,
         bp.naics,
         bp.duns,
         oi.c_location_id                 AS org_location_id,
         oi.taxid,
         dre.salesrep_id,
         COALESCE(ubp.name, u.name)       AS salesrep_name,
         bpgt.greeting                    AS bpgreeting,
         bp.name,
         bp.name2,
         bpcgt.greeting                   AS bpcontactgreeting,
         bpc.title,
         bpc.phone,
         NULLIF(bpc.name, bp.name)        AS contactname,
         bpl.c_location_id,
         bp.referenceno,
         l.postal
         || l.postal_add                  AS postal,
         dre.amt,
         dre.qty,
         dre.note,
         COALESCE(oi.logo_id, ci.logo_id) AS logo_id,
         dr.c_dunning_id,
         dr.c_dunninglevel_id             AS c_dunningrun_c_dunninglevel_id,
         dr.description                   AS c_dunningrun_description,
         dr.processed,
         dr.processing,
         dr.sendit,
         dre.ad_org_id                    AS c_dunningrunentry_ad_org_id,
         dre.ad_user_id,
         dre.c_bpartner_location_id,
         dre.c_currency_id,
         dre.c_dunninglevel_id            AS c_dunrunentry_c_dunlevel_id,
         dre.created                      AS c_dunningrunentry_created,
         dre.createdby                    AS c_dunningrunentry_createdby,
         dre.isactive                     AS c_dunningrunentry_isactive,
         dre.processed                    AS c_dunningrunentry_processed,
         dre.updated                      AS c_dunningrunentry_updated,
         dre.updatedby                    AS c_dunningrunentry_updatedby,
         dl.ad_org_id                     AS c_dunninglevel_ad_org_id,
         dl.c_dunning_id                  AS c_dunninglevel_c_dunning_id,
         dl.chargefee,
         dl.chargeinterest,
         dl.c_paymentterm_id,
         dl.daysafterdue,
         dl.daysbetweendunning,
         dl.description                   AS c_dunninglevel_description,
         dl.dunning_printformat_id,
         dl.feeamt,
         dl.interestpercent,
         dl.invoicecollectiontype,
         dl.isactive                      AS c_dunninglevel_isactive,
         dl.issetcreditstop,
         dl.issetpaymentterm,
         dl.isshowalldue,
         dl.isshownotdue,
         dl.isstatement,
         dl.name                          AS c_dunninglevel_name,
         bp.acqusitioncost                AS c_bpartner_acqusitioncost,
         bp.actuallifetimevalue           AS c_bpartner_actuallifetimevalue,
         bp.ad_language                   AS c_bpartner_ad_language,
         bp.ad_orgbp_id                   AS c_bpartner_ad_orgbp_id,
         bp.ad_org_id                     AS c_bpartner_ad_org_id,
         bp.bpartner_parent_id            AS c_bpartner_bpartner_parent_id,
         bp.c_bp_group_id                 AS c_bpartner_c_bp_group_id,
         bp.c_greeting_id                 AS c_bpartner_c_greeting_id,
         bp.c_invoiceschedule_id          AS c_bpartner_invoiceschedule_id,
         bp.c_paymentterm_id              AS c_bpartner_c_paymentterm_id,
         bp.created                       AS c_bpartner_created,
         bp.createdby                     AS c_bpartner_createdby,
         bp.c_taxgroup_id                 AS c_bpartner_c_taxgroup_id,
         bp.deliveryrule                  AS c_bpartner_deliveryrule,
         bp.deliveryviarule               AS c_bpartner_deliveryviarule,
         bp.description                   AS c_bpartner_description,
         bp.dunninggrace                  AS c_bpartner_dunninggrace,
         bp.firstsale                     AS c_bpartner_firstsale,
         bp.flatdiscount                  AS c_bpartner_flatdiscount,
         bp.freightcostrule               AS c_bpartner_freightcostrule,
         bp.invoicerule                   AS c_bpartner_invoicerule,
         bp.isactive                      AS c_bpartner_isactive,
         bp.iscustomer                    AS c_bpartner_iscustomer,
         bp.isdiscountprinted             AS c_bpartner_isdiscountprinted,
         bp.isemployee                    AS c_bpartner_isemployee,
         bp.ismanufacturer                AS c_bpartner_ismanufacturer,
         bp.isonetime                     AS c_bpartner_isonetime,
         bp.ispotaxexempt                 AS c_bpartner_ispotaxexempt,
         bp.isprospect                    AS c_bpartner_isprospect,
         bp.issalesrep                    AS c_bpartner_issalesrep,
         bp.issummary                     AS c_bpartner_issummary,
         bp.istaxexempt                   AS c_bpartner_istaxexempt,
         bp.isvendor                      AS c_bpartner_isvendor,
         bp.logo_id                       AS c_bpartner_logo_id,
         bp.m_discountschema_id           AS c_bpartner_m_discountschema_id,
         bp.m_pricelist_id                AS c_bpartner_m_pricelist_id,
         bp.naics                         AS c_bpartner_naics,
         bp.numberemployees               AS c_bpartner_numberemployees,
         bp.paymentrule                   AS c_bpartner_paymentrule,
         bp.paymentrulepo                 AS c_bpartner_paymentrulepo,
         bp.po_discountschema_id          AS c_bpartnerpo_discountschema_id,
         bp.po_paymentterm_id             AS c_bpartner_po_paymentterm_id,
         bp.po_pricelist_id               AS c_bpartner_po_pricelist_id,
         bp.poreference                   AS c_bpartner_poreference,
         bp.potentiallifetimevalue        AS c_bpartner_potentiallifetimev,
         bp.rating                        AS c_bpartner_rating,
         bp.salesrep_id                   AS c_bpartner_salesrep_id,
         bp.salesvolume                   AS c_bpartner_salesvolume,
         bp.sendemail                     AS c_bpartner_sendemail,
         bp.shareofcustomer               AS c_bpartner_shareofcustomer,
         bp.shelflifeminpct               AS c_bpartner_shelflifeminpct,
         bp.so_creditlimit                AS c_bpartner_so_creditlimit,
         bp.socreditstatus                AS c_bpartner_socreditstatus,
         bp.so_creditused                 AS c_bpartner_so_creditused,
         bp.so_description                AS c_bpartner_so_description,
         bp.totalopenbalance              AS c_bpartner_totalopenbalance,
         bp.updated                       AS c_bpartner_updated,
         bp.updatedby                     AS c_bpartner_updatedby,
         bp.url                           AS c_bpartner_url,
         bpgt.ad_org_id                   AS c_bpartner_greeting_ad_org_id,
         bpgt.isactive                    AS c_bpartner_greeting_isactive,
         bpg.isfirstnameonly              AS c_bpartner_greeting_isfirstna,
         bpgt.name                        AS c_bpartner_greeting_name,
         bpl.ad_org_id                    AS c_bpartner_location_ad_org_id,
         bpl.created                      AS c_bpartner_location_created,
         bpl.createdby                    AS c_bpartner_location_createdby,
         bpl.c_salesregion_id,
         bpl.fax                          AS c_bpartner_location_fax,
         bpl.isactive                     AS c_bpartner_location_isactive,
         bpl.isbillto,
         bpl.isdn,
         bpl.ispayfrom,
         bpl.isremitto,
         bpl.isshipto,
         bpl.name                         AS c_bpartner_location_name,
         bpl.phone                        AS c_bpartner_location_phone,
         bpl.phone2                       AS c_bpartner_location_phone2,
         bpl.updated                      AS c_bpartner_location_updated,
         bpl.updatedby                    AS c_bpartner_location_updatedby,
         l.address1,
         l.address2,
         l.address3,
         l.address4,
         l.ad_org_id                      AS c_location_ad_org_id,
         l.c_city_id,
         l.c_country_id,
         l.city,
         l.created                        AS c_location_created,
         l.createdby                      AS c_location_createdby,
         l.c_region_id,
         l.isactive                       AS c_location_isactive,
         l.postal_add,
         l.regionname,
         l.updated                        AS c_location_updated,
         l.updatedby                      AS c_location_updatedby,
         bpc.ad_org_id                    AS ad_user_ad_org_id,
         bpc.ad_orgtrx_id                 AS ad_user_ad_ad_orgtrx_id,
         bpc.birthday                     AS ad_user_ad_birthday,
         bpc.c_bpartner_id                AS ad_user_c_bpartner_id,
         bpc.c_bpartner_location_id       AS ad_user_c_bpartner_location_id,
         bpc.c_greeting_id                AS ad_user_c_greeting_id,
         bpc.comments                     AS ad_user_comments,
         bpc.created                      AS ad_user_created,
         bpc.createdby                    AS ad_user_createdby,
         bpc.description                  AS ad_user_description,
         bpc.email                        AS ad_user_email,
         bpc.fax                          AS ad_user_fax,
         bpc.isactive                     AS ad_user_isactive,
         bpc.lastcontact                  AS ad_user_lastcontact,
         bpc.lastresult                   AS ad_user_lastresult,
         bpc.name                         AS ad_user_name,
         bpc.phone2                       AS ad_user_phone2,
         bpc.supervisor_id                AS ad_user_supervisor_id,
         bpc.updated                      AS ad_user_updated,
         bpc.updatedby                    AS ad_user_updatedby,
         bpc.value                        AS ad_user_value,
         bpcgt.ad_org_id                  AS c_user_greeting_ad_org_id,
         bpcgt.isactive                   AS c_user_greeting_isactive,
         bpcg.isfirstnameonly             AS c_user_greeting_isfirstnameon,
         bpcgt.name                       AS c_user_greeting_name,
         oi.ad_org_id                     AS ad_orginfo_ad_org_id,
         oi.ad_orgtype_id,
         oi.c_calendar_id,
         oi.created                       AS ad_orginfo_created,
         oi.createdby                     AS ad_orginfo_createdby,
         oi.dropship_warehouse_id,
         oi.duns                          AS ad_orginfo_duns,
         oi.email                         AS ad_orginfo_email,
         oi.fax                           AS ad_orginfo_fax,
         oi.isactive                      AS ad_orginfo_isactive,
         oi.m_warehouse_id,
         oi.parent_org_id,
         oi.phone                         AS ad_orginfo_phone,
         oi.phone2                        AS ad_orginfo_phone2,
         oi.receiptfootermsg,
         oi.supervisor_id,
         oi.updated                       AS ad_orginfo_updated,
         oi.updatedby                     AS ad_orginfo_updatedby,
         u.ad_org_id                      AS salesrep_ad_org_id,
         u.ad_orgtrx_id                   AS salesrep_ad_ad_orgtrx_id,
         u.birthday                       AS salesrep_ad_birthday,
         u.c_bpartner_id                  AS salesrep_c_bpartner_id,
         u.c_bpartner_location_id         AS salesrep_c_bp_location_id,
         u.c_greeting_id                  AS salesrep_c_greeting_id,
         u.comments                       AS salesrep_comments,
         u.created                        AS salesrep_created,
         u.createdby                      AS salesrep_createdby,
         u.description                    AS salesrep_description,
         u.email                          AS salesrep_email,
         u.fax                            AS salesrep_fax,
         u.isactive                       AS salesrep_isactive,
         u.lastcontact                    AS salesrep_lastcontact,
         u.lastresult                     AS salesrep_lastresult,
         u.phone2                         AS salesrep_phone2,
         u.supervisor_id                  AS salesrep_supervisor_id,
         u.updated                        AS salesrep_updated,
         u.updatedby                      AS salesrep_updatedby,
         u.value                          AS salesrep_value,
         ubp.acqusitioncost               AS salesrep_bpartner_acqusitionc,
         ubp.actuallifetimevalue          AS salesrep_bpartner_actuallifet,
         ubp.ad_language                  AS salesrep_bpartner_ad_language,
         ubp.ad_orgbp_id                  AS salesrep_bpartner_ad_orgbp_id,
         ubp.ad_org_id                    AS salesrep_bpartner_ad_org_id,
         ubp.bpartner_parent_id           AS salesrep_bpartner_bp_parent_id,
         ubp.c_bp_group_id                AS salesrep_bp_c_bp_group_id,
         ubp.c_greeting_id                AS salesrep_bp_c_greeting_id,
         ubp.c_invoiceschedule_id         AS salesrep_bp_invoiceschedule_id,
         ubp.c_paymentterm_id             AS salesrep_bp_c_paymentterm_id,
         ubp.created                      AS salesrep_bpartner_created,
         ubp.createdby                    AS salesrep_bpartner_createdby,
         ubp.c_taxgroup_id                AS salesrep_bp_c_taxgroup_id,
         ubp.deliveryrule                 AS salesrep_bpartner_deliveryrule,
         ubp.deliveryviarule              AS salesrep_bpartner_deliveryvia,
         ubp.description                  AS salesrep_bpartner_description,
         ubp.dunninggrace                 AS salesrep_bpartner_dunninggrace,
         ubp.firstsale                    AS salesrep_bpartner_firstsale,
         ubp.flatdiscount                 AS salesrep_bpartner_flatdiscount,
         ubp.freightcostrule              AS salesrep_bpartner_freightcost,
         ubp.invoicerule                  AS salesrep_bpartner_invoicerule,
         ubp.isactive                     AS salesrep_bpartner_isactive,
         ubp.iscustomer                   AS salesrep_bpartner_iscustomer,
         ubp.isdiscountprinted            AS salesrep_bpartner_isdiscountp,
         ubp.isemployee                   AS salesrep_bpartner_isemployee,
         ubp.ismanufacturer               AS salesrep_bpartner_ismanufactu,
         ubp.isonetime                    AS salesrep_bpartner_isonetime,
         ubp.ispotaxexempt                AS salesrep_bpartner_ispotaxexem,
         ubp.isprospect                   AS salesrep_bpartner_isprospect,
         ubp.issalesrep                   AS salesrep_bpartner_issalesrep,
         ubp.issummary                    AS salesrep_bpartner_issummary,
         ubp.istaxexempt                  AS salesrep_bpartner_istaxexempt,
         ubp.isvendor                     AS salesrep_bpartner_isvendor,
         ubp.logo_id                      AS salesrep_bpartner_logo_id,
         ubp.m_discountschema_id          AS salesrep_bp_mdiscountschema_id,
         ubp.m_pricelist_id               AS salesrep_bp_m_pricelist_id,
         ubp.naics                        AS salesrep_bpartner_naics,
         ubp.numberemployees              AS salesrep_bpartner_numberemplo,
         ubp.paymentrule                  AS salesrep_bpartner_paymentrule,
         ubp.paymentrulepo                AS salesrep_bpartner_paymentrupo,
         ubp.po_discountschema_id         AS salesrep_bp_po_discountscm_id,
         ubp.po_paymentterm_id            AS salesrep_bp_po_paymentterm_id,
         ubp.po_pricelist_id              AS salesrep_bp_po_pricelist_id,
         ubp.poreference                  AS salesrep_bpartner_poreference,
         ubp.potentiallifetimevalue       AS salesrep_bpartner_potentialli,
         ubp.rating                       AS salesrep_bpartner_rating,
         ubp.salesrep_id                  AS salesrep_bpartner_salesrep_id,
         ubp.salesvolume                  AS salesrep_bpartner_salesvolume,
         ubp.sendemail                    AS salesrep_bpartner_sendemail,
         ubp.shareofcustomer              AS salesrep_bpartner_shareofcust,
         ubp.shelflifeminpct              AS salesrep_bpartner_shelflifemi,
         ubp.so_creditlimit               AS salesrep_bpartner_so_creditli,
         ubp.socreditstatus               AS salesrep_bpartner_socreditsta,
         ubp.so_creditused                AS salesrep_bpartner_so_creditus,
         ubp.so_description               AS salesrep_bpartner_so_descript,
         ubp.totalopenbalance             AS salesrep_bpartner_totalopenba,
         ubp.updated                      AS salesrep_bpartner_updated,
         ubp.updatedby                    AS salesrep_bpartner_updatedby,
         ubp.url                          AS salesrep_bpartner_url
  FROM   c_dunningrun dr
         JOIN c_dunningrunentry dre
           ON dr.c_dunningrun_id = dre.c_dunningrun_id
         JOIN c_dunninglevel dl
           ON dre.c_dunninglevel_id = dl.c_dunninglevel_id
         JOIN c_dunninglevel_trl dlt
           ON dl.c_dunninglevel_id = dlt.c_dunninglevel_id
         JOIN c_bpartner bp
           ON dre.c_bpartner_id = bp.c_bpartner_id
         LEFT JOIN c_greeting bpg
                ON bp.c_greeting_id = bpg.c_greeting_id
         LEFT JOIN c_greeting_trl bpgt
                ON bp.c_greeting_id = bpgt.c_greeting_id
                   AND dlt.ad_language = bpgt.ad_language
         JOIN c_bpartner_location bpl
           ON dre.c_bpartner_location_id = bpl.c_bpartner_location_id
         JOIN c_location l
           ON bpl.c_location_id = l.c_location_id
         LEFT JOIN ad_user bpc
                ON dre.ad_user_id = bpc.ad_user_id
         LEFT JOIN c_greeting bpcg
                ON bpc.c_greeting_id = bpcg.c_greeting_id
         LEFT JOIN c_greeting_trl bpcgt
                ON bpc.c_greeting_id = bpcgt.c_greeting_id
                   AND dlt.ad_language = bpcgt.ad_language
         JOIN ad_orginfo oi
           ON dr.ad_org_id = oi.ad_org_id
         JOIN ad_clientinfo ci
           ON dr.ad_client_id = ci.ad_client_id
         LEFT JOIN ad_user u
                ON dre.salesrep_id = u.ad_user_id
         LEFT JOIN c_bpartner ubp
                ON u.c_bpartner_id = ubp.c_bpartner_id 
;

