oracle-ebs-query-to-generate-file-in-remote-server-when-new-record-inserted-into-table
Script 1:
CREATE OR REPLACE DIRECTORY XDMC_OUTBOUND AS ‘XX_TEST/outbound/CUST_PO’;
/
Script 2:
CREATE OR REPLACE PROCEDURE APPS.XDMC_PO_FILE_GEN(
p_cust_po_number IN VARCHAR2,
p_status OUT NOCOPY VARCHAR2
)
AS
v_file UTL_FILE.FILE_TYPE;
lc_filedir VARCHAR2 (150) := NULL;
v_data VARCHAR2(4000);
e_directory_path EXCEPTION;
BEGIN
BEGIN
SELECT directory_path
INTO lc_filedir
FROM sys.all_directories
WHERE DIRECTORY_NAME = ‘XDMC_OUTBOUND’;
EXCEPTION
WHEN OTHERS
THEN
RAISE e_directory_path;
END;
v_file := UTL_FILE.FOPEN(lc_filedir, p_cust_po_number || ‘.csv’, ‘W’);
UTL_FILE.PUT_LINE(v_file, ‘CUST_PO_NUMBER’);
— Write directly from the parameter to the file
UTL_FILE.PUT_LINE(v_file, p_cust_po_number);
— Close the file
UTL_FILE.FCLOSE(v_file);
p_status := ‘Success’; — Set status to indicate success
EXCEPTION
WHEN OTHERS THEN
— Handle exceptions
— errbuf := ‘Error: ‘ || SQLERRM;
— retcode := SQLCODE; — Set return code to SQLCODE
p_status := ‘Error’; — Set status to indicate an error
END XDMC_PO_FILE_GEN;
/
Script 3:
CREATE OR REPLACE TRIGGER XDMC_PO_FILE_BEFORE_INSERT
BEFORE INSERT ON XDMC_PO_FILE_GEN
FOR EACH ROW
DECLARE
v_status VARCHAR2(20);
BEGIN
APPS.XDMC_PO_FILE_GEN(:NEW.CUST_PO_NUMBER, v_status);
— Check v_status and update STATUS_FLAG
IF v_status = ‘Success’ THEN
:NEW.STATUS_FLAG := ‘P’;
:NEW.PROCESS_DATE := sysdate;
ELSE
:NEW.STATUS_FLAG := ‘E’;
END IF;
END;
/
