DPR reconciliation

DPR reconciliation: -

Description: -

This document will provide details for a new report created in i21, where the customers should be able to see the sum of all the transactions those were logged between a specific time frame which have an overall net price risk impact, with an option to drill down across different buckets.

Note: - There can be transaction which do not have net overall impact on the price risk such as delivery against the purchase/sales contract, which will have offsetting transactions therefore these records will NOT be shown in the report. Also the basis contracts will be out of scope (although partial priced basis is in scope of the DPR recon)

  Assumptions: -

• Customers will run this report daily (start of the day) between T-1 and T

• Backdating/multiple days run (not an ideal scenario, although allowed) can have an impact on the performance of this new report

• The DPR recon report will not be saved anywhere and is on demand report (users can simply run the report based on the selected from/to date and time frame) 
• When the DPR or the DPR recon is being run and around same time some transactions are being logged into the system then there can be issues capturing the correct transactions for the given time stamp (timing issues)
• Backdating un-distribution cases for load variance will NOT show in the DPR recon
• DPR recon vs DPR may not display the exact same results i.e. the purpose of DPR recon is to ONLY show the transactions executed within a given time frame (from & to date/time) and those have an impact on the price risk but may not match with the DPR necessarily

           Example -

• • If a 150 BU sales basis contract 123, was partially priced for 80 BU at 8 AM in the morning for a given day and further on the same day at 9:15 AM this priced quantity was changed by 20 then final qty becomes 100 BU (priced) and the users run the DPR later, it shows a price risk of 100 BU for the contract 123 (assuming no other transactions were executed against it)
  • But in this case the DPR recon report is run in the evening same day from 9:15 AM to 7:00 PM, then the transaction qty captured within the mentioned time frame for sales basis contract 123 will be 20 BU and NOT 100 BU.

Interface to run the DPR reconciliation report

• Reconciliation interface will be placed in Risk Management Module 
• This report will be user access controlled. The user having access to Daily position report should be able to access the DPR report

DPR Reconciliation report request screen

• When the user selects DPR Reconciliation Report menu option, a new screen will open where user can select the commodity from the drop down
•  The respective from and to (Date & time) can be selected in the HH:MM AM/PM format as shown below for the time frame for which the required transactions needs to viewed (Future dates is not an ideal scenario but NO validations around these cases as of now)
• Select the required date/time (‘Today’ will default to current date and ‘Now’ to the current system time, although it can be overridden manually and also from the hour/minute slider, as below) and once the required from and to date is selected click OK and further ‘Run’ the report

• The users will be allowed to select any 'From and To' date range (no restrictions as of now) but for a date range greater than three days when Run button is clicked a soft warning message will be displayed - 'Report generation will take time to run on the selected date range. Do you wish to continue'. If the user selects 'Yes' the request will be executed based on the date range given, if the user selects 'No' the request will be terminated and pop up will be closed

Note:-

• The commodity and date & time selection list box should also be defaulted to blank when the report DPR recon screen opens and multi commodity selection from the drop down should not be possible, only single selection should be made

Run :-

• The Run button on top left, will execute the DPR recon report for the selected combination of commodity (from the drop down) and the from & to date and time stamp.
• The Run button will only execute the DPR recon report if both the criteria (commodity + from to date /time) have been entered, if any one or both criteria is/are missing then the report will NOT run.
• If the 'Run' is executed when we already have multiple tabs open in the report, then all the existing tabs will be closed and refreshed summary tab will only be displayed and users have to again open the required drill downs. The filter criteria on the input fields (commodity, from &to date/time) of the DPR recon remain As Is      

Export all: -

• When 'Export All' is executed, it will export all the data present in the summary tab along with data for all the other open individual tabs from the report into the individual excel sheet tab.
• The export functionality will export data (from and to date & time) for a time period of three days. For a period, greater than 3 days, the 'Export All' will be greyed out in the main DPR recon screen and users won't be able to select it
• When the users click on 'Export All' a message will be displayed 'All the open tabs will be exported, do you wish to continue' and when users click on 'Yes' the request will be executed and when 'No’ the process will be terminated and the pop up will be closed
• The Commodity, 'From and To date/time' details will be displayed in the exported excel file

Clear

• 'Clear' button will clear all the filters (Commodity, from & to date) in the input screen of DPR recon and if 'Clear' is executed when no filters/input are present then nothing changes
• In case the clear button is executed & tabs (summary or any individual tab) are already opened then every tab will be closed, input filters will be cleared, and a screen as below will be shown

Refresh -

• The behaviour will be same as that of current Refresh button in other risk screen. i.e when the refresh is done all the existing open tabs will be closed and a regenerated summary tab will only be displayed. The filter criteria on the input field (commodity, From & to date, time) will remain As IS

Close -

• The 'Close' button will close the entire DPR report screen (with or without any filters/tab)

Print -

• Once the 'Print' button is clicked, an info pop up is shown - 'Only the Summary tab details will be printed' (Once users click OK a request to print will be executed and summary tab print details will be shown in a new screen)
• The Print functionality will only print the 'summary ' tab section of DPR reconciliation report (in the below screen shot format) and NOT any other open tabs/individual buckets i.e. if multiple tabs are opened along with the summary tab, only the 'summary tab' details will be printed 
• If User is on a different tab than the 'summary tab 'and clicks on the print, then also the 'summary' tab details will be printed

DPR Reconciliation output

• After the users execute ‘Run’ DPR Reconciliation results will be displayed in one main section 'Summary'
• Summary Tab will list all the results for the selected date & time stamp and Commodity combination selected as parameters for running the reconciliation
• The report will be divided into priced purchase contracts, priced sales contracts, and futures

Note – In the summary tab of the DPR recon, for any of the bucket if no data/transactions is present then those buckets will be shown as NULL (blank) but in case any bucket shows zero (it contains transactions which net out to zero and can be drill down)

Drill downs:-

• Once the report is run and the results will be displayed in the 'Summary section' of the report the users should be able to drill down (hyperlink to the qty displayed) in the respective buckets/rows to see the transactions behind each of the buckets
• The report results for the drill down should be displayed in the respective time zone of the user who is running the report. Once the hyperlink is clicked for a particular bucket a new tab will open in the main report along with the summary tab, containing details of the transactions for the respective bucket/rows (as below)

• The new tab opened will have the necessary details such as Vendor/customer, commodity, contract, create date/time, quantity, UOM and the action performed.

Individual buckets in the DPR recon:-

1. New priced purchase/sale contract- It is the sum of contract type purchase/sale and pricing type as priced, where the create date is between from and to date/time of the run
2. New HTA purchase/sale contract- It is the sum of contract type purchase/sale and pricing type as HTA, where the create date is between from and to date/time of the run
3. Spot purchase/sale - 
1. Spot scale ticket - It is the sum of spot purchase/sale quantity from the scale ticket where the distributed date is between from and to date/time of the run
2. Spot storage settlement- It is the sum of spot purchase/sale quantity from the storage settlement where the posted date is between from and to date/time of the run (it includes the spot against the DP)
4.  Purchase/sale basis pricing- The sum of the priced quantity of purchase/sale basis contract where the pricing date is between from and to date/time of the run. 
5. Purchase/sale Qty adjustment - The sum of contract sequence quantity change (up or down) where the change date is between from and to date/time of the run.
6. Purchase/sale Load Variance - Sum of the difference of load qty and shipped qty for the priced portion of the load-based purchase/sale contract between from and to date/time of the run for the priced/partial priced load contract.
7. Purchase/sale Short close - Sum quantity of purchase/sale priced contracts where status is changed from open to short closed between from and to date/time of the run
8. Purchase/sale Cancel quantity - Sum quantity of purchase/sale priced contracts where status is changed from open to cancel between from and to date/time of the run
9. Net Futures - Sum of the total derivatives entered/updated between from and to date/time of the run

Few examples –

Scenario 1: - Slicing and qty change on a sequence for Sales priced contract

Actual DPR recon report summary tab (as below) –

1. For New priced sales contract for 300 BU and slicing step

         If we Drill down to the respective buckets -

         New priced sales contract

Sales qty adjustment- (slicing)

Note – Sliced transactions are shown in the sales qty adjustment bucket. In the above scenario the 300 qty on the contract sequence-1 was sliced into three sequences for 120, 120 , 60 BU. Therefore, the sequence 1 was reduced by 180 BU (update contract i.e. 300-180=120), sequence-2 for 120 BU (create) and sequence-3 for 60 BU (create). The net change to the qty remains As Is i.e 0.00

2. Now change qty on sequence -3 from 60 BU to 50 BU

            DPR recon Summary tab -

           Drill down-

           Sales qty adjustment

Scenario 2:- For basis contract, partial pricing and price deletion

1.  Actual DPR recon screen for Create price and then delete and change price.

           Sales basis pricing drill down -

Scenario :-3  Cancel and reopen partially priced sales basis contract

DPR recon report-

1. After partial pricing for 100 BU and cancellation of sequence, DPR recon report as below

Drill down –

          Sale basis pricing

           Sales Cancelled

             2. Now re-open the sequence.

               DPR recon summary tab-

               Note – The reopen of the sequence will be shown in the’ sales qty adjustment’ bucket

               Drill down

               Sales qty adjustment

Scenario 4:- Sale Load Variance for distribute and undistributed case

1. Create sales Load contract for 2 loads and distribute, undistributed of 250 BU and again distribute for 216 BU

           DPR recon screen summary tab for the selected time frame shows as below –

Drill down on the respective bucket -

Sales load variance drill down tab displays the un-distribution and again the distribution with new qty in ‘Action’ column, as below

   New Priced sale contract drill down