PURCHASE ORDER - IMPORTANT TABLES

                     PURCHASE ORDER - IMPORTANT TABLES

Table Name : PO_REQUISITION_HEADERS_ALL
Columns:
            REQUISITION_HEADER_ID
            PREPARER_ID
            SEGMENT1
            SUMMARY_FLAG
            ENABLED_FLAG
Segment1:  Stores information about requisition headers. You need one row for each requisition header you create. Each row contains the requisition number, preparer, status, and description.SEGMENT1 is the number you use to identify the requisition in forms and reports (unique).
Table Name : PO_REQUISITION_LINES_ALL
Columns:
            REQUISITION_LINE_ID
            REQUISITION_HEADER_ID
            LINE_NUM
            LINE_TYPE_ID
            CATEGORY_ID
            ITEM_DESCRIPTION
            UNIT_MEAS_LOOKUP_CODE
            UNIT_PRICE, QUANTITY
            DELIVER_TO_LOCATION_ID
            TO_PERSON_ID
            SOURCE_TYPE_CODE
Stores information about requisition-lines. Line number, item number, item category, item description, need–by date, deliver–to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line. LINE_LOCATION_ID - Purchase order shipment line on which you placed the requisition. It is null if you have not placed the requisition line on a purchase order. BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement or CATALOG quotation line information for the requisition line. PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you exploded or multi-sourced this requisition line.
Table Name : PO_HEADERS_ALL
Columns:
            PO_HEADER_ID
            AGENT_ID
            TYPE_LOOKUP_CODE
            SEGMENT1
            SUMMARY_FLAG
            ENABLED_FLAG
Information for your purchasing documents. There are six types of documents that use PO_HEADERS_ALL. RFQs, Quotations, Standard purchase orders, planned purchase orders, Blanket purchase orders, Contracts can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using PO_HEADER_ID.BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders. If we use copy document Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID.
Table Name : PO_LINES_ALL
Columns:
            PO_LINE_ID
            PO_HEADER_ID
            LINE_TYPE_ID
            LINE_NUM
Stores current information about each purchase order line. CONTRACT_NUM reference a contract purchase order from a standard purchase order line.

Table Name : PO_VENDORS
Columns:
            VENDOR_ID
            VENDOR_NAME
            SEGMENT1
            SUMMARY_FLAG
            ENABLED_FLAG
Information about your suppliers. Purchasing, Receiving, Payment, Accounting, Tax, Classification, and General Information.
Table Name : PO_VENDOR_SITES_ALL
Columns:
            VENDOR_SITE_ID
            VENDOR_ID
            VENDOR_SITE_CODE
Information about your supplier sites. A row for each supplier site you define. Each row includes the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this information to store supplier address information.
Table Name : PO_DISTRIBUTIONS_ALL
Columns:
            PO_DISTRIBUTION_ID
            PO_HEADER_ID
            PO_LINE_ID
            LINE_LOCATION_ID
            SET_OF_BOOKS_ID
            CODE_COMBINATION_ID
            QUANTITY_ORDERED
            DISTRIBUTION_NUM
Contains accounting distribution information for a purchase order shipment line. You need one row for each distribution line you attach to a purchase order shipment.

There are 4 - types of documents using distributions in Oracle Purchasing:            · Standard Purchase Orders
            · Planned Purchase Orders
            · Planned Purchase Order Releases
            · Blanket Purchase Order Releases
Includes the Destination Type, Requestor ID, Quantity Ordered and Deliver–To Location for the distribution.

Table Name : PO_RELEASES_ALL
Columns:
            PO_RELEASE_ID
            PO_HEADER_ID
            RELEASE_NUM
            AGENT_ID
            RELEASE_DATE
Contains information about blanket and planned purchase order releases. You need one row for each release you issue for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment.
Table Name : PO_VENDOR_CONTACTS
Columns:
            VENDOR_CONTACT_ID
            VENDOR_SITE_ID
Stores information about contacts for a supplier-site. You need one row for each supplier contact you define.
Each row includes the contact name and site.
Table Name : PO_ACTION_HISTORY
Columns:
            OBJECT_ID
            OBJECT_TYPE_CODE
            OBJECT_SUB_TYPE_CODE
            SEQUENCE_NUM
Information about the approval and control history of your purchasing documents. There is one record in this table for each approval or control action an employee takes on a purchase order, purchase agreement, release, or requisition.
            · OBJECT_ID - Document header Identifier
            · OBJECT_TYPE_CODE - Document Type
            · OBJECT_SUB_TYPE_CODE - Document subtype
            · SEQUENCE_NUM - Sequence of the approval or control action for a document.
Table Name : PO_REQ_DISTRIBUTIONS_ALL
Columns:
            DISTRIBUTION_ID
            REQUISITION_LINE_ID
            SET_OF_BOOKS_ID
            CODE_COMBINATION_ID
            REQ_LINE_QUANTITY
            DISTRIBUTION_NUM
Stores information about the accounting distributions associated with each requisition line.
Table Name : PO_LINE_LOCATIONS_ALL
Columns:
            LINE_LOCATION_ID   
            LAST_UPDATE_DATE
            LAST_UPDATED_BY
            PO_HEADER_ID
            PO_LINE_ID
            SHIPMENT_TYPE
Contains information about purchase order shipment schedules and blanket agreement price breaks. You need one row for each schedule or price break you attach to a document line.

There are 7- Types of documents that use shipment schedules:            · RFQs
            · Quotations
            · Standard Purchase Orders
            · Planned Purchase Orders
            · Planned Purchase Order Releases
            · Blanket Purchase Orders
            · Blanket Purchase Order Releases

Each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders, and price break information for blanket purchase orders, quotations and RFQs.

Purchase Order - Related Tables Information

PO_REQUISITION_HEADESR_ALL PO_REQUISITION_LINES_ALL	When you raise the Requisition these tables effected. (PO_REQUISITION_HEADER_ID) is the join between the tables
PO_REQ_DISTRIBUTION_ALL	It distributes the Requisition Account information.
PO_HEADERS_ALL (PO_HEADER_ID)
PO_LINES_ALL	When PO Created PO # stored in tables. (PO_HEADER_ID) is the join for tables
PO_DISTRIBUTIONS_ALL	It will distribute the PO # Account information
PO_ACTION_HISTORY	Here you can get Approvals notification Status
PO_VENDORS_ALL(VENDOR_ID)
PO_VENDOR_SITES_ALL (VENDOR_SITE_ID)
PO_VENDOR_CONTACTS_ALL  (VENDOR_CONTACT_ID)	Contacts Vendor, Vendor site and contact information (Vendor_id, Vendor_contact_id, VEDNOR_SITE_ID)
RCV_SHIPMENT_HEADERS_ALL (SHIPMENT_HEADER_ID)	Common information about the source of your receipts or expected receipts
RCV_SHPMENT_LINES_ALL	When you issue the receipt The recipient number, Shipment location stores in the table.
RCV_TRANSACTIONS (TRANSACTION_ID)
MTL_MATERIAL_TRANSACTIONS  (TRANSACTION_ID)	Stores material transaction information (Transaction_id)
PO_RELEASE_ALL	To see which purchase order is released.
PO_AGENTS	Contains information about buyers and purchasing managers.
PO_NOTIFICATION_CONTROLS	Contains information about the notification control rules for blanket, planned, and contract purchase orders.
PO_APPROVAL_LIST_HEADERS	List of approvers for the purchasing document. Used for requisition approvals only
PO_APPROVAL_LIST_LINES	Approval list lines for the requisition approval list.
PO_POSITION_CONTROLS_ALL	assignment of control groups to jobs and/or positions
PO_DOCUMENT_TYPES_ALL_B	Default, control, and option information you provide to customize
PO_CONTROL_GROUPS_ALL	Control groups you use in your business
PO_RFQ_VENDORS	Information about the set of suppliers assigned to a request for quotation (RFQ)
PO_VENDOR_LIST_HEADERS (VENDOR_LIST_HEADER_ID)	Stores information about supplier quotation lists you create.
FINANCIALS_SYSTEM_PARAMS_ALL	This Tables stores common and default information between AP and PO

Oracle - E- Business – E-Commerce - EDI

Oracle - E- Business – E-Commerce - EDI

EDI Stands for Electronic Data Interchange.

EDI is the electronic data exchange the information between trading partners. Data files are exchanged in a standard format to minimize manual efforts, speed data processing and ensure accuracy.

EDI, electronic funds transfer (EFF), electronic mail and fax as increasingly being used to schedule operations, streamline order fulfillment and optimize cash flow

 Simple EDI Business Flow:-

1.       Customer creates the Purchase Order (PO) for purchase the items.

2.       Then customer send the PO through mail or fax  to supplier

3.       The supplier receive the PO and valid the PO requirement information

4.       Next supplier Enter/Create the Sales Order  on this System

5.       The supplier send the Sales order acknowledge to customer through fax or mail.

6.       The Supplier shipped the items to the customer .when the items shipped, the supplier arise Invoices to the customer.

7.       The customer makes the payment based on invoice.

Let us imagine, how much information needs to transfer the data and tracked run the business, cost, process times and data errors etc., using Intermediate communication link - EDI performing effective way of business

Organizations that send or receive documents between each other are referred to as "trading partners" in EDI terminology. Trading partners are free to use any method for the transmission of documents. Few of the transmission mediums used are Value Added Network (VAN), Internet/AS and Web EDI.

EDI translation software, popularly known as Translator, validates the partner and checks if the data received meets the standard formats defined before converting it to a desired file format which can be read and imported by the receiver's computer systems (for e.g. ERP).

EDIFACT & X12 are most widely followed EDI standards. The standards prescribe the formats, character sets, and data elements used in the exchange of business documents and forms.

EDI Standard Exchange format:

X12 - The American National Standards Institute (ANSI) chartered the Accredited Standards Committee (ASC) X12 to develop uniform standards for industry electronic exchange of business transactions-electronic data interchange (EDI)

UN/EDIFACT - United Nation Electronic Data Interchange for Administration, Commerce and Transport. UN/EDIFACT is an international EDI standard designed to meet the needs of both government and private industry.

The UN/EDIFACT Working Group (EWG), a permanent working group of the United Nations Centre for Trade Facilitation and Electronic Business (UN/CEFACT), develops and maintains UN/EDIFACT.

Hints: X12 is used in the USA but most of the rest of the world uses the EDIFACT transaction sets.

Components:

There are five process involved in EDI

ü  Sender

ü  Receiver

ü  Language

ü  Content

ü  Medium

In EDI, Sender and Receiver are called as trading partner (like customer and vendors). X12 or EDIFACT standards supply a common language for formatting the information content of common message

Terminology used in EDI: 

Ø  Transaction Set - Business document transmitted between companies (e.g. Purchase Order).

Ø  Data Segment - Units of data used to communicate specific information within a message (e.g. the header).

Ø  Data Element - Specific unit of data within a data segment.

Ø  Trading Partner - Entity such as a customer, supplier, or organization with whom electronic commerce is executed.

Ø  EDI Standards - Agreed to formats for exchanging electronic data (typically developed by representatives of industry verticals).

Ø  Translator - Software application that translates data into or out of a specific format or standard for processing by an ERP system

Oracle EDI e-Commerce Gateway – Functionality

• Define Trading Partner Relationships
• Define Code Conversions
• Customize Flat File Formats (like xml,xls,.csv etc)
• Use Standard Report Submission
• Initiate Extract Process
• Initiate Import Process
• View Extract/Import Process Status

ORDER TO CASH (O2C) Process Flow with Effected Tables in ORACLE EBS R12

LET LOOK the FLOW PART FOR O2C 

• Order  Entry : This is first stage , When the order is entered in the system 
• Order Booking: This is next stage , When the order is booked then the flow status changed from entered into Booked
• Pick Release : Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick then particular sales order 
• Pick confirm / Move order Transaction : Items transferred from Source sub inventory into staging sub inventory
• Ship Confirm: Items are loaded in Truck / Transportation Mode for the delivery to customers 
• Invoice Generation : Invoice is generated
• Close Order 

O2C - TABLE FLOW STATUS 

=> OE_ORDER_HEADERS_ALL - Flow Status Code

1. ENTERED
2. CANCELLED
3. CLOSED
4. BOOKED

=> OE_ORDER_LINES_ALL - Flow Status Code

1. ENTERED
2. REPRICE_COMPLETE
3. CANCELLED
4. AWAITING_SHIPPING
5. CLOSED
6. BOOKED
7. INVOICE_HOLD
8. AWAITING_FULFILLMENT
9. REPRICE_PRICING_ERROR

=> WSH_DELIVERY_DETAILS - Released Status

1. B:Backordered
2. C:Shipped 
3. D:Cancelled 
4. N:Not ready to release 
5. R:Ready to release 
6. S:Released to warehouse 
7. X:Not Applicable 
8. Y:Staged

Step 1; Creating / Entered New Sales Order 

Select * from OE_ORDER_HEADERS_ALL where  ORDER_NUMBER = '830001659'; 

select * from OE_ORDER_HEADERS_ALL where Header_ID = 21009209;

select Flow_status_code from OE_order_Headers_all 

                                where Header_ID = 21009209; -->ENTERED STATUS

select Flow_status_code from OE_ORDER_LINES_ALL 

                               where Header_ID = oe_header_id -->ENTERED 

Step 2; Booked the Sales Order 

Once Order is booked or confirmed then 

select Flow_status_code from OE_order_Headers_all 

                                where Header_ID = 21009209; -->BOOKED STATUS

select Flow_status_code from OE_ORDER_LINES_ALL 

                               where Header_ID = oe_header_id -->AWAITING_SHIPPING

select * from WSH_DELIVERY_DETAILS

                                 where source_header_ID =   OE_HEADER_ID  

select released_status from WSH_DELIVERY_DETAILS

                              where source_header_ID =   OE_HEADER_ID  --> R - READY To RELEASE

Step 3; Release Sales order / Pick Release , Pick Confirm (Staging Location)

Once Release order and pick release some concurrent program trigger out 

1. Pick Selection List Generation 

2. Pick Slip Report ( We can get Move Order Number) 

Select * from MTL_TXN_REQUEST_HEADERS 

            where Request_Number= '<<Move Oder Number(getting from PICKSLIP Report)>>';

Select * from MTL_TXN_REQUEST_LINES where HEADER_ID = '<<Header_ID>>';

Select * from WSH_DELIVERY_DETAILS 

               where Released_Status = 'S';  -- Released to warehouse (Pick Release)

Select * from WSH_DELIVERY_DETAILS

           where Released_Status = 'Y'; -- Staging 

(Pick Confirm , Line has been released to inventory for Processing)

Select * from WSH_picking_batches; --- After batch is created for pick release.

Select * from MTL_Reservations 

             where Inventory_Item_ID = '<<MTL_ITEM_ID>>'; -- Soft move , Not for physical Move

select * From WSH_NEW_DELIVERIES 

             where Deliverd_ID = '<<Delivery_ID- wsh_delivery_assignments>>';

Step 4; Ship Confirm

once ship confirmation done, then it will be triggered out 5 concurrent programs 

1. Bill of Landing , 

2. packing Slip Report , 

3. Commerical Invoice ,

4. Vechile Load Sheet , 

5. Interface Trip stop 

Select * from WSH_DELIVERY_DETAILS where Released_Status = 'C';  -- Shipped

Select flow_status_Code from OE_ORDER_LINES_ALL 

                                       where header_ID = 'Oe_header_ID';  --> Shipped Status

Select * FROM MTL_REVERSATIONS 

               WHERE Inventory_Item_ID = 'ITEM_ID'; --> will be FREEZE

Step 5 : Auto Invoice: 

After Successful completed the respective CC program then run the work flow background process

to Move AR module and Generate Invoice 

 -- >>  RUN WorkFlow - 'WORK FLOW BACKGROUND PROCESS ' INTERFACE 

 --.>>  Auto Invoice Program 

  select * from RA_Interface_lines_all 

                 where Interface_LINE_ATTRIBUTE1 ='ORDER_NUMBER';

  Select * from RA_Interface_Distributions_all 

                  where Interface_Line_ID  ='LINE ID'; 

Step 6 : Account Receivable:

   Select * FROM RA_CUSTOMER_TRX_ALL 

                   Where TRX_NUMBER = 'Invoice_number';

   Select * FROM RA_CUSTOMER_TRX_lines_All 

                   where Customer_Trx_ID = '';

Step 7 : General Ledger:

  Select * From GL_JE_BATCHES Where Name ='Receivables A 155 etc';

  Select * From GL_JE_Headers where JE_Batch_ID ='';

  Select * from GL_JE_LInes where JE_Header_ID = '';

SAMPLE QUERY FOR JOIN BETWEEN  OM, WSH, AR TABLES

SELECT ooh.order_number ,

  ool.line_id ,

  ool.ordered_quantity ,

  ool.shipped_quantity ,

  ool.invoiced_quantity ,

  wdd.delivery_detail_id ,

  wnd.delivery_id ,

  rctl.interface_line_attribute1 ,

  rctl.interface_line_attribute3 ,

  rctl.interface_line_attribute6 ,

  rct.org_id ,

  rct.creation_date ,

  trx_number ,

  rctl.quantity_ordered ,

  rct.interface_header_context

FROM oe_order_headers_all ooh ,

  oe_order_lines_all ool ,

  wsh_delivery_details wdd ,

  wsh_new_deliveries wnd ,

  wsh_delivery_assignments wda ,

  ra_customer_trx_all rct ,

  ra_customer_trx_lines_all rctl

WHERE ooh.header_Id               =ool.header_id

AND wdd.source_header_id          =ooh.header_id

AND wdd.delivery_detail_Id        =wda.delivery_detail_id

AND wda.delivery_id               =wnd.delivery_id

AND rctl.interface_line_attribute1=TO_CHAR(ooh.order_number)

AND rctl.interface_line_attribute6=TO_CHAR(ool.line_id)

AND rctl.interface_line_attribute3=TO_CHAR(wnd.delivery_id)

AND rctl.customer_trx_id          =rct.customer_trx_id;

--      AND rct.interface_header_context='ORDER ENTRY'

/

Example query linking MTL_MATERIAL_TRANSACTIONS to the move order:

SELECT mmt.transaction_id,

  tol.organization_id,

  toh.request_number,

  toh.header_id,

  tol.line_number,

  tol.line_id,

  tol.inventory_item_id,

  toh.description,

  toh.move_order_type,

  tol.line_status,

  tol.quantity,

  tol.quantity_delivered,

  tol.quantity_detailed

FROM mtl_txn_request_headers toh,

  mtl_txn_request_lines tol,

  mtl_material_transactions mmt

WHERE toh.header_id     = tol.header_id

AND toh.organization_id = tol.organization_id

AND tol.line_id         = mmt.move_order_line_id

AND toh.request_number  = '&EnterMONumber' 

/

SQL Statement to find out the Drop Ship SO and corresponding Requistion and PO details along with their status

select ooh.order_number

       ,ool.ordered_item

       ,ool.ordered_quantity

       ,ooh.flow_status_code header_status                   

       ,ool.flow_status_code line_status

       ,prha.segment1 requisition

       ,poh.segment1 po_number

       ,poh.closed_code po_status

       ,pll.quantity

       ,pll.quantity_received

       ,pll.closed_code po_shipment_status

fromapps.oe_order_headers_all                   ooh

    ,apps.oe_order_lines_all                     ool

    ,apps.oe_drop_ship_sources                   odss

    ,apps.po_requisition_headers_all             prha

    ,apps.po_headers_all                         poh

    ,apps.po_lines_all                           pol

    ,apps.po_line_locations_all                  pll

              

whereool.header_id = ooh.header_id

and   odss.header_id = ooh.header_id

and   odss.line_id = ool.line_id

and   prha.requisition_header_id = odss.requisition_header_id

and   poh.po_header_id = odss.po_header_id

and   pol.po_line_id = odss.po_line_id

and   pol.po_header_id = poh.po_header_id

and   pll.po_line_id = pol.po_line_id

and   ooh.order_number = ‘89899’;

Register Custom Tables in Oracle Apps

Register Custom Table In Oracle Applications

All you technical people out there very well know the purpose of creating custom table. Its basically when we need to store data before pulling into standard table (In case of inbound interface) or putting extract of data to share with external entity (In case of outbound interface). All these activities can be accomplished without registering custom table in oracle apps.

If you have a need to use these custom tables in standard functionality in frond end like using in Alerts / Audits, you must register them. Otherwise your custom table will not be visible in front end.

Lets start this by creating a sample custom table.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as c_apps
 
SQL> 
SQL> CREATE TABLE C_FND_INTERFACES
  2  ( INTERFACE_ID            NUMBER               NOT NULL PRIMARY KEY,
  3    SERVICE_CHANNEL         VARCHAR2(64 BYTE)    NOT NULL,
  4    SOURCE                  VARCHAR2(64 BYTE) NOT NULL,
  5    ATTRIBUTE1              VARCHAR2(2000 BYTE),
  6    ATTRIBUTE2              VARCHAR2(2000 BYTE),  7    ATTRIBUTE3              VARCHAR2(2000 BYTE),
  8    ATTRIBUTE4              VARCHAR2(2000 BYTE),
  9    ATTRIBUTE5              VARCHAR2(2000 BYTE),
 10    ATTRIBUTE6              VARCHAR2(2000 BYTE),
 11    ATTRIBUTE7              VARCHAR2(2000 BYTE),
 12    ATTRIBUTE8              VARCHAR2(2000 BYTE),
 13    ATTRIBUTE9              VARCHAR2(2000 BYTE),
 14    ATTRIBUTE10             VARCHAR2(2000 BYTE),
 15    STATUS                  VARCHAR2(50 BYTE)    NOT NULL,
 16    ERROR_MESSAGE           VARCHAR2(2000 BYTE),
 17    CREATED_BY              NUMBER               NOT NULL,
 18    CREATION_DATE           DATE                 NOT NULL,
 19    LAST_UPDATED_BY         NUMBER               NOT NULL,
 20    LAST_UPDATE_DATE        DATE                 NOT NULL,
 21    REQUEST_ID              NUMBER,
 22    BATCH_ID                NUMBER
 23  )
 24  TABLESPACE C_APPS
 25  PCTUSED    0
 26  PCTFREE    10
 27  INITRANS   1
 28  MAXTRANS   255
 29  STORAGE    (
 30              INITIAL          64K
 31              NEXT             1M
 32              MINEXTENTS       1
 33              MAXEXTENTS       UNLIMITED
 34              PCTINCREASE      0
 35              BUFFER_POOL      DEFAULT
 36             )
 37  LOGGING
 38  NOCACHE
 39  NOPARALLEL
 40  /
 
Table created
 
SQL> 

You would have noticed that table is created under custom schema. Make sure you create synonym under APPS schema.

There are 4 steps involved in registering table in Oracle Applications

#1 - Register Table

ad_dd.register_table 
(p_appl_short_name IN VARCHAR2, -- Application Short Name
 p_table_name      IN VARCHAR2, -- Table Name
 p_table_type      IN VARCHAR2, -- Table Type. Use 'T' if transaction table. 'S' for seed data table.
 p_next_extent     IN NUMBER DEFAULT 512,
 p_pct_free        IN NUMBER DEFAULT 10, -- % of space in each of the table block reserved for future updates(1-99)
 p_pct_used        IN NUMBER DEFAULT 70) -- MINimum percentage of used space IN each data block of the table (1-99)
                                       ; -- The sum of p_pct_free and p_pct_used must be less than 100

Sample Script

EXECUTE ad_dd.register_table 

(p_appl_short_name =>  'C_APPS',
 p_table_name      =>  'C_FND_INTERFACES',
 p_table_type      =>  'T',
 p_next_extent     =>  512,
 p_pct_free        =>  10,
 p_pct_used        =>  70);
                             

#2 - Register Column

ad_dd.register_column

(p_appl_short_name IN VARCHAR2, -- Application Short Name
 p_table_name      IN VARCHAR2, -- Table Name
 p_column_name     IN VARCHAR2, -- Column Name
 p_column_seq      IN NUMBER,   -- Sequence NUMBER of Column in table
 p_column_type     IN VARCHAR2, -- Column Type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
 p_column_width    IN NUMBER,   -- Colum Size,Can use 9 for DATE columns, 38 for NUMBER columns
 p_nullable        IN VARCHAR2, -- Use 'N' if mandatory or 'Y' if it allows null values
 p_translate       IN VARCHAR2, -- 'N' if the values are not translated
 p_precision       IN NUMBER DEFAULT NULL, -- NUMBER of digits in a NUMBER    
 p_scale           IN NUMBER DEFAULT NULL);-- NUMBER of digits to the right of the decimal point

Sample Script

EXECUTE ad_dd.register_column
(p_appl_short_name =>   'C_APPS'
 p_table_name      =>   'C_FND_INTERFACES'
 p_column_name     =>   'interface_id'
 p_column_seq      =>   'c_fnd_interfaces_seq'
 p_column_type     =>   NUMBER
 p_column_width    =>   10
 p_nullable        =>   'N'
 p_translate       =>   'N'
 p_precision       =>   NULL
 p_scale           =>   NULL);

#3 - Register Primary Key

ad_dd.register_primary_key
(p_appl_short_name IN VARCHAR2,  -- Application Short Name
 p_key_name        IN VARCHAR2,  -- Primary Key Name
 p_table_name      IN VARCHAR2,  -- Table Name
 p_description     IN VARCHAR2,  -- Primary key Description
 p_key_type        IN VARCHAR2,  -- (D/S)Developer/Surrogate,DEFAULTs to null
 p_audit_flag      IN VARCHAR2,  -- (Y/N)DEFAULTs to null
 p_enabled_flag    IN VARCHAR2); -- (Y/N)DEFAULTs to null

Sample Script

EXECUTE ad_dd.register_primary_key
(p_appl_short_name =>   'C_APPS',
 p_key_name        =>   'INTERFACE_ID_PK',
 p_table_name      =>   'C_FND_INTERFACES',
 p_description     =>   'Interface ID Primary Key',
 p_key_type        =>   'S',
 p_audit_flag      =>   'Y',
 p_enabled_flag    =>   'Y');

#4 - Register Primary Key Column

ad_dd.register_primary_key_column
(p_appl_short_name IN VARCHAR2,
 p_key_name        IN VARCHAR2,
 p_tab_name        IN VARCHAR2,
 p_col_name        IN VARCHAR2,
 p_col_sequence    IN NUMBER);

Sample Script

EXECUTE ad_dd.register_primary_key_column
(p_appl_short_name =>  'C_APPS',
 p_key_name        =>  'INTERFACE_ID_PK',
 p_tab_name        =>  'C_FND_INTERFACES',
 p_col_name        =>  'INTERFACE_ID',
 p_col_sequence    =>  '1');

Let’s go and validate in application.

Navigate to Application Developer –> Application –> Database –> Table
F11 –> Enter your table_name ‘C_FND_INTERFACES’ –> Ctrl + F11.