AUTOLOCKBOX INTERFACE [ AR]

Setting up Bank With Any name and Branch Name

Table: AP_BANK_BRANCHES

select * from AP_BANK_BRANCHES where bank_name = 'Aman Bank';

COLUMN NAMES NEEDED IN THE ABOVE FORM:======================================BANK_BRANCH_IDBANK_NAME BANK_BRACH_NAMEADDRESS_LINE1CITYSTATEZIPCOUNTRYBANK_NUM [ BANK BRANCH NUMBER ]INSTITUTION_TYPEBANK_NUMBERBANK_BRANCH_TYPE

This is where we define our bank account and respective at least 5 code combinations

AP_BANK_ACCOUNTS

SELECT * FROM AP_BANK_ACCOUNTS_ALL WHERE CREATED_BY = '1005254' ANDBANK_BRANCH_ID = '12127'

BANK_ACCOUNT_IDBANK_ACCOUNT_NAMEBANK_ACCOUNT_NUM

BANK_BRANCH_ID = AP_BANK_BRANCHES.BANK_BRANCH_IDSET_OF_BOOKS_ID5 RESPECTIVE CODE COMBINATION ID'sORG_IDMAX_OUTLAYACCOUNT_TYPE

AP_BANK_ACCOUNTS

This is where we define our receipt class

AR_RECEIPT_METHODS

SELECT * FROM AR_RECEIPT_METHODS WHERE CREATED_BY = '1005254'

RECEIPT_METHOD_IDNAMERECEIPT_CLASS_IDSTART_DATEPRINTED_NAME

Assign Bank Accounts to Payment Method

AR_RECEIPT_METHOD_ACCOUNTS

select * from AR_RECEIPT_METHOD_ACCOUNTS_ALL WHERE CREATED_BY = '1005254'

BANK_ACCOUNT_ID = AP_BANK_ACCOUTNS_ALL.BANK_ACCOUNT_IDRECIEPT_METHOD_ID = AR_RECEIPT_METHODS.RECEIPT_METHOD_IDALL CODE COMBINATION ID'sORG_ID

DEFINE OUR RECIEPT SOURCE

AR_BATCH_SOURCES

SELECT * FROM AR_BATCH_SOURCES_ALL WHERE CREATED_BY = '1005254'

BATCH_SOURCE_IDNAMETYPESTART_DATE_ACTIVEEND_DATE_ACTIVE [ IF WE USE IT ]DEFAULT_REMIT_BANK_ACCOUNT_IDAUTO_BATCH_NUMBERINGLAST_BATCH_NUMDEFAULT_RECEIPT_CLASS_ID = AR_RECEIPT_METHODS.RECEIPT_CLASS_IDDEFAULT_RECEIPT_METHOD_ID = AR_RECEIPT_METHOD_ACCOUNTS_ALL.RECEIPT_METHOD_IDORG_ID

Define Lockbox

AR_LOCKBOXES

AR_LOCKBOXES

select * from AR_LOCKBOXES_all WHERE CREATED_BY = '1005254'

LOCKBOX_IDLOCKBOX_NUMSTATUSBATCH_SOURCE_ID = AR_BATCH_SOURCES_ALL.BATCH_SOURCE_IDBANK_ORIGINATION_NUMBER --- This is used in the control .ctl file BATCH_SIZEGL_DATE_SOURCERECEIPT_METHOD_ID = AR_RECEIPT_METHOD_ACCOUNTS_ALL.RECEIPT_METHOD_IDREQUIRE_BILLING_LOCATION_FLAGORG_IDLOCKBOX_MATCHING_OPTION

Define the transmission formats

AR_TRANS_RECORD_FORMATS

select * from AR_TRANS_RECORD_FORMATS WHERE CREATED_BY = '1005254'

RECORD_FORMAT_ID [ GENERATED BY THE SYSTEM ]TRANSMISSION_FORMAT_ID [ GENERATED BY THE SYSTEM ] -- ONE TO MANY RELATION BETWEEN RECORD_FORMAT_ID AND TRANSMISSION_FORMAT_IDRECORD_IDENTIFIERRECORD_TYPE_LOOKUP_CODEDECSRIPTION

Define transmission fields

AR_TRANS_FIELD_FORMATS

AR_TRANS_FIELD_FORMATS

AR_TRANS_FIELD_FORMATS

select * from AR_TRANS_FIELD_FORMATS WHERE CREATED_BY = '1005254'

FIELD_FORMAT_IDRECORD_FORMAT_ID = AR_TRANS_RECORD_FORMATS.RECORD_FORMAT_IDTRANSMISSION_FORMAT_ID = AR_TRANS_RECORD_FORMATS.TRANSMISSION_FORMAT_IDFIELD_START_POSITIONFIELD_END_POSITIONFIELD_TYPE_LOOKUP_CODEDATE_PICTURE_LOOKUP_CODEJUSTIFICATION_LOOKUP_CODEAMOUNT_FORMAT_LOOKUP_CODEFILL_CHARACTER_LOOKUP_CODE

LockBox Interface:

AR Lockbox Interface table

select * from ar_payments_interface_all WHERE CREATED_BY = '1005254'

SYSTEM CREATED COLUMNS --- REFER TO USER GUIDE PAGE G - 123 RECORD_TYPESTATUSORIGINATION = AR_LOCKBOXES_all.BANK_ORIGINATION_NUMBERTRANSMISSION_RECORD_COUNTTRANSMISSION_AMOUNT --- THESE TWO ARE USED , BUT WE DIDNT USE IT FOR OUR CONTROL FILELOCKBOX_NUMBER = AR_LOCKBOXES_all.LOCKBOX_NUMBATCH_AMOUNTBATCH_RECORD_COUNT ITEM_NUMBERREMITTANCE_AMOUNTCHECK_NUMBERCUSTOMER_NUMBERINVOICE1GL_DATECUSTOMER_ID --- THESE ALL FIELDS CAME FROM THE CONTROL FILERECEIPT_METHOD_ID = AR_RECEIPT_METHOD_ACCOUNTS_ALL.RECEIPT_METHOD_IDRECEIPT_DATEORG_ID

After validation step, Lockbox transfers receipt data into AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL

After PostQuickCash, the following Base Tables should be populated:===============================AR_CASH_RECEIPTS_ALL

select * from AR_CASH_RECEIPTS_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

CASH_RECEIPT_IDAMOUNTSET_OF_BOOKS_IDPAY_FROM _CUSTOMER [ CUSTOMER_NUMBER ]STATUSTYPERECEIPT_NUMBER [ FROM EXTERNAL .DAT FILE ]RECEIPT_DATE [ FROM EXTERNAL .DAT FILE ]REMITTANCE_BANK_ACCOUNT_ID = AP_BANK_ACCOUNTS_ALL.BANK_ACCOUNT_IDRECEIPT_METHOD_ID = AR_RECEIPT_METHOD_ACCOUNTS_ALL.RECEIPT_METHOD_IDANTICIPATED_CLEARING_DATE [ FROM EXTERNAL .DAT FILE]ORG_ID

AR_CASH_RECEIPT_HISTORY_ALL

SELECT * FROM AR_CASH_RECEIPT_HISTORY_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE-2;

CASH_RECEIPT_HISTORY_IDCASH_RECEIPT_ID = AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_IDSTATUSTRX_DATEAMOUNTGL_DATEBATCH_IDACCOUNT_CODE_COMBINATION_IDORG_ID

AR_RECEIVABLE_APPLICATIONS_ALL

select * from AR_RECEIVABLE_APPLICATIONS_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 2;

RECEIVABLE_APPLICATION_IDAMOUNT_APPLIED

GL_DATECODE_COMBINATION_ID --- 3 CODE COMBINATION IDS GENERATED WITH 1 APPROVED AND 2 WITH UNAPPR . WE CAN POST THESE 2 UNAPPR TO G.L SO THAT IT GETS APPROVEDSET_OF_BOOKS_IDDISPLAYSTATUSPAYMENT_SCHEDULE_ID = AR.PAYMENTS_SCHEDULES.PAYMENT_SCHEDULE_IDCASH_RECEIPT_ID = AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID APPLIED_CUSTOMER_TRX_ID = RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_IDAPPLIED_PAYMENT_SCHEDULE_ID = AR.PAYMENTS_SCHEDULES.PAYMENT_SCHEDULE_IDCASH_RECEIPT_HISTORY_ID = AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_HISTORY_ID

INTERIM TABLE DESC:-

select * from AR_INTERIM_CASH_RECEIPTS_ALL --WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 2;

CASH_RECEIPT_ID = AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_IDAMOUNTCURRENCY_CODEBATCH_ID [ THIS VALUE IS STORED IN RA_BATCHES_ALL ]PAY_FROM_CUSTOMERSTATUSTYPERECEIPT_NUMBER RECEIPT_DATEGL_DATESPECIAL_TYPERECEIPT_METHOD_IDREMITTANCE_BANK_ACCOUNT_IDSITE_USE_IDANTICIPATED_CLEARING_DATEORG_IDAMOUNT_APPLIEDINVOICE_CURRENCY_CODE

select * from AR_INTERIM_CASH_RCPT_LINES_ALL --WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 2;

CASH_RECEIPT_IDCASH_RECIPT_LINE_IDPAYMENT_AMOUNTPAYMENT_SCHEDULE_IDCUSTOMER_TRX_IDBATCH_IDSOLD_TO_CUSTOMERDUE_DATEORG_IDINVOICE_CURRENCY_CODE.

INTERFACE TABLE:-->===================select * from AR_PAYMENTS_INTERFACE_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

DELETE from AR_PAYMENTS_INTERFACE_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

INTERIM TABLES:-->===================select * from AR_INTERIM_CASH_RECEIPTS_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

DELETE from AR_INTERIM_CASH_RECEIPTS_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

select * from AR_INTERIM_CASH_RCPT_LINES_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

DELETE from AR_INTERIM_CASH_RCPT_LINES_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

BASE TABLES:-->================select * from AR_CASH_RECEIPTS_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

DELETE from AR_CASH_RECEIPTS_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

select * from AR_RECEIVABLE_APPLICATIONS_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

DELETE FROM AR_RECEIVABLE_APPLICATIONS_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

select * from AR_CASH_RECEIPT_HISTORY_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;

DELETE from AR_CASH_RECEIPT_HISTORY_ALL WHERE CREATED_BY = '1005254' AND CREATION_DATE > SYSDATE - 1;