Information Systems:CCP - Miscellaneous Billing

In the 'contract' part of CCP (Customer/Contract Pricing), we can enter rebates from the vendor. COOP, which are co-op fees as the contract level, or VEDI, which are vendor debits at the item level.

Bills to the vendor or rep are generated automatically, based on the entered parameters.

Co-op Fees

These are entered at the contract level.

 2/09/16  12:12:49    uniPHARM -- PRODUCTION               CXRIU410/UPDATE     
UPDATE           UX - Enter/Update Contract Level Billing  PRODUCTION  1.00    
                                                                               
Contract #                                                                     
BNA007BLIS BLISTEX JULY MEMO                                                   
                                                                               
     Seq # Class  Timing                                     Billing Date      
        10 COOP   LAST SALE  Last day of the sale                00 00     
   
           For   Cust/Vend/Broker                                              
           BROK  10102       BLISTEX/ZARINA CONFECTIONER                
        
           Method  Bill Basis       $/Funds           %        $ per Unit      
           APDED   FIXEDAMT          250.00  CAD        .000          .000    
  
           Cost Centre      Project                          Acct Bypass       
           IFP              007                                   N            
                                                                                
           Added by User/Date     Last Updated User/Dte  Posted Date           
           SHELLYL     JUN 12 14  SHELLYL     JUN 12 14  JUL 31 14

Timing determines when the bill is generated

FIRST SALE    First day of the sale        uses sales start date on the contract header   
LAST SALE     Last day of the sale         uses sales end date on the contract header
PO RECEIPT    Receipt of the first PO      looks for PO's with the contract number in the shipment mark
SPEC DATE     On billing date specified    uses the billing date on this record

Bill Basis determines how to calculate the amount of the fee

AMTPURCH      % of Total amount purchased  unbilled PO receipts with this contract # in shipment mark
AMTSOLD       % of Total amount sold       sales for all items on this contract, since last bill date
FIXEDAMT      Fixed amount                 amount from this record
UNITPURCH     $ per unit purchased         unbilled PO receipts with this contract # in shipment mark
UNITSOLD      $ per unit sold              sales for all items on this contract, since last bill date

Notice the difference in the way purchase orders and sales orders are described, even though they both look for the contract or promo number in the shipment mark. The shipment mark on a sales order will always be populated when the store gets the special prices, but it is not unusual for us to get the special price on purchase orders ordered outside the order window. The buyer can then go in InfoNet and update the shipment mark. Which means these programs have to look at more than just the last billed date. File CXPORECP contains a record of every PO line that has had a bill generated. If a PO line has the correct shipment mark, but is not in this file, it will be billed, then added.

The three fields that follow - $, %, and $ per unit - give the rates used to calculate the billing amount.

$              used when the billing basis is fixed amount
%              used when the billing basis is amount purchased or amount sold
$ per unit     used when the billing basis is units purchased or units sold

Method determines how the vendor or rep is to be billed

APCRD         A/P credit                   will apply a credit against the vendor's A/P account
APCS          A/P car stock                will get equivalent value in car stock
APDED         A/P Deduction                will be deducted from the invoice for goods received
ARCHQ         A/R Cheque   
REPRT         Report only  

Cost Centre and Project

These are used in conjunction with the entry in the ‘Misc Billing Item and G/L’ (UX BILLITEM) table to build the account to which the billing will be posted. The cost centre will normally be obtained from the first three characters of the contract code, and the project from the next three.

Vendor Debit

These are entered at the item level.

 2/09/16  12:32:54    uniPHARM --Volume test               CXRIU330/UPDATE     
UPDATE           UX - Enter Contract Item Further Details  VOLUME      1.00    
                                                                               
Contract #                                 Vendor #     Broker #               
SH-DL-0811 ZERO BARS                       22547        22547                  
                                                                               
Item                                                                           
R 00874529        ZERO BARS GOLD 50GM DARK                                     
                                                                               
Seq # Type  For    Warehouse  Timing         $ or %          UOM   Margin %    
  22  VEDI  VEND   *ANY       PO REC            1.930 CAD   BX                 
                                                                               
              Applies to  Acct Effect  EDL Zone    Price List  Rebates?        
              NET VEND    APDED        *ANY        *ANY           N            
                                                                               
      Bill on Date  Send Bill to  Billing Line Description       Bill Backs?   
          00 00     10843                                            N         
                                                                               
                    Added by User/Date     Last Upd by User/Date Posted Date   
                    NORMAM      AUG 10 15  NORMAM      AUG 10 15   AUG 16 15

Timing determines when the bill is generated

MONTHLY       At end of each month        on the first of the month    
PO REC        On receipt of PO            receipt of this item with contract # in shipment mark, since last bill date
SALE          At time of sale             sale for this item with contract # in shipment mark, since last bill date
SLS END       At end of sales period      after sales end date on the contract header
SPEC DT       On billing date specified   bill on date from this record

Timing also determines how the amount is calculated

MONTHLY       At end of each month        unbilled receipts of this item with contract # in shipment mark    
PO REC        On receipt of PO            unbilled receipts of this item with contract # in shipment mark 
SALE          At time of sale             sales for this item with contract # in shipment mark, since last bill date
SLS END       At end of sales period      sales for this item with contract # in shipment mark
SPEC DT       On billing date specified   receipts of this item with contract # in shipment mark

Notice the difference in the way purchase orders and sales orders are described, even though they both look for the contract or promo number in the shipment mark. The shipment mark on a sales order will always be populated when the store gets the special prices, but it is not unusual for us to get the special price on purchase orders ordered outside the order window. The buyer can then go in InfoNet and update the shipment mark. Which means these programs have to look at more than just the last billed date. File CXPORECP contains a record of every PO line that has had a bill generated. If a PO line has the correct shipment mark, but is not in this file, it will be billed, then added.

Applies to determines in which price calculation this is used

Note - be very careful in the use of this field.

MEMO          Memo                not used in any price calculation   
NET SALE      Net Sales Price     affects our selling price
NET VEND      Net Vendor Price    affects our purchase price
PRICE TICK    Price Ticket        affects suggested retail price

Accounting Effect

*NONE         No Accounting Effect 
APCRD         A/P Credit                 transaction will be transferred from A/R to A/P          
APDED         A/P Deduction              transaction will be transferred from A/R to A/P       
ARCHQ         A/R Cheque           
PO PRICE      Purchase Order Price 
REDUCECOST    Reduce cost and print report
REPORT        Report on  

Send Bill to

This is automatically filled in, but can be changed if necessary. If the ‘For’ (for use) field is BROK, the broker number from the contract will be used. If it is VEND, the vendor number will be. If these fields are blank, the main supplier from the item master file will be used.

$ or % – How the Debit will be Calculated

$     debit will be the dollar amount times the quantity received/sold, adjusted to the correct units of measure
%     debit will be a percentage of the received/sold amount

For Use – who will get the invoice

VEND     the vendor will be billed
BROK     the broker will be billed

Programs

CXBILL01 - Contract Header Level Billing (COOP Fees)

Using the values entered in 'Contract Level Billing', a bill is calculated and added to file CXBITMP (CCP - miscellaneous billing item work file).

This will run every night as part of the end of day processing. It will read the file CXBILLP (CONTRACTS – Miscellaneous Billings Master) and search for records that have not yet been billed, and are not flagged to bypass accounting. The ‘timing’ field is used to determine whether or not this record should be billed. ‘Spec Date’ will use the ‘billing date’ from the billing record; ‘First Sale’ and ‘Last Sale’ will use the sales dates from the contract header; ‘PO Receipt’ will bill as soon as an item with this contract number as a shipment mark is received. (Note- as this will run after midnight, the dates to be billed are compared to yesterday’s date.)

The ‘bill basis’ field, and the three billing fields, are used to calculate the amount. ‘Amtpurch’ and ‘Unitpurch’ will read the purchase order file for received items with this contract number as a shipment mark. (Only purchase order types that update onhand stock will be included.) ‘Amtsold’ and ‘Unitsold’ will read the sales order file for invoiced items with this contract number as a shipment mark. (Only sales order types that affect demand will be included.)

CXBILL02 - Contract Detail Level Billing (VEDI)

Using the values entered in 'Contract Item Further Details', a bill is calculated and added to file CXBITMP (CCP - miscellaneous billing item work file).

This will run every night as part of the end of day processing. It will read the file CXITPRP (Contract Item Prices) and search for records that have not yet been billed, and that have an amount or percent (pricing records are added according to a template, whether or not they are going to be used). The ‘timing’ field is used to determine whether or not this record should be billed. ‘Spec Date’ will use the ‘billing date’ from the item details record; ‘Sls End’ will use the sales dates from the contract header; ‘PO Rec’ will bill as soon as this item with this contract number as a shipment mark is received. (Note- as this will run after midnight, the dates to be billed are compared to yesterday’s date.)

Depending on the ‘timing’ field, this item with this contract as shipment mark will be read from either the purchase order file for received items, or the sales order file for invoiced items. (Only purchase order types that update on hand stock, and sales order types that affect demand will be included.) All quantities are converted to the same unit of measure as the billing record.

The ‘timing’ field, and the '$ or %' field, are used to calculate the amount. ‘MONTHLY’ and ‘PO Rec’ will read the purchase order file for this item with this contract number as a shipment mark. (Only purchase order types that update onhand stock will be included.) ‘SALE’ and ‘SLS END’ will read the sales order file for invoiced items with this contract number as a shipment mark. (Only sales order types that affect demand will be included.)

CXBILL03 - Generate Bill

Note that a bill is only created if the total is greater than $10.

File CXBITMP (CCP - miscellaneous billing item work file) is read. One bill is created for each contract number / bill type (COOP or VEDI) / bill to vendor / charge to vendor / accounting effect. Each PO number and item number will build a separate line. (The 'bill to' vendor gets the A/R charge, and the 'charge to' vendor gets the A/P one.)

We have to show the item on the sales order (in the comments) for the vendor's information; but we must bill to a fictitious item. Table UX BILLITEM indicates the number to use.

Records are added to IOP files IOPHDRP (IOP - Order headers to be posted to ASW), IOPLINP (IOP - Order lines to be posted to ASW), IOPEXTP (IOP - Order line extension file).

The ‘method’ field indicates how the bill will be paid. All miscellaneous billing will start as an A/R invoice to the person or company in the ‘for’ and ‘cust/vend/broker’ fields. If method is ‘ARCHQ’, the bill will be left in A/R. For the other methods, ‘APCRD’, ‘APCS’, and ‘APDED’, the transaction will be automatically transferred from A/R to A/P by adding a reversal to file SROKBA (A/R batch input file, invoices) and adding to file SROLBA (A/P Batch input file, invoices) to the account of this contract’s vendor. For both, G/L transactions are added to file SROIBT (Interface accounting trans.from pre-system) to transfer amount from 120000 to 210000.

The 'type' of the generated transaction depends on the 'Method' or 'Accounting Effect'

ACR    APCRD    A/P Credit
ADU    APDED    A/P Deduction

This billing record in CXBITMP (CCP - miscellaneous billing item work file) will be set to billed. There can be two values; ‘Y’ meaning that it has been billed (no more changes can be made to this record), or ‘P’ meaning that it is partially billed. This is applicable when the timing is ‘PO RECEIPT’ and billing basis in anything other than ‘FIXEDAMT’. This would be used to bill either ‘AMTPURCH’ or UNITPURCH’ every time a purchase order is received.

GCFC851 (Interface accounting transfer) is then called to post to the ASW transaction files.

CXBILL06 - Update Transaction Numbers in A/P and A/R to Match Invoice

When CXBILL03 created the transfer from A/R to A/P, the original bill had not yet been invoiced. So the sales order number was used instead on invoice number. That makes it difficult to match all the transactions together. This program, which is run after the invoice has been generated, uses the sales order number to read the invoice header, and get the invoice number. When it updates this, it places an 'X' in the very right of the text field to indicate this has been done.

CXBILL07 - AutoSettle A/R Invoice with Generated Transfer

Reads unsettles ACR and ADU transactions from file SRODTA (A/R Transaction file). If an unsettled record with document type INV, the same document number, and the same but reversed amount is found, settle them against each other. Records are added to SROVPH (A/R Settlement path heading), SROVPX (A/R Settlement path index); and records in SRODTA (A/R Transaction file) are marked as settled.

Process

These programs cannot just run one after the other. CXBILL07 cannot run until after DIS to FIN has posted transactions for the day.

EODUPD   CXBILL03
         CXBILL06
        DIS to FIN
         CXBILL01
         CXBILL02
         CXBILL07

Based on the above information, a sales order (order type MC - Miscellaneous Billing Credit, or MI - Miscellaneous Billing Invoice) is created.

and added into IOP.  IOP then moves it into the EDI files, and posts to ASW.  Because both these order types are set up with direct invoicing, the invoices will automatically generated right away.  The A/R transaction that is generated will be posted during the overn

Generate Billing

Every night, CXBILL03C, CXBILL06, CXBILL, and CXBILL07 are called from EODUPD.