iDempiere en - User contributions [en]

Queries:Report Detail Movement

2021-07-28T05:58:35Z

Ngordon7000: Ngordon7000 moved page Queries:Report Detail Movement to Queries: Report Detail Movement

#REDIRECT [[Queries: Report Detail Movement]]

Queries: Report Detail Movement

2021-07-28T05:58:35Z

Ngordon7000: Ngordon7000 moved page Queries:Report Detail Movement to Queries: Report Detail Movement

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

* '''Maintainer:''' [[User:Hieplq|Hieplq]] ([[User talk:Hieplq|talk]])
* '''Request from:''' [[User:Nakarat|Nakarat]]

this query for report all movement of product in range time.

what thing you get after run it?

1. you must enter start_date, end_date, client_id

2. query all m_transaction record for product HAS TRANSACTION in start_date and end_date.

3. each product has below info:

+ first line for start balance info. it sum form first record to record at start_date.

+ each transaction between start_date and end_date, has info join from document table.

+ for end balance can archive by jasper report.

for test record please refer wiki:[[Jasper_Test_Sql]]

* you can extend it with add filter by product category, org, locator,...

also query plus price, code info

<pre>
select
pro.name as pro_name, pro.m_product_id,

trans.movementdate as move_date,

case when trans.movementdate is null then null
when trans.m_inventoryline_id is not null then doc_inv.documentno
when trans.m_movementline_id is not null then doc_move.documentno
when trans.m_inoutline_id is not null then doc_inout.documentno
when trans.m_productionline_id is not null then doc_pro.documentno
else null end as doc_no,

p_cate.name as cate_name, p_cate.m_product_category_id as cate_id, pro.value as pro_value,

case when trans.movementdate is null then null
when trans.m_inventoryline_id is not null then doc_inv.doc_name
when trans.m_movementline_id is not null then doc_move.doc_name
when trans.m_inoutline_id is not null then doc_inout.doc_name
when trans.m_productionline_id is not null then doc_pro.doc_name
else null end as doc_name,

sum(trans.movementqty ) as movementqty ,

sum(case when trans.movementtype in ('I+','P+','V+','C+','W+','M+') then trans.movementqty else 0 end) as amout_in,

sum(case when trans.movementtype in ('I-','P-','V-','C-','W-','M-') then -1 * trans.movementqty else 0 end) as amout_out,

case when trans.movementdate is null then null
when trans.m_inventoryline_id is not null then trans.m_inventoryline_id
when trans.m_movementline_id is not null then trans.m_movementline_id
when trans.m_inoutline_id is not null then trans.m_inoutline_id
when trans.m_productionline_id is not null then trans.m_productionline_id
else null end as line_id
from
(select
AD_Org_ID, m_product_id, m_locator_id,
case when movementdate< $P{StartDate} then null else movementdate END as movementdate,
movementtype, movementqty, m_inventoryline_id, m_movementline_id, m_inoutline_id, m_productionline_id
from
m_transaction
where
movementdate<=$P{EndDate} and ad_client_id=$P{AD_CLIENT_ID}
and m_product_id in (select DISTINCT m_product_id
from
m_transaction
where
movementdate>=$P{StartDate} and movementdate<=$P{EndDate} and ad_client_id=$P{AD_CLIENT_ID})
) as trans
INNER JOIN (SELECT m_product_category_id, m_product_id, value, name
FROM m_product
where m_product_id in (select DISTINCT m_product_id
from
m_transaction
where
movementdate>=$P{StartDate} and movementdate<=$P{EndDate} and ad_client_id=$P{AD_CLIENT_ID})
) AS pro on trans.m_product_id=pro.m_product_id
inner join m_product_category p_cate on pro.m_product_category_id=p_cate.m_product_category_id
inner join m_locator kho on trans.m_locator_id=kho.m_locator_id

inner join AD_Org bp on trans.AD_Org_ID=bp.AD_Org_ID

left outer join (select m_inventoryline.m_inventoryline_id, m_inventory.movementdate, m_inventory.documentno, c_doctype.name as doc_name from m_inventoryline
inner join m_inventory on m_inventory.m_inventory_id = m_inventoryline.m_inventory_id
inner join c_doctype on c_doctype.c_doctype_id = m_inventory.c_doctype_id
) as doc_inv on trans.m_inventoryline_id = doc_inv.m_inventoryline_id
left outer join (select m_movementline.m_movementline_id, m_movement.movementdate, m_movement.documentno, c_doctype.name as doc_name from m_movementline
inner join m_movement on m_movement.m_movement_id = m_movementline.m_movement_id
inner join c_doctype on c_doctype.c_doctype_id = m_movement.c_doctype_id
) as doc_move on trans.m_movementline_id = doc_move.m_movementline_id
left outer join (select m_inoutline.m_inoutline_id, m_inout.movementdate, m_inout.documentno, c_doctype.name as doc_name from m_inoutline
inner join m_inout on m_inout.m_inout_id = m_inoutline.m_inout_id
inner join c_doctype on c_doctype.c_doctype_id = m_inout.c_doctype_id
) as doc_inout on trans.m_inoutline_id = doc_inout.m_inoutline_id
left outer join (select m_productionline.m_productionline_id, m_production.movementdate, m_production.documentno, 'production'::varchar as doc_name from m_productionline
inner join m_production on m_production.m_production_id = m_productionline.m_production_id
) as doc_pro on trans.m_productionline_id = doc_pro.m_productionline_id

Group By

trans.movementdate, p_cate.name, p_cate.m_product_category_id, pro.name, pro.m_product_id, pro.value,

case when trans.movementdate is null then null
when trans.m_inventoryline_id is not null then trans.m_inventoryline_id
when trans.m_movementline_id is not null then trans.m_movementline_id
when trans.m_inoutline_id is not null then trans.m_inoutline_id
when trans.m_productionline_id is not null then trans.m_productionline_id
else null END,

case when trans.movementdate is null then null
when trans.m_inventoryline_id is not null then doc_inv.documentno
when trans.m_movementline_id is not null then doc_move.documentno
when trans.m_inoutline_id is not null then doc_inout.documentno
when trans.m_productionline_id is not null then doc_pro.documentno
else null end ,

case when trans.movementdate is null then null
when trans.m_inventoryline_id is not null then doc_inv.doc_name
when trans.m_movementline_id is not null then doc_move.doc_name
when trans.m_inoutline_id is not null then doc_inout.doc_name
when trans.m_productionline_id is not null then doc_pro.doc_name
else null end

ORDER BY
pro.name, move_date NULLS first, doc_no
</pre>

Code Scratchpad: Queries: Stock

2021-07-28T05:57:52Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Stock to Queries: Stock

#REDIRECT [[Queries: Stock]]

Queries: Stock

2021-07-28T05:57:52Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Stock to Queries: Stock

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Product =

== Product grouped by product type ==

<nowiki>select
a.ad_client_id, a.producttype,count(*)
from
m_product a
group by
a.ad_client_id, a.producttype
order by a.ad_client_id, a.producttype;</nowiki>

= Cost =

== Products with zero cost ==

<nowiki>
drop view if exists zz_product_qtys_v;

create or replace view zz_product_qtys_v as
SELECT
l.ad_client_id,
p.m_product_id,
p.name AS product_name,
p.value AS product_code,
sum(s.qtyonhand) AS qtyonhand,
sum(s.qtyreserved) AS qtyreserved,
sum(s.qtyordered) AS qtyordered,
(sum(s.qtyonhand) - sum(s.qtyreserved)) AS qtyavailable
FROM
m_product p, m_storage s, m_locator l, m_warehouse w, ad_org o
WHERE
p.m_product_id = s.m_product_id AND
s.m_locator_id = l.m_locator_id AND
l.m_warehouse_id = w.m_warehouse_id AND
l.ad_org_id = o.ad_org_id
GROUP BY
l.ad_client_id, p.m_product_id, p.name, p.value
;

select * from zz_product_qtys_v;

select * from zz_product_qtys_withcosts_v;

drop view if exists zz_product_qtys_withcosts_v;

create or replace view zz_product_qtys_withcosts_v as
SELECT
p.ad_client_id,
cl.name as client_name,
p.m_product_id,
p.value as product_code,
p.name as product_name,
c.m_costelement_id,
c.m_costelement_name,
c.currentcostprice,
c.currentqty,
c.cumulatedamt,
c.cumulatedqty,
q.qtyonhand,
q.qtyreserved,
q.qtyordered,
q.qtyavailable,
pl.name as pricelist_name,
plv.name as pricelistversion_name,
prc.pricelist
from
m_product p
left join
rv_cost c on c.m_product_id = p.m_product_id
LEFT JOIN
zz_product_qtys_v q ON c.m_product_id = q.m_product_id
-- Just to get the price
left join m_productprice prc on
prc.ad_client_id = p.ad_client_id and /* double check we're in the right client */
--prc.m_pricelist_version_id = 1000003 and /* */
prc.m_product_id = p.m_product_id
left join
m_pricelist_version plv on plv.m_pricelist_version_id = prc.m_pricelist_version_id
left join
m_pricelist pl on plv.m_pricelist_id = pl.m_pricelist_id
join
ad_client cl on p.ad_client_id = cl.ad_client_id
;

select * from zz_product_qtys_withcosts_v where product_code = '';

------------------------------------------------------------------
-- Main query to get the zero costs
------------------------------------------------------------------
select
client_name as clientname, product_code, product_name, max(qtyonhand) as qtyonhand, max(pricelist) as pricelist
from
zz_product_qtys_withcosts_v s
where
client_name like '%client name%' and
-- Make sure we get at least one record
--coalesce(pricelistversion_name, '11/08/2013') = '11/08/2013' and
--m_costelement_name = 'Last Invoice' and
m_product_id not in (
-- 1. Products with costs
select distinct m_product_id from zz_product_qtys_withcosts_v s
where
s.client_name like '%client name%' and
-- Make sure we get at least one record
coalesce(s.pricelistversion_name, '11/08/2013') = '11/08/2013' and
s.m_costelement_name = 'Last Invoice' and
coalesce(s.currentcostprice, 0) <> 0
)
group by client_name, product_code, product_name
order by product_code;

------------------------------------------------------------------
-- Some working queries
------------------------------------------------------------------
-- 1. Products with no cost records at all
select * from zz_product_qtys_withcosts_v s
where
s.client_name like '%client name%' and
-- Make sure we get at least one record
coalesce(s.pricelistversion_name, '11/08/2013') = '11/08/2013' and
s.currentcostprice is null;

-- 2. Products with zero cost, but there is something interesting about the product
select * from zz_product_qtys_withcosts_v s
where
s.client_name like '%client name%' and
-- Make sure we get at least one record
coalesce(s.pricelistversion_name, '11/08/2013') = '11/08/2013' and
s.m_costelement_name = 'Last Invoice' and
s.currentcostprice = 0 and (
s.qtyonhand <> 0 OR
s.qtyreserved <> 0 or
s.qtyordered <> 0
);

</nowiki>

== Various cost related ==

<nowiki>select
a.*
from
m_cost a
where
--a.m_product_id = 1035015;
a.m_product_id =
( select m_product_id from m_product where value = 'Test-03' );

update m_cost
set currentcostprice = 50.00
where
m_product_id =
( select m_product_id from m_product where value = 'Test-02' );

select
a.*
from
m_costdetail a
where
--a.m_product_id = 1035015;
a.m_product_id = 1046609;

delete from m_cost where m_product_id in ( select m_product_id from m_product where value = 'Test-03' );
delete from m_costdetail where m_product_id in ( select m_product_id from m_product where value = 'Test-03' ) ;</nowiki>

== Cost element ==

<nowiki>select
count(*)
--cl.name as clientname,
--ele.name as costelementname,
--prod.value as productvalue,
--prod.name as productname,
--a.*,
--a.currentqty, a.currentcostprice
from
m_cost a
join m_costelement ele on a.m_costelement_id = ele.m_costelement_id
join m_product prod on a.m_product_id = prod.m_product_id
join ad_client cl on a.ad_client_id = cl.ad_client_id
where
a.ad_client_id = 1000000 and
ele.name like '%Average%'
--order by
-- prod.value, ele.name ;
--a.m_product_id = 1035015;
;</nowiki>

== Change costing method ==

Still in testing.

<nowiki>select * into bk_m_cost_RQ100022 from m_cost;

-- Identify records for "Exclude all items where there is an average cost entry already (81 items)"
--select
-- distinct a.m_product_id
-- --cl.name as clientname,
-- --ele.name as costelementname,
-- --prod.value as productvalue,
-- --prod.name as productname,
-- --a.currentqty, a.currentcostprice
--from
-- m_cost a
--join m_costelement ele on a.m_costelement_id = ele.m_costelement_id
--join m_product prod on a.m_product_id = prod.m_product_id
--join ad_client cl on a.ad_client_id = cl.ad_client_id
--where
-- a.ad_client_id = 1000000 and
-- ele.name like '%Average%';

-- Update
update
m_cost
set
m_costelement_id = ( select m_costelement_id from m_costelement where name = 'Average Invoice' and ad_client_id = 1000000 )
where
ad_client_id = 1000000 and
-- Exclude all items where there is an average cost entry already (81 items)
m_product_id not in (
1036146,1037058,1036065,1036427,1045882,1036542,1036747,1038486,1036979,1036615,1037721,1036974,1035066,1037092,1037012,1036742,1046172,1036540,
1036793,1036756,1036487,1036431,1036632,1046632,1035286,1036940,1035031,1037884,1036962,1036826,1046011,1036456,1046076,1036459,1045894,1036054,
1036095,1036163,1035074,1036714,1035032,1045866,1038110,1036750,1038118,1037015,1046361,1046135,1035518,1035271,1037014,1045916,1035025,1036429,
1045933,1037305,1036748,1035302,1036665,1036637,1036517,1036541,1036498,1036343,1035061,1038411,1036843,1036464,1036936,1036818,1038447,1036630,
1036068,1036098,1036100,1037071,1038501,1036461,1037873,1037013,1036533
);
</nowiki>

Code Scratchpad: Queries:Report Detail Movement

2021-07-28T05:57:22Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries:Report Detail Movement to Queries:Report Detail Movement

#REDIRECT [[Queries:Report Detail Movement]]

Queries: Report Detail Movement

2021-07-28T05:57:22Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries:Report Detail Movement to Queries:Report Detail Movement

Code Scratchpad: Queries: Invoices, Orders and Quotes

2021-07-28T05:57:01Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Invoices, Orders and Quotes to Queries: Invoices, Orders and Quotes

#REDIRECT [[Queries: Invoices, Orders and Quotes]]

Queries: Invoices, Orders and Quotes

2021-07-28T05:57:01Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Invoices, Orders and Quotes to Queries: Invoices, Orders and Quotes

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Orders =

<nowiki>select * from
c_order a
join c_orderline b on a.c_order_id = b.c_order_id
where
a.documentno='21686';

select a.documentno, b.line, b.qtyentered, b.pricelimit, b.priceentered, b.pricelist, b.priceactual,
b.discount, b.pricelist - b.priceactual as discountAmt, b.linenetamt from
c_order a
join c_orderline b on a.c_order_id = b.c_order_id
where
a.documentno='24307'
order by b.line;

select a.c_order_id from c_order a where a.documentno='24307';

select a.ad_client_id, dt.name, count(*)
from
c_order a
join c_orderline b on a.c_order_id = b.c_order_id
join c_doctype dt on a.c_doctypetarget_id = dt.c_doctype_id
group by a.ad_client_id, dt.name
order by dt.name
;</nowiki>

== With more detail ==

<nowiki>select
a.ad_client_id, client.name as client_name, org.value as org_value, org.name as org_name, usr.name as user_createdby,
bp.value as bp_value, bp.name as bp_name,
dt.name as doctype_name, a.documentno, a.dateordered, a.docstatus, a.totallines, a.grandtotal,
b.line, b.m_product_id, prod.name as productName, b.qtyentered, b.pricelimit, b.priceentered, b.pricelist, b.priceactual,
b.discount, b.pricelist - b.priceactual as discountAmt, b.linenetamt,
from
c_order a
left join c_orderline b on a.c_order_id = b.c_order_id
left join ad_client client on client.ad_client_id = a.ad_client_id
left join ad_org org on org.ad_org_id = a.ad_org_id
left join c_doctype dt on a.c_doctype_id = dt.c_doctype_id
left join m_product prod on prod.m_product_id = b.m_product_id
left join c_bpartner bp on bp.c_bpartner_id = a.c_bpartner_id
left join ad_user usr on usr.ad_user_id = a.createdby
where
a.ad_client_id > 111 and
a.grandtotal = 0 and
a.docstatus in ('CO', 'VO') and
order by a.dateordered desc;</nowiki>

= Invoice and tax =

<nowiki>select
b.c_invoice_id, b.c_invoiceline_id, a.documentno, a.totallines, a.grandtotal, a.istaxincluded, a.dateinvoiced, a.dateacct,
a.docstatus, coalesce(a.c_doctype_id, a.c_doctypetarget_id) as c_doctype_id,
b.qtyinvoiced,b.qtyentered, b.pricelist, b.priceactual, b.pricelimit, b.priceentered, b.linenetamt, b.linetotalamt, b.taxamt
from
c_invoice a
join c_invoiceline b on a.c_invoice_id = b.c_invoice_id;
where
a.documentno='INW82785';

select
*
from
c_invoicetax tax
join c_invoice a on a.c_invoice_id = tax.c_invoice_id
where
a.documentno='INW82785';

select * from
c_invoice a
join c_invoiceline b on a.c_invoice_id = b.c_invoice_id
where
a.documentno='21686';

select a.ad_client_id, client.name, a.c_bpartner_id, b.value, b.name, a.issotrx, max(a.dateinvoiced), min(a.dateinvoiced), count(*) from
c_invoice a
join c_bpartner b on a.c_bpartner_id = b.c_bpartner_id
join ad_client client on a.ad_client_id = client.ad_client_id
group by
a.ad_client_id, client.name, a.c_bpartner_id, b.value, b.name, a.issotrx
order by a.ad_client_id, a.issotrx, count(*) desc;</nowiki>

= Set invoice as paid (system error) =

<nowiki>select ispaid, * from c_invoice where c_invoice_id = 1047724;

select * into bk_20150630_c_invoice from c_invoice where c_invoice_id = 1047724;

update c_invoice set ispaid = 'Y' where c_invoice_id = 1047724;</nowiki>

= Payments =

== Last payment related to an invoice ==

<nowiki>SELECT p.DateTrx,p.PayAmt,alp.Amount
FROM C_AllocationLine ali
INNER JOIN C_AllocationHdr ah ON (ah.C_AllocationHdr_ID = ali.C_AllocationHdr_ID AND ah.DocStatus IN ('CO', 'CL'))
INNER JOIN C_AllocationLine alp ON (alp.C_AllocationHdr_ID = ah.C_AllocationHdr_ID)
INNER JOIN C_Payment p ON (p.C_Payment_ID= alp.C_Payment_ID AND p.DocStatus IN ('CO', 'CL'))
WHERE ali.C_Invoice_ID = ?
ORDER BY p.DateTrx DESC
LIMIT 1</nowiki>

Discussion [https://groups.google.com/forum/#!topic/idempiere/J17EgtVzVKk here]

Credit:Anozi Mada

== Link from POS Order to the invoice to the payment ==

<nowiki>select
posorder.documentno, pmt.documentno
from
c_order posorder
join
c_invoice inv on inv.c_order_id = posorder.c_order_id
join
c_payment pmt on pmt.c_payment_id = inv.c_payment_id
where posorder.documentno = '80014';</nowiki>

Code Scratchpad: Queries: General

2021-07-28T05:56:29Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: General to Queries: General

#REDIRECT [[Queries: General]]

Queries: General

2021-07-28T05:56:29Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: General to Queries: General

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Statistics =

== Various counts ==

<nowiki>select cast('Business partners: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Business partners: Inactive' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and created>='1900/01/01' and isactive = 'N'
union all
select cast('Business partners: Suppliers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and a.isvendor = 'Y' and created>='1900/01/01'
union all
select cast('Business partners: Customers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and a.iscustomer = 'Y' and created>='1900/01/01'
union all
select cast('Business partners: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Invoices: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_invoice a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Invoices: Reversed or voided' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_invoice a where a.ad_client_id = 1000000 and created>='1900/01/01' and docstatus in ('RE','VO')
union all
select cast('Invoices: Suppliers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_invoice a where a.ad_client_id = 1000000 and a.issotrx = 'N' and created>='1900/01/01'
union all
select cast('Invoices: Customers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_invoice a where a.ad_client_id = 1000000 and a.issotrx = 'Y' and created>='1900/01/01'
union all
select cast('Payments: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_payment a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Payments: Reversed or voided' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_payment a where a.ad_client_id = 1000000 and created>='1900/01/01' and docstatus in ('RE','VO')
union all
select cast('Payments: Suppliers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_payment a where a.ad_client_id = 1000000 and a.isreceipt = 'N' and created>='1900/01/01'
union all
select cast('Payments: Customers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_payment a where a.ad_client_id = 1000000 and a.isreceipt = 'Y' and created>='1900/01/01'
union all
select cast('Assets: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from a_asset a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Assets: Inactive' as text) as heading, cast('1900/01/01' as text) as since, count(*) from a_asset a where a.ad_client_id = 1000000 and created>='1900/01/01' and isactive = 'N'
;</nowiki>

== Invoices grouped by document type name ==

<nowiki>select dt.name as DocumentType, cast('1900/01/01' as text) as since, count(*) from c_invoice a join c_doctype dt on a.c_doctype_id = dt.c_doctype_id where a.ad_client_id = 1000000 and a.created>='1900/01/01' group by dt.name;</nowiki>

= Credits =

Initial contribution by: nTier Software Services http://www.ntier.co.za ngordon7000

Add your credit here.

Code Scratchpad: Queries: Financial

2021-07-28T05:56:00Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Financial to Queries: Financial

#REDIRECT [[Queries: Financial]]

Queries: Financial

2021-07-28T05:56:00Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Financial to Queries: Financial

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Fact details =

This view can assist in the reconciliation of facts, or as the basis of a Jasper financial report.

== Base view ==

<nowiki>drop view if exists zz_scratch_factacct; -- cascade;

create or replace view zz_scratch_factacct as
SELECT
fa.ad_client_id,
fa.ad_org_id,
eval.accounttype,
eval.value,
eval.name,
fa.dateacct,
(COALESCE(fa.amtacctdr, (0)::numeric) - COALESCE(fa.amtacctcr, (0)::numeric)) AS amount,
eval.c_elementvalue_id AS account_id,
parent.value AS parentvalue,
parent.name AS parentname,
prod.value as product_value,
prod.name as product_name,
fa.ad_table_id,
fa.record_id,
fa.line_id,
fa.description
--fa.*
FROM
(
(
(
fact_acct fa
JOIN c_elementvalue eval ON ((fa.account_id = eval.c_elementvalue_id))
left join m_product prod on fa.m_product_id = prod.m_product_id
)
LEFT JOIN ad_treenode b ON
(
(
(b.node_id = eval.c_elementvalue_id)
AND (b.ad_client_id = fa.ad_client_id)
)
)
)
LEFT JOIN c_elementvalue parent ON ((parent.c_elementvalue_id = b.parent_id))
)
;

select * from zz_scratch_factacct;

--where
--fa.AD_CLIENT_ID= 1000000 and
--fa.dateacct >='2015-05-09 00:00:00' and
--fa.dateacct <= '2015-05-09 00:00:00' and
--eval.name = 'Cost of Sales' --and

</nowiki>

== Summary: Name ==

<nowiki>select name, sum(amount) *-1.00 as INCOME
from zz_scratch_factacct
where
dateacct >='2015-05-01 00:00:00' and dateacct <= '2015-05-31 00:00:00' and
accounttype ='R' and
--($3=0 OR AD_ORG_ID=0 OR $5 IS NULL) and
AD_CLIENT_ID= 1000000
group by name
HAVING (SUM(amount)) !=0
Order by name;

select name, sum(amount) as EXPENSE
from zz_scratch_factacct
where
dateacct >='2015-05-01 00:00:00' and dateacct <= '2015-05-31 00:00:00' and
accounttype ='E' and
--($3=0 OR AD_ORG_ID=$4 OR $5 IS NULL) and
AD_CLIENT_ID= 1000000 and
parentvalue = '200'
group by name
HAVING (SUM(amount)) !=0
Order by name;</nowiki>

== Summary: DateAcct ==

<nowiki>select
'Cost of Sales: I&E: ' || cast(a.dateacct as varchar(10)) as description, sum(a.amount)
from
zz_scratch_factacct a
where
a.AD_CLIENT_ID= 1000000 and
a.dateacct >='2015-05-01 00:00:00' and
a.dateacct <= '2015-05-31 00:00:00' and
a.name = 'Cost of Sales' --and
--a.created >= '2015-06-08 15:00:00'
group by
a.dateacct
union all
select
cast('Cost of Sales: I&E: *** Total ***' as varchar(80)) as description, sum(a.amount)
from
zz_scratch_factacct a
where
a.AD_CLIENT_ID= 1000000 and
a.dateacct >='2015-05-01 00:00:00' and
a.dateacct <= '2015-05-31 00:00:00' and
a.name = 'Cost of Sales' ---and
--a.created >= '2015-06-08 15:00:00'
order by
description
;</nowiki>

== Detail ==

<nowiki>select
a.created, a.dateacct, a.product_value, a.description, a.amount,
a.ad_table_id, a.record_id
--a.dateacct, a.amount
--a.*
from
zz_scratch_factacct a
where
a.AD_CLIENT_ID= 1000000 and
a.dateacct >='2015-05-01 00:00:00' and
a.dateacct <= '2015-05-31 00:00:00' and
a.name = 'Cost of Sales' and
a.created >= '2015-06-08 15:00:00'
order by
a.dateacct, a.amount
;</nowiki>

= Credits =

Initial contribution by: nTier Software Services http://www.ntier.co.za

Code Scratchpad: Queries: Bank Statement

2021-07-28T05:55:36Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Bank Statement to Queries: Bank Statement

#REDIRECT [[Queries: Bank Statement]]

Queries: Bank Statement

2021-07-28T05:55:36Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Bank Statement to Queries: Bank Statement

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= General =

<nowiki>select c_bankstatement_id,name from c_bankstatement where ad_client_id = 1000009 order by name;</nowiki>

= Rough working queries to reinstate voided bank statement =

* Possibly could be converted into a plugin
* Refer to MBankStatement.voidIt
* It should also be possible to simply delete the bank statement and reinsert the copy before void was done instead of using the staging table, but I didn't try it.
* Take a backup before running any queries

<nowiki>select docstatus, beginningbalance, statementdifference, endingbalance, * from c_bankstatement where C_BankStatement_ID=1000555;

-- Backup
select * into bk_20170408_bankstatement from c_bankstatement where c_bankstatement_id = 1000555;

select count(*) from bk_20170408_bankstatement;

select * into bk_20170408_bankstatementline from c_bankstatementline where c_bankstatement_id = 1000555;

select count(*) from bk_20170408_bankstatementline;

select * into bk_20170408_payment from c_payment;

select count(*) from bk_20170408_payment;

select * into bk_20170408_c_bankaccount from c_bankaccount where c_bankaccount_id =
(select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555);

select count(*) from bk_20170408_c_bankaccount;

-- Staging tables (must contain the original data before void was done)
select * into zz_stage_bankstatement from c_bankstatement where c_bankstatement_id = 1000555;
-- delete from zz_stage_bankstatement;
select * into zz_stage_bankstatementline from c_bankstatementline where c_bankstatement_id = 1000555;
-- delete from zz_stage_bankstatementline;

select count(*) from zz_stage_bankstatement;
select count(*) from zz_stage_bankstatementline;
select count(*) from c_bankstatement where c_bankstatement_id = 1000555;
select count(*) from c_bankstatementline where c_bankstatement_id = 1000555;
select count(*) from c_bankstatementline where c_bankstatement_id = 1000555 and c_payment_id is not null; --0; 90 on restore

select count(*) from c_payment where isreconciled = 'Y' and c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000555); -- 90

update c_bankstatement
set
docstatus = b.docstatus ,
docaction = b.docaction ,
processed = b.processed ,
description = b.description ,
statementdifference = b.statementdifference
from
zz_stage_bankstatement b
where
c_bankstatement.c_bankstatement_id = 1000555 and
c_bankstatement.c_bankstatement_id = b.c_bankstatement_id ;

update c_bankstatementline
set
description = b.description ,
stmtamt = b.stmtamt ,
trxamt = b.trxamt ,
chargeamt = b.chargeamt ,
interestamt = b.interestamt,
c_payment_id = b.c_payment_id
from
zz_stage_bankstatementline b
where
c_bankstatementline.c_bankstatement_id = 1000555 and
c_bankstatementline.c_bankstatementline_id = b.c_bankstatementline_id ;

-- ba.setCurrentBalance(ba.getCurrentBalance().subtract(getStatementDifference()));

select currentbalance from c_bankaccount where c_bankaccount_id = (
select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555
);

-- See MBankStatement.voidIt:
-- ba.setCurrentBalance(ba.getCurrentBalance().subtract(getStatementDifference()));
update c_bankaccount
set currentBalance = currentBalance +
(select statementdifference from c_bankstatement where c_bankstatement_id = 1000555)
where c_bankaccount_id = (
select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555
)
;

select currentbalance from c_bankaccount where c_bankaccount_id = (
select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555
);

update c_payment set
isreconciled = 'Y'
where c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000555);

-- Expect 90
select count(*) from c_payment where isreconciled = 'Y' and c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000555);
</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Code Scratchpad: Queries: Attachment

2021-07-28T05:55:03Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Attachment to Queries: Attachment

#REDIRECT [[Queries: Attachment]]

Queries: Attachment

2021-07-28T05:55:03Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: Attachment to Queries: Attachment

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]
[[NF1.0_Storage_Providers]] store attachment file to folder and store paths into xml push to BINARYDATA of table AD_Attachment.
below query run on oracle, parse data of BINARYDATA to row. so you can get list of paths of attachments

<syntaxhighlight lang="SQL">
SELECT attachInfo.filePath, attachInfo.fileName
FROM AD_Attachment x,
XMLTABLE('attachments/entry'
PASSING
CASE WHEN BINARYDATA IS NULL THEN
XMLTYPE.createXML('<?xml version="1.0" encoding="utf-8"?><attachments></attachments>')
ELSE
XMLTYPE.createXML(BINARYDATA , 871, null)
END
COLUMNS filePath varchar2(4000) PATH '@file',
fileName varchar2(4000) PATH '@name') attachInfo
WHERE title = 'xml'
</syntaxhighlight>

Code Scratchpad: Queries: AD

2021-07-28T05:54:34Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: AD to Queries: AD

#REDIRECT [[Queries: AD]]

Queries: AD

2021-07-28T05:54:34Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Queries: AD to Queries: AD

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<syntaxhighlight lang="sql">select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</syntaxhighlight>

= Windows =
== Windows referencing a specific process ==
<syntaxhighlight lang="sql">select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</syntaxhighlight>

== Window ==
<syntaxhighlight lang="sql">select * from ad_window a
where a.ad_window_id=id</syntaxhighlight>

== Tab ==
<syntaxhighlight lang="sql">select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </syntaxhighlight>

<syntaxhighlight lang="sql">select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</syntaxhighlight>

= Field information =

== Field ==
<syntaxhighlight lang="sql">select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</syntaxhighlight>

== Combining Window, tab, field ==

=== As a view ===

<syntaxhighlight lang="sql">drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</syntaxhighlight>

== Show fields by update date (useful to run after loading 2pack) ==

<syntaxhighlight lang="sql">select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</syntaxhighlight>

= Form =

<syntaxhighlight lang="sql">select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</syntaxhighlight>

= Table =
== Table ==
<syntaxhighlight lang="sql">select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</syntaxhighlight>

= Table Columns =

<syntaxhighlight lang="sql">select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</syntaxhighlight>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<syntaxhighlight lang="sql">update ad_column set entitytype='U' where ad_column_id=3510;
</syntaxhighlight>

= Callout =
<syntaxhighlight lang="sql">select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</syntaxhighlight>

== Update callout ==

<syntaxhighlight lang="sql">update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</syntaxhighlight>

= References =
<syntaxhighlight lang="sql">select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</syntaxhighlight>

== For use in Jasper Report to lookup reference name ==

<syntaxhighlight lang="sql">( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</syntaxhighlight>

== Validation Rules ==

<syntaxhighlight lang="sql">select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</syntaxhighlight>

= Elements =
<syntaxhighlight lang="sql">select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</syntaxhighlight>

= Report and process =
<syntaxhighlight lang="sql">select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</syntaxhighlight>

= Deletion =
== Delete all fields on window ==
<syntaxhighlight lang="sql">delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</syntaxhighlight>

== Delete all fields on tab ==

<syntaxhighlight lang="sql">1.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );

3.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'REPLACEWITHWINDOWUUHERE' ) );

</syntaxhighlight>

== Delete all occurences of column from windows ==

<syntaxhighlight lang="sql">delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</syntaxhighlight>

== Delete index definitions on table ==

* NB: Indexes not dropped automatically

<syntaxhighlight lang="sql">delete from ad_indexcolumn where AD_TableIndex_ID in (select ad_tableindex_id from ad_tableindex where ad_table_id = 318);

delete from AD_TableIndex where ad_table_id = 318;</syntaxhighlight>

= Sequences =
<syntaxhighlight lang="sql">update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</syntaxhighlight>

= Doctype =

<syntaxhighlight lang="sql">select c_doctype_id from c_doctype where name = 'DocumentName'</syntaxhighlight>

= Attachments =

== All pack-in attachments ==

<syntaxhighlight lang="sql">select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</syntaxhighlight>

= Synchronize Translations =

An example:

<syntaxhighlight lang="sql">update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</syntaxhighlight>

= Translations =

== Various helpers (work in progress) ==

<syntaxhighlight lang="sql">select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</syntaxhighlight>

= Pack out =

== Backup AD information before running 2pack ==

<syntaxhighlight lang="sql">select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</syntaxhighlight>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<syntaxhighlight lang="sql">delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

) );</syntaxhighlight>

* See also under the heading: 'Deletion'

== Packout/Packin package details ==

<syntaxhighlight lang="sql">select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

-- For searching for tables linked to packouts

select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;

</syntaxhighlight>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<syntaxhighlight lang="sql">delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</syntaxhighlight>

== Pack in/out helpers ==

=== Delete all fields on a window/tab ===

<syntaxhighlight lang="sql">delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );</syntaxhighlight>

=== Delete all ad_userquery of window/tab process ===

<syntaxhighlight lang="sql">delete from ad_userquery where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE')
));</syntaxhighlight>

=== Delete all ad_customization of window/tab ===

<syntaxhighlight lang="sql">delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE')
));</syntaxhighlight>

=== Delete all attachments of process ===

<syntaxhighlight lang="sql">delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</syntaxhighlight>

= Migration scripts =

== Which migration scripts have been run ==

<syntaxhighlight lang="sql">select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</syntaxhighlight>

= Session =

== Sessions run during the actual year ==

<syntaxhighlight lang="sql">
SELECT
c.name AS clientname,
s.created,
u.name AS username,
s.remote_addr,
s.processed,
CASE
WHEN s.processed = 'Y' THEN s.updated-s.created
ELSE NULL
END AS duration,
r.name AS rolename
FROM ad_session s
JOIN ad_client c ON (s.ad_client_id = c.ad_client_id)
JOIN ad_user u ON (u.ad_user_id = s.createdby)
JOIN ad_role r ON (r.ad_role_id = s.ad_role_id)
WHERE s.created>date_trunc('year', now())
ORDER BY s.ad_session_id DESC
</syntaxhighlight>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Code Scratchpad: Mercurial

2021-07-27T14:16:12Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Mercurial to Mercurial

#REDIRECT [[Mercurial]]

Mercurial

2021-07-27T14:16:12Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Mercurial to Mercurial

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= .hgignore =

<nowiki># Ignore all files under bin
bin

# use glob syntax.
syntax: glob

*.class

# switch to regexp syntax.
syntax: regexp</nowiki>

= Replace file in same location as original =

<nowiki>hg cat -r 71 src/org/compiere/model/I_AD_Client.java -o %p</nowiki>

= Save to alternate location =

<nowiki>hg cat -r 71 src/org/compiere/model/I_AD_Client.java -o /tmp/%s
hg cat -r tip src/org/compiere/ReportCustom/ZZ_Quotation.jrxml -o /tmp/%s</nowiki>

= Get files as at specific revision =

<nowiki>mkdir /tmp/test; hg cat -r 10469 org.adempiere.server-feature/buckminster_linux_gtk_x86_64.properties org.adempiere.server-feature/server.product.eventtest.launch -o /tmp/test/%s</nowiki>

= Purge (remove untracked files) =

<nowiki>Is there a way to delete all untracked files in a mercurial working area? Yes, with the purge extension.

Installing Purge

This extension is shipped by default with mercurial but turned off. To enable it, modify your config file (.hgrc on Unix, Mercurial.ini on Windows) to add
[extensions]
purge = This activates the plugin. You can now use
$ hg purge to delete all untracked files.</nowiki>

= Patches =

Mercurial can generate patch files in the unified diff format with the hg diff command.

= Follow renames =

<nowiki>hg log -f .classpath</nowiki>

= Use extdiff to diff 2 revisions =

<nowiki>hg extdiff -r tip -r 1000 src/org/compiere/model/MShippingInstr.java</nowiki>

= Enable extdiff extension and set Meld as the default diff tool=

<nowiki>.hgrc:

[extensions]
hgext.extdiff =

[extdiff]
cmd.extdiff = meld</nowiki>

= Resolving Conflicts =

<nowiki>hg resolve -m file.txt

hg resolve -t {editor} file</nowiki>

= Default paths =

<nowiki>Edit hgrc file (under .hgrc):

[paths]
default=ssh://user@server://path/to

[paths]
default-push = ssh://hg@example.com/path</nowiki>

= Basics =

<nowiki>hg add / hg addremove (add specific, or add/remove all)

hg init

hg log (-v & --debug) -r : specific revision

hg clone

hg commit -m "A small change - for testing the tagging system"

hg push</nowiki>

== Branches ==

<nowiki>Create new branch:

hg branch development

.. commit files ..

hg commit

Switch between branches:

hg update development

hg update default

Merging branches:

hg update development

hg merge default

... merges default into development
</nowiki>

== Tagging ==

<nowiki>hg tags

hg tag -m "Second tag" "V1.01"</nowiki>

== Clone the specified tag (exact copy) ==

<nowiki>hg clone -r "V1.00" TestProject TestProject1

hg clone -r "V1.0.4a_20140217" ssh://user@server://path/to destination</nowiki>

== To pull (don't forget to update!) ==

<nowiki>hg pull

hg update</nowiki>

NB: Always use filesystem snapshots using tar (before doing any critical work) so that you can backout gracefully from bad pulls and bad commits.

== Viewing revisions for a file ==

<nowiki>hg log src/org/compiere/model/MShippingInstr.java -v | more</nowiki>

= Clone using non-default port =

<nowiki>hg clone ssh://user@server:22227//path/to</nowiki>

= Credits =

Initial contribution by: nTier Software Services http://www.ntier.co.za ngordon7000

Add your credit here.

Code Scratchpad: Java: ZK

2021-07-27T08:50:20Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Java: ZK to ZK: Tidy up main page

#REDIRECT [[ZK]]

ZK

2021-07-27T08:50:20Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Java: ZK to ZK: Tidy up main page

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Show window from process =

== Method 1: Runnable ==

<nowiki>Runnable runnable = new Runnable() {

@Override
public void run() {
MPrintFormat pf = getPrintFormat();
Window viewer = new SMJReportViewer(getAD_PInstance_ID(), get_TrxName(), PA_ReportLineSet_ID, p_C_Period_ID, pf.getAD_PrintFont_ID(), m_columns);
viewer.setAttribute(Window.MODE_KEY, Window.MODE_EMBEDDED);
viewer.setAttribute(Window.INSERT_POSITION_KEY, Window.INSERT_NEXT);
AEnv.showWindow(viewer);
}
};
AEnv.executeAsyncDesktopTask(runnable);</nowiki>

== Method 2: Anonymous Runnable ==

<nowiki>AEnv.executeAsyncDesktopTask(new Runnable() {
@Override
public void run() {
//Window viewer = new SfrReportViewer(ren,"");

ReportEngine re = createReportEngine(getProcessInfo(),Env.WINDOW_MAIN,pf);
if (p_Org_ID==0)
p_Org_ID = Env.getAD_Org_ID(getCtx());
Window viewer = (Window)new SfrReportViewer(getAD_PInstance_ID(), get_TrxName(), PA_ReportLineSet_ID, p_C_Period_ID, pf.getAD_PrintFont_ID(), m_columns,re,p_Org_ID);

viewer.setAttribute(Window.MODE_KEY, Window.MODE_EMBEDDED);
viewer.setAttribute(Window.INSERT_POSITION_KEY, Window.INSERT_NEXT);
viewer.setAttribute(WindowContainer.DEFER_SET_SELECTED_TAB, Boolean.TRUE);
SessionManager.getAppDesktop().showWindow(viewer);
}
});</nowiki>

Credit: Freddy Heredia

= Credits =

Initial contribution by: nTier Software Services http://www.ntier.co.za ngordon7000

Add your credit here.

Java: General

2021-07-27T08:27:56Z

Ngordon7000: Ngordon7000 moved page Java: General to General Code: Tidy up main page

#REDIRECT [[General Code]]

General Code

2021-07-27T08:27:56Z

Ngordon7000: Ngordon7000 moved page Java: General to General Code: Tidy up main page

[[Category:Code Scratchpad]]
__FORCETOC__

= Chat =

Quickly add a chat to any PO (table) record.

<nowiki>package za.co.ntier.common;

import java.util.Properties;
import java.util.logging.Level;

import org.compiere.model.MChat;
import org.compiere.model.MChatEntry;
import org.compiere.model.PO;
import org.compiere.model.Query;
import org.compiere.util.CLogger;
import org.compiere.util.Env;

/**
* Description: Chat support
*/
public class NTierChatUtils {

/** Logger */
private static CLogger log = CLogger.getCLogger(NTierChatUtils.class);

/**
*/
public static MChat getChat(Properties ctx, String trxName,
PO po) {
final String whereClause = "AD_Client_ID=? and AD_Table_ID=? AND Record_ID=? ";
MChat retValue = new Query(ctx, MChat.Table_Name, whereClause,
trxName).setParameters(Env.getAD_Client_ID(ctx), po.get_Table_ID(), po.get_ID())
.firstOnly();
return retValue;
}

/**
* Add chat
*/
public static void addChat(Properties ctx, String trxName,
PO po, String comments) {
try {
String descr = "";
descr = comments;
descr = NTierStringUtils.left(descr, 255);
MChat chat = getChat(ctx, trxName, po);
if (chat == null) {
chat = new MChat (ctx, po.get_Table_ID(), po.get_ID(), descr, trxName);
chat.saveEx(trxName);
}
log.info(String.format("Chat - %s - '%s'", po.get_TableName(), descr));
MChatEntry chatEntry = new MChatEntry(chat, descr);
chatEntry.saveEx(trxName);
} catch (Exception e) {
log.log(Level.SEVERE, "^^^", e);
log.info(String.format("^^^ Failed to save chat record"));
}
}
}
</nowiki>

= Start a process (or report) =

* See FinReportJasper in the base code, for an example of how this is done.

= Database =

== Call a database procedure ==

* From FinReportJasper in the base

<nowiki>// CarlosRuiz - globalqss - allow procedure preprocess
if (proc.getProcedureName() != null && proc.getProcedureName().length() > 0) {
// execute on this thread/connection
String sql = "{call " + proc.getProcedureName() + "(?)}";
CallableStatement cstmt = null;
try
{
cstmt = DB.prepareCall(sql); // ro??
cstmt.setInt(1, getAD_PInstance_ID());
cstmt.executeUpdate();
}
catch (Exception e)
{
log.log(Level.SEVERE, sql, e);
poInfo.setSummary (Msg.getMsg(Env.getCtx(), "ProcessRunError") + " " + e.getLocalizedMessage());
}
finally
{
DB.close(cstmt);
cstmt = null;
}
}</nowiki>

= nTierConfigurator =

== Example use ==

<nowiki>public static int getSequenceNumberNext(Properties ctx, String trxName) {
MSysConfig config = NTierConfigurator.newOrGet(ctx, trxName, false,
"NTIER_SEQUENCE_NEXT", "5000", "Next sequence number");
int r = config.get_ValueAsInt("Value");
config.setValue( (new Integer(r+1)).toString() );
config.saveEx();
return r;
}

public static boolean isRequireUniqueSequence(Properties ctx, String trxName) {
return NTierConfigurator.newOrGetBoolean(ctx, trxName, false,
"NTIER_REQUIRE_UNIQUE_SEQUENCE", "Y", "Require a unique sequence number?");
}

public static String getDevelopmentMode(Properties ctx, String trxName) {
return NTierConfigurator.newOrGetString(ctx, trxName, false,
"NTIER_DEVELOPMENT_MODE", "Test", "Development mode - Test or Live");
}

public static Timestamp getFirstDayDateOfCurrentFinancialYear(Properties ctx, String trxName) {
MSysConfig config = NTierConfigurator.newOrGet(ctx, trxName, false,
"NTIER_FIRST_DAY_CURRENT_YEAR", "2014-03-01", "First day of current financial year: for take-on");
String v = config.get_ValueAsString("Value");
Timestamp r = NTierDateUtils.parse(v, "yyyy-MM-dd") ;
return r;
}

public static boolean isHasAssetAdditions(Properties ctx, String trxName, int assetID, String docStatus) {
int i = getAssetAdditions(ctx, trxName, assetID, docStatus).length;
return i>0;
} </nowiki>

== Class ==

<nowiki>/******************************************************************************
* Copyright (C) 2015 nTier Software Services http://www.ntier.co.za *
* This program is free software; you can redistribute it and/or modify it *
* under the terms version 2 of the GNU General Public License as published *
* by the Free Software Foundation. This program is distributed in the hope *
* that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
* warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. *
* See the GNU General Public License for more details. *
* You should have received a copy of the GNU General Public License along *
* with this program; if not, write to the Free Software Foundation, Inc., *
* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. *
*****************************************************************************/
package za.co.ntier.common;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.model.MSysConfig;
import org.compiere.model.Query;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

/**
* (c) 2015 nTier Software Services
* @author Neil Gordon
* Date: 24 Feb 2015
* Description: Manage configurator settings
*/
public class NTierConfigurator {

/** Logger */
private static CLogger log = CLogger.getCLogger(NTierConfigurator.class);
/**
* Get specified MSysConfig record
* @param ctx context
* @param ID
* @return record
*/
public static MSysConfig getConfigurator(Properties ctx, String trxName, String name) {
int AD_Client_ID = Env.getContextAsInt(ctx, "#AD_Client_ID");
MSysConfig po = new Query(ctx, MSysConfig.Table_Name, "ad_client_id=? and name=?", trxName)
.setParameters(AD_Client_ID, name).setOnlyActiveRecords(true).first();
return po;
}

/**
* Get the configurator setting
*/
public static String getConfiguratorSettingValue(String name, String defaultValue) {
String result = MSysConfig.getValue(name, defaultValue );
return result;
}

/**
* Get the configurator setting - specified Client, or System Client.
* Note, Client will override setting in System if both exist.
*/
public static String getConfiguratorSettingValue(String name, String defaultValue, int AD_Client_ID) {
String result = MSysConfig.getValue(name, defaultValue, AD_Client_ID );
return result;
}

/**
* Does the specified setting exist?
*/
public static boolean isSettingExists(Properties ctx, String trxName, String name) {
return isSettingExists(ctx, trxName, name, 0, 0);
}

/**
* Does the specified setting exist?
*/
public static boolean isSettingExists(Properties ctx, String trxName, String name, int AD_Client_ID, int AD_Org_ID)
{
//
String sql = "SELECT Value FROM AD_SysConfig"
+ " WHERE Name=? AND AD_Client_ID IN (0, ?) AND AD_Org_ID IN (0, ?) " //AND IsActive='Y'
+ " ORDER BY AD_Client_ID DESC, AD_Org_ID DESC";
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = DB.prepareStatement(sql, null);
pstmt.setString(1, name);
pstmt.setInt(2, AD_Client_ID);
pstmt.setInt(3, AD_Org_ID);
rs = pstmt.executeQuery();
if (rs.next())
return true;
}
catch (SQLException e)
{
log.log(Level.SEVERE, "isSettingExists", e);
}
finally
{
DB.close(rs, pstmt);
rs = null; pstmt = null;
}

return false;
}

/**
* Create the given configurator setting
*/
public static void createConfiguratorSetting(Properties ctx, String trxName, boolean isSystem, String name, String value, String description) {

String confLevel;
Properties ctx1;
ctx1 = (Properties)ctx.clone();
if ( isSystem ) {
if (isSettingExists(ctx, trxName, name))
return;
ctx1.setProperty(Env.AD_CLIENT_ID, "0");
confLevel = MSysConfig.CONFIGURATIONLEVEL_System;
} else {
if (isSettingExists(ctx, trxName, name, Env.getAD_Client_ID(ctx), 0))
return;
confLevel = MSysConfig.CONFIGURATIONLEVEL_Client;
}

ctx1.setProperty(Env.AD_ORG_ID, "0");
MSysConfig conf = new MSysConfig(ctx1, 0, null);
conf.setConfigurationLevel(confLevel);
conf.setName(name);
conf.setDescription(description);
conf.setValue(value);
conf.saveEx();
//CacheMgt.get().reset(MSysConfig.Table_Name);
}

public static MSysConfig newOrGet(Properties ctx, String trxName, boolean isSystem, String name, String defaultValue, String description) {
NTierUtils.resetCacheForTable( MSysConfig.Table_Name );
MSysConfig config;
Properties ctx1;
createConfiguratorSetting(ctx, trxName, isSystem, name, defaultValue, description);
ctx1 = (Properties)ctx.clone();
if (isSystem) {
ctx1.setProperty(Env.AD_CLIENT_ID, "0");
} else {
}
config = getConfigurator(ctx1, trxName, name);
if ( config==null ) {
throw new AdempiereException( String.format("Returned configurator setting is null (should've been created) - '%s'", name) );
}
return config;
}

public static String newOrGetString(Properties ctx, String trxName, boolean isSystem, String name, String defaultValue, String description) {
MSysConfig config;
config = newOrGet(ctx, trxName, isSystem, name, defaultValue, description);
String r = config.get_ValueAsString("Value");
return r;
}

public static boolean newOrGetBoolean(Properties ctx, String trxName, boolean isSystem, String name, String defaultValue, String description) {
MSysConfig config;
config = newOrGet(ctx, trxName, isSystem, name, defaultValue, description);
boolean r = config.get_ValueAsBoolean("Value");
return r;
}

public static int newOrGetInt(Properties ctx, String trxName, boolean isSystem, String name, String defaultValue, String description) {
MSysConfig config;
config = newOrGet(ctx, trxName, isSystem, name, defaultValue, description);
int r = config.get_ValueAsInt("Value");
return r;
}

}</nowiki>

= Credits =

Initial contribution by: nTier Software Services http://www.ntier.co.za

Add your credit here.

Code Scratchpad: Java: iDempiere Documents

2021-07-27T08:27:16Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Java: iDempiere Documents to IDempiere Documents: Tidy up main page

#REDIRECT [[IDempiere Documents]]

IDempiere Documents

2021-07-27T08:27:16Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Java: iDempiere Documents to IDempiere Documents: Tidy up main page

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]
__FORCETOC__
= SmartPOS: Various documents =

== Documents supported ==

Among others:

* createSalesOrder
* createPurchaseOrder
* createOrderLine
* createMInOut
* createIOLine
* createInvoice
* createInvoiceLine
* createPayment
* createMovement
* createMovementLine

== Link to source code ==

* '''Source code:''' https://bitbucket.org/PedroRozo_SmartJSP/smartpos3-erp/src/6aa37da834fd9301db4e50ff2530be1087ff8b56/SmartPOSWeb/src/com/smj/util/Documents.java?at=default
* '''Plugin:''' http://wiki.idempiere.org/en/Plugin:_SmartPOS
* '''Credit:''' [[User:Smartjsp|User:Smartjsp]] ([[User talk:Smartjsp|talk]]) - [http://www.smartjsp.com SmartJSP]

== Notes ==

* Recommended, that in the code given above, remove the exception handlers, and change .save to .saveEx

= Create payment =

Example of payment creation, with various features.

<nowiki>private void createPayment() {

m_payment = new MPayment(m_ctx, 0, m_trxName);

m_payment.setAD_Org_ID( m_AD_Org_ID );
/****************************************************************************
* Set fields
****************************************************************************/
m_payment.setC_BPartner_ID( m_matchSetup.getC_BPartner_ID() );
if ( ! m_payment.getC_BPartner().isActive() ) {
throw new AdempiereException( String.format("Business Partner is inactive" ) );
}
m_payment.setDateAcct ( m_dateAcct );
m_payment.setDateTrx ( m_dateAcct );
m_payment.setPayAmt ( m_stmtAmt.abs() );
m_payment.setC_BankAccount_ID ( m_C_BankAccount_ID );
// if ( ! m_po.getC_BankAccount().isActive() ) {
// throw new AdempiereException( String.format("Bank Account is inactive" ) );
// }

// m_po.setDocumentNo( thedocumentno );

m_payment.setIsReceipt( m_isReceipt );
// Still need to share NTierUtils on the Wiki
m_payment.setC_Currency_ID( MClient.get(Env.getCtx()).getC_Currency_ID() );
//
m_payment.saveEx();

// Chat - see http://wiki.idempiere.org/en/Code_Scratchpad:_Java:_General
NTierChatUtils.addChat (m_payment.getCtx(), m_payment.get_TrxName(),
m_payment,
String.format(NBSM_Common.getChatPrefix() + "Payment created. ") +
String.format("Bank statement: '%s'/Line No: %s ", m_bankStatementName, m_bankStatementLineNo) +
m_matchSetup.toString() );

// Sanity check
if ( m_payment.getAD_Org_ID() != m_AD_Org_ID ) {
throw new AdempiereException( String.format(
"The org has changed after saving the record. This is caused by the bank account " +
" having a different org to the requested org. Please select a different bank account, " +
" or correct the org on the selected bank account.") );
}

info.setC_Payment_ID( m_payment.get_ID() );

/****************************************************************************
* Complete Document
****************************************************************************/
String docAction = DocAction.ACTION_Complete;
m_payment.setDocAction( docAction );
m_payment.processIt( docAction );
m_payment.saveEx();

if ( ! docAction.equals( m_payment.getDocStatus() )) {
String msg = String.format("Error completing document: Invalid resulting status:- '%s' (expected '%s') Process Message:- '%s'",
m_payment.getDocStatus(), docAction, m_payment.getProcessMsg() );
throw new AdempiereException( msg );
}

// addInfo("Payment has been created and successfully completed.");

return;

}
</nowiki>

Credit: nTier Software Services http://www.ntier.co.za

Code Scratchpad: Java: iDempiere API

2021-07-27T08:26:29Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Java: iDempiere API to IDempiere API: Tidy up main page

#REDIRECT [[IDempiere API]]

IDempiere API

2021-07-27T08:26:29Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Java: iDempiere API to IDempiere API: Tidy up main page

[[Category:Code Scratchpad]]
[[Category:Developer documentation]]
[[Category:Development]]
__FORCETOC__

The purpose of this page is to share commonly used iDempiere API calls.

Feel free to contribute here to improve the coverage of documentation.

= Get the client ID =

Env.getContextAsInt(ctx, "#AD_Client_ID")

= Get the base currency =

MClient.get(Env.getCtx()).getC_Currency_ID()

= Get the process ID for process with key =

int AD_Process_ID = MProcess.getProcess_ID("KeyValueOfProcess", null);

= Start a report & process =

ProcessUtil.startJavaProcess(ctx, poInfo, trx);

* See FinReportJasper in the base code for example

= Commit transaction =

<nowiki>Trx trx = Trx.get(get_TrxName(), false);
trx.commit(true);</nowiki>

= Complete document =

<nowiki>String docAction = DocAction.ACTION_Complete;
posOrder.setDocAction( docAction );
posOrder.processIt( docAction );
posOrder.saveEx();</nowiki>

* NB: Check the document status after saving is equal to the expected status, otherwise an exception should be thrown.

= Get the persistent object =

== Standard ==

<nowiki>MBPartner partner = new MBPartner(getCtx(), {0 for new record, or record id}, get_TrxName());</nowiki>

== Model factory ==

The following is work in progress

* Using model factory, so that the correct model is returned, based on the available plugins.
** MTable.getPO (write a utility method to wrap)
** Use a Query (see the Query class)
** Write a utility method based on MTable.getPO, which doesn't require instantiating MTable

<nowiki>public PO getPO (int Record_ID, String trxName)
{
String tableName = getTableName();
if (Record_ID != 0 && !isSingleKey())
{
log.log(Level.WARNING, "(id) - Multi-Key " + tableName);
return null;
}

PO po = null;
List<IModelFactory> factoryList = Service.locator().list(IModelFactory.class).getServices();
if (factoryList != null)
{
for(IModelFactory factory : factoryList)
{
po = factory.getPO(tableName, Record_ID, trxName);
if (po != null)
{
if (po.get_ID() != Record_ID && Record_ID > 0)
po = null;
else
break;
}
}
}

if (po == null)
{
po = new GenericPO(tableName, getCtx(), Record_ID, trxName);
if (po.get_ID() != Record_ID && Record_ID > 0)
po = null;
}

return po;
} // getPO
</nowiki>

= Reset the cache for a table =

CacheMgt.get().reset(Table_Name);

= Lock PO for update =

see AdempiereDatabase.forUpdate:

<nowiki>public boolean forUpdate(PO po, int timeout);</nowiki>

= Attachments =

== Attach one or more files to a record ==

* Example copied from the PIPO code:

<nowiki>MAttachment attachment = new MAttachment (adPackageImp.getCtx(), X_AD_Package_Imp_Proc.Table_ID, adPackageImp.getAD_Package_Imp_Proc_ID(), null);
attachment.addEntry(packageFile);
attachment.saveEx(); </nowiki>

= System configurator =

<nowiki>MSysConfig.getBooleanValue("CONFIG_NAME", true)

String retValue = MSysConfig.getValue("CONFIG_NAME", "");</nowiki>

= Creating an order line =

* Similiar logic applies to an invoice line.

<nowiki>MOrderLine line = new MOrderLine(order);
line.setC_BPartner_ID(C_BPartner_ID);
line.setM_Product_ID(M_Product_ID);
line.setQty(BigDecimal.ONE);
// Sets the price based on the price list
line.setPrice();
// Extends the qty
line.setLineNetAmt();
line.saveEx();</nowiki>

= Methods for querying and updating data =

== Query API ==

* Example taken from ModelValidationEngine

<nowiki>MTable table = MTable.get(Env.getCtx(), X_AD_ModelValidator.Table_ID);
Query query = table.createQuery("IsActive='Y'", null);
query.setOrderBy("SeqNo");
..
List<X_AD_ModelValidator> entityTypes = query.list();
..</nowiki>

<nowiki>List<MInfoWindow> list = new Query(Env.getCtx(),
MInfoWindow.Table_Name, "IsValid='Y' AND
IsShowInDashboard='Y'", null)
.setOnlyActiveRecords(true)
.setOrderBy(MInfoWindow.COLUMNNAME_SeqNo)
.list();</nowiki>

== Prepared Statement ==

<nowiki>final String sql = "SELECT * FROM C_BPartner_Location WHERE C_BPartner_ID=? AND IsActive='Y'"
+ " ORDER BY C_BPartner_Location_ID";
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getC_BPartner_ID());
rs = pstmt.executeQuery();
while (rs.next())
list.add(new MBPartnerLocation (getCtx(), rs, get_TrxName()));
}
catch (Exception e)
{
log.log(Level.SEVERE, sql, e);
}
finally
{
DB.close(rs, pstmt);
rs = null; pstmt = null;
}</nowiki>

== Get all IDs ==

<nowiki>int[] m_ids = PO.getAllIDs(MAttachment.Table_Name, " AD_Client_ID= " + MClient.get(getCtx()).getAD_Client_ID() + " Order By AD_Client_ID ", null);</nowiki>

== DB.getSQLValue ==

<nowiki>StringBuilder sql = new StringBuilder();
sql.append("select count(*) from TableName where TableName_ID=").append(TableName_ID));

return DB.getSQLValueEx(trxName sql, params);</nowiki>

== Update ==

<nowiki>DB.executeUpdateEx(sql.toString(), new Object[] { ... }, trxName);</nowiki>

= Access control related =

== Window access ==

* Check whether currently logged in role has access to window
* @return null if no access, TRUE if r/w and FALSE if r/o

<nowiki>if (MRole.getDefault().getWindowAccess(m_AD_Window_ID) == null) { ... }</nowiki>

= UI related =

== Busy Dialog ==

* BusyDialogTemplate: A busy dialog with an encapsulated Runnable

= Translations =

== Simple get message text ==

<nowiki>Msg.getMsg(Env.getCtx(), "messagekey")</nowiki>

== Translation of column (element) ==

<nowiki>String columnNameTrl = Msg.getElement(Env.getCtx(), columnName);</nowiki>

= Pack Out =

== Add support to export new or existing display type ==

<nowiki>PoExporter.export</nowiki>

= User preferences =

* Untested, use at your own risk
* You can use the context for settings that don't need to be persisted.

<nowiki>protected void savePreferences() {
String note = txtNote.getText();
UserPreference pref = SessionManager.getSessionApplication().getUserPreference();
pref.setProperty(getPrefKeyForNote(), note);
pref.savePreference();
}

protected void loadPreferences() {
UserPreference pref = SessionManager.getSessionApplication().getUserPreference();
String note = pref.getProperty(getPrefKeyForNote());
if (note==null) {
return;
}
txtNote.setText(note);
}</nowiki>

= Context =

== Adding a new context variable just after login ==

* See: Login.loadPreferences

= Logging =

* For everything you need to know about logging in iDempiere, see the CLogMgt class

= MD5, hashes, checksums, archives =

== Compare MD5's and more ==

<nowiki>DigestOfFile</nowiki>

== Create zip archives ==

<nowiki>Zipper</nowiki>

= Code paths =

== User clicks on New record ==

1) When user click on New Record button in any window, onNew() function in AbstractADWindowContent class is executed. Please check AbstractADWindowContent class for other functions (onSave,onProcess,onReport etc) as well.

2) getCurrentNext() and setCurrentNext() in MSequence class. Thank you very much Anozi Mada

Regards,
Shanil Fernando

Reference: https://groups.google.com/forum/#!topic/idempiere/aSsv1MkciwM

== Toolbar delete button ==

* Where do I find the code which handles deletion of a record from the toolbar?

<nowiki>AbstractADWindowContent.onDeleteCallback</nowiki>

== Toolbar refresh button ==

<nowiki>AbstractADWindowContent.onRefresh</nowiki>

== Toolbar export button ==

<nowiki>AbstractADWindowContent.onExport</nowiki>

* See: ExportAction

== Toolbar import button ==

<nowiki>AbstractADWindowContent.onFileImport --> FileImportAction.fileImport --> importFile --> GridTabCSVImporter.fileImport</nowiki>

== Process Dialog ==

=== Dialog to start process or report ===

<nowiki>ProcessDialog</nowiki>

=== Process Info Dialog ===

* The dialog shown after a user starts a process from a window

<nowiki>ProcessInfoDialog</nowiki>

* Shown from:

<nowiki>AbstractADWindowContent.updateUI</nowiki>

=== User starts process by clicking on a button on a window ===

<nowiki>AbstractADWindowContent.actionButton</nowiki>

== Clicking on a menu item ==

Where do I find the code which responds to an item being clicked on the menu?

<nowiki>AbstractDesktop.onMenuSelected</nowiki>
== Opening of a window ==

* Where is the opening of a window handled?
** Same class for form, process, workflow, info

<nowiki>TabbedDesktop.openWindow</nowiki>

== Display and layout of fields ==

* Where do I find the logic which handles the display and layout of fields in a standard iDempiere window?

<nowiki>ADTabpanel</nowiki>

* Where is formatting is applied to fields (example, required fields highlighted in red)?

<nowiki>WEditor.applyLabelStyles</nowiki>

* Where do I find the container for a field which is displayed in a window?
** Also handles setting the default value for a field

<nowiki>GridField</nowiki>

* Where are callouts processed?

<nowiki>GridTab.processCallout</nowiki>

* Where is the field change event for when a value changes on a window?

<nowiki>GridTab.processFieldChange</nowiki>

== Printing ==

* Handle printing for various documents

<nowiki>ServerReportCtl</nowiki>

== Application startups ==

=== Model Generator ===

<nowiki>ModelGeneratorApplication -> ModelClassGeneratorDialog -> ModelClassGenerator</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Code Scratchpad: Java: General

2021-07-27T08:24:44Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Java: General to Java: General: Tidy up main page

#REDIRECT [[Java: General]]

General Code

2021-07-27T08:24:44Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Java: General to Java: General: Tidy up main page

Code Scratchpad: Eclipse Templates

2021-07-27T08:23:43Z

Ngordon7000: Ngordon7000 moved page Code Scratchpad: Eclipse Templates to Eclipse Templates: Tidy up main page

#REDIRECT [[Eclipse Templates]]

Category:Code Scratchpad

2021-07-27T08:21:23Z

Ngordon7000:

[[Category:Developer documentation]]
[[Category:Development]]

This is a scratchpad of code. It is a rough working area containing code.

These samples may or may not be correct, as they are a work in progress. Please take care when running any update queries.

If you have any additions or corrections, please edit the page in question.

[[ja:カテゴリ:Code Scratchpad]]

== External Links ==

* [http://wiki.idempiere.org/ru/%D0%9A%D0%B0%D1%82%D0%B5%D0%B3%D0%BE%D1%80%D0%B8%D1%8F:Code_Scratchpad Link to Russian Scratchpad Ссылка на российском блокнота]
* [[:Category:Code snippets]]

== Credits ==

Initial contribution by: nTier Software Services http://www.ntier.co.za

Queries: Invoices, Orders and Quotes

2017-09-30T01:24:41Z

Ngordon7000: /* Invoice and tax */

Queries: AD

2017-07-28T09:30:40Z

Ngordon7000:

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );

3.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'REPLACEWITHWINDOWUUHERE' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

== Delete index definitions on table ==

* NB: Indexes not dropped automatically

<nowiki>delete from ad_indexcolumn where AD_TableIndex_ID in (select ad_tableindex_id from ad_tableindex where ad_table_id = 318);

delete from AD_TableIndex where ad_table_id = 318;</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

) );</nowiki>

* See also under the heading: 'Deletion'

== Packout/Packin package details ==

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

-- For searching for tables linked to packouts

select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Pack in/out helpers ==

=== Delete all fields on a window/tab ===

<nowiki>delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );</nowiki>

=== Delete all ad_userquery of window/tab process ===

<nowiki>delete from ad_userquery where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE')
));</nowiki>

=== Delete all ad_customization of window/tab ===

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE')
));</nowiki>

=== Delete all attachments of process ===

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: AD

2017-07-28T09:29:13Z

Ngordon7000: /* Delete all attachments of process */

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );

3.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'REPLACEWITHWINDOWUUHERE' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

== Delete index definitions on table ==

* NB: Indexes not dropped automatically

<nowiki>delete from ad_indexcolumn where AD_TableIndex_ID in (select ad_tableindex_id from ad_tableindex where ad_table_id = 318);

delete from AD_TableIndex where ad_table_id = 318;</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

) );</nowiki>

* See also under the heading: 'Deletion'

== Packout/Packin package details ==

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

-- For searching for tables linked to packouts

select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Pack in/out helpers ==

=== Delete all fields on a window/tab ===

<nowiki>delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );</nowiki>

=== Delete all ad_userquery of window/tab process ===

<nowiki>delete from ad_userquery where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE')
));</nowiki>

=== Delete all ad_customization of window/tab ==

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE')
));</nowiki>

=== Delete all attachments of process ===

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

IDempiere API

2017-04-21T12:46:57Z

Ngordon7000: /* Toolbar import button */

IDempiere API

2017-04-21T12:43:53Z

Ngordon7000: /* Toolbar import button */

[[Category:Code Scratchpad]]
[[Category:Developer documentation]]
[[Category:Development]]
__FORCETOC__

The purpose of this page is to share commonly used iDempiere API calls.

Feel free to contribute here to improve the coverage of documentation.

= Get the client ID =

Env.getContextAsInt(ctx, "#AD_Client_ID")

= Get the base currency =

MClient.get(Env.getCtx()).getC_Currency_ID()

= Get the process ID for process with key =

int AD_Process_ID = MProcess.getProcess_ID("KeyValueOfProcess", null);

= Start a report & process =

ProcessUtil.startJavaProcess(ctx, poInfo, trx);

* See FinReportJasper in the base code for example

= Commit transaction =

<nowiki>Trx trx = Trx.get(get_TrxName(), false);
trx.commit(true);</nowiki>

= Complete document =

<nowiki>String docAction = DocAction.ACTION_Complete;
posOrder.setDocAction( docAction );
posOrder.processIt( docAction );
posOrder.saveEx();</nowiki>

* NB: Check the document status after saving is equal to the expected status, otherwise an exception should be thrown.

= Get the persistent object =

== Standard ==

<nowiki>MBPartner partner = new MBPartner(getCtx(), {0 for new record, or record id}, get_TrxName());</nowiki>

== Model factory ==

The following is work in progress

* Using model factory, so that the correct model is returned, based on the available plugins.
** MTable.getPO (write a utility method to wrap)
** Use a Query (see the Query class)
** Write a utility method based on MTable.getPO, which doesn't require instantiating MTable

<nowiki>public PO getPO (int Record_ID, String trxName)
{
String tableName = getTableName();
if (Record_ID != 0 && !isSingleKey())
{
log.log(Level.WARNING, "(id) - Multi-Key " + tableName);
return null;
}

PO po = null;
List<IModelFactory> factoryList = Service.locator().list(IModelFactory.class).getServices();
if (factoryList != null)
{
for(IModelFactory factory : factoryList)
{
po = factory.getPO(tableName, Record_ID, trxName);
if (po != null)
{
if (po.get_ID() != Record_ID && Record_ID > 0)
po = null;
else
break;
}
}
}

if (po == null)
{
po = new GenericPO(tableName, getCtx(), Record_ID, trxName);
if (po.get_ID() != Record_ID && Record_ID > 0)
po = null;
}

return po;
} // getPO
</nowiki>

= Reset the cache for a table =

CacheMgt.get().reset(Table_Name);

= Lock PO for update =

see AdempiereDatabase.forUpdate:

<nowiki>public boolean forUpdate(PO po, int timeout);</nowiki>

= Attachments =

== Attach one or more files to a record ==

* Example copied from the PIPO code:

<nowiki>MAttachment attachment = new MAttachment (adPackageImp.getCtx(), X_AD_Package_Imp_Proc.Table_ID, adPackageImp.getAD_Package_Imp_Proc_ID(), null);
attachment.addEntry(packageFile);
attachment.saveEx(); </nowiki>

= System configurator =

<nowiki>MSysConfig.getBooleanValue("CONFIG_NAME", true)

String retValue = MSysConfig.getValue("CONFIG_NAME", "");</nowiki>

= Creating an order line =

* Similiar logic applies to an invoice line.

<nowiki>MOrderLine line = new MOrderLine(order);
line.setC_BPartner_ID(C_BPartner_ID);
line.setM_Product_ID(M_Product_ID);
line.setQty(BigDecimal.ONE);
// Sets the price based on the price list
line.setPrice();
// Extends the qty
line.setLineNetAmt();
line.saveEx();</nowiki>

= Methods for querying and updating data =

== Query API ==

* Example taken from ModelValidationEngine

<nowiki>MTable table = MTable.get(Env.getCtx(), X_AD_ModelValidator.Table_ID);
Query query = table.createQuery("IsActive='Y'", null);
query.setOrderBy("SeqNo");
..
List<X_AD_ModelValidator> entityTypes = query.list();
..</nowiki>

<nowiki>List<MInfoWindow> list = new Query(Env.getCtx(),
MInfoWindow.Table_Name, "IsValid='Y' AND
IsShowInDashboard='Y'", null)
.setOnlyActiveRecords(true)
.setOrderBy(MInfoWindow.COLUMNNAME_SeqNo)
.list();</nowiki>

== Prepared Statement ==

<nowiki>final String sql = "SELECT * FROM C_BPartner_Location WHERE C_BPartner_ID=? AND IsActive='Y'"
+ " ORDER BY C_BPartner_Location_ID";
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getC_BPartner_ID());
rs = pstmt.executeQuery();
while (rs.next())
list.add(new MBPartnerLocation (getCtx(), rs, get_TrxName()));
}
catch (Exception e)
{
log.log(Level.SEVERE, sql, e);
}
finally
{
DB.close(rs, pstmt);
rs = null; pstmt = null;
}</nowiki>

== Get all IDs ==

<nowiki>int[] m_ids = PO.getAllIDs(MAttachment.Table_Name, " AD_Client_ID= " + MClient.get(getCtx()).getAD_Client_ID() + " Order By AD_Client_ID ", null);</nowiki>

== DB.getSQLValue ==

<nowiki>StringBuilder sql = new StringBuilder();
sql.append("select count(*) from TableName where TableName_ID=").append(TableName_ID));

return DB.getSQLValueEx(trxName sql, params);</nowiki>

== Update ==

<nowiki>DB.executeUpdateEx(sql.toString(), new Object[] { ... }, trxName);</nowiki>

= Access control related =

== Window access ==

* Check whether currently logged in role has access to window
* @return null if no access, TRUE if r/w and FALSE if r/o

<nowiki>if (MRole.getDefault().getWindowAccess(m_AD_Window_ID) == null) { ... }</nowiki>

= UI related =

== Busy Dialog ==

* BusyDialogTemplate: A busy dialog with an encapsulated Runnable

= Translations =

== Simple get message text ==

<nowiki>Msg.getMsg(Env.getCtx(), "messagekey")</nowiki>

== Translation of column (element) ==

<nowiki>String columnNameTrl = Msg.getElement(Env.getCtx(), columnName);</nowiki>

= Pack Out =

== Add support to export new or existing display type ==

<nowiki>PoExporter.export</nowiki>

= User preferences =

* Untested, use at your own risk
* You can use the context for settings that don't need to be persisted.

<nowiki>protected void savePreferences() {
String note = txtNote.getText();
UserPreference pref = SessionManager.getSessionApplication().getUserPreference();
pref.setProperty(getPrefKeyForNote(), note);
pref.savePreference();
}

protected void loadPreferences() {
UserPreference pref = SessionManager.getSessionApplication().getUserPreference();
String note = pref.getProperty(getPrefKeyForNote());
if (note==null) {
return;
}
txtNote.setText(note);
}</nowiki>

= Context =

== Adding a new context variable just after login ==

* See: Login.loadPreferences

= Logging =

* For everything you need to know about logging in iDempiere, see the CLogMgt class

= MD5, hashes, checksums, archives =

== Compare MD5's and more ==

<nowiki>DigestOfFile</nowiki>

== Create zip archives ==

<nowiki>Zipper</nowiki>

= Code paths =

== User clicks on New record ==

1) When user click on New Record button in any window, onNew() function in AbstractADWindowContent class is executed. Please check AbstractADWindowContent class for other functions (onSave,onProcess,onReport etc) as well.

2) getCurrentNext() and setCurrentNext() in MSequence class. Thank you very much Anozi Mada

Regards,
Shanil Fernando

Reference: https://groups.google.com/forum/#!topic/idempiere/aSsv1MkciwM

== Toolbar delete button ==

* Where do I find the code which handles deletion of a record from the toolbar?

<nowiki>AbstractADWindowContent.onDeleteCallback</nowiki>

== Toolbar refresh button ==

<nowiki>AbstractADWindowContent.onRefresh</nowiki>

== Toolbar export button ==

<nowiki>AbstractADWindowContent.onExport</nowiki>

* See: ExportAction

== Toolbar import button ==

<nowiki>AbstractADWindowContent.onFileImport --> FileImportAction .fileImport --> importFile --> GridTabCSVImporter.fileImport </nowiki>

== Process Dialog ==

=== Dialog to start process or report ===

<nowiki>ProcessDialog</nowiki>

=== Process Info Dialog ===

* The dialog shown after a user starts a process from a window

<nowiki>ProcessInfoDialog</nowiki>

* Shown from:

<nowiki>AbstractADWindowContent.updateUI</nowiki>

=== User starts process by clicking on a button on a window ===

<nowiki>AbstractADWindowContent.actionButton</nowiki>

== Clicking on a menu item ==

Where do I find the code which responds to an item being clicked on the menu?

<nowiki>AbstractDesktop.onMenuSelected</nowiki>
== Opening of a window ==

* Where is the opening of a window handled?
** Same class for form, process, workflow, info

<nowiki>TabbedDesktop.openWindow</nowiki>

== Display and layout of fields ==

* Where do I find the logic which handles the display and layout of fields in a standard iDempiere window?

<nowiki>ADTabpanel</nowiki>

* Where is formatting is applied to fields (example, required fields highlighted in red)?

<nowiki>WEditor.applyLabelStyles</nowiki>

* Where do I find the container for a field which is displayed in a window?
** Also handles setting the default value for a field

<nowiki>GridField</nowiki>

* Where are callouts processed?

<nowiki>GridTab.processCallout</nowiki>

* Where is the field change event for when a value changes on a window?

<nowiki>GridTab.processFieldChange</nowiki>

== Printing ==

* Handle printing for various documents

<nowiki>ServerReportCtl</nowiki>

== Application startups ==

=== Model Generator ===

<nowiki>ModelGeneratorApplication -> ModelClassGeneratorDialog -> ModelClassGenerator</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

IDempiere API

2017-04-21T12:43:05Z

Ngordon7000: /* Toolbar export button */

[[Category:Code Scratchpad]]
[[Category:Developer documentation]]
[[Category:Development]]
__FORCETOC__

The purpose of this page is to share commonly used iDempiere API calls.

Feel free to contribute here to improve the coverage of documentation.

= Get the client ID =

Env.getContextAsInt(ctx, "#AD_Client_ID")

= Get the base currency =

MClient.get(Env.getCtx()).getC_Currency_ID()

= Get the process ID for process with key =

int AD_Process_ID = MProcess.getProcess_ID("KeyValueOfProcess", null);

= Start a report & process =

ProcessUtil.startJavaProcess(ctx, poInfo, trx);

* See FinReportJasper in the base code for example

= Commit transaction =

<nowiki>Trx trx = Trx.get(get_TrxName(), false);
trx.commit(true);</nowiki>

= Complete document =

<nowiki>String docAction = DocAction.ACTION_Complete;
posOrder.setDocAction( docAction );
posOrder.processIt( docAction );
posOrder.saveEx();</nowiki>

* NB: Check the document status after saving is equal to the expected status, otherwise an exception should be thrown.

= Get the persistent object =

== Standard ==

<nowiki>MBPartner partner = new MBPartner(getCtx(), {0 for new record, or record id}, get_TrxName());</nowiki>

== Model factory ==

The following is work in progress

* Using model factory, so that the correct model is returned, based on the available plugins.
** MTable.getPO (write a utility method to wrap)
** Use a Query (see the Query class)
** Write a utility method based on MTable.getPO, which doesn't require instantiating MTable

<nowiki>public PO getPO (int Record_ID, String trxName)
{
String tableName = getTableName();
if (Record_ID != 0 && !isSingleKey())
{
log.log(Level.WARNING, "(id) - Multi-Key " + tableName);
return null;
}

PO po = null;
List<IModelFactory> factoryList = Service.locator().list(IModelFactory.class).getServices();
if (factoryList != null)
{
for(IModelFactory factory : factoryList)
{
po = factory.getPO(tableName, Record_ID, trxName);
if (po != null)
{
if (po.get_ID() != Record_ID && Record_ID > 0)
po = null;
else
break;
}
}
}

if (po == null)
{
po = new GenericPO(tableName, getCtx(), Record_ID, trxName);
if (po.get_ID() != Record_ID && Record_ID > 0)
po = null;
}

return po;
} // getPO
</nowiki>

= Reset the cache for a table =

CacheMgt.get().reset(Table_Name);

= Lock PO for update =

see AdempiereDatabase.forUpdate:

<nowiki>public boolean forUpdate(PO po, int timeout);</nowiki>

= Attachments =

== Attach one or more files to a record ==

* Example copied from the PIPO code:

<nowiki>MAttachment attachment = new MAttachment (adPackageImp.getCtx(), X_AD_Package_Imp_Proc.Table_ID, adPackageImp.getAD_Package_Imp_Proc_ID(), null);
attachment.addEntry(packageFile);
attachment.saveEx(); </nowiki>

= System configurator =

<nowiki>MSysConfig.getBooleanValue("CONFIG_NAME", true)

String retValue = MSysConfig.getValue("CONFIG_NAME", "");</nowiki>

= Creating an order line =

* Similiar logic applies to an invoice line.

<nowiki>MOrderLine line = new MOrderLine(order);
line.setC_BPartner_ID(C_BPartner_ID);
line.setM_Product_ID(M_Product_ID);
line.setQty(BigDecimal.ONE);
// Sets the price based on the price list
line.setPrice();
// Extends the qty
line.setLineNetAmt();
line.saveEx();</nowiki>

= Methods for querying and updating data =

== Query API ==

* Example taken from ModelValidationEngine

<nowiki>MTable table = MTable.get(Env.getCtx(), X_AD_ModelValidator.Table_ID);
Query query = table.createQuery("IsActive='Y'", null);
query.setOrderBy("SeqNo");
..
List<X_AD_ModelValidator> entityTypes = query.list();
..</nowiki>

<nowiki>List<MInfoWindow> list = new Query(Env.getCtx(),
MInfoWindow.Table_Name, "IsValid='Y' AND
IsShowInDashboard='Y'", null)
.setOnlyActiveRecords(true)
.setOrderBy(MInfoWindow.COLUMNNAME_SeqNo)
.list();</nowiki>

== Prepared Statement ==

<nowiki>final String sql = "SELECT * FROM C_BPartner_Location WHERE C_BPartner_ID=? AND IsActive='Y'"
+ " ORDER BY C_BPartner_Location_ID";
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getC_BPartner_ID());
rs = pstmt.executeQuery();
while (rs.next())
list.add(new MBPartnerLocation (getCtx(), rs, get_TrxName()));
}
catch (Exception e)
{
log.log(Level.SEVERE, sql, e);
}
finally
{
DB.close(rs, pstmt);
rs = null; pstmt = null;
}</nowiki>

== Get all IDs ==

<nowiki>int[] m_ids = PO.getAllIDs(MAttachment.Table_Name, " AD_Client_ID= " + MClient.get(getCtx()).getAD_Client_ID() + " Order By AD_Client_ID ", null);</nowiki>

== DB.getSQLValue ==

<nowiki>StringBuilder sql = new StringBuilder();
sql.append("select count(*) from TableName where TableName_ID=").append(TableName_ID));

return DB.getSQLValueEx(trxName sql, params);</nowiki>

== Update ==

<nowiki>DB.executeUpdateEx(sql.toString(), new Object[] { ... }, trxName);</nowiki>

= Access control related =

== Window access ==

* Check whether currently logged in role has access to window
* @return null if no access, TRUE if r/w and FALSE if r/o

<nowiki>if (MRole.getDefault().getWindowAccess(m_AD_Window_ID) == null) { ... }</nowiki>

= UI related =

== Busy Dialog ==

* BusyDialogTemplate: A busy dialog with an encapsulated Runnable

= Translations =

== Simple get message text ==

<nowiki>Msg.getMsg(Env.getCtx(), "messagekey")</nowiki>

== Translation of column (element) ==

<nowiki>String columnNameTrl = Msg.getElement(Env.getCtx(), columnName);</nowiki>

= Pack Out =

== Add support to export new or existing display type ==

<nowiki>PoExporter.export</nowiki>

= User preferences =

* Untested, use at your own risk
* You can use the context for settings that don't need to be persisted.

<nowiki>protected void savePreferences() {
String note = txtNote.getText();
UserPreference pref = SessionManager.getSessionApplication().getUserPreference();
pref.setProperty(getPrefKeyForNote(), note);
pref.savePreference();
}

protected void loadPreferences() {
UserPreference pref = SessionManager.getSessionApplication().getUserPreference();
String note = pref.getProperty(getPrefKeyForNote());
if (note==null) {
return;
}
txtNote.setText(note);
}</nowiki>

= Context =

== Adding a new context variable just after login ==

* See: Login.loadPreferences

= Logging =

* For everything you need to know about logging in iDempiere, see the CLogMgt class

= MD5, hashes, checksums, archives =

== Compare MD5's and more ==

<nowiki>DigestOfFile</nowiki>

== Create zip archives ==

<nowiki>Zipper</nowiki>

= Code paths =

== User clicks on New record ==

1) When user click on New Record button in any window, onNew() function in AbstractADWindowContent class is executed. Please check AbstractADWindowContent class for other functions (onSave,onProcess,onReport etc) as well.

2) getCurrentNext() and setCurrentNext() in MSequence class. Thank you very much Anozi Mada

Regards,
Shanil Fernando

Reference: https://groups.google.com/forum/#!topic/idempiere/aSsv1MkciwM

== Toolbar delete button ==

* Where do I find the code which handles deletion of a record from the toolbar?

<nowiki>AbstractADWindowContent.onDeleteCallback</nowiki>

== Toolbar refresh button ==

<nowiki>AbstractADWindowContent.onRefresh</nowiki>

== Toolbar export button ==

<nowiki>AbstractADWindowContent.onExport</nowiki>

* See: ExportAction

== Toolbar import button ==

<nowiki>AbstractADWindowContent.onFileImport --> FileImportAction .fileImport --> importFile</nowiki>

== Process Dialog ==

=== Dialog to start process or report ===

<nowiki>ProcessDialog</nowiki>

=== Process Info Dialog ===

* The dialog shown after a user starts a process from a window

<nowiki>ProcessInfoDialog</nowiki>

* Shown from:

<nowiki>AbstractADWindowContent.updateUI</nowiki>

=== User starts process by clicking on a button on a window ===

<nowiki>AbstractADWindowContent.actionButton</nowiki>

== Clicking on a menu item ==

Where do I find the code which responds to an item being clicked on the menu?

<nowiki>AbstractDesktop.onMenuSelected</nowiki>
== Opening of a window ==

* Where is the opening of a window handled?
** Same class for form, process, workflow, info

<nowiki>TabbedDesktop.openWindow</nowiki>

== Display and layout of fields ==

* Where do I find the logic which handles the display and layout of fields in a standard iDempiere window?

<nowiki>ADTabpanel</nowiki>

* Where is formatting is applied to fields (example, required fields highlighted in red)?

<nowiki>WEditor.applyLabelStyles</nowiki>

* Where do I find the container for a field which is displayed in a window?
** Also handles setting the default value for a field

<nowiki>GridField</nowiki>

* Where are callouts processed?

<nowiki>GridTab.processCallout</nowiki>

* Where is the field change event for when a value changes on a window?

<nowiki>GridTab.processFieldChange</nowiki>

== Printing ==

* Handle printing for various documents

<nowiki>ServerReportCtl</nowiki>

== Application startups ==

=== Model Generator ===

<nowiki>ModelGeneratorApplication -> ModelClassGeneratorDialog -> ModelClassGenerator</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: AD

2017-04-15T01:31:20Z

Ngordon7000: /* Deletion */

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );

3.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'REPLACEWITHWINDOWUUHERE' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

== Delete index definitions on table ==

* NB: Indexes not dropped automatically

<nowiki>delete from ad_indexcolumn where AD_TableIndex_ID in (select ad_tableindex_id from ad_tableindex where ad_table_id = 318);

delete from AD_TableIndex where ad_table_id = 318;</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

) );</nowiki>

* See also under the heading: 'Deletion'

== Packout/Packin package details ==

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

-- For searching for tables linked to packouts

select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Delete all attachments of process ==

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: AD

2017-04-11T00:40:00Z

Ngordon7000: /* Packout/Packin package details */

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );

3.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'REPLACEWITHWINDOWUUHERE' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

) );</nowiki>

* See also under the heading: 'Deletion'

== Packout/Packin package details ==

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

-- For searching for tables linked to packouts

select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Delete all attachments of process ==

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: Bank Statement

2017-04-08T14:30:57Z

Ngordon7000: /* Rough working queries to reinstate voided bank statement */

Queries: Bank Statement

2017-04-08T13:51:48Z

Ngordon7000:

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= General =

<nowiki>select c_bankstatement_id,name from c_bankstatement where ad_client_id = 1000009 order by name;</nowiki>

= Rough working queries to reinstate voided bank statement =

* Possibly could be converted into a plugin
* Refer to MBankStatement.voidIt
* It should also be possible to simply delete the bank statement and reinsert the copy before void was done instead of using the staging table, but I didn't try it.
* Take a backup before running any queries

<nowiki>-- select docstatus, * from c_bankstatement where C_BankStatement_ID=1000555;

-- Backup
select * into bk_20170408_bankstatement from c_bankstatement where c_bankstatement_id = 1000555;

select count(*) from bk_20170408_bankstatement;

select * into bk_20170408_bankstatementline from c_bankstatementline where c_bankstatement_id = 1000555;

select count(*) from bk_20170408_bankstatementline;

select * into bk_20170408_payment from c_payment;

select count(*) from bk_20170408_payment;

select * into bk_20170408_c_bankaccount from c_bankaccount where c_bankaccount_id =
(select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555);

select count(*) from bk_20170408_c_bankaccount;

-- Staging tables (must contain the original data before void was done)
select * into zz_stage_bankstatement from c_bankstatement where c_bankstatement_id = 1000555;
-- delete from zz_stage_bankstatement;
select * into zz_stage_bankstatementline from c_bankstatementline where c_bankstatement_id = 1000555;
-- delete from zz_stage_bankstatementline;

select count(*) from zz_stage_bankstatement;
select count(*) from zz_stage_bankstatementline;
select count(*) from c_bankstatement where c_bankstatement_id = 1000555;
select count(*) from c_bankstatementline where c_bankstatement_id = 1000555;
select count(*) from c_bankstatementline where c_bankstatement_id = 1000555 and c_payment_id is not null; --0; 90 on restore

select count(*) from c_payment where isreconciled = 'Y' and c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000555); -- 90

update c_bankstatement
set
docstatus = b.docstatus ,
docaction = b.docaction ,
processed = b.processed ,
description = b.description ,
statementdifference = b.statementdifference
from
zz_stage_bankstatement b
where
c_bankstatement.c_bankstatement_id = 1000555 and
c_bankstatement.c_bankstatement_id = b.c_bankstatement_id ;

update c_bankstatementline
set
description = b.description ,
stmtamt = b.stmtamt ,
trxamt = b.trxamt ,
chargeamt = b.chargeamt ,
interestamt = b.interestamt,
c_payment_id = b.c_payment_id
from
zz_stage_bankstatementline b
where
c_bankstatementline.c_bankstatement_id = 1000555 and
c_bankstatementline.c_bankstatementline_id = b.c_bankstatementline_id ;

-- ba.setCurrentBalance(ba.getCurrentBalance().subtract(getStatementDifference()));

select currentbalance from c_bankaccount where c_bankaccount_id = (
select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555
);

-- See MBankStatement.voidIt:
-- ba.setCurrentBalance(ba.getCurrentBalance().subtract(getStatementDifference()));
update c_bankaccount
set currentBalance = currentBalance +
(select statementdifference from c_bankstatement where c_bankstatement_id = 1000555)
where c_bankaccount_id = (
select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555
)
;

select currentbalance from c_bankaccount where c_bankaccount_id = (
select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555
);

update c_payment set
isreconciled = 'Y'
where c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000555);

-- Expect 90
select count(*) from c_payment where isreconciled = 'Y' and c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000555);
</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: AD

2017-04-05T01:14:36Z

Ngordon7000: /* Delete all fields on tab */

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );

3.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'REPLACEWITHWINDOWUUHERE' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

) );</nowiki>

* See also under the heading: 'Deletion'

== Packout/Packin package details ==

* Work in progress

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Delete all attachments of process ==

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: AD

2017-04-05T01:13:46Z

Ngordon7000: /* Delete all fields + tabs on windows (for Pack In) */

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );

3.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'df93f2c3-f95b-4195-822d-d2f3c590b077' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

) );</nowiki>

* See also under the heading: 'Deletion'

== Packout/Packin package details ==

* Work in progress

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Delete all attachments of process ==

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: AD

2017-04-05T01:12:50Z

Ngordon7000: /* Deletion */

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' )
) );

3.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'df93f2c3-f95b-4195-822d-d2f3c590b077' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
) );</nowiki>

== Packout/Packin package details ==

* Work in progress

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Delete all attachments of process ==

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: AD

2017-04-05T01:00:24Z

Ngordon7000: /* Delete all fields on tab */

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'df93f2c3-f95b-4195-822d-d2f3c590b077' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
) );</nowiki>

== Packout/Packin package details ==

* Work in progress

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Delete all attachments of process ==

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: AD

2017-04-05T01:00:09Z

Ngordon7000: /* Delete all fields on tab */

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.
delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'df93f2c3-f95b-4195-822d-d2f3c590b077' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
) );</nowiki>

== Packout/Packin package details ==

* Work in progress

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Delete all attachments of process ==

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

Queries: AD

2017-04-05T00:59:48Z

Ngordon7000:

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= Client =

<nowiki>select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';
</nowiki>

= Windows =
== Windows referencing a specific process ==
<nowiki>select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);</nowiki>

== Window ==
<nowiki>select * from ad_window a
where a.ad_window_id=id</nowiki>

== Tab ==
<nowiki>select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a </nowiki>

<nowiki>select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where
a.ad_table_id =
(select ad_table_id from ad_table where name='tablename');</nowiki>

= Field information =

== Field ==
<nowiki>select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id</nowiki>

== Combining Window, tab, field ==

=== As a view ===

<nowiki>drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select
tabtable.tablename as table_name,
tabtable.entitytype as table_entitytype,
fld.isdisplayed as field_isdisplayed,
fld.seqno as field_seqno,
fld.name as field_name,
col.columnname as column_name,
fld.displaylogic as field_display_logic,
fld.readonlylogic as field_readonly_logic,
tab.name as tab_name,
win.name as WindowName,
win.ad_window_id,
tab.ad_tab_id,
tab.isactive as tab_isactive,
fld.ad_field_uu,
fld.ad_field_id,
fld.iscentrallymaintained as field_centrallymaintained,
fld.ismandatory as field_ismandatory,
fld.mandatorylogic as field_mandatorylogic,
col.ad_element_id,
ele.name as element_name,
ref.name as ref_name, refvalue.name as ref_value,
refvalue.ad_reference_id,
reftabletbl.tablename as ref_table,
col.created as column_created, col.updated as column_updated,
fld.created as field_created, fld.updated as field_updated,
fld.ad_val_rule_id as field_val_rule_id
from
ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like 'C%' and field_mandatorylogic is not null;
</nowiki>

== Show fields by update date (useful to run after loading 2pack) ==

<nowiki>select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;</nowiki>

= Form =

<nowiki>select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;</nowiki>

= Table =
== Table ==
<nowiki>select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');
</nowiki>

= Table Columns =

<nowiki>select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;
</nowiki>

= Menu =

== All the main (root) menu items in a tree ==

<pre>SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;</pre>

== Update entity type ==

<nowiki>update ad_column set entitytype='U' where ad_column_id=3510;
</nowiki>

= Callout =
<nowiki>select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;</nowiki>

== Update callout ==

<nowiki>update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;</nowiki>

= References =
<nowiki>select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
ad_reference ref on ref.name = 'ZZ_Status'
left join
ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status</nowiki>

== For use in Jasper Report to lookup reference name ==

<nowiki>( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name</nowiki>

== Validation Rules ==

<nowiki>select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');</nowiki>

= Elements =
<nowiki>select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)</nowiki>

= Report and process =
<nowiki>select * from ad_process order by created desc;

select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname'; --Sequence</nowiki>

= Deletion =
== Delete all fields on window ==
<nowiki>delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name like 'Name of window'
) );</nowiki>

== Delete all fields on tab ==

<nowiki>1.
delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in
(
select ad_window_id from ad_window where name = 'Name of window'
) );

2.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'df93f2c3-f95b-4195-822d-d2f3c590b077' ) );

</nowiki>

== Delete all occurences of column from windows ==

<nowiki>delete from ad_field where ad_column_id in
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )</nowiki>

= Sequences =
<nowiki>update ad_sequence a set CURRENTNEXTSYS =13000 where a.name = 'name';</nowiki>

= Doctype =

<nowiki>select c_doctype_id from c_doctype where name = 'DocumentName'</nowiki>

= Attachments =

== All pack-in attachments ==

<nowiki>select * from ad_attachment where ad_table_id = 50008 and record_id in (
select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%'
);</nowiki>

= Synchronize Translations =

An example:

<nowiki>update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';</nowiki>

= Translations =

== Various helpers (work in progress) ==

<nowiki>select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v as
select
main.ad_field_id,
trl.ad_language, trl.ad_field_trl_uu,
main.ad_field_uu, main.name as field_name, main.description as main_description,
trl.name as trl_name, trl.description as trl_description,
win.name as window_name,
col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;

select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where
ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );

select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where
trl.ad_language in ( 'en_ZA' ) and
main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';
</nowiki>

= Pack out =

== Backup AD information before running 2pack ==

<nowiki>select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;</nowiki>

== Delete all fields + tabs on windows (for Pack In) ==

* Helps to ensure is packed in correctly

<nowiki>delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
));

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' )
) );</nowiki>

== Packout/Packin package details ==

* Work in progress

<nowiki>select
hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from
AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Exp hdr
order by updated desc;

select
updated, hdr.name, hdr.description
from
AD_Package_Imp hdr
order by updated desc;

</nowiki>

== Duplicate key error when importing AD_Message ==

* unique constraint (...AD_MESSAGE_TRL_KEY) violated

<nowiki>delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );</nowiki>

== Delete all attachments of process ==

<nowiki>delete from ad_attachment where record_id in (
select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');</nowiki>

= Migration scripts =

== Which migration scripts have been run ==

<nowiki>select releaseno,created,name,status,isapply,filename, script from ad_migrationscript order by releaseno, name;</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.

IDempiere Documents

2017-03-21T22:23:18Z

Ngordon7000:

Queries: Bank Statement

2017-03-21T13:18:32Z

Ngordon7000:

[[Category:Code Scratchpad]]
[[Category:Development‏‎]]
[[Category:Developer documentation‏‎]]

= General =

<nowiki>select c_bankstatement_id,name from c_bankstatement where ad_client_id = 1000009 order by name;</nowiki>

= Rough working queries to reinstate voided bank statement =

* Possibly could be converted into a plugin
* Refer to MBankStatement.voidIt
* It should also be possible to simply delete the bank statement and reinsert the copy before void was done instead of using the staging table, but I didn't try it.
* Take a backup before running any queries

<nowiki>-- select docstatus, * from c_bankstatement where C_BankStatement_ID=1000200;

-- Backup
select * into bk_20160906_bankstatement from c_bankstatement where c_bankstatement_id = 1000200;

select count(*) from bk_20160906_bankstatement;

select * into bk_20160906_bankstatementline from c_bankstatementline where c_bankstatement_id = 1000200;

select count(*) from bk_20160906_bankstatementline;

select * into bk_20160906_payment from c_payment;

select count(*) from bk_20160906_payment;

select * into bk_20160906_c_bankaccount from c_bankaccount where c_bankaccount_id =
(select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000200);

select count(*) from bk_20160906_c_bankaccount;

-- Staging tables (must contain the original data before void was done)
select * into zz_stage_bankstatement from c_bankstatement where c_bankstatement_id = 1000200;
delete from zz_stage_bankstatement;
select * into zz_stage_bankstatementline from c_bankstatementline where c_bankstatement_id = 1000200;
delete from zz_stage_bankstatementline;

select count(*) from zz_stage_bankstatement;
select count(*) from zz_stage_bankstatementline;
select count(*) from c_bankstatement where c_bankstatement_id = 1000200;
select count(*) from c_bankstatementline where c_bankstatement_id = 1000200;
select count(*) from c_bankstatementline where c_bankstatement_id = 1000200 and c_payment_id is not null; --106

select count(*) from c_payment where isreconciled = 'Y' and c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000200); -- 106

update c_bankstatement
set
docstatus = b.docstatus ,
docaction = b.docaction ,
processed = b.processed ,
description = b.description ,
statementdifference = b.statementdifference
from
zz_stage_bankstatement b
where
c_bankstatement.c_bankstatement_id = 1000200 and
c_bankstatement.c_bankstatement_id = b.c_bankstatement_id ;

update c_bankstatementline
set
description = b.description ,
stmtamt = b.stmtamt ,
trxamt = b.trxamt ,
chargeamt = b.chargeamt ,
interestamt = b.interestamt,
c_payment_id = b.c_payment_id
from
zz_stage_bankstatementline b
where
c_bankstatementline.c_bankstatement_id = 1000200 and
c_bankstatementline.c_bankstatementline_id = b.c_bankstatementline_id ;

-- ba.setCurrentBalance(ba.getCurrentBalance().subtract(getStatementDifference()));

select currentbalance from c_bankaccount where c_bankaccount_id = (
select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000200
);

update c_bankaccount
set currentBalance = currentBalance +
(select statementdifference from c_bankstatement where c_bankstatement_id = 1000200)
where c_bankaccount_id = (
select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000200
)
;

select currentbalance from c_bankaccount where c_bankaccount_id = (
select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000200
);

update c_payment set
isreconciled = 'Y'
where c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000200);

-- Expect 106
select count(*) from c_payment where isreconciled = 'Y' and c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000200);
</nowiki>

''This page is brought to you by [http://www.ntier.co.za nTier Software Services]. Feel free to improve directly or suggest using the Discussion tab.