学习并项目应用到EBS FORM查询开发

69 阅读 0 评论 46 点赞

我是靠谱客的博主安静大叔，最近开发中收集的这篇文章主要介绍学习并项目应用到EBS FORM查询开发，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

ORACLE 果然是以数据库起家的，把ORACLE数据库发挥得淋漓尽致，在过去十年甚至更早或许这样是最有效的方式。

下面是查询代码。(主要重点：多组织权限控制、动态LOV、不同的fmx参数传递方式、多条记录应该导向汇总界面）

有啥米问题：QQ: 285979593

PACKAGE BODY QUERY IS
RID NUMBER:=0;
FRID NUMBER;
RetV VARCHAR2(10);
RetN NUMBER;
RESPPROFILE VARCHAR2(30);
exit_error EXCEPTION;
RPH CUX_PAYREQ_HEADERS%ROWTYPE;
TYPE M IS RECORD(
DEFAULT_WHERE VARCHAR2(1000),
ORDER_BY VARCHAR2(100),
DELETE_ALLOWED BOOLEAN,
INSERT_ALLOWED BOOLEAN,
UPDATE_ALLOWED BOOLEAN,
-----------------------
MASTBLK VARCHAR2(30),
COORDOP VARCHAR2(30),
TRIGBLK VARCHAR2(30),
ITEM VARCHAR2(61),
VALUE VARCHAR2(500),
RETSQL VARCHAR2(1000),
FRMSTAT VARCHAR2(15),
STATUS VARCHAR2(30),
CURREL VARCHAR2(30),
CURBLK VARCHAR2(30),
CWND VARCHAR2(50),
EXEEVT VARCHAR2(50),
LOVNAME VARCHAR2(30),
BASESTR VARCHAR2(1000),
STR VARCHAR2(1000)
); C M;
PROCEDURE DEBUGOUT(OutStr varchar2) IS
BEGIN
FND_MESSAGE.SET_STRING(OutStr);

END DEBUGOUT;

PROCEDURE FULLFIELD IS
BEGIN
NULL;
END FULLFIELD;

PROCEDURE QUERYCLEAR IS
BEGIN

:QUERY.Q_DOC_TYPE:=NULL;
:QUERY.Q_INVOICE_DATE:=NULL;
:QUERY.Q_VENDOR_NAME:=NULL;
:QUERY.Q_DOC_STATUS:=NULL;
:QUERY.Q_PROJECTNUM:=NULL;
:QUERY.Q_INVOICE_NUM:=NULL;
:QUERY.Q_GL_DATE:=NULL;
:QUERY.Q_VENDOR_NUM:=NULL;
:QUERY.Q_PROJECTNAME:=NULL;
:QUERY.Q_CURRENCY:=NULL;
:QUERY.Q_INVOICE_AMOUNT:=NULL;
:QUERY.Q_VENDOR_SITE:=NULL;
END QUERYCLEAR;
PROCEDURE ITEMSTATUSCTL IS
BEGIN
IF :QUERY.Q_ORG_NAME IS NULL OR :PARAMETER.ORG_ID IS NULL THEN
APP_ITEM_PROPERTY.SET_PROPERTY('QUERY.Q_FIND',ENABLED,PROPERTY_FALSE);
APP_ITEM_PROPERTY.SET_PROPERTY('QUERY.Q_NEW',ENABLED,PROPERTY_FALSE);
ELSE
APP_ITEM_PROPERTY.SET_PROPERTY('QUERY.Q_FIND',ENABLED,PROPERTY_TRUE);
APP_ITEM_PROPERTY.SET_PROPERTY('QUERY.Q_NEW',ENABLED,PROPERTY_TRUE);
END IF;
END ITEMSTATUSCTL;

PROCEDURE FULLLOV IS
BEGIN
C.BASESTR:='SELECT distinct KHV.PROJECT_NUMBER,KHV.PROJECT_NAME FROM CUX_KP_HEADERS_V KHV WHERE KHV.ORG_ID=:PARAMETER.ORG_ID ';
--ÏîÄ¿±àÂëºÍÏîÄ¿Ãû³Æ¿ÉÒÔÎª¿Õ
C.STR:=' AND (KHV.PROJECT_NUMBER LIKE '||CHR(39)||:QUERY.Q_PROJECTNUM||'%'||CHR(39)|| ' OR KHV.PROJECT_NUMBER IS NULL)'||
' AND (KHV.PROJECT_NAME LIKE '||CHR(39)||:QUERY.Q_PROJECTNAME||'%'||CHR(39)|| ' OR KHV.PROJECT_NAME IS NOT NULL)'||
' AND KHV.KP_DOC_TYPE_DSP LIKE '||CHR(39)||:QUERY.Q_DOC_TYPE||'%'||CHR(39)||
' AND KHV.INVOICE_NUM LIKE '||CHR(39)||:QUERY.Q_INVOICE_NUM||'%'||CHR(39)||
' AND KHV.INVOICE_CURRENCY_CODE LIKE '||CHR(39)||:QUERY.Q_CURRENCY||'%'||CHR(39)||
' AND KHV.INVOICE_DATE LIKE '||CHR(39)||:QUERY.Q_INVOICE_DATE||'%'||CHR(39)||
' AND KHV.GL_DATE LIKE '||CHR(39)||:QUERY.Q_GL_DATE||'%'||CHR(39)||
' AND KHV.INVOICE_AMOUNT LIKE '||CHR(39)||:QUERY.Q_INVOICE_AMOUNT||'%'||CHR(39)||
' AND KHV.VENDOR_NAME LIKE '||CHR(39)||:QUERY.Q_VENDOR_NAME||'%'||CHR(39)||
' AND KHV.VENDOR_NUMBER LIKE '||CHR(39)||:QUERY.Q_VENDOR_NUM||'%'||CHR(39)||
' AND KHV.VENDOR_SITE_CODE LIKE '||CHR(39)||:QUERY.Q_VENDOR_SITE||'%'||CHR(39)||
' AND KHV.KP_SATE_DSP LIKE '||CHR(39)||:QUERY.Q_DOC_STATUS||'%'||CHR(39);
RETN := POPULATE_GROUP_WITH_QUERY('Q_PROJECTNUM', C.BASESTR||C.STR);
C.STR:=' AND (KHV.PROJECT_NUMBER LIKE '||CHR(39)||:QUERY.Q_PROJECTNUM||'%'||CHR(39)|| ' OR KHV.PROJECT_NUMBER IS NULL)'||
' AND (KHV.PROJECT_NAME LIKE '||CHR(39)||:QUERY.Q_PROJECTNAME||'%'||CHR(39)|| ' OR KHV.PROJECT_NAME IS NULL)'||
' AND KHV.KP_DOC_TYPE_DSP LIKE '||CHR(39)||:QUERY.Q_DOC_TYPE||'%'||CHR(39)||
' AND KHV.INVOICE_NUM LIKE '||CHR(39)||:QUERY.Q_INVOICE_NUM||'%'||CHR(39)||
' AND KHV.INVOICE_CURRENCY_CODE LIKE '||CHR(39)||:QUERY.Q_CURRENCY||'%'||CHR(39)||
' AND KHV.INVOICE_DATE LIKE '||CHR(39)||:QUERY.Q_INVOICE_DATE||'%'||CHR(39)||
' AND KHV.GL_DATE LIKE '||CHR(39)||:QUERY.Q_GL_DATE||'%'||CHR(39)||
' AND KHV.INVOICE_AMOUNT LIKE '||CHR(39)||:QUERY.Q_INVOICE_AMOUNT||'%'||CHR(39)||
' AND KHV.VENDOR_NAME LIKE '||CHR(39)||:QUERY.Q_VENDOR_NAME||'%'||CHR(39)||
' AND KHV.VENDOR_NUMBER LIKE '||CHR(39)||:QUERY.Q_VENDOR_NUM||'%'||CHR(39)||
' AND KHV.VENDOR_SITE_CODE LIKE '||CHR(39)||:QUERY.Q_VENDOR_SITE||'%'||CHR(39)||
' AND KHV.KP_SATE_DSP LIKE '||CHR(39)||:QUERY.Q_DOC_STATUS||'%'||CHR(39);
C.BASESTR:='SELECT DISTINCT KHV.VENDOR_NAME,
KHV.VENDOR_NUMBER,
KHV.VENDOR_ID,
KHV.VENDOR_SITE_ID,
KHV.VENDOR_SITE_CODE
FROM CUX_KP_HEADERS_V KHV
WHERE KHV.ORG_ID=:PARAMETER.ORG_ID ';
RETN := POPULATE_GROUP_WITH_QUERY('Q_VENDOR_NUM', C.BASESTR||C.STR);
RETN := POPULATE_GROUP_WITH_QUERY('Q_VENDOR_NAME', C.BASESTR||C.STR);
RETN := POPULATE_GROUP_WITH_QUERY('Q_VENDOR_SITE', C.BASESTR||C.STR);
C.BASESTR:='SELECT KHV.INVOICE_NUM FROM CUX_KP_HEADERS_V KHV WHERE KHV.ORG_ID=:PARAMETER.ORG_ID ';
RETN := POPULATE_GROUP_WITH_QUERY('Q_INVOICE_NUM', C.BASESTR||C.STR);
C.BASESTR:=' SELECT distinct KHV.PROJECT_NAME FROM CUX_KP_HEADERS_V KHV WHERE KHV.ORG_ID=:PARAMETER.ORG_ID
AND KHV.PROJECT_NAME IS NOT NULL ';
RETN := POPULATE_GROUP_WITH_QUERY('Q_PROJECTNAME', C.BASESTR||C.STR);
END FULLLOV;

PROCEDURE QUERYSUM_WNDPROC(MSG VARCHAR2) IS
BEGIN
IF MSG='QUERY' THEN
IF :QUERY.Q_INVOICE_NUM IS NULL THEN
C.CWND:='QUERYSUM';
C.CURBLK:='CUX_KP_HEADERS_V';
C.BASESTR:=
' ORG_ID = '||CHR(39)||:PARAMETER.ORG_ID||CHR(39)||
' AND (PROJECT_NUMBER LIKE '||CHR(39)||:QUERY.Q_PROJECTNUM||'%'||CHR(39)||' OR PROJECT_NUMBER IS NULL)'||
' AND (PROJECT_NAME LIKE '||CHR(39)||:QUERY.Q_PROJECTNAME||'%'||CHR(39)||' OR PROJECT_NAME IS NULL)'||
' AND KP_DOC_TYPE_DSP LIKE '||CHR(39)||:QUERY.Q_DOC_TYPE||'%'||CHR(39)||
' AND INVOICE_NUM LIKE '||CHR(39)||:QUERY.Q_INVOICE_NUM||'%'||CHR(39)||
' AND INVOICE_CURRENCY_CODE LIKE '||CHR(39)||:QUERY.Q_CURRENCY||'%'||CHR(39)||
' AND INVOICE_DATE LIKE '||CHR(39)||:QUERY.Q_INVOICE_DATE||'%'||CHR(39)||
' AND GL_DATE LIKE '||CHR(39)||:QUERY.Q_GL_DATE||'%'||CHR(39)||
' AND INVOICE_AMOUNT LIKE '||CHR(39)||:QUERY.Q_INVOICE_AMOUNT||'%'||CHR(39)||
' AND VENDOR_NAME LIKE '||CHR(39)||:QUERY.Q_VENDOR_NAME||'%'||CHR(39)||
' AND VENDOR_NUMBER LIKE '||CHR(39)||:QUERY.Q_VENDOR_NUM||'%'||CHR(39)||
' AND VENDOR_SITE_CODE LIKE '||CHR(39)||:QUERY.Q_VENDOR_SITE||'%'||CHR(39)||
' AND KP_SATE_DSP LIKE '||CHR(39)||:QUERY.Q_DOC_STATUS||'%'||CHR(39);
ELSE
FND_FUNCTION.EXECUTE(FUNCTION_NAME=>'CUXFPPP',
OPEN_FLAG=>'Y',
SESSION_FLAG=>'Y',
OTHER_PARAMS=>'SYNC_INVOICE_NUM="'||:QUERY.Q_INVOICE_NUM||'"',
ACTIVATE_FLAG=>'ACTIVE');
END IF;
GO_BLOCK (C.CURBLK);
SET_BLOCK_PROPERTY(C.CURBLK,DEFAULT_WHERE,C.BASESTR);
HIDE_WINDOW('QUERY');
SHOW_WINDOW(C.CWND);
EXECUTE_QUERY;
DEBUGOUT(C.BASESTR);
ELSIF MSG='KP_OPEN' THEN
IF :CUX_KP_HEADERS_V.INVOICE_NUM IS NOT NULL THEN

--Synchronize;
:PARAMETER.SYNC_CALL:='Y';
FND_FUNCTION.EXECUTE(FUNCTION_NAME=>'CUXFPPP',
OPEN_FLAG=>'Y',
SESSION_FLAG=>'Y',
OTHER_PARAMS=>'SYNC_INVOICE_NUM="'||:CUX_KP_HEADERS_V.INVOICE_NUM||'"',
ACTIVATE_FLAG=>'ACTIVE');

END IF;
ELSIF MSG IN ('KP_NEW','Q_NEW') THEN
FND_FUNCTION.EXECUTE(FUNCTION_NAME=>'CUXFPPP',
OPEN_FLAG=>'Y',
SESSION_FLAG=>'Y',
OTHER_PARAMS=>'SYNC_ORG_NAME="'||:QUERY.Q_ORG_NAME||
'"SYNC_ORG_ID="'||:PARAMETER.ORG_ID||'"',
ACTIVATE_FLAG=>'ACTIVE');
END IF;
END QUERYSUM_WNDPROC;

PROCEDURE CORE(TRIGGER_BLOCK VARCHAR2 ,HWND VARCHAR2,MSG VARCHAR2) IS
BEGIN

C.MASTBLK := :SYSTEM.MASTER_BLOCK;
C.CURBLK :=:SYSTEM.CURRENT_BLOCK;
C.COORDOP := :SYSTEM.COORDINATION_OPERATION;
C.TRIGBLK := :SYSTEM.TRIGGER_BLOCK;
C.ITEM := :SYSTEM.TRIGGER_ITEM;
---------------------------------------------------
IF MSG='PRE-FORM' THEN

BEGIN
FRID:=FND_PROFILE.VALUE('RESP_ID');

--1¡¢ ²âÊÔ³¬¼¶ÓÃ»§

SELECT COUNT(RT.RESPONSIBILITY_ID)
INTO RID
FROM FND_RESPONSIBILITY_TL RT,
FND_PROFILE_OPTIONS_TL TL,
FND_PROFILE_OPTIONS OP,
FND_PROFILE_OPTION_VALUES VA,
HR_OPERATING_UNITS HOS
WHERE TL.PROFILE_OPTION_NAME = OP.PROFILE_OPTION_NAME
AND VA.PROFILE_OPTION_ID = OP.PROFILE_OPTION_ID
AND /*VA.LEVEL_ID = LV.LEVEL_ID*/
RT.RESPONSIBILITY_ID = VA.LEVEL_VALUE
AND TL.USER_PROFILE_OPTION_NAME = 'MO£ºÒµÎñÊµÌå'
AND HOS.ORGANIZATION_ID = VA.PROFILE_OPTION_VALUE
AND RT.RESPONSIBILITY_ID =FRID
AND RT.LANGUAGE = 'ZHS';

IF RID=0 THEN
APP_ITEM_PROPERTY.SET_PROPERTY('QUERY.Q_ORG_NAME',ENABLED,PROPERTY_TRUE);

:GLOBAL.RESP_NAME:=NULL;
ELSE
APP_ITEM_PROPERTY.SET_PROPERTY('QUERY.Q_ORG_NAME',ENABLED,PROPERTY_FALSE);

--1.1 »ñµÃÄ¬ÈÏÊµÌå
SELECT HOS.ORGANIZATION_ID
INTO :PARAMETER.ORG_ID
FROM FND_RESPONSIBILITY_TL RT,
FND_PROFILE_OPTIONS_TL TL,
FND_PROFILE_OPTIONS OP,
FND_PROFILE_OPTION_VALUES VA,
HR_OPERATING_UNITS HOS
WHERE TL.PROFILE_OPTION_NAME = OP.PROFILE_OPTION_NAME
AND VA.PROFILE_OPTION_ID = OP.PROFILE_OPTION_ID
AND /*VA.LEVEL_ID = LV.LEVEL_ID*/
RT.RESPONSIBILITY_ID = VA.LEVEL_VALUE
AND TL.USER_PROFILE_OPTION_NAME = 'MO£ºÒµÎñÊµÌå'
AND HOS.ORGANIZATION_ID = VA.PROFILE_OPTION_VALUE
AND RT.LANGUAGE = 'ZHS'
AND RT.RESPONSIBILITY_ID=FRID;

SELECT NAME
INTO :PARAMETER.ORG_NAME
FROM HR_OPERATING_UNITS
WHERE ORGANIZATION_ID = :PARAMETER.ORG_ID;
--:GLOBAL.RESP_NAME:=:PARAMETER.LAST_NAME;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
:PARAMETER.ORG_NAME:=NULL;
FND_MESSAGE.DEBUG('PROFILE CONFIGURATION ERROR.');
WHEN OTHERS THEN
RAISE;
END;
ELSIF MSG IN('KEY-LISTVAL','WHEN-NEW-ITEM-INSTANCE') AND C.CURBLK='QUERY' THEN
BEGIN
C.VALUE:=TRIM(:SYSTEM.CURRENT_VALUE);
ITEMSTATUSCTL;
IF C.ITEM ='QUERY.Q_ORG_NAME' THEN
QUERYCLEAR;FULLFIELD;FULLLOV;
ELSIF C.ITEM='QUERY.Q_REQ_NUMBER' THEN
FULLFIELD;FULLLOV;
ELSE
FULLFIELD;FULLLOV;
END IF;

END ;

ELSIF MSG='WHEN-BUTTON-PRESSED' THEN
BEGIN

IF C.ITEM='QUERY.Q_CLEAR' THEN
QUERYCLEAR;
ELSIF C.ITEM='QUERY.Q_FIND' THEN
HIDE_WINDOW(HWND);
QUERYSUM_WNDPROC('QUERY');
ELSIF C.ITEM='CUX_KP_HEADERS_V.KP_OPEN' THEN
HIDE_WINDOW(HWND);
QUERYSUM_WNDPROC('KP_OPEN');
ELSIF C.ITEM ='CUX_KP_HEADERS_V.KP_NEW' THEN
HIDE_WINDOW(HWND);
QUERYSUM_WNDPROC('KP_NEW');
ELSIF C.ITEM='QUERY.Q_NEW' THEN
QUERYSUM_WNDPROC('Q_NEW');
END IF;
END;
END IF;
END CORE;
END;