CREATE OR REPLACE procedure SIEBEL.NR_PRODUCT2EC(v_productId in varchar2,P_STATUS OUT varchar2) as
--cur_user varchar2(100);
numb number;
begin
--return;
select count(*)
into numb
from CX_PRODUCT
where ROW_ID=v_productId;
if numb = 0 then
insert into CX_PRODUCT (
ROW_ID,
COMPANY_MODEL_IN,
COMPANY_MODEL_OUT,
OEM_MODEL,
OEM_REGION,
APPLICABLE_MODEL,
PRODUCT_ATTRIBUTE,
SALES_TYPE,
BRAND,
MT_PRICE_GROUP,
CHIP,
COLOUR,
OEM_OUTPUT,
OUTPUT,
STRUCTRUE,
CARTON_SIZE,
BOX_SIZE,
PLATE,
LABEL_SIEZE,
PACKING_NUMBER,
NET_WEIGHT,
CROSS_WEIGHT,
DISPLAY_MODEL,
START_DATA,
END_DATA,
CLASS,
SMALL_CLASS,
CENTER,
INFLUENCE,
NOTE,
DISCRIPTION,
VC_MODEL,
TONER_LINE,
SALE_GROUP,
ITEM_TYPE_NAME,
NINESTAR_SALES_TERRITORY,
PRINTER_NO,
INKCONTENT,
OEMINKCONTENT,
NINESTAR_INK_TYPE,
OEM_INK_TYPE,
PRODUCT_LINE_INK,
ITEM_LEVEL,
IDENTIFY_PATENT,
CARTON_MATERIAL,
STATUS,
SYS_TIME,
CREATED_BY,LAST_UPD_BY,
FIN_PRO_CODE, --EC产品目录字段
GG_PROD --EC产品目录字段
)
select
t.ROW_ID as ID,
t.NAME as COMPANY_MODEL_IN,
t.BAR_CODE_NUM as COMPANY_MODEL_OUT,
t.ALIAS_NAME as OEM_MODEL,
t.DIVN_CD as OEM_REGION,
t.REF_NUMBER_3 as APPLICABLE_MODEL,
t.SUB_TYPE_CD as PRODUCT_ATTRIBUTE,
t.PROFIT_RANK_CD as SALES_TYPE,
t.REF_NUMBER_4 as BRAND,
t.LEAD_TM as MT_PRICE_GROUP,
t1.CATALOG_NUM as CHIP,
t2.ATTRIB_39 as COLOUR,
t.X_ORIGINAL_LEVEL as OEM_OUTPUT,
t.X_COMPANY_LEVEL as OUTPUT,
t2.ATTRIB_52 as STRUCTRUE,
t.BODY_STYLE_CD as CARTON_SIZE,
t.DOORS_TYPE_CD as BOX_SIZE,
t.TRANSMISSION_CD as PLATE,
t.DRIVE_TRAIN_CD as LABEL_SIEZE,
t.CASE_PACK as PACKING_NUMBER,
t.UNIT_CONV_FACTOR as NET_WEIGHT,
t.ITEM_SIZE as CROSS_WEIGHT,
t.ENGINE_TYPE_CD as DISPLAY_MODEL,
t.EFF_START_DT as START_DATA,
t.EFF_END_DT as END_DATA,
t1.DEV_APPVL_BODY_CD as CLASS,
t1.FREQUENCY_CD as SMALL_CLASS,
t.PREF_CARRIER_CD as CENTER,
t.UOM_CD as INFLUENCE,
t3.COMMENTS as NOTE,
t.DESC_TEXT as DISCRIPTION,
t.MODEL as VC_MODEL,
t.PREF_SHIP_METH_CD as TONER_LINE,
t.VENDR_PART_NUM as SALE_GROUP,
t2.ATTRIB_01 as ITEM_TYPE_NAME,
t2.ATTRIB_02 as NINESTAR_SALES_TERRITORY,
t.REASON_TXT as PRINTER_NO,
t.PART_NUM as INKCONTENT,
t.PROD_ASSEMBLY_LVL as OEMINKCONTENT,
t.PROD_ATTRIB01_CD as NINESTAR_INK_TYPE,
t.TGT_CUST_TYPE_CD as OEM_INK_TYPE,
t2.ATTRIB_36 as PRODUCT_LINE_INK,
t2.ATTRIB_51 as ITEM_LEVEL,
t2.ATTRIB_37 as IDENTIFY_PATENT,
t2.ATTRIB_04 as CARTON_MATERIAL,
'N' as STATUS,
null,tcc.LAST_NAME,
tcu.Last_Name,
t2.ATTRIB_05 as FIN_PRO_CODE, --成品编码（标准） by 103023 20171109
t2.nr_attrib_61 as GG_PROD --G&G产品型号 by 103023 20171109
from S_PROD_INT t
left join S_PROD_INT_LSX t1 on(t1.PAR_ROW_ID=t.ROW_ID)
left join S_PROD_INT_X t2 on(t2.PAR_ROW_ID=t.ROW_ID)
left join S_PROD_INT_TNTX t3 on(t3.PAR_ROW_ID=t.ROW_ID)
left join S_CONTACT tcc on(tcc.ROW_ID=t.CREATED_BY)
left join S_CONTACT tcu on(tcu.ROW_ID=t.LAST_UPD_BY)
where t.ROW_ID=v_productId;
else
update CX_PRODUCT
set (
COMPANY_MODEL_IN,
COMPANY_MODEL_OUT,
OEM_MODEL,
OEM_REGION,
APPLICABLE_MODEL,
PRODUCT_ATTRIBUTE,
SALES_TYPE,
BRAND,
MT_PRICE_GROUP,
CHIP,
COLOUR,
OEM_OUTPUT,
OUTPUT,
STRUCTRUE,
CARTON_SIZE,
BOX_SIZE,
PLATE,
LABEL_SIEZE,
PACKING_NUMBER,
NET_WEIGHT,
CROSS_WEIGHT,
DISPLAY_MODEL,
START_DATA,
END_DATA,
CLASS,
SMALL_CLASS,
CENTER,
INFLUENCE,
NOTE,
DISCRIPTION,
VC_MODEL,
TONER_LINE,
SALE_GROUP,
ITEM_TYPE_NAME,
NINESTAR_SALES_TERRITORY,
PRINTER_NO,
INKCONTENT,
OEMINKCONTENT,
NINESTAR_INK_TYPE,
OEM_INK_TYPE,
PRODUCT_LINE_INK,
ITEM_LEVEL,
IDENTIFY_PATENT,
CARTON_MATERIAL,
STATUS,
SYS_TIME,
CREATED_BY,LAST_UPD_BY,LAST_UPD,
USTATUS,
FIN_PRO_CODE, -- by 103023 20171109
GG_PROD -- by 103023 20171109
) =(
select
t.NAME as COMPANY_MODEL_IN,
t.BAR_CODE_NUM as COMPANY_MODEL_OUT,
t.ALIAS_NAME as OEM_MODEL,
t.DIVN_CD as OEM_REGION,
t.REF_NUMBER_3 as APPLICABLE_MODEL,
t.SUB_TYPE_CD as PRODUCT_ATTRIBUTE,
t.PROFIT_RANK_CD as SALES_TYPE,
t.REF_NUMBER_4 as BRAND,
t.LEAD_TM as MT_PRICE_GROUP,
t1.CATALOG_NUM as CHIP,
t2.ATTRIB_39 as COLOUR,
t.X_ORIGINAL_LEVEL as OEM_OUTPUT,
t.X_COMPANY_LEVEL as OUTPUT,
t2.ATTRIB_52 as STRUCTRUE,
t.BODY_STYLE_CD as CARTON_SIZE,
t.DOORS_TYPE_CD as BOX_SIZE,
t.TRANSMISSION_CD as PLATE,
t.DRIVE_TRAIN_CD as LABEL_SIEZE,
t.CASE_PACK as PACKING_NUMBER,
t.UNIT_CONV_FACTOR as NET_WEIGHT,
t.ITEM_SIZE as CROSS_WEIGHT,
t.ENGINE_TYPE_CD as DISPLAY_MODEL,
t.EFF_START_DT as START_DATA,
t.EFF_END_DT as END_DATA,
t1.DEV_APPVL_BODY_CD as CLASS,
t1.FREQUENCY_CD as SMALL_CLASS,
t.PREF_CARRIER_CD as CENTER,
t.UOM_CD as INFLUENCE,
t3.COMMENTS as NOTE,
t.DESC_TEXT as DISCRIPTION,
t.MODEL as VC_MODEL,
t.PREF_SHIP_METH_CD as TONER_LINE,
t.VENDR_PART_NUM as SALE_GROUP,
t2.ATTRIB_01 as ITEM_TYPE_NAME,
t2.ATTRIB_02 as NINESTAR_SALES_TERRITORY,
t.REASON_TXT as PRINTER_NO,
t.PART_NUM as INKCONTENT,
t.PROD_ASSEMBLY_LVL as OEMINKCONTENT,
t.PROD_ATTRIB01_CD as NINESTAR_INK_TYPE,
t.TGT_CUST_TYPE_CD as OEM_INK_TYPE,
t2.ATTRIB_36 as PRODUCT_LINE_INK,
t2.ATTRIB_51 as ITEM_LEVEL,
t2.ATTRIB_37 as IDENTIFY_PATENT,
t2.ATTRIB_04 as CARTON_MATERIAL,
'N' as STATUS,
null,tcc.LAST_NAME,
tcu.LAST_NAME,sysdate,
'N' as USTATUS,
t2.ATTRIB_05 as FIN_PRO_CODE, --成品编码（标准） by 103023 20171109
t2.nr_attrib_61 as GG_PROD --G&G产品型号 by 103023 20171109
from S_PROD_INT t
left join S_PROD_INT_LSX t1 on(t1.PAR_ROW_ID=t.ROW_ID)
left join S_PROD_INT_X t2 on(t2.PAR_ROW_ID=t.ROW_ID)
left join S_PROD_INT_TNTX t3 on(t3.PAR_ROW_ID=t.ROW_ID)
left join S_CONTACT tcc on(tcc.ROW_ID=t.CREATED_BY)
left join S_CONTACT tcu on(tcu.ROW_ID=t.LAST_UPD_BY)
where t.ROW_ID=v_productId
) where ROW_ID=v_productId;
end if;
commit;
P_STATUS :='SUCCESSS';
end;
/
浙公网安备 33010602011771号