這範本主要是要介紹4gl 中如何使用java 的JAR . 來幫助我們快速產生電子發票交換資料.
,BDL,4gl,turnkey,A0101
需求 BDL 2.4 以上.(沒記錯應是 2.2以上就有支援JAVA了.
第一要先把環境設定好. CLASSPATH 要設.
以下就是產生 A0101 的程式範本.
###########################################################
#程式開始
###########################################################
IMPORT JAVA com.fourjs.fgl.lang.FglDecimal
IMPORT JAVA java.lang.Number
IMPORT JAVA java.lang.Long
#import java com.thoughtworks.xstream.annotations.XStreamAlias
#import JAVA com.thoughtworks.xstream.annotations.XStreamAsAttribute
import JAVA com.tradevan.gateway.client.einv.parse.ParserHelper
import JAVA com.tradevan.gateway.client.util.annotat.DataObjectAble
import JAVA com.tradevan.gateway.client.einv.transform.TransformHelper
import JAVA com.tradevan.gateway.client.einv.util.EINVVersion
#import JAVA com.tradevan.gateway.client.einv.parse.proc.LineParser
import JAVA com.tradevan.gateway.client.einv.parse.proc.Parser
#import JAVA com.tradevan.gateway.client.einv.parse.proc.XMLParser
#import JAVA com.tradevan.gateway.client.einv.util.EINVConfig
import JAVA com.tradevan.gateway.client.einv.util.EncodingType
#import JAVA com.tradevan.gateway.client.einv.util.InvoiceUtil
#import JAVA com.tradevan.gateway.client.log.GatewayLogger
import JAVA com.tradevan.gateway.client.log.GatewayLoggerHandler
import JAVA com.tradevan.gateway.einv.msg.EINVMessage
import JAVA com.tradevan.gateway.einv.msg.EINVPayload
#import JAVA com.tradevan.commons.io.FileUtil
import JAVA com.tradevan.gateway.client.einv.parse.ParserHelper
import JAVA com.tradevan.gateway.client.util.GatewayUtil
import JAVA com.tradevan.gateway.einv.msg.EINVPayload
import JAVA com.tradevan.gateway.einv.msg.v31.A0101
import JAVA com.tradevan.gateway.einv.msg.v31.A0101Body.AmountType
import JAVA com.tradevan.gateway.einv.msg.v31.A0101Body.DetailsType
import JAVA com.tradevan.gateway.einv.msg.v31.A0101Body.MainType
import JAVA com.tradevan.gateway.einv.msg.v31.A0101Body.ProductItem
import JAVA com.tradevan.gateway.einv.msg.v31.UtilBody.BuyerRemarkEnum
import JAVA com.tradevan.gateway.einv.msg.v31.UtilBody.CurrencyCodeEnum
import JAVA com.tradevan.gateway.einv.msg.v31.UtilBody.CustomsClearanceMarkEnum
import JAVA com.tradevan.gateway.einv.msg.v31.UtilBody.DonateMarkEnum
import JAVA com.tradevan.gateway.einv.msg.v31.UtilBody.InvoiceTypeEnum
import JAVA com.tradevan.gateway.einv.msg.v31.UtilBody.RoleDescriptionType
import JAVA com.tradevan.gateway.einv.msg.v31.UtilBody.TaxTypeEnum
import JAVA com.tradevan.gateway.client.einv.parse.ParserException
import JAVA java.io.Serializable
import JAVA java.io.BufferedReader
import JAVA java.io.File
import JAVA java.io.FileReader
import JAVA java.io.PrintStream
import JAVA java.io.BufferedReader
import JAVA java.io.File
import JAVA java.io.FileReader
import JAVA java.io.PrintStream
import JAVA java.lang.reflect.Method
import JAVA java.util.ArrayList
import JAVA java.util.Date
import JAVA java.util.List
import JAVA java.math.BigDecimal
DATABASE ds
Main
DEFINE j_a0101 com.tradevan.gateway.einv.msg.v31.A0101;
DEFINE j_main MainType;
DEFINE j_seller RoleDescriptionType;
DEFINE j_buyer RoleDescriptionType;
DEFINE j_detail DetailsType;
DEFINE j_item ProductItem;
DEFINE j_dec java.math.BigDecimal;
DEFINE j_amount AmountType;
DEFINE j_parser ParserHelper;
DEFINE outXML string;
DEFINE b01,b02, b03 Long;
Define d Date
let d = today
call A0101.create() RETURNING j_a0101;
call MainType.create() RETURNING j_main;
call j_main.setInvoiceNumber("0123456789");
call j_main.setInvoiceDate( '20170201' );
call j_main.setInvoiceTime('08:30:22' );
call RoleDescriptionType.create() RETURNING j_seller;
call RoleDescriptionType.create() RETURNING j_buyer;
call j_seller.setIdentifier("0123456789");
call j_seller.setName("012345678901234567890123456789012345678901234567890123456789");
call j_seller.setAddress("0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789");
call j_seller.setPersonInCharge("012345678901");
call j_seller.setTelephoneNumber("012345678901234");
call j_seller.setFacsimileNumber("012345678901234");
call j_seller.setEmailAddress("123@123.123.123");
call j_seller.setCustomerNumber("01234566789789456123");
call j_seller.setRoleRemark("20 20 20 20 202 0212");
call j_main.setSeller(j_seller);
call j_buyer.setIdentifier("0123456789");
call j_buyer.setName("012345678901234567890123456789012345678901234567890123456789");
call j_buyer.setAddress("0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789");
call j_buyer.setPersonInCharge("012345678901");
call j_buyer.setTelephoneNumber("012345678901234");
call j_buyer.setFacsimileNumber("012345678901234");
call j_buyer.setEmailAddress("123@123.123.123");
call j_buyer.setCustomerNumber("01234566789789456123");
call j_buyer.setRoleRemark("20 20 20 20 202 0212");
call j_main.setBuyer(j_buyer);
call j_main.setCheckNumber("1023456789");
call j_main.setBuyerRemark(BuyerRemarkEnum.DeductableExpense);
call j_main.setMainRemark("mainremark");
call j_main.setCustomsClearanceMark(CustomsClearanceMarkEnum.NotViaCustoms);
call j_main.setTaxCenter("taxcenter");
call j_main.setPermitDate('20170214');
call j_main.setPermitWord("permitword");
call j_main.setPermitNumber("permitnumber");
call j_main.setCategory("ca");
call j_main.setRelateNumber("relatenumber");
call j_main.setInvoiceType(InvoiceTypeEnum.ComputerType);
call j_main.setGroupMark("1");
call j_main.setDonateMark(DonateMarkEnum.Donated);
call j_main.setAttachment("attachment");
call j_a0101.setMain(j_main);
call DetailsType.create() returning j_detail;
call ProductItem.create() returning j_item;
call j_item.setDescription("description2");
call j_item.setQuantity( "2.0" );
call j_item.setUnit("unit");
call j_item.setUnitPrice("6.5");
call j_item.setAmount("5.2");
call j_item.setSequenceNumber("223");
call j_item.setRemark("remark");
call j_item.setRelateNumber("relatenumber");
call j_detail.addProductItem(j_item);
call ProductItem.create() returning j_item;
call j_item.setDescription("description3");
call j_item.setQuantity( "3.0" );
call j_item.setUnit("unit");
call j_item.setUnitPrice("6.5");
call j_item.setAmount("5.3");
call j_item.setSequenceNumber("333");
call j_item.setRemark("remark");
call j_item.setRelateNumber("relatenumber");
call j_detail.addProductItem(j_item);
call j_a0101.setDetails(j_detail);
call AmountType.create() returning j_amount;
call j_amount.setTaxType(TaxTypeEnum.HybridTaxType);
call j_amount.setTaxRate("789.215")
call j_amount.setOriginalCurrencyAmount("123456.852");
call j_amount.setExchangeRate("0123456789.22");
call j_amount.setCurrency(CurrencyCodeEnum.AED);
call j_a0101.setAmount(j_amount);
call ParserHelper.create() returning j_parser ;
call j_parser.marshalToXML(j_a0101) returning outXML ;
#我只顥示在console .沒存成檔這個可以自己作
DISPLAY outXML
end Main
#################################################
#使用後產出的XML
#################################################
<?xml version="1.0" encoding="UTF-8"?>
<Invoice xmlns="urn:GEINV:eInvoiceMessage:A0101:3.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:GEINV:eInvoiceMessage:A0101: 3.1 A0101.xsd">
<Main>
<InvoiceNumber>0123456789</InvoiceNumber>
<InvoiceDate>20170201</InvoiceDate>
<InvoiceTime>08:30:22</InvoiceTime>
<Seller>
<Identifier>0123456789</Identifier>
<Name>012345678901234567890123456789012345678901234567890123456789</Name>
<Address>0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789</Address>
<PersonInCharge>012345678901</PersonInCharge>
<TelephoneNumber>012345678901234</TelephoneNumber>
<FacsimileNumber>012345678901234</FacsimileNumber>
<EmailAddress>123@123.123.123</EmailAddress>
<CustomerNumber>01234566789789456123</CustomerNumber>
<RoleRemark>20 20 20 20 202 0212</RoleRemark>
</Seller>
<Buyer>
<Identifier>0123456789</Identifier>
<Name>012345678901234567890123456789012345678901234567890123456789</Name>
<Address>0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789</Address>
<PersonInCharge>012345678901</PersonInCharge>
<TelephoneNumber>012345678901234</TelephoneNumber>
<FacsimileNumber>012345678901234</FacsimileNumber>
<EmailAddress>123@123.123.123</EmailAddress>
<CustomerNumber>01234566789789456123</CustomerNumber>
<RoleRemark>20 20 20 20 202 0212</RoleRemark>
</Buyer>
<CheckNumber>1023456789</CheckNumber>
<BuyerRemark>1</BuyerRemark>
<MainRemark>mainremark</MainRemark>
<CustomsClearanceMark>1</CustomsClearanceMark>
<TaxCenter>taxcenter</TaxCenter>
<PermitDate>20170214</PermitDate>
<PermitWord>permitword</PermitWord>
<PermitNumber>permitnumber</PermitNumber>
<Category>ca</Category>
<RelateNumber>relatenumber</RelateNumber>
<InvoiceType>05</InvoiceType>
<GroupMark>1</GroupMark>
<DonateMark>1</DonateMark>
<Attachment>attachment</Attachment>
</Main>
<Details>
<ProductItem>
<Description>description2</Description>
<Quantity>2.0</Quantity>
<Unit>unit</Unit>
<UnitPrice>6.5</UnitPrice>
<Amount>5.2</Amount>
<SequenceNumber>223</SequenceNumber>
<Remark>remark</Remark>
<RelateNumber>relatenumber</RelateNumber>
</ProductItem>
<ProductItem>
<Description>description3</Description>
<Quantity>3.0</Quantity>
<Unit>unit</Unit>
<UnitPrice>6.5</UnitPrice>
<Amount>5.3</Amount>
<SequenceNumber>333</SequenceNumber>
<Remark>remark</Remark>
<RelateNumber>relatenumber</RelateNumber>
</ProductItem>
</Details>
<Amount>
<TaxType>9</TaxType>
<TaxRate>789.215</TaxRate>
<TaxAmount>123321</TaxAmount>
<TotalAmount>99999</TotalAmount>
<DiscountAmount>3019203</DiscountAmount>
<OriginalCurrencyAmount>123456.852</OriginalCurrencyAmount>
<ExchangeRate>123456789.22</ExchangeRate>
<Currency>AED</Currency>
</Amount>
</Invoice>
2017年2月13日 星期一
2016年7月11日 星期一
EXCEL 連動式下拉選單設計 TIPTOP
1. 如何準備機種與料號資料表
for ORACLE . 是用以下的指令來產生HTML TABLE 的資料.方便COPY /PAST 到EXCEL.
1.1執行以下指令.
select '<tr><td>' || imaud01 || '</td><td>'|| pono|| '</td></tr>' from
(
select imaud01, listagg( ima01,'</td><td>') WITHIN GROUP (ORDER BY imaud01) "PONO" from ima_file where ( ( ima01 like '01%' and substr(ima01,12,1) <>'0' ) or ima01 like '0T%' ) and ima06 between '11' and '14'
group by imaud01
)
1.2 並把編輯資料存成 XXXX.HTML 檔, 檔案開頭請加上
<html><body><table border="1">
檔尾請加上
</table></body></html>
2. 使用BROWSER 開啟 XXXX.HTML
儲存格內允許:清單
來源: =工作表1!$A:$A
7.4 來源: ==INDIRECT(SUBSTITUTE(A1,"-","_",1))
for ORACLE . 是用以下的指令來產生HTML TABLE 的資料.方便COPY /PAST 到EXCEL.
1.1執行以下指令.
select '<tr><td>' || imaud01 || '</td><td>'|| pono|| '</td></tr>' from
(
select imaud01, listagg( ima01,'</td><td>') WITHIN GROUP (ORDER BY imaud01) "PONO" from ima_file where ( ( ima01 like '01%' and substr(ima01,12,1) <>'0' ) or ima01 like '0T%' ) and ima06 between '11' and '14'
group by imaud01
)
1.2 並把編輯資料存成 XXXX.HTML 檔, 檔案開頭請加上
<html><body><table border="1">
檔尾請加上
</table></body></html>
2. 使用BROWSER 開啟 XXXX.HTML
3.在BROWSER 中使用全選/COPY 的方式將資料貼到EXCEL 工作表中.
4.在sheet2 中的A1 下, 選 資料>資料工具>資料驗證儲存格內允許:清單
來源: =工作表1!$A:$A
5.建立機種名稱
5.1在sheet1 中全部(ctrl+A)
5.2 公式>從選取範圍建立
5.3 只勾選最左欄
6.1 excel 會自動建立好所有機種名稱
7. 再次建資料驗證
7.1 點選sheet2 B1 欄.
7.2 資料>資料工具>資料驗證
7.3 儲存格內允許:清單7.4 來源: ==INDIRECT(SUBSTITUTE(A1,"-","_",1))
OK
這樣B1 的下拉就會因為A1不同而有所不同.
step 1,2,3 可以自己寫成WEB PAGE 後再由EXCEL 中的 資料>新查詢>其他來源.
還沒有空去TEST.
ORACLE 11 G 以上新功能. 把列中的欄文字, 加成一欄.
select rvv31 , listagg( rvv36,',') WITHIN GROUP (ORDER BY rvv31) "PONO" , sum(rvv17) 六月庫入數量 from gwt.rvv_file, gwt.rvu_file where rvv01=rvu01 and rvuconf ='Y' and
rvu03 >= to_date('2016/06/01','yyyy/MM/dd') and rvu03 < to_date('2016/07/01','yyyy/MM/dd')
group by rvv31
機種料號清單.
select '<tr><td>' || imaud01 || '</td><td>'|| pono|| '</td></tr>' from
(
select imaud01, listagg( ima01,'</td><td>') WITHIN GROUP (ORDER BY imaud01) "PONO" from ima_file where ( ( ima01 like '01%' and substr(ima01,12,1) <>'0' ) or ima01 like '0T%' ) and ima06 between '11' and '14'
group by imaud01
)
這東西所產生出來的資料可以加工後放到EXCEL 中. 並配合 indirect() 的東西來作.....
rvu03 >= to_date('2016/06/01','yyyy/MM/dd') and rvu03 < to_date('2016/07/01','yyyy/MM/dd')
group by rvv31
機種料號清單.
select '<tr><td>' || imaud01 || '</td><td>'|| pono|| '</td></tr>' from
(
select imaud01, listagg( ima01,'</td><td>') WITHIN GROUP (ORDER BY imaud01) "PONO" from ima_file where ( ( ima01 like '01%' and substr(ima01,12,1) <>'0' ) or ima01 like '0T%' ) and ima06 between '11' and '14'
group by imaud01
)
這東西所產生出來的資料可以加工後放到EXCEL 中. 並配合 indirect() 的東西來作.....
2016年6月21日 星期二
MSSOAPTookKit30 忘了他 請改用 MSOSop30.dll
MS SOAP Toolkit on Excel 64 bit 沒這種東西 BUT MS office 64 bit 內有附一個 msosoap30.dll 請注意多了一個O.
路徑在:
c:\program File\Common Files\microsoft shared\office14\MSSOAP30.DLL
所以也不太用需要再去裝 SOAP ToolKit 了.
原本 mssop30.dll amyo 是x86 拿去X64 的EXCEL 上會無法使用..他會錯在 set myobj = new MSSOSOAPLib30.SoapClient30 這行. 所以在 excel vba 編輯IDE 中. 要去工具/設定引用項目/瀏覽 去找你自己OFFICE 的路徑.
路徑在:
c:\program File\Common Files\microsoft shared\office14\MSSOAP30.DLL
所以也不太用需要再去裝 SOAP ToolKit 了.
原本 mssop30.dll amyo 是x86 拿去X64 的EXCEL 上會無法使用..他會錯在 set myobj = new MSSOSOAPLib30.SoapClient30 這行. 所以在 excel vba 編輯IDE 中. 要去工具/設定引用項目/瀏覽 去找你自己OFFICE 的路徑.
2016年6月3日 星期五
TIPTOP BOM on ORACLE store procedure
1.
2.
3.
4.
01:建立object TYPE
CREATE OR REPLACE TYPE TP_BOM_01 AS OBJECT
--這是展BOM要用的TYPE
--
( TOP_PART VARCHAR2(50),
LAST_PART VARCHAR2(50),
UNIT varchar2(20), --單位
Type_ varchar(20), --形態 P, M
BOM_PATH VARCHAR2(4000),
UsagePerSignal decimal(18,4),
BOM_LEVEL int,
Locate varchar2(4096)
);
GO
02:將01的object 建一個table type 好回傳
/*return table type*/
CREATE OR REPLACE TYPE TP_BOM_WHERE_USE_01_TABLE AS TABLE OF GWT.TP_BOM_WHERE_USE_01
GO
03: 單一主建展bom 到最底階.
/* BOM 單一料號*/
CREATE OR REPLACE FUNCTION GWT.FN_BOM_001(
p_date in date,
p_start_with in varchar2,
p_OrgPartno in varchar2 default null, --
p_level in number default 0,
p_usage in number default 1,
p_path in varchar2 default '',
p_bmb09 in varchar2 default '' )
return TP_BOM_01_Table PIPELINED is
out_rec TP_BOM_01 := TP_BOM_01(null,null,null,null,null,null,null, null);
rowCount int;
p_OrgPartno2 varchar2(40);
begin
p_OrgPartno2 := p_OrgPartno;
for c in ( select * from bmb_file, ima_file where ima01=bmb03 and bmb01 like p_start_with and
( bmb04 <= p_date OR bmb04 IS NULL) AND ( bmb05 > p_date OR bmb05 IS NULL) )
loop
--if (( p_level >1 and c.bmb19=3 ) or ( p_level<=1)) and (( p_level >1 and c.ima08='M' ) or ( p_level<=1)) then
if p_level <=0 then
p_OrgPartno2 := c.bmb01;
end if;
select count(*) into rowCount from bmb_file where bmb01=c.bmb03 and
( ( bmb04 <= p_date OR bmb04 IS NULL) AND ( bmb05 > p_date OR bmb05 IS NULL) );
if rowCount =0 then -- and c.bmb19=3 then
out_rec.TOP_PART := p_OrgPartno2;
out_rec.LAST_PART := c.bmb03;
out_rec.UNIT := c.bmb10;
out_rec.Type_ := c.ima08;
out_rec.BOM_PATH := p_path || '>>' || c.bmb03 || '(' || p_bmb09 || ')' ;
out_rec.UsagePerSignal := c.BMB06 * p_usage ;
out_rec.BOM_LEVEL := p_level;
out_rec.Locate := '';
PIPE ROW(out_rec);
end if;
if 1=1 then --c.bmb19=3 then
for c2 in ( SELECT * from TABLE(FN_BOM_001( p_date, c.bmb03, p_OrgPartno2, p_level+1, c.BMB06 * p_usage, p_path || '>>' || p_start_with || '(' || p_bmb09 || ')' ,c.bmb09 )) )
loop
out_rec.TOP_PART := c2.TOP_PART;
out_rec.LAST_PART := c2.LAST_PART;
out_rec.BOM_PATH := c2.BOM_PATH ;
out_rec.UNIT := c2.UNIT;
out_rec.Type_ := c2.Type_;
out_rec.UsagePerSignal := c2.UsagePerSignal;
out_rec.BOM_LEVEL := c2.BOM_LEVEL;
out_rec.Locate := c2.Locate;
PIPE ROW(out_rec);
end loop;
end if;
--end if;
end loop;
end;
GO
04:一個一個展太
/*全展*/
CREATE OR REPLACE FUNCTION GWT.FN_BOM_ALL
return TP_BOM_01_Table PIPELINED is
out_rec TP_BOM_01 := TP_BOM_01(null,null,null,null,null,null,null, null);
rowCount int;
p_OrgPartno2 varchar2(40);
begin
for c in ( select * from ima_file where ( ima01 like '01%' or ima01 like '0T%' or ima01 like '13%' or ima01 like '1013%' ) and ((ima01 like '01%' and substr(ima01,12,1)<>'0' ) or (ima01 not like '01%' )) )
loop
-- SELECT * from TABLE(FN_BOM_001( to_date('16/05/18','yy/MM/dd'), '01DS102200GT', null, 0, 1, '' ))
for c2 in ( SELECT * from TABLE(FN_BOM_001( sysdate , c.ima01,null, 0,1,'', '')) )
loop
out_rec.TOP_PART := c2.TOP_PART;
out_rec.LAST_PART := c2.LAST_PART;
out_rec.BOM_PATH := c2.BOM_PATH ;
out_rec.UNIT := c2.UNIT;
out_rec.Type_ := c2.Type_;
out_rec.UsagePerSignal := c2.UsagePerSignal;
out_rec.BOM_LEVEL := c2.BOM_LEVEL;
out_rec.Locate := c2.Locate;
PIPE ROW(out_rec);
end loop;
end loop;
end;
GO
使用方式
01: 單一BOM 查詢
SELECT * from TABLE(FN_BOM_001( sysdate , 'MastPart',null, 0,1,'', ''))
02: 全部料號BOM 查詢
SELECT * FROM TABLE(FN_BOM_ALL())
03: 將查詣的BOM 轉成TABLE 存放
CREATE TABLE bom_20160606 AS
SELECT * FROM TABLE(FN_BOM_ALL())
2.
3.
4.
01:建立object TYPE
CREATE OR REPLACE TYPE TP_BOM_01 AS OBJECT
--這是展BOM要用的TYPE
--
( TOP_PART VARCHAR2(50),
LAST_PART VARCHAR2(50),
UNIT varchar2(20), --單位
Type_ varchar(20), --形態 P, M
BOM_PATH VARCHAR2(4000),
UsagePerSignal decimal(18,4),
BOM_LEVEL int,
Locate varchar2(4096)
);
GO
02:將01的object 建一個table type 好回傳
/*return table type*/
CREATE OR REPLACE TYPE TP_BOM_WHERE_USE_01_TABLE AS TABLE OF GWT.TP_BOM_WHERE_USE_01
GO
03: 單一主建展bom 到最底階.
/* BOM 單一料號*/
CREATE OR REPLACE FUNCTION GWT.FN_BOM_001(
p_date in date,
p_start_with in varchar2,
p_OrgPartno in varchar2 default null, --
p_level in number default 0,
p_usage in number default 1,
p_path in varchar2 default '',
p_bmb09 in varchar2 default '' )
return TP_BOM_01_Table PIPELINED is
out_rec TP_BOM_01 := TP_BOM_01(null,null,null,null,null,null,null, null);
rowCount int;
p_OrgPartno2 varchar2(40);
begin
p_OrgPartno2 := p_OrgPartno;
for c in ( select * from bmb_file, ima_file where ima01=bmb03 and bmb01 like p_start_with and
( bmb04 <= p_date OR bmb04 IS NULL) AND ( bmb05 > p_date OR bmb05 IS NULL) )
loop
--if (( p_level >1 and c.bmb19=3 ) or ( p_level<=1)) and (( p_level >1 and c.ima08='M' ) or ( p_level<=1)) then
if p_level <=0 then
p_OrgPartno2 := c.bmb01;
end if;
select count(*) into rowCount from bmb_file where bmb01=c.bmb03 and
( ( bmb04 <= p_date OR bmb04 IS NULL) AND ( bmb05 > p_date OR bmb05 IS NULL) );
if rowCount =0 then -- and c.bmb19=3 then
out_rec.TOP_PART := p_OrgPartno2;
out_rec.LAST_PART := c.bmb03;
out_rec.UNIT := c.bmb10;
out_rec.Type_ := c.ima08;
out_rec.BOM_PATH := p_path || '>>' || c.bmb03 || '(' || p_bmb09 || ')' ;
out_rec.UsagePerSignal := c.BMB06 * p_usage ;
out_rec.BOM_LEVEL := p_level;
out_rec.Locate := '';
PIPE ROW(out_rec);
end if;
if 1=1 then --c.bmb19=3 then
for c2 in ( SELECT * from TABLE(FN_BOM_001( p_date, c.bmb03, p_OrgPartno2, p_level+1, c.BMB06 * p_usage, p_path || '>>' || p_start_with || '(' || p_bmb09 || ')' ,c.bmb09 )) )
loop
out_rec.TOP_PART := c2.TOP_PART;
out_rec.LAST_PART := c2.LAST_PART;
out_rec.BOM_PATH := c2.BOM_PATH ;
out_rec.UNIT := c2.UNIT;
out_rec.Type_ := c2.Type_;
out_rec.UsagePerSignal := c2.UsagePerSignal;
out_rec.BOM_LEVEL := c2.BOM_LEVEL;
out_rec.Locate := c2.Locate;
PIPE ROW(out_rec);
end loop;
end if;
--end if;
end loop;
end;
GO
04:一個一個展太
/*全展*/
CREATE OR REPLACE FUNCTION GWT.FN_BOM_ALL
return TP_BOM_01_Table PIPELINED is
out_rec TP_BOM_01 := TP_BOM_01(null,null,null,null,null,null,null, null);
rowCount int;
p_OrgPartno2 varchar2(40);
begin
for c in ( select * from ima_file where ( ima01 like '01%' or ima01 like '0T%' or ima01 like '13%' or ima01 like '1013%' ) and ((ima01 like '01%' and substr(ima01,12,1)<>'0' ) or (ima01 not like '01%' )) )
loop
-- SELECT * from TABLE(FN_BOM_001( to_date('16/05/18','yy/MM/dd'), '01DS102200GT', null, 0, 1, '' ))
for c2 in ( SELECT * from TABLE(FN_BOM_001( sysdate , c.ima01,null, 0,1,'', '')) )
loop
out_rec.TOP_PART := c2.TOP_PART;
out_rec.LAST_PART := c2.LAST_PART;
out_rec.BOM_PATH := c2.BOM_PATH ;
out_rec.UNIT := c2.UNIT;
out_rec.Type_ := c2.Type_;
out_rec.UsagePerSignal := c2.UsagePerSignal;
out_rec.BOM_LEVEL := c2.BOM_LEVEL;
out_rec.Locate := c2.Locate;
PIPE ROW(out_rec);
end loop;
end loop;
end;
GO
使用方式
01: 單一BOM 查詢
SELECT * from TABLE(FN_BOM_001( sysdate , 'MastPart',null, 0,1,'', ''))
02: 全部料號BOM 查詢
SELECT * FROM TABLE(FN_BOM_ALL())
03: 將查詣的BOM 轉成TABLE 存放
CREATE TABLE bom_20160606 AS
SELECT * FROM TABLE(FN_BOM_ALL())
2016年4月7日 星期四
Oracle Grant All Permission to User
BEGIN
FOR t IN (select * from dba_tables where owner!='GGSTEST'
AND owner not like '%SYS%'
AND owner not like '%ORDDATA%'
AND owner not like '%ADMIN%'
AND owner not like '%APEX%'
AND owner not like '%XDB%'
AND owner not like '%FLOW_FILES%'
AND owner not like '%OE%' AND iot_type IS NULL)
LOOP
EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES ON '|| t.owner ||'.' || t.table_name ||' TO TIPTOPGP';
END LOOP;
END;
FOR t IN (select * from dba_tables where owner!='GGSTEST'
AND owner not like '%SYS%'
AND owner not like '%ORDDATA%'
AND owner not like '%ADMIN%'
AND owner not like '%APEX%'
AND owner not like '%XDB%'
AND owner not like '%FLOW_FILES%'
AND owner not like '%OE%' AND iot_type IS NULL)
LOOP
EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES ON '|| t.owner ||'.' || t.table_name ||' TO TIPTOPGP';
END LOOP;
END;
2016年2月13日 星期六
SQL Server sp_addlinkedserver 之 insert,update, delete 方法
SQL Server 的 sp_addlinkedserver 很好用. 可是在連上去後要更新遠端主機上的資料可就不能用傳統的方式來update.
以下是 MSDN 上找的指定
Update :
update openquery(linked1, 'select ssn from testlinked where ssn=2')
set ssn=ssn + 1
Insert :
insert openquery(linked1, 'select ssn from testlinked where 1=0') values (1000)
Insert From..
insert openquery(linked1, 'select ssn from testlinked where 1=0')
select myssn from mytable
delete openquery(linked1, 'select ssn from testlinked where ssn>100')
以下是 MSDN 上找的指定
Update :
update openquery(linked1, 'select ssn from testlinked where ssn=2')
set ssn=ssn + 1
Insert :
insert openquery(linked1, 'select ssn from testlinked where 1=0') values (1000)
Insert From..
insert openquery(linked1, 'select ssn from testlinked where 1=0')
select myssn from mytable
delete openquery(linked1, 'select ssn from testlinked where ssn>100')
訂閱:
文章 (Atom)






