OLE Many PO to One Invoice

Owned by Prem Kumar Balu (Unlicensed)
Last updated: Jul 13, 2015 by Nora Roggeveen-Sams (Unlicensed)

Overview

Link

https://jira.kuali.org/browse/OLE-2657 and related linked tasks

Description

Library vendors routinely send invoices billing for multiple Purchase Orders (POs). In many cases, the cost of additional charges (freight, shipping, etc) needs to be split among the accounts on these POs. Instead of manually creating one Payment Request (PREQ) for each PO referenced on the Invoice, users need a way to process the Invoice on one screen in order to avoid having to create individual PREQs and to distribute the cost of additional charges.

Design: Technical


 

Design Technical

The technical design document shall lay out the high level design of how the Many Purchase Orders to One Invoice. The details laid out shall include the Wire¬frames for User Interface design, Data Element Layout, including the logical Database Design, Component Specifications including artifacts such as Class Diagram, State Chart Diagrams, OJB.xml specifications and other relevant information.

Service Design and Implementation
Screen Flow Diagram

Layout use cases based on the screen flows indicating navigation between different screens. 

Security
  • Roles – list of roles to be defined
    • OLE-PURAP Accounts Payable Processor
    • OLE-PURAP Purchasing Processor
    • OLE-SYS Tax Manager
    • OLE-PURAP Accounts Payable Document Reviewer
    • KR-WKFLW Approve Request Recipient
    • OLE-SYS Accounts Payable Manager
    • OLE-PURAP Payment Request Hold / Cancel Initiator
    • OLE-SYS System User
    • OLE-SYS Manager
    • OLE-SELECT Accounting-AQ2
    • OLE-SELECT Accounting-AQ3
    • OLE-SELECT Accounting-AQ4
    • OLE-SELECT OLE-Receive-Mgr
    • OLE-SELECT Receiving-AQ3
    • OLE-SELECT OLE-Invoicing
    • OLE-SELECT Receiving-AQ4
    • OLE-SELECT OLE_Prepayment
    • OLE-SELECT OLE_RCV
    • OLE-SELECT OLE-Payment
    • OLE-SYS Fiscal Officer
    • KR-SYS Document Initiator
    • OLE-SYS Workflow Administrator
    • OLE-SYS Treasury Manager
    • OLE-SYS User
    • KR-WKFLW Initiator or Reviewer
    • KR-NS Document Editor
    • OLE-SYS Active Faculty or Staff
    • Recall Document (KR-WKFLW)     
    • Recall Document OLE (OLE-SYS)
    • OLE-SELECT OLE_User
    • KR-WKFLW Initiator
    • KR-WKFLW Non-Ad Hoc Approve Request Recipient
    • KR-NS Document Opener
    • OLE-PURAP Budget Reviewer
    • OLE-PURAP Sub-Account Reviewer
    • OLE-SYS Accounting Reviewer
    • OLE-SYS Tax Manager
    • OLE-SELECT OLE-Payment
    • OLE-SELECT OLE_Prepayment
    • OLE-SELECT OLE-Invoicing
  • Permissions – list of permissions to be defined
    • Add Note / Attachment PREQ Invoice Image (OLE-PURAP)
    • View Note / Attachment PREQ Invoice Image (OLE-PURAP)
    • Edit Document PREQ R (OLE-PURAP)
    • Use Transactional Document PREQ requestPaymentRequestCancel (OLE-PURAP)
    • Use Transactional Document PREQ paymentRequestHoldCancelRemoval (OLE-PURAP)
    • Use Transactional Document PREQ requestPaymentRequestHold (OLE-PURAP)
    • Edit Document PREQ P (OLE-PURAP)
    • Edit Document PREQ F (OLE-PURAP)
    • Edit Bank Code PREQ (OLE-PURAP)
    • Blanket Approve Document PREQ (OLE-PURAP)
    • Initiate Document PREQ (OLE-PURAP)
    • Edit ENROUTE Payment Request Document (OLE-SELECT)
    • Edit PROCESSED Payment Request Document (OLE-SELECT)
    • Edit FINAL Payment Request Document (OLE-SELECT)
    • Modify PREQ Account Accounting Lines (OLE-SELECT)
    • Cancel Payment Request (OLE-SELECT)
    • Edit Payment Request Document (OLE-SELECT)
    • Edit Document AP PreRoute (OLE-PURAP)
    • Modify Accounting Lines AP Account items.sourceAccountingLines (OLE-PURAP)
    • Use Transactional Document AP editPreExtract (OLE-PURAP)
    • Modify Accounting Lines AP PreRoute items.sourceAccountingLines (OLE-PURAP)
    • Use Transactional Document AP managerCancel (OLE-PURAP)
    • Use Transactional Document AP processorCancel (OLE-PURAP)
    • Open Document PRAP (OLE-PURAP)     
    • Error Correct Document KFST (OLE-SYS)
    • Administer Routing for Document KFST (OLE-SYS)
    • Edit Bank Code OpenLibraryEnvironmentTransactionalDocument (OLE-SYS)
    • Administer Routing for Document KFS (OLE-SYS)
    • Blanket Approve Document KFS (OLE-SYS)
    • Initiate Document KFS (OLE-SYS)
    • Open Document KFS (OLE-SYS)
    • save Document KFS (OLE-SYS)
    • Delete Note / Attachment OLE FALSE (OLE-SYS)
    • Ad Hoc Review Document KFS A (OLE-SYS)
    • Ad Hoc Review Document KFS F (OLE-SYS)
    • Ad Hoc Review Document KFS K (OLE-SYS)
    • Copy Document KFS (OLE-SYS)
    • Add Message to Route Log KFS (OLE-SYS)
    • Super User Approve Single Action Request OLE (OLE-SYS)     
    • Super User Approve Document OLE (OLE-SYS)
    • Super User Disapprove Document OLE (OLE-SYS)
    • Open Document (OLE-SELECT)
    • Initiate OLE Documents (OLE-SELECT)
    • Cancel Document (KUALI)
    • Route Document (KUALI)
    • Edit Kuali ENROUTE Document Node Name PreRoute (KUALI)
    • Edit Kuali ENROUTE Document Route Status Code R (KUALI)
    • Add Note / Attachment Kuali Document (KUALI)
    • View Note / Attachment Kuali Document (KUALI)
    • Send FYI Request Kuali Document (KR-SYS)
    • Send Acknowledge Request Kuali Document (KR-SYS)
    • Send Approve Request Kuali Document (KR-SYS)
    • Add Message to Route Log (KUALI)
    • Send Complete Request Kuali Document (KR-SYS)

 

Component Design
  • OBJ.xml
  • State Chart Diagram
Service Contracts
Service Implementation

Process Diagram

Work-flow Diagram

 UI Wire-frames Design

Invoice Process Screen

Document Overview Tab

Vendor Tab

Invoice Info Tab

Invoice Process Items Tab

Accounting lines

Account Summary Tab

Related Documents

Payment History

General Ledger Pending Entries

Data Model

User Story : Many Purchase Orders to One Invoice

Entity: OLE_AP_INV_T

NAME

LENGTH

DATE TYPE

DEFAULT VALUE

INPUT TYPE

ACTION EVENT

VALIDATION TYPE

TAB INDEX

REMARKS

INV_RQST_ID

9,0

DECIMAL

0

 

 

 

 

 

OBJ_ID

36

VARCHAR

 

 

 

 

 

 

INV_RQST_STAT_CD

4

VARCHAR

 

 

 

 

 

 

FDOC_NBR

14

VARCHAR

 

 

 

 

 

A unique, sequential, system-assigned number for a document.

AP_PUR_DOC_LNK_ID

10,0

DECIMAL

 

 

 

 

 

New link identifier generated for invoice is stored.

INV_PD_DT

 

DATETIME

 

 

 

 

 

Enter the invoice date from the vendor invoice or select the date from the calendar cal.

INV_DT

 

DATETIME

 

 

 

 

 

invoice date

INV_NBR

25

VARCHAR

 

 

 

 

 

The identifying invoice number from the vendor invoice.

VNDR_INV_AMT

19,2

DECIMAL

 

 

 

 

 

The net dollar amount of the invoice to be processed.  For OLE-FS this is where US dollars are entered

INV_RQST_PAY_DT

 

DATETIME

 

 

 

 

 

 

INV_RQST_CST_SRC_CD 

4

VARCHAR

 

 

 

 

 

 

VNDR_HDR_GNRTD_ID

10,0

DECIMAL

 

 

 

 

 

Vendor header generated ID.

VNDR_DTL_ASND_ID

10,0

DECIMAL

 

 

 

 

 

Vendor detail assigned ID.<BR>A vendor can have more than one transmission type; but only 1 should be preferred.  The preferred will appear as the default, but any are selectable.

VNDR_NM

45

VARCHAR

 

 

 

 

 

 

OLE_VNDR_CURR_ID

10,0

DECIMAL

 

 

 

 

 

 

OLE_VNDR_FOR_CURR_INV_AMT

19,2

DECIMAL

 

 

 

 

 

The net amount of the invoice to be processed in foreign currency.

OLE_INV_TYP_ID

10,0

DECIMAL

 

 

 

 

 

Primary Key of the Invoice Type maintenance table.

OLE_INV_SUB_TYP_ID

10,0

DECIMAL

 

 

 

 

 

Primary key of Invoice Subtype table.

OLE_INV_MTHD_ID

10,0

DECIMAL

 

 

 

 

 

 

INV_NTE_LN1_TXT

90

VARCHAR

 

 

 

 

 

 

INV_NTE_LN2_TXT

90

VARCHAR

 

 

 

 

 

 

INV_NTE_LN3_TXT

90

VARCHAR

 

 

 

 

 

 

 

INV_SPCL_HANDLG_INSTRC_LN1_TXT

90

VARCHAR

 

 

 

 

 

 

INV_SPCL_HANDLG_INSTRC_LN2_TXT

90

VARCHAR

 

 

 

 

 

 

INV_SPCL_HANDLG_INSTRC_LN3_TXT

90

VARCHAR

 

 

 

 

 

 

Entity: OLE_AP_INV_PO_ITM_T

NAME

LENGTH

DATE TYPE

DEFAULT VALUE

INPUT TYPE

ACTION EVENT

VALIDATION TYPE

TAB INDEX

REMARKS

INV_PO_ITM_ID

9,0

DECIMAL

0

 

 

 

 

 

INV_RQST_ID

9,0

DECIMAL

0

 

 

 

 

 

PO_ID

9,0

DECIMAL

 

 

 

 

 

 

PO_ENCUM_FSCL_YR

4,0

DECIMAL

 

 

 

 

 

To identify the purchase order number

AP_PUR_DOC_LNK_ID

10,0

DECIMAL

 

 

 

 

 

stores PO Link Identifier

CLOSE_PO_IND

1

VARCHAR

NULL

 

 

 

 

 

REOPEN_PO_IND

1

VARCHAR

NULL

 

 

 

 

 

PO_LN_NBR

3,0

DECIMAL

NULL

 

 

 

 

 

ITM_TYP_CD

4

VARCHAR

 

 

 

 

 

Item Type defines the different descriptive categories that can be applied to requisition, purchase order, payment request and credit memo line items. Some examples are: quantity, non-quantity, shipping , and freight. The purchase order item type carries to the line items of the payment request and credit memo.

ITM_LN_NBR

3,0

DECIMAL

 

 

 

 

 

 

ITM_DESC

4000

 VARCHAR

 

 

 

 

 

 

ITM_CATLG_NBR

30

VARCHAR

 

 

 

 

 

 

ITM_AUX_PART_ID

300

VARCHAR

 

 

 

 

 

 

ITM_UOM_CD

4

VARCHAR

 

 

 

 

 

 

ITM_INV_QTY

11,2

DECIMAL

 

 

 

 

 

 

ITM_UNIT_PRC

19,4

DECIMAL

 

 

 

 

 

 

PO_ITM_UNIT_PRC

19,4

DECIMAL

 

 

 

 

 

 

ITM_OSTND_INV_QTY

11,2

DECIMAL

 

 

 

 

 

 

ITM_OSTND_INV_AMT

19,2

DECIMAL

 

 

 

 

 

 

ITM_TAX_AMT

19,

DECIMAL

 

 

 

 

 

 

ITM_EXTND_PRC

19,2

DECIMAL

 

 

 

 

 

 

OLE_ITM_OSTND_INV_PRTS

11,2

DECIMAL

 

 

 

 

 

 

OLE_ITM_INV_PRTS

11,2

DECIMAL

 

 

 

 

 

 

OLE_PO_ITM_ID

10,0

 DECIMAL

 

 

 

 

 

 

OLE_LST_PRC

 

19,4

DECIMAL

 

 

 

 

 

 

OLE_DOCUMENT_UUID

50

VARCHAR

 

 

 

 

 

 

OLE_ADDL_CHRG_USD

1

VARCHAR

N

 

 

 

 

 

ITM_ASND_TO_TRADE_IN_IND

1

VARCHAR

 

 

 

 

 

Indicates if the Item is to be Assigned to Trade In

OLE_DISCNT

19,4

DECIMAL

 

 

 

 

 

 

OLE_DISCNT_TYP

1

VARCHAR

 

 

 

 
Entity: OLE_AP_INV_ACCT_T

NAME

LENGTH

DATE TYPE

DEFAULT VALUE

INPUT TYPE

ACTION EVENT

VALIDATION TYPE

TAB INDEX

REMARKS

INV_ACCT_ID

10,0

DECIMAL

 

 

 

 

 

 

INV_PO_ITM_ID

8,0

DECIMAL

 

 

 

 

 

 

FIN_COA_CD

2

VARCHAR

 

 

 

 

 

The chart code associated with the account assigned to the sub-account.

ACCOUNT_NBR

7

VARCHAR

 

 

 

 

 

The account number associated with the Sub-Account.  The Sub-Account Number is unique to this account.

SUB_ACCT_NBR

5

VARCHAR

 

 

 

 

 

An optional element of the accounting string that allows you to track financial activity within a particular account at a finer level of detail.

FIN_SUB_OBJ_CD

3

VARCHAR

 

 

 

 

 

SubObject Code used.

FIN_OBJECT_CD

4

VARCHAR

 

 

 

 

 

Object Code used.

ITM_ACCT_TOT_AMT

19,2

DECIMAL

 

 

 

 

 

Dollar amount for account.

DISENC_AMT`

19,2

DECIMAL

 

 

 

 

 

The amount the Payment Request Account will be disemcumbered according to the payment revision.

Entity: OLE_AP_INV_SUM_ACCT_T

NAME

LENGTH

DATE TYPE

DEFAULT VALUE

INPUT TYPE

ACTION EVENT

VALIDATION TYPE

TAB INDEX

REMARKS

SUM_ACCT_ID

10,0

DECIMAL

 

 

 

 

 

 

INV_RQST_ID

9,0

DECIMAL

 

 

 

 

 

 

FIN_COA_CD

2

VARCHAR

 

 

 

 

 

The chart code associated with the account assigned to the sub-account.

ACCOUNT_NBR

7

VARCHAR

 

 

 

 

 

The account number associated with the Sub-Account.  The Sub-Account Number is unique to this account.

SUB_ACCT_NBR

5

VARCHAR

 

 

 

 

 

An optional element of the accounting string that allows you to track financial activity within a particular account at a finer level of detail.

FIN_SUB_OBJ_CD

3

VARCHAR

 

 

 

 

 

SubObject Code used

FIN_OBJECT_CD

4

VARCHAR

 

 

 

 

 

Object Code used.

ITM_ACCT_TOT_AMT

19,2

DECIMAL

 

 

 

 

 

Dollar amount for account.

CRDT_MEMO_ID

9,0

DECIMAL

 

 

 

 

 

Credit memo number defined in KFS.
Entity: OLE_AP_INV_PO_ITM_USE_TAX_T

NAME

LENGTH

DATE TYPE

DEFAULT VALUE

INPUT TYPE

ACTION EVENT

VALIDATION TYPE

TAB INDEX

REMARKS

INV_PO_ITM_USE_TAX_ID

10,0

DECIMAL

 

 

 

 

 

 

INV_PO_ITM_ID

10,0

DECIMAL

 

 

 

 

 

 

FIN_COA_CD

2

VARCHAR

 

 

 

 

 

The chart code associated with the account assigned to the sub-account.

ACCOUNT_NBR

7

VARCHAR

 

 

 

 

 

The account number associated with the Sub-Account.  The Sub-Account Number is unique to this account.

FIN_OBJECT_CD

4

VARCHAR

 

 

 

 

 

Object Code used.

RATE_CD

10

VARCHAR

 

 

 

 

 

 

TAX_AMT

19,4

DECIMAL

 

 

 

 

 

 

Questions

1) How to handle the credit/debit data, in Invoice Document. As per new requirement user can give credit/debit for the invoice amount in Invoice. When debit is given or no data is given, logic will be handled

as in Payment Request now. If the user gives credit.. what needs to be done.

 Answer : Not sure of functional requirements. May be need to generate credit memo's for credit Transaction and Payment Requests for Debit Transaction.

 

2) Now SFC is to be done in Invoice and not in Payment Request. As we are not going to save gl entries in Invoice Screen, gl entries are saved only in Payment Request. We may end up in wrong SFC calculation as

expense amount will not reflect correct amount. How to handle the gl entries?

 Answer : Generate temporary gl entries and do not save these entries and check SFC based on these values. Create one sample role like OLE-SYS-USER with one user and forward the document to that user.

3) How to handle document linkage identifier,...where we need to have the linkage identifier. What identifier needs to be stored in Invoice and Payment Request.

Answer : Store AP_PUR_DOC_LNK_ID generated for PO document in OLE_AP_INV_PO_ITM_T table. Generate new AP_PUR_DOC_LNK_ID and save it in OLE_AP_INV_T table.

Store AP_PUR_DOC_LNK_ID generated for invoice in AP_PMT_RQST_T table, if Payment Request is created through Invoice Screen. If Payment request is created through Payment Request  Screen, then store AP_PUR_DOC_LNK_ID generated for PO document.

4) Payment Terms Calculation...How to handle the Payment Terms Calculation in Invoice and Payment Request Screen.

Answer : OLE currently does not take into account the Payment Terms discount amount at the document level. The info is captured in PDP extract process, when the actual cheque is cut-off.

5) Payment Info.. From where and how the information is fetched?

Answer : The info is fetched after PDP Process is run. Not required in Invoice as PDP Process is done only in Payment Request Screen.

Date : 13-July-2013

1) Accepting negative value in invoice document should result in creating vendor credit memo or the Invoice / Payment Request documents should allow negative values ?

If so how accounting data should be posted concern accounts?

2) Requirement is Extract date should be added to PDP_PROC_T : Process Date (PROC_TS column) is available in PDP_PROC_T table. Is that a extract date or do we need to create new field as extract date in that table

3) The scope for this 1.0 is only the insertion of data into PDP tables or it includes extraction of scripts and services also?  

4) How the invoice / Payment Request Documents should process the prorating ?

 

Date: 29-July-2013

1) If one item in PO line item is negative and another item in the same PO is in positive value, do we need to create Vendor credit memo for negative value and Payment Request for positive value for the same PO?

2) Pay through Receiving Queue Search : [Pay], [Receive and Pay] should create invoice for the selected title automatically or should show invoice document to the user to create invoice manually ?

 

VARCHAR

PDP_PROC_T

Operated as a Community Resource by the Open Library Foundation