Skip to content

Instantly share code, notes, and snippets.

View ranafaraz's full-sized avatar
:shipit:

Rana Faraz ranafaraz

:shipit:
8 followers · 15 following
View GitHub Profile
@ranafaraz
ranafaraz / Budget Allocation and Utilization.sql
Last active February 24, 2021 13:39
Query to fetch Major Head, Minor Head, Sub Head, Budget Head wise ==> Allocation and Utilization against a specified Budget ID.
SELECT
concat(o.value, ' - ', o.name) AS Org,
concat(vwc.majorheadvalue, ' - ', vwc.majorhead) AS MajorHead,
concat(vwc.minorheadvalue, ' - ', vwc.minerhead) AS MinorHead,
concat(vwc.subheadvalue, ' - ', vwc.subhead) AS SubHead,
concat(vwc.value, ' - ', vwc.name) AS BudgetHead,
SUM(f.amt) AS Allocation,
SUM(i.grandtotal) AS Utilization
FROM
@ranafaraz
ranafaraz / furniture.sql
Created February 24, 2021 05:48
Queries to get room and furniture details of IUB
SELECT
ep.title AS Equipment_Type,
e.title AS Equipment,
e.picture
FROM
equipments e
INNER JOIN equipements_type ep ON (( ep.id = e.type_id ))
GROUP BY
@ranafaraz
ranafaraz / CO - AR Invoice.sql
Last active February 22, 2021 15:11
Query to get Paid AR Invoices. This data will be displayed in Account Receivable ---> Received So Far
SELECT
i.documentno AS Invoice_No,
p.documentno AS Payment_No,
concat(bacct.name, ' - ', bacct.accountno) AS Bank_Acct,
concat(ev.value, ' - ', ev.name) AS Charge_Head,
concat(o.value, ' - ', o.name) AS Charge_Org,
i.dateinvoiced,
i.dateacct,
bp.value AS Search_Key,
@ranafaraz
ranafaraz / Budget Allocation.sql
Created February 18, 2021 06:38
Queries to get Budget Allocation details Major, Minor, Sub, Charge Head Wise.
SELECT
concat(vwc.majorheadvalue, ' - ', vwc.majorhead),
SUM(f.amt) AS Current_Allocation
FROM
gl_fund f
INNER JOIN ad_org o ON (( o.ad_org_id = f.ad_org_id))
INNER JOIN gl_budget b ON (( b.gl_budget_id = f.gl_budget_id ))
INNER JOIN f_vwchart vwc ON (( vwc.c_elementvalue_id = f.c_elementvalue_id ))
@ranafaraz
ranafaraz / Bank Acct Details.sql
Created February 16, 2021 20:09
Query to fetch bank account details from iDempiere
SELECT
b.name AS Bank_Name,
bacct.accountno,
-- COUNT(bacct.c_bankaccount_id),
-- SUM(ob.currentbalance)
bacct.bankaccounttype,
@ranafaraz
ranafaraz / Sum of Market Value of Vehicles.sql
Last active March 1, 2021 07:11
Query to get vehicle record from iDempiere
SELECT SUM(mp.market_price) FROM adempiere.market_price mp WHERE mp.ad_client_id = 1000000 AND mp.isactive = 'Y'
--
@ranafaraz
ranafaraz / Callout to Hit Account Book (Live Server)
Last active February 5, 2021 19:21
This Gist includes callouts of iDempiere that have been implemented for IUB.
// Call out to hit Account Book of IUB
@SuppressWarnings("resource")
public String account_book_api(Properties ctx, int WindowNo, GridTab mTab,
GridField mField, Object value, Object oldValue) throws IOException
{
int c_invoice_id = Integer.parseInt((String) mTab.getValue("c_invoice_id").toString());
String sql = "SELECT " +
"i.c_invoice_id AS Trans_ID," +
@ranafaraz
ranafaraz / PaymentVoucherQuery.sql
Last active February 3, 2021 05:26
This query is used to print the details in the payment voucher attached with Payment Window of iDempiere.
-- This query is deprecated as withholding taxes has been moved to Invoice (Vendor) window.
SELECT
( CASE WHEN ( P.c_doctype_id = ( 1000012 ) :: NUMERIC ) THEN 'Bank Payment Voucher' :: TEXT ELSE'Cash Receiving Voucher' :: TEXT END ) :: CHARACTER VARYING ( 70 ) AS print_title,
-- Client
( SELECT cl.NAME FROM adempiere.ad_client cl WHERE cl.ad_client_id = i.ad_client_id ) AS Client,
-- Organization
concat ( org.VALUE, ' - ', org.NAME ) AS Org,
-- BPartner Variables
concat ( bp.NAME, ' (ID: ', bp.VALUE, ') ', bp.description ) AS BPartner,
-- Invoice Variables
@ranafaraz
ranafaraz / BudgetDetails(Callout Query).sql
Last active January 30, 2021 17:51
This query is used to fetch total and utilized budget against a specific charge head and financial year, using a callout applied on C_Charge_ID. Note: InvoiceLine is being used as Ledger and budget is available in GL Fund. #iDempiere
-- Static values were replaced by dynamic Form values in the Callout.
SELECT
f.amt AS Total_Budget,
(
SELECT SUM
( linetotalamt )
FROM
( adempiere.c_invoiceline ledger INNER JOIN adempiere.c_invoice lgi ON ( ( ledger.c_invoice_id = lgi.c_invoice_id ) ) )
WHERE
@ranafaraz
ranafaraz / Set DocAction Complete of a Payment using SOAP in iDempiere
Created December 30, 2020 10:42
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:_0="http://idempiere.org/ADInterface/1_0">
<soapenv:Header/>
<soapenv:Body>
<_0:setDocAction>
<_0:ModelSetDocActionRequest>
<_0:ModelSetDocAction>
<_0:serviceType>complete_payment</_0:serviceType>
<_0:tableName>C_Payment</_0:tableName>