Using the below query we can find the intransit time and other details.
SELECT mism.ship_method f01
, mism.intransit_time f02
, mism.default_flag f03
, mism.from_organization_id f04
, ood.organization_name f05
, mism.to_organization_id f06
, ood1.organization_name f07
, mism.from_location_id f08
, mism.to_location_id f09
, mism.last_update_date f10
, mism.last_updated_by f11
, mism.creation_date f12
, mism.created_by f13
, mism.last_update_login f14
, mism.attribute_category f15
, mism.to_region_id f16
, mism.destination_type f17
, 'CREATE' f18
FROM mtl_interorg_ship_methods mism
, org_organization_definitions ood
, org_organization_definitions ood1
WHERE mism.from_organization_id = ood.organization_id
AND mism.to_organization_id = ood1.organization_id
AND NOT EXISTS(
SELECT 'X'
FROM xxcofi_time_extract_tbl xxtime
WHERE mism.from_organization_id = xxtime.from_organization_ID
AND mism.to_organization_id = xxtime.to_organization_id
AND mism.intransit_time = xxtime.intransit_time)
-- To exclude the records which alre already processed in staging table
UNION
SELECT DISTINCT NULL
, 1
, NULL
, TO_NUMBER(vendor_site_code) f05
, vendor_site_code f05
, organization_id F06
, substr(dest_org_code,1,4) f07
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'CREATE'
FROM xxcofi_sourcing_dc_to_vendor xxsource
WHERE NOT EXISTS(
SELECT 'X'
FROM xxcofi_time_extract_tbl xxtime
WHERE xxsource.vendor_site_code = xxtime.from_organization_name
AND xxsource.organization_id = xxtime.to_organization_id
AND 1 = xxtime.intransit_time);
-- To exclude the records which alre already processed in staging table
Monday, August 23, 2010
Installbase Creation
To create install base for an item you need to run the Install base creation program.
EX: "COFI IB Creation"
For cross checking we can use the below query to find whether install base is created for a particular sales order.
select * from csi.csi_item_instances
where last_oe_order_line_id IN (
select line_id from oe_order_lines_all
where header_id IN (
select header_id from oe_order_headers_all
where order_number = '1560102'));
With the following query, we can findout if there is any error with the transaction.
-------------------------------------------------------------------------------------
select * from csi.CSI_TXN_ERRORS where TRANSACTION_ID IN (SELECT transaction_id--, trx_source_line_id
FROM mtl_material_transactions
WHERE trx_source_line_id IN (select line_id from oe_order_lines_all
where header_id IN (select header_id from oe_order_headers_all
where order_number = '1560102'))
AND transaction_type_id = 33)
If there is no error for this transaction then it may be in the Interface table. TO put the record from the Interface table to the base table. We can run the
following PRogram
Concurrent PRogram Name: Resubmit Interface Process
responsibility--> cofi install base admin
If there is any error in the error table then we need to clear the log and we need to run the above program.
EX: "COFI IB Creation"
For cross checking we can use the below query to find whether install base is created for a particular sales order.
select * from csi.csi_item_instances
where last_oe_order_line_id IN (
select line_id from oe_order_lines_all
where header_id IN (
select header_id from oe_order_headers_all
where order_number = '1560102'));
With the following query, we can findout if there is any error with the transaction.
-------------------------------------------------------------------------------------
select * from csi.CSI_TXN_ERRORS where TRANSACTION_ID IN (SELECT transaction_id--, trx_source_line_id
FROM mtl_material_transactions
WHERE trx_source_line_id IN (select line_id from oe_order_lines_all
where header_id IN (select header_id from oe_order_headers_all
where order_number = '1560102'))
AND transaction_type_id = 33)
If there is no error for this transaction then it may be in the Interface table. TO put the record from the Interface table to the base table. We can run the
following PRogram
Concurrent PRogram Name: Resubmit Interface Process
responsibility--> cofi install base admin
If there is any error in the error table then we need to clear the log and we need to run the above program.
Wednesday, August 18, 2010
Workflow Tables and Queries
Contains all the table information related to Oracle Workflows and queries joining these tables.
Query1: Accepts Workflow itemtype / shortname as input parameter and will all the activities involved along with the status and user name to whom the current activity is assigned.
Query2: Accepts workflow itemtype and activity as input variables and the results will provide the time frame explaining from how long the activity is pending along with the username whose action is req
MULTIPLE INSTANCES OF COPY TO CLIPBOARD DEMO
WORKFLOW TABLES
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS
SELECT * FROM WF_USER_ROLES
SELECT * FROM WF_ROLES
SELECT * FROM WF_ITEMS
SELECT * FROM WF_ITEM_ATTRIBUTES
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES
SELECT * FROM WF_ITEM_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITIES
SELECT * FROM WF_ACTIVITIES_TL
SELECT * FROM WF_ACTIVITY_ATTRIBUTES
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITY_TRANSITIONS
SELECT * FROM WF_DEFERRED--WF_CONTROL
SELECT * FROM WF_ACTIVITY_ATTR_VALUES
WHERE NAME LIKE '%MASTER%'
AND PROCESS_ACTIVITY_ID
IN(
SELECT *-- PROCESS_ACTIVITY
FROM WF_ITEM_ACTIVITY_STATUSES
WHERE ITEM_TYPE = 'ERP'
AND ITEM_KEY ='63865'
)
SELECT * FROM WF_ITEM_TYPES
SELECT * FROM WF_LOOKUPS_TL
SELECT * FROM WF_NOTIFICATIONS
WHERE MESSAGE_TYPE ='ERP'
ORDER BY BEGIN_DATE DESC
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES
SELECT * FROM WF_MESSAGES
SELECT * FROM WF_MESSAGES_TL
SELECT * FROM WF_MESSAGE_ATTRIBUTES
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL
SELECT * FROM WF_ETS
SELECT * FROM WF_PROCESS_ACTIVITIES
Click here to copy script to clipboard
LIST OF ACTIVITIES FOR AN ITEMTYPE
SELECT A.ITEM_KEY,
B.ACTIVITY_NAME,
A.ACTIVITY_STATUS,
A.ACTIVITY_RESULT_CODE,
A.ASSIGNED_USER,
A.BEGIN_DATE,
A.END_DATE
FROM WF_ITEM_ACTIVITY_STATUSES A,
WF_PROCESS_ACTIVITIES B
WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)
AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
AND A.ITEM_TYPE = 'ERP'
AND A.ITEM_KEY = 64077
AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
Click here to copy script to clipboard
TO FIND FROM HOW MANY DAYS AN ACTIVITY IS PENDING
SELECT B.ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,
COUNT(B.ACTIVITY_NAME) TOTAL_PENDING
FROM WF_ITEM_ACTIVITY_STATUSES A,
WF_PROCESS_ACTIVITIES B
WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID
AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
AND A.ITEM_TYPE = 'ERP'
--AND A.ITEM_KEY = 1131
AND END_DATE IS NULL
AND ACTIVITY_STATUS != 'ERROR'
AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
GROUP BY ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)
ORDER BY ACTIVITY_NAME,
PENDING_FROM_NO_OF_DAYS
Click here to copy script to clipboard
LIST OF ACTIVITIES THAT ARE PENDING FROM N DAYS
SELECT SUM(TOTAL_PENDING) PENDING_LESS_THAN_5DAYS
FROM
(SELECT B.ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,
COUNT(B.ACTIVITY_NAME) TOTAL_PENDING
FROM WF_ITEM_ACTIVITY_STATUSES A,
WF_PROCESS_ACTIVITIES B
WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID
AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
AND A.ITEM_TYPE = 'ERP'
--AND A.ITEM_KEY = 1131
AND END_DATE IS NULL
AND ACTIVITY_STATUS != 'ERROR'
AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
GROUP BY ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)
ORDER BY ACTIVITY_NAME,
PENDING_FROM_NO_OF_DAYS ) FIVE_DAYS
WHERE FIVE_DAYS.PENDING_FROM_NO_OF_DAYS < 5
Query1: Accepts Workflow itemtype / shortname as input parameter and will all the activities involved along with the status and user name to whom the current activity is assigned.
Query2: Accepts workflow itemtype and activity as input variables and the results will provide the time frame explaining from how long the activity is pending along with the username whose action is req
MULTIPLE INSTANCES OF COPY TO CLIPBOARD DEMO
WORKFLOW TABLES
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS
SELECT * FROM WF_USER_ROLES
SELECT * FROM WF_ROLES
SELECT * FROM WF_ITEMS
SELECT * FROM WF_ITEM_ATTRIBUTES
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES
SELECT * FROM WF_ITEM_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITIES
SELECT * FROM WF_ACTIVITIES_TL
SELECT * FROM WF_ACTIVITY_ATTRIBUTES
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITY_TRANSITIONS
SELECT * FROM WF_DEFERRED--WF_CONTROL
SELECT * FROM WF_ACTIVITY_ATTR_VALUES
WHERE NAME LIKE '%MASTER%'
AND PROCESS_ACTIVITY_ID
IN(
SELECT *-- PROCESS_ACTIVITY
FROM WF_ITEM_ACTIVITY_STATUSES
WHERE ITEM_TYPE = 'ERP'
AND ITEM_KEY ='63865'
)
SELECT * FROM WF_ITEM_TYPES
SELECT * FROM WF_LOOKUPS_TL
SELECT * FROM WF_NOTIFICATIONS
WHERE MESSAGE_TYPE ='ERP'
ORDER BY BEGIN_DATE DESC
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES
SELECT * FROM WF_MESSAGES
SELECT * FROM WF_MESSAGES_TL
SELECT * FROM WF_MESSAGE_ATTRIBUTES
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL
SELECT * FROM WF_ETS
SELECT * FROM WF_PROCESS_ACTIVITIES
Click here to copy script to clipboard
LIST OF ACTIVITIES FOR AN ITEMTYPE
SELECT A.ITEM_KEY,
B.ACTIVITY_NAME,
A.ACTIVITY_STATUS,
A.ACTIVITY_RESULT_CODE,
A.ASSIGNED_USER,
A.BEGIN_DATE,
A.END_DATE
FROM WF_ITEM_ACTIVITY_STATUSES A,
WF_PROCESS_ACTIVITIES B
WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)
AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
AND A.ITEM_TYPE = 'ERP'
AND A.ITEM_KEY = 64077
AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
Click here to copy script to clipboard
TO FIND FROM HOW MANY DAYS AN ACTIVITY IS PENDING
SELECT B.ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,
COUNT(B.ACTIVITY_NAME) TOTAL_PENDING
FROM WF_ITEM_ACTIVITY_STATUSES A,
WF_PROCESS_ACTIVITIES B
WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID
AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
AND A.ITEM_TYPE = 'ERP'
--AND A.ITEM_KEY = 1131
AND END_DATE IS NULL
AND ACTIVITY_STATUS != 'ERROR'
AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
GROUP BY ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)
ORDER BY ACTIVITY_NAME,
PENDING_FROM_NO_OF_DAYS
Click here to copy script to clipboard
LIST OF ACTIVITIES THAT ARE PENDING FROM N DAYS
SELECT SUM(TOTAL_PENDING) PENDING_LESS_THAN_5DAYS
FROM
(SELECT B.ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,
COUNT(B.ACTIVITY_NAME) TOTAL_PENDING
FROM WF_ITEM_ACTIVITY_STATUSES A,
WF_PROCESS_ACTIVITIES B
WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID
AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
AND A.ITEM_TYPE = 'ERP'
--AND A.ITEM_KEY = 1131
AND END_DATE IS NULL
AND ACTIVITY_STATUS != 'ERROR'
AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
GROUP BY ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)
ORDER BY ACTIVITY_NAME,
PENDING_FROM_NO_OF_DAYS ) FIVE_DAYS
WHERE FIVE_DAYS.PENDING_FROM_NO_OF_DAYS < 5
List of Approvers for a Purchase Order in Position Hierarchy
SCRIPT TO GET POSITION_STRUCTURE_ID AND BUSINESS_GROUP_ID FOR A GIVEN HIERARCHY NAME
SELECT position_structure_id, business_group_id
FROM per_position_structures_v
WHERE NAME = '&POSITION HIERARCHY NAME'
SCRIPT TO GET ALL THE APPROVERS, THEIR POSITIONS, APPROVAL HIERARCHY PATH, APPROVAL LIMITS IN A POSITION HIERARCHY
This Script takes position_structure_id and business_group_id obtained from the above query and top position id for a given position hierarchy as inputs.
SELECT h.POSITION, h.PATH, ass.position_holder, al.doc_type,
al.approval_group, al.OBJECT, al.rule,al.amount_limit, al.low_value, al.high_value
FROM --Getting the Approval Limits
(SELECT psc.position_id pos_id, pcf.control_function_name doc_type,
pcg.control_group_name approval_group, pcr.object_code OBJECT,
pcr.rule_type_code rule, amount_limit,
segment1_low
|| '-'
|| segment2_low
|| '-'
|| segment3_low
|| '-'
|| segment4_low
|| '-'
|| segment5_low low_value,
segment1_high
|| '-'
|| segment2_high
|| '-'
|| segment3_high
|| '-'
|| segment4_high
|| '-'
|| segment5_high high_value
FROM apps.po_position_controls_all psc,
apps.po_control_groups_all pcg,
apps.po_control_rules pcr,
apps.po_control_functions pcf
WHERE 1 = 1
AND psc.control_function_id = pcf.control_function_id
AND psc.org_id = 95
AND psc.control_group_id = pcg.control_group_id
AND pcg.control_group_id = pcr.control_group_id) al,
-- Getting approvers/users for a position in the heirarchy
(SELECT he.full_name position_holder, pa.position_id pos_id
FROM apps.per_all_assignments_f pa, apps.hr_employees he
WHERE pa.business_group_id = 81
AND pa.effective_end_date = '31-DEC-4712'
AND pa.person_id = he.employee_id) ass,
-- Getting the Postion Heirarchy
(SELECT pp.NAME POSITION, pse.parent_position_id position_id,
pp.NAME PATH
FROM per_pos_structure_elements_v pse, per_positions pp
WHERE pse.business_group_id = 81 --business_group_id for SOLO CUP POSITION HIERARCHY
AND pse.pos_structure_version_id = 61 --pos_structure_version_id for SOLO CUP POSITION HIERARCHY
AND pse.parent_position_id = 98 --Top position in SOLO CUP POSITION HIERARCHY
AND pse.parent_position_id = pp.position_id
UNION
SELECT DISTINCT has.NAME POSITION, has.position_id position_id,
(SELECT NAME
FROM per_positions
WHERE position_id = 98)
|| SYS_CONNECT_BY_PATH (has.NAME, '/') PATH
FROM (SELECT NAME, position_id
FROM apps.hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) has,
per_pos_structure_elements pse
WHERE pse.business_group_id = 81 --business_group_id for SOLO CUP POSITION HIERARCHY
AND has.position_id = pse.subordinate_position_id
AND pse.pos_structure_version_id = 61 --pos_structure_version_id for SOLO CUP POSITION HIERARCHY
START WITH pse.parent_position_id = 98 --Top position in SOLO CUP POSITION HIERARCHY
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id
ORDER BY PATH) h
WHERE al.pos_id (+)= h.position_id
AND ass.pos_id(+) = h.position_id
order by path
SELECT position_structure_id, business_group_id
FROM per_position_structures_v
WHERE NAME = '&POSITION HIERARCHY NAME'
SCRIPT TO GET ALL THE APPROVERS, THEIR POSITIONS, APPROVAL HIERARCHY PATH, APPROVAL LIMITS IN A POSITION HIERARCHY
This Script takes position_structure_id and business_group_id obtained from the above query and top position id for a given position hierarchy as inputs.
SELECT h.POSITION, h.PATH, ass.position_holder, al.doc_type,
al.approval_group, al.OBJECT, al.rule,al.amount_limit, al.low_value, al.high_value
FROM --Getting the Approval Limits
(SELECT psc.position_id pos_id, pcf.control_function_name doc_type,
pcg.control_group_name approval_group, pcr.object_code OBJECT,
pcr.rule_type_code rule, amount_limit,
segment1_low
|| '-'
|| segment2_low
|| '-'
|| segment3_low
|| '-'
|| segment4_low
|| '-'
|| segment5_low low_value,
segment1_high
|| '-'
|| segment2_high
|| '-'
|| segment3_high
|| '-'
|| segment4_high
|| '-'
|| segment5_high high_value
FROM apps.po_position_controls_all psc,
apps.po_control_groups_all pcg,
apps.po_control_rules pcr,
apps.po_control_functions pcf
WHERE 1 = 1
AND psc.control_function_id = pcf.control_function_id
AND psc.org_id = 95
AND psc.control_group_id = pcg.control_group_id
AND pcg.control_group_id = pcr.control_group_id) al,
-- Getting approvers/users for a position in the heirarchy
(SELECT he.full_name position_holder, pa.position_id pos_id
FROM apps.per_all_assignments_f pa, apps.hr_employees he
WHERE pa.business_group_id = 81
AND pa.effective_end_date = '31-DEC-4712'
AND pa.person_id = he.employee_id) ass,
-- Getting the Postion Heirarchy
(SELECT pp.NAME POSITION, pse.parent_position_id position_id,
pp.NAME PATH
FROM per_pos_structure_elements_v pse, per_positions pp
WHERE pse.business_group_id = 81 --business_group_id for SOLO CUP POSITION HIERARCHY
AND pse.pos_structure_version_id = 61 --pos_structure_version_id for SOLO CUP POSITION HIERARCHY
AND pse.parent_position_id = 98 --Top position in SOLO CUP POSITION HIERARCHY
AND pse.parent_position_id = pp.position_id
UNION
SELECT DISTINCT has.NAME POSITION, has.position_id position_id,
(SELECT NAME
FROM per_positions
WHERE position_id = 98)
|| SYS_CONNECT_BY_PATH (has.NAME, '/') PATH
FROM (SELECT NAME, position_id
FROM apps.hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) has,
per_pos_structure_elements pse
WHERE pse.business_group_id = 81 --business_group_id for SOLO CUP POSITION HIERARCHY
AND has.position_id = pse.subordinate_position_id
AND pse.pos_structure_version_id = 61 --pos_structure_version_id for SOLO CUP POSITION HIERARCHY
START WITH pse.parent_position_id = 98 --Top position in SOLO CUP POSITION HIERARCHY
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id
ORDER BY PATH) h
WHERE al.pos_id (+)= h.position_id
AND ass.pos_id(+) = h.position_id
order by path
Conversions and Interfaces
Interfaces and Conversions in Oracle Applications
Overview:
Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications. This article briefs you about some of the major interface programs.
Conversion/Interface Strategy:
1. Data Mapping
During the data mapping process, list of all the data sets and data elements that will need to be moved into the Oracle tables as part of conversion are identified. Data mapping tables are prepared as part of this activity that show what are the data elements that are needed by the target system to meet the business requirements and from where they will be extracted in the old system.
2. Download Programs
After the conversion data mapping is complete, download programs are developed that are used to extract the identified conversion data elements from the current systems in the form of an ASCII flat file. The structure of the flat file must match the structure of the Oracle standard interface tables. These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.
3. Upload Program
Once the data has been extracted to a flat file, it is then moved to the target file system and the data from the file is loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities. Then programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables.
4. Interface Program
Once the interface tables are populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. The interface programs validate the data, derive and assign the default values and ultimately populate the production base tables.
Interface/Conversion examples with details:
The below list of interfaces/conversions are covered in this section. Details like pre-requisites required, interface tables, interface program, base tables, validations that need to be performed after inserting the details into the interface tables and required columns that need to be populated in the interface table are discussed for each interface.
· Order Import Interface (Sales Order Conversion)
· Item import (Item conversion)
· Inventory On-hand quantity Interface
· Customer conversion
· Auto Invoice Interface
· AR Receipts
· Lockbox Interface
· AP Invoices
· Vendor
· Purchase Orders
· Requisition
· Receiving
· Journal import
· Budget import
· Daily Conversion Rates
* Order Import Interface (Sales Order Conversion)
Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.
Pre-requisites:
Order Type
Line Type
Items
Customers
Ship Method/ Freight Carrier
Sales Person
Sales Territories
Customer Order Holds
Sub Inventory/ Locations
On hand Quantity
Interface tables:
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_ORDER_CUST_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL
Base tables:
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
Pricing tables: QP_PRICING_ATTRIBUTES
Concurrent Program:
Order Import
Validations:
Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
Check for sales_rep_id. Should exist for a booked order.
Ordered_date should exist (header level)
Delivery_lead_time should exist (line level)
Earliest_acceptable_date should exist.
Freight_terms should exist
Notes:
During import of orders, shipping tables are not populated.
If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables are HZ_PARTIES, HZ_LOCATIONS.
Orders can be categorized based on their status:
1. Entered orders 2. Booked orders 3. Closed orders
Order Import API OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER can also be used to import orders.
Some important columns that need to populated in the interface tables:
OE_HEADERS_IFACE_ALL:
ORIG_SYS_DOCUMENT_REF
ORDER_SOURCE
CONVERSION_RATE
ORG_ID
ORDER_TYPE_ID
PRICE_LIST
SOLD_FROM_ORG_ID
SOLD_TO_ORG_ID
SHIP_TO_ORG_ID
SHIP_FROM_ORG_ID
CUSTOMER_NAME
INVOICE_TO_ORG_ID
OPERATION_CODE
OE_LINES_IFACE_ALL
ORDER_SOURCE_ID
ORIG_SYS_DOCUMENT_REF
ORIG_SYS_LINE_REF
ORIG_SYS_SHIPMENT_REF
INVENTORY_ITEM_ID
LINK_TO_LINE_REF
REQUEST_DATE
DELIVERY_LEAD_TIME
DELIVERY_ID
ORDERED_QUANTITY
ORDER_QUANTITY_UOM
SHIPPING_QUANTITY
PRICING_QUANTITY
PRICING_QUANTITY_UOM
SOLD_FROM_ORG_ID
SOLD_TO_ORG_ID
INVOICE_TO_ ORG_ID
SHIP_TO_ORG_ID
PRICE_LIST_ID
PAYMENT_TERM_ID
§ Item import (Item conversion)
The Item Interface lets you import items into Oracle Inventory.
Pre-requisites:
Creating an Organization
Code Combinations
Templates
Defining Item Status Codes
Defining Item Types
Interface tables:
MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
MTL_INTERFACE_ERRORS (View errors after import)
Concurrent Program:
Item import
In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.
Base Tables:
MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES
Validations:
Check for valid item type.
Check for valid part_id/segment of the source table.
Validate part_id/segment1 for master org.
Validate and translate template id of the source table.
Check for valid template id. (Attributes are already set for items, default attributes for that template, i.e., purchasable, stockable, etc )
Check for valid item status.
Validate primary uom of the source table.
Validate attribute values.
Validate other UOMs of the source table.
Check for unique item type. Discard the item, if part has non-unique item type.
Check for description, inv_um uniqueness
Validate organization id.
Load master records and category records only if all
Load child record if no error found.
Some important columns that need to populated in the interface tables:
MTL_SYSTEM_ITEMS_INTERFACE:
PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete, 3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded)
TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
SET_PROCESS_ID = 1
ORGANIZATION_ID
DESCRIPTION
ITEM_NUMBER and/or SEGMENT (n)
MATERIAL_COST
REVISION
TEMPLATE_ID
SUMMARY_FLAG
ENABLED_FLAG
PURCHASING_ITEM_FLAG
SALES_ACCOUNT (defaulted from
MTL_PARAMETERS.SALES_ACCOUNT)
COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)
MTL_ITEM_CATEGORIES_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER.
ORGANIZATION_ID or ORGANIZATION_CODE or both.
TRANSACTION_TYPE = 'CREATE' ('UPDATE' or 'DELETE' is not possible through Item Import).
CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
CATEGORY_ID or CATEGORY_NAME or both.
PROCESS_FLAG = 1
SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if you are importing item and category assignment together)
MTL_ITEM_REVISIONS_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the
ORGANIZATION_ID or ORGANIZATION_CODE or both
REVISION
CHANGE_NOTICE
ECN_INITIATION_DATE
IMPLEMENTATION_DATE
IMPLEMENTED_SERIAL_NUMBER
EFFECTIVITY_DATE
ATTRIBUTE_CATEGORY
ATTRIBUTEn
REVISED_ITEM_SEQUENCE_ID
DESCRIPTION
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.
§ Inventory On-hand quantity Interface
This interface lets you import the on hand inventory into Oracle.
Interface tables:
MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)
Concurrent Program:
Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.
Base Tables:
MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS
Validations:
Validate organization_id
Check if item is assigned to organization
Validate disposition_id
Check if the item for the org is lot controlled before inserting into the Lots interface table.
Check if the item for the org is serial controlled before inserting into Serial interface table.
Check if inventory already exists for that item in that org and for a lot.
Validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.
Some important columns that need to be populated in the interface tables:
MTL_TRANSACTIONS_INTERFACE:
TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)
TRANSACTION_MODE (2 = Concurrent – to launch a dedicated transaction worker to explicitly process a set of transactions, 3 = Background – will be picked up by transaction manager polling process and assigned to transaction worker. These will not be picked up until the transaction manager is running)
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
TRANSACTION_SOURCE_ID
Source Type
Foreign Key Reference
Account
GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias
MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule
WIP_ENTITIES.WIP_ENTITY_ID
Sales Order
MTL_SALES_ORDERS.SALES_ORDER_ID
ITEM_SEGMENT1 TO 20,
TRANSACTION_QTY,
TRANSACTION_UOM,
SUBINVENTORY_CODE,
ORGANIZATION_ID,
LOC_SEGMENT1 TO 20.
MTL_TRANSACTION_LOTS_INTERFACE:
TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)
MTL_SERIAL_NUMBERS_INTERFACE:
TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
VENDOR_SERIAL_NUMBER
§ Customer conversion
Customer Interface helps you create customers in Oracle Applications.
Interface tables:
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
Base tables:
RA_CUSTOMERS
RA_ADDRESSES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_SITE_USES_ALL
Concurrent program:
Customer Interface
Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:
Validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
Some important columns that need to be populated in the interface tables:
RA_CUSTOMERS_INTERFACE_ALL:
ORIG_SYSTEM_CUSTOMER_REF
SITE_USE_CODE
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG (I = Insert, U = Update)
CUSTOMER_NAME
CUSTOMER_NUMBER
CUSTOMER_STATUS
PRIMARY_SITE_USE_FLAG
LOCATION
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
STATE
PROVINCE
COUNTY
POSTAL_CODE
COUNTRY
CUSTOMER_ATTRIBUTE1
CUSTOMER_ATTRIBUTE2
CUSTOMER_ATTRIBUTE3
CUSTOMER_ATTRIBUTE4
CUSTOMER_ATTRIBUTE5
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATED_BY
CREATION_DATE
ORG_ID
CUSTOMER_NAME_PHONETIC
RA_CUSTOMER_PROFILES_INT_ALL:
INSERT_UPDATE_FLAG
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
CUSTOMER_PROFILE_CLASS_NAME
CREDIT_HOLD
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATION_DATE
CREATED_BY
ORG_ID
RA_CONTACT_PHONES_INT_ALL:
ORIG_SYSTEM_CONTACT_REF
ORIG_SYSTEM_TELEPHONE_REF
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG
CONTACT_FIRST_NAME
CONTACT_LAST_NAME
CONTACT_TITLE
CONTACT_JOB_TITLE
TELEPHONE
TELEPHONE_EXTENSION
TELEPHONE_TYPE
TELEPHONE_AREA_CODE
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
CREATION_DATE
CREATED_BY
EMAIL_ADDRESS
ORG_ID
* Customer API
Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. These APIs utilize the new TCA model, inserting directly to the HZ tables.
API Details:
1. Set the organization id
Exec dbms_application_info.set_client_info(‘204’);
2. Create a party and an account
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
3. Create a physical location
HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
4. Create a party site using party_id you get from step 2 and location_id from step 3.
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
5. Create an account site using account_id you get from step 2 and party_site_id from step 4.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE
6. Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
Base table:
HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_CUST_ACCOUNTS
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCT_SITES_ALL
HZ_PARTY_SITE_USES
Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:
Validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
For detailed explanation refer to the below article:
http://www.erpschools.com/Apps/oracle-applications/articles/financials/Receivables/Customer-TCA-Architecture-and-API/index.aspx
§ Auto Invoice interface
This interface is used to import Customer invoices, Credit memos, Debit memos and On Account credits.
Pre-requisites:
Set of Books
Code combinations
Items
Sales representatives
Customers
Sales Tax rate
Payment Terms
Transaction Types
Freight Carriers
FOB
Batch Sources
Accounting Rules
Interface tables:
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS
RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_ERRORS (details about the failed records)
Base tables:
RA_BATCHES
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_LINE_SALESREPS
RA_CUST_TRX_GL_DIST_ALL
RA_CUSTOMER_TRX_TYPES_ALL
Concurrent Program:
Auto invoice master program
Validations:
Check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id,
Validate if the amount includes tax flag.
Some important columns that need to be populated in the interface tables:
RA_INTERFACE_LINES_ALL:
AGREEMENT_ID
COMMENTS
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
GL_DATE
HEADER_ATTRIBUTE1–15
HEADER_ATTRIBUTE_CATEGORY
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_SHIP_ADDRESS_ID
ORIG_SYSTEM_SHIP_CONTACT_ID
ORIG_SYSTEM_SHIP_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SERVER_ORDER_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER
§ Receipt API
To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.
Pre-requisites:
Set of Books
Code combinations
Items
Quick Codes
Sales representatives
Customers
Sales Tax rate
API:
AR_RECEIPT_API_PUB.CREATE_CASH
AR_RECEIPT_API_PUB.CREATE_AND_APPLY
Base tables:
AR_CASH_RECEIPTS
Validations:
Check the currency and the exchange rate type to assign the exchange rate.
Validate bill to the customer.
Get bill to site use id.
Get the customer trx id for this particular transaction number.
Get payment schedule date for the customer trx id.
§ Lockbox interface
AutoLockbox lets us automatically process receipts that are sent directly to the bank instead of manually feeding them in Oracle Receivables.
AutoLockbox is a three step process:
1. Import: During this step, Lockbox reads and formats the data from your bank file
into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader
script.
2. Validation: The validation program checks data in this interface table for
compatibility with Receivables. Once validated, the data is transferred into
QuickCash tables (AR_INTERIM_CASH_RECEIPTS_ALL and
AR_INTERIM_CASH_RCPT_LINES_ALL).
3. Post QuickCash: This step applies the receipts and updates your customer’s balances.
Pre-Requisites:
Banks
Receipt Class
Payment Method
Receipt Source
Lockbox
Transmission format
AutoCash Rule sets
Interface tables:
AR_PAYMENTS_INTERFACE_ALL (Import data from bank file)
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)
Base Tables:
AR_CASH_RECEIPTS
AR_RECEIVABLES_APPLICATIONS
AR_ADJUSTMENTS
AR_DISTRIBUTIONS_ALL
AR_PAYMENT_SCHEDULES_ALL
Concurrent program:
Lockbox
Validations:
Check for valid record type, transmission record id.
Validate sum of the payments within the transmission.
Identify the lockbox number (no given by a bank to identify a lockbox).
Some important columns that need to be populated in the interface tables:
AR_PAYMENTS_INTERFACE_ALL:
STATUS
RECORD_TYPE
LOCKBOX_NUMBER
BATCH_NAME
TRANSIT_ROUTING_NUMBER
ACCOUNT
CHECK_NUMBER
REMITTANCE_AMOUNT
DEPOSIT_DATE
ITEM_NUMBER
CURRENCY_CODE
DEPOSIT_TIME
§ AP invoice interface
This interface helps us to import vendor invoices into Oracle applications from external systems into Oracle Applications.
Pre-requisites:
Set of Books
Code combinations
Employees
Lookups
Interface tables:
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
Base tables:
AP_INVOICES_ALL – header information
AP_INVOICE_DISTRIBUTIONS_ALL – lines info
Concurrent program:
Payables Open Interface Import
Validations:
Check for valid vendor
Check for Source, Location, org_id, currency_code’s validity
Check for valid vendor site code.
Check if record already exists in payables interface table.
Some important columns that need to be populated in the interface tables:
AP_INVOICES_INTERFACE:
INVOICE_ID
INVOICE_NUM
INVOICE_DATE
VENDOR_NUM
VENDOR_SITE_ID
INVOICE_AMOUNT
INVOICE_CURRENCY_CODE
EXCHANGE_RATE
EXCHANGE_RATE_TYPE
EXCHANGE_DATE
DESCRIPTION
SOURCE
PO_NUMBER
PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE
ATTRIBUTE1 TO 15
ORG_ID
AP_INVOICE_LINES_INTERFACE:
INVOICE_ID
INVOICE_LINE_ID
LINE_TYPE_LOOKUP_CODE
AMOUNT
DESCRIPTION
TAX_CODE
PO_NUMBER
PO_LINE_NUMBER
PO_SHIPMENT_NUM
PO_DISTRIBUTION_NUM
PO_UNIT_OF_MEASURE
QUANTITY_INVOICED
DIST_CODE_CONCATENATED
DIST_CODE_COMBINATION_ID
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ORG_ID
§ Vendor conversion/interface
This interface is used to import suppliers, supplier sites and site contacts into Oracle applications.
Pre-requisites setup’s required:
Payment terms
Pay Groups
CCID
Supplier classifications
Bank Accounts
Employees (if employees have to set up as vendors)
Interface tables:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
Base Tables:
PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS
Interface programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
Validations:
Check if vendor already exists
Check if vendor site already exists
Check if site contact already exists
Check if term is defined.
Some important columns that need to be populated in the interface tables:
AP_SUPPLIERS_INT:
VENDOR_NUMBER, VENDOR_NAME, VENDOR_TYPE, STATE_REPORTABLE, FED_REPORTABLE, NUM_1099, TYPE_1099, PAY_GROUP_LOOKUP_CODE, VENDOR_ID is auto generated.
AP_SUPPLIER_SITES_INT:
VENDOR_SITE_ID, ORG_ID, VENDOR_SITE_CODE, INACTIVE_DATE, PAY_SITE, PURCHASING_SITE, SITE_PAYMENT_TERM, ADDRESS1, ADDRESS2.ADDRESS3, CITY, STATE, COUNTRY, ZIP, PH_NUM, FAX_NUMBER, TAX_REPORTING_SITE_FLAG.
AP_SUP_SITE_CONTACTS_INT:
VENDOR_ID, VENDOR_SITE_ID, FIRST_NAME, LAST_NAME, AREA_CODE, PHONE, EMAIL, ORG_ID
§ Purchase Order conversion:
The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.
Import Standard Purchase Orders
Pre-requisites:
Suppliers, sites and contacts
Buyers
Line Types
Items
PO
Charge account setup
Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_INTERFACE_ERRORS (Fallouts)
Interface Program:
Import Standard Purchase Orders.
Base Tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL
Validations:
Header:
Check if OU name is valid
Check if Supplier is valid
Check if Supplier site is valid
Check if buyer is valid
Check if Payment term is valid
Check if Bill to and ship to are valid
Check if FOB, freight terms are valid
Lines:
Check if Line_type, ship_to_org, item, uom, ship_to_location_id, requestor, charge_account, deliver_to_location are valid
General:
Check for duplicate records in interface tables
Check if the record already exists in base tables.
Some important columns that need to be populated in the interface tables:
PO_HEADERS_INTERFACE:
INTERFACE_HEADER_ID (PO_HEADERS_INTERFACE_S.NEXTVAL), BATCH_ID, ORG_ID, INTERFACE_SOURCE_CODE, ACTION (‘ORIGINAL’,’UPDATE’,’REPLACE’), GROUP_CODE, DOCUMENT_TYPE_CODE, PO_HEADER_ID (NULL), RELEASE_ID, RELEASE_NUM, CURRENCY_CODE, RATE, AGENT_NAME, VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION, BILL_TO_LOCATION, , PAYMENT_TERMS
PO_LINES_INTERFACE:
INTERFACE_LINE_ID, INTERFACE_HEADER_ID, LINE_NUM, SHIPMENT_NUM, ITEM, REQUISITION_LINE_ID, UOM, UNIT_PRICE, FREIGHT_TERMS, FOB
PO_DISTRIBUTIONS_INTERFACE:
INTERFACE_LINE_ID, INTERFACE_HEADER_ID, INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM, QUANTITY_ORDERED, QTY_DELIVERED, QTY_BILLED, QTY_CANCELLED, DELIVER_TO_LOCATION_ID, DELIVER_TO_PERSON_ID, SET_OF_BOOKS, CHARGE_ACCT, AMOUNT_BILLED.
Import Blanket Purchase Agreements:
Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
Interface program:
Import Price Catalogs
Base tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
Example:
Suppose you want to create a blanket with one line and two price breaks and the details for the price break are as below:
1) Quantity = 500, price = 10, effective date from '01-JAN-2006' to
'31-JUN-2006'
2) Quantity = 500, price = 11, effective date from '01-JUL-2006' to
'01-JAN-2007'
To create the above the BPA, you would create ONE record in PO_HEADERS_INTERFACE and THREE records in PO_LINES_INTERFACE
LINE1: It will have only the line information. LINE NUM would be 1.
LINE2: For the first Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would be ‘PRICE BREAK’
LINE3: For the second Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would be ‘PRICE BREAK’
All the line-level records above must have the same INTERFACE_HEADER_ID.
For detailed explanation refer to the below article:
http://www.erpschools.com/Apps/oracle-applications/articles/financials/Purchasing/Import-Blanket-Purchase-Agreements/index.aspx
§ Requisition import
You can automatically import requisitions into Oracle Applications using the Requisitions Open Interface
Pre-requisites:
Set of Books
Code combinations
Employees
Items
Define a Requisition Import Group-By method in the Options window.
Associate a customer with your deliver-to location using the Customer Addresses window for internally sourced requisitions.
Interface tables:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
Base tables:
PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Concurrent program:
REQUISITION IMPORT
Validations:
Check for interface transaction source code, requisition destination type.
Check for quantity ordered, authorization status type.
Some important columns that need to be populated in the interface tables:
PO_REQUISITIONS_INTERFACE_ALL:
INTERFACE_SOURCE_CODE (to identify the source of your Requisitions)
DESTINATION_TYPE_CODE
AUTHORIZATION_STATUS
PREPARER_ID or PREPARER_NAME
QUANTITY
CHARGE_ACCOUNT_ID or charge account segment values
DESTINATION_ORGANIZATION_ID or DESTINATION_ORGANIZATION_CODE
DELIVER_TO_LOCATION_ID or DELIVER_TO_LOCATION_CODE
DELIVER_TO_REQUESTOR_ID or DELIVER_TO_REQUESTOR_NAME
ORG_ID
ITEM_ID or item segment values (values if the SOURCE_TYPE_CODE or DESTINATION_TYPE_CODE is 'INVENTORY')
PO_REQ_DIST_INTERFACE_ALL:
CHARGE_ACCOUNT_ID or charge account segment values
DISTRIBUTION_NUMBER
DESTINATION_ORGANIZATION_ID
DESTINATION_TYPE_CODE
INTERFACE_SOURCE_CODE
ORG_ID
DIST_SEQUENCE_ID (if MULTI_DISTRIBUTIONS is set to Y)
* PO Receipts Interface
The Receiving Open Interface is used for processing and validating receipt data that comes from sources other than the Receipts window in Purchasing.
Pre-requisites:
Set of Books
Code combinations
Employees
Items
Interface tables:
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
PO_INTERFACE_ERRORS
Concurrent program:
RECEIVING OPEN INTERFACE
Base tables:
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
RCV_TRANSACTIONS
Validations:
Check that SHIPPED_DATE should not be later than today.
Check if vendor is valid.
If Invoice number is passed, check for its validity
Check if Item is valid
Some important columns that need to be populated in the interface tables:
RCV_HEADERS_INTERFACE:
HEADER_INTERFACE_ID
GROUP_ID
PROCESSING_STATUS_
CODE
RECEIPT_SOURCE_CODE
TRANSACTION_TYPE
SHIPMENT_NUM
RECEIPT_NUM
VENDOR_NAME
SHIP_TO_
ORGANIZATION_CODE
SHIPPED_DATE
INVOICE_NUM
INVOICE_DATE
TOTAL_INVOICE_
AMOUNT
PAYMENT_TERMS_ID
EMPLOYEE_NAME
VALIDATION_FLAG (Indicates whether to validate a row or not, values ‘Y’, ‘N’)
RCV_TRANSACTIONS_INTERFACE:
INTERFACE_TRANSACTION_ID
GROUP_ID
TRANSACTION_TYPE (’SHIP’ for a standard shipment (an ASN or ASBN) or ’RECEIVE’ for a standard receipt)
TRANSACTION_DATE
PROCESSING_STATUS_CODE =’PENDING’
CATEGORY_ID
QUANTITY
UNIT_OF_MEASURE
ITEM_DESCRIPTION
ITEM_REVISION
EMPLOYEE_ID
AUTO_TRANSACT_CODE
SHIP_TO_LOCATION_ID
RECEIPT_SOURCE_CODE
TO_ORGANIZATION_CODE
SOURCE_DOCUMENT_CODE
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
SUBINVENTORY
HEADER_INTERFACE_ID
DELIVER_TO_PERSON_NAME
DELIVER_TO_LOCATION_CODE
VALIDATION_FLAG
ITEM_NUM
VENDOR_ITEM_NUM
VENDOR_ID
VENDOR_SITE_ID
ITEM_ID
ITEM_DESCRIPTION
SHIP_TO_LOCATION_ID
§ GL Journal interface
This interface lets you import journals from other applications like Receivables, Payables etc to integrate the information with General Ledger.
Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations
Currencies
Categories
Journal Sources
Interface tables:
GL_INTERFACE
Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES
Concurrent Program:
Journal Import
Journal Posting --- populates GL_BALANCES
Validations:
Validate SOB, journal source name, journal category name, actual flag
A – Actual amounts
B – Budget amounts
E – Encumbrance amount
If you enter E in the interface table, then enter appropriate encumbrance ID, if B enter budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if code combination is valid and enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.
Some important columns that need to be populated in the interface tables:
GL_INTERFACE:
STATUS
SET_OF_BOOKS_ID
ACCOUNTING_DATE
CURRENCY_CODE
DATE_CREATED
CREATED_BY
ACTUAL_FLAG
USER_JE_CATEGORY_NAME
USER_JE_SOURCE_NAME
CURRENCY_CONVERSION_DATE
ENCUMBRANCE_TYPE_ID
BUDGET_VERSION_ID
USER_CURRENCY_CONVERSION_TYPE
CURRENCY_CONVERSION_RATE
SEGMENT1 to
ENTERED_DR
ENTERED_CR
ACCOUNTED_DR
ACCOUNTED_CR
TRANSACTION_DATE
PERIOD_NAME
JE_LINE_NUM
CHART_OF_ACCOUNTS_ID
FUNCTIONAL_CURRENCY_CODE
CODE_COMBINATION_ID
DATE_CREATED_IN_GL
GROUP_ID
§ GL budget interface
Budget interface lets you load budget data from external sources into Oracle Applications.
Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations
Interface tables:
GL_BUDGET_INTERFACE
Base tables:
GL_BUDGETS
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_TYPES
Concurrent program:
Budget Upload
Validations:
Check if CURRENCY_CODE is valid.
Check if SET_OF_BOOKS_ID is valid.
Check if BUDGET_ENTITY_NAME (budget organization) is valid.
Some important columns that need to be populated in the interface tables:
GL_BUDGET_INTERFACE:
BUDGET_NAME NOT
BUDGET_ENTITY_NAME
CURRENCY_CODE
FISCAL_YEAR
UPDATE_LOGIC_TYPE
BUDGET_ENTITY_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
BUDGET_VERSION_ID
PERIOD_TYPE
DR_FLAG
STATUS
ACCOUNT_TYPE
PERIOD1_AMOUNT through PERIOD60_AMOUNT
SEGMENT1 through SEGMENT30
* GL daily conversion rates
This interface lets you load the rates automatically into General Ledger.
Pre-requisites:
Currencies
Conversion rate Types
Interface tables:
GL_DAILY_RATES_INTERFACE
Base tables:
GL_DAILY_RATES
GL_DAILY_CONVERSION_TYPES
Concurrent Program:
You do not need to run any import programs. The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE. All that is required is to develop program to populate the interface table with daily rates information.
Validations:
Check if FROM_CURRENCY and TO_CURRENCY are valid.
Check if USER_CONVERSION_TYPE is valid.
Some important columns that need to be populated in the interface tables:
GL_DAILY_RATES_INTERFACE:
FROM_CURRENCY
TO_CURRENCY
FROM_CONVERSION_DATE
TO_CONVERSION_DATE
USER_CONVERSION_TYPE
CONVERSION_RATE
MODE_FLAG (D= Delete, I = Insert, U = Update)
INVERSE_CONVERSION_RATE
Overview:
Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications. This article briefs you about some of the major interface programs.
Conversion/Interface Strategy:
1. Data Mapping
During the data mapping process, list of all the data sets and data elements that will need to be moved into the Oracle tables as part of conversion are identified. Data mapping tables are prepared as part of this activity that show what are the data elements that are needed by the target system to meet the business requirements and from where they will be extracted in the old system.
2. Download Programs
After the conversion data mapping is complete, download programs are developed that are used to extract the identified conversion data elements from the current systems in the form of an ASCII flat file. The structure of the flat file must match the structure of the Oracle standard interface tables. These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.
3. Upload Program
Once the data has been extracted to a flat file, it is then moved to the target file system and the data from the file is loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities. Then programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables.
4. Interface Program
Once the interface tables are populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. The interface programs validate the data, derive and assign the default values and ultimately populate the production base tables.
Interface/Conversion examples with details:
The below list of interfaces/conversions are covered in this section. Details like pre-requisites required, interface tables, interface program, base tables, validations that need to be performed after inserting the details into the interface tables and required columns that need to be populated in the interface table are discussed for each interface.
· Order Import Interface (Sales Order Conversion)
· Item import (Item conversion)
· Inventory On-hand quantity Interface
· Customer conversion
· Auto Invoice Interface
· AR Receipts
· Lockbox Interface
· AP Invoices
· Vendor
· Purchase Orders
· Requisition
· Receiving
· Journal import
· Budget import
· Daily Conversion Rates
* Order Import Interface (Sales Order Conversion)
Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.
Pre-requisites:
Order Type
Line Type
Items
Customers
Ship Method/ Freight Carrier
Sales Person
Sales Territories
Customer Order Holds
Sub Inventory/ Locations
On hand Quantity
Interface tables:
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_ORDER_CUST_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL
Base tables:
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
Pricing tables: QP_PRICING_ATTRIBUTES
Concurrent Program:
Order Import
Validations:
Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
Check for sales_rep_id. Should exist for a booked order.
Ordered_date should exist (header level)
Delivery_lead_time should exist (line level)
Earliest_acceptable_date should exist.
Freight_terms should exist
Notes:
During import of orders, shipping tables are not populated.
If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables are HZ_PARTIES, HZ_LOCATIONS.
Orders can be categorized based on their status:
1. Entered orders 2. Booked orders 3. Closed orders
Order Import API OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER can also be used to import orders.
Some important columns that need to populated in the interface tables:
OE_HEADERS_IFACE_ALL:
ORIG_SYS_DOCUMENT_REF
ORDER_SOURCE
CONVERSION_RATE
ORG_ID
ORDER_TYPE_ID
PRICE_LIST
SOLD_FROM_ORG_ID
SOLD_TO_ORG_ID
SHIP_TO_ORG_ID
SHIP_FROM_ORG_ID
CUSTOMER_NAME
INVOICE_TO_ORG_ID
OPERATION_CODE
OE_LINES_IFACE_ALL
ORDER_SOURCE_ID
ORIG_SYS_DOCUMENT_REF
ORIG_SYS_LINE_REF
ORIG_SYS_SHIPMENT_REF
INVENTORY_ITEM_ID
LINK_TO_LINE_REF
REQUEST_DATE
DELIVERY_LEAD_TIME
DELIVERY_ID
ORDERED_QUANTITY
ORDER_QUANTITY_UOM
SHIPPING_QUANTITY
PRICING_QUANTITY
PRICING_QUANTITY_UOM
SOLD_FROM_ORG_ID
SOLD_TO_ORG_ID
INVOICE_TO_ ORG_ID
SHIP_TO_ORG_ID
PRICE_LIST_ID
PAYMENT_TERM_ID
§ Item import (Item conversion)
The Item Interface lets you import items into Oracle Inventory.
Pre-requisites:
Creating an Organization
Code Combinations
Templates
Defining Item Status Codes
Defining Item Types
Interface tables:
MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
MTL_INTERFACE_ERRORS (View errors after import)
Concurrent Program:
Item import
In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.
Base Tables:
MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES
Validations:
Check for valid item type.
Check for valid part_id/segment of the source table.
Validate part_id/segment1 for master org.
Validate and translate template id of the source table.
Check for valid template id. (Attributes are already set for items, default attributes for that template, i.e., purchasable, stockable, etc )
Check for valid item status.
Validate primary uom of the source table.
Validate attribute values.
Validate other UOMs of the source table.
Check for unique item type. Discard the item, if part has non-unique item type.
Check for description, inv_um uniqueness
Validate organization id.
Load master records and category records only if all
Load child record if no error found.
Some important columns that need to populated in the interface tables:
MTL_SYSTEM_ITEMS_INTERFACE:
PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete, 3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded)
TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
SET_PROCESS_ID = 1
ORGANIZATION_ID
DESCRIPTION
ITEM_NUMBER and/or SEGMENT (n)
MATERIAL_COST
REVISION
TEMPLATE_ID
SUMMARY_FLAG
ENABLED_FLAG
PURCHASING_ITEM_FLAG
SALES_ACCOUNT (defaulted from
MTL_PARAMETERS.SALES_ACCOUNT)
COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)
MTL_ITEM_CATEGORIES_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER.
ORGANIZATION_ID or ORGANIZATION_CODE or both.
TRANSACTION_TYPE = 'CREATE' ('UPDATE' or 'DELETE' is not possible through Item Import).
CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
CATEGORY_ID or CATEGORY_NAME or both.
PROCESS_FLAG = 1
SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if you are importing item and category assignment together)
MTL_ITEM_REVISIONS_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the
ORGANIZATION_ID or ORGANIZATION_CODE or both
REVISION
CHANGE_NOTICE
ECN_INITIATION_DATE
IMPLEMENTATION_DATE
IMPLEMENTED_SERIAL_NUMBER
EFFECTIVITY_DATE
ATTRIBUTE_CATEGORY
ATTRIBUTEn
REVISED_ITEM_SEQUENCE_ID
DESCRIPTION
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.
§ Inventory On-hand quantity Interface
This interface lets you import the on hand inventory into Oracle.
Interface tables:
MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)
Concurrent Program:
Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.
Base Tables:
MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS
Validations:
Validate organization_id
Check if item is assigned to organization
Validate disposition_id
Check if the item for the org is lot controlled before inserting into the Lots interface table.
Check if the item for the org is serial controlled before inserting into Serial interface table.
Check if inventory already exists for that item in that org and for a lot.
Validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.
Some important columns that need to be populated in the interface tables:
MTL_TRANSACTIONS_INTERFACE:
TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)
TRANSACTION_MODE (2 = Concurrent – to launch a dedicated transaction worker to explicitly process a set of transactions, 3 = Background – will be picked up by transaction manager polling process and assigned to transaction worker. These will not be picked up until the transaction manager is running)
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
TRANSACTION_SOURCE_ID
Source Type
Foreign Key Reference
Account
GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias
MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule
WIP_ENTITIES.WIP_ENTITY_ID
Sales Order
MTL_SALES_ORDERS.SALES_ORDER_ID
ITEM_SEGMENT1 TO 20,
TRANSACTION_QTY,
TRANSACTION_UOM,
SUBINVENTORY_CODE,
ORGANIZATION_ID,
LOC_SEGMENT1 TO 20.
MTL_TRANSACTION_LOTS_INTERFACE:
TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)
MTL_SERIAL_NUMBERS_INTERFACE:
TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
VENDOR_SERIAL_NUMBER
§ Customer conversion
Customer Interface helps you create customers in Oracle Applications.
Interface tables:
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
Base tables:
RA_CUSTOMERS
RA_ADDRESSES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_SITE_USES_ALL
Concurrent program:
Customer Interface
Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:
Validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
Some important columns that need to be populated in the interface tables:
RA_CUSTOMERS_INTERFACE_ALL:
ORIG_SYSTEM_CUSTOMER_REF
SITE_USE_CODE
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG (I = Insert, U = Update)
CUSTOMER_NAME
CUSTOMER_NUMBER
CUSTOMER_STATUS
PRIMARY_SITE_USE_FLAG
LOCATION
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
STATE
PROVINCE
COUNTY
POSTAL_CODE
COUNTRY
CUSTOMER_ATTRIBUTE1
CUSTOMER_ATTRIBUTE2
CUSTOMER_ATTRIBUTE3
CUSTOMER_ATTRIBUTE4
CUSTOMER_ATTRIBUTE5
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATED_BY
CREATION_DATE
ORG_ID
CUSTOMER_NAME_PHONETIC
RA_CUSTOMER_PROFILES_INT_ALL:
INSERT_UPDATE_FLAG
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
CUSTOMER_PROFILE_CLASS_NAME
CREDIT_HOLD
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATION_DATE
CREATED_BY
ORG_ID
RA_CONTACT_PHONES_INT_ALL:
ORIG_SYSTEM_CONTACT_REF
ORIG_SYSTEM_TELEPHONE_REF
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG
CONTACT_FIRST_NAME
CONTACT_LAST_NAME
CONTACT_TITLE
CONTACT_JOB_TITLE
TELEPHONE
TELEPHONE_EXTENSION
TELEPHONE_TYPE
TELEPHONE_AREA_CODE
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
CREATION_DATE
CREATED_BY
EMAIL_ADDRESS
ORG_ID
* Customer API
Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. These APIs utilize the new TCA model, inserting directly to the HZ tables.
API Details:
1. Set the organization id
Exec dbms_application_info.set_client_info(‘204’);
2. Create a party and an account
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
3. Create a physical location
HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
4. Create a party site using party_id you get from step 2 and location_id from step 3.
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
5. Create an account site using account_id you get from step 2 and party_site_id from step 4.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE
6. Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
Base table:
HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_CUST_ACCOUNTS
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCT_SITES_ALL
HZ_PARTY_SITE_USES
Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:
Validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
For detailed explanation refer to the below article:
http://www.erpschools.com/Apps/oracle-applications/articles/financials/Receivables/Customer-TCA-Architecture-and-API/index.aspx
§ Auto Invoice interface
This interface is used to import Customer invoices, Credit memos, Debit memos and On Account credits.
Pre-requisites:
Set of Books
Code combinations
Items
Sales representatives
Customers
Sales Tax rate
Payment Terms
Transaction Types
Freight Carriers
FOB
Batch Sources
Accounting Rules
Interface tables:
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS
RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_ERRORS (details about the failed records)
Base tables:
RA_BATCHES
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_LINE_SALESREPS
RA_CUST_TRX_GL_DIST_ALL
RA_CUSTOMER_TRX_TYPES_ALL
Concurrent Program:
Auto invoice master program
Validations:
Check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id,
Validate if the amount includes tax flag.
Some important columns that need to be populated in the interface tables:
RA_INTERFACE_LINES_ALL:
AGREEMENT_ID
COMMENTS
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
GL_DATE
HEADER_ATTRIBUTE1–15
HEADER_ATTRIBUTE_CATEGORY
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_SHIP_ADDRESS_ID
ORIG_SYSTEM_SHIP_CONTACT_ID
ORIG_SYSTEM_SHIP_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SERVER_ORDER_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER
§ Receipt API
To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.
Pre-requisites:
Set of Books
Code combinations
Items
Quick Codes
Sales representatives
Customers
Sales Tax rate
API:
AR_RECEIPT_API_PUB.CREATE_CASH
AR_RECEIPT_API_PUB.CREATE_AND_APPLY
Base tables:
AR_CASH_RECEIPTS
Validations:
Check the currency and the exchange rate type to assign the exchange rate.
Validate bill to the customer.
Get bill to site use id.
Get the customer trx id for this particular transaction number.
Get payment schedule date for the customer trx id.
§ Lockbox interface
AutoLockbox lets us automatically process receipts that are sent directly to the bank instead of manually feeding them in Oracle Receivables.
AutoLockbox is a three step process:
1. Import: During this step, Lockbox reads and formats the data from your bank file
into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader
script.
2. Validation: The validation program checks data in this interface table for
compatibility with Receivables. Once validated, the data is transferred into
QuickCash tables (AR_INTERIM_CASH_RECEIPTS_ALL and
AR_INTERIM_CASH_RCPT_LINES_ALL).
3. Post QuickCash: This step applies the receipts and updates your customer’s balances.
Pre-Requisites:
Banks
Receipt Class
Payment Method
Receipt Source
Lockbox
Transmission format
AutoCash Rule sets
Interface tables:
AR_PAYMENTS_INTERFACE_ALL (Import data from bank file)
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)
Base Tables:
AR_CASH_RECEIPTS
AR_RECEIVABLES_APPLICATIONS
AR_ADJUSTMENTS
AR_DISTRIBUTIONS_ALL
AR_PAYMENT_SCHEDULES_ALL
Concurrent program:
Lockbox
Validations:
Check for valid record type, transmission record id.
Validate sum of the payments within the transmission.
Identify the lockbox number (no given by a bank to identify a lockbox).
Some important columns that need to be populated in the interface tables:
AR_PAYMENTS_INTERFACE_ALL:
STATUS
RECORD_TYPE
LOCKBOX_NUMBER
BATCH_NAME
TRANSIT_ROUTING_NUMBER
ACCOUNT
CHECK_NUMBER
REMITTANCE_AMOUNT
DEPOSIT_DATE
ITEM_NUMBER
CURRENCY_CODE
DEPOSIT_TIME
§ AP invoice interface
This interface helps us to import vendor invoices into Oracle applications from external systems into Oracle Applications.
Pre-requisites:
Set of Books
Code combinations
Employees
Lookups
Interface tables:
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
Base tables:
AP_INVOICES_ALL – header information
AP_INVOICE_DISTRIBUTIONS_ALL – lines info
Concurrent program:
Payables Open Interface Import
Validations:
Check for valid vendor
Check for Source, Location, org_id, currency_code’s validity
Check for valid vendor site code.
Check if record already exists in payables interface table.
Some important columns that need to be populated in the interface tables:
AP_INVOICES_INTERFACE:
INVOICE_ID
INVOICE_NUM
INVOICE_DATE
VENDOR_NUM
VENDOR_SITE_ID
INVOICE_AMOUNT
INVOICE_CURRENCY_CODE
EXCHANGE_RATE
EXCHANGE_RATE_TYPE
EXCHANGE_DATE
DESCRIPTION
SOURCE
PO_NUMBER
PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE
ATTRIBUTE1 TO 15
ORG_ID
AP_INVOICE_LINES_INTERFACE:
INVOICE_ID
INVOICE_LINE_ID
LINE_TYPE_LOOKUP_CODE
AMOUNT
DESCRIPTION
TAX_CODE
PO_NUMBER
PO_LINE_NUMBER
PO_SHIPMENT_NUM
PO_DISTRIBUTION_NUM
PO_UNIT_OF_MEASURE
QUANTITY_INVOICED
DIST_CODE_CONCATENATED
DIST_CODE_COMBINATION_ID
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ORG_ID
§ Vendor conversion/interface
This interface is used to import suppliers, supplier sites and site contacts into Oracle applications.
Pre-requisites setup’s required:
Payment terms
Pay Groups
CCID
Supplier classifications
Bank Accounts
Employees (if employees have to set up as vendors)
Interface tables:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
Base Tables:
PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS
Interface programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
Validations:
Check if vendor already exists
Check if vendor site already exists
Check if site contact already exists
Check if term is defined.
Some important columns that need to be populated in the interface tables:
AP_SUPPLIERS_INT:
VENDOR_NUMBER, VENDOR_NAME, VENDOR_TYPE, STATE_REPORTABLE, FED_REPORTABLE, NUM_1099, TYPE_1099, PAY_GROUP_LOOKUP_CODE, VENDOR_ID is auto generated.
AP_SUPPLIER_SITES_INT:
VENDOR_SITE_ID, ORG_ID, VENDOR_SITE_CODE, INACTIVE_DATE, PAY_SITE, PURCHASING_SITE, SITE_PAYMENT_TERM, ADDRESS1, ADDRESS2.ADDRESS3, CITY, STATE, COUNTRY, ZIP, PH_NUM, FAX_NUMBER, TAX_REPORTING_SITE_FLAG.
AP_SUP_SITE_CONTACTS_INT:
VENDOR_ID, VENDOR_SITE_ID, FIRST_NAME, LAST_NAME, AREA_CODE, PHONE, EMAIL, ORG_ID
§ Purchase Order conversion:
The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.
Import Standard Purchase Orders
Pre-requisites:
Suppliers, sites and contacts
Buyers
Line Types
Items
PO
Charge account setup
Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_INTERFACE_ERRORS (Fallouts)
Interface Program:
Import Standard Purchase Orders.
Base Tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL
Validations:
Header:
Check if OU name is valid
Check if Supplier is valid
Check if Supplier site is valid
Check if buyer is valid
Check if Payment term is valid
Check if Bill to and ship to are valid
Check if FOB, freight terms are valid
Lines:
Check if Line_type, ship_to_org, item, uom, ship_to_location_id, requestor, charge_account, deliver_to_location are valid
General:
Check for duplicate records in interface tables
Check if the record already exists in base tables.
Some important columns that need to be populated in the interface tables:
PO_HEADERS_INTERFACE:
INTERFACE_HEADER_ID (PO_HEADERS_INTERFACE_S.NEXTVAL), BATCH_ID, ORG_ID, INTERFACE_SOURCE_CODE, ACTION (‘ORIGINAL’,’UPDATE’,’REPLACE’), GROUP_CODE, DOCUMENT_TYPE_CODE, PO_HEADER_ID (NULL), RELEASE_ID, RELEASE_NUM, CURRENCY_CODE, RATE, AGENT_NAME, VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION, BILL_TO_LOCATION, , PAYMENT_TERMS
PO_LINES_INTERFACE:
INTERFACE_LINE_ID, INTERFACE_HEADER_ID, LINE_NUM, SHIPMENT_NUM, ITEM, REQUISITION_LINE_ID, UOM, UNIT_PRICE, FREIGHT_TERMS, FOB
PO_DISTRIBUTIONS_INTERFACE:
INTERFACE_LINE_ID, INTERFACE_HEADER_ID, INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM, QUANTITY_ORDERED, QTY_DELIVERED, QTY_BILLED, QTY_CANCELLED, DELIVER_TO_LOCATION_ID, DELIVER_TO_PERSON_ID, SET_OF_BOOKS, CHARGE_ACCT, AMOUNT_BILLED.
Import Blanket Purchase Agreements:
Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
Interface program:
Import Price Catalogs
Base tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
Example:
Suppose you want to create a blanket with one line and two price breaks and the details for the price break are as below:
1) Quantity = 500, price = 10, effective date from '01-JAN-2006' to
'31-JUN-2006'
2) Quantity = 500, price = 11, effective date from '01-JUL-2006' to
'01-JAN-2007'
To create the above the BPA, you would create ONE record in PO_HEADERS_INTERFACE and THREE records in PO_LINES_INTERFACE
LINE1: It will have only the line information. LINE NUM would be 1.
LINE2: For the first Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would be ‘PRICE BREAK’
LINE3: For the second Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would be ‘PRICE BREAK’
All the line-level records above must have the same INTERFACE_HEADER_ID.
For detailed explanation refer to the below article:
http://www.erpschools.com/Apps/oracle-applications/articles/financials/Purchasing/Import-Blanket-Purchase-Agreements/index.aspx
§ Requisition import
You can automatically import requisitions into Oracle Applications using the Requisitions Open Interface
Pre-requisites:
Set of Books
Code combinations
Employees
Items
Define a Requisition Import Group-By method in the Options window.
Associate a customer with your deliver-to location using the Customer Addresses window for internally sourced requisitions.
Interface tables:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
Base tables:
PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Concurrent program:
REQUISITION IMPORT
Validations:
Check for interface transaction source code, requisition destination type.
Check for quantity ordered, authorization status type.
Some important columns that need to be populated in the interface tables:
PO_REQUISITIONS_INTERFACE_ALL:
INTERFACE_SOURCE_CODE (to identify the source of your Requisitions)
DESTINATION_TYPE_CODE
AUTHORIZATION_STATUS
PREPARER_ID or PREPARER_NAME
QUANTITY
CHARGE_ACCOUNT_ID or charge account segment values
DESTINATION_ORGANIZATION_ID or DESTINATION_ORGANIZATION_CODE
DELIVER_TO_LOCATION_ID or DELIVER_TO_LOCATION_CODE
DELIVER_TO_REQUESTOR_ID or DELIVER_TO_REQUESTOR_NAME
ORG_ID
ITEM_ID or item segment values (values if the SOURCE_TYPE_CODE or DESTINATION_TYPE_CODE is 'INVENTORY')
PO_REQ_DIST_INTERFACE_ALL:
CHARGE_ACCOUNT_ID or charge account segment values
DISTRIBUTION_NUMBER
DESTINATION_ORGANIZATION_ID
DESTINATION_TYPE_CODE
INTERFACE_SOURCE_CODE
ORG_ID
DIST_SEQUENCE_ID (if MULTI_DISTRIBUTIONS is set to Y)
* PO Receipts Interface
The Receiving Open Interface is used for processing and validating receipt data that comes from sources other than the Receipts window in Purchasing.
Pre-requisites:
Set of Books
Code combinations
Employees
Items
Interface tables:
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
PO_INTERFACE_ERRORS
Concurrent program:
RECEIVING OPEN INTERFACE
Base tables:
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
RCV_TRANSACTIONS
Validations:
Check that SHIPPED_DATE should not be later than today.
Check if vendor is valid.
If Invoice number is passed, check for its validity
Check if Item is valid
Some important columns that need to be populated in the interface tables:
RCV_HEADERS_INTERFACE:
HEADER_INTERFACE_ID
GROUP_ID
PROCESSING_STATUS_
CODE
RECEIPT_SOURCE_CODE
TRANSACTION_TYPE
SHIPMENT_NUM
RECEIPT_NUM
VENDOR_NAME
SHIP_TO_
ORGANIZATION_CODE
SHIPPED_DATE
INVOICE_NUM
INVOICE_DATE
TOTAL_INVOICE_
AMOUNT
PAYMENT_TERMS_ID
EMPLOYEE_NAME
VALIDATION_FLAG (Indicates whether to validate a row or not, values ‘Y’, ‘N’)
RCV_TRANSACTIONS_INTERFACE:
INTERFACE_TRANSACTION_ID
GROUP_ID
TRANSACTION_TYPE (’SHIP’ for a standard shipment (an ASN or ASBN) or ’RECEIVE’ for a standard receipt)
TRANSACTION_DATE
PROCESSING_STATUS_CODE =’PENDING’
CATEGORY_ID
QUANTITY
UNIT_OF_MEASURE
ITEM_DESCRIPTION
ITEM_REVISION
EMPLOYEE_ID
AUTO_TRANSACT_CODE
SHIP_TO_LOCATION_ID
RECEIPT_SOURCE_CODE
TO_ORGANIZATION_CODE
SOURCE_DOCUMENT_CODE
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
SUBINVENTORY
HEADER_INTERFACE_ID
DELIVER_TO_PERSON_NAME
DELIVER_TO_LOCATION_CODE
VALIDATION_FLAG
ITEM_NUM
VENDOR_ITEM_NUM
VENDOR_ID
VENDOR_SITE_ID
ITEM_ID
ITEM_DESCRIPTION
SHIP_TO_LOCATION_ID
§ GL Journal interface
This interface lets you import journals from other applications like Receivables, Payables etc to integrate the information with General Ledger.
Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations
Currencies
Categories
Journal Sources
Interface tables:
GL_INTERFACE
Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES
Concurrent Program:
Journal Import
Journal Posting --- populates GL_BALANCES
Validations:
Validate SOB, journal source name, journal category name, actual flag
A – Actual amounts
B – Budget amounts
E – Encumbrance amount
If you enter E in the interface table, then enter appropriate encumbrance ID, if B enter budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if code combination is valid and enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.
Some important columns that need to be populated in the interface tables:
GL_INTERFACE:
STATUS
SET_OF_BOOKS_ID
ACCOUNTING_DATE
CURRENCY_CODE
DATE_CREATED
CREATED_BY
ACTUAL_FLAG
USER_JE_CATEGORY_NAME
USER_JE_SOURCE_NAME
CURRENCY_CONVERSION_DATE
ENCUMBRANCE_TYPE_ID
BUDGET_VERSION_ID
USER_CURRENCY_CONVERSION_TYPE
CURRENCY_CONVERSION_RATE
SEGMENT1 to
ENTERED_DR
ENTERED_CR
ACCOUNTED_DR
ACCOUNTED_CR
TRANSACTION_DATE
PERIOD_NAME
JE_LINE_NUM
CHART_OF_ACCOUNTS_ID
FUNCTIONAL_CURRENCY_CODE
CODE_COMBINATION_ID
DATE_CREATED_IN_GL
GROUP_ID
§ GL budget interface
Budget interface lets you load budget data from external sources into Oracle Applications.
Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations
Interface tables:
GL_BUDGET_INTERFACE
Base tables:
GL_BUDGETS
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_TYPES
Concurrent program:
Budget Upload
Validations:
Check if CURRENCY_CODE is valid.
Check if SET_OF_BOOKS_ID is valid.
Check if BUDGET_ENTITY_NAME (budget organization) is valid.
Some important columns that need to be populated in the interface tables:
GL_BUDGET_INTERFACE:
BUDGET_NAME NOT
BUDGET_ENTITY_NAME
CURRENCY_CODE
FISCAL_YEAR
UPDATE_LOGIC_TYPE
BUDGET_ENTITY_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
BUDGET_VERSION_ID
PERIOD_TYPE
DR_FLAG
STATUS
ACCOUNT_TYPE
PERIOD1_AMOUNT through PERIOD60_AMOUNT
SEGMENT1 through SEGMENT30
* GL daily conversion rates
This interface lets you load the rates automatically into General Ledger.
Pre-requisites:
Currencies
Conversion rate Types
Interface tables:
GL_DAILY_RATES_INTERFACE
Base tables:
GL_DAILY_RATES
GL_DAILY_CONVERSION_TYPES
Concurrent Program:
You do not need to run any import programs. The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE. All that is required is to develop program to populate the interface table with daily rates information.
Validations:
Check if FROM_CURRENCY and TO_CURRENCY are valid.
Check if USER_CONVERSION_TYPE is valid.
Some important columns that need to be populated in the interface tables:
GL_DAILY_RATES_INTERFACE:
FROM_CURRENCY
TO_CURRENCY
FROM_CONVERSION_DATE
TO_CONVERSION_DATE
USER_CONVERSION_TYPE
CONVERSION_RATE
MODE_FLAG (D= Delete, I = Insert, U = Update)
INVERSE_CONVERSION_RATE
Oracle Applications Framework
Oracle Applcation Frame Work
Java Introduction :-->
j2me--> Micro edition (Reliance incorporated it first)
Class--> Blueprint of specifications
Object--> Instance of class
Package--> Collection of classes...etc
Design Patterns :-> Specification to prepare project using best practices.
EX: Model View Controller (Oaf is using this), Struts,
Framework :->
Fills the gape (EX:b/w c++ vs forms)
Oracle Application Framework :-->
It is a java based, 3Tier web based application, which has linked to oracle application ebiz instance
And have all the security features of oracle apps.
Components or Parts of OAF-->
-----------------------------
1) BC4J Basic Components for j2ee
2) OC4j --> J2EEE Server (Container)
3) UIX --> User Interface in XML (to design the pages, the source will be in xml Ex: Text filed source)
It was developed by Cabo Company,
Oracle acquired cabo and changes the name.
HTTP is wrap server developed by apache and it is acquired and named as oracle http.
4) AOL/J
Application Object Library / J2EE.
To get the security at ebiz level and implements it in oaf.(function and data security)
5) OA Framework --> Acts as a coordinator, it allows to integrate all the first 4 components.
6) OA Extensions--> Jdeveloper is used to develop extensions.
Jdeveloper--> Allows design, developing, debug and deploys software’s
Types of Jdeveloper :-->
-----------------------
1) Normal and advanced java applications along with oaf (OA Extensions) --> downloaded from only metal ink patch
2) Only java and j2ee --> download from otn.
MVC
End user web server middle server database
HTTP 10.1.2(Forms, reports) 11i
10.1.3(Bpel, webservices, soa)
R12 both 2 and 3 series.
Middle tier has some layers.
1) Specifically 1 layer for interacting with database
i.e called as Model layer
Model Layer:
--------------
It encapsulates all the features to interact with database.
Technologies used:
1) BC4J
2) Top Link sun
3) EJB sun
4) POJO Plain old java objects
5) ADFBC only for adf oracle
Some times web server may be in middle tier server.
Layer wing of sw services.
View Layer:
Part of my applications which is mostly used by my end user.
Interface Technologies in Non Microsoft form
1) HTML
2) JSP
3) JSF
4) UIX we are using it in oaf.
5) PHP
6) PSP
OA.jsp is the global jsp page we can access uix.
Controller
Controls the execution of any Application.
Coordinates view and model.
Controllers in Form based applications:
1) Triggers
Controller Technologies in JAVA:
1) Struts Apache Jakarta
2) JSF SUN
3) OA Controller we are using it in oaf.
JSF allows us to develop both the view and controller.
BC4J:
Components of BC4J:
1) EO - Entity Object
2) VO – View Object
3) Am – Application Module
4) VL – View Link
5) AO – Association Object
6) VAM – Validation Application Module.
7) VVO – Validation View Object
8) EE – Entity Expert
9) PVO – Property View Object
OAF Architecture is more complex than forms.
*************************---------------------------************************
Entity Object :
This object must be based on table/view/synonym
EO must be based on the replica of tables.i.e including all the columns are suggestible.
It must contain a primary key.( If the main table has primary key column then it will automatically made as primary key in eo, if there is no primary key defined in the table then eo makes rowed as primary key column
Always, it should contain who columns for auditing purpose.
We can’t create EO based on multiple tables. To overcome this we create a view and we can use it in EO.
Multiple EO objects can be associated using AO.
Whenever the eo is created then it creates 2 files
1) XML Mandatory Definition file, we can’t update is directly. It contains the table name, columns, data types, mapping of data types, keys info, size etc…
Ex: EmpEO.xml
2) Implementation File Optional File( To write custom business applications)
Generate this file only when some customization needed, otherwise leave it.
It contains accessor methods (get/set methods in java)
Ex: EmpEoImpl.java
Void setSalary ()
{
}
Number getSalary ()
{
}
Each set/get method is mapped with each and every column in EO.
*****************************--------------************************
View Obejct:
Encapsulates the database query.
View can be compared with cursor.
Primarily based on EO.
We can create VO directly based on table/query.
Can be created manually neither based on table nor on EO. I.e. static VO. I.e. called as transient view object.
Must contain the primary key column
Multiple VO can be created based on single EO.
Case:
If you want to add some table / column coming from eo then we need to go to export mode to modify the query and parameters ( oracle style col1, col2, col3 or jdbc style ????)
Types of Files in VO:
1) Mandatory XML File
EX: EmpVO.xml
2) Implementation File : EmpVOImpl.java (when_validate_block )
3) ROW Implementation File : EmpVORowImpl.java (when_validate_row / record)
Business logic is saved in database and other files will be saved in Metadata Repository.
We use XML Importer, import. bat in windows, JPX importer.sh to import data to MDS.
1) EmpVO.xml
2) IBMEmpVO.xml custom file which has custom validations
JPX contains both the old and new page names/ business logic.
Case 1)
If customer wants to display Sal col
1) if the vo is based on query then we can create vo using oaf personalization
2) If the VO is based on EO, then we need to create to another VO including the sal column.
JPX Right click substitute 2 screens old col & new col.
We need to import the changes to mds using jpx importer.
**********************------------------------------***************************
Application Module:
Container of related BC4J Objects(container of am’s , vo ,vl).
Database connection b/w database and page.
Initiates the transactions.
Every page must have the reference to AM.
We pass retainAM=’Y’ as parameter to the other page whenever we are moving from one page to another page.
1 2 3
Types of AM:
1) Root Am Which is associated to Page & Responsible for initiating the transaction. For every page there will be only one Root AM.
2) Nested Am To distribute the transactions.
Files:
1)Mandatory File EmpAM.xml
2)EmpAMImpl.java
*******************------------------------***************************
View Link:
Master Detail Form
View Object of a database.
Association Object:
Join
VAM / VVO / EE:
Validation helper to write validations programmatically.
Declarative validations are treated as client side validations.
And they are created in eo and vo.
PVO:
It is used for partial page rendering (PPR)
Transient transactions
*********************************-**-**-**-**********************************
Controller:
OAControllerImpl is the base class for every controller.
Every method of controller takes 2 parameters.
Methods of Controller:
1) ProcessRequest (OAWebBean, OAPageContext) get
2) ProcessFormRequest (OAWebBean, OAPageContext) post
3) ProcessFormData (OAWebBean, OAPageContext) we can’t change the definition of this method.
OAWebBean:
Text filed
Button
Submit Button
OAPageContext:
Default scope of page is Request.
Application
session
Request
Page
Ways to call page:
Hyperlink get
Type URL get
Submit Button post
HTTP Methods:
Get: Information will be seen on address bar and max 256 characters are sent.
Post: Information is secured and if it exceeds 32767 characters then it will create new packet.
When the page is rendered then processRequest method will be called.
Instead of JPX ,we have to use controller class property to substitute the new controller in place of old controller.
Onion Structure:
E
WML
JAVA_TOP:
Contains Classes, definition bc4j components, look and feel
OC4jJ2EE Server.
Package:
Oracle.apps is mandatory
Oracle.apps...schema. server
EO, AO, VA, VVO, EE
Oracle.apps...server
VO,VL,AM
Oracle.apps...webui
controllers, page
Custom Package:
Custom.oracle.apps...schema. server
Class2:
Jdeveloper Versions:
11i 9.0.3.2 Patch Number: p_4045639
R12 10.1.3 p_5856648
Oracle 11i Jdeveloper is installable. Oracle 10g is not installable, we have to unzip it to use.
Folder Structure:
JdevHome
JdevBin It contains all the binary information
JdevDoc It contains all the documentations and tutorials, ex, api for all the uix,jpx….
Index.html will contain all the information.
Jdeveloper Setup:
We must set JDEV_USER_HOME in environmental variable.
JDEV_USER_HOME=/JDEVHOME/JDEV
Workspace may contain many projects.
Workspace OA Workspace (JWS) Project (JPR – Jdeveloper Project)
Design time database connection
Runtime Database connection we need to place the .dbc file from FND_TOP.
DBC must contain the following variables.
To_task
JDBC URL
JDBC Driver
Host name
Sid
Db port
We need to go the property of projects
Dbc_file_name location
Application user name
Password
Responsibility key
Note:
The responsibility must be assigned to that particular user.
In OAF, error will come from the stack, first 3 lines are sufficient to understand the error.
If you miss oracle.apps it will give some other meaningless error i.e internal error.
If the server and Jdeveloper are not in the same n/w it will take more time to execute the page.
Ouvpn url: https://ouvpn-in.oracle.com
Uername : 2565894.user06
Pwd: Se7veSB
Remote Desktop for development:
eg6191.us.oracle.com
Windows user name and password
Student1/student1
Ebs instance :
http://eg5260.us.oracle.com/OA_HTML/AppsLocalLogin.jsp
Sample Project:
Setup Environmental Variable:
Open Jdeveloper:
Creating Database Connection:
Working on a already developed Page:
FileOpen
fwktester
Fwkdev
To Test the application short name :
We need to Rebuild the code…
We n
We need to zip the class files in my classes folder instead of myprojects to promote it to some other destination.
Once you rebuild the code , then we can see my classes.
Now myclasses will be created
Run the project
Project2:
Create new workspace:
Choose OAWorkspace.if you select workspace, we can’t find the necessary attributes to develop the page.
Give meaningful projet name.
If you check the checkbox then it will automatically create the new project.
Here we are un cheking it to create it manually.
Creating New OA Project:
f
We need to choose the dbc file path and we need to submit the necessary credentials.
Once it is done, the jpr file will be created.
To create New Page:
If the page is a oa component in webtier we need to place the file in webui. Sp that we need to provide the full package name.
Once you created a page then it will create an xml file.
We can change the name of the region and other details as per scenario.
Page Layout components are used for storing images.
Creating Nested Region:
In Message component layout , button will not be displayed.
To Apply Common Property:
Run the Page:
Controller must be defined to go to the next page when clicking on go button.
Even if the contoller is defined , and the page is not opening then we need to check the below conditions.
Debug:
1) Button must be submit button
2) Procedure must be processFormRequest.
3) Controller class must be attached to proper region.
Creating controller:
We need to create the controller for main region.
Now HelloCo.java is created.
Project 3:
SERVER.XML IS USED FOR CUSTOMIZATION AND EXTENSIONS.
Passivation:
It Is the process where the Application status can be stored in a table / database.
ICX_SESSION
Passivation can be activated on a bc4j component i.e AM.
Retention_level:
Values of retention :
1)RESERVE_FULL
2)MANAGE_STATE
SYSTEM PROFILE :
If we want to activate passivation it should be set in “FND PASSIVATION LEVEL”.
If fnd passivation level is set to null/none then it is reserve full otherwise it is manage state.
OAF finds the threads which are idle and assign it’s to new user request and if that thread is activated then it assign the resource back. This is called as activation.
These concepts are inherited from ejb and it added security to it.
SPML Simple Possible Expression Language
We use it in writing email code in OAF.
AM Can’t hold more than one Transient view object (PVO).
Deploying on Server
Fresh Deployment:
Required:
1)Class
2)XML BC4J
3)XML Region , Page MDS
4)JPX (* based on requirement)
We need to pass xml to mds using the import.bat
Jdev Home / Import.bat xml importer
We need to pass parameters while calling import.bat
1) Root Directory
2) Location
3) DB Name / Host String
4) DB User
5) DB Password
We need to zip myclasses and we need to place it in server JAVA_TOP.
Then we need to bounce the server.
SSWAJSP Function OA.jsp? function name or page name=/chandu/oracle/apps/ak/Employee/webui/EmpSearchPG&addBreadCrumb=Y&retainAM=Y
Attach the function to Menu.
Add Menu to responsibility
Add Responsibility to User.
Breadcrum To see visited pages.
Not Required:
1)JWS
2)JPR
3)JAVA
4)XFG
Custom deployment:
6) Class Y
7) JWS N
8) JPR N
9) XML(BC4J) Y
10) JPX (programmatically / non programmatical changes in the project)
Zip my classes folder connet to putty go to JAVA_TOP
Mv zip file to java_top
And unzip the file unzip fil enam e
Mds
C:\> import.bat
Cd jdev\jdevbin\bin\import.bat
Import
Import jdev\jdevhome\myclasses\chandu\oracle\apps\ak\employrr\webui\EmployeePG.xml -rootdir: C:\..classes username a[[s = [assword apps –dbconnectin eg5260.us.oracle.com :1521:eg5260
And we have to do the same procedure for all the other xml files,
Sysadmin functionaladmin function
Jpximport.bat to import jpx files in custom deployment.
Personalization:
User LevelQuery Level,
Admin Level->
Programatical changes can’t be change by admin also.
Java Introduction :-->
j2me--> Micro edition (Reliance incorporated it first)
Class--> Blueprint of specifications
Object--> Instance of class
Package--> Collection of classes...etc
Design Patterns :-> Specification to prepare project using best practices.
EX: Model View Controller (Oaf is using this), Struts,
Framework :->
Fills the gape (EX:b/w c++ vs forms)
Oracle Application Framework :-->
It is a java based, 3Tier web based application, which has linked to oracle application ebiz instance
And have all the security features of oracle apps.
Components or Parts of OAF-->
-----------------------------
1) BC4J Basic Components for j2ee
2) OC4j --> J2EEE Server (Container)
3) UIX --> User Interface in XML (to design the pages, the source will be in xml Ex: Text filed source)
It was developed by Cabo Company,
Oracle acquired cabo and changes the name.
HTTP is wrap server developed by apache and it is acquired and named as oracle http.
4) AOL/J
Application Object Library / J2EE.
To get the security at ebiz level and implements it in oaf.(function and data security)
5) OA Framework --> Acts as a coordinator, it allows to integrate all the first 4 components.
6) OA Extensions--> Jdeveloper is used to develop extensions.
Jdeveloper--> Allows design, developing, debug and deploys software’s
Types of Jdeveloper :-->
-----------------------
1) Normal and advanced java applications along with oaf (OA Extensions) --> downloaded from only metal ink patch
2) Only java and j2ee --> download from otn.
MVC
End user web server middle server database
HTTP 10.1.2(Forms, reports) 11i
10.1.3(Bpel, webservices, soa)
R12 both 2 and 3 series.
Middle tier has some layers.
1) Specifically 1 layer for interacting with database
i.e called as Model layer
Model Layer:
--------------
It encapsulates all the features to interact with database.
Technologies used:
1) BC4J
2) Top Link sun
3) EJB sun
4) POJO Plain old java objects
5) ADFBC only for adf oracle
Some times web server may be in middle tier server.
Layer wing of sw services.
View Layer:
Part of my applications which is mostly used by my end user.
Interface Technologies in Non Microsoft form
1) HTML
2) JSP
3) JSF
4) UIX we are using it in oaf.
5) PHP
6) PSP
OA.jsp is the global jsp page we can access uix.
Controller
Controls the execution of any Application.
Coordinates view and model.
Controllers in Form based applications:
1) Triggers
Controller Technologies in JAVA:
1) Struts Apache Jakarta
2) JSF SUN
3) OA Controller we are using it in oaf.
JSF allows us to develop both the view and controller.
BC4J:
Components of BC4J:
1) EO - Entity Object
2) VO – View Object
3) Am – Application Module
4) VL – View Link
5) AO – Association Object
6) VAM – Validation Application Module.
7) VVO – Validation View Object
8) EE – Entity Expert
9) PVO – Property View Object
OAF Architecture is more complex than forms.
*************************---------------------------************************
Entity Object :
This object must be based on table/view/synonym
EO must be based on the replica of tables.i.e including all the columns are suggestible.
It must contain a primary key.( If the main table has primary key column then it will automatically made as primary key in eo, if there is no primary key defined in the table then eo makes rowed as primary key column
Always, it should contain who columns for auditing purpose.
We can’t create EO based on multiple tables. To overcome this we create a view and we can use it in EO.
Multiple EO objects can be associated using AO.
Whenever the eo is created then it creates 2 files
1) XML Mandatory Definition file, we can’t update is directly. It contains the table name, columns, data types, mapping of data types, keys info, size etc…
Ex: EmpEO.xml
2) Implementation File Optional File( To write custom business applications)
Generate this file only when some customization needed, otherwise leave it.
It contains accessor methods (get/set methods in java)
Ex: EmpEoImpl.java
Void setSalary ()
{
}
Number getSalary ()
{
}
Each set/get method is mapped with each and every column in EO.
*****************************--------------************************
View Obejct:
Encapsulates the database query.
View can be compared with cursor.
Primarily based on EO.
We can create VO directly based on table/query.
Can be created manually neither based on table nor on EO. I.e. static VO. I.e. called as transient view object.
Must contain the primary key column
Multiple VO can be created based on single EO.
Case:
If you want to add some table / column coming from eo then we need to go to export mode to modify the query and parameters ( oracle style col1, col2, col3 or jdbc style ????)
Types of Files in VO:
1) Mandatory XML File
EX: EmpVO.xml
2) Implementation File : EmpVOImpl.java (when_validate_block )
3) ROW Implementation File : EmpVORowImpl.java (when_validate_row / record)
Business logic is saved in database and other files will be saved in Metadata Repository.
We use XML Importer, import. bat in windows, JPX importer.sh to import data to MDS.
1) EmpVO.xml
2) IBMEmpVO.xml custom file which has custom validations
JPX contains both the old and new page names/ business logic.
Case 1)
If customer wants to display Sal col
1) if the vo is based on query then we can create vo using oaf personalization
2) If the VO is based on EO, then we need to create to another VO including the sal column.
JPX Right click substitute 2 screens old col & new col.
We need to import the changes to mds using jpx importer.
**********************------------------------------***************************
Application Module:
Container of related BC4J Objects(container of am’s , vo ,vl).
Database connection b/w database and page.
Initiates the transactions.
Every page must have the reference to AM.
We pass retainAM=’Y’ as parameter to the other page whenever we are moving from one page to another page.
1 2 3
Types of AM:
1) Root Am Which is associated to Page & Responsible for initiating the transaction. For every page there will be only one Root AM.
2) Nested Am To distribute the transactions.
Files:
1)Mandatory File EmpAM.xml
2)EmpAMImpl.java
*******************------------------------***************************
View Link:
Master Detail Form
View Object of a database.
Association Object:
Join
VAM / VVO / EE:
Validation helper to write validations programmatically.
Declarative validations are treated as client side validations.
And they are created in eo and vo.
PVO:
It is used for partial page rendering (PPR)
Transient transactions
*********************************-**-**-**-**********************************
Controller:
OAControllerImpl is the base class for every controller.
Every method of controller takes 2 parameters.
Methods of Controller:
1) ProcessRequest (OAWebBean, OAPageContext) get
2) ProcessFormRequest (OAWebBean, OAPageContext) post
3) ProcessFormData (OAWebBean, OAPageContext) we can’t change the definition of this method.
OAWebBean:
Text filed
Button
Submit Button
OAPageContext:
Default scope of page is Request.
Application
session
Request
Page
Ways to call page:
Hyperlink get
Type URL get
Submit Button post
HTTP Methods:
Get: Information will be seen on address bar and max 256 characters are sent.
Post: Information is secured and if it exceeds 32767 characters then it will create new packet.
When the page is rendered then processRequest method will be called.
Instead of JPX ,we have to use controller class property to substitute the new controller in place of old controller.
Onion Structure:
E
WML
JAVA_TOP:
Contains Classes, definition bc4j components, look and feel
OC4jJ2EE Server.
Package:
Oracle.apps is mandatory
Oracle.apps.
EO, AO, VA, VVO, EE
Oracle.apps.
VO,VL,AM
Oracle.apps.
controllers, page
Custom Package:
Custom.oracle.apps.
Class2:
Jdeveloper Versions:
11i 9.0.3.2 Patch Number: p_4045639
R12 10.1.3 p_5856648
Oracle 11i Jdeveloper is installable. Oracle 10g is not installable, we have to unzip it to use.
Folder Structure:
JdevHome
JdevBin It contains all the binary information
JdevDoc It contains all the documentations and tutorials, ex, api for all the uix,jpx….
Index.html will contain all the information.
Jdeveloper Setup:
We must set JDEV_USER_HOME in environmental variable.
JDEV_USER_HOME=/JDEVHOME/JDEV
Workspace may contain many projects.
Workspace OA Workspace (JWS) Project (JPR – Jdeveloper Project)
Design time database connection
Runtime Database connection we need to place the .dbc file from FND_TOP.
DBC must contain the following variables.
To_task
JDBC URL
JDBC Driver
Host name
Sid
Db port
We need to go the property of projects
Dbc_file_name location
Application user name
Password
Responsibility key
Note:
The responsibility must be assigned to that particular user.
In OAF, error will come from the stack, first 3 lines are sufficient to understand the error.
If you miss oracle.apps it will give some other meaningless error i.e internal error.
If the server and Jdeveloper are not in the same n/w it will take more time to execute the page.
Ouvpn url: https://ouvpn-in.oracle.com
Uername : 2565894.user06
Pwd: Se7veSB
Remote Desktop for development:
eg6191.us.oracle.com
Windows user name and password
Student1/student1
Ebs instance :
http://eg5260.us.oracle.com/OA_HTML/AppsLocalLogin.jsp
Sample Project:
Setup Environmental Variable:
Open Jdeveloper:
Creating Database Connection:
Working on a already developed Page:
FileOpen
fwktester
Fwkdev
To Test the application short name :
We need to Rebuild the code…
We n
We need to zip the class files in my classes folder instead of myprojects to promote it to some other destination.
Once you rebuild the code , then we can see my classes.
Now myclasses will be created
Run the project
Project2:
Create new workspace:
Choose OAWorkspace.if you select workspace, we can’t find the necessary attributes to develop the page.
Give meaningful projet name.
If you check the checkbox then it will automatically create the new project.
Here we are un cheking it to create it manually.
Creating New OA Project:
f
We need to choose the dbc file path and we need to submit the necessary credentials.
Once it is done, the jpr file will be created.
To create New Page:
If the page is a oa component in webtier we need to place the file in webui. Sp that we need to provide the full package name.
Once you created a page then it will create an xml file.
We can change the name of the region and other details as per scenario.
Page Layout components are used for storing images.
Creating Nested Region:
In Message component layout , button will not be displayed.
To Apply Common Property:
Run the Page:
Controller must be defined to go to the next page when clicking on go button.
Even if the contoller is defined , and the page is not opening then we need to check the below conditions.
Debug:
1) Button must be submit button
2) Procedure must be processFormRequest.
3) Controller class must be attached to proper region.
Creating controller:
We need to create the controller for main region.
Now HelloCo.java is created.
Project 3:
SERVER.XML IS USED FOR CUSTOMIZATION AND EXTENSIONS.
Passivation:
It Is the process where the Application status can be stored in a table / database.
ICX_SESSION
Passivation can be activated on a bc4j component i.e AM.
Retention_level:
Values of retention :
1)RESERVE_FULL
2)MANAGE_STATE
SYSTEM PROFILE :
If we want to activate passivation it should be set in “FND PASSIVATION LEVEL”.
If fnd passivation level is set to null/none then it is reserve full otherwise it is manage state.
OAF finds the threads which are idle and assign it’s to new user request and if that thread is activated then it assign the resource back. This is called as activation.
These concepts are inherited from ejb and it added security to it.
SPML Simple Possible Expression Language
We use it in writing email code in OAF.
AM Can’t hold more than one Transient view object (PVO).
Deploying on Server
Fresh Deployment:
Required:
1)Class
2)XML BC4J
3)XML Region , Page MDS
4)JPX (* based on requirement)
We need to pass xml to mds using the import.bat
Jdev Home / Import.bat xml importer
We need to pass parameters while calling import.bat
1) Root Directory
2) Location
3) DB Name / Host String
4) DB User
5) DB Password
We need to zip myclasses and we need to place it in server JAVA_TOP.
Then we need to bounce the server.
SSWAJSP Function OA.jsp? function name or page name=/chandu/oracle/apps/ak/Employee/webui/EmpSearchPG&addBreadCrumb=Y&retainAM=Y
Attach the function to Menu.
Add Menu to responsibility
Add Responsibility to User.
Breadcrum To see visited pages.
Not Required:
1)JWS
2)JPR
3)JAVA
4)XFG
Custom deployment:
6) Class Y
7) JWS N
8) JPR N
9) XML(BC4J) Y
10) JPX (programmatically / non programmatical changes in the project)
Zip my classes folder connet to putty go to JAVA_TOP
Mv zip file to java_top
And unzip the file unzip fil enam e
Mds
C:\> import.bat
Cd jdev\jdevbin\bin\import.bat
Import
Import jdev\jdevhome\myclasses\chandu\oracle\apps\ak\employrr\webui\EmployeePG.xml -rootdir: C:\..classes username a[[s = [assword apps –dbconnectin eg5260.us.oracle.com :1521:eg5260
And we have to do the same procedure for all the other xml files,
Sysadmin functionaladmin function
Jpximport.bat to import jpx files in custom deployment.
Personalization:
User LevelQuery Level,
Admin Level->
Programatical changes can’t be change by admin also.
Tables Used by Purge Concurrent Request Concurrent Program
Log and Output files retention policy can be controlled by you at o/s level. Once you decide the retention policy for these logs and output files, the Purge Concurrent Request Concurrent Program or the Manager Data Concurrent Program will act, based on the information located in the database tables to determine which files it can safely purge and which it needs to retain. The retention policy for these log and output files needs to be determined by your Applications users. Archiving policy needs to be determined by the Internal Project Management staff. These policies can then be implemented, in part, through these concurrent requests. You can maintain statistics on concurrent requests, even after they have been purged from the system, by setting the Concurrent: Collect Request Statistics profile option to yes. These statistics can help you determine where best to start tuning long running or time-intensive concurrent requests.
Purge Concurrent Request Concurrent Program Use the following tables:
1) FND_CONCURRENT_REQUESTS: Contains a complete history of all concurrent requests (both past history and those scheduled to run in the future).
2) FND_RUN_REQUESTS: Stores information about the reports in a report set that a user submits including the report set’s parameter values.
3) FND_CONC_REQUEST_ARGUMENTS: Records all arguments passed by Concurrent Managers to concurrent requests as those requests are running.
4) FND_DUAL: Records when a request does not update any database tables.
5) FND_CONCURRENT_PROCESSES: Records information about Oracle Applications processes and OS processes.
6) FND_CONC_STAT_LIST: Collects runtime performance statistics for concurrent requests.
7) FND_CONC_STAT_SUMMARY: Contains Concurrent Program performance statistics generated by the Purge Concurrent Request program or the manager data program. These programs use the data in FND_CONC_STAT_LIST to compute these statistics.
Purge Concurrent Request Concurrent Program Use the following tables:
1) FND_CONCURRENT_REQUESTS: Contains a complete history of all concurrent requests (both past history and those scheduled to run in the future).
2) FND_RUN_REQUESTS: Stores information about the reports in a report set that a user submits including the report set’s parameter values.
3) FND_CONC_REQUEST_ARGUMENTS: Records all arguments passed by Concurrent Managers to concurrent requests as those requests are running.
4) FND_DUAL: Records when a request does not update any database tables.
5) FND_CONCURRENT_PROCESSES: Records information about Oracle Applications processes and OS processes.
6) FND_CONC_STAT_LIST: Collects runtime performance statistics for concurrent requests.
7) FND_CONC_STAT_SUMMARY: Contains Concurrent Program performance statistics generated by the Purge Concurrent Request program or the manager data program. These programs use the data in FND_CONC_STAT_LIST to compute these statistics.
PO Interview Questions
1. If Purchase Invoice amount sent by the supplier is more than the PO amount and Invoice has to be passed by invoice amount only. How this can be achieved.
Ans: Manually Release the hold by going into the invoice hold option. Thus how we do it in Purchasing module of oracle applications.
2. How you will hide the button Site on the Supplier master form.
Ans: A restricted list of functions a user can perform. For example, two responsibilities may have access to the same window, but one responsibility’s window may have additional function buttons that the other responsibility’s window does not have.
3. When can you make payment for the supplier invoice weather at the time of receipt of material or after receiving
Ans: Invoice only.
4. Payment due days if 30 days means will it be from invoice date or material receiving date.
Ans: Due days calculation is based on what you set up in the PAYMENT tab window of Supplier Master form.
It has Four options:
1. Goods Received
2. Invoice
3. Invoice received
4. System
5. Where do you define matching option for purchasing.
Ans: This is also defined in the supplier master form under tab ‘Control’ window.Two options are given as Purchase Order or Receipts.
In receiving tab define whether it is 2 way, 3 way, 4 way match.
6. What is security type and Cross-validation rules.
Ans: There are two ways security can be defined. One at individual segment level and One for the entire code combination.
Security type with hierarchical security / non-hierarchical / no security is defined in the value set going to be attached with the individual segment. Here you exclude the segment value which user will not see at time of transaction capturing. Where are Security rule is defined including and excluding the entire code combination which will be seen at the time of transaction and if excluded combination is selected then user define message will appear. In both the case security rule is required to be defined.
Security defined for segment is assigned to your application and responsibilities.
Security rule defined for application, structure and flexfield title with inclusion and exclusion.
7. How many types of Purchase Order are there.
Ans: Following are types
i. Standard
ii. Blanket
iii. Contract
iv. Planned
8. What do you mean by planned purchase order.
Ans: A planned purchase order is a long-term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.
9. What do you mean by blanket purchase order.
Ans: You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.
10. Once these two purchase orders are prepared what is the next step.
Ans:You issue scheduled release and blanket release against a planned purchase order to place the actual orders.
11. What is approval hierarchy for Purchase Order.
Ans: Position and Employee. If HR Module is not installed then only employee option is used Set up hierarchy for Purchasing
1. Organization.
2. Financial and Payable.
3. Payment document or supplier site for payment accounting entries.
4. Supplier and Supplier Site.
Payment terms as per supplier site or purchase order if both are there then finally takes from purchase order.
12. Where do you define matching option for purchasing.
Ans: This is also defined in the supplier master form under tab ‘Control’ window.Two options are given as Purchase Order or Receipts.
In receiving tab define whether it is 2 way, 3 way, 4 way match.
13. What happen if price is varying in PO and Invoice how you will resolve it.
Ans: ====================================
14. What posting is done at time of Receipts of material or Purchase Order match directly with Invoice without the receipt consideration.
Ans: ======================================
15. Where you will define Matching options. Is it required to define for all the suppliers or it comes as default. From where it default the setting.
Ans: ===========================================
16. In purchasing Receipts of the inventory is stored at what level in Multi Org setup.
Ans: ===========================================
17. Can you match invoice directly with the purchase order without matching with receipts.
Ans: ===========================================
Ans: Manually Release the hold by going into the invoice hold option. Thus how we do it in Purchasing module of oracle applications.
2. How you will hide the button Site on the Supplier master form.
Ans: A restricted list of functions a user can perform. For example, two responsibilities may have access to the same window, but one responsibility’s window may have additional function buttons that the other responsibility’s window does not have.
3. When can you make payment for the supplier invoice weather at the time of receipt of material or after receiving
Ans: Invoice only.
4. Payment due days if 30 days means will it be from invoice date or material receiving date.
Ans: Due days calculation is based on what you set up in the PAYMENT tab window of Supplier Master form.
It has Four options:
1. Goods Received
2. Invoice
3. Invoice received
4. System
5. Where do you define matching option for purchasing.
Ans: This is also defined in the supplier master form under tab ‘Control’ window.Two options are given as Purchase Order or Receipts.
In receiving tab define whether it is 2 way, 3 way, 4 way match.
6. What is security type and Cross-validation rules.
Ans: There are two ways security can be defined. One at individual segment level and One for the entire code combination.
Security type with hierarchical security / non-hierarchical / no security is defined in the value set going to be attached with the individual segment. Here you exclude the segment value which user will not see at time of transaction capturing. Where are Security rule is defined including and excluding the entire code combination which will be seen at the time of transaction and if excluded combination is selected then user define message will appear. In both the case security rule is required to be defined.
Security defined for segment is assigned to your application and responsibilities.
Security rule defined for application, structure and flexfield title with inclusion and exclusion.
7. How many types of Purchase Order are there.
Ans: Following are types
i. Standard
ii. Blanket
iii. Contract
iv. Planned
8. What do you mean by planned purchase order.
Ans: A planned purchase order is a long-term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.
9. What do you mean by blanket purchase order.
Ans: You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.
10. Once these two purchase orders are prepared what is the next step.
Ans:You issue scheduled release and blanket release against a planned purchase order to place the actual orders.
11. What is approval hierarchy for Purchase Order.
Ans: Position and Employee. If HR Module is not installed then only employee option is used Set up hierarchy for Purchasing
1. Organization.
2. Financial and Payable.
3. Payment document or supplier site for payment accounting entries.
4. Supplier and Supplier Site.
Payment terms as per supplier site or purchase order if both are there then finally takes from purchase order.
12. Where do you define matching option for purchasing.
Ans: This is also defined in the supplier master form under tab ‘Control’ window.Two options are given as Purchase Order or Receipts.
In receiving tab define whether it is 2 way, 3 way, 4 way match.
13. What happen if price is varying in PO and Invoice how you will resolve it.
Ans: ====================================
14. What posting is done at time of Receipts of material or Purchase Order match directly with Invoice without the receipt consideration.
Ans: ======================================
15. Where you will define Matching options. Is it required to define for all the suppliers or it comes as default. From where it default the setting.
Ans: ===========================================
16. In purchasing Receipts of the inventory is stored at what level in Multi Org setup.
Ans: ===========================================
17. Can you match invoice directly with the purchase order without matching with receipts.
Ans: ===========================================
Query for Vendor HST Payment Details
SELECT ail.creation_date f01 --"DATE"
, (SELECT SUM(amount)
FROM ap_invoice_distributions_all
WHERE invoice_id = ai.invoice_id
AND TRUNC(creation_date) = TRUNC(ail.creation_date)) f02 --"AMOUNT RECEIVED ON THIS DATE"
, ail.amount f03 -- "HST AMOUNT"
, pov.vendor_name f04 -- " Vendor Name"
, att.NAME f05 -- "Payment Term Name
, att.creation_date f06 -- "Payment Creation Date"
FROM ap_invoice_distributions_all ail
, ap_tax_codes_all atc
, ap_invoices_all ai
, po_vendor_sites_all povs
, po_vendors pov
, ap_terms_tl att
WHERE atc.tax_id = ail.tax_code_id
AND ai.invoice_id = ail.invoice_id
AND ail.line_type_lookup_code = 'TAX'
AND TRUNC(ail.creation_date) >= '01-JUL-2010'
AND atc.NAME LIKE '%HST%'
AND att.term_id = povs.terms_id
AND povs.vendor_id = pov.vendor_id
AND povs.vendor_site_id = ai.vendor_site_id
AND att.LANGUAGE = USERENV('LANG')
ORDER BY ai.invoice_id
, ai.vendor_site_id;
, (SELECT SUM(amount)
FROM ap_invoice_distributions_all
WHERE invoice_id = ai.invoice_id
AND TRUNC(creation_date) = TRUNC(ail.creation_date)) f02 --"AMOUNT RECEIVED ON THIS DATE"
, ail.amount f03 -- "HST AMOUNT"
, pov.vendor_name f04 -- " Vendor Name"
, att.NAME f05 -- "Payment Term Name
, att.creation_date f06 -- "Payment Creation Date"
FROM ap_invoice_distributions_all ail
, ap_tax_codes_all atc
, ap_invoices_all ai
, po_vendor_sites_all povs
, po_vendors pov
, ap_terms_tl att
WHERE atc.tax_id = ail.tax_code_id
AND ai.invoice_id = ail.invoice_id
AND ail.line_type_lookup_code = 'TAX'
AND TRUNC(ail.creation_date) >= '01-JUL-2010'
AND atc.NAME LIKE '%HST%'
AND att.term_id = povs.terms_id
AND povs.vendor_id = pov.vendor_id
AND povs.vendor_site_id = ai.vendor_site_id
AND att.LANGUAGE = USERENV('LANG')
ORDER BY ai.invoice_id
, ai.vendor_site_id;
Subscribe to:
Posts (Atom)