Flex Fields and Form Personalization in Oracle Apps

Flex fields:

• A flex field to capture information about your organization. Flexible has a flexible structure for storing key information.
• Like a company, cost, center, and account.
• They also give you a highly adaptable structure for storing customized information in an Oracle application.
•  A flex field is a field made up of subfields, or segments. There are two types of flex fields.

1. Key flex field
2. Descriptive flex field

Key flex field:

• A key flex field is made up of segments, where each segment has both a value and a meaning, which appears on your form as a normal text field with an appropriate prompt.
• An example of a key flex field is the accounting flex field. This flex field can always be customized to have as many segments Like the company, department, cost center, account, etc.

Descriptive flex field:

• DFF adds additional fields to the form to track additional information needed by the business that would not be captured by the standard form.
• Dff can be context-sensitive, where the information your application stores depends on other values your users enter in other parts of the form.

Dff concepts:

• DFF has two different types of segments, global and context-sensitive.
• A global segment is a segment that always appears in the DFF pop-up window.
• A context-sensitive segment is a segment that may or may not appear depending on what other information is Present in your form.
•  A DFF uses columns that are added to a database table. A DFF requires one column for each possible segment and one additional column in which to store structure information. The dff is usually named Attribute n where n is a number. 

Difference between kff and dff:

• Kff captures mandatory business information like company department, account, etc.
• Dff captures additional business information like the name of the company’s MD etc.
• Kff is mandatory.
• DFF is optional.
• Kff displays like text items.
• Dff displays like [].
• Kff data are stored in the segments.
• Dff store in the attribute.
• 30 segment columns are reserved for kff data.
• 15 attribute columns are reserved for the dff data.

Benefits of flex fields:

•  Customize your application to conform to your current business practice for accounting codes, product codes, and other codes.
•  Customize your application to capture data that would not otherwise be tracked by your application.
•  Customize data fields to meet your business needs without programming.
•  Query intelligent fields for very specific information.

Tables in Dff:

       Select * from fnd_descriptive_flexs;

       Select * from fnd_descriptive_flexs 

        where application_table_name=’po_requisition_headers_all;

Key flex fields:

   A few Key Flex fields in different Oracle modules are:

     Module: GL

1.       Accounting

           Module: HRMS

1.  Job

2.  Position

3.  Grade

4.  Personal analysis

Module: Inventory

          Account aliases

           Item catalogs

           Item categories

           Sales orders

           Stock Location

           System items

Module: AR

         Sales tax location

         Territory

Main tables:

1. Fnd_id_flexs:

•  This table captures the information on all the key flex fields. The main columns in the tables are:

                 Application_id - Column consists of application id.

                 Id_flex_code - kff code (like ‘gl’,’ar’,etc)

                 Id_flex_name – kff name (‘accounting flexfield’,’category field’,etc)

                 Application_table_name

               

    2. Fnd_id_flex_structures:

• This table stores structure information about key flex fields.

            Application_id- module code

            Id_flex_code- code of kff

            Id_flex_num- number of a structure

    3. Fnd_id_flex_segments:

• It captures the information of segments.

            Application_id

            Id_flex_code

            Id_flex_num

            Seg_num

            Flex_value_set_id

   

   4. Fnd_flex_value_sets:

• This table captures the information on each segment's value set.

          Flex_value_set_id

    5. Fnd_fles_values:

• This table captures the information on each value code of a value set of a segment.

            Flex_value_set_id

            Flex_value_id

   6. Fnd_flex_values_tl:

• This table captures the information of each value description of a value set of a segment.

             Flex_value_id

Form personalization:

• Form personalization is a feature in oracle apps that allows us to change the behavior of form-based screens, including changing properties, executing built, displaying messages, and adding menu entries.
• It can be moved easily through and loaded from one instance to another, and it can be restricted at the site/user/responsibility level.

Uses:

• Personalization is stored in tables rather than files. 
• Can be moved easily through FNDLOAD from one instance to another.
•  Can be restricted at the site/responsibility/user level.
• Easy to disable/enable with the click of a button.
• Can be restricted to function or form.
•  Can be applied to new responsibilities/users easily.

What can be done through form personalization?

• Zoom from one form to another.
• Pass data from one form to another through global variables.
• Enable/disable/hide fields dynamically.
• Launch the URL directly from the oracle form.
• Call plsql procedure.

Meaning of special fields in form personalization:

• Menu (1-15) will be in tools.
• Populate tools menu (special 1-15)
• Populate reports menu (special 16-30)
• Populate action menu (special 31-45)
•  It is recommended to use the menu before tools.

To open form personalization go to help->diagnostics->custom code->personalize

Components used in form personalization:

       Trigger event:

       Processing mode: query mode or not in query mode or both.

       Context: at what level like user or responsibility level.

       Menu: This is used to call one form from another. It is used to implement the zoom functionality.

      Built-in:

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Oracle P2P, O2C, Drop Shipment & B2B Processes Explained | Complete Guide

P2P: (Procure to Pay)

• Create Requisition
• Create Purchase Order
• Create Inventory Receipt
• Enter AP Invoice
• Make Payment
• Transfer to GL

1. Create requisition:

  Headers - po_requisition_headers_all

  Lines - po_requisition_lines_all

  Distribution -  po_req_distribution_all

Po_requisition_headers_all:

  Important columns:

•   Requisition_header_id: requisition header unique identifier.
•   Segment 1: requisition number.
•   Authorization_status: authorization type. (Complete)
•   Type_lookup_code: requisition type. (Purchase and internal) 

Description:

PO_REQUISITION_HEADERS_ALL stores information about requisition headers.

 

Po_requisition_lines_all:

Important columns:

• Requisition_header_id: requisition header unique identifier.
• Line_type_id: line type (goods).
• Quantity_number: quantity ordered.
• Categeory_id: item category unique identifier.

 Description:

• PO_REQUISITION_lines store information about requisition lines. Each row contains the line number, item number, item category, item description, a need-by date, deliver-to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line.

 

 po_req_distribution_all:

 Important columns:

•  Distribution_id: requisition distribution unique identifier.
•  Requisition_line_id: requisition line unique identifier.
•  Code_combination_id: unique identifier for the general ledger.
•  Distribution_num: distribution number.

 Description:

•  Po_req_distribution_all stores information about the accounting distribution associated with each requisition line. Each requisition line must have at least one accounting distribution.
• Po_req_distribution_all is one of three tables storing your requisition number.

Accounting distribution:

• Accounting distributions are used to define how an amount will be accounted for, such as how the expense, tax, or charges will be accounted for on a vendor invoice. Every amount that must be accounted for when the vendor invoice is journalized will have one or more accounting distributions.

2. Create purchase order:

    Header: po_headers_all

    Line: po_lines_all

    Distribution: po_distribution_all

 po_headers_all:

Important columns:

• Po_header_id
• Segment1
• Vendor_id
• Vendor_site_id

Description:

Po_headers_all contains header information for all purchasing documents. Six types of documents use po_headers_all.

• RFQs
• Quotations
• Standard purchase orders
• Planned purchase orders
• Blanket purchase orders
• Contracts

 po_lines_all:         

  Important columns:

               Po_line_id: document the unique identifier.

               Po_header_id: document header unique identifier. (Reference po_headers_all ,po_header_id)

               Item_id: item unique identifier.

               Line_num: line number.

 Description:

     Po_lines_all stores current information about each order purchases order line. There are five document types that use the line.

•  RFQs
• Quotations
• Standard purchase orders
• Blanket purchase orders
• Planned purchase orders

po_distribution_all:

 Important columns:

         Po_distribution_id: document distribution unique identifier. The primary key for this table.

         Po_header_id: document header unique identifier. Reference po_headers_all.po_header_id.

         Po_line_id: document the unique identifier. Reference po_lines_all.po_lines_id.

        Line_location_id:

Description:

            Po_distribution_all contains accounting distribution information for a purchase order shipment line. 

3. Create the receipt:

       Headers: rcv_shipment_headers_all

       Lines: rcv_shipment_lines_all

       Transaction: rcv_transactions

Rcv_shipment_headers_all:

   Important columns:          

          Shipment_header_id: receipt header unique identifier.

          Receipt_source_code: source type of the shipment – vendor, internal order, or customer.

          Vendor_id: source supplier unique identifier.

          Shipment_num: shipment number.

          Receipt_num: receipt number.

 

Description:

            Rcv_shipment_headers_all stores common information about the source of your receipts or expected receipts.

           There are two receipt source types, supplier and internal order.

 

 Rcv_shipment_lines_all:

Important columns:

        Shipment_line_id: shipment line unique identifier.

       Item_id: item identifier.

        Vendor_item_num: suppliers item number.

       Shipment_line_status_code: receipt status of the shipment line

 Description:

       Rcv_shipment_lines_all stores information about items that have been shipped.

 

 Rcv_transaction:

  Important columns:

       Transaction_id: receiving transaction unique identifier.

       Request_id:

      Transaction_type: receiving transaction type.

     Transaction_date:

     Quantity:

Description:

        Rcv_transaction stores historical information about receiving transactions that you have performed. 

4. Invoices:

 ap_invoices_all:

Important columns:

           Invoice_id: generated using a database sequence.

           Vendor_id:                

           Invoice_num:

          Set_of_books_id:

          Payment_currency_code:

Description:

        This table corresponds to the invoice header block of the Invoice workbench. AP_INVOICES_ALL holds information this table corresponds to the Invoices header block of the Invoice workbench. AP_INVOICES_ALL holds information on Oracle Projects, Supplier Portal, Refunds from Oracle Receivables, etc. This table holds all type of invoices, which includes Standard, Prepayments, Credit Memo, Debit Memo, Mixed invoice, Withholding invoices, Interest Invoice, Retainage invoices, Payment Requests, etc.,

 ap_invoice_distributions_all:

 Important columns:

          Invoice_id: Unique invoice distribution identifier.

         SET_OF_BOOKS_ID: Ledger identifier of the invoice distribution.

         AMOUNT: Amount on invoice distribution.

         INVOICE_DISTRIBUTION_ID:

   Description:

          AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution information that is manually entered or system-generated.

 

 ap_checks_all:

Important columns:

         Amount: payment amount

         Bank_account_id: longer used

        Bank_account_name:

        Check_id_number:

        Check_number: payment number

       Vendor_name: supplier name

Vendor_site_code: supplier site code

 

Descriptions:

    Ap_checks_all stores information about payments issued to suppliers or refunds received from suppliers.

 ap_invoice_payments_all:

Important columns:

       Invoice_payment_id:

      Invoice_id:

      Check_id:

      Payment_num:

Descriptions:

       Ap_invoice_payments_all contains the record of invoice payments that you made to suppliers.

 ap_payment_schedules_all:

Important columns:

       Invoice_id:

       Payment_num:

      Amount_remaining:

Descriptions:

    Ap_payment_schedules_all contains information about the schedule for an invoice.

5. GL Transfer:

 gl_periods:

Important columns:

       Period_set_name: name of the accounting calendar.

       Period_name: system-generated accounting period name.

Descriptions:

      Gl_periods contains information about the accounting periods that are defined using the accounting calendar form.

 gl_period_statuses:

Important columns:

Application_id: identifier associated with the application

Ledger_id: the unique identifier of the ledger

Period_name:

Descriptions:

   Gl_period_statuses contains the statuses of your accounting periods.

 

gl_set_of_books:

Important_columns:

 

Oracle application modules:

AP - oracle payables.

AR- oracle receivables

AS- oracle sales

CS - oracle service

CSS - support.

GL - oracle general ledger

GR - oracle processes regulatory management

HRI - human resource intelligence

ONT - oracle order management

PO – oracle purchasing

QP- oracle pricing

QRM- oracle risk management

WIP – oracle work in process

WMS- oracle warehouse management systems

WSH - oracle shipping

DBA – oracle database administrator

Difference between Oracle DBA and Oracle Developer:

• Developers are working on SQL & plsql.
• DBA's job is to manage server-related activities
• The developer has written to coding.
• DBA has to manage servers, like an administration job.

Order to Cash (O2C):

• Enter the Sales Order 
• Book the Sales Order
• Launch Pick Release
• Ship Confirm
• Create Invoice
• Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
• Transfer to General Ledger 
• Journal Import
• Posting

1. Enter sales order:

      Headers – oe_order_headers_all

      Lines – oe_order_lines_all

oe_order_headers_all:

     Important columns:

        Check_number:

        Header_id:

       Order_number:

        Order_date:

        Payment_amount:

        Order_type_id:

Descriptions:

     Order headers are stored in oe_orders_headers_all.

oe_order_lines_all:

   Important columns:

       Booked_flag: indicates whether the line has been closed (n)or not(y)

      Item_type_code:

      Line_id:

     Ordered_item:

    Inventory_item_id:

     Shipment_number:

    Price_quantity:

 Descriptions:

       Oe_order_lines_all stores information for all order lines in Oracle order management.

        

Shipping tables:

Wsh_delievery_details

Wsh_new_deliveries

Wsh_frieght_costs

 

Wsh_delievery_details: (released _status ‘S’ submitted for release)

Columns:

            Delivery_detail_id

             Source_header_id

            Source_line_id

            Inventory_item_id

           Ship_to_locations_id

            Ship_to_contact_id

           Delivered_quantity

           Unit_weight

           rcv_shipment_line_id: this column stores the receiving shipment receipt line unique identifier. This is used to join the rcv_shipment_ lines table.

 

wsh_new_deliveries:

Columns:

          Delivery_id

          Delivery_name: this is the name of the shipment.

         Planned_flag: planned flag (N=unplanned, Y=planned)

          Status_code: delivery shipping status. e.g OP for open delivery.

         Cod_amount: This amount is for cash on delivery.

wsh_frieght_costs:

        WSH_FREIGHT_COSTS records freight costs.

     Columns:

          Freight_costs_id

          Unit_amount: the unit amount of this shipping cost.

          Quantity:

          Delivery_id:

Dropship cycle:

1. Create Sale Order

2. Book Sales Order

3. Check Status

4. Progress Sales Order

5. Check Status

6. Release Purchase Order

7. Import Requisition / Purchase Order

8. Link between sales order and purchase order

9. Receive the material against the purchase order

10. Check the Order status.

Oracle drop-ship tables:

 Po_lines_all: stores purchase document lines for purchase orders, purchase agreements, quotations and RFQ.

 Po_headers_all: Po_headers_all contains header information for all purchasing documents.

 Po_distributions_all: stores purchase order information.

  po_requisition_headers_all:

  po_requisition_lines_all:

  po_req_distribution_all:

  oe_order_lines_all:

   rcv_shipment_lines:

  oe_drop_ship_sources: This table stores the relationship between Order Lines in the oe_order_lines_all table and associated oracle purchasing requisitions in PO_REQUISITIONS_ALL and Oracle purchasing purchase orders in PO_LINES_ALL.

 

Back to Backorder process:

1.     Enter Sales Order

2.     Book Sales Order

3.     Progress Sales Order to create Supply Order

4.     Requisition Import

5.     Create Purchase / Inventory Receipt

6.     Pick Release Sales Order

7.     Ship Confirm

 

 GL INTERFACE:

  Interface table:

• Gl_interface

  Base table:

• Gl_je_batches
• Gl_je_headers
• Gl_je_lines

Table columns:

     Accounting date, date created, category name, source name, entered debit/credit, segments.

Concurrent program:

    Journal Import

    Journal posting

 

Gl_interface:

    gl_interface table is where journal import receives accounting data that you import from other systems.

 

Gl_je_batches:

   GL_JE_BATCHES contains journal entry batches.  Each row includes the batch name, description, status, running total debits and credits, and other information.

 Columns:

     Je_batch_id

     Approve_employee_id

    Name

    Status

 

Gl_je_headers:

   GL_JE_HEADERS contains journal entries. Batch ID, journal entry name and description, and other information about the journal entry.

Columns:

  Je_header_id

  Ledger_id

  Name

  Status

 

Gl_je_lines:

  GL_JE_LINES contains the journal entry lines that you enter in the Enter Journals form.

 Columns:

    Je_header_id

    Je_line_num

    Ledger_id

    Status

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.