PNLREP
Business Purpose is to analyse the TRADE positions with their F&O and FX hedging operations. The results are records of TRADE, F&O and FX P&L per contract record based on SaleTTD (when not WIP operation) else based on Sale Invoice Date. The reporting enables viewing Unrealised and Realised P&L by the nature of the Title Transfer date/ status
There are two elements for the PNLREP operation:
Saving data is needed to provide a basis for the Reporting feature i.e. any FREEZE or Current data source can be secured .
Reporting data requires to select the reporting style Summary, Summary By Contract, Detail or Detail Movement whereby other filters , presentation features are enabled for User Interface.
Technical assistance for SMEs only.
Saving data
Selections
FREEZE Data source AND Target Currency for secured values AND Filter for groups e.g. Department, Category, Commodity Region
Data to save
Physicals – Contract information
F&O for pricing and hedging (presence of physical reference and no physical reference). No 'TDR' types.
FX used to cover physicals (presence of physical reference and balance of deal value less physical cover)
EU Licences
General technical:
Create a holding data source for Contract Reference and Sale TTD. Contract and Sale TTD are TRADE where the following criteria is met; Not 3rd Party, Open Record status or allocation is open Record status, Contract or allocation is part of fix/hedge family with open F&O assigned or contract or allocation is part of FX family with open FX assigned
Retrieve cost codes for contracts on temp table
Retrieve Expense Codes and pct heads for the Expense Codes
Retrieve Futures Prices (twice for some reason)
Create Fix Families temp table to hold contract, fix family, kilos and wohead (flag). This table holds fix families and total kilos for the whole family excluding any written off splits. Add to the table fix families where all splits are written off (wohead flag is set).
Create Fx Families temp table to hold contract, fx family, kilos and wohead. This table holds fx families and total kilos for the whole family excluding any written off splits. Add to the table fx families where all splits are written off
Retrieve Invoice percentage temp table which holds invoice details (not reversals) for all contracts not assigned to complete WIP accounts. Create Value Date and Document Date temp tables for the same invoices.
Create other holding data sources
Physfut for F&O trades by fut02_phys and for F&O trades by members of fix family for fut02_phys. Data stored is contract, trade date, serial number, uniqueness. Open and Settled details are retrieved for physical trades not archived
Phystrs for Fx deals by phys11_contract and for Fx deals by members of fx family. Data stored is contract, phys11_contract, fx deal, uniqueness serial number, uniqueness. Current trs table used, no archived deals are included
EU Licence holding tables for F&O and FX
Physicals; Calculate Contract Values for contracts on contract temp table. Read for allocated pair information (quantity, status, invoice information) driven by sales which are on the contract temp table and purchases which are on the contract temp table for non-tolled items. Read for unallocated information (quantity, status, invoice information) for contracts which are on the contract temp table and may be tolled items.
F&O; Not excluded from mepaccure. Open F&O details (value, commission) retrieved for contracts on Fix Family temp table. Include individual split quantity and total quantity for fix family for subsequent pro-ratering. Include the not written-off contracts of fix families or contracts where the whole fix family is written off. Settled F&O details (value, commission) retrieved for contracts on Fix Family temp table. Include individual split quantity and total quantity for fix family for subsequent pro-ratering. Include the not written-off contracts of fix families or contracts where the whole fix family is written off. Open F&O not assigned to physicals. Settled F&O not assigned to physicals which would have been open at the time the frozen database was saved. F&O assigned to licence Purchases and Sales
FX; Not excluded from mepaccure. FX covering physicals by members of fx family. Phys11_fxcovered stored pro-rata for split quantity divided by fx family quantity, Contracts not tolled, not written off or whole of fx family is written off, not 3rd party. FX not covering physicals or licences. This includes the excess amount on an FX deal above the amount used to cover physicals or licences
FX covering EU licences
Option Cargoes
Physicals for Option Cargoes
Process
Read from database
Save details to mepac01 table. Values in proportion to split quantity for Futures and FX.
Standard mcmktroe and mcmargin used for fx and futures mtm
Save allocated physical details for Purchase and Sale
Save unallocated mtm details for unallocated Purchases and Sales
Save EU Licence details
Save Option Cargo details
Reporting Data
Selections:
Summary, Detailed, Movement Report.
Filters for Department, Physicals, Licences, FX, F&O
Options to include/exclude Physicals, F&O, F&O Commission, FX, Licences
Include contracts with a Sale TTD only
Data retrieval:
Records retrieved from mepac01 table for selected database which match the selections.
Row to report is build up by type so that the physicals P&L is placed in the physicals column and the other P&L values are placed in their respective columns.
For the Detailed report the items which make up the P&L for the contract are listed vertically so that physicals are separated into Purchase and Sale values, Costs, F&O trades and FX deals with the P&L highlighted for each item.