Oracle Fusion #1: Interfaces & Conversion in Oracle Apps

-- Oracle Fusion Video Series : Video 1
#sql
#PLSQL
#Oracle
#ORACLEAPPS
#OracelFusion
#TechTalkswithNaresh
#Naresh
#R12
#EBSConsultant
Join Our Telegram Group:
t.me/techtalks...
FOLLOW US :
============
TH-cam : / techtalkwithnaresh
For More details Contact Us:
Email: [e-mail]
---------------------------------------------------------------------------------------
In this Playlist I am uploading all the videos which help you to learn the Oracle Application and improve your skills. If you have any doubt's you can write in comment section. Please like,share and Subscribe the channel to get latest videos.
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Sample code for out bound interface
DECLARE
CURSOR c1 IS
SELECT
ooha.order_number,
ooha.flow_status_code,
ooha.creation_date,
oola.ordered_item,
oola.ordered_quantity
FROM
oe_order_headers_all ooha,
oe_order_lines_all oola
WHERE
ooha.header_id = oola.header_id
AND ooha.sold_to_org_id = 8428
AND ooha.flow_status_code (( 'CLOSED';
v_file utl_file.file_type;
v_count NUMBER;
BEGIN
v_count := 0;
v_file := utl_file.fopen('/usr/tmp', 'XXSEA_ORDER_EXTRACT.csv', 'W');
FOR i IN c1 LOOP
v_count := v_count + 1;
utl_file.put_line(v_file, i.order_number || ',' || i.flow_status_code || ',' || i.creation_date || ',' || i.ordered_item || ','|| i.ordered_quantity);
dbms_output.put_line(i.order_number || ',' || i.flow_status_code || ',' || i.creation_date || ',' || i.ordered_item || ',' || i.ordered_quantity);
END LOOP;
utl_file.fclose(v_file);
dbms_output.put_line('count' || v_count);
END;
---------------------------------------
Syntax for SQL LOADER:
LOAD DATA
INFILE 'PO_TOP/12.0.0/in/file_name.csv'
INSERT INTO TABLE EBS
FIELD TERMINATED BY ','
(SNO,SNAME,COURSE,FEE)
-----------------------------------------------------------------------
-- Sample PLSQL Procedure with all validation to insert the data in INTERFACE Table.
CREATE OR REPLACE PROCEDURE xxsea_interfaces AS
CURSOR c1 IS SELECT * FROM XXSEA_INTERFACE;
v_count NUMBER;
v_org_count NUMBER;
v_temp_count NUMBER;
l_item_table ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_return_status VARCHAR2(1);
x_msg_count NUMBER(10);
x_msg_data VARCHAR2(1000);
x_message_list error_handler.error_tbl_type;
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
j NUMBER;
BEGIN
j := 0;
fnd_global.apps_initialize(user_id =( 3392, resp_id =( 21876, resp_appl_id =( 401);
FOR i IN c1 LOOP
j := j + 1;
BEGIN
SELECT COUNT(segment1) INTO v_count FROM mtl_system_items_b
WHERE segment1 = i.item_number AND organization_id = i.organization_id;
dbms_output.put_line('ITEM COUNT'||v_count);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||'-'||sqlerrm);
END;
BEGIN
SELECT COUNT(organization_id) INTO v_org_count FROM mtl_system_items_b
WHERE organization_id = i.organization_id;
dbms_output.put_line('ORG COUNT'||v_org_count);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode || '-' || sqlerrm);
END;
BEGIN
SELECT COUNT(template_id) INTO v_temp_count FROM mtl_item_templates WHERE template_name = i.template_name;
dbms_output.put_line('TEMPLATE_COUNT' || v_temp_count);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode || '-' || sqlerrm);
END;
IF
v_count = 0 AND v_org_count ( 0 AND v_temp_count ( 0
THEN
l_item_table(j).transaction_type := 'CREATE';
l_item_table(j).segment1 := i.item_number;
l_item_table(j).description := i.description;
l_item_table(j).template_name := i.template_name;
l_item_table(j).organization_id := i.organization_id;
ELSE
dbms_output.put_line('ALREADY EXISTS');
END IF;
END LOOP;
dbms_output.put_line('CALLING API TO CREATE ITEM');
ego_item_pub.process_items(p_api_version =( 1.0, p_init_msg_list =( fnd_api.g_true, p_commit =( fnd_api.g_true, p_item_tbl =( l_item_table,
x_item_tbl =( x_item_table, x_return_status =( x_return_status, x_msg_count =( x_msg_count);
dbms_output.put_line('RETURN STATUS ==(' || x_return_status);
IF ( x_return_status = fnd_api.g_ret_sts_success ) THEN
FOR i IN 1..x_item_table.count LOOP
dbms_output.put_line('INVENTORY ITEM ID CREATED:' || to_char(x_item_table(i).inventory_item_id));
dbms_output.put_line('ORGANIZATION ID :' || to_char(x_item_table(i).organization_id));
END LOOP;
ELSE
dbms_output.put_line('ERROR MESG:');
error_handler.get_message_list(x_message_list =( x_message_list);
FOR i IN 1..x_message_list.count LOOP
dbms_output.put_line(x_message_list(i).message_text);
END LOOP;
END IF;
END;