Manual Reconciliation Bulk Import

Manual Reconciliation Bulk Import

The private utility BULKADJS is used to make multiple cash and position reconciliation adjustments from a single client-provided input file containing the adjustments. The adjustments then flow to APL’s EDGL and/ or EDPORT databases, as well as to Caliper and APL’s Recon tool to ensure all databases remain in sync.

The input file consists of 11 fields:

• TranDate: Transaction Date in MM/YY/CCYY format. For cash (EDGL) transactions, this date corresponds to the date field in EDGL. For security (EDPORT) transactions this date corresponds to either the purchase date, sale date, receive date or deliver date

• LPLAccountNo: Account Number. This corresponds to the DTCNO1/2 fields in EDAC.

• SleeveTag: If empty, “BLANK” should be used

• KPNUM: Beta Security Number. Use this field to find the APL sacus number except for annuities. A zero in this field will be assumed to map to the sacus 13321 on APL

• CUSIP: For annuities, this field is used to find the APL sacus number. A security is an annuity if the KPNUM on the input file maps to a security on APL that has an ISSTY equal to 38

• Amount: Amount of transaction. For cash (EDGL) transactions, this amount corresponds to the amount field in EDGL. For security (EDPORT) transactions this amount corresponds to either the cost, proceeds, receive value or deliver value. For security journals (both receive and deliver) postings, this field will be empty. APL will calculate the value of the security journal by taking APL’s price and multiplying it by the share quantity in the Shares field. For security journal deletes, the value of the journal will be provided on the input file

• Shares Shares being transacted. For cash (EDGL) transactions this field will typically be 0 but can be a non-zero value

• TransactionCode: For cash (EDGL) transactions, this field will correspond to the transaction type in EDGL. For position (EDPORT) transactions, this field will either be SECOPE, SECCLO, SECRCV or SECDLV for buys, sells, receives and delivers, respectively

• TransactionCond1: For cash (EDGL) transactions, this field will be ignored. For position (EDPORT) transactions, this field will correspond to either the BRKIN or BRKOUT fields. If this field is empty then “BLANK” should be used

• Type: Will be set to either “Posting” or “Cancel”. If “Posting,” then this record is posted to APL and Caliper. If “Cancel,” then this record is removed from APL and Caliper

• ID: Transaction identifier. Some transactions will be linked by this field. For example, a “Cancel” and a “Posting” transaction may have the same ID. When this happens, if one of the transactions for a given ID cannot be processed then none of the transactions for the given ID will be processed. In other words, if the cancel cannot be reversed then the corresponding posting transaction should not be processed

Note: The input file can be named whatever you like, however the filename must be in upper case.

Sample prompt for the input file:

Sample input file:

Once all transactions have been processed, a csv output file will be produced. The output file name will be “pati70_<APL user id>_<APL process number>_<HHMMSS>.csv”. The file will contain the same number of rows that existed in the input file. A single header record will appear at the top of the file followed by detail records that will be similar to the detail records on the input file. Any additional transactions that need to be reversed and reposted to accommodate a record on the input file will not appear on this output file. That is, if you needed to reverse and repost a sale against a modified purchase on the file then only the modified purchase will appear on this file and not the sale.

The file contains the following columns:

• Account: Account Number from DTCNO1/2. If the input file contained an unknown account number then this column will be blank
• Transaction Code: Field from the input file
• Transaction Qualifier: TransactionCond1 field from the input file
• Shares: Field from the input file
• Sacus: The sacus of the security that BULKADJS will be matching on given the KPNUM, Cusip and TransactionCode on the input file
• Transaction Date: Field from the input file
• Amount: Field from the input file. For security journal post records, this field will be zero to mimic the input file even though the BULKADJS utility will calculate an appropriate value for this flow
• Sleeve: Field from the input file.
• Type: Field from the input file.
• ID: Field from the input file.
• Status: Contains either “Success” or “Fail” indicating whether the transaction processed successfully or not
• Reason: Populated if the Status field is “Fail.” Valid entries are:
  • Account Not Found
  • Security Not Found
  • Cancel Transaction Not Processed
  • Corresponding Transaction Not Processed
  • Transaction Date Not a Trading Day
  • Missing Price
  • Not Enough Shares to Close

Sample output file: