Bursting Engine Implementation

其實這部份在一個月前就已經完成測試了
不過現在才有空寫上來

基本上
Tim的Blog提供的例子與架構已經很清楚了
但是習慣上會照著做一個不同的報表來用看看...
在做的過程中
才會知道有那些是重要的...要克服的問題..
否則看別人的Code...一切都是那麼理所當然...自己做看看才知道..即使只是模仿

我的測試分兩部份
第一部份是不管Data怎麼出來的部份...假設已有xml了
然後搭配JSP與Control file
看看有沒有什麼問題

如果第一部份沒問題
那麼第二部份的測試...就是把它完整地implement出來
從rdf開始..含report trigger..並掛到Server上跑
讓它完全如Tim的例子一樣運作完美...

(註:由於我用的是XMLP 5.6.2
所以要自己寫JCP..聽說5.6.3就不用了...)

第一部份:
* Control file : burst.xml 放到/tmp/下
* Data file : emp.xml 放到/tmp/下
* Rtf Template : test.rtf 放到/tmp/下
* JCP: BurstingTest.java compile後掛上Server..
主要在JCP中就會依Control file與Data去抓Template並做Bursting
因此測試時跑JCP就好

這邊卡住的問題有
1. JCP Compile問題
一開始抓下來的Java檔...居然在Compile時就有問題...
經過確認..
發現程式中用OADocumentProcessor是有問題的
http://forums.oracle.com/forums/thread.jspa?messageID=1843531
要使用DocumentProcessor

所以import的部份多加了
import oracle.apps.xdo.batch.DocumentProcessor;

程式中的這一段
lfile.writeln("Starting burst ...",1);

OADocumentProcessor dp = new OADocumentProcessor("/tmp/burst.xml","/tmp/emp.xml","/tmp");

lfile.writeln("Bursting initiated ... ",1);

也改為
file.writeln("Starting burst ...",1);

DocumentProcessor dp = new DocumentProcessor("/tmp/burst.xml","/tmp/emp.xml","/tmp");

lfile.writeln("Bursting initiated ... ",1);

2. Data file的問題
我的emp.xml中
用到幾個會有問題的tag...我用了EMP、EMPLOYEE、NAEM這類的....
結果在5.6.2中...不管我資料有幾筆...都只是Burst出第一筆...
這裡卡了很久
後來才知道可能是Bug
改了Tag後...就沒有問題了
http://forums.oracle.com/forums/message.jspa?messageID=1904432#1904432

這邊兩個問題都要感謝Ashish的協助

第二部份:
* Control file : burst.xml 放到/tmp/下
* Report file : BURSTDEMO.rdf 放到Server上
* Rtf Template : test.rtf 放到/tmp/下
* JCP: XMLPReportBurst.java compile後掛上Server..
主要是跑掛上Server的Report: BURSTDEMO..它產生的output就是XML格式
而由於它的After Report Trigger會去Call JCP
因此...JCP會抓得到它XML的output..加上Control file與template
一氣呵成完成後半部的Bursting動作

這部份與第一部份的差異
就在於怎麼去找到rdf的xml output
主要問題在
1. JCP怎麼寫
這邊沒有太大的麻煩..
Tim的例子本身就有這個功能..
只要傳入request id當參數就好...
甚至還能回過頭去改Request畫面上的Request Name...

2. RDF的After Report怎麼把自己的Request id當參數傳給JCP
這裡卡了一陣子
因為~~自己都還沒跑出去..要怎麼知道自己的Request id??
可以看這邊
http://zhxiangxie.blogspot.com/2007/06/how-to-get-this-request-id.html

而完成第一部份測試
加上克服第二部份的問題後
RDF到Bursting的路就走出來了

應該可以用來取代之前用JavaMail與iText寫的那一段功能...

Function with DML and Commit

之前寫到一個需求
需要動態產生一組Sequence..
且Sequence不只是流水號
而是有前置字元的流水號
例如CF0703001(XXYYMMSSS)這樣的型態

當用Function來產生這個流水號時
同時要在Table中記錄已用到那一個號碼
這樣的話
Function中就會有Update的DML語法...
在Compile時會發生ORA-01655的Error
表示function不應包括DML的SQL

然而...可以透過PRAGMA AUTONOMOUS_TRANSACTION的宣告
避開此Error
如下

CREATE OR REPLACE
FUNCTION tw_get_cks_seq
( i_seq_type IN varchar2)
RETURN varchar2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
--associated obj: tw_cks_sequence
o_seq_no varchar2(20);
v_check varchar2(20);
BEGIN
select seq_last
into v_check
from tw_cks_sequence
where seq_type=i_seq_type;

if substr(v_check,3,4)=to_char(sysdate,'YYMM') then
--use next
select i_seq_type||to_char(sysdate,'YYMM')||trim(to_char(seq_cur+1,seq_length))
into o_seq_no
from tw_cks_sequence
where seq_type=i_seq_type;


update tw_cks_sequence
set seq_cur=seq_cur+1,
seq_last=o_seq_no,
last_updated_date=sysdate
where seq_type=i_seq_type;

COMMIT;

else
--use 1
select i_seq_type||to_char(sysdate,'YYMM')||trim(to_char(1,seq_length))
into o_seq_no
from tw_cks_sequence
where seq_type=i_seq_type;

update tw_cks_sequence
set seq_cur=1,
seq_last=o_seq_no,
last_updated_date=sysdate
where seq_type=i_seq_type;

COMMIT;
end if;

RETURN o_seq_no ;
EXCEPTION
WHEN others THEN
return SQLCODE||':'||SQLERRM ;
END;
/

How to get THIS request id

在implement XMLP Bursting Engine時
會有一個動作
是將RDF報表產生的XML丟到JCP上面
這一個動做會有一個需求
就是在RDF中的After Report Trigger..
將本身的request id拋到JCP上當參數
那麼必須知道...該怎麼抓取...目前這個Concurrent Program的Request id

據說在Application Developer的User Guide中有詳細的說明
做法有點Tricky...
1. 宣告Number型態的User Parameter : P_CONC_REQUEST_ID
2. 在Before Report Trigger中.. : srw.user_exit( 'FND SRWINIT' )
3. 在After Report Trigger中 : srw.user_exit( 'FND SRWEXIT' )
4. 這樣就可以抓到目前的 request id 於 P_CONC_REQUEST_ID 參數中

在Oracle Forum中我也有記著..
http://forums.oracle.com/forums/message.jspa?messageID=1899700#1899700

Form Submit Report - Form Personalization

在部分Form的操作上
切換到Request Submit並輸入報表參數會有一點浪費時間
例如
跑一支報表檢查S/O的內容
或跑一支列印P/O的報表
其實原本要輸入的參數就已經在Form上面了
通常必須Copy order number
然後View > Request > Submit
再貼上order number為參數

然而透過Form Personalization可以減少這樣的成本
要知道的技巧只有兩個
1. 如何由Form call procedure..並傳入參數
2. 如何直接拋出一個Concurrent
以下說明

1. Form call procedure :
透過Form Personalization
可以定義Menu..
並由Menu Built in procedure...

然而要注意的是
procedure的寫法
主要是" =' ' "
等號加上一個字串(procedure的內容..可含Declare..Begin..End)
而當字串中要顯示單引號時
要用'''來表示

2. Submit Concurrent Program
就用FND_REQUEST.SUBMIT_REQUEST這支API就好了

要注意的是..
如果有n個參數
第n+1的參數要放Chr(0)表示參數結束
此外...一定要記得下Commit...

BIP - dynamic grouping and calculating

一般會建議在D2K Report做Data的group與小計加總等
因為在D2K Report裡做這些事會比較簡單...
拉一拉就好了
但是...有幾個小小的缺點
1. 欠缺彈性.
D2K裡的group是固定的...以S/O銷售資料為例...
當拉了客戶別-產品-detail的group structure...
就無法輕易用於產品別-客戶-detail的group structure...
因此當有客戶別 /產品別 / 業務別 /地區別 ..各種報表...
則可能要寫四個...
但根本上的row data其實是一樣的...
2. 設定煩雜
D2K寫Data.. BIP做Template的方式...
至少都要多定義一個excutable來對應RDF檔..
承1..至少要多定義4個executable...
但如果sql只是簡單的抓row data..
則直接用data template的XML與XSD來做...
Concurrent program直接call XDODTEXE這支JCP就好了

因此..
當報表會因不同group條件而分支出多個子報表時
不妨用Data template抓row data...然而直接在RTF template上做動態的分組和加總就好

以下為幾個常用的技巧
假設資料為
<ROW>
<AAA> ... </AAA>
<BBB> ... </BBB>
<CCC> ... </CCC>
</ROW>
的架構
1. Dynamic Grouping
一般用 <?for-each?> ~ <?end for-each?> 只能抓取row data
需要用 <?for-each-group:ROW;AAA?> ~ <?end for-each-group?>
則可以對每一個不同的AAA做分組...

而對於分組內資料的取用..
則使用 <?for-each:current-group()?> ~ <?end for-each?>

假設要做巢式分組...AAA的組內還要依BBB再分
則這麼寫
------- <?for-each-group:ROW;AAA?>
---------------<?for-each-group:current-group();BBB?>
--------------------- <?for-each:current-group()?>
-----------------------------do some thing in current-group
--------------------- <?end for-each?>
---------------<?end for-each-group?>
------- <?end for-each-group?>

2. Group Calculating
在分組小計的時後
特別要注意的是...目前在第幾層
而原本的資料欄位都在ROW這一層
-------<?for-each-group:ROW;AAA?>
---------------<?for-each-group:current-group();BBB?>
--------------------- <?for-each:current-group()?>
-----------------------------do some thing in current-group
-----------------------------<?sum(current-group()//CCC)?> 加總AAA-BBB組內的CCC
--------------------- <?end for-each?>
---------------<?end for-each-group?>
---------------<?sum(current-group()/CCC)?> 加總AAA組內的CCC
------- <?end for-each-group?>

3. Powerful and Flexible-Variables
透過定義變數
<?xdoxslt:set_variable($_XDOCTX,'VAR_NM',VAR_Val)?>
指定變數
<?xdoxslt:set_variable($_XDOCTX,'VAR_NM',VAR_Val)?>
和呼叫變數
<?xdoxslt:get_variable($_XDOCTX,'VAR_NM')?>
可以輕易的做 grant total總計等運算

加上if等條件判斷式
也可以做條件換頁等動作

4. Page Break
一般而言
還是用<xsl:if xdofo:ctx="inblock" test="1=1"><xsl:attribute name="break-before">page</xsl:attribute></xsl:if>的語法就可以了

XMLP Transfer via FNDLOAD / XDOLoader

之前有記FNDLOAD由Test instance搬移資料到Prod instance的方式

然而XMLP的相關資料也可以做這樣的搬移
一樣可透過FNDLOAD

*DOWNLOAD
FNDLOAD apps/apps 0 Y DOWNLOAD
$XDO_TOP/patch/115/import/xdotmpl.lct XMLPData.ldt XDO_DS_DEFINITI
ONS
APPLICATION_SHORT_NAME=AR DATA_SOURCE_CODE=ARXCOBLX
TMPL_APP_SHORT_NAME=AR
{TEMPLATE_CODE=ARLETTER1}

*UPLOAD
FNDLOAD apps/apps 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XMLPData.ldt

另外可以透過XDOLoader
*DOWNLOAD
% java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-LOG_FILE


*UPLOAD
% java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-XDO_FILE_TYPE \
-NLS_LANG \
-FILE_CONTENT_TYPE \
-FILE_NAME \
-OWNER \
-CUSTOM_MODE [FORCENOFORCE] \
-LOG_FILE

詳細可再參考XMLP User Guide...

Oracle + Java - PDF Issue using iText

如上一篇
善用Oracle 9i上的Java技術
讓EBS擴充上更有彈性

雖然XML Publisher已經可以產生非常漂亮的PDF格式報表
但是在我還不知道XML Publisher時
測試也可以透過萬能的Java技術來完成PDF報表的製作

當時我所碰到的需求是
將text格式的報表結果
轉換為pdf格式...

透過itext
還可以加頁首頁尾/浮水印/密碼限制
都有詳細的範例碼
雖然沒有XML Publisher方便
但是功能上甚至比XML Publisher更多變...
因為它是Java base PDF creator..

1. 確認版本與loadjava
　
　itext並不像javax一樣...已經在資料庫中了
　但Oracle Client有提供loadjava的機制
因此沒有也沒關係啦...把它放上去就好了~~~

然而itext有1.4.5版..load上去會因為11.5.10.2的jdk版本問題而無法正確compile
　因此後來改用itext1.3的版本

loadjava –user apps/apps@SERVER–resolve –force itext-1.3.jar

2. Java Source and Compile

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "iTextJPDF"
AS import java.awt.Color;
import java.io.FileOutputStream;
import java.io.*;
import com.lowagie.text.Annotation;
import com.lowagie.text.Chunk;
import com.lowagie.text.Document;
import com.lowagie.text.Element;
import com.lowagie.text.ExceptionConverter;
import com.lowagie.text.Font;
import com.lowagie.text.Image;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Phrase;
import com.lowagie.text.Rectangle;
import com.lowagie.text.pdf.BaseFont;
import com.lowagie.text.pdf.PdfContentByte;
import com.lowagie.text.pdf.PdfGState;
import com.lowagie.text.pdf.PdfPTable;
import com.lowagie.text.pdf.PdfPageEventHelper;
import com.lowagie.text.pdf.PdfTemplate;
import com.lowagie.text.pdf.PdfWriter;
/*
* Modify form iText tutorial
* by zhxiang @ 2006-11-14
*/
public class iTextJPDF extends PdfPageEventHelper {
/** An Image that goes in the header. */
public Image headerImage;
/** The Graphic state */
public PdfGState gstate;
/** A template that will hold the total number of pages. */
public PdfTemplate tpl;
/** The font that will be used. */
public BaseFont helv;

public static int JCvtPDF1 (String file_src,String file_des){
try {
// step 1: creating the document
Document doc = new Document(PageSize.A4, 50, 50, 20, 20);
// step 2: creating the writer
PdfWriter writer = PdfWriter.getInstance(doc, new FileOutputStream(file_des));
// step 3: initialisations + opening the document
writer.setPageEvent(new iTextJPDF());
//-------------
Font NewFont = new Font(Font.COURIER, 9, Font.BOLD);
FileReader fr1 = new FileReader(file_src);
BufferedReader buf_fr1 = new BufferedReader(fr1);
String strReadLine;
//-------Metadata------
doc.addTitle("Invoice and Packing List");
doc.addSubject("Twinhead Invoice and Packing List");
doc.addCreator("Twinhead MIS using iText java tech");
doc.addAuthor("Twinhead MIS");
//-------------
doc.open();
// step 4: adding content
Annotation ann = new Annotation(250f, 400f, 350f, 500f,
"http://www.twinhead.com/");
doc.add(ann);
Image jpg = Image.getInstance("/tmp/logo.jpg");
jpg.scalePercent(90);
jpg.setAnnotation(ann);
doc.add(jpg);
strReadLine = buf_fr1.readLine();
while (strReadLine != null)
{
if (strReadLine.length()==0){
strReadLine=" ";
}
doc.add(new Paragraph(strReadLine,NewFont));
strReadLine = buf_fr1.readLine();
}

buf_fr1.close();
// step 5: closing the document
doc.close();
}
catch ( Exception e ) {
e.printStackTrace();
}
return 1;
}

public void onOpenDocument(PdfWriter writer, Document document) {
try {
// initialization of the header table
headerImage = Image.getInstance("/tmp/logo.jpg");

// initialization of the Graphic State
gstate = new PdfGState();
gstate.setFillOpacity(0.3f);
gstate.setStrokeOpacity(0.3f);
// initialization of the template
tpl = writer.getDirectContent().createTemplate(100, 100);
tpl.setBoundingBox(new Rectangle(-10, -10, 100, 100));
// initialization of the font
helv = BaseFont.createFont("Helvetica", BaseFont.WINANSI, false);
}
catch(Exception e) {
throw new ExceptionConverter(e);
}
}

public void onEndPage(PdfWriter writer, Document document) {
PdfContentByte cb = writer.getDirectContent();
cb.saveState();

// compose the footer
String text = "Page " + writer.getPageNumber() + " of ";
float textSize = helv.getWidthPoint(text, 9);
float textBase = document.bottom() - 10;
cb.beginText();
cb.setFontAndSize(helv, 9);
// for odd pagenumbers, show the footer at the left
//if ((writer.getPageNumber() & 1) == 1) {
cb.setTextMatrix((document.right()-document.left())/2, textBase);
cb.showText(text);
cb.endText();
cb.addTemplate(tpl, (document.right()-document.left())/2 + textSize, textBase);
cb.saveState();
// draw a Rectangle around the page
cb.setColorStroke(Color.black);
cb.setLineWidth(1);
cb.rectangle(20, 20, document.getPageSize().width() - 40, document.getPageSize().height() - 40);
cb.stroke();
cb.restoreState();
// starting on page 3, a watermark with an Image that is made transparent
if (writer.getPageNumber() >= 0) {
cb.setGState(gstate);
cb.setColorFill(Color.pink);
cb.beginText();
cb.setFontAndSize(helv, 48);
cb.showTextAligned(Element.ALIGN_CENTER, "Twinhead International Corp.", document.getPageSize().width() / 2, document.getPageSize().height() / 2, 45);
cb.endText();

cb.restoreState();
}
}


public void onCloseDocument(PdfWriter writer, Document document) {
tpl.beginText();
tpl.setFontAndSize(helv, 9);
tpl.setTextMatrix(0, 0);
tpl.showText("" + (writer.getPageNumber() - 1));
tpl.endText();
}

}
/

itext的使用很簡單(如果看懂的話:p)
主要是五個步驟
step 1: creating the document
step 2: creating the writer
step 3: initialisations + opening the document
step 4: adding content
step 5: closing the document

這段程式中
我是把一整個純文字文件轉為PDF格式
(利用java.io.FileOutputStream的buffer讀寫檔案進去)
並且加頁碼/浮水印與Metadata...
而首頁再加公司logo並超連結到公司首頁

對於CJK的問題
則需要改
helv = BaseFont.createFont("Helvetica", BaseFont.WINANSI, false); 這一句
改為用server上某一個字型檔

3. PL/SQL call Java

CREATE OR REPLACE
FUNCTION tw_cvt_pdf01
( Xfilefrom IN varchar2,
Xfileto IN varchar2)
RETURN number IS
LANGUAGE JAVA
NAME 'iTextJPDF.JCvtPDF1(java.lang.String,java.lang.String) return int';
/

4. Grant Java Permission

主要是我讀純文字報表檔與放pdf結果的位置access權限

exec dbms_java.grant_permission('APPS',
'java.io.FilePermission',
'/report_out_dir/*',
'read');

exec dbms_java.grant_permission('APPS',
'java.io.FilePermission',
'/tmp/*',
'read, write');

5. Application..

如此可以用tw_cvt_pdf01
輕而易舉地轉出PDF檔案...

Oracle + Java - Mail Issue using JavaMail

雖然Oracle的Alert有很強大的Mail功能
但是當需求上要求有多個附檔或多個收件人時
應用上並不是那麼容易

既然Oracle 9i上已經與Java有滿不錯地結合
因此可以善用Java網路上廣大的Free Resources
在mail方面..
Java Mail就是一個容易上手的工具...


1. 確認必要Class

在9i資料庫中查Java Class
可以發現Javax.mail與Javax.activation的package已經在資料庫中了
因此要建置Java Mail並不需要再用loadjava置入額外的class
建置Java Mail主要參考Metalink的Note:120994.1

2. 建立Java Source並Compile

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "SendMail"
AS import java.util.*;
import java.io.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;
public class SendMail {
// Zhxiang -test 20060918
// Sender, Recipient, CCRecipient, and BccRecipient are comma-
// separated lists of addresses;
// Body can span multiple CR/LF-separated lines;
// Attachments is a ///-separated list of file names;
public static int Send(String SMTPServer,
String Sender,
String Recipient,
String CcRecipient,
String BccRecipient,
String Subject,
String Body,
String ErrorMessage[],
String Attachments) {
// Error status;
int ErrorStatus = 0;
// create some properties and get the default Session;
System.setProperty("mail.mime.charset", "utf-8" ); //zhxiang 20060918
Properties props = System.getProperties();
props.put("mail.smtp.host", SMTPServer);
Session session = Session.getDefaultInstance(props, null);
try {
// create a message;
MimeMessage msg = new MimeMessage(session);
// extracts the senders and adds them to the message;
// Sender is a comma-separated list of e-mail addresses as
// per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Sender);
msg.addFrom(TheAddresses);
}
// extract the recipients and assign them to the message;
// Recipient is a comma-separated list of e-mail addresses
// as per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Recipient);
msg.addRecipients(Message.RecipientType.TO,
TheAddresses);
}
// extract the Cc-recipients and assign them to the
// message;
// CcRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != CcRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(CcRecipient);
msg.addRecipients(Message.RecipientType.CC,
TheAddresses);
}
// extract the Bcc-recipients and assign them to the
// message;
// BccRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != BccRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(BccRecipient);
msg.addRecipients(Message.RecipientType.BCC,
TheAddresses);
}
// subject field;
msg.setSubject(Subject);
//msg.setHeader("Subject", MimeUtility.encodeText(Subject, "big5", null)); //zhxiang --20060918 --test
//msg.setSubject(MimeUtility.encodeText(Subject, "big5", null)); //zhxiang --20060918 --test
// create the Multipart to be added the parts to;
Multipart mp = new MimeMultipart();
// create and fill the first message part;
{
MimeBodyPart mbp = new MimeBodyPart();
//mbp.setText(Body);
mbp.setContent(Body,"text/html ;charset=utf-8");//--zhxiang 20060918

// attach the part to the multipart;
mp.addBodyPart(mbp);
}
// attach the files to the message;
if (null != Attachments) {
int StartIndex = 0, PosIndex = 0;
while (-1 != (PosIndex = Attachments.indexOf("///",
StartIndex))) {
// create and fill other message parts;
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex,
PosIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
PosIndex += 3;
StartIndex = PosIndex;
}
// last, or only, attachment file;
if (StartIndex < Attachments.length()) {
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
}
}
// add the Multipart to the message;
msg.setContent(mp);
// set the Date: header;
msg.setSentDate(new Date());
// send the message;
Transport.send(msg);
} catch (MessagingException MsgException) {
ErrorMessage[0] = MsgException.toString();
Exception TheException = null;
if ((TheException = MsgException.getNextException()) !=
null)
ErrorMessage[0] = ErrorMessage[0] + "\n" +
TheException.toString();
ErrorStatus = 1;
}
return ErrorStatus;
}
}
/

其中中文CJK的問題
以utf-8編碼解決
在這一行　mbp.setContent(Body,"text/html ;charset=utf-8");

3. PL/SQL call Java

CREATE OR REPLACE
PACKAGE BODY sendmailjpkg AS

PROCEDURE ParseAttachment(Attachments IN ATTACHMENTS_LIST,
AttachmentList OUT VARCHAR2) IS
AttachmentSeparator CONSTANT VARCHAR2(12) := '///';
BEGIN
-- boolean short-circuit is used here;
IF Attachments IS NOT NULL AND Attachments.COUNT > 0 THEN
AttachmentList := Attachments(Attachments.FIRST);

-- scan the collection if there is more than one element. If there
-- is not, skip the next part for parsing elements 2 and above. If there
-- is, skip the first element since it has been already processed

IF Attachments.COUNT > 1 THEN

FOR I IN Attachments.NEXT(Attachments.FIRST) .. Attachments.LAST LOOP
AttachmentList := AttachmentList AttachmentSeparator
Attachments(I);
END LOOP;
ELSE
-- whe have to terminate the list with the one element with /// for the java function

AttachmentList := AttachmentList AttachmentSeparator;
END IF;
ELSE
AttachmentList := '';
END IF;
END ParseAttachment;

-- forward declaration;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING) RETURN NUMBER;

-- high-level interface with collections;
FUNCTION SendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST) RETURN NUMBER IS
AttachmentList VARCHAR2(4000) := '';
AttachmentTypeList VARCHAR2(2000) := '';
BEGIN
ParseAttachment(Attachments,
AttachmentList);
RETURN JSendMail(SMTPServerName,
Sender,
Recipient,
CcRecipient,
BccRecipient,
Subject,
Body,
ErrorMessage,
AttachmentList);
END SendMail;


FUNCTION SendMail(Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST) RETURN NUMBER IS
AttachmentList VARCHAR2(4000) := '';
AttachmentTypeList VARCHAR2(2000) := '';
BEGIN
ParseAttachment(Attachments,
AttachmentList);
RETURN JSendMail('123.456.789.111', --mail server
Sender,
Recipient,
CcRecipient,
BccRecipient,
Subject,
Body,
ErrorMessage,
AttachmentList);
END SendMail;

-- JSendMail's body is the java function SendMail.Send();
-- thus, no PL/SQL implementation is needed;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING
) RETURN NUMBER IS
LANGUAGE JAVA
NAME 'SendMail.Send(java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String[],
java.lang.String) return int';
END SendMailJPkg;
/

在這邊我同名了 JSendMail的function
使開發者在不知道mail server ip之下一樣可以send mail
而且當mail server ip改變時
只要回來改這個程式...
不必動到所有的application...


4. Grant Java Permission

-- Allows access to system properties
exec dbms_java.grant_permission('APPS',
'java.util.PropertyPermission',
'*',
'read,write');

-- Allows access to sockets
exec dbms_java.grant_permission('APPS',
'java.net.SocketPermission',
'*',
'connect, resolve');

-- Allows access to local files
exec dbms_java.grant_permission('APPS',
'java.io.FilePermission',
'/dir/dir/attachments/*',
'read, write');

5. Application

如此可以使用 sendmailjpkg.JSendMail(...)來發mail了
　

XMLP - How to do Page Break?

XMLP(XML Publisher)是導入R11i中發現非常好用的工具之一
目前應該稱它為BI Publisher吧
因為開發團隊的Blog已由XML Publisher Blog改為BI Publisher Blog了
現在已經到5.6.3版了..附在R12上..已成為R12上滿主要的一個引擎了

不過我還只是R11i的入門使用者
現在系統上還是5.0.0的...
上patch後可以到5.6.2吧...

以下記錄XMLP中進行跳頁的方式
主要參考自Oracle Forums的兩篇討論

對目前一支需求報表
一張A4要印恰好兩個料
非常有幫助

[1]. 直接加page-break
< ?split-by-page-break:? >
但是無法搭配if ..(測試搭配if無效)
通常的用法是 < ?split-by-page-break:? > < ?end for-each? >
用於強制換頁

[2]. 使用xsl語法

<.xsl:if xdofo:ctx="inblock" test=$IF-Condition>
<.xsl:attribute name="break-before">page
<./xsl:attribute><./xsl:if>

例如每個G_ORDER_INFO section都換頁
<.xsl:if xdofo:ctx="inblock" test="position()<.count(//G_ORDER_INFO)">
<.xsl:attribute name="break-before">page<./xsl:attribute>
<./xsl:if>
或這個需求中...PAGE參數為偶數就換頁
<.xsl:if xdofo:ctx="inblock" test="PAGE mod 2=0">
<.xsl:attribute name="break-before">page<./xsl:attribute>
<./xsl:if>

XMLP的User Guide較基本...詳細的應用方法還是要自己由討論區與不斷測試來驗證..

SQLLDR Note --Import Flat Data into Oracle DB

http://http://www.orafaq.com/faq/sql_loader

There are some resources for using SQL loader.

For Example:

* Data File (A0001.txt)

C0001 CUSTA
C0002 CUSTB
C0003 CUSTC
C0004 CUSTD
... ...


* Control File (r11i.ctl)

LOAD DATA
INFILE 'A0001.txt'
INTO TABLE XX_CUST_TEMP
FIELDS TERMINATED BY " " optionally enclosed by '"'
(customer_number, customer_name, sales CONSTANT 'S22')


* SQLLDR Syntax

sqlldr apps/apps control=r11i.ctl

FNDLOAD Note

如下為11.5.10可用的FNDLOAD語法
可將各種設定Download轉出flat file為.lct的檔案
搬移此檔案到新的環境
再透過Upload的語法就能完成設定

在導入階段
多個instance環境間(Test / Pilot-Run/Prod)進行設定時非常有幫助

• Value Set
  
  FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ***.ldt VALUE_SET FLEX_VALUE_SET_NAME='***'
  
  FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct ***.ldt
  
  
• Concurrent Program
  
  FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ***.ldt PROGRAM CONCURRENT_PROGRAM_NAME='***' APPLICATION_SHORT_NAME='***'
  
  FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ***.ldt
  
  
• Lookups
  
  FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct ***.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME='***' LOOKUP_TYPE='***'
  
  FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct ***.ldt
  
  
• Menu
  
  FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ***.ldt MENU MENU_NAME='***'
  
  FNDLOAD apps/ 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ***.ldt
  
  
• Responsibility
  
  FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ***.ldt FND_RESPONSIBILITY RESP_KEY='***'
  
  FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ***.ldt
  
  
• Functions/Forms
  
  FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ***.ldt FUNCTION FUNCTION_NAME='***' APPLICATION_SHORT_NAME='***'
  
  FNDLOAD apps/dlseh 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ***.ldt
  
  
• Profile
  
  FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct ***.ldt PROFILE FND_PROFILE_OPTION_VALUES PROFILE_NAME='***' APPLICATION_SHORT_NAME='***'
  
  FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct ***.ldt
  
  
• Request Group
  
  FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct ***.ldt REQUEST_GROUP REQUEST_GROUP_NAME='***' APPLICATION_SHORT_NAME='***'
  
  FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct ***.ldt
  
  
• Attachement
  
  FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct ***.ldt FND_ATTACHMENT_FUNCTIONS FUNCTION_NAME='***'
  
  FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afattach.lct ***.ldt
  
  
• Personalization
  
  FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct ***.ldt FND_FORM_CUSTOM_RULES FUNCTION_NAME='***' APPLICATION_SHORT_NAME='***'
  
  FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct ***.ldt