订单导入的接口开发

tzhsuccess · 发表于 2006-6-6 09:22

最初由 ghita 发布
[B]

下面是一段我在導入時用到的一些sql
declare
cursor cu is
select distinct
      :control.order_source_id                            ORDER_SOURCE_ID
   ,  to_char(sysdate,'yyyymmddhh24mi')                   ORIG_SYS_DOCUMENT_REF
   ,  arameter.org_id                                     ORG_ID
   ,  to_date(replace(aic.issue,'/','-'),:control.date_fmt)  ORDERED_DATE
   ,  :control.order_type_id                               ORDER_TYPE_ID
   ,  aic.price_list_id                                     PRICE_LIST_ID
   ,  aic.cur                                              TRANSACTIONAL_CURR_CODE
   , 'No Sales Credit'                                     SALESREP
   ,  aic.po_no                                           CUSTOMER_PO_NUMBER
   ,  aic.customer_id                                     SOLD_TO_ORG_ID
   ,  aic.site_use_id                                     SHIP_TO_ORG_ID
   ,  aic.customer_id                                     CUSTOMER_ID
   ,  arameter.USER_ID                                  CREATED_BY
   ,  arameter.USER_ID                                  LAST_UPDATED_BY
   ,  SYSDATE                                              CREATION_DATE
   ,  SYSDATE                                              LAST_UPDATE_DATE
from
      zexsa_cust_om_data_temp aic
where aic.batch_seq=arameter.batch_seq
   and err_msg='0'
   ;

REQ_ID NUMBER;
r number;
BEGIN
if :control.order_type_id is null or :control.order_source_id is null
   then
      fnd_message.debug('You must choose order type and order source');

else

      for lr in cu loop
r:=cu%rowcount;
insert into OE_HEADERS_IFACE_ALL
   (
   ORDER_SOURCE_ID
   , ORIG_SYS_DOCUMENT_REF
   , ORG_ID
   , ORDERED_DATE
   , ORDER_TYPE_ID
   , PRICE_LIST_ID
   , TRANSACTIONAL_CURR_CODE
   , SALESREP
   , CUSTOMER_PO_NUMBER
   , SOLD_TO_ORG_ID
   , SHIP_TO_ORG_ID
   , CUSTOMER_ID
   , CREATED_BY
   , LAST_UPDATED_BY
   , CREATION_DATE
   , LAST_UPDATE_DATE
   )
values
( lr.ORDER_SOURCE_ID
   , lr.ORIG_SYS_DOCUMENT_REF||r
   , lr.ORG_ID
   , lr.ORDERED_DATE
   , lr.ORDER_TYPE_ID
   , lr.PRICE_LIST_ID
   , lr.TRANSACTIONAL_CURR_CODE
   , lr.SALESREP
   , lr.CUSTOMER_PO_NUMBER
   , lr.SOLD_TO_ORG_ID
   , lr.SHIP_TO_ORG_ID
   , lr.CUSTOMER_ID
   , lr.CREATED_BY
   , lr.LAST_UPDATED_BY
   , lr.CREATION_DATE
   , lr.LAST_UPDATE_DATE
   )
   ;

insert into  OE_lineS_IFACE_ALL
   ( ORDER_SOURCE_ID
   , ORIG_SYS_DOCUMENT_REF
   , ORIG_SYS_LINE_REF
   , ORIG_SYS_SHIPMENT_REF
   , ORG_ID
   , customer_po_number
   , LINE_NUMBER
   , INVENTORY_ITEM_ID
   , ORDERED_QUANTITY
   , ORDER_QUANTITY_UOM
   , schedule_ship_date
   , CREATED_BY
   , LAST_UPDATED_BY
   , CREATION_DATE
   , LAST_UPDATE_DATE
)
(
select
   :control.order_source_id
   , lr.ORIG_SYS_DOCUMENT_REF
   , rownum
   , rownum
   , arameter.org_id
   , aic.po_no
   , rownum
   , aic.item_id
   , aic.qty
   , null
   , to_date(replace(aic.del_date,'/','-'),:control.date_fmt)
   , arameter.USER_ID
   , arameter.USER_ID
   , SYSDATE
   , SYSDATE
from
      zexsa_cust_om_data_temp aic
where aic.batch_seq=arameter.batch_seq
   and err_msg='0'
                  and aic.customer_id =lr.CUSTOMER_ID
                  and aic.site_use_id =lr.SHIP_TO_ORG_ID
                  and nvl(aic.po_no,'a')=nvl(lr.CUSTOMER_PO_NUMBER,'a')
                  and to_date(replace(aic.issue,'/','-'),:control.date_fmt)= lr.ORDERED_DATE

   )       ;
      end loop;

forms_ddl('commit');

--FND_REQUEST.SET_MODE(true).
/*

      REQ_ID:=FND_REQUEST.SUBMIT_REQUEST
('ONT',
'OEOIMP'
,null,
to_char(SYSDATE,'DD-MON-YY HH24:MI:SS'),
FALSE,
'',
'ZEXSA_WEB_ORDER',
'',
'N',
'1',
'4',
null,--to_number(null),
null,--to_number(null),
null,--to_number(null),
'Y',

--  , zexsa       , , N ,  1  , 4,  ,  , , Y,
-- N
   'N', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '');

*/
fnd_message.debug('Your data transfer into interface OK.');

delete from zexsa_cust_om_data_temp aic
where aic.batch_seq=arameter.batch_seq
   and err_msg='0' ;
      forms_ddl('commit');

     go_block('ZEXSA_CUST_OM_DATA_TEMP');
      execute_query;



end if;

end; [/B]