Accounts Receiveable API {f45pkg.html}

Title
Create Accounts Receivable Documents.

Summary
The application programming interface (API) uses a composite object as input and either inserts a complete Integrator Accounts Receivable (AR) documents or returns validations errors resulting from the input. The composite input object is a three level structure consisting of a document, document items, general ledger allocations and repeating documents. The relationship of document, document items and general ledger allocations is a one to many to many. For the input received the API performs all the validations required to create an Integrator AR document and if any validation fails the API returns the validations errors encountered during processing. All though the API caters for the creation of documents in other Integrator financial sub systems, the AR document which create an entry in another subsystem, the functionality has not been fully developed. A minimum input data set is required which can be extended. Should the minimum input be received the API will apply default rules to populate the fields not included but available as an input record. The API uses the function in the package f42pkg.sql to validate and maintain AR records.

URL
$ITS_PATH/finc.f45pkg.createARDocument(v_doc,v_return_tab,x_trial);

Method
Not Applicable

Parameters

Parameter	In / Out	Type	Default	Remark
x_doco	IN OUT	Record finc.v_AR_Doco		Record passed to API
x_ret_tab	OUT	Record gen.g0apkg.g0aReturn_tab		Error records returned from API
x_trial	IN	A2	T	Trail or Update - AR docuemnts are only inserted in Update Mode
x_subs	IN	A2	AR	Document System of Origin i.e CT, CB
x_tabl	IN	A2	FRK	Document Table of Origin

Document Records (v_AR_Doco)
Field	Object	Values
Debtor Code	FRKDTTR	Input
Second Reference	FRKREFN2	Input
Document Type	FRKTYPE	Input
Note	FRKNOTE	Input
Account Type	FRKACCTYP	Input
Contact Sequence Number	FRKCSN	0
Repeat Indicator	FRKREPT	N
Process Date	FRKIDTE	System Date
TransactionValue	FRKTVAL	Calculated
Tax Value	FRKGTOT	Calculated
Vat Value	FRKVAT	Calculated
Vat Code	FRKVCODE	Calculated
Value in Foreign/GivenCurrency	FRKFORVAL	Calculated
Vat Rate	FRKVRATE	Calulcated
Invoice Number	FRKINV	Derive
Invoice Date	FRKREFD	Derive
Financial Year	FRKFINY	Derive
Financial Cycle	FRKFINC	Derive
Printed Indicator	FRKPRT	Derive
CurrencyCode	FRKTMONEY	Derive
ExchangeRate	FRKEXCHANGE	Derive
AddressType	FRKADDRTYPE	Derive
Telephone NumberType Code	FRKTNTC	Derive
Telephone NumberType Code (Alt1)	FRKTNTC1	Derive
TransactionGroup Code	FRKTGC	Derive
Department Code	FRKDEPT	Derive or Null
Our ContactPerson	FRKOCNT	Derive or Null
Telephone Number	FRKTELE	Derive or Null
Building Name	FRKBLDN	Derive or Null
Floor	FRKFLR	Derive or Null
Room Number	FRKRMN	Derive or Null
LoanCode	FRKLCODE	Derive or Null
Audit Date	FRKAUDIT	Null
ShowY/N	FRKSHOW	Null
JobNumber	FRKJOBNUM	Null
Print User	FRKPRINTUSER	Null
Print Date	FRKPRINTDATE	Null
Statement Number	FRKSTATNUM	Null
DateCopied	FRKCOPYPD	Null
Originating AR Document Number	FRKORIGINV	Null
Email Address	FRKEMAIL	Null
No ShowDate	FRKNOSHOWD	Null
No ShowSequence	FRKNOSHOWSEQ	Null

Repeating Document Records (v_AR_Doco)
Field	Object	Values
First Cycle	FRLFIRST	Input
Repeat Times	FRLRTIME	Input
Invoice Number	FRLINV	Input
Debtor Code	FRLDTTR
Interval (Mths)	FRLRINV	Derive
Printed Indicator	FRLPRT	Null

For each Document Record (v_AR_Doco)
Field	Object	Values
Invoice Number	FRPINV	v_AR_Doco
Debtor Code	FRPDTTR	v_AR_Doco
Document Type	FRPTYPE	v_AR_Doco
Line Number	FRPLNUM	Input
First/Additional Line	FRPHEAD	Input
Quantity	FRPQUAN	Input
Unit	FRPUNIT	Input
Description	FRPDESC	Input
Unit Price	FRPUTOT	Input
GST / Vat Rate	FRPVRATE	Input
GST / Vat Inclusive/Exclusive	FRPINCEXC	E
Student Number	FRPSTNO	Input
Vat Value	FRPVAT	Calculated
Vat Code	FRPVCODE	Null

For each Item Record (x_doco.item)
Field	Object	Values
Debtor Code	FRDDTTR	x_doco.item
DocumentType Type	FRDTYPE	x_doco.item
InvoiceNumber	FRDINV	x_doco.item
Line Number	FRDLNUM	Input
Transaction Event	FCLEVNT	Input
TransactionType	FRDTRN	Input
Transaction Value	FRDTVAL	Input
Debit Cost Centre	FRDGLDCC	Input
Debit Account	FRDGLDACC	Input
Credit Coset Centre	FRDGLCCC	Input
Credit Account	FRDGLCACC	Input
Financial Year	FRDFYD	Derive
Financial Cycle	FRDFMD	Derive
General Ledger Note	FRDGLNTE	Derive
Original User	FRDUSER	Derive
DocumentType Type	FRDTYPE	Item
ProcessDate	FRDIDTE	Derive
TransactionSequence Number	FRDTSEQ	Derive
Vat TypeOf Trn	FRDVTTRN	Derive
SysTEM / user Generated	FRDSYSUSR	Derive
GL Posting Debit Sequence	FRDGLDSEQ	Derive
GL PostingCredit Sequence	FRDGLCSEQ	Derive
PostedDate	FRDPOST	Null
Audit Date	FRDAUDIT	Null
Vat Reporting Cycle	FRDVATREPT	Null
Date E/D Generated	FRDEDGEN	Null
Vat Pro-Rate	FRDVATPERC	Null

Sample Call

DECLARE

v_doc finc.v_AR_Doco;

v_return_tab gen.g0apkg.g0aReturn_tab;

v_trial VARCHAR2(1) := ‘T’

v_invsql NUMBER := 0;

PROCEDURE PLine (x_line VARCHAR2) IS

BEGIN

v_invsql := 20;

gen.g04pkg.put_line(v_fid,x_line);

END PLine;

PROCEDURE prtErrors IS

ITS_C2 sys_refCURSOR;

v_errrec gen.g0apkg.g0aReturn;

BEGIN

v_invsql := 120;

ITS_C2 := gen.g0apkg.ListErrors(v_return_tab);

LOOP

v_invsql := 130;

FETCH ITS_C2 INTO v_errrec;

EXIT WHEN ITS_C2%NOTFOUND;

PLine(makestring(' ',9)||

v_errrec.status||' '||v_errrec.message);

END LOOP;

CLOSE ITS_C2;

END prtErrors;

BEGIN

-- Delete error table

v_return_tab.DELETE;

-- Initialise Document Object

v_doc := finc.v_AR_Doco(

finc.v_frko(NULL,NULL,NULL,NULL,NULL,NULL,NULL), -- Initialise FRK

finc.v_frp_tabt(), -- Initialise FRP

finc.v_frlo(NULL,NULL,NULL) -- Initailiase FRL

);

-- Add invoice header info (FRK)

v_doc.frk := finc.v_frko('I', -- frktype

NULL, -- frkinv

bi.pitdttr, -- frkdttr

bi.pitacctyp, -- frkacctyp

bi.pitproj, -- frkrefn2

bi.pitprojd, -- frknote

NULL);

v_invsql := 350;

-- Add FRP First line

v_doc.item.EXTEND();

v_doc.item(v_doc.item.LAST) :=

finc.v_ar_itemso(finc.v_frpo(NULL, -- frplnum

bi.pivhrs, -- frpquan

bi.pivptaskc, -- frpunit

'F', -- frphead

SUBSTR(bi.pivname,1,40),-- frddesc

bi.piupayrate, -- frputot

0.0, -- frpvrate

bi.pivnum), -- frpstno

finc.v_frd_tabt());

v_invsql := 360;

-- Add FRD iformation

v_doc.item(v_doc.item.LAST).frd.EXTEND();

v_doc.item(v_doc.item.LAST).frd(v_doc.item(v_doc.item.last).frd.last) :=

finc.v_frd_trnso('TY', -- Event code

finc.v_FRDo(NULL, -- frdlnum

NULL, -- frdtrn

bi.piuglcc, -- frdglccc

bi.piuglacc, -- frdglcacc

NULL, -- frdgldcc

NULL, -- frdgldacc

ROUND(bi.piupayrate * bi.pivhrs,2))); -- frdtval

v_invsql := 370;

-- Add Additional line

v_doc.item.EXTEND();

v_doc.item(v_doc.item.LAST) :=

finc.v_ar_itemso(finc.v_frpo(NULL, -- frplumn

NULL, -- frpquan

NULL, -- frpunit

'A', -- frphead

SUBSTR(bi.pcvptaskd1,1,40), -- frpdesc

NULL, -- frputot

NULL, -- frpvrate

NULL), -- frpstno

finc.v_frd_tabt());

-- Call create document procedure

finc.f45pkg.createARDocument(v_doc,v_return_tab,v_trial);

IF NVL(v_return_tab.COUNT,0) != 0 THEN -- If failure print errors

prtErrors;

ROLLBACK;

ELSE

IF v_trial = 'F' THEN -- If success and not trial COMMIT

COMMIT;

END IF;

END;

Base object representing FRD
CREATE OR REPLACE TYPE v_frdo IS OBJECT
(
   frdlnum   NUMBER(3),
   frdtrn    NUMBER(4),
   frdglccc VARCHAR2(6),
   frdglcacc VARCHAR2(8),
   frdgldcc VARCHAR2(6),
   frdgldacc VARCHAR2(8),
   frdtval   NUMBER(17,2)
);
/

Base object representing FRP
CREATE OR REPLACE TYPE v_frpo IS OBJECT
(
   frplnum NUMBER(3),
   frpquan NUMBER(9,3),
   frpunit VARCHAR2(8),
   frphead VARCHAR2(1),
   frpdesc VARCHAR2(40),
   frputot NUMBER(17,2),
   frpvrate NUMBER(6,3),
   frpstno NUMBER(9)
);
/

Base object representing FRK
CREATE OR REPLACE TYPE v_frko IS OBJECT
(
   frktype   VARCHAR2(2),
   frkinv    VARCHAR2(12),
   frkdttr   NUMBER(9),
   frkacctyp VARCHAR2(4),
   frkrefn2 VARCHAR2(12),
   frknote   VARCHAR2(30),
   frktval   NUMBER(17,2)
);
/

Base object representing FRL
CREATE OR REPLACE TYPE v_frlo IS OBJECT
(
   frlfirst NUMBER(2),
   frlrtime NUMBER(2),
   frlrinv NUMBER(2)
);

/

Object representing FRD transaction
CREATE OR REPLACE TYPE v_frd_trnso IS OBJECT
(
   evnt VARCHAR2(2),
   frd finc.v_FRDo
);
/
CREATE OR REPLACE TYPE v_frd_tabt IS TABLE OF v_frd_trnso;
/

Object representing FRP with repeating FRD
CREATE OR REPLACE TYPE v_ar_itemso IS OBJECT
(
   frp finc.v_frpo,
   frd finc.v_frd_tabt
);
/
CREATE OR REPLACE TYPE v_frp_tabt IS TABLE OF v_ar_itemso;
/

Object representing FRK with repeating FRP and s FRL object
CREATE OR REPLACE TYPE v_ar_Doco IS OBJECT
(
   frk finc.v_FRKo,
   item finc.v_frp_tabt,
   frl finc.v_frlo
);
/

History of Changes

Date	System Version	By Whom	Job	Description
07-Feb-2013	v03.0.0.0	Marchand Hildebrand	T188555	Create manual