**********new  **********
CREATE OR REPLACE VIEW `view_packaging_list` AS SELECT
    `dn`.`id` AS `transaction_id`,
    (
        CASE WHEN(`dn`.`file_id` > 0) THEN `dn`.`file_id` ELSE 'N/A'
    END
) AS `file_id`,
`dn`.`delivery_date` AS `transaction_date`,
`dn`.`customer_id` AS `customer_id`,
(
    CASE WHEN(`dn`.`customer_id` > 0) THEN `customer`.`customer_name` ELSE 'N/A'
END
) AS `customer_name`,
'Delivery Note' AS `packing_frm`,
'D' AS `status`,
'' AS `issue_type`
FROM
    (
        `erp_delivery_note` `dn`
    JOIN `erp_customer` `customer` ON
        ((`customer`.`id` = `dn`.`customer_id`))
    )
WHERE
    (
        (`dn`.`ready_to_invoice` = 'N') AND(`dn`.`status` = 'PN')
    )
UNION ALL
SELECT
    `gi`.`id` AS `transaction_id`,
    'N/A' AS `file_id`,
    `gi`.`goods_issue_date` AS `transaction_date`,
    `gi`.`customer_id` AS `customer_id`,
    (
        CASE WHEN(`gi`.`customer_id` > 0) THEN `customer`.`customer_name` ELSE 'N/A'
    END
) AS `customer_name`,
'Inventory Goods Issue' AS `packing_frm`,
'GI' AS `status`,
`gi`.`issue_type` AS `issue_type`
FROM
    (
        `erp_inventory_goods_issue` `gi`
    LEFT JOIN `erp_customer` `customer` ON
        ((`customer`.`id` = `gi`.`customer_id`))
    )
WHERE
    (`gi`.`stock_updated` = 'N')
UNION ALL
SELECT
    `pdn`.`id` AS `transaction_id`,
    `pdn`.`file_id` AS `file_id`,
    `pdn`.`debited_date` AS `transaction_date`,
    `pdn`.`vendor_id` AS `customer_id`,
    (
        CASE WHEN(`pdn`.`vendor_id` > 0) THEN `vendor`.`vendor_name` ELSE 'N/A'
    END
) AS `customer_name`,
'Debitnote' AS `packing_frm`,
'PDN' AS `status`,
'' AS `issue_type`
FROM
    (
        `erp_purchasing_debit_note` `pdn`
    LEFT JOIN `erp_vendor` `vendor` ON
        ((`vendor`.`id` = `pdn`.`vendor_id`))
    )
WHERE
    (`pdn`.`is_goods_returned` = 'N')
UNION ALL
SELECT
    `prgi`.`id` AS `transaction_id`,
    (
        CASE WHEN(`prgi`.`file_id` > 0) THEN `prgi`.`file_id` ELSE 'N/A'
    END
) AS `file_id`,
`prgi`.`goods_issue_date` AS `transaction_date`,
`prgi`.`customer_id` AS `customer_id`,
(
    CASE WHEN(`prgi`.`customer_id` > 0) THEN `customer`.`customer_name` ELSE 'N/A'
END
) AS `customer_name`,
'Production Goods Issue' AS `packing_frm`,
'PRGI' AS `status`,
'' AS `issue_type`
FROM
    (
        `erp_production_goods_issue` `prgi`
    LEFT JOIN `erp_customer` `customer` ON
        (
            (`customer`.`id` = `prgi`.`customer_id`)
        )
    )
WHERE
    (`prgi`.`is_goods_issued` = 'N')
UNION ALL
SELECT
    `dt`.`id` AS `transaction_id`,
    'N/A' AS `file_id`,
    `dt`.`transfer_date` AS `transaction_date`,
    0 AS `customer_id`,
    'N/A' AS `customer_name`,
    'Direct Transfer' AS `packing_frm`,
    'DTOUT' AS `status`,
    '' AS `issue_type`
FROM
    `erp_inventory_direct_transfer` `dt`
WHERE
    (`dt`.`status` = 'T')
UNION ALL
SELECT
    `idt`.`id` AS `transaction_id`,
    'N/A' AS `file_id`,
    `idt`.`transfer_date` AS `transaction_date`,
    0 AS `customer_id`,
    'N/A' AS `customer_name`,
    'Indirect Transfer' AS `packing_frm`,
    'IDTOUT' AS `status`,
    '' AS `issue_type`
FROM
    `erp_inventory_indirect_transfer` `idt`
WHERE
    (`idt`.`is_complete` = 'N')  

*********************new end




(***********    old one :-    SELECT
    `dn`.`id` AS `transaction_id`,
    (
        CASE WHEN(`dn`.`file_id` > 0) THEN `dn`.`file_id` ELSE 'N/A'
    END
) AS `file_id`,
`dn`.`delivery_date` AS `transaction_date`,
`dn`.`customer_id` AS `customer_id`,
(
    CASE WHEN(`dn`.`customer_id` > 0) THEN `customer`.`customer_name` ELSE 'N/A'
END
) AS `customer_name`,
'Delivery Note' AS `packing_frm`,
'D' AS `status`
FROM
    (
        `erp_delivery_note` `dn`
    JOIN `erp_customer` `customer`
    ON
        ((`customer`.`id` = `dn`.`customer_id`))
    )
WHERE
    (
        (`dn`.`ready_to_invoice` = 'N') AND(`dn`.`status` = 'PN')
    )
UNION ALL
SELECT
    `gi`.`id` AS `transaction_id`,
    'N/A' AS `file_id`,
    `gi`.`goods_issue_date` AS `transaction_date`,
    `gi`.`customer_id` AS `customer_id`,
    (
        CASE WHEN(`gi`.`customer_id` > 0) THEN `customer`.`customer_name` ELSE 'N/A'
    END
) AS `customer_name`,
'Inventory Goods Issue' AS `packing_frm`,
'GI' AS `status`
FROM
    (
        `erp_inventory_goods_issue` `gi`
    LEFT JOIN `erp_customer` `customer`
    ON
        ((`customer`.`id` = `gi`.`customer_id`))
    )
WHERE
    (`gi`.`stock_updated` = 'N')
UNION ALL
SELECT
    `pdn`.`id` AS `transaction_id`,
    `pdn`.`file_id` AS `file_id`,
    `pdn`.`debited_date` AS `transaction_date`,
    `pdn`.`vendor_id` AS `customer_id`,
    (
        CASE WHEN(`pdn`.`vendor_id` > 0) THEN `vendor`.`vendor_name` ELSE 'N/A'
    END
) AS `customer_name`,
'Debitnote' AS `packing_frm`,
'PDN' AS `status`
FROM
    (
        `erp_purchasing_debit_note` `pdn`
    LEFT JOIN `erp_vendor` `vendor`
    ON
        ((`vendor`.`id` = `pdn`.`vendor_id`))
    )
WHERE
    (`pdn`.`is_goods_returned` = 'N')
UNION ALL
SELECT
    `prgi`.`id` AS `transaction_id`,
    (
        CASE WHEN(`prgi`.`file_id` > 0) THEN `prgi`.`file_id` ELSE 'N/A'
    END
) AS `file_id`,
`prgi`.`goods_issue_date` AS `transaction_date`,
`prgi`.`customer_id` AS `customer_id`,
(
    CASE WHEN(`prgi`.`customer_id` > 0) THEN `customer`.`customer_name` ELSE 'N/A'
END
) AS `customer_name`,
'Production Goods Issue' AS `packing_frm`,
'PRGI' AS `status`
FROM
    (
        `erp_production_goods_issue` `prgi`
    LEFT JOIN `erp_customer` `customer`
    ON
        (
            (`customer`.`id` = `prgi`.`customer_id`)
        )
    )
WHERE
    (`prgi`.`is_goods_issued` = 'N')
UNION ALL
SELECT
    `dt`.`id` AS `transaction_id`,
    'N/A' AS `file_id`,
    `dt`.`transfer_date` AS `transaction_date`,
    0 AS `customer_id`,
    'N/A' AS `customer_name`,
    'Direct Transfer' AS `packing_frm`,
    'DTOUT' AS `status`
FROM
    `erp_inventory_direct_transfer` `dt`
WHERE
    (`dt`.`status` = 'T')
UNION ALL
SELECT
    `idt`.`id` AS `transaction_id`,
    'N/A' AS `file_id`,
    `idt`.`transfer_date` AS `transaction_date`,
    0 AS `customer_id`,
    'N/A' AS `customer_name`,
    'Indirect Transfer' AS `packing_frm`,
    'IDTOUT' AS `status`
FROM
    `erp_inventory_indirect_transfer` `idt`
WHERE
    (`idt`.`is_complete` = 'N')   ****************)