Question: create a process download for alteryx that can do the PO splitting. I want to place a script in alteryx that will perform all the
create a process download for alteryx that can do the PO splitting. I want to place a script in alteryx that will perform all the required steps to come out with the right solution: Audit Purchase Orders to Find PO Splitting FinanceAudit Overview In most companies, employees with purchasing authority have a monetary limit on how much they can purchase without management authorization. For example, an IT manager may be able to purchase up to $ in software and equipment. Any amount over that would require director approval. In practice, this means that the IT manager requires director approval for any Purchase Order PO with a purchase amount over $ If a manager wants to purchase $ they could try to get around this by issuing two POs to the same vendor totaling $ but each being less than $ This is a violation of corporate compliance rules known as PO Splitting. Objective Create a simple workflow to detect PO splitting. In this case we are looking for multiple POs to the same vendor within a threeday period. At this point we are not checking to see if the same buyer is issuing the POs. It is possible that there is collusion between employees to hide the splitting so there would need to be further examination of the POs found by our analysis Processes The input file has PO lineitem detail. The first step is to remove inactive POs eliminate fields specific to the PO line, and retain only one record for each PO Then identify the vendors to whom multiple POs have been issued. Determine if there are any vendors where the interval between their consecutive transaction dates is days or less. Output this list for further investigation. ie generate an Excel file output of your findings Data Dictionary Purchase Orders.xlsx Column Name Data Type Description POID Double Purchase Order ID BuyerName VString PO originator buyer CloseDate Date PO close date CompanyID VString Company division ID CreateDate DateTime Date PO created InvoicedAmt Double Total cash amount invoiced to data OrderedQuantity Double Quantity ordered line item IsClosedForInvoiced VString PO Status IsClosedForReceiving VString PO Status PurchAmt Double Total cash amount of invoice TranStatus VString Transaction status Open if field is empty SalestaxAmt Double Sales tax on entire PO TranAmt Double Transaction amount TranDate Date Transaction date TranNo VString Transaction number TranType VString Purchase Order Standard VendorID Double Vendor ID PaymentTerm VString Payment terms DueDayOrMonth Double Due date PmtTermsID VString Payment terms code VouchID Double Voucher ID line item POLineKey Double Identifier for PO Line line item POLineNo Double PO line number line item IsReceivingRequested Double How to receive item line item POLineStatus VString PO Line Status line item, but needed for initial cleansing if any line is active, the PO is active TargetCompanyID VString Varies by line item UnitCost Double Unit cost line item UnitMeasure VString Unit measure line item Guidance, tips and hints: Step : Using the Input Data function Using Filter function, POLineStatus'Inactive' Using Unique function for POID Step : Using Summarize function, group by VendorID, count VendorID Using Filter function, Count Inner join the result from step and step Step : Using Sort function, VendorID and TranDate in Ascending order Using MultiRow Formular function, select Create New Field named TESTTranDate group by VenderID, Expression DATETIMEDIFFRow:TranDateTranDate"DAYS" Using MultiRow Formular function, select Create New Field named PrevTESTTranDate group by VenderID, Expression DATETIMEDIFFTranDateRow:TranDate"DAYS" Using Filter function, customer filter as TESTTranDate OR PrevTESTTranDate
Step by Step Solution
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
