c_dunning_line_vt

SELECT drl.ad_client_id,

    drl.ad_org_id,

    drl.isactive,

    drl.created,

    drl.createdby,

    drl.updated,

    drl.updatedby,

    COALESCE(dt.ad_language, dtp.ad_language) AS ad_language,

    drl.c_dunningrunline_id,

    drl.c_dunningrunentry_id,

    drl.amt,

    drl.convertedamt,

    drl.daysdue,

    drl.timesdunned,

    drl.interestamt,

    drl.feeamt,

    drl.totalamt,

    drl.c_invoice_id,

    COALESCE(i.issotrx, p.isreceipt) AS issotrx,

    COALESCE(i.documentno, p.documentno) AS documentno,

    COALESCE(i.docstatus, p.docstatus) AS docstatus,

    COALESCE(i.dateinvoiced, p.datetrx) AS datetrx,

    COALESCE(i.c_doctype_id, p.c_doctype_id) AS c_doctype_id,

    COALESCE(dt.printname, dtp.printname) AS documenttype,

    COALESCE(i.description, p.description) AS description,

    COALESCE(i.c_currency_id, p.c_currency_id) AS c_currency_id,

    COALESCE(i.c_campaign_id, p.c_campaign_id) AS c_campaign_id,

    COALESCE(i.c_project_id, p.c_project_id) AS c_project_id,

    COALESCE(i.c_activity_id, p.c_activity_id) AS c_activity_id,

    COALESCE(i.user1_id, p.user1_id) AS user1_id,

    COALESCE(i.user2_id, p.user2_id) AS user2_id,

    COALESCE(i.dateacct, p.dateacct) AS dateacct,

    COALESCE(i.c_conversiontype_id, p.c_conversiontype_id) AS c_conversiontype_id,

    COALESCE(i.ad_orgtrx_id, p.ad_orgtrx_id) AS ad_orgtrx_id,

    i.poreference,

    i.dateordered,

    i.dateinvoiced,

    i.isindispute,

    ptt.name AS paymentterm,

    i.c_charge_id,

    i.chargeamt,

    i.totallines,

    i.grandtotal,

    i.grandtotal AS amtinwords,

    i.m_pricelist_id,

    i.ispaid,

    p.isallocated,

    p.tendertype,

    p.discountamt,

    drl.c_invoicepayschedule_id,

    drl.c_payment_id,

    drl.isindispute AS c_dunningrunline_isindispute,

    drl.openamt,

    drl.processed AS c_dunningrunline_processed,

    i.ad_org_id AS c_invoice_ad_org_id,

    i.ad_user_id,

    i.c_bpartner_id AS c_invoice_c_bpartner_id,

    i.c_bpartner_location_id,

    i.c_doctypetarget_id,

    i.c_dunninglevel_id,

    i.c_order_id AS c_invoice_c_order_id,

    i.c_payment_id AS c_invoice_c_payment_id,

    i.created AS c_invoice_created,

    i.createdby AS c_invoice_createdby,

    i.dateprinted,

    i.docaction,

    i.dunninggrace,

    i.generateto,

    i.invoicecollectiontype,

    i.isactive AS c_invoice_isactive,

    i.isapproved AS c_invoice_isapproved,

    i.isdiscountprinted,

    i.ispayschedulevalid,

    i.isprinted,

    i.isselfservice AS c_invoice_isselfservice,

    i.istaxincluded,

    i.istransferred,

    i.m_rma_id,

    i.paymentrule,

    i.posted AS c_invoice_posted,

    i.processedon AS c_invoice_processedon,

    i.processing,

    i.ref_invoice_id,

    i.reversal_id AS c_invoice_reversal_id,

    i.salesrep_id,

    i.sendemail,

    i.updated AS c_invoice_updated,

    i.updatedby AS c_invoice_updatedby,

    ptt.ad_org_id AS c_paymentterm_ad_org_id,

    pt.afterdelivery,

    pt.c_paymentterm_id,

    ptt.description AS c_paymentterm_description,

    pt.discount,

    pt.discount2,

    pt.discountdays,

    pt.discountdays2,

    ptt.documentnote,

    pt.fixmonthcutoff,

    pt.fixmonthday,

    pt.fixmonthoffset,

    pt.gracedays,

    ptt.isactive AS c_paymentterm_isactive,

    pt.isduefixed,

    pt.isnextbusinessday,

    pt.isvalid,

    pt.netday,

    pt.netdays,

    pt.paymenttermusage,

    pt.value AS c_paymentterm_value,

    p.a_city,

    p.a_country,

    p.ad_org_id AS c_payment_ad_org_id,

    p.a_email,

    p.a_ident_dl,

    p.a_name,

    p.a_state,

    p.a_street,

    p.a_zip,

    p.c_bpartner_id AS c_payment_c_bpartner_id,

    p.c_charge_id AS c_payment_c_charge_id,

    p.chargeamt AS c_payment_chargeamt,

    p.checkno,

    p.c_invoice_id AS c_payment_c_invoice_id,

    p.c_order_id AS c_payment_c_order_id,

    p.c_paymentbatch_id,

    p.created AS c_payment_created,

    p.createdby AS c_payment_createdby,

    p.isactive AS c_payment_isactive,

    p.isapproved,

    p.isdelayedcapture,

    p.isonline,

    p.isoverunderpayment,

    p.isprepayment,

    p.isreconciled,

    p.isselfservice AS c_payment_isselfservice,

    p.micr,

    p.orig_trxid,

    p.overunderamt,

    p.payamt,

    p.ponum,

    p.posted AS c_payment_posted,

    p.processed AS c_payment_processed,

    p.processedon AS c_payment_processedon,

    p.r_authcode,

    p.r_authcode_dc,

    p.r_avsaddr,

    p.r_avszip,

    p.r_cvv2match,

    p.ref_payment_id,

    p.reversal_id AS c_payment_reversal_id,

    p.r_info,

    p.routingno,

    p.r_pnref,

    p.r_pnref_dc,

    p.r_respmsg,

    p.r_result,

    p.swipe,

    p.taxamt,

    p.trxtype,

    p.updated AS c_payment_updated,

    p.updatedby AS c_payment_updatedby,

    p.voiceauthcode,

    p.writeoffamt,

    iv.duedate

   FROM (((((((c_dunningrunline drl

     LEFT JOIN c_invoice i ON ((drl.c_invoice_id = i.c_invoice_id)))

     LEFT JOIN c_invoice_v iv ON (((i.c_invoice_id = iv.c_invoice_id) AND (iv.c_invoicepayschedule_id = drl.c_invoicepayschedule_id))))

     LEFT JOIN c_doctype_trl dt ON ((i.c_doctype_id = dt.c_doctype_id)))

     LEFT JOIN c_paymentterm pt ON ((i.c_paymentterm_id = pt.c_paymentterm_id)))

     LEFT JOIN c_paymentterm_trl ptt ON (((i.c_paymentterm_id = ptt.c_paymentterm_id) AND ((ptt.ad_language)::text = (dt.ad_language)::text))))

     LEFT JOIN c_payment p ON ((drl.c_payment_id = p.c_payment_id)))

     LEFT JOIN c_doctype_trl dtp ON ((p.c_doctype_id = dtp.c_doctype_id)))

  WHERE ((COALESCE(dt.ad_language, dtp.ad_language))::text = (COALESCE(dtp.ad_language, dt.ad_language))::text);