|
|
楼主 |
发表于 2008/8/13 14:34:54
|
显示全部楼层
已經解決了
下面是將數據導入接口表的程式。附件為上傳數據的欄位（對就的表單為： lcs_lc_order_list）。希望這些對大家有用處。（裡面單價涉及公司機密，所以都設為了0）
declare
cursor a is
select distinct
1001 ORDER_SOURCE_ID
, a.order_number
, 113 org_id---
, to_date(a.ordered_date,'yyyy/mm/dd') ordered_date
, a.order_type order_type_id
, b.primary_salesrep_id
, b.resource_name
, b.payment_term_id
, b.ship_via
, b.freight_term
, b.fob_point
, b.customer_id
, b.site_use_id
, b.bill_to_site_use_id
, null attribute100
, a.orig_cust_order_number
, a.group_prod
, a.container_type
, a.conbination_PO
, b.attribute1
, b.attribute2
, b.attribute3
, b.attribute4
, b.attribute5
, b.attribute8
, attribute9
, a.comn_remark||';'||a.sp_order attribute14
, b.attribute10
, 1948 CREATED_BY---
, sysdate CREATION_DATE
, 1948 LAST_UPDATED_BY---
, sysdate LAST_UPDATE_DATE
, null LAST_UPDATE_LOGIN
, 'INSERT' OPERATION_CODE
, 'Y' BOOKED_FLAG
, a.cust_code
from lcs_lc_order_list a,lcs_om_cust_v b
where a.cust_code = b.customer_number(+)
and a.orig_cust_code = b.address4(+)
and a.destination_port = b.location(+)
and a.dischange_port = b.bill_to(+);
cursor b (v_cust_code varchar2,v_order_number varchar2) is
select
1001 ORDER_SOURCE_ID
, a.order_number
, 113 org_id---
, decode(a.order_type,1036,1035,1031) line_type_id---
, 'STANDARD' item_type_code
, c.inventory_item_id
, 'INTERNAL' source_type_code
, to_date(a.request_date,'yyyy/mm/dd') request_date
, to_date(a.ship_date,'yyyy/mm/dd') ship_date
, a.ordered_qty
, 'EA' order_quantiry_uom
, b.customer_id
, a.ship_org
, b.site_use_id
, b.bill_to_site_use_id
, 6071 price_list_id
, sysdate pricing_date
, a.sie_up
, a.sie_up UNIT_SELLING_PRICE
, 'N' calculate_price_flag
, ship_factory attribute1
, null attribute2
, null attribute3
, null attribute4
, 'No' attribute5
, null attribute6
, 'No' attribute7
, null attribute8
, null attribute9
, a.shipped_qty attribute10
, a.lc_up attribute11
, 1948 CREATED_BY---
, sysdate CREATION_DATE
, 1948 LAST_UPDATED_BY---
, sysdate LAST_UPDATE_DATE
, null LAST_UPDATE_LOGIN
, 'INSERT' OPERATION_CODE
from lcs_lc_order_list a,lcs_om_cust_v b,mtl_system_items_b c
where a.cust_code = b.customer_number(+)
and a.orig_cust_code = b.address4(+)
and a.destination_port = b.location(+)
and a.dischange_port = b.bill_to(+)
and a.lc_fg_code = c.attribute14(+)
and a.ship_org = c.organization_id(+)
and a.cust_code = v_cust_code
and a.order_number= v_order_number;
p_ORIG_SYS_DOCUMENT_REF varchar2(30);
p_ORIG_SYS_LINE_REF varchar2(30);
p_line_number number;
begin
p_ORIG_SYS_DOCUMENT_REF := '1';
p_ORIG_SYS_LINE_REF := '1';
for i in a loop
p_line_number := 0;
p_ORIG_SYS_DOCUMENT_REF := p_ORIG_SYS_DOCUMENT_REF + '1';
/* p_ORIG_SYS_LINE_REF := p_ORIG_SYS_LINE_REF + '1';*/
INSERT INTO oe_headers_iface_all
(
ORDER_SOURCE_ID
, ORIG_SYS_DOCUMENT_REF
, ORG_ID
, ORDERED_DATE
, ORDER_TYPE_ID
, PRICE_LIST_ID
, SALESREP_ID
, PAYMENT_TERM_ID
, SHIPPING_METHOD_CODE
, FREIGHT_TERMS_CODE
, FOB_POINT_CODE
, CUSTOMER_PO_NUMBER
, SOLD_TO_ORG_ID
, SHIP_TO_ORG_ID
, INVOICE_TO_ORG_ID
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE14
, ATTRIBUTE15
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, OPERATION_CODE
, BOOKED_FLAG
)
values
(
i.ORDER_SOURCE_ID
, p_ORIG_SYS_DOCUMENT_REF
, i.org_id
, i.ordered_date
, i.order_type_id
, 6071
, i.primary_salesrep_id
, i.payment_term_id
, i.ship_via
, i.freight_term
, i.fob_point
, i.order_number
, i.customer_id
, i.site_use_id
, i.bill_to_site_use_id
, i.orig_cust_order_number
, i.attribute100
, i.group_prod
, i.container_type
, i.conbination_PO
, i.attribute1
, i.attribute2
, i.attribute3
, i.attribute4
, i.attribute5
, i.attribute8
, i.attribute9
, i.attribute14
, i.attribute10
, i.CREATED_BY
, i.CREATION_DATE
, i.LAST_UPDATED_BY
, i.LAST_UPDATE_DATE
, i.LAST_UPDATE_LOGIN
, i.OPERATION_CODE
, i.BOOKED_FLAG
);
for k in b(i.cust_code,i.order_number) loop
p_line_number := p_line_number +1;
p_ORIG_SYS_LINE_REF := p_ORIG_SYS_LINE_REF + '1';
/* p_ORIG_SYS_DOCUMENT_REF := p_ORIG_SYS_DOCUMENT_REF + '1';*/
INSERT INTO oe_lines_iface_all
(
ORDER_SOURCE_ID
, ORIG_SYS_DOCUMENT_REF
, ORIG_SYS_LINE_REF
, ORG_ID
, CUSTOMER_PO_NUMBER---新增欄位訂單號
, LINE_NUMBER
, SHIPMENT_NUMBER
, LINE_TYPE_ID
, ITEM_TYPE_CODE
, INVENTORY_ITEM_ID
, SOURCE_TYPE_CODE
, REQUEST_DATE
, PROMISE_DATE
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, SOLD_FROM_ORG_ID
, SOLD_TO_ORG_ID
, SHIP_FROM_ORG_ID
, SHIP_TO_ORG_ID
, INVOICE_TO_ORG_ID
, PRICE_LIST_ID
, PRICING_DATE
, UNIT_LIST_PRICE
, UNIT_SELLING_PRICE
, CALCULATE_PRICE_FLAG
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11--ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,
, ATTRIBUTE13---新增ATTRIBUTE13欄位
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, OPERATION_CODE
)
values
(
k.ORDER_SOURCE_ID
,p_ORIG_SYS_DOCUMENT_REF
,p_ORIG_SYS_LINE_REF
,k.org_id
,k.order_number
,p_line_number
,null
,k.line_type_id
,k.item_type_code
,k.inventory_item_id
,k.source_type_code
,k.request_date
,k.ship_date
,k.ordered_qty
,k.order_quantiry_uom
,k.org_id
,k.customer_id
,k.ship_org
,k.site_use_id
,k.bill_to_site_use_id
,k.price_list_id
,k.pricing_date
,k.sie_up
,k.UNIT_SELLING_PRICE
,k.calculate_price_flag
,k.attribute1
,k.attribute2
,k.attribute3
,k.attribute4
,k.attribute5
,k.attribute6
,k.attribute7
,k.attribute8
,k.attribute9
,k.attribute10
,k.attribute11
,'0'
,k.CREATED_BY
,k.CREATION_DATE
,k.LAST_UPDATED_BY
,k.LAST_UPDATE_DATE
,k.LAST_UPDATE_LOGIN
,k.OPERATION_CODE
);
end loop;
end loop;
commit;
end;
視圖lcs_om_cust_v的代碼：
select b.customer_id,c.cust_acct_site_id,a.site_use_id,
--'I' INSERT_UPDATE_FLAG,
b.customer_name ORIG_SYSTEM_CUSTOMER,
-- b.customer_name,
b.CUSTOMER_NUMBER,
-- c.STATUS,
--b.CUSTOMER_TYPE,
--b.attribute2 cust_attr2,
-- b.attribute3 cust_attr3,
--b.CUSTOMER_NAME_PHONETIC,
g.territory_short_name,
E.ADDRESS1,E.ADDRESS2,E.ADDRESS3,E.ADDRESS4,--E.CITY,E.STATE,E.PROVINCE,E.COUNTY,E.POSTAL_CODE,E.COUNTRY,
-- null CUSTOMER_CATEGORY_CODE,d.identifying_address_flag
d.status addr_status,a.SITE_USE_CODE,a.ship_via,
a.LOCATION,f.location bill_to,c.org_id,NVL(a.bill_to_site_use_id,0) bill_to_site_use_id
,A.PRIMARY_FLAG,a.attribute1,a.attribute2,a.attribute3,a.attribute4,a.attribute5,
a.attribute6,a.attribute7,a.attribute8,a.attribute9,a.attribute10--,a.attribute11,a.attribute12,a.attribute13,a.attribute14,a.attribute15
,a.primary_salesrep_id,H.resource_name,a.payment_term_id,I.NAME TERM_NAME,J.NAME PRICE_LIST_NAME,K.SHIP_METHOD_MEANING,A.fob_point,A.freight_term
from HZ_CUST_SITE_USES_all a,
AR_CUSTOMERS_V b,
HZ_CUST_ACCT_SITES_all c,
HZ_PARTY_SITES D,
HZ_LOCATIONS E,
HZ_CUST_SITE_USES_all f,
FND_TERRITORIES_VL g,
JTF_RS_DEFRESOURCES_VL h,
RA_TERMS_VL i,
qp_list_headers j,
WSH_CARRIER_SERVICES K
where a.cust_acct_site_id = c.cust_acct_site_id
and b.customer_id = c.cust_account_id(+)
AND C.PARTY_SITE_ID = D.PARTY_SITE_ID(+)
AND D.LOCATION_ID = E.LOCATION_ID(+)
and a.bill_to_site_use_id = f.site_use_id(+)
and e.country=g.territory_code(+)
and a.primary_salesrep_id = H.resource_id(+)
and a.price_list_id = J.LIST_HEADER_ID(+)
and a.payment_term_id = I.TERM_ID(+)
and a.ship_via= K.SHIP_METHOD_CODE(+) |
|
|Archiver|小黑屋|手机版|壹佰网 ERP100
( 京ICP备19053597号-2 )
IP卡
狗仔卡
发表于 2008/8/2 19:30:45
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡
