August 18, 2016 at 7:42 am
WE have the following syntax to create a view to pull both invoice and collection data in 1 query that will be used for our reporting tool. I need to create a flag field so we can either filter on the invoice date or collection date based on the users preference. I will be creating a parameter at the report level to allow the users to select either Invoice Date or Collection but I need the view to store that value and filter correctly.
SELECT
eng.sales_doc_no AS Eng,
CASE WHEN eng.doc_user_status_code BETWEEN 31 AND 34 THEN 'Confidential' ELSE eng.customer_name_sold_to END AS Client,
CASE WHEN eng.doc_user_status_code BETWEEN 31 AND 34 THEN 'Confidential' ELSE cus.city END AS Client_city,
CASE WHEN eng.doc_user_status_code BETWEEN 31 AND 34 THEN 'Confidential' ELSE cus.region_code END AS Client_state,
CASE WHEN eng.doc_user_status_code BETWEEN 31 AND 34 THEN 'Confidential' ELSE cus.country_code END AS Client_country,
CASE WHEN eng.doc_user_status_code BETWEEN 31 AND 34 THEN 'Confidential' ELSE eng.position END AS Position,
ord.order_reason AS Work_type,
doc.doc_type,
eng.sales_org,
emp.last_name + ', ' + emp.first_name AS Revenue_Mgr,
ofr.office_rpt AS Revenue_Mgr_Office,
bof.billing_office,
org.region_code AS Billing_Region,
sa1.last_name + ', ' + sa1.first_name AS SA_1,
sa2.last_name + ', ' + sa2.first_name AS SA_2,
sa3.last_name + ', ' + sa3.first_name AS SA_3,
eng.open_date AS Confirmation_date,
eng.accnt_open_date,
eng.accnt_close_date,
esm.fee_estimate AS Fee_Est_USD,
esm.final_fee AS Final_Fee_USD,
eng.eng_currency_code AS Eng_Curr,
CASE WHEN eng.eng_currency_code = 'USD' THEN esm.fee_estimate ELSE esml.fee_estimate END AS Fee_Est_Eng_Curr,
CASE WHEN eng.eng_currency_code = 'USD' THEN esm.final_fee ELSE esml.final_fee END AS Final_Fee_Eng_Curr,
inv.invoice_date,
inv.invoice_no AS Invoice_Number,
idsc.description AS Invoice_Desc,
bil.fee_amount AS Invoice_Fee_USD,
bil.float_amount AS Invoice_Float_USD,
bil.exp_amount AS Invoice_Exp_USD,
bil.tax_amount AS Invoice_Tax_USD,
CASE WHEN eng.eng_currency_code = 'USD' THEN bil.fee_amount ELSE bill.fee_amount END AS Invoice_Fee_Eng_Curr,
CASE WHEN eng.eng_currency_code = 'USD' THEN bil.float_amount ELSE bill.float_amount END AS Invoice_Float_Eng_Curr,
CASE WHEN eng.eng_currency_code = 'USD' THEN bil.exp_amount ELSE bill.exp_amount END AS Invoice_Exp_Eng_Curr,
CASE WHEN eng.eng_currency_code = 'USD' THEN bil.tax_amount END AS Invoice_Tax_Eng_Curr,
col.collection_date,
col2.fee_amount AS collected_fee_USD,
col2.float_amount AS collected_float_USD,
col2.exp_amount AS collected_exp_USD,
col2.tax_amount AS collected_tax_USD,
col.fee_amount AS collected_fee_Eng_Curr,
col.float_amount AS collected_float_Eng_Curr,
col.exp_amount AS collected_exp_Eng_Curr,
col.tax_amount AS collected_tax_Eng_Curr,
20 AS report_id,
cus.ult_parent_no
FROM dbo.spm_engagement AS eng INNER JOIN
dbo.spm_customer AS cus ON cus.customer_no = eng.customer_no_sold_to LEFT OUTER JOIN
dbo.spm_eng_summary AS esml ON esml.sales_doc_no = eng.sales_doc_no AND esml.currency_code = eng.eng_currency_code LEFT OUTER JOIN
dbo.spm_eng_summary AS esm ON esm.sales_doc_no = eng.sales_doc_no AND esm.currency_code = 'USD' LEFT OUTER JOIN
dbo.spm_employee AS emp ON emp.emp_id = eng.rev_manager_emp_id LEFT OUTER JOIN
dbo.spm_employee AS sa1 ON sa1.emp_id = eng.consultant1_emp_id LEFT OUTER JOIN
dbo.spm_employee AS sa2 ON sa2.emp_id = eng.consultant2_emp_id LEFT OUTER JOIN
dbo.spm_employee AS sa3 ON sa3.emp_id = eng.consultant3_emp_id LEFT OUTER JOIN
dbo.spm_office_rpt AS ofr ON ofr.office_rpt_code = emp.office_rpt_code LEFT OUTER JOIN
dbo.spm_eng_order_reason AS ord ON ord.order_reason_code = eng.order_reason_code LEFT OUTER JOIN
dbo.spm_eng_doc_type AS doc ON doc.doc_type_code = eng.doc_type_code INNER JOIN
dbo.spm_eng_billing_office AS bof ON bof.billing_office_code = eng.billing_office_code INNER JOIN
dbo.spm_sales_org AS org ON org.sales_org = bof.sales_org INNER JOIN
dbo.spm_eng_invoice AS inv ON inv.sales_doc_no = eng.sales_doc_no LEFT OUTER JOIN
dbo.spm_inv_desc AS idsc ON idsc.desc_code = inv.desc_code INNER JOIN
dbo.spm_eng_billed AS bill ON bill.invoice_no = inv.invoice_no AND bill.currency_code = eng.eng_currency_code INNER JOIN
dbo.spm_eng_billed AS bil ON bil.invoice_no = inv.invoice_no AND bil.currency_code = 'USD' LEFT OUTER JOIN
dbo.spm_eng_collected AS col ON col.invoice_no = inv.invoice_no AND bill.currency_code = col.currency_code LEFT OUTER JOIN
dbo.spm_eng_collected AS col2 ON col2.invoice_no = inv.invoice_no AND bil.currency_code = col2.currency_code
WHERE (inv.reversal_flag = 0) AND (eng.rejection_status <> 'C')
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply