NALAM Oracle Apps Technical : Interfaces in Oracle Apps

Mostly an oracle apps technical consultant will be working on RICE components.

R--Reports
I--Interfaces
C--Conversions
E--Extensions (Forms personalization)

Reports:
For suppose I'm running a business named 'ALAM' which is wide spread across the globe.Now,I want to know how my business is running across the globe.I will ask a tech guy(oracle apps technical consultant) to develop a report. In that report I ask him to simply print the details of profit or loss,of every branch of my business across the world.He(tech guy) will come up with the report to me then I will come to know whether my company is running fine or not.Thereby, I can take necessary decisions to run my business fine.
Clearly we have seen that report will be developed by technical person,who must know the report building.
We build a report using sql,pl/sql.So,if your are strong in sql,pl/sql then its not a big deal to learn the report building.Its enough to learn the report developer tool.

Conversion:
As I said earlier I'm running a 'ALAM' business(which is factious).Suppose I'm using excel sheets to store the data.Now I want to install oracle apps(E-Business Suite) in my company. So,What ever data that is present in my excel sheets must also be present in my oracle apps tables(A table is a collection of rows and columns).Now I ask a tech guy to write a code such that my excel sheet data will get into oracle apps base tables.Then that tech guy will use 'conversion' to get the data from the legacy system(in our case it is excel sheet) into oracle apps base tables.

Interfaces:
Interfaces are similar to conversions.Conversion is a one-time process where as Interface is on-going process every now and then.We run interfaces daily or periodically.
Interfaces are of two types
1)Inbound Interface
2)Outbound Interface
Inbound Interface:Transferring the data from the legacy system(in our case it is excel sheet) into the Oracle apps base tables.
Outbound Interface:Transferring the data from the Oracle apps base tables into the legacy system(It might be any of these SAP,People soft etc).

Extensions:
Extensions are nothing but personalizing the forms.In oracle e-business suite we have some where around 5000 to 6000 forms.In my 'ALAM' business i want to customise the po(purchase order) form of the oracle apps then i ask the tech guy to do that.He will use form builder tool to do that.

Ur's
AmarAlam

Interface Table Names in Oracle Apps

Posted by Amar Alam at 07:42 Labels: Interfaces 3 comments

oracle financials – payables - invoices
ap_invoices_interface
ap_invoice_lines_interface

oracle financials – receivables - customers
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
hz_party_interface

oracle financials – receivables - invoices
ra_interface_lines_all
ra_interface_distributions_all
ra_interface_salescredits_all

oracle financials – receivables – lock box
ar_payments_interface_all

oracle financials – receivables - tax
ar_tax_interface

oracle financials - cash management – bank statements
ce_statement_headers_int_all
ce_statement_lines_interface

oracle financials - fixed assets
fa_inv_interface

oracle financials - general ledger – journal entry
gl_interface

oracle manufacturing – cost management
cst_pc_item_cost_interface
cst_pc_cost_det_interface

oracle manufacturing - inventory
mtl_replenish_headers_int
mtl_replenish_lines_int
mtl_serial_numbers_interface
mtl_system_items_interface
mtl_transaction_lots_interface
mtl_transactions_interface

oracle manufacturing – purchasing – purchase orders
po_headers_interface
po_lines_interface
po_distributions_interface
po_requisitions_interface_all
po_req_dist_interface_all
po_reschedule_interface

oracle manufacturing – purchasing - receiving
rcv_headers_interface
rcv_transactions_interface
rcv_lots_interface
rcv_serials_interface

oracle manufacturing - order entry – sales orders
so_headers_interface_all
so_header_attributes_interface
so_lines_interface_all
so_line_attributes_interface
so_line_details_interface
so_price_adjustments_interface
so_sales_credits_interface

oracle manufacturing - master scheduling/mrp
mrp_forecast_interface
mrp_schedule_interface

oracle manufacturing - shop floor management
wsm_lot_job_interface
wsm_starting_lots_interface
wsm_lot_move_txn_interface

oracle manufacturing - quality
qa_results_interface

oracle manufacturing - work in process
wip_move_txn_interface
cst_comp_snap_interface
wip_cost_txn_interface
wip_job_schedule_interface
wip_job_dtls_interface

Ur's
Amar Alam

Creating Purchase Order Requisitions

Posted by Amar Alam at 00:26 Labels: API's, Interfaces, PO 0 comments

Below is the example to create a requisition.

We just need to insert data into Interface table and then we need to call standard oracle import program.

In below example, i have created a Internal Requisition.

************************************************************************************************
DECLARE
l_request_id NUMBER;
BEGIN
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (interface_source_code,
source_type_code,
requisition_type,
destination_type_code,
item_id,
item_description,
quantity,
authorization_status,
preparer_id,
autosource_flag,
uom_code,
destination_organization_id,
deliver_to_location_id,
deliver_to_requestor_id,
need_by_date,
gl_date,
charge_account_id,
org_id,
suggested_vendor_id,
suggested_vendor_site_id,
unit_price,
creation_date,
created_by,
last_update_date,
last_updated_by,
batch_id
)
VALUES ('INV',
'INVENTORY',
'INTERNAL',
'INVENTORY',
1831768,
'leadsets',
50,
'APPROVED',
48880,
'P',
'EA',
2609,
75018,
48880,
SYSDATE,
SYSDATE,
237125,
2592,
1058,
607,
150,
SYSDATE,
59944,
SYSDATE,
59944,
454
);

COMMIT;

l_request_id :=
fnd_request.submit_request (application => 'PO',
program => 'REQIMPORT',
argument1 => 'INV',
argument2 => '454',
argument3 => 'ALL',
argument4 => '',
argument5 => '',
argument6 => 'N');
COMMIT;
DBMS_OUTPUT.put_line ('request_id - ' || l_request_id);
END;
/

Item Revision in Oracle Applications

Whenever item is created, default Revision will be assigned to item.

We can also modify the item Revision of an item.

Base table for Item Revision is Mtl_Item_Revisions.

To Modify the item revision, we need to insert new record in Mtl_item_revisions_interface.

Following are the important columns in Mtl_Item_Revisions_Interface

a. Inventory_item_id
b. Organization_id
c. Revision
d. Process_Flag
e. set_process_id
f. Transaction_type

Below code helps you to unserstand how to create a Item Revision.

Code to Import Item Revision
-------------------------------------

DECLARE
l_error VARCHAR2(1000) :=NULL;
l_error_message VARCHAR2(1000) :=NULL;
v_flag NUMBER :=0;
v_date DATE := SYSDATE;
v_user_id fnd_user.user_id%TYPE := FND_PROFILE.VALUE('USER_ID');
v_request_id NUMBER :=0;
v_rev_org_id NUMBER :=0;
v_rev_item_id NUMBER :=0;
l_request_id NUMBER :=0;
v_revision NUMBER :=0;
v_phase VARCHAR2(240);
v_status VARCHAR2(240);
v_request_phase VARCHAR2(240);
v_request_status VARCHAR2(240);
v_finished BOOLEAN;
v_message VARCHAR2(240);

BEGIN
BEGIN
---------Getting Organization Id for Item Revision----------------
BEGIN
SELECT organization_id
INTO v_rev_org_id
FROM mtl_parameters
WHERE organization_code = c_item_revision_rec.organization_code;
EXCEPTION
WHEN OTHERS THEN
l_error := c_item_revision_rec.organization_code' does not exist for the item ''Item Name';
l_error_message := l_error_messagel_error ;
v_flag :=1;
dbms_output.put_line( l_error);
END;
------Checking whether Item exists in Oracl 11i for Item revision ---------------
BEGIN
SELECT inventory_item_id
INTO v_rev_item_id
FROM mtl_system_items_b msi,
mtl_parameters mp
WHERE 1 = 1
AND msi.organization_id = mp.organization_id
AND msi.segment1 = 'Item_Name'
AND mp.organization_code = 'Org_Code';
EXCEPTION
WHEN OTHERS THEN
l_error := 'Item Name''--''Item Does not exist in Oracle 11i';
l_error_message := l_error_messagel_error ;
v_flag :=1;
dbms_output.put_line( l_error);
END;
------Getting Item count inserted in the interface table mainly used for Synchronizng the Effectivity date----
BEGIN
SELECT count(1)
INTO v_count
FROM MTL_ITEM_REVISIONS
WHERE inventory_item_id = v_rev_item_id;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( 'Error in counting the Item revision available in Interface');
v_flag :=1;
END;

IF v_flag = 0 THEN
BEGIN
---------Inserting into Revision Table---------
v_revision := (0.003472 * ( 1 + v_count ));

INSERT INTO MTL_ITEM_REVISIONS_INTERFACE
( INVENTORY_ITEM_ID
,ORGANIZATION_ID
,REVISION
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,IMPLEMENTATION_DATE
,EFFECTIVITY_DATE
,PROCESS_FLAG
,TRANSACTION_TYPE
,SET_PROCESS_ID
)
VALUES
( v_rev_item_id
,v_rev_org_id
,'Revision Number'
,v_date
,v_user_id
,v_date
,v_user_id
,(SYSDATE+v_revision)
,(SYSDATE+v_revision)
,1 --PROCESS_FLAG(1 for pending)
,'CREATE'
,1 --SET_PROCESS_ID
);
COMMIT;

EXCEPTION
WHEN OTHERS THEN
l_error := 'Error in inserting Item Revision to Interface table ''and error is 'SQLERRM;
l_error_message := l_error_messagel_error;
dbms_output.put_line( l_error);
END;
ELSE
dbms_output.put_line( 'Program failed in Initial validation ');
END IF; --End of If l_flag=0

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( 'No Revision Exists for any Item');
END;

-- Calling Item Import Program for Revision----
BEGIN
dbms_output.put_line('--Submitting Item Import Program for assigning revision to the Item--');
l_request_id := apps.Fnd_Request.submit_request ( 'INV',
'INCOIN',
NULL, -- Description
NULL, -- Start Time
FALSE, -- Sub Request
1, -- All Organizations
1, -- Validate Items,
1, -- Process Items
1, -- Delete Process Rows
1, -- Process Set
1 ); -- CREATE

COMMIT;
DBMS_output.Put_line('Item Import Program submitted');

IF ( l_request_id = 0 ) THEN
dbms_output.put_line( 'Submission of Import failed');
END IF;

-- Wait for request to run the import Program to Finish
v_finished := fnd_concurrent.wait_for_request (request_id => l_request_id,
interval => 0,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_request_phase,
dev_status => v_request_status,
message => v_message);

DBMS_output.Put_line('Item Import Program for Revision is Waiting');
DBMS_output.Put_line('Request Phase : ' v_request_phase);
DBMS_output.Put_line('Request Status : ' v_request_status );
DBMS_output.Put_line('Request ID : ' l_request_id );

IF ( UPPER(v_request_status) = 'NORMAL') THEN
DBMS_output.Put_line('Item Import Program completed succesfully');
ELSE
DBMS_output.Put_line('Item Import Program completed with error. Check Mtl_interface_errors table for Knowing about the error');
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_output.Put_line('Error Occured during calling Item Import Program for Revision. 'SQLERRM);
END;

commit;
dbms_output.put_line('End of Revision');

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( 'Error in Item Revision Program and error is :'SQLERRM);
END;

Import Item from Master org to Child Org Using Item Interface

declare
v_organization_id NUMBER := 0;
v_master_org NUMBER :=0;
v_request_id NUMBER := 0;
v_phase VARCHAR2(240);
v_status VARCHAR2(240);
v_request_phase VARCHAR2(240);
v_request_status VARCHAR2(240);
v_finished BOOLEAN;
v_message VARCHAR2(240);
v_item_id NUMBER := 0;

Begin
--Getting the Organization id
BEGIN
SELECT Organization_id,master_organization_id
INTO v_organization_id,v_master_org
FROM mtl_parameters mp
WHERE mp.organization_code = 'C1'; --C1 is the Child Organization Code
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in getting the Organization id for Organization code V1 and error is '||SUBSTR(SQLERRM,1,200));
END;

--Getting the Inventory Item id of the Item which is available in Master Organization
SELECT inventory_item_id
INTO v_item_id
from mtl_system_items_b
where segment1 = 'Existing Item Name'
and organization_id = v_master_org;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in getting the inventory item id for Item and error is '||SUBSTR(SQLERRM,1,200));
END;

--Inserting into Item interface table
BEGIN
INSERT INTO mtl_system_items_interface
(inventory_item_id,
organization_id,
process_flag,
set_process_id,
transaction_type
)
values
( v_item_id,
v_organization_id,
1,
1,
'CREATE'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in inserting record in interface table and error is '||SUBSTR(SQLERRM,1,200));
END;
END;

--Submit the item import program in Create Mode to Assign existing Item
BEGIN

dbms_output.put_line('--Submitting Item Import Program for Item--');
v_request_id := Fnd_Request.submit_request (
application => 'INV',
program => 'INCOIN',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 1,
argument2 => 1,
argument3 => 1, --Group ID option (All)
argument4 => 1, -- Group ID Dummy
argument5 => 1, -- Delete processed Record
argument6 => 1, -- Set Process id
argument7 => 1 -- Create item
);
COMMIT;
dbms_output.put_line('Item Import Program submitted');

IF ( v_request_id = 0 ) THEN
dbms_output.put_line( 'Item Import Program Not Submitted');
END IF;

-- Wait for request to run the import Program to Finish
v_finished := fnd_concurrent.wait_for_request (request_id => v_request_id,
interval => 0,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_request_phase,
dev_status => v_request_status,
message => v_message);

dbms_output.put_line('Request Phase : '|| v_request_phase );
dbms_output.put_line('Request Status : ' || v_request_status );
dbms_output.put_line('Request id : '||v_request_id );

--Testing end status
IF ( UPPER(v_request_status) = 'NORMAL') THEN
dbms_output.put_line( 'Item Import Program Completed Normally');
ELSE
dbms_output.put_line( 'Item Import Program completed with error. Check Mtl_interface_error table for the transaction_id');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in Submitting Item Import Program and error is '||SUBSTR(SQLERRM,1,200));
END;
END;

INTRO TO INTERFACES(For Freshers to oracle Apps)

What is a interface?
In terms of oracle applications interface is a communication channel that allows the data to move in and out of the system.

How many types of interfaces are there?
1.INBOUND INTERFACES
2.OUTBOUND INTERFACES

Inbound Inter Face: The one which allows data to get into oracle application from outside is called inbound interface.

Out Bound Interface:The one which allows data to get data from oracle applications to other systems is called outbound interface.

What are different types of inbound interfaces available?
1. Open interfaces
2. API's(Application Program Interface)
3. EDI(Electronic Data interchange)--Mainly used for automation transactions with third party systems
4. XML GATEWAY --Mainly used for automation transactions with third party systems
5. WEBADI--Used for uploading data from excel with the scope to have some validations--mainly used for one time loading..Just like sqlloader+validation program..
5. PLSQL Packages for Reading XML Data--Use this in case of importing non standard transactions

What are different types of outbound interfaces available?
1. Custom programs where we pull data to a csv file using UTL_FILE in the required format
2. EDI
3. XMLGATEWAY
4. PLSQL Packages for generating XML

what is the difference between OPEN INTERFACE & API's?
OPEN INTERFACE:
I don't see much difference between the open other than the way we load data.
In case of open interface the data is loaded in to open interface table like GL_INTERFACE or Sales Order Interface(OE_ORDER_HEADERS_IFACE_ALL).
Run the interface import program.
This will validate the data and put it into oracle applications.All the invalid records are marked as Error.
One thing is there are GUI Screens available for most of these interface where you check the errror message correct it there only and resubmit the interface.
From the technical perspective there are Error tables available for each interface

API:
API's are the oracle built packages where the validation logic is wrapped inside a package and the data is passed as parameters to the API.
Most of these api's use the PLSQL tables as parameters to take the advantage of bulk binding concepts for faster loading data.
They will have the two OUT parameters to throw back the error code and message in case of data validation failure
Apis' are comparatively faster than open interfaces.
If a API's and open interface are available it is better to load through API's.If the records are more...

This is what i understand till date...Plz put your comment in case of i am wrong any thing more that would be helpful to make this better...

What are Interface table in AP, AR,GL ?

AP INTERFACE TABLE: 1). AP_INTERFACE_CONTROLS.
2). AP_INTERFACE_REJECTIONS
3). AP_INVOICE_INTERFACE
4). AP_INVOICE_LINES_INTERFACE.

AR INTERFACE TABLE: 1). AR_PAYMENTS_INTERFACE_ALL
2). AR_TAX_INTERFACE
3). HZ_PARTY_INTERFACE
4). HZ_PARTY_INTERFACE_ERRORS
5). RA_CUSTOMERS_INTERFACE_ALL
6). RA_INTERFACE_DISTRIBUTIONS_ALL
7). RA_INTERFACE_ERRORS_ALL
8). RA_INTERFACE_LINES_ALL
9). RA_INTERFACE_SALESCREDITS_ALL

GLINTERFACE TABLE: 1). GL_BUDGET_INTERFACE
2). GL_DAILY_RATES_INTERFACE
3). GL_IEA_INTERFACE
4). GL_interface
5). GL_INTERFACE_CONTROL
6). GL_INTERFACE_HISTORY

UTL_FILE Example Using Cursor

CREATE OR REPLACE PROCEDURE xxaj_utl_file

v_file_handle UTL_FILE.FILE_TYPE;

CURSOR c_supplier IS

select a.vendor_name,

a.segment1 Vendror#,

site.vendor_site_code,

site.address_line1,

site.ADDRESS_LINE2,

CITY,

STATE,

country,

zip

FROM ap_suppliers a,

ap_supplier_sites_all site

WHERE a.vendor_id = site.vendor_id

AND site.org_id =204

ORDER BY 1,3;

BEGIN

v_file_handle := UTL_FILE.FOPEN('/usr/tmp','MY_UTL_SUPP.csv', 'W');

UTL_FILE.PUT_LINE(v_file_handle,'VendorNmae'||'|'||

'Vendor#'||'|'||

'vendor_site_code'||'|'||

'Address1'||'|'||

'Address2'||'|'||

'City'||'|'||

'State'||'|'||

'County'||'|'||

'ZIP'

);

FOR c_rec IN c_supplier LOOP

UTL_FILE.PUT_LINE(v_file_handle,c_rec.vendor_name||'|'||

c_rec.Vendror#||'|'||

c_rec.vendor_site_code||'|'||

c_rec.address_line1||'|'||

c_rec.address_line2||'|'||

c_rec.CITY||'|'||

c_rec.STATE||'|'||

c_rec.country||'|'||

c_rec.zip

);

END LOOP;

UTL_FILE.PUT_LINE(v_file_handle,'END OF Extraction');

UTL_FILE.FCLOSE(v_file_handle);

COMMIT;

/* Handle UTL_FILE exceptions */

EXCEPTION

WHEN UTL_FILE.INVALID_OPERATION THEN

utl_file.fclose_all;

dbms_output.put_line(' invalid operation');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN

utl_file.fclose_all;

dbms_output.put_line(' invalid filehandle');

WHEN UTL_FILE.INVALID_PATH THEN

utl_file.fclose_all;

dbms_output.put_line(' invalid path');

WHEN UTL_FILE.INTERNAL_ERROR THEN

utl_file.fclose_all;

dbms_output.put_line(' internal error');

WHEN UTL_FILE.INVALID_MODE THEN

utl_file.fclose_all;

dbms_output.put_line(' invalid mode');

WHEN UTL_FILE.WRITE_ERROR THEN

utl_file.fclose_all;

dbms_output.put_line(' write error');

WHEN UTL_FILE.READ_ERROR THEN

utl_file.fclose_all;

dbms_output.put_line(' read error');

WHEN OTHERS THEN

utl_file.fclose_all;

dbms_output.put_line('other exception');

END xxaj_utl_file;

Pseudo Code For PO Interface

CREATE OR REPLACE PROCEDURE xxaj_po_interface
l_interface_header_id number;
l_interface_line_id NUMBER;
BEGIN
SELECT po_headers_interface_s.NEXTVAL
INTO l_interface_header_id
FROM dual;
dbms_output.put_line('Header SEQ:'||l_interface_header_id);
INSERT INTO po_headers_interface (batch_id
,interface_header_id
, interface_source_code
, action
, org_id
, document_type_code
, agent_id
, vendor_id
, vendor_site_id
, approval_status
,comments
, last_update_date
, last_updated_by
, creation_date
, created_by
)
VALUES ( 1001 --Batch ID
, l_interface_header_id --Header Sequence
, 'CONVERSION' --Source Code
, 'ORIGINAL' --Action
, 204 --Org ID
, 'STANDARD' --PO/Document Type
, 25 --Agent ID
, 6 --Vendor ID
, 7 --Vendor Site ID
, 'APPROVED' --PO status
, 'This is my Interface PO' --Comments
, SYSDATE
, 1318
, SYSDATE
, 1318
);
dbms_output.put_line('Header Details Inserted...');
BEGIN
SELECT po_lines_interface_s.NEXTVAL
INTO l_interface_line_id
FROM dual;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line( 'Error In Getting lines Seuence Number po_lines_interface_s.NEXTVAL..');
END;
INSERT INTO po_lines_interface (interface_line_id
, interface_header_id
,action
, line_num
, shipment_num
, line_type
, item
, uom_code
, quantity
, unit_price
,ship_to_organization_code
, ship_to_location
, need_by_date
, promised_date
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by )
VALUES ( l_interface_line_id --Interface Line ID
, l_interface_header_id --Interface Header Id
,'ORIGINAL' --Action
, 1 --Line Num
, 1 --Shipment Num
, 'Goods' --Line Type
, 'AS54888' --Item Name
, 'Ea' --Uom Code
, 10 --Quantity
, 3 --Price
, 'V1' --Ship to Organization Code
, 'V1- New York City' -- 'One Time Address'
, sysdate+1 --Need by Date
, sysdate+1 --Promise Date
, SYSDATE --Last Update Date
, 1318
, 1318
, SYSDATE
, 1318);
---======================================
---Inserting PO Distributions
INSERT INTO po_distributions_interface (
   interface_header_id
   , interface_line_id
   , interface_distribution_id
   , quantity_ordered
   , charge_account_id
   , last_update_date
   , last_updated_by
   , last_update_login
   , creation_date
   , created_by
)
VALUES ( l_interface_header_id --Interface Header Id
, l_interface_line_id --Interface Line ID
, po_distributions_interface_s.NEXTVAL --Interface Distribution id
, 10 --Quantity
, 13401
, SYSDATE
, 1318
, 1318
, SYSDATE
, 1318);
END xxaj_po_interface;

NALAM Oracle Apps Technical

Sunday, 18 August 2024

Interfaces in Oracle Apps

Interface Table Names in Oracle Apps

Creating Purchase Order Requisitions

Item Revision in Oracle Applications

Import Item from Master org to Child Org Using Item Interface

INTRO TO INTERFACES(For Freshers to oracle Apps)

What are Interface table in AP, AR,GL ?

UTL_FILE Example Using Cursor

Pseudo Code For PO Interface

No comments:

Post a Comment