Invoice_extraction - It extracts the information (Invoice no, invoice date etc…) from the invoice and save it in an excel file.
Sql extraction - It extracts the information from the sql table with information like (invoice no, invoice date etc…)
Problem : Currently the sql table extracts the big table and its huge. Instead is it possible that it can only extract the data for couple of invoice no (which was extracted in the first step). To make it more meaningful pass the output (invoice no) from invoice_extraction flow the sql query.
Please find below the snap shot
Current query
“SELECT T1.[SYS_ID] as System_id
,T1.[ACC_DOC_NUM] as Doc_Num
,T1.[DOC_DATE] as Date
,T1.[REF_DOC_NUM] as Invoice
,T2.[PO_DOC_FX_VALUE] as TOTAL
,T2.[VENDOR_NO] as Vendor_No
,T3.VENDOR_NAME_1 as Vendor_Name
FROM [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER JOIN [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 ON T1.SYS_ID=T2.SYS_ID AND T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] AND T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT JOIN [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 ON T1.SYS_ID = T2.SYS_ID AND T2.VENDOR_NO = T3.VENDOR_NO WHERE T2.VENDOR_NO =xxxxxxxxx’
AND T1.[DOC_TYPE]=‘RE’”
To be modified to
“SELECT T1.[SYS_ID] as System_id
,T1.[ACC_DOC_NUM] as Doc_Num
,T1.[DOC_DATE] as Date
,T1.[REF_DOC_NUM] as Invoice
,T2.[PO_DOC_FX_VALUE] as TOTAL
,T2.[VENDOR_NO] as Vendor_No
,T3.VENDOR_NAME_1 as Vendor_Name
FROM [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER JOIN [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 ON T1.SYS_ID=T2.SYS_ID AND T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] AND T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT JOIN [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 ON T1.SYS_ID = T2.SYS_ID AND T2.VENDOR_NO = T3.VENDOR_NO
WHERE T2.VENDOR_NO =‘xxxxxxxxxx’
AND T1. Invoice = (Invoice no from the output of the invoice_extraction flow)
AND T1.[DOC_TYPE]=‘RE’”
Please let me know if its not clear and if its possible,
Here as you are using 2 workflows, proper declaration of arguments and passing of valuues from arguments to the variables declared in the main workflow should happen.
Its same like how you are calling function sin programming language. If you want to pass value to one workflow, you need to create argument with In direction and for output argument with out direction. After that you can concatenate that with the sql query.
Excellent. Thank you so much. Really appreciate it for the time and effort.
I understand the flow. But one thing i need further help is how should i pass inside the sql query to extract only for that invoice number?
“SELECT T1.[SYS_ID] as System_id
,T1.[ACC_DOC_NUM] as Doc_Num
,T1.[DOC_DATE] as Date
,T1.[REF_DOC_NUM] as Invoice
,T2.[PO_DOC_FX_VALUE] as TOTAL
,T2.[VENDOR_NO] as Vendor_No
,T3.VENDOR_NAME_1 as Vendor_Name
FROM [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER JOIN [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 ON T1.SYS_ID=T2.SYS_ID AND T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] AND T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT JOIN [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 ON T1.SYS_ID = T2.SYS_ID AND T2.VENDOR_NO = T3.VENDOR_NO
WHERE T2.VENDOR_NO =‘xxxxxxxxxx’
AND T1. Invoice = (Invoice no from the output of the invoice_extraction flow)
“SELECT T1.[SYS_ID] as System_id
,T1.[ACC_DOC_NUM] as Doc_Num
,T1.[DOC_DATE] as Date
,T1.[REF_DOC_NUM] as Invoice
,T2.[PO_DOC_FX_VALUE] as TOTAL
,T2.[VENDOR_NO] as Vendor_No
,T3.VENDOR_NAME_1 as Vendor_Name
FROM [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER JOIN [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 ON T1.SYS_ID=T2.SYS_ID AND T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] AND T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT JOIN [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 ON T1.SYS_ID = T2.SYS_ID AND T2.VENDOR_NO = T3.VENDOR_NO
WHERE T2.VENDOR_NO =‘xxxxxxxxxx’
AND T1. Invoice =" & in_InvoiceNumber
in_InvoiceNumber is an argument in the SQL_Extraction Workflow that has invoice number coming from the invoice extraction workflow
Thank you for the help. Really useful. I need one help.
Currently in the Invoice_extraction workflow - Currently there are many columns are extracted from pdf / invoices and exported into excel. I want to pass “only the invoice number column” to the sql query .
Hi @ushu
While executing the query it’s giving me syntax error. Pleaes let me know if i am missing anything also when i tried to give the exact way as you mentioned its giving the same error.
Thank you for all the support. I could manage to run the sql query. However it’s returning empty file with no data. I tried to run the query in the sql editor and i could few records.
Can you please let me know if i am missing anything?
Thank you for the reply.
Please find below the sql query i am using both in (sql server) and inside uipath.
Inside uipath
“Select T1.[SYS_ID] As System_id
,T1.[ACC_DOC_NUM] As Doc_Num
,T1.[DOC_DATE] As Date
,T1.[REF_DOC_NUM] As Invoice
,T2.[PO_DOC_FX_VALUE] As TOTAL
,T2.[VENDOR_NO] As Vendor_No
,T3.VENDOR_NAME_1 As Vendor_Name
From [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER Join [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 On T1.SYS_ID=T2.SYS_ID And T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] And T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT Join [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 On T1.SYS_ID = T2.SYS_ID And T2.VENDOR_NO = T3.VENDOR_NO
Where T1. [REF_DOC_NUM] =‘& in_InvoiceNumber’”
Inside sql server
Select T1.[SYS_ID] As System_id
,T1.[ACC_DOC_NUM] As Doc_Num
,T1.[DOC_DATE] As Date
,T1.[REF_DOC_NUM] As Invoice
,T2.[PO_DOC_FX_VALUE] As TOTAL
,T2.[VENDOR_NO] As Vendor_No
,T3.VENDOR_NAME_1 As Vendor_Name
From [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER Join [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 On T1.SYS_ID=T2.SYS_ID And T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] And T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT Join [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 On T1.SYS_ID = T2.SYS_ID And T2.VENDOR_NO = T3.VENDOR_NO
Where T1. [REF_DOC_NUM] =‘2339575’
ashokkarale
MD_Farhan1
Ajay_Mishra
postwick
Dheerendra_vishwakarma
Anil_G
chandreshsinh.jadeja
Gautham_Pattabiraman
vrdabberu
aravindbalineni123