SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW al_purchase_order_item_view AS SELECT DISTINCT req.request_id AS request_id,req.req_for_user_id AS request_for_user_id,req.status AS request_status,req.name AS request_name,sub_detl_item.id AS request_item_id,req_sts.status AS item_status,item_sts_def.description AS item_status_description,sub_detl.numeric_1 AS item_quantity,rate_def.item_text AS item_description,lnk_rate_model.model_uuid AS item_model_uuid,model.name AS item_model_name,model.preferred_seller_uuid AS item_preferred_seller_uuid,company.company_name AS item_preferred_seller_name,al_po_item.purchase_order_item_id AS purchase_order_item_id,al_po_item.purchase_order_id AS purchase_order_id,al_po_item.version_number AS version_number,'' AS currency_type_id,0 AS item_unit_price,0 AS item_tax,0 AS item_total_amount FROM usm_request req INNER JOIN usm_request_status req_sts ON req_sts.request_id = req.request_id INNER JOIN usm_subscription_detail sub_detl ON sub_detl.id = req_sts.subscription_detail_id AND sub_detl.numeric_1 > 0 AND sub_detl.status = req_sts.status INNER JOIN usm_rate_definition rate_def_row ON rate_def_row.item_id = sub_detl.item_id INNER JOIN usm_rate_definition rate_def ON rate_def.rate_plan_id = sub_detl.rate_plan_id AND rate_def.rate_col = 0 AND rate_def.rate_row = rate_def_row.rate_row INNER JOIN usm_subscription_detail sub_detl_item ON sub_detl_item.item_id = rate_def.item_id AND sub_detl_item.status > 0 AND sub_detl_item.request_id = sub_detl.request_id INNER JOIN al_usm_request_item_status item_sts_def ON item_sts_def.status_id = req_sts.status INNER JOIN usm_link_rateitem_model lnk_rate_model ON lnk_rate_model.rate_item_id = rate_def.item_id INNER JOIN ca_model_def model ON model.model_uuid = lnk_rate_model.model_uuid LEFT OUTER JOIN ca_company company ON company.company_uuid = model.preferred_seller_uuid LEFT OUTER JOIN al_purchase_order_item al_po_item ON al_po_item.request_item_id = sub_detl_item.id

GO
GRANT SELECT ON  [dbo].[al_purchase_order_item_view] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_purchase_order_item_view] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_purchase_order_item_view] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_purchase_order_item_view] TO [swcmadmin]
GO