Report Export Quety

Receipt Report

SELECT
    fy.fiscal_year AS "Fiscal Year",
    r.receipt_number AS "Receipt Number",
	pp.full_name AS "Vendor",
	pt.description AS "Purchase Type",
	r.bill_number AS "Bill Number",
	r.transaction_date AS "Transaction Date",
	r.bill_date AS "Bill Date",
    s.full_name AS "Store",
	gt.description AS "Group Type",
    sig.full_name AS "Item Group",
    i.full_name AS "Item",
	uom.description AS "UOM",
	rd.quantity AS "Qty.",
    rd.rate AS "Rate",
	rd.item_discount_percentage AS "Discount (%)",
    rd.item_discount_amount AS "Dis. Amt",
    rd.net_rate AS "After Dis. Rate",
    rd.total_tax AS "Tax Amt",
    COALESCE(cc.cost_center_name, '') AS "Cost Center",
	COALESCE (ss.full_name, '') AS "Substore"
FROM inv.tbl_receipt_detail rd
LEFT JOIN inv.tbl_store ss
	ON rd.sub_store_id = ss.id
LEFT JOIN app.tbl_cost_center cc
	ON rd.cost_center_id = cc.id
JOIN inv.tbl_item i
    ON rd.item_id = i.id
JOIN public.tbl_static_data_value uom
	ON i.receipt_static_uom_id = uom.id and uom.static_data_id = 18
JOIN inv.tbl_store_item_group sig
    ON i.store_item_group_id = sig.id
JOIN public.tbl_static_data_value gt
	ON sig.static_item_group_type_id = gt.id AND gt.static_data_id = 17
JOIN inv.tbl_store s
    ON rd.store_id = s.id
JOIN inv.tbl_receipt r
    ON rd.receipt_id = r.id
JOIN public.tbl_static_data_value pt
	ON r.static_purchase_type_id = pt.id AND pt.static_data_id = 36
JOIN app.tbl_payable_profile pp
	ON r.payable_profile_id = pp.id
JOIN app.tbl_fiscal_year fy
    ON r.fiscal_year_id = fy.id
WHERE fy.id = 6
    AND rd.is_active = TRUE
    AND rd.is_deleted = FALSE;

Issue Report Substore

SELECT
    fy.fiscal_year,
    issu.transaction_date,
    fs.full_name AS from_store,
    ts.full_name AS to_substore,
    r.receipt_number AS from_batch,
    issu.issue_direct_number AS to_batch,
    itg.full_name AS item_group,
    i.full_name AS item,
    iss.rate AS rate,
    iss.quantity AS qty
FROM inv.tbl_issue_sub_store AS iss
JOIN inv.tbl_receipt AS r
    ON iss.receipt_id = r.id
JOIN inv.tbl_issue AS issu
    ON iss.issue_id = issu.id
JOIN inv.tbl_item AS i
    ON iss.item_id = i.id
JOIN inv.tbl_store AS fs
    ON i.store_id = fs.id
JOIN inv.tbl_store AS ts
    ON iss.to_sub_store_id = ts.id
JOIN inv.tbl_store_item_group AS itg
    ON i.store_item_group_id = itg.id
JOIN app.tbl_fiscal_year AS fy
    ON issu.fiscal_year_id = fy.id
WHERE fy.id = 6 AND
	iss.is_active = true AND
	iss.is_deleted = false;