Report Export Quety

Receipt Report

SELECT
    fy.fiscal_year AS "Fiscal Year",
	r.transaction_date AS "Transaction Date",
    r.receipt_number AS "Receipt Number",
	pp.full_name AS "Vendor",
	pt.description AS "Purchase Type",
	r.bill_number AS "Bill Number",
	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 -- Enter Fiscal Year Id Here
    AND rd.is_active = TRUE
    AND rd.is_deleted = FALSE;

Issue Report Substore

SELECT
    fy.fiscal_year AS "Fiscal Year",
    issu.transaction_date AS "Transaction Date",
	strfrom.description AS "Issued From",
    fs.full_name AS "Store/Substore",
	strto.description AS "Issued To",
    ts.full_name AS "Sub-Store/Cost Center",
    r.receipt_number AS "Issue From Batch",
    issu.issue_direct_number AS "Issue To Batch",
	gt.description AS "Group Type",
    itg.full_name AS "Item Group",
    i.full_name AS "Item",
	uom.description AS "Uom",
	iss.quantity AS "Qty",
    iss.rate AS "Rate",
	iss.rate * iss.quantity AS "Value"
FROM inv.tbl_issue_sub_store AS iss
JOIN public.tbl_static_data_value uom
	ON iss.static_uom_id = uom.id AND uom.static_data_id = 18
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 public.tbl_static_data_value strfrom
	ON fs.static_store_type_id = strfrom.id AND strfrom.static_data_id = 15
JOIN inv.tbl_store AS ts
    ON iss.to_sub_store_id = ts.id
JOIN public.tbl_static_data_value strto
	ON ts.static_store_type_id = strto.id AND strto.static_data_id = 15
JOIN inv.tbl_store_item_group AS itg
    ON i.store_item_group_id = itg.id
JOIN public.tbl_static_data_value gt
	ON itg.static_item_group_type_id = gt.id AND gt.static_data_id = 17
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;

Issue Report, Issue to cost from s,ss

SELECT
    fy.fiscal_year AS "Fiscal Year",
    issufrm.transaction_date AS "Transaction Date",
	COALESCE(issb.description,isf.description) AS "Issued From",

    -- Issue From: dynamically pick substore or store
    CASE
        WHEN icc.issue_sub_store_id = 0 
            THEN s.full_name   -- Store ➜ CC
        ELSE 
            ss.full_name           -- Substore ➜ CC
    END AS "Store/Substore",

    'COST CENTER' AS "Issued To",

    cc.cost_center_name AS "Sub-Store/Cost Center",

    CASE
        WHEN icc.issue_sub_store_id = 0
            THEN r.receipt_number
        ELSE
            issu.issue_direct_number
    END AS "Issue From Batch",
	
	issufrm.issue_direct_number AS "Issue To Batch",
    igt.description AS "Group Type",
    ig.full_name AS "Item Group",
    i.full_name AS "Item",
    uom.description AS "Uom",
    icc.quantity AS "Qty",
    icc.rate AS "Rate",
	icc.rate * icc.quantity AS "Value"

FROM inv.tbl_issue_cost_center icc

JOIN inv.tbl_issue issufrm
    ON icc.issue_id = issufrm.id

LEFT JOIN inv.tbl_issue_sub_store iss
    ON icc.issue_sub_store_id = iss.id

LEFT JOIN inv.tbl_issue issu
    ON iss.issue_id = issu.id

JOIN app.tbl_fiscal_year fy
    ON issufrm.fiscal_year_id = fy.id

JOIN inv.tbl_item i
    ON icc.item_id = i.id

JOIN inv.tbl_store_item_group ig
    ON i.store_item_group_id = ig.id

JOIN public.tbl_static_data_value igt
    ON ig.static_item_group_type_id = igt.id 
    AND igt.static_data_id = 17

JOIN public.tbl_static_data_value uom
    ON icc.static_uom_id = uom.id 
    AND uom.static_data_id = 18

LEFT JOIN inv.tbl_store ss
    ON iss.to_sub_store_id = ss.id

JOIN inv.tbl_store s
    ON i.store_id = s.id

LEFT JOIN public.tbl_static_data_value isf
    ON s.static_store_type_id = isf.id 
    AND isf.static_data_id = 15
	
LEFT JOIN public.tbl_static_data_value issb
	ON ss.static_store_type_id = issb.id
	AND issb.static_data_id = 15

JOIN app.tbl_cost_center cc
    ON icc.to_cost_center_id = cc.id
	
LEFT JOIN inv.tbl_receipt_detail rd
	ON icc.receipt_detail_id = rd.id
	
LEFT JOIN inv.tbl_receipt r
	ON rd.receipt_id = r.id

WHERE 
    fy.id = 6
    AND icc.is_active = TRUE
    AND icc.is_deleted = FALSE;

Both Combined -

COPY (
SELECT
    fy.fiscal_year AS "Fiscal Year",
    issu.transaction_date AS "Transaction Date",
	strfrom.description AS "Issued From",
    fs.full_name AS "Store/Substore",
	strto.description AS "Issued To",
    ts.full_name AS "Sub-Store/Cost Center",
    r.receipt_number AS "Issue From Batch",
    issu.issue_direct_number AS "Issue To Batch",
	gt.description AS "Group Type",
    itg.full_name AS "Item Group",
    i.full_name AS "Item",
	uom.description AS "Uom",
	iss.quantity AS "Qty",
    iss.rate AS "Rate",
	iss.rate * iss.quantity AS "Value"
FROM inv.tbl_issue_sub_store AS iss
JOIN public.tbl_static_data_value uom
	ON iss.static_uom_id = uom.id AND uom.static_data_id = 18
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 public.tbl_static_data_value strfrom
	ON fs.static_store_type_id = strfrom.id AND strfrom.static_data_id = 15
JOIN inv.tbl_store AS ts
    ON iss.to_sub_store_id = ts.id
JOIN public.tbl_static_data_value strto
	ON ts.static_store_type_id = strto.id AND strto.static_data_id = 15
JOIN inv.tbl_store_item_group AS itg
    ON i.store_item_group_id = itg.id
JOIN public.tbl_static_data_value gt
	ON itg.static_item_group_type_id = gt.id AND gt.static_data_id = 17
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
	
UNION ALL

	SELECT
    fy.fiscal_year AS "Fiscal Year",
    issufrm.transaction_date AS "Transaction Date",
	COALESCE(issb.description,isf.description) AS "Issued From",

    -- Issue From: dynamically pick substore or store
    CASE
        WHEN icc.issue_sub_store_id = 0 
            THEN s.full_name   -- Store ➜ CC
        ELSE 
            ss.full_name           -- Substore ➜ CC
    END AS "Store/Substore",

    'COST CENTER' AS "Issued To",

    cc.cost_center_name AS "Sub-Store/Cost Center",

    CASE
        WHEN icc.issue_sub_store_id = 0
            THEN r.receipt_number
        ELSE
            issu.issue_direct_number
    END AS "Issue From Batch",
	
	issufrm.issue_direct_number AS "Issue To Batch",
    igt.description AS "Group Type",
    ig.full_name AS "Item Group",
    i.full_name AS "Item",
    uom.description AS "Uom",
    icc.quantity AS "Qty",
    icc.rate AS "Rate",
	icc.rate * icc.quantity AS "Value"

FROM inv.tbl_issue_cost_center icc

JOIN inv.tbl_issue issufrm
    ON icc.issue_id = issufrm.id

LEFT JOIN inv.tbl_issue_sub_store iss
    ON icc.issue_sub_store_id = iss.id

LEFT JOIN inv.tbl_issue issu
    ON iss.issue_id = issu.id

JOIN app.tbl_fiscal_year fy
    ON issufrm.fiscal_year_id = fy.id

JOIN inv.tbl_item i
    ON icc.item_id = i.id

JOIN inv.tbl_store_item_group ig
    ON i.store_item_group_id = ig.id

JOIN public.tbl_static_data_value igt
    ON ig.static_item_group_type_id = igt.id 
    AND igt.static_data_id = 17

JOIN public.tbl_static_data_value uom
    ON icc.static_uom_id = uom.id 
    AND uom.static_data_id = 18

LEFT JOIN inv.tbl_store ss
    ON iss.to_sub_store_id = ss.id

JOIN inv.tbl_store s
    ON i.store_id = s.id

LEFT JOIN public.tbl_static_data_value isf
    ON s.static_store_type_id = isf.id 
    AND isf.static_data_id = 15
	
LEFT JOIN public.tbl_static_data_value issb
	ON ss.static_store_type_id = issb.id
	AND issb.static_data_id = 15

JOIN app.tbl_cost_center cc
    ON icc.to_cost_center_id = cc.id
	
LEFT JOIN inv.tbl_receipt_detail rd
	ON icc.receipt_detail_id = rd.id
	
LEFT JOIN inv.tbl_receipt r
	ON rd.receipt_id = r.id

WHERE 
    fy.id = 6
    AND icc.is_active = TRUE
    AND icc.is_deleted = FALSE

	)
TO 'D:\Aegis Software\Issue_Report.csv'
WITH (FORMAT CSV, HEADER TRUE);