Create Table Code:--
CREATE TABLE SYB_TABLE_NAME_TBL (
IMPORT_ACTION VARCHAR2(255),
SUPPLIER_NAME VARCHAR2(255),
SUPPLIER_NAME_NEW VARCHAR2(240),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(255),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_BY VARCHAR2(255),
LAST_UPDATE_LOGIN VARCHAR2(255),
LOAD_REQUEST_ID VARCHAR2(255),
INT_INSTANCE_ID VARCHAR2(255),
RICE_ID VARCHAR2(255),
INT_FILE_NAME VARCHAR2(255),
PROCESS_STATUS VARCHAR2(255),
PROCESS_MESSAGE VARCHAR2(255)
);
CREATE TABLE SYB_TABLE_NAME_TBL_STG (
IMPORT_ACTION VARCHAR2(255),
SUPPLIER_NAME VARCHAR2(255),
SUPPLIER_NAME_NEW VARCHAR2(240),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(255),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_BY VARCHAR2(255),
LAST_UPDATE_LOGIN VARCHAR2(255),
LOAD_REQUEST_ID VARCHAR2(255),
INT_INSTANCE_ID VARCHAR2(255),
RICE_ID VARCHAR2(255),
INT_FILE_NAME VARCHAR2(255),
PROCESS_STATUS VARCHAR2(255),
PROCESS_MESSAGE VARCHAR2(255)
);
PACKAGE SPECIFICATION CODE:-
create or replace PACKAGE SYB_PACKAGE_NAME_PKG
IS
PROCEDURE log_message(P_PROCEDURE VARCHAR2,P_MESSAGE VARCHAR2);
TYPE SYB_SUPPLIER_TBL_TYPE IS RECORD (
IMPORT_ACTION VARCHAR2(255),
SUPPLIER_NAME VARCHAR2(255),
SUPPLIER_NAME_NEW VARCHAR2(240),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(255),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_BY VARCHAR2(255),
LAST_UPDATE_LOGIN VARCHAR2(255),
LOAD_REQUEST_ID VARCHAR2(255),
INT_INSTANCE_ID VARCHAR2(255),
RICE_ID VARCHAR2(255),
INT_FILE_NAME VARCHAR2(255),
PROCESS_STATUS VARCHAR2(255),
PROCESS_MESSAGE VARCHAR2(255)
);
--TYPE SYB_SUPPLIER_TBL_TYPE_REC IS TABLE OF FBD_SUPPLIER_TBL_TYPE INDEX BY PLS_INTEGER;
TYPE SYB_SUPPLIER_TBL_TYPE_REC IS TABLE OF SYB_SUPPLIER_TBL_TYPE;
PROCEDURE SYB_SUPPLIER_MAIN(
P_STATUS_OUT OUT VARCHAR2,
P_ERROR_MSG_OUT OUT VARCHAR2
);
-- SYB_SUPP_INSERT This procedure and Logic is to insert data received in Json or in csv to Table
PROCEDURE SYB_SUPP_INSERT(
P_SUPP IN SYB_SUPPLIER_TBL_TYPE_REC,
P_RICE_ID IN VARCHAR2,
P_INSTANCE_ID IN VARCHAR2,
P_STATUS_OUT OUT VARCHAR2,
P_ERROR_MSG_OUT OUT VARCHAR2
);
END SYB_PACKAGE_NAME_PKG;
PACKAGE BODY:-
create or replace PACKAGE BODY SYB_PACKAGE_NAME_PKG IS
PROCEDURE log_message (
p_procedure VARCHAR2,
p_message VARCHAR2
) IS
l_err_msg varchar2(2000);
BEGIN
l_err_msg:='<< '|| p_procedure
|| ' - '
|| p_message
|| ' >>'
;
dbms_output.put_line(l_err_msg);
fnd_file.put_line (fnd_file.log, l_err_msg);
END log_message;
PROCEDURE SYB_SUPP_INSERT(
P_SUPP IN SYB_SUPPLIER_TBL_TYPE_REC,
P_RICE_ID IN VARCHAR2,
P_INSTANCE_ID IN VARCHAR2,
P_STATUS_OUT OUT VARCHAR2,
P_ERROR_MSG_OUT OUT VARCHAR2
) IS
BEGIN
dbms_output.put_line('BUL INSERT START');
FORALL I IN P_SUPP.FIRST..P_SUPP.LAST
INSERT INTO SYB_TABLE_NAME_TBL (
IMPORT_ACTION ,
SUPPLIER_NAME ,
SUPPLIER_NAME_NEW ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_BY ,
LAST_UPDATE_LOGIN ,
LOAD_REQUEST_ID ,
INT_INSTANCE_ID ,
RICE_ID ,
INT_FILE_NAME ,
PROCESS_STATUS ,
PROCESS_MESSAGE
)
VALUES (
P_SUPP(I).IMPORT_ACTION,
P_SUPP(I).SUPPLIER_NAME,
P_SUPP(I).SUPPLIER_NAME_NEW,
P_SUPP(I).CREATION_DATE ,
P_SUPP(I).CREATED_BY,
P_SUPP(I).LAST_UPDATE_DATE,
P_SUPP(I).LAST_UPDATE_BY,
P_SUPP(I).LAST_UPDATE_LOGIN,
P_SUPP(I).LOAD_REQUEST_ID,
P_SUPP(I).INT_INSTANCE_ID,
P_SUPP(I).RICE_ID,
P_SUPP(I).INT_FILE_NAME,
P_SUPP(I).PROCESS_STATUS,
P_SUPP(I).PROCESS_MESSAGE
);
COMMIT;
P_STATUS_OUT := 'SUCCESS';
P_ERROR_MSG_OUT := NULL;
EXCEPTION
WHEN OTHERS THEN
P_STATUS_OUT := 'ERROR';
P_ERROR_MSG_OUT := SQLERRM;
END SYB_SUPP_INSERT;
PROCEDURE SYB_SUPPLIER_MAIN(
P_STATUS_OUT OUT VARCHAR2,
P_ERROR_MSG_OUT OUT VARCHAR2
) IS
BEGIN
P_STATUS_OUT := 'SUCCESS';
P_ERROR_MSG_OUT := NULL;
EXCEPTION
WHEN OTHERS THEN
P_STATUS_OUT := 'ERROR';
P_ERROR_MSG_OUT := SQLERRM;
END SYB_SUPPLIER_MAIN;
END SYB_PACKAGE_NAME_PKG;
--******** TESTING ANONYMOUS BLOCK **********--
DECLARE
CURSOR c1 IS
SELECT *
FROM SYB_TABLE_NAME_TBL_STG;
P_SUPP SYB_PACKAGE_NAME_PKG.SYB_SUPPLIER_TBL_TYPE_REC;
V_INDEX PLS_INTEGER;
V_RICE_ID VARCHAR2(240) := 'RICE001';
V_INSTANCE_ID VARCHAR2(240) := 'INSTANCE001';
V_STATUS_OUT VARCHAR2(240);
V_ERROR_MSG_OUT VARCHAR2(240);
BEGIN
-- Initialize the nested table before using it
P_SUPP := SYB_PACKAGE_NAME_PKG.SYB_SUPPLIER_TBL_TYPE_REC();
FOR rec IN c1 LOOP
P_SUPP.EXTEND;
V_INDEX := P_SUPP.COUNT;
P_SUPP(V_INDEX).IMPORT_ACTION := rec.IMPORT_ACTION;
P_SUPP(V_INDEX).SUPPLIER_NAME := rec.SUPPLIER_NAME;
-- Assign other required fields (use default values if needed)
P_SUPP(V_INDEX).SUPPLIER_NAME_NEW := NULL;
P_SUPP(V_INDEX).CREATION_DATE := SYSDATE;
P_SUPP(V_INDEX).CREATED_BY := 'ANONYMOUS';
P_SUPP(V_INDEX).LAST_UPDATE_DATE := SYSDATE;
P_SUPP(V_INDEX).LAST_UPDATE_BY := 'ANONYMOUS';
P_SUPP(V_INDEX).LAST_UPDATE_LOGIN := 'DEFAULT_LOGIN';
P_SUPP(V_INDEX).LOAD_REQUEST_ID := 'REQ001';
P_SUPP(V_INDEX).INT_INSTANCE_ID := V_INSTANCE_ID;
P_SUPP(V_INDEX).RICE_ID := V_RICE_ID;
P_SUPP(V_INDEX).INT_FILE_NAME := 'default_file.txt';
P_SUPP(V_INDEX).PROCESS_STATUS := 'NEW';
P_SUPP(V_INDEX).PROCESS_MESSAGE := NULL;
END LOOP;
SYB_PACKAGE_NAME_PKG.SYB_SUPP_INSERT(
P_SUPP,
V_RICE_ID,
V_INSTANCE_ID,
V_STATUS_OUT,
V_ERROR_MSG_OUT
);
commit;
DBMS_OUTPUT.PUT_LINE('Status: ' || V_STATUS_OUT);
DBMS_OUTPUT.PUT_LINE('Message: ' || V_ERROR_MSG_OUT);
END;
No comments:
Post a Comment