1
SELECT drl.ad_client_id,
2
drl.ad_org_id,
3
drl.isactive,
4
drl.created,
5
drl.createdby,
6
drl.updated,
7
drl.updatedby,
8
'en_US'::character varying AS ad_language,
9
drl.c_dunningrunline_id,
10
drl.c_dunningrunentry_id,
11
drl.amt,
12
drl.convertedamt,
13
drl.daysdue,
14
drl.timesdunned,
15
drl.interestamt,
16
drl.feeamt,
17
drl.totalamt,
18
drl.c_invoice_id,
19
COALESCE(i.issotrx, p.isreceipt) AS issotrx,
20
COALESCE(i.documentno, p.documentno) AS documentno,
21
COALESCE(i.docstatus, p.docstatus) AS docstatus,
22
COALESCE(i.dateinvoiced, p.datetrx) AS datetrx,
23
COALESCE(i.c_doctype_id, p.c_doctype_id) AS c_doctype_id,
24
COALESCE(dt.printname, dtp.printname) AS documenttype,
25
COALESCE(i.description, p.description) AS description,
26
COALESCE(i.c_currency_id, p.c_currency_id) AS c_currency_id,
27
COALESCE(i.c_campaign_id, p.c_campaign_id) AS c_campaign_id,
28
COALESCE(i.c_project_id, p.c_project_id) AS c_project_id,
29
COALESCE(i.c_activity_id, p.c_activity_id) AS c_activity_id,
30
COALESCE(i.user1_id, p.user1_id) AS user1_id,
31
COALESCE(i.user2_id, p.user2_id) AS user2_id,
32
COALESCE(i.dateacct, p.dateacct) AS dateacct,
33
COALESCE(i.c_conversiontype_id, p.c_conversiontype_id) AS c_conversiontype_id,
34
COALESCE(i.ad_orgtrx_id, p.ad_orgtrx_id) AS ad_orgtrx_id,
35
i.poreference,
36
i.dateordered,
37
i.dateinvoiced,
38
i.isindispute,
39
pt.name AS paymentterm,
40
i.c_charge_id,
41
i.chargeamt,
42
i.totallines,
43
i.grandtotal,
44
i.grandtotal AS amtinwords,
45
i.m_pricelist_id,
46
i.ispaid,
47
p.isallocated,
48
p.tendertype,
49
p.discountamt,
50
drl.c_invoicepayschedule_id,
51
drl.c_payment_id,
52
drl.isindispute AS c_dunningrunline_isindispute,
53
drl.openamt,
54
drl.processed AS c_dunningrunline_processed,
55
i.ad_org_id AS c_invoice_ad_org_id,
56
i.ad_user_id,
57
i.c_bpartner_id AS c_invoice_c_bpartner_id,
58
i.c_bpartner_location_id,
59
i.c_doctypetarget_id,
60
i.c_dunninglevel_id,
61
i.c_order_id AS c_invoice_c_order_id,
62
i.c_payment_id AS c_invoice_c_payment_id,
63
i.created AS c_invoice_created,
64
i.createdby AS c_invoice_createdby,
65
i.dateprinted,
66
i.docaction,
67
i.dunninggrace,
68
i.generateto,
69
i.invoicecollectiontype,
70
i.isactive AS c_invoice_isactive,
71
i.isapproved AS c_invoice_isapproved,
72
i.isdiscountprinted,
73
i.ispayschedulevalid,
74
i.isprinted,
75
i.isselfservice AS c_invoice_isselfservice,
76
i.istaxincluded,
77
i.istransferred,
78
i.m_rma_id,
79
i.paymentrule,
80
i.posted AS c_invoice_posted,
81
i.processedon AS c_invoice_processedon,
82
i.processing,
83
i.ref_invoice_id,
84
i.reversal_id AS c_invoice_reversal_id,
85
i.salesrep_id,
86
i.sendemail,
87
i.updated AS c_invoice_updated,
88
i.updatedby AS c_invoice_updatedby,
89
pt.ad_org_id AS c_paymentterm_ad_org_id,
90
pt.afterdelivery,
91
pt.c_paymentterm_id,
92
pt.description AS c_paymentterm_description,
93
pt.discount,
94
pt.discount2,
95
pt.discountdays,
96
pt.discountdays2,
97
pt.documentnote,
98
pt.fixmonthcutoff,
99
pt.fixmonthday,
100
pt.fixmonthoffset,
101
pt.gracedays,
102
pt.isactive AS c_paymentterm_isactive,
103
pt.isduefixed,
104
pt.isnextbusinessday,
105
pt.isvalid,
106
pt.netday,
107
pt.netdays,
108
pt.paymenttermusage,
109
pt.value AS c_paymentterm_value,
110
p.a_city,
111
p.a_country,
112
p.ad_org_id AS c_payment_ad_org_id,
113
p.a_email,
114
p.a_ident_dl,
115
p.a_name,
116
p.a_state,
117
p.a_street,
118
p.a_zip,
119
p.c_bpartner_id AS c_payment_c_bpartner_id,
120
p.c_charge_id AS c_payment_c_charge_id,
121
p.chargeamt AS c_payment_chargeamt,
122
p.checkno,
123
p.c_invoice_id AS c_payment_c_invoice_id,
124
p.c_order_id AS c_payment_c_order_id,
125
p.c_paymentbatch_id,
126
p.created AS c_payment_created,
127
p.createdby AS c_payment_createdby,
128
p.isactive AS c_payment_isactive,
129
p.isapproved,
130
p.isdelayedcapture,
131
p.isonline,
132
p.isoverunderpayment,
133
p.isprepayment,
134
p.isreconciled,
135
p.isselfservice AS c_payment_isselfservice,
136
p.micr,
137
p.orig_trxid,
138
p.overunderamt,
139
p.payamt,
140
p.ponum,
141
p.posted AS c_payment_posted,
142
p.processed AS c_payment_processed,
143
p.processedon AS c_payment_processedon,
144
p.r_authcode,
145
p.r_authcode_dc,
146
p.r_avsaddr,
147
p.r_avszip,
148
p.r_cvv2match,
149
p.ref_payment_id,
150
p.reversal_id AS c_payment_reversal_id,
151
p.r_info,
152
p.routingno,
153
p.r_pnref,
154
p.r_pnref_dc,
155
p.r_respmsg,
156
p.r_result,
157
p.swipe,
158
p.taxamt,
159
p.trxtype,
160
p.updated AS c_payment_updated,
161
p.updatedby AS c_payment_updatedby,
162
p.voiceauthcode,
163
p.writeoffamt,
164
iv.duedate
165
FROM ((((((c_dunningrunline drl
166
LEFT JOIN c_invoice i ON ((drl.c_invoice_id = i.c_invoice_id)))
167
LEFT JOIN c_invoice_v iv ON (((i.c_invoice_id = iv.c_invoice_id) AND (iv.c_invoicepayschedule_id = drl.c_invoicepayschedule_id))))
168
LEFT JOIN c_doctype dt ON ((i.c_doctype_id = dt.c_doctype_id)))
169
LEFT JOIN c_paymentterm pt ON ((i.c_paymentterm_id = pt.c_paymentterm_id)))
170
LEFT JOIN c_payment p ON ((drl.c_payment_id = p.c_payment_id)))
171
LEFT JOIN c_doctype dtp ON ((p.c_doctype_id = dtp.c_doctype_id)))
172
ORDER BY drl.c_dunningrunline_id;