--Sumit Request 整合
--Fnd Lookup Code Function
--Fnd Flex Value Function
--Generate Form Developer Trigger (Base On View)
CREATE OR REPLACE PACKAGE XXX_UTL_PKG IS
TYPE REQUEST_PARAMETER IS RECORD(
USER_ID NUMBER
,RESP_ID NUMBER
,RESP_APPL_ID NUMBER
,ORG_ID NUMBER
,APPLICATION VARCHAR2(50)
,PROGRAM VARCHAR2(50)
,DESCRIPTION VARCHAR2(240)
,START_TIME VARCHAR2(30)
,SUB_REQUEST BOOLEAN
,ARGUMENT1 VARCHAR2(100)
,ARGUMENT2 VARCHAR2(100)
,ARGUMENT3 VARCHAR2(100)
,ARGUMENT4 VARCHAR2(100)
,ARGUMENT5 VARCHAR2(100)
,ARGUMENT6 VARCHAR2(100)
,ARGUMENT7 VARCHAR2(100)
,ARGUMENT8 VARCHAR2(100)
,ARGUMENT9 VARCHAR2(100)
,ARGUMENT10 VARCHAR2(100)
,ARGUMENT11 VARCHAR2(100)
,ARGUMENT12 VARCHAR2(100)
,ARGUMENT13 VARCHAR2(100)
,ARGUMENT14 VARCHAR2(100)
,ARGUMENT15 VARCHAR2(100)
,ARGUMENT16 VARCHAR2(100)
,ARGUMENT17 VARCHAR2(100)
,ARGUMENT18 VARCHAR2(100)
,ARGUMENT19 VARCHAR2(100)
,ARGUMENT20 VARCHAR2(100));
PROCEDURE GET_USER_INFO(P_USER_NAME IN VARCHAR2
,P_RESPONSIBILITY_KEY IN VARCHAR2
,P_USER_ID OUT NUMBER
,P_RESP_ID OUT NUMBER
,P_RESP_APPL_ID OUT NUMBER);
PROCEDURE SUBMIT_REQUEST(P_INPUT IN REQUEST_PARAMETER
,P_REQUEST_ID OUT NUMBER
,P_OK_FLG OUT VARCHAR2);
FUNCTION FLEX_VALUE_CHR(P_FLEX_VALUE_SET_NAME IN VARCHAR2
,P_FLEX_VALUE IN VARCHAR2
,P_GET_COLUMN IN VARCHAR2) RETURN VARCHAR2;
FUNCTION LOOKUP_CHR(P_APP_SHRT_NAME IN VARCHAR2
,P_LOOKUP_TYPE IN VARCHAR2
,P_LOOKUP_CODE IN VARCHAR2
,P_GET_COLUMN IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE GEN_FORM_TRIGGER(P_TABLE_NAME IN VARCHAR2
,P_TRIGGER_TYPE IN VARCHAR2 DEFAULT NULL);
END XXX_UTL_PKG;
/
CREATE OR REPLACE PACKAGE BODY XXX_UTL_PKG IS
PROCEDURE GET_USER_INFO(P_USER_NAME IN VARCHAR2
,P_RESPONSIBILITY_KEY IN VARCHAR2
,P_USER_ID OUT NUMBER
,P_RESP_ID OUT NUMBER
,P_RESP_APPL_ID OUT NUMBER) IS
BEGIN
SELECT FU.USER_ID
INTO P_USER_ID
FROM FND_USER FU
WHERE FU.USER_NAME = P_USER_NAME;
SELECT FR.APPLICATION_ID, FR.RESPONSIBILITY_ID
INTO P_RESP_APPL_ID, P_RESP_ID
FROM FND_RESPONSIBILITY FR
WHERE FR.RESPONSIBILITY_KEY = P_RESPONSIBILITY_KEY;
EXCEPTION
WHEN OTHERS THEN
NULL;
END GET_USER_INFO;
PROCEDURE SUBMIT_REQUEST(P_INPUT IN REQUEST_PARAMETER
,P_REQUEST_ID OUT NUMBER
,P_OK_FLG OUT VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
LS_PHASE VARCHAR2(50);
LS_STATUS VARCHAR2(50);
LS_DEV_PHASE VARCHAR2(50);
LS_DEV_STATUS VARCHAR2(50);
LS_MSG VARCHAR2(100);
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(P_INPUT.ORG_ID);
FND_GLOBAL.APPS_INITIALIZE(USER_ID => P_INPUT.USER_ID
,RESP_ID => P_INPUT.RESP_ID
,RESP_APPL_ID => P_INPUT.RESP_APPL_ID);
P_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => P_INPUT.APPLICATION
,PROGRAM => P_INPUT.PROGRAM
,DESCRIPTION => P_INPUT.DESCRIPTION
,START_TIME => P_INPUT.START_TIME
,SUB_REQUEST => P_INPUT.SUB_REQUEST
,ARGUMENT1 => P_INPUT.ARGUMENT1
,ARGUMENT2 => P_INPUT.ARGUMENT2
,ARGUMENT3 => P_INPUT.ARGUMENT3
,ARGUMENT4 => P_INPUT.ARGUMENT4
,ARGUMENT5 => P_INPUT.ARGUMENT5
,ARGUMENT6 => P_INPUT.ARGUMENT6
,ARGUMENT7 => P_INPUT.ARGUMENT7
,ARGUMENT8 => P_INPUT.ARGUMENT8
,ARGUMENT9 => P_INPUT.ARGUMENT9
,ARGUMENT10 => P_INPUT.ARGUMENT10
,ARGUMENT11 => P_INPUT.ARGUMENT11
,ARGUMENT12 => P_INPUT.ARGUMENT12
,ARGUMENT13 => P_INPUT.ARGUMENT13
,ARGUMENT14 => P_INPUT.ARGUMENT14
,ARGUMENT15 => P_INPUT.ARGUMENT15
,ARGUMENT16 => P_INPUT.ARGUMENT16
,ARGUMENT17 => P_INPUT.ARGUMENT17
,ARGUMENT18 => P_INPUT.ARGUMENT18
,ARGUMENT19 => P_INPUT.ARGUMENT19
,ARGUMENT20 => P_INPUT.ARGUMENT20);
COMMIT;
IF P_REQUEST_ID = 0 THEN
P_OK_FLG := 'N';
RETURN;
END IF;
IF FND_CONCURRENT.WAIT_FOR_REQUEST(P_REQUEST_ID
,10
,0
,LS_PHASE
,LS_STATUS
,LS_DEV_PHASE
,LS_DEV_STATUS
,LS_MSG) THEN
P_OK_FLG := 'Y';
ELSE
P_OK_FLG := 'N';
END IF;
END SUBMIT_REQUEST;
FUNCTION FLEX_VALUE_CHR(P_FLEX_VALUE_SET_NAME IN VARCHAR2
,P_FLEX_VALUE IN VARCHAR2
,P_GET_COLUMN IN VARCHAR2) RETURN VARCHAR2 IS
LS_SQL_TEXT VARCHAR2(1000);
LS_VALUE VARCHAR2(100);
BEGIN
LS_SQL_TEXT := 'SELECT TO_CHAR(FFVV.' || P_GET_COLUMN || ')' ||
' FROM FND_FLEX_VALUE_SETS FFVS, FND_FLEX_VALUES_VL FFVV' ||
' WHERE FFVS.FLEX_VALUE_SET_ID = FFVV.FLEX_VALUE_SET_ID' ||
' AND FFVS.FLEX_VALUE_SET_NAME = ''' ||
P_FLEX_VALUE_SET_NAME || '''' ||
' AND FFVV.FLEX_VALUE = ''' || P_FLEX_VALUE || '''';
EXECUTE IMMEDIATE LS_SQL_TEXT
INTO LS_VALUE;
RETURN LS_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END FLEX_VALUE_CHR;
FUNCTION LOOKUP_CHR(P_APP_SHRT_NAME IN VARCHAR2
,P_LOOKUP_TYPE IN VARCHAR2
,P_LOOKUP_CODE IN VARCHAR2
,P_GET_COLUMN IN VARCHAR2) RETURN VARCHAR2 IS
LS_SQL_TEXT VARCHAR2(1000);
LS_VALUE VARCHAR2(100);
BEGIN
BEGIN
LS_SQL_TEXT := 'SELECT TO_CHAR(V.' || P_GET_COLUMN || ') FROM ' ||
'FND_LOOKUP_VALUES_VL V, FND_APPLICATION A WHERE ' ||
'V.VIEW_APPLICATION_ID = A.APPLICATION_ID AND ' ||
'A.APPLICATION_SHORT_NAME = ''' || P_APP_SHRT_NAME ||
''' AND V.LOOKUP_TYPE = ''' || P_LOOKUP_TYPE ||
''' AND V.LOOKUP_CODE = ''' || P_LOOKUP_CODE || '''';
EXECUTE IMMEDIATE LS_SQL_TEXT
INTO LS_VALUE;
RETURN LS_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
END LOOKUP_CHR;
PROCEDURE GEN_FORM_TRIGGER(P_TABLE_NAME IN VARCHAR2
,P_TRIGGER_TYPE IN VARCHAR2 DEFAULT NULL) IS
V_COUNT NUMBER;
V_PK_COLUMN VARCHAR2(30);
--
V_COLUMN_CNT NUMBER;
CURSOR L_TAB_COLUMN IS
SELECT T.*
FROM ALL_TAB_COLUMNS T
WHERE T.TABLE_NAME = P_TABLE_NAME
ORDER BY T.COLUMN_ID;
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM ALL_TABLES T
WHERE T.TABLE_NAME = P_TABLE_NAME;
IF V_COUNT <> 0 THEN
SELECT T.COLUMN_NAME
INTO V_PK_COLUMN
FROM ALL_TAB_COLUMNS T
WHERE T.TABLE_NAME = P_TABLE_NAME
AND T.COLUMN_ID = 1;
FOR L_TYPE IN (SELECT *
FROM (SELECT 'INSERT' TYPE
FROM DUAL
UNION ALL
SELECT 'UPDATE' TYPE
FROM DUAL
UNION ALL
SELECT 'DELETE' TYPE FROM DUAL) A
WHERE A.TYPE = NVL(UPPER(P_TRIGGER_TYPE), A.TYPE)) LOOP
CASE
WHEN L_TYPE.TYPE = 'DELETE' THEN
/* ON-DELETE */
DBMS_OUTPUT.PUT_LINE(A => '--ON-DELETE');
DBMS_OUTPUT.PUT_LINE(A => 'BEGIN');
DBMS_OUTPUT.PUT_LINE(A => 'DELETE FROM ' || P_TABLE_NAME ||
' WHERE ' || V_PK_COLUMN || ' = ' || ':' ||
P_TABLE_NAME || '_V.' || V_PK_COLUMN || ';');
DBMS_OUTPUT.PUT_LINE(A => 'END;');
DBMS_OUTPUT.PUT_LINE(A => '');
WHEN L_TYPE.TYPE = 'INSERT' THEN
/* PRE-INSERT */
DBMS_OUTPUT.PUT_LINE(A => '--PRE-INSERT');
DBMS_OUTPUT.PUT_LINE(A => 'BEGIN');
DBMS_OUTPUT.PUT_LINE(A => 'SELECT ' || P_TABLE_NAME ||
'_S.NEXTVAL');
DBMS_OUTPUT.PUT_LINE(A => 'INTO ' || P_TABLE_NAME || '_V.' ||
V_PK_COLUMN);
DBMS_OUTPUT.PUT_LINE(A => 'FROM DUAL;');
DBMS_OUTPUT.PUT_LINE(A => 'FND_STANDARD.SET_WHO;');
DBMS_OUTPUT.PUT_LINE(A => 'END;');
DBMS_OUTPUT.PUT_LINE(A => '');
/* ON-INSERT */
DBMS_OUTPUT.PUT_LINE(A => '--ON-INSERT');
DBMS_OUTPUT.PUT_LINE(A => 'DECLARE');
DBMS_OUTPUT.PUT_LINE(A => 'CURSOR C IS SELECT ROWID FROM ' ||
P_TABLE_NAME || ' WHERE ' ||
V_PK_COLUMN || ' = :' || P_TABLE_NAME ||
'_V.' || V_PK_COLUMN || ';');
DBMS_OUTPUT.PUT_LINE(A => 'BEGIN');
DBMS_OUTPUT.PUT_LINE(A => 'INSERT INTO ' || P_TABLE_NAME || '(');
V_COLUMN_CNT := 0;
FOR A IN L_TAB_COLUMN LOOP
V_COLUMN_CNT := V_COLUMN_CNT + 1;
IF V_COLUMN_CNT = 1 THEN
DBMS_OUTPUT.PUT_LINE(A => A.COLUMN_NAME);
ELSE
DBMS_OUTPUT.PUT_LINE(A => ',' || A.COLUMN_NAME);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(A => ') VALUES (');
V_COLUMN_CNT := 0;
FOR B IN L_TAB_COLUMN LOOP
V_COLUMN_CNT := V_COLUMN_CNT + 1;
IF V_COLUMN_CNT = 1 THEN
DBMS_OUTPUT.PUT_LINE(A => ':' || P_TABLE_NAME || '_V.' ||
B.COLUMN_NAME);
ELSE
DBMS_OUTPUT.PUT_LINE(A => ',:' || P_TABLE_NAME || '_V.' ||
B.COLUMN_NAME);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(A => '); OPEN C; FETCH C INTO :' ||
P_TABLE_NAME ||
'_V.ROW_ID; IF (C%NOTFOUND) THEN CLOSE C; RAISE NO_DATA_FOUND; END IF; CLOSE C; END;');
DBMS_OUTPUT.PUT_LINE(A => '');
WHEN L_TYPE.TYPE = 'UPDATE' THEN
/* PRE-UPDATE */
DBMS_OUTPUT.PUT_LINE(A => '--PRE-UPDATE');
DBMS_OUTPUT.PUT_LINE(A => 'BEGIN FND_STANDARD.SET_WHO; END;');
DBMS_OUTPUT.PUT_LINE(A => '');
/* ON-UPDATE */
DBMS_OUTPUT.PUT_LINE(A => '--ON-UPDATE');
DBMS_OUTPUT.PUT_LINE(A => 'BEGIN UPDATE ' || P_TABLE_NAME ||
' SET ');
V_COLUMN_CNT := 0;
FOR C IN L_TAB_COLUMN LOOP
V_COLUMN_CNT := V_COLUMN_CNT + 1;
IF V_COLUMN_CNT = 1 THEN
DBMS_OUTPUT.PUT_LINE(A => C.COLUMN_NAME || ' = :' ||
P_TABLE_NAME || '_V.' ||
C.COLUMN_NAME);
ELSE
DBMS_OUTPUT.PUT_LINE(A => ',' || C.COLUMN_NAME || ' = :' ||
P_TABLE_NAME || '_V.' ||
C.COLUMN_NAME);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(A => 'WHERE ' || V_PK_COLUMN || ' = :' ||
P_TABLE_NAME || '.' || V_PK_COLUMN || ';');
DBMS_OUTPUT.PUT_LINE(A => 'IF (SQL%NOTFOUND) THEN RAISE NO_DATA_FOUND; END IF; END;');
DBMS_OUTPUT.PUT_LINE(A => '');
END CASE;
END LOOP;
END IF;
END GEN_FORM_TRIGGER;
END XXX_UTL_PKG;
/
Oracle (6)