
CREATE OR REPLACE VIEW public.stocks_export AS
 SELECT DISTINCT ON (stocks.id) stocks.id,
    1 AS company_id,
    1 AS status_id,
    stocks.uuid,
    upper(stocks.lot_no) AS stocks_report_lot_no,
    items.sku AS stocks_report_sku,
    stocks.parent_sku AS stocks_report_parent_sku,
    final_items.sku AS stocks_report_final_sku,
    final_item_types.name AS stocks_report_final_item_type,
    upper(items.name) AS stocks_report_description,
    upper(curr_uom.name) AS stocks_report_uom_current,
    stocks.quantity * COALESCE(u.conversion_factor, 1::numeric) AS stocks_report_uom_quantity,
    upper(u.uom_name) AS stocks_report_uom,
    stocks.quantity * curr_uom_c.conversion_factor - stocks.quantity AS stocks_report_allocated_qty,
    stocks.quantity AS stocks_report_balance_qty,
    upper(u1.uom_name) AS stocks_report_uom1,
        CASE
            WHEN u1.uom_name <> ''::citext THEN round(stocks.quantity / COALESCE(u1.conversion_factor, 1::numeric), 2)
            ELSE NULL::numeric
        END AS stocks_report_uom_quantity1,
    u2.uom_name AS stocks_report_uom3,
        CASE
            WHEN u2.uom_name <> ''::citext THEN round(stocks.quantity / COALESCE(u2.conversion_factor, 1::numeric), 2)
            ELSE NULL::numeric
        END AS stocks_report_uom_quantity2,
    stocks.serial_no AS stocks_report_serial_no,
    upper(stocks.zain_tag) AS stocks_report_zain_tag,
    upper(item_conditions.name) AS stocks_report_item_condition,
    upper(manufacturers.name) AS stocks_report_manufacturer,
    upper(site_origin.name) AS stocks_report_site_origin,
    upper(site_destn.name::text) AS stocks_report_site_destn,
    EXTRACT(day FROM now() - stocks.created_at) AS stocks_report_age_in_days,
    stocks.storage_age AS stocks_report_storage_age,
        CASE
            WHEN EXTRACT(day FROM now() - stocks.created_at) >= 365::numeric THEN concat(floor(EXTRACT(day FROM now() - stocks.created_at) / 365::numeric) + 0::numeric, ' --', floor(EXTRACT(day FROM now() - stocks.created_at) / 365::numeric) + 1::numeric)
            ELSE '0--1'::text
        END AS stocks_report_age_in_years,
    upper(stocks.namma_reference_number::text) AS stocks_report_lsc_ref,
    upper(vendors.name) AS stocks_report_vendor,
    upper(departments.name) AS stocks_report_department,
    upper(mi_teams.name) AS stocks_report_team,
    upper(old_wh.name) AS stocks_report_old_wh,
    upper(new_wh.name::text) AS stocks_report_new_wh,
    upper(final_items.name) AS stocks_report_final_items_description,
    upper(new_wh_loc.name) AS stocks_report_new_wh_loc,
    upper(units_of_measure.name) AS stocks_report_final_sku_uom,
    upper(new_region.name) AS stocks_report_new_region,
    upper(item_actions.name) AS stocks_report_item_action,
    upper(
        CASE
            WHEN stocks.is_item_reusable = true THEN 'Yes'::text
            ELSE 'No'::text
        END) AS stocks_report_is_item_reusable,
    upper(concat(stocks.requester_name)) AS stocks_report_requestor_name,
    --date(stocks.mtr_request_datetime) AS stocks_report_add_date,
    --date(stocks.updated_at) AS stocks_report_edit_date,
    upper(to_char(stocks.created_at, 'DD-Mon-YY')::text) AS stocks_report_add_date,
    upper(to_char(stocks.updated_at, 'DD-Mon-YY')::text) AS stocks_report_edit_date,
    upper(stocks.remarks) AS stocks_report_remarks,
    upper(item_types.name) AS stocks_report_item_type,
    upper(pallets.name) AS stocks_report_pallet,
    upper(stocks.parent_tag) AS stocks_mrn_parent_tag,
    upper(stocks.mtr_seq) AS stocks_mtr_seq,
    upper(stocks.amu_response::text) AS stocks_amu_response,
    upper(stocks.amu_ref::text) AS stocks_amu_ref,
    stocks.amu_nbv AS stocks_amu_nbv,
    upper(stocks.wh_remarks) AS stocks_mrn_wh_remarks,
    CASE
    WHEN stocks.sys_tag_status = 1 THEN 'PARTIAL'::text
    ELSE NULL::text
END AS stocks_sys_tag_status,

        
    upper(stocks.po_number) AS stocks_mtr_po_number,
    upper(proj.name) AS stocks_mtr_project_name,
    upper(stocks.zain_project_owner_id) AS stocks_mtr_zain_project_owner,
    upper(teams.name) AS stocks_mtr_item_related_to_team,
    upper(
        CASE
            WHEN stocks.is_sub_item = 1 THEN 'MAIN-ITEM'::text
			WHEN stocks.is_sub_item = 2 THEN 'SUB-ITEM'::text
			WHEN stocks.is_sub_item = 3 THEN 'UNKNOWN'::text
            
        END) AS stocks_mtr_main_sub,
        upper(CASE
            WHEN stocks.is_sub_item = 2 THEN stocks.main_item_id
            
        END) AS stocks_mtr_main_seq,
    mtrl.item_dimensions AS stocks_mi_item_dimension,
    upper(stocks.mtr_reference_number) AS stocks_mtr_reference_number,
    stocks.status_id AS status,
	stocks.status_remarks AS status_remarks
   FROM stocks
     LEFT JOIN items ON stocks.item_id = items.id
     
     LEFT JOIN items parent_items ON stocks.parent_item_id = parent_items.id
     LEFT JOIN items final_items ON stocks.final_item_id = final_items.id
     LEFT JOIN item_types final_item_types ON final_items.item_type_id = final_item_types.id
     LEFT JOIN mrn_line_items l ON stocks.zain_tag::text = l.zain_tag::text
     LEFT JOIN units_of_measure curr_uom ON stocks.uom_id = curr_uom.id
     LEFT JOIN item_uoms curr_uom_c ON stocks.uom_id = curr_uom_c.uom_id AND stocks.item_id = curr_uom_c.item_id
     LEFT JOIN mtr_line_items mtrl ON stocks.zain_tag::text = mtrl.zain_tag::text
     LEFT JOIN ( SELECT r_uom.name AS uom_name,
            item_uoms_1.conversion_factor,
            item_uoms_1.item_id,
            item_uoms_1.uom_id,
            item_uoms_1.is_base_uom,
            item_uoms_1.id,
            row_number() OVER (PARTITION BY item_uoms_1.item_id ORDER BY item_uoms_1.id) AS uoms_rank
           FROM item_uoms item_uoms_1
             LEFT JOIN units_of_measure r_uom ON r_uom.id = item_uoms_1.uom_id) u ON u.item_id = items.id AND u.is_base_uom = true AND u.uoms_rank = 1
     LEFT JOIN ( SELECT r_uom.name AS uom_name,
            item_uoms_1.conversion_factor,
            item_uoms_1.item_id,
            item_uoms_1.uom_id,
            item_uoms_1.is_base_uom,
            item_uoms_1.id,
            row_number() OVER (PARTITION BY item_uoms_1.item_id ORDER BY item_uoms_1.id) AS uoms_rank
           FROM item_uoms item_uoms_1
             LEFT JOIN units_of_measure r_uom ON r_uom.id = item_uoms_1.uom_id) u1 ON u1.item_id = items.id AND u1.uoms_rank = 2
     LEFT JOIN ( SELECT r_uom.name AS uom_name,
            item_uoms_1.conversion_factor,
            item_uoms_1.item_id,
            item_uoms_1.uom_id,
            item_uoms_1.is_base_uom,
            item_uoms_1.id,
            row_number() OVER (PARTITION BY item_uoms_1.item_id ORDER BY item_uoms_1.id) AS uoms_rank
           FROM item_uoms item_uoms_1
             LEFT JOIN units_of_measure r_uom ON r_uom.id = item_uoms_1.uom_id) u2 ON u2.item_id = items.id AND u2.uoms_rank = 3
     LEFT JOIN item_uoms ON stocks.item_id = item_uoms.item_id AND stocks.uom_id = item_uoms.uom_id AND item_uoms.is_base_uom = true
     LEFT JOIN item_conditions ON stocks.item_condition_id = item_conditions.id
     LEFT JOIN manufacturers ON items.manufacturer_id = manufacturers.id
     LEFT JOIN sites site_origin ON stocks.site_id_origin = site_origin.id
     LEFT JOIN sites site_destn ON stocks.site_id_destn = site_destn.id
     LEFT JOIN mrn_header h ON l.mrn_header_id = h.id --no needed
     LEFT JOIN min_line_items minl ON stocks.zain_tag::text = minl.zain_tag::text
     LEFT JOIN min_header minh ON minl.min_header_id = minh.id AND minh.min_status = 'completed'::"MinStatus" -- no needed
     LEFT JOIN vendors ON stocks.vendor_id = vendors.id
     LEFT JOIN departments ON items.department_id = departments.id
     LEFT JOIN teams ON stocks.team_id = teams.id
     LEFT JOIN teams mi_teams ON items.team_id = mi_teams.id
     LEFT JOIN warehouses old_wh ON stocks.old_warehouse_id = old_wh.id
     LEFT JOIN warehouses new_wh ON stocks.warehouse_id = new_wh.id
     LEFT JOIN regions new_region ON stocks.region_id = new_region.id
     LEFT JOIN units_of_measure ON stocks.uom_id = units_of_measure.id
     LEFT JOIN bins new_wh_loc ON stocks.bin_id = new_wh_loc.id
     LEFT JOIN item_actions ON stocks.item_action_id = item_actions.id
     LEFT JOIN item_types ON items.item_type_id = item_types.id
     LEFT JOIN pallets ON stocks.pallet_id = pallets.id
     LEFT JOIN item_uoms mirl_uom ON mirl_uom.item_id = minl.item_id AND mirl_uom.uom_id = minl.uom_id
     LEFT JOIN projects proj ON proj.id = stocks.project_id
  WHERE stocks.company_id = 1 AND stocks.status_id <> 3 AND stocks.quantity <> '0'::numeric
  GROUP BY l.quantity,stocks.zain_project_owner_id, curr_uom.name, curr_uom_c.conversion_factor, stocks.id, stocks.uuid, stocks.lot_no, items.sku, parent_items.sku, final_items.sku, final_item_types.name, items.name, u.uom_name, stocks.quantity, u1.uom_name, u2.uom_name, stocks.serial_no, stocks.zain_tag, item_conditions.name, manufacturers.name, site_origin.name, site_destn.name, stocks.namma_reference_number, vendors.name, departments.name, teams.name, old_wh.name, new_wh.name, final_items.name, new_wh_loc.name, units_of_measure.name, new_region.name, item_actions.name, stocks.is_item_reusable, stocks.requester_name, stocks.created_at, stocks.updated_at, stocks.remarks, item_types.name, pallets.name, stocks.storage_age, mirl_uom.conversion_factor, u1.uom_id, stocks.uom_id, u1.conversion_factor, u2.uom_id, u2.conversion_factor, stocks.mtr_reference_number, stocks.parent_tag, stocks.mtr_seq, stocks.amu_response, stocks.amu_ref, stocks.amu_nbv, stocks.wh_remarks, stocks.po_number, proj.name, stocks.is_sub_item, stocks.main_item_id, mtrl.item_dimensions, u.conversion_factor,stocks.status_remarks,mi_teams.name;
