oracleerp報表開發(fā)開發(fā)培訓(xùn)教程解讀

1、內(nèi)部資料NO.ERP0002創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page 1 of 28內(nèi)部資料NO.ERP0002KudoNet多茂科技創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page # of 28內(nèi)部資料NO.ERP0002創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page # of 28內(nèi)部資料NO.ERP0002南京多茂科技發(fā)展有限公司Oracle ERP報表開發(fā)培訓(xùn)教程創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kud

2、onet Page # of 28內(nèi)部資料NO.ERP0002報表開發(fā)的總體步驟，及一些工具包的簡單介紹 .-I-Pl/sql 語法及pl/sql 函數(shù)、數(shù)組、記錄、游標(biāo)、視圖、基表、臨時表、異常 ：4-HTML技術(shù)和 CSS修飾 ：查找數(shù)據(jù)的方法及 Oracle Application表命名的規(guī)律 ：-11-可執(zhí)行、并發(fā)程序、請求組和值集的定義 ：-1-2一個報表開發(fā)的實(shí)例:-：5系統(tǒng)中部分表的介紹:：21開發(fā)分頁報表（套打）的步驟 ：2：開發(fā)規(guī)范 ：.： -.：24：附：記錄和數(shù)組使用范例:.: - 28-內(nèi)部資料NO.ERP0002所謂報表開發(fā)就是按照用戶的需求，根據(jù)用戶提供的樣表，運(yùn)用

3、編程的手段，從ERP系統(tǒng)中取岀數(shù)據(jù)展現(xiàn)在頁面上的一個理解需求、查找數(shù)據(jù)、展現(xiàn)結(jié)果的過程。目前開發(fā)報表的方法及工具有很多種，有專門針對報表需求定制的報表系統(tǒng)，通過對sql語句的改寫能實(shí)現(xiàn)特別的報表需求，這樣的系統(tǒng)有很強(qiáng)的針對性和限制性；還有運(yùn)用報表開發(fā)工具根據(jù)用戶需求臨時開發(fā)報表，具有很強(qiáng)的靈活性和應(yīng)用性。本文檔只關(guān)注運(yùn)用 Oracle 在DB中提供的一些開發(fā)工具包，及WEB技術(shù)開發(fā)報表的過程和方法。Oracle 博大精深，如果在實(shí)際開發(fā)過程中遇到困難可以通過網(wǎng)上論壇和oracle 網(wǎng)站獲得技術(shù)幫助。根據(jù)實(shí)際的開發(fā)過程，本文檔從以下幾個方面來說明：一.報表開發(fā)的總體步驟，及一些工具包的簡單介紹開

4、發(fā)步驟1分析客戶提供的樣表2向客戶或者顧問征詢報表中每個值的意思，務(wù)必理解所需開發(fā)報表的意向3需要客戶在ERP指明數(shù)據(jù)的取處4自己動手在測試環(huán)境中做一遍，梳理一下流程5清楚報表的參數(shù)是什么6以包的形式組織數(shù)據(jù)，在包里面實(shí)現(xiàn)各種功能7在ERP中定義可執(zhí)行，從而使 ERP和DB建立聯(lián)系8在ERP中定義并發(fā)程序，定義了報表的輸岀文件的類型和參數(shù)9在相應(yīng)的請求組中加入已定義好的并發(fā)程序名稱工具包介紹1 FND_PROFILERetrieve user profile values for the current run-time environmentSet user profile values f

5、or the current run-time environmentFnd_profile.value(ORG_lD )取配置文件的值2 DBMS_OUTPUTThis package enables you to send messages from stored procedures, packages, and triggers在sql window 中輸出結(jié)果Dbms_output.put_line3 FND_GLOBAL模擬環(huán)境Fnd_global. APPS_INITIA LI ZE(user_id,resp_id,resp_appl_id)其中 user_id,resp_id,

6、resp_appl_id可以通過這種方法獲得：幫助 - 診斷- 檢查，在塊中選擇$PROFILES，在字段選擇USER_ID 可以獲得 USER_ID,同樣方法可 以獲得RESP_ID,RESP_APPL_ID（可能有時你需要在 Oracle用上述方法達(dá)到你的目的)Fnd_global. User_nameFnd_global. User_id4 UTL_FILEApplications環(huán)境外運(yùn)行一些 PL/SQL語句，但是這些語句中需要訪問系統(tǒng)相關(guān)的環(huán)境變量，例如view，這樣你可以使The UTL_FILE package lets your PL/SQLprogramsread and

7、writeoperatingsystem(OS)textfiles.It provides a restricted version of standard OS stream file input/output (I/O).The fileI/O capabilitiesare similarto thoseof thestandardoperatingsystemstreamfileI/O(OPEN,GET, PUT, CLOSE), with some limitations.For example, call the FOPEN functionto returna filehandl

8、e,which youthenuse insubsequent得到當(dāng)前用戶的名稱 得到當(dāng)前用戶的idcalls to GET_LINE or PUT to perform stream I/O to a file. When you are done performing I/Oon the file, call FCLOSE to complete any output and to free any resources associated with the file.UTL_FILE.FILE_TYPE定義文件指針創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Ku

9、donet Page 3 of 28內(nèi)部資料NO.ERP0002utl_file.fopen(dir,name,mode) Opens a file for input or output with the default line sizeutl_file.put_line( , ) Writes a line to a file向文件中寫數(shù)據(jù)utl_file.fclose_all Closes all open file handles關(guān)閉所有已打開的文件指針5 FJ_FUNC客戶化的函數(shù)包，定義了一些在實(shí)際開發(fā)過程中用到一些方法和變量在實(shí)際開發(fā)過程中，如果需要一些方法時可以在這個包中查找

10、，如果通用性比較強(qiáng)的過程也可以加到這個包里詳細(xì)可以參閱這個包6 FJ_OUTPUT客戶化的報表格式生成包，封裝了 html語法詳細(xì)可以參閱這個包二. Pl/sql 語法及pl/sql函數(shù)、數(shù)組、記錄、游標(biāo)、視圖、基表、臨時表、異常、Pl/sql 基本元素的使用SelectThe SELECT statement allows you to retrieve records from one or more tables in your database.The syntax for the SELECT statement is:SELECT columns FROM tables WHERE

11、 predicatesDistinctThe DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statementsThe syntax for the DISTINCT clause is:SELECT DISTINCT columnsFROM tables WHERE predicatesEXISTSThe EXISTS condition is considered to be met if the su

12、bquery returns at least one rowThe EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.Example #1The following is an SQL statement that uses the EXISTS condition:SELECT * FROM suppliersWHERE EXISTS(select * from orders where suppliers.supplier_id = orders.supp

13、lier_id);This select statement will return all records from the suppliers table where there is at least one record in the orders tableith the same supplier_id.Example #2 - NOT EXISTSThe EXISTS condition can also be combined with the NOT operator.For example,SELECT * FROM suppliersWHERE not exists (s

14、elect * from orders Where suppliers.supplier_id = orders.supplier_id);This will return all records from the suppliers table where there are no records in the orders table for the given supplier_idInThe IN function helps reduce the need to use multiple OR conditionsThe IN functioncan be used in any v

15、alid SQL statement - select, insert, update, or delete.Example #1內(nèi)部資料NO.ERP0002The following is an SQL statement that uses the IN function:SELECT *FROM supplierWHERE supplier_name in ( IBM , Hewlett Packard, Microsoft);This would return all rows where the supplier_name is either IBM, Hewlett Packard

16、, or Microsoft.Because the * is used in the select, all fields from the supplier table would appear in the result set.Example #2 NOT INThe IN function can also be combined with the NOT operator.For example,SELECT *FROM supplierWHERE supplier_name not in ( IBM , Hewlett Packard, Microsoft);This would

17、 return all rows where the supplier_name is neither IBM, Hewlett Packard, orMicrosoft. Sometimes,it is more efficientto the values that you do want.to listthe values thatyou do not want,as opposed內(nèi)部資料NO.ERP0002內(nèi)部資料NO.ERP0002of an SQL statement. ThisLikeT he LIKE condition allows you to use wildcards

18、 in the where clause allows you to perform pattern matching. The LIKE condition can be used in any valid SQLstatement - select, insert, update, or delete. The patterns that you can choose from are:% allows you to match any string of any length (including zero length) _ allows you to match on a singl

19、e characterSELECT * FROM supplier WHERE supplier_name like Hew%; SELECT * FROM supplier WHERE supplier_name like %bob%;SELECT * FROM supplier WHERE supplier_name not like T%;SELECT * FROM supplier WHERE supplier_name like Sm_thGROUP BYThe GROUP BY clause can be used in a SELECT statement to collect

20、data across multiple records and group the results by one or more columns.The syntax for the GROUP BY clause is:SELECT columnl, column2, . column_n, aggregate_function (expression)FROM tablesWHERE predicatesGROUP BY columnl, column2, . column_n;aggregate_function can be a function such as SUM, COUNT

21、, MIN, or MAX.Example using the SUM functionFor example, you could also use the SUM function to return the name of the department andthe total sales (in the associated department).SELECT department, SUM (sales) as Total salesFROM order_detailsGROUP BY department;Because you have listed one column in

22、 your SELECT statement that is not encapsulated in theSUM function, you must use a GROUPBY clause. The department field must, therefore, be listed 創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page 5 of 28in the GROUP BY section.HavingThe HAVING clause is used in combination with the GROUP BY clause. I

23、t can be used in a SELECT statement to filter the records that a GROUP BY returns.The syntax for the HAVING clause is:SELECT column1, column2, . column，aggregate_function (expression)FROM tables WHERE predicates GROUP BY column1, column2, . column_nHAVING condition1 . condition_n;aggregate_function

24、can be a function such as SUM, COUNT, MIN, or MAX.Example using the SUM functionFor example, you could also use the SUM function to return the name of the department andthe total sales (in the associated department). The HAVING clause will filter the resultsso that only departments with sales greate

25、r than $1000 will be returned.SELECT department, SUM (sales) as Total salesFROM order_details GROUP BY department HAVING SUM (sales) 1000ORDER BYThe ORDER BY clause allows you to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.The syntax for the ORDER B

26、Y clause is:SELECT columns FROM tablesWHERE predicates ORDER BY column ASC/DESC;The ORDER BY clause sorts the result set based on the columns specified. If the ASC orDESC value is omitted, the system assumed ascending order.ASC indicates ascending order. (default)DESC indicates descending order.Exam

27、ple #1SELECT supplier_city FROM supplier WHERE supplier_name = IBM ORDER BY supplier_city;This would return all records sorted by the supplier_city field in ascending order.Example #2SELECT supplier_cityFROMsupplier WHEREsupplier_name = IBM ORDERBY supplier_city DESC;This would return all records so

28、rted by the supplier_city field in descending order.UNIONThe UNION query allows you to combine the result sets of 2 or more select queries. Itremoves duplicate rows between the various select statements.Each SQL statement within the UNION query must have the same number of fields in the result sets

29、with similar data types.The syntax for a UNION query is:select fieldl, field2, field_n from tablesUNIONselect fieldl, field2, field_n from tablesUNION ALLThe UNION ALL query allows you to combine the result sets of 2 or more select queries.創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page 6 of 28It re

30、turns all rows (even if the row exists in more than one of the select statements).Each SQL statement within the UNION ALL query must have the same number of fields in theresult sets with similar data types.The syntax for a UNION ALL query is:select field1, field2, field_n from tablesUNION ALLselect

31、field1, field2, field_n from tables;UPDATEThe UPDATE statement allows you to update a single record or multiple records in a table.The syntax the UPDATE statement is:UPDATE table SET column = expression WHERE predicatesINSERTThe INSERT statement allows you to insert a single record or multiple recor

32、ds into a table.The syntax for the INSERT statement is:INSERT INTO table(column-1, column-2, . column-n)VALUES(value-1, value-2, . value-n)DELETEThe DELETE statement allows you to delete a single record or multiple records from a tableThe syntax for the DELETE statement is:DELETE FROM table WHERE pr

33、edicatespl/sql 函數(shù)substr (stri ng, start_positi on, le ngth)This function allows you to extract a substring from a stringFor Example:substr (This is a test, 6, 2) would return issubstr (This is a test, 6) would return is a testsubstr (Tech on the Net, 1,4) would return Techdecode ( expression , searc

34、h , result , search , result. , default)This function has the functionality of an IF-THEN-ELSE statementFor Example:SELECT supplier_name,decode (supplier_id, 10000,IBM,10001, Microsoft,10002, Hewlett Packard, Gateway) resultFROM suppliersinstr (string1, string2, start_position, nth_appearance)This f

35、unction returns the location of a substring in a stringFor example:instr (Tech on the net,e)would return 2; the first occurrence of einstr (Tech on the net,e,1,1)would return2; the first occurrence of einstr (Tech on the net,e,1,2)would return11; the second occurrence of einstr (Tech on the net,e,1,

36、3)would return14; the third occurrence of e創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page 7 of 28instr (Tech on the net, e, -3, 2) would return 2Trim (text)This function removes leading and trailing spaces from a stringFor exampleTrim ( Tech on the Net) would return Tech on the NetTrim ( Alphabet )

37、 would return AlphabetRTrim (text)This function removes trailing spaces from a stringFor example:RTrim (Tech on the Net ) would return Tech on the NetRTrim ( Alphabet ) would return AlphabetLTrim (text)This function removes leading spaces from a stringFor example:LTrim ( Tech on the Net) would retur

38、n Tech on the NetLTrim ( Alphabet ) would return Alphabet to_number (string1, format_mask, nls_language)This function converts a string to a numberFor example:to_number (1210.73, 9999.99) would return the number 1210.73to_number (546, 999) would return the number 546to_number (23, 99) would return t

39、he number 23to_char (value, format_mask, nls_language)This function converts a number or date to a stringExamples - NumbersThe following are number examples for the to_char function.to_char (1210.73, 9999.9) would return 1210.7to_char (1210.73, 9,999.99) would return 1,210.73to_char (1210.73, $9,999

40、.00) would return $1,210.73to_char (21, 000099) would return 000021The following are date examples for the to_char function.to_char (sysdate, yyyy/mm/dd); would return 2003/07/09to_char (sysdate, Month DD, YYYY); would return July 09, 2003to_char (sysdate, FMMonth DD, YYYY); would return July 9, 200

41、3 to_char (sysdate, MON DDth, YYYY); would return JUL 09TH, 2003 to_char (sysdate, FMMON DDth, YYYY); would return JUL 9TH, 2003 to_char (sysdate, FMMon ddth, YYYY); would return Jul 9th, 2003to_date (string1, format_mask, nls_language)This function converts a string to a date、nvlKudonet Page 8 of 2

42、8創(chuàng)建時間：2005-2-28 20:31:00作者：lisr公司:For example:to_date (2003/07/09, yyyy/mm/dd); would return a date value of July 9, 2003.to_date (070903, MMDDYY); would return a date value of July 9, 2003.to_date (20020315, yyyymmdd); would return a date value of Mar 15, 2002nvl (string1, replace_with )This functi

43、on lets you substitutes a value when a null value is encounteredExample #1:select NVL (supplier_city, n/a) from suppliers*數(shù)組的定義Type NumArray Is Table Of Number Index By Binary_IntegerType StrArray Is Table Of Varchar2(500) Index By Binary_Integer記錄的定義Type rp_tb_type Is Record(TbBorder Varchar2(10),T

44、bCssBoolean,TdCssBoolean)游標(biāo)的定義Cursor cur_line IsSelect tb.Month, tb.loct_onhandFrom fj_rp_opm003_tmp_tb tbWhere tb.item_no = row_head.item_no;row_line cur_line%Rowtype;視圖的定義sql語句把一個或多個表連接在一起形成的視圖是一個虛擬的、不是物理存在的表，他是通過Create or replace view ic_item_v AsSelect * from ic_item_mst_b基表是一個物理存在的表，能以表格的形式存儲數(shù)據(jù)

45、，是數(shù)據(jù)的載體Create table table_name(coll varchar2(100),col2 varchar2(10)臨時表1會話特有的臨時表CREATE GLOBAL TEMPORARY ( )ON COMMIT PRESERVE ROWS ；2事務(wù)特有的臨時表CREATE GLOBAL TEMPORARY ( )ON COMMIT DELETE ROWS ；CREATE GLOBAL TEMPORARY TABLE MyTempTable創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page 9 of 28所建的臨時表雖然是存在的，但

46、是insert一條記錄然后用別的連接登上去select ，記錄是空的-ON COMMIT DELETE ROWS說明臨時表是事務(wù)指定，每次提交后ORACLE將截斷表（刪除全部行）-ON COMMIT PRESERVE ROWS 說明臨時表是會話指定，當(dāng)中斷會話時ORACLE將截斷表異常PL/SQL 處理異常不同于其他程序語言的錯誤管理方法，PL/SQL的異常處理機(jī)制與 ADA很相似，有一個處理錯誤的全包含方法。當(dāng)發(fā)生錯誤時，程序無條件轉(zhuǎn)到異常處理部分，這就要求代碼要非常干凈并把錯誤處理部分和程序的其它部分 分開。oracle 允許聲明其他異常條件類型以擴(kuò)展錯誤/異常處理。這種擴(kuò)展使 PL/SQ

47、L的異常處理非常靈活。當(dāng)一個運(yùn)行時錯誤發(fā)生時，稱為一個異常被拋岀。PL/SQL程序編譯時的錯誤不是能被處理得異常，只有在運(yùn)行時的異常能被處理。在 PL/SQL程序設(shè)計中異常的拋岀和處理是非常重要的內(nèi)容。由三種方式拋岀異常.通過PL/SQL運(yùn)行時引擎.使用RAISE語句.調(diào)用 RAISE_APPLICATION_ERROR 存儲過程當(dāng)數(shù)據(jù)庫或PL/SQL在運(yùn)行時發(fā)生錯誤時，一個異常被PL/SQL運(yùn)行時引擎自動拋出。異常也可以通過RAISE語句拋出 RAISE exception_name;三. HTML技術(shù)和CSS修飾HTML技術(shù)HTML英語意思是：Hypertext Marked Langua

48、ge ，即超文本標(biāo)記語言，是一種用來制作超文本文檔的簡單標(biāo)記語言。用HTML編寫的超文本文檔稱為 HTML文檔，它能獨(dú)立于各種操作系統(tǒng)平臺（如 UNIX，WINDOWS等）o1 HTML 的基本結(jié)構(gòu)超文本文檔分文檔頭和文檔體兩部分，在文檔頭里，對這個文檔進(jìn)行了一些必要的定義，文檔體中才是要顯示的各種文檔信息。頭部信息v/HEAD文檔主體，正文部分v/BODY其中vHTML在最外層，表示這對標(biāo)記間的內(nèi)容是HTML文檔。我們還會看到一些Hompage省略標(biāo)記，因?yàn)?html 或.htm 文件被 Web瀏覽器默認(rèn)為是 HTML文檔。之間包括文檔的頭部信息，如文檔總標(biāo)題等，若不 需頭部信息則可省略此標(biāo)

49、記。標(biāo)記一般不省略，表示正文內(nèi)容的開始。2 在報表的實(shí)際過程中，最重要的是針對標(biāo)記table 的應(yīng)用，表格的基本結(jié)構(gòu).v/table定義表格.v/caption定義標(biāo)題定義表行定義表頭定義表元（表格的具體數(shù)據(jù)）表中數(shù)據(jù)左右排列方式通過align屬性來定義left 、right 、center表中數(shù)據(jù)上下排列方式通過valign屬性來定義top、middle 、bottom3 跨多行、多列的表元創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page 10 of 28內(nèi)部資料NO.ERP0002要創(chuàng)建跨多行、多列的表元，只需在或中加入ROWSPAN或 COL

50、SPAN屬性，這兩個屬性的值，表明了表元中要跨越的行或列的個數(shù)?？缍嗔械谋碓獎?chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page 13 of 28內(nèi)部資料NO.ERP0002創(chuàng)建時間：2005-2-28 20:31:00 作者：lisr 公司：Kudonet Page # of 28內(nèi)部資料NO.ERP0002colspan表示跨越的列數(shù)，例如跨多行的表元 colspan=2表示這一格的寬度為兩個列的寬度rowspan所要表示的意義是指跨越的行數(shù)，例如rowspan=2 就表示這一格跨越表格兩個行的高度4 插入圖象的標(biāo)簽是，其格式為：vIMG SRC=

51、圖形文件地址SRC屬性指明了所要鏈接的圖象文件地址，這個圖形文件可以是本地機(jī)器上的圖形，也可以是位于遠(yuǎn)端主機(jī)上的圖形。 地址的表示方法可以沿用上一篇內(nèi)容文件的鏈接”中URL地址表示方法。例： vIMG SRC=images/ball.gifIMG還有兩個屬性是 HEIGHT和WIDTH，分別表示圖形的高和寬。通過這兩個屬性，可以改變圖形的大小，如果沒有 設(shè)置，圖形按原大顯示CSS修飾CSS是Cascading Style Sheets（層疊樣式表單）的簡稱。更多的人把它稱作樣式表。顧名思義，它是一種設(shè)計網(wǎng)頁樣式的工具實(shí)際上CSS的代碼都是由一些最基本的語句構(gòu)成的。它的基本語句的結(jié)構(gòu)是這樣的：選

52、擇符屬性：屬性值 一般說來， 下面的CSS語句是以注釋語句的形式書寫的。在報表中預(yù)置了以下 css （具體請參閱包FJ_OUTPUT）：vstyle type=text/cssbodymargin-top:0;td font-family:宋體;font-size:9pt;font-weight:normal;color:black;text-align:center;BACKGROUND-COLOR:white;height:27;vertical-align:middle;.td_cssborder:solid windowtext .5pt;.tb_cssborder-collapse:

53、collapse;border:solid windowtext .5pt;四. 查找數(shù)據(jù)的方法及 Oracle Application表命名的規(guī)律查找數(shù)據(jù)的方法請參閱在ERP系統(tǒng)中查找數(shù)據(jù)的方法Oracle Application表命名的規(guī)律一般來說，在Applications中所有的表的命名都是相當(dāng)規(guī)范，通過名字，一般都可以知道這個表是做什么用，而且還可以通過查看FND_TABLES和FND_COLUMNS來獲得表的詳細(xì)信息。除此之外，還有一些規(guī)則，例如以 TL結(jié)尾表示帶 有語言信息的表，V結(jié)尾表示一般的視圖， VL表示帶有語言信息的視圖，以V$開頭代表動態(tài)性能試圖，以FND開頭是屬于Application Object Library模塊的，以 AR,RA開頭是屬于 Oracle Receivables模塊的，以 MTL開頭是屬于庫存模塊的，以AP開頭是屬于應(yīng)收模塊的，以GL開頭是屬于總帳模塊的，以FA開頭是屬于資產(chǎn)模塊的，以O(shè)E開頭的是屬于訂單模塊的，以WSH開頭是屬于發(fā)運(yùn)模塊的，以WIP開頭是屬于在制車間模塊的，以IBE開頭是屬于網(wǎng)上商店的，etc ;而且根據(jù)主從表的關(guān)系， Ap