AR TABLES

• RA_CUSTOMER_TRX_ALL :stores invoice header information.
• RA_CUSTOMER_TRX_LINES_ALL stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines.
• The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts.
• RA_CUST_TRX_LINE_GL_DIST_ALL stores accounting distribution records for all transaction lines except bills receivable.
• RA_CUSTOMERS – Customer information
• RA_CUST_TRX_TYPES_ALL – Customer Transaction Type
• AR_PAYMENT_SCHEDULES_ALL
• AR_CASH_RECEIPTS_ALL stores one record for each receipt entry
• AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.
• AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
• AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.

Order To Cash

Order to cash normally refer to the process in which taking customer sale order via different sales channel like email, internet, sales person and then generating an invoice and collecting payment for that invoice and then receipt

Complete Order to cash cycle steps including

1. Entering the Sales Order

2. Booking the Sales Order

3. Launch Pick Release

4. Ship Confirm

5. Create Invoice

6. Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)

7. Transfer to General Ledger

8. Journal Import

9. Posting

 

 

Tables:

 

1. OE_ORDER_HEADERS_ALL

2. OE_ORDER_LINES_ALL

3. WSH_DELIVERY_DETAILS

4. WSH_DELIVERY_ASSIGNMENTS

5. WSH_NEW-DELIVERIES

6. WSH_DELIVERY_LEGS

7. WSH_TRIP_STOPS

8. WSH_TRIPS

9. M TL_TRX_REQUEST_LINES

10. MTL_MATERIAL_TRANSACTION_TEMP

11. OE_SETS

12. OE_LINE_SETS

13. OE_LINES_ALL

14. MTL_RESERVATIONS

 

Procure To Pay Cycle

Procurement to pay cycle

In P2P cycle the moduled included are PO,AR,AP,CM and GL
The requirement arises from requirements for items,and company places order for items needed.

The vendor supplies the items.Once items are recieved payment will be made.That is a invoice will be raised and payment will be made to the vendor.The payment can be of cash,cheque etc.


This is governed under cash management and finally the transaction detalis will be accounted in GL.



Important Base Tables Involved are:


Requisition

po_requisition_headers_all
po_requisition_lines_all ( segment1 ========== requisition number)
po_req_distributions_all
Note: requisition_header_id is the link between po_requisition_headers_all and po_requisition_lines_all

requisition_line_id is the link between po_requisition_lines_all and po_req_distributions_all



Purchase order

po_headers_all
po_lines_all
po_distributions_all
po_line_locations_all

Note: po_header_id is the link between all these tables




Receipt


rcv_shipment_headers
rcv_shipment_lines
rcv_transactions

quality result stored in qa_results table


Note: shipment_header_id is the link between rcv_shipment_headers and rcv_shipment_lines

po_header_id is the link between rcv_shipment_headers and rcv_transaction

Invoices


ap_invoices_all

ap_invoice_distributions_all

Note: invoice_id is the link between ap_invoices_all and ap_invoice_distributions_all



Payments


ap_checks_all

ap_invoice_payments_all

ap_payment_schedules_all


Payment Reconcillation


ap_banks

ap_bank_branches



GL Transfer


gl_periods

gl_period_status

gl_set_of_books

glfv_charts_of_accounts

FORMS 3

 Creating LOVs



1. Start a new module by clicking on File - New - Form.


2. Change Module’s name to TEST_LOV.


3. Set up DEPT and EMP data blocks and its relationship.


4. Click Object Navigator
Forms
LOVs and then on tool bar click Create button.


5. In the New LOV dialog box select


• New Record Group Based on the Query Below...


6. Enter the following code


SELECT distinct job


FROM emp


ORDER BY job


7. Click OK. (The hour glass will no go away)


8. Double click to open the property palette for the new LOV (under LOVs).


9. Change Name to JOB_LOV


10. Click on Column Mapping Properties (under Functional).


11. Click on More button.


12. Click Column Name JOB then in Return Item box type Emp.JOB. This specifies the destination into which the LOV will place the job value.


13. Enter a display width of 50. Click OK


14. Change Object Navigator
Record Groups
LOV name to JOB_LOV


15. On Layout Editor double click on JOB to bring up property palette and set List of Values to JOB_LOV


16. Set Validate from List to YES.


17. Save file under c:/orant/Forms50/Class/TEST_LOV.FMB


18. Run form.






• LOV, using auto-reduction, to populate a job if it finds one job that matches the first character that you typed. Otherwise a pup-up window open if more than on job are found or invalid job is entered.

Forms-2

Practice 11 - Create Data Blocks with Relationships

Creating Master Block

================

1.   Right click on Object Navigator | Forms | Data Blocks then select

Data Block Wizard.

2.   At Data Block Wizard - select

      Table or View

Then click Next

3.   Enter DEPT in the edit box of Table or view.

Then click Refresh

Click  select all to database items

Then click Next (Enforce Data Integrity, Content Canvas, Single Record, Form Style, No Scrollbar)

4.       Complete creating the Dept Block

5.      Detail Block Creation

6.      Create a new data block

7.      Select the Table (ex. EMP), enforce data integrity

8.      Select

9.   Click Create Relationship (Auto create relatioship should be checked)

Click OK for the next dialog box.

10. Use the same canvas used by Master block, Should be Multi-Record (in most cases), Style should be Tabular(to display multiple records), Scrollbar should be displayed)

11. Use the default Width and Height

Then click Next

12. Select

       Form

Then click Next

13. Frame Title: DEPARTMENT-EMPLOYEE DETAILS

Records Displayed: 1

Distance Between Records: 0

Then click Next

Then click Finish

14. Compile and Run Form

Forms

Practicals:

Single Table Maintenance - (Create all the single record maintenance forms in your project)

 Practice 1 - Create a Data Block Using the Data Block Wizard

1.   Launching the Form Builder

·                                 Start - Programs - Developer 2000 R2.1 - Form Builder.

2.   At Welcome to the Form Builder - select

·         Use the Data Block Wizard

Then click OK

3.   At Data Block Wizard - select

·         Table or View

Then click Next

4.   Click Browse…

Connect to database SCOTT / TIGER

Then click Connect

Click OK at the next dialog box.

At select DEPT then click OK

Click >> select all to database items then click Next

(Enforce Data Integrity, Content Canvas, Single Record, Form Style, No Scrollbar)

5.   Select

·         Just create the data block

Then click Finish

6.   Change MODULE1 to DEPT

Practice 2 - Create and Modifying Layouts Using the Layout Wizard

1.   Right click on Object Navigator | Forms | Data Blocks | DEPT

2.   Select Layout Wizard

3.   Select Content type

Then click Next

4.   Click >> select all to Display items

Then click Next

5.   Use the default Width and Height

Then click Next

6.   Select

·         Form

Then click Next

7.   Frame Title: DEPT

Records Displayed: 1

Distance Between Records: 0

Then click Next

Then click Finish

8.   Compile and Save the file in D:/Oracle_Dev/TEST_DEPT

9.   Run Form

10. Click Execute Query button to populate data into data block.

11. Click Next / Previous button for navigation.

12. Exit Form

Practice 3 - Modifying Layouts Using the Layout Wizard

1.   Right click on Object Navigator | Forms | Canvases | Graphics | FRAME3

2.   Select Layout Wizard

3.   Remove DEPTNO from Displayed items to Available Items

Then click Next

4.   Use the default Width and Height

Then click Next

5.   Select

·         Tabular

Then click Next

6.   Frame Title: DEPT

Records Displayed: 5

Distance Between Records: 0

Then click Next

Then click Finish

7.   Compile and Run Form

8.   Exit CANVAS2

Restricting Queries with SQL statements

Practice 5 - Restricting queries with field-level Criteria

1.   Click on Enter Query Button.

2.   Move cursor to Sal

3.   Type:

:Salary

4.   Click Execute Query Button.

5.   Type the following in the pop up dialog window.

:Salary between 1200 and 3000

6.   Click OK.

Inserting, Updating and Deleting Records

Practice 6 - using Form to update records

1.   Click on Execute Query Button.

2.   Modify any record.

3.   Click Save Button.

Practice 7 - using Form to insert records

1.   Click on Insert Record Button.

2.   Enter any data in the blank area.

3.   Click Save Button.

Practice 8 - using Form to delete records

1.   Click on Execute Query Button.

2.   Navigate to any that you wont to delete.

3.   Click on Delete Record Button.

4.   Click Save Button.

Practice 10 - Creating a hints

1.   Right click on EXIT button

2.   Select Property Palette

3.   Find Hint under Help section.

4.   Type Click to Exit This Program

5.   Exit and run.

Practice 11 - Creating a ToolTips

1.   Right click on EXIT button

2.   Select Property Palette

3.   Find ToolTip under Help section.

4.   Type Exit

5.   Exit and run.

Attached Documents

Fnd documents can be accessed using Pk_value defined in FND_ATTACHED_DOCUMENTS table.
this will refer to primary key of the refering table.E.g,if po_headers_all then po_hearder_id will be the pk_value1.


sample code to find out pk value:






select * from fnd_document_entities e,fnd_document_entities_tl t,
fnd_attached_documents fad,po_LINES_all ph,FND_DOCUMENTS_SHORT_TEXT FSL
where e.DOCUMENT_ENTITY_ID=t.DOCUMENT_ENTITY_ID and E.table_name='PO_LINES' and e.DATA_OBJECT_CODE='PO_LINES' and fad.ENTITY_NAME=e.ENTITY_NAME and ph.PO_LINE_ID=fad.PK1_VALUE

Base tables for Documents are

fnd_document_datatypes FND_DOCUMENTS_SHORT_TEXT

fnd_document_entities_tl
fnd_documents_tl
fnd_documents
fnd_document_categories_tl
fnd_doc_category_usages
fnd_attachment_functions
fnd_attached_documents

FND_DOCUMENTS_LONG_TEXT

Functions

Example of creating a function and placing it in our program.
Used to avoid repeated records in reports.Where it fetched different vales for same fields.

CREATE OR REPLACE FUNCTION xx_salesname_proj (p_project_id IN NUMBER)

RETURN VARCHAR2
IS
v_full_name VARCHAR2 (240);
v_final_name VARCHAR2 (240);
CURSOR c1 (r_project_id NUMBER)
IS
SELECT DISTINCT full_name
FROM per_all_people_f
WHERE person_id  IN (
SELECT person_id
FROM pa_credit_receivers pcr, pa_projects_all pp
WHERE pcr.project_id = pp.project_id
AND pp.project_id = r_project_id);
v_index NUMBER;
BEGIN
v_full_name := '';
v_final_name := '';
v_index := 0;
OPEN c1 (p_project_id);
LOOP
FETCH c1
INTO v_full_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.put_line (v_full_name);
v_index := v_index + 1;
IF v_index = 1
THEN
v_final_name := v_full_name;
ELSE
v_final_name := v_final_name ','
v_full_name;
END IF;
END LOOP;
CLOSE c1;
RETURN v_final_name;
END;
/

In our program call from query

xxri_salesname_proj (pp.project_id) sales_person_name

AR LOCKBOX

AutoLockbox automated tool used to receipts which are sent to bank directly.


It Validates data before transferring then to recievables.
 
Steps Performed by AR Lockbox:


Make required Functional setups for defining Lockbox.

1.Create Transmission format.

• 
  
  Give Transmission Name
• 
  
  LOCKBOX HEADER,RECIPET,OVERFLOW RECIPET
• 
  
  LOCKBOX HEADER with identifier:

SAMPLE CONTROL FILE:

LOAD DATA

APPEND

-- Type R2- Overflow Receipt

INTO TABLE AR_PAYMENTS_INTERFACE_ALL

WHEN RECORD_TYPE = ’R2’

(STATUS CONSTANT ’AR_PLB_NEW_RECORD’,

RECORD_TYPE POSITION(01:02) CHAR,

LOCKBOX_NUMBER POSITION(03:05) CHAR,

INVOICE1 POSITION(06:19) CHAR,

AMOUNT_APPLIED1 POSITION(20:30) CHAR,

OVERFLOW_SEQUENCE POSITION(31:34) CHAR,

OVERFLOW_INDICATOR POSITION(35:35) CHAR,

ITEM_NUMBER POSITION(36:38) CHAR,

CURRENCY_CODE POSITION(39:41) CHAR)

-- Type R1 - Payment

INTO TABLE AR_PAYMENTS_INTERFACE_ALL

WHEN RECORD_TYPE = ’R1’

(STATUS CONSTANT ’AR_PLB_NEW_RECORD’,

RECORD_TYPE POSITION(01:02) CHAR,

LOCKBOX_NUMBER POSITION(03:05) CHAR,

BATCH_NAME POSITION(06:08) CHAR,

TRANSIT_ROUTING_NUMBER POSITION(09:18) CHAR,

ACCOUNT POSITION(19:36) CHAR,

CHECK_NUMBER POSITION(37:46) CHAR,

REMITTANCE_AMOUNT POSITION(47:56) CHAR,

DEPOSIT_DATE POSITION(57:62) DATE ’RRMMDD’,

ITEM_NUMBER POSITION(63:65) CHAR,

CURRENCY_CODE POSITION(66:68) CHAR,

DEPOSIT_TIME POSITION(69:72) CHAR)

-- Type R0 - Lockbox Header

INTO TABLE AR_PAYMENTS_INTERFACE_ALL

WHEN RECORD_TYPE = ’R0’

(STATUS CONSTANT ’AR_PLB_NEW_RECORD’,

RECORD_TYPE POSITION(01:02) CHAR,

LOCKBOX_NUMBER POSITION(03:05) CHAR,

ORIGINATION POSITION(06:14) CHAR)

2.Create a loader file(Flat File) in specified format.

Sample Data File:

R0P14043000096

R1P1400002130937 612955 20002914640005587025990901018USD2003

R2P142424068 0000102900000010018USD


3.Place the control file in AR_Top bin directory.

4.In AR run the interface program lockbox with transmission name,control file,data file with path.

Note: Give the control file name without extension and path.

5.sumbit for validation(to get the receipts transferred to base tables.

5.sumbit the lockbox interface program.

6.the output will have the details of transferred recepits.

Interface tables involved are




WITHOUT vALIDATION



AR_PAYMENTS_INTERFACE_ALL



FOR VALIDATION



AR_INTERIM_CASH_RECEIPTS_ALL and

AR_INTERIM_CASH_RCPT_LINES_ALL



BASE TABLES



AR_CASH_RECEIPTS_ALL

AR_CASH_RECEIPT_HISTORY_ALL

AR_DISTRIBUTIONS_ALL

AR_RECEIVABLE_APPLICATIONS_ALL

AR_PAYMENT_SCHEDULES_ALL